Search Results build_range_sql
Overview
CSP_EXCESS_PARTS_PVT is a private PL/SQL package in the APPS schema that provides the core processing engine for the Oracle EBS Excess Parts identification and disposition functionality, part of the Enterprise Asset Management (eAM) and Service/Spares replenishment modules. The package is classified as PVT (private) in the ETRM metadata, indicating that it is not intended as a public API and is invoked primarily by the concurrent program framework and by the four documented dependent packages. Its principal business purpose is to evaluate on-hand inventory positions against demand, reservations, WIP requirements, purchase orders, and user-defined excess rules, and to produce a ranked list of candidate excess parts for return, restock, or disposition.
The only public entry point is the excess_parts procedure, whose extensive parameter list (documented in the source header) reflects the breadth of filtering criteria available to the user. Significantly, the package exposes the parameter p_net_wip, which controls whether open WIP material requirements are netted against on-hand supply when computing the excess quantity. When p_net_wip is set to a non-zero value, the procedure includes WIP demand in the netting calculation; complementary parameters p_net_unrsv, p_net_rsv, p_include_po, p_include_wip, p_include_if, and p_include_nonnet allow the caller to fine-tune exactly which supply and demand sources participate.
Key Procedures and Functions
- EXCESS_PARTS — the main driver procedure; accepts organizational, subinventory, category, item, planner, buyer, date-cutoff, and netting parameters (including
p_net_wip) and orchestrates the excess-list generation. - GET_BUSINESS_RULE — a function that returns the applicable business rule identifier for a given organization and subinventory, driving rule-based behaviour.
- APPLY_BUSINESS_RULES — applies the identified excess rule to the qualification set.
- DEFECTIVE_RETURN — handles the return path for defective or non-conforming material.
- CLEAN_UP — removes temporary/staging rows for a given organization, subinventory, and condition type.
- BUILD_ITEM_CAT_SELECT — constructs the dynamic SQL fragment for item and category predicates.
- BUILD_RANGE_SQL — assembles range-based SQL predicates for low/high filters.
- ONHAND — computes on-hand quantities for the candidate set.
- DEMAND — evaluates demand (including WIP) for netting purposes.
- GET_SHIPPED_QTY — retrieves historical shipped quantities.
- NODE_LEVEL_ID — resolves hierarchy node and level identifiers.
- POPULATE_EXCESS_LIST — writes the qualified results into the excess list table.
- FIND_BEST_ROUTING_RULE and CHARGES_RETURN_ROUTING — determine routing and charge treatment for returned material.
Tables Accessed
The package reads and writes CSP_EXCESS_LISTS (the output staging table), CSP_EXCESS_RULES_B (rule definitions), CSP_PLANNING_PARAMETERS, CSP_INV_LOC_ASSIGNMENTS, CSP_SUP_DEM_SUB_TEMP, CSP_RS_CUST_RELATIONS, CSP_SEC_INVENTORIES (security), and CSP_RETURN_ROUTING_RULES. It also references CST_COST_TYPES and CST_ITEM_COSTS for valuation, FINANCIALS_SYSTEM_PARAMS_ALL for ledger context, FND_USER, HR_ALL_ORGANIZATION_UNITS, HR_LOCATIONS_ALL, and HZ_CUST_ACCT_SITES_ALL for organizational and customer-site resolution.