The following lines contain the word 'select', 'insert', 'update' or 'delete':
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
x_column_table.DELETE;
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
FUNCTION : get_select_statement
PARAMETERS:
1. p_lookup_table lookup table name
2. p_lookup_type lookup type;
COMMENT : Call this function to get the select statement for lookup table
information to a view.
EXCEPTION : None
===========================================================================*/
FUNCTION get_select_statement
( p_lookup_table IN VARCHAR2
, p_lookup_type IN VARCHAR2
, p_lookup_column IN VARCHAR2
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
RETURN VARCHAR2
IS
l_select VARCHAR2(1000);
BIS_DEBUG_PUB.Add('> get_select_statement');
l_select := 'select lookup_code, '||p_lookup_column;
l_select := l_select || ' from '||p_lookup_table;
l_select := l_select || ' where lookup_type = '''||p_lookup_type||'''';
BIS_DEBUG_PUB.Add(' l_select = '||l_select);
BIS_DEBUG_PUB.Add('< get_select_statement');
RETURN l_select;
, p_error_proc_name => G_PKG_NAME||'.get_select_statement'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
END get_select_statement;
2. p_select select statement for the lookup table
3. x_Select_Table table of varchars to hold additional select
(select table)
4. x_return_status error or normal
5. x_error_Tbl table of error messages
--
COMMENT : Call this procedure to add a particular decode lookup
information to a view.
EXCEPTION : None
===========================================================================*/
PROCEDURE write_decode_statement
( p_expr IN VARCHAR2
, p_select IN VARCHAR2
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_cursor_id INTEGER;
l_select_table BIS_VG_TYPES.View_Text_Table_Type;
dbms_sql.parse(l_cursor_id, p_select, dbms_sql.V7);
x_select_table(x_select_table.COUNT + 1) := ', ';
x_select_table(x_select_table.COUNT + 1) := 'DECODE';
, l_select_table
, x_return_status
, x_error_Tbl
);
bis_vg_util.concatenate_Tables( x_select_table
, l_select_table
, x_select_table
, x_return_status
, x_error_Tbl
);
x_select_table(x_select_table.COUNT + 1) := ', '|| ''''
|| l_lookup_code
|| '''';
x_select_table(x_select_table.COUNT + 1) := ', '|| ''''
|| l_meaning
|| '''';
x_select_table(x_select_table.COUNT + 1) := 'NULL';
x_select_table(x_select_table.COUNT + 1) := ', NULL )';
x_select_table(x_select_table.COUNT + 1) := ')';
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
5. x_Select_Table table of varchars to hold additional select
(select table)
6. x_return_status error or normal
7. x_error_Tbl table of error messages
--
COMMENT : Call this procedure to add a particular decode lookup
information to a view.
EXCEPTION : None
===========================================================================*/
PROCEDURE put_decode_statement
( p_expr IN VARCHAR2
, p_lookup_table IN VARCHAR2
, p_lookup_type IN VARCHAR2
, p_lookup_column IN VARCHAR2
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_select VARCHAR2(1000);
l_select := get_select_statement( p_lookup_table
, p_lookup_type
, p_lookup_column
, x_return_status
, x_error_Tbl
);
BIS_DEBUG_PUB.Add(' l_select = '||l_select);
, l_select
, x_select_table
, x_return_status
, x_error_Tbl
);
, p_value2 => l_select
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
6. x_Select_Table table of varchars to hold additional select
7. x_return_status error or normal
8. x_error_Tbl table of error messages
(select table)
--
COMMENT : Call this procedure to add a particular decode lookup
information to a view for the given language.
EXCEPTION : None
===========================================================================*/
PROCEDURE put_decode_statement_lang
( p_expr IN VARCHAR2
, p_lookup_table IN VARCHAR2
, p_lookup_type IN VARCHAR2
, p_lookup_column IN VARCHAR2
, p_language IN VARCHAR2
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_select VARCHAR2(1000);
l_select := get_select_statement( p_lookup_table
, p_lookup_type
, p_lookup_column
, x_return_status
, x_error_Tbl
);
l_select := l_select || ' and language = '''|| p_language ||'''';
BIS_DEBUG_PUB.Add(' l_select = '||l_select);
, l_select
, x_select_table
, x_return_status
, x_error_Tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
5. x_Select_Table table of varchars to hold additional select
(select table)
6. x_return_status error or normal
7. x_error_Tbl table of error messages
--
COMMENT : Call this procedure to add a particular decode lookup
information to a view.
EXCEPTION : None
===========================================================================*/
PROCEDURE put_decode_statement_languages
( p_expr IN VARCHAR2
, p_lookup_table IN VARCHAR2
, p_lookup_type IN VARCHAR2
, p_lookup_column IN VARCHAR2
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_select VARCHAR2(1000);
l_select := 'select language from :table_name' ||
'where lookup_type = :type';
dbms_sql.parse(l_cursor_id, l_select, dbms_sql.NATIVE);
x_select_table(x_select_table.COUNT + 1) := 'DECODE';
x_select_table(x_select_table.COUNT + 1) := '( USERENV(''LANG'')';
x_select_table(x_select_table.COUNT + 1) := ', '||l_language;
, x_Select_Table
, x_return_status
, x_error_Tbl
);
x_select_table(x_select_table.COUNT + 1) := ', NULL )';
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
SELECT 1
FROM user_tab_columns
WHERE table_name=p_lookup_table
AND column_name='LANGUAGE';
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
PROCEDURE : add_select_info
PARAMETERS:
1. p_expr pl/sql expression for decode
2. p_lookup_table lookup table name
3. p_lookup_type lookup type;
5. x_Select_Table table of varchars to hold additional select
(select table)
6. x_return_status error or normal
7. x_error_Tbl table of error messages
--
COMMENT : Call this procedure to add a particular lookup select
information to a view.
EXCEPTION : None
===========================================================================*/
PROCEDURE add_select_info
( p_expr IN VARCHAR2
, p_lookup_table IN VARCHAR2
, p_lookup_type IN VARCHAR2
, p_lookup_column IN VARCHAR2
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_lang BOOLEAN := FALSE;
BIS_DEBUG_PUB.Add('> add_select_info');
, x_select_table
, x_return_status
, x_error_Tbl
);
, x_select_table
, x_return_status
, x_error_Tbl
);
BIS_DEBUG_PUB.Add('< add_select_info');
, p_error_proc_name => G_PKG_NAME||'.add_select_info'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
END add_select_info;
PROCEDURE parse_LA_select
( p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
, p_Select_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
, x_expr OUT VARCHAR2
, x_lookup_table OUT VARCHAR2
, x_lookup_type OUT VARCHAR2
, x_lookup_column OUT VARCHAR2
, x_Select_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_str VARCHAR2(2000);
BIS_DEBUG_PUB.Add('> parse_LA_select');
l_str := bis_vg_parser.Skip_Tag( p_View_Select_Table
, p_select_pointer
, x_select_pointer
, x_return_status
, x_error_Tbl
);
l_str := bis_vg_parser.get_expression( p_View_Select_Table
, p_Select_Pointer
, l_tmp_pointer
, x_return_status
, x_error_Tbl
);
, x_select_pointer
, x_return_status
, x_error_Tbl
)
) THEN
BIS_VG_UTIL.Add_Error_message
( p_error_msg_name => bis_vg_lookup.LAT_COL_TAG_EXP_NO_EXP_MSG
, p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_token1 => 'tag'
, p_value1 => l_str
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
( p_View_Select_Table
, l_tmp_pointer
, x_return_status
, x_error_Tbl
);
x_expr := bis_vg_parser.get_token_increment_pointer( p_View_Select_Table
, x_select_pointer
, ':'
, x_select_pointer
, x_return_status
, x_error_Tbl
);
, x_select_pointer
, x_return_status
, x_error_Tbl
)
) THEN
BIS_VG_UTIL.Add_Error_message
( p_error_msg_name => bis_vg_lookup.LAT_COL_TAG_EXP_NO_EXP_MSG
, p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_token1 => 'tag'
, p_value1 => l_str
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
( p_View_Select_Table
, x_select_pointer
, ':'''
, x_select_pointer
, x_return_status
, x_error_Tbl
);
, x_select_pointer
, x_return_status
, x_error_Tbl
)
) THEN
BIS_VG_UTIL.Add_Error_message
( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_TABLE_MSG
, p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_token1 => 'tag'
, p_value1 => l_str
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
( p_View_Select_Table
, x_select_pointer
, ':'''
, x_select_pointer
, x_return_status
, x_error_Tbl
);
, x_select_pointer
, x_return_status
, x_error_Tbl
)
) THEN
BIS_VG_UTIL.Add_Error_message
( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_TYPE_MSG
, p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_token1 => 'tag'
, p_value1 => l_str
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
( p_View_Select_Table
, x_select_pointer
, ''''
, x_select_pointer
, x_return_status
, x_error_Tbl
);
, p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_token1 => 'tag'
, p_value1 => l_str
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
BIS_DEBUG_PUB.Add('Parse_LA_Select Tag = '|| l_str);
BIS_DEBUG_PUB.Add('< parse_LA_select');
, p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
END parse_LA_select;
select object_type
from user_objects
where object_name = UPPER(p_table);
select 1
from user_synonyms syn, all_tab_columns col
where syn.synonym_name = UPPER(p_table)
and syn.table_name = col.table_name
and col.column_name = UPPER(p_column)
and syn.table_owner = col.owner
and col.owner = user
UNION
select 1
from user_tab_columns col1
where col1.table_name =UPPER(p_table)
and col1.column_name = UPPER(p_column);
select 1
from all_tab_columns a, user_synonyms u
where u.synonym_name = UPPER(p_table)
and u.table_name = a.table_name
and a.owner = u.table_owner
and a.column_name = UPPER(p_column)
UNION
select 1
from user_tab_columns col1
where col1.table_name =UPPER(p_table)
and col1.column_name = UPPER(p_column);
PROCEDURE put_decode_in_select
( p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
, p_Select_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
, p_Mode IN NUMBER
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_Select_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_expr VARCHAR2(2000);
BIS_DEBUG_PUB.Add('> put_decode_in_select');
parse_LA_select ( p_View_Select_Table
, p_Select_Pointer
, l_expr
, l_lookup_table
, l_lookup_type
, l_lookup_column
, x_Select_Pointer
, x_return_status
, x_error_Tbl
);
l_tag := bis_vg_util.get_string ( p_View_Select_Table
, p_Select_Pointer
, x_Select_Pointer
, x_return_status
, x_error_Tbl
);
x_select_table(1) := 'TO_CHAR(NULL)';
add_select_info( l_expr
, l_lookup_table
, l_lookup_type
, l_lookup_column
, x_select_table
, x_return_status
, x_error_Tbl
);
, p_error_proc_name => G_PKG_NAME||'.put_decode_in_select'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_token1 => 'tab'
, p_value1 => l_lookup_table
, p_token2 => 'col'
, p_value2 => l_lookup_column
, p_token3 => 'tag'
, p_value3 => l_tag
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
BIS_DEBUG_PUB.Add('> put_decode_in_select');
, p_error_proc_name => G_PKG_NAME||'.put_decode_in_select'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);
END put_decode_in_select;
2. p_View_Select_Table table of varchars to hold SELECT clause of view
3. p_Mode mode of execution of the program
4. p_Column_Pointer pointer to the lookup column in column table
5. p_Select_Pointer pointer to the select clause
6. x_Column_Table table of varchars to hold additional columns
7. x_Select_Table table of varchars to hold additional columns
8. x_Column_Pointer pointer to the character after the delimiter
(column table)
9. x_Select_Pointer pointer to the character after the delimiter
(select table)
10. x_return_status error or normal
11. x_error_Tbl table of error messages
--
COMMENT : Call this procedure to add a particular lookup information
to a view.
EXCEPTION : None
===========================================================================*/
PROCEDURE add_Lookup_Info
( p_View_Column_Table IN BIS_VG_TYPES.View_Text_Table_Type
, p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
, p_Mode IN NUMBER
, p_Column_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
, p_Select_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
, x_Column_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
, x_Column_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_Select_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_return_status OUT VARCHAR2
, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
--
BEGIN
BIS_DEBUG_PUB.Add('> add_Lookup_Info :-)');
put_decode_in_select( p_view_select_table
, p_select_pointer
, p_Mode
, x_select_table
, x_select_pointer
, x_return_status
, x_error_Tbl
);
bis_vg_log.update_failure_log( x_error_tbl
, x_return_status
, x_error_Tbl
);