[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
get selected options from cz_pricing_structures table.
For list price, CZ sends to this API qty per unit model.
For selling price, Configurator sends to this API the actual
ordered quantity.
Configurator send to us the config_session_key. This key is what we
passed to them initially when the configurator form was opened.
This is of the form top_model_line_id#session_id. We use this key
to get the top_model_line_id. It is also the primary key for the CZ
table from which we get the options to price.
For each option sent in we do the following:
1. Get inventory_item_id from the item_key.
item_key in cz_pricing_structures table is concatenation of
component_code, explosion_type, organization_id, top_item_id.
These values are separated by ':'. We will parse the item_key
to get inventory_item_id(from the component code)
The order of concatenation is imp, if Configurator changes it,
there will be bugs.
2. Create the line records (for new options).
For a restored configuration, we use the unit_list_price, and
unit_selling_price from order management tables directly.
Call price_lines API for all the new options. Update the CZ pricing
structures table with list price/selling price obtained from the
pricing API.
Change Record:
bug fix: if in an existing configuration. qty of options modified,
selling price will be affected (list price we always get per unit.)
do not update using old pricing data from oe_order_lines.
oe_debug_pub.add('updating selling prices of already saved options', 1);
SELECT cz.item_key, cz.quantity, cz.uom_code
,item.bom_item_type, item.service_item_flag
,item.pick_components_flag, item.inventory_item_id
,cz.seq_nbr
FROM CZ_PRICING_STRUCTURES cz
, MTL_SYSTEM_ITEMS item
WHERE cz.item_key_type = cz_prc_callback_util.g_item_key_bom_node
AND cz.configurator_session_key = p_config_session_key
AND (cz.list_price is null
OR cz.selling_price is null)
AND item.inventory_item_id = to_number(SUBSTR((SUBSTR( cz.item_key,
1, INSTR(cz.item_key, ':') - 1)),
INSTR(( SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)),
'-', -1) + 1 ))
AND organization_id = OE_Sys_Parameters.Value('MASTER_ORGANIZATION_ID');
UPDATE CZ_PRICING_STRUCTURES cz
SET cz.list_price =
( SELECT ol.unit_list_price
FROM OE_ORDER_LINES ol
WHERE ol.top_model_line_id = l_top_model_line_id
AND ol.component_code =
SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)
--commented this check as for the first time when pricing call back is done the configuration_id
--is not present in OE_ORDER_LINES table hence the query fails and price for the model is not
--displayed.
--AND ol.configuration_id = cz.config_item_id--bug#7595079
)
WHERE cz.configurator_session_key = p_config_session_key;
UPDATE CZ_PRICING_STRUCTURES cz
SET cz.selling_price =
( SELECT ol.unit_selling_price
FROM OE_ORDER_LINES ol
WHERE ol.top_model_line_id = l_top_model_line_id
AND ol.ordered_quantity = cz.quantity -- only if no change in qty
AND ol.component_code =
SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)
--commented this check as for the first time when pricing call back is done the configuration_id
--is not present in OE_ORDER_LINES table hence the query fails and price for the model is not
--displayed.
--AND ol.configuration_id = cz.config_item_id--bug#7595079
)
WHERE cz.configurator_session_key = p_config_session_key;
UPDATE CZ_PRICING_STRUCTURES cz
SET cz.list_price =
( SELECT ol.unit_list_price
FROM OE_ORDER_LINES ol
WHERE ol.top_model_line_id = l_top_model_line_id
AND ol.component_code =
SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)
)
WHERE cz.configurator_session_key = p_config_session_key;
UPDATE CZ_PRICING_STRUCTURES cz
SET cz.selling_price =
( SELECT ol.unit_selling_price
FROM OE_ORDER_LINES ol
WHERE ol.top_model_line_id = l_top_model_line_id
AND ol.ordered_quantity = cz.quantity -- only if no change in qty
AND ol.component_code =
SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1))
WHERE cz.configurator_session_key = p_config_session_key;
IF l_index = 0 THEN -- everything updated
IF l_debug_level > 0 THEN
oe_debug_pub.add('RESTORED CONFIG , UPDATE PRICING BUTTOM PRESSED', 1);
SELECT sum(selling_price * quantity)
INTO l_total_price
FROM cz_pricing_structures
WHERE configurator_session_key = p_config_session_key;
UPDATE CZ_PRICING_STRUCTURES
SET
LIST_PRICE = l_line_tbl(l_index).unit_list_price,
SELLING_PRICE = l_line_Tbl(l_index).unit_selling_price
WHERE SUBSTR( item_key, 1, INSTR(item_key, ':') - 1)
= l_line_tbl(l_index).component_code
AND seq_nbr = -1 - l_line_tbl(l_index).line_id --Bug#2832208
AND configurator_session_key = p_config_session_key
AND item_key_type = cz_prc_callback_util.g_item_key_bom_node;
SELECT sum(selling_price * quantity)
INTO l_total_price
FROM cz_pricing_structures
WHERE configurator_session_key = p_config_session_key;
oe_debug_pub.add('TIME AFTER UPDATE RESTORED : ' ||L_TIME_UPD , 1);
UPDATE CZ_PRICING_STRUCTURES
SET MSG_DATA = l_config_pricing_error
WHERE configurator_session_key = p_config_session_key
AND item_key = l_model_item_key;