5 Replies Latest reply: Jun 11, 2014 5:20 AM by jagan mohan rao appala RSS

    Need to Create AR Buckets

    Alkesh Sharma

      Hello,

       

      Below is the script which creates AR buckets (Backend SAP)

      The buckets created below are 1-30,30-60,60-90,90-120,120-15, and above 120,

       

      But I need buckets as : 1-10,11-20,21-30, .............141-150, above 150.

       

      Below is the script:

       

       

       

      // Table: BSAD - Accounting Cleared Items

      unqualify *;

      [BSID_BSAD]:

      Load *,

         if([Days Late] <1, 'On Time',

        if([Days Late] < 31 and [Days Late] > 0, '1 - 30',

        if([Days Late] < 61 and [Days Late] > 30,'31 - 60',

        if([Days Late] < 91 and [Days Late] > 60, '61 - 90',

        if([Days Late] < 121 and [Days Late] > 90, '91 - 120',

        if([Days Late] < 151 and [Days Late] > 120, '121 - 150',

        if([Days Late] > 150, 'Over 150', 0))))))) as [Age Bucket],

        if([Days Late] <1, [Amount in LC_DMBTR],0) as [On Time],

        if([Days Late] < 31 and [Days Late] > 0,[Amount in LC_DMBTR],0) as [1 - 30],

        if([Days Late] < 61 and [Days Late] > 30,[Amount in LC_DMBTR],0) as [31 - 60],

        if([Days Late] < 91 and [Days Late] > 60,[Amount in LC_DMBTR],0) as [61 - 90],

        if([Days Late] < 121 and [Days Late] > 90,[Amount in LC_DMBTR],0) as [91 - 120],

        if([Days Late] < 151 and [Days Late] > 120,[Amount in LC_DMBTR],0) as [121 - 150],

        if([Days Late] > 150,[Amount in LC_DMBTR],0) as [Over 150];

       

       

      Load *,

        if(([Clearing_AUGDT]>([Due Period Calc] + [Due Date])), (([Amount in LC_DMBTR] + [LC tax_MWSTS])-[Discount amt_SKNTO]),0) as [Net Amt Paid Late],

        if(([Clearing_AUGDT]<=([Due Period Calc] + [Due Date])),(([Amount in LC_DMBTR] + [LC tax_MWSTS])-[Discount amt_SKNTO]),0) as [Net Amt Paid On Time],

        ([Clearing_AUGDT]-([Due Period Calc] + [Due Date])) as [Days Late],

        Year([Due Date incl Pay Terms]) as [Due Year incl Pay Terms],

        Month([Due Date incl Pay Terms]) as [Due Month incl Pay Terms],

        Week([Due Date incl Pay Terms]) as [Due Week incl Pay Terms],

        0 as [Days Overdue],

        0 as [Overdue Amount incl Pay Terms];

       

       

      Load

      KUNNR as %KNA1_BSID_BSAD_KEY,

      BUKRS as %T001_BSID_BSAD_KEY,

      UMSKZ &'/' & BSCHL as %BSAD_TBSLT_KEY,

      'Cleared' as Cleared_Flag,

      if(SHKZG = 'S','D','C') as [Debit/Credit_Flag_SHKZG],

      if(SHKZG='H',-1,1) * DMBTR as [Amount in LC_DMBTR],

        if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T=0,'0',

        if(ZBD1T >0 AND ZBD2T=0 AND ZBD3T=0,'1',

        if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T=0,'2',

        if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T>0,'3',

        if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T<>0,'4',

        if(ZBD1T='-' AND ZBD2T='-' AND ZBD3T='-','','5')))))) as Discount,

      if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

        if((ZBD2T > ZBD3T), (ZBD2T + 1),

        if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))) as [Due Period Calc],

      date(if(SHKZG = 'S',(if(ZFBDT='0000-00-00',BUDAT,ZFBDT)

        +(if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

        if((ZBD2T > ZBD3T), (ZBD2T + 1),

        if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))))),null())) as [Due Date incl Pay Terms],

      if(ZFBDT='0000-00-00',BUDAT,ZFBDT) as [Due Date],

      if(SHKZG = 'S',num((AUGDT - BUDAT)),0) as [Days Open],

      BLART as [Document type_BLART],

      applymap('T003T',BLART) as [Document type Name],

      KUNNR as [Customer ID_KUNNR],

      BUKRS as [Company Code_BUKRS],

      WRBTR as [Amount_WRBTR],

      WAERS as [Currency_WAERS],

      WAERS as [CurrencyLink],

      AUGBL as [Clrng doc._AUGBL],

      PRCTR as [Profit Center],

      Date(AUGDT,'DD/MM/YYYY') as [Clearing_AUGDT],

      month(AUGDT)&' - '&year(AUGDT)  as [Clearing_MonthYear],

      year(AUGDT) as [Clearing Year],

      month(AUGDT) as [Clearing Month],

      week(AUGDT) as [Clearing Week],

      BELNR as [Document Number_BELNR],

      BUDAT as [Posting Date_BUDAT],

      GSBER as [Business Area_GSBER],

      BLDAT as [Document Date_BLDAT],

      Date(BLDAT,'DD/MM/YYYY') as [CurrencyDate],

      BSCHL as [Posting key_BSCHL],

      BSTAT as [Doc.status_BSTAT],

      BUZEI as [Line item_BUZEI],

      BUZID as [Line item ID_BUZID],

      GJAHR as [Fiscal Year_GJAHR],

      MWSKZ as [Tax code_MWSKZ],

      WMWST as [Tax amount_WMWST],

      MABER as [Dunning Area_MABER],

      ZBD1P as [Disc.percent 1_ZBD1P],

      ZBD1T as [Days 1_ZBD1T],

      ZBD2P as [Disc.percent 2_ZBD2P],

      ZBD2T as [Days 2_ZBD2T],

      ZBD3T as [Days net_ZBD3T],

      ZBFIX as [Fixed_ZBFIX],

      date(ZFBDT,'DD/MM/YYYY') as [Baseline date_ZFBDT],

      ZLSCH as [Pmt method_ZLSCH],

      SGTXT as [Text_SGTXT],

      KOSTL as [Cost Center_KOSTL],

      UMSKZ as [Special G/L ind_UMSKZ],

      HKONT as [G/L_HKONT],

      KONTL as [Acct assignment_KONTL],

      KONTT as [Acct assgt cat._KONTT],

      if(SHKZG='H',-1,1) * MWSTS as [LC tax_MWSTS],

      SAKNR as [G/L Account No._SAKNR],

      SHKZG as [Debit/Credit_SHKZG],

      if(SHKZG='H',-1,1) * SKNTO as [Discount amt_SKNTO]

       

       

       

       

      From $(vTRANSACTION)\BSAD.QVD (qvd) Where ZFBDT > '01/09/2010' AND BUDAT > '2010-09-01' AND KUNNR <> 'C101IB0001' AND KUNNR <> 'C101IC0003' AND KUNNR <> 'C101IS0002';

       

       

       

       

      // Table: BSID - Accounting Open Items

       

       

      concatenate Load *,

         if([Days Overdue] <1, 'Not Due',

        if([Days Overdue] < 31 and [Days Overdue] > 0, '1 - 30',

        if([Days Overdue] < 61 and [Days Overdue] > 30,'31 - 60',

        if([Days Overdue] < 91 and [Days Overdue] > 60, '61 - 90',

        if([Days Overdue] < 121 and [Days Overdue] > 90, '91 - 120',

        if([Days Overdue] < 151 and [Days Overdue] > 120, '121 - 150',

        if([Days Overdue] > 150, 'Over 150', 0))))))) as [Age Bucket],

        if([Days Overdue] <1, [Amount in LC_DMBTR],0) as [Current],

        if([Days Overdue] < 31 and [Days Overdue] > 0,[Amount in LC_DMBTR],0) as [1 - 30],

        if([Days Overdue] < 61 and [Days Overdue] > 30,[Amount in LC_DMBTR],0) as [31 - 60],

        if([Days Overdue] < 91 and [Days Overdue] > 60,[Amount in LC_DMBTR],0) as [61 - 90],

        if([Days Overdue] < 121 and [Days Overdue] > 90,[Amount in LC_DMBTR],0) as [91 - 120],

        if([Days Overdue] < 151 and [Days Overdue] > 120,[Amount in LC_DMBTR],0) as [121 - 150],

        if([Days Overdue] > 150,[Amount in LC_DMBTR],0) as [Over 150];

       

       

      Load *,

        0 as [Net Amt Paid Late],

        0 as [Net Amt Paid On Time],

        0 as [Days Late],

        Year([Due Date incl Pay Terms]) as [Due Year incl Pay Terms],

        Month([Due Date incl Pay Terms]) as [Due Month incl Pay Terms],

        Week([Due Date incl Pay Terms]) as [Due Week incl Pay Terms],

        if((today() > [Due Date incl Pay Terms]), (today() - [Due Date incl Pay Terms]), 0) as [Days Overdue],

        if([Due Date incl Pay Terms] < today(),[Amount in LC_DMBTR], 0) as [Overdue Amount incl Pay Terms];

       

       

      Load

      KUNNR as %KNA1_BSID_BSAD_KEY,

      BUKRS as %T001_BSID_BSAD_KEY,

      UMSKZ &'/' & BSCHL as %BSAD_TBSLT_KEY,

      'Open' as Cleared_Flag,

      if(SHKZG = 'S','D','C') as [Debit/Credit_Flag_SHKZG],

      if(SHKZG='H',-1,1) * DMBTR as [Amount in LC_DMBTR],

      if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T=0,'0',

        if(ZBD1T >0 AND ZBD2T=0 AND ZBD3T=0,'1',

        if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T=0,'2',

        if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T>0,'3',

        if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T<>0,'4',

        if(ZBD1T='-' AND ZBD2T='-' AND ZBD3T='-','','5')))))) as Discount,

      if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

        if((ZBD2T > ZBD3T), (ZBD2T + 1),

        if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))) as [Due Period Calc],

      date(if(SHKZG = 'S',(if(ZFBDT='0000-00-00',BUDAT,ZFBDT)

        +(if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

        if((ZBD2T > ZBD3T), (ZBD2T + 1),

        if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))))),null())) as [Due Date incl Pay Terms],

      if(ZFBDT='0000-00-00',BUDAT,ZFBDT) as [Due Date],

      if(SHKZG = 'S',num((today()- BUDAT)),0) as [Days Open],

      BLART as [Document type_BLART],

      applymap('T003T',BLART) as [Document type Name],

      KUNNR as [Customer ID_KUNNR],

      BUKRS as [Company Code_BUKRS],

      WRBTR as [Amount_WRBTR], 

      WAERS as [Currency_WAERS],

      WAERS as [CurrencyLink],

      AUGBL as [Clrng doc._AUGBL],

      PRCTR as [Profit Center],

      Date(AUGDT,'DD/MM/YYYY') as [Clearing_AUGDT],

      month(AUGDT)&' - '& year(AUGDT)  as [Clearing_MonthYear],

      year(AUGDT) as [Clearing Year],

      month(AUGDT) as [Clearing Month],

      week(AUGDT) as [Clearing Week],

      BELNR as [Document Number_BELNR],

      BUDAT as [Posting Date_BUDAT],

      GSBER as [Business Area_GSBER],

      BLDAT as [Document Date_BLDAT],

      Date(BLDAT,'DD/MM/YYYY') as [CurrencyDate],

      BSCHL as [Posting key_BSCHL],

      BSTAT as [Doc.status_BSTAT],

      BUZEI as [Line item_BUZEI],

      BUZID as [Line item ID_BUZID],

      GJAHR as [Fiscal Year_GJAHR],

      MWSKZ as [Tax code_MWSKZ],

      WMWST as [Tax amount_WMWST],

      MABER as [Dunning Area_MABER],

      ZBD1P as [Disc.percent 1_ZBD1P],

      ZBD1T as [Days 1_ZBD1T],

      ZBD2P as [Disc.percent 2_ZBD2P],

      ZBD2T as [Days 2_ZBD2T],

      ZBD3T as [Days net_ZBD3T],

      ZBFIX as [Fixed_ZBFIX],

      Date(ZFBDT,'DD/MM/YYYY') as [Baseline date_ZFBDT],

      ZLSCH as [Pmt method_ZLSCH],

      SGTXT as [Text_SGTXT],

      KOSTL as [Cost Center_KOSTL],

      UMSKZ as [Special G/L ind_UMSKZ],

      HKONT as [G/L_HKONT], 

      KONTL as [Acct assignment_KONTL],

      KONTT as [Acct assgt cat._KONTT],

      if(SHKZG='H',-1,1) * MWSTS as [LC tax_MWSTS], 

      SAKNR as [G/L Account No._SAKNR],

      SHKZG as [Debit/Credit_SHKZG],

      if(SHKZG='H',-1,1) * SKNTO as [Discount amt_SKNTO]

       

       

      From $(vTRANSACTION)\BSID.QVD (qvd) Where ZFBDT > '01/09/2010' AND BUDAT > '2010-09-01' AND KUNNR <> 'C101IB0001' AND KUNNR <> 'C101IC0003' AND KUNNR <> 'C101IS0002';

      //Store Data to QVD File:BSID_BSAD.qvd