Sunday, April 25, 2021

Query to fetch Purchase Requisition Approval Action History Details in Oracle Apps R12 (PO_ACTION_HISTORY)

 

 

 

 

 

 

 

In this post, we have given a query which helps to derive the approval action history of a Purchase Requisition.

Key Table: PO_ACTION_HISTORY

Instance: R12.2.4

Query

SELECT hou.name ou_name, hou.short_code ou_short_code, prha.segment1 pr_number, (SELECT pah.action_date FROM po_action_history pah WHERE prha.requisition_header_id = pah.object_id AND pah.object_type_code = 'REQUISITION' AND pah.sequence_num = (SELECT MIN(sequence_num) FROM po_action_history pah1 WHERE pah1.action_code = 'SUBMIT' AND prha.requisition_header_id = pah1.object_id AND pah1.object_type_code= 'REQUISITION' ) ) pr_last_submission_date, prha.creation_date, (SELECT prla.catalog_type FROM po_requisition_lines_all prla WHERE prha.requisition_header_id = prla.requisition_header_id AND ROWNUM < 2 ) pr_type, NVL2((SELECT prla.urgent_flag FROM po_requisition_lines_all prla WHERE prha.requisition_header_id = prla.requisition_header_id AND urgent_flag = 'Y' AND ROWNUM < 2 ),'Y','N' ) urgent_flag, auth_status.meaning, authorization_status pr_status, pah.sequence_num action_seq_num, papf.full_name action_person, pah.action_code, pah.action_date, pah.note, prha.change_pending_flag FROM po_requisition_headers_all prha, fnd_lookup_values_vl auth_status, hr_operating_units hou, po_action_history pah, per_all_people_f papf WHERE prha.org_id = hou.organization_id AND prha.requisition_header_id = pah.object_id AND papf.person_id = pah.employee_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND pah.object_type_code = 'REQUISITION' AND auth_status.lookup_type = 'AUTHORIZATION STATUS' AND auth_status.lookup_code = prha.authorization_status;

0 Responses to “Query to fetch Purchase Requisition Approval Action History Details in Oracle Apps R12 (PO_ACTION_HISTORY)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.