GL BALANCES & MOVEMENTS

/* GL BALANCES & MOVEMENTS
Gives 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.
(Tested on Vision 11.5.10.2  June 2007 ) */
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 ('Pr')
--AND GCC.SEGMENT1 = '85'
--AND GCC.SEGMENT2 = '70'
--AND GCC.SEGMENT3 = '0000'
--AND GCC.SEGMENT4 IN ('99659')
--AND GCC.SEGMENT7 = 'T'
--AND    NVL(GB.TRANSLATED_FLAG,'X') != 'R'
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