Search Results ctrl_attribute_type

Overview

AMW.AMW_FIN_CERT_CTRL_SUM is a transactional summary table in the Oracle E-Business Suite Financial Certification (AMW) module. It holds all controls associated with a financial certification, aggregating control counts and effectiveness statistics at the level of a financial statement line or natural account. The table is owned by the AMW schema, resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and carries an ETRM status of VALID. It is registered in FND Design Data as AMW.AMW_FIN_CERT_CTRL_SUM.

The object is unusually wide, exposing 195 documented columns. To external systems and extension developers, the most visible attribute is CTRL_ATTRIBUTE_TYPE, a VARCHAR2(30) column that discriminates between different categories of control summarization recorded against the same certification, statement, item, and natural account combination. The heuristic Data Vault classification mined from the foreign key structure is standalone; candidates for modeling would therefore treat this as an independent entity rather than a dependent satellite, though the natural key of the table suggests it functions as a summarized link-and-satellite pattern combining certification, statement, item, account, and control-type granularity.

Key Information Stored

The business key of the table is defined by the unique index AMW_FIN_CERT_CTRL_SUM_U1, whose five columns form the business-key candidate: FIN_CERTIFICATION_ID, FINANCIAL_STATEMENT_ID, FINANCIAL_ITEM_ID, NATURAL_ACCOUNT_ID, and CTRL_ATTRIBUTE_TYPE. FIN_CERTIFICATION_ID identifies the parent financial certification, FINANCIAL_STATEMENT_ID the statement within that certification, and FINANCIAL_ITEM_ID the specific line item within the statement. NATURAL_ACCOUNT_ID anchors the row to a natural account in the chart of accounts, while CTRL_ATTRIBUTE_TYPE records the classified attribute category being summarized.

• FIN_CERTIFICATION_ID — financial certification identifier; leading column of all three indexes.
• FINANCIAL_STATEMENT_ID — financial statement identifier within the certification.
• FINANCIAL_ITEM_ID — financial item identifier within the statement.
• ACCOUNT_GROUP_ID — account group identifier grouping related natural accounts.
• NATURAL_ACCOUNT_ID — natural account identifier from the accounting flexfield.
• OBJECT_TYPE — VARCHAR2(30); indicates whether the row pertains to an account or a financial item.
• CTRL_ATTRIBUTE_TYPE — VARCHAR2(30); the control attribute type that classifies the control summary, and the attribute most frequently queried.
• CTRL_ATTR_CODE_1..30 — repeating control attribute codes; paired with INEFF_CTRL_ATTR_n (ineffective control count) and TOTAL_CTRL_ATTR_n (total control count) for up to thirty attribute slots.
• INEFF_CTRL_PRCNT_1..30 — computed percentage of ineffective controls per attribute slot, the primary reported metric.
• ACC_ASSERT_FLAG_1..30 — flags indicating which assertions each attribute slot relates to.
• EVAL_CTRL_ATTR_1..30 — evaluation status per control attribute.
• CONTROLS_EXIST_FLAG — indicates whether any controls exist for the row.
• SECURITY_GROUP_ID — security group, foreign key to FND_SECURITY_GROUPS.

Auditing columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER follow the standard EBS WHO column pattern and support optimistic locking during concurrent maintenance.

Common Use Cases and Queries

The table is used to report on control effectiveness across certifications. A typical query retrieves the total and ineffective control counts for a given certification:

SELECT c.FIN_CERTIFICATION_ID, c.FINANCIAL_STATEMENT_ID,
       c.FINANCIAL_ITEM_ID, c.NATURAL_ACCOUNT_ID,
       c.CTRL_ATTRIBUTE_TYPE, c.TOTAL_CTRL_ATTR_1, c.INEFF_CTRL_ATTR_1,
       c.INEFF_CTRL_PRCNT_1
FROM   AMW.AMW_FIN_CERT_CTRL_SUM c
WHERE  c.FIN_CERTIFICATION_ID = :cert_id
AND    c.CTRL_ATTRIBUTE_TYPE = :ctrl_attr_type;

• Control effectiveness dashboards — aggregate INEFF_CTRL_PRCNT_n against TOTAL_CTRL_ATTR_n to compute pass/fail rates per certification and period.
• Attribute-type analysis — grouping by CTRL_ATTRIBUTE_TYPE to compare control categories within a statement line.
• Account-level rollups — joining on NATURAL_ACCOUNT_ID to the chart of accounts for financial statement generation.
• Population of summaries — inserting derived counts that drive downstream certification sign-off workflows.

Related Objects

Relationship metadata records a single foreign key: AMW_FIN_CERT_CTRL_SUM.SECURITY_GROUP_ID references FND_SECURITY_GROUPS. Significant related objects include:

• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for row-level security filtering.
• AMW_FIN_CERTIFICATIONS — parent certification header, joined via FIN_CERTIFICATION_ID.
• AMW_FIN_STATEMENTS — joined via FINANCIAL_STATEMENT_ID to resolve statement context.
• AMW_FIN_ITEMS — joined via FINANCIAL_ITEM_ID for line item detail.
• FND_FLEX_VALUES / GL_CODE_COMBINATIONS — resolved through NATURAL_ACCOUNT_ID and ACCOUNT_GROUP_ID.
• AMW_FIN_CERT_CTRL_DET — the underlying control detail that feeds the summarized counts in this table.

The unique index U1, combined with non-unique indexes N1 (FIN_CERTIFICATION_ID) and N2 (FIN_CERTIFICATION_ID, CTRL_ATTRIBUTE_TYPE), dictates that most access paths begin with the certification identifier, making that column the natural entry point for reporting and integration queries.