[Home] [Help]
PACKAGE BODY: APPS.MSD_TRANSLATE_LEVEL_VALUES
Source
1 PACKAGE BODY MSD_TRANSLATE_LEVEL_VALUES AS
2 /* $Header: msdtlvlb.pls 120.6 2011/12/07 11:45:07 mpmurali ship $ */
3
4 /* Debug */
5 C_DEBUG Constant varchar2(1) := 'Y';
6
7
8
9 /* Private API */
10 procedure log_debug( pBUFF in varchar2)
11 is
12 begin
13
14 if C_MSC_DEBUG = 'Y' then
15 fnd_file.put_line( fnd_file.log, pBUFF);
16 else
17 null;
18 --dbms_output.put_line( pBUFF);
19 end if;
20
21 end log_debug;
22
23 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
24 IS
25 BEGIN
26
27 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
28
29 END LOG_MESSAGE;
30
31 Procedure ins( a in varchar2) is
32 Begin
33 /* Debugging Code
34 insert into msd_test values ('VM' || to_char(sysdate, 'hh24:mi') || ' ' || a);
35 commit;
36 */
37 null;
38 End;
39
40
41
42 PROCEDURE CREATE_ITEM_LIST_PRICE(
43 errbuf OUT NOCOPY VARCHAR2,
44 retcode OUT NOCOPY VARCHAR2,
45 p_source_table IN VARCHAR2,
46 p_dest_table IN VARCHAR2,
47 p_instance_id IN VARCHAR2,
48 p_level_id IN NUMBER,
49 p_seq_num IN NUMBER,
50 p_delete_flag IN VARCHAR2);
51
52 PROCEDURE DELETED_ITEM_LIST_PRICE(
53 errbuf OUT NOCOPY VARCHAR2,
54 retcode OUT NOCOPY VARCHAR2,
55 p_instance_id IN VARCHAR2,
56 p_seq_num IN NUMBER);
57
58 PROCEDURE UPDATE_ITEM_LIST_PRICE(
59 errbuf OUT NOCOPY VARCHAR2,
60 retcode OUT NOCOPY VARCHAR2,
61 p_instance_id IN VARCHAR2,
62 p_seq_num IN NUMBER);
63
64
65
66 PROCEDURE PROCESS_LEVEL_VALUE_PER_ROW(
67 errbuf OUT NOCOPY VARCHAR2,
68 retcode OUT NOCOPY VARCHAR2,
69 p_instance_id IN VARCHAR2,
70 p_level_id IN NUMBER,
71 p_seq_num IN NUMBER);
72
73
74 PROCEDURE PROCESS_LEVEL_ASSOCIATION(
75 errbuf OUT NOCOPY VARCHAR2,
76 retcode OUT NOCOPY VARCHAR2,
77 p_instance_id IN VARCHAR2,
78 p_level_id IN NUMBER,
79 p_parent_level_id IN NUMBER,
80 p_seq_num IN NUMBER);
81
82
83 PROCEDURE PROCESS_TOP_LEVEL_VALUES (
84 errbuf OUT NOCOPY VARCHAR2,
85 retcode OUT NOCOPY VARCHAR2,
86 p_source_table IN VARCHAR2,
87 p_dest_table IN VARCHAR2,
88 p_instance_id IN VARCHAR2,
89 p_parent_level_id IN NUMBER,
90 p_parent_value_column IN VARCHAR2,
91 p_parent_value_pk_column IN VARCHAR2,
92 p_parent_value_desc_column IN VARCHAR2,
93 p_seq_num IN NUMBER,
94 p_delete_flag IN VARCHAR2);
95
96
97
98 PROCEDURE CREATE_DELETED_LEVEL_ASSOCI(
99 errbuf OUT NOCOPY VARCHAR2,
100 retcode OUT NOCOPY VARCHAR2,
101 p_instance_id IN VARCHAR2,
102 p_level_id IN NUMBER,
103 p_parent_level_id IN NUMBER,
104 p_seq_num IN NUMBER);
105
106 PROCEDURE CREATE_DELETED_LEVEL_VALUES(
107 errbuf OUT NOCOPY VARCHAR2,
108 retcode OUT NOCOPY VARCHAR2,
109 p_instance_id IN VARCHAR2,
110 p_level_id IN NUMBER,
111 p_seq_num IN NUMBER );
112
113 /* Populates the Working and Non-working days for
114 * each specific Organization. This is called for
115 * the Organization level only.
116 *
117 *
118 */
119
120 PROCEDURE POP_ORG_CAL_ASSOCIATIONS (
121 errbuf OUT NOCOPY VARCHAR2,
122 retcode OUT NOCOPY VARCHAR2,
123 p_source_table IN VARCHAR2,
124 p_dest_table IN VARCHAR2,
125 p_instance_id IN NUMBER);
126
127
128 /* Populates the relationships between levels and organizations.
129 */
130
131 PROCEDURE POP_ORG_LVL_ASSOCIATIONS (
132 errbuf OUT NOCOPY VARCHAR2,
133 retcode OUT NOCOPY VARCHAR2,
134 p_lvl_id IN NUMBER,
135 p_source_table IN VARCHAR2,
136 p_org_relationship_view IN VARCHAR2,
137 p_dest_table IN VARCHAR2,
138 p_instance_id IN NUMBER,
139 p_delete_flag IN VARCHAR2);
140
141 /* Stores the maximum refresh number for level values collections
142 */
143
144 PROCEDURE POP_MAX_SEQ_NUM (
145 errbuf OUT NOCOPY VARCHAR2,
146 retcode OUT NOCOPY VARCHAR2,
147 p_seq_num IN NUMBER);
148
149
150
151 Procedure show_line(p_sql in varchar2);
152
153
154 Procedure debug_line(p_sql in varchar2);
155
156
157
158 /* Public API */
159 procedure translate_level_parent_values(
160 errbuf OUT NOCOPY VARCHAR2,
161 retcode OUT NOCOPY VARCHAR2,
162 p_source_table IN VARCHAR2,
163 p_dest_table IN VARCHAR2,
164 p_instance_id IN NUMBER,
165 p_level_id IN NUMBER,
166 p_level_value_column IN VARCHAR2,
167 p_level_value_pk_column IN VARCHAR2,
168 p_level_value_desc_column IN VARCHAR2,
169 p_parent_level_id IN NUMBER,
170 p_parent_value_column IN VARCHAR2,
171 p_parent_value_pk_column IN VARCHAR2,
172 p_parent_value_desc_column IN VARCHAR2,
173 p_update_lvl_table IN NUMBER,
174 p_delete_flag IN VARCHAR2,
175 p_seq_num IN NUMBER ) IS
176 --,p_launched_from IN NUMBER ) IS --jarorad
177
178 v_instance_id varchar2(40);
179 v_retcode number;
180 v_sql_stmt varchar2(4000);
181 v_sql_stmt1 varchar2(1000) :=to_char(NULL); --jarorad
182
183 v_sql_stmt3 varchar2(2000); --jarorad
184 v_sql_stmt4 varchar2(2000); --jarorad
185
186 v_count1 number :=0; --jarorad
187 v_count2 number :=0; --jarorad
188 v_dest_ass_table varchar2(240) ;
189 v_sr_ass_table varchar2(240) ;
190 v_parent_lvl_type varchar2(3);
191 v_lvl_type varchar2(1);
192 v_dim_code varchar2(3);
193 v_org_view varchar2(30);
194 v_up number;
195 x_dblink VARCHAR2(128);
196
197 v_table_name varchar2(100);
198 v_view_exist number;
199 v_select_sql varchar2(300);
200
201 Begin
202
203 log_debug('In procedure TRANSLATE_LEVEL_PARENT_VALUE');
204 debug_line('In translate_level_parent_value');
205
206 v_view_exist := 1;
207 v_up := p_update_lvl_table;
208 ins('In Translate' || p_level_id || ' ' || p_parent_level_id);
209 debug_line('In Translate LEVEL_ID :' || p_level_id || ' PARENT_LEVEL_ID :' || p_parent_level_id);
210
211 retcode :=0;
212
213 Savepoint Before_Delete ;
214
215 /* Beginning of IF 1 */
216 IF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE) THEN
217
218 v_dest_ass_table := MSD_COMMON_UTILITIES.LEVEL_ASSOC_FACT_TABLE ;
219 v_sr_ass_table := MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE ;
220
221 /* First time to process this level_id */
222 IF (p_update_lvl_table = 1) THEN
223 /* Insert deleted level values into deleted_level_value table and delete it
224 from the fact level value table */
225 /* For Incremental Level Value Collection, p_delete_flag = 'N'
226 So, we don't delete existing level values */
227 IF (p_delete_flag = 'Y') THEN
228 CREATE_DELETED_LEVEL_VALUES( errbuf,
229 retcode,
230 p_instance_id,
231 p_level_id,
232 p_seq_num);
233 END IF;
234
235 /* Process row by row from staging level values table */
236 PROCESS_LEVEL_VALUE_PER_ROW( errbuf,
237 retcode,
238 p_instance_id,
239 p_level_id,
240 p_seq_num);
241 END IF;
242
243 /* Insert deleted level associations into deleted level association table
244 and delete it from the existing fact level associations table */
245 /* For Incremental Level Value Collection, p_delete_flag = 'N'
246 So, we don't delete existing level values */
247 IF (p_delete_flag = 'Y') THEN
248 CREATE_DELETED_LEVEL_ASSOCI( errbuf,
249 retcode,
250 p_instance_id,
251 p_level_id,
252 p_parent_level_id,
253 p_seq_num);
254 END IF;
255
256 /* Process from staging level associations table */
257 PROCESS_LEVEL_ASSOCIATION(
258 errbuf,
259 retcode,
260 p_instance_id,
261 p_level_id,
262 p_parent_level_id,
263 p_seq_num);
264
265 /* ELSE for IF 1. COLLECTION */
266 ELSIF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) THEN
267
268 log_debug(' Entering Collect Level values Condition');
269
270 v_dest_ass_table := MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE;
271
272 /* Delete Staging Table only if delete flag = Yes */
273 IF (p_delete_flag = 'Y') THEN
274 /* First time to process this level_id */
275 IF (p_update_lvl_table = 1) THEN
276 DELETE FROM msd_st_level_values
277 WHERE instance = p_instance_id AND level_id = p_level_id;
278 END IF;
279
280 DELETE FROM msd_st_level_associations
281 WHERE instance = p_instance_id AND
282 level_id = p_level_id AND parent_level_id = p_parent_level_id;
283 END IF;
284
285
286 /* Logic to figure out if the source view contains the relevant columns required for
287 Sales and Operation Planning */
288
289 /* Get the x_dblink from p_instance_id */
290 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
291
292 /* Check for errors in getting the db link */
293
294 if (retcode = -1) then
295
296 retcode :=-1;
297 errbuf := 'Error while getting db_link';
298 return;
299
300 end if;
301
302 select substr(p_source_table,1,decode(instr(p_source_table,'@')-1,-1,length(p_source_table),instr(p_source_table,'@')-1))
303 INTO v_table_name from dual;
304
305
306 BEGIN
307 v_count1 :=0;
308
309 v_sql_stmt3 := ' select count(*) '
310 ||' from sys.all_tab_columns'|| x_dblink ||
311 ' where table_name = '''||v_table_name||''' and column_name = ''SYSTEM_ATTRIBUTE1'' ';
312
313 log_debug('statement before exec :'||v_sql_stmt3);
314
315 EXECUTE IMMEDIATE v_sql_stmt3 INTO v_count1;
316
317
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 v_count1 := 0;
321 WHEN OTHERS THEN
322 v_count1 := 0;
323 END;
324
325
326 BEGIN
327 v_count2 :=0;
328
329
330 v_sql_stmt4 := ' select count(*) '
331 ||' from sys.all_tab_columns'|| x_dblink ||
332 ' where table_name = '''||v_table_name||''' and column_name = ''DP_ENABLED_FLAG'' ';
333
334 log_debug('statement before exec :'||v_sql_stmt4);
335
336 EXECUTE IMMEDIATE v_sql_stmt4 INTO v_count2;
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 v_count2 := 0;
341 WHEN OTHERS THEN
342 v_count2 := 0;
343 END;
344
345
346
347
348
349 log_debug('Source table Name :'||p_source_table);
350 log_debug('value of v_count1 :'||v_count1);
351 log_debug('value of v_count2 :'||v_count2);
352
353 /*
354 --What should happen if TPClass(GEO), TPZone(GEO) and PF(PRD) hierarchies are customized?
355
356 -- Do we need to make this code generic?
357 -- Use code which based on the column existence(dp_enabled_flag etc) in the p_source_table
358 -- and then attach this extra statement.
359
360 IF ( ((p_level_id = 1) AND (p_parent_level_id = 3)) --jarorad
361 OR
362 ((p_level_id = 11) AND (p_parent_level_id = 15))
363 OR
364 ((p_level_id = 15) AND (p_parent_level_id = 16))
365 OR
366 ((p_level_id = 16) AND (p_parent_level_id = 30))
367 OR
368 ((p_level_id = 11) AND (p_parent_level_id = 41))
369 OR
370 ((p_level_id = 41) AND (p_parent_level_id = 42))
371 OR
372 ((p_level_id = 42) AND (p_parent_level_id = 30))
373
374 ) THEN
375
376 v_sql_stmt1 := ' system_attribute1, ' || --jarorad
377 'system_attribute2, ' ||
378 'dp_enabled_flag, ';
379
380 END IF; --jarorad
381 */
382
383
384 IF v_count1 > 0 THEN
385 log_debug('setting the v_sql_stmt1');
386
387 v_sql_stmt1 := ' system_attribute1, ' || --jarorad
388 'system_attribute2, ' ;
389
390 END IF;
391
392 IF v_count2 > 0 THEN
393 log_debug('changing the v_sql_stmt1');
394
395 v_sql_stmt1 := v_sql_stmt1||' dp_enabled_flag, ';
396
397 END IF;
398
399 log_debug('The final value for v_sql_stmt1 is :'||v_sql_stmt1);
400
401 begin
402 v_view_exist := 1;
403 v_select_sql := 'select * from ' || p_source_table || ' where rownum = 1 ';
404 execute immediate v_select_sql;
405 exception
406 WHEN others THEN
407 v_view_exist := 0;
408 retcode := 1;
409 fnd_file.put_line(fnd_file.log,'View ' || p_source_table || ' does not exist on the source');
410 end;
411
412 /* Insert Level Values into staging table */
413 v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( ' ||
414 'instance, ' ||
415 'level_id, ' ||
416 'level_value, ' ||
417 'sr_level_pk, ' ||
418 'level_value_desc, ' ||
419 'attribute1, ' ||
420 'attribute2, ' ||
421 'attribute3, ' ||
422 'attribute4, ' ||
423 'attribute5, ' ||
424 v_sql_stmt1 || --jarorad
425 'last_update_date, ' ||
426 'last_updated_by, ' ||
427 'creation_date, ' ||
428 'created_by ) ' ||
429 'select ''' ||
430 p_instance_id ||''', ' ||
431 p_level_id || ', ' ||
432 p_level_value_column||', ' ||
433 p_level_value_pk_column||', ' ||
434 p_level_value_desc_column||', ' ||
435 'attribute1, ' ||
436 'attribute2, ' ||
437 'attribute3, ' ||
438 'attribute4, ' ||
439 'attribute5, ' ||
440 v_sql_stmt1 || --jarorad
441 'sysdate, ' ||
442 FND_GLOBAL.USER_ID || ', ' ||
443 'sysdate, ' ||
444 FND_GLOBAL.USER_ID || ' ' ||
445 'from ' ||
446 p_source_table ;
447
448 /* Following filter causes a performance hit. We'll colect duplicates
449 into staging. At the end these will be deleted by delete_duplicate in the
450 collection program
451
452 if (p_update_lvl_table = 0) then
453 v_sql_stmt := v_sql_stmt ||
454 ' where ' || p_level_value_pk_column || ' not in ' ||
455 '(select sr_level_pk from ' || p_dest_table ||
456 ' where instance = ' || p_instance_id ||
457 ' and level_id = ' || p_level_id || ')';
458 end if;
459 */
460 if (v_view_exist = 1) then
461 ins(v_sql_stmt);
462 debug_line(v_sql_stmt);
463 EXECUTE IMMEDIATE v_sql_stmt;
464 end if;
465
466 /* Insert Level Associations into staging table */
467 v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || v_dest_ass_table || ' ( ' ||
468 'instance, ' ||
469 'level_id, ' ||
470 'sr_level_pk, ' ||
471 'parent_level_id, ' ||
472 'sr_parent_level_pk, ' ||
473 'last_update_date, ' ||
474 'last_updated_by, ' ||
475 'creation_date, ' ||
476 'created_by ) ' ||
477 'select ''' ||
478 p_instance_id ||''', ' ||
479 p_level_id || ', ' ||
480 p_level_value_pk_column||', ' ||
481 p_parent_level_id || ', ' ||
482 p_parent_value_pk_column ||', ' ||
483 'sysdate, ' ||
484 FND_GLOBAL.USER_ID || ', ' ||
485 'sysdate, ' ||
486 FND_GLOBAL.USER_ID || ' ' ||
487 'from ' ||
488 p_source_table ;
489
490 if (v_view_exist = 1) then
491 ins(v_sql_stmt);
492 debug_line(v_sql_stmt);
493 EXECUTE IMMEDIATE v_sql_stmt;
494 end if;
495
496 log_debug(' Entering Collect Level values Condition');
497
498 END IF; /* End of IF 1 */
499
500 /* Get the Parent Level Type */
501 begin
502 select level_type_code into v_parent_lvl_type
503 from msd_levels
504 where level_id = p_parent_level_id
505 and plan_type is null; --vinekuma
506 exception
507 when NO_DATA_FOUND then
508 null;
509 WHEN others THEN
510 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
511 errbuf := substr(SQLERRM,1,150);
512 end ;
513
514
515
516 /* dbms_output.put_line('Parent Level : ' || p_parent_level_id ) ;
517 dbms_output.put_line('Parent Level Type : ' || v_parent_lvl_type ) ; */
518
519 /* Process parent level value only if it is TOP level value*/
520 IF (v_parent_lvl_type = '1' AND p_update_lvl_table = 1) THEN
521
522
523 PROCESS_TOP_LEVEL_VALUES (
524 errbuf,
525 retcode,
526 p_source_table,
527 p_dest_table,
528 p_instance_id,
529 p_parent_level_id,
530 p_parent_value_column,
531 p_parent_value_pk_column,
532 p_parent_value_desc_column,
533 p_seq_num,
534 p_delete_flag);
535
536
537 END IF;
538
539
540 /* this piece of code copies item_list_price data if necessary - i.e.
541 if p_update_lvl_table is set to 1 (i.e. the level_id had not been
542 processed before) and level_id is the lowest level or intermediate level
543 in the product dimension */
544
545 IF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) THEN
546
547 IF(MSD_COMMON_UTILITIES.COLLECT_ILP = 1 and p_update_lvl_table = 0) THEN
548
549 MSD_COMMON_UTILITIES.COLLECT_ILP := 3;
550
551 END IF;
552
553 END IF;
554
555
556
557 select level_type_code, dimension_code, org_relationship_view
558 into v_lvl_type, v_dim_code, v_org_view
559 from msd_levels
560 where level_id = p_level_id
561 and plan_type is null; --vinekuma
562
563 IF ((p_update_lvl_table = 1 and MSD_COMMON_UTILITIES.COLLECT_ILP = 2) or MSD_COMMON_UTILITIES.COLLECT_ILP = 3) THEN /* IF 1 */
564 --fnd_file.put_line(fnd_file.log,'came here' || p_update_lvl_table ||' ' || MSD_COMMON_UTILITIES.COLLECT_ILP);
565
566 IF (p_level_id = 1 OR p_level_id = 3) THEN /* IF 2 */
567
568 CREATE_ITEM_LIST_PRICE(
569 errbuf,
570 retcode,
571 p_source_table,
572 p_dest_table,
573 p_instance_id,
574 p_level_id,
575 p_seq_num,
576 p_delete_flag);
577
578
579 END IF; /* End of (v_lvl_type = '2' AND v_dim_code = 'PRD') IF 2*/
580
581 END IF; /* End of p_update_lvl_table = 1 IF 1*/
582
583 IF (p_update_lvl_table = 1 AND p_level_id = 7) THEN
584
585 pop_org_cal_associations (
586 errbuf,
587 retcode,
588 p_source_table,
589 p_dest_table,
590 p_instance_id
591 );
592 END IF;
593
594 IF (p_update_lvl_table = 1 AND p_level_id in (1,18,11)) THEN
595
596 pop_org_lvl_associations (
597 errbuf,
598 retcode,
599 p_level_id,
600 p_source_table,
601 v_org_view,
602 p_dest_table,
603 p_instance_id,
604 p_delete_flag);
605 END IF;
606
607
608 POP_MAX_SEQ_NUM ( errbuf, retcode, p_seq_num );
609
610 COMMIT;
611 if(v_view_exist = 0) then
612 retcode := 1;
613 end if;
614
615 log_debug('Exiting procedure TRANSLATE_LEVEL_PARENT_VALUE');
616
617 exception
618 when others then
619 --write to log an back out
620 errbuf := substr(SQLERRM,1,150);
621 retcode := -1 ; --error
622 ins('ERROR ' || v_sql_stmt);
623 debug_line('ERROR ' || v_sql_stmt);
624 ins('ERROR ' || errbuf);
625 debug_line('ERROR ' || errbuf);
626 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
627 fnd_file.put_line(fnd_file.log, 'The offending sql is:');
628 fnd_file.put_line(fnd_file.log, v_sql_stmt);
629 rollback;
630 -- rollback to Savepoint Before_Delete ;
631
632 End translate_level_parent_values ;
633
634
635
636 /***********************************************************
637
638 PROCEDURE PROCESS_LEVEL_VALUE_PER_ROW
639
640 ***********************************************************/
641 PROCEDURE PROCESS_LEVEL_VALUE_PER_ROW(
642 errbuf OUT NOCOPY VARCHAR2,
643 retcode OUT NOCOPY VARCHAR2,
644 p_instance_id IN VARCHAR2,
645 p_level_id IN NUMBER,
646 p_seq_num IN NUMBER) IS
647
648 /* New Level values will be inserted into fact table
649 and will get deleted from the staging */
650 CURSOR c_insert IS
651 select sr_level_pk
652 from msd_st_level_values
653 where instance = p_instance_id and level_id = p_level_id
654 MINUS
655 select sr_level_pk
656 from msd_level_values
657 where instance = p_instance_id and level_id = p_level_id;
658
659 /* Cursor to find modified level values */
660 /* This cursor needs to be opend only after
661 new level values are deleted from the staging table
662 */
663 CURSOR c_update IS
664 (select sr_level_pk, level_value,
665 attribute1, attribute2, attribute3,
666 attribute4, attribute5,
667 level_value_desc,system_attribute1,system_attribute2, --jarorad
668 dp_enabled_flag --jarorad
669 from msd_st_level_values
670 where instance = p_instance_id and level_id = p_level_id
671 MINUS
672 select sr_level_pk, level_value,
673 attribute1, attribute2, attribute3,
674 attribute4, attribute5,
675 level_value_desc,system_attribute1,system_attribute2, --jarorad
676 dp_enabled_flag --jarorad
677 from msd_level_values
678 where instance = p_instance_id and level_id = p_level_id);
679
680
681
682 TYPE sr_level_pk_tab IS TABLE OF msd_st_level_values.sr_level_pk%TYPE;
683 TYPE level_val_tab IS TABLE OF msd_st_level_values.level_value%TYPE;
684 TYPE level_attribute_tab IS TABLE OF msd_st_level_values.attribute1%TYPE;
685
686 TYPE system_attribute1_tab IS TABLE OF msd_st_level_values.system_attribute1%TYPE; --jarorad
687 TYPE system_attribute2_tab IS TABLE OF msd_st_level_values.system_attribute2%TYPE; --jarorad
688 TYPE dp_enabled_flag_tab IS TABLE OF msd_st_level_values.dp_enabled_flag%TYPE; --jarorad
689
690 a_sr_level_pk sr_level_pk_tab;
691 a_level_value level_val_tab;
692 a_attribute1 level_attribute_tab;
693 a_attribute2 level_attribute_tab;
694 a_attribute3 level_attribute_tab;
695 a_attribute4 level_attribute_tab;
696 a_attribute5 level_attribute_tab;
697 a_level_value_desc level_attribute_tab;
698 a_system_attribute1 system_attribute1_tab; --jarorad
699 a_system_attribute2 system_attribute2_tab; --jarorad
700 a_dp_enabled_flag dp_enabled_flag_tab; --jarorad
701
702 BEGIN
703
704 OPEN c_insert;
705 FETCH c_insert BULK COLLECT INTO a_sr_level_pk;
706 CLOSE c_insert;
707
708 IF (a_sr_level_pk.exists(1)) THEN
709 /* First Delete fetched rows from staging, and then
710 Insert them into Fact Table.
711 */
712 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
713 DELETE FROM msd_st_level_values
714 WHERE instance = p_instance_id and
715 level_id = p_level_id and
716 sr_level_pk = a_sr_level_pk(i)
717 RETURNING level_value, attribute1,
718 attribute2, attribute3, attribute4,
719 attribute5, level_value_desc,
720 system_attribute1,system_attribute2, --jarorad
721 dp_enabled_flag --jarorad
722 BULK COLLECT INTO a_level_value, a_attribute1,
723 a_attribute2, a_attribute3,
724 a_attribute4, a_attribute5,
725 a_level_value_desc,a_system_attribute1, --jarorad
726 a_system_attribute2,a_dp_enabled_flag; --jarorad
727
728 /* Insert new rows into fact table */
729 FORALL j IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
730 INSERT INTO msd_level_values(
731 instance, level_id, level_value,
732 sr_level_pk, level_pk, level_value_desc,
733 action_code, created_by_refresh_num, last_refresh_num,
734 last_update_date, last_updated_by,
735 creation_date, created_by,
736 last_update_login, attribute1, attribute2,
737 attribute3, attribute4, attribute5,
738 system_attribute1,system_attribute2, --jarorad
739 dp_enabled_flag) --jarorad
740 VALUES( p_instance_id, p_level_id, a_level_value(j),
741 a_sr_level_pk(j), MSD_COMMON_UTILITIES.get_level_pk(),
742 a_level_value_desc(j),
743 'I', p_seq_num, p_seq_num,
744 sysdate, FND_GLOBAL.USER_ID,
745 sysdate, FND_GLOBAL.USER_ID,
746 FND_GLOBAL.LOGIN_ID, a_attribute1(j), a_attribute2(j),
747 a_attribute3(j), a_attribute4(j), a_attribute5(j),
748 a_system_attribute1(j), a_system_attribute2(j), --jarorad
749 a_dp_enabled_flag(j) ); --jarorad
750 END IF;
751
752
753 /* Fetch updated rows from staging */
754 OPEN c_update;
755 FETCH c_update BULK COLLECT INTO a_sr_level_pk, a_level_value, a_attribute1,
756 a_attribute2, a_attribute3,
757 a_attribute4, a_attribute5, a_level_value_desc,
758 a_system_attribute1,a_system_attribute2, --jarorad
759 a_dp_enabled_flag; --jarorad
760 CLOSE c_update;
761
762 IF (a_sr_level_pk.exists(1)) THEN
763 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
764 UPDATE msd_level_values
765 SET level_value = a_level_value(i),
766 attribute1 = a_attribute1(i),
767 attribute2 = a_attribute2(i),
768 attribute3 = a_attribute3(i),
769 attribute4 = a_attribute4(i),
770 attribute5 = a_attribute5(i),
771 level_value_desc = a_level_value_desc(i),
772 system_attribute1 = a_system_attribute1(i), --jarorad
773 system_attribute2 = a_system_attribute2(i), --jarorad
774 dp_enabled_flag = a_dp_enabled_flag(i), --jarorad
775 action_code = 'U',
776 last_refresh_num = p_seq_num,
777 last_update_date = sysdate
778 WHERE instance = p_instance_id and
779 level_id = p_level_id and
780 sr_level_pk = a_sr_level_pk(i);
781 END IF;
782
783
784
785 EXCEPTION
786 when others then
787 errbuf := substr(SQLERRM,1,150);
788 retcode := -1;
789 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
790
791
792 END PROCESS_LEVEL_VALUE_PER_ROW;
793
794
795
796 /***********************************************************
797
798 PROCEDURE CREATE_DELETED_LEVEL_VALUES
799
800 ***********************************************************/
801
802 PROCEDURE CREATE_DELETED_LEVEL_VALUES(
803 errbuf OUT NOCOPY VARCHAR2,
804 retcode OUT NOCOPY VARCHAR2,
805 p_instance_id IN VARCHAR2,
806 p_level_id IN NUMBER,
807 p_seq_num IN NUMBER) IS
808
809 CURSOR c_delete IS
810 (select sr_level_pk
811 from msd_level_values
812 where instance = p_instance_id and level_id = p_level_id
813 MINUS
814 select sr_level_pk
815 from msd_st_level_values
816 where instance = p_instance_id and level_id = p_level_id);
817
818 TYPE sr_level_pk_tab is table of msd_level_values.sr_level_pk%TYPE;
819 TYPE level_pk_tab is table of msd_level_values.level_pk%TYPE;
820 TYPE crn_tab is table of msd_level_values.created_by_refresh_num%TYPE;
821
822 a_sr_level_pk SR_LEVEL_PK_TAB;
823 a_level_pk LEVEL_PK_TAB;
824 a_crn CRN_TAB;
825
826
827 BEGIN
828
829 OPEN c_delete;
830 FETCH c_delete BULK COLLECT INTO a_sr_level_pk;
831 CLOSE c_delete;
832
833 IF (a_sr_level_pk.exists(1)) THEN
834 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
835 DELETE FROM msd_level_values
836 WHERE instance = p_instance_id and
837 level_id = p_level_id and
838 sr_level_pk = a_sr_level_pk(i)
839 RETURNING level_pk, created_by_refresh_num
840 BULK COLLECT INTO a_level_pk, a_crn;
841
842 FORALL j IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
843 INSERT INTO msd_deleted_level_values(instance, level_id,
844 sr_level_pk, level_pk,
845 created_by_refresh_num, last_refresh_num,
846 creation_date, created_by, last_update_date,
847 last_updated_by, last_update_login)
848 VALUES(p_instance_id, p_level_id,
849 a_sr_level_pk(j), a_level_pk(j),
850 a_crn(j) , p_seq_num,
851 sysdate, FND_GLOBAL.USER_ID, sysdate,
852 FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
853
854 /* VM - To be implemented
855 1. We should mark data deleted from msd_cs_Data for deleted level
856 values
857 2. We should delete level associations for level values being deleted.
858 */
859 END IF;
860
861 EXCEPTION
862 when others then
863 errbuf := substr(SQLERRM,1,150);
864 retcode := -1;
865 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
866
867
868
869 END CREATE_DELETED_LEVEL_VALUES;
870
871
872
873 /***********************************************************
874
875 PROCEDURE PROCESS_LEVEL_ASSOCIATION
876
877 ***********************************************************/
878 PROCEDURE PROCESS_LEVEL_ASSOCIATION(
879 errbuf OUT NOCOPY VARCHAR2,
880 retcode OUT NOCOPY VARCHAR2,
881 p_instance_id IN VARCHAR2,
882 p_level_id IN NUMBER,
883 p_parent_level_id IN NUMBER,
884 p_seq_num IN NUMBER) IS
885
886 /* This cursur will select only new level associations */
887 CURSOR c_new_rows IS
888 (select sr_level_pk
889 from msd_st_level_associations
890 where instance = p_instance_id and level_id = p_level_id and
891 parent_level_id = p_parent_level_id
892 MINUS
893 select sr_level_pk
894 from msd_level_associations
895 where instance = p_instance_id and level_id = p_level_id and
896 parent_level_id = p_parent_level_id);
897
898
899 /* Cursor for updated level association */
900 /* This cursor need to be opened only after
901 new associations are deleted from the staging table */
902 CURSOR c_update_rows IS
903 (select sr_level_pk, sr_parent_level_pk
904 from msd_st_level_associations
905 where instance = p_instance_id and level_id = p_level_id and
906 parent_level_id = p_parent_level_id
907 MINUS
908 select sr_level_pk, sr_parent_level_pk
909 from msd_level_associations
910 where instance = p_instance_id and level_id = p_level_id and
911 parent_level_id = p_parent_level_id);
912
913
914
915 TYPE sr_level_pk_tab is table of msd_level_associations.sr_level_pk%TYPE;
916 TYPE sr_parent_level_pk_tab is table of msd_level_associations.sr_parent_level_pk%TYPE;
917
918 a_sr_level_pk SR_LEVEL_PK_TAB;
919 a_sr_parent_level_pk SR_PARENT_LEVEL_PK_TAB;
920
921 l_count NUMBER := 0;
922
923 BEGIN
924 OPEN c_new_rows;
925 FETCH c_new_rows BULK COLLECT INTO a_sr_level_pk;
926 CLOSE c_new_rows;
927
928 /* For new level association */
929 IF (a_sr_level_pk.exists(1)) THEN
930 /* First Delete fetched rows(new level associations) from staging,
931 and then Insert them into Fact Table.
932 */
933 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
934 DELETE FROM msd_st_level_associations
935 WHERE instance = p_instance_id and
936 level_id = p_level_id and
937 sr_level_pk = a_sr_level_pk(i) and
938 parent_level_id = p_parent_level_id
939 RETURNING sr_parent_level_pk
940 BULK COLLECT INTO a_sr_parent_level_pk;
941
942 /* Insert new rows into fact table */
943 IF (a_sr_parent_level_pk.exists(1)) THEN
944 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
945 INSERT INTO msd_level_associations(
946 instance, level_id, sr_level_pk,
947 parent_level_id, sr_parent_level_pk,
948 last_update_date, last_updated_by,
949 creation_date, created_by, last_update_login,
950 created_by_refresh_num, last_refresh_num, action_code)
951 VALUES(p_instance_id, p_level_id, a_sr_level_pk(i),
952 p_parent_level_id, a_sr_parent_level_pk(i),
953 sysdate, FND_GLOBAL.USER_ID,
954 sysdate,FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
955 p_seq_num, p_seq_num, 'I');
956 END IF;
957 END IF; /* End of New Association */
958
959 OPEN c_update_rows;
960 FETCH c_update_rows BULK COLLECT INTO a_sr_level_pk, a_sr_parent_level_pk;
961 CLOSE c_update_rows;
962
963 /* For updated level association */
964 IF (a_sr_level_pk.exists(1) and a_sr_parent_level_pk.exists(1)) THEN
965 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
966 UPDATE msd_level_associations
967 SET
968 sr_parent_level_pk = a_sr_parent_level_pk(i),
969 action_code = 'U',
970 last_refresh_num = p_seq_num,
971 last_update_date = sysdate
972 WHERE instance = p_instance_id and
973 level_id = p_level_id and
974 sr_level_pk = a_sr_level_pk(i) and
975 parent_level_id = p_parent_level_id;
976 END IF;
977
978 EXCEPTION
979 when others then
980 errbuf := substr(SQLERRM,1,150);
981 retcode := -1;
982 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
983
984
985 END PROCESS_LEVEL_ASSOCIATION;
986
987
988
989 /***********************************************************
990
991 PROCEDURE CREATE_DELETED_LEVEL_ASSOCI
992
993 ***********************************************************/
994
995 PROCEDURE CREATE_DELETED_LEVEL_ASSOCI(
996 errbuf OUT NOCOPY VARCHAR2,
997 retcode OUT NOCOPY VARCHAR2,
998 p_instance_id IN VARCHAR2,
999 p_level_id IN NUMBER,
1000 p_parent_level_id IN NUMBER,
1001 p_seq_num IN NUMBER) IS
1002
1003
1004 CURSOR c_delete IS
1005 (select sr_level_pk, sr_parent_level_pk
1006 from msd_level_associations
1007 where instance = p_instance_id and level_id = p_level_id and
1008 parent_level_id = p_parent_level_id
1009 MINUS
1010 select sr_level_pk, sr_parent_level_pk
1011 from msd_st_level_associations
1012 where instance = p_instance_id and level_id = p_level_id and
1013 parent_level_id = p_parent_level_id);
1014
1015 TYPE sr_level_pk_tab is table of msd_level_associations.sr_level_pk%TYPE;
1016 TYPE sr_parent_level_pk_tab is table of msd_level_associations.sr_parent_level_pk%TYPE;
1017
1018 a_sr_level_pk SR_LEVEL_PK_TAB;
1019 a_sr_parent_level_pk SR_PARENT_LEVEL_PK_TAB;
1020
1021 BEGIN
1022
1023 OPEN c_delete;
1024 FETCH c_delete BULK COLLECT INTO a_sr_level_pk, a_sr_parent_level_pk;
1025 CLOSE c_delete;
1026
1027 IF (a_sr_level_pk.exists(1)) THEN
1028 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1029 DELETE FROM msd_level_associations
1030 WHERE instance = p_instance_id and
1031 level_id = p_level_id and
1032 sr_level_pk = a_sr_level_pk(i) and
1033 parent_level_id = p_parent_level_id and
1034 sr_parent_level_pk = a_sr_parent_level_pk(i);
1035 END IF;
1036
1037 EXCEPTION
1038 when others then
1039 errbuf := substr(SQLERRM,1,150);
1040 retcode := -1;
1041 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1042
1043
1044
1045 END CREATE_DELETED_LEVEL_ASSOCI;
1046
1047
1048 /***********************************************************
1049
1050 PROCEDURE PROCESS_TOP_LEVEL_VALUES
1051
1052 ***********************************************************/
1053 PROCEDURE PROCESS_TOP_LEVEL_VALUES (
1054 errbuf OUT NOCOPY VARCHAR2,
1055 retcode OUT NOCOPY VARCHAR2,
1056 p_source_table IN VARCHAR2,
1057 p_dest_table IN VARCHAR2,
1058 p_instance_id IN VARCHAR2,
1059 p_parent_level_id IN NUMBER,
1060 p_parent_value_column IN VARCHAR2,
1061 p_parent_value_pk_column IN VARCHAR2,
1062 p_parent_value_desc_column IN VARCHAR2,
1063 p_seq_num IN NUMBER,
1064 p_delete_flag IN VARCHAR2) IS
1065
1066
1067 v_sql_stmt varchar2(4000);
1068
1069 BEGIN
1070
1071
1072 /* dbms_output.put_line('Parent Level : ' || p_parent_level_id ) ; */
1073
1074 /* Note that we will not be able to get the attributes 1 - 5 for the
1075 Top level as we will not have a separate view for the top level */
1076
1077 /* For PULL */
1078 IF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE) THEN
1079 /* Find deleted top level values, if any */
1080 /* Top Level Values can be modified, but should not be deleted.
1081 comment out this part
1082 IF (p_delete_flag = 'Y') THEN
1083 CREATE_DELETED_LEVEL_VALUES( errbuf,
1084 retcode,
1085 p_instance_id,
1086 p_parent_level_id,
1087 p_seq_num);
1088 END IF;
1089 */
1090
1091 /* Update or insert new top level values */
1092 PROCESS_LEVEL_VALUE_PER_ROW( errbuf,
1093 retcode,
1094 p_instance_id,
1095 p_parent_level_id,
1096 p_seq_num);
1097 ELSE
1098 /* Collect into Staging table*/
1099
1100 delete from msd_st_level_values
1101 where instance = p_instance_id
1102 and level_id = p_parent_level_id ;
1103
1104 v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( ' ||
1105 'instance, ' ||
1106 'level_value, ' ||
1107 'sr_level_pk, ' ||
1108 'level_id, ' ||
1109 'level_value_desc, ' ||
1110 'last_update_date, ' ||
1111 'last_updated_by, ' ||
1112 'creation_date, ' ||
1113 'created_by ) ' ||
1114 'SELECT ''' ||
1115 p_instance_id ||''', ' ||
1116 p_parent_value_column || ', ' ||
1117 p_parent_value_pk_column ||', ' ||
1118 p_parent_level_id || ', ' ||
1119 'parent_desc_alias' ||', ' ||
1120 'sysdate, ' || FND_GLOBAL.USER_ID || ', ' ||
1121 'sysdate, ' || FND_GLOBAL.USER_ID || ' ' ||
1122 'FROM ' ||
1123 '(select distinct ' || p_parent_value_column || ', ' ||
1124 p_parent_value_pk_column || ', ' ||
1125 p_parent_level_id || ', '||
1126 p_parent_value_desc_column || ' parent_desc_alias ' || ' from ' ||
1127 p_source_table || ') src ';
1128
1129 ins(v_sql_stmt);
1130 debug_line(v_sql_stmt);
1131 EXECUTE IMMEDIATE v_sql_stmt;
1132
1133 END IF;
1134
1135
1136
1137 EXCEPTION
1138 when others then
1139 errbuf := substr(SQLERRM,1,150);
1140 retcode := -1;
1141 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1142
1143
1144
1145 END PROCESS_TOP_LEVEL_VALUES;
1146
1147
1148
1149
1150 /***********************************************************
1151
1152 PROCEDURE CREATE_ITEM_LIST_PRICE
1153
1154 ***********************************************************/
1155
1156 PROCEDURE CREATE_ITEM_LIST_PRICE(
1157 errbuf OUT NOCOPY VARCHAR2,
1158 retcode OUT NOCOPY VARCHAR2,
1159 p_source_table IN VARCHAR2,
1160 p_dest_table IN VARCHAR2,
1161 p_instance_id IN VARCHAR2,
1162 p_level_id IN NUMBER,
1163 p_seq_num IN NUMBER,
1164 p_delete_flag IN VARCHAR2) IS
1165
1166
1167 x_dblink VARCHAR2(128);
1168 v_sql_stmt varchar2(4000);
1169
1170
1171 BEGIN
1172
1173 IF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE) THEN
1174 /* pulling data */
1175 IF (p_delete_flag = 'Y' and p_level_id = 1) THEN
1176 DELETED_ITEM_LIST_PRICE( errbuf,
1177 retcode,
1178 p_instance_id,
1179 p_seq_num);
1180 END IF;
1181 UPDATE_ITEM_LIST_PRICE( errbuf,
1182 retcode,
1183 p_instance_id,
1184 p_seq_num);
1185
1186 delete from msd_st_item_list_price
1187 where instance = p_instance_id;
1188
1189
1190 ELSIF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1191
1192 /* Collection */
1193 IF (p_level_id = 1) THEN
1194 delete from msd_st_item_list_price
1195 where instance = p_instance_id;
1196 END IF;
1197
1198 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
1199 if (retcode = -1) then
1200 retcode :=-1;
1201 return;
1202 end if;
1203
1204 v_sql_stmt:= ' insert into msd_st_item_list_price ( '||
1205 'instance, '||
1206 'item, '||
1207 'sr_item_pk, '||
1208 'list_price, '||
1209 'avg_discount, '||
1210 'base_uom, '||
1211 'item_type_id, ' ||
1212 'forecast_type_id, ' ||
1213 'creation_date, '||
1214 'created_by, '||
1215 'last_update_date, '||
1216 'last_updated_by, '||
1217 'last_update_login) '||
1218 'SELECT ''' || p_instance_id || ''','||
1219 'item,'||
1220 'sr_item_pk, '||
1221 'list_price, '||
1222 'avg_discount, '||
1223 'base_uom, ' ||
1224 'item_type_id, ' ||
1225 'forecast_type_id, ' ||
1226 'sysdate, ' ||
1227 FND_GLOBAL.USER_ID || ', ' ||
1228 'sysdate, ' ||
1229 FND_GLOBAL.USER_ID || ', ' ||
1230 FND_GLOBAL.USER_ID || ' ' ||
1231 'FROM ' ||
1232 ' msd_sr_item_list_price_v' || x_dblink ||
1233 ' where sr_item_pk in (select to_number(decode(ltrim(sr_level_pk, ''.0123456789''),' ||
1234 ' null, sr_level_pk, null)) ' ||
1235 ' from msd_st_level_values ' ||
1236 ' where level_id = ' || p_level_id || ')' ;
1237
1238 ins(v_sql_stmt);
1239 debug_line(v_sql_stmt);
1240 EXECUTE IMMEDIATE v_sql_stmt;
1241
1242 END IF;
1243
1244 EXCEPTION
1245 when others then
1246 errbuf := substr(SQLERRM,1,150);
1247 retcode := -1;
1248 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1249
1250
1251
1252 END CREATE_ITEM_LIST_PRICE;
1253
1254
1255 /***********************************************************
1256
1257 PROCEDURE DELETED_ITEM_LIST_PRICE
1258
1259 ***********************************************************/
1260
1261 PROCEDURE DELETED_ITEM_LIST_PRICE(
1262 errbuf OUT NOCOPY VARCHAR2,
1263 retcode OUT NOCOPY VARCHAR2,
1264 p_instance_id IN VARCHAR2,
1265 p_seq_num IN NUMBER) IS
1266
1267 CURSOR c_delete IS
1268 (select sr_item_pk
1269 from msd_item_list_price
1270 where instance = p_instance_id
1271 MINUS
1272 select sr_item_pk
1273 from msd_st_item_list_price
1274 where instance = p_instance_id);
1275
1276 TYPE sr_item_pk_tab is table of msd_item_list_price.sr_item_pk%TYPE;
1277
1278 a_sr_item_pk SR_ITEM_PK_TAB;
1279
1280
1281 BEGIN
1282
1283 OPEN c_delete;
1284 FETCH c_delete BULK COLLECT INTO a_sr_item_pk;
1285 CLOSE c_delete;
1286
1287 IF (a_sr_item_pk.exists(1)) THEN
1288 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1289 DELETE FROM msd_item_list_price
1290 WHERE sr_item_pk = a_sr_item_pk(i) and instance = p_instance_id;
1291 /*
1292 FORALL j IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1293 INSERT INTO msd_deleted_item_list_price(instance, sr_item_pk, created_by_refresh_num,
1294 creation_date, created_by, last_update_date,
1295 last_updated_by, last_update_login)
1296 VALUES(p_instance_id, a_sr_item_pk(j), p_seq_num,
1297 sysdate, FND_GLOBAL.USER_ID, sysdate,
1298 FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
1299 */
1300 END IF;
1301
1302 EXCEPTION
1303 when others then
1304 errbuf := substr(SQLERRM,1,150);
1305 retcode := -1;
1306 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1307
1308
1309
1310 END DELETED_ITEM_LIST_PRICE;
1311
1312
1313
1314 /***********************************************************
1315
1316 PROCEDURE UPDATE_ITEM_LIST_PRICE
1317
1318 ***********************************************************/
1319 PROCEDURE UPDATE_ITEM_LIST_PRICE(
1320 errbuf OUT NOCOPY VARCHAR2,
1321 retcode OUT NOCOPY VARCHAR2,
1322 p_instance_id IN VARCHAR2,
1323 p_seq_num IN NUMBER) IS
1324
1325
1326 CURSOR c_st_rows IS
1327 select item, list_price, avg_discount, base_uom,
1328 sr_item_pk, item_type_id, forecast_type_id
1329 from msd_st_item_list_price
1330 where instance = p_instance_id;
1331
1332
1333 CURSOR c_fact_rows (p_item_pk VARCHAR2) IS
1334 select sr_item_pk, item, list_price, avg_discount, base_uom,
1335 item_type_id, forecast_type_id
1336 from msd_item_list_price
1337 where instance = p_instance_id and sr_item_pk = p_item_pk;
1338
1339 l_item VARCHAR2(240);
1340 l_list_price NUMBER;
1341 l_avg_discount NUMBER;
1342 l_base_uom VARCHAR2(40);
1343 l_item_pk VARCHAR2(240);
1344 l_item_type_id NUMBER;
1345 l_forecast_type_id NUMBER;
1346
1347
1348 BEGIN
1349
1350 FOR rec_st_rows IN c_st_rows LOOP
1351
1352 OPEN c_fact_rows( rec_st_rows.sr_item_pk);
1353 FETCH c_fact_rows
1354 INTO l_item_pk, l_item, l_list_price,
1355 l_avg_discount, l_base_uom, l_item_type_id,
1356 l_forecast_type_id;
1357 CLOSE c_fact_rows;
1358
1359 /* If this row doesn't exist in fact table then insert */
1360 IF (l_item_pk is null) THEN
1361
1362 INSERT INTO msd_item_list_price( instance, item, list_price,
1363 avg_discount, base_uom,
1364 sr_item_pk, item_type_id, forecast_type_id,
1365 action_code, created_by_refresh_num, last_refresh_num,
1366 last_update_date, last_updated_by,
1367 creation_date, created_by,
1368 last_update_login)
1369 VALUES( p_instance_id, rec_st_rows.item, rec_st_rows.list_price,
1370 rec_st_rows.avg_discount, rec_st_rows.base_uom,
1371 rec_st_rows.sr_item_pk, rec_st_rows.item_type_id,
1372 rec_st_rows.forecast_type_id,
1373 'I', p_seq_num, p_seq_num,
1374 sysdate, FND_GLOBAL.USER_ID,
1375 sysdate, FND_GLOBAL.USER_ID,
1376 FND_GLOBAL.LOGIN_ID);
1377
1378 ELSE
1379
1380 /* If this row exists in the fact then check row has been
1381 updated row or not. */
1382 IF ( (nvl(rec_st_rows.item, 'NULL') <> nvl(l_item, 'NULL')) OR
1383 (nvl(rec_st_rows.list_price,-9999) <> nvl(l_list_price, -9999)) OR
1384 (nvl(rec_st_rows.avg_discount,-9999) <> nvl(l_avg_discount,-9999)) OR
1385 (nvl(rec_st_rows.base_uom,'NULL') <> nvl(l_base_uom,'NULL') ) OR
1386 (nvl(rec_st_rows.item_type_id,-9999) <> nvl(l_item_type_id,-9999)) OR
1387 (nvl(rec_st_rows.forecast_type_id,-9999) <> nvl(l_forecast_type_id,-9999)) ) THEN
1388 /* If this row has been modified */
1389
1390 UPDATE msd_item_list_price
1391 SET item = rec_st_rows.item,
1392 list_price = rec_st_rows.list_price,
1393 avg_discount = rec_st_rows.avg_discount,
1394 base_uom = rec_st_rows.base_uom,
1395 item_type_id = rec_st_rows.item_type_id,
1396 forecast_type_id = rec_st_rows.forecast_type_id,
1397 action_code = 'U',
1398 last_refresh_num = p_seq_num,
1399 last_update_date = sysdate
1400 WHERE instance = p_instance_id and
1401 sr_item_pk = rec_st_rows.sr_item_pk;
1402 END IF;
1403 END IF; /* End of IF (l_item_pk is null) */
1404
1405 l_item_pk := null;
1406 l_item := null;
1407 l_list_price := null;
1408 l_avg_discount := null;
1409 l_base_uom := null;
1410 l_item_type_id := null;
1411 l_forecast_type_id := null;
1412 END LOOP; /* End of For LOOP */
1413
1414
1415
1416 EXCEPTION
1417 when others then
1418 errbuf := substr(SQLERRM,1,150);
1419 retcode := -1;
1420 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1421
1422
1423 END UPDATE_ITEM_LIST_PRICE;
1424
1425
1426
1427
1428
1429 /*----------------------------------------------------------------
1430 This procedure will clean up the MSD_DELETED_LEVEL_VALUES table,
1431 MSD_DELETED_LEVEL_ASSOCIATIONS, MSD_DELETED_ITEM_LIST_PRICE table
1432
1433 -----------------------------------------------------------------*/
1434
1435 PROCEDURE CLEAN_DELETED_LEVEL_VALUES(errbuf OUT NOCOPY VARCHAR2,
1436 retcode OUT NOCOPY VARCHAR2) IS
1437
1438 l_least_refresh_num NUMBER := 0;
1439
1440 BEGIN
1441
1442 /* Find the least refresh number for existing demand plan */
1443 SELECT nvl(min(dp_build_refresh_num), 0) INTO l_least_refresh_num
1444 FROM msd_demand_plans;
1445
1446 if l_least_refresh_num <> 0 then
1447 DELETE FROM msd_deleted_level_values
1448 WHERE LAST_REFRESH_NUM <= l_least_refresh_num;
1449 end if;
1450
1451 /* DELETE FROM msd_deleted_level_associations
1452 WHERE CREATED_BY_REFRESH_NUM < l_least_refresh_num;
1453
1454 DELETE FROM msd_deleted_item_list_price
1455 WHERE CREATED_BY_REFRESH_NUM < l_least_refresh_num;
1456 */
1457
1458
1459
1460 EXCEPTION
1461 when others then
1462 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1463 errbuf := substr(SQLERRM,1,150);
1464 retcode := -1;
1465
1466 END CLEAN_DELETED_LEVEL_VALUES;
1467
1468
1469
1470 /** Added for Multiple Time Hierarchies.
1471 * This will populate associations between
1472 * enabled orgs in source with manufacturing calendars.
1473 **/
1474
1475 PROCEDURE POP_ORG_CAL_ASSOCIATIONS (
1476 errbuf OUT NOCOPY VARCHAR2,
1477 retcode OUT NOCOPY VARCHAR2,
1478 p_source_table IN VARCHAR2,
1479 p_dest_table IN VARCHAR2,
1480 p_instance_id IN NUMBER) IS
1481
1482 /* The Type Id for Manufacturing Calendar */
1483 p_man_cal_type VARCHAR2(240) := MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR;
1484
1485 /* Destination table to insert into */
1486 v_dest_table VARCHAR2(1000);
1487
1488 /* The Insert-Select Sql Statement */
1489 v_stmt VARCHAR2(2000);
1490
1491 /* The link to the source database */
1492 x_dblink VARCHAR2(2000);
1493
1494 BEGIN
1495
1496 if (p_source_table <> MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1497 /* Get the x_dblink from p_instance_id */
1498 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
1499
1500 /* Check for errors in getting the db link */
1501
1502 if (retcode = -1) then
1503
1504 retcode :=-1;
1505 errbuf := 'Error while getting db_link';
1506 return;
1507
1508 end if;
1509 end if;
1510
1511
1512 /* Refresh the existing Org Calendar relationships for this instance */
1513
1514 if (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1515
1516 delete from msd_st_org_calendars
1517 where instance = p_instance_id;
1518
1519 v_dest_table := 'MSD_ST_ORG_CALENDARS';
1520
1521 else
1522
1523 delete from msd_org_calendars
1524 where instance = p_instance_id;
1525
1526 v_dest_table := 'MSD_ORG_CALENDARS';
1527
1528 end if;
1529
1530 /** Insert Data **/
1531
1532 if (p_source_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1533
1534 insert into msd_org_calendars
1535 (INSTANCE,
1536 SR_ORG_PK,
1537 CALENDAR_TYPE,
1538 CALENDAR_CODE,
1539 CREATION_DATE,
1540 CREATED_BY,
1541 LAST_UPDATE_DATE,
1542 LAST_UPDATED_BY)
1543 select
1544 a.instance,
1545 a.sr_org_pk,
1546 a.calendar_type,
1547 a.calendar_code,
1548 sysdate,
1549 fnd_global.user_id,
1550 sysdate,
1551 fnd_global.user_id
1552 from
1553 (select distinct
1554 instance,
1555 sr_org_pk,
1556 calendar_type,
1557 calendar_code
1558 from msd_st_org_calendars
1559 where instance = p_instance_id) a;
1560
1561 delete from msd_st_org_calendars where instance = p_instance_id;
1562
1563
1564 else
1565
1566 v_stmt := 'insert into ' || v_dest_table ||
1567 ' ( INSTANCE, SR_ORG_PK, CALENDAR_TYPE, CALENDAR_CODE, ' ||
1568 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) ' ||
1569 ' select ' ||
1570 p_instance_id || ', ' ||
1571 ' mod.organization_id' || ', ' ||
1572 '''' || p_man_cal_type || '''' || ', ' ||
1573 ' mod.calendar_code' || ', ' ||
1574 ' sysdate' || ', ' ||
1575 fnd_global.user_id || ', ' ||
1576 ' sysdate' || ', ' ||
1577 fnd_global.user_id || ' ' ||
1578 ' From MSD_ORGANIZATION_DEFINITIONS' || x_dblink || ' MOD';
1579 v_stmt := v_stmt || ' where exists (select 1 from msd_app_instance_orgs' || x_dblink || ' maio where MOD.organization_id = MAIO.organization_id)';
1580
1581 execute immediate v_stmt;
1582
1583 end if;
1584
1585
1586 EXCEPTION
1587 when others then
1588 errbuf := substr(SQLERRM,1,150);
1589 retcode := -1;
1590 fnd_file.put_line(fnd_file.log, substr(v_stmt, 1, 100));
1591 fnd_file.put_line(fnd_file.log, substr(v_stmt,100, 200));
1592 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1593
1594 end;
1595
1596
1597 PROCEDURE POP_ORG_LVL_ASSOCIATIONS (
1598 errbuf OUT NOCOPY VARCHAR2,
1599 retcode OUT NOCOPY VARCHAR2,
1600 p_lvl_id IN NUMBER,
1601 p_source_table IN VARCHAR2,
1602 p_org_relationship_view IN VARCHAR2,
1603 p_dest_table IN VARCHAR2,
1604 p_instance_id IN NUMBER,
1605 p_delete_flag IN VARCHAR2) IS
1606
1607 /* Destination table to insert into */
1608 v_dest_table VARCHAR2(1000);
1609
1610 /* The Insert-Select Sql Statement */
1611 v_stmt VARCHAR2(2000);
1612
1613 /* The link to the source database */
1614 x_dblink VARCHAR2(2000);
1615
1616 cursor c_delete is
1617 select instance,
1618 level_id,
1619 sr_level_pk,
1620 org_level_id,
1621 org_sr_level_pk
1622 from msd_st_level_org_asscns
1623 where instance = p_instance_id
1624 and level_id = p_lvl_id;
1625
1626
1627
1628 BEGIN
1629
1630 if (p_source_table <> MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1631
1632 /* Get the x_dblink from p_instance_id */
1633 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
1634
1635 /* Check for errors in getting the db link */
1636
1637 if (retcode = -1) then
1638
1639 retcode :=-1;
1640 errbuf := 'Error while getting db_link';
1641 return;
1642
1643 end if;
1644 end if;
1645
1646
1647 /* Refresh the existing Org Calendar relationships for this instance */
1648
1649 if (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1650
1651 delete from msd_st_level_org_asscns
1652 where instance = p_instance_id
1653 and level_id = p_lvl_id;
1654
1655 v_dest_table := 'MSD_ST_LEVEL_ORG_ASSCNS';
1656 /* Bug # 3745624. Delete all level org asscns only when Complete Refresh = 'y' else delete only those values which
1657 exist in msd_st_level_org_asscns */
1658 elsif (p_delete_flag = 'Y') then
1659
1660 delete from msd_level_org_asscns
1661 where instance = p_instance_id
1662 and level_id = p_lvl_id;
1663
1664 v_dest_table := 'MSD_LEVEL_ORG_ASSCNS';
1665
1666 elsif (p_delete_flag = 'N') then
1667
1668 for c_delete_cur in c_delete loop
1669
1670 delete from msd_level_org_asscns
1671 where instance = p_instance_id
1672 and level_id = p_lvl_id
1673 and sr_level_pk = c_delete_cur.sr_level_pk
1674 and org_level_id = c_delete_cur.org_level_id
1675 and org_sr_level_pk = c_delete_cur.org_sr_level_pk;
1676
1677 end loop;
1678
1679 v_dest_table := 'MSD_LEVEL_ORG_ASSCNS';
1680
1681 end if;
1682
1683 /** Insert Data **/
1684
1685 if (p_source_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
1686
1687 insert into msd_level_org_asscns
1688 ( INSTANCE,
1689 LEVEL_ID,
1690 SR_LEVEL_PK,
1691 ORG_LEVEL_ID,
1692 ORG_SR_LEVEL_PK,
1693 LAST_UPDATE_DATE,
1694 LAST_UPDATED_BY,
1695 CREATION_DATE,
1696 CREATED_BY
1697 )
1698 select
1699 a.instance,
1700 a.level_id,
1701 a.sr_level_pk,
1702 a.org_level_id,
1703 a.org_sr_level_pk,
1704 sysdate,
1705 fnd_global.user_id,
1706 sysdate,
1707 fnd_global.user_id
1708 from
1709 (select distinct
1710 instance,
1711 level_id,
1712 sr_level_pk,
1713 org_level_id,
1714 org_sr_level_pk
1715 from msd_st_level_org_asscns
1716 where instance = p_instance_id
1717 and level_id = p_lvl_id) a;
1718
1719 delete from msd_st_level_org_asscns
1720 where instance = p_instance_id
1721 and level_id = p_lvl_id;
1722
1723 else
1724
1725 v_stmt := 'insert into ' || v_dest_table ||
1726 ' ( INSTANCE, LEVEL_ID, SR_LEVEL_PK, ORG_LEVEL_ID, ORG_SR_LEVEL_PK, ' ||
1727 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) ' ||
1728 ' select ' ||
1729 p_instance_id || ', ' ||
1730 p_lvl_id || ', ' ||
1731 ' src.level_value_pk ' || ', ' ||
1732 ' src.org_level_id ' || ', ' ||
1733 ' src.org_level_value_pk ' || ', ' ||
1734 ' sysdate ' || ', ' ||
1735 fnd_global.user_id || ', ' ||
1736 ' sysdate ' || ', ' ||
1737 fnd_global.user_id || ' ' ||
1738 ' From ' || p_org_relationship_view || x_dblink || ' src';
1739
1740 execute immediate v_stmt;
1741
1742 end if;
1743
1744
1745 EXCEPTION
1746 when others then
1747 errbuf := substr(SQLERRM,1,150);
1748 retcode := -1;
1749 fnd_file.put_line(fnd_file.log, substr(v_stmt, 1, 100));
1750 fnd_file.put_line(fnd_file.log, substr(v_stmt,100, 200));
1751 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1752
1753 end;
1754
1755
1756 /* Stores the maximum refresh number for level values collections
1757 */
1758
1759 PROCEDURE POP_MAX_SEQ_NUM (
1760 errbuf OUT NOCOPY VARCHAR2,
1761 retcode OUT NOCOPY VARCHAR2,
1762 p_seq_num IN NUMBER) IS
1763
1764 x_temp number;
1765
1766 BEGIN
1767
1768 SELECT REFRESH_NUM INTO x_temp
1769 FROM MSD_DP_PARAMETERS_DS
1770 WHERE DEMAND_PLAN_ID = -1;
1771
1772 UPDATE MSD_DP_PARAMETERS_DS
1773 SET REFRESH_NUM = p_seq_num
1774 WHERE DEMAND_PLAN_ID = -1;
1775
1776 EXCEPTION WHEN NO_DATA_FOUND THEN
1777
1778 INSERT INTO MSD_DP_PARAMETERS_DS
1779 ( DEMAND_PLAN_ID,
1780 DATA_TYPE,
1781 PARAMETER_TYPE,
1782 PARAMETER_NAME,
1783 REFRESH_NUM,
1784 LAST_UPDATE_DATE,
1785 LAST_UPDATED_BY,
1786 CREATION_DATE,
1787 CREATED_BY
1788 ) VALUES
1789 (
1790 -1,
1791 'LEVEL_VALUES',
1792 null,
1793 null,
1794 p_seq_num,
1795 sysdate,
1796 fnd_global.user_id,
1797 sysdate,
1798 fnd_global.user_id
1799 );
1800
1801 END;
1802
1803
1804
1805
1806
1807
1808 /*------------------------------------------------------------------
1809 This program is no longer needed, but is kept around for possible
1810
1811
1812
1813
1814 /*------------------------------------------------------------------
1815 This program is no longer needed, but is kept around for possible
1816 future use.
1817 --------------------------------------------------------------------*/
1818 procedure revert_level_values(p_level_id number, p_instance varchar2) is
1819 v_sql varchar2(2000);
1820 begin
1821 -- this is dynamic sql because the pl/sql refuses to accept this
1822 -- query as valid.
1823 v_sql := 'update msd_level_values lv '||
1824 'set level_pk = nvl((select level_pk '||
1825 'from msd_backup_level_values bak '||
1826 'where bak.level_id = ' || p_level_id ||
1827 ' and bak.instance = '''|| p_instance ||
1828 ''' and bak.sr_level_pk = lv.sr_level_pk), level_pk) '||
1829 'where lv.instance = ''' || p_instance ||
1830 ''' and lv.level_id = ' || p_level_id;
1831
1832 execute immediate v_sql;
1833
1834 end revert_level_values;
1835
1836 /* For Debugging purpose */
1837 Procedure show_line(p_sql in varchar2) is
1838 i number:=1;
1839 Begin
1840 while i<= length(p_sql)
1841 loop
1842 -- dbms_output.put_line (substr(p_sql, i, 255));
1843 fnd_file.put_line(fnd_file.log,substr(p_sql, i, 255));
1844 null;
1845 i := i+255;
1846 end loop;
1847 End;
1848
1849 Procedure debug_line(p_sql in varchar2)is
1850 Begin
1851 if c_debug = 'Y' then
1852 show_line(p_sql);
1853 end if;
1854 End;
1855
1856 END ;