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