Upload or Download Oracle Workflows using WFLOAD Command Line Utility

Oracle Workflows are Database Applications whose definitions are stored in different Database Tables.

We can easily Upload/Download these definitions of the Workflows to the flat files using WFLOAD command line utility.

Workflow files are stored under <Application Top>/patch/115/import/US/ (Ex: PO_TOP/patch/115/import/US/powfpoag.wft)

To Upload

--------------

WFLOAD apps/<apps_password> <Access_Level> Y <upload_mode> <file_name>.wft

Example: WFLOAD apps/appspwd 0 Y FORCE powfpoag.wft

To Download

--------------------

WFLOAD apps/<apps_password> <Access_Level> Y DOWNLOAD <file_name>.wft

Example: WFLOAD apps/appspwd 0 Y DOWNLOAD powfpoag.wft

Different Access Levels Used:

0 -  9	Reserved for Oracle Workflow
10 - 19	Reserved for Oracle Application Library
20 - 99	Reserved for Oracle E-Business Suite
100 - 999	Reserved for Customer Organizations
1000	Public

Different UPLOAD modes used while uploading the WFT file to the database.

UPGRADE	Honors both Customization and Protection Levels of Data.
UPLOAD	Honors only Protection. Customization Levels are not respected.
FORCE	Both Customization and Protection Levels are not honored.

Usually all the Oracle Patches run the Workflows in UPGRADE mode and access level of 20 and hence our customizations are protected if properly preserved.

Basics about Profile Options

Profile Options are like Global Variables for Oracle Applications. They provide flexibility to Applications and how they work.

There are 2 types of Profile Options

1    1. System Profile Options

      2. User Profile Options

When logged in as System Administrator, the difference between System Profile and Personal Profile are to whom you are making the changes for.

To Navigate to System Profiles
System Administrator à Profile à System

From the System Profile screen, you are making changes to the profile options at any level including personal/user

From the User Profile Screen, you are making changes to the profile option values for yourself /user(who logged in). To navigate to Personal Profile:
System Administrator --> Profile --> Personal

User Profile Option is also available for all the users through Edit à Preferences à Profiles

Oracle Data Security and its components

Data Security: Coordinating with Function Security, Data Security provides additional security on the data. It lets the administrators decide what are all the actions that users can perform on the data.

Data Security Policies can reflect access to 
a. All Instances : All instances of an object represents that all rows in the database related to that object. If you consider Purchase Order (PO) as an object and All Instances of PO means all the Purchase Orders in the database. 
b. Instance Set: Instance set of an object represents a set of the related instances of an object. In our Purchase Order as an object example, a set of Blanket Purchase Orders represent a set of Instances of the PO. 
c. A Specific Instance: This generally represents a single row in the database. It is usually identified by a Primary Key value for the object(Ex: PO Header Id)

How to see Account Generator Workflows in Status Monitor of Workflow Administrator

By default, Oracle Account Generator Workflows does not show up in the Workflow Status Monitor screen of Workflow Administrator. To be able to see them in the status monitor, couple of profile options should be enabled to “Yes”.

           1. PO: Set Debug Workflow ON

           2. Account Generator:Run in Debug Mode

How to get a Total Amount on a Standard Purchase Order

Oracle provides couple of API's to get the Total Amount on a Standard Purchase Order. Using this API, you don't have to worry about the calculation of the Quantity x Unit Price and what needs to be excluded from the calculation.

To get the Total Amount of the current revision of the Standard Purchase Order
##########################################################
DECLARE
  l_total  NUMBER := 0;
BEGIN
  l_total := po_core_s.get_total('H', :p_po_header_id);
  dbms_output.put_line('PO Total Amount:'||l_total);
END;
##########################################################

To get the Total Amount of any previous revisions of the Standard Purchase Order, use the following API.

##########################################################
DECLARE
  l_total   NUMBER := 0;
BEGIN
l_total := po_core_s.get_archive_total_for_any_rev
                     (:p_po_header_id,-- PO Header Id

                      'H',            -- Standard Header
                      'PO',           -- Document Type
                      'STANDARD',     -- Document Sub Type
                      0,              -- Revision Num
                      'Y');           -- Base Currency
dbms_output.put_line('PO Total Amount:'||l_total);
END;
###########################################################

FND_REQUEST.ADD_LAYOUT for Setting Layout options for a Concurrent Request

Using FND_REQUEST.SUBMIT_REQUEST, Concurrent Request can only be submitted as a request and any layout options or print options cannot be added to the request. To be able to set the layout options for a request a separate function FND_REQUEST.ADD_LAYOUT needs to be called before calling the SUBMIT_REQUEST. This will be useful while calling a XML Publisher Report Concurrent Program is being submitted from another Program.

