Search Results ctrl_attr_code_1

Overview

The AMW.AMW_FIN_CERT_CTRL_SUM table is an Oracle E-Business Suite table owned by the AMW schema, delivered under Oracle's Financial Certification / management reporting and internal controls functionality (commonly associated with the Enterprise Management and Governance or financial close certification processes). Its documented purpose is straightforward: "Holds all of controls associated with the financial certification." Each row persists the summarized control inventory and control effectiveness results tied to a specific financial certification, financial statement, financial item, and natural account combination.

In Oracle EBS 12.1.1 and 12.2.2, the table resides in the APPS_TS_TX_DATA tablespace (PCT Free 10) with three supporting indexes in APPS_TS_TX_IDX. The metadata records 195 columns in the documented physical schema. The heuristic Data Vault classification for this object is standalone, with no natural parent link to another AMW transaction entity beyond a security group reference; this suggests it functions as an independent summary/aggregate that is regenerated or refreshed by the certification process rather than a pure transactional detail table.

Key Information Stored

The single unique index, AMW_FIN_CERT_CTRL_SUM_U1, defines the business-key candidate: FIN_CERTIFICATION_ID, FINANCIAL_STATEMENT_ID, FINANCIAL_ITEM_ID, NATURAL_ACCOUNT_ID, and CTRL_ATTRIBUTE_TYPE. Non-unique indexes _N1 and _N2 support lookups by certification alone and by certification plus attribute type. The most significant columns include:

• FIN_CERTIFICATION_ID — Identifier of the parent financial certification; the primary access path for reporting.
• FINANCIAL_STATEMENT_ID — Statement within a certification (e.g., balance sheet, income statement).
• FINANCIAL_ITEM_ID — Line item within the statement that the controls are mapped to.
• NATURAL_ACCOUNT_ID — Natural account segment for account-level control attribution.
• ACCOUNT_GROUP_ID — Grouping used to aggregate accounts for control assessment.
• OBJECT_TYPE — Indicates whether the control is associated with an account or a financial item.
• CTRL_ATTRIBUTE_TYPE — The category/type of control attribute being summarized (critical to interpretation).
• CTRL_ATTR_CODE_1 … CTRL_ATTR_CODE_30 — Up to 30 user-visible control attribute codes; the searched term CTRL_ATTR_CODE_1 is the first of these repeating attribute slots.
• INEFF_CTRL_ATTR_1 … _30 — Count of ineffective controls for each attribute slot.
• TOTAL_CTRL_ATTR_1 … _30 — Total number of controls recorded for each attribute slot.
• EVAL_CTRL_ATTR_1 … _30 — Evaluated control measure per attribute slot.
• ACC_ASSERT_FLAG_1 … _30 — Financial statement assertion flags (e.g., existence, completeness, valuation) applied per attribute slot.
• INEFF_CTRL_PRCNT_1 … _30 — Derived inefficiency percentage per attribute slot.
• CONTROLS_EXIST_FLAG — Boolean indicator stating whether any controls were identified for the row.
• SECURITY_GROUP_ID — Row-level security grouping; foreign key to FND_SECURITY_GROUPS.
• Creation/audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER for standard EBS concurrency control.

Common Use Cases and Queries

Typical reporting scenarios include certifying whether controls exist for a given statement line, calculating ineffective-control percentages before sign-off, and trending control coverage across certification cycles. The repeating _1 through _30 column groups are ideal pivot candidates for control attribute analysis.

Sample query retrieving control effectiveness for a certification:

• SELECT financial_statement_id, financial_item_id, natural_account_id, ctrl_attribute_type, ctrl_attr_code_1, total_ctrl_attr_1, ineff_ctrl_attr_1, ineff_ctrl_prcnt_1
• FROM amw.amw_fin_cert_ctrl_sum
• WHERE fin_certification_id = :p_cert_id
• AND ctrl_attribute_type = :p_attr_type
• ORDER BY financial_statement_id, financial_item_id;

A second pattern identifies accounts with no controls in scope:

• SELECT fin_certification_id, natural_account_id, object_type
• FROM amw.amw_fin_cert_ctrl_sum
• WHERE controls_exist_flag = 'N';

Related Objects

The FK metadata documents one outgoing reference: SECURITY_GROUP_ID → AMW.AMW_FIN_CERT_CTRL_SUM.SECURITY_GROUP_ID referencing FND_SECURITY_GROUPS. Other objects that logically participate in the financial certification model are inferred from the key naming conventions:

• FND_SECURITY_GROUPS — Row-level security grouping; joined on SECURITY_GROUP_ID.
• AMW_FIN_CERTIFICATIONS (parent certification header) — joined on FIN_CERTIFICATION_ID.
• AMW_FIN_CERT_STATEMENTS — joined on FINANCIAL_STATEMENT_ID, scoped to the certification.
• AMW_FIN_CERT_FIN_ITEMS — joined on FINANCIAL_ITEM_ID, supplying the financial item description.
• GL_CODE_COMBINATIONS — natural account validation for NATURAL_ACCOUNT_ID.
• FND_OBJECTS / FND_APPLICATION — metadata registration for the AMW application.

Because the table is classified as standalone and no AMW package/API is documented in the supplied metadata, most integrations read it directly for reporting. Direct DML should be avoided; changes to control summaries are expected to flow through the financial certification process that populates this table.