My Oracle Support Banner

Dynamic SQL Query With po_ame_setup_pvt.get_new_req_header_id(:transactionId) In AME For Requisition Approval Does Not Build The Approval List (Doc ID 406960.1)

Last updated on DECEMBER 03, 2019

Applies to:

Oracle iProcurement - Version 11.5.10 to 12.2 [Release 11.5 to 12.2]
Information in this document applies to any platform.

Symptoms

An AME approval group based on dynamic query using the procedure call po_ame_setup_pvt.get_new_req_header_id(:transactionId) does not build the approval list for purchase requisition approval.  :transactionId = po_requisition_headers_all.requisition_header_id.

Due to this issue, Approval Groups built using the Dynamic method are not working. However, Static approval groups work fine.


DYNAMIC QUERY used for the Approval Group :

Select a.PERSON_ID
from pa_project_players a,
po_req_distributions_all b,
po_requisition_headers_all h,
po_requisition_lines_all l
where a.project_role_type = 'PROJECT MANAGER'
and a.end_date_active is null
and a.PROJECT_ID = b.project_id
and h.REQUISITION_HEADER_ID = l.REQUSITIION_HEADER_ID
and l.REQUISITION_LINE_ID = b.REQUISITION_LINE_ID
and REQUISITION_HEADER_ID = po_ame_setup_pvt.get_new_req_header_id(:transactionId)


Steps To Reproduce
1. Create a new approval group in AME.
-- Specify Dynamic
-- Use the above query to find the approver that should be used in the approval group.
-- Running the query manually from sqlplus finds the correct approver.
2. Create a new AME rule that uses the above approval group and contains the dynamic query.
3. Create a requisition in iProcurement that should use the AME rule described above.
4. User encounters an error:

 

You do not have authority to approve this requisition and an approval list was not built

or

Approval list could not be generated.

or

No error, but approval list or graphical display issues during requisition approval routing


 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.