eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CE_101_TRANSACTIONS_V

Source

View Text - Preformatted


SELECT jel.ROWID , 'N' , aba.bank_account_id , aba.bank_account_name , aba.bank_account_num , bankparty.party_name , branchparty.party_name , jel.je_line_num , DECODE(DECODE(NVL(jel.entered_dr, 0),0, NVL (jel.accounted_dr, 0),jel.entered_dr),0,'PAYMENT','CASH') , l2.meaning , jel.description , jeh.currency_code , DECODE(NVL(jel.entered_dr,NVL(jel.accounted_dr,0)),0,jel.entered_cr,jel.entered_dr) , DECODE(jeh.currency_code,sob.currency_code,DECODE(NVL(jel.entered_dr,NVL(jel.accounted_dr, 0)),0,jel.entered_cr,jel.entered_dr), aba.currency_code,DECODE(NVL(jel.entered_dr,NVL(jel.accounted_dr, 0)),0, jel.entered_cr,jel.entered_dr), DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr)) , TO_NUMBER (NULL) , jel.effective_date , l1.meaning , jel.status , jel.description , jel.effective_date , TO_DATE (NULL) , TO_DATE (NULL) , jeh.currency_conversion_date , jeh.currency_conversion_type , glcc.user_conversion_type , jeh.currency_conversion_rate , TO_NUMBER (NULL) , TO_NUMBER (NULL) , jeb.NAME , jeb.je_batch_id , NULL , NULL , NULL , NULL , TO_NUMBER (NULL) , TO_NUMBER (NULL) , NULL , NULL , TO_NUMBER (NULL) , DECODE(cre.status_flag,'M',DECODE (jeh.currency_code,sob.currency_code,DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr), aba.currency_code,DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr), DECODE(NVL(jel.entered_dr,0),0,jel.accounted_cr,jel.accounted_dr))) , jel.creation_date , jel.created_by , jel.last_update_date , jel.last_updated_by , NULL , jeh.je_header_id , 101 , 0 , aba.currency_code , DECODE (jeh.currency_code,sob.currency_code, 'FUNCTIONAL',aba.currency_code, 'BANK','FOREIGN') , jel.code_combination_id , jel.period_name , jeh.NAME , jeh.doc_sequence_value , jel.je_line_num , 'JE_LINE' , jec.user_je_category_name , DECODE (NVL (jel.entered_dr, NVL (jel.accounted_dr, 0)),0, jel.accounted_cr,jel.accounted_dr) , TO_NUMBER (NULL) , TO_NUMBER (NULL) , NULL , TO_DATE (NULL) , NULL , TO_NUMBER (NULL) , TO_DATE (NULL) , TO_NUMBER (NULL) , TO_NUMBER (NULL) , TO_NUMBER (NULL) FROM gl_daily_conversion_types glcc, gl_je_lines jel, ce_lookups l2, gl_lookups l1, gl_ledgers sob, /* 14749240 - Changed from gl_sets_of_books */ ce_system_parameters SYS, gl_je_categories jec, gl_je_batches jeb, gl_je_headers jeh, ce_bank_accounts aba, hz_parties bankparty, hz_parties branchparty, gl_period_statuses gps, ce_statement_reconcils_all cre WHERE glcc.conversion_type = jeh.currency_conversion_type AND l2.lookup_type = 'TRX_TYPE' AND l2.lookup_code = DECODE (DECODE (NVL (jel.entered_dr, 0),0, NVL (jel.accounted_dr, 0),jel.entered_dr),0, 'JE_CREDIT','JE_DEBIT') AND l1.lookup_type = 'MJE_BATCH_STATUS' AND l1.lookup_code = jel.status AND aba.account_classification = 'INTERNAL' AND NVL (aba.netting_acct_flag, 'N') = 'N' AND sob.object_type_code = 'L' /* 14749240 Added */ AND NVL (sob.complete_flag, 'Y') = 'Y' /* 14749240 Added */ AND DECODE (aba.currency_code,sob.currency_code, jeh.currency_code,aba.currency_code) = jeh.currency_code AND aba.bank_branch_id = branchparty.party_id AND aba.bank_id = bankparty.party_id AND aba.account_owner_org_id = SYS.legal_entity_id AND jec.je_category_name = jeh.je_category AND jeb.je_batch_id = jeh.je_batch_id AND jeh.je_header_id = jel.je_header_id AND jeh.je_source NOT IN ('Payables', 'Receivables', 'AP Translator', 'AR Translator','Treasury', 'Cash Management', 'Payroll', 'Consolidation') AND cre.je_header_id(+) = jel.je_header_id AND cre.reference_id(+) = jel.je_line_num AND cre.reference_type(+) = 'JE_LINE' AND NVL (cre.status_flag, 'U') = 'U' AND NVL (cre.current_record_flag, 'Y') = 'Y' AND '' || jel.status(+) = 'P' /* 14729240 - fetch for ccids attached at bank account use level */ AND jel.code_combination_id in ( SELECT cgl.ap_asset_ccid FROM ce_bank_acct_uses_all cbu, ce_gl_accounts_ccid cgl WHERE cbu.bank_account_id = aba.bank_id AND cgl.bank_acct_use_id = cbu.bank_acct_use_id AND cbu.ap_use_enable_flag = 'Y' UNION ALL SELECT cgl.ar_asset_ccid FROM ce_bank_acct_uses_all cbu, ce_gl_accounts_ccid cgl WHERE cbu.bank_account_id = aba.bank_id AND cgl.bank_acct_use_id = cbu.bank_acct_use_id AND cbu.ar_use_enable_flag = 'Y') AND jel.period_name = gps.period_name AND gps.application_id = 101 AND gps.set_of_books_id = sob.ledger_id /* 14749240 - Changed from sob.sets_of_books_id */ AND sob.ledger_id = SYS.set_of_books_id /* 14749240 - Changed from sob.sets_of_books_id */ AND jel.ledger_id = SYS.set_of_books_id AND jel.effective_date >= SYS.cashbook_begin_date AND jeb.actual_flag = 'A' AND je_category <> 'Revaluation' /* Bug 11931023 */ AND EXISTS ( SELECT /*+ push_subq */ 1 FROM ce_bank_acct_uses_all bau, ce_security_profiles_gt ou WHERE aba.bank_account_id = bau.bank_account_id AND ( (bau.org_id = ou.organization_id AND ou.organization_type IN ('OPERATING_UNIT', 'BUSINESS_GROUP')) OR (bau.legal_entity_id = ou.organization_id AND ou.organization_type = 'LEGAL_ENTITY')) ) UNION SELECT jel.ROWID , 'N' , aba.bank_account_id , aba.bank_account_name , aba.bank_account_num , bankparty.party_name , branchparty.party_name , jel.je_line_num , DECODE(DECODE(NVL(jel.entered_dr, 0),0, NVL (jel.accounted_dr, 0),jel.entered_dr),0,'PAYMENT','CASH') , l2.meaning , jel.description , jeh.currency_code , DECODE(NVL(jel.entered_dr,NVL(jel.accounted_dr,0)),0,jel.entered_cr,jel.entered_dr) , DECODE(jeh.currency_code,sob.currency_code,DECODE(NVL(jel.entered_dr,NVL(jel.accounted_dr, 0)),0,jel.entered_cr,jel.entered_dr), aba.currency_code,DECODE(NVL(jel.entered_dr,NVL(jel.accounted_dr, 0)),0, jel.entered_cr,jel.entered_dr), DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr)) , TO_NUMBER (NULL) , jel.effective_date , l1.meaning , jel.status , jel.description , jel.effective_date , TO_DATE (NULL) , TO_DATE (NULL) , jeh.currency_conversion_date , jeh.currency_conversion_type , glcc.user_conversion_type , jeh.currency_conversion_rate , TO_NUMBER (NULL) , TO_NUMBER (NULL) , jeb.NAME , jeb.je_batch_id , NULL , NULL , NULL , NULL , TO_NUMBER (NULL) , TO_NUMBER (NULL) , NULL , NULL , TO_NUMBER (NULL) , DECODE(cre.status_flag,'M',DECODE (jeh.currency_code,sob.currency_code,DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr), aba.currency_code,DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr), DECODE(NVL(jel.entered_dr,0),0,jel.accounted_cr,jel.accounted_dr))) , jel.creation_date , jel.created_by , jel.last_update_date , jel.last_updated_by , NULL , jeh.je_header_id , 101 , 0 , aba.currency_code , DECODE (jeh.currency_code,sob.currency_code, 'FUNCTIONAL',aba.currency_code, 'BANK','FOREIGN') , jel.code_combination_id , jel.period_name , jeh.NAME , jeh.doc_sequence_value , jel.je_line_num , 'JE_LINE' , jec.user_je_category_name , DECODE (NVL (jel.entered_dr, NVL (jel.accounted_dr, 0)),0, jel.accounted_cr,jel.accounted_dr) , TO_NUMBER (NULL) , TO_NUMBER (NULL) , NULL , TO_DATE (NULL) , NULL , TO_NUMBER (NULL) , TO_DATE (NULL) , TO_NUMBER (NULL) , TO_NUMBER (NULL) , TO_NUMBER (NULL) FROM gl_daily_conversion_types glcc, gl_je_lines jel, ce_lookups l2, gl_lookups l1, gl_ledgers sob, /* 14749240 - Changed from gl_sets_of_books */ ce_system_parameters SYS, gl_je_categories jec, gl_je_batches jeb, gl_je_headers jeh, ce_bank_accounts aba, hz_parties bankparty, hz_parties branchparty, gl_period_statuses gps, ce_statement_reconcils_all cre WHERE glcc.conversion_type = jeh.currency_conversion_type AND l2.lookup_type = 'TRX_TYPE' AND l2.lookup_code = DECODE (DECODE (NVL (jel.entered_dr, 0),0, NVL (jel.accounted_dr, 0),jel.entered_dr),0, 'JE_CREDIT','JE_DEBIT') AND l1.lookup_type = 'MJE_BATCH_STATUS' AND l1.lookup_code = jel.status AND aba.account_classification = 'INTERNAL' AND NVL (aba.netting_acct_flag, 'N') = 'N' AND sob.object_type_code = 'L' /* 14749240 Added */ AND NVL (sob.complete_flag, 'Y') = 'Y' /* 14749240 Added */ AND DECODE (aba.currency_code,sob.currency_code, jeh.currency_code,aba.currency_code) = jeh.currency_code AND aba.bank_branch_id = branchparty.party_id AND aba.bank_id = bankparty.party_id AND aba.account_owner_org_id = SYS.legal_entity_id AND jec.je_category_name = jeh.je_category AND jeb.je_batch_id = jeh.je_batch_id AND jeh.je_header_id = jel.je_header_id AND jeh.je_source NOT IN ('Payables', 'Receivables', 'AP Translator', 'AR Translator','Treasury', 'Cash Management', 'Payroll', 'Consolidation') AND cre.je_header_id(+) = jel.je_header_id AND cre.reference_id(+) = jel.je_line_num AND cre.reference_type(+) = 'JE_LINE' AND NVL (cre.status_flag, 'U') = 'U' AND NVL (cre.current_record_flag, 'Y') = 'Y' AND '' || jel.status(+) = 'P' /* 14729240 - fetch for ccid attached at bank account level */ AND jel.code_combination_id= aba.asset_code_combination_id AND jel.period_name = gps.period_name AND gps.application_id = 101 AND gps.set_of_books_id = sob.ledger_id /* 14749240 - Changed from sob.sets_of_books_id */ AND sob.ledger_id = SYS.set_of_books_id /* 14749240 - Changed from sob.sets_of_books_id */ AND jel.ledger_id = SYS.set_of_books_id AND jel.effective_date >= SYS.cashbook_begin_date AND jeb.actual_flag = 'A' AND je_category <> 'Revaluation' /* Bug 11931023 */ AND EXISTS ( SELECT /*+ push_subq */ 1 FROM ce_bank_acct_uses_all bau, ce_security_profiles_gt ou WHERE aba.bank_account_id = bau.bank_account_id AND ( (bau.org_id = ou.organization_id AND ou.organization_type IN ('OPERATING_UNIT', 'BUSINESS_GROUP')) OR (bau.legal_entity_id = ou.organization_id AND ou.organization_type = 'LEGAL_ENTITY')) )

