[Home] [Help]
PACKAGE BODY: APPS.FII_EUL4I_UTILS
Source
1 PACKAGE BODY FII_EUL4I_UTILS AS
2 /* $Header: FIIEL41B.pls 120.2 2004/01/14 06:57:10 sgautam noship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('EDW_DEBUG'), 'N');
5
6 g_errbuf VARCHAR2(2000) := NULL;
7 g_retcode VARCHAR2(200) := NULL;
8 g_eulOwner VARCHAR2(30) := NULL;
9 g_fiiSchema VARCHAR2(30) := 'FII';
10 g_Mode VARCHAR2(30) := NULL;
11 g_processName VARCHAR2(50) := NULL;
12
13 G_EUL_OWNER_DOES_NOT_EXIST EXCEPTION;
14 PRAGMA EXCEPTION_INIT(G_EUL_OWNER_DOES_NOT_EXIST, -942);
15
16 G_BUSINESS_AREA_DOES_NOT_EXIST EXCEPTION;
17 PRAGMA EXCEPTION_INIT(G_BUSINESS_AREA_DOES_NOT_EXIST, 100);
18
19
20 /* FII Business Area Names */
21 G_BusArea_1 VARCHAR2(100) := 'Revenue Intelligence Business Area';
22 G_BusArea_2 VARCHAR2(100) := 'Payables Intelligence Business Area';
23 G_BusArea_3 VARCHAR2(100) := 'Project Intelligence Business Area';
24
25 /* list of table to modify even if they are shared by other groups - due to
26 standard attributes not being hidden i.e. creation date */
27 G_TableList VARCHAR2(1000) := '('''')';
28
29 TYPE eulBusAreaRecType IS RECORD (
30 BA_ID NUMBER(10),
31 BA_NAME VARCHAR2(100),
32 BA_DEVELOPER_KEY VARCHAR2(100),
33 BA_RECORDS_EVALUATED NUMBER(10),
34 BA_RECORDS_HIDDEN NUMBER(10));
35 TYPE eulBusAreaTabType IS TABLE OF eulBusAreaRecType
36 INDEX BY BINARY_INTEGER;
37 eulBusAreaTab eulBusAreaTabType;
38
39
40 TYPE eulTableRecType IS RECORD (
41 BA_ID NUMBER(10),
42 BA_DEVELOPER_KEY VARCHAR2(100),
43 FOLDER_ID NUMBER(10),
44 FOLDER_NAME VARCHAR2(100),
45 NEW_FOLDER_NAME VARCHAR2(100),
46 TABLE_NAME VARCHAR2(64),
47 VIEW_NAME VARCHAR2(64),
48 TABLE_DEVELOPER_KEY VARCHAR2(100),
49 TABLE_TYPE VARCHAR2(1),
50 HIDDEN_ITEM_FLAG VARCHAR2(1),
51 FOLDER_HIDDEN NUMBER(1),
52 FOLDER_SB_HIDDEN NUMBER(1));
53 eulTablesRec eulTableRecType;
54 TYPE eulTablesTabType IS TABLE OF eulTableRecType
55 INDEX BY BINARY_INTEGER;
56 eulTablesTab eulTablesTabType;
57
58
59 TYPE eulColumnsRecType IS RECORD (
60 BA_ID NUMBER(10),
61 BA_DEVELOPER_KEY VARCHAR2(100),
62 FOLDER_ID NUMBER(10),
63 ITEM_ID NUMBER(10),
64 ITEM_DATA_TYPE NUMBER(2),
65 ITEM_HEADING VARCHAR2(240),
66 ITEM_FORMAT_MASK VARCHAR2(100),
67 ITEM_NAME VARCHAR2(100),
68 NEW_ITEM_NAME VARCHAR2(100),
69 COLUMN_NAME VARCHAR2(64),
70 COLUMN_DEVELOPER_KEY VARCHAR2(100),
71 ITEM_HIDDEN NUMBER(1),
72 ITEM_SB_HIDDEN NUMBER(1),
73 TABLE_TYPE VARCHAR2(1));
74 eulColumnsRec eulColumnsRecType;
75 TYPE eulColumnsTabType IS TABLE OF eulColumnsRecType
76 INDEX BY BINARY_INTEGER;
77 eulColumnsTab eulColumnsTabType;
78
79
80 TYPE dimTabRec IS RECORD (
81 table_name VARCHAR2(30),
82 view_name VARCHAR2(30),
83 rowcnt NUMBER,
84 table_status VARCHAR2(30),
85 hier1_status VARCHAR2(30),
86 hier2_status VARCHAR2(30),
87 hier3_status VARCHAR2(30),
88 hier4_status VARCHAR2(30),
89 eul_tab_status VARCHAR2(30),
90 eul_hier1_status VARCHAR2(30),
91 eul_hier2_status VARCHAR2(30),
92 eul_hier3_status VARCHAR2(30),
93 eul_hier4_status VARCHAR2(30));
94 TYPE dimTabType IS TABLE OF dimTabRec
95 INDEX BY BINARY_INTEGER;
96 dimTab dimTabType;
97
98 /* ---------------------------------
99 PRIVATE PROCEDURES AND FUNCTIONS
100 ---------------------------------*/
101
102 /******************************************************************************
103 PROCEDURE CHILD_SETUP
104 ******************************************************************************/
105
106 PROCEDURE CHILD_SETUP(p_object_name VARCHAR2) IS
107 l_dir VARCHAR2(400);
108 BEGIN
109 /* IF (fnd_profile.value('EDW_TRACE')='Y') THEN
110 dbms_session.set_sql_trace(TRUE);
111 ELSE
112 dbms_session.set_sql_trace(FALSE);
113 END IF; */ --Commented for bug 3304365
114
115 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
116 edw_log.g_debug := TRUE;
117 END IF;
118
119 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
120
121 if l_dir is null then
122 l_dir:='/sqlcom/log';
123 end if;
124
125 if g_debug_flag = 'Y' then
126 edw_log.put_names(p_object_name||'.log',p_object_name||'.out',l_dir);
127 end if;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 g_errbuf:=sqlerrm;
132 g_retcode:=sqlcode;
133
134 raise;
135
136 END;
137
138
139
140 /******************************************************************************
141 Function ItemsToHide
142 ******************************************************************************/
143 FUNCTION ItemsToHide(pBusAreaNameIn VARCHAR2,
144 pTableNameIn VARCHAR2,
145 pColumnNameIn VARCHAR2,
146 pItemNameIn VARCHAR2)
147 RETURN INTEGER
148 IS
149 RETURN_VALUE INTEGER := 0;
150 --
151 BEGIN
152
153 g_processName := 'itemsToHide';
154 --
155
156 -- Assuming all key item names end in 'ID','_PK' or 'KEY'
157 IF ((SUBSTRB(PColumnNameIn,-3,3) IN ('_ID' , '_PK' , 'KEY','_FK') OR
158 SUBSTRB(PColumnNameIn,-2,2) IN ('ID' , 'PK')) AND
159 pColumnNameIn NOT LIKE '%TAX%ID' /* Tax Payer IDs */) OR
160
161 -- User attribute and measures
162 (NVL(INSTR(pColumnNameIn,'USER_ATTRIBUTE'),0) > 0 AND
163 (NVL(INSTR(UPPER(REPLACE(pItemNameIn,'_',' ')),'USER ATTRIBUTE'),0) > 0 OR
164 UPPER(pItemNameIn) LIKE 'USER%ATTRIBUTE%'))
165 OR
166 (NVL(INSTR(pColumnNameIn,'USER_MEASURE'),0) > 0 AND
167 (NVL(INSTR(UPPER(REPLACE(pItemNameIn,'_',' ')),'USER MEASURE'),0) > 0 OR
168 UPPER(pItemNameIn) LIKE 'USER%MEASURE%')) OR
169
170 -- Instance is not a user attribute
171 (NVL(INSTR(PColumnNameIn,'INSTANCE'),0) > 0 AND
172 /* (
173 NOT
174 (pBusAreaNameIn = G_BusArea_3 -- Projects wants instance attribute
175 AND
176 pTableNameIn LIKE '%_M' AND
177 pTableNameIn NOT IN ('EDW_TIME_M') ))
178 OR */
179 (pColumnNameIn NOT IN ('TPRT_INSTANCE' ,
180 'ASGN_INSTANCE' ,
181 'ITEM_INSTANCE_CODE' ,
182 'ORGA_INSTANCE',
183 'PRJ_INSTANCE') )
184 ) OR
185
186 -- Hide Time Span Columns
187 SUBSTRB(PColumnNameIn,-8,8) = 'TIMESPAN' OR
188
189 -- Hide creation and last update dates
190 PColumnNameIn LIKE '%CREATION_DATE' OR
191 (PColumnNameIn LIKE '%LAST_UPDATE_DATE' AND
192 /* POA Request for DUNS lud */
193 pColumnNameIn NOT IN ('DNMR_DNB_LAST_UPDATE_DATE')) OR
194
195 -- Hide reservered columns, inactive,start and end dates for dimensions
196 (PTableNameIn LIKE '%_M' AND
197 (PColumnNameIn LIKE '%_INACTIVE_DATE' OR
198 pColumnNameIn LIKE '%_LEVEL_NAME' OR
199 NVL(INSTR(pColumnNameIn,'_ID_'),0) > 0 OR
200 ((PColumnNameIn LIKE '%_START_DATE%' OR
201 PColumnNameIn LIKE '%_STRT_DATE%' OR
202 PColumnNameIn LIKE '%_END_DATE%') AND
203 SUBSTRB(pColumnNameIn,1,4) NOT IN ('CYR_','CPER','CQTR' /* EDW_TIME_M */,
204 'TASK','TTSK' /* EDW_PROJECT_M */,
205 'ASGN','PERS' /* EDW_HR_PERSON_M */)) OR
206 PColumnNameIn LIKE '%_DP')) OR
207
208 (pTableNameIn = 'EDW_ORGANIZATION_M' AND
209 NVL(INSTR(pColumnNameIn,'_CAT_'),0) > 0 ) OR
210
211 FII_EUL4I_UTILS_2.ItemsToHide(pBusAreaNameIn,
212 pTableNameIn,
213 pColumnNameIn,
214 pItemNameIn) = 1
215 --
216
217 THEN RETURN_VALUE := 1;
218
219 END IF;
220 --
221 /* Reset other groups table and change them only
222 for columns specified in FII_EUL4I_utils_2*/
223 IF INSTR(g_TableList,pTableNameIn) > 0 THEN
224
225 RETURN_VALUE := -1;
226
227 RETURN_VALUE := FII_EUL4I_UTILS_2.ItemsToHide(pBusAreaNameIn,
228 pTableNameIn,
229 pColumnNameIn,
230 pItemNameIn);
231
232 END IF;
233
234 RETURN RETURN_VALUE;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 if g_debug_flag = 'Y' then
239 edw_log.put_line('');
240 edw_log.put_line('Error in '||g_processName);
241 edw_log.put_line('pBusAreaNamein = '||pBusAreaNamein);
242 edw_log.put_line('pTableName = '||pTableNamein);
243 edw_log.put_line('pColumnNameIn ='||pColumnNameIn);
244 edw_log.put_line('pItemNameIn ='||pItemNameIn);
245 end if;
246
247 g_errbuf:=sqlerrm;
248 g_retcode:=sqlcode;
249
250 raise;
251
252 END ItemsToHide;
253
254 /******************************************************************************
255 Procedure hideFolderItems
256 ******************************************************************************/
257 PROCEDURE hideFolderItems(pItemID IN NUMBER,
258 pHideDisplay IN NUMBER DEFAULT 1)
259 IS
260
261 l_stmt VARCHAR2(1000) := NULL;
262
263 BEGIN
264
265 g_processName := 'hideFolderItems';
266
267 l_stmt := 'UPDATE '||g_EulOwner||'.eul4_expressions exp '||
268 'SET exp.it_hidden = '||pHideDisplay||' '||
269 'WHERE exp.exp_id = '||pItemId;
270
271 /* Update tables record to indicate that items were hidden for that table */
272 eulTablesTab(eulColumnsTab(pItemID).folder_id).hidden_item_flag := 'Y';
273
274 IF g_Mode <> 'TEST' THEN
275
276 EXECUTE IMMEDIATE l_stmt;
277
278 END IF;
279
280 if g_debug_flag = 'Y' then
281 edw_log.debug_line('Procedure hideFolderItems');
282 edw_log.debug_line('Going to execute statement:');
283 edw_log.debug_line(l_stmt);
284 end if;
285
286
287 EXCEPTION
288 WHEN OTHERS THEN
289
290 g_errbuf:=sqlerrm;
291 g_retcode:=sqlcode;
292
293 raise;
294
295 END hideFolderItems;
296
297 /******************************************************************************
298 Procedure hideFolderItems
299 ******************************************************************************/
300 PROCEDURE hideFolderItems(pBusAreaID IN NUMBER,
301 pTableName IN VARCHAR2,
302 pViewName IN VARCHAR2,
303 pColumn IN VARCHAR2,
304 pHideDisplay IN NUMBER DEFAULT 1)
305 IS
306
307 l_stmt VARCHAR2(1000);
308 l_itemId NUMBER(10):= 0;
309
310 /* Cursor variable to hold item id */
311 TYPE eulItemCurType IS REF CURSOR;
312 eulItem_cv eulItemCurType;
313
314 BEGIN
315
316 g_processName := 'hideFolderItems';
317
318 l_stmt := 'SELECT exp.exp_id '||
319 'FROM '||g_EulOwner||'.eul4_expressions exp , '||
320 ' '||g_EulOwner||'.eul4_objs obj, '||
321 ' '||g_EulOwner||'.eul4_ba_obj_links bol '||
322 'WHERE bol.bol_ba_id = '||pBusAreaID||' '||
323 'AND obj.obj_id = bol.bol_obj_id '||
324 'AND obj.obj_hidden = 0 '||
325 'AND obj.sobj_ext_table IN ('''||pTableName||''' ,'''||pViewName||''') '||
326 'AND exp.it_obj_id = obj.obj_id '||
327 'AND exp.it_ext_column LIKE '''||pColumn||'''';
328
329 if g_debug_flag = 'Y' then
330 edw_log.debug_line('Procedure hideFolderItems');
331 edw_log.debug_line('Going to execute statement:');
332 edw_log.debug_line(l_stmt);
333 end if;
334
335 -- EXECUTE IMMEDIATE l_stmt INTO l_itemID ;
336
337 OPEN eulItem_cv FOR l_stmt;
338 LOOP
339
340 FETCH eulItem_cv INTO l_itemID;
341 EXIT WHEN eulItem_cv%NOTFOUND;
342
343 IF eulColumnsTab.exists(l_itemID) THEN
344
345 eulColumnsTab(l_itemID).item_sb_hidden := pHideDisplay;
346 /* Update tables record to indicate that items were hidden for that table */
347 eulTablesTab(eulColumnsTab(l_ItemID).folder_id).hidden_item_flag := 'Y';
348
349 END IF;
350
351 IF pHideDisplay = 0 THEN
352
353 hideFolderItems(l_itemID,0);
354
355 END IF;
356
357
358 END LOOP;
359
360 CLOSE eulItem_cv;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 if g_debug_flag = 'Y' then
365 edw_log.put_line('');
366 edw_log.put_line('Error in hideFolderItems');
367 edw_log.put_line('pBusAreaID = '||pBusAreaID);
368 edw_log.put_line('pTableName = '||pTableName);
369 edw_log.put_line('pViewName = '||pViewName);
370 edw_log.put_line('pColumn LIKE '||pColumn);
371 edw_log.put_line('l_stmt '||l_stmt);
372 end if ;
373
374 g_errbuf:=sqlerrm;
375 g_retcode:=sqlcode;
376
377 raise;
378
379 -- hideFolderItems(l_itemID);
380
381 END hideFolderItems;
382
383
384 /******************************************************************************
385 Procedure InitBusAreas
386 ******************************************************************************/
387
388 PROCEDURE InitBusAreas(pBusAreaName IN VARCHAR2,
389 pAction IN VARCHAR2 DEFAULT 'ADD') IS
390
391 ctr PLS_INTEGER := 0;
392 l_stmt VARCHAR2(1000) := NULL;
393 lEulBA_ID NUMBER(10);
394 lEulBA_NAME VARCHAR2(100);
395 lEulBA_DEVELOPER_KEY VARCHAR2(100);
396
397 BEGIN
398
399 g_processName := 'initBusAreas';
400
401 IF pAction = 'RESET' THEN
402
403 eulBusAreaTab.delete;
404
405 ELSE
406
407 l_stmt := 'SELECT ba.ba_id, '||
408 ' ba.ba_name,'||
409 ' ba.ba_name ba_developer_key '||
410 'FROM '||g_EulOwner||'.eul4_bas ba '||
411 'WHERE ba.ba_name = '''||pBusAreaName||'''';
412
416 edw_log.debug_line(l_stmt);
413 if g_debug_flag = 'Y' then
414 edw_log.debug_line('Procedure initBusAreas');
415 edw_log.debug_line('Going to execute statement:');
417 end if;
418
419
420 EXECUTE IMMEDIATE l_stmt INTO lEulBA_ID, lEulBA_Name, lEulBA_DEVELOPER_KEY;
421
422 eulBusAreaTab(lEulBA_id).ba_id := lEulBA_ID;
423 eulBusAreaTab(lEulBA_id).ba_name := lEulBA_Name;
424 eulBusAreaTab(lEulBA_id).ba_developer_key := lEulBA_DEVELOPER_KEY;
425 eulBusAreaTab(lEulBA_id).ba_records_evaluated := 0;
426 eulBusAreaTab(lEulBA_id).ba_records_hidden := 0;
427
428 END IF;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 g_errbuf:=sqlerrm;
433 g_retcode:=sqlcode;
434
435 raise;
436 END InitBusAreas;
437
438 /******************************************************************************
439 Procedure InitColumns
440 ******************************************************************************/
441 PROCEDURE InitColumns(pBusAreaName IN VARCHAR2,
442 pFolderID IN NUMBER,
443 pTableName IN VARCHAR2,
444 pTableType IN VARCHAR2) IS
445
446 ctr PLS_INTEGER := 0;
447 l_stmt VARCHAR2(2000) := NULL;
448
449 /* Cursor variable to hold table names */
450 TYPE eulColCurType IS REF CURSOR;
451 eulCol_cv eulColCurType;
452
453 BEGIN
454
455 g_processName := 'initColumns';
456
457 l_stmt := 'SELECT NULL BA_ID , '||
458 ' NULL BA_DEVELOPER_KEY ,'||
459 ' '||pFolderID||' folder_id , '||
460 ' folder_items.EXP_ID Item_ID , '||
461 ' folder_items.exp_data_type item_data_type , '||
462 ' folder_items.it_heading item_heading , '||
463 ' folder_items.it_format_mask item_format_mask , '||
464 ' folder_items.exp_name Item_Name , '||
465 ' NULL new_item_name , '||
466 ' folder_items.IT_EXT_COLUMN column_Name , '||
467 ' folder_items.exp_developer_Key column_developer_key , '||
468 ' folder_items.IT_HIDDEN Item_Hidden , '||
469 ' FII_EUL4I_utils.ItemsToHide('''||pBusAreaName||''','''||pTableName||''', folder_items.IT_EXT_COLUMN,folder_items.exp_name) item_sb_hidden , '||
470 ' '''||pTableType||''' table_type '||
471 'FROM '||g_EulOwner||'.eul4_EXPRESSIONS folder_items '||
472 'WHERE folder_items.it_obj_id = '||pFolderId||' '||
473 'ORDER BY folder_items.IT_EXT_COLUMN, '||
474 ' folder_items.exp_id';
475
476 if g_debug_flag = 'Y' then
477 edw_log.debug_line('Procedure initColumns');
478 edw_log.debug_line('Going to execute statement:');
479 edw_log.debug_line(l_stmt);
480 end if;
481
482 OPEN eulCol_cv FOR l_stmt;
483 LOOP
484
485 FETCH eulCol_cv INTO eulColumnsRec;
486 EXIT WHEN eulCol_cv%NOTFOUND;
487
488 eulColumnsTab(eulColumnsRec.item_id) := eulColumnsRec;
489
490 -- /* Delete row if hidden and should be hidden are the same */
491 -- IF eulColumnsTab(eulColumnsRec.item_id).item_hidden = eulColumnsTab(eulColumnsRec.item_id).item_sb_hidden THEN
492
493 -- eulColumnsTab.delete(eulColumnsRec.item_id);
494
495 -- END IF;
496
497 END LOOP;
498
499 CLOSE eulCol_cv;
500
501 EXCEPTION
502 WHEN OTHERS THEN
503 if g_debug_flag = 'Y' then
504 edw_log.put_line('');
505 edw_log.put_line('Error in initColumns');
506 edw_log.put_line('pBusAreaName = '||pBusAreaName);
507 edw_log.put_line('pTableName = '||pTableName);
508 edw_log.put_line('l_stmt '||l_stmt);
509 end if;
510
511 g_errbuf:=sqlerrm;
512 g_retcode:=sqlcode;
513
514 raise;
515
516 END InitColumns;
517
518
519 /******************************************************************************
520 Procedure InitTables
521 ******************************************************************************/
522 PROCEDURE InitTables(pBusAreaName IN VARCHAR2,
523 pAction IN VARCHAR2 DEFAULT 'NULL')
524 IS
525
526 ctr PLS_INTEGER := 0;
527 l_stmt VARCHAR2(3000) := NULL;
528
529 /* Cursor variable to hold table names */
530 TYPE eulTabCurType IS REF CURSOR;
531 eulTab_cv eulTabCurType;
532
533 BEGIN
534
535 g_processName := 'initTables';
536
537 l_stmt := 'SELECT BUSINESS_AREAS.BA_ID BA_ID , '||
538 ' BUSINESS_AREAS.BA_DEVELOPER_KEY BA_DEVELOPER_KEY ,'||
539 ' folders.OBJ_ID Folder_ID , '||
540 ' folders.obj_name Folder_Name , '||
544 ' folders.OBJ_DEVELOPER_KEY OBJ_DEVELOPER_KEY , '||
541 ' NULL new_folder_name , '||
542 ' folders.SOBJ_EXT_TABLE Table_Name , '||
543 '''MIS_''||RTRIM(folders.sobj_ext_table,''M'')||''V'' view_name, '||
545 ' SUBSTRB(folders.SOBJ_EXT_TABLE,-1,1) table_type , '||
546 ' NULL hidden_item_flag , ' ||
547 ' folders.obj_hidden folder_hidden , '||
548 ' folders.obj_hidden folder_sb_hidden '||
549 'FROM '||g_EulOwner||'.eul4_OBJS folders , '||
550 ' '||g_EulOwner||'.eul4_BA_OBJ_LINKS BA_Folders , '||
551 ' '||g_EulOwner||'.eul4_bas BUSINESS_AREAS '||
552 'WHERE business_areas.ba_name = '''||pBusAreaName||''' '||
553 'AND BA_Folders.BOL_OBJ_ID=folders.OBJ_ID '||
554 'AND BA_Folders.BOL_BA_ID=BUSINESS_AREAS.BA_ID '||
555 'AND folders.obj_hidden = 0 '||
556 'AND (NOT EXISTS (SELECT 1 '||
557 'FROM '||g_EulOwner||'.eul4_BA_OBJ_LINKS BA_Folders2 , '||
558 ' '||g_EulOwner||'.eul4_bas BUSINESS_AREAS2 '||
559 'WHERE BA_Folders2.BOL_BA_ID = BUSINESS_AREAS2.BA_ID '||
560 'AND ba_folders2.bol_obj_id = ba_folders.bol_obj_id '||
561 'AND BUSINESS_AREAS2.BA_name NOT IN '||
562 ' ('''||g_BusArea_1||''', '||
563 ' '''||g_BusArea_2||''', '||
564 ' '''||g_BusArea_3||''')) OR '||
565 ' folders.sobj_ext_table IN ('||g_TableList||') OR '||
566 ' folders.sobj_ext_table LIKE ''EDW_GL_ACCT%'' OR '||
567 ' folders.sobj_ext_table LIKE ''MIS_EDW_GL_ACCT%'' ) ' ||
568 'ORDER BY folders.SOBJ_EXT_TABLE, '||
569 ' folders.OBJ_ID ';
570
571
572 if g_debug_flag = 'Y' then
573 edw_log.debug_line('Procedure initTables');
574 edw_log.debug_line('Going to execute statement:');
575 edw_log.debug_line(l_stmt);
576 end if;
577
578
579 -- eulTablesTab.delete;
580 -- eulColumnsTab.delete;
581
582 OPEN eulTab_cv FOR l_stmt;
583 LOOP
584
585 FETCH eulTab_cv INTO eulTablesRec;
586 EXIT WHEN eulTab_cv%NOTFOUND;
587
588 eulTablesTab(eulTablesRec.folder_id) := eulTablesRec;
589
590 InitColumns(pBusAreaName, eulTablesRec.folder_id,eulTablesRec.table_name,eulTablesRec.table_type);
591
592 END LOOP;
593
594 CLOSE eulTab_cv;
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 if g_debug_flag = 'Y' then
599 edw_log.put_line('');
600 edw_log.put_line('Error in initTables');
601 edw_log.put_line('pBusAreaName = '||pBusAreaName);
602 edw_log.put_line('l_stmt '||l_stmt);
603 end if;
604
605 g_errbuf:=sqlerrm;
606 g_retcode:=sqlcode;
607
608 raise;
609
610 END InitTables;
611
612 /******************************************************************************
613 Procedure hideFolders
614 ******************************************************************************/
615 PROCEDURE hideFolders(pFolderID IN NUMBER)
616 IS
617
618 ctr PLS_INTEGER := 0;
619 l_stmt VARCHAR2(1000);
620
621 BEGIN
622
623 g_processName := 'hideFolders';
624
625 l_stmt :=
626 'UPDATE '||g_EulOwner||'.eul4_objs obj '||
627 'SET obj.obj_hidden = 1 '||
628 'WHERE obj.obj_id = '||pFolderId||'';
629
630 IF g_Mode <> 'TEST' THEN
631
632 if g_debug_flag = 'Y' then
633 edw_log.debug_line('Procedure hideFolders');
634 edw_log.debug_line('Going to execute statement:');
635 edw_log.debug_line(l_stmt);
636 end if;
637
638 EXECUTE IMMEDIATE l_stmt;
639
640 END IF;
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 g_errbuf:=sqlerrm;
645 g_retcode:=sqlcode;
646
647 raise;
648
649 END hideFolders;
650
651 /******************************************************************************
652 Procedure hideFolders
653 ******************************************************************************/
654 PROCEDURE hideFolders(pBusAreaID IN NUMBER,
655 pTableName IN VARCHAR2,
656 pViewName IN VARCHAR2)
657
658 IS
659
660 l_stmt VARCHAR2(1000);
661 l_folderId NUMBER(10):= 0;
662
663 TYPE eulFolderCurType IS REF CURSOR;
664 eulFolder_cv eulFolderCurType;
665
666
667 BEGIN
668 g_processName := 'hideFolders';
669
670 l_stmt := 'SELECT obj.obj_id folder_id '||
671 'FROM '||g_EulOwner||'.eul4_objs obj, '||
672 ' '||g_EulOwner||'.eul4_ba_obj_links bol '||
676
673 'WHERE bol.bol_ba_id = '||pBusAreaID||' '||
674 'AND obj.obj_id = bol.bol_obj_id '||
675 'AND oBj.sobj_ext_table IN ('''||pTableName||''' ,'''||pViewName||''') ';
677 if g_debug_flag = 'Y' then
678 edw_log.debug_line('Procedure hideFolders');
679 edw_log.debug_line('Going to execute statement:');
680 edw_log.debug_line(l_stmt);
681 end if;
682
683
684 OPEN eulFolder_cv FOR l_Stmt;
685 LOOP
686
687 FETCH eulFolder_cv INTO l_FolderID;
688 EXIT WHEN eulFolder_cv%NOTFOUND;
689
690 eulTablesTab(l_FolderID).folder_sb_hidden := 1;
691 hideFolders(l_FolderID);
692
693 END LOOP;
694
695 CLOSE eulFolder_cv;
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 g_errbuf:=sqlerrm;
700 g_retcode:=sqlcode;
701
702 raise;
703
704 END hideFolders;
705
706
707 /******************************************************************************
708 Procedure hideEulDimLevels
709 ******************************************************************************/
710 PROCEDURE hideEulDimLevels(pBusAreaID IN NUMBER,
711 pdimTableName IN VARCHAR2,
712 pdimViewName IN VARCHAR2,
713 pLevelName IN VARCHAR2)
714
715 IS
716
717 levelTab DBMS_UTILITY.uncl_array;
718
719 levelStr VARCHAR2(2000) := NULL;
720 rowCtr BINARY_INTEGER;
721
722 l_stmt VARCHAR2(4000);
723
724 GL_FLEX_DIM_TAB_NF EXCEPTION;
725 PRAGMA EXCEPTION_INIT(GL_FLEX_DIM_TAB_NF, -942);
726
727 BEGIN
728 g_processName := 'hideEulDimLevels';
729
730 /* Display all name columns in this hier */
731 hideFolderItems(pBusAreaID , pDimTableName, pdimViewName , pLevelName||'%'||'NAME' , 0);
732
733 /* Display all description columns in this hier */
734 hideFolderItems(pBusAreaID , pDimTableName, pdimViewName , pLevelName||'%'||'DESCRIPTION' , 0);
735
736 l_stmt :=
737 'SELECT DISTINCT '||
738 ' LTRIM( '||
739 ' DECODE(MAX(DECODE(H102_NAME , H103_NAME , 0, 1)),0, ''H102'',NULL)|| '||
740 ' DECODE(MAX(DECODE(H103_NAME , H104_NAME , 0, 1)),0,'',H103'',NULL)|| '||
741 ' DECODE(MAX(DECODE(H104_NAME , H105_NAME , 0, 1)),0,'',H104'',NULL)|| '||
742 ' DECODE(MAX(DECODE(H105_NAME , H106_NAME , 0, 1)),0,'',H105'',NULL)|| '||
743 ' DECODE(MAX(DECODE(H106_NAME , H107_NAME , 0, 1)),0,'',H106'',NULL)|| '||
744 ' DECODE(MAX(DECODE(H107_NAME , H108_NAME , 0, 1)),0,'',H107'',NULL)|| '||
745 ' DECODE(MAX(DECODE(H108_NAME , H109_NAME , 0, 1)),0,'',H108'',NULL)|| '||
746 ' DECODE(MAX(DECODE(H109_NAME , H110_NAME , 0, 1)),0,'',H109'',NULL)|| '||
747 ' DECODE(MAX(DECODE(H110_NAME , H111_NAME , 0, 1)),0,'',H110'',NULL)|| '||
748 ' DECODE(MAX(DECODE(H111_NAME , H112_NAME , 0, 1)),0,'',H111'',NULL)|| '||
749 ' DECODE(MAX(DECODE(H112_NAME , H113_NAME , 0, 1)),0,'',H112'',NULL)|| '||
750 ' DECODE(MAX(DECODE(H113_NAME , H114_NAME , 0, 1)),0,'',H113'',NULL)|| '||
751 ' DECODE(MAX(DECODE(H114_NAME , H115_NAME , 0, 1)),0,'',H114'',NULL)|| '||
752 ' DECODE(MAX(DECODE(H202_NAME , H203_NAME , 0, 1)),0,'',H202'',NULL)|| '||
753 ' DECODE(MAX(DECODE(H203_NAME , H204_NAME , 0, 1)),0,'',H203'',NULL)|| '||
754 ' DECODE(MAX(DECODE(H204_NAME , H205_NAME , 0, 1)),0,'',H204'',NULL)|| '||
755 ' DECODE(MAX(DECODE(H205_NAME , H206_NAME , 0, 1)),0,'',H205'',NULL)|| '||
756 ' DECODE(MAX(DECODE(H206_NAME , H207_NAME , 0, 1)),0,'',H206'',NULL)|| '||
757 ' DECODE(MAX(DECODE(H207_NAME , H208_NAME , 0, 1)),0,'',H207'',NULL)|| '||
758 ' DECODE(MAX(DECODE(H208_NAME , H209_NAME , 0, 1)),0,'',H208'',NULL)|| '||
759 ' DECODE(MAX(DECODE(H209_NAME , H210_NAME , 0, 1)),0,'',H209'',NULL)|| '||
760 ' DECODE(MAX(DECODE(H210_NAME , H211_NAME , 0, 1)),0,'',H210'',NULL)|| '||
761 ' DECODE(MAX(DECODE(H211_NAME , H212_NAME , 0, 1)),0,'',H211'',NULL)|| '||
762 ' DECODE(MAX(DECODE(H212_NAME , H213_NAME , 0, 1)),0,'',H212'',NULL)|| '||
763 ' DECODE(MAX(DECODE(H213_NAME , H214_NAME , 0, 1)),0,'',H213'',NULL)|| '||
764 ' DECODE(MAX(DECODE(H214_NAME , H215_NAME , 0, 1)),0,'',H214'',NULL)|| '||
765 ' DECODE(MAX(DECODE(H302_NAME , H303_NAME , 0, 1)),0,'',H302'',NULL)|| '||
766 ' DECODE(MAX(DECODE(H303_NAME , H304_NAME , 0, 1)),0,'',H303'',NULL)|| '||
767 ' DECODE(MAX(DECODE(H304_NAME , H305_NAME , 0, 1)),0,'',H304'',NULL)|| '||
768 ' DECODE(MAX(DECODE(H305_NAME , H306_NAME , 0, 1)),0,'',H305'',NULL)|| '||
769 ' DECODE(MAX(DECODE(H306_NAME , H307_NAME , 0, 1)),0,'',H306'',NULL)|| '||
770 ' DECODE(MAX(DECODE(H307_NAME , H308_NAME , 0, 1)),0,'',H307'',NULL)|| '||
771 ' DECODE(MAX(DECODE(H308_NAME , H309_NAME , 0, 1)),0,'',H308'',NULL)|| '||
772 ' DECODE(MAX(DECODE(H309_NAME , H310_NAME , 0, 1)),0,'',H309'',NULL)|| '||
773 ' DECODE(MAX(DECODE(H310_NAME , H311_NAME , 0, 1)),0,'',H310'',NULL)|| '||
774 ' DECODE(MAX(DECODE(H311_NAME , H312_NAME , 0, 1)),0,'',H311'',NULL)|| '||
775 ' DECODE(MAX(DECODE(H312_NAME , H313_NAME , 0, 1)),0,'',H312'',NULL)|| '||
776 ' DECODE(MAX(DECODE(H313_NAME , H314_NAME , 0, 1)),0,'',H313'',NULL)|| '||
777 ' DECODE(MAX(DECODE(H314_NAME , H315_NAME , 0, 1)),0,'',H314'',NULL)|| '||
778 ' DECODE(MAX(DECODE(H402_NAME , H403_NAME , 0, 1)),0,'',H402'',NULL)|| '||
779 ' DECODE(MAX(DECODE(H403_NAME , H404_NAME , 0, 1)),0,'',H403'',NULL)|| '||
783 ' DECODE(MAX(DECODE(H407_NAME , H408_NAME , 0, 1)),0,'',H407'',NULL)|| '||
780 ' DECODE(MAX(DECODE(H404_NAME , H405_NAME , 0, 1)),0,'',H404'',NULL)|| '||
781 ' DECODE(MAX(DECODE(H405_NAME , H406_NAME , 0, 1)),0,'',H405'',NULL)|| '||
782 ' DECODE(MAX(DECODE(H406_NAME , H407_NAME , 0, 1)),0,'',H406'',NULL)|| '||
784 ' DECODE(MAX(DECODE(H408_NAME , H409_NAME , 0, 1)),0,'',H408'',NULL)|| '||
785 ' DECODE(MAX(DECODE(H409_NAME , H410_NAME , 0, 1)),0,'',H409'',NULL)|| '||
786 ' DECODE(MAX(DECODE(H410_NAME , H411_NAME , 0, 1)),0,'',H410'',NULL)|| '||
787 ' DECODE(MAX(DECODE(H411_NAME , H412_NAME , 0, 1)),0,'',H411'',NULL)|| '||
788 ' DECODE(MAX(DECODE(H412_NAME , H413_NAME , 0, 1)),0,'',H412'',NULL)|| '||
789 ' DECODE(MAX(DECODE(H413_NAME , H414_NAME , 0, 1)),0,'',H413'',NULL)|| '||
790 ' DECODE(MAX(DECODE(H414_NAME , H415_NAME , 0, 1)),0,'',H414'',NULL),'','') '||
791 'FROM '||pDimTableName;
792
793 if g_debug_flag = 'Y' then
794 edw_log.debug_line('Procedure hideEulDimLevels');
795 edw_log.debug_line('Going to execute statement:');
796 edw_log.debug_line(l_stmt);
797 end if;
798
799 EXECUTE IMMEDIATE l_stmt INTO levelStr;
800
801 DBMS_UTILITY.COMMA_TO_TABLE(levelStr,rowCtr,levelTab);
802
803 FOR ctr IN 1..levelTab.count LOOP
804
805 IF levelTab(ctr) IS NOT NULL AND levelTab(ctr) LIKE pLevelName THEN
806
807 hideFolderItems(pBusAreaID , pDimTableName, pdimViewName , levelTab(ctr)||'%');
808
809 END IF;
810
811 END LOOP;
812
813 EXCEPTION
814 WHEN GL_FLEX_DIM_TAB_NF
815 THEN
816 if g_debug_flag = 'Y' then
817 edw_log.put_line('GL Flex Dimension Tables not found in '||g_fiischema||' schema');
818 end if;
819
820 WHEN OTHERS THEN
821
822 g_errbuf:=sqlerrm;
823 g_retcode:=sqlcode;
824
825 raise;
826
827 END hideEulDimLevels;
828
829
830 /******************************************************************************
831 Procedure eulGLFlexDimMaint
832 ******************************************************************************/
833 PROCEDURE eulGLFlexDimMaint
834 IS
835
836 ctr PLS_INTEGER := 0;
837 ba_ctr PLS_INTEGER := 0;
838 l_stmt VARCHAR2(2000) := NULL;
839
840 GL_FLEX_DIM_TAB_NF EXCEPTION;
841 PRAGMA EXCEPTION_INIT(GL_FLEX_DIM_TAB_NF, -942);
842
843 BEGIN
844 g_processName := 'eulGLFlexDimMaint';
845
846
847 /* Load GL Flex dimension table names into array */
848 dimTab(1).table_name := 'EDW_GL_ACCT1_M';
849 dimTab(2).table_name := 'EDW_GL_ACCT2_M';
850 dimTab(3).table_name := 'EDW_GL_ACCT3_M';
851 dimTab(4).table_name := 'EDW_GL_ACCT4_M';
852 dimTab(5).table_name := 'EDW_GL_ACCT5_M';
853 dimTab(6).table_name := 'EDW_GL_ACCT6_M';
854 dimTab(7).table_name := 'EDW_GL_ACCT7_M';
855 dimTab(8).table_name := 'EDW_GL_ACCT8_M';
856 dimTab(9).table_name := 'EDW_GL_ACCT9_M';
857 dimTab(10).table_name := 'EDW_GL_ACCT10_M';
858
859 /* Load GL Flex dimension table and hierarchy status into array */
860 FOR ctr IN 1..dimTab.count LOOP
861
862 dimTab(ctr).view_name := 'MIS_'||RTRIM(dimTab(ctr).table_name,'M')||'V';
863
864 l_stmt := 'SELECT COUNT(*), '||
865 'DECODE(LEAST(COUNT(DISTINCT DECODE( L1_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , L1_NAME)),1),1,''Used'',''Not Used'') L1_NAME, '||
866 'DECODE(LEAST(COUNT(DISTINCT DECODE(H115_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H115_NAME)),1),1,''Used'',''Not Used'') H115_NAME, '||
867 'DECODE(LEAST(COUNT(DISTINCT DECODE(H215_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H215_NAME)),1),1,''Used'',''Not Used'') H215_NAME, '||
868 'DECODE(LEAST(COUNT(DISTINCT DECODE(H315_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H315_NAME)),1),1,''Used'',''Not Used'') H315_NAME, '||
869 'DECODE(LEAST(COUNT(DISTINCT DECODE(H415_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H415_NAME)),1),1,''Used'',''Not Used'') H415_NAME '||
870 'FROM '||dimTab(ctr).table_name;
871
872 if g_debug_flag = 'Y' then
873 edw_log.debug_line('Procedure eulGLFlexDimMaint');
874 edw_log.debug_line('Going to execute statement:');
875 edw_log.debug_line(l_stmt);
876 end if;
877
878
879 EXECUTE IMMEDIATE l_stmt INTO dimTab(ctr).rowcnt , dimTab(ctr).table_status , dimTab(ctr).hier1_status , dimTab(ctr).hier2_status , dimTab(ctr).hier3_status , dimTab(ctr).hier4_status;
880
881 if g_debug_flag = 'Y' then
882 edw_log.put_line('Table :'||dimTab(ctr).table_name);
883 -- edw_log.put_line(' View :'||dimTab(ctr).view_name);
884 edw_log.put_line(' Rows :'||dimTab(ctr).rowcnt);
885 edw_log.put_line(' Table Status :'||dimTab(ctr).table_status);
886 edw_log.put_line(' Hier 1 Status :'||dimTab(ctr).hier1_status);
887 edw_log.put_line(' Hier 2 Status :'||dimTab(ctr).hier2_status);
888 edw_log.put_line(' Hier 3 Status :'||dimTab(ctr).hier3_status);
889 edw_log.put_line(' Hier 4 Status :'||dimTab(ctr).hier4_status);
890 edw_log.put_line('');
891 end if;
892
893
894 -- Hide Unused dimensions and hier
895 --
896 ba_ctr := eulBusAreaTab.first;
897
898 FOR ctr1 IN 1..eulBusAreaTab.count LOOP
899 --
900
904
901 IF dimTab(ctr).table_status = 'Not Used' THEN
902 --
903 hideFolders(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name ,dimTab(ctr).view_name);
905 ELSE
906
907 -- Hide columns for hierarchy 1
908 IF dimTab(ctr).hier1_status = 'Not Used' THEN
909
910 hideFolderItems(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
911 dimTab(ctr).view_name , 'H1%');
912 ELSE
913 -- Hide unused columns within used hierachies
914 hideEulDimLevels(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
915 dimTab(ctr).view_name , 'H1%');
916 END IF;
917
918 -- Hide columns for hierarchy 2
919 IF dimTab(ctr).hier2_status = 'Not Used' THEN
920
921 hideFolderItems(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
922 dimTab(ctr).view_name , 'H2%');
923 ELSE
924 -- Hide unused columns within used hierachies
925 hideEulDimLevels(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
926 dimTab(ctr).view_name , 'H2%');
927 END IF;
928
929
930 -- Hide columns for hierarchy 3
931 IF dimTab(ctr).hier3_status = 'Not Used' THEN
932
933 hideFolderItems(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
934 dimTab(ctr).view_name , 'H3%');
935 ELSE
936 -- Hide unused columns within used hierachies
937 hideEulDimLevels(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
938 dimTab(ctr).view_name , 'H3%');
939 END IF;
940
941
942 -- Hide columns for hierarchy 4
943 IF dimTab(ctr).hier4_status = 'Not Used' THEN
944
945 hideFolderItems(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
946 dimTab(ctr).view_name , 'H4%');
947 ELSE
948 -- Hide unused columns within used hierachies
949 hideEulDimLevels(eulBusAreaTab(ba_ctr).ba_id , dimTab(ctr).table_name,
950 dimTab(ctr).view_name , 'H4%');
951 END IF;
952 --
953 END IF;
954 --
955 ba_ctr := eulBusAreaTab.next(ba_ctr);
956
957 END LOOP;
958 --
959 END LOOP;
960
961 EXCEPTION
962 WHEN GL_FLEX_DIM_TAB_NF
963 THEN
964 if g_debug_flag = 'Y' then
965 edw_log.put_line('GL Flex Dimension Tables not found in '||g_fiischema||' schema');
966 end if;
967
968 WHEN OTHERS THEN
969 edw_log.put_line('');
970 edw_log.put_line('Error in eulGLFlexDimMaint '||eulBusAreaTab(ba_ctr).ba_name);
971 edw_log.put_line('dimTab(ctr).table_status = '||dimTab(ctr).table_status);
972 edw_log.put_line('ctr = '||ctr);
973 edw_log.put_line('Table :'||dimTab(ctr).table_name);
974 -- edw_log.put_line(' View :'||dimTab(ctr).view_name);
975 edw_log.put_line(' Rows :'||dimTab(ctr).rowcnt);
976 edw_log.put_line(' Table Status :'||dimTab(ctr).table_status);
977 edw_log.put_line(' Hier 1 Status :'||dimTab(ctr).hier1_status);
978 edw_log.put_line(' Hier 2 Status :'||dimTab(ctr).hier2_status);
979 edw_log.put_line(' Hier 3 Status :'||dimTab(ctr).hier3_status);
980 edw_log.put_line(' Hier 4 Status :'||dimTab(ctr).hier4_status);
981 edw_log.put_line('');
982
983 g_errbuf:=sqlerrm;
984 g_retcode:=sqlcode;
985
986 raise;
987
988 END eulGLFlexDimMaint;
989
990 /******************************************************************************
991 Procedure eulHierDelete
992 ******************************************************************************/
993 Procedure eulHierDelete
994 IS
995 -- Hierarchies
996 -- 445 Week
997 -- Enterprise Calendar
998 -- GL Period
999 -- Gregorian Calendar
1000 -- Project Period
1001
1002 /* 'Project Intelligence Business Area' */
1003 project_hier1 VARCHAR2(5) := 'P445%';
1004 project_hier2 VARCHAR2(5) := 'zzz';
1005 project_hier3 VARCHAR2(5) := 'zzz';
1006 project_hier4 VARCHAR2(5) := 'zzz';
1007
1008 /* 'Payables Intelligence Business Area' */
1009 payables_hier1 VARCHAR2(5) := 'P445%';
1010 payables_hier2 VARCHAR2(5) := 'PPER%';
1011 payables_hier3 VARCHAR2(5) := 'EC%';
1012 payables_hier4 VARCHAR2(5) := 'YEAR%';
1013
1014 /* 'Revenue Intelligence Business Area' */
1015 revenue_hier1 VARCHAR2(5) := 'P445%';
1016 revenue_hier2 VARCHAR2(5) := 'EC%%';
1017 revenue_hier3 VARCHAR2(5) := 'YEAR%';
1018 revenue_hier4 VARCHAR2(5) := 'zzz';
1019
1020
1021 l_stmt VARCHAR2(4000);
1022 l_hierId NUMBER(10):= 0;
1023
1024 TYPE eulHierCurType IS REF CURSOR;
1025 eulHier_cv eulHierCurType;
1026
1027 BEGIN
1028
1029 l_stmt := 'SELECT DISTINCT hi_id '||
1030 'FROM '||g_EulOwner||'.eul4_OBJS folders , '||
1031 ' '||g_EulOwner||'.eul4_BA_OBJ_LINKS ba_folders , '||
1032 ' '||g_EulOwner||'.eul4_BAS business_areas , '||
1033 ' '||g_EulOwner||'.eul4_EXPRESSIONS folder_items , '||
1037 ' '||g_EulOwner||'.eul4_HIERARCHIES hier '||
1034 ' '||g_EulOwner||'.eul4_IG_EXP_LINKS item_to_hier , '||
1035 ' '||g_EulOwner||'.eul4_HI_NODES hier_nodes , '||
1036 ' '||g_EulOwner||'.eul4_HI_SEGMENTS hier_segments, '||
1038 'WHERE ( '||
1039 ' /*PROJECTS */ '||
1040 ' (business_areas.ba_name = '''||G_BusArea_3||''' AND '||
1041 ' (folder_items.it_ext_column LIKE '''||project_hier1||''' OR '||
1042 ' folder_items.it_ext_column LIKE '''||project_hier2||''' OR '||
1043 ' folder_items.it_ext_column LIKE '''||project_hier3||''' OR '||
1044 ' folder_items.it_ext_column LIKE '''||project_hier4||''')) '||
1045 ' OR '||
1046 ' /* Payables */ '||
1047 ' (business_areas.ba_name = '''||G_BusArea_2||''' AND '||
1048 ' (folder_items.it_ext_column LIKE '''||payables_hier1||''' OR '||
1049 ' folder_items.it_ext_column LIKE '''||payables_hier2||''' OR '||
1050 ' folder_items.it_ext_column LIKE '''||payables_hier3||''' OR '||
1051 ' folder_items.it_ext_column LIKE '''||payables_hier4||''')) '||
1052 ' OR '||
1053 ' /* Revenue */ '||
1054 ' (business_areas.ba_name = '''||G_BusArea_1||''' AND '||
1055 ' (folder_items.it_ext_column LIKE '''||revenue_hier1||''' OR '||
1056 ' folder_items.it_ext_column LIKE '''||revenue_hier2||''' OR '||
1057 ' folder_items.it_ext_column LIKE '''||revenue_hier3||''' OR '||
1058 ' folder_items.it_ext_column LIKE '''||revenue_hier4||''')) '||
1059 ' ) '||
1060 'AND ba_folders.bol_ba_id = business_areas.ba_id '||
1061 'AND ba_folders.bol_obj_id = folders.obj_id '||
1062 'AND folders.sobj_ext_table = ''EDW_TIME_M'' '||
1063 'AND folder_items.it_obj_id = folders.obj_id '||
1064 'AND folder_items.exp_id = item_to_hier.hil_exp_id '||
1065 'AND item_to_hier.hil_hn_id = hier_nodes.hn_iD '||
1066 'AND hier_segments.ihs_hi_id = hier_nodes.hn_hi_id '||
1067 'AND hier_segments.ihs_hn_id_child = hier_nodes.hn_id '||
1068 'AND hier_nodes.hn_hi_id = hier.hi_id';
1069
1070 if g_debug_flag = 'Y' then
1071 edw_log.debug_line('Procedure eulHierDelete');
1072 edw_log.debug_line('Going to execute statement:');
1073 edw_log.debug_line(l_stmt);
1074 end if;
1075
1076 IF g_mode <> 'TEST' THEN
1077
1078 OPEN eulHier_cv FOR l_Stmt;
1079 LOOP
1080
1081 FETCH eulHier_cv INTO l_HierID;
1082 EXIT WHEN eulHier_cv%NOTFOUND;
1083
1084 -- Delete parent-child relationships
1085 EXECUTE IMMEDIATE 'DELETE '||
1086 'FROM '||g_EulOwner||'.eul4_HI_SEGMENTS hier_segments '||
1087 'WHERE hier_segments.ihs_hi_id = :1' USING l_HierId;
1088
1089 -- Delete links from hier nodes to folder.items
1090 EXECUTE IMMEDIATE 'DELETE '||
1091 'FROM '||g_EulOwner||'.eul4_IG_EXP_LINKS item_to_hier '||
1092 'WHERE hil_hn_id IN '||
1093 '(SELECT hn_id '||
1094 ' FROM '||g_EulOwner||'.eul4_HI_NODES '||
1095 ' WHERE hn_hi_id = :1)' USING l_HierId;
1096
1097 -- Delete hier nodes
1098 EXECUTE IMMEDIATE 'DELETE '||
1099 'FROM '||g_EulOwner||'.eul4_HI_NODES '||
1100 'WHERE hn_hi_id = :1' USING l_HierId;
1101
1102 -- Delete hier
1103 EXECUTE IMMEDIATE 'DELETE '||
1104 'FROM '||g_EulOwner||'.eul4_HIERARCHIES hier '||
1105 'WHERE hier.hi_id = :1' USING l_HierId;
1106
1107 END LOOP;
1108
1109 CLOSE eulHier_cv;
1110
1111 if g_debug_flag = 'Y' then
1112 edw_log.put_line('');
1113 edw_log.put_line('Deleted Unused time hierarchies from FII Business Areas.');
1114 edw_log.put_line('');
1115 end if;
1116
1117 END IF;
1118
1119 END eulHierDelete;
1120
1121
1122 /******************************************************************************
1123 Procedure EULMaint
1124 ******************************************************************************/
1125
1126 PROCEDURE EULMaint(Errbuf IN OUT NOCOPY VARCHAR2,
1127 Retcode IN OUT NOCOPY VARCHAR2,
1128 pEulOwnerName IN VARCHAR2,
1129 pMode IN VARCHAR2,
1130 pBusAreaName IN VARCHAR2,
1131 pAction IN VARCHAR2)
1132 IS
1133
1134 l_exception_msg VARCHAR2(2400):=Null;
1135 ctr PLS_INTEGER := 0;
1136 lFactChangeCtr PLS_INTEGER := 0;
1137 lDimChangeCtr PLS_INTEGER := 0;
1138 ba_ctr PLS_INTEGER := 0;
1139 tab_ctr PLS_INTEGER := 0;
1140 col_ctr PLS_INTEGER := 0;
1141 l_stmt VARCHAR2(2000) := NULL;
1142
1143 BEGIN
1144
1145 CHILD_SETUP(pEulOwnerName||'_'||pBusAreaName||'_'||pMode);
1146
1150
1147 g_EulOwner := UPPER(pEulOwnerName);
1148 g_Mode := UPPER(pMode);
1149
1151 /* Set Business Area Nanes */
1152 IF pBusAreaName IS NULL THEN
1153
1154 /* Set FII Bus Areas */
1155 InitBusAreas(G_BusArea_1,pAction);
1156 InitBusAreas(G_BusArea_2,pAction);
1157 InitBusAreas(G_BusArea_3,pAction);
1158
1159 /* Set Bus Area Tables */
1160 InitTables(G_BusArea_1);
1161 InitTables(G_BusArea_2);
1162 InitTables(G_BusArea_3);
1163
1164 ELSE
1165
1166 /* Set Bus Area */
1167 InitBusAreas(pBusAreaName,pAction);
1168
1169 /* Set Bus Area Tables */
1170 InitTables(pBusAreaName);
1171
1172 END IF;
1173
1174 -- hideFolders(eulTablesTab);
1175
1176 /* Remove unused time hierarchies for FII bus areas */
1177 eulHierDelete;
1178
1179 /* Test usage of GL Acct FlexDimensions
1180 Hide duplicate levels and unused hierarchies and dimensions
1181 */
1182 eulGLFlexDimMaint;
1183
1184
1185
1186 /* Hide folder items */
1187 ctr := eulColumnsTab.first;
1188
1189 FOR col IN 1..eulColumnsTab.count LOOP
1190
1191 eulColumnsTab(ctr).BA_ID := eulTablesTab(eulColumnsTab(ctr).folder_id).BA_ID ;
1192
1193 ba_ctr := eulColumnsTab(ctr).BA_ID ;
1194
1195 eulBusAreaTab(ba_ctr).ba_records_evaluated := eulBusAreaTab(ba_ctr).ba_records_evaluated + 1;
1196
1197 /* test for number columns to reformat */
1198 IF eulColumnsTab(ctr).item_data_type = 2 AND
1199 eulColumnsTab(ctr).table_type = 'F' THEN
1200
1201 /*
1202 edw_log.put_line('Item Name '||eulColumnsTab(ctr).item_name);
1203 edw_log.put_line('Item Heading '||eulColumnsTab(ctr).item_heading);
1204 edw_log.put_line('Item Format '||eulColumnsTab(ctr).item_format_mask);
1205 */
1206 l_stmt := 'UPDATE '||g_EulOwner||'.eul4_expressions '||
1207 'SET it_heading = NVL(it_heading,exp_name) , '||
1208 ' it_format_mask = NVL(it_format_mask , ''999G999G999G999'') '||
1209 'WHERE exp_id = :1';
1210
1211 EXECUTE IMMEDIATE l_stmt USING eulColumnsTab(ctr).item_id;
1212
1213 COMMIT;
1214
1215 END IF;
1216
1217 /* Test for columns to Hide */
1218 IF eulColumnsTab(ctr).item_sb_hidden = 1 AND
1219 eulColumnsTab(ctr).item_hidden = 0 THEN
1220
1221 hideFolderItems(eulColumnsTab(ctr).item_id);
1222 /* Update Business Area Ctr for hidden items */
1223 eulBusAreaTab(ba_ctr).ba_records_hidden := eulBusAreaTab(ba_ctr).ba_records_hidden + 1;
1224
1225 IF eulColumnsTab(ctr).table_type = 'M' THEN
1226
1227 lDimChangeCtr := lDimChangeCtr + 1;
1228
1229 ELSIF eulColumnsTab(ctr).table_type = 'F' THEN
1230
1231 lFactChangeCtr := lFactChangeCtr + 1;
1232
1233 ELSE
1234
1235 NULL;
1236
1237 END IF;
1238
1239 ELSIF /*Display instance and other columns for Projects */
1240 eulColumnsTab(ctr).item_sb_hidden = 0 AND
1241 eulColumnsTab(ctr).item_hidden = 1 AND
1242 NVL(INSTR(eulColumnsTab(ctr).column_name,'INSTANCE'),0) > 0 AND
1243 eulBusAreaTab(ba_ctr).ba_name = G_BusArea_3 THEN
1244 /* in progress */
1245 -- hideFolderItems(eulColumnsTab(ctr).item_id,0);
1246 NULL;
1247
1248 ELSE
1249
1250 NULL;
1251
1252 END IF;
1253
1254 ctr := eulColumnsTab.next(ctr);
1255
1256 END LOOP;
1257
1258 /* Report on results */
1259 ba_ctr := eulBusAreaTab.first;
1260
1261 FOR ba IN 1..eulBusAreaTab.count LOOP
1262
1263 if g_debug_flag = 'Y' then
1264 edw_log.put_line('');
1265 edw_log.put_line(eulBusAreaTab(ba_ctr).ba_name);
1266 edw_log.put_line('');
1267 edw_log.put_line(' '||RPAD('Table Name',30,' ')||' '||'Folder Name');
1268 edw_log.put_line(' '||RPAD('-',30,'-')||' '||RPAD('-',30,'-'));
1269 end if;
1270
1271 tab_ctr := eulTablesTab.first;
1272
1273 FOR tab IN 1..eulTablesTab.count LOOP
1274
1275 IF eulTablesTab(tab_ctr).BA_ID = eulBusAreaTab(ba_ctr).ba_id THEN
1276 if g_debug_flag = 'Y' then
1277 edw_log.PUT_LINE(' '||RPAD(eulTablesTab(tab_ctr).table_name,30,' ')||' '||eulTablesTab(tab_ctr).folder_name);
1278 end if;
1279 IF eulTablesTab(tab_ctr).hidden_item_flag = 'Y' AND eulTablesTab(tab_ctr).folder_sb_hidden = 0 THEN
1280
1281 if g_debug_flag = 'Y' then
1282 edw_log.put_line(' '||RPAD('Column Name',30,' ')||' '||'Change '||' '||'Item Name');
1283 edw_log.put_line(' '||RPAD('-',30,'-')||' '||RPAD('-',30,'-'));
1284 end if;
1285
1286 col_ctr := eulColumnsTab.first;
1287
1288 FOR col IN 1..eulColumnsTab.count LOOP
1289
1290 IF eulColumnsTab(col_ctr).folder_id = eulTablesTab(tab_ctr).folder_Id THEN
1291
1292 IF eulColumnsTab(col_ctr).item_sb_hidden = 1 AND
1293 eulColumnsTab(col_ctr).item_hidden = 0 THEN
1294
1295 if g_debug_flag = 'Y' then
1296 edw_log.put_line(' '||RPAD(eulColumnsTab(col_ctr).column_name,30,' ')||' '||'Hide '||' '||eulColumnsTab(col_ctr).item_name);
1297 end if;
1298
1299 ELSIF eulColumnsTab(col_ctr).item_sb_hidden = 0 AND
1300 eulColumnsTab(col_ctr).item_hidden = 1 THEN
1301
1302 if g_debug_flag = 'Y' then
1303 edw_log.put_line(' '||RPAD(eulColumnsTab(col_ctr).column_name,30,' ')||' '||'Display'||' '||eulColumnsTab(col_ctr).item_name);
1304 end if;
1305 ELSE
1306
1307 NULL;
1308
1309 END IF;
1310
1311 END IF;
1312
1313 col_ctr := eulColumnsTab.next(col_ctr);
1314
1315 END LOOP;
1316 if g_debug_flag = 'Y' then
1317 edw_log.put_line(' ');
1318 end if;
1319
1320 END IF;
1321
1322 END IF;
1323
1324 tab_ctr := eulTablesTab.next(tab_ctr);
1325
1326 END LOOP;
1327 if g_debug_flag = 'Y' then
1328 edw_log.put_line(' ');
1329 end if;
1330 ba_ctr := eulBusAreaTab.next(ba_ctr);
1331
1332 END LOOP;
1333
1334 ba_ctr := eulBusAreaTab.first;
1335
1336 FOR ba IN 1..eulBusAreaTab.count LOOP
1337
1338 if g_debug_flag = 'Y' then
1339 edw_log.put_line('');
1340 edw_log.put_line(eulBusAreaTab(ba_ctr).ba_name||' - ');
1341 edw_log.put_line('Items Evaluated:'||eulBusAreaTab(ba_ctr).ba_records_evaluated);
1342 edw_log.put_line('Items Hidden:'||eulBusAreaTab(ba_ctr).ba_records_hidden);
1343 end if;
1344 ba_ctr := eulBusAreaTab.next(ba_ctr);
1345
1346 END LOOP;
1347 if g_debug_flag = 'Y' then
1348 edw_log.put_line(eulColumnsTab.count||' Items Evaluated');
1349 edw_log.put_line(lFactChangeCtr||' Fact Items Hidden');
1350 edw_log.put_line(lDimChangeCtr||' Dimension Items Hidden');
1351
1352 end if;
1353
1354 COMMIT;
1355
1356 EXCEPTION
1357
1358 WHEN G_BUSINESS_AREA_DOES_NOT_EXIST THEN
1359 Errbuf := g_errbuf;
1360 Retcode := g_retcode;
1361 l_exception_msg := Retcode || ':' || Errbuf;
1362 if g_debug_flag = 'Y' then
1363 edw_log.put_line('Business Area Name not found');
1364 edw_log.put_line('Process : '||g_processName);
1365 end if;
1366 raise;
1367
1368 WHEN G_EUL_OWNER_DOES_NOT_EXIST THEN
1369 Errbuf:=g_errbuf;
1370 Retcode:=g_retcode;
1371 l_exception_msg := Retcode || ':' || Errbuf;
1372 if g_debug_flag = 'Y' then
1373 edw_log.put_line('End User Layer (EUL) Owner not found');
1374 edw_log.put_line('Process : '||g_processName);
1375 end if;
1376 raise;
1377
1378 WHEN OTHERS THEN
1379 Errbuf:=g_errbuf;
1380 Retcode:=g_retcode;
1381 l_exception_msg := Retcode || ':' || Errbuf;
1382 if g_debug_flag = 'Y' then
1383 edw_log.put_line(l_exception_msg);
1384 edw_log.put_line('pAction'||pAction);
1385 edw_log.put_line('pEulOwnerName'||g_EulOwner);
1386 edw_log.put_line('pBusAreaName'||pBusAreaName);
1387 edw_log.put_line('pMode'||g_mode);
1388 edw_log.put_line('Process : '||g_processName);
1389 edw_log.put_line('l_stmt : '||l_stmt);
1390 end if;
1391 raise;
1392
1393 END EulMaint;
1394
1395 END FII_EUL4I_UTILS;