Results for “build_where_clause”

47 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ARP_TRX_SELECT_CONTROL is a PL/SQL package in the Oracle E-Business Suite Receivables (AR) module, owned by the APPS schema and classified as an OTHER API in the ETRM repository for releases 12.1.1 and 12.2.2. Its business function is to centralize the construction of dynamic SQL WHERE clauses and table reference fragments used to query transaction data in Receivables. Rather than hard-coding filtering logic in every form, report, or inquiry screen that needs to select transactions, the module consolidates that logic into a single callable interface. The package is declared with AUTHID CURRENT_USER, meaning it executes with the privileges of the calling schema rather than the defining APPS schema, which allows it to be safely invoked from custom code while still resolving APPS synonyms for the underlying data dictionary views. The header comment ($Header: ARPLTSCS.pls 115.4 2002/11/15) indicates the source file is APLTSCS.pls, a long-standing component of the Receivables inquiry infrastructure.

Key Procedures and Functions

The package exposes a single documented procedure, BUILD_WHERE_CLAUSE. Its purpose is to accept a set of user- or program-supplied selection criteria and return two WHERE clause fragments (P_where1 and P_where2) along with two corresponding table fragments (p_table1 and p_table2). These outputs are then concatenated by the caller into a complete query against the transaction tables.

The input criteria are extensive and cover the principal Receivables transaction inquiry dimensions: a choice indicator (P_choice), an open-invoice flag, transaction type identifiers, transaction class, installment number, date ranges for both transactions and adjustments, customer and customer class identifiers, transaction number ranges, batch identifier, a specific customer transaction identifier, and adjustment number and date ranges. A final parameter, p_call_from, defaults to 'INV', allowing the same procedure to serve multiple calling contexts (for example invoice inquiry versus other transaction-selection screens) by varying the generated clause structure. OUT parameters are declared NOCOPY for performance, avoiding redundant copying of the varchar2 fragments on return. No other procedures or functions are documented in the ETRM metadata.

Tables Accessed

The documented referenced tables, resolved through APPS synonyms, are FND_LANGUAGES and DUAL. FND_LANGUAGES is the Oracle Application Object Library table that stores installed language definitions; its inclusion suggests the generated WHERE clause accounts for language-dependent or translated lookup values when filtering transactions. DUAL is the single-row pseudo-table used for syntactic scaffolding, such as returning constants or serving as a placeholder in the generated table fragments when only one table is required. The actual transaction tables (for example RA_CUSTOMER_TRX_ALL and related entities) are referenced by the generated clause text rather than being queried directly by this package; the caller supplies and executes the final SQL. ETRM records that the package is referenced by two other packages, confirming it functions as a shared utility rather than a top-level entry point.

Usage Notes

ARP_TRX_SELECT_CONTROL is typically invoked from Receivables inquiry forms and internal selection logic that need a consistent, parameterized filter over transactions. A calling form gathers user criteria, passes them into BUILD_WHERE_CLAUSE, and appends the returned WHERE and table fragments to a base SELECT statement before executing it through dynamic SQL. The p_call_from parameter lets the caller signal its context so the procedure returns clauses shaped for that screen. Because the package uses AUTHID CURRENT_USER and resolves synonyms at runtime, custom integrations may call it directly, but they must supply valid APPS synonyms and observe the documented parameter contract. No standalone concurrent program is associated with this package; it is a supporting utility within the transaction selection framework.