Search Results ap_terms




Overview
Explore in AI Chat

Overview

EDW_AP_PAY_TERM_MPV is a database view owned by the APPS schema in Oracle E-Business Suite, classified under the FII (Financial Intelligence) product family. In Release 12.1.1 and 12.2.2, this view serves as a data validity (DV) source-side construct used by the Oracle Financial Intelligence and Enterprise Data Warehouse (EDW) extraction layer. Its stated purpose in the ETRM documentation is to provide a validated, consolidated source for payment terms data drawn from both Payables and Receivables before that data is loaded into the analytical warehouse.

Payment terms are configuration-driven reference data that govern due date calculation, discount periods, and installment schedules. Because these terms are defined independently in Oracle Payables (AP) and Oracle Receivables (AR), the warehouse requires a unified, de-duplicated, and instance-tagged representation. EDW_AP_PAY_TERM_MPV fulfills this role by projecting a harmonised key across both module-specific term tables, enabling downstream dimensions and fact loads to reference payment terms consistently regardless of the originating subledger.

The suffix "MPV" is characteristic of Financial Intelligence source-side views (materialised or mapping validity views), while the "EDW_" prefix identifies the object as belonging to the enterprise data warehouse staging namespace. The view is documented as VALID and is therefore safe to query in a supported EBS environment.

Underlying Base Objects

The view is defined as a UNION ALL over two base tables:

A reference to the package function EDW_INSTANCE.GET_CODE is embedded in the SELECT list. This function returns the current EBS instance identifier (for example, the instance short code), which is concatenated into the derived key so that records from multiple source instances can coexist in the warehouse without collision. Note that the ETRM 12.2.2 metadata documents no explicit referenced base objects; the relationship to AP_TERMS and RA_TERMS is derived from the view text itself rather than from declared foreign-key metadata.

Key Columns

The view exposes two columns, both of which are derived rather than physically stored:

  • DIM_PK — the composite dimensional primary key. It is constructed as TO_CHAR(TERM_ID) || '-' || 'AP-' or 'AR-' concatenated with the truncated instance code (first 40 bytes via SUBSTRB). The embedded module token ("AP" or "AR") and instance code guarantee uniqueness across subledgers and instances, while TERM_ID links back to the originating AP_TERMS or RA_TERMS row.
  • DIM_PK_DATE — the LAST_UPDATE_DATE sourced from the relevant base table. This column supports incremental (delta) extraction and slowly changing dimension (SCD) logic by indicating when the payment term record was last modified.

Filtering is applied in the AP branch only: rows are restricted to those where NVL(ENABLED_FLAG, 'Y') = 'Y', meaning disabled Payables terms are excluded, while NULL enabled flags default to enabled. The AR branch applies no such filter.

Common Use Cases and Queries

Typical uses include populating an EDW payment terms dimension, validating that all active source terms have been captured, and driving incremental loads based on the last update timestamp.

  • Loading the full term dimension: SELECT DIM_PK, DIM_PK_DATE FROM APPS.EDW_AP_PAY_TERM_MPV;
  • Incremental extraction since a prior load: SELECT DIM_PK, DIM_PK_DATE FROM APPS.EDW_AP_PAY_TERM_MPV WHERE DIM_PK_DATE >= :last_run_date;
  • Separating Payables from Receivables terms by inspecting the module token embedded in DIM_PK: WHERE DIM_PK LIKE '%-AP-%' or LIKE '%-AR-%'.

Because DIM_PK is a derived string, joins to fact tables should match on the complete concatenated key rather than on TERM_ID alone. Queries should be issued against the APPS schema (or a synonym) with appropriate read privileges, and the union nature of the view means duplicate TERM_ID values across AP and AR are expected and intentionally preserved.