Balance Sheet Query showing wrong amounts on VAT accounts only

• raimon.tadya

  Mr or Mrs. 500

  Points: 533

  More actions

  May 21, 2015 at 4:52 am

  #304131

  I have one query which is pulling Balance sheet amounts from SAP Business One database. The query is giving the correct figures for the rest of the accounts except for the VAT Input refundable account 123600 and VAT Output Payable account 221400. The query sums up totals at Title account level(FatherNum) and the above accounts are the title accounts. Here the query below and I really look forward to your assistance:

  ---------------------------------------------------------------------------------------------------------------------

  SELECT CAST(T0.TransId AS Varchar(30)) AS TransId, CASE WHEN t3.FatherNum IN ('100000', '350000') THEN '-3 OK' ELSE CAST(T0.TransType AS Varchar(30))

  END AS TransType, CAST(T0.BaseRef AS VarChar(30)) AS BaseRef, T0.RefDate,T0.Number as Docnum, DATEPART(Month, T0.RefDate) AS JrnMonth, T0.FinncPriod, T1.Account, T1.Debit,

  T1.Credit, T1.Debit - T1.Credit AS JrnAmt, ISNULL(T1.SYSCred, 0) AS SysCred, ISNULL(T1.SYSDeb, 0) AS SysDeb, T1.ShortName, T1.Ref1, T1.Ref2, T1.Project,

  T1.ProfitCode, T1.TotalVat, T0.ObjType, T2.AbsEntry, T2.F_RefDate,t6.AcctCode as AccountCode,t6.Levels,t6.FormatCode, t3.AcctName AS Name,t3.FatherNum, t3.FatherNum AS Fathernum_4, t4.FatherNum AS Fathernum_3,

  t5.FatherNum AS Fathernum_2, t3.GroupMask AS FatherNum_1,t3."Segment_0",t3."Segment_1",t3."Segment_2",

  CASE

  WHEN t3.FatherNum like '1%'

  THEN 'Assets'

  WHEN t3.FatherNum like '2%'

  THEN 'Liabilities'

  WHEN t3.FatherNum like '3%'

  THEN 'Capital and Reserves'

  End As 'BS Group',

  CASE

  WHENT0.FinncPriod='133'THEN'2020-Dec'

  WHENT0.FinncPriod='132'THEN'2020-Nov'

  WHENT0.FinncPriod='131'THEN'2020-Oct'

  WHENT0.FinncPriod='130'THEN'2020-Sep'

  WHENT0.FinncPriod='129'THEN'2020-Aug'

  WHENT0.FinncPriod='128'THEN'2020-Jul'

  WHENT0.FinncPriod='127'THEN'2020-Jun'

  WHENT0.FinncPriod='126'THEN'2020-May'

  WHENT0.FinncPriod='125'THEN'2020-Apr'

  WHENT0.FinncPriod='124'THEN'2020-Mar'

  WHENT0.FinncPriod='123'THEN'2020-Feb'

  WHENT0.FinncPriod='122'THEN'2020-Jan'

  WHENT0.FinncPriod='121'THEN'2019-Dec'

  WHENT0.FinncPriod='120'THEN'2019-Nov'

  WHENT0.FinncPriod='119'THEN'2019-Oct'

  WHENT0.FinncPriod='118'THEN'2019-Sep'

  WHENT0.FinncPriod='117'THEN'2019-Aug'

  WHENT0.FinncPriod='116'THEN'2019-Jul'

  WHENT0.FinncPriod='115'THEN'2019-Jun'

  WHENT0.FinncPriod='114'THEN'2019-May'

  WHENT0.FinncPriod='113'THEN'2019-Apr'

  WHENT0.FinncPriod='112'THEN'2019-Mar'

  WHENT0.FinncPriod='111'THEN'2019-Feb'

  WHENT0.FinncPriod='110'THEN'2019-Jan'

  WHENT0.FinncPriod='109'THEN'2018-Dec'

  WHENT0.FinncPriod='108'THEN'2018-Nov'

  WHENT0.FinncPriod='107'THEN'2018-Oct'

  WHENT0.FinncPriod='106'THEN'2018-Sep'

  WHENT0.FinncPriod='105'THEN'2018-Aug'

  WHENT0.FinncPriod='104'THEN'2018-Jul'

  WHENT0.FinncPriod='103'THEN'2018-Jun'

  WHENT0.FinncPriod='102'THEN'2018-May'

  WHENT0.FinncPriod='101'THEN'2018-Apr'

  WHENT0.FinncPriod='100'THEN'2018-Mar'

  WHENT0.FinncPriod='99'THEN'2018-Feb'

  WHENT0.FinncPriod='98'THEN'2018-Jan'

  WHENT0.FinncPriod='97'THEN'2017-Dec'

  WHENT0.FinncPriod='96'THEN'2017-Nov'

  WHENT0.FinncPriod='95'THEN'2017-Oct'

  WHENT0.FinncPriod='94'THEN'2017-Sep'

  WHENT0.FinncPriod='93'THEN'2017-Aug'

  WHENT0.FinncPriod='92'THEN'2017-Jul'

  WHENT0.FinncPriod='91'THEN'2017-Jun'

  WHENT0.FinncPriod='90'THEN'2017-May'

  WHENT0.FinncPriod='89'THEN'2017-Apr'

  WHENT0.FinncPriod='88'THEN'2017-Mar'

  WHENT0.FinncPriod='87'THEN'2017-Feb'

  WHENT0.FinncPriod='86'THEN'2017-Jan'

  WHENT0.FinncPriod='85'THEN'2016-Dec'

  WHENT0.FinncPriod='84'THEN'2016-Nov'

  WHENT0.FinncPriod='83'THEN'2016-Oct'

  WHENT0.FinncPriod='82'THEN'2016-Sep'

  WHENT0.FinncPriod='81'THEN'2016-Aug'

  WHENT0.FinncPriod='80'THEN'2016-Jul'

  WHENT0.FinncPriod='79'THEN'2016-Jun'

  WHENT0.FinncPriod='78'THEN'2016-May'

  WHENT0.FinncPriod='77'THEN'2016-Apr'

  WHENT0.FinncPriod='76'THEN'2016-Mar'

  WHENT0.FinncPriod='75'THEN'2016-Feb'

  WHENT0.FinncPriod='74'THEN'2016-Jan'

  WHENT0.FinncPriod='73'THEN'2015-Dec'

  WHENT0.FinncPriod='72'THEN'2015-Nov'

  WHENT0.FinncPriod='71'THEN'2015-Oct'

  WHENT0.FinncPriod='70'THEN'2015-Sep'

  WHENT0.FinncPriod='69'THEN'2015-Aug'

  WHENT0.FinncPriod='68'THEN'2015-Jul'

  WHENT0.FinncPriod='67'THEN'2015-Jun'

  WHENT0.FinncPriod='66'THEN'2015-May'

  WHENT0.FinncPriod='65'THEN'2015-Apr'

  WHENT0.FinncPriod='64'THEN'2015-Mar'

  WHENT0.FinncPriod='63'THEN'2015-Feb'

  WHENT0.FinncPriod='62'THEN'2015-Jan'

  WHENT0.FinncPriod='60'THEN'2014-Dec'

  WHENT0.FinncPriod='59'THEN'2014-Nov'

  WHENT0.FinncPriod='58'THEN'2014-Oct'

  WHENT0.FinncPriod='57'THEN'2014-Sep'

  WHENT0.FinncPriod='56'THEN'2014-Aug'

  WHENT0.FinncPriod='55'THEN'2014-Jul'

  WHENT0.FinncPriod='54'THEN'2014-Jun'

  WHENT0.FinncPriod='53'THEN'2014-May'

  WHENT0.FinncPriod='52'THEN'2014-Apr'

  WHENT0.FinncPriod='51'THEN'2014-Mar'

  WHENT0.FinncPriod='50'THEN'2014-Feb'

  WHENT0.FinncPriod='49'THEN'2014-Jan'

  WHENT0.FinncPriod='48'THEN'2013-Dec'

  WHENT0.FinncPriod='47'THEN'2013-Nov'

  WHENT0.FinncPriod='46'THEN'2013-Oct'

  WHENT0.FinncPriod='45'THEN'2013-Sep'

  WHENT0.FinncPriod='44'THEN'2013-Jun'

  WHENT0.FinncPriod='41'THEN'2013-May'

  WHENT0.FinncPriod='40'THEN'2013-Apr'

  WHENT0.FinncPriod='39'THEN'2013-Mar'

  WHENT0.FinncPriod='38'THEN'2013-Feb'

  WHENT0.FinncPriod='37'THEN'2013-Jan'

  WHENT0.FinncPriod='36'THEN'2012-Dec'

  WHENT0.FinncPriod='35'THEN'2012-Nov'

  WHENT0.FinncPriod='34'THEN'2012-Oct'

  WHENT0.FinncPriod='33'THEN'2012-Sep'

  WHENT0.FinncPriod='32'THEN'2012-Aug'

  WHENT0.FinncPriod='31'THEN'2012-Jul'

  WHENT0.FinncPriod='30'THEN'2012-Jun'

  WHENT0.FinncPriod='29'THEN'2012-May'

  WHENT0.FinncPriod='28'THEN'2012-Apr'

  WHENT0.FinncPriod='27'THEN'2012-Mar'

  WHENT0.FinncPriod='26'THEN'2012-Feb'

  WHENT0.FinncPriod='25'THEN'2012-Jan'

  WHENT0.FinncPriod='24'THEN'2011-Dec'

  WHENT0.FinncPriod='23'THEN'2011-Nov'

  WHENT0.FinncPriod='22'THEN'2011-Oct'

  WHENT0.FinncPriod='21'THEN'2011-Sep'

  WHENT0.FinncPriod='20'THEN'2011-Aug'

  WHENT0.FinncPriod='19'THEN'2011-Jul'

  WHENT0.FinncPriod='18'THEN'2011-Jun'

  WHENT0.FinncPriod='17'THEN'2011-May'

  WHENT0.FinncPriod='16'THEN'2011-Apr'

  WHENT0.FinncPriod='15'THEN'2011-Mar'

  WHENT0.FinncPriod='14'THEN'2011-Feb'

  WHENT0.FinncPriod='13'THEN'2011-Jan'

  WHENT0.FinncPriod='12'THEN'2010-Dec'

  WHENT0.FinncPriod='11'THEN'2010-Nov'

  WHENT0.FinncPriod='10'THEN'2010-Oct'

  WHENT0.FinncPriod='9' THEN'2010-Sep'

  WHENT0.FinncPriod='8'THEN'2010-Aug'

  WHENT0.FinncPriod='7'THEN'2010-Jul'

  WHENT0.FinncPriod='6'THEN'2010-Jun'

  WHENT0.FinncPriod='5'THEN'2010-May'

  WHENT0.FinncPriod='4'THEN'2010-Apr'

  WHENT0.FinncPriod='3'THEN'2010-Mar'

  WHENT0.FinncPriod='2'THEN'2010-Feb'

  WHENT0.FinncPriod='1'THEN'2010-Jan'

  END AS 'FinPerdCode'

  FROM dbo.JDT1 AS T1 INNER JOIN

  dbo.OJDT AS T0 ON T0.TransId = T1.TransId LEFT OUTER JOIN

  dbo.OFPR AS T2 ON T2.AbsEntry = T1.FinncPriod LEFT OUTER JOIN

  dbo.OACT AS t3 ON t3.AcctCode = T1.Account LEFT OUTER JOIN

  dbo.OACT AS t4 ON t4.AcctCode = t3.FatherNum LEFT OUTER JOIN

  dbo.OACT AS t5 ON t5.AcctCode = t4.FatherNum LEFT OUTER JOIN

  dbo.OACT AS t6 ON t6.AcctCode = T1.Account

  where t3.Segment_2 = 'LSAL' and t3.GroupMask in ('1','2','3')

  --------------------------------------------------------------------------------------------------------------

  RT

