Search Results amw_audit_projects_pk

Overview

AMW_AUDIT_PROJECTS is a core transactional table in the Oracle E-Business Suite module AMW – Internal Controls Manager. It stores the master definition of every audit project managed within the application, serving as the parent record for the objectives, tasks, scope, procedures, and workpapers that constitute an audit engagement. The table resides in the AMW schema and is documented as VALID in both release 12.1.1 and 12.2.2. Its primary key is AMW_AUDIT_PROJECTS_PK, defined on the surrogate column AUDIT_PROJECT_ID.

From a dimensional modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table carries its own surrogate key while bridging outward to a Project Accounting project through PROJECT_ID and to an application security context through SECURITY_GROUP_ID; downstream AMW entities depend on it heavily. This positions AMW_AUDIT_PROJECTS as the central anchor of the audit engagement model rather than a pure reference or bridge table.

Key Information Stored

The documented physical schema contains 39 columns. The most significant include:

• AUDIT_PROJECT_ID — the surrogate primary key and the value carried by all child tables.
• AUDIT_PROJECT_ID (U1 unique index) — documented as AMW_AUDIT_PROJECTS_U1, the unique business-key candidate, though in practice it is the same surrogate column.
• PROJECT_ID — foreign key to PA_PROJECTS_ALL, coupling the audit project to a Project Accounting project number and its costing/structuring attributes.
• PROJECT_NUMBER — the human-readable identifier for the audit project.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, driving row-level access control.
• AUDIT_PROJECT_STATUS — the life-cycle state of the engagement.
• START_DATE and COMPLETION_DATE — the planned or actual execution window.
• ENGAGEMENT_TYPE_ID — classifies the audit (for example, financial, operational, compliance).
• AUDIT_MANAGER_PERSON_ID — the person accountable for the engagement.
• TEMPLATE_FLAG — distinguishes reusable audit project templates from live engagements.
• SIGN_OFF_REQUIRED_FLAG and SIGN_OFF_STATUS — govern the approval and sign-off workflow.
• SCOPE_CHANGED_FLAG — indicates scope revisions made after baseline.
• CREATED_FROM_PROJECT_ID — self-referencing lineage to the source or template project.
• PHASE, ORIGINAL_SYSTEM_SOURCE_CODE, and ORIGINAL_SYSTEM_REFERENCE — phase tracking and legacy/system-of-origin identifiers.
• OBJECT_VERSION_NUMBER and the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — concurrency and audit trail.

Common Use Cases and Queries

Typical reporting needs include listing active engagements, aging open audits, reconciling AMW audit projects to Project Accounting, and producing management dashboards that count tasks and scope items per engagement.

A simple listing of live engagements joined to their accounting project:

• SELECT a.AUDIT_PROJECT_ID, a.PROJECT_NUMBER, a.AUDIT_PROJECT_STATUS, p.PROJECT_NAME, p.PROJECT_NUMBER AS PA_NUMBER FROM AMW.AMW_AUDIT_PROJECTS a, PA.PA_PROJECTS_ALL p WHERE a.PROJECT_ID = p.PROJECT_ID AND a.TEMPLATE_FLAG = 'N';

Counts of dependent artefacts per engagement:

• SELECT t.AUDIT_PROJECT_ID, COUNT(*) FROM AMW.AMW_AUDIT_TASKS_B t GROUP BY t.AUDIT_PROJECT_ID;

Aging report on open audits using START_DATE and COMPLETION_DATE:

• SELECT PROJECT_NUMBER, START_DATE, COMPLETION_DATE, SYSDATE - START_DATE AS AGE_DAYS FROM AMW.AMW_AUDIT_PROJECTS WHERE AUDIT_PROJECT_STATUS NOT IN ('CLOSED','CANCELLED');

Sign-off backlog monitoring with SIGN_OFF_REQUIRED_FLAG and SIGN_OFF_STATUS is another frequent request.

Related Objects

The following are the most significant dependents and references of AMW_AUDIT_PROJECTS based on the documented foreign-key relationships:

• PA_PROJECTS_ALL — referenced via AMW_AUDIT_PROJECTS.PROJECT_ID; supplies accounting project definition, naming, and organisational context.
• FND_SECURITY_GROUPS — referenced via AMW_AUDIT_PROJECTS.SECURITY_GROUP_ID; enforces multi-org and row-level security.
• AMW_AUDIT_TASKS_B — references AUDIT_PROJECT_ID; holds the individual audit tasks that constitute an engagement.
• AMW_AUDIT_OBJECTIVES_B — references AUDIT_PROJECT_ID; stores the stated audit objectives.
• AMW_AUDIT_SCOPE_ORGANIZATIONS and AMW_AUDIT_SCOPE_PROCESSES — reference AUDIT_PROJECT_ID; define the organisational units and business processes covered by the engagement.
• AMW_AUDIT_PROCEDURE_INTERFACE — references AUDIT_PROJECT_ID; bridges audit procedures into the engagement.
• AMW_WORK_EXT_B and AMW_WORK_EXT_TL — reference AUDIT_PROJECT_ID; hold workpaper base and translated records.

Together these objects form the audit construction and execution model, with AMW_AUDIT_PROJECTS as the mandatory parent record for every engagement element.