DBA Data[Home] [Help]

VIEW: APPS.MST_TS_DELIVERIES_V

Source

View Text - Preformatted

SELECT md.plan_id , mts.stop_id , md.delivery_id , md.delivery_number , md.pickup_location_id , md.dropoff_location_id , substr(mst_wb_util.get_name(md.pickup_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(md.dropoff_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , ml1.meaning , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.latest_pickup_date),1,40) , md.earliest_pickup_date , md.latest_pickup_date , nvl(md.gross_weight,0) , nvl(md.volume,0) , nvl(md.number_of_pallets,0) , nvl(md.number_of_pieces,0) , (select count(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id)) from mst_delivery_details mdd , mst_delivery_assignments mda WHERE mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.parent_delivery_detail_id is null and mda.delivery_id = md.delivery_id and mda.plan_id = md.plan_id ) , mp.weight_uom , mp.volume_uom , mp.currency_uom , (select nvl(sum(mdl1.allocated_transport_cost),0) + nvl(sum(mdl1.allocated_fac_shp_hand_cost),0) + nvl(sum(mdl1.allocated_fac_rec_hand_cost),0) + nvl(sum(mdl1.allocated_fac_loading_cost),0) + nvl(sum(mdl1.allocated_fac_unloading_cost),0) from mst_delivery_legs mdl1 where mdl1.plan_id = md.plan_id and mdl1.delivery_id = md.delivery_id) , md.direct_cost , ml2.meaning from mst_plans mp , mst_trip_stops mts , mst_delivery_legs mdl , mst_deliveries md , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , wsh_locations wl , mfg_lookups ml1 , mfg_lookups ml2 where mp.plan_id = mts.plan_id and mts.plan_id = mdl.plan_id and mts.stop_id = mdl.pick_up_stop_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and md.pickup_location_id = wl1.wsh_location_id and md.dropoff_location_id = wl2.wsh_location_id and wl1.wsh_location_id = flp1.location_id and wl2.wsh_location_id = flp2.location_id and mts.stop_location_id = wl.wsh_location_id and ml2.lookup_code = decode(md.planned_flag, 1, 1 , 2, decode(md.preserve_grouping_flag, 1, 2 , 2, 3 , 3 ) , 3 ) and ml2.lookup_type = 'MST_DELIVERY_PLANNED_FLAG' and ml1.lookup_code = 31 and ml1.lookup_type = 'MST_STRING' UNION ALL select md.plan_id , mts.stop_id , md.delivery_id , md.delivery_number , md.pickup_location_id , md.dropoff_location_id , substr(mst_wb_util.get_name(md.pickup_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(md.dropoff_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , ml1.meaning , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.earliest_acceptable_date),1,40) , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.latest_acceptable_date),1,40) , md.earliest_acceptable_date , md.latest_acceptable_date , nvl(md.gross_weight,0) , nvl(md.volume,0) , nvl(md.number_of_pallets,0) , nvl(md.number_of_pieces,0) , (select count(mdd.delivery_detail_id) from mst_delivery_details mdd , mst_delivery_assignments mda where mdd.split_from_delivery_detail_id is null and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.delivery_id = md.delivery_id and mda.plan_id = md.plan_id ) , mp.weight_uom , mp.volume_uom , mp.currency_uom , (select nvl(sum(mdl1.allocated_transport_cost),0) + nvl(sum(mdl1.allocated_fac_shp_hand_cost),0) + nvl(sum(mdl1.allocated_fac_rec_hand_cost),0) + nvl(sum(mdl1.allocated_fac_loading_cost),0) + nvl(sum(mdl1.allocated_fac_unloading_cost),0) from mst_delivery_legs mdl1 where mdl1.plan_id = md.plan_id and mdl1.delivery_id = md.delivery_id) , nvl(md.direct_cost,0) , ml2.meaning from mst_plans mp , mst_trip_stops mts , mst_delivery_legs mdl , mst_deliveries md , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , wsh_locations wl , mfg_lookups ml1 , mfg_lookups ml2 where mp.plan_id = mts.plan_id and mts.plan_id = mdl.plan_id and mts.stop_id = mdl.drop_off_stop_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and md.pickup_location_id = wl1.wsh_location_id and md.dropoff_location_id = wl2.wsh_location_id and wl1.wsh_location_id = flp1.location_id and wl2.wsh_location_id = flp2.location_id and mts.stop_location_id = wl.wsh_location_id and ml2.lookup_code = decode(md.planned_flag, 1, 1 , 2, decode(md.preserve_grouping_flag, 1, 2 , 2, 3 , 3 ) , 3 ) and ml2.lookup_type = 'MST_DELIVERY_PLANNED_FLAG' and ml1.lookup_code = 32 and ml1.lookup_type = 'MST_STRING'
View Text - HTML Formatted

