eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MST_R_MASTER_ORDER_DET_V

Source

View Text - Preformatted


SELECT mdd.plan_id , 'NORMAL' , (select wlo.owner_type from wsh_location_owners wlo WHERE wlo.wsh_location_id = wl1.wsh_location_id) , flp1.facility_id , md.customer_id , md.supplier_id , mdd.reference_source_type , mdd.tp_refer_header_number , mdd.source_header_number , (select nvl(sum(mdd1.requested_quantity),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.split_from_delivery_detail_id is null) , (select nvl(sum(mdd1.gross_weight),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.split_from_delivery_detail_id is null) , (select nvl(sum(mdd1.volume),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.split_from_delivery_detail_id is null) , substr(mst_wb_util.get_local_chardt(md.dropoff_location_id, (select max(mdd1.earliest_acceptable_date) from mst_delivery_details mdd1 where mdd1.plan_id = mdd.plan_id and mdd1.delivery_detail_id = mdd.delivery_detail_id) ) ,1,20) , substr(mst_wb_util.get_local_chardt(md.dropoff_location_id, (select min(mdd1.latest_acceptable_date) from mst_delivery_details mdd1 where mdd1.plan_id = mdd.plan_id and mdd1.delivery_detail_id = mdd.delivery_detail_id) ) ,1,20) , (select nvl(sum(mdd1.allocated_cost),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number) , md.delivery_id , 1 , substr(wl1_d.location_code,0,10) , substr(mst_wb_util.get_name(wl1_d.wsh_location_id),1,80) , wl1_d.city , wl1_d.state , wl1_d.postal_code , substr(wl2_d.location_code,0,10) , substr(mst_wb_util.get_name(wl2_d.wsh_location_id),1,80) , wl2_d.city , wl2_d.state , wl2_d.postal_code , md.number_of_pieces , md.gross_weight , md.volume , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , ( select sum ( nvl ( mdl1.allocated_transport_cost, 0 ) ) from mst_delivery_legs mdl1 where mdl1.plan_id = mdl.plan_id and mdl1.delivery_id = mdl.delivery_id ) , mdl.sequence_number , mdl.delivery_leg_id , wlk.meaning , wc.freight_code , mt.trip_number , decode(mt.mode_of_transport,'TRUCK',mts1.stop_sequence_number,null) , decode(mt.mode_of_transport,'TRUCK',mts2.stop_sequence_number,null) , substr(mst_wb_util.get_name(mts1.stop_location_id),1,80) , mst_wb_util.r_checkif_orig_ispool (md.plan_id, md.delivery_id, mdl.delivery_leg_id, mdl.pick_up_stop_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mts1.stop_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mts1.stop_location_id) , substr(mst_wb_util.get_name(mts2.stop_location_id),1,80) , mst_wb_util.r_checkif_dest_ispool (md.plan_id, md.delivery_id, mdl.delivery_leg_id, mdl.drop_off_stop_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mts2.stop_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mts2.stop_location_id) , substr(mst_wb_util.get_local_chardt(mts1.stop_location_id,mts1.planned_departure_date),1,20) , substr(mst_wb_util.get_local_chardt(mts2.stop_location_id,mts2.planned_arrival_date),1,20) , nvl(mdl.allocated_transport_cost,0) from mst_plans mp , mst_delivery_details mdd , mst_delivery_assignments mda , mst_deliveries md , mst_delivery_legs mdl , mst_trips mt , (select mts.plan_id plan_id , mts.trip_id trip_id , mts.stop_location_id stop_location_id , mts.stop_id stop_id , mts.planned_departure_date planned_departure_date , mts.stop_sequence_number stop_sequence_number from mst_trip_stops mts) mts1 , (select mts.plan_id plan_id , mts.trip_id trip_id , mts.stop_location_id stop_location_id , mts.stop_id stop_id , mts.planned_arrival_date planned_arrival_date , mts.stop_sequence_number stop_sequence_number from mst_trip_stops mts) mts2 , wsh_locations wl1_d , wsh_locations wl2_d , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , mtl_system_items_kfv msikfv , mtl_system_items_tl msitl , wsh_carriers wc , wsh_lookups wlk WHERE mdd.plan_id = mp.plan_id and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mts1.plan_id = mt.plan_id and mts1.trip_id = mt.trip_id and mts1.stop_id = mdl.pick_up_stop_id and mts2.plan_id = mt.plan_id and mts2.trip_id = mt.trip_id and mts2.stop_id = mdl.drop_off_stop_id and wl1_d.wsh_location_id = md.pickup_location_id and wl2_d.wsh_location_id = md.dropoff_location_id and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_to_location_id = wl2.wsh_location_id and mdd.ship_from_location_id = flp1.location_id and mdd.ship_to_location_id = flp2.location_id and mdd.inventory_item_id = msikfv.inventory_item_id and mdd.organization_id = msikfv.organization_id and mdd.inventory_item_id = msitl.inventory_item_id and mdd.organization_id = msitl.organization_id and wc.carrier_id = mt.carrier_id and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wlk.lookup_code = mt.mode_of_transport and msitl.language = userenv('LANG') union all SELECT mdd.plan_id , 'FACILITY' , (select wlo.owner_type from wsh_location_owners wlo WHERE wlo.wsh_location_id = wl1.wsh_location_id) , flp1.facility_id , md.customer_id , md.supplier_id , mdd.reference_source_type , mdd.tp_refer_header_number , mdd.source_header_number , (select nvl(sum(mdd1.requested_quantity),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.ship_from_location_id = mdd.ship_from_location_id and mdd1.split_from_delivery_detail_id is null) , (select nvl(sum(mdd1.gross_weight),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.ship_from_location_id = mdd.ship_from_location_id and mdd1.split_from_delivery_detail_id is null) , (select nvl(sum(mdd1.volume),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.ship_from_location_id = mdd.ship_from_location_id and mdd1.split_from_delivery_detail_id is null) , substr(mst_wb_util.get_local_chardt(md.dropoff_location_id, (select max(mdd1.earliest_acceptable_date) from mst_delivery_details mdd1 where mdd1.plan_id = mdd.plan_id and mdd1.delivery_detail_id = mdd.delivery_detail_id and mdd1.ship_from_location_id = mdd.ship_from_location_id) ) ,1,20) , substr(mst_wb_util.get_local_chardt(md.dropoff_location_id, (select min(mdd1.latest_acceptable_date) from mst_delivery_details mdd1 where mdd1.plan_id = mdd.plan_id and mdd1.delivery_detail_id = mdd.delivery_detail_id and mdd1.ship_from_location_id = mdd.ship_from_location_id) ) ,1,20) , (select nvl(sum(mdd1.allocated_cost),0) from mst_delivery_details mdd1 where mdd1.plan_id = mp.plan_id and mdd1.source_header_number = mdd.source_header_number and mdd1.ship_from_location_id = mdd.ship_from_location_id) , md.delivery_id , 1 , substr(wl1_d.location_code,0,10) , substr(mst_wb_util.get_name(wl1_d.wsh_location_id),1,80) , wl1_d.city , wl1_d.state , wl1_d.postal_code , substr(wl2_d.location_code,0,10) , substr(mst_wb_util.get_name(wl2_d.wsh_location_id),1,80) , wl2_d.city , wl2_d.state , wl2_d.postal_code , md.number_of_pieces , md.gross_weight , md.volume , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , ( select sum ( nvl ( mdl1.allocated_transport_cost, 0 ) ) from mst_delivery_legs mdl1 where mdl1.plan_id = mdl.plan_id and mdl1.delivery_id = mdl.delivery_id ) , mdl.sequence_number , mdl.delivery_leg_id , wlk.meaning , wc.freight_code , mt.trip_number , decode(mt.mode_of_transport,'TRUCK',mts1.stop_sequence_number,null) , decode(mt.mode_of_transport,'TRUCK',mts2.stop_sequence_number,null) , substr(mst_wb_util.get_name(mts1.stop_location_id),1,80) , mst_wb_util.r_checkif_orig_ispool (md.plan_id, md.delivery_id, mdl.delivery_leg_id, mdl.pick_up_stop_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mts1.stop_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mts1.stop_location_id) , substr(mst_wb_util.get_name(mts2.stop_location_id),1,80) , mst_wb_util.r_checkif_dest_ispool (md.plan_id, md.delivery_id, mdl.delivery_leg_id, mdl.drop_off_stop_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mts2.stop_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mts2.stop_location_id) , substr(mst_wb_util.get_local_chardt(mts1.stop_location_id,mts1.planned_departure_date),1,20) , substr(mst_wb_util.get_local_chardt(mts2.stop_location_id,mts2.planned_arrival_date),1,20) , nvl(mdl.allocated_transport_cost,0) from mst_plans mp , mst_delivery_details mdd , mst_delivery_assignments mda , mst_deliveries md , mst_delivery_legs mdl , mst_trips mt , (select mts.plan_id plan_id , mts.trip_id trip_id , mts.stop_location_id stop_location_id , mts.stop_id stop_id , mts.planned_departure_date planned_departure_date , mts.stop_sequence_number stop_sequence_number from mst_trip_stops mts) mts1 , (select mts.plan_id plan_id , mts.trip_id trip_id , mts.stop_location_id stop_location_id , mts.stop_id stop_id , mts.planned_arrival_date planned_arrival_date , mts.stop_sequence_number stop_sequence_number from mst_trip_stops mts) mts2 , wsh_locations wl1_d , wsh_locations wl2_d , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , mtl_system_items_kfv msikfv , mtl_system_items_tl msitl , wsh_carriers wc , wsh_lookups wlk WHERE mdd.plan_id = mp.plan_id and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mts1.plan_id = mt.plan_id and mts1.trip_id = mt.trip_id and mts1.stop_id = mdl.pick_up_stop_id and mts2.plan_id = mt.plan_id and mts2.trip_id = mt.trip_id and mts2.stop_id = mdl.drop_off_stop_id and wl1_d.wsh_location_id = md.pickup_location_id and wl2_d.wsh_location_id = md.dropoff_location_id and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_to_location_id = wl2.wsh_location_id and mdd.ship_from_location_id = flp1.location_id and mdd.ship_to_location_id = flp2.location_id and mdd.inventory_item_id = msikfv.inventory_item_id and mdd.organization_id = msikfv.organization_id and mdd.inventory_item_id = msitl.inventory_item_id and mdd.organization_id = msitl.organization_id and wc.carrier_id = mt.carrier_id and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wlk.lookup_code = mt.mode_of_transport and msitl.language = userenv('LANG')

View Text - HTML Formatted

SELECT MDD.PLAN_ID
, 'NORMAL'
, (SELECT WLO.OWNER_TYPE
FROM WSH_LOCATION_OWNERS WLO
WHERE WLO.WSH_LOCATION_ID = WL1.WSH_LOCATION_ID)
, FLP1.FACILITY_ID
, MD.CUSTOMER_ID
, MD.SUPPLIER_ID
, MDD.REFERENCE_SOURCE_TYPE
, MDD.TP_REFER_HEADER_NUMBER
, MDD.SOURCE_HEADER_NUMBER
, (SELECT NVL(SUM(MDD1.REQUESTED_QUANTITY)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL)
, (SELECT NVL(SUM(MDD1.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL)
, (SELECT NVL(SUM(MDD1.VOLUME)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MD.DROPOFF_LOCATION_ID
, (SELECT MAX(MDD1.EARLIEST_ACCEPTABLE_DATE)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MDD.PLAN_ID
AND MDD1.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID) )
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MD.DROPOFF_LOCATION_ID
, (SELECT MIN(MDD1.LATEST_ACCEPTABLE_DATE)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MDD.PLAN_ID
AND MDD1.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID) )
, 1
, 20)
, (SELECT NVL(SUM(MDD1.ALLOCATED_COST)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER)
, MD.DELIVERY_ID
, 1
, SUBSTR(WL1_D.LOCATION_CODE
, 0
, 10)
, SUBSTR(MST_WB_UTIL.GET_NAME(WL1_D.WSH_LOCATION_ID)
, 1
, 80)
, WL1_D.CITY
, WL1_D.STATE
, WL1_D.POSTAL_CODE
, SUBSTR(WL2_D.LOCATION_CODE
, 0
, 10)
, SUBSTR(MST_WB_UTIL.GET_NAME(WL2_D.WSH_LOCATION_ID)
, 1
, 80)
, WL2_D.CITY
, WL2_D.STATE
, WL2_D.POSTAL_CODE
, MD.NUMBER_OF_PIECES
, MD.GROSS_WEIGHT
, MD.VOLUME
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, ( SELECT SUM ( NVL ( MDL1.ALLOCATED_TRANSPORT_COST
, 0 ) )
FROM MST_DELIVERY_LEGS MDL1
WHERE MDL1.PLAN_ID = MDL.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID )
, MDL.SEQUENCE_NUMBER
, MDL.DELIVERY_LEG_ID
, WLK.MEANING
, WC.FREIGHT_CODE
, MT.TRIP_NUMBER
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MTS1.STOP_SEQUENCE_NUMBER
, NULL)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MTS2.STOP_SEQUENCE_NUMBER
, NULL)
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS1.STOP_LOCATION_ID)
, 1
, 80)
, MST_WB_UTIL.R_CHECKIF_ORIG_ISPOOL (MD.PLAN_ID
, MD.DELIVERY_ID
, MDL.DELIVERY_LEG_ID
, MDL.PICK_UP_STOP_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS1.STOP_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS1.STOP_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS2.STOP_LOCATION_ID)
, 1
, 80)
, MST_WB_UTIL.R_CHECKIF_DEST_ISPOOL (MD.PLAN_ID
, MD.DELIVERY_ID
, MDL.DELIVERY_LEG_ID
, MDL.DROP_OFF_STOP_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS2.STOP_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS2.STOP_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, NVL(MDL.ALLOCATED_TRANSPORT_COST
, 0)
FROM MST_PLANS MP
, MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
, MST_TRIPS MT
, (SELECT MTS.PLAN_ID PLAN_ID
, MTS.TRIP_ID TRIP_ID
, MTS.STOP_LOCATION_ID STOP_LOCATION_ID
, MTS.STOP_ID STOP_ID
, MTS.PLANNED_DEPARTURE_DATE PLANNED_DEPARTURE_DATE
, MTS.STOP_SEQUENCE_NUMBER STOP_SEQUENCE_NUMBER
FROM MST_TRIP_STOPS MTS) MTS1
, (SELECT MTS.PLAN_ID PLAN_ID
, MTS.TRIP_ID TRIP_ID
, MTS.STOP_LOCATION_ID STOP_LOCATION_ID
, MTS.STOP_ID STOP_ID
, MTS.PLANNED_ARRIVAL_DATE PLANNED_ARRIVAL_DATE
, MTS.STOP_SEQUENCE_NUMBER STOP_SEQUENCE_NUMBER
FROM MST_TRIP_STOPS MTS) MTS2
, WSH_LOCATIONS WL1_D
, WSH_LOCATIONS WL2_D
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK
WHERE MDD.PLAN_ID = MP.PLAN_ID
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MT.PLAN_ID = MDL.PLAN_ID
AND MT.TRIP_ID = MDL.TRIP_ID
AND MTS1.PLAN_ID = MT.PLAN_ID
AND MTS1.TRIP_ID = MT.TRIP_ID
AND MTS1.STOP_ID = MDL.PICK_UP_STOP_ID
AND MTS2.PLAN_ID = MT.PLAN_ID
AND MTS2.TRIP_ID = MT.TRIP_ID
AND MTS2.STOP_ID = MDL.DROP_OFF_STOP_ID
AND WL1_D.WSH_LOCATION_ID = MD.PICKUP_LOCATION_ID
AND WL2_D.WSH_LOCATION_ID = MD.DROPOFF_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = FLP1.LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = FLP2.LOCATION_ID
AND MDD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MDD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND WC.CARRIER_ID = MT.CARRIER_ID
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WLK.LOOKUP_CODE = MT.MODE_OF_TRANSPORT
AND MSITL.LANGUAGE = USERENV('LANG') UNION ALL SELECT MDD.PLAN_ID
, 'FACILITY'
, (SELECT WLO.OWNER_TYPE
FROM WSH_LOCATION_OWNERS WLO
WHERE WLO.WSH_LOCATION_ID = WL1.WSH_LOCATION_ID)
, FLP1.FACILITY_ID
, MD.CUSTOMER_ID
, MD.SUPPLIER_ID
, MDD.REFERENCE_SOURCE_TYPE
, MDD.TP_REFER_HEADER_NUMBER
, MDD.SOURCE_HEADER_NUMBER
, (SELECT NVL(SUM(MDD1.REQUESTED_QUANTITY)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SHIP_FROM_LOCATION_ID = MDD.SHIP_FROM_LOCATION_ID
AND MDD1.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL)
, (SELECT NVL(SUM(MDD1.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SHIP_FROM_LOCATION_ID = MDD.SHIP_FROM_LOCATION_ID
AND MDD1.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL)
, (SELECT NVL(SUM(MDD1.VOLUME)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SHIP_FROM_LOCATION_ID = MDD.SHIP_FROM_LOCATION_ID
AND MDD1.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MD.DROPOFF_LOCATION_ID
, (SELECT MAX(MDD1.EARLIEST_ACCEPTABLE_DATE)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MDD.PLAN_ID
AND MDD1.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD1.SHIP_FROM_LOCATION_ID = MDD.SHIP_FROM_LOCATION_ID) )
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MD.DROPOFF_LOCATION_ID
, (SELECT MIN(MDD1.LATEST_ACCEPTABLE_DATE)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MDD.PLAN_ID
AND MDD1.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD1.SHIP_FROM_LOCATION_ID = MDD.SHIP_FROM_LOCATION_ID) )
, 1
, 20)
, (SELECT NVL(SUM(MDD1.ALLOCATED_COST)
, 0)
FROM MST_DELIVERY_DETAILS MDD1
WHERE MDD1.PLAN_ID = MP.PLAN_ID
AND MDD1.SOURCE_HEADER_NUMBER = MDD.SOURCE_HEADER_NUMBER
AND MDD1.SHIP_FROM_LOCATION_ID = MDD.SHIP_FROM_LOCATION_ID)
, MD.DELIVERY_ID
, 1
, SUBSTR(WL1_D.LOCATION_CODE
, 0
, 10)
, SUBSTR(MST_WB_UTIL.GET_NAME(WL1_D.WSH_LOCATION_ID)
, 1
, 80)
, WL1_D.CITY
, WL1_D.STATE
, WL1_D.POSTAL_CODE
, SUBSTR(WL2_D.LOCATION_CODE
, 0
, 10)
, SUBSTR(MST_WB_UTIL.GET_NAME(WL2_D.WSH_LOCATION_ID)
, 1
, 80)
, WL2_D.CITY
, WL2_D.STATE
, WL2_D.POSTAL_CODE
, MD.NUMBER_OF_PIECES
, MD.GROSS_WEIGHT
, MD.VOLUME
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, ( SELECT SUM ( NVL ( MDL1.ALLOCATED_TRANSPORT_COST
, 0 ) )
FROM MST_DELIVERY_LEGS MDL1
WHERE MDL1.PLAN_ID = MDL.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID )
, MDL.SEQUENCE_NUMBER
, MDL.DELIVERY_LEG_ID
, WLK.MEANING
, WC.FREIGHT_CODE
, MT.TRIP_NUMBER
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MTS1.STOP_SEQUENCE_NUMBER
, NULL)
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, MTS2.STOP_SEQUENCE_NUMBER
, NULL)
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS1.STOP_LOCATION_ID)
, 1
, 80)
, MST_WB_UTIL.R_CHECKIF_ORIG_ISPOOL (MD.PLAN_ID
, MD.DELIVERY_ID
, MDL.DELIVERY_LEG_ID
, MDL.PICK_UP_STOP_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS1.STOP_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS1.STOP_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS2.STOP_LOCATION_ID)
, 1
, 80)
, MST_WB_UTIL.R_CHECKIF_DEST_ISPOOL (MD.PLAN_ID
, MD.DELIVERY_ID
, MDL.DELIVERY_LEG_ID
, MDL.DROP_OFF_STOP_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS2.STOP_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS2.STOP_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, NVL(MDL.ALLOCATED_TRANSPORT_COST
, 0)
FROM MST_PLANS MP
, MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
, MST_TRIPS MT
, (SELECT MTS.PLAN_ID PLAN_ID
, MTS.TRIP_ID TRIP_ID
, MTS.STOP_LOCATION_ID STOP_LOCATION_ID
, MTS.STOP_ID STOP_ID
, MTS.PLANNED_DEPARTURE_DATE PLANNED_DEPARTURE_DATE
, MTS.STOP_SEQUENCE_NUMBER STOP_SEQUENCE_NUMBER
FROM MST_TRIP_STOPS MTS) MTS1
, (SELECT MTS.PLAN_ID PLAN_ID
, MTS.TRIP_ID TRIP_ID
, MTS.STOP_LOCATION_ID STOP_LOCATION_ID
, MTS.STOP_ID STOP_ID
, MTS.PLANNED_ARRIVAL_DATE PLANNED_ARRIVAL_DATE
, MTS.STOP_SEQUENCE_NUMBER STOP_SEQUENCE_NUMBER
FROM MST_TRIP_STOPS MTS) MTS2
, WSH_LOCATIONS WL1_D
, WSH_LOCATIONS WL2_D
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK
WHERE MDD.PLAN_ID = MP.PLAN_ID
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MT.PLAN_ID = MDL.PLAN_ID
AND MT.TRIP_ID = MDL.TRIP_ID
AND MTS1.PLAN_ID = MT.PLAN_ID
AND MTS1.TRIP_ID = MT.TRIP_ID
AND MTS1.STOP_ID = MDL.PICK_UP_STOP_ID
AND MTS2.PLAN_ID = MT.PLAN_ID
AND MTS2.TRIP_ID = MT.TRIP_ID
AND MTS2.STOP_ID = MDL.DROP_OFF_STOP_ID
AND WL1_D.WSH_LOCATION_ID = MD.PICKUP_LOCATION_ID
AND WL2_D.WSH_LOCATION_ID = MD.DROPOFF_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = FLP1.LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = FLP2.LOCATION_ID
AND MDD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MDD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND WC.CARRIER_ID = MT.CARRIER_ID
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WLK.LOOKUP_CODE = MT.MODE_OF_TRANSPORT
AND MSITL.LANGUAGE = USERENV('LANG')