VIEW: APPS.MST_ACT_OPPLOADS_V

Source

View Text - Preformatted

SELECT mt2.plan_id PLAN_ID, mt2.trip_id TRIP_ID, mt2.mode_of_transport MODE_OF_TRANSPORT, mt1.trip_id MASTER_TRIP_ID, mt2.trip_number TRIP_OR_DELIVERY_NUMBER, mt2.carrier_id CARRIER_ID, to_number(null) DELIVERY_ID, mt2.origin_location_id ORIGIN_LOCATION_ID, mt2.destination_location_id DESTINATION_LOCATION_ID, flp1.facility_id ORIGIN_FACILITY_ID, flp2.facility_id DESTINATION_FACILITY_ID, wc.freight_code CARRIER_NAME, wlk.meaning SERVICE_LEVEL, substr(MST_WB_UTIL.GET_NAME(mt2.origin_location_id),1,80) ORIGIN_COMPANY, flp1.facility_code ORIGIN_FACILITY, flp1.description ORIGIN_FACILITY_DESC, wl1.city ORIGIN_CITY, wl1.state ORIGIN_STATE, wl1.postal_code ORIGIN_ZIP, wl1.country ORIGIN_COUNTRY, substr(MST_WB_UTIL.GET_NAME(mt2.destination_location_id),1,80) DESTINATION_COMPANY, flp2.facility_code DESTINATION_FACILITY, flp2.description DESTINATION_FACILITY_DESC, wl2.city DESTINATION_CITY, wl2.state DESTINATION_STATE, wl2.postal_code DESTINATION_ZIP, wl2.country DESTINATION_COUNTRY, nvl(mt2.total_basic_transport_cost,0) + nvl(mt2.total_stop_cost, 0) + nvl(mt2.total_load_unload_cost,0) + nvl(mt2.total_layover_cost, 0) + nvl(mt2.total_accessorial_cost, 0) + nvl(mt2.total_handling_cost, 0) TOTAL_COST, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(mt2.plan_id, mt2.trip_id) TOTAL_WEIGHT, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt2.plan_id, mt2.trip_id) TOTAL_CUBE, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(mt2.plan_id, mt2.trip_id) TOTAL_PALLETS, MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(mt2.plan_id, mt2.trip_id) TOTAL_PIECES, mp.weight_uom WEIGHT_UOM, mp.volume_uom VOLUME_UOM, mp.currency_uom COST_UOM, wlk1.meaning MODE_OF_TRANSPORT_TL, greatest(decode(nvl(mt1.effective_vol_capacity, 0), 0, 0, (MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt1.plan_id, mt1.trip_id) + MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt2.plan_id, mt2.trip_id)) /mt1.effective_vol_capacity), decode(nvl(msikfv.maximum_load_weight, 0), 0, 0, (MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(mt1.plan_id, mt1.trip_id) + MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(mt2.plan_id, mt2.trip_id)) /msikfv.maximum_load_weight), decode(nvl((fvt.pallet_floor_space*fvt.pallet_stacking_height), 0), 0, 0, (MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(mt1.plan_id, mt1.trip_id) + MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(mt2.plan_id, mt2.trip_id)) /(fvt.pallet_floor_space*fvt.pallet_stacking_height)) ) * 100 UTILIZATION_FACTOR, 'TRIP' OPPORTUNITY_TYPE from mst_plans mp , mst_trips mt1 , mst_trips mt2 , wsh_carriers wc , wsh_locations wl1 , wsh_locations wl2 , wsh_lookups wlk, wsh_lookups wlk1, fte_vehicle_types fvt, mtl_system_items_kfv msikfv, fte_location_parameters flp1 , fte_location_parameters flp2 WHERE mp.plan_id = mt1.plan_id and mt1.plan_id = mt2.plan_id and mt1.vehicle_type_id = fvt.vehicle_type_id and fvt.organization_id = msikfv.organization_id and fvt.inventory_item_id = msikfv.inventory_item_id and mt2.mode_of_transport in ('LTL', 'PARCEL') and nvl(mt2.planned_flag, 3) = 3 and mt2.carrier_id = wc.carrier_id and mt2.origin_location_id = wl1.wsh_location_id and mt2.destination_location_id = wl2.wsh_location_id and mt2.origin_location_id = flp1.location_id and mt2.destination_location_id = flp2.location_id and mt2.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' and mt2.mode_of_transport = wlk1.lookup_code and wlk1.lookup_type = 'WSH_MODE_OF_TRANSPORT' union all SELECT distinct md.plan_id PLAN_ID, to_number(null) TRIP_ID, null MODE_OF_TRANSPORT, mt1.trip_id MASTER_TRIP_ID, md.delivery_number TRIP_OR_DELIVERY_NUMBER, to_number(null) CARRIER_ID, md.delivery_id DELIVERY_ID, md.pickup_location_id ORIGIN_LOCATION_ID, md.dropoff_location_id DESTINATION_LOCATION_ID, flp1.facility_id ORIGIN_FACILITY_ID, flp2.facility_id DESTINATION_FACILITY_ID, null CARRIER_NAME, null SERVICE_LEVEL, substr(MST_WB_UTIL.GET_NAME(md.pickup_location_id),1,80) ORIGIN_COMPANY, flp1.facility_code ORIGIN_FACILITY, flp1.description ORIGIN_FACILITY_DESC, wl1.city ORIGIN_CITY, wl1.state ORIGIN_STATE, wl1.postal_code ORIGIN_ZIP, wl1.country ORIGIN_COUNTRY, substr(MST_WB_UTIL.GET_NAME(md.dropoff_location_id),1,80) DESTINATION_COMPANY, flp2.facility_code DESTINATION_FACILITY, flp2.description DESTINATION_FACILITY_DESC, wl2.city DESTINATION_CITY, wl2.state DESTINATION_STATE, wl2.postal_code DESTINATION_ZIP, wl2.country DESTINATION_COUNTRY, to_number(null) TOTAL_COST, nvl(md.gross_weight, 0) TOTAL_WEIGHT, nvl(md.volume, 0) TOTAL_CUBE, nvl(md.number_of_pallets, 0) TOTAL_PALLETS, nvl(md.number_of_pieces, 0) TOTAL_PIECES, mp.weight_uom WEIGHT_UOM, mp.volume_uom VOLUME_UOM, mp.currency_uom COST_UOM, MST_WB_UTIL.GET_MEANING('MST_STRING', 36, 'MFG') MODE_OF_TRANSPORT_TL, greatest(decode(nvl(mt1.effective_vol_capacity, 0), 0, 0, (MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt1.plan_id, mt1.trip_id) + nvl(md.volume, 0)) /mt1.effective_vol_capacity), decode(nvl(msikfv.maximum_load_weight, 0), 0, 0, (MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(mt1.plan_id, mt1.trip_id) + nvl(md.gross_weight, 0)) /msikfv.maximum_load_weight), decode(nvl((fvt.pallet_floor_space*fvt.pallet_stacking_height), 0), 0, 0, (MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(mt1.plan_id, mt1.trip_id) + nvl(md.number_of_pallets, 0)) /fvt.pallet_floor_space*fvt.pallet_stacking_height) ) * 100 UTILIZATION_FACTOR, 'ORDER' OPPORTUNITY_TYPE from mst_plans mp, mst_trips mt1, mst_deliveries md, wsh_locations wl1 , wsh_locations wl2 , wsh_lookups wlk, fte_vehicle_types fvt, mtl_system_items_kfv msikfv, fte_location_parameters flp1 , fte_location_parameters flp2 WHERE mp.plan_id = mt1.plan_id and mt1.plan_id = md.plan_id and mt1.vehicle_type_id = fvt.vehicle_type_id and fvt.organization_id = msikfv.organization_id and fvt.inventory_item_id = msikfv.inventory_item_id and md.pickup_location_id = wl1.wsh_location_id and md.dropoff_location_id = wl2.wsh_location_id and md.pickup_location_id = flp1.location_id and md.dropoff_location_id = flp2.location_id and md.delivery_id not in ( select delivery_id from mst_delivery_legs mdl where mdl.plan_id = md.plan_id )

