Results for “qpfv_coupon_issues”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The QPFV_COUPON_ISSUES view is a read-only reporting object in the Oracle Advanced Pricing (QP) module of Oracle E-Business Suite, owned by the APPS schema and validated in both 12.1.1 and 12.2.2. It exposes the configuration of Coupon Issue modifiers — the pricing entities that generate or issue coupons to customers — by consolidating modifier header information, coupon relationship data, and unit-of-measure details into a single denormalized result set. Because the underlying pricing tables store coupon issuance attributes across multiple normalized structures, this view provides an integration-friendly surface for reports, BI Publisher templates, Oracle Discoverer workbooks, and outbound interfaces that need to examine coupon validity windows, accrual behavior, and expiration rules without reconstructing the joins manually. It is defined with WITH READ ONLY, reflecting its intended role as a query-only construct rather than a transactional interface.

Underlying Base Objects

The view is defined over three documented base objects:

• QP_LIST_LINES (aliased LL) — the primary source, supplying coupon line attributes. The view filters on LIST_LINE_TYPE_CODE = 'CIE' to restrict output to Coupon Issue line types.
• QP_RLTD_MODIFIERS (aliased RLTD) — the relationship table that links the coupon modifier line to its related modifier group. It is joined on LL.LIST_LINE_ID = RLTD.FROM_RLTD_MODIFIER_ID with the constraint RLTD_MODIFIER_GRP_TYPE = 'COUPON'.
• MTL_UNITS_OF_MEASURE (aliased MTL) — joined with an outer join (EXPIRATION_PERIOD_UOM = MTL.UOM_CODE (+)) to resolve the expiration period UOM code to its descriptive name.

The relationship join is essential: a coupon issue line in QP_LIST_LINES is only returned when a corresponding Coupon-type relationship exists in QP_RLTD_MODIFIERS, ensuring that only fully configured coupon issuances appear.

Key Columns

• LIST_HEADER_ID / LIST_LINE_ID — Identifiers for the pricing list header and coupon issue line.
• BENEFIT_MODIFIER_LINE_ID — Exposed as TO_RLTD_MODIFIER_ID in the view text; identifies the related (benefit) modifier produced by the relationship.
• EXPIRATION_PERIOD_START_DATE — The anchor date from which expiration periods are counted.
• NUMBER_EXPIRATION_PERIODS — The count of periods (in the UOM defined by EXPIRATION_PERIOD_UOM) during which the coupon remains valid. This is the column associated with the user search term.
• EXPIRATION_PERIOD_UOM / UOM_NAME — The UOM code for the expiration period and its resolved name from MTL_UNITS_OF_MEASURE.
• EXPIRATION_DATE — The explicit expiration date, where populated.
• ACCRUAL_FLAG / ESTIM_GL_VALUE / ACCRUAL_CONVERSION_RATE — Accrual accounting configuration: whether accrual is enabled, the estimated GL value, and the conversion rate.
• PRICING_PHASE_ID / PRICING_GROUP_SEQUENCE / PRODUCT_PRECEDENCE — Govern evaluation order and precedence.
• MODIFIER_LEVEL_CODE / PRICE_BREAK_TYPE_CODE / INCOMPATIBILITY_GRP_CODE — Descriptive codes with associated _LA lookup-meaning columns for reporting.
• START_DATE_ACTIVE / END_DATE_ACTIVE — The active date range of the line.

Common Use Cases and Queries

Typical scenarios include auditing coupon expiration policies, verifying accrual configuration, and feeding coupon metadata into downstream systems.

Listing coupons with their expiration policy:

• SELECT list_header_id, list_line_no, expiration_period_start_date, number_expiration_periods, uom_name, expiration_date FROM qpfv_coupon_issues;

Identifying coupons with long expiration windows:

• SELECT list_line_id, number_expiration_periods, uom_name FROM qpfv_coupon_issues WHERE number_expiration_periods > 12 ORDER BY number_expiration_periods DESC;

Reviewing accrual-enabled coupon issues:

• SELECT list_header_id, list_line_id, accrural_flag, estim_gl_value, accrual_conversion_rate FROM qpfv_coupon_issues WHERE accrual_flag = 'Y';

Because the view is read-only and built on the pricing synonym layer, queries should be directed at it rather than at the base tables when coupon-specific reporting is required, preserving the documented filter semantics for CIE line types and Coupon relationships.