RCL_INV_TRANSFER_ORDERS_V
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 |
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 |