How to Diagnose Purchasing Document Approval Routing

In this Document
TESTCASE INFORMATION
SETUP
DIAGNOSTICS
PROCESSING
TROUBLESHOOTING
TRACING
LOGGING
DATA AND QUERIES
CODE FILES
TESTCASE INFORMATIONThere are multiple conditions and setups that govern the routing of purchasing approval documents. It is key to understand the business requirements of the organization for which purchasing documents are being submitted under, to be able to properly setup and debug routing logic. Hence the following information should be understood ahead of time to make the proper assignments and approval setups:
Determine the hierarchy routing being used (Employee/Supervisor or Position Hierarchy)
Determine the routing method enabled (Direct or Hierarchical)
Determine what documents types are used (Requisitions, Purchase Orders, Releases, Blankets, etc.)
Determine under which applications are documents entered through. (core Purchasing, iProcurement, or both)
Determine the source of the documents entering the application. (Imported or entered through the application)
Determine the link between Requisitions and Purchase Orders/Releases (Autocreation from requisitions or manual entering through the forms)

SETUP
Regardless of the purchasing document type, the majority of all businesses require some sort of check and balance for the processing of procurement requests. The most efficient, and accepted way is through the establishment of a routing system for purchasing documents that enables different levels with approval authorities to be engaged in a methodical and predictable way. Purchase Approval Document Routing is the science and method behind mapping out a path for a procurement document to follow to allow for the proper review and acceptance by the desired members in a business or company.


1. Types of Hierarchy Routing
Employee / Supervisor:
The employee / supervisor relationship can be utilized to establish a route that a purchasing document will take to allow for the necessary approval and review to take place before final Approval. The routing is a peer-to-peer relationship to form a linear routing from one specific user to another specific user and then onto another specific user. The approval routing structures are defined as the employees are entered using the Enter Person window. For this type of setup, Purchasing does not require that positions be setup. By giving each employee in the approval line increasing approval authority, purchasing documents are able to be submitted, reviewed, processed and approved in a streamlined and predictable fashion.

Enter Person Form

Enter Assignment Form - Job

Enter Assignment Form - Supervisor



Position Hierarchy:
The Position Hierarchy routing method, progresses documents through various levels of Approval Groups to achieve the necessary review for final Approval. Multiple users can be assigned to a single Approval Group, so it is possible to quickly define and manage document routing for users that hold the same tasks in the Approval process. For position hierarchy usage, both jobs and positions must be defined. While positions and position hierarchies require more initial effort to set up, they allow for definition of approval routing structures that remain stable regardless of how frequently individual employees leave an organization or relocate within it. The flexibility with employee assignments, makes positions much more conducive for a larger user/employee base.

For more details on Position Hierarchy logic and setup see the documentation and very comprehensive Note 134947.1 - Purchasing Setup of Approval Hierarchies
2. Approval Groups a Approval Assignments
Approval Groups:
Approval Groups allow for flexible authorization rules to be defined to document type and job relationships, or positions. These authorization rules allow for includes and excludes, along with amount limits. The following elements utilize these rules:

- Document Total *
- Account Range *
- Item Range
- Item Category Range
- Location

* Mandatory usage for an Approval group
The ranges and limits under the Approval Rules section, form the authorization rules for the specific Approval Group being defined.
Approval Groups - Approval Rules

Approval Rules - Account Range



Approval Assignments:
Approval Assignments allows for the specific Document Types to be tied to the desired Approval Groups, and then assigned to a job and/or position. The linking of the three pieces taken here form sa position/job assignment with specific limits and ranges for a specific document type.
Assign Approval Group Form - Job and Position

Assign Approval Group Form - Document Types

Assign Approval Group Form - Approval Groups


Approval Routing:
Regardless if an Employee / Supervisor hierarchy or a Position Hierarchy is used the logic in setting-up the document approval routing remains the same. Approval levels are defined in a sequential order. Each level picks up where the previous level’s limits were exceeded. This relationship from level to level is the backbone for forming the Default Approval List for the document. The Default Approval List is the dynamically generated road map that a specific document will follow in an Approval Hierarchy. A valid Approval Hierarchy will be able to correctly route any document to the correct Approval Group that is able to complete the Approval action.

Example:
Approval Groups

Approval Group: Requestor
- Document Type: Purchase Requisition
- Document Total: $100
- Account Range: 01.000.000.00.000 to 01.123.999.99.999

