1 PACKAGE BODY MST_AUDIT_REP_EXCP AS
2 /* $Header: MSTEAREB.pls 115.27 2004/05/05 21:40:19 jansanch noship $ */
3
4 --global variables
5 distScaleFactor NUMBER;
6 avgDrvSpeed NUMBER;
7 tp_time_uom VARCHAR2(3);
8 tp_distance_uom VARCHAR2(3);
9 tp_dimension_uom VARCHAR2(3);
10 tp_volume_uom VARCHAR2(3);
11 user_id NUMBER(15);
12 tp_plan_id NUMBER;
13 msg_seq_num NUMBER;
14
15 PROCEDURE MissingLatLongCoordExcptn (plan_idIn NUMBER, userIdIn NUMBER) IS
16 excptnId NUMBER;
17 dummyThreshold NUMBER;
18 BEGIN
19 -- Delete previous occurrences of this exception in mst_exceptions and the details table
20 DELETE FROM mst_exception_details
21 WHERE plan_id = plan_idIn
22 AND exception_type = 704;
23
24 DELETE FROM mst_exceptions
25 WHERE plan_id = plan_idIn
26 AND exception_type = 704;
27
28 --check if exception appears in mst_excep_preferences (it does not have a threshold but
29 --if it is enabled there should be an entry in that table with a dummy value for threshold).
30 dummyThreshold := getExceptionThreshold(704,userIdIn);
31 --if no threshold found it means exception is disabled, so exit procedure
32 IF dummyThreshold = -999 THEN
33 debug_output('exception calculation disabled');
34 RETURN;
35 END IF;
36
37 --Create fresh entry for exception and keep exception_id for updates in details table
38 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
39 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
40 CREATION_DATE)
41 VALUES (mst_exceptions_s.nextval, plan_idIn, 700, 704, 0, userIdIn,
42 userIdIn,-1,sysdate, sysdate);
43
44 SELECT exception_id INTO excptnId
45 FROM mst_exceptions
46 WHERE plan_id = plan_idIn
47 AND exception_type = 704;
48
49 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
50 location_id,
51 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
52 CREATION_DATE, STATUS)
53 SELECT mst_exception_details_s.nextval, excptnId , plan_idIn, 704,
54 location_id,
55 userIdIn, userIdIn,-1,sysdate, sysdate, 3
56 FROM (SELECT distinct wsh_location_id as location_id
57 FROM WSH_LOCATIONS loc,
58 MST_DELIVERY_DETAILS det
59 WHERE det.plan_id = plan_idIn
60 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
61 AND ( det.ship_from_location_id = loc.wsh_location_id
62 OR det.ship_to_location_id = loc.wsh_location_id)
63 AND ( latitude is null OR longitude is null)
64 UNION
65 SELECT distinct wsh_location_id as location_id
66 FROM FTE_LOCATION_PARAMETERS param,
67 WSH_LOCATIONS loc
68 WHERE loc.wsh_location_id = param.location_id
69 AND ( param.consolidation_allowed = 'Y'
70 OR param.deconsolidation_allowed = 'Y'
71 OR param.crossdocking_allowed = 'Y')
72 AND ( loc.latitude is null OR loc.longitude is null));
73
74 --update count of this exception in mst_exceptions
75 UPDATE mst_exceptions
76 SET EXCEPTION_COUNT = ( select count(*)
77 from mst_exception_details det
78 where det.exception_id = excptnId)
79 WHERE EXCEPTION_id = excptnId;
80
81 --in case no exception of this type was generate delete entry in mst_exceptions table
82 DELETE FROM mst_exceptions
83 WHERE exception_count = 0 and exception_id = excptnId;
84
85 COMMIT;
86 END MissingLatLongCoordExcptn;
87
88
89
90
91 PROCEDURE MissingDistanceDataExcptn (plan_idIn NUMBER, userIdIn NUMBER) IS
92 excptnId NUMBER;
93 dummyThreshold NUMBER;
94 distanceEnginePresent VARCHAR(10);
95 BEGIN
96 -- Delete previous occurrences of this exception in mst_exceptions and the details table
97 DELETE FROM mst_exception_details
98 WHERE plan_id = plan_idIn
99 AND exception_type = 900;
100
101 DELETE FROM mst_exceptions
102 WHERE plan_id = plan_idIn
103 AND exception_type = 900;
104
105 --Determine if distance engine is present (if it is not then no exception of this type should be generated)
106 distanceEnginePresent := fnd_profile.value('MST_MILEAGE_ENGINE_AVAIL');
107 IF distanceEnginePresent <> 'Y' THEN
108 debug_output('No mileage engine present, no check for this exception. MST_MILEAGE_ENGINE_AVAIL=' || distanceEnginePresent);
109 RETURN;
110 END IF;
111
112
113 --check if exception appears in mst_excep_preferences (it does not have a threshold but
114 --if it is enabled there should be an entry in that table with a dummy value for threshold).
115 dummyThreshold := getExceptionThreshold(900,userIdIn);
116 --if no threshold found it means exception is disabled, so exit procedure
117 IF dummyThreshold = -999 THEN
118 debug_output('exception calculation disabled');
119 RETURN;
120 END IF;
121
122 --Create fresh entry for exception and keep exception_id for updates in details table
123 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
124 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
125 CREATION_DATE)
126 VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 900, 0, userIdIn,
127 userIdIn,-1,sysdate, sysdate);
128
129 SELECT exception_id INTO excptnId
130 FROM mst_exceptions
131 WHERE plan_id = plan_idIn
132 AND exception_type = 900;
133
134
135 --find ship_from or ship_to locations whose zip-code does not appear in the distance table
136 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
137 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
138 CREATION_DATE, STATUS,
139 delivery_detail_id, delivery_id)
140 SELECT mst_exception_details_s.nextval, excptnId , plan_idIn, 900,
141 userIdIn, userIdIn,-1,sysdate, sysdate,3,
142 delivery_detail_id, delivery_id
143 FROM ( SELECT DISTINCT det.delivery_detail_id AS delivery_detail_id, da.delivery_id AS delivery_id
144 FROM MST_DELIVERY_DETAILS det,
145 MST_DELIVERY_ASSIGNMENTS da,
146 WSH_REGION_LOCATIONS origReg,
147 WSH_REGION_LOCATIONS destReg
148 WHERE det.PLAN_ID = plan_idIn
149 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
150 AND det.delivery_detail_id = da.delivery_detail_id (+)
151 AND da.parent_delivery_detail_id is null
152 AND origReg.location_id = det.ship_from_location_id
153 AND destReg.location_id = det.ship_to_location_id
154
155 --FTE_LOCATION_MILEAGES is distance table
156 --key is <origin_id, destination_id,IDENTIFIER_TYPE>. Origin and destination are FK to WSH_REGIONS.
157 --IDENTIFIER_TYPE can be city or state (Anuj)
158 AND NOT EXISTS (SELECT DISTANCE
159 FROM FTE_LOCATION_MILEAGES
160 WHERE origReg.region_id = origin_id
161 AND destReg.region_id = destination_id));
162
163
164 --update count of this exception in mst_exceptions
165 UPDATE mst_exceptions
166 SET EXCEPTION_COUNT = ( select count(*)
167 from mst_exception_details det
168 where det.exception_id = excptnId)
169 WHERE EXCEPTION_id = excptnId;
170
171 --in case no exception of this type was generated delete entry in mst_exceptions table
172 DELETE FROM mst_exceptions
173 WHERE exception_count = 0 and exception_id = excptnId;
174
175 commit;
176 END MissingDistanceDataExcptn;
177
178
179
180 --Delivery lines with zero pieces, cube or weight
181 PROCEDURE DL_with_zero_values (plan_idIn NUMBER, userIdIn NUMBER) IS
182 excptnId NUMBER;
183 dummyThreshold NUMBER;
184 BEGIN
185 --Delete previous occurrences of this exception in mst_exceptions and the details table
186 DELETE FROM mst_exception_details
187 WHERE plan_id = plan_idIn
188 AND exception_type = 219;
189
190 DELETE FROM mst_exceptions
191 WHERE plan_id = plan_idIn
192 AND exception_type = 219;
193
194 --check if exception appears in mst_excep_preferences (it does not have a threshold but
195 --if it is enabled there should be an entry in that table with a dummy value for threshold).
196 dummyThreshold := getExceptionThreshold(219,userIdIn);
197 --if no threshold found it means exception is disabled, so exit procedure
198 IF dummyThreshold = -999 THEN
199 debug_output('exception calculation disabled');
200 RETURN;
201 END IF;
202
203 --Create fresh entry for exception and keep exception_id for updates in details table
204 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
205 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
206 CREATION_DATE)
207 VALUES (mst_exceptions_s.nextval, plan_idIn, 200, 219, 0, userIdIn,
208 userIdIn,-1,sysdate, sysdate);
209
210 SELECT exception_id INTO excptnId
211 FROM mst_exceptions
212 WHERE plan_id = plan_idIn
213 AND exception_type = 219;
214
215
216 --check highest level delivery lines for zero values in pieces, weight and volume
217 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
218 delivery_detail_id, delivery_id,
219 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
220 CREATION_DATE, STATUS)
221 SELECT mst_exception_details_s.nextval, excptnId , det.plan_id, 219,
222 det.delivery_detail_id, da.delivery_id,
223 userIdIn, userIdIn,-1,sysdate, sysdate,3
224 FROM MST_DELIVERY_DETAILS det,
225 MST_DELIVERY_ASSIGNMENTS da
226 WHERE det.plan_id = plan_idIn
227 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
228 AND det.delivery_detail_id = da.delivery_detail_id (+)
229 AND da.parent_delivery_detail_id is null
230 AND ( det.NET_WEIGHT = 0 OR det.NET_WEIGHT IS NULL
231 OR det.VOLUME = 0 OR det.VOLUME IS NULL
232 OR det.REQUESTED_QUANTITY = 0);
233
234
235 --check for all items associated to delivery lines to see if they have zero cube or weight
236 /*
237 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
238 delivery_detail_id, delivery_id,
239 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
240 CREATION_DATE,STATUS)
241 SELECT mst_exception_details_s.nextval, excptnId , det.plan_id,219,
242 det.delivery_detail_id, da.delivery_id,
243 userIdIn, userIdIn,-1,sysdate, sysdate,3
244 FROM MST_DELIVERY_DETAILS det,
245 MTL_SYSTEM_ITEMS it,
246 MST_DELIVERY_ASSIGNMENTS da
247 WHERE det.plan_id = plan_idIn
248 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
249 AND det.delivery_detail_id = da.delivery_detail_id (+)
250 AND it.inventory_item_id = det.inventory_item_id
251 AND it.organization_id = det.organization_id
252 AND ( it.unit_volume = 0 OR it.unit_volume IS NULL
253 OR it.unit_weight = 0 OR it.unit_weight IS NULL);
254 */
255
256 --update count of this exception in mst_exceptions
257 UPDATE mst_exceptions
258 SET EXCEPTION_COUNT = ( select count(*)
259 from mst_exception_details det
260 where det.exception_id = excptnId)
261 WHERE EXCEPTION_id = excptnId;
262
263 --in case no exception of this type was generated delete entry in mst_exceptions table
264 DELETE FROM mst_exceptions
265 WHERE exception_count = 0 and exception_id = excptnId;
266
267 commit;
268 END DL_with_zero_values;
269
270
271 PROCEDURE DimensionViolForPieceExcptn (plan_idIn NUMBER, userIdIn NUMBER) IS
272 excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
273 deliveryId NUMBER;
274 -- total NUMBER; -- used to count occurrences of vehicles with infinite length, height and width.
275 length NUMBER;
276 width NUMBER;
277 height NUMBER;
278 maxDimV NUMBER; --maximum dimension of (length, widht, height) of biggest vehicle
279 midDimV NUMBER; --volume (length * width * height) of the biggest vehicle
280 minDimV NUMBER; --minimum dimension of (length, widht, height) of biggest vehicle
281 maxDimP NUMBER; --maximum dimension of (length, widht, height) for the piece
282 midDimP NUMBER; --volume (length * width * height) for the piece
283 minDimP NUMBER; --minimum dimension of (length, widht, height) for the piece
284 vehicle_dimension_uom_code VARCHAR2(3); -- UOM of length, width and height of vehicle
285 p_conv_found BOOLEAN;
286 p_conv_rate NUMBER;
287 thresholdPct NUMBER;
288
289 --Allows access to the relevant pieces and their dimensions.
290 CURSOR c_pieces IS
291 SELECT det.delivery_detail_id,
292 IT.UNIT_LENGTH, IT.UNIT_WIDTH, IT.UNIT_HEIGHT, it.DIMENSION_UOM_CODE, it.inventory_item_id
293 FROM MST_DELIVERY_DETAILS DET,
294 MTL_SYSTEM_ITEMS IT
295 WHERE IT.INVENTORY_ITEM_ID = DET.INVENTORY_ITEM_ID
296 AND det.organization_id = it.organization_id
297 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
298 AND det.plan_id = plan_idIn;
299
300 BEGIN
301 --clean previous exceptions of this type
302 DELETE FROM mst_exception_details
303 WHERE plan_id = plan_idIn
304 AND exception_type = 902;
305
306 DELETE FROM mst_exceptions
307 WHERE plan_id = plan_idIn
308 AND exception_type = 902;
309
310 --get the threshold from mst_excep_preferences
311 thresholdPct := getExceptionThreshold(902,userIdIn);
312 debug_output('thresholdPct='||thresholdPct);
313 --if no threshold found it means exception is disabled, so exit procedure
314 IF thresholdPct = -999 THEN
315 debug_output('exception calculation disabled');
316 RETURN;
317 END IF;
318
319 --find dimensions of vehicle with biggest volume
320 BEGIN
321 SELECT
322 NVL(CONV_TO_UOM(vt.usable_length, si.dimension_uom_code, tp_dimension_uom, si.inventory_item_id),-99),
323 NVL(CONV_TO_UOM(vt.usable_width, si.dimension_uom_code, tp_dimension_uom, si.inventory_item_id),-99),
324 NVL(CONV_TO_UOM(vt.usable_height, si.dimension_uom_code, tp_dimension_uom, si.inventory_item_id),-99)
325 INTO length, width, height
326 FROM fte_vehicle_types vt,
327 mtl_system_items_b si
328 WHERE vt.inventory_item_id = si.inventory_item_id
329 AND vt.organization_id = si.organization_id
330 AND vt.usable_length is not null
331 AND vt.usable_width is not null
332 AND vt.usable_height is not null
333 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
334 FROM wsh_carriers carr, wsh_carrier_services serv,
335 wsh_carrier_vehicle_types carrVeh
336 WHERE carr.carrier_id = serv.carrier_id
337 AND serv.mode_of_transport like 'TRUCK'
338 AND carrVeh.carrier_id = carr.carrier_id)
339 AND NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0) =
340 (SELECT MAX(NVL(CONV_TO_UOM(si2.internal_volume, si2.volume_uom_code, tp_volume_uom, si2.inventory_item_id),0))
341 FROM fte_vehicle_types vt2,
342 mtl_system_items_b si2
343 WHERE vt2.inventory_item_id = si2.inventory_item_id
344 AND vt2.organization_id = si2.organization_id
345 AND vt2.usable_length is not null
346 AND vt2.usable_width is not null
347 AND vt2.usable_height is not null
348 AND vt2.vehicle_type_id IN (SELECT distinct vehicle_type_id
349 FROM wsh_carriers carr, wsh_carrier_services serv,
350 wsh_carrier_vehicle_types carrVeh
351 WHERE carr.carrier_id = serv.carrier_id
352 AND serv.mode_of_transport like 'TRUCK'
353 AND carrVeh.carrier_id = carr.carrier_id))
354 AND ROWNUM = 1; --we need to pick one vehicle
355
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 length := -99;
359 width := -99;
360 height := -99;
361 END;
362
363
364 --determine how many rows in fte_vehicle_types contain null in at least one of the dimension fields
365 -- SELECT COUNT(*) INTO total
366 -- FROM fte_vehicle_types vt,
367 -- mtl_system_items_b si
368 -- WHERE vt.inventory_item_id = si.inventory_item_id
369 -- AND vt.organization_id = si.organization_id
370 -- AND (usable_length IS NULL
371 -- OR usable_width IS NULL
372 -- OR usable_height IS NULL
373 -- OR dimension_uom_code IS NULL);
374
375 --if there is at least one record in vehicle_types with null (=-99) in one of its dimensions then assume
376 --that vehicle has infinite capacity. Consequently, no exception of this type will be generated
377 --On the other hand, if that count is zero then we check for the exception
378 -- IF total > 0 THEN
379
380 debug_output('length='||length || ', width=' || width || ', height=' || height );
381 --We do not insert a row if count = 0
382 IF length <> -99 AND width <> -99 AND height <> -99 THEN
383 --Find max, med, and min of the three dimensions
384 IF length > width AND length > height THEN
385 maxDimV := length;
386 SELECT GREATEST(width, height) INTO midDimV FROM dual;
387 SELECT LEAST(width, height) INTO minDimV FROM dual;
388 ELSE
389 IF width > height THEN
390 maxDimV := width;
391 SELECT GREATEST(length, height) INTO midDimV FROM dual;
392 SELECT LEAST(length, height) INTO minDimV FROM dual;
393 ELSE
394 maxDimV := height;
395 SELECT GREATEST(width, length) INTO midDimV FROM dual;
396 SELECT LEAST(width, length) INTO minDimV FROM dual;
397 END IF;
398 END IF;
399
400 debug_output('maxDimV='||maxDimV || ', midDimV=' || midDimV || ', minDimV=' || minDimV );
401
402 --insert entry in mst_exceptions
403 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
404 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
405 CREATION_DATE)
406 VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 902, 0,userIdIn,
407 userIdIn,-1,sysdate, sysdate);
408
409 SELECT exception_id INTO excptnId
410 FROM mst_exceptions
411 WHERE plan_id = plan_idIn
412 AND exception_type = 902;
413
414 FOR c1rec IN c_pieces LOOP
415
416 --Find max, med, and min of the three dimensions
417 IF c1rec.UNIT_LENGTH > c1rec.UNIT_WIDTH AND c1rec.UNIT_LENGTH > c1rec.UNIT_HEIGHT THEN
418 maxDimP := c1rec.UNIT_LENGTH;
419 SELECT GREATEST(c1rec.UNIT_WIDTH, c1rec.UNIT_HEIGHT) INTO midDimP FROM dual;
420 SELECT LEAST(c1rec.UNIT_WIDTH, c1rec.UNIT_HEIGHT) INTO minDimP FROM dual;
421 ELSE
422 IF c1rec.UNIT_WIDTH > c1rec.UNIT_HEIGHT THEN
423 maxDimP := c1rec.UNIT_WIDTH;
424 SELECT GREATEST(c1rec.UNIT_LENGTH, c1rec.UNIT_HEIGHT) INTO midDimP FROM dual;
425 SELECT LEAST(c1rec.UNIT_LENGTH, c1rec.UNIT_HEIGHT) INTO minDimP FROM dual;
426 ELSE
427 maxDimP := c1rec.UNIT_HEIGHT;
428 SELECT GREATEST(c1rec.UNIT_WIDTH, c1rec.UNIT_LENGTH) INTO midDimP FROM dual;
429 SELECT LEAST(c1rec.UNIT_WIDTH, c1rec.UNIT_LENGTH) INTO minDimP FROM dual;
430 END IF;
431 END IF;
432
433 GET_UOM_CONVERSION_RATES(c1rec.dimension_uom_code,
434 tp_dimension_uom,
435 c1rec.inventory_item_id,
436 p_conv_found,
437 p_conv_rate);
438 IF p_conv_found = true THEN
439 maxDimP := maxDimP * p_conv_rate;
440 minDimP := minDimP * p_conv_rate;
441 midDimP := midDimP * p_conv_rate;
442
443 IF 100*((maxDimP/maxDimV)-1)>thresholdPct
444 OR 100*((minDimP/minDimV)-1)>thresholdPct
445 OR 100*((midDimP/midDimV)-1)>thresholdPct THEN
446 --obtain delivery_id
447 BEGIN
448 SELECT da.delivery_id
449 INTO deliveryId
450 FROM mst_delivery_assignments da,
451 mst_delivery_details det
452 WHERE det.delivery_detail_id = da.delivery_detail_id (+)
453 AND det.plan_id = plan_idIn
454 AND c1rec.delivery_detail_id = det.delivery_detail_id;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 debug_output('delivery_detail_id could not be associated to delivery_id');
458 deliveryId := NULL;
459 WHEN TOO_MANY_ROWS THEN
460 debug_output('delivery_detail_id associated to more than one delivery_id');
461 deliveryId := NULL;
462 END;
463
464 debug_output('delivery_detail_id=' || c1rec.delivery_detail_id || ', maxDimP='||maxDimP || ', midDimP=' || midDimP || ', minDimP=' || minDimP );
465
466 --insert exceptions detected into mst_exception_details
467 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id,
468 exception_type, delivery_detail_id,
469 delivery_id, number1, number2, number3, char1,
470 created_by, last_updated_by, LAST_UPDATE_LOGIN,
471 LAST_UPDATE_DATE, CREATION_DATE, STATUS)
472 VALUES (mst_exception_details_s.nextval, excptnId, plan_idIn,
473 902, c1rec.delivery_detail_id,
474 deliveryId, length, width, height, tp_dimension_uom,
475 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
476 END IF;
477 END IF;
478 END LOOP;
479
480 --update count of this exception in mst_exceptions
481 UPDATE mst_exceptions
482 SET EXCEPTION_COUNT = ( select count(*)
483 from mst_exception_details det
484 where det.exception_id = excptnId)
485 WHERE EXCEPTION_id = excptnId;
486
487 --in case no exception of this type was generated delete entry in mst_exceptions table
488 DELETE FROM mst_exceptions
489 WHERE exception_count = 0 and exception_id = excptnId;
490 END IF;
491
492 COMMIT;
493 END DimensionViolForPieceExcptn;
494
495
496
497 PROCEDURE WgtVolViolForPieceExcptn (plan_idIn NUMBER, userIdIn NUMBER) IS
498 excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
499 maxVolumeV NUMBER; -- volume of the biggest vehicle available
500 maxWeightV NUMBER; -- weight of the biggest vehicle available
501 exceptionFlag BOOLEAN;
502 tp_volume_uom VARCHAR2(3);
503 tp_weight_uom VARCHAR2(3);
504 thresholdPct NUMBER;
505
506 -- --Obtain pieces to check
507 -- CURSOR c_pieces IS
508 -- SELECT DELIVERY_DETAIL_ID, det.inventory_item_id,
509 -- net_weight, si.unit_length, si.unit_width, si.unit_height,
510 -- requested_quantity, si.weight_uom_code, si.dimension_uom_code
511 -- FROM MST_DELIVERY_DETAILS det,
512 -- MTL_SYSTEM_ITEMS si
513 -- WHERE det.PLAN_ID = plan_idIn
514 -- AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
515 -- AND det.requested_quantity = 1
516 -- AND det.inventory_item_id = si.inventory_item_id
517 -- AND det.organization_id = si.organization_id;
518
519 --According to HLD (6/12/03) we need to check total weight and volume in delivery line for
520 --those delivery lines which consist of a package or pallet, or contain quantity = 1.
521 --Obtain delivery lines to be checked (weight and volume converted to tp uom)
522 CURSOR c_pieces (tp_vol_uom VARCHAR2, tp_wgt_uom VARCHAR2) IS
523 SELECT det.delivery_detail_id, da.delivery_id,
524 NVL(det.net_weight,0) weight,
525 NVL(det.volume,0) volume
526 FROM MST_DELIVERY_DETAILS det,
527 MTL_SYSTEM_ITEMS si,
528 MST_DELIVERY_ASSIGNMENTS da
529 WHERE det.PLAN_ID = plan_idIn
530 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
531 AND det.delivery_detail_id = da.delivery_detail_id (+)
532 AND (det.requested_quantity = 1 OR det.container_flag = 1)
533 AND det.inventory_item_id = si.inventory_item_id
534 AND det.organization_id = si.organization_id;
535
536 maxVolVehTypeId NUMBER;
537 maxWgtVehTypeId NUMBER;
538
539 BEGIN
540 --clean previous exceptions of this type
541 DELETE FROM mst_exception_details
542 WHERE plan_id = plan_idIn
543 AND exception_type = 903;
544
545 DELETE FROM mst_exceptions
546 WHERE plan_id = plan_idIn
547 AND exception_type = 903;
548
549 --get the threshold from mst_excep_preferences
550 thresholdPct := getExceptionThreshold(903,userIdIn);
551 debug_output('thresholdPct='||thresholdPct);
552 --if no threshold found it means exception is disabled, so exit procedure
553 IF thresholdPct = -999 THEN
554 debug_output('exception calculation disabled');
555 RETURN;
556 END IF;
557
558 --get tp uoms from mst_plans
559 SELECT weight_uom, volume_uom
560 INTO tp_weight_uom, tp_volume_uom
561 FROM mst_plans
562 WHERE plan_id = plan_idIn;
563
564
565 --find vehicle with the biggest volume
566 SELECT MAX(NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0)) as volume
567 INTO maxVolumeV
568 FROM fte_vehicle_types vt,
569 mtl_system_items_b si
570 WHERE vt.inventory_item_id = si.inventory_item_id
571 AND vt.organization_id = si.organization_id
572 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
573 FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
574 WHERE carr.carrier_id = serv.carrier_id
575 AND serv.mode_of_transport like 'TRUCK'
576 AND carrVeh.carrier_id = carr.carrier_id);
577
578
579 --find vehicle with the biggest weight
580 SELECT MAX(NVL(CONV_TO_UOM(si.maximum_load_weight, si.weight_uom_code,tp_weight_uom, si.inventory_item_id),0)) as load_weight
581 INTO maxWeightV
582 FROM fte_vehicle_types vt,
583 mtl_system_items_b si
584 WHERE vt.inventory_item_id = si.inventory_item_id
585 AND vt.organization_id = si.organization_id
586 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
587 FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
588 WHERE carr.carrier_id = serv.carrier_id
589 AND serv.mode_of_transport like 'TRUCK'
590 AND carrVeh.carrier_id = carr.carrier_id);
591
592
593 debug_output('maxVolume = ' || maxVolumeV || ' ' || tp_volume_uom || ' (veh id=' || maxVolVehTypeId || ')' );
594 debug_output('maxWeight = ' || maxWeightV || ' ' || tp_weight_uom || ' (veh id=' || maxWgtVehTypeId || ')' );
595
596 --insert entry in mst_exceptions
597 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
598 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
599 CREATION_DATE)
600 VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 903, 0, userIdIn,
601 userIdIn,-1,sysdate, sysdate);
602
603
604 SELECT exception_id INTO excptnId
605 FROM mst_exceptions
606 WHERE plan_id = plan_idIn
607 AND exception_type = 903;
608
609 FOR piece IN c_pieces(tp_volume_uom,tp_weight_uom) LOOP
610 exceptionFlag := false;
611
612 IF maxVolumeV > 0 THEN
613 IF 100*((piece.volume/maxVolumeV)-1) > thresholdPct THEN
614 exceptionFlag := true;
615 --debug_output('Exception volume!!');
616 END IF;
617 END IF;
618
619 IF maxWeightV > 0 THEN
620 IF 100*((piece.weight/maxWeightV)-1) > thresholdPct THEN
621 exceptionFlag := true;
622 --debug_output('Exception weight!!');
623 END IF;
624 END IF;
625
626 --if maxVolumeV = 0 or maxWeightV = 0 then we assume it is infinite and no exceptions is generated
627 IF exceptionFlag = true THEN
628 debug_output('delivery_detail_id = ' || TO_CHAR(piece.delivery_detail_id) || ', volume = ' || piece.volume || ', weight = ' || piece.weight);
629
630 --insert exception detected into mst_exception_details
631 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
632 delivery_detail_id, delivery_id,
633 number1, number2,
634 char1, char2,
635 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
636 CREATION_DATE, STATUS)
637 VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 903,
638 piece.delivery_detail_id, piece.delivery_id,
639 maxWeightV, maxVolumeV,
640 tp_weight_uom, tp_volume_uom,
641 userIdIn, userIdIn,-1,sysdate, sysdate,3);
642 END IF;
643 END LOOP;
644
645 --update count of this exception in mst_exceptions
646 UPDATE mst_exceptions
647 SET EXCEPTION_COUNT = ( select count(*)
648 from mst_exception_details det
649 where det.exception_id = excptnId)
650 WHERE EXCEPTION_id = excptnId;
651
652 --in case no exception of this type was generated delete entry in mst_exceptions table
653 DELETE FROM mst_exceptions
654 WHERE exception_count = 0 and exception_id = excptnId;
655
656 commit;
657 END WgtVolViolForPieceExcptn;
658
659
660 PROCEDURE WgtVolViolForDLExcptn (plan_idIn NUMBER, userIdIn NUMBER) IS
661 excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
662 maxVolumeV NUMBER; -- volume of the biggest vehicle available
663 maxWeightV NUMBER; -- weight of the biggest vehicle available
664 exceptionFlag BOOLEAN;
665 tp_weight_uom VARCHAR2(3);
666 tp_volume_uom VARCHAR2(3);
667 thresholdPct NUMBER;
668
669 CURSOR c_DL (tp_vol_uom VARCHAR2, tp_wgt_uom VARCHAR2) IS
670 SELECT det.delivery_detail_id, da.delivery_id,
671 NVL(det.net_weight,0) weight,
672 NVL(det.volume,0) volume
673 FROM MST_DELIVERY_DETAILS det,
674 MTL_SYSTEM_ITEMS si,
675 MST_DELIVERY_ASSIGNMENTS da
676 WHERE det.PLAN_ID = plan_idIn
677 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
678 AND det.delivery_detail_id = da.delivery_detail_id (+)
679 AND det.requested_quantity > 1
680 AND det.inventory_item_id = si.inventory_item_id
681 AND det.organization_id = si.organization_id;
682
683 BEGIN
684 --clean previous exceptions of this type
685 DELETE FROM mst_exception_details
686 WHERE plan_id = plan_idIn
687 AND exception_type = 904;
688
689 DELETE FROM mst_exceptions
690 WHERE plan_id = plan_idIn
691 AND exception_type = 904;
692
693 --get the threshold from mst_excep_preferences
694 thresholdPct := getExceptionThreshold(904,userIdIn);
695 debug_output('thresholdPct='||thresholdPct);
696 --if no threshold found it means exception is disabled, so exit procedure
697 IF thresholdPct = -999 THEN
698 debug_output('exception calculation disabled');
699 RETURN;
700 END IF;
701
702
703 --get tp uoms from mst_plans
704 SELECT weight_uom, volume_uom
705 INTO tp_weight_uom, tp_volume_uom
706 FROM mst_plans
707 WHERE plan_id = plan_idIn;
708
709
710 /* Debug query
711 SELECT si.internal_volume as volume, si.volume_uom_code, VEHICLE_TYPE_ID, vt.inventory_item_id , vt.organization_id
712 FROM fte_vehicle_types vt,
713 mtl_system_items_b si
714 WHERE vt.inventory_item_id = si.inventory_item_id
715 AND vt.organization_id = si.organization_id
716 AND vt.vehicle_type_id in (select distinct vehicle_type_id
717 from wsh_carriers carr, wsh_carrier_services serv, WSH_CARRIER_VEHICLE_TYPES carrVeh
718 where carr.carrier_id = serv.carrier_id
719 and serv.mode_of_transport like 'TRUCK'
720 and carrVeh.carrier_id = carr.carrier_id)
721 AND si.internal_volume =
722 (SELECT MAX(si.internal_volume) as volume
723 FROM fte_vehicle_types vt,
724 mtl_system_items_b si
725 WHERE vt.inventory_item_id = si.inventory_item_id
726 AND vt.organization_id = si.organization_id
727 AND vt.vehicle_type_id in (select distinct vehicle_type_id
728 from wsh_carriers carr, wsh_carrier_services serv, WSH_CARRIER_VEHICLE_TYPES carrVeh
729 where carr.carrier_id = serv.carrier_id
730 and serv.mode_of_transport like 'TRUCK'
731 and carrVeh.carrier_id = carr.carrier_id))
732 AND ROWNUM = 1;
733 */
734
735 --find vehicle with the biggest volume
736 SELECT MAX(NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0)) as volume
737 INTO maxVolumeV
738 FROM fte_vehicle_types vt,
739 mtl_system_items_b si
740 WHERE vt.inventory_item_id = si.inventory_item_id
741 AND vt.organization_id = si.organization_id
742 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
743 FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
744 WHERE carr.carrier_id = serv.carrier_id
745 AND serv.mode_of_transport like 'TRUCK'
746 AND carrVeh.carrier_id = carr.carrier_id);
747
748
749 --find vehicle with the biggest weight
750 SELECT MAX(NVL(CONV_TO_UOM(si.maximum_load_weight, si.weight_uom_code,tp_weight_uom, si.inventory_item_id),0)) as load_weight
751 INTO maxWeightV
752 FROM fte_vehicle_types vt,
753 mtl_system_items_b si
754 WHERE vt.inventory_item_id = si.inventory_item_id
755 AND vt.organization_id = si.organization_id
756 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
757 FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
758 WHERE carr.carrier_id = serv.carrier_id
759 AND serv.mode_of_transport like 'TRUCK'
760 AND carrVeh.carrier_id = carr.carrier_id);
761
762
763 debug_output('maxVolume = ' || maxVolumeV || ' ' || tp_volume_uom || ', maxWeight = ' || maxWeightV || ' ' || tp_weight_uom);
764
765 --insert entry in mst_exceptions
766 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
767 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
768 CREATION_DATE)
769 VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 904, 0, userIdIn,
770 userIdIn,-1,sysdate, sysdate);
771
772 SELECT exception_id INTO excptnId
773 FROM mst_exceptions
774 WHERE plan_id = plan_idIn
775 AND exception_type = 904;
776
777 FOR deliveryLine IN c_DL(tp_volume_uom, tp_weight_uom) LOOP
778
779 exceptionFlag := false;
780 IF maxVolumeV > 0 THEN
781 -- debug_output('volumeDL ' || TO_CHAR(deliveryLine.volume));
782 IF 100*((deliveryLine.volume/maxVolumeV)-1) > thresholdPct THEN
783 exceptionFlag := true;
784 -- debug_output('Exception volume!!');
785 END IF;
786 END IF;
787
788 IF maxWeightV > 0 THEN
789 -- debug_output('weightDL ' || TO_CHAR(deliveryLine.weight));
790 IF 100*((deliveryLine.weight/maxWeightV)-1) > thresholdPct THEN
791 exceptionFlag := true;
792 -- debug_output('Exception weight!!');
793 END IF;
794 END IF;
795
796 IF exceptionFlag = true THEN
797 --insert exception detected into mst_exception_details
798 debug_output('delivery_detail_id = ' || TO_CHAR(deliveryLine.DELIVERY_DETAIL_ID) || ', volume = ' || deliveryLine.volume || ', weight = ' || deliveryLine.weight);
799
800 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
801 delivery_detail_id, delivery_id,
802 number1, number2, char1, char2,
803 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
804 CREATION_DATE, STATUS)
805 VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 904,
806 deliveryLine.DELIVERY_DETAIL_ID, deliveryLine.delivery_id,
807 maxWeightV, maxVolumeV, tp_weight_uom, tp_volume_uom,
808 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
809 END IF;
810 END LOOP;
811
812
813 --update count of this exception in mst_exceptions
814 UPDATE mst_exceptions
815 SET EXCEPTION_COUNT = ( select count(*)
816 from mst_exception_details det
817 where det.exception_id = excptnId)
818 WHERE EXCEPTION_id = excptnId;
819
820 --in case no exception of this type was generated delete entry in mst_exceptions table
821 DELETE FROM mst_exceptions
822 WHERE exception_count = 0 and exception_id = excptnId;
823
824 commit;
825 END WgtVolViolForDLExcptn;
826
827
828
829
830 PROCEDURE WgtVolViolForFirmDelivExcptn (plan_idIn NUMBER, userIdIn NUMBER) IS
831 excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
832 maxVolumeV NUMBER; -- volume of the biggest vehicle available
833 maxWeightV NUMBER; -- weight of the biggest vehicle available
834 exceptionFlag BOOLEAN;
835 tp_weight_uom VARCHAR2(3);
836 tp_volume_uom VARCHAR2(3);
837 thresholdPct NUMBER;
838
839 CURSOR c_firmDel IS
840 SELECT delivery_id, NVL(gross_weight,0) weight, NVL(volume,0) volume
841 FROM MST_DELIVERIES
842 WHERE PLAN_ID = plan_idIn
843 AND (PLANNED_FLAG <> 2
844 OR PRESERVE_GROUPING_FLAG <> 2
845 OR KNOWN_TE_FIRM_STATUS <> 3);
846 BEGIN
847 --clean previous exceptions of this type
848 DELETE FROM mst_exception_details
849 WHERE plan_id = plan_idIn
850 AND exception_type = 905;
851
852 DELETE FROM mst_exceptions
853 WHERE plan_id = plan_idIn
854 AND exception_type = 905;
855
856 --get the threshold from mst_excep_preferences
857 thresholdPct := getExceptionThreshold(905,userIdIn);
858 debug_output('thresholdPct='||thresholdPct);
859 --if no threshold found it means exception is disabled, so exit procedure
860 IF thresholdPct = -999 THEN
861 debug_output('exception calculation disabled');
862 RETURN;
863 END IF;
864
865
866 --get tp uoms from mst_plans
867 SELECT weight_uom, volume_uom
868 INTO tp_weight_uom, tp_volume_uom
869 FROM mst_plans
870 WHERE plan_id = plan_idIn;
871
872 --find vehicle with the biggest volume
873 SELECT MAX(NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0)) as volume
874 INTO maxVolumeV
875 FROM fte_vehicle_types vt,
876 mtl_system_items_b si
877 WHERE vt.inventory_item_id = si.inventory_item_id
878 AND vt.organization_id = si.organization_id
879 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
880 FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
881 WHERE carr.carrier_id = serv.carrier_id
882 AND serv.mode_of_transport like 'TRUCK'
883 AND carrVeh.carrier_id = carr.carrier_id);
884
885
886 --find vehicle with the biggest weight
887 SELECT MAX(NVL(CONV_TO_UOM(si.maximum_load_weight, si.weight_uom_code,tp_weight_uom, si.inventory_item_id),0)) as load_weight
888 INTO maxWeightV
889 FROM fte_vehicle_types vt,
890 mtl_system_items_b si
891 WHERE vt.inventory_item_id = si.inventory_item_id
892 AND vt.organization_id = si.organization_id
893 AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
894 FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
895 WHERE carr.carrier_id = serv.carrier_id
896 AND serv.mode_of_transport like 'TRUCK'
897 AND carrVeh.carrier_id = carr.carrier_id);
898
899
900 debug_output('maxVolume = ' || maxVolumeV || ' ' || tp_volume_uom || ', maxWeight = ' || maxWeightV || ' ' || tp_weight_uom);
901
902 --insert entry in mst_exceptions
903 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
904 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
905 CREATION_DATE)
906 VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 905, 0, userIdIn,
907 userIdIn,-1,sysdate, sysdate);
908
909 SELECT exception_id INTO excptnId
910 FROM mst_exceptions
911 WHERE plan_id = plan_idIn
912 AND exception_type = 905;
913
914 FOR delivery IN c_firmDel LOOP
915 debug_output('deliv=' || TO_CHAR(delivery.delivery_id) ||
916 ', volume=' || TO_CHAR(delivery.volume) ||
917 ', weight=' || TO_CHAR(delivery.weight));
918 exceptionFlag := false;
919 IF maxVolumeV > 0 THEN
920 IF 100*((delivery.volume/maxVolumeV)-1) > thresholdPct THEN
921 exceptionFlag := true;
922 --debug_output('Exception volume!!');
923 END IF;
924 END IF;
925
926 IF maxWeightV > 0 THEN
927 IF 100*((delivery.weight/maxWeightV)-1) > thresholdPct THEN
928 exceptionFlag := true;
929 --debug_output('Exception weight!!');
930 END IF;
931 END IF;
932
933 IF exceptionFlag = true THEN
934 --insert exception detected into mst_exception_details
935 debug_output('delivery_id = ' || TO_CHAR(delivery.DELIVERY_ID) || ', volume = ' || delivery.volume || ', weight = ' || delivery.weight);
936
937 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
938 delivery_id,
939 number1, number2, char1, char2,
940 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
941 CREATION_DATE, STATUS)
942 VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 905,
943 delivery.delivery_id,
944 maxWeightV, maxVolumeV, tp_weight_uom, tp_volume_uom,
945 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
946 END IF;
947 END LOOP;
948
949
950 --update count of this exception in mst_exceptions
951 UPDATE mst_exceptions
952 SET EXCEPTION_COUNT = ( select count(*)
953 from mst_exception_details det
954 where det.exception_id = excptnId)
955 WHERE EXCEPTION_id = excptnId;
956
957 --in case no exception of this type was generated delete entry in mst_exceptions table
958 DELETE FROM mst_exceptions
959 WHERE exception_count = 0 and exception_id = excptnId;
960
961 commit;
962 END WgtVolViolForFirmDelivExcptn;
963
964
965
966 PROCEDURE InsufficientIntransitTimeExptn (plan_idIn NUMBER, userIdIn NUMBER) IS
967 excptnId NUMBER;
968 requiredTransitTimeInHrs NUMBER;
969 availTimeInDays NUMBER;
970 distance NUMBER;
971
972 --According to Anuj doing the join without using the source_location_code is safe. However, in the case
973 --in the future we need to do that join then we will need an index on that column, since it takes a
974 --considerable amount of time to perform this query adding those two conditions.
975 CURSOR cursor_DL IS
976 SELECT distinct delivery_id, det.delivery_detail_id,
977 det.ship_from_location_id ship_from_location_id,
978 det.ship_to_location_id ship_to_location_id,
979 latest_acceptable_date, earliest_pickup_date
980 FROM MST_DELIVERY_DETAILS det,
981 MST_DELIVERY_ASSIGNMENTS da,
982 WSH_LOCATIONS loc1,
983 WSH_LOCATIONS loc2
984 WHERE det.plan_id = plan_idIn
985 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
986 AND det.delivery_detail_id = da.delivery_detail_id (+)
987 AND parent_delivery_detail_id is null
988 AND loc1.wsh_location_id = det.ship_from_location_id
989 AND loc2.wsh_location_id = det.ship_to_location_id;
990
991 --Auxiliary data structures to cache all the data to insert in exception_details
992 TYPE delivery_id_t IS TABLE OF MST_DELIVERIES.DELIVERY_ID%TYPE INDEX BY BINARY_INTEGER;
993 TYPE delivery_detail_id_t IS TABLE OF MST_DELIVERY_DETAILS.DELIVERY_DETAIL_ID%TYPE INDEX BY BINARY_INTEGER;
994 TYPE distance_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
995 TYPE time_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
996
997 all_details_delivery_id delivery_id_t;
998 all_details_delivery_detail_id delivery_detail_id_t;
999 all_details_distance distance_t;
1000 all_details_time time_t;
1001 all_details_availTime time_t;
1002 j NUMBER;
1003 thresholdInHrs NUMBER;
1004 planStartDate DATE;
1005
1006 BEGIN
1007 --Delete previous occurrences of this exception in mst_exceptions and the details table
1008 DELETE FROM mst_exception_details
1009 WHERE plan_id = plan_idIn
1010 AND exception_type = 104;
1011
1012 DELETE FROM mst_exceptions
1013 WHERE plan_id = plan_idIn
1014 AND exception_type = 104;
1015
1016 --get the threshold from mst_excep_preferences
1017 thresholdInHrs := getExceptionThreshold(104,userIdIn);
1018 debug_output('thresholdInHrs=' ||thresholdInHrs);
1019 --if no threshold found it means exception is disabled, so exit procedure
1020 IF thresholdInHrs = -999 THEN
1021 debug_output('exception calculation disabled');
1022 RETURN;
1023 END IF;
1024
1025 --current date is plan_start_date (not sysdate)
1026 --SELECT plan_start_date into planStartDate
1027 SELECT start_date into planStartDate
1028 FROM mst_plans
1029 WHERE plan_id = plan_idIn;
1030
1031 --Create fresh entry for exception and keep exception_id for updates in details table
1032 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
1033 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1034 CREATION_DATE)
1035 VALUES (mst_exceptions_s.nextval, plan_idIn, 100, 104, 0, userIdIn,
1036 userIdIn,-1,sysdate, sysdate);
1037
1038 SELECT exception_id INTO excptnId
1039 FROM mst_exceptions
1040 WHERE plan_id = plan_idIn
1041 AND exception_type = 104;
1042
1043 j := 0;
1044 FOR delivLine IN cursor_DL LOOP
1045 requiredTransitTimeInHrs := GET_MINIMUM_TRANSIT_TIME(delivLine.ship_from_location_id, delivLine.ship_to_location_id, plan_idIn);
1046
1047 debug_output('delivery_detail_id=' || delivLine.delivery_detail_id || 'reqTransitTime=' || requiredTransitTimeInHrs);
1048
1049 IF delivLine.earliest_pickup_date < planStartDate THEN
1050 IF delivLine.latest_acceptable_date < planStartDate THEN
1051 availTimeInDays := 0;
1052 ELSE
1053 availTimeInDays := delivLine.latest_acceptable_date - planStartDate;
1054 END IF;
1055 ELSE
1056 availTimeInDays := delivLine.latest_acceptable_date - delivLine.earliest_pickup_date;
1057 END IF;
1058
1059 debug_output('availTransitTime=' || availTimeInDays*24);
1060
1061 -- ****************** check for ocurrence of the exception ***********************
1062 IF ((availTimeInDays*24 + thresholdInHrs) < (requiredTransitTimeInHrs)) THEN
1063 --determine distance for this O-D pair
1064 BEGIN
1065 --FTE_LOCATION_MILEAGES is distance table
1066 --key is <origin_id, destination_id,IDENTIFIER_TYPE>. Origin and destination are FK to WSH_REGIONS.
1067 --IDENTIFIER_TYPE can be city or state (Anuj)
1068 --select MIN distance because we might have more than one entry in distance table. Take the one with minimum distance for now.
1069 --Should we use distance_level profile to choose which one?
1070 SELECT MIN(lm.distance)
1071 INTO distance
1072 FROM FTE_LOCATION_MILEAGES lm,
1073 WSH_REGION_LOCATIONS origReg,
1074 WSH_REGION_LOCATIONS destReg
1075 WHERE origReg.location_id = delivLine.ship_from_location_id
1076 AND destReg.location_id = delivLine.ship_to_location_id
1077 AND origReg.region_id = origin_id
1078 AND destReg.region_id = destination_id;
1079 EXCEPTION
1080 WHEN NO_DATA_FOUND THEN
1081 distance := -9999;
1082 END;
1083
1084 debug_output('delivery_detail_id=' || delivLine.delivery_detail_id || ', distance=' || distance
1085 || ', requiredTransitTimeInHrs='||requiredTransitTimeInHrs || ', availTimeInHrs=' || availTimeInDays * 24);
1086
1087 all_details_delivery_id(j) := delivLine.delivery_id;
1088 all_details_delivery_detail_id(j) := delivLine.delivery_detail_id;
1089 all_details_distance(j) := distance;
1090 all_details_time(j) := requiredTransitTimeInHrs;
1091 all_details_availTime(j) := availTimeInDays * 24;
1092 j := j + 1;
1093 END IF;
1094 END LOOP;
1095
1096 --bulk add all exception_details
1097 --This is used to improve performance (using this insert inside the loop makes the procedure
1098 --take minutes to execute, compared to seconds in this way in test data).
1099 IF j > 0 THEN
1100 FORALL k IN all_details_delivery_id.FIRST..all_details_delivery_id.LAST
1101 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
1102 delivery_id, delivery_detail_id,
1103 number1, number2,
1104 number3,
1105 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1106 CREATION_DATE, STATUS)
1107 VALUES (mst_exception_details_s.nextval, excptnId , plan_IdIn, 104,
1108 all_details_delivery_id(k), all_details_delivery_detail_id(k),
1109 all_details_distance(k), all_details_time(k),
1110 all_details_availTime(k),
1111 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
1112 END IF;
1113
1114 --update count of this exception in mst_exceptions
1115 UPDATE mst_exceptions
1116 SET EXCEPTION_COUNT = ( select count(*)
1117 from mst_exception_details det
1118 where det.exception_id = excptnId)
1119 WHERE EXCEPTION_id = excptnId;
1120
1121 --in case no exception of this type was generated delete entry in mst_exceptions table
1122 DELETE FROM mst_exceptions
1123 WHERE exception_count = 0 and exception_id = excptnId;
1124 commit;
1125
1126 END InsufficientIntransitTimeExptn;
1127
1128
1129 PROCEDURE PastDueOrdersExptn (plan_idIn NUMBER, userIdIn NUMBER) IS
1130 excptnId NUMBER;
1131 requiredTransitTimeInHrs NUMBER;
1132 availTransitTimeInHrs NUMBER;
1133 thresholdInHrs NUMBER;
1134
1135 --highest level delivery lines that are past due.
1136 CURSOR cursor_past_due_orders IS
1137 SELECT DISTINCT da.delivery_id, det.delivery_detail_id,
1138 det.ship_from_location_id, det.ship_to_location_id,
1139 ((det.latest_acceptable_date - plan.start_date)*24) AS availTime
1140 FROM MST_DELIVERY_DETAILS det,
1141 MST_DELIVERY_ASSIGNMENTS da,
1142 MST_PLANS plan
1143 WHERE det.plan_id = plan_idIn
1144 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
1145 AND det.delivery_detail_id = da.delivery_detail_id (+)
1146 AND da.parent_delivery_detail_id is null
1147 AND plan.plan_id = plan_idIn
1148 AND ( (det.latest_acceptable_date + thresholdInHrs/24 < plan.start_date)
1149 OR (det.latest_pickup_date + thresholdInHrs/24 < plan.start_date) );
1150 BEGIN
1151 --Delete previous occurrences of this exception in mst_exceptions and the details table
1152 DELETE FROM mst_exception_details
1153 WHERE plan_id = plan_idIn
1154 AND exception_type = 105;
1155
1156 DELETE FROM mst_exceptions
1157 WHERE plan_id = plan_idIn
1158 AND exception_type = 105;
1159
1160 --get the threshold from mst_excep_preferences
1161 thresholdInHrs := getExceptionThreshold(105,userIdIn);
1162 debug_output('thresholdInHrs='||thresholdInHrs);
1163 --if no threshold found it means exception is disabled, so exit procedure
1164 IF thresholdInHrs = -999 THEN
1165 debug_output('exception calculation disabled');
1166 RETURN;
1167 END IF;
1168
1169
1170 --Create fresh entry for exception and keep exception_id for updates in details table
1171 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
1172 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1173 CREATION_DATE)
1174 VALUES (mst_exceptions_s.nextval, plan_idIn, 100, 105, 0, userIdIn,
1175 userIdIn,-1,sysdate, sysdate);
1176
1177 SELECT exception_id INTO excptnId
1178 FROM mst_exceptions
1179 WHERE plan_id = plan_idIn
1180 AND exception_type = 105;
1181
1182
1183 --store the details in mst_exception_details
1184 FOR delivLine IN cursor_past_due_orders LOOP
1185
1186 requiredTransitTimeInHrs := GET_MINIMUM_TRANSIT_TIME(delivLine.ship_from_location_id, delivLine.ship_to_location_id, plan_idIn);
1187
1188 IF delivLine.availTime < 0 THEN
1189 availTransitTimeInHrs := 0;
1190 ELSE
1191 availTransitTimeInHrs := delivLine.availTime;
1192 END IF;
1193
1194 debug_output('delivery_detail_id='||delivLine.delivery_detail_id || ', delivLine.availTime=' || delivLine.availTime ||
1195 ', requiredTransitTimeInHrs=' || requiredTransitTimeInHrs);
1196 INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
1197 delivery_id, delivery_detail_id,
1198 number2, number3,
1199 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1200 CREATION_DATE, STATUS)
1201 VALUES (mst_exception_details_s.nextval, excptnId , plan_IdIn, 105,
1202 delivLine.delivery_id, delivLine.delivery_detail_id,
1203 requiredTransitTimeInHrs, availTransitTimeInHrs,
1204 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
1205 END LOOP;
1206
1207 --update count of this exception in mst_exceptions
1208 UPDATE mst_exceptions
1209 SET EXCEPTION_COUNT = ( select count(*)
1210 from mst_exception_details det
1211 where det.exception_id = excptnId)
1212 WHERE EXCEPTION_id = excptnId;
1213
1214 --in case no exception of this type was generated delete entry in mst_exceptions table
1215 DELETE FROM mst_exceptions
1216 WHERE exception_count = 0 and exception_id = excptnId;
1217 commit;
1218 END PastDueOrdersExptn;
1219
1220
1221 --input:
1222 -- ship_from: location_id of origin
1223 -- ship_to: location_id of destination
1224 --output:
1225 -- minimum transit time irrespective of mode/vehicle features (in tp_uom which represents hours)
1226 FUNCTION GET_MINIMUM_TRANSIT_TIME(ship_from NUMBER, ship_to NUMBER, plan_idIn NUMBER) RETURN NUMBER IS
1227 minimumTransitTime NUMBER;
1228 tl_minimumTransitTime NUMBER;
1229 Distance NUMBER;
1230 PI NUMBER;
1231 HighwayDistance NUMBER;
1232 lat_1 NUMBER;
1233 lat_2 NUMBER;
1234 lon_1 NUMBER;
1235 lon_2 NUMBER;
1236
1237 -- Anuj says FTE_LANES is not going to be populated by TE
1238 -- CURSOR c_LTLParcel_transit_times IS
1239 -- SELECT lane_id, carrier_id, mode_of_transportation_code, lanes.transit_time, lanes.transit_time_uom
1240 -- FROM WSH_REGION_LOCATIONS rl1,
1241 -- WSH_REGION_LOCATIONS rl2,
1242 -- FTE_LANES lanes
1243 -- WHERE rl1.location_id = ship_from
1244 -- AND rl2.location_id = ship_to
1245 -- AND lanes.ORIGIN_ID = rl1.region_id
1246 -- AND lanes.DESTINATION_ID = rl2.region_id;
1247
1248 BEGIN
1249 --check LTL and Parcel
1250 SELECT NVL(MIN(NVL(CONV_TO_UOM(sm.INTRANSIT_TIME,sm.TIME_UOM_CODE,tp_time_uom,0),99999)),99999)
1251 INTO minimumTransitTime
1252 FROM MTL_INTERORG_SHIP_METHODS sm,
1253 WSH_REGION_LOCATIONS rl1,
1254 WSH_REGION_LOCATIONS rl2
1255 WHERE rl1.location_id = ship_from
1256 AND rl2.location_id = ship_to
1257 AND ( ship_from = sm.from_location_id AND ship_to = sm.to_location_id
1258 OR (ship_from = sm.from_location_id AND rl2.region_id = sm.to_region_id)
1259 OR (rl1.region_id = sm.from_region_id AND ship_to = sm.to_location_id)
1260 OR (rl1.region_id = sm.from_region_id AND rl2.region_id = sm.to_region_id));
1261
1262 -- debug_output('minPCL_LTL_TransitTime=' || minimumTransitTime);
1263
1264 --Check TL
1265 --FTE_LOCATION_MILEAGES is distance table
1266 --key is <origin_id, destination_id,IDENTIFIER_TYPE>. Origin and destination are FK to WSH_REGIONS.
1267 --IDENTIFIER_TYPE can be city or state (Anuj)
1268 --TRANSIT_TIME, TRANSIT_TIME_UOM are columns of interest here.
1269 BEGIN
1270 SELECT MIN(CONV_TO_UOM(distTable.transit_time,distTable.transit_time_uom,tp_time_uom,0))
1271 INTO tl_minimumTransitTime
1272 FROM FTE_LOCATION_MILEAGES distTable,
1273 WSH_REGION_LOCATIONS origReg,
1274 WSH_REGION_LOCATIONS destReg
1275 WHERE origReg.location_id = ship_from
1276 AND destReg.location_id = ship_to
1277 AND origReg.region_id = distTable.origin_id
1278 AND destReg.region_id = distTable.destination_id;
1279 EXCEPTION
1280 WHEN NO_DATA_FOUND THEN
1281 tl_minimumTransitTime := -23453;
1282 debug_output('no TL transit time in FTE_LOCATION_MILEAGES');
1283 END;
1284 IF tl_minimumTransitTime IS NULL THEN
1285 tl_minimumTransitTime := -23453;
1286 debug_output('TL transit time in FTE_LOCATION_MILEAGES is null after conversion');
1287 END IF;
1288
1289
1290 -- debug_output('origin=' || ship_from || ', destination=' || ship_to || ', tl_minimumTransitTime=' || tl_minimumTransitTime);
1291 -- IF no entry in dist. table THEN
1292 IF tl_minimumTransitTime = -23453 THEN
1293 --we need to use lat/long method
1294 BEGIN
1295 SELECT NVL(latitude,0), NVL(longitude,0)
1296 INTO lat_1, lon_1
1297 FROM WSH_LOCATIONS
1298 WHERE wsh_location_id = ship_from;
1299 -- debug_output('ship_from=' || ship_from || ', lat=' || lat_1 || ', long=' || lon_1);
1300 EXCEPTION WHEN no_data_found then
1301 debug_output('Error: no entry in distance table and latitude/longitude is null for location_id = ' || ship_from);
1302 RETURN 99999;
1303 END;
1304
1305 BEGIN
1306 SELECT NVL(latitude,0), NVL(longitude,0)
1307 INTO lat_2, lon_2
1308 FROM WSH_LOCATIONS
1309 WHERE wsh_location_id = ship_to;
1310 -- debug_output('ship_to=' || ship_to || ', lat=' || lat_2 || ', long=' || lon_2);
1311
1312 EXCEPTION WHEN no_data_found then
1313 debug_output('Error: no entry in distance table and latitude/longitude is null for location_id = ' || ship_to);
1314 RETURN 99999;
1315 END;
1316
1317 PI := 3.1415926;
1318 Distance := 69.075 * 180/PI *
1319 (ACOS ((SIN(PI /180*lat_1) * SIN(PI /180*lat_2)) +
1320 (COS(PI /180*lat_1) * COS(PI /180*lat_2) * COS(PI /180*ABS(lon_1 - lon_2)))
1321 ));
1322 HighwayDistance := (1+distScaleFactor)*Distance;
1323 tl_minimumTransitTime := HighwayDistance / avgDrvSpeed;
1324 -- debug_output('HighwayDistance=' || HighwayDistance || ', Distance=' || Distance || ', distScaleFactor=' || distScaleFactor );
1325 END IF;
1326
1327 -- debug_output('min_TL_TransitTime=' || tl_minimumTransitTime);
1328
1329 IF tl_minimumTransitTime < minimumTransitTime THEN
1330 minimumTransitTime := tl_minimumTransitTime;
1331 END IF;
1332
1333 RETURN minimumTransitTime;
1334 END GET_MINIMUM_TRANSIT_TIME;
1335
1336
1337 PROCEDURE FacCalViolForPickUpExptn (plan_idIn NUMBER, userIdIn NUMBER) IS
1338 thresholdInHrs NUMBER;
1339
1340 --type used to cache association between location id and calendar code (getCalendar gets too expensive for this query)
1341 TYPE calendar_map_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
1342 calendar_lookup calendar_map_type;
1343
1344
1345 CURSOR cursor_DL IS
1346 SELECT det.delivery_detail_id, det.ship_from_location_id, da.delivery_id,
1347 det.earliest_pickup_date, det.latest_pickup_date
1348 FROM MST_DELIVERY_DETAILS det,
1349 MST_DELIVERY_ASSIGNMENTS da
1350 WHERE da.delivery_detail_id = det.delivery_detail_id (+)
1351 AND da.parent_delivery_detail_id is null
1352 AND det.plan_id = plan_idIn
1353 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null; --added so we consider only input data from TE
1354
1355
1356 excptnId NUMBER;
1357 exceptionFlag NUMBER;
1358 delivDetail NUMBER;
1359 delivId NUMBER;
1360 openTime DATE;
1361 closeTime DATE;
1362 shipFrom NUMBER;
1363 earliest_pickup_date DATE;
1364 latest_pickup_date DATE;
1365 start_open DATE;
1366 end_open DATE;
1367 seq_num NUMBER;
1368
1369 local_EPD DATE;
1370 server_EPD DATE;
1371 local_LPD DATE;
1372 server_LPD DATE;
1373 numberOfCalDays NUMBER;
1374
1375 calCode VARCHAR2(10);
1376 BEGIN
1377 --clean previous exceptions of this type
1378 DELETE FROM mst_exception_details
1379 WHERE plan_id = plan_idIn
1380 AND exception_type = 404;
1381
1382 DELETE FROM mst_exceptions
1383 WHERE plan_id = plan_idIn
1384 AND exception_type = 404;
1385
1386 --get the threshold from mst_excep_preferences
1387 thresholdInHrs:= getExceptionThreshold(404,userIdIn);
1388 debug_output('thresholdInHrs='||thresholdInHrs || ', sysdate = ' || sysdate);
1389 --if no threshold found it means exception is disabled, so exit procedure
1390 IF thresholdInHrs = -999 THEN
1391 debug_output('exception calculation disabled');
1392 RETURN;
1393 END IF;
1394
1395
1396 --insert entry in mst_exceptions
1397 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
1398 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1399 CREATION_DATE)
1400 VALUES (mst_exceptions_s.nextval,plan_idIn, 400, 404, 0, userIdIn,
1401 userIdIn,-1,sysdate, sysdate);
1402
1403 SELECT exception_id INTO excptnId
1404 FROM mst_exceptions
1405 WHERE plan_id = plan_idIn
1406 AND exception_type = 404;
1407
1408 --Obtain number of extra days to consider in plan (using snapshot's logic here) (6/6/2003)
1409 numberOfCalDays := fnd_profile.value('MST_CALENDAR_EXTRA_DAYS');
1410 IF numberOfCalDays is null THEN
1411 numberOfCalDays := 30;
1412 END IF;
1413
1414
1415 debug_output('starting cache of calendar codes');
1416
1417
1418 --cache shipping calendar code and location id association
1419 --Use MST_SNAPSHOT_TASK_UTIL.getCalendar function to obtain calendar code
1420 -- Input : locationId, type = 'SHIPPING' or 'RECEIVING'
1421 -- Output: calendarCode
1422 FOR delivLine IN cursor_DL LOOP
1423 calendar_lookup(delivLine.ship_from_location_id) := MST_SNAPSHOT_TASK_UTIL.getCalendar(delivLine.ship_from_location_id, 'SHIPPING');
1424 END LOOP;
1425
1426
1427 debug_output('starting deliv detail loop');
1428
1429 --loop trough delivery details
1430 OPEN cursor_DL;
1431 LOOP
1432 --get next row (delivery detail)
1433 FETCH cursor_DL INTO delivDetail, shipFrom, delivId,
1434 earliest_pickup_date, latest_pickup_date;
1435 EXIT WHEN cursor_DL%NOTFOUND;
1436
1437 IF earliest_pickup_date IS NOT NULL AND latest_pickup_date IS NOT NULL THEN
1438 -- Adjust pickup time window to local time zone for ship_from facility (using Jin's function)
1439 local_EPD := MST_GEOCODING.Get_local_time(shipFrom,earliest_pickup_date);
1440 server_EPD := earliest_pickup_date;
1441 local_LPD := MST_GEOCODING.Get_local_time(shipFrom,latest_pickup_date);
1442 server_LPD := latest_pickup_date;
1443 /*
1444 debug_output('local_EPD=' || to_char(local_EPD,'MM/DD/YYYY HH24:MI:SS') || ', server_EPD=' || to_char(server_EPD,'MM/DD/YYYY HH24:MI:SS') ||
1445 ', local_LPD=' || to_char(local_LPD,'MM/DD/YYYY HH24:MI:SS') || ', server_LPD=' || to_char(server_LPD,'MM/DD/YYYY HH24:MI:SS') );
1446 */
1447 END IF;
1448
1449 calCode := calendar_lookup(shipFrom);
1450
1451 /*
1452 debug_output('checking delivery_detail_id=' || delivDetail ||
1453 ', cal_code=' || calCode || ', earliest_pkup_date=' || to_char(earliest_pickup_date,'MM/DD/YYYY HH24:MI:SS') ||
1454 ', latest_pickup_date=' || to_char(latest_pickup_date,'MM/DD/YYYY HH24:MI:SS') );
1455 */
1456
1457 IF calCode = '-23453' THEN
1458 debug_output('NULL calendar for delivery_detail_id' || delivDetail);
1459 ELSIF earliest_pickup_date IS NULL OR latest_pickup_date IS NULL THEN
1460 debug_output('earliest_pickup_date IS NULL OR latest_pickup_date IS NULL');
1461 ELSE
1462
1463 --Note: database stores dates in julian format, i.e. is a double whose unit is a day.
1464 --This query allows access to the expanded windows of activity of the origin facility
1465 --Query based on what is currently being used in snapshot to populate calendars flat-file (6/6/2003)
1466 BEGIN
1467 SELECT 2 INTO exceptionFlag --SYS_NO (no exception since there is at least one overlapping shift)
1468 FROM DUAL
1469 WHERE EXISTS
1470 ( SELECT caldates.CALENDAR_DATE
1471 FROM BOM_CALENDAR_DATES caldates,
1472 BOM_SHIFT_DATES sdates,
1473 BOM_SHIFT_TIMES bshift,
1474 MST_PLANS plan
1475 WHERE plan.plan_id = plan_idIn
1476 AND caldates.CALENDAR_CODE = sdates.CALENDAR_CODE (+)
1477 AND caldates.CALENDAR_DATE = sdates.SHIFT_DATE (+)
1478 AND sdates.CALENDAR_CODE = bshift.CALENDAR_CODE (+)
1479 AND sdates.SHIFT_NUM = bshift.SHIFT_NUM (+)
1480 AND caldates.SEQ_NUM is not null
1481 AND sdates.SEQ_NUM(+) is not null
1482 -- AND caldates.CALENDAR_DATE between sysdate and plan.CUTOFF_DATE+numberOfCalDays
1483 AND caldates.CALENDAR_DATE between local_EPD-1 and local_LPD+1
1484 AND caldates.CALENDAR_CODE = calCode
1485 --check if any shift falls inside [EPD,LPD]
1486 AND ( ( local_EPD <= (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) AND
1487 (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LPD)
1488 OR
1489 ( local_EPD <= (caldates.CALENDAR_DATE+bshift.TO_TIME/86400) AND
1490 (caldates.CALENDAR_DATE+bshift.TO_TIME/86400) <= local_LPD)
1491 --check if EPD or LPD falls inside any shift
1492 OR
1493 ( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_EPD AND
1494 local_EPD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) )
1495 OR
1496 ( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LPD AND
1497 local_LPD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) ) ) );
1498
1499
1500 EXCEPTION WHEN no_data_found then
1501 exceptionFlag := 1; --SYS_YES (there is an exception since there is no overlapping shift)
1502 END;
1503
1504 IF exceptionFlag = 1 THEN
1505 debug_output('Exception detected, delivDet=' || delivDetail);
1506
1507 --insert exception detected into mst_exception_details
1508 INSERT INTO mst_exception_details
1509 (exception_detail_id,exception_id, plan_id, exception_type,
1510 delivery_detail_id, delivery_id,
1511 date1, date2,
1512 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1513 CREATION_DATE, STATUS)
1514 VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 404,
1515 delivDetail, delivId,
1516 server_EPD, server_LPD,
1517 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
1518 END IF;
1519 END IF;
1520 END LOOP;
1521 CLOSE cursor_DL;
1522
1523 --update count of this exception in mst_exceptions
1524 UPDATE mst_exceptions
1525 SET EXCEPTION_COUNT = ( select count(*)
1526 from mst_exception_details det
1527 where det.exception_id = excptnId)
1528 WHERE EXCEPTION_id = excptnId;
1529
1530 --in case no exception of this type was generated delete entry in mst_exceptions table
1531 DELETE FROM mst_exceptions
1532 WHERE exception_count = 0 and exception_id = excptnId;
1533 commit;
1534
1535 END FacCalViolForPickUpExptn;
1536
1537
1538
1539 PROCEDURE FacCalViolForDeliveryExptn (plan_idIn NUMBER, userIdIn NUMBER) IS
1540 thresholdInHrs NUMBER;
1541
1542 --type used to cache association between location id and calendar code (getCalendar gets too expensive for this query)
1543 TYPE calendar_map_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
1544 calendar_lookup calendar_map_type;
1545
1546 CURSOR cursor_DL IS
1547 SELECT det.delivery_detail_id, det.ship_to_location_id, da.delivery_id,
1548 det.earliest_acceptable_date, det.latest_acceptable_date
1549 FROM MST_DELIVERY_DETAILS det,
1550 MST_DELIVERY_ASSIGNMENTS da
1551 WHERE da.delivery_detail_id = det.delivery_detail_id (+)
1552 AND da.parent_delivery_detail_id is null
1553 AND det.plan_id = plan_idIn
1554 AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null; --added so we consider only input data from TE
1555
1556 excptnId NUMBER;
1557 exceptionFlag NUMBER;
1558 delivDetail NUMBER;
1559 delivId NUMBER;
1560 openTime DATE;
1561 closeTime DATE;
1562 shipTo NUMBER;
1563 earliest_acceptable_date DATE;
1564 latest_acceptable_date DATE;
1565 start_open DATE;
1566 end_open DATE;
1567
1568 local_EDD DATE;
1569 server_EDD DATE;
1570 local_LDD DATE;
1571 server_LDD DATE;
1572 numberOfCalDays NUMBER;
1573
1574 calCode VARCHAR2(10);
1575 BEGIN
1576 --clean previous exceptions of this type
1577 DELETE FROM mst_exception_details
1578 WHERE plan_id = plan_idIn
1579 AND exception_type = 405;
1580
1581 DELETE FROM mst_exceptions
1582 WHERE plan_id = plan_idIn
1583 AND exception_type = 405;
1584
1585
1586 --get the threshold from mst_excep_preferences
1587 thresholdInHrs := getExceptionThreshold(405,userIdIn);
1588 debug_output('thresholdInHrs='||thresholdInHrs || ', sysdate = ' || sysdate);
1589 --if no threshold found it means exception is disabled, so exit procedure
1590 IF thresholdInHrs = -999 THEN
1591 debug_output('exception calculation disabled');
1592 RETURN;
1593 END IF;
1594
1595
1596 --insert entry in mst_exceptions
1597 INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
1598 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1599 CREATION_DATE)
1600 VALUES (mst_exceptions_s.nextval, plan_idIn, 400, 405, 0, userIdIn,
1601 userIdIn,-1,sysdate, sysdate);
1602
1603 SELECT exception_id INTO excptnId
1604 FROM mst_exceptions
1605 WHERE plan_id = plan_idIn
1606 AND exception_type = 405;
1607
1608
1609 --Obtain number of extra days to consider in plan (using snapshot's logic here) (6/6/2003)
1610 numberOfCalDays := fnd_profile.value('MST_CALENDAR_EXTRA_DAYS');
1611 IF numberOfCalDays is null THEN
1612 numberOfCalDays := 30;
1613 END IF;
1614
1615
1616 debug_output('starting cache of calendar codes');
1617
1618
1619 --cache receiving calendar code and location id association
1620 FOR delivLine IN cursor_DL LOOP
1621 calendar_lookup(delivLine.ship_to_location_id) := MST_SNAPSHOT_TASK_UTIL.getCalendar(delivLine.ship_to_location_id, 'RECEIVING');
1622 END LOOP;
1623
1624
1625 debug_output('starting deliv detail loop');
1626
1627 --loop trough delivery details
1628 OPEN cursor_DL;
1629 LOOP
1630 --get next row (delivery detail)
1631 FETCH cursor_DL INTO delivDetail, shipTo, delivId,
1632 earliest_acceptable_date, latest_acceptable_date;
1633 EXIT WHEN cursor_DL%NOTFOUND;
1634
1635 IF earliest_acceptable_date IS NOT NULL AND latest_acceptable_date IS NOT NULL THEN
1636 -- Adjust dropoff time window to local time zone for ship_to facility (using Jin's function)
1637 local_EDD := MST_GEOCODING.Get_local_time(shipTo,earliest_acceptable_date);
1638 server_EDD := earliest_acceptable_date;
1639 local_LDD := MST_GEOCODING.Get_local_time(shipTo,latest_acceptable_date);
1640 server_LDD := latest_acceptable_date;
1641 /*
1642 debug_output('local_EDD=' || to_char(local_EDD,'MM/DD/YYYY HH24:MI:SS') || ', server_EDD=' || to_char(server_EDD,'MM/DD/YYYY HH24:MI:SS') ||
1643 ', local_LDD=' || to_char(local_LDD,'MM/DD/YYYY HH24:MI:SS') || ', server_LDD=' || to_char(server_LDD,'MM/DD/YYYY HH24:MI:SS') );
1644 */
1645 END IF;
1646
1647 calCode := calendar_lookup(shipTo);
1648
1649 /*
1650 debug_output('checking delivery_detail_id=' || delivDetail ||
1651 ', cal_code=' || calCode || ', earliest_acceptable_date=' || to_char(earliest_acceptable_date,'MM/DD/YYYY HH24:MI:SS') ||
1652 ', latest_acceptable_date=' || to_char(latest_acceptable_date,'MM/DD/YYYY HH24:MI:SS') );
1653 */
1654
1655 IF calCode = '-23453' THEN
1656 debug_output('NULL calendar for delivery_detail_id' || delivDetail);
1657 ELSIF earliest_acceptable_date IS NULL OR latest_acceptable_date IS NULL THEN
1658 debug_output('earliest_acceptable_date IS NULL OR latest_acceptable_date IS NULL');
1659 ELSE
1660
1661
1662 --Note: database stores dates in julian format, i.e. is a double whose unit is a day.
1663 --This query allows access to the expanded windows of activity of the origin facility
1664 --Query based on what is currently being used in snapshot to populate calendars flat-file (6/6/2003)
1665 BEGIN
1666 SELECT 2 INTO exceptionFlag --SYS_NO (no exception since there is at least one overlapping shift)
1667 FROM DUAL
1668 WHERE EXISTS
1669 ( SELECT caldates.CALENDAR_DATE
1670 FROM BOM_CALENDAR_DATES caldates,
1671 BOM_SHIFT_DATES sdates,
1672 BOM_SHIFT_TIMES bshift,
1673 MST_PLANS plan
1674 WHERE plan.plan_id = plan_idIn
1675 AND caldates.CALENDAR_CODE = sdates.CALENDAR_CODE (+)
1676 AND caldates.CALENDAR_DATE = sdates.SHIFT_DATE (+)
1677 AND sdates.CALENDAR_CODE = bshift.CALENDAR_CODE (+)
1678 AND sdates.SHIFT_NUM = bshift.SHIFT_NUM (+)
1679 AND caldates.SEQ_NUM is not null
1680 AND sdates.SEQ_NUM(+) is not null
1681 -- AND caldates.CALENDAR_DATE between sysdate and plan.CUTOFF_DATE+numberOfCalDays
1682 AND caldates.CALENDAR_DATE between local_EDD-1 and local_LDD+1
1683 AND caldates.CALENDAR_CODE = calCode
1684 --check if any shift falls inside [EDD,LDD]
1685 AND ( ( local_EDD <= (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) AND
1686 (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LDD)
1687 OR
1688 ( local_EDD <= (caldates.CALENDAR_DATE+bshift.TO_TIME/86400) AND
1689 (caldates.CALENDAR_DATE+bshift.TO_TIME/86400) <= local_LDD)
1690 --check if EDD or LDD falls inside any shift
1691 OR
1692 ( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_EDD AND
1693 local_EDD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) )
1694 OR
1695 ( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LDD AND
1696 local_LDD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) ) ) );
1697
1698 EXCEPTION WHEN no_data_found then
1699 exceptionFlag := 1; --SYS_YES (there is an exception since there is no overlapping shift)
1700 END;
1701
1702 IF exceptionFlag = 1 THEN
1703 debug_output('Exception detected, delivDet=' || delivDetail);
1704 --insert exception detected into mst_exception_details
1705 INSERT INTO mst_exception_details
1706 (exception_detail_id, exception_id, plan_id, exception_type,
1707 delivery_detail_id, delivery_id,
1708 date1, date2,
1709 created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
1710 CREATION_DATE, STATUS)
1711 VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 405,
1712 delivDetail, delivId,
1713 server_EDD, server_LDD,
1714 userIdIn, userIdIn,-1,sysdate, sysdate, 3);
1715 END IF;
1716 END IF;
1717 END LOOP;
1718 CLOSE cursor_DL;
1719
1720 --update count of this exception in mst_exceptions
1721 UPDATE mst_exceptions
1722 SET EXCEPTION_COUNT = ( select count(*)
1723 from mst_exception_details det
1724 where det.exception_id = excptnId)
1725 WHERE EXCEPTION_id = excptnId;
1726
1727 --in case no exception of this type was generated delete entry in mst_exceptions table
1728 DELETE FROM mst_exceptions
1729 WHERE exception_count = 0 and exception_id = excptnId;
1730 commit;
1731
1732 END FacCalViolForDeliveryExptn;
1733
1734
1735
1736 FUNCTION CONV_TO_UOM(src_value NUMBER, src_uom_code VARCHAR2,
1737 dest_uom_code VARCHAR2,
1738 inventory_item_id NUMBER DEFAULT 0) RETURN NUMBER IS
1739 p_conv_found BOOLEAN;
1740 p_conv_rate NUMBER;
1741 dest_value NUMBER;
1742 BEGIN
1743 GET_UOM_CONVERSION_RATES(src_uom_code, dest_uom_code, inventory_item_id,
1744 p_conv_found, p_conv_rate);
1745
1746 IF p_conv_found = true THEN
1747 dest_value := src_value * p_conv_rate;
1748 ELSE
1749 dest_value := NULL;
1750 END IF;
1751 RETURN dest_value;
1752 END CONV_TO_UOM;
1753
1754 /*----------------------------------------------------+
1755 | This procedure takes as input 2 uom codes in the |
1756 | same uom class or across classes and returns a |
1757 | conv_rate between them. |
1758 | If a conversion is not found then it sets |
1759 | the output variable conv_found to FALSE |
1760 | and returns a conv_rate of 1. |
1761 | Note: code based on |
1762 | MSC_X_UTIL.GET_UOM_CONVERSION_RATES |
1763 +-----------------------------------------------------*/
1764
1765 PROCEDURE GET_UOM_CONVERSION_RATES(p_uom_code IN VARCHAR2,
1766 p_dest_uom_code IN VARCHAR2,
1767 p_inventory_item_id IN NUMBER DEFAULT 0,
1768 p_conv_found OUT NOCOPY BOOLEAN,
1769 p_conv_rate OUT NOCOPY NUMBER) IS
1770 l_uom_class VARCHAR2(10);
1771 l_dest_uom_class VARCHAR2(10);
1772 BEGIN
1773
1774
1775 /*-------------------------------------------------------------+
1776 | Rownum = 1 is used to account for the corner case APS bug |
1777 | when the same uom code points to different unit of measures |
1778 | in multiple instances. This can be removed when APS makes |
1779 | the fix to allow only 1 uom code in addition to unit of |
1780 | measure in MSC_UNITS_OF_MEASURE. |
1781 +--------------------------------------------------------------*/
1782
1783 /*-----------------------------------------------------+
1784 | Inventory Item Id = non zero is required only if |
1785 | we are doing conversions across uom classes |
1786 +------------------------------------------------------*/
1787
1788 BEGIN
1789 SELECT uom_class INTO l_uom_class
1790 FROM mtl_units_of_measure
1791 WHERE uom_code = p_uom_code
1792 AND ROWNUM = 1;
1793
1794 EXCEPTION WHEN no_data_found then
1795 p_conv_found := FALSE;
1796 p_conv_rate := 1.0;
1797 RETURN;
1798 END;
1799
1800 BEGIN
1801 SELECT uom_class INTO l_dest_uom_class
1802 FROM mtl_units_of_measure
1803 WHERE uom_code = p_dest_uom_code
1804 AND ROWNUM = 1;
1805
1806 EXCEPTION WHEN no_data_found then
1807 p_conv_found := FALSE;
1808 p_conv_rate := 1.0;
1809 RETURN;
1810 END;
1811
1812
1813 IF (l_uom_class = l_dest_uom_class) THEN
1814 BEGIN
1815 SELECT muc1.conversion_rate/muc2.conversion_rate INTO p_conv_rate
1816 FROM mtl_uom_conversions muc1,
1817 mtl_uom_conversions muc2
1818 WHERE muc1.inventory_item_id = 0
1819 AND muc2.inventory_item_id = 0
1820 AND muc1.uom_class = muc2.uom_class
1821 AND muc1.uom_class = l_uom_class
1822 AND muc1.uom_code = p_uom_code
1823 AND muc2.uom_code = p_dest_uom_code
1824 AND ROWNUM = 1;
1825
1826 EXCEPTION when NO_DATA_FOUND then
1827 p_conv_found := FALSE;
1828 p_conv_rate := 1.0;
1829 return;
1830 END;
1831
1832 ELSE
1833 BEGIN
1834 SELECT muc.conversion_rate INTO p_conv_rate
1835 FROM mtl_uom_conversions_view muc
1836 WHERE muc.inventory_item_id = p_inventory_item_id
1837 AND muc.primary_uom_code = p_uom_code
1838 AND muc.uom_code = p_dest_uom_code
1839 AND rownum = 1;
1840
1841 EXCEPTION when NO_DATA_FOUND then
1842 --The following alternative was taken from Anuj's snapshot file (jansanch)
1843 BEGIN
1844 SELECT muc.conversion_rate INTO p_conv_rate
1845 FROM mtl_uom_conversions_view muc
1846 WHERE muc.inventory_item_id = p_inventory_item_id
1847 AND muc.primary_uom_code = p_dest_uom_code
1848 AND muc.uom_code = p_uom_code
1849 AND rownum = 1;
1850 EXCEPTION when NO_DATA_FOUND then
1851 p_conv_found := FALSE;
1852 p_conv_rate := 1.0;
1853 return;
1854 END;
1855 END;
1856 END IF;
1857
1858 p_conv_found := TRUE;
1859
1860 END;
1861
1862
1863 PROCEDURE testConv(source varchar2, dest varchar2) IS
1864 p_conv_found BOOLEAN;
1865 p_conv_rate NUMBER;
1866 BEGIN
1867
1868 GET_UOM_CONVERSION_RATES(source,dest,0,p_conv_found,p_conv_rate);
1869 IF p_conv_found = TRUE THEN
1870 debug_output('found, conv_rate=' || TO_CHAR(p_conv_rate));
1871 ELSE
1872 debug_output('not found');
1873 END IF;
1874 END testConv;
1875
1876 --returns thresold (first looks for user value, then for global value if no user value is defined)
1877 --if no entry appears in mst_excep_preferences returns -999 (this is equivalent to a disabled exception)
1878 FUNCTION getExceptionThreshold (exceptionType NUMBER, userIdIn NUMBER) RETURN NUMBER IS
1879 total NUMBER;
1880 threshold NUMBER;
1881 BEGIN
1882 --get the threshold from mst_excep_preferences
1883 BEGIN
1884 SELECT nvl(threshold_value,0) INTO threshold
1885 FROM mst_excep_preferences
1886 WHERE exception_type = exceptionType
1887 and user_id = -9999
1888 and ROWNUM = 1;
1889 EXCEPTION
1890 WHEN NO_DATA_FOUND THEN
1891 --exception is disabled
1892 threshold := -999;
1893 END;
1894 RETURN threshold;
1895 END getExceptionThreshold;
1896
1897
1898 PROCEDURE initializeGlobalVariables(plan_idIn IN NUMBER, user_idIn IN NUMBER) IS
1899 p_conv_found BOOLEAN;
1900 convRateTime NUMBER;
1901 convRateDistance NUMBER;
1902 lAvgHwayDist NUMBER;
1903 lDisConstant NUMBER;
1904 lDistanceUomCode VARCHAR2(3);
1905 lTimeUomCode VARCHAR2(3);
1906 BEGIN
1907 tp_plan_id := plan_idIn;
1908
1909 --get TP UOMs
1910 SELECT p.TIME_UOM, p.DISTANCE_UOM, p.dimension_uom, p.volume_uom
1911 INTO tp_time_uom, tp_distance_uom, tp_dimension_uom, tp_volume_uom
1912 FROM MST_PLANS p
1913 WHERE plan_id = plan_idIn;
1914 debug_output('tp_distance_uom=' || tp_distance_uom || ', tp_time_uom=' || tp_time_uom);
1915 debug_output('tp_dimension_uom=' || tp_dimension_uom || ', tp_volume_uom=' || tp_volume_uom);
1916
1917 --Code adapted from snapshot "/mstdev/mst/11.5/src/snap/mstsplno.ppc" to obtain avg_driving_speed and distance_scale_factor
1918 BEGIN
1919 SELECT nvl(wgp.AVG_HWAY_SPEED,'-99') , nvl(wgp.TL_HWAY_DIS_EMP_CONSTANT,'-99'), wgp.GU_DISTANCE_UOM, wgp.TIME_UOM
1920 INTO lAvgHwayDist, lDisConstant, lDistanceUomCode, lTimeUomCode
1921 FROM MST_PARAMETERS mgp, WSH_GLOBAL_PARAMETERS wgp, MST_PARAMETERS mup
1922 WHERE mgp.user_id = -9999
1923 and mup.user_id = user_idIn
1924 and rownum = 1;
1925 EXCEPTION
1926 WHEN NO_DATA_FOUND THEN
1927 BEGIN
1928 debug_output('first select gave no results in parameters query');
1929 SELECT nvl(wgp.AVG_HWAY_SPEED,'-99'), nvl(wgp.TL_HWAY_DIS_EMP_CONSTANT,'-99'), wgp.GU_DISTANCE_UOM, wgp.TIME_UOM
1930 INTO lAvgHwayDist, lDisConstant, lDistanceUomCode, lTimeUomCode
1931 FROM MST_PARAMETERS mgp, WSH_GLOBAL_PARAMETERS wgp
1932 WHERE mgp.user_id = -9999
1933 and rownum = 1;
1934 EXCEPTION
1935 WHEN NO_DATA_FOUND THEN
1936 debug_output('second select gave no results in parameters query, assuming defaults');
1937 lAvgHwayDist := 59;
1938 lDisConstant := 0;
1939 lTimeUomCode := tp_time_uom;
1940 lDistanceUomCode := tp_distance_uom;
1941 END;
1942 END;
1943
1944 debug_output('AvgHwayDist= ' || lAvgHwayDist || ',lDistanceUomCode=<' || lDistanceUomCode || '>,lTimeUomCode=' || lTimeUomCode || ', lDisConstant=' || lDisConstant);
1945 IF lDistanceUomCode <> tp_distance_uom AND lDistanceUomCode <> '-99' THEN
1946 GET_UOM_CONVERSION_RATES(lDistanceUomCode, tp_distance_uom, 0, p_conv_found, convRateDistance);
1947 IF p_conv_found = false THEN
1948 convRateDistance := 1;
1949 END IF;
1950 ELSE
1951 convRateDistance := 1;
1952 END IF;
1953 debug_output('convRateDistance=' || convRateDistance);
1954
1955 IF lTimeUomCode <> tp_time_uom AND lTimeUomCode <> '-99' THEN
1956 GET_UOM_CONVERSION_RATES(lTimeUomCode, tp_time_uom, 0, p_conv_found, convRateTime);
1957 IF p_conv_found = false THEN
1958 convRateTime := 1;
1959 END IF;
1960 ELSE
1961 convRateTime := 1;
1962 END IF;
1963 debug_output('convRateTime=' || convRateTime);
1964
1965 --set global variables for later use
1966 avgDrvSpeed := (lAvgHwayDist * convRateDistance)/convRateTime;
1967 distScaleFactor := lDisConstant;
1968
1969 debug_output('avgDrvSpeed = ' || avgDrvSpeed);
1970 debug_output('distScaleFactor = ' || distScaleFactor);
1971 debug_output('----');
1972 END initializeGlobalVariables;
1973
1974 --Run all the exceptions in the audit report (each exception checks if it is enabled or not)
1975 PROCEDURE runAuditReport(errbuf OUT NOCOPY VARCHAR2,
1976 retcode OUT NOCOPY NUMBER,
1977 plan_idIn IN NUMBER,
1978 snapshotIsCaller IN NUMBER DEFAULT 2) IS
1979 planRows NUMBER;
1980 user_id NUMBER(15);
1981
1982 mst_state NUMBER;
1983 mst_program NUMBER;
1984 mst_request_id NUMBER;
1985 own_request_id NUMBER;
1986 BEGIN
1987 msg_seq_num := 0;
1988
1989 SELECT count(*) into planRows
1990 FROM mst_plans
1991 WHERE plan_id = plan_idIn;
1992
1993 IF planRows <> 1 THEN
1994 SELECT 'no entry or too many entries in mst plans for plan ' || TO_CHAR(plan_idIn)
1995 INTO errbuf
1996 FROM dual;
1997 retcode := 2;
1998 RETURN;
1999 END IF;
2000
2001
2002 --Obtain data from mst_plans
2003 SELECT created_by, state, program, request_id
2004 INTO user_id, mst_state, mst_program, mst_request_id
2005 FROM mst_plans
2006 WHERE plan_id = plan_idIn;
2007
2008 debug_output('MST_PLANS.state=' || mst_state);
2009 debug_output('MST_PLANS.program=' || mst_program);
2010 debug_output('MST_PLANS.request_id=' || mst_request_id);
2011 debug_output('snapshotIsCaller=' || snapshotIsCaller);
2012
2013 --Determine if audit report can be run. Need to check if another instance of engine/snapshot/audit is running
2014 --Obtain own request id
2015 own_request_id := fnd_profile.value('CONC_REQUEST_ID');
2016 IF own_request_id IS NULL THEN
2017 debug_output('No CONC_REQUEST_ID present');
2018 END IF;
2019 debug_output('own_request_id=' || own_request_id);
2020
2021
2022 IF mst_request_id <> own_request_id THEN
2023 --Prevent audit report from running when data has not been snapshoted
2024 IF mst_state IS NULL THEN
2025 debug_output('Data needs to be snapshoted before running audit report');
2026 SELECT 'Data needs to be snapshoted before running audit report'
2027 INTO errbuf
2028 FROM dual;
2029 retcode := 3;
2030 RETURN;
2031 END IF;
2032 ELSE
2033 debug_output('mst_request_id == owd_request_id');
2034 END IF;
2035
2036 /*
2037 IF mst_request_id <> own_request_id THEN
2038 --IF state <> snapshot done AND state <> optimization done THEN exit
2039 IF mst_state <> 1 AND mst_state <> 2 THEN
2040 debug_output('Audit report cannot be run in current state, state = ' || TO_CHAR(mst_state));
2041 SELECT 'Audit report cannot be run in current state, state = ' || TO_CHAR(mst_state)
2042 INTO errbuf
2043 FROM dual;
2044 retcode := 3;
2045 RETURN;
2046 END IF;
2047 ELSE
2048 debug_output('mst_request_id == owd_request_id');
2049 END IF;
2050 */
2051
2052 --update MST_PLANS signaling successful start of audit report
2053 UPDATE MST_PLANS
2054 SET state=4, program=8, request_id = own_request_id
2055 WHERE plan_id = plan_idIn;
2056 COMMIT;
2057
2058 debug_output('Entering initializeGlobalVariables');
2059 initializeGlobalVariables(plan_idIn, user_id);
2060 debug_output('Done with initializeGlobalVariables');
2061
2062 debug_output('----');
2063
2064 debug_output('Entering MissingLatLongCoordExcptn');
2065 MissingLatLongCoordExcptn(plan_idIn, user_id);
2066 debug_output('Done with MissingLatLongCoordExcptn');
2067
2068 debug_output('----');
2069
2070 debug_output('Entering MissingDistanceDataExcptn');
2071 MissingDistanceDataExcptn(plan_idIn, user_id);
2072 debug_output('Done with MissingDistanceDataExcptn');
2073
2074 debug_output('----');
2075
2076 debug_output('Entering DimensionViolForPieceExcptn');
2077 DimensionViolForPieceExcptn(plan_idIn,user_id);
2078 debug_output('Done with DimensionViolForPieceExcptn');
2079
2080 debug_output('----');
2081
2082 debug_output('Entering DL_with_zero_values');
2083 DL_with_zero_values(plan_idIn,user_id);
2084 debug_output('Done with DL_with_zero_values');
2085
2086 debug_output('----');
2087
2088 debug_output('Entering WgtVolViolForPieceExcptn');
2089 WgtVolViolForPieceExcptn(plan_idIn,user_id);
2090 debug_output('Done with WgtVolViolForPieceExcptn');
2091
2092 debug_output('----');
2093
2094 debug_output('Entering WgtVolViolForDLExcptn');
2095 WgtVolViolForDLExcptn(plan_idIn,user_id);
2096 debug_output('Done with WgtVolViolForDLExcptn');
2097
2098 debug_output('----');
2099
2100 debug_output('Entering WgtVolViolForFirmDelivExcptn');
2101 WgtVolViolForFirmDelivExcptn(plan_idIn,user_id);
2102 debug_output('Done with WgtVolViolForFirmDelivExcptn');
2103
2104 debug_output('----');
2105
2106 debug_output('Entering InsufficientIntransitTimeExptn');
2107 InsufficientIntransitTimeExptn(plan_idIn,user_id);
2108 debug_output('Done with InsufficientIntransitTimeExptn');
2109
2110 debug_output('----');
2111
2112 debug_output('Entering PastDueOrdersExptn');
2113 PastDueOrdersExptn(plan_idIn,user_id);
2114 debug_output('Done with PastDueOrdersExptn');
2115
2116 debug_output('----');
2117
2118 debug_output('Entering FacCalViolForPickUpExptn');
2119 FacCalViolForPickUpExptn(plan_idIn,user_id);
2120 debug_output('Done with FacCalViolForPickUpExptn');
2121
2122 debug_output('----');
2123
2124 debug_output('Entering FacCalViolForDeliveryExptn');
2125 FacCalViolForDeliveryExptn(plan_idIn,user_id);
2126 debug_output('Done with FacCalViolForDeliveryExptn');
2127
2128 commit;
2129
2130 --if snapshotIsCaller == 1 then update state to 1
2131 IF snapshotIsCaller = 1 THEN
2132 mst_state := 1;
2133 END IF;
2134
2135 --update MST_PLANS signaling successful end of audit report
2136 UPDATE MST_PLANS
2137 SET state=mst_state, program=NULL
2138 WHERE plan_id = plan_idIn;
2139 debug_output('Update MST_PLANS.program = NULL');
2140 debug_output('Update MST_PLANS.state = ' || mst_state);
2141 debug_output('Audit report finished successfully, exiting now...');
2142 commit;
2143
2144 errbuf := NULL;
2145 retcode := 0;
2146 END runAuditReport;
2147
2148
2149 PROCEDURE debug_output(p_str in varchar2) IS
2150 var NUMBER;
2151 BEGIN
2152 -- fnd_file.put_line( FND_FILE.LOG, p_str);
2153 --dbms_output.put_line(p_str);
2154
2155 /*
2156 Use following to create table for debug:
2157 create table mst_audit_rep_excp_debug(pk number, plan_id number,message_seq_num number, date_message date,message varchar2(255));
2158 create sequence mst_audit_rep_excp_debug_s;
2159 Then uncomment code to insert messages to that table
2160 */
2161
2162 /*
2163 insert into mst_audit_rep_excp_debug(pk, plan_id,message_seq_num, date_message,message)
2164 values (mst_audit_rep_excp_debug_s.nextval, tp_plan_id, msg_seq_num, sysdate, p_str);
2165 msg_seq_num := msg_seq_num + 1;
2166 commit;
2167 */
2168 var := 0;
2169 END debug_output;
2170
2171 END MST_AUDIT_REP_EXCP;
[Home] [Help]