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 JOURNAL BASED TRIAL BALANCE

/* GL JOURNAL BASED TRIAL BALANCE
Creates a trial balance based on Journal Lines.  Can be used for nervous data conversion managers as you can see the impact
of journals on account balances without the need to post the journals.
(Tested on Vision 11.5.10.2  June 2007 ) */
SELECT SOB.SHORT_NAME
,    SOB.NAME
,    GJH.NAME
,    GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9 "ACCOUNT"
,    GJH.CURRENCY_CODE
,    SUM(GJL.ACCOUNTED_DR)"DR"
,    SUM(GJL.ACCOUNTED_CR)"CR"
,    SUM( NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))"END BALANCE"
,    GJL.PERIOD_NAME
FROM GL_JE_LINES GJL
,  GL_JE_HEADERS GJH
,   GL_CODE_COMBINATIONS GCC
,  GL_SETS_OF_BOOKS SOB
WHERE GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = GJL.SET_OF_BOOKS_ID
AND GJL.PERIOD_NAME = 'JUL-03'
--AND SOB.SHORT_NAME = 'GBMAN'
--AND GJH.NAME LIKE '%PPL%'
--AND GCC.SEGMENT1 = '85'
--AND GCC.SEGMENT2 = '70'
--AND GCC.SEGMENT3 = '0000'
--AND GCC.SEGMENT4 = '88165'
--AND GJH.STATUS = 'P'
--AND GJL.EFFECTIVE_DATE >= TO_DATE('06/04/2002','DD/MM/YYYY')
--AND GJL.EFFECTIVE_DATE <= TO_DATE('30/11/2002','DD/MM/YYYY')
GROUP BY SOB.SHORT_NAME, SOB.NAME, GJH.NAME
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9
,GJH.CURRENCY_CODE, GJL.PERIOD_NAME

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