Search Results amw_fin_process_flat_n1

Overview

AMW.AMW_FIN_PROCESS_FLAT is a transactional table in the Oracle E-Business Suite Applications (AMW) schema that stores the flattened parent-child hierarchy of business processes affecting items or accounts participating in a financial certification. Rather than recursing a normalized process structure at runtime, the table materializes each ancestor-descendant pairing as a discrete row, enabling efficient set-based lookups during financial certification and process impact analysis. It is a core component of the ETRM (Enterprise Tax, Risk and Compliance Management) functional area, which governs financial certification workflows and the approval lifecycle of processes referenced in those certifications.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and is supported by four non-unique indexes, AMW_FIN_PROCESS_FLAT_N1 through N4, occupying the APPS_TS_TX_IDX tablespace. From a dimensional modelling perspective, the heuristic Data Vault classification of this object is standalone: the flattened hierarchy is self-contained, and the only documented foreign key relationship is the SECURITY_GROUP_ID reference to FND_SECURITY_GROUPS, used to support hosting and multi-tenant segregation.

Key Information Stored

The table contains thirteen columns, of which the business-critical set is described below. The documented indexes establish the principal access paths rather than a single surrogate key:

• FIN_CERTIFICATION_ID (NUMBER) — identifies the financial certification to which the flattened process belongs; indexed by AMW_FIN_PROCESS_FLAT_N1 and a primary business-key candidate.
• ORGANIZATION_ID (NUMBER) — the inventory organization context for the process; indexed by N2.
• PARENT_PROCESS_ID (NUMBER) — the ancestor process in the flattened relationship; indexed by N3.
• CHILD_PROCESS_ID (NUMBER) — the descendant process; indexed by N4.
• APPROVAL_DATE (DATE) — the date on which the process was approved.
• DELETION_APPROVAL_DATE (DATE) — the approval date authorising deletion of a process, directly relevant to the search term "deletion_approval_date."
• OBJECT_VERSION_NUMBER (NUMBER) — optimistic locking column for concurrent updates.
• SECURITY_GROUP_ID (NUMBER) — hosting security context; references FND_SECURITY_GROUPS.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS "Who" audit columns.

The combination of FIN_CERTIFICATION_ID, PARENT_PROCESS_ID, and CHILD_PROCESS_ID functions as the logical composite business key, while the "Who" columns provide audit lineage.

Common Use Cases and Queries

Typical scenarios include retrieving the full flattened process tree for a certification, identifying processes pending or completed deletion approval, and reconciling approval timelines against certification scope.

• List processes approved for deletion: SELECT FIN_CERTIFICATION_ID, PARENT_PROCESS_ID, CHILD_PROCESS_ID, DELETION_APPROVAL_DATE FROM AMW.AMW_FIN_PROCESS_FLAT WHERE DELETION_APPROVAL_DATE IS NOT NULL;
• Retrieve all children for a given ancestor within a certification: SELECT CHILD_PROCESS_ID FROM AMW.AMW_FIN_PROCESS_FLAT WHERE FIN_CERTIFICATION_ID = :cert_id AND PARENT_PROCESS_ID = :parent_id;
• Compare approvals versus deletion approvals by organization: SELECT ORGANIZATION_ID, APPROVAL_DATE, DELETION_APPROVAL_DATE FROM AMW.AMW_FIN_PROCESS_FLAT WHERE ORGANIZATION_ID = :org_id;

Related Objects

The documented dependency chain shows AMW.AMW_FIN_PROCESS_FLAT referenced by the APPS synonym AMW_FIN_PROCESS_FLAT and its own indexed columns, and referencing FND_SECURITY_GROUPS via SECURITY_GROUP_ID. Related objects of practical significance include:

• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID to resolve hosting security context.
• AMW_FIN_CERTIFICATIONS — the parent certification entity joined on FIN_CERTIFICATION_ID.
• AMW_PROCESSES / AMW_PROCESSES_B — process definition tables resolved through PARENT_PROCESS_ID and CHILD_PROCESS_ID.
• HR_ORGANIZATION_UNITS — resolves ORGANIZATION_ID to an organizational name.
• APPS.AMW_FIN_PROCESS_FLAT — the APPS-layer synonym or view through which the table is typically queried.