MATERIALIZED VIEW: APPS.BIM_LD_CAMP_T_MV

Source

SELECT /* Dummy Comment */
     1                               umark
    ,a.group_id                      group_id
    ,a.resource_id                   resource_id
    ,a.source_code_id                source_code_id
      ,(CASE
      WHEN GROUPING(b.report_date_julian)=0 THEN b.report_date_julian
      WHEN GROUPING(b.week_id)=0            THEN b.week_id
      WHEN GROUPING(b.ent_period_id)=0      THEN b.ent_period_id
      WHEN GROUPING(b.ent_qtr_id)=0         THEN b.ent_qtr_id
      ELSE b.ent_year_id
      END
     )                               time_id
      ,(CASE
      WHEN GROUPING(b.report_date_julian)=0 THEN 1
      WHEN GROUPING(b.week_id)=0            THEN 16
      WHEN GROUPING(b.ent_period_id)=0      THEN 32
      WHEN GROUPING(b.ent_qtr_id)=0         THEN 64
      ELSE 128
      END
     )                               period_type_id
    ,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(decode(a.lead_converted_date, null, 0,
          a.leads_converted*(a.lead_converted_date-a.lead_creation_date)))
                                     conversion_time
    ,sum(a.leads_dead)               leads_dead
    ,sum(a.leads_closed)             leads_closed
    ,sum(a.leads_qualified)          leads_qualified
    ,0                               leads_new_changed
    ,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
    ,sum(a.orders_booked_amt)        orders_booked_amt
    ,sum(a.orders_invoiced_amt)      orders_invoiced_amt
    ,sum(a.lead_amount)              lead_amount
    ,b.report_date_julian            dummy1_fr
    ,b.week_id                       dummy2_fr
    ,b.ent_period_id                 dummy3_fr
    ,b.ent_qtr_id                    dummy4_fr
    ,b.ent_year_id                   dummy5_fr
    ,0                               dummy6_fr
    ,0                               dummy7_fr
    ,0                               dummy8_fr
    ,0                               dummy9_fr
    ,GROUPING_ID(a.group_id,a.resource_id,a.source_code_id,b.report_date_julian,b.week_id,
                 b.ent_period_id,b.ent_qtr_id,b.ent_year_id) dummy10_fr
    ,count(a.leads)                  dummy11_fr
    ,count(a.leads_new)              dummy12_fr
    ,count(a.leads_open)             dummy13_fr
    ,count(a.leads_changed)          dummy14_fr
    ,count(a.leads_converted)        dummy15_fr
    ,count(decode(a.lead_converted_date, null, 0,
          a.leads_converted*(a.lead_converted_date-a.lead_creation_date)))
                                     dummy16_fr
    ,count(a.leads_dead)             dummy17_fr
    ,count(a.leads_closed)           dummy18_fr
    ,count(a.leads_qualified)        dummy19_fr
    ,0                               dummy20_fr
    ,0                               dummy21_fr
    ,count(a.rank_a)                 dummy22_fr
    ,count(a.rank_b)                 dummy23_fr
    ,count(a.rank_c)                 dummy24_fr
    ,count(a.rank_d)                 dummy25_fr
    ,count(a.rank_z)                 dummy26_fr
    ,count(a.orders_booked_amt)      dummy27_fr
    ,count(a.leads_qualified_a)      dummy28_fr
    ,count(a.orders_invoiced_amt)    dummy29_fr
    ,count(a.lead_amount)            dummy30_fr
    ,count(*)                        dummy31_fr
from
    bim_ld_camp_gh_mv a
    ,FII.FII_TIME_DAY b
WHERE   a.transaction_create_date = b.report_date
GROUP BY
     a.group_id
    ,a.resource_id
    ,a.source_code_id
    ,GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)