Sql Script to extract Vendor, Project, Receipt information for a PO

Sql Script to extract Vendor, Project, Receipt information for a PO

SELECT pha.po_header_id, pha.segment1 po_number, pov.vendor_name,
pov.segment1 vendor_num, pla.line_num po_line_number, pla.item_id,
(SELECT MAX (segment1)
FROM mtl_system_items_b
WHERE inventory_item_id = pla.item_id) item,
DECODE (NVL (pla.item_id, 0),
0, NULL,
pla.item_description
) item_description,
DECODE (pla.cancel_flag,
'Y', 'CANCELLED',
DECODE (pha.authorization_status,
'IN PROCESS', 'PENDING APPROVAL',
NVL (pha.authorization_status, 'INCOMPLETE')
)
) authorization_status,
DECODE (pla.cancel_flag,
'Y', 'N/A',
DECODE (pha.authorization_status,
'APPROVED', DECODE (NVL (pla.closed_code, 'b'),
'CLOSED', 'CLOSED',
'FINALLY CLOSED', 'CLOSED',
'OPEN'
),
'N/A'
)
) po_status,
pha.creation_date date_issued, pla.creation_date line_creation_date,
(pda.quantity_ordered - pda.quantity_cancelled) po_quantity,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_ordered, 0)
) po_line_amt,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_cancelled, 0)
) po_cancelled_amount,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_delivered, 0)
) po_line_received_amount,
rt.transaction_date receipt_date,
TO_CHAR (rt.transaction_date, 'MON-RR') receipt_month,
rsh.receipt_num,
(DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_quantity,
( NVL (rt.po_unit_price, 0)
* DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_amount,
ai.invoice_id, ai.invoice_num, ai.invoice_amount, ai.amount_paid,
ai.invoice_date, aid.quantity_invoiced, aid.amount inv_line_amount,
ppa.project_id, ppa.segment1 project_number, ppa.NAME project_name,
(SELECT full_name
FROM pa_project_players_v
WHERE project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER'
AND NVL (end_date_active, SYSDATE) IN (
SELECT MAX (NVL (end_date_active, SYSDATE))
FROM pa_project_players_v
WHERE 1 = 1
AND project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER')
AND ROWNUM < 2) "Project Manager",
total_billings.invoice_amount total_actual_billings
FROM po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pov,
pa_projects_all ppa,
(SELECT i.project_id, SUM (NVL (ii.inv_amount, 0)) invoice_amount
FROM pa_draft_invoices_all i, pa_draft_inv_items_bas ii
WHERE 1 = 1
AND ii.project_id(+) = i.project_id
AND ii.draft_invoice_num(+) = i.draft_invoice_num
AND i.pa_date < ((SELECT end_date
FROM pa_periods_all ppi
WHERE ppi.current_pa_period_flag = 'Y') + 1)
GROUP BY i.project_id) total_billings
WHERE pha.po_header_id = pla.po_header_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pla.po_line_id
AND pda.po_header_id = pha.po_header_id
AND pha.vendor_id = pov.vendor_id
AND pda.po_distribution_id = aid.po_distribution_id(+)
AND aid.invoice_id = ai.invoice_id(+)
AND pla.po_line_id = rt.po_line_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.destination_type_code(+) = 'RECEIVING'
AND pda.project_id = ppa.project_id(+)
AND ppa.project_id = total_billings.project_id(+)