[Home] [Help]
PACKAGE BODY: APPS.OKL_BILLING_UTIL_PVT
Source
1 PACKAGE BODY OKL_BILLING_UTIL_PVT AS
2 /* $Header: OKLRBULB.pls 120.9.12010000.2 2009/01/30 05:54:02 rpillay ship $ */
3 --Start of wrapper code generated automatically by Debug Code Generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 --End of wrapper code generated automatically by Debug code generator
9
10 ------------------------------------------------------------------------------
11
12 -- **** Authoring requirement APIs ****
13 -------------------------------------------------------------------------------
14 -- Start of comments
15 --
16 -- Procedure Name : LAST_INVOICE_DATE
17 -- Description : api to return last(max) invoice date for a contract
18 -- Business Rules :
19 -- Parameters :
20 -- p_contract_id - Contract ID
21 --
22 -- x_invoice_date - Last invoice date
23 --
24 -- Version : 1.0
25 -- End of comments
26 -------------------------------------------------------------------------------
27 --Comments:
28 --1. need to tune the sql for the cursor, gives full scan for ra_customer_trx_lines_all
29
30 PROCEDURE Last_Invoice_Date(
31 p_api_version IN NUMBER
32 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ,p_contract_id IN NUMBER
37 ,x_invoice_date OUT NOCOPY DATE
38 )
39 IS
40
41 -- Cursor to obtain the last invoice date for a contract
42 CURSOR c_last_invoice_date(p_contract_id IN NUMBER) IS
43 SELECT max(ractrx.trx_date)
44 FROM ra_customer_trx_all ractrx
45 WHERE EXISTS (
46 SELECT 'x'
47 FROM ra_customer_trx_lines_all ractrl
48 WHERE ractrl.customer_trx_id = ractrx.customer_trx_id
49 AND ractrl.interface_line_attribute6 = (SELECT contract_number
50 FROM okc_k_headers_b
51 WHERE ID = p_contract_id)
52 );
53 l_invoice_date DATE;
54 l_api_name VARCHAR2(60) := 'Last Invoice Date';
55 BEGIN
56 OPEN c_last_invoice_date(p_contract_id);
57 FETCH c_last_invoice_date INTO l_invoice_date;
58 CLOSE c_last_invoice_date;
59
60 x_invoice_date := l_invoice_date;
61 x_return_status := 'S';
62 EXCEPTION
63 WHEN OTHERS THEN
64 IF c_last_invoice_date%ISOPEN THEN
65 CLOSE c_last_invoice_date;
66 END IF;
67
68 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
69 p_api_name => l_api_name,
70 p_pkg_name => G_PKG_NAME,
71 p_exc_name => 'OTHERS',
72 x_msg_count => x_msg_count,
73 x_msg_data => x_msg_data,
74 p_api_type => '_PVT');
75
76 END Last_Invoice_Date;
77
78
79
80 -------------------------------------------------------------------------------
81 -- Start of comments
82 --
83 -- Procedure Name : INVOICE_AMOUNT_FOR_STREAM
84 -- Description : api to return all the the total invoice line amount for
85 -- stream type purpose = p_stream_purpose ('UNSCHEDULED_PRINCIPAL_PAYMENT') for
86 -- each contract.
87 -- Select khr_id, sum(line_amount) group by khr_id where OKL invoice
88 -- and stream type purpose matches with invice line context field.
89 -- Business Rules :
90 -- Parameters :
91 -- p_stream_purpose - Stream type purpose
92 --
93 -- x_contract_invoice_tbl - table containing contract_id
94 -- and invoice amount
95 --
96 -- Version : 1.0
97 -- End of comments
98 -------------------------------------------------------------------------------
99 -- Comments:
100 --
101 PROCEDURE INVOICE_AMOUNT_FOR_STREAM(
102 p_api_version IN NUMBER
103 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
104 ,x_return_status OUT NOCOPY VARCHAR2
105 ,x_msg_count OUT NOCOPY NUMBER
106 ,x_msg_data OUT NOCOPY VARCHAR2
107 ,p_stream_purpose IN VARCHAR2
108 ,x_contract_invoice_tbl OUT NOCOPY contract_invoice_tbl
109 )
110 IS
111 CURSOR c_strm_invoice_amt(p_stream_purpose IN VARCHAR2) IS
112 SELECT chr.id,
113 SUM(ractrl.amount_due_original) amount
114 FROM ra_customer_trx_lines_all ractrl,
115 okc_k_headers_b chr
116 WHERE chr.contract_number = ractrl.interface_line_attribute6
117 AND EXISTS(SELECT sty.code
118 FROM OKL_STRM_TYPE_B sty
119 WHERE sty.stream_type_purpose = p_stream_purpose
120 AND sty.code = ractrl.interface_line_attribute9)
121 GROUP BY chr.id;
122
123 l_api_name VARCHAR2(60) := 'Invoice Amount for Stream';
124 l_contract_invoice_tbl contract_invoice_tbl;
125 i NUMBER;
126 BEGIN
127 i := 1;
128 --Loop through the cursor
129 FOR l_invoice_amt_rec IN c_strm_invoice_amt(p_stream_purpose) LOOP
130 l_contract_invoice_tbl(i).khr_id := l_invoice_amt_rec.id;
131 l_contract_invoice_tbl(i).amount := l_invoice_amt_rec.amount;
132 i := i + 1;
133 END LOOP;
134
135 x_contract_invoice_tbl := l_contract_invoice_tbl;
136 x_return_status := 'S';
137 EXCEPTION
138 WHEN OTHERS THEN
139
140 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
141 p_api_name => l_api_name,
142 p_pkg_name => G_PKG_NAME,
143 p_exc_name => 'OTHERS',
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data,
146 p_api_type => '_PVT');
147
148 END Invoice_Amount_For_Stream;
149
150
151 FUNCTION INVOICE_LINE_AMOUNT_ORIG(
152 p_customer_trx_id IN NUMBER,
153 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
154
155 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
156 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
157 FROM RA_CUSTOMER_TRX_LINES_ALL
158 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id;
159
160 l_line_amount NUMBER;
161 l_line_tax_amount NUMBER;
162 l_line_original_due NUMBER;
163
164 BEGIN
165
166 --Bug# 7720775
167 l_line_amount := INV_LN_AMT_ORIG_WOTAX(p_customer_trx_id,
168 p_customer_trx_line_id);
169
170 FOR r IN invoice_line_tax_amount(p_customer_trx_line_id)
171 LOOP
172 l_line_tax_amount := r.LINE_TAX_AMOUNT;
173 END LOOP;
174
175 l_line_original_due := NVL(NVL(l_line_amount,0) + NVL(l_line_tax_amount,0),0);
176 return (l_line_original_due);
177 END;
178 --asawanka modified for bug # 6497335 start
179 FUNCTION INVOICE_LINE_AMOUNT_APPLIED(
180 p_customer_trx_id IN NUMBER,
181 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
182
183 CURSOR tax_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
184 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
185 FROM ar_receivable_applications_all app,
186 ar_payment_schedules_all sch,
187 ar_distributions_all ad,
188 ra_customer_trx_lines_all lines
189 WHERE app.status = 'APP'
190 AND app.applied_payment_schedule_id = sch.payment_schedule_id
191 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
192 AND sch.customer_trx_id = p_header_id
193 AND app.application_type = 'CASH'
194 AND app.receivable_application_id = ad.source_id
195 AND ad.source_table = 'RA'
196 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
197 AND lines.link_to_cust_trx_line_id = p_line_id
198 AND lines.line_type = 'TAX';
199
200 l_line_amount_applied NUMBER;
201 l_tax_amount_applied NUMBER;
202 BEGIN
203
204 --Bug# 7720775
205 l_line_amount_applied := INV_LN_AMT_APPLIED_WOTAX(p_customer_trx_id,
206 p_customer_trx_line_id);
207
208 FOR r in tax_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
209 LOOP
210 l_tax_amount_applied := r.tax_applied;
211 END LOOP;
212 RETURN(nvl(l_line_amount_applied,0) + NVL(l_tax_amount_applied,0));
213 END;
214
215 FUNCTION INVOICE_LINE_AMOUNT_CREDITED(
216 p_customer_trx_id IN NUMBER,
217 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
218
219 CURSOR tax_credited_csr(p_header_id NUMBER, p_line_id NUMBER) IS
220 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
221 FROM ar_receivable_applications_all app,
222 ar_payment_schedules_all sch,
223 ar_distributions_all ad,
224 ra_customer_trx_lines_all lines
225 WHERE app.status = 'APP'
226 AND app.applied_payment_schedule_id = sch.payment_schedule_id
227 AND sch.class = 'INV'
228 AND sch.customer_trx_id = p_header_id
229 AND app.application_type = 'CM'
230 AND app.receivable_application_id = ad.source_id
231 AND ad.source_table = 'RA'
232 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
233 AND lines.link_to_cust_trx_line_id = p_line_id
234 AND lines.line_type = 'TAX';
235
236 l_line_amount_credited NUMBER;
237 l_tax_amount_credited NUMBER;
238 BEGIN
239
240 --Bug# 7720775
241 l_line_amount_credited := INV_LN_AMT_CREDITED_WOTAX(
242 p_customer_trx_id,
243 p_customer_trx_line_id);
244
245 FOR r in tax_credited_csr(p_customer_trx_id, p_customer_trx_line_id)
246 LOOP
247 l_tax_amount_credited := r.credit_applied;
248 END LOOP;
249 RETURN(nvl(l_line_amount_credited,0) + NVL(l_tax_amount_credited,0));
250 END;
251 --asawanka modified for bug # 6497335 end
252 FUNCTION INVOICE_LINE_AMOUNT_REMAINING(
253 p_customer_trx_id IN NUMBER,
254 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
255 l_invoice_line_amount_orig NUMBER;
256 l_invoice_line_amount_applied NUMBER;
257 l_invoice_line_amount_credited NUMBER;
258 l_invoice_line_amt_remaining NUMBER;
259 l_invoice_line_amount_adjusted NUMBER;
260 BEGIN
261 l_invoice_line_amount_orig := INVOICE_LINE_AMOUNT_ORIG(
262 p_customer_trx_id,
263 p_customer_trx_line_id);
264
265 l_invoice_line_amount_applied := INVOICE_LINE_AMOUNT_APPLIED(
266 p_customer_trx_id,
267 p_customer_trx_line_id);
268
269 l_invoice_line_amount_credited := INVOICE_LINE_AMOUNT_CREDITED(
270 p_customer_trx_id,
271 p_customer_trx_line_id);
272
273 l_invoice_line_amount_adjusted := INVOICE_LINE_AMOUNT_ADJUSTED(
274 p_customer_trx_id,
275 p_customer_trx_line_id);
276
277 l_invoice_line_amt_remaining := NVL(l_invoice_line_amount_orig -l_invoice_line_amount_applied - l_invoice_line_amount_credited + l_invoice_line_amount_adjusted, 0);
278
279 RETURN (l_invoice_line_amt_remaining);
280 END;
281
282 FUNCTION INVOICE_AMOUNT_ORIG(
283 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
284 CURSOR invoice_amount_orig_csr(p_header_id NUMBER) IS
285 SELECT SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0)) AMOUNT_DUE_ORIGINAL
286 FROM AR_PAYMENT_SCHEDULES_ALL APS,
287 RA_CUSTOMER_TRX_ALL RACTRX
288 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
289 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
290 l_invoice_amount_orig NUMBER;
291 BEGIN
292 FOR R IN invoice_amount_orig_csr (p_customer_trx_id)
293 LOOP
294 l_invoice_amount_orig := r.AMOUNT_DUE_ORIGINAL;
295 END LOOP;
296 RETURN (l_invoice_amount_orig);
297 END;
298
299 FUNCTION INVOICE_AMOUNT_APPLIED(
300 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
301 --dkagrawa modified the cusrsor to add class CM
302 CURSOR invoice_amount_applied_csr(p_header_id NUMBER) IS
303 SELECT NVL(SUM(app.amount_applied),0) AMOUNT_APPLIED
304 FROM ar_receivable_applications_all app,
305 ar_payment_schedules_all sch
306 WHERE app.status = 'APP'
307 AND app.applied_payment_schedule_id = sch.payment_schedule_id
308 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
309 AND sch.customer_trx_id = p_header_id
310 AND app.application_type = 'CASH';
311 l_invoice_amount_applied NUMBER;
312 BEGIN
313 FOR R IN invoice_amount_applied_csr (p_customer_trx_id)
314 LOOP
315 l_invoice_amount_applied := r.AMOUNT_APPLIED;
316 END LOOP;
317 RETURN (l_invoice_amount_applied);
318 END;
319
320 FUNCTION INVOICE_AMOUNT_CREDITED(
321 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
322 CURSOR invoice_amount_credited_csr(p_header_id NUMBER) IS
323 SELECT NVL(SUM(app.amount_applied),0) AMOUNT_CREDITED
324 FROM ar_receivable_applications_all app,
325 ar_payment_schedules_all sch
326 WHERE app.status = 'APP'
327 AND app.applied_payment_schedule_id = sch.payment_schedule_id
328 AND sch.class = 'INV'
329 AND sch.customer_trx_id = p_header_id
330 AND app.application_type = 'CM';
331 l_invoice_amount_credited NUMBER;
332 BEGIN
333 FOR R IN invoice_amount_credited_csr (p_customer_trx_id)
334 LOOP
335 l_invoice_amount_credited := r.AMOUNT_CREDITED;
336 END LOOP;
337 RETURN (l_invoice_amount_credited);
338 END;
339
340 FUNCTION INVOICE_AMOUNT_REMAINING(
341 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
342 CURSOR invoice_amount_remaining_csr(p_header_id NUMBER) IS
343 SELECT SUM(NVL(APS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING
344 FROM AR_PAYMENT_SCHEDULES_ALL APS,
345 RA_CUSTOMER_TRX_ALL RACTRX
346 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
347 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
348 l_invoice_amount_remaining NUMBER;
349 BEGIN
350 FOR R IN invoice_amount_remaining_csr (p_customer_trx_id)
351 LOOP
352 l_invoice_amount_remaining := r.AMOUNT_DUE_REMAINING;
353 END LOOP;
354 RETURN (l_invoice_amount_remaining);
355 END;
356
357 FUNCTION LINE_ID_APPLIED(p_cash_receipt_id IN NUMBER,
358 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
359 CURSOR invoice_lines(p_cash_receipt_id IN NUMBER,p_customer_trx_id IN NUMBER) IS
360 SELECT RACTRXLN.customer_trx_line_id
361 FROM RA_CUSTOMER_TRX_LINES_ALL RACTRXLN
362 WHERE RACTRXLN.LINE_TYPE = 'LINE'
363 AND RACTRXLN.interface_line_context = 'OKL_CONTRACTS'
364 AND RACTRXLN.CUSTOMER_TRX_ID = p_customer_trx_id;
365 l_inv_ln_id NUMBER := NULL;
366 l_count NUMBER :=0;
367 BEGIN
368 FOR R IN invoice_lines (p_cash_receipt_id,p_customer_trx_id)
369 LOOP
370 l_count := l_count + 1;
371 l_inv_ln_id := R.customer_trx_line_id;
372 END LOOP;
373 IF l_count = 1 THEN
374 RETURN l_inv_ln_id;
375 ELSE
376 RETURN NULL;
377 END IF;
378 END;
379
380 FUNCTION LINE_NUMBER_APPLIED(p_cash_receipt_id IN NUMBER,
381 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
382 CURSOR invoice_lines(p_cash_receipt_id IN NUMBER,p_customer_trx_id IN NUMBER) IS
383 SELECT RACTRXLN.line_number
384 FROM RA_CUSTOMER_TRX_LINES_ALL RACTRXLN
385 WHERE RACTRXLN.LINE_TYPE = 'LINE'
386 AND RACTRXLN.interface_line_context = 'OKL_CONTRACTS'
387 AND RACTRXLN.CUSTOMER_TRX_ID = p_customer_trx_id;
388 l_inv_ln_num NUMBER := NULL;
389 l_count NUMBER :=0;
390 BEGIN
391 FOR R IN invoice_lines (p_cash_receipt_id,p_customer_trx_id)
392 LOOP
393 l_count := l_count + 1;
394 l_inv_ln_num := R.line_number;
395 END LOOP;
396 IF l_count = 1 THEN
397 RETURN l_inv_ln_num;
398 ELSE
399 RETURN NULL;
400 END IF;
401 END;
402
403 /*FUNCTION DEBUG_PROC(msg varchar2) RETURN VARCHAR2 as
404 PRAGMA AUTONOMOUS_TRANSACTION ;
405 l_seq_num number;
406 BEGIN
407 SELECT new_seq.nextval into l_seq_num from dual;
408 INSERT INTO DEBUG_TABLE_k VALUES(l_seq_num,SYSDATE, msg);
409 commit;
410 RETURN 'X';
411 END DEBUG_PROC;*/
412
413 FUNCTION get_tld_amount_orig( p_tld_id IN NUMBER ) RETURN NUMBER IS
414 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
415 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
416 FROM RA_CUSTOMER_TRX_LINES_ALL
417 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
418 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
419
420 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
421 SELECT COUNT(1) LINE_COUNT
422 FROM RA_CUSTOMER_TRX_LINES_ALL
423 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
424 AND LINE_TYPE = 'LINE';
425
426 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
427 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
428 l_line_count NUMBER;
429 l_amount_orig NUMBER := 0;
430 BEGIN
431 NULL;
432 FOR r IN cust_trx_csr(p_tld_id)
433 LOOP
434 l_customer_trx_id := r.CUSTOMER_TRX_ID;
435 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
436 END LOOP;
437
438 FOR r IN line_count_csr(l_customer_trx_id)
439 LOOP
440 l_line_count := r.line_count;
441 END LOOP;
442
443 IF (l_line_count > 1) THEN
444 -- New style (R12B) invoice
445 l_amount_orig := invoice_line_amount_orig(
446 l_customer_trx_id, l_customer_trx_line_id);
447 ELSIF (l_line_count = 1) THEN
448 -- Old style invoice
449 l_amount_orig := invoice_amount_orig(l_customer_trx_id);
450 END IF;
451
452 RETURN (l_amount_orig);
453
454 END;
455
456 FUNCTION get_tld_amount_applied( p_tld_id IN NUMBER ) RETURN NUMBER IS
457 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
458 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
459 FROM RA_CUSTOMER_TRX_LINES_ALL
460 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
461 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
462
463 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
464 SELECT COUNT(1) LINE_COUNT
465 FROM RA_CUSTOMER_TRX_LINES_ALL
466 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
467 AND LINE_TYPE = 'LINE';
468
469 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
470 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
471 l_line_count NUMBER;
472 l_amount_applied NUMBER := 0;
473 BEGIN
474 NULL;
475 FOR r IN cust_trx_csr(p_tld_id)
476 LOOP
477 l_customer_trx_id := r.CUSTOMER_TRX_ID;
478 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
479 END LOOP;
480
481 FOR r IN line_count_csr(l_customer_trx_id)
482 LOOP
483 l_line_count := r.line_count;
484 END LOOP;
485
486 IF (l_line_count > 1) THEN
487 -- New style (R12B) invoice
488 l_amount_applied := invoice_line_amount_applied(
489 l_customer_trx_id, l_customer_trx_line_id);
490 ELSIF (l_line_count = 1) THEN
491 -- Old style invoice
492 l_amount_applied := invoice_amount_applied(l_customer_trx_id);
493 END IF;
494
495 RETURN (l_amount_applied);
496
497 END;
498
499 FUNCTION get_tld_amount_credited( p_tld_id IN NUMBER ) RETURN NUMBER IS
500 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
501 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
502 FROM RA_CUSTOMER_TRX_LINES_ALL
503 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
504 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
505
506 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
507 SELECT COUNT(1) LINE_COUNT
508 FROM RA_CUSTOMER_TRX_LINES_ALL
509 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
510 AND LINE_TYPE = 'LINE';
511
512 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
513 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
514 l_line_count NUMBER;
515 l_amount_credited NUMBER := 0;
516 BEGIN
517 NULL;
518 FOR r IN cust_trx_csr(p_tld_id)
519 LOOP
520 l_customer_trx_id := r.CUSTOMER_TRX_ID;
521 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
522 END LOOP;
523
524 FOR r IN line_count_csr(l_customer_trx_id)
525 LOOP
526 l_line_count := r.line_count;
527 END LOOP;
528
529 IF (l_line_count > 1) THEN
530 -- New style (R12B) invoice
531 l_amount_credited := invoice_line_amount_credited(
532 l_customer_trx_id, l_customer_trx_line_id);
533 ELSIF (l_line_count = 1) THEN
534 -- Old style invoice
535 l_amount_credited := invoice_amount_credited(l_customer_trx_id);
536 END IF;
537
538 RETURN (l_amount_credited);
539
540 END;
541
542 FUNCTION get_tld_amount_remaining( p_tld_id IN NUMBER ) RETURN NUMBER IS
543 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
544 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
545 FROM RA_CUSTOMER_TRX_LINES_ALL
546 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
547 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
548
549 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
550 SELECT COUNT(1) LINE_COUNT
551 FROM RA_CUSTOMER_TRX_LINES_ALL
552 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
553 AND LINE_TYPE = 'LINE';
554
555 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
556 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
557 l_line_count NUMBER;
558 l_amount_remaining NUMBER := 0;
559 BEGIN
560 NULL;
561 FOR r IN cust_trx_csr(p_tld_id)
562 LOOP
563 l_customer_trx_id := r.CUSTOMER_TRX_ID;
564 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
565 END LOOP;
566
567 FOR r IN line_count_csr(l_customer_trx_id)
568 LOOP
569 l_line_count := r.line_count;
570 END LOOP;
571
572 IF (l_line_count > 1) THEN
573 -- New style (R12B) invoice
574 l_amount_remaining := invoice_line_amount_remaining(
575 l_customer_trx_id, l_customer_trx_line_id);
576 ELSIF (l_line_count = 1) THEN
577 -- Old style invoice
578 l_amount_remaining := invoice_amount_remaining(l_customer_trx_id);
579 END IF;
580
581 RETURN (l_amount_remaining);
582
583 END;
584
585 PROCEDURE get_tld_balance(
586 p_api_version IN NUMBER
587 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
588 ,p_tld_id IN NUMBER
589 ,x_return_status OUT NOCOPY VARCHAR2
590 ,x_msg_count OUT NOCOPY NUMBER
591 ,x_msg_data OUT NOCOPY VARCHAR2
592 ,x_orig_amount OUT NOCOPY NUMBER
593 ,x_applied_amount OUT NOCOPY NUMBER
594 ,x_credited_amount OUT NOCOPY NUMBER
595 ,x_remaining_amount OUT NOCOPY NUMBER
596 ,x_tax_amount OUT NOCOPY NUMBER
597 )
598 IS
599
600 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
601 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
602 FROM RA_CUSTOMER_TRX_LINES_ALL
603 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
604 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
605
606 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
607 SELECT COUNT(1) LINE_COUNT
608 FROM RA_CUSTOMER_TRX_LINES_ALL
609 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
610 AND LINE_TYPE = 'LINE';
611
612 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
613 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
614 FROM RA_CUSTOMER_TRX_LINES_ALL
615 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id
616 AND LINE_TYPE='TAX';
617
618 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
619 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
620 l_line_count NUMBER;
621 l_amount_remaining NUMBER := 0;
622
623 l_api_name VARCHAR2(60) := 'Get TLD Balance';
624
625 BEGIN
626
627 x_orig_amount := 0.0;
628 x_applied_amount := 0;
629 x_credited_amount := 0;
630 x_remaining_amount := 0;
631 x_tax_amount := 0;
632
633 FOR r IN cust_trx_csr(p_tld_id)
634 LOOP
635 l_customer_trx_id := r.CUSTOMER_TRX_ID;
636 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
637 END LOOP;
638
639 FOR r IN line_count_csr(l_customer_trx_id)
640 LOOP
641 l_line_count := r.line_count;
642 END LOOP;
643
644 FOR r IN invoice_line_tax_amount(l_customer_trx_line_id)
645 LOOP
646 x_tax_amount := r.LINE_TAX_AMOUNT;
647 END LOOP;
648
649 IF (l_line_count > 1) THEN
650 -- New style (R12B) invoice
651 x_orig_amount := invoice_line_amount_orig(
652 l_customer_trx_id, l_customer_trx_line_id);
653 x_applied_amount := invoice_line_amount_applied(
654 l_customer_trx_id, l_customer_trx_line_id);
655 x_credited_amount := invoice_line_amount_credited(
656 l_customer_trx_id, l_customer_trx_line_id);
657 x_remaining_amount := invoice_line_amount_remaining(
658 l_customer_trx_id, l_customer_trx_line_id);
659 ELSIF (l_line_count = 1) THEN
660 -- Old style invoice
661 x_orig_amount := invoice_amount_orig(l_customer_trx_id);
662 x_applied_amount := invoice_amount_applied(l_customer_trx_id);
663 x_credited_amount := invoice_amount_credited(l_customer_trx_id);
664 x_remaining_amount := invoice_amount_remaining(l_customer_trx_id);
665 END IF;
666
667 x_return_status := 'S';
668
669 EXCEPTION
670 WHEN OTHERS THEN
671
672 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
673 p_api_name => l_api_name,
674 p_pkg_name => G_PKG_NAME,
675 p_exc_name => 'OTHERS',
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 p_api_type => '_PVT');
679
680 END get_tld_balance;
681
682
683
684 PROCEDURE get_contract_invoice_balance(
685 p_api_version IN NUMBER
686 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
687 ,p_contract_number IN VARCHAR2
688 ,p_trx_number IN VARCHAR2
689 ,x_return_status OUT NOCOPY VARCHAR2
690 ,x_msg_count OUT NOCOPY NUMBER
691 ,x_msg_data OUT NOCOPY VARCHAR2
692 ,x_remaining_amount OUT NOCOPY NUMBER
693 ) IS
694
695
696 CURSOR cust_trx_csr(p_cust_trx_number VARCHAR2, p_contract_number VARCHAR2) IS
697 SELECT LNS.CUSTOMER_TRX_ID
698 FROM RA_CUSTOMER_TRX_LINES_ALL LNS,
699 RA_CUSTOMER_TRX_ALL HDR
700 WHERE LNS.INTERFACE_LINE_ATTRIBUTE6 = p_contract_number
701 AND HDR.TRX_NUMBER = p_cust_trx_number
702 AND HDR.CUSTOMER_TRX_ID = LNS.CUSTOMER_TRX_ID;
703
704
705 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
706 l_amount_remaining NUMBER := 0;
707
708 l_api_name VARCHAR2(60) := 'get_contract_invoice_balance';
709
710 BEGIN
711
712 x_remaining_amount := 0;
713
714 open cust_trx_csr(p_trx_number,p_contract_number);
715 fetch cust_trx_csr into l_customer_trx_id;
716 close cust_trx_csr;
717
718 x_remaining_amount := invoice_amount_remaining(l_customer_trx_id);
719 x_return_status := 'S';
720
721 EXCEPTION
722 WHEN OTHERS THEN
723
724 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
725 p_api_name => l_api_name,
726 p_pkg_name => G_PKG_NAME,
727 p_exc_name => 'OTHERS',
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data,
730 p_api_type => '_PVT');
731
732 END get_contract_invoice_balance;
733
734 FUNCTION INVOICE_LINE_TAX_AMOUNT(p_customer_trx_line_id NUMBER) RETURN NUMBER IS
735 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
736 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
737 FROM RA_CUSTOMER_TRX_LINES_ALL
738 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id
739 AND LINE_TYPE='TAX';
740
741 l_tax_amount NUMBER;
742 BEGIN
743
744 l_tax_amount := 0;
745
746 FOR r IN invoice_line_tax_amount(p_customer_trx_line_id)
747 LOOP
748 l_tax_amount := r.LINE_TAX_AMOUNT;
749 END LOOP;
750
751 RETURN(l_tax_amount);
752
753 END;
754
755
756 /** VPANWAR 05-Sep-2007 Procedure to list In Process Billing
757 Transactions pre-upgrade script **/
758 PROCEDURE CHECK_PREUPGRADE_DATA(x_errbuf OUT NOCOPY VARCHAR2,
759 x_retcode OUT NOCOPY NUMBER,
760 x_any_data_exists OUT NOCOPY BOOLEAN )
761 IS
762 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_PREUPGRADE_DATA';
763 l_msg_count NUMBER;
764 l_msg_data VARCHAR2(2000);
765 l_return_status VARCHAR2(1);
766 l_api_version NUMBER;
767 l_init_msg_list VARCHAR2(1);
768 l_total_length CONSTANT NUMBER DEFAULT 152;
769 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_BILLING_UTIL_PVT.CHECK_PREUPGRADE_DATA';
770 l_debug_enabled VARCHAR2(10);
771 is_debug_procedure_on BOOLEAN;
772 is_debug_statement_on BOOLEAN;
773 l_contract VARCHAR2(120);
774
775 -- This cursor is used for the following purpose
776 -- For every record in okl_trx_ar_invoices_b, okl_txl_ar_inv_lns_b, okl_txd_ar_ln_dtls_b
777 -- check if corresponding records exist in okl_ext_sell_invs_b, okl_xtl_sell_invs_b table.
778 -- We check both two level and three level transactions with the external tables.
779 CURSOR int_ext_check_csr IS
780 SELECT OKC_K_HEADERS_B.CONTRACT_NUMBER
781 FROM OKL_TXL_AR_INV_LNS_B
782 , OKL_TXD_AR_LN_DTLS_B
783 , OKL_TRX_AR_INVOICES_B
784 , OKL_K_HEADERS
785 , OKC_K_HEADERS_B
786 WHERE OKL_TXD_AR_LN_DTLS_B.TIL_ID_DETAILS = OKL_TXL_AR_INV_LNS_B.ID
787 AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
788 AND OKL_K_HEADERS.ID = OKL_TRX_AR_INVOICES_B.KHR_ID
789 AND OKL_TXL_AR_INV_LNS_B.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
790 AND OKL_TXD_AR_LN_DTLS_B.ID NOT IN
791 ( SELECT OKL_XTL_SELL_INVS_B.TLD_ID
792 FROM OKL_XTL_SELL_INVS_B
793 , OKL_EXT_SELL_INVS_B
794 WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
795 AND OKL_XTL_SELL_INVS_B.TLD_ID IS NOT NULL)
796 UNION
797 SELECT OKC_K_HEADERS_B.CONTRACT_NUMBER
798 FROM OKL_TXL_AR_INV_LNS_B TIL
799 , OKL_TRX_AR_INVOICES_B
800 , OKL_K_HEADERS
801 , OKC_K_HEADERS_B
802 WHERE OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
803 AND OKL_K_HEADERS.ID = OKL_TRX_AR_INVOICES_B.KHR_ID
804 AND TIL.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
805 AND NOT EXISTS (
806 SELECT 1 FROM OKL_TXD_AR_LN_DTLS_B TXD
807 WHERE TXD.TIL_ID_DETAILS = TIL.ID
808 )
809 AND TIL.ID NOT IN
810 (SELECT OKL_XTL_SELL_INVS_B.TIL_ID
811 FROM OKL_XTL_SELL_INVS_B
812 , OKL_EXT_SELL_INVS_B
813 WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
814 AND OKL_XTL_SELL_INVS_B.TIL_ID IS NOT NULL);
815
816 -- This cursor is used for the following purpose
817 -- For every record in okl_ext_sell_invs_b, okl_xtl_sell_invs_b check if
818 -- corresponding records exist in okl_cnsld_ar_hdrs_b, okl_cnsld_ar_lines_b,
819 -- okl_cnsld_ar_Strms_b tables.
820
821 CURSOR ext_cnsld_check_csr IS
822 SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
823 FROM OKL_XTL_SELL_INVS_B
824 ,OKL_EXT_SELL_INVS_B
825 ,OKL_XTL_SELL_INVS_TL
826 ,OKL_TRX_AR_INVOICES_B
827 ,OKL_TXL_AR_INV_LNS_B
828 ,OKL_TXD_AR_LN_DTLS_B
829 ,OKC_K_HEADERS_B
830 WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
831 AND OKL_XTL_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_TL.ID
832 AND OKL_XTL_SELL_INVS_B.LSM_ID IS NULL
833 AND (OKL_TXL_AR_INV_LNS_B.ID = OKL_XTL_SELL_INVS_B.TIL_ID OR OKL_XTL_SELL_INVS_B.TLD_ID = OKL_TXD_AR_LN_DTLS_B.ID)
834 AND OKL_TXD_AR_LN_DTLS_B.TIL_ID_DETAILS = OKL_TXL_AR_INV_LNS_B.ID
835 AND OKL_TXL_AR_INV_LNS_B.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
836 AND OKL_TRX_AR_INVOICES_B.KHR_ID = OKC_K_HEADERS_B.id;
837
838 -- This cursor is used for the following purpose
839 -- For every record in okl_cnsld_hdrs_b, okl_cnsld_ar_lines_b, okl_cnsld_ar_strms_b check
840 -- if corresponding records exist in AR.
841 CURSOR cnsld_ar_check_csr IS
842 SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
843 FROM OKL_CNSLD_AR_STRMS_B
844 , OKL_CNSLD_AR_LINES_B
845 , OKL_CNSLD_AR_HDRS_B
846 , OKL_K_HEADERS
847 , OKC_K_HEADERS_B
848 WHERE OKL_CNSLD_AR_LINES_B.CNR_ID = OKL_CNSLD_AR_HDRS_B.ID
849 AND OKL_CNSLD_AR_STRMS_B.LLN_ID = OKL_CNSLD_AR_LINES_B.ID
850 AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
851 AND OKL_K_HEADERS.ID = OKL_CNSLD_AR_STRMS_B.KHR_ID
852 AND OKL_CNSLD_AR_STRMS_B.KHR_ID = OKL_CNSLD_AR_LINES_B.KHR_ID
853 AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID IS NULL;
854
855 -- This cursor is used for the following purpose
856 -- For every record in okl_cnsld_ar_strms_b check if receivable_invoice_id is a positive number.
857 CURSOR cnsld_pstv_check_csr IS
858 SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
859 FROM OKL_CNSLD_AR_STRMS_B
860 , OKL_CNSLD_AR_LINES_B
861 , OKL_CNSLD_AR_HDRS_B
862 , OKL_K_HEADERS
863 , OKC_K_HEADERS_B
864 WHERE OKL_CNSLD_AR_LINES_B.CNR_ID = OKL_CNSLD_AR_HDRS_B.ID
865 AND OKL_CNSLD_AR_STRMS_B.LLN_ID = OKL_CNSLD_AR_LINES_B.ID
866 AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
867 AND OKL_K_HEADERS.ID = OKL_CNSLD_AR_STRMS_B.KHR_ID
868 AND OKL_CNSLD_AR_STRMS_B.KHR_ID = OKL_CNSLD_AR_LINES_B.KHR_ID
869 AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID < 0
870 AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID IS NOT NULL;
871
872 BEGIN
873
874 x_any_data_exists := false; -- VPANWAR changed for pre upgrade test
875
876 /* l_debug_enabled := okl_debug_pub.check_log_enabled;
877 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
878 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
879 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBULB.pls call CHECK_PREUPGRADE_DATA');
880 END IF;*/
881
882 x_retcode := 0;
883 x_errbuf := null;
884 x_any_data_exists := FALSE;
885
886 l_api_version := 1.0;
887 l_init_msg_list := Okl_Api.G_TRUE;
888 l_msg_count := 0;
889
890 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
891 G_PKG_NAME,
892 l_init_msg_list,
893 l_api_version,
894 l_api_version,
895 '_PVT',
896 l_return_status);
897 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
899 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
900 RAISE OKL_API.G_EXCEPTION_ERROR;
901 END IF;
902
903 -- Printing In Process Billing Transactions report header. The formatting of the message titles is
904 -- also taken care here.
905 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKLHOMENAVTITLE') ||
906 RPAD(' ', 53 , ' ' ));
907 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
908 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
909 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKL_IN_PRCS_BILL_TRANS_REP') ||
910 RPAD(' ', 53 , ' ' ));
911 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || '-------------------------------' || RPAD(' ', 51, ' ' ));
912 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
913 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
914 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));*/
915
916 -- Printing the titles for 'Contract Number' and 'Next Step'
917 /* FND_FILE.PUT(FND_FILE.OUTPUT, RPAD('Contract Number Next Step',150,' '));
918 FND_FILE.PUT(FND_FILE.OUTPUT, RPAD('------------------ --------------',150,' ')); */
919
920 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract Number Next Step');
921 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------------ --------------'); */
922
923 -- Initialize the contract variable to Null.
924 -- Open the cursor Int_Ext_Check_Csr, Loop through it and display the contract number
925 -- and the Next step value should be 'Run Prepare Receivables Bills concurrent program'.
926 l_contract := NULL;
927 OPEN int_ext_check_csr;
928 -- Fetch from the cursor the first time and check if any row is found.
929 -- If found then print the contract number , the next step and loop through
930 -- the cursor. If not found then display the No records found message.
931 FETCH int_ext_check_csr INTO l_contract;
932 IF int_ext_check_csr%FOUND
933 THEN
934 LOOP
935 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
936 x_retcode := 1;
937 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
938 RPAD(' Run Prepare Receivables Bills concurrent program',120,' '));
939 FETCH int_ext_check_csr INTO l_contract;
940 EXIT WHEN int_ext_check_csr%NOTFOUND;*/
941 x_errbuf := 'Prepare Receivables Bills concurrent program';
942 EXIT;
943 END LOOP;
944 /* ELSE
945 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
946 END IF;
947 CLOSE int_ext_check_csr;
948
949 -- Insert blank lines
950 /*FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
951 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
952 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));*/
953
954 -- Initialize the contract variable to Null.
955 -- Open the cursor ext_cnsld_check_csr, Loop through it and display the contract number
956 -- and the Next step value should be 'Run Receivables Bills Consolidation concurrent program'.
957 l_contract := NULL;
958 OPEN ext_cnsld_check_csr;
959 -- Fetch from the cursor the first time and check if any row is found.
960 -- If found then print the contract number , the next step and loop through
961 -- the cursor. If not found then display the No records found message.
962 FETCH ext_cnsld_check_csr INTO l_contract;
963 IF ext_cnsld_check_csr%FOUND
964 THEN
965 LOOP
966 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
967 x_retcode := 1;
968 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
969 RPAD(' Run Receivables Bills Consolidation concurrent program',120,' '));
970 FETCH ext_cnsld_check_csr INTO l_contract;
971 EXIT WHEN ext_cnsld_check_csr%NOTFOUND;*/
972 x_errbuf := 'Receivables Bills Consolidation concurrent program';
973 EXIT;
974 END LOOP;
975 /*ELSE
976 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
977 END IF;
978 CLOSE ext_cnsld_check_csr;
979
980 -- Insert blank lines
981 /*FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
982 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
983 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));*/
984
985 -- Initialize the contract variable to Null.
986 -- Open the cursor cnsld_ar_check_csr, Loop through it and display the contract number
987 -- and the Next step value should be 'Run Prepare Receivables Bills concurrent program'.
988 l_contract := NULL;
989 OPEN cnsld_ar_check_csr;
990 -- Fetch from the cursor the first time and check if any row is found.
991 -- If found then print the contract number , the next step and loop through
992 -- the cursor. If not found then display the No records found message.
993 FETCH cnsld_ar_check_csr INTO l_contract;
994 IF cnsld_ar_check_csr%FOUND
995 THEN
996 LOOP
997 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
998 x_retcode := 1;
999 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
1000 RPAD(' Run Receivables Invoice Transfer to AR concurrent program',120,' '));
1001 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',30,' ')||
1002 RPAD(' Run AutoInvoice Master Program',120,' '));
1003 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',30,' ')||
1004 RPAD(' Run Fetch AR Invoice Numbers',120,' '));
1005 FETCH cnsld_ar_check_csr INTO l_contract;
1006 EXIT WHEN cnsld_ar_check_csr%NOTFOUND;*/
1007 x_errbuf := 'AutoInvoice Master Program and Fetch AR Invoice Numbers';
1008 EXIT;
1009 END LOOP;
1010 /*ELSE
1011 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
1012 END IF;
1013 CLOSE cnsld_ar_check_csr;
1014
1015 -- Insert blank lines
1016 /*FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1017 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1018 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));*/
1019
1020 -- Initialize the contract variable to Null.
1021 -- Open the cursor cnsld_pstv_check_csr, Loop through it and display the contract number
1022 -- and the Next step value should be 'Run Prepare Receivables Bills concurrent program'.
1023 l_contract := NULL;
1024 OPEN cnsld_pstv_check_csr;
1025 -- Fetch from the cursor the first time and check if any row is found.
1026 -- If found then print the contract number , the next step and loop through
1027 -- the cursor. If not found then display the No records found message.
1028 FETCH cnsld_pstv_check_csr INTO l_contract;
1029 IF cnsld_pstv_check_csr%FOUND
1030 THEN
1031 LOOP
1032 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1033 x_retcode := 1;
1034 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
1035 RPAD(' Run Fetch AR Invoice Numbers',120,' '));
1036 FETCH cnsld_pstv_check_csr INTO l_contract;
1037 EXIT WHEN cnsld_pstv_check_csr%NOTFOUND;*/
1038 x_errbuf := 'Fetch AR Invoice Numbers';
1039 EXIT;
1040 END LOOP;
1041 /*ELSE
1042 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
1043 END IF;
1044 CLOSE cnsld_pstv_check_csr;
1045
1046 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
1047 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1048 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRUPGB.pls call IN_PROCESS_BILLING_TXN');
1049 END IF;
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 IF int_ext_check_csr%ISOPEN
1054 THEN
1055 CLOSE int_ext_check_csr;
1056 END IF;
1057 IF ext_cnsld_check_csr%ISOPEN
1058 THEN
1059 CLOSE ext_cnsld_check_csr;
1060 END IF;
1061 IF cnsld_ar_check_csr%ISOPEN
1062 THEN
1063 CLOSE cnsld_ar_check_csr;
1064 END IF;
1065 IF cnsld_pstv_check_csr%ISOPEN
1066 THEN
1067 CLOSE cnsld_pstv_check_csr;
1068 END IF;
1069 x_errbuf := SQLERRM;
1070 x_retcode := 2;
1071 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1072
1073 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
1074
1075 IF (SQLCODE <> -20001) THEN
1076 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
1077 RAISE;
1078 ELSE
1079 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
1080 APP_EXCEPTION.RAISE_EXCEPTION;
1081 END IF;
1082 END check_preupgrade_data;
1083
1084 -- bug bug 6328168 get the amounts without Tax
1085
1086 FUNCTION get_tld_amt_remaining_WOTAX( p_tld_id IN NUMBER ) RETURN NUMBER IS
1087 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
1088 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
1089 FROM RA_CUSTOMER_TRX_LINES_ALL
1090 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
1091 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
1092
1093 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
1094 SELECT COUNT(1) LINE_COUNT
1095 FROM RA_CUSTOMER_TRX_LINES_ALL
1096 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
1097 AND LINE_TYPE = 'LINE';
1098
1099 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
1100 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
1101 l_line_count NUMBER;
1102 l_amount_remaining NUMBER := 0;
1103 BEGIN
1104 NULL;
1105 FOR r IN cust_trx_csr(p_tld_id)
1106 LOOP
1107 l_customer_trx_id := r.CUSTOMER_TRX_ID;
1108 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
1109 END LOOP;
1110
1111 FOR r IN line_count_csr(l_customer_trx_id)
1112 LOOP
1113 l_line_count := r.line_count;
1114 END LOOP;
1115
1116 IF (l_line_count > 1) THEN
1117 -- New style (R12B) invoice
1118 l_amount_remaining := INV_LN_AMT_REMAINING_WOTAX(
1119 l_customer_trx_id, l_customer_trx_line_id);
1120 ELSIF (l_line_count = 1) THEN
1121 -- Old style invoice
1122 l_amount_remaining := INV_AMT_REMAINING_WOTAX(l_customer_trx_id);
1123 END IF;
1124
1125 RETURN (l_amount_remaining);
1126
1127 END;
1128
1129 FUNCTION INV_LN_AMT_REMAINING_WOTAX(
1130 p_customer_trx_id IN NUMBER,
1131 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1132 l_invoice_line_amount_orig NUMBER;
1133 l_invoice_line_amount_applied NUMBER;
1134 l_invoice_line_amount_credited NUMBER;
1135 l_invoice_line_amt_remaining NUMBER;
1136 --Bug# 7720775
1137 l_invoice_line_amount_adjusted NUMBER;
1138 BEGIN
1139 l_invoice_line_amount_orig := INV_LN_AMT_ORIG_WOTAX(
1140 p_customer_trx_id,
1141 p_customer_trx_line_id);
1142
1143 l_invoice_line_amount_applied := INV_LN_AMT_APPLIED_WOTAX(
1144 p_customer_trx_id,
1145 p_customer_trx_line_id);
1146
1147 l_invoice_line_amount_credited := INV_LN_AMT_CREDITED_WOTAX(
1148 p_customer_trx_id,
1149 p_customer_trx_line_id);
1150
1151 --Bug# 7720775
1152 l_invoice_line_amount_adjusted := INV_LN_AMT_ADJUSTED_WOTAX(
1153 p_customer_trx_id,
1154 p_customer_trx_line_id);
1155
1156 l_invoice_line_amt_remaining := NVL(l_invoice_line_amount_orig -l_invoice_line_amount_applied - l_invoice_line_amount_credited + l_invoice_line_amount_adjusted, 0);
1157
1158 RETURN (l_invoice_line_amt_remaining);
1159 END;
1160
1161 FUNCTION INV_AMT_REMAINING_WOTAX(
1162 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
1163 CURSOR invoice_amount_remaining_csr(p_header_id NUMBER) IS
1164 SELECT SUM(NVL(APS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING
1165 FROM AR_PAYMENT_SCHEDULES_ALL APS,
1166 RA_CUSTOMER_TRX_ALL RACTRX
1167 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
1168 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
1169 l_invoice_amount_remaining NUMBER;
1170 BEGIN
1171 FOR R IN invoice_amount_remaining_csr (p_customer_trx_id)
1172 LOOP
1173 l_invoice_amount_remaining := r.AMOUNT_DUE_REMAINING;
1174 END LOOP;
1175 RETURN (l_invoice_amount_remaining);
1176 END;
1177
1178
1179 FUNCTION INV_LN_AMT_ORIG_WOTAX(
1180 p_customer_trx_id IN NUMBER,
1181 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1182 CURSOR invoice_line_amount(p_header_id NUMBER, p_line_id NUMBER) IS
1183 SELECT NVL(EXTENDED_AMOUNT, 0) LINE_AMOUNT
1184 FROM RA_CUSTOMER_TRX_LINES_ALL
1185 WHERE CUSTOMER_TRX_ID = p_header_id
1186 AND CUSTOMER_TRX_LINE_ID = p_line_id;
1187
1188 l_line_amount NUMBER;
1189 l_line_original_due NUMBER;
1190
1191 BEGIN
1192
1193 FOR r IN invoice_line_amount(p_customer_trx_id, p_customer_trx_line_id)
1194 LOOP
1195 l_line_amount := r.LINE_AMOUNT;
1196 END LOOP;
1197
1198 l_line_original_due := NVL(l_line_amount,0);
1199 return (l_line_original_due);
1200 END;
1201
1202
1203 FUNCTION INV_LN_AMT_CREDITED_WOTAX(
1204 p_customer_trx_id IN NUMBER,
1205 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1206 CURSOR line_credited_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1207 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
1208 FROM ar_receivable_applications_all app,
1209 ar_payment_schedules_all sch,
1210 ar_distributions_all ad
1211 WHERE app.status = 'APP'
1212 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1213 AND sch.class = 'INV'
1214 AND sch.customer_trx_id = p_header_id
1215 AND app.application_type = 'CM'
1216 AND app.receivable_application_id = ad.source_id
1217 AND ad.source_table = 'RA'
1218 AND ad.ref_Customer_trx_Line_Id = p_line_id;
1219
1220 l_line_amount_credited NUMBER;
1221 BEGIN
1222 FOR r in line_credited_csr(p_customer_trx_id, p_customer_trx_line_id)
1223 LOOP
1224 l_line_amount_credited := r.credit_applied;
1225 END LOOP;
1226 RETURN(nvl(l_line_amount_credited,0));
1227 END;
1228
1229 FUNCTION INV_LN_AMT_APPLIED_WOTAX(
1230 p_customer_trx_id IN NUMBER,
1231 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1232 --dkagrawa modified the cusrsor to add class CM
1233 CURSOR line_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1234 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) line_applied
1235 FROM ar_receivable_applications_all app,
1236 ar_payment_schedules_all sch,
1237 ar_distributions_all ad
1238 WHERE app.status = 'APP'
1239 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1240 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
1241 AND sch.customer_trx_id = p_header_id
1242 AND app.application_type = 'CASH'
1243 AND app.receivable_application_id = ad.source_id
1244 AND ad.source_table = 'RA'
1245 AND ad.ref_Customer_trx_Line_Id = p_line_id;
1246 --dkagrawa modified the cusrsor to add class CM
1247
1248 l_line_amount_applied NUMBER;
1249 BEGIN
1250 FOR r in line_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
1251 LOOP
1252 l_line_amount_applied := r.line_applied;
1253 END LOOP;
1254 RETURN(nvl(l_line_amount_applied,0));
1255 END;
1256
1257 FUNCTION INVOICE_AMOUNT_ADJUSTED(
1258 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
1259 CURSOR invoice_amount_adj_csr(p_header_id NUMBER) IS
1260 SELECT SUM(NVL(APS.AMOUNT_ADJUSTED,0)) AMOUNT_ADJUSTED
1261 FROM AR_PAYMENT_SCHEDULES_ALL APS,
1262 RA_CUSTOMER_TRX_ALL RACTRX
1263 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
1264 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
1265 l_invoice_amount_adj NUMBER;
1266 BEGIN
1267 FOR R IN invoice_amount_adj_csr (p_customer_trx_id)
1268 LOOP
1269 l_invoice_amount_adj := r.AMOUNT_ADJUSTED;
1270 END LOOP;
1271 RETURN (l_invoice_amount_adj);
1272 END INVOICE_AMOUNT_ADJUSTED;
1273
1274 FUNCTION INVOICE_LINE_AMOUNT_ADJUSTED(
1275 p_customer_trx_id IN NUMBER,
1276 p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
1277
1278 CURSOR invoice_ln_tax_adj(p_header_id NUMBER, p_line_id NUMBER) IS
1279 SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) tax_adjusted
1280 FROM ar_adjustments_all adj,
1281 ar_payment_schedules_all sch,
1282 ar_distributions_all dist,
1283 ra_customer_trx_lines_all lines
1284 WHERE adj.payment_schedule_id = sch.payment_schedule_id
1285 AND sch.class = 'INV'
1286 AND sch.customer_trx_id = p_header_id
1287 AND adj.ADJUSTMENT_ID = dist.source_id
1288 AND dist.source_table = 'ADJ'
1289 AND dist.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1290 AND lines.link_to_cust_trx_line_id = p_line_id
1291 AND lines.line_type = 'TAX';
1292
1293 l_invoice_ln_amount_adj NUMBER;
1294 l_invoice_ln_tax_adj NUMBER;
1295 BEGIN
1296
1297 --Bug# 7720775
1298 l_invoice_ln_amount_adj := INV_LN_AMT_ADJUSTED_WOTAX(
1299 p_customer_trx_id,
1300 p_customer_trx_line_id);
1301
1302 FOR R IN invoice_ln_tax_adj (p_customer_trx_id,p_customer_trx_line_id)
1303 LOOP
1304 l_invoice_ln_tax_adj := r.tax_adjusted;
1305 END LOOP;
1306
1307 RETURN (NVL(l_invoice_ln_amount_adj,0)+NVL(l_invoice_ln_tax_adj,0));
1308
1309 END INVOICE_LINE_AMOUNT_ADJUSTED;
1310
1311 --Bug# 7720775
1312 FUNCTION INV_LN_AMT_ADJUSTED_WOTAX(
1313 p_customer_trx_id IN NUMBER,
1314 p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
1315
1316 CURSOR invoice_ln_amount_adj(p_header_id NUMBER, p_line_id NUMBER) IS
1317 SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) amt_adjsuted
1318 FROM ar_distributions_all dist ,
1319 ar_adjustments_all adj ,
1320 ar_payment_schedules_all aps
1321 WHERE dist.source_table = 'ADJ'
1322 AND dist.source_id = adj.adjustment_id
1323 AND aps.customer_trx_id = p_header_id
1324 AND adj.payment_schedule_id = aps.payment_schedule_id
1325 AND aps.class = 'INV'
1326 AND ref_customer_trx_line_id = p_line_id;
1327
1328 l_invoice_ln_amount_adj NUMBER;
1329 BEGIN
1330 FOR R IN invoice_ln_amount_adj (p_customer_trx_id,p_customer_trx_line_id)
1331 LOOP
1332 l_invoice_ln_amount_adj := r.amt_adjsuted;
1333 END LOOP;
1334
1335 RETURN NVL(l_invoice_ln_amount_adj,0);
1336
1337 END INV_LN_AMT_ADJUSTED_WOTAX;
1338
1339 --Bug# 7720775
1340 -- Functions to return Invoice Line Amount with Inclusive Invoice Tax Line Amounts
1341 FUNCTION INV_LN_AMT_ORIG_W_INCTAX(
1342 p_customer_trx_id IN NUMBER,
1343 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1344
1345 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
1346 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
1347 FROM RA_CUSTOMER_TRX_LINES_ALL
1348 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id
1349 AND AMOUNT_INCLUDES_TAX_FLAG = 'Y';
1350
1351 l_line_amount NUMBER;
1352 l_line_tax_amount NUMBER;
1353 l_line_original_due NUMBER;
1354
1355 BEGIN
1356
1357 l_line_amount := INV_LN_AMT_ORIG_WOTAX(p_customer_trx_id,
1358 p_customer_trx_line_id);
1359
1360 FOR r IN invoice_line_tax_amount(p_customer_trx_line_id)
1361 LOOP
1362 l_line_tax_amount := r.LINE_TAX_AMOUNT;
1363 END LOOP;
1364
1365 l_line_original_due := NVL(NVL(l_line_amount,0) + NVL(l_line_tax_amount,0),0);
1366 return (l_line_original_due);
1367 END;
1368
1369 FUNCTION INV_LN_AMT_APPLIED_W_INCTAX(
1370 p_customer_trx_id IN NUMBER,
1371 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1372
1373 CURSOR tax_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1374 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
1375 FROM ar_receivable_applications_all app,
1376 ar_payment_schedules_all sch,
1377 ar_distributions_all ad,
1378 ra_customer_trx_lines_all lines
1379 WHERE app.status = 'APP'
1380 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1381 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
1382 AND sch.customer_trx_id = p_header_id
1383 AND app.application_type = 'CASH'
1384 AND app.receivable_application_id = ad.source_id
1385 AND ad.source_table = 'RA'
1386 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1387 AND lines.link_to_cust_trx_line_id = p_line_id
1388 AND lines.line_type = 'TAX'
1389 AND lines.amount_includes_tax_flag = 'Y';
1390
1391 l_line_amount_applied NUMBER;
1392 l_tax_amount_applied NUMBER;
1393 BEGIN
1394
1395 l_line_amount_applied := INV_LN_AMT_APPLIED_WOTAX(p_customer_trx_id,
1396 p_customer_trx_line_id);
1397
1398 FOR r in tax_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
1399 LOOP
1400 l_tax_amount_applied := r.tax_applied;
1401 END LOOP;
1402 RETURN(nvl(l_line_amount_applied,0) + NVL(l_tax_amount_applied,0));
1403 END;
1404
1405 FUNCTION INV_LN_AMT_CREDITED_W_INCTAX(
1406 p_customer_trx_id IN NUMBER,
1407 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1408
1409 CURSOR tax_credited_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1410 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
1411 FROM ar_receivable_applications_all app,
1412 ar_payment_schedules_all sch,
1413 ar_distributions_all ad,
1414 ra_customer_trx_lines_all lines
1415 WHERE app.status = 'APP'
1416 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1417 AND sch.class = 'INV'
1418 AND sch.customer_trx_id = p_header_id
1419 AND app.application_type = 'CM'
1420 AND app.receivable_application_id = ad.source_id
1421 AND ad.source_table = 'RA'
1422 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1423 AND lines.link_to_cust_trx_line_id = p_line_id
1424 AND lines.line_type = 'TAX'
1425 AND lines.amount_includes_tax_flag = 'Y';
1426
1427 l_line_amount_credited NUMBER;
1428 l_tax_amount_credited NUMBER;
1429 BEGIN
1430
1431 l_line_amount_credited := INV_LN_AMT_CREDITED_WOTAX(
1432 p_customer_trx_id,
1433 p_customer_trx_line_id);
1434
1435 FOR r in tax_credited_csr(p_customer_trx_id, p_customer_trx_line_id)
1436 LOOP
1437 l_tax_amount_credited := r.credit_applied;
1438 END LOOP;
1439 RETURN(nvl(l_line_amount_credited,0) + NVL(l_tax_amount_credited,0));
1440 END;
1441
1442 FUNCTION INV_LN_AMT_ADJUSTED_W_INCTAX(
1443 p_customer_trx_id IN NUMBER,
1444 p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
1445
1446 CURSOR invoice_ln_tax_adj(p_header_id NUMBER, p_line_id NUMBER) IS
1447 SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) tax_adjusted
1448 FROM ar_adjustments_all adj,
1449 ar_payment_schedules_all sch,
1450 ar_distributions_all dist,
1451 ra_customer_trx_lines_all lines
1452 WHERE adj.payment_schedule_id = sch.payment_schedule_id
1453 AND sch.class = 'INV'
1454 AND sch.customer_trx_id = p_header_id
1455 AND adj.ADJUSTMENT_ID = dist.source_id
1456 AND dist.source_table = 'ADJ'
1457 AND dist.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1458 AND lines.link_to_cust_trx_line_id = p_line_id
1459 AND lines.line_type = 'TAX'
1460 AND lines.amount_includes_tax_flag = 'Y';
1461
1462 l_invoice_ln_amount_adj NUMBER;
1463 l_invoice_ln_tax_adj NUMBER;
1464 BEGIN
1465
1466 l_invoice_ln_amount_adj := INV_LN_AMT_ADJUSTED_WOTAX(
1467 p_customer_trx_id,
1468 p_customer_trx_line_id);
1469
1470 FOR R IN invoice_ln_tax_adj (p_customer_trx_id,p_customer_trx_line_id)
1471 LOOP
1472 l_invoice_ln_tax_adj := r.tax_adjusted;
1473 END LOOP;
1474
1475 RETURN (NVL(l_invoice_ln_amount_adj,0)+NVL(l_invoice_ln_tax_adj,0));
1476
1477 END INV_LN_AMT_ADJUSTED_W_INCTAX;
1478
1479 FUNCTION INV_LN_AMT_REMAINING_W_INCTAX(
1480 p_customer_trx_id IN NUMBER,
1481 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1482 l_invoice_line_amount_orig NUMBER;
1483 l_invoice_line_amount_applied NUMBER;
1484 l_invoice_line_amount_credited NUMBER;
1485 l_invoice_line_amt_remaining NUMBER;
1486 l_invoice_line_amount_adjusted NUMBER;
1487 BEGIN
1488 l_invoice_line_amount_orig := INV_LN_AMT_ORIG_W_INCTAX(
1489 p_customer_trx_id,
1490 p_customer_trx_line_id);
1491
1492 l_invoice_line_amount_applied := INV_LN_AMT_APPLIED_W_INCTAX(
1493 p_customer_trx_id,
1494 p_customer_trx_line_id);
1495
1496 l_invoice_line_amount_credited := INV_LN_AMT_CREDITED_W_INCTAX(
1497 p_customer_trx_id,
1498 p_customer_trx_line_id);
1499
1500 l_invoice_line_amount_adjusted := INV_LN_AMT_ADJUSTED_W_INCTAX(
1501 p_customer_trx_id,
1502 p_customer_trx_line_id);
1503
1504 l_invoice_line_amt_remaining := NVL(l_invoice_line_amount_orig -l_invoice_line_amount_applied - l_invoice_line_amount_credited + l_invoice_line_amount_adjusted, 0);
1505
1506 RETURN (l_invoice_line_amt_remaining);
1507 END;
1508
1509 END OKL_BILLING_UTIL_PVT;