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_CUST_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 Customer Base Summary - Initial Load Concurrent Program before running this');

Line: 279


	select list_query_id, query
	from ams_list_queries_all ;

Line: 360


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

Line: 395


	BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SGMT_CUST');

Line: 398


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

Line: 400


	Execute Immediate 'INSERT/*+ append parallel*/ INTO bim_i_party_sgmt_facts ' ||
'		(segment_id ' ||
'		,party_id ' ||
'		,start_date_active ' ||
'		,end_date_active ' ||
'		,status,account_close_date ' ||
'		,creation_date ' ||
'		,last_update_date ' ||
'		,created_by ' ||
'		,last_updated_by ' ||
'		,last_update_login ' ||
'		) ' ||
'	SELECT segment_id ' ||
'		   ,inner.party_id ' ||
'		   ,TRUNC(start_date_active) ' ||
'		   ,TRUNC(end_date_active) ' ||
'		   ,party.status status ' ||
'		   ,CASE WHEN party.status=''I'' AND party.last_update_date BETWEEN start_date_active and end_date_active THEN  ' ||
'				party.last_update_date  ' ||
'			ELSE  ' ||
'				NULL  ' ||
'			END account_close_date ' ||
'			,sysdate ' ||
'			,sysdate ' ||
'			,-1 ' ||
'			,-1 ' ||
'			,-1 ' ||
'	FROM  ' ||
'	(SELECT market_segment_id segment_id ' ||
'			,nvl(reln.subject_id,a.party_id) party_id ' ||
'			,min(start_date_active) start_date_active ' ||
'			,max(nvl(end_date_active,TO_DATE(''31/12/2199'',''DD/MM/YYYY''))) end_date_active ' ||
'	FROM	ams_party_market_segments a ' ||
'			,(SELECT a.cell_id  cell_id  ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_scfb 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'') ' ||
'			 ) b  ' ||
'			 , hz_relationships reln ' ||
'	WHERE a.market_segment_id = b.cell_id ' ||
'	AND	 trunc(a.creation_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
'	AND  a.party_id = reln.party_id(+) ' ||
'	AND reln.subject_type(+) =''ORGANIZATION'' ' ||
'	AND object_type(+)=''PERSON'' ' ||
'	GROUP BY nvl(reln.subject_id,a.party_id),a.market_segment_id) inner,hz_parties party ' ||
'	WHERE inner.party_id=party.party_id';

Line: 468


	UPDATE bim_i_party_sgmt_facts a
	SET account_open_date=(SELECT GREATEST(min(TRUNC(creation_date)),trunc(a.start_date_active))
							   FROM  hz_cust_accounts b
								WHERE a.party_id=b.party_id)
	WHERE EXISTS
	( SELECT 1
	  FROM hz_cust_accounts b
	  WHERE a.party_id=b.party_id);

Line: 574


	select list_query_id, query
	from ams_list_queries_all ;

Line: 655


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

Line: 685


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

Line: 694


	Execute Immediate 'INSERT /*+Append parallel*/ INTO bim_i_party_sgmt_stg ' ||
'		( ' ||
'		segment_id  ' ||
'		,party_id 	 ' ||
'		,end_date_active  ' ||
'		,status  ' ||
'		,account_close_date		 ' ||
'		) ' ||
'	SELECT  ' ||
'		stg.segment_id segment_id ' ||
'		,stg.party_id party_id ' ||
'		,TRUNC(max(segs_party.end_date_active)) end_date_active ' ||
'		,parties.status status ' ||
'		,(CASE WHEN parties.status IN (''I'',''A'') THEN TRUNC(parties.last_update_date) ELSE NULL END) account_closure_date ' ||
'	FROM   ams_party_market_segments a ' ||
'			,(SELECT a.cell_id    ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_scfb 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'') ' ||
'			 ) b ' ||
'			,hz_relationships reln ' ||
'			,bim_i_party_sgmt_facts stg ' ||
'			,hz_relationships reln2 ' ||
'			,ams_party_market_segments segs_party ' ||
'			,hz_parties parties ' ||
'	WHERE a.market_segment_id=b.cell_id	 ' ||
'	AND  stg.party_id=nvl(reln.subject_id,a.party_id) ' ||
'	AND  stg.segment_id=a.market_segment_id	 ' ||
'	AND  a.party_id=reln.party_id(+) ' ||
'	AND  reln.subject_type(+) =''ORGANIZATION'' ' ||
'	AND  reln.object_type(+)=''PERSON'' ' ||
'	AND  reln.relationship_code(+)=''CONTACT'' ' ||
'	AND  a.party_id=reln.party_id(+) ' ||
'	AND  reln2.subject_type(+) =''PERSON'' ' ||
'	AND  reln2.object_type(+)=''ORGANIZATION'' ' ||
'	AND  reln2.relationship_code(+)=''CONTACT_OF'' ' ||
'	AND  reln2.object_id(+)=stg.party_id ' ||
'	AND ((segs_party.party_id=reln2.party_id) or (segs_party.party_id=stg.party_id)) ' ||
'	AND segs_party.market_segment_id=stg.segment_id ' ||
'	AND stg.party_id=parties.party_id ' ||
'	AND trunc(a.last_update_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
'	GROUP BY stg.segment_id,stg.party_id,parties.status,parties.last_update_date' ;

