[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM | MxN relationship when the child is updated for |
REM | Bug #3395161 |
REM | 19-MAR-2004 PAJOHRI Bug #3518647, Added a validation for message |
REM | text "BSC_MAX_DIM_OBJ_RELS" and replaced |
REM | VARCHAR2(8000) size to VARCHAR2(32000) |
REM | 29-MAR-2004 PAJOHRI Bug #3530886, Modified tablespaces for tables |
REM | VARCHAR2(8000) size to VARCHAR2(32000) |
REM | 23-APR-2004 ASHANKAR Bug #3518610,Added the fucntion Validate |
REM | listbutton |
REM | 15-OCT-2004 ASHANKAR Bug#3459282 Filter button Validation. |
REM | 16-FEB-2005 ashankar Bug#4184438 Added the Synch Up API |
REM | BSC_SYNC_MVLOGS.Sync_dim_table_mv_log |
REM | 02-May-2005 visuri Modified for Bug#4323383 |
REM | 18-Jul-2005 ppandey Enh #4417483, Restrict Internal/Calendar Dims |
REM | 12-SEP-2005 adrao Modified API Assign_Dim_Obj_Rels for Bug4601099 |
REM | 29-SEP-2005 adrao Modified API Assign_Dim_Obj_Rels for Bug4619393 |
REM | 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
REM | 27-DEC-2005 kyadamak Calling BIA API for bug#4875047 |
REM | 13-jan-2005 ashankar Bug#4947293 calling the API sync_dimension_table|
REM | dynamically |
REM | 31-JAN-2005 adrao Made a call to Refresh_BSC_PMF_Dim_View() API |
REM | for Bug#4758995 |
REM | 01-MAR-2006 adrao is_KPI_Flag_For_Dim_Obj_Rels Modified for |
REM | Bug#5057436 |
REM | 19-JUN-2006 adrao Bug#5300060 - refresh all the child dimension |
REM | objects as well |
REM | 26-JUN-2006 akoduri Bug#5335325 - Prototype flag not getting changed|
REM | when a BIS dimension object is added to AG Rep |
REM | 17-JUL-2006 ppandey Bug#5389895 - Create/Update Relationship issue |
REM | for non-numeric user code |
REM +=======================================================================+
*/
CONFIG_LIMIT_RELS CONSTANT NUMBER := 5;
SELECT Dim_Level_Id
, Parent_Dim_Level_Id
, Relation_Type
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE (Parent_Dim_Level_Id = p_dim_obj_id
AND Relation_Type <> 2 )
OR Dim_Level_Id = p_dim_obj_id
ORDER BY Dim_Level_Id;
SELECT Dim_Level_Id
, Parent_Dim_Level_Id
, Relation_Type
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE Parent_Dim_Level_Id = p_dim_obj_id
OR Dim_Level_Id = p_dim_obj_id
ORDER BY Dim_Level_Id;
SELECT COUNT(0)
INTO l_count
FROM BSC_SYS_FILTERS_VIEWS
WHERE DIM_LEVEL_ID = p_dim_level_id;
SELECT DISTINCT source_code
FROM BSC_SYS_FILTERS_VIEWS
WHERE source_type = 1
AND dim_level_id = p_par_dim_level_id;
SELECT DISTINCT source_code
FROM BSC_SYS_FILTERS_VIEWS
WHERE source_type = 1
AND dim_level_id = p_dim_level_id;
l_Sql := ' SELECT DISTINCT A.TAB_ID '||
' FROM ' ||
' BSC_TABS_VL A '||
' , BSC_TAB_INDICATORS B '||
' , BSC_KPI_DIM_LEVELS_VL C '||
' , BSC_SYS_DIM_LEVELS_VL D '||
' WHERE A.TAB_ID =B.TAB_ID '||
' AND B.INDICATOR =C.INDICATOR '||
' AND C.LEVEL_TABLE_NAME = D.LEVEL_TABLE_NAME '||
' AND D.DIM_LEVEL_ID IN (:1,:2) ' ;
l_Sql := C_SELECT || C_SELECT_CLAUSE
|| C_FROM || p_level_view_name
|| C_WHERE || C_WHERE_CLAUSE ;
SELECT count(1)
INTO l_count
FROM ALL_VIEWS
WHERE VIEW_NAME = p_level_view_name
AND OWNER = BSC_APPS.get_user_schema('APPS')
AND TEXT IS NOT NULL;
SELECT source_code,level_view_name,dim_level_id
FROM BSC_SYS_FILTERS_VIEWS
WHERE SOURCE_TYPE=1
AND DIM_LEVEL_ID =l_dim_level_id;
SELECT parent_dim_level_id
, relation_type
, relation_col
, data_source_type
, data_source
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = p_dim_obj_id;
SELECT dim_level_id
, relation_type
, relation_col
, data_source_type
, data_source
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE parent_dim_level_id = p_dim_obj_id
AND relation_type = 1;
will be inserted into the following table.
1. BSC_SYS_DIM_LEVEL_RELS
The procedure will remove all the older relationships before assigning new
relationships.
Validations:
1. Source must be same either BSC or PMF.
2. Circularity check must be there.
3. p_dim_obj_id must not be null.
*/
PROCEDURE Assign_New_Dim_Obj_Rels
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_dim_obj_id IN NUMBER
, p_parent_ids IN VARCHAR2
, p_parent_rel_type IN VARCHAR2
, p_parent_rel_column IN VARCHAR2
, p_parent_data_type IN VARCHAR2
, p_parent_data_source IN VARCHAR2
, p_child_ids IN VARCHAR2
, p_child_rel_type IN VARCHAR2
, p_child_rel_column IN VARCHAR2
, p_child_data_type IN VARCHAR2
, p_child_data_source IN VARCHAR2
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, p_is_not_config IN BOOLEAN := TRUE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_One_N_Table BSC_BIS_DIM_REL_PUB.One_To_N_Org_Table_Type;
l_is_denorm_deleted VARCHAR(1);
SELECT parent_dim_level_id
, relation_type
, dim_level_id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = l_dim_obj_id;
SELECT dim_level_id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE parent_dim_level_id = p_dim_obj_id
AND relation_type <> 2;
SELECT DISTINCT A.INDICATOR Indicator,
A.DIM_SET_ID Dim_Set_Id,
C.short_name
FROM BSC_KPI_DIM_LEVELS_VL A,
BSC_SYS_DIM_LEVELS_VL B,
BSC_KPIS_B C
WHERE A.LEVEL_TABLE_NAME=B.LEVEL_TABLE_NAME
AND C.INDICATOR = A.INDICATOR
AND C.SHARE_FLAG <> 2
AND INSTR(l_Refresh_Kpi_Ids, ','||b.dim_level_id||',') > 0;
SELECT DISTINCT Dim_Level_Id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE (Parent_Dim_Level_Id = p_dim_obj_id
OR Dim_Level_Id = p_dim_obj_id);
SELECT Relation_Type
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id
AND parent_dim_level_id = l_bsc_dim_obj_rec.Bsc_Parent_Level_Id;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_dim_obj_id;
SELECT NAME, SHORT_NAME
INTO l_dim_obj_name, l_dim_obj_sname
FROM BSC_SYS_DIM_LEVELS_VL
WHERE DIM_LEVEL_ID = l_rel_id;
, p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
, p_Entity_Name => l_dim_obj_name
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
SELECT NAME, SHORT_NAME
INTO l_dim_obj_name, l_dim_obj_sname
FROM BSC_SYS_DIM_LEVELS_VL
WHERE DIM_LEVEL_ID = l_rel_id;
, p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
, p_Entity_Name => l_dim_obj_name
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
SELECT NVL(source, 'BSC')
, short_name
INTO l_bsc_dim_obj_rec.bsc_parent_level_source
, l_bsc_dim_obj_rec.bsc_parent_level_short_name
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_dim_obj_id;
BSC_BIS_LOCKS_PUB.LOCK_UPDATE_RELATIONSHIPS
( p_dim_object_id => p_dim_obj_id
, p_selected_parends => lg_Dim_Obj_Tab_p
, p_selected_childs => lg_Dim_Obj_Tab_c
, p_time_stamp => p_time_stamp
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
DELETE FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = p_dim_obj_id
OR parent_dim_level_id = p_dim_obj_id;
SELECT NVL(source, 'BSC')
, short_name
INTO l_bsc_dim_obj_rec.Bsc_Source,
l_bsc_dim_obj_rec.bsc_level_short_name
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id
AND parent_dim_level_id = l_bsc_dim_obj_rec.bsc_parent_level_id;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = l_bsc_dim_obj_rec.bsc_parent_level_id
AND parent_dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id;
SELECT NVL(source, 'BSC')
, short_name
INTO l_bsc_dim_obj_rec.Bsc_Source,
l_bsc_dim_obj_rec.bsc_level_short_name
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_dim_obj_id;
SELECT NVL(source, 'BSC')
, short_name
INTO l_bsc_dim_obj_rec.bsc_parent_level_source
, l_bsc_dim_obj_rec.bsc_parent_level_short_name
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Parent_Level_Id;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = l_bsc_dim_obj_rec.Bsc_Parent_Level_Id
AND parent_dim_level_id = l_bsc_dim_obj_rec.Bsc_Level_Id;
, p_delete => TRUE -- delete before creating in cascading
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(A.Parent_Dim_Level_Id) INTO l_Count
FROM BSC_SYS_DIM_LEVEL_RELS A
WHERE A.Dim_Level_Id = l_One_N_Table(i).p_dim_obj_id
AND A.Relation_Type = 1;
l_is_denorm_deleted := FND_API.G_TRUE;
SELECT short_name
INTO l_dim_short_name
FROM bsc_sys_dim_levels_b
WHERE dim_level_id = p_dim_obj_id;
EXECUTE IMMEDIATE l_Sql USING IN l_dim_short_name,IN BIS_UTIL.G_ALTER_TABLE,OUT x_msg_data,OUT l_is_denorm_deleted;
IF(l_is_denorm_deleted=FND_API.G_FALSE) THEN
RAISE FND_API.G_EXC_ERROR;
SELECT short_name
INTO l_dim_short_name
FROM bsc_sys_dim_levels_b
WHERE dim_level_id = p_dim_obj_id;
SELECT short_name
INTO l_dim_short_name
FROM bsc_sys_dim_levels_b
WHERE dim_level_id = l_rel_id;
SELECT parent_dim_level_id
, relation_type
, relation_col
, data_source_type
, data_source
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = p_dim_obj_id;
SELECT dim_level_id
, relation_type
, relation_col
, data_source_type
, data_source
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE parent_dim_level_id = p_dim_obj_id
AND relation_type = 1;
SELECT l.name name
FROM BSC_SYS_DIM_LEVEL_RELS r
, BSC_SYS_DIM_LEVELS_VL l
WHERE r.dim_level_id = p_dim_obj_id
AND r.parent_dim_level_id = l.dim_level_id;
SELECT l.name name
FROM BSC_SYS_DIM_LEVEL_RELS r
, BSC_SYS_DIM_LEVELS_VL l
WHERE r.parent_dim_level_id = p_dim_obj_id
AND r.dim_level_id = l.dim_level_id;
SELECT r.DIM_LEVEL_ID
FROM BSC_SYS_DIM_LEVEL_RELS r
WHERE r.parent_dim_level_id = p_dim_obj_id;
SELECT A.Parent_Dim_Level_Id
, B.Level_Pk_Col
, B.Level_Table_Name
FROM BSC_SYS_DIM_LEVEL_RELS A
, BSC_SYS_DIM_LEVELS_B B
WHERE A.Dim_Level_Id = p_dim_obj_id
AND B.Dim_Level_Id = A.Parent_Dim_Level_Id
AND A.Relation_Type = 1;
SELECT COUNT(0) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE Dim_Level_Id = p_dim_obj_id;
SELECT level_table_name
INTO l_bsc_dim_obj_rec.Bsc_Level_View_Name
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = p_dim_obj_id;
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT '||l_col_names||' ';
l_sql_stmt1 := 'SELECT code,user_code FROM '||l_label_table_name||' WHERE code = (SELECT MAX(a.code) FROM '||l_label_table_name ||' a) AND ROWNUM <2';
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT * FROM '||l_dummy_table;
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT '||l_input_col_names||' FROM '||l_master_table||' WHERE 1 = 2';
' SELECT '||l_view_columns||
' FROM '||l_master_table||
' WHERE LANGUAGE = USERENV(''LANG''))';
SELECT COUNT(0) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE Dim_Level_Id = p_dim_obj_id;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = p_dim_obj_id
AND parent_dim_level_id = p_parent_id
AND relation_type = 2;
SELECT dim_level_id
, abbreviation
, level_pk_col
, level_table_name
INTO l_c_dim_level_id
, l_c_abbr
, l_c_level_pk_col
, l_c_level_table
FROM BSC_SYS_DIM_LEVELS_B WHERE dim_level_id = p_dim_obj_id;
SELECT dim_level_id
, abbreviation
, level_pk_col
, level_table_name
INTO l_p_dim_level_id
, l_p_abbr
, l_p_level_pk_col
, l_p_level_table
FROM BSC_SYS_DIM_LEVELS_B WHERE dim_level_id = p_parent_id;
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT '||' A.CODE AS '||l_c_level_pk_col||
', B.CODE AS '||l_p_level_pk_col||
' FROM '||l_c_level_table||' A, '||
l_p_level_table||' B '||
' WHERE A.LANGUAGE = B.LANGUAGE '||
' AND A.LANGUAGE = '''||USERENV('LANG')||'''';
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
' AS SELECT '||' A.USER_CODE AS '||l_c_level_pk_col||'_USR, B.USER_CODE AS '||
l_p_level_pk_col||'_USR '||
' FROM '||l_c_level_table||' A, '||
' '||l_p_level_table||' B '||
' WHERE 1 = 2 ';
SELECT COUNT(*) INTO l_count
FROM BSC_DB_TABLES_RELS
WHERE Source_Table_Name = l_input_table;
INSERT INTO BSC_DB_TABLES_RELS
(Table_Name, Source_Table_Name, Relation_Type)
VALUES (l_master_table, l_input_table, 0);
UPDATE BSC_DB_TABLES_RELS
SET Table_Name = l_master_table
WHERE Source_Table_Name = l_input_table;
SELECT COUNT(*) INTO l_count
FROM BSC_DB_TABLES
WHERE Table_Name = l_input_table;
INSERT INTO BSC_DB_TABLES
(Table_Name, Table_Type, Periodicity_Id,
Source_Data_Type, Source_File_Name)
VALUES (l_input_table, 2, 0, 0, NULL);
SELECT abbreviation INTO l_p_abbre
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_dim_obj_id;
SELECT abbreviation INTO l_c_abbre
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_parent_id;
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql_stmt := ' SELECT COUNT(*) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
DELETE FROM BSC_DB_TABLES
WHERE Table_Name = l_input_table;
DELETE FROM BSC_DB_TABLES_RELS
WHERE Source_Table_Name = l_input_table;
SELECT Source
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_dim_obj_id;
SELECT Parent_Dim_Level_Id
, Relation_Type
, Dim_Level_Id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = p_dim_obj_id;
SELECT Dim_Level_Id
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE parent_dim_level_id = p_dim_obj_id;
SELECT DISTINCT C.Name||'['||C.Indicator||']' Name, C.short_name
FROM BSC_KPI_DIM_LEVELS_B A
, BSC_SYS_DIM_LEVELS_B D
, BSC_KPIS_VL C
WHERE A.Level_Table_Name = D.Level_Table_Name
AND D.Dim_Level_Id = p_dim_obj_id
AND C.share_flag <> 2
AND C.Indicator = A.Indicator;
SELECT DISTINCT C.Name||'['||C.Indicator||']' Name, C.short_name
FROM BSC_KPI_DIM_LEVELS_VL A,
BSC_SYS_DIM_LEVELS_VL B,
BSC_KPIS_VL C
WHERE A.LEVEL_TABLE_NAME=B.LEVEL_TABLE_NAME
AND C.INDICATOR = A.INDICATOR
AND C.SHARE_FLAG <> 2
AND INSTR(', '||l_final_chd_ids||',', ', '||b.dim_level_id||',') > 0;
SELECT INDICATOR,DIM_SET_ID
FROM BSC_KPI_DIM_LEVEL_PROPERTIES
WHERE DIM_LEVEL_ID = p_dim_obj_id;
dim_objs_in_dimset.delete;
SELECT COUNT(INDICATOR) INTO l_no_rels
FROM BSC_KPI_DIM_LEVELS_B
WHERE INDICATOR = l_kpi_id
AND DIM_SET_ID = l_dimset_id
AND PARENT_LEVEL_INDEX >= 0;
SELECT short_name
FROM bsc_sys_dim_levels_vl
WHERE INSTR(','||p_child_ids ||',',','||dim_level_id||',') > 0;
SELECT SHORT_NAME
INTO l_short_name
FROM BSC_SYS_DIM_LEVELS_VL
WHERE DIM_LEVEL_ID = p_dim_obj_id;