Search Results get_item_count

Overview

APPS.AHL_PRD_MRSHL_PVT is a private PL/SQL package body belonging to the Oracle E-Business Suite Enterprise Asset Management (EAM) / Asset Lifecycle family of modules, specifically the Oracle Advanced Service (AHL) product schema. The package encapsulates marshalling logic used by the Maintenance Workbench and Production Visit functionality to determine which material items are available or unavailable for a given maintenance visit, unit, or item instance. It forms part of the internal "PVT" (private) API layer, meaning it is not intended to be called directly by external integrations; rather, it is consumed by the public workbench APIs and concurrent processing routines that drive spare-parts picking, reservation management, and work-order material planning.

The package header declares several tightly scoped subprograms that assemble table-based ("tbl_type") structures of material marshalling details, and several scalar helper functions used to summarise on-hand quantities, item counts, and work-order counts. These helpers feed the Maintenance Workbench UI so that service technicians and planners can see, per visit, what inventory is physically available at the target subinventory/locator and what remains reserved or otherwise committed elsewhere.

Key Procedures and Functions

The documented package exposes eight subprograms, each with a distinct role in material marshalling:

• GET_ITEM_COUNT — Returns a count of the distinct items associated with the marshalling context being evaluated. It is used by the workbench to display the total number of material line items and is the entry point most frequently searched by developers working with item summarisation.
• GET_AVAILABLE_ITEMS — Populates the collection of items that are available for issue or reservation against the visit. Typically filters on on-hand availability and reservation status.
• GET_UNAVAILABLE_ITEMS — Populates the complementary collection of items that cannot be marshalled, for example because they are short, reserved by another visit, or lack a valid locator.
• GET_WORKORDER_COUNT — Returns the number of work orders linked to the visit or unit, used for progress indicators on the workbench.
• GET_VISIT_COMPLETION_PERC — Calculates a percentage-complete metric for the visit based on task completion, supporting dashboard and status rendering.
• GET_MRSHL_DETAILS — The primary aggregator that returns the full marshalling detail table combining item, on-hand, reservation, and work-order information.
• GET_ONHAND_AVAILABLE — A helper function that sums transaction quantities in MTL_ONHAND_QUANTITIES for the requested organization, item, subinventory, and locator, returning zero when no rows are found or when the sum is negative.
• GET_ONHAND_NOTAVAILABLE — The inverse helper, summing on-hand quantities for the same item held at locators other than the requested locator, thereby quantifying stock that is present but not reachable at the desired pick location.

Tables Accessed

The package reads from a broad set of EBS base tables through APPS synonyms. Inventory position data is drawn from MTL_ONHAND_QUANTITIES, MTL_ITEM_LOCATIONS, MTL_RESERVATIONS, MTL_SERIAL_NUMBERS, and MTL_SYSTEM_ITEMS_KFV, which together supply the descriptive and quantitative view of stock. Item-instance relationships are resolved through CSI_ITEM_INSTANCES and CSI_II_RELATIONSHIPS, while production and visit context comes from AHL_PRD_MB_UC_DETAILS, AHL_VISIT_TASKS_B, AHL_VISITS_B, AHL_VISITS_B, AHL_WORKORDERS, AHL_ITEM_ASSOCIATIONS_B, AHL_MC_RELATIONSHIPS, and AHL_SCHEDULE_MATERIALS. Work-order material requirements are sourced from WIP_DISCRETE_JOBS and WIP_REQUIREMENT_OPERATIONS. The package is read-only with respect to these tables, performing summarisation and filtering rather than DML.

Usage Notes

Because the API classification is PVT, the package is referenced by only one other package within the AHL schema and should not be invoked directly from customer extensions; Oracle Corporation reserves the right to change its signature without notice. In practice it is called from the Maintenance Workbench form and from the concurrent programs that assemble visit-level material reports. Custom code that needs equivalent counts should call the corresponding public AHL workbench APIs rather than AHL_PRD_MRSHL_PVT directly. The package is unchanged in behavioural terms between EBS 12.1.1 and 12.2.2, though the 12.2 multitenancy and Online Patching model means the APPS synonym resolution must be verified against the correct edition after each patch cycle.