[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select PROPERTY_VALUE
Into l_system_type
From BSC_SYS_INIT
Where PROPERTY_CODE = 'MODEL_TYPE';
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type;
' SELECT CODE,'||
' USER_CODE,'||
' NAME'||
' FROM BSC_D_TYPE_OF_ACCOUNT '||
' WHERE LANGUAGE = userenv(''LANG''))';
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type;
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type;
' SELECT CODE,'||
' USER_CODE,'||
' NAME,'||
' Typ_of_Acc_Code,'||
' Typ_of_Acc_Code_Usr,'||
' Position'||
' FROM BSC_D_ACCOUNT '||
' WHERE LANGUAGE = userenv(''LANG''))';
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type ;
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type ;
' SELECT CODE,'||
' USER_CODE,'||
' NAME,'||
' Account_Code ,'||
' Account_Code_Usr'||
' FROM BSC_D_SUBACCOUNT '||
' WHERE LANGUAGE = userenv(''LANG''))';
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type;
Select count(*)
Into l_project_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type;
' SELECT CODE,'||
' USER_CODE,'||
' NAME'||
' FROM BSC_D_PROJECT '||
' WHERE LANGUAGE = userenv(''LANG''))';
Select count(*)
Into l_count
From USER_OBJECTS
Where OBJECT_NAME = l_object_type;
l_sql_stmt := 'Insert Into BSC_D_PROJECT (CODE, USER_CODE, NAME,LANGUAGE,SOURCE_LANG) Values ( '||
' SELECT '||to_char(l_code) ||' AS CODE,' ||
'to_char('||to_char(l_code)||') AS USER_CODE, ' ||
' ' || l_code_name|| ' AS NAME, ' ||
' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG '||
' FROM DUAL , ' ||
' FND_LANGUAGES LNG ';
l_sql_stmt := 'INSERT INTO BSC_D_TYPE_OF_ACCOUNT ' ||
' (CODE,LANGUAGE,SOURCE_LANG,USER_CODE,NAME) ' ||
' SELECT '||l_acct_type(i_acct_type).code||' AS CODE,' ||
' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG,'||
'to_char('||l_acct_type(i_acct_type).code||') AS USER_CODE, ' ||
' SUBSTRB(FEM_ALIAS.MEANING,1,20) AS NAME ' ||
' FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS, ' ||
' FND_LANGUAGES LNG ';
l_sql_stmt := 'INSERT INTO BSC_D_ACCOUNT ' ||
'(CODE,LANGUAGE,SOURCE_LANG,USER_CODE,NAME,TYP_OF_ACC_CODE,TYP_OF_ACC_CODE_USR,POSITION) ' ||
' SELECT '||l_account(i_acct_ind).code||' AS CODE, '||
' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG,'||
'to_char('||l_account(i_acct_ind).code||') AS USER_CODE, '||
'SUBSTRB(FEM_ALIAS.MEANING,1,20) AS NAME,'||
l_account(i_acct_ind).acct_type||' AS TYP_OF_ACC_CODE, '||
l_account(i_acct_ind).acct_type||' AS TYP_OF_ACC_CODE_USR, '||
l_account(i_acct_ind).position ||' AS POSITION '||
'FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS,'||
' FND_LANGUAGES LNG ';
l_sql_stmt := 'INSERT INTO BSC_D_SUBACCOUNT ' ||
' (CODE,LANGUAGE,SOURCE_LANG,USER_CODE,NAME,ACCOUNT_CODE,ACCOUNT_CODE_USR) ' ||
' SELECT '||l_account(i_acct_ind).code||' AS CODE, ' ||
' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG,'||
' to_char('||l_account(i_acct_ind).code||') AS USER_CODE,' ||
' SUBSTRB(FEM_ALIAS.MEANING,1,20) AS NAME, '||
l_account(i_acct_ind).code||' AS ACCOUNT_CODE, '||
l_account(i_acct_ind).code||' AS ACCOUNT_CODE_USR'||
' FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS, ' ||
' FND_LANGUAGES LNG ';
G_Pro_Tbl.Delete;
G_DF_Tbl.Delete;
G_DC_Tbl.Delete;
l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVELS_B, Dim_level_id=' ||
to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
Insert Into BSC_SYS_DIM_LEVELS_B (
DIM_LEVEL_ID,
LEVEL_TABLE_NAME,
TABLE_TYPE,
LEVEL_PK_COL,
ABBREVIATION,
VALUE_ORDER_BY,
COMP_ORDER_BY,
CUSTOM_GROUP,
USER_KEY_SIZE,
DISP_KEY_SIZE,
EDW_FLAG,
LEVEL_VIEW_NAME,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE
)
Values (
G_Pro_Tbl(i_dimension).Dim_level_id,
G_Pro_Tbl(i_dimension).Table_name,
G_Pro_Tbl(i_dimension).Table_Type,
G_Pro_Tbl(i_dimension).Level_pk_col,
G_Pro_Tbl(i_dimension).abbreviation,
G_Pro_Tbl(i_dimension).Value_Order,
G_Pro_Tbl(i_dimension).Comp_Order,
G_Pro_Tbl(i_dimension).Custom_Group,
5, 20, 0,
G_Pro_Tbl(i_dimension).Level_View_Name,
1,SYSDATE,1,SYSDATE
);
l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVELS_TL, Dim_level_id=' ||
to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
l_sql := 'INSERT INTO BSC_SYS_DIM_LEVELS_TL '||
' (DIM_LEVEL_ID,LANGUAGE,SOURCE_LANG,NAME,HELP,TOTAL_DISP_NAME,COMP_DISP_NAME) '||
'SELECT '||
G_Pro_Tbl(i_dimension).Dim_level_id||' AS DIM_LEVEL_ID, '||
'FEM_DIM.LANGUAGE AS LANGUAGE, '||
'FEM_DIM.SOURCE_LANG AS SOURCE_LANG, '||
'SUBSTR(FEM_DIM.MEANING,1,30) AS NAME, '||
'SUBSTR(FEM_DIM.MEANING,1,80) AS HELP, '||
'SUBSTR(FEM_ALL.MEANING,1,15) AS TOTAL_DISP_NAME, '||
'SUBSTR(FEM_COMP.MEANING,1,15) AS COMP_DISP_NAME '||
'FROM '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DIM, '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALL, '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_COMP '||
'WHERE '||
'FEM_DIM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND '||
'FEM_DIM.LOOKUP_CODE = '''||G_Pro_Tbl(i_dimension).Dim_level_id||''' AND '||
'FEM_ALL.LOOKUP_TYPE =''BSC_UI_COMMON'' AND '||
'FEM_ALL.LOOKUP_CODE = ''ALL'' AND '||
'FEM_COMP.LOOKUP_TYPE =''BSC_UI_COMMON'' AND '||
'FEM_COMP.LOOKUP_CODE = ''COMPARISON'' AND '||
'FEM_DIM.LANGUAGE = FEM_ALL.LANGUAGE AND '||
'FEM_COMP.LANGUAGE = FEM_ALL.LANGUAGE';
l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVEL_COLS, Dim_level_id=' ||
to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
Insert Into BSC_SYS_DIM_LEVEL_COLS (
DIM_LEVEL_ID,
COLUMN_NAME,
COLUMN_TYPE)
Values (
G_Pro_Tbl(i_dimension).Dim_level_id,
G_DC_Tbl(i_dimension_col).Column_Name,
G_DC_Tbl(i_dimension_col).Column_Type
);
Insert Into BSC_SYS_DIM_LEVEL_COLS (
DIM_LEVEL_ID,
COLUMN_NAME,
COLUMN_TYPE)
Values (
G_Pro_Tbl(i_dimension).Dim_level_id,
G_DC_Tbl(i_dimension_col).Column_Name,
G_DC_Tbl(i_dimension_col).Column_Type
);
l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVEL_RELS, Dim_level_id=' ||
to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
Insert Into BSC_SYS_DIM_LEVEL_RELS (
DIM_LEVEL_ID,
RELATION_COL,
PARENT_DIM_LEVEL_ID,
RELATION_TYPE,
DIRECT_RELATION )
Values (
G_Pro_Tbl(i_dimension).Dim_level_id,
G_Pro_Tbl(i_dimension).fk_field, -- relation_field
G_Pro_Tbl(i_dimension).parent_level, -- parent_entity_code
G_Pro_Tbl(i_dimension).rel_type, -- relation_type
G_Pro_Tbl(i_dimension).direct_rel -- direct_relation
);
l_debug_stmt := 'Insert Into BSC_DB_TABLES_RELS, Dim_level_id=' ||
to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
Insert Into BSC_DB_TABLES_RELS (
TABLE_NAME,
SOURCE_TABLE_NAME)
Values (
G_Pro_Tbl(i_dimension).Table_name,
'BSC_DI_' || G_Pro_Tbl(i_dimension).Dim_level_id
);
l_debug_stmt := 'Insert Into BSC_DB_TABLES, Dim_level_id=' ||
to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
Insert Into BSC_DB_TABLES (
TABLE_NAME,
TABLE_TYPE,
PERIODICITY_ID,
SOURCE_DATA_TYPE,
SOURCE_FILE_NAME)
Values (
'BSC_DI_' || G_Pro_Tbl(i_dimension).Dim_level_id,
2,
0,
0,
NULL
);
l_debug_stmt := 'Insert Into MPROJ_DRILLS_FAMILIES,Dim_group_id=' ||
to_char(G_DF_Tbl(i_dem_family).Dim_group_id);
l_sql := 'INSERT INTO BSC_SYS_DIM_GROUPS_TL '||
' (DIM_GROUP_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) ' ||
'SELECT '||
G_DF_Tbl(i_dem_family).Dim_group_id||' AS DIM_GROUP_ID, '||
'FEM_ALIAS.LANGUAGE AS LANGUAGE, '||
'FEM_ALIAS.SOURCE_LANG AS SOURCE_LANG, '||
'SUBSTR(FEM_ALIAS.MEANING,1,15) AS NAME '||
',1,SYSDATE,1,SYSDATE '||
'FROM '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS '||
'WHERE '||
'FEM_ALIAS.LOOKUP_TYPE = ''BSC_TPLATE_TAB_DIM_GROUP_NAME'' AND '||
'FEM_ALIAS.LOOKUP_CODE = '''||G_DF_Tbl(i_dem_family).Dim_group_id||'''';
l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVELS_BY_GROUP, ' ||
' Dim_group_id=' || to_char(G_DF_Tbl(i_dem_family).Dim_group_id) ||
', Dim_level_id=' || to_char(G_DF_Tbl(i_dem_family).Dim_level_id);
Insert Into BSC_SYS_DIM_LEVELS_BY_GROUP (
DIM_GROUP_ID,
DIM_LEVEL_ID,
DIM_LEVEL_INDEX,
TOTAL_FLAG,
COMPARISON_FLAG,
FILTER_COLUMN,
FILTER_VALUE,
DEFAULT_VALUE,
DEFAULT_TYPE,
PARENT_IN_TOTAL,
NO_ITEMS )
Values (
G_DF_Tbl(i_dem_family).Dim_group_id, -- family_code
G_DF_Tbl(i_dem_family).Dim_level_id, -- entity_code
G_DF_Tbl(i_dem_family).Dim_level_idx, -- order_r
G_DF_Tbl(i_dem_family).Total, -- total
G_DF_Tbl(i_dem_family).Comparison, -- comparison
G_DF_Tbl(i_dem_family).Filter_col, -- condition_field
G_DF_Tbl(i_dem_family).Filter_val, -- condition_value
G_DF_Tbl(i_dem_family).Default_val, -- init
G_DF_Tbl(i_dem_family).Default_type, -- init_type
G_DF_Tbl(i_dem_family).Parent_Total, -- status_whn_parnt_is_total
G_DF_Tbl(i_dem_family).No_items -- hide_if_no_items
);