Results for “ce_contact_assignments”

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

Overview

CE_CONTACT_ASSIGNMENTS is a Cash Management (CE) table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model that records the assignment of a contact to a specific banking entity level. The table captures whether a given contact relationship applies at the bank, branch, or bank account level, thereby enabling the application to present the correct contacts when users work with a treasury or banking record. Each row represents a single assignment, tying a contact relationship to one of the three organizational tiers of the banking hierarchy.

The ETRM relationship metadata classifies this table heuristically as a link in a Data Vault model. This classification is a modeling suggestion rather than a physical attribute: the table's purpose is to resolve the many-to-many association between contacts (stored in HZ_PARTIES via relationship records) and the banking entities they serve. It carries no descriptive attributes of its own beyond the assignment and audit columns, which is characteristic of a link construct rather than a hub or satellite.

Key Information Stored

The table contains eleven documented columns. The most significant are:

• CONTACT_ASSIGNMENT_ID — the surrogate primary key, enforced by the unique index CE_CONTACT_ASSIGNMENTS_U1. It uniquely identifies each assignment record and is the value users reference when searching for "contact_assignment_id".
• ASSIGNMENT_TYPE — indicates the level at which the contact is assigned (bank, branch, or account).
• RELATIONSHIP_ID — identifies the underlying contact relationship, linking the assignment to the party relationship definition.
• BANK_PARTY_ID — foreign key to HZ_PARTIES, identifying the bank party associated with the assignment when the assignment is at the bank level.
• BRANCH_PARTY_ID — foreign key to HZ_PARTIES, identifying the branch party when the assignment is at the branch level.
• BANK_ACCOUNT_ID — foreign key to CE_BANK_ACCOUNTS, identifying the internal or external bank account when the assignment is at the account level.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, and CREATED_BY, which support concurrency control, change tracking, and standard EBS who-column reporting.

The primary key is a system-generated surrogate; the unique index CE_CONTACT_ASSIGNMENTS_U1 is the only documented business-key candidate, as no natural unique constraint spanning the party and account columns is published in the metadata.

Common Use Cases and Queries

Typical scenarios include determining which contacts are available for a bank account, auditing contact coverage across a banking hierarchy, and driving contact selection lists in Cash Management setup and payment workflows. A representative query that returns all assignments for a given bank account is:

• SELECT contact_assignment_id, assignment_type, relationship_id, bank_party_id, branch_party_id, bank_account_id FROM ce_contact_assignments WHERE bank_account_id = :p_account_id;
• Joining to CE_BANK_ACCOUNTS to resolve account numbers, and to HZ_PARTIES on BANK_PARTY_ID or BRANCH_PARTY_ID to retrieve party names.
• Reporting all assignments at the branch level by filtering on ASSIGNMENT_TYPE and joining BRANCH_PARTY_ID to HZ_PARTIES.
• Auditing recent changes using LAST_UPDATE_DATE and LAST_UPDATED_BY.

Because the table is a leaf in the banking hierarchy, most reporting queries join outward to the party and account tables rather than relying on the assignment row alone.

Related Objects

• HZ_PARTIES — referenced twice, via BANK_PARTY_ID and BRANCH_PARTY_ID, supplying bank and branch party details.
• CE_BANK_ACCOUNTS — referenced via BANK_ACCOUNT_ID, supplying the account context for account-level assignments.
• CE_BANK_ACCT_USES_ALL and CE_BANK_BRANCHES — commonly joined to resolve account and branch usage and address context.
• HZ_RELATIONSHIPS — the likely source of RELATIONSHIP_ID, describing the contact-to-party relationship.
• HZ_ORG_CONTACTS — supplies contact role information for the assigned relationship.

These joins confirm the table's function as the linking construct between the banking hierarchy and the Trading Community Architecture contact model.