Search Results oefv_dscntcasgns

Overview

OEFV_DSCNTCASGNS is a read-only Oracle E-Business Suite view defined within the Order Entry (OE) module. Its purpose is to present customer-specific discount assignments in a fully resolved, denormalized form suitable for reporting, downstream integration, and inquiry screens. The object consolidates data drawn from discount definition tables, customer master records, site-use and address information, and lookup values, so that a single query yields a complete picture of who receives a discount, at which customer site, and with what effective dates.

The view is defined WITH READ ONLY, meaning it cannot be used as the target of DML operations and exists purely for retrieval. ETRM metadata records that the view is not implemented in the database version documented, and that no base objects are separately documented for it. The view text itself, however, explicitly references the six underlying tables described below, which serves as the authoritative source for its structure.

Underlying Base Objects

The view is defined over the following tables, joined with outer (+) syntax on every relationship except the discount assignment to discount definition:

• SO_DISCOUNT_CUSTOMERS (aliased DISCOUNT_CUST) — the driving table holding discount-to-customer assignments.
• SO_DISCOUNTS (aliased DISCOUNT) — the discount header, supplying the discount name.
• RA_CUSTOMERS (aliased CUSTOMER) — the customer master, supplying the customer name.
• RA_SITE_USES_ALL (aliased SITE_USE) — the customer site-use assignment, supplying the location and address identifier.
• RA_ADDRESSES_ALL (aliased SITE_ADDR) — the address detail, supplying address lines, city, state, postal code, province, county, and country.
• AR_LOOKUPS (aliased LOOKUP) — restricted to lookup type 'CUSTOMER CLASS', supplying the customer class meaning.

Because the customer, site-use, address, and lookup joins are outer joins, an assignment row is retained even when related customer, site-use, address, or lookup data is absent, allowing the discount customer identifier to be reported regardless of referential completeness.

Key Columns

The view exposes a set of regular attributes, resolved foreign keys, and WHO audit columns:

• START_DATE / END_DATE — the active date range of the discount assignment, derived from START_DATE_ACTIVE and END_DATE_ACTIVE.
• CUSTOMER_CLASS_NAME — the customer class meaning from AR_LOOKUPS.
• DISCOUNT_NAME — the name of the discount from SO_DISCOUNTS.
• CUSTOMER_NAME — the customer name from RA_CUSTOMERS.
• CUSTOMER_SITE_LOCATION — the site-use location, plus the CUSTOMER_SITE_ADDRESS1 through ADDRESS4, CITY, STATE, POSTAL_CODE, PROVINCE, COUNTY, and COUNTRY address elements.
• DISCOUNT_CUSTOMER_ID, DISCOUNT_ID, CUSTOMER_ID, SITE_USE_ID, ADDRESS_ID — the resolved keys linking back to each base entity.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard audit columns carried from the assignment record.
• "_DF" — a literal descriptor string identifying the source context, '_DF:OE:SO_DISCOUNT_CUSTOMERS:DISCOUNT_CUST'.

Common Use Cases and Queries

The view is typically used to report which customers receive which discounts, and against which site, without requiring the caller to re-create the multi-table join. A representative query is:

• SELECT DISCOUNT_NAME, CUSTOMER_NAME, CUSTOMER_SITE_CITY, START_DATE, END_DATE FROM OEFV_DSCNTCASGNS WHERE SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE); — lists currently active discount assignments.
• SELECT CUSTOMER_CLASS_NAME, COUNT(*) FROM OEFV_DSCNTCASGNS GROUP BY CUSTOMER_CLASS_NAME; — summarizes assignments by customer class.
• A filter on SITE_USE_ID or CUSTOMER_ID returns all discounts tied to a given customer site, which is the common pattern when a business user searches on site use.