Search Results amw_ap_steps_b_u1

Overview

AMW.AMW_AP_STEPS_B is a transactional table within the Oracle E-Business Suite Audit Management and Workflow (AMW) schema. It stores the individual steps that compose an audit procedure, and it preserves the full revision history of those steps through effectivity tracking. Each row represents a single audit step associated with a specific revision window of an audit procedure. Steps may be added, updated, or deleted as an audit procedure evolves, and the table retains versioned rows rather than overwriting them, using the FROM_REV_NUM and TO_REV_NUM columns to define the range of procedure revisions over which a given step definition remains valid.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT_FREE setting of 10 percent. Its primary key, AMW_AP_STEPS_B_PK, is defined on AP_STEP_ID. A unique index, AMW_AP_STEPS_B_U1, is defined on AP_STEP_ID in the APPS_TS_TX_IDX tablespace. Because the table maintains itself as an accumulating record of step definitions with revision-based validity, a Data Vault modeling heuristic would classify it as a satellite attached to the audit procedure hub, capturing descriptive step attributes and their temporal effectivity. The "_B" suffix conventionally indicates an entity base table in EBS, with a corresponding "_TL" table expected for translated columns.

Key Information Stored

The table contains 32 documented columns. The most significant are summarized below.

• AP_STEP_ID — Surrogate primary key uniquely identifying each audit step; also the unique index column AMW_AP_STEPS_B_U1.
• AUDIT_PROCEDURE_ID — Foreign identifier referencing the parent audit procedure in AUDIT_PROCEDURES_B.
• FROM_REV_NUM — Revision number in which the step was created or last updated; marks the beginning of the effectivity window.
• TO_REV_NUM — Revision number through which the step remains valid; marks the end of the effectivity window. This is the column targeted by the search term "to_rev_num".
• SAMPLESIZE — Number of samples to be drawn for execution of this audit step.
• SEQNUM — Reserved; no longer used.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS supporting multi-org data isolation.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the framework for concurrent update control.
• ORIG_SYSTEM_REFERENCE — External system reference for data migration or integration.
• REQUESTOR_ID — Identifier of the requesting user associated with the step.
• CSEQNUM — Concurrent/sequence control number used in internal processing.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.
• Attribute columns — ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 supporting descriptive flexfield data.

Common Use Cases and Queries

The primary functional scenario is reconstructing a procedure's step set for a given revision. Because a step's validity spans FROM_REV_NUM to TO_REV_NUM, a point-in-time query can filter by revision:

SELECT ap_step_id, audit_procedure_id, samplesize
FROM   amw.amw_ap_steps_b
WHERE  audit_procedure_id = :p_proc_id
AND    :p_rev_num BETWEEN from_rev_num AND NVL(to_rev_num, 999999);

Analyzing change history uses TO_REV_NUM to identify superseded step definitions — those closed off when an update occurred — while FROM_REV_NUM isolates the superseding rows. Reporting on sample size drivers across procedures is a straightforward aggregate:

SELECT audit_procedure_id, COUNT(*), SUM(samplesize)
FROM   amw.amw_ap_steps_b
WHERE  to_rev_num IS NULL
GROUP  BY audit_procedure_id;

Integration audits frequently join ORIG_SYSTEM_REFERENCE against source-system keys, and multi-org reporting filters on SECURITY_GROUP_ID.

Related Objects

• AMW.AMW_AP_EXECUTIONS — References AMW_AP_STEPS_B via AP_STEP_ID; records execution results per step.
• AMW.AUDIT_PROCEDURES_B — Parent procedure table; AUDIT_PROCEDURE_ID joins to AUDIT_PROCEDURE_ID.
• FND_SECURITY_GROUPS — Referenced through SECURITY_GROUP_ID.
• AMW_AP_STEPS_TL — Translation table keyed on AP_STEP_ID, holding language-specific text.
• AMW_AP_STEPS_B_PK / AMW_AP_STEPS_B_U1 — Primary key constraint and unique index on AP_STEP_ID.