CE_GL_AVAILABLE_TRX

Details

Schema: FUSION
Object owner: CE
Object type: VIEW

Columns

Name
TRANSACTION_SOURCE TRANSACTION_ID TRX_LINE_ID BANK_ACCOUNT_ID BA_CURRENCY_CODE TRX_CURRENCY_CODE RECON_MATCH_REFERENCE RECON_MATCH_AMOUNT RECON_MATCH_EXCHANGE_RATE RECON_MATCH_DATE RECON_MATCH_TYPE RECON_MATCH_TYPE_CODE TRX_SUBTYPE TRX_DATE TRX_AMOUNT TRANSACTION_NUMBER CLEARED_DATE PAY_RECEIPT_METHOD RECEIPT_CLASS_NAME STATUS CPARTY_NAME CPARTY_SITE CPARTY_BANK_ACCOUNT_ID PAYMENT_INSTRUCTION_ID PAYMENT_REFERENCE_ID PAYMENT_LOGICAL_GROUP_ID BATCH_NAME JOURNAL_BATCH_NAME JOURNAL_NAME RECEIPT_BATCH_NAME BANK_DEPOSIT_NUMBER BUSINESS_UNIT_ID LEGAL_ENTITY_ID RECON_FLAG RECON_HISTORY_ID EXTERNAL_TRX_SOURCE TRX_EXCHANGE_RATE TRX_EXCHANGE_RATE_TYPE TRX_EXCHANGE_DATE ACCOUNTING_FLAG JOURNAL_BATCH_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE15 CASH_CODE_COMBINATION_ID JOURNAL_LINE_DESCRIPTION

Name

TRANSACTION_SOURCE

TRANSACTION_ID

TRX_LINE_ID

BANK_ACCOUNT_ID

BA_CURRENCY_CODE

TRX_CURRENCY_CODE

RECON_MATCH_REFERENCE

RECON_MATCH_AMOUNT

RECON_MATCH_EXCHANGE_RATE

RECON_MATCH_DATE

RECON_MATCH_TYPE

RECON_MATCH_TYPE_CODE

TRX_SUBTYPE

TRX_DATE

TRX_AMOUNT

TRANSACTION_NUMBER

CLEARED_DATE

PAY_RECEIPT_METHOD

RECEIPT_CLASS_NAME

STATUS

CPARTY_NAME

CPARTY_SITE

CPARTY_BANK_ACCOUNT_ID

PAYMENT_INSTRUCTION_ID

PAYMENT_REFERENCE_ID

PAYMENT_LOGICAL_GROUP_ID

BATCH_NAME

JOURNAL_BATCH_NAME

JOURNAL_NAME

RECEIPT_BATCH_NAME

BANK_DEPOSIT_NUMBER

BUSINESS_UNIT_ID

LEGAL_ENTITY_ID

RECON_FLAG

RECON_HISTORY_ID

EXTERNAL_TRX_SOURCE

TRX_EXCHANGE_RATE

TRX_EXCHANGE_RATE_TYPE

TRX_EXCHANGE_DATE

ACCOUNTING_FLAG

JOURNAL_BATCH_ID

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

GLOBAL_ATTRIBUTE_CATEGORY

GLOBAL_ATTRIBUTE1

GLOBAL_ATTRIBUTE2

GLOBAL_ATTRIBUTE3

GLOBAL_ATTRIBUTE4

GLOBAL_ATTRIBUTE5

GLOBAL_ATTRIBUTE6

GLOBAL_ATTRIBUTE7

GLOBAL_ATTRIBUTE8

GLOBAL_ATTRIBUTE9

GLOBAL_ATTRIBUTE10

GLOBAL_ATTRIBUTE11

GLOBAL_ATTRIBUTE12

GLOBAL_ATTRIBUTE13

GLOBAL_ATTRIBUTE14

GLOBAL_ATTRIBUTE15

CASH_CODE_COMBINATION_ID

JOURNAL_LINE_DESCRIPTION

Query

