eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIM_I_SGMT_FACTS_PKG SQL Statements

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

Line: 109


			bis_collection_utilities.log('Please run the Update Segment First Time Base Summary Concurrent Program before running this');

Line: 155


			SELECT	ent_year_start_date
			INTO	l_year_start_date
			FROM	fii_Time_Day
			WHERE	report_date = trunc(l_end_date);

Line: 291


	select list_query_id, query
	from ams_list_queries_all ;

Line: 372


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

Line: 407


	BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SGMT_SIZE');

Line: 409


	bis_collection_utilities.log('Inserting Segment Size');

Line: 411


	Execute Immediate ' INSERT /*+ append parallel */ INTO bim_i_sgmt_facts ' ||
'		(creation_date ' ||
'		,last_update_date ' ||
'		,created_by ' ||
'		,last_updated_by ' ||
'		,last_update_login ' ||
'		,transaction_create_date ' ||
'		,segment_id ' ||
'		,metric_type ' ||
'		,segment_size ' ||
'		,segment_status ' ||
'		,cust_count_week ' ||
'		,cust_count_month ' ||
'		,cust_count_qtr ' ||
'		,cust_count_year) ' ||
'	SELECT  ' ||
'		 SYSDATE ' ||
'		,SYSDATE ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,TRUNC(sizes.creation_date) ' ||
'		,act_size_used_by_id ' ||
'		,''SIZE'' ' ||
'		,sizes.size_delta ' ||
'		,seg.status_code ' ||
'		,0 ' ||
'		,0 ' ||
'		,0 ' ||
'		,0 ' ||
'	FROM	(SELECT a.cell_id  , a.status_code ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb 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 , a.status_code ' ||
'			 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'') ' ||
'			 ) seg , ams_act_sizes sizes ' ||
'	WHERE	seg.cell_id = sizes.act_size_used_by_id	 ' ||
'	AND		trunc(sizes.creation_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
 '   AND		sizes.arc_act_size_used_by = ''CELL'' ';

Line: 470


	bis_collection_utilities.log('Inserting Active Customer Count');

Line: 472


	Execute Immediate ' INSERT INTO bim_i_sgmt_facts ' ||
