Search Results freeze_date

Overview

MTL_PHYSICAL_INVENTORIES_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite (documented for 12.1.1 and 12.2.2) within the INV – Inventory product. It is defined over the MTL_PHYSICAL_INVENTORIES synonym and exposes all physical inventory setup information in a single, denormalized presentation. Physical inventory in Oracle Inventory is the process by which a warehouse is frozen, tagged, counted, approved, and adjusted so that the system on-hand quantity reflects the physical count. The view surfaces the header-level control record for each physical inventory: identifying information, scheduling dates, approval and costing tolerances, tag numbering rules, and the accounting attributes used when count variances are posted. Because it presents the same columns as the base table without joins or filters, it is effectively a stable read interface that shields reporting tools, custom concurrent programs, and integration layers from direct dependency on the table definition. It is particularly relevant to users investigating the default_gl_adjust_account, which is the General Ledger account assigned to absorb adjustment value generated by an approved physical inventory.

Underlying Base Objects

The view is defined solely over MTL_PHYSICAL_INVENTORIES, referenced in the APPS schema through a synonym. Each row in the base table corresponds to one physical inventory definition for a given organization, and the view returns that data one-for-one. The view text selects every business column from the table using the alias M, and adds the ROWID as ROW_ID for row-level addressing. Two derived columns are computed at query time rather than stored: SNAPSHOT_COMPLETE, which returns 1 when FREEZE_DATE is populated and 2 when it is null, and ADJUSTMENTS_POSTED, which returns 1 when LAST_ADJUSTMENT_DATE is populated and 2 when it is null. No other tables are joined, so the view inherits the organization-level security and multi-org behavior of the underlying table when accessed through the standard Inventory responsibilities.

Key Columns

• ORGANIZATION_ID / PHYSICAL_INVENTORY_ID – The inventory organization and the unique physical inventory identifier; together they form the natural key of a physical inventory.
• PHYSICAL_INVENTORY_NAME / DESCRIPTION – The user-defined name and free-text description of the count.
• PHYSICAL_INVENTORY_DATE, FREEZE_DATE, LAST_ADJUSTMENT_DATE – The scheduled count date, the date the system froze on-hand balances to create the snapshot, and the date adjustments were last posted.
• DEFAULT_GL_ADJUST_ACCOUNT – The default General Ledger account used to record the adjustment value resulting from approved count variances. This is the column most frequently sought when tracing where physical inventory adjustments will post.
• TOTAL_ADJUSTMENT_VALUE – The accumulated monetary value of adjustments for the physical inventory.
• APPROVAL_REQUIRED, APPROVAL_TOLERANCE_POS, APPROVAL_TOLERANCE_NEG – Flags and positive/negative tolerance thresholds that govern whether counts must be approved before adjustments are generated.
• COST_VARIANCE_POS / COST_VARIANCE_NEG – Cost variance tolerances applied when validating count quantities.
• ALL_SUBINVENTORIES_FLAG, EXCLUDE_ZERO_BALANCE, EXCLUDE_NEGATIVE_BALANCE – Scope controls determining which subinventories and balances are included in the count.
• NEXT_TAG_NUMBER, TAG_NUMBER_INCREMENTS, DYNAMIC_TAG_ENTRY_FLAG, NUMBER_OF_SKUS – Tag generation and entry configuration for the count.
• SNAPSHOT_COMPLETE, ADJUSTMENTS_POSTED – Derived status indicators.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE – Concurrent program audit columns identifying the request that last processed the record.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 – Descriptive flexfield columns available for client-specific extensions.

Common Use Cases and Queries

Typical uses include reporting the status of physical inventories across organizations, auditing the GL account that will receive adjustment postings, and feeding downstream reconciliation or integration processes. The following examples illustrate common access patterns.

List pending physical inventories with their adjustment account:

• SELECT organization_id, physical_inventory_name, physical_inventory_date, freeze_date, default_gl_adjust_account, total_adjustment_value FROM mtl_physical_inventories_v WHERE adjustments_posted = 2;

Identify the default GL account for a specific physical inventory:

• SELECT default_gl_adjust_account FROM mtl_physical_inventories_v WHERE physical_inventory_id = :p_physical_inventory_id;

Summarize adjustment value by organization for completed counts:

• SELECT organization_id, SUM(total_adjustment_value) FROM mtl_physical_inventories_v WHERE snapshot_complete = 1 GROUP BY organization_id;

Because the view exposes the same columns as MTL_PHYSICAL_INVENTORIES, queries written against it remain portable across the documented 12.1.1 and 12.2.2 releases.