Approval Group: Buyer
- Document Type: Purchase Requisition
- Document Total: $1,000
- Account Range: 01.000.000.00.000 to 01.456.999.99.999

Approval Group: Sr Buyer
- Document Type: Purchase Requisition
- Document Total: $10,000
- Account Range: 01.000.000.00.000 to 01.789.999.99.999

Approval Group: Director
- Document Type: Purchase Requisition
- Document Total: $100,000
- Account Range: 01.000.000.00.000 to 01.999.999.99.999

Approval Hierarchy
Requestor => Buyer => Sr Buyer => Director

Documents

Doc A:
- Document Type: Purchase Requisition
- Document Total: $950
- Charge Account: 01.500.123.12.123

Routing: Requestor => Buyer => Sr Buyer
Reason: Even though the dollar amount was within the limit of Buyer level, the Charge Account was out of range for the Buyer approval group. The Requisition would route to the Sr Buyer approval group level, because the change account would now be within the Account Range.

Doc B:
- Document Type: Purchase Requisition
- Document Total: $5,750
- Charge Account: 01.100.000.00.000
Routing: Requestor => Buyer => Sr Buyer
Reason: Even though the Charge Account is within the approval limits of the Requestor approval group, the Document Total is greater then the approval limits given the Requestor and Buyer approval groups. The Document Total is within the Document Total Limits for the Sr Buyer approval group.

Doc C:
- Document Type: Purchase Requisition
- Document Total: $9,850
- Charge Account: 01.850.123.01.000

Routing: Requestor => Buyer => Sr Buyer => Director
Reason: Even though the Document Total is within the limits of the Sr Buyer approval group, the Charge Account is out of range for the Sr Buyer approval group, but is within range for the Director approval group.

Forwarding Method:
There are two types of Forwarding methods within Oracle Purchasing.
The Forwarding Method selection is setup under the Document Types setup screen,
for the specific desired selected document.

Navigation
• Select Purchasing Responsibility – Setup
• Purchasing
• Document Types.

The two methods of forwarding are:

• Direct
• Hierarchy

Direct
The document will pass directly to the next position or supervisor in the hierarchy
who has the correct authority to approve the document.

Hierarchy
The doucment will pass to the next person in the hierarchy regardless of their
approval limits, until finally the level of the user who has the correct authority to
approve the document, is reached. This latter method is often used to allow for
review by business users prior to the Approval to have the opportunity to comment
request modifictions, or reject.
DIAGNOSTICS
The following diagnostic scripts can be used to gain an in depth view of Document Approval Routing for debugging logic issues and routing errors:
Note 243026.1 - 11i - Purchasing(PO) Documents Approval Script
Note 243026.1 - 11i - Bug_POCheckSetup115.sql (Over all purchasing application setup)
Note 243026.1 - 11i - Bug_POUserApprovalCheckSetup115.sql (Purchasing user setup)
Note 243026.1 - 11i - Bug_POEmployeeCheckSetup115.sql (Employee setup details)
Note 243026.1 - 11i - Bug_PODocumentApprovalStatus115.sql (Doc approval routing)
Note 243026.1 - 11i - Bug_Appr_POTransactions115.sql (PO lines and header details)
Note 243026.1 - 11i - Bug_POReqCreate115.sql (Requisition lines and header details)
Note 243026.1 - 11i - Bug_Appscheck.sql (Application setup at the table level)
wfver.sql 11i / R12 - (Details of the workflow setup and workflow file version)
wfstat.sql 11i / R12 - (Document approval flow from the database / table level)
PROCESSING
Purchasing lets you approve a number of different document types, using a common process.

The document approval routing within the Oracle Purchasing Application encompasses the progression of the following Document Types:
Purchase Requisition
Internal Requisition
Standard Purchase Order
Planned Purchase Orders
Blanket Purchase Agreements
Global Blanket Agreements
Blanket Releases
Scheduled Releases
Contract Purchase Agreement
Global Contract Agreements
The routing of the major document types are primarily broken out under the following two Workflows:
Purchase Order Approval Workflow (poxwfpoa.wft)
Requisition Approval Workflow (poxwfrqa.wft)
The following Item Types are then covered under each of the two main workflows:
poxwfpoa.wft
- POAPPRV

poxwfrqa.wft
- REQAPPRV
The overall approval routing logic is as follows:
1. Create and complete the desired document for submission
2. Initiate the document approval process
3. If 'Owner Can Approve' is enabled, Document Approval will take place at this level
4. If the document requires an additional user for approval, the document will route as such
5. Regardless of which level the Approval occurs, the Approval Hierarchy is invoked
6. Purchasing offers the following document approval actions:

