[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(PColumnNameIn LIKE '%LAST_UPDATE_DATE' AND
/* POA Request for DUNS lud */
pColumnNameIn NOT IN ('DNMR_DNB_LAST_UPDATE_DATE')) OR
-- Hide reservered columns, inactive,start and end dates for dimensions
(PTableNameIn LIKE '%_M' AND
(PColumnNameIn LIKE '%_INACTIVE_DATE' OR
pColumnNameIn LIKE '%_LEVEL_NAME' OR
NVL(INSTR(pColumnNameIn,'_ID_'),0) > 0 OR
((PColumnNameIn LIKE '%_START_DATE%' OR
PColumnNameIn LIKE '%_STRT_DATE%' OR
PColumnNameIn LIKE '%_END_DATE%') AND
SUBSTRB(pColumnNameIn,1,4) NOT IN ('CYR_','CPER','CQTR' /* EDW_TIME_M */,
'TASK','TTSK' /* EDW_PROJECT_M */,
'ASGN','PERS' /* EDW_HR_PERSON_M */)) OR
PColumnNameIn LIKE '%_DP')) OR
(pTableNameIn = 'EDW_ORGANIZATION_M' AND
NVL(INSTR(pColumnNameIn,'_CAT_'),0) > 0 ) OR
FII_EUL_UTILS_2.ItemsToHide(pBusAreaNameIn,
pTableNameIn,
pColumnNameIn,
pItemNameIn) = 1
--
THEN RETURN_VALUE := 1;
l_stmt := 'UPDATE '||g_EulOwner||'.eul_expressions exp '||
'SET exp.it_hidden = '||pHideDisplay||' '||
'WHERE exp.exp_id = '||pItemId;
/* Update tables record to indicate that items were hidden for that table */
eulTablesTab(eulColumnsTab(pItemID).folder_id).hidden_item_flag := 'Y';
l_stmt := 'SELECT exp.exp_id '||
'FROM '||g_EulOwner||'.eul_expressions exp , '||
' '||g_EulOwner||'.eul_objs obj, '||
' '||g_EulOwner||'.eul_ba_obj_links bol '||
'WHERE bol.bol_ba_id = '||pBusAreaID||' '||
'AND obj.obj_id = bol.bol_obj_id '||
'AND obj.obj_hidden = 0 '||
'AND obj.sobj_ext_table IN ('''||pTableName||''' ,'''||pViewName||''') '||
'AND exp.ite_obj_id = obj.obj_id '||
'AND exp.it_ext_column LIKE '''||pColumn||'''';
/* Update tables record to indicate that items were hidden for that table */
eulTablesTab(eulColumnsTab(l_ItemID).folder_id).hidden_item_flag := 'Y';
eulBusAreaTab.delete;
l_stmt := 'SELECT ba.ba_id, '||
' ba.ba_name,'||
' ba.ba_name ba_developer_key '||
'FROM '||g_EulOwner||'.EUL_BUSINESS_AREAS ba '||
'WHERE ba.ba_name = '''||pBusAreaName||'''';
l_stmt := 'SELECT NULL bus_area_id , '||
' '||pFolderID||' folder_id , '||
' folder_items.EXP_ID Item_ID , '||
' folder_items.exp_data_type item_data_type , '||
' folder_items.it_heading item_heading , '||
' folder_items.ite_format_mask item_format_mask , '||
' folder_items.exp_name Item_Name , '||
' NULL new_item_name , '||
' folder_items.IT_EXT_COLUMN column_Name , '||
' folder_items.IT_EXT_COLUMN column_developer_key , '||
' folder_items.IT_HIDDEN Item_Hidden , '||
' fii_eul_utils.ItemsToHide('''||pBusAreaName||''','''||pTableName||''', folder_items.IT_EXT_COLUMN,folder_items.exp_name) item_sb_hidden , '||
' '''||pTableType||''' table_type '||
'FROM '||g_EulOwner||'.EUL_EXPRESSIONS folder_items '||
'WHERE folder_items.ite_obj_id = '||pFolderId||' '||
'ORDER BY folder_items.IT_EXT_COLUMN, '||
' folder_items.exp_id';
l_stmt := 'SELECT BUSINESS_AREAS.BA_ID bus_area_id , '||
' folders.OBJ_ID Folder_ID , '||
' folders.obj_name Folder_Name , '||
' NULL new_folder_name , '||
' folders.SOBJ_EXT_TABLE Table_Name , '||
'''MIS_''||RTRIM(folders.sobj_ext_table,''M'')||''V'' view_name, '||
' folders.SOBJ_EXT_TABLE OBJ_DEVELOPER_KEY , '||
' SUBSTRB(folders.SOBJ_EXT_TABLE,-1,1) table_type , '||
' NULL hidden_item_flag , ' ||
' folders.obj_hidden folder_hidden , '||
' folders.obj_hidden folder_sb_hidden '||
'FROM '||g_EulOwner||'.EUL_OBJS folders , '||
' '||g_EulOwner||'.EUL_BA_OBJ_LINKS BA_Folders , '||
' '||g_EulOwner||'.EUL_BUSINESS_AREAS BUSINESS_AREAS '||
'WHERE business_areas.ba_name = '''||pBusAreaName||''' '||
'AND BA_Folders.BOL_OBJ_ID=folders.OBJ_ID '||
'AND BA_Folders.BOL_BA_ID=BUSINESS_AREAS.BA_ID '||
'AND folders.obj_hidden = 0 '||
'AND (NOT EXISTS (SELECT 1 '||
'FROM '||g_EulOwner||'.EUL_BA_OBJ_LINKS BA_Folders2 , '||
' '||g_EulOwner||'.EUL_BUSINESS_AREAS BUSINESS_AREAS2 '||
'WHERE BA_Folders2.BOL_BA_ID = BUSINESS_AREAS2.BA_ID '||
'AND ba_folders2.bol_obj_id = ba_folders.bol_obj_id '||
'AND BUSINESS_AREAS2.BA_name NOT IN '||
' ('''||g_BusArea_1||''', '||
' '''||g_BusArea_2||''', '||
' '''||g_BusArea_3||''')) OR '||
' folders.sobj_ext_table IN ('||g_TableList||') OR '||
' folders.sobj_ext_table LIKE ''EDW_GL_ACCT%'' OR '||
' folders.sobj_ext_table LIKE ''MIS_EDW_GL_ACCT%'' ) ' ||
'ORDER BY folders.SOBJ_EXT_TABLE, '||
' folders.OBJ_ID ';
'UPDATE '||g_EulOwner||'.eul_objs obj '||
'SET obj.obj_hidden = 1 '||
'WHERE obj.obj_id = '||pFolderId||'';
l_stmt := 'SELECT obj.obj_id folder_id '||
'FROM '||g_EulOwner||'.eul_objs obj, '||
' '||g_EulOwner||'.eul_ba_obj_links bol '||
'WHERE bol.bol_ba_id = '||pBusAreaID||' '||
'AND obj.obj_id = bol.bol_obj_id '||
'AND oBj.sobj_ext_table IN ('''||pTableName||''' ,'''||pViewName||''') ';
'SELECT DISTINCT '||
' LTRIM( '||
' DECODE(MAX(DECODE(H102_NAME , H103_NAME , 0, 1)),0, ''H102'',NULL)|| '||
' DECODE(MAX(DECODE(H103_NAME , H104_NAME , 0, 1)),0,'',H103'',NULL)|| '||
' DECODE(MAX(DECODE(H104_NAME , H105_NAME , 0, 1)),0,'',H104'',NULL)|| '||
' DECODE(MAX(DECODE(H105_NAME , H106_NAME , 0, 1)),0,'',H105'',NULL)|| '||
' DECODE(MAX(DECODE(H106_NAME , H107_NAME , 0, 1)),0,'',H106'',NULL)|| '||
' DECODE(MAX(DECODE(H107_NAME , H108_NAME , 0, 1)),0,'',H107'',NULL)|| '||
' DECODE(MAX(DECODE(H108_NAME , H109_NAME , 0, 1)),0,'',H108'',NULL)|| '||
' DECODE(MAX(DECODE(H109_NAME , H110_NAME , 0, 1)),0,'',H109'',NULL)|| '||
' DECODE(MAX(DECODE(H110_NAME , H111_NAME , 0, 1)),0,'',H110'',NULL)|| '||
' DECODE(MAX(DECODE(H111_NAME , H112_NAME , 0, 1)),0,'',H111'',NULL)|| '||
' DECODE(MAX(DECODE(H112_NAME , H113_NAME , 0, 1)),0,'',H112'',NULL)|| '||
' DECODE(MAX(DECODE(H113_NAME , H114_NAME , 0, 1)),0,'',H113'',NULL)|| '||
' DECODE(MAX(DECODE(H114_NAME , H115_NAME , 0, 1)),0,'',H114'',NULL)|| '||
' DECODE(MAX(DECODE(H202_NAME , H203_NAME , 0, 1)),0,'',H202'',NULL)|| '||
' DECODE(MAX(DECODE(H203_NAME , H204_NAME , 0, 1)),0,'',H203'',NULL)|| '||
' DECODE(MAX(DECODE(H204_NAME , H205_NAME , 0, 1)),0,'',H204'',NULL)|| '||
' DECODE(MAX(DECODE(H205_NAME , H206_NAME , 0, 1)),0,'',H205'',NULL)|| '||
' DECODE(MAX(DECODE(H206_NAME , H207_NAME , 0, 1)),0,'',H206'',NULL)|| '||
' DECODE(MAX(DECODE(H207_NAME , H208_NAME , 0, 1)),0,'',H207'',NULL)|| '||
' DECODE(MAX(DECODE(H208_NAME , H209_NAME , 0, 1)),0,'',H208'',NULL)|| '||
' DECODE(MAX(DECODE(H209_NAME , H210_NAME , 0, 1)),0,'',H209'',NULL)|| '||
' DECODE(MAX(DECODE(H210_NAME , H211_NAME , 0, 1)),0,'',H210'',NULL)|| '||
' DECODE(MAX(DECODE(H211_NAME , H212_NAME , 0, 1)),0,'',H211'',NULL)|| '||
' DECODE(MAX(DECODE(H212_NAME , H213_NAME , 0, 1)),0,'',H212'',NULL)|| '||
' DECODE(MAX(DECODE(H213_NAME , H214_NAME , 0, 1)),0,'',H213'',NULL)|| '||
' DECODE(MAX(DECODE(H214_NAME , H215_NAME , 0, 1)),0,'',H214'',NULL)|| '||
' DECODE(MAX(DECODE(H302_NAME , H303_NAME , 0, 1)),0,'',H302'',NULL)|| '||
' DECODE(MAX(DECODE(H303_NAME , H304_NAME , 0, 1)),0,'',H303'',NULL)|| '||
' DECODE(MAX(DECODE(H304_NAME , H305_NAME , 0, 1)),0,'',H304'',NULL)|| '||
' DECODE(MAX(DECODE(H305_NAME , H306_NAME , 0, 1)),0,'',H305'',NULL)|| '||
' DECODE(MAX(DECODE(H306_NAME , H307_NAME , 0, 1)),0,'',H306'',NULL)|| '||
' DECODE(MAX(DECODE(H307_NAME , H308_NAME , 0, 1)),0,'',H307'',NULL)|| '||
' DECODE(MAX(DECODE(H308_NAME , H309_NAME , 0, 1)),0,'',H308'',NULL)|| '||
' DECODE(MAX(DECODE(H309_NAME , H310_NAME , 0, 1)),0,'',H309'',NULL)|| '||
' DECODE(MAX(DECODE(H310_NAME , H311_NAME , 0, 1)),0,'',H310'',NULL)|| '||
' DECODE(MAX(DECODE(H311_NAME , H312_NAME , 0, 1)),0,'',H311'',NULL)|| '||
' DECODE(MAX(DECODE(H312_NAME , H313_NAME , 0, 1)),0,'',H312'',NULL)|| '||
' DECODE(MAX(DECODE(H313_NAME , H314_NAME , 0, 1)),0,'',H313'',NULL)|| '||
' DECODE(MAX(DECODE(H314_NAME , H315_NAME , 0, 1)),0,'',H314'',NULL)|| '||
' DECODE(MAX(DECODE(H402_NAME , H403_NAME , 0, 1)),0,'',H402'',NULL)|| '||
' DECODE(MAX(DECODE(H403_NAME , H404_NAME , 0, 1)),0,'',H403'',NULL)|| '||
' DECODE(MAX(DECODE(H404_NAME , H405_NAME , 0, 1)),0,'',H404'',NULL)|| '||
' DECODE(MAX(DECODE(H405_NAME , H406_NAME , 0, 1)),0,'',H405'',NULL)|| '||
' DECODE(MAX(DECODE(H406_NAME , H407_NAME , 0, 1)),0,'',H406'',NULL)|| '||
' DECODE(MAX(DECODE(H407_NAME , H408_NAME , 0, 1)),0,'',H407'',NULL)|| '||
' DECODE(MAX(DECODE(H408_NAME , H409_NAME , 0, 1)),0,'',H408'',NULL)|| '||
' DECODE(MAX(DECODE(H409_NAME , H410_NAME , 0, 1)),0,'',H409'',NULL)|| '||
' DECODE(MAX(DECODE(H410_NAME , H411_NAME , 0, 1)),0,'',H410'',NULL)|| '||
' DECODE(MAX(DECODE(H411_NAME , H412_NAME , 0, 1)),0,'',H411'',NULL)|| '||
' DECODE(MAX(DECODE(H412_NAME , H413_NAME , 0, 1)),0,'',H412'',NULL)|| '||
' DECODE(MAX(DECODE(H413_NAME , H414_NAME , 0, 1)),0,'',H413'',NULL)|| '||
' DECODE(MAX(DECODE(H414_NAME , H415_NAME , 0, 1)),0,'',H414'',NULL),'','') '||
'FROM '||pDimTableName;
l_stmt := 'SELECT COUNT(*), '||
'DECODE(LEAST(COUNT(DISTINCT DECODE( L1_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , L1_NAME)),1),1,''Used'',''Not Used'') L1_NAME, '||
'DECODE(LEAST(COUNT(DISTINCT DECODE(H115_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H115_NAME)),1),1,''Used'',''Not Used'') H115_NAME, '||
'DECODE(LEAST(COUNT(DISTINCT DECODE(H215_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H215_NAME)),1),1,''Used'',''Not Used'') H215_NAME, '||
'DECODE(LEAST(COUNT(DISTINCT DECODE(H315_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H315_NAME)),1),1,''Used'',''Not Used'') H315_NAME, '||
'DECODE(LEAST(COUNT(DISTINCT DECODE(H415_NAME , ''NA_EDW'' , NULL , ''NA_ERR'' , NULL , H415_NAME)),1),1,''Used'',''Not Used'') H415_NAME '||
'FROM '||dimTab(ctr).table_name;
Procedure eulHierDelete
******************************************************************************/
Procedure eulHierDelete
IS
-- Hierarchies
-- 445 Week
-- Enterprise Calendar
-- GL Period
-- Gregorian Calendar
-- Project Period
/* 'Project Intelligence Business Area' */
project_hier1 VARCHAR2(5) := 'P445%';
l_stmt := 'SELECT DISTINCT hi_id '||
'FROM '||g_EulOwner||'.EUL_OBJS folders , '||
' '||g_EulOwner||'.EUL_BA_OBJ_LINKS ba_folders , '||
' '||g_EulOwner||'.EUL_BUSINESS_AREAS business_areas , '||
' '||g_EulOwner||'.EUL_EXPRESSIONS folder_items , '||
' '||g_EulOwner||'.EUL_IG_EXP_LINKS item_to_hier , '||
' '||g_EulOwner||'.EUL_HI_NODES hier_nodes , '||
' '||g_EulOwner||'.EUL_HI_SEGMENTS hier_segments, '||
' '||g_EulOwner||'.EUL_HIERARCHIES hier '||
'WHERE ( '||
' /*PROJECTS */ '||
' (business_areas.ba_name = '''||G_BusArea_3||''' AND '||
' (folder_items.it_ext_column LIKE '''||project_hier1||''' OR '||
' folder_items.it_ext_column LIKE '''||project_hier2||''' OR '||
' folder_items.it_ext_column LIKE '''||project_hier3||''' OR '||
' folder_items.it_ext_column LIKE '''||project_hier4||''')) '||
' OR '||
' /* Payables */ '||
' (business_areas.ba_name = '''||G_BusArea_2||''' AND '||
' (folder_items.it_ext_column LIKE '''||payables_hier1||''' OR '||
' folder_items.it_ext_column LIKE '''||payables_hier2||''' OR '||
' folder_items.it_ext_column LIKE '''||payables_hier3||''' OR '||
' folder_items.it_ext_column LIKE '''||payables_hier4||''')) '||
' OR '||
' /* Revenue */ '||
' (business_areas.ba_name = '''||G_BusArea_1||''' AND '||
' (folder_items.it_ext_column LIKE '''||revenue_hier1||''' OR '||
' folder_items.it_ext_column LIKE '''||revenue_hier2||''' OR '||
' folder_items.it_ext_column LIKE '''||revenue_hier3||''' OR '||
' folder_items.it_ext_column LIKE '''||revenue_hier4||''')) '||
' ) '||
'AND ba_folders.bol_ba_id = business_areas.ba_id '||
'AND ba_folders.bol_obj_id = folders.obj_id '||
'AND folders.sobj_ext_table = ''EDW_TIME_M'' '||
'AND folder_items.ite_obj_id = folders.obj_id '||
'AND folder_items.exp_id = item_to_hier.hil_exp_id '||
'AND item_to_hier.hil_hn_id = hier_nodes.hn_iD '||
'AND hier_segments.ihs_hi_id = hier_nodes.hn_hi_id '||
'AND hier_segments.ihs_hn_id_child = hier_nodes.hn_id '||
'AND hier_nodes.hn_hi_id = hier.hi_id';
edw_log.debug_line('Procedure eulHierDelete');
EXECUTE IMMEDIATE 'DELETE '||
'FROM '||g_EulOwner||'.EUL_HI_SEGMENTS hier_segments '||
'WHERE hier_segments.ihs_hi_id = :1' USING l_HierId;
EXECUTE IMMEDIATE 'DELETE '||
'FROM '||g_EulOwner||'.EUL_IG_EXP_LINKS item_to_hier '||
'WHERE hil_hn_id IN '||
'(SELECT hn_id '||
' FROM '||g_EulOwner||'.EUL_HI_NODES '||
' WHERE hn_hi_id = :1)' USING l_HierId;
EXECUTE IMMEDIATE 'DELETE '||
'FROM '||g_EulOwner||'.EUL_HI_NODES '||
'WHERE hn_hi_id = :1' USING l_HierId;
EXECUTE IMMEDIATE 'DELETE '||
'FROM '||g_EulOwner||'.EUL_HIERARCHIES hier '||
'WHERE hier.hi_id = :1' USING l_HierId;
edw_log.put_line('Deleted Unused time hierarchies from FII Business Areas.');
END eulHierDelete;
sql_stmt_ba := 'SELECT DISTINCT ba_id
FROM '||pEulOwnerName||'.eul4_bas
WHERE ba_name IN ('''||G_BusArea_1||''',
'''||G_BusArea_2||''',
'''||G_BusArea_3||''')';
'SELECT d.dom_id, d.dom_it_id_rank, e.exp_id, e.it_ext_column,
e.it_obj_id
FROM '|| pEulOwnerName
|| '.eul4_domains d,'
|| pEulOwnerName
|| '.eul4_expressions e
WHERE e.exp_id = d.dom_it_id_lov
AND e.it_ext_column IN
(''CDAY_NAME'',
''CPER_NAME'',
''CPER_PERIOD_NAME'',
''PPER_NAME'',
''PPER_PA_PERIOD'',
''CQTR_CAL_QTR'',
''CQTR_NAME'',
''CYR_CAL_YEAR'',
''CYR_NAME'',
''DAY_DAY'',
''DAY_DAY_OF_WEEK'',
''DAY_NAME'',
''WEEK_NAME'',
''WEEK_WEEK'',
''MNTH_MONTH'',
''MNTH_NAME'',
''HMNT_HALF_MONTH'',
''HMNT_HALF_MONTH_NAME'',
''HMNT_NAME'',
''YEAR_NAME'',
''YEAR_YEAR'',
''P445_NAME'',
''P445_PERIOD_445'',
''ECYR_CAL_YEAR'',
''ECYR_NAME'',
''ECQR_CAL_QTR'',
''ECQR_NAME'',
''ECPR_CAL_PERIOD'',
''ECPR_NAME'',
''HYR_HALF_YEAR'',
''HYR_NAME'',
''QTR_NAME'',
''QTR_QTR'',
''CDAY_CAL_DAY'')
AND e.it_obj_id in (SELECT o.obj_id
FROM ' ||pEulOwnerName||'.eul4_objs o,'
||pEulOwnerName||'.eul4_bas B,'
||pEulOwnerName||'.EUL4_BA_OBJ_LINKS L
WHERE l.BOL_OBJ_ID=o.obj_id
AND l.BOL_BA_ID=b.ba_id
AND b.ba_id='||v_ba_id||'
AND (o.obj_developer_key LIKE ''TAB_%''
OR o.obj_developer_key LIKE ''EDW_TIME_M%''))
ORDER BY it_obj_id';
'SELECT exp_id
FROM '
|| pEulOwnerName
|| '.eul4_expressions
WHERE it_ext_column = :1 AND it_obj_id =:2';
'UPDATE '
|| pEulOwnerName
|| '.eul4_domains
SET dom_it_id_rank = :4
WHERE dom_it_id_lov = :5';
'UPDATE '
|| pEulOwnerName
|| '.eul4_expressions
SET it_dom_id = :6
WHERE exp_id = :7';
SELECT DECODE(TABLE_NAME,'EUL_BUSINESS_AREAS','3i','EUL4_BAS','4i') eul
INTO lEULVersion
FROM ALL_TABLES
WHERE TABLE_NAME IN ('EUL_BUSINESS_AREAS' , 'EUL4_BAS')
AND owner = pEulOwnerName;
eulHierDelete;
l_stmt := 'UPDATE '||g_EulOwner||'.eul_expressions '||
'SET it_heading = NVL(it_heading,exp_name) , '||
' ite_format_mask = NVL(ite_format_mask , ''999G999G999G999'') '||
'WHERE exp_id = :1';
/* Update Business Area Ctr for hidden items */
eulBusAreaTab(ba_ctr).ba_records_hidden := eulBusAreaTab(ba_ctr).ba_records_hidden + 1;