1 PACKAGE BODY MST_AGG_PKG AS
2 /*$Header: MSTAGGQB.pls 115.46 2004/07/01 14:41:21 skakani noship $ */
3 /* Plan Summary*/
4 function get_loads_count(p_plan_id in number)
5 return number is
6 l_count number;
7 begin
8 select count(1)
9 into l_count
10 from mst_trips
11 where plan_id = p_plan_id
12 and nvl(move_type, 2) <> 1;
13 return l_count;
14
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 return 0;
18 end;
19
20 function get_trip_count(p_plan_id in number, p_mode_of_transport in varchar2)
21 return number is
22 l_count number;
23 begin
24 select count(1)
25 into l_count
26 from mst_trips
27 where plan_id = p_plan_id
28 and mode_of_transport = p_mode_of_transport
29 and nvl(move_type, 2) <> 1 ;
30 return l_count;
31
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 return 0;
35 end;
36
37 function get_cm_count(p_plan_id in number)
38 return number is
39 l_count number;
40 begin
41 select count(1)
42 into l_count
43 from mst_cm_trips
44 where plan_id = p_plan_id;
45 return l_count;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 return 0;
49 end;
50
51 function get_loads_orders(p_plan_id in number)
52 return number is
53 l_count number;
54 begin
55 select total_orders
56 into l_count
57 from mst_plans
58 where plan_id = p_plan_id;
59 return l_count;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 return 0;
63 end;
64
65 function get_trip_orders(p_plan_id in number, p_mode_of_transport in varchar2)
66 return number is
67 l_count number;
68 begin
69 select decode(p_mode_of_transport, 'TRUCK', total_tl_orders, 'LTL', total_ltl_orders, 'PARCEL', total_parcel_orders, 0)
70 into l_count
71 from mst_plans
72 where plan_id = p_plan_id;
73 return l_count;
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 return 0;
77 end;
78
79 function get_cm_orders(p_plan_id in number)
80 return number is
81 l_count number;
82 begin
83 select total_cm_orders
84 into l_count
85 from mst_plans
86 where plan_id = p_plan_id;
87 return l_count;
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 return 0;
91 end;
92
93
94 function get_unassigned_order_count(p_plan_id in number)
95 return number is
96 l_count number;
97 begin
98 select count(1)
99 into l_count
100 from mst_deliveries
101 where plan_id = p_plan_id
102 and delivery_id not in (
103 select delivery_id
104 from mst_delivery_legs
105 where plan_id = p_plan_id
106 );
107 return nvl(l_count,0);
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 return 0;
111 end;
112
113 function get_unassigned_order_weight(p_plan_id in number)
114 return number is
115 l_count number;
116 begin
117 select sum(gross_weight)
118 into l_count
119 from mst_deliveries
120 where plan_id = p_plan_id
121 and delivery_id not in (
122 select delivery_id
123 from mst_delivery_legs
124 where plan_id = p_plan_id
125 );
126 return nvl(l_count,0);
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 return 0;
130 end;
131 function get_unassigned_order_cube(p_plan_id in number)
132 return number is
133 l_count number;
134 begin
135 select sum(volume)
136 into l_count
137 from mst_deliveries
138 where plan_id = p_plan_id
139 and delivery_id not in (
140 select delivery_id
141 from mst_delivery_legs
142 where plan_id = p_plan_id
143 );
144 return nvl(l_count,0);
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 return 0;
148 end;
149
150 function get_unassigned_order_pieces(p_plan_id in number)
151 return number is
152 l_count number;
153 begin
154 select sum(number_of_pieces)
155 into l_count
156 from mst_deliveries
157 where plan_id = p_plan_id
158 and delivery_id not in (
159 select delivery_id
160 from mst_delivery_legs
161 where plan_id = p_plan_id
162 );
163 return nvl(l_count,0);
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 return 0;
167 end;
168
169 function get_unassigned_order_pallets(p_plan_id in number)
170 return number is
171 l_count number;
172 begin
173 select sum(number_of_pallets)
174 into l_count
175 from mst_deliveries
176 where plan_id = p_plan_id
177 and delivery_id not in (
178 select delivery_id
179 from mst_delivery_legs
180 where plan_id = p_plan_id
181 );
182 return nvl(l_count,0);
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 return 0;
186 end;
187
188 /* All Truckloads Summary */
189 function get_summary_fixed_charge(p_plan_id in number, p_mode in varchar2)
190 return number is
191 l_fixed_charge number;
192 begin
193 select sum(total_basic_transport_cost)
194 into l_fixed_charge
195 from mst_trips
196 where plan_id = p_plan_id
197 and mode_of_transport = p_mode;
198 return l_fixed_charge;
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 l_fixed_charge := 0;
202 return l_fixed_charge;
203 end;
204
205 function get_summary_stop_charge(p_plan_id in number)
206 return number is
207 l_stop_charge number;
208 begin
209 select sum(total_stop_cost)
210 into l_stop_charge
211 from mst_trips
212 where plan_id = p_plan_id
213 and mode_of_transport = 'TRUCK';
214 return l_stop_charge;
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 l_stop_charge := 0;
218 return l_stop_charge;
219 end;
220
221 function get_summary_ld_unld_charge(p_plan_id in number)
222 return number is
223 l_ld_unld_charge number;
224 begin
225 select sum(total_load_unload_cost)
226 into l_ld_unld_charge
227 from mst_trips
228 where plan_id = p_plan_id
229 and mode_of_transport = 'TRUCK';
230 return l_ld_unld_charge;
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 l_ld_unld_charge := 0;
234 return l_ld_unld_charge;
235 end;
236
237 function get_summary_layover_charge(p_plan_id in number)
238 return number is
239 l_layover_charge number;
240 begin
241 select sum(total_layover_cost)
242 into l_layover_charge
243 from mst_trips
244 where plan_id = p_plan_id
245 and mode_of_transport = 'TRUCK';
246 return l_layover_charge;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 l_layover_charge := 0;
250 return l_layover_charge;
251 end;
252
253 function get_summary_assessorial_charge(p_plan_id in number, p_mode in varchar2)
254 return number is
255 l_assessorial_charge number;
256 begin
257 select sum(total_accessorial_cost)
258 into l_assessorial_charge
259 from mst_trips
260 where plan_id = p_plan_id
261 and mode_of_transport = p_mode;
262 return l_assessorial_charge;
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265 l_assessorial_charge := 0;
266 return l_assessorial_charge;
267 end;
268
269 /* TruckLoad Details */
270 FUNCTION Get_alloc_cost_for_delivery(p_plan_id IN NUMBER,
271 p_delivery_id IN NUMBER)
272 RETURN NUMBER IS
273
274 CURSOR cur_alloc_cost IS
275 SELECT SUM(dd.allocated_Cost)
276 FROM mst_delivery_details dd,
277 mst_delivery_assignments da
278 WHERE dd.plan_id = p_plan_id
279 AND da.plan_id = dd.plan_id
280 AND da.delivery_detail_id = dd.delivery_detail_id
281 and da.parent_delivery_detail_id is null
282 AND da.delivery_id = p_delivery_id;
283
284 l_cost NUMBER;
285
286 BEGIN
287 OPEN cur_alloc_cost;
288 FETCH cur_alloc_cost INTO l_cost;
289 CLOSE cur_alloc_cost;
290 RETURN l_cost;
291 EXCEPTION
292 WHEN OTHERS THEN
293 RETURN 0;
294 END Get_alloc_cost_for_delivery;
295
296 /* Vehicle Trip Stop Details */
297 function get_loading_charge(p_plan_id IN NUMBER,
298 p_stop_id in number)
299 return number is
300 l_loading_charge number;
301 begin
302 -- SQL repository issues as on 25-05-04:
303 -- Added new parameter plan_id
304 -- Added join for plan id
305 select sum(mdl.allocated_fac_loading_cost)
306 into l_loading_charge
307 from mst_trip_stops mts,
308 mst_delivery_legs mdl
309 where mts.stop_id = p_stop_id
310 AND mts.plan_id = P_PLAN_ID
311 AND mts.plan_id = mdl.plan_Id
312 and mts.stop_id = mdl.pick_up_stop_id;
313 return l_loading_charge;
314 EXCEPTION
315 WHEN OTHERS THEN
316 RETURN 0;
317 end;
318
319 function get_unloading_charge(p_stop_id in number)
320 return number is
321 l_unloading_charge number;
322 begin
323 select sum(mdl.allocated_fac_unloading_cost)
324 into l_unloading_charge
325 from mst_trip_stops mts,
326 mst_delivery_legs mdl
327 where mts.stop_id = p_stop_id
328 and mts.stop_id = mdl.drop_off_stop_id;
329 return l_unloading_charge;
330 EXCEPTION
331 WHEN OTHERS THEN
332 RETURN 0;
333 end;
334 -- Loading Weight/Cube/Pallets/Pieces
335 function get_loading_weight(p_stop_id in number)
336 return number is
337 l_loading_weight number;
338 begin
339 select sum(md.gross_weight)
340 into l_loading_weight
341 from mst_trip_stops mts,
342 mst_delivery_legs mdl,
343 mst_deliveries md
344 where mts.stop_id = p_stop_id
345 and mts.stop_id = mdl.pick_up_stop_id
346 and mdl.delivery_id = md.delivery_id;
347 return l_loading_weight;
348 EXCEPTION
349 WHEN OTHERS THEN
350 RETURN 0;
351 end;
352
353 function get_loading_volume(p_stop_id in number)
354 return number is
355 l_loading_volume number;
356 begin
357 select sum(md.volume)
358 into l_loading_volume
359 from mst_trip_stops mts,
360 mst_delivery_legs mdl,
361 mst_deliveries md
362 where mts.stop_id = p_stop_id
363 and mts.stop_id = mdl.pick_up_stop_id
364 and mdl.delivery_id = md.delivery_id;
365 return l_loading_volume;
366 EXCEPTION
367 WHEN OTHERS THEN
368 RETURN 0;
369 end;
370
371 function get_loading_pallets(p_stop_id in number)
372 return number is
376 into l_loading_pallets
373 l_loading_pallets number;
374 begin
375 select sum(md.volume)
377 from mst_trip_stops mts,
378 mst_delivery_legs mdl,
379 mst_deliveries md
380 where mts.stop_id = p_stop_id
381 and mts.stop_id = mdl.pick_up_stop_id
382 and mdl.delivery_id = md.delivery_id;
383 return l_loading_pallets;
384 EXCEPTION
385 WHEN OTHERS THEN
386 RETURN 0;
387 end;
388
389 function get_loading_pieces(p_stop_id in number)
390 return number is
391 l_loading_pieces number;
395 from mst_trip_stops mts,
392 begin
393 select sum(md.number_of_pieces)
394 into l_loading_pieces
396 mst_delivery_legs mdl,
397 mst_deliveries md
398 where mts.stop_id = p_stop_id
399 and mts.stop_id = mdl.pick_up_stop_id
400 and mdl.delivery_id = md.delivery_id;
401 return l_loading_pieces;
402 EXCEPTION
403 WHEN OTHERS THEN
404 RETURN 0;
405 end;
406
407 -- Unloading Weight/Cube/Pallets/Pieces
408 function get_unloading_weight(p_stop_id in number)
409 return number is
410 l_unloading_weight number;
411 begin
412 select sum(md.gross_weight)
413 into l_unloading_weight
414 from mst_trip_stops mts,
415 mst_delivery_legs mdl,
421 EXCEPTION
416 mst_deliveries md
417 where mts.stop_id = p_stop_id
418 and mts.stop_id = mdl.drop_off_stop_id
419 and mdl.delivery_id = md.delivery_id;
420 return l_unloading_weight;
422 WHEN OTHERS THEN
423 RETURN 0;
424 end;
425
426 function get_unloading_volume(p_stop_id in number)
427 return number is
428 l_unloading_volume number;
429 begin
430 select sum(md.volume)
431 into l_unloading_volume
432 from mst_trip_stops mts,
433 mst_delivery_legs mdl,
437 and mdl.delivery_id = md.delivery_id;
434 mst_deliveries md
435 where mts.stop_id = p_stop_id
436 and mts.stop_id = mdl.drop_off_stop_id
438 return l_unloading_volume;
439 EXCEPTION
440 WHEN OTHERS THEN
441 RETURN 0;
442 end;
443
444 function get_unloading_pallets(p_stop_id in number)
445 return number is
446 l_unloading_pallets number;
447 begin
448 select sum(md.number_of_pallets)
449 into l_unloading_pallets
450 from mst_trip_stops mts,
451 mst_delivery_legs mdl,
452 mst_deliveries md
453 where mts.stop_id = p_stop_id
454 and mts.stop_id = mdl.drop_off_stop_id
455 and mdl.delivery_id = md.delivery_id;
456 return l_unloading_pallets;
457 EXCEPTION
458 WHEN OTHERS THEN
459 RETURN 0;
460 end;
461
462 function get_unloading_pieces(p_stop_id in number)
463 return number is
464 l_unloading_pieces number;
465 begin
466 select sum(md.number_of_pieces)
467 into l_unloading_pieces
468 from mst_trip_stops mts,
469 mst_delivery_legs mdl,
470 mst_deliveries md
471 where mts.stop_id = p_stop_id
472 and mts.stop_id = mdl.drop_off_stop_id
473 and mdl.delivery_id = md.delivery_id;
474
478 RETURN 0;
475 return l_unloading_pieces;
476 EXCEPTION
477 WHEN OTHERS THEN
479 end;
480
481 /* Vehicle Trip Leg Details */
482 -- None aggregation fields:
483 -- On board weight/cube/pallets/pieces can be fetched directly from MST_TRIP_STOPS
484 -- the columns are departure weight/cube/pieces/pallets of the first stop
485
486 -- On board orders:
487 ---- ?????
488
489 -- Distance Charge:
490 ---- ?????
491
492 -- Max weight/cube/pallets for a vehicle
493 /*
494 select
495 item.maximum_load_weight max_load_weight,
496 ??decode() max_load_volume -- based on direct move, pool move, stop numbers, the effective cap could be different
497 pallet_floor_space * pallet_stacking_height max_number_of_pallets
498 from fte_vehicle_types fte, mtl_system_items item
499 where vehicle_type_id = p_vehicle_type_id
500 and fte.organization_id = item.organization_id
501 and fte.inventory_item_id = item.inventory_item_id
502 */
503
504 /* Delivery Details */
505 function get_delivery_cost(p_delivery_id in number)
506 return number is
507 l_delivery_cost number;
508 begin
509 select sum(allocated_transport_cost + allocated_fac_loading_cost + allocated_fac_unloading_cost)
510 into l_delivery_cost
511 from mst_delivery_legs
512 where delivery_id = p_delivery_id;
513
514 return l_delivery_cost;
515 EXCEPTION
516 WHEN OTHERS THEN
517 RETURN 0;
518 end;
519
520 /* LTL/Parcel Summary */
521 -- Weight Based/Minimum charges
522 ---- use get_summary_fixed_charge() with appropriate mode and plan_id
523
524 -- Assessorial charges
525 ---- use get_summary_assessorial_charge() with appropriate mode and plan_id
526
527 /* LTL/Parcel Details */
528 -- no aggregate functions, cost/weight/cube/pallets/pieces can be fetched
529 -- directly from base tables in view
530
531
532 /* All Continuous Moves */
533 function get_total_savings(p_plan_id in number)
534 return number is
535 l_total_cm_cost number;
536 l_total_cost_for_TLs_with_CMs number;
537 l_total_savings number;
538 begin
539 select total_cm_cost
540 into l_total_cm_cost
541 from mst_plans
542 where plan_id = p_plan_id;
543
544 select
545 sum(total_basic_transport_cost + total_stop_cost
546 + total_load_unload_cost + total_layover_cost
547 + total_accessorial_cost + total_handling_cost)
548 into l_total_cost_for_TLs_with_CMs
549 from mst_trips
550 where plan_id = p_plan_id
551 and continuous_move_id is not null
552 and mode_of_transport = 'TRUCK';
553
554 l_total_savings := l_total_cost_for_TLs_with_CMs - l_total_cm_cost;
555 return l_total_savings;
556 EXCEPTION
557 WHEN OTHERS THEN
558 RETURN 0;
559 end;
560
561 function get_percent_of_tl_in_cm(p_plan_id in number)
562 return number is
563 l_total_number_of_tl number;
564 l_total_number_of_tl_in_cm number;
565 l_percent number;
566 begin
567 select count(1)
568 into l_total_number_of_tl
569 from mst_trips
570 where plan_id = p_plan_id
571 and mode_of_transport = 'TRUCK';
572
573 select count(1)
574 into l_total_number_of_tl_in_cm
575 from mst_trips
576 where plan_id = p_plan_id
577 and continuous_move_id is not null
578 and mode_of_transport = 'TRUCK';
579
580 l_percent := l_total_number_of_tl_in_cm/l_total_number_of_tl;
581 return l_percent;
582 EXCEPTION
583 WHEN OTHERS THEN
584 RETURN 0;
585 end;
586
587 /* Continuous Move Details */
588 -- open issues
589
590 /* All Orders */
591 -- number of orders, total costs can be fetched directly from MST_PLANS
592 /* Order Details */
593 function get_order_weight(p_source_code in varchar2,
594 p_source_header_number in varchar2)
595 return number is
596
597 l_order_weight number;
598
599 begin
600 select sum(mdd.net_weight)
601 into l_order_weight
602 from mst_delivery_details mdd
603 where mdd.source_code = p_source_code
604 and mdd.source_header_number = p_source_header_number;
605
606 return l_order_weight;
607 EXCEPTION
608 WHEN OTHERS THEN
609 RETURN 0;
610 end;
611
612 function get_order_cube(p_source_code in varchar2,
613 p_source_header_number in varchar2)
614 return number is
615
616 l_order_cube number;
617
618 begin
619 select sum(mdd.volume)
620 into l_order_cube
621 from mst_delivery_details mdd
622 where mdd.source_code = p_source_code
623 and mdd.source_header_number = p_source_header_number;
624
625 return l_order_cube;
626 EXCEPTION
627 WHEN OTHERS THEN
628 RETURN 0;
629 end;
630
631 function get_order_pallets(p_source_code in varchar2,
632 p_source_header_number in varchar2)
633 return number is
634
635 l_order_pallets number;
636
637 begin
638 select sum(ceil(mdd.number_of_pallets))
639 into l_order_pallets
643
640 from mst_delivery_details mdd
641 where mdd.source_code = p_source_code
642 and mdd.source_header_number = p_source_header_number;
644 return l_order_pallets;
645 EXCEPTION
646 WHEN OTHERS THEN
647 RETURN 0;
648 end;
649
650 function get_order_pieces(p_source_code in varchar2,
651 p_source_header_number in varchar2)
652 return number is
653
654 l_order_pieces number;
655
656 begin
657 select sum(mdd.requested_quantity)
658 into l_order_pieces
659 from mst_delivery_details mdd
660 where mdd.source_code = p_source_code
661 and mdd.source_header_number = p_source_header_number;
662
663 return l_order_pieces;
664 EXCEPTION
665 WHEN OTHERS THEN
666 RETURN 0;
667 end;
668
669 /* Carrier Details */
670
671 function get_carrier_total_cost(p_plan_id in number, p_carrier_id in number)
672 return number is
673 l_carrier_cost number;
674 l_carrier_tl_cost number;
675 l_carrier_ltl_parcel_cost number;
676 l_carrier_cm_cost number;
677
678 begin
679 select sum(total_basic_transport_cost + total_stop_cost + total_load_unload_cost + total_layover_cost + total_accessorial_cost + total_handling_cost)
680 into l_carrier_tl_cost
681 from mst_trips
682 where plan_id = p_plan_id
683 and carrier_id = p_carrier_id
684 and mode_of_transport = 'TRUCK'
685 and continuous_move_id is null;
686
687 select sum(total_basic_transport_cost + total_accessorial_cost)
688 into l_carrier_ltl_parcel_cost
689 from mst_trips
690 where plan_id = p_plan_id
691 and carrier_id = p_carrier_id
692 and mode_of_transport in ('LTL', 'PARCEL');
693
694 select sum(TOTAL_TRANSPORTATION_COST)
695 into l_carrier_cm_cost
696 from mst_cm_trips
697 where plan_id = p_plan_id
698 and carrier_id = p_carrier_id;
699 l_carrier_cost := l_carrier_tl_cost + l_carrier_cm_cost + l_carrier_ltl_parcel_cost;
700 return l_carrier_cost;
701 EXCEPTION
702 WHEN OTHERS THEN
703 RETURN 0;
704 end;
705
706 FUNCTION get_carrier_weight(p_plan_id IN NUMBER, p_carrier_id IN NUMBER)
707 RETURN NUMBER IS
708 l_carrier_weight NUMBER;
709 l_carrier_weight_tmp NUMBER;
710 BEGIN
711 -- ----------------------------------
712 -- Re-writing the sql for bug#3335462
713 -- ----------------------------------
714 /*
715 SELECT SUM(md.gross_weight)
716 INTO l_carrier_weight
717 FROM MST_DELIVERIES md
718 WHERE md.plan_id = p_plan_id
719 AND md.delivery_id IN
720 ( SELECT mdl.delivery_id
721 FROM MST_DELIVERY_LEGS mdl,
722 MST_TRIPS mt,
723 MST_TRIP_STOPS mts
724 WHERE mt.plan_id = md.plan_id
725 AND mt.carrier_id = p_carrier_id
726 AND mt.plan_id = mdl.plan_id
727 AND mt.trip_id = mdl.trip_id
728 AND mt.plan_id = mts.plan_id
729 AND mt.trip_id = mts.trip_id
730 AND mts.stop_location_id in
731 (SELECT wlo.wsh_location_id
732 FROM WSH_LOCATION_OWNERS wlo
733 WHERE wlo.owner_party_id = mt.carrier_id) );
734 */
735 -- ----------------------------------------------------------------------------
736 -- As per bug#3546059 and 3546163, We need to consider all the trips that touch
737 -- the facility owned by a specific carrier.
738 -- Also, we need to double count KPIs - weight/Cube/pallets/Pieces/Orders
739 -- for unload and load.
740 -- ----------------------------------------------------------------------------
741 SELECT SUM(md.gross_weight)
742 INTO l_carrier_weight
743 FROM mst_deliveries md
744 WHERE md.plan_id = p_plan_id
745 AND md.delivery_id IN
746 ( SELECT mdl.delivery_id
747 FROM mst_delivery_legs mdl,
748 mst_trip_stops mts
749 WHERE mdl.plan_id = md.plan_id
750 AND mdl.plan_id = mts.plan_id
751 AND mdl.pick_up_stop_id = mts.stop_id
752 AND EXISTS
753 (SELECT 1
754 FROM wsh_location_owners wlo
755 WHERE wlo.owner_party_id = p_carrier_id
756 AND wlo.owner_type = CARRIER
757 AND wlo.wsh_location_id = mts.stop_location_id) );
758 l_carrier_weight_tmp := NVL(l_carrier_weight,0);
759
760 l_carrier_weight := 0;
761
762 SELECT SUM(md.gross_weight)
763 INTO l_carrier_weight
764 FROM mst_deliveries md
765 WHERE md.plan_id = p_plan_id
766 AND md.delivery_id IN
767 ( SELECT mdl.delivery_id
768 FROM mst_delivery_legs mdl,
769 mst_trip_stops mts
770 WHERE mdl.plan_id = md.plan_id
771 AND mdl.plan_id = mts.plan_id
775 FROM wsh_location_owners wlo
772 AND mdl.drop_off_stop_id = mts.stop_id
773 AND EXISTS
774 (SELECT 1
776 WHERE wlo.owner_party_id = p_carrier_id
777 AND wlo.owner_type = CARRIER
778 AND wlo.wsh_location_id = mts.stop_location_id) );
779
780 l_carrier_weight := l_carrier_weight_tmp + NVL(l_carrier_weight,0);
781 RETURN l_carrier_weight;
782 EXCEPTION
783 WHEN OTHERS THEN
784 RETURN 0;
785 END get_carrier_weight;
786
787 FUNCTION get_carrier_volume(p_plan_id IN NUMBER,p_carrier_id IN NUMBER)
788 RETURN NUMBER IS
789 l_carrier_volume NUMBER;
790 l_carrier_volume_tmp NUMBER;
791 BEGIN
792 -- ----------------------------------
793 -- Re-writing the sql for bug#3335462
794 -- ----------------------------------
795 /*********************
796 SELECT SUM(md.volume)
797 INTO l_carrier_volume
798 FROM MST_DELIVERIES md
799 WHERE md.plan_id = p_plan_id
800 AND md.delivery_id IN
801 ( SELECT mdl.delivery_id
802 FROM MST_DELIVERY_LEGS mdl,
803 MST_TRIPS mt,
804 MST_TRIP_STOPS mts
805 WHERE mt.plan_id = md.plan_id
806 AND mt.carrier_id = p_carrier_id
807 AND mt.plan_id = mdl.plan_id
808 AND mt.trip_id = mdl.trip_id
809 AND mt.plan_id = mts.plan_id
810 AND mt.trip_id = mts.trip_id
811 AND mts.stop_location_id in
812 (SELECT wlo.wsh_location_id
813 FROM WSH_LOCATION_OWNERS wlo
814 WHERE wlo.owner_party_id = mt.carrier_id) );
815 **************************/
816 -- ----------------------------------------------------------------------------
817 -- As per bug#3546059 and 3546163, We need to consider all the trips that touch
818 -- the facility owned by a specific carrier.
819 -- Also, we need to double count KPIs - weight/Cube/pallets/Pieces/Orders
820 -- for unload and load.
821 -- ----------------------------------------------------------------------------
822 SELECT SUM(md.volume)
823 INTO l_carrier_volume
824 FROM mst_deliveries md
825 WHERE md.plan_id = p_plan_id
826 AND md.delivery_id IN
827 ( SELECT mdl.delivery_id
828 FROM mst_delivery_legs mdl,
829 mst_trip_stops mts
830 WHERE mdl.plan_id = md.plan_id
831 AND mdl.plan_id = mts.plan_id
832 AND mdl.pick_up_stop_id = mts.stop_id
833 AND EXISTS
834 (SELECT 1
835 FROM wsh_location_owners wlo
836 WHERE wlo.owner_party_id = p_carrier_id
837 AND wlo.owner_type = CARRIER
838 AND wlo.wsh_location_id = mts.stop_location_id) );
839 l_carrier_volume_tmp := NVL(l_carrier_volume,0);
840
841 l_carrier_volume := 0;
842
843 SELECT SUM(md.volume)
844 INTO l_carrier_volume
845 FROM mst_deliveries md
846 WHERE md.plan_id = p_plan_id
847 AND md.delivery_id IN
848 ( SELECT mdl.delivery_id
849 FROM mst_delivery_legs mdl,
850 mst_trip_stops mts
851 WHERE mdl.plan_id = md.plan_id
852 AND mdl.plan_id = mts.plan_id
853 AND mdl.drop_off_stop_id = mts.stop_id
854 AND EXISTS
855 (SELECT 1
856 FROM wsh_location_owners wlo
857 WHERE wlo.owner_party_id = p_carrier_id
858 AND wlo.owner_type = CARRIER
859 AND wlo.wsh_location_id = mts.stop_location_id) );
860
861 l_carrier_volume := l_carrier_volume_tmp + NVL(l_carrier_volume,0);
862
863 RETURN l_carrier_volume;
864 EXCEPTION
865 WHEN OTHERS THEN
866 RETURN 0;
867 END get_carrier_volume;
868
869 function get_carrier_pallets(p_plan_id in number,p_carrier_id in number)
870 return number is
871 l_carrier_pallets number;
872 l_carrier_pallets_tmp number;
873 begin
874 -- ----------------------------------
875 -- Re-writing the sql for bug#3335462
876 -- ----------------------------------
877 /******************************
878 SELECT SUM(md.number_of_pallets)
879 INTO l_carrier_pallets
880 FROM MST_DELIVERIES md
881 WHERE md.plan_id = p_plan_id
882 AND md.delivery_id IN
883 ( SELECT mdl.delivery_id
884 FROM MST_DELIVERY_LEGS mdl,
885 MST_TRIPS mt,
886 MST_TRIP_STOPS mts
887 WHERE mt.plan_id = md.plan_id
888 AND mt.carrier_id = p_carrier_id
889 AND mt.plan_id = mdl.plan_id
890 AND mt.trip_id = mdl.trip_id
891 AND mt.plan_id = mts.plan_id
892 AND mt.trip_id = mts.trip_id
893 AND mts.stop_location_id in
894 (SELECT wlo.wsh_location_id
895 FROM WSH_LOCATION_OWNERS wlo
896 WHERE wlo.owner_party_id = mt.carrier_id) );
897 **************************/
898 -- ----------------------------------------------------------------------------
902 -- for unload and load.
899 -- As per bug#3546059 and 3546163, We need to consider all the trips that touch
900 -- the facility owned by a specific carrier.
901 -- Also, we need to double count KPIs - weight/Cube/pallets/Pieces/Orders
903 -- ----------------------------------------------------------------------------
904 SELECT SUM(md.number_of_pallets)
905 INTO l_carrier_pallets
906 FROM mst_deliveries md
907 WHERE md.plan_id = p_plan_id
908 AND md.delivery_id IN
909 ( SELECT mdl.delivery_id
910 FROM mst_delivery_legs mdl,
911 mst_trip_stops mts
912 WHERE mdl.plan_id = md.plan_id
913 AND mdl.plan_id = mts.plan_id
914 AND mdl.pick_up_stop_id = mts.stop_id
915 AND EXISTS
916 (SELECT 1
917 FROM wsh_location_owners wlo
918 WHERE wlo.owner_party_id = p_carrier_id
919 AND wlo.owner_type = CARRIER
920 AND wlo.wsh_location_id = mts.stop_location_id) );
921 l_carrier_pallets_tmp := NVL(l_carrier_pallets,0);
922
923 l_carrier_pallets := 0;
924
925 SELECT SUM(md.number_of_pallets)
926 INTO l_carrier_pallets
927 FROM mst_deliveries md
928 WHERE md.plan_id = p_plan_id
929 AND md.delivery_id IN
930 ( SELECT mdl.delivery_id
931 FROM mst_delivery_legs mdl,
932 mst_trip_stops mts
933 WHERE mdl.plan_id = md.plan_id
934 AND mdl.plan_id = mts.plan_id
935 AND mdl.drop_off_stop_id = mts.stop_id
936 AND EXISTS
937 (SELECT 1
938 FROM wsh_location_owners wlo
939 WHERE wlo.owner_party_id = p_carrier_id
940 AND wlo.owner_type = CARRIER
941 AND wlo.wsh_location_id = mts.stop_location_id) );
942
943 l_carrier_pallets := l_carrier_pallets_tmp + NVL(l_carrier_pallets,0);
944 RETURN l_carrier_pallets;
945 EXCEPTION
946 WHEN OTHERS THEN
947 RETURN 0;
948 END get_carrier_pallets;
949
950 function get_carrier_pieces(p_plan_id in number,p_carrier_id in number)
951 return number is
952 l_carrier_pieces number;
953 l_carrier_pieces_tmp number;
954 begin
955 -- ----------------------------------
956 -- Re-writing the sql for bug#3335462
957 -- ----------------------------------
958 /******************************
959 SELECT SUM(md.number_of_pieces)
960 INTO l_carrier_pieces
961 FROM MST_DELIVERIES md
962 WHERE md.plan_id = p_plan_id
963 AND md.delivery_id IN
964 ( SELECT mdl.delivery_id
965 FROM MST_DELIVERY_LEGS mdl,
966 MST_TRIPS mt,
967 MST_TRIP_STOPS mts
968 WHERE mt.plan_id = md.plan_id
969 AND mt.carrier_id = p_carrier_id
970 AND mt.plan_id = mdl.plan_id
971 AND mt.trip_id = mdl.trip_id
972 AND mt.plan_id = mts.plan_id
973 AND mt.trip_id = mts.trip_id
974 AND mts.stop_location_id in
975 (SELECT wlo.wsh_location_id
976 FROM WSH_LOCATION_OWNERS wlo
977 WHERE wlo.owner_party_id = mt.carrier_id) );
978 **************************/
979 -- ----------------------------------------------------------------------------
980 -- As per bug#3546059 and 3546163, We need to consider all the trips that touch
981 -- the facility owned by a specific carrier.
982 -- Also, we need to double count KPIs - weight/Cube/pallets/Pieces/Orders
983 -- for unload and load.
984 -- ----------------------------------------------------------------------------
985 SELECT SUM(md.number_of_pieces)
986 INTO l_carrier_pieces
987 FROM mst_deliveries md
988 WHERE md.plan_id = p_plan_id
989 AND md.delivery_id IN
990 ( SELECT mdl.delivery_id
991 FROM mst_delivery_legs mdl,
992 mst_trip_stops mts
993 WHERE mdl.plan_id = md.plan_id
994 AND mdl.plan_id = mts.plan_id
995 AND mdl.pick_up_stop_id = mts.stop_id
996 AND EXISTS
997 (SELECT 1
998 FROM wsh_location_owners wlo
999 WHERE wlo.owner_party_id = p_carrier_id
1000 AND wlo.owner_type = CARRIER
1001 AND wlo.wsh_location_id = mts.stop_location_id) );
1002 l_carrier_pieces_tmp := NVL(l_carrier_pieces,0);
1003
1004 l_carrier_pieces := 0;
1005
1006 SELECT SUM(md.number_of_pieces)
1007 INTO l_carrier_pieces
1008 FROM mst_deliveries md
1009 WHERE md.plan_id = p_plan_id
1010 AND md.delivery_id IN
1011 ( SELECT mdl.delivery_id
1012 FROM mst_delivery_legs mdl,
1013 mst_trip_stops mts
1014 WHERE mdl.plan_id = md.plan_id
1015 AND mdl.plan_id = mts.plan_id
1016 AND mdl.drop_off_stop_id = mts.stop_id
1017 AND EXISTS
1018 (SELECT 1
1019 FROM wsh_location_owners wlo
1020 WHERE wlo.owner_party_id = p_carrier_id
1021 AND wlo.owner_type = CARRIER
1022 AND wlo.wsh_location_id = mts.stop_location_id) );
1023
1024 l_carrier_pieces := l_carrier_pieces_tmp + NVL(l_carrier_pieces,0);
1025
1029 RETURN 0;
1026 RETURN l_carrier_pieces;
1027 EXCEPTION
1028 WHEN OTHERS THEN
1030 END get_carrier_pieces;
1031
1032 -- get carrier orders
1033 function get_carrier_orders(p_plan_id in number,p_carrier_id in number)
1034 return number is
1035 l_carrier_orders number;
1036 l_carrier_orders_tmp NUMBER;
1037 begin
1038 -- ------------------------------------------ -
1039 -- As per bug#3244044, we need to consider -
1040 -- Distinct orders instead of raw orders. -
1041 -- ------------------------------------------ -
1042 -- ----------------------------------
1043 -- Modifying the sql for bug#3335462
1044 -- ----------------------------------
1045 /*********************************************
1046 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1047 INTO l_carrier_orders
1048 FROM MST_DELIVERY_DETAILS MDD,
1049 MST_DELIVERIES MD,
1050 MST_DELIVERY_ASSIGNMENTS MDA
1051 WHERE MD.PLAN_ID = MDA.PLAN_ID
1052 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1053 AND MD.DELIVERY_ID IN
1054 (SELECT MDL.DELIVERY_ID
1055 FROM MST_TRIPS T,
1056 MST_TRIP_STOPS TS,
1057 MST_DELIVERY_LEGS MDL
1058 WHERE MDL.PLAN_ID = MD.PLAN_ID
1059 AND TS.PLAN_ID = MDL.PLAN_ID
1060 AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
1061 OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
1062 AND TS.PLAN_ID = T.PLAN_ID
1063 AND TS.TRIP_ID = T.TRIP_ID
1064 AND T.CARRIER_ID = P_CARRIER_ID
1065 AND ts.stop_location_id in
1066 (SELECT wlo.wsh_location_id
1067 FROM WSH_LOCATION_OWNERS wlo
1068 WHERE wlo.owner_party_id = t.carrier_id))
1069 AND MDA.PLAN_ID = MDD.PLAN_ID
1070 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1071 AND MD.PLAN_ID = P_PLAN_ID;
1072 ************************************/
1073 -- ----------------------------------------------------------------------------
1074 -- As per bug#3546059 and 3546163, We need to consider all the trips that touch
1075 -- the facility owned by a specific carrier.
1076 -- Also, we need to double count KPIs - weight/Cube/pallets/Pieces/Orders
1077 -- for unload and load.
1078 -- ----------------------------------------------------------------------------
1079 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1080 INTO l_carrier_orders
1081 FROM mst_delivery_details mdd,
1082 mst_deliveries md,
1083 mst_delivery_assignments mda
1084 WHERE md.plan_id = p_plan_id
1085 and md.plan_id = mda.plan_id
1086 and md.delivery_id = mda.delivery_id
1087 and mda.plan_id = mdd.plan_id
1088 and mda.delivery_detail_id = mdd.delivery_detail_id
1089 AND md.delivery_id IN
1090 ( SELECT mdl.delivery_id
1091 FROM mst_delivery_legs mdl,
1092 mst_trip_stops mts
1093 WHERE mdl.plan_id = md.plan_id
1094 AND mdl.plan_id = mts.plan_id
1095 AND mdl.pick_up_stop_id = mts.stop_id
1096 AND EXISTS
1097 (SELECT 1
1098 FROM wsh_location_owners wlo
1099 WHERE wlo.owner_party_id = p_carrier_id
1100 AND wlo.owner_type = CARRIER
1101 AND wlo.wsh_location_id = mts.stop_location_id) );
1102 l_carrier_orders_tmp := NVL(l_carrier_orders,0);
1103
1104 l_carrier_orders := 0;
1105
1106 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1107 INTO l_carrier_orders
1108 FROM mst_delivery_details mdd,
1109 mst_deliveries md,
1110 mst_delivery_assignments mda
1111 WHERE md.plan_id = p_plan_id
1112 and md.plan_id = mda.plan_id
1113 and md.delivery_id = mda.delivery_id
1114 and mda.plan_id = mdd.plan_id
1115 and mda.delivery_detail_id = mdd.delivery_detail_id
1116 AND md.delivery_id IN
1117 ( SELECT mdl.delivery_id
1118 FROM mst_delivery_legs mdl,
1119 mst_trip_stops mts
1120 WHERE mdl.plan_id = md.plan_id
1121 AND mdl.plan_id = mts.plan_id
1122 AND mdl.drop_off_stop_id = mts.stop_id
1123 AND EXISTS
1124 (SELECT 1
1125 FROM wsh_location_owners wlo
1126 WHERE wlo.owner_party_id = p_carrier_id
1127 AND wlo.owner_type = CARRIER
1128 AND wlo.wsh_location_id = mts.stop_location_id) );
1129
1130 l_carrier_orders := l_carrier_orders_tmp + NVL(l_carrier_orders,0);
1131
1132 RETURN l_carrier_orders;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 RETURN 0;
1136 END get_carrier_orders;
1137
1138 /* Service Details */
1139 -- Should we exclude cost for tls in CM for mode = TL?
1140 function get_carrier_service_total_cost(p_plan_id in number,
1144 l_carrier_tl_cost number;
1141 p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
1142 return number is
1143 l_carrier_service_cost number;
1145 l_carrier_ltl_cost number;
1146 l_carrier_parcel_cost number;
1147 begin
1148 if p_mode = 'TRUCK' then
1149 select sum(total_basic_transport_cost + total_stop_cost + total_load_unload_cost+ total_layover_cost+ total_accessorial_cost + total_handling_cost)
1150 into l_carrier_tl_cost
1151 from mst_trips
1152 where plan_id = p_plan_id
1153 and carrier_id = p_carrier_id
1154 and mode_of_transport = 'TRUCK'
1155 and service_level = p_service
1156 and continuous_move_id is null;
1157 l_carrier_service_cost := l_carrier_tl_cost;
1158 elsif p_mode = 'LTL' then
1159 select sum(total_basic_transport_cost + total_accessorial_cost)
1160 into l_carrier_ltl_cost
1161 from mst_trips
1162 where plan_id = p_plan_id
1163 and carrier_id = p_carrier_id
1164 and mode_of_transport = 'LTL'
1165 and service_level = p_service;
1166 l_carrier_service_cost := l_carrier_ltl_cost;
1167 elsif p_mode = 'PARCEL' then
1168 select sum(total_basic_transport_cost + total_accessorial_cost)
1169 into l_carrier_parcel_cost
1170 from mst_trips
1171 where plan_id = p_plan_id
1172 and carrier_id = p_carrier_id
1173 and mode_of_transport = 'LTL'
1174 and service_level = p_service;
1175 l_carrier_service_cost := l_carrier_parcel_cost;
1176 end if;
1177 return l_carrier_service_cost;
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 RETURN 0;
1181 end;
1182
1183 function get_carrier_service_weight(p_plan_id in number, p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
1184 return number is
1185 l_carrier_weight number;
1186 begin
1187 select sum(gross_weight)
1188 into l_carrier_weight
1189 from(
1190 select distinct md.delivery_id, md.gross_weight gross_weight
1191 from mst_delivery_legs mdl,
1192 mst_deliveries md,
1193 mst_trips mt,
1194 mst_trip_stops mts
1195 where mt.plan_id = p_plan_id
1196 and mt.mode_of_transport = p_mode
1197 and mt.service_level = p_service
1198 and mt.carrier_id = p_carrier_id
1199 and mt.trip_id = mts.trip_id
1200 and mts.stop_id = mdl.pick_up_stop_id
1201 and mdl.delivery_id = md.delivery_id
1202 );
1203
1204 return l_carrier_weight;
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 RETURN 0;
1208 end;
1209
1210 function get_carrier_service_volume(p_plan_id in number,p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
1211 return number is
1212 l_carrier_volume number;
1213 begin
1214 select sum(volume)
1215 into l_carrier_volume
1216 from(
1217 select distinct md.delivery_id, md.volume volume
1218 from mst_delivery_legs mdl,
1219 mst_deliveries md,
1220 mst_trips mt,
1221 mst_trip_stops mts
1222 where mt.plan_id = p_plan_id
1223 and mt.mode_of_transport = p_mode
1224 and mt.service_level = p_service
1225 and mt.carrier_id = p_carrier_id
1226 and mt.trip_id = mts.trip_id
1227 and mts.stop_id = mdl.pick_up_stop_id
1228 and mdl.delivery_id = md.delivery_id
1229 );
1230
1231 return l_carrier_volume;
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 RETURN 0;
1235 end;
1236 function get_carrier_service_pallets(p_plan_id in number,p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
1237 return number is
1238 l_carrier_pallets number;
1239 begin
1240 select sum(number_of_pallets)
1241 into l_carrier_pallets
1242 from(
1243 select distinct md.delivery_id, md.number_of_pallets number_of_pallets
1244 from mst_delivery_legs mdl,
1245 mst_deliveries md,
1246 mst_trips mt,
1247 mst_trip_stops mts
1248 where mt.plan_id = p_plan_id
1249 and mt.mode_of_transport = p_mode
1250 and mt.service_level = p_service
1251 and mt.carrier_id = p_carrier_id
1252 and mt.trip_id = mts.trip_id
1253 and mts.stop_id = mdl.pick_up_stop_id
1254 and mdl.delivery_id = md.delivery_id
1255 );
1256
1257 return l_carrier_pallets;
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 RETURN 0;
1261 end;
1262 function get_carrier_service_pieces(p_plan_id in number,p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
1263 return number is
1264 l_carrier_pieces number;
1265 begin
1266 select sum(number_of_pieces)
1267 into l_carrier_pieces
1268 from(
1269 select distinct md.delivery_id, md.number_of_pieces number_of_pieces
1270 from mst_delivery_legs mdl,
1271 mst_deliveries md,
1272 mst_trips mt,
1273 mst_trip_stops mts
1274 where mt.plan_id = p_plan_id
1275 and mt.mode_of_transport = p_mode
1276 and mt.service_level = p_service
1277 and mt.carrier_id = p_carrier_id
1278 and mt.trip_id = mts.trip_id
1279 and mts.stop_id = mdl.pick_up_stop_id
1280 and mdl.delivery_id = md.delivery_id
1281 );
1282
1283 return l_carrier_pieces;
1284 EXCEPTION
1285 WHEN OTHERS THEN
1286 RETURN 0;
1290 function get_total_cost_cust (p_plan_id in number,
1287 end;
1288
1289 /* Customer Details */
1291 p_customer_id in number)
1292 return number is
1293
1294 l_total_cost number;
1295
1296 begin
1297 -- SQL repository issues as on 25-05-04:
1298 -- Added join for plan id
1299 select sum(mdl.allocated_fac_loading_cost +
1300 mdl.allocated_fac_unloading_cost +
1301 mdl.ALLOCATED_FAC_SHP_HAND_COST +
1302 mdl.ALLOCATED_FAC_REC_HAND_COST +
1303 mdl.allocated_transport_cost )
1304 into l_total_cost
1305 from mst_deliveries md,
1306 mst_delivery_legs mdl
1307 where md.plan_id = p_plan_id
1308 and md.customer_id = p_customer_id
1309 AND md.plan_id = mdl.plan_id
1310 and md.delivery_id = mdl.delivery_id;
1311
1312 return l_total_cost;
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 RETURN 0;
1316 end;
1317
1318 function get_total_weight_cust (p_plan_id in number,
1319 p_customer_id in number)
1320 return number is
1321
1322 l_total_weight number;
1323
1324 BEGIN
1325 -- ----------------------------------------
1326 -- As per bug#3316114, we need to consider
1327 -- both Assigned and Unassigned deliveries.
1328 -- ----------------------------------------
1329 -- --------------------
1330 -- Bug#3423219
1331 -- Need to show net wt.
1332 -- --------------------
1333 --select sum(md.gross_weight)
1334 /* select sum(md.net_weight)
1335 into l_total_weight
1336 from mst_deliveries md
1337 where md.plan_id = p_plan_id
1338 and md.customer_id = p_customer_id;
1339 */
1340 --and exists (select 1 from mst_delivery_legs mdl
1341 -- where md.delivery_id = mdl.delivery_id
1342 -- );
1343
1344
1345 -- ----------------------------------------
1346 -- Changing as per bug#3548552
1347 -- ----------------------------------------
1348
1349 select sum(nvl(mdd.net_weight,0))
1350 into l_total_weight
1351 from mst_delivery_details mdd
1352 , mst_delivery_assignments mda
1353 , mst_deliveries md
1354 where md.plan_id = mda.plan_id
1355 and md.delivery_id = mda.delivery_id
1356 and mda.plan_id = mdd.plan_id
1357 and mda.delivery_detail_id = mdd.delivery_detail_id
1358 and mdd.container_flag = 2
1359 and md.plan_id = p_plan_id
1360 and md.customer_id = p_customer_id;
1361
1362 return l_total_weight;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 RETURN 0;
1366 end;
1367
1368
1369
1370 function get_total_cube_cust (p_plan_id in number,
1371 p_customer_id in number)
1372 return number is
1373
1374 l_total_volume number;
1375
1376 BEGIN
1377 -- ----------------------------------------
1378 -- As per bug#3316114, we need to consider
1379 -- both Assigned and Unassigned deliveries.
1380 -- ----------------------------------------
1381 /* select sum(md.volume)
1382 into l_total_volume
1383 from mst_deliveries md
1384 where md.plan_id = p_plan_id
1385 and md.customer_id = p_customer_id;
1386 */
1387 --and exists (select 1 from mst_delivery_legs mdl
1388 -- where md.delivery_id = mdl.delivery_id
1389 -- );
1390
1391
1392 -- ----------------------------------------
1393 -- Changing as per bug#3548552
1394 -- ----------------------------------------
1395
1396 select sum(nvl(mdd.volume,0))
1397 into l_total_volume
1398 from mst_delivery_details mdd
1399 , mst_delivery_assignments mda
1400 , mst_deliveries md
1401 where md.plan_id = mda.plan_id
1402 and md.delivery_id = mda.delivery_id
1403 and mda.plan_id = mdd.plan_id
1404 and mda.delivery_detail_id = mdd.delivery_detail_id
1405 and mdd.container_flag = 2
1406 and md.plan_id = p_plan_id
1407 and md.customer_id = p_customer_id;
1408
1409 return l_total_volume;
1410 EXCEPTION
1411 WHEN OTHERS THEN
1412 RETURN 0;
1413 end;
1414
1415 function get_total_pallets_cust (p_plan_id in number,
1416 p_customer_id in number)
1417 return number is
1418
1419 l_total_pallets number;
1420
1421 begin
1422 -- ----------------------------------------
1423 -- As per bug#3316114, we need to consider
1424 -- both Assigned and Unassigned deliveries.
1425 -- ----------------------------------------
1426 /* select sum(md.number_of_pallets)
1427 into l_total_pallets
1428 from mst_deliveries md
1429 where md.plan_id = p_plan_id
1430 and md.customer_id = p_customer_id;
1431 */
1432 --and exists (select 1 from mst_delivery_legs mdl
1433 -- where md.delivery_id = mdl.delivery_id
1434 -- );
1435
1436
1437 -- ----------------------------------------
1438 -- Changing as per bug#3548552
1439 -- ----------------------------------------
1440
1441 select sum(ceil(nvl(mdd.number_of_pallets,0)))
1445 , mst_deliveries md
1442 into l_total_pallets
1443 from mst_delivery_details mdd
1444 , mst_delivery_assignments mda
1446 where md.plan_id = mda.plan_id
1447 and md.delivery_id = mda.delivery_id
1448 and mda.plan_id = mdd.plan_id
1449 and mda.delivery_detail_id = mdd.delivery_detail_id
1450 and mdd.container_flag = 2
1451 and md.plan_id = p_plan_id
1452 and md.customer_id = p_customer_id;
1453
1454 return l_total_pallets;
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 RETURN 0;
1458 end;
1459
1460
1461
1462 function get_total_pieces_cust (p_plan_id in number,
1463 p_customer_id in number)
1464 return number is
1465
1466 l_total_pieces number;
1467
1468 begin
1469 -- ----------------------------------------
1470 -- As per bug#3316114, we need to consider
1471 -- both Assigned and Unassigned deliveries.
1472 -- ----------------------------------------
1473 /* select sum(md.number_of_pieces)
1474 into l_total_pieces
1475 from mst_deliveries md
1476 where md.plan_id = p_plan_id
1477 and md.customer_id = p_customer_id;
1478 */
1479 --and exists (select 1 from mst_delivery_legs mdl
1480 -- where md.delivery_id = mdl.delivery_id
1481 -- );
1482
1483
1484 -- ----------------------------------------
1485 -- Changing as per bug#3548552
1486 -- ----------------------------------------
1487
1488 select sum(nvl(mdd.requested_quantity,0))
1489 into l_total_pieces
1490 from mst_delivery_details mdd
1491 , mst_delivery_assignments mda
1492 , mst_deliveries md
1493 where md.plan_id = mda.plan_id
1494 and md.delivery_id = mda.delivery_id
1495 and mda.plan_id = mdd.plan_id
1496 and mda.delivery_detail_id = mdd.delivery_detail_id
1497 and mdd.container_flag = 2
1498 and md.plan_id = p_plan_id
1499 and md.customer_id = p_customer_id;
1500
1501 return l_total_pieces;
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 RETURN 0;
1505 end;
1506
1507
1508
1509 function get_total_orders_cust (p_plan_id in number,
1510 p_customer_id in number)
1511 return number is
1512
1513 l_total_orders number;
1514
1515 BEGIN
1516 -- ----------------------------------------
1517 -- As per bug#3283569, we need to consider
1518 -- raw orders instead of TP order lines.
1519 -- ----------------------------------------
1520 /***
1521
1522 select count(1)
1523 into l_total_orders
1524 from mst_delivery_details mdd,
1525 mst_delivery_assignments mda
1526 where mdd.plan_id = p_plan_id
1527 and mdd.customer_id = p_customer_id
1528 and mdd.split_from_delivery_detail_id is null
1529 and mdd.delivery_detail_id = mda.delivery_detail_id
1530 and mda.parent_delivery_detail_id is null
1531 and exists (select 1
1532 from mst_delivery_legs mdl
1533 where mdl.delivery_id = mda.delivery_id
1534 );
1535 ***/
1536 /*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1537 INTO l_total_orders
1538 FROM MST_DELIVERY_DETAILS MDD,
1539 MST_DELIVERIES MD,
1540 MST_DELIVERY_ASSIGNMENTS MDA
1541 WHERE MD.PLAN_ID = MDA.PLAN_ID
1542 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1543 AND MD.DELIVERY_ID IN
1544 (SELECT MDL.DELIVERY_ID
1545 FROM MST_DELIVERY_LEGS MDL
1546 WHERE MDL.PLAN_ID = MD.PLAN_ID)
1547 AND MDA.PLAN_ID = MDD.PLAN_ID
1548 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1549 AND MD.PLAN_ID = P_PLAN_ID
1550 AND MD.CUSTOMER_ID = p_customer_id;*/
1551 -- ----------------------------------------
1552 -- As per bug#3316114, we need to consider
1553 -- both Assigned and Unassigned deliveries.
1554 -- ----------------------------------------
1555 SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
1556 INTO l_total_orders
1557 from (
1558 SELECT mdd.SOURCE_HEADER_NUMBER
1559 FROM MST_DELIVERY_DETAILS MDD,
1560 MST_DELIVERIES MD,
1561 MST_DELIVERY_ASSIGNMENTS MDA
1562 WHERE MD.PLAN_ID = MDA.PLAN_ID
1563 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1564 AND MD.DELIVERY_ID IN
1565 (SELECT MDL.DELIVERY_ID
1566 FROM MST_DELIVERY_LEGS MDL
1567 WHERE MDL.PLAN_ID = MD.PLAN_ID)
1568 AND MDA.PLAN_ID = MDD.PLAN_ID
1569 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1570 AND MD.PLAN_ID = p_PLAN_ID
1571 AND MD.CUSTOMER_ID = p_customer_id
1572 union all
1573 SELECT mdd.SOURCE_HEADER_NUMBER
1574 FROM MST_DELIVERY_DETAILS MDD,
1575 MST_DELIVERIES MD,
1576 MST_DELIVERY_ASSIGNMENTS MDA
1577 WHERE MD.PLAN_ID = MDA.PLAN_ID
1578 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1579 AND not exists
1580 (SELECT 1
1581 FROM MST_DELIVERY_LEGS MDL
1582 WHERE MDL.PLAN_ID = MD.PLAN_ID
1583 and MDL.DELIVERY_ID = md.plan_id)
1584 AND MDA.PLAN_ID = MDD.PLAN_ID
1585 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1586 AND MD.PLAN_ID = p_PLAN_ID
1587 AND MD.CUSTOMER_ID = p_customer_id) dd;
1588 return l_total_orders;
1592 end;
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 RETURN 0;
1593
1594
1595 /* function get_total_trip_count_cust (p_plan_id in number,
1596 p_customer_id in number )
1597
1598 return varchar2 is
1599 cursor mst_d is
1600 select delivery_id
1601 from mst_deliveries md
1602 where md.plan_id = p_plan_id
1603 and md.customer_id = p_customer_id;
1604
1605 cursor mst_d_l_count (p_delivery_id NUMBER) is
1606 select count(mdl.delivery_leg_id)
1607 from mst_delivery_legs mdl
1608 where mdl.delivery_id = p_delivery_id
1609 and mdl.plan_id = p_plan_id;
1610
1611 cursor mst_d_l (p_delivery_id NUMBER) is
1612 select mdl.delivery_leg_id
1613 from mst_delivery_legs mdl
1614 where mdl.delivery_id = p_delivery_id
1615 and mdl.plan_id = p_plan_id;
1616
1617 cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1618 select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
1619 from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
1620 where mdl.delivery_leg_id = p_delivery_leg_id
1621 and mdl.plan_id = mts.plan_id
1622 and mdl.pick_up_stop_id = mts.stop_id
1623 and mdl.plan_id = mt.plan_id
1624 and mts.trip_id = mt.trip_id;
1625
1626 cursor mst_pickup_stop_location (p_delivery_leg_id NUMBER) is
1627 select mts.stop_location_id location_id
1628 from mst_delivery_legs mdl, mst_trip_stops mts
1629 where delivery_leg_id = p_delivery_leg_id
1630 and mdl.plan_id = mts.plan_id
1631 and mdl.pick_up_stop_id = mts.stop_id ;
1632
1633 cursor mst_dropoff_stop_location (p_delivery_leg_id NUMBER) is
1634 select mts.stop_location_id location_id
1635 from mst_delivery_legs mdl, mst_trip_stops mts
1636 where delivery_leg_id = p_delivery_leg_id
1637 and mdl.plan_id = mts.plan_id
1638 and mdl.drop_off_stop_id = mts.stop_id ;
1639
1640 l_num_of_direct_tls number := 0;
1641 l_num_of_multi_stop_tls number := 0;
1642 l_num_of_ltls number := 0;
1643 l_num_of_parcels number := 0;
1644
1645 l_delivery_leg_count number;
1646 l_delivery_leg_id number;
1647 l_mode_of_transport varchar2(50);
1648 l_trip_id number;
1649 l_first_leg_id number;
1650 l_last_leg_id number;
1651 l_pickup_location_id number;
1652 l_dropoff_location_id number;
1653 l_source_code VARCHAR2(10);
1654 i number;
1655 l_num_string varchar2(100);
1656 l_delivery_id number;
1657 l_num_of_stops_for_tl number;
1658 l_customer_facilities NumList := NumList();
1659 begin
1660 for c_mst_d in mst_d loop
1661 l_source_code := null;
1662 l_delivery_id := c_mst_d.delivery_id;
1663 open mst_d_l_count(l_delivery_id);
1664 fetch mst_d_l_count into l_delivery_leg_count;
1665 close mst_d_l_count;
1666 -- if l_delivery_leg_count = 0, this is a unassigned delivery, no trip
1667 if l_delivery_leg_count = 1 then
1668 open mst_d_l(l_delivery_id);
1669 fetch mst_d_l into l_delivery_leg_id;
1670 close mst_d_l;
1671 open mst_mode_of_transport(l_delivery_leg_id);
1672 fetch mst_mode_of_transport into l_mode_of_transport, l_trip_id;
1673 close mst_mode_of_transport;
1674 if l_mode_of_transport = 'LTL' then
1675 l_num_of_ltls := l_num_of_ltls + 1;
1676 elsif l_mode_of_transport = 'PARCEL' then
1677 l_num_of_parcels := l_num_of_parcels + 1;
1678 elsif l_mode_of_transport = 'TRUCK' then
1679 l_num_of_stops_for_tl := get_num_of_stops_for_tl(l_trip_id, p_plan_id);
1680 if l_num_of_stops_for_tl = 2 then
1681 l_num_of_direct_tls := l_num_of_direct_tls + 1;
1682 else
1683 l_num_of_multi_stop_tls := l_num_of_multi_stop_tls + 1;
1684 end if;
1685 end if;
1686 elsif l_delivery_leg_count > 1 then
1687 l_first_leg_id := get_first_or_last_delivery_leg (p_plan_id, c_mst_d.delivery_id, 0);
1688 l_last_leg_id := get_first_or_last_delivery_leg (p_plan_id, c_mst_d.delivery_id, 1);
1689
1690 l_customer_facilities := get_customer_facilities(p_plan_id, p_customer_id );
1691 open mst_dropoff_stop_location(l_last_leg_id);
1692 fetch mst_dropoff_stop_location into l_dropoff_location_id;
1693 close mst_dropoff_stop_location;
1694 for i in 1..g_num_of_facilities loop
1695 if l_customer_facilities(i) = l_dropoff_location_id then
1696 l_source_code := OE;
1697 open mst_mode_of_transport(l_last_leg_id) ;
1698 fetch mst_mode_of_transport into l_mode_of_transport, l_trip_id;
1699 close mst_mode_of_transport;
1700 if l_mode_of_transport = 'LTL' then
1701 l_num_of_ltls := l_num_of_ltls + 1;
1702 elsif l_mode_of_transport = 'PARCEL' then
1703 l_num_of_parcels := l_num_of_parcels + 1;
1704 elsif l_mode_of_transport = 'TRUCK' then
1705 l_num_of_stops_for_tl := get_num_of_stops_for_tl(l_trip_id, p_plan_id);
1706 if l_num_of_stops_for_tl = 2 then
1707 l_num_of_direct_tls := l_num_of_direct_tls + 1;
1708 else
1709 l_num_of_multi_stop_tls := l_num_of_multi_stop_tls + 1;
1710 end if;
1711 end if;
1712 end if;
1713 end loop;
1714
1715 if l_source_code = null then -- if not RFC then must be OE
1719 close mst_mode_of_transport;
1716 --fetch mst_pickup_stop_location(l_last_leg_id) into l_pickup_location_id;
1717 open mst_mode_of_transport(l_first_leg_id);
1718 fetch mst_mode_of_transport into l_mode_of_transport, l_trip_id;
1720 if l_mode_of_transport = 'LTL' then
1721 l_num_of_ltls := l_num_of_ltls + 1;
1722 elsif l_mode_of_transport = 'PARCEL' then
1723 l_num_of_parcels := l_num_of_parcels + 1;
1724 elsif l_mode_of_transport = 'TRUCK' then
1725 l_num_of_stops_for_tl := get_num_of_stops_for_tl(l_trip_id, p_plan_id);
1726 if l_num_of_stops_for_tl = 2 then
1727 l_num_of_direct_tls := l_num_of_direct_tls + 1;
1728 else
1729 l_num_of_multi_stop_tls := l_num_of_multi_stop_tls + 1;
1730 end if;
1731 end if;
1732 end if;
1733 end if;
1734 end loop;
1735
1736 l_num_string := l_num_of_direct_tls || p_delim ||l_num_of_multi_stop_tls ||
1737 p_delim||l_num_of_ltls||p_delim||l_num_of_parcels;
1738
1739 return l_num_string;
1740 end;
1741 */
1742
1743 -- this function should be used to get the trip counts for both Customer and Suppliers
1744 function get_total_trip_count_partner(p_plan_id in number,
1745 p_partner_id in number, p_partner_type in number )
1746
1747 return varchar2 is
1748
1749 -- p_partner_type: 0 -- customer, 1 -- supplier
1750 cursor mst_d is
1751 select delivery_id
1752 from mst_deliveries md
1753 where md.plan_id = p_plan_id
1754 and decode(p_partner_type, 0, md.customer_id, md.supplier_id) = p_partner_id;
1755
1756 cursor mst_d_l_count (p_delivery_id NUMBER) is
1757 select count(mdl.delivery_leg_id)
1758 from mst_delivery_legs mdl
1759 where mdl.delivery_id = p_delivery_id
1760 and mdl.plan_id = p_plan_id;
1761
1762 cursor mst_d_l (p_delivery_id NUMBER) is
1763 select mdl.delivery_leg_id
1764 from mst_delivery_legs mdl
1765 where mdl.delivery_id = p_delivery_id
1766 and mdl.plan_id = p_plan_id;
1767
1768 cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1769 select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
1770 from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
1771 where mdl.delivery_leg_id = p_delivery_leg_id
1772 and mdl.plan_id = mts.plan_id
1773 and mdl.pick_up_stop_id = mts.stop_id
1774 and mdl.plan_id = mt.plan_id
1775 and mts.trip_id = mt.trip_id;
1776
1777 cursor mst_pickup_stop_location (p_delivery_leg_id NUMBER) is
1778 select mts.stop_location_id location_id
1779 from mst_delivery_legs mdl, mst_trip_stops mts
1780 where delivery_leg_id = p_delivery_leg_id
1781 and mdl.plan_id = mts.plan_id
1782 and mdl.pick_up_stop_id = mts.stop_id ;
1783
1784 cursor mst_dropoff_stop_location (p_delivery_leg_id NUMBER) is
1785 select mts.stop_location_id location_id
1786 from mst_delivery_legs mdl, mst_trip_stops mts
1787 where delivery_leg_id = p_delivery_leg_id
1788 and mdl.plan_id = mts.plan_id
1789 and mdl.drop_off_stop_id = mts.stop_id ;
1790
1791 l_num_of_direct_tls number := 0;
1792 l_num_of_multi_stop_tls number := 0;
1793 l_num_of_ltls number := 0;
1794 l_num_of_parcels number := 0;
1795
1796 l_delivery_leg_count number;
1797 l_delivery_leg_id number;
1798 l_mode_of_transport varchar2(50);
1799 l_trip_id number;
1800 l_first_leg_id number;
1801 l_last_leg_id number;
1802 l_pickup_location_id number;
1803 l_dropoff_location_id number;
1804 l_source_code VARCHAR2(10);
1805 i number;
1806 l_num_string varchar2(100);
1807 l_delivery_id number;
1808 l_num_of_stops_for_tl number;
1809 l_partner_facilities NumList := NumList();
1810 begin
1811 /*
1812 for c_mst_d in mst_d loop
1813 l_source_code := null;
1814 l_delivery_id := c_mst_d.delivery_id;
1815 open mst_d_l_count(l_delivery_id);
1816 fetch mst_d_l_count into l_delivery_leg_count;
1817 close mst_d_l_count;
1818 -- if l_delivery_leg_count = 0, this is a unassigned delivery, no trip
1819 if l_delivery_leg_count = 1 then
1820 open mst_d_l(l_delivery_id);
1821 fetch mst_d_l into l_delivery_leg_id;
1822 close mst_d_l;
1823 open mst_mode_of_transport(l_delivery_leg_id);
1824 fetch mst_mode_of_transport into l_mode_of_transport, l_trip_id;
1825 close mst_mode_of_transport;
1826 if l_mode_of_transport = 'LTL' then
1827 l_num_of_ltls := l_num_of_ltls + 1;
1828 elsif l_mode_of_transport = 'PARCEL' then
1829 l_num_of_parcels := l_num_of_parcels + 1;
1830 elsif l_mode_of_transport = 'TRUCK' THEN
1831 -- -----------------------------------------------
1832 -- skakani - 01.12.2003 - Changing for bug#3283545
1833 -- -----------------------------------------------
1834 --l_num_of_stops_for_tl := get_num_of_stops_for_tl(l_trip_id, p_plan_id);
1835 l_num_of_stops_for_tl := get_num_of_stops_for_tl(p_plan_id, l_trip_id);
1836 if l_num_of_stops_for_tl = 2 then
1837 l_num_of_direct_tls := l_num_of_direct_tls + 1;
1838 else
1839 l_num_of_multi_stop_tls := l_num_of_multi_stop_tls + 1;
1840 end if;
1841 end if;
1845 if p_partner_type = 0 then
1842 elsif l_delivery_leg_count > 1 then
1843 l_first_leg_id := get_first_or_last_delivery_leg (p_plan_id, c_mst_d.delivery_id, 0);
1844 l_last_leg_id := get_first_or_last_delivery_leg (p_plan_id, c_mst_d.delivery_id, 1);
1846 l_partner_facilities := get_customer_facilities(p_plan_id, p_partner_id );
1847 else
1848 l_partner_facilities := get_supplier_facilities(p_plan_id, p_partner_id );
1849 end if;
1850 if l_partner_facilities.FIRST is not null then
1851 open mst_dropoff_stop_location(l_last_leg_id);
1852 fetch mst_dropoff_stop_location into l_dropoff_location_id;
1853 close mst_dropoff_stop_location;
1854 for i in l_partner_facilities.FIRST..l_partner_facilities.LAST loop
1855 if l_partner_facilities(i) = l_dropoff_location_id then
1856 if p_partner_type = 0 then
1857 l_source_code := OE;
1858 else
1859 l_source_code := RTV; -- RTS;
1860 end if;
1861 open mst_mode_of_transport(l_last_leg_id) ;
1862 fetch mst_mode_of_transport into l_mode_of_transport, l_trip_id;
1863 close mst_mode_of_transport;
1864 if l_mode_of_transport = 'LTL' then
1865 l_num_of_ltls := l_num_of_ltls + 1;
1866 elsif l_mode_of_transport = 'PARCEL' then
1867 l_num_of_parcels := l_num_of_parcels + 1;
1868 elsif l_mode_of_transport = 'TRUCK' THEN
1869 -- -----------------------------------------------
1870 -- skakani - 01.12.2003 - Changing for bug#3283545
1871 -- -----------------------------------------------
1872 --l_num_of_stops_for_tl := get_num_of_stops_for_tl(l_trip_id, p_plan_id);
1873 l_num_of_stops_for_tl := get_num_of_stops_for_tl(p_plan_id, l_trip_id);
1874 if l_num_of_stops_for_tl = 2 then
1875 l_num_of_direct_tls := l_num_of_direct_tls + 1;
1876 else
1877 l_num_of_multi_stop_tls := l_num_of_multi_stop_tls + 1;
1878 end if;
1879 end if;
1880 end if;
1881 end loop;
1882
1883 if l_source_code = null then -- if not OE/RTV then must be RFC/PO
1884 --fetch mst_pickup_stop_location(l_last_leg_id) into l_pickup_location_id;
1885 open mst_mode_of_transport(l_first_leg_id);
1886 fetch mst_mode_of_transport into l_mode_of_transport, l_trip_id;
1887 close mst_mode_of_transport;
1888 if l_mode_of_transport = 'LTL' then
1889 l_num_of_ltls := l_num_of_ltls + 1;
1890 elsif l_mode_of_transport = 'PARCEL' then
1891 l_num_of_parcels := l_num_of_parcels + 1;
1892 elsif l_mode_of_transport = 'TRUCK' then
1893 -- -----------------------------------------------
1894 -- skakani - 01.12.2003 - Changing for bug#3283545
1895 -- -----------------------------------------------
1896 --l_num_of_stops_for_tl := get_num_of_stops_for_tl(l_trip_id, p_plan_id);
1897 l_num_of_stops_for_tl := get_num_of_stops_for_tl(p_plan_id, l_trip_id);
1898 if l_num_of_stops_for_tl = 2 then
1899 l_num_of_direct_tls := l_num_of_direct_tls + 1;
1900 else
1901 l_num_of_multi_stop_tls := l_num_of_multi_stop_tls + 1;
1905 else
1902 end if;
1903 end if;
1904 end if;
1906 --dbms_output.put_line('Error: Customer or supplier facilities are not defined!');
1907 null;
1908 end if;
1909 end if;
1910 end loop;
1911
1912 l_num_string := l_num_of_direct_tls || p_delim ||l_num_of_multi_stop_tls ||
1913 p_delim||l_num_of_ltls||p_delim||l_num_of_parcels;
1914
1915 return l_num_string;
1916
1917 */
1918
1919 if p_partner_type = 0 then -- partner_type = 0 means for customer
1920 --for DTLs
1921 select count(mt.trip_id)
1922 into l_num_of_direct_tls
1923 from mst_trips mt
1924 where mt.plan_id = p_plan_id
1925 and mt.trip_id in
1926 (select distinct mts.trip_id
1927 from mst_trip_stops mts
1928 , mst_delivery_legs mdl
1929 , mst_deliveries md
1930 where md.plan_id = mt.plan_id
1931 and md.customer_id = p_partner_id
1932 and mts.plan_id = md.plan_id
1933 and mts.stop_location_id = md.dropoff_location_id
1934 and mdl.plan_id = md.plan_id
1935 and mdl.delivery_id = md.delivery_id
1936 and mdl.trip_id = mts.trip_id
1937 and mdl.drop_off_stop_id = mts.stop_id)
1938 and mt.mode_of_transport = 'TRUCK'
1939 and EXISTS
1940 (select ts.trip_id
1941 from mst_trip_stops ts
1942 where ts.plan_id = mt.plan_id
1943 and ts.trip_id = mt.trip_id
1944 having count(ts.stop_id) =2
1945 group by ts.trip_id);
1946
1947 -- for MultiStop TLs
1948 select count(mt.trip_id)
1949 into l_num_of_multi_stop_tls
1950 from mst_trips mt
1951 where mt.plan_id = p_plan_id
1952 and mt.trip_id in
1953 (select distinct mts.trip_id
1954 from mst_trip_stops mts
1955 , mst_delivery_legs mdl
1956 , mst_deliveries md
1957 where md.plan_id = mt.plan_id
1958 and md.customer_id = p_partner_id
1959 and mts.plan_id = md.plan_id
1960 and mts.stop_location_id = md.dropoff_location_id
1961 and mdl.plan_id = md.plan_id
1962 and mdl.delivery_id = md.delivery_id
1963 and mdl.trip_id = mts.trip_id
1964 and mdl.drop_off_stop_id = mts.stop_id)
1965 and mt.mode_of_transport = 'TRUCK'
1966 and EXISTS
1967 (select ts.trip_id
1968 from mst_trip_stops ts
1969 where ts.plan_id = mt.plan_id
1970 and ts.trip_id = mt.trip_id
1971 having count(ts.stop_id) >2
1972 group by ts.trip_id);
1973
1974 --for LTLs
1975 select count(mt.trip_id)
1976 into l_num_of_ltls
1977 from mst_trips mt
1978 where mt.plan_id = p_plan_id
1979 and mt.trip_id in
1980 (select distinct mts.trip_id
1981 from mst_trip_stops mts
1982 , mst_delivery_legs mdl
1983 , mst_deliveries md
1984 where md.plan_id = mt.plan_id
1985 and md.customer_id = p_partner_id
1986 and mts.plan_id = md.plan_id
1987 and mts.stop_location_id = md.dropoff_location_id
1988 and mdl.plan_id = md.plan_id
1989 and mdl.delivery_id = md.delivery_id
1990 and mdl.trip_id = mts.trip_id
1991 and mdl.drop_off_stop_id = mts.stop_id)
1992 and mt.mode_of_transport = 'LTL';
1993
1994 --for Parcels
1995 select count(mt.trip_id)
1996 into l_num_of_parcels
1997 from mst_trips mt
1998 where mt.plan_id = p_plan_id
1999 and mt.trip_id in
2000 (select distinct mts.trip_id
2001 from mst_trip_stops mts
2005 and md.customer_id = p_partner_id
2002 , mst_delivery_legs mdl
2003 , mst_deliveries md
2004 where md.plan_id = mt.plan_id
2006 and mts.plan_id = md.plan_id
2010 and mdl.trip_id = mts.trip_id
2007 and mts.stop_location_id = md.dropoff_location_id
2008 and mdl.plan_id = md.plan_id
2009 and mdl.delivery_id = md.delivery_id
2011 and mdl.drop_off_stop_id = mts.stop_id)
2012 and mt.mode_of_transport = 'PARCEL';
2013
2014 elsif p_partner_type = 1 then -- partner_type = 1 means for supplier
2015 --for DTLs
2016 select count(mt.trip_id)
2017 into l_num_of_direct_tls
2018 from mst_trips mt
2019 where mt.plan_id = p_plan_id
2020 and mt.trip_id in
2021 (select distinct mts.trip_id
2022 from mst_trip_stops mts
2023 , mst_delivery_legs mdl
2024 , mst_deliveries md
2025 where md.plan_id = mt.plan_id
2026 and md.supplier_id = p_partner_id
2027 and mts.plan_id = md.plan_id
2028 and mts.stop_location_id = md.pickup_location_id
2029 and mdl.plan_id = md.plan_id
2033 and mt.mode_of_transport = 'TRUCK'
2030 and mdl.delivery_id = md.delivery_id
2031 and mdl.trip_id = mts.trip_id
2032 and mdl.pick_up_stop_id = mts.stop_id)
2034 and EXISTS
2035 (select ts.trip_id
2036 from mst_trip_stops ts
2037 where ts.plan_id = mt.plan_id
2038 and ts.trip_id = mt.trip_id
2039 having count(ts.stop_id) =2
2040 group by ts.trip_id);
2041
2042 -- for MultiStop TLs
2043 select count(mt.trip_id)
2044 into l_num_of_multi_stop_tls
2045 from mst_trips mt
2046 where mt.plan_id = p_plan_id
2047 and mt.trip_id in
2048 (select distinct mts.trip_id
2049 from mst_trip_stops mts
2050 , mst_delivery_legs mdl
2051 , mst_deliveries md
2052 where md.plan_id = mt.plan_id
2053 and md.supplier_id = p_partner_id
2054 and mts.plan_id = md.plan_id
2055 and mts.stop_location_id = md.pickup_location_id
2056 and mdl.plan_id = md.plan_id
2057 and mdl.delivery_id = md.delivery_id
2058 and mdl.trip_id = mts.trip_id
2059 and mdl.pick_up_stop_id = mts.stop_id)
2060 and mt.mode_of_transport = 'TRUCK'
2061 and EXISTS
2062 (select ts.trip_id
2063 from mst_trip_stops ts
2064 where ts.plan_id = mt.plan_id
2065 and ts.trip_id = mt.trip_id
2066 having count(ts.stop_id) >2
2067 group by ts.trip_id);
2068
2069 --for LTLs
2070 select count(mt.trip_id)
2071 into l_num_of_ltls
2072 from mst_trips mt
2073 where mt.plan_id = p_plan_id
2074 and mt.trip_id in
2075 (select distinct mts.trip_id
2076 from mst_trip_stops mts
2077 , mst_delivery_legs mdl
2078 , mst_deliveries md
2079 where md.plan_id = mt.plan_id
2080 and md.supplier_id = p_partner_id
2081 and mts.plan_id = md.plan_id
2082 and mts.stop_location_id = md.pickup_location_id
2083 and mdl.plan_id = md.plan_id
2084 and mdl.delivery_id = md.delivery_id
2085 and mdl.trip_id = mts.trip_id
2086 and mdl.pick_up_stop_id = mts.stop_id)
2087 and mt.mode_of_transport = 'LTL';
2088
2089 --for Parcels
2090 select count(mt.trip_id)
2091 into l_num_of_parcels
2092 from mst_trips mt
2093 where mt.plan_id = p_plan_id
2094 and mt.trip_id in
2095 (select distinct mts.trip_id
2096 from mst_trip_stops mts
2097 , mst_delivery_legs mdl
2098 , mst_deliveries md
2099 where md.plan_id = mt.plan_id
2100 and md.supplier_id = p_partner_id
2101 and mts.plan_id = md.plan_id
2102 and mts.stop_location_id = md.pickup_location_id
2103 and mdl.plan_id = md.plan_id
2107 and mt.mode_of_transport = 'PARCEL';
2104 and mdl.delivery_id = md.delivery_id
2105 and mdl.trip_id = mts.trip_id
2106 and mdl.pick_up_stop_id = mts.stop_id)
2108
2109 else
2110 null;
2111 end if;
2112
2113 l_num_string := l_num_of_direct_tls || p_delim ||l_num_of_multi_stop_tls ||
2114 p_delim||l_num_of_ltls||p_delim||l_num_of_parcels;
2115
2116 return l_num_string;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 RETURN p_delim||p_delim||p_delim;
2121 end get_total_trip_count_partner;
2122
2123
2124
2125
2126 function get_customer_facilities(p_plan_id in number,
2127 p_customer_id in number)
2128 return NumList is
2129 l_c_facilities NumList := NumList();
2130 -- SQL repository issues as on 25-05-04:
2131 -- Rewritten sql to avoid distinct clause
2132 CURSOR customer_facilities IS
2133 SELECT fte.location_id customer_facility_id,
2134 fte.facility_code facility_code,
2135 fte.description description
2136 FROM fte_location_parameters fte
2137 WHERE fte.location_id IN
2138 (SELECT DECODE(mdd.source_code, OE , mdd.ship_to_location_id,
2139 RFC, mdd.ship_from_location_id) location_id
2140 FROM mst_delivery_details mdd
2141 WHERE mdd.plan_id = p_plan_id
2142 AND mdd.customer_id = p_customer_id
2143 AND mdd.source_code IN (OE, RFC) );
2144 /*cursor customer_facilities is
2145 select distinct decode(source_code, OE, ship_to_location_id,
2146 RFC, ship_from_location_id) customer_facility_id,
2147 fte.facility_code facility_code,
2148 fte.description description
2149 from mst_delivery_details, fte_location_parameters fte
2150 where plan_id = p_plan_id
2151 and customer_id = p_customer_id
2152 and source_code in (OE, RFC)
2153 and decode(source_code, RFC, ship_from_location_id, ship_to_location_id) = fte.location_id;*/
2154
2155 j number:=1;
2156 begin
2157 --g_num_of_facilities := 0;
2158 for c_customer_facility in customer_facilities loop
2159 l_c_facilities.EXTEND;
2160 l_c_facilities(j) := c_customer_facility.customer_facility_id;
2161 j := j + 1;
2162 --g_num_of_facilities := g_num_of_facilities + 1;
2163 end loop;
2164 return l_c_facilities;
2165 end;
2166
2167 function get_supplier_facilities(p_plan_id in number,
2168 p_supplier_id in number)
2169 return NumList is
2170 l_c_facilities NumList := NumList();
2171 -- SQL repository issues as on 25-05-04:
2172 -- Rewritten sql to avoid distinct clause
2173 CURSOR supplier_facilities is
2174 SELECT fte.location_id supplier_facility_id,
2175 fte.facility_code facility_code,
2176 fte.description description
2177 FROM fte_location_parameters fte
2178 WHERE fte.location_id in
2179 (SELECT DECODE(mdd.source_code, RTV, mdd.ship_to_location_id,
2180 PO, mdd.ship_from_location_id) location_id
2181 FROM mst_delivery_details mdd
2182 WHERE mdd.plan_id = p_plan_id
2183 AND mdd.supplier_id = p_supplier_id
2184 AND mdd.source_code in (PO, RTV));
2185 /*cursor supplier_facilities is
2186 select distinct decode(source_code, RTV, ship_to_location_id,
2187 PO, ship_from_location_id) supplier_facility_id,
2188 fte.facility_code facility_code,
2189 fte.description description
2190 from mst_delivery_details, fte_location_parameters fte
2191 where plan_id = p_plan_id
2192 and supplier_id = p_supplier_id
2193 and source_code in (PO, RTV)
2194 and decode(source_code, RTV, ship_to_location_id, PO, ship_from_location_id) = fte.location_id;*/
2195 j number:=1;
2196 begin
2197 --g_num_of_facilities := 0;
2198 for c_supplier_facility in supplier_facilities loop
2199 l_c_facilities.EXTEND;
2200 l_c_facilities(j) := c_supplier_facility.supplier_facility_id;
2201 j := j + 1;
2202 --g_num_of_facilities := g_num_of_facilities + 1;
2203 end loop;
2204 return l_c_facilities;
2205 end;
2206
2207 FUNCTION get_num_of_stops_for_tl(p_plan_id IN NUMBER, p_trip_id IN NUMBER)
2208 RETURN NUMBER IS
2209 CURSOR tl_stops is
2210 SELECT count(stop_id)
2211 FROM mst_trip_stops
2212 WHERE plan_id = p_plan_id
2213 AND trip_id = p_trip_id;
2214
2215 l_num_of_stops number;
2216 BEGIN
2217 OPEN tl_stops;
2218 FETCH tl_stops into l_num_of_stops;
2219 CLOSE tl_stops;
2220 RETURN l_num_of_stops;
2221 END;
2222
2223 FUNCTION get_first_or_last_delivery_leg(p_plan_id IN NUMBER, p_delivery_id IN NUMBER, p_type IN NUMBER)
2224 RETURN NUMBER IS
2225
2226 CURSOR Cur_First_Delivery_Leg IS
2227 SELECT DL.delivery_leg_id
2228 FROM mst_delivery_legs DL
2229 WHERE DL.plan_id = p_plan_id
2230 AND DL.delivery_id = p_delivery_id
2231 AND DL.sequence_number = (SELECT min(DL1.sequence_number) seq_no
2232 FROM mst_delivery_legs DL1
2233 WHERE DL1.plan_id = DL.plan_id
2234 AND DL1.delivery_id = DL.delivery_id);
2235
2236 CURSOR Cur_Last_Delivery_Leg IS
2237 SELECT DL.delivery_leg_id
2238 FROM mst_delivery_legs DL
2239 WHERE DL.plan_id = p_plan_id
2240 AND DL.delivery_id = p_delivery_id
2244 AND DL1.delivery_id = DL.delivery_id);
2241 AND DL.sequence_number = (SELECT max(DL1.sequence_number) seq_no
2242 FROM mst_delivery_legs DL1
2243 WHERE DL1.plan_id = DL.plan_id
2245 /*
2246 cursor seq_no is
2247 select decode (p_type, 0, min(sequence_number),
2248 1, max(sequence_number)) seq_no
2249 from mst_delivery_legs
2250 where plan_id = p_plan_id
2251 and delivery_id = p_delivery_id;
2252
2253 cursor delivery_leg (p_seq_no number) is
2254 select delivery_leg_id
2255 from mst_delivery_legs
2256 where plan_id = p_plan_id
2257 and delivery_id = p_delivery_id
2258 and sequence_number = p_seq_no;
2259 */
2260 l_seq_no number;
2261 l_leg_id number;
2262 BEGIN
2263 IF p_type = 0 THEN
2264 OPEN Cur_First_Delivery_Leg;
2265 FETCH Cur_First_Delivery_Leg INTO l_leg_id;
2266 CLOSE Cur_First_Delivery_Leg;
2267 ELSIF p_type = 1 THEN
2268 OPEN Cur_Last_Delivery_Leg;
2269 FETCH Cur_Last_Delivery_Leg INTO l_leg_id;
2270 CLOSE Cur_Last_Delivery_Leg;
2271 END IF;
2272
2273 RETURN l_leg_id;
2274 /*
2275 open seq_no;
2276 fetch seq_no into l_seq_no;
2277 close seq_no;
2278 open delivery_leg(l_seq_no);
2279 fetch delivery_leg into l_leg_id;
2280 close delivery_leg;
2281 return l_leg_id;
2282 */
2283 end;
2284 /* Supplier Details */
2285
2286 function get_total_cost_supp (p_plan_id in number,
2287 p_supplier_id in number)
2288 return number is
2289
2290 l_total_cost number;
2291
2292 begin
2293 select sum(mdl.allocated_fac_loading_cost + mdl.allocated_fac_unloading_cost
2294 + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.ALLOCATED_FAC_REC_HAND_COST + mdl.allocated_transport_cost)
2295 into l_total_cost
2296 from mst_deliveries md,
2297 mst_delivery_legs mdl
2298 where md.plan_id = p_plan_id
2299 and md.supplier_id = p_supplier_id
2300 and md.delivery_id = mdl.delivery_id;
2301
2302 return l_total_cost;
2303 EXCEPTION
2304 WHEN OTHERS THEN
2305 RETURN 0;
2306 end;
2307
2308 function get_total_weight_supp (p_plan_id in number,
2309 p_supplier_id in number)
2310 return number is
2311
2312 l_total_weight number;
2313
2314 begin
2315 -- ----------------------------------------
2316 -- As per bug#3316114, we need to consider
2317 -- both Assigned and Unassigned deliveries.
2318 -- ----------------------------------------
2319 -- --------------------
2320 -- Bug#3423219
2321 -- Need to show net wt.
2322 -- --------------------
2323 --select sum(md.gross_weight)
2324 /* select sum(md.net_weight)
2325 into l_total_weight
2326 from mst_deliveries md
2327 where md.plan_id = p_plan_id
2328 and md.supplier_id = p_supplier_id;
2329 */
2330 --and exists (select 1 from mst_delivery_legs mdl
2331 -- where md.delivery_id = mdl.delivery_id
2332 -- );
2333
2334 -- ----------------------------------------
2335 -- Changing as per bug#3548552
2336 -- ----------------------------------------
2337
2338 select sum(nvl(mdd.net_weight,0))
2339 into l_total_weight
2340 from mst_delivery_details mdd
2341 , mst_delivery_assignments mda
2342 , mst_deliveries md
2343 where md.plan_id = mda.plan_id
2344 and md.delivery_id = mda.delivery_id
2345 and mda.plan_id = mdd.plan_id
2346 and mda.delivery_detail_id = mdd.delivery_detail_id
2347 and mdd.container_flag = 2
2348 and md.plan_id = p_plan_id
2349 and md.supplier_id = p_supplier_id;
2350
2351 return l_total_weight;
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 RETURN 0;
2355 end;
2356
2357
2358
2359 function get_total_cube_supp (p_plan_id in number,
2360 p_supplier_id in number)
2361 return number is
2362
2363 l_total_volume number;
2364
2365 BEGIN
2366 -- ----------------------------------------
2367 -- As per bug#3316114, we need to consider
2368 -- both Assigned and Unassigned deliveries.
2369 -- ----------------------------------------
2370 /* select sum(md.volume)
2371 into l_total_volume
2372 from mst_deliveries md
2373 where md.plan_id = p_plan_id
2374 and md.supplier_id = p_supplier_id;
2375 */
2376 --and exists (select 1 from mst_delivery_legs mdl
2377 -- where md.delivery_id = mdl.delivery_id
2378 -- );
2379
2380 -- ----------------------------------------
2381 -- Changing as per bug#3548552
2382 -- ----------------------------------------
2383
2384 select sum(nvl(mdd.volume,0))
2385 into l_total_volume
2386 from mst_delivery_details mdd
2387 , mst_delivery_assignments mda
2388 , mst_deliveries md
2389 where md.plan_id = mda.plan_id
2390 and md.delivery_id = mda.delivery_id
2391 and mda.plan_id = mdd.plan_id
2392 and mda.delivery_detail_id = mdd.delivery_detail_id
2393 and mdd.container_flag = 2
2394 and md.plan_id = p_plan_id
2395 and md.supplier_id = p_supplier_id;
2396
2397 return l_total_volume;
2398 EXCEPTION
2399 WHEN OTHERS THEN
2400 RETURN 0;
2401 end;
2402
2403
2404
2408
2405 function get_total_pallets_supp (p_plan_id in number,
2406 p_supplier_id in number)
2407 return number is
2409 l_total_pallets number;
2410
2411 begin
2412 -- ----------------------------------------
2413 -- As per bug#3316114, we need to consider
2414 -- both Assigned and Unassigned deliveries.
2415 -- ----------------------------------------
2416 /* select sum(md.number_of_pallets)
2417 into l_total_pallets
2418 from mst_deliveries md
2419 where md.plan_id = p_plan_id
2420 and md.supplier_id = p_supplier_id;
2421 */
2422 --and exists (select 1 from mst_delivery_legs mdl
2423 -- where md.delivery_id = mdl.delivery_id
2424 -- );
2425
2426 -- ----------------------------------------
2427 -- Changing as per bug#3548552
2428 -- ----------------------------------------
2429
2430 select sum(ceil(nvl(mdd.number_of_pallets,0)))
2431 into l_total_pallets
2432 from mst_delivery_details mdd
2433 , mst_delivery_assignments mda
2434 , mst_deliveries md
2435 where md.plan_id = mda.plan_id
2436 and md.delivery_id = mda.delivery_id
2437 and mda.plan_id = mdd.plan_id
2438 and mda.delivery_detail_id = mdd.delivery_detail_id
2439 and mdd.container_flag = 2
2440 and md.plan_id = p_plan_id
2441 and md.supplier_id = p_supplier_id;
2442
2443 return l_total_pallets;
2444 EXCEPTION
2445 WHEN OTHERS THEN
2446 RETURN 0;
2447 end;
2448
2449
2450
2451 function get_total_pieces_supp (p_plan_id in number,
2452 p_supplier_id in number)
2453 return number is
2454
2455 l_total_pieces number;
2456
2457 begin
2458 -- ----------------------------------------
2459 -- As per bug#3316114, we need to consider
2460 -- both Assigned and Unassigned deliveries.
2461 -- ----------------------------------------
2462 /* select sum(md.number_of_pieces)
2463 into l_total_pieces
2464 from mst_deliveries md
2465 where md.plan_id = p_plan_id
2466 and md.supplier_id = p_supplier_id;
2467 */
2468 --and exists (select 1 from mst_delivery_legs mdl
2469 -- where md.delivery_id = mdl.delivery_id
2470 -- );
2471
2472 -- ----------------------------------------
2473 -- Changing as per bug#3548552
2474 -- ----------------------------------------
2475
2476 select sum(nvl(mdd.requested_quantity,0))
2477 into l_total_pieces
2478 from mst_delivery_details mdd
2479 , mst_delivery_assignments mda
2480 , mst_deliveries md
2481 where md.plan_id = mda.plan_id
2482 and md.delivery_id = mda.delivery_id
2483 and mda.plan_id = mdd.plan_id
2484 and mda.delivery_detail_id = mdd.delivery_detail_id
2485 and mdd.container_flag = 2
2486 and md.plan_id = p_plan_id
2487 and md.supplier_id = p_supplier_id;
2488
2489 return l_total_pieces;
2490 EXCEPTION
2491 WHEN OTHERS THEN
2492 RETURN 0;
2493 end;
2494
2495
2496
2497 function get_total_orders_supp (p_plan_id in number,
2498 p_supplier_id in number)
2499 return number is
2500
2501 l_total_orders number;
2502
2503 begin
2509 select count(1)
2504 -- ----------------------------------------
2505 -- As per bug#3283569, we need to consider
2506 -- raw orders instead of TP order lines.
2507 -- ----------------------------------------
2508 /***
2510 into l_total_orders
2511 from mst_delivery_details mdd,
2512 mst_delivery_assignments mda
2513 where mdd.plan_id = p_plan_id
2514 and mdd.supplier_id = p_supplier_id
2515 and mdd.split_from_delivery_detail_id is null
2516 and mdd.delivery_detail_id = mda.delivery_detail_id
2517 and mda.parent_delivery_detail_id is null
2518 and exists (select 1
2522 ***/
2519 from mst_delivery_legs mdl
2520 where mdl.delivery_id = mda.delivery_id
2521 );
2523 /*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
2524 INTO l_total_orders
2525 FROM MST_DELIVERY_DETAILS MDD,
2526 MST_DELIVERIES MD,
2527 MST_DELIVERY_ASSIGNMENTS MDA
2528 WHERE MD.PLAN_ID = MDA.PLAN_ID
2529 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2530 AND MD.DELIVERY_ID IN
2531 (SELECT MDL.DELIVERY_ID
2532 FROM MST_DELIVERY_LEGS MDL
2533 WHERE MDL.PLAN_ID = MD.PLAN_ID)
2534 AND MDA.PLAN_ID = MDD.PLAN_ID
2535 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2536 AND MD.PLAN_ID = P_PLAN_ID
2537 AND MD.SUPPLIER_ID = p_supplier_id;*/
2538 -- ----------------------------------------
2539 -- As per bug#3316114, we need to consider
2540 -- both Assigned and Unassigned deliveries.
2541 -- ----------------------------------------
2542 SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
2543 INTO l_total_orders
2544 from (
2545 SELECT mdd.SOURCE_HEADER_NUMBER
2546 FROM MST_DELIVERY_DETAILS MDD,
2547 MST_DELIVERIES MD,
2548 MST_DELIVERY_ASSIGNMENTS MDA
2549 WHERE MD.PLAN_ID = MDA.PLAN_ID
2553 FROM MST_DELIVERY_LEGS MDL
2550 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2551 AND MD.DELIVERY_ID IN
2552 (SELECT MDL.DELIVERY_ID
2554 WHERE MDL.PLAN_ID = MD.PLAN_ID)
2555 AND MDA.PLAN_ID = MDD.PLAN_ID
2556 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2557 AND MD.PLAN_ID = p_PLAN_ID
2558 AND MD.SUPPLIER_ID = p_supplier_id
2559 union all
2560 SELECT mdd.SOURCE_HEADER_NUMBER
2561 FROM MST_DELIVERY_DETAILS MDD,
2562 MST_DELIVERIES MD,
2563 MST_DELIVERY_ASSIGNMENTS MDA
2564 WHERE MD.PLAN_ID = MDA.PLAN_ID
2565 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2566 AND not exists
2567 (SELECT 1
2568 FROM MST_DELIVERY_LEGS MDL
2569 WHERE MDL.PLAN_ID = MD.PLAN_ID
2570 and MDL.DELIVERY_ID = md.plan_id)
2571 AND MDA.PLAN_ID = MDD.PLAN_ID
2572 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2573 AND MD.PLAN_ID = p_PLAN_ID
2574 AND MD.SUPPLIER_ID = p_supplier_id) dd;
2575 return l_total_orders;
2576 EXCEPTION
2577 WHEN OTHERS THEN
2578 RETURN 0;
2579 end;
2580
2581
2582
2583 /* Customer/Supplier Facility Details */
2584 function get_total_cost_c_s_fac (p_plan_id in number, p_facility_type in number,
2585 p_cust_or_supp_id in number, p_location_id in number)
2586 return number is
2587
2588 l_total_cost number;
2589
2590 begin
2591 select sum(mdl.allocated_fac_loading_cost + mdl.allocated_fac_unloading_cost
2592 + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.ALLOCATED_FAC_REC_HAND_COST + mdl.allocated_transport_cost)
2593 into l_total_cost
2594 from mst_deliveries md,
2595 mst_delivery_legs mdl
2596 where md.plan_id = p_plan_id
2597 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2598 and (md.pickup_location_id = p_location_id
2599 or md.dropoff_location_id = p_location_id)
2600 and md.delivery_id = mdl.delivery_id;
2601 l_total_cost := NVL(l_total_cost,0);
2602 return l_total_cost;
2603 EXCEPTION
2604 WHEN OTHERS THEN
2605 RETURN 0;
2606 end;
2607
2608
2609 function get_total_weight_c_s_fac (p_plan_id in number, p_facility_type in number,
2610 p_cust_or_supp_id in number, p_location_id in number)
2611 return number is
2612
2613 l_total_weight number;
2614
2615 begin
2616 -- ----------------------------------------
2617 -- As per bug#3316114, we need to consider
2618 -- both Assigned and Unassigned deliveries.
2619 -- ----------------------------------------
2620 -- --------------------
2621 -- Bug#3423219
2622 -- Need to show net wt.
2623 -- --------------------
2624 --select sum(md.gross_weight)
2625 /* select sum(md.net_weight)
2626 into l_total_weight
2627 from mst_deliveries md
2628 where md.plan_id = p_plan_id
2629 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2630 and (md.pickup_location_id = p_location_id
2631 or md.dropoff_location_id = p_location_id);
2632 */
2633 --and exists (select * from mst_delivery_legs mdl
2634 -- where md.delivery_id = mdl.delivery_id
2635 -- );
2636 -- l_total_weight := NVL(l_total_weight,0);
2637
2638 -- ----------------------------------------
2639 -- Changing as per bug#3548552
2640 -- ----------------------------------------
2641
2642 select sum(nvl(mdd.net_weight,0))
2643 into l_total_weight
2644 from mst_delivery_details mdd
2645 , mst_delivery_assignments mda
2646 , mst_deliveries md
2647 where md.plan_id = mda.plan_id
2648 and md.delivery_id = mda.delivery_id
2649 and mda.plan_id = mdd.plan_id
2650 and mda.delivery_detail_id = mdd.delivery_detail_id
2651 and mdd.container_flag = 2
2652 and md.plan_id = p_plan_id
2653 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2654 and (md.pickup_location_id = p_location_id
2655 or md.dropoff_location_id = p_location_id);
2656
2657 return l_total_weight;
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 RETURN 0;
2661 end;
2662
2663 function get_total_cube_c_s_fac (p_plan_id in number,p_facility_type in number,
2664 p_cust_or_supp_id in number, p_location_id in number)
2665 return number is
2666
2667 l_total_cube number;
2668
2669 begin
2670 -- ----------------------------------------
2671 -- As per bug#3316114, we need to consider
2672 -- both Assigned and Unassigned deliveries.
2673 -- ----------------------------------------
2674 /* select sum(md.volume)
2675 into l_total_cube
2676 from mst_deliveries md
2677 where md.plan_id = p_plan_id
2678 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2679 and (md.pickup_location_id = p_location_id
2680 or md.dropoff_location_id = p_location_id);
2681 */
2682 --and exists (select * from mst_delivery_legs mdl
2683 -- where md.delivery_id = mdl.delivery_id
2684 -- );
2685 -- l_total_cube := NVL(l_total_cube,0);
2686
2687 -- ----------------------------------------
2688 -- Changing as per bug#3548552
2689 -- ----------------------------------------
2690
2694 , mst_delivery_assignments mda
2691 select sum(nvl(mdd.volume,0))
2692 into l_total_cube
2693 from mst_delivery_details mdd
2695 , mst_deliveries md
2696 where md.plan_id = mda.plan_id
2697 and md.delivery_id = mda.delivery_id
2698 and mda.plan_id = mdd.plan_id
2699 and mda.delivery_detail_id = mdd.delivery_detail_id
2700 and mdd.container_flag = 2
2701 and md.plan_id = p_plan_id
2702 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2703 and (md.pickup_location_id = p_location_id
2704 or md.dropoff_location_id = p_location_id);
2705
2706 return l_total_cube;
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 RETURN 0;
2710 end;
2711
2712 function get_total_pallets_c_s_fac (p_plan_id in number, p_facility_type in number,
2713 p_cust_or_supp_id in number, p_location_id in number)
2714 return number is
2715
2716 l_total_pallets number;
2717
2718 begin
2719 -- ----------------------------------------
2720 -- As per bug#3316114, we need to consider
2721 -- both Assigned and Unassigned deliveries.
2722 -- ----------------------------------------
2723 /* select sum(md.number_of_pallets)
2724 into l_total_pallets
2725 from mst_deliveries md
2726 where md.plan_id = p_plan_id
2727 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2728 and (md.pickup_location_id = p_location_id
2729 or md.dropoff_location_id = p_location_id);
2730 */
2731 --and exists (select * from mst_delivery_legs mdl
2732 -- where md.delivery_id = mdl.delivery_id
2733 -- );
2734
2735 -- l_total_pallets := NVL(l_total_pallets,0);
2736
2737 -- ----------------------------------------
2738 -- Changing as per bug#3548552
2739 -- ----------------------------------------
2740
2741 select sum(ceil(nvl(mdd.number_of_pallets,0)))
2742 into l_total_pallets
2743 from mst_delivery_details mdd
2744 , mst_delivery_assignments mda
2745 , mst_deliveries md
2746 where md.plan_id = mda.plan_id
2747 and md.delivery_id = mda.delivery_id
2748 and mda.plan_id = mdd.plan_id
2749 and mda.delivery_detail_id = mdd.delivery_detail_id
2750 and mdd.container_flag = 2
2751 and md.plan_id = p_plan_id
2752 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2753 and (md.pickup_location_id = p_location_id
2754 or md.dropoff_location_id = p_location_id);
2755
2756 return l_total_pallets;
2757 EXCEPTION
2758 WHEN OTHERS THEN
2759 RETURN 0;
2760 end;
2761
2762 function get_total_pieces_c_s_fac (p_plan_id in number, p_facility_type in number,
2763 p_cust_or_supp_id in number, p_location_id in number)
2764 return number is
2765
2766 l_total_pieces number;
2767
2768 begin
2769 -- ----------------------------------------
2770 -- As per bug#3316114, we need to consider
2771 -- both Assigned and Unassigned deliveries.
2772 -- ----------------------------------------
2773 /* select sum(md.number_of_pieces)
2774 into l_total_pieces
2775 from mst_deliveries md
2776 where md.plan_id = p_plan_id
2777 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2778 and (md.pickup_location_id = p_location_id
2779 or md.dropoff_location_id = p_location_id);
2780 */
2781 --and exists (select 1 from mst_delivery_legs mdl
2782 -- where md.delivery_id = mdl.delivery_id
2783 -- );
2784
2785 -- l_total_pieces := NVL(l_total_pieces, 0);
2786
2787
2788 -- ----------------------------------------
2789 -- Changing as per bug#3548552
2790 -- ----------------------------------------
2791
2792 select sum(nvl(mdd.requested_quantity,0))
2793 into l_total_pieces
2794 from mst_delivery_details mdd
2795 , mst_delivery_assignments mda
2796 , mst_deliveries md
2797 where md.plan_id = mda.plan_id
2798 and md.delivery_id = mda.delivery_id
2799 and mda.plan_id = mdd.plan_id
2800 and mda.delivery_detail_id = mdd.delivery_detail_id
2801 and mdd.container_flag = 2
2802 and md.plan_id = p_plan_id
2803 and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2804 and (md.pickup_location_id = p_location_id
2805 or md.dropoff_location_id = p_location_id);
2806
2807 return l_total_pieces;
2808 EXCEPTION
2809 WHEN OTHERS THEN
2810 RETURN 0;
2811 end;
2812
2813 function get_total_order_c_s_fac (p_plan_id in number, p_facility_type in number,
2814 p_cust_or_supp_id in number,p_location_id in number)
2815 return number is
2816 l_total_orders number;
2817 BEGIN
2818 -- ----------------------------------------
2819 -- As per bug#3283731, we need to consider
2820 -- raw orders instead of TP order lines.
2821 -- ----------------------------------------
2822 /***
2823 select count(1)
2824 into l_total_orders
2825 from mst_delivery_details mdd,
2826 mst_delivery_assignments mda
2827 where mdd.plan_id = p_plan_id
2828 and decode(p_facility_type, 0, mdd.customer_id, mdd.supplier_id) = p_cust_or_supp_id
2829 and (mdd.ship_from_location_id = p_location_id or
2830 mdd.ship_to_location_id = p_location_id)
2831 and mdd.split_from_delivery_detail_id is null
2835 from mst_delivery_legs mdl
2832 and mdd.delivery_detail_id = mda.delivery_detail_id
2833 and mda.parent_delivery_detail_id is null
2834 and exists (select 1
2836 where mdl.delivery_id = mda.delivery_id
2837 );
2838 ***/
2839 -- ----------------------------------------
2840 -- As per bug#3316114, we need to consider
2841 -- both Assigned and Unassigned deliveries.
2842 -- ----------------------------------------
2843 IF p_facility_type = 0 THEN
2844 SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
2845 INTO l_total_orders
2846 FROM (
2847 SELECT mdd.SOURCE_HEADER_NUMBER
2848 FROM MST_DELIVERY_DETAILS MDD,
2849 MST_DELIVERIES MD,
2850 MST_DELIVERY_ASSIGNMENTS MDA
2851 WHERE MD.PLAN_ID = MDA.PLAN_ID
2852 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2853 AND MDA.PLAN_ID = MDD.PLAN_ID
2854 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2855 AND MD.PLAN_ID = P_PLAN_ID
2856 AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
2857 AND MD.DELIVERY_ID IN
2858 (SELECT MDL.DELIVERY_ID
2859 FROM MST_DELIVERY_LEGS MDL,
2860 MST_TRIP_STOPS MTS
2861 WHERE MDL.PLAN_ID = MD.PLAN_ID
2862 AND MDL.PLAN_ID = MTS.PLAN_ID
2863 AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
2864 OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
2865 AND MTS.STOP_LOCATION_ID = P_LOCATION_ID)
2866 union ALL
2867 SELECT mdd.SOURCE_HEADER_NUMBER
2868 FROM MST_DELIVERY_DETAILS MDD,
2869 MST_DELIVERIES MD,
2870 MST_DELIVERY_ASSIGNMENTS MDA
2871 WHERE MD.PLAN_ID = MDA.PLAN_ID
2872 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2873 AND MDA.PLAN_ID = MDD.PLAN_ID
2874 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2875 AND MD.PLAN_ID = P_PLAN_ID
2876 AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
2877 AND md.dropoff_location_id = p_location_id
2878 AND NOT EXISTS (SELECT 1 FROM mst_delivery_legs mdl
2879 WHERE mdl.plan_id=md.plan_id
2880 AND mdl.delivery_id = md.delivery_id)) dd;
2881 ELSE
2882 SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
2883 INTO l_total_orders
2884 FROM (
2885 SELECT mdd.SOURCE_HEADER_NUMBER
2886 FROM MST_DELIVERY_DETAILS MDD,
2887 MST_DELIVERIES MD,
2888 MST_DELIVERY_ASSIGNMENTS MDA
2889 WHERE MD.PLAN_ID = MDA.PLAN_ID
2890 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2891 AND MDA.PLAN_ID = MDD.PLAN_ID
2892 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2893 AND MD.PLAN_ID = P_PLAN_ID
2894 AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
2895 AND MD.DELIVERY_ID IN
2896 (SELECT MDL.DELIVERY_ID
2897 FROM MST_DELIVERY_LEGS MDL,
2898 MST_TRIP_STOPS MTS
2899 WHERE MDL.PLAN_ID = MD.PLAN_ID
2900 AND MDL.PLAN_ID = MTS.PLAN_ID
2901 AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
2902 OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
2903 AND MTS.STOP_LOCATION_ID = P_LOCATION_ID)
2904 union ALL
2905 SELECT mdd.SOURCE_HEADER_NUMBER
2906 FROM MST_DELIVERY_DETAILS MDD,
2907 MST_DELIVERIES MD,
2908 MST_DELIVERY_ASSIGNMENTS MDA
2909 WHERE MD.PLAN_ID = MDA.PLAN_ID
2910 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2911 AND MDA.PLAN_ID = MDD.PLAN_ID
2912 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2913 AND MD.PLAN_ID = P_PLAN_ID
2914 AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
2915 AND md.dropoff_location_id = p_location_id
2916 AND NOT EXISTS (SELECT 1 FROM mst_delivery_legs mdl
2917 WHERE mdl.plan_id=md.plan_id
2918 AND mdl.delivery_id = md.delivery_id)) dd;
2919
2920 END IF;
2921 l_total_orders := NVL(l_total_orders, 0);
2922 return l_total_orders;
2923 EXCEPTION
2924 WHEN OTHERS THEN
2925 RETURN 0;
2926 end;
2927 -- This function is merged from Sasidhar's package
2928 -- the function name has been changed from get_total_direct_tls_facility for consistency
2929 FUNCTION get_total_tl_count_c_s_fac
2930 (p_plan_id IN NUMBER,
2931 p_partner_id IN NUMBER,
2932 p_partner_type IN NUMBER,
2933 p_location_id IN NUMBER,
2934 p_mode_of_transport IN VARCHAR2)
2935 RETURN NUMBER IS
2936
2937 l_total_trips NUMBER;
2938
2939 BEGIN
2940 -- Used in Customer/Supplier Facility Details UI
2941 IF p_mode_of_transport = DTL THEN -- Direct TL
2942 IF (p_partner_type = CUSTOMER) THEN
2943 -- ---------------------------------------------------- --
2944 -- For a given customer facility and mode of transport, --
2945 -- consider all those deliveries handled by that trip. --
2946 -- Verify for origin (OE) or destination (RFC)Locations --
2947 -- of these diliveries to be custmer facility. Also, --
2948 -- verify if these deliveries been assigned to one or --
2952 SELECT COUNT(mt.trip_id)
2949 -- more delivery legs. Check the number of stops for --
2950 -- each trip to be 2 (Direct)Get count of such trips. --
2951 -- ---------------------------------------------------- --
2953 INTO l_total_trips
2954 FROM mst_trips mt
2955 WHERE mt.plan_id = p_plan_id
2956 AND mt.trip_id IN
2957 (select distinct mts.trip_id
2958 from mst_trip_stops mts
2959 , mst_delivery_legs mdl
2960 , mst_deliveries md
2961 where md.plan_id = mt.plan_id
2962 and md.customer_id = p_partner_id
2963 and mts.plan_id = md.plan_id
2964 and mts.stop_location_id = md.dropoff_location_id
2965 and md.dropoff_location_id = p_location_id
2966 and mdl.plan_id = md.plan_id
2967 and mdl.delivery_id = md.delivery_id
2968 and mdl.trip_id = mts.trip_id
2969 and mdl.drop_off_stop_id = mts.stop_id)
2970 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
2971 AND EXISTS
2972 (SELECT ts.trip_id
2973 FROM mst_trip_stops ts
2974 WHERE ts.plan_id = mt.plan_id
2975 AND ts.trip_id = mt.trip_ID
2976 HAVING COUNT(ts.stop_id) =2
2977 GROUP BY ts.trip_id) ;
2978
2979 ELSIF (p_partner_type = SUPPLIER) THEN
2980 -- ---------------------------------------------------- --
2981 -- For a given supplier facility and mode of transport, --
2982 -- consider all those deliveries handled by that trip. --
2983 -- Verify for origin (PO) or destination (RTV)Locations --
2984 -- of these diliveries to be supplier facility. Also, --
2985 -- verify if these deliveries been assigned to one or --
2986 -- more delivery legs. Check the number of stops for --
2987 -- each trip to be 2 (Direct)Get count of such trips. --
2988 -- ---------------------------------------------------- --
2989 SELECT COUNT(mt.trip_id)
2990 INTO l_total_trips
2991 FROM mst_trips mt
2992 WHERE mt.plan_id = p_plan_id
2993 AND mt.trip_id IN
2994 (select distinct mts.trip_id
2995 from mst_trip_stops mts
2996 , mst_delivery_legs mdl
2997 , mst_deliveries md
2998 where md.plan_id = mt.plan_id
2999 and md.supplier_id = p_partner_id
3000 and mts.plan_id = md.plan_id
3001 and mts.stop_location_id = md.pickup_location_id
3002 and md.pickup_location_id = p_location_id
3003 and mdl.plan_id = md.plan_id
3004 and mdl.delivery_id = md.delivery_id
3005 and mdl.trip_id = mts.trip_id
3006 and mdl.pick_up_stop_id = mts.stop_id)
3007 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
3008 AND EXISTS
3009 (SELECT ts.trip_id
3010 FROM mst_trip_stops ts
3011 WHERE ts.plan_id = mt.plan_id
3012 AND ts.trip_id = mt.trip_ID
3013 HAVING COUNT(ts.stop_id) =2
3014 GROUP BY ts.trip_id) ;
3015
3016 END IF;
3017 ELSE
3018 IF (p_partner_type = CUSTOMER) THEN
3019 -- ---------------------------------------------------- --
3020 -- For a given customer facility and mode of transport, --
3021 -- consider all those deliveries handled by that trip. --
3022 -- Verify for origin (OE) or destination (RFC)Locations --
3023 -- of these diliveries to be custmer facility. Also, --
3024 -- verify if these deliveries been assigned to one or --
3025 -- more delivery legs. Check number of stops in each --
3026 -- trip to be > 2 (Multi stop)Get count of such trips. --
3027 -- ---------------------------------------------------- --
3028 SELECT COUNT(mt.trip_id)
3029 INTO l_total_trips
3030 FROM mst_trips mt
3031 WHERE mt.plan_id = p_plan_id
3032 AND mt.trip_id IN
3033 (select distinct mts.trip_id
3034 from mst_trip_stops mts
3035 , mst_delivery_legs mdl
3036 , mst_deliveries md
3037 where md.plan_id = mt.plan_id
3038 and md.customer_id = p_partner_id
3039 and mts.plan_id = md.plan_id
3040 and mts.stop_location_id = md.dropoff_location_id
3041 and md.dropoff_location_id = p_location_id
3042 and mdl.plan_id = md.plan_id
3043 and mdl.delivery_id = md.delivery_id
3044 and mdl.trip_id = mts.trip_id
3045 and mdl.drop_off_stop_id = mts.stop_id)
3046 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
3047 AND EXISTS
3051 AND ts.trip_id = mt.trip_ID
3048 (SELECT ts.trip_id
3049 FROM mst_trip_stops ts
3050 WHERE ts.plan_id = mt.plan_id
3052 HAVING COUNT(ts.stop_id) > 2
3053 GROUP BY ts.trip_id) ;
3054
3055 ELSIF (p_partner_type = SUPPLIER) THEN
3056 -- ---------------------------------------------------- --
3057 -- For a given supplier facility and mode of transport, --
3058 -- consider all those deliveries handled by that trip. --
3059 -- Verify for origin (PO) or destination (RTV)Locations --
3060 -- of these diliveries to be supplier facility. Also, --
3061 -- verify if these deliveries been assigned to one or --
3062 -- more delivery legs. Check number of stops in each --
3063 -- trip to be > 2 (Multi stop)Get count of such trips. --
3064 -- ---------------------------------------------------- --
3065 SELECT COUNT(mt.trip_id)
3066 INTO l_total_trips
3067 FROM mst_trips mt
3068 WHERE mt.plan_id = p_plan_id
3069 AND mt.trip_id IN
3070 (select distinct mts.trip_id
3071 from mst_trip_stops mts
3072 , mst_delivery_legs mdl
3073 , mst_deliveries md
3074 where md.plan_id = mt.plan_id
3075 and md.supplier_id = p_partner_id
3076 and mts.plan_id = md.plan_id
3077 and mts.stop_location_id = md.pickup_location_id
3078 and md.pickup_location_id = p_location_id
3079 and mdl.plan_id = md.plan_id
3080 and mdl.delivery_id = md.delivery_id
3081 and mdl.trip_id = mts.trip_id
3082 and mdl.pick_up_stop_id = mts.stop_id)
3083 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
3084 AND EXISTS
3085 (SELECT ts.trip_id
3086 FROM mst_trip_stops ts
3087 WHERE ts.plan_id = mt.plan_id
3088 AND ts.trip_id = mt.trip_ID
3089 HAVING COUNT(ts.stop_id) >2
3090 GROUP BY ts.trip_id) ;
3091 END IF;
3092 END IF;
3093 l_total_trips := NVL(l_total_trips,0);
3094 RETURN l_total_trips;
3095 EXCEPTION
3096 WHEN OTHERS THEN
3097 RETURN 0;
3098 END get_total_tl_count_c_s_fac;
3099
3100 -- This function is merged from Sasidhar's package
3101 -- the function name has been changed from get_total_trips_facility for consistency
3102 FUNCTION get_total_trips_c_s_fac( p_plan_id IN NUMBER,
3103 p_partner_id IN NUMBER,
3104 p_partner_type IN NUMBER,
3105 p_location_id IN NUMBER,
3106 p_mode_of_transport IN VARCHAR2)
3107 RETURN NUMBER IS
3108
3109 l_total_trips NUMBER := 0;
3110
3111 BEGIN
3112 -- Used in Customer/Supplier Facility Details UI
3113 IF (p_partner_type = CUSTOMER) THEN
3114 -- ---------------------------------------------------- --
3115 -- For a given customer facility and mode of transport, --
3116 -- consider all those deliveries handled by that trip. --
3117 -- Verify for origin (OE) or destination (RFC)Locations --
3118 -- of these diliveries to be custmer facility. Also, --
3119 -- verify if these deliveries been assigned to one or --
3120 -- more delivery legs. --
3121 -- ---------------------------------------------------- --
3122 /*SELECT COUNT(mt.trip_id)
3123 INTO l_total_trips
3124 FROM mst_trips mt
3125 WHERE mt.plan_id = p_plan_id
3126 AND mt.trip_id IN
3127 (SELECT mdl.trip_id
3128 FROM mst_delivery_legs mdl,
3129 mst_deliveries md
3130 WHERE mdl.plan_id = mt.plan_id
3131 AND md.plan_id = mdl.plan_id
3132 AND md.delivery_id = mdl.delivery_id
3133 AND md.customer_id = p_partner_id
3134 AND ( md.pickup_location_id = p_location_id
3135 OR md.dropoff_location_id = p_location_id))
3136 AND mt.mode_of_transport = p_mode_of_transport;*/
3137 -- Rewritten for bug#3598252.
3138 SELECT COUNT(mt.trip_id)
3139 INTO l_total_trips
3140 FROM mst_trips mt
3141 WHERE mt.plan_id = p_plan_id
3142 AND mt.trip_id IN
3143 (SELECT mdl.trip_id
3144 FROM mst_delivery_legs mdl,
3145 mst_deliveries md,
3146 mst_trip_stops mts
3147 WHERE mdl.plan_id = mt.plan_id
3148 AND md.plan_id = mdl.plan_id
3149 AND md.delivery_id = mdl.delivery_id
3150 AND md.customer_id = p_partner_id
3151 AND mts.plan_id = mdl.plan_id
3152 AND ( mdl.pick_up_stop_id = mts.stop_id
3153 OR mdl.drop_off_stop_id = mts.stop_id)
3154 AND mts.stop_location_id = p_location_id)
3155 AND mt.mode_of_transport = p_mode_of_transport;
3156
3157 ELSIF (p_partner_type = SUPPLIER) THEN
3158 -- ---------------------------------------------------- --
3162 -- of these diliveries to be custmer facility. Also, --
3159 -- For a given supplier facility and mode of transport, --
3160 -- consider all those deliveries handled by that trip. --
3161 -- Verify for origin (PO) or destination (RTV)Locations --
3163 -- verify if these deliveries been assigned to one or --
3164 -- more delivery legs. --
3165 -- ---------------------------------------------------- --
3166 /*SELECT COUNT(mt.trip_id)
3167 INTO l_total_trips
3168 FROM mst_trips mt
3169 WHERE mt.plan_id = p_plan_id
3170 AND mt.trip_id IN
3171 (SELECT mdl.trip_id
3172 FROM mst_delivery_legs mdl,
3173 mst_deliveries md
3174 WHERE mdl.plan_id = mt.plan_id
3175 AND md.plan_id = mdl.plan_id
3176 AND md.delivery_id = mdl.delivery_id
3177 AND md.supplier_id = p_partner_id
3178 AND ( md.pickup_location_id = p_location_id
3179 OR md.dropoff_location_id = p_location_id))
3180 AND mt.mode_of_transport = p_mode_of_transport;*/
3181 -- Rewritten for bug#3598252.
3182 SELECT COUNT(mt.trip_id)
3183 INTO l_total_trips
3184 FROM mst_trips mt
3185 WHERE mt.plan_id = p_plan_id
3186 AND mt.trip_id IN
3187 (SELECT mdl.trip_id
3188 FROM mst_delivery_legs mdl,
3189 mst_deliveries md,
3190 mst_trip_stops mts
3191 WHERE mdl.plan_id = mt.plan_id
3192 AND md.plan_id = mdl.plan_id
3193 AND md.delivery_id = mdl.delivery_id
3194 AND md.supplier_id = p_partner_id
3195 AND mts.plan_id = mdl.plan_id
3196 AND ( mdl.pick_up_stop_id = mts.stop_id
3197 OR mdl.drop_off_stop_id = mts.stop_id)
3198 AND mts.stop_location_id = p_location_id)
3199 AND mt.mode_of_transport = p_mode_of_transport;
3200 END IF;
3201
3202 l_total_trips := NVL(l_total_trips,0);
3203 RETURN l_total_trips;
3204
3205 EXCEPTION
3206 WHEN OTHERS THEN
3207 RETURN 0;
3208 END get_total_trips_c_s_fac;
3209
3210 /* My Facility Details*/
3211 -- This function is merged from Sasidhar's package
3212 -- the function name has been changed from get_total_trips_Myfacility for consistency
3213 FUNCTION get_total_trips_for_myfac( p_plan_id IN NUMBER,
3214 p_fac_loc_id IN NUMBER,
3215 p_mode_of_transport IN VARCHAR2,
3216 p_location_type IN VARCHAR2)
3217 RETURN NUMBER IS
3218
3219 CURSOR get_trips IS
3220 SELECT COUNT(mt.trip_id)
3221 FROM mst_trips mt
3222 WHERE mt.plan_id = p_plan_id
3223 AND mt.mode_of_transport = p_mode_of_transport
3224 AND mt.trip_id IN (
3225 SELECT ts.trip_id
3226 FROM mst_trip_stops ts,
3227 mst_delivery_legs mdl
3228 WHERE ts.plan_id = p_plan_id
3229 AND ts.stop_location_id = p_fac_loc_id
3230 AND ts.plan_Id = mdl.plan_Id
3231 AND ( ts.stop_id = mdl.pick_up_stop_id
3232 OR ts.stop_id = mdl.drop_off_stop_id));
3233
3234 CURSOR get_empty_trips IS
3235 SELECT COUNT(mt.trip_id)
3236 FROM mst_trips mt
3237 WHERE mt.plan_id = p_plan_id
3238 AND mt.mode_of_transport = p_mode_of_transport
3239 AND mt.continuous_move_id IS NULL
3240 AND EXISTS (SELECT 1
3241 FROM mst_trip_stops mts
3242 WHERE mts.plan_id = mt.plan_id
3243 AND mts.trip_id = mt.trip_id
3244 AND mts.stop_location_id = p_fac_loc_id)
3245 AND NOT EXISTS (SELECT 1
3246 FROM mst_delivery_legs mdl
3247 WHERE mdl.plan_id = mt.plan_id
3248 AND mdl.trip_id = mt.trip_id);
3249
3250 CURSOR get_trips_load IS
3251 SELECT COUNT(mt.trip_id)
3252 FROM mst_trips mt
3253 WHERE mt.plan_id = p_plan_id
3254 AND mt.mode_of_transport = p_mode_of_transport
3255 AND mt.trip_id IN (
3256 SELECT ts.trip_id
3257 FROM mst_trip_stops ts,
3258 mst_delivery_legs mdl
3259 WHERE ts.plan_id = p_plan_id
3260 AND ts.stop_location_id = p_fac_loc_id
3261 AND ts.stop_id = mdl.pick_up_stop_id
3262 AND ts.plan_id = mdl.plan_id);
3263
3264 CURSOR get_trips_unload IS
3265 SELECT count(mt.trip_id)
3266 FROM mst_trips mt
3267 WHERE mt.plan_id = p_plan_id
3268 AND mt.mode_of_transport = p_mode_of_transport
3269 AND mt.trip_id IN (
3270 SELECT ts.trip_id
3271 FROM mst_trip_stops ts,
3272 mst_delivery_legs mdl
3273 WHERE ts.plan_id = p_plan_id
3274 AND ts.stop_location_id = p_fac_loc_id
3275 AND ts.stop_id = mdl.drop_off_stop_id
3276 AND ts.plan_id = mdl.plan_id);
3277
3278 l_total_trips NUMBER := 0;
3279 l_empty_trips NUMBER := 0;
3280
3281 BEGIN
3282 -- Used in MyFacility Details UI
3283 IF p_location_type IS NULL THEN
3284 OPEN get_trips;
3285 FETCH get_trips INTO l_total_trips;
3286 CLOSE get_trips;
3290 CLOSE get_empty_trips;
3287
3288 OPEN get_empty_trips;
3289 FETCH get_empty_trips INTO l_empty_trips;
3291 l_total_trips := NVL(l_total_trips,0) + NVL(l_empty_trips,0);
3292 ELSIF p_location_type = 'L' THEN
3293 OPEN get_trips_load;
3294 FETCH get_trips_load INTO l_total_trips;
3295 CLOSE get_trips_load;
3296 ELSIF p_location_type = 'U' THEN
3297 OPEN get_trips_unload;
3298 FETCH get_trips_unload INTO l_total_trips;
3299 CLOSE get_trips_unload;
3300 END IF;
3301
3302 RETURN l_total_trips;
3303
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 RETURN 0;
3307 END get_total_trips_for_myfac;
3308
3309 -- This function is merged from Sasidhar's package
3310 -- the function name has been changed from get_total_orders_Myfacility for consistency
3311 -- the implementation logic has also been changed to aggregate on TP orders instead of raw orders
3312 FUNCTION get_total_orders_for_myfac( p_plan_id IN NUMBER,
3313 p_my_fac_location_id IN NUMBER,
3314 p_mode IN VARCHAR2 DEFAULT null,
3315 p_activity_type IN VARCHAR2 DEFAULT null)
3316 RETURN NUMBER IS
3317
3318 l_orders NUMBER;
3319 l_total_orders NUMBER;
3320
3321 BEGIN
3322 /* -- Used in MyFacility Details UI
3323 -- -------------------------------------------
3324 -- Deliveries with delivery legs whose
3325 -- intermediate drop-off is at my facility.
3326 -- It was assumed that at an intermediate
3327 -- stop, all the deliveries that are getting
3328 -- dropped off (from one trip), will be picked
3329 -- up (by other trip).
3330 -- -------------------------------------------
3331 SELECT COUNT(DISTINCT mdd.source_header_number)
3332 INTO l_orders
3333 FROM mst_delivery_details mdd
3334 WHERE mdd.plan_id = p_plan_id
3335 AND mdd.delivery_detail_id IN
3336 (SELECT mda.delivery_detail_id
3337 FROM mst_delivery_assignments mda,
3338 mst_trips mt,
3339 mst_trip_stops mts,
3340 mst_delivery_legs mdl,
3341 mst_deliveries md
3342 WHERE mda.plan_id = mdd.plan_id
3343 AND mda.plan_id = md.plan_id
3344 AND mda.delivery_id = md.delivery_id
3345 AND md.plan_id = mdl.plan_id
3346 AND md.delivery_id = mdl.delivery_id
3347 AND mdl.plan_id = mts.plan_id
3348 AND mdl.drop_off_stop_id = mts.stop_id
3349 AND mts.stop_location_id = p_fac_loc_id
3350 AND mt.plan_id = mts.plan_id
3351 AND mt.trip_id = mts.trip_id
3352 AND mt.mode_of_transport = p_mode_of_transport
3353 AND ( md.pickup_location_id <> p_fac_loc_id
3354 OR md.dropoff_location_id <> p_fac_loc_id ));
3355
3356 l_total_orders := l_orders;
3357 l_orders := 0;
3358 IF p_activity_type IS NULL THEN
3359 -- ------------------------------
3360 -- deliveries whose origination
3361 -- or destination is my facility:
3362 -- ------------------------------
3363 SELECT COUNT(DISTINCT mdd.source_header_number)
3364 INTO l_orders
3365 FROM mst_delivery_details mdd
3366 WHERE mdd.plan_id = p_plan_id
3367 AND mdd.delivery_detail_id IN
3368 ( SELECT mda.delivery_detail_id
3369 FROM mst_delivery_assignments mda,
3370 mst_deliveries md,
3371 mst_trips mt,
3372 mst_delivery_legs mdl
3373 WHERE mda.plan_id = mdd.plan_id
3374 AND mda.plan_id = md.plan_id
3375 AND mda.delivery_id = md.delivery_id
3376 AND md.plan_id = mdl.plan_id
3377 AND md.delivery_id = mdl.delivery_id
3378 AND mdl.plan_id = mt.plan_Id
3379 AND mdl.trip_id = mt.trip_id
3380 AND mt.mode_of_transport = p_mode_of_transport
3381 AND ( md.pickup_location_id = p_fac_loc_id
3382 OR md.dropoff_location_id = p_fac_loc_id));
3383
3384 l_total_orders := l_total_orders + l_orders;
3385
3386 ELSIF p_activity_type = 'L' THEN
3387 -- ------------------------------
3388 -- deliveries whose origination
3389 -- is my facility:
3390 -- ------------------------------
3391 SELECT COUNT(DISTINCT mdd.source_header_number)
3392 INTO l_orders
3393 FROM mst_delivery_details mdd
3394 WHERE mdd.plan_id = p_plan_id
3395 AND mdd.delivery_detail_id IN
3396 ( SELECT mda.delivery_detail_id
3397 FROM mst_delivery_assignments mda,
3398 mst_deliveries md,
3399 mst_trips mt,
3400 mst_delivery_legs mdl
3401 WHERE mda.plan_id = mdd.plan_id
3402 AND mda.plan_id = md.plan_id
3403 AND mda.delivery_id = md.delivery_id
3404 AND md.plan_id = mdl.plan_id
3405 AND md.delivery_id = mdl.delivery_id
3406 AND mdl.plan_id = mt.plan_Id
3407 AND mdl.trip_id = mt.trip_id
3411 l_total_orders := l_total_orders + l_orders;
3408 AND mt.mode_of_transport = p_mode_of_transport
3409 AND md.pickup_location_id = p_fac_loc_id );
3410
3412
3413 ELSIF p_location_type = 'U' THEN
3414 -- ------------------------------
3415 -- deliveries whose destination
3416 -- is my facility:
3417 -- ------------------------------
3418 SELECT COUNT(DISTINCT mdd.source_header_number)
3419 INTO l_orders
3420 FROM mst_delivery_details mdd
3421 WHERE mdd.plan_id = p_plan_id
3422 AND mdd.delivery_detail_id IN
3423 ( SELECT mda.delivery_detail_id
3424 FROM mst_delivery_assignments mda,
3425 mst_deliveries md,
3426 mst_trips mt,
3427 mst_delivery_legs mdl
3428 WHERE mda.plan_id = mdd.plan_id
3429 AND mda.plan_id = md.plan_id
3430 AND mda.delivery_id = md.delivery_id
3431 AND md.plan_id = mdl.plan_id
3432 AND md.delivery_id = mdl.delivery_id
3433 AND mdl.plan_id = mt.plan_Id
3434 AND mdl.trip_id = mt.trip_id
3435 AND mt.mode_of_transport = p_mode_of_transport
3436 AND md.dropoff_location_id = p_fac_loc_id );
3437
3438 l_total_orders := l_total_orders + l_orders;
3439
3440 END IF;
3441 */
3442
3443 --as per requirements in bug # 3364598
3444 --total orders for a given facility location
3445 if (p_activity_type is null and p_mode is null) then
3446 select count(distinct mdd.source_header_number)
3447 into l_total_orders
3448 from mst_delivery_details mdd,
3449 mst_deliveries md,
3450 mst_delivery_assignments mda
3451 where md.plan_id = mda.plan_id
3452 and md.delivery_id = mda.delivery_id
3453 and md.delivery_id in
3454 (select mdl.delivery_id
3455 from mst_trips t,
3456 mst_trip_stops ts,
3457 mst_delivery_legs mdl
3458 where mdl.plan_id = md.plan_id
3459 and ts.plan_id = mdl.plan_id
3460 and (ts.stop_id = mdl.pick_up_stop_id
3461 or ts.stop_id = mdl.drop_off_stop_id)
3462 and ts.stop_location_id = p_my_fac_location_id
3463 and ts.plan_id = t.plan_id
3464 and ts.trip_id = t.trip_id)
3465 and mda.plan_id = mdd.plan_id
3466 and mda.delivery_detail_id = mdd.delivery_detail_id
3467 and md.plan_id = p_plan_id
3468 and mdd.container_flag = 2;
3469 end if;
3470
3471
3472 -- total orders for a given mode_of_transport
3473 if p_activity_type is null and p_mode is not null then
3474 select count(mdd.delivery_detail_id)
3475 into l_total_orders
3476 from mst_trips mt,
3477 mst_trip_stops mts,
3478 mst_delivery_legs mdl,
3479 mst_deliveries md,
3480 mst_delivery_details mdd,
3481 mst_delivery_assignments mda
3482 where mt.plan_id = p_plan_id
3483 and mt.mode_of_transport = p_mode
3484 and mt.trip_id = mts.trip_id
3485 and mt.trip_id = mdl.trip_id
3486 and (mdl.pick_up_stop_id = mts.stop_id or
3487 mdl.drop_off_stop_id = mts.stop_id)
3488 and mts.stop_location_id = p_my_fac_location_id
3489 and mdl.delivery_id = md.delivery_id
3490 and md.delivery_id = mda.delivery_id
3491 and mda.delivery_detail_id = mdd.delivery_detail_id
3492 and mda.parent_delivery_detail_id is null
3493 and mdd.split_from_delivery_detail_id is null;
3494
3495 elsif p_activity_type = 'L' THEN
3496 /*
3497 - ------------------------------------------ -
3498 - As per bug#3244044, we need to consider -
3499 - Distinct orders instead of raw orders. -
3500 - ------------------------------------------ -
3501 */
3502 /*select count(mdd.delivery_detail_id)
3503 into l_total_orders
3504 from mst_trips mt,
3505 mst_trip_stops mts,
3506 mst_delivery_legs mdl,
3507 mst_deliveries md,
3508 mst_delivery_details mdd,
3509 mst_delivery_assignments mda
3510 where mt.plan_id = p_plan_id
3511 and mt.mode_of_transport = p_mode
3512 and mt.trip_id = mts.trip_id
3513 and mt.trip_id = mdl.trip_id
3514 and mdl.pick_up_stop_id = mts.stop_id
3515 and mts.stop_location_id = p_my_fac_location_id
3516 and mdl.delivery_id = md.delivery_id
3517 and md.delivery_id = mda.delivery_id
3518 and mda.delivery_detail_id = mdd.delivery_detail_id
3519 and mda.parent_delivery_detail_id is null
3520 and mdd.split_from_delivery_detail_id is null;*/
3521
3522 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
3523 INTO l_total_orders
3524 FROM MST_DELIVERY_DETAILS MDD,
3525 MST_DELIVERIES MD,
3526 MST_DELIVERY_ASSIGNMENTS MDA
3527 WHERE MD.PLAN_ID = MDA.PLAN_ID
3528 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
3529 AND MD.DELIVERY_ID IN
3530 (SELECT MDL.DELIVERY_ID
3531 FROM MST_TRIPS T,
3532 MST_TRIP_STOPS TS,
3533 MST_DELIVERY_LEGS MDL
3534 WHERE MDL.PLAN_ID = MD.PLAN_ID
3535 AND TS.PLAN_ID = MDL.PLAN_ID
3536 AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
3537 AND TS.STOP_LOCATION_ID = p_my_fac_location_id
3538 AND TS.PLAN_ID = T.PLAN_ID
3542 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
3539 AND TS.TRIP_ID = T.TRIP_ID
3540 AND T.MODE_OF_TRANSPORT = P_MODE)
3541 AND MDA.PLAN_ID = MDD.PLAN_ID
3543 AND MD.PLAN_ID = P_PLAN_ID
3544 AND MDD.CONTAINER_FLAG = 2;
3545 -- AND MDD.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL;
3546 --OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID)
3547 elsif p_activity_type = 'U' then
3548 /*
3549 - ------------------------------------------ -
3550 - As per bug#3244044, we need to consider -
3551 - Distinct orders instead of raw orders. -
3552 - ------------------------------------------ -
3553 */
3554 /*select count(mdd.delivery_detail_id)
3555 into l_total_orders
3556 from mst_trips mt,
3557 mst_trip_stops mts,
3558 mst_delivery_legs mdl,
3559 mst_deliveries md,
3560 mst_delivery_details mdd,
3561 mst_delivery_assignments mda
3562 where mt.plan_id = p_plan_id
3563 and mt.mode_of_transport = p_mode
3564 and mt.trip_id = mts.trip_id
3565 and mt.trip_id = mdl.trip_id
3566 and mdl.drop_off_stop_id = mts.stop_id
3567 and mts.stop_location_id = p_my_fac_location_id
3568 and mdl.delivery_id = md.delivery_id
3569 and md.delivery_id = mda.delivery_id
3570 and mda.delivery_detail_id = mdd.delivery_detail_id
3571 and mda.parent_delivery_detail_id is null
3572 and mdd.split_from_delivery_detail_id is null;*/
3573 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
3574 INTO l_total_orders
3575 FROM MST_DELIVERY_DETAILS MDD,
3576 MST_DELIVERIES MD,
3577 MST_DELIVERY_ASSIGNMENTS MDA
3578 WHERE MD.PLAN_ID = MDA.PLAN_ID
3579 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
3580 AND MD.DELIVERY_ID IN
3581 (SELECT MDL.DELIVERY_ID
3582 FROM MST_TRIPS T,
3583 MST_TRIP_STOPS TS,
3584 MST_DELIVERY_LEGS MDL
3585 WHERE MDL.PLAN_ID = MD.PLAN_ID
3586 AND TS.PLAN_ID = MDL.PLAN_ID
3587 AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
3588 AND TS.STOP_LOCATION_ID = p_my_fac_location_id
3589 AND TS.PLAN_ID = T.PLAN_ID
3590 AND TS.TRIP_ID = T.TRIP_ID
3591 AND T.MODE_OF_TRANSPORT = P_MODE)
3592 AND MDA.PLAN_ID = MDD.PLAN_ID
3593 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
3594 AND MD.PLAN_ID = P_PLAN_ID
3595 AND MDD.CONTAINER_FLAG = 2;
3596 --AND MDD.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL;
3597 end if;
3598
3599 RETURN l_total_orders;
3600 EXCEPTION
3601 WHEN OTHERS THEN
3602 RETURN 0;
3603 END get_total_orders_for_myfac;
3604
3605 -- loading/unloading/total cost for TL/LTL/Parcel
3606 FUNCTION get_loading_cost_for_myfac(p_plan_id IN NUMBER,
3607 p_my_fac_location_id IN NUMBER,
3608 p_mode IN VARCHAR2)
3609 RETURN NUMBER IS
3610
3611 l_loading_cost NUMBER;
3612 l_loading_cost_temp NUMBER;
3613 BEGIN
3614 /*
3615 - ------------------------------------------ -
3616 - As per bug#3244044, we need to consider -
3617 - transportation, and facility related costs -
3618 - along with loading cost. -
3619 - ------------------------------------------ -
3620 */
3621 -- -----------------------------------------
3622 -- Modified for performance ( bug#3379415).
3623 -- -----------------------------------------
3624 -- -----------------------------------------
3625 -- Bug#3403402 - we should consider cost of
3626 -- the delivery as a whole instead of a
3627 -- Particular leg.
3628 -- -----------------------------------------
3629 /*SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3630 NVL(mdl.allocated_transport_cost,0) +
3631 NVL(mdl.allocated_fac_shp_hand_cost,0) )
3632 INTO l_loading_cost
3633 FROM mst_trips mt,
3634 mst_trip_stops mts,
3635 mst_delivery_legs mdl
3636 WHERE mt.plan_id = p_plan_id
3637 AND mt.mode_of_transport = p_mode
3638 AND mt.plan_id = mts.plan_id
3639 AND mt.trip_id = mts.trip_id
3640 AND mts.plan_id = mdl.plan_id
3641 AND mdl.pick_up_stop_id = mts.stop_id
3642 AND mts.stop_location_id = p_my_fac_location_id;*/
3643
3644 SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3645 NVL(mdl.allocated_transport_cost,0) +
3646 NVL(mdl.allocated_fac_shp_hand_cost,0) )
3647 INTO l_loading_cost
3648 FROM mst_delivery_legs mdl
3649 WHERE mdl.plan_id = p_plan_id
3650 AND mdl.delivery_id IN
3651 ( SELECT md.delivery_id
3652 FROM mst_delivery_legs mdl1,
3653 mst_deliveries md,
3654 mst_trips mt,
3655 mst_trip_stops mts
3656 WHERE mt.plan_id = mdl1.plan_id
3657 AND mt.trip_id = mdl1.trip_id
3658 AND mt.mode_of_transport = p_mode
3659 and mt.plan_id = mts.plan_Id
3660 and mt.trip_id = mts.trip_id
3661 and mts.stop_location_id = p_my_fac_location_id
3662 AND md.plan_id = mdl1.plan_id
3663 AND md.delivery_id = mdl1.delivery_id
3664 AND md.plan_id = mdl.plan_id
3665 AND md.PICKUP_LOCATION_ID = p_my_fac_location_id);
3666 -- ----------------------------------------
3667 -- As per bug#3508237, we need to consider
3668 -- cost of cross-dock deliveries as well.
3672 NVL(mdl.allocated_fac_shp_hand_cost,0) )
3669 -- ----------------------------------------
3670 SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3671 NVL(mdl.allocated_transport_cost,0) +
3673 INTO l_loading_cost_temp
3674 FROM mst_delivery_legs mdl
3675 WHERE mdl.plan_id = p_plan_id
3676 AND mdl.delivery_id IN
3677 ( SELECT md.delivery_id
3678 FROM mst_delivery_legs mdl1,
3679 mst_deliveries md,
3680 mst_trips mt,
3681 mst_trip_stops mts
3682 WHERE mt.plan_id = mdl1.plan_id
3683 AND mt.trip_id = mdl1.trip_id
3684 AND mt.mode_of_transport = p_mode
3685 and mt.plan_id = mts.plan_Id
3686 and mt.trip_id = mts.trip_id
3687 and mts.stop_location_id = p_my_fac_location_id
3688 AND MTS.STOP_ID = mdl.pick_up_stop_id
3689 AND md.plan_id = mdl1.plan_id
3690 AND md.delivery_id = mdl1.delivery_id
3691 AND md.plan_id = mdl.plan_id
3692 AND md.PICKUP_LOCATION_ID <> mts.stop_location_id
3693 AND MD.DROPOFF_LOCATION_ID <> mts.stop_location_id);
3694
3695 IF l_loading_cost IS NULL THEN
3696 l_loading_cost := 0;
3697 END IF;
3698
3699 IF l_loading_cost_temp IS NULL THEN
3700 l_loading_cost_temp := 0;
3701 END IF;
3702 RETURN l_loading_cost + l_loading_cost_temp;
3703 EXCEPTION
3704 WHEN OTHERS THEN
3705 RETURN 0;
3706 END get_loading_cost_for_myfac;
3707
3708 FUNCTION get_unloading_cost_for_myfac(p_plan_id IN NUMBER,
3709 p_my_fac_location_id IN NUMBER,
3710 p_mode IN VARCHAR2)
3711 RETURN NUMBER IS
3712
3713 l_unloading_cost NUMBER;
3714 l_unloading_cost_temp NUMBER;
3715 BEGIN
3716 /*
3717 - ------------------------------------------ -
3718 - As per bug#3244044, we need to consider -
3719 - transportation, and facility related costs -
3720 - along with unloading cost. -
3721 - ------------------------------------------ -
3722 */
3723 -- -----------------------------------------
3724 -- Modified for performance ( bug#3379415).
3725 -- -----------------------------------------
3726 -- -----------------------------------------
3727 -- Bug#3403402 - we should consider cost of
3728 -- the delivery as a whole instead of just
3729 -- leg cost for those deliveries whose legs
3730 -- touch the facility.
3731 -- -----------------------------------------
3732 /*select sum(NVL(mdl.allocated_fac_unloading_cost,0)+
3733 NVL(mdl.allocated_transport_cost,0) +
3734 NVL(mdl.allocated_fac_rec_hand_cost,0) )
3735 into l_unloading_cost
3736 from mst_trips mt,
3737 mst_trip_stops mts,
3738 mst_delivery_legs mdl
3739 where mt.plan_id = p_plan_id
3740 and mt.mode_of_transport = p_mode
3741 and mt.plan_id = mts.plan_id
3742 and mt.trip_id = mts.trip_id
3743 and mts.plan_id = mdl.plan_id
3744 and mdl.drop_off_stop_id = mts.stop_id
3745 and mts.stop_location_id = p_my_fac_location_id;*/
3746
3747 SELECT SUM(NVL(mdl.allocated_fac_unloading_cost,0) +
3748 NVL(mdl.allocated_transport_cost,0) +
3749 NVL(mdl.allocated_fac_rec_hand_cost,0) )
3750 INTO l_unloading_Cost
3751 FROM mst_delivery_legs mdl
3752 WHERE mdl.plan_id = p_plan_id
3753 AND mdl.delivery_id IN
3754 ( SELECT md.delivery_id
3755 FROM mst_delivery_legs mdl1,
3756 mst_deliveries md,
3757 mst_trips mt,
3758 mst_trip_stops mts
3759 WHERE mt.plan_id = mdl1.plan_id
3760 AND mt.trip_id = mdl1.trip_id
3761 AND mt.mode_of_transport = p_mode
3762 and mt.plan_id = mts.plan_Id
3763 and mt.trip_id = mts.trip_id
3764 and mts.stop_location_id = p_my_fac_location_id
3765 --AND mts.stop_id = mdl.drop_off_stop_id
3766 AND md.plan_id = mdl1.plan_id
3767 AND md.delivery_id = mdl1.delivery_id
3768 AND md.plan_id = mdl.plan_id
3769 AND md.DROPOFF_LOCATION_ID = p_my_fac_location_id);
3770
3771 -- ----------------------------------------
3772 -- As per bug#3508237, we need to consider
3773 -- cost of cross-dock deliveries as well.
3774 -- ----------------------------------------
3775 SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3776 NVL(mdl.allocated_transport_cost,0) +
3777 NVL(mdl.allocated_fac_shp_hand_cost,0) )
3778 INTO l_unloading_cost_temp
3779 FROM mst_delivery_legs mdl
3780 WHERE mdl.plan_id = p_plan_id
3781 AND mdl.delivery_id IN
3782 ( SELECT md.delivery_id
3783 FROM mst_delivery_legs mdl1,
3784 mst_deliveries md,
3785 mst_trips mt,
3786 mst_trip_stops mts
3787 WHERE mt.plan_id = mdl1.plan_id
3788 AND mt.trip_id = mdl1.trip_id
3789 AND mt.mode_of_transport = p_mode
3790 and mt.plan_id = mts.plan_Id
3791 and mt.trip_id = mts.trip_id
3792 and mts.stop_location_id = p_my_fac_location_id
3793 AND MTS.STOP_ID = mdl.drop_off_stop_id
3794 AND md.plan_id = mdl1.plan_id
3795 AND md.delivery_id = mdl1.delivery_id
3796 AND md.plan_id = mdl.plan_id
3800 IF l_unloading_Cost IS NULL THEN
3797 AND md.PICKUP_LOCATION_ID <> mts.stop_location_id
3798 AND MD.DROPOFF_LOCATION_ID <> mts.stop_location_id);
3799
3801 l_unloading_Cost := 0;
3802 END IF;
3803 IF l_unloading_cost_temp IS NULL THEN
3804 l_unloading_cost_temp := 0;
3805 END IF;
3806 RETURN l_unloading_cost + l_unloading_cost_temp;
3807 EXCEPTION
3808 WHEN OTHERS THEN
3809 RETURN 0;
3810 END get_unloading_cost_for_myfac;
3811
3812 function get_total_cost_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
3813 p_mode in varchar2)
3814 return number is
3815
3816 l_total_cost number;
3817 l_total_departing_cost number;
3818 l_total_arriving_cost number;
3819
3820 cursor departing_delivery_leg is
3821 select sum(mdl.allocated_fac_loading_cost + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.allocated_transport_cost) total_departing_cost
3822 from mst_trips mt,
3823 mst_trip_stops mts,
3824 mst_delivery_legs mdl
3825 where mt.plan_id = p_plan_id
3826 and mt.mode_of_transport = p_mode
3827 and mt.trip_id = mts.trip_id
3828 and mdl.pick_up_stop_id = mts.stop_id
3829 and mts.stop_location_id = p_my_fac_location_id;
3830
3831 cursor arriving_delivery_leg is
3832 select sum(mdl.allocated_fac_unloading_cost + mdl.ALLOCATED_FAC_REC_HAND_COST+ mdl.allocated_transport_cost) total_arriving_cost
3833 from mst_trips mt,
3834 mst_trip_stops mts,
3835 mst_delivery_legs mdl
3836 where mt.plan_id = p_plan_id
3837 and mt.mode_of_transport = p_mode
3838 and mt.trip_id = mts.trip_id
3839 and mdl.drop_off_stop_id = mts.stop_id
3840 and mts.stop_location_id = p_my_fac_location_id;
3841 begin
3842 open departing_delivery_leg;
3843 fetch departing_delivery_leg into l_total_departing_cost;
3844 close departing_delivery_leg;
3845 fetch arriving_delivery_leg into l_total_arriving_cost;
3846 close arriving_delivery_leg;
3847 l_total_cost := l_total_departing_cost + l_total_arriving_cost;
3848 return l_total_cost;
3849 end;
3850
3851 -- loading/unloading weight/cube/pallets/pieces for TL/LTL/Parcels
3852
3853
3854 function get_loading_weight_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
3855 p_mode in varchar2)
3856 return number is
3857
3858 l_loading_weight number;
3859
3860 BEGIN
3861 -- -----------------------------------------
3862 -- Modified for performance ( bug#3379415).
3863 -- -----------------------------------------
3864 select sum(md.gross_weight)
3865 into l_loading_weight
3866 from mst_trips mt,
3867 mst_trip_stops mts,
3868 mst_delivery_legs mdl,
3869 mst_deliveries md
3870 where mt.plan_id = p_plan_id
3871 and mt.mode_of_transport = p_mode
3872 and mt.plan_id = mts.plan_id
3873 and mt.trip_id = mts.trip_id
3874 and mts.plan_id = mdl.plan_id
3875 and mdl.pick_up_stop_id = mts.stop_id
3876 and mts.stop_location_id = p_my_fac_location_id
3877 and mdl.plan_id = md.plan_id
3878 and mdl.delivery_id = md.delivery_id;
3879 IF l_loading_weight IS NULL THEN
3880 l_loading_weight := 0;
3881 END IF;
3882 return l_loading_weight;
3883 EXCEPTION
3884 WHEN OTHERS THEN
3885 RETURN 0;
3886 end;
3887
3888 function get_unloading_weight_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
3889 p_mode in varchar2)
3890 return number is
3891
3892 l_unloading_weight number;
3893
3894 BEGIN
3895 -- -----------------------------------------
3896 -- Modified for performance ( bug#3379415).
3897 -- -----------------------------------------
3898 select sum(md.gross_weight)
3899 into l_unloading_weight
3900 from mst_trips mt,
3901 mst_trip_stops mts,
3902 mst_delivery_legs mdl,
3903 mst_deliveries md
3904 where mt.plan_id = p_plan_id
3905 and mt.mode_of_transport = p_mode
3906 and mt.plan_id = mts.plan_id
3907 and mt.trip_id = mts.trip_id
3908 and mts.plan_id = mdl.plan_id
3909 and mdl.drop_off_stop_id = mts.stop_id
3910 and mts.stop_location_id = p_my_fac_location_id
3911 and mdl.plan_id = md.plan_id
3912 and mdl.delivery_id = md.delivery_id;
3913 IF l_unloading_weight IS NULL THEN
3914 l_unloading_weight := 0;
3915 END IF;
3916 return l_unloading_weight;
3917 EXCEPTION
3918 WHEN OTHERS THEN
3919 RETURN 0;
3920 end;
3921
3922 function get_loading_cube_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
3923 p_mode in varchar2)
3924 return number is
3925
3926 l_loading_cube number;
3927
3928 begin
3929 -- -----------------------------------------
3930 -- Modified for performance ( bug#3379415).
3931 -- -----------------------------------------
3932 select sum(md.volume)
3933 into l_loading_cube
3934 from mst_trips mt,
3935 mst_trip_stops mts,
3936 mst_delivery_legs mdl,
3937 mst_deliveries md
3938 where mt.plan_id = p_plan_id
3939 and mt.mode_of_transport = p_mode
3940 and mt.plan_id = mts.plan_id
3941 and mt.trip_id = mts.trip_id
3942 and mts.plan_id = mdl.plan_id
3943 and mdl.pick_up_stop_id = mts.stop_id
3944 and mts.stop_location_id = p_my_fac_location_id
3948 l_loading_cube := 0;
3945 and mdl.plan_id = md.plan_id
3946 and mdl.delivery_id = md.delivery_id;
3947 IF l_loading_cube IS NULL THEN
3949 END IF;
3950 return l_loading_cube;
3951 EXCEPTION
3952 WHEN OTHERS THEN
3953 RETURN 0;
3954 end;
3955
3956 function get_unloading_cube_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
3957 p_mode in varchar2)
3958 return number is
3959
3960 l_unloading_cube number;
3961
3962 begin
3963 -- -----------------------------------------
3964 -- Modified for performance ( bug#3379415).
3965 -- -----------------------------------------
3966 select sum(md.volume)
3967 into l_unloading_cube
3968 from mst_trips mt,
3969 mst_trip_stops mts,
3970 mst_delivery_legs mdl,
3971 mst_deliveries md
3972 where mt.plan_id = p_plan_id
3973 and mt.mode_of_transport = p_mode
3974 and mt.plan_id = mts.plan_id
3975 and mt.trip_id = mts.trip_id
3976 and mts.plan_id = mdl.plan_id
3977 and mdl.drop_off_stop_id = mts.stop_id
3978 and mts.stop_location_id = p_my_fac_location_id
3979 and mdl.plan_id = md.plan_id
3980 and mdl.delivery_id = md.delivery_id;
3981 IF l_unloading_cube IS NULL THEN
3982 l_unloading_cube := 0;
3983 END IF;
3984 return l_unloading_cube;
3985 EXCEPTION
3986 WHEN OTHERS THEN
3987 RETURN 0;
3988 end;
3989
3990 function get_loading_pallet_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
3991 p_mode in varchar2)
3992 return number is
3993
3994 l_loading_pallets number;
3995
3996 begin
3997 -- -----------------------------------------
3998 -- Modified for performance ( bug#3379415).
3999 -- -----------------------------------------
4000 select sum(md.number_of_pallets)
4001 into l_loading_pallets
4002 from mst_trips mt,
4003 mst_trip_stops mts,
4004 mst_delivery_legs mdl,
4005 mst_deliveries md
4006 where mt.plan_id = p_plan_id
4007 and mt.mode_of_transport = p_mode
4008 and mt.plan_id = mts.plan_id
4009 and mt.trip_id = mts.trip_id
4010 and mdl.plan_id = mts.plan_id
4011 and mdl.pick_up_stop_id = mts.stop_id
4012 and mts.stop_location_id = p_my_fac_location_id
4013 and mdl.plan_id = md.plan_id
4014 and mdl.delivery_id = md.delivery_id;
4015 IF l_loading_pallets IS NULL THEN
4016 l_loading_pallets := 0;
4017 END IF;
4018 return l_loading_pallets;
4019 EXCEPTION
4020 WHEN OTHERS THEN
4021 RETURN 0;
4022 end;
4023
4024 function get_unloading_pallet_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
4025 p_mode in varchar2)
4026 return number is
4027
4028 l_unloading_pallets number;
4029
4030 begin
4031 -- -----------------------------------------
4032 -- Modified for performance ( bug#3379415).
4033 -- -----------------------------------------
4034 select sum(md.number_of_pallets)
4035 into l_unloading_pallets
4036 from mst_trips mt,
4037 mst_trip_stops mts,
4038 mst_delivery_legs mdl,
4039 mst_deliveries md
4040 where mt.plan_id = p_plan_id
4041 and mt.mode_of_transport = p_mode
4042 and mt.plan_id = mts.plan_id
4043 and mt.trip_id = mts.trip_id
4044 and mdl.plan_id = mts.plan_id
4045 and mdl.drop_off_stop_id = mts.stop_id
4046 and mts.stop_location_id = p_my_fac_location_id
4047 and mdl.plan_id = md.plan_id
4048 and mdl.delivery_id = md.delivery_id;
4049 IF l_unloading_pallets IS NULL THEN
4050 l_unloading_pallets := 0;
4051 END IF;
4052 return l_unloading_pallets;
4053 EXCEPTION
4054 WHEN OTHERS THEN
4055 RETURN 0;
4056 end;
4057
4058 function get_loading_piece_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
4059 p_mode in varchar2)
4060 return number is
4061
4062 l_loading_pieces number;
4063
4064 begin
4065 -- -----------------------------------------
4066 -- Modified for performance ( bug#3379415).
4067 -- -----------------------------------------
4068 select sum(md.number_of_pieces)
4069 into l_loading_pieces
4070 from mst_trips mt,
4071 mst_trip_stops mts,
4072 mst_delivery_legs mdl,
4073 mst_deliveries md
4074 where mt.plan_id = p_plan_id
4075 and mt.mode_of_transport = p_mode
4076 and mt.plan_id = mts.plan_id
4077 and mt.trip_id = mts.trip_id
4078 and mdl.plan_id = mts.plan_id
4079 and mdl.pick_up_stop_id = mts.stop_id
4080 and mts.stop_location_id = p_my_fac_location_id
4081 and mdl.plan_id = md.plan_id
4082 and mdl.delivery_id = md.delivery_id;
4083 IF l_loading_pieces IS NULL THEN
4084 l_loading_pieces := 0;
4085 END IF;
4086 return l_loading_pieces;
4087 EXCEPTION
4088 WHEN OTHERS THEN
4089 RETURN 0;
4090 end;
4091
4092 function get_unloading_piece_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
4093 p_mode in varchar2)
4094 return number is
4095
4096 l_unloading_pieces number;
4097
4098 begin
4099 -- -----------------------------------------
4100 -- Modified for performance ( bug#3379415).
4101 -- -----------------------------------------
4105 mst_trip_stops mts,
4102 SELECT sum(md.number_of_pieces)
4103 into l_unloading_pieces
4104 from mst_trips mt,
4106 mst_delivery_legs mdl,
4107 mst_deliveries md
4108 where mt.plan_id = p_plan_id
4109 and mt.mode_of_transport = p_mode
4110 and mt.plan_id = mts.plan_id
4111 and mt.trip_id = mts.trip_id
4112 and mdl.plan_id = mts.plan_id
4113 and mdl.drop_off_stop_id = mts.stop_id
4114 and mts.stop_location_id = p_my_fac_location_id
4115 and mdl.plan_id = md.plan_id
4116 and mdl.delivery_id = md.delivery_id;
4117 IF l_unloading_pieces IS NULL THEN
4118 l_unloading_pieces := 0;
4119 END IF;
4120 return l_unloading_pieces;
4121 EXCEPTION
4122 WHEN OTHERS THEN
4123 RETURN 0;
4124 end;
4125
4126 /* Carrier Details */
4127 /* Carrier Facility Details */
4128 /* The following functions for Carrier Details and Carrier Facility Details windows were created by Sasidhar */
4129
4130 /****************************************************************
4131 FUNCTION get_total_trips_carrier(p_plan_id IN NUMBER,
4132 p_carrier_id IN NUMBER )
4133 RETURN VARCHAR2 IS
4134
4135 -- CURSOR carr_facilities IS
4136 -- SELECT location_id
4137 -- FROM fte_facility_carriers
4138 -- WHERE carrier_id = p_carrier_id;
4139
4140 --CURSOR partner_trips(p_location_id IN NUMBER) IS
4141
4142 --CURSOR get_carrier_facilities IS
4143 --SELECT wlo.wsh_location_id
4144 --FROM wsh_location_owners wlo
4145 --WHERE owner_party_id = p_carrier_id
4146 --AND owner_type = 3; -- 'carrier'
4147
4148 CURSOR carrier_trips IS
4149 SELECT t.trip_id, t.mode_of_transport
4150 FROM mst_trips t
4151 WHERE t.plan_id = p_plan_id
4152 AND t.carrier_id = p_carrier_id
4153 ORDER BY t.mode_of_transport;
4154
4155 CURSOR stop_locations(p_trip_id IN NUMBER) IS
4156 SELECT ts.stop_location_id
4157 FROM mst_trip_stops ts,
4158 wsh_location_owners wlo
4159 WHERE plan_id = p_plan_id
4160 AND ts.trip_id = p_trip_id
4161 AND ts.stop_location_id = wlo.wsh_location_id
4162 AND owner_party_id = p_carrier_id
4163 AND owner_type = CARRIER; -- 'carrier'
4164
4165 CURSOR total_stops(P_TRIP IN NUMBER) IS
4166 SELECT COUNT(mst.stop_id)
4167 FROM mst_trip_stops mst
4168 WHERE mst.plan_id = p_plan_id
4169 AND mst.trip_id = P_TRIP;
4170
4171 l_stops NUMBER;
4172
4173 --l_carr_locations NUM_LIST;
4174
4175 l_Stop_locations NUMBER;
4176 l_is_carrier_location BOOLEAN := FALSE;
4177 l_total_direct_TLs NUMBER := 0;
4178 l_total_Multistop_TLs NUMBER := 0;
4179 l_Total_LTLs NUMBER := 0;
4180 l_Total_PARCELS NUMBER := 0;
4181
4182 l_trips_str VARCHAR2(200);
4183 BEGIN
4184 --FOR CUR_carr_facilities IN carr_facilities LOOP
4185 -- FOR cur_partner_trips IN partner_trips(CUR_carr_facilities.location_id) LOOP
4186 --OPEN get_carrier_facilities;
4187 --FETCH get_carrier_facilities BULK COLLECT INTO l_carr_locations;
4188 --CLOSE get_carrier_facilities;
4189
4190 FOR cur_carrier_trips IN carrier_trips LOOP
4191 l_is_carrier_location := FALSE;
4192 OPEN stop_locations(cur_carrier_trips.TRIP_ID);
4193 FETCH stop_locations INTO l_Stop_locations;
4194 IF stop_locations%FOUND THEN
4195 l_is_carrier_location := TRUE;
4196 END IF;
4197 CLOSE stop_locations;
4198 IF l_is_carrier_location THEN
4199 IF cur_carrier_trips.mode_of_transport = TRUCK THEN
4200 OPEN total_stops(cur_carrier_trips.TRIP_ID);
4201 FETCH total_stops INTO l_stops;
4202 CLOSE total_stops;
4203 IF l_stops = 2 THEN
4204 l_total_direct_TLs := l_total_direct_TLs + 1;
4205 ELSE
4206 l_total_Multistop_TLs := l_total_Multistop_TLs + 1;
4207 END IF;
4208 ELSIF cur_carrier_trips.mode_of_transport = LTL THEN
4209 l_Total_LTLs := l_Total_LTLs + 1 ;
4210 ELSIF cur_carrier_trips.mode_of_transport = PARCEL THEN
4211 l_Total_PARCELS := l_Total_PARCELS + 1 ;
4212 END IF;
4213 END IF;
4214 END LOOP;
4215 l_trips_str := l_total_direct_TLs ||G_delim||
4216 l_total_Multistop_TLs||G_delim||
4217 l_Total_LTLs ||G_delim||
4218 l_Total_PARCELS;
4219 RETURN l_trips_str;
4220 EXCEPTION
4221 WHEN OTHERS THEN
4222 RETURN NULL;
4223 END get_total_trips_carrier;
4224 -- ----------------------------------------------
4225 -- As per bug#3546059 and 3546163,
4226 -- We need to consider all the carrier trips
4227 -- that touch the facility owned by a specific
4228 -- carrier. Hence, function was re-written below.
4229 -- ----------------------------------------------
4230 *****************************************************/
4231
4232 FUNCTION get_total_trips_carrier(p_plan_id IN NUMBER,
4233 p_carrier_id IN NUMBER ) RETURN VARCHAR2 IS
4234
4235 CURSOR get_carrier_facilities IS
4236 SELECT wlo.wsh_location_id
4237 FROM wsh_location_owners wlo
4238 WHERE owner_party_id = p_carrier_id
4239 AND owner_type = CARRIER;
4240
4241 CURSOR trips_to_location(p_location_id IN NUMBER) IS
4242 SELECT t.trip_id, t.mode_of_transport
4243 FROM mst_trips t,
4244 mst_trip_stops ts
4245 WHERE t.plan_id = p_plan_id
4249 ORDER BY t.mode_of_transport;
4246 AND t.plan_id = ts.plan_id
4247 AND t.trip_id = ts.trip_id
4248 AND ts.stop_location_id = p_location_id
4250
4251 CURSOR total_stops(P_TRIP IN NUMBER) IS
4252 SELECT COUNT(mst.stop_id)
4253 FROM mst_trip_stops mst
4254 WHERE mst.plan_id = p_plan_id
4255 AND mst.trip_id = P_TRIP;
4256
4257 l_stops NUMBER;
4258
4259 l_total_direct_TLs NUMBER := 0;
4260 l_total_Multistop_TLs NUMBER := 0;
4261 l_Total_LTLs NUMBER := 0;
4262 l_Total_PARCELS NUMBER := 0;
4263
4264 l_trips_str VARCHAR2(200);
4265 BEGIN
4266
4267 FOR l_carrier_facilities IN get_carrier_facilities LOOP
4268 FOR l_trips_to_location in trips_to_location(l_carrier_facilities.wsh_location_id) loop
4269 l_stops := 0;
4270 IF l_trips_to_location.mode_of_transport = TRUCK THEN
4271 OPEN total_stops(l_trips_to_location.TRIP_ID);
4272 FETCH total_stops INTO l_stops;
4273 CLOSE total_stops;
4274 IF l_stops = 2 THEN
4275 l_total_direct_TLs := l_total_direct_TLs + 1;
4276 ELSE
4277 l_total_Multistop_TLs := l_total_Multistop_TLs + 1;
4278 END IF;
4279 ELSIF l_trips_to_location.mode_of_transport = LTL THEN
4280 l_Total_LTLs := l_Total_LTLs + 1 ;
4281 ELSIF l_trips_to_location.mode_of_transport = PARCEL THEN
4282 l_Total_PARCELS := l_Total_PARCELS + 1 ;
4283 END IF;
4284 END LOOP;
4285 END LOOP;
4286 l_trips_str := l_total_direct_TLs ||G_delim||
4287 l_total_Multistop_TLs||G_delim||
4288 l_Total_LTLs ||G_delim||
4289 l_Total_PARCELS;
4290 RETURN l_trips_str;
4291 EXCEPTION
4292 WHEN OTHERS THEN
4293 RETURN NULL;
4294 END get_total_trips_carrier;
4295
4296 FUNCTION get_total_cost_carrier(p_plan_id IN NUMBER,
4297 p_carrier_id IN NUMBER)
4298 RETURN NUMBER IS
4299
4300 -- ----------------------------------------------
4301 -- As per bug#3546059 and 3546163,
4302 -- We need to consider all the carrier trips
4303 -- that touch the facility owned by a specific
4304 -- carrier.
4305 -- ----------------------------------------------
4306 /*CURSOR cur_total_cost IS
4307 SELECT SUM(mdl.allocated_transport_cost +
4308 DECODE(mts.stop_id,
4309 mdl.pick_up_stop_id,
4310 mdl.allocated_fac_loading_cost,
4311 mdl.drop_off_stop_id,
4312 mdl.allocated_fac_unloading_cost,0))
4313 FROM mst_delivery_legs mdl,
4314 mst_trip_stops mts,
4315 mst_trips mt
4316 WHERE mdl.plan_id = p_plan_id
4317 AND mts.plan_id = mdl.plan_id
4318 AND ( mts.stop_id = mdl.pick_up_stop_id
4319 OR mts.stop_id = mdl.drop_off_stop_id)
4320 AND mt.plan_id = mts.plan_Id
4321 AND mt.trip_id = mts.trip_id
4322 AND mt.carrier_id = p_carrier_id
4323 AND EXISTS (SELECT 1
4324 FROM wsh_location_owners wlo
4325 WHERE wlo.owner_party_id = mt.carrier_id
4326 AND wlo.wsh_location_id = mts.stop_location_id
4327 AND wlo.owner_type = CARRIER);*/
4328
4329 CURSOR cur_total_cost IS
4330 SELECT SUM(mdl.allocated_transport_cost +
4331 DECODE(mts.stop_id,
4332 mdl.pick_up_stop_id,
4333 mdl.allocated_fac_loading_cost,
4334 mdl.drop_off_stop_id,
4335 mdl.allocated_fac_unloading_cost,0) +
4336 DECODE(mts.stop_id,
4337 mdl.pick_up_stop_id,
4338 mdl.allocated_fac_shp_hand_cost,
4339 mdl.drop_off_stop_id,
4340 mdl.allocated_fac_Rec_hand_cost,0) )
4341 FROM mst_delivery_legs mdl,
4342 mst_trip_stops mts,
4343 mst_trips mt
4344 WHERE mdl.plan_id = p_plan_id
4345 AND mt.plan_id = mdl.plan_id
4346 AND mt.trip_id = mdl.trip_id
4347 AND mt.plan_id = mts.plan_Id
4348 AND mt.trip_id = mts.trip_id
4349 AND EXISTS (SELECT 1
4350 FROM wsh_location_owners wlo
4351 WHERE wlo.owner_party_id = p_carrier_id
4352 AND wlo.wsh_location_id = mts.stop_location_id
4353 AND wlo.owner_type = CARRIER);
4354 l_total_cost NUMBER;
4355
4356 BEGIN
4357 OPEN cur_total_cost;
4358 FETCH cur_total_cost INTO l_total_cost;
4359 CLOSE cur_total_cost;
4360 l_total_cost := NVL(l_total_cost,0);
4361 RETURN l_total_cost;
4362
4363 EXCEPTION
4364 WHEN OTHERS THEN
4365 RETURN 0;
4366 END get_total_cost_carrier;
4367
4368 FUNCTION get_total_weight_carr_facility(p_plan_id IN NUMBER,
4369 p_carrier_id IN NUMBER,
4370 p_location_id IN NUMBER,
4371 p_location_type IN VARCHAR2,
4372 p_mode_of_transport IN VARCHAR2)
4373 RETURN NUMBER IS
4374
4375 l_total_weight NUMBER := 0;
4376 l_total_weight_tmp NUMBER := 0;
4377 BEGIN
4378 -- ---------------------------------------------------
4379 -- As per bug#3546059 and 3546163, we need to consider
4380 -- all trips touching the specific carrier facility.
4384 -- ---------------------------------------------
4381 -- ---------------------------------------------------
4382
4383 IF p_location_type = 'U' THEN
4385 -- Need to show weight in transit by all trips
4386 -- (Unload) by all modes of transport
4387 -- for a specified carrier facility.
4388 -- ---------------------------------------------
4389
4390 SELECT SUM(md.gross_weight)
4391 INTO l_total_weight
4392 FROM mst_deliveries md
4393 WHERE md.plan_id = p_plan_id
4394 AND md.delivery_id IN
4395 (SELECT mdl.delivery_id
4396 FROM mst_delivery_legs mdl,
4397 mst_trips mt,
4398 mst_trip_stops mts
4399 WHERE mdl.plan_id = md.plan_id
4400 AND mts.plan_Id = mdl.plan_id
4401 AND mts.stop_id = mdl.drop_off_stop_id
4402 AND mts.stop_location_id = p_location_id
4403 AND mdl.plan_id = mt.plan_id
4404 AND mdl.trip_id = mt.trip_id );
4405 --AND mt.carrier_id = p_carrier_id);
4406
4407 ELSIF p_LOCATION_type = 'L' THEN
4408 -- ---------------------------------------------
4409 -- Need to show weight in transit by all trips
4410 -- (load) by all modes of transport
4411 -- for a specified carrier facility.
4412 -- ---------------------------------------------
4413 SELECT SUM(md.gross_weight)
4414 INTO l_total_weight
4415 FROM mst_deliveries md
4416 WHERE md.plan_id = p_plan_id
4417 AND md.delivery_id IN
4418 (SELECT mdl.delivery_id
4419 FROM mst_delivery_legs mdl,
4420 mst_trips mt,
4421 mst_trip_stops mts
4422 WHERE mdl.plan_id = md.plan_id
4423 AND mts.plan_id = mdl.plan_id
4424 AND mts.stop_id = mdl.pick_up_stop_id
4425 AND mts.stop_location_id = p_location_id
4426 AND mdl.plan_id = mt.plan_id
4427 AND mdl.trip_id = mt.trip_id);
4428 --AND mt.carrier_id = p_carrier_id);
4429 ELSIF p_mode_of_transport IS NOT NULL THEN
4430 -- ---------------------------------------------
4431 -- Need to show weight in transit by all trips
4432 -- (load/Unload) by a given mode of transport
4433 -- for a specified carrier facility.
4434 -- ---------------------------------------------
4435 -- -------------------------------------
4436 -- 28-jUN-04 - Per bug#3713507,
4437 -- we need to double count KPIs
4438 -- - weight/Cube/pallets/Pieces/Orders
4439 -- for unload and load.
4440 -- -------------------------------------
4441 /*
4442 SELECT SUM(md.gross_weight)
4443 INTO l_total_weight
4444 FROM mst_deliveries md
4445 WHERE md.plan_id = p_plan_id
4446 AND md.delivery_id IN
4447 (SELECT mdl.delivery_id
4448 FROM mst_delivery_legs mdl,
4449 mst_trips mt,
4450 mst_trip_stops mts
4451 WHERE mdl.plan_id = md.plan_id
4452 AND mts.plan_Id = mdl.plan_id
4453 AND ( mts.stop_id = mdl.pick_up_stop_id
4454 OR mts.stop_id = mdl.drop_off_stop_id)
4455 AND mts.stop_location_id = p_location_id
4456 AND mdl.plan_id = mt.plan_id
4457 AND mdl.trip_id = mt.trip_id
4458 --AND mt.carrier_id = p_carrier_id
4459 AND mt.mode_of_transport = p_mode_of_transport);
4460 */
4461 SELECT SUM(md.gross_weight)
4462 INTO l_total_weight
4463 FROM mst_deliveries md
4464 WHERE md.plan_id = p_plan_id
4465 AND md.delivery_id IN
4466 (SELECT mdl.delivery_id
4467 FROM mst_delivery_legs mdl,
4468 mst_trips mt,
4469 mst_trip_stops mts
4470 WHERE mdl.plan_id = md.plan_id
4471 AND mts.plan_Id = mdl.plan_id
4472 AND mts.stop_id = mdl.pick_up_stop_id
4473 AND mts.stop_location_id = p_location_id
4474 AND mdl.plan_id = mt.plan_id
4475 AND mdl.trip_id = mt.trip_id
4476 AND mt.mode_of_transport = p_mode_of_transport);
4477
4478 l_total_weight_tmp := NVL(l_total_weight,0);
4479 l_total_weight := 0;
4480
4481 SELECT SUM(md.gross_weight)
4482 INTO l_total_weight
4483 FROM mst_deliveries md
4484 WHERE md.plan_id = p_plan_id
4485 AND md.delivery_id IN
4486 (SELECT mdl.delivery_id
4487 FROM mst_delivery_legs mdl,
4488 mst_trips mt,
4489 mst_trip_stops mts
4490 WHERE mdl.plan_id = md.plan_id
4491 AND mts.plan_Id = mdl.plan_id
4492 AND mts.stop_id = mdl.drop_off_stop_id
4493 AND mts.stop_location_id = p_location_id
4494 AND mdl.plan_id = mt.plan_id
4495 AND mdl.trip_id = mt.trip_id
4496 AND mt.mode_of_transport = p_mode_of_transport);
4497
4501 -- Need to show weight in transit by all trips
4498 l_total_weight := l_total_weight_tmp + NVL(l_total_weight,0);
4499 ELSIF p_mode_of_transport IS NULL THEN
4500 -- ---------------------------------------------
4502 -- (load/Unload) by all modes of transport
4503 -- for a specified carrier facility.
4504 -- ---------------------------------------------
4505 -- ------------------------------------------------------------------
4506 -- Also, as per bug#3546059 and 3546163, we need to double count KPIs
4507 -- - weight/Cube/pallets/Pieces/Orders for unload and load.
4508 -- ------------------------------------------------------------------
4509 /*************************
4510 SELECT SUM(md.gross_weight)
4511 INTO l_total_weight
4512 FROM mst_deliveries md
4513 WHERE md.plan_id = p_plan_id
4514 AND md.delivery_id IN
4515 (SELECT mdl.delivery_id
4516 FROM mst_delivery_legs mdl,
4517 mst_trips mt,
4518 mst_trip_stops mts
4519 WHERE mdl.plan_id = md.plan_id
4520 AND mts.plan_Id = mdl.plan_id
4521 AND ( mts.stop_id = mdl.pick_up_stop_id
4522 OR mts.stop_id = mdl.drop_off_stop_id)
4523 AND mts.stop_location_id = p_location_id
4524 AND mdl.plan_id = mt.plan_id
4525 AND mdl.trip_id = mt.trip_id
4526 AND mt.carrier_id = p_carrier_id);
4527 ***************************/
4528 SELECT SUM(md.gross_weight)
4529 INTO l_total_weight
4530 FROM mst_deliveries md
4531 WHERE md.plan_id = p_plan_id
4532 AND md.delivery_id IN
4533 (SELECT mdl.delivery_id
4534 FROM mst_delivery_legs mdl,
4535 mst_trips mt,
4536 mst_trip_stops mts
4537 WHERE mdl.plan_id = md.plan_id
4538 AND mts.plan_Id = mdl.plan_id
4539 AND mts.stop_id = mdl.pick_up_stop_id
4540 AND mts.stop_location_id = p_location_id
4541 AND mdl.plan_id = mt.plan_id
4542 AND mdl.trip_id = mt.trip_id);
4543
4544 l_total_weight_tmp := NVL(l_total_weight,0);
4545 l_total_weight := 0;
4546
4547 SELECT SUM(md.gross_weight)
4548 INTO l_total_weight
4549 FROM mst_deliveries md
4550 WHERE md.plan_id = p_plan_id
4551 AND md.delivery_id IN
4552 (SELECT mdl.delivery_id
4553 FROM mst_delivery_legs mdl,
4554 mst_trips mt,
4555 mst_trip_stops mts
4556 WHERE mdl.plan_id = md.plan_id
4557 AND mts.plan_Id = mdl.plan_id
4558 AND mts.stop_id = mdl.drop_off_stop_id
4559 AND mts.stop_location_id = p_location_id
4560 AND mdl.plan_id = mt.plan_id
4561 AND mdl.trip_id = mt.trip_id);
4562
4563 l_total_weight := l_total_weight_tmp + NVL(l_total_weight,0);
4564 END IF;
4565
4566 l_total_weight := NVL(l_total_weight,0);
4567
4568 RETURN l_total_weight;
4569 EXCEPTION
4570 WHEN OTHERS THEN
4571 RETURN 0;
4572 END get_total_weight_carr_facility;
4573
4574 FUNCTION get_total_Cube_carr_facility(p_plan_id IN NUMBER,
4575 p_carrier_id IN NUMBER,
4576 p_location_id IN NUMBER,
4577 p_location_type IN VARCHAR2,
4578 p_mode_of_transport IN VARCHAR2)
4579 RETURN NUMBER IS
4580
4581 l_total_volume NUMBER :=0;
4582 l_total_volume_tmp NUMBER :=0;
4583 BEGIN
4584
4585 -- ---------------------------------------------------
4586 -- As per bug#3546059 and 3546163, we need to consider
4587 -- all trips touching the specific carrier facility.
4588 -- ---------------------------------------------------
4589 IF p_location_type = 'U' THEN
4590 -- ---------------------------------------------
4591 -- Need to show volume in transit by all trips
4592 -- (Unload) by all modes of transport
4593 -- for a specified carrier facility.
4594 -- ---------------------------------------------
4595 SELECT SUM(md.volume)
4596 INTO l_total_volume
4597 FROM mst_deliveries md
4598 WHERE md.plan_id = p_plan_id
4599 AND md.delivery_id IN
4600 (SELECT mdl.delivery_id
4601 FROM mst_delivery_legs mdl,
4602 mst_trips mt,
4603 mst_trip_stops mts
4604 WHERE mdl.plan_id = md.plan_id
4605 AND mts.plan_Id = mdl.plan_id
4606 AND mts.stop_id = mdl.drop_off_stop_id
4607 AND mts.stop_location_id = p_location_id
4608 AND mdl.plan_id = mt.plan_id
4609 AND mdl.trip_id = mt.trip_id);
4610 --AND mt.carrier_id = p_carrier_id);
4611
4612 ELSIF p_LOCATION_type = 'L' THEN
4613 -- ---------------------------------------------
4614 -- Need to show volume in transit by all trips
4615 -- (load) by all modes of transport
4616 -- for a specified carrier facility.
4617 -- ---------------------------------------------
4621 WHERE md.plan_id = p_plan_id
4618 SELECT SUM(md.volume)
4619 INTO l_total_volume
4620 FROM mst_deliveries md
4622 AND md.delivery_id IN
4623 (SELECT mdl.delivery_id
4624 FROM mst_delivery_legs mdl,
4625 mst_trips mt,
4626 mst_trip_stops mts
4627 WHERE mdl.plan_id = md.plan_id
4628 AND mts.plan_id = mdl.plan_id
4629 AND mts.stop_id = mdl.pick_up_stop_id
4630 AND mts.stop_location_id = p_location_id
4631 AND mdl.plan_id = mt.plan_id
4632 AND mdl.trip_id = mt.trip_id);
4633 --AND mt.carrier_id = p_carrier_id);
4634 ELSIF p_mode_of_transport IS NOT NULL THEN
4635 -- ---------------------------------------------
4636 -- Need to show volume in transit by all trips
4637 -- (load/Unload) by a given mode of transport
4638 -- for a specified carrier facility.
4639 -- ---------------------------------------------
4640 -- -------------------------------------
4641 -- 28-jUN-04 - Per bug#3713507,
4642 -- we need to double count KPIs
4643 -- - weight/Cube/pallets/Pieces/Orders
4644 -- for unload and load.
4645 -- -------------------------------------
4646 /*
4647 SELECT SUM(md.volume)
4648 INTO l_total_volume
4649 FROM mst_deliveries md
4650 WHERE md.plan_id = p_plan_id
4651 AND md.delivery_id IN
4652 (SELECT mdl.delivery_id
4653 FROM mst_delivery_legs mdl,
4654 mst_trips mt,
4655 mst_trip_stops mts
4656 WHERE mdl.plan_id = md.plan_id
4657 AND mts.plan_Id = mdl.plan_id
4658 AND (mts.stop_id = mdl.pick_up_stop_id
4659 OR mts.stop_id = mdl.drop_off_stop_id)
4660 AND mts.stop_location_id = p_location_id
4661 AND mdl.plan_id = mt.plan_id
4662 AND mdl.trip_id = mt.trip_id
4663 --AND mt.carrier_id = p_carrier_id
4664 AND mt.mode_of_transport = p_mode_of_transport);
4665 */
4666 SELECT SUM(md.volume)
4667 INTO l_total_volume
4668 FROM mst_deliveries md
4669 WHERE md.plan_id = p_plan_id
4670 AND md.delivery_id IN
4671 (SELECT mdl.delivery_id
4672 FROM mst_delivery_legs mdl,
4673 mst_trips mt,
4674 mst_trip_stops mts
4675 WHERE mdl.plan_id = md.plan_id
4676 AND mts.plan_Id = mdl.plan_id
4677 AND mts.stop_id = mdl.pick_up_stop_id
4678 AND mts.stop_location_id = p_location_id
4679 AND mdl.plan_id = mt.plan_id
4680 AND mdl.trip_id = mt.trip_id
4681 AND mt.mode_of_transport = p_mode_of_transport);
4682
4683 l_total_volume_tmp := NVL(l_total_volume,0);
4684 l_total_volume := 0;
4685
4686 SELECT SUM(md.volume)
4687 INTO l_total_volume
4688 FROM mst_deliveries md
4689 WHERE md.plan_id = p_plan_id
4690 AND md.delivery_id IN
4691 (SELECT mdl.delivery_id
4692 FROM mst_delivery_legs mdl,
4693 mst_trips mt,
4694 mst_trip_stops mts
4695 WHERE mdl.plan_id = md.plan_id
4696 AND mts.plan_Id = mdl.plan_id
4697 AND mts.stop_id = mdl.drop_off_stop_id
4698 AND mts.stop_location_id = p_location_id
4699 AND mdl.plan_id = mt.plan_id
4700 AND mdl.trip_id = mt.trip_id
4701 AND mt.mode_of_transport = p_mode_of_transport);
4702
4703 l_total_volume := l_total_volume_tmp + NVL(l_total_volume,0);
4704 ELSIF p_mode_of_transport IS NULL THEN
4705 -- ---------------------------------------------
4706 -- Need to show volume in transit by all trips
4707 -- (load/Unload) by all modes of transport
4708 -- for a specified carrier facility.
4709 -- ---------------------------------------------
4710 -- ------------------------------------------------------------------
4711 -- Also, as per bug#3546059 and 3546163, we need to double count KPIs
4712 -- - weight/Cube/pallets/Pieces/Orders for unload and load.
4713 -- ------------------------------------------------------------------
4714 /*************************
4715 SELECT SUM(md.volume)
4716 INTO l_total_volume
4717 FROM mst_deliveries md
4718 WHERE md.plan_id = p_plan_id
4719 AND md.delivery_id IN
4720 (SELECT mdl.delivery_id
4721 FROM mst_delivery_legs mdl,
4722 mst_trips mt,
4723 mst_trip_stops mts
4724 WHERE mdl.plan_id = md.plan_id
4725 AND mts.plan_Id = mdl.plan_id
4726 AND (mts.stop_id = mdl.pick_up_stop_id
4727 OR mts.stop_id = mdl.drop_off_stop_id)
4728 AND mts.stop_location_id = p_location_id
4729 AND mdl.plan_id = mt.plan_id
4730 AND mdl.trip_id = mt.trip_id
4731 AND mt.carrier_id = p_carrier_id);
4735 FROM mst_deliveries md
4732 *****************************/
4733 SELECT SUM(md.volume)
4734 INTO l_total_volume
4736 WHERE md.plan_id = p_plan_id
4737 AND md.delivery_id IN
4738 (SELECT mdl.delivery_id
4739 FROM mst_delivery_legs mdl,
4740 mst_trips mt,
4741 mst_trip_stops mts
4742 WHERE mdl.plan_id = md.plan_id
4743 AND mts.plan_Id = mdl.plan_id
4744 AND mts.stop_id = mdl.pick_up_stop_id
4745 AND mts.stop_location_id = p_location_id
4746 AND mdl.plan_id = mt.plan_id
4747 AND mdl.trip_id = mt.trip_id);
4748
4749 l_total_volume_tmp := NVL(l_total_volume,0);
4750 l_total_volume := 0;
4751
4752 SELECT SUM(md.volume)
4753 INTO l_total_volume
4754 FROM mst_deliveries md
4755 WHERE md.plan_id = p_plan_id
4756 AND md.delivery_id IN
4757 (SELECT mdl.delivery_id
4758 FROM mst_delivery_legs mdl,
4759 mst_trips mt,
4760 mst_trip_stops mts
4761 WHERE mdl.plan_id = md.plan_id
4762 AND mts.plan_Id = mdl.plan_id
4763 AND mts.stop_id = mdl.drop_off_stop_id
4764 AND mts.stop_location_id = p_location_id
4765 AND mdl.plan_id = mt.plan_id
4766 AND mdl.trip_id = mt.trip_id );
4767 l_total_volume := l_total_volume_tmp + NVL(l_total_volume,0);
4768 END IF;
4769 l_total_volume := NVL(l_total_volume, 0);
4770 RETURN l_total_volume;
4771 EXCEPTION
4772 WHEN OTHERS THEN
4773 RETURN 0;
4774 END get_total_cube_carr_facility;
4775
4776 FUNCTION get_tot_Pal_carr_facility(p_plan_id IN NUMBER,
4777 p_carrier_id IN NUMBER,
4778 p_location_id IN NUMBER,
4779 p_location_type IN VARCHAR2,
4780 p_mode_of_transport IN VARCHAR2)
4781 RETURN NUMBER IS
4782
4783 l_total_Pallets NUMBER := 0;
4784 l_total_Pallets_tmp NUMBER := 0;
4785
4786 BEGIN
4787
4788 -- ---------------------------------------------------
4789 -- As per bug#3546059 and 3546163, we need to consider
4790 -- all trips touching the specific carrier facility.
4791 -- ---------------------------------------------------
4792 IF p_location_type = 'U' THEN
4793 -- ---------------------------------------------
4794 -- Need to show pallets in transit by all trips
4795 -- (Unload) by all modes of transport
4796 -- for a specified carrier facility.
4797 -- ---------------------------------------------
4798 SELECT SUM(md.number_of_pallets)
4799 INTO l_total_Pallets
4800 FROM mst_deliveries md
4801 WHERE md.plan_id = p_plan_id
4802 AND md.delivery_id IN
4803 (SELECT mdl.delivery_id
4804 FROM mst_delivery_legs mdl,
4805 mst_trips mt,
4806 mst_trip_stops mts
4807 WHERE mdl.plan_id = md.plan_id
4808 AND mts.plan_Id = mdl.plan_id
4809 AND mts.stop_id = mdl.drop_off_stop_id
4810 AND mts.stop_location_id = p_location_id
4811 AND mdl.plan_id = mt.plan_id
4812 AND mdl.trip_id = mt.trip_id );
4813 --AND mt.carrier_id = p_carrier_id);
4814
4815 ELSIF p_LOCATION_type = 'L' THEN
4816 -- ---------------------------------------------
4817 -- Need to show pallets in transit by all trips
4818 -- (load) by all modes of transport
4819 -- for a specified carrier facility.
4820 -- ---------------------------------------------
4821 SELECT SUM(md.number_of_pallets)
4822 INTO l_total_Pallets
4823 FROM mst_deliveries md
4824 WHERE md.plan_id = p_plan_id
4825 AND md.delivery_id IN
4826 (SELECT mdl.delivery_id
4827 FROM mst_delivery_legs mdl,
4828 mst_trips mt,
4829 mst_trip_stops mts
4830 WHERE mdl.plan_id = md.plan_id
4831 AND mts.plan_id = mdl.plan_id
4832 AND mts.stop_id = mdl.pick_up_stop_id
4833 AND mts.stop_location_id = p_location_id
4834 AND mdl.plan_id = mt.plan_id
4835 AND mdl.trip_id = mt.trip_id);
4836 --AND mt.carrier_id = p_carrier_id);
4837 ELSIF p_mode_of_transport IS NOT NULL THEN
4838 -- ---------------------------------------------
4839 -- Need to show pallets in transit by all trips
4840 -- (load/Unload) by a given mode of transport
4841 -- for a specified carrier facility.
4842 -- ---------------------------------------------
4843 -- -------------------------------------
4844 -- 28-jUN-04 - Per bug#3713507,
4845 -- we need to double count KPIs
4846 -- - weight/Cube/pallets/Pieces/Orders
4847 -- for unload and load.
4848 -- -------------------------------------
4849 /*
4850 SELECT SUM(md.number_of_pallets)
4851 INTO l_total_Pallets
4852 FROM mst_deliveries md
4853 WHERE md.plan_id = p_plan_id
4854 AND md.delivery_id IN
4858 mst_trip_stops mts
4855 (SELECT mdl.delivery_id
4856 FROM mst_delivery_legs mdl,
4857 mst_trips mt,
4859 WHERE mdl.plan_id = md.plan_id
4860 AND mts.plan_Id = mdl.plan_id
4861 AND ( mts.stop_id = mdl.pick_up_stop_id
4862 OR mts.stop_id = mdl.drop_off_stop_id)
4863 AND mts.stop_location_id = p_location_id
4864 AND mdl.plan_id = mt.plan_id
4865 AND mdl.trip_id = mt.trip_id
4866 AND mt.carrier_id = p_carrier_id);
4867 --AND mt.mode_of_transport = p_mode_of_transport);
4868 */
4869 SELECT SUM(md.number_of_pallets)
4870 INTO l_total_Pallets
4871 FROM mst_deliveries md
4872 WHERE md.plan_id = p_plan_id
4873 AND md.delivery_id IN
4874 (SELECT mdl.delivery_id
4875 FROM mst_delivery_legs mdl,
4876 mst_trips mt,
4877 mst_trip_stops mts
4878 WHERE mdl.plan_id = md.plan_id
4879 AND mts.plan_Id = mdl.plan_id
4880 AND mts.stop_id = mdl.pick_up_stop_id
4881 AND mts.stop_location_id = p_location_id
4882 AND mdl.plan_id = mt.plan_id
4883 AND mdl.trip_id = mt.trip_id
4884 AND mt.carrier_id = p_carrier_id);
4885
4886 l_total_Pallets_tmp := NVL(l_total_Pallets,0);
4887 l_total_Pallets := 0;
4888
4889 SELECT SUM(md.number_of_pallets)
4890 INTO l_total_Pallets
4891 FROM mst_deliveries md
4892 WHERE md.plan_id = p_plan_id
4893 AND md.delivery_id IN
4894 (SELECT mdl.delivery_id
4895 FROM mst_delivery_legs mdl,
4896 mst_trips mt,
4897 mst_trip_stops mts
4898 WHERE mdl.plan_id = md.plan_id
4899 AND mts.plan_Id = mdl.plan_id
4900 AND mts.stop_id = mdl.drop_off_stop_id
4901 AND mts.stop_location_id = p_location_id
4902 AND mdl.plan_id = mt.plan_id
4903 AND mdl.trip_id = mt.trip_id
4904 AND mt.carrier_id = p_carrier_id);
4905
4906 l_total_Pallets := l_total_Pallets_tmp + NVL(l_total_Pallets,0);
4907 ELSIF p_mode_of_transport IS NULL THEN
4908 -- ---------------------------------------------
4909 -- Need to show pallets in transit by all trips
4910 -- (load/Unload) by all modes of transport
4911 -- for a specified carrier facility.
4912 -- ---------------------------------------------
4913 -- ------------------------------------------------------------------
4914 -- Also, as per bug#3546059 and 3546163, we need to double count KPIs
4915 -- - weight/Cube/pallets/Pieces/Orders for unload and load.
4916 -- ------------------------------------------------------------------
4917 /*************************
4918 SELECT SUM(md.number_of_pallets)
4919 INTO l_total_Pallets
4920 FROM mst_deliveries md
4921 WHERE md.plan_id = p_plan_id
4922 AND md.delivery_id IN
4923 (SELECT mdl.delivery_id
4924 FROM mst_delivery_legs mdl,
4925 mst_trips mt,
4926 mst_trip_stops mts
4927 WHERE mdl.plan_id = md.plan_id
4928 AND mts.plan_Id = mdl.plan_id
4929 AND ( mts.stop_id = mdl.pick_up_stop_id
4930 OR mts.stop_id = mdl.drop_off_stop_id)
4931 AND mts.stop_location_id = p_location_id
4932 AND mdl.plan_id = mt.plan_id
4933 AND mdl.trip_id = mt.trip_id
4934 AND mt.carrier_id = p_carrier_id);
4935 ************************/
4936 SELECT SUM(md.number_of_pallets)
4937 INTO l_total_Pallets
4938 FROM mst_deliveries md
4939 WHERE md.plan_id = p_plan_id
4940 AND md.delivery_id IN
4941 (SELECT mdl.delivery_id
4942 FROM mst_delivery_legs mdl,
4943 mst_trips mt,
4944 mst_trip_stops mts
4945 WHERE mdl.plan_id = md.plan_id
4946 AND mts.plan_Id = mdl.plan_id
4947 AND mts.stop_id = mdl.pick_up_stop_id
4948 AND mts.stop_location_id = p_location_id
4949 AND mdl.plan_id = mt.plan_id
4950 AND mdl.trip_id = mt.trip_id);
4951
4952 l_total_Pallets_tmp := NVL(l_total_Pallets,0);
4953 l_total_Pallets := 0;
4954
4955 SELECT SUM(md.number_of_pallets)
4956 INTO l_total_Pallets
4957 FROM mst_deliveries md
4958 WHERE md.plan_id = p_plan_id
4959 AND md.delivery_id IN
4960 (SELECT mdl.delivery_id
4961 FROM mst_delivery_legs mdl,
4962 mst_trips mt,
4963 mst_trip_stops mts
4964 WHERE mdl.plan_id = md.plan_id
4965 AND mts.plan_Id = mdl.plan_id
4966 AND mts.stop_id = mdl.drop_off_stop_id
4967 AND mts.stop_location_id = p_location_id
4971 END IF;
4968 AND mdl.plan_id = mt.plan_id
4969 AND mdl.trip_id = mt.trip_id);
4970 l_total_Pallets := l_total_Pallets_tmp + NVL(l_total_Pallets,0);
4972 l_total_Pallets := NVL(l_total_Pallets,0);
4973 RETURN l_total_Pallets;
4974 EXCEPTION
4975 WHEN OTHERS THEN
4976 RETURN 0;
4977 END get_tot_Pal_carr_facility;
4978
4979 FUNCTION get_tot_Pieces_carr_facility(p_plan_id IN NUMBER,
4980 p_carrier_id IN NUMBER,
4981 p_location_id IN NUMBER,
4982 p_location_type IN VARCHAR2,
4983 p_mode_of_transport IN VARCHAR2)
4984 RETURN NUMBER IS
4985
4986 l_total_Pieces NUMBER := 0;
4987 l_total_Pieces_tmp NUMBER := 0;
4988
4989 BEGIN
4990
4991 -- ---------------------------------------------------
4992 -- As per bug#3546059 and 3546163, we need to consider
4993 -- all trips touching the specific carrier facility.
4994 -- ---------------------------------------------------
4995 IF p_location_type = 'U' THEN
4996 -- ---------------------------------------------
4997 -- Need to show pieces in transit by all trips
4998 -- (Unload) by all modes of transport
4999 -- for a specified carrier facility.
5000 -- ---------------------------------------------
5001 SELECT SUM(md.number_of_pieces)
5002 INTO l_total_Pieces
5003 FROM mst_deliveries md
5004 WHERE md.plan_id = p_plan_id
5005 AND md.delivery_id IN
5006 (SELECT mdl.delivery_id
5007 FROM mst_delivery_legs mdl,
5008 mst_trips mt,
5009 mst_trip_stops mts
5010 WHERE mdl.plan_id = md.plan_id
5011 AND mts.plan_Id = mdl.plan_id
5012 AND mts.stop_id = mdl.drop_off_stop_id
5013 AND mts.stop_location_id = p_location_id
5014 AND mdl.plan_id = mt.plan_id
5015 AND mdl.trip_id = mt.trip_id);
5016 --AND mt.carrier_id = p_carrier_id);
5017
5018 ELSIF p_LOCATION_type = 'L' THEN
5019 -- ---------------------------------------------
5020 -- Need to show pieces in transit by all trips
5021 -- (load) by all modes of transport
5022 -- for a specified carrier facility.
5023 -- ---------------------------------------------
5024 SELECT SUM(md.number_of_pieces)
5025 INTO l_total_Pieces
5026 FROM mst_deliveries md
5027 WHERE md.plan_id = p_plan_id
5028 AND md.delivery_id IN
5029 (SELECT mdl.delivery_id
5030 FROM mst_delivery_legs mdl,
5031 mst_trips mt,
5032 mst_trip_stops mts
5033 WHERE mdl.plan_id = md.plan_id
5034 AND mts.plan_id = mdl.plan_id
5035 AND mts.stop_id = mdl.pick_up_stop_id
5036 AND mts.stop_location_id = p_location_id
5037 AND mdl.plan_id = mt.plan_id
5038 AND mdl.trip_id = mt.trip_id);
5039 --AND mt.carrier_id = p_carrier_id);
5040 ELSIF p_mode_of_transport IS NOT NULL THEN
5041 -- ---------------------------------------------
5042 -- Need to show pallets in transit by all trips
5043 -- (load/Unload) by a given mode of transport
5044 -- for a specified carrier facility.
5045 -- ---------------------------------------------
5046 -- -------------------------------------
5047 -- 28-jUN-04 - Per bug#3713507,
5048 -- we need to double count KPIs
5049 -- - weight/Cube/pallets/Pieces/Orders
5050 -- for unload and load.
5051 -- -------------------------------------
5052 /*
5053 SELECT SUM(md.number_of_pieces)
5054 INTO l_total_Pieces
5055 FROM mst_deliveries md
5056 WHERE md.plan_id = p_plan_id
5057 AND md.delivery_id IN
5058 (SELECT mdl.delivery_id
5059 FROM mst_delivery_legs mdl,
5060 mst_trips mt,
5061 mst_trip_stops mts
5062 WHERE mdl.plan_id = md.plan_id
5063 AND mts.plan_Id = mdl.plan_id
5064 AND ( mts.stop_id = mdl.pick_up_stop_id
5065 OR mts.stop_id = mdl.drop_off_stop_id)
5066 AND mts.stop_location_id = p_location_id
5067 AND mdl.plan_id = mt.plan_id
5068 AND mdl.trip_id = mt.trip_id
5069 --AND mt.carrier_id = p_carrier_id
5070 AND mt.mode_of_transport = p_mode_of_transport);
5071 */
5072 SELECT SUM(md.number_of_pieces)
5073 INTO l_total_Pieces
5074 FROM mst_deliveries md
5075 WHERE md.plan_id = p_plan_id
5076 AND md.delivery_id IN
5077 (SELECT mdl.delivery_id
5078 FROM mst_delivery_legs mdl,
5079 mst_trips mt,
5080 mst_trip_stops mts
5081 WHERE mdl.plan_id = md.plan_id
5082 AND mts.plan_Id = mdl.plan_id
5083 AND mts.stop_id = mdl.pick_up_stop_id
5084 AND mts.stop_location_id = p_location_id
5085 AND mdl.plan_id = mt.plan_id
5086 AND mdl.trip_id = mt.trip_id
5090 l_total_Pieces := 0;
5087 AND mt.mode_of_transport = p_mode_of_transport);
5088
5089 l_total_Pieces_tmp := NVL(l_total_Pieces,0);
5091
5092 SELECT SUM(md.number_of_pieces)
5093 INTO l_total_Pieces
5094 FROM mst_deliveries md
5095 WHERE md.plan_id = p_plan_id
5096 AND md.delivery_id IN
5097 (SELECT mdl.delivery_id
5098 FROM mst_delivery_legs mdl,
5099 mst_trips mt,
5100 mst_trip_stops mts
5101 WHERE mdl.plan_id = md.plan_id
5102 AND mts.plan_Id = mdl.plan_id
5103 AND mts.stop_id = mdl.drop_off_stop_id
5104 AND mts.stop_location_id = p_location_id
5105 AND mdl.plan_id = mt.plan_id
5106 AND mdl.trip_id = mt.trip_id
5107 AND mt.mode_of_transport = p_mode_of_transport);
5108
5109 l_total_Pieces := l_total_Pieces_tmp + NVL(l_total_Pieces,0);
5110 ELSIF p_mode_of_transport IS NULL THEN
5111 -- ---------------------------------------------
5112 -- Need to show pallets in transit by all trips
5113 -- (load/Unload) by all modes of transport
5114 -- for a specified carrier facility.
5115 -- ---------------------------------------------
5116 -- ------------------------------------------------------------------
5117 -- Also, as per bug#3546059 and 3546163, we need to double count KPIs
5118 -- - weight/Cube/pallets/Pieces/Orders for unload and load.
5119 -- ------------------------------------------------------------------
5120 /*************************
5121 SELECT SUM(md.number_of_pieces)
5122 INTO l_total_Pieces
5123 FROM mst_deliveries md
5124 WHERE md.plan_id = p_plan_id
5125 AND md.delivery_id IN
5126 (SELECT mdl.delivery_id
5127 FROM mst_delivery_legs mdl,
5128 mst_trips mt,
5129 mst_trip_stops mts
5130 WHERE mdl.plan_id = md.plan_id
5131 AND mts.plan_Id = mdl.plan_id
5132 AND ( mts.stop_id = mdl.pick_up_stop_id
5133 OR mts.stop_id = mdl.drop_off_stop_id)
5134 AND mts.stop_location_id = p_location_id
5135 AND mdl.plan_id = mt.plan_id
5136 AND mdl.trip_id = mt.trip_id
5137 AND mt.carrier_id = p_carrier_id);
5138 *************************/
5139 SELECT SUM(md.number_of_pieces)
5140 INTO l_total_Pieces
5141 FROM mst_deliveries md
5142 WHERE md.plan_id = p_plan_id
5143 AND md.delivery_id IN
5144 (SELECT mdl.delivery_id
5145 FROM mst_delivery_legs mdl,
5146 mst_trips mt,
5147 mst_trip_stops mts
5148 WHERE mdl.plan_id = md.plan_id
5149 AND mts.plan_Id = mdl.plan_id
5150 AND mts.stop_id = mdl.pick_up_stop_id
5151 AND mts.stop_location_id = p_location_id
5152 AND mdl.plan_id = mt.plan_id
5153 AND mdl.trip_id = mt.trip_id);
5154
5155 l_total_Pieces_tmp := NVL(l_total_Pieces,0);
5156 l_total_Pieces := 0;
5157
5158 SELECT SUM(md.number_of_pieces)
5159 INTO l_total_Pieces
5160 FROM mst_deliveries md
5161 WHERE md.plan_id = p_plan_id
5162 AND md.delivery_id IN
5163 (SELECT mdl.delivery_id
5164 FROM mst_delivery_legs mdl,
5165 mst_trips mt,
5166 mst_trip_stops mts
5167 WHERE mdl.plan_id = md.plan_id
5168 AND mts.plan_Id = mdl.plan_id
5169 AND mts.stop_id = mdl.drop_off_stop_id
5170 AND mts.stop_location_id = p_location_id
5171 AND mdl.plan_id = mt.plan_id
5172 AND mdl.trip_id = mt.trip_id);
5173 l_total_Pieces := l_total_Pieces_tmp + NVL(l_total_Pieces,0);
5174 END IF;
5175 l_total_Pieces := NVL(l_total_Pieces, 0);
5176 RETURN l_total_Pieces;
5177 EXCEPTION
5178 WHEN OTHERS THEN
5179 RETURN 0;
5180 END get_tot_Pieces_carr_facility;
5181
5182 -- the implementation logic has been changed to aggregate on TP orders instead of raw orders
5183 FUNCTION get_tot_Orders_carr_facility(p_plan_id IN NUMBER,
5184 p_carrier_id IN NUMBER,
5185 p_fac_location_id IN NUMBER,
5186 p_activity_type IN VARCHAR2,
5187 p_mode IN VARCHAR2
5188 )
5189 RETURN NUMBER IS
5190
5191 l_total_orders NUMBER := 0;
5192 l_total_orders_tmp NUMBER := 0;
5193 BEGIN
5194
5195 if p_mode is not null THEN
5196 -- ---------------------------------------------------
5197 -- As per bug#3546059 and 3546163, we need to consider
5198 -- all trips touching the specific carrier facility.
5199 -- ---------------------------------------------------
5200 -- ------------------------------------------ -
5201 -- As per bug#3244044, we need to consider -
5202 -- Distinct orders instead of raw orders. -
5203 -- ------------------------------------------ -
5204 -- -------------------------------------
5208 -- for unload and load.
5205 -- 28-jUN-04 - Per bug#3713507,
5206 -- we need to double count KPIs
5207 -- - weight/Cube/pallets/Pieces/Orders
5209 -- -------------------------------------
5210 /*
5211 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5212 INTO l_total_orders
5213 FROM MST_DELIVERY_DETAILS MDD,
5214 MST_DELIVERIES MD,
5215 MST_DELIVERY_ASSIGNMENTS MDA
5216 WHERE MD.PLAN_ID = MDA.PLAN_ID
5217 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5218 AND MD.DELIVERY_ID IN
5219 (SELECT MDL.DELIVERY_ID
5220 FROM MST_TRIPS T,
5221 MST_TRIP_STOPS TS,
5222 MST_DELIVERY_LEGS MDL
5223 WHERE MDL.PLAN_ID = MD.PLAN_ID
5224 AND TS.PLAN_ID = MDL.PLAN_ID
5225 AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
5226 OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
5227 AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
5228 AND TS.PLAN_ID = T.PLAN_ID
5229 AND TS.TRIP_ID = T.TRIP_ID
5230 --AND T.CARRIER_ID = P_CARRIER_ID
5231 AND T.MODE_OF_TRANSPORT = P_MODE)
5232 AND MDA.PLAN_ID = MDD.PLAN_ID
5233 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
5234 AND MD.PLAN_ID = P_PLAN_ID;
5235 */
5236 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5237 INTO l_total_orders
5238 FROM MST_DELIVERY_DETAILS MDD,
5239 MST_DELIVERIES MD,
5240 MST_DELIVERY_ASSIGNMENTS MDA
5241 WHERE MD.PLAN_ID = MDA.PLAN_ID
5242 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5243 AND MD.DELIVERY_ID IN
5244 (SELECT MDL.DELIVERY_ID
5245 FROM MST_TRIPS T,
5246 MST_TRIP_STOPS TS,
5247 MST_DELIVERY_LEGS MDL
5248 WHERE MDL.PLAN_ID = MD.PLAN_ID
5249 AND TS.PLAN_ID = MDL.PLAN_ID
5250 AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
5251 AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
5252 AND TS.PLAN_ID = T.PLAN_ID
5253 AND TS.TRIP_ID = T.TRIP_ID
5254 AND T.MODE_OF_TRANSPORT = P_MODE)
5255 AND MDA.PLAN_ID = MDD.PLAN_ID
5256 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
5257 AND MD.PLAN_ID = P_PLAN_ID;
5258
5259 l_total_orders_tmp := NVL(l_total_orders,0);
5260 l_total_orders := 0;
5261
5262 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5263 INTO l_total_orders
5264 FROM MST_DELIVERY_DETAILS MDD,
5265 MST_DELIVERIES MD,
5266 MST_DELIVERY_ASSIGNMENTS MDA
5267 WHERE MD.PLAN_ID = MDA.PLAN_ID
5268 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5269 AND MD.DELIVERY_ID IN
5270 (SELECT MDL.DELIVERY_ID
5271 FROM MST_TRIPS T,
5272 MST_TRIP_STOPS TS,
5273 MST_DELIVERY_LEGS MDL
5274 WHERE MDL.PLAN_ID = MD.PLAN_ID
5275 AND TS.PLAN_ID = MDL.PLAN_ID
5276 AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
5277 AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
5278 AND TS.PLAN_ID = T.PLAN_ID
5279 AND TS.TRIP_ID = T.TRIP_ID
5280 AND T.MODE_OF_TRANSPORT = P_MODE)
5281 AND MDA.PLAN_ID = MDD.PLAN_ID
5282 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
5283 AND MD.PLAN_ID = P_PLAN_ID;
5284
5285 l_total_orders := l_total_orders_tmp + NVL(l_total_orders,0);
5286 ELSIF p_activity_type IS NULL AND p_mode IS NULL THEN
5287 -- ------------------------------------------------------------------
5288 -- Also, as per bug#3546059 and 3546163, we need to double count KPIs
5289 -- - weight/Cube/pallets/Pieces/Orders for unload and load.
5290 -- ------------------------------------------------------------------
5291 -- ------------------------------------------ -
5292 -- As per bug#3244044, we need to consider -
5293 -- Distinct orders instead of raw orders. -
5294 -- ------------------------------------------ -
5295 /*******
5296 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5297 INTO l_total_orders
5298 FROM MST_DELIVERY_DETAILS MDD,
5299 MST_DELIVERIES MD,
5300 MST_DELIVERY_ASSIGNMENTS MDA
5301 WHERE MD.PLAN_ID = MDA.PLAN_ID
5302 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5303 AND MD.DELIVERY_ID IN
5304 (SELECT MDL.DELIVERY_ID
5305 FROM MST_TRIPS T,
5306 MST_TRIP_STOPS TS,
5307 MST_DELIVERY_LEGS MDL
5308 WHERE MDL.PLAN_ID = MD.PLAN_ID
5309 AND TS.PLAN_ID = MDL.PLAN_ID
5310 AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
5311 OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
5312 AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
5313 AND TS.PLAN_ID = T.PLAN_ID
5314 AND TS.TRIP_ID = T.TRIP_ID
5315 AND T.CARRIER_ID = P_CARRIER_ID)
5316 AND MDA.PLAN_ID = MDD.PLAN_ID
5317 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
5318 AND MD.PLAN_ID = P_PLAN_ID;
5319 ***********/
5320 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5321 INTO l_total_orders
5322 FROM mst_delivery_details mdd,
5323 mst_deliveries md,
5324 mst_delivery_assignments mda
5325 WHERE md.plan_id = p_plan_id
5326 AND md.plan_id = mda.plan_id
5327 AND md.delivery_id = mda.delivery_id
5328 AND mda.plan_id = mdd.plan_id
5332 FROM mst_delivery_legs mdl,
5329 AND mda.delivery_detail_id = mdd.delivery_detail_id
5330 AND md.delivery_id IN
5331 ( SELECT mdl.delivery_id
5333 mst_trip_stops mts
5334 WHERE mdl.plan_id = md.plan_id
5335 AND mdl.plan_id = mts.plan_id
5336 AND mdl.pick_up_stop_id = mts.stop_id
5337 AND mts.stop_location_id = p_fac_location_id);
5338
5339 l_total_orders_tmp := NVL(l_total_orders,0);
5340 l_total_orders := 0;
5341
5342 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5343 INTO l_total_orders
5344 FROM mst_delivery_details mdd,
5345 mst_deliveries md,
5346 mst_delivery_assignments mda
5347 WHERE md.plan_id = p_plan_id
5348 AND md.plan_id = mda.plan_id
5349 AND md.delivery_id = mda.delivery_id
5350 AND mda.plan_id = mdd.plan_id
5351 AND mda.delivery_detail_id = mdd.delivery_detail_id
5352 AND md.delivery_id IN
5353 ( SELECT mdl.delivery_id
5354 FROM mst_delivery_legs mdl,
5355 mst_trip_stops mts
5356 WHERE mdl.plan_id = md.plan_id
5357 AND mdl.plan_id = mts.plan_id
5358 AND mdl.drop_off_stop_id = mts.stop_id
5359 AND mts.stop_location_id = p_fac_location_id);
5360
5361 l_total_orders := l_total_orders_tmp + NVL(l_total_orders,0);
5362 elsif p_activity_type = 'L' THEN
5363 -- ------------------------------------------ -
5364 -- As per bug#3244044, we need to consider -
5365 -- Distinct orders instead of raw orders. -
5366 -- ------------------------------------------ -
5367 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5368 INTO l_total_orders
5369 FROM MST_DELIVERY_DETAILS MDD,
5370 MST_DELIVERIES MD,
5371 MST_DELIVERY_ASSIGNMENTS MDA
5372 WHERE MD.PLAN_ID = MDA.PLAN_ID
5373 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5374 AND MD.DELIVERY_ID IN
5375 (SELECT MDL.DELIVERY_ID
5376 --FROM MST_TRIPS T,
5377 FROM MST_TRIP_STOPS TS,
5378 MST_DELIVERY_LEGS MDL
5379 WHERE MDL.PLAN_ID = MD.PLAN_ID
5380 AND TS.PLAN_ID = MDL.PLAN_ID
5381 AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
5382 AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID)
5383 --AND TS.PLAN_ID = T.PLAN_ID
5384 --AND TS.TRIP_ID = T.TRIP_ID
5385 --AND T.CARRIER_ID = P_CARRIER_ID)
5386 AND MDA.PLAN_ID = MDD.PLAN_ID
5387 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
5388 AND MD.PLAN_ID = P_PLAN_ID;
5389 elsif p_activity_type = 'U' THEN
5390 -- ------------------------------------------ -
5391 -- As per bug#3244044, we need to consider -
5392 -- Distinct orders instead of raw orders. -
5393 -- ------------------------------------------ -
5394 SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5395 INTO l_total_orders
5396 FROM MST_DELIVERY_DETAILS MDD,
5397 MST_DELIVERIES MD,
5398 MST_DELIVERY_ASSIGNMENTS MDA
5399 WHERE MD.PLAN_ID = MDA.PLAN_ID
5400 AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5401 AND MD.DELIVERY_ID IN
5402 (SELECT MDL.DELIVERY_ID
5403 --FROM MST_TRIPS T,
5404 FROM MST_TRIP_STOPS TS,
5405 MST_DELIVERY_LEGS MDL
5406 WHERE MDL.PLAN_ID = MD.PLAN_ID
5407 AND TS.PLAN_ID = MDL.PLAN_ID
5408 AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
5409 AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID)
5410 --AND TS.PLAN_ID = T.PLAN_ID
5411 --AND TS.TRIP_ID = T.TRIP_ID
5412 --AND T.CARRIER_ID = P_CARRIER_ID)
5413 AND MDA.PLAN_ID = MDD.PLAN_ID
5414 AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
5415 AND MD.PLAN_ID = P_PLAN_ID;
5416 end if;
5417 l_total_orders := NVL(l_total_orders,0);
5418 RETURN l_total_orders;
5419 EXCEPTION
5420 WHEN OTHERS THEN
5421 RETURN 0;
5422
5423 END get_tot_Orders_carr_facility;
5424
5425 FUNCTION get_tot_trips_carr_Facility(p_plan_id IN NUMBER,
5426 p_carrier_id IN NUMBER,
5427 P_LOCATION_ID IN NUMBER,
5428 p_location_type IN VARCHAR2,
5429 p_mode_of_transport IN VARCHAR2)
5430 RETURN NUMBER IS
5431 l_total_trips NUMBER;
5432 l_total_trips_tmp NUMBER;
5433 BEGIN
5434 -- ---------------------------------------------------
5435 -- As per bug#3546059 and 3546163, we need to consider
5436 -- all trips touching the specific carrier facility.
5437 -- ---------------------------------------------------
5438 IF p_location_type = 'U' THEN
5439 -- ---------------------------------------------
5440 -- Need to show all trips (Unload) and
5441 -- by a given mode of transport by the specified
5442 -- carrier facility.
5443 -- ---------------------------------------------
5444 SELECT COUNT(mt.trip_id)
5445 INTO l_total_trips
5446 FROM mst_trips mt
5447 WHERE mt.plan_id = p_plan_id
5448 AND mt.trip_id IN
5449 (SELECT mdl.trip_id
5450 FROM mst_delivery_legs mdl,
5451 mst_trip_stops mts
5452 WHERE mdl.plan_id = mt.plan_id
5453 AND mts.plan_id = mdl.plan_id
5454 AND mts.stop_id = mdl.drop_off_stop_id
5458 ELSIF p_location_type = 'L' THEN
5455 AND mts.stop_location_id = P_location_id)
5456 --AND mt.carrier_id = p_carrier_id
5457 AND mt.mode_of_transport = p_mode_of_transport;
5459 -- ---------------------------------------------
5460 -- Need to show all trips (load) and
5461 -- by a given mode of transport by the specified
5462 -- carrier facility.
5463 -- ---------------------------------------------
5464 SELECT COUNT(mt.trip_id)
5465 INTO l_total_trips
5466 FROM mst_trips mt
5467 WHERE mt.plan_id = p_plan_id
5468 AND mt.trip_id IN
5469 (SELECT mdl.trip_id
5470 FROM mst_deliveries md,
5471 mst_delivery_legs mdl,
5472 mst_trip_stops mts
5473 WHERE mdl.plan_id = mt.plan_id
5474 AND mts.plan_id = mdl.plan_id
5475 AND mts.stop_id = mdl.pick_up_stop_id
5476 AND mts.stop_location_id = p_location_Id)
5477 --AND mt.carrier_id = p_carrier_id
5478 AND mt.mode_of_transport = p_mode_of_transport;
5479 ELSIF p_mode_of_transport IS NOT NULL THEN
5480 -- ------------------------------------------
5481 -- Need to show all trips (Load/Unload) by a
5482 -- given mode of transport, by the specified
5483 -- carrier facility.
5484 -- ------------------------------------------
5485 SELECT COUNT(mt.trip_id)
5486 INTO l_total_trips
5487 FROM mst_trips mt
5488 WHERE mt.plan_id = p_plan_id
5489 AND mt.trip_id IN
5490 (SELECT mdl.trip_id
5491 FROM mst_delivery_legs mdl,
5492 mst_trip_stops mts
5493 WHERE mdl.plan_id = mt.plan_id
5494 AND mts.plan_id = mdl.plan_id
5495 AND ( mts.stop_id = mdl.pick_up_stop_id
5496 OR mts.stop_id = mdl.drop_off_stop_id)
5497 AND mts.stop_location_id = P_location_id)
5498 --AND mt.carrier_id = p_carrier_id
5499 AND mt.mode_of_transport = p_mode_of_transport;
5500 ELSIF p_mode_of_transport IS NULL THEN
5501 -- ------------------------------------------
5502 -- Need to show all trips (Load/Unload) and
5503 -- by all modes of transport by the specified
5504 -- carrier facility.
5505 -- ------------------------------------------
5506 -- ------------------------------------------------------------------
5507 -- Also, as per bug#3546059 and 3546163, we need to double count KPIs
5508 -- - weight/Cube/pallets/Pieces/Orders for unload and load.
5509 -- ------------------------------------------------------------------
5510 /***********************
5511 SELECT COUNT(mt.trip_id)
5512 INTO l_total_trips
5513 FROM mst_trips mt
5514 WHERE mt.plan_id = p_plan_id
5515 AND mt.trip_id IN
5516 (SELECT mdl.trip_id
5517 FROM mst_delivery_legs mdl,
5518 mst_trip_stops mts
5519 WHERE mdl.plan_id = mt.plan_id
5520 AND mts.plan_id = mdl.plan_id
5521 AND ( mts.stop_id = mdl.pick_up_stop_id
5522 OR mts.stop_id = mdl.drop_off_stop_id)
5523 AND mts.stop_location_id = P_location_id)
5524 AND mt.carrier_id = p_carrier_id;
5525 *************************/
5526 SELECT COUNT(mt.trip_id)
5527 INTO l_total_trips
5528 FROM mst_trips mt
5529 WHERE mt.plan_id = p_plan_id
5530 AND mt.trip_id IN
5531 (SELECT mdl.trip_id
5532 FROM mst_delivery_legs mdl,
5533 mst_trip_stops mts
5534 WHERE mdl.plan_id = mt.plan_id
5535 AND mts.plan_id = mdl.plan_id
5536 AND mts.stop_id = mdl.pick_up_stop_id
5537 AND mts.stop_location_id = P_location_id);
5538
5539 l_total_trips_tmp := NVL(l_total_trips,0);
5540 l_total_trips := 0;
5541
5542 SELECT COUNT(mt.trip_id)
5543 INTO l_total_trips
5544 FROM mst_trips mt
5545 WHERE mt.plan_id = p_plan_id
5546 AND mt.trip_id IN
5547 (SELECT mdl.trip_id
5548 FROM mst_delivery_legs mdl,
5549 mst_trip_stops mts
5550 WHERE mdl.plan_id = mt.plan_id
5551 AND mts.plan_id = mdl.plan_id
5552 AND mts.stop_id = mdl.drop_off_stop_id
5553 AND mts.stop_location_id = P_location_id);
5554
5555 l_total_trips := l_total_trips_tmp + NVL(l_total_trips,0);
5556 END IF;
5557 RETURN l_total_trips;
5558 EXCEPTION
5559 WHEN OTHERS THEN
5560 RETURN 0;
5561 END get_tot_trips_carr_Facility;
5562
5563 FUNCTION get_direct_tls_carr_facility
5564 (p_plan_id IN NUMBER,
5565 p_carrier_id IN NUMBER,
5566 p_location_id IN NUMBER,
5567 p_Location_type IN VARCHAR2,
5568 p_mode_of_transport IN VARCHAR2)
5569 RETURN NUMBER IS
5570
5571 l_total_trips NUMBER;
5572
5573 BEGIN
5574 -- Used in Carrier Facility Details UI
5575 -- ---------------------------------------------------
5576 -- As per bug#3546059 and 3546163, we need to consider
5580 -- ---------------------------------------------------- --
5577 -- all trips touching the specific carrier facility.
5578 -- ---------------------------------------------------
5579 IF p_mode_of_transport =DTL THEN -- Direct TLs
5581 -- For a given carrier facility and mode of transport, --
5582 -- consider those delivery legs involved in that trip. --
5583 -- Verify for pickup(LOAD)/drop off(UNLOAD) locations --
5584 -- of these dilivery legs to be carrier facility. Also, --
5585 -- verify if these deliveries been assigned to one or --
5586 -- more delivery legs. Check number of stops in each --
5587 -- trip to be 2 ( Direct )Get count of such trips. --
5588 -- ---------------------------------------------------- --
5589 IF p_Location_type = 'L' THEN
5590
5591 SELECT COUNT(mt.trip_id)
5592 INTO l_total_trips
5593 FROM mst_trips mt
5594 WHERE mt.plan_id = p_plan_id
5595 AND mt.trip_id IN
5596 (SELECT mdl.trip_id
5597 FROM mst_delivery_legs mdl,
5598 mst_trip_stops mts
5599 WHERE mdl.plan_id = mt.plan_id
5600 AND mts.plan_id = mdl.plan_id
5601 AND mts.stop_id = mdl.pick_up_stop_Id
5602 AND mts.stop_location_id = p_location_id)
5603 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5604 --AND mt.carrier_id = p_carrier_id
5605 AND EXISTS (SELECT mts1.trip_id
5606 FROM mst_trip_stops mts1
5607 WHERE mts1.plan_id = mt.plan_id
5608 AND mts1.trip_Id = mt.trip_ID
5609 HAVING COUNT(mts1.stop_id) =2
5610 GROUP BY mts1.trip_id);
5611 ELSIF p_Location_type = 'U' THEN
5612
5613 SELECT COUNT(mt.trip_id)
5614 INTO l_total_trips
5615 FROM mst_trips mt
5616 WHERE mt.plan_id = p_plan_id
5617 AND mt.trip_id IN
5618 (SELECT mdl.trip_id
5619 FROM mst_delivery_legs mdl,
5620 mst_trip_stops mts
5621 WHERE mdl.plan_id = mt.plan_id
5622 AND mts.plan_id = mdl.plan_id
5623 AND mts.stop_id = mdl.drop_off_stop_id
5624 AND mts.stop_location_id = p_location_id)
5625 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5626 --AND mt.carrier_id = p_carrier_id
5627 AND EXISTS (SELECT mts1.trip_id
5628 FROM mst_trip_stops mts1
5629 WHERE mts1.plan_id = mt.plan_id
5630 AND mts1.trip_Id = mt.trip_ID
5631 HAVING COUNT(mts1.stop_id) =2
5632 GROUP BY mts1.trip_id);
5633
5634 END IF;
5635 ELSE -- Multi-stop TLs
5636 -- ---------------------------------------------------- --
5637 -- For a given carrier facility and mode of transport, --
5638 -- consider those delivery legs involved in that trip. --
5639 -- Verify for pickup(LOAD)/drop off(UNLOAD) locations --
5640 -- of these dilivery legs to be carrier facility. Also, --
5641 -- verify if these deliveries been assigned to one or --
5642 -- more delivery legs. Check number of stops in each --
5643 -- trip to be > 2 (Multi stop)Get count of such trips. --
5644 -- ---------------------------------------------------- --
5645 IF p_Location_type = 'L' THEN
5646 SELECT COUNT(mt.trip_id)
5647 INTO l_total_trips
5648 FROM mst_trips mt
5649 WHERE mt.plan_id = p_plan_id
5650 AND mt.trip_id IN
5651 (SELECT mdl.trip_id
5652 FROM mst_delivery_legs mdl,
5653 mst_trip_stops mts
5654 WHERE mdl.plan_id = mt.plan_id
5655 AND mts.plan_id = mdl.plan_id
5656 AND mts.stop_id = mdl.pick_up_stop_Id
5657 AND mts.stop_location_id = p_location_id)
5658 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5659 --AND mt.carrier_id = p_carrier_id
5660 AND EXISTS (SELECT mts1.trip_id
5661 FROM mst_trip_stops mts1
5662 WHERE mts1.plan_id = mt.plan_id
5663 AND mts1.trip_Id = mt.trip_ID
5664 HAVING COUNT(mts1.stop_id) > 2
5665 GROUP BY mts1.trip_id);
5666 ELSIF p_Location_type = 'U' THEN
5667
5668 SELECT COUNT(mt.trip_id)
5669 INTO l_total_trips
5670 FROM mst_trips mt
5671 WHERE mt.plan_id = p_plan_id
5672 AND mt.trip_id IN
5673 (SELECT mdl.trip_id
5674 FROM mst_delivery_legs mdl,
5675 mst_trip_stops mts
5676 WHERE mdl.plan_id = mt.plan_id
5677 AND mts.plan_id = mdl.plan_id
5678 AND mts.stop_id = mdl.drop_off_stop_Id
5679 AND mts.stop_location_id = p_location_id)
5683 FROM mst_trip_stops mts1
5680 AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5681 --AND mt.carrier_id = p_carrier_id
5682 AND EXISTS (SELECT mts1.trip_id
5684 WHERE mts1.plan_id = mt.plan_id
5685 AND mts1.trip_Id = mt.trip_ID
5686 HAVING COUNT(mts1.stop_id) > 2
5687 GROUP BY mts1.trip_id);
5688 END IF;
5689 END IF;
5690
5691 RETURN l_total_trips;
5692 EXCEPTION
5693 WHEN OTHERS THEN
5694 RETURN 0;
5695 END get_direct_tls_carr_facility;
5696
5697 FUNCTION get_total_cost_carr_fac(p_plan_id IN NUMBER,
5698 p_carrier_id IN NUMBER,
5699 p_location_id IN NUMBER,
5700 p_mode_of_transport IN VARCHAR2)
5701 RETURN NUMBER IS
5702 -- ---------------------------------------------
5703 -- Need to show cost of all trips (load/Unload)
5704 -- by a given mode of transport by the specified
5705 -- carrier facility.
5706 -- ---------------------------------------------
5707 CURSOR cur_total_cost_by_mode IS
5708 SELECT NVL(SUM(NVL(mdl.allocated_transport_cost,0) +
5709 DECODE(mts.stop_id,
5710 mdl.pick_up_stop_id,
5711 NVL(mdl.allocated_fac_loading_cost,0),
5712 mdl.drop_off_stop_id,
5713 NVL(mdl.allocated_fac_unloading_cost,0),0)+
5714 DECODE(mts.stop_id,
5715 mdl.pick_up_stop_id,
5716 NVL(mdl.allocated_fac_shp_hand_cost,0),
5717 mdl.drop_off_stop_id,
5718 NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
5719 FROM mst_delivery_legs mdl,
5720 mst_trip_stops mts,
5721 mst_trips mt
5722 WHERE mdl.plan_id = p_plan_id
5723 AND mt.plan_id = mdl.plan_id
5724 AND mt.trip_id = mdl.trip_id
5725 AND mt.plan_id = mts.plan_Id
5726 AND mt.trip_id = mts.trip_id
5727 AND mt.mode_of_transport = p_mode_of_transport
5728 AND mts.stop_location_id = p_location_id;
5729
5730 -- ---------------------------------------------
5731 -- Need to show cost of all trips (load/Unload)
5732 -- by a all modes of transport by the specified
5733 -- carrier facility.
5734 -- ---------------------------------------------
5735 CURSOR cur_total_cost IS
5736 SELECT NVL(SUM(NVL(mdl.allocated_transport_cost,0) +
5737 DECODE(mts.stop_id,
5738 mdl.pick_up_stop_id,
5739 NVL(mdl.allocated_fac_loading_cost,0),
5740 mdl.drop_off_stop_id,
5741 NVL(mdl.allocated_fac_unloading_cost,0),0) +
5742 DECODE(mts.stop_id,
5743 mdl.pick_up_stop_id,
5744 NVL(mdl.allocated_fac_shp_hand_cost,0),
5745 mdl.drop_off_stop_id,
5746 NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
5747 FROM mst_delivery_legs mdl,
5748 mst_trip_stops mts,
5749 mst_trips mt
5750 WHERE mdl.plan_id = p_plan_id
5751 AND mt.plan_id = mdl.plan_id
5752 AND mt.trip_id = mdl.trip_id
5753 AND mt.plan_id = mts.plan_Id
5754 AND mt.trip_id = mts.trip_id
5755 AND mts.stop_location_id = p_location_id;
5756
5757 l_total_cost NUMBER :=0;
5758
5759 BEGIN
5760
5761 IF p_mode_of_transport IS NOT NULL THEN
5762 OPEN cur_total_cost_by_mode;
5763 FETCH cur_total_cost_by_mode INTO l_total_cost;
5764 CLOSE cur_total_cost_by_mode;
5765 ELSE
5766 OPEN cur_total_cost;
5767 FETCH cur_total_cost INTO l_total_cost;
5768 CLOSE cur_total_cost;
5769 END IF;
5770 RETURN l_total_cost;
5771
5772 EXCEPTION
5773 WHEN OTHERS THEN
5774 RETURN 0;
5775 END get_total_cost_carr_fac;
5776 /* End of Carrier Details/Carrier Facility Details functions */
5777
5778
5779 -- Used by view definitions of Delivery Line grids:
5780 -- 1. Call this function when source_code = OE for the Destination Company Name:
5781 -- p_location_id = MST_DELIVERY_DETAILS.ship_to_location_id
5782 -- p_customer_id = MST_DELIVERY_DETAILS.customer_id
5783 -- 2. Call this function when source_code = OE for the Origin Company Name:
5784 -- p_location_id = MST_DELIVERY_DETAILS.ship_from_location_id
5785 -- p_customer_id = null
5786
5787 function get_owner_name_for_del_line(p_location_id IN number, p_customer_id IN number)
5788 return varchar2 is
5789
5790 l_location_source_code varchar2(30);
5791 l_owner_name varchar2(360);
5792
5793 begin
5794
5795 select location_source_code
5796 into l_location_source_code
5797 from wsh_locations loc
5798 where loc.wsh_location_id = p_location_id;
5799 -- including both customer and carrier, don't care it's customer/carrier
5800 if l_location_source_code = 'HZ' then
5801 select hzp.party_name
5802 into l_owner_name
5803 from hz_cust_accounts hzc,
5804 hz_parties hzp
5805 where hzc.cust_account_id = p_customer_id
5806 and hzc.party_id = hzp.party_id;
5807
5808 -- p_party_id is orgnization_id from mst_delivery_details
5809 elsif l_location_source_code = 'HR' then
5810 l_owner_name := fnd_profile.value('MST_COMPANY_NAME');
5811 end if;
5812 return l_owner_name;
5813 end;
5814
5818 function get_owner_name_for_del_leg(p_location_id IN number,
5815 -- Used by view definitions of Delivery Leg grids:
5816 -- Call this function to get both the Origin Company Name and the Destination Company Name
5817
5819 p_plan_id IN NUMBER,
5820 p_delivery_id IN number)
5821 return varchar2 is
5822
5823 l_location_source_code varchar2(30);
5824 l_owner_name varchar2(360) := null;
5825
5826 -- SQL repository issues as on 25-05-04:
5827 -- Added new parameter plan_id
5828 -- added filter for plan_id
5829 cursor customer_account is
5830 select party_name
5831 from hz_locations hzl,
5832 hz_party_sites hzs,
5833 hz_cust_acct_sites_all hzcas,
5834 hz_cust_accounts hzca,
5835 hz_parties hzp,
5836 mst_deliveries md
5837 where md.plan_id = p_plan_id
5838 AND md.delivery_id = p_delivery_id
5839 and hzl.location_id = p_location_id
5840 and hzl.location_id = hzs.location_id
5841 and hzs.party_site_id = hzcas.party_site_id
5842 and hzcas.cust_account_id = hzca.cust_account_id
5843 and hzcas.cust_account_id = md.customer_id
5844 and hzca.party_id = hzp.party_id;
5845
5846 cursor carriers is
5847 select car.freight_code
5848 from hz_locations hzl,
5849 hz_party_sites hzs,
5850 hz_parties hzp,
5851 wsh_carriers car
5852 where hzl.location_id = p_location_id
5853 and hzl.location_id = hzs.location_id
5854 and hzs.party_id = hzp.party_id
5855 and hzp.party_id = car.carrier_id;
5856
5857 begin
5858
5859 select location_source_code
5860 into l_location_source_code
5861 from wsh_locations loc
5862 where loc.wsh_location_id = p_location_id;
5863 -- including both customer and carrier, don't care it's customer/carrier
5864 if l_location_source_code = 'HZ' then
5865 fetch customer_account into l_owner_name;
5866 if l_owner_name = null then
5867 fetch carriers into l_owner_name;
5868 end if;
5869
5870 elsif l_location_source_code = 'HR' then
5871 l_owner_name := fnd_profile.value('MST_COMPANY_NAME');
5872 end if;
5873 return l_owner_name;
5874 end;
5875
5876 -- Used by view definitions of Trips/Loads/Stops/Trip-legs grids:
5877 -- Call this function to get both the Origin Company Name and the Destination Company Name
5878
5879 function get_owner_name_for_trip(p_location_id IN number)
5880 return varchar2 is
5881
5882 l_location_source_code varchar2(30);
5883 l_owner_name varchar2(360) := null;
5884 l_next_owner_name varchar2(360) := null;
5885 -- SQL repository issues as on 25-05-04:
5886 -- Rewritten sql to avoid distinct clause
5887 CURSOR customer_account IS
5888 SELECT hzp.party_name
5889 FROM hz_parties hzp
5890 WHERE hzp.party_id IN (SELECT hzca.party_id
5891 FROM hz_locations hzl , hz_party_sites hzs,
5892 hz_cust_acct_sites_all hzcas, hz_cust_accounts hzca
5893 WHERE hzl.location_id = p_location_id
5894 AND hzl.location_id = hzs.location_id
5895 AND hzs.party_site_id = hzcas.party_site_id
5896 AND hzcas.cust_account_id = hzca.cust_account_id);
5897
5898 /*
5899 cursor customer_account is
5900 select distinct party_name
5901 from hz_locations hzl,
5902 hz_party_sites hzs,
5903 hz_cust_acct_sites_all hzcas,
5904 hz_cust_accounts hzca,
5905 hz_parties hzp
5906 where hzl.location_id = p_location_id
5907 and hzl.location_id = hzs.location_id
5908 and hzs.party_site_id = hzcas.party_site_id
5909 and hzcas.cust_account_id = hzca.cust_account_id
5910 and hzca.party_id = hzp.party_id;*/
5911
5912 cursor carriers is
5913 select car.freight_code
5914 from hz_locations hzl,
5915 hz_party_sites hzs,
5916 hz_parties hzp,
5917 wsh_carriers car
5918 where hzl.location_id = p_location_id
5919 and hzl.location_id = hzs.location_id
5920 and hzs.party_id = hzp.party_id
5921 and hzp.party_id = car.carrier_id;
5922
5923 begin
5924
5925 select location_source_code
5926 into l_location_source_code
5927 from wsh_locations loc
5928 where loc.wsh_location_id = p_location_id;
5929 -- including both customer and carrier, don't care it's customer/carrier
5930 if l_location_source_code = 'HZ' then
5931 fetch customer_account into l_owner_name;
5932 if l_owner_name = null then
5933 fetch carriers into l_owner_name;
5934 else
5935 fetch customer_account into l_next_owner_name;
5936 if l_next_owner_name is not null then
5937 l_owner_name := 'MULTIPLE'; -- should be translatable, hardcode for now
5938 end if;
5939 end if;
5940
5941 elsif l_location_source_code = 'HR' then
5942 l_owner_name := fnd_profile.value('MST_COMPANY_NAME');
5943 end if;
5944 return l_owner_name;
5945 end;
5946
5947
5948 function get_total_tls_in_cms(p_plan_id in number)
5949 return number is
5950 l_count number;
5951 begin
5952 select count(mt.trip_id)
5953 into l_count
5954 from mst_trips mt
5955 where plan_id = p_plan_id
5956 and mt.continuous_move_id in (select cm.continuous_move_id
5957 from mst_cm_trips cm
5958 where cm.plan_id = p_plan_id);
5959 return l_count;
5960 end;
5961
5962
5963 function get_total_excp_in_plan(p_plan_id in number) --get_total_excp
5964 return number is
5965 l_count number;
5966 begin
5967 select sum(exception_count)
5968 into l_count
5969 from mst_exceptions
5973
5970 where plan_id = p_plan_id;
5971 return l_count;
5972 end;
5974 function get_total_excp_in_trip(p_plan_id in number, p_trip_id in number) --any_exceptions
5975 return number is
5976 l_count number;
5977 begin
5978 select count(*)
5979 into l_count
5980 from mst_exception_details
5981 where plan_id = p_plan_id
5982 and (trip_id1 = p_trip_id
5983 or trip_id2 = p_trip_id);
5984 return l_count;
5985 end;
5986
5987 function get_total_trip_weight(p_plan_id in number,
5988 p_trip_id in number)
5989 return number is
5990 l_total_weight number;
5991 begin
5992 select NVL(sum(nvl(md.gross_weight, 0)),0)
5993 into l_total_weight
5994 from mst_deliveries md
5995 where md.plan_id = p_plan_id
5996 and exists (select 1
5997 from mst_delivery_legs mdl,
5998 mst_trip_stops mts,
5999 mst_trips mt
6000 where mdl.delivery_id = md.delivery_id
6001 and mdl.pick_up_stop_id = mts.stop_id
6002 and mt.trip_id = p_trip_id
6003 and mts.trip_id = mt.trip_id
6004 and mt.plan_id = p_plan_id
6005 and mts.plan_id = p_plan_id
6006 and mdl.plan_id = p_plan_id);
6007 return l_total_weight;
6008 end;
6009
6010 function get_total_trip_volume(p_plan_id in number,
6011 p_trip_id in number)
6012 return number is
6013 l_total_volume number;
6014 begin
6015 select NVL(sum(md.volume),0)
6016 into l_total_volume
6017 from mst_deliveries md
6018 where md.plan_id = p_plan_id
6019 and exists (select 1
6020 from mst_delivery_legs mdl,
6021 mst_trip_stops mts,
6022 mst_trips mt
6023 where mdl.delivery_id = md.delivery_id
6024 and mdl.pick_up_stop_id = mts.stop_id
6025 and mt.trip_id = p_trip_id
6026 and mts.trip_id = mt.trip_id
6027 and mt.plan_id = p_plan_id
6028 and mts.plan_id = p_plan_id
6029 and mdl.plan_id = p_plan_id);
6030 return l_total_volume;
6031 end;
6032
6033 function get_total_trip_pallets(p_plan_id in number,
6034 p_trip_id in number)
6035 return number is
6036 l_total_pallets number;
6037 begin
6038 select NVL(sum(nvl(md.number_of_pallets,0)), 0)
6039 into l_total_pallets
6040 from mst_deliveries md
6041 where md.plan_id = p_plan_id
6042 and exists (select 1
6043 from mst_delivery_legs mdl,
6044 mst_trip_stops mts,
6045 mst_trips mt
6046 where mdl.delivery_id = md.delivery_id
6047 and mdl.pick_up_stop_id = mts.stop_id
6048 and mt.trip_id = p_trip_id
6049 and mts.trip_id = mt.trip_id
6050 and mt.plan_id = p_plan_id
6051 and mts.plan_id = p_plan_id
6052 and mdl.plan_id = p_plan_id);
6053 return l_total_pallets;
6054 end;
6055
6056 function get_total_trip_pieces(p_plan_id in number,
6057 p_trip_id in number)
6058 return number is
6059 l_total_pieces number;
6060 begin
6061 select sum(nvl(md.number_of_pieces, 0))
6062 into l_total_pieces
6063 from mst_deliveries md
6064 where md.plan_id = p_plan_id
6065 and exists (select 1
6066 from mst_delivery_legs mdl,
6067 mst_trip_stops mts,
6068 mst_trips mt
6069 where mdl.delivery_id = md.delivery_id
6070 and mdl.pick_up_stop_id = mts.stop_id
6071 and mt.trip_id = p_trip_id
6072 and mts.trip_id = mt.trip_id
6073 and mt.plan_id = p_plan_id
6074 and mts.plan_id = p_plan_id
6075 and mdl.plan_id = p_plan_id);
6076 return l_total_pieces;
6077 end;
6078
6079 function get_total_direct_tls(p_plan_id in number)
6080 return number is
6081 l_count number;
6082 begin
6083 select count(mt.trip_id)
6084 into l_count
6085 from mst_trips mt
6086 where mt.mode_of_transport = 'TRUCK'
6087 and mt.plan_id = p_plan_id
6088 and mt.trip_id in (select mts.trip_id
6089 from mst_trip_stops mts
6090 where mt.trip_id = mts.trip_id
6091 and mt.plan_id = mts.plan_id
6092 having count(mts.trip_id) = 2
6093 group by mts.trip_id);
6094 return l_count;
6095 end;
6096
6097 function get_total_direct_mstop_tls(p_plan_id in number)
6098 return number is
6099 l_count number;
6100 begin
6101 select count(mt.trip_id)
6102 into l_count
6103 from mst_trips mt
6104 where mt.mode_of_transport = 'TRUCK'
6105 and mt.plan_id = p_plan_id
6106 and mt.trip_id in (select mts.trip_id
6107 from mst_trip_stops mts
6108 where mt.trip_id = mts.trip_id
6109 and mt.plan_id = mts.plan_id
6110 having count(mts.trip_id) > 2
6111 group by mts.trip_id);
6112 return l_count;
6113 end;
6114
6115
6119 begin
6116 function get_auto_release_trip_count (p_plan_id number, p_mode varchar2)
6117 return number is
6118 l_count number;
6120 select count('x')
6121 into l_count
6122 from mst_trips
6123 where plan_id = p_plan_id
6124 and auto_release_flag = 1
6125 and mode_of_transport = p_mode;
6126 return l_count;
6127 end get_auto_release_trip_count;
6128
6129 function get_released_trip_count (p_plan_id number, p_mode varchar2)
6130 return number is
6131 l_count number;
6132 begin
6133 select count('x')
6134 into l_count
6135 from mst_trips
6136 where plan_id = p_plan_id
6137 and release_status <> 4
6138 and release_date is not null
6139 and auto_release_flag <> 1
6140 and mode_of_transport = p_mode;
6141 return l_count;
6142 end get_released_trip_count;
6143
6144
6145 function get_release_failed_trip_count (p_plan_id number, p_mode varchar2)
6146 return number is
6147 l_count number;
6148 begin
6149 select count('x')
6150 into l_count
6151 from mst_trips
6152 where plan_id = p_plan_id
6153 and release_status = 4
6154 and release_date is not null
6155 and mode_of_transport = p_mode;
6156 return l_count;
6157 end get_release_failed_trip_count;
6158
6159 function get_flag_for_rel_trip_count (p_plan_id number, p_mode varchar2)
6160 return number is
6161 l_count number;
6162 begin
6163 select count('x')
6164 into l_count
6165 from mst_trips
6166 where plan_id = p_plan_id
6167 and selected_for_release = 1
6168 and mode_of_transport = p_mode;
6169 return l_count;
6170 end get_flag_for_rel_trip_count;
6171
6172 function get_not_rel_trip_count (p_plan_id number, p_mode varchar2)
6173 return number is
6174 l_count number;
6175 begin
6176 select count('x')
6177 into l_count
6178 from mst_trips
6179 where plan_id = p_plan_id
6180 and release_date is null
6181 and mode_of_transport = p_mode;
6182 return l_count;
6183 end get_not_rel_trip_count;
6184
6185 function get_auto_release_cm_count (p_plan_id number)
6186 return number is
6187 l_count number;
6188 begin
6189 select count('x')
6190 into l_count
6191 from mst_cm_trips
6192 where plan_id = p_plan_id
6193 and auto_release_flag = 1;
6194 return l_count;
6195 end get_auto_release_cm_count;
6196
6197 function get_released_cm_count (p_plan_id number)
6198 return number is
6199 l_count number;
6200 begin
6201 select count('x')
6202 into l_count
6203 from mst_cm_trips
6204 where plan_id = p_plan_id
6205 and release_status <> 4
6206 and release_date is not null
6207 and auto_release_flag <> 1;
6208 return l_count;
6209 end get_released_cm_count;
6210
6211
6212 function get_rel_failed_cm_count (p_plan_id number)
6213 return number is
6214 l_count number;
6215 begin
6216 select count('x')
6217 into l_count
6218 from mst_cm_trips
6219 where plan_id = p_plan_id
6220 and release_status = 4
6221 and release_date is not null;
6222 return l_count;
6223 end get_rel_failed_cm_count;
6224
6225 function get_flag_for_rel_cm_count (p_plan_id number)
6226 return number is
6227 l_count number;
6228 begin
6229 select count('x')
6230 into l_count
6231 from mst_cm_trips
6232 where plan_id = p_plan_id
6233 and selected_for_release = 1;
6234 return l_count;
6235 end get_flag_for_rel_cm_count;
6236
6237 function get_not_rel_cm_count (p_plan_id number)
6238 return number is
6239 l_count number;
6240 begin
6241 select count('x')
6242 into l_count
6243 from mst_cm_trips
6244 where plan_id = p_plan_id
6245 and release_date is null;
6246 return l_count;
6247 end get_not_rel_cm_count;
6248
6249 function get_total_excp_in_cm(p_plan_id in number, p_cm_id in number)
6250 return number is
6251 l_count number;
6252 begin
6253 select count(*)
6254 into l_count
6255 from mst_exception_details
6256 where plan_id = p_plan_id
6257 and continuous_move_id = p_cm_id;
6258 return l_count;
6259 end;
6260
6261 END MST_AGG_PKG;
[Home] [Help]