INV_TRANSFER_ORDER_SHIPMENTS_V

Details

• Schema: FUSION

• Object owner: INV

• Object type: VIEW

Columns

Name

HEADER_ID HEADER_NUMBER LINE_ID LINE_NUMBER DELIVERY_ID SHIPMENT SHIPMENT_STATUS_CODE BILL_OF_LADING_NUMBER CARRIER_ID SERVICE_LEVEL MODE_OF_TRANSPORT SHIP_METHOD_CODE ORGANIZATION_ID ACTUAL_SHIP_DATE GROSS_WEIGHT WEIGHT_UOM_CODE PACKING_SLIP_NUMBER INTIAL_SHIP_DATE PLANNED_DELIVERY_DATE WAYBILL SEAL_CODE VOLUME VOLUME_UOM_CODE TRANSACTION_ACTION_ID SHIPMENT_HEADER_ID RECEIPT_NUM EXPECTED_RECEIPT_DATE EMPLOYEE_ID TRANSFER_ORDER_LINE_ID DATE_RECEIVED

Query

SQL_Statement

select distinct wsh.header_id , wsh.header_number , wsh.line_id , wsh.line_number , wsh.delivery_id , wsh.shipment , wsh.shipment_status_code , wsh.bill_of_lading_number , wsh.carrier_id , wsh.service_level , wsh.mode_of_transport , wsh.ship_method_code , wsh.organization_id , wsh.actual_ship_date , wsh.gross_weight , wsh.weight_uom_code , wsh.packing_slip_number , wsh.intial_ship_date , wsh.planned_delivery_date , wsh.waybill , wsh.seal_code , wsh.volume , wsh.volume_uom_code , wsh.transaction_action_id , rcv.shipment_header_id , rcv.receipt_num , rcv.expected_receipt_date , rcv.employee_id , rcv.transfer_order_line_id , rcv.date_received from (select itoh.header_id , itoh.header_number , itol.line_id , itol.line_number , wnd.delivery_id , wnd.delivery_name as shipment , wnd.status_code as shipment_status_code , wnd.bill_of_lading_number , wnd.carrier_id , wnd.service_level , wnd.mode_of_transport , wnd.ship_method_code , wnd.organization_id , wnd.actual_ship_date , wnd.gross_weight , wnd.weight_uom_code , wnd.packing_slip_number , wnd.initial_pickup_date as intial_ship_date , wnd.ultimate_dropoff_date as planned_delivery_date , wnd.waybill , wnd.seal_code , wnd.volume , wnd.volume_uom_code , imt.transaction_action_id from inv_transfer_order_lines itol , inv_transfer_order_headers itoh , wsh_new_deliveries wnd , wsh_delivery_assignments wda , wsh_delivery_details wdd , inv_material_txns imt where itol.header_id = itoh.header_id and wnd.delivery_id = wda.delivery_id and wda.delivery_detail_id = wdd.delivery_detail_id and itoh.header_number = wdd.sales_order_number and to_char(itol.line_number) = wdd.sales_order_line_number and wdd.source_line_type in ('TRANSFER_ORDER', 'TRANSFER_ORDER_RETURN') and imt.picking_line_id (+) = wdd.delivery_detail_id /*Bug 33147300: updated condition added by Bug 31596443 to exclude all system added transactions such as secondary quantity adjustment transactions 98:Residual Quantity Issue and 99: Residual Quantity Receipt, also Transfer to Owned transaction involving consigned stock and using transaction source type id of TO and return TO to join IMT records instead of transaction type id*/ and nvl(imt.transaction_source_type_id, -1) in (-1, 8, 18) ) wsh, (select itol2.line_id , rsh.shipment_header_id , rsh.receipt_num , rsh.shipment_num , rsh.expected_receipt_date , rsh.employee_id , rsl.transfer_order_line_id , (select max(transaction_date) from rcv_transactions rt where rt.shipment_header_id = rsh.shipment_header_id group by rt.shipment_header_id) as date_received from inv_transfer_order_lines itol2 , rcv_shipment_headers rsh , rcv_shipment_lines rsl where rsh.shipment_header_id = rsl.shipment_header_id and itol2.line_id = rsl.transfer_order_line_id ) rcv where wsh.shipment = rcv.shipment_num (+) and wsh.line_id = rcv.line_id (+)