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.

APPS.ENI_DBI_COR_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 58


  l_select   VARCHAR2(100);

Line: 59


  l_select_id   VARCHAR2(100);

Line: 74


  l_select_id_list  VARCHAR2(1000);

Line: 158


      'SELECT null as VIEWBY,
        null as ENI_MEASURE64,
        null as ENI_MEASURE1,
        null as ENI_MEASURE12,
        null as ENI_MEASURE6,
        null as ENI_MEASURE2,
        null as ENI_MEASURE3,
        null as ENI_MEASURE7,
        null as ENI_MEASURE4,
        null as ENI_MEASURE5,
        null as ENI_MEASURE40,
        null as ENI_MEASURE41,
        null as ENI_MEASURE42,
        null as ENI_MEASURE9,
        null as ENI_MEASURE13,
        null as ENI_MEASURE8,
        null as ENI_MEASURE10,
        null as ENI_MEASURE14,
        null as ENI_MEASURE15,
        null as ENI_MEASURE27,
        null as ENI_MEASURE28,
        null as ENI_MEASURE16,
        null as ENI_MEASURE17,
        null as ENI_MEASURE20,
        null as ENI_MEASURE18,
        null as ENI_MEASURE21,
        null as ENI_MEASURE22,
        null as ENI_MEASURE23,
        null as ENI_MEASURE24,
        null as ENI_MEASURE25,
        null as ENI_MEASURE26,
        null as ENI_MEASURE43,
        null as ENI_MEASURE44,
        null as ENI_MEASURE45,
        null as ENI_MEASURE30,
        null as ENI_MEASURE31,
        null as ENI_MEASURE35,
        null as ENI_MEASURE36,
        null as ENI_MEASURE37,
        null as ENI_MEASURE38,
        null as ENI_MEASURE39,
 null as ENI_MEASURE47,
 null as ENI_MEASURE48,
 null as ENI_MEASURE49,
 null as ENI_MEASURE50,
 null as ENI_MEASURE56,
 null as ENI_MEASURE57,
 null as ENI_MEASURE58,
 null as ENI_MEASURE59,
 null as ENI_MEASURE61,
 null as ENI_MEASURE62,
 null as ENI_MEASURE63,
 null as ENI_MEASURE53,
 null as ENI_MEASURE54
 FROM DUAL';

Line: 327


SELECT
  s.name AS VIEWBY
, NULL AS ENI_MEASURE64
, c.C_OPEN_SUM AS ENI_MEASURE1
, nvl(c.C_OPEN_SUM,0) AS ENI_MEASURE12
, c.P_OPEN_SUM AS ENI_MEASURE6
, (((c.C_OPEN_SUM - c.P_OPEN_SUM)
   /DECODE(c.P_OPEN_SUM,0,NULL,c.P_OPEN_SUM))* 100)
  AS ENI_MEASURE2
, (c.c_avg_age/DECODE(c.c_open_sum,0,NULL,c.c_open_sum))
  AS ENI_MEASURE3
, (c.p_avg_age/DECODE(c.p_open_sum,0,NULL,c.p_open_sum))
  AS ENI_MEASURE7
, ((((c.c_avg_age/DECODE(c.c_open_sum,0,NULL,c.c_open_sum))
    -(c.p_avg_age/DECODE(c.p_open_sum,0,NULL,c.p_open_sum)))
   /DECODE((c.p_avg_age
            /DECODE(c.p_open_sum,0,NULL,c.p_open_sum))
            ,0,NULL,
            (c.p_avg_age/DECODE(c.p_open_sum,0,NULL,c.p_open_sum)))) * 100)
  AS ENI_MEASURE4
