Tuesday, 23 February 2021

Table Function for Calendar/Date Attributes

This Blog would give us most of the information about calendar attributes or date attributes through ABAP CDS. I am involved in Group Reporting implementation where we have to deal with lot of date attributes.

Little background on Group reporting, the data from group reporting is stored in ACDOCU (Consolidation Data table) where the data can be stored as Consolidated or Pre-consolidated which differentiated by RECORD TYPE. Consolidated data will have RECORD TYPE ‘R’, and the data is stored in cumulative manner ( Balance Carry Forward). For Balance Sheet reports if we want report DEC.2020 value then if you can directly the value of last period of the year 2020 and also YTD value for the 2020 can derived by reading last period value of 2020 as Record Type R will have Balance Carry forward. But the complexity increases when you are creating or designing the P&L reports where we need to show periodic values of the each month instead of Balance Carry forward.

Below are few KPI’s where you need Date attributes.

Current Month Amount in Group Currency = Current Month Cumulative Balance - Prev Month Cumulative 

Prev Month Amount in Group Currency = Prev Month Cumul Balance - Prev to Prev Month Balance

Current Qtr Amount in Group Currency = Current Month Cumulative Balance - Prev Qtr last Month Balance

So Order to achieve all these KPI’s in CDS views we need to have Date function/attributes defined so that our KPI definitions would be easy.

As first step we need to create a table function. 

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Table Function for Calender'

define table function ZT_FISCPER

returns

{

  client                        : mandt;

  fiscalyear                    : abap.numc(4);

  period                        : abap.numc(3);

  Currentfiscper                : abap.numc(7);

  prevfiscper                   : abap.numc(7);

  prevtoprevfiscper             : abap.numc(7);

  Curryearfirstfiscper          : abap.numc(7);

  prevyearCurrentFiscper        : abap.numc(7);

  prevyearfirstperiod           : abap.numc(7);

  prevfiscperfirstfiscper       : abap.numc(7);

  prevtoprevfiscperfirstfiscper : abap.numc(7);

  prevyear                      : abap.numc(4);

  prevtoprevyear                : abap.numc(4);

  currQtrPrevYear               : abap.numc(4);

  CurrentmonthYrText            : abap.char(8);

  firstquarter                  : vdm_yearquarter;

  secondquarter                 : vdm_yearquarter;

  thirdquarter                  : vdm_yearquarter;

  fourthquarter                 : vdm_yearquarter;

  Currentquarter                : vdm_yearquarter;

  Prevquarter                   : vdm_yearquarter;

  Prevyearfirstquarter          : vdm_yearquarter;

  prevyearquarter               : vdm_yearquarter;

  prevyearlastquarter           : vdm_yearquarter;

  CurrQtrlastFiscper            : abap.numc(7);

  CurrQtrsecondFiscper          : abap.numc(7);

  CurrQtrFirstFiscper           : abap.numc(7);

  PrevQtrlastFiscper            : abap.numc(7);

  PrevQtrFirstFiscper           : abap.numc(7);

  PrevtoPrevQtrlastFiscper      : abap.numc(7);

  CurrQtrPrevYrlastFiscper      : abap.numc(7);

  PrevQtrPrevYrlastFiscper      : abap.numc(7);

  PrevtoPrevLastFiscper         : abap.numc(7);

  PrevQtryear                   : abap.numc(4);

  PrevtoPrevQtryear             : abap.numc(4);

  CurrentYearlastFiscper        : abap.numc(7);

  PrevYearlastFiscper           : abap.numc(7);

  FirstmonthofCurrentQtr        : abap.numc(7);

  FirstmonthofPrevyrCurrentQtr  : abap.numc(7);

  BeginningFIFiscper            : abap.numc(7);

  PrevYearBeginningFIFiscper    : abap.numc(7);

  CurrMinus1fiscper             : abap.numc(7);

  CurrMinus2fiscper             : abap.numc(7);

  CurrMinus3fiscper             : abap.numc(7);

  CurrMinus4fiscper             : abap.numc(7);

  CurrMinus5fiscper             : abap.numc(7);

  CurrMinus6fiscper             : abap.numc(7);

  CurrMinus7fiscper             : abap.numc(7);

  CurrMinus8fiscper             : abap.numc(7);

  CurrMinus9fiscper             : abap.numc(7);

  CurrMinus10fiscper            : abap.numc(7);

  CurrMinus11fiscper            : abap.numc(7);

  CurrMinus12fiscper            : abap.numc(7);

  PrevYrCurrMinus1fiscper       : abap.numc(7);

  PrevYrCurrMinus2fiscper       : abap.numc(7);

  PrevYrCurrMinus3fiscper       : abap.numc(7);

  PrevYrCurrMinus4fiscper       : abap.numc(7);

  PrevYrCurrMinus5fiscper       : abap.numc(7);

  PrevYrCurrMinus6fiscper       : abap.numc(7);

  PrevYrCurrMinus7fiscper       : abap.numc(7);

  PrevYrCurrMinus8fiscper       : abap.numc(7);

  PrevYrCurrMinus9fiscper       : abap.numc(7);

  PrevYrCurrMinus10fiscper      : abap.numc(7);

  PrevYrCurrMinus11fiscper      : abap.numc(7);

  PrevYrCurrMinus12fiscper      : abap.numc(7);

  CurrMinus1Year                : abap.numc(4);

  CurrMinus2Year                : abap.numc(4);

  CurrMinus3Year                : abap.numc(4);

  CurrMinus4Year                : abap.numc(4);

  CurrMinus5Year                : abap.numc(4);

  CurrMinus6Year                : abap.numc(4);

  CurrMinus7Year                : abap.numc(4);

  CurrMinus8Year                : abap.numc(4);

  CurrMinus9Year                : abap.numc(4);

  CurrMinus10Year               : abap.numc(4);

  CurrMinus11Year               : abap.numc(4);

  CurrMinus12Year               : abap.numc(4);

  PrevYrCurrMinus1Year          : abap.numc(4);

  PrevYrCurrMinus2Year          : abap.numc(4);

  PrevYrCurrMinus3Year          : abap.numc(4);

  PrevYrCurrMinus4Year          : abap.numc(4);

  PrevYrCurrMinus5Year          : abap.numc(4);

  PrevYrCurrMinus6Year          : abap.numc(4);

  PrevYrCurrMinus7Year          : abap.numc(4);

  PrevYrCurrMinus8Year          : abap.numc(4);

  PrevYrCurrMinus9Year          : abap.numc(4);

  PrevYrCurrMinus10Year         : abap.numc(4);

  PrevYrCurrMinus11Year         : abap.numc(4);

  PrevYrCurrMinus12Year         : abap.numc(4);

  CurrQtrYrinStr                : abap.char(7);

  PrevQtrYrinStr                : abap.char(7);

  CurrQtrPrYrinStr              : abap.char(7);

  LastPrdofQtr                  : abap.char(1);

}

