Wednesday, 27 April 2016

Purchase Requisition Approval History Query in Oracle apps

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;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete