PO Approval History Query in Oracle apps

Action History on PO and REQ  

SELECT DISTINCT prha.segment1 req

              , pha.segment1 po

              , gcc.segment3 acct

              , acct_desc.description acct_desc

              , gcc.segment4 ctr

              , ctr_desc.description ctr_desc

              , papf.full_name req_raised_by

              , reqah.full_name req_last_approver

              , poah.full_name po_last_approver

              , pv.vendor_name supplier

              , pvsa.vendor_site_code site

           FROM po.po_requisition_headers_all prha

              , po.po_requisition_lines_all prla

              , po.po_line_locations_all plla

              , po.po_lines_all pla

              , po.po_headers_all pha

              , po.po_distributions_all pda

              , gl.gl_code_combinations gcc

              , apps.po_vendors pv

              , apps.po_vendor_sites_all pvsa

              , apps.fnd_flex_values_vl acct_desc

              , apps.fnd_flex_values_vl ctr_desc

              , hr.per_all_people_f papf

              , (SELECT papf.full_name

                      , pah.action_code

                      , pah.object_id

                   FROM po.po_action_history pah

                      , po.po_requisition_headers_all prha

                      , applsys.fnd_user fu

                      , hr.per_all_people_f papf

                  WHERE object_id = prha.requisition_header_id

                    AND pah.employee_id = fu.employee_id

                    AND fu.employee_id = papf.person_id

                    AND SYSDATE BETWEEN papf.effective_start_date

                                    AND papf.effective_end_date

                    AND pah.object_type_code = 'REQUISITION'

                    AND pah.action_code = 'APPROVE'

                    AND pah.sequence_num =

                           (SELECT MAX(sequence_num)

                              FROM po.po_action_history pah1

                             WHERE pah1.object_id = pah.object_id

                               AND pah1.object_type_code = 'REQUISITION'

                               AND pah1.action_code = 'APPROVE')) reqah

              , (SELECT papf.full_name

                      , pah.action_code

                      , pah.object_id

                   FROM po.po_action_history pah

                      , po.po_headers_all pha

                      , applsys.fnd_user fu

                      , hr.per_all_people_f papf

                  WHERE object_id = pha.po_header_id

                    AND pah.employee_id = fu.employee_id

                    AND fu.employee_id = papf.person_id

                    AND SYSDATE BETWEEN papf.effective_start_date

                                    AND papf.effective_end_date

                    AND pah.object_type_code = 'PO'

                    AND pah.action_code = 'APPROVE'

                    AND pah.sequence_num =

                           (SELECT MAX(sequence_num)

                              FROM po.po_action_history pah1

                             WHERE pah1.object_id = pah.object_id

                               AND pah1.object_type_code = 'PO'

                               AND pah1.action_code = 'APPROVE')) poah

          WHERE prha.requisition_header_id = prla.requisition_header_id

            AND prla.line_location_id = plla.line_location_id

            AND plla.po_header_id = pla.po_header_id

            AND pla.po_header_id = pha.po_header_id

            AND pla.po_line_id = pda.po_line_id

            AND pda.code_combination_id = gcc.code_combination_id

            AND reqah.object_id = prha.requisition_header_id

            AND poah.object_id = pha.po_header_id

            AND prha.preparer_id = papf.person_id

            AND gcc.segment3 = acct_desc.flex_value

            AND gcc.segment4 = ctr_desc.flex_value

            AND pha.vendor_id = pv.vendor_id

            AND pha.vendor_site_id = pvsa.vendor_site_id

            AND pv.vendor_id = pvsa.vendor_id

            AND SYSDATE BETWEEN papf.effective_start_date

                            AND papf.effective_end_date

            AND prha.creation_date >= '01-APR-2009'

            AND prha.creation_date <= '03-APR-2009'

       ORDER BY prha.segment1

              , gcc.segment4

 

Checking Requisition Approval History for a specific member of staff

SELECT   pah.action_code

       , pah.object_id

       , pah.action_date

       , pah.sequence_num step

       , pah.creation_date

       , prha.segment1 req_num

       , prha.wf_item_key

       , prha.authorization_status

       , fu.description

       , papf.full_name hr_full_name

       , papf.employee_number emp_no

       , pj.NAME job

    FROM po.po_action_history pah

       , po.po_requisition_headers_all prha

       , applsys.fnd_user fu

       , hr.per_all_people_f papf

       , hr.per_all_assignments_f paaf

       , hr.per_jobs pj

   WHERE object_id = prha.requisition_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 paaf.primary_flag = 'Y'

     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

     AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

     AND pah.object_type_code = 'REQUISITION'

     AND pah.action_code = 'APPROVE'

     AND papf.full_name = :pn

ORDER BY pah.creation_date desc;

 

Checking Purchase Order Approval History for a specific member of staff

SELECT   pah.action_code

       , pah.object_id

       , pah.action_date

       , pah.sequence_num step

       , 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 paaf.primary_flag = 'Y'

     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

     AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

     AND pah.object_type_code = 'PO'

     AND pah.action_code = 'APPROVE'

     AND papf.full_name = :pn

ORDER BY pah.sequence_num;