In this Document
Purpose Troubleshooting Steps Running WFSTAT and WFRETRY Instructions to run wfstat Requisition: Purchase Order: Purchase Order Release: Instructions to run wfretry Requisition: Purchase Order: Purchase Order Release: Alternate retry method Instructions to run wfstat for Createpo workflow Instructions to run wfstat for Account Generator Workflow Still Have Questions? References
APPLIES TO:
Oracle Purchasing - Version 11.5.10 to 12.2 [Release 11.5 to 12.2]
IBM AIX on POWER Systems (32-bit)
Oracle Solaris on SPARC (32-bit)
HP Tru64 UNIX
Linux x86
z*OBSOLETE: Microsoft Windows 2000
HP-UX PA-RISC (64-bit)
IBM AIX on POWER Systems (64-bit)
z*OBSOLETE: IBM AIX 4.3 Based Systems (64-bit)
HP-UX PA-RISC (32-bit)
Obsolete Linux Intel (64-bit)
Oracle Solaris on SPARC (64-bit)
PURPOSE
The purpose of this article is to explain the use of the script wfstat and wfretry in order to monitor and retry workflow processes.
File wfstat.sql is a script that can be run to determine why a document is stuck. It usually provides the user with information that can be used to resolve the stuck document. Once the problem has been resolved, wfretry can be executed on the stuck document to get it moving through the system again.
In addition to instructions on running wfstat.sql and wfretry.sql, this document provides details about how to get the values necessary to run wfstat.sql for purchase orders, release, requisitions and account generator.
TROUBLESHOOTING STEPS
In order to run wfstat and wfretry, the user must have access to the APPS schema.
To run wfstat 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 WFSTAT and WFRETRY
Instructions to run wfstat
Run wfstat to determine why a document is stuck in In Process / Pre-Approved 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.
1. Login to SQL*Plus in the APPS schema and enter username/password.
2. Identify the parameter values. The wfstat 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';
3. Run the script and create an output file:
3.1 Start spooling an output file:
SQL> spool wfstat.lst
3.2 Run wfstat.sql, using the results from the appropriate query above as parameters: SQL> @$FND_TOP/sql/wfstat.sql
For example:
'Enter a value for 1: ' <enter the wf_item_type>
'Enter a value for 2: ' <enter the wf_item_key>
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 to the service request in My Oracle Support.
Note: The PO Approval analyzer incorporates the same information as the wfstat.sql script plus other validations to troubleshoot issues with the approval process, including employee authority checks, critical patches, invalid objects, data integrity validations and more. Reference Note 1525670.1 for instructions to download and run the script.
Instructions to run 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. If you implement the POERROR workflow, it will automatically retry certain document manager errors. Refer to Note:224028.1 - Oracle Purchasing POERROR Workflow Setup and Usage Guide White Paper for implementation details. This script comes seeded with the Oracle Applications and can be found in the $FND_TOP/sql directory residing on your database server machine.
1. Login to SQL*Plus in the APPS schema and enter username/password.
2. Identify the parameter values. The wfretry script requires 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';
3. Run the script wfretry with the following parameters:
a- wf_item_type (retrieved from above script)
b- wf_item_key (retrieved from above script)
c- 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.
d- Command: Uses the value of 'RETRY' in this scenario.
e- Result: Does not require a value in this scenario. Press return
For example:
'Enter a value for 1: ' <enter the wf_item_type>
'Enter a value for 2: ' <enter the wf_item_key>
accept activity prompt "Label:" POAPPRV_TOP for purchase orders MAIN_REQAPPRV_PROCESS for requisitions
accept comflag prompt "Command:" (RETRY)
accept result prompt "Result:" (NULL)
Alternate retry method
1. First determine the document's wf_item_type and wf_item_key values for the document type that is affected, using the scripts listed above.
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.handleError('&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:
Responsibility System Administrator: Navigation: Sysadmin -> Request -> Run
Set the parameters
Item Type = PO Approval 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 wfstat for Createpo workflow
1. Substitute the requisition number and the requisition's org_id for when you are prompted in the statement below:
select requisition_header_id
from po_requisition_headers_all
where segment1 = '&Enter_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 wfstat. The output from this wfstat 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 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 two or more records (depending on the number of document lines) . These values will be used to obtain the wfstat outputs for the sub-processes 'Verify Req Line Information' as well as 'Create And Approve Purchase Order/Release'.
After you have retrieved the 2 or 3 sets of item_types and item_keys (one set from step 2 and two or more possible sets from step 3), run the wfstat.sql file.
SQL> @wfstat <item_type> <item_key>
Replace item_type and item_key using the values retrieved in steps 2 and 3.
Note: An alternate method to obtain the workflow data for createpo workflow is through the data collection script from CREATEPO Workflow - Data Collection Script Note 1415918.1.
Instructions to run wfstat 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 purchase order, 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 wfstat.sql as follows:
SQL> @$FND_TOP/sql/wfstat.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 step 1 above.
10. Upload the output to the SR (Upload to Support link on SR update page) .
Still Have Questions?
Join our growing Oracle Procurement Community and learn from your peers and Oracle on how to address your unique issues in Procurement.
You can access the main Oracle Communities page at http://communities.oracle.com (If you are enrolled, the Procurement community will be listed on your left. If you're not already enrolled in the Procurement community, you can do so by clicking on the link Edit Subscriptions).
OR
From "My Oracle Support" as follows:
- Log into My Oracle Support
- Click on the 'Community' link at the top of the page
- Click in 'Find a Community' field and enter Procurement
- Double click on Procurement in the list
- Click on the 'Create a Community Post' button and submit your question.
转到底部
