Search Results amw_audit_projects_u2

Overview

AMW.AMW_AUDIT_PROJECTS is a transaction table in the Oracle E-Business Suite Audit Management (AMW) application module. It stores information about audit projects, where an audit project represents a compilation of audit activities for one or more entities or organizations. In EBS 12.1.1 and 12.2.2, this table functions as the central header record for audit engagements, anchoring scope definitions, audit tasks, work records, objectives, and procedures that are captured in dependent AMW tables. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are stored in APPS_TS_TX_IDX.

From a data modeling perspective, the mined relationship data suggests a satellite-leaning classification. The table carries a surrogate primary key, extensive descriptive and flexfield columns, and standard WHO audit columns, but it also holds foreign keys to PA_PROJECTS_ALL and FND_SECURITY_GROUPS. This pattern is characteristic of a satellite attached to the Oracle Projects hub, extended with AMW-specific audit attributes rather than acting as an independent hub or a pure association link.

Key Information Stored

The surrogate primary key is AUDIT_PROJECT_ID, defined by primary key constraint AMW_AUDIT_PROJECTS_PK and enforced through the unique index AMW_AUDIT_PROJECTS_U1. A second index, AMW_AUDIT_PROJECTS_U2, is non-unique on PROJECT_ID, which serves as the business-key candidate linking the audit record to the corresponding Oracle Projects definition.

• AUDIT_PROJECT_ID — surrogate identifier for the audit project; primary key and unique index column.
• PROJECT_ID — foreign key to PA_PROJECTS_ALL; the underlying Oracle Projects project that the audit is associated with.
• PROJECT_NUMBER — denormalized project number used for display and reporting.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling multi-organization data security.
• SCOPE_CHANGED_FLAG — indicates whether the project scope has been modified since original definition.
• AUDIT_MANAGER_PERSON_ID — identifies the person managing the audit engagement.
• ENGAGEMENT_TYPE_ID — classifies the type of audit engagement.
• AUDIT_PROJECT_STATUS — current lifecycle status of the audit project.
• TEMPLATE_FLAG — distinguishes reusable audit templates from live projects.
• START_DATE and COMPLETION_DATE — planned or actual audit timeline boundaries.
• CREATED_FROM_PROJECT_ID — self-referencing lineage to the source project when copied or templated.
• PHASE — current audit phase within the engagement lifecycle.
• SIGN_OFF_REQUIRED_FLAG and SIGN_OFF_STATUS — track approval requirements and completion of sign-off.
• ORIGINAL_SYSTEM_SOURCE_CODE and ORIGINAL_SYSTEM_REFERENCE — support data migration and external system traceability.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield segments for client-specific extensions.

Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER provide auditability and optimistic locking.

Common Use Cases and Queries

Reporting on active audit engagements by status and manager is a frequent requirement. A typical query joins the audit project header to Oracle Projects and HR data:

• Listing audit projects by status: SELECT audit_project_id, project_number, audit_project_status, start_date FROM amw.amw_audit_projects WHERE audit_project_status = :status;
• Identifying projects with changed scope: SELECT project_number FROM amw.amw_audit_projects WHERE scope_changed_flag = 'Y';
• Finding projects pending sign-off: SELECT p.project_number, a.sign_off_status FROM amw.amw_audit_projects a, pa_projects_all p WHERE a.project_id = p.project_id AND a.sign_off_required_flag = 'Y' AND a.sign_off_status <> 'COMPLETE';
• Locating template projects for reuse: SELECT audit_project_id, project_number FROM amw.amw_audit_projects WHERE template_flag = 'Y';
• Tracing lineage of a copied audit: SELECT project_number, created_from_project_id FROM amw.amw_audit_projects WHERE created_from_project_id IS NOT NULL;

Integration and reconciliation routines commonly filter on ORIGINAL_SYSTEM_SOURCE_CODE to isolate records originating from legacy or external audit systems during migration validation.

Related Objects

AMW_AUDIT_PROJECTS is referenced by multiple AMW child tables through the AUDIT_PROJECT_ID column, forming the audit engagement hierarchy:

• AMW_AUDIT_SCOPE_ORGANIZATIONS — organizations included in the audit scope, joined on AUDIT_PROJECT_ID.
• AMW_AUDIT_SCOPE_PROCESSES — business processes within the audit scope.
• AMW_AUDIT_OBJECTIVES_B — audit objectives defined for the project.
• AMW_AUDIT_TASKS_B — audit tasks executed under the engagement.
• AMW_AUDIT_PROCEDURE_INTERFACE — procedures applied during the audit.
• AMW_WORK_EXT_B and AMW_WORK_EXT_TL — audit work records and their translations.
• PA_PROJECTS_ALL — parent Oracle Projects definition, joined on PROJECT_ID.
• FND_SECURITY_GROUPS — security group assignment, joined on SECURITY_GROUP_ID.

These relationships confirm the table's central role as the header entity for the AMW audit data model, with dependent satellites capturing scope, work, objectives, and procedures tied back through the AUDIT_PROJECT_ID surrogate key.