RCL_INV_TRANSFER_ORDERS

Details

Schema: FUSION
Object owner: RCL
Object type: VIEW

Columns

Name
HEADER_NUMBER LINE_ID SHIPPED_QUANTITY DELIVERY_DETAIL_ID ACTUAL_SHIP_DATE SHIPMENT_NUM RECEIPT_NUM SHIPMENT_HEADER_ID SHIPMENT_LINE_ID RECEIPT_DATE

Name

HEADER_NUMBER

LINE_ID

SHIPPED_QUANTITY

DELIVERY_DETAIL_ID

ACTUAL_SHIP_DATE

SHIPMENT_NUM

RECEIPT_NUM

SHIPMENT_HEADER_ID

SHIPMENT_LINE_ID

RECEIPT_DATE

Query

SQL_Statement
SELECT itoh.header_number, itol.line_id, wdd.shipped_quantity, wdd.delivery_detail_id, imt1.transaction_date AS actual_ship_date, rsh.shipment_num, rsh.receipt_num, rsh.shipment_header_id, rsl.shipment_line_id, MAX(rt.transaction_date) AS receipt_date FROM inv_transfer_order_lines itol LEFT OUTER JOIN inv_transfer_order_headers itoh ON itol.header_id = itoh.header_id LEFT OUTER JOIN wsh_delivery_details wdd ON TO_CHAR(itol.line_number) = TO_CHAR(wdd.sales_order_line_number) AND itoh.header_number = wdd.sales_order_number LEFT OUTER JOIN wsh_delivery_assignments wda1 ON wda1.delivery_detail_id = wdd.delivery_detail_id LEFT OUTER JOIN wsh_new_deliveries wnd1 ON wda1.delivery_id = wnd1.delivery_id LEFT OUTER JOIN inv_material_txns imt1 ON imt1.transaction_source_id = wdd.source_header_id AND imt1.trx_source_line_id = wdd.source_line_id AND imt1.transaction_type_id IN ( 34,50,7,54,62,46,47 ) AND wdd.delivery_detail_id = imt1.picking_line_id AND imt1.trx_source_delivery_id = wnd1.delivery_id AND imt1.shipment_number = wnd1.delivery_name LEFT OUTER JOIN wsh_delivery_assignments wda ON wda.delivery_detail_id = wdd.delivery_detail_id LEFT OUTER JOIN wsh_new_deliveries wnd ON wda.delivery_id = wnd.delivery_id LEFT OUTER JOIN rcv_shipment_lines rsl ON rsl.transfer_order_line_id = itol.line_id AND imt1.transaction_id = rsl.mmt_transaction_id LEFT OUTER JOIN rcv_shipment_headers rsh ON rsh.shipment_header_id = rsl.shipment_header_id AND rsh.shipment_num = wnd.delivery_name LEFT OUTER JOIN rcv_transactions rt ON rsh.shipment_header_id = rt.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id GROUP BY itoh.header_number, itol.line_id, wdd.shipped_quantity, wdd.delivery_detail_id, imt1.transaction_date, rsh.shipment_num, rsh.receipt_num, rsh.shipment_header_id, rsl.shipment_line_id

SQL_Statement

SELECT

itoh.header_number,

itol.line_id,

wdd.shipped_quantity,

wdd.delivery_detail_id,

imt1.transaction_date AS actual_ship_date,

rsh.shipment_num,

rsh.receipt_num,

rsh.shipment_header_id,

rsl.shipment_line_id,

MAX(rt.transaction_date) AS receipt_date

FROM

inv_transfer_order_lines itol

LEFT OUTER JOIN inv_transfer_order_headers itoh ON itol.header_id = itoh.header_id

LEFT OUTER JOIN wsh_delivery_details wdd ON

TO_CHAR(itol.line_number) = TO_CHAR(wdd.sales_order_line_number)

AND

itoh.header_number = wdd.sales_order_number

LEFT OUTER JOIN wsh_delivery_assignments wda1 ON wda1.delivery_detail_id = wdd.delivery_detail_id

LEFT OUTER JOIN wsh_new_deliveries wnd1 ON wda1.delivery_id = wnd1.delivery_id

LEFT OUTER JOIN inv_material_txns imt1 ON

imt1.transaction_source_id = wdd.source_header_id

AND

imt1.trx_source_line_id = wdd.source_line_id

AND

imt1.transaction_type_id IN (

34,50,7,54,62,46,47

)

AND

wdd.delivery_detail_id = imt1.picking_line_id

AND

imt1.trx_source_delivery_id = wnd1.delivery_id

AND

imt1.shipment_number = wnd1.delivery_name

LEFT OUTER JOIN wsh_delivery_assignments wda ON wda.delivery_detail_id = wdd.delivery_detail_id

LEFT OUTER JOIN wsh_new_deliveries wnd ON wda.delivery_id = wnd.delivery_id

LEFT OUTER JOIN rcv_shipment_lines rsl ON

rsl.transfer_order_line_id = itol.line_id

AND

imt1.transaction_id = rsl.mmt_transaction_id

LEFT OUTER JOIN rcv_shipment_headers rsh ON

rsh.shipment_header_id = rsl.shipment_header_id

AND

rsh.shipment_num = wnd.delivery_name

LEFT OUTER JOIN rcv_transactions rt ON

rsh.shipment_header_id = rt.shipment_header_id

AND

rsl.shipment_line_id = rt.shipment_line_id

GROUP BY