Balances and Movements

GL : Balances and Movements:-
------------------------------------

The following query will give a Trial balance with opening, movement and closing balances for upto ten segments in the chart of accounts by currency.

This can be used to as a quick method of running a trial balance for data extract in the desired format.

For example to use to extract to a third party reporting system such as Hyperion

It is recommended that this script is run for a single period and book first to gauge performance in your environment.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

SELECT SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.CODE_COMBINATION_ID
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "DISTRIBUTION"
,SUM( NVL(GB.BEGIN_BALANCE_DR,0) - NVL(GB.BEGIN_BALANCE_CR,0))"OPEN BAL"
,NVL(GB.PERIOD_NET_DR,0) "DEBIT"
,NVL(GB.PERIOD_NET_CR,0) "CREDIT"
,SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))"NET MOVEMENT"
,SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0))"CLOSE BAL"
, GB.CURRENCY_CODE
, GB.TRANSLATED_FLAG
, GB.TEMPLATE_ID
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GB.PERIOD_NAME = 'APR-04'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HB')
GROUP BY SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.CODE_COMBINATION_ID
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10
, NVL(GB.PERIOD_NET_DR,0)
, NVL(GB.PERIOD_NET_CR,0)
, GB.CURRENCY_CODE
, GB.TRANSLATED_FLAG
, GB.TEMPLATE_ID
HAVING SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) <> 0