Oracle Supply Chain Management Handbook
A comprehensive guide which contains functional and technical information: documents, articles, notes, SQL scripts and references in Oracle e-Business Suite Supply Chain modules.
Oracle Workflow Builder is a graphical tool for creating, viewing, and modifying workflow process definitions. It contains a Navigator window that you use to define the activities and components of your business process. You then assemble the activities in a process window to create a process diagram.
At any time you can add, remove, or change workflow activities, or set up new prerequisite relationships among activities. Oracle Workflow manages business processes according to rules that you define. Workflow Engine The Workflow Engine embedded in the Oracle database server implements process definitions at runtime. The Workflow Engine monitors workflow states and coordinates the routing of activities for a process. The Workflow Engine supports sophisticated workflow rules, including looping, branching, parallel flows, and sub flows.
Business Event System
The Business Event System consists of the Event Manager, which lets you register subscriptions to significant events, and event activities, which let you model business events within workflow processes.
Attributes: lists the attributes for the current item type. Item type attributes describe features of an item type. For example, if an item type is a purchase order requisition, then an item type Attribute can be the requisition amount or the requisition ID.
Processes: lists the process activities or workflow process definitions for the current item type. See: Process Window
Notifications: lists the notification activities associated with the current item type. A notification activity sends a message to a user or role. The message may prompt for a response or may Simply provide information
Functions: lists the function activities associated with the current item type. A function activity represents a PL/SQL stored procedure that the Workflow Engine executes automatically. A function activity can also have activity attributes associated with it
Events: lists the event activities associated with the current item type. An event activity represents a business event that the process receives, raises, or sends
Messages: lists the messages that a notification activity associated with the current item type can send to a user or role. A message can have message attributes associated with it. Lookup Types: lists the lookup types associated with the current item type. A lookup type has one or more values called lookup codes associated with it. A lookup type is a list of values that can be referenced by a message, or by a notification, function, or process as its possible result type.
Access Level: A numeric value ranging from 0 to 1000. Every workflow user operates at a specific access level. The access level defines whether the user can modify certain workflow data. You can only modify data that is protected at a level equal to or higher than your access level.
Activity: A unit of work performed during a business process.
Process: A set of activities that need to be performed to accomplish a business goal.
Workflow Engine: The Workflow Engine manages the state of all activities for an item, automatically executes functions and sends notifications, maintains a history of completed activities, and detects error conditions and starts error processes. The Workflow Engine: is implemented in server PL/SQL and activated when a call to an engine API is made. Performer: A user or role assigned to perform a human activity (notification). Notification activities that are included in a process must be assigned to a performer.
Role: One or more users grouped by a common responsibility or position. Notification: An instance of a message delivered to a user. Lookup Code: An internal name of a value defined in a lookup type. Lookup Type: A predefined list of values. Each value in a lookup type has an internal and a display name. Item Type: A grouping of all items of a particular category that share the same set of item attributes. For example, PO Requisition is an item type used to group all requisitions created by Oracle Internet Commerce’s Web Requisitions page. Item type is also used as a high level grouping for processes. Event: An occurrence in an internet or intranet application or program that might be significant to other objects in a system or to external agents. Background Engines: A supplemental Workflow Engine that processes deferred or timed out activities. Message: The information that is sent by a notification activity. A message must be defined before it can be associated with a notification activity. A message contains a subject, a priority, a body, and possibly one or more message attributes. Synchronous or Asynchronous Processes
A workflow process can be either synchronous or asynchronous.
A synchronous process is a process that can be executed without interruption from start to finish.
The Workflow Engine executes a process synchronously when the process includes activities that can be completed immediately, such as function activities that are not deferred to the background engine.
With a synchronous process, you can immediately check for process results that were written to item attributes or directly to the database. However, the user must wait for the process to complete.
An asynchronous process is a process that the Workflow Engine cannot complete immediately because it contains activities that interrupt the flow.
Examples of activities that force an asynchronous process include deferred activities, notifications with responses, blocking activities, and wait activities.
With an asynchronous process, the user does not have to wait for the process to complete to continue using the application. However, the results of the process are not available until the process is completed at a later time.
Item type: The internal name for the item type. Item types are defined in the Oracle Workflow Builder.
Item key: The string uniquely identifies the item within an item type.
Actid: The ID number of the activity from which this procedure is called.
Funcmode: The execution mode of the activity. If the activity is a function activity, the mode is either 'RUN' or 'CANCEL'. If the activity is a notification activity, with a postnotification function, then the mode can be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or 'RUN'. Other execution modes may be added in the future.
Resultout: COMPLETE: activity completes with the indicated result code. The result code must match one of the result codes specified in the result type of the function activity.
WAITING: activity is pending, waiting on another activity to complete before it completes. An example is the Standard 'AND' activity.
DEFERRED: activity is deferred to a background engine for execution until a given date. Must be of the format: to_char, f_engine.date_format)
NOTIFIED: an external entity is notified that an action must be performed. A notification ID and an assigned user can optionally be returned with this result. Note that the external entity must call Complete Activity () to inform the Workflow Engine when the action completes. ERROR: activity encounters an error and returns the indicated error code.
Wf_engine.SetItemAttrText (itemtype, itemkey, ‘’, ‘’); Creates a new runtime process for an application item. Procedure CreateProcess (itemtype in varchar2, itemkey in varchar2, process in varchar2 default ’’, user_key in varchar2 default null, owner_role in varchar2 default null);
Procedure StartProcess (itemtype in varchar2, itemkey in varchar2);
Procedure LaunchProcess (itemtype in varchar2, itemkey in varchar2, process in varchar2 default '', userkey in varchar2 default '', owner in varchar2 default '');
Procedure SetItemAttrText (itemtype in varchar2, itemkey in varchar2, aname in varchar2, avalue in varchar2);
Procedure SetItemAttrNumber (itemtype in varchar2, itemkey in varchar2, aname in varchar2, avalue in number);
Procedure SetItemAttrDate (itemtype in varchar2, itemkey in varchar2, aname in varchar2, avalue in date);
Function GetItemAttrText (itemtype in varchar2, itemkey in varchar2, aname in varchar2) return varchar2;
Function GetItemAttrNumber (itemtype in varchar2, itemkey in varchar2, aname in varchar2) return number;
Function GetItemAttrDate (itemtype in varchar2, itemkey in varchar2, aname in varchar2) return date;
Procedure Background (itemtype in varchar2, minthreshold in number default null, maxthreshold in number default null, process_deferred in boolean default TRUE, process_timeout in boolean default FALSE, process_stuck in boolean default FALSE);
Procedure ItemStatus
(itemtype in varchar2,
itemkey in varchar2,
status out varchar2,
result out varchar2);
1) What are the major components of the workflow system?
Workflow Engine,
Business Event System,
The Notification Service,
He Workflow Builder,
And administrative and monitoring tools.
The Workflow Engine is a set of tables and PL/SQL stored procedures that manage the execution of workflow processes and tracks work-in- process.
2) What platform is workflow currently available on?
Oracle Workflow embedded in Oracle Applications is available on all platforms that Oracle Applications is available on.
The builder of course is a client product running on Windows 95/98/2000/NT.
3) Can workflow work on a distributed database? Yes - Oracle Workflow 2.6 includes a major new feature called the Business Event System which supports the sending and receiving of messages between databases.
4) How does Oracle Workflow differ from Oracle Alert?
Oracle Alert is a database event detection tool.
Oracle Workflow technology enables automation and continuous improvement to business processes, routing information according to user-defined business rules.
5) What electronic mail systems can I use in conjunction with Oracle Workflow Notifications? The Oracle Workflow Notification Mailer can be used (WFMAIL).
6) How do I verify the version of Oracle Workflow that I have installed?
For Workflow versions prior to 2.5: SQL> select TEXT from WF_RESOURCES where NAME='WF_VERSION';
7) How do I select the 'File, Load Roles' option when it is grayed out?
To use the 'File, Load Roles' option you must store your workflow definition in the database so that you have a database connection through which you can bring back records from the database.
8) What Access Level should I be using in Oracle Workflow Builder?
You should be running the Workflow Builder with an Access Level of 100. 9)How do I trace errors in my Workflow processes?
Using the Workflow Monitor and/or wfstatus.sql script.
Use wfstatus.sql to display an end user status report for an indicated item. The output is 132 characters per line. Use the script as follows:
sqlplus @wfstatus
10) How do I customize the Account Generator Workflow process for my company? Customers are expected to customize the Account Generator process. You can do this by editing the existing process or creating your own process, but this MUST be within the seeded 'Item Type'. You should NOT create a NEW 'Item Type' as this would NEVER be run by Account Generator.
If you create a new process, you must ensure it is "Runnable". You must then assign the appropriate process to the Accounting Flex field structure in the Account Generator Process window in Oracle Applications (login as System Administrator, Application -> Flex fields -> Key -> Accounts and query up the required Accounting flex field structure). 11) Whats new about Oracle Workflow 2.6?
Executing custom code on the event information
Sending event information to a workflow process
Concurrent programs can be run from the command line as well:
WFRESGEN 0 Y FILE ...
WFRESGEN 0 Y DATABASE ...
WFLOAD 0 Y DOWNLOAD
WFLOAD 0 Y UPLOAD
WFLOAD 0 Y UPGRADE
WFMAIL 0 Y
for example
WFLOAD apps/apps 0 Y UPGRADE @fnd:install/data/wferror.wft
WFLOAD apps/apps 0 Y UPLOAD @po:install/data/poreq.wft
WFLOAD apps/apps 0 Y DOWNLOAD myprocess.wft POREQ
WFLOAD apps/apps 0 Y UPGRADE c:\orant\wf20\res\US\wfstd.wft
12) What is the difference between WFLOAD and wfload?
UPPERCASE executable names usually refer to the Concurrent Manager variant of the program.
Lowercase executable names usually refer to the stand-alone unix version.
These two programs do the same thing, but take different arguments. 13) Can a workflow process be restarted from the point of failure? Yes, Wf_Engine.HandleError is designed for this. When the cause of the error has been corrected, the HandleError procedure can be called with the 'RETRY' option which will attempt to re-execute the specified activity.
14) How can I tell if a work item already exists?
There is an API for this: WF_ITEM.ITEM_EXISTS
15) Does workflow always execute according to the latest definition for an itemtype? No. Running processes use the activity definitions that were in effect when the process was started.
16) How do I completely wipe out workflow definitions from the database?
There are scripts that directly delete workflow definition data from the database. wfrmita.sql -- Delete all definitions for an ITem Attribute wfrmitt.sql -- Delete all definitions for an ITem Type wfrmall.sql -- Delete all definitions 17) What does wf_purge.total actually do?
WF_PURGE.TOTAL deletes obsolete runtime data which includes: Items Item activity statuses Notifications Expired activity versions 18) How do I raise errors in my application? DO I have to add errors to the workflow resource table?
standard message dictionary APIs : fnd_message.set_name(...); fnd_message.set_token(...); app_exception.raise_exception;
19) How do you retrieve the actual Performer's user name? Use the API username := wf_notification.Responder(nid)
20) How does the DOCUMENT type attribute work?
The DOCUMENT type attribute permits you to execute a pl/sql package procedure which you may use to build the message body dynamically at run time. For example, if you have an unlimited number of lines to display, rather than hardcoding an attribute for each line and setting a limit, use the DOCUMENT type. This allows you to call a package with the following parameters
procedure MY_PROCEDURE
( document_id in varchar2, -- your parameter list display_type in varchar2, -- either text/plain of text/HTML set by WF document in out varchar2, -- out string inserted into mesage body by WF document_type in out varchar2) -- usually set this equal to display_type is ...
The next statements will show how many events of each type are in the WF_DEFERRED queue:
column EVENT_NAME format a40;
select w.user_data.event_name EVENT_NAME, count(*) COUNT from WF_DEFERRED w group by w.user_data.event_name 22) How do you determine the WF Item Key value to run wfstatus.sql for Account Generator workflows?
Select item_key, begin_date, end_date from wf_items where item_type = 'PAAPINVW' and begin_date between '&low_start' and '&high_start';
STEP 1: Create a SQL*Loader program that uses the flat file provided by the card issuer containing the credit card transaction details you want to transfer into the AP_EXPENSE_FEED_LINES_ALL table.
STEP 2: Define the card issuer as a supplier. Navigation: Oracle Payable Super User--> Suppliers--> Entry.
STEP 5: Define the GL Account Sets Navigation: Oracle Payable Super User--> Setup--> Credit Cards-->Procurement Card--> GL Account Sets.
STEP 6: Define the Credit Card Profile Navigation: Oracle Payable Super User--> Setup--> Credit Cards-->Procurement Card--> Profiles.
STEP 7: Select the Procurement Card check box to indicate that the supplier site is P-Card enabled.
STEP 8: Set the Profile Options: PO: Use P-Cards in Purchasing
STEP 9: After that upload the data through the SQL Loader Program, then run the "Procurement Card Transaction Validation Program" Request. Navigation: Payable Super User--> View--> Request
STEP 10: Run the other Requests: Navigation: Payable Super User--> View--> Request "Create Procurement Card Issuer Invoice" "Payables Open Interface Import with source" Source Parameter as "PCARD"
Internal Requisitions provide the mechanism for requesting and transferring material from inventory to other inventory or expense locations. When Purchasing, Order Entry / Shipping, and Inventory are installed, they combine to give you a flexible solution for your inter-organization and intra-organization requests.
Internal Requisitions provide a flexible solution to your inventory requisitioning needs. You can eliminate user intervention when you do not need it, such as when you are loading internal requisitions into Order Entry / Shipping or placing demand. The following sections describe the entire business and information flow for internal requisitions, and identifies opportunities for you to simplify processing.
Shipping Networks
Inventory provides the features you need to define your inter-organization shipping network. For transfers between two organizations, you can specify whether to use intransit or direct shipments. You can also require internal orders for transfers between specific organizations. Inter-organization shipping network information describes the relationships and accounting information that exists between a shipping (from) organization that ships inventory to a destination (to) organization.
To define shipping network:
1. Navigate to the Shipping Networks window.
2. Determine if the organization displayed is the To Organization, From Organization, or the From or To Organization.
Note: If your function security allows multiple organization access, you can enter any organization. Otherwise, the Organization field is display only.
3. To define a new shipping network for the current organization choose New Record from the Edit menu. To enter a new record in a single-row window choose the New button.
To find existing shipping network information for the current organization choose Find.
4. Select the Main alternative region.
5. Enter the shipping or From organization.
6. Enter the destination or To organization.
7. Enter the transfer type:
Direct: Inter-organization transfers move inventory directly from the shipping organization to the destination organization.
Intransit: Inter-organization transfers move inventory to intransit inventory first. You can track this inventory until it arrives at the destination organization.
8. If you selected Intransit in the Transfer Type field, select the FOB terms:
Receipt: The shipping organization owns the shipment until the destination organization receives it.
Shipment: The destination organization owns the shipment when the from organization ships it (and while the shipment is in transit).
9. If you selected Intransit in the Transfer Type field, select a receipt routing option:Standard: Receive this item first, then deliver without inspection.Direct: At receipt, deliver this item directly to its location.Inspection: Receive this item first, inspect it, then deliver.
10. Indicate whether an internal order is required for each transfer.11. Save your work.
To define transfer charge information:
1. Select the Transfer, Distance alternative region.
2. Select the inter-organization transfer charge type for calculating transfer charges:
None: Do not add transfer charges.
Predefined Percent: Automatically add a predefined percent of the transaction value.
Requested Value: Enter a discrete value to add.
Requested Percent: Enter a discrete percentage of the transfer value to add.
The default value is the value you defined in the Organization Parameters window for the shipping organization.
3. If you selected Predefined Percent in the Transfer Charge Type field, enter the percentage value to add to a material transfer. For example, a value of 10 equals a transaction value of 10%.The default value is the value you defined in the Organization Parameters window for the shipping organization.
4. Optionally, you can define the following: Enter the unit of measure for the distance from the shipping organization to the destination organization. Enter the distance value (in the unit of measure) from the shipping organization to the destination organization.
To define account information:
1. Select the Primary Accounts alternative region.
2. Enter the general ledger transfer credit account used to collect transfer charges for the shipping organization. The default value is the value you defined in the Organization Parameters window for the shipping organization.
3. Enter the general ledger account used to collect the purchase price variance for inter-organization receipts into standard cost organizations. You must enter an account if your receiving organization is using standard costing.
To enter additional account information:
1. Select the Secondary Accounts alternative region. Note: The default values for the following fields are the values you defined in the Organization Parameters window for the shipping and destination organizations.
2. Enter the general ledger receivables account used as an inter-organization clearing account for the shipping organization. The inter-organization receivable account for the shipping organization should equal the inter-organization payables account for the receiving organization.
3. Enter the general ledger payables account used as an inter-organization clearing account for the receiving organization. The inter-organization receivable account for the shipping organization should equal the inter-organization payables account for the receiving organization.
To enter intransit account information:
1. Select the Intransit Account alternative region.
2. Enter the general ledger account used to hold the intransit inventory value.
To enter intransit lead time for shipping methods:
1. Select Shipping Methods on the Special menu to open the Inter-org Shipping Methods window.
2. Enter the shipping method for which you want to associate an intransit lead time for the displayed from and to organizations.
You create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts, and you can encumber these purchase orders if you use encumbrance accounting.
You can include agreement dollar amount, Ship to and Bill to address with supplier details. Select the Global checkbox only if you are creating a Global agreement.
Standard purchase orders can only reference the contract during its effective dates. Part of the terms and conditions could include a percentage discount off list prices for all products the supplier sells or different discounts for different groups of products.
Click Approve button and then Ok.
Once contract purchase agreements is approved you can use it with standard purchase order per agreed amount or less than committed amount.
Click on Referenced Document tab.
Here you can refer previously created contract purchase agreements.
Click on Shipment button.
Mention Shipment details with need by date. You can read more about Standard Purchase Order and splitting shipment and charging accounts.
You can enter sub inventory here.
Click on Approve button. Once Standard Purchase order is approved you can check contract purchase agreement. Open purchase order in query mode and check released amount. The dollar value displayed in Released field is the total dollar value of all standard purchase order lines referencing the contract.
Using global agreements (a special type of blanket purchase agreement), buyers can negotiate enterprise-wide pricing, business by business, then execute and manage those agreements in one central shared environment. Enterprise organizations can then access the agreement to create purchase orders that leverage pre-negotiated prices and terms. You can encumber funds for a global agreement.
Create And Update Agreements
The Agreements page of the Buyer's Work Center supports two basic Oracle Purchasing document types: the global blanket purchase agreement and the global contract purchase agreement. See: Purchase Order Types, Entering Purchase Agreement Information
1. From the Agreements page of the Buyer's Work Center navigate to the Create Blanket Purchase Agreements page or the Create Contract Purchase Agreements page by selecting a style from the Create list and then clicking Go. If you need to modify an existing agreement click its Agreement number and it is displayed on the Blanket Purchase Agreements page or the Contract Purchase Agreements page. Click Update to make your changes. You can change the emphasis of page by clicking Headers, Lines, or Controls.
2. Select the Agreement Header tab to enter general information about the entire
agreement:
Enter agreement header information: Operating Unit, Supplier, Supplier Site,
Supplier Contact, Communication Method, Buyer, Amount Agreed, Currency,
Effective From and To dates, and Description.
Enter agreement terms information: Bill-To Location, Payment Terms, Pay On
Receipt, Required Acknowledgment, Acknowledge By, Amount Limit, Default
Ship-To, Ship Via, Freight Terms, FOB, and Transportation Arranger.
Enter agreement attachments are entered by clicking Add Attachments.
Optionally enter a Note to Supplier or a Note to Receiver.
3. Select the Agreements Lines tab to enter details about the items on this
agreement:
Note: There are no lines on a contract purchase agreement.
To add lines from the catalog, select From Catalog in the Add Lines list and
click Go. To add lines from your favorites list, select From Favorites in the Add
Lines list and click Go.
Enter line details: Type, Item/Job, Description, Category, Unit of Measure
(Unit), Price, and Expiration date. To expand the line to full page view click the
Actions Update icon.
In the Action column for each line you can click icons to Update, Split, or
Delete. For details on what you can do to your agreement.
4. Select the Agreements Control tab to enter control details for this agreement:
Define operating unit access by selecting a Purchasing OU and Purchasing Site
for a Requesting OU.
Enable Automatic Processes for your blanket purchase agreement by selecting: Enable Automatic Sourcing
Update Sourcing Rules and Assignments
See: Enter Additional Options,
Enable Retroactive Pricing for your blanket purchase agreement by selecting:
Apply Price Updates to Existing POs
Communicate Price Updates
Enter Notification Controls for this agreement by entering Condition, Warning
Delay, Percent, Amount, Effective From date, and Effective To date.
Using Agreement Actions
• You can take the standard actions of Cancel, Save, Approval Options, and Submit
by clicking one at the top or bottom of the page. .
Note: The Cancel button is used to return to the Summary page.
• From the Actions list at the top you can select (depending on the status of your
agreement and the page you are viewing):
• Delete
• Enable Catalog Administrator Authoring
• Enable Supplier Authoring
• Place on Hold
• Duplicate with Attachments
• Duplicate without Attachments
• If the PDF option is enabled, you can select View PDF to see the formatted
purchase order as it will be communicated to the supplier.
• If Oracle Procurement Contracts is implemented you can select Add Contract
Terms.
Click Go after your selection. For details regarding these actions see: Purchase
Order Tools Menu Options, and Purchase Order Inquire Menu Options, .
Information templates are used to gather additional information in Oracle iProcurement to pass necessary order processing information to suppliers. When an information template is assigned to a category or item, the application prompts requesters to provide the information specified in the template when the item is added to the shopping cart. This information becomes a line-level attachment to the requisition.
For example, you can implement information templates for items such as business cards that require additional information (name, address, e-mail address, phone) from the requester. Oracle iProcurement prompts for name, address, e-mail address, and phone number when you order business cards. Each information template must be associated with an Oracle Purchasing item or item category. If an information template is associated with an item category, all items belonging to that category are also associated with the template.
Create an information template
1. Navigate to the Define Information Template window in Oracle Purchasing.
2. Give your Template a name. This name appears as a heading during checkout.
3. Select Available in All Organizations to make the template available to requesters in all operating units. Deselect this option to make the template available only to requesters in the same operating unit in which you are creating the information template. For example, you are logged in with a responsibility that is tied to the Vision France operating unit. If you leave this option deselected, then your information template is available only to users in your vision France operating unit.
4. Select an Attachment Category of To Supplier or To Buyer. To Buyer means that the requisition, when turned into a purchase order, displays the information template as an attachment to the buyer. To Supplier means that the information template also is available as an attachment to the supplier.
5. Enter an Attribute Name and Description. The Attribute Description is the actual field prompt that appears in Oracle iProcurement.
6. Optionally, enter a default value to automatically appear in the field. For example, for an Attribute Name of Body Color, the default value could be Black.
7. To make the template field to be a list of values from which the requester selects, see the steps for creating lists.For example, you create a Country of Origin field. Instead of letting the requester enter any value, you provide a menu that contains only the values Canada, US, and Mexico.
8. Indicate whether the field is mandatory for Oracle iProcurement requesters. If the field is mandatory, requesters must enter a value in the field before proceeding to complete the requisition.
9.Indicate whether to activate the attribute to actually appear in Oracle iProcurement. In certain circumstances, you can define an attribute, but delay enabling it for display.
10. Choose Associate Template to associate the template with an item or item category.
11. In the Information Template Association window select the type of association (Item Number or Item Category) to associate with the template.
12. If you selected Item Number, enter the number. If you selected Item Category, enter the category.