View Text - HTML Formatted

SELECT JEL.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, BANKPARTY.PARTY_NAME
, BRANCHPARTY.PARTY_NAME
, JEL.JE_LINE_NUM
, DECODE(DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, NVL (JEL.ACCOUNTED_DR
, 0)
, JEL.ENTERED_DR)
, 0
, 'PAYMENT'
, 'CASH')
, L2.MEANING
, JEL.DESCRIPTION
, JEH.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ACCOUNTED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR))
, TO_NUMBER (NULL)
, JEL.EFFECTIVE_DATE
, L1.MEANING
, JEL.STATUS
, JEL.DESCRIPTION
, JEL.EFFECTIVE_DATE
, TO_DATE (NULL)
, TO_DATE (NULL)
, JEH.CURRENCY_CONVERSION_DATE
, JEH.CURRENCY_CONVERSION_TYPE
, GLCC.USER_CONVERSION_TYPE
, JEH.CURRENCY_CONVERSION_RATE
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, JEB.NAME
, JEB.JE_BATCH_ID
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, NULL
, TO_NUMBER (NULL)
, DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE (JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR)))
, JEL.CREATION_DATE
, JEL.CREATED_BY
, JEL.LAST_UPDATE_DATE
, JEL.LAST_UPDATED_BY
, NULL
, JEH.JE_HEADER_ID
, 101
, 0
, ABA.CURRENCY_CODE
, DECODE (JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, JEL.CODE_COMBINATION_ID
, JEL.PERIOD_NAME
, JEH.NAME
, JEH.DOC_SEQUENCE_VALUE
, JEL.JE_LINE_NUM
, 'JE_LINE'
, JEC.USER_JE_CATEGORY_NAME
, DECODE (NVL (JEL.ENTERED_DR
, NVL (JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, TO_DATE (NULL)
, NULL
, TO_NUMBER (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
FROM GL_DAILY_CONVERSION_TYPES GLCC
, GL_JE_LINES JEL
, CE_LOOKUPS L2
, GL_LOOKUPS L1
, GL_LEDGERS SOB
, /* 14749240 - CHANGED
FROM GL_SETS_OF_BOOKS */ CE_SYSTEM_PARAMETERS SYS
, GL_JE_CATEGORIES JEC
, GL_JE_BATCHES JEB
, GL_JE_HEADERS JEH
, CE_BANK_ACCOUNTS ABA
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, GL_PERIOD_STATUSES GPS
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE GLCC.CONVERSION_TYPE = JEH.CURRENCY_CONVERSION_TYPE
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = DECODE (DECODE (NVL (JEL.ENTERED_DR
, 0)
, 0
, NVL (JEL.ACCOUNTED_DR
, 0)
, JEL.ENTERED_DR)
, 0
, 'JE_CREDIT'
, 'JE_DEBIT')
AND L1.LOOKUP_TYPE = 'MJE_BATCH_STATUS'
AND L1.LOOKUP_CODE = JEL.STATUS
AND ABA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND NVL (ABA.NETTING_ACCT_FLAG
, 'N') = 'N'
AND SOB.OBJECT_TYPE_CODE = 'L' /* 14749240 ADDED */
AND NVL (SOB.COMPLETE_FLAG
, 'Y') = 'Y' /* 14749240 ADDED */
AND DECODE (ABA.CURRENCY_CODE
, SOB.CURRENCY_CODE
, JEH.CURRENCY_CODE
, ABA.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND JEC.JE_CATEGORY_NAME = JEH.JE_CATEGORY
AND JEB.JE_BATCH_ID = JEH.JE_BATCH_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_SOURCE NOT IN ('PAYABLES'
, 'RECEIVABLES'
, 'AP TRANSLATOR'
, 'AR TRANSLATOR'
, 'TREASURY'
, 'CASH MANAGEMENT'
, 'PAYROLL'
, 'CONSOLIDATION')
AND CRE.JE_HEADER_ID(+) = JEL.JE_HEADER_ID
AND CRE.REFERENCE_ID(+) = JEL.JE_LINE_NUM
AND CRE.REFERENCE_TYPE(+) = 'JE_LINE'
AND NVL (CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL (CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND '' || JEL.STATUS(+) = 'P' /* 14729240 - FETCH FOR CCIDS ATTACHED AT BANK ACCOUNT USE LEVEL */
AND JEL.CODE_COMBINATION_ID IN ( SELECT CGL.AP_ASSET_CCID
FROM CE_BANK_ACCT_USES_ALL CBU
, CE_GL_ACCOUNTS_CCID CGL
WHERE CBU.BANK_ACCOUNT_ID = ABA.BANK_ID
AND CGL.BANK_ACCT_USE_ID = CBU.BANK_ACCT_USE_ID
AND CBU.AP_USE_ENABLE_FLAG = 'Y' UNION ALL SELECT CGL.AR_ASSET_CCID
FROM CE_BANK_ACCT_USES_ALL CBU
, CE_GL_ACCOUNTS_CCID CGL
WHERE CBU.BANK_ACCOUNT_ID = ABA.BANK_ID
AND CGL.BANK_ACCT_USE_ID = CBU.BANK_ACCT_USE_ID
AND CBU.AR_USE_ENABLE_FLAG = 'Y')
AND JEL.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = SOB.LEDGER_ID /* 14749240 - CHANGED
FROM SOB.SETS_OF_BOOKS_ID */
AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID /* 14749240 - CHANGED
FROM SOB.SETS_OF_BOOKS_ID */
AND JEL.LEDGER_ID = SYS.SET_OF_BOOKS_ID
AND JEL.EFFECTIVE_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND JEB.ACTUAL_FLAG = 'A'
AND JE_CATEGORY <> 'REVALUATION' /* BUG 11931023 */
AND EXISTS ( SELECT /*+ PUSH_SUBQ */ 1
FROM CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
WHERE ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND ( (BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE IN ('OPERATING_UNIT'
, 'BUSINESS_GROUP')) OR (BAU.LEGAL_ENTITY_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'LEGAL_ENTITY')) ) UNION SELECT JEL.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, BANKPARTY.PARTY_NAME
, BRANCHPARTY.PARTY_NAME
, JEL.JE_LINE_NUM
, DECODE(DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, NVL (JEL.ACCOUNTED_DR
, 0)
, JEL.ENTERED_DR)
, 0
, 'PAYMENT'
, 'CASH')
, L2.MEANING
, JEL.DESCRIPTION
, JEH.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ACCOUNTED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR))
, TO_NUMBER (NULL)
, JEL.EFFECTIVE_DATE
, L1.MEANING
, JEL.STATUS
, JEL.DESCRIPTION
, JEL.EFFECTIVE_DATE
, TO_DATE (NULL)
, TO_DATE (NULL)
, JEH.CURRENCY_CONVERSION_DATE
, JEH.CURRENCY_CONVERSION_TYPE
, GLCC.USER_CONVERSION_TYPE
, JEH.CURRENCY_CONVERSION_RATE
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, JEB.NAME
, JEB.JE_BATCH_ID
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, NULL
, TO_NUMBER (NULL)
, DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE (JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR)))
, JEL.CREATION_DATE
, JEL.CREATED_BY
, JEL.LAST_UPDATE_DATE
, JEL.LAST_UPDATED_BY
, NULL
, JEH.JE_HEADER_ID
, 101
, 0
, ABA.CURRENCY_CODE
, DECODE (JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, JEL.CODE_COMBINATION_ID
, JEL.PERIOD_NAME
, JEH.NAME
, JEH.DOC_SEQUENCE_VALUE
, JEL.JE_LINE_NUM
, 'JE_LINE'
, JEC.USER_JE_CATEGORY_NAME
, DECODE (NVL (JEL.ENTERED_DR
, NVL (JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, TO_DATE (NULL)
, NULL
, TO_NUMBER (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
FROM GL_DAILY_CONVERSION_TYPES GLCC
, GL_JE_LINES JEL
, CE_LOOKUPS L2
, GL_LOOKUPS L1
, GL_LEDGERS SOB
, /* 14749240 - CHANGED
FROM GL_SETS_OF_BOOKS */ CE_SYSTEM_PARAMETERS SYS
, GL_JE_CATEGORIES JEC
, GL_JE_BATCHES JEB
, GL_JE_HEADERS JEH
, CE_BANK_ACCOUNTS ABA
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, GL_PERIOD_STATUSES GPS
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE GLCC.CONVERSION_TYPE = JEH.CURRENCY_CONVERSION_TYPE
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = DECODE (DECODE (NVL (JEL.ENTERED_DR
, 0)
, 0
, NVL (JEL.ACCOUNTED_DR
, 0)
, JEL.ENTERED_DR)
, 0
, 'JE_CREDIT'
, 'JE_DEBIT')
AND L1.LOOKUP_TYPE = 'MJE_BATCH_STATUS'
AND L1.LOOKUP_CODE = JEL.STATUS
AND ABA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND NVL (ABA.NETTING_ACCT_FLAG
, 'N') = 'N'
AND SOB.OBJECT_TYPE_CODE = 'L' /* 14749240 ADDED */
AND NVL (SOB.COMPLETE_FLAG
, 'Y') = 'Y' /* 14749240 ADDED */
AND DECODE (ABA.CURRENCY_CODE
, SOB.CURRENCY_CODE
, JEH.CURRENCY_CODE
, ABA.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND JEC.JE_CATEGORY_NAME = JEH.JE_CATEGORY
AND JEB.JE_BATCH_ID = JEH.JE_BATCH_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_SOURCE NOT IN ('PAYABLES'
, 'RECEIVABLES'
, 'AP TRANSLATOR'
, 'AR TRANSLATOR'
, 'TREASURY'
, 'CASH MANAGEMENT'
, 'PAYROLL'
, 'CONSOLIDATION')
AND CRE.JE_HEADER_ID(+) = JEL.JE_HEADER_ID
AND CRE.REFERENCE_ID(+) = JEL.JE_LINE_NUM
AND CRE.REFERENCE_TYPE(+) = 'JE_LINE'
AND NVL (CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL (CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND '' || JEL.STATUS(+) = 'P' /* 14729240 - FETCH FOR CCID ATTACHED AT BANK ACCOUNT LEVEL */
AND JEL.CODE_COMBINATION_ID= ABA.ASSET_CODE_COMBINATION_ID
AND JEL.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = SOB.LEDGER_ID /* 14749240 - CHANGED
FROM SOB.SETS_OF_BOOKS_ID */
AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID /* 14749240 - CHANGED
FROM SOB.SETS_OF_BOOKS_ID */
AND JEL.LEDGER_ID = SYS.SET_OF_BOOKS_ID
AND JEL.EFFECTIVE_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND JEB.ACTUAL_FLAG = 'A'
AND JE_CATEGORY <> 'REVALUATION' /* BUG 11931023 */
AND EXISTS ( SELECT /*+ PUSH_SUBQ */ 1
FROM CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
WHERE ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND ( (BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE IN ('OPERATING_UNIT'
, 'BUSINESS_GROUP')) OR (BAU.LEGAL_ENTITY_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'LEGAL_ENTITY')) )