, c.c_bucket1 AS ENI_MEASURE5
, c.c_bucket2 AS ENI_MEASURE40
, c.c_bucket3 AS ENI_MEASURE41
, c.c_bucket4 AS ENI_MEASURE42
, s.C_NEW_SUM AS ENI_MEASURE9
, nvl(s.C_NEW_SUM,0) AS ENI_MEASURE13
, s.P_NEW_SUM AS ENI_MEASURE8
, (((s.C_NEW_SUM - s.P_NEW_SUM)/DECODE(s.P_NEW_SUM,0,NULL,s.P_NEW_SUM))*100)
  AS ENI_MEASURE10
, s.C_CANL_SUM AS ENI_MEASURE14
, s.C_IMPL_SUM AS ENI_MEASURE15
, s.P_CANL_SUM AS ENI_MEASURE27
, s.P_CANL_SUM AS ENI_MEASURE28
, ((((s.C_CANL_SUM+s.C_IMPL_SUM)-(s.P_CANL_SUM+s.P_IMPL_SUM))
   /DECODE((s.P_CANL_SUM+s.P_IMPL_SUM),0,NULL,(s.P_CANL_SUM+s.P_IMPL_SUM))) * 100)
  AS ENI_MEASURE16
, s.C_CYCL_SUM/DECODE(s.C_CYCL_CNT,0,NULL,s.C_CYCL_CNT)
  AS ENI_MEASURE17
, s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT)
  AS ENI_MEASURE20
, ((((s.C_CYCL_SUM/DECODE(s.C_CYCL_CNT,0,NULL,s.C_CYCL_CNT)
     )
     -(s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT))
    )
    /DECODE((s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT)),0,NULL,
            (s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT)))
   )*100
  ) AS ENI_MEASURE18
, NVL(s.C_CANL_SUM,0)+NVL(s.C_IMPL_SUM,0) AS ENI_MEASURE21
, NULL AS ENI_MEASURE22
, NULL AS ENI_MEASURE23
, NULL AS ENI_MEASURE24
, NULL AS ENI_MEASURE25
, NULL AS ENI_MEASURE26
, NULL AS ENI_MEASURE43
, NULL AS ENI_MEASURE44
, NULL AS ENI_MEASURE45
, NULL AS ENI_MEASURE30
, NULL AS ENI_MEASURE31
, NULL AS ENI_MEASURE35
, NULL AS ENI_MEASURE36
, NULL AS ENI_MEASURE37
, NULL AS ENI_MEASURE38
, NULL AS ENI_MEASURE39
, NULL AS ENI_MEASURE47
, NULL AS ENI_MEASURE48
, NULL AS ENI_MEASURE49
, NULL AS ENI_MEASURE50
, NULL AS ENI_MEASURE56
, NULL AS ENI_MEASURE57
, NULL AS ENI_MEASURE58
, NULL as ENI_MEASURE59
, NULL as ENI_MEASURE61
, NULL as ENI_MEASURE62
, NULL as ENI_MEASURE63
, NULL as ENI_MEASURE53
, NULL as ENI_MEASURE54
FROM
 (SELECT
    t.name as name,t.start_date,t.c_end_date,t.c_end_date as time_id
  , SUM(CASE WHEN ftrs.report_date = t.c_end_date
             THEN edcs.new_sum
             ELSE null
        END)
    AS C_NEW_SUM
  , SUM(CASE WHEN ftrs.report_date = t.p_end_date
             THEN edcs.new_sum
             ELSE 0
        END)
    AS P_NEW_SUM
  , SUM(CASE WHEN ftrs.report_date = t.c_end_date
             THEN edcs.implemented_sum
             ELSE null
        END)
    AS C_IMPL_SUM
  , SUM(CASE WHEN ftrs.report_date = t.p_end_date
             THEN edcs.implemented_sum
             ELSE 0
        END)
    AS P_IMPL_SUM
  , SUM(CASE WHEN ftrs.report_date = t.c_end_date
             THEN edcs.cancelled_sum
             ELSE null
        END)
    AS C_CANL_SUM
  , SUM(CASE WHEN ftrs.report_date = t.p_end_date
             THEN edcs.cancelled_sum
             ELSE 0
        END)
    AS P_CANL_SUM
  , SUM(CASE WHEN ftrs.report_date = t.c_end_date
             THEN edcs.cycle_time_sum
             ELSE 0
        END)
    AS C_CYCL_SUM
  , SUM(CASE WHEN ftrs.report_date = t.p_end_date
             THEN edcs.cycle_time_sum
             ELSE 0
        END)
    AS P_CYCL_SUM
  , SUM(CASE WHEN ftrs.report_date = t.c_end_date
             THEN edcs.cycle_time_cnt
             ELSE 0
        END)
    AS C_CYCL_CNT
  , SUM(CASE WHEN ftrs.report_date = t.p_end_date
             THEN edcs.cycle_time_cnt
             ELSE 0
        END)
    AS P_CYCL_CNT
  FROM eni_dbi_co_sum_mv edcs
     , '|| l_from_clause ||
' WHERE '|| l_where_clause ||
' GROUP BY ' || l_group_by_clause||'
 ) s';

