[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(COST_DISTANCE_ALLOC_METHOD,1)
FROM mst_parameters
WHERE user_Id = -9999;
SELECT PLAN_ID, currency_uom
FROM mst_plans
WHERE plan_id = p_plan_id;
SELECT wl.wsh_location_id
FROM wsh_locations wl,
fte_location_parameters fte
WHERE wl.wsh_location_id = fte.location_id
AND fte.facility_id = p_facility_id;
SELECT T.TotalCost/NVL(P.TOTAL_WEIGHT, 1) AS CostPerUnitWeight
FROM (SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_Id
GROUP BY T1.PLAN_ID) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;*/
SELECT NVL(P.TOTAL_PLAN_COST,0)/DECODE((
NVL(P.TOTAL_TL_WEIGHT, 0) +
NVL(P.TOTAL_LTL_WEIGHT, 0) +
NVL(P.TOTAL_PARCEL_WEIGHT, 0) ),0,1,
(NVL(P.TOTAL_TL_WEIGHT, 0) +
NVL(P.TOTAL_LTL_WEIGHT, 0) +
NVL(P.TOTAL_PARCEL_WEIGHT, 0)))
AS CostPerUnitWeight
FROM MST_PLANS P
WHERE P.PLAN_ID = P_PLAN_ID;
SELECT T.TotalCost/NVL(P.TOTAL_TL_WEIGHT, 1) CostPerUnitWeight
FROM (SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload -- 'TRUCK'
GROUP BY T1.PLAN_ID ) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;
SELECT T.TotalCost/NVL(P.TOTAL_LTL_WEIGHT, 1) CostPerUnitWeight
FROM (SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
NVL(T1.TOTAL_HANDLING_COST ,0 ) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_ltl
GROUP BY T1.PLAN_ID ) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;
SELECT T.TotalCost/NVL(P.TOTAL_PARCEL_WEIGHT, 1) CostPerUnitWeight
FROM (SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
NVL(T1.TOTAL_HANDLING_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_parcel
GROUP BY T1.PLAN_ID ) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;
SELECT T11.TotalCost/NVL(T12.TotalWeight, 1) CostPerUnitWeight
FROM (SELECT SUM(
NVL(T1.TOTAL_HANDLING_COST, 0) +
NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_Id
AND T1.CARRIER_ID = p_carrier_id
) T11,
(SELECT SUM(D.GROSS_WEIGHT) TotalWeight
FROM MST_DELIVERIES D
WHERE D.PLAN_ID = p_plan_id
AND D.DELIVERY_ID IN
( SELECT DL.DELIVERY_ID
FROM MST_DELIVERY_LEGS DL,
MST_TRIPS T2
WHERE dl.plan_id = d.plan_id
AND T2.PLAN_ID = dl.PLAN_ID
AND T2.TRIP_ID = dl.TRIP_ID
AND T2.CARRIER_ID = p_carrier_id
)
)T12;
SELECT T1.TotalCost/T2.TotalWeight CostPerUnitWeight
FROM (SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D
WHERE DL.PLAN_ID = p_plan_Id
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.CUSTOMER_ID = p_customer_id
) T1,
(SELECT SUM(D1.GROSS_WEIGHT) TotalWeight
FROM MST_DELIVERIES D1
WHERE D1.PLAN_ID = p_plan_id
AND D1.CUSTOMER_ID = p_customer_id
AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
FROM MST_DELIVERY_LEGS DL1
WHERE DL1.PLAN_ID = D1.PLAN_ID
AND DL1.DELIVERY_ID = D1.DELIVERY_ID
)
) T2;
SELECT T1.TotalCost/T2.TotalWeight CostPerUnitWeight
FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D
WHERE DL.PLAN_ID = p_plan_id
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.SUPPLIER_ID = p_Supplier_ID
) T1,
( SELECT SUM(D1.GROSS_WEIGHT) TotalWeight
FROM MST_DELIVERIES D1
WHERE D1.PLAN_ID = p_plan_id
AND D1.SUPPLIER_ID = p_Supplier_ID
AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
FROM MST_DELIVERY_LEGS DL1
WHERE DL1.PLAN_ID = D1.PLAN_ID
AND DL1.DELIVERY_ID = D1.DELIVERY_ID
)
) T2;
SELECT T1.TotalCost/T2.TotalWeight CostPerUnitWeight
FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D
WHERE DL.PLAN_ID = p_plan_id
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND ( D.PICKUP_LOCATION_ID = p_location_id
OR D.DROPOFF_LOCATION_ID = p_location_id )
) T1,
( SELECT SUM(D1.GROSS_WEIGHT) TotalWeight
FROM MST_DELIVERIES D1
WHERE D1.PLAN_ID = p_plan_Id
AND ( D1.DROPOFF_LOCATION_ID = p_location_id
OR D1.PICKUP_LOCATION_ID = p_location_id )
AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
FROM MST_DELIVERY_LEGS DL1
WHERE DL1.DELIVERY_ID = D1.DELIVERY_ID
AND DL1.PLAN_ID = D1.PLAN_ID
)
) T2;
SELECT (T.TotalCost/NVL(P.TOTAL_VOLUME,1)) AS CostPerUnitVolume
FROM ( SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE t1.PLAN_ID = p_plan_id
GROUP BY T1.PLAN_ID
) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;*/
SELECT NVL(P.TOTAL_PLAN_COST,0)/DECODE((
NVL(P.TOTAL_TL_VOLUME, 0) +
NVL(P.TOTAL_LTL_VOLUME, 0) +
NVL(P.TOTAL_PARCEL_VOLUME, 0) ),0,1,
(NVL(P.TOTAL_TL_VOLUME, 0) +
NVL(P.TOTAL_LTL_VOLUME, 0) +
NVL(P.TOTAL_PARCEL_VOLUME, 0)))
AS CostPerUnitVolume
FROM MST_PLANS P
WHERE P.PLAN_ID = P_PLAN_ID;
SELECT T.TotalCost/NVL(P.TOTAL_TL_VOLUME, 1) AS CostPerUnitVolume
FROM ( SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
GROUP BY T1.PLAN_ID) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;
SELECT T.TotalCost/NVL(P.TOTAL_LTL_VOLUME, 1) AS CostPerUnitVolume
FROM ( SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
NVL(T1.TOTAL_HANDLING_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_ltl
GROUP BY T1.PLAN_ID) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;
SELECT T.TotalCost/NVL(P.TOTAL_PARCEL_VOLUME, 1) AS CostPerUnitVolume
FROM ( SELECT T1.PLAN_ID,
SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
NVL(T1.TOTAL_HANDLING_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_parcel
GROUP BY T1.PLAN_ID) T,
MST_PLANS P
WHERE T.PLAN_ID = P.PLAN_ID;
SELECT T11.TotalCost/T12.TotalVolume CostPerUnitVolume
FROM ( SELECT SUM(
NVL(T1.TOTAL_HANDLING_COST, 0) +
NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.CARRIER_ID = p_carrier_id
) T11,
(SELECT SUM(d.VOLUME) TotalVolume
FROM MST_DELIVERIES D
WHERE D.PLAN_ID = p_plan_id
AND D.DELIVERY_ID IN
( SELECT DL.DELIVERY_ID
FROM MST_DELIVERY_LEGS DL,
MST_TRIPS T2
WHERE DL.PLAN_ID = d.PLAN_ID
AND T2.PLAN_ID = dl.PLAN_ID
AND T2.TRIP_ID = dl.TRIP_ID
AND T2.PLAN_ID = p_plan_id
AND T2.CARRIER_ID = p_carrier_id
)
) T12;
SELECT T1.TotalCost/T2.TotalVolume CostPerUnitVolume
FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0)+
NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D
WHERE DL.PLAN_ID = p_plan_id
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.CUSTOMER_ID = p_customer_id
) T1,
( SELECT SUM(D1.VOLUME) TotalVolume
FROM MST_DELIVERIES D1
WHERE D1.PLAN_ID = p_plan_id
AND D1.CUSTOMER_ID = p_customer_id
AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
FROM MST_DELIVERY_LEGS DL1
WHERE DL1.PLAN_ID = D1.PLAN_ID
AND DL1.DELIVERY_ID = D1.DELIVERY_ID
)
) T2;
SELECT T1.TotalCost/T2.TotalVolume CostPerUnitVolume
FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0)+
NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D
WHERE DL.PLAN_ID = p_plan_id
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.SUPPLIER_ID = p_supplier_id
) T1,
(SELECT SUM(D1.VOLUME) TotalVolume
FROM MST_DELIVERIES D1
WHERE D1.PLAN_ID = p_plan_id
AND D1.SUPPLIER_ID = p_supplier_id
AND EXISTS ( SELECT DL1.DELIVERY_LEG_ID
FROM MST_DELIVERY_LEGS DL1
WHERE DL1.PLAN_ID = D1.PLAN_ID
AND DL1.DELIVERY_ID = D1.DELIVERY_ID
)
) T2;
SELECT T1.TotalCost/T2.TotalVolume CostPerUnitVolume
FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D
WHERE DL.PLAN_ID = p_plan_id
AND DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND ( D.PICKUP_LOCATION_ID = p_location_id
OR D.DROPOFF_LOCATION_ID = p_location_id )
) T1,
( SELECT SUM(D1.VOLUME) TotalVolume
FROM MST_DELIVERIES D1
WHERE D1.PLAN_ID = p_plan_id
AND ( D1.DROPOFF_LOCATION_ID = p_location_id
OR D1.PICKUP_LOCATION_ID = p_location_id )
AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
FROM MST_DELIVERY_LEGS DL1
WHERE DL1.DELIVERY_ID = D1.DELIVERY_ID
AND DL1.PLAN_ID = D1.PLAN_ID
)
) T2;
SELECT (SUM(NVL(T.TOTAL_HANDLING_COST, 0) +
NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T.TOTAL_STOP_COST, 0) +
NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T.TOTAL_LAYOVER_COST, 0) +
NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) /
SUM(T.TOTAL_TRIP_DISTANCE) )TLCostPerUnitDist
FROM MST_TRIPS T
WHERE T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload;
SELECT (SUM(
NVL(T.TOTAL_HANDLING_COST, 0) +
NVL(T.TOTAL_BASIC_TRANSPORT_COST,0) +
NVL(T.TOTAL_STOP_COST, 0) +
NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T.TOTAL_LAYOVER_COST, 0) +
NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) /
SUM(T.TOTAL_TRIP_DISTANCE) )TLCostPerUnitDist
FROM MST_TRIPS T
WHERE T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload
AND T.CARRIER_ID = p_carrier_id;
SELECT (T11.TotalTLCost /
T12.TotalTLCubeDist) TLCostPerUnitCubeDist
FROM ( SELECT SUM(NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T.TOTAL_STOP_COST, 0) +
NVL(T.TOTAL_LAYOVER_COST, 0) +
NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T
WHERE T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload
) T11,
( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
NVL(TS.DEPARTURE_VOLUME, 0) ) TotalTLCubeDist
FROM MST_TRIP_STOPS TS,
MST_TRIPS T1
WHERE TS.PLAN_ID = T1.PLAN_ID
AND TS.TRIP_ID = T1.TRIP_ID
AND T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
) T12;
SELECT (T11.TotalTLCost /
T12.TotalTLCubeDist) TLCostPerUnitCubeDist
FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
) T11,
( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.VOLUME, 0)) TotalTLCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T2,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
AND TS.PLAN_ID = T2.PLAN_ID
AND TS.TRIP_ID = T2.TRIP_ID
AND T2.PLAN_ID = p_plan_id
AND T2.MODE_OF_TRANSPORT = g_tload
) T12;
SELECT (T11.TotalTLCost /
T12.TotalTLCubeDist) TLCostPerUnitCubeDist
FROM ( SELECT SUM(
NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T.TOTAL_STOP_COST, 0) +
NVL(T.TOTAL_LAYOVER_COST, 0) +
NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T
WHERE T.PLAN_ID = p_plan_id
AND T.CARRIER_ID = p_carrier_id
AND T.MODE_OF_TRANSPORT = g_tload
) T11,
( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
NVL(TS.DEPARTURE_VOLUME, 0) ) TotalTLCubeDist
FROM MST_TRIP_STOPS TS,
MST_TRIPS T1
WHERE TS.PLAN_ID = T1.PLAN_ID
AND TS.TRIP_ID = T1.TRIP_ID
AND T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
AND T1.CARRIER_ID = p_Carrier_ID
) T12;
SELECT (T11.TotalTLCost /
T12.TotalTLCubeDist) TLCostPerUnitCubeDist
FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.CARRIER_ID = p_carrier_id
AND T1.MODE_OF_TRANSPORT = g_tload
) T11,
( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) TotalTLCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T2,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
AND TS.PLAN_ID = T2.PLAN_ID
AND TS.TRIP_ID = T2.TRIP_ID
AND T2.PLAN_ID = p_plan_id
AND T2.MODE_OF_TRANSPORT = g_tload
AND T2.CARRIER_ID = p_carrier_id
) T12;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.CUSTOMER_ID = p_customer_Id
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) ) AS TLCostPerUnitCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.CUSTOMER_ID = p_customer_id
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.SUPPLIER_ID = p_supplier_id
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.SUPPLIER_ID = p_supplier_id
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) ) AS TLCostPerUnitCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND TS.STOP_LOCATION_ID = p_location_id
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND TS.STOP_LOCATION_ID = p_location_id
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (T11.TotalTLCost /
T12.TotalTLWeightDist) TLCostPerUnitWtDist
FROM ( SELECT SUM(NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0) +
NVL(T.TOTAL_STOP_COST, 0) +
NVL(T.TOTAL_LAYOVER_COST, 0) +
NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T
WHERE T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload
) T11,
( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
NVL(TS.DEPARTURE_GROSS_WEIGHT, 0) ) TotalTLWeightDist
FROM MST_TRIP_STOPS TS,
MST_TRIPS T1
WHERE TS.PLAN_ID = T1.PLAN_ID
AND TS.TRIP_ID = T1.TRIP_ID
AND T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
) T12;
SELECT (T11.TotalTLCost /
T12.TotalTLWeightDist) TLCostPerUnitWtDist
FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
) T11,
( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) TotalTLWeightDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T2,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
AND TS.PLAN_ID = T2.PLAN_ID
AND TS.TRIP_ID = T2.TRIP_ID
AND T2.PLAN_ID = p_plan_id
AND T2.MODE_OF_TRANSPORT = g_tload
) T12;
SELECT (T11.TotalTLCost / T12.TotalTLWeightDist)
TLCostPerUnitWtDist
FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
AND T1.CARRIER_ID = p_carrier_id
) T11,
( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
NVL(TS.DEPARTURE_GROSS_WEIGHT, 0) ) TotalTLWeightDist
FROM MST_TRIP_STOPS TS,
MST_TRIPS T2
WHERE TS.PLAN_ID = T2.PLAN_ID
AND TS.TRIP_ID = T2.TRIP_ID
AND T2.PLAN_ID = p_plan_id
AND T2.MODE_OF_TRANSPORT = g_tload
AND T2.CARRIER_ID = p_carrier_id
) T12;
SELECT (T11.TotalTLCost / T12.TotalTLWeightDist)
TLCostPerUnitWtDist
FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
NVL(T1.TOTAL_STOP_COST, 0) +
NVL(T1.TOTAL_LAYOVER_COST, 0) +
NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
FROM MST_TRIPS T1
WHERE T1.PLAN_ID = p_plan_id
AND T1.MODE_OF_TRANSPORT = g_tload
AND T1.CARRIER_ID = p_carrier_id
) T11,
( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) TotalTLWeightDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T2,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
AND TS.PLAN_ID = T2.PLAN_ID
AND TS.TRIP_ID = T2.TRIP_ID
AND T2.PLAN_ID = p_plan_id
AND T2.MODE_OF_TRANSPORT = g_tload
AND T2.CARRIER_ID = p_carrier_id
) T12;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.CUSTOMER_ID = p_customer_id
AND DL.PLAN_iD = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.CUSTOMER_ID = p_customer_id
AND DL.PLAN_iD = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.SUPPLIER_ID = p_supplier_id
AND DL.PLAN_iD = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND D.SUPPLIER_ID = p_supplier_id
AND DL.PLAN_iD = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.PLAN_ID = p_plan_id
AND T.MODE_OF_TRANSPORT = g_tload;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) ) AS TLCostPerUnitWtDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND TS.STOP_LOCATION_ID = p_location_id
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
SUM(NVL(DL.DIRECT_DISTANCE, 0) *
NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
FROM MST_DELIVERY_LEGS DL,
MST_DELIVERIES D,
MST_TRIPS T,
MST_TRIP_STOPS TS
WHERE DL.PLAN_ID = D.PLAN_ID
AND DL.DELIVERY_ID = D.DELIVERY_ID
AND DL.PLAN_ID = TS.PLAN_ID
AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND TS.STOP_LOCATION_ID = p_location_id
AND T.MODE_OF_TRANSPORT = g_tload
AND T.PLAN_ID = p_plan_id;
SELECT t.target_id
FROM bisfv_targets t,
bisbv_target_levels tl,
bisbv_application_measures am
WHERE am.measure_short_name = p_tgt_short_name
AND am.measure_id = tl.measure_id
AND tl.target_level_id = t.target_level_id
AND decode(p_tgt_short_name,'MST_COST_PU_WT_PLAN' ,t.dim1_level_value_id
,'MST_COST_PU_VOL_PLAN' ,t.dim1_level_value_id
,'MST_COST_PU_DIST_PLAN' ,t.dim1_level_value_id
,'MST_COST_PU_VOL_DIST_PLAN' ,t.dim1_level_value_id
,'MST_COST_PU_WT_DIST_PLAN' ,t.dim1_level_value_id
,'MST_COST_PU_VOL_DIST_CARR' ,t.dim3_level_value_id
,'MST_COST_PU_WT_CARR' ,t.dim3_level_value_id
,'MST_COST_PU_VOL_CARR' ,t.dim3_level_value_id
,'MST_COST_PU_DIST_CARR' ,t.dim3_level_value_id
,'MST_COST_PU_WT_DIST_CARR' ,t.dim3_level_value_id
,'MST_COST_PU_WT_CUST' ,t.dim1_level_value_id
,'MST_COST_PU_VOL_CUST' ,t.dim1_level_value_id
,'MST_COST_PU_WT_DIST_CUST' ,t.dim1_level_value_id
,'MST_COST_PU_VOL_DIST_CUST' ,t.dim1_level_value_id
,'MST_COST_PU_WT_SUPP' ,t.dim2_level_value_id
,'MST_COST_PU_VOL_SUPP' ,t.dim2_level_value_id
,'MST_COST_PU_VOL_DIST_SUPP' ,t.dim2_level_value_id
,'MST_COST_PU_WT_DIST_SUPP' ,t.dim2_level_value_id
,'MST_COST_PU_WT_FAC' ,t.dim4_level_value_id
,'MST_COST_PU_VOL_FAC' ,t.dim4_level_value_id
,'MST_COST_PU_VOL_DIST_FAC' ,t.dim4_level_value_id
,'MST_COST_PU_WT_DIST_FAC' ,t.dim4_level_value_id
,'MST_COST_PU_WT_MODE' ,t.dim5_level_value_id
,'MST_COST_PU_VOL_MODE' ,t.dim5_level_value_id
,t.dim1_level_value_id) = p_dim_id;