http://receivablesar.blogspot.com/2016/02/select-h.html
SELECT
H.NAME,
H.DEFAULT_EFFECTIVE_DATE,
H.DESCRIPTION,
H.JE_HEADER_ID,
A.JE_HEADER_ID,
H.JE_SOURCE,
H.JE_CATEGORY,
H.LEDGER_ID,
H.ACTUAL_FLAG,
H.PERIOD_NAME,
A.GL_SL_LINK_ID,
H.CODE_COMBINATION_ID,
H.ENTERED_DR,
H.ENTERED_CR
FROM
(
SELECT * FROM GL_INTERFACE
WHERE USER_JE_SOURCE_NAME='Receivables' AND USER_JE_CATEGORY_NAME='Sales Invoices' AND LEDGER_ID=2021 AND ACTUAL_FLAG='A' -- 783
) A,
(
SELECT
H.JE_SOURCE,
H.JE_CATEGORY,
H.LEDGER_ID,
H.ACTUAL_FLAG,
H.PERIOD_NAME,
gll.CODE_COMBINATION_ID,
gll.ENTERED_DR, gll.ENTERED_CR,
ael.gl_sl_link_id,
H.NAME,
H.DEFAULT_EFFECTIVE_DATE,
H.JE_HEADER_ID,
H.DESCRIPTION
FROM
GL_JE_HEADERS H,
GL_JE_LINES gll,
XLA_AE_HEADERS aeh,
XLA_AE_LINES ael,
XLA_EVENTS aea,
GL_IMPORT_REFERENCES gir
WHERE 1=1
AND H.JE_HEADER_ID=gll.JE_HEADER_ID
AND ael.gl_sl_link_id = gir.gl_sl_link_id
AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')
AND aea.application_id = 222
AND aea.event_id = aeh.event_id
AND aeh.ae_header_id = ael.ae_header_id
AND gll.je_header_id = gir.je_header_id
AND gll.je_line_num = gir.je_line_num
AND H.JE_SOURCE='Receivables'
AND H.JE_CATEGORY='Sales Invoices'
AND H.LEDGER_ID=2021
AND H.ACTUAL_FLAG='A'
--AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'
--AND H.PERIOD_NAME='JUL-09'
--AND H.DESCRIPTION LIKE '%200900160%'
) H
WHERE 1=1
AND H.JE_SOURCE=A.USER_JE_SOURCE_NAME
AND H.JE_CATEGORY=A.USER_JE_CATEGORY_NAME
AND H.LEDGER_ID=A.LEDGER_ID
AND H.ACTUAL_FLAG=A.ACTUAL_FLAG
AND H.PERIOD_NAME=A.PERIOD_NAME
--AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID
--AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)
AND H.GL_SL_LINK_ID=A.GL_SL_LINK_ID
No comments:
Post a Comment