TABLE: RCV_TRANSACTIONS
COLUMN: ROUTING_HEADER_ID
VALUES:
1 (Standard Receipt),
2 (Inspection Required) And
3 (Direct Delivery).
SQL Query:
SELECT pha.segment1 po_number,
pla.line_num po_line_number,
pra.release_num po_release_number,
rsh.receipt_num receipt_number,
decode(rt.routing_header_id,1,'Standard Receipt', 2, 'Inspection Required', 3, 'Direct Delivery',null) Receipt_Routing,
msik.concatenated_segments item_number,
msik.description,
rs.unit_of_measure,
rs.item_revision,
rs.receipt_date,
plla.quantity total_quantity,
rs.quantity quantity_to_inspect,
plla.quantity_received,
plla.quantity_accepted,
plla.quantity_rejected,
rt.vendor_lot_num,
rt.comments vendor_serial_num,
rsh.bill_of_lading customs_number,
rsh.comments customs_date,
rt.attribute1 manufactured_date,
rs.po_line_id,
rs.po_header_id,
rs.po_release_id,
rs.po_line_location_id,
rs.shipment_header_id,
rs.shipment_line_id,
rs.rcv_transaction_id,
rs.item_id,
rs.to_organization_id,
rs.supply_source_id,
mp.organization_code
FROM apps.rcv_supply rs,
apps.mtl_system_items_kfv msik,
apps.po_headers_all pha,
apps.po_lines_all pla,
apps.po_releases_all pra,
apps.po_line_locations_all plla,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.mtl_parameters mp
WHERE rs.to_organization_id = mp.organization_id
AND mp.organization_code IN ('SKO', 'TIF', 'TIS', 'TIW')
AND rs.supply_type_code = 'RECEIVING'
AND rs.po_header_id = pha.po_header_id
AND msik.inventory_item_id = rs.item_id
AND msik.organization_id = rs.to_organization_id
AND rs.po_line_id = pla.po_line_id
AND rs.po_release_id = pra.po_release_id(+)
--AND TRUNC(rs.receipt_date) = TRUNC(SYSDATE)
AND rs.po_line_location_id = plla.line_location_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.transaction_type = 'RECEIVE'
AND rt.inspection_status_code = 'NOT INSPECTED'
AND rs.shipment_header_id = rsh.shipment_header_id
-- AND pha.segment1='333428'
-- AND rt.routing_header_id=2 -- 1 (Standard receipt), 2 (Inspection required) and 3 (Direct delivery)
ORDER BY 9,
5,
1,
2,
4;