Line: 482


, (SELECT
     t.name,t.c_end_date AS time_id
   , SUM(CASE WHEN
         (NVL(edcs.cancellation_date, NVL(edcs.implementation_date,:NVL_DATE)
             ) >  t.c_end_date
         ) AND edcs.creation_date <= t.c_end_date
         THEN edcs.cnt ELSE null
         END) AS C_OPEN_SUM
   , SUM(CASE WHEN
         (NVL(edcs.cancellation_date, NVL(edcs.implementation_date,:NVL_DATE)
             ) >  t.p_end_date
         ) AND edcs.creation_date <= t.p_end_date
         THEN edcs.cnt ELSE 0
         END) AS P_OPEN_SUM
   , SUM(CASE WHEN
         NVL(edcs.cancellation_date,
             NVL(edcs.implementation_date,:NVL_DATE)
            ) >  t.c_end_date
         AND edcs.creation_date <= t.c_end_date
         THEN
         (CASE WHEN
          (LEAST(NVL(edcs.cancellation_date,
                     NVL(edcs.implementation_date,t.c_end_date)
                    ), t.c_end_date) - edcs.creation_date)
          BETWEEN 0 AND 1
          THEN edcs.CNT ELSE null END)
         ELSE null END)
     AS c_bucket1
   , SUM(CASE WHEN
         NVL(edcs.cancellation_date,
             NVL(edcs.implementation_date,:NVL_DATE)
            ) >  t.c_end_date
         AND edcs.creation_date <= t.c_end_date
         THEN
         (CASE WHEN
          (LEAST(NVL(edcs.cancellation_date,
                     NVL(edcs.implementation_date,t.c_end_date)
                    ), t.c_end_date) - edcs.creation_date)
          BETWEEN 2 AND 5
          THEN edcs.cnt ELSE null END)
         ELSE null END)
     AS c_bucket2
   , SUM(CASE WHEN
         NVL(edcs.cancellation_date,
             NVL(edcs.implementation_date,:NVL_DATE)
            ) >  t.c_end_date
         AND edcs.creation_date <= t.c_end_date
         THEN
         (CASE WHEN
          (LEAST(NVL(edcs.cancellation_date,
                     NVL(edcs.implementation_date,t.c_end_date)
                    ), t.c_end_date ) - edcs.creation_date)
          BETWEEN 6 and 10
          THEN edcs.cnt ELSE null END)
         ELSE null END)
     AS c_bucket3
   , SUM(CASE WHEN
         NVL(edcs.cancellation_date,
             NVL(edcs.implementation_date,:NVL_DATE)
            ) >  t.c_end_date
         AND edcs.creation_date <= t.c_end_date
         THEN
         (CASE WHEN
          (LEAST(NVL(edcs.cancellation_date,
                     NVL(edcs.implementation_date,t.c_end_date)
                    ), t.c_end_date) - edcs.creation_date)
          > 10
          THEN edcs.cnt ELSE null END)
         ELSE null END)
     AS c_bucket4
   , SUM(CASE WHEN
         NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          > t.c_end_date
          AND edcs.creation_date <= t.c_end_date
         THEN
         ((t.c_end_date - edcs.creation_date) * edcs.cnt)
         ELSE 0 END)
     AS c_avg_age
   , SUM(CASE WHEN
         NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          > t.p_end_date
          AND edcs.creation_date <= t.p_end_date
         THEN
         ((t.p_end_date - edcs.creation_date) * edcs.cnt)
         ELSE 0 END)
     AS p_avg_age
   FROM eni_dbi_co_dnum_mv edcs
      , '|| l_from_clause || '
   WHERE
    1 = 1
  AND
  edcs.creation_date <= &BIS_CURRENT_ASOF_DATE
  ' || l_where_clause_dn || '
   GROUP BY
    ' || l_group_by_clause || '
  ) c
