The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_stmt VARCHAR2(32000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT'
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| l_resp_clause
|| ' ORDER BY VALUE';
l_select_stmt := 'SELECT DISTINCT'
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' ORDER BY VALUE';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT '
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' RESPONSIBILITY_ID = :p_Responsibility_ID '
|| ' ORDER BY VALUE';
l_select_stmt := 'SELECT DISTINCT'
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' ORDER BY VALUE';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT'
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' ORDER BY VALUE';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT '
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' ORDER BY VALUE';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000) := NULL;
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT '
|| ' ID '
|| ', VALUE '
|| ', START_DATE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' ORGANIZATION_ID = :org_id'
|| ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :org_type '
|| ' ORDER BY START_DATE';
l_select_stmt := 'SELECT '
|| ' ID '
|| ', VALUE '
|| ', START_DATE '
|| ' FROM '
|| l_view_name
|| ' ORDER BY START_DATE';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT '
|| ' START_DATE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' ID = :p_start_period ';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT '
|| ' END_DATE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' ID = :p_end_period ';
, statement => l_select_stmt
, language_flag => DBMS_SQL.NATIVE
);
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT '
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' WHERE ID = :1';
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_ID;
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT '
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' WHERE VALUE = :1 ';
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT '
|| ' PERIOD_SET_NAME '
|| ', PERIOD_NAME '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' ORGANIZATION_ID = :1 '
|| ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :2 '
|| ' AND '
|| ' PERIOD_SET_NAME || ''+'' || PERIOD_NAME = :3 ';
EXECUTE IMMEDIATE l_select_stmt INTO l_period_set, l_value
USING p_Org_Level_Value_Rec.Dimension_Level_Value_ID
, p_Org_Level_Value_Rec.Dimension_Level_Short_Name
, p_Dim_Level_Value_Rec.Dimension_Level_Value_ID;
l_select_stmt := 'SELECT '
|| ' PERIOD_SET_NAME '
|| ', PERIOD_NAME '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' PERIOD_SET_NAME || ''+'' || PERIOD_NAME = :1';
EXECUTE IMMEDIATE l_select_stmt INTO l_period_set, l_value
USING p_Dim_Level_Value_Rec.Dimension_Level_Value_ID;
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT '
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' ORGANIZATION_ID = :1 '
|| ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :2 '
|| ' AND '
|| ' VALUE = :3 ';
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value
USING p_Org_Level_Value_Rec.Dimension_Level_Value_ID
, p_Org_Level_Value_Rec.Dimension_Level_Short_Name
, p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
l_select_stmt := 'SELECT '
|| ' ID '
|| ', VALUE '
|| ' FROM '
|| l_view_name
|| ' WHERE '
|| ' VALUE = :1 '
|| ' AND rownum < 2 '; -- take the first row
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value
USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
l_select_stmt VARCHAR2(2000);
,x_select_String => l_select_stmt
,x_table_name => l_view_name
,x_value_name => l_value_name
,x_id_name => l_id_name
,x_level_name => l_name
,x_description => l_description
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_select_stmt := l_select_stmt || ' WHERE ' ||l_id_name ||' = :1';
l_select_stmt := l_select_stmt || ' AND ROWNUM < 2 ';
l_select_stmt := l_select_stmt || ' AND SET_OF_BOOKS_ID = :2';
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Id, p_set_of_books_id;
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value, l_start_date, l_end_date USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Id;
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Id;
l_select_stmt VARCHAR2(2000);
SELECT
DIMENSION_LEVEL_SHORT_NAME
, DIMENSION_LEVEL_NAME
, LEVEL_VALUES_VIEW_NAME
INTO
l_short_name
, l_name
, l_view_name
FROM BISBV_DIMENSION_LEVELS
WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
l_select_stmt := 'SELECT DISTINCT ID, VALUE FROM '|| l_view_name
|| ' WHERE Value = :1';
EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
l_select_stmt VARCHAR2(2000);
,x_select_String => l_select_stmt
,x_table_name => l_view_name
,x_value_name => l_value_name
,x_id_name => l_id_name
,x_level_name => l_name
,x_description => l_description
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_select_stmt VARCHAR2(2000);
,x_select_String => l_select_stmt
,x_table_name => l_view_name
,x_value_name => l_value_name
,x_id_name => l_id_name
,x_level_name => l_name
,x_description => l_description
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);