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_I_LP_AGE_SG_MV

Source


SELECT /* DUMMY COMMENT */
	  1 umark
      ,a.group_id
      ,a.resource_id
      ,b.parent_id                     product_category_id
      ,a.channel_code                  channel_code
       ,a.cust_category                 cust_category
       ,a.lead_rank_id                  lead_rank_id
       ,a.lead_source                   lead_source
       ,a.lead_country                  lead_country
      ,null                             item_id
      ,null                            organization_id
      ,(CASE
            WHEN GROUPING_ID(a.group_id,a.resource_id,b.parent_id,a.channel_code)=0
	    THEN 'CHANNEL'
	    WHEN GROUPING_ID(a.group_id,a.resource_id,b.parent_id,a.cust_category)=0
	    THEN 'CUSTCAT'
            WHEN GROUPING_ID(a.group_id,a.resource_id,b.parent_id,a.lead_rank_id)=0
	    THEN 'QUALITY'
            WHEN GROUPING_ID(a.group_id,a.resource_id,b.parent_id,a.lead_source)=0
	    THEN 'SOURCE'
            WHEN GROUPING_ID(a.group_id,a.resource_id,b.parent_id,a.lead_country)=0
	    THEN 'COUNTRY'
            WHEN GROUPING_ID(a.group_id,a.resource_id,b.parent_id)=0
            THEN 'ALL'
	    END)   dim_id
     ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 0 and 2
         THEN a.leads_open
         ELSE 0
         END)  age_3_below
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 3 and 7
         THEN a.leads_open
    	 ELSE 0
         END)  age_3_to_7
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 8 and 14
         THEN a.leads_open
         ELSE 0
         END)  age_8_to_14
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 15 and 21
         THEN a.leads_open
         ELSE 0
         END)  age_15_to_21
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 22 and 28
         THEN a.leads_open
         ELSE 0
         END)  age_22_to_28
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 29 and 35
         THEN a.leads_open
         ELSE 0
         END)  age_29_to_35
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 36 and 42
         THEN a.leads_open
         ELSE 0
         END)  age_36_to_42
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) > 42
         THEN a.leads_open
         ELSE 0
         END)  age_42_to_above
     ,0 c_days
     ,0 p_days
     ,0 c_leads_open
     ,0 p_leads_open
     ,0 c_adays
     ,0 p_adays
     ,0 c_aleads_open
     ,0 p_aleads_open
FROM
     bim_i_lp_gh_sum_mv a
    ,ENI.ENI_DENORM_HIERARCHIES b
    ,INV.MTL_DEFAULT_CATEGORY_SETS d
WHERE
      lead_creation_date >= trunc(sysdate-365)
    AND a.product_category_id = b.child_id
    AND b.object_type = 'CATEGORY_SET'
    AND b.object_id = d.category_set_id
    AND b.dbi_flag = 'Y'
    AND d.functional_area_id = 11
GROUP BY
     grouping sets(
     (a.group_id,a.resource_id,b.parent_id),
     (a.group_id,a.resource_id,b.parent_id,a.channel_code),
     (a.group_id,a.resource_id,b.parent_id,a.cust_category),
     (a.group_id,a.resource_id,b.parent_id,a.lead_rank_id),
     (a.group_id,a.resource_id,b.parent_id,a.lead_source),
     (a.group_id,a.resource_id,b.parent_id,a.lead_country)
     )
UNION ALL
SELECT
     2 umark
     ,a.group_id
     ,a.resource_id
     ,a.product_category_id
     ,a.channel_code
    ,a.cust_category
    ,a.lead_rank_id
    ,a.lead_source
    ,a.lead_country
     ,a.item_id
     ,a.organization_id
     ,(CASE WHEN GROUPING_ID(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.channel_code)=0
       THEN 'CHANNEL'
       WHEN GROUPING_ID(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.cust_category)=0
       THEN 'CUSTCAT'
       WHEN GROUPING_ID(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.lead_rank_id)=0
       THEN 'QUALITY'
       WHEN GROUPING_ID(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.lead_source)=0
       THEN 'SOURCE'
       WHEN GROUPING_ID(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.lead_country)=0
       THEN 'COUNTRY'
       WHEN GROUPING_ID(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id)=0
       THEN 'ALL'
       END
       ) dim_id
     ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 0 and 3
         THEN a.leads_open
         ELSE 0
         END)  age_3_below
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 3 and 7
         THEN a.leads_open
    	 ELSE 0
         END)  age_3_to_7
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 7 and 14
         THEN a.leads_open
         ELSE 0
         END)  age_8_to_14
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 14 and 21
         THEN a.leads_open
         ELSE 0
         END)  age_15_to_21
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 21 and 28
         THEN a.leads_open
         ELSE 0
         END)  age_22_to_28
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 28 and 35
         THEN a.leads_open
         ELSE 0
         END)  age_29_to_35
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) between 35 and 42
         THEN a.leads_open
         ELSE 0
         END)  age_36_to_42
    ,sum(CASE
         WHEN (trunc(sysdate) - a.lead_creation_date) > 42
         THEN a.leads_open
         ELSE 0
         END)  age_42_to_above
     ,0 c_days
     ,0 p_days
     ,0 c_leads_open
     ,0 p_leads_open
     ,0 c_adays
     ,0 p_adays
     ,0 c_aleads_open
     ,0 p_aleads_open
