Search Results qp_arch_pricing_attributes




Overview
Explore in AI Chat

Overview

QP_ARCH_PRICING_ATTRIBUTES is a QP (Advanced Pricing) schema table that stores archived pricing attribute qualification criteria associated with Advanced Pricing list lines and pricing phases. In Oracle EBS 12.1.1 and 12.2.2, this table functions as a companion archive equivalent of the online pricing attribute definition used by the pricing engine to determine which qualifiers a price list line or modifier responds to. Each row describes an individual attribute test — product attribute, pricing attribute, comparison operator, and the value range or pattern applied — that must be satisfied for a pricing entity to qualify.

The table is populated when pricing entities are archived, typically by concurrent purge or archive programs that relocate historical pricing definitions out of the active pricing tables. The presence of ARCH_PURG_REQUEST_ID and DISTINCT_ROW_COUNT confirms that rows are stamped with the archive request that produced them, enabling downstream retrieval by process run.

From a Data Vault modeling perspective, the heuristic classification surfaced in the ETRM metadata is standalone. This suggests modeling the table as an independent satellite-like structure keyed on its own surrogate, rather than as a pure hub or link, because the mined foreign-key relationships are limited and the table carries descriptive qualification detail rather than acting solely as an associative bridge.

Key Information Stored

The surrogate primary key is PRICING_ATTRIBUTE_ID, which uniquely identifies each archived pricing attribute row. LIST_LINE_ID and LIST_HEADER_ID are the principal business-key candidates, linking the attribute back to the price list line and header it qualifies. PRICING_PHASE_ID ties the attribute to the pricing phase in which it participates.

The qualification semantics are held in the pairing of PRODUCT_ATTRIBUTE_CONTEXT / PRODUCT_ATTRIBUTE / PRODUCT_ATTR_VALUE and PRICING_ATTRIBUTE_CONTEXT / PRICING_ATTRIBUTE, with PRICING_ATTR_VALUE_FROM and PRICING_ATTR_VALUE_TO defining range boundaries and their numeric counterparts PRICING_ATTR_VALUE_FROM_NUMBER and PRICING_ATTR_VALUE_TO_NUMBER carrying numeric comparisons. COMPARISON_OPERATOR_CODE captures the operator (=, BETWEEN, LIKE, and so on), while QUALIFICATION_IND and EXCLUDER_FLAG control whether the attribute qualifies or excludes a match. ATTRIBUTE_GROUPING_NO and ACCUMULATE_FLAG govern grouping and accumulator behavior.

Pattern-based matching is supported through PATTERN_VALUE_FROM_POSITIVE, PATTERN_VALUE_TO_POSITIVE, PATTERN_VALUE_FROM_NEGATIVE and PATTERN_VALUE_TO_NEGATIVE. SEARCH_IND and DISTINCT_ROW_COUNT support retrieval and cardinality reporting. Standard audit columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and the PROGRAM_/REQUEST_ID concurrency columns, with ARCH_PURG_REQUEST_ID identifying the originating archive run. Fifteen descriptive ATTRIBUTE1–ATTRIBUTE15 columns provide extensibility.

Common Use Cases and Queries

Typical scenarios include auditing historical qualification criteria after a price list has been archived, reconciling archive content against the active QP_PRICING_ATTRIBUTES table, and reporting on the attributes that drove a given pricing phase. A representative query retrieves all attributes for a pricing phase:

  • SELECT PRICING_ATTRIBUTE_ID, LIST_LINE_ID, PRICING_ATTRIBUTE, COMPARISON_OPERATOR_CODE, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO FROM QP_ARCH_PRICING_ATTRIBUTES WHERE PRICING_PHASE_ID = :phase_id;
  • SELECT * FROM QP_ARCH_PRICING_ATTRIBUTES WHERE ARCH_PURG_REQUEST_ID = :request_id; — isolates the rows produced by a specific archive run.
  • SELECT LIST_LINE_ID, COUNT(*) FROM QP_ARCH_PRICING_ATTRIBUTES GROUP BY LIST_LINE_ID; — measures qualification complexity per line.

These patterns support data migration validation, historical pricing analysis, and troubleshooting of pricing qualification regressions.

Related Objects

The documented foreign keys anchor this table to the core pricing model:

  • QP_LIST_LINES — joined via LIST_LINE_ID; the parent price list line being qualified.
  • QP_PRICING_PHASES — joined via PRICING_PHASE_ID; the phase in which the attribute applies.
  • QP_LIST_HEADERS — related through LIST_HEADER_ID; the price list or modifier header.
  • QP_PRICING_ATTRIBUTES — the active (non-archived) counterpart used for comparison.
  • QP_ARCH_LIST_LINES — archived list lines that correspond to LIST_LINE_ID.

Together these objects form the archival slice of the Advanced Pricing qualification model, enabling historical reconstruction of the criteria that governed past pricing behavior.