Tuesday, 5 September 2017

Query to get details for Purchase order (PO) approval History

Query to get details for Purchase order approval History 

SELECT
  pah.action_code ,
  pah.object_id ,
  pah.action_date ,
  pah.sequence_num,
  pah.creation_date ,
  prha.segment1 PO_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_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.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 prha.segment1        ='4010231234'
  --AND pah.action_code = 'APPROVE'
ORDER BY pah.sequence_num;

No comments:

Post a Comment

AP Invoice Notes related Table details in Oracle Fusion

 What is the table that stores the AP Invoice notes? AP notes are stored in ZMM_NOTES table with SOURCE_OBJECT_CODE = 'AP_STANDARD_INVOI...