[Home] [Help]
PACKAGE BODY: APPS.MST_CMP_KPIS
Source
1 PACKAGE BODY MST_CMP_KPIS AS
2 /* $Header: MSTCKPIB.pls 120.0 2005/05/26 17:49:28 appldev noship $ */
3 -- -------------------------------------------------------------
4 -- Note:
5 -- =====
6 -- This is just a first cut of design and need to be changed
7 -- further to validation. There exists some SQL's which really
8 -- need to be changed. All the SQL's are replicas of those
9 -- mentioned in the KPI's calculation doc. This comment will be
10 -- removed once the code is been validated and tested.
11 -- -------------------------------------------------------------
12
13 g_plan_level CONSTANT NUMBER(1) := 1;
14 g_mode_level CONSTANT NUMBER(1) := 2;
15 g_customer_level CONSTANT NUMBER(1) := 3;
16 g_supplier_level CONSTANT NUMBER(1) := 4;
17 g_carrier_level CONSTANT NUMBER(1) := 5;
18 g_facility_level CONSTANT NUMBER(1) := 6;
19
20 g_precision CONSTANT NUMBER := 3;
21
22 g_tload CONSTANT VARCHAR2(6) := 'TRUCK';
23 g_ltl CONSTANT VARCHAR2(3) := 'LTL';
24 g_parcel CONSTANT VARCHAR2(6) := 'PARCEL';
25
26 g_act_dist_travel CONSTANT NUMBER := 1;
27 g_dir_route_dist CONSTANT NUMBER := 2;
28
29 CURSOR cur_get_parameters IS
30 SELECT NVL(COST_DISTANCE_ALLOC_METHOD,1)
31 FROM mst_parameters
32 WHERE user_Id = -9999;
33
34 CURSOR cur_plan_info(p_plan_id IN NUMBER) IS
35 SELECT PLAN_ID, currency_uom
36 FROM mst_plans
37 WHERE plan_id = p_plan_id;
38
39 g_rec_plan_info cur_plan_info%ROWTYPE;
40
41 FUNCTION get_location(p_facility_id IN NUMBER) RETURN NUMBER IS
42 CURSOR cur_location IS
43 SELECT wl.wsh_location_id
44 FROM wsh_locations wl,
45 fte_location_parameters fte
46 WHERE wl.wsh_location_id = fte.location_id
47 AND fte.facility_id = p_facility_id;
48 l_location_id NUMBER;
49 BEGIN
50 OPEN cur_location;
51 FETCH cur_location INTO l_location_id;
52 CLOSE cur_location;
53 RETURN l_location_id;
54 EXCEPTION
55 WHEN OTHERS THEN
56 RETURN NULL;
57 END get_location;
58
59 -- Cost per Unit Weight (KPI - 38)
60 -- ===============================
61 FUNCTION Cost_per_Unit_Weight( p_plan_id IN NUMBER,
62 p_level IN NUMBER,
63 p_customer_id IN NUMBER,
64 p_supplier_id IN NUMBER,
65 p_carrier_id IN NUMBER,
66 p_mode_of_transport IN VARCHAR2,
67 p_fac_loc_id IN NUMBER)
68 RETURN NUMBER IS
69
70 -- Plan level
71 -- -----------------
72 -- ---------------------------------------------------
73 -- Expected result: Given a specific plan, Total Cost
74 -- (as measured by KPI 22) divided by Total Weight
75 -- (as measured by KPI 3) at the plan level.
76 -- ---------------------------------------------------
77 /*CURSOR cur_cpuw_plan IS
78 SELECT T.TotalCost/NVL(P.TOTAL_WEIGHT, 1) AS CostPerUnitWeight
79 FROM (SELECT T1.PLAN_ID,
80 SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
81 NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
82 NVL(T1.TOTAL_STOP_COST, 0) +
83 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
84 NVL(T1.TOTAL_LAYOVER_COST, 0) +
85 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
86 FROM MST_TRIPS T1
87 WHERE T1.PLAN_ID = p_plan_Id
88 GROUP BY T1.PLAN_ID) T,
89 MST_PLANS P
90 WHERE T.PLAN_ID = P.PLAN_ID;*/
91 -- -----------------------------------------
92 -- As per bug#3535276, We always need to go
93 -- with delivery legs to compute total cost.
94 -- -----------------------------------------
95 -- -----------------------------------------
96 -- As per bug#3555250, exclude weight of
97 -- unassigned deliveries.
98 -- -----------------------------------------
99 CURSOR cur_cpuw_plan IS
100 --SELECT NVL(P.TOTAL_PLAN_COST,0)/NVL(P.TOTAL_WEIGHT, 1) AS CostPerUnitWeight
101 SELECT NVL(P.TOTAL_PLAN_COST,0)/DECODE((
102 NVL(P.TOTAL_TL_WEIGHT, 0) +
103 NVL(P.TOTAL_LTL_WEIGHT, 0) +
104 NVL(P.TOTAL_PARCEL_WEIGHT, 0) ),0,1,
105 (NVL(P.TOTAL_TL_WEIGHT, 0) +
106 NVL(P.TOTAL_LTL_WEIGHT, 0) +
107 NVL(P.TOTAL_PARCEL_WEIGHT, 0)))
108 AS CostPerUnitWeight
109 FROM MST_PLANS P
110 WHERE P.PLAN_ID = P_PLAN_ID;
111
112 -- Mode level
113 -- ----------
114 -- ---------------------------------------------------
115 -- Expected result: Given a specific plan and mode of
116 -- transportation, Total Cost (as measured by KPI 22)
117 -- divided by Total Weight (as measured by KPI 3) for
118 -- that mode.
119 -- ---------------------------------------------------
120
121 -- Mode level - TL
122 -- ----------------
123 CURSOR cur_cpuw_Truck IS
124 SELECT T.TotalCost/NVL(P.TOTAL_TL_WEIGHT, 1) CostPerUnitWeight
125 FROM (SELECT T1.PLAN_ID,
126 SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
127 NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
128 NVL(T1.TOTAL_STOP_COST, 0) +
129 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
130 NVL(T1.TOTAL_LAYOVER_COST, 0) +
131 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
132 FROM MST_TRIPS T1
133 WHERE T1.PLAN_ID = p_plan_id
134 AND T1.MODE_OF_TRANSPORT = g_tload -- 'TRUCK'
135 GROUP BY T1.PLAN_ID ) T,
136 MST_PLANS P
137 WHERE T.PLAN_ID = P.PLAN_ID;
138
139 -- Mode level - LTL
140 -- -----------------
141 CURSOR cur_cpuw_ltl IS
142 SELECT T.TotalCost/NVL(P.TOTAL_LTL_WEIGHT, 1) CostPerUnitWeight
143 FROM (SELECT T1.PLAN_ID,
144 SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
145 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
146 NVL(T1.TOTAL_HANDLING_COST ,0 ) ) TotalCost
147 FROM MST_TRIPS T1
148 WHERE T1.PLAN_ID = p_plan_id
149 AND T1.MODE_OF_TRANSPORT = g_ltl
150 GROUP BY T1.PLAN_ID ) T,
151 MST_PLANS P
152 WHERE T.PLAN_ID = P.PLAN_ID;
153
154 -- Mode level - Parcel
155 -- -------------------
156 CURSOR cur_cpuw_parcel IS
157 SELECT T.TotalCost/NVL(P.TOTAL_PARCEL_WEIGHT, 1) CostPerUnitWeight
158 FROM (SELECT T1.PLAN_ID,
159 SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
160 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
161 NVL(T1.TOTAL_HANDLING_COST, 0) ) TotalCost
162 FROM MST_TRIPS T1
163 WHERE T1.PLAN_ID = p_plan_id
164 AND T1.MODE_OF_TRANSPORT = g_parcel
165 GROUP BY T1.PLAN_ID ) T,
166 MST_PLANS P
167 WHERE T.PLAN_ID = P.PLAN_ID;
168
169 -- Carrier level
170 -- -------------
171 -- ---------------------------------------------------
172 -- Expected result: Given a specific plan and carrier,
173 -- Total Cost (as measured by KPI 22) divided by Total
174 -- Weight (as measured by KPI 3) for that carrier.
175 -- ---------------------------------------------------
176 CURSOR cur_cpuw_carr IS
177 SELECT T11.TotalCost/NVL(T12.TotalWeight, 1) CostPerUnitWeight
178 FROM (SELECT SUM(
179 NVL(T1.TOTAL_HANDLING_COST, 0) +
180 NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
181 NVL(T1.TOTAL_STOP_COST, 0) +
182 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
183 NVL(T1.TOTAL_LAYOVER_COST, 0) +
184 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
185 FROM MST_TRIPS T1
186 WHERE T1.PLAN_ID = p_plan_Id
187 AND T1.CARRIER_ID = p_carrier_id
188 ) T11,
189 (SELECT SUM(D.GROSS_WEIGHT) TotalWeight
190 FROM MST_DELIVERIES D
191 WHERE D.PLAN_ID = p_plan_id
192 AND D.DELIVERY_ID IN
193 ( SELECT DL.DELIVERY_ID
194 FROM MST_DELIVERY_LEGS DL,
195 MST_TRIPS T2
196 WHERE dl.plan_id = d.plan_id
197 AND T2.PLAN_ID = dl.PLAN_ID
198 AND T2.TRIP_ID = dl.TRIP_ID
199 AND T2.CARRIER_ID = p_carrier_id
200 )
201 )T12;
202
203 -- Customer level
204 -- --------------
205 -- ---------------------------------------------------
206 -- Expected result: Given a specific plan and customer,
207 -- Total Cost (as measured by KPI 22) divided by Total
208 -- Weight (as measured by KPI 3) for that customer.
209 -- ---------------------------------------------------
210 CURSOR cur_cpuw_cust IS
211 SELECT T1.TotalCost/T2.TotalWeight CostPerUnitWeight
212 FROM (SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
213 NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
214 NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
215 NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
216 NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
217 FROM MST_DELIVERY_LEGS DL,
218 MST_DELIVERIES D
219 WHERE DL.PLAN_ID = p_plan_Id
220 AND DL.PLAN_ID = D.PLAN_ID
221 AND DL.DELIVERY_ID = D.DELIVERY_ID
222 AND D.CUSTOMER_ID = p_customer_id
223 ) T1,
224 (SELECT SUM(D1.GROSS_WEIGHT) TotalWeight
225 FROM MST_DELIVERIES D1
226 WHERE D1.PLAN_ID = p_plan_id
227 AND D1.CUSTOMER_ID = p_customer_id
228 AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
229 FROM MST_DELIVERY_LEGS DL1
230 WHERE DL1.PLAN_ID = D1.PLAN_ID
231 AND DL1.DELIVERY_ID = D1.DELIVERY_ID
232 )
233 ) T2;
234
235 -- Supplier level
236 -- --------------
237 -- ---------------------------------------------------
238 -- Expected result: Given a specific plan and supplier,
239 -- the Total Cost (as measured by KPI 22) divided by
240 -- Total Weight (as measured by KPI 3) for that supplier.
241 -- ---------------------------------------------------
242 CURSOR cur_cpuw_supp IS
243 SELECT T1.TotalCost/T2.TotalWeight CostPerUnitWeight
244 FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
245 NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
246 NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
247 NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
248 NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
249 FROM MST_DELIVERY_LEGS DL,
250 MST_DELIVERIES D
251 WHERE DL.PLAN_ID = p_plan_id
252 AND DL.PLAN_ID = D.PLAN_ID
253 AND DL.DELIVERY_ID = D.DELIVERY_ID
254 AND D.SUPPLIER_ID = p_Supplier_ID
255 ) T1,
256 ( SELECT SUM(D1.GROSS_WEIGHT) TotalWeight
257 FROM MST_DELIVERIES D1
258 WHERE D1.PLAN_ID = p_plan_id
259 AND D1.SUPPLIER_ID = p_Supplier_ID
260 AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
261 FROM MST_DELIVERY_LEGS DL1
262 WHERE DL1.PLAN_ID = D1.PLAN_ID
263 AND DL1.DELIVERY_ID = D1.DELIVERY_ID
264 )
265 ) T2;
266
267 -- Facility level
268 -- --------------
269 -- ---------------------------------------------------
270 -- Expected result: Given a specific plan and facility
271 -- (location id), Total Cost (as measured by KPI 22)
272 -- divided by Total Weight (as measured by KPI 3) for
273 -- that facility.
274 -- ---------------------------------------------------
275 CURSOR cur_cpuw_fac(p_location_id IN NUMBER) IS
276 SELECT T1.TotalCost/T2.TotalWeight CostPerUnitWeight
277 FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
278 NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
279 NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
280 NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
281 NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
282 FROM MST_DELIVERY_LEGS DL,
283 MST_DELIVERIES D
284 WHERE DL.PLAN_ID = p_plan_id
285 AND DL.PLAN_ID = D.PLAN_ID
286 AND DL.DELIVERY_ID = D.DELIVERY_ID
287 AND ( D.PICKUP_LOCATION_ID = p_location_id
288 OR D.DROPOFF_LOCATION_ID = p_location_id )
289 ) T1,
290 ( SELECT SUM(D1.GROSS_WEIGHT) TotalWeight
291 FROM MST_DELIVERIES D1
292 WHERE D1.PLAN_ID = p_plan_Id
293 AND ( D1.DROPOFF_LOCATION_ID = p_location_id
294 OR D1.PICKUP_LOCATION_ID = p_location_id )
295 AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
296 FROM MST_DELIVERY_LEGS DL1
297 WHERE DL1.DELIVERY_ID = D1.DELIVERY_ID
298 AND DL1.PLAN_ID = D1.PLAN_ID
299 )
300 ) T2;
301
302 l_costperunitweight NUMBER;
303 l_location_id NUMBER;
304
305 BEGIN
306 IF P_LEVEL = g_plan_level THEN
307 OPEN cur_cpuw_plan;
308 FETCH cur_cpuw_plan INTO l_costperunitweight;
309 CLOSE cur_cpuw_plan;
310 ELSIF p_level = g_mode_level THEN
311 IF p_mode_of_transport = g_tload THEN
312 OPEN cur_cpuw_Truck;
313 FETCH cur_cpuw_Truck INTO l_costperunitweight;
314 CLOSE cur_cpuw_Truck;
315 ELSIF p_mode_of_transport = g_ltl THEN
316 OPEN cur_cpuw_ltl;
317 FETCH cur_cpuw_ltl INTO l_costperunitweight;
318 CLOSE cur_cpuw_ltl;
319 ELSIF p_mode_of_transport = g_parcel THEN
320 OPEN cur_cpuw_parcel;
321 FETCH cur_cpuw_parcel INTO l_costperunitweight;
322 CLOSE cur_cpuw_parcel;
323 END IF;
324 ELSIF p_level = g_customer_level THEN
325 OPEN cur_cpuw_cust;
326 FETCH cur_cpuw_cust INTO l_costperunitweight;
327 CLOSE cur_cpuw_cust;
328 ELSIF p_level = g_supplier_level THEN
329 OPEN cur_cpuw_supp;
330 FETCH cur_cpuw_supp INTO l_costperunitweight;
331 CLOSE cur_cpuw_supp;
332 ELSIF p_level = g_carrier_level THEN
333 OPEN cur_cpuw_carr;
334 FETCH cur_cpuw_carr INTO l_costperunitweight;
335 CLOSE cur_cpuw_carr;
336 ELSIF p_level = g_facility_level THEN
337 l_location_id := get_location(p_fac_loc_id);
338 OPEN cur_cpuw_fac(l_location_id);
339 FETCH cur_cpuw_fac INTO l_costperunitweight;
340 CLOSE cur_cpuw_fac;
341 END IF;
342 /**IF g_rec_plan_info.plan_Id <> p_plan_id THEN
343 OPEN cur_plan_info(p_plan_id);
344 FETCH cur_plan_info INTO g_rec_plan_info;
345 CLOSE cur_plan_info;
346 IF g_rec_plan_info.currency_uom IS NOT NULL THEN
347 fnd_currency.get_info
348 (g_rec_plan_info.currency_uom,
349 g_precision,
350 g_ext_precision,
351 g_mau);
352 END IF;
353 END IF;
354 IF g_precision IS NOT NULL THEN
355 l_costperunitweight:= round(l_costperunitweight,g_precision);
356 END IF;*/
357 IF l_costperunitweight IS NULL THEN
358 l_costperunitweight := 0;
359 END IF;
360 l_costperunitweight:= round(l_costperunitweight,g_precision);
361 RETURN l_costperunitweight;
362 EXCEPTION
363 WHEN OTHERS THEN
364 RETURN 0;
365 END Cost_per_Unit_Weight;
366
367 -- Cost per Unit Volume (KPI - 39)
368 -- ===============================
369 FUNCTION Cost_per_Unit_Volume( p_plan_id IN NUMBER,
370 p_level IN NUMBER,
371 p_customer_id IN NUMBER,
372 p_supplier_id IN NUMBER,
373 p_carrier_id IN NUMBER,
374 p_mode_of_transport IN VARCHAR2,
375 p_fac_loc_id IN NUMBER)
376 RETURN NUMBER IS
377
378
379 -- Plan level
380 -- ----------
381 -- ---------------------------------------------------
382 -- Expected result: Given a specific plan, Total Cost
383 -- (as measured by KPI 22) divided by Total Volume
384 -- (as measured by KPI 4) at the plan level.
385 -- ---------------------------------------------------
386 /*CURSOR cur_cpuv_plan IS
387 SELECT (T.TotalCost/NVL(P.TOTAL_VOLUME,1)) AS CostPerUnitVolume
388 FROM ( SELECT T1.PLAN_ID,
389 SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
390 NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
391 NVL(T1.TOTAL_STOP_COST, 0) +
392 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
393 NVL(T1.TOTAL_LAYOVER_COST, 0) +
394 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
395 FROM MST_TRIPS T1
396 WHERE t1.PLAN_ID = p_plan_id
397 GROUP BY T1.PLAN_ID
398 ) T,
399 MST_PLANS P
400 WHERE T.PLAN_ID = P.PLAN_ID;*/
401 -- -----------------------------------------
402 -- As per bug#3535276, We always need to go
403 -- with delivery legs to compute total cost.
404 -- -----------------------------------------
405 -- -----------------------------------------
406 -- As per bug#3555250, exclude VOLUME of
407 -- unassigned deliveries.
408 -- -----------------------------------------
409 CURSOR cur_cpuv_plan IS
410 --SELECT NVL(P.TOTAL_PLAN_COST,0)/NVL(P.TOTAL_VOLUME, 1) AS CostPerUnitVolume
411 SELECT NVL(P.TOTAL_PLAN_COST,0)/DECODE((
412 NVL(P.TOTAL_TL_VOLUME, 0) +
413 NVL(P.TOTAL_LTL_VOLUME, 0) +
414 NVL(P.TOTAL_PARCEL_VOLUME, 0) ),0,1,
415 (NVL(P.TOTAL_TL_VOLUME, 0) +
416 NVL(P.TOTAL_LTL_VOLUME, 0) +
417 NVL(P.TOTAL_PARCEL_VOLUME, 0)))
418 AS CostPerUnitVolume
419 FROM MST_PLANS P
420 WHERE P.PLAN_ID = P_PLAN_ID;
421 -- Mode level - TL
422 -- ---------------
423 -- ---------------------------------------------------
424 -- Expected result: Given a specific plan and mode of
425 -- transportation, Total Cost (as measured by KPI 22)
426 -- divided by Total Volume (as measured by KPI 4) for
427 -- that mode.
428 -- ---------------------------------------------------
429 CURSOR cur_cpuv_Truck IS
430 SELECT T.TotalCost/NVL(P.TOTAL_TL_VOLUME, 1) AS CostPerUnitVolume
431 FROM ( SELECT T1.PLAN_ID,
432 SUM(NVL(T1.TOTAL_HANDLING_COST, 0) +
433 NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
434 NVL(T1.TOTAL_STOP_COST, 0) +
435 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
436 NVL(T1.TOTAL_LAYOVER_COST, 0) +
437 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
438 FROM MST_TRIPS T1
439 WHERE T1.PLAN_ID = p_plan_id
440 AND T1.MODE_OF_TRANSPORT = g_tload
441 GROUP BY T1.PLAN_ID) T,
442 MST_PLANS P
443 WHERE T.PLAN_ID = P.PLAN_ID;
444
445 -- Mode level - LTL
446 -- -----------------
447 CURSOR cur_cpuv_LTL IS
448 SELECT T.TotalCost/NVL(P.TOTAL_LTL_VOLUME, 1) AS CostPerUnitVolume
449 FROM ( SELECT T1.PLAN_ID,
450 SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
451 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
452 NVL(T1.TOTAL_HANDLING_COST, 0) ) TotalCost
453 FROM MST_TRIPS T1
454 WHERE T1.PLAN_ID = p_plan_id
455 AND T1.MODE_OF_TRANSPORT = g_ltl
456 GROUP BY T1.PLAN_ID) T,
457 MST_PLANS P
458 WHERE T.PLAN_ID = P.PLAN_ID;
459
460 -- Mode level - Parcel
461 -- -------------------
462 CURSOR cur_cpuv_Parcel IS
463 SELECT T.TotalCost/NVL(P.TOTAL_PARCEL_VOLUME, 1) AS CostPerUnitVolume
464 FROM ( SELECT T1.PLAN_ID,
465 SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
466 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) +
467 NVL(T1.TOTAL_HANDLING_COST, 0) ) TotalCost
468 FROM MST_TRIPS T1
469 WHERE T1.PLAN_ID = p_plan_id
470 AND T1.MODE_OF_TRANSPORT = g_parcel
471 GROUP BY T1.PLAN_ID) T,
472 MST_PLANS P
473 WHERE T.PLAN_ID = P.PLAN_ID;
474
475 -- Carrier level
476 -- -------------
477 -- ---------------------------------------------------
478 -- Expected result: Given a specific plan and carrier,
479 -- Total Cost (as measured by KPI 22) divided by Total
480 -- Volume (as measured by KPI 4) for that carrier.
481 -- ---------------------------------------------------
482 CURSOR cur_cpuv_carr IS
483 SELECT T11.TotalCost/T12.TotalVolume CostPerUnitVolume
484 FROM ( SELECT SUM(
485 NVL(T1.TOTAL_HANDLING_COST, 0) +
486 NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
487 NVL(T1.TOTAL_STOP_COST, 0) +
488 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
489 NVL(T1.TOTAL_LAYOVER_COST, 0) +
490 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalCost
491 FROM MST_TRIPS T1
492 WHERE T1.PLAN_ID = p_plan_id
493 AND T1.CARRIER_ID = p_carrier_id
494 ) T11,
495 (SELECT SUM(d.VOLUME) TotalVolume
496 FROM MST_DELIVERIES D
497 WHERE D.PLAN_ID = p_plan_id
498 AND D.DELIVERY_ID IN
499 ( SELECT DL.DELIVERY_ID
500 FROM MST_DELIVERY_LEGS DL,
501 MST_TRIPS T2
502 WHERE DL.PLAN_ID = d.PLAN_ID
503 AND T2.PLAN_ID = dl.PLAN_ID
504 AND T2.TRIP_ID = dl.TRIP_ID
505 AND T2.PLAN_ID = p_plan_id
506 AND T2.CARRIER_ID = p_carrier_id
507 )
508 ) T12;
509
510 -- Customer level
511 -- --------------
512 -- ---------------------------------------------------
513 -- Expected result: Given a specific plan and customer,
514 -- Total Cost (as measured by KPI 22) divided by Total
515 -- Volume (as measured by KPI 4) for that customer.
516 -- ---------------------------------------------------
517 CURSOR cur_cpuv_cust IS
518 SELECT T1.TotalCost/T2.TotalVolume CostPerUnitVolume
519 FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
520 NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0)+
521 NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
522 NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
523 NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
524 FROM MST_DELIVERY_LEGS DL,
525 MST_DELIVERIES D
526 WHERE DL.PLAN_ID = p_plan_id
527 AND DL.PLAN_ID = D.PLAN_ID
528 AND DL.DELIVERY_ID = D.DELIVERY_ID
529 AND D.CUSTOMER_ID = p_customer_id
530 ) T1,
531 ( SELECT SUM(D1.VOLUME) TotalVolume
532 FROM MST_DELIVERIES D1
533 WHERE D1.PLAN_ID = p_plan_id
534 AND D1.CUSTOMER_ID = p_customer_id
535 AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
536 FROM MST_DELIVERY_LEGS DL1
537 WHERE DL1.PLAN_ID = D1.PLAN_ID
538 AND DL1.DELIVERY_ID = D1.DELIVERY_ID
539 )
540 ) T2;
541
542 -- Supplier level
543 -- --------------
544 -- ---------------------------------------------------
545 -- Expected result: Given a specific plan and supplier,
546 -- Total Cost (as measured by KPI 22) divided by Total
547 -- Volume (as measured by KPI 4) for that supplier.
548 -- ---------------------------------------------------
549 CURSOR cur_cpuv_Supp IS
550 SELECT T1.TotalCost/T2.TotalVolume CostPerUnitVolume
551 FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
552 NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0)+
553 NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
554 NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
555 NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
556 FROM MST_DELIVERY_LEGS DL,
557 MST_DELIVERIES D
558 WHERE DL.PLAN_ID = p_plan_id
559 AND DL.PLAN_ID = D.PLAN_ID
560 AND DL.DELIVERY_ID = D.DELIVERY_ID
561 AND D.SUPPLIER_ID = p_supplier_id
562 ) T1,
563 (SELECT SUM(D1.VOLUME) TotalVolume
564 FROM MST_DELIVERIES D1
565 WHERE D1.PLAN_ID = p_plan_id
566 AND D1.SUPPLIER_ID = p_supplier_id
567 AND EXISTS ( SELECT DL1.DELIVERY_LEG_ID
568 FROM MST_DELIVERY_LEGS DL1
569 WHERE DL1.PLAN_ID = D1.PLAN_ID
570 AND DL1.DELIVERY_ID = D1.DELIVERY_ID
571 )
572 ) T2;
573
574 -- Facility level
575 -- --------------
576 -- ---------------------------------------------------
577 -- Expected result: Given a specific plan and facility
578 -- (location id), Total Cost (as measured by KPI 22)
579 -- divided by Total Volume (as measured by KPI 4) for
580 -- that facility.
581 -- ---------------------------------------------------
582 CURSOR cur_cpuv_fac(p_location_id IN NUMBER) IS
583 SELECT T1.TotalCost/T2.TotalVolume CostPerUnitVolume
584 FROM ( SELECT SUM(NVL(DL.ALLOCATED_FAC_LOADING_COST, 0) +
585 NVL(DL.ALLOCATED_FAC_UNLOADING_COST, 0) +
586 NVL(DL.ALLOCATED_FAC_SHP_HAND_COST, 0) +
587 NVL(DL.ALLOCATED_FAC_REC_HAND_COST, 0) +
588 NVL(DL.ALLOCATED_TRANSPORT_COST, 0) ) TotalCost
589 FROM MST_DELIVERY_LEGS DL,
590 MST_DELIVERIES D
591 WHERE DL.PLAN_ID = p_plan_id
592 AND DL.PLAN_ID = D.PLAN_ID
593 AND DL.DELIVERY_ID = D.DELIVERY_ID
594 AND ( D.PICKUP_LOCATION_ID = p_location_id
595 OR D.DROPOFF_LOCATION_ID = p_location_id )
596 ) T1,
597 ( SELECT SUM(D1.VOLUME) TotalVolume
598 FROM MST_DELIVERIES D1
599 WHERE D1.PLAN_ID = p_plan_id
600 AND ( D1.DROPOFF_LOCATION_ID = p_location_id
601 OR D1.PICKUP_LOCATION_ID = p_location_id )
602 AND EXISTS (SELECT DL1.DELIVERY_LEG_ID
603 FROM MST_DELIVERY_LEGS DL1
604 WHERE DL1.DELIVERY_ID = D1.DELIVERY_ID
605 AND DL1.PLAN_ID = D1.PLAN_ID
606 )
607 ) T2;
608
609 l_CostPerUnitVolume NUMBER;
610 l_location_id NUMBER;
611 BEGIN
612 IF P_LEVEL = g_plan_level THEN
613 OPEN cur_cpuv_plan;
614 FETCH cur_cpuv_plan INTO l_CostPerUnitVolume;
615 CLOSE cur_cpuv_plan;
616 ELSIF p_level = g_mode_level THEN
617 IF p_mode_of_transport = g_tload THEN
618 OPEN cur_cpuv_Truck;
619 FETCH cur_cpuv_Truck INTO l_CostPerUnitVolume;
620 CLOSE cur_cpuv_Truck;
621 ELSIF p_mode_of_transport = g_ltl THEN
622 OPEN cur_cpuv_ltl;
623 FETCH cur_cpuv_ltl INTO l_CostPerUnitVolume;
624 CLOSE cur_cpuv_ltl;
625 ELSIF p_mode_of_transport = g_parcel THEN
626 OPEN cur_cpuv_parcel;
627 FETCH cur_cpuv_parcel INTO l_CostPerUnitVolume;
628 CLOSE cur_cpuv_parcel;
629 END IF;
630 ELSIF p_level = g_customer_level THEN
631 OPEN cur_cpuv_cust;
632 FETCH cur_cpuv_cust INTO l_CostPerUnitVolume;
633 CLOSE cur_cpuv_cust;
634 ELSIF p_level = g_supplier_level THEN
635 OPEN cur_cpuv_supp;
636 FETCH cur_cpuv_supp INTO l_CostPerUnitVolume;
637 CLOSE cur_cpuv_supp;
638 ELSIF p_level = g_carrier_level THEN
639 OPEN cur_cpuv_carr;
640 FETCH cur_cpuv_carr INTO l_CostPerUnitVolume;
641 CLOSE cur_cpuv_carr;
642 ELSIF p_level = g_facility_level THEN
643 l_location_id := get_location(p_fac_loc_id);
644 OPEN cur_cpuv_fac(l_location_id);
645 FETCH cur_cpuv_fac INTO l_CostPerUnitVolume;
646 CLOSE cur_cpuv_fac;
647 END IF;
648 IF l_CostPerUnitVolume IS NULL THEN
649 l_CostPerUnitVolume := 0;
650 END IF;
651 l_CostPerUnitVolume := round(l_CostPerUnitVolume,g_precision);
652 RETURN l_CostPerUnitVolume;
653 EXCEPTION
654 WHEN OTHERS THEN
655 RETURN 0;
656 END Cost_per_Unit_Volume;
657
658 -- TL Cost per Unit Distance (KPI - 40)
659 -- ====================================
660 FUNCTION TL_Cost_per_Unit_Dist( p_plan_id IN NUMBER,
661 p_level IN NUMBER,
662 p_carrier_id IN NUMBER)
663 RETURN NUMBER IS
664
665 -- Plan level
666 -- ----------
667 -- ---------------------------------------------------
668 -- Expected result: Given a specific plan, Total TL
669 -- Cost (as measured by KPI 24) divided by Total TL
670 -- Distance (as measured by KPI 47).
671 -- ---------------------------------------------------
672 CURSOR cur_cpud_plan IS
673 SELECT (SUM(NVL(T.TOTAL_HANDLING_COST, 0) +
674 NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0)+
675 NVL(T.TOTAL_STOP_COST, 0) +
676 NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
677 NVL(T.TOTAL_LAYOVER_COST, 0) +
678 NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) /
679 SUM(T.TOTAL_TRIP_DISTANCE) )TLCostPerUnitDist
680 FROM MST_TRIPS T
681 WHERE T.PLAN_ID = p_plan_id
682 AND T.MODE_OF_TRANSPORT = g_tload;
683
684
685 -- Carrier Level
686 -- -------------
687 -- ---------------------------------------------------
688 -- Expected result: Given a specific plan and carrier
689 -- id, Total TL Cost (as measured by KPI 24) divided
693 CURSOR cur_cpud_carr IS
690 -- by Total TL Distance (as measured by KPI 47) at the
691 -- carrier level for that carrier.
692 -- ---------------------------------------------------
694 SELECT (SUM(
695 NVL(T.TOTAL_HANDLING_COST, 0) +
696 NVL(T.TOTAL_BASIC_TRANSPORT_COST,0) +
697 NVL(T.TOTAL_STOP_COST, 0) +
698 NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
699 NVL(T.TOTAL_LAYOVER_COST, 0) +
700 NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) /
701 SUM(T.TOTAL_TRIP_DISTANCE) )TLCostPerUnitDist
702 FROM MST_TRIPS T
703 WHERE T.PLAN_ID = p_plan_id
704 AND T.MODE_OF_TRANSPORT = g_tload
705 AND T.CARRIER_ID = p_carrier_id;
706
707 l_CostPerUnitDist NUMBER;
708 BEGIN
709 IF P_LEVEL = g_plan_level THEN
710 OPEN cur_cpud_plan;
711 FETCH cur_cpud_plan INTO l_CostPerUnitDist;
712 CLOSE cur_cpud_plan;
713 ELSIF p_level = g_carrier_level THEN
714 OPEN cur_cpud_carr;
715 FETCH cur_cpud_carr INTO l_CostPerUnitDist;
716 CLOSE cur_cpud_carr;
717 END IF;
718 IF l_CostPerUnitDist IS NULL THEN
719 l_CostPerUnitDist := 0;
720 END IF;
721 l_CostPerUnitDist:= round(l_CostPerUnitDist, g_precision);
722 RETURN l_CostPerUnitDist;
723 EXCEPTION
724 WHEN OTHERS THEN
725 RETURN 0;
726 END TL_Cost_per_Unit_Dist;
727
728 -- TL Cost per Unit Cube-Distance (KPI - 41)
729 -- =========================================
730 FUNCTION TL_Cost_per_Unit_Cube_Dist(p_plan_id IN NUMBER,
731 p_level IN NUMBER,
732 p_customer_id IN NUMBER,
733 p_supplier_id IN NUMBER,
734 p_carrier_id IN NUMBER,
735 p_fac_loc_id IN NUMBER)
736 RETURN NUMBER IS
737
738 -- ---------------------------------------------------
739 -- To compute this KPI we compute Total TL Cost (as
740 -- measured by KPI 24) divided by TL cube-distance. TL
741 -- Cube Distance is computed, depending on profile
742 -- options, as
743 -- (a) Profile option is "actual distance traveled"
744 -- sum(Volume * DistanceTraveled)
745 -- (b) Profile option is "direct route distance"
746 -- sum(Volume * DirectRouteDistance)
747 -- Calculation of this KPI requires cost-allocation to
748 -- delivery legs.
749 -- ---------------------------------------------------
750
751 -- Plan Level
752 -- -----------
753 -- ---------------------------------------------------
754 -- Expected result: For a given plan, Total TL Cost
755 -- (as measured by KPI 24) at plan level divided by
756 -- total TL Cube Distance.
757 -- ---------------------------------------------------
758 -- Using actual distance traveled:
759 -- -------------------------------
760 CURSOR cur_tl_cpucd_act_plan IS
761 SELECT (T11.TotalTLCost /
762 T12.TotalTLCubeDist) TLCostPerUnitCubeDist
763 FROM ( SELECT SUM(NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0) +
764 NVL(T.TOTAL_STOP_COST, 0) +
765 NVL(T.TOTAL_LAYOVER_COST, 0) +
766 NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
767 NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
768 FROM MST_TRIPS T
769 WHERE T.PLAN_ID = p_plan_id
770 AND T.MODE_OF_TRANSPORT = g_tload
771 ) T11,
772 ( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
773 NVL(TS.DEPARTURE_VOLUME, 0) ) TotalTLCubeDist
774 FROM MST_TRIP_STOPS TS,
775 MST_TRIPS T1
776 WHERE TS.PLAN_ID = T1.PLAN_ID
777 AND TS.TRIP_ID = T1.TRIP_ID
778 AND T1.PLAN_ID = p_plan_id
779 AND T1.MODE_OF_TRANSPORT = g_tload
780 ) T12;
781
782 -- Using direct route distance
783 -- ---------------------------
784 CURSOR cur_tl_cpucd_direct_plan IS
785 SELECT (T11.TotalTLCost /
786 T12.TotalTLCubeDist) TLCostPerUnitCubeDist
787 FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
788 NVL(T1.TOTAL_STOP_COST, 0) +
789 NVL(T1.TOTAL_LAYOVER_COST, 0) +
790 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
791 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
792 FROM MST_TRIPS T1
793 WHERE T1.PLAN_ID = p_plan_id
794 AND T1.MODE_OF_TRANSPORT = g_tload
795 ) T11,
796 ( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
797 NVL(D.VOLUME, 0)) TotalTLCubeDist
798 FROM MST_DELIVERY_LEGS DL,
799 MST_DELIVERIES D,
803 AND DL.DELIVERY_ID = D.DELIVERY_ID
800 MST_TRIPS T2,
801 MST_TRIP_STOPS TS
802 WHERE DL.PLAN_ID = D.PLAN_ID
804 AND DL.PLAN_ID = TS.PLAN_ID
805 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
806 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
807 AND TS.PLAN_ID = T2.PLAN_ID
808 AND TS.TRIP_ID = T2.TRIP_ID
809 AND T2.PLAN_ID = p_plan_id
810 AND T2.MODE_OF_TRANSPORT = g_tload
811 ) T12;
812
813 -- Carrier level
814 -- -------------
815 -- ---------------------------------------------------
816 -- Expected result: For a given plan and carrier, Total
817 -- TL Cost (as measured by KPI 24) at carrier level
818 -- divided by Total TL Cube Distance per carrier.
819 -- ---------------------------------------------------
820 -- Using actual distance traveled
821 -- ------------------------------
822 CURSOR cur_tl_cpucd_act_carr IS
823 SELECT (T11.TotalTLCost /
824 T12.TotalTLCubeDist) TLCostPerUnitCubeDist
825 FROM ( SELECT SUM(
826 NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0) +
827 NVL(T.TOTAL_STOP_COST, 0) +
828 NVL(T.TOTAL_LAYOVER_COST, 0) +
829 NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
830 NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
831 FROM MST_TRIPS T
832 WHERE T.PLAN_ID = p_plan_id
833 AND T.CARRIER_ID = p_carrier_id
834 AND T.MODE_OF_TRANSPORT = g_tload
835 ) T11,
836 ( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
837 NVL(TS.DEPARTURE_VOLUME, 0) ) TotalTLCubeDist
838 FROM MST_TRIP_STOPS TS,
839 MST_TRIPS T1
840 WHERE TS.PLAN_ID = T1.PLAN_ID
841 AND TS.TRIP_ID = T1.TRIP_ID
842 AND T1.PLAN_ID = p_plan_id
843 AND T1.MODE_OF_TRANSPORT = g_tload
844 AND T1.CARRIER_ID = p_Carrier_ID
845 ) T12;
846
847 -- Using direct route distance
848 -- ---------------------------
849 CURSOR cur_tl_cpucd_direct_carr IS
850 SELECT (T11.TotalTLCost /
851 T12.TotalTLCubeDist) TLCostPerUnitCubeDist
852 FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0) +
853 NVL(T1.TOTAL_STOP_COST, 0) +
854 NVL(T1.TOTAL_LAYOVER_COST, 0) +
855 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
856 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
857 FROM MST_TRIPS T1
858 WHERE T1.PLAN_ID = p_plan_id
859 AND T1.CARRIER_ID = p_carrier_id
860 AND T1.MODE_OF_TRANSPORT = g_tload
861 ) T11,
862 ( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
863 NVL(D.VOLUME, 0) ) TotalTLCubeDist
864 FROM MST_DELIVERY_LEGS DL,
865 MST_DELIVERIES D,
866 MST_TRIPS T2,
867 MST_TRIP_STOPS TS
868 WHERE DL.PLAN_ID = D.PLAN_ID
869 AND DL.DELIVERY_ID = D.DELIVERY_ID
870 AND DL.PLAN_ID = TS.PLAN_ID
871 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
872 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
873 AND TS.PLAN_ID = T2.PLAN_ID
874 AND TS.TRIP_ID = T2.TRIP_ID
875 AND T2.PLAN_ID = p_plan_id
876 AND T2.MODE_OF_TRANSPORT = g_tload
877 AND T2.CARRIER_ID = p_carrier_id
878 ) T12;
879
880 -- Customer level
881 -- --------------
882 -- ---------------------------------------------------
883 -- Expected result: Given a specific plan and customer,
884 -- Total TL Cost (as measured by KPI 24) at the
885 -- customer level divided by Total TL Cube Distance
886 -- for orders corresponding to that customer.
887 -- ---------------------------------------------------
888 -- Using actual distance traveled
889 -- ------------------------------
890 CURSOR cur_tl_cpucd_act_cust IS
891 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
892 SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
893 NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
894 FROM MST_DELIVERY_LEGS DL,
895 MST_DELIVERIES D,
896 MST_TRIPS T,
897 MST_TRIP_STOPS TS
898 WHERE DL.PLAN_ID = D.PLAN_ID
899 AND DL.DELIVERY_ID = D.DELIVERY_ID
900 AND D.CUSTOMER_ID = p_customer_Id
901 AND DL.PLAN_ID = TS.PLAN_ID
902 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
903 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
904 AND TS.PLAN_ID = T.PLAN_ID
905 AND TS.TRIP_ID = T.TRIP_ID
909 -- Using direct route distance
906 AND T.MODE_OF_TRANSPORT = g_tload
907 AND T.PLAN_ID = p_plan_id;
908
910 -- ---------------------------
911 CURSOR cur_tl_cpucd_direct_cust IS
912 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
913 SUM(NVL(DL.DIRECT_DISTANCE, 0) *
914 NVL(D.VOLUME, 0) ) ) AS TLCostPerUnitCubeDist
915 FROM MST_DELIVERY_LEGS DL,
916 MST_DELIVERIES D,
917 MST_TRIPS T,
918 MST_TRIP_STOPS TS
919 WHERE DL.PLAN_ID = D.PLAN_ID
920 AND DL.DELIVERY_ID = D.DELIVERY_ID
921 AND D.CUSTOMER_ID = p_customer_id
922 AND DL.PLAN_ID = TS.PLAN_ID
923 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
924 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
925 AND TS.PLAN_ID = T.PLAN_ID
926 AND TS.TRIP_ID = T.TRIP_ID
927 AND T.MODE_OF_TRANSPORT = g_tload
928 AND T.PLAN_ID = p_plan_id;
929
930 -- Supplier level
931 -- --------------
932 -- ---------------------------------------------------
933 -- Expected result: Given a specific plan and supplier,
934 -- Total TL Cost (as measured by KPI 24) at the
935 -- supplier level divided by Total TL Cube Distance
936 -- for orders corresponding to that supplier.
937 -- ---------------------------------------------------
938 -- Using actual distance traveled
939 -- ------------------------------
940 CURSOR cur_tl_cpucd_act_supp IS
941 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
942 SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
943 NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
944 FROM MST_DELIVERY_LEGS DL,
945 MST_DELIVERIES D,
946 MST_TRIPS T,
947 MST_TRIP_STOPS TS
948 WHERE DL.PLAN_ID = D.PLAN_ID
949 AND DL.DELIVERY_ID = D.DELIVERY_ID
950 AND D.SUPPLIER_ID = p_supplier_id
951 AND DL.PLAN_ID = TS.PLAN_ID
952 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
953 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
954 AND TS.PLAN_ID = T.PLAN_ID
955 AND TS.TRIP_ID = T.TRIP_ID
956 AND T.MODE_OF_TRANSPORT = g_tload
957 AND T.PLAN_ID = p_plan_id;
958
959 -- Using direct route distance
960 -- ---------------------------
961 CURSOR cur_tl_cpucd_direct_supp IS
962 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
963 SUM(NVL(DL.DIRECT_DISTANCE, 0) *
964 NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
965 FROM MST_DELIVERY_LEGS DL,
966 MST_DELIVERIES D,
967 MST_TRIPS T,
968 MST_TRIP_STOPS TS
969 WHERE DL.PLAN_ID = D.PLAN_ID
970 AND DL.DELIVERY_ID = D.DELIVERY_ID
971 AND D.SUPPLIER_ID = p_supplier_id
972 AND DL.PLAN_ID = TS.PLAN_ID
973 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
974 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
975 AND TS.PLAN_ID = T.PLAN_ID
976 AND TS.TRIP_ID = T.TRIP_ID
977 AND T.MODE_OF_TRANSPORT = g_tload
978 AND T.PLAN_ID = p_plan_id;
979
980 -- Facility level
981 -- --------------
982 -- ---------------------------------------------------
983 -- Expected result: Given a specific plan and facility
984 -- (location id), Total TL Cost (as measured by KPI 24)
985 -- divided by Total TL Cube Distance for deliveries
986 -- originating (ship from) or ending (ship to) at that
987 -- facility.
988 -- ---------------------------------------------------
989 -- Using actual distance traveled
990 -- ------------------------------
991 CURSOR cur_tl_cpucd_act_fac(p_location_id IN NUMBER) IS
992 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
993 SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
994 NVL(D.VOLUME, 0) ) ) AS TLCostPerUnitCubeDist
995 FROM MST_DELIVERY_LEGS DL,
996 MST_DELIVERIES D,
997 MST_TRIPS T,
998 MST_TRIP_STOPS TS
999 WHERE DL.PLAN_ID = D.PLAN_ID
1000 AND DL.DELIVERY_ID = D.DELIVERY_ID
1001 AND DL.PLAN_ID = TS.PLAN_ID
1002 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1003 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1004 AND TS.PLAN_ID = T.PLAN_ID
1005 AND TS.TRIP_ID = T.TRIP_ID
1006 AND TS.STOP_LOCATION_ID = p_location_id
1007 AND T.MODE_OF_TRANSPORT = g_tload
1008 AND T.PLAN_ID = p_plan_id;
1009
1010 -- Using direct route distance
1011 -- ---------------------------
1012 CURSOR cur_tl_cpucd_direct_fac(p_location_id IN NUMBER) IS
1013 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1014 SUM(NVL(DL.DIRECT_DISTANCE, 0) *
1015 NVL(D.VOLUME, 0) ) ) TLCostPerUnitCubeDist
1016 FROM MST_DELIVERY_LEGS DL,
1017 MST_DELIVERIES D,
1021 AND DL.DELIVERY_ID = D.DELIVERY_ID
1018 MST_TRIPS T,
1019 MST_TRIP_STOPS TS
1020 WHERE DL.PLAN_ID = D.PLAN_ID
1022 AND DL.PLAN_ID = TS.PLAN_ID
1023 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1024 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1025 AND TS.PLAN_ID = T.PLAN_ID
1026 AND TS.TRIP_ID = T.TRIP_ID
1027 AND TS.STOP_LOCATION_ID = p_location_id
1028 AND T.MODE_OF_TRANSPORT = g_tload
1029 AND T.PLAN_ID = p_plan_id;
1030
1031 l_TLCostPerUnitCubeDist NUMBER;
1032 l_dist_calc_type NUMBER := 1; -- default "actual distance traveled"
1033 l_location_id NUMBER;
1034 BEGIN
1035 OPEN cur_get_parameters;
1036 FETCH cur_get_parameters INTO l_dist_calc_type;
1037 CLOSE cur_get_parameters;
1038 IF l_dist_calc_type = g_act_dist_travel THEN
1039 IF P_LEVEL = g_plan_level THEN
1040 OPEN cur_tl_cpucd_act_plan;
1041 FETCH cur_tl_cpucd_act_plan INTO l_TLCostPerUnitCubeDist;
1042 CLOSE cur_tl_cpucd_act_plan;
1043 ELSIF p_level = g_customer_level THEN
1044 OPEN cur_tl_cpucd_act_cust;
1045 FETCH cur_tl_cpucd_act_cust INTO l_TLCostPerUnitCubeDist;
1046 CLOSE cur_tl_cpucd_act_cust;
1047 ELSIF p_level = g_supplier_level THEN
1048 OPEN cur_tl_cpucd_act_supp;
1049 FETCH cur_tl_cpucd_act_supp INTO l_TLCostPerUnitCubeDist;
1050 CLOSE cur_tl_cpucd_act_supp;
1051 ELSIF p_level = g_carrier_level THEN
1052 OPEN cur_tl_cpucd_act_carr;
1053 FETCH cur_tl_cpucd_act_carr INTO l_TLCostPerUnitCubeDist;
1054 CLOSE cur_tl_cpucd_act_carr;
1055 ELSIF p_level = g_facility_level THEN
1056 l_location_id := get_location(p_fac_loc_id);
1057 OPEN cur_tl_cpucd_act_fac(l_location_id);
1058 FETCH cur_tl_cpucd_act_fac INTO l_TLCostPerUnitCubeDist;
1059 CLOSE cur_tl_cpucd_act_fac;
1060 END IF;
1061 ELSIF l_dist_calc_type = g_dir_route_dist THEN
1062 IF P_LEVEL = g_plan_level THEN
1063 OPEN cur_tl_cpucd_direct_plan;
1064 FETCH cur_tl_cpucd_direct_plan INTO l_TLCostPerUnitCubeDist;
1065 CLOSE cur_tl_cpucd_direct_plan;
1066 ELSIF p_level = g_customer_level THEN
1067 OPEN cur_tl_cpucd_direct_cust;
1068 FETCH cur_tl_cpucd_direct_cust INTO l_TLCostPerUnitCubeDist;
1069 CLOSE cur_tl_cpucd_direct_cust;
1070 ELSIF p_level = g_supplier_level THEN
1071 OPEN cur_tl_cpucd_direct_supp;
1072 FETCH cur_tl_cpucd_direct_supp INTO l_TLCostPerUnitCubeDist;
1073 CLOSE cur_tl_cpucd_direct_supp;
1074 ELSIF p_level = g_carrier_level THEN
1075 OPEN cur_tl_cpucd_direct_carr;
1076 FETCH cur_tl_cpucd_direct_carr INTO l_TLCostPerUnitCubeDist;
1077 CLOSE cur_tl_cpucd_direct_carr;
1078 ELSIF p_level = g_facility_level THEN
1079 l_location_id := get_location(p_fac_loc_id);
1080 OPEN cur_tl_cpucd_direct_fac(l_location_id);
1081 FETCH cur_tl_cpucd_direct_fac INTO l_TLCostPerUnitCubeDist;
1082 CLOSE cur_tl_cpucd_direct_fac;
1083 END IF;
1084 END IF;
1085 IF l_TLCostPerUnitCubeDist IS NULL THEN
1086 l_TLCostPerUnitCubeDist := 0 ;
1087 END IF;
1088 l_TLCostPerUnitCubeDist := round(l_TLCostPerUnitCubeDist, g_precision);
1089 RETURN l_TLCostPerUnitCubeDist;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 RETURN 0;
1094 END TL_Cost_per_Unit_Cube_Dist;
1095
1096 -- TL Cost per Unit Weight-Distance (KPI - 42)
1097 -- ===========================================
1098 FUNCTION TL_Cost_per_Unit_Wt_Dist(p_plan_id IN NUMBER,
1099 p_level IN NUMBER,
1100 p_customer_id IN NUMBER,
1101 p_supplier_id IN NUMBER,
1102 p_carrier_id IN NUMBER,
1103 p_fac_loc_id IN NUMBER)
1104 RETURN NUMBER IS
1105
1106 -- ---------------------------------------------------
1107 -- To compute this KPI we compute Total TL Cost (as
1108 -- measured by KPI 24) divided by TL weight-distance.
1109 -- Total TL Weight Distance is computed, depending on
1110 -- profile options, as
1111 -- (c) Profile option is "actual distance traveled"
1112 -- sum(GrossWeight * DistanceTraveled)
1113 -- (d) Profile option is "direct route distance"
1114 -- sum(GrossWeight * DirectRouteDistance)
1115 -- Calculation of this KPI requires cost-allocation to
1116 -- delivery legs.
1117 -- ---------------------------------------------------
1118
1119 -- Plan level
1120 -- -----------
1121 -- ---------------------------------------------------
1122 -- Expected result: For a given plan, Total TL Cost
1123 -- (as measured by KPI 24) at plan level divided by
1124 -- Total TL Weight Distance.
1125 -- ---------------------------------------------------
1126 -- Using actual distance traveled
1130 T12.TotalTLWeightDist) TLCostPerUnitWtDist
1127 -- -------------------------------
1128 CURSOR cur_tl_cpuwd_act_plan IS
1129 SELECT (T11.TotalTLCost /
1131 FROM ( SELECT SUM(NVL(T.TOTAL_BASIC_TRANSPORT_COST, 0) +
1132 NVL(T.TOTAL_STOP_COST, 0) +
1133 NVL(T.TOTAL_LAYOVER_COST, 0) +
1134 NVL(T.TOTAL_LOAD_UNLOAD_COST, 0) +
1135 NVL(T.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
1136 FROM MST_TRIPS T
1137 WHERE T.PLAN_ID = p_plan_id
1138 AND T.MODE_OF_TRANSPORT = g_tload
1139 ) T11,
1140 ( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
1141 NVL(TS.DEPARTURE_GROSS_WEIGHT, 0) ) TotalTLWeightDist
1142 FROM MST_TRIP_STOPS TS,
1143 MST_TRIPS T1
1144 WHERE TS.PLAN_ID = T1.PLAN_ID
1145 AND TS.TRIP_ID = T1.TRIP_ID
1146 AND T1.PLAN_ID = p_plan_id
1147 AND T1.MODE_OF_TRANSPORT = g_tload
1148 ) T12;
1149
1150 -- Using direct route distance
1151 -- ----------------------------
1152 CURSOR cur_tl_cpuwd_direct_plan IS
1153 SELECT (T11.TotalTLCost /
1154 T12.TotalTLWeightDist) TLCostPerUnitWtDist
1155 FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
1156 NVL(T1.TOTAL_STOP_COST, 0) +
1157 NVL(T1.TOTAL_LAYOVER_COST, 0) +
1158 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
1159 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
1160 FROM MST_TRIPS T1
1161 WHERE T1.PLAN_ID = p_plan_id
1162 AND T1.MODE_OF_TRANSPORT = g_tload
1163 ) T11,
1164 ( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
1165 NVL(D.GROSS_WEIGHT, 0) ) TotalTLWeightDist
1166 FROM MST_DELIVERY_LEGS DL,
1167 MST_DELIVERIES D,
1168 MST_TRIPS T2,
1169 MST_TRIP_STOPS TS
1170 WHERE DL.PLAN_ID = D.PLAN_ID
1171 AND DL.DELIVERY_ID = D.DELIVERY_ID
1172 AND DL.PLAN_ID = TS.PLAN_ID
1173 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1174 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
1175 AND TS.PLAN_ID = T2.PLAN_ID
1176 AND TS.TRIP_ID = T2.TRIP_ID
1177 AND T2.PLAN_ID = p_plan_id
1178 AND T2.MODE_OF_TRANSPORT = g_tload
1179 ) T12;
1180
1181 -- Carrier level
1182 -- -------------
1183 -- ---------------------------------------------------
1184 -- Expected result: For a given plan and carrier, Total
1185 -- TL Cost (as measured by KPI 24) at carrier level
1186 -- divided by Total Weight Distance per carrier.
1187 -- ---------------------------------------------------
1188 -- Using actual distance traveled
1189 -- ------------------------------
1190 CURSOR cur_tl_cpuwd_act_carr IS
1191 SELECT (T11.TotalTLCost / T12.TotalTLWeightDist)
1192 TLCostPerUnitWtDist
1193 FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
1194 NVL(T1.TOTAL_STOP_COST, 0) +
1195 NVL(T1.TOTAL_LAYOVER_COST, 0) +
1196 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
1197 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
1198 FROM MST_TRIPS T1
1199 WHERE T1.PLAN_ID = p_plan_id
1200 AND T1.MODE_OF_TRANSPORT = g_tload
1201 AND T1.CARRIER_ID = p_carrier_id
1202 ) T11,
1203 ( SELECT SUM(NVL(TS.DISTANCE_TO_NEXT_STOP, 0) *
1204 NVL(TS.DEPARTURE_GROSS_WEIGHT, 0) ) TotalTLWeightDist
1205 FROM MST_TRIP_STOPS TS,
1206 MST_TRIPS T2
1207 WHERE TS.PLAN_ID = T2.PLAN_ID
1208 AND TS.TRIP_ID = T2.TRIP_ID
1209 AND T2.PLAN_ID = p_plan_id
1210 AND T2.MODE_OF_TRANSPORT = g_tload
1211 AND T2.CARRIER_ID = p_carrier_id
1212 ) T12;
1213
1214 -- Using direct route distance:
1215 -- ----------------------------
1216 CURSOR cur_tl_cpuwd_direct_carr IS
1217 SELECT (T11.TotalTLCost / T12.TotalTLWeightDist)
1218 TLCostPerUnitWtDist
1219 FROM ( SELECT SUM(NVL(T1.TOTAL_BASIC_TRANSPORT_COST, 0)+
1220 NVL(T1.TOTAL_STOP_COST, 0) +
1221 NVL(T1.TOTAL_LAYOVER_COST, 0) +
1222 NVL(T1.TOTAL_LOAD_UNLOAD_COST, 0) +
1223 NVL(T1.TOTAL_ACCESSORIAL_COST, 0) ) TotalTLCost
1224 FROM MST_TRIPS T1
1225 WHERE T1.PLAN_ID = p_plan_id
1226 AND T1.MODE_OF_TRANSPORT = g_tload
1227 AND T1.CARRIER_ID = p_carrier_id
1228 ) T11,
1232 MST_DELIVERIES D,
1229 ( SELECT SUM(NVL(DL.DIRECT_DISTANCE, 0) *
1230 NVL(D.GROSS_WEIGHT, 0) ) TotalTLWeightDist
1231 FROM MST_DELIVERY_LEGS DL,
1233 MST_TRIPS T2,
1234 MST_TRIP_STOPS TS
1235 WHERE DL.PLAN_ID = D.PLAN_ID
1236 AND DL.DELIVERY_ID = D.DELIVERY_ID
1237 AND DL.PLAN_ID = TS.PLAN_ID
1238 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1239 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID )
1240 AND TS.PLAN_ID = T2.PLAN_ID
1241 AND TS.TRIP_ID = T2.TRIP_ID
1242 AND T2.PLAN_ID = p_plan_id
1243 AND T2.MODE_OF_TRANSPORT = g_tload
1244 AND T2.CARRIER_ID = p_carrier_id
1245 ) T12;
1246
1247
1248 -- Customer level
1249 -- --------------
1250 -- ---------------------------------------------------
1251 -- Expected result: Given a specific plan and customer,
1252 -- the Total TL Cost (as measured by KPI 24) at the
1253 -- customer level divided by Total TL Weight Distance
1254 -- for orders corresponding to that customer.
1255 -- ---------------------------------------------------
1256 -- Using actual distance traveled
1257 -- ------------------------------
1258 CURSOR cur_tl_cpuwd_act_cust IS
1259 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1260 SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
1261 NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
1262 FROM MST_DELIVERY_LEGS DL,
1263 MST_DELIVERIES D,
1264 MST_TRIPS T,
1265 MST_TRIP_STOPS TS
1266 WHERE DL.PLAN_ID = D.PLAN_ID
1267 AND DL.DELIVERY_ID = D.DELIVERY_ID
1268 AND D.CUSTOMER_ID = p_customer_id
1269 AND DL.PLAN_iD = TS.PLAN_ID
1270 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1271 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1272 AND TS.PLAN_ID = T.PLAN_ID
1273 AND TS.TRIP_ID = T.TRIP_ID
1274 AND T.PLAN_ID = p_plan_id
1275 AND T.MODE_OF_TRANSPORT = g_tload;
1276
1277 -- Using direct route distance
1278 -- ---------------------------
1279 CURSOR cur_tl_cpuwd_direct_cust IS
1280 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1281 SUM(NVL(DL.DIRECT_DISTANCE, 0) *
1282 NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
1283 FROM MST_DELIVERY_LEGS DL,
1284 MST_DELIVERIES D,
1285 MST_TRIPS T,
1286 MST_TRIP_STOPS TS
1287 WHERE DL.PLAN_ID = D.PLAN_ID
1288 AND DL.DELIVERY_ID = D.DELIVERY_ID
1289 AND D.CUSTOMER_ID = p_customer_id
1290 AND DL.PLAN_iD = TS.PLAN_ID
1291 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1292 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1293 AND TS.PLAN_ID = T.PLAN_ID
1294 AND TS.TRIP_ID = T.TRIP_ID
1295 AND T.PLAN_ID = p_plan_id
1296 AND T.MODE_OF_TRANSPORT = g_tload;
1297
1298
1299 -- Supplier level
1300 -- --------------
1301 -- ---------------------------------------------------
1302 -- Expected result: Given a specific plan and supplier,
1303 -- Total TL Cost (as measured by KPI 24) at the
1304 -- supplier level divided by Total TL Weight Distance
1305 -- for orders corresponding to that supplier.
1306 -- ---------------------------------------------------
1307 -- Using actual distance traveled
1308 -- ------------------------------
1309 CURSOR cur_tl_cpuwd_act_supp IS
1310 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1311 SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
1312 NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
1313 FROM MST_DELIVERY_LEGS DL,
1314 MST_DELIVERIES D,
1315 MST_TRIPS T,
1316 MST_TRIP_STOPS TS
1317 WHERE DL.PLAN_ID = D.PLAN_ID
1318 AND DL.DELIVERY_ID = D.DELIVERY_ID
1319 AND D.SUPPLIER_ID = p_supplier_id
1320 AND DL.PLAN_iD = TS.PLAN_ID
1321 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1322 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1323 AND TS.PLAN_ID = T.PLAN_ID
1324 AND TS.TRIP_ID = T.TRIP_ID
1325 AND T.PLAN_ID = p_plan_id
1326 AND T.MODE_OF_TRANSPORT = g_tload;
1327
1328 -- Using direct route distance
1329 -- ---------------------------
1330 CURSOR cur_tl_cpuwd_direct_supp IS
1331 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1332 SUM(NVL(DL.DIRECT_DISTANCE, 0) *
1333 NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
1334 FROM MST_DELIVERY_LEGS DL,
1335 MST_DELIVERIES D,
1336 MST_TRIPS T,
1337 MST_TRIP_STOPS TS
1338 WHERE DL.PLAN_ID = D.PLAN_ID
1339 AND DL.DELIVERY_ID = D.DELIVERY_ID
1340 AND D.SUPPLIER_ID = p_supplier_id
1344 AND TS.PLAN_ID = T.PLAN_ID
1341 AND DL.PLAN_iD = TS.PLAN_ID
1342 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1343 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1345 AND TS.TRIP_ID = T.TRIP_ID
1346 AND T.PLAN_ID = p_plan_id
1347 AND T.MODE_OF_TRANSPORT = g_tload;
1348
1349 -- Facility level
1350 -- --------------
1351 -- ---------------------------------------------------
1352 -- Expected result: Given a specific plan and facility
1353 -- (location id), Total TL Cost (as measured by KPI 24)
1354 -- divided by Total TL Weight Distance for orders
1355 -- originating (ship from) or ending (ship to) at
1356 -- that facility.
1357 -- ---------------------------------------------------
1358 -- Using actual distance traveled
1359 -- ------------------------------
1360 CURSOR cur_tl_cpuwd_act_fac(p_location_id IN NUMBER) IS
1361 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1362 SUM(NVL(DL.TRAVELED_DISTANCE, 0) *
1363 NVL(D.GROSS_WEIGHT, 0) ) ) AS TLCostPerUnitWtDist
1364 FROM MST_DELIVERY_LEGS DL,
1365 MST_DELIVERIES D,
1366 MST_TRIPS T,
1367 MST_TRIP_STOPS TS
1368 WHERE DL.PLAN_ID = D.PLAN_ID
1369 AND DL.DELIVERY_ID = D.DELIVERY_ID
1370 AND DL.PLAN_ID = TS.PLAN_ID
1371 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1372 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1373 AND TS.PLAN_ID = T.PLAN_ID
1374 AND TS.TRIP_ID = T.TRIP_ID
1375 AND TS.STOP_LOCATION_ID = p_location_id
1376 AND T.MODE_OF_TRANSPORT = g_tload
1377 AND T.PLAN_ID = p_plan_id;
1378
1379 -- Using direct route distance
1380 -- ---------------------------
1384 NVL(D.GROSS_WEIGHT, 0) ) ) TLCostPerUnitWtDist
1381 CURSOR cur_tl_cpuwd_direct_fac(p_location_id IN NUMBER) IS
1382 SELECT (SUM(DL.ALLOCATED_TRANSPORT_COST) /
1383 SUM(NVL(DL.DIRECT_DISTANCE, 0) *
1385 FROM MST_DELIVERY_LEGS DL,
1386 MST_DELIVERIES D,
1387 MST_TRIPS T,
1388 MST_TRIP_STOPS TS
1389 WHERE DL.PLAN_ID = D.PLAN_ID
1390 AND DL.DELIVERY_ID = D.DELIVERY_ID
1391 AND DL.PLAN_ID = TS.PLAN_ID
1392 AND ( DL.PICK_UP_STOP_ID = TS.STOP_ID
1393 OR DL.DROP_OFF_STOP_ID = TS.STOP_ID)
1394 AND TS.PLAN_ID = T.PLAN_ID
1395 AND TS.TRIP_ID = T.TRIP_ID
1396 AND TS.STOP_LOCATION_ID = p_location_id
1397 AND T.MODE_OF_TRANSPORT = g_tload
1398 AND T.PLAN_ID = p_plan_id;
1399
1400 l_TLCostPerUnitWtDist NUMBER;
1401 l_dist_calc_type NUMBER := 1; -- default "actual distance traveled"
1402 l_location_id NUMBER;
1403
1404 BEGIN
1405
1406 OPEN cur_get_parameters;
1407 FETCH cur_get_parameters INTO l_dist_calc_type;
1408 CLOSE cur_get_parameters;
1409
1410 IF l_dist_calc_type = g_act_dist_travel THEN
1411 IF P_LEVEL = g_plan_level THEN
1412 OPEN cur_tl_cpuwd_act_plan;
1413 FETCH cur_tl_cpuwd_act_plan INTO l_TLCostPerUnitWtDist;
1414 CLOSE cur_tl_cpuwd_act_plan;
1415 ELSIF p_level = g_customer_level THEN
1416 OPEN cur_tl_cpuwd_act_cust;
1417 FETCH cur_tl_cpuwd_act_cust INTO l_TLCostPerUnitWtDist;
1418 CLOSE cur_tl_cpuwd_act_cust;
1419 ELSIF p_level = g_supplier_level THEN
1420 OPEN cur_tl_cpuwd_act_supp;
1421 FETCH cur_tl_cpuwd_act_supp INTO l_TLCostPerUnitWtDist;
1422 CLOSE cur_tl_cpuwd_act_supp;
1423 ELSIF p_level = g_carrier_level THEN
1424 OPEN cur_tl_cpuwd_act_carr;
1425 FETCH cur_tl_cpuwd_act_carr INTO l_TLCostPerUnitWtDist;
1426 CLOSE cur_tl_cpuwd_act_carr;
1427 ELSIF p_level = g_facility_level THEN
1428 l_location_id := get_location(p_fac_loc_id);
1429 OPEN cur_tl_cpuwd_act_fac(l_location_id);
1430 FETCH cur_tl_cpuwd_act_fac INTO l_TLCostPerUnitWtDist;
1431 CLOSE cur_tl_cpuwd_act_fac;
1432 END IF;
1433 ELSIF l_dist_calc_type = g_dir_route_dist THEN
1434 IF P_LEVEL = g_plan_level THEN
1435 OPEN cur_tl_cpuwd_direct_plan;
1436 FETCH cur_tl_cpuwd_direct_plan INTO l_TLCostPerUnitWtDist;
1437 CLOSE cur_tl_cpuwd_direct_plan;
1438 ELSIF p_level = g_customer_level THEN
1439 OPEN cur_tl_cpuwd_direct_cust;
1440 FETCH cur_tl_cpuwd_direct_cust INTO l_TLCostPerUnitWtDist;
1441 CLOSE cur_tl_cpuwd_direct_cust;
1442 ELSIF p_level = g_supplier_level THEN
1443 OPEN cur_tl_cpuwd_direct_supp;
1444 FETCH cur_tl_cpuwd_direct_supp INTO l_TLCostPerUnitWtDist;
1445 CLOSE cur_tl_cpuwd_direct_supp;
1446 ELSIF p_level = g_carrier_level THEN
1447 OPEN cur_tl_cpuwd_direct_carr;
1448 FETCH cur_tl_cpuwd_direct_carr INTO l_TLCostPerUnitWtDist;
1449 CLOSE cur_tl_cpuwd_direct_carr;
1450 ELSIF p_level = g_facility_level THEN
1451 l_location_id := get_location(p_fac_loc_id);
1452 OPEN cur_tl_cpuwd_direct_fac(l_location_id);
1453 FETCH cur_tl_cpuwd_direct_fac INTO l_TLCostPerUnitWtDist;
1454 CLOSE cur_tl_cpuwd_direct_fac;
1455 END IF;
1456 END IF;
1457 IF l_TLCostPerUnitWtDist IS NULL THEN
1458 l_TLCostPerUnitWtDist := 0;
1459 END IF;
1460 l_TLCostPerUnitWtDist := round(l_TLCostPerUnitWtDist, g_precision);
1461
1462 RETURN l_TLCostPerUnitWtDist;
1463
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 RETURN 0;
1467 END TL_Cost_per_Unit_Wt_Dist;
1468
1469 FUNCTION mst_performance_targets(p_target_level IN VARCHAR2,
1470 p_dimension_id IN VARCHAR2 )
1471 RETURN NUMBER IS
1472
1473 CURSOR get_target_id(p_tgt_short_name IN VARCHAR2,
1474 p_dim_id IN VARCHAR2 ) IS
1475 SELECT t.target_id
1476 FROM bisfv_targets t,
1477 bisbv_target_levels tl,
1478 bisbv_application_measures am
1479 WHERE am.measure_short_name = p_tgt_short_name
1480 AND am.measure_id = tl.measure_id
1481 AND tl.target_level_id = t.target_level_id
1482 AND decode(p_tgt_short_name,'MST_COST_PU_WT_PLAN' ,t.dim1_level_value_id
1483 ,'MST_COST_PU_VOL_PLAN' ,t.dim1_level_value_id
1484 ,'MST_COST_PU_DIST_PLAN' ,t.dim1_level_value_id
1485 ,'MST_COST_PU_VOL_DIST_PLAN' ,t.dim1_level_value_id
1486 ,'MST_COST_PU_WT_DIST_PLAN' ,t.dim1_level_value_id
1487 ,'MST_COST_PU_VOL_DIST_CARR' ,t.dim3_level_value_id
1488 ,'MST_COST_PU_WT_CARR' ,t.dim3_level_value_id
1489 ,'MST_COST_PU_VOL_CARR' ,t.dim3_level_value_id
1490 ,'MST_COST_PU_DIST_CARR' ,t.dim3_level_value_id
1491 ,'MST_COST_PU_WT_DIST_CARR' ,t.dim3_level_value_id
1492 ,'MST_COST_PU_WT_CUST' ,t.dim1_level_value_id
1493 ,'MST_COST_PU_VOL_CUST' ,t.dim1_level_value_id
1497 ,'MST_COST_PU_VOL_SUPP' ,t.dim2_level_value_id
1494 ,'MST_COST_PU_WT_DIST_CUST' ,t.dim1_level_value_id
1495 ,'MST_COST_PU_VOL_DIST_CUST' ,t.dim1_level_value_id
1496 ,'MST_COST_PU_WT_SUPP' ,t.dim2_level_value_id
1498 ,'MST_COST_PU_VOL_DIST_SUPP' ,t.dim2_level_value_id
1499 ,'MST_COST_PU_WT_DIST_SUPP' ,t.dim2_level_value_id
1500 ,'MST_COST_PU_WT_FAC' ,t.dim4_level_value_id
1501 ,'MST_COST_PU_VOL_FAC' ,t.dim4_level_value_id
1502 ,'MST_COST_PU_VOL_DIST_FAC' ,t.dim4_level_value_id
1503 ,'MST_COST_PU_WT_DIST_FAC' ,t.dim4_level_value_id
1504 ,'MST_COST_PU_WT_MODE' ,t.dim5_level_value_id
1505 ,'MST_COST_PU_VOL_MODE' ,t.dim5_level_value_id
1506 ,t.dim1_level_value_id) = p_dim_id;
1507
1508 l_return_status VARCHAR2(3);
1509 l_error_tbl BIS_UTILITIES_PUB.error_tbl_type;
1510
1511 l_target_rec BIS_TARGET_PUB.target_rec_type;
1512 l_target_rec_out BIS_TARGET_PUB.target_rec_type;
1513
1514 invalid_target EXCEPTION;
1515
1516 BEGIN
1517
1518 OPEN get_target_id(p_target_level, p_dimension_id);
1519 FETCH get_target_id INTO l_target_rec.target_id;
1520 IF get_target_id%found THEN
1521 BIS_Target_Pub.retrieve_target(p_api_version => 1,
1522 p_target_rec => l_target_rec,
1523 p_all_info => FND_API.G_TRUE,
1524 x_target_rec => l_target_rec_out,
1525 x_return_status => l_return_status,
1526 x_error_tbl => l_error_tbl);
1527 IF l_return_status = 'E' THEN
1528 CLOSE get_target_id;
1529 RAISE invalid_target;
1530 END IF;
1531
1532 --DBMS_OUTPUT.put_line('Range1_low is '||l_target_rec_out.Range1_low||
1533 -- ' and Hig val is '||l_target_rec_out.Range1_high||
1534 -- ' and target is '||l_target_rec_out.target);
1535 ELSE
1536 --dbms_output.put_line('target not defined!!!');
1537 NULL;
1538 END IF;
1539 CLOSE get_target_id;
1540 RETURN NVL(l_target_rec_out.target,0);
1541 EXCEPTION
1542 WHEN invalid_target THEN
1543 for i in 1..l_error_tbl.count loop
1544 DBMS_OUTPUT.put_line('error is '||l_error_tbl(i).Error_Description);
1545 end loop;
1546 RETURN 0;
1547 WHEN OTHERS THEN
1548 DBMS_OUTPUT.put_line('error is '||sqlerrm(sqlcode));
1549 return 0;
1550 END mst_performance_targets;
1551 END MST_CMP_KPIS;