1. Tables and Views for Financials
  2. CE_XLA_EXT_EXTRACT_V

CE_XLA_EXT_EXTRACT_V

Details

  • Schema: FUSION

  • Object owner: CE

  • Object type: VIEW

Columns

Name

EVENT_ID

EXT_TRANSACTION_ID

ACCT_HISTORY_ID

DISTRIBUTION_TYPE

ACCOUNTING_EVENT_TYPE

ACCOUNTING_DATE

ACCOUNTING_EVENT_STATUS

REVERSAL_INDICATOR

REVERSAL_DIST_TYPE

REVERSAL_FIRST_DIST_ID

REVERSAL_SECOND_DIST_ID

EXT_LEDGER_NAME

EXT_LE_NAME

EXT_BANK_ACCOUNT_NUM

EXT_DIRECTION

EXT_CURRENCY

EXT_TRX_DATE

EXT_DESCRIPTION

EXT_RECON_REFERENCE

EXT_TRX_TYPE

EXT_TRX_SUBTYPE

EXT_VALUE_DATE

EXT_OFFSET_CCID

EXT_ASSET_CCID

EXT_STATUS

EXT_SOURCE

EXT_BUSINESS_UNIT

CLEARED_DATE

CLEARED_AMOUNT

CLEARED_EXCHANGE_RATE

CLEARED_EXCHANGE_DATE

CLEARED_EXCHANGE_RATE_TYPE

EXT_LE_COUNTRY

EXT_BANK_NAME

EXT_BANK_NUMBER

EXT_BRANCH_NAME

EXT_BRANCH_NUMBER

EXT_BRANCH_COUNTRY

EXT_BANK_ACCOUNT_NAME

EXT_BA_CURRENCY

EXT_BA_DESCRIPTION

EXT_BA_ATTRIBUTE_CATEGORY

EXT_BA_ATTRIBUTE1

EXT_BA_ATTRIBUTE2

EXT_BA_ATTRIBUTE3

EXT_BA_ATTRIBUTE4

EXT_BA_ATTRIBUTE5

EXT_BA_ATTRIBUTE6

EXT_BA_ATTRIBUTE7

EXT_BA_ATTRIBUTE8

EXT_BA_ATTRIBUTE9

EXT_BA_ATTRIBUTE10

EXT_BA_ATTRIBUTE11

EXT_BA_ATTRIBUTE12

EXT_BA_ATTRIBUTE13

EXT_BA_ATTRIBUTE14

EXT_BA_ATTRIBUTE15

EXT_ATTRIBUTE_CATEGORY

EXT_ATTRIBUTE1

EXT_ATTRIBUTE2

EXT_ATTRIBUTE3

EXT_ATTRIBUTE4

EXT_ATTRIBUTE5

EXT_ATTRIBUTE6

EXT_ATTRIBUTE7

EXT_ATTRIBUTE8

EXT_ATTRIBUTE9

EXT_ATTRIBUTE10

EXT_ATTRIBUTE11

EXT_ATTRIBUTE12

EXT_ATTRIBUTE13

EXT_ATTRIBUTE14

EXT_ATTRIBUTE15

EXT_ATTRIBUTE_NUMBER1

EXT_ATTRIBUTE_NUMBER2

EXT_ATTRIBUTE_NUMBER3

EXT_ATTRIBUTE_NUMBER4

EXT_ATTRIBUTE_NUMBER5

EXT_ATTRIBUTE_DATE1

EXT_ATTRIBUTE_DATE2

EXT_ATTRIBUTE_DATE3

EXT_ATTRIBUTE_DATE4

EXT_ATTRIBUTE_DATE5

CREATED_BY

LAST_UPDATED_BY

CREATION_DATE

LAST_UPDATE_DATE

Query

SQL_Statement

select ah.event_id event_id,

ext.transaction_id ext_transaction_id,

ah.acct_history_id acct_history_id,

'CE_TRANS' distribution_type,

ah.event_type accounting_event_type,

ah.accounting_date accounting_date,

ah.status_code accounting_event_status,

ah.reversal_indicator_flag reversal_indicator,

'CE_TRANS' reversal_dist_type,

ah.reversal_first_dist_id reversal_first_dist_id,

ah.reversal_second_dist_num reversal_second_dist_id,

le_ledger.ledger_name ext_ledger_name,

le.name ext_le_name,

ba.bank_account_num ext_bank_account_num,

DECODE(ext.amount, abs(ext.amount), 'CREDIT', 'DEBIT') ext_direction,

ext.currency_code ext_currency,

ext.transaction_date ext_trx_date,