fnd_request.add_layout (template_appl_name   => 'Template Application',
                                             template_code        => 'Template Code',
                                             template_language    => 'en', --Use language from                   template definition
                                             template_territory   => 'US', --Use territory from template definition
                                             output_format        => 'PDF' --Use output format from template definition
                                            );

 

REP-0300: ORACLE error occurred REP-0069: Internal error

Following is the error I got while running a report through Concurrent Program in R12.
This is a very generic error and does not give you any details on what is causing this.


Enter Password:
REP-0300: ORACLE error occurred.
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: ORACLE error occurred.


I created a report and I was using a Select * from in one of the queries of the report datamodel. When I executed the concurrent program I was getting the above listed error.
I had no clue why this is happenning. I had multiple queries in that report. I tried to debug which query is causing the error by removing one query at a time and I was able to pin down the query causing this error. Then I realized that I am using Select * from.... in this query and I am selecting individual columns in all other queries. When I replaced the * with Individual columns in this query, Error disappeared.
Hope this helps.

Timeout Functionality on Workflow Notifications

Timeout functionality can be set on the Workflow Notifications. This will enable notifications to get Timed Out after desired time and as per the requirement if a reminder is needed, reminder notification can be sent and if reminder is not needed, notification can be escalated to the supervisors.

Below is the Approval notification properties of the Expense Report Approval Workflow

Go to Node Tab where you will see the option for Timeout. There are 3 options in here. 

Relative Time: This can be set when the Timeout period you want to set is Constant. If relative time is chosen and Timeout is set then if the Timeout needs to be changed in the future, Workflow has to be touched to set a new Timeout.

Item Attribute: This is a better option to choose when there might be a change in the Timeout period in the future. When Item Attribute is selected, we set the Timeout period from the back end code by setting the Timeout period in Minutes to the Workflow Attribute. Make sure this attribute value is set before sending the notification for approval.

Invoice Matching 2-Way, 3-Way and 4-Way

When Invoices are matched with PO, following 3 options can be used to match the PO.

2-Way Match: In 2-Way Match PO Quantity Should Match with Invoice Quantity and PO Unit Price should match with Invoice Unit Price. All the matching should be with in the tolerance limits allowed in the Receiving Control options.

           PO Unit Price = Invoice Unit Price
           PO Quantity = Invoice Quantity

3-Way Match:  In 3-Way Match PO Quantity should match with Invoice Quantity and Receipt Quantity. Also PO Unit Price should match with Invoice Unit Price. All the matching should be with in the tolerance limits allowed in the Receiving Control options.

           PO Unit Price = Invoice Unit Price
           PO Quantity = Invoice Quantity

           Invoice Quantity = Receipt Quantity

4-Way Match: In 4-Way Match PO Quantity should match with Invoice Quantity and Receipt Quantity and also Accepted Quantity Also PO Unit Price should match with Invoice Unit Price. All the matching should be with in the tolerance limits allowed in the Receiving Control options.

           PO Unit Price = Invoice Unit Price
           PO Quantity = Invoice Quantity

           Invoice Quantity = Accepted Quantity

           Invoice Quantity = Receipt Quantity

PO Receipt Accounting entries to GL

When a PO Receipt is created in the Receiving Transactions form with destination type as Inventory, 2 entries get created in the RCV_TRANSACTIONS form. One with Transaction type as Deliver and other with Receive. Also accounting is also derived for the 'RECEIVE' transaction. You will see an entry with RECEIVING_INSPECTION and an ACCRUAL entry in the RCV_RECEIVING_SUB_LEDGER table. When "Create Accounting: Receiving" is run, these Accrual Transaction for Receipt is transferred to GL through SLA. In SLA for Receive Transactions "Receiving Inspection" account is Debited and "Accrual" Account is credited. Journal Category will be "Receiving" for these transactions

For the Deliver Transaction type the transaction is sourced to Material Transactions as "PO Receipt" Transaction Type. Accounting will be done on this transaction when "Active Cost Worker" program is run. Usually this program will be scheduled to run every few minutes to calculate cost of the transactions. When "Accounting Program" is run, these transactions from MTL_TRANSACTION_ACCOUNTS will be transferred to GL through SLA. In SLA for Deliver Transactions, "Inventory Valuation" account is Debited and "Receiving Inspection" account is Credited. Journal Category will be "Inventory" for these Deliver Transactions.