Search Results c_order_list_cformula




Overview
Explore in AI Chat

Overview

ONT_OEXPRPRS_XMLP_PKG is an Oracle E-Business Suite PL/SQL package owned by APPS that encapsulates the server-side logic for an Order Management XML Publisher (BI Publisher) report. Within Oracle EBS 12.1.1 and 12.2.2, the package follows the standard XMLP report package pattern where the report template (.rtf) is registered as a concurrent program, and the underlying package supplies the bind parameters, the SQL query, and the PL/SQL calculations that populate the report output. Based on the naming convention (OEXPRPRS), this report is associated with the Order Management "Order Print" or similar order processing report, providing users the ability to extract order header and line information filtered by a range of user-selected criteria.

The package declares a comprehensive set of global variables that map directly to the report's concurrent program parameters (e.g., P_SOB_ID, P_ORDER_AMOUNT_LOW, P_ORDER_AMOUNT_HIGH, P_ORDER_TYPE_HI, P_ORDER_DATE_LOW, P_ORDER_DATE_HIGH, P_ORDER_NUM_LOW, P_ORDER_NUM_HIGH, P_CUSTOMER_NAME_HI, P_CUSTOMER_NUMBER_HI, P_ORGANIZATION_ID, P_AGREEMENT, P_SALESREP_HI, P_ORDER_CATEGORY, P_LINE_CATEGORY, P_ONE_TIME, and P_ORDER_BY). The specific parameter the user searched for — P_OPEN_ORDERS_ONLY — is declared as a VARCHAR2(50) input parameter and is paired with an internal variable LP_OPEN_ORDERS_ONLY that is initialized to the neutral predicate 'and 1=1'. This dynamic SQL pattern is characteristic of XML Publisher report packages in EBS: the parameter is tested at runtime in BEFOREREPORT or AFTERPFORM, and the internal LP_ variable is rebuilt to include or exclude the corresponding filter clause before the query is executed.

Key Procedures and Functions

The package exposes 38 documented procedures and functions. The core concurrent-program lifecycle procedures are:

  • BEFOREREPORT — Executes before the report query runs; establishes session context, initializes globals, and prepares the dynamic WHERE-clause fragments (including LP_OPEN_ORDERS_ONLY) based on user parameter input.
  • AFTERPFORM — Fires after the parameter form is submitted; typically used to further validate or transform user-entered parameters before the query is executed.
  • AFTERREPORT — Executes after report completion, performing cleanup or writing summary values.
  • RP_ORDER_BYFORMULA — A formula column that returns the ORDER BY clause used to sort report output, driven by the P_ORDER_BY parameter.

The remaining documented functions are XML Publisher formula columns that compute derived report values, generally in the original currency, the functional currency, and the reporting currency. They include C_DISCOUNT_PRICEFORMULA, C_ORDER_AMOUNTFORMULA, C_ORDER_LISTFORMULA, C_DISCOUNT_PRICE_CUFORMULA, C_ORDER_LIST_CUFORMULA, C_ORDER_AMOUNT_CUFORMULA, C_DISCOUNT_PRICE_OTFORMULA, C_ORDER_LIST_OTFORMULA, C_ORDER_AMOUNT_OTFORMULA, C_DISCOUNT_PRCE_CFORMULA, C_ORDER_LIST_CFORMULA, C_ORDER_AMOUNT_CFORMULA, CF_1FORMULA, CF_2FORMULA, and C_DATA_NOT_FOUNDFORMULA. Collectively these support the display of amounts, list prices, and discounts across multiple currencies, and the C_DATA_NOT_FOUNDFORMULA indicates whether the report returned no rows so the template can render a "no data found" message.

Tables Accessed

The package references four documented tables via APPS synonyms:

  • FND_CONCURRENT_REQUESTS — Retrieved to obtain the concurrent request context (P_CONC_REQUEST_ID), used to identify the running request and support auditing or logging.
  • FND_CURRENCIES — Looked up to resolve currency names/codes for the multi-currency display columns.
  • OE_AGREEMENTS — Queried to associate orders with their agreements when the P_AGREEMENT parameter is supplied.
  • OE_TRANSACTION_TYPES_TL — Joined to translate order transaction type codes into their language-specific descriptions for report display.

Usage Notes

ONT_OEXPRPRS_XMLP_PKG is invoked exclusively through its associated concurrent program registration in Order Management. Users launch it from the Submit Requests form or a responsibility-specific menu, populate the parameter form (which includes P_OPEN_ORDERS_ONLY and the other range parameters), and the report server calls BEFOREREPORT to build the dynamic SQL. The package is not documented as being referenced by any other package (Referenced by 0 other packages), confirming it is a leaf-level report implementation rather than a shared API. Customizations should be applied via the XML Publisher template or a copied concurrent program, not by modifying the seeded package, to preserve supportability during patching in 12.1.1 and 12.2.2.