FROM
     bim_i_lp_gh_sum_mv a
WHERE
      lead_creation_date >= trunc(sysdate-365)
GROUP BY
grouping sets(
(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id),
(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.channel_code),
(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.cust_category),
(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.lead_rank_id),
(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.lead_source),
(a.group_id,a.resource_id,a.product_category_id,a.item_id,a.organization_id,a.lead_country)
)
UNION ALL
SELECT
     3 umark
     ,group_id
     ,resource_id
     ,c.parent_id                     product_category_id
     ,null channel_code
     ,null cust_category
     ,null lead_rank_id
     ,null lead_source
     ,null lead_country
     ,null item_id
     ,null organization_id
     ,null dim_id
     ,0 age_3_below
     ,0 age_3_to_7
     ,0 age_8_to_14
     ,0 age_15_to_21
     ,0 age_22_to_28
     ,0 age_29_to_35
     ,0 age_36_to_42
     ,0 age_42_to_above
     ,sum(CASE
          WHEN a.lead_creation_date between trunc(sysdate-365) and trunc(sysdate)
          THEN (trunc(sysdate)-a.lead_creation_date)*leads_open
          ELSE 0
          END) c_days
     ,sum(CASE
          WHEN a.lead_creation_date between trunc(sysdate-730) and trunc(sysdate-365)
          THEN (trunc(sysdate-365)-a.lead_creation_date)*leads_open
          ELSE 0
          END) p_days
     ,sum(CASE when a.lead_creation_date between trunc(sysdate-365) and trunc(sysdate)
          THEN leads_open
          ELSE 0
          END) c_leads_open
     ,sum(CASE when a.lead_creation_date between trunc(sysdate-730) and trunc(sysdate-365)
          THEN leads_open
          ELSE 0
          END) p_leads_open
     ,sum(CASE when b.column_name='A' and a.lead_creation_date between trunc(sysdate-365) and trunc(sysdate)
          THEN(trunc(sysdate)-a.lead_creation_date)*leads_open
          ELSE null
          END) c_adays
     ,sum(CASE when b.column_name='A' and a.lead_creation_date between trunc(sysdate-730) and trunc(sysdate-365)
          THEN(trunc(sysdate-365)-a.lead_creation_date)*leads_open
          ELSE null
          END) p_adays
     ,sum(CASE when b.column_name='A' and a.lead_creation_date between trunc(sysdate-365) and trunc(sysdate)
          THEN leads_open
          ELSE null
          END) c_aleads_open
     ,sum(CASE when b.column_name='A' and a.lead_creation_date between trunc(sysdate-730) and trunc(sysdate-365)
          THEN leads_open
          ELSE null
          END) p_aleads_open
FROM bim_i_lp_gh_sum_mv a
    , bim_r_code_definitions b
    ,ENI.ENI_DENORM_HIERARCHIES c
    ,INV.MTL_DEFAULT_CATEGORY_SETS d
WHERE a.lead_creation_date >= trunc(sysdate-730)
      AND b.object_type(+)='RANK_DBI'
      AND a.lead_rank_id=b.object_def(+)
      AND a.product_category_id = c.child_id
      AND c.object_type = 'CATEGORY_SET'
      AND c.object_id = d.category_set_id
      AND c.dbi_flag = 'Y'
      AND d.functional_area_id = 11
GROUP BY
     a.group_id
    ,a.resource_id
    ,c.parent_id