In order to run wfstatus and wfretry, the user must have access to the APPS schema.
To run wfstatus and wfretry, the following system tools and access are required:
Login capabilities to the database server and $APPL_TOP
Access to sqlplus on the database server - APPS schema
Write permissions for a spool file to be created via sqlplus
Running wfstatus and wfretry
wfstatus
Run wfstatus to determine why a document is stuck In Process or a workflow process has failed. This script comes seeded with the Oracle Applications and can be found in the $FND_TOP/sql directory residing on your database server machine.
Login to SQL*Plus in the APPS schema and enter username/password.
The wfstatus script requires two values: wf_item_type and wf_item_key. The following scripts will return their values. Run one of the following queries, entering the problematic Purchase Order Number or Requisition Number, (be sure to enclose the number in single quotes):
--------------------------------------------------------------------------------
Requisition:
SQL > select hr.name, prh.segment1, prh.wf_item_type, prh.wf_item_key
from
po_requisition_headers_all prh,
hr_all_organization_units hr
where
prh.org_id = hr.organization_id and
prh.segment1 = '&Enter_Req_Number'
--------------------------------------------------------------------------------
Purchase Order:
SQL > select hr.name, poh.segment1, poh.wf_item_type, poh.wf_item_key
from
po_headers_all poh,
hr_all_organization_units hr
where
poh.org_id = hr.organization_id and
poh.segment1 = '&Enter_PO_Number'
--------------------------------------------------------------------------------
Purchase Order Release:
SQL > select hr.name, poh.segment1, por.release_num, por.wf_item_type, por.wf_item_key
from
po_headers_all poh,
po_releases_all por,
hr_all_organization_units hr
where
poh.org_id = hr.organization_id and
por.org_id = poh.org_id and
poh.po_header_id = por.po_header_id and
poh.segment1 = '&Enter_PO_Number' and
por.release_num = '&Enter_Release_Num'
--------------------------------------------------------------------------------
Start spooling an output file:
SQL> spool wfstatus.lst
- Run wfstatus.sql, using the results from the appropriate query above as parameters: SQL> @$FND_TOP/sql/wfstatus.sql
For example:
'Enter a value for 1: '
'Enter a value for 2: '
End the spooled output file:
SQL> spool off
SQL> exit
Review the output and take appropriate corrective action. If you need assistance with file output, upload the file using Metalink (Upload to Support link on SR update page) .
--------------------------------------------------------------------------------------------------------------------
Instructions to run the wfretry
When a workflow errors, once the cause of the error is resolved, you MUST always resubmit it, the system will not automatically process it again, unless you have implemented the POERROR workflow. Note 224028.1 - Oracle Purchasing POERROR Workflow Setup and Usage Guide White Paper. The POERROR will automatically retry document manager errors.
wfretry is run with the following parameters:
accept activity prompt "Label:" POAPPRV_TOP
accept comflag prompt "Command:" (RETRY)
accept result prompt "Result:" (NULL)
item_type: Represents the Workflow Item Type. For requisitions, it will be 'REQAPPRV';
for any type of purchase order, it will be 'POAPPRV'.
item_key: Represents the Workflow Item Key. This will be a numeric value that is unique for each document created. The Item Key can be located by running the following statements in SQL*Plus, using your APPS schema:
--------------------------------------------------------------------------------
Requisition:
SQL > select hr.name, prh.segment1, prh.wf_item_type, prh.wf_item_key
from
po_requisition_headers_all prh,
hr_all_organization_units hr
where
prh.org_id = hr.organization_id and
prh.segment1 = '&Enter_Req_Number'
--------------------------------------------------------------------------------
Purchase Order:
SQL > select hr.name, poh.segment1, poh.wf_item_type, poh.wf_item_key
from
po_headers_all poh,
hr_all_organization_units hr
where
poh.org_id = hr.organization_id and
poh.segment1 = '&Enter_PO_Number'
--------------------------------------------------------------------------------
Purchase Order Release:
SQL > select hr.name, poh.segment1, por.release_num, por.wf_item_type, por.wf_item_key
from
po_headers_all poh,
po_releases_all por,
hr_all_organization_units hr
where
poh.org_id = hr.organization_id and
por.org_id = poh.org_id and
poh.po_header_id = por.po_header_id and
poh.segment1 = '&Enter_PO_Number' and
por.release_num = '&Enter_Release_Num'
Label: The label field is used to call the workflow startup process for the document type in question. This can be located in the Oracle Purchasing application.
Responsibility: Purchasing Super User
Navigation: Setup -> Purchasing -> Document Types
Select the document type for which you are going to run the wfretry script, and take note of the value which resides in the Workflow Startup Process field. Usually, but not always, this value will be 'MAIN_REQAPPRV_PROCESS' for purchase requisitions and 'POAPPRV_TOP' for purchase orders; confirmation of this value should be made by navigating to the Document Types form.
Command: Uses the value of 'RETRY' in this scenario.
Result: Does not require a value in this scenario.
1. cd $FND_TOP/sql
2. run @wfretry.sql
3. You will be prompted for value1 (Item type from above)
4. You will be prompted for value2 (Item key from above)
5. Workflow startup process: POAPPRV_TOP for purchase orders MAIN_REQAPPRV_PROCESS for requisitions
6. You will be prompted for process attempt: choose RETRY
7. Ignore the next command and press return.
Alternate wfretry
1. First determine the document's wf_item_type and wf_item_key values for the document type that is affected:
Requisition:
SQL > select hr.name, prh.segment1, prh.wf_item_type, prh.wf_item_key
from
po_requisition_headers_all prh,
hr_all_organization_units hr
where
prh.org_id = hr.organization_id and
prh.segment1 = '&Enter_Req_Number'
--------------------------------------------------------------------------------
Purchase Order:
SQL > select hr.name, poh.segment1, poh.wf_item_type, poh.wf_item_key
from
po_headers_all poh,
hr_all_organization_units hr
where
poh.org_id = hr.organization_id and
poh.segment1 = '&Enter_PO_Number'
--------------------------------------------------------------------------------
Purchase Order Release:
SQL > select hr.name, poh.segment1, por.release_num, por.wf_item_type, por.wf_item_key
from
po_headers_all poh,
po_releases_all por,
hr_all_organization_units hr
where
poh.org_id = hr.organization_id and
por.org_id = poh.org_id and
poh.po_header_id = por.po_header_id and
poh.segment1 = '&Enter_PO_Number' and
por.release_num = '&Enter_Release_Num'
2. Then with the above wf_item_type and wf_item_key values, retry the approval from SQLPLUS as follows, when run it will prompt you for the values obtained above in step 1:
SQL> exec wf_engine.startprocess('&WF_ITEM_TYPE','&WF_ITEM_KEY');
SQL> commit;
3. Run the Workflow Background program as now the approval workflow for this document will be run in a background process:
Select System Administrator responsibility.
Navigate => Sysadmin => Request => Run
Set the parameters
Item Type = PO or Requisition Approval;
Processed Deferred = Yes;
Process Time Out = Yes.
Process Stuck = Yes, if the Process Stuck parameter is available
4. Check if the document gets approved now.
-----------------------------------------------------------------------------------------------------------------------
Instructions to run wfstatus.sql for Createpo workflow
1. Substitute the requisition number and the req's org_id for when you are prompted in the statement below:
select requisition_header_id
from po_requisition_headers_all
where segment1 = '&REQ_NUMBER'
and org_id = &ORG_ID;
Segment1 will represent the requisition number which was not created into the Standard Purchase Order
2. Substitute the requisition_header_id from script 1 when prompted in script 2 below:
select item_type, Item_key
from wf_items
where item_key like '&REQ_HDR_ID'||'%'
and item_type='CREATEPO'
and root_activity = 'OVERALL_AUTOCREATE_PROCESS';
The results of this script will deliver an item_key and item_type this represents the first values that will be used for wfstatus. The output from this wfstatus represents a text explanation for the overall Create Documents Process
3. It is now necessary to get the item_type and item_key for the two sub-processes called during the Overall Create Documents Top Process. We know these processes as 'Verify Req Line Information' and 'Launch Process to Create/Approve PO or Release'. You will substitute the value wf_item_key obtained from the 2nd script that was run in step 2.
select item_type, Item_key
from wf_items
where parent_item_type='CREATEPO'
and parent_item_key = '&WF_ITEM_KEY';
This process will return 2 records. Use the Item_key and Item_type from the 3rd statement to run a wfstatus against the two sub-processes 'Verify Req Line Information' as well as 'Create And Approve Purchase Order/Release'. The wfstatus should reveal more information on the CREATEPO process.
After you have retrieved the 2 or 3 sets of item_types and item_keys (1 set from step 2 and 2 possible sets from step 3 please move to run the wfstatus.sql file. Since you have started the sql session from $FND_TOP/sql you may just type in @wfstatus
- You will then be prompted for two values : Value1 is the Item Type and Value 2 is the Item Key. Please run the wfstatus.sql script for each
item_type/item_key combination which was retrieved in steps 2 and 3. These will all spool to the file createpo.txt.
----------------------------------------------------------------------------------------------------------
Instructions to run wfstatus.sql for Account Generator Workflow
1. Set the following personal profiles:
Account Generator: Purge Runtime Data = NO
Account Generator: Run in Debug Mode = YES
2. Create a PO as usual, and drill down to the Distributions region.
3. Click in the Charge Account field and allow the system to attempt to build the charge account.
If errors are received during this process, please choose the OK button through all errors.
4. Do no exit the Distributions form yet. Instead, choose Help/Tools/Examine and enter the following:
Block: Parameter
Field: charge_acc_wf_itemkey
Value: (this value will be returned when moving from 'Field' to 'Value')
Take note of what is returned for 'Value'.
5. From the database server, login to SQL*Plus using the APPS username/password.
6. Create an output file in the home directory using the following command in
SQL*Plus:
SQL> spool $HOME/acctgen.txt
7. Run wfstatus.sql as follows:
SQL> @$FND_TOP/sql/wfstatus.sql
When prompted with 'Enter value for 1', type: POWFPOAG
When prompted with 'Enter value for 2', type the numeric value that was returned in step #4.
8. Once the script completes, end the spooled output file:
SQL> spool off
9. Reset the profile options changed in #1 above.
10. Please upload the output to Metalink (Upload to Support link on SR update page) .
---------------------------------------------------------------------------------------------------------
