APPS.BIM_I_SGMT_ACT_FACTS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 110

			bis_collection_utilities.log('Please run the Update Segment Activity Base Summary - Initial Load Concurrent Program before running this');

Line: 280

	select list_query_id, query
	from ams_list_queries_all ;

Line: 361

			Execute Immediate 'INSERT INTO source_query_safb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';

Line: 398

	BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SGMT_ACT');

Line: 401

	bis_collection_utilities.log('Inserting Segment Activity Details');

Line: 403

	Execute Immediate 'INSERT /*+ append parallel*/ ' ||
'	INTO bim_i_sgmt_act_facts ' ||
'	(creation_date		 ' ||
'	 ,last_update_date	 ' ||
'	 ,created_by		 ' ||
'	 ,last_updated_by	 ' ||
'	 ,last_update_login	 ' ||
'	 ,segment_id		 ' ||
'	 ,segment_association_date ' ||
'	 ,schedule_id		 ' ||
'	 ,schedule_source_code	 ' ||
'	 ,source_code_id ' ||
'	) ' ||
'	SELECT ' ||
'		SYSDATE ' ||
'		,SYSDATE ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,c.cell_id segment_id ' ||
'		,a.creation_date  ' ||
'		,b.schedule_id  ' ||
'		,b.source_code  ' ||
'		,d.source_code_id ' ||
'	FROM  ams_act_lists a  ' ||
'		, ams_campaign_schedules_b b  ' ||
'		, (SELECT a.cell_id    ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_safb 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.creation_date >= '''||p_start_date||''''||
'			 AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
'			 UNION ALL         ' ||
'			 SELECT a.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.creation_date >= '''||p_start_date||''''||
'			 AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
'			 ) c 	 ' ||
'		, bim_i_source_codes d ' ||
'	WHERE a.list_header_id= c.cell_id ' ||
'	AND   a.list_act_type = ''CELL'' ' ||
'	AND   a.list_used_by=''CSCH'' ' ||
'	AND   a.list_used_by_id = b.schedule_id ' ||
'	AND	  b.schedule_id = d.child_object_id ' ||
'	AND   d.child_object_type = ''CSCH'' ' ||
'	AND   b.status_code IN (''COMPLETED'', ''CANCELLED'', ''CLOSED'', ''ACTIVE'', ''ON_HOLD'') ' ||
'	AND   trunc(a.creation_date) BETWEEN  '''|| p_start_date ||''' AND '''||p_end_date||'''' ;

Line: 561

	select list_query_id, query
	from ams_list_queries_all ;

Line: 643

			Execute Immediate 'INSERT INTO source_query_safb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';

Line: 673

	bis_collection_utilities.log('Inserting Segment Activity Details');

Line: 675

	Execute Immediate 'INSERT /*+ append parallel*/ ' ||
'	INTO bim_i_sgmt_act_facts ' ||
'	(creation_date		 ' ||
'	 ,last_update_date	 ' ||
'	 ,created_by		 ' ||
'	 ,last_updated_by	 ' ||
'	 ,last_update_login	 ' ||
'	 ,segment_id		 ' ||
'	 ,segment_association_date ' ||
'	 ,schedule_id		 ' ||
'	 ,schedule_source_code	 ' ||
'	 ,source_code_id ' ||
'	) ' ||
'	SELECT ' ||
'		SYSDATE ' ||
'		,SYSDATE ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,c.cell_id segment_id ' ||
'		,a.creation_date  ' ||
'		,b.schedule_id  ' ||
'		,b.source_code  ' ||
'		,d.source_code_id ' ||
'	FROM  ams_act_lists a  ' ||
'		, ams_campaign_schedules_b b  ' ||
'		, (SELECT a.cell_id    ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_safb 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.creation_date >= '''||g_initial_start_date||''''||
'			 AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
'			 UNION ALL         ' ||
'			 SELECT a.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.creation_date >= '''||g_initial_start_date||''''||
'			 AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
'			 ) c 	 ' ||
'		, bim_i_source_codes d ' ||
'	WHERE a.list_header_id= c.cell_id ' ||
'	AND   a.list_act_type = ''CELL'' ' ||
'	AND   a.list_used_by=''CSCH'' ' ||
'	AND   a.list_used_by_id = b.schedule_id ' ||
'	AND	  b.schedule_id = d.child_object_id ' ||
'	AND   d.child_object_type = ''CSCH'' ' ||
'	AND   b.status_code IN (''COMPLETED'', ''CANCELLED'', ''CLOSED'', ''ACTIVE'', ''ON_HOLD'')	 ' ||
'	AND   trunc(d.obj_last_update_date) BETWEEN  '''||p_start_date||''' AND '''||p_end_date||''''||
'	AND   NOT EXISTS (  SELECT 1  ' ||
'						FROM bim_i_sgmt_act_facts  fct ' ||
'						WHERE fct.segment_id = a.list_header_id ' ||
'						AND fct.source_code_id = d.source_code_id)';