implemented by method

  zcl_amdp_fiscper=>zfiscper

Below is the code for the AMDP method called ZFISCPER

class ZCL_AMDP_FISCPER definition

  public

  final

  create public .

  public section.

    interfaces IF_AMDP_MARKER_HDB.

    class-methods ZFISCPER for table function ZT_FISCPER.

  protected section.

  private section.

endclass.

class ZCL_AMDP_FISCPER implementation.

  method ZFISCPER

  by database function for hdb language sqlscript

  options read-only

  using SCAL_TT_DATE SEPMRAIMONTHNAME .

    I_MAIN = SELECT

             DISTINCT

              SCL.CALENDARYEAR         AS CALENDARYEAR,

              SCL.CALENDARMONTH        AS CALENDARMONTH,

              SCL.CALENDARQUARTER      AS CALENDARQUARTER,

              SCL.YEARQUARTER          AS YEARQUARTER

      FROM SCAL_TT_DATE AS SCL

      WHERE SCL.CALENDARYEAR >= '2000'

      AND SCL.CALENDARYEAR <= '2200'

      ORDER BY SCL.CALENDARYEAR;

  RETURN SELECT

   SESSION_CONTEXT( 'CLIENT' )                    AS CLIENT,

            SCL.CALENDARYEAR AS FISCALYEAR,

            CAST( CONCAT('0', SCL.CALENDARMONTH) AS NVARCHAR(3)) AS PERIOD,

             /* FISCAL PERIOD */

            CAST( CONCAT( CONCAT( SCL.CALENDARYEAR, '0'), SCL.CALENDARMONTH) as NVARCHAR(7) )   AS CURRENTFISCPER,

             /* PREV FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 1 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 1 )) AS NVARCHAR(7) )

            end                  AS PREVFISCPER,

            /*PREVIOUS TO PREV FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 2 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 2 )) AS NVARCHAR(7) ) end

            end               AS PREVTOPREVFISCPER,

             /* FISCAL PERIOD */

            CAST( CONCAT( CONCAT( SCL.CALENDARYEAR, '0'), '01') AS NVARCHAR(7) )   AS CURRYEARFIRSTFISCPER,

            /* PREV YEAR CURRENT PERIOD */

            CAST( CONCAT( concat( (SCL.CALENDARYEAR - 1), '0'), SCL.CALENDARMONTH) as NVARCHAR(7) ) AS PREVYEARCURRENTFISCPER ,

            /* PREV YEAR First PERIOD */

            CAST( CONCAT( CONCAT( (SCL.CALENDARYEAR - 1), '0'), '01') as NVARCHAR(7) ) AS PREVYEARFIRSTPERIOD,

            /* PREV FISCPER FIRST FISCPER */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '001') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )

            END                  AS PREVFISCPERFIRSTFISCPER,

            /* PREV TO PREV FISCPER FIRST FISCPER */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '001') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )

            END     AS PREVTOPREVFISCPERFIRSTFISCPER,

            /* PREV FISCAL YEAR */

            CAST((SCL.CALENDARYEAR - 1) as NVARCHAR(4) )  as PREVYEAR,

            /* PREV FISCAL YEAR */

            CAST((SCL.CALENDARYEAR - 2) as NVARCHAR(4) )  as PREVTOPREVYEAR,

            /* PREV YEAR of THE current QTR */

            case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR  - 1 ), '009') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  -1 ), '012') AS NVARCHAR(7) )

                 END END END END AS  CURRQTRPREVYEAR,

            /* CURRENT MONTH YEAR IN TEXT FORMAT */

            CONCAT(CONCAT(MONTHTEXT.MONTHSHORTNAME,'.'),SCL.CALENDARYEAR) AS CURRENTMONTHYRTEXT,

            /*FIRST QTR */

            CAST( CONCAT( SCL.CALENDARYEAR, 1) as NVARCHAR(5) ) AS FIRSTQUARTER,

            /*SECOND QTR */

            CAST( CONCAT( SCL.CALENDARYEAR, 2) as NVARCHAR(5) ) AS SECONDQUARTER,

            /*THIRD QTR */

            CAST( CONCAT( SCL.CALENDARYEAR, 3) as NVARCHAR(5) ) AS THIRDQUARTER,

            /*FOURTH QTR */

            CAST( CONCAT( SCL.CALENDARYEAR, 4) as NVARCHAR(5) ) AS FOURTHQUARTER,

            /*CURRENT QTR */

            SCL.YEARQUARTER   AS CURRENTQUARTER,

            /*PREVIOUS QUARTER */

            CASE WHEN SCL.CALENDARQUARTER = '1'

            THEN CONCAT( (SCL.CALENDARYEAR - 1),'4' )

            else CONCAT( SCL.CALENDARYEAR ,( SCL.CALENDARQUARTER - 1 ) )

            END AS PREVQUARTER,

            /*PREVIOUS YEAR QTR */

            CAST( CONCAT( (SCL.CALENDARYEAR - 1), '1') as NVARCHAR(5) ) AS PREVYEARFIRSTQUARTER,

            /*PREVIOUS YEAR QUARTER */

            CAST( CONCAT( (SCL.CALENDARYEAR - 1), SCL.CALENDARQUARTER) as NVARCHAR(5) ) AS PREVYEARQUARTER,

            /*PREVIOUS YEAR LAST QUARTER */

            CAST( CONCAT( (SCL.CALENDARYEAR - 1), '4') as NVARCHAR(5) ) AS PREVYEARLASTQUARTER,

              /*CURRENT QTR Last FISCPER */

             case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '003') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '006') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR  ), '009') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  ), '012') AS NVARCHAR(7) )

                 END END END END AS  CURRQTRLASTFISCPER,

              /*CURRENT QTR SECOND FISCPER */

             case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '002') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '005') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR  ), '007') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  ), '010') AS NVARCHAR(7) )

                 END END END END AS  CURRQTRSECONDFISCPER,

              /*CURRENT QTR FIRST FISCPER */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '004') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR  ), '007') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  ), '010') AS NVARCHAR(7) )

                 END END END END AS  CURRQTRFIRSTFISCPER,

            /*PREV QTR LAST FISCPER */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '003') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR ), '006') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  ), '009') AS NVARCHAR(7) )

                 END END END END AS  PREVQTRLASTFISCPER,

            /*PREV QTR FIRST FISCPER */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR ), '004') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  ), '007') AS NVARCHAR(7) )

                 END END END END AS  PREVQTRFIRSTFISCPER,

             /*PREV TO PREV QTR LAST FISCPER */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR ), '003') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  ), '006') AS NVARCHAR(7) )

                 END END END END AS  PREVTOPREVQTRLASTFISCPER,

              /*CURRENT QTR PREV YEAR LAST FISCPER */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR  - 1 ), '009') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR  -1 ), '012') AS NVARCHAR(7) )

                 END END END END AS  CURRQTRPREVYRLASTFISCPER,

            /*PREV QTR PREV YEAR LAST FISCPER */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

                 END END END END AS  PREVQTRPREVYRLASTFISCPER,

             /* PREV TO PREV YEAR LAST FISCPER */

             CAST( CONCAT( (SCL.CALENDARYEAR - 2),'012' ) as NVARCHAR(7) )   as PREVTOPREVLASTFISCPER,

            /*PREV QTR YEAR */

             case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END END END END AS  PREVQTRYEAR,

            /*PREV TO PREV QTR YEAR */

             CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END END END END AS  PREVTOPREVQTRYEAR,

            /* PREV 1ST PERIOD */

            CAST( CONCAT( ( SCL.CALENDARYEAR ), '012') AS NVARCHAR(7) ) AS      CURRENTYEARLASTFISCPER,

            CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) ) AS      PREVYEARLASTFISCPER,

            /*FIRST FISCAL PERIOD OF THE QTR */

            CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '004') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '007') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR ), '010') AS NVARCHAR(7) )

                 END END END END AS FIRSTMONTHOFCURRENTQTR,

            /*FIRST FISCAL PERIOD OF THE QTR */

            CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '001') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1), '004') AS NVARCHAR(7) )

                 else case WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'

                 then  CAST( CONCAT( (SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

                 ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'

                 then  CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

                 END END END END AS  FIRSTMONTHOFPREVYRCURRENTQTR,

            /*FI OPENING BALANCE FISCPER */

            CAST( CONCAT( ( SCL.CALENDARYEAR ), '000') AS NVARCHAR(7) ) AS BEGINNINGFIFISCPER,

            /*PREV YEAR FI OPENING BALANCE FISCPER */

            CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '000') AS NVARCHAR(7) ) AS PREVYEARBEGINNINGFIFISCPER,

              /* PREV FISCAL PERIOD */

            CASE WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 1 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 1 )) AS NVARCHAR(7) )

            end                  AS CURRMINUS1FISCPER,

              /* PREV SECOND FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 2 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 2 )) AS NVARCHAR(7) ) end

            end               AS CURRMINUS2FISCPER,

              /* PREV THIRD FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 3 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 3 )) AS NVARCHAR(7) ) end end

            end               AS CURRMINUS3FISCPER,

              /* PREV 4TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE  CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 4 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 4 )) AS NVARCHAR(7) ) end end end

            end               AS CURRMINUS4FISCPER,

              /* PREV 5TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 5 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 5 )) AS NVARCHAR(7) ) end end end end

            end               AS CURRMINUS5FISCPER,

              /* PREV 6TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 6 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 6 )) AS NVARCHAR(7) ) end end end end end

            end               AS CURRMINUS6FISCPER,

              /* PREV 7TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 7 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 7 )) AS NVARCHAR(7) ) end end end end end end

            end               AS CURRMINUS7FISCPER,

              /* PREV 8TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 8 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 8 )) AS NVARCHAR(7) ) end end end end end end end

            end               AS CURRMINUS8FISCPER,

              /* PREV 9TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '004') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '09'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 9 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 9 )) AS NVARCHAR(7) ) end end end end end end end end

            end               AS CURRMINUS9FISCPER,

              /* PREV 10TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '004') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '09'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '10'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 10 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 10 )) AS NVARCHAR(7) ) end end end end end end end end end

            end               AS CURRMINUS10FISCPER,

              /* PREV 11TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '002') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '004') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '09'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '10'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '11'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 11 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 11 )) AS NVARCHAR(7) ) end end end end end end end end end end

            end               AS CURRMINUS11FISCPER,

            /* PREV 12TH FISCAL PERIOD */

            CAST( CONCAT( CONCAT( SCL.CALENDARYEAR - 1, '0'), SCL.CALENDARMONTH) as NVARCHAR(7) )   AS CURRMINUS12FISCPER,

            /* PREV YEAR PREV FISCAL PERIOD */

            CASE WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 1 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 1 )) AS NVARCHAR(7) )

            end                  AS PREVYRCURRMINUS1FISCPER,

              /* PREV YEAR PREV SECOND FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 2 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 2 )) AS NVARCHAR(7) ) end

            end               AS PREVYRCURRMINUS2FISCPER,

              /* PREV YEAR PREV THIRD FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 3 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 3 )) AS NVARCHAR(7) ) end end

            end               AS PREVYRCURRMINUS3FISCPER,

              /* PREV YEAR PREV 4TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE  CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 4 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 4 )) AS NVARCHAR(7) ) end end end

            end               AS PREVYRCURRMINUS4FISCPER,

              /* PREV YEAR PREV 5TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 5 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 5 )) AS NVARCHAR(7) ) end end end end

            end               AS PREVYRCURRMINUS5FISCPER,

              /* PREV YEAR PREV 6TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 6 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 6 )) AS NVARCHAR(7) ) end end end end end

            end               AS PREVYRCURRMINUS6FISCPER,

              /* PREV YEAR PREV 7TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 7 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 7 )) AS NVARCHAR(7) ) end end end end end end

            end               AS PREVYRCURRMINUS7FISCPER,

              /* PREV YEAR PREV 8TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 8 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 8 )) AS NVARCHAR(7) ) end end end end end end end

            end               AS PREVYRCURRMINUS8FISCPER,

              /* PREV YEAR PREV 9TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '004') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '09'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 9 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 9 )) AS NVARCHAR(7) ) end end end end end end end end

            end               AS PREVYRCURRMINUS9FISCPER,

              /* PREV YEAR PREV 10TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '003') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '004') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '09'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '10'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 10 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 10 )) AS NVARCHAR(7) ) end end end end end end end end end

            end               AS PREVYRCURRMINUS10FISCPER,

              /* PREV YEAR PREV 11TH FISCAL PERIOD */

            case WHEN SCL.CALENDARMONTH = '01'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '002') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '02'

            then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '003') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '03'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '004') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '04'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '05'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '06'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '07'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '08'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '09'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '10'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )

            ELSE CASE WHEN SCL.CALENDARMONTH = '11'

            THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )

            ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 11 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 11 )) AS NVARCHAR(7) ) end end end end end end end end end end

            end               AS PREVYRCURRMINUS11FISCPER,

            /* PREV YEAR PREV 12TH FISCAL PERIOD 014467*/

            CAST( CONCAT( CONCAT( SCL.CALENDARYEAR - 2, '0'), SCL.CALENDARMONTH) as NVARCHAR(7) )   AS PREVYRCURRMINUS12FISCPER,

             /*CURR PERIOD -1 YEAR */

            case WHEN SCL.CALENDARMONTH = '01'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS1YEAR,

            /*CURR PERIOD -2 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS2YEAR,

            /*CURR PERIOD -3 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS3YEAR,

            /*CURR PERIOD -4 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS4YEAR,

            /*CURR PERIOD -5 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS5YEAR,

            /*CURR PERIOD -6 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS6YEAR,

            /*CURR PERIOD -7 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS7YEAR,

            /*CURR PERIOD -8 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS8YEAR,

             /*CURR PERIOD -9 YEAR 014467*/

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS9YEAR,

             /*CURR PERIOD -10 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09' OR  SCL.CALENDARMONTH = '10'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS10YEAR,

             /*CURR PERIOD -11 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09' OR  SCL.CALENDARMONTH = '10' OR  SCL.CALENDARMONTH = '11'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS11YEAR,

             /*CURR PERIOD -12 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09' OR  SCL.CALENDARMONTH = '10' OR  SCL.CALENDARMONTH = '11' OR  SCL.CALENDARMONTH = '12'

                 then  CAST(  ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )

                 END AS  CURRMINUS12YEAR,


         /*PREV YR CURR PERIOD -1 YEAR */

            case WHEN SCL.CALENDARMONTH = '01'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS1YEAR,

            /*PREV YR CURR PERIOD -2 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS2YEAR,

            /*PREV YR CURR PERIOD -3 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS3YEAR,

            /*PREV YR CURR PERIOD -4 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS4YEAR,

            /*PREV YR CURR PERIOD -5 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS5YEAR,

            /*PREV YR CURR PERIOD -6 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS6YEAR,

            /*PREV YR CURR PERIOD -7 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS7YEAR,

            /*PREV YR CURR PERIOD -8 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS8YEAR,

             /*PREV YR CURR PERIOD -9 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS9YEAR,

             /*PREV YR CURR PERIOD -10 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09' OR  SCL.CALENDARMONTH = '10'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS10YEAR,

             /*PREV YR CURR PERIOD -11 YEAR 014467*/

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09' OR  SCL.CALENDARMONTH = '10' OR  SCL.CALENDARMONTH = '11'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS11YEAR,

             /*PREV YR CURR PERIOD -12 YEAR */

            CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'

                  OR  SCL.CALENDARMONTH = '05' OR  SCL.CALENDARMONTH = '06' OR  SCL.CALENDARMONTH = '07' OR  SCL.CALENDARMONTH = '08'

                  OR  SCL.CALENDARMONTH = '09' OR  SCL.CALENDARMONTH = '10' OR  SCL.CALENDARMONTH = '11' OR  SCL.CALENDARMONTH = '12'

                 then  CAST(  ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )

                 ELSE  CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )

                 END AS  PREVYRCURRMINUS12YEAR,

            /*CURRENT QTR IN STRING FORMAT */

            CAST ( CONCAT( SCL.CALENDARYEAR ,CONCAT( '.Q', ( SCL.CALENDARQUARTER ) ) )  AS VARCHAR(7) )  AS CURRQTRYRINSTR,

            /*PREV QTR IN STRING FORMAT */

            CASE WHEN SCL.CALENDARQUARTER = '1'

            THEN CAST( CONCAT( (SCL.CALENDARYEAR - 1), CONCAT ( '.Q','4' ) )  AS VARCHAR(7) )

            else CAST( CONCAT( SCL.CALENDARYEAR , CONCAT ( '.Q' ,( SCL.CALENDARQUARTER - 1 ) ) )  AS VARCHAR(7) )

            END AS PREVQTRYRINSTR,

             /*CURRENT QTR IN STRING FORMAT */

            CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ),CONCAT( '.Q', ( SCL.CALENDARQUARTER ) ) )  AS VARCHAR(7) )   AS CURRQTRPRYRINSTR,

            /*FLAG LAST PERIOD OF THE QTR*/

           CASE WHEN SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '12'

                THEN 'X' ELSE '' END AS     LASTPRDOFQTR

   FROM :I_MAIN AS SCL

                           LEFT OUTER JOIN SEPMRAIMONTHNAME AS MONTHTEXT

                        ON  MONTHTEXT.LANGUAGE = 'E'

                        and SCL.CALENDARMONTH  = MONTHTEXT.CALENDARMONTH;

  ENDMETHOD.

endclass.

upon activation you can execute the table function like a CDS which would give us below result.

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Guides, SAP HANA Career, SAP HANA Preparation

We can filter to get the distinct records

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Guides, SAP HANA Career, SAP HANA Preparation

you can consume the Table function like any other CDS view using Association or Join to make use of the attributes, Here is how I consumed the Table function

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Guides, SAP HANA Career, SAP HANA Preparation

No comments:

Post a Comment