[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OE_PAYMENTS_UTIL
Source
1 PACKAGE BODY oe_payments_Util AS
2 /* $Header: OEXULCMB.pls 120.0.12020000.3 2013/04/12 06:01:14 suthumma ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'oe_payments_Util';
7
8 -- Procedure Update_Row
9 PROCEDURE Update_Row
10 ( p_payment_types_rec IN OUT NOCOPY Payment_Types_Rec_Type
11 )
12 IS
13 l_lock_control NUMBER;
14
15 --
16 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
17 --
18 BEGIN
19
20 -- increment lock_control by 1 whenever the record is updated
21 /**
22 SELECT lock_control
23 INTO l_lock_control
24 FROM OE_PRICE_ADJUSTMENTS
25 WHERE price_adjustment_id = p_payment_types_rec.price_adjustment_id;
26 **/
27
28 -- l_lock_control := l_lock_control + 1;
29
30 UPDATE oe_payments
31 SET PAYMENT_TRX_ID = p_payment_types_rec.payment_trx_id
32 , COMMITMENT_APPLIED_AMOUNT = p_payment_types_rec.commitment_applied_amount
33 , COMMITMENT_INTERFACED_AMOUNT = p_payment_types_rec.commitment_interfaced_amount
34 /* START PREPAYMENT */
35 , PAYMENT_SET_ID = p_payment_types_rec.payment_set_id
36 , PREPAID_AMOUNT = p_payment_types_rec.prepaid_amount
37 , PAYMENT_TYPE_CODE = p_payment_types_rec.payment_type_code
38 , CREDIT_CARD_CODE = p_payment_types_rec.credit_card_code
39 , CREDIT_CARD_NUMBER = p_payment_types_rec.credit_card_number
40 , CREDIT_CARD_HOLDER_NAME = p_payment_types_rec.credit_card_holder_name
41 , CREDIT_CARD_EXPIRATION_DATE = p_payment_types_rec.credit_card_expiration_date
42 /* END PREPAYMENT */
43 , PAYMENT_LEVEL_CODE = p_payment_types_rec.payment_level_code
44 , HEADER_ID = p_payment_types_rec.header_id
45 , LINE_ID = p_payment_types_rec.line_id
46 , LAST_UPDATE_DATE = p_payment_types_rec.last_update_date
47 , LAST_UPDATED_BY = p_payment_types_rec.last_updated_by
48
49 WHERE PAYMENT_TRX_ID = p_payment_types_rec.payment_trx_id
50 ;
51
52 -- p_payment_types_rec.lock_control := l_lock_control;
53 IF l_debug_level > 0 THEN
54 oe_debug_pub.add( 'EXITING OE_PAYMENTS_UTIL.UPDATE_ROW.' , 1 ) ;
55 END IF;
56
57 EXCEPTION
58
59 WHEN OTHERS THEN
60
61 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
62 THEN
63 FND_MSG_PUB.Add_Exc_Msg
64 ( G_PKG_NAME
65 , 'Update_Row'
66 );
67 END IF;
68
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70
71 END Update_Row;
72
73 -- Procedure Insert_Row
74 PROCEDURE Insert_Row
75 ( p_payment_types_rec IN OUT NOCOPY Payment_Types_Rec_Type
76 )
77 IS
78 l_lock_control NUMBER := 1;
79
80 --
81 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
82 --
83 BEGIN
84
85
86 INSERT INTO oe_payments
87 ( PAYMENT_TRX_ID
88 , COMMITMENT_APPLIED_AMOUNT
89 , COMMITMENT_INTERFACED_AMOUNT
90 /* START PREPAYMENT */
91 , PAYMENT_SET_ID
92 , PREPAID_AMOUNT
93 , PAYMENT_TYPE_CODE
94 , CREDIT_CARD_CODE
95 , CREDIT_CARD_NUMBER
96 , CREDIT_CARD_HOLDER_NAME
97 , CREDIT_CARD_EXPIRATION_DATE
98 /* END PREPAYMENT */
99 , PAYMENT_LEVEL_CODE
100 , HEADER_ID
101 , LINE_ID
102 , CREATION_DATE
103 , CREATED_BY
104 , LAST_UPDATE_DATE
105 , LAST_UPDATED_BY
106 , LAST_UPDATE_LOGIN
107 , REQUEST_ID
108 , PROGRAM_APPLICATION_ID
109 , PROGRAM_ID
110 , PROGRAM_UPDATE_DATE
111 , CONTEXT
112 , ATTRIBUTE1
113 , ATTRIBUTE2
114 , ATTRIBUTE3
115 , ATTRIBUTE4
116 , ATTRIBUTE5
117 , ATTRIBUTE6
118 , ATTRIBUTE7
119 , ATTRIBUTE8
120 , ATTRIBUTE9
121 , ATTRIBUTE10
122 , ATTRIBUTE11
123 , ATTRIBUTE12
124 , ATTRIBUTE13
125 , ATTRIBUTE14
126 , ATTRIBUTE15
127 , PAYMENT_NUMBER
128 )
129 VALUES
130 ( p_payment_types_rec.payment_trx_id
131 , p_payment_types_rec.commitment_applied_amount
132 , p_payment_types_rec.commitment_interfaced_amount
133 /* START PREPAYMENT */
134 , p_payment_types_rec.payment_set_id
135 , p_payment_types_rec.prepaid_amount
136 , p_payment_types_rec.payment_type_code
137 , p_payment_types_rec.credit_card_code
138 , p_payment_types_rec.credit_card_number
139 , p_payment_types_rec.credit_card_holder_name
140 , p_payment_types_rec.credit_card_expiration_date
141 /* END PREPAYMENT */
142 , p_payment_types_rec.payment_level_code
143 , p_payment_types_rec.header_id
144 , p_payment_types_rec.line_id
145 , p_payment_types_rec.creation_date
146 , p_payment_types_rec.created_by
147 , p_payment_types_rec.last_update_date
148 , p_payment_types_rec.last_updated_by
149 , p_payment_types_rec.last_update_login
150 , p_payment_types_rec.request_id
151 , p_payment_types_rec.program_application_id
152 , p_payment_types_rec.program_id
153 , p_payment_types_rec.program_update_date
154 , p_payment_types_rec.context
155 , p_payment_types_rec.attribute1
156 , p_payment_types_rec.attribute2
157 , p_payment_types_rec.attribute3
158 , p_payment_types_rec.attribute4
159 , p_payment_types_rec.attribute5
160 , p_payment_types_rec.attribute6
161 , p_payment_types_rec.attribute7
162 , p_payment_types_rec.attribute8
163 , p_payment_types_rec.attribute9
164 , p_payment_types_rec.attribute10
165 , p_payment_types_rec.attribute11
166 , p_payment_types_rec.attribute12
167 , p_payment_types_rec.attribute13
168 , p_payment_types_rec.attribute14
169 , p_payment_types_rec.attribute15
170 , p_payment_types_rec.payment_number
171 );
172
173 -- p_payment_types_rec.lock_control := l_lock_control;
174 IF l_debug_level > 0 THEN
175 oe_debug_pub.add( 'EXITING OE_PAYMENTS_UTIL.INSERT_ROW.' , 1 ) ;
176 END IF;
177
178 EXCEPTION
179 WHEN DUP_VAL_ON_INDEX Then
180 --self correction on this error so that it would not happen again
181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
182 THEN
183 FND_MSG_PUB.Add_Exc_Msg
184 ( G_PKG_NAME
185 , 'Insert_Row:'||SQLERRM
186 );
187 END IF;
188
189 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Insert_Row:'||SQLERRM);
190
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192
193 WHEN OTHERS THEN
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( G_PKG_NAME||':INSER_ROW:'||SQLERRM ) ;
196 END IF;
197 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
198 THEN
199 FND_MSG_PUB.Add_Exc_Msg
200 ( G_PKG_NAME
201 , 'Insert_Row:'||SQLERRM
202 );
203 END IF;
204
205 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Insert_Row:'||SQLERRM);
206
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208
209 END Insert_Row;
210
211 -- Procedure Delete_Row
212 PROCEDURE Delete_Row
213 ( p_payment_trx_id IN NUMBER := FND_API.G_MISS_NUM
214 , p_header_id IN NUMBER := FND_API.G_MISS_NUM
215 , p_line_id IN NUMBER := FND_API.G_MISS_NUM
216 )
217 IS
218 l_return_status VARCHAR2(30);
219
220 --
221 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
222 --
223 BEGIN
224
225 IF l_debug_level > 0 THEN
226 oe_debug_pub.add( 'ENTERING DELETING COMMITMENT FOR LINE: '||P_LINE_ID , 1 ) ;
227 END IF;
228
229 IF p_payment_trx_id IS NOT NULL AND p_payment_trx_id <> FND_API.G_MISS_NUM THEN
230 DELETE FROM oe_payments
231 WHERE payment_trx_id = p_payment_trx_id;
232 END IF;
233
234 IF p_line_id IS NOT NULL AND p_line_id <> FND_API.G_MISS_NUM
235 AND p_header_id IS NOT NULL AND p_header_id <> FND_API.G_MISS_NUM THEN
236 DELETE FROM oe_payments
237 WHERE line_id = p_line_id
238 AND header_id = p_header_id
239 AND payment_type_code = 'COMMITMENT'; --Added for Bug# 16654431
240 END IF;
241
242 EXCEPTION
243
244 WHEN OTHERS THEN
245 IF l_debug_level > 0 THEN
246 oe_debug_pub.add( G_PKG_NAME||':DELETE_ROW:'||SQLERRM ) ;
247 END IF;
248 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
249 THEN
250 FND_MSG_PUB.Add_Exc_Msg
251 ( G_PKG_NAME
252 , 'Delete_Row'
253 );
254 END IF;
255
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257
258 END Delete_Row;
259
260 -- Procedure Query_Row
261
262 PROCEDURE Query_Row
263 ( p_payment_trx_id IN NUMBER
264 , p_header_id IN NUMBER
265 , p_line_id IN NUMBER
266 , x_Payment_types_Rec IN OUT NOCOPY Payment_Types_Rec_Type
267 )
268 IS
269 l_Payment_Types_Tbl Payment_Types_Tbl_Type;
270 l_return_status VARCHAR2(30);
271 BEGIN
272
273 Query_Rows
274 ( p_payment_trx_id => p_payment_trx_id
275 , p_header_id => p_header_id
276 , p_line_id => p_line_id
277 , x_Payment_Types_Tbl => l_Payment_Types_Tbl
278 , x_return_status => l_return_status
279 );
280 x_Payment_Types_Rec := l_Payment_Types_Tbl(1);
281
282
283 END Query_Row;
284
285 -- Procedure Query_Rows
286 PROCEDURE Query_Rows
287 ( p_payment_trx_id IN NUMBER := FND_API.G_MISS_NUM
288 , p_Header_id IN NUMBER := FND_API.G_MISS_NUM
289 , p_line_id IN NUMBER := FND_API.G_MISS_NUM
290 , x_Payment_Types_Tbl IN OUT NOCOPY Payment_Types_Tbl_Type
291 , x_return_status OUT NOCOPY VARCHAR2
292
293 )
294 IS
295 l_count NUMBER;
296
297 CURSOR l_payment_types_csr IS
298 SELECT PAYMENT_TRX_ID
299 , COMMITMENT_APPLIED_AMOUNT
300 , COMMITMENT_INTERFACED_AMOUNT
301 /* START PREPAYMENT */
302 , PAYMENT_SET_ID
303 , PREPAID_AMOUNT
304 , PAYMENT_TYPE_CODE
305 , CREDIT_CARD_CODE
306 , CREDIT_CARD_NUMBER
307 , CREDIT_CARD_HOLDER_NAME
308 , CREDIT_CARD_EXPIRATION_DATE
309 /* END PREPAYMENT */
310 , PAYMENT_LEVEL_CODE
311 , HEADER_ID
312 , LINE_ID
313 , CREATION_DATE
314 , CREATED_BY
315 , LAST_UPDATE_DATE
316 , LAST_UPDATED_BY
317 , LAST_UPDATE_LOGIN
318 , REQUEST_ID
319 , PROGRAM_APPLICATION_ID
320 , PROGRAM_ID
321 , PROGRAM_UPDATE_DATE
322 , CONTEXT
323 , ATTRIBUTE1
324 , ATTRIBUTE2
325 , ATTRIBUTE3
326 , ATTRIBUTE4
327 , ATTRIBUTE5
328 , ATTRIBUTE6
329 , ATTRIBUTE7
330 , ATTRIBUTE8
331 , ATTRIBUTE9
332 , ATTRIBUTE10
333 , ATTRIBUTE11
334 , ATTRIBUTE12
335 , ATTRIBUTE13
336 , ATTRIBUTE14
337 , ATTRIBUTE15
338 , PAYMENT_AMOUNT
339 FROM oe_payments
340 WHERE PAYMENT_TRX_ID = p_payment_trx_id
341 AND nvl(PAYMENT_TYPE_CODE, 'COMMITMENT') = 'COMMITMENT'
342 AND line_id = p_line_id
343 AND HEADER_ID = p_header_id
344 UNION
345 SELECT PAYMENT_TRX_ID
346 , COMMITMENT_APPLIED_AMOUNT
347 , COMMITMENT_INTERFACED_AMOUNT
348 /* START PREPAYMENT */
349 , PAYMENT_SET_ID
350 , PREPAID_AMOUNT
351 , PAYMENT_TYPE_CODE
352 , CREDIT_CARD_CODE
353 , CREDIT_CARD_NUMBER
354 , CREDIT_CARD_HOLDER_NAME
355 , CREDIT_CARD_EXPIRATION_DATE
356 /* END PREPAYMENT */
357 , PAYMENT_LEVEL_CODE
358 , HEADER_ID
359 , LINE_ID
360 , CREATION_DATE
361 , CREATED_BY
362 , LAST_UPDATE_DATE
363 , LAST_UPDATED_BY
364 , LAST_UPDATE_LOGIN
365 , REQUEST_ID
366 , PROGRAM_APPLICATION_ID
367 , PROGRAM_ID
368 , PROGRAM_UPDATE_DATE
369 , CONTEXT
370 , ATTRIBUTE1
371 , ATTRIBUTE2
372 , ATTRIBUTE3
373 , ATTRIBUTE4
374 , ATTRIBUTE5
375 , ATTRIBUTE6
376 , ATTRIBUTE7
377 , ATTRIBUTE8
378 , ATTRIBUTE9
379 , ATTRIBUTE10
380 , ATTRIBUTE11
381 , ATTRIBUTE12
382 , ATTRIBUTE13
383 , ATTRIBUTE14
384 , ATTRIBUTE15
385 , PAYMENT_AMOUNT
386 FROM oe_payments
387 WHERE line_id = p_line_id
388 AND nvl(PAYMENT_TYPE_CODE, 'COMMITMENT') = 'COMMITMENT'
389 AND header_id = p_header_id
390 ;
391
392 --
393 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
394 --
395 BEGIN
396
397 /***
398 IF
399 (p_price_adjustment_id IS NOT NULL
400 AND
401 p_price_adjustment_id <> FND_API.G_MISS_NUM)
402 AND
403 (p_line_id IS NOT NULL
404 AND
405 p_line_id <> FND_API.G_MISS_NUM)
406 THEN
407 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
408 THEN
409 FND_MSG_PUB.Add_Exc_Msg
410 ( G_PKG_NAME
411 , 'Query Rows'
412 , 'Keys are mutually exclusive: price_adjustment_id = '|| p_price_adjustment_id || ', line_id = '|| p_line_id
413 );
414 END IF;
415
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417
418 END IF;
419 ***/
420
421
422 -- Loop over fetched records
423
424 l_count := 1;
425 FOR l_implicit_rec IN l_payment_types_csr LOOP
426
427 x_payment_types_tbl(l_count).payment_trx_id := l_implicit_rec.payment_trx_id;
428 x_payment_types_tbl(l_count).commitment_applied_amount := l_implicit_rec.commitment_applied_amount;
429 x_payment_types_tbl(l_count).commitment_interfaced_amount := l_implicit_rec.commitment_interfaced_amount;
430 /* START PREPAYMENT */
431 x_payment_types_tbl(l_count).payment_set_id := l_implicit_rec.payment_set_id;
432 x_payment_types_tbl(l_count).prepaid_amount := l_implicit_rec.prepaid_amount;
433 x_payment_types_tbl(l_count).payment_type_code := l_implicit_rec.payment_type_code;
434 x_payment_types_tbl(l_count).credit_card_code := l_implicit_rec.credit_card_code;
435 x_payment_types_tbl(l_count).credit_card_number := l_implicit_rec.credit_card_number;
436 x_payment_types_tbl(l_count).credit_card_holder_name := l_implicit_rec.credit_card_holder_name;
437 x_payment_types_tbl(l_count).credit_card_expiration_date := l_implicit_rec.credit_card_expiration_date;
438 /* END PREPAYMENT */
439 x_payment_types_tbl(l_count).payment_level_code := l_implicit_rec.payment_level_code;
440 x_payment_types_tbl(l_count).header_id := l_implicit_rec.HEADER_ID;
441 x_payment_types_tbl(l_count).line_id := l_implicit_rec.LINE_ID;
442 x_payment_types_tbl(l_count).creation_date := l_implicit_rec.CREATION_DATE;
443 x_payment_types_tbl(l_count).created_by := l_implicit_rec.CREATED_BY;
444 x_payment_types_tbl(l_count).last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
445 x_payment_types_tbl(l_count).last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
446 x_payment_types_tbl(l_count).last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
447 x_payment_types_tbl(l_count).request_id := l_implicit_rec.REQUEST_ID;
448 x_payment_types_tbl(l_count).program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
449 x_payment_types_tbl(l_count).program_id := l_implicit_rec.PROGRAM_ID;
450 x_payment_types_tbl(l_count).program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
451 x_payment_types_tbl(l_count).context := l_implicit_rec.CONTEXT;
452 x_payment_types_tbl(l_count).attribute1 := l_implicit_rec.ATTRIBUTE1;
453 x_payment_types_tbl(l_count).attribute2 := l_implicit_rec.ATTRIBUTE2;
454 x_payment_types_tbl(l_count).attribute3 := l_implicit_rec.ATTRIBUTE3;
455 x_payment_types_tbl(l_count).attribute4 := l_implicit_rec.ATTRIBUTE4;
456 x_payment_types_tbl(l_count).attribute5 := l_implicit_rec.ATTRIBUTE5;
457 x_payment_types_tbl(l_count).attribute6 := l_implicit_rec.ATTRIBUTE6;
458 x_payment_types_tbl(l_count).attribute7 := l_implicit_rec.ATTRIBUTE7;
459 x_payment_types_tbl(l_count).attribute8 := l_implicit_rec.ATTRIBUTE8;
460 x_payment_types_tbl(l_count).attribute9 := l_implicit_rec.ATTRIBUTE9;
461 x_payment_types_tbl(l_count).attribute10 := l_implicit_rec.ATTRIBUTE10;
462 x_payment_types_tbl(l_count).attribute11 := l_implicit_rec.ATTRIBUTE11;
463 x_payment_types_tbl(l_count).attribute12 := l_implicit_rec.ATTRIBUTE12;
464 x_payment_types_tbl(l_count).attribute13 := l_implicit_rec.ATTRIBUTE13;
465 x_payment_types_tbl(l_count).attribute14 := l_implicit_rec.ATTRIBUTE14;
466 x_payment_types_tbl(l_count).attribute15 := l_implicit_rec.ATTRIBUTE15;
467 x_payment_types_tbl(l_count).payment_amount := l_implicit_rec.payment_amount;
468
469 -- set values for non-DB fields
470 x_payment_types_tbl(l_count).db_flag := FND_API.G_TRUE;
471 x_payment_types_tbl(l_count).operation := FND_API.G_MISS_CHAR;
472 x_payment_types_tbl(l_count).return_status := FND_API.G_MISS_CHAR;
473
474 l_count := l_count + 1;
475
476 END LOOP;
477
478
479 -- PK sent and no rows found
480 /***
481 IF
482 (p_payment_id IS NOT NULL
483 AND
484 p_payment_id <> FND_API.G_MISS_NUM)
485 AND
486 (x_payment_types_tbl.COUNT = 0)
487 THEN
488 RAISE NO_DATA_FOUND;
489 END IF;
490 ***/
491
492
493 -- Return fetched table
494
495 EXCEPTION
496
497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
498 IF l_debug_level > 0 THEN
499 oe_debug_pub.add( G_PKG_NAME||':QUERY_ROW:'||SQLERRM ) ;
500 END IF;
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502
503 WHEN OTHERS THEN
504 IF l_debug_level > 0 THEN
505 oe_debug_pub.add( G_PKG_NAME||':QUERY_ROW:'||SQLERRM ) ;
506 END IF;
507 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
508 THEN
509 FND_MSG_PUB.Add_Exc_Msg
510 ( G_PKG_NAME
511 , 'Query_Rows'
512 );
513 END IF;
514
515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
516
517 END Query_Rows;
518
519 -- get the balance of applied commitment
520 FUNCTION Get_Uninvoiced_Commitment_Bal
521 (
522 p_customer_trx_id IN NUMBER
523 )
524 RETURN NUMBER IS
525
526 l_uninv_commitment_bal NUMBER := 0;
527 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
528 BEGIN
529 IF l_debug_level > 0 THEN
530 oe_debug_pub.add('ENTERING OE_PAYMENTS_UTIL.GET_UNINVOICED_COMMITMENT_BAL');
531 END IF;
532
533 --14723264 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
534
535 --bug3567339 added the following IF condition and the code for the else part.
536 IF OE_Commitment_Pvt.Do_Commitment_Sequencing THEN
537
538 SELECT
539 SUM(nvl(commitment_applied_amount, 0)
540 - nvl(commitment_interfaced_amount,0))
541 INTO l_uninv_commitment_bal
542 FROM oe_payments opt
543 WHERE opt.payment_trx_id = p_customer_trx_id;
544 IF l_debug_level > 0 THEN
545 oe_debug_pub.add('pviprana: l_uninv_commitment_bal is ' || l_uninv_commitment_bal);
546 oe_debug_pub.add('pviprana: p_customer_trx_id is ' || p_customer_trx_id);
547 END IF;
548 ELSE
549 -- when profile options is set to NO
550 SELECT
551 NVL( SUM( ( NVL( ordered_quantity, 0 ) -
552 --bug3604062
553 -- NVL( cancelled_quantity, 0 ) -
554 NVL( invoiced_quantity, 0 )
555 ) *
556 NVL( unit_selling_price, 0 )
557 ), 0 )
558 INTO l_uninv_commitment_bal
559 FROM oe_order_lines_all
560 WHERE commitment_id = p_customer_trx_id
561 AND NVL(line_category_code,'STANDARD') <> 'RETURN'
562 AND NVL(invoice_interface_status_code,'NO') <> 'YES';
563
564 IF l_debug_level > 0 THEN
565 oe_debug_pub.add('pviprana: l_uninv_commitment_bal is ' || l_uninv_commitment_bal);
566 oe_debug_pub.add('pviprana: p_customer_trx_id is ' || p_customer_trx_id);
567 END IF;
568 END IF; --bug3567339 end
569
570 /*14723264 start
571 ELSE
572
573 SELECT
574 NVL( SUM( ( NVL( ordered_quantity, 0 ) -
575 NVL( cancelled_quantity, 0 ) -
576 NVL( invoiced_quantity, 0 )
577 ) *
578 NVL( selling_price, 0 )
579 ), 0 )
580 INTO l_uninv_commitment_bal
581 FROM so_lines
582 WHERE commitment_id = p_customer_trx_id
583 AND line_type_code IN ( 'REGULAR', 'DETAIL');
584
585
586 END IF;
587 14723264 end*/
588
589 RETURN (l_uninv_commitment_bal);
590
591 END Get_Uninvoiced_Commitment_Bal;
592
593
594 END oe_payments_Util;