'		(creation_date ' ||
'		,last_update_date ' ||
'		,created_by ' ||
'		,last_updated_by ' ||
'		,last_update_login ' ||
'		,transaction_create_date ' ||
'		,segment_id ' ||
'		,metric_type ' ||
'		,segment_size ' ||
'		,cust_count_year ' ||
'		,cust_count_qtr ' ||
'		,cust_count_month ' ||
'		,cust_count_week)	 ' ||
'	SELECT  ' ||
'		SYSDATE ' ||
'		,SYSDATE ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,transaction_create_date ' ||
'		,segment_id ' ||
'		,''CUST'' ' ||
'		,0 ' ||
'		,SUM(ptd_year_cnt) ptd_year_cnt ' ||
'		,SUM(ptd_qtr_cnt ) ptd_qtr_cnt ' ||
'		,SUM(ptd_month_cnt) ptd_month_cnt ' ||
'		,SUM(ptd_week_cnt) ptd_week_cnt ' ||
'	FROM  ' ||
'		(  ' ||
'		 WITH party_orders AS  ' ||
'			(SELECT   ' ||
'				segs1.segment_id ' ||
'				,trunc(ord1.creation_date) transaction_create_date		 ' ||
'				,segs1.party_id ' ||
'			FROM  ' ||
'				oe_order_headers_all ord1 ' ||
'				,bim_i_party_sgmt_facts segs1 ' ||
'				,(SELECT a.cell_id  cell_id  ' ||
'				 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb 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'') ' ||
'			     ) cells  ' ||
'				,hz_cust_accounts cust_acct ' ||
'			WHERE cust_acct.party_id  = segs1.party_id ' ||
'			AND   cust_acct.cust_account_id = ord1.sold_to_org_id ' ||
'			AND   trunc(ord1.creation_date) between '''||p_start_date||''' AND '''||p_end_date||''''||
'			AND   ord1.creation_date between segs1.start_date_active AND segs1.end_date_active ' ||
'			AND   cells.cell_id=segs1.segment_id ' ||
'			) ' ||
'		(SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,(CASE ' ||
'				WHEN ROW_NUMBER() OVER (partition by b.week_id,a.party_id,segment_id ORDER BY week_id,a.party_id,segment_id,a.transaction_create_date asc)=1 ' ||
'				THEN 1 ' ||
'				ELSE 0 ' ||
'				END ' ||
'			) ptd_week_cnt ' ||
 '           ,0 ptd_qtr_cnt ' ||
  '  		,0 ptd_month_cnt ' ||
   ' 		,0 ptd_year_cnt       ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date = b.report_date ' ||
'		GROUP BY b.report_date_julian, a.transaction_create_date ,a.party_id,a.segment_id,b.week_id ' ||
 '       UNION ALL ' ||
  '      SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,0 ptd_week_cnt ' ||
 '           ,0 ptd_qtr_cnt ' ||
  '  		,(CASE ' ||
'				when ROW_NUMBER() OVER (partition by b.ent_period_id,a.party_id,segment_id ORDER BY b.ent_period_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
'				THEN 1 ' ||
'				ELSE 0 ' ||
'				END ' ||
'			) ptd_month_cnt ' ||
 '   		,0 ptd_year_cnt             ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date =  b.report_date  ' ||
'		GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id, b.ent_period_id ' ||
 '       UNION ALL ' ||
  '      SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,0 ptd_week_cnt ' ||
 '           ,(CASE ' ||
'				 WHEN ROW_NUMBER() OVER (partition by b.ent_qtr_id,a.party_id,segment_id ORDER BY b.ent_qtr_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
'				 THEN 1 ' ||
'				 ELSE 0 ' ||
'				 END ' ||
'			) ptd_qtr_cnt ' ||
 '   		,0 ptd_month_cnt ' ||
  '  		,0 ptd_year_cnt       ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date = b.report_date  ' ||
'		GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id,b.ent_qtr_id                         ' ||
'		UNION ALL ' ||
'		SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,0 ptd_week_cnt ' ||
 '           ,0 ptd_qtr_cnt ' ||
  '  		,0 ptd_month_cnt ' ||
   ' 		,(CASE ' ||
'				WHEN ROW_NUMBER() OVER (partition by b.ent_year_id,a.party_id,segment_id ORDER BY ent_year_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
'				THEN 1 ' ||
'				ELSE 0 ' ||
'				END ' ||
'			) ptd_year_cnt ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date = b.report_date  ' ||
'		GROUP BY b.report_date_julian,a.transaction_create_date ,a.party_id,a.segment_id, b.ent_year_id        ' ||
 '      ) ) ' ||
'	WHERE	ptd_year_cnt >0 or ptd_qtr_cnt>0 or ptd_month_cnt>0 or ptd_week_cnt >0 ' ||
'	GROUP BY segment_id , transaction_create_date ' ;

Line: 705


	select list_query_id, query
	from ams_list_queries_all ;

Line: 786


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

Line: 815


	bis_collection_utilities.log('Inserting Segment Size');

Line: 817


	DELETE FROM bim_i_sgmt_facts
	WHERE	metric_type = 'CUST'
	AND		transaction_create_date = TRUNC(p_start_date);

Line: 823


	Execute Immediate ' INSERT /*+ append parallel */ INTO bim_i_sgmt_facts ' ||
'		(creation_date ' ||
'		,last_update_date ' ||
'		,created_by ' ||
'		,last_updated_by ' ||
'		,last_update_login ' ||
'		,transaction_create_date ' ||
'		,segment_id ' ||
'		,metric_type ' ||
'		,segment_size ' ||
'		,cust_count_year ' ||
'		,cust_count_qtr ' ||
'		,cust_count_month ' ||
'		,cust_count_week) ' ||
'	SELECT  ' ||
'		 SYSDATE ' ||
'		,SYSDATE ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,TRUNC(sizes.creation_date) ' ||
'		,act_size_used_by_id ' ||
'		,''SIZE'' ' ||
'		,sizes.size_delta ' ||
'		,0 ' ||
'		,0 ' ||
'		,0 ' ||
'		,0 ' ||
'	FROM	(SELECT a.cell_id  , a.status_code ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb 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 , a.status_code ' ||
'			 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'') ' ||
'			 ) seg  ' ||
'			 , ams_act_sizes sizes ' ||
'	WHERE	seg.cell_id = sizes.act_size_used_by_id	 ' ||
'	AND		sizes.last_update_date between '''||p_start_date||''' and '''||p_end_date||''''||
 '   AND		sizes.ARC_ACT_SIZE_USED_BY = ''CELL'' ' ||
'	UNION ALL		 ' ||
'	SELECT  ' ||
'		SYSDATE ' ||
'		,SYSDATE ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,transaction_create_date ' ||
'		,segment_id ' ||
'		,''CUST'' ' ||
'		,0 ' ||
'		,SUM(ptd_year_cnt) ptd_year_cnt ' ||
'		,SUM(ptd_qtr_cnt ) ptd_qtr_cnt ' ||
'		,SUM(ptd_month_cnt) ptd_month_cnt ' ||
'		,SUM(ptd_week_cnt) ptd_week_cnt ' ||
'	FROM  ' ||
'		(  ' ||
'		 WITH party_orders AS  ' ||
'			(SELECT   ' ||
'				segs1.segment_id ' ||
'				,trunc(ord1.creation_date) transaction_create_date		 ' ||
'				,segs1.party_id ' ||
'			FROM  ' ||
'				oe_order_headers_all ord1 ' ||
'				,bim_i_party_sgmt_facts segs1 ' ||
'				,(SELECT a.cell_id  cell_id  ' ||
'				 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb 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'') ' ||
'			 ) cells  ' ||
'				,hz_cust_accounts cust_acct ' ||
'			WHERE cust_acct.party_id  = segs1.party_id ' ||
'			AND   cust_acct.cust_account_id = ord1.sold_to_org_id ' ||
'			AND   ord1.creation_date between '''||p_year_start_date||''' AND '''||p_end_date||''''||
'			AND   ord1.creation_date between segs1.start_date_active AND segs1.end_date_active ' ||
'			AND   cells.cell_id=segs1.segment_id		 ' ||
'			) ' ||
'		(SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,(CASE ' ||
'				WHEN ROW_NUMBER() OVER (partition by b.week_id,a.party_id,segment_id ORDER BY week_id,a.party_id,segment_id,a.transaction_create_date asc)=1 ' ||
'				THEN 1 ' ||
'				ELSE 0 ' ||
'				END ' ||
'			) ptd_week_cnt ' ||
 '           ,0 ptd_qtr_cnt ' ||
  '  		,0 ptd_month_cnt ' ||
   ' 		,0 ptd_year_cnt       ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date = b.report_date ' ||
'		GROUP BY b.report_date_julian, a.transaction_create_date ,a.party_id,a.segment_id,b.week_id ' ||
 '       UNION ALL ' ||
  '      SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,0 ptd_week_cnt ' ||
 '           ,0 ptd_qtr_cnt ' ||
  '  		,(CASE ' ||
'				when ROW_NUMBER() OVER (partition by b.ent_period_id,a.party_id,segment_id ORDER BY b.ent_period_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
'				THEN 1 ' ||
'				ELSE 0 ' ||
'				END ' ||
'			) ptd_month_cnt ' ||
 '   		,0 ptd_year_cnt             ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date =  b.report_date  ' ||
'		GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id, b.ent_period_id ' ||
 '       UNION ALL ' ||
  '      SELECT   ' ||
'			a.segment_id ' ||
'			,a.transaction_create_date transaction_create_date		 ' ||
'			,0 ptd_week_cnt ' ||
 '           ,(CASE ' ||
'				 WHEN ROW_NUMBER() OVER (partition by b.ent_qtr_id,a.party_id,segment_id ORDER BY b.ent_qtr_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
'				 THEN 1 ' ||
'				 ELSE 0 ' ||
'				 END ' ||
'			) ptd_qtr_cnt ' ||
 '   		,0 ptd_month_cnt ' ||
  '  		,0 ptd_year_cnt       ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date = b.report_date  ' ||
'		GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id,b.ent_qtr_id                         ' ||
'		UNION ALL ' ||
'		SELECT   ' ||
'			a.segment_id ' ||
'			,a. transaction_create_date transaction_create_date		 ' ||
'			,0 ptd_week_cnt ' ||
 '           ,0 ptd_qtr_cnt ' ||
  '  		,0 ptd_month_cnt ' ||
   ' 		,(CASE ' ||
'				WHEN ROW_NUMBER() OVER (partition by b.ent_year_id,a.party_id,segment_id ORDER BY ent_year_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
'				THEN 1 ' ||
'				ELSE 0 ' ||
'				END ' ||
'			) ptd_year_cnt ' ||
'		FROM party_orders a  ' ||
'			 ,fii_time_day b ' ||
'		WHERE a.transaction_create_date = b.report_date  ' ||
'		GROUP BY b.report_date_julian,a.transaction_create_date ,a.party_id,a.segment_id, b.ent_year_id        ' ||
 '      ) ) ' ||
'	WHERE	transaction_create_date >= trunc(to_date('''||p_start_date||''',''DD-MON-YY''),''MONTH'')'||
'	AND     (ptd_year_cnt >0 or ptd_qtr_cnt>0 or ptd_month_cnt>0 or ptd_week_cnt >0	) ' ||
'	GROUP BY segment_id , transaction_create_date ' ;