[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select list_query_id, query
from ams_list_queries_all ;
Execute Immediate 'INSERT INTO source_query_sgdb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
/* This Delete Statement is for the records changed after last run. */
IF p_load_type = 'F' THEN
--it is a call for First/Inital load, then truncate the denorm table first
bis_collection_utilities.log('Truncating the Denorm Table ');
BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SEGMENT_DENORM_PKG');
DELETE bim_i_sgmt_denorm
WHERE segment_id IN
(SELECT cell_id
FROM ams_cells_all_b a
WHERE last_update_date > l_temp_start_date
);
/* This Delete Statement for the objects that had an update somewhere in their hierarachy chain.*/
DELETE bim_i_sgmt_denorm
WHERE segment_id IN (SELECT segment_id
FROM bim_i_sgmt_denorm
WHERE parent_segment_id in (SELECT b.segment_id
FROM ams_cells_all_b a
,bim_i_sgmt_denorm b
WHERE b.segment_id = a.cell_id
AND a.last_update_date > l_temp_start_date
)
);
UPDATE bim_i_sgmt_denorm
SET leaf_node_flag = 'N'
WHERE segment_id IN (SELECT parent_cell_id
FROM ams_cells_all_b a
WHERE NOT EXISTS (SELECT 1 FROM bim_i_sgmt_denorm b WHERE b.segment_id = a.cell_id)
AND object_level = 1
AND a.last_update_date > l_temp_start_date
);
Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||
' ( segment_id ' ||
' ,parent_segment_id ' ||
' ,immediate_parent_flag ' ||
' ,immediate_parent_id ' ||
' ,object_level ' ||
' ,top_node_flag ' ||
' ,leaf_node_flag ' ||
' ,prior_id ' ||
' ,creation_date ' ||
' ,last_update_date ' ||
' ,created_by ' ||
' ,last_updated_by ' ||
' ,last_update_login ' ||
' ) ' ||
' SELECT ' ||
' x.segment_id ' ||
' ,x.parent_segment_id ' ||
' ,x.immediate_parent_flag ' ||
' ,x.immediate_parent_id ' ||
' ,x.object_level ' ||
' ,decode(s.parent_cell_id, NULL, ''Y'', ''N'') top_node_flag ' ||
' ,x.leaf_node_flag ' ||
' ,s.parent_cell_id prior_id ' ||
' ,sysdate ' ||
' ,sysdate ' ||
' ,-1 ' ||
' ,-1 ' ||
' ,-1 ' ||
' FROM ' ||
' ( SELECT ' ||
' cell_id segment_id ' ||
' ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),2, INSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),''/'',2) -2),cell_id)) AS parent_segment_id ' ||
' ,decode(parent_cell_id,NULL,''Y'',decode(level,2,''Y'',''N'')) immediate_parent_flag ' ||
' ,parent_cell_id immediate_parent_id ' ||
' ,LEVEL object_level ' ||
' ,decode(parent_cell_id, NULL,''Y'',''N'') top_node_flag ' ||
' ,DECODE((SELECT COUNT(1) FROM ams_cells_all_b c WHERE parent_cell_id = a.cell_id),0,''Y'',''N'') leaf_node_flag ' ||
' FROM (SELECT a.cell_id , a.parent_cell_id ' ||
' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgdb d ' ||
' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
' AND b.arc_act_list_query_used_by =''CELL'' ' ||
' AND b.list_query_id = d.sql_id ' ||
' AND d.source_name = c.source_type_code ' ||
' AND c.based_on_tca_flag = ''Y'' ' ||
' AND a.sel_type =''SQL'' ' ||
' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
' UNION ALL ' ||
' SELECT a.cell_id , a.parent_cell_id ' ||
' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
' AND d.source_type_code = b.source_object_name ' ||
' AND b.based_on_tca_flag = ''Y'' ' ||
' AND a.sel_type=''DIWB'' ' ||
' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
' ) a ' ||
' WHERE NOT EXISTS ( SELECT 1 FROM bim_i_sgmt_denorm b ' ||
' WHERE b.segment_id = a.cell_id ) ' ||
' CONNECT BY PRIOR cell_id = parent_cell_id ' ||
' )x, ams_cells_all_b s ' ||
' WHERE s.cell_id = x.parent_segment_id' ;
bis_collection_utilities.log('Records Inserted for Incremental load ');
Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||
' ( segment_id ' ||
' ,parent_segment_id ' ||
' ,immediate_parent_flag ' ||
' ,immediate_parent_id ' ||
' ,object_level ' ||
' ,top_node_flag ' ||
' ,leaf_node_flag ' ||
' ,prior_id ' ||
' ,creation_date ' ||
' ,last_update_date ' ||
' ,created_by ' ||
' ,last_updated_by ' ||
' ,last_update_login ' ||
' ) ' ||
' SELECT ' ||
' x.segment_id ' ||
' ,x.parent_segment_id ' ||
' ,x.immediate_parent_flag ' ||
' ,x.immediate_parent_id ' ||
' ,x.object_level ' ||
' ,decode(s.parent_cell_id, NULL, ''Y'', ''N'') top_node_flag ' ||
' ,x.leaf_node_flag ' ||
' ,s.parent_cell_id prior_id ' ||
' ,sysdate ' ||
' ,sysdate ' ||
' ,-1 ' ||
' ,-1 ' ||
',-1 ' ||
' FROM ' ||
' (SELECT ' ||
' cell_id segment_id ' ||
' ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),2, INSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),''/'',2) -2),cell_id)) AS parent_segment_id ' ||
' ,decode(parent_cell_id,NULL,''Y'',decode(level,2,''Y'',''N'')) immediate_parent_flag ' ||
' ,parent_cell_id immediate_parent_id ' ||
' ,LEVEL object_level ' ||
' ,decode(parent_cell_id, NULL,''Y'',''N'') top_node_flag ' ||
' ,DECODE((SELECT COUNT(1) FROM ams_cells_all_b c WHERE parent_cell_id = a.cell_id),0,''Y'',''N'') leaf_node_flag ' ||
' FROM (SELECT a.cell_id , a.parent_cell_id ' ||
' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgdb d ' ||
' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
' AND b.arc_act_list_query_used_by =''CELL'' ' ||
' AND b.list_query_id = d.sql_id ' ||
' AND d.source_name = c.source_type_code ' ||
' AND c.based_on_tca_flag = ''Y'' ' ||
' AND a.sel_type =''SQL'' ' ||
' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
' UNION ALL ' ||
' SELECT a.cell_id , a.parent_cell_id ' ||
' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
' AND d.source_type_code = b.source_object_name ' ||
' AND b.based_on_tca_flag = ''Y'' ' ||
' AND a.sel_type=''DIWB'' ' ||
' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
' ) a ' ||
' CONNECT BY PRIOR a.cell_id = a.parent_cell_id ' ||
' )x, ams_cells_all_b s ' ||
' WHERE s.cell_id = x.parent_segment_id' ;
SELECT
-1 segment_id
,-1 parent_segment_id
,'Y' immediate_parent_flag
,null immediate_parent_id
,1 object_level
,'Y' top_node_flag
,'Y' leaf_node_flag
,null prior_id
,sysdate creation_date
,sysdate last_update_date
,-1 created_by
,-1 last_updated_by
,-1 last_update_login
FROM dual ;*/
bis_collection_utilities.log('Records Inserted for Initial load ');