Search Results adjustments_posted

Overview

APPS.MTL_PHYSICAL_INVENTORIES_V is a reporting and integration view in Oracle E-Business Suite that exposes physical inventory header information stored in the MTL_PHYSICAL_INVENTORIES base table. The view presents one row per physical inventory defined within an inventory organization, combining the persistent attributes of the physical inventory document with several derived status indicators computed directly in the view definition. Unlike a simple column projection, the view applies DECODE logic to convert nullable date columns into normalized status codes, which makes it particularly useful for downstream reporting, interfaces, and status-driven logic. The view is owned by APPS and is primarily consumed for operational reporting on physical inventory cycles, snapshot readiness, and adjustment posting state.

Underlying Base Objects

The view is defined over a single documented base object: MTL_PHYSICAL_INVENTORIES, accessed through its synonym. Every column in the view is selected from this table with the alias M, except for two derived columns computed at query time. The first derived column, SNAPSHOT_COMPLETE, evaluates M.FREEZE_DATE; when the freeze date is null, SNAPSHOT_COMPLETE returns 2, otherwise it returns 1. The second derived column, ADJUSTMENTS_POSTED, evaluates M.LAST_ADJUSTMENT_DATE using the same DECODE pattern. Because the view does not join to any additional tables, it carries no row multiplication risk and reflects the base table row count directly. The M.ROWID value is surfaced as ROW_ID, preserving the ability to identify and act on the underlying physical row.

Key Columns

The view exposes identity, scheduling, control, and tolerance attributes of a physical inventory. Principal columns include:

• ORGANIZATION_ID — the inventory organization in which the physical inventory is conducted.
• PHYSICAL_INVENTORY_ID — the unique identifier of the physical inventory header.
• PHYSICAL_INVENTORY_NAME and DESCRIPTION — user-facing identifiers for the count.
• PHYSICAL_INVENTORY_DATE — the scheduled or actual date of the physical inventory.
• FREEZE_DATE — the date the system froze inventory to establish the snapshot; drives SNAPSHOT_COMPLETE.
• SNAPSHOT_COMPLETE — DECODE-derived status: 1 when a freeze date exists, 2 when it does not.
• LAST_ADJUSTMENT_DATE — the date adjustments were last posted; drives ADJUSTMENTS_POSTED.
• ADJUSTMENTS_POSTED — DECODE-derived status: 1 when adjustments have posted, 2 when they have not.
• TOTAL_ADJUSTMENT_VALUE — the aggregate value of adjustments resulting from the count.
• APPROVAL_REQUIRED, APPROVAL_TOLERANCE_POS, APPROVAL_TOLERANCE_NEG — approval and tolerance configuration.
• COST_VARIANCE_POS and COST_VARIANCE_NEG — cost variance thresholds applied during approval.
• ALL_SUBINVENTORIES_FLAG — indicates whether all subinventories are included in the count.
• NUMBER_OF_SKUS, NEXT_TAG_NUMBER, TAG_NUMBER_INCREMENTS, DYNAMIC_TAG_ENTRY_FLAG — tag generation and count scope attributes.
• EXCLUDE_ZERO_BALANCE and EXCLUDE_NEGATIVE_BALANCE — snapshot inclusion rules for zero and negative quantities.
• DEFAULT_GL_ADJUST_ACCOUNT — the general ledger account used for adjustment postings.

Common Use Cases and Queries

The SNAPSHOT_COMPLETE and ADJUSTMENTS_POSTED columns are the principal reason this view is preferred over the base table. Because they encode null dates as a readable status, they simplify status filtering and dashboard logic. Typical queries include identifying physical inventories whose snapshot is incomplete, monitoring counts that have not yet been posted, and reconciling adjustment values by organization.

SELECT organization_id,
       physical_inventory_id,
       physical_inventory_name,
       snapshot_complete,
       adjustments_posted,
       total_adjustment_value
  FROM apps.mtl_physical_inventories_v
 WHERE snapshot_complete = 2
   AND organization_id = :org_id;

A second common pattern aggregates adjustment values per organization or per inventory for period reporting:

SELECT organization_id,
       COUNT(*) inventory_count,
       SUM(NVL(total_adjustment_value,0)) total_adjustment
  FROM apps.mtl_physical_inventories_v
 WHERE adjustments_posted = 1
 GROUP BY organization_id;

Because the view is a thin wrapper over the base table, it inherits the base table's performance characteristics and requires no additional join tuning. It is suited to both ad hoc querying and embedded use in concurrent programs and interface extraction routines.