SELECT pv.vendor_name "Supplier" ,
pvsa.vendor_site_code "Supplier site",
pha.segment1 "PO No" , (
CASE
WHEN pha.type_lookup_code = 'BLANKET'
THEN
(SELECT release_num
FROM po_releases_all pra
WHERE pra.po_release_id = plla.po_release_id
)
ELSE NULL
END) "Release No" ,
pla.line_num "PO Line No" ,
rsh.receipt_num "Receipt NO" ,
rsl.line_num "Receipt Line No" ,
msib.segment1 Item ,
rsl.unit_of_measure "Unit Of Measure" ,
rsl.quantity_received "Receive Qty" ,
rsh.attribute_category "Receipt Category" ,
rsh.attribute1 "Excise Invoice No" ,
TRUNC(to_date(rsh.attribute2,'yyyy/mm/dd HH24:MI:SS')) "Excise Invoice date" ,
jrtl.currency Currency ,
jrtl.tax_name "Tax Name" ,
jrtl.tax_rate "Tax Rate" ,
jrtl.tax_amount "Tax Amount" ,
gcc.concatenated_segments "Code combination"
FROM rcv_shipment_headers rsh ,
rcv_shipment_lines rsl ,
ja.ja_in_receipt_tax_lines jrtl ,
po_headers_all pha ,
po_lines_all pla ,
po_line_locations_all plla ,
mtl_system_items_b msib ,
ja.JA_IN_RCV_JOURNALS_B jrj ,
gl_code_combinations_kfv gcc ,
po_vendors pv ,
po_vendor_sites_all pvsa
WHERE 1 =1
AND pv.vendor_id =pvsa.vendor_id
AND pv.vendor_id =pha.vendor_id
AND pvsa.vendor_site_id =pha.vendor_site_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND jrtl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = jrtl.shipment_line_id
AND pha.org_id = pla.org_id
AND plla.org_id = pla.org_id
AND pha.org_id = &OU_ID
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = rsl.po_header_id
AND pla.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.po_line_id = rsl.po_line_id
AND msib.inventory_item_id = pla.item_id
AND msib.inventory_item_id =rsl.item_id
AND msib.organization_id = rsh.ship_to_org_id
AND rsl.po_line_location_id =plla.line_location_id
AND rsh.ship_to_org_id = &INV_ID
AND plla.quantity_received >0
AND TRUNC(rsh.creation_date) BETWEEN to_date('&P_S_DATE','DD-Mon-YYYY') AND to_date('&P_E_DATE','DD-Mon-YYYY')
AND jrj.receipt_num =rsh.receipt_num
AND jrj.shipment_line_id =rsl.shipment_line_id
AND jrj.entered_dr =jrtl.tax_amount
AND jrj.code_combination_id=gcc.code_combination_id
ORDER BY 1,2,3,4,5,6
pvsa.vendor_site_code "Supplier site",
pha.segment1 "PO No" , (
CASE
WHEN pha.type_lookup_code = 'BLANKET'
THEN
(SELECT release_num
FROM po_releases_all pra
WHERE pra.po_release_id = plla.po_release_id
)
ELSE NULL
END) "Release No" ,
pla.line_num "PO Line No" ,
rsh.receipt_num "Receipt NO" ,
rsl.line_num "Receipt Line No" ,
msib.segment1 Item ,
rsl.unit_of_measure "Unit Of Measure" ,
rsl.quantity_received "Receive Qty" ,
rsh.attribute_category "Receipt Category" ,
rsh.attribute1 "Excise Invoice No" ,
TRUNC(to_date(rsh.attribute2,'yyyy/mm/dd HH24:MI:SS')) "Excise Invoice date" ,
jrtl.currency Currency ,
jrtl.tax_name "Tax Name" ,
jrtl.tax_rate "Tax Rate" ,
jrtl.tax_amount "Tax Amount" ,
gcc.concatenated_segments "Code combination"
FROM rcv_shipment_headers rsh ,
rcv_shipment_lines rsl ,
ja.ja_in_receipt_tax_lines jrtl ,
po_headers_all pha ,
po_lines_all pla ,
po_line_locations_all plla ,
mtl_system_items_b msib ,
ja.JA_IN_RCV_JOURNALS_B jrj ,
gl_code_combinations_kfv gcc ,
po_vendors pv ,
po_vendor_sites_all pvsa
WHERE 1 =1
AND pv.vendor_id =pvsa.vendor_id
AND pv.vendor_id =pha.vendor_id
AND pvsa.vendor_site_id =pha.vendor_site_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND jrtl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = jrtl.shipment_line_id
AND pha.org_id = pla.org_id
AND plla.org_id = pla.org_id
AND pha.org_id = &OU_ID
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = rsl.po_header_id
AND pla.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.po_line_id = rsl.po_line_id
AND msib.inventory_item_id = pla.item_id
AND msib.inventory_item_id =rsl.item_id
AND msib.organization_id = rsh.ship_to_org_id
AND rsl.po_line_location_id =plla.line_location_id
AND rsh.ship_to_org_id = &INV_ID
AND plla.quantity_received >0
AND TRUNC(rsh.creation_date) BETWEEN to_date('&P_S_DATE','DD-Mon-YYYY') AND to_date('&P_E_DATE','DD-Mon-YYYY')
AND jrj.receipt_num =rsh.receipt_num
AND jrj.shipment_line_id =rsl.shipment_line_id
AND jrj.entered_dr =jrtl.tax_amount
AND jrj.code_combination_id=gcc.code_combination_id
ORDER BY 1,2,3,4,5,6