[Home] [Help]
PACKAGE BODY: APPS.QP_LIMIT_BALANCE_CHECK_PVT
Source
1 PACKAGE BODY QP_LIMIT_BALANCE_CHECK_PVT AS
2 /* $Header: QPXVLCKB.pls 120.7.12010000.3 2008/11/05 11:59:57 kdurgasi ship $ */
3
4
5 /**********************************************************************
6 Utility Function to Update Limit Balance and the adjustment on the
7 ldets table(s).
8 ***********************************************************************/
9 l_debug VARCHAR2(3);
10 PROCEDURE Build_Message_Text(p_List_Header_Id IN NUMBER
11 ,p_List_Line_Id IN NUMBER
12 ,p_Limit_Id IN NUMBER
13 ,p_full_available_amount IN NUMBER
14 ,p_wanted_amount IN NUMBER
15 ,p_limit_code IN VARCHAR2 -- EXCEEDED or ADJUSTED
16 ,p_limit_level IN VARCHAR2 -- H or L
17 ,p_operand_value IN NUMBER
18 ,p_operand_calculation_code IN VARCHAR2
19 ,p_least_percent IN NUMBER
20 ,p_message_text OUT NOCOPY VARCHAR2
21 )
22 IS
23 l_modifier_name VARCHAR2(240);
24 l_list_line_no VARCHAR2(30);
25 l_limit_number NUMBER;
26 l_message_text VARCHAR2(2000);
27 l_limit_exceeded_by NUMBER;
28 l_original_modifier_value NUMBER := 0; --defined as number for bug 4912649 (10,2) := 0;
29 l_operator_name VARCHAR2(80);
30 BEGIN
31 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
32
33 IF l_debug = FND_API.G_TRUE THEN
34 QP_PREQ_GRP.engine_debug('*** Entering Build_Message_Text ****');
35 QP_PREQ_GRP.engine_debug('Limit Level '||p_limit_level);
36 END IF;
37
38 IF p_limit_level = 'H' THEN
39 BEGIN
40 select name into l_modifier_name from qp_list_headers_vl
41 where list_header_id = p_List_Header_Id;
42 EXCEPTION
43 when no_data_found then
44 IF l_debug = FND_API.G_TRUE THEN
45 QP_PREQ_GRP.engine_debug('*** list header not found for id ***' || p_List_Header_Id);
46 END IF;
47 END;
48 END IF;
49
50 IF p_limit_level = 'L' THEN
51 BEGIN
52 select list_line_no into l_list_line_no from qp_list_lines
53 where list_line_id = p_List_Line_Id;
54 EXCEPTION
55 when no_data_found then
56 IF l_debug = FND_API.G_TRUE THEN
57 QP_PREQ_GRP.engine_debug('*** list line not found for id ***' || p_List_Line_Id);
58 END IF;
59 END;
60 END IF;
61
62 BEGIN
63 select limit_number into l_limit_number from qp_limits
64 where limit_id = p_Limit_Id;
65 EXCEPTION
66 when no_data_found then
67 IF l_debug = FND_API.G_TRUE THEN
68 QP_PREQ_GRP.engine_debug('*** limit not found for id ***'||p_Limit_Id);
69 END IF;
70 END;
71
72 BEGIN
73 select meaning into l_operator_name from qp_lookups
74 where LOOKUP_TYPE = 'ARITHMETIC_OPERATOR' and lookup_code = p_operand_calculation_code;
75 EXCEPTION
76 when no_data_found then
77 IF l_debug = FND_API.G_TRUE THEN
78 QP_PREQ_GRP.engine_debug('*** lookup code found ***');
79 END IF;
80 END;
81
82 IF l_debug = FND_API.G_TRUE THEN
83 QP_PREQ_GRP.engine_debug('######INSIDE BUILD_MESSAGE_TEXT######- ARITHMETIC_OPERATOR ' || p_operand_calculation_code);
84
85 QP_PREQ_GRP.engine_debug('######INSIDE BUILD_MESSAGE_TEXT######- ARITHMETIC_OPERATOR ' || l_operator_name);
86
87 END IF;
88 l_limit_exceeded_by := p_wanted_amount - p_full_available_amount;
89 l_original_modifier_value := (100 * p_operand_value)/p_least_percent;
90
91 IF (p_limit_code = QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED) THEN
92 IF (p_limit_level = 'H') THEN
93 FND_MESSAGE.SET_NAME('QP','QP_HEADER_LIMIT_EXCEEDED');
94 FND_MESSAGE.SET_TOKEN('PROMOTION_NUMBER',l_modifier_name);
95 FND_MESSAGE.SET_TOKEN('LIMIT_NUMBER',l_limit_number);
96 FND_MESSAGE.SET_TOKEN('LIMIT_EXCEEDED_BY',nvl(l_limit_exceeded_by,0));
97 l_message_text := FND_MESSAGE.GET;
98 ELSIF (p_limit_level = 'L') THEN
99 FND_MESSAGE.SET_NAME('QP','QP_LINE_LIMIT_EXCEEDED');
100 FND_MESSAGE.SET_TOKEN('MODIFIER_NUMBER',l_list_line_no);
101 FND_MESSAGE.SET_TOKEN('LIMIT_NUMBER',l_limit_number);
102 FND_MESSAGE.SET_TOKEN('LIMIT_EXCEEDED_BY',nvl(l_limit_exceeded_by,0));
103 l_message_text := FND_MESSAGE.GET;
104 END IF;
105 END IF;
106
107 IF (p_limit_code = QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED) THEN
108 IF (p_limit_level = 'H') THEN
109 FND_MESSAGE.SET_NAME('QP','QP_HEADER_LIMIT_ADJUSTED');
110 FND_MESSAGE.SET_TOKEN('PROMOTION_NUMBER',l_modifier_name);
111 FND_MESSAGE.SET_TOKEN('LIMIT_NUMBER',l_limit_number);
112 FND_MESSAGE.SET_TOKEN('OPERAND',l_original_modifier_value);
113 FND_MESSAGE.SET_TOKEN('PERCENT',p_operand_value);
114 FND_MESSAGE.SET_TOKEN('OPERATOR',l_operator_name);
115 l_message_text := FND_MESSAGE.GET;
116 ELSIF (p_limit_level = 'L') THEN
117 FND_MESSAGE.SET_NAME('QP','QP_LINE_LIMIT_ADJUSTED');
118 FND_MESSAGE.SET_TOKEN('MODIFIER_NUMBER',l_list_line_no);
119 FND_MESSAGE.SET_TOKEN('LIMIT_NUMBER',l_limit_number);
120 FND_MESSAGE.SET_TOKEN('OPERAND',l_original_modifier_value);
121 FND_MESSAGE.SET_TOKEN('PERCENT',p_operand_value);
122 FND_MESSAGE.SET_TOKEN('OPERATOR',l_operator_name);
123 l_message_text := FND_MESSAGE.GET;
124 END IF;
125 END IF;
126
127 p_message_text := l_message_text;
128
129 IF l_debug = FND_API.G_TRUE THEN
130 QP_PREQ_GRP.engine_debug('*** Leaving Build_Message_Text ****');
131 END IF;
132
133 END Build_Message_Text;
134
135 FUNCTION Update_Balance (x_return_text OUT NOCOPY VARCHAR2)
136 RETURN VARCHAR2
137 IS
138 PRAGMA AUTONOMOUS_TRANSACTION;
139
140 l_percent NUMBER := 100;
141 l_given_amount NUMBER;
142
143 e_balance_not_available EXCEPTION;
144 l_return_status VARCHAR2(1);
145
146 BEGIN
147 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
148 IF l_debug = FND_API.G_TRUE THEN
149 QP_PREQ_GRP.engine_debug('------------------------');
150 QP_PREQ_GRP.engine_debug('***Begin Update_Balance***');
151
152 END IF;
153 IF g_limit_balance_line.COUNT > 0 THEN
154
155 --Get the minimum available_percent across all limitbalances for a given line
156 FOR i IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
157 LOOP
158 IF l_debug = FND_API.G_TRUE THEN
159 QP_PREQ_GRP.engine_debug('Processing g_limit_balance_line ' || i);
160
161 END IF;
162 IF g_limit_balance_line(i).hard_limit_exceeded THEN
163 IF l_debug = FND_API.G_TRUE THEN
164 QP_PREQ_GRP.engine_debug('Hard Limit with 0 balance encountered. ' ||
165 'Deleting all Balance lines for current list_line_id. ');
166 END IF;
167 g_limit_balance_line.DELETE; -- No need to process balances further
168 l_return_status := FND_API.G_RET_STS_SUCCESS;
169 RETURN l_return_status;
170 --without updating any of the limits for current list_line_id
171 END IF;
172
173 l_percent := least(l_percent, g_limit_balance_line(i).available_percent);
174 IF l_debug = FND_API.G_TRUE THEN
175 QP_PREQ_GRP.engine_debug('least percent so far' || l_percent);
176 END IF;
177 END LOOP;
178
179 --Perform Update or Insert into qp_limit_balances as required.
180 FOR i IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
181 LOOP
182 IF l_debug = FND_API.G_TRUE THEN
183 QP_PREQ_GRP.engine_debug('Processing limit '||g_limit_balance_line(i).limit_id);
184
185 QP_PREQ_GRP.engine_debug('Limit Level Code '||
186 g_limit_balance_line(i).limit_level_code);
187 QP_PREQ_GRP.engine_debug('Organization Context '||
188 g_limit_balance_line(i).organization_attr_context);
189 QP_PREQ_GRP.engine_debug('Organization Attribute '||
190 g_limit_balance_line(i).organization_attribute);
191 QP_PREQ_GRP.engine_debug('Organization Attr Value '||
192 g_limit_balance_line(i).organization_attr_value);
193 QP_PREQ_GRP.engine_debug('Multival Attr1 Context '||
194 g_limit_balance_line(i).multival_attr1_context);
195 QP_PREQ_GRP.engine_debug('Multival Attribute1 '||
196 g_limit_balance_line(i).multival_attribute1);
197 QP_PREQ_GRP.engine_debug('Multival Attr1 Value '||
198 g_limit_balance_line(i).multival_attr1_value);
199 QP_PREQ_GRP.engine_debug('Multival Attr2 Context '||
200 g_limit_balance_line(i).multival_attr2_context);
201 QP_PREQ_GRP.engine_debug('Multival Attribute2 '||
202 g_limit_balance_line(i).multival_attribute2);
203 QP_PREQ_GRP.engine_debug('Multival Attr2 Value '||
204 g_limit_balance_line(i).multival_attr2_value);
205 QP_PREQ_GRP.engine_debug('Balance Price Request Code '||
206 g_limit_balance_line(i).bal_price_request_code);
207 QP_PREQ_GRP.engine_debug('Amount Given '||l_given_amount);
208 QP_PREQ_GRP.engine_debug('Limit Id '|| g_limit_balance_line(i).limit_id);
209 QP_PREQ_GRP.engine_debug('Limit Balance Id '||
210 g_limit_balance_line(i).limit_balance_id);
211
212
213 END IF;
214
215 l_given_amount := round((l_percent/100) * g_limit_balance_line(i).wanted_amount,2);
216
217 IF g_limit_balance_line(i).process_action = g_update THEN
218 IF l_debug = FND_API.G_TRUE THEN
219 QP_PREQ_GRP.engine_debug('Update Required');
220
221 END IF;
222 IF g_limit_balance_line(i).limit_level_code = 'ACROSS_TRANSACTION' THEN
223 /*
224 INDX,qp_limit_balance_check_pvt.update_balance.upd1,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
225 */
226 --sql statement upd1
227 UPDATE qp_limit_balances
228 SET available_amount = round(available_amount,2) - l_given_amount
229 + nvl(g_limit_balance_line(i).transaction_amount, 0),
230 last_update_date = sysdate,
231 last_updated_by = fnd_global.user_id
232 WHERE round(available_amount,2) >=
233 DECODE(g_limit_balance_line(i).limit_exceed_action_code,
234 'HARD', l_given_amount -
235 nvl(g_limit_balance_line(i).transaction_amount, 0),
236 -999999999999999999999999999
237 )
238 AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
239
240 ELSIF g_limit_balance_line(i).limit_level_code = 'TRANSACTION' THEN
241 /*
242 INDX,qp_limit_balance_check_pvt.update_balance.upd2,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
243 */
244 --sql statement upd2
245 UPDATE qp_limit_balances
246 SET available_amount = round(available_amount,2) - l_given_amount
247 + nvl(g_limit_balance_line(i).transaction_amount, 0),
248 last_update_date = sysdate,
249 last_updated_by = fnd_global.user_id
250 WHERE round(available_amount,2) >=
251 DECODE(g_limit_balance_line(i).limit_exceed_action_code,
252 'HARD',l_given_amount -
253 nvl(g_limit_balance_line(i).transaction_amount, 0),
254 -99999999999999999999999999999
255 )
256 AND limit_balance_id = g_limit_balance_line(i).limit_balance_id
257 AND price_request_code =
258 g_limit_balance_line(i).bal_price_request_code;
259
260 END IF; --If limit_level_code = 'ACROSS_TRANSACTION'
261
262 --Calculated Limit Balance no longer available. Raise error.
263 IF SQL%ROWCOUNT = 0 THEN
264 IF l_debug = FND_API.G_TRUE THEN
265 QP_PREQ_GRP.engine_debug('Balance no longer available ');
266 END IF;
267 RAISE E_BALANCE_NOT_AVAILABLE;
268 END IF;
269
270 ELSIF g_limit_balance_line(i).process_action = g_insert THEN
271 IF l_debug = FND_API.G_TRUE THEN
272 QP_PREQ_GRP.engine_debug('Insert Required');
273
274 QP_PREQ_GRP.engine_debug('given_amount '|| l_given_amount);
275 QP_PREQ_GRP.engine_debug('transaction_amount '||
276 nvl(g_limit_balance_line(i).transaction_amount, 0));
277
278 END IF;
279 INSERT INTO qp_limit_balances
280 (limit_id,
281 limit_balance_id,
282 creation_date,
283 created_by,
284 last_update_date,
285 last_updated_by,
286 available_amount,
287 reserved_amount,
288 consumed_amount,
289 organization_attr_context,
290 organization_attribute,
291 organization_attr_value,
292 multival_attr1_context,
293 multival_attribute1,
294 multival_attr1_value,
295 multival_attr1_type,
296 multival_attr1_datatype,
297 multival_attr2_context,
298 multival_attribute2,
299 multival_attr2_value,
300 multival_attr2_type,
301 multival_attr2_datatype,
302 price_request_code
303 )
304 VALUES
305 (g_limit_balance_line(i).limit_id,
306 g_limit_balance_line(i).limit_balance_id,
307 sysdate,
308 fnd_global.user_id,
309 sysdate,
310 fnd_global.user_id,
311 g_limit_balance_line(i).limit_amount - l_given_amount
312 + nvl(g_limit_balance_line(i).transaction_amount, 0),
313 0,
314 0,
315 g_limit_balance_line(i).organization_attr_context,
316 g_limit_balance_line(i).organization_attribute,
317 g_limit_balance_line(i).organization_attr_value,
318 g_limit_balance_line(i).multival_attr1_context,
319 g_limit_balance_line(i).multival_attribute1,
320 g_limit_balance_line(i).multival_attr1_value,
321 g_limit_balance_line(i).multival_attr1_type,
322 g_limit_balance_line(i).multival_attr1_datatype,
323 g_limit_balance_line(i).multival_attr2_context,
324 g_limit_balance_line(i).multival_attribute2,
325 g_limit_balance_line(i).multival_attr2_value,
326 g_limit_balance_line(i).multival_attr2_type,
327 g_limit_balance_line(i).multival_attr2_datatype,
328 g_limit_balance_line(i).bal_price_request_code
329 );
330
331 END IF;
332
333 IF l_debug = FND_API.G_TRUE THEN
334 QP_PREQ_GRP.engine_debug('amount given'||l_given_amount);
335 END IF;
336 g_limit_balance_line(i).given_amount := l_given_amount;
337 g_limit_balance_line(i).least_percent := l_percent;
338
339
340 IF g_limit_balance_line(i).created_from_list_line_type IN
341 ('DIS', 'SUR', 'FREIGHT_CHARGE', 'PBH')
342 AND g_limit_balance_line(i).operand_calculation_code IN
343 ('%', 'AMT', 'LUMPSUM')
344 THEN
345 g_limit_balance_line(i).operand_value :=
346 (l_percent/100) * g_limit_balance_line(i).operand_value;
347
348 ELSIF g_limit_balance_line(i).created_from_list_line_type IN
349 ('DIS', 'SUR', 'FREIGHT_CHARGE', 'PBH')
350 AND g_limit_balance_line(i).operand_calculation_code = 'NEWPRICE'
351 THEN
352 g_limit_balance_line(i).operand_value :=
353 g_limit_balance_line(i).operand_value -
354 (100 - l_percent)/100 * g_limit_balance_line(i).adjustment_amount;
355
356 END IF;
357
358 IF g_limit_balance_line(i).created_from_list_line_type IN
359 ('DIS', 'SUR', 'FREIGHT_CHARGE', 'PBH')
360 AND g_limit_balance_line(i).basis = 'ACCRUAL'
361 THEN
362 g_limit_balance_line(i).benefit_qty :=
363 (l_percent/100) * g_limit_balance_line(i).benefit_qty;
364 END IF;
365
366 END LOOP;
367
371
368 END IF; --g_limit_balance_line.COUNT > 0
369
370 COMMIT;
372 IF l_debug = FND_API.G_TRUE THEN
373 QP_PREQ_GRP.engine_debug('***End Update_Balance***');
374 QP_PREQ_GRP.engine_debug('------------------------');
375
376 END IF;
377 l_return_status := FND_API.G_RET_STS_SUCCESS;
378 x_return_text := 'Success';
379
380 RETURN l_return_status;
381
382 EXCEPTION
383 WHEN DUP_VAL_ON_INDEX THEN
384 IF l_debug = FND_API.G_TRUE THEN
385 QP_PREQ_GRP.engine_debug('Insert Failed with Duplicate Value on Index');
386 END IF;
387 l_return_status := FND_API.G_RET_STS_ERROR;
388 x_return_text := 'Insert Failed with Duplicate Value on Index error ' ||
389 'in procedure Update_Balance';
390 ROLLBACK;
391 RETURN l_return_status;
392
393 WHEN E_BALANCE_NOT_AVAILABLE THEN
394 IF l_debug = FND_API.G_TRUE THEN
395 QP_PREQ_GRP.engine_debug('Limit Balance no longer available.Update Failed.');
396 END IF;
397 l_return_status := FND_API.G_RET_STS_ERROR;
398 x_return_text := 'Update Failed in procedure Update_Balance because ' ||
399 'Limit Balance no longer available';
400 ROLLBACK;
401 RETURN l_return_status;
402
403 WHEN OTHERS THEN
404 IF l_debug = FND_API.G_TRUE THEN
405 QP_PREQ_GRP.engine_debug('Other Exception in Update_Balance');
406 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
407 END IF;
408 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 x_return_text := substr(sqlerrm, 1, 2000);
410 ROLLBACK;
411 RETURN l_return_status;
412
413 END Update_Balance;
414
415
416 /***********************************************************************
417 Utility Function to recalculate available balance before updating the
418 limit_balance table. Called after update_balance fails the first time.
419 ***********************************************************************/
420
421 FUNCTION Recheck_Balance
422 RETURN BOOLEAN
423 IS
424 l_full_available_amount NUMBER := 0;
425 l_message VARCHAR2(240);
426
427 BEGIN
428
429 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
430 --Increment the recheck loop count everytime the Function is entered.
431 G_LOOP_COUNT := G_LOOP_COUNT + 1;
432
433 IF l_debug = FND_API.G_TRUE THEN
434 QP_PREQ_GRP.engine_debug('***Begin Recheck_Balance***');
435
436 END IF;
437 IF g_limit_balance_line.COUNT > 0 THEN
438
439 FOR i IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
440 LOOP
441 IF g_limit_balance_line(i).each_attr_exists = 'N' THEN
442 IF l_debug = FND_API.G_TRUE THEN
443 QP_PREQ_GRP.engine_debug('Each attr does not exist');
444 END IF;
445 BEGIN
446
447 IF g_limit_balance_line(i).limit_level_code = 'ACROSS_TRANSACTION' THEN
448 /*
449 INDX,qp_limit_balance_check_pvt.recheck_balance.sel1,QP_LIMIT_BALANCES_U2,LIMIT_ID,1
450 */
451 --sql statement sel1
452 SELECT available_amount
453 INTO l_full_available_amount
454 FROM qp_limit_balances
455 WHERE limit_id = g_limit_balance_line(i).limit_id;
456
457 ELSIF g_limit_balance_line(i).limit_level_code = 'TRANSACTION' THEN
458 /*
459 INDX,qp_limit_balance_check_pvt.recheck_balance.sel2,QP_LIMIT_BALANCES_N1,LIMIT_ID,1
460 INDX,qp_limit_balance_check_pvt.recheck_balance.sel2,QP_LIMIT_BALANCES_N1,PRICE_REQUEST_CODE,2
461 */
462 --sql statement sel2
463 SELECT available_amount
464 INTO l_full_available_amount
465 FROM qp_limit_balances
466 WHERE limit_id = g_limit_balance_line(i).limit_id
467 AND price_request_code =
468 g_limit_balance_line(i).bal_price_request_code;
469
470 END IF; --IF limit_level_code = 'ACROSS_TRANSACTION'
471
472 EXCEPTION
473 WHEN NO_DATA_FOUND THEN
474 l_full_available_amount := g_limit_balance_line(i).limit_amount;
475 g_limit_balance_line(i).process_action := g_insert;
476
477 WHEN TOO_MANY_ROWS THEN
478 IF l_debug = FND_API.G_TRUE THEN
479 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
480
481 END IF;
482 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
483 THEN
484 FND_MESSAGE.SET_NAME('QP','QP_MULT_LIMIT_BALANCES');
485 FND_MESSAGE.SET_TOKEN('LIMIT', g_limit_balance_line(i).limit_id);
486 l_message := FND_MESSAGE.GET;
487
488 --Update the lines_tmp table with the translated error message.
489 /*
490 INDX,qp_limit_balance_check_pvt.recheck_balance.upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
491 */
492 --sql statement upd1
493 UPDATE qp_npreq_lines_tmp
494 SET pricing_status_text = l_message,
495 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
496 WHERE line_index = g_limit_balance_line(i).line_index;
497
498 --Update the ldets_tmp table with the translated error message.
499 /*
500 INDX,qp_limit_balance_check_pvt.recheck_balance.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
501 INDX,qp_limit_balance_check_pvt.recheck_balance.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
502 */
503 --sql statement upd2
504 UPDATE qp_npreq_ldets_tmp
508 AND created_from_list_line_id =
505 SET pricing_status_text = l_message,
506 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
507 WHERE line_index = g_limit_balance_line(i).line_index
509 g_limit_balance_line(i).list_line_id;
510
511 IF l_debug = FND_API.G_TRUE THEN
512 QP_PREQ_GRP.engine_debug(l_message);
513
514 END IF;
515 END IF;
516
517 --Set the hard_limit_exceeded flag to true. So that the record in
518 --ldets_tmp table is set to deleted status.
519 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
520 RAISE;
521
522 WHEN OTHERS THEN
523 IF l_debug = FND_API.G_TRUE THEN
524 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
525
526 END IF;
527 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
528 THEN
529 FND_MESSAGE.SET_NAME('QP','QP_ERROR_IN_LIMIT_PROCESSING');
530 l_message := FND_MESSAGE.GET;
531
532 --Update the lines_tmp table with the translated error message.
533 /*
534 INDX,qp_limit_balance_check_pvt.recheck_balance.upd3,qp_npreq_lines_tmp_N1,LINE_INDEX,1
535 */
536 --sql statement upd3
537 UPDATE qp_npreq_lines_tmp
538 SET pricing_status_text = l_message,
539 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
540 WHERE line_index = g_limit_balance_line(i).line_index;
541
542 --Update the ldets_tmp table with the translated error message.
543 /*
544 INDX,qp_limit_balance_check_pvt.recheck_balance.upd4,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
545 INDX,qp_limit_balance_check_pvt.recheck_balance.upd4,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
546 */
547 --sql statement upd4
548 UPDATE qp_npreq_ldets_tmp
549 SET pricing_status_text = l_message,
550 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
551 WHERE line_index = g_limit_balance_line(i).line_index
552 AND created_from_list_line_id =
553 g_limit_balance_line(i).list_line_id;
554
555 IF l_debug = FND_API.G_TRUE THEN
556 QP_PREQ_GRP.engine_debug(l_message);
557
558 END IF;
559 END IF;
560
561 --Set the hard_limit_exceeded flag to true. So that the record in
562 --ldets_tmp table is set to deleted status.
563 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
564 RAISE;
565
566 END;--End of Block around Select Stmt when limit does not have each attrs
567
568 ELSIF g_limit_balance_line(i).each_attr_exists = 'Y' THEN
569 IF l_debug = FND_API.G_TRUE THEN
570 QP_PREQ_GRP.engine_debug('Each attr exists');
571 END IF;
572 BEGIN
573
574 IF g_limit_balance_line(i).limit_level_code = 'ACROSS_TRANSACTION' THEN
575 /*
576 INDX,qp_limit_balance_check_pvt.recheck_balance.sel3,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
577 */
578 --sql statement sel3
579 SELECT available_amount
580 INTO l_full_available_amount
581 FROM qp_limit_balances
582 WHERE limit_balance_id = g_limit_balance_line(i).limit_balance_id;
583
584 ELSIF g_limit_balance_line(i).limit_level_code = 'TRANSACTION' THEN
585 /*
586 INDX,qp_limit_balance_check_pvt.recheck_balance.sel4,QP_LIMIT_BALANCES_N1,LIMIT_ID,1
587 INDX,qp_limit_balance_check_pvt.recheck_balance.sel4,QP_LIMIT_BALANCES_N1,PRICE_REQUEST_CODE,2
588 */
589 --sql statement sel4
590 SELECT available_amount
591 INTO l_full_available_amount
592 FROM qp_limit_balances
593 WHERE limit_id = g_limit_balance_line(i).limit_id
594 AND price_request_code =
595 g_limit_balance_line(i).bal_price_request_code;
596
597 END IF; --IF limit_level_code = 'ACROSS_TRANSACTION'
598
599 EXCEPTION
600 WHEN NO_DATA_FOUND THEN
601 l_full_available_amount := g_limit_balance_line(i).limit_amount;
602 g_limit_balance_line(i).process_action := g_insert;
603
604 WHEN TOO_MANY_ROWS THEN
605 IF l_debug = FND_API.G_TRUE THEN
606 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
607
608 END IF;
609 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
610 THEN
611 FND_MESSAGE.SET_NAME('QP','QP_MULT_LIMIT_BALANCES');
612 FND_MESSAGE.SET_TOKEN('LIMIT', g_limit_balance_line(i).limit_id);
613 l_message := FND_MESSAGE.GET;
614
615 --Update the lines_tmp table with the translated error message.
616 /*
617 INDX,qp_limit_balance_check_pvt.recheck_balance.upd5,qp_npreq_lines_tmp_N1,LINE_INDEX,1
618 */
619 --sql statement upd5
620 UPDATE qp_npreq_lines_tmp
621 SET pricing_status_text = l_message,
622 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
623 WHERE line_index = g_limit_balance_line(i).line_index;
624
625 --Update the ldets_tmp table with the translated error message.
626 /*
627 INDX,qp_limit_balance_check_pvt.recheck_balance.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
631 UPDATE qp_npreq_ldets_tmp
628 INDX qp_limit_balance_check_pvt.recheck_balance.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
629 */
630 --sql statement upd6
632 SET pricing_status_text = l_message,
633 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
634 WHERE line_index = g_limit_balance_line(i).line_index
635 AND created_from_list_line_id =
636 g_limit_balance_line(i).list_line_id;
637
638 IF l_debug = FND_API.G_TRUE THEN
639 QP_PREQ_GRP.engine_debug(l_message);
640
641 END IF;
642 END IF;
643
644 --Set the hard_limit_exceeded flag to true. So that the record in
645 --ldets_tmp table is set to deleted status.
646 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
647 RAISE;
648
649 WHEN OTHERS THEN
650 IF l_debug = FND_API.G_TRUE THEN
651 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
652
653 END IF;
654 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
655 THEN
656 FND_MESSAGE.SET_NAME('QP','QP_ERROR_IN_LIMIT_PROCESSING');
657 l_message := FND_MESSAGE.GET;
658
659 --Update the lines_tmp table with the translated error message.
660 /*
661 INDX,qp_limit_balance_check_pvt.recheck_balance.upd7,qp_npreq_lines_tmp_N1,LINE_INDEX,1
662 */
663 --sql statement upd7
664 UPDATE qp_npreq_lines_tmp
665 SET pricing_status_text = l_message,
666 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
667 WHERE line_index = g_limit_balance_line(i).line_index;
668
669 --Update the ldets_tmp table with the translated error message.
670 /*
671 INDX,qp_limit_balance_check_pvt.recheck_balance.upd8,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
672 INDX,qp_limit_balance_check_pvt.recheck_balance.upd8,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
673 */
674 --sql statement upd8
675 UPDATE qp_npreq_ldets_tmp
676 SET pricing_status_text = l_message,
677 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
678 WHERE line_index = g_limit_balance_line(i).line_index
679 AND created_from_list_line_id =
680 g_limit_balance_line(i).list_line_id;
681
682 IF l_debug = FND_API.G_TRUE THEN
683 QP_PREQ_GRP.engine_debug(l_message);
684
685 END IF;
686 END IF;
687
688 --Set the hard_limit_exceeded flag to true. So that the record in
689 --ldets_tmp table is set to deleted status.
690 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
691 RAISE;
692
693 END;--End of Block around Select Stmt when limit has each attrs
694
695 END IF; --If each_attr_exists
696
697 IF l_debug = FND_API.G_TRUE THEN
698 QP_PREQ_GRP.engine_debug('Action to take '||g_limit_balance_line(i).process_action);
699
700 END IF;
701 --Check the Limit Transaction Table to see if the same request has a
702 --record. If so, this is a repricing request so populate the
703 --transaction_amount in the balanceline plsql table for later use.
704 BEGIN
705 /*
706 INDX,qp_limit_balance_check_pvt.recheck_balance.sel5,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
707 INDX,qp_limit_balance_check_pvt.recheck_balance.sel5,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
708 INDX,qp_limit_balance_check_pvt.recheck_balance.sel5,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
709 INDX,qp_limit_balance_check_pvt.recheck_balance.sel5,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
710 */
711 --sql statement sel5
712 SELECT amount
713 INTO g_limit_balance_line(i).transaction_amount
714 FROM qp_limit_transactions
715 WHERE price_request_code = g_limit_balance_line(i).price_request_code
716 AND list_header_id = g_limit_balance_line(i).list_header_id
717 AND list_line_id = g_limit_balance_line(i).list_line_id
718 AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
719 EXCEPTION
720 WHEN NO_DATA_FOUND THEN
721 g_limit_balance_line(i).transaction_amount := null;
722 END;
723
724
725 --Increment the full_available_amount by the transaction_amount
726 l_full_available_amount := l_full_available_amount +
727 nvl(g_limit_balance_line(i).transaction_amount, 0);
728
729 g_limit_balance_line(i).full_available_amount := l_full_available_amount;
730
731 --fix for bug 4765137 to remove the modifier if limit balance is zero or
732 --negative for a hard limit on new or repriced orders
733 IF l_full_available_amount <= 0 AND
734 g_limit_balance_line(i).limit_exceed_action_code = 'HARD'
735 THEN
736 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
737 RETURN FALSE;
738 END IF;
739
740 IF g_limit_balance_line(i).limit_exceed_action_code = 'HARD' THEN
741
742 g_limit_balance_line(i).available_amount :=
743 least(l_full_available_amount, g_limit_balance_line(i).wanted_amount);
744
745 IF g_limit_balance_line(i).wanted_amount > l_full_available_amount THEN
746 g_limit_balance_line(i).limit_code :=
750 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
747 QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
748 ELSE
749 g_limit_balance_line(i).limit_code :=
751 END IF; --if p_wanted_amount > l_full_available_amount
752
753 ELSE --Soft Limit
754
755 g_limit_balance_line(i).available_amount :=
756 g_limit_balance_line(i).wanted_amount;
757
758 IF g_limit_balance_line(i).line_category = 'RETURN' THEN
759
760 g_limit_balance_line(i).limit_code :=
761 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
762
763 ELSE --If line_category is not 'RETURN'
764
765 IF g_limit_balance_line(i).wanted_amount > l_full_available_amount
766 THEN
767 g_limit_balance_line(i).limit_code :=
768 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
769 ELSE
770 g_limit_balance_line(i).limit_code :=
771 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
772 END IF; --If wanted_amount > l_full_available_amount
773
774 END IF;--If line_category is 'RETURN'
775
776 END IF;--Hard or Soft limit
777
778 IF g_limit_balance_line(i).wanted_amount <> 0 THEN
779 g_limit_balance_line(i).available_percent :=
780 ABS(g_limit_balance_line(i).available_amount/
781 g_limit_balance_line(i).wanted_amount) * 100;
782 ELSE
783 g_limit_balance_line(i).available_percent := 100;
784 END IF;
785
786 g_limit_balance_line(i).hard_limit_exceeded := FALSE;
787 RETURN TRUE;
788
789 END LOOP; --over the g_limit_balance_lines
790
791 END IF; --g_limit_balance_line.COUNT > 0
792
793 IF l_debug = FND_API.G_TRUE THEN
794 QP_PREQ_GRP.engine_debug('***End Recheck_Balance***');
795
796 END IF;
797 EXCEPTION
798 WHEN OTHERS THEN
799 IF l_debug = FND_API.G_TRUE THEN
800 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
801 END IF;
802 RETURN FALSE;
803 END Recheck_Balance;
804
805
806 /**********************************************************************
807 Utility Function to Populate a pl/sql table with information on the
808 the Limit, Limit Balance, etc. so that the main procedure Process_Limits
809 can process the limits. Returns TRUE/FALSE if limit is available/not.
810 ***********************************************************************/
811 FUNCTION Check_Balance(p_limit_rec IN Limit_Rec,
812 p_wanted_amount IN NUMBER,
813 x_skip_limit OUT NOCOPY BOOLEAN)
814 RETURN BOOLEAN
815 IS
816
817 l_limit_balance_line Limit_Balance_Line_Tbl;
818 i INTEGER;
819 l_full_available_amount NUMBER;
820 l_message VARCHAR2(240);
821
822 /*
823 INDX,qp_limit_balance_check_pvt.check_balance.request_attr_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
824 INDX,qp_limit_balance_check_pvt.check_balance.request_attr_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
825 INDX,qp_limit_balance_check_pvt.check_balance.request_attr_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
826 INDX,qp_limit_balance_check_pvt.check_balance.request_attr_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,6
827 */
828 CURSOR request_attr_cur(a_line_index NUMBER, a_context VARCHAR2,
829 a_attribute VARCHAR2)
830 IS
831 SELECT context, attribute, value_from, attribute_type, datatype
832 FROM qp_npreq_line_attrs_tmp
833 WHERE line_index = a_line_index
834 AND context = a_context
835 AND attribute = a_attribute
836 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
837
838 TYPE each_attr_rec_type IS RECORD
839 (context VARCHAR2(30),
840 attribute VARCHAR2(30),
841 value VARCHAR2(240)
842 );
843
844 TYPE each_attr_table_type IS TABLE OF each_attr_rec_type
845 INDEX BY BINARY_INTEGER;
846
847 l_org_table each_attr_table_type;
848 l_cust_table each_attr_table_type;
849 l_item_table each_attr_table_type;
850
851 i1 NUMBER := 1;
852 i2 NUMBER := 1;
853 i3 NUMBER := 1;
854
855 E_ORDER_PRICE_REQ_CODE_NULL EXCEPTION;
856
857 BEGIN
858
859 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
860 IF l_debug = FND_API.G_TRUE THEN
861 QP_PREQ_GRP.engine_debug('***Begin Check_Balance***');
862 END IF;
863 IF g_Limit_balance_line.COUNT = 0 THEN
864 i:=1;
865 ELSE
866 i := g_limit_balance_line.LAST + 1;
867 END IF;
868 x_skip_limit := FALSE;
869
870 IF l_debug = FND_API.G_TRUE THEN
871 QP_PREQ_GRP.engine_debug('populating limit_balance_line '||i);
872 QP_PREQ_GRP.engine_debug('limit_id '||p_limit_rec.limit_id);
873
874 END IF;
875 IF p_limit_rec.each_attr_exists = 'N' THEN
876 --If limit has no each (pure non-each) or no attributes
877 --(For such cases a balance record will always exist since setup creates one)
878 IF l_debug = FND_API.G_TRUE THEN
879 QP_PREQ_GRP.engine_debug('No Each-type attributes defined for this limit');
880
881 END IF;
882 g_limit_balance_line(i).limit_id := p_limit_rec.limit_id;
883 g_limit_balance_line(i).line_index := p_limit_rec.line_index;
884 g_limit_balance_line(i).list_header_id :=
885 p_limit_rec.created_from_list_header_id;
889 g_limit_balance_line(i).each_attr_exists := p_limit_rec.each_attr_exists;
886 g_limit_balance_line(i).list_line_id :=
887 p_limit_rec.created_from_list_line_id;
888 g_limit_balance_line(i).wanted_amount := p_wanted_amount;
890 g_limit_balance_line(i).limit_amount := p_limit_rec.amount;
891 g_limit_balance_line(i).basis := p_limit_rec.basis;
892 g_limit_balance_line(i).limit_exceed_action_code :=
893 p_limit_rec.limit_exceed_action_code;
894 g_limit_balance_line(i).adjustment_amount := p_limit_rec.adjustment_amount;
895 g_limit_balance_line(i).operand_value := p_limit_rec.operand_value;
896 g_limit_balance_line(i).benefit_qty := p_limit_rec.benefit_qty;
897 g_limit_balance_line(i).created_from_list_line_type :=
898 p_limit_rec.created_from_list_line_type;
899 g_limit_balance_line(i).pricing_group_sequence :=
900 p_limit_rec.pricing_group_sequence;
901 g_limit_balance_line(i).operand_calculation_code :=
902 p_limit_rec.operand_calculation_code;
903 g_limit_balance_line(i).limit_level := p_limit_rec.limit_level;
904 g_limit_balance_line(i).limit_hold_flag := p_limit_rec.limit_hold_flag;
905 --Populate bal_price_request_code for limit_balances table
906 IF p_limit_rec.limit_level_code = 'ACROSS_TRANSACTION' THEN
907 g_limit_balance_line(i).bal_price_request_code := NULL;
908 ELSIF p_limit_rec.limit_level_code = 'TRANSACTION' THEN
909 g_limit_balance_line(i).bal_price_request_code :=
910 QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE;
911 END IF; --If limit_level_code = 'ACROSS_TRANSACTION'
912 IF l_debug = FND_API.G_TRUE THEN
913 QP_PREQ_GRP.engine_debug('bal_price_request_code '||
914 g_limit_balance_line(i).bal_price_request_code);
915 END IF;
916 --Populate price_request_code from limits_cur for limit_transns table
917 g_limit_balance_line(i).price_request_code :=
918 p_limit_rec.price_request_code;
919 g_limit_balance_line(i).request_type_code := p_limit_rec.request_type_code;
920 g_limit_balance_line(i).line_category := p_limit_rec.line_category;
921 g_limit_balance_line(i).pricing_phase_id := p_limit_rec.pricing_phase_id;
922 g_limit_balance_line(i).limit_level_code := p_limit_rec.limit_level_code;
923 g_limit_balance_line(i).line_detail_index := p_limit_rec.line_detail_index;
924
925 BEGIN
926
927 IF p_limit_rec.limit_level_code = 'ACROSS_TRANSACTION' THEN
928 /*
929 INDX,qp_limit_balance_check_pvt.check_balance.sel1,QP_LIMIT_BALANCES_U2,LIMIT_ID,1
930 */
931 --sql statement sel1
932 SELECT available_amount, limit_balance_id
933 INTO l_full_available_amount, g_limit_balance_line(i).limit_balance_id
934 FROM qp_limit_balances
935 WHERE limit_id = p_limit_rec.limit_id;
936
937 ELSIF p_limit_rec.limit_level_code = 'TRANSACTION' THEN
938 /*
939 INDX,qp_limit_balance_check_pvt.check_balance.sel2,QP_LIMIT_BALANCES_N1,LIMIT_ID,1
940 INDX,qp_limit_balance_check_pvt.check_balance.sel2,QP_LIMIT_BALANCES_N1,PRICE_REQUEST_CODE,2
941 */
942
943 --If g_order_price_request_code is null then raise an error.
944 IF QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE IS NULL THEN
945 RAISE E_ORDER_PRICE_REQ_CODE_NULL;
946 END IF;
947
948 --sql statement sel2
949 SELECT available_amount, limit_balance_id
950 INTO l_full_available_amount, g_limit_balance_line(i).limit_balance_id
951 FROM qp_limit_balances
952 WHERE limit_id = p_limit_rec.limit_id
953 AND price_request_code =
954 g_limit_balance_line(i).bal_price_request_code;
955
956 END IF; --If limit_level_code is 'ACROSS_TRANSACTION'
957
958 g_limit_balance_line(i).process_action := g_update;
959
960 IF l_debug = FND_API.G_TRUE THEN
961 QP_PREQ_GRP.engine_debug('Available Balance '|| l_full_available_amount);
962
963 END IF;
964 EXCEPTION
965 WHEN NO_DATA_FOUND THEN
966 select qp_limit_balances_s.nextval
967 into g_limit_balance_line(i).limit_balance_id from dual;
968 l_full_available_amount := p_limit_rec.amount;
969 g_limit_balance_line(i).process_action := g_insert;
970 IF l_debug = FND_API.G_TRUE THEN
971 QP_PREQ_GRP.engine_debug('Balance Record Missing');
972
973 END IF;
974 WHEN E_ORDER_PRICE_REQ_CODE_NULL THEN
975
976 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
977 THEN
978 FND_MESSAGE.SET_NAME('QP','QP_ORDER_PRICE_REQ_CODE_NULL');
979 l_message := FND_MESSAGE.GET;
980
981 --Update the lines_tmp table with the translated error message.
982 /*
983 INDX,qp_limit_balance_check_pvt.check_balance.upd9,qp_npreq_lines_tmp_N1,LINE_INDEX,1
984 */
985 --sql statement upd9
986 UPDATE qp_npreq_lines_tmp
987 SET pricing_status_text = l_message,
988 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
989 WHERE line_index = g_limit_balance_line(i).line_index;
990
991 --Update the ldets_tmp table with the translated error message.
992 /*
993 INDX,qp_limit_balance_check_pvt.check_balance.upd10,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
994 INDX,qp_limit_balance_check_pvt.check_balance.upd10,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
995 */
999 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
996 --sql statement upd10
997 UPDATE qp_npreq_ldets_tmp
998 SET pricing_status_text = l_message,
1000 WHERE line_index = g_limit_balance_line(i).line_index
1001 AND created_from_list_line_id =
1002 g_limit_balance_line(i).list_line_id;
1003
1004 IF l_debug = FND_API.G_TRUE THEN
1005 QP_PREQ_GRP.engine_debug(l_message);
1006
1007 END IF;
1008 END IF;
1009
1010 WHEN TOO_MANY_ROWS THEN
1011 IF l_debug = FND_API.G_TRUE THEN
1012 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1013
1014 END IF;
1015 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1016 THEN
1017 FND_MESSAGE.SET_NAME('QP','QP_MULT_LIMIT_BALANCES');
1018 FND_MESSAGE.SET_TOKEN('LIMIT', g_limit_balance_line(i).limit_id);
1019 l_message := FND_MESSAGE.GET;
1020
1021 --Update the lines_tmp table with the translated error message.
1022 /*
1023 INDX,qp_limit_balance_check_pvt.check_balance.upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1024 */
1025 --sql statement upd1
1026 UPDATE qp_npreq_lines_tmp
1027 SET pricing_status_text = l_message,
1028 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1029 WHERE line_index = g_limit_balance_line(i).line_index;
1030
1031 --Update the ldets_tmp table with the translated error message.
1032 /*
1033 INDX,qp_limit_balance_check_pvt.check_balance.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1034 INDX,qp_limit_balance_check_pvt.check_balance.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1035 */
1036 --sql statement upd2
1037 UPDATE qp_npreq_ldets_tmp
1038 SET pricing_status_text = l_message,
1039 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1040 WHERE line_index = g_limit_balance_line(i).line_index
1041 AND created_from_list_line_id =
1042 g_limit_balance_line(i).list_line_id;
1043
1044 IF l_debug = FND_API.G_TRUE THEN
1045 QP_PREQ_GRP.engine_debug(l_message);
1046
1047 END IF;
1048 END IF;
1049
1050 --Set the hard_limit_exceeded flag to true. So that the record in
1051 --ldets_tmp table is set to deleted status.
1052 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
1053 RAISE;
1054
1055 WHEN OTHERS THEN
1056 IF l_debug = FND_API.G_TRUE THEN
1057 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1058
1059 END IF;
1060 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1061 THEN
1062 FND_MESSAGE.SET_NAME('QP','QP_ERROR_IN_LIMIT_PROCESSING');
1063 l_message := FND_MESSAGE.GET;
1064 l_message := substr(ltrim(rtrim(l_message))||l_message, 1, 2000);
1065 --Update the lines_tmp table with the translated error message.
1066 /*
1067 INDX,qp_limit_balance_check_pvt.check_balance.upd3,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1068 */
1069 --sql statement upd3
1070 UPDATE qp_npreq_lines_tmp
1071 SET pricing_status_text = l_message,
1072 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1073 WHERE line_index = g_limit_balance_line(i).line_index;
1074
1075 --Update the ldets_tmp table with the translated error message.
1076 /*
1077 INDX,qp_limit_balance_check_pvt.check_balance.upd4,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1078 INDX,qp_limit_balance_check_pvt.check_balance.upd4,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1079 */
1080 --sql statement upd4
1081 UPDATE qp_npreq_ldets_tmp
1082 SET pricing_status_text = l_message,
1083 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1084 WHERE line_index = g_limit_balance_line(i).line_index
1085 AND created_from_list_line_id =
1086 g_limit_balance_line(i).list_line_id;
1087
1088 IF l_debug = FND_API.G_TRUE THEN
1089 QP_PREQ_GRP.engine_debug(l_message);
1090
1091 END IF;
1092 END IF;
1093
1094 --Set the hard_limit_exceeded flag to true. So that the record in
1095 --ldets_tmp table is set to deleted status.
1096 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
1097 RAISE;
1098
1099 END;--Block around select stmt when no each attr exists
1100
1101 --Check the Limit Transaction Table to see if the same request has a
1102 --record. If so, this is a repricing request so populate the
1103 --transaction_amount in the balanceline plsql table for later use.
1104 BEGIN
1105 /*
1106 INDX,qp_limit_balance_check_pvt.check_balance.sel3,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
1107 INDX,qp_limit_balance_check_pvt.check_balance.sel3,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
1108 INDX,qp_limit_balance_check_pvt.check_balance.sel3,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
1109 INDX,qp_limit_balance_check_pvt.check_balance.sel3,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
1110 */
1111 --sql statement sel3
1112 SELECT amount
1113 INTO g_limit_balance_line(i).transaction_amount
1114 FROM qp_limit_transactions
1115 WHERE price_request_code = p_limit_rec.price_request_code
1116 AND list_header_id = p_limit_rec.created_from_list_header_id
1120 WHEN NO_DATA_FOUND THEN
1117 AND list_line_id = p_limit_rec.created_from_list_line_id
1118 AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
1119 EXCEPTION
1121 g_limit_balance_line(i).transaction_amount := null;
1122 END;
1123
1124
1125 --Increment the full_available_amount by the transaction_amount
1126 l_full_available_amount := l_full_available_amount +
1127 nvl(g_limit_balance_line(i).transaction_amount, 0);
1128
1129 g_limit_balance_line(i).full_available_amount := l_full_available_amount;
1130
1131 --fix for bug 4765137 to remove the modifier if limit balance is zero or
1132 --negative for a hard limit on new or repriced orders
1133 IF l_full_available_amount <= 0 AND
1134 p_limit_rec.limit_exceed_action_code = 'HARD'
1135 THEN
1136 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
1137 RETURN FALSE;
1138 END IF;
1139
1140 IF p_limit_rec.limit_exceed_action_code = 'HARD' THEN
1141
1142 g_limit_balance_line(i).available_amount :=
1143 least(l_full_available_amount, p_wanted_amount);
1144
1145 IF p_wanted_amount > l_full_available_amount THEN
1146 g_limit_balance_line(i).limit_code :=
1147 QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
1148 ELSE
1149 g_limit_balance_line(i).limit_code :=
1150 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1151 END IF; --if p_wanted_amount > l_full_available_amount
1152
1153 ELSE --Soft Limit
1154
1155 g_limit_balance_line(i).available_amount := p_wanted_amount;
1156
1157 IF g_limit_balance_line(i).line_category = 'RETURN' THEN
1158
1159 g_limit_balance_line(i).limit_code :=
1160 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1161
1162 ELSE--If line_category is not 'RETURN'
1163
1164 IF p_wanted_amount > l_full_available_amount
1165 THEN
1166 g_limit_balance_line(i).limit_code :=
1167 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
1168 ELSE
1169 g_limit_balance_line(i).limit_code :=
1170 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1171 END IF; --If wanted_amount > l_full_available_amount
1172
1173 END IF;--If line_category is 'RETURN'
1174
1175 END IF; --If limit_exceed_action_code = 'HARD'
1176
1177 IF l_debug = FND_API.G_TRUE THEN
1178 QP_PREQ_GRP.engine_debug('p_wanted_amount '||p_wanted_amount);
1179 QP_PREQ_GRP.engine_debug('available_amount '||
1180 g_limit_balance_line(i).available_amount);
1181
1182 END IF;
1183 IF p_wanted_amount <> 0 THEN
1184 g_limit_balance_line(i).available_percent :=
1185 ABS(g_limit_balance_line(i).available_amount/p_wanted_amount)*100;
1186 ELSE
1187 g_limit_balance_line(i).available_percent := 100;
1188 END IF;
1189
1190 IF l_debug = FND_API.G_TRUE THEN
1191 QP_PREQ_GRP.engine_debug('available_percent '||
1192 g_limit_balance_line(i).available_percent);
1193 END IF;
1194
1195 g_limit_balance_line(i).hard_limit_exceeded := FALSE;
1196 IF l_debug = FND_API.G_TRUE THEN
1197 QP_PREQ_GRP.engine_debug('***End Check_Balance***');
1198 END IF;
1199 RETURN TRUE;
1200
1201 ELSIF p_limit_rec.each_attr_exists = 'Y' THEN
1202 --Mixed case where both Each and Non-Each Attributes exist for the limit
1203 --and pure Each case
1204 IF l_debug = FND_API.G_TRUE THEN
1205 QP_PREQ_GRP.engine_debug('Each-type attributes defined for this limit');
1206 END IF;
1207
1208 --Fetch all the org, customer and item type context,attribute and value
1209 --combinations for the current list_line from the request line attrs table.
1210 FOR l_org_rec IN request_attr_cur(p_limit_rec.line_index,
1211 p_limit_rec.organization_attr_context,
1212 p_limit_rec.organization_attribute)
1213 LOOP
1214 l_org_table(i1).context := l_org_rec.context;
1215 l_org_table(i1).attribute := l_org_rec.attribute;
1216 l_org_table(i1).value := l_org_rec.value_from;
1217 i1 := i1+1;
1218 END LOOP;
1219
1220 FOR l_cust_rec IN request_attr_cur(p_limit_rec.line_index,
1221 p_limit_rec.multival_attr1_context,
1222 p_limit_rec.multival_attribute1)
1223 LOOP
1224 l_cust_table(i2).context := l_cust_rec.context;
1225 l_cust_table(i2).attribute := l_cust_rec.attribute;
1226 l_cust_table(i2).value := l_cust_rec.value_from;
1227 i2 := i2+1;
1228 END LOOP;
1229
1230 FOR l_item_rec IN request_attr_cur(p_limit_rec.line_index,
1231 p_limit_rec.multival_attr2_context,
1232 p_limit_rec.multival_attribute2)
1233 LOOP
1234 l_item_table(i3).context := l_item_rec.context;
1235 l_item_table(i3).attribute := l_item_rec.attribute;
1236 l_item_table(i3).value := l_item_rec.value_from;
1237 i3 := i3+1;
1238 END LOOP;
1239
1240 IF l_debug = FND_API.G_TRUE THEN
1241 QP_PREQ_GRP.engine_debug('Organization Attr Count '|| l_org_table.count);
1242 QP_PREQ_GRP.engine_debug('Attribute1 Count '|| l_cust_table.count);
1246 --If for any of org, customer and item limit attribute setup for 'EACH'
1243 QP_PREQ_GRP.engine_debug('Attribute2 Count '|| l_item_table.count);
1244
1245 END IF;
1247 --value doesn't have any corresponding records from the request attrs table
1248 --mark limit to be skipped and return from check_balance function.
1249 IF p_limit_rec.organization_attr_context <> 'NA' AND
1250 l_org_table.COUNT = 0
1251 OR p_limit_rec.multival_attr1_context <> 'NA' AND
1252 l_cust_table.COUNT = 0
1253 OR p_limit_rec.multival_attr2_context <> 'NA' AND
1254 l_item_table.COUNT = 0
1255 THEN
1256 IF l_debug = FND_API.G_TRUE THEN
1257 QP_PREQ_GRP.engine_debug('Skipping Limit '|| p_limit_rec.limit_id);
1258 END IF;
1259 x_skip_limit := TRUE;
1260 RETURN TRUE;
1261 END IF;
1262
1263 --If an each attribute is not defined in limit and count of request line
1264 --attributes = 0 then insert a dummy record to enable cartesian product.
1265 IF p_limit_rec.organization_attr_context = 'NA' AND
1266 l_org_table.COUNT=0
1267 THEN
1268 l_org_table(1).context := 'NA';
1269 l_org_table(1).attribute := 'NA';
1270 l_org_table(1).value := 'NA';
1271 END IF;
1272
1273 IF p_limit_rec.multival_attr1_context = 'NA' AND
1274 l_cust_table.COUNT=0
1275 THEN
1276 l_cust_table(1).context := 'NA';
1277 l_cust_table(1).attribute := 'NA';
1278 l_cust_table(1).value := 'NA';
1279 END IF;
1280
1281 IF p_limit_rec.multival_attr2_context = 'NA' AND
1282 l_item_table.COUNT=0
1283 THEN
1284 l_item_table(1).context := 'NA';
1285 l_item_table(1).attribute := 'NA';
1286 l_item_table(1).value := 'NA';
1287 END IF;
1288
1289 FOR j IN l_org_table.FIRST..l_org_table.LAST
1290 LOOP
1291 FOR k IN l_cust_table.FIRST..l_cust_table.LAST
1292 LOOP
1293 FOR m IN l_item_table.FIRST..l_item_table.LAST
1294 LOOP
1295 g_limit_balance_line(i).limit_id := p_limit_rec.limit_id;
1296 g_limit_balance_line(i).line_index := p_limit_rec.line_index;
1297 g_limit_balance_line(i).list_header_id :=
1298 p_limit_rec.created_from_list_header_id;
1299 g_limit_balance_line(i).list_line_id :=
1300 p_limit_rec.created_from_list_line_id;
1301 g_limit_balance_line(i).wanted_amount := p_wanted_amount;
1302 g_limit_balance_line(i).each_attr_exists :=
1303 p_limit_rec.each_attr_exists;
1304 g_limit_balance_line(i).limit_amount := p_limit_rec.amount;
1305 g_limit_balance_line(i).basis := p_limit_rec.basis;
1306 g_limit_balance_line(i).limit_exceed_action_code :=
1307 p_limit_rec.limit_exceed_action_code;
1308 g_limit_balance_line(i).adjustment_amount :=
1309 p_limit_rec.adjustment_amount;
1310 g_limit_balance_line(i).operand_value :=
1311 p_limit_rec.operand_value;
1312 g_limit_balance_line(i).benefit_qty := p_limit_rec.benefit_qty;
1313 g_limit_balance_line(i).created_from_list_line_type :=
1314 p_limit_rec.created_from_list_line_type;
1315 g_limit_balance_line(i).pricing_group_sequence :=
1316 p_limit_rec.pricing_group_sequence;
1317 g_limit_balance_line(i).operand_calculation_code :=
1318 p_limit_rec.operand_calculation_code;
1319 g_limit_balance_line(i).limit_level := p_limit_rec.limit_level;
1320 g_limit_balance_line(i).limit_hold_flag :=
1321 p_limit_rec.limit_hold_flag;
1322 --Populate bal_price_request_code for limit_balances table
1323 IF p_limit_rec.limit_level_code = 'ACROSS_TRANSACTION' THEN
1324 g_limit_balance_line(i).bal_price_request_code := NULL;
1325 ELSIF p_limit_rec.limit_level_code = 'TRANSACTION' THEN
1326 g_limit_balance_line(i).bal_price_request_code :=
1327 QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE;
1328 END IF;--If limit_level_code = 'ACROSS_TRANSACTION'
1329 IF l_debug = FND_API.G_TRUE THEN
1330 QP_PREQ_GRP.engine_debug('bal_price_request_code '||
1331 g_limit_balance_line(i).bal_price_request_code);
1332 END IF;
1333 --Populate price_request_code for limit_transactions table
1334 g_limit_balance_line(i).price_request_code :=
1335 p_limit_rec.price_request_code;
1336 g_limit_balance_line(i).request_type_code :=
1337 p_limit_rec.request_type_code;
1338 g_limit_balance_line(i).line_category := p_limit_rec.line_category;
1339 g_limit_balance_line(i).pricing_phase_id :=
1340 p_limit_rec.pricing_phase_id;
1341 g_limit_balance_line(i).limit_level_code :=
1342 p_limit_rec.limit_level_code;
1343 g_limit_balance_line(i).line_detail_index :=
1344 p_limit_rec.line_detail_index;
1345
1346 BEGIN
1347 IF p_limit_rec.limit_level_code = 'ACROSS_TRANSACTION' THEN
1348
1349 /*
1350 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,LIMIT_ID,1
1351 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,ORGANIZATION_ATTR_CONTEXT,2
1352 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,ORGANIZATION_ATTRIBUTE,3
1356 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR1_VALUE,7
1353 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,ORGANIZATION_ATTR_VALUE,4
1354 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR1_CONTEXT,5
1355 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTRIBUTE1,6
1357 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR2_CONTEXT,8
1358 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTRIBUTE2,9
1359 INDX,qp_limit_balance_check_pvt.check_balance.sel4,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR2_VALUE,10
1360 */
1361 --sql statement sel4
1362 SELECT available_amount, limit_balance_id,
1363 organization_attr_context, organization_attribute,
1364 organization_attr_value,
1365 multival_attr1_context, multival_attribute1,
1366 multival_attr1_value, multival_attr1_type,
1367 multival_attr1_datatype,
1368 multival_attr2_context, multival_attribute2,
1369 multival_attr2_value, multival_attr2_type,
1370 multival_attr2_datatype
1371 INTO l_full_available_amount,
1372 g_limit_balance_line(i).limit_balance_id,
1373 g_limit_balance_line(i).organization_attr_context,
1374 g_limit_balance_line(i).organization_attribute,
1375 g_limit_balance_line(i).organization_attr_value,
1376 g_limit_balance_line(i).multival_attr1_context,
1377 g_limit_balance_line(i).multival_attribute1,
1378 g_limit_balance_line(i).multival_attr1_value,
1379 g_limit_balance_line(i).multival_attr1_type,
1380 g_limit_balance_line(i).multival_attr1_datatype,
1381 g_limit_balance_line(i).multival_attr2_context,
1382 g_limit_balance_line(i).multival_attribute2,
1383 g_limit_balance_line(i).multival_attr2_value,
1384 g_limit_balance_line(i).multival_attr2_type,
1385 g_limit_balance_line(i).multival_attr2_datatype
1386 FROM qp_limit_balances
1387 WHERE limit_id = p_limit_rec.limit_id
1388 AND organization_attr_context = l_org_table(j).context
1389 AND organization_attribute = l_org_table(j).attribute
1390 AND organization_attr_value = l_org_table(j).value
1391 AND multival_attr1_context = l_cust_table(k).context
1392 AND multival_attribute1 = l_cust_table(k).attribute
1393 AND multival_attr1_value = l_cust_table(k).value
1394 AND multival_attr2_context = l_item_table(m).context
1395 AND multival_attribute2 = l_item_table(m).attribute
1396 AND multival_attr2_value = l_item_table(m).value;
1397
1398 ELSIF p_limit_rec.limit_level_code = 'TRANSACTION' THEN
1399
1400 /*
1401 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,LIMIT_ID,1
1402 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,ORGANIZATION_ATTR_CONTEXT,2
1403 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,ORGANIZATION_ATTRIBUTE,3
1404 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,ORGANIZATION_ATTR_VALUE,4
1405 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR1_CONTEXT,5
1406 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTRIBUTE1,6
1407 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR1_VALUE,7
1408 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR2_CONTEXT,8
1409 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTRIBUTE2,9
1410 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR2_VALUE,10
1411 INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,PRICE_REQUEST_CODE,11
1412 */
1413 --If g_order_price_request_code is null then raise an error.
1414 IF QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE IS NULL THEN
1415 RAISE E_ORDER_PRICE_REQ_CODE_NULL;
1416 END IF;
1417
1418 --sql statement sel5
1419 SELECT available_amount, limit_balance_id,
1420 organization_attr_context, organization_attribute,
1421 organization_attr_value,
1422 multival_attr1_context, multival_attribute1,
1423 multival_attr1_value, multival_attr1_type,
1424 multival_attr1_datatype,
1425 multival_attr2_context, multival_attribute2,
1426 multival_attr2_value, multival_attr2_type,
1427 multival_attr2_datatype
1428 INTO l_full_available_amount,
1429 g_limit_balance_line(i).limit_balance_id,
1430 g_limit_balance_line(i).organization_attr_context,
1431 g_limit_balance_line(i).organization_attribute,
1432 g_limit_balance_line(i).organization_attr_value,
1433 g_limit_balance_line(i).multival_attr1_context,
1434 g_limit_balance_line(i).multival_attribute1,
1435 g_limit_balance_line(i).multival_attr1_value,
1436 g_limit_balance_line(i).multival_attr1_type,
1437 g_limit_balance_line(i).multival_attr1_datatype,
1441 g_limit_balance_line(i).multival_attr2_type,
1438 g_limit_balance_line(i).multival_attr2_context,
1439 g_limit_balance_line(i).multival_attribute2,
1440 g_limit_balance_line(i).multival_attr2_value,
1442 g_limit_balance_line(i).multival_attr2_datatype
1443 FROM qp_limit_balances
1444 WHERE limit_id = p_limit_rec.limit_id
1445 AND organization_attr_context = l_org_table(j).context
1446 AND organization_attribute = l_org_table(j).attribute
1447 AND organization_attr_value = l_org_table(j).value
1448 AND multival_attr1_context = l_cust_table(k).context
1449 AND multival_attribute1 = l_cust_table(k).attribute
1450 AND multival_attr1_value = l_cust_table(k).value
1451 AND multival_attr2_context = l_item_table(m).context
1452 AND multival_attribute2 = l_item_table(m).attribute
1453 AND multival_attr2_value = l_item_table(m).value
1454 AND price_request_code =
1455 g_limit_balance_line(i).bal_price_request_code;
1456
1457 END IF;--If limit_level_code = 'ACROSS_TRANSACTION'
1458
1459 g_limit_balance_line(i).process_action := g_update;
1460
1461 IF l_debug = FND_API.G_TRUE THEN
1462 QP_PREQ_GRP.engine_debug('Available Balance '|| l_full_available_amount);
1463 END IF;
1464 EXCEPTION
1465 WHEN NO_DATA_FOUND THEN
1466
1467 select qp_limit_balances_s.nextval
1468 into g_limit_balance_line(i).limit_balance_id from dual;
1469
1470 g_limit_balance_line(i).organization_attr_context
1471 := l_org_table(j).context;
1472 g_limit_balance_line(i).organization_attribute
1473 := l_org_table(j).attribute;
1474 g_limit_balance_line(i).organization_attr_value
1475 := l_org_table(j).value;
1476
1477 g_limit_balance_line(i).multival_attr1_context
1478 := l_cust_table(k).context;
1479 g_limit_balance_line(i).multival_attribute1
1480 := l_cust_table(k).attribute;
1481 g_limit_balance_line(i).multival_attr1_value
1482 := l_cust_table(k).value;
1483 g_limit_balance_line(i).multival_attr1_type
1484 := p_limit_rec.multival_attr1_type;
1485 g_limit_balance_line(i).multival_attr1_datatype
1486 := p_limit_rec.multival_attr1_datatype;
1487
1488 g_limit_balance_line(i).multival_attr2_context
1489 := l_item_table(m).context;
1490 g_limit_balance_line(i).multival_attribute2
1491 := l_item_table(m).attribute;
1492 g_limit_balance_line(i).multival_attr2_value
1493 := l_item_table(m).value;
1494 g_limit_balance_line(i).multival_attr2_type
1495 := p_limit_rec.multival_attr2_type;
1496 g_limit_balance_line(i).multival_attr2_datatype
1497 := p_limit_rec.multival_attr2_datatype;
1498
1499 l_full_available_amount := p_limit_rec.amount;
1500 g_limit_balance_line(i).process_action := g_insert;
1501
1502 WHEN E_ORDER_PRICE_REQ_CODE_NULL THEN
1503
1504 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1505 THEN
1506 FND_MESSAGE.SET_NAME('QP','QP_ORDER_PRICE_REQ_CODE_NULL');
1507 l_message := FND_MESSAGE.GET;
1508
1509 --Update the lines_tmp table with the translated error message.
1510 /*
1511 INDX,qp_limit_balance_check_pvt.check_balance.upd11,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1512 */
1513 --sql statement upd11
1514 UPDATE qp_npreq_lines_tmp
1515 SET pricing_status_text = l_message,
1516 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1517 WHERE line_index = g_limit_balance_line(i).line_index;
1518
1519 --Update the ldets_tmp table with the translated error message.
1520 /*
1521 INDX,qp_limit_balance_check_pvt.check_balance.upd12,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1522 INDX,qp_limit_balance_check_pvt.check_balance.upd12,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1523 */
1524 --sql statement upd12
1525 UPDATE qp_npreq_ldets_tmp
1526 SET pricing_status_text = l_message,
1527 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1528 WHERE line_index = g_limit_balance_line(i).line_index
1529 AND created_from_list_line_id =
1530 g_limit_balance_line(i).list_line_id;
1531
1532 IF l_debug = FND_API.G_TRUE THEN
1533 QP_PREQ_GRP.engine_debug(l_message);
1534
1535 END IF;
1536 END IF;
1537
1538 WHEN TOO_MANY_ROWS THEN
1539 IF l_debug = FND_API.G_TRUE THEN
1540 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1541
1542 END IF;
1543 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1544 THEN
1545 FND_MESSAGE.SET_NAME('QP','QP_MULT_LIMIT_BALANCES');
1546 FND_MESSAGE.SET_TOKEN('LIMIT', g_limit_balance_line(i).limit_id);
1547 l_message := FND_MESSAGE.GET;
1548
1552 */
1549 --Update the lines_tmp table with the translated error message.
1550 /*
1551 INDX,qp_limit_balance_check_pvt.check_balance.upd5,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1553 --sql statement upd5
1554 UPDATE qp_npreq_lines_tmp
1555 SET pricing_status_text = l_message,
1556 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1557 WHERE line_index = g_limit_balance_line(i).line_index;
1558
1559 --Update the ldets_tmp table with the translated error message.
1560 /*
1561 INDX,qp_limit_balance_check_pvt.check_balance.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1562 INDX,qp_limit_balance_check_pvt.check_balance.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1563 */
1564 --sql statement upd6
1565 UPDATE qp_npreq_ldets_tmp
1566 SET pricing_status_text = l_message,
1567 pricing_status_code =
1568 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1569 WHERE line_index = g_limit_balance_line(i).line_index
1570 AND created_from_list_line_id =
1571 g_limit_balance_line(i).list_line_id;
1572
1573 IF l_debug = FND_API.G_TRUE THEN
1574 QP_PREQ_GRP.engine_debug(l_message);
1575
1576 END IF;
1577 END IF;
1578
1579 --Set the hard_limit_exceeded flag to true. So that the record in
1580 --ldets_tmp table is set to deleted status.
1581 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
1582 RAISE;
1583
1584 WHEN OTHERS THEN
1585 IF l_debug = FND_API.G_TRUE THEN
1586 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1587
1588 END IF;
1589 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1590 THEN
1591 FND_MESSAGE.SET_NAME('QP','QP_ERROR_IN_LIMIT_PROCESSING');
1592 l_message := FND_MESSAGE.GET;
1593 l_message := substr(ltrim(rtrim(l_message))||l_message, 1, 2000);
1594
1595 --Update the lines_tmp table with the translated error message.
1596 /*
1597 INDX,qp_limit_balance_check_pvt.check_balance.upd7,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1598 */
1599 --sql statement upd7
1600 UPDATE qp_npreq_lines_tmp
1601 SET pricing_status_text = l_message,
1602 pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1603 WHERE line_index = g_limit_balance_line(i).line_index;
1604
1605 --Update the ldets_tmp table with the translated error message.
1606 /*
1607 INDX,qp_limit_balance_check_pvt.check_balance.upd8,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1608 INDX,qp_limit_balance_check_pvt.check_balance.upd8,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1609 */
1610 --sql statement upd8
1611 UPDATE qp_npreq_ldets_tmp
1612 SET pricing_status_text = l_message,
1613 pricing_status_code =
1614 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1615 WHERE line_index = g_limit_balance_line(i).line_index
1616 AND created_from_list_line_id =
1617 g_limit_balance_line(i).list_line_id;
1618
1619 IF l_debug = FND_API.G_TRUE THEN
1620 QP_PREQ_GRP.engine_debug(l_message);
1621
1622 END IF;
1623 END IF;
1624
1625 --Set the hard_limit_exceeded flag to true. So that the record in
1626 --ldets_tmp table is set to deleted status.
1627 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
1628 RAISE;
1629
1630 END;--Block around select stmt when no each attr exists
1631
1632 --Check the Limit Transaction Table to see if the same request has a
1633 --record. If so, this is a repricing request so populate the
1634 --transaction_amount in the balanceline plsql table for later use.
1635 BEGIN
1636 /*
1637 INDX,qp_limit_balance_check_pvt.check_balance.sel6,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
1638 INDX,qp_limit_balance_check_pvt.check_balance.sel6,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
1639 INDX,qp_limit_balance_check_pvt.check_balance.sel6,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
1640 INDX,qp_limit_balance_check_pvt.check_balance.sel6,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
1641 */
1642 --sql statement sel6
1643 SELECT amount
1644 INTO g_limit_balance_line(i).transaction_amount
1645 FROM qp_limit_transactions
1646 WHERE price_request_code = p_limit_rec.price_request_code
1647 AND list_header_id = p_limit_rec.created_from_list_header_id
1648 AND list_line_id = p_limit_rec.created_from_list_line_id
1649 AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
1650 EXCEPTION
1651 WHEN NO_DATA_FOUND THEN
1652 g_limit_balance_line(i).transaction_amount := null;
1653 END;
1654
1655
1656 --Increment the full_available_amount by the transaction_amount
1657 l_full_available_amount := l_full_available_amount +
1658 nvl(g_limit_balance_line(i).transaction_amount, 0);
1659
1663 p_limit_rec.limit_exceed_action_code = 'HARD'
1660 --fix for bug 4765137 to remove the modifier if limit balance is zero or
1661 --negative for a hard limit on new or repriced orders
1662 IF l_full_available_amount <= 0 AND
1664 THEN
1665 g_limit_balance_line(i).hard_limit_exceeded := TRUE;
1666 RETURN FALSE;
1667 END IF;
1668
1669 IF p_limit_rec.limit_exceed_action_code = 'HARD'
1670 THEN
1671 g_limit_balance_line(i).available_amount :=
1672 least(l_full_available_amount, p_wanted_amount);
1673
1674 IF p_wanted_amount > l_full_available_amount THEN
1675 g_limit_balance_line(i).limit_code :=
1676 QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
1677 ELSE
1678 g_limit_balance_line(i).limit_code :=
1679 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1680 END IF; --if p_wanted_amount > l_full_available_amount
1681
1682 ELSE --Soft Limit
1683 g_limit_balance_line(i).available_amount := p_wanted_amount;
1684
1685 IF g_limit_balance_line(i).line_category = 'RETURN' THEN
1686
1687 g_limit_balance_line(i).limit_code :=
1688 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1689
1690 ELSE --If line_category is not 'RETURN'
1691
1692 IF p_wanted_amount > l_full_available_amount
1693 THEN
1694 g_limit_balance_line(i).limit_code :=
1695 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
1696 ELSE
1697 g_limit_balance_line(i).limit_code :=
1698 QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1699 END IF; --If wanted_amount > l_full_available_amount
1700
1701 END IF; --If line_category is 'RETURN'
1702
1703 END IF; --If limit_exceed_action_code is 'HARD'
1704
1705 IF l_debug = FND_API.G_TRUE THEN
1706 QP_PREQ_GRP.engine_debug('p_wanted_amount '||p_wanted_amount);
1707 QP_PREQ_GRP.engine_debug('available_amount '||
1708 g_limit_balance_line(i).available_amount);
1709
1710 END IF;
1711 IF p_wanted_amount <> 0 THEN
1712 g_limit_balance_line(i).available_percent :=
1713 ABS(g_limit_balance_line(i).available_amount/p_wanted_amount)*100;
1714 ELSE
1715 g_limit_balance_line(i).available_percent := 100;
1716 END IF;
1717
1718 IF l_debug = FND_API.G_TRUE THEN
1719 QP_PREQ_GRP.engine_debug('available_percent '||
1720 g_limit_balance_line(i).available_percent);
1721
1722 END IF;
1723 g_limit_balance_line(i).hard_limit_exceeded := FALSE;
1724
1725 i := i + 1; --Increment i for the next balance line for the limit.
1726
1727 END LOOP; --over l_item_table
1728 END LOOP; --over l_cust_table
1729 END LOOP; --over l_org_table
1730
1731 IF l_debug = FND_API.G_TRUE THEN
1732 QP_PREQ_GRP.engine_debug('***End Check_balance***');
1733 END IF;
1734 RETURN TRUE;
1735
1736 END IF; --If Each Attributes Exist for the Limit
1737
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 IF l_debug = FND_API.G_TRUE THEN
1741 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1742 END IF;
1743 RETURN FALSE;
1744 END Check_Balance;
1745
1746
1747 /**********************************************************************
1748 Utility Function to serve as a wrapper around calls to check_balance.
1749 So that repeated calls can be made elegantly.
1750 ***********************************************************************/
1751 FUNCTION Check_Balance_Wrapper(p_limit_rec IN Limit_Rec,
1752 x_skip_limit OUT NOCOPY BOOLEAN)
1753 RETURN BOOLEAN
1754 IS
1755 l_limit_available BOOLEAN := FALSE;
1756 l_skip_limit BOOLEAN := FALSE;
1757 l_wanted_amount NUMBER;
1758 BEGIN
1759
1760 IF p_limit_rec.basis = 'USAGE' THEN
1761
1762 IF p_limit_rec.line_category = 'RETURN' THEN
1763 l_wanted_amount := -1;
1764 ELSE
1765 l_wanted_amount := 1;
1766 END IF;
1767
1768 ELSIF p_limit_rec.basis = 'QUANTITY' THEN
1769
1770 IF p_limit_rec.line_category = 'RETURN' THEN
1771 l_wanted_amount := -1 * p_limit_rec.quantity_wanted;
1772 ELSE
1773 l_wanted_amount := p_limit_rec.quantity_wanted;
1774 END IF;
1775
1776 ELSIF p_limit_rec.basis = 'ACCRUAL' THEN
1777
1778 IF p_limit_rec.line_category = 'RETURN' THEN
1779 l_wanted_amount := -1 * p_limit_rec.accrual_wanted;
1780 ELSE
1781 l_wanted_amount := p_limit_rec.accrual_wanted;
1782 END IF;
1783
1784 ELSIF p_limit_rec.basis IN ('COST', 'CHARGE') THEN
1785
1786 IF p_limit_rec.line_category = 'RETURN' THEN
1787 l_wanted_amount := -1 * p_limit_rec.cost_wanted;
1788 ELSE
1789 l_wanted_amount := p_limit_rec.cost_wanted;
1790 END IF;
1791
1792 ELSIF p_limit_rec.basis = 'GROSS_REVENUE' THEN
1793
1794 IF p_limit_rec.line_category = 'RETURN' THEN
1795 l_wanted_amount := -1 * p_limit_rec.gross_revenue_wanted;
1796 ELSE
1800 END IF; --Set the appropriate wanted_amount for different types of limit basis
1797 l_wanted_amount := p_limit_rec.gross_revenue_wanted;
1798 END IF;
1799
1801
1802 --Then call the Check_Balance function
1803 l_limit_available := Check_Balance(p_limit_rec => p_limit_rec,
1804 p_wanted_amount => l_wanted_amount,
1805 x_skip_limit => l_skip_limit);
1806
1807 x_skip_limit := l_skip_limit;
1808 RETURN l_limit_available;
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 IF l_debug = FND_API.G_TRUE THEN
1813 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1814 END IF;
1815 RETURN FALSE;
1816 END Check_Balance_Wrapper;
1817
1818
1819 /**************************************************************************
1820 The following is the main procedure to match and evaluate if the
1821 limit balance has been exceeded for each request line and appropriately
1822 update the request line.
1823 ***************************************************************************/
1824 PROCEDURE Process_Limits(x_return_status OUT NOCOPY VARCHAR2,
1825 x_return_text OUT NOCOPY VARCHAR2)
1826 IS
1827 /*
1828 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
1829 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,2
1830 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,QP_LIMITS_N1,LIST_HEADER_ID,1
1831 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,QP_LIMITS_N1,LIST_LINE_ID,2
1832 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,QP_LIMIT_ATTRIBUTES_N1,LIMIT_ID,1
1833 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
1834 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
1835 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
1836 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
1837 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,5
1838 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,6
1839 INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1840 */
1841 CURSOR limits_cur
1842 IS
1843 --Statement to select line-level limits for pure Non-each and mixed cases
1844 SELECT /*+ ordered use_nl (l a rl q) index(rl qp_preq_line_attrs_tmp_N2) */
1845 r.line_index, r.created_from_list_header_id,
1846 r.created_from_list_line_id, 'L' limit_level, l.limit_id,
1847 l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
1848 l.limit_level_code, r.adjustment_amount, r.benefit_qty,
1849 r.created_from_list_line_type, r.pricing_group_sequence,
1850 r.operand_calculation_code, q.price_request_code,
1851 q.request_type_code, q.line_category,
1852 r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
1853 l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
1854 decode(l.organization_flag,
1855 'Y','PARTY','NA') organization_attr_context,
1856 decode(l.organization_flag,
1857 'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
1858 nvl(l.multival_attr1_context,'NA') multival_attr1_context,
1859 nvl(l.multival_attribute1,'NA') multival_attribute1,
1860 nvl(l.multival_attr1_type,'NA') multival_attr1_type,
1861 nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
1862 nvl(l.multival_attr2_context,'NA') multival_attr2_context,
1863 nvl(l.multival_attribute2,'NA') multival_attribute2,
1864 nvl(l.multival_attr2_type,'NA') multival_attr2_type,
1865 nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
1866 (q.priced_quantity * q.unit_price) gross_revenue_wanted,
1867 (decode(q.line_type_code,
1868 'ORDER', decode(r.operand_calculation_code,
1869 '%', q.unit_price * r.operand_value/100,
1870 r.operand_value),
1871 r.adjustment_amount * q.priced_quantity)) cost_wanted,
1872 /*decode(r.operand_calculation_code,
1873 QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
1874 r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
1875 r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
1876 q.priced_quantity quantity_wanted
1877 FROM qp_npreq_ldets_tmp r, qp_limits l,
1878 qp_limit_attributes a, qp_npreq_line_attrs_tmp rl, qp_npreq_lines_tmp q
1879 WHERE r.created_from_list_header_id = l.list_header_id
1880 AND r.created_from_list_line_id = l.list_line_id
1881 AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1882 AND r.applied_flag = 'Y' -- [5385851/5322832]
1883 AND r.header_limit_exists = 'Y' --common flag for both header and line
1884 AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
1885 AND l.limit_id = a.limit_id
1886 AND a.limit_attribute_context = rl.context
1887 AND a.limit_attribute = rl.attribute
1888 AND a.limit_attr_value = rl.value_from
1889 AND a.limit_attribute_type = rl.attribute_type
1890 AND rl.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
1891 AND r.line_index = rl.line_index
1892 AND r.line_index = q.line_index
1893 GROUP BY r.line_index, r.created_from_list_header_id,
1897 r.created_from_list_line_type, r.pricing_group_sequence,
1894 r.created_from_list_line_id, 'L', l.limit_id,
1895 l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
1896 l.limit_level_code, r.adjustment_amount, r.benefit_qty,
1898 r.operand_calculation_code, q.price_request_code,
1899 q.request_type_code, q.line_category, r.operand_value, q.unit_price,
1900 l.each_attr_exists, r.pricing_phase_id, l.non_each_attr_count,
1901 l.total_attr_count, r.line_detail_index, l.organization_flag,
1902 l.multival_attr1_context, l.multival_attribute1,
1903 l.multival_attr1_type, l.multival_attr1_datatype,
1904 l.multival_attr2_context, l.multival_attribute2,
1905 l.multival_attr2_type, l.multival_attr2_datatype,
1906 q.priced_quantity, q.line_type_code
1907 HAVING count(*) = (select count(*)
1908 from qp_limit_attributes la
1909 where la.limit_id = l.limit_id)
1910
1911 UNION
1912
1913 --Statement to select line-level limits for pure Each and no limit attrs cases
1914 SELECT r.line_index, r.created_from_list_header_id,
1915 r.created_from_list_line_id, 'L' limit_level, l.limit_id,
1916 l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
1917 l.limit_level_code, r.adjustment_amount, r.benefit_qty,
1918 r.created_from_list_line_type, r.pricing_group_sequence,
1919 r.operand_calculation_code, q.price_request_code,
1920 q.request_type_code, q.line_category,
1921 r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
1922 l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
1923 decode(l.organization_flag,
1924 'Y','PARTY','NA') organization_attr_context,
1925 decode(l.organization_flag,
1926 'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
1927 nvl(l.multival_attr1_context,'NA') multival_attr1_context,
1928 nvl(l.multival_attribute1,'NA') multival_attribute1,
1929 nvl(l.multival_attr1_type,'NA') multival_attr1_type,
1930 nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
1931 nvl(l.multival_attr2_context,'NA') multival_attr2_context,
1932 nvl(l.multival_attribute2,'NA') multival_attribute2,
1933 nvl(l.multival_attr2_type,'NA') multival_attr2_type,
1934 nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
1935 (q.priced_quantity * q.unit_price) gross_revenue_wanted,
1936 (decode(q.line_type_code,
1937 'ORDER', decode(r.operand_calculation_code,
1938 '%', q.unit_price * r.operand_value/100,
1939 r.operand_value),
1940 r.adjustment_amount * q.priced_quantity)) cost_wanted,
1941 /*decode(r.operand_calculation_code,
1942 QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
1943 r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
1944 r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
1945 q.priced_quantity quantity_wanted
1946 FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
1947 WHERE r.created_from_list_header_id = l.list_header_id
1948 AND r.created_from_list_line_id = l.list_line_id
1949 AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1950 AND r.applied_flag = 'Y' -- [5385851/5322832]
1951 AND r.header_limit_exists = 'Y' --common flag for both header and line
1952 AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
1953 AND r.line_index = q.line_index
1954 AND l.non_each_attr_count = 0
1955
1956 UNION
1957
1958 --Statement to select header-level limits for pure Non-each and mixed cases
1959 SELECT r.line_index, r.created_from_list_header_id,
1960 r.created_from_list_line_id, 'H' limit_level, l.limit_id,
1961 l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
1962 l.limit_level_code, r.adjustment_amount, r.benefit_qty,
1963 r.created_from_list_line_type, r.pricing_group_sequence,
1964 r.operand_calculation_code, q.price_request_code,
1965 q.request_type_code, q.line_category,
1966 r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
1967 l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
1968 decode(l.organization_flag,
1969 'Y','PARTY','NA') organization_attr_context,
1970 decode(l.organization_flag,
1971 'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
1972 nvl(l.multival_attr1_context,'NA') multival_attr1_context,
1973 nvl(l.multival_attribute1,'NA') multival_attribute1,
1974 nvl(l.multival_attr1_type,'NA') multival_attr1_type,
1975 nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
1976 nvl(l.multival_attr2_context,'NA') multival_attr2_context,
1977 nvl(l.multival_attribute2,'NA') multival_attribute2,
1978 nvl(l.multival_attr2_type,'NA') multival_attr2_type,
1979 nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
1980 (q.priced_quantity * q.unit_price) gross_revenue_wanted,
1981 (decode(q.line_type_code,
1982 'ORDER', decode(r.operand_calculation_code,
1983 '%', q.unit_price * r.operand_value/100,
1984 r.operand_value),
1985 r.adjustment_amount * q.priced_quantity)) cost_wanted,
1986 /* decode(r.operand_calculation_code,
1990 q.priced_quantity quantity_wanted
1987 QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
1988 r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
1989 r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
1991 FROM qp_npreq_ldets_tmp r, qp_limits l,
1992 qp_limit_attributes a, qp_npreq_line_attrs_tmp rl, qp_npreq_lines_tmp q
1993 WHERE r.created_from_list_header_id = l.list_header_id
1994 AND l.list_line_id = -1
1995 AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1996 AND r.applied_flag = 'Y' -- [5385851/5322832]
1997 AND r.header_limit_exists = 'Y' --common flag for both header and line
1998 AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
1999 AND l.limit_id = a.limit_id
2000 AND a.limit_attribute_context = rl.context
2001 AND a.limit_attribute = rl.attribute
2002 AND a.limit_attr_value = rl.value_from
2003 AND a.limit_attribute_type = rl.attribute_type
2004 AND rl.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
2005 AND r.line_index = rl.line_index
2006 AND r.line_index = q.line_index
2007 GROUP BY r.line_index, r.created_from_list_header_id,
2008 r.created_from_list_line_id, 'H', l.limit_id,
2009 l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
2010 l.limit_level_code, r.adjustment_amount, r.benefit_qty,
2011 r.created_from_list_line_type, r.pricing_group_sequence,
2012 r.operand_calculation_code, q.price_request_code,
2013 q.request_type_code, q.line_category, r.operand_value, q.unit_price,
2014 l.each_attr_exists, r.pricing_phase_id, l.non_each_attr_count,
2015 l.total_attr_count, r.line_detail_index, l.organization_flag,
2016 l.multival_attr1_context, l.multival_attribute1,
2017 l.multival_attr1_type, l.multival_attr1_datatype,
2018 l.multival_attr2_context, l.multival_attribute2,
2019 l.multival_attr2_type, l.multival_attr2_datatype,
2020 q.priced_quantity, q.line_type_code
2021 HAVING count(*) = (select count(*)
2022 from qp_limit_attributes la
2023 where la.limit_id = l.limit_id)
2024
2025 UNION
2026
2027 --Statement to select headerlevel limits for pure Each and no limit attrs cases
2028 SELECT r.line_index, r.created_from_list_header_id,
2029 r.created_from_list_line_id, 'H' limit_level, l.limit_id,
2030 l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
2031 l.limit_level_code, r.adjustment_amount, r.benefit_qty,
2032 r.created_from_list_line_type, r.pricing_group_sequence,
2033 r.operand_calculation_code, q.price_request_code,
2034 q.request_type_code, q.line_category,
2035 r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
2036 l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
2037 decode(l.organization_flag,
2038 'Y','PARTY','NA') organization_attr_context,
2039 decode(l.organization_flag,
2040 'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
2041 nvl(l.multival_attr1_context,'NA') multival_attr1_context,
2042 nvl(l.multival_attribute1,'NA') multival_attribute1,
2043 nvl(l.multival_attr1_type,'NA') multival_attr1_type,
2044 nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
2045 nvl(l.multival_attr2_context,'NA') multival_attr2_context,
2046 nvl(l.multival_attribute2,'NA') multival_attribute2,
2047 nvl(l.multival_attr2_type,'NA') multival_attr2_type,
2048 nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
2049 (q.priced_quantity * q.unit_price) gross_revenue_wanted,
2050 (decode(q.line_type_code,
2051 'ORDER', decode(r.operand_calculation_code,
2052 '%', q.unit_price * r.operand_value/100,
2053 r.operand_value),
2054 r.adjustment_amount * q.priced_quantity)) cost_wanted,
2055 /*decode(r.operand_calculation_code,
2056 QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
2057 r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
2058 r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
2059 q.priced_quantity quantity_wanted
2060 FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
2061 WHERE r.created_from_list_header_id = l.list_header_id
2062 AND l.list_line_id = -1
2063 AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2064 AND r.applied_flag = 'Y' -- [5385851/5322832]
2065 AND r.header_limit_exists = 'Y' --common flag for both header and line
2066 AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
2067 AND r.line_index = q.line_index
2068 AND l.non_each_attr_count = 0
2069
2070 ORDER BY 1,2,3,4,5;
2071 --That is, order by r.line_index, r.created_from_list_header_id, r.created_from_list_line_id, limit_level, l.limit_id
2072
2073
2074 l_limit_available BOOLEAN := FALSE;
2075 l_retcode BOOLEAN := FALSE;
2076 l_available_amount NUMBER;
2077 l_limit_exceed_action_code VARCHAR2(30);
2078 l_req_attr_value VARCHAR2(240);
2079 l_limit_text VARCHAR2(2000) := '';
2080 l_return_status VARCHAR2(1);
2081
2085 l_skip_limit BOOLEAN := FALSE;
2082 l_old_limit_rec limits_cur%ROWTYPE;
2083 l_skip_header BOOLEAN := FALSE;
2084 l_skip_line BOOLEAN := FALSE;
2086
2087 l_limit_code VARCHAR2(30) := '';
2088 l_hold_code VARCHAR2(240) := '';
2089
2090 TYPE number_table is table of NUMBER index by BINARY_INTEGER; --Bug 4457725
2091 l_processed_limits_tbl number_table; --Bug 4457725
2092 l_processed_limit_count NUMBER := 1; --Bug 4457725
2093 l_processed BOOLEAN; --Bug 4457725
2094 l_modifier_level_code VARCHAR2(30) := ''; --Bug 4457725
2095
2096 BEGIN
2097
2098 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2099 IF l_debug = FND_API.G_TRUE THEN
2100 QP_PREQ_GRP.engine_debug('***Begin Process_Limits***');
2101
2102 END IF;
2103 --Initialize x_return_status
2104 x_return_status := FND_API.G_RET_STS_SUCCESS;
2105 x_return_text := 'Success';
2106
2107 --Reset global plsql table storing limit balances
2108 g_limit_balance_line.DELETE;
2109
2110 --Initialize list_line_id, list_header_id and line_index in l_old_limit_rec
2111 l_old_limit_rec.created_from_list_line_id := -9999999;
2112 l_old_limit_rec.created_from_list_header_id := -9999999;
2113 l_old_limit_rec.line_index := -9999999;
2114
2115 FOR l_limit_rec IN limits_cur
2116 LOOP
2117
2118 IF l_debug = FND_API.G_TRUE THEN
2119 QP_PREQ_GRP.engine_debug('========================');
2120 QP_PREQ_GRP.engine_debug('In limits_cur loop');
2121
2122 QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123 QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124 QP_PREQ_GRP.engine_debug('------------------------');
2125 QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126 QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127 QP_PREQ_GRP.engine_debug('------------------------');
2128 QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129 QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2130 QP_PREQ_GRP.engine_debug('------------------------');
2131
2132 END IF;
2133 --If Skip_line flag is true then skip all limits until list_line_id changes
2134 --or until line_index changes.
2135 IF l_skip_line AND
2136 l_limit_rec.created_from_list_line_id =
2137 l_old_limit_rec.created_from_list_line_id AND
2138 l_limit_rec.line_index =
2139 l_old_limit_rec.line_index
2140 THEN
2141 IF l_debug = FND_API.G_TRUE THEN
2142 QP_PREQ_GRP.engine_debug('skipping limit '|| l_limit_rec.limit_id ||
2143 'for line '||l_limit_rec.created_from_list_line_id);
2144
2145 END IF;
2146 l_old_limit_rec := l_limit_rec;
2147 GOTO limits_loop; --to next record in limits_cur loop
2148 END IF;
2149
2150 --If Skip_header flag is true, skip all limits until list_header_id changes
2151 --or until line_index changes.
2152 IF l_skip_header AND
2153 l_limit_rec.created_from_list_header_id =
2154 l_old_limit_rec.created_from_list_header_id AND
2155 l_limit_rec.line_index =
2156 l_old_limit_rec.line_index
2157 THEN
2158 IF l_debug = FND_API.G_TRUE THEN
2159 QP_PREQ_GRP.engine_debug('skipping limit '|| l_limit_rec.limit_id ||
2160 'for header '||l_limit_rec.created_from_list_header_id);
2161
2162 END IF;
2163 l_old_limit_rec := l_limit_rec;
2164 GOTO limits_loop; --to next record in limits_cur loop
2165 END IF;
2166
2167 l_skip_line := FALSE;
2168 l_skip_header := FALSE;
2169
2170
2171 --If list_line_id or line_index changes
2172 IF (l_old_limit_rec.created_from_list_line_id <>
2173 l_limit_rec.created_from_list_line_id AND
2174 l_old_limit_rec.created_from_list_line_id >= 0)
2175 --no need to execute this for the first time.
2176 OR
2177 (l_old_limit_rec.line_index <> l_limit_rec.line_index AND
2178 l_old_limit_rec.line_index >= 0)
2179 --no need to execute this for the first time.
2180 THEN
2181
2182 --Bug 4457725] Added to check for Group of Lines modifier such that a limit
2183 --is applied only once for a group
2184 select MODIFIER_LEVEL_CODE into l_modifier_level_code
2185 from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
2186 IF l_debug = FND_API.G_TRUE THEN
2187 QP_PREQ_GRP.engine_debug('Modifier Level '||l_modifier_level_code);
2188 END IF;
2189
2190 IF l_modifier_level_code = 'LINEGROUP' AND l_processed_limits_tbl.COUNT > 0
2191 THEN
2192 l_processed := false;
2193 FOR j in l_processed_limits_tbl.FIRST..l_processed_limits_tbl.LAST
2194 LOOP
2195 IF l_processed_limits_tbl(j) = l_old_limit_rec.limit_id THEN
2196 l_processed := true;
2197 exit;
2198 END IF;
2199 END LOOP;
2200 END IF;
2201
2202 IF l_modifier_level_code = 'LINEGROUP' AND l_processed
2203 THEN
2204 IF l_debug = FND_API.G_TRUE THEN
2208 g_limit_balance_line.DELETE;
2205 QP_PREQ_GRP.engine_debug('Line Group Modifier with limit id '||l_old_limit_rec.limit_id||' already processed');
2206 END IF;
2207 --Clear the global plsql table storing limits info.
2209
2210 --Skip updating the limit balance if limit is processed already
2211 GOTO next_record;
2212 END IF;
2213
2214 G_LOOP_COUNT := 0; --Initialize whenever line_index or list_line_id
2215 --changes, i.e., before each loop
2216 LOOP
2217 IF l_debug = FND_API.G_TRUE THEN
2218 QP_PREQ_GRP.engine_debug('Change in list_line_id or line_index. ' ||
2219 'Update limit balance. Loop through ' ||
2220 'Recheck_balance and Update_Balance, if ' ||
2221 'necessary ');
2222
2223 END IF;
2224 l_return_status := Update_Balance(x_return_text);
2225
2226 IF l_modifier_level_code = 'LINEGROUP' THEN
2227 l_processed_limits_tbl(l_processed_limit_count) := l_old_limit_rec.limit_id;
2228 l_processed_limit_count := l_processed_limit_count+1;
2229 END IF;
2230
2231 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2232
2233 IF G_LOOP_COUNT <= G_MAX_LOOP_COUNT THEN
2234 l_retcode := Recheck_Balance;
2235 ELSE
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238
2239 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2241
2242 ELSE --If Update_Balance successful
2243 IF g_limit_balance_line.COUNT > 0 THEN
2244
2245 --Reset limit_code and limit_text when line_index or
2246 --list_line_id changes.
2247 IF (l_old_limit_rec.created_from_list_line_id <>
2248 l_limit_rec.created_from_list_line_id) OR
2249 (l_old_limit_rec.line_index <> l_limit_rec.line_index)
2250 THEN
2251 l_limit_code := '';
2252 l_limit_text := '';
2253 END IF;
2254
2255 --Reset hold_code when list_line_id changes.
2256 IF (l_old_limit_rec.line_index <> l_limit_rec.line_index) THEN
2257 l_hold_code := '';
2258 END IF;
2259
2260 FOR j IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
2261 LOOP
2262
2263 IF (g_limit_balance_line(j).limit_code =
2264 QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED)
2265 OR
2266 (g_limit_balance_line(j).limit_code =
2267 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED AND
2268 l_limit_code IS NULL)
2269 THEN
2270
2271 l_limit_code := g_limit_balance_line(j).limit_code;
2272
2273 Build_Message_Text(
2274 p_List_Header_Id => g_limit_balance_line(j).list_header_id
2275 ,p_List_Line_Id => g_limit_balance_line(j).list_line_id
2276 ,p_Limit_Id => g_limit_balance_line(j).limit_id
2277 ,p_full_available_amount => g_limit_balance_line(j).full_available_amount
2278 ,p_wanted_amount => g_limit_balance_line(j).wanted_amount
2279 ,p_limit_code => g_limit_balance_line(j).limit_code
2280 ,p_limit_level => g_limit_balance_line(j).limit_level
2281 ,p_operand_value => g_limit_balance_line(j).operand_value
2282 ,p_operand_calculation_code => g_limit_balance_line(j).operand_calculation_code
2283 ,p_least_percent => g_limit_balance_line(j).least_percent
2284 ,p_message_text => l_limit_text
2285 );
2286
2287 IF l_debug = FND_API.G_TRUE THEN
2288 QP_PREQ_GRP.engine_debug('################# ' || l_limit_text);
2289
2290 END IF;
2291 END IF;
2292
2293 IF g_limit_balance_line(j).limit_hold_flag = 'Y' AND
2294 g_limit_balance_line(j).limit_code IN
2295 (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2296 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2297 THEN
2298 l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2299 END IF;
2300
2301 IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2302 g_limit_balance_line(j).limit_code IN
2303 (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2304 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2305 THEN
2306 l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2307 END IF;
2308
2309 IF l_debug = FND_API.G_TRUE THEN
2310 QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2311 QP_PREQ_GRP.engine_debug('Hold Code is ' || l_hold_code);
2312
2313 END IF;
2314 --Do the following update for all list line types
2315 /*
2316 INDX,qp_limit_balance_check_pvt.process_limits.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2317 INDX,qp_limit_balance_check_pvt.process_limits.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2318 INDX,qp_limit_balance_check_pvt.process_limits.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
2319 */
2320 --sql statement upd1
2321 UPDATE qp_npreq_ldets_tmp
2322 SET operand_value = g_limit_balance_line(j).operand_value,
2326 WHERE line_index = g_limit_balance_line(j).line_index
2323 benefit_qty = g_limit_balance_line(j).benefit_qty,
2324 limit_code = l_limit_code,
2325 limit_text = l_limit_text
2327 AND created_from_list_line_id =
2328 g_limit_balance_line(j).list_line_id
2329 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2330
2331 --Update the Child Break Lines for 'PBH' lines
2332 IF g_limit_balance_line(j).created_from_list_line_type = 'PBH'
2333 THEN
2334 /*
2335 INDX,qp_limit_balance_check_pvt.process_limits.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2336 INDX,qp_limit_balance_check_pvt.process_limits.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2337 INDX,qp_limit_balance_check_pvt.process_limits.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
2338 INDX,qp_limit_balance_check_pvt.process_limits.upd2,QP_RLTD_LINES_TMP_INDEX,LINE_INDEX,1
2339 */
2340 --sql statement upd2
2341 UPDATE qp_npreq_ldets_tmp a
2342 SET a.operand_value = DECODE(
2343 g_limit_balance_line(j).operand_calculation_code,
2344 '%', (g_limit_balance_line(j).least_percent/100) *
2345 g_limit_balance_line(j).operand_value,
2346 'AMT', (g_limit_balance_line(j).least_percent/100) *
2347 g_limit_balance_line(j).operand_value,
2348 'LUMPSUM', (g_limit_balance_line(j).least_percent/100)
2349 * g_limit_balance_line(j).operand_value,
2350 'NEWPRICE', g_limit_balance_line(j).operand_value -
2351 (100 - g_limit_balance_line(j).least_percent)/100
2352 * g_limit_balance_line(j).adjustment_amount,
2353 g_limit_balance_line(j).operand_value),
2354
2355 a.benefit_qty = DECODE(
2356 g_limit_balance_line(j).basis,
2357 'ACCRUAL', (g_limit_balance_line(j).least_percent/100)
2358 * g_limit_balance_line(j).benefit_qty,
2359 g_limit_balance_line(j).benefit_qty),
2360
2361 a.limit_code = l_limit_code,
2362 a.limit_text = l_limit_text
2363
2364 WHERE a.line_index = g_limit_balance_line(j).line_index
2365 AND a.created_from_list_line_id =
2366 g_limit_balance_line(j).list_line_id
2367 AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2368 AND a.line_detail_index IN
2369 (SELECT b.related_line_detail_index
2370 FROM qp_npreq_rltd_lines_tmp b
2371 WHERE b.line_index = a.line_index
2372 AND b.relationship_type_code = 'PRICE_BREAK'
2373 AND b.line_detail_index =
2374 g_limit_balance_line(j).line_detail_index);
2375
2376 END IF;--If created_from_list_line_type = 'PBH'
2377
2378 IF l_debug = FND_API.G_TRUE THEN
2379 QP_PREQ_GRP.engine_debug('line_index '||
2380 g_limit_balance_line(j).line_index);
2381 QP_PREQ_GRP.engine_debug('limit_code '||
2382 g_limit_balance_line(j).limit_code);
2383 QP_PREQ_GRP.engine_debug('list_line_id '||
2384 g_limit_balance_line(j).list_line_id);
2385 QP_PREQ_GRP.engine_debug('benefit_qty '||
2386 g_limit_balance_line(j).benefit_qty);
2387 QP_PREQ_GRP.engine_debug('operand_value '||
2388 g_limit_balance_line(j).operand_value);
2389
2390 END IF;
2391 --Update Hold_Code
2392 /*
2393 INDX,qp_limit_balance_check_pvt.process_limits.upd3,qp_npreq_lines_tmp_N1,LINE_INDEX,1
2394 */
2395 --sql statement upd3
2396 UPDATE qp_npreq_lines_tmp
2397 SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
2398 WHERE line_index = g_limit_balance_line(j).line_index;
2399
2400 IF l_debug = FND_API.G_TRUE THEN
2401 QP_PREQ_GRP.engine_debug('Hold Flag is Y');
2402 QP_PREQ_GRP.engine_debug('Hold Code is '||QP_PREQ_GRP.G_STATUS_LIMIT_HOLD);
2403
2404 END IF;
2405
2406 IF g_limit_balance_line(j).transaction_amount is null THEN
2407 INSERT INTO qp_limit_transactions
2408 (
2409 limit_balance_id,
2410 creation_date,
2411 created_by,
2412 last_update_date,
2413 last_updated_by,
2414 list_header_id,
2415 list_line_id,
2416 price_request_date,
2417 price_request_type_code,
2418 price_request_code,
2419 pricing_phase_id,
2420 amount
2421 )
2422 VALUES
2423 (
2424 g_limit_balance_line(j).limit_balance_id,
2425 sysdate,
2426 fnd_global.user_id,
2427 sysdate,
2428 fnd_global.user_id,
2429 g_limit_balance_line(j).list_header_id,
2433 g_limit_balance_line(j).price_request_code,
2430 g_limit_balance_line(j).list_line_id,
2431 sysdate,
2432 g_limit_balance_line(j).request_type_code,
2434 g_limit_balance_line(j).pricing_phase_id,
2435 g_limit_balance_line(j).given_amount
2436 );
2437
2438 ELSIF g_limit_balance_line(j).transaction_amount <>
2439 g_limit_balance_line(j).given_amount THEN
2440 /*
2441 INDX,qp_limit_balance_check_pvt.process_limits.upd4,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
2442 INDX,qp_limit_balance_check_pvt.process_limits.upd4,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
2443 INDX,qp_limit_balance_check_pvt.process_limits.upd4,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
2444 INDX,qp_limit_balance_check_pvt.process_limits.upd4,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
2445 */
2446 --sql statement upd4
2447 update qp_limit_transactions
2448 set amount = g_limit_balance_line(j).given_amount,
2449 last_update_date = sysdate,
2450 last_updated_by = fnd_global.user_id,
2451 price_request_date = sysdate
2452 where limit_balance_id = g_limit_balance_line(j).limit_balance_id and list_header_id = g_limit_balance_line(j).list_header_id
2453 and list_line_id = g_limit_balance_line(j).list_line_id
2454 and price_request_code =
2455 g_limit_balance_line(j).price_request_code;
2456
2457 END IF; --If transaction_amount is null
2458
2459 END LOOP; --through limit balance lines
2460
2461 END IF; --g_limit_balance_line.COUNT > 0
2462
2463 IF l_debug = FND_API.G_TRUE THEN
2464 QP_PREQ_GRP.engine_debug('Done Updating Balances when line changed');
2465
2466 END IF;
2467 --Clear the global plsql table storing limits info.
2468 g_limit_balance_line.DELETE;
2469 EXIT;
2470
2471 END IF; --If Update_Balance returns Error
2472
2473 END LOOP;
2474
2475 END IF; --If list_line_id or line_index has changed
2476
2477 <<next_record>>
2478 l_limit_available := Check_Balance_Wrapper(p_limit_rec => l_limit_rec,
2479 x_skip_limit => l_skip_limit);
2480
2481 IF NOT l_limit_available THEN
2482
2483 IF l_debug = FND_API.G_TRUE THEN
2484 QP_PREQ_GRP.engine_debug('Limit not available - hard limit with 0 balance');
2485
2486 END IF;
2487 --If Header-level limit, then skip all limits and go to the next
2488 --list_header limit. Elseif line_level go to the next line_level limit.
2489 IF l_limit_rec.limit_level = 'H' THEN
2490 IF l_debug = FND_API.G_TRUE THEN
2491 QP_PREQ_GRP.engine_debug('Header Level Limit '||
2492 l_limit_rec.limit_id ||' not available');
2493 END IF;
2494 /*
2495 INDX,qp_limit_balance_check_pvt.process_limits.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2496 INDX,qp_limit_balance_check_pvt.process_limits.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2497 INDX,qp_limit_balance_check_pvt.process_limits.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
2498 */
2499 --sql statement upd5
2500 UPDATE qp_npreq_ldets_tmp
2501 SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
2502 WHERE created_from_list_header_id =
2503 l_limit_rec.created_from_list_header_id
2504 AND line_index = l_limit_rec.line_index
2505 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2506
2507 l_skip_header := TRUE;
2508
2509 ELSIF l_limit_rec.limit_level = 'L' THEN
2510 IF l_debug = FND_API.G_TRUE THEN
2511 QP_PREQ_GRP.engine_debug('Line Level Limit '||
2512 l_limit_rec.limit_id ||' not available');
2513 END IF;
2514 /*
2515 INDX,qp_limit_balance_check_pvt.process_limits.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2516 INDX,qp_limit_balance_check_pvt.process_limits.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2517 INDX,qp_limit_balance_check_pvt.process_limits.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
2518 */
2519 --sql statement upd6
2520 UPDATE qp_npreq_ldets_tmp
2521 SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
2522 WHERE created_from_list_line_id =
2523 l_limit_rec.created_from_list_line_id
2524 AND line_index = l_limit_rec.line_index
2525 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2526
2527 l_skip_line := TRUE;
2528
2529 END IF;
2530
2531 ELSIF l_limit_available AND l_skip_limit THEN
2532
2533 IF l_debug = FND_API.G_TRUE THEN
2534 QP_PREQ_GRP.engine_debug('limit ' || l_limit_rec.limit_id ||
2535 ' available but' || 'skipped - does not qualify');
2536 END IF;
2537 l_skip_limit := FALSE;
2538 GOTO limits_loop; --to next record in limits_cur loop
2539
2540 END IF; --If NOT l_limit_available
2541
2542 l_old_limit_rec := l_limit_rec;
2543
2544 IF l_debug = FND_API.G_TRUE THEN
2545 QP_PREQ_GRP.engine_debug('Repeat limits_cur loop for next limit - if any');
2546 END IF;
2547 <<limits_loop>>
2548 null;
2552 --Loop to update balance for the last list line of the limits_cur loop
2549 END LOOP; --loop over main limits_cur
2550
2551
2553 --(Boundary condition)
2554
2555 IF l_old_limit_rec.created_from_list_line_id >= 0 THEN
2556 --no need to execute this the first time, when no limits to be processed
2557
2558 G_LOOP_COUNT := 0; --Initialize before boundary condition loop
2559
2560 LOOP
2561 IF l_debug = FND_API.G_TRUE THEN
2562 QP_PREQ_GRP.engine_debug('***Start Boundary condition processing***');
2563 END IF;
2564
2565 select MODIFIER_LEVEL_CODE into l_modifier_level_code
2566 from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
2567 IF l_debug = FND_API.G_TRUE THEN
2568 QP_PREQ_GRP.engine_debug('Modifier Level '||l_modifier_level_code);
2569 END IF;
2570
2571 IF l_modifier_level_code = 'LINEGROUP' AND l_processed_limits_tbl.COUNT > 0
2572 THEN
2573 l_processed := false;
2574 FOR j in l_processed_limits_tbl.FIRST..l_processed_limits_tbl.LAST
2575 LOOP
2576 IF l_processed_limits_tbl(j) = l_old_limit_rec.limit_id THEN
2577 l_processed := true;
2578 exit;
2579 END IF;
2580 END LOOP;
2581 END IF;
2582
2583 IF l_modifier_level_code = 'LINEGROUP' AND l_processed
2584 THEN
2585 IF l_debug = FND_API.G_TRUE THEN
2586 QP_PREQ_GRP.engine_debug('***Boundary condition GroupOfLines Limit already processed***');
2587 END IF;
2588 EXIT;
2589 END IF;
2590
2591 l_return_status := Update_Balance(x_return_text);
2592
2593 IF l_modifier_level_code = 'LINEGROUP' THEN
2594 l_processed_limits_tbl(l_processed_limit_count) := l_old_limit_rec.limit_id;
2595 l_processed_limit_count := l_processed_limit_count+1;
2596 END IF;
2597
2598 IF l_debug = FND_API.G_TRUE THEN
2599 QP_PREQ_GRP.engine_debug('Update_Balance l_return_status ' ||l_return_status);
2600 END IF;
2601
2602 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2603
2604 IF G_LOOP_COUNT <= G_MAX_LOOP_COUNT THEN
2605 l_retcode := Recheck_Balance;
2606 ELSE
2607 RAISE FND_API.G_EXC_ERROR;
2608 END IF;
2609
2610 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2612
2613 ELSE --If Update_Balance successful
2614
2615 IF g_limit_balance_line.COUNT > 0 THEN
2616
2617 --Reset limit_code and limit_text when line_index for boundary condition
2618 --processing.
2619 l_limit_code := '';
2620 l_limit_text := '';
2621
2622 --Reset hold_code for boundary condition processing.
2623 l_hold_code := '';
2624
2625 FOR j IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
2626 LOOP
2627
2628 IF g_limit_balance_line(j).limit_code =
2629 QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED
2630 OR
2631 (g_limit_balance_line(j).limit_code =
2632 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED AND
2633 l_limit_code IS NULL)
2634 THEN
2635
2636 l_limit_code := g_limit_balance_line(j).limit_code;
2637
2638 IF l_debug = FND_API.G_TRUE THEN
2639 QP_PREQ_GRP.engine_debug('Before Buid_Message_Text');
2640 END IF;
2641
2642 Build_Message_Text(
2643 p_List_Header_Id => g_limit_balance_line(j).list_header_id
2644 ,p_List_Line_Id => g_limit_balance_line(j).list_line_id
2645 ,p_Limit_Id => g_limit_balance_line(j).limit_id
2646 ,p_full_available_amount => g_limit_balance_line(j).full_available_amount
2647 ,p_wanted_amount => g_limit_balance_line(j).wanted_amount
2648 ,p_limit_code => g_limit_balance_line(j).limit_code
2649 ,p_limit_level => g_limit_balance_line(j).limit_level
2650 ,p_operand_value => g_limit_balance_line(j).operand_value
2651 ,p_operand_calculation_code => g_limit_balance_line(j).operand_calculation_code
2652 ,p_least_percent => g_limit_balance_line(j).least_percent
2653 ,p_message_text => l_limit_text
2654 );
2655
2656 IF l_debug = FND_API.G_TRUE THEN
2657 QP_PREQ_GRP.engine_debug('################# ' || l_limit_text);
2658
2659 END IF;
2660 END IF;
2661
2662 IF g_limit_balance_line(j).limit_hold_flag = 'Y' AND
2663 g_limit_balance_line(j).limit_code IN
2664 (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2665 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2666 THEN
2667 IF l_debug = FND_API.G_TRUE THEN
2668 QP_PREQ_GRP.engine_debug('label 1');
2669 END IF;
2670 l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2671 END IF;
2672
2673 IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2674 g_limit_balance_line(j).limit_code IN
2675 (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2676 QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2677 THEN
2678 IF l_debug = FND_API.G_TRUE THEN
2679 QP_PREQ_GRP.engine_debug('label 2');
2680 END IF;
2681 l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2685 QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2682 END IF;
2683
2684 IF l_debug = FND_API.G_TRUE THEN
2686 QP_PREQ_GRP.engine_debug('Hold Code is ' || l_hold_code);
2687
2688 END IF;
2689 --Do the following update for all list line types
2690 /*
2691 INDX,qp_limit_balance_check_pvt.process_limits.upd7,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2692 INDX,qp_limit_balance_check_pvt.process_limits.upd7,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2693 INDX,qp_limit_balance_check_pvt.process_limits.upd7,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
2694 */
2695 --sql statement upd7
2696 UPDATE qp_npreq_ldets_tmp
2697 SET operand_value = g_limit_balance_line(j).operand_value,
2698 benefit_qty = g_limit_balance_line(j).benefit_qty,
2699 limit_code = l_limit_code,
2700 limit_text = l_limit_text
2701 WHERE line_index = g_limit_balance_line(j).line_index
2702 AND created_from_list_line_id =
2703 g_limit_balance_line(j).list_line_id
2704 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2705
2706 IF l_debug = FND_API.G_TRUE THEN
2707 QP_PREQ_GRP.engine_debug('label 3');
2708 END IF;
2709
2710 --Update the Child Break Lines for 'PBH' lines
2711 IF g_limit_balance_line(j).created_from_list_line_type = 'PBH' THEN
2712 /*
2713 INDX,qp_limit_balance_check_pvt.process_limits.upd8,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2714 INDX,qp_limit_balance_check_pvt.process_limits.upd8,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2715 INDX,qp_limit_balance_check_pvt.process_limits.upd8,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
2716 INDX,qp_limit_balance_check_pvt.process_limits.upd8,QP_RLTD_LINES_TMP_INDEX,LINE_INDEX,1
2717 */
2718 --sql statement upd8
2719 UPDATE qp_npreq_ldets_tmp a
2720 SET a.operand_value = DECODE(
2721 g_limit_balance_line(j).operand_calculation_code,
2722 '%', (g_limit_balance_line(j).least_percent/100) *
2723 g_limit_balance_line(j).operand_value,
2724 'AMT', (g_limit_balance_line(j).least_percent/100) *
2725 g_limit_balance_line(j).operand_value,
2726 'LUMPSUM', (g_limit_balance_line(j).least_percent/100)
2727 * g_limit_balance_line(j).operand_value,
2728 'NEWPRICE', g_limit_balance_line(j).operand_value -
2729 (100 - g_limit_balance_line(j).least_percent)/100
2730 * g_limit_balance_line(j).adjustment_amount,
2731 g_limit_balance_line(j).operand_value),
2732
2733 a.benefit_qty = DECODE(
2734 g_limit_balance_line(j).basis,
2735 'ACCRUAL', (g_limit_balance_line(j).least_percent/100) *
2736 g_limit_balance_line(j).benefit_qty,
2737 g_limit_balance_line(j).benefit_qty),
2738
2739 a.limit_code = l_limit_code,
2740 a.limit_text = l_limit_text
2741
2742 WHERE a.line_index = g_limit_balance_line(j).line_index
2743 AND a.created_from_list_line_id =
2744 g_limit_balance_line(j).list_line_id
2745 AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2746 AND a.line_detail_index IN
2747 (SELECT b.related_line_detail_index
2748 FROM qp_npreq_rltd_lines_tmp b
2749 WHERE b.line_index = a.line_index
2750 AND b.relationship_type_code = 'PRICE_BREAK'
2751 AND b.line_detail_index =
2752 g_limit_balance_line(j).line_detail_index);
2753
2754 IF l_debug = FND_API.G_TRUE THEN
2755 QP_PREQ_GRP.engine_debug('label 4');
2756 END IF;
2757
2758 END IF;--If created_from_list_line_type = 'PBH'
2759
2760 IF l_debug = FND_API.G_TRUE THEN
2761 QP_PREQ_GRP.engine_debug('line_index '||
2762 g_limit_balance_line(j).line_index);
2763 QP_PREQ_GRP.engine_debug('limit_code '||
2764 g_limit_balance_line(j).limit_code);
2765 QP_PREQ_GRP.engine_debug('list_line_id '||
2766 g_limit_balance_line(j).list_line_id);
2767 QP_PREQ_GRP.engine_debug('benefit_qty '||
2768 g_limit_balance_line(j).benefit_qty);
2769 QP_PREQ_GRP.engine_debug('operand_value '||
2770 g_limit_balance_line(j).operand_value);
2771
2772 END IF;
2773 /*
2774 INDX,qp_limit_balance_check_pvt.process_limits.upd9,qp_npreq_lines_tmp_N1,LINE_INDEX,1
2775 */
2776 --sql statement upd9
2777
2778 UPDATE qp_npreq_lines_tmp
2779 SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
2780 WHERE line_index = g_limit_balance_line(j).line_index;
2781
2782 IF g_limit_balance_line(j).transaction_amount is null THEN
2783
2784 IF l_debug = FND_API.G_TRUE THEN
2785 QP_PREQ_GRP.engine_debug('transaction amount is null');
2786 END IF;
2787
2788 IF l_debug = FND_API.G_TRUE THEN
2789 QP_PREQ_GRP.engine_debug('Inserting Into qp_limit_transactions');
2790 END IF;
2791
2792 INSERT INTO qp_limit_transactions
2793 (
2794 limit_balance_id,
2795 creation_date,
2796 created_by,
2797 last_update_date,
2798 last_updated_by,
2799 list_header_id,
2800 list_line_id,
2801 price_request_date,
2802 price_request_type_code,
2803 price_request_code,
2804 pricing_phase_id,
2805 amount
2806 )
2807 VALUES
2808 (
2809 g_limit_balance_line(j).limit_balance_id,
2810 sysdate,
2811 fnd_global.user_id,
2812 sysdate,
2813 fnd_global.user_id,
2814 g_limit_balance_line(j).list_header_id,
2815 g_limit_balance_line(j).list_line_id,
2816 sysdate,
2817 g_limit_balance_line(j).request_type_code,
2818 g_limit_balance_line(j).price_request_code,
2819 g_limit_balance_line(j).pricing_phase_id,
2820 g_limit_balance_line(j).given_amount
2821 );
2822
2823 ELSIF g_limit_balance_line(j).transaction_amount <>
2824 g_limit_balance_line(j).given_amount THEN
2825
2826 IF l_debug = FND_API.G_TRUE THEN
2827 QP_PREQ_GRP.engine_debug('Updating qp_limit_transactions');
2828
2829 END IF;
2830 /*
2831 INDX,qp_limit_balance_check_pvt.process_limits.upd10,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
2832 INDX,qp_limit_balance_check_pvt.process_limits.upd10,QP_LIMIT_TRANSACTIONS_U1,LIST_HEADER_ID,2
2833 INDX,qp_limit_balance_check_pvt.process_limits.upd10,QP_LIMIT_TRANSACTIONS_U1,LIST_LINE_ID,3
2834 INDX,qp_limit_balance_check_pvt.process_limits.upd10,QP_LIMIT_TRANSACTIONS_U1,LIMIT_BALANCE_ID,4
2835 */
2836 --sql statement upd10
2837 update qp_limit_transactions
2838 set amount = g_limit_balance_line(j).given_amount,
2839 last_update_date = sysdate,
2840 last_updated_by = fnd_global.user_id,
2841 price_request_date = sysdate
2842 where limit_balance_id = g_limit_balance_line(j).limit_balance_id
2843 and list_header_id = g_limit_balance_line(j).list_header_id
2844 and list_line_id = g_limit_balance_line(j).list_line_id
2845 and price_request_code =
2846 g_limit_balance_line(j).price_request_code;
2847
2848 END IF; --If transaction_amount is null
2849
2850 END LOOP; --through limit balance lines
2851 END IF; --g_limit_balance_line.COUNT > 0
2852
2853 --Clear the global plsql table storing limits info.
2854 g_limit_balance_line.DELETE;
2855 IF l_debug = FND_API.G_TRUE THEN
2856 QP_PREQ_GRP.engine_debug('***End Boundary condition processing***');
2857 END IF;
2858 EXIT;
2859 END IF; --If Update_Balance returns Error
2860 END LOOP; --Boundary condition loop
2861
2862 END IF; --IF l_old_limit_rec.created_from_list_line_id >= 0
2863
2864 IF l_debug = FND_API.G_TRUE THEN
2865 QP_PREQ_GRP.engine_debug('***End Process_Limits***');
2866
2867 END IF;
2868 EXCEPTION
2869 WHEN FND_API.G_EXC_ERROR THEN
2870
2871 x_return_status := FND_API.G_RET_STS_ERROR;
2872 --x_return_text is already set by Update_Balance
2873
2874 IF l_debug = FND_API.G_TRUE THEN
2875 QP_PREQ_GRP.engine_debug('Expected Error in Process_Limits');
2876 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2877
2878 END IF;
2879 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2880
2881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2882 x_return_text := substr(sqlerrm, 1, 2000);
2883
2884 IF l_debug = FND_API.G_TRUE THEN
2885 QP_PREQ_GRP.engine_debug('Unexpected Error in Process_Limits');
2886 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2887
2888 END IF;
2889 WHEN OTHERS THEN
2890
2891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2892 x_return_text := substr(sqlerrm, 1, 2000);
2893
2894 IF l_debug = FND_API.G_TRUE THEN
2895 QP_PREQ_GRP.engine_debug('Other Error in Process_Limits');
2896 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2897
2898 END IF;
2899 END Process_Limits;
2900
2901 END QP_LIMIT_BALANCE_CHECK_PVT;