query of PR (Purchase Requisition Approval Action History)

 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;