Search Results ies_cycle_id

Overview

AMW.AMW_SURVEY_ASSOCS is a transactional table in the Oracle E-Business Suite AMW (Advanced Management Workbench) schema that stores the association between surveys and assessments. The object is documented in the ETRM repository as VALID and is owned by the AMW schema, with an FND Design Data reference to AMW.AMW_SURVEY_ASSOCS. Its documented purpose is to hold the details of the association between a survey and an assessment, functioning as the junction construct through which survey definitions in the IES (Integrated Evaluation System) subsystem are bound to assessment or object records.

Physically the table resides in the APPS_TS_TX_DATA tablespace, with PCTFREE 10, which is typical for transactional data expected to undergo update activity. The table is documented with 12 columns and is uniquely identified by the primary key constraint AMW_SURVEY_ASSOCS_PK on SURVEY_ASSOC_ID. From a heuristic Data Vault modeling perspective, the metadata suggests this object functions as a link table: it resolves a many-to-many association between a survey entity and an object entity, and it carries only descriptive and foreign-key attributes rather than the full descriptive payload that a satellite would hold. This classification is a modeling suggestion derived from the foreign-key structure and should be validated against the actual deployment.

Key Information Stored

The table records one row per survey-to-object association. The most significant columns are as follows.

• SURVEY_ASSOC_ID — the surrogate primary key, populated by the AMW_SURVEY_ASSOCS_PK constraint. It is the sole unique identifier documented and should be treated as the technical key, not a business key.
• OBJECT_ID and OBJECT_TYPE — the polymorphic reference to the associated business object. OBJECT_ID is numeric and OBJECT_TYPE is a VARCHAR2(30) discriminator, so the pair identifies which business entity (for example, an assessment) the survey is attached to.
• IES_SURVEY_ID — the identifier of the survey definition in the IES survey model.
• IES_CYCLE_ID — the IES cycle identifier, indicating the evaluation cycle or period under which the survey association is valid.
• IES_DEPLOYMENT_ID — the IES deployment identifier, linking the association to a specific deployment of the survey instrument.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Application Framework to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns, used for change tracking and audit reporting.

No business-key unique index is documented beyond the primary key, so SURVEY_ASSOC_ID remains the only documented unique identifier. The combination of OBJECT_ID, OBJECT_TYPE, IES_SURVEY_ID, IES_CYCLE_ID, and IES_DEPLOYMENT_ID is the logical candidate for the association, but this is a modeling observation rather than a documented constraint.

Common Use Cases and Queries

The table is primarily consumed when resolving which surveys are attached to which assessment objects, particularly across cycles and deployments. A standard retrieval by primary key follows the documented query text:

• Point lookup: SELECT * FROM AMW.AMW_SURVEY_ASSOCS WHERE SURVEY_ASSOC_ID = :p_id;
• Association by object: selecting all associations for a given assessment, filtering on OBJECT_ID and OBJECT_TYPE.
• Cycle and deployment analysis: grouping rows by IES_CYCLE_ID and IES_DEPLOYMENT_ID to report participation coverage per cycle.
• Audit and change tracking: querying on LAST_UPDATE_DATE and LAST_UPDATED_BY to identify recent modifications, with OBJECT_VERSION_NUMBER used to detect stale reads.

Typical reporting scenarios include verifying that every assessment object has an associated survey for the active cycle, and reconciling survey deployment counts against cycle definitions.

Related Objects

According to the documented dependency data, AMW.AMW_SURVEY_ASSOCS does not reference any database object through declared foreign keys, and it is referenced only by the APPS synonym APPS.AMW_SURVEY_ASSOCS, which exposes the table to the applications schema. Because no FK constraints are documented, joins to related objects are made logically rather than through enforced referential integrity. The most significant related objects are:

• APPS.AMW_SURVEY_ASSOCS — the APPS-layer synonym through which application code and reports access the table.
• IES survey definition tables — joined on IES_SURVEY_ID to resolve survey metadata.
• IES cycle tables — joined on IES_CYCLE_ID to resolve cycle context.
• IES deployment tables — joined on IES_DEPLOYMENT_ID to resolve deployment context.
• Object/assessment tables — joined on OBJECT_ID and OBJECT_TYPE to resolve the associated business entity.

Because referential integrity is not enforced at the database level for these relationships, validation and orphan detection should be performed explicitly in reporting queries.