Introduction
This will displays the journal entry details
SELECT DISTINCT
–LEG_ENT.COUNTRY,
(select DISTINCT
HRL.COUNTRY
from apps.XLE_ENTITY_PROFILES LEP,
apps.XLE_REGISTRATIONS REG,
apps.HR_LOCATIONS_ALL HRL,
apps.gl_ledgers gl,
apps.HR_OPERATING_UNITS HRO
where LEP.TRANSACTING_ENTITY_FLAG = ‘Y’
and LEP.LEGAL_ENTITY_ID = REG.SOURCE_ID
and REG.SOURCE_TABLE = ‘XLE_ENTITY_PROFILES’
and HRL.LOCATION_ID = REG.LOCATION_ID
and REG.IDENTIFYING_FLAG = ‘Y’
and HRO.SET_OF_BOOKS_ID=GL.LEDGER_ID
AND GL.LEDGER_ID=GLJH.ledger_id
and LEP.LEGAL_ENTITY_ID = HRO.DEFAULT_LEGAL_CONTEXT_ID
)COUNTRY,
–LEG_ENT.NAME LEDGER_NAME,
(SELECT NAME FROM apps.gl_ledgers where ledger_id=GLJH.ledger_id)LEDGER_NAME,
(SELECT LEDGER_CATEGORY_CODE FROM apps.gl_ledgers where ledger_id=GLJH.ledger_id)LEDGER_CATEGORY,
GLJH.PERIOD_NAME,
GLJB.NAME BATCH_NAME,
GLJH.NAME JOURNAL_NAME,
REPLACE(REPLACE(GLJH.DESCRIPTION,CHR(13),”),CHR(10),”)JOURNAL_DESCRIPTION,
DECODE(GLJH.STATUS ,’u’,’unposted’,GLJH.STATUS) STATUS,
gljh.creation_date DATE_CREATED,
gljb.POSTED_DATE,
(select USER_JE_CATEGORY_NAME from apps.gl_je_categories where JE_CATEGORY_NAME=GLJH.JE_CATEGORY)CATEGORY,
(select USER_JE_SOURCE_NAME from apps.gl_je_sources where JE_SOURCE_NAME=GLJH.JE_SOURCE)SOURCE,
Case when (select USER_JE_SOURCE_NAME from apps.gl_je_sources where JE_SOURCE_NAME=GLJH.JE_SOURCE) = ‘Cadency’ then
(select ATTRIBUTE2 from apps.GL_JE_LINES WHERE JE_header_ID=GLJH.JE_header_ID and ATTRIBUTE2 is not null and ROWNUM<2)
else (SELECT user_name FROM APPS.FND_USER WHERE USER_ID=GLJH.CREATED_BY) end as PREPARER,
Case when (select USER_JE_SOURCE_NAME from apps.gl_je_sources where JE_SOURCE_NAME=GLJH.JE_SOURCE) = ‘Cadency’ then
(select ATTRIBUTE3 from apps.GL_JE_LINES WHERE JE_header_ID=GLJH.JE_header_ID and ATTRIBUTE3 is not null and ROWNUM<2)
else (SELECT user_name FROM APPS.FND_USER WHERE USER_ID=GLJB.POSTED_BY) end as APPROVER,
GLJH.CURRENCY_CODE ENTERED_CURRENCY,
–LEG_ENT.CURRENCY_CODE ACCOUNTED_CURRENCY,
(SELECT CURRENCY_CODE FROM apps.gl_ledgers where ledger_id=GLJH.ledger_id)ACCOUNTED_CURRENCY,
GLJH.RUNNING_TOTAL_DR “ENTERED AMOUNT DR”,
GLJH.RUNNING_TOTAL_CR “ENTERED AMOUNT CR”,
GLJH.RUNNING_TOTAL_ACCOUNTED_DR “ACCOUNTED AMOUNT DR”,
GLJH.RUNNING_TOTAL_ACCOUNTED_CR “ACCOUNTED AMOUNT CR”,
Case
WHEN (select conversion_rate
from apps.gl_daily_rates_v a
where a.from_currency =GLJH.CURRENCY_CODE
and a.to_currency=’USD’
and a.conversion_date=GLJH.currency_conversion_date and a.USER_CONVERSION_TYPE=’Corporate’) IS NULL THEN 1
ELSE (select ROUND(conversion_rate,4)
from apps.gl_daily_rates_v a
where a.from_currency =GLJH.CURRENCY_CODE
and a.to_currency=’USD’
and a.conversion_date=GLJH.currency_conversion_date and a.USER_CONVERSION_TYPE=’Corporate’)
end as Accounted_Currency_To_USD_Rate
FROM
apps.GL_JE_HEADERS GLJH,
apps.GL_JE_BATCHES GLJB
WHERE
GLJB.JE_BATCH_ID = GLJH.JE_BATCH_ID
AND GLJH.LEDGER_ID=NVL(:P_LEDGER_ID,GLJH.LEDGER_ID)
AND GLJH.STATUS = ‘P’
AND GLJH.PERIOD_NAME =NVL(:P_PERIOD_NAME,GLJH.PERIOD_NAME)
ORDER BY GLJH.PERIOD_NAME
Summary
This report is used to identify the journal entry details
