[Home] [Help]
PACKAGE BODY: APPS.OE_COMMITMENT_PVT
Source
1 PACKAGE BODY oe_commitment_pvt AS
2 /* $Header: OEXVCMTB.pls 115.34 2004/07/22 00:03:27 lkxu 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
448 BEGIN
445 oe_debug_pub.add( 'COMMITMENT BALANCE IS: '||L_COMMITMENT_BAL ) ;
446 END IF;
447
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' ) ;
569 UPDATE oe_payments
566 END IF;
567
568 IF OE_PREPAYMENT_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED = TRUE then
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
695
692 , l_payment_amount
693 FROM oe_order_headers_all
694 WHERE header_id = l_header_id;
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 END IF;
710
711 END IF; -- Payment Type Code
712
713 -- Log Delayed Request for Verify Payment
714 --
715 IF l_verify_payment_flag = 'Y' THEN
716
717 IF l_debug_level > 0 THEN
718 oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR VERIFY PAYMENT IN COMMITMENTS' , 2 ) ;
719 END IF;
720 OE_delayed_requests_Pvt.log_request
721 (p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
722 p_entity_id => l_header_id,
723 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
724 p_requesting_entity_id => l_line_id,
725 p_request_type => OE_GLOBALS.G_VERIFY_PAYMENT,
726 x_return_status => l_return_status);
727
728 END IF;
729
730 IF l_debug_level > 0 THEN
731 oe_debug_pub.add( 'EXITING OE_COMMITMENT_PVT.CALCULATE_COMMITMENT.' , 1 ) ;
732 END IF;
733
734 END calculate_commitment;
735
736 FUNCTION get_commitment_applied_amount
737 ( p_header_id IN NUMBER
738 , p_line_id IN NUMBER
739 , p_commitment_id IN NUMBER
740 ) RETURN NUMBER IS
741 l_commitment_applied_amount NUMBER := 0.0;
742
743 --
744 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
745 --
746 BEGIN
747
748
749 BEGIN
750 SELECT NVL(commitment_applied_amount, 0)
751 INTO l_commitment_applied_amount
752 FROM oe_payments
753 WHERE line_id = p_line_id
754 AND header_id = p_header_id
755 AND payment_trx_id = p_commitment_id;
756
757 EXCEPTION
758 WHEN NO_DATA_FOUND THEN
759 l_commitment_applied_amount := 0.0;
760 END;
761
762 return l_commitment_applied_amount;
763
764
765 EXCEPTION
766 WHEN OTHERS THEN
767 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
768 THEN
769 FND_MSG_PUB.Add_Exc_Msg
770 ( G_PKG_NAME
771 ,'OE_COMMITMENT_PVT.GET_COMMITMENT_APPLIED_AMOUNT'
772 );
773 END IF;
774
775 END get_commitment_applied_amount;
776
777 PROCEDURE update_commitment(
778 p_line_id IN NUMBER
779 ,x_return_status OUT NOCOPY VARCHAR2
780
781 )
782 IS
783 l_split_by VARCHAR2(30);
784 l_split_from_line_id NUMBER;
785 l_commitment_applied_amount NUMBER;
786 l_commitment_id NUMBER;
787 l_new_commitment_id NUMBER;
788 l_header_id NUMBER := -1;
789 l_payment_types_rec OE_PAYMENTS_UTIL.Payment_Types_Rec_Type;
790 l_payment_types_tbl OE_PAYMENTS_UTIL.Payment_Types_Tbl_Type;
791 l_children_line_id NUMBER;
792 l_children_commitment NUMBER;
793 l_children_line_rec OE_ORDER_PUB.Line_Rec_Type;
794 l_children_commitment_id NUMBER;
795 l_children_header_id NUMBER;
796 l_children_ordered_quantity NUMBER;
797 l_children_unit_selling_price NUMBER;
798 l_children_tax_value NUMBER;
799
800 -- QUOTING change
801 l_transaction_phase_code VARCHAR2(30);
802
803 CURSOR l_split_lines_cur IS
804 SELECT line_id,commitment_id,header_id,
805 ordered_quantity,unit_selling_price,tax_value
806 FROM oe_order_lines_all
807 WHERE header_id = l_header_id
808 AND split_from_line_id = p_line_id;
809
810 --
811 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
812 --
813 BEGIN
814
815 IF l_debug_level > 0 THEN
816 oe_debug_pub.add( 'ENTERING UPDATE_COMMITMENT FOR LINE_ID '||P_LINE_ID , 1 ) ;
817 END IF;
818
819 BEGIN
820 SELECT l.header_id
821 ,l.commitment_id
822 ,l.split_by
823 ,l.split_from_line_id
824 -- QUOTING change
825 ,l.transaction_phase_code
826 INTO l_header_id
827 ,l_new_commitment_id
828 ,l_split_by
829 ,l_split_from_line_id
830 -- QUOTING change
831 ,l_transaction_phase_code
832 FROM oe_order_lines l
833 WHERE l.line_id = p_line_id;
834
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 null;
838 end;
839
840 -- QUOTING change
841 -- No need to update commitment for orders in negotiation phase
842 if l_debug_level > 0 then
843 oe_debug_pub.add('trxn phase :'||l_transaction_phase_code);
844 end if;
845 IF l_transaction_phase_code = 'N' THEN
846 RETURN;
847 END IF;
848
849 BEGIN
850 SELECT commitment_applied_amount
851 INTO l_commitment_applied_amount
852 FROM oe_payments oop
856 EXCEPTION
853 WHERE nvl(payment_type_code,'COMMITMENT') = 'COMMITMENT'
854 AND line_id=p_line_id
855 AND header_id = l_header_id;
857 WHEN NO_DATA_FOUND THEN
858 null;
859 END;
860
861 IF l_split_by IS NOT NULL THEN
862
863 oe_globals.g_commitment_balance
864 := oe_globals.g_original_commitment_applied - l_commitment_applied_amount;
865 END IF;
866
867 IF l_debug_level > 0 THEN
868 oe_debug_pub.add('commitment balance is: '||oe_globals.g_commitment_balance,3);
869 oe_debug_pub.add('commitment applied amount is: '||l_commitment_applied_amount,3);
870 oe_debug_pub.add('original commitment applied is: '||oe_globals.g_original_commitment_applied,3);
871 END IF;
872
873 OPEN l_split_lines_cur;
874 LOOP
875 -- loop through split children lines to re-adjust the commitment
876 -- applied amount.
877 FETCH l_split_lines_cur INTO l_children_line_id,
878 l_children_commitment_id,
879 l_children_header_id,
880 l_children_ordered_quantity,
881 l_children_unit_selling_price,
882 l_children_tax_value;
883
884 EXIT WHEN l_split_lines_cur%NOTFOUND;
885
886 -- populating l_line_rec.
887 l_children_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
888 l_children_line_rec.commitment_id := l_children_commitment_id;
889 l_children_line_rec.header_id := l_children_header_id;
890 l_children_line_rec.line_id := l_children_line_id;
891 l_children_line_rec.ordered_quantity := l_children_ordered_quantity;
892 l_children_line_rec.unit_selling_price := l_children_unit_selling_price;
893 l_children_line_rec.tax_value := l_children_tax_value;
894
895 l_children_commitment
896 := get_line_total(p_line_rec => l_children_line_rec);
897
898 oe_debug_pub.add('Linda0721 -- child commit applied is: '||l_children_commitment,1);
899
900 IF nvl(oe_globals.g_commitment_balance,0) <= 0 THEN
901 update oe_payments
902 set commitment_applied_amount = 0
903 WHERE line_id = l_children_line_id
904 AND header_id = l_header_id
905 AND nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
906
907 ELSIF nvl(oe_globals.g_commitment_balance, 0) >= l_children_commitment THEN
908 update oe_payments
909 set commitment_applied_amount = l_children_commitment
910 where line_id = l_children_line_id
911 and header_id = l_header_id
912 and nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
913
914 oe_globals.g_commitment_balance
915 := nvl(oe_globals.g_commitment_balance, 0) - l_children_commitment;
916 ELSIF nvl(oe_globals.g_commitment_balance, 0) < l_children_commitment THEN
917 update oe_payments
918 set commitment_applied_amount = nvl(oe_globals.g_commitment_balance, 0)
919 where line_id = l_children_line_id
920 and header_id =l_header_id
921 and nvl(payment_type_code, 'COMMITMENT') = 'COMMITMENT';
922
923 oe_globals.g_commitment_balance := 0;
924 END IF;
925
926 END LOOP;
927 CLOSE l_split_lines_cur;
928
929 IF l_debug_level > 0 THEN
930 oe_debug_pub.add( 'Exiting UPDATE_COMMITMENT. ', 1 ) ;
931 END IF;
932
933 END update_commitment;
934
935 FUNCTION Do_Commitment_Sequencing RETURN BOOLEAN IS
936
937 l_column1_exists VARCHAR2(1) := 'N';
938 l_column2_exists VARCHAR2(1) := 'N';
939 INVALID_COLUMN_NAME EXCEPTION;
940 PRAGMA EXCEPTION_INIT(INVALID_COLUMN_NAME, -904);
941
942 --
943 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
944 --
945 BEGIN
946
947 IF G_Do_Commitment_Sequencing <> FND_API.G_MISS_NUM THEN
948 IF G_Do_Commitment_Sequencing = 0 THEN
949 RETURN FALSE;
950 ELSE
951 RETURN TRUE;
952 END IF;
953 ELSE
954 IF Nvl(Fnd_Profile.Value('OE_COMMITMENT_SEQUENCING'),'N') = 'N' THEN
955 G_Do_Commitment_Sequencing := 0;
956 RETURN FALSE;
957 ELSE
958 BEGIN
959
960 EXECUTE IMMEDIATE
961 'SELECT ALLOCATE_TAX_FREIGHT
962 FROM RA_CUST_TRX_TYPES_ALL
963 WHERE ROWNUM = 1';
967 EXCEPTION WHEN INVALID_COLUMN_NAME THEN
964
965 l_column1_exists := 'Y';
966
968 l_column1_exists := 'N';
969 END;
970
971 IF l_column1_exists = 'N' THEN
972 G_Do_Commitment_Sequencing := 0;
973 RETURN FALSE;
974 ELSE
975 BEGIN
976
977 EXECUTE IMMEDIATE
978 'SELECT PROMISED_COMMITMENT_AMOUNT
979 FROM RA_INTERFACE_LINES_ALL
980 WHERE ROWNUM = 1';
981
982 l_column2_exists := 'Y';
983
984
985 EXCEPTION WHEN INVALID_COLUMN_NAME THEN
986 l_column2_exists := 'N';
987 END;
988
989 IF l_column2_exists = 'N' THEN
990 G_Do_Commitment_Sequencing := 0;
991 RETURN FALSE;
992 ELSE
993 G_Do_Commitment_Sequencing := 1;
994 RETURN TRUE;
995 END IF; -- end of column2 exists check
996
997 END IF; -- end of column1 exists chck
998 END IF; -- end of profile option checking.
999
1000 END IF; -- end of G_Do_Commitment_Sequencing is FND_API.G_MISS_NUM.
1001
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1005 THEN
1006 FND_MSG_PUB.Add_Exc_Msg
1007 ( G_PKG_NAME
1008 ,'OE_COMMITMENT_PVT.DO_COMMITMENT_SEQUENCING'
1009 );
1010 END IF;
1011
1012 END Do_Commitment_Sequencing;
1013
1014 procedure update_commitment_applied(
1015 p_line_id IN NUMBER
1016 , p_amount IN NUMBER
1017 , p_header_id IN NUMBER
1018 , p_commitment_id IN NUMBER
1019 , x_return_status OUT NOCOPY VARCHAR2
1020
1021 ) IS
1022
1023 l_commitment_applied_amount NUMBER := 0;
1024 l_commitment_bal NUMBER;
1025 l_class VARCHAR2(30);
1026 l_so_source_code VARCHAR2(30);
1027 l_oe_installed_flag VARCHAR2(30);
1028 l_amount_to_apply NUMBER;
1029 --bug 3560198
1030 l_new_commitment_bal NUMBER;
1031 l_commitment VARCHAR2(20);
1032 l_currency_code varchar2(30) := 'USD';
1033 l_precision NUMBER;
1034 l_ext_precision NUMBER;
1035 l_min_acct_unit NUMBER;
1036 --bug 3560198
1037
1038 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1039
1040 BEGIN
1041
1042 IF l_debug_level > 0 THEN
1043 oe_debug_pub.add('OEXVCMTB: Entering UPDATE_COMMITMENT_APPLIED FOR LINE_ID '||P_LINE_ID, 1 );
1044 oe_debug_pub.add('OEXVCMTB: p_amount is: '||p_amount, 3 );
1045 oe_debug_pub.add('OEXVCMTB: p_header_id is: '||p_header_id, 3 );
1046 oe_debug_pub.add('OEXVCMTB: p_commitment_id is: '||p_commitment_id, 3 );
1047 END IF;
1048
1049 -- get the commitment balance
1050 l_class := NULL;
1051 l_so_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
1052 l_oe_installed_flag := 'I';
1053
1054 l_commitment_bal := ARP_BAL_UTIL.GET_COMMITMENT_BALANCE(
1055 p_commitment_id
1056 ,l_class
1057 ,l_so_source_code
1058 ,l_oe_installed_flag );
1059
1060 IF l_debug_level > 0 THEN
1061 oe_debug_pub.add('OEXVCMTB: commitment balance is '||l_commitment_bal, 1 );
1062 END IF;
1063
1064 BEGIN
1065 SELECT nvl(commitment_applied_amount,0)
1066 INTO l_commitment_applied_amount
1067 FROM oe_payments
1068 WHERE header_id = p_header_id
1069 AND line_id = p_line_id
1070 AND payment_trx_id = p_commitment_id;
1071 EXCEPTION
1072 WHEN NO_DATA_FOUND THEN
1073 l_commitment_applied_amount := 0;
1074 END;
1075
1076 l_commitment_bal := l_commitment_bal + l_commitment_applied_amount;
1077
1078 IF l_debug_level > 0 THEN
1079 oe_debug_pub.add('OEXVCMTB: available commitment balance is '||l_commitment_bal, 1 );
1080 END IF;
1081
1082 IF p_amount <= l_commitment_bal THEN
1083 l_amount_to_apply := p_amount;
1084 ELSE
1085 l_amount_to_apply := l_commitment_bal;
1086
1087 IF l_debug_level > 0 THEN
1088 oe_debug_pub.add('OEXVCMTB: no sufficient balance '||l_amount_to_apply, 1 );
1089 END IF;
1090
1091 END IF;
1092
1093 IF l_debug_level > 0 THEN
1094 oe_debug_pub.add('OEXVCMTB: amount to apply is '||l_amount_to_apply, 1 );
1095 END IF;
1096
1097 --bug 3560198
1098 IF l_debug_level > 0 THEN
1099 oe_debug_pub.add('To display the commitment balance');
1100 END IF;
1101 l_new_commitment_bal := l_commitment_bal - l_amount_to_apply;
1102
1103 IF l_debug_level > 0 THEN
1104 oe_debug_pub.add('New commitment balance is : '||l_new_commitment_bal);
1105 END IF;
1106
1107 -- build currency format.
1108 IF g_fmt_mask IS NULL THEN
1109 BEGIN
1110 SELECT nvl(transactional_curr_code,'USD')
1111 INTO l_currency_code from oe_order_headers
1112 WHERE header_id=p_header_id;
1113
1114 EXCEPTION
1115 WHEN NO_DATA_FOUND THEN
1116 l_currency_code := 'USD';
1117 END ;
1118
1119 FND_CURRENCY.Get_Info(l_currency_code, -- IN variable
1120 l_precision,
1121 l_ext_precision,
1122 l_min_acct_unit);
1123
1124 FND_CURRENCY.Build_Format_Mask(G_Fmt_mask, 20, l_precision,
1125 l_min_acct_unit, TRUE
1126 );
1127 END IF;
1128
1129 FND_MESSAGE.Set_Name('ONT','OE_COM_BALANCE');
1130 FND_MESSAGE.Set_Token('COMMITMENT',l_commitment);
1131 FND_Message.Set_Token('BALANCE',to_char(l_new_commitment_bal, g_fmt_mask));
1132 OE_MSG_PUB.ADD;
1133 --bug 3560198
1134
1135
1136 UPDATE oe_payments
1137 SET commitment_applied_amount = l_amount_to_apply
1138 WHERE header_id = p_header_id
1139 AND line_id = p_line_id
1140 AND payment_trx_id = p_commitment_id;
1141
1142 IF l_debug_level > 0 THEN
1143 oe_debug_pub.add('OEXVCMTB: Exiting UPDATE_COMMITMENT_APPLIED. ', 1 );
1144 END IF;
1145
1146 END update_commitment_applied;
1147
1148 END oe_commitment_pvt;
1149