SQL_Statement
SELECT /+ leading(ce_ba ce_ccid gl_jel) use_nl(ce_ccid) index(gl_jel GL_JE_LINES_N1) / 'ORA_GL' AS transaction_source , gl_jel.je_header_id AS transaction_id , gl_jel.je_line_num AS trx_line_id , ce_ba.bank_account_id AS bank_account_id , ce_ba.currency_code AS ba_currency_code , gl_jel.currency_code AS trx_currency_code , gl_jel_rec.jgzz_recon_ref AS recon_match_reference , DECODE(gl_jel.currency_code, ce_ba.currency_code, nvl(gl_jel.entered_dr,0) - nvl(gl_jel.entered_cr,0) , nvl(gl_jel.accounted_dr,0) - nvl(gl_jel.accounted_cr,0)) AS recon_match_amount , NULL AS recon_match_exchange_rate , trunc(gl_jel.effective_date) AS recon_match_date , NULL AS recon_match_type , NULL AS recon_match_type_code , NULL AS trx_subtype , trunc(gl_jel.effective_date) AS trx_date , decode(nvl(gl_jel.entered_dr,nvl(gl_jel.accounted_dr,0)),0,-1gl_jel.entered_cr,gl_jel.entered_dr) AS trx_amount , gl_jel.description AS transaction_number , NULL AS cleared_date , NULL AS pay_receipt_method , NULL AS receipt_class_name , gl_jel.status AS status , NULL AS cparty_name , NULL AS cparty_site , NULL AS cparty_bank_account_id , NULL AS payment_instruction_id , NULL AS payment_reference_id , NULL AS payment_logical_group_id , NULL AS batch_name , gl_jeb.NAME AS journal_batch_name , gl_jeh.NAME AS journal_name , NULL AS receipt_batch_name , NULL AS bank_deposit_number , to_number(NULL) AS business_unit_id , to_number(NULL) AS legal_entity_id , 'N' AS recon_flag , NULL AS recon_history_id , NULL AS external_trx_source , gl_jel.currency_conversion_rate AS trx_exchange_rate , gl_jel.currency_conversion_type AS trx_exchange_rate_type , gl_jel.currency_conversion_date AS trx_exchange_date , NULL AS accounting_flag , gl_jeb.je_batch_id AS journal_batch_id , gl_jel.attribute_category AS attribute_category , gl_jel.attribute1 AS attribute1 , gl_jel.attribute2 AS attribute2 , gl_jel.attribute3 AS attribute3 , gl_jel.attribute4 AS attribute4 , gl_jel.attribute5 AS attribute5 , gl_jel.attribute6 AS attribute6 , gl_jel.attribute7 AS attribute7 , gl_jel.attribute8 AS attribute8 , gl_jel.attribute9 AS attribute9 , gl_jel.attribute10 AS attribute10 , gl_jel.attribute11 AS attribute11 , gl_jel.attribute12 AS attribute12 , gl_jel.attribute13 AS attribute13 , gl_jel.attribute14 AS attribute14 , gl_jel.attribute15 AS attribute15 , gl_jel.global_attribute_category AS global_attribute_category , gl_jel.global_attribute1 AS global_attribute1 , gl_jel.global_attribute2 AS global_attribute2 , gl_jel.global_attribute3 AS global_attribute3 , gl_jel.global_attribute4 AS global_attribute4 , gl_jel.global_attribute5 AS global_attribute5 , gl_jel.global_attribute6 AS global_attribute6 , gl_jel.global_attribute7 AS global_attribute7 , gl_jel.global_attribute8 AS global_attribute8 , gl_jel.global_attribute9 AS global_attribute9 , gl_jel.global_attribute10 AS global_attribute10 , gl_jel.global_attribute11 AS global_attribute11 , gl_jel.global_attribute12 AS global_attribute12 , gl_jel.global_attribute13 AS global_attribute13 , gl_jel.global_attribute14 AS global_attribute14 , gl_jel.global_attribute15 AS global_attribute15 , gl_jel.code_combination_id AS cash_code_combination_id , gl_jel.description AS journal_line_description FROM gl_je_batches gl_jeb , gl_je_headers gl_jeh , gl_je_lines gl_jel , gl_je_lines_recon gl_jel_rec , ce_bank_accounts ce_ba , gl_ledger_le_v gl_lev , (SELECT bank_account_id, asset_code_combination_id, end_date FROM ce_bank_accounts WHERE asset_code_combination_id IS NOT NULL UNION SELECT cbu.bank_account_id, cgl.asset_code_combination_id, cbu.end_date FROM ce_bank_acct_uses_all cbu, ce_bank_accounts cba, ce_gl_accounts_ccid cgl WHERE cbu.bank_acct_use_id = cgl.bank_acct_use_id AND cbu.bank_account_id = cba.bank_account_id AND cgl.asset_code_combination_id IS NOT NULL AND cgl.asset_code_combination_id != nvl(cba.asset_code_combination_id, 0)) ce_ccid WHERE fnd_profile.value('CE_GL_RECON_ENABLED') = 'Y' AND nvl(ce_ba.multi_cash_recon_enabled_flag,'N') = 'N' AND ce_ba.bank_account_id = ce_ccid.bank_account_id AND gl_jel.code_combination_id = ce_ccid.asset_code_combination_id AND gl_lev.legal_entity_id = ce_ba.account_owner_org_id AND gl_lev.ledger_category_code = 'PRIMARY' AND gl_jel.ledger_id = gl_lev.ledger_id AND (0 = nvl(nvl(gl_jel.entered_dr,gl_jel.accounted_dr),0) OR 0 = nvl(nvl(gl_jel.entered_cr,gl_jel.accounted_cr),0)) AND gl_jel.effective_date >= trunc(nvl(nvl(ce_ba.gl_recon_start_date, ce_ba.recon_start_date), to_date('1970-01-01', 'YYYY-MM-DD'))) AND gl_jel.effective_date <= trunc(nvl(ce_ccid.end_date, gl_jel.effective_date)) AND gl_jel.status = 'P' AND gl_jeh.je_header_id = gl_jel.je_header_id AND gl_jel.currency_code = decode(ce_ba.currency_code, gl_lev.currency_code, gl_jel.currency_code, ce_ba.currency_code) AND gl_jel_rec.je_header_id (+) = gl_jel.je_header_id AND gl_jel_rec.je_line_num (+) = gl_jel.je_line_num AND gl_lev.ledger_id = gl_jeh.ledger_id AND gl_jeb.je_batch_id = gl_jeh.je_batch_id AND gl_jeb.actual_flag = 'A' AND gl_jeh.je_source NOT IN ('Cash Management','Receivables','Payables','Revaluation','Payroll') AND NOT EXISTS ( SELECT 1 FROM ce_recon_history_items ce_rhi WHERE ce_rhi.recon_source = 'ORA_GL' AND ce_rhi.source_id = gl_jeh.je_header_id AND ce_rhi.source_line_id = gl_jel.je_line_num) / begin FIN-1057 exclude reversed journals / AND ( cep_standard.is_feature_enabled('FSM', 'ORA_EXCLUDE_REVERSAL_JOURNAL_ENTRIES_FROM_BANK_STATEMENT_RECONCILIATION') = 'N' OR (gl_jeh.accrual_rev_je_header_id is null AND gl_jeh.reversed_je_header_id is null) ) / end FIN-1057 / union all SELECT /+ leading(ce_ba ce_ccid gl_jel) use_nl(ce_ccid) index(gl_jel GL_JE_LINES_N1) / 'ORA_GL' AS transaction_source , gl_jel.je_header_id AS transaction_id , gl_jel.je_line_num AS trx_line_id , ce_ba.bank_account_id AS bank_account_id , ce_ba.currency_code AS ba_currency_code , gl_jel.currency_code AS trx_currency_code , gl_jel_rec.jgzz_recon_ref AS recon_match_reference , DECODE(gl_jel.currency_code, ce_ba.currency_code, nvl(gl_jel.entered_dr,0) - nvl(gl_jel.entered_cr,0) , nvl(gl_jel.accounted_dr,0) - nvl(gl_jel.accounted_cr,0)) AS recon_match_amount , NULL AS recon_match_exchange_rate , trunc(gl_jel.effective_date) AS recon_match_date , NULL AS recon_match_type , NULL AS recon_match_type_code , NULL AS trx_subtype , trunc(gl_jel.effective_date) AS trx_date , decode(nvl(gl_jel.entered_dr,nvl(gl_jel.accounted_dr,0)),0,-1gl_jel.entered_cr,gl_jel.entered_dr) AS trx_amount , gl_jel.description AS transaction_number , NULL AS cleared_date , NULL AS pay_receipt_method , NULL AS receipt_class_name , gl_jel.status AS status , NULL AS cparty_name , NULL AS cparty_site , NULL AS cparty_bank_account_id , NULL AS payment_instruction_id , NULL AS payment_reference_id , NULL AS payment_logical_group_id , NULL AS batch_name , gl_jeb.NAME AS journal_batch_name , gl_jeh.NAME AS journal_name , NULL AS receipt_batch_name , NULL AS bank_deposit_number , to_number(NULL) AS business_unit_id , to_number(NULL) AS legal_entity_id , 'N' AS recon_flag , NULL AS recon_history_id , NULL AS external_trx_source , gl_jel.currency_conversion_rate AS trx_exchange_rate , gl_jel.currency_conversion_type AS trx_exchange_rate_type , gl_jel.currency_conversion_date AS trx_exchange_date , NULL AS accounting_flag , gl_jeb.je_batch_id AS journal_batch_id , gl_jel.attribute_category AS attribute_category , gl_jel.attribute1 AS attribute1 , gl_jel.attribute2 AS attribute2 , gl_jel.attribute3 AS attribute3 , gl_jel.attribute4 AS attribute4 , gl_jel.attribute5 AS attribute5 , gl_jel.attribute6 AS attribute6 , gl_jel.attribute7 AS attribute7 , gl_jel.attribute8 AS attribute8 , gl_jel.attribute9 AS attribute9 , gl_jel.attribute10 AS attribute10 , gl_jel.attribute11 AS attribute11 , gl_jel.attribute12 AS attribute12 , gl_jel.attribute13 AS attribute13 , gl_jel.attribute14 AS attribute14 , gl_jel.attribute15 AS attribute15 , gl_jel.global_attribute_category AS global_attribute_category , gl_jel.global_attribute1 AS global_attribute1 , gl_jel.global_attribute2 AS global_attribute2 , gl_jel.global_attribute3 AS global_attribute3 , gl_jel.global_attribute4 AS global_attribute4 , gl_jel.global_attribute5 AS global_attribute5 , gl_jel.global_attribute6 AS global_attribute6 , gl_jel.global_attribute7 AS global_attribute7 , gl_jel.global_attribute8 AS global_attribute8 , gl_jel.global_attribute9 AS global_attribute9 , gl_jel.global_attribute10 AS global_attribute10 , gl_jel.global_attribute11 AS global_attribute11 , gl_jel.global_attribute12 AS global_attribute12 , gl_jel.global_attribute13 AS global_attribute13 , gl_jel.global_attribute14 AS global_attribute14 , gl_jel.global_attribute15 AS global_attribute15 , gl_jel.code_combination_id AS cash_code_combination_id , gl_jel.description AS journal_line_description FROM gl_je_batches gl_jeb , gl_je_headers gl_jeh , gl_je_lines gl_jel , gl_je_lines_recon gl_jel_rec , ce_bank_accounts ce_ba , gl_ledgers gl_led , ce_bank_account_cash_ccids ce_ccid WHERE fnd_profile.value('CE_GL_RECON_ENABLED') = 'Y' AND ce_ba.multi_cash_recon_enabled_flag = 'Y' AND ce_ba.bank_account_id = ce_ccid.bank_account_id AND gl_jel.code_combination_id = ce_ccid.cash_ccid AND gl_led.ledger_id = ce_ccid.ledger_id AND gl_jel.ledger_id = ce_ccid.ledger_id AND (0 = nvl(nvl(gl_jel.entered_dr,gl_jel.accounted_dr),0) OR 0 = nvl(nvl(gl_jel.entered_cr,gl_jel.accounted_cr),0)) AND gl_jel.effective_date >= trunc(nvl(nvl(ce_ba.gl_recon_start_date, ce_ba.recon_start_date), to_date('1970-01-01', 'YYYY-MM-DD'))) AND gl_jel.status = 'P' AND gl_jeh.je_header_id = gl_jel.je_header_id AND gl_jel.currency_code = decode(ce_ba.currency_code, gl_led.currency_code, gl_jel.currency_code, ce_ba.currency_code) AND gl_jel_rec.je_header_id (+) = gl_jel.je_header_id AND gl_jel_rec.je_line_num (+) = gl_jel.je_line_num AND gl_jeh.ledger_id = ce_ccid.ledger_id AND gl_jeb.je_batch_id = gl_jeh.je_batch_id AND gl_jeb.actual_flag = 'A' AND gl_jeh.je_source NOT IN ('Cash Management','Receivables','Payables','Revaluation','Payroll') AND NOT EXISTS ( SELECT 1 FROM ce_recon_history_items ce_rhi WHERE ce_rhi.recon_source = 'ORA_GL' AND ce_rhi.source_id = gl_jeh.je_header_id AND ce_rhi.source_line_id = gl_jel.je_line_num) /* begin FIN-1057 exclude reversed journals / AND ( cep_standard.is_feature_enabled('FSM', 'ORA_EXCLUDE_REVERSAL_JOURNAL_ENTRIES_FROM_BANK_STATEMENT_RECONCILIATION') = 'N' OR (gl_jeh.accrual_rev_je_header_id is null AND gl_jeh.reversed_je_header_id is null) ) / end FIN-1057 */

