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
Last updated on FEBRUARY 14, 2017
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.
Checked for relevance: 04-Feb-2016
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 :
from pa_project_players a,
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:
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms