Results for “qp_coupons”

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

Overview

QP_COUPONS is a table in the QP (Advanced Pricing) schema of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It stores coupon records that have been issued against pricing modifiers, capturing the identity, validity window, and redemption state of each coupon. In Advanced Pricing, coupons are delivered as a promotional mechanism tied to modifier list lines, allowing organizations to issue and track discount instruments that customers present at order entry or invoicing. The table is owned by the QP schema and holds a VALID status in the data dictionary.

From a dimensional modeling perspective, the relationship metadata suggests a satellite-leaning classification. QP_COUPONS is anchored by the surrogate primary key COUPON_ID and references QP_LIST_LINES through ISSUED_BY_MODIFIER_ID, which implies the table records descriptive attributes and state changes about a coupon entity whose parent context lives in the pricing modifier model. This classification is a heuristic mined from the foreign key structure and should be treated as a modeling suggestion rather than a mandated design.

Key Information Stored

QP_COUPONS contains 30 documented columns. The most significant are:

• COUPON_ID — the surrogate primary key, enforced by the QP_COUPONS_PK unique index. This is the sole documented business-key candidate and the join key to dependent tables.
• COUPON_NUMBER — the primary identifier presented to customers and referenced at redemption.
• USER_DEF_COUPON_NUMBER and GENERATED_COUPON_NUMBER — distinguish manually assigned numbers from system-generated ones, supporting flexible numbering schemes.
• ISSUED_DATE, START_DATE, and EXPIRATION_DATE — define the issuance date and the active validity window during which the coupon may be applied.
• ISSUED_BY_MODIFIER_ID — the foreign key to QP_LIST_LINES that ties the coupon to the pricing modifier line that governs its discount behavior.
• REDEEMED_FLAG — indicates whether the coupon has already been consumed, enabling one-time-use enforcement.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield columns for extensibility.

Common Use Cases and Queries

Typical reporting scenarios include identifying active versus expired coupons, reconciling issued versus redeemed counts, and auditing coupons tied to a specific promotional modifier. A common query pattern lists unredeemed coupons within their validity window:

• SELECT c.coupon_number, c.issued_date, c.expiration_date FROM qp_coupons c WHERE c.redeemed_flag = 'N' AND SYSDATE BETWEEN c.start_date AND c.expiration_date;
• Join to QP_LIST_LINES to resolve the governing modifier: SELECT c.coupon_number, l.list_line_id FROM qp_coupons c, qp_list_lines l WHERE c.issued_by_modifier_id = l.list_line_id;
• Redemption analysis by modifier for promotional effectiveness reporting.
• Audit of user-defined versus generated coupon numbers to validate numbering controls.

Related Objects

QP_COUPONS is most tightly coupled to the following objects:

• QP_LIST_LINES — referenced via ISSUED_BY_MODIFIER_ID; the modifier line governing each coupon's discount.
• QP_LIST_HEADERS_B and QP_LIST_HEADERS_TL — the parent pricing list headers reached through list lines.
• QP_PRICE_BREAKS — break definitions on the associated modifier line that determine discount tiers.
• QP_QUALIFIERS — qualification rules that determine coupon eligibility.
• QP_PRICING_ATTRIBUTES — pricing attribute context applied to the modifier.
• OE_ORDER_LINES_ALL — order lines where applied coupons are recorded at order entry.
• QP_COUPON_LIMITS — where present, constrains coupon usage volume.

These relationships make QP_COUPONS the central reference point for coupon issuance and redemption reporting within Advanced Pricing.