PO - Action History

********** PO - Action History **********

  SELECT   pah.object_id,
           pah.object_type_code,
           pah.object_sub_type_code,
           pah.sequence_num,
           pah.last_update_date,
           pah.last_updated_by,
           pah.creation_date,
           pah.created_by,
           pah.action_code,
           plc1.displayed_field Action,
           pah.action_date,
           pah.employee_id,
           hr.full_name Employee,
           pah.approval_path_id,
           pah.note,
           pah.object_revision_num,
           pah.offline_code,
           pah.last_update_login,
           pah.request_id,
           pah.program_application_id,
           pah.program_id,
           pah.program_update_date,
           pah.program_date,
           pah.approval_group_id
    FROM   po_action_history pah,
           po_lookup_codes plc1,
           po_lookup_codes plc2,
           per_all_people_f hr
   WHERE       pah.object_id = 176977  -- po_header_id
           AND pah.object_type_code <> 'RELEASE'
           AND pah.object_sub_type_code = 'STANDARD'
           AND hr.person_id = pah.employee_id
           AND pah.action_date BETWEEN hr.effective_start_date
                                   AND  hr.effective_end_date
           AND plc1.lookup_code(+) = pah.action_code
           AND plc1.lookup_type(+) = 'APPROVER ACTIONS'
           AND plc2.lookup_code(+) = pah.action_code
           AND plc2.lookup_type(+) = 'CONTROL ACTIONS'
ORDER BY   pah.sequence_num


********* PO - Approval Hierarchy *******
SELECT   det.lev "Level",
         det.subordinate_POSITION_ID "Subordinate Position Id",
         det.NAME "Name",
         DECODE (
            (SELECT   COUNT ( * ) cnt
               FROM   PER_ALL_ASSIGNMENTS_F paa
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)),
            1,
            (SELECT   full_name
               FROM   PER_ALL_ASSIGNMENTS_F paa, per_all_people_f pp
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)
                      AND SYSDATE BETWEEN pp.effective_start_date
                                      AND  NVL (pp.effective_end_date,
                                                SYSDATE)
                      AND paa.PERSON_ID = pp.person_id),
            'Multiple Employees attached to this Position'
         )
            Employee_Name
  FROM   (SELECT   ps.lev, PS.subordinate_POSITION_ID, pos.NAME
            FROM   (    SELECT   LEVEL lev, PS.subordinate_POSITION_ID
                          FROM   per_pos_structure_elements PS
                    START WITH   PS.POS_STRUCTURE_VERSION_ID = NVL (62, 0)
                                 AND PS.SUBORDINATE_POSITION_ID = 4087
                    CONNECT BY   PRIOR PS.PARENT_POSITION_ID =
                                    PS.SUBORDINATE_POSITION_ID
                                 AND PS.POS_STRUCTURE_VERSION_ID =
                                       NVL (62, 0)) ps,
                   per_positions POS
           WHERE   ps.subordinate_POSITION_ID = pos.POSITION_ID) det

********* PO - AP *********

SELECT   ih.invoice_num "Invoice Num",
         pla.line_num "PO Line Num",
         plla.shipment_num "PO Shipment Num",
         plla.quantity_billed "Shipment Qty Billed"
  FROM   ap_invoices ih,
         ap_invoice_distributions id,
         po_headers ph,
         po_distributions pd,
         po_lines pla,
         po_line_locations plla
 WHERE       ih.invoice_id(+) = id.invoice_id
         AND id.po_distribution_id(+) = pd.po_distribution_id
         AND ph.po_header_id = pd.po_header_id
         AND ph.po_header_id = pla.po_header_id
         AND pla.po_line_id = plla.po_line_id
         AND plla.line_location_id = pd.line_location_id
         AND ih.invoice_num IS NOT NULL
         AND NVL (plla.po_release_id, 0) = 0
         AND ph.po_header_id = 176977