ADI Journal Balances

GL : ADI Journal Balances:-
-------------------------------

The following query gives a Trial balance in ADI format for upto 10 segments in the chart of accounts with Debit and Credit Balance.

This can be used to extract GL balances data from one environment in and ADI Journal format to load into another environment.

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

I have tested this script in Vision 11.5.10.2 (This will work in all the 11i Instances).

In the following select statement, I have commented PERIOD and SOB condition. Check in your Instances and run according to your requirment.

SELECT SOB.NAME
,    GB.PERIOD_NAME
,    GCC.SEGMENT1
,    GCC.SEGMENT2
,    GCC.SEGMENT3
,    GCC.SEGMENT4
,    GCC.SEGMENT5
,    GCC.SEGMENT6
,    GCC.SEGMENT7
,    GCC.SEGMENT8
,    GCC.SEGMENT9
,    GCC.SEGMENT10 
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) >= 0 
       THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)))  
    ELSE 0 END ) "DEBIT" 
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <= 0 
       THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))*-1)  
    ELSE 0 END )  "CREDIT"
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.PERIOD_NAME = 'JAN-08'
AND    GB.CURRENCY_CODE = SOB.CURRENCY_CODE
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HB')
AND  GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY  SOB.NAME
,    GB.ACTUAL_FLAG
,    GB.PERIOD_NAME
,    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)
HAVING SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <> 0
ORDER BY 1,2,3,4,5,6,7,8,9