• Lowell

  SSC Guru

  Points: 323506

  More actions

  May 21, 2015 at 5:37 am

  #1799611

  Raimon it looks like the issue has got to be in the data.

  there's nothing in the query itself that seems to show that VAT Input refundable account 123600 and VAT Output Payable account 221400 should be handled differently.

  How should those two specific qaccounts be handled differently?

  as a side note, i found a different way to calculate your FinPerdCode, instead of the long case statement:

  --Creates '2010-Jan' As String, based on FinncPriod

  --the first financial period is '2010-Jan', zero date to add period to to calculate is simply the month before as a ZeroBizDate

  LEFT(CONVERT(varchar,DateAdd(mm,T0.FinncPriod,CONVERT(date,'2009-12-01')),112),4)

  + '-'

  + LEFT(DATENAME(mm,DateAdd(mm,T0.FinncPriod,CONVERT(date,'2009-12-01'))),3) As FinPerdCode

  here's your original post featuring that style, so you can test it and see my snippet would actually satisfy your requirement:

  SELECT CAST(T0.TransId AS Varchar(30)) AS TransId, CASE WHEN t3.FatherNum IN ('100000', '350000') THEN '-3 OK' ELSE CAST(T0.TransType AS Varchar(30))

  END AS TransType, CAST(T0.BaseRef AS VarChar(30)) AS BaseRef, T0.RefDate,T0.Number as Docnum, DATEPART(Month, T0.RefDate) AS JrnMonth, T0.FinncPriod, T1.Account, T1.Debit,

  T1.Credit, T1.Debit - T1.Credit AS JrnAmt, ISNULL(T1.SYSCred, 0) AS SysCred, ISNULL(T1.SYSDeb, 0) AS SysDeb, T1.ShortName, T1.Ref1, T1.Ref2, T1.Project,

  T1.ProfitCode, T1.TotalVat, T0.ObjType, T2.AbsEntry, T2.F_RefDate,t6.AcctCode as AccountCode,t6.Levels,t6.FormatCode, t3.AcctName AS Name,t3.FatherNum, t3.FatherNum AS Fathernum_4, t4.FatherNum AS Fathernum_3,

  t5.FatherNum AS Fathernum_2, t3.GroupMask AS FatherNum_1,t3."Segment_0",t3."Segment_1",t3."Segment_2",

  CASE

  WHEN t3.FatherNum like '1%'

  THEN 'Assets'

  WHEN t3.FatherNum like '2%'

  THEN 'Liabilities'

  WHEN t3.FatherNum like '3%'

  THEN 'Capital and Reserves'

  End As 'BS Group',

  --Creates '2010-Jan' As String, based on FinncPriod

  --the first financial period is '2010-Jan', zero date to add period to to calculate is simply the month before as a ZeroBizDate

  LEFT(CONVERT(varchar,DateAdd(mm,T0.FinncPriod,CONVERT(date,'2009-12-01')),112),4)

  + '-'

  + LEFT(DATENAME(mm,DateAdd(mm,T0.FinncPriod,CONVERT(date,'2009-12-01'))),3) As FinPerdCode

  FROM dbo.JDT1 AS T1

  FROM dbo.JDT1 AS T1 INNER JOIN

  dbo.OJDT AS T0 ON T0.TransId = T1.TransId LEFT OUTER JOIN

  dbo.OFPR AS T2 ON T2.AbsEntry = T1.FinncPriod LEFT OUTER JOIN

  dbo.OACT AS t3 ON t3.AcctCode = T1.Account LEFT OUTER JOIN

  dbo.OACT AS t4 ON t4.AcctCode = t3.FatherNum LEFT OUTER JOIN

  dbo.OACT AS t5 ON t5.AcctCode = t4.FatherNum LEFT OUTER JOIN

  dbo.OACT AS t6 ON t6.AcctCode = T1.Account

  where t3.Segment_2 = 'LSAL' and t3.GroupMask in ('1','2','3')

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• raimon.tadya

  Mr or Mrs. 500

  Points: 533

  More actions

  May 21, 2015 at 6:17 am

  #1799625

  Thanks Lowell

  I will try your suggestions on the financial period its just that Im a newbie in SQL deep core coding hence that long casing. Still I'm not sure how problem on these accounts can be resolved as its just those specific accounts out of the whole chart of accounts.

