Search Results qp_patterns




Overview
Explore in AI Chat

Overview

QP_PATTERNS is a configuration table in the Oracle Advanced Pricing (QP) module of Oracle E-Business Suite, owned by the QP schema. It defines the pattern structures that govern how pricing attributes and segment values are organized and matched during pricing engine evaluation. In EBS Releases 12.1.1 and 12.2.2, this table supports the context and qualifier framework used by pricing rules, modifiers, and price lists, where pricing attributes (segments) are grouped into logical patterns for efficient evaluation and storage of pricing qualifier combinations.

The table formulates pattern definitions tied to HR segments defined in the HR_PATTERNS table and to pricing segment definitions stored in CSF_TDS_SEGMENTS. Based on the foreign key structure mined from the ETRM metadata, QP_PATTERNS is classified as a standalone object under a heuristic Data Vault model, meaning it neither receives aggregating hub-style references nor acts as a pure link between two hubs. In practice this classification suggests modeling QP_PATTERNS as an independent reference or lookup entity rather than a transactional link, though the presence of PATTERN_ID and SEGMENT_ID foreign keys indicates it participates in referencing HR and CSF configuration objects.

Key Information Stored

The documented physical schema for QP_PATTERNS in 12.2.2 contains 15 columns. The most significant are:

  • PATTERN_ID — Foreign key to HR_PATTERNS, identifying the parent pattern definition this row belongs to. This is the primary structural reference and the key join column for pattern-level reporting.
  • SEGMENT_ID — Foreign key to CSF_TDS_SEGMENTS, identifying the pricing segment (qualifier attribute) associated with the pattern.
  • PATTERN_TYPE — Classifies the pattern, distinguishing pricing-context groupings (e.g., qualifier versus pricing attribute patterns).
  • PATTERN_STRING — Stores the concatenated or formatted representation of the segment combination that defines the pattern match.
  • SEGMENT_INDEX — Ordinal position of the segment within the pattern, critical for reconstructing the ordered qualifier sequence.
  • SEGMENT_COUNT — Number of segments included in the pattern, used to validate pattern completeness and drive matching logic.

The surrogate primary key is PATTERN_ID combined with SEGMENT_INDEX (or SEGMENT_ID) in composite form, while PATTERN_ID alone serves as the business-key reference to HR_PATTERNS. Standard EBS audit columns are also present: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID. These support the concurrent manager audit trail and are essential for diagnosing configuration changes.

Common Use Cases and Queries

QP_PATTERNS is queried primarily to understand how pricing qualifiers are grouped and ordered, and to diagnose pricing engine match failures. Typical scenarios include verifying that a price list or modifier qualifier maps to the expected segment sequence, and auditing pattern definitions after configuration changes.

  • Confirm how a pricing pattern is associated with its HR pattern definitions and segments across a patch or upgrade.
  • Extract reporting rows that expose pricing pattern definitions and associated segment context for analytics.
  • Diagnose modifier or proration issues by checking the ordering and completeness of segment combinations.

Representative SQL patterns:

SELECT pp.pattern_id, pp.segment_id, pp.pattern_type, pp.pattern_string,
       pp.segment_index, pp.segment_count
FROM   qp_patterns pp
WHERE  pp.pattern_id = :pattern_id
ORDER BY pp.segment_index;

SELECT pp.pattern_id, hp.pattern_name, cs.segment_name
FROM   qp_patterns pp,
       hr_patterns hp,
       csf_tds_segments cs
WHERE  pp.pattern_id = hp.pattern_id
  AND  pp.segment_id = cs.segment_id;

Related Objects

The following objects share documented relationships with QP_PATTERNS:

  • HR_PATTERNS — Referenced by QP_PATTERNS.PATTERN_ID; the parent pattern header supplying the business pattern definition.
  • CSF_TDS_SEGMENTS — Referenced by QP_PATTERNS.SEGMENT_ID; supplies the segment (pricing attribute) metadata that the pattern lists.
  • QP_QUALIFIERS and QP_PRICING_ATTRIBUTES, which link pricing rules and modifiers to the underlying segment and pattern structures when the pricing engine resolves a qualifier combination.
  • QP_LIST_HEADERS and QP_MODIFIERS, the pricing entities that consume the qualifier and pattern configuration to evaluate price lists and modifiers.
  • Pricing engine PL/SQL APIs such as QP_ATTR_MAPPING and QP_PREQ_GRP_PUB, which read pattern configuration during price calculation and qualifier matching.