GL--- SUBLEDGER ALL LINE DRILLDOW QUERY

There are many available reports in Oracle Apps so you can discuss the requirements related to these reports here.
Post Reply
SANJUPAL19
Posts: 6
Joined: Tue Jun 23, 2009 8:37 am
Location: India

GL--- SUBLEDGER ALL LINE DRILLDOW QUERY

Post by SANJUPAL19 »

Hi All,

I am very new to this foram,,,

I need help for a query by which i can find out all drildown lines till transaction lavel for GL balance.
As in India we hv to justify each and every tansaction but in oracele GL line are posted as group...

So i think you will help me in this sql query.

Thanks in advance,

Sanjay Khandelwal
cmagesh
Posts: 1
Joined: Tue Nov 17, 2009 1:57 am
Location: India

Post by cmagesh »

Hi,
This is wuld fetch the line Details from sub ledgers

SELECT NVL(SRC.USER_JE_SOURCE_NAME, '**********') SOURCE,
NVL(CAT.USER_JE_CATEGORY_NAME, '**********') CATEGORY,
JEL.PERIOD_NAME PERIOD_NAME,
GJBH.BATCH_NAME BATCH_NAME,
GJBH.HEADER_NAME HEADER_NAME,
JEL.DESCRIPTION DESCRIPTION,
GJBH.CURRENCY_CODE,
to_char(JEL.EFFECTIVE_DATE,'Mon-YY') EFF_DATE,
JEL.EFFECTIVE_DATE EFFECTIVE_DATE,
CC.SEGMENT7 NATURAL_ACCOUNT_CODE,
JEL.ATTRIBUTE10 PROJECT_NO,
TO_CHAR(TO_DATE(JEL.ATTRIBUTE8, 'YYYY-MM-DD HH24:MI:SS'), 'DD-MON-YY') EXP_ITEM_DATE
FROM GL_CODE_COMBINATIONS CC,
GL_JE_LINES JEL,
GL_JE_BATCHES_HEADERS_V GJBH,
GL_JE_CATEGORIES_tl CAT,
GL_JE_SOURCES SRC,
FND_USER FU
WHERE GJBH.PERIOD_NAME IN ('Nov-09','Dec-09')
AND CC.SEGMENT7= --A/c code
AND CC.CHART_OF_ACCOUNTS_ID = 101
AND JEL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND JEL.STATUS || '' = 'P'
AND GJBH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND GJBH.ACTUAL_FLAG = 'A'
AND GJBH.JE_BATCH_ID = GJBH.JE_BATCH_ID
AND GJBH.SET_OF_BOOKS_ID = 1
AND GJBH.AVERAGE_JOURNAL_FLAG = 'N'
AND SRC.JE_SOURCE_NAME = GJBH.JE_SOURCE
AND CAT.JE_CATEGORY_NAME = GJBH.JE_CATEGORY
-- AND GJBH.JE_SOURCE NOT IN ('Receivables','Payables','Purchasing')
AND GJBH.CREATED_BY = FU.USER_ID(+)
Post Reply

Who is online

Users browsing this forum: Google [Bot] and 0 guests