Calculating Monthly Balance for GLAccounts using BSEG

Hi ,
I have been working a lot with SAP related systems both ECC and CRM. Just wanted to tell you that it was a real nightmare when I started my journey almost an year back in 2019. So the SAP Systems have all the table and column names based in German. The is an organization called Simplement which adds a lot of helpful texts in English which help us to understand what the table/column actually means. Trust me it helps a lot to keep the sanity in place when you just start off. But later on once you get the hang of it you will remember the most important tables like VBAK, VBRK , BSEG , BKPF on top of your head. These tables are very broad and have multiple column names which sound similar and have similar data and sometimes it makes no sense to plain eyes why so many similar columns are required. But SAP treats or uses each column for a specific purpose and has these columns populated so it have less agrregations or calculations done on the fly.

Well to being with today's article/blog. I want to tell you what BSEG actually is and how you can do a balance for all GLAccounts for a company. Now BSEG is Accounting Document Segment and BKPF is Accounting Document Header
Here goes the complicated yet beautiful query.

SELECT h.[BUKRS: (PK) Company Code],s.[HKONT: General Ledger Account],h.[GJAHR: (PK) Fiscal Year], MONTH( h.[BUDAT: Posting Date in the Document]) PostingMonth,

  SUM(  CASE WHEN s.[SHKZG: Debit/Credit Indicator]='H' THEN s.[DMBE2: Amount in Second Local Currency]*-1 ELSE s.[DMBE2: Amount in Second Local Currency] END )

 GLAMountUSD 

FROM LIB_ECP_RTP.bv.[BSEG: Accounting Document Segment] s WITH (nolock)

JOIN LIB_ECP_RTP.bv.[BKPF: Accounting Document Header] h WITH (nolock) ON h.[BELNR: (PK) Accounting Document Number]=s.[BELNR: (PK) Accounting Document Number]

AND h.[BUKRS: (PK) Company Code] = s.[BUKRS: (PK) Company Code] AND h.[GJAHR: (PK) Fiscal Year] = s.[GJAHR: (PK) Fiscal Year]

AND MONTH(h.[BUDAT_SIMP_DT: (GC) Posting Date in the Document]) = h.[MONAT: Fiscal Period]

AND h.[MANDT: (PK) Client] = s.[MANDT: (PK) Client]

WHERE 1=1

AND h.[BUKRS: (PK) Company Code]='YourCompany'

AND h.[GJAHR: (PK) Fiscal Year]='2020'

AND s.[VORGN: Transaction Type for General Ledger]<>'RFIG'

AND s.[UMSKZ: Special G/L Indicator] NOT IN ('F','A')

GROUP BY  MONTH(h.[BUDAT: Posting Date in the Document]), h.[BUKRS: (PK) Company Code], s.[HKONT: General Ledger Account], h.[GJAHR: (PK) Fiscal Year]

ORDER BY h.[BUKRS: (PK) Company Code], s.[HKONT: General Ledger Account], h.[GJAHR: (PK) Fiscal Year] ,MONTH(h.[BUDAT: Posting Date in the Document])


Now we are using the BUKRS as the first column which is nothing but the name of the company. The next is the GLAccount which is what you actually what to see the balance monthly for. 
The GJAHR is the Actual year ; here we have filtered by 2020.
Next is BUDAT which is the data and we are taking the month from that, which is the line item we want to see for the balance. 
Next is GLAMountUSD which is the sum of the amount with a case for the debit and credit indicator. So when it's H it is to be mutipolied by  -1 so it is reversed as the amount column is not signed. 

There you so you have the monthly balance in a very condensed form. 

Thanks,
Tushar Kanti