[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_status_stmt CONSTANT VARCHAR2(320) :=
'UPDATE EDW_LOCAL_GENERATION_STATUS
SET generate_status = :status,
error_message = :error,
last_update_date = Sysdate
WHERE flex_view_name = :viewname'
;
insert_status_stmt CONSTANT VARCHAR2(560) :=
'INSERT INTO EDW_LOCAL_GENERATION_STATUS
(FLEX_VIEW_NAME,
GENERATE_STATUS,
ERROR_MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
( :viewname,
:status,
:error,
sysdate,0,0,0,Sysdate
)'
;
'SELECT upper(table_name),
upper(table_alias),
upper(source_column_name)
FROM edw_view_gen_base_table_v@edw_apps_to_wh
WHERE
instance_code = :1
AND
flex_view_name = :2
ORDER BY table_name, table_alias'
;
selection_v_query_stmt CONSTANT VARCHAR2(2000) :=
'SELECT
structure _num,
structure_name ,
application_column_name,
segment_name,
segment_datatype,
id_flex_code,
flex_field_type,
flex_field_name,
application_name,
FROM edw_view_gen_flex_v@edw_apps_to_wh
WHERE object_short_name = :1
AND instance_code = :2 '
; --- For use in release 4.0
selection_query_stmt CONSTANT VARCHAR2(2000) :=
'SELECT
a.structure_num,
a.structure_name ,
a.application_column_name,
a.segment_name,
a.value_set_datatype,
a.id_flex_code,
decode(a.flex_field_type,''A'',''K'',a.flex_field_type),
a.flex_field_name,
c.application_short_name application_name
FROM edw_flex_seg_mappings@edw_apps_to_wh a,
edw_fact_flex_fk_maps@edw_apps_to_wh b,
fnd_application@edw_apps_to_wh c
WHERE b.fact_short_name = :1
AND b.enabled_flag =''Y''
AND b.dimension_short_name = a.dimension_short_name
AND a.instance_code = :2
AND c.application_id = a.application_id
union
select distinct
b.structure_num,
b.structure_name,
b.application_column_name,
b.segment_name,
b.value_set_datatype,
b.id_flex_code,
b.flex_field_type,
b.flex_field_name,
c.application_short_name application_name
from edw_attribute_mappings@edw_apps_to_wh a,
edw_flex_attribute_mappings@edw_apps_to_wh b,
fnd_application@edw_apps_to_wh c
where a.source_view = :3
and a.object_short_name= :1
and a.instance_code = :2
and a.ATTR_MAPPING_PK = b.attr_mapping_fk
AND c.application_id = b.application_id'
;
PROCEDURE update_flexfields -- PRIVATE PROCEDURE
( p_view_column_text_table IN bis_vg_types.View_Text_Table_Type
, p_view_select_text_table IN bis_vg_types.View_Text_Table_Type
, p_mode IN NUMBER := bis_vg_types.sqlplus_production_mode
, p_selected_columns IN BIS_VG_TYPES.flexfield_column_table_type := NULL
, 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_comment_table OUT bis_vg_types.Flex_Column_Comment_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
l_str VARCHAR2(300);
bis_debug_pub.Add('> update_flexfields');
l_str := bis_vg_util.get_row( p_view_select_text_table
, p_select_pointer
, x_return_status
, x_error_Tbl
);
, p_select_pointer.col_num
, ':'
, l_col
, x_return_status
, x_error_Tbl
);
, p_error_proc_name => G_PKG_NAME||'.update_flexfields'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
, p_View_Select_Text_Table
, p_Mode
, p_selected_columns
, p_Column_Pointer
, p_Select_Pointer
, p_select_Pointer -- not used
, x_Column_Table
, x_Select_Table
, x_Column_Comment_Table
, x_Column_Pointer
, x_Select_Pointer
, x_return_status
, x_error_Tbl
);
, p_View_Select_Text_Table
, p_Mode
, p_selected_columns
, p_Column_Pointer
, p_Select_Pointer
, p_select_pointer -- not used
, x_Column_Table
, x_Select_Table
, x_Column_Comment_Table
, x_Column_Pointer
, x_Select_Pointer
, x_return_status
, x_error_Tbl
);
, p_View_Select_Text_Table
, p_Mode
, p_Column_Pointer
, p_Select_Pointer
, x_Column_Table
, x_Select_Table
, x_Column_Pointer
, x_Select_Pointer
, x_return_status
, x_error_Tbl
);
bis_debug_pub.Add('< update_flexfields');
, p_error_proc_name => G_PKG_NAME||'. update_flexfields'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
END update_flexfields;
( p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type
, p_Select_Pointer IN bis_vg_types.view_character_pointer_type
, p_start_pointer IN 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
)
RETURN NUMBER
IS
--
l_select_pointer bis_vg_types.view_character_pointer_type;
l_select_pointer := p_Select_Pointer;
WHILE( bis_vg_util.get_char( p_View_Select_Text_Table
, l_select_pointer
, x_return_status
, x_error_Tbl
)
<> ','
) LOOP
l_select_pointer := bis_vg_util.decrement_pointer
( p_View_Select_Text_Table
, l_select_pointer
, x_return_status
, x_error_Tbl
);
( l_select_pointer
, p_start_pointer
, x_return_status
, x_error_Tbl
)
)
THEN
x_select_pointer := p_select_pointer;
( l_select_pointer
, p_start_pointer
, x_return_status
, x_error_Tbl
)
)
THEN
x_select_pointer := bis_vg_util.decrement_pointer
( p_View_Select_Text_Table
, l_select_pointer
, x_return_status
, x_error_Tbl
);
( p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type
, p_Select_Pointer IN bis_vg_types.view_character_pointer_type
, p_end_pointer IN 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
--
l_select_pointer bis_vg_types.view_character_pointer_type;
l_select_pointer := p_Select_Pointer;
WHILE( bis_vg_util.get_char( p_View_Select_Text_Table
, l_select_pointer
, x_return_status
, x_error_Tbl
)
<> ','
) LOOP
l_select_pointer := bis_vg_util.increment_pointer
( p_View_Select_Text_Table
, l_select_pointer
, x_return_status
, x_error_Tbl
);
( l_select_pointer
, p_end_pointer
, x_return_status
, x_error_Tbl
)
)
THEN
x_select_pointer := p_select_pointer;
( l_select_pointer
, p_end_pointer
, x_return_status
, x_error_Tbl
)
)
THEN
x_select_pointer := bis_vg_util.increment_pointer
( p_View_Select_Text_Table
, l_select_pointer
, x_return_status
, x_error_Tbl
);
PROCEDURE Get_Base_Table_Selects
(p_view_select_table IN bis_vg_types.View_Text_Table_Type
, p_start_pos IN bis_vg_types.View_Character_Pointer_Type
, p_unique_tables IN superset_summary_table_type
, p_extra_columns IN superset_table_type
, x_select_table OUT bis_vg_types.View_Text_Table_Type
, x_error_Tbl OUT BIS_VG_UTIL.error_tbl_type
)
IS
l_pos bis_vg_types.View_Character_Pointer_Type;
bis_debug_pub.Add('> Get_Base_Table_Selects');
( p_view_select_table(p_start_pos.row_num)
, p_start_pos.col_num
, ', '||g_newline||g_tab
, l_end
, l_dummy
, x_error_Tbl
);
( p_view_select_table
, p_start_pos
, l_dummy
, x_error_Tbl
);
( p_view_select_table(l_pos.row_num)
,l_pos.col_num
, ', '||g_newline||g_tab
, l_end
, l_dummy
, x_error_Tbl
)
);
( p_view_select_table
, l_pos
, l_dummy
, x_error_Tbl
);
( p_view_select_table(l_pos.row_num)
,l_pos.col_num
, ', '||g_newline||g_tab
, l_end
, l_dummy
, x_error_Tbl
)
)
)
THEN
l_unique_tabs(l_table_count).currently_valid := TRUE;
( p_view_select_table
, l_pos
, l_dummy
, x_error_Tbl
);
END LOOP; --- over the select statement string table
x_select_table(column_count) := ', NULL';
x_select_table(column_count) :=
' , '
|| p_extra_columns(column_count).table_alias
|| '.'
|| p_extra_columns(column_count).column_name;
bis_debug_pub.Add('< Get_Base_Table_Selects');
, p_error_proc_name => G_PKG_NAME||'.Get_Base_Table_Selects'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
RAISE FND_API.G_EXC_UNEXPECTED_ERROR; END get_base_table_selects;
PROCEDURE Update_View --- PRIVATE PROCEDURE
( p_View_Column_Text_Table IN bis_vg_types.View_Text_Table_Type
, p_View_Select_Text_Table IN bis_vg_types.view_text_table_type
, p_mode IN NUMBER := bis_vg_types.sqlplus_production_mode
, p_selected_columns IN BIS_VG_TYPES.flexfield_column_table_type := NULL
, p_extra_columns IN superset_table_type := NULL
, x_View_Column_Text_Table OUT bis_vg_types.View_Text_Table_Type
, x_View_Select_Text_Table OUT bis_vg_types.View_Text_Table_Type
, x_View_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
, x_error_Tbl OUT BIS_VG_UTIL.error_tbl_type
)
IS
l_col_tab_curr_pos bis_vg_types.view_character_pointer_type;
l_sel_tab_SELECT_pos bis_vg_types.view_character_pointer_type;
l_select_string_table bis_vg_types.view_text_table_type;
l_select_table bis_vg_types.view_text_table_type;
l_temp_select_table bis_vg_types.view_text_table_type;
l_select_count NUMBER;
bis_debug_pub.Add('> Update_View');
l_select_string_table(1) := 'SELECT';
l_sel_tab_SELECT_pos := l_sel_tab_curr_pos;
( p_view_select_text_table
, l_from_string_table
, l_sel_tab_curr_pos
, l_dummy
, x_error_Tbl
);
( p_view_select_text_table
, l_flex_string_table
, l_sel_tab_past_pos
, l_dummy
, x_error_Tbl
);
update_flexfields( p_View_Column_Text_Table
, p_view_select_text_table
, p_mode
, p_selected_columns
, l_col_tab_curr_pos
, l_sel_tab_curr_pos
, l_Column_Table
, l_Select_Table
, l_Column_Comment_Table
, l_col_tab_curr_pos
, l_sel_tab_curr_pos
, l_dummy
, x_error_Tbl
);
-- position the pointer before the last single quote in select
l_sel_tab_pretag_pos := bis_vg_util.position_before_characters
( p_view_select_text_table
, '''' ----||' '||' '
, l_sel_tab_pretag_pos
, l_dummy
, x_error_Tbl
);
IF (l_select_table.COUNT = 0) THEN
-- no flex definition or remove_tags mode,
-- remove the comma prior to the tag just processed in the
-- select table. (Note: no commas in column table yet)
l_hcr := exclude_comma_before_tag( p_view_select_text_table
, l_sel_tab_pretag_pos
, l_sel_tab_SELECT_pos
, l_sel_tab_pretag_pos
, l_dummy
, x_error_Tbl
);
-- No valid columns prior to current tag in select statement.
-- Current (unexpanded) tag is first tag, so we must remove the
-- trailing ',' in order for the next column to be first in
-- the generated select statement
exclude_comma_after_tag( p_view_select_text_table
, l_sel_tab_curr_pos
, l_sel_tab_FROM_pos
, l_sel_tab_curr_pos
, l_dummy
, x_error_Tbl
);
( p_view_select_text_table
, l_sel_tab_pretag_pos
, l_dummy
, x_error_Tbl
);
bis_vg_util.copy_part_of_table( p_view_select_text_table
, l_sel_tab_past_pos
, l_sel_tab_pretag_pos
, l_temp_select_table
, l_dummy
, x_error_Tbl
);
bis_vg_util.concatenate_tables( x_view_select_text_table
, l_temp_select_table
, x_view_select_text_table
, l_dummy
, x_error_Tbl
);
--- second - the select statement
bis_vg_util.concatenate_tables( x_view_select_text_table
, l_select_table
, x_view_select_text_table
, l_dummy
, x_error_Tbl
);
get_base_table_selects(p_view_select_text_table
, l_sel_tab_from_pos
, l_unique_tables
, p_extra_columns
, l_select_table
, x_error_Tbl
);
bis_vg_util.concatenate_tables( x_view_select_text_table
, l_select_table
, x_view_select_text_table
, l_dummy
, x_error_Tbl
);
( p_view_select_text_table
, l_security_string_table
, l_sel_tab_FROM_pos
, l_dummy
, x_error_Tbl
);
-- copy part of select table from FROM pointer to security pointer
bis_debug_pub.Add('security pointer is not null');
( p_view_select_text_table
, ' ,'
, l_sel_tab_sec_tag_pos
, l_dummy
, x_error_Tbl
);
bis_vg_util.copy_part_of_table( p_view_select_text_table
, l_sel_tab_past_pos
, l_sel_tab_pretag_pos
, l_select_table
, l_dummy
, x_error_Tbl
);
bis_vg_util.concatenate_tables( x_view_select_text_table
, l_select_table
, x_view_select_text_table
, l_dummy
, x_error_Tbl
);
bis_vg_security.add_security_Info( p_View_Select_Text_Table
, l_sel_tab_sec_tag_pos
, l_select_table
, l_sel_tab_past_pos
, l_dummy
, x_error_Tbl
);
bis_vg_util.concatenate_tables( x_view_select_text_table
, l_select_table
, x_view_select_text_table
, l_dummy
, x_error_Tbl
);
( p_view_select_text_table
, l_union_string_table
, l_sel_tab_past_pos
, l_dummy
, x_error_Tbl
);
bis_debug_pub.ADD('more select info');
( p_view_select_text_table
, l_select_string_table
, l_sel_tab_curr_pos
, l_dummy
, x_error_Tbl
);
l_sel_tab_SELECT_pos := l_sel_tab_curr_pos;
bis_vg_util.copy_part_of_table( p_view_select_text_table
, l_sel_tab_past_pos
, l_sel_tab_curr_pos
, l_select_table
, l_dummy
, x_error_Tbl
);
bis_vg_util.concatenate_tables( x_view_select_text_table
, l_select_table
, x_view_select_text_table
, l_dummy
, x_error_Tbl
);
bis_debug_pub.Add('< Update_View');
, p_error_proc_name => G_PKG_NAME||'.Update_View'
, p_error_table => x_error_tbl
, x_error_table => x_error_tbl
);
END Update_View;
bis_vg_log.update_failure_log( p_ViewName
, l_error_code
, SUBSTR(l_message||' '||l_trace, 1, 2000)
, x_return_status
, x_error_Tbl
);
l_View_Select_Text_Table bis_vg_types.View_Text_Table_Type;
l_View_Select_Out_Table bis_vg_types.View_Text_Table_Type;
SELECT db_link INTO l_instance from user_db_links where
db_link like 'EDW_APPS_TO_WH%';
execute immediate 'SELECT instance_code FROM edw_local_instance'
INTO l_instance;
FOR selection_query_stmt
using p_objectname, l_instance, p_viewname, p_objectname, l_instance;
, l_view_select_text_table
, l_dummy_tbl
);
Update_View( p_view_column_text_table => l_view_column_text_table
, p_view_select_text_table => l_View_Select_Text_Table
, p_selected_columns => l_subset_table
, p_extra_columns => l_superset_table
, x_view_column_text_table => l_view_column_out_table
, x_view_select_text_table => l_view_select_out_table
, x_view_column_comment_table => l_view_column_comment_table
, x_error_tbl => l_dummy_tbl
);
, l_View_Select_out_Table
, l_View_Column_Comment_Table
, l_View_Column_Comment_Table
, l_dummy_tbl ----- not used
);
, l_View_Select_out_Table
, l_View_Column_Comment_Table
, l_View_Column_Comment_Table
, l_dummy_tbl ----- not used
);
execute immediate update_status_stmt
using
'GENERATED_PRUNED',
'',
p_viewname;
execute immediate update_status_stmt
using
'FAILED_PRUNED',
l_error_string,
p_viewname;
execute immediate update_status_stmt
using
'FAILED_PRUNED',
l_error_string,
p_viewname;
execute immediate update_status_stmt
using
'FAILED_PRUNED',
l_error_string,
p_viewname;
l_View_Select_Text_Table bis_vg_types.View_Text_Table_Type;
l_View_Select_Out_Table bis_vg_types.View_Text_Table_Type;
SELECT db_link INTO l_instance from user_db_links where
db_link like 'EDW_APPS_TO_WH%';
SELECT a.table_name INTO l_instance FROM all_tables a, user_synonyms u
WHERE a.table_name = 'EDW_LOCAL_GENERATION_STATUS'
AND u.table_name= 'EDW_LOCAL_GENERATION_STATUS'
AND a.owner = u.table_owner;
SELECT substrb(version, 1, instrb(version,'.') -1) into BIS_VIEW_GENERATOR_PVT.g_db_version
FROM product_component_version
WHERE upper(product) like 'ORACLE';
, l_View_Select_Text_Table
, l_error_Tbl
);
Update_View( p_view_column_text_table => l_view_column_text_table
, p_view_select_text_table => l_View_Select_Text_Table
, p_mode => g_mode
, x_view_column_text_table => l_view_column_out_table
, x_view_select_text_table => l_view_select_out_table
, x_view_column_comment_table => l_view_column_comment_table
, x_error_tbl => l_error_Tbl
);
, l_View_Select_out_Table
, l_View_Column_Comment_Table
, l_View_Column_Comment_Table
--- , l_return_status
, l_error_Tbl
);
-- Update the view column comments
comment_Flex_Columns(l_generated_view_name
, l_view_column_comment_table);
--- Update generate status for warehouse views
IF ( l_warehouse_exists = 1
AND
(l_generated_view_name like '%LCV'
OR l_generated_view_name like '%FCV'
)
)
THEN
BEGIN
execute immediate update_status_stmt
using
'GENERATED_ALL',
'',
l_View_Table(i).view_name
;
execute immediate insert_status_stmt
using
l_View_Table(i).view_name,
'GENERATED_ALL',
''
;
bis_vg_log.update_success_log( l_View_Table(i).view_name
, l_generated_view_name
, l_return_status
, l_error_Tbl
);
execute immediate update_status_stmt
using
'FAILED_ALL',
l_error_message,
l_View_Table(i).view_name
;
execute immediate insert_status_stmt
using
l_View_Table(i).view_name,
'FAILED_ALL',
l_error_message
;
bis_vg_log.update_success_log( l_View_Table(i).view_name
, l_generated_view_name
, l_return_status
, l_error_Tbl
);
bis_vg_log.update_failure_log( l_View_Table(i).view_name
, SQLCODE
, SQLERRM
, l_return_status
, l_error_Tbl
);
bis_vg_log.update_failure_log( l_error_tbl
, l_return_status
, l_error_Tbl
);