Friday, January 29, 2016

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 ANDpapf.effective_end_date
     AND SYSDATE BETWEEN paaf.effective_start_date ANDpaaf.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 ANDpapf.effective_end_date
     AND SYSDATE BETWEEN paaf.effective_start_date ANDpaaf.effective_end_date
     AND pah.object_type_code = 'PO'
     AND pah.action_code = 'APPROVE'
     AND papf.full_name = :pn
ORDER BY pah.sequence_num;

 

No comments:

Post a Comment