Profit Period Balance Sheet Query on SAP SBO

[Former Member]

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.

[Former Member]

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

[former_member375912]

sudah beres kan berarti ?

[former_member375912]

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