[Home] [Help]
PACKAGE BODY: APPS.OE_PAYMENTS_UTIL
Source
1 PACKAGE BODY oe_payments_Util AS
2 /* $Header: OEXULCMB.pls 115.21 2004/06/03 23:49:12 lkxu 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 END IF;
240
241 EXCEPTION
242
243 WHEN OTHERS THEN
244 IF l_debug_level > 0 THEN
245 oe_debug_pub.add( G_PKG_NAME||':DELETE_ROW:'||SQLERRM ) ;
246 END IF;
247 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
248 THEN
249 FND_MSG_PUB.Add_Exc_Msg
250 ( G_PKG_NAME
251 , 'Delete_Row'
252 );
253 END IF;
254
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256
257 END Delete_Row;
258
259 -- Procedure Query_Row
260
261 PROCEDURE Query_Row
262 ( p_payment_trx_id IN NUMBER
263 , p_header_id IN NUMBER
264 , p_line_id IN NUMBER
265 , x_Payment_types_Rec IN OUT NOCOPY Payment_Types_Rec_Type
266 )
267 IS
268 l_Payment_Types_Tbl Payment_Types_Tbl_Type;
269 l_return_status VARCHAR2(30);
270 BEGIN
271
272 Query_Rows
273 ( p_payment_trx_id => p_payment_trx_id
274 , p_header_id => p_header_id
275 , p_line_id => p_line_id
276 , x_Payment_Types_Tbl => l_Payment_Types_Tbl
277 , x_return_status => l_return_status
278 );
279 x_Payment_Types_Rec := l_Payment_Types_Tbl(1);
280
281
282 END Query_Row;
283
284 -- Procedure Query_Rows
285 PROCEDURE Query_Rows
286 ( p_payment_trx_id IN NUMBER := FND_API.G_MISS_NUM
287 , p_Header_id IN NUMBER := FND_API.G_MISS_NUM
288 , p_line_id IN NUMBER := FND_API.G_MISS_NUM
289 , x_Payment_Types_Tbl IN OUT NOCOPY Payment_Types_Tbl_Type
290 , x_return_status OUT NOCOPY VARCHAR2
291
292 )
293 IS
294 l_count NUMBER;
295
296 CURSOR l_payment_types_csr IS
297 SELECT PAYMENT_TRX_ID
298 , COMMITMENT_APPLIED_AMOUNT
299 , COMMITMENT_INTERFACED_AMOUNT
300 /* START PREPAYMENT */
301 , PAYMENT_SET_ID
302 , PREPAID_AMOUNT
303 , PAYMENT_TYPE_CODE
304 , CREDIT_CARD_CODE
305 , CREDIT_CARD_NUMBER
306 , CREDIT_CARD_HOLDER_NAME
307 , CREDIT_CARD_EXPIRATION_DATE
308 /* END PREPAYMENT */
309 , PAYMENT_LEVEL_CODE
310 , HEADER_ID
311 , LINE_ID
312 , CREATION_DATE
313 , CREATED_BY
314 , LAST_UPDATE_DATE
315 , LAST_UPDATED_BY
316 , LAST_UPDATE_LOGIN
317 , REQUEST_ID
318 , PROGRAM_APPLICATION_ID
319 , PROGRAM_ID
320 , PROGRAM_UPDATE_DATE
321 , CONTEXT
322 , ATTRIBUTE1
323 , ATTRIBUTE2
324 , ATTRIBUTE3
325 , ATTRIBUTE4
326 , ATTRIBUTE5
327 , ATTRIBUTE6
328 , ATTRIBUTE7
329 , ATTRIBUTE8
330 , ATTRIBUTE9
331 , ATTRIBUTE10
332 , ATTRIBUTE11
333 , ATTRIBUTE12
334 , ATTRIBUTE13
335 , ATTRIBUTE14
336 , ATTRIBUTE15
337 , PAYMENT_AMOUNT
338 FROM oe_payments
339 WHERE PAYMENT_TRX_ID = p_payment_trx_id
340 AND nvl(PAYMENT_TYPE_CODE, 'COMMITMENT') = 'COMMITMENT'
341 AND line_id = p_line_id
342 AND HEADER_ID = p_header_id
343 UNION
344 SELECT PAYMENT_TRX_ID
345 , COMMITMENT_APPLIED_AMOUNT
346 , COMMITMENT_INTERFACED_AMOUNT
347 /* START PREPAYMENT */
348 , PAYMENT_SET_ID
349 , PREPAID_AMOUNT
350 , PAYMENT_TYPE_CODE
351 , CREDIT_CARD_CODE
352 , CREDIT_CARD_NUMBER
353 , CREDIT_CARD_HOLDER_NAME
354 , CREDIT_CARD_EXPIRATION_DATE
355 /* END PREPAYMENT */
356 , PAYMENT_LEVEL_CODE
357 , HEADER_ID
358 , LINE_ID
359 , CREATION_DATE
360 , CREATED_BY
361 , LAST_UPDATE_DATE
362 , LAST_UPDATED_BY
363 , LAST_UPDATE_LOGIN
364 , REQUEST_ID
365 , PROGRAM_APPLICATION_ID
366 , PROGRAM_ID
367 , PROGRAM_UPDATE_DATE
368 , CONTEXT
369 , ATTRIBUTE1
370 , ATTRIBUTE2
371 , ATTRIBUTE3
372 , ATTRIBUTE4
373 , ATTRIBUTE5
374 , ATTRIBUTE6
375 , ATTRIBUTE7
376 , ATTRIBUTE8
377 , ATTRIBUTE9
378 , ATTRIBUTE10
379 , ATTRIBUTE11
380 , ATTRIBUTE12
381 , ATTRIBUTE13
382 , ATTRIBUTE14
383 , ATTRIBUTE15
384 , PAYMENT_AMOUNT
385 FROM oe_payments
386 WHERE line_id = p_line_id
387 AND nvl(PAYMENT_TYPE_CODE, 'COMMITMENT') = 'COMMITMENT'
388 AND header_id = p_header_id
389 ;
390
391 --
392 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
393 --
394 BEGIN
395
396 /***
397 IF
398 (p_price_adjustment_id IS NOT NULL
399 AND
400 p_price_adjustment_id <> FND_API.G_MISS_NUM)
401 AND
402 (p_line_id IS NOT NULL
403 AND
404 p_line_id <> FND_API.G_MISS_NUM)
405 THEN
406 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
407 THEN
408 FND_MSG_PUB.Add_Exc_Msg
409 ( G_PKG_NAME
410 , 'Query Rows'
411 , 'Keys are mutually exclusive: price_adjustment_id = '|| p_price_adjustment_id || ', line_id = '|| p_line_id
412 );
413 END IF;
414
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416
417 END IF;
418 ***/
419
420
421 -- Loop over fetched records
422
423 l_count := 1;
424 FOR l_implicit_rec IN l_payment_types_csr LOOP
425
426 x_payment_types_tbl(l_count).payment_trx_id := l_implicit_rec.payment_trx_id;
427 x_payment_types_tbl(l_count).commitment_applied_amount := l_implicit_rec.commitment_applied_amount;
428 x_payment_types_tbl(l_count).commitment_interfaced_amount := l_implicit_rec.commitment_interfaced_amount;
429 /* START PREPAYMENT */
430 x_payment_types_tbl(l_count).payment_set_id := l_implicit_rec.payment_set_id;
431 x_payment_types_tbl(l_count).prepaid_amount := l_implicit_rec.prepaid_amount;
432 x_payment_types_tbl(l_count).payment_type_code := l_implicit_rec.payment_type_code;
433 x_payment_types_tbl(l_count).credit_card_code := l_implicit_rec.credit_card_code;
434 x_payment_types_tbl(l_count).credit_card_number := l_implicit_rec.credit_card_number;
435 x_payment_types_tbl(l_count).credit_card_holder_name := l_implicit_rec.credit_card_holder_name;
436 x_payment_types_tbl(l_count).credit_card_expiration_date := l_implicit_rec.credit_card_expiration_date;
437 /* END PREPAYMENT */
438 x_payment_types_tbl(l_count).payment_level_code := l_implicit_rec.payment_level_code;
439 x_payment_types_tbl(l_count).header_id := l_implicit_rec.HEADER_ID;
440 x_payment_types_tbl(l_count).line_id := l_implicit_rec.LINE_ID;
441 x_payment_types_tbl(l_count).creation_date := l_implicit_rec.CREATION_DATE;
442 x_payment_types_tbl(l_count).created_by := l_implicit_rec.CREATED_BY;
443 x_payment_types_tbl(l_count).last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
444 x_payment_types_tbl(l_count).last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
448 x_payment_types_tbl(l_count).program_id := l_implicit_rec.PROGRAM_ID;
445 x_payment_types_tbl(l_count).last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
446 x_payment_types_tbl(l_count).request_id := l_implicit_rec.REQUEST_ID;
447 x_payment_types_tbl(l_count).program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
449 x_payment_types_tbl(l_count).program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
450 x_payment_types_tbl(l_count).context := l_implicit_rec.CONTEXT;
451 x_payment_types_tbl(l_count).attribute1 := l_implicit_rec.ATTRIBUTE1;
452 x_payment_types_tbl(l_count).attribute2 := l_implicit_rec.ATTRIBUTE2;
453 x_payment_types_tbl(l_count).attribute3 := l_implicit_rec.ATTRIBUTE3;
454 x_payment_types_tbl(l_count).attribute4 := l_implicit_rec.ATTRIBUTE4;
455 x_payment_types_tbl(l_count).attribute5 := l_implicit_rec.ATTRIBUTE5;
456 x_payment_types_tbl(l_count).attribute6 := l_implicit_rec.ATTRIBUTE6;
457 x_payment_types_tbl(l_count).attribute7 := l_implicit_rec.ATTRIBUTE7;
458 x_payment_types_tbl(l_count).attribute8 := l_implicit_rec.ATTRIBUTE8;
459 x_payment_types_tbl(l_count).attribute9 := l_implicit_rec.ATTRIBUTE9;
460 x_payment_types_tbl(l_count).attribute10 := l_implicit_rec.ATTRIBUTE10;
461 x_payment_types_tbl(l_count).attribute11 := l_implicit_rec.ATTRIBUTE11;
462 x_payment_types_tbl(l_count).attribute12 := l_implicit_rec.ATTRIBUTE12;
463 x_payment_types_tbl(l_count).attribute13 := l_implicit_rec.ATTRIBUTE13;
464 x_payment_types_tbl(l_count).attribute14 := l_implicit_rec.ATTRIBUTE14;
465 x_payment_types_tbl(l_count).attribute15 := l_implicit_rec.ATTRIBUTE15;
466 x_payment_types_tbl(l_count).payment_amount := l_implicit_rec.payment_amount;
467
468 -- set values for non-DB fields
469 x_payment_types_tbl(l_count).db_flag := FND_API.G_TRUE;
470 x_payment_types_tbl(l_count).operation := FND_API.G_MISS_CHAR;
471 x_payment_types_tbl(l_count).return_status := FND_API.G_MISS_CHAR;
472
473 l_count := l_count + 1;
474
475 END LOOP;
476
477
478 -- PK sent and no rows found
479 /***
480 IF
481 (p_payment_id IS NOT NULL
482 AND
483 p_payment_id <> FND_API.G_MISS_NUM)
484 AND
485 (x_payment_types_tbl.COUNT = 0)
486 THEN
487 RAISE NO_DATA_FOUND;
488 END IF;
489 ***/
490
491
492 -- Return fetched table
493
494 EXCEPTION
495
496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add( G_PKG_NAME||':QUERY_ROW:'||SQLERRM ) ;
499 END IF;
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501
502 WHEN OTHERS THEN
503 IF l_debug_level > 0 THEN
504 oe_debug_pub.add( G_PKG_NAME||':QUERY_ROW:'||SQLERRM ) ;
505 END IF;
506 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
507 THEN
508 FND_MSG_PUB.Add_Exc_Msg
509 ( G_PKG_NAME
510 , 'Query_Rows'
511 );
512 END IF;
513
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515
516 END Query_Rows;
517
518 -- get the balance of applied commitment
519 FUNCTION Get_Uninvoiced_Commitment_Bal
520 (
521 p_customer_trx_id IN NUMBER
522 )
523 RETURN NUMBER IS
524
525 l_uninv_commitment_bal NUMBER := 0;
526 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
527 BEGIN
528 IF l_debug_level > 0 THEN
529 oe_debug_pub.add('ENTERING OE_PAYMENTS_UTIL.GET_UNINVOICED_COMMITMENT_BAL');
530 END IF;
531
532 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
533
534 --bug3567339 added the following IF condition and the code for the else part.
535 IF OE_Commitment_Pvt.Do_Commitment_Sequencing THEN
536
537 SELECT
538 SUM(nvl(commitment_applied_amount, 0)
539 - nvl(commitment_interfaced_amount,0))
540 INTO l_uninv_commitment_bal
541 FROM oe_payments opt
542 WHERE opt.payment_trx_id = p_customer_trx_id;
543 IF l_debug_level > 0 THEN
544 oe_debug_pub.add('pviprana: l_uninv_commitment_bal is ' || l_uninv_commitment_bal);
545 oe_debug_pub.add('pviprana: p_customer_trx_id is ' || p_customer_trx_id);
546 END IF;
547 ELSE
548 -- when profile options is set to NO
549 SELECT
550 NVL( SUM( ( NVL( ordered_quantity, 0 ) -
551 --bug3604062
552 -- NVL( cancelled_quantity, 0 ) -
553 NVL( invoiced_quantity, 0 )
554 ) *
555 NVL( unit_selling_price, 0 )
556 ), 0 )
557 INTO l_uninv_commitment_bal
558 FROM oe_order_lines_all
559 WHERE commitment_id = p_customer_trx_id
560 AND NVL(line_category_code,'STANDARD') <> 'RETURN'
561 AND NVL(invoice_interface_status_code,'NO') <> 'YES';
562
563 IF l_debug_level > 0 THEN
564 oe_debug_pub.add('pviprana: l_uninv_commitment_bal is ' || l_uninv_commitment_bal);
565 oe_debug_pub.add('pviprana: p_customer_trx_id is ' || p_customer_trx_id);
566 END IF;
567 END IF; --bug3567339 end
568
569 ELSE
570
571 SELECT
572 NVL( SUM( ( NVL( ordered_quantity, 0 ) -
573 NVL( cancelled_quantity, 0 ) -
574 NVL( invoiced_quantity, 0 )
575 ) *
576 NVL( selling_price, 0 )
577 ), 0 )
578 INTO l_uninv_commitment_bal
579 FROM so_lines
580 WHERE commitment_id = p_customer_trx_id
581 AND line_type_code IN ( 'REGULAR', 'DETAIL');
582
583
584 END IF;
585
586 RETURN (l_uninv_commitment_bal);
587
588 END Get_Uninvoiced_Commitment_Bal;
589
590
591 END oe_payments_Util;