Search Results op_prce_eff




Overview
Explore in AI Chat

Overview

The OP_PRCE_EFF table is a core data object within the Process Manufacturing Logistics (GML) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the central repository for pricing effectivity rules, which are critical for establishing and managing the active status of various pricing components within the system. Its primary role is to define the conditions—such as date ranges, customer assignments, and organizational context—under which a specific price list, sales contract, or charge becomes applicable for a transaction. This table enables complex, time-based pricing strategies and ensures the correct price is automatically determined during order entry and other logistics processes.

Key Information Stored

The table stores the criteria that link pricing entities to business contexts. Its structure includes two primary keys: a composite key (OP_PRCE_EFFI1) for business rule uniqueness and a surrogate key (OP_PRCE_EFF_PK) for system relationships. Key columns define the scope and validity of a pricing rule. The ORGN_CODE specifies the operating unit. Customer targeting is managed through CUST_ID, CUSTPRICE_CLASS, and TERRITORY. The temporal validity is controlled by START_DATE and END_DATE. The rule's priority among conflicting rules is set by PREFERENCE. The EFFECTIVITY_TYPE column determines the rule's nature. Finally, the table points to the specific pricing entity via PRICELIST_ID, CONTRACT_ID, or CHARGE_ID, establishing the link between the rule and the actual price, terms, or charge.

Common Use Cases and Queries

A primary use case is troubleshooting order pricing during sales order entry. When a price does not appear as expected, consultants often query this table to verify an active, effective rule exists for the customer, organization, and date. Another common scenario is generating reports on active pricing agreements for a customer or analyzing pricing rule coverage across territories. Sample queries include finding all effective price lists for a specific customer on a given date, or identifying rules that will become active in the future.

  • Finding active price lists for a customer: SELECT PRICELIST_ID FROM GML.OP_PRCE_EFF WHERE ORGN_CODE = :org AND CUST_ID = :cust AND :order_date BETWEEN START_DATE AND NVL(END_DATE, :order_date) AND PRICELIST_ID IS NOT NULL ORDER BY PREFERENCE;
  • Auditing all effectivity rules for a contract: SELECT * FROM GML.OP_PRCE_EFF WHERE CONTRACT_ID = :contract_id ORDER BY START_DATE;

Related Objects

OP_PRCE_EFF is a central hub connected to numerous master and transactional tables via foreign key relationships. It directly references master data tables: OP_PRCE_MST (Price Lists), OP_CNTR_HDR (Contracts), OP_CHRG_MST (Charges), OP_CUST_MST (Customers), OP_CPRC_CLS (Customer Price Classes), and SY_ORGN_MST (Organizations). Crucially, it is referenced by the transactional table OP_ORDR_DTL (Order Details), where the selected PRICEFF_ID is stored to document which pricing effectivity rule was applied to each order line. This creates a complete audit trail from the master pricing rule to the final transaction.