Search Results amw_control_reports_u1

Overview

AMW.AMW_CONTROL_REPORTS is a transaction data table owned by the AMW schema and registered in Oracle E-Business Suite as FND Design Data AMW.AMW_CONTROL_REPORTS. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and it holds 26 documented columns at the 12.1.1 baseline. The object is currently VALID. Oracle classifies it as Internal Use Only: access to the underlying data is supported only through standard Oracle Applications programs, not through external or ad hoc direct SQL. In practice the table functions as an operational cross-reference that binds a revenue/COGS control record to a specific report definition, providing the AMW subsystem a mechanism to enumerate which reports are associated with a given control rule.

The supplied metadata does not classify this object into a Data Vault construct, so a heuristic reading is offered as a modeling suggestion only. With a single-column surrogate key (CONTROL_REPORT_ID) and two outgoing foreign keys pointing to CST_REVENUE_COGS_CONTROL and FND_SECURITY_GROUPS, the table is best modeled as a link-style association that resolves a many-to-many style relationship between controls and reports, with audit and descriptive-flexfield attributes carried alongside. It is not a pure hub, because the primary key does not represent an independently defined business entity, and it is not a standalone satellite, because the foreign key relationships anchor it to external entity tables.

Key Information Stored

The table's documented columns fall into four groups: the primary key, the foreign key relationships, the standard EBS audit columns, and the descriptive flexfield block.

• CONTROL_REPORT_ID — NUMBER, the surrogate primary key, enforced by unique index AMW_CONTROL_REPORTS_U1 on APPS_TS_TX_IDX and by constraint AMW_CONTROL_REPORTS_PK. No separate business-key column set is identified in the metadata; the surrogate key is the sole uniqueness candidate.
• CONTROL_ID — NUMBER, foreign key to AMW.AMW_CONTROL_REPORTS referencing CST_REVENUE_COGS_CONTROL; identifies the revenue/COGS control record to which the report association belongs.
• REPORT_ID — NUMBER, identifies the report registered against the control.
• SECURITY_GROUP_ID — NUMBER, foreign key to FND_SECURITY_GROUPS; participates in the Applications row-level security model.
• OBJECT_VERSION_NUMBER — NUMBER, the standard optimistic-locking counter used by the EBS framework and by the OAF/ADF BC layer to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — DATE and NUMBER, the mandatory EBS who-column set used for audit trail and for concurrent program reconciliation.
• ATTRIBUTE_CATEGORY — VARCHAR2(30), the descriptive flexfield context column.
• ATTRIBUTE1, ATTRIBUTE2 — VARCHAR2(150), documented as descriptive flexfield segments and therefore the only attributes intended for customer extension.
• ATTRIBUTE3 through ATTRIBUTE15 — VARCHAR2(150) each, present for structural completeness but documented as not used and not exposed as configured flexfield segments.

Common Use Cases and Queries

The principal use case is resolving which reports belong to a given revenue/COGS control. Because the table is flagged Internal Use Only, Production reporting should normally be driven from the corresponding Oracle-provided view or concurrent program rather than by direct SELECT. Where read-only diagnostics are permitted, a join of the control and security group anchors is the standard pattern:

• List all report associations for a single control: SELECT CONTROL_REPORT_ID, REPORT_ID, SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER FROM AMW.AMW_CONTROL_REPORTS WHERE CONTROL_ID = :control_id ORDER BY REPORT_ID;
• Confirm uniqueness and surrogate-key integrity: SELECT CONTROL_REPORT_ID, COUNT(*) FROM AMW.AMW_CONTROL_REPORTS GROUP BY CONTROL_REPORT_ID HAVING COUNT(*) > 1;
• Detect orphaned security group references: SELECT a.CONTROL_REPORT_ID FROM AMW.AMW_CONTROL_REPORTS a WHERE NOT EXISTS (SELECT 1 FROM FND_SECURITY_GROUPS g WHERE g.SECURITY_GROUP_ID = a.SECURITY_GROUP_ID);
• Identify stale rows for purge review using the audit columns: SELECT CONTROL_REPORT_ID, CONTROL_ID, REPORT_ID, LAST_UPDATE_DATE FROM AMW.AMW_CONTROL_REPORTS WHERE LAST_UPDATE_DATE < SYSDATE - 365;

Typical reporting uses include control-to-report coverage analysis, reconciliation of configured controls against executed reports, and audit extracts keyed on LAST_UPDATED_BY and CREATION_DATE.

Related Objects

• CST_REVENUE_COGS_CONTROL — referenced through CONTROL_ID; the parent control entity in the documented FK chain.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; supplies the security context for row-level access.
• AMW.AMW_CONTROL_REPORTS_U1 — the unique index on CONTROL_REPORT_ID in APPS_TS_TX_IDX, and AMW_CONTROL_REPORTS_PK, the primary key constraint; both enforce the surrogate key.
• FND_DESCR_FLEX_COLUMN_USAGES / FND_DESCRIPTIVE_FLEXS — the descriptive flexfield infrastructure that defines ATTRIBUTE_CATEGORY and the ATTRIBUTE1–ATTRIBUTE15 segment usage.
• FND_OBJECTS / FND_APPLICATION — registration objects that tie the AMW schema and table to the application and data vault framework.
• The corresponding AMW report and control views — the preferred, supported read interface for reporting instead of direct table access.