Select
org.organization_code,
poh.segment1 po_number,
trunc(poh.creation_date) po_date,
rsh.receipt_num,
(rsh.creation_date) receipt_date,
mtl.segment1||'-'||mtl.segment2 item,
mtl.description,
rcv.quantity,
pol.unit_price
from rcv_transactions rcv
, po_lines_all pol,
po_headers_all poh,
po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
mtl_system_items mtl,
org_organization_definitions org
where rcv.transaction_type = 'RETURN TO VENDOR'
and rcv.po_line_location_id = pll.line_location_id
and rcv.po_line_id = pol.po_line_id
and pll.SHIP_TO_ORGANIZATION_ID=org.ORGANIZATION_ID
and rcv.shipment_line_id=rsl.shipment_line_id
and org.ORGANIZATION_ID=mtl.ORGANIZATION_ID
and rcv.shipment_header_id = rsh.shipment_header_id
and mtl.inventory_item_id=pol.item_id
and pol.po_header_id=poh.po_header_id
and rsl.shipment_header_id=rsh.shipment_header_id
and org.operating_unit=1214
order by 1,2,4
org.organization_code,
poh.segment1 po_number,
trunc(poh.creation_date) po_date,
rsh.receipt_num,
(rsh.creation_date) receipt_date,
mtl.segment1||'-'||mtl.segment2 item,
mtl.description,
rcv.quantity,
pol.unit_price
from rcv_transactions rcv
, po_lines_all pol,
po_headers_all poh,
po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
mtl_system_items mtl,
org_organization_definitions org
where rcv.transaction_type = 'RETURN TO VENDOR'
and rcv.po_line_location_id = pll.line_location_id
and rcv.po_line_id = pol.po_line_id
and pll.SHIP_TO_ORGANIZATION_ID=org.ORGANIZATION_ID
and rcv.shipment_line_id=rsl.shipment_line_id
and org.ORGANIZATION_ID=mtl.ORGANIZATION_ID
and rcv.shipment_header_id = rsh.shipment_header_id
and mtl.inventory_item_id=pol.item_id
and pol.po_header_id=poh.po_header_id
and rsl.shipment_header_id=rsh.shipment_header_id
and org.operating_unit=1214
order by 1,2,4
Thank you! Saved me a lot of time...
ReplyDeleteThanks S!!!
DeleteHow to link the AP Invoice Lines to this
ReplyDelete