Select statement to derive all the POs, Reqs delegated by user/s with WF Admin privileges in Oracle EBS

-------------------------------------------------------------------------------------------------------------------------------------

--Query to fetch all the POs,Requisitions delegated by User with WF Admin privileges

--Parameters:

-- p_po_or_req_num -- Input PO/Requisition Number (Optional parameter)

-- p_delegated_by_racfid -- Input RACFID of person who delegated the PO/Requisitions (Optional parameter)

-- p_delegate_from_date -- Input date to fetch records on/after the input date(Defaults to 03/Aug/2014 if left blank)

-------------------------------------------------------------------------------------------------------------------------------------

 

SELECT   papf2.full_name, pah3.sequence_num seq, pah3.action_code,

         pha.segment1, pah3.object_type_code, pah3.note,

         pah3.last_update_date, fu.user_name delegated_by,

         fu.description delegated_by_person, fu1.user_name delegated_from,

         fu1.description delegated_from_person, fu.employee_id,

         pah3.employee_id, prh.wf_item_key

    FROM hr.per_all_people_f papf2,

         po.po_action_history pah3,

         po.po_headers_all pha,

         fnd_user fu,

         fnd_user fu1,

         po_requisition_headers_all prh

   WHERE pah3.employee_id = papf2.person_id

     AND pah3.last_updated_by = fu.user_id

     AND pah3.object_id = pha.po_header_id

     AND pah3.employee_id = fu1.employee_id

     AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date

                             AND papf2.effective_end_date

     AND pah3.action_code = 'DELEGATE'

     AND TRUNC (pah3.action_date) >= NVL(:p_delegate_from_date,'01-JAN-1951')

     AND pah3.object_type_code = 'REQUISITION'

     AND pah3.object_id = prh.requisition_header_id

     AND fu.user_name = NVL (:p_delegated_by_racfid, fu.user_name)

     AND prh.segment1 = NVL (:p_po_or_req_num, prh.segment1)

UNION

SELECT   papf2.full_name, pah3.sequence_num seq, pah3.action_code,

         pha.segment1, pah3.object_type_code, pah3.note,

         pah3.last_update_date, fu.user_name delegated_by,

         fu.description delegated_by_person, fu1.user_name delegated_from,

         fu1.description delegated_from_person, fu.employee_id,

         pah3.employee_id, prh.wf_item_key

    FROM hr.per_all_people_f papf2,

         po.po_action_history pah3,

         po.po_headers_all pha,

         fnd_user fu,

         fnd_user fu1,

         po_headers_all prh

   WHERE pah3.employee_id = papf2.person_id

     AND pah3.last_updated_by = fu.user_id

     AND pah3.object_id = pha.po_header_id

     AND pah3.employee_id = fu1.employee_id

     AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date

                             AND papf2.effective_end_date

     AND pah3.action_code = 'DELEGATE'

     AND TRUNC (pah3.action_date) >= NVL(:p_delegate_from_date,'01-JAN-1951')

     AND pah3.object_type_code = 'PO'

     AND pah3.object_id = prh.po_header_id

     AND prh.segment1 = NVL (:p_po_or_req_num, prh.segment1)

     AND fu.user_name = NVL (:p_delegated_by_racfid, fu.user_name)

ORDER BY 5 DESC, 7 DESC;