Search Results customer_contact_email2

Overview

PV_SQL_UTILITY is a helper PL/SQL package owned by APPS in the Oracle E-Business Suite, shipped as part of the CRM/TeleSales (Oracle Sales, "AS") technology stack. Its $Header comment (pvsqluts.pls 120.3, dated 2005/12/19) indicates it has been stable since the early 11i era and remains present, unchanged in purpose, in both EBS 12.1.1 and 12.2.2. The package is declared AUTHID CURRENT_USER, meaning its SQL executes with the privileges of the calling session rather than the definer, and its functions are designed to be embedded directly in SQL statements (for example, in a SELECT list or WHERE clause) to translate stored codes and foreign keys into human-readable descriptions.

The package serves as a lightweight "lookup and attribute retrieval" layer. Rather than requiring application code to join repeatedly to FND_LOOKUP_VALUES, FND_TERRITORIES_TL, HZ_PARTIES, HZ_CONTACT_POINTS, and the Sales foundation tables, callers invoke a single function that returns a scalar VARCHAR2. In this respect, AR_LOOKUP — the function the user searched for under "ar_lookup" — is one of several sibling lookup resolvers, alongside PV_LOOKUP, AS_LOOKUP, and FND_LOOKUP_VALUES.

Key Procedures and Functions

The package exposes 22 documented functions (no public procedures). They fall into four logical groups:

• Lookup code resolvers: PV_LOOKUP, AS_LOOKUP, AR_LOOKUP, and FND_LOOKUP_VALUES each accept a lookup code and a lookup type and return the corresponding meaning or description. AR_LOOKUP specifically resolves lookup values from the Receivables (AR) lookup namespace, allowing Sales-side code to interpret AR-defined codes without a direct dependency on AR package internals.
• Sales status and channel resolvers: AS_STATUS translates a status code; ASO_I_SALES_CHANNELS resolves a sales channel code; AS_SALES_METHODOLOGY, AS_SALES_STAGES_ALL, and AS_SALES_LEAD_RANKS resolve numeric IDs (sales methodology, sales stage, lead rank) into their display names.
• Geography and contact resolvers: FND_TERRITORIES returns a territory name from a territory code; HZ_LOCATION_COUNTRY returns the country for a party site; CUSTOMER_CONTACT_NAME, CUSTOMER_CONTACT_EMAIL, and CUSTOMER_CONTACT_PHONE retrieve primary contact attributes by party ID; the "2" variants (CUSTOMER_CONTACT_NAME2, CUSTOMER_CONTACT_EMAIL2, CUSTOMER_CONTACT_PHONE2) perform the equivalent retrieval keyed on a lead ID.
• Miscellaneous resolvers: REFERRAL_CUSTOMER_ADDRESS formats a referral's customer address; JTF_RESOURCE and PARTY_ADDRESS return resource and address attributes respectively.

Tables Accessed

The functions read (and do not write) the following tables through APPS synonyms:

• FND_LOOKUP_VALUES — backing store for PV_LOOKUP, AS_LOOKUP, AR_LOOKUP, and FND_LOOKUP_VALUES.
• AS_STATUSES_TL, AS_LEAD_CONTACTS_ALL — Sales status and lead/contact data used by AS_STATUS and the "2" contact functions.
• FND_TERRITORIES_TL — territory translations for FND_TERRITORIES.
• HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CONTACT_POINTS, HZ_RELATIONSHIPS — TCA customer/contact model tables supporting the customer_contact_* and HZ_LOCATION_COUNTRY functions.
• FND_USER — resolves the application user associated with a resource.
• JTF_AUTH_DOMAINS_B, JTF_AUTH_PERMISSIONS_B, JTF_AUTH_PRINCIPALS_B, JTF_AUTH_PRINCIPAL_MAPS, JTF_AUTH_ROLE_PERMS — the JTF authentication/authorization model, supporting JTF_RESOURCE and related permission checks.

Usage Notes

PV_SQL_UTILITY has no documented dependencies on other packages and is referenced by zero other packages in the ETRM metadata, indicating it is invoked directly by Oracle Forms, OAF pages, concurrent programs, and customer extensions rather than being called internally by other PL/SQL APIs. Because each function returns a single VARCHAR2, it is most commonly embedded inside a SELECT statement — for example, SELECT pv_sql_utility.ar_lookup(code, type) FROM ... — to avoid hand-coded joins. The AUTHID CURRENT_USER declaration means the caller must have SELECT privileges on the underlying tables (typically granted via the APPS schema and standard EBS responsibilities), and calls should be avoided in high-volume loops because each invocation issues its own SQL. Callers should also confirm that the lookup type passed to AR_LOOKUP belongs to the AR namespace; passing a type owned by another product returns NULL rather than raising an error, since the resolvers are designed to be null-safe within SQL expressions.