[Home] [Help]
PACKAGE BODY: APPS.MSC_X_NETTING_PKG
Source
1 PACKAGE BODY MSC_X_NETTING_PKG AS
2 /* $Header: MSCXNETB.pls 120.6 2008/01/07 10:15:08 dejoshi ship $ */
3
4 TYPE ExcpTblTyp IS TABLE OF VARCHAR2(80);
5
6 lv_exception_type ExcpTblTyp := ExcpTblTyp();
7 lv_exception_grp ExcpTblTyp := ExcpTblTyp();
8
9 --================================================================================
10 -- LAUNCH_PLANS
11 --=================================================================================
12 PROCEDURE LAUNCH_ENGINE (p_errbuf OUT NOCOPY VARCHAR2,
13 p_retcode OUT NOCOPY VARCHAR2,
14 p_early_order IN VARCHAR2,
15 p_changed_order IN VARCHAR2,
16 p_forecast_accuracy IN VARCHAR2,
17 p_forecast_mismatch IN VARCHAR2,
18 p_late_order IN VARCHAR2,
19 p_material_excess IN VARCHAR2,
20 p_material_shortage IN VARCHAR2,
21 p_performance IN VARCHAR2,
22 p_potential_late_order IN VARCHAR2,
23 p_response_required IN VARCHAR2,
24 p_custom_exception IN VARCHAR2) IS
25
26
27 l_errbuf Varchar2(1000);
28 l_retcode Varchar2(240);
29 BEGIN
30
31 select meaning
32 BULK COLLECT INTO lv_exception_grp
33 from mfg_lookups
34 where lookup_type = 'MSC_X_EXCEPTION_GROUP'
35 order by lookup_code;
36
37 select meaning
38 BULK COLLECT INTO lv_exception_type
39 from mfg_lookups
40 where lookup_type = 'MSC_X_EXCEPTION_TYPE'
41 order by lookup_code;
42
43 IF (p_late_order in (to_char(2), 'N') and
44 p_material_shortage in (to_char(2), 'N') and
45 p_response_required in (to_char(2), 'N') and
46 p_potential_late_order in (to_char(2), 'N') and
47 p_forecast_mismatch in (to_char(2), 'N') and
48 p_early_order in (to_char(2), 'N') and
49 p_material_excess in (to_char(2), 'N') and
50 p_changed_order in (to_char(2), 'N') and
51 p_forecast_accuracy in (to_char(2), 'N') and
52 p_performance in (to_char(2), 'N') and
53 p_custom_exception in (to_char(2), 'N')) THEN
54
55 return;
56 ELSE
57
58 -- initialize the pmf setup
59 msc_pmf_pkg.process_pmf_thresholds;
60
61 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Launch Regular Exception at: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
62 start_netting(p_early_order,
63 p_changed_order,
64 p_forecast_accuracy,
65 p_forecast_mismatch,
66 p_late_order,
67 p_material_excess,
68 p_material_shortage,
69 p_performance,
70 p_potential_late_order,
71 p_response_required,
72 p_custom_exception);
73 END IF;
74
75 END LAUNCH_ENGINE;
76
77 --===============================================================================
78 -- START_NETTING
79 --===============================================================================
80 PROCEDURE START_NETTING (p_early_order IN VARCHAR2,
81 p_changed_order IN VARCHAR2,
82 p_forecast_accuracy IN VARCHAR2,
83 p_forecast_mismatch IN VARCHAR2,
84 p_late_order IN VARCHAR2,
85 p_material_excess IN VARCHAR2,
86 p_material_shortage IN VARCHAR2,
87 p_performance IN VARCHAR2,
88 p_potential_late_order IN VARCHAR2,
89 p_response_required IN VARCHAR2,
90 p_custom_exception IN VARCHAR2) IS
91
92
93 l_max_refresh_number Number;
94 l_errbuf Varchar2(1000);
95 l_retcode Varchar2(240);
96 l_retnum Number;
97 l_late_order_refnum Number;
98 l_material_shortage_refnum Number;
99 l_response_required_refnum Number;
100 l_forecast_mismatch_refnum Number;
101 l_early_order_refnum Number;
102 l_material_excess_refnum Number;
103 l_changed_order_refnum Number;
104 l_forecast_accuracy_refnum Number;
105 l_performance_refnum Number;
106
107 --========================================================
108 -- create the constructor the plsql table
109 --========================================================
110 t_item_list number_arr := number_arr();
111 t_company_list number_arr := number_arr();
112 t_company_site_list number_arr := number_arr();
113 t_customer_list number_arr := number_arr();
114 t_customer_site_list number_arr := number_arr();
115 t_supplier_list number_arr := number_arr();
116 t_supplier_site_list number_arr := number_arr();
117 t_group_list number_arr := number_arr();
118 t_type_list number_arr := number_arr();
119 t_trxid1_list number_arr := number_arr();
120 t_trxid2_list number_arr := number_arr();
121 t_date1_list date_arr := date_arr();
122 t_date2_list date_arr := date_arr();
123 a_company_id number_arr := number_arr();
124 a_company_name publisherList := publisherList();
125 a_company_site_id number_arr := number_arr();
126 a_company_site_name pubsiteList := pubsiteList();
127 a_item_id number_arr := number_arr();
128 a_item_name itemnameList := itemnameList();
129 a_item_desc itemdescList := itemdescList();
130 a_exception_type number_arr := number_arr();
131 a_exception_type_name exceptypeList := exceptypeList();
132 a_exception_group number_arr := number_arr();
133 a_exception_group_name excepgroupList := excepgroupList();
134 a_trx_id1 number_arr := number_arr();
135 a_trx_id2 number_arr := number_arr();
136 a_customer_id number_arr := number_arr();
137 a_customer_name customerList := customerList();
138 a_customer_site_id number_arr := number_arr();
139 a_customer_site_name custsiteList := custsiteList();
140 a_customer_item_name itemnameList := itemnameList();
141 a_supplier_id number_arr := number_arr();
142 a_supplier_name supplierList := supplierList();
143 a_supplier_site_id number_arr := number_arr();
144 a_supplier_site_name suppsiteList := suppsiteList();
145 a_supplier_item_name itemnameList := itemnameList();
146 a_number1 number_arr := number_arr();
147 a_number2 number_arr := number_arr();
148 a_number3 number_arr := number_arr();
149 a_threshold number_arr := number_arr();
150 a_lead_time number_arr := number_arr();
151 a_item_min_qty number_arr := number_arr();
152 a_item_max_qty number_arr := number_arr();
153 a_order_number ordernumberList := ordernumberList();
154 a_release_number releasenumList := releasenumList();
155 a_line_number linenumList := linenumList();
156 a_end_order_number ordernumberList := ordernumberList();
157 a_end_order_rel_number releasenumList := releasenumList();
158 a_end_order_line_number linenumList := linenumList();
159 a_creation_date date_arr := date_arr();
160 a_tp_creation_date date_arr := date_arr();
161 a_date1 date_arr := date_arr();
162 a_date2 date_arr := date_arr();
163 a_date3 date_arr := date_arr();
164 a_date4 date_arr := date_arr();
165 a_date5 date_arr := date_arr();
166 a_exception_basis exceptbasisList := exceptbasisList();
167
168 l_request_id Number;
169 l_refreshnum Number;
170
171
172 BEGIN
173
174 -------------------------------------------------------------------
175 -- Make sure the the exeption group is exist in msc_plan_org_status
176 -- for the first run. Use this table for set the refresh number
177 -- for each run by exception group. Status column will be
178 -- populated with refresh number
179 -------------------------------------------------------------------
180 --dbms_output.put_line('Start populating exception group');
181
182 populate_exception_org;
183
184 ---------------------------------------------------------------------
185 --if records are loaded during the prior run of the
186 --planning engine and as a result refresh numbers are updated
187 --and these records are not picked up. Therefore, get the refresh
188 --number before the engine run. If any record is loaded during
189 --the engine run with later refresh number, then the record
190 --will be included in the next run.
191 ---------------------------------------------------------------------
192 begin
193 select nvl(max(last_refresh_number),0)
194 into l_max_refresh_number
195 from msc_sup_dem_entries
196 where last_refresh_number > G_ZERO;
197 exception
198 when no_data_found then
199 l_max_refresh_number := 0;
200 end;
201
202 --dbms_output.put_line('Max refresh number ' || l_max_refresh_number);
203 -------------------------------------------------------------
204 --Make sure delete all the exceptions where the quantity = 0
205 --in msc_sup_dem_entries because later on this transaction
206 --will be purged.
207 -------------------------------------------------------------
208
209 Delete_Exec_Order_Dependency(l_max_refresh_number);
210
211
212 -------------------------------------------------------------------
213 -- Deleting all old exceptions depending on the profile option set
214 -------------------------------------------------------------------
215
216 DELETE_EXCEP (); --added for bug#6729356
217
218 /*-----------------------------------------------------------
219 CHANGED_ORDER group need to be called first if it set to Y
220 before purging the transaction in msc_sup_dem_entries where
221 quantity = 0.
222 Cancelled Order (exception_34) will base on a cancelled PO
223 where quantity = 0 to generate.
224 Other exceptions which are depending on Execution Entities
225 will not capture any transactions where quantity = 0 to generate.
226
227 The reason behind is
228 when we use the 'D' sync indicator during the data upload the records
229 that need to be deleted are not removed from the table (msc_sup_dem_entries).
230 Instead the quantity is set to zero and the transaction id is updated
231 to ensure that the exceptions for the items deleted are recomputed
232 The current netting engine will only consider this (quantity = 0)
233 for non-execution entities exceptions.
234 -----------------------------------------------------------*/
235 IF (p_changed_order in (to_char(1), 'Y')) THEN
236
237 select status
238 into l_changed_order_refnum
239 from msc_plan_org_status
240 where plan_id = G_PLAN_ID
241 and organization_id = G_GROUP8
242 and sr_instance_id = G_SR_INSTANCE_ID;
243 --dbms_output.put_line('Max refresh_number : ' || l_changed_order_refnum);
244
245 msc_x_netting4_pkg.compute_changed_order(l_changed_order_refnum,
246 t_company_list,
247 t_company_site_list,
248 t_customer_list,
249 t_customer_site_list,
250 t_supplier_list,
251 t_supplier_site_list,
252 t_item_list,
253 t_group_list,
254 t_type_list,
255 t_trxid1_list,
256 t_trxid2_list,
257 t_date1_list,
258 t_date2_list,
259 a_company_id,
260 a_company_name,
261 a_company_site_id,
262 a_company_site_name,
263 a_item_id,
264 a_item_name,
265 a_item_desc,
266 a_exception_type,
267 a_exception_type_name ,
268 a_exception_group,
269 a_exception_group_name,
270 a_trx_id1,
271 a_trx_id2 ,
272 a_customer_id,
273 a_customer_name,
274 a_customer_site_id,
275 a_customer_site_name ,
276 a_customer_item_name,
277 a_supplier_id ,
278 a_supplier_name ,
279 a_supplier_site_id ,
280 a_supplier_site_name,
281 a_supplier_item_name,
282 a_number1,
283 a_number2,
284 a_number3 ,
285 a_threshold,
286 a_lead_time,
287 a_item_min_qty,
288 a_item_max_qty,
289 a_order_number,
290 a_release_number,
291 a_line_number,
292 a_end_order_number,
293 a_end_order_rel_number ,
294 a_end_order_line_number,
295 a_creation_date,
296 a_tp_creation_date ,
297 a_date1,
298 a_date2,
299 a_date3,
300 a_date4,
301 a_date5,
302 a_exception_basis);
303
304 update msc_plan_org_status
305 set status = l_max_refresh_number,
306 status_date = sysdate
307 where plan_id = G_PLAN_ID
308 and organization_id = G_GROUP8
309 and sr_instance_id = G_SR_INSTANCE_ID;
310
311 END IF;
312
313 ---------------------------------------------------------------
314 -- purge the execution order where quantity = 0
315 ---------------------------------------------------------------
316 Purge_Zqty_Exec_Order(l_max_refresh_number);
317
318 IF (p_late_order in (to_char(1), 'Y')) THEN
319
320 select status
321 into l_late_order_refnum
322 from msc_plan_org_status
323 where plan_id = G_PLAN_ID
324 and organization_id = G_GROUP1
325 and sr_instance_id = G_SR_INSTANCE_ID;
326
327 --dbms_output.put_line('Late Order refresh_number : ' || l_late_order_refnum);
328
329 msc_x_netting1_pkg.compute_late_order(l_late_order_refnum,
330 t_company_list,
331 t_company_site_list,
332 t_customer_list,
333 t_customer_site_list,
334 t_supplier_list,
335 t_supplier_site_list,
336 t_item_list,
337 t_group_list,
338 t_type_list,
339 t_trxid1_list,
340 t_trxid2_list,
341 t_date1_list,
342 t_date2_list,
343 a_company_id,
344 a_company_name,
345 a_company_site_id,
346 a_company_site_name,
347 a_item_id,
348 a_item_name,
349 a_item_desc,
350 a_exception_type,
351 a_exception_type_name ,
352 a_exception_group,
353 a_exception_group_name,
354 a_trx_id1,
355 a_trx_id2 ,
356 a_customer_id,
357 a_customer_name,
358 a_customer_site_id,
359 a_customer_site_name ,
360 a_customer_item_name,
361 a_supplier_id ,
362 a_supplier_name ,
363 a_supplier_site_id ,
364 a_supplier_site_name,
365 a_supplier_item_name,
366 a_number1,
367 a_number2,
368 a_number3 ,
369 a_threshold,
370 a_lead_time,
371 a_item_min_qty,
372 a_item_max_qty,
373 a_order_number,
374 a_release_number,
375 a_line_number,
376 a_end_order_number,
377 a_end_order_rel_number ,
378 a_end_order_line_number,
379 a_creation_date,
380 a_tp_creation_date ,
381 a_date1,
382 a_date2,
383 a_date3,
384 a_date4,
385 a_date5,
386 a_exception_basis);
387
388
389 update msc_plan_org_status
390 set status = l_max_refresh_number,
391 status_date = sysdate
392 where plan_id = G_PLAN_ID
393 and organization_id = G_GROUP1
394 and sr_instance_id = G_SR_INSTANCE_ID;
395
396 END IF;
397 IF (p_material_shortage in (to_char(1), 'Y')) THEN
398
399 select status
400 into l_material_shortage_refnum
401 from msc_plan_org_status
402 where plan_id = G_PLAN_ID
403 and organization_id = G_GROUP2
404 and sr_instance_id = G_SR_INSTANCE_ID;
405 --dbms_output.put_line('Max refresh_number : ' || l_material_shortage_refnum);
406
407 MSC_EXCHANGE_BUCKETING.start_bucketing( l_material_shortage_refnum );
408
409
410 msc_x_netting2_pkg.compute_material_shortage(l_material_shortage_refnum,
411 t_company_list,
412 t_company_site_list,
413 t_customer_list,
414 t_customer_site_list,
415 t_supplier_list,
416 t_supplier_site_list,
417 t_item_list,
418 t_group_list,
419 t_type_list,
420 t_trxid1_list,
421 t_trxid2_list,
422 t_date1_list,
423 t_date2_list,
424 a_company_id,
425 a_company_name,
426 a_company_site_id,
427 a_company_site_name,
428 a_item_id,
429 a_item_name,
430 a_item_desc,
431 a_exception_type,
432 a_exception_type_name ,
433 a_exception_group,
434 a_exception_group_name,
435 a_trx_id1,
436 a_trx_id2 ,
437 a_customer_id,
438 a_customer_name,
439 a_customer_site_id,
440 a_customer_site_name ,
441 a_customer_item_name,
442 a_supplier_id ,
443 a_supplier_name ,
444 a_supplier_site_id ,
445 a_supplier_site_name,
446 a_supplier_item_name,
447 a_number1,
448 a_number2,
449 a_number3 ,
450 a_threshold,
451 a_lead_time,
452 a_item_min_qty,
453 a_item_max_qty,
454 a_order_number,
455 a_release_number,
456 a_line_number,
457 a_end_order_number,
458 a_end_order_rel_number ,
459 a_end_order_line_number,
460 a_creation_date,
461 a_tp_creation_date ,
462 a_date1,
463 a_date2,
464 a_date3,
465 a_date4,
466 a_date5,
467 a_exception_basis);
468
469 update msc_plan_org_status
470 set status = l_max_refresh_number,
471 status_date = sysdate
472 where plan_id = G_PLAN_ID
473 and organization_id = G_GROUP2
474 and sr_instance_id = G_SR_INSTANCE_ID;
475
476 END IF;
477
478 IF (p_response_required in (to_char(1), 'Y')) THEN
479
480 select status
481 into l_response_required_refnum
482 from msc_plan_org_status
483 where plan_id = G_PLAN_ID
484 and organization_id = G_GROUP3
485 and sr_instance_id = G_SR_INSTANCE_ID;
486 --dbms_output.put_line('Max refresh_number : ' || l_response_required_refnum);
487
488 msc_x_netting3_pkg.compute_response_required(
489 a_company_id,
490 a_company_name,
491 a_company_site_id,
492 a_company_site_name,
493 a_item_id,
494 a_item_name,
495 a_item_desc,
496 a_exception_type,
497 a_exception_type_name ,
498 a_exception_group,
499 a_exception_group_name,
500 a_trx_id1,
501 a_trx_id2 ,
502 a_customer_id,
503 a_customer_name,
504 a_customer_site_id,
505 a_customer_site_name ,
506 a_customer_item_name,
507 a_supplier_id ,
508 a_supplier_name ,
509 a_supplier_site_id ,
510 a_supplier_site_name,
511 a_supplier_item_name,
512 a_number1,
513 a_number2,
514 a_number3 ,
515 a_threshold,
516 a_lead_time,
517 a_item_min_qty,
518 a_item_max_qty,
519 a_order_number,
520 a_release_number,
521 a_line_number,
522 a_end_order_number,
523 a_end_order_rel_number ,
524 a_end_order_line_number,
525 a_creation_date,
526 a_tp_creation_date ,
527 a_date1,
528 a_date2,
529 a_date3,
530 a_date4,
531 a_date5,
532 a_exception_basis);
533
534 update msc_plan_org_status
535 set status = l_max_refresh_number,
536 status_date = sysdate
537 where plan_id = G_PLAN_ID
538 and organization_id = G_GROUP3
539 and sr_instance_id = G_SR_INSTANCE_ID;
540
541 END IF;
542
543 IF (p_forecast_mismatch in (to_char(1), 'Y')) THEN
544
545 select status
546 into l_forecast_mismatch_refnum
547 from msc_plan_org_status
548 where plan_id = G_PLAN_ID
549 and organization_id = G_GROUP5
550 and sr_instance_id = G_SR_INSTANCE_ID;
551 --dbms_output.put_line('Max refresh_number : ' || l_forecast_mismatch_refnum);
552 FND_FILE.PUT_LINE(FND_FILE.LOG, 'forecast mismatch refnum ' || l_forecast_mismatch_refnum);
553
554 msc_x_netting1_pkg.compute_forecast_mismatch(l_forecast_mismatch_refnum,
555 t_company_list,
556 t_company_site_list,
557 t_customer_list,
558 t_customer_site_list,
559 t_supplier_list,
560 t_supplier_site_list,
561 t_item_list,
562 t_group_list,
563 t_type_list,
564 t_trxid1_list,
565 t_trxid2_list,
566 t_date1_list,
567 t_date2_list,
568 a_company_id,
569 a_company_name,
570 a_company_site_id,
571 a_company_site_name,
572 a_item_id,
573 a_item_name,
574 a_item_desc,
575 a_exception_type,
576 a_exception_type_name ,
577 a_exception_group,
578 a_exception_group_name,
579 a_trx_id1,
580 a_trx_id2 ,
581 a_customer_id,
582 a_customer_name,
583 a_customer_site_id,
584 a_customer_site_name ,
585 a_customer_item_name,
586 a_supplier_id ,
587 a_supplier_name ,
588 a_supplier_site_id ,
589 a_supplier_site_name,
590 a_supplier_item_name,
591 a_number1,
592 a_number2,
593 a_number3 ,
594 a_threshold,
595 a_lead_time,
596 a_item_min_qty,
597 a_item_max_qty,
598 a_order_number,
599 a_release_number,
600 a_line_number,
601 a_end_order_number,
602 a_end_order_rel_number ,
603 a_end_order_line_number,
604 a_creation_date,
605 a_tp_creation_date ,
606 a_date1,
607 a_date2,
608 a_date3,
609 a_date4,
610 a_date5,
611 a_exception_basis);
612
613
614 update msc_plan_org_status
615 set status = l_max_refresh_number,
616 status_date = sysdate
617 where plan_id = G_PLAN_ID
618 and organization_id = G_GROUP5
619 and sr_instance_id = G_SR_INSTANCE_ID;
620
621
622 END IF;
623 IF (p_early_order in (to_char(1), 'Y')) THEN
624 select status
625 into l_early_order_refnum
626 from msc_plan_org_status
627 where plan_id = G_PLAN_ID
628 and organization_id = G_GROUP6
629 and sr_instance_id = G_SR_INSTANCE_ID;
630 --dbms_output.put_line('Max refresh_number : ' || l_early_order_refnum);
631
632 msc_x_netting1_pkg.compute_early_order(l_early_order_refnum,
633 t_company_list,
634 t_company_site_list,
635 t_customer_list,
636 t_customer_site_list,
637 t_supplier_list,
638 t_supplier_site_list,
639 t_item_list,
640 t_group_list,
641 t_type_list,
642 t_trxid1_list,
643 t_trxid2_list,
644 t_date1_list,
645 t_date2_list,
646 a_company_id,
647 a_company_name,
648 a_company_site_id,
649 a_company_site_name,
650 a_item_id,
651 a_item_name,
652 a_item_desc,
653 a_exception_type,
654 a_exception_type_name ,
655 a_exception_group,
656 a_exception_group_name,
657 a_trx_id1,
658 a_trx_id2 ,
659 a_customer_id,
660 a_customer_name,
661 a_customer_site_id,
662 a_customer_site_name ,
663 a_customer_item_name,
664 a_supplier_id ,
665 a_supplier_name ,
666 a_supplier_site_id ,
667 a_supplier_site_name,
668 a_supplier_item_name,
669 a_number1,
670 a_number2,
671 a_number3 ,
672 a_threshold,
673 a_lead_time,
674 a_item_min_qty,
675 a_item_max_qty,
676 a_order_number,
677 a_release_number,
678 a_line_number,
679 a_end_order_number,
680 a_end_order_rel_number ,
681 a_end_order_line_number,
682 a_creation_date,
683 a_tp_creation_date ,
684 a_date1,
685 a_date2,
686 a_date3,
687 a_date4,
688 a_date5,
689 a_exception_basis);
690
691 update msc_plan_org_status
692 set status = l_max_refresh_number,
693 status_date = sysdate
694 where plan_id = G_PLAN_ID
695 and organization_id = G_GROUP6
696 and sr_instance_id = G_SR_INSTANCE_ID;
697
698 END IF;
699 IF (p_material_excess in (to_char(1),'Y')) THEN
700
701
702 select status
703 into l_material_excess_refnum
704 from msc_plan_org_status
705 where plan_id = G_PLAN_ID
706 and organization_id = G_GROUP7
707 and sr_instance_id = G_SR_INSTANCE_ID;
708 --dbms_output.put_line('Max refresh_number : ' || l_material_excess_refnum);
709
710 --- call to bucketing
711
712 IF (p_material_shortage in (to_char(2),'N')) THEN
713 MSC_EXCHANGE_BUCKETING.start_bucketing( l_material_excess_refnum );
714
715
716 END IF;
717
718 msc_x_netting2_pkg.compute_material_excess(l_material_excess_refnum,
719 t_company_list,
720 t_company_site_list,
721 t_customer_list,
722 t_customer_site_list,
723 t_supplier_list,
724 t_supplier_site_list,
725 t_item_list,
726 t_group_list,
727 t_type_list,
728 t_trxid1_list,
729 t_trxid2_list,
730 t_date1_list,
731 t_date2_list,
732 a_company_id,
733 a_company_name,
734 a_company_site_id,
735 a_company_site_name,
736 a_item_id,
737 a_item_name,
738 a_item_desc,
739 a_exception_type,
740 a_exception_type_name ,
741 a_exception_group,
742 a_exception_group_name,
743 a_trx_id1,
744 a_trx_id2 ,
745 a_customer_id,
746 a_customer_name,
747 a_customer_site_id,
748 a_customer_site_name ,
749 a_customer_item_name,
750 a_supplier_id ,
751 a_supplier_name ,
752 a_supplier_site_id ,
753 a_supplier_site_name,
754 a_supplier_item_name,
755 a_number1,
756 a_number2,
757 a_number3 ,
758 a_threshold,
759 a_lead_time,
760 a_item_min_qty,
761 a_item_max_qty,
762 a_order_number,
763 a_release_number,
764 a_line_number,
765 a_end_order_number,
766 a_end_order_rel_number ,
767 a_end_order_line_number,
768 a_creation_date,
769 a_tp_creation_date ,
770 a_date1,
771 a_date2,
772 a_date3,
773 a_date4,
774 a_date5,
775 a_exception_basis);
776
777 update msc_plan_org_status
778 set status = l_max_refresh_number,
779 status_date = sysdate
780 where plan_id = G_PLAN_ID
781 and organization_id = G_GROUP7
782 and sr_instance_id = G_SR_INSTANCE_ID;
783
784 END IF;
785 IF (p_forecast_accuracy in (to_char(1), 'Y')) THEN
786
787 select status
788 into l_forecast_accuracy_refnum
789 from msc_plan_org_status
790 where plan_id = G_PLAN_ID
791 and organization_id = G_GROUP9
792 and sr_instance_id = G_SR_INSTANCE_ID;
793 --dbms_output.put_line('Max refresh_number : ' || l_forecast_accuracy_refnum);
794
795 msc_x_netting4_pkg.compute_forecast_accuracy(
796 a_company_id,
797 a_company_name,
798 a_company_site_id,
799 a_company_site_name,
800 a_item_id,
801 a_item_name,
802 a_item_desc,
803 a_exception_type,
804 a_exception_type_name ,
805 a_exception_group,
806 a_exception_group_name,
807 a_trx_id1,
808 a_trx_id2 ,
809 a_customer_id,
810 a_customer_name,
811 a_customer_site_id,
812 a_customer_site_name ,
813 a_customer_item_name,
814 a_supplier_id ,
815 a_supplier_name ,
816 a_supplier_site_id ,
817 a_supplier_site_name,
818 a_supplier_item_name,
819 a_number1,
820 a_number2,
821 a_number3 ,
822 a_threshold,
823 a_lead_time,
824 a_item_min_qty,
825 a_item_max_qty,
826 a_order_number,
827 a_release_number,
828 a_line_number,
829 a_end_order_number,
830 a_end_order_rel_number ,
831 a_end_order_line_number,
832 a_creation_date,
833 a_tp_creation_date ,
834 a_date1,
835 a_date2,
836 a_date3,
837 a_date4,
838 a_date5,
839 a_exception_basis);
840
841 update msc_plan_org_status
842 set status = l_max_refresh_number,
843 status_date = sysdate
844 where plan_id = G_PLAN_ID
845 and organization_id = G_GROUP9
846 and sr_instance_id = G_SR_INSTANCE_ID;
847
848 END IF;
849 IF (p_performance in (to_char(1), 'Y')) THEN
850
851 select status
852 into l_performance_refnum
853 from msc_plan_org_status
854 where plan_id = G_PLAN_ID
855 and organization_id = G_GROUP10
856 and sr_instance_id = G_SR_INSTANCE_ID;
857 --dbms_output.put_line('Max refresh_number : ' || l_performance_refnum);
858
859 msc_x_netting4_pkg.compute_performance(
860 a_company_id,
861 a_company_name,
862 a_company_site_id,
863 a_company_site_name,
864 a_item_id,
865 a_item_name,
866 a_item_desc,
867 a_exception_type,
868 a_exception_type_name ,
869 a_exception_group,
870 a_exception_group_name,
871 a_trx_id1,
872 a_trx_id2 ,
873 a_customer_id,
874 a_customer_name,
875 a_customer_site_id,
876 a_customer_site_name ,
877 a_customer_item_name,
878 a_supplier_id ,
879 a_supplier_name ,
880 a_supplier_site_id ,
881 a_supplier_site_name,
882 a_supplier_item_name,
883 a_number1,
884 a_number2,
885 a_number3 ,
886 a_threshold,
887 a_lead_time,
888 a_item_min_qty,
889 a_item_max_qty,
890 a_order_number,
891 a_release_number,
892 a_line_number,
893 a_end_order_number,
894 a_end_order_rel_number ,
895 a_end_order_line_number,
896 a_creation_date,
897 a_tp_creation_date ,
898 a_date1,
899 a_date2,
900 a_date3,
901 a_date4,
902 a_date5,
903 a_exception_basis);
904
905 update msc_plan_org_status
906 set status = l_max_refresh_number,
907 status_date = sysdate
908 where plan_id = G_PLAN_ID
909 and organization_id = G_GROUP10
910 and sr_instance_id = G_SR_INSTANCE_ID;
911
912 END IF;
913 IF (p_custom_exception in (to_char(1),'Y')) THEN
914 msc_x_netting4_pkg.compute_custom_exception;
915 END IF;
916
917
918 --================================================
919 -- insert to the msc_x_exception_details table by bulk
920 --=================================================
921
922 populate_exception_data(a_company_id,
923 a_company_name,
924 a_company_site_id,
925 a_company_site_name,
926 a_item_id,
927 a_item_name,
928 a_item_desc,
929 a_exception_type,
930 a_exception_type_name ,
931 a_exception_group,
932 a_exception_group_name,
933 a_trx_id1,
934 a_trx_id2 ,
935 a_customer_id,
936 a_customer_name,
937 a_customer_site_id,
938 a_customer_site_name ,
939 a_customer_item_name,
940 a_supplier_id ,
941 a_supplier_name ,
942 a_supplier_site_id ,
943 a_supplier_site_name,
944 a_supplier_item_name,
945 a_number1,
946 a_number2,
947 a_number3 ,
948 a_threshold,
949 a_lead_time,
950 a_item_min_qty,
951 a_item_max_qty,
952 a_order_number,
953 a_release_number,
954 a_line_number,
955 a_end_order_number,
956 a_end_order_rel_number ,
957 a_end_order_line_number,
958 a_creation_date,
959 a_tp_creation_date ,
960 a_date1,
961 a_date2,
962 a_date3,
963 a_date4,
964 a_date5,
965 a_exception_basis);
966
967 -------------------------------------------------------------
968 -- ARCHIVE THE old exceptions
969 ------------------------------------------------------------
970
971 archive_exception( t_company_list,
972 t_company_site_list,
973 t_customer_list,
974 t_customer_site_list,
975 t_supplier_list,
976 t_supplier_site_list,
977 t_item_list,
978 t_group_list,
979 t_type_list,
980 t_trxid1_list,
981 t_trxid2_list,
982 t_date1_list,
983 t_date2_list);
984
985 --------------------------------------------------------------------
986 --update item name, desc, customer item name, supplier item name
987 --------------------------------------------------------------------
988
989 IF (p_material_shortage in (to_char(1),'Y') or
990 p_material_excess in (to_char(1), 'Y')) THEN
991
992 IF (l_material_shortage_refnum is null OR l_material_shortage_refnum >= l_material_excess_refnum) THEN
993
994 l_refreshnum := l_material_excess_refnum;
995 ELSIF (l_material_excess_refnum is null OR l_material_shortage_refnum <= l_material_excess_refnum) THEN
996 l_refreshnum := l_material_shortage_refnum;
997
998 END IF;
999 --dbms_output.put_line('update item ' || l_refreshnum);
1000 update_item(l_refreshnum);
1001
1002 END IF;
1003
1004
1005 ----------------------------------------------------------------
1006 --potential late order exceptions are dependending on late order
1007 --and response required exceptions. Therefore, the potential late
1008 --order group will be running after these two groups.
1009 ----------------------------------------------------------------
1010 Potential_lo_netting(l_max_refresh_number,p_potential_late_order);
1011
1012
1013 --------------------------------------------------------------------------
1014 --Here is deleting only Planning Entities.
1015 --Delete all transactions from msc_sup_dem_entries with quantity = 0 for
1016 --the planning entities (means not the execution entites -- SO, PO, asn).
1017 --When we use the 'D' sync indicator during the data upload the records
1018 --that need to be deleted are not removed from the table. Instead the
1019 --quantity is set to zero and the transaction id is updated to ensure that
1020 --the exceptions for the items deleted are recomputed. After exceptions
1021 --are computed we purge these records from the table.
1022 ---------------------------------------------------------------------------
1023 --dbms_output.put_line('Delete zero qty for Planning Entities');
1024 delete /*+ PARALLEL(sd) */ from msc_sup_dem_entries sd
1025 where sd.plan_id = G_PLAN_ID
1026 and sd.quantity = 0
1027 and nvl(sd.last_update_login,-1) = -99
1028 and sd.last_refresh_number <= l_max_refresh_number ;
1029
1030
1031 --------------------------------------------------------------------------
1032 --Deleting the disable serial number record
1033 --------------------------------------------------------------------------
1034 DELETE from msc_serial_numbers msn
1035 WHERE NVL(msn.disable_date,sysdate+1)<=sysdate OR
1036 serial_txn_id not in (select transaction_id from msc_sup_dem_entries);
1037
1038 --=================================================================
1039 -- A profile option to set the workflow notification on/off
1040 -- By default it is set to "Y" and let it launch the workflow
1041 -- notification. If it is set to 'N', no need to send and set
1042 -- the flag back to normal in msc_x_exception_details table
1043 --====================================================================
1044
1045
1046 -----------------------------------------------------------
1047 --Clean up at the end of the netting engine run
1048 -----------------------------------------------------------
1049 clean_up_process;
1050
1051 commit;
1052
1053 IF (nvl(FND_PROFILE.VALUE('MSC_LAUNCH_EXCEPTION_NOTIFICATION'),'Y') = 'Y') THEN
1054 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Launch workflow process' || ':' ||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1055
1056 -- launch the cp exception workflow program
1057 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1058 'MSC', -- application
1059 'MSCXEWF', -- program
1060 NULL, -- description
1061 NULL, -- start time
1062 FALSE); -- sub_request
1063
1064 COMMIT;
1065
1066 IF l_request_id=0 THEN
1067 FND_FILE.PUT_LINE(FND_FILE.LOG,'Launch Exception Workflow request failed');
1068 begin
1069 update msc_x_exception_details
1070 set version = null, last_update_login = null
1071 where plan_id = -1
1072 and version = 'CURRENT'
1073 and exception_group in (1,2,3,4,5,6,7,8,9,10);
1074 exception
1075 when others then
1076 null;
1077 end;
1078 ELSE
1079 FND_FILE.PUT_LINE(FND_FILE.LOG,'Launch Exception Workflow request :'|| to_char(l_request_id));
1080 END IF;
1081
1082 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Workflow process completed at' || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1083 ELSE
1084 --Reset the records for which the no notifications need to sent
1085 begin
1086 update msc_x_exception_details
1087 set version = null, last_update_login = null
1088 where plan_id = -1
1089 and version = 'CURRENT'
1090 and exception_group in (1,2,3,4,5,6,7,8,9,10);
1091 exception
1092 when others then
1093 null;
1094 end;
1095 END IF;
1096
1097
1098 -----------------------------------------------------------
1099 --Transaction complete
1100 -----------------------------------------------------------
1101 commit;
1102
1103 END START_NETTING;
1104
1105 --------------------------------------------------------------------
1106 --PROCEDURE POTENTIAL_LO_NETTING
1107 --------------------------------------------------------------------
1108 PROCEDURE POTENTIAL_LO_NETTING (p_max_refresh_number in Number,
1109 p_potential_late_order in VARCHAR2) AS
1110
1111 l_potential_late_order_refnum Number;
1112
1113
1114 --========================================================
1115 -- create the constructor the plsql table
1116 --========================================================
1117 t_item_list number_arr := number_arr();
1118 t_company_list number_arr := number_arr();
1119 t_company_site_list number_arr := number_arr();
1120 t_customer_list number_arr := number_arr();
1121 t_customer_site_list number_arr := number_arr();
1122 t_supplier_list number_arr := number_arr();
1123 t_supplier_site_list number_arr := number_arr();
1124 t_group_list number_arr := number_arr();
1125 t_type_list number_arr := number_arr();
1126 t_trxid1_list number_arr := number_arr();
1127 t_trxid2_list number_arr := number_arr();
1128 t_date1_list date_arr := date_arr();
1129 t_date2_list date_arr := date_arr();
1130 a_company_id number_arr := number_arr();
1131 a_company_name publisherList := publisherList();
1132 a_company_site_id number_arr := number_arr();
1133 a_company_site_name pubsiteList := pubsiteList();
1134 a_item_id number_arr := number_arr();
1135 a_item_name itemnameList := itemnameList();
1136 a_item_desc itemdescList := itemdescList();
1137 a_exception_type number_arr := number_arr();
1138 a_exception_type_name exceptypeList := exceptypeList();
1139 a_exception_group number_arr := number_arr();
1140 a_exception_group_name excepgroupList := excepgroupList();
1141 a_trx_id1 number_arr := number_arr();
1142 a_trx_id2 number_arr := number_arr();
1143 a_customer_id number_arr := number_arr();
1144 a_customer_name customerList := customerList();
1145 a_customer_site_id number_arr := number_arr();
1146 a_customer_site_name custsiteList := custsiteList();
1147 a_customer_item_name itemnameList := itemnameList();
1148 a_supplier_id number_arr := number_arr();
1149 a_supplier_name supplierList := supplierList();
1150 a_supplier_site_id number_arr := number_arr();
1151 a_supplier_site_name suppsiteList := suppsiteList();
1152 a_supplier_item_name itemnameList := itemnameList();
1153 a_number1 number_arr := number_arr();
1154 a_number2 number_arr := number_arr();
1155 a_number3 number_arr := number_arr();
1156 a_threshold number_arr := number_arr();
1157 a_lead_time number_arr := number_arr();
1158 a_item_min_qty number_arr := number_arr();
1159 a_item_max_qty number_arr := number_arr();
1160 a_order_number ordernumberList := ordernumberList();
1161 a_release_number releasenumList := releasenumList();
1162 a_line_number linenumList := linenumList();
1163 a_end_order_number ordernumberList := ordernumberList();
1164 a_end_order_rel_number releasenumList := releasenumList();
1165 a_end_order_line_number linenumList := linenumList();
1166 a_creation_date date_arr := date_arr();
1167 a_tp_creation_date date_arr := date_arr();
1168 a_date1 date_arr := date_arr();
1169 a_date2 date_arr := date_arr();
1170 a_date3 date_arr := date_arr();
1171 a_date4 date_arr := date_arr();
1172 a_date5 date_arr := date_arr();
1173 a_exception_basis exceptbasisList := exceptbasisList();
1174
1175 BEGIN
1176 IF (p_potential_late_order in (to_char(1), 'Y')) THEN
1177
1178 select status
1179 into l_potential_late_order_refnum
1180 from msc_plan_org_status
1181 where plan_id = G_PLAN_ID
1182 and organization_id = G_GROUP4
1183 and sr_instance_id = G_SR_INSTANCE_ID;
1184 --dbms_output.put_line('Max refresh_number : ' || l_potential_late_order_refnum);
1185
1186 msc_x_netting3_pkg.compute_potential_late_order(l_potential_late_order_refnum,
1187 t_company_list,
1188 t_company_site_list,
1189 t_customer_list,
1190 t_customer_site_list,
1191 t_supplier_list,
1192 t_supplier_site_list,
1193 t_item_list,
1194 t_group_list,
1195 t_type_list,
1196 t_trxid1_list,
1197 t_trxid2_list,
1198 t_date1_list,
1199 t_date2_list, a_company_id,
1200 a_company_name,
1201 a_company_site_id,
1202 a_company_site_name,
1203 a_item_id,
1204 a_item_name,
1205 a_item_desc,
1206 a_exception_type,
1207 a_exception_type_name ,
1208 a_exception_group,
1209 a_exception_group_name,
1210 a_trx_id1,
1211 a_trx_id2 ,
1212 a_customer_id,
1213 a_customer_name,
1214 a_customer_site_id,
1215 a_customer_site_name ,
1216 a_customer_item_name,
1217 a_supplier_id ,
1218 a_supplier_name ,
1219 a_supplier_site_id ,
1220 a_supplier_site_name,
1221 a_supplier_item_name,
1222 a_number1,
1223 a_number2,
1224 a_number3 ,
1225 a_threshold,
1226 a_lead_time,
1227 a_item_min_qty,
1228 a_item_max_qty,
1229 a_order_number,
1230 a_release_number,
1231 a_line_number,
1232 a_end_order_number,
1233 a_end_order_rel_number ,
1234 a_end_order_line_number,
1235 a_creation_date,
1236 a_tp_creation_date ,
1237 a_date1,
1238 a_date2,
1239 a_date3,
1240 a_date4,
1241 a_date5,
1242 a_exception_basis);
1243
1244 update msc_plan_org_status
1245 set status = p_max_refresh_number,
1246 status_date = sysdate
1247 where plan_id = G_PLAN_ID
1248 and organization_id = G_GROUP4
1249 and sr_instance_id = G_SR_INSTANCE_ID;
1250 END IF;
1251
1252 --================================================
1253 -- insert to the msc_x_exception_details table by bulk
1254 --=================================================
1255
1256 populate_exception_data(a_company_id,
1257 a_company_name,
1258 a_company_site_id,
1259 a_company_site_name,
1260 a_item_id,
1261 a_item_name,
1262 a_item_desc,
1263 a_exception_type,
1264 a_exception_type_name ,
1265 a_exception_group,
1266 a_exception_group_name,
1267 a_trx_id1,
1268 a_trx_id2 ,
1269 a_customer_id,
1270 a_customer_name,
1271 a_customer_site_id,
1272 a_customer_site_name ,
1273 a_customer_item_name,
1274 a_supplier_id ,
1275 a_supplier_name ,
1276 a_supplier_site_id ,
1277 a_supplier_site_name,
1278 a_supplier_item_name,
1279 a_number1,
1280 a_number2,
1281 a_number3 ,
1282 a_threshold,
1283 a_lead_time,
1284 a_item_min_qty,
1285 a_item_max_qty,
1286 a_order_number,
1287 a_release_number,
1288 a_line_number,
1289 a_end_order_number,
1290 a_end_order_rel_number ,
1291 a_end_order_line_number,
1292 a_creation_date,
1293 a_tp_creation_date ,
1294 a_date1,
1295 a_date2,
1296 a_date3,
1297 a_date4,
1298 a_date5,
1299 a_exception_basis);
1300
1301 -------------------------------------------------------------
1302 -- ARCHIVE THE old exceptions
1303 ------------------------------------------------------------
1304
1305 archive_exception( t_company_list,
1306 t_company_site_list,
1307 t_customer_list,
1308 t_customer_site_list,
1309 t_supplier_list,
1310 t_supplier_site_list,
1311 t_item_list,
1312 t_group_list,
1313 t_type_list,
1314 t_trxid1_list,
1315 t_trxid2_list,
1316 t_date1_list,
1317 t_date2_list);
1318
1319
1320 END potential_lo_netting;
1321
1322 --------------------------------------------------------------------
1323 --FUNCTION does_exception_org_exist
1324 --------------------------------------------------------------------
1325 FUNCTION DOES_EXCEPTION_ORG_EXIST (p_org_id IN Number) RETURN Number IS
1326
1327 l_ret_flag Number := 0;
1328
1329 BEGIN
1330
1331
1332 select 1 into l_ret_flag
1333 from dual
1334 where exists ( select 1
1335 from msc_plan_org_status
1336 where plan_id = G_PLAN_ID
1337 and sr_instance_id = G_SR_INSTANCE_ID
1338 and organization_id = p_org_id
1339 );
1340
1341 return l_ret_flag;
1342
1343 EXCEPTION
1344
1345 when NO_DATA_FOUND then
1346 l_ret_flag := 0;
1347 return l_ret_flag;
1348 END does_exception_org_exist;
1349
1350 -----------------------------------------------------------------------
1351 -- PROCEDURE POPULATE_EXCEPTION_ORG
1352 --insert the organization_id that is not existing for the plan_id = -1
1353 -----------------------------------------------------------------------
1354 PROCEDURE POPULATE_EXCEPTION_ORG IS
1355
1356 l_count Number := 0;
1357
1358 BEGIN
1359
1360 select count(*)
1361 into l_count
1362 from msc_plan_org_status
1363 where plan_id = -1
1364 and organization_id in (G_GROUP1,G_GROUP2,G_GROUP3,
1365 G_GROUP4,G_GROUP5,G_GROUP6,
1366 G_GROUP7,G_GROUP8,G_GROUP9,G_GROUP10);
1367
1368 IF (l_count <> 10) THEN
1369
1370 IF ( does_exception_org_exist (G_GROUP1) <> 1) THEN
1371 --dbms_output.put_line('Inserting... ' || G_GROUP1);
1372 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1373 values (G_PLAN_ID, G_GROUP1, G_SR_INSTANCE_ID, 0, sysdate);
1374 END IF;
1375 IF ( does_exception_org_exist (G_GROUP2) <> 1) THEN
1376 --dbms_output.put_line('Inserting... ' || G_GROUP2);
1377 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1378 values (G_PLAN_ID, G_GROUP2, G_SR_INSTANCE_ID, 0, sysdate);
1379 END IF;
1380 IF ( does_exception_org_exist (G_GROUP3) <> 1) THEN
1381 --dbms_output.put_line('Inserting... ' || G_GROUP3);
1382 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1383 values (G_PLAN_ID, G_GROUP3, G_SR_INSTANCE_ID, 0, sysdate);
1384 END IF;
1385 IF ( does_exception_org_exist (G_GROUP4) <> 1) THEN
1386 --dbms_output.put_line('Inserting... ' || G_GROUP4);
1387 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1388 values (G_PLAN_ID, G_GROUP4, G_SR_INSTANCE_ID, 0, sysdate);
1389 END IF;
1390 IF ( does_exception_org_exist (G_GROUP5) <> 1) THEN
1391 --dbms_output.put_line('Inserting... ' || G_GROUP5);
1392 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1393 values (G_PLAN_ID, G_GROUP5, G_SR_INSTANCE_ID, 0, sysdate);
1394 END IF;
1395 IF ( does_exception_org_exist (G_GROUP6) <> 1) THEN
1396 --dbms_output.put_line('Inserting... ' || G_GROUP6);
1397 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1398 values (G_PLAN_ID, G_GROUP6, G_SR_INSTANCE_ID, 0, sysdate);
1399 END IF;
1400 IF ( does_exception_org_exist (G_GROUP7) <> 1) THEN
1401 --dbms_output.put_line('Inserting... ' || G_GROUP7);
1402 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1403 values (G_PLAN_ID, G_GROUP7, G_SR_INSTANCE_ID, 0, sysdate);
1404 END IF;
1405 IF ( does_exception_org_exist (G_GROUP8) <> 1) THEN
1406 --dbms_output.put_line('Inserting... ' || G_GROUP8);
1407 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1408 values (G_PLAN_ID, G_GROUP8, G_SR_INSTANCE_ID, 0, sysdate);
1409 END IF;
1410 IF ( does_exception_org_exist (G_GROUP9) <> 1) THEN
1411 --dbms_output.put_line('Inserting... ' || G_GROUP9);
1412 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1413 values (G_PLAN_ID, G_GROUP9, G_SR_INSTANCE_ID, 0, sysdate);
1414 END IF;
1415 IF ( does_exception_org_exist (G_GROUP10) <> 1) THEN
1416 --dbms_output.put_line('Inserting... ' || G_GROUP10);
1417 insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
1418 values (G_PLAN_ID, G_GROUP10, G_SR_INSTANCE_ID, 0, sysdate);
1419 END IF;
1420
1421 END IF;
1422
1423 EXCEPTION
1424 when others then
1425 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.populate_exception_org');
1426 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1427 return;
1428 END POPULATE_EXCEPTION_ORG;
1429
1430
1431 -----------------------------------------------------------------------------------
1432 --FUNCTION GENERATE_COMPLEMENT_EXCEPTION
1433 -----------------------------------------------------------------------------------
1434 FUNCTION GENERATE_COMPLEMENT_EXCEPTION(p_company_id IN Number,
1435 p_company_site_id IN Number,
1436 p_item_id IN Number,
1437 p_refresh_number IN Number,
1438 p_type in NUMBER,
1439 p_role in NUMBER) RETURN Boolean IS
1440 l_return_flag Boolean;
1441 l_max_rf_num Number;
1442 plans_rf_num Number;
1443 tps_role Number;
1444 BEGIN
1445
1446 BEGIN
1447 if p_type in (SUPPLY_PLANNING,DEMAND_PLANNING) then
1448 if (p_role = SELLER) then
1449 select nvl(max(sd.last_refresh_number),-1)
1450 into l_max_rf_num
1451 from msc_sup_dem_entries sd
1452 where sd.plan_id = G_PLAN_ID
1453 and sd.publisher_id = p_company_id
1454 and sd.publisher_site_id = p_company_site_id
1455 and sd.inventory_item_id = p_item_id
1456 and trunc(sd.key_date) >= trunc(sysdate);
1457 elsif (p_role = BUYER) then
1458 select nvl(max(sd.last_refresh_number),-1)
1459 into l_max_rf_num
1460 from msc_sup_dem_entries sd
1461 where sd.plan_id = G_PLAN_ID
1462 and sd.publisher_id = p_company_id
1463 and sd.publisher_site_id = p_company_site_id
1464 and sd.inventory_item_id = p_item_id
1465 and trunc(sd.key_date) >= trunc(sysdate);
1466 end if;
1467
1468 elsif p_type in (VMI) then
1469 if (p_role = SELLER) then
1470 select nvl(max(sd.last_refresh_number),-1)
1471 into l_max_rf_num
1472 from msc_sup_dem_entries sd
1473 where sd.plan_id = G_PLAN_ID
1474 and sd.publisher_id = p_company_id
1475 and sd.publisher_site_id = p_company_site_id
1476 and sd.inventory_item_id = p_item_id
1477 and ((trunc(sd.ship_date) >= trunc(sysdate)) or sd.publisher_order_type = 15);
1478 /* Bug# 4303597 -- added OR condition so that refresh_number of updated ASN is selected */
1479 elsif (p_role = BUYER) then
1480 select nvl(max(sd.last_refresh_number),-1)
1481 into l_max_rf_num
1482 from msc_sup_dem_entries sd
1483 where sd.plan_id = G_PLAN_ID
1484 and sd.publisher_id = p_company_id
1485 and sd.publisher_site_id = p_company_site_id
1486 and sd.inventory_item_id = p_item_id;
1487 end if;
1488 end if;
1489
1490 EXCEPTION
1491 WHEN NO_DATA_FOUND THEN
1492 --dbms_output.put_line('No data found for item ' || p_item_id);
1493 l_return_flag := FALSE;
1494 END;
1495
1496
1497 --dbms_output.put_line('Plans refresh # := ' || p_refresh_number);
1498 --dbms_output.put_line('Entry refresh # := ' || l_max_rf_num);
1499
1500 if p_refresh_number >= l_max_rf_num then
1501 l_return_flag := TRUE;
1502 else
1503 l_return_flag := FALSE;
1504 end if;
1505 return l_return_flag;
1506 END GENERATE_COMPLEMENT_EXCEPTION;
1507
1508 ------------------------------------------------------------------
1509 -- DELETE_ITEM
1510 ------------------------------------------------------------------
1511 PROCEDURE Delete_Item(l_type in varchar2, l_key in varchar2) IS
1512
1513 cursor get_notification_c (l_type in varchar2, l_key in varchar2) IS
1514 select notification_id
1515 from wf_item_activity_statuses
1516 where item_type = l_type
1517 and item_key like l_key
1518 union
1519 select notification_id
1520 from wf_item_activity_statuses_h
1521 where item_type = l_type
1522 and item_key like l_key;
1523
1524 l_item_key Varchar2(100);
1525 l_notification_id Number;
1526
1527 BEGIN
1528
1529 open get_notification_c (l_type, l_key);
1530 loop
1531 fetch get_notification_c into l_notification_id;
1532 exit when (l_notification_id is null or get_notification_c%NOTFOUND or get_notification_c%NOTFOUND is null) ;
1533
1534 update wf_notifications set
1535 end_date = sysdate
1536 where notification_id = l_notification_id;
1537 end loop;
1538 close get_notification_c;
1539
1540
1541
1542 update wf_items set
1543 end_date = sysdate
1544 where item_type = l_type
1545 and item_key like l_key;
1546
1547 update wf_item_activity_statuses set
1548 end_date = sysdate
1549 where item_type = l_type
1550 and item_key like l_key;
1551
1552 update wf_item_activity_statuses_h set
1553 end_date = sysdate
1554 where item_type = l_type
1555 and item_key like l_key;
1556
1557 wf_purge.total(l_type,l_key,sysdate,false);
1558
1559 EXCEPTION
1560 WHEN OTHERS THEN
1561 return;
1562 END Delete_Item;
1563
1564 ------------------------------------------------------------------
1565 -- delete_wf_notification
1566 ------------------------------------------------------------------
1567 PROCEDURE delete_wf_notification(p_type in varchar2, p_key in varchar2) IS
1568
1569 cursor get_notification_c (p_type In Varchar2,
1570 p_key in varchar2) IS
1571
1572 select (max(notification_id))
1573 from wf_item_activity_statuses
1574 where item_type = p_type
1575 and item_key like p_key || '%'
1576 and notification_id is not null;
1577
1578 l_item_key Varchar2(100);
1579 l_notification_id Number;
1580 BEGIN
1581
1582 open get_notification_c (p_type, p_key);
1583 loop
1584 fetch get_notification_c into l_notification_id;
1585 exit when (l_notification_id is null or get_notification_c%NOTFOUND or get_notification_c%NOTFOUND is null) ;
1586 FND_FILE.PUT_LINE(FND_FILE.LOG,'inside PROCEDURE delete_wf_notification : l_notification_id = '||l_notification_id);
1587 wf_notification.cancel(l_notification_id);
1588
1589 end loop;
1590 close get_notification_c;
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 --dbms_output.put_line('Error in delete wf nid ' || sqlerrm);
1595 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in delete wf nid '||sqlerrm);
1596 return;
1597 END delete_wf_notification;
1598
1599 --------------------------------------------------------------------------
1600 -- Function GET_MESSAGE_TYPE
1601 ----------------------------------------------------------------------
1602 FUNCTION GET_MESSAGE_TYPE(p_exception_code in Number) RETURN Varchar2 IS
1603 l_message_type Varchar2(100);
1604 BEGIN
1605
1606 /*
1607 if (p_exception_code in (17,18,37,38,41,42) ) then
1608 return l_message_type;
1609 end if;
1610
1611 if (p_exception_code <= 16) then
1612 return lv_exception_type(p_exception_code);
1613 elsif (p_exception_code <= 36) then
1614 return lv_exception_type(p_exception_code-2);
1615 elsif (p_exception_code <= 40) then
1616 return lv_exception_type(p_exception_code-4);
1617 elsif (p_exception_code <= 51) then
1618 return lv_exception_type(p_exception_code-6);
1619 else
1620 return l_message_type;
1621 end if;
1622 */
1623 select meaning
1624 into l_message_type
1625 from mfg_lookups
1626 where lookup_type = 'MSC_X_EXCEPTION_TYPE'
1627 and lookup_code = p_exception_code;
1628
1629 return l_message_type;
1630 EXCEPTION
1631 when others then
1632 l_message_type := null;
1633 return l_message_type;
1634 END get_message_type;
1635
1636 --------------------------------------------------------------------------
1637 -- Function GET_MESSAGE_GROUP
1638 ----------------------------------------------------------------------
1639 FUNCTION GET_MESSAGE_GROUP(p_exception_group in Number) RETURN Varchar2 IS
1640 l_message_group Varchar2(100);
1641 BEGIN
1642
1643 /* This code is added for performance.
1644 But this is not currently used since QA has not tested it .....
1645 if (p_exception_group = -99) then
1646 return lv_exception_grp(1);
1647 elsif (p_exception_group <= 10) then
1648 return lv_exception_grp(p_exception_group+1);
1649 else
1650 return l_message_group;
1651 end if;
1652 */
1653
1654 select meaning
1655 into l_message_group
1656 from mfg_lookups
1657 where lookup_type = 'MSC_X_EXCEPTION_GROUP'
1658 and lookup_code = p_exception_group;
1659
1660 return l_message_group;
1661 EXCEPTION
1662 when others then
1663 l_message_group := null;
1664 return l_message_group;
1665
1666 END get_message_group;
1667
1668
1669 -----------------------------------------------------------------
1670 -- procedure UPDATE_EXCEPTION_SUMMARY
1671 -----------------------------------------------------------------
1672 PROCEDURE UPDATE_EXCEPTIONS_SUMMARY( p_company_id IN Number,
1673 p_company_site_id IN Number,
1674 p_item_id IN Number,
1675 p_exception_type IN Number,
1676 p_exception_group IN Number) IS
1677
1678 l_exception_exists Number;
1679
1680 BEGIN
1681
1682 l_exception_exists := does_exception_exist(p_company_id,
1683 p_company_site_id,
1684 p_item_id,
1685 p_exception_type,
1686 p_exception_group);
1687 --dbms_output.put_line('Exception item exist ' || l_exception_exists);
1688 if l_exception_exists = 1 then
1689 update_item_exception(p_company_id,
1690 p_company_site_id,
1691 p_item_id,
1692 p_exception_type,
1693 p_exception_group);
1694 else
1695 add_item_exception(p_company_id,
1696 p_company_site_id,
1697 p_item_id,
1698 p_exception_type,
1699 p_exception_group);
1700 end if;
1701
1702 EXCEPTION
1703 when others then
1704 ----dbms_output.put_line('Error ' || sqlerrm);
1705 return;
1706 END UPDATE_EXCEPTIONS_SUMMARY;
1707
1708 --------------------------------------------------------------------------
1709 --procedure ADD_EXCEPTION_DETAILS
1710 --------------------------------------------------------------------------
1711 PROCEDURE ADD_EXCEPTION_DETAILS (p_company_id IN Number,
1712 p_company_name IN Varchar2,
1713 p_company_site_id IN Number,
1714 p_company_site_name In Varchar2,
1715 p_item_id In Number,
1716 p_item_name In Varchar2,
1717 p_item_description In Varchar2,
1718 p_exception_type IN Number,
1719 p_exception_type_name In Varchar2,
1720 p_exception_group In Number,
1721 p_exception_group_name IN Varchar2,
1722 p_trx_id1 IN Number,
1723 p_trx_id2 IN Number,
1724 p_customer_id IN Number,
1725 p_customer_name IN Varchar2,
1726 p_customer_site_id IN Number,
1727 p_customer_site_name in varchar2,
1728 p_customer_item_name iN varchar2,
1729 p_supplier_id IN Number,
1730 p_supplier_name In Varchar2,
1731 p_supplier_site_id IN Number ,
1732 p_supplier_site_name In Varchar2,
1733 p_supplier_item_name In Varchar2,
1734 p_quantity3 IN Number ,
1735 p_quantity1 In Number,
1736 p_quantity2 In Number,
1737 p_threshold In Number,
1738 p_lead_time In Number,
1739 p_item_min_qty In Number,
1740 p_item_max_qty In Number,
1741 p_order_number IN Varchar2 ,
1742 p_release_number IN Varchar2,
1743 p_line_number IN Varchar2,
1744 p_end_order_number IN Varchar2,
1745 p_end_order_rel_number In Varchar2,
1746 p_end_order_line_number IN Varchar2,
1747 p_actual_date IN Date,
1748 p_tp_actual_date IN Date,
1749 p_creation_date IN Date,
1750 p_tp_creation_date IN Date,
1751 p_other_date IN Date
1752 , p_replenishment_method IN NUMBER
1753 ) IS
1754 l_ex_dtl_id Number;
1755 l_user_id Number := fnd_global.user_id;
1756 BEGIN
1757 select msc_x_exception_details_s.nextval
1758 into l_ex_dtl_id
1759 from dual;
1760
1761 insert into msc_x_exception_details ( exception_detail_id,
1762 exception_type,
1763 exception_type_name,
1764 exception_group,
1765 exception_group_name,
1766 number3,
1767 date1,
1768 date2,
1769 date3,
1770 order_creation_date1,
1771 order_creation_date2,
1772 transaction_id1,
1773 transaction_id2,
1774 number1,
1775 number2,
1776 threshold,
1777 lead_time,
1778 item_min_qty,
1779 item_max_qty,
1780 version,
1781 plan_id,
1782 sr_instance_id,
1783 company_id,
1784 company_name,
1785 company_site_id,
1786 company_site_name,
1787 inventory_item_id,
1788 item_name,
1789 item_description,
1790 last_update_date,
1791 last_updated_by,
1792 last_update_login,
1793 creation_date,
1794 created_by,
1795 customer_id,
1796 customer_name,
1797 customer_site_id,
1798 customer_site_name,
1799 customer_item_name,
1800 supplier_id,
1801 supplier_name,
1802 supplier_site_id,
1803 supplier_site_name,
1804 supplier_item_name,
1805 order_number,
1806 release_number,
1807 line_number,
1808 end_order_number,
1809 end_order_rel_number,
1810 end_order_line_number
1811 , replenishment_method
1812 )
1813
1814 values (l_ex_dtl_id,
1815 p_exception_type,
1816 p_exception_type_name,
1817 p_exception_group,
1818 p_exception_group_name,
1819 p_quantity3,
1820 trunc(p_actual_date),
1821 trunc(p_tp_actual_date),
1822 trunc(p_other_date),
1823 trunc(p_creation_date),
1824 trunc(p_tp_creation_date),
1825 p_trx_id1,
1826 p_trx_id2,
1827 p_quantity1,
1828 p_quantity2,
1829 p_threshold,
1830 p_lead_time,
1831 p_item_min_qty,
1832 p_item_max_qty,
1833 'CURRENT',
1834 G_PLAN_ID,
1835 G_SR_INSTANCE_ID,
1836 p_company_id,
1837 p_company_name,
1838 p_company_site_id,
1839 p_company_site_name,
1840 p_item_id,
1841 p_item_name,
1842 p_item_description,
1843 sysdate,
1844 l_user_id,
1845 G_MAGIC_NUMBER,
1846 sysdate,
1847 l_user_id,
1848 p_customer_id,
1849 p_customer_name,
1850 p_customer_site_id,
1851 p_customer_site_name,
1852 p_customer_item_name,
1853 p_supplier_id,
1854 p_supplier_name,
1855 p_supplier_site_id,
1856 p_supplier_site_name,
1857 p_supplier_item_name,
1858 p_order_number,
1859 p_release_number,
1860 p_line_number,
1861 p_end_order_number,
1862 p_end_order_rel_number,
1863 p_end_order_line_number
1864 , p_replenishment_method
1865 );
1866
1867 EXCEPTION
1868 when others then
1869 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.add_exception_details');
1870 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1871 return;
1872 END ADD_EXCEPTION_DETAILS;
1873
1874 --------------------------------------------------------------------------
1875 --procedure UPDATE_ITEM_EXCEPTION
1876 --------------------------------------------------------------------------
1877 PROCEDURE UPDATE_ITEM_EXCEPTION( p_company_id IN Number,
1878 p_company_site_id IN Number,
1879 p_item_id IN Number,
1880 p_exception_type IN Number,
1881 p_exception_group IN Number) IS
1882 BEGIN
1883 --dbms_output.put_line('Update msc_item_exceptions');
1884 update msc_item_exceptions ex
1885 set ex.exception_count = ex.exception_count + 1,
1886 ex.last_update_date = sysdate
1887 where ex.plan_id = G_PLAN_ID
1888 and ex.company_id = p_company_id
1889 and ex.company_site_id = p_company_site_id
1890 and ex.inventory_item_id = p_item_id
1891 and ex.exception_type = p_exception_type
1892 and ex.exception_group = p_exception_group
1893 and ex.version = 0;
1894
1895 EXCEPTION
1896 when others then
1897 --dbms_output.put_line('Error ' || sqlerrm);
1898 return;
1899 END UPDATE_ITEM_EXCEPTION;
1900
1901 -------------------------------------------------------------------------------
1902 -- ADD_ITEM_EXCEPTION
1903 --------------------------------------------------------------------------
1904 PROCEDURE ADD_ITEM_EXCEPTION( p_company_id IN Number,
1905 p_company_site_id IN Number,
1906 p_item_id IN Number,
1907 p_exception_type IN Number,
1908 p_exception_group IN Number) IS
1909 l_version Number := 0;
1910 BEGIN
1911 --dbms_output.put_line('Add item exception ');
1912 insert into msc_item_exceptions( plan_id,
1913 sr_instance_id,
1914 company_id,
1915 company_site_id,
1916 organization_id,
1917 inventory_item_id,
1918 version,
1919 exception_type,
1920 exception_group,
1921 exception_count,
1922 last_update_date,
1923 last_updated_by,
1924 creation_date,
1925 created_by,
1926 last_update_login)
1927 values (G_PLAN_ID,
1928 G_SR_INSTANCE_ID,
1929 p_company_id,
1930 p_company_site_id,
1931 p_company_site_id,
1932 p_item_id,
1933 l_version,
1934 p_exception_type,
1935 p_exception_group,
1936 1,
1937 sysdate,
1938 -1,
1939 sysdate,
1940 -1,
1941 G_MAGIC_NUMBER);
1942 EXCEPTION
1943 when others then
1944 --dbms_output.put_line('Error ' || sqlerrm);
1945 return;
1946
1947 END ADD_ITEM_EXCEPTION;
1948
1949 ------------------------------------------------------------------------
1950 -- DOES_EXCEPTION_EXIST
1951 -------------------------------------------------------------------------
1952 FUNCTION DOES_EXCEPTION_EXIST(p_company_id IN Number,
1953 p_company_site_id IN Number,
1954 p_item_id IN Number,
1955 p_exception_type IN Number,
1956 p_exception_group IN Number) RETURN Number IS
1957 l_ret_flag Number := -1;
1958 l_count Number;
1959 BEGIN
1960
1961
1962 select 1 into l_ret_flag
1963 from dual
1964 where exists ( select 1
1965 from msc_item_exceptions ex
1966 where ex.plan_id = G_PLAN_ID
1967 and ex.company_id = p_company_id
1968 and ex.company_site_id = p_company_site_id
1969 and ex.inventory_item_id = p_item_id
1970 and ex.exception_type = p_exception_type
1971 and ex.exception_group = p_exception_group
1972 and ex.version = 0
1973 --and nvl(ex.last_update_login,-1) = G_MAGIC_NUMBER
1974 );
1975
1976 return l_ret_flag;
1977 EXCEPTION
1978 when NO_DATA_FOUND then
1979 l_ret_flag := -1;
1980 return l_ret_flag;
1981 END DOES_EXCEPTION_EXIST;
1982
1983 ----------------------------------------------------------------
1984 -- Function get_total_qty
1985 -----------------------------------------------------------------
1986 FUNCTION GET_TOTAL_QTY( p_order_number IN VARCHAR2,
1987 p_release_number IN VARCHAR2,
1988 p_line_number IN VARCHAR2,
1989 p_company_id IN Number,
1990 p_company_site_id IN NUMBER,
1991 p_tp_id IN NUMBER,
1992 p_tp_site_id IN Number,
1993 p_item_id IN NUMBER)
1994 RETURN NUMBER IS
1995 l_total_qty number:= 0;
1996
1997 BEGIN
1998
1999 --for all cases, the tp view org is the po org.
2000 --to compare the po quantity and so quantity, need to
2001 --get the tp_quantity of the so.
2002 SELECT sum(sd.tp_quantity)
2003 INTO l_total_qty
2004 FROM msc_sup_dem_entries sd
2005 WHERE sd.plan_id = G_PLAN_ID
2006 AND sd.publisher_id = p_company_id
2007 AND sd.publisher_site_id = p_company_site_id
2008 AND sd.inventory_item_id = p_item_id
2009 AND sd.customer_id = p_tp_id
2010 AND sd.customer_site_id = p_tp_site_id
2011 AND sd.publisher_order_type = SALES_ORDER
2012 AND sd.end_order_number = p_order_number
2013 AND nvl(sd.end_order_rel_number, -1) = nvl(p_release_number, -1)
2014 AND nvl(sd.end_order_line_number, -1) = nvl(p_line_number, -1);
2015
2016 If (l_total_qty is null) then
2017 l_total_qty := 0;
2018 end if;
2019 return l_total_qty;
2020
2021 EXCEPTION
2022 when NO_DATA_FOUND then
2023 l_total_qty := 0;
2024 return l_total_qty;
2025
2026 END GET_TOTAL_QTY;
2027
2028 -----------------------------------------------------------------
2029 -- Function does_so_exist
2030 -----------------------------------------------------------------
2031 FUNCTION DOES_SO_EXIST( p_order_number IN VARCHAR2,
2032 p_release_number IN VARCHAR2,
2033 p_line_number IN VARCHAR2,
2034 p_company_id IN Number,
2035 p_company_site_id IN NUMBER,
2036 p_tp_id IN Number,
2037 p_tp_site_id IN NUMBER,
2038 p_item_id IN NUMBER)
2039 RETURN NUMBER IS
2040 l_return_code Number := 0;
2041
2042 BEGIN
2043 SELECT 1
2044 INTO l_return_code
2045 FROM dual
2046 WHERE EXISTS (SELECT sd.order_number
2047 FROM msc_sup_dem_entries sd
2048 WHERE sd.plan_id = G_PLAN_ID
2049 AND sd.publisher_id = p_company_id
2050 AND sd.publisher_site_id = p_company_site_id
2051 AND sd.publisher_order_type = SALES_ORDER
2052 AND sd.end_order_number = p_order_number
2053 AND nvl(sd.end_order_rel_number, -1) =
2054 nvl(p_release_number, -1)
2055 AND nvl(sd.end_order_line_number, -1) =
2056 nvl(p_line_number, -1)
2057 AND sd.customer_id = p_tp_id
2058 AND sd.customer_site_id = p_tp_site_id
2059 AND sd.inventory_item_id = p_item_id);
2060
2061 return l_return_code;
2062 EXCEPTION
2063 when NO_DATA_FOUND then
2064 l_return_code := 0;
2065 return l_return_code;
2066 END DOES_SO_EXIST;
2067 -----------------------------------------------------------------
2068 -- Function does_po_exist
2069 -----------------------------------------------------------------
2070 FUNCTION DOES_PO_EXIST( p_end_order_number IN VARCHAR2,
2071 p_end_order_rel_number IN VARCHAR2,
2072 p_end_order_line_number IN VARCHAR2,
2073 p_company_id IN NUMBER,
2074 p_company_site_id IN NUMBER,
2075 p_tp_id IN NUMBER,
2076 p_tp_site_id IN NUMBER,
2077 p_item_id IN NUMBER)
2078 RETURN NUMBER IS
2079 l_return_code Number := 0;
2080
2081 BEGIN
2082 SELECT 1
2083 INTO l_return_code
2084 FROM dual
2085 WHERE EXISTS (SELECT sd.order_number
2086 FROM msc_sup_dem_entries sd
2087 WHERE sd.plan_id = G_PLAN_ID
2088 AND sd.publisher_id = p_company_id
2089 AND sd.publisher_site_id = p_company_site_id
2090 AND sd.publisher_order_type = PURCHASE_ORDER
2091 AND sd.order_number = p_end_order_number
2092 AND nvl(sd.release_number, -1) =
2093 nvl(p_end_order_rel_number, -1)
2094 AND nvl(sd.line_number, -1) =
2095 nvl(p_end_order_line_number, -1)
2096 AND sd.supplier_id = p_tp_id
2097 AND sd.supplier_site_id = p_tp_site_id
2098 AND sd.inventory_item_id = p_item_id);
2099
2100 return l_return_code;
2101 EXCEPTION
2102 when NO_DATA_FOUND then
2103 return l_return_code;
2104 END DOES_PO_EXIST;
2105
2106 -----------------------------------------------------------------
2107 -- Function does_shiprcpt_exist
2108 -----------------------------------------------------------------
2109 FUNCTION DOES_SHIPRCPT_EXIST( p_order_number IN VARCHAR2,
2110 p_release_number IN VARCHAR2,
2111 p_line_number IN VARCHAR2,
2112 p_company_id IN NUMBER,
2113 p_company_site_id IN NUMBER,
2114 p_tp_id IN NUMBER,
2115 p_tp_site_id IN NUMBER,
2116 p_item_id IN NUMBER)
2117 RETURN NUMBER IS
2118 l_return_code Number := 0;
2119
2120 BEGIN
2121 SELECT 1
2122 INTO l_return_code
2123 FROM dual
2124 WHERE EXISTS (SELECT sd.order_number
2125 FROM msc_sup_dem_entries sd
2126 WHERE sd.plan_id = G_PLAN_ID
2127 AND sd.publisher_id = p_company_id
2128 AND sd.publisher_site_id = p_company_site_id
2129 AND sd.publisher_order_type = SHIPMENT_RECEIPT
2130 AND sd.end_order_number = p_order_number
2131 AND nvl(sd.end_order_rel_number, -1) =
2132 nvl(p_release_number, -1)
2133 AND nvl(sd.end_order_line_number, -1) =
2134 nvl(p_line_number, -1)
2135 AND sd.supplier_id = p_tp_id
2136 AND sd.supplier_site_id = p_tp_site_id
2137 AND sd.inventory_item_id = p_item_id);
2138
2139 return l_return_code;
2140 EXCEPTION
2141 when NO_DATA_FOUND then
2142 l_return_code := 0;
2143 return l_return_code;
2144 END DOES_SHIPRCPT_EXIST;
2145
2146 -----------------------------------------------------------------
2147 -- Function does_detail_excep_exist
2148 -----------------------------------------------------------------
2149 FUNCTION DOES_DETAIL_EXCEP_EXIST( p_company_id IN Number,
2150 p_company_site_id IN NUMBER,
2151 p_item_id IN NUMBER,
2152 p_exception_type IN NUMBER,
2153 p_trx_id1 IN NUMBER,
2154 p_trx_id2 IN NUMBER)
2155 RETURN NUMBER IS
2156 l_exception_detail_id Number := 0;
2157
2158 BEGIN
2159
2160
2161 if p_trx_id2 is null then
2162 SELECT ed.exception_detail_id
2163 INTO l_exception_detail_id
2164 FROM msc_x_exception_details ed
2165 WHERE ed.plan_id = G_PLAN_ID
2166 AND ed.inventory_item_id = p_item_id
2167 AND ed.company_id = p_company_id
2168 AND ed.company_site_id = p_company_site_id
2169 AND ed.exception_type = p_exception_type
2170 AND ed.transaction_id1 = p_trx_id1;
2171 else
2172
2173 SELECT ed.exception_detail_id
2174 INTO l_exception_detail_id
2175 FROM msc_x_exception_details ed
2176 WHERE ed.plan_id = G_PLAN_ID
2177 AND ed.inventory_item_id = p_item_id
2178 AND ed.company_id = p_company_id
2179 AND ed.company_site_id = p_company_site_id
2180 AND ed.exception_type = p_exception_type
2181 AND ed.transaction_id1 = p_trx_id1
2182 AND ed.transaction_id2 = p_trx_id2;
2183 end if;
2184
2185 return l_exception_detail_id;
2186
2187 EXCEPTION
2188 when NO_DATA_FOUND then
2189 l_exception_detail_id := 0;
2190 return l_exception_detail_id;
2191 END DOES_DETAIL_EXCEP_EXIST;
2192
2193 ------------------------------------------------------------
2194 FUNCTION DOES_LO_EXIST (p_company_id IN Number,
2195 p_company_site_id IN Number,
2196 p_item_id In Number,
2197 p_exception_type In number,
2198 p_trx_id In number)
2199 RETURN NUMBER IS
2200
2201 l_ret_flag Number := -1;
2202 BEGIN
2203
2204 SELECT 1
2205 INTO l_ret_flag
2206 FROM dual
2207 WHERE EXISTS (SELECT 'x'
2208 FROM msc_x_exception_details
2209 WHERE plan_id = G_PLAN_ID
2210 AND company_id = p_company_id
2211 AND company_site_id = p_company_site_id
2212 AND inventory_item_id = p_item_id
2213 AND exception_type = p_exception_type
2214 AND transaction_id1 = p_trx_id);
2215 return l_ret_flag;
2216
2217 exception
2218 when NO_DATA_FOUND then
2219 l_ret_flag := -1;
2220 return l_ret_flag;
2221 END DOES_LO_EXIST;
2222
2223
2224 -------------------------------------------------------------
2225 --This is procedure is call when ods netting is run and also when loading
2226 --data.
2227 --delete all dependent exceptions when deleting or purging any entry
2228 --in msc_sup_dem_entries. We use the 'D' or 'P' sync indicator
2229 -- during the data upload that the record needs to removed. Currently,
2230 --the entry in msc_sup_dem_entries is set with the quantity = 0
2231
2232 PROCEDURE DELETE_EXEC_ORDER_DEPENDENCY (p_refresh_number IN Number) IS
2233 CURSOR delete_entry_c IS
2234 SELECT distinct sd.transaction_id,
2235 med.company_id,
2236 med.company_site_id,
2237 med.inventory_item_id,
2238 med.customer_id,
2239 med.customer_site_id,
2240 med.supplier_id,
2241 med.supplier_site_id,
2242 med.exception_group,
2243 med.exception_type,
2244 med.exception_detail_id
2245 FROM msc_sup_dem_entries sd, msc_x_exception_details med
2246 WHERE sd.plan_id = G_PLAN_ID
2247 AND sd.quantity = 0
2248 AND nvl(sd.last_update_login,-1) = -99
2249 AND sd.last_refresh_number >= p_refresh_number
2250 AND med.plan_id = sd.plan_id
2251 AND med.transaction_id1 = sd.transaction_id
2252 UNION
2253 SELECT distinct sd.transaction_id,
2254 med.company_id,
2255 med.company_site_id,
2256 med.inventory_item_id,
2257 med.customer_id,
2258 med.customer_site_id,
2259 med.supplier_id,
2260 med.supplier_site_id,
2261 med.exception_group,
2262 med.exception_type,
2263 med.exception_detail_id
2264 FROM msc_sup_dem_entries sd, msc_x_exception_details med
2265 WHERE sd.plan_id = G_PLAN_ID
2266 AND sd.quantity = 0
2267 AND nvl(sd.last_update_login, -1) = -99
2268 AND sd.last_refresh_number >= p_refresh_number
2269 AND med.plan_id = sd.plan_id
2270 AND med.transaction_id2 = sd.transaction_id;
2271
2272
2273 l_company_id Number;
2274 l_company_site_id Number;
2275 l_item_id Number;
2276 l_customer_id Number;
2277 l_customer_site_id Number;
2278 l_supplier_id Number;
2279 l_supplier_site_id Number;
2280 l_exception_group Number;
2281 l_exception_type Number;
2282 l_exception_detail_id Number;
2283 l_transaction_id Number;
2284 l_sr_instance_id Number;
2285 l_item_type Varchar2(20) := 'MSCSNDNT';
2286 l_item_key Varchar2(100) := null;
2287 l_row Number;
2288 BEGIN
2289 --dbms_output.put_line('Start delete qty = 0');
2290 open delete_entry_c;
2291 loop
2292
2293 fetch delete_entry_c into l_transaction_id,
2294 l_company_id,
2295 l_company_site_id,
2296 l_item_id,
2297 l_customer_id,
2298 l_customer_site_id,
2299 l_supplier_id,
2300 l_supplier_site_id,
2301 l_exception_group,
2302 l_exception_type,
2303 l_exception_detail_id;
2304 exit when delete_entry_c%NOTFOUND;
2305
2306 l_item_key := to_char(l_exception_group) || '-' ||
2307 to_char(l_exception_type) || '-' ||
2308 to_char(l_item_id) || '-' ||
2309 to_char(l_company_id) || '-' ||
2310 to_char(l_company_site_id) || '-' ||
2311 to_char(l_customer_id) || '-' ||
2312 to_char(l_customer_site_id) || '-' ||
2313 to_char(l_supplier_id) || '-' ||
2314 to_char(l_supplier_site_id) || '-' ||
2315 to_char(l_exception_detail_id) || '%';
2316
2317 delete_wf_notification(l_item_type, l_item_key);
2318 BEGIN
2319 --dbms_output.put_line('---Maintaining exceptions details');
2320 delete msc_x_exception_details
2321 where plan_id = G_PLAN_ID
2322 and company_id = l_company_id
2323 and company_site_id = l_company_site_id
2324 and inventory_item_id = l_item_id
2325 and exception_type = l_exception_type
2326 and exception_detail_id = l_exception_detail_id;
2327
2328 l_row := SQL%ROWCOUNT;
2329 --dbms_output.put_line('archive detail exception ' || l_row);
2330
2331 --dbms_output.put_line('---Maintaining item exceptions');
2332 update msc_item_exceptions
2333 set exception_count = exception_count - l_row,
2334 last_update_date = sysdate
2335 where plan_id = G_PLAN_ID
2336 and company_id = l_company_id
2337 and company_site_id = l_company_site_id
2338 and inventory_item_id = l_item_id
2339 and exception_type = l_exception_type;
2340
2341 l_row := SQL%ROWCOUNT;
2342 --dbms_output.put_line('archive item exception ' || l_row);
2343
2344 EXCEPTION
2345 when others then
2346 return;
2347 END;
2348
2349 end loop;
2350 close delete_entry_c;
2351
2352 exception
2353 when others then
2354 return;
2355 END DELETE_EXEC_ORDER_DEPENDENCY;
2356
2357 ---------------------------------------------------------------------
2358
2359 PROCEDURE DELETE_EXCEP IS
2360
2361 l_company_id Number;
2362 l_company_site_id Number;
2363 l_item_id Number;
2364 l_customer_id Number;
2365 l_customer_site_id Number;
2366 l_supplier_id Number;
2367 l_supplier_site_id Number;
2368 l_exception_group Number;
2369 l_exception_type Number;
2370 l_exception_detail_id Number;
2371 l_sr_instance_id Number;
2372 l_item_type Varchar2(20) := 'MSCSNDNT';
2373 l_item_key Varchar2(100) := null;
2374 l_row Number;
2375 p_days Number;
2376
2377
2378 CURSOR delete_excep_c (p_days in number)IS
2379 SELECT company_id,
2380 company_site_id,
2381 inventory_item_id,
2382 customer_id,
2383 customer_site_id,
2384 supplier_id,
2385 supplier_site_id,
2386 exception_group,
2387 exception_type,
2388 exception_detail_id
2389 FROM msc_x_exception_details
2390 WHERE nvl(plan_id,G_PLAN_ID) = G_PLAN_ID --6603563
2391 and trunc(last_update_date ) < trunc(sysdate) - p_days;
2392
2393
2394 BEGIN
2395
2396 p_days := nvl(FND_PROFILE.VALUE('MSC_PURGE_EXCEPTION_DAYS'),0);
2397
2398 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_days '||p_days);
2399 If (p_days > 0) then
2400
2401 open delete_excep_c(p_days);
2402 loop
2403
2404 fetch delete_excep_c into l_company_id,
2405 l_company_site_id,
2406 l_item_id,
2407 l_customer_id,
2408 l_customer_site_id,
2409 l_supplier_id,
2410 l_supplier_site_id,
2411 l_exception_group,
2412 l_exception_type,
2413 l_exception_detail_id;
2414 exit when delete_excep_c%NOTFOUND;
2415
2416 l_item_key := to_char(l_exception_group) || '-' ||
2417 to_char(l_exception_type) || '-' ||
2418 to_char(l_item_id) || '-' ||
2419 to_char(l_company_id) || '-' ||
2420 to_char(l_company_site_id) || '-' ||
2421 to_char(l_customer_id) || '-' ||
2422 to_char(l_customer_site_id) || '-' ||
2423 to_char(l_supplier_id) || '-' ||
2424 to_char(l_supplier_site_id) || '-' ||
2425 to_char(l_exception_detail_id) || '%';
2426
2427 delete_wf_notification(l_item_type, l_item_key);
2428
2429 BEGIN
2430
2431 delete msc_x_exception_details
2432 where exception_detail_id = l_exception_detail_id;
2433
2434 l_row := SQL%ROWCOUNT;
2435
2436 update msc_item_exceptions
2437 set exception_count = exception_count - l_row,
2438 last_update_date = sysdate
2439 where plan_id = G_PLAN_ID
2440 and company_id = l_company_id
2441 and company_site_id = l_company_site_id
2442 and inventory_item_id = l_item_id
2443 and exception_type = l_exception_type;
2444
2445 l_row := SQL%ROWCOUNT;
2446
2447
2448 EXCEPTION
2449 when others then
2450 return;
2451 END;
2452
2453 end loop;
2454 close delete_excep_c;
2455 end if;
2456 exception
2457 when others then
2458 return;
2459 END DELETE_EXCEP;
2460
2461 -------------------------------------------------------------------------
2462 -- PROCEDURE PURGE_ZQTY_EXEC_ORDER
2463 ------------------------------------------------------------------------
2464 PROCEDURE PURGE_ZQTY_EXEC_ORDER (p_refresh_number IN Number) IS
2465
2466 BEGIN
2467
2468 DELETE /*+ PARALLEL(sd) */ from msc_sup_dem_entries sd
2469 WHERE sd.plan_id = G_PLAN_ID
2470 AND sd.quantity = 0
2471 AND sd.publisher_order_type in
2472 (PURCHASE_ORDER,SALES_ORDER,ASN,SHIPMENT_RECEIPT)
2473 AND nvl(last_update_login,-1) = -99
2474 AND sd.last_refresh_number >= p_refresh_number;
2475
2476 EXCEPTION
2477 when others then
2478 return;
2479
2480 END PURGE_ZQTY_EXEC_ORDER;
2481 ---------------------------------------------------------------------
2482 --procedure DELETE_OBSOLETE_EXCEPTIONS
2483 ---------------------------------------------------------------------
2484 PROCEDURE delete_obsolete_exceptions(p_company_id IN Number,
2485 p_company_site_id IN Number,
2486 p_customer_id in Number,
2487 p_customer_site_id In Number,
2488 p_supplier_id IN Number,
2489 p_supplier_site_id IN Number,
2490 p_exception_group IN Number,
2491 p_curr_exc_type in Number,
2492 p_obs_exc_type in Number,
2493 p_item_id in Number,
2494 p_bkt_start_date in Date,
2495 p_bkt_end_date in Date,
2496 p_type In Number,
2497 p_transaction_id1 In Number,
2498 p_transaction_id2 IN number
2499 ) IS
2500
2501 l_exception_detail_id Number;
2502 l_row number := 0;
2503 l_item_type Varchar2(20) := 'MSCSNDNT';
2504 l_item_key Varchar2(100) := null;
2505 v_exception_group Number ;
2506
2507
2508 BEGIN
2509 l_item_key := to_char(p_exception_group) || '-' ||
2510 to_char(p_curr_exc_type) || '-' ||
2511 to_char(p_item_id) || '-' ||
2512 to_char(p_company_id) || '-' ||
2513 to_char(p_company_site_id) || '-' ||
2514 to_char(p_customer_id) || '-' ||
2515 to_char(p_customer_site_id) || '-' ||
2516 to_char(p_supplier_id) || '-' ||
2517 to_char(p_supplier_site_id) || '%';
2518
2519 delete_wf_notification(l_item_type, l_item_key);
2520
2521 IF (p_obs_exc_type is not null) THEN
2522 l_item_key := to_char(p_exception_group) || '-' ||
2523 to_char(p_obs_exc_type) || '-' ||
2524 to_char(p_item_id) || '-' ||
2525 to_char(p_company_id) || '-' ||
2526 to_char(p_company_site_id) || '-' ||
2527 to_char(p_customer_id) || '-' ||
2528 to_char(p_customer_site_id) || '-' ||
2529 to_char(p_supplier_id) || '-' ||
2530 to_char(p_supplier_site_id) || '%';
2531
2532 FND_FILE.PUT_LINE(FND_FILE.LOG,'for 1 : l_item_key'||l_item_key);
2533 delete_wf_notification(l_item_type, l_item_key);
2534
2535 -- added for VMI Exceptions: notification deletion behaviour
2536
2537 IF (p_obs_exc_type in (G_EXCEP29, G_EXCEP30)) then
2538
2539 v_exception_group := G_MATERIAL_EXCESS ;
2540
2541 l_item_key := to_char(v_exception_group) || '-' ||
2542 to_char(p_obs_exc_type) || '-' ||
2543 to_char(p_item_id) || '-' ||
2544 to_char(p_company_id) || '-' ||
2545 to_char(p_company_site_id) || '-' ||
2546 to_char(p_customer_id) || '-' ||
2547 to_char(p_customer_site_id) || '-' ||
2548 to_char(p_supplier_id) || '-' ||
2549 to_char(p_supplier_site_id) || '%';
2550
2551 FND_FILE.PUT_LINE(FND_FILE.LOG,'for 2 : l_item_key = '||l_item_key);
2552 delete_wf_notification(l_item_type, l_item_key);
2553
2554 END IF;
2555 END IF;
2556
2557 IF (p_type = EXECUTION_ORDER) then
2558 IF (p_curr_exc_type is not null) THEN
2559 delete from msc_x_exception_details ex
2560 where ex.plan_id = G_PLAN_ID
2561 and ex.company_id = p_company_id
2562 and ex.company_site_id = p_company_site_id
2563 and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
2564 and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
2565 and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
2566 and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
2567 and ex.inventory_item_id = p_item_id
2568 and ex.exception_type = p_curr_exc_type
2569 and ex.transaction_id1 = p_transaction_id1
2570 and ex.transaction_id2 = p_transaction_id2
2571 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2572
2573 l_row := SQL%ROWCOUNT;
2574 ----dbms_output.put_line('item row delete ' || l_row);
2575
2576 update msc_item_exceptions ex
2577 set ex.exception_count = ex.exception_count - l_row,
2578 ex.last_update_date = sysdate
2579 where ex.plan_id = G_PLAN_ID
2580 and ex.company_id = p_company_id
2581 and ex.company_site_id = p_company_site_id
2582 and ex.inventory_item_id = p_item_id
2583 and ex.exception_type = p_curr_exc_type
2584 and ex.version = 0
2585 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2586 ----dbms_output.put_line('delete item exception');
2587 END IF;
2588
2589 IF (p_obs_exc_type is not null) THEN
2590 l_row := 0;
2591
2592 delete from msc_x_exception_details ex
2593 where ex.plan_id = G_PLAN_ID
2594 and ex.company_id = p_company_id
2595 and ex.company_site_id = p_company_site_id
2596 and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
2597 and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
2598 and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
2599 and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
2600 and ex.inventory_item_id = p_item_id
2601 and ex.exception_type = p_obs_exc_type
2602 and ex.transaction_id1 = p_transaction_id1
2603 and ex.transaction_id2 = p_transaction_id2
2604 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2605
2606 l_row := SQL%ROWCOUNT;
2607 ----dbms_output.put_line('item row delete ' || l_row);
2608
2609 update msc_item_exceptions ex
2610 set ex.exception_count = ex.exception_count - l_row,
2611 ex.last_update_date = sysdate
2612 where ex.plan_id = G_PLAN_ID
2613 and ex.company_id = p_company_id
2614 and ex.company_site_id = p_company_site_id
2615 and ex.inventory_item_id = p_item_id
2616 and ex.exception_type = p_obs_exc_type
2617 and ex.version = 0
2618 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2619 ----dbms_output.put_line('delete item exception');
2620 END IF;
2621
2622 ELSIF (p_type in (SUPPLY_PLANNING,DEMAND_PLANNING,VMI)) then
2623
2624 IF (p_curr_exc_type is not null) THEN
2625 delete from msc_x_exception_details ex
2626 where ex.plan_id = G_PLAN_ID
2627 and ex.company_id = p_company_id
2628 and ex.company_site_id = p_company_site_id
2629 and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
2630 and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
2631 and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
2632 and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
2633 and ex.inventory_item_id = p_item_id
2634 and ex.exception_type = p_curr_exc_type
2635 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2636
2637 l_row := SQL%ROWCOUNT;
2638 ----dbms_output.put_line('detail row delete ' || l_row);
2639
2640 update msc_item_exceptions ex
2641 set ex.exception_count = ex.exception_count - l_row,
2642 ex.last_update_date = sysdate
2643 where ex.plan_id = G_PLAN_ID
2644 and ex.company_id = p_company_id
2645 and ex.company_site_id = p_company_site_id
2646 and ex.inventory_item_id = p_item_id
2647 and ex.exception_type = p_curr_exc_type
2648 and ex.version = 0
2649 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2650 ----dbms_output.put_line('delete item exception');
2651 END IF;
2652 IF (p_obs_exc_type is not null) THEN
2653 l_row := 0;
2654 delete from msc_x_exception_details ex
2655 where ex.plan_id = G_PLAN_ID
2656 and ex.company_id = p_company_id
2657 and ex.company_site_id = p_company_site_id
2658 and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
2659 and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
2660 and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
2661 and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
2662 and ex.inventory_item_id = p_item_id
2663 and ex.exception_type = p_obs_exc_type
2664 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2665
2666 l_row := SQL%ROWCOUNT;
2667 ----dbms_output.put_line('detail row delete ' || l_row);
2668
2669 update msc_item_exceptions ex
2670 set ex.exception_count = ex.exception_count - l_row,
2671 ex.last_update_date = sysdate
2672 where ex.plan_id = G_PLAN_ID
2673 and ex.company_id = p_company_id
2674 and ex.company_site_id = p_company_site_id
2675 and ex.inventory_item_id = p_item_id
2676 and ex.exception_type = p_obs_exc_type
2677 and ex.version = 0
2678 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2679 ----dbms_output.put_line('delete item exception');
2680 END IF;
2681 ELSE
2682 IF (p_curr_exc_type is not null) THEN
2683 delete from msc_x_exception_details ex
2684 where ex.plan_id = G_PLAN_ID
2685 and ex.company_id = p_company_id
2686 and ex.company_site_id = p_company_site_id
2687 and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
2688 and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
2689 and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
2690 and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
2691 and ex.inventory_item_id = p_item_id
2692 and ex.exception_type = p_curr_exc_type
2693 and ex.date1 = p_bkt_start_date
2694 and ex.date2 = p_bkt_end_date
2695 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2696
2697 l_row := SQL%ROWCOUNT;
2698 ----dbms_output.put_line('detail row delete ' || l_row);
2699
2700 update msc_item_exceptions ex
2701 set ex.exception_count = ex.exception_count - l_row,
2702 ex.last_update_date = sysdate
2703 where ex.plan_id = G_PLAN_ID
2704 and ex.company_id = p_company_id
2705 and ex.company_site_id = p_company_site_id
2706 and ex.inventory_item_id = p_item_id
2707 and ex.exception_type = p_curr_exc_type
2708 and ex.version = 0
2709 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2710 ----dbms_output.put_line('delete item exception');
2711 END IF;
2712 IF (p_obs_exc_type is not null) THEN
2713 l_row := 0;
2714 delete from msc_x_exception_details ex
2715 where ex.plan_id = G_PLAN_ID
2716 and ex.company_id = p_company_id
2717 and ex.company_site_id = p_company_site_id
2718 and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
2719 and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
2720 and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
2721 and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
2722 and ex.inventory_item_id = p_item_id
2723 and ex.exception_type = p_obs_exc_type
2724 and ex.date1 = p_bkt_start_date
2725 and ex.date2 = p_bkt_end_date
2726 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2727
2728 l_row := SQL%ROWCOUNT;
2729 ----dbms_output.put_line('detail row delete ' || l_row);
2730
2731 update msc_item_exceptions ex
2732 set ex.exception_count = ex.exception_count - l_row,
2733 ex.last_update_date = sysdate
2734 where ex.plan_id = G_PLAN_ID
2735 and ex.company_id = p_company_id
2736 and ex.company_site_id = p_company_site_id
2737 and ex.inventory_item_id = p_item_id
2738 and ex.exception_type = p_obs_exc_type
2739 and ex.version = 0
2740 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
2741 ----dbms_output.put_line('delete item exception');
2742 END IF;
2743 END IF;
2744
2745 exception
2746 when others then
2747 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.delete_obsolete_exceptions');
2748 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
2749 return;
2750
2751 END delete_obsolete_exceptions;
2752
2753 ----------------------------------------------------------------------
2754 --PROCEDURE CLEAN_UP_PROCESS
2755 --Clean up process only for the seeded exception group
2756 -------------------------------------------------------------------------
2757 PROCEDURE clean_up_process IS
2758
2759 BEGIN
2760
2761 --dbms_output.put_line('Update the magic number');
2762
2763
2764 --Update the last_update_login back to null to ensure accurate archival
2765 --when exceptions are generated in the next round
2766
2767 update msc_item_exceptions ex
2768 set ex.last_update_login = null,
2769 ex.last_update_date = sysdate
2770 where ex.plan_id = G_PLAN_ID
2771 --and ex.version = 0
2772 and exception_group in (1,2,3,4,5,6,7,8,9,10)
2773 and nvl(ex.last_update_login,-1) = G_MAGIC_NUMBER;
2774
2775 --update the if the count is 0 to older version
2776 update msc_item_exceptions ex
2777 set ex.version = version + 1,
2778 ex.last_update_date = sysdate
2779 where ex.plan_id = G_PLAN_ID
2780 --and ex.version = 0
2781 and exception_group in (1,2,3,4,5,6,7,8,9,10)
2782 and ex.exception_count = 0;
2783
2784 -- In prior release, we have kept a history of all the exceptions
2785 -- occuring during netting for intelligent analysis use such as
2786 -- exception convergence; overtime or latency of a plan in msc_item_exceptions
2787 -- table. The current usage is using the latest version of the data and
2788 -- not all the history data. If the table is maintaining all the history
2789 -- data, in the rolling of the netting engine run for a period of time,
2790 -- the table will grow quickly and create a performance problem. Therefore,
2791 -- the table is arhived based on the user defined profile option and only keep
2792 -- certain number of version. Default verion = 20
2793
2794 delete msc_item_exceptions ex
2795 where plan_id = G_PLAN_ID
2796 and exception_group in (1,2,3,4,5,6,7,8,9,10)
2797 and version > 20;
2798
2799 EXCEPTION
2800 when others then
2801 return;
2802
2803 END CLEAN_UP_PROCESS;
2804
2805
2806 --==============================================================================
2807 -- PROCEDURE add_to_detail_tbl
2808 --==============================================================================
2809 PROCEDURE ADD_TO_EXCEPTION_TBL(p_company_id IN Number,
2810 p_company_name IN Varchar2,
2811 p_company_site_id IN Number,
2812 p_company_site_name IN Varchar2,
2813 p_item_id IN Number,
2814 p_item_name IN Varchar2,
2815 p_item_description IN Varchar2,
2816 p_exception_type IN Number,
2817 p_exception_type_name IN Varchar2,
2818 p_exception_group IN Number,
2819 p_exception_group_name IN Varchar2,
2820 p_trx_id1 IN Number,
2821 p_trx_id2 IN Number,
2822 p_customer_id IN Number,
2823 p_customer_name IN Varchar2,
2824 p_customer_site_id IN Number,
2825 p_customer_site_name IN varchar2,
2826 p_customer_item_name IN Varchar2,
2827 p_supplier_id IN Number,
2828 p_supplier_name IN Varchar2,
2829 p_supplier_site_id IN Number,
2830 p_supplier_site_name IN Varchar2,
2831 p_supplier_item_name IN Varchar2,
2832 p_number1 IN Number,
2833 p_number2 IN Number,
2834 p_number3 IN Number,
2835 p_threshold IN Number,
2836 p_lead_time IN Number,
2837 p_item_min_qty IN Number,
2838 p_item_max_qty IN Number,
2839 p_order_number IN Varchar2 ,
2840 p_release_number IN Varchar2,
2841 p_line_number IN Varchar2,
2842 p_end_order_number IN Varchar2,
2843 p_end_order_rel_number IN Varchar2,
2844 p_end_order_line_number IN Varchar2,
2845 p_creation_date IN Date,
2846 p_tp_creation_date IN Date,
2847 p_date1 IN Date,
2848 p_date2 IN Date,
2849 p_date3 IN Date,
2850 p_date4 IN Date,
2851 p_date5 IN Date,
2852 p_exception_basis IN Varchar2,
2853 a_company_id IN OUT NOCOPY number_arr,
2854 a_company_name IN OUT NOCOPY publisherList,
2855 a_company_site_id IN OUT NOCOPY number_arr,
2856 a_company_site_name IN OUT NOCOPY pubsiteList,
2857 a_item_id IN OUT NOCOPY number_arr,
2858 a_item_name IN OUT NOCOPY itemnameList,
2859 a_item_desc IN OUT NOCOPY itemdescList,
2860 a_exception_type IN OUT NOCOPY number_arr,
2861 a_exception_type_name IN OUT NOCOPY exceptypeList,
2862 a_exception_group IN OUT NOCOPY number_arr,
2863 a_exception_group_name IN OUT NOCOPY excepgroupList,
2864 a_trx_id1 IN OUT NOCOPY number_arr,
2865 a_trx_id2 IN OUT NOCOPY number_arr,
2866 a_customer_id IN OUT NOCOPY number_arr,
2867 a_customer_name IN OUT NOCOPY customerList,
2868 a_customer_site_id IN OUT NOCOPY number_arr,
2869 a_customer_site_name IN OUT NOCOPY custsiteList,
2870 a_customer_item_name IN OUT NOCOPY itemnameList,
2871 a_supplier_id IN OUT NOCOPY number_arr,
2872 a_supplier_name IN OUT NOCOPY supplierList,
2873 a_supplier_site_id IN OUT NOCOPY number_arr,
2874 a_supplier_site_name IN OUT NOCOPY suppsiteList,
2875 a_supplier_item_name IN OUT NOCOPY itemnameList,
2876 a_number1 IN OUT NOCOPY number_arr,
2877 a_number2 IN OUT NOCOPY number_arr,
2878 a_number3 IN OUT NOCOPY number_arr,
2879 a_threshold IN OUT NOCOPY number_arr,
2880 a_lead_time IN OUT NOCOPY number_arr,
2881 a_item_min_qty IN OUT NOCOPY number_arr,
2882 a_item_max_qty IN OUT NOCOPY number_arr,
2883 a_order_number IN OUT NOCOPY ordernumberList,
2884 a_release_number IN OUT NOCOPY releasenumList,
2885 a_line_number IN OUT NOCOPY linenumList,
2886 a_end_order_number IN OUT NOCOPY ordernumberList,
2887 a_end_order_rel_number IN OUT NOCOPY releasenumList,
2888 a_end_order_line_number IN OUT NOCOPY linenumList,
2889 a_creation_date IN OUT NOCOPY date_arr,
2890 a_tp_creation_date IN OUT NOCOPY date_arr,
2891 a_date1 IN OUT NOCOPY date_arr,
2892 a_date2 IN OUT NOCOPY date_arr,
2893 a_date3 IN OUT NOCOPY date_arr,
2894 a_date4 IN OUT NOCOPY date_arr,
2895 a_date5 IN OUT NOCOPY date_arr,
2896 a_exception_basis IN OUT NOCOPY exceptbasisList) IS
2897
2898 BEGIN
2899
2900 a_company_id.EXTEND;
2901 a_company_name.EXTEND;
2902 a_company_site_id.EXTEND;
2903 a_company_site_name.EXTEND;
2904 a_item_id.EXTEND;
2905 a_item_name.EXTEND;
2906 a_item_desc.EXTEND;
2907 a_exception_type.EXTEND;
2908 a_exception_type_name.EXTEND;
2909 a_exception_group.EXTEND;
2910 a_exception_group_name.EXTEND;
2911 a_trx_id1.EXTEND;
2912 a_trx_id2.EXTEND;
2913 a_customer_id.EXTEND;
2914 a_customer_name.EXTEND;
2915 a_customer_site_id.EXTEND;
2916 a_customer_site_name.EXTEND;
2917 a_customer_item_name.EXTEND;
2918 a_supplier_id.EXTEND;
2919 a_supplier_name.EXTEND;
2920 a_supplier_site_id.EXTEND;
2921 a_supplier_site_name.EXTEND;
2922 a_supplier_item_name.EXTEND;
2923 a_number1.EXTEND;
2924 a_number2.EXTEND;
2925 a_number3.EXTEND;
2926 a_threshold.EXTEND;
2927 a_lead_time.EXTEND;
2928 a_item_min_qty.EXTEND;
2929 a_item_max_qty.EXTEND;
2930 a_order_number.EXTEND;
2931 a_release_number.EXTEND;
2932 a_line_number.EXTEND;
2933 a_end_order_number.EXTEND;
2934 a_end_order_rel_number.EXTEND;
2935 a_end_order_line_number.EXTEND;
2936 a_creation_date.EXTEND;
2937 a_tp_creation_date.EXTEND;
2938 a_date1.EXTEND;
2939 a_date2.EXTEND;
2940 a_date3.EXTEND;
2941 a_date4.EXTEND;
2942 a_date5.EXTEND;
2943 a_exception_basis.EXTEND;
2944
2945 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Count ' || a_company_id.COUNT);
2946
2947 a_company_id(a_company_id.COUNT) := p_company_id;
2948 a_company_name(a_company_id.COUNT) := p_company_name;
2949 a_company_site_id(a_company_id.COUNT) := p_company_site_id;
2950 a_company_site_name(a_company_id.COUNT) := p_company_site_name;
2951 a_item_id(a_company_id.COUNT) := p_item_id;
2952 a_item_name(a_company_id.COUNT) := p_item_name;
2953 a_item_desc(a_company_id.COUNT) := p_item_description;
2954 a_exception_type(a_company_id.COUNT) := p_exception_type;
2955 a_exception_type_name(a_company_id.COUNT) := p_exception_type_name;
2956 a_exception_group(a_company_id.COUNT) := p_exception_group;
2957 a_exception_group_name(a_company_id.COUNT) := p_exception_group_name;
2958 a_trx_id1(a_company_id.COUNT) := p_trx_id1;
2959 a_trx_id2(a_company_id.COUNT) := p_trx_id2;
2960 a_customer_id(a_company_id.COUNT) := p_customer_id;
2961 a_customer_name(a_company_id.COUNT) := p_customer_name;
2962 a_customer_site_id(a_company_id.COUNT) := p_customer_site_id;
2963 a_customer_site_name(a_company_id.COUNT) := p_customer_site_name;
2964 a_customer_item_name(a_company_id.COUNT) := p_customer_item_name;
2965 a_supplier_id(a_company_id.COUNT) := p_supplier_id;
2966 a_supplier_name(a_company_id.COUNT) := p_supplier_name;
2967 a_supplier_site_id(a_company_id.COUNT) := p_supplier_site_id;
2968 a_supplier_site_name(a_company_id.COUNT) := p_supplier_site_name;
2969 a_supplier_item_name(a_company_id.COUNT) := p_supplier_item_name;
2970 a_number1(a_company_id.COUNT) := p_number1;
2971 a_number2(a_company_id.COUNT) := p_number2;
2972 a_number3(a_company_id.COUNT) := p_number3;
2973 a_threshold(a_company_id.COUNT) := p_threshold;
2974 a_lead_time(a_company_id.COUNT) := p_lead_time;
2975 a_item_min_qty(a_company_id.COUNT) := p_item_min_qty;
2976 a_item_max_qty(a_company_id.COUNT) := p_item_max_qty;
2977 a_order_number(a_company_id.COUNT) := p_order_number;
2978 a_release_number(a_company_id.COUNT) := p_release_number;
2979 a_line_number(a_company_id.COUNT) := p_line_number;
2980 a_end_order_number(a_company_id.COUNT) := p_end_order_number;
2981 a_end_order_rel_number(a_company_id.COUNT) := p_end_order_rel_number;
2982 a_end_order_line_number(a_company_id.COUNT) := p_end_order_line_number;
2983 a_creation_date(a_company_id.COUNT) := p_creation_date;
2984 a_tp_creation_date(a_company_id.COUNT) := p_tp_creation_date;
2985 a_date1(a_company_id.COUNT) := p_date1;
2986 a_date2(a_company_id.COUNT) := p_date2;
2987 a_date3(a_company_id.COUNT) := p_date3;
2988 a_date4(a_company_id.COUNT) := p_date4;
2989 a_date5(a_company_id.COUNT) := p_date5;
2990 a_exception_basis(a_company_id.COUNT) := p_exception_basis;
2991
2992
2993
2994
2995 EXCEPTION
2996 when others then
2997 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.add_to_exception_tbl');
2998 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
2999 --dbms_output.put_line('add_to_exception_tbl error ' || sqlerrm);
3000 return;
3001 END add_to_exception_tbl;
3002
3003 --===================================================================
3004 -- PROCEDURE POPULATE_EXCEPTION_DATA
3005 --===================================================================
3006 PROCEDURE POPULATE_EXCEPTION_DATA(
3007 a_company_id IN number_arr,
3008 a_company_name IN publisherList,
3009 a_company_site_id IN number_arr,
3010 a_company_site_name IN pubsiteList,
3011 a_item_id IN number_arr,
3012 a_item_name IN itemnameList,
3013 a_item_desc IN itemdescList,
3014 a_exception_type IN number_arr,
3015 a_exception_type_name IN exceptypeList,
3016 a_exception_group IN number_arr,
3017 a_exception_group_name IN excepgroupList,
3018 a_trx_id1 IN number_arr,
3019 a_trx_id2 IN number_arr,
3020 a_customer_id IN number_arr,
3021 a_customer_name IN customerList,
3022 a_customer_site_id IN number_arr,
3023 a_customer_site_name IN custsiteList,
3024 a_customer_item_name IN itemnameList,
3025 a_supplier_id IN number_arr,
3026 a_supplier_name IN supplierList,
3027 a_supplier_site_id IN number_arr,
3028 a_supplier_site_name IN suppsiteList,
3029 a_supplier_item_name IN itemnameList,
3030 a_number1 IN number_arr,
3031 a_number2 IN number_arr,
3032 a_number3 IN number_arr,
3033 a_threshold IN number_arr,
3034 a_lead_time IN number_arr,
3035 a_item_min_qty IN number_arr,
3036 a_item_max_qty IN number_arr,
3037 a_order_number IN ordernumberList,
3038 a_release_number IN releasenumList,
3039 a_line_number IN linenumList,
3040 a_end_order_number IN ordernumberList,
3041 a_end_order_rel_number IN releasenumList,
3042 a_end_order_line_number IN linenumList,
3043 a_creation_date IN date_arr,
3044 a_tp_creation_date IN date_arr,
3045 a_date1 IN date_arr,
3046 a_date2 IN date_arr,
3047 a_date3 IN date_arr,
3048 a_date4 IN date_arr,
3049 a_date5 IN date_arr,
3050 a_exception_basis IN exceptbasisList) IS
3051
3052 l_exist Number := 0;
3053 l_row Number;
3054 errors Number;
3055
3056
3057 BEGIN
3058 --dbms_output.put_line('Populate exception data ' || a_company_id.COUNT);
3059
3060 IF a_company_id IS NOT NULL AND a_company_id.COUNT > 0 THEN
3061 FORALL i in 1..a_company_id.COUNT
3062 insert into msc_x_exception_details (
3063 exception_detail_id,
3064 exception_type,
3065 exception_type_name,
3066 exception_group,
3067 exception_group_name,
3068 date1,
3069 date2,
3070 date3,
3071 date4,
3072 date5,
3073 order_creation_date1,
3074 order_creation_date2,
3075 transaction_id1,
3076 transaction_id2,
3077 number1,
3078 number2,
3079 number3,
3080 threshold,
3081 lead_time,
3082 item_min_qty,
3083 item_max_qty,
3084 version,
3085 plan_id,
3086 sr_instance_id,
3087 company_id,
3088 company_name,
3089 company_site_id,
3090 company_site_name,
3091 inventory_item_id,
3092 item_name,
3093 item_description,
3094 last_update_date,
3095 last_updated_by,
3096 last_update_login,
3097 creation_date,
3098 created_by,
3099 customer_id,
3100 customer_name,
3101 customer_site_id,
3102 customer_site_name,
3103 customer_item_name,
3104 supplier_id,
3105 supplier_name,
3106 supplier_site_id,
3107 supplier_site_name,
3108 supplier_item_name,
3109 order_number,
3110 release_number,
3111 line_number,
3112 end_order_number,
3113 end_order_rel_number,
3114 end_order_line_number,
3115 exception_basis
3116 )
3117 values (
3118 msc_x_exception_details_s.nextval,
3119 a_exception_type(i),
3120 a_exception_type_name(i),
3121 a_exception_group(i),
3122 a_exception_group_name(i),
3123 trunc(a_date1(i)),
3124 trunc(a_date2(i)),
3125 trunc(a_date3(i)),
3126 trunc(a_date4(i)),
3127 trunc(a_date5(i)),
3128 trunc(a_creation_date(i)),
3129 trunc(a_tp_creation_date(i)),
3130 a_trx_id1(i),
3131 a_trx_id2(i),
3132 a_number1(i),
3133 a_number2(i),
3134 a_number3(i),
3135 a_threshold(i),
3136 a_lead_time(i),
3137 a_item_min_qty(i),
3138 a_item_max_qty(i),
3139 'CURRENT',
3140 G_PLAN_ID,
3141 G_SR_INSTANCE_ID,
3142 a_company_id(i),
3143 a_company_name(i),
3144 a_company_site_id(i),
3145 a_company_site_name(i),
3146 a_item_id(i),
3147 a_item_name(i),
3148 a_item_desc(i),
3149 sysdate,
3150 fnd_global.user_id,
3151 G_MAGIC_NUMBER,
3152 sysdate,
3153 fnd_global.user_id,
3154 a_customer_id(i),
3155 a_customer_name(i),
3156 a_customer_site_id(i),
3157 a_customer_site_name(i),
3158 a_customer_item_name(i),
3159 a_supplier_id(i),
3160 a_supplier_name(i),
3161 a_supplier_site_id(i),
3162 a_supplier_site_name(i),
3163 a_supplier_item_name(i),
3164 a_order_number(i),
3165 a_release_number(i),
3166 a_line_number(i),
3167 a_end_order_number(i),
3168 a_end_order_rel_number(i),
3169 a_end_order_line_number(i),
3170 a_exception_basis(i)
3171 );
3172
3173
3174
3175 FOR i in 1..a_company_id.COUNT LOOP
3176 l_exist := does_exception_exist(a_company_id(i),
3177 a_company_site_id(i),
3178 a_item_id(i),
3179 a_exception_type(i),
3180 a_exception_group(i));
3181 IF (l_exist = 1) THEN
3182 update msc_item_exceptions
3183 set exception_count = exception_count + 1,
3184 last_update_date = sysdate
3185 where plan_id = G_PLAN_ID
3186 and company_id = a_company_id(i)
3187 and company_site_id = a_company_site_id(i)
3188 and inventory_item_id = a_item_id(i)
3189 and exception_type = a_exception_type(i)
3190 and exception_group = a_exception_group(i)
3191 and version = 0;
3192
3193
3194 ELSE
3195 insert into msc_item_exceptions( plan_id,
3196 sr_instance_id,
3197 company_id,
3198 company_site_id,
3199 organization_id,
3200 inventory_item_id,
3201 version,
3202 exception_type,
3203 exception_group,
3204 exception_count,
3205 last_update_date,
3206 last_updated_by,
3207 creation_date,
3208 created_by,
3209 last_update_login)
3210 values (G_PLAN_ID,
3211 G_SR_INSTANCE_ID,
3212 a_company_id(i),
3213 a_company_site_id(i),
3214 -1,
3215 a_item_id(i),
3216 0,
3217 a_exception_type(i),
3218 a_exception_group(i),
3219 1,
3220 sysdate,
3221 -1,
3222 sysdate,
3223 -1,
3224 G_MAGIC_NUMBER
3225 );
3226 END IF;
3227 END LOOP;
3228 END IF;
3229
3230 EXCEPTION
3231 when others then
3232 --dbms_output.put_line('Error in Populate_exception_date ' || sqlerrm);
3233 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.populate_exception_data');
3234 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3235 return;
3236 END POPULATE_EXCEPTION_DATA;
3237
3238 ----------------------------------------------------------------
3239 --PROCEDURE ADD_TO_DELETE_TBL
3240 --------------------------------------------------------------
3241 PROCEDURE add_to_delete_tbl ( p_company_id in number,
3242 p_company_site_id in number,
3243 p_customer_id in number,
3244 p_customer_site_id in number,
3245 p_supplier_id in number,
3246 p_supplier_site_id in number,
3247 p_item_id in number,
3248 p_group in number,
3249 p_type in number,
3250 p_trxid1 in number,
3251 p_trxid2 in number,
3252 p_date1 in date,
3253 p_date2 in date,
3254 t_company_list IN OUT NOCOPY number_arr,
3255 t_company_site_list IN OUT NOCOPY number_arr,
3256 t_customer_list IN OUT NOCOPY number_arr,
3257 t_customer_site_list IN OUT NOCOPY number_arr,
3258 t_supplier_list IN OUT NOCOPY number_arr,
3259 t_supplier_site_list IN OUT NOCOPY number_arr,
3260 t_item_list IN OUT NOCOPY number_arr,
3261 t_group_list IN OUT NOCOPY number_arr,
3262 t_type_list IN OUT NOCOPY number_arr,
3263 t_trxid1_list IN OUT NOCOPY number_arr,
3264 t_trxid2_list IN OUT NOCOPY number_arr,
3265 t_date1_list IN OUT NOCOPY date_arr,
3266 t_date2_list IN OUT NOCOPY date_arr) IS
3267
3268
3269 l_counter Number := 0;
3270
3271 BEGIN
3272
3273 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Add to plsql table list');
3274 --dbms_output.put_line('Add to plsql delete table list');
3275
3276 t_item_list.EXTEND;
3277 t_company_list.EXTEND;
3278 t_company_site_list.EXTEND;
3279 t_customer_list.EXTEND;
3280 t_customer_site_list.EXTEND;
3281 t_supplier_list.EXTEND;
3282 t_supplier_site_list.EXTEND;
3283 t_group_list.EXTEND;
3284 t_type_list.EXTEND;
3285 t_trxid1_list.EXTEND;
3286 t_trxid2_list.EXTEND;
3287 t_date1_list.EXTEND;
3288 t_date2_list.EXTEND;
3289
3290 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Count ' || t_item_list.COUNT);
3291 -- dbms_output.put_line('Count ' || t_item_list.COUNT);
3292
3293 t_item_list(t_item_list.COUNT) := p_item_id;
3294 t_company_list(t_item_list.COUNT) := p_company_id;
3295 t_company_site_list(t_item_list.COUNT) := p_company_site_id;
3296 t_customer_list(t_item_list.COUNT) := p_customer_id;
3297 t_customer_site_list(t_item_list.COUNT) := p_customer_site_id;
3298 t_supplier_list(t_item_list.COUNT) := p_supplier_id;
3299 t_supplier_site_list(t_item_list.COUNT) := p_supplier_site_id;
3300 t_group_list(t_item_list.COUNT) := p_group;
3301 t_type_list(t_item_list.COUNT) := p_type;
3302 t_trxid1_list(t_item_list.COUNT) := p_trxid1;
3303 t_trxid2_list(t_item_list.COUNT) := p_trxid2;
3304 t_date1_list(t_item_list.COUNT) := p_date1;
3305 t_date2_list(t_item_list.COUNT) := p_date2;
3306
3307 EXCEPTION
3308 WHEN others then
3309 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.add_to_delete_tbl');
3310 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3311 --dbms_output.put_line('add_to_delete_tbl error ' || sqlerrm);
3312 return;
3313 END add_to_delete_tbl;
3314
3315 --------------------------------------------------------------------
3316 --archive_exception
3317 --------------------------------------------------------------------
3318
3319 PROCEDURE archive_exception (t_company_list In number_arr,
3320 t_company_site_list in number_arr,
3321 t_customer_list in number_arr,
3322 t_customer_site_list in number_arr,
3323 t_supplier_list in number_arr,
3324 t_supplier_site_list in number_arr,
3325 t_item_list In number_arr,
3326 t_group_list in number_arr,
3327 t_type_list in number_arr,
3328 t_trxid1_list in number_arr,
3329 t_trxid2_list in number_arr,
3330 t_date1_list in date_arr,
3331 t_date2_list in date_arr) IS
3332
3333
3334 l_row number:= 0;
3335 l_item_type Varchar2(20) := 'MSCSNDNT';
3336 l_item_key Varchar2(100) := null;
3337 i Number;
3338 t_count1 number_arr := number_arr();
3339 t_count2 number_arr := number_arr();
3340 t_count3 number_arr := number_arr();
3341
3342 BEGIN
3343
3344 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Delete in batch:' || t_item_list.COUNT);
3345 --dbms_output.put_line('Delete batch ' || t_item_list.COUNT);
3346
3347 IF (t_item_list is not null and t_item_list.COUNT > 0) THEN
3348
3349 --======================================================================
3350 -- archive the order execution exceptions
3351 --======================================================================
3352 FORALL i in 1..t_item_list.COUNT
3353 delete msc_x_exception_details
3354 where inventory_item_id = t_item_list(i)
3355 and company_id = t_company_list(i)
3356 and company_site_id = t_company_site_list(i)
3357 and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
3358 and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
3359 and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
3360 and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
3361 and exception_group = t_group_list(i)
3362 and exception_type = t_type_list(i)
3363 and transaction_id1 = t_trxid1_list(i)
3364 and nvl(transaction_id2,-1) = nvl(t_trxid2_list(i),-1)
3365 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER
3366 and nvl(LAST_UPDATE_LOGIN,0) <> -99;
3367 --checking the key in case the record is not to be deleted.This is updated only for cursor exception_34.
3368
3369
3370 FOR i in 1..t_item_list.COUNT LOOP
3371 t_count1.EXTEND;
3372 t_count1(t_count1.COUNT) := SQL%BULK_ROWCOUNT(i);
3373 --dbms_output.put_line('delete trx ' || t_trxid1_list(i) || '-' || t_count1(i) || ' Comp ' || t_company_list(i) || t_company_site_list(i) );
3374 --dbms_output.put_line('item ' || t_item_list(i) || ' Cust ' || t_customer_list(i) || t_customer_site_list(i) );
3375 --dbms_output.put_line(' supp ' || t_supplier_list(i) || t_supplier_site_list(i) );
3376 --FND_FILE.PUT_LINE(FND_FILE.LOG,'delete trx ' || t_trxid1_list(i) || '-' || t_count1(i));
3377 END LOOP;
3378
3379 FORALL i in 1..t_item_list.COUNT
3380 update msc_item_exceptions ex
3381 set ex.exception_count = ex.exception_count - t_count1(i),
3382 ex.last_update_date = sysdate
3383 where ex.plan_id = G_PLAN_ID
3384 and ex.company_id = t_company_list(i)
3385 and ex.company_site_id = t_company_site_list(i)
3386 and ex.inventory_item_id = t_item_list(i)
3387 and ex.exception_group = t_group_list(i)
3388 and ex.exception_type = t_type_list(i)
3389 and ex.version = 0
3390 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3391
3392 --===============================================================
3393 -- archive bucket data with date range
3394 --===============================================================
3395 FORALL i in 1..t_item_list.COUNT
3396 delete msc_x_exception_details
3397 where inventory_item_id = t_item_list(i)
3398 and company_id = t_company_list(i)
3399 and company_site_id = t_company_site_list(i)
3400 and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
3401 and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
3402 and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
3403 and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
3404 and exception_group = t_group_list(i)
3405 and exception_type = t_type_list(i)
3406 and transaction_id1 is null
3407 and transaction_id2 is null
3408 and t_date1_list(i) is not null
3409 and t_date2_list(i) is not null
3410 and date1 = t_date1_list(i)
3411 and date2 = t_date2_list(i)
3412 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3413
3414 FOR i in 1..t_item_list.COUNT LOOP
3415 t_count2.EXTEND;
3416 t_count2(t_count2.COUNT) := SQL%BULK_ROWCOUNT(i);
3417 --FND_FILE.PUT_LINE(FND_FILE.LOG,'delete date ' || t_date1_list(i) || '-' || t_count2(i));
3418 END LOOP;
3419
3420 FORALL i in 1..t_item_list.COUNT
3421 update msc_item_exceptions ex
3422 set ex.exception_count = ex.exception_count - t_count2(i),
3423 ex.last_update_date = sysdate
3424 where ex.plan_id = G_PLAN_ID
3425 and ex.company_id = t_company_list(i)
3426 and ex.company_site_id = t_company_site_list(i)
3427 and ex.inventory_item_id = t_item_list(i)
3428 and ex.exception_group = t_group_list(i)
3429 and ex.exception_type = t_type_list(i)
3430 and ex.version = 0
3431 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3432
3433 --===============================================================
3434 -- archive bucket data without date range
3435 --===============================================================
3436 FORALL i in 1..t_item_list.COUNT
3437 delete msc_x_exception_details
3438 where inventory_item_id = t_item_list(i)
3439 and company_id = t_company_list(i)
3440 and company_site_id = t_company_site_list(i)
3441 and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
3442 and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
3443 and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
3444 and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
3445 and exception_group = t_group_list(i)
3446 and exception_type = t_type_list(i)
3447 and transaction_id1 is null
3448 and transaction_id2 is null
3449 and t_date1_list(i) is null
3450 and t_date2_list(i) is null
3451 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3452
3453 FOR i in 1..t_item_list.COUNT LOOP
3454 t_count3.EXTEND;
3455 t_count3(t_count3.COUNT) := SQL%BULK_ROWCOUNT(i);
3456 --FND_FILE.PUT_LINE(FND_FILE.LOG,'delete date ' || t_type_list(i) || '-' || t_count3(i));
3457 END LOOP;
3458
3459 FORALL i in 1..t_item_list.COUNT
3460 update msc_item_exceptions ex
3461 set ex.exception_count = ex.exception_count - t_count3(i),
3462 ex.last_update_date = sysdate
3463 where ex.plan_id = G_PLAN_ID
3464 and ex.company_id = t_company_list(i)
3465 and ex.company_site_id = t_company_site_list(i)
3466 and ex.inventory_item_id = t_item_list(i)
3467 and ex.exception_group = t_group_list(i)
3468 and ex.exception_type = t_type_list(i)
3469 and ex.version = 0
3470 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3471
3472 --======================================================================
3473 -- archive the potential late order exceptions
3474 --======================================================================
3475 FORALL i in 1..t_item_list.COUNT
3476 delete msc_x_exception_details
3477 where inventory_item_id = t_item_list(i)
3478 and company_id = t_company_list(i)
3479 and company_site_id = t_company_site_list(i)
3480 and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
3481 and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
3482 and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
3483 and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
3484 and exception_group = msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER
3485 and exception_type = msc_x_netting_pkg.G_EXCEP13
3486 and transaction_id1 = t_trxid1_list(i)
3487 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3488
3489 FOR i in 1..t_item_list.COUNT LOOP
3490 t_count1.EXTEND;
3491 t_count1(t_count1.COUNT) := SQL%BULK_ROWCOUNT(i);
3492 --FND_FILE.PUT_LINE(FND_FILE.LOG,'delete trx ex: ' || t_trxid1_list(i) || '-' || t_count1(i) || '-' || t_type_list(i));
3493 --dbms_output.put_line('delete trx ex: ' || t_trxid1_list(i) || '-' || t_count1(i) || '-' || t_type_list(i));
3494 END LOOP;
3495
3496 FORALL i in 1..t_item_list.COUNT
3497 update msc_item_exceptions ex
3498 set ex.exception_count = ex.exception_count - t_count1(i),
3499 ex.last_update_date = sysdate
3500 where ex.plan_id = G_PLAN_ID
3501 and ex.company_id = t_company_list(i)
3502 and ex.company_site_id = t_company_site_list(i)
3503 and ex.inventory_item_id = msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER
3504 and ex.exception_group = msc_x_netting_pkg.G_EXCEP13
3505 and ex.exception_type = t_type_list(i)
3506 and ex.version = 0
3507 and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
3508
3509 --===================================================================
3510 -- clean up the old workflow notification
3511 --=====================================================================
3512 FOR i in t_item_list.FIRST..t_item_list.LAST LOOP
3513
3514 l_item_key := to_char(t_group_list(i)) || '-' ||
3515 to_char(t_type_list(i)) || '-' ||
3516 to_char(t_item_list(i)) || '-' ||
3517 to_char(t_company_list(i)) || '-' ||
3518 to_char(t_company_site_list(i)) || '-' ||
3519 to_char(t_customer_list(i)) || '-' ||
3520 to_char(t_customer_site_list(i)) || '-' ||
3521 to_char(t_supplier_list(i)) || '-' ||
3522 to_char(t_supplier_site_list(i)) || '%';
3523
3524 delete_wf_notification(l_item_type, l_item_key);
3525 END LOOP;
3526 END IF;
3527
3528
3529
3530 --dbms_output.put_line('Done with delete ');
3531
3532
3533 EXCEPTION
3534 when others then
3535 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.archive_exception');
3536 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3537 return;
3538 END archive_exception;
3539
3540 ------------------------------------------------------------------------
3541 --update item desc, customer item name, supplier item name
3542 --------------------------------------------------------------------
3543
3544 PROCEDURE update_item (p_refresh_number in Number) IS
3545
3546 cursor update_item_c1 IS
3547 select distinct publisher_id,
3548 publisher_site_id,
3549 customer_id,
3550 customer_site_id,
3551 inventory_item_id,
3552 item_name,
3553 item_description,
3554 customer_item_name,
3555 supplier_item_name
3556 from msc_sup_dem_entries sd
3557 where plan_id = -1
3558 and publisher_order_type in (3,14)
3559 and last_refresh_number > p_refresh_number
3560 and exists (select 1
3561 from msc_x_exception_details
3562 where plan_id = -1
3563 and inventory_item_id = sd.inventory_item_id
3564 and exception_type in (5,25)
3565 and company_id = sd.publisher_id
3566 and company_site_id = sd.publisher_site_id
3567 and customer_id = sd.customer_id
3568 and customer_site_id = sd.customer_site_id)
3569 union all
3570 select distinct supplier_id,
3571 supplier_site_id,
3572 publisher_id,
3573 publisher_site_id,
3574 inventory_item_id,
3575 item_name,
3576 item_description,
3577 customer_item_name,
3578 supplier_item_name
3579 from msc_sup_dem_entries sd
3580 where plan_id = -1
3581 and publisher_order_type = 2
3582 and last_refresh_number > p_refresh_number
3583 and exists (select 1
3584 from msc_x_exception_details
3585 where plan_id = -1
3586 and inventory_item_id = sd.inventory_item_id
3587 and exception_type in (5,25)
3588 and company_id = sd.supplier_id
3589 and company_site_id = sd.supplier_site_id
3590 and customer_id = sd.publisher_id
3591 and customer_site_id = sd.publisher_site_id);
3592
3593 cursor update_item_c2 IS
3594 select distinct publisher_id,
3595 publisher_site_id,
3596 supplier_id,
3597 supplier_site_id,
3598 inventory_item_id,
3599 item_name,
3600 item_description,
3601 customer_item_name,
3602 supplier_item_name
3603 from msc_sup_dem_entries sd
3604 where plan_id = -1
3605 and publisher_order_type = 2
3606 and last_refresh_number > p_refresh_number
3607 and exists (select 1
3608 from msc_x_exception_details
3609 where plan_id = -1
3610 and inventory_item_id = sd.inventory_item_id
3611 and exception_type in (6,26)
3612 and company_id = sd.publisher_id
3613 and company_site_id = sd.publisher_site_id
3614 and supplier_id = sd.supplier_id
3615 and supplier_site_id = sd.supplier_site_id)
3616 union all
3617 select distinct customer_id,
3618 customer_site_id,
3619 publisher_id,
3620 publisher_site_id,
3621 inventory_item_id,
3622 item_name,
3623 item_description,
3624 customer_item_name,
3625 supplier_item_name
3626 from msc_sup_dem_entries sd
3627 where plan_id = -1
3628 and publisher_order_type in (3,14)
3629 and last_refresh_number > p_refresh_number
3630 and exists (select 1
3631 from msc_x_exception_details
3632 where plan_id = -1
3633 and inventory_item_id = sd.inventory_item_id
3634 and exception_type in (6,26)
3635 and company_id = sd.customer_id
3636 and company_site_id = sd.customer_site_id
3637 and supplier_id = sd.publisher_id
3638 and supplier_site_id = sd.publisher_site_id);
3639
3640 b_publisher_id msc_x_netting_pkg.number_arr;
3641 b_publisher_site_id msc_x_netting_pkg.number_arr;
3642 b_supplier_id msc_x_netting_pkg.number_arr;
3643 b_supplier_site_id msc_x_netting_pkg.number_arr;
3644 b_customer_id msc_x_netting_pkg.number_arr;
3645 b_customer_site_id msc_x_netting_pkg.number_arr;
3646 b_item_id msc_x_netting_pkg.number_arr;
3647 b_item_name msc_x_netting_pkg.itemnameList;
3648 b_item_desc msc_x_netting_pkg.itemdescList;
3649 b_customer_item_name msc_x_netting_pkg.itemnameList;
3650 b_supplier_item_name msc_x_netting_pkg.itemnameList;
3651
3652
3653 BEGIN
3654
3655 open update_item_c1;
3656 fetch update_item_c1 bulk collect into
3657 b_publisher_id,
3658 b_publisher_site_id,
3659 b_customer_id,
3660 b_customer_site_id,
3661 b_item_id,
3662 b_item_name,
3663 b_item_desc,
3664 b_customer_item_name,
3665 b_supplier_item_name;
3666 close update_item_c1;
3667 --dbms_output.put_line('Count of 5-25 ' || b_publisher_id.COUNT);
3668 IF (b_publisher_id is not null and b_publisher_id.COUNT > 0) THEN
3669 FORALL j in 1..b_publisher_id.COUNT
3670
3671 update msc_x_exception_details
3672 set item_name = b_item_name(j),
3673 item_description = b_item_desc(j),
3674 customer_item_name = b_customer_item_name(j),
3675 supplier_item_name = b_supplier_item_name(j)
3676 where plan_id = -1
3677 and company_id = b_publisher_id(j)
3678 and company_site_id = b_publisher_site_id(j)
3679 and customer_id = b_customer_id(j)
3680 and customer_site_id = b_customer_site_id(j)
3681 and inventory_item_id = b_item_id(j)
3682 and exception_type in (5,25);
3683 END IF;
3684
3685 open update_item_c2;
3686 fetch update_item_c2 bulk collect into
3687 b_publisher_id,
3688 b_publisher_site_id,
3689 b_supplier_id,
3690 b_supplier_site_id,
3691 b_item_id,
3692 b_item_name,
3693 b_item_desc,
3694 b_customer_item_name,
3695 b_supplier_item_name;
3696 close update_item_c2;
3697 --dbms_output.put_line('Count ' || b_publisher_id.COUNT);
3698 IF (b_publisher_id is not null and b_publisher_id.COUNT > 0) THEN
3699 FORALL j in 1..b_publisher_id.COUNT
3700
3701 update msc_x_exception_details
3702 set item_name = b_item_name(j),
3703 item_description = b_item_desc(j),
3704 customer_item_name = b_customer_item_name(j),
3705 supplier_item_name = b_supplier_item_name(j)
3706 where plan_id = -1
3707 and company_id = b_publisher_id(j)
3708 and company_site_id = b_publisher_site_id(j)
3709 and supplier_id = b_supplier_id(j)
3710 and supplier_site_id = b_supplier_site_id(j)
3711 and inventory_item_id = b_item_id(j)
3712 and exception_type in (6,26);
3713 END IF;
3714
3715
3716
3717 EXCEPTION
3718 when others then
3719 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.update_item');
3720 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3721 --dbms_output.put_line('Error in update item');
3722 return;
3723 END UPDATE_ITEM;
3724
3725 END MSC_X_NETTING_PKG;
3726