SQL_Statement

SELECT /*+ leading(ce_ba ce_ccid gl_jel) use_nl(ce_ccid) index(gl_jel GL_JE_LINES_N1) */

'ORA_GL' AS transaction_source

, gl_jel.je_header_id AS transaction_id

, gl_jel.je_line_num AS trx_line_id

, ce_ba.bank_account_id AS bank_account_id

, ce_ba.currency_code AS ba_currency_code

, gl_jel.currency_code AS trx_currency_code

, gl_jel_rec.jgzz_recon_ref AS recon_match_reference

, DECODE(gl_jel.currency_code, ce_ba.currency_code, nvl(gl_jel.entered_dr,0) - nvl(gl_jel.entered_cr,0)

, nvl(gl_jel.accounted_dr,0) - nvl(gl_jel.accounted_cr,0))

AS recon_match_amount

, NULL AS recon_match_exchange_rate

, trunc(gl_jel.effective_date) AS recon_match_date

, NULL AS recon_match_type

, NULL AS recon_match_type_code

, NULL AS trx_subtype

, trunc(gl_jel.effective_date) AS trx_date

, decode(nvl(gl_jel.entered_dr,nvl(gl_jel.accounted_dr,0)),0,-1*gl_jel.entered_cr,gl_jel.entered_dr)

