PO approval query in oracle apps

PO approval query in oracle apps

In this post , We will be discuss about the PO approval query in oracle apps. PO approval query will help to extract the Purchase order approval action history means we will get the list of all approvers who have approved the purchase order and in which sequence with approved dates also. This query helps to back track the PO approval process and can verify the PO approval history informations in oracle apps r12. This is one of the important part in Approval audits. Here below i will try to share the table for the PO approval query and then later on will share the PO approval SQL query too.

Important Tables of PO approval query in oracle apps

1.po_action_history

2.po_headers_all

Detail PO approval query in Oracle Apps r12

SELECT   pah.sequence_num step
       , pah.action_code

       , pah.object_id

       , pah.action_date

       , pah.creation_date

       , pha.segment1 po_num

       , fu.description

       , papf.full_name hr_full_name

       , papf.employee_number emp_no

       , papf.person_id

       , fu.user_name

       , pj. NAME job

    FROM po.po_action_history pah

       , po.po_headers_all pha

       , applsys.fnd_user fu

       , hr.per_all_people_f papf

       , hr.per_all_assignments_f paaf

       , hr.per_jobs pj

   WHERE object_id = pha.po_header_id

     AND pah.employee_id = fu.employee_id

     AND fu.employee_id = papf.person_id

     AND papf.person_id = paaf.person_id

     AND paaf.job_id = pj.job_id

     AND pha.segment1=:P_PO_NUMBER

     AND paaf.primary_flag = 'Y'

     AND papf.effective_end_date >=sysdate

     AND paaf.effective_end_date >= sysdate

     AND pah.object_type_code = 'PO'

     AND pah.action_code = 'APPROVE'