Search Results first_rows

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

Overview

The QP_CUSTOMERS_V view is an Oracle Advanced Pricing (QP) dictionary object owned by the APPS schema. It exposes a simplified, active-customer listing derived from Oracle Receivables' Trading Community Architecture (TCA) tables. The view exists to populate the SOLD_TO_ORG_ID segment in the Customer context of the "Qualifiers Contexts" descriptive flexfield used by Advanced Pricing qualifiers. In EBS 12.1.1 and 12.2.2, pricing qualifiers determine which price list, modifier, or promotion applies to a transaction line; the SOLD_TO_ORG_ID qualifier is one of the most frequently used, since most pricing rules are scoped to a specific customer or customer group. By presenting a compact, human-readable list of active customer names and numbers alongside their internal identifiers, this view supports the value-set lookups and the Flexfield Value Set (FND_FLEX_VALUES) LOV behaviour for that segment. Reporting and integration layers that must resolve a customer's name and account number from an ID may also query the view, although its primary consumer is the Advanced Pricing qualifier setup UI.

Underlying Base Objects

The ETRM metadata documents two referenced base objects, both synonyms resolving to the TCA schema: HZ_CUST_ACCOUNTS and HZ_PARTIES. The view joins them on PARTY.PARTY_ID = ACCT.PARTY_ID and filters to rows where ACCT.STATUS = 'A'. HZ_CUST_ACCOUNTS stores the customer account (the billing entity), while HZ_PARTIES stores the underlying party — the person or organisation — whose PARTY_NAME is the customer name displayed. Because the relationship between a party and its customer accounts is one-to-many, a single party may appear more than once if it holds multiple active accounts. The STATUS = 'A' predicate restricts the output to active accounts, excluding inactive or merged accounts that should not be selectable as pricing qualifiers. The view definition is wrapped in a /*+ FIRST_ROWS */ hint, which instructs the optimiser to prioritise fast retrieval of the first rows — the behaviour expected of a value-set LOV. The documented view text reproduces this hint verbatim, and all columns retain the unqualified names visible to the APPS schema.

Key Columns

• CUSTOMER_NAME — Sourced from HZ_PARTIES.PARTY_NAME; the display name of the party associated with the account.
• CUSTOMER_NUMBER — Derived as HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER || ' CUSTOMER'; the account number concatenated with the literal string ' CUSTOMER' so the LOV presents an unambiguous customer label.
• CUSTOMER_ID — Sourced from HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID; the internal account identifier that is stored as the qualifier value when a user selects a customer, and which is subsequently compared against the SOLD_TO_ORG_ID on an order or transaction.

These three columns form a canonical lookup triad: ID for storage, name and number for presentation. No other columns are exposed by the documented view text.

Common Use Cases and Queries

The principal use case is the Advanced Pricing qualifier setup, where the view supplies values for the SOLD_TO_ORG_ID segment of the Qualifiers Contexts flexfield. A typical lookup query filters on name or number:

SELECT customer_name, customer_number, customer_id
  FROM   apps.qp_customers_v
 WHERE   UPPER(customer_name) LIKE UPPER(:p_search)||'%'
    OR  customer_number LIKE :p_search||'%';

Integrators resolving an order's sold-to account to a friendly label can join the view to an order line:

SELECT oha.order_number, c.customer_name, c.customer_number
  FROM   oe_order_headers_all oha,
         apps.qp_customers_v  c
 WHERE  c.customer_id = oha.sold_to_org_id;

Because the query carries the FIRST_ROWS hint and reads only two TCA tables, it returns quickly even on large installations — an important property for interactive LOVs. Analysts validating qualifier configuration may count active customers with SELECT COUNT(*) FROM apps.qp_customers_v. Where a party holds multiple active accounts, results will contain duplicate names with distinct CUSTOMER_ID values, so queries intended to return one row per party should aggregate or apply a DISTINCT on the party name. All access requires APPS-level or appropriate TCA read privileges.