Search Results start_date_changed

Overview

The APPS.OEFV_ORDER_AGREEMENTS view is a predefined Oracle E-Business Suite database object owned by the APPS schema and classified under the ONT (Order Management) product. In ETRM releases 12.1.1 and 12.2.2 it holds a VALID status and functions as a denormalized, reporting-oriented presentation layer over the order agreement (sales agreement) data model. Rather than requiring report authors and integrators to join the agreement header, its translations, receivables rules, customer address hierarchy, salesperson, terms, and price list tables individually, the view exposes all of these attributes in a single flattened result set. It is commonly consumed by Oracle Reports, custom concurrent programs, Discoverer workbooks, OBIEE/BI Publisher data models, and ad hoc SQL used by order management and pricing analysts.

A distinguishing characteristic of this view is that several of its columns are not plain database values but are returned as descriptive-flex or lookup-convention expressions, for example '_LA:AGREEMENT_TYPE_CODE:QP_LOOKUPS:QP_AGREEMENT_TYPE:MEANING', '_LA:OVERRIDE_ARULE_FLAG:OE_LOOKUPS:YES_NO:MEANING', and '_LA:OVERRIDE_IRULE_FLAG:OE_LOOKUPS:YES_NO:MEANING'. The leading '_LA:' token instructs the Oracle EBS "lookup attribute" mechanism to translate the stored lookup code into its meaning at runtime, while '_DF:' entries reference descriptive flexfield definitions. Consumers must therefore be aware that the returned values may be interpreted by the calling tool rather than being literal table contents.

Underlying Base Objects

Per the documented 12.2.2 metadata, the view is defined over the following APPS synonyms and program units:

• OE_AGREEMENTS_B – the base table holding agreement header data such as agreement number, dates, sold-to and invoice-to organizations, salesrep, terms, and price list identifiers.
• OE_AGREEMENTS_TL – the translation table supplying the language-dependent agreement name, joined with T.LANGUAGE = USERENV('LANG').
• HZ_CUST_SITE_USES_ALL, HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES, and HZ_LOCATIONS – the Trading Community Architecture (TCA) hierarchy used to resolve the invoice-to site use and its formatted address.
• RA_RULES – joined twice (aliased RULE1 and RULE2) to resolve the invoicing rule and accounting rule names via outer joins.
• RA_TERMS_TL – provides the translated payment term name.
• QP_LIST_HEADERS_TL – provides the translated price list name.
• OE_BIS_SALESPERSON – a PL/SQL package whose GET_SALESPERSON_NAME function is invoked to derive the salesperson name from SALESREP_ID.

The joins are predominantly outer joins ((+) syntax), ensuring agreements are returned even when optional rule, site, or address data is absent.

Key Columns

• AGREEMENT_ID, AGREEMENT_NUM, REVISION – primary identifier and human-readable agreement number plus revision.
• NAME (from OE_AGREEMENTS_TL) – translated agreement description.
• START_DATE_ACTIVE, END_DATE_ACTIVE, SIGNATURE_DATE – agreement validity and execution dates.
• PURCHASE_ORDER_NUM – customer purchase order reference.
• SALESREP_NAME, SALESREP_ID – salesperson resolved via OE_BIS_SALESPERSON.
• Invoice-to address columns (LOCATION, ADDRESS1–ADDRESS4, CITY, STATE, POSTAL_CODE, PROVINCE, COUNTY, COUNTRY) – resolved from the TCA tables.
• INVOICING_RULE_ID, ACCOUNTING_RULE_ID with their resolved RULE1.NAME/RULE2.NAME.
• TERM_ID with term name, and PRICE_LIST_ID with price list name.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include listing active agreements for a sold-to customer, reporting agreements by salesperson, and validating pricing or invoicing rule assignments. Because the view already resolves names and addresses, it is well suited to a single-table query.

Example: active agreements with customer and term details.

• SELECT agreement_num, name, start_date_active, end_date_active, salesrep_name, location, city FROM apps.oefv_order_agreements WHERE TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, TRUNC(SYSDATE)+1) ORDER BY agreement_num;

Example: agreements by sold-to organization.

• SELECT agreement_num, name, purchase_order_num, price_list_id FROM apps.oefv_order_agreements WHERE sold_to_org_id = :p_sold_to_org_id;

Note that lookup-translated columns (agreement type and override flags) are returned as encoded tokens and should be interpreted by the consuming reporting tool; direct SQL extraction may require the corresponding QP_LOOKUPS or OE_LOOKUPS query. The view is read-only and should not be used for transactional DML.