WHERE
 s.time_id = c.time_id(+)
ORDER BY '||l_order_by;

Line: 597


      l_select  := 'ecmt.value';

Line: 598


      l_select_id := l_group_by;

Line: 599


      l_select_id_list := l_group_by || ' as viewby_id ';

Line: 602


      l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';

Line: 609


      l_select := 'ecms.value';

Line: 610


      l_select_id := l_group_by;

Line: 611


      l_select_id_list := l_group_by || ' as viewby_id ';

Line: 614


      l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';

Line: 622


      l_select := 'ecmr.value';

Line: 623


      l_select_id := l_group_by;

Line: 624


      l_select_id_list := l_group_by || ' as viewby_id ';

Line: 627


      l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';

Line: 634


      l_select:=' ecmp.value ';

Line: 635


      l_select_id := l_group_by;

Line: 636


      l_select_id_list := l_group_by || ' as viewby_id ';

Line: 639


      l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';

Line: 653


 l_select := 'ecmp.value ';

Line: 654


 l_select_id := l_group_by;

Line: 655


 l_select_id_list := 'edcs.item_id  as viewby_id , edcs.organization_id as org_viewby_id ';

Line: 659


 l_outer_group_by:= l_select ||' , viewby_id , ecmp.description ,'|| l_lookup_alias || '.id';

Line: 670


SELECT ' || l_select || '
  AS VIEWBY
,  '||l_description||'
, '|| l_lookup_alias || '.id AS VIEWBYID
, SUM(c_open_sum) AS ENI_MEASURE1
, SUM(c_open_sum) AS ENI_MEASURE12
, SUM(p_open_sum) AS ENI_MEASURE6
, ((SUM(c_open_sum)-SUM(p_open_sum))
   /DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum))
  ) * 100
  AS ENI_MEASURE2
, SUM(c_open_days_sum)
  /DECODE(SUM(c_open_sum),0,NULL,SUM(c_open_sum))
  AS ENI_MEASURE3
, SUM(p_open_days_sum)
  /DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum))
  AS ENI_MEASURE7
, (((SUM(c_open_days_sum)
     /DECODE(SUM(c_open_sum),0,NULL,SUM(c_open_sum))
    )
    -(SUM(p_open_days_sum)/DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum)))
   )
   /(DECODE(SUM(p_open_days_sum),0,NULL,SUM(p_open_days_sum))/DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum)))
  ) * 100
  AS ENI_MEASURE4
, SUM(c_bucket1) AS ENI_MEASURE5
, SUM(c_bucket2) AS ENI_MEASURE40
, SUM(c_bucket3) AS ENI_MEASURE41
, SUM(c_bucket4) AS ENI_MEASURE42
, SUM(c_new_sum) AS ENI_MEASURE9
, SUM(c_new_sum) AS ENI_MEASURE13
, SUM(p_new_sum) AS ENI_MEASURE8
, ((SUM(c_new_sum)-SUM(p_new_sum))
   /DECODE(SUM(p_new_sum),0,NULL,SUM(p_new_sum))
  ) * 100
  AS ENI_MEASURE10
