Search Results amw_fin_cert_ctrl_sum

Overview

AMW_FIN_CERT_CTRL_SUM is a table owned by the AMW schema and belongs to the Internal Controls Manager product. The product is flagged as Obsolete in the ETRM documentation, and the implementation note states that the object is not implemented in the current database. The table's documented purpose is to hold all of the controls associated with a financial certification — in effect, a denormalized summary of control attributes, assertions, and evaluation outcomes that support the financial certification workflow.

The table is documented in the Oracle EBS 12.1.1 physical schema with an owner of AMW and 195 columns. The heuristic Data Vault classification mined from the FK structure is standalone, meaning it does not carry the characteristic hub, link, or satellite signature. As a modeling suggestion, the object is therefore better treated as a wide fact or summary table rather than as a normalized Data Vault construct.

Key Information Stored

The table is organized around a composite business key. The unique index AMW_FIN_CERT_CTRL_SUM_U1 covers FIN_CERTIFICATION_ID, FINANCIAL_STATEMENT_ID, FINANCIAL_ITEM_ID, NATURAL_ACCOUNT_ID, and CTRL_ATTRIBUTE_TYPE. This combination identifies a certification, the statement and financial item it relates to, the natural account, and the control attribute being summarized. The table does not expose a documented surrogate primary key column beyond this business-key candidate.

The most significant columns fall into repeating groups of 30, corresponding to control attributes 1 through 30:

• FIN_CERTIFICATION_ID, FINANCIAL_STATEMENT_ID, FINANCIAL_ITEM_ID, ACCOUNT_GROUP_ID, and NATURAL_ACCOUNT_ID — the certification and account context.
• CTRL_ATTRIBUTE_TYPE and OBJECT_TYPE — the type of control attribute and the object being summarized.
• CTRL_ATTR_CODE_1..30 — the control attribute code for each of up to thirty attributes.
• INEFF_CTRL_ATTR_1..30 and INEFF_CTRL_PRCNT_1..30 — the ineffective control attribute value and its percentage.
• TOTAL_CTRL_ATTR_1..30 — the total control attribute value.
• ACC_ASSERT_FLAG_1..30 and EVAL_CTRL_ATTR_1..30 — the account assertion flag and evaluation attribute per control.
• CONTROLS_EXIST_FLAG — indicates whether controls exist for the summary row.
• Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Because the table is obsolete and not implemented, no production query workload exists on the current database. Where the object is still present in an upgraded or legacy environment, typical usage is reporting on control effectiveness per financial certification and account. A representative pattern groups by the business key and aggregates the repeating control attribute groups:

• Join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to enforce security group filtering.
• Filter by FIN_CERTIFICATION_ID and FINANCIAL_STATEMENT_ID to isolate one certification cycle.
• Use CONTROLS_EXIST_FLAG to distinguish items with controls from those without.
• Sum TOTAL_CTRL_ATTR_n and divide by INEFF_CTRL_ATTR_n to derive an ineffectiveness ratio for each attribute index.
• Pivot ACC_ASSERT_FLAG_n to report which account assertions are covered.

A sample query shape is: SELECT FIN_CERTIFICATION_ID, FINANCIAL_STATEMENT_ID, SUM(TOTAL_CTRL_ATTR_1), SUM(INEFF_CTRL_ATTR_1) FROM AMW_FIN_CERT_CTRL_SUM WHERE SECURITY_GROUP_ID = :sgid GROUP BY FIN_CERTIFICATION_ID, FINANCIAL_STATEMENT_ID;

Related Objects

The documented FK relationship is limited to the security group reference. The most significant related objects are:

• FND_SECURITY_GROUPS — referenced by AMW_FIN_CERT_CTRL_SUM.SECURITY_GROUP_ID; establishes the security group for the row.
• AMW_FIN_CERTIFICATIONS (or the equivalent financial certification header) — the parent for FIN_CERTIFICATION_ID.
• Financial statement and financial item definition tables — the parents for FINANCIAL_STATEMENT_ID and FINANCIAL_ITEM_ID.
• Natural account and account group definition tables — the parents for NATURAL_ACCOUNT_ID and ACCOUNT_GROUP_ID.
• Control attribute type and control definition tables — the parents for CTRL_ATTRIBUTE_TYPE and the CTRL_ATTR_CODE_n values.

Because no child tables are documented, the object behaves as a leaf summary table within the AMW certification schema. Any integration should treat it as read-only reference data rather than a transactional source.