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