, SUM(c_cancelled_sum)   AS ENI_MEASURE14
, SUM(c_implemented_sum) AS ENI_MEASURE15
, SUM(p_cancelled_sum)   AS ENI_MEASURE27
, SUM(p_implemented_sum) AS ENI_MEASURE28
, (((NVL(SUM(c_cancelled_sum),0)+NVL(SUM(c_implemented_sum),0))
    -(NVL(SUM(p_cancelled_sum),0)+NVL(SUM(p_implemented_sum),0))
   )
   /DECODE(
    (NVL(SUM(p_cancelled_sum),0)
     +NVL(SUM(p_implemented_sum),0)),0,NULL,
     (NVL(SUM(p_cancelled_sum),0)+NVL(SUM(p_implemented_sum),0))
   )
  ) * 100
  AS ENI_MEASURE16
, SUM(c_cycle_time_sum)
  /DECODE(SUM(c_cycle_time_cnt),0,NULL,SUM(c_cycle_time_cnt))
  AS ENI_MEASURE17
, SUM(p_cycle_time_sum)
  /DECODE(SUM(p_cycle_time_cnt),0,NULL,SUM(p_cycle_time_cnt))
  AS ENI_MEASURE20
, (((SUM(c_cycle_time_sum)
     /DECODE(SUM(c_cycle_time_cnt),0,NULL,SUM(c_cycle_time_cnt))
    )
    -(SUM(p_cycle_time_sum)
      /DECODE(SUM(p_cycle_time_cnt),0,NULL,SUM(p_cycle_time_cnt))
     )
   )/(DECODE(SUM(p_cycle_time_sum),0,NULL,SUM(p_cycle_time_sum))
    /DECODE(SUM(p_cycle_time_cnt),0,NULL,SUM(p_cycle_time_cnt))
   )
  ) * 100
  AS ENI_MEASURE18
, NVL(SUM(c_cancelled_sum),0)+NVL(SUM(c_implemented_sum),0) AS ENI_MEASURE21
, SUM(SUM(c_open_sum)) OVER()
  AS ENI_MEASURE22
, (((SUM(SUM(c_open_sum)) OVER())-(SUM(SUM(p_open_sum)) OVER()))
   /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER())
  ) * 100
  AS ENI_MEASURE23
, ((SUM(SUM(c_open_days_sum)) OVER())
   /DECODE(SUM(SUM(c_open_sum)) OVER(),0,NULL,SUM(SUM(c_open_sum)) OVER())
  )
  AS ENI_MEASURE24
, ((((SUM(SUM(c_open_days_sum)) OVER())
     /DECODE(SUM(SUM(c_open_sum)) OVER(),0,NULL,SUM(SUM(c_open_sum)) OVER()))
    -((SUM(SUM(p_open_days_sum)) OVER())
      /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER()))
   )
   /DECODE(((SUM(SUM(p_open_days_sum)) OVER())
            /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER()))
           ,0,NULL,
           ((SUM(SUM(p_open_days_sum)) OVER())
            /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER()))
          )
  ) * 100
  AS ENI_MEASURE25
, SUM(SUM(c_bucket1)) OVER() as ENI_MEASURE26
, SUM(SUM(c_bucket2)) OVER() as ENI_MEASURE43
, SUM(SUM(c_bucket3)) OVER() as ENI_MEASURE44
, SUM(SUM(c_bucket4)) OVER() as ENI_MEASURE45
, SUM(SUM(c_new_sum)) OVER() as ENI_MEASURE30
, ((SUM(SUM(c_new_sum)) OVER()-SUM(SUM(p_new_sum)) OVER())
   /DECODE(SUM(SUM(p_new_sum)) OVER(),0,NULL,SUM(SUM(p_new_sum)) OVER())
  ) * 100
  AS ENI_MEASURE31
