Search Results from_requested_date




Overview
Explore in AI Chat

Overview

The view APPS.WSHBV_PICKING_RULES is a Shipping Execution (WSH) database object that presents the picking rule definitions stored in WSH_PICKING_RULES in a denormalized, report-friendly form. Picking rules in Oracle EBS govern how the system selects material for outbound sales orders and move order transactions: they specify the subinventory and locator from which material is picked, the sequence and grouping in which pick slips are generated, the criteria used to filter eligible demand, and the flags controlling automatic detail, automatic confirmation, backorder processing, and delivery creation. In Oracle EBS 12.1.1 and 12.2.2 the view is owned by APPS and is reported as VALID in the ETRM data dictionary.

Unlike the base table, which stores foreign-key identifiers, the view resolves those identifiers into descriptive values, and in several columns exposes Flexfield and Lookup translation expressions (the _KF and _LA tokens visible in the view text). This makes the view suitable for reporting, data extraction, and integration scenarios where human-readable picking rule attributes are required without joining the lookup and key flexfield tables manually.

Underlying Base Objects

The ETRM 12.2.2 metadata documents four referenced base objects, all accessed through APPS synonyms: WSH_PICKING_RULES (synonym), MTL_SYSTEM_ITEMS (synonym), MTL_ITEM_LOCATIONS (synonym), and WSH_LOCATIONS (synonym). Consistent with this, the view text selects from WSH_PICKING_RULES WPR as the driving table, with outer joins ((+)) to MTL_ITEM_LOCATIONS (aliased twice, for the default stage locator and the pick-from locator), WSH_LOCATIONS (aliased twice, for the ship-to and ship-from locations), and MTL_SYSTEM_ITEMS for the inventory item.

All joins are outer joins keyed on INVENTORY_ITEM_ID, ORGANIZATION_ID, or the relevant location identifiers, so a picking rule row is retained even when the referenced item, locator, or location master record does not exist. The view is further constrained by a row-level security predicate (_SEC:WPR.ORGANIZATION_ID), which restricts rows to the operating units and organizations accessible to the requesting user through Oracle's Multi-Org security model. This makes the view safe to expose in operational reports without additional organization filtering.

Key Columns

The view exposes the picking rule identity and descriptive attributes, including PICKING_RULE_ID, NAME, DOCUMENT_SET_ID, ORGANIZATION_ID, and PROJECT_ID/TASK_ID for project-aware picking. Inventory and location columns include INVENTORY_ITEM_ID, PICK_FROM_SUBINVENTORY, DEFAULT_STAGE_SUBINVENTORY, SUBNINVENTORY, and the resolved locator and location identifiers SOURCE_LOCATION_ID, SHIP_TO_LOCATION_ID, and SHIP_FROM_LOCATION_ID.

Date-range columns define the demand selection window: FROM_REQUESTED_DATE and TO_REQUESTED_DATE bound the requested date, while FROM_SCHEDULED_SHIP_DATE and TO_SCHEDULED_SHIP_DATE bound the scheduled ship date — the latter pair being the attribute most commonly used to scope a picking rule to a shipping window. START_DATE_ACTIVE and END_DATE_ACTIVE control rule validity over time. Boolean flags include BACKORDERS_ONLY_FLAG, INCLUDE_PLANNED_LINES, EXISTING_RSVS_ONLY_FLAG, PARTIAL_ALLOWED_FLAG, AUTODETAIL_PR_FLAG, AUTO_PICK_CONFIRM_FLAG, and AUTOCREATE_DELIVERY_FLAG. Lookup-translated columns resolve SHIPMENT_PRIORITY_CODE and SHIP_METHOD_CODE, while CARRIER_ID, PICK_GROUPING_RULE_ID, and PICK_SEQUENCE_RULE_ID capture carrier and sequencing configuration. Audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY are also present.

Common Use Cases and Queries

The view is typically queried to audit picking rule configuration, to identify which rules are active for a given organization, or to drive custom pick-slip preparation logic. A basic listing of active rules for an organization follows:

SELECT picking_rule_id, name, organization_id,
       pick_from_subinventory, default_stage_subinventory,
       from_scheduled_ship_date, to_scheduled_ship_date
FROM   apps.wshbv_picking_rules
WHERE  organization_id IN (:p_org_id)
AND    TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE)
                          AND NVL(end_date_active, SYSDATE);

Because the search that retrieved this object was from_scheduled_ship_date, a frequent pattern is to locate rules whose scheduled ship date window covers a requirement:

SELECT name, from_scheduled_ship_date, to_scheduled_ship_date,
       backorders_only_flag, existing_rsvs_only_flag
FROM   apps.wshbv_picking_rules
WHERE  :p_ship_date BETWEEN from_scheduled_ship_date AND to_scheduled_ship_date;

Note that the date columns may be nullable, so NVL or explicit null handling is recommended when the intent is to treat a missing bound as unbounded.