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 :
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:
or
or
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 |