- Reserve or Unreserve (If using encumbrance / budgetary control)
- Submit for Approval
- Forward
7. Supplier Notification through one of the following options/combinations:

- Print option (Document will automatically be printed once it is approved.)
- Facsimile
- E–mail
- Electronic Data Interchange (EDI)
- Extensibile Markup Language (XML)
8. Purchasing performs submission checks to verify that the document is complete and in an appropriate state for the action chosen.

Processes Overview:
REQAPPRV
AME_APPROVAL_LIST_ROUTING
Approval List Routing Using AME
Handles the document routing for Approvers through the AME Approval List
Handles the notification and response processing for Approvers in the AME Approval List
APPROVAL_LIST_ROUTING
Approval List Routing
Handles the document routing for Approvers through the main Approval List
Handles the notification and response processing for Approvers in the main Approval List
APPROVAL_ROUTING_CHOOSER
Approval Routing Chooser
Handles the decision for which routing is called (Standard Workflow or AME)
Calls: AME_APPROVAL_LIST_ROUTING
Calls: APPROVAL_LIST_ROUTING
APPROVAL_REQ_SUB_PROCESS
Approve Requisition
Handles decision routing at the point IS_DOCUMENT_APPROVE is called
Handles part of the the notification and response processing
Calls: DOC_STATE_CHECK_BEFORE_APP
Calls: DOC_SUBMISSION_CHECK_BEF_APP
Calls: DOC_APPROVE_SUB_PROCESS
BUILD_APPROVAL_LIST_PROCESS
Build Approval List
Checks if a current Approval List exists
Handles the Build Default Approval List decision
CHECK_REQUIRE_APPROVAL_PROCESS
Is Approval Routing Needed?
Checks if Owner Can Approve
Calls: VERIFY_APPROVER_AUTH_PROCESS
DOC_APPROVE_SUB_PROCESS
Document Manager Approve Subprocess
Handles the call to the APPROVE_REQUISITION function
DOC_STATE_CHECK
Document State check Subprocess
Handles one call to the VERIFY_STATE_CHECK_APPROVE function
DOC_STATE_CHECK_BEFORE_APP
Document State Check Before Approve Subprocess
Handles one call to the VERIFY_STATE_CHECK_APPROVE function
MAIN_REQAPPRV_PROCESS
Main Requisition Approval
Handles the primary calls and flow for Requisition Approval
Calls: START_OF_APPROVE_REQ
Calls: VERIFY_REQUISITION
Calls: BUILD_APPROVAL_LIST_PROCESS
Calls: CHECK_REQUIRES_APPROVAL_PROCESS
Calls: APPROVAL_ROTUING_CHOOSER
Calls: REJECT_REQUISITION_PROCESS
Calls: APPROVE_REQ_SUB_PROCESS
NOTIFY_APPROVER_CHOOSER
Notify Approver Chooser
Handles the call to IS_FORWARD_ACTION_ALLOWED function
Calls: NOTIFY_APPROVER_PROCESS
NOTIFY_APPROVER_PROCESS
Notify Approver
Handles the original Approval Notification to and from the Approver
Handles the Notification Reminder call
NOTIFY_APPROVER_PROCESS_SIMPLE
Notify Approver (Simplified)
Handles the call to INSERT_ACTION_HISTORY functioi>
NOTIFY_APPROVER_SIMPLE_AME
Notify Approver (Simplified) In AME
Handles the call to INSERT_ACTION_HISTORY function for AME routing
NOTIFY_RETURN_REQ
Notification for Requisition Returned By Buyer
Handles the call to GET_REQUISITION_DATA function
REJECT_REQUISITION_PROCESS
Reject Requisition
Handles the call to REJECT_REQUISITION
RESPONSE_APPROVE
Response with Approve Action
Handles the actual Approve reply action by the Approver
Calls: VERIFY_APPROVER_AUTH_APPROVE
RESPONSE_APPROVE_AME
Response with Approve Action Using AME
Handles the call to UPDATE_APPROVAL_RESPONSE_AME function
Handles the call to UPDATE_ACTION_HISTORY_APPROVE function
RESPONSE_APPROVE_FORWARD
Response with Approve and Forward Action
Handles the actual Approve and Forward reply action by the Approver
Calls: VERIFY_APPROVER_AUTH_APPROVE
RESPONSE_FORWARD
Response with Forward Action
Handles the call to UPDATE_APPR_LIST_RESP_FWD function
Handles the call to UPDATE_ACTION_HISTORY_FORWARD function
RESPONSE_REJECT
Response with Reject Action
Handles the call to UPDATE_APPR_LIST_RESP_REJECT function
Handles the call to UPDATE_ACTION_HISTORY_REJECT function
RESPONSE_REJECT_AME
Response with Approve Action Using AME
Handles the call to UPDATE_APPROVAL_RESPONSE_AME function
Handles the call to UPDATE_ACTION_HISTORY_APPROVE function
START_OF_APPROVE_REQ
Start Of Approve Requisition Process
Handles the call to IS_AME_USED_FOR_APPROVAL function
Handles the call to FIND_APPROVAL_LIST function
Handles the call to GET_WORKFLOW_APPROVAL_MODE function
VERIFY_APPROVER_AUTH_APP_FWD
Verify Approval Authority for Approve and Forward Action
Handles the call to IS APPROVER THE PREPARER? function
Handles the call to VERIFY_APPROVER_AUTHORITY function
Handles the call to CAN_OWNER_APPROVE function
VERIFY_APPROVER_AUTH_APPROVE
Verify Approval Authority for Approve Action
Handles the call to IS APPROVER THE PREPARER? function
Handles the call to VERIFY_APPROVER_AUTHORITY function
Handles the call to CAN_OWNER_APPROVE function
VERIFY_APPROVER_AUTH_PROCESS
Verify Approval Authority
Handles the call to VERIFY_APPROVER_AUTHORITY function
VERIFY_REQUISITION
Verify Requisition
Calls: DOC_STATE_CHECK

