Results for “pos_acct_addr_rel”

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

Overview

The POS_ACCT_ADDR_REL table resides in the POS schema, which supports the iSupplier Portal module in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store supplier bank account and address assignments, providing the linkage between a supplier's banking details, the physical or remit-to addresses held in the Trading Community Architecture (TCA), and the supplier bank account request records that drive iSupplier Portal self-service maintenance flows. In practice, the table records the relationship that determines which bank account a supplier may use in conjunction with a given address, along with validity dates and status information that govern whether the assignment is currently active.

In Data Vault modeling terms, the metadata's heuristic classification of this object is link. This is a modeling suggestion: the table behaves as a relationship table that resolves associations between suppliers, bank branches, bank account numbers, currencies, addresses, and bank account requests. It is not a pure descriptive hub or satellite; its identity is defined by the combination of entities it connects rather than by a single naturally occurring business key.

Key Information Stored

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

• RELATIONSHIP_ID — the surrogate primary key, defined by the constraint POS_ACCT_ADDR_REL_PK. It uniquely identifies each supplier bank account and address assignment record.
• VENDOR_ID — identifies the supplier (vendor) to which the assignment belongs.
• BANK_BRANCH_ID — foreign key to AP_BANK_BRANCHES, identifying the bank branch associated with the assignment.
• BANK_ACCOUNT_NUMBER — the supplier's bank account number used in the assignment.
• ADDRESS_ID — foreign key to HZ_PARTY_SITES, identifying the TCA party site (address) linked to the assignment.
• CURRENCY_CODE — foreign key to FND_CURRENCIES, indicating the currency in which the bank account is denominated.
• REQUEST_ID — foreign key to POS_SUP_BANK_ACCOUNT_REQUESTS, tying the record back to the originating iSupplier Portal bank account request.
• RELATIONSHIP_TYPE — classifies the nature of the assignment (for example, the role the address plays relative to the bank account).
• PARENT_TYPE — identifies the parent entity context for the relationship.
• PRIMARY_FLAG — indicates whether the assignment is the primary one for the supplier or account.
• STATUS — the current state of the relationship record.
• START_ACTIVE_DATE and END_ACTIVE_DATE — define the effective date range during which the assignment is valid.

The surrogate key is RELATIONSHIP_ID. The metadata does not document any alternate unique index on business columns, so business-key candidates such as VENDOR_ID combined with BANK_ACCOUNT_NUMBER and ADDRESS_ID should be treated as logical identifiers rather than enforced unique constraints. Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) are also present.

Common Use Cases and Queries

Typical reporting and support scenarios include auditing which bank accounts are assigned to which supplier addresses, verifying active assignments by date range, and tracing self-service supplier changes back to the originating request. Representative query patterns include:

• Joining to HZ_PARTY_SITES on ADDRESS_ID to resolve the address details for a supplier's bank account assignment.
• Joining to AP_BANK_BRANCHES on BANK_BRANCH_ID to report the bank and branch name alongside the account.
• Joining to POS_SUP_BANK_ACCOUNT_REQUESTS on REQUEST_ID to reconcile approved supplier requests with resulting assignments.
• Filtering on STATUS, PRIMARY_FLAG, and the START_ACTIVE_DATE / END_ACTIVE_DATE range to list only currently effective primary assignments.

A common pattern is: SELECT r.relationship_id, r.vendor_id, r.bank_account_number, b.bank_name, s.party_site_id FROM pos.pos_acct_addr_rel r, ap_bank_branches b, hz_party_sites s WHERE r.bank_branch_id = b.branch_id AND r.address_id = s.party_site_id AND r.status = 'ACTIVE';

Related Objects

The documented foreign keys identify the objects most closely related to POS_ACCT_ADDR_REL:

• AP_BANK_BRANCHES — joined via POS_ACCT_ADDR_REL.BANK_BRANCH_ID.
• HZ_PARTY_SITES — joined via POS_ACCT_ADDR_REL.ADDRESS_ID.
• POS_SUP_BANK_ACCOUNT_REQUESTS — joined via POS_ACCT_ADDR_REL.REQUEST_ID.
• FND_CURRENCIES — joined via POS_ACCT_ADDR_REL.CURRENCY_CODE.
• POS_ACCT_ADDR_REL_PK — the primary key constraint that enforces uniqueness on RELATIONSHIP_ID.

Through these relationships, the table integrates supplier banking data maintained in iSupplier Portal with core Payables banking structures and TCA address data, making it a central link for supplier bank account and address assignment reporting.