ext.description ext_description,

ext.reference_text ext_recon_reference,

ext.transaction_type ext_trx_type,

ext.transaction_subtype ext_trx_subtype,

ext.value_date ext_value_date,

ext.offset_ccid ext_offset_ccid,

ext.asset_ccid ext_asset_ccid,

ext.status ext_status,

ext.source ext_source,

bu.bu_name ext_business_unit,

ah.cleared_date cleared_date,

abs( ah.cleared_amount) cleared_amount,

ah.cleared_exchange_rate cleared_exchange_rate,

nvl(ah.cleared_exchange_date, ext.cleared_date) cleared_exchange_date,

nvl(ah.cleared_exchange_rate_type, ba.GL_CUR_EXC_RATE_TYPE) cleared_exchange_rate_type,

le.country ext_le_country,

bb.bank_name ext_bank_name,

bb.bank_number ext_bank_number,

bb.bank_branch_name ext_branch_name,

bb.branch_number ext_branch_number,

bb.country ext_branch_country,

ba.bank_account_name ext_bank_account_name,

ba.currency_code ext_ba_currency,

ba.description ext_ba_description,

ba.attribute_category ext_ba_attribute_category,

ba.attribute1 ext_ba_attribute1,

ba.attribute2 ext_ba_attribute2,

ba.attribute3 ext_ba_attribute3,

ba.attribute4 ext_ba_attribute4,

ba.attribute5 ext_ba_attribute5,

ba.attribute6 ext_ba_attribute6,

ba.attribute7 ext_ba_attribute7,

ba.attribute8 ext_ba_attribute8,

ba.attribute9 ext_ba_attribute9,

ba.attribute10 ext_ba_attribute10,

ba.attribute11 ext_ba_attribute11,

ba.attribute12 ext_ba_attribute12,

ba.attribute13 ext_ba_attribute13,

ba.attribute14 ext_ba_attribute14,

ba.attribute15 ext_ba_attribute15,

ext.attribute_category ext_attribute_category,

ext.attribute1 ext_attribute1,

ext.attribute2 ext_attribute2,

ext.attribute3 ext_attribute3,

ext.attribute4 ext_attribute4,

ext.attribute5 ext_attribute5,

ext.attribute6 ext_attribute6,

ext.attribute7 ext_attribute7,

ext.attribute8 ext_attribute8,

ext.attribute9 ext_attribute9,

ext.attribute10 ext_attribute10,

ext.attribute11 ext_attribute11,

ext.attribute12 ext_attribute12,

ext.attribute13 ext_attribute13,

ext.attribute14 ext_attribute14,

ext.attribute15 ext_attribute15,

ext.ATTRIBUTE_NUMBER1 EXT_ATTRIBUTE_NUMBER1,

ext.ATTRIBUTE_NUMBER2 EXT_ATTRIBUTE_NUMBER2,

ext.ATTRIBUTE_NUMBER3 EXT_ATTRIBUTE_NUMBER3,

ext.ATTRIBUTE_NUMBER4 EXT_ATTRIBUTE_NUMBER4,

ext.ATTRIBUTE_NUMBER5 EXT_ATTRIBUTE_NUMBER5,

ext.ATTRIBUTE_DATE1 EXT_ATTRIBUTE_DATE1,

ext.ATTRIBUTE_DATE2 EXT_ATTRIBUTE_DATE2,

ext.ATTRIBUTE_DATE3 EXT_ATTRIBUTE_DATE3,

ext.ATTRIBUTE_DATE4 EXT_ATTRIBUTE_DATE4,

ext.ATTRIBUTE_DATE5 EXT_ATTRIBUTE_DATE5,

ah.CREATED_BY CREATED_BY,

ah.LAST_UPDATED_BY LAST_UPDATED_BY,

ah.CREATION_DATE CREATION_DATE,

ah.LAST_UPDATE_DATE LAST_UPDATE_DATE

from ce_external_transactions ext,

ce_acct_history ah,

xle_firstparty_information_v le,

gl_ledger_le_v le_ledger,

fun_organizations_v bu,

ce_bank_accounts ba,

ce_bank_branches_v bb

where ext.transaction_id = ah.transaction_id

and ah.transaction_source = 'XT'

and ext.bank_account_id = ba.bank_account_id

and ba.bank_branch_id = bb.branch_party_id

and ba.account_owner_org_id = le.legal_entity_id

and le_ledger.legal_entity_id = ba.account_owner_org_id

and le_ledger.LEDGER_CATEGORY_CODE = 'PRIMARY'

and bu.bu_id(+) = ext.business_unit_id