Discount Did Not Apply To Sales Order Line With Volume Offer As Modifier (Doc ID 1415593.1)

Last updated on SEPTEMBER 06, 2016

Applies to:

Oracle Trade Management - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms


No utilization records were found for some Orders even though Offer was applied, Order is booked, shipped and Closed..


Customer has noticed that some utilization records were missing for some Orders and Customers.

After running following SQL, it was identified that utilization record was missing for certain Orders.

select
fut.org_id,
fut.cust_account_id,
fut.component_type,
fut.component_id qp_list_header_id,
fut.volume_offer_tiers_id,
fut.object_type,
fut.object_id so_header_id,
fut.order_line_id,
fut.product_id inventory_item_id,
rc.customer_name,
rc.customer_number,
offers.offer_code,
offers.status_code offer_status,
msi.segment1,
msi.description,
fu.fund_number,
fut.utilization_type, --UTILIZATION_TYPE= ACCRUAL , ADJUSTMENT , UTILIZED ** Accrual and Adjustment are in the GL
fut.amount,
fut.currency_code,
fut.gl_date,
fut.adjustment_type,
fut.gl_posted_flag,
fu.liability_flag,
-- sales order and line details
oh.order_number,
oh.flow_status_code order_status,
oh.header_id order_header_id,
ol.line_id order_line_id,
ol.flow_status_code order_line_status,
rc.customer_id,
trunc(oh.ordered_date) ordered_date,
ol.line_number||'.'||ol.shipment_number order_line_number,
case
when ol.line_category_code = 'ORDER' then ol.ordered_quantity
else ol.ordered_quantity * (-1)
end ordered_quantity,
ol.order_quantity_uom,
ol.unit_selling_price,
ol.unit_list_price,
ol.cancelled_quantity,
nvl(ol.shipping_quantity,
ol.shipped_quantity * (-1) * -- Shipped quantity is populated for RMA
xxilgmf_costing_pkg.inv_um_conv(ol.order_quantity_uom,
ol.shipping_quantity_uom,
msi.inventory_item_id)) shipped_quantity,
nvl(ol.shipping_quantity,
ol.shipped_quantity * (-1) * -- Shipped quantity is populated for RMA
xxilgmf_costing_pkg.inv_um_conv(ol.order_quantity_uom,
ol.shipping_quantity_uom,
msi.inventory_item_id))*
xxilgmf_costing_pkg.inv_um_conv(ol.order_quantity_uom, -- From Qty im UOM
'kg', -------------------- To Qty im UOM
msi.inventory_item_id) shipped_quantity_kg,

(case
when ol.line_category_code = 'ORDER' then ol.ordered_quantity
else ol.ordered_quantity * (-1)
end

* ol.unit_selling_price) order_line_total
--
from ozf_funds_utilized_all_b fut,
ozf_offers offers,
ra_customers rc,
oe_order_lines_all ol,
oe_order_headers_all oh,
mtl_system_items_b msi,
ozf_funds_all_b fu
where fut.org_id=?
and fut.component_id=offers.qp_list_header_id
and fut.cust_account_id=rc.customer_id
and fut.order_line_id=ol.line_id
and oh.header_id=ol.header_id
and fut.org_id=ol.org_id
and fut.fund_id=fu.fund_id
and ol.inventory_item_id = msi.inventory_item_id
and msi.organization_id =?
--and oh.order_number='< Enter Order number>'
and rc.customer_number in ('?')
--and fut.gl_posted_flag='N'
--and fut.utilization_type in ('ACCRUAL','ADJUSTMENT') -- ****** For GL trx only *****
--group by fut.utilization_type

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms