Results for “ce_je_mappings_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CE_JE_MAPPINGS_V is a Cash Management (CE) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to denormalize the base table CE_JE_MAPPINGS so that Oracle Forms-based cash management setup screens can display bank account, transaction code, and sub-type information without issuing multiple joins in their block queries. The view joins the journal entry mapping table to bank accounts, bank branches, transaction codes, and transaction sub-type codes, presenting a single readable row per mapping rule.

Functionally, the view exposes the rules that determine how bank statement lines are translated into subledger journal entries. Each row links a specific bank account and transaction code to a General Ledger account combination (GL_ACCOUNT_CCID), with optional sub-type qualification and a search string used by the transaction matching logic. Because it surfaces the TRX_CODE_ID column directly, it is frequently consulted by developers and analysts searching for the surrogate key of a bank transaction code when building custom reports or integration extracts.

Underlying Base Objects

The view is defined over five documented objects in the APPS schema:

• CE_JE_MAPPINGS (SYNONYM) — the primary table holding each mapping rule, contributing ROWID, SEARCH_STRING_TXT, GL_ACCOUNT_CCID, REFERENCE_TXT, TRX_CODE_ID, JE_MAPPING_ID, TRXN_SUBTYPE_CODE_ID, and audit columns.
• CE_BANK_ACCOUNTS (SYNONYM) — supplies BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, CURRENCY_CODE, and BANK_ACCOUNT_ID.
• CE_BANK_BRANCHES_V (VIEW) — supplies the descriptive BANK_BRANCH_NAME and BANK_NAME values.
• CE_TRANSACTION_CODES (SYNONYM) — supplies TRX_CODE, START_DATE, and END_DATE for the transaction code.
• CE_TRXNS_SUBTYPE_CODES (SYNONYM) — joined with an outer join to supply TRANSACTION_SUB_TYPE_NAME; the predicate NVL(SUB.TRXN_SUBTYPE_STATUS(+), 'A') = 'A' restricts sub-types to active rows while preserving mappings with no sub-type.

The join chain is BA.BANK_BRANCH_ID = BB.BRANCH_PARTY_ID, BA.BANK_ACCOUNT_ID = JEM.BANK_ACCOUNT_ID, JEM.TRX_CODE_ID = TC.TRANSACTION_CODE_ID, and an outer join on TRXN_SUBTYPE_CODE_ID.

Key Columns

• TRX_CODE_ID — the surrogate key of the bank transaction code; the most commonly queried column and the anchor between a mapping rule and the transaction code definition.
• JE_MAPPING_ID — the primary key of the mapping rule row in CE_JE_MAPPINGS.
• GL_ACCOUNT_CCID — the code combination identifier of the General Ledger account to which the mapping posts.
• BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, CURRENCY_CODE — identify and describe the bank account to which the rule applies.
• TRX_CODE, START_DATE, END_DATE — the transaction code name and its effective date range.
• TRXN_SUBTYPE_CODE_ID, TRANSACTION_SUB_TYPE_NAME — the optional transaction sub-type qualified by the rule.
• SEARCH_STRING_TXT, REFERENCE_TXT — free-text matching attributes used during statement line processing.
• ROW_ID and the standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) support Forms row identification and audit reporting.

Common Use Cases and Queries

The view is typically queried to retrieve mapping rules for a bank account or to resolve a TRX_CODE_ID into its descriptive transaction code. Because it is a view, it is read-only and intended for inquiry and reporting rather than DML.

Mappings for one bank account:

SELECT trx_code, transaction_sub_type_name, gl_account_ccid,
       search_string_txt, start_date, end_date
FROM   apps.ce_je_mappings_v
WHERE  bank_account_id = :p_bank_account_id;

Resolving a transaction code identifier:

SELECT trx_code_id, trx_code, bank_account_num,
       transaction_sub_type_name
FROM   apps.ce_je_mappings_v
WHERE  trx_code_id = :p_trx_code_id;

Listing all active mappings with bank and branch descriptions for reconciliation or audit purposes:

SELECT bank_name, bank_branch_name, bank_account_num,
       trx_code, transaction_sub_type_name
FROM   apps.ce_je_mappings_v
WHERE  TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, SYSDATE);

These queries are equally valid in 12.1.1 and 12.2.2, as the documented view text and base objects are unchanged across both releases.