View Text - HTML Formatted

SELECT MT2.PLAN_ID PLAN_ID
, MT2.TRIP_ID TRIP_ID
, MT2.MODE_OF_TRANSPORT MODE_OF_TRANSPORT
, MT1.TRIP_ID MASTER_TRIP_ID
, MT2.TRIP_NUMBER TRIP_OR_DELIVERY_NUMBER
, MT2.CARRIER_ID CARRIER_ID
, TO_NUMBER(NULL) DELIVERY_ID
, MT2.ORIGIN_LOCATION_ID ORIGIN_LOCATION_ID
, MT2.DESTINATION_LOCATION_ID DESTINATION_LOCATION_ID
, FLP1.FACILITY_ID ORIGIN_FACILITY_ID
, FLP2.FACILITY_ID DESTINATION_FACILITY_ID
, WC.FREIGHT_CODE CARRIER_NAME
, WLK.MEANING SERVICE_LEVEL
, SUBSTR(MST_WB_UTIL.GET_NAME(MT2.ORIGIN_LOCATION_ID)
, 1
, 80) ORIGIN_COMPANY
, FLP1.FACILITY_CODE ORIGIN_FACILITY
, FLP1.DESCRIPTION ORIGIN_FACILITY_DESC
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT2.DESTINATION_LOCATION_ID)
, 1
, 80) DESTINATION_COMPANY
, FLP2.FACILITY_CODE DESTINATION_FACILITY
, FLP2.DESCRIPTION DESTINATION_FACILITY_DESC
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, NVL(MT2.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT2.TOTAL_STOP_COST
, 0) + NVL(MT2.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT2.TOTAL_LAYOVER_COST
, 0) + NVL(MT2.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT2.TOTAL_HANDLING_COST
, 0) TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT2.PLAN_ID
, MT2.TRIP_ID) TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT2.PLAN_ID
, MT2.TRIP_ID) TOTAL_CUBE
, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT2.PLAN_ID
, MT2.TRIP_ID) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(MT2.PLAN_ID
, MT2.TRIP_ID) TOTAL_PIECES
, MP.WEIGHT_UOM WEIGHT_UOM
, MP.VOLUME_UOM VOLUME_UOM
, MP.CURRENCY_UOM COST_UOM
, WLK1.MEANING MODE_OF_TRANSPORT_TL
, GREATEST(DECODE(NVL(MT1.EFFECTIVE_VOL_CAPACITY
, 0)
, 0
, 0
, (MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT1.PLAN_ID
, MT1.TRIP_ID) + MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT2.PLAN_ID
, MT2.TRIP_ID)) /MT1.EFFECTIVE_VOL_CAPACITY)
, DECODE(NVL(MSIKFV.MAXIMUM_LOAD_WEIGHT
, 0)
, 0
, 0
, (MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT1.PLAN_ID
, MT1.TRIP_ID) + MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT2.PLAN_ID
, MT2.TRIP_ID)) /MSIKFV.MAXIMUM_LOAD_WEIGHT)
, DECODE(NVL((FVT.PALLET_FLOOR_SPACE*FVT.PALLET_STACKING_HEIGHT)
, 0)
, 0
, 0
, (MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT1.PLAN_ID
, MT1.TRIP_ID) + MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT2.PLAN_ID
, MT2.TRIP_ID)) /(FVT.PALLET_FLOOR_SPACE*FVT.PALLET_STACKING_HEIGHT)) ) * 100 UTILIZATION_FACTOR
, 'TRIP' OPPORTUNITY_TYPE
FROM MST_PLANS MP
, MST_TRIPS MT1
, MST_TRIPS MT2
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
WHERE MP.PLAN_ID = MT1.PLAN_ID
AND MT1.PLAN_ID = MT2.PLAN_ID
AND MT1.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MT2.MODE_OF_TRANSPORT IN ('LTL'
, 'PARCEL')
AND NVL(MT2.PLANNED_FLAG
, 3) = 3
AND MT2.CARRIER_ID = WC.CARRIER_ID
AND MT2.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT2.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT2.ORIGIN_LOCATION_ID = FLP1.LOCATION_ID
AND MT2.DESTINATION_LOCATION_ID = FLP2.LOCATION_ID
AND MT2.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND MT2.MODE_OF_TRANSPORT = WLK1.LOOKUP_CODE
AND WLK1.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT' UNION ALL SELECT DISTINCT MD.PLAN_ID PLAN_ID
, TO_NUMBER(NULL) TRIP_ID
, NULL MODE_OF_TRANSPORT
, MT1.TRIP_ID MASTER_TRIP_ID
, MD.DELIVERY_NUMBER TRIP_OR_DELIVERY_NUMBER
, TO_NUMBER(NULL) CARRIER_ID
, MD.DELIVERY_ID DELIVERY_ID
, MD.PICKUP_LOCATION_ID ORIGIN_LOCATION_ID
, MD.DROPOFF_LOCATION_ID DESTINATION_LOCATION_ID
, FLP1.FACILITY_ID ORIGIN_FACILITY_ID
, FLP2.FACILITY_ID DESTINATION_FACILITY_ID
, NULL CARRIER_NAME
, NULL SERVICE_LEVEL
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.PICKUP_LOCATION_ID)
, 1
, 80) ORIGIN_COMPANY
, FLP1.FACILITY_CODE ORIGIN_FACILITY
, FLP1.DESCRIPTION ORIGIN_FACILITY_DESC
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MD.DROPOFF_LOCATION_ID)
, 1
, 80) DESTINATION_COMPANY
, FLP2.FACILITY_CODE DESTINATION_FACILITY
, FLP2.DESCRIPTION DESTINATION_FACILITY_DESC
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, TO_NUMBER(NULL) TOTAL_COST
, NVL(MD.GROSS_WEIGHT
, 0) TOTAL_WEIGHT
, NVL(MD.VOLUME
, 0) TOTAL_CUBE
, NVL(MD.NUMBER_OF_PALLETS
, 0) TOTAL_PALLETS
, NVL(MD.NUMBER_OF_PIECES
, 0) TOTAL_PIECES
, MP.WEIGHT_UOM WEIGHT_UOM
, MP.VOLUME_UOM VOLUME_UOM
, MP.CURRENCY_UOM COST_UOM
, MST_WB_UTIL.GET_MEANING('MST_STRING'
, 36
, 'MFG') MODE_OF_TRANSPORT_TL
, GREATEST(DECODE(NVL(MT1.EFFECTIVE_VOL_CAPACITY
, 0)
, 0
, 0
, (MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT1.PLAN_ID
, MT1.TRIP_ID) + NVL(MD.VOLUME
, 0)) /MT1.EFFECTIVE_VOL_CAPACITY)
, DECODE(NVL(MSIKFV.MAXIMUM_LOAD_WEIGHT
, 0)
, 0
, 0
, (MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT1.PLAN_ID
, MT1.TRIP_ID) + NVL(MD.GROSS_WEIGHT
, 0)) /MSIKFV.MAXIMUM_LOAD_WEIGHT)
, DECODE(NVL((FVT.PALLET_FLOOR_SPACE*FVT.PALLET_STACKING_HEIGHT)
, 0)
, 0
, 0
, (MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT1.PLAN_ID
, MT1.TRIP_ID) + NVL(MD.NUMBER_OF_PALLETS
, 0)) /FVT.PALLET_FLOOR_SPACE*FVT.PALLET_STACKING_HEIGHT) ) * 100 UTILIZATION_FACTOR
, 'ORDER' OPPORTUNITY_TYPE
FROM MST_PLANS MP
, MST_TRIPS MT1
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_LOOKUPS WLK
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
WHERE MP.PLAN_ID = MT1.PLAN_ID
AND MT1.PLAN_ID = MD.PLAN_ID
AND MT1.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MD.PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MD.PICKUP_LOCATION_ID = FLP1.LOCATION_ID
AND MD.DROPOFF_LOCATION_ID = FLP2.LOCATION_ID
AND MD.DELIVERY_ID NOT IN ( SELECT DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID )