Script for getting PO SLA Details

SELECT DISTINCT pap.last_name || ‘, ‘ || pap.first_name AS “BUYER”,
                prh.segment1 AS “REQUISITION”,
                (SELECT    pap2.last_name
                        || ‘, ‘
                        || pap2.first_name
                   FROM per_all_people_f pap2
                  WHERE pap2.person_id = pahv2.employee_id
                    AND TRUNC (effective_start_date) <= TRUNC (SYSDATE)
                    AND TRUNC (pap2.effective_end_date) >= TRUNC (SYSDATE))
                                                            AS “REQ_APPROVER”,
                TRUNC (prh.approved_date) AS “REQ_APPROVAL_DT”,
                DECODE
                   (prh.attribute_category,
                    ‘No’, ‘Opex(‘
                     || CASE
                           WHEN pla.purchase_basis = ‘GOODS’
                           AND (SELECT plt.purchase_basis
                                  FROM po_line_types_b plt
                                 WHERE plt.line_type_id = prl.line_type_id) =
                                                                       ‘GOODS’
                              THEN
                                   — tipo de aquisition_type
                                  ‘Goods’
                           WHEN pla.purchase_basis = ‘SERVICES’
                           AND (SELECT plt.purchase_basis
                                  FROM po_line_types_b plt
                                 WHERE plt.line_type_id = prl.line_type_id) =
                                                                    ‘SERVICES’
                              THEN
                                   — tipo de aquisition_type
                                  ‘Services’
                        END
                     || ‘)’,
                    ‘Yes’, prh.attribute_category
                   ) AS “ACQUISITION_TYPE”,
                pha.segment1 AS “PURCHASE_ORDER”,
                pahv.employee_name AS “PO_APPROVER”,
                TRUNC (pha.approved_date) AS “PO_APPROVAL_DT”,
                TRUNC (ABS (pha.approved_date – prh.approved_date)
                      ) AS “TIME_SPENT”,
                NVL
                   ((SELECT   SUM ((pla.unit_price * pla.quantity
                                   )) amount_po
                         FROM po_headers_all pha2,
                              po_lines_all pla,
                              po_line_locations_all plla,
                              po_distributions_all pda,
                              hr_all_organization_units haou,
                              po_req_distributions_all prd,
                              po_requisition_lines_all prl,
                              po_requisition_headers_all prh2
                        WHERE 1 = 1
                          AND pla.po_header_id = pha2.po_header_id
                          AND pla.org_id = pha2.org_id
                          AND plla.po_header_id = pla.po_header_id
                          AND plla.po_line_id = pla.po_line_id
                          AND plla.org_id = pla.org_id
                          AND pda.po_header_id = plla.po_header_id
                          AND pda.po_line_id = plla.po_line_id
                          AND pda.line_location_id = plla.line_location_id
                          AND pda.org_id = plla.org_id
                          AND pha2.org_id = haou.organization_id
                          AND prd.distribution_id = pda.req_distribution_id
                          AND prd.org_id = pda.org_id
                          AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                          AND prl.org_id = prd.org_id
                          AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                          AND prh2.org_id = prl.org_id
                          AND haou.organization_id = haou2.organization_id
                          –‘IM BR OU’
                          AND pha2.type_lookup_code = pha.type_lookup_code
                          AND pha2.segment1 = pha.segment1
                          AND prh2.segment1 = prh.segment1
                     GROUP BY pha2.segment1, prh2.segment1),
                    0
                   ) AS sla_required,
                CASE
                   WHEN NVL
                          (  TRUNC (ABS (pha.approved_date – prh.approved_date)
                                   )
                           – (SELECT   SUM ((pla.unit_price * pla.quantity)
                                           ) amount_po
                                  FROM po_headers_all pha2,
                                       po_lines_all pla,
                                       po_line_locations_all plla,
                                       po_distributions_all pda,
                                       hr_all_organization_units haou,
                                       po_req_distributions_all prd,
                                       po_requisition_lines_all prl,
                                       po_requisition_headers_all prh2
                                 WHERE 1 = 1
                                   AND pla.po_header_id = pha2.po_header_id
                                   AND pla.org_id = pha2.org_id
                                   AND plla.po_header_id = pla.po_header_id
                                   AND plla.po_line_id = pla.po_line_id
                                   AND plla.org_id = pla.org_id
                                   AND pda.po_header_id = plla.po_header_id
                                   AND pda.po_line_id = plla.po_line_id
                                   AND pda.line_location_id =
                                                         plla.line_location_id
                                   AND pda.org_id = plla.org_id
                                   AND pha2.org_id = haou.organization_id
                                   AND prd.distribution_id =
                                                       pda.req_distribution_id
                                   AND prd.org_id = pda.org_id
                                   AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                                   AND prl.org_id = prd.org_id
                                   AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                                   AND prh2.org_id = prl.org_id
                                   AND haou.organization_id =
                                                         haou2.organization_id
                                   AND pha2.type_lookup_code =
                                                          pha.type_lookup_code
                                   AND pha2.segment1 = pha.segment1
                                   AND prh2.segment1 = prh.segment1
                              GROUP BY pha2.segment1, prh2.segment1),
                           0
                          ) > 0
                      THEN ‘DELAYED’
                   WHEN NVL (  TRUNC (ABS (  pha.approved_date
                                           – prh.approved_date
                                          )
                                     )
                             – (SELECT   SUM ((pla.unit_price * pla.quantity
                                              )
                                             ) amount_po
                                    FROM po_headers_all pha2,
                                         po_lines_all pla,
                                         po_line_locations_all plla,
                                         po_distributions_all pda,
                                         hr_all_organization_units haou,
                                         po_req_distributions_all prd,
                                         po_requisition_lines_all prl,
                                         po_requisition_headers_all prh2
                                   WHERE 1 = 1
                                     AND pla.po_header_id = pha2.po_header_id
                                     AND pla.org_id = pha2.org_id
                                     AND plla.po_header_id = pla.po_header_id
                                     AND plla.po_line_id = pla.po_line_id
                                     AND plla.org_id = pla.org_id
                                     AND pda.po_header_id = plla.po_header_id
                                     AND pda.po_line_id = plla.po_line_id
                                     AND pda.line_location_id =
                                                         plla.line_location_id
                                     AND pda.org_id = plla.org_id
                                     AND pha2.org_id = haou.organization_id
                                     AND prd.distribution_id =
                                                       pda.req_distribution_id
                                     AND prd.org_id = pda.org_id
                                     AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                                     AND prl.org_id = prd.org_id
                                     AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                                     AND prh2.org_id = prl.org_id
                                     AND haou.organization_id =
                                                         haou2.organization_id
                                     AND pha2.type_lookup_code =
                                                          pha.type_lookup_code
                                     AND pha2.segment1 = pha.segment1
                                     AND prh2.segment1 = prh.segment1
                                GROUP BY pha2.segment1, prh2.segment1),
                             0
                            ) <= 0
                      THEN ‘ON TIME’
                END AS status,
                NVL (ABS (  TRUNC (ABS (pha.approved_date – prh.approved_date))
                          – (SELECT   SUM ((pla.unit_price * pla.quantity)
                                          ) amount_po
                                 FROM po_headers_all pha2,
                                      po_lines_all pla,
                                      po_line_locations_all plla,
                                      po_distributions_all pda,
                                      hr_all_organization_units haou,
                                      po_req_distributions_all prd,
                                      po_requisition_lines_all prl,
                                      po_requisition_headers_all prh2
                                WHERE 1 = 1
                                  AND pla.po_header_id = pha2.po_header_id
                                  AND pla.org_id = pha2.org_id
                                  AND plla.po_header_id = pla.po_header_id
                                  AND plla.po_line_id = pla.po_line_id
                                  AND plla.org_id = pla.org_id
                                  AND pda.po_header_id = plla.po_header_id
                                  AND pda.po_line_id = plla.po_line_id
                                  AND pda.line_location_id =
                                                         plla.line_location_id
                                  AND pda.org_id = plla.org_id
                                  AND pha2.org_id = haou.organization_id
                                  AND prd.distribution_id =
                                                       pda.req_distribution_id
                                  AND prd.org_id = pda.org_id
                                  AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                                  AND prl.org_id = prd.org_id
                                  AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                                  AND prh2.org_id = prl.org_id
                                  AND haou.organization_id =
                                                         haou2.organization_id
                                  AND pha2.type_lookup_code =
                                                          pha.type_lookup_code
                                  AND pha2.segment1 = pha.segment1
                                  AND prh2.segment1 = prh.segment1
                             GROUP BY pha2.segment1, prh2.segment1)
                         ),
                     0
                    ) AS RESULT
           FROM po_headers_all pha,
                po_lines_all pla,
                po_line_locations_all plla,
                po_distributions_all pda,
                hr_all_organization_units haou2,
                po_req_distributions_all prd,
                po_requisition_lines_all prl,
                po_requisition_headers_all prh,
                per_all_people_f pap,
                po_action_history_v pahv,
                po_action_history pahv2
          WHERE 1 = 1
            AND pla.po_header_id = pha.po_header_id
            AND pla.org_id = pha.org_id
            AND plla.po_header_id = pla.po_header_id
            AND plla.po_line_id = pla.po_line_id
            AND plla.org_id = pla.org_id
            AND pda.po_header_id = plla.po_header_id
            AND pda.po_line_id = plla.po_line_id
            AND pda.line_location_id = plla.line_location_id
            AND pda.org_id = plla.org_id
            AND pha.org_id = haou2.organization_id
            AND prd.distribution_id = pda.req_distribution_id
            AND prd.org_id = pda.org_id
            AND prl.requisition_line_id = prd.requisition_line_id
            AND prl.org_id = prd.org_id
            AND prh.requisition_header_id = prl.requisition_header_id
            AND prh.org_id = prl.org_id
            AND pap.person_id = pha.agent_id
            AND pahv.object_id = pha.po_header_id           
            AND pahv.action_code = ‘APPROVE’
            AND pahv.object_type_code = ‘PO’
            AND pahv.sequence_num =
                   (SELECT MAX (pahv_in.sequence_num)
                      FROM po_action_history_v pahv_in
                     WHERE pahv_in.action_code = pahv.action_code
                       AND pahv_in.object_type_code = pahv.object_type_code
                                              AND pahv_in.object_id = pahv.object_id)           
            AND pahv2.object_type_code(+) = ‘REQUISITION’
            AND pahv2.action_code(+) = ‘APPROVE’
            AND pahv2.object_id(+) = prh.requisition_header_id
            AND pahv2.sequence_num =
                   (SELECT MAX (pahv3.sequence_num)
                      FROM po_action_history_v pahv3
                     WHERE pahv3.object_id = pahv2.object_id
                       AND pahv2.action_code = pahv3.action_code
                       AND pahv2.object_type_code = pahv3.object_type_code)
            AND pha.authorization_status = ‘APPROVED’
            AND pha.approved_flag = ‘Y’
            AND pha.type_lookup_code =
                   DECODE (:p_po_type,
                           ‘ALL’, pha.type_lookup_code,
                           :p_po_type
                          )
            AND pha.agent_id = NVL (:p_buyer_id, pha.agent_id)           
            AND DECODE (prh.attribute_category,
                        ‘No’, ‘Opex(‘
                         || CASE
                               WHEN pla.purchase_basis = ‘GOODS’
                               AND (SELECT plt.purchase_basis
                                      FROM po_line_types_b plt
                                     WHERE plt.line_type_id = prl.line_type_id) =
                                                                       ‘GOODS’
                                  THEN                                     
                                      ‘Goods’
                               WHEN pla.purchase_basis = ‘SERVICES’
                               AND (SELECT plt.purchase_basis
                                      FROM po_line_types_b plt
                                     WHERE plt.line_type_id = prl.line_type_id) =
                                                                    ‘SERVICES’
                                  THEN                                     
                                      ‘Services’
                            END
                         || ‘)’,
                        ‘Yes’, prh.attribute_category
                       ) =
                   NVL
                      (:p_aquisition_type,
                       DECODE
                          (prh.attribute_category,
                           ‘No’, ‘Opex(‘
                            || CASE
                                  WHEN pla.purchase_basis = ‘GOODS’
                                  AND (SELECT plt.purchase_basis
                                         FROM po_line_types_b plt
                                        WHERE plt.line_type_id =
                                                              prl.line_type_id) =
                                                                       ‘GOODS’
                                     THEN                                         
                                         ‘Goods’
                                  WHEN pla.purchase_basis = ‘SERVICES’
                                  AND (SELECT plt.purchase_basis
                                         FROM po_line_types_b plt
                                        WHERE plt.line_type_id =
                                                              prl.line_type_id) =
                                                                    ‘SERVICES’
                                     THEN                                         
                                         ‘Services’
                               END
                            || ‘)’,
                           ‘Yes’, prh.attribute_category
                          )
                      )
       ORDER BY 1
  • January 2, 2019 | 17 views
  • Comments