Search Results referral_code

Overview

PV_REFERRALS_VL is a translated (VL) view owned by the APPS schema in Oracle E-Business Suite, belonging to the Partner Management (PV) product family. It presents referral records — opportunities, leads, or deal registrations that a partner submits and that are subsequently qualified, accepted, or declined — together with the descriptive text that must be presented in the user's session language. The view is defined as a join between the transactional referral entity, PV_REFERRALS_B, and its translation table, PV_REFERRALS_TL, restricted so that only rows matching the language returned by USERENV('LANG') are exposed. Because it carries the "_VL" suffix, the view is the standard interface for forms, concurrent programs, OAF pages, and custom reports that require referral data in a single, language-resolved result set rather than joining the base and translation tables manually. The object is documented as VALID and is available in both EBS 12.1.1 and 12.2.2, where the underlying tables are referenced through APPS synonyms.

Underlying Base Objects

The view is defined over exactly two documented base objects: the synonym PV_REFERRALS_B and the synonym PV_REFERRALS_TL. PV_REFERRALS_B holds the non-translatable attributes of a referral, including its identifiers, status, partner references, customer and address details, compensation amounts, and the twenty-five descriptive flexfield attribute columns. PV_REFERRALS_TL holds the translatable text — REFERRAL_NAME and DESCRIPTION — keyed by REFERRAL_ID and LANGUAGE. The join condition is B.REFERRAL_ID = T.REFERRAL_ID AND T.LANGUAGE = USERENV('LANG'). Note that the view is not a strict outer join, so a referral lacking a translation row in the session language is not returned; report authors should be aware of this behavior when reconciling totals against PV_REFERRALS_B directly.

Key Columns

• REFERRAL_ID — Primary key and foreign key to the translation table; the anchor for all referral-level queries.
• REFERRAL_NAME, DESCRIPTION — Translatable text sourced from PV_REFERRALS_TL.
• REFERRAL_CODE, REFERRAL_STATUS, BENEFIT_ID, BENEFIT_TYPE_CODE — Business identifiers and the current workflow state of the referral.
• DECLINE_REASON_CODE — The reason a referral was rejected; the column matched by the user's search. It is populated when REFERRAL_STATUS indicates a declined outcome, and typically resolves against a lookup such as the decline reason lookup type. This is the principal column for win/loss and rejection analysis.
• PARTNER_ID, PARTNER_CONTACT_RESOURCE_ID, PARTNER_CUST_ACCOUNT_ID — The submitting partner and its contacts.
• CUSTOMER_PARTY_ID, CUSTOMER_PARTY_SITE_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS1–4, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_POSTAL_CODE, CUSTOMER_COUNTRY — Denormalized end-customer and address data captured at referral entry.
• CUSTOMER_CONTACT_* — Contact name, title, phone, extension, and e-mail details.
• EST_COMPENSATION_AMT, ACTUAL_COMPENSATION_AMT, CURRENCY_CODE, ACTUAL_CURRENCY_CODE — Estimated and realized compensation for the referral.
• ORDER_ID, CLAIM_ID, CLAIM_NUMBER, ENTITY_TYPE, ENTITY_ID_LINKED_TO — Linkage to downstream order and claim entities.
• ORG_ID, SECURITY_GROUP_ID, DUPLICATE_CUSTOMER_FLAG, STATUS_CHANGE_DATE — Multi-org, security, and lifecycle metadata.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE24 — Descriptive flexfield segments.
• ROW_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — WHO columns and optimistic locking support.

Common Use Cases and Queries

Typical uses include referral pipeline reporting, partner performance measurement, compensation reconciliation, and decline-reason trending. A decline analysis query might read:

SELECT referral_id, referral_name, partner_id, referral_status, decline_reason_code, status_change_date FROM apps.pv_referrals_vl WHERE decline_reason_code IS NOT NULL AND status_change_date >= :p_from_date ORDER BY status_change_date DESC;

To join decline codes to their lookup meanings:

SELECT r.referral_id, r.referral_name, r.decline_reason_code, l.meaning FROM apps.pv_referrals_vl r, apps.fnd_lookup_values_vl l WHERE r.decline_reason_code = l.lookup_code AND l.lookup_type = 'PV_DECLINE_REASON' AND l.language = USERENV('LANG');

For partner-level compensation reporting, aggregate EST_COMPENSATION_AMT and ACTUAL_COMPENSATION_AMT by PARTNER_ID and CURRENCY_CODE. Because the view filters by the session language, always run reports under a responsibility whose language matches the expected translation; otherwise referral names may be absent for records translated only in another language. When a complete listing of referrals is required regardless of translation, query PV_REFERRALS_B directly or apply an outer join to PV_REFERRALS_TL.