ORACLE MASTERMINDS: Script for getting PO SLA Details

Wednesday, 2 January 2019

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