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