SELECT
gjst.user_je_source_name source,
(
SELECT
gjct.user_je_category_name
FROM
apps.gl_je_categories_tl gjct
WHERE
gjh.je_category = gjct.je_category_name
AND language = ‘US’
) category,
gjh.name batch_name,
gjh.description header_description,
gjl.je_line_num,
gjl.description line_description,
CASE
WHEN gjl.entered_dr IS NOT NULL THEN
gcc.concatenated_segments
END debit_account_string,
CASE
WHEN gjl.entered_cr IS NOT NULL THEN
gcc.concatenated_segments
END credit_account_string,
gjh.currency_code entered_currency,
gjl.entered_dr,
gjl.entered_cr,
gjl.accounted_dr,
gjl.accounted_cr,
(
SELECT
gl.currency_code
FROM
apps.gl_ledgers gl
WHERE
gl.ledger_id = gjh.ledger_id
) accounted_cuurency,
gjh.period_name
FROM
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations_kfv gcc,
apps.gl_je_sources_tl gjst
WHERE
1 = 1
and gjh.default_effective_date BETWEEN ’01-JAN-2017′ AND ’30-SEP-2020′
AND gjh.je_source = gjst.je_source_name
AND gjst.user_je_source_name IN (
‘Manual’,
‘Spreadsheet’,
‘Cadency’
)
AND gjst.language = ‘US’
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
–AND gcc.segment1 = ‘10100’
–AND gcc.segment5 = ‘1100006’
AND gjh.ledger_id = 2021
AND gjh.je_header_id IN (
SELECT DISTINCT
gjl.je_header_id
FROM
apps.gl_je_lines gjl,
apps.gl_code_combinations_kfv gcc
WHERE
1 = 1
AND gcc.segment1 = ‘10100’
AND gcc.segment5 = ‘1100006’
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.ledger_id = 2021
)
ORDER BY
1,
3,
5
Recommended Posts
