The below query gives the Vendor delivery performance details with po approval TAT and delivery TAT.
SELECT
po_number,
vendor_name,
po_creation_date,
po_approved_date,
po_item_code,
po_item_description,
po_quantity,
po_price,
rate_type,
exchange_rate_date,
exchange_rate,
po_line_amt,
pr_to_po_creation_tat,
po_creation_to_approval_tat,
need_by_date,
mrn_date,
actual_material_receipt_date,
mrn_tat,
delivery_tat
FROM
(
SELECT DISTINCT
poh.segment1 po_number,
poh.creation_date po_creation_date,
aps.vendor_name vendor_name,
poh.approved_date po_approved_date,
mtl.segment1 po_item_code,
pol.po_line_id,
pol.item_description po_item_description,
pol.quantity po_quantity,
pol.unit_price po_price,
poh.rate_type,
poh.rate_date exchange_rate_date,
poh.rate exchange_rate,
( pol.quantity * pol.unit_price * nvl(poh.rate,1) ) po_line_amt,
( TO_DATE(poh.creation_date,’DD/MM/YYYY’) – TO_DATE(prh.approved_date,’DD/MM/YYYY’) – 2 * ( TO_CHAR(TO_DATE(poh.creation_date
,’DD/MM/YYYY’),’WW’) – TO_CHAR(TO_DATE(prh.approved_date,’DD/MM/YYYY’),’WW’) ) ) pr_to_po_creation_tat,
( TO_DATE(poh.approved_date,’DD/MM/YYYY’) – TO_DATE(poh.creation_date,’DD/MM/YYYY’) – 2 * ( TO_CHAR(TO_DATE(poh.approved_date
,’DD/MM/YYYY’),’WW’) – TO_CHAR(TO_DATE(poh.creation_date,’DD/MM/YYYY’),’WW’) ) ) po_creation_to_approval_tat,
poll.need_by_date,
trunc(rsh.creation_date) mrn_date,
DECODE(rsh.attribute_category,’SIFY_RECEIPT’, (TO_DATE(rsh.attribute15,’YYYY/MM/DD HH24:MI:SS’) ),NULL) actual_material_receipt_date
,
DECODE(rsh.attribute_category,’SIFY_RECEIPT’, (TO_DATE(rsh.attribute15,’YYYY/MM/DD HH24:MI:SS’) – trunc(poh.creation_date
) – 2 * (TO_CHAR(TO_DATE(rsh.attribute15,’YYYY/MM/DD HH24:MI:SS’),’WW’) – TO_CHAR(trunc(poh.creation_date),’WW’) ) )
,NULL) mrn_tat,
DECODE(rsh.attribute_category,’SIFY_RECEIPT’, (TO_DATE(rsh.attribute15,’YYYY/MM/DD HH24:MI:SS’) – trunc(poll.need_by_date
) – 2 * (TO_CHAR(TO_DATE(rsh.attribute15,’YYYY/MM/DD HH24:MI:SS’),’WW’) – TO_CHAR(trunc(poll.need_by_date),’WW’) ) )
,NULL) delivery_tat
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
ap_suppliers aps,
mtl_system_items_b mtl,
rcv_transactions rct,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE
1 = 1
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id
AND pod.po_header_id = pol.po_header_id
AND pol.po_line_id = pod.po_line_id
AND pod.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
AND aps.vendor_id = poh.vendor_id
AND mtl.inventory_item_id = pol.item_id
AND mtl.organization_id = poll.ship_to_organization_id
AND rct.po_header_id = poh.po_header_id
AND rct.po_line_location_id = poll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rct.transaction_type = ‘DELIVER’
)
GROUP BY
po_number,
vendor_name,
po_creation_date,
po_approved_date,
po_item_code,
po_item_description,
po_quantity,
po_price,
rate_type,
exchange_rate_date,
exchange_rate,
po_line_amt,
pr_to_po_creation_tat,
po_creation_to_approval_tat,
need_by_date,
mrn_date,
actual_material_receipt_date,
mrn_tat,
delivery_tat,
po_line_id
ORDER BY
1
Recent Posts