Search Results get_booked_status

Overview

OE_HEADER_STATUS_PUB is a public PL/SQL API package in the Oracle E-Business Suite Order Management (OM) module, owned by the APPS schema and classified as a PUB (public) API. Its business purpose is to encapsulate the logic used to derive the workflow-driven status of an order header and expose that status to other application components through a stable, callable interface. Rather than forcing callers to query order header columns or Oracle Workflow tables directly, the package centralizes the interpretation rules — for example, translating a flag value of Y or N into an externally meaningful status result, and optionally returning the date on which the corresponding workflow activity completed.

The package body is identified by the version string OEXPHDSB.pls 120.0, indicating it is a shipped, supported component of the OM codebase. It is documented in ETRM for both Oracle EBS 12.1.1 and 12.2.2 with the same six documented procedures/functions, and it is referenced by three other packages, confirming it is a reused dependency rather than a leaf-level utility.

Key Procedures and Functions

ETRM documents six procedures/functions in total. The three named procedures are:

• GET_BOOKED_STATUS — Returns the booked status of an order header. This is the entry point most commonly searched for by developers, and it is the API used when a caller needs to determine whether an order header has reached the booked state without directly interrogating the workflow activity tables.
• GET_CLOSED_STATUS — Returns whether the order header is closed. The closure determination is derived from the underlying header open/closed flag semantics, so the procedure abstracts the flag-to-status translation for the caller.
• GET_CANCELLED_STATUS — Determines whether the order header has been cancelled. Documented source shows two overloaded forms: one returning only the Y/N result, and a second overloaded version that additionally returns the date the corresponding workflow activity occurred, with a null result date returned when the order is not cancelled.

The remaining three documented units complete the set of header status derivations used by the OM status framework. Consistent with the package naming convention, each procedure writes its outcome to an OUT result parameter and, where applicable, a companion OUT date parameter. Parameter lists are not enumerated here beyond what the metadata documents.

Tables Accessed

The package reads from and derives status from the following tables, referenced through APPS synonyms:

• OE_ORDER_HEADERS_ALL — The primary order header table. Documented source shows a direct lookup of cancelled_flag by header_id, which is then converted to the API return value.
• WF_ITEM_ACTIVITY_STATUSES — Oracle Workflow activity status table, used to determine the completion state and end date of the workflow activities associated with the order header.
• WF_PROCESS_ACTIVITIES — Oracle Workflow process activity definition table, used to resolve the activity identity and its relationship to the workflow process for the order header.

Usage Notes

Because this is a PUB API, it is intended to be called from custom PL/SQL, Oracle Forms personalizations and customizations, concurrent programs, and other OM packages rather than being modified. Three packages already reference it, so callers should be aware that behavior changes would have downstream impact.

The typical invocation pattern is to pass an order header identifier and receive the status result, and — for the date-bearing overloads — the associated workflow activity date. Error handling follows the standard OM pattern: exceptions are trapped and routed through OE_MSG_PUB, with messages added only when the message level permits. When integrating, callers should rely on the packaged procedures (including the search term get_booked_status) instead of querying OE_ORDER_HEADERS_ALL flags or Workflow tables directly, since the package encodes the supported interpretation of those values.