Search Results invbv_physical_adjustments

Overview

APPS.INVBV_PHYSICAL_ADJUSTMENTS is a read-only reporting view in the Oracle E-Business Suite Inventory (INV) module, owned by the APPS schema. The view is documented as "Retrofitted," indicating it was introduced or retrofitted into the ETRM 12.2.2 release to expose physical inventory adjustment data through a secured, business-intelligence-friendly interface. Its primary role is to present the rows of MTL_PHYSICAL_ADJUSTMENTS — the transaction-level record of quantity adjustments generated during a physical inventory count — in a form suitable for Oracle Business Intelligence (OBIEE), Discoverer, and custom concurrent-program reporting.

For the user searching adjustment_account_id, this view is the correct access point: it exposes the accounting flexfield reference for each physical adjustment, allowing reconciliation of inventory counts to the General Ledger. Unlike the base table, the view applies Oracle Application Object Library (AOL) row-level security through the organization hierarchy, so a user querying the view only sees adjustments for the inventory organizations they are authorized to access.

Underlying Base Objects

The view is defined over a single base object, MTL_PHYSICAL_ADJUSTMENTS, referenced through a synonym in the APPS schema. The defining query aliases this table as MPA and selects the physical adjustment columns required for reporting. Two security/translation mechanisms are embedded in the view definition:

• _SEC:MPA.ORGANIZATION_ID — enforces organization-level row security in the predicate clause.
• _LA:MPA.APPROVAL_STATUS:MFG_LOOKUPS:MTL_APPROVALS:MEANING — applies a lookup translation to the approval status code, mapping the stored code to the MEANING value from the MTL_APPROVALS lookup type in the Manufacturing (MFG) lookup category.

The view is declared WITH READ ONLY, meaning it cannot be used for DML; all inserts and updates to physical adjustments must be performed against MTL_PHYSICAL_ADJUSTMENTS or through the Physical Inventory forms.

Key Columns

The view projects the principal physical adjustment attributes. Notable columns include:

• ADJUSTMENT_ID — primary identifier of the physical adjustment row.
• ORGANIZATION_ID — the inventory organization in which the count and adjustment occurred; drives security and reporting filters.
• PHYSICAL_INVENTORY_ID — links the adjustment to its parent physical inventory definition.
• INVENTORY_ITEM_ID — the item being adjusted.
• SYSTEM_QUANTITY, COUNT_QUANTITY, ADJUSTMENT_QUANTITY — the on-hand quantity before the count, the counted quantity, and the resulting delta.
• REVISION, INVENTORY_LOCATOR_ID, SUBINVENTORY_NAME, LOT_NUMBER, LOT_EXPIRATION_DATE, SERIAL_NUMBER — the storage and tracking dimensions of the adjusted stock.
• COST_PER_UNIT — the unit cost applied to the adjustment.
• "_LA:APPROVAL_STATUS" — the translated approval status description.
• ADJUSTMENT_ACCOUNT_ID — the accounting flexfield identifier used to post the adjustment to the General Ledger; this is the column referenced in the user's search.
• APPROVED_BY_EMPLOYEE_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — audit and approval metadata.

Note that the view text exposes GL_ADJUST_ACCOUNT, which is surfaced to the user as ADJUSTMENT_ACCOUNT_ID in the documented column list.

Common Use Cases and Queries

Typical scenarios include reconciling physical count variances by organization, auditing approval status, and investigating the GL account used for a posting. Because the view enforces organization security, queries naturally return only the operating units assigned to the session user.

Sample query to retrieve adjustments and their accounting flexfield:

• SELECT adjustment_id, organization_id, inventory_item_id, adjustment_quantity, cost_per_unit, adjustment_account_id, approved_by_employee_id FROM apps.invbv_physical_adjustments WHERE organization_id = :org_id AND physical_inventory_id = :pi_id;

Sample query to summarize adjustments by approval status:

• SELECT "_LA:APPROVAL_STATUS" approval_status, COUNT(*) adj_count, SUM(adjustment_quantity) total_qty FROM apps.invbv_physical_adjustments GROUP BY "_LA:APPROVAL_STATUS";

For direct accounting analysis, join ADJUSTMENT_ACCOUNT_ID to GL_CODE_COMBINATIONS to resolve the concatenated account string. Because the view is read-only and security-enabled, it is well suited to ad-hoc reporting and OBIEE subject areas without additional row-level security configuration.