Results for “as_changed_accounts_all”

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

Overview

AS_CHANGED_ACCOUNTS_ALL is a transactional staging table in the Oracle E-Business Suite Sales Foundation (AS) product family, owned by the OSM schema. In Oracle EBS 12.1.1 and 12.2.2, it serves as the change-capture repository for account and lead assignment processing. The table records changes applied to customer accounts, addresses, and leads within the Sales Foundation data model, acting as an intermediate store between source transactions and downstream synchronization to the Trading Community Architecture (TCA) model.

From a Data Vault modeling perspective, the mined foreign-key structure suggests this object behaves as a link table. Its composite primary key, AS_CHANGED_ACCOUNTS_PK, is defined on (ADDRESS_ID, LEAD_ID), which intersects party site and lead identities. This positions the table as an associative or relationship record rather than a pure hub or satellite, joining multiple business entities into a single change record.

Key Information Stored

The table defines 21 documented columns. The most operationally significant include:

• CUSTOMER_ID — identifies the party (account) involved; foreign key to HZ_PARTIES.
• ADDRESS_ID — the party site affected; foreign key to HZ_PARTY_SITES and a component of the primary key.
• LEAD_ID — the lead record affected; foreign key to AS_LEADS_ALL and a component of the primary key.
• SALES_LEAD_ID — reference to AS_SALES_LEADS for the sales lead context.
• CHANGE_TYPE — classifies the nature of the account change captured.
• INSERT_FLAG, DELETE_FLAG, PROCESSED_FLAG — control flags governing how the change is reconciled and whether it has been consumed.
• ORG_ID — multi-org operating unit context.
• REQUEST_ID — the concurrent request that generated the change.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS for data security partitioning.

The physical surrogate key AS_CHANGED_ACCOUNTS_PK uses ADDRESS_ID and LEAD_ID. The documented unique index AS_CHANGED_ACCOUNTS_U1 on (CUSTOMER_ID, ADDRESS_ID, LEAD_ID, SALES_LEAD_ID, REQUEST_ID, ORG_ID) represents the broader business-key candidate, distinguishing it from the narrower primary key. Standard audit columns LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN are also present, alongside OBJECT_VERSION_NUMBER for optimistic locking.

Common Use Cases and Queries

Typical uses include auditing account changes, reconciling pending changes before processing, and generating reports of unprocessed change records. A common pattern is to retrieve all pending changes for a given account:

• Query by CUSTOMER_ID to list all addresses and leads with changes.
• Filter on PROCESSED_FLAG = 'N' to identify records awaiting reconciliation.
• Join to HZ_PARTIES on CUSTOMER_ID to resolve account names.
• Join to AS_LEADS_ALL on LEAD_ID to enrich lead context.
• Aggregate by CHANGE_TYPE to analyze change distribution.

A representative query pattern would select changed accounts joined across HZ_PARTIES, HZ_PARTY_SITES, and AS_LEADS_ALL, filtered on ORG_ID and PROCESSED_FLAG, ordered by CREATION_DATE.

Related Objects

The following objects are most directly coupled to AS_CHANGED_ACCOUNTS_ALL through documented foreign keys:

• AS_LEADS_ALL — via LEAD_ID; the primary lead entity.
• AS_SALES_LEADS — via SALES_LEAD_ID; sales-specific lead records.
• HZ_PARTIES — via CUSTOMER_ID; the TCA party master.
• HZ_PARTY_SITES — via ADDRESS_ID; party site/address records.
• FND_SECURITY_GROUPS — via SECURITY_GROUP_ID; security partitioning.

These relationships confirm the table's role as a dependent change record linked to both Sales Foundation lead entities and the TCA party model.