[Home] [Help]
PACKAGE BODY: APPS.OKL_PROP_TAX_ADJ_PVT
Source
1 PACKAGE BODY OKL_PROP_TAX_ADJ_PVT AS
2 /* $Header: OKLREPRB.pls 120.12.12010000.5 2009/06/03 04:18:35 racheruv ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
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 wraper code generated automatically by Debug code generator
9
10 -- ----------------------------------------------------------------
11 -- Procedure create_adjustment_invoice to reconcile actual and
12 -- estimated property tax
13 -- ----------------------------------------------------------------
14
15 PROCEDURE create_adjustment_invoice
16 (p_api_version IN NUMBER
17 ,p_init_msg_list IN VARCHAR2
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_msg_count OUT NOCOPY NUMBER
20 ,x_msg_data OUT NOCOPY VARCHAR2
21 ,p_contract_number IN VARCHAR2
22 ,p_asset_number IN VARCHAR2
23 ) IS
24
25 --start changed by abhsaxen for Bug#6174484
26 CURSOR term_contracts_csr( p_contract_number IN VARCHAR2, p_asset_number IN VARCHAR2 ) IS
27 SELECT khr.id,
28 khr.contract_number,
29 khr.currency_code,
30 astb.id kle_id
31 FROM okc_k_headers_all_b khr,
32 okc_k_lines_b astb,
33 okc_k_lines_tl astl
34 WHERE astb.sts_code = 'TERMINATED'
35 AND khr.contract_number = nvl(p_contract_number, khr.contract_number)
36 AND astl.name = nvl(p_asset_number, astl.name)
37 AND astb.dnz_chr_id = khr.id
38 AND astb.id = astl.id
39 AND astl.LANGUAGE = userenv('LANG')
40 AND NOT EXISTS
41 (SELECT khr_id
42 FROM okl_trx_contracts trx
43 WHERE trx.khr_id = khr.id
44 AND(source_trx_id IS NULL OR source_trx_id = astb.id)
45 AND trx.tcn_type = 'EPT'
46 AND trx.tsu_code = 'PROCESSED'
47 --rkuttiya added for 12.1.1 Muti GAAP
48 AND trx.representation_type = 'PRIMARY');
49 --
50 --end changed by abhsaxen for Bug#6174484
51
52 CURSOR asset_csr ( p_khr_id IN NUMBER, p_kle_id IN NUMBER ) IS
53 SELECT *
54 FROM okl_k_lines_full_v
55 WHERE DNZ_CHR_ID = p_khr_id
56 AND id = p_kle_id;
57
58 ------------------------------------------------------------
59 -- Extract all actual property tax billable
60 ------------------------------------------------------------
61 CURSOR actual_property_tax_csr ( p_khr_id IN NUMBER, p_kle_id IN NUMBER ) IS
62 SELECT NVL(SUM(ste.amount),0) amount
63 FROM okl_strm_elements ste,
64 okl_streams stm,
65 okl_strm_type_v sty,
66 okc_k_headers_b khr,
67 okl_k_headers khl,
68 okc_k_lines_b kle,
69 okc_statuses_b khs,
70 okc_statuses_b kls
71 WHERE ste.amount <> 0
72 AND stm.id = ste.stm_id
73 AND stm.kle_id = p_kle_id
74 AND sty.id = stm.sty_id
75 AND sty.billable_yn = 'Y'
76 AND sty.STREAM_TYPE_PURPOSE = 'ACTUAL_PROPERTY_TAX'
77 AND khr.id = stm.khr_id
78 AND khr.scs_code IN ('LEASE', 'LOAN')
79 AND khr.id = p_khr_id
80 AND khl.id = stm.khr_id
81 AND khl.deal_type IS NOT NULL
82 AND khs.code = khr.sts_code
83 AND kle.id (+) = stm.kle_id
84 AND kls.code (+) = kle.sts_code
85 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED');
86
87 ------------------------------------------------------------
88 -- Extract all estimated property tax billable
89 ------------------------------------------------------------
90 CURSOR est_property_tax_csr ( p_khr_id IN NUMBER, p_kle_id IN NUMBER ) IS
91 SELECT SUM(AMOUNT)
92 FROM (
93 SELECT NVL(SUM(ste.amount),0) amount
94 FROM okl_strm_elements ste,
95 okl_streams stm,
96 okl_strm_type_v sty,
97 okc_k_headers_b khr,
98 okl_k_headers khl,
99 okc_k_lines_b kle,
100 okc_statuses_b khs,
101 okc_statuses_b kls
102 WHERE ste.amount <> 0
103 AND stm.id = ste.stm_id
104 AND ste.date_billed IS NOT NULL
105 AND stm.kle_id = p_kle_id
106 AND sty.id = stm.sty_id
107 AND sty.billable_yn = 'Y'
108 AND sty.STREAM_TYPE_PURPOSE = 'ESTIMATED_PROPERTY_TAX'
109 AND khr.id = stm.khr_id
110 AND khr.scs_code IN ('LEASE', 'LOAN')
111 AND khr.id = p_khr_id
112 AND khl.id = stm.khr_id
113 AND khl.deal_type IS NOT NULL
114 AND khs.code = khr.sts_code
115 AND kle.id (+) = stm.kle_id
116 AND kls.code (+) = kle.sts_code
117 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
118 UNION
119 SELECT NVL(SUM(til.amount),0) amount
120 FROM okc_k_headers_b khr,
121 okl_trx_ar_invoices_v tai,
122 okl_txl_ar_inv_lns_v til,
123 okl_strm_type_v sty
124 WHERE khr.id = p_khr_id
125 AND tai.khr_id = khr.id
126 AND tai.id = til.tai_id
127 AND tai.qte_id IS NOT NULL
128 AND til.sty_id = sty.id
129 AND til.kle_id = p_kle_id
130 AND sty.STREAM_TYPE_PURPOSE = 'AMPRTX');
131
132 ------------------------------------------------------------
133 -- Declare variables required by APIs
134 ------------------------------------------------------------
135 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ADJUSTMENT_INVOICE';
136 l_api_version CONSTANT NUMBER := 1;
137 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
138 l_error_status VARCHAR2(1);
139 l_error_message VARCHAR2(2000);
140 l_op_unit_name hr_operating_units.name%TYPE;
141 l_request_id VARCHAR2(100);
142
143 ------------------------------------------------------------
144 -- Local Variables
145 ------------------------------------------------------------
146 l_try_id NUMBER;
147 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
148 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
149 l_actual_tax_amt NUMBER;
150 l_estimated_tax_amt NUMBER;
151 l_adjusted_amt NUMBER;
152 l_sty_id NUMBER;
153 l_stm_id NUMBER;
154 l_max_line_num NUMBER;
155 l_actual_tax_count NUMBER;
156 l_adjust_flag okc_rules_b.rule_information3%TYPE;
157 l_prev_contract okc_k_headers_b.contract_number%TYPE;
158 l_display_contract okc_k_headers_b.contract_number%TYPE;
159
160 -- -------------------------------------------------
161 -- Streams Record
162 -- -------------------------------------------------
163 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
164 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
165 l_init_stmv_rec Okl_Streams_Pub.stmv_rec_type;
166
167 -- -------------------------------------------------
168 -- Stream Elements Record
169 -- -------------------------------------------------
170 p_selv_rec Okl_Sel_Pvt.selv_rec_type;
171 x_selv_rec Okl_Sel_Pvt.selv_rec_type;
172 l_init_selv_rec Okl_Sel_Pvt.selv_rec_type;
173
174 ------------------------------------------------------------
175 -- Get try_id for Estimated Property Tax Transaction Type
176 ------------------------------------------------------------
177 CURSOR try_id_csr is
178 SELECT id
179 FROM OKL_TRX_TYPES_V
180 WHERE NAME = 'Estimated Property Tax';
181
182 ------------------------------------------------------------
183 -- Get Count of specific stream type
184 ------------------------------------------------------------
185 CURSOR c_sty_count_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_name VARCHAR2 ) IS
186 SELECT count(*)
187 FROM okl_streams stm,
188 okl_strm_type_v sty
189 WHERE stm.khr_id = p_khr_id
190 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
191 AND stm.sty_id = sty.id
192 AND stm.say_code = 'CURR'
193 AND stm.active_yn = 'Y'
194 AND sty.stream_type_purpose = p_sty_name;
195
196 ------------------------------------------------------------
197 -- Transaction Number Cursor
198 ------------------------------------------------------------
199 CURSOR c_tran_num_csr IS
200 SELECT okl_sif_seq.nextval
201 FROM dual;
202
203 ------------------------------------------------------------
204 -- Stream Type Cursor
205 ------------------------------------------------------------
206 CURSOR sty_csr IS
207 SELECT id
208 FROM okl_strm_type_v
209 WHERE STREAM_TYPE_PURPOSE = 'ADJUSTED_PROPERTY_TAX';
210
211 ------------------------------------------------------------
212 -- Get stm_id of Adjusted Property Tax record
213 ------------------------------------------------------------
214 CURSOR c_stm_id_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_name VARCHAR2 ) IS
215 SELECT stm.id
216 FROM okl_streams stm,
217 okl_strm_type_v sty
218 WHERE stm.khr_id = p_khr_id
219 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
220 AND stm.sty_id = sty.id
221 AND stm.say_code = 'CURR'
222 AND stm.active_yn = 'Y'
223 AND sty.STREAM_TYPE_PURPOSE = p_sty_name;
224
225 ------------------------------------------------------------
226 -- Max Line Number
227 ------------------------------------------------------------
228 CURSOR max_line_num_csr (p_stm_id NUMBER) IS
229 SELECT max(se_line_number)
230 FROM okl_strm_elements
231 WHERE stm_id = p_stm_id;
232
233 ------------------------------------------------------------
234 -- Operating Unit
235 ------------------------------------------------------------
236 CURSOR op_unit_csr IS
237 SELECT NAME
238 FROM hr_operating_units
239 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; -- MOAC fix - Bug#5378114 --varangan- 29-9-06
240
241 ------------------------------------------------------------
242 -- Request Id
243 ------------------------------------------------------------
244 CURSOR req_id_csr IS
245 SELECT
246 RPAD(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),25,' ')
247 FROM DUAL;
248
249 ------------------------------------------------------------
250 -- Bill Property Tax Rule Attribute Value
251 ------------------------------------------------------------
252 CURSOR bill_tax_csr( p_chr_id IN NUMBER, p_cle_id IN NUMBER ) IS
253 SELECT rul.RULE_INFORMATION3
254 FROM okc_rule_groups_b rgp,
255 okc_rules_b rul
256 WHERE rgp.id = rul.rgp_id
257 AND rgp.rgd_code = 'LAASTX'
258 AND rul.RULE_INFORMATION_CATEGORY = 'LAPRTX'
259 AND rul.rule_information3 is not null
260 AND rgp.dnz_chr_id = p_chr_id
261 AND rgp.cle_id = p_cle_id;
262
263 l_f_actual_tax_amt VARCHAR2(50);
264 l_f_estimated_tax_amt VARCHAR2(50);
265 l_f_adjusted_amt VARCHAR2(50);
266
267 TYPE succ_rec_type IS RECORD (
268 l_display_contract okl_k_headers_full_v.contract_number%TYPE,
269 asset_name okx_asset_lines_v.NAME%TYPE,
270 actual_tax VARCHAR2(50),
271 est_tax VARCHAR2(50),
272 adj_tax VARCHAR2(50)
273 );
274
275 TYPE succ_tbl_type IS TABLE OF succ_rec_type
276 INDEX BY BINARY_INTEGER;
277
278 succ_tbl succ_tbl_type;
279 l_init_succ_tbl succ_tbl_type;
280 succ_tbl_idx NUMBER;
281
282 BEGIN
283
284 ------------------------------------------------------------
285 -- Start processing
286 ------------------------------------------------------------
287
288 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
289
290 l_return_status := Okl_Api.START_ACTIVITY(
291 p_api_name => l_api_name,
292 p_pkg_name => G_PKG_NAME,
293 p_init_msg_list => p_init_msg_list,
294 l_api_version => l_api_version,
295 p_api_version => p_api_version,
296 p_api_type => '_PVT',
297 x_return_status => l_return_status);
298
299 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
300 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
301 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
302 RAISE Okl_Api.G_EXCEPTION_ERROR;
303 END IF;
304
305 ------------------------------------------
306 -- Get try_id for estimated property tax
307 ------------------------------------------
308 l_try_id := NULL;
309 OPEN try_id_csr;
310 FETCH try_id_csr INTO l_try_id;
311 CLOSE try_id_csr;
312
313 ----------------------------------------
314 -- Get Operating unit name
315 ----------------------------------------
316 l_op_unit_name := NULL;
317 OPEN op_unit_csr;
318 FETCH op_unit_csr INTO l_op_unit_name;
319 CLOSE op_unit_csr;
320
321 ----------------------------------------
322 -- Get request id cursor
323 ----------------------------------------
324 l_request_id := NULL;
325 OPEN req_id_csr;
326 FETCH req_id_csr INTO l_request_id;
327 CLOSE req_id_csr;
328
329
330 -- ----------------------------------------------------------
331 -- Property Tax Header lines for the report
332 -- ----------------------------------------------------------
333 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 54, ' ')||'Oracle Lease and Finance Management'||lpad(' ', 55, ' '));
334
335 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
336
337 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 48, ' ')||'Property Tax Reconciliation Program'||lpad(' ', 49, ' '));
338 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 48, ' ')||'-----------------------------------'||lpad(' ', 49, ' '));
339
340 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
341 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
342
343 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Operating Unit: '||l_op_unit_name);
344 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Request Id: '||l_request_id||lpad(' ',74,' ') ||'Run Date: '||to_char(sysdate));
345 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Contract Number: '||p_contract_number);
346 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Asset Number : '||p_asset_number);
347
348 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
349
350 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad('-', 132, '-'));
351
352 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Contract Number'||RPAD(' ',25,' ')||
353 'Asset Number'||RPAD(' ',20,' ')||
354 'Actual Property Tax'||RPAD(' ',7,' ')||
355 'Property Tax'||RPAD(' ',12,' ')||
356 'Adjustment');
357 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' '||RPAD(' ',25,' ')||
358 ' '||RPAD(' ',24,' ')||
359 ' '||RPAD(' ',10,' ')||
360 ' Billed '||RPAD(' ',11,' ')||
361 ' Amount ');
362 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad('=', 132, '='));
363
364 ------------------------------------------------------------
365 -- For each Contract and Asset
366 ------------------------------------------------------------
367
368 l_prev_contract := NULL;
369
370 FOR term_contracts_rec IN term_contracts_csr ( p_contract_number, p_asset_number ) LOOP
371
372 ----------------------------------------
373 -- Get sty_id for adjusted property tax
374 ----------------------------------------
375 l_sty_id := NULL;
376 OKL_STREAMS_UTIL.get_dependent_stream_type
377 ( term_contracts_rec.ID,
378 'ACTUAL_PROPERTY_TAX',
379 'ADJUSTED_PROPERTY_TAX',
380 x_return_status,
381 l_sty_id);
382
383
384 FND_FILE.PUT_LINE (FND_FILE.LOG,'Processing Contract :'||term_contracts_rec.contract_number);
385
386 ---------------------------------------------
387 -- Reset error message and error status for
388 -- each record
389 ---------------------------------------------
390 l_error_status := 'S';
391 l_error_message := NULL;
392
393 ---------------------------------------------
394 -- Reset local tax amounts for each tax type
395 ---------------------------------------------
396 l_actual_tax_amt := NULL;
397 l_estimated_tax_amt := NULL;
398 l_adjusted_amt := NULL;
399
400 ------------------------------------------------------------
401 -- Create Contract Transaction Header and Line
402 -- in Submitted Status
403 ------------------------------------------------------------
404 l_trxH_in_rec.tcn_type := 'EPT';
405 l_trxH_in_rec.tsu_code := 'SUBMITTED';
406 l_trxH_in_rec.description := 'Estimated Property Tax';
407 l_trxH_in_rec.date_transaction_occurred := SYSDATE;
408 l_trxH_in_rec.khr_id := term_contracts_rec.ID;
409 l_trxH_in_rec.try_id := l_try_id;
410 l_trxH_in_rec.SOURCE_TRX_ID := term_contracts_rec.kle_id;
411 l_trxH_in_rec.SOURCE_TRX_TYPE := 'KLE';
412
413 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Creating Contract Transaction.');
414 Okl_Trx_Contracts_Pub.create_trx_contracts(
415 p_api_version => l_api_version
416 ,p_init_msg_list => p_init_msg_list
417 ,x_return_status => l_return_status
418 ,x_msg_count => x_msg_count
419 ,x_msg_data => x_msg_data
420 ,p_tcnv_rec => l_trxH_in_rec
421 ,x_tcnv_rec => l_trxH_out_rec);
422
423 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
424 l_error_status := 'E';
425 l_error_message := 'Error: Creating header record in OKL_TRX_CONTRACTS';
426 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error: Creating header record in OKL_TRX_CONTRACTS');
427 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
428 l_error_status := 'E';
429 l_error_message := 'Error: Creating header record in OKL_TRX_CONTRACTS';
430 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error: Creating header record in OKL_TRX_CONTRACTS');
431 END IF;
432
433
434 -- -------------------------------------------------
435 -- For printing the result only in case of success
436 -- -------------------------------------------------
437 succ_tbl_idx := 1;
438 succ_tbl := l_init_succ_tbl;
439
440 ------------------------------------------------------------
441 -- For each asset line process a summary of records
442 ------------------------------------------------------------
443 FOR asset_rec IN asset_csr( term_contracts_rec.ID, term_contracts_rec.kle_id ) LOOP
444
445 l_actual_tax_amt := NULL;
446 OPEN actual_property_tax_csr ( term_contracts_rec.ID, asset_rec.id );
447 FETCH actual_property_tax_csr INTO l_actual_tax_amt;
448 CLOSE actual_property_tax_csr;
449
450 l_estimated_tax_amt := NULL;
451 OPEN est_property_tax_csr ( term_contracts_rec.ID, asset_rec.id );
452 FETCH est_property_tax_csr INTO l_estimated_tax_amt;
453 CLOSE est_property_tax_csr;
454
455 l_adjusted_amt := 0;
456
457 l_adjust_flag := NULL;
458 OPEN bill_tax_csr( term_contracts_rec.ID, asset_rec.id );
459 FETCH bill_tax_csr INTO l_adjust_flag;
460 CLOSE bill_tax_csr;
461
462 IF l_adjust_flag = 'ESTIMATED_AND_ACTUAL' THEN
463
464 l_adjusted_amt := l_actual_tax_amt - l_estimated_tax_amt;
465
466 ----------------------------------------------------
467 -- Check if there exists a stream for
468 ----------------------------------------------------
469 l_actual_tax_count := 0;
470 OPEN c_sty_count_csr ( term_contracts_rec.ID, asset_rec.id, 'ADJUSTED_PROPERTY_TAX' );
471 FETCH c_sty_count_csr INTO l_actual_tax_count;
472 CLOSE c_sty_count_csr;
473
474 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Creating Adjusted Property Tax Streams.');
475
476 IF l_actual_tax_count > 0 THEN -- check for ADJUSTED property tax stream
477 NULL;
478 ELSE -- check for ADJUSTED property tax stream
479 -- -------------------------
480 -- Null out records
481 -- -------------------------
482 l_stmv_rec := l_init_stmv_rec;
483 lx_stmv_rec := l_init_stmv_rec;
484
485 OPEN c_tran_num_csr;
486 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
487 CLOSE c_tran_num_csr;
488
489 l_stmv_rec.sty_id := l_sty_id;
490 l_stmv_rec.khr_id := term_contracts_rec.ID;
491 l_stmv_rec.kle_id := asset_rec.id;
492 l_stmv_rec.sgn_code := 'MANL';
493 l_stmv_rec.say_code := 'CURR';
494 l_stmv_rec.active_yn := 'Y';
495 l_stmv_rec.date_current := sysdate;
496 l_stmv_rec.comments := 'Adjusted Property Tax';
497
498 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Creating Adjusted Property Tax Streams');
499
500 Okl_Streams_Pub.create_streams(
501 p_api_version => p_api_version,
502 p_init_msg_list => p_init_msg_list,
503 x_return_status => x_return_status,
504 x_msg_count => x_msg_count,
505 x_msg_data => x_msg_data,
506 p_stmv_rec => l_stmv_rec,
507 x_stmv_rec => lx_stmv_rec);
508
509 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
510 l_error_status := 'E';
511 l_error_message := 'Error: Creating header record in OKL_TRX_CONTRACTS';
512 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error: Creating Streams for Adjusted Property Tax');
513 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
514 l_error_status := 'E';
515 l_error_message := 'Error: Creating Streams for Adjusted Property Tax';
516 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error: Creating Streams for Adjusted Property Tax');
517 END IF;
518 END IF; -- check for adjusted property tax stream
519 -- --------------------------------------------------------
520 -- Create stream elements, if there were no error messages
521 -- --------------------------------------------------------
522 IF l_error_status <> 'E' THEN
523
524 p_selv_rec := l_init_selv_rec;
525
526 -- Create Stream Element
527 l_stm_id := NULL;
528 OPEN c_stm_id_csr ( term_contracts_rec.ID, asset_rec.id, 'ADJUSTED_PROPERTY_TAX' );
529 FETCH c_stm_id_csr INTO l_stm_id;
530 CLOSE c_stm_id_csr;
531
532 l_max_line_num := 0;
533 OPEN max_line_num_csr ( l_stm_id );
534 FETCH max_line_num_csr INTO l_max_line_num;
535 CLOSE max_line_num_csr;
536
537 p_selv_rec.stm_id := l_stm_id;
538 p_selv_rec.SE_LINE_NUMBER := NVL( l_max_line_num, 0 ) + 1;
539 p_selv_rec.STREAM_ELEMENT_DATE := SYSDATE;
540 p_selv_rec.AMOUNT := l_adjusted_amt;
541 p_selv_rec.COMMENTS := 'Adjusted Property Tax';
542
543 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Creating Adjustment Stream Element.');
544
545 -- Create adjustment stream element only if the adjustment
546 -- amount is non-zero
547 IF l_adjusted_amt <> 0 THEN
548 Okl_Sel_Pvt.insert_row(
549 p_api_version,
550 p_init_msg_list,
551 x_return_status,
552 x_msg_count,
553 x_msg_data,
554 p_selv_rec,
555 x_selv_rec);
556 ELSE
557 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Adjustment Stream Element not created because adjustment amount is zero.');
558 END IF;
559
560 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
561 l_error_status := 'E';
562 l_error_message := 'Error: Creating Adjusted Property Tax stream element';
563 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error: Creating Adjusted Property Tax stream element');
564 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
565 l_error_status := 'E';
566 l_error_message := 'Error: Creating Adjusted Property Tax stream element';
567 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error: Creating Adjusted Property Tax stream element');
568 END IF;
569 END IF; -- Process Error
570 END IF; -- Check if Bill Property Tax is 'ESTIMATED_ACTUAL'
571
572 -- -----------------------------------------------------
573 -- Get currency precision
574 -- -----------------------------------------------------
575 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_actual_tax_amt: '||l_actual_tax_amt);
576 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_estimated_tax_amt: '||l_estimated_tax_amt);
577 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_adjusted_amt: '||l_adjusted_amt);
578
579 l_f_actual_tax_amt := okl_accounting_util.format_amount(l_actual_tax_amt, term_contracts_rec.currency_code);
580 l_f_estimated_tax_amt := okl_accounting_util.format_amount(l_estimated_tax_amt, term_contracts_rec.currency_code);
581 l_f_adjusted_amt := okl_accounting_util.format_amount(l_adjusted_amt, term_contracts_rec.currency_code);
582
583 IF (term_contracts_rec.contract_number = l_prev_contract) THEN
584 l_display_contract := ' ';
585 ELSE
586 l_display_contract := term_contracts_rec.contract_number;
587 END IF;
588 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_display_contract: '||l_display_contract);
589 -- --------------------------
590 -- Record successful records
591 -- --------------------------
592 succ_tbl(succ_tbl_idx).l_display_contract := l_display_contract;
593 succ_tbl(succ_tbl_idx).asset_name := asset_rec.name;
594 succ_tbl(succ_tbl_idx).actual_tax := l_f_actual_tax_amt;
595 succ_tbl(succ_tbl_idx).est_tax := l_f_estimated_tax_amt;
596 succ_tbl(succ_tbl_idx).adj_tax := l_f_adjusted_amt;
597
598 succ_tbl_idx := succ_tbl_idx + 1;
599
600 FND_FILE.PUT_LINE (FND_FILE.LOG, 'asset_rec.name: '||asset_rec.name);
601 /* FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD(SUBSTR(l_display_contract,1,25),25,' ' )|| */
602 /* RPAD(' ',15,' ')|| */
603 /* RPAD(SUBSTR(asset_rec.name,1,20),20,' ')|| */
604 /* RPAD(' ',6,' ')|| */
605 /* lpad(SUBSTR(l_f_actual_tax_amt,1,22),22,' ')|| */
606 /* lpad(SUBSTR(l_f_estimated_tax_amt,1,22),22,' ')|| */
607 /* lpad(SUBSTR(l_f_adjusted_amt,1,22),22,' ') */
608 /* ); */
609 -- ---------------------
610 -- Save Contract Number
611 -- ---------------------
612 l_prev_contract := term_contracts_rec.contract_number;
613
614 END LOOP; -- loop for each asset
615
616 ------------------------------------------------------------
617 -- Update Contract transaction status to be Success or error
618 ------------------------------------------------------------
619 IF l_error_status = 'S' THEN
620 -- If there was no adjustment amounts to process
621 -- mark the record as ERROR to indicate nothing was processed.
622 -- This allows for future runs when actual property tax info
623 -- is uploaded at a later date
624 IF ( (l_actual_tax_amt = 0)
625 AND (l_estimated_tax_amt = 0)
626 AND (l_adjusted_amt = 0)
627 )
628 THEN
629 UPDATE OKL_TRX_CONTRACTS
630 SET TSU_CODE = 'ERROR'
631 WHERE ID = l_trxH_out_rec.ID;
632 ELSE
633 UPDATE OKL_TRX_CONTRACTS
634 SET TSU_CODE = 'PROCESSED'
635 WHERE ID = l_trxH_out_rec.ID;
636 END IF;
637 -- -------------------------------
638 -- Loop thru the success records
639 -- -------------------------------
640 FOR i IN succ_tbl.FIRST..succ_tbl.LAST LOOP
641 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD(SUBSTR(succ_tbl(i).l_display_contract,1,25),25,' ' )||
642 RPAD(' ',15,' ')||
643 RPAD(SUBSTR(succ_tbl(i).asset_name,1,20),20,' ')||
644 RPAD(' ',6,' ')||
645 lpad(SUBSTR(succ_tbl(i).actual_tax,1,22),22,' ')||
646 lpad(SUBSTR(succ_tbl(i).est_tax,1,22),22,' ')||
647 lpad(SUBSTR(succ_tbl(i).adj_tax,1,22),22,' ')
648 );
649 END LOOP;
650 ELSE
651 UPDATE OKL_TRX_CONTRACTS
652 SET TSU_CODE = 'ERROR'
653 WHERE ID = l_trxH_out_rec.ID;
654 END IF;
655 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
656
657 -- Start Bug 4520466
658 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date( term_contracts_rec.id );
659 -- End Bug 4520466
660
661 END LOOP; -- loop for each contract
662
663 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad('=', 132, '='));
664 ------------------------------------------------------------
665 -- End processing
666 ------------------------------------------------------------
667 Okl_Api.END_ACTIVITY (
668 x_msg_count => x_msg_count,
669 x_msg_data => x_msg_data);
670
671 EXCEPTION
672
673 ------------------------------------------------------------
674 -- Exception handling
675 ------------------------------------------------------------
676
677 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
678 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error (EXCP) => '||SQLERRM);
679 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
680 p_api_name => l_api_name,
681 p_pkg_name => G_PKG_NAME,
682 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
683 x_msg_count => x_msg_count,
684 x_msg_data => x_msg_data,
685 p_api_type => '_PVT');
686
687 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
688 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error (UNEXP) => '||SQLERRM);
689 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
690 p_api_name => l_api_name,
691 p_pkg_name => G_PKG_NAME,
692 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data,
695 p_api_type => '_PVT');
696
697 WHEN OTHERS THEN
698 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error (OTHERS) => '||SQLERRM);
699 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
700 p_api_name => l_api_name,
701 p_pkg_name => G_PKG_NAME,
702 p_exc_name => 'OTHERS',
703 x_msg_count => x_msg_count,
704 x_msg_data => x_msg_data,
705 p_api_type => '_PVT');
706
707 END create_adjustment_invoice;
708
709
710 END OKL_PROP_TAX_ADJ_PVT;