[Home] [Help]
PACKAGE BODY: APPS.MST_WB_UTIL
Source
1 PACKAGE BODY MST_WB_UTIL AS
2 /* $Header: MSTWUTLB.pls 120.1 2005/05/27 05:21:30 appldev $ */
3 --TYPE NUM_LIST IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4
5 G_Time_delim CONSTANT VARCHAR2(1) := ':';
6 --G_delim CONSTANT VARCHAR2(1) := '#';
7
8 --ORGANIZATION CONSTANT NUMBER := 1;
9 --CUSTOMER CONSTANT NUMBER :=2;
10 --CARRIER CONSTANT NUMBER := 3;
11 --SUPPLIER CONSTANT NUMBER := 4;
12
13 --TRUCK CONSTANT VARCHAR2(5) := 'TRUCK'; -- TRUCK
14 --DTL CONSTANT VARCHAR2(3) := 'DTL'; -- Direct TL
15 --MTL CONSTANT VARCHAR2(3) := 'MTL'; -- Multistop TL
16 --LTL CONSTANT VARCHAR2(3) := 'LTL'; -- LESS THAN TRUCK
17 --PARCEL CONSTANT VARCHAR2(6) := 'PARCEL'; -- PARCEL
18
19 --Bug_Fix for 4394839
20 FUNCTION get_company_type ( p_facility_id IN NUMBER )
21 RETURN VARCHAR2
22 IS
23 l_Partner_Info VARCHAR2 ( 30 );
24 l_Partner_Id NUMBER;
25 l_Partner_Type NUMBER;
26 l_partner_type_str VARCHAR2 ( 60 );
27 l_Len NUMBER;
28 l_Pos NUMBER;
29 l_Delimiter VARCHAR2 ( 1 ) := ',';
30 BEGIN
31 l_Partner_Info := MST_WB_UTIL.Get_Facility_Owner ( p_Facility_Id, l_Delimiter );
32 l_Len := LENGTH ( l_Partner_Info );
33
34 IF l_Len > 3
35 THEN
36 l_Pos := instrb ( l_Partner_Info, l_Delimiter );
37 l_Partner_Id := TO_NUMBER ( substrb ( l_Partner_Info, 1, ( l_Pos - 1 ) ) );
38 l_Partner_Type := TO_NUMBER ( substrb ( l_Partner_Info, ( l_Pos + 1 ), l_Len ) );
39 END IF;
40
41 IF l_Partner_Id IS NOT NULL AND l_Partner_Type IS NOT NULL
42 THEN
43 IF l_Partner_Type = 1
44 THEN -- Organization
45 l_partner_type_str := fnd_profile.value ( 'MST_COMPANY_NAME' );
46 ELSIF l_Partner_Type = 2
47 THEN -- Customer
48 l_partner_type_str := 'CUSTOMER';
49 ELSIF l_Partner_Type = 3
50 THEN -- Carrier
51 l_partner_type_str := 'CARRIER';
52 ELSIF l_Partner_Type = 4
53 THEN -- Supplier
54 l_partner_type_str := 'SUPPLIER';
55 ELSE
56 l_partner_type_str := '';
57 END IF;
58 ELSE
59 l_partner_type_str := '';
60 END IF;
61
62 RETURN l_partner_type_str;
63 EXCEPTION
64 WHEN OTHERS
65 THEN
66 l_partner_type_str := '';
67 RETURN l_partner_type_str;
68 END get_company_type;
69
70 FUNCTION get_format_string ( p_format_type IN VARCHAR2 )
71 RETURN VARCHAR2
72 IS
73 -- Format for numbers
74 NUMBER_FORMAT CONSTANT VARCHAR2 ( 24 ) := 'FM999G999G999G990';
75 BEGIN
76 IF ( p_format_type = 'NUMBER' )
77 THEN
78 RETURN ( NUMBER_FORMAT );
79 ELSE
80 RETURN ( NULL );
81 END IF;
82 END get_format_string;
83
84 FUNCTION get_total_order_weight( p_plan_id IN NUMBER,
85 p_source_code IN VARCHAR2,
86 p_source_header_number IN VARCHAR2)
87 RETURN NUMBER IS
88
89 L_Weight NUMBER;
90 BEGIN
91 -- Used in MyFacility Details UI
92 SELECT NVL(sum(mdd.Gross_weight),0)
93 INTO l_weight
94 FROM mst_delivery_details mdd
95 WHERE mdd.plan_id = p_plan_id
96 AND mdd.source_code = p_source_code
97 AND mdd.source_header_number = p_source_header_number;
98
99 RETURN l_weight;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 RETURN 0;
104 END get_total_order_weight;
105
106 FUNCTION get_total_order_volume( p_plan_id IN NUMBER,
107 p_source_code IN VARCHAR2,
108 p_source_header_number IN VARCHAR2)
109 RETURN NUMBER IS
110
111 l_volume NUMBER;
112 BEGIN
113 -- Used in MyFacility Details UI
114 SELECT NVL(sum(mdd.volume),0)
115 INTO l_volume
116 FROM mst_delivery_details mdd
117 WHERE mdd.plan_id = p_plan_id
118 AND mdd.source_code = p_source_code
119 AND mdd.source_header_number = p_source_header_number;
120
121 RETURN l_volume;
122 EXCEPTION
123 WHEN OTHERS THEN
124 RETURN 0;
125 END get_total_order_volume;
126
127 FUNCTION get_total_order_pallets(p_plan_id IN NUMBER,
128 p_source_code IN VARCHAR2,
129 p_source_header_number IN VARCHAR2)
130 RETURN NUMBER IS
131
132 l_pallets NUMBER;
133
134 BEGIN
135 -- Used in MyFacility Details UI
136 SELECT NVL(sum(mdd.number_of_pallets),0)
137 INTO l_pallets
138 FROM mst_delivery_details mdd
139 WHERE mdd.plan_id = p_plan_id
140 AND mdd.source_code = p_source_code
141 AND mdd.source_header_number = p_source_header_number;
142
143 RETURN l_pallets;
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN 0;
147 END get_total_order_pallets;
148
149 FUNCTION get_total_order_pieces( p_plan_id IN NUMBER,
150 p_source_code IN VARCHAR2,
151 p_source_header_number IN VARCHAR2)
152 RETURN NUMBER IS
153
154 l_pieces NUMBER;
155
156 BEGIN
157 -- Used in MyFacility Details UI
158 SELECT NVL(sum(mdd.requested_quantity),0)
159 INTO l_pieces
160 FROM mst_delivery_details mdd
161 WHERE mdd.plan_id = p_plan_id
162 AND mdd.source_code = p_source_code
163 AND mdd.source_header_number = p_source_header_number;
164
165 RETURN l_pieces;
166 EXCEPTION
167 WHEN OTHERS THEN
168 RETURN 0;
169 END get_total_order_pieces;
170
171
172 FUNCTION get_total_order_cost ( p_plan_id IN NUMBER,
173 p_source_code IN VARCHAR2,
174 p_source_header_number IN VARCHAR2)
175 RETURN NUMBER IS
176
177 l_cost NUMBER := 0;
178
179 BEGIN
180 -- Used in MyFacility Details UI
181 SELECT NVL(sum(MDD.ALLOCATED_COST),0)
182 INTO l_cost
183 FROM mst_delivery_details mdd
184 WHERE mdd.plan_id = p_plan_id
185 AND mdd.source_code = p_source_code
186 AND mdd.source_header_number = p_source_header_number;
187
188 RETURN l_cost;
189 EXCEPTION
190 WHEN OTHERS THEN
191 RETURN 0;
192 END get_total_order_cost;
193
194 FUNCTION Get_Trip_Circuity(P_Plan_id IN NUMBER,
195 P_Trip_id IN NUMBER)
196 RETURN NUMBER IS
197
198 CURSOR Cur_Circuity IS
199 SELECT (T.Total_trip_distance/T.Total_direct_distance -1)*100
200 FROM mst_trips T
201 WHERE T.plan_id = p_plan_id
202 AND T.trip_id = p_trip_id;
203
204 v_circuity NUMBER;
205 BEGIN
206 -- Used in Truckload Details UI
207 OPEN cur_circuity;
208 FETCH cur_circuity INTO v_circuity;
209 CLOSE cur_circuity;
210 IF v_circuity < 0 THEN
211 v_circuity := 0;
212 END IF;
213
214 RETURN v_circuity;
215 EXCEPTION
216 WHEN OTHERS THEN
217 -- Nothing to return
218 RETURN 0;
219 END Get_Trip_Circuity;
220
221 FUNCTION Get_Trip_Stops(P_Plan_id IN NUMBER,
222 P_Trip_id IN NUMBER)
223 RETURN NUMBER IS
224
225 CURSOR Cur_Trip_Stops IS
226 SELECT COUNT(TS.Stop_Id)
227 FROM mst_trip_stops TS
228 WHERE TS.plan_id = p_plan_id
229 AND TS.trip_id = p_trip_id;
230
231 V_Trip_Stops NUMBER;
232 BEGIN
233 -- Used in Truckload Details and CM Details UI
234 OPEN Cur_Trip_Stops;
235 FETCH Cur_Trip_Stops into V_Trip_Stops;
236 CLOSE Cur_Trip_Stops;
237
238 RETURN V_Trip_Stops;
239 EXCEPTION
240 WHEN OTHERS THEN
241 -- Nothing to return
242 RETURN 0;
243 END Get_Trip_Stops;
244
245 FUNCTION Get_Trip_Orders( p_plan_id IN NUMBER, p_trip_id IN NUMBER )
246 RETURN NUMBER IS
247
248 /*skakani - commented on 16-10-03 ...
249 CURSOR cur_get_trip_orders is
250 SELECT COUNT(DISTINCT dd.source_header_number)
251 FROM mst_delivery_details dd,
252 mst_delivery_assignments da
253 WHERE dd.plan_id = p_plan_Id
254 AND dd.delivery_detail_id = da.delivery_detail_id
255 AND dd.plan_id = da.plan_id
256 AND da.delivery_id IN
257 (SELECT dl.delivery_id
258 FROM mst_delivery_legs dl
259 WHERE dl.plan_id = da.plan_id
260 AND dl.trip_id = p_trip_id);*/
261
262 CURSOR cur_get_trip_orders is
263 SELECT COUNT(distinct nvl(dd.split_from_delivery_detail_id,dd.delivery_detail_id))
264 FROM mst_delivery_details dd,
265 mst_delivery_assignments da
266 WHERE dd.plan_id = p_plan_Id
267 AND dd.delivery_detail_id = da.delivery_detail_id
268 AND dd.plan_id = da.plan_id
269 AND da.delivery_id IN
270 (SELECT dl.delivery_id
271 FROM mst_delivery_legs dl
272 WHERE dl.plan_id = da.plan_id
273 AND dl.trip_id = p_trip_id)
274 AND da.parent_delivery_detail_id is null;
275
276 V_Orders NUMBER;
277 BEGIN
278 -- Used in Truckload/LTL/PARCEL Details and CM Details UI
279 OPEN Cur_Get_Trip_Orders ;
280 FETCH Cur_Get_Trip_Orders into V_Orders;
281 CLOSE Cur_Get_Trip_Orders;
282
283 RETURN v_Orders;
284 EXCEPTION
285 WHEN OTHERS THEN
286 -- Nothing to return.
287 RETURN 0;
288 END Get_Trip_Orders;
289
290 FUNCTION Get_Trip_Det(P_plan_id IN NUMBER,
291 p_trip_id IN NUMBER,
292 p_stop_id IN NUMBER,
293 p_stop_type IN VARCHAR2,
294 p_return_val IN VARCHAR2)
295 RETURN NUMBER IS
296
297 CURSOR Cur_Org_Stop_Det(pp_plan_id IN NUMBER,
298 pp_trip_id IN NUMBER,
299 pp_stop_id IN NUMBER ) IS
300 SELECT NVL(SUM(d.gross_weight),0), NVL(SUM(d.volume),0),
301 NVL(SUM(d.number_of_pallets),0),NVL(SUM(d.number_of_pieces),0)
302 FROM mst_deliveries d
303 WHERE d.plan_id = pp_plan_id
304 AND d.delivery_id IN
305 (SELECT delivery_id
306 FROM mst_delivery_legs dl,
307 mst_trip_stops ts
308 WHERE dl.plan_id = d.plan_id
309 AND dl.pick_up_stop_id = ts.stop_id
310 AND ts.plan_id = dl.plan_id
311 AND ts.stop_id = pp_stop_id
312 AND ts.trip_id = pp_trip_id);
313
314 CURSOR cur_dest_stop_det(pp_plan_id in NUMBER,
315 pp_trip_id in NUMBER,
316 pp_stop_id in NUMBER ) IS
317 SELECT NVL(SUM(d.gross_weight),0), NVL(SUM(d.volume),0),
318 NVL(SUM(d.number_of_pallets),0), NVL(SUM(d.number_of_pieces),0)
319 FROM mst_deliveries d
320 WHERE d.plan_id = pp_plan_id
321 AND d.delivery_id IN
322 (SELECT delivery_id
323 FROM mst_delivery_legs dl,
324 mst_trip_stops ts
325 WHERE dl.plan_id = d.plan_id
326 AND dl.drop_off_stop_id = ts.stop_id
327 AND ts.plan_id = dl.plan_id
328 AND ts.stop_id = pp_stop_id
329 AND ts.trip_id = pp_trip_id);
330
331 V_Weight NUMBER;
332 V_Volume NUMBER;
333 V_Pallets NUMBER;
334 V_Pieces NUMBER;
335 BEGIN
336 -- Used in Truckload details UI
337 IF P_Stop_Type = 'O' THEN
338 OPEN Cur_Org_Stop_Det(P_Plan_id, P_Trip_id, P_Stop_id) ;
339 FETCH Cur_Org_Stop_Det into V_Weight, V_Volume,
340 V_Pallets, V_Pieces;
341 CLOSE Cur_Org_Stop_Det;
342 ELSIF P_Stop_Type = 'D' THEN
343 OPEN Cur_Dest_Stop_Det(P_Plan_id, P_Trip_id, P_Stop_id) ;
344 FETCH Cur_Dest_Stop_Det into V_Weight, V_Volume,
345 V_Pallets, V_Pieces;
346 CLOSE Cur_Dest_Stop_Det;
347 END IF;
348
349 IF P_Return_Val = 'W' THEN
350 RETURN v_Weight;
351 ELSIF P_Return_Val = 'V' THEN
352 RETURN v_Volume;
353 ELSIF P_Return_Val = 'P' THEN
354 RETURN v_Pallets;
355 ELSIF P_Return_Val = 'PC' THEN
356 RETURN v_Pieces;
357 END IF;
358 EXCEPTION
359 WHEN OTHERS THEN
360 -- Nothing to return.
361 RETURN 0;
362 END Get_Trip_Det;
363
364 FUNCTION Get_Trip_Det(P_plan_id IN NUMBER,
365 P_TRIP_ID IN NUMBER,
366 P_Return_val IN VARCHAR2)
367 RETURN NUMBER IS
368
369 CURSOR Cur_Stop_Det(pp_plan_id IN NUMBER,PP_TRIP_ID IN NUMBER ) IS
370 SELECT NVL(SUM(D.Gross_Weight),0), NVL(SUM(D.Volume),0),
371 NVL(SUM(D.NUMBER_OF_Pallets),0), NVL(SUM(D.NUMBER_OF_PIECES),0)
372 FROM MST_DELIVERIES D
373 WHERE D.PLAN_ID = pp_plan_id
374 AND D.DELIVERY_ID IN
375 (SELECT DL.DELIVERY_ID
376 FROM MST_DELIVERY_LEGS DL
377 WHERE DL.plan_id = d.plan_id
378 AND dl.Trip_id = PP_Trip_Id);
379
380 V_Weight NUMBER;
381 V_Volume NUMBER;
382 V_Pallets NUMBER;
383 V_Pieces NUMBER;
384 BEGIN
385 -- Used in LTL/PARCEL Details and CM Details UI
386 OPEN Cur_Stop_Det(P_Plan_id, P_Trip_id ) ;
387 FETCH Cur_Stop_Det into V_Weight, V_Volume,
388 V_Pallets, V_Pieces;
389 CLOSE Cur_Stop_Det;
390
391
392 IF P_Return_Val = 'W' THEN
393 RETURN v_Weight;
394 ELSIF P_Return_Val = 'V' THEN
395 RETURN v_Volume;
396 ELSIF P_Return_Val = 'P' THEN
397 RETURN v_Pallets;
398 ELSIF P_Return_Val = 'PC' THEN
399 RETURN v_Pieces;
400 END IF;
401 EXCEPTION
402 WHEN OTHERS THEN
403 -- Nothing to return.
404 RETURN 0;
405 END Get_Trip_Det;
406
407 FUNCTION Get_Stop_Orders( P_plan_id IN NUMBER,
408 P_TRIP_ID IN NUMBER,
409 P_STOP_ID IN NUMBER )
410 RETURN NUMBER IS
411
412 /*CURSOR Cur_Get_Trip_Stop_Orders IS
413 SELECT COUNT(DISTINCT mdd.source_header_number)
414 FROM mst_delivery_details mdd
415 WHERE mdd.plan_id = p_plan_id
416 AND mdd.delivery_detail_id IN
417 ( SELECT mda.delivery_detail_id
418 FROM mst_deliveries md,
419 mst_delivery_assignments mda,
420 mst_delivery_legs mdl
421 WHERE mda.plan_id = mdd.plan_id
422 AND mda.plan_id = md.plan_id
423 AND mda.delivery_id = md.delivery_id
424 AND md.plan_id = mdl.plan_id
425 AND md.delivery_id = mdl.delivery_id
426 AND mdl.trip_id = p_trip_id
427 AND ( mdl.pick_up_stop_id = p_stop_id
428 OR mdl.drop_off_stop_id = p_stop_id));*/
429
430 CURSOR get_stop_det IS
431 SELECT stop_sequence_number
432 FROM mst_trip_stops
433 WHERE plan_Id = p_plan_Id
434 AND trip_id = p_trip_id
435 AND stop_id = p_stop_id;
436
437 /*CURSOR Cur_Get_Trip_Stop_Orders is
438 SELECT COUNT(mdd.delivery_detail_id)
439 FROM mst_delivery_details mdd
440 WHERE mdd.plan_id = p_plan_Id
441 AND mdd.delivery_detail_id IN
442 ( SELECT mda.delivery_detail_id
443 FROM mst_delivery_assignments mda,
444 mst_delivery_legs mdl
445 WHERE mda.plan_id = mdd.plan_id
446 AND mda.plan_id = mdl.plan_id
447 AND mda.delivery_id = mdl.delivery_id
448 AND mdl.trip_id = p_trip_id
449 AND mdl.pick_up_stop_id = p_stop_id;*/
450
451 CURSOR Cur_Get_pick_Orders(p_stop_sequence NUMBER) is
452 SELECT COUNT(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id))
453 FROM mst_delivery_details mdd
454 WHERE mdd.plan_id = p_plan_Id
455 AND mdd.delivery_detail_id IN
456 ( SELECT mda.delivery_detail_id
457 FROM mst_delivery_assignments mda,
458 mst_delivery_legs mdl
459 WHERE mda.plan_id = mdd.plan_id
460 AND mda.plan_id = mdl.plan_id
461 AND mda.delivery_id = mdl.delivery_id
462 AND mda.parent_delivery_detail_id is null
463 AND mdl.trip_id = p_trip_id
464 AND mdl.pick_up_stop_id IN
465 (SELECT mts1.stop_id
466 FROM mst_trip_stops mts1
467 WHERE mts1.plan_id = mdl.plan_Id
468 AND mts1.trip_id = mdl.trip_id
469 AND mts1.stop_sequence_number <= p_stop_sequence));
470
471 CURSOR Cur_Get_drop_Orders(p_stop_sequence NUMBER) is
472 SELECT COUNT(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id))
473 FROM mst_delivery_details mdd
474 WHERE mdd.plan_id = p_plan_Id
475 AND mdd.delivery_detail_id IN
476 ( SELECT mda.delivery_detail_id
477 FROM mst_delivery_assignments mda,
478 mst_delivery_legs mdl
479 WHERE mda.plan_id = mdd.plan_id
480 AND mda.plan_id = mdl.plan_id
481 AND mda.delivery_id = mdl.delivery_id
482 AND mda.parent_delivery_detail_id is null
483 AND mdl.trip_id = p_trip_id
484 AND mdl.drop_off_stop_id IN
485 (SELECT mts1.stop_id
486 FROM mst_trip_stops mts1
487 WHERE mts1.plan_id = mdl.plan_Id
488 AND mts1.trip_id = mdl.trip_id
489 AND mts1.stop_sequence_number <= p_stop_sequence));
490
491 l_stop_info get_stop_det%ROWTYPE;
492 l_pick_orders NUMBER;
493 l_drop_orders NUMBER;
494 V_Orders Number := 0;
495 BEGIN
496 -- Used in Truckload Details UI
497 /*
498 OPEN Cur_Get_Trip_Stop_Orders ;
499 FETCH Cur_Get_Trip_Stop_Orders INTO V_Orders;
500 CLOSE Cur_Get_Trip_Stop_Orders;
501 */
502 OPEN get_stop_det;
503 FETCH get_stop_det INTO l_stop_info;
504 CLOSE get_stop_det;
505
506 IF l_stop_info.stop_sequence_number IS NOT NULL THEN
507 OPEN Cur_Get_pick_Orders(l_stop_info.stop_sequence_number);
508 FETCH Cur_Get_pick_Orders INTO l_pick_orders;
509 CLOSE Cur_Get_pick_Orders;
510 OPEN Cur_Get_drop_Orders(l_stop_info.stop_sequence_number);
511 FETCH Cur_Get_drop_Orders INTO l_drop_orders;
512 CLOSE Cur_Get_drop_Orders;
513 v_orders := NVL(l_pick_orders,0) - NVL(l_drop_orders,0);
514 END IF;
515 RETURN v_Orders;
516 EXCEPTION
517 WHEN OTHERS THEN
518 -- Nothing to return.
519 RETURN 0;
520 End Get_STOP_Orders;
521
522 FUNCTION GET_DELIVERY_ORDERS( P_PLAN_ID IN NUMBER,
523 P_DELIVERY_ID IN NUMBER,
524 P_DELIVERY_FLAG IN VARCHAR2 )
525 RETURN NUMBER IS
526
527 CURSOR Cur_Get_Orders IS
528 Select Count(distinct nvl(DD.split_from_delivery_Detail_id,DD.Delivery_Detail_Id))
529 FROM Mst_Delivery_Details DD,
530 Mst_Delivery_Assignments DA
531 WHERE DD.PLAN_ID = DA.PLAN_ID
532 AND DD.Delivery_Detail_Id = DA.Delivery_Detail_Id
533 AND DA.parent_delivery_detail_id is null
534 AND DA.Delivery_Id = p_delivery_id
535 AND DA.plan_id = p_plan_id;
536
537 V_Orders NUMBER;
538 BEGIN
539 -- Used in Truckload Details UI
540 OPEN Cur_Get_Orders ;
541 FETCH Cur_Get_Orders into V_Orders;
542 CLOSE Cur_Get_Orders;
543
544 RETURN v_Orders;
545 EXCEPTION
546 WHEN OTHERS THEN
547 -- Nothing to return.
548 RETURN 0;
549 END GET_DELIVERY_ORDERS;
550
551 FUNCTION Get_Name(P_Location_id IN NUMBER)
552 RETURN VARCHAR2 IS
553
554 CURSOR CUR_COMPANY_NAME( P_PARTY_ID IN NUMBER ) IS
555 SELECT HZP.PARTY_NAME
556 FROM HZ_PARTIES HZP
557 WHERE HZP.PARTY_ID = P_party_id;
558
559 CURSOR chk_owner_info(p_location_id IN NUMBER) IS
560 SELECT wlo.OWNER_TYPE, wlo.OWNER_PARTY_ID
561 FROM wsh_location_owners wlo
562 WHERE wlo.wsh_location_id = p_location_id;
563
564 CURSOR chk_cust_site(p_location_id IN NUMBER, p_owner_party_id IN NUMBER) IS
565 SELECT 1
566 FROM hz_cust_acct_sites_all hzcasa,
567 hz_party_sites hzps,
568 hz_cust_accounts hzc
569 WHERE hzc.party_id = hzps.party_id
570 AND hzps.location_id = p_location_id
571 AND hzps.party_site_id = hzcasa.party_site_id
572 AND hzcasa.cust_account_id = hzc.cust_account_id
573 AND hzps.party_id = p_owner_party_id;
574 --AND HZCASA.SHIP_TO_FLAG in ('P', 'Y');
575 l_owner_type NUMBER;
576 l_owner_party_id NUMBER;
577
578 l_Name VARCHAR2(360);
579 l_dummy NUMBER;
580
581 BEGIN
582 -- Used in Truckload/LTL/PARCEL/CM/CUSTOMER/SUPPLIER/MYFACILITY/CARRIER Details UI
583 FOR rec_owner IN chk_owner_info(P_Location_id) LOOP
584 l_owner_type := rec_owner.owner_type;
585 l_owner_party_id := rec_owner.owner_party_id;
586 IF l_owner_type = 2 THEN
587 OPEN chk_cust_site(p_location_id, l_owner_party_id);
588 FETCH chk_cust_site INTO l_dummy;
589 IF chk_cust_site%FOUND THEN
590 CLOSE chk_cust_site;
591 EXIT;
592 END IF;
593 CLOSE chk_cust_site;
594 ELSE
595 IF chk_owner_info%rowcount > 1 THEN
596 RAISE too_many_rows;
597 END IF;
598 END IF;
599 END LOOP;
600
601 IF l_owner_type = 1 THEN
602 l_name := fnd_profile.value('MST_COMPANY_NAME');
603 ELSE
604 OPEN CUR_COMPANY_NAME(l_owner_party_ID);
605 FETCH CUR_COMPANY_NAME INTO l_Name;
606 CLOSE CUR_COMPANY_NAME;
607 END IF;
608
609 RETURN l_Name;
610 EXCEPTION
611 WHEN too_many_rows THEN
612 l_name := Get_meaning('MST_STRING','33','MFG');
613 RETURN L_NAME;
614 WHEN OTHERS THEN
615 -- Nothing to return
616 RETURN NULL;
617 END Get_Name;
618
619 FUNCTION Get_meaning( p_Lookup_Type IN VARCHAR2,
620 p_Lookup_Code IN VARCHAR2,
621 p_Product IN VARCHAR2)
622 RETURN VARCHAR2 IS
623
624 CURSOR Ml_Cur IS
625 SELECT Meaning
626 FROM MFG_Lookups
627 WHERE Lookup_Code = P_Lookup_Code
628 AND Lookup_Type = P_Lookup_Type;
629
630 CURSOR Wl_Cur Is
631 SELECT Meaning
632 FROM Wsh_Lookups
633 WHERE Lookup_Code = P_Lookup_Code
634 AND Lookup_Type = P_Lookup_Type;
635
636 l_meaning VARCHAR2(80);
637
638 Invalid_Lookup EXCEPTION;
639
640 BEGIN
641 -- Used in Truckload/LTL/PARCEL/CM Details UI
642 IF P_PRODUCT = 'MFG' THEN
643 OPEN Ml_Cur;
644 FETCH Ml_Cur INTO l_Meaning;
645 IF Ml_Cur%NOTFOUND THEN
646 CLOSE Ml_Cur;
647 RAISE Invalid_Lookup;
648 END IF;
649 CLOSE Ml_Cur;
650 ELSIF p_Product = 'WSH' THEN
651 OPEN Wl_Cur;
652 FETCH Wl_Cur INTO l_Meaning;
653 IF Wl_Cur%NOTFOUND THEN
654 CLOSE Wl_Cur;
655 RAISE Invalid_Lookup;
656 END IF;
657 CLOSE Wl_Cur;
658 END IF;
659 RETURN l_Meaning;
660 EXCEPTION
661 WHEN OTHERS THEN
662 RETURN NULL;
663 END Get_meaning;
664
665 FUNCTION Get_Partner_Name(P_PARTY_id IN Number, P_PARTY_TYPE IN Number)
666 RETURN VARCHAR2 IS
667
668 CURSOR CUR_PARTY_NAME( P_PARTY_ID IN NUMBER ) IS
669 SELECT HZP.PARTY_NAME
670 FROM HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HCA
671 WHERE HCA.CUST_ACCOUNT_ID = P_party_id
672 AND HCA.PARTY_ID = HZP.PARTY_ID;
673
674 CURSOR CUR_VENDOR_NAME( P_PARTY_ID IN NUMBER ) IS
675 SELECT VENDOR_NAME
676 FROM PO_VENDORS pov
677 WHERE pov.VENDOR_ID = p_PARTY_ID;
678
679 l_name VARCHAR2(360);
680 BEGIN
681 IF p_party_type = 1 THEN
682 OPEN CUR_PARTY_NAME(P_PARTY_id);
683 FETCH CUR_PARTY_NAME INTO l_name;
684 CLOSE CUR_PARTY_NAME;
685 ELSIF p_party_type = 2 THEN
686 OPEN CUR_VENDOR_NAME(P_PARTY_id);
687 FETCH CUR_VENDOR_NAME INTO l_name;
688 CLOSE CUR_VENDOR_NAME;
689 END IF;
690 RETURN l_name;
691
692 EXCEPTION
693 WHEN OTHERS THEN
694 RETURN NULL;
695 END Get_Partner_Name;
696
697 FUNCTION Get_Cont_Move_Distance( p_Plan_Id IN NUMBER,
698 p_cont_move_id IN NUMBER)
699 RETURN NUMBER IS
700
701 CURSOR Cm_Dist_Cur(Cp_Plan_Id IN NUMBER,
702 Cp_Cont_Move_Id IN NUMBER) IS
703 SELECT NVL(sum(Total_Trip_Distance),0)
704 FROM Mst_Trips
705 WHERE Plan_Id = Cp_Plan_Id
706 AND Continuous_Move_Id = Cp_Cont_Move_Id;
707
708 l_Distance NUMBER;
709 BEGIN
710 -- Used in CM Details UI
711 OPEN Cm_Dist_Cur(P_Plan_Id,P_Cont_Move_Id);
712 FETCH Cm_Dist_Cur INTO l_Distance;
713 IF Cm_Dist_Cur%NOTFOUND THEN
714 CLOSE Cm_Dist_Cur;
715 RAISE No_Data_Found;
716 END IF;
717 CLOSE Cm_Dist_Cur;
718 RETURN l_Distance;
719 EXCEPTION
720 WHEN OTHERS THEN
721 RETURN 0;
722 END Get_Cont_Move_Distance;
723
724 FUNCTION Get_Cont_Move_total_loads( p_Plan_Id IN NUMBER,
725 p_cont_move_id IN NUMBER)
726 Return NUMBER IS
727 CURSOR Cm_count_Cur(Cp_Plan_Id IN NUMBER,
728 Cp_Cont_Move_Id IN NUMBER) IS
729 SELECT COUNT(Trip_id)
730 FROM Mst_Trips
731 WHERE Plan_Id = Cp_Plan_Id
732 AND Continuous_Move_Id = Cp_Cont_Move_Id;
733
734 l_trips NUMBER;
735 BEGIN
736 -- Used in CM Details UI
737 OPEN Cm_count_Cur(P_Plan_Id,P_Cont_Move_Id);
738 FETCH Cm_count_Cur INTO l_trips;
739 IF Cm_count_Cur%NOTFOUND THEN
740 CLOSE Cm_count_Cur;
741 RAISE No_Data_Found;
742 END IF;
743 CLOSE Cm_count_Cur;
744 RETURN l_trips;
745 EXCEPTION
746 WHEN OTHERS THEN
747 RETURN 0;
748 END Get_Cont_Move_total_loads;
749
750 FUNCTION GET_FIRST_DEPARTURE_DATE(P_PLAN_ID IN NUMBER,
751 P_TRIP_ID IN NUMBER)
752 RETURN DATE IS
753 CURSOR TRIP_STOP_CUR IS
754 SELECT TS.PLANNED_DEPARTURE_DATE
755 FROM MST_TRIP_STOPS TS
756 WHERE TS.PLAN_ID = P_PLAN_ID
757 AND TS.TRIP_ID = P_TRIP_ID
758 AND TS.STOP_SEQUENCE_NUMBER =
759 (SELECT MIN(STOP_SEQUENCE_NUMBER)
760 FROM MST_TRIP_STOPS TS1
761 WHERE TS1.PLAN_ID = TS.PLAN_ID
762 AND TS1.TRIP_ID = TS.TRIP_ID
763 GROUP BY TS1.PLAN_ID, TS1.TRIP_ID);
764 l_Date DATE;
765 BEGIN
766 -- Used in LTL/PARCEL/CM Details UI
767 OPEN TRIP_STOP_CUR;
768 FETCH TRIP_STOP_CUR INTO l_Date;
769 IF TRIP_STOP_CUR%NOTFOUND THEN
770 CLOSE TRIP_STOP_CUR;
771 RAISE NO_DATA_FOUND;
772 END IF;
773 CLOSE TRIP_STOP_CUR;
774 RETURN l_Date;
775 EXCEPTION
776 WHEN OTHERS THEN
777 RETURN NULL;
778 END GET_FIRST_DEPARTURE_DATE;
779
780 FUNCTION GET_LAST_ARRIVAL_DATE(P_PLAN_ID IN NUMBER,
781 P_TRIP_ID IN NUMBER)
782 RETURN DATE IS
783
784 CURSOR TRIP_STOP_CUR IS
785 SELECT TS.PLANNED_ARRIVAL_DATE
786 FROM MST_TRIP_STOPS TS
787 WHERE TS.PLAN_ID = P_PLAN_ID
788 AND TS.TRIP_ID = P_TRIP_ID
789 AND TS.STOP_SEQUENCE_NUMBER =
790 (SELECT MAX(STOP_SEQUENCE_NUMBER)
791 FROM MST_TRIP_STOPS TS1
792 WHERE TS1.PLAN_ID = TS.PLAN_ID
793 AND TS1.TRIP_ID = TS.TRIP_ID
794 GROUP BY TS1.PLAN_ID, TS1.TRIP_ID);
795 l_Date DATE;
796 BEGIN
797 -- Used in LTL/PARCEL/CM Details UI
798 OPEN TRIP_STOP_CUR;
799 FETCH TRIP_STOP_CUR INTO l_Date;
800 IF TRIP_STOP_CUR%NOTFOUND THEN
801 CLOSE TRIP_STOP_CUR;
802 RAISE NO_DATA_FOUND;
803 END IF;
804 CLOSE TRIP_STOP_CUR;
805 RETURN l_Date;
806 EXCEPTION
807 WHEN OTHERS THEN
808 RETURN NULL;
809 END GET_LAST_ARRIVAL_DATE;
810
811 FUNCTION get_effective_cube_capacity(p_plan_id IN NUMBER,
812 p_trip_id IN NUMBER)
813 RETURN NUMBER IS
814 cursor stops is
815 select departure_volume
816 from mst_trip_stops
817 where plan_id = p_plan_id
818 and trip_id = p_trip_id;
819
820 cursor trips is
821 select nvl(peak_volume_utilization, 1)
822 from mst_trips
823 where plan_id = p_plan_id
824 and trip_id = p_trip_id;
825
826 l_peak_volume number := 0;
827 l_peak_volume_utilization number;
828 BEGIN
829 for c_trip_stops in stops loop
830 if l_peak_volume < c_trip_stops.departure_volume then
831 l_peak_volume := c_trip_stops.departure_volume;
832 end if;
833 end loop;
834
835 open trips;
836 fetch trips into l_peak_volume_utilization;
837 close trips;
838 if l_peak_volume_utilization = 0 then
839 l_peak_volume_utilization := 1;
840 end if;
841 return l_peak_volume/l_peak_volume_utilization;
842
843 END get_effective_cube_capacity;
844
845 FUNCTION get_vehicle_capacity(p_plan_id IN NUMBER,
846 p_vehicle_type_id IN NUMBER,
847 p_ret_val IN VARCHAR2)
848 RETURN NUMBER IS
849
850 CURSOR CUR_VEH_WT_CAPACITY(pp_plan_id IN NUMBER,
851 pp_vehicle_type_id IN NUMBER) IS
852 SELECT MSI.Maximum_Load_Weight
853 FROM MTL_SYSTEM_ITEMS MSI,
854 FTE_VEHICLE_TYPES FVT
855 WHERE FVT.VEHICLE_TYPE_ID = pp_vehicle_type_id
856 AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
857 AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID;
858
859 CURSOR CUR_VEH_VOL_CAPACITY(pp_plan_id IN NUMBER,
860 pp_vehicle_type_id IN NUMBER) IS
861 SELECT (FVT.USABLE_LENGTH *
862 FVT.USABLE_WIDTH *
863 FVT.USABLE_HEIGHT) USABLE_VOLUME
864 FROM MTL_SYSTEM_ITEMS MSI,
865 FTE_VEHICLE_TYPES FVT
866 WHERE FVT.VEHICLE_TYPE_ID = pp_vehicle_type_id
867 AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
868 AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID;
869
870 CURSOR CUR_VEH_PAL_CAPACITY(pp_plan_id IN NUMBER,
871 pp_vehicle_type_id IN NUMBER) IS
872 SELECT (FVT.PALLET_FLOOR_SPACE *
873 FVT.PALLET_STACKING_HEIGHT ) Pallets
874 FROM MTL_SYSTEM_ITEMS MSI,
875 FTE_VEHICLE_TYPES FVT
876 WHERE FVT.VEHICLE_TYPE_ID = pp_vehicle_type_id
877 AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
878 AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID;
879
880 v_capacity NUMBER;
881 BEGIN
882 -- Used in Truckload Details UI
883 IF P_RET_VAL = 'W' then
884 OPEN cur_veh_WT_capacity(p_plan_id, P_VEHICLE_TYPE_ID );
885 FETCH cur_veh_WT_capacity INTO v_capacity;
886 CLOSE cur_veh_WT_capacity;
887 ELSIF P_RET_VAL = 'V' Then
888 OPEN cur_veh_VOL_capacity(p_plan_id, P_VEHICLE_TYPE_ID );
889 FETCH cur_veh_VOL_capacity INTO v_capacity;
890 CLOSE cur_veh_VOL_capacity;
891 ELSIF P_RET_VAL = 'P' Then
892 OPEN Cur_Veh_Pal_Capacity(p_plan_id, P_VEHICLE_TYPE_ID );
893 FETCH Cur_Veh_Pal_Capacity INTO v_capacity;
894 CLOSE Cur_Veh_Pal_Capacity;
895 END IF;
896 --V_Capacity := NVL(V_Capacity, 0.001);
897 RETURN V_Capacity;
898 EXCEPTION
899 WHEN OTHERS THEN
900 -- Nothing to return
901 RETURN 0;
902 END get_vehicle_capacity;
903
904 FUNCTION elapsed_time(p_start_date DATE,
905 p_end_date DATE)
906 RETURN VARCHAR2 is
907 v_hours NUMBER;
908 v_minutes NUMBER;
909 v_string VARCHAR2(10);
910 BEGIN
911 -- Used in Truckload Details UI
912 v_hours := (p_end_date - p_start_date)*24;
913 v_minutes := mod(v_hours,1);
914 v_hours := v_hours - v_minutes;
915 v_minutes := round(v_minutes*60);
916 IF v_hours < 0 THEN
917 v_hours := 0;
918 END IF;
919 IF v_minutes < 0 THEN
920 v_minutes := 0;
921 END IF;
922 IF v_minutes < 10 THEN
923 v_string := '0'||v_minutes;
924 ELSE
925 v_string := v_minutes;
926 END IF;
927 IF v_minutes IS NULL or v_hours IS NULL THEN
928 v_string := '00'||G_Time_delim||'00';
929 ELSE
930 v_string := v_hours||G_Time_delim||v_string;
931 END IF;
932 RETURN v_string;
933 EXCEPTION
934 WHEN OTHERS THEN
935 RETURN '00'||G_Time_delim||'00';
936 END elapsed_time;
937
938 FUNCTION elapsed_time(p_end_date DATE, p_delay NUMBER)
939 RETURN VARCHAR2 IS
940 v_hours NUMBER;
941 v_minutes NUMBER;
942 v_string VARCHAR2(10);
943 BEGIN
944 -- Used in Truckload Details UI
945 v_hours := (p_end_date - (p_end_date - p_delay))*24;
946 v_minutes := mod(v_hours,1);
947 v_hours := v_hours - v_minutes;
948 v_minutes := round(v_minutes*60);
949 IF v_hours < 0 THEN
950 v_hours := 0;
951 END IF;
952 IF v_minutes <0 THEN
953 v_minutes := 0;
954 END IF;
955 IF v_minutes < 10 THEN
956 v_string := '0'||v_minutes;
957 ELSE
958 v_string := v_minutes;
959 END IF;
960 IF v_minutes IS NULL or v_hours IS NULL THEN
961 v_string := '00'||G_Time_delim||'00';
962 ELSE
963 v_string := v_hours||G_Time_delim||v_string;
964 END IF;
965 RETURN v_string;
966 EXCEPTION
967 WHEN OTHERS THEN
968 RETURN '00'||G_Time_delim||'00';
969 END elapsed_time;
970
971 FUNCTION get_threshold_value (p_exception_type IN NUMBER)
972 RETURN VARCHAR2 IS
973
974 CURSOR c1 (p_user_id IN NUMBER) IS
975 SELECT TRIM(TO_CHAR(threshold_value))||' '||threshold_uom
976 FROM mst_excep_preferences
977 WHERE exception_type = p_exception_type
978 AND user_id = p_user_id;
979
980 l_threshold VARCHAR2(100) := NULL;
981
982 BEGIN
983 OPEN c1(fnd_global.user_id);
984 FETCH c1 into l_threshold;
985 CLOSE c1;
986 IF l_threshold IS NULL THEN
987 OPEN c1(-9999);
988 FETCH c1 INTO l_threshold;
989 CLOSE c1;
990 END IF;
991 RETURN l_threshold;
992 EXCEPTION
993 WHEN OTHERS THEN
994 IF c1%ISOPEN THEN
995 CLOSE c1;
996 END IF;
997 RETURN NULL;
998 END get_threshold_value;
999
1000 FUNCTION get_facility_owner(P_Facility_id IN NUMBER,
1001 P_Delim IN VARCHAR2)
1002 RETURN VARCHAR2 IS
1003 -- SQL repository issues as on 25-05-04:
1004 -- Rewritten sql to avoid ORDER BY clause
1005 CURSOR get_owner(p_owner_type IN Number) IS
1006 SELECT wlo.owner_party_id, wlo.owner_type
1007 FROM wsh_location_owners wlo
1008 WHERE wlo.wsh_location_id =
1009 (SELECT flp.location_id
1010 FROM fte_location_parameters flp
1011 WHERE flp.facility_id = p_facility_ID )
1012 AND Wlo.owner_type = p_owner_type;
1013 /*CURSOR get_owner IS
1014 SELECT wlo.owner_party_id, wlo.owner_type
1015 FROM wsh_location_owners wlo,
1016 fte_location_parameters flp
1017 WHERE wlo.wsh_location_id = flp.location_id
1018 AND flp.facility_id = p_facility_ID
1019 ORDER BY wlo.owner_type;*/
1020
1021 l_rec_owner get_owner%ROWTYPE;
1022 l_Partner_info VARCHAR2(30);
1023 j NUMBER := 0;
1024
1025 BEGIN
1026 -- Used in ALL the screens where Link to facility need to be shown ( in UI).
1027
1028 /*FOR rec_owner IN get_owner LOOP
1029 l_partner_info := rec_owner.owner_party_id ||p_delim||rec_owner.owner_type;
1030 IF rec_owner.owner_type IN (1,3) THEN
1031 EXIT;
1032 END IF;
1033 END LOOP;*/
1034 FOR I IN 1..4 LOOP -- 1 Org, 2 Cust, 3 Carr, 4 Supp
1035 IF i = 2 THEN
1036 j := 3;
1037 ELSIF i = 3 THEN
1038 j := 2;
1039 ELSE
1040 j := i;
1041 END IF;
1042 OPEN get_owner(j);
1043 FETCH get_owner INTO l_rec_owner;
1044 IF get_owner%FOUND THEN
1045 l_partner_info := l_rec_owner.owner_party_id ||p_delim||l_rec_owner.owner_type;
1046 END IF;
1047 CLOSE get_owner;
1048 EXIT WHEN l_rec_owner.owner_type IN (1,3);
1049 END LOOP;
1050 RETURN l_partner_info;
1051 EXCEPTION
1052 WHEN too_many_rows THEN
1053 l_partner_info := '-9999'||p_delim;
1054 RETURN l_partner_info;
1055 WHEN OTHERS THEN
1056 RETURN NULL;
1057 END get_facility_owner;
1058
1059 FUNCTION Get_Contact_name(p_contact_id IN NUMBER)
1060 RETURN VARCHAR2 IS
1061
1062 CURSOR cur_contact IS
1063 SELECT hzp.party_name
1064 FROM hz_parties hzp,
1065 HZ_CONTACT_POINTS hzcp
1066 WHERE hzp.party_id = hzcp.owner_table_id
1067 AND hzcp.CONTACT_POINT_ID = p_contact_id;
1068
1069 l_Contact VARCHAR2(100);
1070 BEGIN
1071 -- Used in Customer/Supplier/Carrier/Myfacility Details UI
1072 OPEN cur_Contact;
1073 FETCH cur_contact INTO l_Contact;
1074 CLOSE cur_Contact;
1075 RETURN l_Contact;
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 RETURN NULL;
1079 END Get_Contact_name;
1080
1081 FUNCTION get_phone_number(p_contact_id IN NUMBER)
1082 RETURN VARCHAR2 IS
1083
1084 CURSOR cur_phone IS
1085 SELECT hzcp.PHONE_NUMBER
1086 FROM HZ_CONTACT_POINTS hzcp
1087 WHERE hzcp.CONTACT_POINT_ID = p_contact_id;
1088
1089 l_Phone VARCHAR2(100);
1090 BEGIN
1091 -- Used in Customer/Supplier/Carrier/Myfacility Details UI
1092 OPEN cur_Phone;
1093 FETCH cur_Phone INTO l_Phone;
1094 CLOSE cur_Phone;
1095 RETURN l_Phone;
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 RETURN NULL;
1099 END get_phone_number;
1100
1101 FUNCTION get_min_sec(p_hours NUMBER)
1102 RETURN VARCHAR2 IS
1103
1104 v_hours NUMBER;
1105 v_minutes NUMBER;
1106 v_seconds NUMBER;
1107 v_string VARCHAR2(10);
1108 BEGIN
1109
1110 v_hours := p_hours;
1111 IF v_hours > 1 THEN
1112 v_minutes := mod(v_hours,1);
1113 v_hours := v_hours - v_minutes;
1114 ELSE
1115 v_minutes := v_hours;
1116 END IF;
1117
1118 v_minutes := v_minutes*60;
1119 v_seconds := mod(v_minutes,1);
1120 v_minutes := v_minutes - v_seconds;
1121 v_seconds := round(v_seconds*60,2);
1122 IF v_minutes IS NULL THEN
1123 v_minutes := 0;
1124 END IF;
1125 IF v_minutes < 10 THEN
1126 v_string := '0'||v_minutes;
1127 ELSE
1128 v_string := v_minutes;
1129 END IF;
1130 IF v_seconds IS NULL THEN
1131 v_seconds := 0;
1132 END IF;
1133 IF v_seconds < 10 THEN
1134 v_string := v_string||G_Time_delim||'0'||v_seconds;
1135 ELSE
1136 v_string := v_string||G_Time_delim||v_seconds;
1137 END IF;
1138
1139 RETURN v_string;
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 RETURN '00'||G_Time_delim||'00';
1143 END get_min_sec;
1144
1145 FUNCTION get_hr_min (p_hours IN NUMBER)
1146 RETURN VARCHAR2 IS
1147 l_Hours NUMBER;
1148 l_Minutes NUMBER;
1149 l_String VARCHAR2(10);
1150 l_Null_Str_Exception EXCEPTION;
1151 l_sign varchar2(1);
1152 BEGIN
1153 IF p_hours IS NULL THEN
1154 RAISE l_Null_Str_Exception;
1155 END IF;
1156
1157 IF p_Hours < 0 THEN
1158 l_sign := '-';
1159 l_Hours := TRUNC(p_Hours) * -1;
1160 l_Minutes := ROUND(((p_Hours * -1) - l_Hours) * 60);
1161 ELSE
1162 l_Hours := TRUNC(p_Hours);
1163 l_Minutes := ROUND((p_Hours - l_Hours) * 60);
1164 END IF;
1165
1166 --Bug_Fix for 4211337
1167 IF l_Minutes = 60
1168 THEN
1169 l_Hours := l_Hours + 1;
1170 l_Minutes := 0;
1171 END IF;
1172
1173 IF l_Hours = 0 THEN
1174 l_String := '00';
1175 ELSIF l_Hours < 10 THEN
1176 l_String := '0'||TO_CHAR(l_Hours);
1177 ELSE
1178 l_String := TO_CHAR(l_Hours);
1179 END IF;
1180
1181 IF l_Minutes <= 0 THEN
1182 l_String := l_String||g_Time_Delim||'00';
1183 ELSIF l_Minutes < 10 THEN
1184 l_String := l_String||g_Time_Delim||'0'||TO_CHAR(l_Minutes);
1185 ELSE
1186 l_String := l_String||g_Time_Delim||TO_CHAR(l_Minutes);
1187 END IF;
1188
1189 IF l_sign = '-' THEN
1190 l_String := '<' || l_String || '>';
1191 END IF;
1192
1193 RETURN l_String;
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 RETURN '00'||g_Time_Delim||'00';
1197 END get_hr_min;
1198
1199 FUNCTION get_local_chardt(p_location_id IN NUMBER, p_date IN DATE)
1200 RETURN VARCHAR2 IS
1201 l_localDate DATE ; --:= mst_geocoding.get_local_time(p_location_id, p_date);
1202 l_dateTimeStr VARCHAR2(30);
1203 BEGIN
1204 l_localDate := mst_geocoding.get_local_time(p_location_id, p_date);
1205 l_dateTimeStr := ltrim(rtrim(fnd_date.date_to_displayDT(l_localDate, fnd_timezones.get_server_timezone_code)));
1206 l_dateTimeStr := Substr(l_dateTimeStr,1,length(l_dateTimeStr)-3);
1207 RETURN l_dateTimeStr;
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 l_dateTimeStr := NULL;
1211 RETURN l_dateTimeStr;
1212 END get_local_chardt;
1213
1214 FUNCTION get_local_chardtzone(p_location_id IN NUMBER, p_date IN DATE)
1215 RETURN VARCHAR2 IS
1216 l_localDate DATE ; --:= mst_geocoding.get_local_time(p_location_id, p_date);
1217 l_timeZone VARCHAR2(10) ; --:= mst_geocoding.get_timezone_code(p_location_id, p_date);
1218 l_dateTimeStr VARCHAR2(30);
1219 BEGIN
1220 l_localDate := mst_geocoding.get_local_time(p_location_id, p_date);
1221 l_timeZone := mst_geocoding.get_timezone_code(p_location_id, p_date);
1222 l_dateTimeStr := ltrim(rtrim(fnd_date.date_to_displaydt(l_localDate, fnd_timezones.get_server_timezone_code)));
1223 l_dateTimeStr := Substr(l_dateTimeStr,1,length(l_dateTimeStr)-3)||' '||l_timeZone;
1224 RETURN l_dateTimeStr;
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 l_dateTimeStr := NULL;
1228 RETURN l_dateTimeStr;
1229 END get_local_chardtzone;
1230
1231 FUNCTION GET_LEG_NUMBER(P_PLAN_ID IN NUMBER,
1232 P_TRIP_ID IN NUMBER,
1233 P_STOP_ID IN NUMBER)
1234 RETURN NUMBER IS
1235
1236 CURSOR Cur_Leg_num IS
1237 SELECT ts.stop_id
1238 FROM mst_trip_stops ts
1239 WHERE plan_id = p_plan_Id
1240 AND ts.trip_id = p_trip_id
1241 ORDER BY ts.stop_sequence_number;
1242 l_stop_id NUMBER;
1243 l_leg NUMBER;
1244 BEGIN
1245 OPEN cur_leg_num;
1246 LOOP
1247 FETCH Cur_Leg_num INTO l_stop_id;
1248 l_leg := Cur_Leg_num%ROWCOUNT;
1249 EXIT WHEN Cur_Leg_num%NOTFOUND OR l_stop_id = p_stop_id;
1250 END LOOP;
1251 CLOSE Cur_Leg_num;
1252 RETURN l_leg;
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 RETURN 0;
1256 END get_leg_number;
1257
1258 FUNCTION GET_TRIP_UTILIZATION(P_PLAN_ID IN NUMBER,
1259 P_TRIP_ID IN NUMBER)
1260 RETURN NUMBER IS
1261 l_weight_utilization number;
1262 l_volume_utilization number;
1263 l_pallet_utilization number;
1264 l_trip_utilization number;
1265 BEGIN
1266 select nvl(peak_weight_utilization,0)
1267 into l_weight_utilization
1268 from mst_trips mt
1269 where mt.trip_id = p_trip_id
1270 and mt.plan_id = p_plan_id;
1271
1272 select nvl(peak_volume_utilization,0)
1273 into l_volume_utilization
1274 from mst_trips mt
1275 where mt.trip_id = p_trip_id
1276 and mt.plan_id = p_plan_id;
1277
1278 select nvl(peak_pallet_utilization,0)
1279 into l_pallet_utilization
1280 from mst_trips mt
1281 where mt.trip_id = p_trip_id
1282 and mt.plan_id = p_plan_id;
1283
1284 l_trip_utilization := l_weight_utilization;
1285 if (l_trip_utilization < l_volume_utilization) then
1286 l_trip_utilization := l_volume_utilization;
1287 end if;
1288 if (l_trip_utilization < l_pallet_utilization) then
1289 l_trip_utilization := l_pallet_utilization;
1290 end if;
1291 return l_trip_utilization;
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 RETURN 0;
1295 END GET_TRIP_UTILIZATION;
1296
1297
1298 FUNCTION GET_TRIP_REMAINING_TIME(P_PLAN_ID IN NUMBER,
1299 P_TRIP_ID IN NUMBER)
1300 RETURN NUMBER IS
1301 l_time_remaining number;
1302 BEGIN
1303 select (trip_start_date - sysdate)
1304 into l_time_remaining
1305 from mst_trips
1306 where plan_id = p_plan_id
1307 and trip_id = p_trip_id;
1308 if l_time_remaining < 0 then
1309 return 0;
1310 else
1311 return l_time_remaining*24; -- in hours
1312 end if;
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 RETURN 0;
1316 END GET_TRIP_REMAINING_TIME;
1317
1318 FUNCTION GET_CM_REMAINING_TIME(P_PLAN_ID IN NUMBER,
1319 P_CM_ID IN NUMBER)
1320 RETURN NUMBER IS
1321 l_time_remaining number;
1322 BEGIN
1323 select (start_date - sysdate)
1324 into l_time_remaining
1325 from mst_cm_trips
1326 where plan_id = p_plan_id
1327 and continuous_move_id = p_cm_id;
1328 if l_time_remaining < 0 then
1329 return 0;
1330 else
1331 return l_time_remaining*24; -- in hours
1332 end if;
1333 EXCEPTION
1334 WHEN OTHERS THEN
1335 RETURN 0;
1336 END GET_CM_REMAINING_TIME;
1337
1338 FUNCTION GET_TRIP_TOKENIZED_EXCEPTION(P_PLAN_ID IN NUMBER,
1339 P_EXCEPTION_DETAIL_ID IN NUMBER,
1340 P_TRIP_ID IN NUMBER,
1341 P_LINE_NUM IN NUMBER)
1342 RETURN VARCHAR2 IS
1343 l_message VARCHAR2(2000);
1344 l_exception_type NUMBER;
1345 l_temp VARCHAR2(2500);
1346 l_temp1 VARCHAR2(2500);
1347 l_temp2 varchar2(2500);
1348 l_temp3 varchar2(2500);
1349 l_id1 NUMBER;
1350 l_id2 NUMBER;
1351 l_id3 NUMBER;
1352 l_id4 NUMBER;
1353 l_id5 NUMBER;
1354 l_id6 NUMBER;
1355 l_id7 NUMBER;
1356 l_id8 NUMBER;
1357 l_str1 VARCHAR2(2500);
1358
1359 l_date1 date;
1360 l_date2 date;
1361 BEGIN
1362 SELECT exception_type
1363 INTO l_exception_type
1364 FROM mst_exception_details
1365 WHERE plan_id = P_PLAN_ID
1366 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1367
1368 -- added by giyer
1369 IF l_exception_type = 220 THEN --Item Mode Incompatibility
1370 IF P_LINE_NUM = 1 THEN
1371 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_220_1');
1372 --Trip_number, mode_of_transport
1373 SELECT mt.trip_number, wlk.meaning
1374 INTO l_id1, l_temp
1375 FROM mst_trips mt, wsh_lookups wlk
1376 WHERE plan_id = P_PLAN_ID
1377 AND trip_id = P_TRIP_ID
1378 and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
1379 and mt.mode_of_transport = wlk.lookup_code;
1380 fnd_message.set_token('TRIP_NUMBER', l_id1);
1381 fnd_message.set_token('MODE_OF_TRANSPORT', l_temp);
1382 -- item_description
1383 select description, med.delivery_detail_id
1384 into l_temp1, l_id2
1385 from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1386 where med.plan_id=p_plan_id
1387 and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
1388 and med.delivery_detail_id = mdd.delivery_detail_id
1389 and med.plan_id = mdd.plan_id
1390 and mdd.inventory_item_id = msitl.inventory_item_id
1391 and mdd.organization_id = msitl.organization_id
1392 and msitl.language = userenv('LANG');
1393 fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
1394 fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1395 l_message := fnd_message.get;
1396 END IF;
1397
1398 ELSIF l_exception_type = 221 THEN --Item Carrier Incompatibility
1399 IF P_LINE_NUM = 1 THEN
1400 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_221_1');
1401 --Trip_number
1402 SELECT trip_number, carrier_id
1403 INTO l_id1, l_id2
1404 FROM mst_trips
1405 WHERE plan_id = P_PLAN_ID
1406 AND trip_id = P_TRIP_ID;
1407 -- item_description
1408 select description, med.delivery_detail_id
1409 into l_temp, l_id3
1410 from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1411 where med.plan_id=p_plan_id
1412 and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
1413 and med.delivery_detail_id = mdd.delivery_detail_id
1414 and med.plan_id=mdd.plan_id
1415 and mdd.inventory_item_id = msitl.inventory_item_id
1416 and mdd.organization_id = msitl.organization_id
1417 and msitl.language = userenv('LANG');
1418 --carrier name
1419 SELECT freight_code into l_temp1
1420 FROM wsh_carriers
1421 WHERE carrier_id = l_id2;
1422 fnd_message.set_token('TRIP_NUMBER', l_id1);
1423 fnd_message.set_token('DELIVERY_DETAIL_ID', l_id3);
1424 fnd_message.set_token('CARRIER_NAME', l_temp1);
1425 fnd_message.set_token('ITEM_DESCRIPTION', l_temp);
1426 l_message := fnd_message.get;
1427 END IF;
1428
1429 ELSIF l_exception_type = 222 THEN --Ship Set Violation
1430 IF P_LINE_NUM = 1 THEN
1431 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_222_1');
1432 --Ship_set
1433 select number1
1434 into l_id1
1435 from mst_exception_details med
1436 where med.exception_detail_id = p_exception_detail_id
1437 and med.plan_id=p_plan_id;
1438 -- Ship_set Name
1439 select set_name into l_temp
1440 from oe_sets os
1441 where os.set_id=l_id1;
1442 fnd_message.set_token('SHIP_SET', l_temp);
1443 l_message := fnd_message.get;
1444 END IF;
1445
1446 ELSIF l_exception_type = 223 THEN -- Arrival Set Violation
1447 IF P_LINE_NUM = 1 THEN
1448 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_223_1');
1449 --Arrival_set
1450 select number1
1451 into l_id1
1452 from mst_exception_details med
1453 where med.exception_detail_id = p_exception_detail_id
1454 and med.plan_id=p_plan_id;
1455 --Arrival_set Name
1456 select set_name into l_temp
1457 from oe_sets os
1458 where os.set_id=l_id1;
1459 fnd_message.set_token('ARRIVAL_SET', l_temp);
1460 l_message := fnd_message.get;
1461 END IF;
1462
1463 ELSIF l_exception_type = 301 THEN --Carrier Commitment Underutilization
1464 IF P_LINE_NUM = 1 THEN
1465 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
1466 --start_date, end_date, shortfall
1467 select carrier_id
1468 , fnd_date.DATE_TO_CHARDATE(date1)
1469 , fnd_date.DATE_TO_CHARDATE(date2)
1470 , decode ( fsrr.attribute_name
1471 , 'SPEND'
1472 , to_char ( ( med.number3 - med.number2 ) , fnd_currency.get_format_mask ( fsl.uom_code, 67 ) )
1473 , to_char( round ( med.number3 ) - round ( med.number2 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) )
1474 into l_id2,l_temp1,l_temp2,l_temp3
1475 from mst_exception_details med
1476 , fte_sel_rule_restrictions fsrr
1477 , fte_sel_rules fsl
1478 where med.exception_detail_id = p_exception_detail_id
1479 and med.number1 = fsrr.rule_id
1480 and med.number1 = fsl.rule_id
1481 and med.plan_id=p_plan_id;
1482 fnd_message.set_token('START_DATE', l_temp1);
1483 fnd_message.set_token('END_DATE', l_temp2);
1484 fnd_message.set_token('SHORTFALL', l_temp3);
1485 --Carrier name
1486 SELECT freight_code INTO l_temp
1487 FROM wsh_carriers
1488 WHERE carrier_id = l_id2;
1489 fnd_message.set_token('CARRIER', l_temp);
1490 --get Lane_Number
1491 select LANE
1492 into l_temp
1493 from(
1494 select fl.lane_number "LANE"
1495 from fte_lanes fl, mst_exception_details med
1496 where med.lane_id = fl.lane_id
1497 and med.exception_detail_id=p_exception_detail_id
1498 and med.plan_id=p_plan_id
1499 union all
1500 select flg.name "LANE"
1501 from fte_lane_groups flg, mst_exception_details med
1502 where med.lane_set_id = flg.lane_group_id
1503 and med.exception_detail_id=p_exception_detail_id
1504 and med.plan_id=p_plan_id);
1505 --as per dld, Lane_Number = fl.lane_number or flg.name whichever is not null
1506 fnd_message.set_token('LANE_NAME', l_temp);
1507 l_message := fnd_message.get;
1508 END IF;
1509
1510 ELSIF l_exception_type = 406 THEN --Carrier Facility Appointment violation
1511 IF P_LINE_NUM = 1 THEN
1512 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_406_1');
1513 --facility_description
1514 select flp.facility_code
1515 into l_temp
1516 from fte_location_parameters flp, mst_trip_stops mts, mst_exception_details med
1517 where mts.stop_id = med.stop_id1
1518 and mts.stop_location_id = flp.location_id
1519 and med.exception_detail_id = p_exception_detail_id
1520 and med.plan_id = mts.plan_id
1521 and med.plan_id = p_plan_id;
1522 fnd_message.set_token('FACILITY_DESCRIPTION', l_temp);
1523 --trip_number,carrier_name
1524 SELECT trip_number, carrier_id
1525 INTO l_id1, l_id2
1526 FROM mst_trips
1527 WHERE plan_id = P_PLAN_ID
1528 AND trip_id = P_TRIP_ID;
1529 --carrier name
1530 SELECT freight_code INTO l_temp
1531 FROM wsh_carriers
1532 WHERE carrier_id = l_id2;
1533 fnd_message.set_token('CARRIER_NAME', l_temp);
1534 fnd_message.set_token('TRIP_NUMBER', l_id1);
1535 l_message := fnd_message.get;
1536 END IF;
1537
1538 ELSIF l_exception_type = 602 THEN --Item Vehicle Incompatibility
1539 IF P_LINE_NUM = 1 THEN
1540 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_602_1');
1541 --Trip_number, item_description,vehicle_type
1542 --Trip_number
1543 SELECT mt.trip_number,vehicle_type_id
1544 INTO l_id1,l_id2
1545 FROM mst_trips mt
1546 WHERE plan_id = P_PLAN_ID
1547 AND trip_id = P_TRIP_ID;
1548 fnd_message.set_token('TRIP_NUMBER', l_id1);
1549 -- item_description
1550 select msitl.description into l_temp1
1551 from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1552 where med.plan_id=p_plan_id
1553 and med.plan_id = mdd.plan_id
1554 and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
1555 and med.delivery_detail_id = mdd.delivery_detail_id
1556 and mdd.inventory_item_id = msitl.inventory_item_id
1557 and mdd.organization_id = msitl.organization_id
1558 and msitl.language = userenv('LANG');
1559 fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1560 -- vehicle_type
1561 SELECT msikfv.concatenated_segments
1562 into l_temp
1563 FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
1564 WHERE fvt.vehicle_type_id = l_id2
1565 AND fvt.organization_id = msikfv.organization_id
1566 AND fvt.inventory_item_id = msikfv.inventory_item_id;
1567 fnd_message.set_token('VEHICLE_TYPE', l_temp);
1568 l_message := fnd_message.get;
1569 END IF;
1570
1571 ELSIF l_exception_type = 705 THEN --Facility Item Incompatibility
1572 IF P_LINE_NUM = 1 THEN
1573 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_705_1');
1574 --Trip_Number, Item_description, facility_description
1575 --Trip_number
1576 SELECT mt.trip_number
1577 INTO l_id1
1578 FROM mst_trips mt
1579 WHERE plan_id = P_PLAN_ID
1580 AND trip_id = P_TRIP_ID;
1581 fnd_message.set_token('TRIP_NUMBER', l_id1);
1582 -- item_description
1583 select msitl.description into l_temp1
1584 from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1585 where med.plan_id=p_plan_id
1586 and med.plan_id = mdd.plan_id
1587 and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
1588 and med.delivery_detail_id = mdd.delivery_detail_id
1589 and mdd.inventory_item_id = msitl.inventory_item_id
1590 and mdd.organization_id = msitl.organization_id
1591 and msitl.language = userenv('LANG');
1592 fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1593 -- Facility_Description
1594 select flp.description
1595 into l_temp
1596 from fte_location_parameters flp, mst_exception_details med
1597 where
1598 med.location_id = flp.location_id
1599 and med.plan_id=p_plan_id
1600 and med.exception_detail_id=p_exception_detail_id;
1601 fnd_message.set_token('FACILITY_DESCRIPTION', l_temp);
1602 l_message := fnd_message.get;
1603 END IF;
1604
1605 ELSIF l_exception_type = 706 THEN --Facility Mode Incompatibility
1606 IF P_LINE_NUM = 1 THEN
1607 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_706_1');
1608 --Trip_number, mode_of_transport, facility_description
1609 --Trip_number, mode_of_transport
1610 SELECT mt.trip_number, wlk.meaning
1611 INTO l_id1, l_temp
1612 FROM mst_trips mt, wsh_lookups wlk
1613 WHERE plan_id = P_PLAN_ID
1614 AND trip_id = P_TRIP_ID
1615 and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
1616 and mt.mode_of_transport = wlk.lookup_code;
1617 fnd_message.set_token('TRIP_NUMBER', l_id1);
1618 fnd_message.set_token('MODE_OF_TRANSPORT', l_temp);
1619 --facility_description
1620 select flp.description
1621 into l_temp1
1622 from fte_location_parameters flp, mst_exception_details med
1623 where
1624 med.location_id = flp.location_id
1625 and med.plan_id=p_plan_id
1626 and med.exception_detail_id=p_exception_detail_id;
1627 fnd_message.set_token('FACILITY_DESCRIPTION', l_temp1);
1628 l_message := fnd_message.get;
1629 END IF;
1630
1631 ELSIF l_exception_type = 707 THEN --Facility Facility Incompatibility
1632 IF P_LINE_NUM = 1 THEN
1633 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_707_1');
1634 --Delivery_number
1635 select md.delivery_number
1636 into l_id1
1637 from mst_deliveries md, mst_exception_details med
1638 where med.delivery_id = md.delivery_id
1639 and med.plan_id=p_plan_id
1640 and med.plan_id = md.plan_id
1641 and med.exception_detail_id=p_exception_detail_id;
1642 fnd_message.set_token('DELIVERY_NUMBER', l_id1);
1643 --End_facility_name, intermediate_facility_name
1644 select flp1.facility_code, flp2.facility_code
1645 into l_temp1, l_temp2
1646 from fte_location_parameters flp1, fte_location_parameters flp2, mst_exception_details med
1647 where med.location_id = flp1.location_id
1648 and med.number1 = flp2.location_id
1649 and med.plan_id = p_plan_id
1650 and med.exception_detail_id = p_exception_detail_id;
1651 fnd_message.set_token('END_FACILITY_NAME', l_temp1);
1652 fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_temp2);
1653 l_message := fnd_message.get;
1654 END IF;-- end giyer
1655
1656 ELSIF l_exception_type = 200 THEN
1657 IF P_LINE_NUM = 1 THEN
1658 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_200_1');
1659 SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1660 INTO l_id1, l_id2, l_id3, l_id4
1661 FROM mst_trips
1662 WHERE plan_id = P_PLAN_ID
1663 AND trip_id = P_TRIP_ID;
1664 fnd_message.set_token('TRIP_NUMBER', l_id4);
1665
1666 SELECT freight_code INTO l_temp
1667 FROM wsh_carriers
1668 WHERE carrier_id = l_id1;
1669 fnd_message.set_token('FREIGHT_CODE', l_temp);
1670
1671 SELECT city INTO l_temp
1672 FROM wsh_locations
1673 WHERE wsh_location_id = l_id2;
1674 fnd_message.set_token('ORIGIN_CITY', l_temp);
1675
1676 SELECT city INTO l_temp
1677 FROM wsh_locations
1678 WHERE wsh_location_id = l_id3;
1679 fnd_message.set_token('DESTINATION_CITY', l_temp);
1680
1681 SELECT distance_uom
1682 INTO l_str1
1683 FROM mst_plans
1684 WHERE plan_id = P_PLAN_ID;
1685 fnd_message.set_token('DISTANCE_UOM', l_str1);
1686
1687 SELECT round(number3), round(number4)
1688 INTO l_id1, l_id2
1689 FROM mst_exception_details
1690 WHERE plan_id = P_PLAN_ID
1691 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1692 fnd_message.set_token('MAX_DISTANCE_IN_24HR', l_id2);
1693 fnd_message.set_token('DISTANCE_IN_24HR', l_id1);
1694
1695 SELECT round(number1), round(number2)
1696 INTO l_id1, l_id2
1697 FROM mst_exception_details
1698 WHERE plan_id = P_PLAN_ID
1699 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1700 fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
1701 fnd_message.set_token('DISTANCE', l_id1);
1702 l_message := fnd_message.get;
1703
1704 END IF;
1705 ELSIF l_exception_type = 207 THEN
1706 IF P_LINE_NUM = 1 THEN
1707 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_207_1');
1708 SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1709 INTO l_id1, l_id2, l_id3, l_id4
1710 FROM mst_trips
1711 WHERE plan_id = P_PLAN_ID
1712 AND trip_id = P_TRIP_ID;
1713 fnd_message.set_token('TRIP_NUMBER', l_id4);
1714
1715 SELECT freight_code INTO l_temp
1716 FROM wsh_carriers
1717 WHERE carrier_id = l_id1;
1718 fnd_message.set_token('FREIGHT_CODE', l_temp);
1719
1720 SELECT city INTO l_temp
1721 FROM wsh_locations
1722 WHERE wsh_location_id = l_id2;
1723 fnd_message.set_token('ORIGIN_CITY', l_temp);
1724
1725 SELECT city INTO l_temp
1726 FROM wsh_locations
1727 WHERE wsh_location_id = l_id3;
1728 fnd_message.set_token('DESTINATION_CITY', l_temp);
1729
1730 SELECT nvl(round(wcs.max_driving_time_in_24hr),0)
1731 INTO l_id2
1732 FROM wsh_carrier_services wcs,
1733 mst_exception_details med
1734 WHERE wcs.carrier_service_id = med.carrier_service_id
1735 AND med.plan_id = P_PLAN_ID
1736 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1737 fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id2);
1738
1739 SELECT nvl(round(number1),0)
1740 INTO l_id1
1741 FROM mst_exception_details
1742 WHERE plan_id = P_PLAN_ID
1743 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1744 fnd_message.set_token('DRIVING_TIME', l_id1);
1745
1746 SELECT nvl(round(wcs.max_total_time),0)
1747 INTO l_id2
1748 FROM wsh_carrier_services wcs,
1749 mst_exception_details med
1750 WHERE wcs.carrier_service_id = med.carrier_service_id
1751 AND med.plan_id = P_PLAN_ID
1752 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1753 fnd_message.set_token('MAX_TIME', l_id2);
1754
1755 SELECT nvl(round(wcs.max_duty_time_in_24hr),0)
1756 INTO l_id2
1757 FROM wsh_carrier_services wcs,
1758 mst_exception_details med
1759 WHERE wcs.carrier_service_id = med.carrier_service_id
1760 AND med.plan_id = P_PLAN_ID
1761 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1762 fnd_message.set_token('ALLOWED_DUTY_TIME', l_id2);
1763
1764 SELECT nvl(round(number2),0)
1765 INTO l_id1
1766 FROM mst_exception_details
1767 WHERE plan_id = P_PLAN_ID
1768 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1769 fnd_message.set_token('DUTY_TIME', l_id1);
1770
1771 SELECT nvl(round(wcs.min_layover_time),0)
1772 INTO l_id2
1773 FROM wsh_carrier_services wcs,
1774 mst_exception_details med
1775 WHERE wcs.carrier_service_id = med.carrier_service_id
1776 AND med.plan_id = P_PLAN_ID
1777 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1778 fnd_message.set_token('MIN_LAYOVER_TIME', l_id2);
1779
1780 SELECT nvl(round(number3),0)
1781 INTO l_id1
1782 FROM mst_exception_details
1783 WHERE plan_id = P_PLAN_ID
1784 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1785 fnd_message.set_token('LAYOVER_TIME', l_id1);
1786
1787 SELECT nvl(round(((trip_end_date - trip_start_date) * 24)),0)
1788 INTO l_id1
1789 FROM mst_trips
1790 WHERE plan_id = P_PLAN_ID
1791 AND trip_id = P_TRIP_ID;
1792 fnd_message.set_token('TIME', l_id1);
1793
1794 l_message := fnd_message.get;
1795 END IF;
1796 ELSIF l_exception_type = 206 THEN
1797 IF P_LINE_NUM = 1 THEN
1798 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
1799 SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1800 INTO l_id1, l_id2, l_id3, l_id4
1801 FROM mst_trips
1802 WHERE plan_id = P_PLAN_ID
1803 AND trip_id = P_TRIP_ID;
1804 fnd_message.set_token('TRIP_NUMBER', l_id4);
1805
1806 SELECT freight_code INTO l_temp
1807 FROM wsh_carriers
1808 WHERE carrier_id = l_id1;
1809 fnd_message.set_token('FREIGHT_CODE', l_temp);
1810
1811 SELECT city INTO l_temp
1812 FROM wsh_locations
1813 WHERE wsh_location_id = l_id2;
1814 fnd_message.set_token('ORIGIN_CITY', l_temp);
1815
1816 SELECT city INTO l_temp
1817 FROM wsh_locations
1818 WHERE wsh_location_id = l_id3;
1819 fnd_message.set_token('DESTINATION_CITY', l_temp);
1820 l_message := fnd_message.get;
1821 ELSIF P_LINE_NUM = 2 THEN
1822 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
1823 SELECT number1, number2
1824 INTO l_id1, l_id2
1825 FROM mst_exception_details
1826 WHERE plan_id = P_PLAN_ID
1827 AND trip_id1 = P_TRIP_ID
1828 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1829 fnd_message.set_token('STOPS', l_id1);
1830 fnd_message.set_token('MAX_STOPS', l_id2);
1831 l_message := fnd_message.get;
1832 END IF;
1833 ELSIF l_exception_type = 208 THEN
1834 IF P_LINE_NUM = 1 THEN
1835 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
1836 SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1837 INTO l_id1, l_id2, l_id3, l_id4
1838 FROM mst_trips
1839 WHERE plan_id = P_PLAN_ID
1840 AND trip_id = P_TRIP_ID;
1841 fnd_message.set_token('TRIP_NUMBER', l_id4);
1842
1843 SELECT freight_code INTO l_temp
1844 FROM wsh_carriers
1845 WHERE carrier_id = l_id1;
1846 fnd_message.set_token('FREIGHT_CODE', l_temp);
1847
1848 SELECT city INTO l_temp
1849 FROM wsh_locations
1850 WHERE wsh_location_id = l_id2;
1851 fnd_message.set_token('ORIGIN_CITY', l_temp);
1852
1853 SELECT city INTO l_temp
1854 FROM wsh_locations
1855 WHERE wsh_location_id = l_id3;
1856 fnd_message.set_token('DESTINATION_CITY', l_temp);
1857 l_message := fnd_message.get;
1858 END IF;
1859 ELSIF l_exception_type = 201 THEN
1860 IF P_LINE_NUM = 1 THEN
1861 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
1862 SELECT distance_uom INTO l_temp
1863 FROM mst_plans
1864 WHERE plan_id = P_PLAN_ID;
1865 fnd_message.set_token('DISTANCE_UOM', l_temp);
1866
1867 SELECT carrier_id, continuous_move_id, round(number2), round(number3)
1868 INTO l_id1, l_id2, l_id3, l_id4
1869 FROM mst_exception_details
1870 WHERE plan_id = P_PLAN_ID
1871 AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1872
1873 fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id3);
1874 fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id4);
1875
1876 SELECT freight_code INTO l_temp
1877 FROM wsh_carriers
1878 WHERE carrier_id = l_id1;
1879 fnd_message.set_token('FREIGHT_CODE', l_temp);
1880
1881 SELECT cm_trip_number, start_location_id, end_location_id
1882 INTO l_id1, l_id3, l_id4
1883 FROM mst_cm_trips
1884 WHERE plan_id = P_PLAN_ID
1885 AND continuous_move_id = l_id2;
1886 fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
1887
1888 SELECT city INTO l_temp
1889 FROM wsh_locations
1890 WHERE wsh_location_id = l_id3;
1891 fnd_message.set_token('ORIGIN_CITY', l_temp);
1892
1893 SELECT city INTO l_temp
1894 FROM wsh_locations
1895 WHERE wsh_location_id = l_id4;
1896 fnd_message.set_token('DESTINATION_CITY', l_temp);
1897 l_message := fnd_message.get;
1898 END IF;
1899 ELSIF l_exception_type = 202 THEN
1900 IF P_LINE_NUM = 1 THEN
1901 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
1902 SELECT origin_location_id, destination_location_id, trip_number
1903 INTO l_id2, l_id3, l_id4
1904 FROM mst_trips
1905 WHERE plan_id = P_PLAN_ID
1906 AND trip_id = P_TRIP_ID;
1907 fnd_message.set_token('TRIP_NUMBER', l_id4);
1908
1909 SELECT city INTO l_temp
1910 FROM wsh_locations
1911 WHERE wsh_location_id = l_id2;
1912 fnd_message.set_token('ORIGIN_CITY', l_temp);
1913
1914 SELECT city INTO l_temp
1915 FROM wsh_locations
1916 WHERE wsh_location_id = l_id3;
1917 fnd_message.set_token('DESTINATION_CITY', l_temp);
1918 l_message := fnd_message.get;
1919 END IF;
1920 ELSIF l_exception_type = 203 THEN
1921 IF P_LINE_NUM = 1 THEN
1922 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
1923 SELECT trip_number
1924 INTO l_id1
1925 FROM mst_trips
1926 WHERE plan_id = P_PLAN_ID
1927 AND trip_id = P_TRIP_ID;
1928 fnd_message.set_token('TRIP_NUMBER', l_id1);
1929
1930 SELECT mts.stop_sequence_number, mts.stop_location_id
1931 INTO l_id2, l_id3
1932 FROM mst_trip_stops mts,
1933 mst_exception_details med
1934 WHERE mts.plan_id = med.plan_id
1935 AND med.plan_id = P_PLAN_ID
1936 AND mts.trip_id = med.trip_id1
1937 AND mts.stop_id = med.stop_id1
1938 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1939 fnd_message.set_token('STOP_SEQUENCE_NUMBER', l_id2);
1940
1941 SELECT city INTO l_temp
1942 FROM wsh_locations
1943 WHERE wsh_location_id = l_id3;
1944 fnd_message.set_token('CITY', l_temp);
1945
1946 l_message := fnd_message.get;
1947 END IF;
1948 ELSIF l_exception_type = 204 THEN
1949 IF P_LINE_NUM = 1 THEN
1950 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
1951 SELECT trip_number
1952 INTO l_id1
1953 FROM mst_trips
1954 WHERE plan_id = P_PLAN_ID
1955 AND trip_id = P_TRIP_ID;
1956 fnd_message.set_token('TRIP_NUMBER', l_id1);
1957
1958 SELECT mts.stop_location_id, mts.planned_departure_date, round(med.number1)
1959 INTO l_id1, l_date1, l_id3
1960 FROM mst_trip_stops mts,
1961 mst_exception_details med
1962 WHERE mts.plan_id = med.plan_id
1963 AND mts.stop_id = med.stop_id1
1964 AND med.plan_id = P_PLAN_ID
1965 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1966
1967 SELECT city INTO l_temp
1968 FROM wsh_locations
1969 WHERE wsh_location_id = l_id1;
1970 fnd_message.set_token('ORIGIN_CITY', l_temp);
1971 fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id3);
1972
1973 SELECT mts.stop_location_id, mts.planned_arrival_date
1974 INTO l_id3, l_date2
1975 FROM mst_trip_stops mts,
1976 mst_exception_details med
1977 WHERE mts.plan_id = med.plan_id
1978 AND mts.stop_id = med.stop_id2
1979 AND med.plan_id = P_PLAN_ID
1980 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1981
1982 SELECT city INTO l_temp
1983 FROM wsh_locations
1984 WHERE wsh_location_id = l_id3;
1985 fnd_message.set_token('DESTINATION_CITY', l_temp);
1986
1987 l_id1 := round(((l_date2 - l_date1) * 24));
1988 fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id1);
1989 l_message := fnd_message.get;
1990 END IF;
1991 ELSIF l_exception_type = 205 THEN
1992 IF P_LINE_NUM = 1 THEN
1993 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
1994 SELECT trip_number
1995 INTO l_id1
1996 FROM mst_trips
1997 WHERE plan_id = P_PLAN_ID
1998 AND trip_id = P_TRIP_ID;
1999 fnd_message.set_token('TRIP_NUMBER', l_id1);
2000
2001 SELECT round(nvl(number1, 0)), round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0))
2002 INTO l_id1, l_id2, l_id3, l_id7
2003 FROM mst_exception_details med
2004 WHERE exception_detail_id = P_EXCEPTION_DETAIL_ID
2005 AND plan_id = P_PLAN_ID;
2006
2007 SELECT round(nvl(max_drive_time, 0)), round(nvl(max_duty_time, 0)), round(nvl(minimum_lay_time, 0)), round(nvl(max_driving_distance, 0))
2008 INTO l_id4, l_id5, l_id6, l_id8
2009 FROM mst_parameters
2010 WHERE user_id = -9999;
2011
2012 fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id4);
2013 fnd_message.set_token('DRIVING_TIME', l_id2);
2014 fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015 fnd_message.set_token('DUTY_TIME', l_id3);
2016 fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017 fnd_message.set_token('LAYOVER_TIME', l_id1);
2018 fnd_message.set_token('MAX_DISTANCE', l_id8);
2019 fnd_message.set_token('DISTANCE', l_id7);
2020
2021 l_message := fnd_message.get;
2022 END IF;
2023 ELSIF l_exception_type = 400 THEN
2024 IF P_LINE_NUM = 1 THEN
2025 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
2026 SELECT trip_number
2027 INTO l_id1
2028 FROM mst_trips
2029 WHERE plan_id = P_PLAN_ID
2030 AND trip_id = P_TRIP_ID;
2031 fnd_message.set_token('TRIP_NUMBER', l_id1);
2032
2033 SELECT description into l_temp
2034 FROM fte_location_parameters
2035 WHERE location_id in (SELECT mts.stop_location_id
2036 FROM mst_trip_stops mts,
2037 mst_exception_details med
2038 WHERE mts.plan_id = med.plan_id
2039 AND mts.stop_id = med.stop_id1
2040 AND med.plan_id = P_PLAN_ID
2041 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID);
2042 fnd_message.set_token('FACILITY_DESC', l_temp);
2043
2044 l_message := fnd_message.get;
2045 END IF;
2046 ELSIF l_exception_type = 401 THEN
2047 IF P_LINE_NUM = 1 THEN
2048 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
2049 SELECT trip_number
2050 INTO l_id1
2051 FROM mst_trips
2052 WHERE plan_id = P_PLAN_ID
2053 AND trip_id = P_TRIP_ID;
2054 fnd_message.set_token('TRIP_NUMBER', l_id1);
2055
2056 SELECT flp.description, round(nvl(med.number1, 0), 1), round((mts.planned_departure_date - mts.planned_arrival_date) * 24, 1)
2057 INTO l_temp, l_id1, l_id2
2058 FROM fte_location_parameters flp,
2059 mst_trip_stops mts,
2060 mst_exception_details med
2061 WHERE flp.location_id = mts.stop_location_id
2062 AND mts.plan_id = med.plan_id
2063 AND mts.stop_id = med.stop_id1
2064 AND med.plan_id = P_PLAN_ID
2065 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2066 fnd_message.set_token('FACILITY_DESC', l_temp);
2067 fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id1, '999990.0')));
2068 fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id2, '999990.0')));
2069
2070 l_message := fnd_message.get;
2071 END IF;
2072 ELSIF l_exception_type = 500 THEN
2073 IF P_LINE_NUM = 1 THEN
2074 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
2075 SELECT trip_number, origin_location_id, destination_location_id, carrier_id,
2076 round((nvl(total_basic_transport_cost,0) + nvl(total_accessorial_cost,0) + nvl(total_layover_cost,0) + nvl(total_load_unload_cost,0) + nvl(total_stop_cost,0)))
2077 INTO l_id1, l_id2, l_id3, l_id4, l_id5
2078 FROM mst_trips
2079 WHERE plan_id = P_PLAN_ID
2080 AND trip_id = P_TRIP_ID;
2081 fnd_message.set_token('TRIP_NUMBER', l_id1);
2082
2083 SELECT freight_code
2084 INTO l_temp
2085 FROM wsh_carriers
2086 WHERE carrier_id = l_id4;
2087 fnd_message.set_token('FREIGHT_CODE', l_temp);
2088
2089 SELECT city
2090 INTO l_temp
2091 FROM wsh_locations
2092 WHERE wsh_location_id = l_id2;
2093 fnd_message.set_token('ORIGIN_CITY', l_temp);
2094
2095 SELECT city
2096 INTO l_temp
2097 FROM wsh_locations
2098 WHERE wsh_location_id = l_id3;
2099 fnd_message.set_token('DESTINATION_CITY', l_temp);
2100
2101 SELECT wc.freight_code, round(nvl(med.number1, 0))
2102 INTO l_temp, l_id1
2103 FROM wsh_carriers wc,
2104 mst_exception_details med
2105 WHERE med.plan_id = P_PLAN_ID
2106 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2107 AND med.carrier_id = wc.carrier_id;
2108
2109 fnd_message.set_token('LOW_COST_CARRIER_COST', l_id1);
2110 fnd_message.set_token('LOW_COST_CARRIER', l_temp);
2111 fnd_message.set_token('COST', l_id5);
2112
2113 l_message := fnd_message.get;
2114 END IF;
2115 ELSIF l_exception_type = 501 THEN
2116 IF P_LINE_NUM = 1 THEN
2117 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
2118 SELECT trip_number
2119 INTO l_id1
2120 FROM mst_trips
2121 WHERE plan_id = P_PLAN_ID
2122 AND trip_id = P_TRIP_ID;
2123 fnd_message.set_token('TRIP_NUMBER', l_id1);
2124
2125 l_message := fnd_message.get;
2126 END IF;
2127 ELSIF l_exception_type = 600 THEN
2128 IF P_LINE_NUM = 1 THEN
2129 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_600_1');
2130 SELECT mt.trip_number, mt.peak_weight_utilization,
2131 mt.peak_volume_utilization,
2132 mt.peak_pallet_utilization,
2133 med.number2,
2134 med.number1,
2135 med.number3,
2136 med.vehicle_type_id
2137 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
2138 FROM mst_trips mt,
2139 mst_exception_details med
2140 WHERE mt.plan_id = med.plan_id
2141 AND med.plan_id = P_PLAN_ID
2142 AND mt.trip_id = P_TRIP_ID
2143 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2144 fnd_message.set_token('TRIP_NUMBER', l_id1);
2145
2146 fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2147 fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148 fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149
2150 fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151 fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152 fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153
2154 fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2155 fnd_message.set_token('MAX_VOLUME', round(l_id6));
2156 fnd_message.set_token('MAX_PALLETS', round(l_id7));
2157
2158 SELECT volume_uom
2159 INTO l_temp
2160 FROM mst_plans
2161 WHERE plan_id = P_PLAN_ID;
2162
2163 SELECT msikfv.internal_volume * get_uom_conversion_rate (msikfv.volume_uom_code,
2164 l_temp,
2165 msikfv.organization_id,
2166 msikfv.inventory_item_id)
2167 INTO l_id1
2168 FROM mtl_system_items_kfv msikfv,
2169 fte_vehicle_types fvt
2170 WHERE msikfv.inventory_item_id = fvt.inventory_item_id
2171 AND msikfv.organization_id = fvt.organization_id
2172 AND fvt.vehicle_type_id = l_id8;
2173
2174 If l_id1 is not null Then
2175 fnd_message.set_token('PHYSICAL_CAPACITY', l_id1);
2176 Else
2177 fnd_message.set_token('PHYSICAL_CAPACITY', '');
2178 End If;
2179
2180 SELECT weight_uom, volume_uom
2181 INTO l_temp, l_temp1
2182 FROM mst_plans
2183 WHERE plan_id = P_PLAN_ID;
2184
2185 fnd_message.set_token('WEIGHT_UOM', l_temp);
2186 fnd_message.set_token('VOLUME_UOM', l_temp1);
2187
2188 l_message := fnd_message.get;
2189 END IF;
2190 ELSIF l_exception_type = 601 THEN
2191 IF P_LINE_NUM = 1 THEN
2192 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_601_1');
2193 SELECT mt.trip_number, mt.peak_weight_utilization,
2194 mt.peak_volume_utilization,
2195 mt.peak_pallet_utilization,
2196 med.number2,
2197 med.number1,
2198 med.number3
2199 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
2200 FROM mst_trips mt,
2201 mst_exception_details med
2202 WHERE mt.plan_id = med.plan_id
2203 AND med.plan_id = P_PLAN_ID
2204 AND mt.trip_id = P_TRIP_ID
2205 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2206 fnd_message.set_token('TRIP_NUMBER', l_id1);
2207
2208 fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2209 fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210 fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211
2212 fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213 fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214 fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215
2216 fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2217 fnd_message.set_token('MAX_VOLUME', round(l_id6));
2218 fnd_message.set_token('MAX_PALLETS', round(l_id7));
2219
2220 SELECT weight_uom, volume_uom
2221 INTO l_temp, l_temp1
2222 FROM mst_plans
2223 WHERE plan_id = P_PLAN_ID;
2224
2225 fnd_message.set_token('WEIGHT_UOM', l_temp);
2226 fnd_message.set_token('VOLUME_UOM', l_temp1);
2227
2228 l_message := fnd_message.get;
2229 END IF;
2230 ELSIF l_exception_type = 800 THEN
2231 IF P_LINE_NUM = 1 THEN
2232 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
2233 SELECT trip_number
2234 INTO l_id1
2235 FROM mst_trips
2236 WHERE plan_id = P_PLAN_ID
2237 AND trip_id = P_TRIP_ID;
2238 fnd_message.set_token('TRIP_NUMBER', l_id1);
2239
2240 SELECT wc.freight_code
2241 INTO l_temp
2242 FROM mst_exception_details med,
2243 wsh_carriers wc
2244 WHERE med.plan_id = P_PLAN_ID
2245 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2246 AND med.carrier_id = wc.carrier_id;
2247 fnd_message.set_token('FREIGHT_CODE', l_temp);
2248
2249 SELECT Get_Partner_Name(decode(med.customer_id, null, med.supplier_id, med.customer_id),
2250 decode(med.customer_id, null, 2, 1))
2251 INTO l_temp
2252 FROM mst_exception_details med
2253 WHERE med.plan_id = P_PLAN_ID
2254 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2255
2256 fnd_message.set_token('TRADING_PARTNER', l_temp);
2257 fnd_message.set_token('FACILITY_DESC', 'facility description');
2258
2259 l_message := fnd_message.get;
2260 END IF;
2261 ELSIF l_exception_type = 801 THEN
2262 IF P_LINE_NUM = 1 THEN
2263 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
2264 SELECT trip_number, carrier_id
2265 INTO l_id1, l_id2
2266 FROM mst_trips
2267 WHERE plan_id = P_PLAN_ID
2268 AND trip_id = P_TRIP_ID;
2269 fnd_message.set_token('TRIP_NUMBER', l_id1);
2270
2271 SELECT wc.freight_code
2272 INTO l_temp
2273 FROM wsh_carriers wc
2274 WHERE wc.carrier_id = l_id2;
2275 fnd_message.set_token('FREIGHT_CODE', l_temp);
2276
2277 SELECT flp.description
2278 INTO l_temp
2279 FROM fte_location_parameters flp,
2280 mst_exception_details med,
2281 mst_trip_stops mts
2282 WHERE med.stop_id1 = mts.stop_id
2283 AND mts.plan_id = P_PLAN_ID
2284 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2285 AND mts.stop_location_id = flp.location_id;
2286 fnd_message.set_token('FACILITY_DESC', l_temp);
2287
2288 SELECT Get_Partner_Name(decode(med.customer_id, null, med.supplier_id, med.customer_id),
2289 decode(med.customer_id, null, 2, 1))
2290 INTO l_temp
2291 FROM mst_exception_details med
2292 WHERE med.plan_id = P_PLAN_ID
2293 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2294 fnd_message.set_token('CUST_SUPPLIER', l_temp);
2295 l_message := fnd_message.get;
2296 END IF;
2297 ELSIF l_exception_type = 803 THEN
2298 IF P_LINE_NUM = 1 THEN
2299 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_803_1');
2300 SELECT trip_number
2301 INTO l_id1
2302 FROM mst_trips
2303 WHERE plan_id = P_PLAN_ID
2304 AND trip_id = P_TRIP_ID;
2305 fnd_message.set_token('TRIP_NUMBER', l_id1);
2306
2307 SELECT Get_Partner_Name(med.customer_id, 1)
2308 INTO l_temp
2309 FROM mst_exception_details med
2310 WHERE med.plan_id = P_PLAN_ID
2311 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2312 fnd_message.set_token('CUSTOMER1', l_temp);
2313
2314 SELECT Get_Partner_Name(med.number1, 1)
2315 INTO l_temp
2316 FROM mst_exception_details med
2317 WHERE med.plan_id = P_PLAN_ID
2318 AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2319 fnd_message.set_token('CUSTOMER2', l_temp);
2320
2321 l_message := fnd_message.get;
2322 END IF;
2323 ELSE
2324 SELECT ml.meaning INTO l_message
2325 FROM mfg_lookups ml, mst_exception_details med
2326 WHERE med.exception_type = ml.lookup_code
2327 AND ml.lookup_type = 'MST_EXCEPTION_TYPE'
2328 and med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2329 END IF;
2330 RETURN l_message;
2331 EXCEPTION
2332 WHEN OTHERS THEN
2333 RETURN '';
2334 END GET_TRIP_TOKENIZED_EXCEPTION;
2335
2336 function get_contact_info (p_contact_id in number
2337 , p_ret_str_type in varchar2
2338 , p_owner_type_id in number default null)
2339 return varchar2 is
2340 -- SQL repository issues as on 25-05-04:
2341 -- Removed distinct clause
2342 cursor cur_contact_name (l_contact_id in number) is
2343 SELECT hzp.party_name
2344 from hz_parties hzp
2345 where hzp.party_id = l_contact_id;
2346
2347 cursor cur_prim_contact (l_contact_id in number, l_ret_str_type in varchar2) is
2348 select hzcp.phone_country_code||'-'||hzcp.phone_area_code||'-'||hzcp.phone_number||'-'||hzcp.phone_extension
2349 , hzcp.email_address
2350 from hz_contact_points hzcp
2351 where hzcp.owner_table_id = l_contact_id
2352 and hzcp.contact_point_type = l_ret_str_type
2353 and hzcp.primary_flag = 'Y'
2354 and hzcp.owner_table_name = 'HZ_PARTIES'
2355 and hzcp.status = 'A';
2356
2357 cursor cur_sec_contact (l_contact_id in number, l_ret_str_type in varchar2) is
2358 select hzcp.phone_country_code||'-'||hzcp.phone_area_code||'-'||hzcp.phone_number||'-'||hzcp.phone_extension
2359 , hzcp.email_address
2360 from hz_contact_points hzcp
2361 where hzcp.owner_table_id = l_contact_id
2362 and hzcp.contact_point_type = l_ret_str_type
2363 and hzcp.primary_flag = 'N'
2364 and hzcp.owner_table_name = 'HZ_PARTIES'
2365 and hzcp.status = 'A';
2366
2367 l_contact_person varchar2(100);
2368 l_phone_num varchar2(100);
2369 l_email_address varchar2(100);
2370
2371 begin
2372 if p_owner_type_id = 1 then
2373 if p_ret_str_type = 'NAME' then
2374 l_contact_person := fnd_profile.value('MST_COMPANY_CONTACT_NAME');
2375 return l_contact_person;
2376 elsif p_ret_str_type = 'PHONE' then
2377 l_phone_num := fnd_profile.value('MST_COMPANY_CONTACT_PHONE');
2378 return l_phone_num;
2379 elsif p_ret_str_type = 'EMAIL' then
2380 l_email_address := fnd_profile.value('MST_COMPANY_CONTACT_EMAIL');
2381 return l_email_address;
2382 else
2383 return null;
2384 end if;
2385 else
2386 if p_ret_str_type = 'NAME' then
2387 --get the contact person name
2388 open cur_contact_name (p_contact_id);
2389 fetch cur_contact_name into l_contact_person;
2390 close cur_contact_name;
2391
2392 return l_contact_person;
2393 elsif p_ret_str_type IN ('PHONE','EMAIL') then
2394 --get the primary phone number OR email
2395 open cur_prim_contact (p_contact_id, p_ret_str_type);
2396 fetch cur_prim_contact into l_phone_num, l_email_address;
2397 if cur_prim_contact%notfound then
2398 close cur_prim_contact;
2399 --else get one secondary phone number OR email
2400 open cur_sec_contact (p_contact_id, p_ret_str_type);
2401 fetch cur_sec_contact into l_phone_num, l_email_address;
2402 close cur_sec_contact;
2403 else
2404 close cur_prim_contact;
2405 end if;
2406 if p_ret_str_type = 'PHONE' then
2407 return l_phone_num;
2408 elsif p_ret_str_type = 'EMAIL' then
2409 return l_email_address;
2410 end if;
2411 else
2412 return null;
2413 end if;
2414 end if;
2415
2416 exception
2417 when others then
2418 return null;
2419 end get_contact_info;
2420
2421
2422 --***************USED IN REPORTS **************************
2423
2424 function r_get_canonical_number (p_number in number
2425 , p_format_mask_ident in number default 1)
2426 return varchar2 is
2427 l_format_mask varchar2(50);
2428 l_string varchar2(50);
2429 begin
2430 if p_format_mask_ident = 1 then
2431 l_format_mask := 'FM999G999G999G990';
2432 elsif p_format_mask_ident = 2 then
2433 l_format_mask := 'FM999G999G999G990D09999';
2434 elsif p_format_mask_ident = 3 then
2435 l_format_mask := 'FM999G999G999G990D00';
2436 else
2437 l_format_mask := null;
2438 end if;
2439
2440 if l_format_mask is not null then
2441 l_string := to_char(p_number, l_format_mask);
2442 else
2443 l_string := 'Format Mask Not Defined';
2444 end if;
2445 return l_string;
2446 exception
2447 when others then
2448 return 'Exception Raised';
2449 end r_get_canonical_number;
2450
2451 FUNCTION r_get_company_name(P_Location_id IN NUMBER, P_Owner_Type IN NUMBER)
2452 RETURN VARCHAR2 IS
2453
2454 CURSOR CUR_COMPANY_NAME( P_PARTY_ID IN NUMBER ) IS
2455 SELECT HZP.PARTY_NAME
2456 FROM HZ_PARTIES HZP
2457 WHERE HZP.PARTY_ID = P_party_id;
2458
2459 l_owner_type NUMBER;
2460 l_owner_party_id NUMBER;
2461
2462 l_Name VARCHAR2(360);
2463
2464 BEGIN
2465 SELECT wlo.OWNER_PARTY_ID
2466 INTO l_owner_party_id
2467 FROM wsh_location_owners wlo
2468 WHERE wlo.wsh_location_id = P_Location_id
2469 AND wlo.owner_type = P_Owner_Type;
2470 IF P_Owner_Type = 1 THEN
2471 l_Name := fnd_profile.value('MST_COMPANY_NAME');
2472 ELSE
2473 OPEN CUR_COMPANY_NAME(l_owner_party_id);
2474 FETCH CUR_COMPANY_NAME INTO l_Name;
2475 CLOSE CUR_COMPANY_NAME;
2476 END IF;
2477
2478 RETURN l_Name;
2479 EXCEPTION
2480 WHEN too_many_rows THEN
2481 l_name := Get_meaning('MST_STRING','33','MFG');
2482 RETURN L_NAME;
2483 WHEN OTHERS THEN
2484 -- Nothing to return
2485 RETURN NULL;
2486 END r_get_company_name;
2487
2488 function r_plan_value (p_plan_id in number)
2489 return number
2490 is l_plan_value number;
2491 begin
2492 /* select sum(nvl(mt.total_basic_transport_cost,0)
2493 + nvl(mt.total_accessorial_cost,0)
2494 + nvl(mt.total_handling_cost,0)
2495 + nvl(mt.total_layover_cost,0)
2496 + nvl(mt.total_load_unload_cost,0)
2497 + nvl(mt.total_stop_cost,0))
2498 into l_plan_value
2499 from mst_trips mt
2500 where mt.plan_id = p_plan_id;
2501 */
2502 -- Changed as per bug # 3509257
2503 -- The Plan value should be calculated from the selling price at the delivery lines.
2504 -- The data model supports the selling price in the mst_delivery_details table
2505 -- as unit price column, which is directly snapshot from the wsh_delivery_details.unit_price.
2506
2507 select sum(nvl(mdd.unit_price,0)* mdd.requested_quantity)
2508 into l_plan_value
2509 from mst_delivery_details mdd
2510 , mst_delivery_assignments mda
2511 where mdd.plan_id = p_plan_id
2512 and mda.plan_id = mdd.plan_id
2513 and mda.delivery_detail_id = mdd.delivery_detail_id
2514 and mda.parent_delivery_detail_id is null;
2515
2516 return l_plan_value;
2517 exception
2518 when others then
2519 return 0;
2520 end r_plan_value;
2521
2522
2523 function r_plan_alloc_cost (p_plan_id in number)
2524 return number
2525 is l_plan_alloc_cost number;
2526 begin
2527 select sum(nvl(mdl.allocated_transport_cost,0)
2528 + (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
2529 + nvl(mdl.allocated_fac_loading_cost,0)
2530 + nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
2531 into l_plan_alloc_cost
2532 from mst_delivery_legs mdl
2533 where mdl.plan_id = p_plan_id;
2534
2535 return l_plan_alloc_cost;
2536 exception
2537 when others then
2538 return 0;
2539 end r_plan_alloc_cost;
2540
2541 /*
2542 function r_total_orders_myfac(p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2, p_activity_type in varchar2)
2543 return number is
2544 l_total_orders number :=0;
2545 begin
2546 */
2547 /*
2548 if p_activity_type is null then
2549 select count(mdd.delivery_detail_id)
2550 into l_total_orders
2551 from mst_trips mt,
2552 mst_trip_stops mts,
2553 mst_delivery_legs mdl,
2554 mst_deliveries md,
2555 mst_delivery_details mdd,
2556 mst_delivery_assignments mda
2557 where mt.plan_id = p_plan_id
2558 and mt.mode_of_transport = p_mode
2559 and mt.trip_id = mts.trip_id
2560 and mt.trip_id = mdl.trip_id
2561 and (mdl.pick_up_stop_id = mts.stop_id or
2562 mdl.drop_off_stop_id = mts.stop_id)
2563 and mts.stop_location_id = p_my_fac_location_id
2564 and mdl.delivery_id = md.delivery_id
2565 and md.delivery_id = mda.delivery_id
2566 and mda.delivery_detail_id = mdd.delivery_detail_id
2567 and mda.parent_delivery_detail_id is null
2568 and mdd.split_from_delivery_detail_id is null;
2569 elsif p_activity_type = 'L' then
2570 select count(mdd.delivery_detail_id)
2571 into l_total_orders
2572 from mst_trips mt,
2573 mst_trip_stops mts,
2574 mst_delivery_legs mdl,
2575 mst_deliveries md,
2576 mst_delivery_details mdd,
2577 mst_delivery_assignments mda
2578 where mt.plan_id = p_plan_id
2579 and mt.mode_of_transport = p_mode
2580 and mt.trip_id = mts.trip_id
2581 and mt.trip_id = mdl.trip_id
2582 and mdl.pick_up_stop_id = mts.stop_id
2583 and mts.stop_location_id = p_my_fac_location_id
2584 and mdl.delivery_id = md.delivery_id
2585 and md.delivery_id = mda.delivery_id
2586 and mda.delivery_detail_id = mdd.delivery_detail_id
2587 and mda.parent_delivery_detail_id is null
2588 and mdd.split_from_delivery_detail_id is null;
2589 elsif p_activity_type = 'U' then
2590 select count(mdd.delivery_detail_id)
2591 into l_total_orders
2592 from mst_trips mt,
2593 mst_trip_stops mts,
2594 mst_delivery_legs mdl,
2595 mst_deliveries md,
2596 mst_delivery_details mdd,
2597 mst_delivery_assignments mda
2598 where mt.plan_id = p_plan_id
2599 and mt.mode_of_transport = p_mode
2600 and mt.trip_id = mts.trip_id
2601 and mt.trip_id = mdl.trip_id
2602 and mdl.drop_off_stop_id = mts.stop_id
2603 and mts.stop_location_id = p_my_fac_location_id
2604 and mdl.delivery_id = md.delivery_id
2605 and md.delivery_id = mda.delivery_id
2606 and mda.delivery_detail_id = mdd.delivery_detail_id
2607 and mda.parent_delivery_detail_id is null
2608 and mdd.split_from_delivery_detail_id is null;
2609 end if;
2610 */
2611 /*
2612 select count(distinct mdd.source_header_number)
2613 into l_total_orders
2614 from mst_delivery_details mdd,
2615 mst_deliveries md,
2616 mst_delivery_assignments mda
2617 where md.plan_id = mda.plan_id
2618 and md.delivery_id = mda.delivery_id
2619 and md.delivery_id in
2620 (select mdl.delivery_id
2621 from mst_trips t,
2622 mst_trip_stops ts,
2623 mst_delivery_legs mdl
2624 where mdl.plan_id = md.plan_id
2625 and ts.plan_id = mdl.plan_id
2626 and ts.stop_id = mdl.pick_up_stop_id
2627 and ts.stop_location_id = p_my_fac_location_id
2628 and ts.plan_id = t.plan_id
2629 and ts.trip_id = t.trip_id
2630 and t.mode_of_transport = p_mode)
2631 and mda.plan_id = mdd.plan_id
2632 and mda.delivery_detail_id = mdd.delivery_detail_id
2633 and md.plan_id = p_plan_id
2634 and mdd.container_flag = 2
2635 and mdd.split_from_delivery_detail_id is null;
2636
2637 return l_total_orders;
2638 exception
2639 when others then
2640 return 0;
2641 end r_total_orders_myfac;
2642 */
2643
2644
2645 function r_total_cost_myfac (p_plan_id in number,p_my_fac_location_id in number, p_mode in varchar2)
2646 return number is
2647
2648 l_total_cost number;
2649 l_total_departing_cost number;
2650 l_total_arriving_cost number;
2651
2652 cursor departing_delivery_leg(l_plan_id in number,l_my_fac_location_id in number, l_mode in varchar2) is
2653 select sum( nvl(mdl.allocated_fac_loading_cost,0)
2654 + nvl(mdl.allocated_fac_shp_hand_cost,0)
2655 + nvl(mdl.allocated_transport_cost,0)) total_departing_cost
2656 from mst_trips mt,
2657 mst_trip_stops mts,
2658 mst_delivery_legs mdl
2659 where mt.plan_id = l_plan_id
2660 and mt.mode_of_transport = l_mode
2661 and mt.plan_id = mts.plan_id
2662 and mt.trip_id = mts.trip_id
2663 and mts.plan_id = mdl.plan_id
2664 and mdl.pick_up_stop_id = mts.stop_id
2665 and mts.stop_location_id = l_my_fac_location_id;
2666
2667 cursor arriving_delivery_leg(l_plan_id in number,l_my_fac_location_id in number, l_mode in varchar2) is
2668 select sum( nvl(mdl.allocated_fac_unloading_cost,0)
2669 + nvl(mdl.allocated_fac_rec_hand_cost,0)
2670 + nvl(mdl.allocated_transport_cost,0)) total_arriving_cost
2671 from mst_trips mt,
2672 mst_trip_stops mts,
2673 mst_delivery_legs mdl
2674 where mt.plan_id = l_plan_id
2675 and mt.mode_of_transport = l_mode
2676 and mt.plan_id = mts.plan_id
2677 and mt.trip_id = mts.trip_id
2678 and mts.plan_id = mdl.plan_id
2679 and mdl.drop_off_stop_id = mts.stop_id
2680 and mts.stop_location_id = l_my_fac_location_id;
2681 begin
2682 open departing_delivery_leg(p_plan_id, p_my_fac_location_id, p_mode);
2683 fetch departing_delivery_leg into l_total_departing_cost;
2684 close departing_delivery_leg;
2685 open arriving_delivery_leg(p_plan_id, p_my_fac_location_id, p_mode);
2686 fetch arriving_delivery_leg into l_total_arriving_cost;
2687 close arriving_delivery_leg;
2688
2689 l_total_cost := nvl(l_total_departing_cost,0) + nvl(l_total_arriving_cost,0);
2690
2691 return l_total_cost;
2692 end r_total_cost_myfac;
2693
2694
2695
2696 --includes both loading and unloading weight. Name not changed to avoid regression
2697 function r_loading_weight_myfac (p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2)
2698 return number is
2699 l_loading_weight number;
2700 begin
2701 select sum(md.gross_weight)
2702 into l_loading_weight
2703 from mst_trips mt,
2704 mst_trip_stops mts,
2705 mst_delivery_legs mdl,
2706 mst_deliveries md
2707 where mt.plan_id = p_plan_id
2708 and mt.mode_of_transport = p_mode
2709 and mts.trip_id = mt.trip_id
2710 and mts.plan_id = mt.plan_id
2711 and mdl.plan_id = mt.plan_id
2712 and mdl.trip_id = mt.trip_id
2713 and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)
2714 and mts.stop_location_id = p_my_fac_location_id
2715 and md.delivery_id = mdl.delivery_id
2716 and md.plan_id = mt.plan_id;
2717 if l_loading_weight IS NULL then
2718 l_loading_weight := 0;
2719 end if;
2720 return l_loading_weight;
2721 exception
2722 when others then
2723 return 0;
2724 end r_loading_weight_myfac;
2725
2726
2727 --includes both loading and unloading cube. Name not changed to avoid regression
2728 function r_loading_cube_myfac (p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2)
2729 return number is
2730 l_loading_cube number;
2731 begin
2732 select sum(md.volume)
2733 into l_loading_cube
2734 from mst_trips mt,
2735 mst_trip_stops mts,
2736 mst_delivery_legs mdl,
2737 mst_deliveries md
2738 where mt.plan_id = p_plan_id
2739 and mt.mode_of_transport = p_mode
2740 and mts.trip_id = mt.trip_id
2741 and mts.plan_id = mt.plan_id
2742 and mdl.plan_id = mt.plan_id
2743 and mdl.trip_id = mt.trip_id
2744 and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)
2745 and mts.stop_location_id = p_my_fac_location_id
2746 and md.delivery_id = mdl.delivery_id
2747 and md.plan_id = mt.plan_id;
2748 if l_loading_cube IS NULL then
2749 l_loading_cube := 0;
2750 end if;
2751 return l_loading_cube;
2752 exception
2753 when others then
2754 return 0;
2755 end r_loading_cube_myfac;
2756
2757
2758
2759 --includes both loading and unloading pieces. Name not changed to avoid regression
2760 function r_loading_piece_myfac (p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2)
2761 return number is
2762 l_loading_pieces number;
2763 begin
2764 select sum(md.number_of_pieces)
2765 into l_loading_pieces
2766 from mst_trips mt,
2767 mst_trip_stops mts,
2768 mst_delivery_legs mdl,
2769 mst_deliveries md
2770 where mt.plan_id = p_plan_id
2771 and mt.mode_of_transport = p_mode
2772 and mts.trip_id = mt.trip_id
2773 and mts.plan_id = mt.plan_id
2774 and mdl.plan_id = mt.plan_id
2775 and mdl.trip_id = mt.trip_id
2776 and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)
2777 and mts.stop_location_id = p_my_fac_location_id
2778 and md.delivery_id = mdl.delivery_id
2779 and md.plan_id = mt.plan_id;
2780 if l_loading_pieces IS NULL then
2781 l_loading_pieces := 0;
2782 end if;
2783 return l_loading_pieces;
2784 exception
2785 when others then
2786 return 0;
2787 end r_loading_piece_myfac;
2788
2789
2790
2791
2792 function r_value_myfac(p_plan_id in number, p_facility_id in number)
2793 return number
2794 is
2795 l_total_value number;
2796 begin
2797 select sum(nvl(mt.total_basic_transport_cost,0)
2798 + nvl(mt.total_accessorial_cost,0)
2799 + nvl(mt.total_handling_cost,0)
2800 + nvl(mt.total_layover_cost,0)
2801 + nvl(mt.total_load_unload_cost,0)
2802 + nvl(mt.total_stop_cost,0))
2803 into l_total_value
2804 from mst_trips mt
2805 where mt.plan_id = p_plan_id
2806 and (mt.origin_location_id IN (select distinct fte.location_id
2807 from fte_location_parameters fte
2808 where fte.facility_id = p_facility_id)
2809 or mt.destination_location_id IN (select distinct fte.location_id
2810 from fte_location_parameters fte
2811 where fte.facility_id = p_facility_id)
2812 );
2813
2814 return l_total_value;
2815
2816 exception
2817 when others then
2818 return 0;
2819 end r_value_myfac;
2820
2821 function r_total_orders_myfac_general (p_plan_id in number, p_my_fac_location_id in number)
2822 return number is l_total_orders number;
2823 begin
2824 /* l_total_orders := nvl(r_total_orders_myfac(p_plan_id, p_facility_id, 'TRUCK', 'L'),0)
2825 + nvl(r_total_orders_myfac(p_plan_id, p_facility_id, 'LTL', 'L'),0)
2826 + nvl(r_total_orders_myfac(p_plan_id, p_facility_id, 'PARCEL', 'L'),0);
2827 */
2828
2829
2830 -- As per bug # 3364598, this change is done for calculating total orders for myfac.
2831
2832 select count(distinct mdd.source_header_number)
2833 into l_total_orders
2834 from mst_delivery_details mdd,
2835 mst_deliveries md,
2836 mst_delivery_assignments mda
2837 where md.plan_id = mda.plan_id
2838 and md.delivery_id = mda.delivery_id
2839 and md.delivery_id in
2840 (select mdl.delivery_id
2841 from mst_trips t,
2842 mst_trip_stops ts,
2843 mst_delivery_legs mdl
2844 where mdl.plan_id = md.plan_id
2845 and ts.plan_id = mdl.plan_id
2846 and (ts.stop_id = mdl.pick_up_stop_id
2847 or ts.stop_id = mdl.drop_off_stop_id)
2848 and ts.stop_location_id = p_my_fac_location_id
2849 and ts.plan_id = t.plan_id
2850 and ts.trip_id = t.trip_id)
2851 and mda.plan_id = mdd.plan_id
2852 and mda.delivery_detail_id = mdd.delivery_detail_id
2853 and md.plan_id = p_plan_id
2854 and mdd.container_flag = 2;
2855
2856 return l_total_orders;
2857
2858 exception
2859 when others then
2860 return 0;
2861 end r_total_orders_myfac_general;
2862
2863 function r_total_weight_myfac (p_plan_id in number, p_facility_id in number)
2864 return number is l_total_weight number;
2865 begin
2866 l_total_weight := nvl(r_loading_weight_myfac(p_plan_id, p_facility_id, 'TRUCK'),0)
2867 + nvl(r_loading_weight_myfac(p_plan_id, p_facility_id, 'LTL'),0)
2868 + nvl(r_loading_weight_myfac(p_plan_id, p_facility_id, 'PARCEL'),0);
2869
2870 return l_total_weight;
2871
2872 exception
2873 when others then
2874 return 0;
2875 end r_total_weight_myfac;
2876
2877
2878 function r_total_cube_myfac (p_plan_id in number, p_facility_id in number)
2879 return number is l_total_cube number;
2880 begin
2881 l_total_cube := nvl(r_loading_cube_myfac(p_plan_id, p_facility_id, 'TRUCK'),0)
2882 + nvl(r_loading_cube_myfac(p_plan_id, p_facility_id, 'LTL'),0)
2883 + nvl(r_loading_cube_myfac(p_plan_id, p_facility_id, 'PARCEL'),0);
2884
2885 return l_total_cube;
2886
2887 exception
2888 when others then
2889 return 0;
2890 end r_total_cube_myfac;
2891
2892
2893 function r_total_pieces_myfac (p_plan_id in number, p_facility_id in number)
2894 return number is l_total_pieces number;
2895 begin
2896 l_total_pieces := nvl(r_loading_piece_myfac(p_plan_id, p_facility_id, 'TRUCK'),0)
2897 + nvl(r_loading_piece_myfac(p_plan_id, p_facility_id, 'LTL'),0)
2898 + nvl(r_loading_piece_myfac(p_plan_id, p_facility_id, 'PARCEL'),0);
2899
2900 return l_total_pieces;
2901
2902 exception
2903 when others then
2904 return 0;
2905 end r_total_pieces_myfac;
2906
2907
2908 function r_total_trans_cost_myfac (p_plan_id in number, p_facility_id in number)
2909 return number is l_total_trans_cost number;
2910 begin
2911 l_total_trans_cost := nvl(r_total_cost_myfac(p_plan_id, p_facility_id, 'TRUCK'),0)
2912 + nvl(r_total_cost_myfac(p_plan_id, p_facility_id, 'LTL'),0)
2913 + nvl(r_total_cost_myfac(p_plan_id, p_facility_id, 'PARCEL'),0);
2914
2915 return l_total_trans_cost;
2916
2917 exception
2918 when others then
2919 return 0;
2920 end r_total_trans_cost_myfac;
2921
2922
2923 function r_value_origin(p_plan_id in number, p_origin_id in number)
2924 return number
2925 is
2926 l_total_value number;
2927 begin
2928 select sum(nvl(mt.total_basic_transport_cost,0)
2929 + nvl(mt.total_accessorial_cost,0)
2930 + nvl(mt.total_handling_cost,0)
2931 + nvl(mt.total_layover_cost,0)
2932 + nvl(mt.total_load_unload_cost,0)
2933 + nvl(mt.total_stop_cost,0))
2934 into l_total_value
2935 from mst_trips mt
2936 where mt.plan_id = p_plan_id
2937 and mt.origin_location_id = p_origin_id;
2938
2939 return l_total_value;
2940
2941 exception
2942 when others then
2943 return 0;
2944 end r_value_origin;
2945
2946
2947
2948
2949 function r_get_alloc_cost_origin (p_plan_id in number, p_origin_id in number)
2950 return number
2951 is l_alloc_cost number;
2952 begin
2953 select sum(nvl(mdl.allocated_transport_cost,0)
2954 + (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
2955 + nvl(mdl.allocated_fac_loading_cost,0)
2956 + nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
2957 into l_alloc_cost
2958 from mst_delivery_legs mdl
2959 , mst_trips mt
2960 where mdl.plan_id = mt.plan_id
2961 and mdl.trip_id = mt.trip_id
2962 and mt.plan_id = p_plan_id
2963 and mt.origin_location_id = p_origin_id;
2964
2965 return l_alloc_cost;
2966
2967 exception
2968 when others then
2969 return 0;
2970
2971 end r_get_alloc_cost_origin;
2972
2973
2974
2975
2976 function r_get_total_orders_origin (p_plan_id in number, p_origin_id in number)
2977 return number is
2978 l_count_origin_orders number;
2979 begin
2980 select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
2981 into l_count_origin_orders
2982 from mst_delivery_details mdd
2983 , mst_delivery_assignments mda
2984 , mst_deliveries md
2985 where md.plan_id = mda.plan_id
2986 and md.delivery_id = mda.delivery_id
2987 and md.pickup_location_id = p_origin_id
2988 and mdd.plan_id = mda.plan_id
2989 and mdd.delivery_detail_id = mda.delivery_detail_id
2990 and mda.parent_delivery_detail_id is null
2991 and md.plan_id = p_plan_id;
2992
2993 return l_count_origin_orders;
2994
2995 exception
2996 when others then
2997 return 0;
2998 end r_get_total_orders_origin;
2999
3000
3001 function r_get_count_stops_origin (p_plan_id in number, p_origin_id in number)
3002 return number is
3003 l_count_stops_origin number;
3004 begin
3005 select count(*)
3006 into l_count_stops_origin
3007 from (select distinct mt.trip_id, count(*) num_stops
3008 from mst_trips mt
3009 , mst_trip_stops mts
3010 where mt.plan_id = p_plan_id
3011 and mt.origin_location_id = p_origin_id
3012 and mts.plan_id = mt.plan_id
3013 and mts.trip_id = mt.trip_id
3014 group by mt.trip_id) temp
3015 where temp.num_stops > 2;
3016
3017 return l_count_stops_origin;
3018
3019 exception
3020 when others then
3021 return 0;
3022 end r_get_count_stops_origin;
3023
3024
3025 function r_get_total_weight_origin (p_plan_id in number, p_origin_id in number)
3026 return number is
3027 l_total_weight_origin number;
3028 begin
3029 select sum(md.gross_weight)
3030 into l_total_weight_origin
3031 from mst_deliveries md
3032 where md.plan_id = p_plan_id
3033 and md.pickup_location_id = p_origin_id;
3034
3035 return l_total_weight_origin;
3036
3037 exception
3038 when others then
3039 return 0;
3040 end r_get_total_weight_origin;
3041
3042
3043
3044 function r_get_total_volume_origin (p_plan_id in number, p_origin_id in number)
3045 return number is
3046 l_total_volume_origin number;
3047 begin
3048 select sum(md.volume)
3049 into l_total_volume_origin
3050 from mst_deliveries md
3051 where md.plan_id = p_plan_id
3052 and md.pickup_location_id = p_origin_id;
3053
3054 return l_total_volume_origin;
3055
3056 exception
3057 when others then
3058 return 0;
3059 end r_get_total_volume_origin;
3060
3061
3062
3063
3064 function r_get_total_pieces_origin (p_plan_id in number, p_origin_id in number)
3065 return number is
3066 l_total_pieces_origin number;
3067 begin
3068 select sum(md.number_of_pieces)
3069 into l_total_pieces_origin
3070 from mst_deliveries md
3071 where md.plan_id = p_plan_id
3072 and md.pickup_location_id = p_origin_id;
3073
3074 return l_total_pieces_origin;
3075
3076 exception
3077 when others then
3078 return 0;
3079 end r_get_total_pieces_origin;
3080
3081
3082
3083 function r_get_trip_count_origin (p_plan_id in number, p_origin_id in number, p_mode_of_transport in varchar2)
3084 return number is
3085 l_trip_count number;
3086 begin
3087 select count (1)
3088 into l_trip_count
3089 from mst_trips mt
3090 where mt.plan_id = p_plan_id
3091 and mt.mode_of_transport = p_mode_of_transport
3092 and mt.origin_location_id = p_origin_id;
3093
3094 return l_trip_count;
3095
3096 exception
3097 when others then
3098 return 0;
3099 end r_get_trip_count_origin;
3100
3101
3102
3103 function r_get_cost_origin (p_plan_id in number, p_origin_id in number, p_mode_of_transport in varchar2)
3104 return number is
3105 l_cost number;
3106 begin
3107 /*
3108 select nvl(sum(nvl(mt.total_accessorial_cost,0)
3109 + nvl(mt.total_basic_transport_cost,0)
3110 + nvl(mt.total_layover_cost,0)
3111 + nvl(mt.total_handling_cost,0)
3112 + nvl(mt.total_load_unload_cost,0)
3113 + nvl(mt.total_stop_cost,0)),0)
3114 into l_cost
3115 from mst_trips mt
3116 where mt.plan_id = p_plan_id
3117 and mt.mode_of_transport = p_mode_of_transport
3118 and mt.origin_location_id = p_origin_id;
3119 */
3120
3121 select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
3122 + nvl(mdl.allocated_fac_unloading_cost,0)
3123 + nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
3124 + nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
3125 + nvl(mdl.allocated_transport_cost,0)),0)
3126 into l_cost
3127 from mst_trips mt
3128 , mst_trip_stops mts
3129 , mst_delivery_legs mdl
3130 where mt.plan_id = p_plan_id
3131 and mt.mode_of_transport = p_mode_of_transport
3132 and mt.origin_location_id = p_origin_id
3133 and mts.plan_id = mt.plan_id
3134 and mts.trip_id = mt.trip_id
3135 and mts.stop_location_id = mt.origin_location_id
3136 and mdl.plan_id = mt.plan_id
3137 and mdl.trip_id = mt.trip_id;
3138
3139 return l_cost;
3140
3141 exception
3142 when others then
3143 return 0;
3144 end r_get_cost_origin;
3145
3146
3147 function r_get_count_dtl_origin (p_plan_id in number, p_origin_id in number)
3148 return number is
3149 total_dtl number := 0;
3150 begin
3151 select count(*)
3152 into total_dtl
3153 from (select distinct mt.trip_id, count(*) num_stops
3154 from mst_trips mt
3155 , mst_trip_stops mts
3156 where mt.plan_id = p_plan_id
3157 and mt.origin_location_id = p_origin_id
3158 and mt.mode_of_transport = 'TRUCK'
3159 and mts.plan_id = mt.plan_id
3160 and mts.trip_id = mt.trip_id
3161 group by mt.trip_id) temp
3162 where temp.num_stops = 2;
3163
3164 return total_dtl;
3165
3166 exception
3167 when others then
3168 return 0;
3169 end r_get_count_dtl_origin;
3170
3171
3172
3173 function r_value_dest(p_plan_id in number, p_dest_id in number)
3174 return number is
3175 l_total_value number;
3176 begin
3177 select sum(nvl(mt.total_basic_transport_cost,0)
3178 + nvl(mt.total_accessorial_cost,0)
3179 + nvl(mt.total_handling_cost,0)
3180 + nvl(mt.total_layover_cost,0)
3181 + nvl(mt.total_load_unload_cost,0)
3182 + nvl(mt.total_stop_cost,0))
3183 into l_total_value
3184 from mst_trips mt
3185 where mt.plan_id = p_plan_id
3186 and mt.destination_location_id = p_dest_id;
3187
3188 return l_total_value;
3189
3190 exception
3191 when others then
3192 return 0;
3193 end r_value_dest;
3194
3195
3196
3197 function r_get_alloc_cost_dest (p_plan_id in number, p_dest_id in number)
3198 return number is
3199 l_alloc_cost number;
3200 begin
3201 select sum(nvl(mdl.allocated_transport_cost,0)
3202 + (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
3203 + nvl(mdl.allocated_fac_loading_cost,0)
3204 + nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
3205 into l_alloc_cost
3206 from mst_delivery_legs mdl
3207 , mst_trips mt
3208 where mdl.plan_id = mt.plan_id
3209 and mdl.trip_id = mt.trip_id
3210 and mt.plan_id = p_plan_id
3211 and mt.destination_location_id = p_dest_id;
3212
3213 return l_alloc_cost;
3214
3215 exception
3216 when others then
3217 return 0;
3218 end r_get_alloc_cost_dest;
3219
3220
3221 function r_get_total_orders_dest (p_plan_id in number, p_dest_id in number)
3222 return number is
3223 l_count_dest_orders number;
3224 begin
3225 select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
3226 into l_count_dest_orders
3227 from mst_delivery_details mdd
3228 , mst_delivery_assignments mda
3229 , mst_deliveries md
3230 where md.plan_id = mda.plan_id
3231 and md.delivery_id = mda.delivery_id
3232 and md.dropoff_location_id = p_dest_id
3233 and mdd.plan_id = mda.plan_id
3234 and mdd.delivery_detail_id = mda.delivery_detail_id
3235 and mda.parent_delivery_detail_id is null
3236 and md.plan_id = p_plan_id;
3237
3238 return l_count_dest_orders;
3239
3240 exception
3241 when others then
3242 return 0;
3243 end r_get_total_orders_dest;
3244
3245
3246 function r_get_count_stops_dest (p_plan_id in number, p_dest_id in number)
3247 return number is
3248 l_count_stops_dest number;
3249 begin
3250 select count(*)
3251 into l_count_stops_dest
3252 from (select distinct mt.trip_id, count(*) num_stops
3253 from mst_trips mt
3254 , mst_trip_stops mts
3255 where mt.plan_id = p_plan_id
3256 and mt.destination_location_id = p_dest_id
3257 and mts.plan_id = mt.plan_id
3258 and mts.trip_id = mt.trip_id
3259 group by mt.trip_id) temp
3260 where temp.num_stops > 2;
3261
3262 return l_count_stops_dest;
3263
3264 exception
3265 when others then
3266 return 0;
3267 end r_get_count_stops_dest;
3268
3269
3270 function r_get_total_weight_dest (p_plan_id in number, p_dest_id in number)
3271 return number is
3272 l_total_weight_dest number;
3273 begin
3274 select sum(md.gross_weight)
3275 into l_total_weight_dest
3276 from mst_deliveries md
3277 where md.plan_id = p_plan_id
3278 and md.dropoff_location_id = p_dest_id;
3279
3280 return l_total_weight_dest;
3281
3282 exception
3283 when others then
3284 return 0;
3285 end r_get_total_weight_dest;
3286
3287
3288
3289 function r_get_total_volume_dest (p_plan_id in number, p_dest_id in number)
3290 return number is
3291 l_total_volume_dest number;
3292 begin
3293 select sum(md.volume)
3294 into l_total_volume_dest
3295 from mst_deliveries md
3296 where md.plan_id = p_plan_id
3297 and md.dropoff_location_id = p_dest_id;
3298
3299 return l_total_volume_dest;
3300
3301 exception
3302 when others then
3303 return 0;
3304 end r_get_total_volume_dest;
3305
3306
3307 function r_get_total_pieces_dest (p_plan_id in number, p_dest_id in number)
3308 return number is
3309 l_total_pieces_dest number;
3310 begin
3311 select sum(md.number_of_pieces)
3312 into l_total_pieces_dest
3313 from mst_deliveries md
3314 where md.plan_id = p_plan_id
3315 and md.dropoff_location_id = p_dest_id;
3316
3317 return l_total_pieces_dest;
3318
3319 exception
3320 when others then
3321 return 0;
3322 end r_get_total_pieces_dest;
3323
3324
3325
3326 function r_get_trip_count_dest (p_plan_id in number, p_dest_id in number, p_mode_of_transport in varchar2)
3327 return number is
3328 l_trip_count number;
3329 begin
3330 select count (1)
3331 into l_trip_count
3332 from mst_trips mt
3333 where mt.plan_id = p_plan_id
3334 and mt.mode_of_transport = p_mode_of_transport
3335 and mt.destination_location_id = p_dest_id;
3336
3337 return l_trip_count;
3338
3339 exception
3340 when others then
3341 return 0;
3342 end r_get_trip_count_dest;
3343
3344
3345
3346 function r_get_cost_dest (p_plan_id in number, p_dest_id in number, p_mode_of_transport in varchar2)
3347 return number is
3348 l_cost number;
3349 begin
3350 /*
3351 select nvl(sum(nvl(mt.total_accessorial_cost,0)
3352 + nvl(mt.total_basic_transport_cost,0)
3353 + nvl(mt.total_layover_cost,0)
3354 + nvl(mt.total_handling_cost,0)
3355 + nvl(mt.total_load_unload_cost,0)
3356 + nvl(mt.total_stop_cost,0)),0)
3357 into l_cost
3358 from mst_trips mt
3359 where mt.plan_id = p_plan_id
3360 and mt.mode_of_transport = p_mode_of_transport
3361 and mt.destination_location_id = p_dest_id;
3362 */
3363
3364 select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
3365 + nvl(mdl.allocated_fac_unloading_cost,0)
3366 + nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
3367 + nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
3368 + nvl(mdl.allocated_transport_cost,0)),0)
3369 into l_cost
3370 from mst_trips mt
3371 , mst_trip_stops mts
3372 , mst_delivery_legs mdl
3373 where mt.plan_id = p_plan_id
3374 and mt.mode_of_transport = p_mode_of_transport
3375 and mt.destination_location_id = p_dest_id
3376 and mts.plan_id = mt.plan_id
3377 and mts.trip_id = mt.trip_id
3378 and mts.stop_location_id = mt.destination_location_id
3379 and mdl.plan_id = mt.plan_id
3380 and mdl.trip_id = mt.trip_id;
3381
3382 return l_cost;
3383
3384 exception
3385 when others then
3386 return 0;
3387 end r_get_cost_dest;
3388
3389
3390 function r_get_count_dtl_dest (p_plan_id in number, p_dest_id in number)
3391 return number is
3392 total_dtl number := 0;
3393 begin
3394 select count(*)
3395 into total_dtl
3396 from (select distinct mt.trip_id, count(*) num_stops
3397 from mst_trips mt
3398 , mst_trip_stops mts
3399 where mt.plan_id = p_plan_id
3400 and mt.destination_location_id = p_dest_id
3401 and mt.mode_of_transport = 'TRUCK'
3402 and mts.plan_id = mt.plan_id
3403 and mts.trip_id = mt.trip_id
3404 group by mt.trip_id) temp
3405 where temp.num_stops = 2;
3406
3407 return total_dtl;
3408
3409 exception
3410 when others then
3411 return 0;
3412
3413 end r_get_count_dtl_dest;
3414
3415
3416
3417 function r_value_cust(p_plan_id in number, p_customer_id in number)
3418 return number is
3419 l_total_value number;
3420 begin
3421 select sum(nvl(mt.total_basic_transport_cost,0)
3422 + nvl(mt.total_accessorial_cost,0)
3423 + nvl(mt.total_handling_cost,0)
3424 + nvl(mt.total_layover_cost,0)
3425 + nvl(mt.total_load_unload_cost,0)
3426 + nvl(mt.total_stop_cost,0))
3427 into l_total_value
3428 from mst_trips mt
3429 where mt.plan_id = p_plan_id
3430 and mt.trip_id IN
3431 (select distinct mts.trip_id
3432 from mst_trip_stops mts
3433 , mst_delivery_legs mdl
3434 , mst_deliveries md
3435 where md.plan_id = mdl.plan_id
3436 and md.customer_id = p_customer_id
3437 and mts.plan_id = md.plan_id
3438 and mts.stop_location_id = md.dropoff_location_id
3439 and mdl.plan_id = md.plan_id
3440 and mdl.delivery_id = md.delivery_id
3441 and mdl.trip_id = mts.trip_id
3442 and mdl.drop_off_stop_id = mts.stop_id);
3443
3444 return l_total_value;
3445
3446 exception
3447 when others then
3448 return 0;
3449 end r_value_cust;
3450
3451
3452
3453
3454
3455 function r_get_alloc_cost_cust (p_plan_id in number, p_customer_id in number)
3456 return number
3457 is l_alloc_cost number;
3458 begin
3459 select sum(nvl(mdl.allocated_transport_cost,0)
3460 + (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
3461 + nvl(mdl.allocated_fac_loading_cost,0)
3462 + nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
3463 into l_alloc_cost
3464 from mst_delivery_legs mdl
3465 where mdl.plan_id = p_plan_id
3466 and mdl.trip_id IN
3467 (select distinct mts.trip_id
3468 from mst_trip_stops mts
3469 , mst_delivery_legs mdl
3470 , mst_deliveries md
3471 where md.plan_id = mdl.plan_id
3472 and md.customer_id = p_customer_id
3473 and mts.plan_id = md.plan_id
3474 and mts.stop_location_id = md.dropoff_location_id
3475 and mdl.plan_id = md.plan_id
3476 and mdl.delivery_id = md.delivery_id
3477 and mdl.trip_id = mts.trip_id
3478 and mdl.drop_off_stop_id = mts.stop_id);
3479
3480 return l_alloc_cost;
3481
3482 exception
3483 when others then
3484 return 0;
3485 end r_get_alloc_cost_cust;
3486
3487
3488
3489 FUNCTION r_get_count_stops_cust (p_plan_id in number, p_customer_id in number)
3490 RETURN number
3491 IS
3492 l_count_stops number;
3493 BEGIN
3494 select count(mt.trip_id)
3495 into l_count_stops
3496 from mst_trips mt
3497 where mt.plan_id = p_plan_id
3498 and mt.trip_id in
3499 (select distinct mts.trip_id
3500 from mst_trip_stops mts
3501 , mst_delivery_legs mdl
3502 , mst_deliveries md
3503 where md.plan_id = mt.plan_id
3504 and md.customer_id = p_customer_id
3505 and mts.plan_id = md.plan_id
3506 and mts.stop_location_id = md.dropoff_location_id
3507 and mdl.plan_id = md.plan_id
3508 and mdl.delivery_id = md.delivery_id
3509 and mdl.trip_id = mts.trip_id
3510 and mdl.drop_off_stop_id = mts.stop_id)
3511 and mt.mode_of_transport = 'TRUCK'
3512 and EXISTS
3513 (select ts.trip_id
3514 from mst_trip_stops ts
3515 where ts.plan_id = mt.plan_id
3516 and ts.trip_id = mt.trip_id
3517 having count(ts.stop_id) >2
3518 group by ts.trip_id);
3519
3520 RETURN l_count_stops;
3521
3522 EXCEPTION
3523 WHEN OTHERS THEN
3524 RETURN 0;
3525 END r_get_count_stops_cust;
3526
3527
3528 FUNCTION r_get_trip_count_cust (p_plan_id in number, p_customer_id in number, p_mode_of_transport in varchar2)
3529 RETURN number IS
3530 l_trip_count number;
3531 BEGIN
3532 select count(mt.trip_id)
3533 into l_trip_count
3534 from mst_trips mt
3535 where mt.plan_id = p_plan_id
3536 and mt.mode_of_transport = p_mode_of_transport
3537 and mt.trip_id in
3538 (select distinct mts.trip_id
3539 from mst_trip_stops mts
3540 , mst_delivery_legs mdl
3541 , mst_deliveries md
3542 where md.plan_id = mt.plan_id
3543 and md.customer_id = p_customer_id
3544 and mts.plan_id = md.plan_id
3545 and mts.stop_location_id = md.dropoff_location_id
3546 and mdl.plan_id = md.plan_id
3547 and mdl.delivery_id = md.delivery_id
3548 and mdl.trip_id = mts.trip_id
3549 and mdl.drop_off_stop_id = mts.stop_id);
3550
3551 RETURN l_trip_count;
3552
3553 EXCEPTION
3554 WHEN OTHERS THEN
3555 RETURN 0;
3556 END r_get_trip_count_cust;
3557
3558
3559 FUNCTION r_get_cost_cust (p_plan_id in number, p_customer_id in number, p_mode_of_transport in varchar2)
3560 RETURN number IS
3561 l_cost number;
3562 BEGIN
3563 /*
3564 SELECT SUM (nvl(MT.TOTAL_ACCESSORIAL_COST,0)
3565 + nvl(MT.TOTAL_BASIC_TRANSPORT_COST,0)
3566 + nvl(MT.TOTAL_LAYOVER_COST,0)
3567 + nvl(MT.TOTAL_HANDLING_COST,0)
3568 + nvl(MT.TOTAL_LOAD_UNLOAD_COST,0)
3569 + nvl(MT.TOTAL_STOP_COST,0))
3570 INTO l_cost
3571 FROM MST_TRIPS MT
3572 WHERE MT.PLAN_ID = p_plan_id
3573 AND MT.MODE_OF_TRANSPORT = p_mode_of_transport
3574 AND MT.TRIP_ID IN
3575 (SELECT DISTINCT MTS.TRIP_ID
3576 FROM MST_TRIP_STOPS MTS
3577 , MST_DELIVERY_LEGS MDL
3578 , MST_DELIVERIES MD
3579 WHERE MD.PLAN_ID = MT.PLAN_ID
3580 AND MD.CUSTOMER_ID = p_customer_id
3581 AND MTS.PLAN_ID = MD.PLAN_ID
3582 AND MTS.STOP_LOCATION_ID = MD.DROPOFF_LOCATION_ID
3583 AND MDL.PLAN_ID = MD.PLAN_ID
3584 AND MDL.DELIVERY_ID = MD.DELIVERY_ID
3585 AND MDL.TRIP_ID = MTS.TRIP_ID
3586 AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID);
3587 */
3588
3589 select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
3590 + nvl(mdl.allocated_fac_unloading_cost,0)
3591 + nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
3592 + nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
3593 + nvl(mdl.allocated_transport_cost,0)),0)
3594 into l_cost
3595 from mst_deliveries md
3596 , mst_delivery_legs mdl
3597 , mst_trips mt
3598 where md.plan_id = p_plan_id
3599 and md.customer_id = p_customer_id
3600 and md.plan_id = mdl.plan_id
3601 and md.delivery_id = mdl.delivery_id
3602 and mt.plan_id = mdl.plan_id
3603 and mt.trip_id = mdl.trip_id
3604 and mt.mode_of_transport = p_mode_of_transport;
3605
3606 RETURN l_cost;
3607
3608 EXCEPTION
3609 WHEN OTHERS THEN
3610 RETURN 0;
3611 END r_get_cost_cust;
3612
3613
3614 FUNCTION r_get_count_dtl_cust (p_plan_id in number, p_customer_id in number)
3615 RETURN number IS
3616 total_dtl number := 0;
3617 BEGIN
3618 select count(mt.trip_id)
3619 into total_dtl
3620 from mst_trips mt
3621 where mt.plan_id = p_plan_id
3622 and mt.trip_id in
3623 (select distinct mts.trip_id
3624 from mst_trip_stops mts
3625 , mst_delivery_legs mdl
3626 , mst_deliveries md
3627 where md.plan_id = mt.plan_id
3628 and md.customer_id = p_customer_id
3629 and mts.plan_id = md.plan_id
3630 and mts.stop_location_id = md.dropoff_location_id
3631 and mdl.plan_id = md.plan_id
3632 and mdl.delivery_id = md.delivery_id
3633 and mdl.trip_id = mts.trip_id
3634 and mdl.drop_off_stop_id = mts.stop_id)
3635 and mt.mode_of_transport = 'TRUCK'
3636 and EXISTS
3637 (select ts.trip_id
3638 from mst_trip_stops ts
3639 where ts.plan_id = mt.plan_id
3640 and ts.trip_id = mt.trip_id
3641 having count(ts.stop_id) =2
3642 group by ts.trip_id);
3643
3644 RETURN total_dtl;
3645
3646 EXCEPTION
3647 WHEN OTHERS THEN
3648 RETURN 0;
3649 END r_get_count_dtl_cust;
3650
3651
3652
3653 function r_value_supp(p_plan_id in number, p_supplier_id in number)
3654 return number is
3655 l_total_value number;
3656 begin
3657 select sum(nvl(mt.total_basic_transport_cost,0)
3658 + nvl(mt.total_accessorial_cost,0)
3659 + nvl(mt.total_handling_cost,0)
3660 + nvl(mt.total_layover_cost,0)
3661 + nvl(mt.total_load_unload_cost,0)
3662 + nvl(mt.total_stop_cost,0))
3663 into l_total_value
3664 from mst_trips mt
3665 where mt.plan_id = p_plan_id
3666 and mt.trip_id IN
3667 (select distinct mts.trip_id
3668 from mst_trip_stops mts
3669 , mst_delivery_legs mdl
3670 , mst_deliveries md
3671 where md.plan_id = mdl.plan_id
3672 and md.supplier_id = p_supplier_id
3673 and mts.plan_id = md.plan_id
3674 and mts.stop_location_id = md.pickup_location_id
3675 and mdl.plan_id = md.plan_id
3676 and mdl.delivery_id = md.delivery_id
3677 and mdl.trip_id = mts.trip_id
3678 and mdl.pick_up_stop_id = mts.stop_id);
3679
3680 return l_total_value;
3681
3682 exception
3683 when others then
3684 return 0;
3685 end r_value_supp;
3686
3687
3688
3689
3690 function r_get_alloc_cost_supp (p_plan_id in number, p_supplier_id in number)
3691 return number
3692 is l_alloc_cost number;
3693 begin
3694 select sum(nvl(mdl.allocated_transport_cost,0)
3695 + (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
3696 + nvl(mdl.allocated_fac_loading_cost,0)
3697 + nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
3698 into l_alloc_cost
3699 from mst_delivery_legs mdl
3700 where mdl.plan_id = p_plan_id
3701 and mdl.trip_id IN
3702 (select distinct mts.trip_id
3703 from mst_trip_stops mts
3704 , mst_delivery_legs mdl
3705 , mst_deliveries md
3706 where md.plan_id = mdl.plan_id
3707 and md.supplier_id = p_supplier_id
3708 and mts.plan_id = md.plan_id
3709 and mts.stop_location_id = md.pickup_location_id
3710 and mdl.plan_id = md.plan_id
3711 and mdl.delivery_id = md.delivery_id
3712 and mdl.trip_id = mts.trip_id
3713 and mdl.pick_up_stop_id = mts.stop_id);
3714
3715 return l_alloc_cost;
3716
3717 exception
3718 when others then
3719 return 0;
3720 end r_get_alloc_cost_supp;
3721
3722
3723
3724
3725 FUNCTION r_get_count_stops_supp (p_plan_id in number, p_supplier_id in number)
3726 RETURN number IS
3727 l_count_stops number;
3728 BEGIN
3729 select count(mt.trip_id)
3730 into l_count_stops
3731 from mst_trips mt
3732 where mt.plan_id = p_plan_id
3733 and mt.trip_id in
3734 (select distinct mts.trip_id
3735 from mst_trip_stops mts
3736 , mst_delivery_legs mdl
3737 , mst_deliveries md
3738 where md.plan_id = mt.plan_id
3739 and md.supplier_id = p_supplier_id
3740 and mts.plan_id = md.plan_id
3741 and mts.stop_location_id = md.pickup_location_id
3742 and mdl.plan_id = md.plan_id
3743 and mdl.delivery_id = md.delivery_id
3744 and mdl.trip_id = mts.trip_id
3745 and mdl.pick_up_stop_id = mts.stop_id)
3746 and mt.mode_of_transport = 'TRUCK'
3747 and EXISTS
3748 (select ts.trip_id
3749 from mst_trip_stops ts
3750 where ts.plan_id = mt.plan_id
3751 and ts.trip_id = mt.trip_id
3752 having count(ts.stop_id) >2
3753 group by ts.trip_id);
3754
3755 RETURN l_count_stops;
3756
3757 EXCEPTION
3758 WHEN OTHERS THEN
3759 RETURN 0;
3760 END r_get_count_stops_supp;
3761
3762
3763
3764 FUNCTION r_get_trip_count_supp (p_plan_id in number, p_supplier_id in number, p_mode_of_transport in varchar2)
3765 RETURN number IS
3766 l_trip_count number;
3767 BEGIN
3768 select count(mt.trip_id)
3769 into l_trip_count
3770 from mst_trips mt
3771 where mt.plan_id = p_plan_id
3772 and mt.mode_of_transport = p_mode_of_transport
3773 and mt.trip_id in
3774 (select distinct mts.trip_id
3775 from mst_trip_stops mts
3776 , mst_delivery_legs mdl
3777 , mst_deliveries md
3778 where md.plan_id = mt.plan_id
3779 and md.supplier_id = p_supplier_id
3780 and mts.plan_id = md.plan_id
3781 and mts.stop_location_id = md.pickup_location_id
3782 and mdl.plan_id = md.plan_id
3783 and mdl.delivery_id = md.delivery_id
3784 and mdl.trip_id = mts.trip_id
3785 and mdl.pick_up_stop_id = mts.stop_id);
3786
3787 RETURN l_trip_count;
3788
3789 EXCEPTION
3790 WHEN OTHERS THEN
3791 RETURN 0;
3792 END r_get_trip_count_supp;
3793
3794
3795
3796 FUNCTION r_get_cost_supp (p_plan_id in number, p_supplier_id in number, p_mode_of_transport in varchar2)
3797 RETURN number IS
3798 l_cost number;
3799 BEGIN
3800 /*
3801 SELECT SUM (nvl(MT.TOTAL_ACCESSORIAL_COST,0)
3802 + nvl(MT.TOTAL_BASIC_TRANSPORT_COST,0)
3803 + nvl(MT.TOTAL_LAYOVER_COST,0)
3804 + nvl(MT.TOTAL_HANDLING_COST,0)
3805 + nvl(MT.TOTAL_LOAD_UNLOAD_COST,0)
3806 + nvl(MT.TOTAL_STOP_COST,0))
3807 INTO l_cost
3808 FROM MST_TRIPS MT
3809 WHERE MT.PLAN_ID = p_plan_id
3810 AND MT.MODE_OF_TRANSPORT = p_mode_of_transport
3811 AND MT.TRIP_ID IN
3812 (SELECT DISTINCT MTS.TRIP_ID
3813 FROM MST_TRIP_STOPS MTS
3814 , MST_DELIVERY_LEGS MDL
3815 , MST_DELIVERIES MD
3816 WHERE MD.PLAN_ID = MT.PLAN_ID
3817 AND MD.SUPPLIER_ID = p_supplier_id
3818 AND MTS.PLAN_ID = MD.PLAN_ID
3819 AND MTS.STOP_LOCATION_ID = MD.PICKUP_LOCATION_ID
3820 AND MDL.PLAN_ID = MD.PLAN_ID
3821 AND MDL.DELIVERY_ID = MD.DELIVERY_ID
3822 AND MDL.TRIP_ID = MTS.TRIP_ID
3823 AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID);
3824 */
3825
3826 select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
3827 + nvl(mdl.allocated_fac_unloading_cost,0)
3828 + nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
3829 + nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
3830 + nvl(mdl.allocated_transport_cost,0)),0)
3831 into l_cost
3832 from mst_deliveries md
3833 , mst_delivery_legs mdl
3834 , mst_trips mt
3835 where md.plan_id = p_plan_id
3836 and md.supplier_id = p_supplier_id
3837 and md.plan_id = mdl.plan_id
3838 and md.delivery_id = mdl.delivery_id
3839 and mt.plan_id = mdl.plan_id
3840 and mt.trip_id = mdl.trip_id
3841 and mt.mode_of_transport = p_mode_of_transport;
3842
3843 RETURN l_cost;
3844
3845 EXCEPTION
3846 WHEN OTHERS THEN
3847 RETURN 0;
3848 END r_get_cost_supp;
3849
3850
3851
3852 FUNCTION r_get_count_dtl_supp (p_plan_id in number, p_supplier_id in number)
3853 RETURN number IS
3854 total_dtl number := 0;
3855 BEGIN
3856 select count(mt.trip_id)
3857 into total_dtl
3858 from mst_trips mt
3859 where mt.plan_id = p_plan_id
3860 and mt.trip_id in
3861 (select distinct mts.trip_id
3862 from mst_trip_stops mts
3863 , mst_delivery_legs mdl
3864 , mst_deliveries md
3865 where md.plan_id = mt.plan_id
3866 and md.supplier_id = p_supplier_id
3867 and mts.plan_id = md.plan_id
3868 and mts.stop_location_id = md.pickup_location_id
3869 and mdl.plan_id = md.plan_id
3870 and mdl.delivery_id = md.delivery_id
3871 and mdl.trip_id = mts.trip_id
3872 and mdl.pick_up_stop_id = mts.stop_id)
3873 and mt.mode_of_transport = 'TRUCK'
3874 and EXISTS
3875 (select ts.trip_id
3876 from mst_trip_stops ts
3877 where ts.plan_id = mt.plan_id
3878 and ts.trip_id = mt.trip_id
3879 having count(ts.stop_id) =2
3880 group by ts.trip_id);
3881
3882 RETURN total_dtl;
3883
3884 EXCEPTION
3885 WHEN OTHERS THEN
3886 RETURN 0;
3887 END r_get_count_dtl_supp;
3888
3889
3890 function r_get_wait_time_at_stop (p_plan_id in number, p_stop_id in number, p_trip_id in number)
3891 return varchar2 is
3892 l_wait_time varchar2(20) := null;
3893 l_arr_time date;
3894 l_dep_time date;
3895 l_wait_hrs_min number;
3896 l_wait_hrs number;
3897 l_wait_min number;
3898
3899 begin
3900
3901 select mts.planned_arrival_date, mts.planned_departure_date
3902 into l_arr_time, l_dep_time
3903 from mst_trip_stops mts
3904 where mts.plan_id = p_plan_id
3905 and mts.trip_id = p_trip_id
3906 and mts.stop_id = p_stop_id;
3907
3908 if l_dep_time > l_arr_time then
3909 select (mts.planned_departure_date - mts.planned_arrival_date) * 24
3910 , TRUNC((mts.planned_departure_date - mts.planned_arrival_date) * 24)
3911 into l_wait_hrs_min, l_wait_hrs
3912 from mst_trip_stops mts
3913 where mts.plan_id = p_plan_id
3914 and mts.trip_id = p_trip_id
3915 and mts.stop_id = p_stop_id;
3916
3917 l_wait_min := (l_wait_hrs_min - l_wait_hrs)*60;
3918
3919 l_wait_time := CONCAT(CONCAT(TO_CHAR(l_wait_hrs, '99'), ':'), TO_CHAR(l_wait_min, '99'));
3920
3921 end if;
3922
3923 return l_wait_time;
3924
3925 exception
3926 when others then
3927 return null;
3928 end r_get_wait_time_at_stop;
3929
3930
3931
3932 function r_get_prev_carr_detail (p_plan_id in number,p_delivery_id in number,p_trip_id in number, p_stop_location_id in number, p_identifier in varchar2)
3933 return number
3934 is
3935
3936 l_trip_id number := 0;
3937 l_prev_carr number := 0;
3938 l_carr_cost number := 0;
3939
3940 begin
3941
3942 select mt.carrier_id, mt.trip_id
3943 into l_prev_carr, l_trip_id
3944 from mst_delivery_legs mdl
3945 , mst_trips mt
3946 where mdl.plan_id = p_plan_id
3947 and mdl.delivery_id = p_delivery_id
3948 and mdl.trip_id <> p_trip_id
3949 and mt.plan_id = mdl.plan_id
3950 and mt.trip_id = mdl.trip_id
3951 and (mdl.pick_up_stop_id IN (select mts.stop_id from mst_trip_stops mts
3952 where mts.plan_id = p_plan_id
3953 and mts.stop_location_id = p_stop_location_id)
3954 or mdl.drop_off_stop_id IN (select mts.stop_id from mst_trip_stops mts
3955 where mts.plan_id = p_plan_id
3956 and mts.stop_location_id = p_stop_location_id)
3957 );
3958
3959 if p_identifier = 'F' then
3960 return nvl(l_prev_carr,0);
3961 elsif p_identifier = 'C' then
3962 select nvl(mt.total_handling_cost,0) -- + mt.total_load_unload_cost)
3963 into l_carr_cost
3964 from mst_trips mt
3965 where mt.plan_id = p_plan_id
3966 and mt.trip_id = l_trip_id;
3967 end if;
3968
3969 return nvl(l_carr_cost,0);
3970
3971 exception
3972 when others then
3973 return 0;
3974
3975 end r_get_prev_carr_detail;
3976
3977
3978
3979 function r_get_prev_stop_seqnum (p_plan_id in number, p_trip_id in number, p_curr_seq_num in number)
3980 return number is
3981 l_seq_num number := 0;
3982 exit_loop_flag number := 0;
3983
3984 cursor seq_num_cur is
3985 select mts.stop_sequence_number
3986 from mst_trip_stops mts
3987 where mts.plan_id = p_plan_id
3988 and mts.trip_id = p_trip_id
3989 order by mts.stop_sequence_number desc;
3990
3991 begin
3992
3993 open seq_num_cur;
3994 loop
3995 fetch seq_num_cur into l_seq_num;
3996 exit when seq_num_cur%NOTFOUND;
3997
3998 if exit_loop_flag = 1 then
3999 return l_seq_num;
4000 end if;
4001
4002 if p_curr_seq_num = l_seq_num then
4003 exit_loop_flag := 1;
4004 end if;
4005
4006 end loop;
4007 close seq_num_cur;
4008
4009 return l_seq_num;
4010
4011 exception
4012 when others then
4013 return 0;
4014
4015 end r_get_prev_stop_seqnum;
4016
4017
4018
4019
4020
4021 function r_get_prev_trip_detail (p_plan_id in number, p_trip_id in number, p_curr_seq_num in number, p_identifier in varchar2)
4022 return number
4023 is
4024 p_min_seq_num number := 0;
4025 p_ret_val number := 0;
4026
4027 begin
4028
4029 select min(mts.stop_sequence_number)
4030 into p_min_seq_num
4031 from mst_trip_stops mts
4032 where mts.plan_id = p_plan_id
4033 and mts.trip_id = p_trip_id;
4034
4035 if p_curr_seq_num = p_min_seq_num then
4036 return p_ret_val;
4037 else
4038 if p_identifier = 'D' then
4039 select mts.distance_to_next_stop
4040 into p_ret_val
4041 from mst_trip_stops mts
4042 where mts.plan_id = p_plan_id
4043 and mts.trip_id = p_trip_id
4044 and mts.stop_sequence_number = r_get_prev_stop_seqnum (p_plan_id, p_trip_id, p_curr_seq_num);
4045 elsif p_identifier = 'T' then
4046 select mts.drv_time_to_next_stop
4047 into p_ret_val
4048 from mst_trip_stops mts
4049 where mts.plan_id = p_plan_id
4050 and mts.trip_id = p_trip_id
4051 and mts.stop_sequence_number = r_get_prev_stop_seqnum (p_plan_id, p_trip_id, p_curr_seq_num);
4052 elsif p_identifier = 'C' then
4053 select mts.distance_cost
4054 into p_ret_val
4055 from mst_trip_stops mts
4056 where mts.plan_id = p_plan_id
4057 and mts.trip_id = p_trip_id
4058 and mts.stop_sequence_number = r_get_prev_stop_seqnum (p_plan_id, p_trip_id, p_curr_seq_num);
4059 else
4060 return 0;
4061 end if;
4062 end if;
4063
4064 return p_ret_val;
4065
4066 Exception
4067 when others then
4068 return 0;
4069 end r_get_prev_trip_detail;
4070
4071 FUNCTION r_get_pool_loc_detail (ret_type IN VARCHAR2
4072 , loc_id IN NUMBER)
4073 RETURN VARCHAR2
4074 IS
4075 l_code VARCHAR2(5) := NULL;
4076 l_name1 VARCHAR2(10) := NULL;
4077 l_address VARCHAR2(20) := NULL;
4078 l_city VARCHAR2(10) := NULL;
4079 l_state VARCHAR2(10) := NULL;
4080 l_zip VARCHAR2(5) := NULL;
4081 l_ret_val VARCHAR2(20) := NULL;
4082
4083 BEGIN
4084 SELECT substr(wl.location_code,1,5)
4085 , substr(mst_wb_util.get_name(loc_id),1,10)
4086 , substr(wl.address1,1,20)
4087 , substr(wl.city,1,10)
4088 , substr(wl.state,1,10)
4089 , substr(wl.postal_code,1,5)
4090 INTO l_code
4091 , l_name1
4092 , l_address
4093 , l_city
4094 , l_state
4095 , l_zip
4096 FROM wsh_locations wl
4097 WHERE wl.wsh_location_id = loc_id;
4098
4099 IF ret_type = 'O' THEN
4100 l_ret_val := l_code;
4101 ELSIF ret_type = 'N' THEN
4102 l_ret_val := l_name1;
4103 ELSIF ret_type = 'A' THEN
4104 l_ret_val := l_address;
4105 ELSIF ret_type = 'C' THEN
4106 l_ret_val := l_city;
4107 ELSIF ret_type = 'S' THEN
4108 l_ret_val := l_state;
4109 ELSIF ret_type = 'Z' THEN
4110 l_ret_val := l_zip;
4111 END IF;
4112
4113 RETURN l_ret_val;
4114 EXCEPTION
4115 WHEN OTHERS THEN
4116 RETURN NULL;
4117 END r_get_pool_loc_detail;
4118
4119
4120 function r_dep_frm_dest(p_plan_id in number, p_trip_id in number, p_stop_location_id in number)
4121 return varchar2 is latest_depart_date_frm_dest varchar2(20) := null;
4122 begin
4123 select substr(mst_wb_util.get_local_chardt(p_stop_location_id, min(mts.planned_departure_date)),1,20)
4124 into latest_depart_date_frm_dest
4125 from mst_delivery_legs mdl
4126 , mst_trip_stops mts
4127 where mdl.plan_id = p_plan_id
4128 and mdl.delivery_id in (select distinct mdl1.delivery_id from mst_delivery_legs mdl1
4129 where mdl1.plan_id = mdl.plan_id
4130 and mdl1.trip_id = p_trip_id)
4131 and mdl.trip_id <> p_trip_id
4132 and mts.plan_id = mdl.plan_id
4133 and mts.trip_id = mdl.trip_id
4134 and mts.stop_location_id = p_stop_location_id -- will be destination_location_id of the previous trip
4135 and mdl.pick_up_stop_id in (select distinct mts1.stop_id from mst_trip_stops mts1
4136 where mts1.plan_id = mts.plan_id
4137 and mts1.trip_id = mts.trip_id);
4138
4139 return latest_depart_date_frm_dest;
4140
4141 exception
4142 when others then
4143 return null;
4144
4145 end r_dep_frm_dest;
4146
4147
4148
4149 function r_get_order_cost(p_source_code in varchar2,p_source_header_number in varchar2)
4150 return number is
4151
4152 l_order_cost number;
4153
4154 begin
4155 select sum(mdd.allocated_cost)
4156 into l_order_cost
4157 from mst_delivery_details mdd
4158 where mdd.source_code = p_source_code
4159 and mdd.source_header_number = p_source_header_number;
4160
4161 return l_order_cost;
4162 EXCEPTION
4163 WHEN OTHERS THEN
4164 RETURN 0;
4165 end r_get_order_cost;
4166
4167
4168
4169
4170 function r_checkif_orig_ispool (p_plan_id in number,p_delivery_id in number,p_delivery_leg_id in number,p_pick_up_stop_id in number)
4171 return varchar2 is
4172 l_pool_indicator varchar2(1) := null;
4173 l_loc_id number;
4174 leg_pickup_loc_id number;
4175
4176 cursor pickup_loc_cur is
4177 select mt.destination_location_id
4178 from mst_deliveries md
4179 , mst_delivery_legs mdl
4180 , mst_trips mt
4181 where md.plan_id = p_plan_id
4182 and md.delivery_id = p_delivery_id
4183 and mdl.plan_id = md.plan_id
4184 and mdl.delivery_id = md.delivery_id
4185 and mdl.delivery_leg_id <> p_delivery_leg_id
4186 and mt.plan_id = mdl.plan_id
4187 and mt.trip_id = mdl.trip_id;
4188
4189 begin
4190
4191 select mts.stop_location_id
4192 into leg_pickup_loc_id
4193 from mst_trip_stops mts
4194 where mts.plan_id = p_plan_id
4195 and mts.stop_id = p_pick_up_stop_id;
4196
4197 open pickup_loc_cur;
4198 loop
4199 fetch pickup_loc_cur into l_loc_id;
4200 exit when pickup_loc_cur%NOTFOUND;
4201
4202 if l_loc_id = leg_pickup_loc_id then
4203 l_pool_indicator := 'P';
4204 return l_pool_indicator;
4205 end if;
4206 end loop;
4207 close pickup_loc_cur;
4208
4209 return l_pool_indicator;
4210
4211 end r_checkif_orig_ispool;
4212
4213
4214
4215
4216
4217 function r_checkif_dest_ispool (p_plan_id in number,p_delivery_id in number,p_delivery_leg_id in number,p_drop_off_stop_id in number)
4218 return varchar2 is
4219 l_pool_indicator varchar2(1) := null;
4220 l_loc_id number;
4221 leg_dropoff_loc_id number;
4222
4223 cursor dropoff_loc_cur is
4224 select mt.origin_location_id
4225 from mst_deliveries md
4226 , mst_delivery_legs mdl
4227 , mst_trips mt
4228 where md.plan_id = p_plan_id
4229 and md.delivery_id = p_delivery_id
4230 and mdl.plan_id = md.plan_id
4231 and mdl.delivery_id = md.delivery_id
4232 and mdl.delivery_leg_id <> p_delivery_leg_id
4233 and mt.plan_id = mdl.plan_id
4234 and mt.trip_id = mdl.trip_id;
4235
4236 begin
4237
4238 select mts.stop_location_id
4239 into leg_dropoff_loc_id
4240 from mst_trip_stops mts
4241 where mts.plan_id = p_plan_id
4242 and mts.stop_id = p_drop_off_stop_id;
4243
4244 open dropoff_loc_cur;
4245 loop
4246 fetch dropoff_loc_cur into l_loc_id;
4247 exit when dropoff_loc_cur%NOTFOUND;
4248
4249 if l_loc_id = leg_dropoff_loc_id then
4250 l_pool_indicator := 'P';
4251 return l_pool_indicator;
4252 end if;
4253 end loop;
4254 close dropoff_loc_cur;
4255
4256 return l_pool_indicator;
4257
4258 end r_checkif_dest_ispool;
4259
4260 PROCEDURE Execute_Report (ERRBUF OUT NOCOPY VARCHAR2
4261 ,RETCODE OUT NOCOPY VARCHAR2
4262 , request_id out nocopy number
4263 , arg1 in number
4264 , arg2 in number
4265 , arg3 in number
4266 , arg4 in number
4267 , arg5 in number
4268 , arg6 in number
4269 , arg7 in varchar2
4270 , arg8 in varchar2
4271 , arg9 in number
4272 , arg10 in number
4273 ) IS
4274 l_req_id number := 0;
4275 BEGIN
4276 if arg1 = 1 then
4277 l_req_id := fnd_request.submit_request ('MST', 'MSTMASSU', '', '', FALSE, arg2, arg4, arg5, arg7, arg8, chr(0), '',
4278 '', '', '', '', '', '', '', '', '', '',
4279 '', '', '', '', '', '', '', '', '', '',
4280 '', '', '', '', '', '', '', '', '', '',
4281 '', '', '', '', '', '', '', '', '', '',
4282 '', '', '', '', '', '', '', '', '', '',
4283 '', '', '', '', '', '', '', '', '', '',
4284 '', '', '', '', '', '', '', '', '', '',
4285 '', '', '', '', '', '', '', '', '', '',
4286 '', '', '', '', '', '', '', '');
4287 elsif arg1 = 2 then
4288 l_req_id := fnd_request.submit_request ('MST', 'MSTMCLSD', '', '', FALSE, arg2, arg3, arg4, arg5, arg7, arg8, chr(0),
4289 '', '', '', '', '', '', '', '', '', '',
4290 '', '', '', '', '', '', '', '', '', '',
4291 '', '', '', '', '', '', '', '', '', '',
4292 '', '', '', '', '', '', '', '', '', '',
4293 '', '', '', '', '', '', '', '', '', '',
4294 '', '', '', '', '', '', '', '', '', '',
4295 '', '', '', '', '', '', '', '', '', '',
4296 '', '', '', '', '', '', '', '', '', '',
4297 '', '', '', '', '', '', '', '');
4298 elsif arg1 = 3 then
4299 l_req_id := fnd_request.submit_request ('MST', 'MSTMAORD', '', '', FALSE, arg2, arg4, arg5, arg7, arg8, chr(0), '',
4300 '', '', '', '', '', '', '', '', '', '',
4301 '', '', '', '', '', '', '', '', '', '',
4302 '', '', '', '', '', '', '', '', '', '',
4303 '', '', '', '', '', '', '', '', '', '',
4304 '', '', '', '', '', '', '', '', '', '',
4305 '', '', '', '', '', '', '', '', '', '',
4306 '', '', '', '', '', '', '', '', '', '',
4307 '', '', '', '', '', '', '', '', '', '',
4308 '', '', '', '', '', '', '', '');
4309 elsif arg1 = 4 then
4310 l_req_id := fnd_request.submit_request ('MST', 'MSTFLDSH', '', '', FALSE, arg2, arg4, arg5, arg6, arg7, arg8, chr(0),
4311 '', '', '', '', '', '', '', '', '', '',
4312 '', '', '', '', '', '', '', '', '', '',
4313 '', '', '', '', '', '', '', '', '', '',
4314 '', '', '', '', '', '', '', '', '', '',
4315 '', '', '', '', '', '', '', '', '', '',
4316 '', '', '', '', '', '', '', '', '', '',
4317 '', '', '', '', '', '', '', '', '', '',
4318 '', '', '', '', '', '', '', '', '', '',
4319 '', '', '', '', '', '', '', '');
4320 elsif arg1 = 5 then
4321 l_req_id := fnd_request.submit_request ('MST', 'MSTPOSPA', '', '', FALSE, arg2, arg4, arg5, arg6, arg7, arg8, chr(0),
4322 '', '', '', '', '', '', '', '', '', '',
4323 '', '', '', '', '', '', '', '', '', '',
4324 '', '', '', '', '', '', '', '', '', '',
4325 '', '', '', '', '', '', '', '', '', '',
4326 '', '', '', '', '', '', '', '', '', '',
4327 '', '', '', '', '', '', '', '', '', '',
4328 '', '', '', '', '', '', '', '', '', '',
4329 '', '', '', '', '', '', '', '', '', '',
4330 '', '', '', '', '', '', '', '');
4331 elsif arg1 = 6 then
4332 -- l_req_id := fnd_request.submit_request ('MST', 'MSTSHLAD', '', '', FALSE, arg2, arg5, arg8, chr(0), '', '', '',
4333 --Bug_Fix for 3713710
4334 l_req_id := fnd_request.submit_request ('MST', 'MSTSHLAD', '', '', FALSE, arg2, arg4, arg5, arg7, arg8, chr(0), '',
4335 '', '', '', '', '', '', '', '', '', '',
4336 '', '', '', '', '', '', '', '', '', '',
4337 '', '', '', '', '', '', '', '', '', '',
4338 '', '', '', '', '', '', '', '', '', '',
4339 '', '', '', '', '', '', '', '', '', '',
4340 '', '', '', '', '', '', '', '', '', '',
4341 '', '', '', '', '', '', '', '', '', '',
4342 '', '', '', '', '', '', '', '', '', '',
4343 '', '', '', '', '', '', '', '');
4344 end if;
4345
4346 request_id := l_req_id;
4347
4348 if (l_req_id = 0) then
4349 -- fnd_message.retrieve;
4350 -- fnd_message.error;
4351 null;
4352 else
4353 commit;
4354 end if;
4355 exception
4356 when others then
4357 ERRBUF := SQLERRM;
4358 RETCODE := SQLCODE;
4359
4360 END Execute_Report;
4361
4362 FUNCTION get_cost_wihtout_cm_for_trips (p_plan_id number, p_trip_id1 number, p_trip_id2 number)
4363 RETURN NUMBER IS
4364 l_cost NUMBER;
4365 BEGIN
4366 SELECT SUM(total_basic_transport_cost + total_accessorial_cost +
4367 total_handling_cost + total_layover_cost +
4368 total_load_unload_cost + total_stop_cost)
4369 INTO l_cost
4370 FROM mst_trips
4371 WHERE plan_id = p_plan_id
4372 AND trip_id IN (p_trip_id1, p_trip_id2);
4373 RETURN l_cost;
4374 EXCEPTION
4375 WHEN OTHERS THEN
4376 RETURN 0;
4377 END get_cost_wihtout_cm_for_trips;
4378
4379 FUNCTION get_load_type (p_plan_id IN NUMBER,
4380 p_trip_id IN NUMBER)
4381 RETURN VARCHAR2 IS
4382 l_count NUMBER;
4383 l_str VARCHAR2(80);
4384 l_lookup_code NUMBER;
4385 BEGIN
4386 SELECT COUNT('x')
4387 INTO l_count
4388 FROM mst_delivery_legs
4389 WHERE plan_id = p_plan_id
4390 AND trip_id = p_trip_id;
4391
4392 If l_count > 0 Then
4393 l_lookup_code := 2;
4394 Else
4395 l_lookup_code := 1;
4396 End If;
4397 SELECT meaning
4398 INTO l_str
4399 FROM mfg_lookups
4400 WHERE lookup_type = 'MST_TRIP_LOADING_STATUS'
4401 AND lookup_code = l_lookup_code;
4402
4403 RETURN l_str;
4404 EXCEPTION
4405 WHEN OTHERS THEN
4406 RETURN '' ;
4407 END get_load_type;
4408
4409 FUNCTION GET_ACT_TOKENIZED_EXCEPTION(P_PLAN_ID IN NUMBER,
4410 P_OUT_REQUEST_DETAIL_ID IN NUMBER,
4411 P_EXCEPTION_TYPE IN NUMBER,
4412 P_LINE_NUM IN NUMBER)
4413 RETURN VARCHAR2 IS
4414 l_message VARCHAR2(2000);
4415 l_id1 NUMBER;
4416 l_id2 NUMBER;
4417 l_id3 NUMBER;
4418 l_id4 NUMBER;
4419 l_id5 NUMBER;
4420 l_id6 NUMBER;
4421 l_id7 NUMBER;
4422 l_id8 NUMBER;
4423 l_id9 NUMBER;
4424 l_str1 VARCHAR2(2500);
4425 l_str2 VARCHAR2(2500);
4426 l_str3 VARCHAR2(2500);
4427 l_date1 date;
4428 l_date2 date;
4429
4430 CURSOR l_city_cur (p_location_id IN NUMBER) IS
4431 SELECT city
4432 FROM wsh_locations
4433 WHERE wsh_location_id = p_location_id;
4434
4435 CURSOR l_carrier_cur (p_carrier_id IN NUMBER) IS
4436 SELECT freight_code
4437 FROM wsh_carriers
4438 WHERE carrier_id = p_carrier_id;
4439
4440 CURSOR l_uom_cur IS
4441 SELECT distance_uom, weight_uom, volume_uom
4442 FROM mst_plans
4443 WHERE plan_id = P_PLAN_ID;
4444
4445 CURSOR l_vehicle_cur (p_vehicle_type_id IN NUMBER) IS
4446 SELECT msikfv.concatenated_segments
4447 FROM mtl_system_items_kfv msikfv,
4448 fte_vehicle_types fvt
4449 WHERE fvt.vehicle_type_id = p_vehicle_type_id
4450 AND fvt.organization_id = msikfv.organization_id
4451 AND fvt.inventory_item_id = msikfv.inventory_item_id;
4452 l_uom l_uom_cur%ROWTYPE;
4453
4454 CURSOR l_lanes_cur (p_lane_id NUMBER) IS
4455 SELECT lane_number
4456 FROM fte_lanes
4457 WHERE lane_id = p_lane_id;
4458
4459 CURSOR l_fac_desc_cur (p_location_id NUMBER) IS
4460 SELECT description
4461 FROM fte_location_parameters
4462 WHERE location_id = p_location_id;
4463
4464 l_new_trip VARCHAR2(200);
4465
4466 BEGIN
4467 FND_MESSAGE.SET_NAME('MST', 'MST_NEW_TRIP');
4468 l_new_trip := FND_MESSAGE.GET;
4469 IF P_EXCEPTION_TYPE IN (100, 101, 102, 103) THEN
4470 SELECT nvl(number1, 0), round(nvl(number2, 0), 1)
4471 INTO l_id1, l_id2
4472 FROM mst_out_request_details
4473 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4474 AND exception_type = P_EXCEPTION_TYPE;
4475 l_str1 := ltrim(to_char(l_id2, '999990.0'));
4476 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
4477 fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
4478 fnd_message.set_token('SEVERITY', l_str1);
4479 l_message := fnd_message.get;
4480 ELSIF P_EXCEPTION_TYPE = 200 THEN
4481 IF P_LINE_NUM = 1 THEN
4482 SELECT number1, number2, number3, number4
4483 INTO l_id1, l_id2, l_id3, l_id4
4484 FROM mst_out_request_details
4485 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4486 AND exception_type = P_EXCEPTION_TYPE;
4487 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_200_1');
4488 IF l_id1 < 0 THEN
4489 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4490 ELSE
4491 fnd_message.set_token('TRIP_NUMBER', l_id1);
4492 END IF;
4493 Open l_city_cur(l_id3);
4494 Fetch l_city_cur Into l_str1;
4495 If l_city_cur%NotFound Then
4496 l_str1 := '';
4497 End If;
4498 Close l_city_cur;
4499 fnd_message.set_token('ORIGIN_CITY', l_str1);
4500
4501 Open l_city_cur(l_id4);
4502 Fetch l_city_cur Into l_str2;
4503 If l_city_cur%NotFound Then
4504 l_str2 := '';
4505 End If;
4506 Close l_city_cur;
4507 fnd_message.set_token('DESTINATION_CITY', l_str2);
4508
4509 Open l_carrier_cur(l_id2);
4510 Fetch l_carrier_cur Into l_str3;
4511 If l_carrier_cur%NotFound Then
4512 l_str3 := '';
4513 End If;
4514 Close l_carrier_cur;
4515 fnd_message.set_token('FREIGHT_CODE', l_str3);
4516
4517 SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
4518 INTO l_id1, l_id2
4519 FROM mst_out_request_details
4520 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4521 AND exception_type = P_EXCEPTION_TYPE;
4522 fnd_message.set_token('MAX_DISTANCE_IN_24HR', l_id2);
4523 fnd_message.set_token('DISTANCE_IN_24HR', l_id1);
4524
4525 SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
4526 INTO l_id1, l_id2
4527 FROM mst_out_request_details
4528 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4529 AND exception_type = P_EXCEPTION_TYPE;
4530
4531 Open l_uom_cur;
4532 Fetch l_uom_cur Into l_uom;
4533 If l_uom_cur%Found Then
4534 l_str1 := l_uom.distance_uom;
4535 Else
4536 l_str1 := '';
4537 End If;
4538 Close l_uom_cur;
4539 fnd_message.set_token('DISTANCE_UOM', l_str1);
4540 fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
4541 fnd_message.set_token('DISTANCE', l_id1);
4542 END IF;
4543 l_message := fnd_message.get;
4544 ELSIF P_EXCEPTION_TYPE = 201 THEN
4545 SELECT number1, number2, number3, number4, round(nvl(number5, 0)), round(nvl(number6, 0))
4546 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
4547 FROM mst_out_request_details
4548 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4549 AND exception_type = P_EXCEPTION_TYPE;
4550
4551 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
4552 fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
4553 fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id5);
4554 fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id6);
4555 Open l_uom_cur;
4556 Fetch l_uom_cur Into l_uom;
4557 If l_uom_cur%Found Then
4558 l_str1 := l_uom.distance_uom;
4559 Else
4560 l_str1 := '';
4561 End If;
4562 Close l_uom_cur;
4563 fnd_message.set_token('DISTANCE_UOM', l_str1);
4564 Open l_city_cur(l_id3);
4565 Fetch l_city_cur Into l_str1;
4566 If l_city_cur%NotFound Then
4567 l_str1 := '';
4568 End If;
4569 Close l_city_cur;
4570 fnd_message.set_token('ORIGIN_CITY', l_str1);
4571
4572 Open l_city_cur(l_id4);
4573 Fetch l_city_cur Into l_str2;
4574 If l_city_cur%NotFound Then
4575 l_str2 := '';
4576 End If;
4577 Close l_city_cur;
4578 fnd_message.set_token('DESTINATION_CITY', l_str2);
4579 Open l_carrier_cur(l_id2);
4580 Fetch l_carrier_cur Into l_str3;
4581 If l_carrier_cur%NotFound Then
4582 l_str3 := '';
4583 End If;
4584 Close l_carrier_cur;
4585 fnd_message.set_token('FREIGHT_CODE', l_str3);
4586 l_message := fnd_message.get;
4587 ELSIF P_EXCEPTION_TYPE = 202 THEN
4588 SELECT nvl(number1, -1)
4589 INTO l_id1
4590 FROM mst_out_request_details
4591 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4592 AND exception_type = P_EXCEPTION_TYPE;
4593
4594 SELECT trip_number, origin_location_id, destination_location_id
4595 INTO l_id2, l_id3, l_id4
4596 FROM mst_trips
4597 WHERE plan_id = P_PLAN_ID
4598 AND trip_id = l_id1;
4599
4600 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
4601 IF l_id2 < 0 THEN
4602 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4603 ELSE
4604 fnd_message.set_token('TRIP_NUMBER', l_id2);
4605 END IF;
4606
4607 Open l_city_cur(l_id3);
4608 Fetch l_city_cur Into l_str1;
4609 If l_city_cur%NotFound Then
4610 l_str1 := '';
4611 End If;
4612 Close l_city_cur;
4613 fnd_message.set_token('ORIGIN_CITY', l_str1);
4614
4615 Open l_city_cur(l_id4);
4616 Fetch l_city_cur Into l_str2;
4617 If l_city_cur%NotFound Then
4618 l_str2 := '';
4619 End If;
4620 Close l_city_cur;
4621 fnd_message.set_token('DESTINATION_CITY', l_str2);
4622 l_message := fnd_message.get;
4623 ELSIF P_EXCEPTION_TYPE = 203 THEN
4624 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
4625 SELECT nvl(number1, -1), nvl(number2, -1)
4626 INTO l_id1, l_id2
4627 FROM mst_out_request_details
4628 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4629 AND exception_type = P_EXCEPTION_TYPE;
4630
4631 SELECT trip_number
4632 INTO l_id3
4633 FROM mst_trips
4634 WHERE trip_id = l_id1
4635 AND plan_id = P_PLAN_ID;
4636
4637 IF l_id3 < 0 THEN
4638 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4639 ELSE
4640 fnd_message.set_token('TRIP_NUMBER', l_id3);
4641 END IF;
4642
4643 SELECT stop_location_id, stop_sequence_number
4644 INTO l_id4, l_id5
4645 FROM mst_trip_stops
4646 WHERE plan_id = P_PLAN_ID
4647 AND stop_id = l_id2;
4648 fnd_message.set_token('STOP_SEQUENCE_NUMBER', l_id5);
4649
4650 Open l_city_cur(l_id4);
4651 Fetch l_city_cur Into l_str1;
4652 If l_city_cur%NotFound Then
4653 l_str1 := '';
4654 End If;
4655 Close l_city_cur;
4656 fnd_message.set_token('CITY', l_str1);
4657 l_message := fnd_message.get;
4658 ELSIF P_EXCEPTION_TYPE = 204 THEN
4659 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
4660 SELECT number1, number2, number3, round(nvl(number4, 0)), round(nvl(number5, 0))
4661 INTO l_id1, l_id2, l_id3, l_id4, l_id5
4662 FROM mst_out_request_details
4663 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4664 AND exception_type = P_EXCEPTION_TYPE;
4665
4666 IF l_id1 < 0 THEN
4667 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4668 ELSE
4669 fnd_message.set_token('TRIP_NUMBER', l_id1);
4670 END IF;
4671
4672 fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id4);
4673 fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id5);
4674
4675 Open l_city_cur(l_id2);
4676 Fetch l_city_cur Into l_str1;
4677 If l_city_cur%NotFound Then
4678 l_str1 := '';
4679 End If;
4680 Close l_city_cur;
4681 fnd_message.set_token('ORIGIN_CITY', l_str1);
4682
4683 Open l_city_cur(l_id3);
4684 Fetch l_city_cur Into l_str2;
4685 If l_city_cur%NotFound Then
4686 l_str2 := '';
4687 End If;
4688 Close l_city_cur;
4689 fnd_message.set_token('DESTINATION_CITY', l_str2);
4690 l_message := fnd_message.get;
4691 ELSIF P_EXCEPTION_TYPE = 205 THEN
4692 SELECT number1, round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0)), round(nvl(number5, 0)), round(nvl(number6, 0)), round(nvl(number7, 0)), round(nvl(number8, 0)), round(nvl(number9, 0))
4693 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8, l_id9
4694 FROM mst_out_request_details
4695 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4696 AND exception_type = P_EXCEPTION_TYPE;
4697 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
4698 IF l_id1 < 0 THEN
4699 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4700 ELSE
4701 fnd_message.set_token('TRIP_NUMBER', l_id1);
4702 END IF;
4703 fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704 fnd_message.set_token('LAYOVER_TIME', l_id2);
4705 fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706 fnd_message.set_token('DRIVING_TIME', l_id4);
4707 fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708 fnd_message.set_token('DUTY_TIME', l_id6);
4709 fnd_message.set_token('MAX_DISTANCE', l_id9);
4710 fnd_message.set_token('DISTANCE', l_id8);
4711 l_message := fnd_message.get;
4712 ELSIF P_EXCEPTION_TYPE = 206 THEN
4713 IF P_LINE_NUM = 1 THEN
4714 SELECT number1, number2, number3, number4
4715 INTO l_id1, l_id2, l_id3, l_id4
4716 FROM mst_out_request_details
4717 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4718 AND exception_type = P_EXCEPTION_TYPE;
4719 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
4720 IF l_id1 < 0 THEN
4721 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4722 ELSE
4723 fnd_message.set_token('TRIP_NUMBER', l_id1);
4724 END IF;
4725 Open l_city_cur(l_id3);
4726 Fetch l_city_cur Into l_str1;
4727 If l_city_cur%NotFound Then
4728 l_str1 := '';
4729 End If;
4730 Close l_city_cur;
4731 fnd_message.set_token('ORIGIN_CITY', l_str1);
4732
4733 Open l_city_cur(l_id4);
4734 Fetch l_city_cur Into l_str2;
4735 If l_city_cur%NotFound Then
4736 l_str2 := '';
4737 End If;
4738 Close l_city_cur;
4739 fnd_message.set_token('DESTINATION_CITY', l_str2);
4740
4741 Open l_carrier_cur(l_id2);
4742 Fetch l_carrier_cur Into l_str3;
4743 If l_carrier_cur%NotFound Then
4744 l_str3 := '';
4745 End If;
4746 Close l_carrier_cur;
4747 fnd_message.set_token('FREIGHT_CODE', l_str3);
4748 l_message := fnd_message.get;
4749 ELSIF P_LINE_NUM = 2 THEN
4750 SELECT nvl(number5, 0), nvl(number6, 0)
4751 INTO l_id1, l_id2
4752 FROM mst_out_request_details
4753 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4754 AND exception_type = P_EXCEPTION_TYPE;
4755 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
4756 fnd_message.set_token('MAX_STOPS', l_id2);
4757 fnd_message.set_token('STOPS', l_id1);
4758 l_message := fnd_message.get;
4759 END IF;
4760 ELSIF P_EXCEPTION_TYPE = 207 THEN
4761 IF P_LINE_NUM = 1 THEN
4762 SELECT number1, number2, number3, number4
4763 INTO l_id1, l_id2, l_id3, l_id4
4764 FROM mst_out_request_details
4765 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4766 AND exception_type = P_EXCEPTION_TYPE;
4767 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_207_1');
4768 IF l_id1 < 0 THEN
4769 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4770 ELSE
4771 fnd_message.set_token('TRIP_NUMBER', l_id1);
4772 END IF;
4773 Open l_city_cur(l_id3);
4774 Fetch l_city_cur Into l_str1;
4775 If l_city_cur%NotFound Then
4776 l_str1 := '';
4777 End If;
4778 Close l_city_cur;
4779 fnd_message.set_token('ORIGIN_CITY', l_str1);
4780
4781 Open l_city_cur(l_id4);
4782 Fetch l_city_cur Into l_str2;
4783 If l_city_cur%NotFound Then
4784 l_str2 := '';
4785 End If;
4786 Close l_city_cur;
4787 fnd_message.set_token('DESTINATION_CITY', l_str2);
4788
4789 Open l_carrier_cur(l_id2);
4790 Fetch l_carrier_cur Into l_str3;
4791 If l_carrier_cur%NotFound Then
4792 l_str3 := '';
4793 End If;
4794 Close l_carrier_cur;
4795 fnd_message.set_token('FREIGHT_CODE', l_str3);
4796
4797 SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
4798 INTO l_id1, l_id2
4799 FROM mst_out_request_details
4800 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4801 AND exception_type = P_EXCEPTION_TYPE;
4802 fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id2);
4803 fnd_message.set_token('DRIVING_TIME', l_id1);
4804
4805 SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
4806 INTO l_id1, l_id2
4807 FROM mst_out_request_details
4808 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4809 AND exception_type = P_EXCEPTION_TYPE;
4810 fnd_message.set_token('ALLOWED_DUTY_TIME', l_id2);
4811 fnd_message.set_token('DUTY_TIME', l_id1);
4812
4813 SELECT round(nvl(number9, 0)), round(nvl(number10, 0))
4814 INTO l_id1, l_id2
4815 FROM mst_out_request_details
4816 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4817 AND exception_type = P_EXCEPTION_TYPE;
4818 fnd_message.set_token('MIN_LAYOVER_TIME', l_id2);
4819 fnd_message.set_token('LAYOVER_TIME', l_id1);
4820
4821 SELECT round(nvl(number11, 0)), round(nvl(number12, 0))
4822 INTO l_id1, l_id2
4823 FROM mst_out_request_details
4824 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4825 AND exception_type = P_EXCEPTION_TYPE;
4826 fnd_message.set_token('MAX_TIME', l_id2);
4827 fnd_message.set_token('TIME', l_id1);
4828 END IF;
4829 l_message := fnd_message.get;
4830 ELSIF P_EXCEPTION_TYPE = 208 THEN
4831 SELECT number1, number2, number3, number4
4832 INTO l_id1, l_id2, l_id3, l_id4
4833 FROM mst_out_request_details
4834 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4835 AND exception_type = P_EXCEPTION_TYPE;
4836 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
4837 IF l_id1 < 0 THEN
4838 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4839 ELSE
4840 fnd_message.set_token('TRIP_NUMBER', l_id1);
4841 END IF;
4842 Open l_city_cur(l_id3);
4843 Fetch l_city_cur Into l_str1;
4844 If l_city_cur%NotFound Then
4845 l_str1 := '';
4846 End If;
4847 Close l_city_cur;
4848 fnd_message.set_token('ORIGIN_CITY', l_str1);
4849 Open l_city_cur(l_id4);
4850 Fetch l_city_cur Into l_str2;
4851 If l_city_cur%NotFound Then
4852 l_str2 := '';
4853 End If;
4854 Close l_city_cur;
4855 fnd_message.set_token('DESTINATION_CITY', l_str2);
4856
4857 Open l_carrier_cur(l_id2);
4858 Fetch l_carrier_cur Into l_str3;
4859 If l_carrier_cur%NotFound Then
4860 l_str3 := '';
4861 End If;
4862 Close l_carrier_cur;
4863 fnd_message.set_token('FREIGHT_CODE', l_str3);
4864 l_message := fnd_message.get;
4865 ELSIF P_EXCEPTION_TYPE = 220 THEN --Item Mode Incompatibility
4866 IF P_LINE_NUM = 1 THEN
4867 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_220_1');
4868 SELECT number1, number2, char1
4869 INTO l_id1, l_id2, l_str1
4870 FROM mst_out_request_details
4871 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4872 AND exception_type = P_EXCEPTION_TYPE;
4873 IF l_id1 < 0 THEN
4874 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4875 ELSE
4876 fnd_message.set_token('TRIP_NUMBER', l_id1);
4877 END IF;
4878 fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4879
4880 SELECT wlk.meaning
4881 INTO l_str2
4882 FROM wsh_lookups wlk
4883 WHERE wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
4884 and wlk.lookup_code = l_str1;
4885 fnd_message.set_token('MODE_OF_TRANSPORT', l_str2);
4886
4887 -- item_description
4888 select msitl.description into l_str3
4889 from mtl_system_items_tl msitl, mst_delivery_details mdd
4890 where mdd.plan_id = P_PLAN_ID
4891 and mdd.delivery_detail_id = l_id2
4892 and mdd.inventory_item_id = msitl.inventory_item_id
4893 and mdd.organization_id = msitl.organization_id
4894 and msitl.language = userenv('LANG');
4895 fnd_message.set_token('ITEM_DESCRIPTION', l_str3);
4896 l_message := fnd_message.get;
4897 END IF;
4898 ELSIF P_EXCEPTION_TYPE = 221 THEN --Item Carrier Incompatibility
4899 IF P_LINE_NUM = 1 THEN
4900 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_221_1');
4901 --Trip_number
4902 SELECT number1, number2, number3
4903 INTO l_id1, l_id2, l_id3
4904 FROM mst_out_request_details
4905 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4906 AND exception_type = P_EXCEPTION_TYPE;
4907 IF l_id1 < 0 THEN
4908 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4909 ELSE
4910 fnd_message.set_token('TRIP_NUMBER', l_id1);
4911 END IF;
4912 fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4913
4914 -- item_description
4915 select msitl.description into l_str2
4916 from mtl_system_items_tl msitl, mst_delivery_details mdd
4917 where mdd.plan_id = P_PLAN_ID
4918 and mdd.delivery_detail_id = l_id2
4919 and mdd.inventory_item_id = msitl.inventory_item_id
4920 and mdd.organization_id = msitl.organization_id
4921 and msitl.language = userenv('LANG');
4922 fnd_message.set_token('ITEM_DESCRIPTION', l_str2);
4923
4924 --carrier name
4925 SELECT freight_code into l_str3
4926 FROM wsh_carriers
4927 WHERE carrier_id = l_id3;
4928 fnd_message.set_token('CARRIER_NAME', l_str3);
4929
4930 l_message := fnd_message.get;
4931 END IF;
4932 ELSIF P_EXCEPTION_TYPE = 222 THEN --Ship Set Violation
4933 IF P_LINE_NUM = 1 THEN
4934 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_222_1');
4935 --Ship_set
4936 SELECT number1
4937 INTO l_id1
4938 FROM mst_out_request_details
4939 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4940 AND exception_type = P_EXCEPTION_TYPE;
4941
4942 -- Ship_set Name
4943 select set_name into l_str1
4944 from oe_sets os
4945 where os.set_id=l_id1;
4946 fnd_message.set_token('SHIP_SET', l_str1);
4947 l_message := fnd_message.get;
4948 END IF;
4949 ELSIF P_EXCEPTION_TYPE = 223 THEN --Ship Set Violation
4950 IF P_LINE_NUM = 1 THEN
4951 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_223_1');
4952 --Ship_set
4953 SELECT number1
4954 INTO l_id1
4955 FROM mst_out_request_details
4956 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4957 AND exception_type = P_EXCEPTION_TYPE;
4958
4959 -- Ship_set Name
4960 select set_name into l_str1
4961 from oe_sets os
4962 where os.set_id=l_id1;
4963 fnd_message.set_token('ARRIVAL_SET', l_str1);
4964 l_message := fnd_message.get;
4965 END IF;
4966 ELSIF P_EXCEPTION_TYPE = 300 THEN
4967 SELECT number1, number2, number3, number4, nvl(number5, 0), nvl(number6, 0)
4968 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
4969 FROM mst_out_request_details
4970 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4971 AND exception_type = P_EXCEPTION_TYPE;
4972 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_300_1');
4973 Open l_carrier_cur(l_id1);
4974 Fetch l_carrier_cur Into l_str1;
4975 If l_carrier_cur%NotFound Then
4976 l_str1 := '';
4977 End If;
4978 fnd_message.set_token('FREIGHT_CODE', l_str1);
4979 Close l_carrier_cur;
4980 Open l_vehicle_cur(l_id2);
4981 Fetch l_vehicle_cur Into l_str2;
4982 If l_vehicle_cur%NotFound Then
4983 l_str2 := '';
4984 End If;
4985 Close l_vehicle_cur;
4986 fnd_message.set_token('VEHICLE_TYPE', l_str2);
4987
4988 Open l_lanes_cur (l_id3);
4989 Fetch l_lanes_cur Into l_str3;
4990 If l_lanes_cur%NotFound Then
4991 l_str3 := '';
4992 End If;
4993 Close l_lanes_cur;
4994 fnd_message.set_token('LANE', l_str3);
4995 fnd_message.set_token('TOTAL_LOADS', l_id5);
4996 fnd_message.set_token('VEHICLE_AVAILABILITY', l_id6);
4997 l_message := fnd_message.get;
4998 ELSIF P_EXCEPTION_TYPE = 301 THEN -- Carrier commitment under-utilization
4999 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
5000
5001 SELECT number1, number3, number4, number5, date1, date2
5002 INTO l_id1, l_id3, l_id4, l_id5, l_date1, l_date2
5003 FROM mst_out_request_details
5004 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5005 AND exception_type = P_EXCEPTION_TYPE;
5006
5007 fnd_message.set_token('START_DATE', fnd_date.DATE_TO_CHARDATE(l_date1));
5008 fnd_message.set_token('END_DATE', fnd_date.DATE_TO_CHARDATE(l_date2));
5009 fnd_message.set_token('SHORTFALL', round(l_id5));
5010
5011 --Carrier name
5012 SELECT freight_code INTO l_str1
5013 FROM wsh_carriers
5014 WHERE carrier_id = l_id1;
5015 fnd_message.set_token('CARRIER', l_str1);
5016
5017 --get Lane_Number
5018 select LANE
5019 into l_str2
5020 from(
5021 select fl.lane_number "LANE"
5022 from fte_lanes fl
5023 where fl.lane_id = l_id3
5024 UNION ALL
5025 select flg.name "LANE"
5026 from fte_lane_groups flg
5027 where flg.lane_group_id = l_id4);
5028 --as per dld, Lane_Number = fl.lane_number or flg.name whichever is not null
5029 fnd_message.set_token('LANE_NAME', l_str2);
5030 l_message := fnd_message.get;
5031 ELSIF P_EXCEPTION_TYPE = 400 THEN
5032 SELECT number1, number2
5033 INTO l_id1, l_id2
5034 FROM mst_out_request_details
5035 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5036 AND exception_type = P_EXCEPTION_TYPE;
5037 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
5038 IF l_id1 < 0 THEN
5039 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5040 ELSE
5041 fnd_message.set_token('TRIP_NUMBER', l_id1);
5042 END IF;
5043
5044 Open l_fac_desc_cur (l_id2);
5045 Fetch l_fac_desc_cur Into l_str1;
5046 If l_fac_desc_cur%NotFound Then
5047 l_str1 := '';
5048 End If;
5049 Close l_fac_desc_cur;
5050 fnd_message.set_token('FACILITY_DESC', l_str1);
5051 l_message := fnd_message.get;
5052 ELSIF P_EXCEPTION_TYPE = 401 THEN
5053 SELECT number1, number2, round(nvl(number3, 0), 2), round(nvl(number4, 0), 2)
5054 INTO l_id1, l_id2, l_id3, l_id4
5055 FROM mst_out_request_details
5056 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5057 AND exception_type = P_EXCEPTION_TYPE;
5058 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
5059 IF l_id1 < 0 THEN
5060 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5061 ELSE
5062 fnd_message.set_token('TRIP_NUMBER', l_id1);
5063 END IF;
5064
5065 Open l_fac_desc_cur (l_id2);
5066 Fetch l_fac_desc_cur Into l_str1;
5067 If l_fac_desc_cur%NotFound Then
5068 l_str1 := '';
5069 End If;
5070 Close l_fac_desc_cur;
5071 fnd_message.set_token('FACILITY_DESC', l_str1);
5072 fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id4, '999990.0')));
5073 fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id3, '999990.0')));
5074 l_message := fnd_message.get;
5075 ELSIF P_EXCEPTION_TYPE = 402 THEN
5076 SELECT number1, number2, number3, number4, round(nvl(number5, 0), 2), round(nvl(number6, 0), 2)
5077 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5078 FROM mst_out_request_details
5079 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5080 AND exception_type = P_EXCEPTION_TYPE;
5081 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_402_1');
5082 fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
5083 IF l_id2 < 0 THEN
5084 fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5085 ELSE
5086 fnd_message.set_token('TRIP_NUMBER1', l_id2);
5087 END IF;
5088 IF l_id3 < 0 THEN
5089 fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5090 ELSE
5091 fnd_message.set_token('TRIP_NUMBER2', l_id3);
5092 END IF;
5093
5094 Open l_fac_desc_cur (l_id4);
5095 Fetch l_fac_desc_cur Into l_str1;
5096 If l_fac_desc_cur%NotFound Then
5097 l_str1 := '';
5098 End If;
5099 Close l_fac_desc_cur;
5100 fnd_message.set_token('FACILITY_DESC', l_str1);
5101 fnd_message.set_token('REQUIRED_CONNECT_TIME', ltrim(to_char(l_id6, '999990.0')));
5102 fnd_message.set_token('CONNECT_TIME', ltrim(to_char(l_id5, '999990.0')));
5103 l_message := fnd_message.get;
5104 ELSIF P_EXCEPTION_TYPE = 403 THEN
5105 SELECT number1, number2, number3
5106 INTO l_id1, l_id2, l_id3
5107 FROM mst_out_request_details
5108 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5109 AND exception_type = P_EXCEPTION_TYPE;
5110 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_403_1');
5111 fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
5112 IF l_id2 < 0 THEN
5113 fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5114 ELSE
5115 fnd_message.set_token('TRIP_NUMBER1', l_id2);
5116 END IF;
5117 IF l_id3 < 0 THEN
5118 fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5119 ELSE
5120 fnd_message.set_token('TRIP_NUMBER2', l_id3);
5121 END IF;
5122 l_message := fnd_message.get;
5123 ELSIF P_EXCEPTION_TYPE = 406 THEN --Carrier Facility Appointment violation
5124 IF P_LINE_NUM = 1 THEN
5125 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_406_1');
5126 SELECT number1, number2, number3, number4
5127 INTO l_id1, l_id2, l_id3, l_id4
5128 FROM mst_out_request_details
5129 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5130 AND exception_type = P_EXCEPTION_TYPE;
5131 IF l_id1 < 0 THEN
5132 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5133 ELSE
5134 fnd_message.set_token('TRIP_NUMBER', l_id1);
5135 END IF;
5136
5137 --facility_description
5138 select flp.facility_code
5139 into l_str1
5140 from fte_location_parameters flp
5141 where flp.location_id = l_id2;
5142
5143 fnd_message.set_token('FACILITY_DESCRIPTION', l_str1);
5144
5145 --carrier name
5146 SELECT freight_code INTO l_str2
5147 FROM wsh_carriers
5148 WHERE carrier_id = l_id4;
5149 fnd_message.set_token('CARRIER_NAME', l_str2);
5150
5151 l_message := fnd_message.get;
5152 END IF;
5153 ELSIF P_EXCEPTION_TYPE = 500 THEN
5154 SELECT number1, number2, number3, number4, round(nvl(number5, 0)), number6, round(nvl(number7, 0))
5155 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5156 FROM mst_out_request_details
5157 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5158 AND exception_type = P_EXCEPTION_TYPE;
5159 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
5160 IF l_id1 < 0 THEN
5161 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5162 ELSE
5163 fnd_message.set_token('TRIP_NUMBER', l_id1);
5164 END IF;
5165 fnd_message.set_token('LOW_COST_CARRIER_COST', l_id7);
5166
5167 Open l_city_cur(l_id2);
5168 Fetch l_city_cur Into l_str1;
5169 If l_city_cur%NotFound Then
5170 l_str1 := '';
5171 End If;
5172 Close l_city_cur;
5173 fnd_message.set_token('ORIGIN_CITY', l_str1);
5174
5175 Open l_city_cur(l_id3);
5176 Fetch l_city_cur Into l_str2;
5177 If l_city_cur%NotFound Then
5178 l_str2 := '';
5179 End If;
5180 Close l_city_cur;
5181 fnd_message.set_token('DESTINATION_CITY', l_str2);
5182
5183 Open l_carrier_cur(l_id4);
5184 Fetch l_carrier_cur Into l_str1;
5185 If l_carrier_cur%NotFound Then
5186 l_str1 := '';
5187 End If;
5188 Close l_carrier_cur;
5189 fnd_message.set_token('FREIGHT_CODE', l_str1);
5190
5191 Open l_carrier_cur(l_id6);
5192 Fetch l_carrier_cur Into l_str2;
5193 If l_carrier_cur%NotFound Then
5194 l_str2 := '';
5195 End If;
5196 Close l_carrier_cur;
5197 fnd_message.set_token('LOW_COST_CARRIER', l_str2);
5198 fnd_message.set_token('COST', l_id5);
5199 l_message := fnd_message.get;
5200 ELSIF P_EXCEPTION_TYPE = 501 THEN
5201 SELECT number1
5202 INTO l_id1
5203 FROM mst_out_request_details
5204 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5205 AND exception_type = P_EXCEPTION_TYPE;
5206 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
5207 fnd_message.set_token('TRIP_NUMBER', l_id1);
5208 l_message := fnd_message.get;
5209 ELSIF P_EXCEPTION_TYPE = 600 THEN
5210 SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7), number8
5211 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
5212 FROM mst_out_request_details
5213 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5214 AND exception_type = P_EXCEPTION_TYPE;
5215 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5216 IF l_id1 < 0 THEN
5217 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5218 ELSE
5219 fnd_message.set_token('TRIP_NUMBER', l_id1);
5220 END IF;
5221
5222 SELECT volume_uom
5223 INTO l_str1
5224 FROM mst_plans
5225 WHERE plan_id = P_PLAN_ID;
5226
5227 SELECT msikfv.internal_volume * get_uom_conversion_rate (msikfv.volume_uom_code,
5228 l_str1,
5229 msikfv.organization_id,
5230 msikfv.inventory_item_id)
5231 INTO l_id9
5232 FROM mtl_system_items_kfv msikfv,
5233 fte_vehicle_types fvt
5234 WHERE msikfv.inventory_item_id = fvt.inventory_item_id
5235 AND msikfv.organization_id = fvt.organization_id
5236 AND fvt.vehicle_type_id = l_id8;
5237
5238 If l_id9 is not null Then
5239 fnd_message.set_token('PHYSICAL_CAPACITY', l_id9);
5240 Else
5241 fnd_message.set_token('PHYSICAL_CAPACITY', '');
5242 End If;
5243
5244 If l_id3 > 0 Then
5245 l_id8 := round((l_id2/l_id3) * 100);
5246 l_str1 := l_id8;
5247 Else
5248 l_str1 := ' ';
5249 End If;
5250 fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5251 fnd_message.set_token('PEAK_VOLUME', l_id2);
5252 fnd_message.set_token('MAX_VOLUME', l_id3);
5253
5254 If l_id5 > 0 Then
5255 l_id8 := round((l_id4/l_id5) * 100);
5256 l_str1 := l_id8;
5257 Else
5258 l_str1 := ' ';
5259 End If;
5260 fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5261 fnd_message.set_token('PEAK_WEIGHT', l_id4);
5262 fnd_message.set_token('MAX_WEIGHT', l_id5);
5263
5264 If l_id7 > 0 Then
5265 l_id8 := round((l_id6/l_id7) * 100);
5266 l_str1 := l_id8;
5267 Else
5268 l_str1 := ' ';
5269 End If;
5270 fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5271 fnd_message.set_token('PEAK_PALLETS', l_id6);
5272 fnd_message.set_token('MAX_PALLETS', l_id7);
5273
5274 Open l_uom_cur;
5275 Fetch l_uom_cur Into l_uom;
5276 If l_uom_cur%Found Then
5277 l_str1 := l_uom.weight_uom;
5278 l_str2 := l_uom.volume_uom;
5279 Else
5280 l_str1 := '';
5281 l_str2 := '';
5282 End If;
5283 Close l_uom_cur;
5284 fnd_message.set_token('WEIGHT_UOM', l_str1);
5285 fnd_message.set_token('VOLUME_UOM', l_str2);
5286 l_message := fnd_message.get;
5287 ELSIF P_EXCEPTION_TYPE = 601 THEN
5288 SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7)
5289 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5290 FROM mst_out_request_details
5291 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5292 AND exception_type = P_EXCEPTION_TYPE;
5293 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5294 IF l_id1 < 0 THEN
5295 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5296 ELSE
5297 fnd_message.set_token('TRIP_NUMBER', l_id1);
5298 END IF;
5299
5300 If l_id3 > 0 Then
5301 l_id8 := round((l_id2/l_id3) * 100);
5302 l_str1 := l_id8;
5303 Else
5304 l_str1 := ' ';
5305 End If;
5306 fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5307 fnd_message.set_token('PEAK_VOLUME', l_id2);
5308 fnd_message.set_token('MAX_VOLUME', l_id3);
5309
5310 If l_id5 > 0 Then
5311 l_id8 := round((l_id4/l_id5) * 100);
5312 l_str1 := l_id8;
5313 Else
5314 l_str1 := ' ';
5315 End If;
5316 fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5317 fnd_message.set_token('PEAK_WEIGHT', l_id4);
5318 fnd_message.set_token('MAX_WEIGHT', l_id5);
5319
5320 If l_id7 > 0 Then
5321 l_id8 := round((l_id6/l_id7) * 100);
5322 l_str1 := l_id8;
5323 Else
5324 l_str1 := ' ';
5325 End If;
5326 fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5327 fnd_message.set_token('PEAK_PALLETS', l_id6);
5328 fnd_message.set_token('MAX_PALLETS', l_id7);
5329
5330 Open l_uom_cur;
5331 Fetch l_uom_cur Into l_uom;
5332 If l_uom_cur%Found Then
5333 l_str1 := l_uom.weight_uom;
5334 l_str2 := l_uom.volume_uom;
5335 Else
5336 l_str1 := '';
5337 l_str2 := '';
5338 End If;
5339 Close l_uom_cur;
5340 fnd_message.set_token('WEIGHT_UOM', l_str1);
5341 fnd_message.set_token('VOLUME_UOM', l_str2);
5342 l_message := fnd_message.get;
5343 ELSIF P_EXCEPTION_TYPE = 602 THEN --Item Vehicle Incompatibility
5344 IF P_LINE_NUM = 1 THEN
5345 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_602_1');
5346
5347 SELECT number1, number2, number3
5348 INTO l_id1, l_id2, l_id3
5349 FROM mst_out_request_details
5350 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5351 AND exception_type = P_EXCEPTION_TYPE;
5352 IF l_id1 < 0 THEN
5353 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5354 ELSE
5355 fnd_message.set_token('TRIP_NUMBER', l_id1);
5356 END IF;
5357
5358 -- item_description
5359 select msitl.description into l_str1
5360 from mtl_system_items_tl msitl, mst_delivery_details mdd
5361 where mdd.plan_id = P_PLAN_ID
5362 and mdd.delivery_detail_id = l_id2
5363 and mdd.inventory_item_id = msitl.inventory_item_id
5364 and mdd.organization_id = msitl.organization_id
5365 and msitl.language = userenv('LANG');
5366 fnd_message.set_token('ITEM_DESCRIPTION', l_str1);
5367
5368 SELECT msikfv.concatenated_segments
5369 into l_str2
5370 FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
5371 WHERE fvt.vehicle_type_id = l_id3
5372 AND fvt.organization_id = msikfv.organization_id
5373 AND fvt.inventory_item_id = msikfv.inventory_item_id;
5374 fnd_message.set_token('VEHICLE_TYPE', l_str2);
5375 l_message := fnd_message.get;
5376 END IF;
5377 ELSIF P_EXCEPTION_TYPE IN (700, 701) THEN
5378 SELECT number1
5379 INTO l_id1
5380 FROM mst_out_request_details
5381 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5382 AND exception_type = P_EXCEPTION_TYPE;
5383 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5384
5385 Open l_fac_desc_cur (l_id1);
5386 Fetch l_fac_desc_cur Into l_str1;
5387 If l_fac_desc_cur%NotFound Then
5388 l_str1 := '';
5389 End If;
5390 Close l_fac_desc_cur;
5391 fnd_message.set_token('FACILITY_DESC', l_str1);
5392 l_message := fnd_message.get;
5393 ELSIF P_EXCEPTION_TYPE = 702 THEN
5394 SELECT number1, number2, number3
5395 INTO l_id1, l_id2, l_id3
5396 FROM mst_out_request_details
5397 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5398 AND exception_type = P_EXCEPTION_TYPE;
5399 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_702_1');
5400 IF l_id1 < 0 THEN
5401 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5402 ELSE
5403 fnd_message.set_token('TRIP_NUMBER', l_id1);
5404 END IF;
5405
5406 Open l_vehicle_cur(l_id2);
5407 Fetch l_vehicle_cur Into l_str2;
5408 If l_vehicle_cur%NotFound Then
5409 l_str2 := '';
5410 End If;
5411 Close l_vehicle_cur;
5412 fnd_message.set_token('VEHICLE_TYPE', l_str2);
5413
5414 Open l_fac_desc_cur (l_id3);
5415 Fetch l_fac_desc_cur Into l_str1;
5416 If l_fac_desc_cur%NotFound Then
5417 l_str1 := '';
5418 End If;
5419 Close l_fac_desc_cur;
5420 fnd_message.set_token('FACILITY_DESC', l_str1);
5421 l_message := fnd_message.get;
5422 ELSIF P_EXCEPTION_TYPE = 703 THEN
5423 SELECT number1, number2, number3
5424 INTO l_id1, l_id2, l_id3
5425 FROM mst_out_request_details
5426 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5427 AND exception_type = P_EXCEPTION_TYPE;
5428 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_703_1');
5429 IF l_id1 < 0 THEN
5430 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5431 ELSE
5432 fnd_message.set_token('TRIP_NUMBER', l_id1);
5433 END IF;
5434
5435 Open l_carrier_cur(l_id2);
5436 Fetch l_carrier_cur Into l_str2;
5437 If l_carrier_cur%NotFound Then
5438 l_str2 := '';
5439 End If;
5440 Close l_carrier_cur;
5441 fnd_message.set_token('FREIGHT_CODE', l_str2);
5442
5443 Open l_fac_desc_cur (l_id3);
5444 Fetch l_fac_desc_cur Into l_str1;
5445 If l_fac_desc_cur%NotFound Then
5446 l_str1 := '';
5447 End If;
5448 Close l_fac_desc_cur;
5449 fnd_message.set_token('FACILITY_DESC', l_str1);
5450 l_message := fnd_message.get;
5451 ELSIF P_EXCEPTION_TYPE = 705 THEN --Facility Item Incompatibility
5452 IF P_LINE_NUM = 1 THEN
5453 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_705_1');
5454 SELECT number1, number2, number3
5455 INTO l_id1, l_id2, l_id3
5456 FROM mst_out_request_details
5457 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5458 AND exception_type = P_EXCEPTION_TYPE;
5459 IF l_id1 < 0 THEN
5460 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5461 ELSE
5462 fnd_message.set_token('TRIP_NUMBER', l_id1);
5463 END IF;
5464
5465 -- item_description
5466 select msitl.description into l_str1
5467 from mtl_system_items_tl msitl, mst_delivery_details mdd
5468 where mdd.plan_id = P_PLAN_ID
5469 and mdd.delivery_detail_id = l_id2
5470 and mdd.inventory_item_id = msitl.inventory_item_id
5471 and mdd.organization_id = msitl.organization_id
5472 and msitl.language = userenv('LANG');
5473 fnd_message.set_token('ITEM_DESCRIPTION', l_str1);
5474
5475 -- Facility_Description
5476 select flp.description
5477 into l_str2
5478 from fte_location_parameters flp
5479 where flp.location_id = l_id3;
5480 fnd_message.set_token('FACILITY_DESCRIPTION', l_str2);
5481 l_message := fnd_message.get;
5482 END IF;
5483 ELSIF P_EXCEPTION_TYPE = 706 THEN --Facility Mode Incompatibility
5484 IF P_LINE_NUM = 1 THEN
5485 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_706_1');
5486 SELECT number1, number3, char1
5487 INTO l_id1, l_id3, l_str1
5488 FROM mst_out_request_details
5489 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5490 AND exception_type = P_EXCEPTION_TYPE;
5491 IF l_id1 < 0 THEN
5492 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5493 ELSE
5494 fnd_message.set_token('TRIP_NUMBER', l_id1);
5495 END IF;
5496
5497 SELECT wlk.meaning
5498 INTO l_str2
5499 FROM wsh_lookups wlk
5500 WHERE wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
5501 and wlk.lookup_code = l_str1;
5502
5503 fnd_message.set_token('MODE_OF_TRANSPORT', l_str2);
5504
5505 -- Facility_Description
5506 select flp.description
5507 into l_str3
5508 from fte_location_parameters flp
5509 where flp.location_id = l_id3;
5510 fnd_message.set_token('FACILITY_DESCRIPTION', l_str3);
5511 l_message := fnd_message.get;
5512 END IF;
5513 ELSIF P_EXCEPTION_TYPE = 707 THEN --Facility Facility Incompatibility
5514 IF P_LINE_NUM = 1 THEN
5515 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_707_1');
5516
5517 SELECT number1, number2, number3, number4
5518 INTO l_id1, l_id2, l_id3, l_id4
5519 FROM mst_out_request_details
5520 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5521 AND exception_type = P_EXCEPTION_TYPE;
5522 fnd_message.set_token('DELIVERY_NUMBER', l_id1);
5523
5524 --End_facility_name, intermediate_facility_name
5525 select flp1.facility_code
5526 into l_str1
5527 from fte_location_parameters flp1
5528 where flp1.location_id = l_id2;
5529
5530 fnd_message.set_token('END_FACILITY_NAME', l_str1);
5531 select flp1.facility_code
5532 into l_str2
5533 from fte_location_parameters flp1
5534 where flp1.location_id = l_id3;
5535 fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_str2);
5536 l_message := fnd_message.get;
5537 END IF;
5538 ELSIF P_EXCEPTION_TYPE = 800 THEN
5539 SELECT number1, number2, number3, number4, number5
5540 INTO l_id1, l_id2, l_id3, l_id4, l_id5
5541 FROM mst_out_request_details
5542 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5543 AND exception_type = P_EXCEPTION_TYPE;
5544 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
5545 IF l_id1 < 0 THEN
5546 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5547 ELSE
5548 fnd_message.set_token('TRIP_NUMBER', l_id1);
5549 END IF;
5550
5551 Open l_carrier_cur(l_id2);
5552 Fetch l_carrier_cur Into l_str2;
5553 If l_carrier_cur%NotFound Then
5554 l_str2 := '';
5555 End If;
5556 Close l_carrier_cur;
5557 fnd_message.set_token('FREIGHT_CODE', l_str2);
5558
5559 Open l_fac_desc_cur (l_id3);
5560 Fetch l_fac_desc_cur Into l_str1;
5561 If l_fac_desc_cur%NotFound Then
5562 l_str1 := '';
5563 End If;
5564 Close l_fac_desc_cur;
5565 fnd_message.set_token('FACILITY_DESC', l_str1);
5566
5567 If l_id4 Is Not Null Then
5568 l_str2 := Get_Partner_Name(l_id4, 1);
5569 Elsif l_id5 Is Not Null Then
5570 l_str2 := Get_Partner_Name(l_id5, 2);
5571 End If;
5572
5573 fnd_message.set_token('TRADING_PARTNER', l_str2);
5574 l_message := fnd_message.get;
5575 ELSIF P_EXCEPTION_TYPE = 801 THEN
5576 SELECT number1, number2, number3, number4, number5, number6
5577 INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5578 FROM mst_out_request_details
5579 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5580 AND exception_type = P_EXCEPTION_TYPE;
5581 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
5582 IF l_id1 < 0 THEN
5583 fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5584 ELSE
5585 fnd_message.set_token('TRIP_NUMBER', l_id1);
5586 END IF;
5587
5588 Open l_carrier_cur(l_id2);
5589 Fetch l_carrier_cur Into l_str2;
5590 If l_carrier_cur%NotFound Then
5591 l_str2 := '';
5592 End If;
5593 Close l_carrier_cur;
5594 fnd_message.set_token('FREIGHT_CODE', l_str2);
5595
5596 Open l_fac_desc_cur (l_id3);
5597 Fetch l_fac_desc_cur Into l_str1;
5598 If l_fac_desc_cur%NotFound Then
5599 l_str1 := '';
5600 End If;
5601 Close l_fac_desc_cur;
5602 fnd_message.set_token('FACILITY_DESC', l_str1);
5603
5604 If l_id4 Is Not Null Then
5605 l_str2 := Get_Partner_Name(l_id4, 1);
5606 Elsif l_id5 Is Not Null Then
5607 l_str2 := Get_Partner_Name(l_id5, 2);
5608 Elsif l_id6 Is Not Null Then
5609 l_str2 := fnd_profile.value('MST_COMPANY_NAME');
5610 End If;
5611 fnd_message.set_token('CUST_SUPPLIER', l_str2);
5612 l_message := fnd_message.get;
5613 ELSIF P_EXCEPTION_TYPE = 1000 THEN
5614 SELECT number1
5615 INTO l_id1
5616 FROM mst_out_request_details
5617 WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5618 AND exception_type = P_EXCEPTION_TYPE;
5619 fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_1000_1');
5620 fnd_message.set_token('DELIVERY_NUMBER', l_id1);
5621 l_message := fnd_message.get;
5622 ELSE
5623 SELECT meaning
5624 INTO l_message
5625 FROM mfg_lookups
5626 WHERE lookup_type = 'MST_EXCEPTION_TYPE'
5627 AND lookup_code = P_EXCEPTION_TYPE;
5628 END IF;
5629 return l_message;
5630 EXCEPTION
5631 WHEN OTHERS THEN
5632 RETURN '';
5633 END GET_ACT_TOKENIZED_EXCEPTION;
5634
5635 FUNCTION adjust_to_server_time(p_datetime IN DATE,
5636 p_location_id IN NUMBER,
5637 p_facility_id IN NUMBER) RETURN DATE IS
5638
5639 CURSOR cur_location(P_facility_id IN NUMBER) IS
5640 SELECT flp.location_id
5641 FROM fte_location_parameters flp
5642 WHERE flp.facility_id = P_facility_id;
5643
5644 l_server_datetime date;
5645 l_location_id number;
5646 BEGIN
5647 IF p_location_id IS NULL THEN
5648 open cur_location(P_facility_id);
5649 fetch cur_location into l_location_id;
5650 close cur_location;
5651 ELSE
5652 l_location_id:= p_location_id;
5653 END IF;
5654 l_server_datetime:= MST_GEOCODING.Get_server_time(l_location_id, p_datetime);
5655 RETURN l_server_datetime;
5656 EXCEPTION
5657 WHEN OTHERS THEN
5658 RETURN p_datetime;
5659 END adjust_to_server_time;
5660
5661 FUNCTION convert_time(p_time IN NUMBER,
5662 p_uom_from IN VARCHAR2,
5663 p_uom_to IN VARCHAR2) RETURN NUMBER IS
5664
5665 l_factor_to_hour NUMBER;
5666 l_factor_from_hour NUMBER;
5667 BEGIN
5668
5669 -- factor to convert into hours
5670 IF p_uom_from = 'WK' THEN -- week
5671 l_factor_to_hour := 24*7;
5672 ELSIF p_uom_from = 'DAY' THEN
5673 l_factor_to_hour := 24;
5674 ELSIF p_uom_from = 'HR' THEN
5675 l_factor_to_hour := 1;
5676 ELSIF p_uom_from = 'MIN' THEN
5677 l_factor_to_hour := 1/60;
5678 ELSIF p_uom_from = 'SEC' THEN
5679 l_factor_to_hour := 1/(60*60);
5680 END IF;
5681
5682 -- factor to convert from hours
5683 IF p_uom_to = 'WK' THEN -- week
5684 l_factor_from_hour := 1/(24*7);
5685 ELSIF p_uom_to = 'DAY' THEN
5686 l_factor_from_hour := 1/24;
5687 ELSIF p_uom_to = 'HR' THEN
5688 l_factor_from_hour := 1;
5689 ELSIF p_uom_to = 'MIN' THEN
5690 l_factor_from_hour := 60;
5691 ELSIF p_uom_to = 'SEC' THEN
5692 l_factor_from_hour := 60*60;
5693 END IF;
5694
5695 RETURN p_time * l_factor_from_hour * l_factor_to_hour;
5696 EXCEPTION
5697 WHEN OTHERS THEN
5698 RETURN p_time;
5699 END convert_time;
5700
5701 /**************FOLLOWING ARE BEING USED FOR PURGE PLAN************************/
5702 procedure print_info(p_purge_debug_control in number, p_info_str in varchar2) is
5703 begin
5704 if p_purge_debug_control = 1 then
5705 fnd_file.put_line(fnd_file.log, p_info_str);
5706 --dbms_output.put_line(p_info_str);
5707 --abc123pro(p_info_str);
5708 end if;
5709 end print_info;
5710
5711 procedure purge_plan (p_err_code OUT NOCOPY VARCHAR2
5712 , p_err_buff OUT NOCOPY VARCHAR2
5713 , p_plan_id IN NUMBER
5714 , p_compile_designator IN VARCHAR2
5715 , p_description IN VARCHAR2) is
5716 l_message varchar2(1000);
5717 begin
5718 l_message := 'Purge of plan '||p_compile_designator||':'||p_description||' started by '||fnd_global.user_name;
5719 print_info(1,l_message);
5720
5721 delete from MST_DELIVERY_LEGS
5722 where plan_id = p_plan_id;
5723
5724 delete from MST_TRIP_STOPS
5725 where plan_id = p_plan_id;
5726
5727 delete from MST_TRIPS
5728 where plan_id = p_plan_id;
5729
5730 delete from MST_CM_TRIPS
5731 where plan_id = p_plan_id;
5732
5733 delete from MST_DELIVERY_ASSIGNMENTS
5734 where plan_id = p_plan_id;
5735
5736 delete from MST_DELIVERY_DETAILS
5737 where plan_id = p_plan_id;
5738
5739 delete from MST_DELIVERIES
5740 where plan_id = p_plan_id;
5741
5742 delete from MST_EXCEPTION_DETAILS
5743 where plan_id = p_plan_id;
5744
5745 delete from MST_EXCEPTIONS
5746 where plan_id = p_plan_id;
5747
5748 delete from MST_FILES
5749 where plan_id = p_plan_id;
5750
5751 delete from MST_IN_REQUESTS
5752 where plan_id = p_plan_id;
5753
5754 delete from MST_LOAD_SUMMARY
5755 where plan_id = p_plan_id;
5756
5757 delete from MST_OUT_REQUEST_DETAILS
5758 where plan_id = p_plan_id;
5759
5760 delete from MST_OUT_REQUESTS
5761 where plan_id = p_plan_id;
5762
5763 delete from MST_PERSONAL_QUERY_RESULTS
5764 where plan_id = p_plan_id;
5765
5766 delete from MST_PLAN_CONSTRAINT_RULES
5767 where plan_id = p_plan_id;
5768
5769 delete from MST_PLAN_FACILITIES
5770 where plan_id = p_plan_id;
5771
5772 delete from MST_PLAN_PENALTY_BREAKS
5773 where plan_id = p_plan_id;
5774
5775 delete from MST_PLAN_ZIP_LOCATIONS
5776 where plan_id = p_plan_id;
5777
5778 delete from MST_RELATED_LOADS_TEMP
5779 where plan_id = p_plan_id;
5780
5781 delete from MST_RELEASE_TEMP
5782 where plan_id = p_plan_id;
5783
5784 delete from MST_SNAPSHOT_TASKS
5785 where plan_id = p_plan_id;
5786
5787 delete from MST_TMP_PLN_LOC
5788 where plan_id = p_plan_id;
5789
5790 delete from MST_EXCEPT_DETAILS_DETAILS
5791 where plan_id = p_plan_id;
5792
5793 delete from MST_PLANS
5794 where plan_id = p_plan_id;
5795
5796 commit;
5797 l_message := 'Purge of plan '||p_compile_designator||':'||p_description||' successfully completed.';
5798 print_info(1,l_message);
5799 exception
5800 when others then
5801 rollback;
5802 l_message := 'Purge of plan '||p_compile_designator||':'||p_description||' unsuccessfully completed.';
5803 print_info(1,l_message);
5804 l_message := to_char(SQLCODE)||':'||SQLERRM;
5805 print_info(1,l_message);
5806 end purge_plan;
5807
5808 procedure submit_purge_plan_request ( p_request_id OUT NOCOPY NUMBER
5809 , p_plan_id IN NUMBER
5810 , p_compile_designator IN VARCHAR2
5811 , p_description IN VARCHAR2) is
5812 begin
5813 p_request_id := fnd_request.submit_request('MST', 'MSTPRGPL', NULL, NULL, NULL, p_plan_id, p_compile_designator, p_description);
5814 if p_request_id > 0 then
5815 commit;
5816 end if;
5817 end submit_purge_plan_request;
5818
5819 /******************************************************************************/
5820
5821 function get_org_id(p_plan_id in number, p_delivery_id in number) return number is
5822 cursor cur_orgs is
5823 select mdd.organization_id
5824 from mst_deliveries md
5825 , mst_delivery_assignments mda
5826 , mst_delivery_details mdd
5827 where mdd.plan_id = mda.plan_id
5828 and mdd.delivery_detail_id = mda.delivery_detail_id
5829 and mda.plan_id = md.plan_id
5830 and mda.delivery_id = md.delivery_id
5831 and md.plan_id = p_plan_id
5832 and md.delivery_id = p_delivery_id;
5833
5834 l_org_id number;
5835 begin
5836 open cur_orgs;
5837 fetch cur_orgs into l_org_id;
5838 close cur_orgs;
5839
5840 return l_org_id;
5841 exception
5842 when others then
5843 return 0;
5844 end get_org_id;
5845
5846 function get_workflow_status(p_plan_id in number, p_exception_detail_id in number) return varchar2 is
5847 l_image_name varchar2(20);
5848
5849 cursor cur_check_notification (l_plan_id in number, l_exception_detail_id in number)
5850 is
5851 select '1'
5852 from wf_item_activity_statuses
5853 where item_type ='MSTEXPWF'
5854 and item_key = l_plan_id || '-' || l_exception_detail_id
5855 and notification_id is not null;
5856
5857 begin
5858 open cur_check_notification (p_plan_id, p_exception_detail_id);
5859 fetch cur_check_notification into l_image_name;
5860 if cur_check_notification%notfound then
5861 l_image_name := 'jtfgnull.gif';
5862 else
5863 l_image_name := 'jtfuwnac.gif';
5864 end if;
5865 close cur_check_notification;
5866 return l_image_name;
5867 exception
5868 when others then
5869 if cur_check_notification%isopen then
5870 close cur_check_notification;
5871 end if;
5872 return 'jtfgnull.gif';
5873 end get_workflow_status;
5874
5875 function get_city_code(p_location_id NUMBER) return VARCHAR2 IS
5876 Cursor city_code is
5877 select nvl(wr.city_code, wr.city) from
5878 wsh_locations wsh,
5879 wsh_region_locations wlr,
5880 wsh_regions_v wr
5881 where wsh.wsh_location_id = p_location_id
5882 and wsh.wsh_location_id = wlr.location_id
5883 and wlr.region_type = 2 -- city
5884 and wlr.region_id = wr.region_id;
5885
5886 CURSOR wsh_city_name IS
5887 SELECT substr(wl.city , 1, 10)
5888 FROM wsh_locations wl
5889 WHERE wsh_location_id = p_location_id;
5890
5891 l_city_code VARCHAR2(60);
5892 BEGIN
5893 open city_code;
5894 fetch city_code into l_city_code;
5895 close city_code;
5896
5897 IF l_city_code IS NULL THEN
5898 open wsh_city_name;
5899 fetch wsh_city_name into l_city_code;
5900 close wsh_city_name;
5901 END IF;
5902
5903 return l_city_code;
5904 END get_city_code;
5905
5906 procedure Compute_Exception_Counts(p_Plan_Id IN NUMBER, p_Exp_Summary_Where_Clause IN VARCHAR2, p_Exp_Details_Where_Clause IN VARCHAR2) is
5907 --pragma autonomous_transaction;
5908
5909 cursor Cur_Exp_Details (l_Plan_Id IN NUMBER, l_Dummy IN NUMBER)
5910 is
5911 select exception_type
5912 from mst_exceptions
5913 where plan_id = l_plan_id
5914 and exception_count_context = l_dummy;
5915
5916 type number_tab_type is table of number index by binary_integer;
5917 l_Exception_Type_Tab number_tab_type;
5918 l_Count_Tab number_tab_type;
5919 begin
5920 execute immediate 'update mst_exceptions
5921 set exception_count_context = -9999
5922 where '||p_Exp_Summary_Where_Clause;
5923
5924 open Cur_Exp_Details (p_Plan_Id, -9999);
5925 fetch Cur_Exp_Details bulk collect into l_Exception_Type_Tab;
5926 close Cur_Exp_Details;
5927
5928 if nvl(l_Exception_Type_Tab(1),0) > 0 then
5929 for i in 1..l_Exception_Type_Tab.count loop
5930 execute immediate 'select count(1)
5931 from mst_exception_details
5932 where '||replace(p_Exp_Details_Where_Clause,'!~!',l_Exception_Type_Tab(i))
5933 into l_Count_Tab(i);
5934 end loop;
5935 forall i in 1..l_Exception_Type_Tab.last
5936 update mst_exceptions
5937 set exception_count_context = l_count_Tab(i)
5938 where Plan_id = p_Plan_Id
5939 and exception_type = l_Exception_Type_Tab(i);
5940 commit;
5941 -- else
5942 -- rollback;
5943 end if;
5944 exception
5945 when others then
5946 -- rollback;
5947 null;
5948 end Compute_Exception_Counts;
5949
5950 PROCEDURE run_dynamic_sql(p_query_string IN VARCHAR2) IS
5951 BEGIN
5952 EXECUTE IMMEDIATE p_query_string;
5953 END;
5954
5955 PROCEDURE notify_engine(p_plan_id IN NUMBER,
5956 p_object_type IN NUMBER,
5957 p_object_id IN NUMBER,
5958 p_firm_status IN NUMBER) IS
5959 l_status NUMBER;
5960 BEGIN
5961 --g_plan_id := p_plan_id;
5962 IF p_object_type = 1 THEN -- delivery
5963 DBMS_PIPE.PACK_MESSAGE('D|' || p_object_id || '|' || p_firm_status);
5964 ELSIF p_object_type = 2 THEN -- trip
5965 DBMS_PIPE.PACK_MESSAGE('T|' || p_object_id || '|' || p_firm_status);
5966 ELSIF p_object_type = 3 THEN -- cm trip
5967 DBMS_PIPE.PACK_MESSAGE('C|' || p_object_id || '|' || p_firm_status);
5968 END IF;
5969 l_status := DBMS_PIPE.SEND_MESSAGE('MST_IP_' || p_plan_id, 0);
5970 END notify_engine;
5971
5972 PROCEDURE Update_Del_And_Rel_Trips(p_Plan_Id IN NUMBER,
5973 p_Trip_Id IN NUMBER,
5974 p_Planned_Flag IN NUMBER,
5975 P_Notified OUT NOCOPY NUMBER) IS
5976
5977 CURSOR cur_affected_deliveries(p_plan_id IN NUMBER,
5978 p_trip_id IN NUMBER) IS
5979 SELECT DELIVERY_ID, PLANNED_FLAG, KNOWN_TE_FIRM_STATUS, PRESERVE_GROUPING_FLAG
5980 FROM MST_DELIVERIES md
5981 WHERE md.plan_id = p_plan_id
5982 AND md.DELIVERY_ID IN (SELECT mdl.DELIVERY_ID
5983 FROM MST_DELIVERY_LEGS mdl
5984 WHERE mdl.PLAN_ID = md.Plan_Id
5985 AND mdl.TRIP_ID = p_Trip_Id)
5986 FOR UPDATE OF PLANNED_FLAG NOWAIT;
5987
5988 CURSOR cur_affected_trips(p_plan_id IN NUMBER,
5989 p_trip_id IN NUMBER) IS
5990 SELECT TRIP_ID, PLANNED_FLAG
5991 FROM mst_trips mt
5992 WHERE mt.PLAN_ID = p_Plan_Id
5993 AND mt.TRIP_ID IN (SELECT mdl2.TRIP_ID
5994 FROM MST_DELIVERY_LEGS mdl1
5995 , MST_DELIVERY_LEGS mdl2
5996 WHERE mdl1.PLAN_ID = mdl2.PLAN_ID
5997 AND mdl1.DELIVERY_ID = mdl2.DELIVERY_ID
5998 AND mdl1.TRIP_ID <> mdl2.TRIP_ID
5999 AND mdl1.PLAN_ID = mt.Plan_Id
6000 AND mdl1.TRIP_ID = p_Trip_Id);
6001
6002 l_rec_affected_deliveries cur_affected_deliveries%ROWTYPE;
6003 l_rec_affected_trips cur_affected_trips%ROWTYPE;
6004 l_update_stmt VARCHAR2(500);
6005 l_planned_flag NUMBER;
6006 BEGIN
6007 IF p_Planned_Flag IN (1,3) THEN
6008 OPEN cur_affected_deliveries(p_plan_id, p_trip_id);
6009 LOOP
6010 l_planned_flag := NULL;
6011 FETCH cur_affected_deliveries INTO l_rec_affected_deliveries;
6012 EXIT WHEN cur_affected_deliveries%NOTFOUND;
6013
6014 l_update_stmt := 'UPDATE MST_DELIVERIES '||
6015 ' SET PLANNED_FLAG = decode(:p_Planned_Flag,1,1,3,2,PLANNED_FLAG) '||
6016 ' , PRESERVE_GROUPING_FLAG = DECODE(:p_Planned_Flag, '||
6017 ' 3, DECODE(KNOWN_TE_FIRM_STATUS, '||
6018 ' 2 , 1, '||
6019 ' PRESERVE_GROUPING_FLAG), '||
6020 ' PRESERVE_GROUPING_FLAG) '||
6021 ' WHERE plan_id = :p_plan_id '||
6022 ' and delivery_id = :p_delivery_id '||
6023 ' RETURNING planned_flag into :l_planned_flag';
6024
6025 EXECUTE IMMEDIATE l_update_stmt
6026 USING p_planned_flag, p_planned_flag,
6027 p_plan_id, l_rec_affected_deliveries.delivery_id
6028 RETURNING INTO l_planned_flag;
6029 IF l_rec_affected_deliveries.planned_flag <> l_planned_flag THEN
6030 notify_engine(p_plan_id, 1, l_rec_affected_deliveries.delivery_id, l_planned_flag);
6031 P_Notified := 1;
6032 END IF;
6033
6034 --dbms_output.put_line('planned_flag changed from ' ||l_rec_affected_deliveries.planned_flag ||
6035 -- ' to ' ||l_planned_flag);
6036 END LOOP;
6037 CLOSE cur_affected_deliveries;
6038
6039 OPEN cur_affected_trips(p_plan_id, p_trip_id);
6040 LOOP
6041 l_planned_flag := NULL;
6042 FETCH cur_affected_trips INTO l_rec_affected_trips;
6043 EXIT WHEN cur_affected_trips%NOTFOUND;
6044 l_update_stmt := ' UPDATE MST_TRIPS '||
6045 ' SET PLANNED_FLAG = DECODE(:p_Planned_Flag, '||
6046 ' 1, DECODE(SIGN(PLANNED_FLAG-2), '||
6047 ' -1, PLANNED_FLAG, 2), '||
6048 ' 3,DECODE(SIGN(PLANNED_FLAG-2), '||
6049 ' -1, 2, PLANNED_FLAG), '||
6050 ' PLANNED_FLAG) '||
6051 ' WHERE PLAN_ID = :p_Plan_Id '||
6052 ' AND TRIP_ID = :p_trip_id '||
6053 ' RETURNING planned_flag into :l_planned_flag';
6054
6055 EXECUTE IMMEDIATE l_update_stmt
6056 USING p_planned_flag, p_plan_id, l_rec_affected_trips.trip_id
6057 RETURNING INTO l_planned_flag;
6058
6059 IF l_rec_affected_trips.planned_flag <> l_planned_flag THEN
6060 notify_engine(p_plan_id, 2, l_rec_affected_trips.trip_id, l_planned_flag);
6061 P_Notified := 1;
6062 END IF;
6063 --dbms_output.put_line('planned_flag changed from ' ||l_rec_affected_trips.planned_flag ||
6064 -- ' to ' ||l_planned_flag);
6065 END LOOP;
6066 CLOSE cur_affected_trips;
6067 END IF;
6068 EXCEPTION
6069 WHEN OTHERS THEN
6070 IF cur_affected_deliveries%ISOPEN THEN
6071 CLOSE cur_affected_deliveries;
6072 END IF;
6073 IF cur_affected_trips%ISOPEN THEN
6074 CLOSE cur_affected_trips;
6075 END IF;
6076 RAISE;
6077 END Update_Del_And_Rel_Trips;
6078
6079 PROCEDURE Update_Trips_Of_CM(p_Plan_Id IN NUMBER,
6080 p_Continuous_Move_Id IN NUMBER,
6081 P_Notified OUT NOCOPY NUMBER) IS
6082
6083 CURSOR cur_affected_trips(p_plan_id IN NUMBER,
6084 p_Continuous_Move_Id IN NUMBER) IS
6085 SELECT TRIP_ID, PLANNED_FLAG
6086 FROM mst_trips mt
6087 WHERE plan_id = p_plan_id
6088 AND Continuous_Move_Id = p_Continuous_Move_Id
6089 FOR UPDATE OF PLANNED_FLAG NOWAIT;
6090
6091 l_rec_affected_trips cur_affected_trips%ROWTYPE;
6092 l_update_stmt VARCHAR2(500);
6093 l_planned_flag NUMBER;
6094 BEGIN
6095 OPEN cur_affected_trips(p_plan_id, p_Continuous_Move_Id);
6096 LOOP
6097 l_planned_flag := NULL;
6098 FETCH cur_affected_trips INTO l_rec_affected_trips;
6099 EXIT WHEN cur_affected_trips%NOTFOUND;
6100 l_update_stmt := ' UPDATE MST_TRIPS '||
6101 ' SET PLANNED_FLAG = DECODE(SIGN(PLANNED_FLAG-2),-1,PLANNED_FLAG,2) '||
6102 ' WHERE PLAN_ID = :p_Plan_Id '||
6103 ' AND TRIP_ID = :p_trip_id '||
6104 ' RETURNING planned_flag into :l_planned_flag';
6105 EXECUTE IMMEDIATE l_update_stmt
6106 USING p_plan_id, l_rec_affected_trips.trip_id
6107 RETURNING INTO l_planned_flag;
6108 IF l_rec_affected_trips.planned_flag <> l_planned_flag THEN
6109 notify_engine(p_plan_id, 2, l_rec_affected_trips.trip_id, l_planned_flag);
6110 P_Notified := 1;
6111 END IF;
6112 --dbms_output.put_line('planned_flag changed from ' ||l_rec_affected_trips.planned_flag ||
6113 -- ' to ' ||l_planned_flag);
6114 END LOOP;
6115 CLOSE cur_affected_trips;
6116 EXCEPTION
6117 WHEN OTHERS THEN
6118 IF cur_affected_trips%ISOPEN THEN
6119 CLOSE cur_affected_trips;
6120 END IF;
6121 RAISE;
6122 END Update_Trips_Of_CM;
6123
6124 FUNCTION GET_UOM_CONVERSION_RATE (p_from_uom_code VARCHAR2, p_to_uom_code VARCHAR2, p_org_id NUMBER, p_inventory_item_id NUMBER)
6125 RETURN NUMBER IS
6126 l_from_uom_class VARCHAR2(80);
6127 l_to_uom_class VARCHAR2(80);
6128 l_conversion_rate NUMBER := 1;
6129
6130 CURSOR l_uom_class (p_uom_code VARCHAR2) IS
6131 SELECT uom_class
6132 FROM mtl_units_of_measure
6133 WHERE uom_code = p_uom_code;
6134
6135 CURSOR l_intraclass_conversion_cur (p_from_uom_code VARCHAR2, p_to_uom_code VARCHAR2) IS
6136 SELECT muc2.conversion_rate/muc1.conversion_rate
6137 FROM mtl_uom_conversions muc1,
6138 mtl_uom_conversions muc2
6139 WHERE muc1.inventory_item_id = 0
6140 AND muc2.inventory_item_id = 0
6141 AND muc1.uom_class = l_to_uom_class
6142 AND muc1.uom_code = p_to_uom_code
6143 AND muc2.uom_class = l_from_uom_class
6144 AND muc2.uom_code = p_from_uom_code;
6145
6146 CURSOR l_interclass_conversion_cur (p_uom1 VARCHAR2, p_uom2 VARCHAR2, p_org_id NUMBER, p_inventory_item_id NUMBER) IS
6147 SELECT muc.conversion_rate
6148 FROM mtl_uom_conversions_view muc
6149 WHERE muc.inventory_item_id = p_inventory_item_id
6150 AND muc.organization_id = p_org_id
6151 AND muc.primary_uom_code = p_uom1
6152 AND muc.uom_code = p_uom2;
6153
6154 BEGIN
6155 OPEN l_uom_class (p_from_uom_code);
6156 FETCH l_uom_class INTO l_from_uom_class;
6157 IF l_uom_class%NOTFOUND THEN
6158 RAISE NO_DATA_FOUND;
6159 END IF;
6160 CLOSE l_uom_class;
6161
6162 OPEN l_uom_class (p_to_uom_code);
6163 FETCH l_uom_class INTO l_to_uom_class;
6164 IF l_uom_class%NOTFOUND THEN
6165 RAISE NO_DATA_FOUND;
6166 END IF;
6167 CLOSE l_uom_class;
6168
6169 IF l_from_uom_class = l_to_uom_class THEN
6170 Open l_intraclass_conversion_cur(p_from_uom_code, p_to_uom_code);
6171 Fetch l_intraclass_conversion_cur INTO l_conversion_rate;
6172 Close l_intraclass_conversion_cur;
6173 ELSE
6174 Open l_interclass_conversion_cur(p_from_uom_code, p_to_uom_code, p_org_id, p_inventory_item_id);
6175 Fetch l_interclass_conversion_cur INTO l_conversion_rate;
6176 If l_interclass_conversion_cur%notfound Then
6177 Close l_interclass_conversion_cur;
6178 Open l_interclass_conversion_cur(p_to_uom_code, p_from_uom_code, p_org_id, p_inventory_item_id);
6179 Fetch l_interclass_conversion_cur INTO l_conversion_rate;
6180 If l_interclass_conversion_cur%found Then
6181 If l_conversion_rate = 0 Then
6182 RAISE NO_DATA_FOUND;
6183 Else
6184 l_conversion_rate := 1/l_conversion_rate;
6185 End If;
6186 End If;
6187 END IF;
6188 Close l_interclass_conversion_cur;
6189 END IF;
6190
6191 RETURN l_conversion_rate;
6192
6193 EXCEPTION
6194 WHEN OTHERS THEN
6195 IF l_uom_class%ISOPEN THEN
6196 CLOSE l_uom_class;
6197 END IF;
6198 IF l_intraclass_conversion_cur%ISOPEN THEN
6199 CLOSE l_intraclass_conversion_cur;
6200 END IF;
6201 IF l_interclass_conversion_cur%ISOPEN THEN
6202 CLOSE l_interclass_conversion_cur;
6203 END IF;
6204 RETURN l_conversion_rate;
6205 END GET_UOM_CONVERSION_RATE;
6206
6207 FUNCTION get_rule_type ( p_rule_id IN NUMBER )
6208 RETURN VARCHAR2 IS
6209 CURSOR rules IS
6210 SELECT 'x'
6211 FROM fte_sel_rule_restrictions fsrr
6212 WHERE fsrr.rule_id = p_rule_id
6213 AND fsrr.attribute_name = 'SPEND';
6214
6215 l_return VARCHAR2 (1);
6216 BEGIN
6217 OPEN rules;
6218 FETCH rules into l_return;
6219 CLOSE rules;
6220
6221 IF l_return IS NULL
6222 THEN
6223 RETURN 'NOT_SPEND';
6224 ELSE
6225 RETURN 'SPEND';
6226 END IF;
6227
6228 EXCEPTION
6229 WHEN OTHERS THEN
6230 IF rules%ISOPEN
6231 THEN
6232 CLOSE rules;
6233 END IF;
6234
6235 RETURN ( 'NOT_SPEND' );
6236 END get_rule_type;
6237
6238 FUNCTION get_row_count (p_view_name IN VARCHAR2, p_where_clause IN VARCHAR2)
6239 RETURN NUMBER IS
6240 l_count NUMBER;
6241 BEGIN
6242 EXECUTE IMMEDIATE ' SELECT COUNT(1) FROM ' || p_view_name || ' WHERE ' || p_where_clause
6243 INTO l_count;
6244 RETURN l_count;
6245 EXCEPTION
6246 WHEN OTHERS THEN
6247 RETURN 0;
6248 END get_row_count;
6249
6250
6251 END MST_WB_UTIL;