AS trx_amount

, gl_jel.description AS transaction_number

, NULL AS cleared_date

, NULL AS pay_receipt_method

, NULL AS receipt_class_name

, gl_jel.status AS status

, NULL AS cparty_name

, NULL AS cparty_site

, NULL AS cparty_bank_account_id

, NULL AS payment_instruction_id

, NULL AS payment_reference_id

, NULL AS payment_logical_group_id

, NULL AS batch_name

, gl_jeb.NAME AS journal_batch_name

, gl_jeh.NAME AS journal_name

, NULL AS receipt_batch_name

, NULL AS bank_deposit_number

, to_number(NULL) AS business_unit_id

, to_number(NULL) AS legal_entity_id

, 'N' AS recon_flag

, NULL AS recon_history_id

, NULL AS external_trx_source

, gl_jel.currency_conversion_rate AS trx_exchange_rate

, gl_jel.currency_conversion_type AS trx_exchange_rate_type

, gl_jel.currency_conversion_date AS trx_exchange_date

, NULL AS accounting_flag

, gl_jeb.je_batch_id AS journal_batch_id

, gl_jel.attribute_category AS attribute_category

, gl_jel.attribute1 AS attribute1

, gl_jel.attribute2 AS attribute2

, gl_jel.attribute3 AS attribute3

