[Home] [Help]
PACKAGE BODY: APPS.EDW_VBH_SETUP
Source
1 package body edw_vbh_setup as
2 /* $Header: EDWVBHSB.pls 120.2 2006/02/27 03:34:12 rkumar noship $ */
3 procedure LOOKUP_DB_LINK(P_INSTANCE IN VARCHAR2,
4 p_status out nocopy boolean,
5 p_errMsg out nocopy varchar2,
6 p_db_link out nocopy varchar2) IS
7
8 begin
9 select warehouse_to_instance_link
10 into p_db_link
11 from edw_source_instances
12 where instance_code=p_instance
13 and warehouse_to_instance_link is not null
14 and enabled_flag='Y';
15 p_status:=true;
16 exception
17 when others then
18 p_db_link := null;
19 p_status:=false;
20 p_errMsg:=sqlcode||':'||sqlerrm;
21 end lookup_db_link;
22
23 PROCEDURE INSERT_INTO_EDW_SET_OF_BOOKS(
24 p_status out nocopy boolean,
25 p_errMsg out nocopy varchar2) AS
26 l_instance_code edw_source_instances.instance_code%TYPE;
27 l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
28 l_insert_stmt varchar2(20000);
29 l_cursor_id integer;
30 l_rows_inserted integer:=0;
31 l_stmt varchar2(100);
32 cursor l_source_instances_cur is
33 select INSTANCE_CODE,WAREHOUSE_TO_INSTANCE_LINK
34 from edw_source_instances
35 where WAREHOUSE_TO_INSTANCE_LINK is not null
36 and enabled_flag ='Y'
37 order by instance_code;
38 begin
39 l_stmt:='alter session set global_names = FALSE';
40 execute immediate l_stmt;
41 delete from edw_set_of_books;
42 delete from edw_cons_set_of_books;
43 delete from edw_equi_set_of_books;
44 delete from edw_vbh_roots;
45 delete from edw_segment_classes;
46 open l_source_instances_cur;
47 loop
48 fetch l_source_instances_cur into l_instance_code,l_instance_link;
49 exit when l_source_instances_cur%NOTFOUND;
50
51 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
52 l_insert_stmt:= 'insert into edw_set_of_books(EDW_SET_OF_BOOKS_ID,
53 instance,
54 SET_OF_BOOKS_ID,
55 SET_OF_BOOKS_NAME ,
56 CHART_OF_ACCOUNTS_ID,
57 description,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY ,
62 LAST_UPDATE_LOGIN
63 )
64 select EDW_SET_OF_BOOKS_S.nextval,'''||l_instance_code||''',set_of_books_id,
65 name,CHART_OF_ACCOUNTS_ID,description,
66 sysdate,
67 fnd_global.user_id,
68 sysdate,
69 fnd_global.user_id,
70 fnd_global.login_id
71 from gl_sets_of_books@'||l_instance_link
72 ||' where CHART_OF_ACCOUNTS_ID in (select distinct STRUCTURE_NUM from edw_flex_seg_mappings_v where instance_code=:b_instance_code)';
73 DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
74 DBMS_SQL.bind_variable(l_cursor_id,':b_instance_code',l_instance_code);
75 l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
76 DBMS_SQL.close_cursor(l_cursor_id);
77 commit;
78 end loop;
79 close l_source_instances_cur;
80 exception
81 when others then
82 p_status :=false;
83 p_errMsg:=sqlcode||':'||sqlerrm;
84
85 end insert_into_edw_set_of_books;
86
87 procedure insert_source(p_status out nocopy boolean,p_errMsg out nocopy varchar2) is
88 l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
89 l_instance_code edw_source_instances.instance_code%TYPE;
90 l_insert_stmt varchar2(20000);
91 l_delete_stmt varchar2(200);
92 l_stmt varchar2(1000);
93
94 cursor l_source_instances_cur is
95 select warehouse_to_instance_link, INSTANCE_CODE
96 from edw_source_instances
97 where WAREHOUSE_TO_INSTANCE_LINK is not null
98 and enabled_flag='Y';
99
100 begin
101 l_stmt:='alter session set global_names = FALSE';
102 execute immediate l_stmt;
103
104 open l_source_instances_cur;
105 loop
106 fetch l_source_instances_cur into l_instance_link, l_instance_code;
107 exit when l_source_instances_cur%NOTFOUND;
108
109 l_delete_stmt:='delete from EDW_LOCAL_SET_OF_BOOKS@'||l_instance_link;
110 execute immediate l_delete_stmt;
111
112 l_insert_stmt:= 'insert into EDW_LOCAL_SET_OF_BOOKS@'||l_instance_link||'
113 (EDW_SET_OF_BOOKS_ID,
114 instance,
115 SET_OF_BOOKS_ID,
116 SET_OF_BOOKS_NAME ,
117 CHART_OF_ACCOUNTS_ID,
118 description,
119 CREATION_DATE,
120 CREATED_BY,
121 LAST_UPDATE_DATE,
122 LAST_UPDATED_BY ,
123 LAST_UPDATE_LOGIN
124 )
125 select EDW_SET_OF_BOOKS_ID,
126 instance,
127 SET_OF_BOOKS_ID,
128 SET_OF_BOOKS_NAME ,
129 CHART_OF_ACCOUNTS_ID,
130 description,
131 CREATION_DATE,
132 CREATED_BY,
133 LAST_UPDATE_DATE,
134 LAST_UPDATED_BY ,
135 LAST_UPDATE_LOGIN
136 from edw_set_of_books
137 WHERE instance = ''' || l_instance_code || '''';
138
139 execute immediate l_insert_stmt;
140 end loop;
141 COMMIT;
142 close l_source_instances_cur;
143 exception
144 when others then
145 p_status :=false;
146 p_errMsg:=sqlcode||':'||sqlerrm;
147 end insert_source;
148
149
150 procedure insert_cons_to_source(p_status out nocopy boolean,p_errMsg out nocopy varchar2) is
151 l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
152 l_insert_stmt varchar2(20000);
153 l_delete_stmt varchar2(200);
154 l_delete_cursor_id integer;
155 l_insert_cursor_id integer;
156 l_rows_deleted integer:=0;
157 l_rows_inserted integer:=0;
158 l_stmt varchar2(100);
159
160 cursor l_source_instances_cur is
161 select WAREHOUSE_TO_INSTANCE_LINK
162 from edw_source_instances
163 where WAREHOUSE_TO_INSTANCE_LINK is not null
164 and enabled_flag ='Y';
165
166 begin
167 l_stmt:='alter session set global_names = FALSE';
168 execute immediate l_stmt;
169
170 open l_source_instances_cur;
171 loop
172 fetch l_source_instances_cur into l_instance_link;
173 exit when l_source_instances_cur%NOTFOUND;
174
175 l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
176 l_delete_stmt:='delete from EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link;
177 DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
178 l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
179 commit;
180 DBMS_SQL.close_cursor(l_delete_cursor_id);
181
182 l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
183 l_insert_stmt:= 'insert into EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link||'
184 (child_EDW_SET_OF_BOOKS_ID,
185 parent_edw_SET_OF_BOOKS_ID,
186 consolidation_id,
187 consolidation_NAME,
188 CREATION_DATE,
189 CREATED_BY,
190 LAST_UPDATE_DATE,
191 LAST_UPDATED_BY ,
192 LAST_UPDATE_LOGIN
193 )
194 select child_EDW_SET_OF_BOOKS_ID,
195 parent_edw_SET_OF_BOOKS_ID,
196 consolidation_id,
197 consolidation_NAME,
198 CREATION_DATE,
199 CREATED_BY,
200 LAST_UPDATE_DATE,
201 LAST_UPDATED_BY ,
202 LAST_UPDATE_LOGIN
203 from edw_cons_set_of_books';
204 DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
205 l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
206 commit;
207 DBMS_SQL.close_cursor(l_insert_cursor_id);
208 end loop;
209 close l_source_instances_cur;
210
211 exception
212 when others then
213 p_status :=false;
214 p_errMsg:=sqlcode||':'||sqlerrm;
215 end insert_cons_to_source;
216
217
218 procedure insert_equi_to_source(p_status out nocopy boolean, p_errMsg out nocopy varchar2) is
219 l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
220 l_insert_stmt varchar2(20000);
221 l_delete_stmt varchar2(200);
222 l_delete_cursor_id integer;
223 l_insert_cursor_id integer;
224 l_rows_deleted integer:=0;
225 l_rows_inserted integer:=0;
226 l_stmt varchar2(100);
227
228 cursor l_source_instances_cur is
229 select WAREHOUSE_TO_INSTANCE_LINK
230 from edw_source_instances
231 where WAREHOUSE_TO_INSTANCE_LINK is not null
232 and enabled_flag='Y';
233
234 begin
235 l_stmt:='alter session set global_names = FALSE';
236 execute immediate l_stmt;
237
238 open l_source_instances_cur;
239 loop
240 fetch l_source_instances_cur into l_instance_link;
241 exit when l_source_instances_cur%NOTFOUND;
242
243 l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
244 l_delete_stmt:='delete from EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link;
245 DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
246 l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
247 commit;
248 DBMS_SQL.close_cursor(l_delete_cursor_id);
249
250 l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
251 l_insert_stmt:= 'insert into EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link||'
252 (EDW_SET_OF_BOOKS_ID,
253 equi_SET_OF_BOOKS_ID,
254 CREATION_DATE,
255 CREATED_BY,
256 LAST_UPDATE_DATE,
257 LAST_UPDATED_BY ,
258 LAST_UPDATE_LOGIN
259 )
260 select EDW_SET_OF_BOOKS_ID,
261 equi_SET_OF_BOOKS_ID,
262 CREATION_DATE,
263 CREATED_BY,
264 LAST_UPDATE_DATE,
265 LAST_UPDATED_BY ,
266 LAST_UPDATE_LOGIN
267 from edw_equi_set_of_books';
268 DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
269 l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
270 commit;
271 DBMS_SQL.close_cursor(l_insert_cursor_id);
272 end loop;
273 close l_source_instances_cur;
274
275 exception
276 when others then
277 p_status :=false;
278 p_errMsg:=sqlcode||':'||sqlerrm;
279 end insert_equi_to_source;
280
281 procedure lookup_sob_coa_id(
282 p_db_link in varchar2,
283 p_sob_name in varchar2,
284 p_sob_id out nocopy number,
285 p_coa_id out nocopy number,
286 p_description out nocopy varchar2,
287 p_status out nocopy boolean,
288 p_errMsg out nocopy varchar2) is
289 l_select_stmt varchar2(2000);
290 l_rows_selected number;
291 l_cursor_id number;
292 l_sob_id number;
293 l_coa_id number;
294 l_description varchar2(240);
295 l_stmt varchar2(100);
296
297 begin
298
299 l_stmt:='alter session set global_names = FALSE';
300 execute immediate l_stmt;
301
302 l_cursor_id:=dbms_sql.open_cursor;
303 l_select_stmt :=
304 'select set_of_books_id, chart_of_accounts_id, description
305 from gl_sets_of_books@'||p_db_link||'
306 where name = :b_sob_name';
307 dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
308 dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309 dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310 dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311 dbms_sql.define_column(l_cursor_id,3,l_description,240);
312 l_rows_selected:= dbms_sql.execute(l_cursor_id);
313 if(dbms_sql.fetch_rows(l_cursor_id)=0) then
314 p_status:=false;
315 fnd_message.set_name('BIS','EDW_NODATA_SET_OF_BOOKS');
316 fnd_message.set_token('NAME', p_sob_name);
317 p_errMsg:=fnd_message.get;
318 --p_errMsg:='Cannot find '||p_sob_name||' in gl_sets_of_books';
319 else
320 dbms_sql.column_value(l_cursor_id,1,l_sob_id);
321 dbms_sql.column_value(l_cursor_id,2,l_coa_id);
322 dbms_sql.column_value(l_cursor_id,3,l_description);
323 p_sob_id:=l_sob_id;
324 p_coa_id:=l_coa_id;
325 p_description:= l_description;
326 p_status:=true;
327 end if;
328 exception
329 when others then
330 p_sob_id := null;
331 p_coa_id := null;
332 p_description := null;
333 p_status:=false;
334 p_errMsg:=sqlcode||':'||sqlerrm;
335 end;
336
337
338 procedure lookup_wh_dimension_name(
339 p_instance in varchar2,
340 p_segment_name in varchar2,
341 p_coa_id in number,
342 p_wh_dimension_name out nocopy varchar2,
343 p_status out nocopy boolean,
344 p_errMsg out nocopy varchar2) is
345 begin
346
347 select dim_long_name
348 into p_wh_dimension_name
349 from edw_dimensions_md_v
350 where dim_name =(
351 select DIMENSION_SHORT_NAME
352 from edw_flex_seg_mappings_v
353 where lower(instance_code)=lower(p_instance)
354 and segment_name =p_segment_name
355 and structure_num=p_coa_id);
356 p_status:=true;
357 exception
358 when others then
359 p_wh_dimension_name := null;
360 p_status :=false;
361 p_errMsg:=sqlcode||':'||sqlerrm;
362 end lookup_wh_dimension_name;
363
364 FUNCTION check_db_status_all(x_instance_code OUT NOCOPY VARCHAR2)
365 return boolean IS
366
367 l_status BOOLEAN := TRUE;
368 l_instance_code VARCHAR2(30);
369 l_db_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
370 --l_db_link VARCHAR2(30);
371 l_progress VARCHAR2(3):= '000';
372 l_dummy VARCHAR2(30);
373 l_dummy_int NUMBER;
374 cid NUMBER;
375 l_stmt varchar2(100);
376 l_temp number := 0;
377
378 CURSOR instances IS
379 SELECT instance_code, warehouse_to_instance_link
380 FROM edw_source_instances
381 WHERE enabled_flag = 'Y';
382
383 BEGIN
384
385 l_stmt:='alter session set global_names = FALSE';
386 execute immediate l_stmt;
387
388 x_instance_code:='';
389 l_progress := '010';
390 -- Check to make sure that all the enabled OLTP sources are up and running
391 cid := DBMS_SQL.open_cursor;
392 OPEN instances;
393 LOOP
394 BEGIN
395 l_progress := '020';
396
397 FETCH instances INTO l_instance_code, l_db_link;
398 EXIT WHEN instances%NOTFOUND;
399
400 -- Store the instance name in the out parameter to return
401
402 DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);
403 l_dummy_int := DBMS_SQL.EXECUTE(cid);
404 l_progress := '030';
405 EXCEPTION
406 when others then
407 l_status := FALSE;
408 x_instance_code:=x_instance_code||l_instance_code||' ';
409 edw_message_s.sql_error('check_db_status',l_progress,sqlcode);
410 END;
411 END LOOP;
412 CLOSE instances;
413 DBMS_SQL.close_cursor(cid);
414 return l_status;
415
416 exception
417 when others then
418 DBMS_SQL.close_cursor(cid);
419 x_instance_code := null;
420 return false;
421 END check_db_status_all;
422
423 --changed to_set_of_books_id to to_ledger_id for bug#4583057
424 --changed from_set_of_books_id to from_ledger_id for bug#4583057
425 procedure check_valid_consolidation
426 (p_instance in varchar2,p_from_ledger_id in number,
427 p_to_ledger_id in number,p_result out nocopy boolean,
428 p_status out nocopy boolean,p_error_mesg out nocopy varchar2)AS
429 l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
430 l_select_stmt varchar2(20000);
431 l_cursor_id integer;
432 l_rows_selected integer:=0;
433 l_count number;
434 l_status boolean;
435 l_errMsg varchar2(80);
436 l_stmt varchar2(100);
437
438 begin
439
440 l_stmt:='alter session set global_names = FALSE';
441 execute immediate l_stmt;
442
443
444 lookup_db_link(p_instance,l_status,l_errMsg,l_instance_link);
445 if(l_status) then
446 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
447 l_select_stmt:=
448 'select count(*)
449 into :b_count
450 from gl_consolidation@'||l_instance_link||'
451 where from_ledger_id =:b_from_ledger_id
452 and to_ledger_id=:b_to_ledger_id';
453 DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
454 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
455 p_from_ledger_id);
456 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
457 p_to_ledger_id);
458 DBMS_SQL.define_column(l_cursor_id,1,l_count);
459 l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
460
461 if(DBMS_SQL.fetch_rows(l_cursor_id)=0) then
462 p_status:=false;
463 p_result:=false;
464 return;
465 end if;
466
467 DBMS_SQL.column_value(l_cursor_id,1,l_count);
468 DBMS_SQL.close_cursor(l_cursor_id);
469 if l_count=0 then
470 p_status:=true;
471 p_result:=false;
472 elsif l_count>0 then
473 p_status:=true;
474 p_result:=true;
475 end if;
476
477 else
478 p_status:=false;
479 p_result:=false;
480 p_error_mesg:=l_errMsg;
481 end if;
482 exception
483 when others then
484 p_status:=false;
485 p_result:=false;
486 p_error_mesg:=sqlcode||':'||sqlerrm;
487 end;
488
489 --changed from_set_of_books_id to from_ledger_id for bug#4583057
490 procedure get_consolidation_id
491 (p_instance in varchar2,
492 p_from_ledger_id in number,
493 p_to_ledger_id in number,
494 p_consolidation_name in varchar2,
495 p_consolidation_id out nocopy number,
496 p_status out nocopy boolean,
497 p_error_mesg out nocopy varchar2)AS
498 l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
499 l_select_stmt varchar2(20000);
500 l_cursor_id integer;
501 l_rows_selected integer:=0;
502 l_count number;
503 l_stmt varchar2(100);
504
505 begin
506
507 l_stmt:='alter session set global_names = FALSE';
508 execute immediate l_stmt;
509
510 lookup_db_link(p_instance,p_status, p_error_mesg,l_instance_link );
511 if(p_status) then
512 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
513 l_select_stmt:=
514 'select consolidation_id
515 into :b_consolidation_id
516 from gl_consolidation@'||l_instance_link||'
517 where from_ledger_id =:b_from_ledger_id
518 and to_ledger_id=:b_to_ledger_id
519 and name =:b_consolidation_name';
520 DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
521 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
522 p_from_ledger_id);
523 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
524 p_to_ledger_id);
525 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',
526 p_consolidation_name);
527 DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);
528 l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
529
530 if(DBMS_SQL.fetch_rows(l_cursor_id)>0) then
531 p_status:=true;
532 DBMS_SQL.column_value(l_cursor_id,1,p_consolidation_id);
533 else
534 p_status:=false;
535 end if;
536 DBMS_SQL.close_cursor(l_cursor_id);
537 end if;
538 exception
539 when others then
540 p_status:=false;
541 p_consolidation_id := null;
542 p_error_mesg := sqlcode||':'||sqlerrm;
543 end;
544
545 procedure check_root_all (p_status out nocopy boolean,
546 p_problem_sob_id out nocopy integer,
547 p_problem_sob_id2 out nocopy integer,
548 p_hierarchy_no out nocopy integer,
549 p_segment_name out nocopy varchar2) as
550 l_edw_sob_id number(15);
551 l_segment_name varchar2(30);
552 l_root_value1 varchar2(240);
553 l_root_value2 varchar2(240);
554 l_root_value3 varchar2(240);
555 l_root_value4 varchar2(240);
556 l_instance varchar2(30);
557
558 cursor l_cur_edw_vbh_roots is
559 select edw_set_of_books_id,segment_name,
560 root_value1,root_value2,root_value3,root_value4
561 from edw_vbh_roots;
562 begin
563 open l_cur_edw_vbh_roots;
564 loop
565 fetch l_cur_edw_vbh_roots into l_edw_sob_id,l_segment_name,
566 l_root_value1,l_root_value2,l_root_value3,l_root_value4;
567 exit when l_cur_edw_vbh_roots%NOTFOUND;
568 select instance into l_instance
569 from edw_set_of_books
570 where edw_set_of_books_id= l_edw_sob_id;
571 p_problem_sob_id2:=l_edw_sob_id;
572 p_segment_name := l_segment_name;
573
574 if l_root_value1 IS NOT NULL THEN
575 check_vbh_root_setup (l_edw_sob_id, l_segment_name, l_instance,1,
576 p_status, p_problem_sob_id);
577 p_hierarchy_no:=1;
578 if p_status = false then return;
579 end if;
580 end if;
581
582 if l_root_value2 IS NOT NULL THEN
583 check_vbh_root_setup (l_edw_sob_id, l_segment_name, l_instance,2,
584 p_status, p_problem_sob_id);
585 p_hierarchy_no:=2;
586 if p_status = false then return;
587 end if;
588 end if;
589
590 if l_root_value3 IS NOT NULL THEN
591 check_vbh_root_setup (l_edw_sob_id, l_segment_name, l_instance,3,
592 p_status, p_problem_sob_id);
593 p_hierarchy_no:=3;
594 if p_status = false then return;
595 end if;
596 end if;
597
598 if l_root_value4 IS NOT NULL THEN
599 check_vbh_root_setup (l_edw_sob_id, l_segment_name, l_instance,4,
600 p_status, p_problem_sob_id);
601 p_hierarchy_no:=4;
602 if p_status = false then return;
603 end if;
604 end if;
605 p_status:=true;
606 end loop;
607 close l_cur_edw_vbh_roots;
608
609 exception
610 when others then
611 close l_cur_edw_vbh_roots;
612 p_status:=false;
613 p_problem_sob_id := null;
614 p_problem_sob_id2 := null;
615 p_hierarchy_no := null;
616 p_segment_name := null;
617
618 end;
619
620
621
622 procedure check_vbh_root_setup (
623 p_edw_sob_id in integer
624 , p_segment_name in varchar2
625 , p_instance in varchar2
626 , p_hierarchy_no in number
627 , p_status out nocopy boolean
628 , p_problem_sob_id out nocopy integer) as
629
630 type t_cur_edw_cons is ref cursor;
631 l_cur_edw_cons t_cur_edw_cons;
632 l_parent_edw_sob_id number;
633 l_child_edw_sob_id number;
634 l_consolidation_id number;
635 l_status boolean;
636 l_err_msg varchar2(100);
637 --l_db_link varchar2(30);
638 l_db_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
639 l_from_value_set_id number;
640 l_to_value_set_id number;
641 l_select_stmt varchar2(200);
642 l_cursor_id number;
643 l_result number;
644 l_dummy number;
645 begin
646 edw_vbh_setup.LOOKUP_DB_LINK(p_instance,l_status,l_err_msg,l_db_link);
647 open l_cur_edw_cons for
648 select parent_edw_set_of_books_id, child_edw_set_of_books_id,
649 consolidation_id
650 from edw_cons_set_of_books
651 where child_edw_set_of_books_id
652 in (select edw_set_of_books_id from edw_vbh_roots where segment_name=p_segment_name)
653 and parent_edw_set_of_books_id=p_edw_sob_id;
654
655 loop
656 fetch l_cur_edw_cons into l_parent_edw_sob_id,l_child_edw_sob_id,l_consolidation_id;
657 exit when l_cur_edw_cons%NOTFOUND;
658
659 select from_f.value_set_id
660 into l_from_value_set_id
661 from edw_flex_seg_mappings_v from_f,edw_set_of_books from_b
662 where from_b.edw_set_of_books_id=l_child_edw_sob_id
663 and from_b.chart_of_accounts_id=from_f.structure_num
664 and from_f.instance_code=p_instance
665 and from_f.segment_name=p_segment_name;
666
667 select to_f.value_set_id
668 into l_to_value_set_id
669 from edw_flex_seg_mappings_v to_f,edw_set_of_books to_b
670 where to_b.edw_set_of_books_id=l_parent_edw_sob_id
671 and to_b.chart_of_accounts_id=to_f.structure_num
672 and to_f.instance_code=p_instance
673 and to_f.segment_name=p_segment_name;
674
675 l_cursor_id:=dbms_sql.open_cursor;
676 l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
677 ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
678 dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
679
680 dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681 dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682 dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683 dbms_sql.define_column(l_cursor_id,1,l_result);
684 l_dummy:=dbms_sql.execute(l_cursor_id);
685 if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686 end if;
687 dbms_sql.column_value(l_cursor_id,1,l_result);
688 dbms_sql.close_cursor(l_cursor_id);
689
690 if l_result<>0 then
691 l_cursor_id:=dbms_sql.open_cursor;
692 l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';
693
694 dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
695 dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);
696 dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697
698 dbms_sql.define_column(l_cursor_id,1,l_result);
699 l_dummy:=dbms_sql.execute(l_cursor_id);
700 if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
701 end if;
702 dbms_sql.column_value(l_cursor_id,1,l_result);
703 dbms_sql.close_cursor(l_cursor_id);
704 if l_result <> 0 then
705 p_status:=false;
706 p_problem_sob_id:=l_child_edw_sob_id;
707 return;
708 end if;
709 end if;
710 end loop;
711 close l_cur_edw_cons;
712
713 open l_cur_edw_cons for
714 select parent_edw_set_of_books_id,child_edw_set_of_books_id,consolidation_id
715 from edw_cons_set_of_books
716 where parent_edw_set_of_books_id in (select edw_set_of_books_id from edw_vbh_roots where segment_name=p_segment_name)
717 and child_edw_set_of_books_id =p_edw_sob_id;
718 loop
719 fetch l_cur_edw_cons into l_parent_edw_sob_id,l_child_edw_sob_id,l_consolidation_id;
720 exit when l_cur_edw_cons%NOTFOUND;
721 select from_f.value_set_id
722 into l_from_value_set_id
723 from edw_flex_seg_mappings_v from_f,edw_set_of_books from_b
724 where from_b.edw_set_of_books_id=l_child_edw_sob_id
725 and from_b.chart_of_accounts_id=from_f.structure_num
726 and from_f.instance_code=p_instance
727 and from_f.segment_name=p_segment_name;
728
729 select to_f.value_set_id
730 into l_to_value_set_id
731 from edw_flex_seg_mappings_v to_f,edw_set_of_books to_b
732 where to_b.edw_set_of_books_id=l_parent_edw_sob_id
733 and to_b.chart_of_accounts_id=to_f.structure_num
734 and to_f.instance_code=p_instance
735 and to_f.segment_name=p_segment_name;
736
737 l_cursor_id:=dbms_sql.open_cursor;
738 l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
739 ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
740
741 dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
742 dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743 dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744 dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745 dbms_sql.define_column(l_cursor_id,1,l_result);
746 l_dummy:=dbms_sql.execute(l_cursor_id);
747 if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748 end if;
749 dbms_sql.column_value(l_cursor_id,1,l_result);
750 dbms_sql.close_cursor(l_cursor_id);
751 if l_result<>0 then
752
753 l_cursor_id:=dbms_sql.open_cursor;
754 l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';
755
756 dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
757 dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);
758 dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759
760 dbms_sql.define_column(l_cursor_id,1,l_result);
761 l_dummy:=dbms_sql.execute(l_cursor_id);
762 if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
763 end if;
764 dbms_sql.column_value(l_cursor_id,1,l_result);
765 dbms_sql.close_cursor(l_cursor_id);
766 if l_result <> 0 then
767 p_status:=false;
768 p_problem_sob_id:=l_parent_edw_sob_id;
769 return;
770 end if;
771 end if;
772 end loop;
773 close l_cur_edw_cons;
774 p_status:=true;
775
776 exception
777 when others then
778 p_status:=false;
779 p_problem_sob_id := null;
780
781 end;
782
783
784 FUNCTION check_sob_exist(p_status out nocopy BOOLEAN,
785 p_errMsg out nocopy VARCHAR2,
786 p_set_of_books_id IN NUMBER ) return boolean IS
787 l_status BOOLEAN := TRUE;
788 TYPE curType IS REF CURSOR;
789 cv curType;
790 set_of_book_id_dup number;
791 l_stmt varchar2(1000);
792 BEGIN
793 l_stmt := 'select SET_OF_BOOKS_ID from edw_set_of_books where SET_OF_BOOKS_ID = '|| p_set_of_books_id;
794 open cv for l_stmt;
795 loop
796 fetch cv into set_of_book_id_dup ;
797 EXIT WHEN cv%NOTFOUND;
798 end loop;
799 if set_of_book_id_dup is not null then
800 return true;
801 else
802 return false;
803 end if;
804 EXCEPTION
805 when others then
806 close cv;
807 p_status :=false;
808 p_errMsg:=sqlcode||':'||sqlerrm;
809 END check_sob_exist;
810
811
812
813 procedure insert_set_of_books(
814 p_status out nocopy BOOLEAN,
815 p_errMsg out nocopy VARCHAR2,
816 p_edw_set_of_books_id NUMBER,
817 p_instance VARCHAR2,
818 p_set_of_books_id NUMBER,
819 p_set_of_books_name VARCHAR2,
820 p_chart_of_accounts_id NUMBER,
821 p_description VARCHAR2,
822 p_creation_date DATE,
823 p_created_by NUMBER,
824 p_last_update_date DATE,
825 p_last_updated_by NUMBER ,
826 p_last_update_login NUMBER) as
827
828 l_insert_stmt varchar2(20000);
829
830
831 begin
832 l_insert_stmt:= 'insert into edw_set_of_books(EDW_SET_OF_BOOKS_ID,instance,
833 SET_OF_BOOKS_ID,
834 SET_OF_BOOKS_NAME ,
835 CHART_OF_ACCOUNTS_ID,
836 description,
837 CREATION_DATE,
838 CREATED_BY,
839 LAST_UPDATE_DATE,
840 LAST_UPDATED_BY ,
841 LAST_UPDATE_LOGIN
842 )values('||p_edw_set_of_books_id ||','''|| p_instance||''','
843 ||p_set_of_books_id||','''||p_set_of_books_name||''','
844 ||p_chart_of_accounts_id||','''
845 ||p_description||''' ,'''
846 ||p_creation_date||''','
847 ||p_created_by||','''
848 ||p_last_update_date||''','
849 ||p_last_updated_by||','
850 ||p_last_update_login||')';
851
852 execute immediate l_insert_stmt;
853 commit;
854
855 EXCEPTION
856 when others then
857 p_status :=false;
858 p_errMsg:=sqlcode||':'||sqlerrm;
859
860 end insert_set_of_books;
861
862 end;