SELECT MD.PLAN_ID
, MTS.STOP_ID
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
, MD.PICKUP_LOCATION_ID
, MD.DROPOFF_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.PICKUP_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.DROPOFF_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, ML1.MEANING
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.LATEST_PICKUP_DATE)
, 1
, 40)
, MD.EARLIEST_PICKUP_DATE
, MD.LATEST_PICKUP_DATE
, NVL(MD.GROSS_WEIGHT
, 0)
, NVL(MD.VOLUME
, 0)
, NVL(MD.NUMBER_OF_PALLETS
, 0)
, NVL(MD.NUMBER_OF_PIECES
, 0)
, (SELECT COUNT(DISTINCT NVL(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID))
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
WHERE MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PARENT_DELIVERY_DETAIL_ID IS NULL
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDA.PLAN_ID = MD.PLAN_ID )
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MP.CURRENCY_UOM
, (SELECT NVL(SUM(MDL1.ALLOCATED_TRANSPORT_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_SHP_HAND_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_REC_HAND_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_LOADING_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_UNLOADING_COST)
, 0)
FROM MST_DELIVERY_LEGS MDL1
WHERE MDL1.PLAN_ID = MD.PLAN_ID
AND MDL1.DELIVERY_ID = MD.DELIVERY_ID)
, MD.DIRECT_COST
, ML2.MEANING
FROM MST_PLANS MP
, MST_TRIP_STOPS MTS
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOCATIONS WL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
WHERE MP.PLAN_ID = MTS.PLAN_ID
AND MTS.PLAN_ID = MDL.PLAN_ID
AND MTS.STOP_ID = MDL.PICK_UP_STOP_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = FLP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = FLP2.LOCATION_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID
AND ML2.LOOKUP_CODE = DECODE(MD.PLANNED_FLAG
, 1
, 1
, 2
, DECODE(MD.PRESERVE_GROUPING_FLAG
, 1
, 2
, 2
, 3
, 3 )
, 3 )
AND ML2.LOOKUP_TYPE = 'MST_DELIVERY_PLANNED_FLAG'
AND ML1.LOOKUP_CODE = 31
AND ML1.LOOKUP_TYPE = 'MST_STRING' UNION ALL SELECT MD.PLAN_ID
, MTS.STOP_ID
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
, MD.PICKUP_LOCATION_ID
, MD.DROPOFF_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.PICKUP_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.DROPOFF_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, ML1.MEANING
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MD.EARLIEST_ACCEPTABLE_DATE
, MD.LATEST_ACCEPTABLE_DATE
, NVL(MD.GROSS_WEIGHT
, 0)
, NVL(MD.VOLUME
, 0)
, NVL(MD.NUMBER_OF_PALLETS
, 0)
, NVL(MD.NUMBER_OF_PIECES
, 0)
, (SELECT COUNT(MDD.DELIVERY_DETAIL_ID)
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
WHERE MDD.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDA.PLAN_ID = MD.PLAN_ID )
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MP.CURRENCY_UOM
, (SELECT NVL(SUM(MDL1.ALLOCATED_TRANSPORT_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_SHP_HAND_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_REC_HAND_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_LOADING_COST)
, 0) + NVL(SUM(MDL1.ALLOCATED_FAC_UNLOADING_COST)
, 0)
FROM MST_DELIVERY_LEGS MDL1
WHERE MDL1.PLAN_ID = MD.PLAN_ID
AND MDL1.DELIVERY_ID = MD.DELIVERY_ID)
, NVL(MD.DIRECT_COST
, 0)
, ML2.MEANING
FROM MST_PLANS MP
, MST_TRIP_STOPS MTS
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOCATIONS WL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
WHERE MP.PLAN_ID = MTS.PLAN_ID
AND MTS.PLAN_ID = MDL.PLAN_ID
AND MTS.STOP_ID = MDL.DROP_OFF_STOP_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = FLP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = FLP2.LOCATION_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID
AND ML2.LOOKUP_CODE = DECODE(MD.PLANNED_FLAG
, 1
, 1
, 2
, DECODE(MD.PRESERVE_GROUPING_FLAG
, 1
, 2
, 2
, 3
, 3 )
, 3 )
AND ML2.LOOKUP_TYPE = 'MST_DELIVERY_PLANNED_FLAG'
AND ML1.LOOKUP_CODE = 32
AND ML1.LOOKUP_TYPE = 'MST_STRING'