Search Results oe_pricing_contracts_v




Overview
Explore in AI Chat

Overview

OE_PRICING_CONTRACTS_V is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, historically delivered under the QP (Advanced Pricing) product family. In both EBS 12.1.1 and 12.2.2 the object is documented as Obsolete, and ETRM reports a status of VALID at the database level. It was originally designed to flatten pricing agreements — the "pricing contracts" maintained through Oracle Order Management agreements and QP price lists — into a single denormalized result set combining agreement header attributes, customer and contact information, address data, freight terms, currency and rounding attributes, and the descriptive flexfield columns (CONTEXT, ATTRIBUTE1 through ATTRIBUTE15). Because Oracle replaced the underlying agreement model with Oracle Trading Community Architecture (TCA) and moved pricing contract functionality to later-generation APIs, the view is retained only for backward compatibility. Legacy customizations, Discoverer workbooks, and ad hoc reports that still reference it will continue to compile, but Oracle does not recommend new development against it.

Underlying Base Objects

The view is defined over a mixture of QP views, legacy Oracle Receivables (RA) synonyms, and TCA synonyms. Its FROM clause joins OE_AGREEMENTS_VL (the agreement header VL view supplying the SA alias), QP_LIST_HEADERS (SP), QP_LOOKUPS (SL), SO_FREIGHT_TERMS_ACTIVE_V (FT), RA_TERMS (RT), RA_RULES (RR and RR2), RA_CONTACTS (RA and RC2), RA_CUSTOMERS (RC and C), and RA_ADDRESSES (D). The documented 12.2.2 dependency list confirms that the older RA_% synonyms are resolved onto TCA objects, including HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES, HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_GEOGRAPHIES, HZ_GEO_NAME_REFERENCE_LOG, and HZ_GEO_STRUCT_MAP. It also calls the QP_UTIL and FND_GLOBAL packages. All joins to contacts and site uses are outer joins (the (+) notation in the view text), so an agreement without a matching contact or address still returns a row. The implied joins are driven by AGREEMENT_ID, PRICE_LIST_ID, CUSTOMER_ID, ADDRESS_ID, TERM_ID, and SALESREP_ID.

Key Columns

The view exposes agreement identification and versioning columns: AGREEMENT_ID, AGREEMENT_NUM, REVISION, REVISION_REASON_CODE, REVISION_DATE, and AGREEMENT_TYPE_CODE. Customer-facing columns include CUSTOMER_NAME, CUSTOMER_NUMBER, SHIP_TO and invoice-to organization identifiers (SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID), contact identifiers (INVOICE_CONTACT_ID, AGREEMENT_CONTACT_ID), and a formatted contact name built with DECODE. Address data is presented as ADDRESS1, ADDRESS2, and a concatenated CITY/STATE/POSTAL_CODE string. Pricing and commercial terms are represented by PRICE_LIST_ID, LIST_TYPE_CODE, CURRENCY_CODE, ROUNDING_FACTOR, TERM_ID, FREIGHT_TERMS_CODE, FREIGHT_TERMS, SHIP_METHOD_CODE, INVOICING_RULE_ID, and ACCOUNTING_RULE_ID. Effective dating is carried by START_DATE_ACTIVE and END_DATE_ACTIVE, while SALESREP_ID is translated through QP_UTIL.GET_SALESREP. Audit columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN, along with the ORIG_SYSTEM_AGR_ID and ORIG_SYSTEM_AGR_DESC pair used for external system reconciliation.

Common Use Cases and Queries

Typical uses include auditing active pricing agreements, reconciling agreement revisions with customer master data, and extracting pricing contract data for a data warehouse. A representative query filters by effective date and customer:

  • SELECT AGREEMENT_NUM, REVISION, CUSTOMER_NAME, PRICE_LIST_ID, CURRENCY_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE FROM APPS.OE_PRICING_CONTRACTS_V WHERE CUSTOMER_NUMBER = :p_customer AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
  • SELECT AGREEMENT_ID, AGREEMENT_NUM, CUSTOMER_ID, SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID FROM APPS.OE_PRICING_CONTRACTS_V WHERE AGREEMENT_TYPE_CODE = :p_type ORDER BY LAST_UPDATE_DATE DESC;
  • SELECT AGREEMENT_NUM, ADDRESS1, ADDRESS2, FREIGHT_TERMS, SHIP_METHOD_CODE FROM APPS.OE_PRICING_CONTRACTS_V WHERE SALESREP_ID = :p_salesrep;

Because the search term "old_customer_id" is not a column of this view, no such attribute is exposed here; customer identity is published only through CUSTOMER_ID, CUSTOMER_NAME, and CUSTOMER_NUMBER. The "old_customer_id" concept belongs to pre-TCA customer tables and is not part of OE_PRICING_CONTRACTS_V. Given the obsolete classification, any ongoing dependency on this view should be reviewed and migrated to supported QP agreement APIs or TCA-based reporting views.