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

Source


Select /* 12.0: bug#4526784 */	f.authoring_org_id			AUTHORING_ORG_ID,
	f.resource_id					RESOURCE_ID,
	f.resource_group_id				RG_ID,
	CASE WHEN f.renewal_flag = 1
	  THEN 'REN'
	  ELSE 'NEW' END 				REN_TYPE,
	grouping_id(	f.authoring_org_id,
			f.resource_id,
			f.resource_group_id,
			CASE WHEN f.renewal_flag = 1 THEN 'REN' ELSE 'NEW' END,
			ent_qtr_id,
			ent_period_id,
			week_id,
			report_date_julian)			GRP_ID,
        decode( grouping_id(ent_qtr_id,ent_period_id,week_id,report_date_julian),
                14 ,report_date_julian,
                13 ,week_id,
                11 ,ent_period_id,
                7  ,ent_qtr_id)		TIME_ID,
        ent_qtr_id			ENT_QTR_ID,
        ent_period_id			ENT_PERIOD_ID,
        week_id				WEEK_ID,
        report_date_julian DAY_ID
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range1_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range1_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET1_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range1_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range1_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET1_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range1_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range1_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET1_amt_f
   , SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range2_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range2_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET2_amt_g
   , SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range2_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range2_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET2_amt_sg
   , SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range2_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range2_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET2_amt_f
 ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range3_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range3_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET3_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range3_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range3_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET3_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range3_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range3_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET3_amt_f
 ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range4_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range4_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET4_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range4_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range4_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET4_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range4_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range4_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET4_amt_f
 ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range5_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range5_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET5_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range5_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range5_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET5_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range5_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range5_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET5_amt_f
  ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range6_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range6_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET6_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range6_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range6_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET6_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range6_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range6_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET6_amt_f
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range7_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range7_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET7_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range7_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range7_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET7_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range7_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range7_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET7_amt_f
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range8_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range8_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET8_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range8_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range8_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET8_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range8_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range8_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET8_amt_f
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range9_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range9_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET9_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range9_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range9_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET9_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range9_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range9_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET9_amt_f
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range10_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range10_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET10_amt_g
   ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range10_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range10_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET10_amt_sg
    ,SUM(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range10_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range10_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET10_amt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range1_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range1_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET1_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range1_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range1_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET1_cnt_sg
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range1_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range1_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET1_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range2_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range2_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET2_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range2_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range2_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET2_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range2_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range2_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET2_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range3_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range3_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET3_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range3_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range3_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET3_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range3_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range3_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET3_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range4_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range4_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET4_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range4_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range4_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET4_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range4_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range4_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET4_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range5_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range5_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET5_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range5_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range5_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET5_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range5_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range5_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET5_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range6_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range6_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET6_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range6_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range6_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET6_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range6_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range6_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET6_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range7_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range7_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET7_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range7_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range7_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET7_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range7_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range7_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET7_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range8_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range8_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET8_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range8_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range8_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET8_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range8_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range8_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET8_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range9_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range9_high, 9999999)
              THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g END) BUCKET9_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range9_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range9_high, 9999999)
              THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET9_cnt_sg
  , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range9_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range9_high, 9999999)
              THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f END) BUCKET9_cnt_f
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range10_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range10_high, 9999999)
          THEN price_negotiated_g + ubt_amt_g + supp_credit_g + credit_amt_g  END) BUCKET10_cnt_g
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range10_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range10_high, 9999999)
          THEN price_negotiated_sg + ubt_amt_sg + supp_credit_sg + credit_amt_sg END) BUCKET10_cnt_sg
   , COUNT(CASE WHEN  (f.date_signed - f.hstart_date) >= bkt.range10_low
                AND (f.date_signed - f.hstart_date) <  nvl(bkt.range10_high, 9999999)
          THEN price_negotiated_f + ubt_amt_f + supp_credit_f + credit_amt_f  END) BUCKET10_cnt_f
, COUNT(*) AS c_total FROM     OKI.OKI_DBI_CLE_B f
        , FII.FII_TIME_DAY fii
	, BIS.BIS_BUCKET_CUSTOMIZATIONS	bkt
        , BIS.BIS_BUCKET b
WHERE   f.application_id = 515
  AND   f.buy_or_sell = 'S'
  AND   f.scs_code IN ('SERVICE','WARRANTY')
  AND   f.root_lty_code IN ('SERVICE','WARRANTY','EXT_WARRANTY')
  AND   f.term_flag = 1
  AND   f.gsd_flag = 1
/*  AND   f.renewal_flag = 1 AND f.p_cle_id IS NOT NULL */
  AND (
        (renewal_flag = 1 AND (p_cle_id IS NOT NULL OR nvl(falsernwlyn,'N') = 'Y' ) )
      )
  AND	trunc(f.date_signed) = fii.report_date
  AND   b.bucket_id = bkt.bucket_id
  AND  	b.short_name = 'OKI_DBI_SRM_LATE_AGING'
  AND	trunc(f.start_date) < fii.report_date
  AND 	abs(f.price_negotiated_g) + abs(f.credit_amt_g) + abs(f.ubt_amt_g) + abs(f.supp_credit_g) +
    abs(f.p_price_negotiated_g) + abs(f.p_credit_amt_g) + abs(f.p_ubt_amt_g) + abs(f.p_supp_credit_g) > 0
GROUP BY f.authoring_org_id,
	f.resource_id,
	f.resource_group_id,
	case when f.renewal_flag = 1 THEN 'REN' ELSE 'NEW' END,
	grouping sets(ent_qtr_id, ent_period_id, week_id,report_date_julian)