Oracle EBS SQL Scripts for data extracts

 SQL Script to display the REQUISITION Approver details:-

select

        a.SEGMENT1           ,

        b.creation_date        ,

        b.ACTION_DATE    ,

        b.employee_id          ,

        b.ACTION_CODE    ,

        max(SEQUENCE_NUM),

        c.full_name

from

        po_requisition_headers_all a ,

        po_action_history                  b ,

        per_all_people_f                    c

where

        OBJECT_TYPE_CODE = 'REQUISITION'

and     AUTHORIZATION_STATUS in ( 'APPROVED' ) 

and     a.REQUISITION_HEADER_ID = b.OBJECT_ID

and     b.employee_id  = c.person_id

and     a.SEGMENT1 = '&REQ'          -- Enter Req No.

and     a.ORG_ID   = &ORG               -- Enter Org

GROUP BY

        a.SEGMENT1        ,

        b.creation_date     ,

        b.ACTION_DATE ,

        b.employee_id       ,

        b.ACTION_CODE ,

        c.full_name

order by

        b.creation_date desc;

SQL Script to display the Purchase Order Approver details:-

SELECT

        ph.segment1                  AS po_number,

        ph.creation_date          AS po_creation_date,

        ph.approved_date        AS po_approved_date,

        pah.action_code           AS po_action_code,

        pah.action_date           AS po_action_date,

        fu.user_name               AS approver_username ,

        ppf.full_name              AS approver_name

FROM

        po_headers_all ph

JOIN

        po_action_history pah

ON

        ph.po_header_id = pah.object_id

JOIN

        fnd_user fu

ON

        pah.employee_id = fu.employee_id

LEFT JOIN

        per_people_f ppf

ON

        fu.employee_id = ppf.person_id

WHERE

        pah.object_type_code = 'PO'

AND     pah.action_code     = 'APPROVE' -- Filter for approval actions

AND     ph.segment1             = '&PO'           -- Replace with your desired PO number

ORDER BY

        pah.action_date DESC;

Notes:-

In Oracle Apps PO_ACTION_HISTORY table, OBJECT_TYPE_CODE identifies the specific type of purchasing document involved in an approval/workflow action.

Example:

      A text field indicating the document category.

                'PO'               : Purchase Order.

                'REQ'            : Requisition.

                'RELEASE'  : Blanket Release.

                'PA'               : BPA