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.BIL_BI_OPTY_G_MV

Source


SELECT
    DECODE(GROUPING_ID(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian),
        15,time.ent_year_id,23,time.ent_qtr_id,27,time.ent_period_id,29,time.week_id,30,time.report_date_julian)
    effective_time_id
    ,DECODE(GROUPING_ID(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian),
        15,128,23,64,27,32,29,16,30,1) effective_period_type_id
    ,op22.parent_sales_group_id parent_sales_group_id
    ,op22.sales_group_id sales_group_id
    ,op22.salesrep_id
    ,SUM(new_opty_amt) new_opty_amt
    ,SUM(new_opty_amt_s) new_opty_amt_s
    ,SUM(won_opty_amt) won_opty_amt
    ,SUM(won_opty_amt_s) won_opty_amt_s
    ,SUM(lost_opty_amt) lost_opty_amt
    ,SUM(lost_opty_amt_s) lost_opty_amt_s
    ,SUM(no_opty_amt) no_opty_amt
    ,SUM(no_opty_amt_s) no_opty_amt_s
    ,SUM(cnv_opty_amt) cnv_opty_amt
    ,SUM(cnv_opty_amt_s) cnv_opty_amt_s
    ,SUM(nfp_yr) nfp_yr
    ,SUM(nfp_yr_s) nfp_yr_s
    ,SUM(nfp_qtr) nfp_qtr
    ,SUM(nfp_qtr_s) nfp_qtr_s
    ,SUM(nfp_per) nfp_per
    ,SUM(nfp_per_s) nfp_per_s
    ,SUM(nfp_wk) nfp_wk
    ,SUM(nfp_wk_s) nfp_wk_s
    ,SUM(new_opty_cnt) new_opty_cnt
    ,SUM(won_opty_cnt) won_opty_cnt
    ,SUM(lost_opty_cnt) lost_opty_cnt
    ,SUM(no_opty_cnt) no_opty_cnt
    ,SUM(cnv_opty_cnt) cnv_opty_cnt
    ,SUM(latest_open_opty_cnt) latest_open_opty_cnt
    ,COUNT(new_opty_amt) new_opty_xcnt
    ,COUNT(new_opty_amt_s) new_opty_xcnt_s
    ,COUNT(won_opty_amt) won_opty_xcnt
    ,COUNT(won_opty_amt_s) won_opty_xcnt_s
    ,COUNT(lost_opty_amt) lost_opty_xcnt
    ,COUNT(lost_opty_amt_s) lost_opty_xcnt_s
    ,COUNT(no_opty_amt) no_opty_xcnt
    ,COUNT(no_opty_amt_s) no_opty_xcnt_s
    ,COUNT(cnv_opty_amt) cnv_opty_amt_xcnt
    ,COUNT(cnv_opty_amt_s) cnv_opty_amt_xcnt_s
    ,COUNT(nfp_yr) nfp_yr_xcnt
    ,COUNT(nfp_yr_s) nfp_yr_xcnt_s
    ,COUNT(nfp_qtr) nfp_qtr_xcnt
    ,COUNT(nfp_qtr_s) nfp_qtr_xcnt_s
    ,COUNT(nfp_per) nfp_per_xcnt
    ,COUNT(nfp_per_s) nfp_per_xcnt_s
    ,COUNT(nfp_wk) nfp_wk_xcnt
    ,COUNT(nfp_wk_s) nfp_wk_xcnt_s
    ,COUNT(new_opty_cnt) new_opty_cnt_xcnt
    ,COUNT(won_opty_cnt) won_opty_cnt_xcnt
    ,COUNT(lost_opty_cnt) lost_opty_cnt_xcnt
    ,COUNT(no_opty_cnt) no_opty_cnt_xcnt
    ,COUNT(cnv_opty_cnt) cnv_opty_cnt_xcnt
    ,COUNT(latest_open_opty_cnt) latest_open_opty_cnt_xcnt
    ,COUNT(*) all_xcnt
    ,time.ent_year_id txn_yr_id
    ,time.ent_qtr_id txn_qtr_id
    ,time.ent_period_id txn_per_id
    ,time.report_date_julian txn_day_id
    ,time.week_id txn_wk_id
    ,GROUPING_ID(parent_sales_group_id,sales_group_id,salesrep_id,time.ent_year_id,time.ent_qtr_id,
      time.ent_period_id,time.week_id,time.report_date_julian)xgrp
  FROM
    bil_bi_op22_g_mv op22,
    FII.FII_TIME_DAY time
  WHERE
    time.report_date_julian = op22.effective_time_id
  GROUP BY
    op22.parent_sales_group_id
    ,op22.sales_group_id
    ,op22.salesrep_id
    ,GROUPING SETS(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian)