[Home] [Help]
PACKAGE BODY: APPS.OE_COMMITMENT_PVT
Source
1 PACKAGE BODY oe_commitment_pvt AS
2 /* $Header: OEXVCMTB.pls 120.1 2012/01/04 07:30:29 slagiset ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'oe_commitment_pvt';
7 g_fmt_mask VARCHAR2(500);
8
9 -- ar_balance :=
10 -- oe_balance := balance that has not been invoiced yet
11 -- ar_balance - oe_balance -> true balance
12 -- the new line total <= true balance
13 --
14 --
15 --
16 --
17 procedure evaluate_commitment(
18 p_commitment_id IN NUMBER
19 ,p_header_id IN NUMBER
20 ,x_return_status OUT NOCOPY VARCHAR2
21
22 ,x_msg_count OUT NOCOPY NUMBER
23
24 ,x_msg_data OUT NOCOPY VARCHAR2
25
26 ,p_unit_selling_price IN NUMBER
27 )
28 IS
29
30 cursor c_trx_number_cur IS
31 select TRX_NUMBER
32 FROM RA_CUSTOMER_TRX
33 WHERE CUSTOMER_TRX_ID = p_commitment_id;
34
35 l_api_name CONSTANT VARCHAR2(30) := 'Evaluate_Commitment';
36 l_commitment_bal NUMBER;
37 l_total_balance NUMBER;
38 l_converted_commitment_bal VARCHAR2(500);
39 l_converted_total_balance VARCHAR2(500);
40 l_trx_number VARCHAR2(20);
41 l_class VARCHAR2(30);
42 l_so_source_code VARCHAR2(30);
43 l_oe_installed_flag VARCHAR2(30);
44 l_currency_code varchar2(30) := 'USD';
45 l_precision NUMBER;
46 l_ext_precision NUMBER;
47 l_min_acct_unit NUMBER;
48 l_text VARCHAR2(500);
49
50 --
51 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
52 --
53 BEGIN
54 IF l_debug_level > 0 THEN
55 oe_debug_pub.add( 'ENTERING OE_COMMITMENT_PVT' ) ;
56 END IF;
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59
60 If do_commitment_sequencing Then
61 --new behavior this old procedure should not even get called
62 IF l_debug_level > 0 THEN
63 oe_debug_pub.add( ' NEW COMMITMENT RETURNING TO CALLER' ) ;
64 END IF;
65 Return;
66 End If;
67
68 If p_unit_selling_price Is Null Or p_unit_selling_price = 0 Then
69 --Return, no validation needed since the price is 0
70 Return;
71 End If;
72
73 IF l_debug_level > 0 THEN
74 oe_debug_pub.add( 'EVALUATE COMMITMENT FOR COMMITMENTID:' || TO_CHAR ( P_COMMITMENT_ID ) , 1 ) ;
75 END IF;
76 l_class := NULL;
77 l_so_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
78 l_oe_installed_flag := 'I';
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add( 'COMMITMENT AFTER CALLING FND_PROFILE.VALUE' ) ;
81 END IF;
82
83 l_commitment_bal := nvl(oe_globals.g_commitment_balance, 0);
84
85 l_total_balance := l_commitment_bal - p_unit_selling_price;
86 IF l_debug_level > 0 THEN
87 oe_debug_pub.add( 'TOTAL COMMITMENT BALANCE:'|| TO_CHAR ( L_TOTAL_BALANCE ) , 1 ) ;
88 END IF;
89 BEGIN
90 SELECT nvl(transactional_curr_code,'USD')
91 INTO l_currency_code from oe_order_headers
92 WHERE header_id=p_header_id;
93 EXCEPTION WHEN no_data_found THEN
94 l_currency_code := 'USD';
95 END ;
96
97 FND_CURRENCY.Get_Info(l_currency_code, -- IN variable
98 l_precision,
99 l_ext_precision,
100 l_min_acct_unit);
101
102 FND_CURRENCY.Build_Format_Mask(G_Fmt_mask, 20, l_precision,
103 l_min_acct_unit, TRUE
104 );
105
106 l_converted_commitment_bal := TO_CHAR(l_commitment_bal, g_fmt_mask);
107 l_converted_total_balance := TO_CHAR(l_total_balance, g_fmt_mask);
108
109 open c_trx_number_cur;
110 fetch c_trx_number_cur into l_trx_number;
111 close c_trx_number_cur;
112
113 if l_commitment_bal <= 0 then
114 --raise error if saved balance has exceeded allowed balance
115 FND_MESSAGE.SET_NAME('ONT','OE_COM_ZERO_BALANCE');
116 FND_MESSAGE.SET_TOKEN('COMMITMENT' , l_trx_number);
117 OE_MSG_PUB.ADD;
118 IF l_debug_level > 0 THEN
119 oe_debug_pub.add( ' COMMITMENT HAVE OVERDRAWN MORE THAN ONCE' ) ;
120 oe_debug_pub.add( ' EXPECTED ERROR IN EVALUATE_COMMITMENT' , 1 ) ;
121 END IF;
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 -- move code to calculate_commitment
124 elsif l_total_balance <= 0 then
125 --not to raise error if balance - current line amount exceed
126 --balance, allowing to use up last available balance
127 FND_MESSAGE.Set_Name('ONT','OE_COM_BALANCE_WARNING');
128 FND_MESSAGE.Set_Token('COMMITMENT',l_trx_number);
129 FND_Message.Set_Token('BALANCE',TO_CHAR(l_total_balance * -1, g_fmt_mask));
130 OE_MSG_PUB.ADD;
131 IF l_debug_level > 0 THEN
132 oe_debug_pub.add( ' ISSUE A WARNING ABOUT COMMITMENT HAS BEEN OVERDRAWN' ) ;
133 END IF;
134 -- show the message after saving the commitment
135 else
136 FND_MESSAGE.Set_Name('ONT','OE_COM_BALANCE');
137 FND_MESSAGE.Set_Token('COMMITMENT',l_trx_number);
138 -- FND_Message.Set_Token('BALANCE',l_total_balance);
139 FND_Message.Set_Token('BALANCE',l_converted_total_balance);
140 OE_MSG_PUB.ADD;
141
142 end if;
143
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'LEAVING COMMITMENT' ) ;
146 END IF;
147 EXCEPTION
148 WHEN OTHERS THEN
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
151 THEN
152 FND_MSG_PUB.Add_Exc_Msg
153 ( G_PKG_NAME
154 ,l_api_name
155 );
156 END IF;
157
158 END evaluate_commitment;
159
160 FUNCTION Get_Allocate_Tax_Freight
161 ( p_line_rec IN OE_ORDER_PUB.line_rec_type
162 ) RETURN VARCHAR2 IS
163
164 l_allocate_tax_freight VARCHAR2(1);
165 v_CursorID INTEGER;
166 v_SelectStmt VARCHAR2(500);
167 v_allocate_tax_freight VARCHAR2(1);
168 v_Dummy INTEGER;
169
170 --
171 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
172 --
173 BEGIN
174
175 IF l_debug_level > 0 THEN
176 oe_debug_pub.add( 'ENTERING GET_ALLOCATE_TAX_FREIGHT' , 1 ) ;
177 END IF;
178
179 -- implementing dynamic SQL to avoid compilation error if AR Patch D is not installed.
180 v_CursorID := DBMS_SQL.OPEN_CURSOR;
181
182 v_SelectStmt :=
183 'SELECT allocate_tax_freight
184 INTO :allocate_fax_freight
185 FROM ra_cust_trx_types_all rctt
186 ,ra_customer_trx_all rcta
187 WHERE rctt.cust_trx_type_id = rcta.cust_trx_type_id
188 AND rcta.customer_trx_id = :commitment_id';
189
190 DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
191 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':commitment_id', p_line_rec.commitment_id);
192
193 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_allocate_tax_freight, 20);
194
195 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
196
197 LOOP
198 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
199 EXIT;
200 END IF;
201
202 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_allocate_tax_freight);
203
204 END LOOP;
205
206 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
207
208 IF v_allocate_tax_freight IS NULL THEN
209 v_allocate_tax_freight := 'N';
210 END IF;
211
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( 'THE RETURNED VALUE FOR ALLOCATE_TAX_FREIGHT IS: '||V_ALLOCATE_TAX_FREIGHT , 3 ) ;
214 END IF;
215
216
217 /***
218 SELECT NVL(allocate_tax_freight, 'N')
219 INTO l_allocate_tax_freight
220 FROM ra_cust_trx_types_all rctt
221 ,ra_customer_trx_all rcta
222 WHERE rctt.cust_trx_type_id = rcta.cust_trx_type_id
223 AND rcta.customer_trx_id = p_line_rec.commitment_id;
224 ***/
225
226
227 RETURN(v_allocate_tax_freight);
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 l_allocate_tax_freight := 'N';
232 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
233 END Get_Allocate_Tax_Freight;
234
235 -- get the total amount of an order line according to allocate_tax_freight flag.
236 FUNCTION Get_Line_Total
237 ( p_line_rec IN OE_ORDER_PUB.line_rec_type
238 ) RETURN NUMBER IS
239
240 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
241 l_total NUMBER;
242 l_charge_amount NUMBER;
243 l_allocate_tax_freight VARCHAR2(1) := 'N';
244 l_msg_count NUMBER;
245 l_msg_data VARCHAR2(2000);
246
247 --
248 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
249 --
250 BEGIN
251
252 IF l_debug_level > 0 THEN
253 oe_debug_pub.add( 'ENTERING OE_COMMITMENT_PVT.GET_LINE_TOTAL.' , 1 ) ;
254 END IF;
255
256 l_allocate_tax_freight := Get_Allocate_Tax_Freight(p_line_rec => p_line_rec);
257
258 IF l_debug_level > 0 THEN
259 oe_debug_pub.add( 'IN OE_COMMITMENT_PVT , ALLOCATE_TAX_FREIGHT IS: '||L_ALLOCATE_TAX_FREIGHT , 3 ) ;
260 END IF;
261
262 -- get line level charges
263 IF nvl(p_line_rec.ordered_quantity, 0) > 0 THEN
264 OE_CHARGE_PVT.Get_Charge_Amount(
265 p_api_version_number => 1.1 ,
266 p_init_msg_list => FND_API.G_FALSE ,
267 p_header_id => p_line_rec.header_id ,
268 p_line_id => p_line_rec.line_id ,
269 p_all_charges => FND_API.G_FALSE ,
270 x_return_status => l_return_status ,
271 x_msg_count => l_msg_count ,
272 x_msg_data => l_msg_data ,
273 x_charge_amount => l_charge_amount );
274
275 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
278 RAISE FND_API.G_EXC_ERROR;
279 END IF;
280 END IF;
281
282 -- to include tax only if the tax calculation flag is 'Y'
283 IF l_allocate_tax_freight = 'Y' THEN
284 l_total := nvl(p_line_rec.ordered_quantity,0) * nvl(p_line_rec.unit_selling_price,0)
285 + nvl(p_line_rec.tax_value,0) + nvl(l_charge_amount, 0);
286 ELSE
287 l_total := nvl(p_line_rec.ordered_quantity,0) * nvl(p_line_rec.unit_selling_price,0);
288 END IF;
289
290
291 RETURN l_total;
292
293 END Get_Line_Total;
294
295
296 PROCEDURE calculate_commitment(
297 p_request_rec IN OE_Order_PUB.request_rec_type
298 ,x_return_status OUT NOCOPY VARCHAR2
299
300 )
301 IS
302 l_line_id NUMBER := p_request_rec.entity_id;
303 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
304 l_payment_types_rec OE_PAYMENTS_UTIL.Payment_Types_Rec_Type;
305 l_payment_types_tbl OE_PAYMENTS_UTIL.Payment_Types_Tbl_Type;
306 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
307 l_header_id NUMBER;
308 l_commitment_id NUMBER;
309 l_new_commitment_id NUMBER;
310 l_commitment_applied_amount NUMBER;
311 l_class VARCHAR2(30);
312 l_so_source_code VARCHAR2(30);
313 l_oe_installed_flag VARCHAR2(30);
314 l_currency_code varchar2(30) := 'USD';
315 l_precision NUMBER;
316 l_ext_precision NUMBER;
317 l_min_acct_unit NUMBER;
318 l_commitment_bal NUMBER;
319 l_new_commitment_bal NUMBER;
320 l_total NUMBER;
321 l_ordered_quantity NUMBER;
322 l_unit_selling_price NUMBER;
323 l_tax_value NUMBER;
324 l_allocate_tax_freight VARCHAR2(1) := 'N';
325 l_commitment VARCHAR2(20);
326 l_overdrawn_amount NUMBER;
327 l_msg_count NUMBER;
328 l_msg_data VARCHAR2(2000);
329 l_payment_type_code VARCHAR2(30);
330 l_payment_amount NUMBER;
331 l_outbound_total NUMBER;
332 l_verify_payment_flag VARCHAR2(1):= 'N';
333 l_show_balance BOOLEAN := FALSE;
334 l_split_by VARCHAR2(30);
335 -- QUOTING change
336 l_transaction_phase_code VARCHAR2(30);
337 --bug 3560198
338 l_result BOOLEAN;
339 --
340 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
341 --
342 BEGIN
343
344 IF l_debug_level > 0 THEN
345 oe_debug_pub.add( 'ENTERING OE_COMMITMENT_PVT.CALCULATE_COMMITMENT FOR LINE: '||l_LINE_ID , 1 ) ;
346 END IF;
347
348 BEGIN
349 SELECT l.header_id
350 ,l.ordered_quantity
351 ,l.commitment_id
352 ,nvl(l.unit_selling_price, 0)
353 ,nvl(l.tax_value,0)
354 -- QUOTING change
355 ,l.transaction_phase_code
356 ,l.split_by
357 INTO l_header_id
358 ,l_ordered_quantity
359 ,l_new_commitment_id
360 ,l_unit_selling_price
361 ,l_tax_value
362 -- QUOTING change
363 ,l_transaction_phase_code
364 ,l_split_by
365 FROM oe_order_lines_all l
366 WHERE l.line_id = l_line_id;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 RETURN;
371 END;
372
373 -- QUOTING change
374 -- No need to calculate commitment for orders in negotiation phase
375 if l_debug_level > 0 then
376 oe_debug_pub.add('trxn phase :'||l_transaction_phase_code);
377 end if;
378 IF l_transaction_phase_code = 'N' THEN
379 RETURN;
380 END IF;
381
382 -- bug 2405348, comment out the following code.
383 /***
384 IF l_new_commitment_id IS NULL THEN
385 RETURN;
386 END IF;
387 ***/
388
389 -- build currency format.
390 IF g_fmt_mask IS NULL THEN
391 BEGIN
392 SELECT nvl(transactional_curr_code,'USD')
393 INTO l_currency_code from oe_order_headers
394 WHERE header_id=l_header_id;
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 l_currency_code := 'USD';
399 END ;
400
401 FND_CURRENCY.Get_Info(l_currency_code, -- IN variable
402 l_precision,
403 l_ext_precision,
404 l_min_acct_unit);
405
406 FND_CURRENCY.Build_Format_Mask(G_Fmt_mask, 20, l_precision,
407 l_min_acct_unit, TRUE
408 );
409 END IF;
410
411 -- populating l_line_rec.
412 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
413 l_line_rec.commitment_id := l_new_commitment_id;
414 l_line_rec.header_id := l_header_id;
415 l_line_rec.line_id := l_line_id;
416 l_line_rec.ordered_quantity := l_ordered_quantity;
417 l_line_rec.unit_selling_price := l_unit_selling_price;
418 l_line_rec.tax_value := l_tax_value;
419
420 -- calling the procedure to get the line total amount
421 l_total := get_line_total(p_line_rec => l_line_rec);
422
423 IF l_debug_level > 0 THEN
424 oe_debug_pub.add( 'LINE TOTAL RETURNED IS: '||L_TOTAL , 1 ) ;
425 END IF;
426
427 -- get the commitment balance
428 l_class := NULL;
429 l_so_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
430 l_oe_installed_flag := 'I';
431
432 -- Fix Bug # 2511389: Get the commitment balance from tables.
433 IF l_new_commitment_id IS NOT NULL THEN
434
435 l_commitment_bal := ARP_BAL_UTIL.GET_COMMITMENT_BALANCE(
436 l_new_commitment_id
437 ,l_class
438 ,l_so_source_code
439 ,l_oe_installed_flag );
440 END IF;
441
442 -- l_commitment_bal := nvl(oe_globals.g_commitment_balance, 0);
443
444 IF l_debug_level > 0 THEN
445 oe_debug_pub.add( 'COMMITMENT BALANCE IS: '||L_COMMITMENT_BAL ) ;
446 END IF;
447
448 BEGIN
449
450 IF l_debug_level > 0 THEN
451 oe_debug_pub.add( 'BEFORE CALLING OE_PAYMENTS_UTIL.QUERY_ROWS' ) ;
452 END IF;
453
454 oe_payments_Util.Query_Rows
455 ( p_payment_trx_id => l_new_commitment_id /* Bug#3536642 */
456 ,p_header_id => l_header_id
457 ,p_line_id => l_line_id
458 ,x_payment_types_tbl => l_payment_types_tbl
459 ,x_return_status => l_return_status
460 );
461
462 IF l_debug_level > 0 THEN
463 oe_debug_pub.add( 'AFTER CALLING OE_PAYMENTS_UTIL.QUERY_ROWS' ) ;
464 END IF;
465
466 EXCEPTION
467 WHEN NO_DATA_FOUND THEN
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469 END;
470
471
472 -- commitment_applied should be the lesser of the l_total and l_commitment_bal
473 -- Fix Bug # 2511389: Changed logic inside following IF condition
474 IF l_new_commitment_id IS NOT NULL THEN
475
476 IF l_payment_types_tbl.COUNT > 0 THEN
477 oe_debug_pub.add( 'commitment type '||l_payment_types_tbl(1).payment_type_code);
478 oe_debug_pub.add( '1 : '||l_payment_types_tbl(1).payment_trx_id);
479 oe_debug_pub.add( 'New commitment Id '||l_new_commitment_id);
480 IF l_new_commitment_id = l_payment_types_tbl(1).payment_trx_id THEN
481 /* Added nvl for the Bug #3536642 */
482 oe_debug_pub.add( 'commitment applied amount '||l_payment_types_tbl(1).commitment_applied_amount);
483 l_commitment_bal := l_commitment_bal + nvl(l_payment_types_tbl(1).commitment_applied_amount,0);
484
485 --added for multiple payments
486 if l_payment_types_tbl(1).payment_amount is not null
487 and oe_prepayment_util.is_multiple_payments_enabled = TRUE
488 then
489 l_total := l_payment_types_tbl(1).payment_amount;
490 end if;
491
492 IF l_debug_level > 0 THEN
493 oe_debug_pub.add( 'COMMITMENT BALANCE WITH CURRENTLY APPLIED COMMITMENT: '||L_COMMITMENT_BAL ) ;
494 END IF;
495 END IF;
496
497 END IF;
498
499 IF l_commitment_bal >= l_total THEN
500 l_commitment_applied_amount := l_total;
501 ELSE
502 l_commitment_applied_amount := l_commitment_bal;
503 l_overdrawn_amount := l_total - l_commitment_applied_amount;
504 END IF;
505
506 IF l_split_by is not null THEN
507 IF l_commitment_applied_amount > oe_globals.g_original_commitment_applied THEN
508 l_commitment_applied_amount := oe_globals.g_original_commitment_applied;
509 l_overdrawn_amount := 0;
510 END IF;
511 END IF;
512
513
514 IF l_debug_level > 0 THEN
515 oe_debug_pub.add( 'COMMITMENT APPLIED AMOUNT IS: '||L_COMMITMENT_APPLIED_AMOUNT ) ;
516 END IF;
517 END IF;
518
519 IF l_payment_types_tbl.COUNT > 0 THEN
520 IF l_line_id IS NOT NULL THEN
521 IF l_new_commitment_id IS NULL THEN
522 oe_payments_Util.Delete_Row(p_line_id => l_line_id, p_header_id => l_header_id);
523 ELSE
524 -- Fix Bug # 2511389: Delete payment record if applied amount has been set to <= 0.
525 IF nvl(l_commitment_applied_amount, 0) <= 0 THEN
526 oe_payments_Util.Delete_Row(p_line_id => l_line_id, p_header_id => l_header_id);
527 ELSE
528 IF NOT OE_GLOBALS.Equal(l_new_commitment_id, l_payment_types_tbl(1).payment_trx_id) THEN
529
530 IF l_debug_level > 0 THEN
531 oe_debug_pub.add( 'UPDATING BOTH APPLIED AMOUNT AND COMMITMENT' ) ;
532 END IF;
533
534 IF OE_PREPAYMENT_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED = TRUE then
535 UPDATE oe_payments
536 SET payment_trx_id = l_new_commitment_id,
537 commitment_applied_amount = l_commitment_applied_amount,
538 payment_number = nvl(payment_number, 1)
539 WHERE line_id = l_line_id
540 AND header_id = l_header_id
541 AND nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
542
543 ELSE
544 UPDATE oe_payments
545 SET payment_trx_id = l_new_commitment_id,
546 commitment_applied_amount = l_commitment_applied_amount
547 WHERE line_id = l_line_id
548 AND header_id = l_header_id
549 AND nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
550 END IF;
551
552 l_show_balance := TRUE;
553
554 ELSE
555
556 /*** Fix Bug # 2511389: Call to delete_row added above should take care of this scenario also
557 IF l_commitment_bal >0 OR (l_commitment_bal <= 0 AND l_commitment_applied_amount < l_payment_types_tbl(1).commitment_applied_amount) THEN
558 IF l_ordered_quantity = 0 THEN
559 Oe_Payments_Util.Delete_Row(p_line_id => p_line_id);
560 ELSE
561 ***/
562 IF NVL(l_commitment_applied_amount, 0) <> NVL(l_payment_types_tbl(1).commitment_applied_amount, 0) THEN
563
564 IF l_debug_level > 0 THEN
565 oe_debug_pub.add( 'UPDATING ONLY THE APPLIED AMOUNT' ) ;
566 END IF;
567
568 IF OE_PREPAYMENT_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED = TRUE then
569 UPDATE oe_payments
570 SET commitment_applied_amount = l_commitment_applied_amount,
571 payment_number = nvl(payment_number, 1)
572 WHERE line_id = l_line_id
573 AND header_id = l_header_id
574 AND payment_trx_id = l_new_commitment_id /* Added this condition for Bug #3536642 */
575 AND nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
576
577 ELSE
578 UPDATE oe_payments
579 SET commitment_applied_amount = l_commitment_applied_amount
580 WHERE line_id = l_line_id
581 AND header_id = l_header_id
582 AND payment_trx_id = l_new_commitment_id /* Added this condition for Bug #3536642 */
583 AND nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
584 END IF;
585
586 l_show_balance := TRUE;
587
588 END IF;
589 -- END IF;
590 END IF;
591 END IF;
592 END IF;
593 END IF;
594 ELSE
595
596 -- Fix Bug # 2511389: Added condition so that record is created only if commitment is really applied
597 IF nvl(l_commitment_applied_amount, 0) > 0 THEN
598 l_payment_types_rec.payment_trx_id := l_new_commitment_id;
599 l_payment_types_rec.payment_type_code := 'COMMITMENT';
600 l_payment_types_rec.header_id := l_header_id;
601 l_payment_types_rec.line_id := l_line_id;
602 l_payment_types_rec.payment_level_code := 'LINE';
603 l_payment_types_rec.creation_date := SYSDATE;
604 l_payment_types_rec.created_by := FND_GLOBAL.USER_ID;
605 l_payment_types_rec.last_update_date := SYSDATE;
606 l_payment_types_rec.last_updated_by := FND_GLOBAL.USER_ID;
607 l_payment_types_rec.commitment_applied_amount := l_commitment_applied_amount;
608
609 IF OE_PREPAYMENT_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED = TRUE then
610
611 Begin
612
613 select (nvl(max(payment_number),0) + 1)
614 into l_payment_types_rec.payment_number
615 from oe_payments
616 where header_id = l_header_id
617 and line_id = l_line_id;
618
619 Exception
620 when no_data_found then
621 l_payment_types_rec.payment_number := 1;
622 end;
623
624 END IF;
625
626 IF l_debug_level > 0 THEN
627 oe_debug_pub.add( 'BEFORE CALLING OE_PAYMENTS_UTIL.INSERT_ROW' ) ;
628 END IF;
629
630 OE_Payments_Util.INSERT_ROW(p_payment_types_rec => l_payment_types_rec);
631
632 IF l_debug_level > 0 THEN
633 oe_debug_pub.add( 'AFTER CALLING OE_PAYMENTS_UTIL.INSERT_ROW' ) ;
634 END IF;
635
636 l_show_balance := TRUE;
637 END IF;
638 END IF;
639
640 --bug 3560198
641 IF l_debug_level > 0 THEN
642 oe_debug_pub.add('Check if a request for update commitment applied has been logged or not');
643 oe_debug_pub.add('p_request_rec.entity_code : '||p_request_rec.entity_code);
644 oe_debug_pub.add('p_request_rec.entity_id : '||p_request_rec.entity_id);
645 oe_debug_pub.add('p_request_rec.request_type : '||p_request_rec.request_type);
646 END IF;
647 l_result := Oe_Delayed_Requests_Pvt.Check_For_Request(p_request_rec.entity_code,
648 p_request_rec.entity_id,
649 OE_GLOBALS.G_UPDATE_COMMITMENT_APPLIED
650 );
651 IF l_result THEN
652 oe_debug_pub.add('setting l_show_balance to false');
653 l_show_balance := FALSE;
654 END IF;
655 --bug 3560198
656 IF l_new_commitment_id IS NOT NULL AND l_show_balance THEN
657
658 /* Fix Bug # 2511389: Replaced call the api with statement below
659 l_new_commitment_bal := ARP_BAL_UTIL.GET_COMMITMENT_BALANCE(
660 l_new_commitment_id
661 ,l_class
662 ,l_so_source_code
663 ,l_oe_installed_flag );
664 */
665
666 l_new_commitment_bal := l_commitment_bal - l_commitment_applied_amount;
667 IF l_debug_level > 0 THEN
668 oe_debug_pub.add( 'NEW COMMITMENT BALANCE IS: '||L_NEW_COMMITMENT_BAL ) ;
669 END IF;
670
671 FND_MESSAGE.Set_Name('ONT','OE_COM_BALANCE');
672 FND_MESSAGE.Set_Token('COMMITMENT',l_commitment);
673 FND_Message.Set_Token('BALANCE',to_char(l_new_commitment_bal, g_fmt_mask));
674 OE_MSG_PUB.ADD;
675
676 -- to display message if the current line overdraws
677 IF l_total > l_commitment_bal and nvl(l_overdrawn_amount, 0) <> 0 THEN
678 FND_MESSAGE.Set_Name('ONT','OE_COM_BALANCE_WARNING');
679 FND_MESSAGE.Set_Token('COMMITMENT',l_commitment);
680 FND_Message.Set_Token('BALANCE',TO_CHAR(l_overdrawn_amount, g_fmt_mask));
681 OE_MSG_PUB.ADD;
682 END IF;
683 l_show_balance := FALSE;
684 END IF;
685
686 -- Get Header Inforamtion ..
687 SELECT
688 payment_type_code
689 , NVL(payment_amount, 0)
690 INTO
691 l_payment_type_code
692 , l_payment_amount
693 FROM oe_order_headers_all
694 WHERE header_id = l_header_id;
695
696 -- If Credit Card Order then
697 --
698 IF l_payment_type_code = 'CREDIT_CARD' THEN
699
700 -- Get the Outbound Lines Total
701 --
702 l_outbound_total := OE_OE_TOTALS_SUMMARY.OUTBOUND_ORDER_TOTAL(l_header_id);
703
704 -- Log a Delayed Request only if previously authorized
705 -- amount is less than the current outbound total.
706 --
707 IF l_payment_amount < l_outbound_total THEN
708 l_verify_payment_flag := 'Y';
709 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := FALSE; --ER 12363706, Tolerance is not considered when commitment is changed
710 END IF;
711
712 END IF; -- Payment Type Code
713
714 -- Log Delayed Request for Verify Payment
715 --
716 IF l_verify_payment_flag = 'Y' THEN
717
718 IF l_debug_level > 0 THEN
719 oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR VERIFY PAYMENT IN COMMITMENTS' , 2 ) ;
720 END IF;
721 OE_delayed_requests_Pvt.log_request
722 (p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
723 p_entity_id => l_header_id,
724 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
725 p_requesting_entity_id => l_line_id,
726 p_request_type => OE_GLOBALS.G_VERIFY_PAYMENT,
727 x_return_status => l_return_status);
728
729 END IF;
730
731 IF l_debug_level > 0 THEN
732 oe_debug_pub.add( 'EXITING OE_COMMITMENT_PVT.CALCULATE_COMMITMENT.' , 1 ) ;
733 END IF;
734
735 END calculate_commitment;
736
737 FUNCTION get_commitment_applied_amount
738 ( p_header_id IN NUMBER
739 , p_line_id IN NUMBER
740 , p_commitment_id IN NUMBER
741 ) RETURN NUMBER IS
742 l_commitment_applied_amount NUMBER := 0.0;
743
744 --
745 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
746 --
747 BEGIN
748
749
750 BEGIN
751 SELECT NVL(commitment_applied_amount, 0)
752 INTO l_commitment_applied_amount
753 FROM oe_payments
754 WHERE line_id = p_line_id
755 AND header_id = p_header_id
756 AND payment_trx_id = p_commitment_id;
757
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 l_commitment_applied_amount := 0.0;
761 END;
762
763 return l_commitment_applied_amount;
764
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
769 THEN
770 FND_MSG_PUB.Add_Exc_Msg
771 ( G_PKG_NAME
772 ,'OE_COMMITMENT_PVT.GET_COMMITMENT_APPLIED_AMOUNT'
773 );
774 END IF;
775
776 END get_commitment_applied_amount;
777
778 PROCEDURE update_commitment(
779 p_line_id IN NUMBER
780 ,x_return_status OUT NOCOPY VARCHAR2
781
782 )
783 IS
784 l_split_by VARCHAR2(30);
785 l_split_from_line_id NUMBER;
786 l_commitment_applied_amount NUMBER;
787 l_commitment_id NUMBER;
788 l_new_commitment_id NUMBER;
789 l_header_id NUMBER := -1;
790 l_payment_types_rec OE_PAYMENTS_UTIL.Payment_Types_Rec_Type;
791 l_payment_types_tbl OE_PAYMENTS_UTIL.Payment_Types_Tbl_Type;
792 l_children_line_id NUMBER;
793 l_children_commitment NUMBER;
794 l_children_line_rec OE_ORDER_PUB.Line_Rec_Type;
795 l_children_commitment_id NUMBER;
796 l_children_header_id NUMBER;
797 l_children_ordered_quantity NUMBER;
798 l_children_unit_selling_price NUMBER;
799 l_children_tax_value NUMBER;
800
801 -- QUOTING change
802 l_transaction_phase_code VARCHAR2(30);
803
804 CURSOR l_split_lines_cur IS
805 SELECT line_id,commitment_id,header_id,
806 ordered_quantity,unit_selling_price,tax_value
807 FROM oe_order_lines_all
808 WHERE header_id = l_header_id
809 AND split_from_line_id = p_line_id;
810
811 --
812 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
813 --
814 BEGIN
815
816 IF l_debug_level > 0 THEN
817 oe_debug_pub.add( 'ENTERING UPDATE_COMMITMENT FOR LINE_ID '||P_LINE_ID , 1 ) ;
818 END IF;
819
820 BEGIN
821 SELECT l.header_id
822 ,l.commitment_id
823 ,l.split_by
824 ,l.split_from_line_id
825 -- QUOTING change
826 ,l.transaction_phase_code
827 INTO l_header_id
828 ,l_new_commitment_id
829 ,l_split_by
830 ,l_split_from_line_id
831 -- QUOTING change
832 ,l_transaction_phase_code
833 FROM oe_order_lines l
834 WHERE l.line_id = p_line_id;
835
836 EXCEPTION
837 WHEN NO_DATA_FOUND THEN
838 null;
839 end;
840
841 -- QUOTING change
842 -- No need to update commitment for orders in negotiation phase
843 if l_debug_level > 0 then
844 oe_debug_pub.add('trxn phase :'||l_transaction_phase_code);
845 end if;
846 IF l_transaction_phase_code = 'N' THEN
847 RETURN;
848 END IF;
849
850 BEGIN
851 SELECT commitment_applied_amount
852 INTO l_commitment_applied_amount
853 FROM oe_payments oop
854 WHERE nvl(payment_type_code,'COMMITMENT') = 'COMMITMENT'
855 AND line_id=p_line_id
856 AND header_id = l_header_id;
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 null;
860 END;
861
862 IF l_split_by IS NOT NULL THEN
863
864 oe_globals.g_commitment_balance
865 := oe_globals.g_original_commitment_applied - l_commitment_applied_amount;
866 END IF;
867
868 IF l_debug_level > 0 THEN
869 oe_debug_pub.add('commitment balance is: '||oe_globals.g_commitment_balance,3);
870 oe_debug_pub.add('commitment applied amount is: '||l_commitment_applied_amount,3);
871 oe_debug_pub.add('original commitment applied is: '||oe_globals.g_original_commitment_applied,3);
872 END IF;
873
874 OPEN l_split_lines_cur;
875 LOOP
876 -- loop through split children lines to re-adjust the commitment
877 -- applied amount.
878 FETCH l_split_lines_cur INTO l_children_line_id,
879 l_children_commitment_id,
880 l_children_header_id,
881 l_children_ordered_quantity,
882 l_children_unit_selling_price,
883 l_children_tax_value;
884
885 EXIT WHEN l_split_lines_cur%NOTFOUND;
886
887 -- populating l_line_rec.
888 l_children_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
889 l_children_line_rec.commitment_id := l_children_commitment_id;
890 l_children_line_rec.header_id := l_children_header_id;
891 l_children_line_rec.line_id := l_children_line_id;
892 l_children_line_rec.ordered_quantity := l_children_ordered_quantity;
893 l_children_line_rec.unit_selling_price := l_children_unit_selling_price;
894 l_children_line_rec.tax_value := l_children_tax_value;
895
896 l_children_commitment
897 := get_line_total(p_line_rec => l_children_line_rec);
898
899 oe_debug_pub.add('Linda0721 -- child commit applied is: '||l_children_commitment,1);
900
901 IF nvl(oe_globals.g_commitment_balance,0) <= 0 THEN
902 update oe_payments
903 set commitment_applied_amount = 0
904 WHERE line_id = l_children_line_id
905 AND header_id = l_header_id
906 AND nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
907
908 ELSIF nvl(oe_globals.g_commitment_balance, 0) >= l_children_commitment THEN
909 update oe_payments
910 set commitment_applied_amount = l_children_commitment
911 where line_id = l_children_line_id
912 and header_id = l_header_id
913 and nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
914
915 oe_globals.g_commitment_balance
916 := nvl(oe_globals.g_commitment_balance, 0) - l_children_commitment;
917 ELSIF nvl(oe_globals.g_commitment_balance, 0) < l_children_commitment THEN
918 update oe_payments
919 set commitment_applied_amount = nvl(oe_globals.g_commitment_balance, 0)
920 where line_id = l_children_line_id
921 and header_id =l_header_id
922 and nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
923
924 oe_globals.g_commitment_balance := 0;
925 END IF;
926
927 END LOOP;
928 CLOSE l_split_lines_cur;
929
930 IF l_debug_level > 0 THEN
931 oe_debug_pub.add( 'Exiting UPDATE_COMMITMENT. ', 1 ) ;
932 END IF;
933
934 END update_commitment;
935
936 FUNCTION Do_Commitment_Sequencing RETURN BOOLEAN IS
937
938 l_column1_exists VARCHAR2(1) := 'N';
939 l_column2_exists VARCHAR2(1) := 'N';
940 INVALID_COLUMN_NAME EXCEPTION;
941 PRAGMA EXCEPTION_INIT(INVALID_COLUMN_NAME, -904);
942
943 --
944 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
945 --
946 BEGIN
947
948 IF G_Do_Commitment_Sequencing <> FND_API.G_MISS_NUM THEN
949 IF G_Do_Commitment_Sequencing = 0 THEN
950 RETURN FALSE;
951 ELSE
952 RETURN TRUE;
953 END IF;
954 ELSE
955 IF Nvl(Fnd_Profile.Value('OE_COMMITMENT_SEQUENCING'),'N') = 'N' THEN
956 G_Do_Commitment_Sequencing := 0;
957 RETURN FALSE;
958 ELSE
959 BEGIN
960
961 EXECUTE IMMEDIATE
962 'SELECT ALLOCATE_TAX_FREIGHT
963 FROM RA_CUST_TRX_TYPES_ALL
964 WHERE ROWNUM = 1';
965
966 l_column1_exists := 'Y';
967
968 EXCEPTION WHEN INVALID_COLUMN_NAME THEN
969 l_column1_exists := 'N';
970 END;
971
972 IF l_column1_exists = 'N' THEN
973 G_Do_Commitment_Sequencing := 0;
974 RETURN FALSE;
975 ELSE
976 BEGIN
977
978 EXECUTE IMMEDIATE
979 'SELECT PROMISED_COMMITMENT_AMOUNT
980 FROM RA_INTERFACE_LINES_ALL
981 WHERE ROWNUM = 1';
982
983 l_column2_exists := 'Y';
984
985
986 EXCEPTION WHEN INVALID_COLUMN_NAME THEN
987 l_column2_exists := 'N';
988 END;
989
990 IF l_column2_exists = 'N' THEN
991 G_Do_Commitment_Sequencing := 0;
992 RETURN FALSE;
993 ELSE
994 G_Do_Commitment_Sequencing := 1;
995 RETURN TRUE;
996 END IF; -- end of column2 exists check
997
998 END IF; -- end of column1 exists chck
999 END IF; -- end of profile option checking.
1000
1001 END IF; -- end of G_Do_Commitment_Sequencing is FND_API.G_MISS_NUM.
1002
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1006 THEN
1007 FND_MSG_PUB.Add_Exc_Msg
1008 ( G_PKG_NAME
1009 ,'OE_COMMITMENT_PVT.DO_COMMITMENT_SEQUENCING'
1010 );
1011 END IF;
1012
1013 END Do_Commitment_Sequencing;
1014
1015 procedure update_commitment_applied(
1016 p_line_id IN NUMBER
1017 , p_amount IN NUMBER
1018 , p_header_id IN NUMBER
1019 , p_commitment_id IN NUMBER
1020 , x_return_status OUT NOCOPY VARCHAR2
1021
1022 ) IS
1023
1024 l_commitment_applied_amount NUMBER := 0;
1025 l_commitment_bal NUMBER;
1026 l_class VARCHAR2(30);
1027 l_so_source_code VARCHAR2(30);
1028 l_oe_installed_flag VARCHAR2(30);
1029 l_amount_to_apply NUMBER;
1030 --bug 3560198
1031 l_new_commitment_bal NUMBER;
1032 l_commitment VARCHAR2(20);
1033 l_currency_code varchar2(30) := 'USD';
1034 l_precision NUMBER;
1035 l_ext_precision NUMBER;
1036 l_min_acct_unit NUMBER;
1037 --bug 3560198
1038
1039 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1040
1041 BEGIN
1042
1043 IF l_debug_level > 0 THEN
1044 oe_debug_pub.add('OEXVCMTB: Entering UPDATE_COMMITMENT_APPLIED FOR LINE_ID '||P_LINE_ID, 1 );
1045 oe_debug_pub.add('OEXVCMTB: p_amount is: '||p_amount, 3 );
1046 oe_debug_pub.add('OEXVCMTB: p_header_id is: '||p_header_id, 3 );
1047 oe_debug_pub.add('OEXVCMTB: p_commitment_id is: '||p_commitment_id, 3 );
1048 END IF;
1049
1050 -- get the commitment balance
1051 l_class := NULL;
1052 l_so_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
1053 l_oe_installed_flag := 'I';
1054
1055 l_commitment_bal := ARP_BAL_UTIL.GET_COMMITMENT_BALANCE(
1056 p_commitment_id
1057 ,l_class
1058 ,l_so_source_code
1059 ,l_oe_installed_flag );
1060
1061 IF l_debug_level > 0 THEN
1062 oe_debug_pub.add('OEXVCMTB: commitment balance is '||l_commitment_bal, 1 );
1063 END IF;
1064
1065 BEGIN
1066 SELECT nvl(commitment_applied_amount,0)
1067 INTO l_commitment_applied_amount
1068 FROM oe_payments
1069 WHERE header_id = p_header_id
1070 AND line_id = p_line_id
1071 AND payment_trx_id = p_commitment_id;
1072 EXCEPTION
1073 WHEN NO_DATA_FOUND THEN
1074 l_commitment_applied_amount := 0;
1075 END;
1076
1077 l_commitment_bal := l_commitment_bal + l_commitment_applied_amount;
1078
1079 IF l_debug_level > 0 THEN
1080 oe_debug_pub.add('OEXVCMTB: available commitment balance is '||l_commitment_bal, 1 );
1081 END IF;
1082
1083 IF p_amount <= l_commitment_bal THEN
1084 l_amount_to_apply := p_amount;
1085 ELSE
1086 l_amount_to_apply := l_commitment_bal;
1087
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add('OEXVCMTB: no sufficient balance '||l_amount_to_apply, 1 );
1090 END IF;
1091
1092 END IF;
1093
1094 IF l_debug_level > 0 THEN
1095 oe_debug_pub.add('OEXVCMTB: amount to apply is '||l_amount_to_apply, 1 );
1096 END IF;
1097
1098 --bug 3560198
1099 IF l_debug_level > 0 THEN
1100 oe_debug_pub.add('To display the commitment balance');
1101 END IF;
1102 l_new_commitment_bal := l_commitment_bal - l_amount_to_apply;
1103
1104 IF l_debug_level > 0 THEN
1105 oe_debug_pub.add('New commitment balance is : '||l_new_commitment_bal);
1106 END IF;
1107
1108 -- build currency format.
1109 IF g_fmt_mask IS NULL THEN
1110 BEGIN
1111 SELECT nvl(transactional_curr_code,'USD')
1112 INTO l_currency_code from oe_order_headers
1113 WHERE header_id=p_header_id;
1114
1115 EXCEPTION
1116 WHEN NO_DATA_FOUND THEN
1117 l_currency_code := 'USD';
1118 END ;
1119
1120 FND_CURRENCY.Get_Info(l_currency_code, -- IN variable
1121 l_precision,
1122 l_ext_precision,
1123 l_min_acct_unit);
1124
1125 FND_CURRENCY.Build_Format_Mask(G_Fmt_mask, 20, l_precision,
1126 l_min_acct_unit, TRUE
1127 );
1128 END IF;
1129
1130 FND_MESSAGE.Set_Name('ONT','OE_COM_BALANCE');
1131 FND_MESSAGE.Set_Token('COMMITMENT',l_commitment);
1132 FND_Message.Set_Token('BALANCE',to_char(l_new_commitment_bal, g_fmt_mask));
1133 OE_MSG_PUB.ADD;
1134 --bug 3560198
1135
1136
1137 UPDATE oe_payments
1138 SET commitment_applied_amount = l_amount_to_apply
1139 WHERE header_id = p_header_id
1140 AND line_id = p_line_id
1141 AND payment_trx_id = p_commitment_id;
1142
1143 IF l_debug_level > 0 THEN
1144 oe_debug_pub.add('OEXVCMTB: Exiting UPDATE_COMMITMENT_APPLIED. ', 1 );
1145 END IF;
1146
1147 END update_commitment_applied;
1148
1149 END oe_commitment_pvt;
1150