• Lowell

  SSC Guru

  Points: 323506

  More actions

  May 21, 2015 at 7:00 am

  #1799645

  well, you might need to pull in some of the business guys.

  obviously they already told you "all the numbers except VAT are good"

  so VAT is supposed to be handled differently, apparently, so it's probably going to require some input from the biz guys as to what makes those accounts different. then you can treat them differently in the way they are calculated in the report.

  they might be able to tell you, for example, that the Payables account fo VAT is [PayableAmount] -[Taxes], and not just [PayableAmount]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• raimon.tadya

  Mr or Mrs. 500

  Points: 533

  More actions

  May 22, 2015 at 1:04 am

  #1799901

  Hi Lowell

  I think treating it differently could be the best possible solution. Many thanks

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  May 22, 2015 at 8:29 pm

  #1800188

  Shifting gears, is To.FinncPriod supposed to skip a couple of beats like this in your CASE statement???

  WHEN T0.FinncPriod = '46' THEN '2013-Oct'

  WHEN T0.FinncPriod = '45' THEN '2013-Sep'

  WHEN T0.FinncPriod = '[font="Arial Black"]44[/font]' THEN '2013-Jun'

  WHEN T0.FinncPriod = '[font="Arial Black"]41[/font]' THEN '2013-May'

  WHEN T0.FinncPriod = '40' THEN '2013-Apr'

  WHEN T0.FinncPriod = '39' THEN '2013-Mar'

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• raimon.tadya

  Mr or Mrs. 500

  Points: 533

  More actions

  May 26, 2015 at 12:37 am

  #1800477

  Hi Jeff

  Noted the issue, I will rectify. Many thanks