, gl_jel.attribute4 AS attribute4

, gl_jel.attribute5 AS attribute5

, gl_jel.attribute6 AS attribute6

, gl_jel.attribute7 AS attribute7

, gl_jel.attribute8 AS attribute8

, gl_jel.attribute9 AS attribute9

, gl_jel.attribute10 AS attribute10

, gl_jel.attribute11 AS attribute11

, gl_jel.attribute12 AS attribute12

, gl_jel.attribute13 AS attribute13

, gl_jel.attribute14 AS attribute14

, gl_jel.attribute15 AS attribute15

, gl_jel.global_attribute_category AS global_attribute_category

, gl_jel.global_attribute1 AS global_attribute1

, gl_jel.global_attribute2 AS global_attribute2

, gl_jel.global_attribute3 AS global_attribute3

, gl_jel.global_attribute4 AS global_attribute4

, gl_jel.global_attribute5 AS global_attribute5

, gl_jel.global_attribute6 AS global_attribute6

, gl_jel.global_attribute7 AS global_attribute7

, gl_jel.global_attribute8 AS global_attribute8

, gl_jel.global_attribute9 AS global_attribute9

, gl_jel.global_attribute10 AS global_attribute10

, gl_jel.global_attribute11 AS global_attribute11

, gl_jel.global_attribute12 AS global_attribute12

