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.
[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_SGMT_ACT_B_MV

Source


SELECT /*comment for mv recreate */
			1				umark
			,segment_id			segment_id
			,activity_id			activity_id
			,schedule_purpose		schedule_purpose
			,NVL(category_id,-1) 	        category_id
			,source_code_id				source_code_id
			,schedule_start_date	transaction_create_date
			,count(source_code_id)	activities_count
			,0						responses
			,0						resp_cnt
			,0						leads
			,0						new_opportunity_amt
			,0						new_opportunity_amt_c
			,0						new_opportunity_amt_s
			,0						new_opportunity_amt_s_c
			,0						booked_amt
			,0						booked_amt_c
			,0						booked_amt_s
			,0						booked_amt_s_c
			,count(*)				count_all
		FROM  bim_sgmt_act_h_mv
		GROUP BY segment_id, activity_id,NVL(category_id,-1),schedule_start_date ,schedule_purpose,source_code_id
		UNION ALL
		SELECT
			2						umark
			,segs.segment_id		segment_id
			,segs.activity_id		activity_id
			,schedule_purpose		schedule_purpose
			,NVL(segs.category_id,-1) 	category_id
			,segs.source_code_id	source_code_id
			,resp_mv.transaction_create_date transaction_create_date
			,0						activities_count
			,sum(response_count)	responses
			,count(response_count)	resp_cnt
			,0						leads
			,0						new_opportunity_amt
			,0						new_opportunity_amt_c
			,0						new_opportunity_amt_s
			,0						new_opportunity_amt_s_c
			,0						booked_amt
			,0						booked_amt_c
			,0						booked_amt_s
			,0						booked_amt_s_c
			,count(*)				count_all
		FROM  bim_sgmt_act_h_mv segs,BIM.BIM_I_PARTY_SGMT_FACTS party,bix_call_presp_mv resp_mv
		WHERE segs.segment_id = party.segment_id
		AND   resp_mv.party_id = party.party_id
		AND   resp_mv.source_code_id = segs.source_code_id
		AND		resp_mv.transaction_create_date BETWEEN party.start_date_active AND party.end_date_active
		GROUP BY segs.segment_id, activity_id,NVL(segs.category_id,-1) , resp_mv.transaction_create_date,segs.source_code_id ,schedule_purpose
		UNION ALL
		SELECT
			3 umark
			,segs.segment_id		segment_id
			,activity_id			activity_id
			,schedule_purpose		schedule_purpose
			,NVL(segs.category_id,-1) 	category_id
			,segs.source_code_id	source_code_id
			,leads_mv.lead_creation_date	transaction_create_date
			,0						activities_count
			,0						responses
			,0						resp_cnt
			,count(lead_id)			leads
			,0						new_opportunity_amt
			,0						new_opportunity_amt_c
			,0						new_opportunity_amt_s
			,0						new_opportunity_amt_s_c
			,0						booked_amt
			,0						booked_amt_c
			,0						booked_amt_s
			,0						booked_amt_s_c
			,count(*)				count_all
		FROM  bim_sgmt_act_h_mv segs, BIM.BIM_I_PARTY_SGMT_FACTS party,bim_i_ld_header_mv leads_mv
		WHERE segs.segment_id = party.segment_id
		AND   leads_mv.customer_id = party.party_id
		AND   leads_mv.source_code_id = segs.source_code_id
		AND	  leads_mv.lead_creation_date BETWEEN party.start_date_active AND party.end_date_active
		GROUP BY segs.segment_id,segs.activity_id,NVL(segs.category_id,-1),leads_mv.lead_creation_date,segs.source_code_id , schedule_purpose
		UNION ALL
		SELECT
			4						umark
			,segs.segment_id		segment_id
			,activity_id			activity_id
			,schedule_purpose		schedule_purpose
			,NVL(segs.category_id,-1) 	category_id
			,segs.source_code_id	source_code_id
			,to_date(opty_mv.opty_creation_time_id,'J') transaction_create_date
			,0						activities_count
			,0						responses
			,0						resp_cnt
			,0						leads
			,sum(opty_mv.new_opty_amt)	new_opportunity_amt
			,count(opty_mv.new_opty_amt)	new_opportunity_amt_c
			,sum(opty_mv.new_opty_amt_s)	new_opportunity_amt_s
			,count(opty_mv.new_opty_amt_s)	new_opportunity_amt_s_c
			,0						booked_amt
			,0						booked_amt_c
			,0						booked_amt_s
			,0						booked_amt_s_c
			,count(*)				count_all
		FROM  bim_sgmt_act_h_mv segs,BIM.BIM_I_PARTY_SGMT_FACTS party,bil_bi_opdtl_mv opty_mv
		WHERE segs.segment_id = party.segment_id
		AND   opty_mv.customer_id = party.party_id
		AND   opty_mv.hdr_source_promotion_id = segs.source_code_id
		AND   to_date(opty_mv.opty_creation_time_id,'J') BETWEEN party.start_date_active AND party.end_date_active
		GROUP BY segs.segment_id,segs.activity_id,NVL(segs.category_id,-1),to_date(opty_mv.opty_creation_time_id,'J'),segs.source_code_id , schedule_purpose
		UNION ALL
		SELECT
			5						umark
			,segs.segment_id		segment_id
			,activity_id			activity_id
			,schedule_purpose		schedule_purpose
			,NVL(segs.category_id,-1) 	category_id
			,segs.source_code_id	source_code_id
			,order_mv.time_booked_date_id transaction_create_date
			,0						activities_count
			,0						responses
			,0						resp_cnt
			,0						leads
			,0						new_opportunity_amt
			,0						new_opportunity_amt_c
			,0						new_opportunity_amt_s
			,0						new_opportunity_amt_s_c
			,sum(order_mv.booked_amt_f)		booked_amt
			,count(order_mv.booked_amt_f)	booked_amt_c
			,sum(order_mv.booked_amt_g1)	booked_amt_s
			,count(order_mv.booked_amt_g1)	booked_amt_s_c
			,count(*)			count_all
		FROM  bim_sgmt_act_h_mv segs,BIM.BIM_I_PARTY_SGMT_FACTS party,ISC.ISC_BOOK_SUM2_F order_mv
		WHERE segs.segment_id = party.segment_id
		AND   order_mv.customer_id = party.party_id
		AND order_mv.h_marketing_source_code_id = segs.source_code_id
		AND order_mv.ORDER_CATEGORY_ID ='ORDER'
		AND order_mv.LINE_CATEGORY_CODE ='ORDER'
		AND	order_mv.time_booked_date_id	BETWEEN party.start_date_active AND party.end_date_active
		GROUP BY segs.segment_id,segs.activity_id,NVL(segs.category_id,-1) ,order_mv.time_booked_date_id
		,segs.source_code_id ,schedule_purpose -- dummy comments