Results for “csp_pln_drilldown_subinv_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CSP_PLN_DRILLDOWN_SUBINV_V view is a reporting and inquiry object within the Oracle E-Business Suite (EBS) Spares Management module (CSP). It belongs to the APPS schema and holds a VALID status in both EBS 12.1.1 and 12.2.2. The view is designed to present a summarized drilldown of planning cost data at the subinventory level, allowing users to analyze actual, recommended, and delta costs for specific inventory items across organizations and secondary inventories.

The view aggregates cost metrics from an underlying planning headers view, filtering only records where HEADER_DATA_TYPE equals 1. This filter restricts the result set to a specific class of planning header data, typically representing the "as-is" or current-state planning scenario used during spares planning and replenishment analysis. By exposing a function call identifier (FUNC_CALL) alongside item and organization attributes, the view supports interactive drilldown behavior in Oracle Forms-based CSP inquiries, where users navigate from a summary planning screen to detailed subinventory-level cost breakdowns.

As a view rather than a table, CSP_PLN_DRILLDOWN_SUBINV_V does not store data. It derives its content dynamically at query time from its base objects, ensuring that reported costs always reflect the current state of the underlying planning data. This makes it suitable for real-time reporting but also means query performance is contingent on the efficiency of the base views and packages involved.

Underlying Base Objects

The documented base objects referenced by this view include CSP_PICK_UTILS (a PL/SQL package), CSP_PLANNING_HEADERS_V (a view), and the standard EBS security and utility packages FND_GLOBAL, HR_GENERAL, and HR_SECURITY. The view text draws its data exclusively from CSP_PLANNING_HEADERS_V, aliased as CPHV, applying a WHERE clause on HEADER_DATA_TYPE = 1 and a GROUP BY over the non-aggregated columns.

The inclusion of CSP_PICK_UTILS, FND_GLOBAL, HR_GENERAL, and HR_SECURITY indicates that the view participates in the EBS security model, particularly multi-org and HR security profiles. These packages typically supply organization context (via FND_GLOBAL.ORG_ID) and security predicates that restrict visible rows according to the user's assigned responsibilities and operating units. Consequently, query results are automatically filtered to the organizations and subinventories the querying user is authorized to access.

Key Columns

• FUNC_CALL — Identifier for the originating planning function or operation; used to group and distinguish drilldown contexts.
• INVENTORY_ITEM_ID — Internal surrogate key of the inventory item, joining to MTL_SYSTEM_ITEMS_B.
• ORGANIZATION_ID — The inventory organization under which the item and subinventory reside.
• SECONDARY_INVENTORY — The subinventory code where the planned item is located.
• ITEM_NUMBER — User-facing concatenated item number for display and search.
• ITEM_DESCRIPTION — Descriptive text for the item.
• ACTUAL_COST — Sum of actual costs for the grouped planning records.
• RECOMMENDED_COST — Sum of recommended costs produced by the planning engine.
• DELTA_COST — Sum of the difference between recommended and actual costs, highlighting variance.

Common Use Cases and Queries

Typical usage involves drilldown inquiry screens in Spares Management where a planner reviews planning headers and then expands to subinventory-level cost detail. A representative query filters by item and organization:

SELECT func_call, inventory_item_id, organization_id, secondary_inventory,
item_number, item_description, actual_cost, recommended_cost, delta_cost
FROM apps.csp_pln_drilldown_subinv_v
WHERE organization_id = :org_id
AND inventory_item_id = :item_id;

Because the view enforces HR and org security through its base packages, no additional organization-security predicate is required. Reporting queries should avoid unfiltered full scans, as aggregation over CSP_PLANNING_HEADERS_V can be costly; filtering by organization, item, or subinventory is recommended for performance.