Search Results p_where1

Overview

ARP_ARXCOBL_MLS_PACKAGE is a multilingual (MLS) helper package in the Oracle Receivables module of Oracle E-Business Suite. Its name reflects its role in the "ARXCOBL" family of packages, which deal with customer balance and collection-related concurrent program processing. The _MLS suffix indicates that this package is specifically responsible for constructing and resolving the selection criteria (WHERE clauses and SQL statements) used by a multilingual concurrent program, translating runtime language settings and user-supplied parameters into the correct SQL statements executed against the Trading Community Architecture (TCA) foundation tables.

The package body is a legacy construct carried from earlier releases (the header shows revision 120.4, dated 2006) and remains valid across EBS 12.1.1 and 12.2.2. It is invoked by the parent concurrent program to build dynamic queries rather than to expose a formal public API. The only documented function, ARP_ARXCOBL_MLS_FUNCTION, returns a VARCHAR2 and acts as the package's primary entry point.

Key Procedures and Functions

The documentation identifies a single public function:

• ARP_ARXCOBL_MLS_FUNCTION — This function performs two principal tasks. First, it reads the session's language information by calling USERENV('LANG') and truncating the first four characters into a local variable (p_userenv_lang). Second, it reads concurrent program parameter values supplied by the user at submission time using the FND_REQUEST_INFO API calls GET_PARAM_NUMBER and GET_PARAMETER, specifically resolving the "Customer Name From" parameter. The function uses an internal package variable, p_where1, to hold a dynamically constructed WHERE clause fragment; together with p_where2 and p_table1, these variables support the internal build_where_clause logic referenced in the source comments. The function returns a string that the calling concurrent program uses to drive its query against customer data.

No other procedures or functions are externally documented, though the body contains internal helper logic and record types (notably select_rec_type with a language field and the null_rec constant).

Tables Accessed

• HZ_PARTIES — Queried through the hz_parties synonym to obtain party names and party identifiers. The package uses the party_name column to search for customers within a user-specified name range, typically filtering using an UPPER comparison between p_customer_name_from and p_customer_name_to. This supports the "Customer Name From" parameter selection.
• DUAL — Used as the source for the USERENV('LANG') selection that populates the user language variable.
• DBMS_SQL — Listed as referenced, indicating that the package executes dynamically constructed SQL statements. This is consistent with the presence of select_sql1 and select_sql2 VARCHAR2 variables and the p_where1 / p_where2 clause-building variables.

The historical cursor cusinfo, which joined hz_cust_accounts to hz_parties, is present only as commented-out code (disabled by bug 1946875 during the TCA uptake), confirming the package's migration toward direct HZ_PARTIES access.

Usage Notes

ARP_ARXCOBL_MLS_PACKAGE is not a general-purpose API and is not referenced by any other documented packages. It is invoked indirectly through the concurrent program that owns it, executing during the program's parameter validation and query construction phase. The program's language handling means behavior is sensitive to the session's NLS_LANG setting: a user submitting in French, for example, receives queries and parameters resolved against French-language data where applicable, while the internal language string is capped at four characters.

The user search term "p_where1" corresponds to the VARCHAR2(8096) package variable that accumulates the WHERE clause built from customer name parameters. When debugging or tracing performance of the associated concurrent program, p_where1 is the variable that determines the predicate pushed into the dynamic SQL, and its contents depend directly on the "Customer Name From" and related parameter values read through FND_REQUEST_INFO. Because the package relies on DBMS_SQL and dynamic statement strings, the generated clause should be inspected for correctness whenever parameter defaults (NULL for p_customer_name_from) are supplied, since the code explicitly assigns NULL when the parameter lookup fails.