[Home] [Help]
PACKAGE BODY: APPS.EDW_GL_ACCT_M_C
Source
1 Package Body EDW_GL_ACCT_M_C AS
2 /* $Header: EDWVBHPB.pls 120.4 2006/03/10 03:34:08 rkumar noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 G_DEBUG Boolean:=false;
6 g_row_count Number:=0;
7 g_exception_msg varchar2(2000):=Null;
8 g_hie_temp_table_name varchar2(50);
9 g_value_temp_table varchar2(50);
10 g_value_set_temp_table varchar2(50);
11 g_value_orp_dup_table varchar2(50);
12 g_value_con_dup_table varchar2(50);
13 g_sob_vset_lookup_table varchar2(50);
14 g_dimension_name varchar2(30);
15 g_parallel_level varchar2(10);
16
17
18
19 PROCEDURE INITDEBUG IS
20 BEGIN
21 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
22 g_debug := true;
23 ELSE
24 g_debug := false;
25 END IF;
26 END INITDEBUG;
27
28 PROCEDURE VBHDEBUG(
29 p_log varchar2)
30 IS
31 BEGIN
32 if( g_debug) then
33 edw_log.debug_line(p_log);
34 end if;
35 END VBHDEBUG ;
36
37 -- simple timing tools, setTimer and logTime.
38 -- could be used for performance tuning.
39 PROCEDURE setTimer(
40 p_log_timstamp in out NOCOPY date)
41 IS
42 BEGIN
43 p_log_timstamp := sysdate;
44 END;
45
46
47 PROCEDURE logTime(
48 p_process varchar2,
49 p_log_timstamp date)
50 IS
51 l_duration number := null;
52 BEGIN
53 l_duration := sysdate - p_log_timstamp;
54 edw_log.put_line('Process Time for '|| p_process || ' : ' || edw_log.duration(l_duration));
55 edw_log.put_line('');
56 END;
57
58 procedure lookup_value_set_id(
59 p_set_of_books_id IN number
60 , p_instance_code IN VARCHAR2
61 , p_value_set_id OUT NOCOPY number)
62 AS
63 l_set_of_books_name varchar2(100);
64 l_select_stmt varchar2(2000);
65 l_cursor_id integer;
66 l_rows_inserted integer:=0;
67 BEGIN
68
69 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
70 l_select_stmt:=
71 'SELECT value_set_id
72 FROM EDW_FLEX_SEG_MAPPINGS_V@' || G_TARGET_LINK ||
73 ' WHERE DIMENSION_SHORT_NAME= :g_dimension_name
74 AND lower(INSTANCE_CODE)= lower(:p_instance_code)
75 AND structure_num=(SELECT chart_of_accounts_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id= :p_set_of_books_id)';
76
77 DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.NATIVE);
78
79 DBMS_SQL.bind_variable(l_cursor_id,'g_dimension_name',g_dimension_name);
80 DBMS_SQL.bind_variable(l_cursor_id,'p_instance_code',p_instance_code);
81 DBMS_SQL.bind_variable(l_cursor_id,'p_set_of_books_id',p_set_of_books_id);
82
83 DBMS_SQL.define_column(l_cursor_id,1,p_value_set_id);
84
85 l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
86
87 IF DBMS_SQL.fetch_rows(l_cursor_id) > 0 THEN
88 DBMS_SQL.column_value(l_cursor_id,1,p_value_set_id);
89 ELSE
90 select set_of_books_name into l_set_of_books_name
91 from edw_local_set_of_books
92 where lower(p_instance_code)=lower(instance)
93 and set_of_books_id=p_set_of_books_id;
94 VBHDEBUG('No Segment mapped to '|| g_dimension_name||' for '|| l_set_of_books_name);
95 END IF;
96
97 DBMS_SQL.close_cursor(l_cursor_id);
98
99 EXCEPTION
100 when others then
101 p_value_set_id := null;
102 VBHDEBUG('Error: when looking up the value_set_id for '
103 ||p_set_of_books_id||' in dimension '|| g_dimension_name);
104 raise;
105 END lookup_value_set_id;
106
107 function is_multiple_target
108 return integer as
109 l_impl integer := 0;
110 l_dummy integer := 0;
111 l_stmt varchar2(200):=0 ;
112 l_cursor_id integer;
113
114 begin
115
116 l_cursor_id:= dbms_sql.open_cursor;
117 l_stmt := 'select COUNT(*) from FND_LOOKUP_VALUES ' ||
118 'where ENABLED_FLAG = ''Y'' and LOOKUP_TYPE = ''EDW_OBJECTS_TO_LOAD'' and LOOKUP_CODE = ''' ||
119 g_dimension_name || '''';
120
121
122 DBMS_SQL.parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
123 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_impl);
124 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor_id);
125 DBMS_SQL.column_value(l_cursor_id,1,l_impl);
126 DBMS_SQL.close_cursor(l_cursor_id);
127
128 return l_impl;
129 end is_multiple_target;
130
131
132 procedure insert_into_temp_table(
133 p_temp_table_name IN VARCHAR2,
134 p_parent IN varchar2,
135 p_parent_name in varchar2,
136 p_parent_desc in varchar2,
137 p_child in varchar2,
138 p_child_name in varchar2,
139 p_child_desc in varchar2,
140 p_rows_inserted out NOCOPY integer) as
141
142 l_insert_stmt varchar2(20000);
143 l_cursor_id integer;
144 l_rows_inserted integer:=0;
145 begin
146
147 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
148 l_insert_stmt:= 'INSERT INTO ' || p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc)
149 values(:b_parent,:b_parent_name,:b_parent_desc,:b_child,:b_child_name,:b_child_desc)';
150 VBHDEBUG('Going to execute '|| l_insert_stmt);
151 DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
152 DBMS_SQL.bind_variable(l_cursor_id,':b_parent',p_parent);
153 DBMS_SQL.bind_variable(l_cursor_id,':b_parent_name',p_parent_name);
154 DBMS_SQL.bind_variable(l_cursor_id,':b_parent_desc',p_parent_desc);
155 DBMS_SQL.bind_variable(l_cursor_id,':b_child',p_child);
156 DBMS_SQL.bind_variable(l_cursor_id,':b_child_name',p_child_name);
157 DBMS_SQL.bind_variable(l_cursor_id,':b_child_desc',p_child_desc);
158 l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
159 p_rows_inserted:=l_rows_inserted;
160 DBMS_SQL.close_cursor(l_cursor_id);
161 exception
162 when others then
163 p_rows_inserted := 0;
164 VBHDEBUG('error: when inserting '||p_parent||','||p_child||' into ' || p_temp_table_name );
165 DBMS_SQL.close_cursor(l_cursor_id);
166 end insert_into_temp_table ;
167
168 procedure clean_up_temp_table(
169 p_temp_table_name in varchar2)
170 as
171 l_cursor_id integer;
172 l_rows_deleted integer:=0;
173 l_temp_value varchar2(50);
174 l_delete_stmt varchar2(200);
175 begin
176 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
177 l_temp_value:='%'||g_instance_code||'%';
178 l_delete_stmt:='delete from ' || p_temp_table_name || ' where child like :b_temp_value or (child is null and parent like :b_temp_value1)';
179 DBMS_SQL.parse(l_cursor_id,l_delete_stmt,DBMS_SQL.V7);
180 DBMS_SQL.bind_variable(l_cursor_id,':b_temp_value',l_temp_value);
181 DBMS_SQL.bind_variable(l_cursor_id,':b_temp_value1',l_temp_value);
182 l_rows_deleted:=DBMS_SQL.execute(l_cursor_id);
183 commit;
184 DBMS_SQL.close_cursor(l_cursor_id);
185 VBHDEBUG('Removed ' || l_rows_deleted || ' rows from ' || p_temp_table_name );
186
187 exception
188 when others then
189 VBHDEBUG('error: when cleaning up the temp table');
190 DBMS_SQL.close_cursor(l_cursor_id);
191 raise;
192 end clean_up_temp_table ;
193
194 function get_db_user(
195 p_product varchar2) return varchar2
196 is
197 l_dummy1 varchar2(2000);
198 l_dummy2 varchar2(2000);
199 l_schema varchar2(400);
200 Begin
201 if FND_INSTALLATION.GET_APP_INFO(p_product,l_dummy1, l_dummy2,l_schema) = false then
202 VBHDEBUG('FND_INSTALLATION.GET_APP_INFO returned with error');
203 return null;
204 end if;
205 return l_schema;
206 Exception when others then
207 VBHDEBUG('Error in get_db_user '||sqlerrm);
208 return null;
209 End;
210
211 function check_table(
212 p_table varchar2 ) return boolean
213 is
214 l_stmt varchar2(10000);
215 TYPE CurTyp IS REF CURSOR;
216 cv CurTyp;
217 Begin
218 VBHDEBUG('check_table for '||p_table);
219
220 begin
221 l_stmt:='select 1 from '||p_table||' where rownum=1';
222 open cv for l_stmt;
223 close cv;
224 VBHDEBUG('Table found');
225 return true;
226 exception when others then
227 VBHDEBUG('Table NOT found');
228 return false;
229 end;
230 Exception when others then
231 VBHDEBUG('Exception in check_table '||sqlerrm);
232 return false;
233 End;
234
235
236 procedure drop_table (
237 p_table_name in varchar2)
238 is
239 l_stmt varchar2(400);
240 Begin
241 if check_table( p_table_name) then
242 l_stmt:='drop table '|| p_table_name;
243 VBHDEBUG('Going to execute '||l_stmt);
244 execute immediate l_stmt;
245 end if;
246 Exception when others then
247 VBHDEBUG('Error in drop_table '||sqlerrm);
248 raise;
249 End;
250
251
252
253 -- -----------------------------------------------------------------------------
254 -- create_vbh_val_set_temp_tbl will get parallel level from profile option.
255 -- Created to tune up performance for orphans and parent-child pair insertions.
256 -- It is used by create_vbh_temp_table
257 -- -----------------------------------------------------------------------------
258 procedure create_vbh_sob_vset_lookup_tbl is
259 l_stmt varchar2(2000);
260 l_paral_clause varchar2(100) := null;
261 l_owner varchar2(30);
262 l_log_timstamp Date:=Null;
263 l_sob_vset_lookup_table varchar2(100);
264 begin
265 setTimer(l_log_timstamp);
266 l_owner:= get_db_user('BIS');
267 l_sob_vset_lookup_table := g_sob_vset_lookup_table;
268 if l_owner is not null then
269 g_sob_vset_lookup_table:= l_owner||'.'|| g_sob_vset_lookup_table;
270 end if;
271
272 drop_table(g_sob_vset_lookup_table);
273
274 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
275 execute immediate l_stmt;
276 commit;
277
278 if(g_parallel_level is not null) then
279 l_paral_clause := 'parallel (degree ' || g_parallel_level || ' )';
280 end if;
281
282 l_stmt:= 'create table ' || g_sob_vset_lookup_table ||
283 ' storage (initial 5M next 1M pctincrease 0) '|| l_paral_clause || '
284 as select b.edw_set_of_books_id, b.instance, b.set_of_books_id,
285 b.set_of_books_name, b.chart_of_accounts_id,
286 b.description, c.value_set_id
287 from
288 (SELECT distinct *
289 FROM edw_local_set_of_books
290 WHERE instance IN (
291 select instance_code
292 from edw_local_instance )
293 AND edw_set_of_books_id NOT IN(
294 SELECT DISTINCT edw_set_of_books_id
295 FROM edw_local_equi_set_of_books)
296 ) B,
297 EDW_FLEX_SEG_MAPPINGS_V@' || G_TARGET_LINK || ' C
298 where C.DIMENSION_SHORT_NAME = ''' || g_dimension_name|| '''
299 AND lower(C.INSTANCE_CODE)= lower(B.INSTANCE)
300 AND c.structure_num=(SELECT chart_of_accounts_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id= b.set_of_books_id)';
301
302 VBHDEBUG('executing '|| l_stmt);
303 execute immediate l_stmt;
304 commit;
305
306 --bug fix 3355535
307 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_sob_vset_lookup_table);
308
309 logTime('Creating SOB-Value_set Lookup Table', l_log_timstamp);
310
311 exception
312 when others then
313 VBHDEBUG('error: recreating the '|| g_sob_vset_lookup_table||' table.');
314 raise;
315
316 end;
317
318
319
320
321 -- -----------------------------------------------------------------------------
322 -- create_vbh_val_set_temp_tbl will get parallel level from profile option.
323 -- Created to avoid duplicates and tune up performance. It is used by
324 -- create_vbh_temp_table
325 -- -----------------------------------------------------------------------------
326 procedure create_vbh_val_set_temp_tbl is
327 l_stmt varchar2(2000);
328 l_paral_clause varchar2(100) := null;
329 l_owner varchar2(30);
330 l_log_timstamp Date:=Null;
331 l_value_set_temp_table varchar2(100);
332
333 begin
334
335 setTimer(l_log_timstamp);
336 l_owner:= get_db_user('BIS');
337 l_value_set_temp_table := g_value_set_temp_table;
338 if l_owner is not null then
339 g_value_set_temp_table:= l_owner||'.'|| g_value_set_temp_table;
340 end if;
341
342 drop_table(g_value_set_temp_table);
343
344 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
345 execute immediate l_stmt;
346 commit;
347
348 if(g_parallel_level is not null) then
349 l_paral_clause := 'parallel (degree ' || g_parallel_level || ' )';
350 end if;
351
352 l_stmt:= 'create table '|| g_value_set_temp_table ||' storage (initial 5M next 1M pctincrease 0) '|| l_paral_clause ||
353 ' as select distinct value_set_id from edw_flex_seg_mappings_v@' || G_TARGET_LINK ||
354 ' where dimension_short_name = '''|| g_dimension_name||'''';
355
356
357 VBHDEBUG('executing '|| l_stmt);
358 execute immediate l_stmt;
359 commit;
360
361 --bug fix 3355535
362 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_value_set_temp_table);
363
364 logTime('Creating Value_set Temp table', l_log_timstamp);
365 exception
366 when others then
367 VBHDEBUG('error: recreating the '|| g_value_set_temp_table||' table.');
368 raise;
369
370 end;
371
372
373 -- -----------------------------------------------------------------------------
374 -- create_vbh_temp_table will get parallel level from profile option for the hierarchy
375 -- temp table, created for performance.
376 -- -----------------------------------------------------------------------------
377 procedure create_vbh_temp_table is
378 l_stmt varchar2(2000);
379 l_drop_stmt varchar2(200);
380 l_cursor_id number;
381 l_owner varchar2(30);
382 l_result boolean;
383 l_paral_clause varchar2(100) := null;
384 l_log_timstamp Date:=Null;
385 l_hie_temp_table_name varchar2(100);
386
387
388 begin
389 setTimer(l_log_timstamp);
390 l_owner:= get_db_user('BIS');
391 l_hie_temp_table_name :=g_hie_temp_table_name;
392 if l_owner is not null then
393 g_hie_temp_table_name:= l_owner||'.'|| g_hie_temp_table_name;
394 end if;
395 drop_table(g_hie_temp_table_name);
396 commit;
397 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
398 execute immediate l_stmt;
399
400 if(g_parallel_level is not null) then
401 l_paral_clause := 'parallel (degree ' || g_parallel_level || ' )';
402 end if;
403
404 /*Added g_value_set_temp_table table to inner query to improve performance bug 3222635 */
405
406 l_stmt:= 'create table '||g_hie_temp_table_name||
407 ' storage (initial 5M next 1M pctincrease 0) '|| l_paral_clause || ' as '||
408 'select a.flex_value_set_id,a.parent_flex_value, c.description parent_desc,'||
409 'a.flex_value,a.description,a.summary_flag from
410 ( SELECT v.flex_value_set_id, h.parent_flex_value,
411 v.flex_value, v.description, v.summary_flag
412 FROM fnd_flex_values_vl v, fnd_flex_value_norm_hierarchy h,
413 fnd_flex_value_sets s, '||g_value_set_temp_table||' vst
414 WHERE vst.value_set_id = v.flex_value_set_id
415 AND h.flex_value_set_id = v.flex_value_set_id
416 AND s.flex_value_set_id = v.flex_value_set_id
417 AND (((s.format_type NOT IN (''N'',''D'', ''T''))
418 AND ( v.flex_value BETWEEN h.child_flex_value_low AND
419 h.child_flex_value_high)))
423 || ' and c.flex_value_set_id=a.flex_value_set_id and a.parent_flex_value=c.flex_value';
420 AND ( (v.summary_flag = ''Y'' AND h.range_attribute = ''P'')
421 OR (v.summary_flag = ''N'' AND h.range_attribute = ''C''))) a, ' || g_value_set_temp_table || ' b,'
422 || g_value_temp_table||' c where a.flex_value_set_id = b.value_set_id '
424
425 VBHDEBUG('Going to execute '|| l_stmt);
426
427 execute immediate l_stmt;
428 commit;
429 VBHDEBUG('Created table '||g_hie_temp_table_name);
430
431
432 ----------fix bug 2356452
433 l_stmt:='create index '||g_hie_temp_table_name||'_N1'||' on '||g_hie_temp_table_name||'(flex_value_set_id)';
434 VBHDEBUG('Executing statement: '||l_stmt);
435 execute immediate l_stmt;
436 VBHDEBUG('Created index on '||g_hie_temp_table_name||'(flex_value_set_id)');
437 commit;
438 --bug fix 3355535
439 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_hie_temp_table_name);
440
441 logTime('Creating hierarchy temp table', l_log_timstamp);
442 exception
443 when others then
444 VBHDEBUG('error: recreating the '|| g_hie_temp_table_name||' table.');
445 raise;
446 end;
447
448 -- -----------------------------------------------------------------------------
449 -- create_value_temp_table will get parallel level from profile option for the value
450 -- temp table, created for performance.
451 -- -----------------------------------------------------------------------------
452 procedure create_value_temp_table is
453 l_stmt varchar2(1000);
454 l_drop_stmt varchar2(200);
455 l_cursor_id number;
456 l_owner varchar2(30);
457 l_result boolean;
458 l_paral_clause varchar2(100) := null;
459 l_log_timstamp Date:=Null;
460 l_value_temp_table varchar2(100);
461 begin
462 setTimer(l_log_timstamp);
463 l_owner:= get_db_user('BIS');
464 l_value_temp_table := g_value_temp_table;
465 if l_owner is not null then
466 g_value_temp_table:= l_owner||'.'|| g_value_temp_table;
467 end if;
468 drop_table(g_value_temp_table);
469
470 commit;
471 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
472 execute immediate l_stmt;
473 commit;
474
475 if(g_parallel_level is not null) then
476 l_paral_clause := 'parallel (degree ' || g_parallel_level || ' )';
477 end if;
478
479 l_stmt:= 'create table '|| g_value_temp_table ||' storage (initial 5M next 1M pctincrease 0) '|| l_paral_clause ||
480 ' as select distinct flex_value_set_id,flex_value,description,summary_flag, ENABLED_FLAG '||
481 ' from fnd_flex_values_vl where flex_value_set_id in '||
482 '(select value_set_id from edw_flex_seg_mappings_v@' ||
483 G_TARGET_LINK ||
484 ' where dimension_short_name = '''|| g_dimension_name||''') '||
485 'UNION '||
486 'select flex_value_set_id, value_column_name, meaning_column_name, summary_column_name, ''Y'' '||
487 ' from FND_FLEX_VALIDATION_TABLES '||
488 ' WHERE FLEX_VALUE_SET_ID in '||
489 ' (select value_set_id from edw_flex_seg_mappings_v@'||
490 G_TARGET_LINK||
491 ' where value_set_type=''F'' and dimension_short_name = '''||g_dimension_name||''')';
492 --Added the above UNION for bug 4081205
493 VBHDEBUG('Going to execute '|| l_stmt);
494
495 execute immediate l_stmt;
496 commit;
497
498
499 ----------fix bug 2356452
500 -- Modified to for index reduction project (4542654)
501 -- l_stmt:='create index '||g_value_temp_table||'_N1'||' on '||g_value_temp_table||'(flex_value)';
502 -- VBHDEBUG('Executing statement: '||l_stmt);
503 -- execute immediate l_stmt;
504 -- VBHDEBUG('Created index on '||g_value_temp_table||'(flex_value)');
505
506
507 -- l_stmt:='create index '||g_value_temp_table||'_N2'||' on '||g_value_temp_table||'(flex_value_set_id)';
508 -- VBHDEBUG('Executing statement: '||l_stmt);
509 -- execute immediate l_stmt;
510 -- VBHDEBUG('Created index on '||g_value_temp_table||'(flex_value_set_id)');
511
512
513 -- l_stmt:='create index '||g_value_temp_table||'_N3'||' on '||g_value_temp_table||'(flex_value, flex_value_set_id)';
514 -- VBHDEBUG('Executing statement: '||l_stmt);
515 -- execute immediate l_stmt;
516 -- VBHDEBUG('Created index on '||g_value_temp_table||'(flex_value, flex_value_set_id)');
517
518 commit;
519
520 --bug fix 3355535
521 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_value_temp_table);
522
523 logTime('Creating value temp table', l_log_timstamp);
524 exception
525 when others then
526 VBHDEBUG('error: recreating the '|| g_value_temp_table||' table.');
527 raise;
528 end;
529
530 function bulk_push_orphans(
531 p_temp_table_name VARCHAR2
532 ) return number as
533
534 l_log_timstamp Date:=Null;
535 l_stmt varchar2(1000);
536 l_temp_insert_count number := 0;
537
538 begin
539 l_stmt:= 'INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc) '
540 || 'select a.flex_value||''-''||b.set_of_books_id||''-''||b.instance, a.flex_value,a.description, NULL, NULL,NULL'
541 ||' FROM (select flex_value_set_id, flex_value,description from '|| g_value_temp_table
542 ||' minus '||
546 || ' )) a, '
543 '(select flex_value_set_id,flex_value,description from ' || g_hie_temp_table_name
544 || ' union all '
545 || ' select flex_value_set_id, parent_flex_value,parent_desc from ' || g_hie_temp_table_name
547 || g_sob_vset_lookup_table || ' b'
548 || ' WHERE b.value_set_id = a.flex_value_set_id';
549
550 VBHDEBUG('Going to execute '||l_stmt);
551
552 setTimer(l_log_timstamp);
553 execute immediate l_stmt;
554 l_temp_insert_count:=sql%rowcount;
555 VBHDEBUG('inserted '|| l_temp_insert_count||' stand alone nodes into '|| p_temp_table_name );
556 logTime('Orphans', l_log_timstamp);
557 return l_temp_insert_count;
558
559 end bulk_push_orphans;
560
561 -- replaced by bulk_push_orphans
562 function push_orphans(
563 p_temp_table_name VARCHAR2,
564 p_set_of_books edw_local_set_of_books%ROWTYPE,
565 p_value_set_id FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID%TYPE
566 ) return number as
567
568 l_log_timstamp Date:=Null;
569 l_stmt varchar2(1000);
570 l_temp_insert_count number := 0;
571
572 begin
573
574 l_stmt:='INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc)
575 '|| 'select a.flex_value||''-''||'''
576 ||p_set_of_books.set_of_books_id||'''||''-''||'''
577 ||p_set_of_books.instance||
578 ''',a.flex_value,a.description, NULL, NULL,NULL'
579 ||' FROM (select flex_value,description from '|| g_value_temp_table
580 ||' where flex_value_set_id= '|| p_value_set_id ||' minus '||
581 '(select flex_value,description from '
582 || g_hie_temp_table_name
583 ||' where flex_value_set_id='|| p_value_set_id||' union all '
584 ||' select parent_flex_value,parent_desc from '
585 || g_hie_temp_table_name
586 ||' where flex_value_set_id='|| p_value_set_id ||')) a';
587
588 VBHDEBUG('Going to execute '||l_stmt);
589
590 setTimer(l_log_timstamp);
591 execute immediate l_stmt;
592 l_temp_insert_count:=sql%rowcount;
593 VBHDEBUG('inserted '|| l_temp_insert_count||' stand alone nodes into '|| p_temp_table_name||' from '|| p_set_of_books.set_of_books_name);
594 logTime('Orphans', l_log_timstamp);
595 return l_temp_insert_count;
596 end push_orphans;
597
598 -- simple tools to get instance code of warehouse and source.
599 -- used to tell whether the configuration is single instance.
600 function get_source_instance_code
601 return varchar2 as
602 l_ins_code varchar2(100);
603 begin
604
605 --select instance.instance_name into l_ins_code from V$INSTANCE instance;
606 Select instance_code INTO l_ins_code FROM edw_local_instance;--added bug 3973264
607
608 IF l_ins_code is not NULL then
609 VBHDEBUG('Source Instance code ' || l_ins_code );
610 return l_ins_code;
611 ELSE
612 VBHDEBUG('No able to get instance code on source' );
613 return null;
614 END IF;
615 end get_source_instance_code;
616
617 function get_target_instance_code
618 return varchar2 as
619 l_stmt varchar2(100);
620 l_ins_code varchar2(100);
621 l_cursor_id integer;
622 l_rows_queried integer:=0;
623
624 begin
625 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
626 --l_stmt:= 'select instance.instance_name from V$INSTANCE@'|| G_TARGET_LINK ||' instance';
627 --added bug 3973264
628 l_stmt:= 'select instance.instance_code from edw_local_instance@'|| G_TARGET_LINK ||' instance';
629 DBMS_SQL.parse(l_cursor_id,l_stmt,DBMS_SQL.NATIVE);
630 DBMS_SQL.define_column(l_cursor_id,1,l_ins_code, 255);
631 l_rows_queried := DBMS_SQL.execute(l_cursor_id);
632
633 IF DBMS_SQL.fetch_rows(l_cursor_id) > 0 THEN
634 DBMS_SQL.column_value(l_cursor_id,1,l_ins_code);
635 VBHDEBUG('Target Instance code ' || l_ins_code );
636 return l_ins_code;
637 ELSE
638 VBHDEBUG('No able to get instance code on ' || G_TARGET_LINK);
639 return null;
640 END IF;
641
642 end get_target_instance_code;
643
644 function is_single_instance return boolean as
645 l_source_instance varchar2(100);
646 l_target_instance varchar2(100);
647 begin
648 l_source_instance := get_source_instance_code;
649 l_target_instance := get_target_instance_code;
650
651 if ( l_source_instance = l_target_instance) then
652 VBHDEBUG('Source and Target on the same instance');
653 return TRUE;
654 else
655 VBHDEBUG('Source and Target on different instance');
656 return FALSE;
657 end if;
658 end;
659
660
661 -- since the existence of the edw_vbh_temp# tables depends on the configuration,
662 -- this procedure is used to create edw_vbh_temp# tables on the fly if necessary.
663 -- we could consider delivering those staging tables to local source in the future,
664 -- but this procedure will be forwardly/backwordly compatible.
665 procedure prepare_source_temp_table(
666 p_source_temp_table_name in out NOCOPY varchar2 ,
667 p_target_temp_table_name in varchar2
668 ) is
669 l_source_instance varchar2(100);
673 l_retcode number;
670 l_target_instance varchar2(100);
671 l_stmt varchar2(100);
672 l_errbuf varchar2(100);
674 l_owner varchar2(30);
675 l_source_temp_table_name varchar2(100);
676 begin
677 if is_single_instance then
678 return;
679 end if;
680 l_source_temp_table_name := p_source_temp_table_name;
681 l_owner:= get_db_user('BIS');
682 p_source_temp_table_name := l_owner||'.' || p_source_temp_table_name;
683 if check_table(p_source_temp_table_name) then
684 return;
685 end if;
686
687 l_stmt := 'create table ' || p_source_temp_table_name || ' as select * from ' || p_target_temp_table_name
688 || ' where 1 = 2 ';
689 VBHDEBUG('Source temp being created with statement: ' || l_stmt);
690 VBHDEBUG('');
691 execute immediate l_stmt;
692
693 --bug fix 3355535
694 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_source_temp_table_name);
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 l_errbuf :=sqlerrm;
699 l_retcode:=sqlcode;
700 p_source_temp_table_name := null;
701 if( l_retcode <> -955 ) then
702 -- should never come here
703 VBHDEBUG('error code : ' || sqlcode);
704 VBHDEBUG('error message : ' || sqlerrm);
705 VBHDEBUG('');
706 raise;
707 else
708 VBHDEBUG(p_source_temp_table_name || ' already exists');
709 VBHDEBUG('');
710 end if;
711 end prepare_source_temp_table;
712
713 -- this function will push data from source staging table to target.
714 -- since it is a one time push, it improves the performance.
715 function rep_from_src_to_target (
716 p_source_temp_table_name varchar2,
717 p_target_temp_table_name varchar2
718 ) return number as
719
720 l_temp_insert_count number := 0;
721 l_temp_delete_count number := 0;
722 l_log_timstamp Date:=Null;
723 l_stmt varchar2(1000);
724 l_source_instance varchar2(100);
725 l_target_instance varchar2(100);
726
727 BEGIN
728
729 if is_single_instance then
730 return 0;
731 else
732 /*
733 l_stmt := 'delete from '|| p_target_temp_table_name ;
734 VBHDEBUG('Going to execute '||l_stmt);
735 setTimer(l_log_timstamp);
736 execute immediate l_stmt;
737 */
738 clean_up_temp_table(p_target_temp_table_name);
739
740 l_temp_delete_count:=sql%rowcount;
741 logTime('clean up the temp table at target', l_log_timstamp);
742 commit;
743
744
745 l_stmt := 'INSERT INTO '|| p_target_temp_table_name ||
746 ' SELECT * from ' || p_source_temp_table_name;
747
748 VBHDEBUG('Going to execute '||l_stmt);
749 setTimer(l_log_timstamp);
750 execute immediate l_stmt;
751
752 l_temp_insert_count:=sql%rowcount;
753 logTime('replicating temp table from source to warehouse', l_log_timstamp);
754 commit;
755 end if;
756 return l_temp_insert_count;
757
758 EXCEPTION
759 WHEN OTHERS THEN
760 VBHDEBUG('Error: when replicating temp table from source to warehouse');
761 return 0;
762
763 end rep_from_src_to_target;
764
765
766 function bulk_push_parent_child_pair(
767 p_temp_table_name VARCHAR2
768 ) return number as
769
770 l_log_timstamp Date:=Null;
771 l_stmt varchar2(1000);
772 l_temp_insert_count number := 0;
773
774 begin
775 l_stmt:= 'INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc) '
776 || ' select a.parent_flex_value||''-''||b.set_of_books_id||''-''||b.instance, a.parent_flex_value, a.parent_desc,'
777 || ' a.flex_value||''-''||b.set_of_books_id||''-''||b.instance, a.flex_value, a.description FROM '
778 || g_hie_temp_table_name || ' A,'
779 || g_sob_vset_lookup_table || ' B'
780 || ' where a.flex_value_set_id = b.value_set_id ';
781
782 VBHDEBUG('Going to execute '||l_stmt);
783 setTimer(l_log_timstamp);
784 execute immediate l_stmt;
785
786 l_temp_insert_count:=sql%rowcount;
787
788 VBHDEBUG('inserted '|| l_temp_insert_count||' parent-child pairs into '|| p_temp_table_name);
789 logTime('PC pair', l_log_timstamp);
790 return l_temp_insert_count;
791
792 end bulk_push_parent_child_pair;
793
794
795 -- replaced by bulk_push_parent_child_pair
796 function push_parent_child_pair(
797 p_temp_table_name VARCHAR2,
798 p_set_of_books edw_local_set_of_books%ROWTYPE,
799 p_value_set_id FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID%TYPE
800 ) return number as
801
802 l_log_timstamp Date:=Null;
803 l_stmt varchar2(1000);
804 l_temp_insert_count number := 0;
805
806 begin
807 l_stmt:='INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc) '||
808 ' select parent_flex_value||''-''||'''
809 ||p_set_of_books.set_of_books_id||'''||''-''||'''
810 ||p_set_of_books.instance||
814 ''',flex_value,description FROM '
811 ''',parent_flex_value,parent_desc,flex_value||''-''||'''
812 ||p_set_of_books.set_of_books_id||'''||''-''||'''
813 ||p_set_of_books.instance||
815 || g_hie_temp_table_name
816 ||' where flex_value_set_id = '||p_value_set_id;
817
818 VBHDEBUG('Going to execute '||l_stmt);
819 setTimer(l_log_timstamp);
820 execute immediate l_stmt;
821
822 l_temp_insert_count:=sql%rowcount;
823
824 VBHDEBUG('inserted '|| l_temp_insert_count||' parent-child pairs into '|| p_temp_table_name ||' from '|| p_set_of_books.set_of_books_name);
825 logTime('PC pair', l_log_timstamp);
826 return l_temp_insert_count;
827 end push_parent_child_pair;
828
829
830 -- In the case of dependent value set values got pulled in,
831 -- duplicates will be intruduced, the following two functions serve as
832 -- current workaround to cleanup the duplicates. To full support the dependent value set,
833 -- need enhancement on VBH design and Consolidation API provided by FII.
834 function remove_dup_from_orphans(
835 p_temp_table_name VARCHAR2,
836 p_dups_removed number
837 ) return number
838 as
839 TYPE curType IS REF CURSOR ;
840 l_log_timstamp Date:=Null;
841 l_stmt varchar2(1000);
842 l_temp_insert_count number := 0;
843 cur_parent_dup curType;
844 l_dups_removed number := 0;
845 l_count number := 0;
846 l_deleted_count number := 0;
847 l_parent varchar2(1000);
848 begin
849 l_dups_removed := p_dups_removed;
850 VBHDEBUG('') ;
851 VBHDEBUG('Get rid of those duplicate parent names among the orphans.') ;
852 l_stmt := 'SELECT parent, count(*) count from ' || p_temp_table_name ||
853 ' WHERE child IS NULL GROUP BY parent having count(*) > 1';
854 VBHDEBUG('Executing ' || l_stmt);
855 open cur_parent_dup for l_stmt;
856 loop
857 FETCH cur_parent_dup INTO l_parent, l_count;
858 exit when cur_parent_dup%NOTFOUND;
859
860 l_stmt := 'DELETE FROM ' || p_temp_table_name ||
861 ' WHERE parent = '''|| l_parent ||''' AND child IS NULL AND ROWNUM < ' || l_count;
862 VBHDEBUG('Executing ' || l_stmt);
863 execute immediate l_stmt;
864 l_deleted_count:=sql%rowcount;
865 VBHDEBUG(l_deleted_count || ' duplicate rows got removed for ' || l_parent );
866 l_dups_removed := l_dups_removed + l_deleted_count;
867 end loop;
868 VBHDEBUG(l_deleted_count || ' duplicate orphan rows got removed' );
869 close cur_parent_dup;
870 return l_dups_removed;
871 EXCEPTION
872 WHEN OTHERS THEN
873 close cur_parent_dup;
874 VBHDEBUG('Error: when removing the rows with duplicated parent names');
875
876 end remove_dup_from_orphans;
877
878
879 procedure create_vbh_orp_dup_tbl(
880 p_temp_table_name VARCHAR2)
881 is
882 l_stmt varchar2(3000);
883 l_paral_clause varchar2(100) := null;
884 l_owner varchar2(30);
885 l_log_timstamp Date:=Null;
886 l_value_orp_dup_table varchar2(100);
887
888 begin
889
890 setTimer(l_log_timstamp);
891 l_owner:= get_db_user('BIS');
892 l_value_orp_dup_table := g_value_orp_dup_table;
893 if l_owner is not null then
894 g_value_orp_dup_table:= l_owner||'.'|| g_value_orp_dup_table;
895 end if;
896
897 drop_table(g_value_orp_dup_table);
898 /*
899 VBHDEBUG('ALTER SESSION ENABLE PARALLEL DML');
900 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
901 execute immediate l_stmt;
902 commit;
903 */
904 if(g_parallel_level is not null) then
905 l_paral_clause := 'parallel (degree ' || g_parallel_level || ' )';
906 end if;
907
908 VBHDEBUG('executing '|| l_stmt);
909 l_stmt:= 'create table '|| g_value_orp_dup_table ||' storage (initial 5M next 1M pctincrease 0) '|| l_paral_clause ||
910 ' as select a.parent, max(a.rowid) rep , count(*) count
911 from ' || p_temp_table_name || ' a
912 where a.child is null
913 group by a.parent
914 having count(*) > 1 ';
915 VBHDEBUG('executing '|| l_stmt);
916 execute immediate l_stmt;
917
918 l_stmt:='create index '||g_value_orp_dup_table||'_N1'||' on '||g_value_orp_dup_table||'(parent)';
919 execute immediate l_stmt;
920
921 commit;
922
923 --bug fix 3355535
924 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_value_orp_dup_table);
925
926 logTime('Creating orphan value duplication holder table', l_log_timstamp);
927 exception
928 when others then
929 VBHDEBUG('error: recreating the '|| g_value_orp_dup_table ||' table.');
930 raise;
931
932 end create_vbh_orp_dup_tbl;
933
934
935
936 function bulk_remove_dup_from_orphans(
937 p_temp_table_name VARCHAR2,
938 p_dups_removed number
939 ) return number
940 as
941 l_log_timstamp Date:=Null;
942 l_stmt varchar2(1000);
943 l_temp_insert_count number := 0;
944 l_dups_removed number := 0;
945 l_count number := 0;
946 l_deleted_count number := 0;
947 l_parent varchar2(1000);
951 create_vbh_orp_dup_tbl(p_temp_table_name);
948 begin
949 l_dups_removed := p_dups_removed;
950
952
953 setTimer(l_log_timstamp);
954 VBHDEBUG('') ;
955 VBHDEBUG('Get rid of those duplicate parent names among the orphans.') ;
956 l_stmt := 'delete from ' || p_temp_table_name || ' a
957 where
958 a.child is null
959 and exists(
960 select 1 from
961 ' || g_value_orp_dup_table || ' b
962 where a.parent = b.parent
963 and a.rowid <> b.rep )';
964 VBHDEBUG('Executing ' || l_stmt);
965 execute immediate l_stmt;
966 l_deleted_count := sql%rowcount;
967 VBHDEBUG(l_deleted_count || ' duplicate orphan rows got removed' );
968 l_dups_removed := l_dups_removed + l_deleted_count;
969 logTime('removing dups from orphans', l_log_timstamp);
970
971 return l_dups_removed;
972 EXCEPTION
973 WHEN OTHERS THEN
974 VBHDEBUG('Error: when removing the rows with duplicated parent names');
975 raise;
976 end bulk_remove_dup_from_orphans;
977
978
979 procedure create_vbh_con_dup_tbl(
980 p_temp_table_name VARCHAR2)
981 is
982 l_stmt varchar2(3000);
983 l_paral_clause varchar2(100) := null;
984 l_owner varchar2(30);
985 l_log_timstamp Date:=Null;
986 l_value_con_dup_table varchar2(100);
987
988 begin
989
990 setTimer(l_log_timstamp);
991 l_owner:= get_db_user('BIS');
992 l_value_con_dup_table := g_value_con_dup_table;
993 if l_owner is not null then
994 g_value_con_dup_table:= l_owner||'.'|| g_value_con_dup_table;
995 end if;
996
997 drop_table(g_value_con_dup_table);
998
999 /*
1000 VBHDEBUG('ALTER SESSION ENABLE PARALLEL DML');
1001 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
1002 execute immediate l_stmt;
1003 commit;
1004 */
1005
1006 if(g_parallel_level is not null) then
1007 l_paral_clause := 'parallel (degree ' || g_parallel_level || ' )';
1008 end if;
1009
1010 VBHDEBUG('executing '|| l_stmt);
1011 l_stmt:= 'create table '|| g_value_con_dup_table ||' storage (initial 5M next 1M pctincrease 0) '||
1012 l_paral_clause ||
1013 ' as select a.parent, a.child, max(a.rowid) rep , count(*) count
1014 from ' || p_temp_table_name || ' a
1015 where a.child is not null
1016 group by a.parent, a.child
1017 having count(*) > 1 ';
1018 VBHDEBUG('executing '|| l_stmt);
1019 execute immediate l_stmt;
1020
1021 l_stmt:='create index '||g_value_con_dup_table||'_N1'||' on '||g_value_con_dup_table||'(parent, child)';
1022 execute immediate l_stmt;
1023
1024 commit;
1025
1026 --bug fix 3355535
1027 DBMS_STATS.GATHER_TABLE_STATS(l_owner,l_value_con_dup_table);
1028
1029 logTime('Creating consolidation value duplication holder table', l_log_timstamp);
1030 exception
1031 when others then
1032 VBHDEBUG('error: recreating the '|| g_value_con_dup_table ||' table.');
1033 raise;
1034
1035 end create_vbh_con_dup_tbl;
1036
1037 function bulk_remove_dup_from_cons(
1038 p_temp_table_name VARCHAR2,
1039 p_dups_removed number
1040 ) return number
1041 as
1042 l_log_timstamp Date:=Null;
1043 l_stmt varchar2(1000);
1044 l_temp_insert_count number := 0;
1045 l_dups_removed number := 0;
1046 l_count number := 0;
1047 l_deleted_count number := 0;
1048 l_parent varchar2(1000);
1049 begin
1050 l_dups_removed := p_dups_removed;
1051
1052 create_vbh_con_dup_tbl(p_temp_table_name);
1053
1054 setTimer(l_log_timstamp);
1055 VBHDEBUG('') ;
1056 VBHDEBUG('Get rid of those duplicate parent names among the consolidations.') ;
1057 l_stmt := 'delete from ' || p_temp_table_name || ' a
1058 where
1059 a.child is not null
1060 and exists(
1061 select 1 from
1062 ' || g_value_con_dup_table || ' b
1063 where a.parent = b.parent
1064 and a.child = b.child
1065 and a.rowid <> b.rep )';
1066 VBHDEBUG('Executing ' || l_stmt);
1067 execute immediate l_stmt;
1068 l_deleted_count := sql%rowcount;
1069 VBHDEBUG(l_deleted_count || ' duplicate consolidation rows got removed' );
1070 l_dups_removed := l_dups_removed + l_deleted_count;
1071 logTime('removing dups from consolidations', l_log_timstamp);
1072 return l_dups_removed;
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 VBHDEBUG('Error: when removing the rows with duplicated parent names');
1076 raise;
1077 end bulk_remove_dup_from_cons;
1078
1079
1080
1081 function remove_dup_from_consolidations(
1082 p_temp_table_name VARCHAR2,
1083 p_dups_removed VARCHAR2
1084 ) return number
1085 as
1086 TYPE curType IS REF CURSOR ;
1087 l_log_timstamp Date:=Null;
1088 l_stmt varchar2(1000);
1089 l_temp_insert_count number := 0;
1090 cur_parent_dup curType;
1091 l_parent varchar2(1000);
1092 l_child varchar2(1000);
1093 l_deleted_count number := 0;
1097 begin
1094 l_dups_removed number := 0;
1095 l_count number := 0;
1096
1098 l_dups_removed := p_dups_removed;
1099 VBHDEBUG('') ;
1100 VBHDEBUG('Get rid of those duplicates from consolidation.') ;
1101 l_stmt := 'SELECT parent, child, count(*) count from ' || p_temp_table_name || ' GROUP BY parent, child having count(*) > 1';
1102 VBHDEBUG('Executing ' || l_stmt);
1103 open cur_parent_dup for l_stmt;
1104 loop
1105 FETCH cur_parent_dup INTO l_parent, l_child, l_count;
1106 exit when cur_parent_dup%NOTFOUND;
1107 l_stmt := 'DELETE FROM ' || p_temp_table_name ||
1108 ' WHERE parent = '''|| l_parent ||''' AND child = '''||l_child||''' AND ROWNUM < ' || l_count;
1109 VBHDEBUG('Executing ' || l_stmt);
1110 execute immediate l_stmt;
1111 l_deleted_count:=sql%rowcount;
1112 VBHDEBUG(l_deleted_count || ' duplicate rows got removed for (' || l_parent ||',' || l_child ||')' );
1113 l_dups_removed := l_dups_removed + l_deleted_count;
1114 end loop;
1115 VBHDEBUG(l_deleted_count || ' duplicate consolidated rows got removed' );
1116 close cur_parent_dup;
1117 return l_dups_removed;
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 close cur_parent_dup;
1121 VBHDEBUG('Error: when removing the rows within duplicated consolidated relationships');
1122
1123 end remove_dup_from_consolidations;
1124
1125
1126 PROCEDURE Push(Errbuf out NOCOPY Varchar2,
1127 Retcode out NOCOPY Varchar2,
1128 p_from_date IN Varchar2,
1129 p_to_date IN Varchar2,
1130 p_dimension_name IN varchar2) IS
1131
1132 --L_PUSH_DATE_RANGE1 Date:=Null;
1133 --L_PUSH_DATE_RANGE2 Date:=Null;
1134 l_row_count Number:=0;
1135 l_exception_msg Varchar2(2000):=Null;
1136 l_source_temp_table_name VARCHAR2(100);
1137 l_target_temp_table_name VARCHAR2(100);
1138 l_temp_table_name VARCHAR2(100);
1139 l_temp_date Date:=Null;
1140 l_duration Number:=0;
1141 l_from_date Date:=Null;
1142 l_to_date Date:=Null;
1143 p_dimension_no INTEGER;
1144 -- -------------------------------------------
1145 -- Put any additional developer variables here
1146 -- -------------------------------------------
1147 l_value_set_id FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID%TYPE;
1148 l_parent_value_set_id FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID%TYPE;
1149 l_child_edw_set_of_books_id number;
1150 l_parent_value FND_FLEX_VALUE_CHILDREN_V.flex_value%TYPE;
1151 l_parent_desc FND_FLEX_VALUES_VL.description%TYPE;
1152 l_set_of_books edw_local_set_of_books%ROWTYPE;
1153 l_parent_set_of_books edw_local_set_of_books%ROWTYPE;
1154 l_edw_equi_sob edw_local_set_of_books%ROWTYPE;
1155 l_consolidation_id edw_local_cons_set_of_books.consolidation_id%TYPE;
1156 l_coa_mapping_id gl_consolidation.coa_mapping_id%TYPE;
1157 l_resurn_msg varchar2(150);
1158 l_rows_inserted Number:=0;
1159 l_dups_removed Number:=0;
1160 l_rows_replicated Number:=0;
1161 l_cons_error exception;
1162 l_insert_count integer:=0;
1163 l_dimension_name varchar2(150);
1164 l_log_timstamp Date:=Null;
1165 l_hie_temp_table_name varchar2(30);
1166 l_stmt varchar2(2000);
1167 l_temp_insert_count number := 0;
1168 l_cons_status boolean;
1169 l_cursor_id number;
1170 l_dummy_num number;
1171 TYPE curType IS REF CURSOR ;
1172 cur_parent_set_of_books curType;
1173 l_cur_set_of_books curType;
1174
1175 cur_coa_mapping_id curType;
1176
1177 TYPE t_edw_equi_sob IS REF CURSOR
1178 RETURN edw_local_set_of_books%ROWTYPE;
1179 cur_edw_equi_sob t_edw_equi_sob;
1180
1181
1182 TYPE t_fnd_flex_value_pair_rec IS RECORD(
1183 parent_flex_value FND_FLEX_VALUES_VL.flex_value%TYPE,
1184 parent_desc FND_FLEX_VALUES_VL.description%TYPE,
1185 child_flex_value FND_FLEX_VALUES_VL.flex_value%TYPE,
1186 child_desc FND_FLEX_VALUES_VL.description%TYPE
1187 );
1188
1189 l_fnd_flex_value_pair t_fnd_flex_value_pair_rec;
1190
1191 CUR_FND_FLEX_VALUE_PAIR curType;
1192 cur_desc curType;
1193
1194 TYPE t_flex_value_desc_rec IS RECORD(
1195 value FND_FLEX_VALUES_VL.flex_value%TYPE,
1196 description FND_FLEX_VALUES_VL.description%TYPE,
1197 parent_flag varchar2(1));
1198 l_flex_value_desc t_flex_value_desc_rec;
1199
1200 TYPE t_flex_value_orp_rec IS RECORD(
1201 value FND_FLEX_VALUES_VL.flex_value%TYPE,
1202 description FND_FLEX_VALUES_VL.description%TYPE);
1203
1204 l_flex_value_orp t_flex_value_orp_rec;
1205
1206 cur_flex_value_orp curType;
1207 cur_flex_value_desc curType;
1208
1209 CURSOR cur_set_of_books IS
1210 SELECT distinct *
1211 FROM edw_local_set_of_books
1212 WHERE instance IN (
1213 select instance_code
1214 from edw_local_instance
1215 )
1216 AND edw_set_of_books_id NOT IN(
1217 SELECT DISTINCT edw_set_of_books_id
1218 FROM edw_local_equi_set_of_books
1219 );
1220
1221 l_temp_stmt varchar2(1000);
1222 l_source_link VARCHAR2(128);
1223 BEGIN
1224 Errbuf :=NULL;
1225 Retcode:=0;
1226
1227
1231 -- get databaselink
1228 INITDEBUG;
1229
1230
1232 EDW_COLLECTION_UTIL.get_dblink_names(l_source_link, g_target_link);
1233
1234 l_from_date :=fnd_date.canonical_to_date(p_from_date);
1235 l_to_date :=fnd_date.canonical_to_date(p_to_date);
1236
1237 l_cursor_id:= dbms_sql.open_cursor;
1238 l_temp_stmt:='select dim_name from edw_dimensions_md_v@' || g_target_link ||
1239 ' where DIM_LONG_NAME = :b_dimension_name';
1240 DBMS_SQL.parse(l_cursor_id,l_temp_stmt,DBMS_SQL.V7);
1241 DBMS_SQL.bind_variable(l_cursor_id,':b_dimension_name',p_dimension_name);
1242 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,g_dimension_name,255);
1243 l_dummy_num:= DBMS_SQL.EXECUTE_AND_FETCH(l_cursor_id);
1244 DBMS_SQL.column_value(l_cursor_id,1,g_dimension_name);
1245 DBMS_SQL.close_cursor(l_cursor_id);
1246
1247 p_dimension_no := substr(g_dimension_name, 12 , instr(g_dimension_name, '_M') - 12 );
1248
1249 g_hie_temp_table_name:= g_dimension_name||'_HIE_TEMP';
1250 g_value_temp_table:= g_dimension_name||'_VAL_TEMP';
1251 g_value_set_temp_table:= g_dimension_name||'_VAL_SET_TEMP';
1252 g_sob_vset_lookup_table:= g_dimension_name||'_SOB_VSET_LKUP';
1253 g_value_orp_dup_table := g_dimension_name || '_ORP_DUP';
1254 g_value_con_dup_table := g_dimension_name || '_CON_DUP';
1255
1256 l_source_temp_table_name:='EDW_VBH_TEMP'||p_dimension_no;
1257 l_target_temp_table_name := l_source_temp_table_name || '@' || g_target_link;
1258
1259
1260 IF (Not EDW_COLLECTION_UTIL.setup(g_dimension_name)) THEN
1261 VBHDEBUG('setup failed');
1262 errbuf := fnd_message.get;
1263 Return;
1264 END IF;
1265
1266 VBHDEBUG('Got g_target_link ' || g_target_link );
1267
1268 IF (p_dimension_no<1) or (p_dimension_no>10) THEN
1269 edw_log.put_line( 'invalid dimension number '||p_dimension_no);
1270 Return;
1271 END IF;
1272 edw_log.put_line('Collect program for '|| p_dimension_name ||' dimension');
1273 edw_log.put_line('Dimension number '|| p_dimension_no);
1274 edw_log.put_line('Dimension physical name : '||g_dimension_name);
1275
1276 EDW_GL_ACCT_M_C.g_push_date_range1 := nvl(l_from_date,
1277 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
1278 EDW_GL_ACCT_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
1279 EDW_LOG.PUT_LINE( 'The collection range is from '||to_char(EDW_GL_ACCT_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||to_char(EDW_GL_ACCT_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
1280 EDW_LOG.PUT_LINE(' ');
1281
1282 select FND_PROFILE.VALUE('EDW_PARALLEL_SRC') into g_parallel_level from dual;
1283 if ( g_parallel_level is not null ) then
1284 EDW_LOG.PUT_LINE('parallelism is set to ' || g_parallel_level);
1285 else
1286 EDW_LOG.PUT_LINE('no parallelism');
1287 end if;
1288 EDW_LOG.PUT_LINE(' ');
1289
1290
1291 -- -----------------------------------------------------------------------------
1292 -- Start of Collection , Developer Customizable Section
1293 -- -----------------------------------------------------------------------------
1294 edw_log.put_line(' ');
1295 edw_log.put_line('Start pushing data');
1296 l_temp_date := sysdate;
1297
1298 create_value_temp_table;
1299 create_vbh_val_set_temp_tbl;
1300 create_vbh_temp_table;
1301 create_vbh_sob_vset_lookup_tbl;
1302
1303 begin
1304 select instance_code
1305 into g_instance_code
1306 from edw_local_instance;
1307 exception
1308 when no_data_found then
1309 edw_log.put_line( 'No data found in EDW_LOCAL_INSTANCE table.');
1310 return;
1311 when others then
1312 edw_log.put_line( 'Error: When looking up instance code in EDW_LOCAL_INSTANCE table.');
1313 return;
1314 end;
1315
1316 prepare_source_temp_table(l_source_temp_table_name, l_target_temp_table_name);
1317 l_temp_table_name := l_source_temp_table_name;
1318
1319 edw_log.put_line('');
1320 edw_log.put_line('Pushing data from '||g_instance_code||'...');
1321 edw_log.put_line('');
1322 clean_up_temp_table(l_temp_table_name);
1323
1324
1325 l_temp_insert_count := bulk_push_parent_child_pair( l_temp_table_name);
1326 g_row_count:= g_row_count+ l_temp_insert_count;
1327
1328
1329 l_temp_insert_count := bulk_push_orphans( l_temp_table_name);
1330 g_row_count:= g_row_count+ l_temp_insert_count;
1331
1332 --push the consolidation relationship into temp table
1333 setTimer(l_log_timstamp);
1334 l_stmt := 'SELECT DISTINCT
1335 con.consolidation_id
1336 , con.child_edw_set_of_books_id
1337 , con.parent_edw_set_of_books_id
1338 , p_sob.instance
1339 , p_sob.set_of_books_id
1340 , p_sob.set_of_books_name
1341 , p_sob.chart_of_accounts_id
1342 , p_sob.description
1343 , lookup.value_set_id
1344 , c_sob.instance
1345 , c_sob.set_of_books_id
1346 , c_sob.set_of_books_name
1347 , c_sob.value_set_id
1348 FROM edw_local_cons_set_of_books con,
1349 edw_local_set_of_books p_sob,
1350 ' || g_sob_vset_lookup_table || ' c_sob,
1351 EDW_FLEX_SEG_MAPPINGS_V@' || G_TARGET_LINK || ' lookup
1352 WHERE p_sob.edw_set_of_books_id = con.parent_edw_set_of_books_id
1356 AND lookup.structure_num=(
1353 AND c_sob.edw_set_of_books_id = con.child_edw_set_of_books_id
1354 AND lookup.DIMENSION_SHORT_NAME= ''' || g_dimension_name || '''
1355 AND lower(lookup.INSTANCE_CODE)= lower(p_sob.instance)
1357 SELECT chart_of_accounts_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id= p_sob.set_of_books_id)
1358 ';
1359
1360 VBHDEBUG( 'Executing query: ' || l_stmt);
1361 OPEN cur_parent_set_of_books FOR l_stmt;
1362 LOOP
1363 FETCH cur_parent_set_of_books
1364 INTO l_consolidation_id,
1365 l_child_edw_set_of_books_id,
1366 l_parent_set_of_books.edw_set_of_books_id,
1367 l_parent_set_of_books.instance,
1368 l_parent_set_of_books.set_of_books_id,
1369 l_parent_set_of_books.set_of_books_name,
1370 l_parent_set_of_books.chart_of_accounts_id,
1371 l_parent_set_of_books.description,
1372 l_parent_value_set_id,
1373 l_set_of_books.instance,
1374 l_set_of_books.set_of_books_id,
1375 l_set_of_books.set_of_books_name,
1376 l_value_set_id;
1377 EXIT WHEN cur_parent_set_of_books%NOTFOUND;
1378
1379 --get the coa_mapping_id for the consolidation_id :Bug#4583057
1380 OPEN cur_coa_mapping_id FOR
1381 SELECT coa_mapping_id
1382 From gl_consolidation
1383 WHERE consolidation_id=l_consolidation_id;
1384
1385 FETCH cur_coa_mapping_id INTO l_coa_mapping_id;
1386 CLOSE cur_coa_mapping_id;
1387
1388 --look up the equi_set_of_books_id for parent set_of_books
1389 OPEN cur_edw_equi_sob FOR
1390 SELECT distinct *
1391 FROM edw_local_set_of_books
1392 WHERE
1393 edw_set_of_books_id=(
1394 SELECT equi_set_of_books_id
1395 FROM edw_local_equi_set_of_books
1396 WHERE edw_set_of_books_id=l_parent_set_of_books.edw_set_of_books_id
1397 );
1398
1399 FETCH cur_edw_equi_sob INTO l_edw_equi_sob;
1400 IF cur_edw_equi_sob%FOUND THEN
1401 l_parent_set_of_books:=l_edw_equi_sob;
1402 END IF;
1403 CLOSE cur_edw_equi_sob ;
1404
1405 -- lookup the l_flex_value_desc and call EDW_GL_CONSOLIDATION
1406 OPEN cur_flex_value_desc FOR
1407 'SELECT flex_value,description,summary_flag
1408 FROM '|| g_value_temp_table||'
1409 WHERE flex_value_set_id=:s1'
1410 using l_value_set_id;
1411 LOOP
1412 FETCH cur_flex_value_desc INTO l_flex_value_desc;
1413 EXIT WHEN cur_flex_value_desc%NOTFOUND;
1414 l_parent_value:=null;
1415 EDW_GL_CONSOLIDATION.edw_get_cons_flex_value(
1416 l_coa_mapping_id,l_value_set_id,
1417 l_parent_value_set_id,l_flex_value_desc.value, l_flex_value_desc.parent_flag,
1418 l_parent_value,l_resurn_msg, l_cons_status);
1419
1420 IF l_parent_value IS NOT NULL THEN
1421 --look up the description for the parent value
1422 -- clean up before using, the garbage value is causing problem when desc lookup fails.
1423 l_parent_desc:= null;
1424
1425 open cur_desc for
1426 'SELECT description FROM '|| g_value_temp_table||'
1427 WHERE flex_value_set_id=:s1
1428 AND flex_value=:s2' using l_parent_value_set_id,l_parent_value;
1429 fetch cur_desc into l_parent_desc;
1430 if cur_desc%NOTFOUND then
1431 edw_log.put_line('Error:'||l_parent_value||
1432 ' returned by the consolidation funcation is not found in value set '
1433 ||l_parent_value_set_id);
1434 end if;
1435 close cur_desc;
1436
1437 --push the bridge into temp, only if the parent value/desc lookup succeeeds
1438 IF l_parent_desc IS NOT NULL THEN
1439 insert_into_temp_table(l_temp_table_name,
1440 l_parent_value||'-'||l_parent_set_of_books.set_of_books_id||'-'||
1441 l_parent_set_of_books.instance,l_parent_value,l_parent_desc,
1442 l_flex_value_desc.value||'-'||l_set_of_books.set_of_books_id
1443 ||'-'||l_set_of_books.instance,l_flex_value_desc.value,
1444 l_flex_value_desc.description,
1445 l_insert_count);
1446 l_rows_inserted:=l_rows_inserted + l_insert_count;
1447 END IF; -- for l_parent_desc is not null
1448 END IF;--for parent value is not null
1449 END LOOP;--for cur_cons_from_flex_value
1450 CLOSE cur_flex_value_desc;
1451 edw_log.put_line('inserted '|| l_rows_inserted||' consolidation relationships between '||
1452 l_set_of_books.set_of_books_name||' and '|| l_parent_set_of_books.set_of_books_name||
1453 ' into '|| l_temp_table_name);
1454 edw_log.put_line('');
1455
1456 END LOOP; --for parent set of books loop
1457 CLOSE cur_parent_set_of_books;
1458 logTime('Consolidations', l_log_timstamp);
1459 g_row_count:= g_row_count+ l_rows_inserted;
1460 l_rows_inserted := 0;
1461
1462
1463 setTimer(l_log_timstamp);
1464 l_dups_removed := bulk_remove_dup_from_orphans(l_temp_table_name, l_dups_removed);
1465 l_dups_removed := bulk_remove_dup_from_cons(l_temp_table_name, l_dups_removed);
1466 logTime('duplication removing', l_log_timstamp);
1467 commit;
1468
1469 l_rows_replicated := rep_from_src_to_target(l_source_temp_table_name, l_target_temp_table_name);
1470
1471 l_duration := sysdate - l_temp_date;
1475 edw_log.put_line(l_dups_removed ||' duplicates removed');
1472 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
1473 edw_log.put_line(' ');
1474 edw_log.put_line(g_row_count||' rows inserted');
1476 edw_log.put_line(l_rows_replicated ||' rows replicated from source temp to target');
1477
1478
1479 -- ---------------------------------------------------------------------------
1480 -- END OF Collection , Developer Customizable Section
1481 -- ---------------------------------------------------------------------------
1482 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count - l_dups_removed ,null,g_push_date_range1, g_push_date_range2);
1483
1484
1485
1486 Exception
1487 When others then
1488 Errbuf:=sqlerrm;
1489 Retcode:=sqlcode;
1490 l_exception_msg := Retcode || ':' || Errbuf;
1491 EDW_GL_ACCT_M_C.g_exception_msg := l_exception_msg;
1492 rollback;
1493 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_GL_ACCT_M_C.g_exception_msg,g_push_date_range1, g_push_date_range2);
1494
1495 End push;
1496
1497 END EDW_GL_ACCT_M_C ;