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_OPLPC_MV

Source


SELECT /* 12.0: bug#4526784 */ /* bug4654692 */
      'LDCNTS' umarker,
      TO_NUMBER(TO_CHAR(fact.transaction_create_date,'J')) effective_time_id,
      group_id  sales_group_id,
      resource_id salesrep_id,
      product_category_id,
      NVL(source_code_id, -1) source_code_id,
      SUM(CASE WHEN leads_new=0 THEN NULL ELSE leads_new END) new_leads_cnt,
      SUM(CASE WHEN leads_converted=0 THEN NULL ELSE leads_converted END) cnv_leads_cnt,
      SUM(CASE WHEN leads_dead=0 THEN NULL ELSE leads_dead END) dead_leads_cnt,
      SUM(CASE WHEN leads_closed=0 THEN NULL ELSE leads_closed END) closed_leads_cnt,
      NULL  new_opty_amt,
      NULL  cnv_opty_amt,
      NULL  won_opty_amt,
      NULL  lost_opty_amt,
      NULL  no_opty_amt,
      NULL  new_opty_amt_s,
      NULL  cnv_opty_amt_s,
      NULL  won_opty_amt_s,
      NULL  lost_opty_amt_s,
      NULL  no_opty_amt_s,
      NULL  new_opty_xcnt,
      NULL  cnv_opty_xcnt,
      NULL  won_opty_xcnt,
      NULL  lost_opty_xcnt,
      NULL  no_opty_xcnt,
      NULL  new_opty_xcnt_s,
      NULL  cnv_opty_xcnt_s,
      NULL  won_opty_xcnt_s,
      NULL  lost_opty_xcnt_s,
      NULL  no_opty_xcnt_s,
      COUNT(CASE WHEN leads_new=0 THEN NULL ELSE leads_new END) new_leads_xcnt,
      COUNT(CASE WHEN leads_converted=0 THEN NULL ELSE leads_converted END) cnv_leads_xcnt,
      COUNT(CASE WHEN leads_dead=0 THEN NULL ELSE leads_dead END) dead_leads_xcnt,
      COUNT(CASE WHEN leads_closed=0 THEN NULL ELSE leads_closed END) closed_leads_xcnt,
      COUNT(*) xcnt
    FROM
      bim_i_lp_base_mv fact
    WHERE
      NOT(leads_new=0 AND leads_converted=0 AND leads_dead=0 AND leads_closed=0)
      AND group_id IS NOT NULL
    GROUP BY
      group_id,
      resource_id,
      NVL(source_code_id, -1),
      TO_NUMBER(TO_CHAR(fact.transaction_create_date,'J')),
      product_category_id
    UNION ALL
    SELECT
      'NEWOPTY'  umarker,
      fact.opty_creation_time_id effective_time_id,
      sales_group_id,
      salesrep_id,
      product_category_id,
      hdr_source_promotion_id source_code_id,
      NULL  new_leads_cnt,
      NULL  cnv_leads_cnt,
      NULL  dead_leads_cnt,
      NULL  closed_leads_cnt,
      SUM(new_opty_amt) new_opty_amt,
      NULL  cnv_opty_amt,
      NULL  won_opty_amt,
      NULL  lost_opty_amt,
      NULL  no_opty_amt,
      SUM(new_opty_amt_s) new_opty_amt_s,
      NULL  cnv_opty_amt_s,
      NULL  won_opty_amt_s,
      NULL  lost_opty_amt_s,
      NULL  no_opty_amt_s,
      COUNT(new_opty_amt) new_opty_xcnt,
      NULL  cnv_opty_xcnt,
      NULL  won_opty_xcnt,
      NULL  lost_opty_xcnt,
      NULL  no_opty_xcnt,
      COUNT(new_opty_amt_s) new_opty_xcnt_s,
      NULL  cnv_opty_xcnt_s,
      NULL  won_opty_xcnt_s,
      NULL  lost_opty_xcnt_s,
      NULL  no_opty_xcnt_s,
      NULL,
      NULL,
      NULL,
      NULL,
      COUNT(*) xcnt
    FROM
      bil_bi_opdtl_mv fact
    GROUP BY
      fact.opty_creation_time_id,
      sales_group_id,
      salesrep_id,
      hdr_source_promotion_id,
      product_category_id
    UNION ALL
    SELECT
      'CONVOPTY'  umarker,
      fact.opty_ld_conversion_time_id effective_time_id,
      sales_group_id,
      salesrep_id,
      product_category_id,
      hdr_source_promotion_id source_code_id,
      NULL  new_leads_cnt,
      NULL  cnv_leads_cnt,
      NULL  dead_leads_cnt,
      NULL  closed_leads_cnt,
      NULL  new_opty_amt,
      SUM(cnv_opty_amt) cnv_opty_amt,
      NULL  won_opty_amt,
      NULL  lost_opty_amt,
      NULL  no_opty_amt,
      NULL  new_opty_amt_s,
      SUM(cnv_opty_amt_s) cnv_opty_amt_s,
      NULL  won_opty_amt_s,
      NULL  lost_opty_amt_s,
      NULL  no_opty_amt_s,      
      NULL  new_opty_xcnt,
      COUNT(cnv_opty_amt)  cnv_opty_xcnt,
      NULL  won_opty_xcnt,
      NULL  lost_opty_xcnt,
      NULL  no_opty_xcnt,
      NULL  new_opty_xcnt_s,
      COUNT(cnv_opty_amt_s)  cnv_opty_xcnt_s,
      NULL  won_opty_xcnt_s,
      NULL  lost_opty_xcnt_s,
      NULL  no_opty_xcnt_s,
      NULL,
      NULL,
      NULL,
      NULL,
      COUNT(*)  xcnt
    FROM
      bil_bi_opdtl_mv fact
    WHERE 
      fact.opty_ld_conversion_time_id IS NOT NULL
    GROUP BY
      fact.opty_ld_conversion_time_id,
      sales_group_id ,
      salesrep_id,
       hdr_source_promotion_id,
      product_category_id
    UNION ALL
    SELECT
      'CLSDOPTY'  umarker,
      fact.opty_close_time_id effective_time_id,
      sales_group_id,
      salesrep_id,
      product_category_id,
       hdr_source_promotion_id source_code_id,
     NULL  new_leads_cnt,
      NULL  cnv_leads_cnt,
      NULL  dead_leads_cnt,
      NULL  closed_leads_cnt,
      NULL  new_opty_amt,
      NULL  cnv_opty_amt,
      SUM(won_opty_amt) won_opty_amt,
      SUM(lost_opty_amt)  lost_opty_amt,
      SUM(no_opty_amt) no_opty_amt,
      NULL  new_opty_amt,
      NULL  cnv_opty_amt,
      SUM(won_opty_amt_s) won_opty_amt_s,
      SUM(lost_opty_amt_s)  lost_opty_amt_s,
      SUM(no_opty_amt_s) no_opty_amt_s,
      NULL  new_opty_xcnt,
      NULL  cnv_opty_xcnt,
      COUNT(won_opty_amt)  won_opty_xcnt,
      COUNT(lost_opty_amt)  lost_opty_xcnt,
      COUNT(no_opty_amt)  no_opty_xcnt,
      NULL  new_opty_xcnt_s,
      NULL  cnv_opty_xcnt_s,
      COUNT(won_opty_amt_s)  won_opty_xcnt_s,
      COUNT(lost_opty_amt_s) lost_opty_xcnt_s,
      COUNT(no_opty_amt_s)  no_opty_xcnt_s,
      NULL,
      NULL,
      NULL,
      NULL,
      COUNT(*)  xcnt
    FROM
      bil_bi_opdtl_mv fact
    WHERE
      NOT(won_opty_amt IS NULL AND lost_opty_amt IS NULL AND no_opty_amt IS NULL)
    GROUP BY
      fact.opty_close_time_id,
      sales_group_id,
      salesrep_id,
      hdr_source_promotion_id,
      product_category_id