Search Results orig_system_acct_value




Overview
Explore in AI Chat

Overview

AMW.AMW_ACCT_ASSOCIATIONS is a transactional table in the Oracle E-Business Suite database, owned by the AMW schema and registered under FND Design Data as AMW.AMW_ACCT_ASSOCIATIONS. It stores the mapping between business processes — expressed in either a library or an organization context — and the natural accounts associated with those processes. The table functions as a configuration and reference registry: for each process (identified by an OBJECT_TYPE and a composite of PK1 through PK5), the row records the natural account, its lifecycle timestamps, and a descriptive flexfield payload.

Physically, the table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and exposes a documented footprint of 37 columns. Under the heuristic Data Vault classification supplied with the metadata, this object models as a standalone structure. Since the classification yields no strict hub, link, or satellite designation, AMW_ACCT_ASSOCIATIONS is best treated as a standalone reference/association entity whose identity is its own surrogate key. It does reference two external objects — MSD_AUDIT_SQL_STATEMENTS via STATEMENT_ID and FND_SECURITY_GROUPS via SECURITY_GROUP_ID — so a loosely coupled link interpretation is also defensible.

Key Information Stored

The surrogate primary key is ACCT_ASSOC_ID (NUMBER), enforced by the unique index AMW_ACCT_ASSOCIATIONS_U1 and the primary key constraint AMW_ACCT_ASSOCIATIONS_PK. The unique index is the only documented business-key candidate; the remaining uniqueness of a row is logically determined by the combination of object and account attributes. The most significant columns are:

  • ACCT_ASSOC_ID — surrogate primary key and the strongest join key to dependent tables.
  • OBJECT_TYPE (VARCHAR2, 30) — discriminates the process context (library versus organization, and subtype). Indexed by AMW_ACCT_ASSOCIATIONS_N2, it is the principal filter predicate.
  • PK1 through PK5 (NUMBER) — store the primary-key values that identify the owning process entity, allowing polymorphic association across multiple source objects without dedicated foreign keys.
  • NATURAL_ACCOUNT_ID (VARCHAR2, 150) — the natural account identifier being associated. It is indexed together with OBJECT_TYPE in AMW_ACCT_ASSOCIATIONS_N1.
  • ORIG_SYSTEM_ACCT_VALUE — the originating source-system account value. This is the column most frequently cited in query-level searches and links each association back to the source ledger account value.
  • STATEMENT_ID and STATEMENT_LINE_ID — link the association to an auditing or statement definition and its line.
  • SECURITY_GROUP_ID — enforces multi-org / security-group data isolation, joining to FND_SECURITY_GROUPS.
  • ASSOCIATION_CREATION_DATE, APPROVAL_DATE, DELETION_DATE, DELETION_APPROVAL_DATE — the lifecycle timestamps enabling approval and soft-deletion reporting.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the descriptive flexfield structure and segments used for customer-specific extension.

Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER support the concurrency and auditing conventions common to EBS 12.1.1 and 12.2.2.

Common Use Cases and Queries

The typical application of this table is resolving which natural account is associated with a given library or organization process, and reconciling that mapping to the originating source system value. A representative query filters by object type and account:

  • Account lookup for a process context: select ACCT_ASSOC_ID, OBJECT_TYPE, NATURAL_ACCOUNT_ID, ORIG_SYSTEM_ACCT_VALUE where OBJECT_TYPE = :p_type and NATURAL_ACCOUNT_ID = :p_account.
  • Lifecycle reporting: count rows by APPROVAL_DATE and DELETION_DATE to report active versus soft-deleted associations.
  • Audit linkage: join STATEMENT_ID to MSD_AUDIT_SQL_STATEMENTS to trace which statement produced a given account association.
  • Security-scoped extracts: restrict output by SECURITY_GROUP_ID for multi-org reporting.
  • Flexfield reporting: surface ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 where the DFF carries site-specific classification.

Because ORIG_SYSTEM_ACCT_VALUE is not indexed, filters on that column alone may perform full scans; pairing it with OBJECT_TYPE leverages AMW_ACCT_ASSOCIATIONS_N2.

Related Objects

The documented relationships identify the following significant objects:

  • MSD_AUDIT_SQL_STATEMENTS — referenced through AMW_ACCT_ASSOCIATIONS.STATEMENT_ID.
  • FND_SECURITY_GROUPS — referenced through AMW_ACCT_ASSOCIATIONS.SECURITY_GROUP_ID.
  • AMW_ACCT_ASSOCIATIONS_U1, _N1, _N2 — the unique and non-unique indexes that define access paths and the business-key candidate.
  • AMW_ACCT_ASSOCIATIONS_PK — the primary key constraint on ACCT_ASSOC_ID.
  • Application-facing AMW package APIs and views that resolve natural accounts, which consume NATURAL_ACCOUNT_ID and PK1–PK5 to identify the owning process.

Queries should generally drive from ACCT_ASSOC_ID or from the OBJECT_TYPE/NATURAL_ACCOUNT_ID pair to exploit the documented indexes.