Main Requisition Approval



POAPPRV
APPROVE_AND_FORWARD_PO
Approve And Forward PO
Calls: DOC_STATE_CHECK_APP_FWD
Calls: DOC_COMLETE_CHECK_APP_FWD
Handles the verification before Notification send for Approve and Forward action
APPROVE_PO_SUB_PROCESS
Approve PO
Calls: DOC_STATE_CHECK_BEFORE_APP
Calls: DOC_COMPLETE_CHECK_BEFORE_APP
Handles the verification before Notification and Communication send
APPROVE_PO_SUB_PROCESS_CO
Approve PO (Change Order)
Calls: DOC_STATE_CHECK_BEFORE_APP_CO
Calls: DOC_COMP_CHECK_BEFORE_APP_CO
Handles the verification before Notification and Communication send for Change Order
CREATE_SR_AND_ASL
Create SR and ASL
Handles the call to CREATE_SOURCING_RULES function
Handles the Sourcing Rule and Approved Supplier List build for PO Approval
DOC_COMP_CHECK_BEFORE_APP_CO
Doc Complete Check Before Approve Subprocess (Change Order)
Handles the call to SUBMISSION_CHECK function
Handles the CO document check validation before Approval Subprocess call
DOC_COMPLETE_CHECK
Document Complete Check Subprocess
Handles the call to SUBMISSION_CHECK function
Handles the document check validation
DOC_COMPLETE_CHECK_APP_FWD
Document Complete Check Before Approve & Forward Subprocess
Handles the call to SUBMISSION_CHECK function
Handles the document check validation before Approve & Forward Subprocess
DOC_COMP_CHECK_BEFORE_APP
Document Complete Check Before Approve Subprocess
Handles the call to SUBMISSION_CHECK function
Handles the document check validation before Approval Subprocess call
DOC_RESERVE_PROCESS
Document Reserve Subprocess
Handles the call to RESERVE DOCUMENT function
Handles the Reserve action for call to Accounting Encumbrance
DOC_RESERVE_PROCESS_CO
Document Reserve Subprocess (Change Order)
Handles the call to RESERVE DOCUMENT function
Handles the Reserve action for Change Order
DOC_STATE_CHECK
Document State Check Subprocess
Handles the call to VERIFY_STATE_CHECK_APPROVE function
Handles document state check for PO Approval
DOC_STATE_CHECK_FWD
Document State Check Before Approve & Forward Subprocess
Handles the call to VERIFY_STATE_CHECK_APPROVE function
Handles Document State Check prior to calling the Approve & Forward subprocess
DOC_STATE_CHECK_BEFORE_APP
Document State Check Before Approve Subprocess
Handles the call to VERIFY_STATE_CHECK_APPROVE function
Handles Document State Check prior to calling the Approve subprocess
DOC_STATE_CHECK_BEFORE_APP_CO
Document State Check Before Approve Subprocess (Change Order)
Handles the call to VERIFY_STATE_CHECK_APPROVE function
Handles Document State Check for CO prior to calling the Approve subprocess
FIND_APPROVER
Find Approver
Calls: VERIFY_APP_AUTH_FWD_NO_HIER
Calls: VERIFY_APP_AUTH_FWD_HIER
Handles the call to FOWARD_TO_PROVIDED function
Handles the call to GET_APPROVAL_PATH_ID function
Handles the call to FORWARDING_MODE function
Handles the call to USE_POSITION_FLAG function
Handles the securing of the Approver for PO document actions
FORWARD_PO
Forward PO
Handles the call to IS_PO_PRE-APPROVED-1 function
Handles the call to FORWARD_PO_INPROCESS function
Handles the call to FORWARD_PO_PREAPPROVED-1 function
Handles the call to GET_PO_DATA function
Handles the call to GET_PO_DATA-1 function
Handles the Forward action for Purchase Order Approval
GET_ALL_CHANGES
Get All Document Changes
Calls: GET_CONTRACT_PO_CHANGES
Calls: GET_BLANKET_PO_CHANGES
Calls: GET_PLANNED_PO_CHANGES
Calls: GET_STANDARD_PO_CHANGES
Calls: GET_REL_CHANGES
GET_BLANKET_PO_CHANGES
Get All Blanket PO Changes
Handles the call to GET_HEADER_CHANGES function
Handles the call to GET_LINE_CHANGES function
Handles the call to GET_SHIPMENTS_CHANGES function
GET_CONTRACT_PO_CHANGES
Get All Contract PO Changes
Handles the call to GET_HEADER_CHANGES function
GET_PLANNED_PO_CHANGES
Get All Planned PO Changes
Handles the call to GET_HEADER_CHANGES function
Handles the call to GET_LINE_CHANGES function
Handles the call to GET_SHIPMENTS_CHANGES function
Handles the call to GET_DIST_CHANGES function
GET_REL_CHANGES
Get All Release Changes
Handles the call to GET_RELEASE_CHANGES function
Handles the call to GET_SHIPMENTS_CHANGES function
Handles the call to GET_DIST_CHANGES function
GET_STANDARD_PO
Get All Standard PO Changes
Handles the call to GET_HEADER_CHANGES function
Handles the call to GET_LINE_CHANGES function
Handles the call to GET_SHIPMENT_CHANGES function
Handles the call to GET_DIST_CHANGES function
MAIN_POAPPRV_PROCESS
PO Approval Process
Calls: VERIFY_PO
Calls: FIND_APPROVER
Calls: RETURN_PO
Calls: FORWARD_PO
Calls: VERIFIY_APPROVER_AUTH_PROC
Calls: NOTIFY_APPROVER_PROCESS-1
Calls: APPROVE_AND_FORWARD_PO
Calls: REJECT_PO_PROCESS
Calls: RESERVE_BEFORE_APPROVE-1
Calls: RAISE_CHANGE_EVENT3
Calls: APPROVE_PO_SUB_PROCESS
Calls: RAISE_CHANGE_EVENT2
Calls: RAISE_SEND_PO_EVENT
Calls: CREATE_SR_AND_ASL
Handles the call to CAN_OWNER_APPROVE function
Handles the call to IS_FORWARD_TO_USER_NAME_VALID function
Handles the call to IS APPROVER ALSO THE PREPARER function
Handles the call to IS_PO_PRE_APPROVED function
Handles the call to FORWARD_TO_PROVIDED function
Handles the call to SET_PO_STAT_TO_PREAPPROVED
Handles the call to DOES_USER_WANT_SR_ASL_CREATED function
NOTIFY_APPROVER_PROCESS
Notify Approver
Calls: NOTIFY_APPROVER_SUBPROCESS
Handles the call to PO_NEW_COMMUNICATION function
Handles the call to GET_APP_NOTIFICATION_ATTRIBUTE function
Handles the call to IS_FORWARD_TO_VALID function
Handles the build and sent of the notification action to the Approver
NOTIFY_APPROVER_SUBPROCESS
Notify Approver Subprocess
Handles the call to PO_NEW_COMMUNICATION function
Handles the subprocess for the build and sent of the notification action to the Approver

