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