Line: 758


	--UPDATE STATEMENT FOR END_DATE_ACTIVE IF END_DATE_ACTIVE IS DIFFRENT FROM LAST RUN

	UPDATE	bim_i_party_sgmt_facts facts
	SET		end_date_Active = ( SELECT TRUNC(end_date_active)
								FROM	bim_i_party_sgmt_stg stg
								WHERE	facts.segment_id=stg.segment_id
								AND     facts.party_id = stg.party_id
							  )
	WHERE	EXISTS (SELECT 1
					FROM	bim_i_party_sgmt_stg stg
					WHERE	facts.segment_id=stg.segment_id
					AND     facts.party_id = stg.party_id
					AND		facts.end_date_active<>stg.end_date_active
					) ;

Line: 773


	--UPDATE STATEMENT FOR ACCOUNT_CLOSURE_DATE IF ACCOUNT_CLOSURE_DATE IS DIFFRENT FROM LAST RUN

	UPDATE	bim_i_party_sgmt_facts facts
	SET		account_close_date = ( SELECT	TRUNC(account_close_date)
									 FROM	bim_i_party_sgmt_stg stg
									 WHERE	facts.segment_id=stg.segment_id
									 AND     facts.party_id = stg.party_id
									)
	WHERE	EXISTS (SELECT 1
					FROM	bim_i_party_sgmt_stg stg
					WHERE	facts.segment_id=stg.segment_id
					AND     facts.party_id = stg.party_id
					AND		facts.account_close_date <> stg.account_close_date
					);

Line: 788


	--Insert rows for newly created segment and party combination

	Execute Immediate ' INSERT /*+ append parallel */INTO bim_i_party_sgmt_facts ' ||
'		(segment_id ' ||
'		,party_id ' ||
'		,start_date_active ' ||
'		,end_date_active ' ||
'		,status ' ||
'		,account_close_date ' ||
'		,creation_date ' ||
'		,last_update_date ' ||
'		,created_by ' ||
'		,last_updated_by ' ||
'		,last_update_login) ' ||
'	SELECT ' ||
'		market_segment_id segment_id ' ||
'		,nvl(reln.subject_id,a.party_id) party_id ' ||
'		,trunc(min(start_date_active)) start_date_active ' ||
'		,trunc(max(nvl(end_date_active,TO_DATE(''31/12/2199'',''DD/MM/YYYY'')))) end_date_active ' ||
'		,parties.status status ' ||
'		,CASE WHEN parties.status=''I'' then parties.last_update_date ELSE NULL END ' ||
'		,sysdate ' ||
'		,sysdate ' ||
'		,-1 ' ||
'		,-1 ' ||
'		,-1 ' ||
'	FROM	ams_party_market_segments a ' ||
'			,(SELECT a.cell_id   ' ||
'			 FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_scfb 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'') ' ||
'			 ) b ' ||
'			 ,hz_relationships reln ' ||
'			 ,hz_parties parties ' ||
'	WHERE	a.market_segment_id=b.cell_id ' ||
'	AND	a.party_id=reln.party_id(+) ' ||
'	AND	reln.subject_type(+) =''ORGANIZATION''  ' ||
'	AND	OBJECT_TYPE(+)=''PERSON'' ' ||
'	AND	nvl(reln.subject_id,a.party_id)=parties.party_id ' ||
'	AND trunc(a.last_update_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
'	AND	NOT EXISTS ( SELECT 1  ' ||
'					FROM bim_i_party_sgmt_stg stg ' ||
'					WHERE stg.segment_id=a.market_segment_id ' ||
'					AND nvl(reln.subject_id,a.party_id)=stg.party_id ' ||
'					) ' ||
'	GROUP BY nvl(reln.subject_id,a.party_id),a.market_segment_id,parties.status,parties.last_update_date ' ;

Line: 856


	--update account creation date for those parties which does not have acoounts(in case  of prospect)

	UPDATE bim_i_party_sgmt_facts a
	SET account_open_date=(SELECT GREATEST(min(TRUNC(creation_date)),trunc(a.start_date_active))
								FROM  hz_cust_accounts b
								WHERE a.party_id=b.party_id
								AND  b.creation_date >= g_initial_start_date)
	WHERE EXISTS( SELECT 1
				  FROM   hz_cust_accounts b
				  WHERE  a.party_id=b.party_id
				  AND    b.creation_date >= g_initial_start_date)
	AND    account_open_date IS NULL;