, gl_jel.global_attribute13 AS global_attribute13

, gl_jel.global_attribute14 AS global_attribute14

, gl_jel.global_attribute15 AS global_attribute15

, gl_jel.code_combination_id AS cash_code_combination_id

, gl_jel.description AS journal_line_description

FROM gl_je_batches gl_jeb

, gl_je_headers gl_jeh

, gl_je_lines gl_jel

, gl_je_lines_recon gl_jel_rec

, ce_bank_accounts ce_ba

, gl_ledger_le_v gl_lev

, (SELECT bank_account_id, asset_code_combination_id, end_date

FROM ce_bank_accounts

WHERE asset_code_combination_id IS NOT NULL

UNION

SELECT cbu.bank_account_id, cgl.asset_code_combination_id, cbu.end_date

FROM ce_bank_acct_uses_all cbu,

ce_bank_accounts cba,

ce_gl_accounts_ccid cgl

WHERE cbu.bank_acct_use_id = cgl.bank_acct_use_id

AND cbu.bank_account_id = cba.bank_account_id

AND cgl.asset_code_combination_id IS NOT NULL

AND cgl.asset_code_combination_id != nvl(cba.asset_code_combination_id, 0)) ce_ccid

WHERE fnd_profile.value('CE_GL_RECON_ENABLED') = 'Y'

AND nvl(ce_ba.multi_cash_recon_enabled_flag,'N') = 'N'

AND ce_ba.bank_account_id = ce_ccid.bank_account_id

AND gl_jel.code_combination_id = ce_ccid.asset_code_combination_id

AND gl_lev.legal_entity_id = ce_ba.account_owner_org_id

AND gl_lev.ledger_category_code = 'PRIMARY'

AND gl_jel.ledger_id = gl_lev.ledger_id

AND (0 = nvl(nvl(gl_jel.entered_dr,gl_jel.accounted_dr),0) OR 0 = nvl(nvl(gl_jel.entered_cr,gl_jel.accounted_cr),0))

AND gl_jel.effective_date >= trunc(nvl(nvl(ce_ba.gl_recon_start_date, ce_ba.recon_start_date), to_date('1970-01-01', 'YYYY-MM-DD')))

AND gl_jel.effective_date <= trunc(nvl(ce_ccid.end_date, gl_jel.effective_date))

AND gl_jel.status = 'P'

AND gl_jeh.je_header_id = gl_jel.je_header_id

AND gl_jel.currency_code = decode(ce_ba.currency_code, gl_lev.currency_code, gl_jel.currency_code, ce_ba.currency_code)

AND gl_jel_rec.je_header_id (+) = gl_jel.je_header_id

AND gl_jel_rec.je_line_num (+) = gl_jel.je_line_num

AND gl_lev.ledger_id = gl_jeh.ledger_id

AND gl_jeb.je_batch_id = gl_jeh.je_batch_id

AND gl_jeb.actual_flag = 'A'

AND gl_jeh.je_source NOT IN ('Cash Management','Receivables','Payables','Revaluation','Payroll')

AND NOT EXISTS (

SELECT 1 FROM ce_recon_history_items ce_rhi

WHERE ce_rhi.recon_source = 'ORA_GL'

AND ce_rhi.source_id = gl_jeh.je_header_id

AND ce_rhi.source_line_id = gl_jel.je_line_num)

/* begin FIN-1057 exclude reversed journals */

AND (

cep_standard.is_feature_enabled('FSM', 'ORA_EXCLUDE_REVERSAL_JOURNAL_ENTRIES_FROM_BANK_STATEMENT_RECONCILIATION') = 'N'

OR (gl_jeh.accrual_rev_je_header_id is null AND gl_jeh.reversed_je_header_id is null)

) /* end FIN-1057 */

union all