[Home] [Help]
PACKAGE BODY: APPS.QP_PROCESS_OTHER_BENEFITS_PVT
Source
1 PACKAGE BODY QP_Process_Other_Benefits_PVT AS
2 /* $Header: QPXVOTHB.pls 120.5.12010000.2 2008/08/18 06:56:55 smuhamme ship $ */
3 l_debug VARCHAR2(3);
4 PROCEDURE Calculate_Recurring_Quantity(p_list_line_id NUMBER,
5 p_list_header_id NUMBER,
6 p_line_index NUMBER,
7 p_benefit_line_id NUMBER,
8 x_benefit_line_qty OUT NOCOPY NUMBER,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_return_status_txt OUT NOCOPY VARCHAR2) IS
11 CURSOR determine_context_cur IS
12 SELECT CONTEXT , ATTRIBUTE
13 FROM qp_npreq_line_attrs_tmp
14 WHERE LINE_INDEX = p_line_index
15 AND ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
16 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
17 AND LIST_HEADER_ID = p_list_header_id
18 AND LIST_LINE_ID = p_list_line_id
19 AND CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT;
20
21 /*CURSOR get_total_qty_cur(p_pricing_attribute VARCHAR2) IS
22 SELECT a.VALUE_FROM,a.GROUP_QUANTITY,a.GROUP_AMOUNT
23 FROM qp_npreq_line_attrs_tmp a
24 WHERE a.LINE_INDEX = p_line_index
25 AND a.CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT
26 AND a.ATTRIBUTE = p_pricing_attribute
27 AND a.ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
28 AND a.PRICING_STATUS_CODE in (qp_preq_grp.G_STATUS_UNCHANGED,qp_preq_grp.G_STATUS_NEW);*/
29
30 CURSOR get_total_qty_cur IS
31 SELECT LINE_QUANTITY,GROUP_QUANTITY,GROUP_AMOUNT
32 FROM qp_npreq_ldets_tmp
33 WHERE CREATED_FROM_LIST_LINE_ID = p_list_line_id
34 AND LINE_INDEX = p_line_index
35 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
36
37 CURSOR get_base_qty_cur(p_pricing_attribute VARCHAR2) IS
38 SELECT a.SETUP_VALUE_FROM , b.CREATED_FROM_LIST_LINE_TYPE,b.OPERAND_VALUE,b.BENEFIT_QTY
39 FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
40 WHERE a.LINE_INDEX = p_line_index
41 AND a.LINE_INDEX = b.LINE_INDEX
42 AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
43 AND a.CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT
44 AND a.ATTRIBUTE = p_pricing_attribute
45 AND a.ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
46 AND a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
47 AND b.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
48 AND a.LIST_LINE_ID = p_list_line_id
49 AND a.LIST_HEADER_ID = p_list_header_id
50 AND b.created_from_list_line_id = p_list_line_id -- sql repos
51 AND b.created_from_list_header_id = p_list_header_id; -- sql repos
52
53 CURSOR get_list_line_attrs_cur(p_line_id NUMBER) IS
54 SELECT LIST_LINE_TYPE_CODE,OPERAND,BENEFIT_QTY
55 FROM QP_LIST_LINES
56 WHERE LIST_LINE_ID = p_line_id;
57
58 CURSOR get_modifier_level_code_cur IS
59 SELECT MODIFIER_LEVEL_CODE
60 FROM QP_LIST_LINES
61 WHERE LIST_LINE_ID = p_list_line_id;
62
63 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Process_Other_Benefits_PVT.Calculate_Recurring_Quantity';
64
65 v_total_base_qty NUMBER;
66 v_group_qty NUMBER;
67 v_group_amount NUMBER;
68 v_qualifying_qty NUMBER;
69 v_qualifying_attribute VARCHAR2(30);
70 v_modifier_level_code VARCHAR2(30);
71 v_base_qty NUMBER;
72 v_list_line_type_code VARCHAR2(30);
73 v_list_line_type VARCHAR2(30);
74 v_arithmetic_operator VARCHAR2(30);
75 v_operand NUMBER;
76 v_benefit_qty NUMBER;
77 v_buy_base_qty NUMBER;
78 v_pricing_attr_context VARCHAR2(30);
79 v_pricing_attribute VARCHAR2(30);
80 v_return_status VARCHAR2(30);
81 l_benefit_qty NUMBER;
82 v_total_benefit_qty NUMBER;
83
84 INVALID_CONTEXT EXCEPTION;
85
86 BEGIN
87 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89 IF l_debug = FND_API.G_TRUE THEN
90 qp_preq_grp.engine_debug ('List Line Id: ' || p_list_line_id);
91 END IF;
92
93 OPEN get_modifier_level_code_cur;
94 FETCH get_modifier_level_code_cur INTO v_modifier_level_code;
95 CLOSE get_modifier_level_code_cur;
96
97 OPEN determine_context_cur;
98 FETCH determine_context_cur INTO v_pricing_attr_context , v_pricing_attribute ;
99 CLOSE determine_context_cur;
100
101 v_qualifying_attribute := v_pricing_attribute;
102
103 IF (v_pricing_attr_context = qp_preq_grp.G_PRIC_VOLUME_CONTEXT) THEN
104 -- shu 2118147, need to fix this, v_group_amount is amt_per_unit
105 OPEN get_total_qty_cur;
106 FETCH get_total_qty_cur INTO v_total_base_qty,v_group_qty,v_group_amount ;
107 CLOSE get_total_qty_cur;
108 ELSE
109 IF l_debug = FND_API.G_TRUE THEN
110 qp_preq_grp.engine_debug('context : ' || v_pricing_attr_context);
111 END IF;
112 RAISE INVALID_CONTEXT;
113 END IF;
114
115 IF l_debug = FND_API.G_TRUE THEN
116 qp_preq_grp.engine_debug('v_total_base_qty: ' || v_total_base_qty);
117 qp_preq_grp.engine_debug('v_group_qty: ' ||v_group_qty);
118 END IF;
119
120 OPEN get_base_qty_cur(v_pricing_attribute);
121 FETCH get_base_qty_cur INTO v_base_qty ,v_list_line_type_code,v_operand,v_benefit_qty ;
122 CLOSE get_base_qty_cur;
123
124 IF l_debug = FND_API.G_TRUE THEN
125 qp_preq_grp.engine_debug('Total Setup Qty: ' || v_base_qty);
126 qp_preq_grp.engine_debug('Operand: ' || v_operand);
127 qp_preq_grp.engine_debug('List Line Type Code: ' || v_list_line_type_code);
128 END IF;
129
130 IF (p_benefit_line_id IS NOT NULL) THEN
131 OPEN get_list_line_attrs_cur(p_benefit_line_id);
132 FETCH get_list_line_attrs_cur INTO v_list_line_type,v_operand,v_benefit_qty;
133 CLOSE get_list_line_attrs_cur;
134 END IF;
135
136 IF(v_list_line_type_code in(qp_preq_grp.G_DISCOUNT,qp_preq_grp.G_SURCHARGE)) THEN
137 l_benefit_Qty := v_operand;
138 ELSIF(v_list_line_type_code = qp_preq_grp.G_PROMO_GOODS_DISCOUNT) THEN
139 l_benefit_qty := v_benefit_qty;
140 ELSIF(v_list_line_type_code = qp_preq_grp.G_COUPON_ISSUE) THEN
141 l_benefit_qty := 1; -- Need not look at the benefit part
142 END IF;
143
144 -- Determine Qualifying Quantity
145 IF (v_modifier_level_code IN ('LINE','ORDER')) THEN
146 v_qualifying_qty := v_total_base_qty; --from ldets_tmp.line_quantity
147 ELSE
148 -- If LINEGROUP
149 IF (v_qualifying_attribute = qp_preq_grp.G_QUANTITY_ATTRIBUTE) THEN
150 v_qualifying_qty := v_group_qty; --from ldets_tmp.group_quantity
151 ELSE
152 --v_qualifying_qty := v_group_amount; -- shu 2388011, this is wrong, should be from ldets_tmp.line_quantity
153 v_qualifying_qty := v_total_base_qty; -- shu 2388011, --from ldets_tmp.line_quantity
154 END IF;
155 END IF;
156 IF l_debug = FND_API.G_TRUE THEN
157 qp_preq_grp.engine_debug('Modifier Level Code: '|| v_modifier_level_code);
158 qp_preq_grp.engine_debug('Qualifying Attribute : ' || v_qualifying_attribute);
159 qp_preq_grp.engine_debug('v_qualifying_qty: ' || v_qualifying_qty);
160 qp_preq_grp.engine_debug('v_base_qty: ' || v_base_qty);
161 qp_preq_grp.engine_debug('l_benefit_qty: ' || l_benefit_qty);
162 END IF;
163 v_total_benefit_qty := TRUNC((v_qualifying_qty / v_base_qty)) * l_benefit_qty;
164 IF l_debug = FND_API.G_TRUE THEN
165 qp_preq_grp.engine_debug('Total Benefit Qty: ' || v_total_benefit_qty);
166 END IF;
167 x_benefit_line_qty := v_total_benefit_qty;
168
169 EXCEPTION
170 WHEN INVALID_CONTEXT THEN
171 IF l_debug = FND_API.G_TRUE THEN
172 qp_preq_grp.engine_debug(v_routine_name || ' Invalid Context Specified.. Could Not Calculate Recurring Qty');
173 END IF;
174 x_return_status_txt := v_routine_name ||' Invalid Context Specified ..Could Not Calculate Recurring Qty';
175 v_return_status := FND_API.G_RET_STS_ERROR;
176 x_return_status := v_return_status;
177 IF l_debug = FND_API.G_TRUE THEN
178 qp_preq_grp.engine_debug(x_return_status_txt);
179 END IF;
180 WHEN OTHERS THEN
181 IF l_debug = FND_API.G_TRUE THEN
182 qp_preq_grp.engine_debug(v_routine_name || ' ' || SQLERRM);
183 END IF;
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
186 END Calculate_Recurring_Quantity;
187
188 PROCEDURE Calculate_Recurring_Quantity(p_pricing_phase_id NUMBER,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_return_status_txt OUT NOCOPY VARCHAR2) IS
191 v_routine_name CONSTANT VARCHAR2(240):='QP_Process_Other_Benefits_PVT.Calculate_Recurring_Quantity';
192
193 CURSOR get_recurring_details_cur IS
194 SELECT QPLT.LINE_DETAIL_INDEX,
195 QPLT.CREATED_FROM_LIST_LINE_ID,
196 QPLT.CREATED_FROM_LIST_HEADER_ID,
197 QPLT.LINE_INDEX
198 FROM qp_npreq_ldets_tmp QPLT
199 WHERE PRICING_PHASE_ID = p_pricing_phase_id
200 AND QPLT.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES -- 5632314
201 AND QPLT.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
202 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT, QP_PREQ_GRP.G_SURCHARGE, QP_PREQ_GRP.G_FREIGHT_CHARGE)
203 AND PRICE_BREAK_TYPE_CODE = QP_PREQ_GRP.G_RECURRING_BREAK;
204
205 l_line_detail_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
206 l_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
207 l_list_header_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
208 l_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
209 l_benefit_line_qty_tbl QP_PREQ_GRP.NUMBER_TYPE;
210 l_return_status VARCHAR2(240);
211 l_status_text VARCHAR2(240);
212
213 BEGIN
214 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
215
216 IF l_debug = FND_API.G_TRUE THEN
217 QP_PREQ_GRP.engine_debug('Calculate_Recurring_Quantity-PHASE='||p_pricing_phase_id);
218 END IF;
219
220 OPEN get_recurring_details_cur;
221 LOOP
222 FETCH get_recurring_details_cur BULK COLLECT INTO
223 l_line_detail_index_tbl,
224 l_list_line_id_tbl,
225 l_list_header_id_tbl,
226 l_line_index_tbl;
227 EXIT WHEN l_line_detail_index_tbl.COUNT = 0;
228
229 FOR I in l_line_detail_index_tbl.first .. l_line_detail_index_tbl.last
230 LOOP
231 IF l_debug = FND_API.G_TRUE THEN
232 QP_PREQ_GRP.engine_debug('In Recurring Routine --- #1');
233 QP_PREQ_GRP.engine_debug('List Header Id: ' || l_LIST_HEADER_ID_TBL(I) );
234 QP_PREQ_GRP.engine_debug('List Line Id: ' || l_LIST_LINE_ID_TBL(I) );
235 QP_PREQ_GRP.engine_debug('List Line Index: ' || l_LINE_INDEX_TBL(I) );
236 END IF;
237
238 -- Call Recurring Routine
239 QP_Process_Other_Benefits_PVT.Calculate_Recurring_Quantity(
240 l_LIST_LINE_ID_TBL(I),
241 l_LIST_HEADER_ID_TBL(I),
242 l_LINE_INDEX_TBL(I),
243 NULL,
244 l_benefit_line_qty_tbl(I),
245 l_return_status,
246 l_status_text);
247
248 IF l_debug = FND_API.G_TRUE THEN
249 QP_PREQ_GRP.engine_debug('Return Status Text : ' || l_status_text);
250 END IF;
251
252 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255 END LOOP;
256
257 FORALL I IN l_line_detail_index_tbl.first..l_line_detail_index_tbl.last
258 UPDATE qp_npreq_ldets_tmp --upd1
259 SET operand_value = l_benefit_line_qty_tbl(I)
260 WHERE LINE_DETAIL_INDEX = l_line_detail_index_tbl(I);
261
262 IF l_debug = FND_API.G_TRUE THEN
263 QP_PREQ_GRP.engine_debug('Updated count: ' || l_line_detail_index_tbl.count);
264 END IF;
265 END LOOP;
266
267 IF l_debug = FND_API.G_TRUE THEN
268 QP_PREQ_GRP.engine_debug('End Calculate_Recurring_Quantity-PHASE='||p_pricing_phase_id);
269 END IF;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF l_debug = FND_API.G_TRUE THEN
274 qp_preq_grp.engine_debug(v_routine_name || ' ' || SQLERRM);
275 END IF;
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
278 END Calculate_Recurring_Quantity;
279
280 PROCEDURE Process_PRG(p_line_index NUMBER,
281 p_line_detail_index NUMBER,
282 p_modifier_level_code VARCHAR2,
283 p_list_line_id NUMBER,
284 p_pricing_phase_id NUMBER,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_return_status_txt OUT NOCOPY VARCHAR2) AS
287
288 -- Get the Related Modifier id from qp_rltd_modifiers table
289 -- Insert a line into the qp_npreq_lines_tmp table for the other item
290 -- Insert a line into qp_npreq_rltd_lines_tmp
291 -- Insert a line into qp_npreq_ldets_tmp table for discount on the new line
292
293 -- Insert a line into qp_npreq_rltd_lines_tmp
294 -- Insert a line into qp_npreq_line_attrs_tmp
295
296
297 CURSOR get_related_modifier_id_cur IS
298 SELECT distinct a.PRICE_BREAK_TYPE_CODE , b.FROM_RLTD_MODIFIER_ID,b.TO_RLTD_MODIFIER_ID,c.LINE_DETAIL_INDEX ,
299 a.PRICING_PHASE_ID , a.AUTOMATIC_FLAG,a.LIST_HEADER_ID
300 FROM QP_LIST_LINES a, QP_RLTD_MODIFIERS b,qp_npreq_ldets_tmp c
301 WHERE a.LIST_LINE_ID = b.FROM_RLTD_MODIFIER_ID
302 AND a.LIST_LINE_ID = c.CREATED_FROM_LIST_LINE_ID
303 AND b.RLTD_MODIFIER_GRP_TYPE = qp_preq_grp.G_BENEFIT_TYPE
304 AND c.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_PROMO_GOODS_DISCOUNT
305 AND c.PRICING_PHASE_ID = p_pricing_phase_id
306 AND c.LINE_INDEX = p_line_index
307 AND a.LIST_LINE_ID = p_list_line_id
308 AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
309
310 CURSOR get_related_lines_cur(p_related_modifier_id NUMBER) IS
311 SELECT a.LIST_TYPE_CODE,b.LIST_HEADER_ID,b.LIST_LINE_ID,b.LIST_LINE_TYPE_CODE,
312 b.PRICING_GROUP_SEQUENCE, b.ARITHMETIC_OPERATOR,b.OPERAND,b.PRICING_PHASE_ID,
313 b.BENEFIT_PRICE_LIST_LINE_ID, b.BENEFIT_UOM_CODE,b.BENEFIT_QTY,b.LIST_PRICE,
314 b.PRICE_BREAK_TYPE_CODE,b.AUTOMATIC_FLAG, c.PRODUCT_ATTRIBUTE_CONTEXT, c.PRODUCT_ATTRIBUTE,
315 c.PRODUCT_ATTR_VALUE,c.PRODUCT_UOM_CODE , b.ACCRUAL_FLAG,b.MODIFIER_LEVEL_CODE
316 FROM QP_LIST_HEADERS_B a ,QP_LIST_LINES b,QP_PRICING_ATTRIBUTES c
317 WHERE a.LIST_HEADER_ID = b.LIST_HEADER_ID
318 AND b.LIST_LINE_ID = c.LIST_LINE_ID
319 AND b.LIST_LINE_ID= p_related_modifier_id;
320
321 CURSOR get_benefit_line_index_cur(p_list_line_id NUMBER, p_line_index NUMBER) IS
322 SELECT CREATED_FROM_LIST_LINE_ID
323 FROM qp_npreq_ldets_tmp c
324 WHERE c.CREATED_FROM_LIST_LINE_ID = p_list_line_id
325 AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
326 AND c.LINE_INDEX = p_line_index ;
327
328
329 CURSOR get_list_price_cur(p_line_id NUMBER) IS
330 SELECT a.LIST_HEADER_ID,a.LIST_LINE_ID,a.OPERAND,b.ROUNDING_FACTOR
331 FROM QP_LIST_LINES a ,QP_LIST_HEADERS_B b
332 WHERE a.LIST_LINE_ID = p_line_id
333 AND a.LIST_HEADER_ID = b.LIST_HEADER_ID
334 AND a.ARITHMETIC_OPERATOR = qp_preq_grp.G_UNIT_PRICE;
335
336 CURSOR get_request_type_code_cur IS
337 SELECT REQUEST_TYPE_CODE
338 FROM qp_npreq_lines_tmp
339 WHERE LINE_INDEX = p_line_index;
340
341 CURSOR get_max_line_index_cur IS
342 SELECT MAX(LINE_INDEX)
343 FROM qp_npreq_lines_tmp;
344
345 --removed rownum=1 spgopal to create relation for multiple buys/gets
346 --for linegroup prg against each qualified line
347 CURSOR get_rltd_line_detail_index IS
348 -- Bug 2979447 - also selected related_list_line_id needed by order capture
349 -- Bug 2998770 - removed LINE_INDEX and also added distinct, code merged from trunk version 115.66
350 -- Bug 3074630 - ignore the relationships passed by calling application for free good line, join to
351 -- qp_npreq_lines_tmp to check the process_status for string FREEGOOD
352 --SELECT RELATED_LINE_DETAIL_INDEX,LINE_INDEX,RELATED_LINE_INDEX
353 SELECT distinct RELATED_LINE_DETAIL_INDEX,RELATED_LINE_INDEX -- fix bug 2998770
354 ,related_list_line_id -- bug 2979447
355 FROM qp_npreq_rltd_lines_tmp a, qp_npreq_lines_tmp b
356 WHERE a.LIST_LINE_ID = p_list_line_id
357 AND b.line_index = a.related_line_index
358 AND (instr(b.PROCESS_STATUS,'FREEGOOD')=0)
359 AND a.RELATIONSHIP_TYPE_CODE = qp_preq_grp.G_GENERATED_LINE
360 AND a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
361 -- AND ROWNUM = 1;
362
363
364 v_line_index NUMBER;
365 v_detail_line_index NUMBER;
366 v_other_item_list_price NUMBER;
367 v_other_item_base_qty NUMBER;
368 v_list_price NUMBER;
369 v_list_header_id NUMBER;
370 v_list_line_id NUMBER;
371 l_list_line_id NUMBER;
372 v_base_qty NUMBER;
373 v_base_uom VARCHAR2(30);
374 v_request_type_code VARCHAR2(30);
375 x_total_benefit_qty NUMBER;
376 x_ret_status VARCHAR2(30);
377 x_ret_status_txt VARCHAR2(240);
378 v_benefit_exists BOOLEAN;
379 v_rounding_factor NUMBER;
380 l_rltd_line_detail_index NUMBER;
381 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Process_Other_Benefits.Process_PRG';
382 BEGIN
383
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385 v_benefit_exists := FALSE;
386
387 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
388
389 l_rltd_line_detail_index := NULL;
390
391 -- Ravi (If it is a LINEGROUP PRG) Then Just create the relationship
392 -- Get the line detail index of the prg line from qp_npreq_ldets_tmp and then get the rltd_line_detail_index
393 -- from qp_npreq_rltd_lines_tmp and create a relationship between the line_detail_index and the rltd_line_detail_index
394 -- If record does not exist go thru the regular prg processing
395
396
397 IF l_debug = FND_API.G_TRUE THEN
398 qp_preq_grp.engine_debug('Modifier Level Code: ' || p_modifier_level_code);
399 END IF;
400
401 IF (p_modifier_level_code = 'LINEGROUP') THEN
402 -- OPEN get_rltd_line_detail_index;
403 -- FETCH get_rltd_line_detail_index INTO l_rltd_line_detail_index;
404 -- CLOSE get_rltd_line_detail_index;
405 -- END IF;--p_modifier_level_code
406 --changes made by spgopal to insert the relationships for each line
407 --qualifying for the linegroup modifier
408
409 FOR j IN get_rltd_line_detail_index
410 LOOP
411 IF l_debug = FND_API.G_TRUE THEN
412 qp_preq_grp.engine_debug('Linegroup Related Line Detail Index: ' || j.related_line_detail_index);
413 END IF;
414 l_rltd_line_detail_index := j.related_line_detail_index;
415
416 INSERT INTO qp_npreq_rltd_lines_tmp
417 (REQUEST_TYPE_CODE
418 ,LINE_DETAIL_INDEX
419 ,RELATIONSHIP_TYPE_CODE
420 ,RELATED_LINE_DETAIL_INDEX
421 ,PRICING_STATUS_CODE
422 ,LIST_LINE_ID
423 ,LINE_INDEX
424 ,RELATED_LINE_INDEX
425 ,related_list_line_id) -- bug 2979447
426 VALUES
427 (v_request_type_code
428 ,p_line_detail_index
429 ,qp_preq_grp.G_GENERATED_LINE
430 ,j.related_line_detail_index
431 ,qp_preq_grp.G_STATUS_NEW
432 ,p_list_line_id
433 ,p_line_index
434 ,j.related_line_index
435 ,j.related_list_line_id); -- bug 2979447
436
437 IF l_debug = FND_API.G_TRUE THEN
438 qp_preq_grp.engine_debug('Only relationship is getting created'
439 ||' for LineGroup discounts');
440 qp_preq_grp.engine_debug('New Related Line Detail-Line Detail Created');
441 END IF;
442 END LOOP;--get_rltd_line_detail_index
443 END IF;--p_modifier_level_code
444 -- ELSE
445
446 IF l_rltd_line_detail_index IS NULL THEN
447
448 IF l_debug = FND_API.G_TRUE THEN
449 qp_preq_grp.engine_debug('Regular PRG processing');
450 END IF;
451
452 OPEN get_max_line_index_cur;
453 FETCH get_max_line_index_cur INTO v_line_index;
454 CLOSE get_max_line_index_cur;
455
456 v_detail_line_index := qp_preq_grp.G_LINE_DETAIL_INDEX;
457
458 FOR j IN get_related_modifier_id_cur
459 LOOP
460 FOR i IN get_related_lines_cur(j.TO_RLTD_MODIFIER_ID)
461 LOOP
462 v_line_index := v_line_index + 1; -- Temporary
463
464 IF (j.PRICE_BREAK_TYPE_CODE = qp_preq_grp.G_RECURRING_BREAK) THEN
465 Calculate_Recurring_Quantity(j.FROM_RLTD_MODIFIER_ID,
466 j.LIST_HEADER_ID,
467 p_line_index,
468 j.TO_RLTD_MODIFIER_ID,
469 x_total_benefit_qty,
470 x_ret_status,
471 x_ret_status_txt);
472 ELSE--j.PRICE_BREAK_TYPE_CODE
473 x_total_benefit_qty := i.BENEFIT_QTY;
474 END IF;--j.PRICE_BREAK_TYPE_CODE
475
476 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480 IF l_debug = FND_API.G_TRUE THEN
481 qp_preq_grp.engine_debug('Total Benefit Qty:' || x_total_benefit_qty);
482 END IF;
483
484 -- Find the Price of the DIS for PRG Line Vivek
485 -- Only LIST_PRICE column on BENEFIT_PRICE_LIST_LINE_ID
486
487 IF l_debug = FND_API.G_TRUE THEN
488 qp_preq_grp.engine_debug('Benefit Price List Line Id:' || i.BENEFIT_PRICE_LIST_LINE_ID);
489 END IF;
490
491 OPEN get_list_price_cur(i.BENEFIT_PRICE_LIST_LINE_ID);
492 FETCH get_list_price_cur
493 INTO v_list_header_id,v_list_line_id,v_list_price,v_rounding_factor;
494 CLOSE get_list_price_cur;
495
496 IF l_debug = FND_API.G_TRUE THEN
497 qp_preq_grp.engine_debug('PRG Line Rounding Factor :' || v_rounding_factor);
498 END IF;
499
500 OPEN get_request_type_code_cur;
501 FETCH get_request_type_code_cur INTO v_request_type_code;
502 CLOSE get_request_type_code_cur;
503
504 IF l_debug = FND_API.G_TRUE THEN
505 qp_preq_grp.engine_debug('List Price of DIS Line:' || v_list_price);
506 END IF;
507
508 OPEN get_benefit_line_index_cur(i.LIST_LINE_ID,v_line_index);
509 FETCH get_benefit_line_index_cur INTO l_list_line_id;
510 CLOSE get_benefit_line_index_cur;
511
512 -- If this adjustment line does not exist against
513 -- any PRG Line Index,then create
514 -- For other PRG Lines , they will be deleted
515
516 IF (l_list_line_id IS NULL) THEN
517 v_benefit_exists := TRUE;
518
519 -- begin shu, side fix bug 2491158,
520 -- insert price_list_header_id for generated line
521 INSERT INTO qp_npreq_lines_tmp
522 (LINE_INDEX
523 , PRICE_LIST_HEADER_ID
524 , LINE_TYPE_CODE
525 , PRICING_EFFECTIVE_DATE
526 , LINE_QUANTITY
527 , LINE_UOM_CODE
528 , CURRENCY_CODE
529 , PRICING_STATUS_CODE
530 , PROCESSED_FLAG
531 , ADJUSTED_UNIT_PRICE
532 , PRICE_FLAG
533 , UNIT_PRICE
534 , REQUEST_TYPE_CODE
535 , PRICED_UOM_CODE
536 , PRICED_QUANTITY
537 , PROCESSED_CODE
538 , ROUNDING_FACTOR
539 , ROUNDING_FLAG
540 --added by spgopal for prg
541 , PROCESS_STATUS
542 --to create returns of prg to create return for freegood spgopal
543 , LINE_CATEGORY)
544 SELECT v_line_index
545 , v_list_header_id
546 , LINE_TYPE_CODE
547 , PRICING_EFFECTIVE_DATE
548 , x_total_benefit_qty
549 , i.BENEFIT_UOM_CODE
550 , CURRENCY_CODE
551 , qp_preq_grp.G_STATUS_UNCHANGED
552 , PROCESSED_FLAG
553 , v_list_price
554 , qp_preq_grp.G_NO
555 , v_list_price
556 , REQUEST_TYPE_CODE
557 , i.BENEFIT_UOM_CODE
558 , x_total_benefit_qty
559 , qp_preq_grp.G_BY_ENGINE
560 , v_rounding_factor
561 , qp_preq_grp.G_YES
562 , qp_preq_grp.G_STATUS_NEW
563 , LINE_CATEGORY
564 FROM qp_npreq_lines_tmp
565 WHERE LINE_INDEX = p_line_index;
566
567 IF l_debug = FND_API.G_TRUE THEN
568 qp_preq_grp.engine_debug('New Line Created');
569 END IF;
570
571 -- Ravi remove line to line relationship ie comment out the insert below
572 /* INSERT INTO qp_npreq_rltd_lines_tmp(REQUEST_TYPE_CODE, LINE_INDEX,RELATIONSHIP_TYPE_CODE,
573 RELATED_LINE_INDEX, PRICING_STATUS_CODE) VALUES
574 (v_request_type_code,p_line_index,qp_preq_grp.G_GENERATED_LINE,v_line_index,
575 qp_preq_grp.G_STATUS_NEW);
576 IF l_debug = FND_API.G_TRUE THEN
577 qp_preq_grp.engine_debug('New Line-Line Created');
578 END IF; */
579
580 v_detail_line_index := v_detail_line_index + 1; -- Temporary
581
582 -- Create a PLL Line
583 INSERT INTO qp_npreq_ldets_tmp
584 (LINE_DETAIL_INDEX
585 ,LINE_DETAIL_TYPE_CODE
586 ,LINE_INDEX
587 ,CREATED_FROM_LIST_HEADER_ID
588 ,CREATED_FROM_LIST_LINE_ID
589 ,CREATED_FROM_LIST_LINE_TYPE
590 ,PRICING_GROUP_SEQUENCE
591 ,OPERAND_CALCULATION_CODE
592 ,OPERAND_VALUE
593 ,PROCESSED_FLAG
594 ,CREATED_FROM_LIST_TYPE_CODE
595 ,PRICING_STATUS_CODE
596 ,LINE_QUANTITY
597 ,ROUNDING_FACTOR
598 ,PROCESS_CODE)
599 VALUES
600 (v_detail_line_index
601 ,qp_preq_grp.G_GENERATED_LINE
602 ,v_line_index
603 ,v_list_header_id
604 ,v_list_line_id
605 ,qp_preq_grp.G_PRICE_LIST_TYPE
606 ,0
607 ,qp_preq_grp.G_UNIT_PRICE
608 ,v_list_price
609 ,qp_preq_grp.G_NO
610 ,qp_preq_grp.G_PRICE_LIST_HEADER
611 ,qp_preq_grp.G_STATUS_NEW
612 ,x_total_benefit_qty
613 ,v_rounding_factor
614 ,qp_preq_grp.G_STATUS_NEW);
615
616 IF l_debug = FND_API.G_TRUE THEN
617 qp_preq_grp.engine_debug('New PLL Line Created');
618 END IF;
619
620 v_detail_line_index := v_detail_line_index + 1; -- Temporary
621
622 -- Create an Adjustment Line
623 INSERT INTO qp_npreq_ldets_tmp
624 (LINE_DETAIL_INDEX
625 ,LINE_DETAIL_TYPE_CODE
626 ,LINE_INDEX
627 ,CREATED_FROM_LIST_HEADER_ID
628 ,CREATED_FROM_LIST_LINE_ID
629 ,CREATED_FROM_LIST_LINE_TYPE
630 ,PRICING_GROUP_SEQUENCE
631 ,OPERAND_CALCULATION_CODE
632 ,PRICING_PHASE_ID
633 ,OPERAND_VALUE
634 ,PROCESSED_FLAG
635 ,AUTOMATIC_FLAG
636 ,APPLIED_FLAG
637 ,ACCRUAL_FLAG
638 ,CREATED_FROM_LIST_TYPE_CODE
639 ,PRICING_STATUS_CODE
640 ,LINE_QUANTITY
641 ,PROCESS_CODE
642 ,MODIFIER_LEVEL_CODE
643 ,CALCULATION_CODE)
644 VALUES
645 (v_detail_line_index
646 ,qp_preq_grp.G_GENERATED_LINE
647 ,v_line_index
648 ,i.LIST_HEADER_ID
649 ,i.LIST_LINE_ID
650 ,i.LIST_LINE_TYPE_CODE
651 ,i.PRICING_GROUP_SEQUENCE
652 ,i.ARITHMETIC_OPERATOR
653 ,j.PRICING_PHASE_ID
654 ,i.OPERAND
655 ,qp_preq_grp.G_NO
656 ,j.AUTOMATIC_FLAG
657 ,qp_preq_grp.G_YES
658 ,nvl(i.ACCRUAL_FLAG ,'N')
659 ,i.LIST_TYPE_CODE
660 ,qp_preq_grp.G_STATUS_NEW
661 ,x_total_benefit_qty
662 ,qp_preq_grp.G_STATUS_NEW
663 ,i.MODIFIER_LEVEL_CODE
664 --fix for bug 2988476
665 ,QP_PREQ_PUB.G_FREEGOOD);
666
667 IF l_debug = FND_API.G_TRUE THEN
668 qp_preq_grp.engine_debug('New Adjustment Line Created');
669 END IF;
670
671 INSERT INTO qp_npreq_rltd_lines_tmp
672 (REQUEST_TYPE_CODE,
673 LINE_DETAIL_INDEX,
674 RELATIONSHIP_TYPE_CODE,
675 RELATED_LINE_DETAIL_INDEX,
676 PRICING_STATUS_CODE,
677 LIST_LINE_ID,
678 -- begin shu,side fix bug 2491158,missing data in qp_npreq_rltd_lines_tmp
679 line_index,
680 related_line_index,
681 related_list_line_id,
682 related_list_line_type,
683 operand_calculation_code,
684 operand,
685 qualifier_value
686 -- end shu, side fix bug 2491158, missing data in qp_npreq_rltd_lines_tmp
687 )
688 VALUES
689 (v_request_type_code,
690 j.LINE_DETAIL_INDEX,
691 qp_preq_grp.G_GENERATED_LINE,
692 v_detail_line_index,
693 qp_preq_grp.G_STATUS_NEW,
694 p_list_line_id,
695 -- begin shu,side fix bug 2491158,missing data in qp_npreq_rltd_lines_tmp
696 p_line_index,
697 v_line_index,
698 i.LIST_LINE_ID,
699 i.LIST_LINE_TYPE_CODE,
700 i.ARITHMETIC_OPERATOR,
701 i.OPERAND,
702 x_total_benefit_qty
703 -- end shu, side fix bug 2491158, missing data in qp_npreq_rltd_lines_tmp
704 );
705
706 IF l_debug = FND_API.G_TRUE THEN
707 qp_preq_grp.engine_debug('New Related Line Detail-Line Detail Created');
708 END IF;
709
710 -- Insert into Line Attributes table qp_npreq_line_attrs_tmp
711 INSERT INTO qp_npreq_line_attrs_tmp
712 (LIST_LINE_ID
713 ,LINE_INDEX
714 ,line_detail_index
715 ,CONTEXT
716 ,ATTRIBUTE
717 ,VALUE_FROM
718 ,SETUP_VALUE_FROM
719 ,VALIDATED_FLAG
720 ,PRODUCT_UOM_CODE
721 ,ATTRIBUTE_LEVEL
722 ,ATTRIBUTE_TYPE
723 ,PRICING_STATUS_CODE)
724 VALUES
725 (i.LIST_LINE_ID
726 ,v_line_index
727 ,v_detail_line_index
728 ,qp_preq_grp.G_PRIC_ITEM_CONTEXT
729 ,qp_preq_grp.G_PRIC_ATTRIBUTE1
730 ,i.PRODUCT_ATTR_VALUE
731 ,i.PRODUCT_ATTR_VALUE
732 ,qp_preq_grp.G_NO
733 ,i.PRODUCT_UOM_CODE
734 ,qp_preq_grp.G_LINE_LEVEL
735 ,qp_preq_grp.G_PRODUCT_TYPE
736 ,qp_preq_grp.G_STATUS_NEW); --Item
737
738 IF l_debug = FND_API.G_TRUE THEN
739 qp_preq_grp.engine_debug('New Attribute Line Created');
740 END IF;
741 END IF; -- l_list_line_id;
742
743 -- Ravi Do not delete the PRG Lines ie uncomment the update below
744 -- This is the code to delete the PRG lines which do not have
745 -- adjustments/benefits even thou x_qualifier_flag=TRUE
746
747 /* IF (v_benefit_exists = FALSE) THEN
748 UPDATE qp_npreq_ldets_tmp --upd1
749 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
750 WHERE PRICING_PHASE_ID = p_pricing_phase_id
751 AND LINE_INDEX = p_line_index
752 AND CREATED_FROM_LIST_LINE_ID = p_list_line_id
753 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
754
755 -- This statement can be commented out
756 UPDATE qp_npreq_line_attrs_tmp
757 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
758 WHERE LINE_INDEX = p_line_index
759 AND LIST_LINE_ID = p_list_line_id
760 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
761
762 END IF; */
763
764 END LOOP;--get_related_lines_cur
765 END LOOP;--get_related_modifier_id_cur
766 qp_preq_grp.G_LINE_DETAIL_INDEX := v_detail_line_index;
767 END IF;--l_rltd_line_detail_index
768 EXCEPTION
769 WHEN OTHERS THEN
770 IF l_debug = FND_API.G_TRUE THEN
771 qp_preq_grp.engine_debug(v_routine_name || ' ' || SQLERRM);
772 END IF;
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
775 IF l_debug = FND_API.G_TRUE THEN
776 qp_preq_grp.engine_debug('Process_PRG: ' || SQLERRM);
777 END IF;
778 END Process_PRG;
779
780 PROCEDURE Process_OID(p_line_index NUMBER,
781 p_list_line_id NUMBER,
782 p_pricing_phase_id NUMBER,
783 x_return_status OUT NOCOPY VARCHAR2,
784 x_return_status_txt OUT NOCOPY VARCHAR2) AS
785
786 -- Get the Related Modifier id from qp_rltd_modifiers table
787 -- Insert a line into the qp_npreq_lines_tmp table for the other item
788 -- Insert a line into qp_npreq_rltd_lines_tmp
789 -- Insert a line into qp_npreq_ldets_tmp table for discount on the new line
790
791 -- Insert a line into qp_npreq_rltd_lines_tmp
792 -- Insert a line into qp_npreq_line_attrs_tmp
793
794
795 CURSOR get_related_modifier_id_cur IS
796 SELECT distinct a.LIST_LINE_TYPE_CODE,b.FROM_RLTD_MODIFIER_ID,b.TO_RLTD_MODIFIER_ID , c.LINE_DETAIL_INDEX,
797 a.PRICING_PHASE_ID , a.AUTOMATIC_FLAG
798 FROM QP_LIST_LINES a, QP_RLTD_MODIFIERS b,qp_npreq_ldets_tmp c
799 WHERE a.LIST_LINE_ID = b.FROM_RLTD_MODIFIER_ID
800 AND a.LIST_LINE_ID = c.CREATED_FROM_LIST_LINE_ID
801 AND b.RLTD_MODIFIER_GRP_TYPE = qp_preq_grp.G_BENEFIT_TYPE
802 AND c.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_OTHER_ITEM_DISCOUNT
803 AND c.PRICING_PHASE_ID = p_pricing_phase_id
804 AND c.LINE_INDEX = p_line_index
805 AND a.LIST_LINE_ID = p_list_line_id
806 AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
807
808 CURSOR get_related_lines_cur(p_related_modifier_id NUMBER) IS
809 SELECT distinct a.LIST_TYPE_CODE,b.LIST_HEADER_ID,b.LIST_LINE_ID,b.LIST_LINE_TYPE_CODE,
810 b.PRICING_GROUP_SEQUENCE, b.ARITHMETIC_OPERATOR,b.OPERAND,b.AUTOMATIC_FLAG,
811 b.BENEFIT_PRICE_LIST_LINE_ID,b.LIST_PRICE, c.PRODUCT_ATTRIBUTE_CONTEXT,
812 c.PRODUCT_ATTRIBUTE,c.PRODUCT_ATTR_VALUE , b.ACCRUAL_FLAG,b.MODIFIER_LEVEL_CODE
813 FROM QP_LIST_HEADERS_B a ,QP_LIST_LINES b, QP_PRICING_ATTRIBUTES c
814 WHERE a.LIST_HEADER_ID = b.LIST_HEADER_ID
815 AND b.LIST_LINE_ID = c.LIST_LINE_ID
816 AND b.LIST_LINE_ID= p_related_modifier_id;
817
818 CURSOR get_benefit_line_index_cur(p_context VARCHAR2,
819 p_attribute VARCHAR2,
820 p_value VARCHAR2,
821 p_list_line_id NUMBER) IS
822
823 -- performance fix for 5573416
824 SELECT /*+ ORDERED USE_NL(a)
825 index(b qp_preq_lines_tmp_n2)
826 index(a qp_preq_line_attrs_tmp_n2)
827 get_benefit_line_index_cur */
828 distinct a.LINE_INDEX,nvl(b.PRICED_QUANTITY,b.LINE_QUANTITY) LINE_QUANTITY
829 FROM qp_npreq_lines_tmp b , qp_npreq_line_attrs_tmp a
830 WHERE a.LINE_INDEX = b.LINE_INDEX
831 AND a.CONTEXT = p_context
832 AND a.ATTRIBUTE = p_attribute
833 AND a.VALUE_FROM = p_value
834 AND a.ATTRIBUTE_TYPE = qp_preq_grp.G_PRODUCT_TYPE
835 AND a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
836 AND b.LINE_TYPE_CODE <> qp_preq_grp.G_ORDER_LEVEL
837 AND NOT EXISTS ( SELECT /*+ index(c qp_preq_ldets_tmp_n1) */ 'x'
838 FROM qp_npreq_ldets_tmp c
839 WHERE c.LINE_INDEX = a.LINE_INDEX
840 AND c.CREATED_FROM_LIST_LINE_ID = p_list_line_id
841 AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW);
842
843 CURSOR get_request_type_code_cur IS
844 SELECT REQUEST_TYPE_CODE
845 FROM qp_npreq_lines_tmp
846 WHERE LINE_INDEX = p_line_index;
847
848 v_detail_line_index NUMBER;
849 v_other_item_list_price NUMBER;
850 v_other_item_final_price NUMBER;
851 v_other_item_base_qty NUMBER;
852 v_other_item_base_uom VARCHAR2(30);
853
854 v_list_price NUMBER;
855 v_base_qty NUMBER;
856 v_base_uom VARCHAR2(30);
857
858 v_other_item_final_qty NUMBER;
859 v_request_type_code VARCHAR2(30);
860
861 x_qualifier_flag BOOLEAN;
862 x_ret_status VARCHAR2(30);
863 x_ret_status_txt VARCHAR2(240);
864 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Process_Other_Benefits.Process_OID';
865 v_benefit_exists BOOLEAN;
866
867 BEGIN
868
869 x_return_status := FND_API.G_RET_STS_SUCCESS;
870 v_benefit_exists := FALSE;
871
872 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
873
874 OPEN get_request_type_code_cur;
875 FETCH get_request_type_code_cur INTO v_request_type_code;
876 CLOSE get_request_type_code_cur;
877
878 v_detail_line_index := qp_preq_grp.G_LINE_DETAIL_INDEX;
879
880 FOR j IN get_related_modifier_id_cur
881 LOOP
882 FOR i IN get_related_lines_cur(j.TO_RLTD_MODIFIER_ID)
883 LOOP
884 IF l_debug = FND_API.G_TRUE THEN
885 qp_preq_grp.engine_debug('Loop Count');
886 END IF;
887 IF l_debug = FND_API.G_TRUE THEN
888 qp_preq_grp.engine_debug ('FROM RLTD MODIFIER ID : ' || j.FROM_RLTD_MODIFIER_ID);
889 qp_preq_grp.engine_debug ('TO RLTD MODIFIER ID : ' || j.TO_RLTD_MODIFIER_ID);
890 END IF;
891
892 -- Verify the qualification for the benefit line also
893 Find_Qualification_For_Benefit(p_line_index,
894 j.TO_RLTD_MODIFIER_ID,
895 qp_preq_grp.G_BENEFIT_TYPE,
896 qp_preq_grp.G_OTHER_ITEM_DISCOUNT,
897 x_qualifier_flag,
898 x_ret_status,
899 x_ret_status_txt);
900
901 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
902 RAISE FND_API.G_EXC_ERROR;
903 END IF;
904
905 IF (x_qualifier_flag = TRUE) THEN
906 IF l_debug = FND_API.G_TRUE THEN
907 qp_preq_grp.engine_debug('The qualifier flag:' || 'TRUE');
908 END IF;
909 ELSE
910
911 IF l_debug = FND_API.G_TRUE THEN
912 qp_preq_grp.engine_debug('Failed in Qualification:' || j.TO_RLTD_MODIFIER_ID);
913 END IF;
914
915 UPDATE qp_npreq_ldets_tmp --upd1
916 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
917 WHERE PRICING_PHASE_ID = p_pricing_phase_id
918 AND LINE_INDEX = p_line_index
919 AND CREATED_FROM_LIST_LINE_ID = j.FROM_RLTD_MODIFIER_ID
920 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
921
922 --This statement is not needed
923 /* UPDATE qp_npreq_line_attrs_tmp
924 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
925 WHERE LIST_LINE_ID = j.FROM_RLTD_MODIFIER_ID
926 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW; */
927
928 END IF;
929
930
931 IF (x_qualifier_flag = TRUE) THEN
932
933 -- This cursor below might fetch multiple benefit lines for the combination of context , attribute , value
934 -- against different request line id's
935 -- Then create Same Benefit line against multiple request lines
936 -- Ex: 1 ItemA(OID) 2 ItemB(Benefit) 3 ItemA(OID) 4ItemB(Benefit)
937 -- In this case , the engine would select line index 2 and line index 4 as part of the cursor
938 -- It will insert the same benefit line against line2 and line 4 when processing OID 1
939 -- For OID2 , the cursor would not fetch any records and the v_benefits_exists flag is FALSE and the
940 -- second OID line gets deleted
941 -- Essentially , the second benefit line is also because of OID 1
942
943 FOR m IN get_benefit_line_index_cur
944 (i.PRODUCT_ATTRIBUTE_CONTEXT, i.PRODUCT_ATTRIBUTE, i.PRODUCT_ATTR_VALUE,i.LIST_LINE_ID)
945 LOOP
946
947 v_benefit_exists := TRUE;
948 v_detail_line_index := v_detail_line_index + 1; -- Temporary
949
950 INSERT INTO qp_npreq_ldets_tmp
951 (LINE_DETAIL_INDEX,
952 LINE_DETAIL_TYPE_CODE,
953 LINE_INDEX,
954 CREATED_FROM_LIST_HEADER_ID,
955 CREATED_FROM_LIST_LINE_ID,
956 CREATED_FROM_LIST_LINE_TYPE,
957 PRICING_GROUP_SEQUENCE,
958 OPERAND_CALCULATION_CODE,
959 OPERAND_VALUE,
960 PROCESSED_FLAG,
961 AUTOMATIC_FLAG,
962 APPLIED_FLAG,
963 ACCRUAL_FLAG,
964 PRICING_PHASE_ID,
965 CREATED_FROM_LIST_TYPE_CODE,
966 PRICING_STATUS_CODE,
967 LINE_QUANTITY,
968 PROCESS_CODE,
969 MODIFIER_LEVEL_CODE)
970 VALUES
971 (v_detail_line_index,
972 qp_preq_grp.G_GENERATED_LINE,
973 m.line_index,
974 i.LIST_HEADER_ID,
975 i.LIST_LINE_ID,
976 i.LIST_LINE_TYPE_CODE,
977 i.PRICING_GROUP_SEQUENCE,
978 i.ARITHMETIC_OPERATOR,
979 i.OPERAND,
980 qp_preq_grp.G_NO,
981 j.AUTOMATIC_FLAG,
982 qp_preq_grp.G_YES,nvl(i.ACCRUAL_FLAG,'N'),
983 j.PRICING_PHASE_ID,
984 i.LIST_TYPE_CODE,
985 qp_preq_grp.G_STATUS_NEW,
986 m.LINE_QUANTITY,
987 qp_preq_grp.G_STATUS_NEW,
988 i.MODIFIER_LEVEL_CODE);
989
990 -- shu, begin fix bug 2491158
991 IF l_debug = FND_API.G_TRUE THEN
992 qp_preq_grp.engine_debug ('debug data going to qp_npreq_rltd_lines_tmp .........');
993 qp_preq_grp.engine_debug ('OID line_index: '||p_line_index);
994 qp_preq_grp.engine_debug ('OID list_line_id: '||p_list_line_id);
995 qp_preq_grp.engine_debug ('Benifit line_index: '||m.line_index);
996 qp_preq_grp.engine_debug ('Benifit list_line_id: '||i.list_line_id);
997 qp_preq_grp.engine_debug ('Benifit list_line_type_code: '||i.list_line_type_code);
998 qp_preq_grp.engine_debug ('Benifit OPERAND: '||i.OPERAND);
999 qp_preq_grp.engine_debug ('Benifit ARITHMETIC_OPERATOR: '||i.ARITHMETIC_OPERATOR);
1000 qp_preq_grp.engine_debug ('Benifit LINE_QUANTITY: '||m.LINE_QUANTITY);
1001 END IF;
1002
1003 INSERT INTO qp_npreq_rltd_lines_tmp
1004 (REQUEST_TYPE_CODE,
1005 LINE_DETAIL_INDEX,
1006 RELATIONSHIP_TYPE_CODE,
1007 RELATED_LINE_DETAIL_INDEX,
1008 PRICING_STATUS_CODE,
1009 line_index, -- begin shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
1010 related_line_index,
1011 list_line_id,
1012 related_list_line_id,
1013 related_list_line_type,
1014 operand_calculation_code,
1015 operand,
1016 qualifier_value -- end shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
1017 )
1018 VALUES
1019 (v_request_type_code,
1020 j.LINE_DETAIL_INDEX,
1021 qp_preq_grp.G_GENERATED_LINE,
1022 v_detail_line_index,
1023 qp_preq_grp.G_STATUS_NEW,
1024 p_line_index, -- begin shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
1025 m.line_index,
1026 p_list_line_id,
1027 i.list_line_id,
1028 i.list_line_type_code,
1029 i.arithmetic_operator,
1030 i.operand,
1031 m.line_quantity -- end shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
1032 );
1033 -- shu, end fix bug 2491158
1034 END LOOP;
1035
1036 -- This is the code to delete the OID lines which do not have adjustments/benefits even thou
1037 -- x_qualifier_flag=TRUE
1038
1039 --Begin Bug No: 7323590
1040 /* commented out for bug No: 7323590
1041 IF (v_benefit_exists = FALSE) THEN
1042 UPDATE qp_npreq_ldets_tmp --upd2
1043 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
1044 WHERE PRICING_PHASE_ID = p_pricing_phase_id
1045 AND LINE_INDEX = p_line_index
1046 AND CREATED_FROM_LIST_LINE_ID = p_list_line_id
1047 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
1048
1049 -- This statement is not needed
1050 /* UPDATE qp_npreq_line_attrs_tmp
1051 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
1052 WHERE LINE_INDEX = p_line_index
1053 AND LIST_LINE_ID = p_list_line_id
1054 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
1055 END IF;
1056 */ -- commenting end
1057 --End Bug No: 7323590
1058 END IF;
1059 END LOOP;
1060 END LOOP;
1061 qp_preq_grp.G_LINE_DETAIL_INDEX := v_detail_line_index;
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 IF l_debug = FND_API.G_TRUE THEN
1065 qp_preq_grp.engine_debug(v_routine_name || ' ' || SQLERRM);
1066 END IF;
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
1069 IF l_debug = FND_API.G_TRUE THEN
1070 qp_preq_grp.engine_debug(SQLERRM);
1071 END IF;
1072 END Process_OID;
1073
1074 PROCEDURE Find_Qualification_For_Benefit(p_line_index NUMBER,
1075 p_list_line_id NUMBER,
1076 p_rltd_modifier_type VARCHAR2,
1077 p_list_line_type VARCHAR2,
1078 x_qualified_flag OUT NOCOPY BOOLEAN,
1079 x_return_status OUT NOCOPY VARCHAR2,
1080 x_return_status_txt OUT NOCOPY VARCHAR2) AS
1081
1082 CURSOR no_rltd_modifiers_in_grp_cur IS
1083 SELECT RLTD_MODIFIER_GRP_NO,COUNT(*) NO_OF_MODIFIERS_IN_GRP
1084 FROM QP_RLTD_MODIFIERS
1085 WHERE FROM_RLTD_MODIFIER_ID = p_list_line_id
1086 AND RLTD_MODIFIER_GRP_TYPE = p_rltd_modifier_type
1087 GROUP BY RLTD_MODIFIER_GRP_NO;
1088
1089 CURSOR rltd_modifiers_cur(p_rltd_grp_no NUMBER) IS
1090 SELECT a.LIST_LINE_TYPE_CODE , b.TO_RLTD_MODIFIER_ID
1091 FROM QP_LIST_LINES a , QP_RLTD_MODIFIERS b
1092 WHERE a.LIST_LINE_ID = b.FROM_RLTD_MODIFIER_ID
1093 AND b.FROM_RLTD_MODIFIER_ID = p_list_line_id
1094 AND b.RLTD_MODIFIER_GRP_TYPE = p_rltd_modifier_type
1095 AND b.RLTD_MODIFIER_GRP_NO = p_rltd_grp_no;
1096
1097 -- bug# 2748723 buy line and additional buy lines should belong to same
1098 -- line category
1099 CURSOR check_rltd_mods_passed_cur(p_rltd_modifier_id NUMBER) IS
1100 -- 10g/R12 performance fixes for 5573393
1101 SELECT distinct c.LIST_LINE_ID
1102 FROM QP_PRICING_ATTRIBUTES c
1103 WHERE c.LIST_LINE_ID = p_rltd_modifier_id
1104 AND c.PRICING_ATTRIBUTE_CONTEXT IS NULL
1105 AND EXISTS
1106 (SELECT /*+ ORDERED NO_UNNEST index(b qp_preq_line_attrs_tmp_n2)
1107 index(a qp_preq_lines_tmp_u1)
1108 index(d qp_preq_lines_tmp_u1) */ 'X'
1109 FROM qp_npreq_line_attrs_tmp b,
1110 qp_npreq_lines_tmp a,
1111 qp_npreq_lines_tmp d
1112 WHERE b.CONTEXT = c.PRODUCT_ATTRIBUTE_CONTEXT
1113 AND b.ATTRIBUTE = c.PRODUCT_ATTRIBUTE
1114 AND b.VALUE_FROM = c.PRODUCT_ATTR_VALUE
1115 AND b.ATTRIBUTE_TYPE = qp_preq_grp.G_PRODUCT_TYPE
1116 AND b.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
1117 AND a.LINE_INDEX = b.LINE_INDEX
1118 AND a.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
1119 AND instr(a.PROCESS_STATUS, QP_PREQ_PUB.G_FREEGOOD) = 0 -- bug 3006670
1120 AND a.PRICED_UOM_CODE = nvl(c.PRODUCT_UOM_CODE, a.PRICED_UOM_CODE)
1121 AND d.LINE_INDEX = p_line_index
1122 AND d.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
1123 AND nvl(d.LINE_CATEGORY,'ORDER') = nvl(a.LINE_CATEGORY,'ORDER'))
1124 UNION
1125 -- 10g/R12 performance fixes for 5573393
1126 SELECT distinct a.LIST_LINE_ID -- Index N7 can be replaced with N2
1127 FROM QP_PRICING_ATTRIBUTES a
1128 WHERE a.LIST_LINE_ID = p_rltd_modifier_id
1129 AND EXISTS
1130 (SELECT /*+ ORDERED NO_UNNEST index(b qp_preq_line_attrs_n2)
1131 index(d qp_preq_lines_tmp_u1)
1132 index(c qp_preq_line_attrs_tmp_n2)
1133 index(e qp_preq_lines_tmp_u1) */ 'X'
1134 FROM qp_npreq_line_attrs_tmp b,
1135 qp_npreq_lines_tmp d,
1136 qp_npreq_line_attrs_tmp c,
1137 qp_npreq_lines_tmp e
1138 WHERE b.CONTEXT = a.PRODUCT_ATTRIBUTE_CONTEXT
1139 AND b.ATTRIBUTE = a.PRODUCT_ATTRIBUTE
1140 AND b.VALUE_FROM = a.PRODUCT_ATTR_VALUE
1141 AND b.ATTRIBUTE_TYPE = qp_preq_grp.G_PRODUCT_TYPE
1142 AND b.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
1143 AND d.PRICED_UOM_CODE = nvl(a.PRODUCT_UOM_CODE, d.PRICED_UOM_CODE)
1144 AND d.LINE_INDEX = b.LINE_INDEX
1145 AND d.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
1146 AND b.LINE_INDEX = c.LINE_INDEX
1147 AND instr(d.PROCESS_STATUS, QP_PREQ_PUB.G_FREEGOOD) = 0 -- bug 3006670
1148 AND c.CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT
1149 AND c.CONTEXT = a.PRICING_ATTRIBUTE_CONTEXT
1150 AND c.ATTRIBUTE = a.PRICING_ATTRIBUTE
1151 AND qp_number.canonical_to_number(c.VALUE_FROM) BETWEEN
1152 nvl(a.PRICING_ATTR_VALUE_FROM,qp_number.canonical_to_number(c.VALUE_FROM)) AND
1153 nvl(a.PRICING_ATTR_VALUE_TO,qp_number.canonical_to_number(c.VALUE_FROM))
1154 AND c.ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
1155 AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
1156 AND e.LINE_INDEX = p_line_index
1157 AND e.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
1158 AND nvl(e.LINE_CATEGORY,'ORDER') = nvl(d.LINE_CATEGORY,'ORDER'));
1159
1160 v_count NUMBER := 0;
1161 v_qualified_flag BOOLEAN := FALSE;
1162 v_to_rltd_modifier_id NUMBER;
1163 v_no_of_mods_in_grp NUMBER := 0;
1164
1165 v_update_flag BOOLEAN := FALSE;
1166 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Process_Other_Benefits.Find_Qualification_For_Benefit';
1167 BEGIN
1168
1169 x_return_status := FND_API.G_RET_STS_SUCCESS;
1170 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1171
1172 IF (p_rltd_modifier_type = qp_preq_grp.G_QUALIFIER_TYPE) THEN -- Do group checking
1173
1174 IF l_debug = FND_API.G_TRUE THEN
1175 qp_preq_grp.engine_debug('QUALIFIER QUALIFICATION');
1176 END IF;
1177
1178 FOR i IN no_rltd_modifiers_in_grp_cur
1179 LOOP
1180 IF l_debug = FND_API.G_TRUE THEN
1181 qp_preq_grp.engine_debug('Group no: ' || i.RLTD_MODIFIER_GRP_NO);
1182 END IF;
1183
1184 v_count := 0; --satisfied group cnt re-init on group change -- #2725979
1185
1186 FOR j IN rltd_modifiers_cur(i.RLTD_MODIFIER_GRP_NO)
1187 LOOP
1188 IF l_debug = FND_API.G_TRUE THEN
1189 qp_preq_grp.engine_debug('To Modifier Id: ' || j.TO_RLTD_MODIFIER_ID);
1190 END IF;
1191
1192 FOR k IN check_rltd_mods_passed_cur(j.TO_RLTD_MODIFIER_ID)
1193 LOOP
1194 IF l_debug = FND_API.G_TRUE THEN
1195 qp_preq_grp.engine_debug('Qualification Succeeded for Rltd Modifier:' || j.TO_RLTD_MODIFIER_ID ||
1196 k.list_line_id);
1197 END IF;
1198
1199 IF(j.TO_RLTD_MODIFIER_ID = k.LIST_LINE_ID) THEN
1200 v_count := v_count + 1;
1201 END IF;
1202
1203 END LOOP; --k
1204 END LOOP; --j
1205
1206 v_no_of_mods_in_grp := i.NO_OF_MODIFIERS_IN_GRP; -- Store the no of modifiers count
1207
1208 IF l_debug = FND_API.G_TRUE THEN
1209 qp_preq_grp.engine_debug('No of modifiers in grp :' || v_no_of_mods_in_grp);
1210 qp_preq_grp.engine_debug('Count :' || v_count);
1211 END IF;
1212
1213 IF(v_no_of_mods_in_grp = v_count) THEN
1214 v_qualified_flag := TRUE;
1215 v_update_flag := FALSE;
1216 ELSE
1217 v_qualified_flag := FALSE;
1218 v_update_flag := TRUE;
1219 END IF;
1220 x_qualified_flag := v_qualified_flag;
1221 EXIT WHEN (v_qualified_flag = TRUE); -- What will happen to other groups , once 1 group is valid
1222 END LOOP; --i
1223
1224 -- This is the case wherin there is no QUALIFIER Record .. Ex: Buy A , get 20% of B.There is
1225 -- only BENEFIT line
1226
1227 IF (v_no_of_mods_in_grp = 0 and v_count = 0 ) THEN
1228 IF l_debug = FND_API.G_TRUE THEN
1229 qp_preq_grp.engine_debug(v_no_of_mods_in_grp);
1230 qp_preq_grp.engine_debug(v_count);
1231 END IF;
1232 x_qualified_flag := TRUE;
1233 END IF;
1234 ELSE -- Benefit -- If there on the order give it
1235
1236 IF l_debug = FND_API.G_TRUE THEN
1237 qp_preq_grp.engine_debug('BENEFIT QUALIFICATION');
1238 END IF;
1239
1240 FOR k IN check_rltd_mods_passed_cur(p_list_line_id)
1241 LOOP
1242 IF l_debug = FND_API.G_TRUE THEN
1243 qp_preq_grp.engine_debug('Passed List line id:' || p_list_line_id);
1244 qp_preq_grp.engine_debug('Matching List Line Id:' || k.list_line_id);
1245 END IF;
1246
1247 IF(k.LIST_LINE_ID = p_list_line_id) THEN
1248 v_count := v_count + 1;
1249 END IF;
1250 END LOOP; --k
1251
1252 IF (v_count > 0) THEN
1253 v_qualified_flag := TRUE;
1254 v_update_flag := FALSE;
1255 ELSE
1256 v_qualified_flag := FALSE;
1257 v_update_flag := TRUE;
1258 END IF;
1259 x_qualified_flag := v_qualified_flag;
1260 END IF;
1261
1262 IF l_debug = FND_API.G_TRUE THEN
1263 qp_preq_grp.engine_debug('Benefit Record Count:' || v_count);
1264 END IF;
1265
1266 IF (v_update_flag = TRUE) THEN
1267 UPDATE qp_npreq_ldets_tmp --upd1
1268 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
1269 WHERE CREATED_FROM_LIST_LINE_ID = p_list_line_id;
1270
1271 -- This statement is not needed
1272 /* UPDATE qp_npreq_line_attrs_tmp a
1273 SET a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
1274 WHERE a.LINE_DETAIL_INDEX IN (SELECT b.LINE_DETAIL_INDEX
1275 FROM qp_npreq_ldets_tmp b
1276 WHERE b.CREATED_FROM_LIST_LINE_ID = p_list_line_id); */
1277
1278 IF l_debug = FND_API.G_TRUE THEN
1279 qp_preq_grp.engine_debug ('BENEFIT STATUS UPDATED');
1280 END IF;
1281 END IF;
1282 --v_count := 0; -- Reinitialize for each grp(done on change of group earlier)
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 IF l_debug = FND_API.G_TRUE THEN
1286 qp_preq_grp.engine_debug(v_routine_name || ' ' || SQLERRM);
1287 END IF;
1288 x_return_status := FND_API.G_RET_STS_ERROR;
1289 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
1290 IF l_debug = FND_API.G_TRUE THEN
1291 qp_preq_grp.engine_debug ('Error in Proc');
1292 END IF;
1293 END Find_Qualification_For_Benefit;
1294
1295 PROCEDURE Process_Other_Benefits(p_line_index NUMBER,
1296 p_pricing_phase_id NUMBER,
1297 p_pricing_effective_date DATE,
1298 p_line_quantity NUMBER,
1299 p_simulation_flag VARCHAR2,
1300 x_return_status OUT NOCOPY VARCHAR2,
1301 x_return_status_txt OUT NOCOPY VARCHAR2) AS
1302
1303
1304 CURSOR get_list_lines_cur IS
1305 SELECT /*+ index (ldets qp_preq_ldets_tmp_N2) */
1306 CREATED_FROM_LIST_LINE_ID,CREATED_FROM_LIST_LINE_TYPE,LINE_DETAIL_INDEX , MODIFIER_LEVEL_CODE
1307 FROM qp_npreq_ldets_tmp ldets
1308 WHERE CREATED_FROM_LIST_LINE_TYPE IN (qp_preq_grp.G_OTHER_ITEM_DISCOUNT, qp_preq_grp.G_PROMO_GOODS_DISCOUNT,
1309 qp_preq_grp.G_COUPON_ISSUE)
1310 AND PRICING_PHASE_ID = p_pricing_phase_id
1311 AND LINE_INDEX = p_line_index
1312 AND ASK_FOR_FLAG IN (qp_preq_grp.G_YES,qp_preq_grp.G_NO)
1313 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
1314
1315 x_qualifier_flag BOOLEAN;
1316
1317 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Process_Other_Benefits.Process_Other_Benefits';
1318 x_ret_status VARCHAR2(30);
1319 x_ret_status_txt VARCHAR2(240);
1320
1321 BEGIN
1322
1323 x_return_status := FND_API.G_RET_STS_SUCCESS;
1324 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1325
1326 FOR i IN get_list_lines_cur
1327 LOOP
1328
1329 IF l_debug = FND_API.G_TRUE THEN
1330 qp_preq_grp.engine_debug('Qualifying List Line Id: ' || i.CREATED_FROM_LIST_LINE_ID);
1331 qp_preq_grp.engine_debug('List Line Type: ' || i.CREATED_FROM_LIST_LINE_TYPE);
1332 END IF;
1333
1334 Find_Qualification_For_Benefit(p_line_index,
1335 i.CREATED_FROM_LIST_LINE_ID,
1336 qp_preq_grp.G_QUALIFIER_TYPE,
1337 i.CREATED_FROM_LIST_LINE_TYPE,
1338 x_qualifier_flag,
1339 x_ret_status,
1340 x_ret_status_txt);
1341
1342 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1343 RAISE FND_API.G_EXC_ERROR;
1344 END IF;
1345
1346 IF (x_qualifier_flag = TRUE) THEN
1347 IF l_debug = FND_API.G_TRUE THEN
1348 qp_preq_grp.engine_debug('QUALIFICATION SUCCEDED');
1349 qp_preq_grp.engine_debug('List Line Type : ' || i.created_from_list_line_type);
1350 END IF;
1351 IF (i.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_OTHER_ITEM_DISCOUNT) THEN
1352 Process_OID(p_line_index,i.CREATED_FROM_LIST_LINE_ID,p_pricing_phase_id,x_ret_status,x_ret_status_txt);
1353 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF;
1356 ELSIF (i.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_PROMO_GOODS_DISCOUNT) THEN
1357 Process_PRG(p_line_index,i.line_detail_index,i.modifier_level_code,i.CREATED_FROM_LIST_LINE_ID,
1358 p_pricing_phase_id,x_ret_status,x_ret_status_txt);
1359 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1360 RAISE FND_API.G_EXC_ERROR;
1361 END IF;
1362 ELSIF (i.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_COUPON_ISSUE)
1363 AND (qp_preq_grp.G_PUBLIC_API_CALL_FLAG = qp_preq_grp.G_NO
1364 or (qp_preq_grp.G_PUBLIC_API_CALL_FLAG = qp_preq_grp.G_YES --bug 3859759
1365 and qp_preq_grp.G_TEMP_TABLE_INSERT_FLAG = qp_preq_grp.G_YES)) THEN
1366 QP_COUPON_PVT.PROCESS_COUPON_ISSUE(i.line_detail_index,
1367 p_pricing_phase_id,
1368 p_line_quantity,
1369 p_simulation_flag,
1370 x_ret_status,
1371 x_ret_status_txt);
1372
1373 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1374 RAISE FND_API.G_EXC_ERROR;
1375 END IF;
1376
1377 /* redeem coupon should not be called here. Because coupon benefits could be of
1378 any type, moved to qp_preq_grp package
1379 QP_COUPON_PVT.REDEEM_COUPONS(p_simulation_flag, x_ret_status, x_ret_status_txt);
1380
1381 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384 */
1385
1386 END IF;
1387 ELSE
1388 UPDATE qp_npreq_ldets_tmp --upd1
1389 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
1390 WHERE PRICING_PHASE_ID = p_pricing_phase_id
1391 AND LINE_INDEX = p_line_index
1392 AND CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
1393 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
1394
1395 -- This statement is not needed
1396 /* UPDATE qp_npreq_line_attrs_tmp
1397 SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
1398 WHERE LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
1399 AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW; */
1400 END IF;
1401 END LOOP;
1402 EXCEPTION
1403 WHEN FND_API.G_EXC_ERROR THEN
1404 IF l_debug = FND_API.G_TRUE THEN
1405 qp_preq_grp.engine_debug(v_routine_name || ' ' || x_ret_status_txt);
1406 END IF;
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 x_return_status_txt := x_ret_status_txt;
1409 WHEN OTHERS THEN
1410 IF l_debug = FND_API.G_TRUE THEN
1411 qp_preq_grp.engine_debug(v_routine_name || ' ' || SQLERRM);
1412 END IF;
1413 x_return_status := FND_API.G_RET_STS_ERROR;
1414 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
1415 END Process_Other_Benefits;
1416 END QP_Process_Other_Benefits_PVT;