[Home] [Help]
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);
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
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
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:
53: where list_line_id = p_List_Line_Id;
54: EXCEPTION
55: when no_data_found then
56: IF l_debug = FND_API.G_TRUE THEN
57: QP_PREQ_GRP.engine_debug('*** list line not found for id ***' || p_List_Line_Id);
58: END IF;
59: END;
60: END IF;
61:
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
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
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;
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;
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);
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);
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:
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:
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
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
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;
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.
179: --Perform Update or Insert into qp_limit_balances as required.
180: FOR i IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
181: LOOP
182: IF l_debug = FND_API.G_TRUE THEN
183: QP_PREQ_GRP.engine_debug('Processing limit '||g_limit_balance_line(i).limit_id);
184:
185: QP_PREQ_GRP.engine_debug('Limit Level Code '||
186: g_limit_balance_line(i).limit_level_code);
187: QP_PREQ_GRP.engine_debug('Organization Context '||
181: LOOP
182: IF l_debug = FND_API.G_TRUE THEN
183: QP_PREQ_GRP.engine_debug('Processing limit '||g_limit_balance_line(i).limit_id);
184:
185: QP_PREQ_GRP.engine_debug('Limit Level Code '||
186: g_limit_balance_line(i).limit_level_code);
187: QP_PREQ_GRP.engine_debug('Organization Context '||
188: g_limit_balance_line(i).organization_attr_context);
189: QP_PREQ_GRP.engine_debug('Organization Attribute '||
183: QP_PREQ_GRP.engine_debug('Processing limit '||g_limit_balance_line(i).limit_id);
184:
185: QP_PREQ_GRP.engine_debug('Limit Level Code '||
186: g_limit_balance_line(i).limit_level_code);
187: QP_PREQ_GRP.engine_debug('Organization Context '||
188: g_limit_balance_line(i).organization_attr_context);
189: QP_PREQ_GRP.engine_debug('Organization Attribute '||
190: g_limit_balance_line(i).organization_attribute);
191: QP_PREQ_GRP.engine_debug('Organization Attr Value '||
185: QP_PREQ_GRP.engine_debug('Limit Level Code '||
186: g_limit_balance_line(i).limit_level_code);
187: QP_PREQ_GRP.engine_debug('Organization Context '||
188: g_limit_balance_line(i).organization_attr_context);
189: QP_PREQ_GRP.engine_debug('Organization Attribute '||
190: g_limit_balance_line(i).organization_attribute);
191: QP_PREQ_GRP.engine_debug('Organization Attr Value '||
192: g_limit_balance_line(i).organization_attr_value);
193: QP_PREQ_GRP.engine_debug('Multival Attr1 Context '||
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 '||
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 '||
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 '||
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 '||
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 '||
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 '||
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);
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 '||
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:
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:
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;
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: /*
261:
262: --Calculated Limit Balance no longer available. Raise error.
263: IF SQL%ROWCOUNT = 0 THEN
264: IF l_debug = FND_API.G_TRUE THEN
265: QP_PREQ_GRP.engine_debug('Balance no longer available ');
266: END IF;
267: RAISE E_BALANCE_NOT_AVAILABLE;
268: END IF;
269:
268: END IF;
269:
270: ELSIF g_limit_balance_line(i).process_action = g_insert THEN
271: IF l_debug = FND_API.G_TRUE THEN
272: QP_PREQ_GRP.engine_debug('Insert Required');
273:
274: QP_PREQ_GRP.engine_debug('given_amount '|| l_given_amount);
275: QP_PREQ_GRP.engine_debug('transaction_amount '||
276: nvl(g_limit_balance_line(i).transaction_amount, 0));
270: ELSIF g_limit_balance_line(i).process_action = g_insert THEN
271: IF l_debug = FND_API.G_TRUE THEN
272: QP_PREQ_GRP.engine_debug('Insert Required');
273:
274: QP_PREQ_GRP.engine_debug('given_amount '|| l_given_amount);
275: QP_PREQ_GRP.engine_debug('transaction_amount '||
276: nvl(g_limit_balance_line(i).transaction_amount, 0));
277:
278: END IF;
271: IF l_debug = FND_API.G_TRUE THEN
272: QP_PREQ_GRP.engine_debug('Insert Required');
273:
274: QP_PREQ_GRP.engine_debug('given_amount '|| l_given_amount);
275: QP_PREQ_GRP.engine_debug('transaction_amount '||
276: nvl(g_limit_balance_line(i).transaction_amount, 0));
277:
278: END IF;
279: INSERT INTO qp_limit_balances
330:
331: END IF;
332:
333: IF l_debug = FND_API.G_TRUE THEN
334: QP_PREQ_GRP.engine_debug('amount given'||l_given_amount);
335: END IF;
336: g_limit_balance_line(i).given_amount := l_given_amount;
337: g_limit_balance_line(i).least_percent := l_percent;
338:
369:
370: COMMIT;
371:
372: IF l_debug = FND_API.G_TRUE THEN
373: QP_PREQ_GRP.engine_debug('***End Update_Balance***');
374: QP_PREQ_GRP.engine_debug('------------------------');
375:
376: END IF;
377: l_return_status := FND_API.G_RET_STS_SUCCESS;
370: COMMIT;
371:
372: IF l_debug = FND_API.G_TRUE THEN
373: QP_PREQ_GRP.engine_debug('***End Update_Balance***');
374: QP_PREQ_GRP.engine_debug('------------------------');
375:
376: END IF;
377: l_return_status := FND_API.G_RET_STS_SUCCESS;
378: x_return_text := 'Success';
381:
382: EXCEPTION
383: WHEN DUP_VAL_ON_INDEX THEN
384: IF l_debug = FND_API.G_TRUE THEN
385: QP_PREQ_GRP.engine_debug('Insert Failed with Duplicate Value on Index');
386: END IF;
387: l_return_status := FND_API.G_RET_STS_ERROR;
388: x_return_text := 'Insert Failed with Duplicate Value on Index error ' ||
389: 'in procedure Update_Balance';
391: RETURN l_return_status;
392:
393: WHEN E_BALANCE_NOT_AVAILABLE THEN
394: IF l_debug = FND_API.G_TRUE THEN
395: QP_PREQ_GRP.engine_debug('Limit Balance no longer available.Update Failed.');
396: END IF;
397: l_return_status := FND_API.G_RET_STS_ERROR;
398: x_return_text := 'Update Failed in procedure Update_Balance because ' ||
399: 'Limit Balance no longer available';
401: RETURN l_return_status;
402:
403: WHEN OTHERS THEN
404: IF l_debug = FND_API.G_TRUE THEN
405: QP_PREQ_GRP.engine_debug('Other Exception in Update_Balance');
406: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
407: END IF;
408: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409: x_return_text := substr(sqlerrm, 1, 2000);
402:
403: WHEN OTHERS THEN
404: IF l_debug = FND_API.G_TRUE THEN
405: QP_PREQ_GRP.engine_debug('Other Exception in Update_Balance');
406: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
407: END IF;
408: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409: x_return_text := substr(sqlerrm, 1, 2000);
410: ROLLBACK;
425: l_message VARCHAR2(240);
426:
427: BEGIN
428:
429: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
430: --Increment the recheck loop count everytime the Function is entered.
431: G_LOOP_COUNT := G_LOOP_COUNT + 1;
432:
433: IF l_debug = FND_API.G_TRUE THEN
430: --Increment the recheck loop count everytime the Function is entered.
431: G_LOOP_COUNT := G_LOOP_COUNT + 1;
432:
433: IF l_debug = FND_API.G_TRUE THEN
434: QP_PREQ_GRP.engine_debug('***Begin Recheck_Balance***');
435:
436: END IF;
437: IF g_limit_balance_line.COUNT > 0 THEN
438:
439: FOR i IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
440: LOOP
441: IF g_limit_balance_line(i).each_attr_exists = 'N' THEN
442: IF l_debug = FND_API.G_TRUE THEN
443: QP_PREQ_GRP.engine_debug('Each attr does not exist');
444: END IF;
445: BEGIN
446:
447: IF g_limit_balance_line(i).limit_level_code = 'ACROSS_TRANSACTION' THEN
475: g_limit_balance_line(i).process_action := g_insert;
476:
477: WHEN TOO_MANY_ROWS THEN
478: IF l_debug = FND_API.G_TRUE THEN
479: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
480:
481: END IF;
482: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
483: THEN
491: */
492: --sql statement upd1
493: UPDATE qp_npreq_lines_tmp
494: SET pricing_status_text = l_message,
495: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
496: WHERE line_index = g_limit_balance_line(i).line_index;
497:
498: --Update the ldets_tmp table with the translated error message.
499: /*
502: */
503: --sql statement upd2
504: UPDATE qp_npreq_ldets_tmp
505: SET pricing_status_text = l_message,
506: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
507: WHERE line_index = g_limit_balance_line(i).line_index
508: AND created_from_list_line_id =
509: g_limit_balance_line(i).list_line_id;
510:
508: AND created_from_list_line_id =
509: g_limit_balance_line(i).list_line_id;
510:
511: IF l_debug = FND_API.G_TRUE THEN
512: QP_PREQ_GRP.engine_debug(l_message);
513:
514: END IF;
515: END IF;
516:
520: RAISE;
521:
522: WHEN OTHERS THEN
523: IF l_debug = FND_API.G_TRUE THEN
524: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
525:
526: END IF;
527: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
528: THEN
535: */
536: --sql statement upd3
537: UPDATE qp_npreq_lines_tmp
538: SET pricing_status_text = l_message,
539: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
540: WHERE line_index = g_limit_balance_line(i).line_index;
541:
542: --Update the ldets_tmp table with the translated error message.
543: /*
546: */
547: --sql statement upd4
548: UPDATE qp_npreq_ldets_tmp
549: SET pricing_status_text = l_message,
550: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
551: WHERE line_index = g_limit_balance_line(i).line_index
552: AND created_from_list_line_id =
553: g_limit_balance_line(i).list_line_id;
554:
552: AND created_from_list_line_id =
553: g_limit_balance_line(i).list_line_id;
554:
555: IF l_debug = FND_API.G_TRUE THEN
556: QP_PREQ_GRP.engine_debug(l_message);
557:
558: END IF;
559: END IF;
560:
566: END;--End of Block around Select Stmt when limit does not have each attrs
567:
568: ELSIF g_limit_balance_line(i).each_attr_exists = 'Y' THEN
569: IF l_debug = FND_API.G_TRUE THEN
570: QP_PREQ_GRP.engine_debug('Each attr exists');
571: END IF;
572: BEGIN
573:
574: IF g_limit_balance_line(i).limit_level_code = 'ACROSS_TRANSACTION' THEN
602: g_limit_balance_line(i).process_action := g_insert;
603:
604: WHEN TOO_MANY_ROWS THEN
605: IF l_debug = FND_API.G_TRUE THEN
606: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
607:
608: END IF;
609: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
610: THEN
618: */
619: --sql statement upd5
620: UPDATE qp_npreq_lines_tmp
621: SET pricing_status_text = l_message,
622: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
623: WHERE line_index = g_limit_balance_line(i).line_index;
624:
625: --Update the ldets_tmp table with the translated error message.
626: /*
629: */
630: --sql statement upd6
631: UPDATE qp_npreq_ldets_tmp
632: SET pricing_status_text = l_message,
633: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
634: WHERE line_index = g_limit_balance_line(i).line_index
635: AND created_from_list_line_id =
636: g_limit_balance_line(i).list_line_id;
637:
635: AND created_from_list_line_id =
636: g_limit_balance_line(i).list_line_id;
637:
638: IF l_debug = FND_API.G_TRUE THEN
639: QP_PREQ_GRP.engine_debug(l_message);
640:
641: END IF;
642: END IF;
643:
647: RAISE;
648:
649: WHEN OTHERS THEN
650: IF l_debug = FND_API.G_TRUE THEN
651: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
652:
653: END IF;
654: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
655: THEN
662: */
663: --sql statement upd7
664: UPDATE qp_npreq_lines_tmp
665: SET pricing_status_text = l_message,
666: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
667: WHERE line_index = g_limit_balance_line(i).line_index;
668:
669: --Update the ldets_tmp table with the translated error message.
670: /*
673: */
674: --sql statement upd8
675: UPDATE qp_npreq_ldets_tmp
676: SET pricing_status_text = l_message,
677: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
678: WHERE line_index = g_limit_balance_line(i).line_index
679: AND created_from_list_line_id =
680: g_limit_balance_line(i).list_line_id;
681:
679: AND created_from_list_line_id =
680: g_limit_balance_line(i).list_line_id;
681:
682: IF l_debug = FND_API.G_TRUE THEN
683: QP_PREQ_GRP.engine_debug(l_message);
684:
685: END IF;
686: END IF;
687:
694:
695: END IF; --If each_attr_exists
696:
697: IF l_debug = FND_API.G_TRUE THEN
698: QP_PREQ_GRP.engine_debug('Action to take '||g_limit_balance_line(i).process_action);
699:
700: END IF;
701: --Check the Limit Transaction Table to see if the same request has a
702: --record. If so, this is a repricing request so populate the
743: least(l_full_available_amount, g_limit_balance_line(i).wanted_amount);
744:
745: IF g_limit_balance_line(i).wanted_amount > l_full_available_amount THEN
746: g_limit_balance_line(i).limit_code :=
747: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
748: ELSE
749: g_limit_balance_line(i).limit_code :=
750: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
751: END IF; --if p_wanted_amount > l_full_available_amount
746: g_limit_balance_line(i).limit_code :=
747: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
748: ELSE
749: g_limit_balance_line(i).limit_code :=
750: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
751: END IF; --if p_wanted_amount > l_full_available_amount
752:
753: ELSE --Soft Limit
754:
757:
758: IF g_limit_balance_line(i).line_category = 'RETURN' THEN
759:
760: g_limit_balance_line(i).limit_code :=
761: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
762:
763: ELSE --If line_category is not 'RETURN'
764:
765: IF g_limit_balance_line(i).wanted_amount > l_full_available_amount
764:
765: IF g_limit_balance_line(i).wanted_amount > l_full_available_amount
766: THEN
767: g_limit_balance_line(i).limit_code :=
768: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
769: ELSE
770: g_limit_balance_line(i).limit_code :=
771: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
772: END IF; --If wanted_amount > l_full_available_amount
767: g_limit_balance_line(i).limit_code :=
768: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
769: ELSE
770: g_limit_balance_line(i).limit_code :=
771: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
772: END IF; --If wanted_amount > l_full_available_amount
773:
774: END IF;--If line_category is 'RETURN'
775:
790:
791: END IF; --g_limit_balance_line.COUNT > 0
792:
793: IF l_debug = FND_API.G_TRUE THEN
794: QP_PREQ_GRP.engine_debug('***End Recheck_Balance***');
795:
796: END IF;
797: EXCEPTION
798: WHEN OTHERS THEN
796: END IF;
797: EXCEPTION
798: WHEN OTHERS THEN
799: IF l_debug = FND_API.G_TRUE THEN
800: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
801: END IF;
802: RETURN FALSE;
803: END Recheck_Balance;
804:
832: FROM qp_npreq_line_attrs_tmp
833: WHERE line_index = a_line_index
834: AND context = a_context
835: AND attribute = a_attribute
836: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
837:
838: TYPE each_attr_rec_type IS RECORD
839: (context VARCHAR2(30),
840: attribute VARCHAR2(30),
855: E_ORDER_PRICE_REQ_CODE_NULL EXCEPTION;
856:
857: BEGIN
858:
859: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
860: IF l_debug = FND_API.G_TRUE THEN
861: QP_PREQ_GRP.engine_debug('***Begin Check_Balance***');
862: END IF;
863: IF g_Limit_balance_line.COUNT = 0 THEN
857: BEGIN
858:
859: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
860: IF l_debug = FND_API.G_TRUE THEN
861: QP_PREQ_GRP.engine_debug('***Begin Check_Balance***');
862: END IF;
863: IF g_Limit_balance_line.COUNT = 0 THEN
864: i:=1;
865: ELSE
867: END IF;
868: x_skip_limit := FALSE;
869:
870: IF l_debug = FND_API.G_TRUE THEN
871: QP_PREQ_GRP.engine_debug('populating limit_balance_line '||i);
872: QP_PREQ_GRP.engine_debug('limit_id '||p_limit_rec.limit_id);
873:
874: END IF;
875: IF p_limit_rec.each_attr_exists = 'N' THEN
868: x_skip_limit := FALSE;
869:
870: IF l_debug = FND_API.G_TRUE THEN
871: QP_PREQ_GRP.engine_debug('populating limit_balance_line '||i);
872: QP_PREQ_GRP.engine_debug('limit_id '||p_limit_rec.limit_id);
873:
874: END IF;
875: IF p_limit_rec.each_attr_exists = 'N' THEN
876: --If limit has no each (pure non-each) or no attributes
875: IF p_limit_rec.each_attr_exists = 'N' THEN
876: --If limit has no each (pure non-each) or no attributes
877: --(For such cases a balance record will always exist since setup creates one)
878: IF l_debug = FND_API.G_TRUE THEN
879: QP_PREQ_GRP.engine_debug('No Each-type attributes defined for this limit');
880:
881: END IF;
882: g_limit_balance_line(i).limit_id := p_limit_rec.limit_id;
883: g_limit_balance_line(i).line_index := p_limit_rec.line_index;
906: IF p_limit_rec.limit_level_code = 'ACROSS_TRANSACTION' THEN
907: g_limit_balance_line(i).bal_price_request_code := NULL;
908: ELSIF p_limit_rec.limit_level_code = 'TRANSACTION' THEN
909: g_limit_balance_line(i).bal_price_request_code :=
910: QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE;
911: END IF; --If limit_level_code = 'ACROSS_TRANSACTION'
912: IF l_debug = FND_API.G_TRUE THEN
913: QP_PREQ_GRP.engine_debug('bal_price_request_code '||
914: g_limit_balance_line(i).bal_price_request_code);
909: g_limit_balance_line(i).bal_price_request_code :=
910: QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE;
911: END IF; --If limit_level_code = 'ACROSS_TRANSACTION'
912: IF l_debug = FND_API.G_TRUE THEN
913: QP_PREQ_GRP.engine_debug('bal_price_request_code '||
914: g_limit_balance_line(i).bal_price_request_code);
915: END IF;
916: --Populate price_request_code from limits_cur for limit_transns table
917: g_limit_balance_line(i).price_request_code :=
940: INDX,qp_limit_balance_check_pvt.check_balance.sel2,QP_LIMIT_BALANCES_N1,PRICE_REQUEST_CODE,2
941: */
942:
943: --If g_order_price_request_code is null then raise an error.
944: IF QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE IS NULL THEN
945: RAISE E_ORDER_PRICE_REQ_CODE_NULL;
946: END IF;
947:
948: --sql statement sel2
957:
958: g_limit_balance_line(i).process_action := g_update;
959:
960: IF l_debug = FND_API.G_TRUE THEN
961: QP_PREQ_GRP.engine_debug('Available Balance '|| l_full_available_amount);
962:
963: END IF;
964: EXCEPTION
965: WHEN NO_DATA_FOUND THEN
967: into g_limit_balance_line(i).limit_balance_id from dual;
968: l_full_available_amount := p_limit_rec.amount;
969: g_limit_balance_line(i).process_action := g_insert;
970: IF l_debug = FND_API.G_TRUE THEN
971: QP_PREQ_GRP.engine_debug('Balance Record Missing');
972:
973: END IF;
974: WHEN E_ORDER_PRICE_REQ_CODE_NULL THEN
975:
984: */
985: --sql statement upd9
986: UPDATE qp_npreq_lines_tmp
987: SET pricing_status_text = l_message,
988: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
989: WHERE line_index = g_limit_balance_line(i).line_index;
990:
991: --Update the ldets_tmp table with the translated error message.
992: /*
995: */
996: --sql statement upd10
997: UPDATE qp_npreq_ldets_tmp
998: SET pricing_status_text = l_message,
999: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1000: WHERE line_index = g_limit_balance_line(i).line_index
1001: AND created_from_list_line_id =
1002: g_limit_balance_line(i).list_line_id;
1003:
1001: AND created_from_list_line_id =
1002: g_limit_balance_line(i).list_line_id;
1003:
1004: IF l_debug = FND_API.G_TRUE THEN
1005: QP_PREQ_GRP.engine_debug(l_message);
1006:
1007: END IF;
1008: END IF;
1009:
1008: END IF;
1009:
1010: WHEN TOO_MANY_ROWS THEN
1011: IF l_debug = FND_API.G_TRUE THEN
1012: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1013:
1014: END IF;
1015: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1016: THEN
1024: */
1025: --sql statement upd1
1026: UPDATE qp_npreq_lines_tmp
1027: SET pricing_status_text = l_message,
1028: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1029: WHERE line_index = g_limit_balance_line(i).line_index;
1030:
1031: --Update the ldets_tmp table with the translated error message.
1032: /*
1035: */
1036: --sql statement upd2
1037: UPDATE qp_npreq_ldets_tmp
1038: SET pricing_status_text = l_message,
1039: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1040: WHERE line_index = g_limit_balance_line(i).line_index
1041: AND created_from_list_line_id =
1042: g_limit_balance_line(i).list_line_id;
1043:
1041: AND created_from_list_line_id =
1042: g_limit_balance_line(i).list_line_id;
1043:
1044: IF l_debug = FND_API.G_TRUE THEN
1045: QP_PREQ_GRP.engine_debug(l_message);
1046:
1047: END IF;
1048: END IF;
1049:
1053: RAISE;
1054:
1055: WHEN OTHERS THEN
1056: IF l_debug = FND_API.G_TRUE THEN
1057: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1058:
1059: END IF;
1060: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1061: THEN
1068: */
1069: --sql statement upd3
1070: UPDATE qp_npreq_lines_tmp
1071: SET pricing_status_text = l_message,
1072: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1073: WHERE line_index = g_limit_balance_line(i).line_index;
1074:
1075: --Update the ldets_tmp table with the translated error message.
1076: /*
1079: */
1080: --sql statement upd4
1081: UPDATE qp_npreq_ldets_tmp
1082: SET pricing_status_text = l_message,
1083: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1084: WHERE line_index = g_limit_balance_line(i).line_index
1085: AND created_from_list_line_id =
1086: g_limit_balance_line(i).list_line_id;
1087:
1085: AND created_from_list_line_id =
1086: g_limit_balance_line(i).list_line_id;
1087:
1088: IF l_debug = FND_API.G_TRUE THEN
1089: QP_PREQ_GRP.engine_debug(l_message);
1090:
1091: END IF;
1092: END IF;
1093:
1143: least(l_full_available_amount, p_wanted_amount);
1144:
1145: IF p_wanted_amount > l_full_available_amount THEN
1146: g_limit_balance_line(i).limit_code :=
1147: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
1148: ELSE
1149: g_limit_balance_line(i).limit_code :=
1150: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1151: END IF; --if p_wanted_amount > l_full_available_amount
1146: g_limit_balance_line(i).limit_code :=
1147: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
1148: ELSE
1149: g_limit_balance_line(i).limit_code :=
1150: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1151: END IF; --if p_wanted_amount > l_full_available_amount
1152:
1153: ELSE --Soft Limit
1154:
1156:
1157: IF g_limit_balance_line(i).line_category = 'RETURN' THEN
1158:
1159: g_limit_balance_line(i).limit_code :=
1160: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1161:
1162: ELSE--If line_category is not 'RETURN'
1163:
1164: IF p_wanted_amount > l_full_available_amount
1163:
1164: IF p_wanted_amount > l_full_available_amount
1165: THEN
1166: g_limit_balance_line(i).limit_code :=
1167: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
1168: ELSE
1169: g_limit_balance_line(i).limit_code :=
1170: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1171: END IF; --If wanted_amount > l_full_available_amount
1166: g_limit_balance_line(i).limit_code :=
1167: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
1168: ELSE
1169: g_limit_balance_line(i).limit_code :=
1170: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1171: END IF; --If wanted_amount > l_full_available_amount
1172:
1173: END IF;--If line_category is 'RETURN'
1174:
1174:
1175: END IF; --If limit_exceed_action_code = 'HARD'
1176:
1177: IF l_debug = FND_API.G_TRUE THEN
1178: QP_PREQ_GRP.engine_debug('p_wanted_amount '||p_wanted_amount);
1179: QP_PREQ_GRP.engine_debug('available_amount '||
1180: g_limit_balance_line(i).available_amount);
1181:
1182: END IF;
1175: END IF; --If limit_exceed_action_code = 'HARD'
1176:
1177: IF l_debug = FND_API.G_TRUE THEN
1178: QP_PREQ_GRP.engine_debug('p_wanted_amount '||p_wanted_amount);
1179: QP_PREQ_GRP.engine_debug('available_amount '||
1180: g_limit_balance_line(i).available_amount);
1181:
1182: END IF;
1183: IF p_wanted_amount <> 0 THEN
1187: g_limit_balance_line(i).available_percent := 100;
1188: END IF;
1189:
1190: IF l_debug = FND_API.G_TRUE THEN
1191: QP_PREQ_GRP.engine_debug('available_percent '||
1192: g_limit_balance_line(i).available_percent);
1193: END IF;
1194:
1195: g_limit_balance_line(i).hard_limit_exceeded := FALSE;
1193: END IF;
1194:
1195: g_limit_balance_line(i).hard_limit_exceeded := FALSE;
1196: IF l_debug = FND_API.G_TRUE THEN
1197: QP_PREQ_GRP.engine_debug('***End Check_Balance***');
1198: END IF;
1199: RETURN TRUE;
1200:
1201: ELSIF p_limit_rec.each_attr_exists = 'Y' THEN
1201: ELSIF p_limit_rec.each_attr_exists = 'Y' THEN
1202: --Mixed case where both Each and Non-Each Attributes exist for the limit
1203: --and pure Each case
1204: IF l_debug = FND_API.G_TRUE THEN
1205: QP_PREQ_GRP.engine_debug('Each-type attributes defined for this limit');
1206: END IF;
1207:
1208: --Fetch all the org, customer and item type context,attribute and value
1209: --combinations for the current list_line from the request line attrs table.
1237: i3 := i3+1;
1238: END LOOP;
1239:
1240: IF l_debug = FND_API.G_TRUE THEN
1241: QP_PREQ_GRP.engine_debug('Organization Attr Count '|| l_org_table.count);
1242: QP_PREQ_GRP.engine_debug('Attribute1 Count '|| l_cust_table.count);
1243: QP_PREQ_GRP.engine_debug('Attribute2 Count '|| l_item_table.count);
1244:
1245: END IF;
1238: END LOOP;
1239:
1240: IF l_debug = FND_API.G_TRUE THEN
1241: QP_PREQ_GRP.engine_debug('Organization Attr Count '|| l_org_table.count);
1242: QP_PREQ_GRP.engine_debug('Attribute1 Count '|| l_cust_table.count);
1243: QP_PREQ_GRP.engine_debug('Attribute2 Count '|| l_item_table.count);
1244:
1245: END IF;
1246: --If for any of org, customer and item limit attribute setup for 'EACH'
1239:
1240: IF l_debug = FND_API.G_TRUE THEN
1241: QP_PREQ_GRP.engine_debug('Organization Attr Count '|| l_org_table.count);
1242: QP_PREQ_GRP.engine_debug('Attribute1 Count '|| l_cust_table.count);
1243: QP_PREQ_GRP.engine_debug('Attribute2 Count '|| l_item_table.count);
1244:
1245: END IF;
1246: --If for any of org, customer and item limit attribute setup for 'EACH'
1247: --value doesn't have any corresponding records from the request attrs table
1253: OR p_limit_rec.multival_attr2_context <> 'NA' AND
1254: l_item_table.COUNT = 0
1255: THEN
1256: IF l_debug = FND_API.G_TRUE THEN
1257: QP_PREQ_GRP.engine_debug('Skipping Limit '|| p_limit_rec.limit_id);
1258: END IF;
1259: x_skip_limit := TRUE;
1260: RETURN TRUE;
1261: END IF;
1323: IF p_limit_rec.limit_level_code = 'ACROSS_TRANSACTION' THEN
1324: g_limit_balance_line(i).bal_price_request_code := NULL;
1325: ELSIF p_limit_rec.limit_level_code = 'TRANSACTION' THEN
1326: g_limit_balance_line(i).bal_price_request_code :=
1327: QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE;
1328: END IF;--If limit_level_code = 'ACROSS_TRANSACTION'
1329: IF l_debug = FND_API.G_TRUE THEN
1330: QP_PREQ_GRP.engine_debug('bal_price_request_code '||
1331: g_limit_balance_line(i).bal_price_request_code);
1326: g_limit_balance_line(i).bal_price_request_code :=
1327: QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE;
1328: END IF;--If limit_level_code = 'ACROSS_TRANSACTION'
1329: IF l_debug = FND_API.G_TRUE THEN
1330: QP_PREQ_GRP.engine_debug('bal_price_request_code '||
1331: g_limit_balance_line(i).bal_price_request_code);
1332: END IF;
1333: --Populate price_request_code for limit_transactions table
1334: g_limit_balance_line(i).price_request_code :=
1410: INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,MULTIVAL_ATTR2_VALUE,10
1411: INDX,qp_limit_balance_check_pvt.check_balance.sel5,QP_LIMIT_BALANCES_U2,PRICE_REQUEST_CODE,11
1412: */
1413: --If g_order_price_request_code is null then raise an error.
1414: IF QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE IS NULL THEN
1415: RAISE E_ORDER_PRICE_REQ_CODE_NULL;
1416: END IF;
1417:
1418: --sql statement sel5
1458:
1459: g_limit_balance_line(i).process_action := g_update;
1460:
1461: IF l_debug = FND_API.G_TRUE THEN
1462: QP_PREQ_GRP.engine_debug('Available Balance '|| l_full_available_amount);
1463: END IF;
1464: EXCEPTION
1465: WHEN NO_DATA_FOUND THEN
1466:
1512: */
1513: --sql statement upd11
1514: UPDATE qp_npreq_lines_tmp
1515: SET pricing_status_text = l_message,
1516: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1517: WHERE line_index = g_limit_balance_line(i).line_index;
1518:
1519: --Update the ldets_tmp table with the translated error message.
1520: /*
1523: */
1524: --sql statement upd12
1525: UPDATE qp_npreq_ldets_tmp
1526: SET pricing_status_text = l_message,
1527: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1528: WHERE line_index = g_limit_balance_line(i).line_index
1529: AND created_from_list_line_id =
1530: g_limit_balance_line(i).list_line_id;
1531:
1529: AND created_from_list_line_id =
1530: g_limit_balance_line(i).list_line_id;
1531:
1532: IF l_debug = FND_API.G_TRUE THEN
1533: QP_PREQ_GRP.engine_debug(l_message);
1534:
1535: END IF;
1536: END IF;
1537:
1536: END IF;
1537:
1538: WHEN TOO_MANY_ROWS THEN
1539: IF l_debug = FND_API.G_TRUE THEN
1540: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1541:
1542: END IF;
1543: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1544: THEN
1552: */
1553: --sql statement upd5
1554: UPDATE qp_npreq_lines_tmp
1555: SET pricing_status_text = l_message,
1556: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1557: WHERE line_index = g_limit_balance_line(i).line_index;
1558:
1559: --Update the ldets_tmp table with the translated error message.
1560: /*
1564: --sql statement upd6
1565: UPDATE qp_npreq_ldets_tmp
1566: SET pricing_status_text = l_message,
1567: pricing_status_code =
1568: QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1569: WHERE line_index = g_limit_balance_line(i).line_index
1570: AND created_from_list_line_id =
1571: g_limit_balance_line(i).list_line_id;
1572:
1570: AND created_from_list_line_id =
1571: g_limit_balance_line(i).list_line_id;
1572:
1573: IF l_debug = FND_API.G_TRUE THEN
1574: QP_PREQ_GRP.engine_debug(l_message);
1575:
1576: END IF;
1577: END IF;
1578:
1582: RAISE;
1583:
1584: WHEN OTHERS THEN
1585: IF l_debug = FND_API.G_TRUE THEN
1586: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1587:
1588: END IF;
1589: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1590: THEN
1598: */
1599: --sql statement upd7
1600: UPDATE qp_npreq_lines_tmp
1601: SET pricing_status_text = l_message,
1602: pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1603: WHERE line_index = g_limit_balance_line(i).line_index;
1604:
1605: --Update the ldets_tmp table with the translated error message.
1606: /*
1610: --sql statement upd8
1611: UPDATE qp_npreq_ldets_tmp
1612: SET pricing_status_text = l_message,
1613: pricing_status_code =
1614: QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1615: WHERE line_index = g_limit_balance_line(i).line_index
1616: AND created_from_list_line_id =
1617: g_limit_balance_line(i).list_line_id;
1618:
1616: AND created_from_list_line_id =
1617: g_limit_balance_line(i).list_line_id;
1618:
1619: IF l_debug = FND_API.G_TRUE THEN
1620: QP_PREQ_GRP.engine_debug(l_message);
1621:
1622: END IF;
1623: END IF;
1624:
1672: least(l_full_available_amount, p_wanted_amount);
1673:
1674: IF p_wanted_amount > l_full_available_amount THEN
1675: g_limit_balance_line(i).limit_code :=
1676: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
1677: ELSE
1678: g_limit_balance_line(i).limit_code :=
1679: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1680: END IF; --if p_wanted_amount > l_full_available_amount
1675: g_limit_balance_line(i).limit_code :=
1676: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
1677: ELSE
1678: g_limit_balance_line(i).limit_code :=
1679: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1680: END IF; --if p_wanted_amount > l_full_available_amount
1681:
1682: ELSE --Soft Limit
1683: g_limit_balance_line(i).available_amount := p_wanted_amount;
1684:
1685: IF g_limit_balance_line(i).line_category = 'RETURN' THEN
1686:
1687: g_limit_balance_line(i).limit_code :=
1688: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1689:
1690: ELSE --If line_category is not 'RETURN'
1691:
1692: IF p_wanted_amount > l_full_available_amount
1691:
1692: IF p_wanted_amount > l_full_available_amount
1693: THEN
1694: g_limit_balance_line(i).limit_code :=
1695: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
1696: ELSE
1697: g_limit_balance_line(i).limit_code :=
1698: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1699: END IF; --If wanted_amount > l_full_available_amount
1694: g_limit_balance_line(i).limit_code :=
1695: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED;
1696: ELSE
1697: g_limit_balance_line(i).limit_code :=
1698: QP_PREQ_GRP.G_STATUS_LIMIT_CONSUMED;
1699: END IF; --If wanted_amount > l_full_available_amount
1700:
1701: END IF; --If line_category is 'RETURN'
1702:
1702:
1703: END IF; --If limit_exceed_action_code is 'HARD'
1704:
1705: IF l_debug = FND_API.G_TRUE THEN
1706: QP_PREQ_GRP.engine_debug('p_wanted_amount '||p_wanted_amount);
1707: QP_PREQ_GRP.engine_debug('available_amount '||
1708: g_limit_balance_line(i).available_amount);
1709:
1710: END IF;
1703: END IF; --If limit_exceed_action_code is 'HARD'
1704:
1705: IF l_debug = FND_API.G_TRUE THEN
1706: QP_PREQ_GRP.engine_debug('p_wanted_amount '||p_wanted_amount);
1707: QP_PREQ_GRP.engine_debug('available_amount '||
1708: g_limit_balance_line(i).available_amount);
1709:
1710: END IF;
1711: IF p_wanted_amount <> 0 THEN
1715: g_limit_balance_line(i).available_percent := 100;
1716: END IF;
1717:
1718: IF l_debug = FND_API.G_TRUE THEN
1719: QP_PREQ_GRP.engine_debug('available_percent '||
1720: g_limit_balance_line(i).available_percent);
1721:
1722: END IF;
1723: g_limit_balance_line(i).hard_limit_exceeded := FALSE;
1728: END LOOP; --over l_cust_table
1729: END LOOP; --over l_org_table
1730:
1731: IF l_debug = FND_API.G_TRUE THEN
1732: QP_PREQ_GRP.engine_debug('***End Check_balance***');
1733: END IF;
1734: RETURN TRUE;
1735:
1736: END IF; --If Each Attributes Exist for the Limit
1737:
1738: EXCEPTION
1739: WHEN OTHERS THEN
1740: IF l_debug = FND_API.G_TRUE THEN
1741: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1742: END IF;
1743: RETURN FALSE;
1744: END Check_Balance;
1745:
1809:
1810: EXCEPTION
1811: WHEN OTHERS THEN
1812: IF l_debug = FND_API.G_TRUE THEN
1813: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1814: END IF;
1815: RETURN FALSE;
1816: END Check_Balance_Wrapper;
1817:
1869: '%', q.unit_price * r.operand_value/100,
1870: r.operand_value),
1871: r.adjustment_amount * q.priced_quantity)) cost_wanted,
1872: /*decode(r.operand_calculation_code,
1873: QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
1874: r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
1875: r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
1876: q.priced_quantity quantity_wanted
1877: FROM qp_npreq_ldets_tmp r, qp_limits l,
1877: FROM qp_npreq_ldets_tmp r, qp_limits l,
1878: qp_limit_attributes a, qp_npreq_line_attrs_tmp rl, qp_npreq_lines_tmp q
1879: WHERE r.created_from_list_header_id = l.list_header_id
1880: AND r.created_from_list_line_id = l.list_line_id
1881: AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1882: AND r.applied_flag = 'Y' -- [5385851/5322832]
1883: AND r.header_limit_exists = 'Y' --common flag for both header and line
1884: AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
1885: AND l.limit_id = a.limit_id
1886: AND a.limit_attribute_context = rl.context
1887: AND a.limit_attribute = rl.attribute
1888: AND a.limit_attr_value = rl.value_from
1889: AND a.limit_attribute_type = rl.attribute_type
1890: AND rl.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
1891: AND r.line_index = rl.line_index
1892: AND r.line_index = q.line_index
1893: GROUP BY r.line_index, r.created_from_list_header_id,
1894: r.created_from_list_line_id, 'L', l.limit_id,
1938: '%', q.unit_price * r.operand_value/100,
1939: r.operand_value),
1940: r.adjustment_amount * q.priced_quantity)) cost_wanted,
1941: /*decode(r.operand_calculation_code,
1942: QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
1943: r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
1944: r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
1945: q.priced_quantity quantity_wanted
1946: FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
1945: q.priced_quantity quantity_wanted
1946: FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
1947: WHERE r.created_from_list_header_id = l.list_header_id
1948: AND r.created_from_list_line_id = l.list_line_id
1949: AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1950: AND r.applied_flag = 'Y' -- [5385851/5322832]
1951: AND r.header_limit_exists = 'Y' --common flag for both header and line
1952: AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
1953: AND r.line_index = q.line_index
1983: '%', q.unit_price * r.operand_value/100,
1984: r.operand_value),
1985: r.adjustment_amount * q.priced_quantity)) cost_wanted,
1986: /* decode(r.operand_calculation_code,
1987: QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
1988: r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
1989: r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
1990: q.priced_quantity quantity_wanted
1991: FROM qp_npreq_ldets_tmp r, qp_limits l,
1991: FROM qp_npreq_ldets_tmp r, qp_limits l,
1992: qp_limit_attributes a, qp_npreq_line_attrs_tmp rl, qp_npreq_lines_tmp q
1993: WHERE r.created_from_list_header_id = l.list_header_id
1994: AND l.list_line_id = -1
1995: AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1996: AND r.applied_flag = 'Y' -- [5385851/5322832]
1997: AND r.header_limit_exists = 'Y' --common flag for both header and line
1998: AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
1999: AND l.limit_id = a.limit_id
2000: AND a.limit_attribute_context = rl.context
2001: AND a.limit_attribute = rl.attribute
2002: AND a.limit_attr_value = rl.value_from
2003: AND a.limit_attribute_type = rl.attribute_type
2004: AND rl.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
2005: AND r.line_index = rl.line_index
2006: AND r.line_index = q.line_index
2007: GROUP BY r.line_index, r.created_from_list_header_id,
2008: r.created_from_list_line_id, 'H', l.limit_id,
2052: '%', q.unit_price * r.operand_value/100,
2053: r.operand_value),
2054: r.adjustment_amount * q.priced_quantity)) cost_wanted,
2055: /*decode(r.operand_calculation_code,
2056: QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
2057: r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
2058: r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
2059: q.priced_quantity quantity_wanted
2060: FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
2059: q.priced_quantity quantity_wanted
2060: FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
2061: WHERE r.created_from_list_header_id = l.list_header_id
2062: AND l.list_line_id = -1
2063: AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2064: AND r.applied_flag = 'Y' -- [5385851/5322832]
2065: AND r.header_limit_exists = 'Y' --common flag for both header and line
2066: AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
2067: AND r.line_index = q.line_index
2094: l_modifier_level_code VARCHAR2(30) := ''; --Bug 4457725
2095:
2096: BEGIN
2097:
2098: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2099: IF l_debug = FND_API.G_TRUE THEN
2100: QP_PREQ_GRP.engine_debug('***Begin Process_Limits***');
2101:
2102: END IF;
2096: BEGIN
2097:
2098: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2099: IF l_debug = FND_API.G_TRUE THEN
2100: QP_PREQ_GRP.engine_debug('***Begin Process_Limits***');
2101:
2102: END IF;
2103: --Initialize x_return_status
2104: x_return_status := FND_API.G_RET_STS_SUCCESS;
2115: FOR l_limit_rec IN limits_cur
2116: LOOP
2117:
2118: IF l_debug = FND_API.G_TRUE THEN
2119: QP_PREQ_GRP.engine_debug('========================');
2120: QP_PREQ_GRP.engine_debug('In limits_cur loop');
2121:
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2116: LOOP
2117:
2118: IF l_debug = FND_API.G_TRUE THEN
2119: QP_PREQ_GRP.engine_debug('========================');
2120: QP_PREQ_GRP.engine_debug('In limits_cur loop');
2121:
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2118: IF l_debug = FND_API.G_TRUE THEN
2119: QP_PREQ_GRP.engine_debug('========================');
2120: QP_PREQ_GRP.engine_debug('In limits_cur loop');
2121:
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2119: QP_PREQ_GRP.engine_debug('========================');
2120: QP_PREQ_GRP.engine_debug('In limits_cur loop');
2121:
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2120: QP_PREQ_GRP.engine_debug('In limits_cur loop');
2121:
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2121:
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129: QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2122: QP_PREQ_GRP.engine_debug('list header id of previous limit ' || l_old_limit_rec.created_from_list_header_id);
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129: QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2130: QP_PREQ_GRP.engine_debug('------------------------');
2123: QP_PREQ_GRP.engine_debug('list header id of current limit ' || l_limit_rec.created_from_list_header_id);
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129: QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2130: QP_PREQ_GRP.engine_debug('------------------------');
2131:
2124: QP_PREQ_GRP.engine_debug('------------------------');
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129: QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2130: QP_PREQ_GRP.engine_debug('------------------------');
2131:
2132: END IF;
2125: QP_PREQ_GRP.engine_debug('list_line_id of previous limit ' || l_old_limit_rec.created_from_list_line_id);
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129: QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2130: QP_PREQ_GRP.engine_debug('------------------------');
2131:
2132: END IF;
2133: --If Skip_line flag is true then skip all limits until list_line_id changes
2126: QP_PREQ_GRP.engine_debug('list_line_id of current limit ' || l_limit_rec.created_from_list_line_id);
2127: QP_PREQ_GRP.engine_debug('------------------------');
2128: QP_PREQ_GRP.engine_debug('line_index of previous limit ' || l_old_limit_rec.line_index);
2129: QP_PREQ_GRP.engine_debug('line_index of current limit ' || l_limit_rec.line_index);
2130: QP_PREQ_GRP.engine_debug('------------------------');
2131:
2132: END IF;
2133: --If Skip_line flag is true then skip all limits until list_line_id changes
2134: --or until line_index changes.
2138: l_limit_rec.line_index =
2139: l_old_limit_rec.line_index
2140: THEN
2141: IF l_debug = FND_API.G_TRUE THEN
2142: QP_PREQ_GRP.engine_debug('skipping limit '|| l_limit_rec.limit_id ||
2143: 'for line '||l_limit_rec.created_from_list_line_id);
2144:
2145: END IF;
2146: l_old_limit_rec := l_limit_rec;
2155: l_limit_rec.line_index =
2156: l_old_limit_rec.line_index
2157: THEN
2158: IF l_debug = FND_API.G_TRUE THEN
2159: QP_PREQ_GRP.engine_debug('skipping limit '|| l_limit_rec.limit_id ||
2160: 'for header '||l_limit_rec.created_from_list_header_id);
2161:
2162: END IF;
2163: l_old_limit_rec := l_limit_rec;
2183: --is applied only once for a group
2184: select MODIFIER_LEVEL_CODE into l_modifier_level_code
2185: from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
2186: IF l_debug = FND_API.G_TRUE THEN
2187: QP_PREQ_GRP.engine_debug('Modifier Level '||l_modifier_level_code);
2188: END IF;
2189:
2190: IF l_modifier_level_code = 'LINEGROUP' AND l_processed_limits_tbl.COUNT > 0
2191: THEN
2201:
2202: IF l_modifier_level_code = 'LINEGROUP' AND l_processed
2203: THEN
2204: IF l_debug = FND_API.G_TRUE THEN
2205: QP_PREQ_GRP.engine_debug('Line Group Modifier with limit id '||l_old_limit_rec.limit_id||' already processed');
2206: END IF;
2207: --Clear the global plsql table storing limits info.
2208: g_limit_balance_line.DELETE;
2209:
2214: G_LOOP_COUNT := 0; --Initialize whenever line_index or list_line_id
2215: --changes, i.e., before each loop
2216: LOOP
2217: IF l_debug = FND_API.G_TRUE THEN
2218: QP_PREQ_GRP.engine_debug('Change in list_line_id or line_index. ' ||
2219: 'Update limit balance. Loop through ' ||
2220: 'Recheck_balance and Update_Balance, if ' ||
2221: 'necessary ');
2222:
2260: FOR j IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
2261: LOOP
2262:
2263: IF (g_limit_balance_line(j).limit_code =
2264: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED)
2265: OR
2266: (g_limit_balance_line(j).limit_code =
2267: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED AND
2268: l_limit_code IS NULL)
2263: IF (g_limit_balance_line(j).limit_code =
2264: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED)
2265: OR
2266: (g_limit_balance_line(j).limit_code =
2267: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED AND
2268: l_limit_code IS NULL)
2269: THEN
2270:
2271: l_limit_code := g_limit_balance_line(j).limit_code;
2284: ,p_message_text => l_limit_text
2285: );
2286:
2287: IF l_debug = FND_API.G_TRUE THEN
2288: QP_PREQ_GRP.engine_debug('################# ' || l_limit_text);
2289:
2290: END IF;
2291: END IF;
2292:
2291: END IF;
2292:
2293: IF g_limit_balance_line(j).limit_hold_flag = 'Y' AND
2294: g_limit_balance_line(j).limit_code IN
2295: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2296: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2297: THEN
2298: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2299: END IF;
2292:
2293: IF g_limit_balance_line(j).limit_hold_flag = 'Y' AND
2294: g_limit_balance_line(j).limit_code IN
2295: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2296: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2297: THEN
2298: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2299: END IF;
2300:
2294: g_limit_balance_line(j).limit_code IN
2295: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2296: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2297: THEN
2298: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2299: END IF;
2300:
2301: IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2302: g_limit_balance_line(j).limit_code IN
2299: END IF;
2300:
2301: IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2302: g_limit_balance_line(j).limit_code IN
2303: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2304: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2305: THEN
2306: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2307: END IF;
2300:
2301: IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2302: g_limit_balance_line(j).limit_code IN
2303: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2304: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2305: THEN
2306: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2307: END IF;
2308:
2302: g_limit_balance_line(j).limit_code IN
2303: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2304: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2305: THEN
2306: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2307: END IF;
2308:
2309: IF l_debug = FND_API.G_TRUE THEN
2310: QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2306: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2307: END IF;
2308:
2309: IF l_debug = FND_API.G_TRUE THEN
2310: QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2311: QP_PREQ_GRP.engine_debug('Hold Code is ' || l_hold_code);
2312:
2313: END IF;
2314: --Do the following update for all list line types
2307: END IF;
2308:
2309: IF l_debug = FND_API.G_TRUE THEN
2310: QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2311: QP_PREQ_GRP.engine_debug('Hold Code is ' || l_hold_code);
2312:
2313: END IF;
2314: --Do the following update for all list line types
2315: /*
2325: limit_text = l_limit_text
2326: WHERE line_index = g_limit_balance_line(j).line_index
2327: AND created_from_list_line_id =
2328: g_limit_balance_line(j).list_line_id
2329: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2330:
2331: --Update the Child Break Lines for 'PBH' lines
2332: IF g_limit_balance_line(j).created_from_list_line_type = 'PBH'
2333: THEN
2363:
2364: WHERE a.line_index = g_limit_balance_line(j).line_index
2365: AND a.created_from_list_line_id =
2366: g_limit_balance_line(j).list_line_id
2367: AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2368: AND a.line_detail_index IN
2369: (SELECT b.related_line_detail_index
2370: FROM qp_npreq_rltd_lines_tmp b
2371: WHERE b.line_index = a.line_index
2375:
2376: END IF;--If created_from_list_line_type = 'PBH'
2377:
2378: IF l_debug = FND_API.G_TRUE THEN
2379: QP_PREQ_GRP.engine_debug('line_index '||
2380: g_limit_balance_line(j).line_index);
2381: QP_PREQ_GRP.engine_debug('limit_code '||
2382: g_limit_balance_line(j).limit_code);
2383: QP_PREQ_GRP.engine_debug('list_line_id '||
2377:
2378: IF l_debug = FND_API.G_TRUE THEN
2379: QP_PREQ_GRP.engine_debug('line_index '||
2380: g_limit_balance_line(j).line_index);
2381: QP_PREQ_GRP.engine_debug('limit_code '||
2382: g_limit_balance_line(j).limit_code);
2383: QP_PREQ_GRP.engine_debug('list_line_id '||
2384: g_limit_balance_line(j).list_line_id);
2385: QP_PREQ_GRP.engine_debug('benefit_qty '||
2379: QP_PREQ_GRP.engine_debug('line_index '||
2380: g_limit_balance_line(j).line_index);
2381: QP_PREQ_GRP.engine_debug('limit_code '||
2382: g_limit_balance_line(j).limit_code);
2383: QP_PREQ_GRP.engine_debug('list_line_id '||
2384: g_limit_balance_line(j).list_line_id);
2385: QP_PREQ_GRP.engine_debug('benefit_qty '||
2386: g_limit_balance_line(j).benefit_qty);
2387: QP_PREQ_GRP.engine_debug('operand_value '||
2381: QP_PREQ_GRP.engine_debug('limit_code '||
2382: g_limit_balance_line(j).limit_code);
2383: QP_PREQ_GRP.engine_debug('list_line_id '||
2384: g_limit_balance_line(j).list_line_id);
2385: QP_PREQ_GRP.engine_debug('benefit_qty '||
2386: g_limit_balance_line(j).benefit_qty);
2387: QP_PREQ_GRP.engine_debug('operand_value '||
2388: g_limit_balance_line(j).operand_value);
2389:
2383: QP_PREQ_GRP.engine_debug('list_line_id '||
2384: g_limit_balance_line(j).list_line_id);
2385: QP_PREQ_GRP.engine_debug('benefit_qty '||
2386: g_limit_balance_line(j).benefit_qty);
2387: QP_PREQ_GRP.engine_debug('operand_value '||
2388: g_limit_balance_line(j).operand_value);
2389:
2390: END IF;
2391: --Update Hold_Code
2393: INDX,qp_limit_balance_check_pvt.process_limits.upd3,qp_npreq_lines_tmp_N1,LINE_INDEX,1
2394: */
2395: --sql statement upd3
2396: UPDATE qp_npreq_lines_tmp
2397: SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
2398: WHERE line_index = g_limit_balance_line(j).line_index;
2399:
2400: IF l_debug = FND_API.G_TRUE THEN
2401: QP_PREQ_GRP.engine_debug('Hold Flag is Y');
2397: SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
2398: WHERE line_index = g_limit_balance_line(j).line_index;
2399:
2400: IF l_debug = FND_API.G_TRUE THEN
2401: QP_PREQ_GRP.engine_debug('Hold Flag is Y');
2402: QP_PREQ_GRP.engine_debug('Hold Code is '||QP_PREQ_GRP.G_STATUS_LIMIT_HOLD);
2403:
2404: END IF;
2405:
2398: WHERE line_index = g_limit_balance_line(j).line_index;
2399:
2400: IF l_debug = FND_API.G_TRUE THEN
2401: QP_PREQ_GRP.engine_debug('Hold Flag is Y');
2402: QP_PREQ_GRP.engine_debug('Hold Code is '||QP_PREQ_GRP.G_STATUS_LIMIT_HOLD);
2403:
2404: END IF;
2405:
2406: IF g_limit_balance_line(j).transaction_amount is null THEN
2460:
2461: END IF; --g_limit_balance_line.COUNT > 0
2462:
2463: IF l_debug = FND_API.G_TRUE THEN
2464: QP_PREQ_GRP.engine_debug('Done Updating Balances when line changed');
2465:
2466: END IF;
2467: --Clear the global plsql table storing limits info.
2468: g_limit_balance_line.DELETE;
2480:
2481: IF NOT l_limit_available THEN
2482:
2483: IF l_debug = FND_API.G_TRUE THEN
2484: QP_PREQ_GRP.engine_debug('Limit not available - hard limit with 0 balance');
2485:
2486: END IF;
2487: --If Header-level limit, then skip all limits and go to the next
2488: --list_header limit. Elseif line_level go to the next line_level limit.
2487: --If Header-level limit, then skip all limits and go to the next
2488: --list_header limit. Elseif line_level go to the next line_level limit.
2489: IF l_limit_rec.limit_level = 'H' THEN
2490: IF l_debug = FND_API.G_TRUE THEN
2491: QP_PREQ_GRP.engine_debug('Header Level Limit '||
2492: l_limit_rec.limit_id ||' not available');
2493: END IF;
2494: /*
2495: INDX,qp_limit_balance_check_pvt.process_limits.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2497: INDX,qp_limit_balance_check_pvt.process_limits.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
2498: */
2499: --sql statement upd5
2500: UPDATE qp_npreq_ldets_tmp
2501: SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
2502: WHERE created_from_list_header_id =
2503: l_limit_rec.created_from_list_header_id
2504: AND line_index = l_limit_rec.line_index
2505: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2501: SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
2502: WHERE created_from_list_header_id =
2503: l_limit_rec.created_from_list_header_id
2504: AND line_index = l_limit_rec.line_index
2505: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2506:
2507: l_skip_header := TRUE;
2508:
2509: ELSIF l_limit_rec.limit_level = 'L' THEN
2507: l_skip_header := TRUE;
2508:
2509: ELSIF l_limit_rec.limit_level = 'L' THEN
2510: IF l_debug = FND_API.G_TRUE THEN
2511: QP_PREQ_GRP.engine_debug('Line Level Limit '||
2512: l_limit_rec.limit_id ||' not available');
2513: END IF;
2514: /*
2515: INDX,qp_limit_balance_check_pvt.process_limits.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2517: INDX,qp_limit_balance_check_pvt.process_limits.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
2518: */
2519: --sql statement upd6
2520: UPDATE qp_npreq_ldets_tmp
2521: SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
2522: WHERE created_from_list_line_id =
2523: l_limit_rec.created_from_list_line_id
2524: AND line_index = l_limit_rec.line_index
2525: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2521: SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
2522: WHERE created_from_list_line_id =
2523: l_limit_rec.created_from_list_line_id
2524: AND line_index = l_limit_rec.line_index
2525: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2526:
2527: l_skip_line := TRUE;
2528:
2529: END IF;
2530:
2531: ELSIF l_limit_available AND l_skip_limit THEN
2532:
2533: IF l_debug = FND_API.G_TRUE THEN
2534: QP_PREQ_GRP.engine_debug('limit ' || l_limit_rec.limit_id ||
2535: ' available but' || 'skipped - does not qualify');
2536: END IF;
2537: l_skip_limit := FALSE;
2538: GOTO limits_loop; --to next record in limits_cur loop
2541:
2542: l_old_limit_rec := l_limit_rec;
2543:
2544: IF l_debug = FND_API.G_TRUE THEN
2545: QP_PREQ_GRP.engine_debug('Repeat limits_cur loop for next limit - if any');
2546: END IF;
2547: <
2548: null;
2549: END LOOP; --loop over main limits_cur
2558: G_LOOP_COUNT := 0; --Initialize before boundary condition loop
2559:
2560: LOOP
2561: IF l_debug = FND_API.G_TRUE THEN
2562: QP_PREQ_GRP.engine_debug('***Start Boundary condition processing***');
2563: END IF;
2564:
2565: select MODIFIER_LEVEL_CODE into l_modifier_level_code
2566: from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
2564:
2565: select MODIFIER_LEVEL_CODE into l_modifier_level_code
2566: from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
2567: IF l_debug = FND_API.G_TRUE THEN
2568: QP_PREQ_GRP.engine_debug('Modifier Level '||l_modifier_level_code);
2569: END IF;
2570:
2571: IF l_modifier_level_code = 'LINEGROUP' AND l_processed_limits_tbl.COUNT > 0
2572: THEN
2582:
2583: IF l_modifier_level_code = 'LINEGROUP' AND l_processed
2584: THEN
2585: IF l_debug = FND_API.G_TRUE THEN
2586: QP_PREQ_GRP.engine_debug('***Boundary condition GroupOfLines Limit already processed***');
2587: END IF;
2588: EXIT;
2589: END IF;
2590:
2595: l_processed_limit_count := l_processed_limit_count+1;
2596: END IF;
2597:
2598: IF l_debug = FND_API.G_TRUE THEN
2599: QP_PREQ_GRP.engine_debug('Update_Balance l_return_status ' ||l_return_status);
2600: END IF;
2601:
2602: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2603:
2625: FOR j IN g_limit_balance_line.FIRST..g_limit_balance_line.LAST
2626: LOOP
2627:
2628: IF g_limit_balance_line(j).limit_code =
2629: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED
2630: OR
2631: (g_limit_balance_line(j).limit_code =
2632: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED AND
2633: l_limit_code IS NULL)
2628: IF g_limit_balance_line(j).limit_code =
2629: QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED
2630: OR
2631: (g_limit_balance_line(j).limit_code =
2632: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED AND
2633: l_limit_code IS NULL)
2634: THEN
2635:
2636: l_limit_code := g_limit_balance_line(j).limit_code;
2635:
2636: l_limit_code := g_limit_balance_line(j).limit_code;
2637:
2638: IF l_debug = FND_API.G_TRUE THEN
2639: QP_PREQ_GRP.engine_debug('Before Buid_Message_Text');
2640: END IF;
2641:
2642: Build_Message_Text(
2643: p_List_Header_Id => g_limit_balance_line(j).list_header_id
2653: ,p_message_text => l_limit_text
2654: );
2655:
2656: IF l_debug = FND_API.G_TRUE THEN
2657: QP_PREQ_GRP.engine_debug('################# ' || l_limit_text);
2658:
2659: END IF;
2660: END IF;
2661:
2660: END IF;
2661:
2662: IF g_limit_balance_line(j).limit_hold_flag = 'Y' AND
2663: g_limit_balance_line(j).limit_code IN
2664: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2665: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2666: THEN
2667: IF l_debug = FND_API.G_TRUE THEN
2668: QP_PREQ_GRP.engine_debug('label 1');
2661:
2662: IF g_limit_balance_line(j).limit_hold_flag = 'Y' AND
2663: g_limit_balance_line(j).limit_code IN
2664: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2665: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2666: THEN
2667: IF l_debug = FND_API.G_TRUE THEN
2668: QP_PREQ_GRP.engine_debug('label 1');
2669: END IF;
2664: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2665: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2666: THEN
2667: IF l_debug = FND_API.G_TRUE THEN
2668: QP_PREQ_GRP.engine_debug('label 1');
2669: END IF;
2670: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2671: END IF;
2672:
2666: THEN
2667: IF l_debug = FND_API.G_TRUE THEN
2668: QP_PREQ_GRP.engine_debug('label 1');
2669: END IF;
2670: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_HOLD;
2671: END IF;
2672:
2673: IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2674: g_limit_balance_line(j).limit_code IN
2671: END IF;
2672:
2673: IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2674: g_limit_balance_line(j).limit_code IN
2675: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2676: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2677: THEN
2678: IF l_debug = FND_API.G_TRUE THEN
2679: QP_PREQ_GRP.engine_debug('label 2');
2672:
2673: IF g_limit_balance_line(j).limit_hold_flag = 'N' AND
2674: g_limit_balance_line(j).limit_code IN
2675: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2676: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2677: THEN
2678: IF l_debug = FND_API.G_TRUE THEN
2679: QP_PREQ_GRP.engine_debug('label 2');
2680: END IF;
2675: (QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED,
2676: QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED)
2677: THEN
2678: IF l_debug = FND_API.G_TRUE THEN
2679: QP_PREQ_GRP.engine_debug('label 2');
2680: END IF;
2681: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2682: END IF;
2683:
2677: THEN
2678: IF l_debug = FND_API.G_TRUE THEN
2679: QP_PREQ_GRP.engine_debug('label 2');
2680: END IF;
2681: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2682: END IF;
2683:
2684: IF l_debug = FND_API.G_TRUE THEN
2685: QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2681: l_hold_code := QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED;
2682: END IF;
2683:
2684: IF l_debug = FND_API.G_TRUE THEN
2685: QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2686: QP_PREQ_GRP.engine_debug('Hold Code is ' || l_hold_code);
2687:
2688: END IF;
2689: --Do the following update for all list line types
2682: END IF;
2683:
2684: IF l_debug = FND_API.G_TRUE THEN
2685: QP_PREQ_GRP.engine_debug('Hold Flag is ' || g_limit_balance_line(j).limit_hold_flag );
2686: QP_PREQ_GRP.engine_debug('Hold Code is ' || l_hold_code);
2687:
2688: END IF;
2689: --Do the following update for all list line types
2690: /*
2700: limit_text = l_limit_text
2701: WHERE line_index = g_limit_balance_line(j).line_index
2702: AND created_from_list_line_id =
2703: g_limit_balance_line(j).list_line_id
2704: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2705:
2706: IF l_debug = FND_API.G_TRUE THEN
2707: QP_PREQ_GRP.engine_debug('label 3');
2708: END IF;
2703: g_limit_balance_line(j).list_line_id
2704: AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
2705:
2706: IF l_debug = FND_API.G_TRUE THEN
2707: QP_PREQ_GRP.engine_debug('label 3');
2708: END IF;
2709:
2710: --Update the Child Break Lines for 'PBH' lines
2711: IF g_limit_balance_line(j).created_from_list_line_type = 'PBH' THEN
2741:
2742: WHERE a.line_index = g_limit_balance_line(j).line_index
2743: AND a.created_from_list_line_id =
2744: g_limit_balance_line(j).list_line_id
2745: AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2746: AND a.line_detail_index IN
2747: (SELECT b.related_line_detail_index
2748: FROM qp_npreq_rltd_lines_tmp b
2749: WHERE b.line_index = a.line_index
2751: AND b.line_detail_index =
2752: g_limit_balance_line(j).line_detail_index);
2753:
2754: IF l_debug = FND_API.G_TRUE THEN
2755: QP_PREQ_GRP.engine_debug('label 4');
2756: END IF;
2757:
2758: END IF;--If created_from_list_line_type = 'PBH'
2759:
2757:
2758: END IF;--If created_from_list_line_type = 'PBH'
2759:
2760: IF l_debug = FND_API.G_TRUE THEN
2761: QP_PREQ_GRP.engine_debug('line_index '||
2762: g_limit_balance_line(j).line_index);
2763: QP_PREQ_GRP.engine_debug('limit_code '||
2764: g_limit_balance_line(j).limit_code);
2765: QP_PREQ_GRP.engine_debug('list_line_id '||
2759:
2760: IF l_debug = FND_API.G_TRUE THEN
2761: QP_PREQ_GRP.engine_debug('line_index '||
2762: g_limit_balance_line(j).line_index);
2763: QP_PREQ_GRP.engine_debug('limit_code '||
2764: g_limit_balance_line(j).limit_code);
2765: QP_PREQ_GRP.engine_debug('list_line_id '||
2766: g_limit_balance_line(j).list_line_id);
2767: QP_PREQ_GRP.engine_debug('benefit_qty '||
2761: QP_PREQ_GRP.engine_debug('line_index '||
2762: g_limit_balance_line(j).line_index);
2763: QP_PREQ_GRP.engine_debug('limit_code '||
2764: g_limit_balance_line(j).limit_code);
2765: QP_PREQ_GRP.engine_debug('list_line_id '||
2766: g_limit_balance_line(j).list_line_id);
2767: QP_PREQ_GRP.engine_debug('benefit_qty '||
2768: g_limit_balance_line(j).benefit_qty);
2769: QP_PREQ_GRP.engine_debug('operand_value '||
2763: QP_PREQ_GRP.engine_debug('limit_code '||
2764: g_limit_balance_line(j).limit_code);
2765: QP_PREQ_GRP.engine_debug('list_line_id '||
2766: g_limit_balance_line(j).list_line_id);
2767: QP_PREQ_GRP.engine_debug('benefit_qty '||
2768: g_limit_balance_line(j).benefit_qty);
2769: QP_PREQ_GRP.engine_debug('operand_value '||
2770: g_limit_balance_line(j).operand_value);
2771:
2765: QP_PREQ_GRP.engine_debug('list_line_id '||
2766: g_limit_balance_line(j).list_line_id);
2767: QP_PREQ_GRP.engine_debug('benefit_qty '||
2768: g_limit_balance_line(j).benefit_qty);
2769: QP_PREQ_GRP.engine_debug('operand_value '||
2770: g_limit_balance_line(j).operand_value);
2771:
2772: END IF;
2773: /*
2775: */
2776: --sql statement upd9
2777:
2778: UPDATE qp_npreq_lines_tmp
2779: SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
2780: WHERE line_index = g_limit_balance_line(j).line_index;
2781:
2782: IF g_limit_balance_line(j).transaction_amount is null THEN
2783:
2781:
2782: IF g_limit_balance_line(j).transaction_amount is null THEN
2783:
2784: IF l_debug = FND_API.G_TRUE THEN
2785: QP_PREQ_GRP.engine_debug('transaction amount is null');
2786: END IF;
2787:
2788: IF l_debug = FND_API.G_TRUE THEN
2789: QP_PREQ_GRP.engine_debug('Inserting Into qp_limit_transactions');
2785: QP_PREQ_GRP.engine_debug('transaction amount is null');
2786: END IF;
2787:
2788: IF l_debug = FND_API.G_TRUE THEN
2789: QP_PREQ_GRP.engine_debug('Inserting Into qp_limit_transactions');
2790: END IF;
2791:
2792: INSERT INTO qp_limit_transactions
2793: (
2823: ELSIF g_limit_balance_line(j).transaction_amount <>
2824: g_limit_balance_line(j).given_amount THEN
2825:
2826: IF l_debug = FND_API.G_TRUE THEN
2827: QP_PREQ_GRP.engine_debug('Updating qp_limit_transactions');
2828:
2829: END IF;
2830: /*
2831: INDX,qp_limit_balance_check_pvt.process_limits.upd10,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
2852:
2853: --Clear the global plsql table storing limits info.
2854: g_limit_balance_line.DELETE;
2855: IF l_debug = FND_API.G_TRUE THEN
2856: QP_PREQ_GRP.engine_debug('***End Boundary condition processing***');
2857: END IF;
2858: EXIT;
2859: END IF; --If Update_Balance returns Error
2860: END LOOP; --Boundary condition loop
2861:
2862: END IF; --IF l_old_limit_rec.created_from_list_line_id >= 0
2863:
2864: IF l_debug = FND_API.G_TRUE THEN
2865: QP_PREQ_GRP.engine_debug('***End Process_Limits***');
2866:
2867: END IF;
2868: EXCEPTION
2869: WHEN FND_API.G_EXC_ERROR THEN
2871: x_return_status := FND_API.G_RET_STS_ERROR;
2872: --x_return_text is already set by Update_Balance
2873:
2874: IF l_debug = FND_API.G_TRUE THEN
2875: QP_PREQ_GRP.engine_debug('Expected Error in Process_Limits');
2876: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2877:
2878: END IF;
2879: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2872: --x_return_text is already set by Update_Balance
2873:
2874: IF l_debug = FND_API.G_TRUE THEN
2875: QP_PREQ_GRP.engine_debug('Expected Error in Process_Limits');
2876: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2877:
2878: END IF;
2879: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2880:
2881: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2882: x_return_text := substr(sqlerrm, 1, 2000);
2883:
2884: IF l_debug = FND_API.G_TRUE THEN
2885: QP_PREQ_GRP.engine_debug('Unexpected Error in Process_Limits');
2886: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2887:
2888: END IF;
2889: WHEN OTHERS THEN
2882: x_return_text := substr(sqlerrm, 1, 2000);
2883:
2884: IF l_debug = FND_API.G_TRUE THEN
2885: QP_PREQ_GRP.engine_debug('Unexpected Error in Process_Limits');
2886: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2887:
2888: END IF;
2889: WHEN OTHERS THEN
2890:
2891: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2892: x_return_text := substr(sqlerrm, 1, 2000);
2893:
2894: IF l_debug = FND_API.G_TRUE THEN
2895: QP_PREQ_GRP.engine_debug('Other Error in Process_Limits');
2896: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2897:
2898: END IF;
2899: END Process_Limits;
2892: x_return_text := substr(sqlerrm, 1, 2000);
2893:
2894: IF l_debug = FND_API.G_TRUE THEN
2895: QP_PREQ_GRP.engine_debug('Other Error in Process_Limits');
2896: QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
2897:
2898: END IF;
2899: END Process_Limits;
2900: