[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_LP_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.product_category_id product_category_id
,fact.item_id item_id
,fact.organization_id organization_id
,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
,fact.business_unit_id business_unit_id
,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
,sum(fact.lead_amount) lead_amount
,0 orders_booked_amt
,0 orders_invoiced_amt
,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 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
,count(fact.lead_amount) dummy9_fr
,0 dummy10_fr
,count(decode(qualified_flag,'Y',1,0)) dummy11_fr
,count(*) dummy12_fr
from
bim_i_lp_item_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.product_category_id
,fact.item_id
,fact.organization_id
,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
,fact.business_unit_id
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.product_category_id product_category_id
,fact.item_id item_id
,fact.organization_id organization_id
,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
,fact.business_unit_id business_unit_id
,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 lead_amount
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,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
,count(*) dummy12_fr
from bim_i_lp_item_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.product_category_id
,fact.item_id
,fact.organization_id
,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
,fact.business_unit_id
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.product_category_id product_category_id
,fact.item_id item_id
,fact.organization_id organization_id
,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
,fact.business_unit_id business_unit_id
,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 lead_amount
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,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
,count(*) dummy12_fr
from bim_i_lp_item_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.product_category_id
,fact.item_id
,fact.organization_id
,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
,fact.business_unit_id
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.product_category_id product_category_id
,fact.item_id item_id
,fact.organization_id organization_id
,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
,fact.business_unit_id business_unit_id
,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 lead_amount
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,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
,count(*) dummy12_fr
from bim_i_lp_item_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.product_category_id
,fact.item_id
,fact.organization_id
,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
,fact.business_unit_id
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.product_category_id product_category_id
,fact.item_id item_id
,fact.organization_id organization_id
,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
,fact.business_unit_id business_unit_id
,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 lead_amount
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,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
,count(*) dummy12_fr
from bim_i_lp_item_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.product_category_id
,fact.item_id
,fact.organization_id
,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
,fact.business_unit_id
/*UNION ALL
SELECT
6 umark
,fact.time_booked_date_id transaction_create_date
,null lead_creation_date
,null lead_converted_date
,quote.resource_grp_id group_id
,quote.resource_id resource_id
,null channel_code
,null cust_category
,item.vbh_category_id product_category_id
,DECODE(item.master_id,NULL,item.id,item.master_id) item_id
,fact.item_inv_org_id organization_id
,0 lead_rank_id
,null lead_source
,null lead_status
,null lead_region
,null lead_country
,null source_code_id
,null object_type
,null object_id
,null child_object_type
,null child_object_id
,null object_region
,null object_country
,null child_object_region
,null child_object_country
,null business_unit_id
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,0 lead_amount
,sum(fact.booked_amt_g) orders_booked_amt
,sum(fact.invoiced_amt_g) orders_invoiced_amt
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,0 dummy1_fr
,count(fact.booked_amt_g) 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
,count(fact.invoiced_amt_g) dummy11_fr
,count(*) dummy12_fr
from isc.isc_book_sum2_f fact
,aso.aso_bi_quote_hdrs_all quote
,eni.eni_oltp_item_star item
WHERE fact.header_id = quote.order_id
AND fact.line_category_code = 'ORDER'
AND fact.order_category_id = 'ORDER'
AND fact.inventory_item_id = item.inventory_item_id
AND fact.item_inv_org_id = item.organization_id
GROUP BY
fact.time_booked_date_id
,quote.resource_grp_id
,quote.resource_id
,item.vbh_category_id
,DECODE(item.master_id,NULL,item.id,item.master_id)
,fact.item_inv_org_id */
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.product_category_id product_category_id
,fact.item_id item_id
,fact.organization_id organization_id
,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
,fact.business_unit_id business_unit_id
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,0 lead_amount
,0 orders_booked_amt
,0 orders_invoiced_amt
,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 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
,count(decode(code.column_name,'A',decode(qualified_flag, 'Y',1,0),0)) dummy11_fr
,count(*) dummy12_fr
from
bim_i_lp_item_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.product_category_id
,fact.item_id
,fact.organization_id
,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
,fact.business_unit_id