Pricing Logic in Oracle EBS R12.2.14

Interfaces, Order Management, Inventory — Plus Real SQL You Can Reuse

Pricing in Oracle EBS R12.2.14 is not just a setup topic—it’s a data-driven execution engine that directly affects orders, inventory movement, accounting, and revenue.

Whether orders are entered manually or imported via interfaces, pricing must be accurate, traceable, and scalable.

This article explains how pricing works, where it breaks, and gives you ready-to-use SQL objects you can adapt for real business scenarios.

---

Pricing Architecture Recap (Fast)

Pricing in EBS is driven by:

• Advanced Pricing Engine
• Price Lists
• Modifiers (Discounts / Surcharges)
• Qualifiers & Attributes
• Order Management integration

Inventory provides item eligibility, OM executes pricing calls, and interfaces must respect both.

---

🔍 Sample Query: Validate Item Pricing Eligibility

Use this before interfacing orders.

SELECT
    msi.inventory_item_id,
    msi.segment1 item_code,
    msi.description,
    msi.pricing_enabled_flag,
    msi.sales_account
FROM
    mtl_system_items_b msi
WHERE
    msi.organization_id = :org_id
AND msi.pricing_enabled_flag = 'Y';

Business Use Case

✔ Ensure items can be priced
✔ Prevent zero-price orders
✔ Validate Inventory–Pricing alignment

---

🔍 Sample Query: Get Active Price List Line

This confirms whether a base price exists.

SELECT
    qpl.name price_list,
    qll.inventory_item_id,
    qll.operand unit_price,
    qll.start_date_active,
    qll.end_date_active
FROM
    qp_list_headers qpl,
    qp_list_lines qll
WHERE
    qpl.list_header_id = qll.list_header_id
AND qpl.active_flag = 'Y'
AND qll.inventory_item_id = :item_id
AND SYSDATE BETWEEN
    NVL(qll.start_date_active, SYSDATE)
AND NVL(qll.end_date_active, SYSDATE);

Business Use Case

✔ Troubleshoot missing prices
✔ Validate effective dates
✔ Support audit & pricing governance

---

📊 Sample View: Order Pricing Audit View

Perfect for Finance, Sales, and IT reconciliation.

CREATE OR REPLACE VIEW xxom_order_pricing_v AS
SELECT
    oh.order_number,
    ol.line_number,
    ol.inventory_item_id,
    ol.ordered_quantity,
    ol.unit_selling_price,
    ol.list_price_per_unit,
    (ol.list_price_per_unit - ol.unit_selling_price) discount_amount,
    oh.ordered_date
FROM
    oe_order_headers_all oh,
    oe_order_lines_all ol
WHERE
    oh.header_id = ol.header_id;

Business Use Case

✔ Identify discounts
✔ Validate pricing overrides
✔ Revenue and margin analysis

---

🧮 Sample Function: Calculate Discount Percentage

Reusable across reports, interfaces, and validations.

CREATE OR REPLACE FUNCTION xx_get_discount_pct (
    p_list_price   NUMBER,
    p_sell_price   NUMBER
) RETURN NUMBER IS
BEGIN
    IF p_list_price = 0 THEN
        RETURN 0;
    ELSE
        RETURN ROUND(
            ((p_list_price - p_sell_price) / p_list_price) * 100,
            2
        );
    END IF;
END;
/

Business Use Case

✔ KPI reporting
✔ Sales performance dashboards
✔ Pricing compliance checks

---

⚙ Sample Stored Procedure: Interface Pricing Validator

This is a pre-import safety net.

CREATE OR REPLACE PROCEDURE xx_validate_iface_pricing (
    p_item_id     NUMBER,
    p_price_list  NUMBER
) IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM qp_list_lines
    WHERE inventory_item_id = p_item_id
    AND list_header_id = p_price_list
    AND SYSDATE BETWEEN
        NVL(start_date_active, SYSDATE)
    AND NVL(end_date_active, SYSDATE);

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Pricing not found for item in price list'
        );
    END IF;
END;
/

Business Use Case

✔ Prevent failed OM imports
✔ Validate interface data
✔ Reduce support tickets

---

📦 Interface Reality: Pricing Best Practices

When importing orders via:

• OE_HEADERS_IFACE_ALL
• OE_LINES_IFACE_ALL

Best Approach

✔ Pass item, quantity, customer
✔ Let Oracle calculate pricing
✔ Avoid hardcoded prices

Only Override Pricing If:

• Contractual pricing exists
• Regulatory pricing applies
• CFO-approved exception is documented

---

Why This Matters to the Business

Incorrect pricing causes:

• Revenue leakage
• Audit findings
• AR reconciliation issues
• Customer disputes

Correct pricing delivers:

• Clean interfaces
• Accurate revenue
• Scalable integrations
• Trust between IT & Finance

---

Final Takeaway

Pricing Logic in Oracle EBS R12.2.14 is not just configuration—it’s business logic encoded in data.

When Order Management, Inventory, and Interfaces align:
✔ Pricing works
✔ Accounting flows
✔ Business scales

When they don’t—everything breaks.

---

#OracleEBS #OracleR12 #AdvancedPricing #OrderManagement #InventoryManagement #OracleSQL
#ERPIntegration #FinanceIT #OracleFunctional #ERPArchitecture