Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, November 17, 2011

GL : ADI Journal Balances

/* GADI JOURNAL OF OPENING BALANCES & MOVEMENTS
Gives a Trial balance in ADI format for upto ten 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.
(Tested on Vision 11.5.10.2  June 2007 )*/
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 = 'DEC-05'
AND    GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HK','JP','TH','SG','CN')
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

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect