INV_TOL_SHIP_RECEIPT_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 SHIPMENT_STATUS BILL_OF_LADING_NUMBER CARRIER_ID CARRIER_NAME SERVICE_LEVEL_CODE SERVICE_LEVEL MODE_OF_TRANSPORT_CODE MODE_OF_TRANSPORT SHIP_METHOD_CODE SHIP_METHOD ORGANIZATION_ID ORGANIZATION_CODE ORGANIZATION_NAME ACTUAL_SHIP_DATE GROSS_WEIGHT WEIGHT_UOM_CODE WEIGHT_UOM PACKING_SLIP_NUMBER INTIAL_SHIP_DATE PLANNED_DELIVERY_DATE WAYBILL SEAL_CODE VOLUME VOLUME_UOM_CODE VOLUME_UOM SHIPMENT_HEADER_ID RECEIPT_NUM EXPECTED_RECEIPT_DATE EMPLOYEE_ID EMPLOYEE_DISPLAY_NAME 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.shipment_status , wsh.bill_of_lading_number , wsh.carrier_id , wsh.carrier_name , wsh.service_level_code , wsh.service_level , wsh.mode_of_transport_code , wsh.mode_of_transport , wsh.ship_method_code , wsh.ship_method , wsh.organization_id , wsh.organization_code , wsh.organization_name , wsh.actual_ship_date , wsh.gross_weight , wsh.weight_uom_code , wsh.weight_uom , wsh.packing_slip_number , wsh.intial_ship_date , wsh.planned_delivery_date , wsh.waybill , wsh.seal_code , wsh.volume , wsh.volume_uom_code , wsh.volume_uom , rcv.shipment_header_id , rcv.receipt_num , rcv.expected_receipt_date , rcv.employee_id , rcv.employee_display_name , 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 shipment , wnd.status_code shipment_status_code , statuslkup.meaning shipment_status , wnd.bill_of_lading_number , wnd.carrier_id , carrierParty.party_name carrier_name , wnd.service_level service_level_code , srvlevellkup.meaning service_level , wnd.mode_of_transport mode_of_transport_code , motlkup.meaning mode_of_transport , wnd.ship_method_code , (case when wnd.ship_method_code is null then null else (carrierParty.party_name||'-'||motlkup.meaning||'-'||srvlevellkup.meaning) end) ship_method , wnd.organization_id , iodv.organization_code , iodv.organization_name , wnd.actual_ship_date , wnd.gross_weight , wnd.weight_uom_code , weightuom.unit_of_measure weight_uom , wnd.packing_slip_number , wnd.initial_pickup_date intial_ship_date , wnd.ultimate_dropoff_date planned_delivery_date , wnd.waybill , wnd.seal_code , wnd.volume , wnd.volume_uom_code , volumeuom.unit_of_measure volume_uom from inv_transfer_order_lines itol , inv_transfer_order_headers itoh , wsh_new_deliveries wnd , wsh_delivery_assignments wda , wsh_delivery_details wdd , inv_organization_definitions_v iodv , fnd_lookups statuslkup , hz_parties carrierParty , fnd_lookups srvlevellkup , fnd_lookups motlkup , inv_units_of_measure_vl weightuom , inv_units_of_measure_vl volumeuom 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 wnd.organization_id = iodv.organization_id and wnd.status_code = statuslkup.lookup_code and statuslkup.lookup_type = 'WSH_DELIVERY_STATUS' and wnd.carrier_id = carrierParty.party_id (+) and srvlevellkup.lookup_type (+) = 'WSH_SERVICE_LEVELS' and wnd.service_level = srvlevellkup.lookup_code (+) and motlkup.lookup_type (+) = 'WSH_MODE_OF_TRANSPORT' and wnd.mode_of_transport = motlkup.lookup_code (+) and wnd.weight_uom_code = weightuom.uom_code (+) and wnd.volume_uom_code = volumeuom.uom_code (+) ) wsh, (select itol2.line_id line_id , max(rsh.shipment_header_id) shipment_header_id , max(rsh.receipt_num) receipt_num , rsh.shipment_num shipment_num , max(rsh.expected_receipt_date) expected_receipt_date , max(rsh.employee_id) employee_id , max(ppnfv.display_name) employee_display_name , max(rsl.transfer_order_line_id) transfer_order_line_id , max(rt.transaction_date) date_received from inv_transfer_order_lines itol2 , rcv_shipment_headers rsh , rcv_shipment_lines rsl , rcv_transactions rt , per_person_names_f_v ppnfv where rsh.shipment_header_id = rsl.shipment_header_id and rt.shipment_line_id = rsl.shipment_line_id and itol2.line_id = rsl.transfer_order_line_id and nvl(rt.direct_transfer_order_flag, 'N') = 'N' and rsh.employee_id = ppnfv.person_id (+) group by itol2.line_id, rsh.shipment_num ) rcv where wsh.shipment = rcv.shipment_num (+) and wsh.line_id = rcv.line_id (+)