Main Purchase Order Approval




TROUBLESHOOTING
The following section provides some details on diagnosing some common issues in the Purchasing approval processing
1) Document Approval Logic:
Issue 1
Document remains in Incomplete status after Approval

Cause 1A
The creator of the document is not part of a valid approval hierarchy, and does not have the limits to make the approval. The user needs to be associated to a valid Approval Hierarchy that address the approval limits for the specific document type. If employee / supervisor relation is being used for the document approval routing, then the needed assignments and association for each approval level will need to be done through the Enter Person form for each sequential employee/supervisor. For details on assigning employees, refer to Oracle Purchasing User’s Guide Release 11i (Part No. A82913–06) in 11.5.10 or Oracle Purchasing User's Guide Release 12 (Part No. B28669-01)

Cause 1B
The creator is part of a valid hierarchy, but there is not a level within the hierarchy that covers the elements on the document. The main culprit for this is the Change Account. Verify that for each of the limits under each level, that the number of digits for each segment in the account ranges matches that of the Charge Account. If the segment count appears to be correct, perform a quick test, and move the top of the account for the last level to be ‘Z’ for each digit. (Ex: ZZ.ZZZ.ZZ.ZZZZ.ZZZZ) If the resubmit of the approval now moves through without issue, then it is known that the cause was the Account Range, and new limits just need to be set for the each level.

