[Home] [Help]
PACKAGE BODY: APPS.OKL_BILLING_UTIL_PVT
Source
1 PACKAGE BODY OKL_BILLING_UTIL_PVT AS
2 /* $Header: OKLRBULB.pls 120.14 2011/12/05 09:00:03 sosharma 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
203 --Bug# 9116332
204 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
205 SELECT COUNT(1) LINE_COUNT
206 FROM RA_CUSTOMER_TRX_LINES_ALL
207 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
208 AND LINE_TYPE = 'LINE';
209
210 l_line_count NUMBER;
211 l_amount_applied NUMBER;
212 --Bug# 9116332
213
214 BEGIN
215
216 --Bug# 9116332
217 FOR r IN line_count_csr(p_customer_trx_id)
218 LOOP
219 l_line_count := r.line_count;
220 END LOOP;
221
222 IF (l_line_count > 1) THEN
223 -- New style (R12B) invoice
224 --Bug# 7720775
225 l_line_amount_applied := INV_LN_AMT_APPLIED_WOTAX(p_customer_trx_id,
226 p_customer_trx_line_id);
227
228 FOR r in tax_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
229 LOOP
230 l_tax_amount_applied := r.tax_applied;
231 END LOOP;
232
233 l_amount_applied := nvl(l_line_amount_applied,0) + NVL(l_tax_amount_applied,0);
234
235 ELSIF (l_line_count = 1) THEN
236 -- Old style invoice
237 l_amount_applied := invoice_amount_applied(p_customer_trx_id);
238
239 END IF;
240 --Bug# 9116332
241
242 RETURN(l_amount_applied);
243 END;
244
245 FUNCTION INVOICE_LINE_AMOUNT_CREDITED(
246 p_customer_trx_id IN NUMBER,
247 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
248
249 CURSOR tax_credited_csr(p_header_id NUMBER, p_line_id NUMBER) IS
250 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
251 FROM ar_receivable_applications_all app,
252 ar_payment_schedules_all sch,
253 ar_distributions_all ad,
254 ra_customer_trx_lines_all lines
255 WHERE app.status = 'APP'
256 AND app.applied_payment_schedule_id = sch.payment_schedule_id
257 AND sch.class = 'INV'
258 AND sch.customer_trx_id = p_header_id
259 AND app.application_type = 'CM'
260 AND app.receivable_application_id = ad.source_id
261 AND ad.source_table = 'RA'
262 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
263 AND lines.link_to_cust_trx_line_id = p_line_id
264 AND lines.line_type = 'TAX';
265
266 l_line_amount_credited NUMBER;
267 l_tax_amount_credited NUMBER;
268
269 --Bug# 9116332
270 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
271 SELECT COUNT(1) LINE_COUNT
272 FROM RA_CUSTOMER_TRX_LINES_ALL
273 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
274 AND LINE_TYPE = 'LINE';
275
276 l_line_count NUMBER;
277 l_amount_credited NUMBER;
278 --Bug# 9116332
279
280 BEGIN
281
282 --Bug# 9116332
283 FOR r IN line_count_csr(p_customer_trx_id)
284 LOOP
285 l_line_count := r.line_count;
286 END LOOP;
287
288 IF (l_line_count > 1) THEN
289 -- New style (R12B) invoice
290
291 --Bug# 7720775
292 l_line_amount_credited := INV_LN_AMT_CREDITED_WOTAX(
293 p_customer_trx_id,
294 p_customer_trx_line_id);
295
296 FOR r in tax_credited_csr(p_customer_trx_id, p_customer_trx_line_id)
297 LOOP
298 l_tax_amount_credited := r.credit_applied;
299 END LOOP;
300
301 l_amount_credited := nvl(l_line_amount_credited,0) + NVL(l_tax_amount_credited,0);
302
303 ELSIF (l_line_count = 1) THEN
304 -- Old style invoice
305 l_amount_credited := invoice_amount_credited(p_customer_trx_id);
306
307 END IF;
308 --Bug# 9116332
309
310 RETURN(l_amount_credited);
311 END;
312 --asawanka modified for bug # 6497335 end
313
314 -- sosharma added for bug 13394643
315
316 FUNCTION CR_LN_AMT_APPLIED_WTAX(
317 p_customer_trx_id IN NUMBER,
318 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
319
320 CURSOR c_line_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
321 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) line_applied
322 FROM ar_receivable_applications_all app,
323 ar_payment_schedules_all sch,
324 ar_distributions_all ad
325 WHERE app.status = 'APP'
326 AND app.payment_schedule_id = sch.payment_schedule_id
327 AND sch.class IN ('CM') --Receipt can be applied against credit memo
328 AND sch.customer_trx_id = p_header_id
329 AND app.application_type = 'CM'
330 AND app.receivable_application_id = ad.source_id
331 AND ad.source_table = 'RA'
332 AND ad.ref_Customer_trx_Line_Id = p_line_id;
333
334 CURSOR c_tax_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
335 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
336 FROM ar_receivable_applications_all app,
337 ar_payment_schedules_all sch,
338 ar_distributions_all ad,
339 ra_customer_trx_lines_all lines
340 WHERE app.status = 'APP'
341 AND app.payment_schedule_id = sch.payment_schedule_id
342 AND sch.class IN ('CM')
343 AND sch.customer_trx_id = p_header_id
344 AND app.application_type = 'CM'
345 AND app.receivable_application_id = ad.source_id
346 AND ad.source_table = 'RA'
347 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
348 AND lines.link_to_cust_trx_line_id = p_line_id
349 AND lines.line_type = 'TAX';
350
351 l_line_amount_applied_c NUMBER;
352 l_tax_amount_applied_c NUMBER;
353
354
355 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
356 SELECT COUNT(1) LINE_COUNT
357 FROM RA_CUSTOMER_TRX_LINES_ALL
358 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
359 AND LINE_TYPE = 'LINE';
360
361 l_line_count NUMBER;
362 l_amount_applied_c NUMBER;
363
364
365 BEGIN
366
367 FOR r IN line_count_csr(p_customer_trx_id)
368 LOOP
369 l_line_count := r.line_count;
370 END LOOP;
371
372 -- IF (l_line_count > 1) THEN
373 -- New style (R12B) invoice
374
375 FOR r in c_line_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
376 LOOP
377 l_line_amount_applied_c := r.line_applied;
378 END LOOP;
379
380 FOR r in c_tax_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
381 LOOP
382 l_tax_amount_applied_c := r.tax_applied;
383 END LOOP;
384
385 l_amount_applied_c := nvl(l_line_amount_applied_c,0) + NVL(l_tax_amount_applied_c,0);
386
387 -- ELSIF (l_line_count = 1) THEN
388 -- Old style invoice
389 -- l_amount_applied_c := 0;
390
391 -- END IF;
392
393
394 RETURN(l_amount_applied_c);
395 END;
396
397 -- sosharma added for bug 13394643
398
399 FUNCTION INVOICE_LINE_AMOUNT_REMAINING(
400 p_customer_trx_id IN NUMBER,
401 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
402 l_invoice_line_amount_orig NUMBER;
403 l_invoice_line_amount_applied NUMBER;
404 l_invoice_line_amount_credited NUMBER;
405 l_invoice_line_amt_remaining NUMBER;
406 l_invoice_line_amount_adjusted NUMBER;
407 -- sosharma added for bug 13394643
408 l_cr_line_amount_applied NUMBER;
409 BEGIN
410 l_invoice_line_amount_orig := INVOICE_LINE_AMOUNT_ORIG(
411 p_customer_trx_id,
412 p_customer_trx_line_id);
413
414 l_invoice_line_amount_applied := INVOICE_LINE_AMOUNT_APPLIED(
415 p_customer_trx_id,
416 p_customer_trx_line_id);
417
418 l_invoice_line_amount_credited := INVOICE_LINE_AMOUNT_CREDITED(
419 p_customer_trx_id,
420 p_customer_trx_line_id);
421
422 l_invoice_line_amount_adjusted := INVOICE_LINE_AMOUNT_ADJUSTED(
423 p_customer_trx_id,
424 p_customer_trx_line_id);
425
426 l_cr_line_amount_applied:= CR_LN_AMT_APPLIED_WTAX(
427 p_customer_trx_id,
428 p_customer_trx_line_id);
429
430 l_invoice_line_amt_remaining := NVL(l_invoice_line_amount_orig -l_invoice_line_amount_applied - l_invoice_line_amount_credited - l_cr_line_amount_applied + l_invoice_line_amount_adjusted, 0);
431
432 RETURN (l_invoice_line_amt_remaining);
433 END;
434
435 FUNCTION INVOICE_AMOUNT_ORIG(
436 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
437 CURSOR invoice_amount_orig_csr(p_header_id NUMBER) IS
438 SELECT SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0)) AMOUNT_DUE_ORIGINAL
439 FROM AR_PAYMENT_SCHEDULES_ALL APS,
440 RA_CUSTOMER_TRX_ALL RACTRX
441 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
442 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
443 l_invoice_amount_orig NUMBER;
444 BEGIN
445 FOR R IN invoice_amount_orig_csr (p_customer_trx_id)
446 LOOP
447 l_invoice_amount_orig := r.AMOUNT_DUE_ORIGINAL;
448 END LOOP;
449 RETURN (l_invoice_amount_orig);
450 END;
451
452 FUNCTION INVOICE_AMOUNT_APPLIED(
453 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
454 --dkagrawa modified the cusrsor to add class CM
455 CURSOR invoice_amount_applied_csr(p_header_id NUMBER) IS
456 SELECT NVL(SUM(app.amount_applied),0) AMOUNT_APPLIED
457 FROM ar_receivable_applications_all app,
458 ar_payment_schedules_all sch
459 WHERE app.status = 'APP'
460 AND app.applied_payment_schedule_id = sch.payment_schedule_id
461 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
462 AND sch.customer_trx_id = p_header_id
463 AND app.application_type = 'CASH';
464 l_invoice_amount_applied NUMBER;
465 BEGIN
466 FOR R IN invoice_amount_applied_csr (p_customer_trx_id)
467 LOOP
468 l_invoice_amount_applied := r.AMOUNT_APPLIED;
469 END LOOP;
470 RETURN (l_invoice_amount_applied);
471 END;
472
473 FUNCTION INVOICE_AMOUNT_CREDITED(
474 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
475 CURSOR invoice_amount_credited_csr(p_header_id NUMBER) IS
476 SELECT NVL(SUM(app.amount_applied),0) AMOUNT_CREDITED
477 FROM ar_receivable_applications_all app,
478 ar_payment_schedules_all sch
479 WHERE app.status = 'APP'
480 AND app.applied_payment_schedule_id = sch.payment_schedule_id
481 AND sch.class = 'INV'
482 AND sch.customer_trx_id = p_header_id
483 AND app.application_type = 'CM';
484 l_invoice_amount_credited NUMBER;
485 BEGIN
486 FOR R IN invoice_amount_credited_csr (p_customer_trx_id)
487 LOOP
488 l_invoice_amount_credited := r.AMOUNT_CREDITED;
489 END LOOP;
490 RETURN (l_invoice_amount_credited);
491 END;
492
493 FUNCTION INVOICE_AMOUNT_REMAINING(
494 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
495 CURSOR invoice_amount_remaining_csr(p_header_id NUMBER) IS
496 SELECT SUM(NVL(APS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING
497 FROM AR_PAYMENT_SCHEDULES_ALL APS,
498 RA_CUSTOMER_TRX_ALL RACTRX
499 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
500 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
501 l_invoice_amount_remaining NUMBER;
502 BEGIN
503 FOR R IN invoice_amount_remaining_csr (p_customer_trx_id)
504 LOOP
505 l_invoice_amount_remaining := r.AMOUNT_DUE_REMAINING;
506 END LOOP;
507 RETURN (l_invoice_amount_remaining);
508 END;
509
510 FUNCTION LINE_ID_APPLIED(p_cash_receipt_id IN NUMBER,
511 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
512 CURSOR invoice_lines(p_cash_receipt_id IN NUMBER,p_customer_trx_id IN NUMBER) IS
513 SELECT RACTRXLN.customer_trx_line_id
514 FROM RA_CUSTOMER_TRX_LINES_ALL RACTRXLN
515 WHERE RACTRXLN.LINE_TYPE = 'LINE'
516 AND RACTRXLN.interface_line_context = 'OKL_CONTRACTS'
517 AND RACTRXLN.CUSTOMER_TRX_ID = p_customer_trx_id;
518 l_inv_ln_id NUMBER := NULL;
519 l_count NUMBER :=0;
520 BEGIN
521 FOR R IN invoice_lines (p_cash_receipt_id,p_customer_trx_id)
522 LOOP
523 l_count := l_count + 1;
524 l_inv_ln_id := R.customer_trx_line_id;
525 END LOOP;
526 IF l_count = 1 THEN
527 RETURN l_inv_ln_id;
528 ELSE
529 RETURN NULL;
530 END IF;
531 END;
532
533 FUNCTION LINE_NUMBER_APPLIED(p_cash_receipt_id IN NUMBER,
534 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
535 CURSOR invoice_lines(p_cash_receipt_id IN NUMBER,p_customer_trx_id IN NUMBER) IS
536 SELECT RACTRXLN.line_number
537 FROM RA_CUSTOMER_TRX_LINES_ALL RACTRXLN
538 WHERE RACTRXLN.LINE_TYPE = 'LINE'
539 AND RACTRXLN.interface_line_context = 'OKL_CONTRACTS'
540 AND RACTRXLN.CUSTOMER_TRX_ID = p_customer_trx_id;
541 l_inv_ln_num NUMBER := NULL;
542 l_count NUMBER :=0;
543 BEGIN
544 FOR R IN invoice_lines (p_cash_receipt_id,p_customer_trx_id)
545 LOOP
546 l_count := l_count + 1;
547 l_inv_ln_num := R.line_number;
548 END LOOP;
549 IF l_count = 1 THEN
550 RETURN l_inv_ln_num;
551 ELSE
552 RETURN NULL;
553 END IF;
554 END;
555
556 /*FUNCTION DEBUG_PROC(msg varchar2) RETURN VARCHAR2 as
557 PRAGMA AUTONOMOUS_TRANSACTION ;
558 l_seq_num number;
559 BEGIN
560 SELECT new_seq.nextval into l_seq_num from dual;
561 INSERT INTO DEBUG_TABLE_k VALUES(l_seq_num,SYSDATE, msg);
562 commit;
563 RETURN 'X';
564 END DEBUG_PROC;*/
565
566 FUNCTION get_tld_amount_orig( p_tld_id IN NUMBER ) RETURN NUMBER IS
567 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
568 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
569 FROM RA_CUSTOMER_TRX_LINES_ALL
570 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
571 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
572
573 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
574 SELECT COUNT(1) LINE_COUNT
575 FROM RA_CUSTOMER_TRX_LINES_ALL
576 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
577 AND LINE_TYPE = 'LINE';
578
579 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
580 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
581 l_line_count NUMBER;
582 l_amount_orig NUMBER := 0;
583 BEGIN
584 NULL;
585 FOR r IN cust_trx_csr(p_tld_id)
586 LOOP
587 l_customer_trx_id := r.CUSTOMER_TRX_ID;
588 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
589 END LOOP;
590
591 FOR r IN line_count_csr(l_customer_trx_id)
592 LOOP
593 l_line_count := r.line_count;
594 END LOOP;
595
596 IF (l_line_count > 1) THEN
597 -- New style (R12B) invoice
598 l_amount_orig := invoice_line_amount_orig(
599 l_customer_trx_id, l_customer_trx_line_id);
600 ELSIF (l_line_count = 1) THEN
601 -- Old style invoice
602 l_amount_orig := invoice_amount_orig(l_customer_trx_id);
603 END IF;
604
605 RETURN (l_amount_orig);
606
607 END;
608
609 FUNCTION get_tld_amount_applied( p_tld_id IN NUMBER ) RETURN NUMBER IS
610 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
611 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
612 FROM RA_CUSTOMER_TRX_LINES_ALL
613 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
614 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
615
616 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
617 SELECT COUNT(1) LINE_COUNT
618 FROM RA_CUSTOMER_TRX_LINES_ALL
619 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
620 AND LINE_TYPE = 'LINE';
621
622 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
623 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
624 l_line_count NUMBER;
625 l_amount_applied NUMBER := 0;
626 BEGIN
627 NULL;
628 FOR r IN cust_trx_csr(p_tld_id)
629 LOOP
630 l_customer_trx_id := r.CUSTOMER_TRX_ID;
631 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
632 END LOOP;
633
634 FOR r IN line_count_csr(l_customer_trx_id)
635 LOOP
636 l_line_count := r.line_count;
637 END LOOP;
638
639 IF (l_line_count > 1) THEN
640 -- New style (R12B) invoice
641 l_amount_applied := invoice_line_amount_applied(
642 l_customer_trx_id, l_customer_trx_line_id);
643 ELSIF (l_line_count = 1) THEN
644 -- Old style invoice
645 l_amount_applied := invoice_amount_applied(l_customer_trx_id);
646 END IF;
647
648 RETURN (l_amount_applied);
649
650 END;
651
652 FUNCTION get_tld_amount_credited( p_tld_id IN NUMBER ) RETURN NUMBER IS
653 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
654 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
655 FROM RA_CUSTOMER_TRX_LINES_ALL
656 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
657 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
658
659 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
660 SELECT COUNT(1) LINE_COUNT
661 FROM RA_CUSTOMER_TRX_LINES_ALL
662 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
663 AND LINE_TYPE = 'LINE';
664
665 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
666 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
667 l_line_count NUMBER;
668 l_amount_credited NUMBER := 0;
669 BEGIN
670 NULL;
671 FOR r IN cust_trx_csr(p_tld_id)
672 LOOP
673 l_customer_trx_id := r.CUSTOMER_TRX_ID;
674 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
675 END LOOP;
676
677 FOR r IN line_count_csr(l_customer_trx_id)
678 LOOP
679 l_line_count := r.line_count;
680 END LOOP;
681
682 IF (l_line_count > 1) THEN
683 -- New style (R12B) invoice
684 l_amount_credited := invoice_line_amount_credited(
685 l_customer_trx_id, l_customer_trx_line_id);
686 ELSIF (l_line_count = 1) THEN
687 -- Old style invoice
688 l_amount_credited := invoice_amount_credited(l_customer_trx_id);
689 END IF;
690
691 RETURN (l_amount_credited);
692
693 END;
694
695 FUNCTION get_tld_amount_remaining( p_tld_id IN NUMBER ) RETURN NUMBER IS
696 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
697 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
698 FROM RA_CUSTOMER_TRX_LINES_ALL
699 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
700 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
701
702 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
703 SELECT COUNT(1) LINE_COUNT
704 FROM RA_CUSTOMER_TRX_LINES_ALL
705 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
706 AND LINE_TYPE = 'LINE';
707
708 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
709 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
710 l_line_count NUMBER;
711 l_amount_remaining NUMBER := 0;
712 BEGIN
713 NULL;
714 FOR r IN cust_trx_csr(p_tld_id)
715 LOOP
716 l_customer_trx_id := r.CUSTOMER_TRX_ID;
717 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
718 END LOOP;
719
720 FOR r IN line_count_csr(l_customer_trx_id)
721 LOOP
722 l_line_count := r.line_count;
723 END LOOP;
724
725 IF (l_line_count > 1) THEN
726 -- New style (R12B) invoice
727 l_amount_remaining := invoice_line_amount_remaining(
728 l_customer_trx_id, l_customer_trx_line_id);
729 ELSIF (l_line_count = 1) THEN
730 -- Old style invoice
731 l_amount_remaining := invoice_amount_remaining(l_customer_trx_id);
732 END IF;
733
734 RETURN (l_amount_remaining);
735
736 END;
737
738 PROCEDURE get_tld_balance(
739 p_api_version IN NUMBER
740 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
741 ,p_tld_id IN NUMBER
742 ,x_return_status OUT NOCOPY VARCHAR2
743 ,x_msg_count OUT NOCOPY NUMBER
744 ,x_msg_data OUT NOCOPY VARCHAR2
745 ,x_orig_amount OUT NOCOPY NUMBER
746 ,x_applied_amount OUT NOCOPY NUMBER
747 ,x_credited_amount OUT NOCOPY NUMBER
748 ,x_remaining_amount OUT NOCOPY NUMBER
749 ,x_tax_amount OUT NOCOPY NUMBER
750 )
751 IS
752
753 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
754 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
755 FROM RA_CUSTOMER_TRX_LINES_ALL
756 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
757 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
758
759 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
760 SELECT COUNT(1) LINE_COUNT
761 FROM RA_CUSTOMER_TRX_LINES_ALL
762 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
763 AND LINE_TYPE = 'LINE';
764
765 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
766 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
767 FROM RA_CUSTOMER_TRX_LINES_ALL
768 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id
769 AND LINE_TYPE='TAX';
770
771 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
772 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
773 l_line_count NUMBER;
774 l_amount_remaining NUMBER := 0;
775
776 l_api_name VARCHAR2(60) := 'Get TLD Balance';
777
778 BEGIN
779
780 x_orig_amount := 0.0;
781 x_applied_amount := 0;
782 x_credited_amount := 0;
783 x_remaining_amount := 0;
784 x_tax_amount := 0;
785
786 FOR r IN cust_trx_csr(p_tld_id)
787 LOOP
788 l_customer_trx_id := r.CUSTOMER_TRX_ID;
789 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
790 END LOOP;
791
792 FOR r IN line_count_csr(l_customer_trx_id)
793 LOOP
794 l_line_count := r.line_count;
795 END LOOP;
796
797 FOR r IN invoice_line_tax_amount(l_customer_trx_line_id)
798 LOOP
799 x_tax_amount := r.LINE_TAX_AMOUNT;
800 END LOOP;
801
802 IF (l_line_count > 1) THEN
803 -- New style (R12B) invoice
804 x_orig_amount := invoice_line_amount_orig(
805 l_customer_trx_id, l_customer_trx_line_id);
806 x_applied_amount := invoice_line_amount_applied(
807 l_customer_trx_id, l_customer_trx_line_id);
808 x_credited_amount := invoice_line_amount_credited(
809 l_customer_trx_id, l_customer_trx_line_id);
810 x_remaining_amount := invoice_line_amount_remaining(
811 l_customer_trx_id, l_customer_trx_line_id);
812 ELSIF (l_line_count = 1) THEN
813 -- Old style invoice
814 x_orig_amount := invoice_amount_orig(l_customer_trx_id);
815 x_applied_amount := invoice_amount_applied(l_customer_trx_id);
816 x_credited_amount := invoice_amount_credited(l_customer_trx_id);
817 x_remaining_amount := invoice_amount_remaining(l_customer_trx_id);
818 END IF;
819
820 x_return_status := 'S';
821
822 EXCEPTION
823 WHEN OTHERS THEN
824
825 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
826 p_api_name => l_api_name,
827 p_pkg_name => G_PKG_NAME,
828 p_exc_name => 'OTHERS',
829 x_msg_count => x_msg_count,
830 x_msg_data => x_msg_data,
831 p_api_type => '_PVT');
832
833 END get_tld_balance;
834
835
836
837 PROCEDURE get_contract_invoice_balance(
838 p_api_version IN NUMBER
839 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
840 ,p_contract_number IN VARCHAR2
841 ,p_trx_number IN VARCHAR2
842 ,x_return_status OUT NOCOPY VARCHAR2
843 ,x_msg_count OUT NOCOPY NUMBER
844 ,x_msg_data OUT NOCOPY VARCHAR2
845 ,x_remaining_amount OUT NOCOPY NUMBER
846 ) IS
847
848
849 CURSOR cust_trx_csr(p_cust_trx_number VARCHAR2, p_contract_number VARCHAR2) IS
850 SELECT LNS.CUSTOMER_TRX_ID
851 FROM RA_CUSTOMER_TRX_LINES_ALL LNS,
852 RA_CUSTOMER_TRX_ALL HDR
853 WHERE LNS.INTERFACE_LINE_ATTRIBUTE6 = p_contract_number
854 AND HDR.TRX_NUMBER = p_cust_trx_number
855 AND HDR.CUSTOMER_TRX_ID = LNS.CUSTOMER_TRX_ID;
856
857
858 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
859 l_amount_remaining NUMBER := 0;
860
861 l_api_name VARCHAR2(60) := 'get_contract_invoice_balance';
862
863 BEGIN
864
865 x_remaining_amount := 0;
866
867 open cust_trx_csr(p_trx_number,p_contract_number);
868 fetch cust_trx_csr into l_customer_trx_id;
869 close cust_trx_csr;
870
871 x_remaining_amount := invoice_amount_remaining(l_customer_trx_id);
872 x_return_status := 'S';
873
874 EXCEPTION
875 WHEN OTHERS THEN
876
877 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
878 p_api_name => l_api_name,
879 p_pkg_name => G_PKG_NAME,
880 p_exc_name => 'OTHERS',
881 x_msg_count => x_msg_count,
882 x_msg_data => x_msg_data,
883 p_api_type => '_PVT');
884
885 END get_contract_invoice_balance;
886
887 FUNCTION INVOICE_LINE_TAX_AMOUNT(p_customer_trx_line_id NUMBER) RETURN NUMBER IS
888 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
889 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
890 FROM RA_CUSTOMER_TRX_LINES_ALL
891 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id
892 AND LINE_TYPE='TAX';
893
894 l_tax_amount NUMBER;
895 BEGIN
896
897 l_tax_amount := 0;
898
899 FOR r IN invoice_line_tax_amount(p_customer_trx_line_id)
900 LOOP
901 l_tax_amount := r.LINE_TAX_AMOUNT;
902 END LOOP;
903
904 RETURN(l_tax_amount);
905
906 END;
907
908
909 /** VPANWAR 05-Sep-2007 Procedure to list In Process Billing
910 Transactions pre-upgrade script **/
911 PROCEDURE CHECK_PREUPGRADE_DATA(x_errbuf OUT NOCOPY VARCHAR2,
912 x_retcode OUT NOCOPY NUMBER,
913 x_any_data_exists OUT NOCOPY BOOLEAN )
914 IS
915 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_PREUPGRADE_DATA';
916 l_msg_count NUMBER;
917 l_msg_data VARCHAR2(2000);
918 l_return_status VARCHAR2(1);
919 l_api_version NUMBER;
920 l_init_msg_list VARCHAR2(1);
921 l_total_length CONSTANT NUMBER DEFAULT 152;
922 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_BILLING_UTIL_PVT.CHECK_PREUPGRADE_DATA';
923 l_debug_enabled VARCHAR2(10);
924 is_debug_procedure_on BOOLEAN;
925 is_debug_statement_on BOOLEAN;
926 l_contract VARCHAR2(120);
927
928 -- This cursor is used for the following purpose
929 -- For every record in okl_trx_ar_invoices_b, okl_txl_ar_inv_lns_b, okl_txd_ar_ln_dtls_b
930 -- check if corresponding records exist in okl_ext_sell_invs_b, okl_xtl_sell_invs_b table.
931 -- We check both two level and three level transactions with the external tables.
932 CURSOR int_ext_check_csr IS
933 SELECT OKC_K_HEADERS_B.CONTRACT_NUMBER
934 FROM OKL_TXL_AR_INV_LNS_B
935 , OKL_TXD_AR_LN_DTLS_B
936 , OKL_TRX_AR_INVOICES_B
937 , OKL_K_HEADERS
938 , OKC_K_HEADERS_B
939 WHERE OKL_TXD_AR_LN_DTLS_B.TIL_ID_DETAILS = OKL_TXL_AR_INV_LNS_B.ID
940 AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
941 AND OKL_K_HEADERS.ID = OKL_TRX_AR_INVOICES_B.KHR_ID
942 AND OKL_TXL_AR_INV_LNS_B.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
943 AND OKL_TXD_AR_LN_DTLS_B.ID NOT IN
944 ( SELECT OKL_XTL_SELL_INVS_B.TLD_ID
945 FROM OKL_XTL_SELL_INVS_B
946 , OKL_EXT_SELL_INVS_B
947 WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
948 AND OKL_XTL_SELL_INVS_B.TLD_ID IS NOT NULL)
949 UNION
950 SELECT OKC_K_HEADERS_B.CONTRACT_NUMBER
951 FROM OKL_TXL_AR_INV_LNS_B TIL
952 , OKL_TRX_AR_INVOICES_B
953 , OKL_K_HEADERS
954 , OKC_K_HEADERS_B
955 WHERE OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
956 AND OKL_K_HEADERS.ID = OKL_TRX_AR_INVOICES_B.KHR_ID
957 AND TIL.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
958 AND NOT EXISTS (
959 SELECT 1 FROM OKL_TXD_AR_LN_DTLS_B TXD
960 WHERE TXD.TIL_ID_DETAILS = TIL.ID
961 )
962 AND TIL.ID NOT IN
963 (SELECT OKL_XTL_SELL_INVS_B.TIL_ID
964 FROM OKL_XTL_SELL_INVS_B
965 , OKL_EXT_SELL_INVS_B
966 WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
967 AND OKL_XTL_SELL_INVS_B.TIL_ID IS NOT NULL);
968
969 -- This cursor is used for the following purpose
970 -- For every record in okl_ext_sell_invs_b, okl_xtl_sell_invs_b check if
971 -- corresponding records exist in okl_cnsld_ar_hdrs_b, okl_cnsld_ar_lines_b,
972 -- okl_cnsld_ar_Strms_b tables.
973
974 CURSOR ext_cnsld_check_csr IS
975 SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
976 FROM OKL_XTL_SELL_INVS_B
977 ,OKL_EXT_SELL_INVS_B
978 ,OKL_XTL_SELL_INVS_TL
979 ,OKL_TRX_AR_INVOICES_B
980 ,OKL_TXL_AR_INV_LNS_B
981 ,OKL_TXD_AR_LN_DTLS_B
982 ,OKC_K_HEADERS_B
983 WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
984 AND OKL_XTL_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_TL.ID
985 AND OKL_XTL_SELL_INVS_B.LSM_ID IS NULL
986 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)
987 AND OKL_TXD_AR_LN_DTLS_B.TIL_ID_DETAILS = OKL_TXL_AR_INV_LNS_B.ID
988 AND OKL_TXL_AR_INV_LNS_B.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
989 AND OKL_TRX_AR_INVOICES_B.KHR_ID = OKC_K_HEADERS_B.id;
990
991 -- This cursor is used for the following purpose
992 -- For every record in okl_cnsld_hdrs_b, okl_cnsld_ar_lines_b, okl_cnsld_ar_strms_b check
993 -- if corresponding records exist in AR.
994 CURSOR cnsld_ar_check_csr IS
995 SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
996 FROM OKL_CNSLD_AR_STRMS_B
997 , OKL_CNSLD_AR_LINES_B
998 , OKL_CNSLD_AR_HDRS_B
999 , OKL_K_HEADERS
1000 , OKC_K_HEADERS_B
1001 WHERE OKL_CNSLD_AR_LINES_B.CNR_ID = OKL_CNSLD_AR_HDRS_B.ID
1002 AND OKL_CNSLD_AR_STRMS_B.LLN_ID = OKL_CNSLD_AR_LINES_B.ID
1003 AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
1004 AND OKL_K_HEADERS.ID = OKL_CNSLD_AR_STRMS_B.KHR_ID
1005 AND OKL_CNSLD_AR_STRMS_B.KHR_ID = OKL_CNSLD_AR_LINES_B.KHR_ID
1006 AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID IS NULL;
1007
1008 -- This cursor is used for the following purpose
1009 -- For every record in okl_cnsld_ar_strms_b check if receivable_invoice_id is a positive number.
1010 CURSOR cnsld_pstv_check_csr IS
1011 SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
1012 FROM OKL_CNSLD_AR_STRMS_B
1013 , OKL_CNSLD_AR_LINES_B
1014 , OKL_CNSLD_AR_HDRS_B
1015 , OKL_K_HEADERS
1016 , OKC_K_HEADERS_B
1017 WHERE OKL_CNSLD_AR_LINES_B.CNR_ID = OKL_CNSLD_AR_HDRS_B.ID
1018 AND OKL_CNSLD_AR_STRMS_B.LLN_ID = OKL_CNSLD_AR_LINES_B.ID
1019 AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
1020 AND OKL_K_HEADERS.ID = OKL_CNSLD_AR_STRMS_B.KHR_ID
1021 AND OKL_CNSLD_AR_STRMS_B.KHR_ID = OKL_CNSLD_AR_LINES_B.KHR_ID
1022 AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID < 0
1023 AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID IS NOT NULL;
1024
1025 BEGIN
1026
1027 x_any_data_exists := false; -- VPANWAR changed for pre upgrade test
1028
1029 /* l_debug_enabled := okl_debug_pub.check_log_enabled;
1030 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1031 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1032 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRBULB.pls call CHECK_PREUPGRADE_DATA');
1033 END IF;*/
1034
1035 x_retcode := 0;
1036 x_errbuf := null;
1037 x_any_data_exists := FALSE;
1038
1039 l_api_version := 1.0;
1040 l_init_msg_list := Okl_Api.G_TRUE;
1041 l_msg_count := 0;
1042
1043 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
1044 G_PKG_NAME,
1045 l_init_msg_list,
1046 l_api_version,
1047 l_api_version,
1048 '_PVT',
1049 l_return_status);
1050 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1051 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1052 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1053 RAISE OKL_API.G_EXCEPTION_ERROR;
1054 END IF;
1055
1056 -- Printing In Process Billing Transactions report header. The formatting of the message titles is
1057 -- also taken care here.
1058 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKLHOMENAVTITLE') ||
1059 RPAD(' ', 53 , ' ' ));
1060 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1061 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1062 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKL_IN_PRCS_BILL_TRANS_REP') ||
1063 RPAD(' ', 53 , ' ' ));
1064 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || '-------------------------------' || RPAD(' ', 51, ' ' ));
1065 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1066 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1067 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));*/
1068
1069 -- Printing the titles for 'Contract Number' and 'Next Step'
1070 /* FND_FILE.PUT(FND_FILE.OUTPUT, RPAD('Contract Number Next Step',150,' '));
1071 FND_FILE.PUT(FND_FILE.OUTPUT, RPAD('------------------ --------------',150,' ')); */
1072
1073 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract Number Next Step');
1074 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------------ --------------'); */
1075
1076 -- Initialize the contract variable to Null.
1077 -- Open the cursor Int_Ext_Check_Csr, Loop through it and display the contract number
1078 -- and the Next step value should be 'Run Prepare Receivables Bills concurrent program'.
1079 l_contract := NULL;
1080 OPEN int_ext_check_csr;
1081 -- Fetch from the cursor the first time and check if any row is found.
1082 -- If found then print the contract number , the next step and loop through
1083 -- the cursor. If not found then display the No records found message.
1084 FETCH int_ext_check_csr INTO l_contract;
1085 IF int_ext_check_csr%FOUND
1086 THEN
1087 LOOP
1088 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1089 x_retcode := 1;
1090 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
1091 RPAD(' Run Prepare Receivables Bills concurrent program',120,' '));
1092 FETCH int_ext_check_csr INTO l_contract;
1093 EXIT WHEN int_ext_check_csr%NOTFOUND;*/
1094 x_errbuf := 'Prepare Receivables Bills concurrent program';
1095 EXIT;
1096 END LOOP;
1097 /* ELSE
1098 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
1099 END IF;
1100 CLOSE int_ext_check_csr;
1101
1102 -- Insert blank lines
1103 /*FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1104 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1105 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));*/
1106
1107 -- Initialize the contract variable to Null.
1108 -- Open the cursor ext_cnsld_check_csr, Loop through it and display the contract number
1109 -- and the Next step value should be 'Run Receivables Bills Consolidation concurrent program'.
1110 l_contract := NULL;
1111 OPEN ext_cnsld_check_csr;
1112 -- Fetch from the cursor the first time and check if any row is found.
1113 -- If found then print the contract number , the next step and loop through
1114 -- the cursor. If not found then display the No records found message.
1115 FETCH ext_cnsld_check_csr INTO l_contract;
1116 IF ext_cnsld_check_csr%FOUND
1117 THEN
1118 LOOP
1119 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1120 x_retcode := 1;
1121 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
1122 RPAD(' Run Receivables Bills Consolidation concurrent program',120,' '));
1123 FETCH ext_cnsld_check_csr INTO l_contract;
1124 EXIT WHEN ext_cnsld_check_csr%NOTFOUND;*/
1125 x_errbuf := 'Receivables Bills Consolidation concurrent program';
1126 EXIT;
1127 END LOOP;
1128 /*ELSE
1129 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
1130 END IF;
1131 CLOSE ext_cnsld_check_csr;
1132
1133 -- Insert blank lines
1134 /*FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1135 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1136 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));*/
1137
1138 -- Initialize the contract variable to Null.
1139 -- Open the cursor cnsld_ar_check_csr, Loop through it and display the contract number
1140 -- and the Next step value should be 'Run Prepare Receivables Bills concurrent program'.
1141 l_contract := NULL;
1142 OPEN cnsld_ar_check_csr;
1143 -- Fetch from the cursor the first time and check if any row is found.
1144 -- If found then print the contract number , the next step and loop through
1145 -- the cursor. If not found then display the No records found message.
1146 FETCH cnsld_ar_check_csr INTO l_contract;
1147 IF cnsld_ar_check_csr%FOUND
1148 THEN
1149 LOOP
1150 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1151 x_retcode := 1;
1152 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
1153 RPAD(' Run Receivables Invoice Transfer to AR concurrent program',120,' '));
1154 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',30,' ')||
1155 RPAD(' Run AutoInvoice Master Program',120,' '));
1156 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',30,' ')||
1157 RPAD(' Run Fetch AR Invoice Numbers',120,' '));
1158 FETCH cnsld_ar_check_csr INTO l_contract;
1159 EXIT WHEN cnsld_ar_check_csr%NOTFOUND;*/
1160 x_errbuf := 'AutoInvoice Master Program and Fetch AR Invoice Numbers';
1161 EXIT;
1162 END LOOP;
1163 /*ELSE
1164 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
1165 END IF;
1166 CLOSE cnsld_ar_check_csr;
1167
1168 -- Insert blank lines
1169 /*FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1170 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
1171 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));*/
1172
1173 -- Initialize the contract variable to Null.
1174 -- Open the cursor cnsld_pstv_check_csr, Loop through it and display the contract number
1175 -- and the Next step value should be 'Run Prepare Receivables Bills concurrent program'.
1176 l_contract := NULL;
1177 OPEN cnsld_pstv_check_csr;
1178 -- Fetch from the cursor the first time and check if any row is found.
1179 -- If found then print the contract number , the next step and loop through
1180 -- the cursor. If not found then display the No records found message.
1181 FETCH cnsld_pstv_check_csr INTO l_contract;
1182 IF cnsld_pstv_check_csr%FOUND
1183 THEN
1184 LOOP
1185 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1186 x_retcode := 1;
1187 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(SUBSTR(l_contract,1,28),30,' ')||
1188 RPAD(' Run Fetch AR Invoice Numbers',120,' '));
1189 FETCH cnsld_pstv_check_csr INTO l_contract;
1190 EXIT WHEN cnsld_pstv_check_csr%NOTFOUND;*/
1191 x_errbuf := 'Fetch AR Invoice Numbers';
1192 EXIT;
1193 END LOOP;
1194 /*ELSE
1195 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || 'No Records Found' || RPAD(' ', 51, ' ' ));*/
1196 END IF;
1197 CLOSE cnsld_pstv_check_csr;
1198
1199 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
1200 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1201 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRUPGB.pls call IN_PROCESS_BILLING_TXN');
1202 END IF;
1203
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 IF int_ext_check_csr%ISOPEN
1207 THEN
1208 CLOSE int_ext_check_csr;
1209 END IF;
1210 IF ext_cnsld_check_csr%ISOPEN
1211 THEN
1212 CLOSE ext_cnsld_check_csr;
1213 END IF;
1214 IF cnsld_ar_check_csr%ISOPEN
1215 THEN
1216 CLOSE cnsld_ar_check_csr;
1217 END IF;
1218 IF cnsld_pstv_check_csr%ISOPEN
1219 THEN
1220 CLOSE cnsld_pstv_check_csr;
1221 END IF;
1222 x_errbuf := SQLERRM;
1223 x_retcode := 2;
1224 x_any_data_exists := true; -- VPANWAR changed for pre upgrade test
1225
1226 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
1227
1228 IF (SQLCODE <> -20001) THEN
1229 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
1230 RAISE;
1231 ELSE
1232 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
1233 APP_EXCEPTION.RAISE_EXCEPTION;
1234 END IF;
1235 END check_preupgrade_data;
1236
1237 -- bug bug 6328168 get the amounts without Tax
1238
1239 FUNCTION get_tld_amt_remaining_WOTAX( p_tld_id IN NUMBER ) RETURN NUMBER IS
1240 CURSOR cust_trx_csr(p_interface_line_attribute14 VARCHAR2) IS
1241 SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
1242 FROM RA_CUSTOMER_TRX_LINES_ALL
1243 WHERE INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
1244 AND INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';
1245
1246 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
1247 SELECT COUNT(1) LINE_COUNT
1248 FROM RA_CUSTOMER_TRX_LINES_ALL
1249 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
1250 AND LINE_TYPE = 'LINE';
1251
1252 l_customer_trx_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID%TYPE;
1253 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
1254 l_line_count NUMBER;
1255 l_amount_remaining NUMBER := 0;
1256 BEGIN
1257 NULL;
1258 FOR r IN cust_trx_csr(p_tld_id)
1259 LOOP
1260 l_customer_trx_id := r.CUSTOMER_TRX_ID;
1261 l_customer_trx_line_id := r.CUSTOMER_TRX_LINE_ID;
1262 END LOOP;
1263
1264 FOR r IN line_count_csr(l_customer_trx_id)
1265 LOOP
1266 l_line_count := r.line_count;
1267 END LOOP;
1268
1269 IF (l_line_count > 1) THEN
1270 -- New style (R12B) invoice
1271 l_amount_remaining := INV_LN_AMT_REMAINING_WOTAX(
1272 l_customer_trx_id, l_customer_trx_line_id);
1273 ELSIF (l_line_count = 1) THEN
1274 -- Old style invoice
1275 l_amount_remaining := INV_AMT_REMAINING_WOTAX(l_customer_trx_id);
1276 END IF;
1277
1278 RETURN (l_amount_remaining);
1279
1280 END;
1281
1282 FUNCTION INV_LN_AMT_REMAINING_WOTAX(
1283 p_customer_trx_id IN NUMBER,
1284 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1285 l_invoice_line_amount_orig NUMBER;
1286 l_invoice_line_amount_applied NUMBER;
1287 l_invoice_line_amount_credited NUMBER;
1288 l_invoice_line_amt_remaining NUMBER;
1289 --Bug# 7720775
1290 l_invoice_line_amount_adjusted NUMBER;
1291 BEGIN
1292 l_invoice_line_amount_orig := INV_LN_AMT_ORIG_WOTAX(
1293 p_customer_trx_id,
1294 p_customer_trx_line_id);
1295
1296 l_invoice_line_amount_applied := INV_LN_AMT_APPLIED_WOTAX(
1297 p_customer_trx_id,
1298 p_customer_trx_line_id);
1299
1300 l_invoice_line_amount_credited := INV_LN_AMT_CREDITED_WOTAX(
1301 p_customer_trx_id,
1302 p_customer_trx_line_id);
1303
1304 --Bug# 7720775
1305 l_invoice_line_amount_adjusted := INV_LN_AMT_ADJUSTED_WOTAX(
1306 p_customer_trx_id,
1307 p_customer_trx_line_id);
1308
1309 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);
1310
1311 RETURN (l_invoice_line_amt_remaining);
1312 END;
1313
1314 FUNCTION INV_AMT_REMAINING_WOTAX(
1315 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
1316 CURSOR invoice_amount_remaining_csr(p_header_id NUMBER) IS
1317 SELECT SUM(NVL(APS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING
1318 FROM AR_PAYMENT_SCHEDULES_ALL APS,
1319 RA_CUSTOMER_TRX_ALL RACTRX
1320 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
1321 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
1322 l_invoice_amount_remaining NUMBER;
1323 BEGIN
1324 FOR R IN invoice_amount_remaining_csr (p_customer_trx_id)
1325 LOOP
1326 l_invoice_amount_remaining := r.AMOUNT_DUE_REMAINING;
1327 END LOOP;
1328 RETURN (l_invoice_amount_remaining);
1329 END;
1330
1331
1332 FUNCTION INV_LN_AMT_ORIG_WOTAX(
1333 p_customer_trx_id IN NUMBER,
1334 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1335 CURSOR invoice_line_amount(p_header_id NUMBER, p_line_id NUMBER) IS
1336 SELECT NVL(EXTENDED_AMOUNT, 0) LINE_AMOUNT
1337 FROM RA_CUSTOMER_TRX_LINES_ALL
1338 WHERE CUSTOMER_TRX_ID = p_header_id
1339 AND CUSTOMER_TRX_LINE_ID = p_line_id;
1340
1341 l_line_amount NUMBER;
1342 l_line_original_due NUMBER;
1343
1344 BEGIN
1345
1346 FOR r IN invoice_line_amount(p_customer_trx_id, p_customer_trx_line_id)
1347 LOOP
1348 l_line_amount := r.LINE_AMOUNT;
1349 END LOOP;
1350
1351 l_line_original_due := NVL(l_line_amount,0);
1352 return (l_line_original_due);
1353 END;
1354
1355
1356 FUNCTION INV_LN_AMT_CREDITED_WOTAX(
1357 p_customer_trx_id IN NUMBER,
1358 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1359 CURSOR line_credited_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1360 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
1361 FROM ar_receivable_applications_all app,
1362 ar_payment_schedules_all sch,
1363 ar_distributions_all ad
1364 WHERE app.status = 'APP'
1365 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1366 AND sch.class = 'INV'
1367 AND sch.customer_trx_id = p_header_id
1368 AND app.application_type = 'CM'
1369 AND app.receivable_application_id = ad.source_id
1370 AND ad.source_table = 'RA'
1371 AND ad.ref_Customer_trx_Line_Id = p_line_id;
1372
1373 l_line_amount_credited NUMBER;
1374 BEGIN
1375 FOR r in line_credited_csr(p_customer_trx_id, p_customer_trx_line_id)
1376 LOOP
1377 l_line_amount_credited := r.credit_applied;
1378 END LOOP;
1379 RETURN(nvl(l_line_amount_credited,0));
1380 END;
1381
1382 FUNCTION INV_LN_AMT_APPLIED_WOTAX(
1383 p_customer_trx_id IN NUMBER,
1384 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1385 --dkagrawa modified the cusrsor to add class CM
1386 CURSOR line_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1387 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) line_applied
1388 FROM ar_receivable_applications_all app,
1389 ar_payment_schedules_all sch,
1390 ar_distributions_all ad
1391 WHERE app.status = 'APP'
1392 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1393 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
1394 AND sch.customer_trx_id = p_header_id
1395 AND app.application_type = 'CASH'
1396 AND app.receivable_application_id = ad.source_id
1397 AND ad.source_table = 'RA'
1398 AND ad.ref_Customer_trx_Line_Id = p_line_id;
1399 --dkagrawa modified the cusrsor to add class CM
1400
1401 l_line_amount_applied NUMBER;
1402 BEGIN
1403 FOR r in line_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
1404 LOOP
1405 l_line_amount_applied := r.line_applied;
1406 END LOOP;
1407 RETURN(nvl(l_line_amount_applied,0));
1408 END;
1409
1410
1411
1412 FUNCTION INVOICE_AMOUNT_ADJUSTED(
1413 p_customer_trx_id IN NUMBER) RETURN NUMBER IS
1414 CURSOR invoice_amount_adj_csr(p_header_id NUMBER) IS
1415 SELECT SUM(NVL(APS.AMOUNT_ADJUSTED,0)) AMOUNT_ADJUSTED
1416 FROM AR_PAYMENT_SCHEDULES_ALL APS,
1417 RA_CUSTOMER_TRX_ALL RACTRX
1418 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
1419 AND RACTRX.CUSTOMER_TRX_ID = p_header_id;
1420 l_invoice_amount_adj NUMBER;
1421 BEGIN
1422 FOR R IN invoice_amount_adj_csr (p_customer_trx_id)
1423 LOOP
1424 l_invoice_amount_adj := r.AMOUNT_ADJUSTED;
1425 END LOOP;
1426 RETURN (l_invoice_amount_adj);
1427 END INVOICE_AMOUNT_ADJUSTED;
1428
1429 FUNCTION INVOICE_LINE_AMOUNT_ADJUSTED(
1430 p_customer_trx_id IN NUMBER,
1431 p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
1432
1433 CURSOR invoice_ln_tax_adj(p_header_id NUMBER, p_line_id NUMBER) IS
1434 SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) tax_adjusted
1435 FROM ar_adjustments_all adj,
1436 ar_payment_schedules_all sch,
1437 ar_distributions_all dist,
1438 ra_customer_trx_lines_all lines
1439 WHERE adj.payment_schedule_id = sch.payment_schedule_id
1440 AND sch.class = 'INV'
1441 AND sch.customer_trx_id = p_header_id
1442 AND adj.ADJUSTMENT_ID = dist.source_id
1443 AND dist.source_table = 'ADJ'
1444 AND dist.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1445 AND lines.link_to_cust_trx_line_id = p_line_id
1446 AND lines.line_type = 'TAX';
1447
1448 l_invoice_ln_amount_adj NUMBER;
1449 l_invoice_ln_tax_adj NUMBER;
1450
1451 --Bug# 9116332
1452 CURSOR line_count_csr(p_customer_trx_id NUMBER) IS
1453 SELECT COUNT(1) LINE_COUNT
1454 FROM RA_CUSTOMER_TRX_LINES_ALL
1455 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
1456 AND LINE_TYPE = 'LINE';
1457
1458 l_line_count NUMBER;
1459 l_amount_adjusted NUMBER;
1460 --Bug# 9116332
1461
1462 BEGIN
1463
1464 --Bug# 9116332
1465 FOR r IN line_count_csr(p_customer_trx_id)
1466 LOOP
1467 l_line_count := r.line_count;
1468 END LOOP;
1469
1470 IF (l_line_count > 1) THEN
1471 -- New style (R12B) invoice
1472
1473 --Bug# 7720775
1474 l_invoice_ln_amount_adj := INV_LN_AMT_ADJUSTED_WOTAX(
1475 p_customer_trx_id,
1476 p_customer_trx_line_id);
1477
1478 FOR R IN invoice_ln_tax_adj (p_customer_trx_id,p_customer_trx_line_id)
1479 LOOP
1480 l_invoice_ln_tax_adj := r.tax_adjusted;
1481 END LOOP;
1482
1483 l_amount_adjusted := NVL(l_invoice_ln_amount_adj,0)+NVL(l_invoice_ln_tax_adj,0);
1484
1485 ELSIF (l_line_count = 1) THEN
1486 -- Old style invoice
1487 l_amount_adjusted := invoice_amount_adjusted(p_customer_trx_id);
1488
1489 END IF;
1490 --Bug# 9116332
1491
1492 RETURN (l_amount_adjusted);
1493
1494 END INVOICE_LINE_AMOUNT_ADJUSTED;
1495
1496 --Bug# 7720775
1497 FUNCTION INV_LN_AMT_ADJUSTED_WOTAX(
1498 p_customer_trx_id IN NUMBER,
1499 p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
1500
1501 CURSOR invoice_ln_amount_adj(p_header_id NUMBER, p_line_id NUMBER) IS
1502 SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) amt_adjsuted
1503 FROM ar_distributions_all dist ,
1504 ar_adjustments_all adj ,
1505 ar_payment_schedules_all aps
1506 WHERE dist.source_table = 'ADJ'
1507 AND dist.source_id = adj.adjustment_id
1508 AND aps.customer_trx_id = p_header_id
1509 AND adj.payment_schedule_id = aps.payment_schedule_id
1510 AND aps.class = 'INV'
1511 AND ref_customer_trx_line_id = p_line_id;
1512
1513 l_invoice_ln_amount_adj NUMBER;
1514 BEGIN
1515 FOR R IN invoice_ln_amount_adj (p_customer_trx_id,p_customer_trx_line_id)
1516 LOOP
1517 l_invoice_ln_amount_adj := r.amt_adjsuted;
1518 END LOOP;
1519
1520 RETURN NVL(l_invoice_ln_amount_adj,0);
1521
1522 END INV_LN_AMT_ADJUSTED_WOTAX;
1523
1524 --Bug# 7720775
1525 -- Functions to return Invoice Line Amount with Inclusive Invoice Tax Line Amounts
1526 FUNCTION INV_LN_AMT_ORIG_W_INCTAX(
1527 p_customer_trx_id IN NUMBER,
1528 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1529
1530 CURSOR invoice_line_tax_amount(p_line_id NUMBER) IS
1531 SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
1532 FROM RA_CUSTOMER_TRX_LINES_ALL
1533 WHERE LINK_TO_CUST_TRX_LINE_ID = p_line_id
1534 AND AMOUNT_INCLUDES_TAX_FLAG = 'Y';
1535
1536 l_line_amount NUMBER;
1537 l_line_tax_amount NUMBER;
1538 l_line_original_due NUMBER;
1539
1540 BEGIN
1541
1542 l_line_amount := INV_LN_AMT_ORIG_WOTAX(p_customer_trx_id,
1543 p_customer_trx_line_id);
1544
1545 FOR r IN invoice_line_tax_amount(p_customer_trx_line_id)
1546 LOOP
1547 l_line_tax_amount := r.LINE_TAX_AMOUNT;
1548 END LOOP;
1549
1550 l_line_original_due := NVL(NVL(l_line_amount,0) + NVL(l_line_tax_amount,0),0);
1551 return (l_line_original_due);
1552 END;
1553
1554 FUNCTION INV_LN_AMT_APPLIED_W_INCTAX(
1555 p_customer_trx_id IN NUMBER,
1556 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1557
1558 CURSOR tax_applied_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1559 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
1560 FROM ar_receivable_applications_all app,
1561 ar_payment_schedules_all sch,
1562 ar_distributions_all ad,
1563 ra_customer_trx_lines_all lines
1564 WHERE app.status = 'APP'
1565 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1566 AND sch.class IN ('INV','CM') --Receipt can be applied against credit memo
1567 AND sch.customer_trx_id = p_header_id
1568 AND app.application_type = 'CASH'
1569 AND app.receivable_application_id = ad.source_id
1570 AND ad.source_table = 'RA'
1571 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1572 AND lines.link_to_cust_trx_line_id = p_line_id
1573 AND lines.line_type = 'TAX'
1574 AND lines.amount_includes_tax_flag = 'Y';
1575
1576 l_line_amount_applied NUMBER;
1577 l_tax_amount_applied NUMBER;
1578 BEGIN
1579
1580 l_line_amount_applied := INV_LN_AMT_APPLIED_WOTAX(p_customer_trx_id,
1581 p_customer_trx_line_id);
1582
1583 FOR r in tax_applied_csr(p_customer_trx_id, p_customer_trx_line_id)
1584 LOOP
1585 l_tax_amount_applied := r.tax_applied;
1586 END LOOP;
1587 RETURN(nvl(l_line_amount_applied,0) + NVL(l_tax_amount_applied,0));
1588 END;
1589
1590 FUNCTION INV_LN_AMT_CREDITED_W_INCTAX(
1591 p_customer_trx_id IN NUMBER,
1592 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1593
1594 CURSOR tax_credited_csr(p_header_id NUMBER, p_line_id NUMBER) IS
1595 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
1596 FROM ar_receivable_applications_all app,
1597 ar_payment_schedules_all sch,
1598 ar_distributions_all ad,
1599 ra_customer_trx_lines_all lines
1600 WHERE app.status = 'APP'
1601 AND app.applied_payment_schedule_id = sch.payment_schedule_id
1602 AND sch.class = 'INV'
1603 AND sch.customer_trx_id = p_header_id
1604 AND app.application_type = 'CM'
1605 AND app.receivable_application_id = ad.source_id
1606 AND ad.source_table = 'RA'
1607 AND ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1608 AND lines.link_to_cust_trx_line_id = p_line_id
1609 AND lines.line_type = 'TAX'
1610 AND lines.amount_includes_tax_flag = 'Y';
1611
1612 l_line_amount_credited NUMBER;
1613 l_tax_amount_credited NUMBER;
1614 BEGIN
1615
1616 l_line_amount_credited := INV_LN_AMT_CREDITED_WOTAX(
1617 p_customer_trx_id,
1618 p_customer_trx_line_id);
1619
1620 FOR r in tax_credited_csr(p_customer_trx_id, p_customer_trx_line_id)
1621 LOOP
1622 l_tax_amount_credited := r.credit_applied;
1623 END LOOP;
1624 RETURN(nvl(l_line_amount_credited,0) + NVL(l_tax_amount_credited,0));
1625 END;
1626
1627 FUNCTION INV_LN_AMT_ADJUSTED_W_INCTAX(
1628 p_customer_trx_id IN NUMBER,
1629 p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
1630
1631 CURSOR invoice_ln_tax_adj(p_header_id NUMBER, p_line_id NUMBER) IS
1632 SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) tax_adjusted
1633 FROM ar_adjustments_all adj,
1634 ar_payment_schedules_all sch,
1635 ar_distributions_all dist,
1636 ra_customer_trx_lines_all lines
1637 WHERE adj.payment_schedule_id = sch.payment_schedule_id
1638 AND sch.class = 'INV'
1639 AND sch.customer_trx_id = p_header_id
1640 AND adj.ADJUSTMENT_ID = dist.source_id
1641 AND dist.source_table = 'ADJ'
1642 AND dist.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
1643 AND lines.link_to_cust_trx_line_id = p_line_id
1644 AND lines.line_type = 'TAX'
1645 AND lines.amount_includes_tax_flag = 'Y';
1646
1647 l_invoice_ln_amount_adj NUMBER;
1648 l_invoice_ln_tax_adj NUMBER;
1649 BEGIN
1650
1651 l_invoice_ln_amount_adj := INV_LN_AMT_ADJUSTED_WOTAX(
1652 p_customer_trx_id,
1653 p_customer_trx_line_id);
1654
1655 FOR R IN invoice_ln_tax_adj (p_customer_trx_id,p_customer_trx_line_id)
1656 LOOP
1657 l_invoice_ln_tax_adj := r.tax_adjusted;
1658 END LOOP;
1659
1660 RETURN (NVL(l_invoice_ln_amount_adj,0)+NVL(l_invoice_ln_tax_adj,0));
1661
1662 END INV_LN_AMT_ADJUSTED_W_INCTAX;
1663
1664 FUNCTION INV_LN_AMT_REMAINING_W_INCTAX(
1665 p_customer_trx_id IN NUMBER,
1666 p_customer_trx_line_id NUMBER) RETURN NUMBER IS
1667 l_invoice_line_amount_orig NUMBER;
1668 l_invoice_line_amount_applied NUMBER;
1669 l_invoice_line_amount_credited NUMBER;
1670 l_invoice_line_amt_remaining NUMBER;
1671 l_invoice_line_amount_adjusted NUMBER;
1672 BEGIN
1673 l_invoice_line_amount_orig := INV_LN_AMT_ORIG_W_INCTAX(
1674 p_customer_trx_id,
1675 p_customer_trx_line_id);
1676
1677 l_invoice_line_amount_applied := INV_LN_AMT_APPLIED_W_INCTAX(
1678 p_customer_trx_id,
1679 p_customer_trx_line_id);
1680
1681 l_invoice_line_amount_credited := INV_LN_AMT_CREDITED_W_INCTAX(
1682 p_customer_trx_id,
1683 p_customer_trx_line_id);
1684
1685 l_invoice_line_amount_adjusted := INV_LN_AMT_ADJUSTED_W_INCTAX(
1686 p_customer_trx_id,
1687 p_customer_trx_line_id);
1688
1689 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);
1690
1691 RETURN (l_invoice_line_amt_remaining);
1692 END;
1693
1694
1695 --Added by bkatraga for bug 12378106
1696 PROCEDURE CR_INVOICE_AMOUNT_FOR_STREAM(
1697 p_api_version IN NUMBER
1698 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1699 ,x_return_status OUT NOCOPY VARCHAR2
1700 ,x_msg_count OUT NOCOPY NUMBER
1701 ,x_msg_data OUT NOCOPY VARCHAR2
1702 ,p_stream_purpose IN VARCHAR2
1703 ,p_cr_contract_id IN NUMBER
1704 ,x_tot_inv_line_amount OUT NOCOPY NUMBER
1705 )
1706 IS
1707
1708 CURSOR c_strm_invoice_amt IS
1709 SELECT SUM(ractrl.amount_due_original) amount
1710 FROM ra_customer_trx_lines_all ractrl
1711 WHERE ractrl.interface_line_attribute6 IN
1712 (select chr.contract_number
1713 from okc_k_headers_b chr,
1714 okc_governances gv,
1715 okc_k_headers_b crchr
1716 where crchr.id = p_cr_contract_id
1717 and crchr.sts_code = 'ACTIVE'
1718 and crchr.scs_code = 'CREDITLINE_CONTRACT'
1719 and gv.chr_id_referred = crchr.id
1720 and gv.dnz_chr_id = chr.id
1721 and chr.scs_code = 'LEASE'
1722 UNION
1723 select chr.contract_number
1724 from okc_k_headers_b mla_chr,
1725 okc_governances mla_g1,
1726 okc_k_headers_b chr
1727 where mla_chr.id = mla_g1.chr_id_referred
1728 and mla_chr.scs_code = 'MASTER_LEASE'
1729 and mla_chr.id IN
1730 (
1731 select cl_gl.dnz_chr_id
1732 from okc_k_headers_b cl_chr,
1733 okc_governances cl_gl
1734 where cl_chr.id = p_cr_contract_id
1735 and cl_chr.sts_code = 'ACTIVE'
1736 and cl_chr.scs_code = 'CREDITLINE_CONTRACT'
1737 and cl_chr.id = cl_gl.chr_id_referred
1738 )
1739 and chr.id = mla_g1.dnz_chr_id
1740 and chr.scs_code = 'LEASE'
1741 )
1742 AND EXISTS(SELECT sty.code
1743 FROM OKL_STRM_TYPE_B sty
1744 WHERE sty.stream_type_purpose = p_stream_purpose
1745 AND sty.code = ractrl.interface_line_attribute9);
1746
1747
1748 l_api_name VARCHAR2(60) := 'Credit Line Invoice Amount for Stream';
1749
1750 BEGIN
1751 OPEN c_strm_invoice_amt;
1752 FETCH c_strm_invoice_amt into x_tot_inv_line_amount;
1753 CLOSE c_strm_invoice_amt;
1754
1755 x_tot_inv_line_amount := NVL(x_tot_inv_line_amount, 0);
1756 x_return_status := 'S';
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1760 p_api_name => l_api_name,
1761 p_pkg_name => G_PKG_NAME,
1762 p_exc_name => 'OTHERS',
1763 x_msg_count => x_msg_count,
1764 x_msg_data => x_msg_data,
1765 p_api_type => '_PVT');
1766
1767 END CR_INVOICE_AMOUNT_FOR_STREAM;
1768 --end bkatraga for bug 12378106
1769
1770
1771 END OKL_BILLING_UTIL_PVT;