[Home] [Help]
PACKAGE BODY: APPS.MSC_X_NETTING2_PKG
Source
1 PACKAGE BODY MSC_X_NETTING2_PKG AS
2 /* $Header: MSCXEX2B.pls 120.5 2007/07/31 13:14:21 vsiyer ship $ */
3
4
5
6 --The cursor below selects the buckets generated for company/org combination
7 CURSOR time_bkts IS
8 SELECT trunc(b.bkt_start_date), trunc(b.bkt_end_date), b.bucket_type
9 FROM msc_plan_buckets b
10 WHERE b.plan_id = msc_x_netting_pkg.G_PLAN_ID;
11
12 --================================================================
13 --Group 2: Material Shortage
14 --================================================================
15 -------------------------------------------------------------------------------------
16 -- 2.1 Customer's demand within time bucket is greater than your supply: exception_5
17 -------------------------------------------------------------------------------------
18 --The p_company_id is the supplier and below exception cursor is for customer
19 --facing the exception
20
21 CURSOR exception_5 (p_refresh_number IN Number) IS
22 select sd.publisher_id,
23 sd.publisher_site_id,
24 sd.supplier_id,
25 sd.supplier_site_id,
26 sd.customer_id,
27 sd.customer_site_id,
28 nvl(sd.base_item_id,sd.inventory_item_id),
29 bkt.bucket_index,
30 bkt.bkt_start_date,
31 bkt.bkt_end_date,
32 msc_x_netting_pkg.SUPPLY_COMMIT,
33 sd.publisher_name,
34 sd.publisher_site_name,
35 sd.supplier_name,
36 sd.supplier_site_name,
37 sd.customer_name,
38 sd.customer_site_name,
39 null,
40 null,
41 null,
42 null,
43 nvl(sum(sd.primary_quantity),0),
44 nvl(sum(sd.tp_quantity),0),
45 nvl(sum(sd.quantity),0),
46 nvl(max(sd.last_refresh_number),0)
47 from msc_sup_dem_entries sd,
48 msc_cp_plan_buckets bkt
49 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
50 and sd.publisher_id = bkt.supplier_id
51 and sd.publisher_site_id = bkt.supplier_site_id
52 and sd.customer_id = bkt.customer_id
53 and sd.customer_site_id = bkt.customer_site_id
54 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
55 and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
56 and sd.plan_id = bkt.plan_id
57 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
58 group by sd.publisher_id,
59 sd.publisher_site_id,
60 sd.supplier_id,
61 sd.supplier_site_id,
62 sd.customer_id,
63 sd.customer_site_id,
64 nvl(sd.base_item_id,sd.inventory_item_id),
65 bkt.bucket_index,
66 bkt.bkt_start_date,
67 bkt.bkt_end_date,
68 sd.publisher_name,
69 sd.publisher_site_name,
70 sd.supplier_name,
71 sd.supplier_site_name,
72 sd.customer_name,
73 sd.customer_site_name
74 UNION ALL
75 select sd.supplier_id,
76 sd.supplier_site_id,
77 sd.supplier_id,
78 sd.supplier_site_id,
79 sd.publisher_id,
80 sd.publisher_site_id,
81 nvl(sd.base_item_id,sd.inventory_item_id),
82 bkt.bucket_index,
83 bkt.bkt_start_date,
84 bkt.bkt_end_date,
85 msc_x_netting_pkg.ORDER_FORECAST,
86 sd.supplier_name,
87 sd.supplier_site_name,
88 sd.supplier_name,
89 sd.supplier_site_name,
90 sd.publisher_name,
91 sd.publisher_site_name,
92 null,
93 null,
94 null,
95 null,
96 nvl(sum(sd.primary_quantity),0),
97 nvl(sum(sd.tp_quantity),0),
98 nvl(sum(sd.quantity),0),
99 nvl(max(sd.last_refresh_number),0)
100 from msc_sup_dem_entries sd,
101 msc_cp_plan_buckets bkt
102 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
103 and sd.publisher_id = bkt.customer_id
104 and sd.publisher_site_id = bkt.customer_site_id
105 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
106 and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
107 and sd.supplier_id = bkt.supplier_id
108 and sd.supplier_site_id = bkt.supplier_site_id
109 and sd.plan_id = bkt.plan_id
110 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
111 group by sd.supplier_id,
112 sd.supplier_site_id,
113 sd.supplier_id,
114 sd.supplier_site_id,
115 sd.publisher_id,
116 sd.publisher_site_id,
117 nvl(sd.base_item_id,sd.inventory_item_id),
118 bkt.bucket_index,
119 bkt.bkt_start_date,
120 bkt.bkt_end_date,
121 sd.supplier_name,
122 sd.supplier_site_name,
123 sd.supplier_name,
124 sd.supplier_site_name,
125 sd.publisher_name,
126 sd.publisher_site_name
127 order by 1,2,3,4,5,6,7,8,9;
128
129 -----------------------------------------------------------------------------------------
130 --2.2 Supplier's supply within time bucket is less than your demand (SP) : exception_6
131 ------------------------------------------------------------------------------------------
132 --The p_company_id is customer and below exception cursor is for supplier
133 --facing the exception
134
135 CURSOR exception_6(p_refresh_number In Number) IS
136 select sd.publisher_id,
137 sd.publisher_site_id,
138 sd.customer_id,
139 sd.customer_site_id,
140 sd.supplier_id,
141 sd.supplier_site_id,
142 nvl(sd.base_item_id,sd.inventory_item_id),
143 bkt.bucket_index,
144 bkt.bkt_start_date,
145 bkt.bkt_end_date,
146 msc_x_netting_pkg.ORDER_FORECAST,
147 sd.publisher_name,
148 sd.publisher_site_name,
149 sd.customer_name,
150 sd.customer_site_name,
151 sd.supplier_name,
152 sd.supplier_site_name,
153 null,
154 null,
155 null,
156 null,
157 nvl(sum(sd.primary_quantity),0),
158 nvl(sum(sd.tp_quantity),0),
159 nvl(sum(sd.quantity),0),
160 nvl(max(sd.last_refresh_number),0)
161 from msc_sup_dem_entries sd,
162 msc_cp_plan_buckets bkt
163 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
164 and sd.publisher_id = bkt.customer_id
165 and sd.publisher_site_id = bkt.customer_site_id
166 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
167 and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
168 and sd.supplier_id = bkt.supplier_id
169 and sd.supplier_site_id = bkt.supplier_site_id
170 and sd.plan_id = bkt.plan_id
171 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
172 group by sd.publisher_id,
173 sd.publisher_site_id,
174 sd.customer_id,
175 sd.customer_site_id,
176 sd.supplier_id,
177 sd.supplier_site_id,
178 nvl(sd.base_item_id,sd.inventory_item_id),
179 bkt.bucket_index,
180 bkt.bkt_start_date,
181 bkt.bkt_end_date,
182 sd.publisher_name,
183 sd.publisher_site_name,
184 sd.customer_name,
185 sd.customer_site_name,
186 sd.supplier_name,
187 sd.supplier_site_name
188 UNION ALL
189 select sd.customer_id,
190 sd.customer_site_id,
191 sd.customer_id,
192 sd.customer_site_id,
193 sd.publisher_id,
194 sd.publisher_site_id,
195 nvl(sd.base_item_id,sd.inventory_item_id),
196 bkt.bucket_index,
197 bkt.bkt_start_date,
198 bkt.bkt_end_date,
199 msc_x_netting_pkg.SUPPLY_COMMIT,
200 sd.customer_name,
201 sd.customer_site_name,
202 sd.customer_name,
203 sd.customer_site_name,
204 sd.publisher_name,
205 sd.publisher_site_name,
206 null,
207 null,
208 null,
209 null,
210 nvl(sum(sd.primary_quantity),0),
211 nvl(sum(sd.tp_quantity),0),
212 nvl(sum(sd.quantity),0),
213 nvl(max(sd.last_refresh_number),0)
214 from msc_sup_dem_entries sd,
215 msc_cp_plan_buckets bkt
216 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
217 and sd.publisher_id = bkt.supplier_id
218 and sd.publisher_site_id = bkt.supplier_site_id
219 and sd.customer_id = bkt.customer_id
220 and sd.customer_site_id = bkt.customer_site_id
221 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
222 and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
223 and sd.plan_id = bkt.plan_id
224 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
225 group by sd.customer_id,
226 sd.customer_site_id,
227 sd.customer_id,
228 sd.customer_site_id,
229 sd.publisher_id,
230 sd.publisher_site_id,
231 nvl(sd.base_item_id,sd.inventory_item_id),
232 bkt.bucket_index,
233 bkt.bkt_start_date,
234 bkt.bkt_end_date,
235 sd.customer_name,
236 sd.customer_site_name,
237 sd.customer_name,
238 sd.customer_site_name,
239 sd.publisher_name,
240 sd.publisher_site_name
241 order by 1,2,3,4,5,6,7,8,9;
242
243
244 ------------------------------------------------------------------------------------------
245 -- group the order forecast and supply commit together and join to msc_plan_buckets--
246 -- this will improve the performace.
247 ------------------------------------------------------------------------------------------
248
249 cursor get_of_sc(p_customer_id IN NUMBER,
250 p_customer_site_id IN Number,
251 p_supplier_id IN Number,
252 p_supplier_site_id IN Number,
253 p_item_id IN Number,
254 p_cutoff_ref_num IN Number) IS
255 select nvl(sum(sd.primary_quantity),0),
256 nvl(sum(sd.tp_quantity),0),
257 nvl(sum(sd.quantity),0),
258 bkt.bucket_index,
259 bkt.bkt_start_date,
260 bkt.bkt_end_date,
261 msc_x_netting_pkg.ORDER_FORECAST
262 from msc_sup_dem_entries sd,
263 msc_plan_buckets bkt
264 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
265 and sd.publisher_id = p_customer_id
266 and sd.publisher_site_id = p_customer_site_id
267 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
268 and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
269 and sd.supplier_id = p_supplier_id
270 and sd.supplier_site_id = p_supplier_site_id
271 and sd.plan_id = bkt.plan_id
272 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
273 and sd.last_refresh_number <= p_cutoff_ref_num
274 group by bkt.bucket_index, bkt.bkt_start_date, bkt.bkt_end_date
275 UNION ALL
276 select nvl(sum(sd.primary_quantity),0),
277 nvl(sum(sd.tp_quantity),0),
278 nvl(sum(sd.quantity),0),
279 bkt.bucket_index,
280 bkt.bkt_start_date,
281 bkt.bkt_end_date,
282 msc_x_netting_pkg.SUPPLY_COMMIT
283 from msc_sup_dem_entries sd,
284 msc_plan_buckets bkt
285 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
286 and sd.publisher_id = p_supplier_id
287 and sd.publisher_site_id = p_supplier_site_id
288 and sd.customer_id = p_customer_id
289 and sd.customer_site_id = p_customer_site_id
290 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
291 and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
292 and sd.plan_id = bkt.plan_id
293 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
294 and sd.last_refresh_number <= p_cutoff_ref_num
295 group by bkt.bucket_index, bkt.bkt_start_date, bkt.bkt_end_date
296 order by 4,5,6;
297
298
299 -------------------------------------------------------------------------------------
300 --2.3 Fulfillment quantity shortfall for your customers purchase order : exception_7
301 ------------------------------------------------------------------------------------
302 CURSOR exception_7(p_refresh_number IN Number) IS
303 SELECT distinct sd1.transaction_id,
304 sd1.publisher_id,
305 sd1.publisher_name,
306 sd1.publisher_site_id,
307 sd1.publisher_site_name,
308 sd1.inventory_item_id,
309 sd1.item_name,
310 sd1.item_description,
311 sd1.supplier_item_name,
312 sd1.supplier_item_description,
313 sd1.key_date,
314 sd1.ship_date,
315 sd1.receipt_date,
316 sd1.quantity,
317 sd1.primary_quantity,
318 sd1.tp_quantity,
319 sd1.order_number,
320 sd1.release_number,
321 sd1.line_number,
322 sd1.customer_id,
323 sd1.customer_name,
324 sd1.customer_site_id,
325 sd1.customer_site_name,
326 sd1.customer_item_name,
327 sd1.customer_item_description,
328 sd1.creation_date,
329 sd1.last_refresh_number,
330 sd2.transaction_id,
331 sd2.key_date,
332 sd2.ship_date,
333 sd2.receipt_date,
334 sd2.quantity,
335 sd2.primary_quantity,
336 sd2.tp_quantity,
337 sd2.order_number,
338 sd2.release_number,
339 sd2.line_number,
340 sd2.supplier_id,
341 sd2.supplier_name,
342 sd2.supplier_site_id,
343 sd2.supplier_site_name,
344 sd2.creation_date,
345 sd2.last_refresh_number
346 FROM msc_sup_dem_entries sd1,
347 msc_sup_dem_entries sd2
348 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
349 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
350 AND sd2.plan_id = sd1.plan_id
351 AND sd2.inventory_item_id = sd1.inventory_item_id
352 AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
353 AND sd2.supplier_id = sd1.publisher_id
354 AND sd2.supplier_site_id = sd1.publisher_site_id
355 AND sd2.publisher_id = sd1.customer_id
356 AND sd2.publisher_site_id = sd1.customer_site_id
357 AND sd1.end_order_number = sd2.order_number
358 AND nvl(sd1.end_order_rel_number,-1) =
359 nvl(sd2.release_number,-1)
360 AND nvl(sd1.end_order_line_number,-1) =
361 nvl(sd2.line_number,-1)
362 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
363 order by sd1.publisher_id, sd1.publisher_site_id,
364 sd1.customer_id, sd1.customer_site_id, sd1.inventory_item_id,
365 sd2.order_number, sd2.release_number, sd2.line_number, sd1.key_date desc;
366
367 -------------------------------------------------------------------------------------------
368 --2.4 Fulfillment quantity shortfall from your supplier for your purchase order : exception_8
369 --------------------------------------------------------------------------------------------
370 CURSOR exception_8(p_refresh_number IN Number) IS
371 SELECT distinct sd1.transaction_id,
372 sd1.publisher_id,
373 sd1.publisher_name,
374 sd1.publisher_site_id,
375 sd1.publisher_site_name,
376 sd1.inventory_item_id,
377 sd1.item_name,
378 sd1.item_description,
379 sd2.customer_item_name,
380 sd2.customer_item_description,
381 sd1.key_date,
382 sd1.ship_date,
383 sd1.receipt_date,
384 sd1.quantity,
385 sd1.primary_quantity,
386 sd1.tp_quantity,
387 sd1.order_number,
388 sd1.release_number,
389 sd1.line_number,
390 sd1.supplier_id,
391 sd1.supplier_name,
392 sd1.supplier_site_id,
393 sd1.supplier_site_name,
394 sd1.supplier_item_name,
395 sd1.supplier_item_description,
396 sd1.creation_date,
397 sd1.last_refresh_number,
398 sd2.transaction_id,
399 sd2.key_date,
400 sd2.ship_date,
401 sd2.receipt_date,
402 sd2.quantity,
403 sd2.primary_quantity,
404 sd2.tp_quantity,
405 sd2.order_number,
406 sd2.release_number,
407 sd2.line_number,
408 sd2.customer_id,
409 sd2.customer_name,
410 sd2.customer_site_id,
411 sd2.customer_site_name,
412 sd2.creation_date,
413 sd2.last_refresh_number
414 FROM msc_sup_dem_entries sd1,
415 msc_sup_dem_entries sd2
416 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
417 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
418 AND sd2.plan_id = sd1.plan_id
419 AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
420 AND sd2.customer_id = sd1.publisher_id
421 AND sd2.customer_site_id = sd1.publisher_site_id
422 AND sd2.publisher_id = sd1.supplier_id
423 AND sd2.publisher_site_id = sd1.supplier_site_id
424 AND sd2.inventory_item_id = sd1.inventory_item_id
425 AND sd1.order_number = sd2.end_order_number
426 AND nvl(sd1.release_number,-1) =
427 nvl(sd2.end_order_rel_number,-1)
428 AND nvl(sd1.line_number,-1) =
429 nvl(sd2.end_order_line_number,-1)
430 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
431 order by sd1.publisher_id, sd1.publisher_site_id,
432 sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id,
433 sd1.order_number, sd1.release_number, sd1.line_number, sd2.key_date desc;
434
435
436 --=================================================================================
437 --GROUP: MATERIAL_EXCESS
438 -------------------------------------------------------------------------------------
439 -- 7.1 Customer's demand within time bucket is less than your supply: exception_25
440 -------------------------------------------------------------------------------------
441 --The p_company_id is the supplier and below exception cursor is for customer
442 --facing the exception
443
444 CURSOR exception_25(p_refresh_number IN Number) IS
445 select sd.publisher_id,
446 sd.publisher_site_id,
447 sd.supplier_id,
448 sd.supplier_site_id,
449 sd.customer_id,
450 sd.customer_site_id,
451 nvl(sd.base_item_id,sd.inventory_item_id),
452 bkt.bucket_index,
453 bkt.bkt_start_date,
454 bkt.bkt_end_date,
455 msc_x_netting_pkg.SUPPLY_COMMIT,
456 sd.publisher_name,
457 sd.publisher_site_name,
458 sd.supplier_name,
459 sd.supplier_site_name,
460 sd.customer_name,
461 sd.customer_site_name,
462 null,
463 null,
464 null,
465 null,
466 nvl(sum(sd.primary_quantity),0),
467 nvl(sum(sd.tp_quantity),0),
468 nvl(sum(sd.quantity),0),
469 nvl(max(sd.last_refresh_number),0)
470 from msc_sup_dem_entries sd,
471 msc_cp_plan_buckets bkt
472 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
473 and sd.publisher_id = bkt.supplier_id
474 and sd.publisher_site_id = bkt.supplier_site_id
475 and sd.customer_id = bkt.customer_id
476 and sd.customer_site_id = bkt.customer_site_id
477 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
478 and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
479 and sd.plan_id = bkt.plan_id
480 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
481 group by sd.publisher_id,
482 sd.publisher_site_id,
483 sd.supplier_id,
484 sd.supplier_site_id,
485 sd.customer_id,
486 sd.customer_site_id,
487 nvl(sd.base_item_id,sd.inventory_item_id),
488 bkt.bucket_index,
489 bkt.bkt_start_date,
490 bkt.bkt_end_date,
491 sd.publisher_name,
492 sd.publisher_site_name,
493 sd.supplier_name,
494 sd.supplier_site_name,
495 sd.customer_name,
496 sd.customer_site_name
497 UNION ALL
498 select sd.supplier_id,
499 sd.supplier_site_id,
500 sd.supplier_id,
501 sd.supplier_site_id,
502 sd.publisher_id,
503 sd.publisher_site_id,
504 nvl(sd.base_item_id,sd.inventory_item_id),
505 bkt.bucket_index,
506 bkt.bkt_start_date,
507 bkt.bkt_end_date,
508 msc_x_netting_pkg.ORDER_FORECAST,
509 sd.supplier_name,
510 sd.supplier_site_name,
511 sd.supplier_name,
512 sd.supplier_site_name,
513 sd.publisher_name,
514 sd.publisher_site_name,
515 null,
516 null,
517 null,
518 null,
519 nvl(sum(sd.primary_quantity),0),
520 nvl(sum(sd.tp_quantity),0),
521 nvl(sum(sd.quantity),0),
522 nvl(max(sd.last_refresh_number),0)
523 from msc_sup_dem_entries sd,
524 msc_cp_plan_buckets bkt
525 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
526 and sd.publisher_id = bkt.customer_id
527 and sd.publisher_site_id = bkt.customer_site_id
528 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
529 and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
530 and sd.supplier_id = bkt.supplier_id
531 and sd.supplier_site_id = bkt.supplier_site_id
532 and sd.plan_id = bkt.plan_id
533 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
534 group by sd.supplier_id,
535 sd.supplier_site_id,
536 sd.supplier_id,
537 sd.supplier_site_id,
538 sd.publisher_id,
539 sd.publisher_site_id,
540 nvl(sd.base_item_id,sd.inventory_item_id),
541 bkt.bucket_index,
542 bkt.bkt_start_date,
543 bkt.bkt_end_date,
544 sd.supplier_name,
545 sd.supplier_site_name,
546 sd.supplier_name,
547 sd.supplier_site_name,
548 sd.publisher_name,
549 sd.publisher_site_name
550 order by 1,2,3,4,5,6,7,8,9;
551
552
553 -----------------------------------------------------------------------------------------
554 --7.2 Supplier's supply within time bucket is greater than your demand (SP) : exception_26
555 ------------------------------------------------------------------------------------------
556 --The p_company_id is customer and below exception cursor is for supplier
557 --facing the exception
558
559 CURSOR exception_26(p_refresh_number In Number) IS
560 select sd.publisher_id,
561 sd.publisher_site_id,
562 sd.customer_id,
563 sd.customer_site_id,
564 sd.supplier_id,
565 sd.supplier_site_id,
566 nvl(sd.base_item_id,sd.inventory_item_id),
567 bkt.bucket_index,
568 bkt.bkt_start_date,
569 bkt.bkt_end_date,
570 msc_x_netting_pkg.ORDER_FORECAST,
571 sd.publisher_name,
572 sd.publisher_site_name,
573 sd.customer_name,
574 sd.customer_site_name,
575 sd.supplier_name,
576 sd.supplier_site_name,
577 null,
578 null,
579 null,
580 null,
581 nvl(sum(sd.primary_quantity),0),
582 nvl(sum(sd.tp_quantity),0),
583 nvl(sum(sd.quantity),0),
584 nvl(max(sd.last_refresh_number),0)
585 from msc_sup_dem_entries sd,
586 msc_cp_plan_buckets bkt
587 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
588 and sd.publisher_id = bkt.customer_id
589 and sd.publisher_site_id = bkt.customer_site_id
590 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
591 and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
592 and sd.supplier_id = bkt.supplier_id
593 and sd.supplier_site_id = bkt.supplier_site_id
594 and sd.plan_id = bkt.plan_id
595 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
596 group by sd.publisher_id,
597 sd.publisher_site_id,
598 sd.customer_id,
599 sd.customer_site_id,
600 sd.supplier_id,
601 sd.supplier_site_id,
602 nvl(sd.base_item_id,sd.inventory_item_id),
603 bkt.bucket_index,
604 bkt.bkt_start_date,
605 bkt.bkt_end_date,
606 sd.publisher_name,
607 sd.publisher_site_name,
608 sd.customer_name,
609 sd.customer_site_name,
610 sd.supplier_name,
611 sd.supplier_site_name
612 UNION ALL
613 select sd.customer_id,
614 sd.customer_site_id,
615 sd.customer_id,
616 sd.customer_site_id,
617 sd.publisher_id,
618 sd.publisher_site_id,
619 nvl(sd.base_item_id,sd.inventory_item_id),
620 bkt.bucket_index,
621 bkt.bkt_start_date,
622 bkt.bkt_end_date,
623 msc_x_netting_pkg.SUPPLY_COMMIT,
624 sd.customer_name,
625 sd.customer_site_name,
626 sd.customer_name,
627 sd.customer_site_name,
628 sd.publisher_name,
629 sd.publisher_site_name,
630 null,
631 null,
632 null,
633 null,
634 nvl(sum(sd.primary_quantity),0),
635 nvl(sum(sd.tp_quantity),0),
636 nvl(sum(sd.quantity),0),
637 nvl(max(sd.last_refresh_number),0)
638 from msc_sup_dem_entries sd,
639 msc_cp_plan_buckets bkt
640 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
641 and sd.publisher_id = bkt.supplier_id
642 and sd.publisher_site_id = bkt.supplier_site_id
643 and sd.customer_id = bkt.customer_id
644 and sd.customer_site_id = bkt.customer_site_id
645 and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
646 and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
647 and sd.plan_id = bkt.plan_id
648 and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
649 group by sd.customer_id,
650 sd.customer_site_id,
651 sd.customer_id,
652 sd.customer_site_id,
653 sd.publisher_id,
654 sd.publisher_site_id,
655 nvl(sd.base_item_id,sd.inventory_item_id),
656 bkt.bucket_index,
657 bkt.bkt_start_date,
658 bkt.bkt_end_date,
659 sd.customer_name,
660 sd.customer_site_name,
661 sd.customer_name,
662 sd.customer_site_name,
663 sd.publisher_name,
664 sd.publisher_site_name
665 order by 1,2,3,4,5,6,7,8,9;
666
667
668
669 -------------------------------------------------------------------------------------
670 --7.3 Fulfillment quantity excess for your customers purchase order : exception_27
671 ------------------------------------------------------------------------------------
672 CURSOR exception_27(p_refresh_number IN Number) IS
673 SELECT distinct sd1.transaction_id,
674 sd1.publisher_id,
675 sd1.publisher_name,
676 sd1.publisher_site_id,
677 sd1.publisher_site_name,
678 sd1.inventory_item_id,
679 sd1.item_name,
680 sd1.item_description,
681 sd2.supplier_item_name,
682 sd2.supplier_item_description,
683 sd1.key_date,
684 sd1.ship_date,
685 sd1.receipt_date,
686 sd1.quantity,
687 sd1.primary_quantity,
688 sd1.tp_quantity,
689 sd1.order_number,
690 sd1.release_number,
691 sd1.line_number,
692 sd1.customer_id,
693 sd1.customer_name,
694 sd1.customer_site_id,
695 sd1.customer_site_name,
696 sd1.customer_item_name,
697 sd1.customer_item_description,
698 sd1.creation_date,
699 sd1.last_refresh_number,
700 sd2.transaction_id,
701 sd2.key_date,
702 sd2.ship_date,
703 sd2.receipt_date,
704 sd2.quantity,
705 sd2.primary_quantity,
706 sd2.tp_quantity,
707 sd2.order_number,
708 sd2.release_number,
709 sd2.line_number,
710 sd2.supplier_id,
711 sd2.supplier_name,
712 sd2.supplier_site_id,
713 sd2.supplier_site_name,
714 sd2.creation_date,
715 sd2.last_refresh_number
716 FROM msc_sup_dem_entries sd1,
717 msc_sup_dem_entries sd2
718 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
719 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
720 AND sd2.plan_id = sd1.plan_id
721 AND sd2.inventory_item_id = sd1.inventory_item_id
722 AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
723 AND sd2.supplier_id = sd1.publisher_id
724 AND sd2.supplier_site_id = sd1.publisher_site_id
725 AND sd2.publisher_id = sd1.customer_id
726 AND sd2.publisher_site_id = sd1.customer_site_id
727 AND sd1.end_order_number = sd2.order_number
728 AND nvl(sd1.end_order_rel_number,-1) =
729 nvl(sd2.release_number,-1)
730 AND nvl(sd1.end_order_line_number,-1) =
731 nvl(sd2.line_number,-1)
732 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
733 order by sd1.publisher_id, sd1.publisher_site_id,
734 sd1.customer_id, sd1.customer_site_id, sd1.inventory_item_id,
735 sd2.order_number, sd2.release_number, sd2.line_number, sd1.key_date desc;
736
737 -------------------------------------------------------------------------------------------
738 --7.4 Fulfillment quanitty excess from your supplier for your purchase order : exception_28
739 --------------------------------------------------------------------------------------------
740 CURSOR exception_28(p_refresh_number IN Number) IS
741 SELECT distinct sd1.transaction_id,
742 sd1.publisher_id,
743 sd1.publisher_name,
744 sd1.publisher_site_id,
745 sd1.publisher_site_name,
746 sd1.inventory_item_id,
747 sd1.item_name,
748 sd1.item_description,
749 sd2.customer_item_name,
750 sd2.customer_item_description,
751 sd1.key_date,
752 sd1.ship_date,
753 sd1.receipt_date,
754 sd1.quantity,
755 sd1.primary_quantity,
756 sd1.tp_quantity,
757 sd1.order_number,
758 sd1.release_number,
759 sd1.line_number,
760 sd1.supplier_id,
761 sd1.supplier_name,
762 sd1.supplier_site_id,
763 sd1.supplier_site_name,
764 sd1.supplier_item_name,
765 sd1.supplier_item_description,
766 sd1.creation_date,
767 sd1.last_refresh_number,
768 sd2.transaction_id,
769 sd2.key_date,
770 sd2.ship_date,
771 sd2.receipt_date,
772 sd2.quantity,
773 sd2.primary_quantity,
774 sd2.tp_quantity,
775 sd2.order_number,
776 sd2.release_number,
777 sd2.line_number,
778 sd2.customer_id,
779 sd2.customer_name,
780 sd2.customer_site_id,
781 sd2.customer_site_name,
782 sd2.creation_date,
783 sd2.last_refresh_number
784 FROM msc_sup_dem_entries sd1,
785 msc_sup_dem_entries sd2
786 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
787 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
788 AND sd2.plan_id = sd1.plan_id
789 AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
790 AND sd2.customer_id = sd1.publisher_id
791 AND sd2.customer_site_id = sd1.publisher_site_id
792 AND sd2.publisher_id = sd1.supplier_id
793 AND sd2.publisher_site_id = sd1.supplier_site_id
794 AND sd2.inventory_item_id = sd1.inventory_item_id
795 AND sd1.order_number = sd2.end_order_number
796 AND nvl(sd1.release_number,-1) =
797 nvl(sd2.end_order_rel_number,-1)
798 AND nvl(sd1.line_number,-1) =
799 nvl(sd2.end_order_line_number,-1)
800 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
801 order by sd1.publisher_id, sd1.publisher_site_id,
802 sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id,
803 sd1.order_number, sd1.release_number, sd1.line_number, sd2.key_date desc;
804
805 --======================================================================
806 --COMPUTE_MATERIAL_SHORTAGE (supply planning)
807 --======================================================================
808 PROCEDURE COMPUTE_MATERIAL_SHORTAGE ( p_refresh_number IN Number,
809 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
810 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
811 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
812 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
813 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
814 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
815 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
816 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
817 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
818 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
819 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
820 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
821 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
822 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
823 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
824 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
825 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
826 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
827 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
828 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
829 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
830 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
831 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
832 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
833 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
834 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
835 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
836 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
837 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
838 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
839 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
840 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
841 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
842 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
843 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
844 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
845 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
846 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
847 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
848 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
849 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
850 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
851 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
852 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
853 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
854 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
855 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
856 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
857 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
858 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
859 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
860 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
861 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
862 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
863 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
864 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
865 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
866
867
868
869 b_trx_id1 msc_x_netting_pkg.number_arr;
870 b_trx_id2 msc_x_netting_pkg.number_arr;
871 b_publisher_id msc_x_netting_pkg.number_arr;
872 b_publisher_site_id msc_x_netting_pkg.number_arr;
873 b_item_id msc_x_netting_pkg.number_arr;
874 b_po_qty msc_x_netting_pkg.number_arr;
875 b_so_qty msc_x_netting_pkg.number_arr;
876 b_tp_po_qty msc_x_netting_pkg.number_arr;
877 b_tp_so_qty msc_x_netting_pkg.number_arr;
878 b_posting_po_qty msc_x_netting_pkg.number_arr;
879 b_posting_so_qty msc_x_netting_pkg.number_arr;
880 b_customer_id msc_x_netting_pkg.number_arr;
881 b_customer_site_id msc_x_netting_pkg.number_arr;
882 b_supplier_id msc_x_netting_pkg.number_arr;
883 b_supplier_site_id msc_x_netting_pkg.number_arr;
884 b_po_last_refnum msc_x_netting_pkg.number_arr;
885 b_so_last_refnum msc_x_netting_pkg.number_arr;
886 b_po_key_date msc_x_netting_pkg.date_arr;
887 b_so_key_date msc_x_netting_pkg.date_arr;
888 b_po_receipt_date msc_x_netting_pkg.date_arr;
889 b_so_receipt_date msc_x_netting_pkg.date_arr;
890 b_po_ship_date msc_x_netting_pkg.date_arr;
891 b_so_ship_date msc_x_netting_pkg.date_arr;
892 b_po_creation_date msc_x_netting_pkg.date_arr;
893 b_so_creation_date msc_x_netting_pkg.date_arr;
894 b_item_name msc_x_netting_pkg.itemnameList;
895 b_item_desc msc_x_netting_pkg.itemdescList;
896 b_publisher_name msc_x_netting_pkg.publisherList;
897 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
898 b_supplier_name msc_x_netting_pkg.supplierList;
899 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
900 b_supplier_item_name msc_x_netting_pkg.itemnameList;
901 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
902 b_customer_name msc_x_netting_pkg.customerList;
903 b_customer_site_name msc_x_netting_pkg.custsiteList;
904 b_customer_item_name msc_x_netting_pkg.itemnameList;
905 b_customer_item_desc msc_x_netting_pkg.itemdescList;
906 b_order_number msc_x_netting_pkg.ordernumberList;
907 b_release_number msc_x_netting_pkg.releasenumList;
908 b_line_number msc_x_netting_pkg.linenumList;
909 b_end_order_number msc_x_netting_pkg.ordernumberList;
910 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
911 b_end_order_line_number msc_x_netting_pkg.linenumList;
912 b_refresh_number msc_x_netting_pkg.number_arr;
913
914 l_start_date Date;
915 l_end_date Date;
916 l_bucket_type Number;
917 l_total_demand Number := 0;
918 l_tp_total_demand Number := 0;
919 l_total_supply Number := 0;
920 l_tp_total_supply Number := 0;
921 l_total_qty Number := 0;
922 l_exception_type Number;
923 l_exception_group Number;
924 l_generate_complement Boolean;
925 l_updated Number;
926 l_complement_threshold Number;
927 l_cutoff_ref_num Number;
928 l_threshold1 Number;
929 l_threshold2 Number;
930 l_exception_type_name fnd_lookup_values.meaning%type;
931 l_exception_group_name fnd_lookup_values.meaning%type;
932 l_posting_total_demand Number := 0;
933 l_posting_total_supply Number := 0;
934 l_sum Number := 0;
935 l_item_desc msc_sup_dem_entries.item_description%type;
936 l_shipping_control Number;
937 l_exception_basis msc_x_exception_details.exception_basis%type;
938
939 l_last_so_trx_id Number;
940 l_receipt_date Date;
941 l_ship_date Date;
942 l_order_number msc_sup_dem_entries.order_number%type;
943 l_line_number msc_sup_dem_entries.line_number%type;
944 l_release_number msc_sup_dem_entries.release_number%type;
945 l_item_name msc_sup_dem_entries.item_name%type;
946 l_customer_item_name msc_sup_dem_entries.customer_item_name%type;
947 l_supplier_item_name msc_sup_dem_entries.supplier_item_name%type;
948 l_publisher_name msc_sup_dem_entries.publisher_name%type;
949 l_publisher_site_name msc_sup_dem_entries.publisher_site_name%type;
950 l_supplier_name msc_sup_dem_entries.supplier_name%type;
951 l_supplier_site_name msc_sup_dem_entries.supplier_site_name%type;
952 l_customer_name msc_sup_dem_entries.customer_name%type;
953 l_customer_site_name msc_sup_dem_entries.customer_site_name%type;
954 l_publisher_id Number;
955 l_publisher_site_id Number;
956 l_customer_id Number;
957 l_customer_site_id Number;
958 l_supplier_id Number;
959 l_supplier_site_id Number;
960 l_item_id Number;
961
962 l_pair Number:= 0;
963 l_insert Number := 0;
964 l_inserted_record Number := 0;
965
966
967 b_bucket_index msc_x_netting_pkg.number_arr;
968 b_bkt_start_date msc_x_netting_pkg.date_arr;
969 b_bkt_end_date msc_x_netting_pkg.date_arr;
970 b_order_type msc_x_netting_pkg.number_arr;
971 b_total_quantity msc_x_netting_pkg.number_arr;
972 b_tp_total_quantity msc_x_netting_pkg.number_arr;
973 b_posting_total_quantity msc_x_netting_pkg.number_arr;
974
975 BEGIN
976
977 --dbms_output.put_line('Exception 5');
978
979 open exception_5(p_refresh_number);
980 fetch exception_5 BULK COLLECT INTO
981 b_publisher_id,
982 b_publisher_site_id,
983 b_supplier_id,
984 b_supplier_site_id,
985 b_customer_id,
986 b_customer_site_id,
987 b_item_id,
988 b_bucket_index,
989 b_bkt_start_date,
990 b_bkt_end_date,
991 b_order_type,
992 b_publisher_name,
993 b_publisher_site_name,
994 b_supplier_name,
995 b_supplier_site_name,
996 b_customer_name,
997 b_customer_site_name,
998 b_item_name,
999 b_item_desc,
1000 b_supplier_item_name,
1001 b_customer_item_name,
1002 b_total_quantity,
1003 b_tp_total_quantity,
1004 b_posting_total_quantity,
1005 b_refresh_number;
1006 CLOSE exception_5;
1007
1008 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
1009
1010 FOR j in 1..b_item_id.COUNT
1011 LOOP
1012
1013
1014 IF (j = 1 or b_publisher_id(j) <> b_publisher_id(j-1) OR
1015 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
1016 b_customer_id(j) <> b_customer_id(j-1) OR
1017 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
1018 b_supplier_id(j) <> b_supplier_id(j-1) OR
1019 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
1020 b_item_id(j) <> b_item_id(j-1)) THEN
1021 --======================================================
1022 -- archive old exception and its complement
1023 --======================================================
1024
1025 msc_x_netting_pkg.add_to_delete_tbl(
1026 b_publisher_id(j),
1027 b_publisher_site_id(j),
1028 b_customer_id(j),
1029 b_customer_site_id(j),
1030 null,
1031 null,
1032 b_item_id(j),
1033 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
1034 msc_x_netting_pkg.G_EXCEP5,
1035 null,
1036 null,
1037 null,
1038 null,
1039 t_company_list,
1040 t_company_site_list,
1041 t_customer_list,
1042 t_customer_site_list,
1043 t_supplier_list,
1044 t_supplier_site_list,
1045 t_item_list,
1046 t_group_list,
1047 t_type_list,
1048 t_trxid1_list,
1049 t_trxid2_list,
1050 t_date1_list,
1051 t_date2_list);
1052
1053 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP5,
1054 b_publisher_id(j),
1055 b_publisher_site_id(j),
1056 b_item_id(j),
1057 null,
1058 null,
1059 b_customer_id(j),
1060 b_customer_site_id(j),
1061 null);
1062
1063 --------------------------------------------------------------------------
1064 -- get the shipping control
1065 ---------------------------------------------------------------------------
1066 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
1067 b_customer_site_name(j),
1068 b_publisher_name(j),
1069 b_publisher_site_name(j));
1070
1071 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1072 nvl(l_shipping_control,1));
1073
1074 END IF;
1075
1076
1077 IF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
1078 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
1079 b_customer_id(j) = b_customer_id(j-1) AND
1080 b_customer_site_id(j) = b_customer_site_id(j-1) AND
1081 b_supplier_id(j) = b_supplier_id(j-1) AND
1082 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
1083 b_item_id(j) = b_item_id(j-1) and
1084 b_bucket_index(j) = b_bucket_index(j-1) and
1085 b_bkt_start_date(j) = b_bkt_start_date(j-1)) THEN
1086
1087 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
1088 l_total_demand := b_total_quantity(j-1);
1089 l_tp_total_demand := b_tp_total_quantity(j-1);
1090 l_posting_total_demand := b_posting_total_quantity(j-1);
1091 ELSIF (b_order_type(j-1)= msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1092 l_total_supply := b_total_quantity(j-1);
1093 l_tp_total_supply := b_tp_total_quantity(j-1);
1094 l_posting_total_supply := b_posting_total_quantity(j-1);
1095 END IF;
1096 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1097 l_total_demand := b_total_quantity(j);
1098 l_tp_total_demand := b_tp_total_quantity(j);
1099 l_posting_total_demand := b_posting_total_quantity(j);
1100 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1101 l_total_supply := b_total_quantity(j);
1102 l_tp_total_supply := b_tp_total_quantity(j);
1103 l_posting_total_supply := b_posting_total_quantity(j);
1104 END IF;
1105 l_pair := 1;
1106 l_insert := 1;
1107 l_start_date := b_bkt_start_date(j-1);
1108 l_end_date := b_bkt_end_date(j-1);
1109 l_publisher_id := b_publisher_id(j-1);
1110 l_publisher_site_id := b_publisher_site_id(j-1);
1111 l_customer_id := b_customer_id(j-1);
1112 l_customer_site_id := b_customer_site_id(j-1);
1113 l_supplier_id := b_supplier_id(j-1);
1114 l_supplier_site_id := b_supplier_site_id(j-1);
1115 l_item_id := b_item_id(j-1);
1116 l_publisher_name := b_publisher_name(j-1);
1117 l_publisher_site_name := b_publisher_site_name(j-1);
1118 l_customer_name := b_customer_name(j-1);
1119 l_customer_site_name := b_customer_site_name(j-1);
1120 l_supplier_name := b_supplier_name(j-1);
1121 l_supplier_site_name := b_supplier_site_name(j-1);
1122
1123
1124 --dbms_output.put_line('equal insert ' || b_bkt_start_date(j));
1125 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
1126 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
1127 b_customer_id(j) = b_customer_id(j-1) AND
1128 b_customer_site_id(j) = b_customer_site_id(j-1) AND
1129 b_supplier_id(j) = b_supplier_id(j-1) AND
1130 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
1131 b_item_id(j) = b_item_id(j-1) AND
1132 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair = 1) THEN
1133 l_pair := 0;
1134 l_insert := 0;
1135 --dbms_output.put_line('2 no insert with previous line l_pair = 1' || b_bkt_start_date(j));
1136
1137 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
1138 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
1139 b_customer_id(j) = b_customer_id(j-1) AND
1140 b_customer_site_id(j) = b_customer_site_id(j-1) AND
1141 b_supplier_id(j) = b_supplier_id(j-1) AND
1142 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
1143 b_item_id(j) = b_item_id(j-1) AND
1144 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair <> 1) THEN
1145
1146 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1147 l_total_demand := b_total_quantity(j-1);
1148 l_tp_total_demand := b_tp_total_quantity(j-1);
1149 l_posting_total_demand := b_posting_total_quantity(j-1);
1150 l_total_supply := 0;
1151 l_tp_total_supply := 0;
1152 l_posting_total_supply := 0;
1153 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1154 l_total_demand := 0;
1155 l_tp_total_demand := 0;
1156 l_posting_total_demand := 0;
1157 l_total_supply := b_total_quantity(j-1);
1158 l_tp_total_supply := b_tp_total_quantity(j-1);
1159 l_posting_total_supply := b_posting_total_quantity(j-1);
1160 END IF;
1161
1162 l_pair := 0;
1163 l_insert := 1;
1164 l_start_date := b_bkt_start_date(j-1);
1165 l_end_date := b_bkt_end_date(j-1);
1166 l_publisher_id := b_publisher_id(j-1);
1167 l_publisher_site_id := b_publisher_site_id(j-1);
1168 l_customer_id := b_customer_id(j-1);
1169 l_customer_site_id := b_customer_site_id(j-1);
1170 l_supplier_id := b_supplier_id(j-1);
1171 l_supplier_site_id := b_supplier_site_id(j-1);
1172 l_item_id := b_item_id(j-1);
1173 l_publisher_name := b_publisher_name(j-1);
1174 l_publisher_site_name := b_publisher_site_name(j-1);
1175 l_customer_name := b_customer_name(j-1);
1176 l_customer_site_name := b_customer_site_name(j-1);
1177 l_supplier_name := b_supplier_name(j-1);
1178 l_supplier_site_name := b_supplier_site_name(j-1);
1179
1180 --dbms_output.put_line('3 not equal insert ' || b_bkt_start_date(j));
1181 ELSIF (j > 1 and l_pair = 1 ) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
1182 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
1183 b_customer_id(j) <> b_customer_id(j-1) OR
1184 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
1185 b_supplier_id(j) <> b_supplier_id(j-1) OR
1186 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
1187 b_item_id(j) <> b_item_id(j-1) ) THEN
1188
1189 l_pair := 0;
1190 l_insert := 0;
1191
1192 --dbms_output.put_line('4 diff no insert' || b_bkt_start_date(j) || ' ps ' || b_publisher_site_id(j) || ' cs ' || b_customer_site_id(j));
1193 ELSIF (j > 1 and l_pair <> 1 ) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
1194 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
1195 b_customer_id(j) <> b_customer_id(j-1) OR
1196 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
1197 b_supplier_id(j) <> b_supplier_id(j-1) OR
1198 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
1199 b_item_id(j) <> b_item_id(j-1) ) THEN
1200
1201 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1202 l_total_demand := b_total_quantity(j-1);
1203 l_tp_total_demand := b_tp_total_quantity(j-1);
1204 l_posting_total_demand := b_posting_total_quantity(j-1);
1205 l_total_supply := 0;
1206 l_tp_total_supply := 0;
1207 l_posting_total_supply := 0;
1208 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1209 l_total_demand := 0;
1210 l_tp_total_demand := 0;
1211 l_posting_total_demand := 0;
1212 l_total_supply := b_total_quantity(j-1);
1213 l_tp_total_supply := b_tp_total_quantity(j-1);
1214 l_posting_total_supply := b_posting_total_quantity(j-1);
1215 END IF;
1216
1217 l_pair := 0;
1218 l_insert := 1;
1219 l_start_date := b_bkt_start_date(j-1);
1220 l_end_date := b_bkt_end_date(j-1);
1221 l_publisher_id := b_publisher_id(j-1);
1222 l_publisher_site_id := b_publisher_site_id(j-1);
1223 l_customer_id := b_customer_id(j-1);
1224 l_customer_site_id := b_customer_site_id(j-1);
1225 l_supplier_id := b_supplier_id(j-1);
1226 l_supplier_site_id := b_supplier_site_id(j-1);
1227 l_item_id := b_item_id(j-1);
1228 l_publisher_name := b_publisher_name(j-1);
1229 l_publisher_site_name := b_publisher_site_name(j-1);
1230 l_customer_name := b_customer_name(j-1);
1231 l_customer_site_name := b_customer_site_name(j-1);
1232 l_supplier_name := b_supplier_name(j-1);
1233 l_supplier_site_name := b_supplier_site_name(j-1);
1234
1235
1236 --dbms_output.put_line('5 diff with insert' || b_bkt_start_date(j) || ' ps ' || b_publisher_site_id(j) || ' cs ' || b_customer_site_id(j));
1237
1238 ELSIF (j = 1 AND b_bkt_start_date.COUNT = 1) THEN
1239 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
1240 l_total_demand := b_total_quantity(j);
1241 l_tp_total_demand := b_tp_total_quantity(j);
1242 l_posting_total_demand := b_posting_total_quantity(j);
1243 l_total_supply := 0;
1244 l_tp_total_supply := 0;
1245 l_posting_total_supply := 0;
1246 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1247 l_total_demand := 0;
1248 l_tp_total_demand := 0;
1249 l_posting_total_demand := 0;
1250 l_total_supply := b_total_quantity(j);
1251 l_tp_total_supply := b_tp_total_quantity(j);
1252 l_posting_total_supply := b_posting_total_quantity(j);
1253 END IF;
1254
1255 l_pair := 0;
1256 l_insert := 1;
1257 l_start_date := b_bkt_start_date(j);
1258 l_end_date := b_bkt_end_date(j);
1259 l_publisher_id := b_publisher_id(j);
1260 l_publisher_site_id := b_publisher_site_id(j);
1261 l_customer_id := b_customer_id(j);
1262 l_customer_site_id := b_customer_site_id(j);
1263 l_supplier_id := b_supplier_id(j);
1264 l_supplier_site_id := b_supplier_site_id(j);
1265 l_item_id := b_item_id(j);
1266 l_publisher_name := b_publisher_name(j);
1267 l_publisher_site_name := b_publisher_site_name(j);
1268 l_customer_name := b_customer_name(j);
1269 l_customer_site_name := b_customer_site_name(j);
1270 l_supplier_name := b_supplier_name(j);
1271 l_supplier_site_name := b_supplier_site_name(j);
1272
1273 --dbms_output.put_line('5 only one line' || b_bkt_start_date(j));
1274 END IF;
1275
1276
1277 FND_FILE.PUT_LINE(FND_FILE.LOG, '5:supply ' || l_total_supply || ' demand ' || l_total_demand || ' date ' ||
1278 l_start_date || 'cutoff ' || l_cutoff_ref_num );
1279
1280 --dbms_output.put_line( '5:supply ' || l_total_supply || ' demand ' || l_total_demand || ' date ' ||
1281 -- l_start_date || 'cutoff ' || l_cutoff_ref_num || ' pair ' || l_pair);
1282 IF ( ((j > 1 and l_insert = 1) OR (b_bucket_index.COUNT = 1)) and
1283 ((l_tp_total_demand - l_total_supply) > (l_tp_total_demand * l_threshold1/100)))
1284 and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
1285 THEN --- Bug# 4629582
1286
1287 --======================================================
1288 -- clean up the opposite exception and its complement
1289 --======================================================
1290 msc_x_netting_pkg.add_to_delete_tbl(
1291 l_publisher_id, --b_publisher_id(j),
1292 l_publisher_site_id, --b_publisher_site_id(j),
1293 l_customer_id, --b_customer_id(j),
1294 l_customer_site_id, --b_customer_site_id(j),
1295 null,
1296 null,
1297 l_item_id, --b_item_id(j),
1298 msc_x_netting_pkg.G_MATERIAL_EXCESS,
1299 msc_x_netting_pkg.G_EXCEP25,
1300 null,
1301 null,
1302 l_start_date,
1303 l_end_date,
1304 t_company_list,
1305 t_company_site_list,
1306 t_customer_list,
1307 t_customer_site_list,
1308 t_supplier_list,
1309 t_supplier_site_list,
1310 t_item_list,
1311 t_group_list,
1312 t_type_list,
1313 t_trxid1_list,
1314 t_trxid2_list,
1315 t_date1_list,
1316 t_date2_list);
1317
1318 l_exception_type := msc_x_netting_pkg.G_EXCEP5;
1319 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
1320 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1321 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1322
1323 --dbms_output.put_line('Deman ' || l_tp_total_demand || 'supp ' || l_total_supply);
1324
1325 msc_x_netting_pkg.add_to_exception_tbl(
1326 l_publisher_id, --b_publisher_id(j),
1327 l_publisher_name, --b_publisher_name(j),
1328 l_publisher_site_id, --b_publisher_site_id(j),
1329 l_publisher_site_name, --b_publisher_site_name(j),
1330 l_item_id, --b_item_id(j),
1331 l_item_name, --b_item_name(j),
1332 l_item_desc,
1333 l_exception_type,
1334 l_exception_type_name,
1335 l_exception_group,
1336 l_exception_group_name,
1337 null, --trx_id1,
1338 null, --trx_id2,
1339 l_customer_id, --b_customer_id(j),
1340 l_customer_name, --b_customer_name(j),
1341 l_customer_site_id, --b_customer_site_id(j),
1342 l_customer_site_name, --b_customer_site_name(j),
1343 l_customer_item_name, --b_customer_item_name(j),
1344 null, --l_supplier_id
1345 null,
1346 null, --l_supplier_site_id
1347 null,
1348 l_supplier_item_name, --b_supplier_item_name(j),
1349 l_total_supply, --number1
1350 l_tp_total_demand, --number2
1351 null, --number3
1352 l_threshold1,
1353 null, --lead time
1354 null, --item min
1355 null, --item max
1356 null, --l_order_number,
1357 null, --l_release_number,
1358 null, --l_line_number,
1359 null, --l_end_order_number,
1360 null, --l_end_order_rel_number,
1361 null, --l_end_order_line_number,
1362 null, --b_so_creation_date(j),
1363 null, --b_po_creation_date(j),
1364 l_start_date,
1365 l_end_date,
1366 null, --ship_date(j),
1367 null, --ship_date(j),
1368 null,
1369 l_exception_basis,
1370 a_company_id,
1371 a_company_name,
1372 a_company_site_id,
1373 a_company_site_name,
1374 a_item_id,
1375 a_item_name,
1376 a_item_desc,
1377 a_exception_type,
1378 a_exception_type_name,
1379 a_exception_group,
1380 a_exception_group_name,
1381 a_trx_id1,
1382 a_trx_id2,
1383 a_customer_id,
1384 a_customer_name,
1385 a_customer_site_id,
1386 a_customer_site_name,
1387 a_customer_item_name,
1388 a_supplier_id,
1389 a_supplier_name,
1390 a_supplier_site_id,
1391 a_supplier_site_name,
1392 a_supplier_item_name,
1393 a_number1,
1394 a_number2,
1395 a_number3,
1396 a_threshold,
1397 a_lead_time,
1398 a_item_min_qty,
1399 a_item_max_qty,
1400 a_order_number,
1401 a_release_number,
1402 a_line_number,
1403 a_end_order_number,
1404 a_end_order_rel_number,
1405 a_end_order_line_number,
1406 a_creation_date,
1407 a_tp_creation_date,
1408 a_date1,
1409 a_date2,
1410 a_date3,
1411 a_date4,
1412 a_date5,
1413 a_exception_basis);
1414
1415 l_inserted_record := l_inserted_record + 1;
1416
1417 end if; /* compute the exception */
1418 /*------------------------------------------------------------------
1419 Loop for the last record if require to insert
1420 ------------------------------------------------------------------*/
1421 IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
1422 l_insert := 1;
1423 l_start_date := b_bkt_start_date(j);
1424 l_end_date := b_bkt_end_date(j);
1425 l_publisher_id := b_publisher_id(j);
1426 l_publisher_site_id := b_publisher_site_id(j);
1427 l_customer_id := b_customer_id(j);
1428 l_customer_site_id := b_customer_site_id(j);
1429 l_supplier_id := b_supplier_id(j);
1430 l_supplier_site_id := b_supplier_site_id(j);
1431 l_item_id := b_item_id(j);
1432 l_publisher_name := b_publisher_name(j);
1433 l_publisher_site_name := b_publisher_site_name(j);
1434 l_customer_name := b_customer_name(j);
1435 l_customer_site_name := b_customer_site_name(j);
1436 l_supplier_name := b_supplier_name(j);
1437 l_supplier_site_name := b_supplier_site_name(j);
1438
1439 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1440 l_total_demand := b_total_quantity(j);
1441 l_tp_total_demand := b_tp_total_quantity(j);
1442 l_posting_total_demand := b_posting_total_quantity(j);
1443 l_total_supply := 0;
1444 l_tp_total_supply := 0;
1445 l_posting_total_supply := 0;
1446 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1447 l_total_demand := 0;
1448 l_tp_total_demand := 0;
1449 l_posting_total_demand := 0;
1450 l_total_supply := b_total_quantity(j);
1451 l_tp_total_supply := b_tp_total_quantity(j);
1452 l_posting_total_supply := b_posting_total_quantity(j);
1453 END IF;
1454 --dbms_output.put_line(' loop for last record ' );
1455 IF ((l_tp_total_demand - l_total_supply) > (l_tp_total_demand * l_threshold1/100))
1456 and (b_refresh_number(j) > p_refresh_number)
1457 THEN --- Bug#4629582
1458
1459 FND_FILE.PUT_LINE(FND_FILE.LOG, '5:insert' || 'demand ' || l_tp_total_demand || 'sup ' || l_total_supply);
1460
1461
1462 l_exception_type := msc_x_netting_pkg.G_EXCEP5;
1463 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
1464 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1465 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1466
1467 --dbms_output.put_line('Deman ' || l_tp_total_demand || 'supp ' || l_total_supply);
1468
1469 msc_x_netting_pkg.add_to_exception_tbl(
1470 l_publisher_id, --b_publisher_id(j),
1471 l_publisher_name, --b_publisher_name(j),
1472 l_publisher_site_id, --b_publisher_site_id(j),
1473 l_publisher_site_name, --b_publisher_site_name(j),
1474 l_item_id, --b_item_id(j),
1475 l_item_name, --b_item_name(j),
1476 l_item_desc,
1477 l_exception_type,
1478 l_exception_type_name,
1479 l_exception_group,
1480 l_exception_group_name,
1481 null, --trx_id1,
1482 null, --trx_id2,
1483 l_customer_id, --b_customer_id(j),
1484 l_customer_name, --b_customer_name(j),
1485 l_customer_site_id, --b_customer_site_id(j),
1486 l_customer_site_name, --b_customer_site_name(j),
1487 l_customer_item_name, --b_customer_item_name(j),
1488 null, --l_supplier_id
1489 null,
1490 null, --l_supplier_site_id
1491 null,
1492 l_supplier_item_name, --b_supplier_item_name(j),
1493 l_total_supply, --number1
1494 l_tp_total_demand, --number2
1495 null, --number3
1496 l_threshold1,
1497 null, --lead time
1498 null, --item min
1499 null, --item max
1500 null, --l_order_number,
1501 null, --l_release_number,
1502 null, --l_line_number,
1503 null, --l_end_order_number,
1504 null, --l_end_order_rel_number,
1505 null, --l_end_order_line_number,
1506 null, --b_so_creation_date(j),
1507 null, --b_po_creation_date(j),
1508 l_start_date,
1509 l_end_date,
1510 null, --ship_date(j),
1511 null, --ship_date(j),
1512 null,
1513 l_exception_basis,
1514 a_company_id,
1515 a_company_name,
1516 a_company_site_id,
1517 a_company_site_name,
1518 a_item_id,
1519 a_item_name,
1520 a_item_desc,
1521 a_exception_type,
1522 a_exception_type_name,
1523 a_exception_group,
1524 a_exception_group_name,
1525 a_trx_id1,
1526 a_trx_id2,
1527 a_customer_id,
1528 a_customer_name,
1529 a_customer_site_id,
1530 a_customer_site_name,
1531 a_customer_item_name,
1532 a_supplier_id,
1533 a_supplier_name,
1534 a_supplier_site_id,
1535 a_supplier_site_name,
1536 a_supplier_item_name,
1537 a_number1,
1538 a_number2,
1539 a_number3,
1540 a_threshold,
1541 a_lead_time,
1542 a_item_min_qty,
1543 a_item_max_qty,
1544 a_order_number,
1545 a_release_number,
1546 a_line_number,
1547 a_end_order_number,
1548 a_end_order_rel_number,
1549 a_end_order_line_number,
1550 a_creation_date,
1551 a_tp_creation_date,
1552 a_date1,
1553 a_date2,
1554 a_date3,
1555 a_date4,
1556 a_date5,
1557 a_exception_basis);
1558 l_inserted_record := l_inserted_record + 1;
1559
1560 end if; /* compute the exception */
1561 end if; /* m is the last record in the loop */
1562
1563 --END LOOP; /* loop*/
1564 --END IF;
1565 --- END IF; --sum
1566 END LOOP;
1567 END IF;
1568
1569 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(5) ||
1570 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1571
1572 --=======================================================================================
1573 --for Supplier supply planning (exception 2.2 and 7.2)
1574 --======================================================================================
1575 l_total_supply := 0;
1576 l_total_demand := 0;
1577 l_start_date := null;
1578 l_end_date := null;
1579 l_pair := 0;
1580 l_insert := 0;
1581
1582
1583 --dbms_output.put_line('Exception 6');
1584
1585 open exception_6(p_refresh_number);
1586 fetch exception_6 BULK COLLECT INTO
1587 b_publisher_id,
1588 b_publisher_site_id,
1589 b_customer_id,
1590 b_customer_site_id,
1591 b_supplier_id,
1592 b_supplier_site_id,
1593 b_item_id,
1594 b_bucket_index,
1595 b_bkt_start_date,
1596 b_bkt_end_date,
1597 b_order_type,
1598 b_publisher_name,
1599 b_publisher_site_name,
1600 b_customer_name,
1601 b_customer_site_name,
1602 b_supplier_name,
1603 b_supplier_site_name,
1604 b_item_name,
1605 b_item_desc,
1606 b_supplier_item_name,
1607 b_customer_item_name,
1608 b_total_quantity,
1609 b_tp_total_quantity,
1610 b_posting_total_quantity,
1611 b_refresh_number;
1612
1613 CLOSE exception_6;
1614 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
1615 FOR j in 1..b_item_id.COUNT
1616 LOOP
1617
1618
1619 IF (j = 1 or b_publisher_id(j) <> b_publisher_id(j-1) OR
1620 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
1621 b_customer_id(j) <> b_customer_id(j-1) OR
1622 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
1623 b_supplier_id(j) <> b_supplier_id(j-1) OR
1624 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
1625 b_item_id(j) <> b_item_id(j-1)) THEN
1626
1627 --======================================================
1628 -- archive old exception and its complement
1629 --======================================================
1630 msc_x_netting_pkg.add_to_delete_tbl(
1631 b_publisher_id(j),
1632 b_publisher_site_id(j),
1633 null,
1634 null,
1635 b_supplier_id(j),
1636 b_supplier_site_id(j),
1637 b_item_id(j),
1638 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
1639 msc_x_netting_pkg.G_EXCEP6,
1640 null,
1641 null,
1642 null,
1643 null,
1644 t_company_list,
1645 t_company_site_list,
1646 t_customer_list,
1647 t_customer_site_list,
1648 t_supplier_list,
1649 t_supplier_site_list,
1650 t_item_list,
1651 t_group_list,
1652 t_type_list,
1653 t_trxid1_list,
1654 t_trxid2_list,
1655 t_date1_list,
1656 t_date2_list);
1657
1658
1659 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP6,
1660 b_publisher_id(j),
1661 b_publisher_site_id(j),
1662 b_item_id(j),
1663 b_supplier_id(j),
1664 b_supplier_site_id(j),
1665 null,
1666 null,
1667 null);
1668
1669
1670 --------------------------------------------------------------------------
1671 -- get the shipping control
1672 ---------------------------------------------------------------------------
1673 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1674 b_publisher_site_name(j),
1675 b_supplier_name(j),
1676 b_supplier_site_name(j));
1677
1678 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1679 nvl(l_shipping_control,1));
1680
1681 END IF;
1682
1683
1684 IF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
1685 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
1686 b_customer_id(j) = b_customer_id(j-1) AND
1687 b_customer_site_id(j) = b_customer_site_id(j-1) AND
1688 b_supplier_id(j) = b_supplier_id(j-1) AND
1689 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
1690 b_item_id(j) = b_item_id(j-1) and
1691 b_bucket_index(j) = b_bucket_index(j-1) and
1692 b_bkt_start_date(j) = b_bkt_start_date(j-1)) THEN
1693
1694 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
1695 l_total_demand := b_total_quantity(j-1);
1696 l_tp_total_demand := b_tp_total_quantity(j-1);
1697 l_posting_total_demand := b_posting_total_quantity(j-1);
1698 ELSIF (b_order_type(j-1)= msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1699 l_total_supply := b_total_quantity(j-1);
1700 l_tp_total_supply := b_tp_total_quantity(j-1);
1701 l_posting_total_supply := b_posting_total_quantity(j-1);
1702 END IF;
1703
1704 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1705 l_total_demand := b_total_quantity(j);
1706 l_tp_total_demand := b_tp_total_quantity(j);
1707 l_posting_total_demand := b_posting_total_quantity(j);
1708
1709 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1710 l_total_supply := b_total_quantity(j);
1711 l_tp_total_supply := b_tp_total_quantity(j);
1712 l_posting_total_supply := b_posting_total_quantity(j);
1713 END IF;
1714
1715 l_pair := 1;
1716 l_insert := 1;
1717 l_start_date := b_bkt_start_date(j-1);
1718 l_end_date := b_bkt_end_date(j-1);
1719 l_publisher_id := b_publisher_id(j-1);
1720 l_publisher_site_id := b_publisher_site_id(j-1);
1721 l_customer_id := b_customer_id(j-1);
1722 l_customer_site_id := b_customer_site_id(j-1);
1723 l_supplier_id := b_supplier_id(j-1);
1724 l_supplier_site_id := b_supplier_site_id(j-1);
1725 l_item_id := b_item_id(j-1);
1726 l_publisher_name := b_publisher_name(j-1);
1727 l_publisher_site_name := b_publisher_site_name(j-1);
1728 l_customer_name := b_customer_name(j-1);
1729 l_customer_site_name := b_customer_site_name(j-1);
1730 l_supplier_name := b_supplier_name(j-1);
1731 l_supplier_site_name := b_supplier_site_name(j-1);
1732
1733 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
1734 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
1735 b_customer_id(j) = b_customer_id(j-1) AND
1736 b_customer_site_id(j) = b_customer_site_id(j-1) AND
1737 b_supplier_id(j) = b_supplier_id(j-1) AND
1738 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
1739 b_item_id(j) = b_item_id(j-1) AND
1740 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair = 1) THEN
1741 l_pair := 0;
1742 l_insert := 0;
1743
1744 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
1745 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
1746 b_customer_id(j) = b_customer_id(j-1) AND
1747 b_customer_site_id(j) = b_customer_site_id(j-1) AND
1748 b_supplier_id(j) = b_supplier_id(j-1) AND
1749 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
1750 b_item_id(j) = b_item_id(j-1) AND
1751 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair <> 1) THEN
1752
1753 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1754 l_total_demand := b_total_quantity(j-1);
1755 l_tp_total_demand := b_tp_total_quantity(j-1);
1756 l_posting_total_demand := b_posting_total_quantity(j-1);
1757 l_total_supply := 0;
1758 l_tp_total_supply := 0;
1759 l_posting_total_supply := 0;
1760 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1761 l_total_demand := 0;
1762 l_tp_total_demand := 0;
1763 l_posting_total_demand := 0;
1764 l_total_supply := b_total_quantity(j-1);
1765 l_tp_total_supply := b_tp_total_quantity(j-1);
1766 l_posting_total_supply := b_posting_total_quantity(j-1);
1767 END IF;
1768 l_pair := 0;
1769 l_insert := 1;
1770 l_start_date := b_bkt_start_date(j-1);
1771 l_end_date := b_bkt_end_date(j-1);
1772 l_publisher_id := b_publisher_id(j-1);
1773 l_publisher_site_id := b_publisher_site_id(j-1);
1774 l_customer_id := b_customer_id(j-1);
1775 l_customer_site_id := b_customer_site_id(j-1);
1776 l_supplier_id := b_supplier_id(j-1);
1777 l_supplier_site_id := b_supplier_site_id(j-1);
1778 l_item_id := b_item_id(j-1);
1779 l_publisher_name := b_publisher_name(j-1);
1780 l_publisher_site_name := b_publisher_site_name(j-1);
1781 l_customer_name := b_customer_name(j-1);
1782 l_customer_site_name := b_customer_site_name(j-1);
1783 l_supplier_name := b_supplier_name(j-1);
1784 l_supplier_site_name := b_supplier_site_name(j-1);
1785
1786 ELSIF (j > 1 and l_pair = 1 ) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
1787 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
1788 b_customer_id(j) <> b_customer_id(j-1) OR
1789 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
1790 b_supplier_id(j) <> b_supplier_id(j-1) OR
1791 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
1792 b_item_id(j) <> b_item_id(j-1)) THEN
1793 l_pair := 0;
1794 l_insert := 0;
1795
1796 ELSIF (j > 1 and l_pair <> 1) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
1797 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
1798 b_customer_id(j) <> b_customer_id(j-1) OR
1799 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
1800 b_supplier_id(j) <> b_supplier_id(j-1) OR
1801 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
1802 b_item_id(j) <> b_item_id(j-1)) THEN
1803
1804 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
1805 l_total_demand := b_total_quantity(j-1);
1806 l_tp_total_demand := b_tp_total_quantity(j-1);
1807 l_posting_total_demand := b_posting_total_quantity(j-1);
1808 l_total_supply := 0;
1809 l_tp_total_supply := 0;
1810 l_posting_total_supply := 0;
1811 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1812 l_total_demand := 0;
1813 l_tp_total_demand := 0;
1814 l_posting_total_demand := 0;
1815 l_total_supply := b_total_quantity(j-1);
1816 l_tp_total_supply := b_tp_total_quantity(j-1);
1817 l_posting_total_supply := b_posting_total_quantity(j-1);
1818 END IF;
1819 l_pair := 0;
1820 l_insert := 1;
1821 l_start_date := b_bkt_start_date(j-1);
1822 l_end_date := b_bkt_end_date(j-1);
1823 l_publisher_id := b_publisher_id(j-1);
1824 l_publisher_site_id := b_publisher_site_id(j-1);
1825 l_customer_id := b_customer_id(j-1);
1826 l_customer_site_id := b_customer_site_id(j-1);
1827 l_supplier_id := b_supplier_id(j-1);
1828 l_supplier_site_id := b_supplier_site_id(j-1);
1829 l_item_id := b_item_id(j-1);
1830 l_publisher_name := b_publisher_name(j-1);
1831 l_publisher_site_name := b_publisher_site_name(j-1);
1832 l_customer_name := b_customer_name(j-1);
1833 l_customer_site_name := b_customer_site_name(j-1);
1834 l_supplier_name := b_supplier_name(j-1);
1835 l_supplier_site_name := b_supplier_site_name(j-1);
1836
1837 ELSIF (j = 1 and b_bkt_start_date.COUNT = 1) THEN
1838
1839 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
1840 l_total_demand := b_total_quantity(j);
1841 l_tp_total_demand := b_tp_total_quantity(j);
1842 l_posting_total_demand := b_posting_total_quantity(j);
1843 l_total_supply := 0;
1844 l_tp_total_supply := 0;
1845 l_posting_total_supply := 0;
1846 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
1847 l_total_demand := 0;
1848 l_tp_total_demand := 0;
1849 l_posting_total_demand := 0;
1850 l_total_supply := b_total_quantity(j);
1851 l_tp_total_supply := b_tp_total_quantity(j);
1852 l_posting_total_supply := b_posting_total_quantity(j);
1853 END IF;
1854
1855 l_pair := 0;
1856 l_insert := 1;
1857 l_start_date := b_bkt_start_date(j);
1858 l_end_date := b_bkt_end_date(j);
1859 l_publisher_id := b_publisher_id(j);
1860 l_publisher_site_id := b_publisher_site_id(j);
1861 l_customer_id := b_customer_id(j);
1862 l_customer_site_id := b_customer_site_id(j);
1863 l_supplier_id := b_supplier_id(j);
1864 l_supplier_site_id := b_supplier_site_id(j);
1865 l_item_id := b_item_id(j);
1866 l_publisher_name := b_publisher_name(j);
1867 l_publisher_site_name := b_publisher_site_name(j);
1868 l_customer_name := b_customer_name(j);
1869 l_customer_site_name := b_customer_site_name(j);
1870 l_supplier_name := b_supplier_name(j);
1871 l_supplier_site_name := b_supplier_site_name(j);
1872 END IF;
1873
1874
1875 FND_FILE.PUT_LINE(FND_FILE.LOG, '6:supply ' || l_total_supply || ' demand ' || l_total_demand || ' date ' ||
1876 l_start_date || 'cutoff ' || l_cutoff_ref_num || 'bkt ' || b_bucket_index(j));
1877
1878 IF ( ((j > 1 and l_insert = 1 ) OR (b_bucket_index.COUNT = 1)) and
1879 ((l_total_demand - l_tp_total_supply ) > (l_total_demand * l_threshold1/100)))
1880 and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
1881 THEN
1882
1883 --------------------------------------------------------
1884 -- clean up the opposite exception and its complement
1885 --------------------------------------------------------
1886 msc_x_netting_pkg.add_to_delete_tbl(
1887 l_publisher_id, --b_publisher_id(j),
1888 l_publisher_site_id, --b_publisher_site_id(j),
1889 null,
1890 null,
1891 l_supplier_id, --b_supplier_id(j),
1892 l_supplier_site_id, --b_supplier_site_id(j),
1893 l_item_id, --b_item_id(j),
1894 msc_x_netting_pkg.G_MATERIAL_EXCESS,
1895 msc_x_netting_pkg.G_EXCEP26,
1896 null,
1897 null,
1898 l_start_date,
1899 l_end_date,
1900 t_company_list,
1901 t_company_site_list,
1902 t_customer_list,
1903 t_customer_site_list,
1904 t_supplier_list,
1905 t_supplier_site_list,
1906 t_item_list,
1907 t_group_list,
1908 t_type_list,
1909 t_trxid1_list,
1910 t_trxid2_list,
1911 t_date1_list,
1912 t_date2_list);
1913
1914
1915 --if plan_type is SP then detected exception 2.2
1916 l_exception_type := msc_x_netting_pkg.G_EXCEP6;
1917 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
1918 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1919 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1920
1921 msc_x_netting_pkg.add_to_exception_tbl(
1922 l_publisher_id, --b_publisher_id(j),
1923 l_publisher_name, --b_publisher_name(j),
1924 l_publisher_site_id, --b_publisher_site_id(j),
1925 l_publisher_site_name, --b_publisher_site_name(j),
1926 l_item_id, --b_item_id(j),
1927 l_item_name, --b_item_name(j),
1928 l_item_desc, --b_item_desc(j),
1929 l_exception_type,
1930 l_exception_type_name,
1931 l_exception_group,
1932 l_exception_group_name,
1933 null, --trx_id1,
1934 null, --trx_id2,
1935 null, --l_customer_id,
1936 null,
1937 null, --l_customer_site_id,
1938 null,
1939 l_customer_item_name, --b_customer_item_name(j),
1940 l_supplier_id, --b_supplier_id(j),
1941 l_supplier_name, --b_supplier_name(j),
1942 l_supplier_site_id, --b_supplier_site_id(j),
1943 l_supplier_site_name, --b_supplier_site_name(j),
1944 l_supplier_item_name, --b_supplier_item_name(j),
1945 l_tp_total_supply,
1946 l_total_demand,
1947 null,
1948 l_threshold1,
1949 null, --lead time
1950 null, --item min
1951 null, --item_max
1952 null, --l_order_number,
1953 null, --l_release_number,
1954 null, --l_line_number,
1955 null, --l_end_order_number,
1956 null, --l_end_order_rel_number,
1957 null, --l_end_order_line_number,
1958 null, --b_so_creation_date(j),
1959 null, --b_po_creation_date(j),
1960 l_start_date,
1961 l_end_date,
1962 null, --ship_date(j),
1963 null, --ship_date(j),
1964 null,
1965 l_exception_basis,
1966 a_company_id,
1967 a_company_name,
1968 a_company_site_id,
1969 a_company_site_name,
1970 a_item_id,
1971 a_item_name,
1972 a_item_desc,
1973 a_exception_type,
1974 a_exception_type_name,
1975 a_exception_group,
1976 a_exception_group_name,
1977 a_trx_id1,
1978 a_trx_id2,
1979 a_customer_id,
1980 a_customer_name,
1981 a_customer_site_id,
1982 a_customer_site_name,
1983 a_customer_item_name,
1984 a_supplier_id,
1985 a_supplier_name,
1986 a_supplier_site_id,
1987 a_supplier_site_name,
1988 a_supplier_item_name,
1989 a_number1,
1990 a_number2,
1991 a_number3,
1992 a_threshold,
1993 a_lead_time,
1994 a_item_min_qty,
1995 a_item_max_qty,
1996 a_order_number,
1997 a_release_number,
1998 a_line_number,
1999 a_end_order_number,
2000 a_end_order_rel_number,
2001 a_end_order_line_number,
2002 a_creation_date,
2003 a_tp_creation_date,
2004 a_date1,
2005 a_date2,
2006 a_date3,
2007 a_date4,
2008 a_date5,
2009 a_exception_basis);
2010 l_inserted_record := l_inserted_record + 1;
2011
2012 end if; /* compute exception */
2013
2014 /*------------------------------------------------------------------
2015 Loop for the last record if require to insert
2016 ------------------------------------------------------------------*/
2017 IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
2018 l_pair := 0;
2019 l_insert := 1;
2020 l_start_date := b_bkt_start_date(j);
2021 l_end_date := b_bkt_end_date(j);
2022 l_publisher_id := b_publisher_id(j);
2023 l_publisher_site_id := b_publisher_site_id(j);
2024 l_customer_id := b_customer_id(j);
2025 l_customer_site_id := b_customer_site_id(j);
2026 l_supplier_id := b_supplier_id(j);
2027 l_supplier_site_id := b_supplier_site_id(j);
2028 l_item_id := b_item_id(j);
2029 l_publisher_name := b_publisher_name(j);
2030 l_publisher_site_name := b_publisher_site_name(j);
2031 l_customer_name := b_customer_name(j);
2032 l_customer_site_name := b_customer_site_name(j);
2033 l_supplier_name := b_supplier_name(j);
2034 l_supplier_site_name := b_supplier_site_name(j);
2035
2036 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
2037 l_total_demand := b_total_quantity(j);
2038 l_tp_total_demand := b_tp_total_quantity(j);
2039 l_posting_total_demand := b_posting_total_quantity(j);
2040 l_total_supply := 0;
2041 l_tp_total_supply := 0;
2042 l_posting_total_supply := 0;
2043 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
2044 l_total_demand := 0;
2045 l_tp_total_demand := 0;
2046 l_posting_total_demand := 0;
2047 l_total_supply := b_total_quantity(j);
2048 l_tp_total_supply := b_tp_total_quantity(j);
2049 l_posting_total_supply := b_posting_total_quantity(j);
2050 END IF;
2051
2052 IF ((l_total_demand - l_tp_total_supply ) > (l_total_demand * l_threshold1/100))
2053 and (b_refresh_number(j) > p_refresh_number)
2054 THEN
2055
2056 --if plan_type is SP then detected exception 2.2
2057 l_exception_type := msc_x_netting_pkg.G_EXCEP6;
2058 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
2059 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2060 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2061
2062 msc_x_netting_pkg.add_to_exception_tbl(
2063 l_publisher_id, --b_publisher_id(j),
2064 l_publisher_name, --b_publisher_name(j),
2065 l_publisher_site_id, --b_publisher_site_id(j),
2066 l_publisher_site_name, --b_publisher_site_name(j),
2067 l_item_id, --b_item_id(j),
2068 l_item_name, --b_item_name(j),
2069 l_item_desc, --b_item_desc(j),
2070 l_exception_type,
2071 l_exception_type_name,
2072 l_exception_group,
2073 l_exception_group_name,
2074 null, --trx_id1,
2075 null, --trx_id2,
2076 null, --l_customer_id,
2077 null,
2078 null, --l_customer_site_id,
2079 null,
2080 l_customer_item_name, --b_customer_item_name(j),
2081 l_supplier_id, --b_supplier_id(j),
2082 l_supplier_name, --b_supplier_name(j),
2083 l_supplier_site_id, --b_supplier_site_id(j),
2084 l_supplier_site_name, --b_supplier_site_name(j),
2085 l_supplier_item_name, --b_supplier_item_name(j),
2086 l_tp_total_supply,
2087 l_total_demand,
2088 null,
2089 l_threshold1,
2090 null, --lead time
2091 null, --item min
2092 null, --item_max
2093 null, --l_order_number,
2094 null, --l_release_number,
2095 null, --l_line_number,
2096 null, --l_end_order_number,
2097 null, --l_end_order_rel_number,
2098 null, --l_end_order_line_number,
2099 null, --b_so_creation_date(j),
2100 null, --b_po_creation_date(j),
2101 l_start_date,
2102 l_end_date,
2103 null, --ship_date(j),
2104 null, --ship_date(j),
2105 null,
2106 l_exception_basis,
2107 a_company_id,
2108 a_company_name,
2109 a_company_site_id,
2110 a_company_site_name,
2111 a_item_id,
2112 a_item_name,
2113 a_item_desc,
2114 a_exception_type,
2115 a_exception_type_name,
2116 a_exception_group,
2117 a_exception_group_name,
2118 a_trx_id1,
2119 a_trx_id2,
2120 a_customer_id,
2121 a_customer_name,
2122 a_customer_site_id,
2123 a_customer_site_name,
2124 a_customer_item_name,
2125 a_supplier_id,
2126 a_supplier_name,
2127 a_supplier_site_id,
2128 a_supplier_site_name,
2129 a_supplier_item_name,
2130 a_number1,
2131 a_number2,
2132 a_number3,
2133 a_threshold,
2134 a_lead_time,
2135 a_item_min_qty,
2136 a_item_max_qty,
2137 a_order_number,
2138 a_release_number,
2139 a_line_number,
2140 a_end_order_number,
2141 a_end_order_rel_number,
2142 a_end_order_line_number,
2143 a_creation_date,
2144 a_tp_creation_date,
2145 a_date1,
2146 a_date2,
2147 a_date3,
2148 a_date4,
2149 a_date5,
2150 a_exception_basis);
2151 l_inserted_record := l_inserted_record + 1;
2152
2153 end if; -- compute exception
2154
2155 end if; /* end of the last record of the loop */
2156
2157 --END LOOP;
2158 --END IF;
2159 --end if;
2160 END LOOP;
2161 END IF;
2162
2163 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(6) ||
2164 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2165
2166
2167 /* reset the trxid */
2168 l_generate_complement := null;
2169 --dbms_output.put_line('Exception 7 '); --supplier centric
2170 open exception_7 (p_refresh_number);
2171 fetch exception_7 BULK COLLECT INTO
2172 b_trx_id1,
2173 b_publisher_id,
2174 b_publisher_name,
2175 b_publisher_site_id,
2176 b_publisher_site_name,
2177 b_item_id,
2178 b_item_name,
2179 b_item_desc,
2180 b_supplier_item_name,
2181 b_supplier_item_desc,
2182 b_so_key_date,
2183 b_so_ship_date,
2184 b_so_receipt_date,
2185 b_posting_so_qty,
2186 b_so_qty,
2187 b_tp_so_qty,
2188 b_end_order_number,
2189 b_end_order_rel_number,
2190 b_end_order_line_number,
2191 b_customer_id,
2192 b_customer_name,
2193 b_customer_site_id,
2194 b_customer_site_name,
2195 b_customer_item_name,
2196 b_customer_item_desc,
2197 b_so_creation_date,
2198 b_so_last_refnum,
2199 b_trx_id2,
2200 b_po_key_date,
2201 b_po_ship_date,
2202 b_po_receipt_date,
2203 b_posting_po_qty,
2204 b_po_qty,
2205 b_tp_po_qty,
2206 b_order_number,
2207 b_release_number,
2208 b_line_number,
2209 b_supplier_id, --owning com
2210 b_supplier_name,
2211 b_supplier_site_id, --owning org
2212 b_supplier_site_name,
2213 b_po_creation_date,
2214 b_po_last_refnum;
2215 CLOSE exception_7;
2216 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
2217 FOR j in 1..b_trx_id1.COUNT
2218 LOOP
2219 --dbms_output.put_line('-----Exception7: Trx id 1 = ' || b_trx_id1(j));
2220 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
2221 --======================================================
2222 -- archive old exception and its complement
2223 --======================================================
2224 msc_x_netting_pkg.add_to_delete_tbl(
2225 b_publisher_id(j),
2226 b_publisher_site_id(j),
2227 b_customer_id(j),
2228 b_customer_site_id(j),
2229 null,
2230 null,
2231 b_item_id(j),
2232 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
2233 msc_x_netting_pkg.G_EXCEP7,
2234 b_trx_id1(j),
2235 b_trx_id2(j),
2236 null,
2237 null,
2238 t_company_list,
2239 t_company_site_list,
2240 t_customer_list,
2241 t_customer_site_list,
2242 t_supplier_list,
2243 t_supplier_site_list,
2244 t_item_list,
2245 t_group_list,
2246 t_type_list,
2247 t_trxid1_list,
2248 t_trxid2_list,
2249 t_date1_list,
2250 t_date2_list);
2251
2252
2253 msc_x_netting_pkg.add_to_delete_tbl(
2254 b_customer_id(j),
2255 b_customer_site_id(j),
2256 null,
2257 null,
2258 b_publisher_id(j),
2259 b_publisher_site_id(j),
2260 b_item_id(j),
2261 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
2262 msc_x_netting_pkg.G_EXCEP8,
2263 b_trx_id2(j),
2264 b_trx_id1(j),
2265 null,
2266 null,
2267 t_company_list,
2268 t_company_site_list,
2269 t_customer_list,
2270 t_customer_site_list,
2271 t_supplier_list,
2272 t_supplier_site_list,
2273 t_item_list,
2274 t_group_list,
2275 t_type_list,
2276 t_trxid1_list,
2277 t_trxid2_list,
2278 t_date1_list,
2279 t_date2_list);
2280
2281 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP7,
2282 b_publisher_id(j),
2283 b_publisher_site_id(j),
2284 b_item_id(j),
2285 null,
2286 null,
2287 b_customer_id(j),
2288 b_customer_site_id(j),
2289 b_so_receipt_date(j));
2290
2291
2292 l_total_qty := msc_x_netting_pkg.get_total_qty(b_order_number(j),
2293 b_release_number(j),
2294 b_line_number(j),
2295 b_publisher_id(j),
2296 b_publisher_site_id(j),
2297 b_customer_id(j),
2298 b_customer_site_id(j),
2299 b_item_id(j));
2300
2301
2302
2303 if (l_total_qty + (l_threshold1*b_tp_po_qty(j)/100) < b_tp_po_qty(j)) then
2304 --======================================================
2305 -- Clean up the opposite exception and its complement
2306 --======================================================
2307
2308 msc_x_netting_pkg.add_to_delete_tbl(
2309 b_publisher_id(j),
2310 b_publisher_site_id(j),
2311 b_customer_id(j),
2312 b_customer_site_id(j),
2313 null,
2314 null,
2315 b_item_id(j),
2316 msc_x_netting_pkg.G_MATERIAL_EXCESS,
2317 msc_x_netting_pkg.G_EXCEP27,
2318 b_trx_id1(j),
2319 b_trx_id2(j),
2320 null,
2321 null,
2322 t_company_list,
2323 t_company_site_list,
2324 t_customer_list,
2325 t_customer_site_list,
2326 t_supplier_list,
2327 t_supplier_site_list,
2328 t_item_list,
2329 t_group_list,
2330 t_type_list,
2331 t_trxid1_list,
2332 t_trxid2_list,
2333 t_date1_list,
2334 t_date2_list);
2335
2336 msc_x_netting_pkg.add_to_delete_tbl(
2337 b_customer_id(j),
2338 b_customer_site_id(j),
2339 null,
2340 null,
2341 b_publisher_id(j),
2342 b_publisher_site_id(j),
2343 b_item_id(j),
2344 msc_x_netting_pkg.G_MATERIAL_EXCESS,
2345 msc_x_netting_pkg.G_EXCEP28,
2346 b_trx_id2(j),
2347 b_trx_id1(j),
2348 null,
2349 null,
2350 t_company_list,
2351 t_company_site_list,
2352 t_customer_list,
2353 t_customer_site_list,
2354 t_supplier_list,
2355 t_supplier_site_list,
2356 t_item_list,
2357 t_group_list,
2358 t_type_list,
2359 t_trxid1_list,
2360 t_trxid2_list,
2361 t_date1_list,
2362 t_date2_list);
2363
2364 IF (j=1 OR (b_publisher_id(j -1) <> b_publisher_id(j) OR
2365 b_publisher_site_id(j-1) <> b_publisher_site_id(j) OR
2366 b_customer_id(j-1) <> b_customer_id(j) OR
2367 b_customer_site_id(j-1) <> b_customer_site_id(j) OR
2368 b_item_id(j-1) <> b_item_id(j) OR
2369 b_order_number(j-1) <> b_order_number(j) OR
2370 b_release_number(j-1) <> b_release_number(j) OR
2371 b_line_number(j-1) <> b_line_number(j) )) THEN
2372
2373
2374
2375 --dbms_output.put_line('PUB ' || b_publisher_id(j));
2376 --dbms_output.put_line('PUB SITE ' || b_publisher_site_id(j));
2377 --dbms_output.put_line('ITEM ' || b_item_id(j));
2378 --dbms_output.put_line('CUST ' || b_customer_id(j));
2379 --dbms_output.put_line('CUST SITE ' || b_customer_site_id(j));
2380 --dbms_output.put_line('ORDER ' || b_order_number(j));
2381 --dbms_output.put_line('LINE ' || b_line_number(j));
2382 --dbms_output.put_line('REL ' || b_release_number(j));
2383 -------------------------------------------------------------------------
2384 -- get the latest SO to populate the exception
2385 -------------------------------------------------------------------------
2386 BEGIN
2387 select transaction_id, receipt_date, ship_date, order_number, line_number, release_number
2388 into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
2389 b_end_order_line_number(j), b_end_order_rel_number(j)
2390 from msc_sup_dem_entries
2391 where publisher_id = b_publisher_id(j)
2392 and publisher_site_id = b_publisher_site_id(j)
2393 and customer_id = b_customer_id(j)
2394 and customer_site_id = b_customer_site_id(j)
2395 and inventory_item_id = b_item_id(j)
2396 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
2397 and end_order_number = b_order_number(j)
2398 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
2399 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
2400 and key_date = (select max(key_date)
2401 from msc_sup_dem_entries
2402 where publisher_id = b_publisher_id(j)
2403 and publisher_site_id = b_publisher_site_id(j)
2404 and customer_id = b_customer_id(j)
2405 and customer_site_id = b_customer_site_id(j)
2406 and inventory_item_id = b_item_id(j)
2407 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
2408 and end_order_number = b_order_number(j)
2409 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
2410 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1));
2411 --dbms_output.put_line('so trx id ' || l_last_so_trx_id);
2412 EXCEPTION
2413 when others then
2414 null;
2415 --dbms_output.put_line('Error ' || sqlerrm);
2416 END;
2417
2418 msc_x_netting_pkg.add_to_delete_tbl(
2419 b_publisher_id(j),
2420 b_publisher_site_id(j),
2421 b_customer_id(j),
2422 b_customer_site_id(j),
2423 null,
2424 null,
2425 b_item_id(j),
2426 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
2427 msc_x_netting_pkg.G_EXCEP7,
2428 l_last_so_trx_id,
2429 b_trx_id2(j),
2430 null,
2431 null,
2432 t_company_list,
2433 t_company_site_list,
2434 t_customer_list,
2435 t_customer_site_list,
2436 t_supplier_list,
2437 t_supplier_site_list,
2438 t_item_list,
2439 t_group_list,
2440 t_type_list,
2441 t_trxid1_list,
2442 t_trxid2_list,
2443 t_date1_list,
2444 t_date2_list);
2445
2446
2447 msc_x_netting_pkg.add_to_delete_tbl(
2448 b_customer_id(j),
2449 b_customer_site_id(j),
2450 null,
2451 null,
2452 b_publisher_id(j),
2453 b_publisher_site_id(j),
2454 b_item_id(j),
2455 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
2456 msc_x_netting_pkg.G_EXCEP8,
2457 b_trx_id2(j),
2458 l_last_so_trx_id,
2459 null,
2460 null,
2461 t_company_list,
2462 t_company_site_list,
2463 t_customer_list,
2464 t_customer_site_list,
2465 t_supplier_list,
2466 t_supplier_site_list,
2467 t_item_list,
2468 t_group_list,
2469 t_type_list,
2470 t_trxid1_list,
2471 t_trxid2_list,
2472 t_date1_list,
2473 t_date2_list);
2474
2475 msc_x_netting_pkg.add_to_delete_tbl(
2476 b_publisher_id(j),
2477 b_publisher_site_id(j),
2478 b_customer_id(j),
2479 b_customer_site_id(j),
2480 null,
2481 null,
2482 b_item_id(j),
2483 msc_x_netting_pkg.G_MATERIAL_EXCESS,
2484 msc_x_netting_pkg.G_EXCEP27,
2485 l_last_so_trx_id,
2486 b_trx_id2(j),
2487 null,
2488 null,
2489 t_company_list,
2490 t_company_site_list,
2491 t_customer_list,
2492 t_customer_site_list,
2493 t_supplier_list,
2494 t_supplier_site_list,
2495 t_item_list,
2496 t_group_list,
2497 t_type_list,
2498 t_trxid1_list,
2499 t_trxid2_list,
2500 t_date1_list,
2501 t_date2_list);
2502
2503 msc_x_netting_pkg.add_to_delete_tbl(
2504 b_customer_id(j),
2505 b_customer_site_id(j),
2506 null,
2507 null,
2508 b_publisher_id(j),
2509 b_publisher_site_id(j),
2510 b_item_id(j),
2511 msc_x_netting_pkg.G_MATERIAL_EXCESS,
2512 msc_x_netting_pkg.G_EXCEP28,
2513 b_trx_id2(j),
2514 l_last_so_trx_id,
2515 null,
2516 null,
2517 t_company_list,
2518 t_company_site_list,
2519 t_customer_list,
2520 t_customer_site_list,
2521 t_supplier_list,
2522 t_supplier_site_list,
2523 t_item_list,
2524 t_group_list,
2525 t_type_list,
2526 t_trxid1_list,
2527 t_trxid2_list,
2528 t_date1_list,
2529 t_date2_list);
2530
2531
2532 --------------------------------------------------------------------------
2533 -- get the shipping control
2534 ---------------------------------------------------------------------------
2535 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
2536 b_customer_site_name(j),
2537 b_publisher_name(j),
2538 b_publisher_site_name(j));
2539
2540 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
2541 nvl(l_shipping_control,1));
2542
2543 l_exception_type := msc_x_netting_pkg.G_EXCEP7; -- fulfillment qty shortfall for your cust po
2544 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
2545 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2546 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2547
2548 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
2549 b_publisher_name(j),
2550 b_publisher_site_id(j),
2551 b_publisher_site_name(j),
2552 b_item_id(j),
2553 b_item_name(j),
2554 b_item_desc(j),
2555 l_exception_type,
2556 l_exception_type_name,
2557 l_exception_group,
2558 l_exception_group_name,
2559 l_last_so_trx_id, --b_trx_id1(j),
2560 b_trx_id2(j),
2561 b_customer_id(j),
2562 b_customer_name(j),
2563 b_customer_site_id(j),
2564 b_customer_site_name(j),
2565 b_customer_item_name(j),
2566 null, --l_supplier_id,
2567 null,
2568 null, --l_supplier_site_id,
2569 null,
2570 b_supplier_item_name(j), --item name
2571 l_total_qty, --number1
2572 b_tp_po_qty(j), --number2
2573 null, --number3
2574 l_threshold1,
2575 null, --lead time
2576 null, --l_item_min,
2577 null, --l_item_max,
2578 b_order_number(j),
2579 b_release_number(j),
2580 b_line_number(j),
2581 b_end_order_number(j),
2582 b_end_order_rel_number(j),
2583 b_end_order_line_number(j),
2584 null,
2585 null,
2586 b_so_receipt_date(j),
2587 b_po_receipt_date(j),
2588 b_so_ship_date(j),
2589 b_po_ship_date(j),
2590 null,
2591 l_exception_basis,
2592 a_company_id,
2593 a_company_name,
2594 a_company_site_id,
2595 a_company_site_name,
2596 a_item_id,
2597 a_item_name,
2598 a_item_desc,
2599 a_exception_type,
2600 a_exception_type_name,
2601 a_exception_group,
2602 a_exception_group_name,
2603 a_trx_id1,
2604 a_trx_id2,
2605 a_customer_id,
2606 a_customer_name,
2607 a_customer_site_id,
2608 a_customer_site_name,
2609 a_customer_item_name,
2610 a_supplier_id,
2611 a_supplier_name,
2612 a_supplier_site_id,
2613 a_supplier_site_name,
2614 a_supplier_item_name,
2615 a_number1,
2616 a_number2,
2617 a_number3,
2618 a_threshold,
2619 a_lead_time,
2620 a_item_min_qty,
2621 a_item_max_qty,
2622 a_order_number,
2623 a_release_number,
2624 a_line_number,
2625 a_end_order_number,
2626 a_end_order_rel_number,
2627 a_end_order_line_number,
2628 a_creation_date,
2629 a_tp_creation_date,
2630 a_date1,
2631 a_date2,
2632 a_date3,
2633 a_date4,
2634 a_date5,
2635 a_exception_basis);
2636 l_inserted_record := l_inserted_record + 1;
2637
2638 if (b_po_last_refnum(j) <= p_refresh_number) then
2639 --dbms_output.put_line('In complement exception7 ');
2640 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP8,
2641 b_customer_id(j),
2642 b_customer_site_id(j),
2643 b_item_id(j),
2644 b_publisher_id(j),
2645 b_publisher_site_id(j),
2646 null,
2647 null,
2648 b_po_key_date(j));
2649
2650 if (l_total_qty + (l_complement_threshold*b_po_qty(j)/100) < b_po_qty(j)) then
2651
2652 l_exception_type := msc_x_netting_pkg.G_EXCEP8; --fulfillment qty shortfall from yr sup
2653 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
2654 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2655 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2656
2657 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
2658 b_customer_name(j),
2659 b_customer_site_id(j),
2660 b_customer_site_name(j),
2661 b_item_id(j),
2662 b_item_name(j),
2663 b_item_desc(j),
2664 l_exception_type,
2665 l_exception_type_name,
2666 l_exception_group,
2667 l_exception_group_name,
2668 b_trx_id2(j),
2669 l_last_so_trx_id, --b_trx_id1(j),
2670 null, --l_customer_id,
2671 null, --
2672 null, --l_customer_site_id,
2673 null,
2674 b_customer_item_name(j),
2675 b_publisher_id(j),
2676 b_publisher_name(j),
2677 b_publisher_site_id(j),
2678 b_publisher_site_name(j),
2679 b_supplier_item_name(j),
2680 b_po_qty(j), --number1
2681 l_total_qty, --number2
2682 null, --number3
2683 l_complement_threshold,
2684 null, --lead time
2685 null, --l_item_min,
2686 null, --l_item_max,
2687 b_order_number(j),
2688 b_release_number(j),
2689 b_line_number(j),
2690 b_end_order_number(j),
2691 b_end_order_rel_number(j),
2692 b_end_order_line_number(j),
2693 null, --b_po_creation_date(j),
2694 null, --b_so_creation_date(j),
2695 b_po_receipt_date(j),
2696 b_so_receipt_date(j),
2697 b_so_ship_date(j),
2698 b_po_ship_date(j),
2699 null,
2700 l_exception_basis,
2701 a_company_id,
2702 a_company_name,
2703 a_company_site_id,
2704 a_company_site_name,
2705 a_item_id,
2706 a_item_name,
2707 a_item_desc,
2708 a_exception_type,
2709 a_exception_type_name,
2710 a_exception_group,
2711 a_exception_group_name,
2712 a_trx_id1,
2713 a_trx_id2,
2714 a_customer_id,
2715 a_customer_name,
2716 a_customer_site_id,
2717 a_customer_site_name,
2718 a_customer_item_name,
2719 a_supplier_id,
2720 a_supplier_name,
2721 a_supplier_site_id,
2722 a_supplier_site_name,
2723 a_supplier_item_name,
2724 a_number1,
2725 a_number2,
2726 a_number3,
2727 a_threshold,
2728 a_lead_time,
2729 a_item_min_qty,
2730 a_item_max_qty,
2731 a_order_number,
2732 a_release_number,
2733 a_line_number,
2734 a_end_order_number,
2735 a_end_order_rel_number,
2736 a_end_order_line_number,
2737 a_creation_date,
2738 a_tp_creation_date,
2739 a_date1,
2740 a_date2,
2741 a_date3,
2742 a_date4,
2743 a_date5,
2744 a_exception_basis);
2745 l_inserted_record := l_inserted_record + 1;
2746 end if;
2747 end if;
2748 END IF; -- if j=1
2749 end if; -- if total qty
2750 END LOOP;
2751 END IF;
2752 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(7) ||
2753 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2754
2755 /* reset the trxid */
2756 l_generate_complement := null;
2757
2758 --dbms_output.put_line('Exception 8'); --customer centric
2759 open exception_8 ( p_refresh_number);
2760 fetch exception_8 BULK COLLECT INTO
2761 b_trx_id1,
2762 b_publisher_id,
2763 b_publisher_name,
2764 b_publisher_site_id,
2765 b_publisher_site_name,
2766 b_item_id,
2767 b_item_name,
2768 b_item_desc,
2769 b_customer_item_name,
2770 b_customer_item_desc,
2771 b_po_key_date,
2772 b_po_ship_date,
2773 b_po_receipt_date,
2774 b_posting_po_qty,
2775 b_po_qty,
2776 b_tp_po_qty,
2777 b_order_number,
2778 b_release_number,
2779 b_line_number,
2780 b_supplier_id,
2781 b_supplier_name,
2782 b_supplier_site_id,
2783 b_supplier_site_name,
2784 b_supplier_item_name,
2785 b_supplier_item_desc,
2786 b_po_creation_date,
2787 b_po_last_refnum,
2788 b_trx_id2,
2789 b_so_key_date,
2790 b_so_ship_date,
2791 b_so_receipt_date,
2792 b_posting_so_qty,
2793 b_so_qty,
2794 b_tp_so_qty,
2795 b_end_order_number,
2796 b_end_order_rel_number,
2797 b_end_order_line_number,
2798 b_customer_id,
2799 b_customer_name,
2800 b_customer_site_id, --owning org
2801 b_customer_site_name,
2802 b_so_creation_date,
2803 b_so_last_refnum;
2804 CLOSE exception_8;
2805 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
2806 FOR j in 1..b_trx_id1.COUNT
2807 LOOP
2808 --dbms_output.put_line('-----Exception8: Trx id 1 = ' || b_trx_id1(j));
2809 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
2810
2811 --======================================================
2812 -- archive old exception and its complement
2813 --======================================================
2814 msc_x_netting_pkg.add_to_delete_tbl(
2815 b_publisher_id(j),
2816 b_publisher_site_id(j),
2817 null,
2818 null,
2819 b_supplier_id(j),
2820 b_supplier_site_id(j),
2821 b_item_id(j),
2822 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
2823 msc_x_netting_pkg.G_EXCEP8,
2824 b_trx_id1(j),
2825 b_trx_id2(j),
2826 null,
2827 null,
2828 t_company_list,
2829 t_company_site_list,
2830 t_customer_list,
2831 t_customer_site_list,
2832 t_supplier_list,
2833 t_supplier_site_list,
2834 t_item_list,
2835 t_group_list,
2836 t_type_list,
2837 t_trxid1_list,
2838 t_trxid2_list,
2839 t_date1_list,
2840 t_date2_list);
2841
2842 msc_x_netting_pkg.add_to_delete_tbl(
2843 b_supplier_id(j),
2844 b_supplier_site_id(j),
2845 b_publisher_id(j),
2846 b_publisher_site_id(j),
2847 null,
2848 null,
2849 b_item_id(j),
2850 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
2851 msc_x_netting_pkg.G_EXCEP7,
2852 b_trx_id2(j),
2853 b_trx_id1(j),
2854 null,
2855 null,
2856 t_company_list,
2857 t_company_site_list,
2858 t_customer_list,
2859 t_customer_site_list,
2860 t_supplier_list,
2861 t_supplier_site_list,
2862 t_item_list,
2863 t_group_list,
2864 t_type_list,
2865 t_trxid1_list,
2866 t_trxid2_list,
2867 t_date1_list,
2868 t_date2_list);
2869
2870
2871 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP8,
2872 b_publisher_id(j),
2873 b_publisher_site_id(j),
2874 b_item_id(j),
2875 b_supplier_id(j),
2876 b_supplier_site_id(j),
2877 null,
2878 null,
2879 b_po_key_date(j));
2880
2881 l_total_qty := msc_x_netting_pkg.get_total_qty(b_order_number(j),
2882 b_release_number(j),
2883 b_line_number(j),
2884 b_supplier_id(j),
2885 b_supplier_site_id(j),
2886 b_publisher_id(j),
2887 b_publisher_site_id(j),
2888 b_item_id(j));
2889
2890 IF (l_total_qty + (l_threshold1*b_po_qty(j)/100) < b_po_qty(j) ) THEN
2891 --======================================================
2892 -- Clean up the opposite exception and its complement
2893 --======================================================
2894 msc_x_netting_pkg.add_to_delete_tbl(
2895 b_publisher_id(j),
2896 b_publisher_site_id(j),
2897 null,
2898 null,
2899 b_supplier_id(j),
2900 b_supplier_site_id(j),
2901 b_item_id(j),
2902 msc_x_netting_pkg.G_MATERIAL_EXCESS,
2903 msc_x_netting_pkg.G_EXCEP28,
2904 b_trx_id1(j),
2905 b_trx_id2(j),
2906 null,
2907 null,
2908 t_company_list,
2909 t_company_site_list,
2910 t_customer_list,
2911 t_customer_site_list,
2912 t_supplier_list,
2913 t_supplier_site_list,
2914 t_item_list,
2915 t_group_list,
2916 t_type_list,
2917 t_trxid1_list,
2918 t_trxid2_list,
2919 t_date1_list,
2920 t_date2_list);
2921
2922 msc_x_netting_pkg.add_to_delete_tbl(
2923 b_supplier_id(j),
2924 b_supplier_site_id(j),
2925 b_publisher_id(j),
2926 b_publisher_site_id(j),
2927 null,
2928 null,
2929 b_item_id(j),
2930 msc_x_netting_pkg.G_MATERIAL_EXCESS,
2931 msc_x_netting_pkg.G_EXCEP27,
2932 b_trx_id2(j),
2933 b_trx_id1(j),
2934 null,
2935 null,
2936 t_company_list,
2937 t_company_site_list,
2938 t_customer_list,
2939 t_customer_site_list,
2940 t_supplier_list,
2941 t_supplier_site_list,
2942 t_item_list,
2943 t_group_list,
2944 t_type_list,
2945 t_trxid1_list,
2946 t_trxid2_list,
2947 t_date1_list,
2948 t_date2_list);
2949
2950
2951 IF (j=1 OR (b_publisher_id(j -1) <> b_publisher_id(j) OR
2952 b_publisher_site_id(j-1) <> b_publisher_site_id(j) OR
2953 b_customer_id(j-1) <> b_customer_id(j) OR
2954 b_customer_site_id(j-1) <> b_customer_site_id(j) OR
2955 b_item_id(j-1) <> b_item_id(j) OR
2956 b_order_number(j-1) <> b_order_number(j) OR
2957 b_release_number(j-1) <> b_release_number(j) OR
2958 b_line_number(j-1) <> b_line_number(j) )) THEN
2959
2960
2961 -------------------------------------------------------------------------
2962 -- get the latest SO to populate the exception
2963 -------------------------------------------------------------------------
2964 BEGIN
2965 select transaction_id, receipt_date, ship_date, order_number, line_number, release_number, last_refresh_number
2966 into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
2967 b_end_order_line_number(j), b_end_order_rel_number(j), b_so_last_refnum(j)
2968 from msc_sup_dem_entries
2969 where publisher_id = b_supplier_id(j)
2970 and publisher_site_id = b_supplier_site_id(j)
2971 and customer_id = b_publisher_id(j)
2972 and customer_site_id = b_publisher_site_id(j)
2973 and inventory_item_id = b_item_id(j)
2974 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
2975 and end_order_number = b_order_number(j)
2976 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
2977 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
2978 and key_date = (select max(key_date)
2979 from msc_sup_dem_entries
2980 where publisher_id = b_supplier_id(j)
2981 and publisher_site_id = b_supplier_site_id(j)
2982 and customer_id = b_publisher_id(j)
2983 and customer_site_id = b_publisher_site_id(j)
2984 and inventory_item_id = b_item_id(j)
2985 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
2986 and end_order_number = b_order_number(j)
2987 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
2988 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1) );
2989 EXCEPTION
2990 when others then
2991 null;
2992 --dbms_output.put_line('Error ' || sqlerrm);
2993 END;
2994
2995 SELECT max(last_refresh_number)
2996 into b_so_last_refnum(j)
2997 from msc_sup_dem_entries
2998 where publisher_id = b_supplier_id(j)
2999 and publisher_site_id = b_supplier_site_id(j)
3000 and customer_id = b_publisher_id(j)
3001 and customer_site_id = b_publisher_site_id(j)
3002 and inventory_item_id = b_item_id(j)
3003 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
3004 and end_order_number = b_order_number(j)
3005 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
3006 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1);
3007
3008
3009 --dbms_output.put_line('trx id ' || l_last_so_trx_id || ' last ref num ' || b_so_last_refnum(j) || ' p_refnum ' || p_refresh_number);
3010
3011 --======================================================
3012 -- archive old exception and its complement
3013 --======================================================
3014 msc_x_netting_pkg.add_to_delete_tbl(
3015 b_publisher_id(j),
3016 b_publisher_site_id(j),
3017 null,
3018 null,
3019 b_supplier_id(j),
3020 b_supplier_site_id(j),
3021 b_item_id(j),
3022 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
3023 msc_x_netting_pkg.G_EXCEP8,
3024 b_trx_id1(j),
3025 l_last_so_trx_id,
3026 null,
3027 null,
3028 t_company_list,
3029 t_company_site_list,
3030 t_customer_list,
3031 t_customer_site_list,
3032 t_supplier_list,
3033 t_supplier_site_list,
3034 t_item_list,
3035 t_group_list,
3036 t_type_list,
3037 t_trxid1_list,
3038 t_trxid2_list,
3039 t_date1_list,
3040 t_date2_list);
3041
3042 msc_x_netting_pkg.add_to_delete_tbl(
3043 b_supplier_id(j),
3044 b_supplier_site_id(j),
3045 b_publisher_id(j),
3046 b_publisher_site_id(j),
3047 null,
3048 null,
3049 b_item_id(j),
3050 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
3051 msc_x_netting_pkg.G_EXCEP7,
3052 l_last_so_trx_id,
3053 b_trx_id1(j),
3054 null,
3055 null,
3056 t_company_list,
3057 t_company_site_list,
3058 t_customer_list,
3059 t_customer_site_list,
3060 t_supplier_list,
3061 t_supplier_site_list,
3062 t_item_list,
3063 t_group_list,
3064 t_type_list,
3065 t_trxid1_list,
3066 t_trxid2_list,
3067 t_date1_list,
3068 t_date2_list);
3069
3070 msc_x_netting_pkg.add_to_delete_tbl(
3071 b_publisher_id(j),
3072 b_publisher_site_id(j),
3073 null,
3074 null,
3075 b_supplier_id(j),
3076 b_supplier_site_id(j),
3077 b_item_id(j),
3078 msc_x_netting_pkg.G_MATERIAL_EXCESS,
3079 msc_x_netting_pkg.G_EXCEP28,
3080 b_trx_id1(j),
3081 l_last_so_trx_id,
3082 null,
3083 null,
3084 t_company_list,
3085 t_company_site_list,
3086 t_customer_list,
3087 t_customer_site_list,
3088 t_supplier_list,
3089 t_supplier_site_list,
3090 t_item_list,
3091 t_group_list,
3092 t_type_list,
3093 t_trxid1_list,
3094 t_trxid2_list,
3095 t_date1_list,
3096 t_date2_list);
3097
3098 msc_x_netting_pkg.add_to_delete_tbl(
3099 b_supplier_id(j),
3100 b_supplier_site_id(j),
3101 b_publisher_id(j),
3102 b_publisher_site_id(j),
3103 null,
3104 null,
3105 b_item_id(j),
3106 msc_x_netting_pkg.G_MATERIAL_EXCESS,
3107 msc_x_netting_pkg.G_EXCEP27,
3108 l_last_so_trx_id,
3109 b_trx_id1(j),
3110 null,
3111 null,
3112 t_company_list,
3113 t_company_site_list,
3114 t_customer_list,
3115 t_customer_site_list,
3116 t_supplier_list,
3117 t_supplier_site_list,
3118 t_item_list,
3119 t_group_list,
3120 t_type_list,
3121 t_trxid1_list,
3122 t_trxid2_list,
3123 t_date1_list,
3124 t_date2_list);
3125
3126 --------------------------------------------------------------------------
3127 -- get the shipping control
3128 ---------------------------------------------------------------------------
3129 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
3130 b_publisher_site_name(j),
3131 b_supplier_name(j),
3132 b_supplier_site_name(j));
3133
3134 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3135 nvl(l_shipping_control,1));
3136
3137 l_exception_type := msc_x_netting_pkg.G_EXCEP8; -- fulfillment qty shortfall from sup for your po
3138 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
3139 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3140 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3141
3142
3143 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3144 b_publisher_name(j),
3145 b_publisher_site_id(j),
3146 b_publisher_site_name(j),
3147 b_item_id(j),
3148 b_item_name(j),
3149 b_item_desc(j),
3150 l_exception_type,
3151 l_exception_type_name,
3152 l_exception_group,
3153 l_exception_group_name,
3154 b_trx_id1(j),
3155 l_last_so_trx_id, --b_trx_id2(j),
3156 null, --l_customer_id,
3157 null,
3158 null,-- l_customer_site_id,
3159 null,
3160 b_customer_item_name(j),
3161 b_supplier_id(j),
3162 b_supplier_name(j),
3163 b_supplier_site_id(j),
3164 b_supplier_site_name(j),
3165 b_supplier_item_name(j),
3166 b_po_qty(j), --number1
3167 l_total_qty, --number2
3168 null, --number3
3169 l_threshold1,
3170 null, --lead time
3171 null, --l_item_min,
3172 null, --l_item_max,
3173 b_order_number(j),
3174 b_release_number(j),
3175 b_line_number(j),
3176 b_end_order_number(j),
3177 b_end_order_rel_number(j),
3178 b_end_order_line_number(j),
3179 null, --b_po_creation_date(j),
3180 null, --b_so_creation_date(j),
3181 b_po_receipt_date(j),
3182 b_so_receipt_date(j),
3183 b_so_ship_date(j),
3184 b_po_ship_date(j),
3185 null,
3186 l_exception_basis,
3187 a_company_id,
3188 a_company_name,
3189 a_company_site_id,
3190 a_company_site_name,
3191 a_item_id,
3192 a_item_name,
3193 a_item_desc,
3194 a_exception_type,
3195 a_exception_type_name,
3196 a_exception_group,
3197 a_exception_group_name,
3198 a_trx_id1,
3199 a_trx_id2,
3200 a_customer_id,
3201 a_customer_name,
3202 a_customer_site_id,
3203 a_customer_site_name,
3204 a_customer_item_name,
3205 a_supplier_id,
3206 a_supplier_name,
3207 a_supplier_site_id,
3208 a_supplier_site_name,
3209 a_supplier_item_name,
3210 a_number1,
3211 a_number2,
3212 a_number3,
3213 a_threshold,
3214 a_lead_time,
3215 a_item_min_qty,
3216 a_item_max_qty,
3217 a_order_number,
3218 a_release_number,
3219 a_line_number,
3220 a_end_order_number,
3221 a_end_order_rel_number,
3222 a_end_order_line_number,
3223 a_creation_date,
3224 a_tp_creation_date,
3225 a_date1,
3226 a_date2,
3227 a_date3,
3228 a_date4,
3229 a_date5,
3230 a_exception_basis);
3231 l_inserted_record := l_inserted_record + 1;
3232
3233 if (b_so_last_refnum(j) <= p_refresh_number) then
3234 --dbms_output.put_line('In complement 8');
3235
3236 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP7,
3237 b_supplier_id(j),
3238 b_supplier_site_id(j),
3239 b_item_id(j),
3240 null,
3241 null,
3242 b_publisher_id(j),
3243 b_publisher_site_id(j),
3244 b_so_key_date(j));
3245 if (l_total_qty + (l_complement_threshold* b_po_qty(j)/100) < b_po_qty(j) ) THEN
3246
3247 l_exception_type := msc_x_netting_pkg.G_EXCEP7;
3248 l_exception_group := msc_x_netting_pkg.G_MATERIAL_SHORTAGE;
3249 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3250 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3251
3252 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
3253 b_supplier_name(j),
3254 b_supplier_site_id(j),
3255 b_supplier_site_name(j),
3256 b_item_id(j),
3257 b_item_name(j),
3258 b_item_desc(j),
3259 l_exception_type,
3260 l_exception_type_name,
3261 l_exception_group,
3262 l_exception_group_name,
3263 l_last_so_trx_id, --b_trx_id2(j),
3264 b_trx_id1(j),
3265 b_publisher_id(j),
3266 b_publisher_name(j),
3267 b_publisher_site_id(j),
3268 b_publisher_site_name(j),
3269 b_customer_item_name(j),
3270 null, --l_supplier_id,
3271 null,
3272 null, --l_supplier_site_id,
3273 null,
3274 b_supplier_item_name(j), --item name
3275 l_total_qty, --number1
3276 b_tp_po_qty(j), --number2
3277 null, --number3
3278 l_complement_threshold,
3279 null, --lead time
3280 null, --l_item_min,
3281 null, --l_item_max,
3282 b_order_number(j),
3283 b_release_number(j),
3284 b_line_number(j),
3285 b_end_order_number(j),
3286 b_end_order_rel_number(j),
3287 b_end_order_line_number(j),
3288 null, --b_so_creation_date(j),
3289 null, --b_po_creation_date(j),
3290 b_so_receipt_date(j),
3291 b_po_receipt_date(j),
3292 b_so_ship_date(j),
3293 b_po_ship_date(j),
3294 null,
3295 l_exception_basis,
3296 a_company_id,
3297 a_company_name,
3298 a_company_site_id,
3299 a_company_site_name,
3300 a_item_id,
3301 a_item_name,
3302 a_item_desc,
3303 a_exception_type,
3304 a_exception_type_name,
3305 a_exception_group,
3306 a_exception_group_name,
3307 a_trx_id1,
3308 a_trx_id2,
3309 a_customer_id,
3310 a_customer_name,
3311 a_customer_site_id,
3312 a_customer_site_name,
3313 a_customer_item_name,
3314 a_supplier_id,
3315 a_supplier_name,
3316 a_supplier_site_id,
3317 a_supplier_site_name,
3318 a_supplier_item_name,
3319 a_number1,
3320 a_number2,
3321 a_number3,
3322 a_threshold,
3323 a_lead_time,
3324 a_item_min_qty,
3325 a_item_max_qty,
3326 a_order_number,
3327 a_release_number,
3328 a_line_number,
3329 a_end_order_number,
3330 a_end_order_rel_number,
3331 a_end_order_line_number,
3332 a_creation_date,
3333 a_tp_creation_date,
3334 a_date1,
3335 a_date2,
3336 a_date3,
3337 a_date4,
3338 a_date5,
3339 a_exception_basis);
3340 l_inserted_record := l_inserted_record + 1;
3341 end if;
3342 end if;
3343 END IF; -- if j=1
3344 end if;
3345 END LOOP;
3346 END IF;
3347
3348 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(8) ||
3349 ':' ||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3350
3351 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
3352 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_MATERIAL_SHORTAGE) || ':' || l_inserted_record);
3353
3354
3355 -- added exception handler
3356 EXCEPTION
3357 WHEN OTHERS THEN
3358 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING2_PKG.Compute_Material_Shortage');
3359 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3360 --dbms_output.put_line('error in material shortage ' || sqlerrm);
3361 END Compute_Material_Shortage;
3362
3363
3364 --==================================================================
3365 --COMPUTE_MATERIAL_EXCESS (supply planning)
3366 --==================================================================
3367 --======================================================================
3368 PROCEDURE COMPUTE_MATERIAL_EXCESS( p_refresh_number IN Number,
3369 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3370 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3371 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3372 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3373 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3374 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3375 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3376 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3377 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3378 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3379 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3380 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3381 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3382 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3383 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
3384 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3385 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
3386 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3387 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
3388 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
3389 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3390 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
3391 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3392 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
3393 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3394 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3395 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3396 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
3397 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3398 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
3399 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
3400 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3401 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
3402 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3403 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
3404 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
3405 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3406 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3407 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3408 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3409 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3410 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3411 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
3412 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
3413 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
3414 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
3415 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
3416 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
3417 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
3418 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3419 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3420 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3421 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3422 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3423 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3424 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
3425 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
3426
3427
3428 b_trx_id1 msc_x_netting_pkg.number_arr;
3429 b_trx_id2 msc_x_netting_pkg.number_arr;
3430 b_publisher_id msc_x_netting_pkg.number_arr;
3431 b_publisher_site_id msc_x_netting_pkg.number_arr;
3432 b_item_id msc_x_netting_pkg.number_arr;
3433 b_po_qty msc_x_netting_pkg.number_arr;
3434 b_so_qty msc_x_netting_pkg.number_arr;
3435 b_tp_po_qty msc_x_netting_pkg.number_arr;
3436 b_tp_so_qty msc_x_netting_pkg.number_arr;
3437 b_posting_po_qty msc_x_netting_pkg.number_arr;
3438 b_posting_so_qty msc_x_netting_pkg.number_arr;
3439 b_customer_id msc_x_netting_pkg.number_arr;
3440 b_customer_site_id msc_x_netting_pkg.number_arr;
3441 b_supplier_id msc_x_netting_pkg.number_arr;
3442 b_supplier_site_id msc_x_netting_pkg.number_arr;
3443 b_po_last_refnum msc_x_netting_pkg.number_arr;
3444 b_so_last_refnum msc_x_netting_pkg.number_arr;
3445 b_po_key_date msc_x_netting_pkg.date_arr;
3446 b_so_key_date msc_x_netting_pkg.date_arr;
3447 b_po_receipt_date msc_x_netting_pkg.date_arr;
3448 b_so_receipt_date msc_x_netting_pkg.date_arr;
3449 b_po_ship_date msc_x_netting_pkg.date_arr;
3450 b_so_ship_date msc_x_netting_pkg.date_arr;
3451 b_po_creation_date msc_x_netting_pkg.date_arr;
3452 b_so_creation_date msc_x_netting_pkg.date_arr;
3453 b_item_name msc_x_netting_pkg.itemnameList;
3454 b_item_desc msc_x_netting_pkg.itemdescList;
3455 b_publisher_name msc_x_netting_pkg.publisherList;
3456 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
3457 b_supplier_name msc_x_netting_pkg.supplierList;
3458 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
3459 b_supplier_item_name msc_x_netting_pkg.itemnameList;
3460 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
3461 b_customer_name msc_x_netting_pkg.customerList;
3462 b_customer_site_name msc_x_netting_pkg.custsiteList;
3463 b_customer_item_name msc_x_netting_pkg.itemnameList;
3464 b_customer_item_desc msc_x_netting_pkg.itemdescList;
3465 b_order_number msc_x_netting_pkg.ordernumberList;
3466 b_release_number msc_x_netting_pkg.releasenumList;
3467 b_line_number msc_x_netting_pkg.linenumList;
3468 b_end_order_number msc_x_netting_pkg.ordernumberList;
3469 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
3470 b_end_order_line_number msc_x_netting_pkg.linenumList;
3471 b_refresh_number msc_x_netting_pkg.number_arr;
3472
3473 l_start_date Date;
3474 l_end_date Date;
3475 l_bucket_type Number;
3476 l_total_demand Number := 0;
3477 l_tp_total_demand Number := 0;
3478 l_total_supply Number := 0;
3479 l_tp_total_supply Number := 0;
3480 l_total_qty Number;
3481 l_exception_type Number;
3482 l_obs_exception Number;
3483 l_exception_group Number;
3484 l_generate_complement Boolean;
3485 l_updated Number;
3486 l_complement_threshold Number;
3487 l_cutoff_ref_num Number;
3488 l_threshold1 Number;
3489 l_threshold2 Number;
3490 l_exception_type_name fnd_lookup_values.meaning%type;
3491 l_exception_group_name fnd_lookup_values.meaning%type;
3492 l_posting_total_demand Number := 0;
3493 l_posting_total_supply Number := 0;
3494 l_sum Number := 0;
3495 l_item_desc msc_sup_dem_entries.item_description%type;
3496 l_shipping_control Number;
3497 l_exception_basis msc_x_exception_details.exception_basis%type;
3498
3499 l_last_so_trx_id Number;
3500 l_receipt_date Date;
3501 l_ship_date Date;
3502 l_order_number msc_sup_dem_entries.order_number%type;
3503 l_line_number msc_sup_dem_entries.line_number%type;
3504 l_release_number msc_sup_dem_entries.release_number%type;
3505 l_count Number:=0;
3506 l_item_name msc_sup_dem_entries.item_name%type;
3507 l_customer_item_name msc_sup_dem_entries.customer_item_name%type;
3508 l_supplier_item_name msc_sup_dem_entries.supplier_item_name%type;
3509 l_publisher_name msc_sup_dem_entries.publisher_name%type;
3510 l_publisher_site_name msc_sup_dem_entries.publisher_site_name%type;
3511 l_supplier_name msc_sup_dem_entries.supplier_name%type;
3512 l_supplier_site_name msc_sup_dem_entries.supplier_site_name%type;
3513 l_customer_name msc_sup_dem_entries.customer_name%type;
3514 l_customer_site_name msc_sup_dem_entries.customer_site_name%type;
3515 l_publisher_id Number;
3516 l_publisher_site_id Number;
3517 l_customer_id Number;
3518 l_customer_site_id Number;
3519 l_supplier_id Number;
3520 l_supplier_site_id Number;
3521 l_item_id Number;
3522
3523
3524 l_pair Number:= 0;
3525 l_insert Number := 0;
3526 l_inserted_record Number := 0;
3527
3528 b_bucket_index msc_x_netting_pkg.number_arr;
3529 b_bkt_start_date msc_x_netting_pkg.date_arr;
3530 b_bkt_end_date msc_x_netting_pkg.date_arr;
3531 b_order_type msc_x_netting_pkg.number_arr;
3532 b_total_quantity msc_x_netting_pkg.number_arr;
3533 b_tp_total_quantity msc_x_netting_pkg.number_arr;
3534 b_posting_total_quantity msc_x_netting_pkg.number_arr;
3535
3536 BEGIN
3537
3538 --dbms_output.put_line('Exception 25');
3539
3540 open exception_25(p_refresh_number);
3541 fetch exception_25 BULK COLLECT INTO
3542 b_publisher_id,
3543 b_publisher_site_id,
3544 b_supplier_id,
3545 b_supplier_site_id,
3546 b_customer_id,
3547 b_customer_site_id,
3548 b_item_id,
3549 b_bucket_index,
3550 b_bkt_start_date,
3551 b_bkt_end_date,
3552 b_order_type,
3553 b_publisher_name,
3554 b_publisher_site_name,
3555 b_supplier_name,
3556 b_supplier_site_name,
3557 b_customer_name,
3558 b_customer_site_name,
3559 b_item_name,
3560 b_item_desc,
3561 b_supplier_item_name,
3562 b_customer_item_name,
3563 b_total_quantity,
3564 b_tp_total_quantity,
3565 b_posting_total_quantity,
3566 b_refresh_number;
3567 CLOSE exception_25;
3568
3569 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
3570 FOR j in 1..b_item_id.COUNT
3571 LOOP
3572
3573
3574 IF (j = 1 or b_publisher_id(j) <> b_publisher_id(j-1) OR
3575 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
3576 b_customer_id(j) <> b_customer_id(j-1) OR
3577 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
3578 b_supplier_id(j) <> b_supplier_id(j-1) OR
3579 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
3580 b_item_id(j) <> b_item_id(j-1)) THEN
3581
3582
3583 --======================================================
3584 -- archive old exception and its complement
3585 --======================================================
3586 msc_x_netting_pkg.add_to_delete_tbl(
3587 b_publisher_id(j),
3588 b_publisher_site_id(j),
3589 b_customer_id(j),
3590 b_customer_site_id(j),
3591 null,
3592 null,
3593 b_item_id(j),
3594 msc_x_netting_pkg.G_MATERIAL_EXCESS,
3595 msc_x_netting_pkg.G_EXCEP25,
3596 null,
3597 null,
3598 null,
3599 null,
3600 t_company_list,
3601 t_company_site_list,
3602 t_customer_list,
3603 t_customer_site_list,
3604 t_supplier_list,
3605 t_supplier_site_list,
3606 t_item_list,
3607 t_group_list,
3608 t_type_list,
3609 t_trxid1_list,
3610 t_trxid2_list,
3611 t_date1_list,
3612 t_date2_list);
3613
3614
3615 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP25,
3616 b_publisher_id(j),
3617 b_publisher_site_id(j),
3618 b_item_id(j),
3619 null,
3620 null,
3621 b_customer_id(j),
3622 b_customer_site_id(j),
3623 null);
3624
3625 --------------------------------------------------------------------------
3626 -- get the shipping control
3627 ---------------------------------------------------------------------------
3628 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
3629 b_customer_site_name(j),
3630 b_publisher_name(j),
3631 b_publisher_site_name(j));
3632
3633 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3634 nvl(l_shipping_control,1));
3635 END IF;
3636
3637
3638 IF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
3639 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
3640 b_customer_id(j) = b_customer_id(j-1) AND
3641 b_customer_site_id(j) = b_customer_site_id(j-1) AND
3642 b_supplier_id(j) = b_supplier_id(j-1) AND
3643 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
3644 b_item_id(j) = b_item_id(j-1) and
3645 b_bucket_index(j) = b_bucket_index(j-1) and
3646 b_bkt_start_date(j) = b_bkt_start_date(j-1)) THEN
3647
3648 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
3649 l_total_demand := b_total_quantity(j-1);
3650 l_tp_total_demand := b_tp_total_quantity(j-1);
3651 l_posting_total_demand := b_posting_total_quantity(j-1);
3652 ELSIF (b_order_type(j-1)= msc_x_netting_pkg.SUPPLY_COMMIT) THEN
3653 l_total_supply := b_total_quantity(j-1);
3654 l_tp_total_supply := b_tp_total_quantity(j-1);
3655 l_posting_total_supply := b_posting_total_quantity(j-1);
3656 END IF;
3657 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
3658 l_total_demand := b_total_quantity(j);
3659 l_tp_total_demand := b_tp_total_quantity(j);
3660 l_posting_total_demand := b_posting_total_quantity(j);
3661 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
3662 l_total_supply := b_total_quantity(j);
3663 l_tp_total_supply := b_tp_total_quantity(j);
3664 l_posting_total_supply := b_posting_total_quantity(j);
3665 END IF;
3666 l_pair := 1;
3667 l_insert := 1;
3668 l_start_date := b_bkt_start_date(j-1);
3669 l_end_date := b_bkt_end_date(j-1);
3670 l_publisher_id := b_publisher_id(j-1);
3671 l_publisher_site_id := b_publisher_site_id(j-1);
3672 l_customer_id := b_customer_id(j-1);
3673 l_customer_site_id := b_customer_site_id(j-1);
3674 l_supplier_id := b_supplier_id(j-1);
3675 l_supplier_site_id := b_supplier_site_id(j-1);
3676 l_item_id := b_item_id(j-1);
3677 l_publisher_name := b_publisher_name(j-1);
3678 l_publisher_site_name := b_publisher_site_name(j-1);
3679 l_customer_name := b_customer_name(j-1);
3680 l_customer_site_name := b_customer_site_name(j-1);
3681 l_supplier_name := b_supplier_name(j-1);
3682 l_supplier_site_name := b_supplier_site_name(j-1);
3683
3684
3685
3686 --dbms_output.put_line('equal insert ' || b_bkt_start_date(j));
3687 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
3688 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
3689 b_customer_id(j) = b_customer_id(j-1) AND
3690 b_customer_site_id(j) = b_customer_site_id(j-1) AND
3691 b_supplier_id(j) = b_supplier_id(j-1) AND
3692 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
3693 b_item_id(j) = b_item_id(j-1) AND
3694 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair = 1) THEN
3695 l_pair := 0;
3696 l_insert := 0;
3697 --dbms_output.put_line('2 no insert with previous line l_pair = 1' || b_bkt_start_date(j));
3698
3699 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
3700 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
3701 b_customer_id(j) = b_customer_id(j-1) AND
3702 b_customer_site_id(j) = b_customer_site_id(j-1) AND
3703 b_supplier_id(j) = b_supplier_id(j-1) AND
3704 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
3705 b_item_id(j) = b_item_id(j-1) AND
3706 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair <> 1) THEN
3707
3708 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
3709 l_total_demand := b_total_quantity(j-1);
3710 l_tp_total_demand := b_tp_total_quantity(j-1);
3711 l_posting_total_demand := b_posting_total_quantity(j-1);
3712 l_total_supply := 0;
3713 l_tp_total_supply := 0;
3714 l_posting_total_supply := 0;
3715 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
3716 l_total_demand := 0;
3717 l_tp_total_demand := 0;
3718 l_posting_total_demand := 0;
3719 l_total_supply := b_total_quantity(j-1);
3720 l_tp_total_supply := b_tp_total_quantity(j-1);
3721 l_posting_total_supply := b_posting_total_quantity(j-1);
3722 END IF;
3723
3724 l_pair := 0;
3725 l_insert := 1;
3726 l_start_date := b_bkt_start_date(j-1);
3727 l_end_date := b_bkt_end_date(j-1);
3728 l_publisher_id := b_publisher_id(j-1);
3729 l_publisher_site_id := b_publisher_site_id(j-1);
3730 l_customer_id := b_customer_id(j-1);
3731 l_customer_site_id := b_customer_site_id(j-1);
3732 l_supplier_id := b_supplier_id(j-1);
3733 l_supplier_site_id := b_supplier_site_id(j-1);
3734 l_item_id := b_item_id(j-1);
3735 l_publisher_name := b_publisher_name(j-1);
3736 l_publisher_site_name := b_publisher_site_name(j-1);
3737 l_customer_name := b_customer_name(j-1);
3738 l_customer_site_name := b_customer_site_name(j-1);
3739 l_supplier_name := b_supplier_name(j-1);
3740 l_supplier_site_name := b_supplier_site_name(j-1);
3741
3742
3743 --dbms_output.put_line('3 not equal insert ' || b_bkt_start_date(j));
3744 ELSIF (j > 1 and l_pair = 1 ) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
3745 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
3746 b_customer_id(j) <> b_customer_id(j-1) OR
3747 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
3748 b_supplier_id(j) <> b_supplier_id(j-1) OR
3749 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
3750 b_item_id(j) <> b_item_id(j-1) ) THEN
3751
3752 l_pair := 0;
3753 l_insert := 0;
3754
3755 --dbms_output.put_line('4 diff no insert' || b_bkt_start_date(j) || ' ps ' || b_publisher_site_id(j) || ' cs ' || b_customer_site_id(j));
3756 ELSIF (j > 1 and l_pair <> 1 ) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
3757 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
3758 b_customer_id(j) <> b_customer_id(j-1) OR
3759 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
3760 b_supplier_id(j) <> b_supplier_id(j-1) OR
3761 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
3762 b_item_id(j) <> b_item_id(j-1) ) THEN
3763
3764 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
3765 l_total_demand := b_total_quantity(j-1);
3766 l_tp_total_demand := b_tp_total_quantity(j-1);
3767 l_posting_total_demand := b_posting_total_quantity(j-1);
3768 l_total_supply := 0;
3769 l_tp_total_supply := 0;
3770 l_posting_total_supply := 0;
3771 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
3772 l_total_demand := 0;
3773 l_tp_total_demand := 0;
3774 l_posting_total_demand := 0;
3775 l_total_supply := b_total_quantity(j-1);
3776 l_tp_total_supply := b_tp_total_quantity(j-1);
3777 l_posting_total_supply := b_posting_total_quantity(j-1);
3778 END IF;
3779
3780 l_pair := 0;
3781 l_insert := 1;
3782 l_start_date := b_bkt_start_date(j-1);
3783 l_end_date := b_bkt_end_date(j-1);
3784 l_publisher_id := b_publisher_id(j-1);
3785 l_publisher_site_id := b_publisher_site_id(j-1);
3786 l_customer_id := b_customer_id(j-1);
3787 l_customer_site_id := b_customer_site_id(j-1);
3788 l_supplier_id := b_supplier_id(j-1);
3789 l_supplier_site_id := b_supplier_site_id(j-1);
3790 l_item_id := b_item_id(j-1);
3791 l_publisher_name := b_publisher_name(j-1);
3792 l_publisher_site_name := b_publisher_site_name(j-1);
3793 l_customer_name := b_customer_name(j-1);
3794 l_customer_site_name := b_customer_site_name(j-1);
3795 l_supplier_name := b_supplier_name(j-1);
3796 l_supplier_site_name := b_supplier_site_name(j-1);
3797
3798
3799 --dbms_output.put_line('5 diff with insert' || b_bkt_start_date(j) || ' ps ' || b_publisher_site_id(j) || ' cs ' || b_customer_site_id(j));
3800
3801 ELSIF (j = 1 AND b_bkt_start_date.COUNT = 1) THEN
3802 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
3803 l_total_demand := b_total_quantity(j);
3804 l_tp_total_demand := b_tp_total_quantity(j);
3805 l_posting_total_demand := b_posting_total_quantity(j);
3806 l_total_supply := 0;
3807 l_tp_total_supply := 0;
3808 l_posting_total_supply := 0;
3809 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
3810 l_total_demand := 0;
3811 l_tp_total_demand := 0;
3812 l_posting_total_demand := 0;
3813 l_total_supply := b_total_quantity(j);
3814 l_tp_total_supply := b_tp_total_quantity(j);
3815 l_posting_total_supply := b_posting_total_quantity(j);
3816 END IF;
3817
3818 l_pair := 0;
3819 l_insert := 1;
3820 l_start_date := b_bkt_start_date(j);
3821 l_end_date := b_bkt_end_date(j);
3822 l_publisher_id := b_publisher_id(j);
3823 l_publisher_site_id := b_publisher_site_id(j);
3824 l_customer_id := b_customer_id(j);
3825 l_customer_site_id := b_customer_site_id(j);
3826 l_supplier_id := b_supplier_id(j);
3827 l_supplier_site_id := b_supplier_site_id(j);
3828 l_item_id := b_item_id(j);
3829 l_publisher_name := b_publisher_name(j);
3830 l_publisher_site_name := b_publisher_site_name(j);
3831 l_customer_name := b_customer_name(j);
3832 l_customer_site_name := b_customer_site_name(j);
3833 l_supplier_name := b_supplier_name(j);
3834 l_supplier_site_name := b_supplier_site_name(j);
3835
3836 --dbms_output.put_line('5 only one line' || b_bkt_start_date(j));
3837 END IF;
3838
3839 FND_FILE.PUT_LINE(FND_FILE.LOG, '25:supply ' || l_total_supply || ' demand ' || l_total_demand || ' date ' ||
3840 l_start_date || 'cutoff ' || l_cutoff_ref_num );
3841
3842 --dbms_output.put_line( '25:supply ' || l_total_supply || ' demand ' || l_total_demand || ' date ' ||
3843 -- l_start_date || 'cutoff ' || l_cutoff_ref_num || ' pair ' || l_pair);
3844 IF (((j > 1 and l_insert = 1) OR (b_bucket_index.COUNT = 1)) and
3845 ((l_total_supply - l_tp_total_demand) > (l_tp_total_demand * l_threshold2/100)))
3846 and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
3847 THEN
3848
3849
3850 --======================================================
3851 -- clean up the opposite exception and its complement
3852 --======================================================
3853 msc_x_netting_pkg.add_to_delete_tbl(
3854 l_publisher_id, --b_publisher_id(j),
3855 l_publisher_site_id, --b_publisher_site_id(j),
3856 l_customer_id, --b_customer_id(j),
3857 l_customer_site_id, --b_customer_site_id(j),
3858 null,
3859 null,
3860 l_item_id, --b_item_id(j),
3861 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
3862 msc_x_netting_pkg.G_EXCEP5,
3863 null,
3864 null,
3865 l_start_date,
3866 l_end_date,
3867 t_company_list,
3868 t_company_site_list,
3869 t_customer_list,
3870 t_customer_site_list,
3871 t_supplier_list,
3872 t_supplier_site_list,
3873 t_item_list,
3874 t_group_list,
3875 t_type_list,
3876 t_trxid1_list,
3877 t_trxid2_list,
3878 t_date1_list,
3879 t_date2_list);
3880
3881
3882 l_exception_type := msc_x_netting_pkg.G_EXCEP25;--Cust order fcst < your allocated supply
3883 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
3884 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3885 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3886
3887 msc_x_netting_pkg.add_to_exception_tbl(
3888 l_publisher_id, --b_publisher_id(j),
3889 l_publisher_name, --b_publisher_name(j),
3890 l_publisher_site_id, --b_publisher_site_id(j),
3891 l_publisher_site_name, --b_publisher_site_name(j),
3892 l_item_id, --b_item_id(j),
3893 l_item_name, --b_item_name(j),
3894 l_item_desc,
3895 l_exception_type,
3896 l_exception_type_name,
3897 l_exception_group,
3898 l_exception_group_name,
3899 null, --trx_id1,
3900 null, --trx_id2,
3901 l_customer_id, --b_customer_id(j),
3902 l_customer_name, --b_customer_name(j),
3903 l_customer_site_id, --b_customer_site_id(j),
3904 l_customer_site_name, --b_customer_site_name(j),
3905 l_customer_item_name, --b_customer_item_name(j),
3906 null, --l_supplier_id
3907 null,
3908 null, --l_supplier_site_id
3909 null,
3910 l_supplier_item_name, --b_supplier_item_name(j),
3911 l_total_supply,
3912 l_tp_total_demand,
3913 null,
3914 l_threshold2,
3915 null, --lead time
3916 null, --item min
3917 null, --item_max
3918 null, --l_order_number,
3919 null, --l_release_number,
3920 null, --l_line_number,
3921 null, --l_end_order_number,
3922 null, --l_end_order_rel_number,
3923 null, --l_end_order_line_number,
3924 null, --b_so_creation_date(j),
3925 null, --b_po_creation_date(j),
3926 l_start_date,
3927 l_end_date,
3928 null, --ship_date(j),
3929 null, --ship_date(j),
3930 null,
3931 l_exception_basis,
3932 a_company_id,
3933 a_company_name,
3934 a_company_site_id,
3935 a_company_site_name,
3936 a_item_id,
3937 a_item_name,
3938 a_item_desc,
3939 a_exception_type,
3940 a_exception_type_name,
3941 a_exception_group,
3942 a_exception_group_name,
3943 a_trx_id1,
3944 a_trx_id2,
3945 a_customer_id,
3946 a_customer_name,
3947 a_customer_site_id,
3948 a_customer_site_name,
3949 a_customer_item_name,
3950 a_supplier_id,
3951 a_supplier_name,
3952 a_supplier_site_id,
3953 a_supplier_site_name,
3954 a_supplier_item_name,
3955 a_number1,
3956 a_number2,
3957 a_number3,
3958 a_threshold,
3959 a_lead_time,
3960 a_item_min_qty,
3961 a_item_max_qty,
3962 a_order_number,
3963 a_release_number,
3964 a_line_number,
3965 a_end_order_number,
3966 a_end_order_rel_number,
3967 a_end_order_line_number,
3968 a_creation_date,
3969 a_tp_creation_date,
3970 a_date1,
3971 a_date2,
3972 a_date3,
3973 a_date4,
3974 a_date5,
3975 a_exception_basis);
3976 l_inserted_record := l_inserted_record + 1;
3977
3978
3979 END IF; --compute exception
3980
3981 /*------------------------------------------------------------------
3982 Loop for the last record if require to insert
3983 ------------------------------------------------------------------*/
3984 IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
3985 l_insert := 1;
3986 l_start_date := b_bkt_start_date(j);
3987 l_end_date := b_bkt_end_date(j);
3988 l_publisher_id := b_publisher_id(j);
3989 l_publisher_site_id := b_publisher_site_id(j);
3990 l_customer_id := b_customer_id(j);
3991 l_customer_site_id := b_customer_site_id(j);
3992 l_supplier_id := b_supplier_id(j);
3993 l_supplier_site_id := b_supplier_site_id(j);
3994 l_item_id := b_item_id(j);
3995 l_publisher_name := b_publisher_name(j);
3996 l_publisher_site_name := b_publisher_site_name(j);
3997 l_customer_name := b_customer_name(j);
3998 l_customer_site_name := b_customer_site_name(j);
3999 l_supplier_name := b_supplier_name(j);
4000 l_supplier_site_name := b_supplier_site_name(j);
4001
4002 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
4003 l_total_demand := b_total_quantity(j);
4004 l_tp_total_demand := b_tp_total_quantity(j);
4005 l_posting_total_demand := b_posting_total_quantity(j);
4006 l_total_supply := 0;
4007 l_tp_total_supply := 0;
4008 l_posting_total_supply := 0;
4009 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4010 l_total_demand := 0;
4011 l_tp_total_demand := 0;
4012 l_posting_total_demand := 0;
4013 l_total_supply := b_total_quantity(j);
4014 l_tp_total_supply := b_tp_total_quantity(j);
4015 l_posting_total_supply := b_posting_total_quantity(j);
4016 END IF;
4017 --dbms_output.put_line(' loop for last record ' );
4018
4019 IF ((l_total_supply - l_tp_total_demand) > (l_tp_total_demand * l_threshold2/100))
4020 and (b_refresh_number(j) > p_refresh_number)
4021 THEN
4022
4023
4024 l_exception_type := msc_x_netting_pkg.G_EXCEP25;--Cust order fcst < your allocated supply
4025 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
4026 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4027 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4028
4029
4030 msc_x_netting_pkg.add_to_exception_tbl(
4031 l_publisher_id, --b_publisher_id(j),
4032 l_publisher_name, --b_publisher_name(j),
4033 l_publisher_site_id, --b_publisher_site_id(j),
4034 l_publisher_site_name, -- b_publisher_site_name(j),
4035 l_item_id, --b_item_id(j),
4036 l_item_name, --b_item_name(j),
4037 l_item_desc,
4038 l_exception_type,
4039 l_exception_type_name,
4040 l_exception_group,
4041 l_exception_group_name,
4042 null, --trx_id1,
4043 null, --trx_id2,
4044 l_customer_id, --b_customer_id(j),
4045 l_customer_name, --b_customer_name(j),
4046 l_customer_site_id, --b_customer_site_id(j),
4047 l_customer_site_name, --b_customer_site_name(j),
4048 l_customer_item_name, --b_customer_item_name(j),
4049 null, --l_supplier_id
4050 null,
4051 null, --l_supplier_site_id
4052 null,
4053 l_supplier_item_name, --b_supplier_item_name(j),
4054 l_total_supply,
4055 l_tp_total_demand,
4056 null,
4057 l_threshold2,
4058 null, --lead time
4059 null, --item min
4060 null, --item_max
4061 null, --l_order_number,
4062 null, --l_release_number,
4063 null, --l_line_number,
4064 null, --l_end_order_number,
4065 null, --l_end_order_rel_number,
4066 null, --l_end_order_line_number,
4067 null, --b_so_creation_date(j),
4068 null, --b_po_creation_date(j),
4069 l_start_date,
4070 l_end_date,
4071 null, --ship_date(j),
4072 null, --ship_date(j),
4073 null,
4074 l_exception_basis,
4075 a_company_id,
4076 a_company_name,
4077 a_company_site_id,
4078 a_company_site_name,
4079 a_item_id,
4080 a_item_name,
4081 a_item_desc,
4082 a_exception_type,
4083 a_exception_type_name,
4084 a_exception_group,
4085 a_exception_group_name,
4086 a_trx_id1,
4087 a_trx_id2,
4088 a_customer_id,
4089 a_customer_name,
4090 a_customer_site_id,
4091 a_customer_site_name,
4092 a_customer_item_name,
4093 a_supplier_id,
4094 a_supplier_name,
4095 a_supplier_site_id,
4096 a_supplier_site_name,
4097 a_supplier_item_name,
4098 a_number1,
4099 a_number2,
4100 a_number3,
4101 a_threshold,
4102 a_lead_time,
4103 a_item_min_qty,
4104 a_item_max_qty,
4105 a_order_number,
4106 a_release_number,
4107 a_line_number,
4108 a_end_order_number,
4109 a_end_order_rel_number,
4110 a_end_order_line_number,
4111 a_creation_date,
4112 a_tp_creation_date,
4113 a_date1,
4114 a_date2,
4115 a_date3,
4116 a_date4,
4117 a_date5,
4118 a_exception_basis);
4119 l_inserted_record := l_inserted_record + 1;
4120
4121
4122 END IF; --compute exception
4123 END IF; -- m loop to the last record
4124 --END LOOP;
4125 --end if;
4126 -- END IF; --sum
4127 end loop;
4128 END IF;
4129
4130 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(25) ||
4131 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
4132
4133 --=======================================================================================
4134 --for Supplier supply planning (exception 7.2)
4135 --======================================================================================
4136 l_total_supply := 0;
4137 l_total_demand := 0;
4138 l_pair := 0;
4139 l_insert := 0;
4140
4141 --dbms_output.put_line('Exception 26');
4142
4143 open exception_26(p_refresh_number);
4144 fetch exception_26 BULK COLLECT INTO
4145 b_publisher_id,
4146 b_publisher_site_id,
4147 b_customer_id,
4148 b_customer_site_id,
4149 b_supplier_id,
4150 b_supplier_site_id,
4151 b_item_id,
4152 b_bucket_index,
4153 b_bkt_start_date,
4154 b_bkt_end_date,
4155 b_order_type,
4156 b_publisher_name,
4157 b_publisher_site_name,
4158 b_customer_name,
4159 b_customer_site_name,
4160 b_supplier_name,
4161 b_supplier_site_name,
4162 b_item_name,
4163 b_item_desc,
4164 b_supplier_item_name,
4165 b_customer_item_name,
4166 b_total_quantity,
4167 b_tp_total_quantity,
4168 b_posting_total_quantity,
4169 b_refresh_number;
4170
4171 CLOSE exception_26;
4172
4173 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
4174 FOR j in 1..b_item_id.COUNT
4175 LOOP
4176
4177
4178 IF (j = 1 or b_publisher_id(j) <> b_publisher_id(j-1) OR
4179 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
4180 b_customer_id(j) <> b_customer_id(j-1) OR
4181 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
4182 b_supplier_id(j) <> b_supplier_id(j-1) OR
4183 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
4184 b_item_id(j) <> b_item_id(j-1)) THEN
4185
4186 --======================================================
4187 -- archive old exception and its complement
4188 --======================================================
4189 msc_x_netting_pkg.add_to_delete_tbl(
4190 b_publisher_id(j),
4191 b_publisher_site_id(j),
4192 null,
4193 null,
4194 b_supplier_id(j),
4195 b_supplier_site_id(j),
4196 b_item_id(j),
4197 msc_x_netting_pkg.G_MATERIAL_EXCESS,
4198 msc_x_netting_pkg.G_EXCEP26,
4199 null,
4200 null,
4201 null,
4202 null,
4203 t_company_list,
4204 t_company_site_list,
4205 t_customer_list,
4206 t_customer_site_list,
4207 t_supplier_list,
4208 t_supplier_site_list,
4209 t_item_list,
4210 t_group_list,
4211 t_type_list,
4212 t_trxid1_list,
4213 t_trxid2_list,
4214 t_date1_list,
4215 t_date2_list);
4216
4217
4218 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP26,
4219 b_publisher_id(j),
4220 b_publisher_site_id(j),
4221 b_item_id(j),
4222 b_supplier_id(j),
4223 b_supplier_site_id(j),
4224 null,
4225 null,
4226 null);
4227
4228
4229 --------------------------------------------------------------------------
4230 -- get the shipping control
4231 ---------------------------------------------------------------------------
4232 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
4233 b_publisher_site_name(j),
4234 b_supplier_name(j),
4235 b_supplier_site_name(j));
4236
4237 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
4238 nvl(l_shipping_control,1));
4239
4240 END IF;
4241
4242 IF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
4243 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
4244 b_customer_id(j) = b_customer_id(j-1) AND
4245 b_customer_site_id(j) = b_customer_site_id(j-1) AND
4246 b_supplier_id(j) = b_supplier_id(j-1) AND
4247 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
4248 b_item_id(j) = b_item_id(j-1) and
4249 b_bucket_index(j) = b_bucket_index(j-1) and
4250 b_bkt_start_date(j) = b_bkt_start_date(j-1)) THEN
4251
4252 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
4253 l_total_demand := b_total_quantity(j-1);
4254 l_tp_total_demand := b_tp_total_quantity(j-1);
4255 l_posting_total_demand := b_posting_total_quantity(j-1);
4256 ELSIF (b_order_type(j-1)= msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4257 l_total_supply := b_total_quantity(j-1);
4258 l_tp_total_supply := b_tp_total_quantity(j-1);
4259 l_posting_total_supply := b_posting_total_quantity(j-1);
4260 END IF;
4261
4262 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
4263 l_total_demand := b_total_quantity(j);
4264 l_tp_total_demand := b_tp_total_quantity(j);
4265 l_posting_total_demand := b_posting_total_quantity(j);
4266
4267 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4268 l_total_supply := b_total_quantity(j);
4269 l_tp_total_supply := b_tp_total_quantity(j);
4270 l_posting_total_supply := b_posting_total_quantity(j);
4271 END IF;
4272
4273 l_pair := 1;
4274 l_insert := 1;
4275 l_start_date := b_bkt_start_date(j-1);
4276 l_end_date := b_bkt_end_date(j-1);
4277 l_publisher_id := b_publisher_id(j-1);
4278 l_publisher_site_id := b_publisher_site_id(j-1);
4279 l_customer_id := b_customer_id(j-1);
4280 l_customer_site_id := b_customer_site_id(j-1);
4281 l_supplier_id := b_supplier_id(j-1);
4282 l_supplier_site_id := b_supplier_site_id(j-1);
4283 l_item_id := b_item_id(j-1);
4284 l_publisher_name := b_publisher_name(j-1);
4285 l_publisher_site_name := b_publisher_site_name(j-1);
4286 l_customer_name := b_customer_name(j-1);
4287 l_customer_site_name := b_customer_site_name(j-1);
4288 l_supplier_name := b_supplier_name(j-1);
4289 l_supplier_site_name := b_supplier_site_name(j-1);
4290
4291 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
4292 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
4293 b_customer_id(j) = b_customer_id(j-1) AND
4294 b_customer_site_id(j) = b_customer_site_id(j-1) AND
4295 b_supplier_id(j) = b_supplier_id(j-1) AND
4296 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
4297 b_item_id(j) = b_item_id(j-1) AND
4298 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair = 1) THEN
4299 l_pair := 0;
4300 l_insert := 0;
4301
4302 ELSIF (j > 1 and b_publisher_id(j) = b_publisher_id(j-1) AND
4303 b_publisher_site_id(j) = b_publisher_site_id(j-1) AND
4304 b_customer_id(j) = b_customer_id(j-1) AND
4305 b_customer_site_id(j) = b_customer_site_id(j-1) AND
4306 b_supplier_id(j) = b_supplier_id(j-1) AND
4307 b_supplier_site_id(j) = b_supplier_site_id(j-1) AND
4308 b_item_id(j) = b_item_id(j-1) AND
4309 b_bucket_index(j) <> b_bucket_index(j-1) and l_pair <> 1) THEN
4310
4311 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
4312 l_total_demand := b_total_quantity(j-1);
4313 l_tp_total_demand := b_tp_total_quantity(j-1);
4314 l_posting_total_demand := b_posting_total_quantity(j-1);
4315 l_total_supply := 0;
4316 l_tp_total_supply := 0;
4317 l_posting_total_supply := 0;
4318 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4319 l_total_demand := 0;
4320 l_tp_total_demand := 0;
4321 l_posting_total_demand := 0;
4322 l_total_supply := b_total_quantity(j-1);
4323 l_tp_total_supply := b_tp_total_quantity(j-1);
4324 l_posting_total_supply := b_posting_total_quantity(j-1);
4325 END IF;
4326 l_pair := 0;
4327 l_insert := 1;
4328 l_start_date := b_bkt_start_date(j-1);
4329 l_end_date := b_bkt_end_date(j-1);
4330 l_publisher_id := b_publisher_id(j-1);
4331 l_publisher_site_id := b_publisher_site_id(j-1);
4332 l_customer_id := b_customer_id(j-1);
4333 l_customer_site_id := b_customer_site_id(j-1);
4334 l_supplier_id := b_supplier_id(j-1);
4335 l_supplier_site_id := b_supplier_site_id(j-1);
4336 l_item_id := b_item_id(j-1);
4337 l_publisher_name := b_publisher_name(j-1);
4338 l_publisher_site_name := b_publisher_site_name(j-1);
4339 l_customer_name := b_customer_name(j-1);
4340 l_customer_site_name := b_customer_site_name(j-1);
4341 l_supplier_name := b_supplier_name(j-1);
4342 l_supplier_site_name := b_supplier_site_name(j-1);
4343
4344 ELSIF (j > 1 and l_pair = 1 ) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
4345 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
4346 b_customer_id(j) <> b_customer_id(j-1) OR
4347 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
4348 b_supplier_id(j) <> b_supplier_id(j-1) OR
4349 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
4350 b_item_id(j) <> b_item_id(j-1)) THEN
4351 l_pair := 0;
4352 l_insert := 0;
4353
4354 ELSIF (j > 1 and l_pair <> 1) and (b_publisher_id(j) <> b_publisher_id(j-1) OR
4355 b_publisher_site_id(j) <> b_publisher_site_id(j-1) OR
4356 b_customer_id(j) <> b_customer_id(j-1) OR
4357 b_customer_site_id(j) <> b_customer_site_id(j-1) OR
4358 b_supplier_id(j) <> b_supplier_id(j-1) OR
4359 b_supplier_site_id(j) <> b_supplier_site_id(j-1) OR
4360 b_item_id(j) <> b_item_id(j-1)) THEN
4361
4362 IF (b_order_type(j-1) = msc_x_netting_pkg.ORDER_FORECAST) THEN
4363 l_total_demand := b_total_quantity(j-1);
4364 l_tp_total_demand := b_tp_total_quantity(j-1);
4365 l_posting_total_demand := b_posting_total_quantity(j-1);
4366 l_total_supply := 0;
4367 l_tp_total_supply := 0;
4368 l_posting_total_supply := 0;
4369 ELSIF (b_order_type(j-1) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4370 l_total_demand := 0;
4371 l_tp_total_demand := 0;
4372 l_posting_total_demand := 0;
4373 l_total_supply := b_total_quantity(j-1);
4374 l_tp_total_supply := b_tp_total_quantity(j-1);
4375 l_posting_total_supply := b_posting_total_quantity(j-1);
4376 END IF;
4377 l_pair := 0;
4378 l_insert := 1;
4379 l_start_date := b_bkt_start_date(j-1);
4380 l_end_date := b_bkt_end_date(j-1);
4381 l_publisher_id := b_publisher_id(j-1);
4382 l_publisher_site_id := b_publisher_site_id(j-1);
4383 l_customer_id := b_customer_id(j-1);
4384 l_customer_site_id := b_customer_site_id(j-1);
4385 l_supplier_id := b_supplier_id(j-1);
4386 l_supplier_site_id := b_supplier_site_id(j-1);
4387 l_item_id := b_item_id(j-1);
4388 l_publisher_name := b_publisher_name(j-1);
4389 l_publisher_site_name := b_publisher_site_name(j-1);
4390 l_customer_name := b_customer_name(j-1);
4391 l_customer_site_name := b_customer_site_name(j-1);
4392 l_supplier_name := b_supplier_name(j-1);
4393 l_supplier_site_name := b_supplier_site_name(j-1);
4394
4395 ELSIF (j = 1 and b_bkt_start_date.COUNT = 1) THEN
4396
4397 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST ) THEN
4398 l_total_demand := b_total_quantity(j);
4399 l_tp_total_demand := b_tp_total_quantity(j);
4400 l_posting_total_demand := b_posting_total_quantity(j);
4401 l_total_supply := 0;
4402 l_tp_total_supply := 0;
4403 l_posting_total_supply := 0;
4404 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4405 l_total_demand := 0;
4406 l_tp_total_demand := 0;
4407 l_posting_total_demand := 0;
4408 l_total_supply := b_total_quantity(j);
4409 l_tp_total_supply := b_tp_total_quantity(j);
4410 l_posting_total_supply := b_posting_total_quantity(j);
4411 END IF;
4412
4413 l_pair := 0;
4414 l_insert := 1;
4415 l_start_date := b_bkt_start_date(j);
4416 l_end_date := b_bkt_end_date(j);
4417 l_publisher_id := b_publisher_id(j);
4418 l_publisher_site_id := b_publisher_site_id(j);
4419 l_customer_id := b_customer_id(j);
4420 l_customer_site_id := b_customer_site_id(j);
4421 l_supplier_id := b_supplier_id(j);
4422 l_supplier_site_id := b_supplier_site_id(j);
4423 l_item_id := b_item_id(j);
4424 l_publisher_name := b_publisher_name(j);
4425 l_publisher_site_name := b_publisher_site_name(j);
4426 l_customer_name := b_customer_name(j);
4427 l_customer_site_name := b_customer_site_name(j);
4428 l_supplier_name := b_supplier_name(j);
4429 l_supplier_site_name := b_supplier_site_name(j);
4430
4431 END IF;
4432
4433
4434 FND_FILE.PUT_LINE(FND_FILE.LOG, '26:supply ' || l_total_supply || ' demand ' || l_total_demand ||
4435 ' date ' || l_start_date || 'cutoff ' || l_cutoff_ref_num);
4436
4437
4438 IF (((j > 1 and l_insert = 1 ) OR (b_bucket_index.COUNT = 1)) and
4439 ((l_tp_total_supply - l_total_demand) > (l_total_demand * l_threshold2/100)))
4440 and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
4441 THEN
4442
4443 --------------------------------------------------------
4444 -- clean up the opposite exception and its complement
4445 --------------------------------------------------------
4446 msc_x_netting_pkg.add_to_delete_tbl(
4447 l_publisher_id, --b_publisher_id(j),
4448 l_publisher_site_id, --b_publisher_site_id(j),
4449 null,
4450 null,
4451 l_supplier_id, --b_supplier_id(j),
4452 l_supplier_site_id, --b_supplier_site_id(j),
4453 l_item_id, --b_item_id(j),
4454 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
4455 msc_x_netting_pkg.G_EXCEP6,
4456 null,
4457 null,
4458 l_start_date,
4459 l_end_date,
4460 t_company_list,
4461 t_company_site_list,
4462 t_customer_list,
4463 t_customer_site_list,
4464 t_supplier_list,
4465 t_supplier_site_list,
4466 t_item_list,
4467 t_group_list,
4468 t_type_list,
4469 t_trxid1_list,
4470 t_trxid2_list,
4471 t_date1_list,
4472 t_date2_list);
4473
4474
4475 --if plan_type is SP then detected exception 7.2
4476 --dbms_output.put_line('dem ' || l_total_demand || 'sup ' || l_tp_total_supply);
4477 l_exception_type := msc_x_netting_pkg.G_EXCEP26;
4478 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
4479 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4480 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4481
4482 msc_x_netting_pkg.add_to_exception_tbl(
4483 l_publisher_id, --b_publisher_id(j),
4484 l_publisher_name, --b_publisher_name(j),
4485 l_publisher_site_id, --b_publisher_site_id(j),
4486 l_publisher_site_name, --b_publisher_site_name(j),
4487 l_item_id, --b_item_id(j),
4488 l_item_name, --b_item_name(j),
4489 l_item_desc, --b_item_desc(j),
4490 l_exception_type,
4491 l_exception_type_name,
4492 l_exception_group,
4493 l_exception_group_name,
4494 null, --trx_id1,
4495 null, --trx_id2,
4496 null, --l_customer_id,
4497 null,
4498 null, --l_customer_site_id,
4499 null,
4500 l_customer_item_name, --b_customer_item_name(j),
4501 l_supplier_id, --b_supplier_id(j),
4502 l_supplier_name, --b_supplier_name(j),
4503 l_supplier_site_id, --b_supplier_site_id(j),
4504 l_supplier_site_name, --b_supplier_site_name(j),
4505 l_supplier_item_name, --b_supplier_item_name(j),
4506 l_tp_total_supply,
4507 l_total_demand,
4508 null,
4509 l_threshold2,
4510 null, --lead time
4511 null, --item min
4512 null, --item_max
4513 null, --l_order_number,
4514 null, --l_release_number,
4515 null, --l_line_number,
4516 null, --l_end_order_number,
4517 null, --l_end_order_rel_number,
4518 null, --l_end_order_line_number,
4519 null, --b_so_creation_date(j),
4520 null, --b_po_creation_date(j),
4521 l_start_date,
4522 l_end_date,
4523 null, --ship_date(j),
4524 null, --ship_date(j),
4525 null,
4526 l_exception_basis,
4527 a_company_id,
4528 a_company_name,
4529 a_company_site_id,
4530 a_company_site_name,
4531 a_item_id,
4532 a_item_name,
4533 a_item_desc,
4534 a_exception_type,
4535 a_exception_type_name,
4536 a_exception_group,
4537 a_exception_group_name,
4538 a_trx_id1,
4539 a_trx_id2,
4540 a_customer_id,
4541 a_customer_name,
4542 a_customer_site_id,
4543 a_customer_site_name,
4544 a_customer_item_name,
4545 a_supplier_id,
4546 a_supplier_name,
4547 a_supplier_site_id,
4548 a_supplier_site_name,
4549 a_supplier_item_name,
4550 a_number1,
4551 a_number2,
4552 a_number3,
4553 a_threshold,
4554 a_lead_time,
4555 a_item_min_qty,
4556 a_item_max_qty,
4557 a_order_number,
4558 a_release_number,
4559 a_line_number,
4560 a_end_order_number,
4561 a_end_order_rel_number,
4562 a_end_order_line_number,
4563 a_creation_date,
4564 a_tp_creation_date,
4565 a_date1,
4566 a_date2,
4567 a_date3,
4568 a_date4,
4569 a_date5,
4570 a_exception_basis);
4571 l_inserted_record := l_inserted_record + 1;
4572
4573 end if; --compute exception
4574 /*------------------------------------------------------------------
4575 Loop for the last record if require to insert
4576 ------------------------------------------------------------------*/
4577 IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
4578 l_pair := 0;
4579 l_insert := 1;
4580 l_start_date := b_bkt_start_date(j);
4581 l_end_date := b_bkt_end_date(j);
4582 l_publisher_id := b_publisher_id(j);
4583 l_publisher_site_id := b_publisher_site_id(j);
4584 l_customer_id := b_customer_id(j);
4585 l_customer_site_id := b_customer_site_id(j);
4586 l_supplier_id := b_supplier_id(j);
4587 l_supplier_site_id := b_supplier_site_id(j);
4588 l_item_id := b_item_id(j);
4589 l_publisher_name := b_publisher_name(j);
4590 l_publisher_site_name := b_publisher_site_name(j);
4591 l_customer_name := b_customer_name(j);
4592 l_customer_site_name := b_customer_site_name(j);
4593 l_supplier_name := b_supplier_name(j);
4594 l_supplier_site_name := b_supplier_site_name(j);
4595
4596 IF (b_order_type(j) = msc_x_netting_pkg.ORDER_FORECAST) THEN
4597 l_total_demand := b_total_quantity(j);
4598 l_tp_total_demand := b_tp_total_quantity(j);
4599 l_posting_total_demand := b_posting_total_quantity(j);
4600 l_total_supply := 0;
4601 l_tp_total_supply := 0;
4602 l_posting_total_supply := 0;
4603 ELSIF (b_order_type(j) = msc_x_netting_pkg.SUPPLY_COMMIT) THEN
4604 l_total_demand := 0;
4605 l_tp_total_demand := 0;
4606 l_posting_total_demand := 0;
4607 l_total_supply := b_total_quantity(j);
4608 l_tp_total_supply := b_tp_total_quantity(j);
4609 l_posting_total_supply := b_posting_total_quantity(j);
4610 END IF;
4611
4612 IF ((l_tp_total_supply - l_total_demand) > (l_total_demand * l_threshold2/100))
4613 and (b_refresh_number(j) > p_refresh_number)
4614 THEN
4615
4616
4617 --if plan_type is SP then detected exception 7.2
4618 --dbms_output.put_line('dem ' || l_total_demand || 'sup ' || l_tp_total_supply);
4619 l_exception_type := msc_x_netting_pkg.G_EXCEP26;
4620 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
4621 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4622 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4623
4624 msc_x_netting_pkg.add_to_exception_tbl(
4625 l_publisher_id, --b_publisher_id(j),
4626 l_publisher_name, --b_publisher_name(j),
4627 l_publisher_site_id, --b_publisher_site_id(j),
4628 l_publisher_site_name, --b_publisher_site_name(j),
4629 l_item_id, --b_item_id(j),
4630 l_item_name, --b_item_name(j),
4631 l_item_desc, --b_item_desc(j),
4632 l_exception_type,
4633 l_exception_type_name,
4634 l_exception_group,
4635 l_exception_group_name,
4636 null, --trx_id1,
4637 null, --trx_id2,
4638 null, --l_customer_id,
4639 null,
4640 null, --l_customer_site_id,
4641 null,
4642 l_customer_item_name, --b_customer_item_name(j),
4643 l_supplier_id, --b_supplier_id(j),
4644 l_supplier_name, --b_supplier_name(j),
4645 l_supplier_site_id, --b_supplier_site_id(j),
4646 l_supplier_site_name, --b_supplier_site_name(j),
4647 l_supplier_item_name, --b_supplier_item_name(j),
4648 l_tp_total_supply,
4649 l_total_demand,
4650 null,
4651 l_threshold2,
4652 null, --lead time
4653 null, --item min
4654 null, --item_max
4655 null, --l_order_number,
4656 null, --l_release_number,
4657 null, --l_line_number,
4658 null, --l_end_order_number,
4659 null, --l_end_order_rel_number,
4660 null, --l_end_order_line_number,
4661 null, --b_so_creation_date(j),
4662 null, --b_po_creation_date(j),
4663 l_start_date,
4664 l_end_date,
4665 null, --ship_date(j),
4666 null, --ship_date(j),
4667 null,
4668 l_exception_basis,
4669 a_company_id,
4670 a_company_name,
4671 a_company_site_id,
4672 a_company_site_name,
4673 a_item_id,
4674 a_item_name,
4675 a_item_desc,
4676 a_exception_type,
4677 a_exception_type_name,
4678 a_exception_group,
4679 a_exception_group_name,
4680 a_trx_id1,
4681 a_trx_id2,
4682 a_customer_id,
4683 a_customer_name,
4684 a_customer_site_id,
4685 a_customer_site_name,
4686 a_customer_item_name,
4687 a_supplier_id,
4688 a_supplier_name,
4689 a_supplier_site_id,
4690 a_supplier_site_name,
4691 a_supplier_item_name,
4692 a_number1,
4693 a_number2,
4694 a_number3,
4695 a_threshold,
4696 a_lead_time,
4697 a_item_min_qty,
4698 a_item_max_qty,
4699 a_order_number,
4700 a_release_number,
4701 a_line_number,
4702 a_end_order_number,
4703 a_end_order_rel_number,
4704 a_end_order_line_number,
4705 a_creation_date,
4706 a_tp_creation_date,
4707 a_date1,
4708 a_date2,
4709 a_date3,
4710 a_date4,
4711 a_date5,
4712 a_exception_basis);
4713 l_inserted_record := l_inserted_record + 1;
4714
4715 end if; --compute exception
4716
4717 end if; -- end of the last record of the loop
4718 -- END LOOP;
4719 --END IF;
4720 --END IF; --sum
4721 END LOOP;
4722 END IF;
4723 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(26) ||
4724 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
4725
4726
4727 --=================================================================================
4728
4729 /* reset the trxid */
4730 l_generate_complement := null;
4731
4732 --dbms_output.put_line('Exception 27'); --supplier centric
4733 open exception_27 (p_refresh_number);
4734 fetch exception_27 BULK COLLECT INTO
4735 b_trx_id1,
4736 b_publisher_id,
4737 b_publisher_name,
4738 b_publisher_site_id,
4739 b_publisher_site_name,
4740 b_item_id,
4741 b_item_name,
4742 b_item_desc,
4743 b_supplier_item_name,
4744 b_supplier_item_desc,
4745 b_so_key_date,
4746 b_so_ship_date,
4747 b_so_receipt_date,
4748 b_posting_so_qty,
4749 b_so_qty,
4750 b_tp_so_qty,
4751 b_end_order_number,
4752 b_end_order_rel_number,
4753 b_end_order_line_number,
4754 b_customer_id,
4755 b_customer_name,
4756 b_customer_site_id,
4757 b_customer_site_name,
4758 b_customer_item_name,
4759 b_customer_item_desc,
4760 b_so_creation_date,
4761 b_so_last_refnum,
4762 b_trx_id2,
4763 b_po_key_date,
4764 b_po_ship_date,
4765 b_po_receipt_date,
4766 b_posting_po_qty,
4767 b_po_qty,
4768 b_tp_po_qty,
4769 b_order_number,
4770 b_release_number,
4771 b_line_number,
4772 b_supplier_id, --owning com
4773 b_supplier_name,
4774 b_supplier_site_id, --owning org
4775 b_supplier_site_name,
4776 b_po_creation_date,
4777 b_po_last_refnum;
4778 CLOSE exception_27;
4779
4780 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
4781 FOR j in 1..b_trx_id1.COUNT
4782 LOOP
4783 --dbms_output.put_line('-----Exception27: Trx id 1 = ' || b_trx_id1(j));
4784 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
4785 --======================================================
4786 -- archive old exception and its complement
4787 --======================================================
4788 msc_x_netting_pkg.add_to_delete_tbl(
4789 b_publisher_id(j),
4790 b_publisher_site_id(j),
4791 b_customer_id(j),
4792 b_customer_site_id(j),
4793 null,
4794 null,
4795 b_item_id(j),
4796 msc_x_netting_pkg.G_MATERIAL_EXCESS,
4797 msc_x_netting_pkg.G_EXCEP27,
4798 b_trx_id1(j),
4799 b_trx_id2(j),
4800 null,
4801 null,
4802 t_company_list,
4803 t_company_site_list,
4804 t_customer_list,
4805 t_customer_site_list,
4806 t_supplier_list,
4807 t_supplier_site_list,
4808 t_item_list,
4809 t_group_list,
4810 t_type_list,
4811 t_trxid1_list,
4812 t_trxid2_list,
4813 t_date1_list,
4814 t_date2_list);
4815
4816
4817 msc_x_netting_pkg.add_to_delete_tbl(
4818 b_customer_id(j),
4819 b_customer_site_id(j),
4820 null,
4821 null,
4822 b_publisher_id(j),
4823 b_publisher_site_id(j),
4824 b_item_id(j),
4825 msc_x_netting_pkg.G_MATERIAL_EXCESS,
4826 msc_x_netting_pkg.G_EXCEP28,
4827 b_trx_id2(j),
4828 b_trx_id1(j),
4829 null,
4830 null,
4831 t_company_list,
4832 t_company_site_list,
4833 t_customer_list,
4834 t_customer_site_list,
4835 t_supplier_list,
4836 t_supplier_site_list,
4837 t_item_list,
4838 t_group_list,
4839 t_type_list,
4840 t_trxid1_list,
4841 t_trxid2_list,
4842 t_date1_list,
4843 t_date2_list);
4844
4845
4846 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP27,
4847 b_publisher_id(j),
4848 b_publisher_site_id(j),
4849 b_item_id(j),
4850 null,
4851 null,
4852 b_customer_id(j),
4853 b_customer_site_id(j),
4854 b_so_key_date(j));
4855
4856 l_total_qty := msc_x_netting_pkg.get_total_qty(b_order_number(j),
4857 b_release_number(j),
4858 b_line_number(j),
4859 b_publisher_id(j),
4860 b_publisher_site_id(j),
4861 b_customer_id(j),
4862 b_customer_site_id(j),
4863 b_item_id(j));
4864
4865 IF (l_total_qty > b_po_qty(j) + (l_threshold2*b_po_qty(j)/100) ) then
4866 --======================================================
4867 -- Clean up the opposite exception and its complement
4868 --======================================================
4869
4870 msc_x_netting_pkg.add_to_delete_tbl(
4871 b_publisher_id(j),
4872 b_publisher_site_id(j),
4873 b_customer_id(j),
4874 b_customer_site_id(j),
4875 null,
4876 null,
4877 b_item_id(j),
4878 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
4879 msc_x_netting_pkg.G_EXCEP7,
4880 b_trx_id1(j),
4881 b_trx_id2(j),
4882 null,
4883 null,
4884 t_company_list,
4885 t_company_site_list,
4886 t_customer_list,
4887 t_customer_site_list,
4888 t_supplier_list,
4889 t_supplier_site_list,
4890 t_item_list,
4891 t_group_list,
4892 t_type_list,
4893 t_trxid1_list,
4894 t_trxid2_list,
4895 t_date1_list,
4896 t_date2_list);
4897
4898 msc_x_netting_pkg.add_to_delete_tbl(
4899 b_customer_id(j),
4900 b_customer_site_id(j),
4901 null,
4902 null,
4903 b_publisher_id(j),
4904 b_publisher_site_id(j),
4905 b_item_id(j),
4906 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
4907 msc_x_netting_pkg.G_EXCEP8,
4908 b_trx_id2(j),
4909 b_trx_id1(j),
4910 null,
4911 null,
4912 t_company_list,
4913 t_company_site_list,
4914 t_customer_list,
4915 t_customer_site_list,
4916 t_supplier_list,
4917 t_supplier_site_list,
4918 t_item_list,
4919 t_group_list,
4920 t_type_list,
4921 t_trxid1_list,
4922 t_trxid2_list,
4923 t_date1_list,
4924 t_date2_list);
4925
4926
4927 IF (j=1 OR (b_publisher_id(j -1) <> b_publisher_id(j) OR
4928 b_publisher_site_id(j-1) <> b_publisher_site_id(j) OR
4929 b_customer_id(j-1) <> b_customer_id(j) OR
4930 b_customer_site_id(j-1) <> b_customer_site_id(j) OR
4931 b_item_id(j-1) <> b_item_id(j) OR
4932 b_order_number(j-1) <> b_order_number(j) OR
4933 b_release_number(j-1) <> b_release_number(j) OR
4934 b_line_number(j-1) <> b_line_number(j) )) THEN
4935
4936 --dbms_output.put_line('PUB ' || b_publisher_id(j));
4937 --dbms_output.put_line('PUB SITE ' || b_publisher_site_id(j));
4938 --dbms_output.put_line('ITEM ' || b_item_id(j));
4939 --dbms_output.put_line('CUST ' || b_customer_id(j));
4940 --dbms_output.put_line('CUST SITE ' || b_customer_site_id(j));
4941 --dbms_output.put_line('ORDER ' || b_order_number(j));
4942 --dbms_output.put_line('LINE ' || b_line_number(j));
4943 --dbms_output.put_line('REL ' || b_release_number(j));
4944 -------------------------------------------------------------------------
4945 -- get the latest SO to populate the exception
4946 -------------------------------------------------------------------------
4947 BEGIN
4948 select transaction_id, receipt_date, ship_date, order_number, line_number, release_number
4949 into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
4950 b_end_order_line_number(j), b_end_order_rel_number(j)
4951 from msc_sup_dem_entries
4952 where publisher_id = b_publisher_id(j)
4953 and publisher_site_id = b_publisher_site_id(j)
4954 and customer_id = b_customer_id(j)
4955 and customer_site_id = b_customer_site_id(j)
4956 and inventory_item_id = b_item_id(j)
4957 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
4958 and end_order_number = b_order_number(j)
4959 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
4960 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
4961 and key_date = (select max(key_date)
4962 from msc_sup_dem_entries
4963 where publisher_id = b_publisher_id(j)
4964 and publisher_site_id = b_publisher_site_id(j)
4965 and customer_id = b_customer_id(j)
4966 and customer_site_id = b_customer_site_id(j)
4967 and inventory_item_id = b_item_id(j)
4968 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
4969 and end_order_number = b_order_number(j)
4970 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
4971 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1));
4972 --dbms_output.put_line('so trx id ' || l_last_so_trx_id);
4973 EXCEPTION
4974 when others then
4975 null;
4976 --dbms_output.put_line('Error ' || sqlerrm);
4977 END;
4978
4979
4980 --======================================================
4981 -- archive old exception and its complement
4982 --======================================================
4983 msc_x_netting_pkg.add_to_delete_tbl(
4984 b_publisher_id(j),
4985 b_publisher_site_id(j),
4986 b_customer_id(j),
4987 b_customer_site_id(j),
4988 null,
4989 null,
4990 b_item_id(j),
4991 msc_x_netting_pkg.G_MATERIAL_EXCESS,
4992 msc_x_netting_pkg.G_EXCEP27,
4993 l_last_so_trx_id,
4994 b_trx_id2(j),
4995 null,
4996 null,
4997 t_company_list,
4998 t_company_site_list,
4999 t_customer_list,
5000 t_customer_site_list,
5001 t_supplier_list,
5002 t_supplier_site_list,
5003 t_item_list,
5004 t_group_list,
5005 t_type_list,
5006 t_trxid1_list,
5007 t_trxid2_list,
5008 t_date1_list,
5009 t_date2_list);
5010
5011
5012 msc_x_netting_pkg.add_to_delete_tbl(
5013 b_customer_id(j),
5014 b_customer_site_id(j),
5015 null,
5016 null,
5017 b_publisher_id(j),
5018 b_publisher_site_id(j),
5019 b_item_id(j),
5020 msc_x_netting_pkg.G_MATERIAL_EXCESS,
5021 msc_x_netting_pkg.G_EXCEP28,
5022 b_trx_id2(j),
5023 l_last_so_trx_id,
5024 null,
5025 null,
5026 t_company_list,
5027 t_company_site_list,
5028 t_customer_list,
5029 t_customer_site_list,
5030 t_supplier_list,
5031 t_supplier_site_list,
5032 t_item_list,
5033 t_group_list,
5034 t_type_list,
5035 t_trxid1_list,
5036 t_trxid2_list,
5037 t_date1_list,
5038 t_date2_list);
5039
5040
5041 msc_x_netting_pkg.add_to_delete_tbl(
5042 b_publisher_id(j),
5043 b_publisher_site_id(j),
5044 b_customer_id(j),
5045 b_customer_site_id(j),
5046 null,
5047 null,
5048 b_item_id(j),
5049 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
5050 msc_x_netting_pkg.G_EXCEP7,
5051 l_last_so_trx_id,
5052 b_trx_id2(j),
5053 null,
5054 null,
5055 t_company_list,
5056 t_company_site_list,
5057 t_customer_list,
5058 t_customer_site_list,
5059 t_supplier_list,
5060 t_supplier_site_list,
5061 t_item_list,
5062 t_group_list,
5063 t_type_list,
5064 t_trxid1_list,
5065 t_trxid2_list,
5066 t_date1_list,
5067 t_date2_list);
5068
5069 msc_x_netting_pkg.add_to_delete_tbl(
5070 b_customer_id(j),
5071 b_customer_site_id(j),
5072 null,
5073 null,
5074 b_publisher_id(j),
5075 b_publisher_site_id(j),
5076 b_item_id(j),
5077 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
5078 msc_x_netting_pkg.G_EXCEP8,
5079 b_trx_id2(j),
5080 l_last_so_trx_id,
5081 null,
5082 null,
5083 t_company_list,
5084 t_company_site_list,
5085 t_customer_list,
5086 t_customer_site_list,
5087 t_supplier_list,
5088 t_supplier_site_list,
5089 t_item_list,
5090 t_group_list,
5091 t_type_list,
5092 t_trxid1_list,
5093 t_trxid2_list,
5094 t_date1_list,
5095 t_date2_list);
5096
5097 --dbms_output.put_line('generate ex27');
5098
5099 --------------------------------------------------------------------------
5100 -- get the shipping control
5101 ---------------------------------------------------------------------------
5102 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
5103 b_customer_site_name(j),
5104 b_publisher_name(j),
5105 b_publisher_site_name(j));
5106
5107 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
5108 nvl(l_shipping_control,1));
5109
5110 l_exception_type := msc_x_netting_pkg.G_EXCEP27; -- fulfillment qty excess for customer PO
5111 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
5112 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
5113 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
5114
5115
5116 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
5117 b_publisher_name(j),
5118 b_publisher_site_id(j),
5119 b_publisher_site_name(j),
5120 b_item_id(j),
5121 b_item_name(j),
5122 b_item_desc(j),
5123 l_exception_type,
5124 l_exception_type_name,
5125 l_exception_group,
5126 l_exception_group_name,
5127 l_last_so_trx_id, --b_trx_id1(j),
5128 b_trx_id2(j),
5129 b_customer_id(j),
5130 b_customer_name(j),
5131 b_customer_site_id(j),
5132 b_customer_site_name(j),
5133 b_customer_item_name(j),
5134 null,--l_supplier_id,
5135 null,
5136 null, --l_supplier_site_id,
5137 null,
5138 b_supplier_item_name(j), --item name
5139 l_total_qty,
5140 b_tp_po_qty(j),
5141 null,
5142 l_threshold2,
5143 null, --lead time
5144 null, --l_item_min,
5145 null, --l_item_max,
5146 b_order_number(j),
5147 b_release_number(j),
5148 b_line_number(j),
5149 b_end_order_number(j),
5150 b_end_order_rel_number(j),
5151 b_end_order_line_number(j),
5152 null, --b_so_creation_date(j),
5153 null, --b_po_creation_date(j),
5154 b_so_receipt_date(j),
5155 b_po_receipt_date(j),
5156 b_so_ship_date(j),
5157 b_po_ship_date(j),
5158 null,
5159 l_exception_basis,
5160 a_company_id,
5161 a_company_name,
5162 a_company_site_id,
5163 a_company_site_name,
5164 a_item_id,
5165 a_item_name,
5166 a_item_desc,
5167 a_exception_type,
5168 a_exception_type_name,
5169 a_exception_group,
5170 a_exception_group_name,
5171 a_trx_id1,
5172 a_trx_id2,
5173 a_customer_id,
5174 a_customer_name,
5175 a_customer_site_id,
5176 a_customer_site_name,
5177 a_customer_item_name,
5178 a_supplier_id,
5179 a_supplier_name,
5180 a_supplier_site_id,
5181 a_supplier_site_name,
5182 a_supplier_item_name,
5183 a_number1,
5184 a_number2,
5185 a_number3,
5186 a_threshold,
5187 a_lead_time,
5188 a_item_min_qty,
5189 a_item_max_qty,
5190 a_order_number,
5191 a_release_number,
5192 a_line_number,
5193 a_end_order_number,
5194 a_end_order_rel_number,
5195 a_end_order_line_number,
5196 a_creation_date,
5197 a_tp_creation_date,
5198 a_date1,
5199 a_date2,
5200 a_date3,
5201 a_date4,
5202 a_date5,
5203 a_exception_basis);
5204 l_inserted_record := l_inserted_record + 1;
5205
5206 -------------------------------------------------
5207 -- generate complement exceptions
5208 -------------------------------------------------
5209 if (b_po_last_refnum(j) <= p_refresh_number) then
5210 --dbms_output.put_line('In complement 27');
5211
5212 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP28,
5213 b_customer_id(j),
5214 b_customer_site_id(j),
5215 b_item_id(j),
5216 b_publisher_id(j),
5217 b_publisher_site_id(j),
5218 null,
5219 null,
5220 b_po_ship_date(j));
5221
5222 if (l_total_qty > b_po_qty(j) + (l_complement_threshold*b_po_qty(j)/100) ) then
5223
5224 l_exception_type := msc_x_netting_pkg.G_EXCEP28; --fulfillment qty excess from your sup
5225 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
5226 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
5227 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
5228
5229 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
5230 b_customer_name(j),
5231 b_customer_site_id(j),
5232 b_customer_site_name(j),
5233 b_item_id(j),
5234 b_item_name(j),
5235 b_item_desc(j),
5236 l_exception_type,
5237 l_exception_type_name,
5238 l_exception_group,
5239 l_exception_group_name,
5240 b_trx_id2(j),
5241 l_last_so_trx_id, --b_trx_id1(j),
5242 null, --l_customer_id,
5243 null,
5244 null, --l_customer_site_id,
5245 null,
5246 b_customer_item_name(j),
5247 b_publisher_id(j),
5248 b_publisher_name(j),
5249 b_publisher_site_id(j),
5250 b_publisher_site_name(j),
5251 b_supplier_item_name(j),
5252 b_po_qty(j),
5253 l_total_qty,
5254 null,
5255 l_complement_threshold,
5256 null, --lead time
5257 null, --l_item_min,
5258 null, --l_item_max,
5259 b_order_number(j),
5260 b_release_number(j),
5261 b_line_number(j),
5262 b_end_order_number(j),
5263 b_end_order_rel_number(j),
5264 b_end_order_line_number(j),
5265 null, --b_po_creation_date(j),
5266 null, --b_so_creation_date(j),
5267 b_po_receipt_date(j),
5268 b_so_receipt_date(j),
5269 b_so_ship_date(j),
5270 b_po_ship_date(j),
5271 null,
5272 l_exception_basis,
5273 a_company_id,
5274 a_company_name,
5275 a_company_site_id,
5276 a_company_site_name,
5277 a_item_id,
5278 a_item_name,
5279 a_item_desc,
5280 a_exception_type,
5281 a_exception_type_name,
5282 a_exception_group,
5283 a_exception_group_name,
5284 a_trx_id1,
5285 a_trx_id2,
5286 a_customer_id,
5287 a_customer_name,
5288 a_customer_site_id,
5289 a_customer_site_name,
5290 a_customer_item_name,
5291 a_supplier_id,
5292 a_supplier_name,
5293 a_supplier_site_id,
5294 a_supplier_site_name,
5295 a_supplier_item_name,
5296 a_number1,
5297 a_number2,
5298 a_number3,
5299 a_threshold,
5300 a_lead_time,
5301 a_item_min_qty,
5302 a_item_max_qty,
5303 a_order_number,
5304 a_release_number,
5305 a_line_number,
5306 a_end_order_number,
5307 a_end_order_rel_number,
5308 a_end_order_line_number,
5309 a_creation_date,
5310 a_tp_creation_date,
5311 a_date1,
5312 a_date2,
5313 a_date3,
5314 a_date4,
5315 a_date5,
5316 a_exception_basis);
5317 l_inserted_record := l_inserted_record + 1;
5318
5319 end if;
5320 end if;
5321 END IF; -- if j=1
5322 end if;
5323 END LOOP;
5324 END IF;
5325 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(27) ||
5326 ':' ||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
5327
5328 /* reset the trxid */
5329
5330 l_generate_complement := null;
5331
5332 --dbms_output.put_line('Exception 28'); --customer centric
5333 Open exception_28 ( p_refresh_number);
5334 fetch exception_28 BULK COLLECT INTO
5335 b_trx_id1,
5336 b_publisher_id,
5337 b_publisher_name,
5338 b_publisher_site_id,
5339 b_publisher_site_name,
5340 b_item_id,
5341 b_item_name,
5342 b_item_desc,
5343 b_customer_item_name,
5344 b_customer_item_desc,
5345 b_po_key_date,
5346 b_po_ship_date,
5347 b_po_receipt_date,
5348 b_posting_po_qty,
5349 b_po_qty,
5350 b_tp_po_qty,
5351 b_order_number,
5352 b_release_number,
5353 b_line_number,
5354 b_supplier_id,
5355 b_supplier_name,
5356 b_supplier_site_id,
5357 b_supplier_site_name,
5358 b_supplier_item_name,
5359 b_supplier_item_desc,
5360 b_po_creation_date,
5361 b_po_last_refnum,
5362 b_trx_id2,
5363 b_so_key_date,
5364 b_so_ship_date,
5365 b_so_receipt_date,
5366 b_posting_so_qty,
5367 b_so_qty,
5368 b_tp_so_qty,
5369 b_end_order_number,
5370 b_end_order_rel_number,
5371 b_end_order_line_number,
5372 b_customer_id,
5373 b_customer_name,
5374 b_customer_site_id, --owning org
5375 b_customer_site_name,
5376 b_so_creation_date,
5377 b_so_last_refnum;
5378 CLOSE exception_28;
5379
5380 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
5381 FOR j in 1..b_trx_id1.COUNT
5382 LOOP
5383 --dbms_output.put_line('-----Exception28: Trx id 1 = ' || b_trx_id1(j));
5384 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
5385
5386 --======================================================
5387 -- archive old exception and its complement
5388 --======================================================
5389 msc_x_netting_pkg.add_to_delete_tbl(
5390 b_publisher_id(j),
5391 b_publisher_site_id(j),
5392 null,
5393 null,
5394 b_supplier_id(j),
5395 b_supplier_site_id(j),
5396 b_item_id(j),
5397 msc_x_netting_pkg.G_MATERIAL_EXCESS,
5398 msc_x_netting_pkg.G_EXCEP28,
5399 b_trx_id1(j),
5400 b_trx_id2(j),
5401 null,
5402 null,
5403 t_company_list,
5404 t_company_site_list,
5405 t_customer_list,
5406 t_customer_site_list,
5407 t_supplier_list,
5408 t_supplier_site_list,
5409 t_item_list,
5410 t_group_list,
5411 t_type_list,
5412 t_trxid1_list,
5413 t_trxid2_list,
5414 t_date1_list,
5415 t_date2_list);
5416
5417 msc_x_netting_pkg.add_to_delete_tbl(
5418 b_supplier_id(j),
5419 b_supplier_site_id(j),
5420 b_publisher_id(j),
5421 b_publisher_site_id(j),
5422 null,
5423 null,
5424 b_item_id(j),
5425 msc_x_netting_pkg.G_MATERIAL_EXCESS,
5426 msc_x_netting_pkg.G_EXCEP27,
5427 b_trx_id2(j),
5428 b_trx_id1(j),
5429 null,
5430 null,
5431 t_company_list,
5432 t_company_site_list,
5433 t_customer_list,
5434 t_customer_site_list,
5435 t_supplier_list,
5436 t_supplier_site_list,
5437 t_item_list,
5438 t_group_list,
5439 t_type_list,
5440 t_trxid1_list,
5441 t_trxid2_list,
5442 t_date1_list,
5443 t_date2_list);
5444
5445
5446 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP28,
5447 b_publisher_id(j),
5448 b_publisher_site_id(j),
5449 b_item_id(j),
5450 b_supplier_id(j),
5451 b_supplier_site_id(j),
5452 null,
5453 null,
5454 b_po_key_date(j));
5455
5456 l_total_qty := msc_x_netting_pkg.get_total_qty(b_order_number(j),
5457 b_release_number(j),
5458 b_line_number(j),
5459 b_supplier_id(j),
5460 b_supplier_site_id(j),
5461 b_publisher_id(j),
5462 b_publisher_site_id(j),
5463 b_item_id(j));
5464
5465 IF (l_total_qty > b_po_qty(j) + (l_threshold2*b_po_qty(j)/100) ) then
5466
5467 --======================================================
5468 -- Clean up the opposite exception and its complement
5469 --======================================================
5470 msc_x_netting_pkg.add_to_delete_tbl(
5471 b_publisher_id(j),
5472 b_publisher_site_id(j),
5473 null,
5474 null,
5475 b_supplier_id(j),
5476 b_supplier_site_id(j),
5477 b_item_id(j),
5478 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
5479 msc_x_netting_pkg.G_EXCEP8,
5480 b_trx_id1(j),
5481 b_trx_id2(j),
5482 null,
5483 null,
5484 t_company_list,
5485 t_company_site_list,
5486 t_customer_list,
5487 t_customer_site_list,
5488 t_supplier_list,
5489 t_supplier_site_list,
5490 t_item_list,
5491 t_group_list,
5492 t_type_list,
5493 t_trxid1_list,
5494 t_trxid2_list,
5495 t_date1_list,
5496 t_date2_list);
5497
5498 msc_x_netting_pkg.add_to_delete_tbl(
5499 b_supplier_id(j),
5500 b_supplier_site_id(j),
5501 b_publisher_id(j),
5502 b_publisher_site_id(j),
5503 null,
5504 null,
5505 b_item_id(j),
5506 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
5507 msc_x_netting_pkg.G_EXCEP7,
5508 b_trx_id2(j),
5509 b_trx_id1(j),
5510 null,
5511 null,
5512 t_company_list,
5513 t_company_site_list,
5514 t_customer_list,
5515 t_customer_site_list,
5516 t_supplier_list,
5517 t_supplier_site_list,
5518 t_item_list,
5519 t_group_list,
5520 t_type_list,
5521 t_trxid1_list,
5522 t_trxid2_list,
5523 t_date1_list,
5524 t_date2_list);
5525
5526 IF (j=1 OR (b_publisher_id(j -1) <> b_publisher_id(j) OR
5527 b_publisher_site_id(j-1) <> b_publisher_site_id(j) OR
5528 b_customer_id(j-1) <> b_customer_id(j) OR
5529 b_customer_site_id(j-1) <> b_customer_site_id(j) OR
5530 b_item_id(j-1) <> b_item_id(j) OR
5531 b_order_number(j-1) <> b_order_number(j) OR
5532 b_release_number(j-1) <> b_release_number(j) OR
5533 b_line_number(j-1) <> b_line_number(j) )) THEN
5534
5535 --dbms_output.put_line('in if');
5536 -------------------------------------------------------------------------
5537 -- get the latest SO to populate the exception
5538 -------------------------------------------------------------------------
5539 BEGIN
5540 select transaction_id, receipt_date, ship_date, order_number, line_number, release_number, last_refresh_number
5541 into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
5542 b_end_order_line_number(j), b_end_order_rel_number(j), b_so_last_refnum(j)
5543 from msc_sup_dem_entries
5544 where publisher_id = b_supplier_id(j)
5545 and publisher_site_id = b_supplier_site_id(j)
5546 and customer_id = b_publisher_id(j)
5547 and customer_site_id = b_publisher_site_id(j)
5548 and inventory_item_id = b_item_id(j)
5549 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
5550 and end_order_number = b_order_number(j)
5551 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
5552 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
5553 and key_date = (select max(key_date)
5554 from msc_sup_dem_entries
5555 where publisher_id = b_supplier_id(j)
5556 and publisher_site_id = b_supplier_site_id(j)
5557 and customer_id = b_publisher_id(j)
5558 and customer_site_id = b_publisher_site_id(j)
5559 and inventory_item_id = b_item_id(j)
5560 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
5561 and end_order_number = b_order_number(j)
5562 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
5563 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1) );
5564 EXCEPTION
5565 when others then
5566 null;
5567 --dbms_output.put_line('Error ' || sqlerrm);
5568 END;
5569
5570 SELECT max(last_refresh_number)
5571 into b_so_last_refnum(j)
5572 from msc_sup_dem_entries
5573 where publisher_id = b_supplier_id(j)
5574 and publisher_site_id = b_supplier_site_id(j)
5575 and customer_id = b_publisher_id(j)
5576 and customer_site_id = b_publisher_site_id(j)
5577 and inventory_item_id = b_item_id(j)
5578 and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
5579 and end_order_number = b_order_number(j)
5580 and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
5581 and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1);
5582
5583
5584 --dbms_output.put_line('trx id ' || l_last_so_trx_id || ' last ref num ' || b_so_last_refnum(j) || ' p_refnum ' || p_refresh_number);
5585
5586 --======================================================
5587 -- archive old exception and its complement
5588 --======================================================
5589 msc_x_netting_pkg.add_to_delete_tbl(
5590 b_publisher_id(j),
5591 b_publisher_site_id(j),
5592 null,
5593 null,
5594 b_supplier_id(j),
5595 b_supplier_site_id(j),
5596 b_item_id(j),
5597 msc_x_netting_pkg.G_MATERIAL_EXCESS,
5598 msc_x_netting_pkg.G_EXCEP28,
5599 b_trx_id1(j),
5600 l_last_so_trx_id,
5601 null,
5602 null,
5603 t_company_list,
5604 t_company_site_list,
5605 t_customer_list,
5606 t_customer_site_list,
5607 t_supplier_list,
5608 t_supplier_site_list,
5609 t_item_list,
5610 t_group_list,
5611 t_type_list,
5612 t_trxid1_list,
5613 t_trxid2_list,
5614 t_date1_list,
5615 t_date2_list);
5616
5617 msc_x_netting_pkg.add_to_delete_tbl(
5618 b_supplier_id(j),
5619 b_supplier_site_id(j),
5620 b_publisher_id(j),
5621 b_publisher_site_id(j),
5622 null,
5623 null,
5624 b_item_id(j),
5625 msc_x_netting_pkg.G_MATERIAL_EXCESS,
5626 msc_x_netting_pkg.G_EXCEP27,
5627 l_last_so_trx_id,
5628 b_trx_id1(j),
5629 null,
5630 null,
5631 t_company_list,
5632 t_company_site_list,
5633 t_customer_list,
5634 t_customer_site_list,
5635 t_supplier_list,
5636 t_supplier_site_list,
5637 t_item_list,
5638 t_group_list,
5639 t_type_list,
5640 t_trxid1_list,
5641 t_trxid2_list,
5642 t_date1_list,
5643 t_date2_list);
5644
5645 msc_x_netting_pkg.add_to_delete_tbl(
5646 b_publisher_id(j),
5647 b_publisher_site_id(j),
5648 null,
5649 null,
5650 b_supplier_id(j),
5651 b_supplier_site_id(j),
5652 b_item_id(j),
5653 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
5654 msc_x_netting_pkg.G_EXCEP8,
5655 b_trx_id1(j),
5656 l_last_so_trx_id,
5657 null,
5658 null,
5659 t_company_list,
5660 t_company_site_list,
5661 t_customer_list,
5662 t_customer_site_list,
5663 t_supplier_list,
5664 t_supplier_site_list,
5665 t_item_list,
5666 t_group_list,
5667 t_type_list,
5668 t_trxid1_list,
5669 t_trxid2_list,
5670 t_date1_list,
5671 t_date2_list);
5672
5673 msc_x_netting_pkg.add_to_delete_tbl(
5674 b_supplier_id(j),
5675 b_supplier_site_id(j),
5676 b_publisher_id(j),
5677 b_publisher_site_id(j),
5678 null,
5679 null,
5680 b_item_id(j),
5681 msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
5682 msc_x_netting_pkg.G_EXCEP7,
5683 l_last_so_trx_id,
5684 b_trx_id1(j),
5685 null,
5686 null,
5687 t_company_list,
5688 t_company_site_list,
5689 t_customer_list,
5690 t_customer_site_list,
5691 t_supplier_list,
5692 t_supplier_site_list,
5693 t_item_list,
5694 t_group_list,
5695 t_type_list,
5696 t_trxid1_list,
5697 t_trxid2_list,
5698 t_date1_list,
5699 t_date2_list);
5700
5701
5702 --dbms_output.put_line('generate ex28');
5703 --------------------------------------------------------------------------
5704 -- get the shipping control
5705 ---------------------------------------------------------------------------
5706 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
5707 b_publisher_site_name(j),
5708 b_supplier_name(j),
5709 b_supplier_site_name(j));
5710
5711 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
5712 nvl(l_shipping_control,1));
5713 l_exception_type := msc_x_netting_pkg.G_EXCEP28;
5714 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
5715 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
5716 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
5717
5718 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
5719 b_publisher_name(j),
5720 b_publisher_site_id(j),
5721 b_publisher_site_name(j),
5722 b_item_id(j),
5723 b_item_name(j),
5724 b_item_desc(j),
5725 l_exception_type,
5726 l_exception_type_name,
5727 l_exception_group,
5728 l_exception_group_name,
5729 b_trx_id1(j),
5730 l_last_so_trx_id, --b_trx_id2(j),
5731 null, --l_customer_id,
5732 null, --
5733 null, --l_customer_site_id,
5734 null,
5735 b_customer_item_name(j),
5736 b_supplier_id(j),
5737 b_supplier_name(j),
5738 b_supplier_site_id(j),
5739 b_supplier_site_name(j),
5740 b_supplier_item_name(j),
5741 b_po_qty(j), --number1
5742 l_total_qty, --number2
5743 null, --number3
5744 l_threshold2,
5745 null, --lead time
5746 null, --l_item_min,
5747 null, --l_item_max,
5748 b_order_number(j),
5749 b_release_number(j),
5750 b_line_number(j),
5751 b_end_order_number(j),
5752 b_end_order_rel_number(j),
5753 b_end_order_line_number(j),
5754 null, --b_po_creation_date(j),
5755 null, --b_so_creation_date(j),
5756 b_po_receipt_date(j),
5757 b_so_receipt_date(j),
5758 b_so_ship_date(j),
5759 b_po_ship_date(j),
5760 null,
5761 l_exception_basis,
5762 a_company_id,
5763 a_company_name,
5764 a_company_site_id,
5765 a_company_site_name,
5766 a_item_id,
5767 a_item_name,
5768 a_item_desc,
5769 a_exception_type,
5770 a_exception_type_name,
5771 a_exception_group,
5772 a_exception_group_name,
5773 a_trx_id1,
5774 a_trx_id2,
5775 a_customer_id,
5776 a_customer_name,
5777 a_customer_site_id,
5778 a_customer_site_name,
5779 a_customer_item_name,
5780 a_supplier_id,
5781 a_supplier_name,
5782 a_supplier_site_id,
5783 a_supplier_site_name,
5784 a_supplier_item_name,
5785 a_number1,
5786 a_number2,
5787 a_number3,
5788 a_threshold,
5789 a_lead_time,
5790 a_item_min_qty,
5791 a_item_max_qty,
5792 a_order_number,
5793 a_release_number,
5794 a_line_number,
5795 a_end_order_number,
5796 a_end_order_rel_number,
5797 a_end_order_line_number,
5798 a_creation_date,
5799 a_tp_creation_date,
5800 a_date1,
5801 a_date2,
5802 a_date3,
5803 a_date4,
5804 a_date5,
5805 a_exception_basis);
5806 l_inserted_record := l_inserted_record + 1;
5807
5808 ------------------------------------------------------
5809 -- generate complement exception
5810 ------------------------------------------------------
5811 if (b_so_last_refnum(j) <= p_refresh_number) then
5812 --dbms_output.put_line('In complement28');
5813
5814 --dbms_output.put_line('generate complement ex');
5815 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP27,
5816 b_supplier_id(j),
5817 b_supplier_site_id(j),
5818 b_item_id(j),
5819 null,
5820 null,
5821 b_publisher_id(j),
5822 b_publisher_site_id(j),
5823 b_so_key_date(j));
5824
5825 if (l_total_qty > b_po_qty(j) + (l_complement_threshold*b_po_qty(j)/100) ) THEN
5826
5827 l_exception_type := msc_x_netting_pkg.G_EXCEP27; --fulfillment qty excess for your cust po
5828 l_exception_group := msc_x_netting_pkg.G_MATERIAL_EXCESS;
5829 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
5830 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
5831
5832 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
5833 b_supplier_name(j),
5834 b_supplier_site_id(j),
5835 b_supplier_site_name(j),
5836 b_item_id(j),
5837 b_item_name(j),
5838 b_item_desc(j),
5839 l_exception_type,
5840 l_exception_type_name,
5841 l_exception_group,
5842 l_exception_group_name,
5843 l_last_so_trx_id, --b_trx_id2(j),
5844 b_trx_id1(j),
5845 b_publisher_id(j),
5846 b_publisher_name(j),
5847 b_publisher_site_id(j),
5848 b_publisher_site_name(j),
5849 b_customer_item_name(j),
5850 null, --l_supplier_id,
5851 null,
5852 null, --l_supplier_site_id,
5853 null,
5854 b_supplier_item_name(j), --itme name
5855 l_total_qty,
5856 b_tp_po_qty(j),
5857 null,
5858 l_complement_threshold,
5859 null, --lead time
5860 null, --l_item_min,
5861 null, --l_item_max,
5862 b_order_number(j),
5863 b_release_number(j),
5864 b_line_number(j),
5865 b_end_order_number(j),
5866 b_end_order_rel_number(j),
5867 b_end_order_line_number(j),
5868 null, --b_so_creation_date(j),
5869 null, --b_po_creation_date(j),
5870 b_so_receipt_date(j),
5871 b_po_receipt_date(j),
5872 b_so_ship_date(j),
5873 b_po_ship_date(j),
5874 null,
5875 l_exception_basis,
5876 a_company_id,
5877 a_company_name,
5878 a_company_site_id,
5879 a_company_site_name,
5880 a_item_id,
5881 a_item_name,
5882 a_item_desc,
5883 a_exception_type,
5884 a_exception_type_name,
5885 a_exception_group,
5886 a_exception_group_name,
5887 a_trx_id1,
5888 a_trx_id2,
5889 a_customer_id,
5890 a_customer_name,
5891 a_customer_site_id,
5892 a_customer_site_name,
5893 a_customer_item_name,
5894 a_supplier_id,
5895 a_supplier_name,
5896 a_supplier_site_id,
5897 a_supplier_site_name,
5898 a_supplier_item_name,
5899 a_number1,
5900 a_number2,
5901 a_number3,
5902 a_threshold,
5903 a_lead_time,
5904 a_item_min_qty,
5905 a_item_max_qty,
5906 a_order_number,
5907 a_release_number,
5908 a_line_number,
5909 a_end_order_number,
5910 a_end_order_rel_number,
5911 a_end_order_line_number,
5912 a_creation_date,
5913 a_tp_creation_date,
5914 a_date1,
5915 a_date2,
5916 a_date3,
5917 a_date4,
5918 a_date5,
5919 a_exception_basis);
5920 l_inserted_record := l_inserted_record + 1;
5921
5922 end if;
5923 END IF; /* generate complement exception */
5924 END IF; --if j = 1
5925 end if;
5926 END LOOP;
5927 END IF;
5928 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(28) ||
5929 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
5930
5931 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
5932 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_MATERIAL_EXCESS) || ':' || l_inserted_record);
5933
5934 EXCEPTION
5935 WHEN OTHERS THEN
5936 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING2_PKG.Compute_Material_Excess');
5937 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
5938 -- dbms_output.put_line('Error ' || sqlerrm);
5939 return;
5940
5941 END Compute_Material_Excess;
5942
5943 END MSC_X_NETTING2_PKG;
5944