Skip to Content
avatar image
Former Member

Profit Period Balance Sheet Query on SAP SBO

Hi, I want to convert Balance Sheet PLD default SAP SBO to Crystal Report but I can't get Profit Period value SQL Query. Thank you in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 06, 2017 at 02:01 AM

    Hi Sumaryanto,

    Profit period in balance sheet in certain period = profit and lost statement in certain period. Please see the picture below :


    Regards,

    Rizki W

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 07, 2017 at 02:17 AM

    Hi Rizki Wicaksana,

    Thank a lot for your explanation. That is my final query and it work :

    DECLARE @activalancar DECIMAL(16,2)=
    ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '10010%') OR AcctCode LIKE '1001100000%'
    )d),0)
    --PRINT @activalancar
    DECLARE @tdklancar DECIMAL(16,2)=
    ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND Levels=3 AND AcctCode LIKE '10020%'
    )d),0)
    --PRINT @tdklancar

    DECLARE @Assets DECIMAL(16,2)=(@activalancar+@tdklancar)
    --PRINT @Assets

    DECLARE @hutangjpendek DECIMAL(16,2)=
    ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Credit-T1.Debit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '20010%')
    )d),0)
    --PRINT @hutangjpendek

    DECLARE @hutangjpanjang DECIMAL(16,2)=
    ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Credit-T1.Debit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '20020%')
    )d),0)
    --PRINT @hutangjpanjang

    DECLARE @Liabilities DECIMAL(16,2)=(@hutangjpendek+@hutangjpanjang)
    --PRINT @Liabilities

    DECLARE @modal DECIMAL(16,2)=
    ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Credit-T1.Debit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '30010%')
    )d),0)
    --PRINT @modal

    DECLARE @profit DECIMAL(16,2)=
    (SELECT SUM(T1.[Credit]-T1.[Debit]) FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
    INNER JOIN OJDT T2 ON T1.TransId = T2.TransId WHERE T0.GroupMask in (4,5,6,7,8)
    and DateDiff(YY,T2.[RefDate],'2016-12-31') = 0
    AND T1.[Debit] != T1.[Credit])
    --PRINT @profit

    DECLARE @totcapital DECIMAL(16,2)=@modal+@profit
    DECLARE @grandtot DECIMAL(16,2)=@Liabilities+@totcapital


    SELECT (1)Num,'Assets'AccName,(0)Balance,(NULL)Percentage UNION
    SELECT (2),'',NULL,NULL UNION
    SELECT (3),SPACE(10)+AcctCode+' - '+AcctName,NULL,NULL FROM OACT WHERE AcctCode='1001000000' UNION
    SELECT (4),'',NULL,NULL UNION
    SELECT (5),Acc,Balance,(Balance/@Assets)*100 FROM (
    SELECT (SPACE(20)+AcctCode+' - '+AcctName)Acc
    ,ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '10010%') OR AcctCode LIKE '1001100000%'
    )v UNION
    SELECT (6),'',NULL,NULL UNION
    SELECT (7),SPACE(10)+'Total '+AcctCode+' - '+AcctName,@activalancar,(@activalancar/@Assets)*100 FROM OACT WHERE AcctCode='1001000000' UNION
    SELECT (8),'',NULL,NULL UNION
    SELECT (9),SPACE(10)+AcctCode+' - '+AcctName,NULL,NULL FROM OACT WHERE AcctCode='1002000000' UNION
    SELECT (10),'',NULL,NULL UNION
    SELECT (11),Acc,Balance,(Balance/@Assets)*100 FROM (
    SELECT (SPACE(20)+AcctCode+' - '+AcctName)Acc
    ,ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '10020%')
    )v UNION
    SELECT (12),'',NULL,NULL UNION
    SELECT (13),SPACE(10)+'Total '+AcctCode+' - '+AcctName,@tdklancar,(@tdklancar/@Assets)*100 FROM OACT WHERE AcctCode='1002000000' UNION
    SELECT (14),'',NULL,NULL UNION
    SELECT (15),'Total Assets',@Assets,(@Assets/@Assets)*100 UNION
    SELECT (16),'',NULL,NULL UNION
    SELECT (17)Num,'Liabilities',(NULL),(NULL) UNION
    SELECT (18),'',NULL,NULL UNION
    SELECT (19),SPACE(10)+AcctCode+' - '+AcctName,NULL,NULL FROM OACT WHERE AcctCode='2001000000' UNION
    SELECT (20),'',NULL,NULL UNION
    SELECT (21),Acc,Balance,(Balance/@Liabilities)*100 FROM (
    SELECT (SPACE(20)+AcctCode+' - '+AcctName)Acc
    ,ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '20010%')
    )v UNION
    SELECT (22),'',NULL,NULL UNION
    SELECT (23),SPACE(10)+'Total '+AcctCode+' - '+AcctName,@hutangjpendek,(@hutangjpendek/@Liabilities)*100 FROM OACT WHERE AcctCode='2001000000' UNION
    SELECT (24),'',NULL,NULL UNION
    SELECT (25),SPACE(10)+AcctCode+' - '+AcctName,NULL,NULL FROM OACT WHERE AcctCode='2002000000' UNION
    SELECT (26),'',NULL,NULL UNION
    SELECT (27),Acc,Balance,(Balance/@Liabilities)*100 FROM (
    SELECT (SPACE(20)+AcctCode+' - '+AcctName)Acc
    ,ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance
    FROM [OACT]z WHERE 1=1
    AND (Levels=3 AND AcctCode LIKE '20020%')
    )v UNION
    SELECT (28),'',NULL,NULL UNION
    SELECT (29),SPACE(10)+'Total '+AcctCode+' - '+AcctName,@hutangjpanjang,(@hutangjpanjang/@Liabilities)*100 FROM OACT WHERE AcctCode='2002000000' UNION
    SELECT (30),'Total Liabilities',@Liabilities,(@Liabilities/@Liabilities)*100 UNION
    SELECT (31),'',NULL,NULL UNION
    SELECT (32),'Capital & Reserves',(NULL),(NULL) UNION
    SELECT (33),'',NULL,NULL UNION
    SELECT (34),SPACE(10)+AcctCode+' - '+AcctName,NULL,NULL FROM OACT WHERE AcctCode='3001000000' UNION
    SELECT (35),'',NULL,NULL UNION
    SELECT (36),SPACE(20)+AcctCode+' - '+AcctName
    ,ISNULL((SELECT SUM(Balance)Balance FROM (
    SELECT b.ActId,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Balance)Balance FROM (
    SELECT T1.Account,SUM(T1.Debit)Debit,SUM(T1.Credit)Credit
    ,SUM(T1.Debit - T1.Credit)Balance FROM [OJDT]T0 INNER JOIN [JDT1]T1 ON T0.TransId = T1.TransId
    WHERE T0.RefDate<='2016-12-31' GROUP BY T1.Account
    )a INNER JOIN [OACT]b ON a.Account=b.AcctCode GROUP BY b.ActId
    )c WHERE ActId LIKE LEFT(AcctCode,6)+'%'),0)Balance,NULL
    FROM [OACT]z WHERE 1=1 AND (Levels=3 AND AcctCode LIKE '30010%') UNION
    SELECT (37),'',NULL,NULL UNION
    SELECT (38),SPACE(10)+'Total '+AcctCode+' - '+AcctName,@modal,NULL FROM OACT WHERE AcctCode='3001000000' UNION
    SELECT (39),'',NULL,NULL UNION
    SELECT (40),SPACE(10)+'Profit Period ',@profit,NULL UNION
    SELECT (41),'',NULL,NULL UNION
    SELECT (42),'Total Capital and Reserves ',@totcapital,NULL UNION
    SELECT (43),'',@grandtot,NULL --UNION

    Add comment
    10|10000 characters needed characters exceeded