• raimon.tadya

  Mr or Mrs. 500

  Points: 533

  More actions

  June 4, 2015 at 12:39 am

  #1802886

  Hi All

  I have a query that Im using to calculate cumulative balances for Balance Sheet accounts and I need the script to be able to calculate the cumulative balance as at any chosen FinncPriod(Financial Period). Below it, I have attached the other query from where it is getting details for its calculation.

  --------------------------------------------------------------------------

  Select AcctCode ,FatherNum,FormatCode,FinncPriod, AcctName ,JrnAmt, sum(JrnAmt)

  OVER (Partition by AcctCode ) runningTotal

  from [dbo].[xBI_SavannaSBU_FinView_LSAL_BS_Test]

  ---------------------------------------------------------------------------

  select

  t0.Line_ID,t0.ShortName,t1.AcctCode,t1.AcctName,t1.Segment_2,t1.FatherNum,t1.GroupMask,

  t1.FormatCode,isNull(t0.Debit,'0') as Debit,isNull(t0.Credit,'0') as Credit,isNull(t0.Debit,'0')-isNull(t0.Credit,'0') as JrnAmt,t0.RefDate,

  t0.LineMemo,t0.ObjType,

  CASE t0.TransType WHEN '18' THEN 'APINV' WHEN '13' THEN 'ARINV' WHEN '16' THEN 'DN' WHEN '24' THEN 'RC' WHEN '14' THEN 'CN' WHEN '30' THEN 'JE' ELSE 'Other' END AS Type,

  t0.FinncPriod,t2.Code,t0.VatGroup

  from JDT1 T0 left join

  OACT as t1 on t1.AcctCode = T0.ShortName left join

  OFPR as t2 on t2.AbsEntry =t0.FinncPriod

  where t1.Segment_2 = 'LSAL'

  ---------------------------------------------------------------------------------------------------

  Your prompt response will be greatly appreciated.

  RT

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  June 4, 2015 at 9:27 am

  #1803052

  raimon.tadya (6/4/2015)

  ---

  Hi All

  I have a query that Im using to calculate cumulative balances for Balance Sheet accounts and I need the script to be able to calculate the cumulative balance as at any chosen FinncPriod(Financial Period). Below it, I have attached the other query from where it is getting details for its calculation.

  --------------------------------------------------------------------------

  Select AcctCode ,FatherNum,FormatCode,FinncPriod, AcctName ,JrnAmt, sum(JrnAmt)

  OVER (Partition by AcctCode ) runningTotal

  from [dbo].[xBI_SavannaSBU_FinView_LSAL_BS_Test]

  ---------------------------------------------------------------------------

  select

  t0.Line_ID,t0.ShortName,t1.AcctCode,t1.AcctName,t1.Segment_2,t1.FatherNum,t1.GroupMask,

  t1.FormatCode,isNull(t0.Debit,'0') as Debit,isNull(t0.Credit,'0') as Credit,isNull(t0.Debit,'0')-isNull(t0.Credit,'0') as JrnAmt,t0.RefDate,

  t0.LineMemo,t0.ObjType,

  CASE t0.TransType WHEN '18' THEN 'APINV' WHEN '13' THEN 'ARINV' WHEN '16' THEN 'DN' WHEN '24' THEN 'RC' WHEN '14' THEN 'CN' WHEN '30' THEN 'JE' ELSE 'Other' END AS Type,

  t0.FinncPriod,t2.Code,t0.VatGroup

  from JDT1 T0 left join

  OACT as t1 on t1.AcctCode = T0.ShortName left join

  OFPR as t2 on t2.AbsEntry =t0.FinncPriod

  where t1.Segment_2 = 'LSAL'

  ---------------------------------------------------------------------------------------------------

  Your prompt response will be greatly appreciated.

  RT

  Are you actually using SQL Server 2008? Also, please see the first link under "Helpful Links" in my signature line below to get better help for this problem. If you don't want to go that way, then the best I can offer is the following if you're using 2008 or less. http://www.sqlservercentral.com/articles/T-SQL/68467/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)