Issue 2
Document rolls-up to the next approval level, when it should be approved by the owner.

Cause 2A
The assigned approval limits for the owner/creator are not broad or high enough to allow for document approval at the owner level. Review the approval limits and conditions assigned to the owner, and match each against a document that rolled-up to the next level.

Cause 2B
Even though the owner/creator has the correct approval limits for the document approval, the “Owner Can Approve” option under the document type setup has not been set to “Y”. For an owner/creator to be able to approve their own documents, this setting must be defined as “Y”.

Issue 3
Unable to open Requisitions or Purchase Orders from the document summary, for approval.

Cause 3A
The document is in either In Process or Pre-Approved status. A majority of the time, there is an open notification against either the Requisition or Purchase Order. The following query set can be used to determine the current user the notification action is waiting for:

Requisition:
select reqh.segment1 req, wfn.status notify_status, wias.notification_id notify_id,
wfn.to_user name, reqh.org_id org from wf_item_activity_statuses wias, wf_notifications wfn, po_requisition_headers_all reqh
where wias.notification_id is not null and wias.notification_id = wfn.group_id
and wfn.status = 'OPEN' and wias.item_type = 'REQAPPRV'
and wias.item_key = reqh.wf_item_key and reqh.authorization_status = ‘&Req_Number’ order by 5,1,3;
Purchase Order:
select poh.segment1 po, wfn.status notify_status, wias.notification_id notify_id,
wfn.to_user name, poh.org_id org from wf_item_activity_statuses wias,
wf_notifications wfn, po_headers_all poh where wias.notification_id is not null
and wias.notification_id = wfn.group_id and wfn.status = 'OPEN'
and wias.item_type = 'POAPPRV' and wias.item_key = poh.wf_item_key and poh.segment1 = ‘&PO_Number’ order by 4,5,1,3;
Cause 3B
The Purchase Order is in In Process or Pre-Approved status, but does not have any Workflow data. The most common cause for this is that there is an Open Change request against the Purchase Order/Requisition combination. Because the Purge Obsolete Workflow Process may have been run against the document between the last time the Approval took place and the Change Request was raised, the WF data would be missing. The correct action in this case is to have the Buyer, which the Change Request is waiting on, reply to the notification to either except or reject the change. The following query can be used to review the ties between the Purchase Order, Requisition and Change Request.

Purchase Order:
select ref_po_num PO_Num, ref_po_rel_num Rel_Num, document_num Req_Num,
change_request_id CR_Id, change_request_group_id CR_Grp_ID,
last_update_date, document_line_number Doc_Line_Num, action_type,
request_reason, request_status status from po_change_requests where ref_po_header_id =
(select po_header_id from po_headers_all where segment1 = '&PO_NUMBER' and org_id = '&ORG_ID')
and document_type = 'REQ' order by last_update_date
Requisition:
select ref_po_num PO_Num, ref_po_rel_num Rel_Num, document_num Req_Num,
change_request_id CR_Id, change_request_group_id CR_Grp_ID,
last_update_date, document_line_number Doc_Line_Num, action_type,
request_reason, request_status status from po_change_requests where document_header_id =
(select requisition_header_id from po_requisition_headers_all where segment1 = '&Req_NUMBER'
and org_id = '&ORG_ID') and document_type = 'REQ' order by last_update_date
2) Document Approval Errors:
Issue 1
Document remains in Incomplete status with “No Approver Found” message

