Search Results cust_delimiter

Overview

APPS.OPM_CUSTOMERS is a compatibility view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that presents Oracle Process Manufacturing (OPM) customer records using the legacy customer model. It exposes columns such as CUST_NO, CUST_NAME, PHONE_NO, and TERMS_CODE that older OPM applications and reports expect, while sourcing data from the Oracle Receivables/Human Resources (TCA) schema and the OPM pricing tables. Its primary role is to bridge the 11i OPM customer structures to the 12.x multi-org architecture without forcing legacy reports to be rewritten.

The view is relevant to the search term cust_delimiter because it constructs the concatenated customer number using the profile option GL$CUST_DELIMITER. The expression ac.account_number || nvl(fnd_profile.value('GL$CUST_DELIMITER'),'-') || s.location joins account number and location with a delimiter that defaults to a hyphen. This profile option lets installations choose a separator that matches their existing customer numbering conventions, and it is resolved at runtime through the FND_PROFILE package.

Underlying Base Objects

The view is defined over the following documented objects: FND_PROFILE (package), GL_PLCY_MST (synonym), GMF_AR_CUSTOMER (package), GML_INVALID_CUSTOMERS (synonym), HZ_CUSTOMER_PROFILES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_PARTIES, and HZ_PARTY_SITES (all synonyms to the corresponding TCA tables).

The TCA synonyms supply the modern customer master: HZ_CUST_ACCOUNTS provides account_number, customer_type, and status; HZ_CUST_ACCT_SITES_ALL and HZ_CUST_SITE_USES_ALL supply site-level attributes and site use codes such as BILL_TO and SHIP_TO; HZ_PARTIES supplies party_name; and HZ_LOCATIONS/HZ_PARTY_SITES supply location data. The GMF_AR_CUSTOMER package is called for derived values including phone, fax, telex, terms, and salesrep. FND_PROFILE resolves GL$CUST_DELIMITER. GL_PLCY_MST and GML_INVALID_CUSTOMERS contribute company code and validity filtering.

Key Columns

• CUST_NO / ALTCUST_NO — concatenation of account_number, the GL$CUST_DELIMITER profile value (default '-'), and site location.
• CUSTSORT_NO — the raw account_number, used for sorting.
• CUST_NAME — party_name from HZ_PARTIES.
• PHONE_NO, FAX_NO, TELEX_NO — returned by GMF_AR_CUSTOMER.PHONE for the GEN, FAX, and TLX types.
• CO_CODE — operating unit code from GL_PLCY_MST via the site's org_id.
• TERMS_CODE, TERMS_VARY — payment terms resolved through GMF_AR_CUSTOMER.TERMS, with override handling.
• FOB_CODE, SLSREP_CODE, COMMISSION_CODE — trade and sales attributes.
• BACKORDER_IND — derived from ship_sets_include_lines_flag (per Bug 2937494).
• INACTIVE_IND — set to 1 when the customer, account site, or site row status is I.
• BILL_IND, SHIP_IND — flags indicating active BILL_TO or SHIP_TO site uses.
• CUST_TYPE — 2 when customer_type is I (internal), otherwise 0.
• SIC_CODE, FROM_WHSE, FRTBILL_MTHD — shipping and industry classification attributes sourced from site records.

Common Use Cases and Queries

Typical uses include OPM order-entry validation, legacy customer reports, and reconciliation between TCA customer data and OPM expectations. The delimiter-based numbering is frequently queried explicitly.

• Customer lookup by composite number:
  SELECT cust_no, cust_name, terms_code FROM apps.opm_customers WHERE cust_no = '1024-DALLAS';
• List active bill-to customers:
  SELECT cust_no, cust_name FROM apps.opm_customers WHERE bill_ind = 1 AND inactive_ind = 0 ORDER BY custsort_no;
• Inspect the delimiter in use:
  SELECT fnd_profile.value('GL$CUST_DELIMITER') FROM dual;
• Find customers needing contact data:
  SELECT cust_no, cust_name FROM apps.opm_customers WHERE phone_no IS NULL;

Because the view calls PL/SQL functions per row, queries against large customer populations should be filtered tightly to control execution cost.