[Home] [Help]
PACKAGE BODY: APPS.OKL_BILLING_RECON_RPT_PVT
Source
1 PACKAGE BODY OKL_BILLING_RECON_RPT_PVT AS
2 /* $Header: OKLRBREB.pls 120.8.12010000.3 2009/06/03 04:17:58 racheruv ship $ */
3
4 ------------------------------------------------------------------
5 -- Procedure recon_report to print status of transaction in OKL
6 -- and AR for reconciliation purposes
7 ------------------------------------------------------------------
8 PROCEDURE recon_report
9 (p_api_version IN NUMBER
10 ,p_init_msg_list IN VARCHAR2
11 ,x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2
14 ,p_contract_number IN VARCHAR2
15 ,p_from_bill_date IN DATE
16 ,p_to_bill_date IN DATE) IS
17
18 -- ----------------------------------------------------------
19 -- Declare variables required by APIs
20 -- ----------------------------------------------------------
21 l_api_version CONSTANT NUMBER := 1;
22 l_api_name CONSTANT VARCHAR2(30) := 'OKL_BILLING_RECON_RPT_PVT';
23 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
24
25 -- ----------------------------------------------------------
26 -- Record Type for reconciliation report format
27 -- ----------------------------------------------------------
28 TYPE recon_fmt_rec_type IS RECORD (
29 CONTRACT_NUMBER okl_k_headers_full_v.contract_number%TYPE,
30 currency_code fnd_currencies_vl.currency_code%TYPE,
31 okl_count NUMBER,
32 okl_amount NUMBER,
33 ar_count NUMBER,
34 ar_amount NUMBER,
35 int_count NUMBER,
36 int_amount NUMBER
37 );
38
39 -- --------------------------------------------------------
40 -- Record Table Type for reconciliation report format
41 -- --------------------------------------------------------
42 TYPE recon_fmt_tbl_type IS TABLE OF recon_fmt_rec_type
43 INDEX BY BINARY_INTEGER;
44
45 recon_tbl recon_fmt_tbl_type;
46 l_init_recon_tbl recon_fmt_tbl_type;
47 l_tab_index NUMBER;
48
49 -- --------------------------------------------
50 -- OKL Reconciliation Invoice Summary
51 -- --------------------------------------------
52 CURSOR okl_summary_csr ( p_in_contract_number VARCHAR2 )IS
53 SELECT sum(cnt) Number_of_invoices
54 ,sum(invoice_amount) Value
55 ,currency_code
56 FROM
57 (SELECT
58 COUNT(*) cnt,
59 SUM (NVL(xls.amount,0)) invoice_amount,
60 xsi.currency_code
61 FROM
62 okl_trx_ar_invoices_v tai,
63 okl_txl_ar_inv_lns_v til,
64 okl_txd_ar_ln_dtls_v tld,
65 okl_ext_sell_invs_v xsi,
66 okl_xtl_sell_invs_v xls,
67 okl_k_headers_full_v khr
68 WHERE tai.id = til.tai_id
69 AND til.id = tld.til_id_details
70 AND xsi.id = xls.xsi_id_details
71 AND xls.tld_id = tld.id
72 AND tai.trx_status_code <> 'ERROR'
73 AND tai.khr_id = khr.id
74 AND khr.contract_number = NVL(p_in_contract_number, khr.contract_number)
75 GROUP BY xsi.currency_code)
76 GROUP BY currency_code;
77
78 CURSOR okl_wo_summary_csr ( p_in_contract_number VARCHAR2, p_currency_code VARCHAR2 )IS
79 SELECT sum(cnt) Number_of_invoices
80 ,sum(invoice_amount) Value
81 ,currency_code
82 FROM
83 (SELECT
84 COUNT(*) cnt,
85 SUM (NVL(xls.amount,0)) invoice_amount,
86 xsi.currency_code
87 FROM okl_ext_sell_invs_v xsi,
88 okl_xtl_sell_invs_v xls,
89 okl_trx_ar_invoices_v tai,
90 okl_txl_ar_inv_lns_v til,
91 okl_k_headers_full_v khr
92 WHERE tai.id = til.tai_id
93 AND xsi.id = xls.xsi_id_details
94 AND xls.til_id = til.id
95 AND xsi.currency_code = p_currency_code
96 AND tai.trx_status_code <> 'ERROR'
97 AND tai.khr_id = khr.id
98 AND khr.contract_number = NVL(p_in_contract_number, khr.contract_number)
99 GROUP BY xsi.currency_code
100 )
101 GROUP BY currency_code;
102
103 -- --------------------------------------------
104 -- AR Reconciliation Invoice Summary
105 -- --------------------------------------------
106 CURSOR ar_summary_csr( p_in_contract_number VARCHAR2, p_currency_code VARCHAR2) IS
107 SELECT
108 count(*) cnt,
109 SUM(NVL(line.EXTENDED_AMOUNT,0)) Invoice_Amount,
110 hdr.invoice_currency_code
111 FROM ra_customer_trx_all hdr,
112 ra_customer_trx_lines_all line,
113 ra_batch_sources_all batch,
114 RA_CUST_TRX_TYPES_ALL trx_type
115 WHERE line.customer_trx_id = hdr.customer_trx_id
116 AND hdr.batch_source_id = batch.batch_source_id
117 AND batch.name = 'OKL_CONTRACTS'
118 AND line.line_type = 'LINE'
119 AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
120 AND hdr.invoice_currency_code = p_currency_code
121 AND hdr.interface_header_attribute6
122 = NVL(p_in_contract_number,hdr.interface_header_attribute6)
123 GROUP BY
124 hdr.invoice_currency_code
125 ORDER BY 3;
126
127 -- --------------------------------------------
128 -- Interface Reconciliation Summary
129 -- --------------------------------------------
130 CURSOR intf_summary_csr( p_in_contract_number VARCHAR2, p_currency_code VARCHAR2) IS
131 SELECT count(*) cnt,
132 SUM(AMOUNT) Invoice_amt,
133 hdr.currency_code
134 FROM ra_interface_lines_all hdr,
135 RA_CUST_TRX_TYPES_ALL trx_type
136 WHERE hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
137 AND hdr.currency_code = p_currency_code
138 AND hdr.batch_source_name = 'OKL_CONTRACTS'
139 AND hdr.interface_line_attribute6
140 = NVL(p_in_contract_number,hdr.interface_line_attribute6)
141 GROUP BY
142 hdr.currency_code
143 ORDER BY 3;
144
145 ------------------------------------------------------------
146 -- Contract Number Cursor
147 ------------------------------------------------------------
148 CURSOR khr_csr ( p_contract_number VARCHAR2 ) IS
149 SELECT *
150 FROM OKL_K_HEADERS_FULL_V
151 WHERE contract_number = NVL( p_contract_number, contract_number );
152
153 ------------------------------------------------------------
154 -- OKL Generated Invoices
155 ------------------------------------------------------------
156 CURSOR okl_invs_csr( p_in_contract_number VARCHAR2
157 ,p_in_from_bill_date DATE
158 ,p_in_to_bill_date DATE) IS
159 SELECT CURRENCY_CODE
160 ,XTRX_CONTRACT
161 --,TAI
162 --,TRX_STATUS_CODE
163 ,sum(cnt) Number_of_invoices
164 ,sum(amt) Value
165 FROM
166 (SELECT
167 COUNT(*) cnt,
168 SUM (NVL(xls.amount,0)) amt,
169 xsi.currency_code,
170 xls.xtrx_contract,
171 tai.trx_status_code tai,
172 xsi.trx_status_code
173 FROM
174 okl_trx_ar_invoices_v tai,
175 okl_txl_ar_inv_lns_v til,
176 okl_txd_ar_ln_dtls_v tld,
177 okl_ext_sell_invs_v xsi,
178 okl_xtl_sell_invs_v xls
179 WHERE tai.id = til.tai_id
180 AND til.id = tld.til_id_details
181 AND xsi.id = xls.xsi_id_details
182 AND xls.tld_id = tld.id
183 AND tai.trx_status_code <> 'ERROR'
184 AND xls.xtrx_contract = NVL ( p_in_contract_number, xls.xtrx_contract )
185 AND xsi.trx_date >= NVL( p_in_from_bill_date, xsi.trx_date )
186 AND xsi.trx_date <= NVL( p_in_to_bill_date, xsi.trx_date )
187 GROUP BY
188 xsi.currency_code,
189 xls.xtrx_contract,
190 xsi.trx_status_code,
191 tai.trx_status_code)
192 GROUP BY currency_code,XTRX_CONTRACT;
193
194 CURSOR okl_wo_invs_csr( p_in_contract_number VARCHAR2
195 ,p_in_from_bill_date DATE
196 ,p_in_to_bill_date DATE
197 ,p_currency VARCHAR2) IS
198 SELECT CURRENCY_CODE
199 ,XTRX_CONTRACT
200 --,TAI
201 --,TRX_STATUS_CODE
202 ,sum(cnt) Number_of_invoices
203 ,sum(amt) Value
204 FROM
205 ( SELECT
206 COUNT(*) cnt,
207 SUM (NVL(xls.amount,0)) amt,
208 xsi.currency_code,
209 xls.xtrx_contract,
210 tai.trx_status_code tai,
211 xsi.trx_status_code
212 FROM okl_ext_sell_invs_v xsi,
213 okl_xtl_sell_invs_v xls,
214 okl_trx_ar_invoices_v tai,
215 okl_txl_ar_inv_lns_v til
216 WHERE tai.id = til.tai_id
217 AND xsi.id = xls.xsi_id_details
218 AND xsi.currency_code = p_currency
219 AND xls.til_id = til.id
220 AND tai.trx_status_code <> 'ERROR'
221 AND xls.xtrx_contract = NVL ( p_in_contract_number, xls.xtrx_contract )
222 AND xsi.trx_date >= NVL( p_in_from_bill_date, xsi.trx_date )
223 AND xsi.trx_date <= NVL( p_in_to_bill_date, xsi.trx_date )
224 GROUP BY
225 xsi.currency_code,
226 xls.xtrx_contract,
227 xsi.trx_status_code,
228 tai.trx_status_code)
229 GROUP BY currency_code,XTRX_CONTRACT;
230
231 -- ------------------------------------------
232 -- Created in AR
233 -- ------------------------------------------
234 CURSOR ar_invs_csr( p_in_contract_number VARCHAR2
235 ,p_in_from_bill_date DATE
236 ,p_in_to_bill_date DATE
237 ,p_currency VARCHAR2) IS
238 SELECT
239 count(*) cnt,
240 SUM(NVL(line.EXTENDED_AMOUNT,0)) Invoice_Amount,
241 hdr.invoice_currency_code,
242 hdr.INTERFACE_HEADER_ATTRIBUTE6 Contract_Number--,
243 -- decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo') Invoice_Type
244 FROM ra_customer_trx_all hdr,
245 ra_customer_trx_lines_all line,
246 ra_batch_sources_all batch,
247 RA_CUST_TRX_TYPES_ALL trx_type
248 WHERE line.customer_trx_id = hdr.customer_trx_id
249 AND hdr.batch_source_id = batch.batch_source_id
250 AND batch.name = 'OKL_CONTRACTS'
251 AND line.line_type = 'LINE'
252 AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
253 AND hdr.INTERFACE_HEADER_ATTRIBUTE6 =
254 NVL(rtrim(ltrim( p_in_contract_number)),hdr.INTERFACE_HEADER_ATTRIBUTE6)
255 AND hdr.trx_date >= NVL( p_in_from_bill_date , hdr.trx_date )
256 AND hdr.trx_date <= NVL( p_in_to_bill_date , hdr.trx_date)
257 AND hdr.invoice_currency_code = p_currency
258 GROUP BY
259 hdr.invoice_currency_code,
260 hdr.INTERFACE_HEADER_ATTRIBUTE6--,
261 --decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo')
262 ORDER BY 3;
263
264 -- ---------------------------------------------
265 -- Unprocessed, stuck in ra_interface_lines_all
266 -- ---------------------------------------------
267 CURSOR ar_interface_invs_csr( p_in_contract_number VARCHAR2
268 ,p_in_from_bill_date DATE
269 ,p_in_to_bill_date DATE
270 ,p_currency VARCHAR2) IS
271 SELECT count(*) cnt,
272 SUM(AMOUNT) Invoice_amt,
273 hdr.currency_code,
274 interface_line_attribute6--,
275 --decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo') Invoice_Type
276 FROM ra_interface_lines_all hdr,
277 RA_CUST_TRX_TYPES_ALL trx_type
278 WHERE hdr.batch_source_name = 'OKL_CONTRACTS'
279 AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
280 AND hdr.INTERFACE_LINE_ATTRIBUTE6 =
281 NVL(rtrim(ltrim( p_in_contract_number)),hdr.INTERFACE_LINE_ATTRIBUTE6)
282 AND hdr.trx_date >= NVL( p_in_from_bill_date , hdr.trx_date )
283 AND hdr.trx_date <= NVL( p_in_to_bill_date , hdr.trx_date )
284 AND hdr.currency_code = p_currency
285 GROUP BY
286 hdr.currency_code,
287 hdr.interface_line_attribute6--,
288 --decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo')
289 ORDER BY 3;
290
291 ------------------------------------------------------
292 -- Local Variables
293 ------------------------------------------------------
294 l_okl_currency_code okl_k_headers_full_v.currency_code%TYPE;
295 l_okl_contract okl_k_headers_full_v.contract_number%TYPE;
296 l_okl_invoice_count NUMBER;
297 l_okl_invoice_amount NUMBER;
298
299 l_okl_wo_currency_code okl_k_headers_full_v.currency_code%TYPE;
300 l_okl_wo_contract okl_k_headers_full_v.contract_number%TYPE;
301 l_okl_wo_invoice_count NUMBER;
302 l_okl_wo_invoice_amount NUMBER;
303
304 l_ar_currency_code okl_k_headers_full_v.currency_code%TYPE;
305 l_ar_contract okl_k_headers_full_v.contract_number%TYPE;
306 l_ar_invoice_count NUMBER;
307 l_ar_invoice_amount NUMBER;
308
309 l_int_currency_code okl_k_headers_full_v.currency_code%TYPE;
310 l_int_contract okl_k_headers_full_v.contract_number%TYPE;
311 l_int_invoice_count NUMBER;
312 l_int_invoice_amount NUMBER;
313
314 l_cnt_diff NUMBER;
315 l_amt_diff NUMBER;
316
317 l_output_var VARCHAR2(2000);
318 l_no_precision_format VARCHAR2(25) := '99,999,999,999';
319 l_two_precision_format VARCHAR2(25) := '99,999,999,990.99';
320 l_three_precision_format VARCHAR2(25) := '999,999,999,990.999';
321
322 l_number_format VARCHAR2(25) := '99,999,999,999';
323 l_amount_format VARCHAR2(25) := '999,999,999,990.999';
324
325 CURSOR curr_precision_csr( p_curr_code VARCHAR2 ) IS
326 SELECT precision
327 FROM fnd_currencies_vl
328 WHERE CURRENCY_CODE = p_curr_code;
329
330 l_precision fnd_currencies_vl.precision%TYPE;
331
332 CURSOR int_err_csr( p_in_contract_number VARCHAR2
333 ,p_in_from_bill_date DATE
334 ,p_in_to_bill_date DATE) IS
335 SELECT AMOUNT Invoice_amt,
336 hdr.currency_code,
337 interface_line_attribute6 contract_number,
338 INTERFACE_LINE_ATTRIBUTE7 asset,
339 INTERFACE_LINE_ATTRIBUTE9 stream_type,
340 decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo') Invoice_Type,
341 trx_date due_date,
342 err.MESSAGE_TEXT remarks
343 FROM ra_interface_lines_all hdr,
344 RA_CUST_TRX_TYPES_ALL trx_type,
345 ra_interface_errors_all err
346 WHERE hdr.batch_source_name = 'OKL_CONTRACTS'
347 AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
348 AND hdr.INTERFACE_LINE_ATTRIBUTE6 =
349 NVL(rtrim(ltrim( p_in_contract_number)),hdr.INTERFACE_LINE_ATTRIBUTE6)
350 AND hdr.trx_date >= NVL( p_in_from_bill_date , hdr.trx_date )
351 AND hdr.trx_date <= NVL( p_in_to_bill_date , hdr.trx_date )
352 AND hdr.interface_line_id (+) = err.interface_line_id
353 ORDER BY 3,4,5,6;
354
355 CURSOR op_unit_csr IS
356 SELECT NAME
357 FROM hr_operating_units
358 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
359
360
361 l_op_unit_name hr_operating_units.name%TYPE;
362
363 BEGIN
364
365 ------------------------------------------------------------
366 -- Start processing
367 ------------------------------------------------------------
368
369 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
370
371 l_return_status := Okl_Api.START_ACTIVITY(
372 p_api_name => l_api_name,
373 p_pkg_name => G_PKG_NAME,
374 p_init_msg_list => p_init_msg_list,
375 l_api_version => l_api_version,
376 p_api_version => p_api_version,
377 p_api_type => '_PVT',
378 x_return_status => l_return_status);
379
380 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
381 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
382 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
383 RAISE Okl_Api.G_EXCEPTION_ERROR;
384 END IF;
385
386 ---------------------------------------
387 -- Get operating unit name
388 ---------------------------------------
389 l_op_unit_name := NULL;
390 OPEN op_unit_csr;
391 FETCH op_unit_csr INTO l_op_unit_name;
392 CLOSE op_unit_csr;
393
394 l_output_var := NULL;
395 l_output_var := l_output_var||rPAD(' ', 63, ' ');
396 l_output_var := l_output_var||' Oracle Lease and Finance Management';
397 l_output_var := l_output_var||rPAD(' ', 63, ' ');
398 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
399
400 l_output_var := NULL;
401 l_output_var := l_output_var||rPAD(' ', 150, ' ');
402 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
403
404 l_output_var := NULL;
405 l_output_var := l_output_var||rPAD(' ', 60, ' ');
406 l_output_var := l_output_var||' Billing Reconciliation Report';
407 l_output_var := l_output_var||rPAD(' ', 60, ' ');
408 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
409
410 l_output_var := NULL;
411 l_output_var := l_output_var||rPAD(' ', 150, ' ');
412 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
413
414 l_output_var := NULL;
415 l_output_var := l_output_var||'Run Date: ';
416 l_output_var := l_output_var||to_char(sysdate,'DD-MON-YYYY');
417 l_output_var := l_output_var||rPAD(' ', 107, ' ');
418 l_output_var := l_output_var||'Request Id: ';
419 l_output_var := l_output_var||SUBSTR(lpad(Fnd_Global.CONC_REQUEST_ID,10,' '),1,10);
420 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
421
422 l_output_var := NULL;
423 l_output_var := l_output_var||'Operating Unit: ';
424 l_output_var := l_output_var||rpad(l_op_unit_name,134,' ');
425 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
426
427 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('-', 150, '-'));
428
429
430 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
431
432 l_output_var := NULL;
433 l_output_var := l_output_var||'Parameters ';
434 l_output_var := l_output_var||rpad(' ',139,' ');
435 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
436 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
437
438 l_output_var := NULL;
439 l_output_var := l_output_var||'From Date: ';
440 l_output_var := l_output_var||rpad(NVL(to_char(p_from_bill_date,'DD-MON-YYYY'),'Not Supplied'),12,' ');
441 l_output_var := l_output_var||rPAD(' ', 106, ' ');
442 l_output_var := l_output_var||'To Date: ';
443 l_output_var := l_output_var||rpad(NVL(to_char(p_to_bill_date,'DD-MON-YYYY'),'Not Supplied'),12,' ');
444 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
445
446 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Contract Number: '||NVL(p_contract_number,'Not Supplied'), 150, ' '));
447 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('-', 150, '-'));
448
449 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
450
451 l_output_var := NULL;
452 l_output_var := l_output_var||'Invoice Summary';
453 l_output_var := l_output_var||rPAD(' ', 135, ' ');
454 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
455 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
456
457 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Summary Currency Streams Billed Bill w/o Streams Billed in Oracle Receivables Difference', 150, ' '));
458 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('--------------- -------- in Lease and Finance Management in Lease and Finance Management Receivables Interface -------------', 150, ' '));
459 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ------------------- -------------------- ---------------- -------------- ', 150, ' '));
460
461 -- --------------------------------------
462 -- OKL Invoice Summary
463 -- --------------------------------------
464 FOR okl_summary_rec IN okl_summary_csr ( p_contract_number ) LOOP
465
466 ------------------------------------
467 -- Null out local variables
468 ------------------------------------
469 l_okl_currency_code := NULL;
470 l_okl_contract := NULL;
471 l_okl_invoice_count := NULL;
472 l_okl_invoice_amount := NULL;
473
474 l_okl_wo_invoice_count := NULL;
475 l_okl_wo_invoice_amount := NULL;
476 l_okl_wo_currency_code := NULL;
477
478 l_ar_currency_code := NULL;
479 l_ar_contract := NULL;
480 l_ar_invoice_count := NULL;
481 l_ar_invoice_amount := NULL;
482
483 l_int_currency_code := NULL;
484 l_int_contract := NULL;
485 l_int_invoice_count := NULL;
486 l_int_invoice_amount := NULL;
487
488 l_okl_currency_code := okl_summary_rec.currency_code;
489 l_okl_invoice_count := okl_summary_rec.Number_of_invoices;
490 l_okl_invoice_amount := okl_summary_rec.Value;
491
492 OPEN okl_wo_summary_csr ( p_contract_number, okl_summary_rec.currency_code);
493 FETCH okl_wo_summary_csr INTO l_okl_wo_invoice_count,
494 l_okl_wo_invoice_amount,
495 l_okl_wo_currency_code;
496 CLOSE okl_wo_summary_csr;
497
498 IF l_okl_wo_invoice_count IS NULL THEN
499 l_okl_wo_invoice_count := 0;
500 END IF;
501
502 IF l_okl_wo_invoice_amount IS NULL THEN
503 l_okl_wo_invoice_amount := 0;
504 END IF;
505
506 -- --------------------------------------
507 -- AR Invoice Summary by Currency
508 -- --------------------------------------
509 OPEN ar_summary_csr( p_contract_number, okl_summary_rec.currency_code );
510 FETCH ar_summary_csr INTO l_ar_invoice_count,
511 l_ar_invoice_amount,
512 l_ar_currency_code;
513 CLOSE ar_summary_csr;
514
515 IF l_ar_invoice_count IS NULL THEN
516 l_ar_invoice_count := 0;
517 END IF;
518
519 IF l_ar_invoice_amount IS NULL THEN
520 l_ar_invoice_amount := 0;
521 END IF;
522
523 -- --------------------------------------
524 -- Interface Invoice Summary by Currency
525 -- --------------------------------------
526 OPEN intf_summary_csr( p_contract_number, okl_summary_rec.currency_code );
527 FETCH intf_summary_csr INTO l_int_invoice_count,
528 l_int_invoice_amount,
529 l_int_currency_code;
530 CLOSE intf_summary_csr;
531
532 IF l_int_invoice_count IS NULL THEN
533 l_int_invoice_count := 0;
534 END IF;
535
536 IF l_int_invoice_amount IS NULL THEN
537 l_int_invoice_amount := 0;
538 END IF;
539
540 l_cnt_diff := l_okl_invoice_count+l_okl_wo_invoice_count-l_ar_invoice_count-l_int_invoice_count;
541 l_amt_diff := l_okl_invoice_amount+l_okl_wo_invoice_amount -l_ar_invoice_amount-l_int_invoice_amount;
542
543 -- ----------------------------------
544 -- Fetch currency precision into local
545 -- variable
546 -- ----------------------------------
547 l_precision := NULL;
548 OPEN curr_precision_csr( okl_summary_rec.currency_code );
549 FETCH curr_precision_csr INTO l_precision;
550 CLOSE curr_precision_csr;
551
552 -- ---------------------------------------
553 -- Format Variable Precision for printing
554 -- ---------------------------------------
555 IF l_precision = 0 THEN
556 l_amount_format := l_no_precision_format;
557 ELSIF l_precision > 2 THEN
558 l_amount_format := l_three_precision_format;
559 ELSE
560 l_amount_format := l_two_precision_format;
561 END IF;
562
563 l_output_var := NULL;
564
565 l_output_var := l_output_var||'Number of Invoices: ';
566 l_output_var := l_output_var||SUBSTR(l_okl_currency_code,1,8);
567 l_output_var := l_output_var||' ';
568 l_output_var := l_output_var||LPAD(to_char(l_okl_invoice_count,l_number_format),20,' ');
569 l_output_var := l_output_var||' ';
570 l_output_var := l_output_var||LPAD(to_char(l_okl_wo_invoice_count,l_number_format),20,' ');
571 -- l_output_var := l_output_var||' ';
572 l_output_var := l_output_var||LPAD(to_char(l_ar_invoice_count,l_number_format),20,' ');
573 l_output_var := l_output_var||' ';
574 l_output_var := l_output_var||LPAD(to_char(l_int_invoice_count,l_number_format),20,' ');
575 l_output_var := l_output_var||' ';
576 l_output_var := l_output_var||LPAD(to_char(l_cnt_diff,l_number_format),20,' ');
577 -- -------------------------------------------------------
578 -- Print out the Count of Invoices
579 -- -------------------------------------------------------
580 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
581
582 l_output_var := NULL;
583
584 l_output_var := l_output_var||'Value of Invoices : ';
585 l_output_var := l_output_var||SUBSTR(l_okl_currency_code,1,8);
586 l_output_var := l_output_var||' ';
587 l_output_var := l_output_var||LPAD(to_char(l_okl_invoice_amount,l_amount_format),20,' ');
588 l_output_var := l_output_var||' ';
589 l_output_var := l_output_var||LPAD(to_char(l_okl_wo_invoice_amount,l_amount_format),20,' ');
590 -- l_output_var := l_output_var||'';
591 l_output_var := l_output_var||LPAD(to_char(l_ar_invoice_amount,l_amount_format),20,' ');
592 l_output_var := l_output_var||' ';
593 l_output_var := l_output_var||LPAD(to_char(l_int_invoice_amount,l_amount_format),20,' ');
594 l_output_var := l_output_var||' ';
595 l_output_var := l_output_var||LPAD(to_char(l_amt_diff,l_amount_format),20,' ');
596
597 -- -------------------------------------------------------
598 -- Print out the Value of Invoices
599 -- -------------------------------------------------------
600 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
601
602 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
603
604 END LOOP;
605
606
607 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('-', 150, '-'));
608
609 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
610
611 l_output_var := NULL;
612 l_output_var := l_output_var||'Details of Contract having Reconciliation Difference';
613 l_output_var := l_output_var||rPAD(' ', 98, ' ');
614 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
615 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
616
617 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Contract Number Currency Streams Billed Bill w/o Streams Billed in Oracle Receivables Difference', 150, ' '));
618 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('--------------- -------- in Lease and Finance Management in Lease and Finance Management Receivables Interface -------------', 150, ' '));
619 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ------------------- -------------------- ---------------- -------------- ', 150, ' '));
620
621
622 FOR khr_rec IN khr_csr( p_contract_number ) LOOP
623
624 ----------------------------------------------
625 -- For invoices generated in OKL
626 ----------------------------------------------
627 FOR okl_invs_rec IN okl_invs_csr( khr_rec.contract_number
628 ,p_from_bill_date
629 ,p_to_bill_date) LOOP
630
631 ----------------------------------------------
632 -- Initialize Variables
633 ----------------------------------------------
634 l_okl_currency_code := NULL;
635 l_okl_contract := NULL;
636 l_okl_invoice_count := NULL;
637 l_okl_invoice_amount := NULL;
638
639 l_okl_wo_invoice_count := NULL;
640 l_okl_wo_contract := NULL;
641 l_okl_wo_invoice_amount := NULL;
642 l_okl_wo_currency_code := NULL;
643
644 l_ar_currency_code := NULL;
645 l_ar_contract := NULL;
646 l_ar_invoice_count := NULL;
647 l_ar_invoice_amount := NULL;
648
649 l_int_currency_code := NULL;
650 l_int_contract := NULL;
651 l_int_invoice_count := NULL;
652 l_int_invoice_amount := NULL;
653
654 l_okl_currency_code := okl_invs_rec.currency_code;
655 l_okl_contract := okl_invs_rec.XTRX_CONTRACT;
656 l_okl_invoice_count := okl_invs_rec.Number_of_invoices;
657 l_okl_invoice_amount := okl_invs_rec.Value;
658
659 OPEN okl_wo_invs_csr( khr_rec.contract_number
660 ,p_from_bill_date
661 ,p_to_bill_date
662 ,okl_invs_rec.currency_code);
663 FETCH okl_wo_invs_csr INTO l_okl_wo_currency_code,
664 l_okl_wo_contract,
665 l_okl_wo_invoice_count,
666 l_okl_wo_invoice_amount;
667 CLOSE okl_wo_invs_csr;
668
669 IF l_okl_wo_invoice_count IS NULL THEN
670 l_okl_wo_invoice_count := 0;
671 END IF;
672
673 IF l_okl_wo_invoice_amount IS NULL THEN
674 l_okl_wo_invoice_amount := 0;
675 END IF;
676
677 -- --------------------------------------------
678 -- For OKL invoices Created in AR
679 -- --------------------------------------------
680 OPEN ar_invs_csr( khr_rec.contract_number
681 ,p_from_bill_date
682 ,p_to_bill_date
683 ,l_okl_currency_code);
684 FETCH ar_invs_csr INTO l_ar_invoice_count,
685 l_ar_invoice_amount,
686 l_ar_currency_code,
687 l_ar_contract;
688
689 CLOSE ar_invs_csr;
690
691 IF l_ar_invoice_count IS NULL THEN
692 l_ar_invoice_count := 0;
693 END IF;
694
695 IF l_ar_invoice_amount IS NULL THEN
696 l_ar_invoice_amount := 0;
697 END IF;
698
699 -- --------------------------------------------
700 -- For Interface Records
701 -- --------------------------------------------
702 OPEN ar_interface_invs_csr( khr_rec.contract_number
703 ,p_from_bill_date
704 ,p_to_bill_date
705 ,l_okl_currency_code);
706 FETCH ar_interface_invs_csr INTO l_int_invoice_count,
707 l_int_invoice_amount,
708 l_int_currency_code,
709 l_int_contract;
710 CLOSE ar_interface_invs_csr;
711
712
713 IF l_int_invoice_count IS NULL THEN
714 l_int_invoice_count := 0;
715 END IF;
716
717 IF l_int_invoice_amount IS NULL THEN
718 l_int_invoice_amount := 0;
719 END IF;
720
721 l_cnt_diff := l_okl_invoice_count+ l_okl_wo_invoice_count-l_ar_invoice_count-l_int_invoice_count;
722 l_amt_diff := l_okl_invoice_amount+l_okl_wo_invoice_amount-l_ar_invoice_amount-l_int_invoice_amount;
723
724 -- ----------------------------------
725 -- Fetch currency precision into local
726 -- variable
727 -- ----------------------------------
728 l_precision := NULL;
729 OPEN curr_precision_csr( l_okl_currency_code );
730 FETCH curr_precision_csr INTO l_precision;
731 CLOSE curr_precision_csr;
732
733 -- ---------------------------------------
734 -- Format Variable Precision for printing
735 -- ---------------------------------------
736 IF l_precision = 0 THEN
737 l_amount_format := l_no_precision_format;
738 ELSIF l_precision > 2 THEN
739 l_amount_format := l_three_precision_format;
740 ELSE
741 l_amount_format := l_two_precision_format;
742 END IF;
743
744
745 IF (l_cnt_diff <> 0 OR l_amt_diff <> 0) THEN
746
747 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(khr_rec.contract_number||':', 150, ' '));
748
749 l_output_var := NULL;
750
751 l_output_var := l_output_var||' Number of Invoices : ';
752 l_output_var := l_output_var||SUBSTR(l_okl_currency_code,1,8);
753 l_output_var := l_output_var||' ';
754 l_output_var := l_output_var||LPAD(to_char(l_okl_invoice_count,l_number_format),20,' ');
755 l_output_var := l_output_var||' ';
756 l_output_var := l_output_var||LPAD(to_char(l_okl_wo_invoice_count,l_number_format),20,' ');
757 -- l_output_var := l_output_var||'';
758 l_output_var := l_output_var||LPAD(to_char(l_ar_invoice_count,l_number_format),20,' ');
759 l_output_var := l_output_var||' ';
760 l_output_var := l_output_var||LPAD(to_char(l_int_invoice_count,l_number_format),20,' ');
761 l_output_var := l_output_var||' ';
762 l_output_var := l_output_var||LPAD(to_char(l_cnt_diff,l_number_format),20,' ');
763 -- -------------------------------------------------------
764 -- Print out the Count of Invoices
765 -- -------------------------------------------------------
766 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
767
768 l_output_var := NULL;
769
770 l_output_var := l_output_var||' Value of Invoices : ';
771 l_output_var := l_output_var||SUBSTR(l_okl_currency_code,1,8);
772 l_output_var := l_output_var||' ';
773 l_output_var := l_output_var||LPAD(to_char(l_okl_invoice_amount,l_amount_format),20,' ');
774 l_output_var := l_output_var||' ';
775 l_output_var := l_output_var||LPAD(to_char(l_okl_wo_invoice_amount,l_amount_format),20,' ');
776 -- l_output_var := l_output_var||'';
777 l_output_var := l_output_var||LPAD(to_char(l_ar_invoice_amount,l_amount_format),20,' ');
778 l_output_var := l_output_var||' ';
779 l_output_var := l_output_var||LPAD(to_char(l_int_invoice_amount,l_amount_format),20,' ');
780 l_output_var := l_output_var||' ';
781 l_output_var := l_output_var||LPAD(to_char(l_amt_diff,l_amount_format),20,' ');
782 -- -------------------------------------------------------
783 -- Print out the Value of Invoices
784 -- -------------------------------------------------------
785 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
786
787 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
788 END IF;
789
790 END LOOP;
791
792 END LOOP;
793 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('-', 150, '-'));
794
795 l_output_var := NULL;
796 l_output_var := l_output_var||'Note: Above contracts may be reconciled by running following reports';
797 l_output_var := l_output_var||rPAD(' ', 82, ' ');
798 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
799
800 l_output_var := NULL;
801 l_output_var := l_output_var||'1) Prepare Receivables Bills';
802 l_output_var := l_output_var||rPAD(' ', 122, ' ');
803 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
804
805 l_output_var := NULL;
806 l_output_var := l_output_var||'2) Receivables Bills Consolidation';
807 l_output_var := l_output_var||rPAD(' ', 116, ' ');
808 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
809
810 l_output_var := NULL;
811 l_output_var := l_output_var||'3) Receivables Invoices Transfer to AR';
812 l_output_var := l_output_var||rPAD(' ', 112, ' ');
813 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
814
815 -- --------------------------------------------
816 -- Print Interface Errors
817 -- --------------------------------------------
818 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
819
820 l_output_var := NULL;
821 l_output_var := l_output_var||'Details of Contracts not processed from Receivables Interface';
822 l_output_var := l_output_var||rPAD(' ', 89, ' ');
823 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
824
825 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
826
827 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Contract Number Asset Invoice Date Amount Stream Type Remarks ', 150, ' '));
828 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('--------------- -------- ------------- ------------- ------------- -------------', 150, ' '));
829
830 FOR khr_rec IN khr_csr( p_contract_number ) LOOP
831
832 FOR interface_rec IN int_err_csr( khr_rec.contract_number
833 ,p_from_bill_date
834 ,p_to_bill_date ) LOOP
835
836 -- ----------------------------------
837 -- Fetch currency precision into local
838 -- variable
839 -- ----------------------------------
840 l_precision := NULL;
841 OPEN curr_precision_csr( interface_rec.currency_code );
842 FETCH curr_precision_csr INTO l_precision;
843 CLOSE curr_precision_csr;
844
845 -- ---------------------------------------
846 -- Format Variable Precision for printing
847 -- ---------------------------------------
848 IF l_precision = 0 THEN
849 l_amount_format := l_no_precision_format;
850 ELSIF l_precision > 2 THEN
851 l_amount_format := l_three_precision_format;
852 ELSE
853 l_amount_format := l_two_precision_format;
854 END IF;
855
856 l_output_var := NULL;
857
858 l_output_var := l_output_var||RPAD(SUBSTR(interface_rec.contract_number,1,25),28,' ');
859 l_output_var := l_output_var||RPAD(SUBSTR(NVL(interface_rec.asset,'None'),1,15),22,' ');
860 l_output_var := l_output_var||SUBSTR(NVL(to_char(interface_rec.due_date,'DD-MON-YYYY'),'None'),1,13);
861 l_output_var := l_output_var||LPAD(to_char(NVL(interface_rec.Invoice_amt,0),l_amount_format),26,' ');
862 l_output_var := l_output_var||' ';
863 l_output_var := l_output_var||SUBSTR(NVL(interface_rec.stream_type,'None'),1,25);
864 l_output_var := l_output_var||RPAD(' ',17,' ');
865 l_output_var := l_output_var||NVL(interface_rec.remarks,'None');
866
867 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_output_var);
868 END LOOP;
869 END LOOP;
870 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('-', 150, '-'));
871
872 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 150, ' '));
873 l_output_var := NULL;
874 l_output_var := l_output_var||'NOTE: Invoices in Receivables interfaces can be cleared by resolving errors in the interface,';
875 l_output_var := l_output_var||' if any, and running Autoinvoice program.';
876 l_output_var := l_output_var||rPAD(' ', 16, ' ');
877 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,l_output_var);
878
879
880 ------------------------------------------------------------
881 -- End processing
882 ------------------------------------------------------------
883
884 Okl_Api.END_ACTIVITY (
885 x_msg_count => x_msg_count,
886 x_msg_data => x_msg_data);
887
888 EXCEPTION
889
890 ------------------------------------------------------------
891 -- Exception handling
892 ------------------------------------------------------------
893
894 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
895 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
896 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
897 p_api_name => l_api_name,
898 p_pkg_name => G_PKG_NAME,
899 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
900 x_msg_count => x_msg_count,
901 x_msg_data => x_msg_data,
902 p_api_type => '_PVT');
903
904 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
905 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
906 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
907 p_api_name => l_api_name,
908 p_pkg_name => G_PKG_NAME,
909 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
910 x_msg_count => x_msg_count,
911 x_msg_data => x_msg_data,
912 p_api_type => '_PVT');
913
914 WHEN OTHERS THEN
915 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
916 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
917 p_api_name => l_api_name,
918 p_pkg_name => G_PKG_NAME,
919 p_exc_name => 'OTHERS',
920 x_msg_count => x_msg_count,
921 x_msg_data => x_msg_data,
922 p_api_type => '_PVT');
923
924 END recon_report;
925
926
927 PROCEDURE recon_report_conc (
928 errbuf OUT NOCOPY VARCHAR2
929 ,retcode OUT NOCOPY NUMBER
930 ,p_from_bill_date IN VARCHAR2
931 ,p_to_bill_date IN VARCHAR2
932 ,p_contract_number IN VARCHAR2) IS
933
934 l_api_version CONSTANT NUMBER := 1;
935 l_msg_count NUMBER;
936 l_return_status VARCHAR2(1):= 'S';
937
938 l_from_bill_date DATE;
939 l_to_bill_date DATE;
940
941 BEGIN
942
943 IF p_from_bill_date IS NOT NULL THEN
944 l_from_bill_date := FND_DATE.CANONICAL_TO_DATE(p_from_bill_date);
945 END IF;
946
947 IF p_to_bill_date IS NOT NULL THEN
948 l_to_bill_date := FND_DATE.CANONICAL_TO_DATE(p_to_bill_date);
949 END IF;
950
951 OKL_BILLING_RECON_RPT_PVT.recon_report(
952 p_api_version => l_api_version,
953 p_init_msg_list => Okl_Api.G_FALSE,
954 x_return_status => l_return_status,
955 x_msg_count => l_msg_count,
956 x_msg_data => errbuf,
957 p_contract_number => p_contract_number,
958 p_from_bill_date => l_from_bill_date,
959 p_to_bill_date => l_to_bill_date);
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
964 END recon_report_conc;
965
966
967 END OKL_BILLING_RECON_RPT_PVT;