Search Results start_date_changed

Overview

OEFV_CUSTOMER_AGREEMENTS is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Order Management (ONT) product family. It presents a consolidated, denormalized projection of customer agreement data maintained in Oracle Order Management, combining agreement header attributes with related customer, contact, address, salesperson, pricing, and terms information. Its designation as a view rather than a base table means it exposes no independent storage; instead it resolves at query time against the underlying agreement and Oracle Trading Community Architecture (TCA) entities.

The view serves reporting, inbound integration, and user-facing query needs where agreement context must be retrieved in a single pass. Column aliases prefixed with _LA: and _DF: indicate that the view is designed for use by Oracle's descriptive flexfield and lookup display conventions, allowing agreement type, override rule flags, and agreement references to surface as display-ready values. Because the referenced objects include HZ_PARTIES, HZ_CUST_ACCOUNTS, and HZ_CUST_ACCOUNT_ROLES, contact resolution is anchored in TCA rather than legacy contact tables. The view is documented as VALID in the 12.2.2 ETRM metadata and is also present in 12.1.1.

Underlying Base Objects

The documented base objects are: AR_LOOKUPS (VIEW), HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_PARTIES, HZ_PARTY_SITES, HZ_RELATIONSHIPS, OE_AGREEMENTS_B, OE_AGREEMENTS_TL, QP_LIST_HEADERS_TL, RA_RULES, RA_TERMS_TL, and the OE_BIS_SALESPERSON package. Agreements are sourced from OE_AGREEMENTS_B, the base table holding agreement header records, with OE_AGREEMENTS_TL supplying translated agreement names. Customer and contact data flow through the TCA hierarchy.

HZ_PARTIES provides PARTY_NAME, exposed as CUSTOMER_NAME, while HZ_CUST_ACCOUNT_ROLES and HZ_RELATIONSHIPS link contacts to customer accounts. HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTY_SITES, and HZ_LOCATIONS together resolve invoice-to site and address details. AR_LOOKUPS supplies meaning values for contact role codes; RA_TERMS_TL and RA_RULES provide payment terms and accounting/invoicing rules; QP_LIST_HEADERS_TL supplies the price list name. The OE_BIS_SALESPERSON package function GET_SALESPERSON_NAME resolves the sales representative name at query time. Because most TCA references are synonyms in APPS, the view resolves across schemas transparently.

Key Columns

• AGREEMENT_NUM, REVISION, START_DATE_ACTIVE, END_DATE_ACTIVE, SIGNATURE_DATE — agreement identifiers and effective-dating attributes from OE_AGREEMENTS_B.
• AGREEMENT_ID, SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, AGREEMENT_CONTACT_ID, INVOICE_CONTACT_ID — primary and foreign keys linking to customer, site, and contact records.
• CUSTOMER_NAME — resolved from HZ_PARTIES.PARTY_NAME.
• INVOICE_CONTACT_LAST_NAME, INVOICE_CONTACT_FIRST_NAME, INVOICE_CONTACT_NAME — invoice contact attributes; the last name is truncated to 50 bytes, and the concatenated name appends first name and role meaning.
• AGREEMENT_CONTACT_LAST_NAME, AGREEMENT_CONTACT_FIRST_NAME, AGREEMENT_CONTACT_NAME — the equivalent contact triplet for the agreement-level contact.
• INVOICE_SITE_USE.LOCATION and INVOICE_TO_ADDR2 address columns — formatted invoice-to location and address lines.
• SALESREP_NAME — computed via OE_BIS_SALESPERSON.GET_SALESPERSON_NAME.
• TERM.NAME, PRICE_LIST.NAME, RULE1.NAME, RULE2.NAME — terms, price list, and rule descriptions.

Common Use Cases and Queries

A frequent requirement is retrieving agreements by invoice contact last name, since the user search term invoice_contact_last_name maps directly to a column on this view. A representative query is:

SELECT agreement_num, customer_name, invoice_contact_last_name, invoice_contact_first_name, start_date_active, end_date_active FROM apps.oefv_customer_agreements WHERE invoice_contact_last_name = 'SMITH' ORDER BY customer_name, start_date_active DESC;

Report authors frequently join OE_AGREEMENTS and OE_AGREEMENT_LINES to this view on AGREEMENT_ID to reconcile header context with line detail. Integration developers use it to extract agreement header and contact data without navigating the TCA joins manually. Filters on START_DATE_ACTIVE and END_DATE_ACTIVE support effective-date reporting, while CUSTOMER_NAME and AGREEMENT_NUM support search pages. Because the view includes descriptive flexfield and lookup display columns, it is also suitable for DFF-enabled inquiries. Note that the view is read-only and carries no indexes of its own; performance depends on predicates against OE_AGREEMENTS_B and the TCA base tables.