- The Creator of the document is not part of a Hierarchy that has the needed limits.
Note 290365.1 - Troubleshooting Document For Build Default Approval List Failure

- The intended Approval Group is configured incorrectlsp; Note 297576.1 - Document Approval is Returned with a Notification Saying No Approver...

- One or more employees in the hierarchy are not valid or fully setup-up
Note 302955.1 - 11.5.10 Purchase Order Status Changes To Incomplete After Approval

- The owner is not able to complete the required approval.
Note 265903.1 - Approving Purchasing Document Stuck Incomplete

Issue 2
Document remains in Incomplete status with “Has No Performer” error

- Purchase Order fails with error for EDI enabled Supplier.
Note 356636.1 - POAPPRV WORKFLOW ARE FAILING: 3120: ACTIVITY...

- Requisition fails with error for Shared HR Install.
Note 337157.1 - 11.5.10: 3120: Activity 'REQAPPRV' Has No Performer - Errors...

Issue 3
Document approval fails at “Does Approver Have Authority” Activity

- The document fails with RESPONDER_APPL_ID & RESPONDER_RESP_ID -1.
Note 392202.1 - 11.5.10.2: Document Manager Error #3 In VERIFY_AUTHORITY...

- The document fails with POXDMACTION-100.
Note 394328.1 - Document Manager Error #3 POXDMACTION-100: ORA-00054

- The document fails with Error Number 3 message.
Note 392977.1 - Document Manager Failed With Error Number 3 - Dead Process

- The document fails with POXDM-210, POACA-870 or POACA-790 error.
Note 554284.1 - Purchase Orders Stuck in Process and Fail With POXDM-210...

- The document fails with RAC setup after ATG PF.H RUP3.
Note 376556.1 - 11.5.10: Doc Mgr Error #3 in VERIFY_APPROVER_AUTHORITY


Issue 4
Document approval fails with “OPEN_DOC_STATE” error

- The document fails with RAC setup.
Note 309578.1 - 11.5.10 - Document Approval Manager Error #3 in OPEN_DOC_STATE...

- The document fails with RESPONDER_APPL_ID & RESPONDER_RESP_ID -1.
Note 364356.1 - Document Manager Error #3 in Open Document State #EXCEPTION...

- The document fails during CREATEPO Workflow call.
Note 369964.1 - Open Doc State Error During Purchase Order Approval, For PO's Created...

- The document fails after Patch 5389914 application.
Note 428043.1 - After Applying Patch 5389914 Context Switching Issues Still Occur

- Complete overview of current causes.
Note 387468.1 - Troubleshooting Purchasing Documents for Failure on Approval with...

TRACING
The following steps can be used to trace the sql statements used during the approval process.
1. Enable the tracing for the Purchasing Application:

- Enter the Core Application as the System Administrator responsibility
- Select "Profile" => "System"
- Select the "Application" check-box
- Input "Purchasing" as the value for the Application
- Set Profile Option "Initialization SQL Statement - Custom" with the following update;
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;
Note: The SQL Statement should be made against the custom profile at the user level. Ensure that the statement is entered as one complete line without any carriage returns, paying close attention to supplying a valid identifier within the line's text.
2. Upon the profile being set, immediately navigate into a Purchasing responsibility, and replicate the issue for which the trace is desired. The goal is to only capture those database entries related to the issue being seen.

3. Locate the newly generated trace through the following steps:
- Use the following SQL statement to locate the dump destination:
select name, value from v$parameter where name like 'user_dump_dest';

- The returned path is the location of the trace file on the DB Server
- Search within this directory on the identifier selected in the earlier statement

4. Tkprof the located trace file to have both a RAW and Tkprof result

tkprof .trc .out explain=

5. Turn of the earlier set profile within the application, to stop the tracing. This will prevent generation of additional trace files.

