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.
MATERIALIZED VIEW: APPS.BIM_SGMT_VAL_B_MV

Source


SELECT
			1							umark
			,segment_id					segment_id
			,account_open_date			transaction_create_date
			,count(CASE WHEN account_open_date BETWEEN start_date_active and end_date_active THEN 1
					ELSE 0
					END)				acquired_customers
			, count(party_id)			total_customers
			,0							lost_customers
			,0							segment_size
			,0							segement_size_c
			,0							booked_amt
			,0							booked_amt_c
			,0							booked_amt_s
			,0							booked_amt_s_c
			,0							booked_Count
			,0							revenue
			,0							revenue_c
			,0							revenue_s
			,0							revenue_s_c
			,count(*)					count_all
		FROM  bim_i_party_sgmt_facts
		WHERE account_open_date IS NOT NULL
		GROUP BY segment_id, account_open_date
		UNION ALL
		SELECT
			2							umark
			,segment_id					segment_id
			,account_close_date			transaction_create_date
			,0		 					acquired_customers
			,count(segment_id)			lost_customers
			,0							total_customers
			,0							segment_size
			,0							segement_size_c
			,0							booked_amt
			,0							booked_amt_c
			,0							booked_amt_s
			,0							booked_amt_s_c
			,0							booked_Count
			,0							revenue
			,0							revenue_c
			,0							revenue_s
			,0							revenue_s_c
			,count(*)					count_all
		FROM  bim_i_party_sgmt_facts
		WHERE account_close_date IS NOT NULL
		GROUP BY segment_id, account_close_date
		UNION ALL
		--Segment Size
		SELECT
			3							umark
			,segment_id					segment_id
			,transaction_create_date	transaction_create_date
			,0							acquired_customers
			,0							lost_customers
			,0							total_customers
			,sum(segment_size)			segment_size
			,count(segment_size)		segement_size_c
			,0							booked_amt
			,0							booked_amt_c
			,0							booked_amt_s
			,0							booked_amt_s_c
			,0							booked_Count
			,0							revenue
			,0							revenue_c
			,0							revenue_s
			,0							revenue_s_c
			,count(*)					count_all
		FROM  bim_i_sgmt_facts
		WHERE metric_type = 'SIZE'
		GROUP BY segment_id,transaction_create_date
		UNION ALL
		--Average Transactional Value
		SELECT
			4								umark
			,segment_id						segment_id
			,order_mv.time_booked_date_id	transaction_create_date
			,0								acquired_customers
			,0								lost_customers
			,0								total_customers
			,0								segment_size
			,0								segement_size_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(line_id)			        booked_Count
			,0								revenue
			,0								revenue_c
			,0								revenue_s
			,0								revenue_s_c
			,count(*)			            count_all
		FROM  bim_i_party_sgmt_facts party,ISC.isc_book_sum2_f order_mv
		WHERE order_mv.customer_id = party.party_id
		AND   order_mv.time_booked_date_id BETWEEN party.start_date_active and party.end_date_active
		AND   order_mv.ORDER_CATEGORY_ID ='ORDER'
		AND   order_mv.LINE_CATEGORY_CODE ='ORDER'
		GROUP BY party.segment_id,order_mv.time_booked_date_id
		--- revenue
		UNION ALL
		SELECT
			5								umark
			,seg.segment_id					segment_id
			,rev.gl_date					transaction_create_date
			,0								acquired_customers
			,0								lost_customers
			,0								total_customers
			,0								segment_size
			,0								segement_size_c
			,0								booked_amt
			,0								booked_amt_c
			,0								booked_amt_s
			,0								booked_amt_s_c
			,0								booked_Count
			,sum(rev.prim_amount_g)			revenue
			,count(rev.prim_amount_g)		revenue_c
			,sum(rev.sec_amount_g)			revenue_s
			,count(rev.sec_amount_g)		revenue_s_c
			,count(*)			            count_all
		FROM FII.fii_ar_revenue_b rev, bim_i_party_sgmt_facts seg
		WHERE seg.party_id = rev.bill_to_party_id
		AND rev.fin_cat_type_code = 'R'
		AND rev.gl_date BETWEEN seg.start_date_active AND seg.end_date_active
		GROUP BY seg.segment_id, rev.gl_date