[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_LP_GH_SUM_MV
Source
SELECT /* Dummy Comment */
1 umark
,gdn.parent_group_id group_id
,-1 resource_id
,a.transaction_create_date transaction_create_date
,a.lead_creation_date lead_creation_date
,a.lead_converted_date lead_converted_date
,a.product_category_id product_category_id
,a.item_id item_id
,a.organization_id organization_id
,a.lead_rank_id lead_rank_id
,a.lead_source lead_source
,a.channel_code channel_code
,a.cust_category cust_category
,a.lead_region lead_region
,a.lead_country lead_country
,sum(a.leads) leads
,sum(a.leads_new) leads_new
,sum(a.leads_open) leads_open
,sum(a.leads_changed) leads_changed
,sum(a.leads_converted) leads_converted
,sum(a.leads_dead) leads_dead
,sum(a.leads_closed) leads_closed
,sum(a.leads_qualified) leads_qualified
,sum(a.lead_amount) lead_amount
,sum(a.orders_booked_amt) orders_booked_amt
,sum(a.orders_invoiced_amt) orders_invoiced_amt
,sum(a.rank_a) rank_a
,sum(a.rank_b) rank_b
,sum(a.rank_c) rank_c
,sum(a.rank_d) rank_d
,sum(a.rank_z) rank_z
,sum(a.leads_qualified_a) leads_qualified_a
,count(a.leads) dummy1_fr
,count(a.leads_new) dummy2_fr
,count(a.leads_open) dummy3_fr
,count(a.leads_changed) dummy4_fr
,count(a.leads_converted) dummy5_fr
,count(a.leads_dead) dummy6_fr
,count(a.leads_closed) dummy7_fr
,count(a.leads_qualified) dummy8_fr
,count(a.rank_a) dummy9_fr
,count(a.rank_b) dummy10_fr
,count(a.rank_c) dummy11_fr
,count(a.rank_d) dummy12_fr
,count(a.rank_z) dummy13_fr
,count(a.leads_qualified_a) dummy14_fr
,count(a.lead_amount) dummy15_fr
,count(a.orders_booked_amt) dummy16_fr
,count(a.orders_invoiced_amt) dummy17_fr
,count(*) dummy18_fr
FROM
bim_i_lp_base_mv A
,JTF.JTF_RS_GROUPS_DENORM GDN
,JTF.JTF_RS_GROUP_USAGES GU
WHERE
gdn.group_id = a.group_id
AND gdn.latest_relationship_flag = 'Y'
AND gdn.group_id = gu.group_id
AND gu.usage = 'SALES'
GROUP BY
gdn.parent_group_id
,a.transaction_create_date
,a.lead_creation_date
,a.lead_converted_date
,a.product_category_id
,a.item_id
,a.organization_id
,a.lead_rank_id
,a.lead_source
,a.channel_code
,a.cust_category
,a.lead_region
,a.lead_country
UNION ALL
SELECT
2 umark
,a.group_id group_id
,a.resource_id resource_id
,a.transaction_create_date transaction_create_date
,a.lead_creation_date lead_creation_date
,a.lead_converted_date lead_converted_date
,a.product_category_id product_category_id
,a.item_id item_id
,a.organization_id organization_id
,a.lead_rank_id lead_rank_id
,a.lead_source lead_source
,a.channel_code channel_code
,a.cust_category cust_category
,a.lead_region lead_region
,a.lead_country lead_country
,sum(a.leads) leads
,sum(a.leads_new) leads_new
,sum(a.leads_open) leads_open
,sum(a.leads_changed) leads_changed
,sum(a.leads_converted) leads_converted
,sum(a.leads_dead) leads_dead
,sum(a.leads_closed) leads_closed
,sum(a.leads_qualified) leads_qualified
,sum(a.lead_amount) lead_amount
,sum(a.orders_booked_amt) orders_booked_amt
,sum(a.orders_invoiced_amt) orders_invoiced_amt
,sum(a.rank_a) rank_a
,sum(a.rank_b) rank_b
,sum(a.rank_c) rank_c
,sum(a.rank_d) rank_d
,sum(a.rank_z) rank_z
,sum(a.leads_qualified_a) leads_qualified_a
,count(a.leads) dummy1_fr
,count(a.leads_new) dummy2_fr
,count(a.leads_open) dummy3_fr
,count(a.leads_changed) dummy4_fr
,count(a.leads_converted) dummy5_fr
,count(a.leads_dead) dummy6_fr
,count(a.leads_closed) dummy7_fr
,count(a.leads_qualified) dummy8_fr
,count(a.rank_a) dummy9_fr
,count(a.rank_b) dummy10_fr
,count(a.rank_c) dummy11_fr
,count(a.rank_d) dummy12_fr
,count(a.rank_z) dummy13_fr
,count(a.leads_qualified_a) dummy14_fr
,count(a.lead_amount) dummy15_fr
,count(a.orders_booked_amt) dummy16_fr
,count(a.orders_invoiced_amt) dummy17_fr
,count(*) dummy18_fr
FROM
bim_i_lp_base_mv A
GROUP BY
a.group_id
,a.resource_id
,a.transaction_create_date
,a.lead_creation_date
,a.lead_converted_date
,a.product_category_id
,a.item_id
,a.organization_id
,a.lead_rank_id
,a.lead_source
,a.channel_code
,a.cust_category
,a.lead_region
,a.lead_country