Search Results oracle forms 12c set_menu_item_property documentation




The QP_ARCH_PRICING_ATTRIBUTES table in Oracle E-Business Suite (EBS) 12.1.1 or 12.2.2 is a critical data structure within the Pricing module (Order Management & Advanced Pricing). It serves as an archival repository for historical pricing attribute data, ensuring traceability and compliance while optimizing performance by segregating active and inactive records. Below is a detailed technical breakdown of its purpose, structure, and functional significance.

Purpose & Functional Context

The QP_ARCH_PRICING_ATTRIBUTES table stores archived pricing attribute records that are no longer actively referenced but must be retained for audit, reporting, or historical analysis. In Oracle Advanced Pricing, attributes define conditions for price adjustments (e.g., customer segment, product category, or geographic region). As pricing rules evolve, older attribute data is moved to this table to:

  • Improve Performance: Reduces the volume of active data in operational tables like QP_PRICING_ATTRIBUTES, enhancing query efficiency.
  • Ensure Compliance: Maintains a historical record for regulatory or financial auditing.
  • Support Analytics: Enables trend analysis of pricing strategies over time.

Key Columns & Structure

The table's schema typically includes the following columns (specifics may vary slightly between EBS 12.1.1 and 12.2.2):

Column Name Data Type Description
ARCHIVE_ID NUMBER Primary key, unique identifier for archived records.
PRICING_ATTRIBUTE_ID NUMBER Foreign key referencing the original record in QP_PRICING_ATTRIBUTES.
LIST_HEADER_ID NUMBER Links to the price list (QP_LIST_HEADERS) associated with the attribute.
ATTRIBUTE_TYPE VARCHAR2 Defines the attribute category (e.g., 'CUSTOMER', 'PRODUCT').
OPERATOR VARCHAR2 Comparison logic (e.g., '=', 'BETWEEN') used in pricing rules.
ATTRIBUTE_VALUE VARCHAR2 Specific value or range for the attribute (e.g., customer ID '10045').
CREATION_DATE DATE Timestamp when the original record was created.
ARCHIVE_DATE DATE Timestamp when the record was archived.

Integration & Workflow

The archiving process is typically triggered by:

  1. Pricing Engine Updates: When a price list or modifier is revised, obsolete attributes are moved to QP_ARCH_PRICING_ATTRIBUTES via PL/SQL APIs or concurrent programs.
  2. Data Purging Jobs: Scheduled processes (e.g., QP_CLEANUP_PKG) archive records based on retention policies.

Downstream integrations include:

  • Reporting: Historical pricing reports may join this table with QP_ARCH_LIST_LINES for comprehensive analysis.
  • Audit Trails: Linked to FND_AUDIT_SCHEMAS for change tracking.

Technical Considerations

  • Indexing: Typically indexed on ARCHIVE_ID, PRICING_ATTRIBUTE_ID, and LIST_HEADER_ID for retrieval efficiency.
  • Partitioning: In high-volume environments, partitions by ARCHIVE_DATE may be implemented.
  • Retention Policies: Governed by Oracle's Data Retention Manager or custom scripts.

Conclusion

The QP_ARCH_PRICING_ATTRIBUTES table is a foundational component of Oracle Advanced Pricing's data lifecycle management. By decoupling historical data from active transactional tables, it balances performance with regulatory requirements, ensuring robust pricing governance in EBS 12.1.1 and 12.2.2 environments.