LOGGING
Debug logging can be enabled at the Document Manager Level to show activities done during processing for deeper analysis. This is done using the following steps;
1. Set the following profile options at the Oracle Purchasing Application level.
Set "PO: Set Debug Workflow ON" to "Yes"
Set "PO: Set Debug Concurrent ON " to "Yes"
2. Set the number of Document Manager processes to 1
3. Clear the debug table by deleting the contents of po_wf_debug table
4. Bounce the Document Manager process.
5. Reproduce the issue.
6. Query the po_wf_debug table so find the log lines written.
7. Revert the profile option settings:
8. Bounce the Document Approval Manager to update the setting changes.

DATA AND QUERIES
The following queries can be used to validate the data that is part of the approval processing.
Purchase Order Status:
select segment1 po#, revision_num r#,
substr(type_lookup_code,1,4) type, authorization_status auth_status,
closed_code, wf_item_type, wf_item_key, org_id
from po_headers_all
where segment1 = '&PO_NUMBER'


Requisition Status:
select segment1 req#,authorization_status auth_status,
closed_code, wf_item_type, wf_item_key, org_id
from po_requisition_headers_all
where segment1 = '&REQ_NUM'
Release Status:
select po.segment1 po#, rel.release_num rel#, po.revision_num po_r#,
po.authorization_status po_status, po.closed_code close_po,
po.wf_item_type po_type, po.wf_item_key po_key, po.org_id org,
rel.wf_item_type rel_type, rel.wf_item_key rel_key,
rel.po_release_id rel_id, rel.authorization_status rel_status, rel.hold_flag hold
from po_headers_all po, po_releases_all rel
where po.po_header_id = rel.po_header_id
and po.segment1 = '&PO_NUM'
and rel.release_num = 'REL_NUM'
order by 1,2

Purchase Order Action History:
select poah.sequence_num seq#, poah.action_date, poah.action_code,
poah.employee_id emp_id, fnd.user_name, substr(poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type,
poah.object_revision_num rev, pohead.org_id
from po_action_history poah, fnd_user fnd, po_headers_all pohead
where poah.object_id = pohead.po_header_id
and pohead.segment1 = '&PO_NUMBER'
and pohead.org_id = '&ORG_ID'
and substr(poah.object_type_code,1,3) = 'PO'
and poah.employee_id = fnd.employee_id
and fnd.session_number != 0
order by 2,1

Requisition Action History:
select poah.sequence_num seq#, poah.action_date, poah.action_code,
poah.employee_id emp_id, fnd.user_name, substr (poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type,
poah.object_revision_num rev, pohead.org_id, poah.note
from po_action_history poah, fnd_user fnd, po_requisition_headers_all pohead
where poah.object_id = pohead.requisition_header_id
and pohead.segment1 = '&REQ_NUMBER'
and substr(poah.object_type_code,1,3) = 'REQ'
and pohead.org_id = '&ORG_ID'
and poah.employee_id = fnd.employee_id
and fnd.session_number != 0
order by 9,2,1

Position Attached to Username:
SELECT pos.name position_name, pa.position_id,fnd.user_name
FROM PER_ALL_ASSIGNMENTS_F pa, per_positions pos,per_jobs job, fnd_user fnd
WHERE pa.POSITION_ID = pos.POSITION_ID
and pa.job_ID = job.job_id
and sysdate between pa.EFFECTIVE_START_DATE
and pa.EFFECTIVE_END_DATE
and pa.primary_flag = 'Y'
and pa.assignment_type = 'E'
and pa.person_id = fnd.employee_id
and pa.PERSON_ID = (select employee_id
from fnd_user
where user_name = '&user_name'

CODE FILES
The following code files can be used to compare the file versions in the problematic instance to those available in later patches, for possible fixes. This can be done by searching on Metalink, under Patches, searching by file name (included in).
Type Filename Description
Workflow poxwfrqa.wft Requisition Approval Workflow file
Workflow poxwfpoa.wft Purchase Order Approval Workflow file
Workflow poxwfatc.wft CREATEPO Workflow file
Package POXWPA1B.pls PO_REQAPPROVAL_INIT1 - Base Requisition Approval
Package POXWPA2B.pls PO_POAPPROVAL_INIT1 - Base Purchase Order Approval
Package POXWPA3B.pls PO_REQAPPROVAL_FINDAPPRV1 - Find Approver for Reqs
Package POXWPA4B.pls PO_REQAPPROVAL_ACTION - Req Approval Action & History
Package POXWPA5B.pls PO_REQAPPROVAL_LAUNCH - Start of Req Approval Process
Package POXWPA9B.pls PO_APPROVAL_ACTION - PO Approval Action and History