Search Results sign_off_status_code

Overview

AMW_AUDIT_PROJECTS_V is a seeded Oracle E-Business Suite view owned by the APPS schema, defined in the AMW – Internal Controls Manager product. In Oracle EBS 12.1.1 and 12.2.2 this view exposes audit engagement (audit project) header information in a flattened, reporting-friendly form. Because it resolves lookup codes to their meanings and derives the audit manager name through correlated subqueries, it is the standard presentation layer for engagement-level data used by ICM audit workbenches, dashboards, and management reporting. The view carries a VALID status and is intended for query access only; no DML should be issued against it.

Underlying Base Objects

The view text demonstrates that it is defined primarily over two tables: AMW_AUDIT_PROJECTS (aliased AP), the transactional audit engagement header, and AMW_AUDIT_PROJECTS_TL (aliased APT), its translation table holding language-dependent name and description. The view joins AP.AUDIT_PROJECT_ID to APT.AUDIT_PROJECT_ID and restricts APT.LANGUAGE to USERENV('LANG'), so name and description are returned in the session language.

The first branch of the UNION ALL additionally joins AMW_WORK_CATEGORIES_B and AMW_WORK_TYPES_B, requiring CATEGORY_CODE = 'ENGAGEMENT' and linking AP.ENGAGEMENT_TYPE_ID to TB.WORK_TYPE_ID, and filters on AP.PROJECT_ID IS NULL — that is, engagements not yet linked to a Projects (PA) project. The second branch handles engagements that have an associated PA project, pulling the project number (SEGMENT1), name, description, dates, and template/created-from attributes from PA_PROJECTS_ALL and resolving the project manager via PA_PROJECT_PARTIES_UTILS. Lookup meanings come from AMW_LOOKUPS, and the audit manager name is resolved from PER_ALL_PEOPLE_F for the current effective date range.

Key Columns

• AUDIT_PROJECT_ID — Primary key of the audit engagement; the join key to related ICM tables.
• PROJECT_ID / PROJECT_NUMBER — Associated Projects project identifiers; PROJECT_ID is NULL in the first UNION branch.
• NAME / DESCRIPTION — Language-specific engagement name and description from AMW_AUDIT_PROJECTS_TL (or PA_PROJECTS_ALL in the project-linked branch).
• AUDIT_PROJECT_STATUS — Code value whose meaning is resolved from AMW_LOOKUPS using lookup type AMW_ENGAGEMENT_STATUS.
• AUDIT_MANAGER_PERSON_ID — Person identifier of the engagement manager; the view also returns the corresponding full name from PER_ALL_PEOPLE_F for the current effective date.
• SIGN_OFF_STATUS / SIGN_OFF_REQUIRED_FLAG — Sign-off code with its meaning from lookup type AMW_ENGAGEMENT_SIGN_OFF_STATUS; the required flag defaults to 'N' via NVL.
• START_DATE / COMPLETION_DATE — Engagement scheduling dates.
• TEMPLATE_FLAG / CREATED_FROM_PROJECT_ID — Indicate whether the engagement is a template and, if so, the source it was copied from.
• ENGAGEMENT_TYPE_ID / SCOPE_CHANGED_FLAG — Engagement type reference and indicator that scope has changed.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard EBS audit/WHO columns.
• USERENV('CLIENT_INFO') — Converted to a numeric Org/context value to support multi-org or client-context filtering.

Common Use Cases and Queries

Typical uses include listing active engagements, reporting sign-off status, and locating engagements by manager. Because the view returns a UNION ALL, consumers should be aware that an engagement may appear in one branch only, depending on whether a PA project is linked.

Example: list engagements with their status meanings and managers.

SELECT audit_project_id,
       name,
       project_number,
       audit_project_status,
       audit_manager_person_id,
       start_date,
       completion_date
FROM   apps.amw_audit_projects_v
WHERE  audit_project_status = 'IN_PROGRESS'
ORDER  BY start_date;

Example: identify engagements still pending sign-off.

SELECT audit_project_id, name, sign_off_status, sign_off_required_flag
FROM   apps.amw_audit_projects_v
WHERE  sign_off_required_flag = 'Y'
AND    sign_off_status IS NULL;

These queries should be executed with the APPS schema or an appropriate responsibility that grants SELECT on the view, and results filtered by the relevant operating unit or client context where multi-org security applies.