1 PACKAGE BODY OE_CONFIG_PRICE_UTIL AS
2 /* $Header: OEXUCFPB.pls 120.0.12010000.2 2008/11/25 11:59:14 amimukhe ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30):='Oe_Config_Price_Util';
6
7
8 /*----------------------------------------------------------------------
9 PROCEDURE OE_Config_Price_Items:
10
11 get selected options from cz_pricing_structures table.
12 For list price, CZ sends to this API qty per unit model.
13 For selling price, Configurator sends to this API the actual
14 ordered quantity.
15
16 Configurator send to us the config_session_key. This key is what we
17 passed to them initially when the configurator form was opened.
18 This is of the form top_model_line_id#session_id. We use this key
19 to get the top_model_line_id. It is also the primary key for the CZ
20 table from which we get the options to price.
21
22 For each option sent in we do the following:
23 1. Get inventory_item_id from the item_key.
24 item_key in cz_pricing_structures table is concatenation of
25 component_code, explosion_type, organization_id, top_item_id.
26 These values are separated by ':'. We will parse the item_key
27 to get inventory_item_id(from the component code)
28 The order of concatenation is imp, if Configurator changes it,
29 there will be bugs.
30 2. Create the line records (for new options).
31
32 For a restored configuration, we use the unit_list_price, and
33 unit_selling_price from order management tables directly.
34
35 Call price_lines API for all the new options. Update the CZ pricing
36 structures table with list price/selling price obtained from the
37 pricing API.
38
39 Change Record:
40 bug fix: if in an existing configuration. qty of options modified,
41 selling price will be affected (list price we always get per unit.)
42 do not update using old pricing data from oe_order_lines.
43 oe_debug_pub.add('updating selling prices of already saved options', 1);
44
45 bug 2211600 : operationis set to CREATE on the line records.
46
47 Recurring Charges: The call to OE_DEFAULT_LINE.Attributes for every child line
48 of network model will mean that it affects performance of pricing callback as
49 compare d to a regualr PTO or ATO model.
50 --------------------------------------------------------------------------*/
51
52 PROCEDURE OE_Config_Price_Items
53 ( p_config_session_key IN VARCHAR2
54 ,p_price_type IN VARCHAR2 -- list, selling
55 ,x_total_price OUT NOCOPY NUMBER
56
57 )
58 IS
59
60 l_header_id NUMBER;
61 l_top_model_line_id NUMBER;
62 l_index NUMBER;
63 l_seq_nbr NUMBER;
64 l_item_key VARCHAR2(2000);
65 l_price_control_rec QP_PREQ_GRP.Control_record_type;
66 l_total_price NUMBER;
67 l_model_item_key VARCHAR2(2000);
68 l_config_pricing_error VARCHAR2(2000);
69 l_return_status VARCHAR2(1);
70 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
71 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
72 l_top_line_id_pos NUMBER;
73 l_model_line_rec OE_Order_Pub.Line_Rec_Type;
74 l_defaulted_flag VARCHAR2(1);
75 l_old_line_rec OE_Order_Pub.Line_Rec_Type;
76 l_pricing_callback_off VARCHAR2(1);
77
78 l_time_start VARCHAR2(100);
79 l_time_upd VARCHAR2(100);
80 l_time_po VARCHAR2(100);
81 l_time_read VARCHAR2(100);
82 l_time_bef_price VARCHAR2(100);
83 l_time_price VARCHAR2(100);
84 l_time_end VARCHAR2(100);
85
86 l_bom_item_type NUMBER;
87 l_pick_components_flag VARCHAR2(1);
88 l_service_item_flag VARCHAR2(1);
89 l_top_container_model VARCHAR2(1);
90 l_part_of_container VARCHAR2(1);
91
92 CURSOR options_to_be_priced IS
93 SELECT cz.item_key, cz.quantity, cz.uom_code
94 ,item.bom_item_type, item.service_item_flag
95 ,item.pick_components_flag, item.inventory_item_id
96 ,cz.seq_nbr
97 FROM CZ_PRICING_STRUCTURES cz
98 , MTL_SYSTEM_ITEMS item
99 WHERE cz.item_key_type = cz_prc_callback_util.g_item_key_bom_node
100 AND cz.configurator_session_key = p_config_session_key
101 AND (cz.list_price is null
102 OR cz.selling_price is null)
103 AND item.inventory_item_id = to_number(SUBSTR((SUBSTR( cz.item_key,
104 1, INSTR(cz.item_key, ':') - 1)),
105 INSTR(( SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)),
106 '-', -1) + 1 ))
107 AND organization_id = OE_Sys_Parameters.Value('MASTER_ORGANIZATION_ID');
108
109 --
110 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
111 --
112 BEGIN
113
114 l_pricing_callback_off := FND_PROFILE.VALUE('ONT_BYPASS_PRICING_CALLBACK');
115
116 IF nvl(upper(l_pricing_callback_off), 'N') = 'Y' THEN
117 IF l_debug_level > 0 THEN
118 oe_debug_pub.add('CONFIGURATOR PRICING CALLBACK TURNED OFF', 1);
119 END IF;
120 x_total_price := 0;
121 RETURN;
122 ELSE
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add('CONFIGURATOR PRICING CALLBACK IS ON', 1);
125 END IF;
126 END IF;
127
128 l_time_start := to_char (new_time (sysdate, 'PST', 'EST'),
129 'DD-MON-YY HH24:MI:SS');
130
131
132 ------------------- parse session_key ----------------------
133
134 IF l_debug_level > 0 THEN
135 oe_debug_pub.add('ENTERING OE_CONFIG_PRICE_ITEMS', 1);
136 END IF;
137
138 -- ex => '120000#10000', extract top_model_line_id from session_key
139
140 IF l_debug_level > 0 THEN
141 oe_debug_pub.add('CONFIG_SESSION_KEY: '|| P_CONFIG_SESSION_KEY , 1);
142 END IF;
143
144 l_top_line_id_pos :=
145 INSTR(p_config_session_key, '#' );
146 IF l_debug_level > 0 THEN
147 oe_debug_pub.add('MODEL END POS: '|| L_TOP_LINE_ID_POS , 1);
148 END IF;
149
150
151 l_top_model_line_id := TO_NUMBER(SUBSTR(p_config_session_key, 1,
152 l_top_line_id_pos - 1 ));
153
154 IF l_debug_level > 0 THEN
155 oe_debug_pub.add('L_TOP_MODEL_LINE_ID: ' || L_TOP_MODEL_LINE_ID , 1);
156 END IF;
157
158
159 --------------------- restored configuration ---------------
160
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add('UPDATING LIST PRICES ALREADY SAVED OPTIONS', 1);
163 END IF;
164
165 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' THEN
166 IF l_debug_level > 0 THEN
167 oe_debug_pub.add('PRICING CALLBACK: PACK H NEW LOGIC MI', 1);
168 END IF;
169
170 UPDATE CZ_PRICING_STRUCTURES cz
171 SET cz.list_price =
172 ( SELECT ol.unit_list_price
173 FROM OE_ORDER_LINES ol
174 WHERE ol.top_model_line_id = l_top_model_line_id
175 AND ol.component_code =
176 SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)
177 --commented this check as for the first time when pricing call back is done the configuration_id
178 --is not present in OE_ORDER_LINES table hence the query fails and price for the model is not
179 --displayed.
180 --AND ol.configuration_id = cz.config_item_id--bug#7595079
181 )
182 WHERE cz.configurator_session_key = p_config_session_key;
183
184 UPDATE CZ_PRICING_STRUCTURES cz
185 SET cz.selling_price =
186 ( SELECT ol.unit_selling_price
187 FROM OE_ORDER_LINES ol
188 WHERE ol.top_model_line_id = l_top_model_line_id
189 AND ol.ordered_quantity = cz.quantity -- only if no change in qty
190 AND ol.component_code =
191 SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)
192 --commented this check as for the first time when pricing call back is done the configuration_id
193 --is not present in OE_ORDER_LINES table hence the query fails and price for the model is not
194 --displayed.
195 --AND ol.configuration_id = cz.config_item_id--bug#7595079
196 )
197 WHERE cz.configurator_session_key = p_config_session_key;
198
199 ELSE
200
201 UPDATE CZ_PRICING_STRUCTURES cz
202 SET cz.list_price =
203 ( SELECT ol.unit_list_price
204 FROM OE_ORDER_LINES ol
205 WHERE ol.top_model_line_id = l_top_model_line_id
206 AND ol.component_code =
207 SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1)
208 )
209 WHERE cz.configurator_session_key = p_config_session_key;
210
211 UPDATE CZ_PRICING_STRUCTURES cz
212 SET cz.selling_price =
213 ( SELECT ol.unit_selling_price
214 FROM OE_ORDER_LINES ol
215 WHERE ol.top_model_line_id = l_top_model_line_id
216 AND ol.ordered_quantity = cz.quantity -- only if no change in qty
217 AND ol.component_code =
218 SUBSTR( cz.item_key, 1, INSTR(cz.item_key, ':') - 1))
219 WHERE cz.configurator_session_key = p_config_session_key;
220 END IF;
221
222 l_time_upd := to_char
223 (new_time
224 (sysdate, 'PST', 'EST'),
225 'DD-MON-YY HH24:MI:SS');
226
227
228 ----------------- read cz table ---------------------------
229
230 -- if ato model continue with model line.
231 -- pto, default 1 and continue
232
233 OE_Line_Util.Query_Row(p_line_id => l_top_model_line_id,
234 x_line_rec => l_model_line_rec);
235
236
237 IF l_model_line_rec.ato_line_id = l_top_model_line_id THEN
238 IF l_debug_level > 0 THEN
239 oe_debug_pub.add('THIS IS AN ATO MODEL', 1);
240 END IF;
241 l_line_rec := l_model_line_rec;
242 l_defaulted_flag := 'Y';
243 ELSE
244 IF l_debug_level > 0 THEN
245 oe_debug_pub.add('THIS IS A PTO MODEL', 1);
246 END IF;
247 l_line_rec := OE_Order_Pub.G_Miss_Line_Rec;
248 l_defaulted_flag := 'N';
249 END IF;
250
251 IF l_debug_level > 0 THEN
252 oe_debug_pub.add('DEFAULTED_FLAG: '|| L_DEFAULTED_FLAG , 1);
253 END IF;
254
255
256 l_index := 0;
257 l_line_rec.header_id := l_model_line_rec.header_id;
258 l_line_rec.top_model_line_id := l_top_model_line_id;
259 l_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
260
261 --recurring charges
262 IF OE_SYS_PARAMETERS.Value ('RECURRING_CHARGES') = 'Y' THEN
263 IF l_debug_level > 0 THEN
264 OE_DEBUG_PUB.Add ('Recurring Charges System Param ENABLED',1);
265 OE_DEBUG_PUB.Add('Top Model Line ID:'||l_top_model_line_id,3);
266 END IF;
267 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
268 ( p_top_model_line_id => l_top_model_line_id
269 ,x_top_container_model => l_top_container_model
270 ,x_part_of_container => l_part_of_container );
271 END IF;
272
273 OPEN options_to_be_priced;
274 LOOP
275
276 FETCH options_to_be_priced into
277 l_item_key, l_line_rec.ordered_quantity,
278 l_line_rec.order_quantity_uom, l_bom_item_type,
279 l_service_item_flag, l_pick_components_flag,
280 l_line_rec.inventory_item_id, l_seq_nbr;
281
282 EXIT WHEN options_to_be_priced%NOTFOUND;
283
284 IF l_line_rec.inventory_item_id <> l_model_line_rec.inventory_item_id THEN
285 l_line_rec.line_id := -1 - l_seq_nbr; --Bug#2832208
286 l_line_rec.component_code
287 := SUBSTR( l_item_key, 1, INSTR(l_item_key, ':') - 1);
288
289 IF l_defaulted_flag = 'Y' AND
290 l_part_of_container = 'N' THEN --recurring charges
291
292 IF l_bom_item_type = 2 THEN
293 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_CLASS;
294 ELSIF l_bom_item_type = 4 and l_service_item_flag = 'Y' THEN
295 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_SERVICE;
296 ELSIF l_bom_item_type = 4 and l_pick_components_flag = 'Y' THEN
297 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_KIT;
298 ELSE
299 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_OPTION;
300 END IF;
301 IF l_debug_level > 0 THEN
302 oe_debug_pub.add(L_SEQ_NBR||' HDR : '||L_LINE_REC.HEADER_ID,1);
303 oe_debug_pub.add('QTY: ' || L_LINE_REC.ORDERED_QUANTITY,1);
304 oe_debug_pub.add('UCFPB,ITEM_TYPE: '||L_LINE_REC.ITEM_TYPE_CODE,1);
305 END IF;
306 ELSE -- not defaulted, pto model, default 1st option
307 OE_Default_Line.Attributes
308 ( p_x_line_rec => l_line_rec
309 ,p_old_line_rec => l_old_line_rec );
310 l_defaulted_flag := 'Y';
311 END IF;
312
313 l_index := l_index + 1;
314 l_line_tbl(l_index) := l_line_rec;
315 IF l_debug_level > 0 THEN
316 oe_debug_pub.add('LINE INDEX: '|| L_INDEX , 1);
317 oe_debug_pub.add('COMP: '||L_LINE_TBL(L_INDEX).COMPONENT_CODE , 1);
318 oe_debug_pub.add('ITEM: '||L_LINE_TBL(L_INDEX).INVENTORY_ITEM_ID,1);
319 END IF;
320 ELSE
321 l_model_item_key := l_item_key;
322 IF l_debug_level > 0 THEN
323 oe_debug_pub.add('THIS IS A MODEL LINE', 1);
324 END IF;
325 l_index := l_index + 1;
326 l_line_tbl(l_index + 1) := l_model_line_rec;
327 END IF; -- inventory item id match
328
329
330 END LOOP;
331
332 CLOSE options_to_be_priced;
333
334 ----------------------- line rec done ---------------------
335
336
337 l_time_read := to_char
338 (new_time
339 (sysdate, 'PST', 'EST'),
340 'DD-MON-YY HH24:MI:SS');
341
342 IF l_index = 0 THEN -- everything updated
343 IF l_debug_level > 0 THEN
344 oe_debug_pub.add('RESTORED CONFIG , UPDATE PRICING BUTTOM PRESSED', 1);
345 END IF;
346 l_total_price := 0;
347 BEGIN
348 SELECT sum(selling_price * quantity)
349 INTO l_total_price
350 FROM cz_pricing_structures
351 WHERE configurator_session_key = p_config_session_key;
352 EXCEPTION
353 WHEN OTHERS THEN
354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
355 END;
356
357 x_total_price := l_total_price;
358
359 RETURN;
360 END IF;
361
362
363 l_time_po := to_char
364 (new_time
365 (sysdate, 'PST', 'EST'),
366 'DD-MON-YY HH24:MI:SS');
367
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add('DONE WITH PO CALL', 1);
370 END IF;
371
372 ---------------------------- price_line --------------------
373
374 -- event PRICE to get only list price.
375 -- event LINE to get selling and list.
376
377 IF p_price_type = CZ_Prc_Callback_Util.G_PRC_TYPE_LIST THEN
378 l_price_control_rec.pricing_event := 'PRICE';
379 ELSE
380 l_price_control_rec.pricing_event := 'LINE';
381 END IF;
382
383 l_price_control_rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
384 l_price_control_rec.simulation_flag := 'Y';
385
386 IF l_debug_level > 0 THEN
387 oe_debug_pub.add('IN CONFIG_PRICE_ITEMS , BEFORE CALL TO PRICE LINE', 1);
388 END IF;
389
390 l_time_bef_price := to_char
391 (new_time
392 (sysdate, 'PST', 'EST'),
393 'DD-MON-YY HH24:MI:SS');
394
395
396 OE_Order_Adj_Pvt.Price_Line
397 ( p_request_type_code => 'ONT'
398 ,p_control_rec => l_price_control_Rec
399 ,p_write_to_Db => FALSE
400 ,x_line_tbl => l_line_tbl -- IN/OUT
401 ,x_return_Status => l_return_status);
402
403
404 l_time_price := to_char
405 (new_time
406 (sysdate, 'PST', 'EST'),
407 'DD-MON-YY HH24:MI:SS');
408
409 IF l_debug_level > 0 THEN
410 oe_debug_pub.add('AFTER CALL TO PRICE LINE: '|| L_RETURN_STATUS , 1);
411 END IF;
412
413 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418
419 IF l_debug_level > 0 THEN
420 oe_debug_pub.add('AFTER PRICING , TABLE COUNT: '||L_LINE_TBL.COUNT, 1);
421 END IF;
422
423
424
425
426 -------------------------- update cz tables ----------------
427
428 -- update cz_pricing_structures with pricing information.
429
430 l_index := l_line_Tbl.FIRST;
431
432 While l_index is not null
433 LOOP
434
435 IF l_debug_level > 0 THEN
436 oe_debug_pub.add('INDEX: '|| L_INDEX || ' '|| L_LINE_TBL ( L_INDEX ).INVENTORY_ITEM_ID );
437 END IF;
438
439 -- avoid processing for free items
440 IF l_line_tbl(l_index).top_model_line_id = l_top_model_line_id
441 THEN
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add('GG0', 1);
444 END IF;
445 l_item_key := l_line_tbl(l_index).inventory_item_id;
446
447 IF l_debug_level > 0 THEN
448 oe_debug_pub.add('GG01', 1);
449 END IF;
450
451 -- using msg_data field temporarily to save debug file name.
452
453 UPDATE CZ_PRICING_STRUCTURES
454 SET
455 LIST_PRICE = l_line_tbl(l_index).unit_list_price,
456 SELLING_PRICE = l_line_Tbl(l_index).unit_selling_price
457 WHERE SUBSTR( item_key, 1, INSTR(item_key, ':') - 1)
458 = l_line_tbl(l_index).component_code
459 AND seq_nbr = -1 - l_line_tbl(l_index).line_id --Bug#2832208
460 AND configurator_session_key = p_config_session_key
461 AND item_key_type = cz_prc_callback_util.g_item_key_bom_node;
462
463
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add('ITEM: '||L_LINE_TBL ( L_INDEX ).COMPONENT_CODE , 1);
466 oe_debug_pub.add
467 ('THE LIST PRICE IS '||L_LINE_TBL(L_INDEX ).UNIT_LIST_PRICE , 1);
468 oe_debug_pub.add
469 ('THE SELL PRICE IS '||L_LINE_TBL(L_INDEX ).UNIT_SELLING_PRICE , 1);
470 END IF;
471
472 END IF;
473
474 l_index := l_line_Tbl.NEXT(l_index);
475
476 END LOOP;
477
478 l_total_price := 0;
479 BEGIN
480 SELECT sum(selling_price * quantity)
481 INTO l_total_price
482 FROM cz_pricing_structures
483 WHERE configurator_session_key = p_config_session_key;
484 EXCEPTION
485 WHEN OTHERS THEN
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487 END;
488
489 x_total_price := l_total_price;
490
491 IF l_debug_level > 0 THEN
492 oe_debug_pub.add('TOTAL_PRICE: '|| L_TOTAL_PRICE , 1);
493 END IF;
494
495 l_time_end := to_char
496 (new_time
497 (sysdate, 'PST', 'EST'),
498 'DD-MON-YY HH24:MI:SS');
499
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add(' ' );
502 oe_debug_pub.add('TIME AT START OF CALLBACK : ' ||L_TIME_START , 1);
503 oe_debug_pub.add('TIME AFTER UPDATE RESTORED : ' ||L_TIME_UPD , 1);
504 oe_debug_pub.add('TIME AFTER CZ READ : ' ||L_TIME_READ , 1);
505 oe_debug_pub.add('TIME AFTER PROCESS_ORDER : ' ||L_TIME_PO , 1);
506 oe_debug_pub.add('TIME BEFORE PRICING API : ' ||L_TIME_BEF_PRICE );
507 oe_debug_pub.add('TIME AFTER PRICING API : ' ||L_TIME_PRICE );
508 oe_debug_pub.add('TIME AT END OF CALLBACK : ' ||L_TIME_END , 1);
509 oe_debug_pub.add('LEAVING OE_CONFIG_PRICE_ITEMS', 1);
510 END IF;
511
512 EXCEPTION
513
514 WHEN OTHERS THEN
515
516 l_config_pricing_error := OE_Msg_Pub.Get();
517 --l_config_pricing_error := 'raising error for testing purpose';
518
519 -- set the error message in the model line field.
520 UPDATE CZ_PRICING_STRUCTURES
521 SET MSG_DATA = l_config_pricing_error
522 WHERE configurator_session_key = p_config_session_key
523 AND item_key = l_model_item_key;
524
525
526 IF l_debug_level > 0 THEN
527 oe_debug_pub.add('TIME AT START OF CALLBACK : ' ||L_TIME_START , 1);
528 oe_debug_pub.add('TIME AFTER READ FROM CZ : ' ||L_TIME_READ , 1);
529 oe_debug_pub.add('TIME AFTER PROCESS_ORDER : ' ||L_TIME_PO , 1);
530 oe_debug_pub.add('TIME AFTER PRICING API : ' ||L_TIME_PRICE );
531 oe_debug_pub.add('TIME AT END OF CALLBACK : ' ||L_TIME_END , 1);
532 oe_debug_pub.add('OE_CONFIG_PRICE_ITEMS: ' ||SUBSTR (SQLERRM ,1 ,100),1);
533 oe_debug_pub.add('OTHERS EXCEPTION IN OE_CONFIG_PRICE_ITEMS', 1);
534 END IF;
535
536 END OE_Config_Price_Items;
537
538
539 END OE_CONFIG_PRICE_UTIL;
[Home] [Help]