Search Results ss_model_refetch




Overview
Explore in AI Chat

Overview

The APPS.WSH_PR_PICKING_ROWS package body supports the Oracle E-Business Suite shipping execution module by assembling the pool of order lines that are eligible for pick release. Its central purpose is to open and fetch the details of unreleased lines, backordered lines, and non-shippable lines, then merge the unreleased and backordered results into a single consolidated set. That consolidated set is exposed to a client Pro*C program as a table of eligible release rows, which the pick release engine subsequently consumes.

In the Oracle EBS 12.1.1 and 12.2.2 releases, the package is classified as an OTHER API owned by APPS. It is not a public concurrent-program entry point; it functions as an internal service layer for the pick release flow. The header comment ($Header: WSHPRPRB.pls 115.14 99/07/16 08:20:06 porting ship $) indicates a long-lived code line that has been carried forward across release families. The user search term insert_rl_row corresponds to the row-insertion logic associated with populating the release-line (RL) structures that this package feeds to the calling program.

Key Procedures and Functions

  • INIT — Initializes the package state, including the initialized flag and the local PL/SQL table tracking variables, before the first fetch operation.
  • GET_SIZE — Returns the number of rows currently held in the package's local line collection, allowing the client to size its own buffer (bounded by the MAX_LINES constant, 52).
  • GET_LINE_DETAILS — Opens and fetches the unreleased, backordered, and non-shippable cursors, merges unreleased and backordered lines, and returns the eligible release rows. It controls the first-call flag first_call_gld and the model/ship-set re-fetch mode used when a model exceeds 50 lines.
  • SET_SYNC_LINE — Sets the current synchronization line position within the local PL/SQL table, supporting the sync-line cursor used for model/ship-set alignment.
  • GET_SYNC_LINE_DETAILS — Fetches details for the currently positioned synchronization line, again honoring the SS_MODEL_REFETCH behavior for oversized models.
  • SET_REQUEST_LINES — Establishes the request line context for the fetch operation, driving the sreq_SQL cursor.
  • GET_NON_SHIP_LINES — Returns non-shippable line details, using the ns_lines_refetch_mode flag to manage re-fetch when the set exceeds the buffer limit.
  • CLOSE_CURSORS — Releases the open dynamic cursors (u_cursor, b_cursor, s_cursor, ns_cursor, sreq_cursor) at the end of processing.

Tables Accessed

The documented references are to the DBMS_SQL and PLITBLM packages rather than to base application tables. The package relies on DBMS_SQL because it builds SQL text dynamically into the unreleased_SQL, backordered_SQL, sync_SQL, non_ship_SQL, and sreq_SQL buffers and maps fetched columns into local variables. PLITBLM is used for PL/SQL indexed-table manipulation of the relRecTyp line records (first_line, first_sync_line). The underlying shipping and order data is resolved at runtime through dynamically constructed queries rather than through statically named APPS synonyms in the package body.

Usage Notes

The package is invoked from the Pro*C client program that implements pick release, not directly from Oracle Forms. It is called as a set of successive operations: INIT first, then GET_LINE_DETAILS (or the sync/non-ship variants) repeatedly until the DONE and FAILURE constants govern termination, followed by CLOSE_CURSORS. The constants SUCCESS (0), FAILURE (-1), DONE (2), and SS_MODEL_REFETCH (5) define the return protocol. Custom code should treat this package as internal, since the ETRM metadata records zero packages referencing it; direct calls risk instability across patches. Because the package depends on DBMS_SQL and dynamic SQL, any customization should preserve cursor-open/close balance and respect the MAX_LINES buffer boundary of 52.