, SUM(SUM(c_cancelled_sum)) OVER() as ENI_MEASURE35
, SUM(SUM(c_implemented_sum)) OVER() as ENI_MEASURE36
, (((NVL(SUM(SUM(c_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(c_implemented_sum)) OVER(),0))
    -(NVL(SUM(SUM(p_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(p_implemented_sum)) OVER(),0))
   )
   /DECODE(
           (NVL(SUM(SUM(p_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(p_implemented_sum)) OVER(),0)),0,NULL,
           (NVL(SUM(SUM(p_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(p_implemented_sum)) OVER(),0))
          )
  ) * 100
  AS ENI_MEASURE37
, SUM(SUM(c_cycle_time_sum)) OVER()
  /DECODE(SUM(SUM(c_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(c_cycle_time_cnt)) OVER())
  AS ENI_MEASURE38
, (((SUM(SUM(c_cycle_time_sum)) OVER()
     /DECODE(SUM(SUM(c_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(c_cycle_time_cnt)) OVER())
    )
    -
    (
     SUM(SUM(p_cycle_time_sum)) OVER()
     /DECODE(SUM(SUM(p_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(p_cycle_time_cnt)) OVER())
    )
   )
   /DECODE(
     (SUM(SUM(p_cycle_time_sum)) OVER()
      /DECODE(SUM(SUM(p_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(p_cycle_time_cnt)) OVER())
     )
     ,0,NULL,
     (SUM(SUM(p_cycle_time_sum)) OVER()
      /DECODE(SUM(SUM(p_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(p_cycle_time_cnt)) OVER()))
    )
  ) * 100
  AS ENI_MEASURE39
, NULL AS ENI_MEASURE47
, NULL AS ENI_MEASURE48
, NULL AS ENI_MEASURE49
, NULL AS ENI_MEASURE50
, NULL AS ENI_MEASURE56
, NULL AS ENI_MEASURE57
, NULL AS ENI_MEASURE58
,(CASE WHEN SUM(c_open_sum) IS NULL
 OR SUM(c_open_sum)=0
 THEN NULL
 ELSE   '||l_open_url||' END) as ENI_MEASURE59
,(CASE WHEN SUM(c_new_sum) IS NULL
 OR SUM(c_new_sum)=0
 THEN NULL
 ELSE   '||l_new_url||' END) as ENI_MEASURE61
,(CASE WHEN SUM(c_implemented_sum) IS NULL
 OR SUM(c_implemented_sum)=0
 THEN NULL
 ELSE   '||l_impl_url||' END) as ENI_MEASURE62
,(CASE WHEN SUM(c_cancelled_sum) IS NULL
 OR SUM(c_cancelled_sum)=0
 THEN NULL
 ELSE   '||l_canc_url||' END) as ENI_MEASURE63
,(CASE WHEN SUM(c_open_sum) IS NULL
 OR SUM(c_open_sum)=0
 THEN NULL
 ELSE   '||l_avg_age_url||' END)  as ENI_MEASURE53
,(CASE WHEN SUM(c_implemented_sum) IS NULL
 OR SUM(c_implemented_sum)=0
 THEN NULL
 ELSE   '||l_cycle_url||' END) as ENI_MEASURE54
FROM
 ( SELECT '||l_select_id_list ||'
 , null c_open_sum
 , null p_open_sum
 , null c_open_days_sum
 , null p_open_days_sum
 , null c_bucket1
 , null c_bucket2
 , null c_bucket3
 , null c_bucket4
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
         THEN edcs.new_sum ELSE 0 END
   ) AS c_new_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
         THEN edcs.new_sum ELSE 0 END
   ) AS p_new_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
         THEN edcs.cancelled_sum ELSE 0 END
   ) AS c_cancelled_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
         THEN edcs.cancelled_sum ELSE 0 END
   ) AS p_cancelled_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
         THEN edcs.implemented_sum  ELSE 0 END
   ) AS c_implemented_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
         THEN edcs.implemented_sum ELSE 0 END
   ) AS p_implemented_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
         THEN edcs.cycle_time_sum ELSE 0 END
   ) AS c_cycle_time_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
         THEN edcs.cycle_time_cnt ELSE 0 END
   ) AS c_cycle_time_cnt
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
         THEN edcs.cycle_time_sum ELSE 0 END
   ) AS p_cycle_time_sum
 , SUM(
    CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
         THEN edcs.cycle_time_cnt ELSE 0 END
   ) AS p_cycle_time_cnt
 FROM eni_dbi_co_sum_mv edcs
    , fii_time_structures ftrs'||l_item_from_clause||'
 WHERE
  edcs.time_id = ftrs.time_id
  AND edcs.period_type_id = ftrs.period_type_id
  AND(ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
      OR ftrs.report_Date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE)
  AND BITAND(ftrs.record_type_id, :PERIODAND )=  :PERIODAND --Bug 5083876,5083652
  ' || l_where_clause || '
 GROUP BY
  ' || l_select_id || '
 UNION ALL
 SELECT '|| l_select_id_list || '
  , SUM(
     CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN edcs.cnt ELSE 0 END
    ) AS c_open_sum
  , SUM(
     CASE WHEN '|| '&' ||'BIS_PREVIOUS_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN edcs.cnt ELSE 0 END
    ) AS p_open_sum
  , SUM(
     CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date ) * edcs.cnt
          ELSE 0 END
    ) AS c_open_days_sum
  , SUM(
     CASE WHEN '|| '&' ||'BIS_PREVIOUS_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN ('|| '&' ||'BIS_PREVIOUS_ASOF_DATE - edcs.creation_date ) * edcs.cnt
          ELSE 0 END
    ) AS p_open_days_sum
  , SUM(
     CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN (CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date)
                          BETWEEN 0 AND 1
                     THEN edcs.cnt ELSE 0 END
               )
          ELSE 0 END
    ) AS c_bucket1
  , SUM(
     CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN (CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date)
                          BETWEEN 2 AND 5
                     THEN edcs.cnt ELSE 0 END
               )
          ELSE 0 END
    ) AS c_bucket2
  , SUM(
     CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN(CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date)
                         BETWEEN 6 and 10
                    THEN edcs.cnt ELSE 0 END
              )
          ELSE 0 END
    ) AS c_bucket3
  , SUM(
     CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
               AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
          THEN(CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date) > 10
                    THEN edcs.cnt ELSE 0 END
              )
          ELSE 0 END
    ) AS c_bucket4
  , null c_new_sum
  , null p_new_sum
  , null c_cancelled_sum
  , null p_cancelled_sum
  , null c_implemented_sum
  , null p_implemented_sum
  , null c_cycle_time_sum
  , null c_cycle_time_cnt
  , null p_cycle_time_sum
  , null p_cycle_time_cnt
 FROM eni_dbi_co_dnum_mv edcs'||l_item_from_clause||'
 WHERE
  (('|| '&' ||'BIS_CURRENT_ASOF_DATE >= edcs.creation_date
   AND '|| '&' ||'BIS_CURRENT_ASOF_DATE < NVL(NVL(edcs.cancellation_date, edcs.implementation_date), 1 + '|| '&' ||'BIS_CURRENT_ASOF_DATE)
  )
  OR
  ('|| '&' ||'BIS_PREVIOUS_ASOF_DATE >= edcs.creation_date
   AND '|| '&' ||'BIS_PREVIOUS_ASOF_DATE < NVL(NVL(edcs.cancellation_date, edcs.implementation_date), 1 + '|| '&' ||'BIS_PREVIOUS_ASOF_DATE)
  )) ' || l_where_clause_dn || '
 GROUP BY
  ' || l_select_id || '
 ) dnum_sum
  , '|| l_lookup || '
 WHERE
  '||l_outer_join_condition||'
 GROUP BY '||l_outer_group_by||'
 ORDER BY ' || l_order_by;