Monday, 22 July 2024

Oracle EBS - SQL Query to get the Purchase Requisition Number List which we need to Delegate to other user while employee is on leave

 SELECT

    segment1 pr,
    wf_item_key,
    wf_item_type,
    authorization_status
FROM
    apps.po_requisition_headers_all
WHERE
    authorization_status NOT IN ( 'APPROVED', 'CANCELLED' )
    AND requisition_header_id IN (
        SELECT
            object_id
        FROM
            apps.po_action_history pah, apps.per_all_people_f  ppf, apps.fnd_user          fu
        WHERE
                1 = 1
            AND pah.object_type_code = 'REQUISITION'
            AND pah.action_code IS NULL
            AND pah.employee_id = fu.employee_id
            AND nvl(fu.end_date, sysdate) >= sysdate
            AND fu.employee_id = ppf.person_id
            AND ppf.effective_end_date > sysdate
--and pah.employee_id=18581
            AND ppf.full_name LIKE '%Lien%Fung%' --user full name who is on leave
    );

No comments:

Post a Comment