[Home] [Help]
PACKAGE BODY: APPS.MSD_ITEM_RELATIONSHIPS_PKG
Source
1 PACKAGE BODY MSD_ITEM_RELATIONSHIPS_PKG AS
2 /* $Header: msdsupsb.pls 120.2.12010000.1 2008/05/15 08:50:04 vrepaka ship $ */
3
4 /* This procedure to collect Supersession data from source instance to DP staging table. */
5
6 PROCEDURE collect_supersession_data (
7 errbuf out NOCOPY varchar2,
8 retcode out NOCOPY varchar2,
9 p_instance_id in number
10 ) IS
11
12 x_dblink varchar2(128);
13 v_sql_stmt varchar2(4000);
14
15 BEGIN
16
17 retcode :=0;
18
19 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
20 if (retcode = -1) then
21 retcode :=-1;
22 errbuf := 'Error while getting db_link for Item Supersession Collection';
23 --dbms_output.put_line('Error while getting db_link');
24 return;
25 end if;
26
27 /* Delete records by instance before collecting supersession data from source instance */
28
29 delete from msd_st_item_relationships
30 where instance_id = p_instance_id;
31
32 /* Single step collection internally is two step hence colect always inserts it in staging table */
33 /* Net Change is not needed for this entity */
34
35 v_sql_stmt:= ' insert into msd_st_item_relationships ( '||
36 'instance_id, '||
37 'inventory_item_id, '||
38 'inventory_item, '||
39 'related_item_id, '||
40 'related_item, '||
41 'relationship_type_id, '||
42 'creation_date, '||
43 'created_by, '||
44 'last_update_date, '||
45 'last_updated_by, '||
46 'last_update_login, '||
47 'start_date, '|| /*--Bug#4707819--*/
48 'end_date) '|| /*--Bug#4707819--*/
49 'SELECT ''' || p_instance_id || ''','||
50 'inventory_item_id,'||
51 'inventory_item,'||
52 'related_item_id, '||
53 'related_item, '||
54 'relationship_type_id, '||
55 'sysdate, ' ||
56 FND_GLOBAL.USER_ID || ', ' ||
57 'sysdate, ' ||
58 FND_GLOBAL.USER_ID || ', ' ||
59 FND_GLOBAL.USER_ID || ' ,' ||
60 'start_date, '|| /*--Bug#4707819--*/
61 'end_date '|| /*--Bug#4707819--*/
62 'FROM ' ||
63 'msd_sr_item_supersession_v' || x_dblink;
64
65 EXECUTE IMMEDIATE v_sql_stmt;
66
67 -- COMMIT;
68
69 EXCEPTION
70 when others then
71 errbuf := substr(SQLERRM,1,150);
72 retcode := -1;
73 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
74 rollback;
75
76 END collect_supersession_data;
77
78
79 /* This procedure to pull Supersession data from staging table to fact table */
80
81 PROCEDURE pull_supersession_data (
82 errbuf out NOCOPY varchar2,
83 retcode out NOCOPY varchar2
84 ) IS
85
86 l_event_name varchar2(240);
87 l_instance_id number;
88
89 CURSOR auto_update_events_c1 IS
90 SELECT event_name
91 FROM msd_events
92 where auto_update_ss_flag = 'Y'
93 and event_type = 3
94 and introduction_type = 2;
95
96 CURSOR item_instance_c2 IS
97 select distinct instance_id
98 from msd_st_item_relationships;
99
100 BEGIN
101
102 OPEN item_instance_c2;
103 LOOP
104 l_instance_id := NULL;
105
106 FETCH item_instance_c2 INTO l_instance_id;
107 EXIT WHEN item_instance_c2%NOTFOUND;
108
109 /* Delete records in fact table by instance before pull supersession data from staging table */
110
111 delete from msd_item_relationships
112 where instance_id = l_instance_id;
113
114 /* Insert Supersession data into DP fact table */
115
116 insert into msd_item_relationships (
117 instance_id,
118 inventory_item_id,
119 inventory_item,
120 related_item_id,
121 related_item,
122 relationship_type_id,
123 creation_date,
124 created_by,
125 last_update_date,
126 last_updated_by,
127 last_update_login,
128 start_date, /*--Bug#4707819--*/
129 end_date ) /*--Bug#4707819--*/
130 SELECT instance_id,
131 inventory_item_id,
132 inventory_item,
133 related_item_id,
134 related_item,
135 relationship_type_id,
136 sysdate,
137 FND_GLOBAL.USER_ID,
138 sysdate,
139 FND_GLOBAL.USER_ID,
140 FND_GLOBAL.USER_ID,
141 start_date, /*--Bug#4707819--*/
142 end_date /*--Bug#4707819--*/
143 FROM msd_st_item_relationships
144 WHERE instance_id = l_instance_id;
145
146 commit;
147
148 OPEN auto_update_events_c1;
149 LOOP
150 l_event_name := NULL;
151
152 /* Get auto update events list by checking auto_update_ss_flag column in MSD_EVENTS table */
153
154 FETCH auto_update_events_c1 INTO l_event_name;
155 EXIT WHEN auto_update_events_c1%NOTFOUND;
156
157 fnd_file.put_line(fnd_file.log, 'Auto Refreshing Event: '|| l_event_name || ' Instance Id: '|| l_instance_id);
158
159 msd_item_relationships_pkg.create_supersession_events (
160 errbuf => errbuf,
161 retcode => retcode,
162 p_instance_id => l_instance_id,
163 p_event_name => l_event_name );
164
165 END LOOP;
166 CLOSE auto_update_events_c1;
167
168 END LOOP;
169 CLOSE item_instance_c2;
170
171 /* new */
172 commit;
173
174 EXCEPTION
175 when others then
176 errbuf := substr(SQLERRM,1,150);
177 retcode := -1;
178 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
179 rollback;
180
181 END pull_supersession_data;
182
183
184 /* This procedure to delete events data before refreshing */
185
186 PROCEDURE delete_events_data (
187 errbuf out NOCOPY varchar2,
188 retcode out NOCOPY varchar2,
189 p_instance_id in number,
190 p_event_id in number
191 ) IS
192
193 BEGIN
194
195 delete from msd_evt_product_details
196 where event_id = p_event_id
197 and instance = p_instance_id;
198
199 delete from msd_evt_prod_relationships
200 where event_id = p_event_id
201 and instance = p_instance_id;
202
203 delete from msd_event_products
204 where event_id = p_event_id
205 and instance = p_instance_id;
206
207 -- commit;
208
209 EXCEPTION
210 when others then
211 errbuf := substr(SQLERRM,1,150);
212 retcode := -1;
213 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
214 rollback;
215
216
217 END delete_events_data;
218
219 /* This procedure will insert supersession new item information into MSD_EVENTS_PRODUCTS */
220
221 PROCEDURE insert_event_products (
222 errbuf out NOCOPY varchar2,
223 retcode out NOCOPY varchar2,
224 p_instance_id in number,
225 l_event_id in number,
226 l_seq_id in number,
227 l_level_id in number,
228 l_inventory_item in varchar2,
229 l_inventory_item_id in varchar2,
230 l_start_time in date, /*--Bug#4707819--*/
231 l_end_time in date /*--Bug#4707819--*/
232 ) IS
233
234 BEGIN
235
236 INSERT INTO msd_event_products (
237 instance,
238 event_id,
239 seq_id,
240 product_lvl_id,
241 product_lvl_val,
242 sr_product_lvl_pk,
243 start_time, /*--Bug#4707819--*/
244 end_time, /*--Bug#4707819--*/
245 creation_date,
246 created_by,
247 last_update_date,
248 last_updated_by,
249 last_update_login )
250 VALUES (p_instance_id,
251 l_event_id,
252 l_seq_id,
253 l_level_id,
254 l_inventory_item,
255 l_inventory_item_id,
256 l_start_time, /*--Bug#4707819--*/
257 l_end_time, /*--Bug#4707819--*/
258 sysdate,
259 FND_GLOBAL.USER_ID,
260 sysdate,
261 FND_GLOBAL.USER_ID,
262 FND_GLOBAL.USER_ID );
263
264 -- commit;
265
266 EXCEPTION
267 when others then
268 errbuf := substr(SQLERRM,1,150);
269 retcode := -1;
270 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
271 rollback;
272
273 END insert_event_products;
274
275 /* This procedure will insert supersession related item's relation information into msd_evt_prod_relationships table */
276
277 PROCEDURE insert_evt_prod_relationships (
278 errbuf out NOCOPY varchar2,
279 retcode out NOCOPY varchar2,
280 p_instance_id in number,
281 l_event_id in number,
282 l_seq_id in number,
283 l_relation_id in number,
284 l_level_id in number,
285 l_related_item in varchar2,
286 l_related_item_id in varchar2,
287 l_qty_mod_type in number,
288 l_qty_mod_factor in number,
289 l_npi_prd_relshp in number,
290 l_start_time in date, /*--Bug#4707819--*/
291 l_end_time in date /*--Bug#4707819--*/
292 ) IS
293
294 BEGIN
295
296 INSERT INTO msd_evt_prod_relationships (
297 instance,
298 event_id,
299 seq_id,
300 relation_id,
301 product_lvl_id,
302 product_lvl_val,
303 sr_product_lvl_pk,
304 lag,
305 qty_modification_type,
306 qty_modification_factor,
307 npi_prod_relationship,
308 start_time, /*--Bug#4707819--*/
309 end_time, /*--Bug#4707819--*/
310 creation_date,
311 created_by,
312 last_update_date,
313 last_updated_by,
314 last_update_login )
315 VALUES (p_instance_id,
316 l_event_id,
317 l_seq_id,
318 l_relation_id,
319 l_level_id,
320 l_related_item,
321 l_related_item_id,
322 0,
323 l_qty_mod_type,
324 l_qty_mod_factor,
325 l_npi_prd_relshp,
326 l_start_time, /*--Bug#4707819--*/
327 l_end_time, /*--Bug#4707819--*/
328 sysdate,
329 FND_GLOBAL.USER_ID,
330 sysdate,
331 FND_GLOBAL.USER_ID,
332 FND_GLOBAL.USER_ID );
333
334 -- commit;
335
336 EXCEPTION
337 when others then
338 errbuf := substr(SQLERRM,1,150);
339 retcode := -1;
340 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
341 rollback;
342
343 END insert_evt_prod_relationships;
344
345
346 /* This procedure will insert supersession related item's detail information into msd__evt_product_details table */
347
348 PROCEDURE insert_evt_product_details (
349 errbuf out NOCOPY varchar2,
350 retcode out NOCOPY varchar2,
351 p_instance_id in number,
352 l_event_id in number,
353 l_seq_id in number,
354 l_detail_id in number,
355 l_relation_id in number,
356 l_level_id in number,
357 l_related_item in varchar2,
358 l_related_item_id in varchar2,
359 l_qty_mod_type in number,
360 l_qty_mod_factor in number
361 ) IS
362
363 BEGIN
364
365 INSERT INTO msd_evt_product_details (
366 instance,
367 event_id,
368 seq_id,
369 detail_id,
370 relation_id,
371 product_lvl_id,
372 product_lvl_val,
373 sr_product_lvl_pk,
374 time_lvl_val_from,
375 time_lvl_val_to,
376 qty_modification_type,
377 qty_modification_factor,
378 creation_date,
379 created_by,
380 last_update_date,
381 last_updated_by,
382 last_update_login )
383 VALUES (p_instance_id,
384 l_event_id,
385 l_seq_id,
386 l_detail_id,
387 l_relation_id,
388 l_level_id,
389 l_related_item,
390 l_related_item_id,
391 sysdate,
392 sysdate,
393 l_qty_mod_type,
394 l_qty_mod_factor,
395 sysdate,
396 FND_GLOBAL.USER_ID,
397 sysdate,
398 FND_GLOBAL.USER_ID,
399 FND_GLOBAL.USER_ID );
400
401 -- commit;
402
403 EXCEPTION
404 when others then
405 errbuf := substr(SQLERRM,1,150);
406 retcode := -1;
407 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
408 rollback;
409
410 END insert_evt_product_details;
411
412
413 /* this procedure will update supersession events based on item relationship information */
414
415 PROCEDURE update_supersession_events (
416 errbuf out NOCOPY varchar2,
417 retcode out NOCOPY varchar2,
418 p_event_name in varchar2
419 ) IS
420
421 l_instance_id number;
422
423 CURSOR item_instance_c1 IS
424 select distinct instance_id
425 from msd_item_relationships;
426
427 BEGIN
428
429 OPEN item_instance_c1;
430 LOOP
431 l_instance_id := NULL;
432
433 FETCH item_instance_c1 INTO l_instance_id;
434 EXIT WHEN item_instance_c1%NOTFOUND;
435
436 msd_item_relationships_pkg.create_supersession_events (
437 errbuf => errbuf,
438 retcode => retcode,
439 p_instance_id => l_instance_id,
440 p_event_name => p_event_name );
441
442 END LOOP;
443 CLOSE item_instance_c1;
444
445 EXCEPTION
446 when others then
447 errbuf := substr(SQLERRM,1,150);
448 retcode := -1;
449 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
450
451 END update_supersession_events;
452
453
454 /* This procedure to create events based on supersession data */
455
456 PROCEDURE create_supersession_events (
457 errbuf out NOCOPY varchar2,
458 retcode out NOCOPY varchar2,
459 p_instance_id in number,
460 p_event_name in varchar2
461 ) IS
462
463 l_event_id number;
464 l_seq_id number;
465 l_relation_id number;
466 l_detail_id number;
467 l_qty_mod_type varchar2(1);
468 l_qty_mod_factor number;
469 l_npi_prd_relshp varchar2(1);
470 l_inventory_item_id varchar2(240);
471 l_inventory_item varchar2(240);
472 l_related_item_id varchar2(240);
473 l_related_item varchar2(240);
474 l_count number;
475 l_level_id number;
476 l_start_time date; /*--Bug#4707819--*/
477 l_end_time date; /*--Bug#4707819--*/
478
479
480 /*
481 CURSOR new_item_c1 IS
482 SELECT lvl.sr_level_pk,
483 lvl.level_value
484 FROM msd_item_relationships rel,
485 msd_level_values lvl
486 WHERE lvl.instance = rel.instance_id
487 AND lvl.sr_level_pk = rel.inventory_item_id
488 AND lvl.level_id = 1
489 AND rel.inventory_item_id not in (SELECT re2.related_item_id
490 FROM msd_item_relationships re2
491 WHERE re2.instance_id = to_char(p_instance_id))
492 AND rel.instance_id = to_char(p_instance_id);
493 */
494
495 CURSOR new_item_c1 IS
496 SELECT lvl.sr_level_pk,
497 lvl.level_value, rel.start_date, rel.end_date /*--Bug#4707819--*/
498 FROM msd_item_relationships rel,
499 msd_level_values lvl
500 WHERE lvl.instance = rel.instance_id
501 AND lvl.sr_level_pk = rel.inventory_item_id
502 AND lvl.level_id = 1
503 AND rel.instance_id = to_char(p_instance_id)
504 MINUS
505 SELECT re2.related_item_id,
506 re2.related_item, re2.start_date, re2.end_date /*--Bug#4707819--*/
507 FROM msd_item_relationships re2
508 WHERE re2.instance_id = to_char(p_instance_id);
509
510
511 CURSOR superseded_item_c1(l_inventory_item_id in number) IS
512 SELECT related_item_id,
513 related_item
514 FROM msd_item_relationships
515 START WITH inventory_item_id = l_inventory_item_id
516 AND instance_id = p_instance_id
517 CONNECT BY PRIOR related_item_id = inventory_item_id
518 AND instance_id = p_instance_id;
519
520 CURSOR chk_item_exists (p_related_item_id in varchar2) IS
521 SELECT count(*)
522 FROM msd_level_values
523 WHERE instance = p_instance_id
524 AND sr_level_pk = p_related_item_id
525 AND level_id = 1;
526
527 BEGIN
528
529 select event_id
530 into l_event_id
531 from msd_events
532 where event_name = p_event_name;
533
534 /* Delete refresh events before updating with supersession item relationships */
535
536 msd_item_relationships_pkg.delete_events_data (
537 errbuf => errbuf,
538 retcode => retcode,
539 p_instance_id => p_instance_id,
540 p_event_id => l_event_id );
541
542 l_level_id := 1;
543
544 OPEN new_item_c1;
545 LOOP
546
547 l_inventory_item_id := NULL;
548 l_inventory_item := NULL;
549
550 FETCH new_item_c1 INTO l_inventory_item_id, l_inventory_item, l_start_time, l_end_time; /*--Bug#4707819--*/
551 EXIT WHEN new_item_c1%NOTFOUND;
552
553 l_seq_id := null;
554
555 SELECT msd_event_products_s.nextval
556 INTO l_seq_id
557 FROM dual;
558
559 /* Insert new items into MSD_EVENT_PRODUCTS table */
560
561 msd_item_relationships_pkg.insert_event_products(
562 errbuf => errbuf,
563 retcode => retcode,
564 p_instance_id => p_instance_id,
565 l_event_id => l_event_id,
566 l_seq_id => l_seq_id,
567 l_level_id => l_level_id,
568 l_inventory_item => l_inventory_item,
569 l_inventory_item_id => l_inventory_item_id,
570 l_start_time=>l_start_time, /*--Bug#4707819--*/
571 l_end_time => l_end_time ); /*--Bug#4707819--*/
572
573 begin
574
575 OPEN superseded_item_c1 (l_inventory_item_id);
576 LOOP
577
578 l_related_item_id := NULL;
579 l_related_item := NULL;
580
581 begin
582
583 FETCH superseded_item_c1 INTO l_related_item_id, l_related_item;
584 EXIT WHEN superseded_item_c1%NOTFOUND;
585
586 l_count := 0;
587 /*
588 SELECT 1
589 INTO l_count
590 FROM msd_level_values
591 WHERE instance = p_instance_id
592 AND sr_level_pk = l_related_item_id
593 AND level_id = 1;
594 */
595 OPEN chk_item_exists(l_related_item_id);
596 FETCH chk_item_exists INTO l_count;
597 CLOSE chk_item_exists;
598
599 IF l_count <> 0 THEN
600
601 l_relation_id := NULL;
602
603 select msd_evt_prod_relationships_s.nextval
604 into l_relation_id
605 from dual;
606
607 l_qty_mod_type := 2;
608 l_qty_mod_factor := 0;
609 l_npi_prd_relshp := 1;
610
611 /* Insert base items into MSD_EVT_PROD_RELATIONSHIPS table */
612 msd_item_relationships_pkg.insert_evt_prod_relationships(
613 errbuf => errbuf,
614 retcode => retcode,
615 p_instance_id => p_instance_id,
616 l_event_id => l_event_id,
617 l_seq_id => l_seq_id,
618 l_relation_id => l_relation_id,
619 l_level_id => l_level_id,
620 l_related_item => l_related_item,
621 l_related_item_id => l_related_item_id,
622 l_qty_mod_type => l_qty_mod_type,
623 l_qty_mod_factor => l_qty_mod_factor,
624 l_npi_prd_relshp => l_npi_prd_relshp,
625 l_start_time=>null, /*--Bug#4707819--*/
626 l_end_time=>null ); /*--Bug#4707819--*/
627
628 l_relation_id := NULL;
629
630 select msd_evt_prod_relationships_s.nextval
631 into l_relation_id
632 from dual;
633
634 select msd_evt_product_details_s.nextval
635 into l_detail_id
636 from dual;
637
638 l_qty_mod_type := 2;
639 l_qty_mod_factor := 100;
640 l_npi_prd_relshp := 2;
641
642 /* Insert cannabilized items into MSD_EVT_PROD_RELATIONSHIPS table */
643 msd_item_relationships_pkg.insert_evt_prod_relationships(
644 errbuf => errbuf,
645 retcode => retcode,
646 p_instance_id => p_instance_id,
647 l_event_id => l_event_id,
648 l_seq_id => l_seq_id,
649 l_relation_id => l_relation_id,
650 l_level_id => l_level_id,
651 l_related_item => l_related_item,
652 l_related_item_id => l_related_item_id,
653 l_qty_mod_type => l_qty_mod_type,
654 l_qty_mod_factor => l_qty_mod_factor,
655 l_npi_prd_relshp => l_npi_prd_relshp,
656 l_start_time => l_start_time, /*--Bug#4707819--*/
657 l_end_time =>l_end_time ); /*--Bug#4707819--*/
658
659 /* Insert cannabilized items details into MSD_EVT_PRODUCT_DETAILS table */
660 msd_item_relationships_pkg.insert_evt_product_details(
661 errbuf => errbuf,
662 retcode => retcode,
663 p_instance_id => p_instance_id,
664 l_event_id => l_event_id,
665 l_seq_id => l_seq_id,
666 l_detail_id => l_detail_id,
667 l_relation_id => l_relation_id,
668 l_level_id => l_level_id,
669 l_related_item => l_related_item,
670 l_related_item_id => l_related_item_id,
671 l_qty_mod_type => l_qty_mod_type,
672 l_qty_mod_factor => l_qty_mod_factor );
673
674 END IF;
675
676 EXCEPTION
677 when others then
678 errbuf := substr(SQLERRM,1,150);
679 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
680 rollback;
681 /* Exit the process after an error happens */
682 exit;
683 end;
684
685 END LOOP;
686 CLOSE superseded_item_c1;
687
688 /* This exception for multiple looping */
689 EXCEPTION
690 when others then
691 fnd_file.put_line(fnd_file.log, 'Item relationship is in loop: '|| l_inventory_item_id);
692
693 end;
694
695 COMMIT;
696
697 END LOOP;
698 CLOSE new_item_c1;
699
700 EXCEPTION
701 when others then
702 errbuf := substr(SQLERRM,1,150);
703 retcode := -1;
704 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
705 rollback;
706
707 END create_supersession_events;
708
709 END MSD_ITEM_RELATIONSHIPS_PKG;