[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_LD_BASE_MV
Source
SELECT /* Dummy Comment */
1 umark
,fact.lead_creation_date transaction_create_date
,fact.lead_creation_date lead_creation_date
,fact.lead_converted_date lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,count(fact.lead_id) leads
,count(fact.lead_id) leads_new
,sum(CASE
WHEN fact.lead_converted_date is null
AND fact.lead_dead_date is null
AND fact.lead_closed_date is null
THEN 1
ELSE 0
END) leads_open
,0 leads_changed
,0 leads_converted
,0 leads_dead
,0 leads_closed
,sum(decode(qualified_flag,'Y',1,0)) leads_qualified
,0 aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,sum(fact.lead_amount) lead_amount
,sum(decode(customer_flag,'Y',1,0)) leads_customer
,sum(decode(customer_flag,'N',1,0)) leads_prospect
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,sum(decode(lead_rank_id,null,1,0)) rank_z
,0 leads_qualified_a
,0 leads_conv_customer
,0 leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,count(CASE
WHEN fact.lead_converted_date is null
AND fact.lead_dead_date is null
AND fact.lead_closed_date is null
THEN 1
ELSE 0
END) dummy3_fr
,0 dummy4_fr
,0 dummy5_fr
,0 dummy6_fr
,0 dummy7_fr
,count(decode(lead_rank_id,null,1,0)) dummy8_fr
,0 dummy9_fr
,count(fact.lead_amount) dummy10_fr
,count(decode(customer_flag,'Y',1,0)) dummy11_fr
,count(decode(customer_flag,'N',1,0)) dummy12_fr
,count(decode(qualified_flag,'Y',1,0)) dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
GROUP BY
fact.lead_creation_date
,fact.lead_converted_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country
UNION ALL
SELECT
2 umark
,fact.lead_touched_date transaction_create_date
,null lead_creation_date
,null lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,0 leads
,0 leads_new
,0 leads_open
,count(fact.lead_id) leads_changed
,0 leads_converted
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,0 aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,0 leads_customer
,0 leads_prospect
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,0 leads_conv_customer
,0 leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,0 dummy3_fr
,0 dummy4_fr
,0 dummy5_fr
,0 dummy6_fr
,0 dummy7_fr
,0 dummy8_fr
,0 dummy9_fr
,0 dummy10_fr
,0 dummy11_fr
,0 dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
WHERE fact.lead_touched_date is not null
GROUP BY
fact.lead_touched_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country
UNION ALL
SELECT
3 umark
,fact.lead_converted_date transaction_create_date
,fact.lead_creation_date lead_creation_date
,fact.lead_converted_date lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,count(fact.lead_id) leads_converted
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,0 aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,0 leads_customer
,0 leads_prospect
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,sum(decode(customer_flag,'Y',1,0)) leads_conv_customer
,sum(decode(customer_flag,'N',1,0)) leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,0 dummy3_fr
,0 dummy4_fr
,0 dummy5_fr
,0 dummy6_fr
,0 dummy7_fr
,0 dummy8_fr
,0 dummy9_fr
,0 dummy10_fr
,0 dummy11_fr
,0 dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,count(decode(customer_flag,'Y',1,0)) dummy15_fr
,count(decode(customer_flag,'N',1,0)) dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
WHERE fact.lead_converted_date is not null
GROUP BY
fact.lead_converted_date
,fact.lead_creation_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country
UNION ALL
SELECT
4 umark
,fact.lead_dead_date transaction_create_date
,null lead_creation_date
,null lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,count(fact.lead_id) leads_dead
,0 leads_closed
,0 leads_qualified
,0 aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,0 leads_customer
,0 leads_prospect
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,0 leads_conv_customer
,0 leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,0 dummy3_fr
,0 dummy4_fr
,0 dummy5_fr
,0 dummy6_fr
,0 dummy7_fr
,0 dummy8_fr
,0 dummy9_fr
,0 dummy10_fr
,0 dummy11_fr
,0 dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
WHERE fact.lead_dead_date is not null
GROUP BY
fact.lead_dead_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country
UNION ALL
SELECT
5 umark
,fact.lead_closed_date transaction_create_date
,null lead_creation_date
,null lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,0 leads_dead
,count(fact.lead_id) leads_closed
,0 leads_qualified
,0 aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,0 leads_customer
,0 leads_prospect
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,0 leads_conv_customer
,0 leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,0 dummy3_fr
,0 dummy4_fr
,0 dummy5_fr
,0 dummy6_fr
,0 dummy7_fr
,0 dummy8_fr
,0 dummy9_fr
,0 dummy10_fr
,0 dummy11_fr
,0 dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
WHERE fact.lead_closed_date is not null
GROUP BY
fact.lead_closed_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country
UNION ALL
SELECT
7 umark
,fact.lead_creation_date transaction_create_date
,null lead_creation_date
,null lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,0 aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,0 leads_customer
,0 leads_prospect
,sum(decode(code.column_name, 'A',1,0)) rank_a
,sum(decode(code.column_name, 'B',1,0)) rank_b
,sum(decode(code.column_name, 'C',1,0)) rank_c
,sum(decode(code.column_name, 'D',1,0)) rank_d
,sum(decode(code.column_name, 'Z',1,0)) rank_z
,sum(decode(code.column_name, 'A', decode(qualified_flag, 'Y',1,0),0) ) leads_qualified_a
,0 leads_conv_customer
,0 leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,0 dummy3_fr
,count(decode(code.column_name, 'A',1,0)) dummy4_fr
,count(decode(code.column_name, 'B',1,0)) dummy5_fr
,count(decode(code.column_name, 'C',1,0)) dummy6_fr
,count(decode(code.column_name, 'D',1,0)) dummy7_fr
,count(decode(code.column_name, 'Z',1,0)) dummy8_fr
,0 dummy9_fr
,0 dummy10_fr
,0 dummy11_fr
,count(decode(code.column_name, 'A', decode(qualified_flag, 'Y',1,0),0) ) dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
,BIM.BIM_R_CODE_DEFINITIONS code
WHERE fact.lead_rank_id = code.object_def
AND code.object_type = 'RANK_DBI'
GROUP BY
fact.lead_creation_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country
UNION ALL
SELECT
8 umark
,fact.lead_converted_date transaction_create_date
,fact.lead_creation_date lead_creation_date
,fact.lead_converted_date lead_converted_date
,fact.group_id group_id
,fact.resource_id resource_id
,fact.channel_code channel_code
,fact.cust_category cust_category
,fact.lead_rank_id lead_rank_id
,fact.lead_source lead_source
,fact.lead_status lead_status
,fact.lead_region lead_region
,fact.lead_country lead_country
,fact.source_code_id source_code_id
,fact.object_type object_type
,fact.object_id object_id
,fact.child_object_type child_object_type
,fact.child_object_id child_object_id
,fact.object_region object_region
,fact.object_country object_country
,fact.child_object_region child_object_region
,fact.child_object_country child_object_country
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,count(decode(code.column_name,'A',1,NULL)) aleads_converted
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,0 leads_customer
,0 leads_prospect
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,0 leads_conv_customer
,0 leads_conv_prospect
,0 dummy1_fr
,0 dummy2_fr
,0 dummy3_fr
,0 dummy4_fr
,0 dummy5_fr
,0 dummy6_fr
,0 dummy7_fr
,0 dummy8_fr
,0 dummy9_fr
,0 dummy10_fr
,0 dummy11_fr
,0 dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,count(*) dummy17_fr
from
bim_i_ld_header_mv fact
,BIM.BIM_R_CODE_DEFINITIONS code
WHERE fact.lead_converted_date is not null
AND fact.lead_rank_id = code.object_def
AND code.object_type = 'RANK_DBI'
GROUP BY
fact.lead_converted_date
,fact.lead_creation_date
,fact.group_id
,fact.resource_id
,fact.channel_code
,fact.cust_category
,fact.lead_rank_id
,fact.lead_source
,fact.lead_status
,fact.lead_region
,fact.lead_country
,fact.source_code_id
,fact.object_type
,fact.object_id
,fact.child_object_type
,fact.child_object_id
,fact.object_region
,fact.object_country
,fact.child_object_region
,fact.child_object_country