1 PACKAGE BODY OKL_ACCRUAL_DEPRN_ADJ_PVT AS
2 /* $Header: OKLRADAB.pls 120.9.12010000.2 2008/09/09 20:04:28 rkuttiya ship $ */
3
4 FUNCTION GET_PERIOD_NAME(p_date IN DATE) RETURN VARCHAR2 IS
5
6 l_period_name VARCHAR2(2000);
7 l_period_set_name VARCHAR2(2000);
8 l_user_period_type VARCHAR2(2000);
9 l_sob_id NUMBER;
10
11 -- cursor to select period details
12 CURSOR period_details_csr(p_sob_id NUMBER) IS
13 SELECT period_set_name, accounted_period_type
14 FROM GL_LEDGERS_PUBLIC_V
15 WHERE ledger_id = p_sob_id;
16
17 -- cursor to select period name
18 CURSOR period_name_csr(p_current_date DATE, p_period_set_name VARCHAR2, p_user_period_type VARCHAR2) IS
19 SELECT period_name
20 FROM gl_periods
21 WHERE start_date <= p_current_date
22 AND end_date >= p_current_date
23 AND period_set_name = p_period_set_name
24 AND period_type = p_user_period_type;
25
26 BEGIN
27 -- get sob id
28 l_sob_id := OKL_ACCOUNTING_UTIL.get_set_of_books_id;
29
30 --validate sob id
31 IF l_sob_id IS NULL OR l_sob_id = OKL_API.G_MISS_NUM THEN
32 okl_api.set_message(p_app_name => G_APP_NAME,
33 p_msg_name => 'OKL_AGN_SOB_ID_ERROR');
34 RAISE OKL_API.G_EXCEPTION_ERROR;
35 END IF;
36
37 OPEN period_details_csr(l_sob_id);
38 FETCH period_details_csr INTO l_period_set_name, l_user_period_type;
39 IF period_details_csr%NOTFOUND THEN
40 -- store SQL error message on message stack for caller
41 okl_api.set_message(p_app_name => G_APP_NAME,
42 p_msg_name => G_NO_MATCHING_RECORD,
43 p_token1 => G_COL_NAME_TOKEN,
44 p_token1_value => 'l_sob_id');
45 RAISE OKL_API.G_EXCEPTION_ERROR;
46 END IF;
47 CLOSE period_details_csr;
48
49 OPEN period_name_csr(p_date, l_period_set_name, l_user_period_type);
50 FETCH period_name_csr INTO l_period_name;
51 IF period_name_csr%NOTFOUND THEN
52 -- store SQL error message on message stack for caller
53 okl_api.set_message(p_app_name => G_APP_NAME,
54 p_msg_name => G_NO_MATCHING_RECORD,
55 p_token1 => G_COL_NAME_TOKEN,
56 p_token1_value => 'l_period_set_name');
57 RAISE OKL_API.G_EXCEPTION_ERROR;
58 END IF;
59 CLOSE period_name_csr;
60
61 RETURN l_period_name;
62
63 EXCEPTION
64 WHEN OKL_API.G_EXCEPTION_ERROR THEN
65 IF period_details_csr%ISOPEN THEN
66 CLOSE period_details_csr;
67 END IF;
68
69 IF period_name_csr%ISOPEN THEN
70 CLOSE period_name_csr;
71 END IF;
72
73 RETURN NULL;
74
75 WHEN OTHERS THEN
76 IF period_details_csr%ISOPEN THEN
77 CLOSE period_details_csr;
78 END IF;
79
80 IF period_name_csr%ISOPEN THEN
81 CLOSE period_name_csr;
82 END IF;
83
84 RETURN NULL;
85
86 END GET_PERIOD_NAME;
87
88
89 FUNCTION GET_CATEGORY_NAME(p_category_id IN NUMBER) RETURN VARCHAR2 IS
90
91 l_category_name VARCHAR2(2000);
92
93 -- cursor to get get category name
94 CURSOR category_name_csr(p_category_id NUMBER) IS
95 SELECT CONCATENATED_SEGMENTS
96 FROM FA_CATEGORIES_B_KFV
97 WHERE category_id = p_category_id;
98
99 BEGIN
100 OPEN category_name_csr(p_category_id);
101 FETCH category_name_csr INTO l_category_name;
102 IF category_name_csr%NOTFOUND THEN
103 -- store SQL error message on message stack for caller
104 OKL_API.set_message(p_app_name => G_APP_NAME,
105 p_msg_name => G_NO_MATCHING_RECORD,
106 p_token1 => G_COL_NAME_TOKEN,
107 p_token1_value => 'p_category_id');
108 RAISE OKL_API.G_EXCEPTION_ERROR;
109 END IF;
110 CLOSE category_name_csr;
111
112 RETURN l_category_name;
113
114 EXCEPTION
115 WHEN OKL_API.G_EXCEPTION_ERROR THEN
116 IF category_name_csr%ISOPEN THEN
117 CLOSE category_name_csr;
118 END IF;
119
120 RETURN NULL;
121
122 WHEN OTHERS THEN
123 IF category_name_csr%ISOPEN THEN
124 CLOSE category_name_csr;
125 END IF;
126
127 RETURN NULL;
128
129 END GET_CATEGORY_NAME;
130
131 -- Function to call the GENERATE_ACCRUALS Procedure
132 FUNCTION SUBMIT_DEPRN_ADJUSTMENT(
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2,
136 p_api_version IN NUMBER,
137 p_batch_name IN VARCHAR2,
138 p_date_from IN DATE,
139 p_date_to IN DATE ) RETURN NUMBER IS
140
141 x_request_id NUMBER;
142 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
143 l_msg_count NUMBER;
144 l_msg_data VARCHAR2(2000);
145 l_api_name VARCHAR2(2000) := 'SUBMIT_DEPRN_ADJUSTMENT';
146 l_api_version CONSTANT NUMBER := 1.0;
147 l_init_msg_list VARCHAR2(20) DEFAULT Okl_Api.G_FALSE;
148 l_date_from VARCHAR2(2000);
149 l_date_to VARCHAR2(2000);
150
151 BEGIN
152 -- Set save point
153 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
154 ,G_PKG_NAME
155 ,l_init_msg_list
156 ,l_api_version
157 ,p_api_version
158 ,'_PVT'
159 ,l_return_status);
160 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
161 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
162 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
163 RAISE Okl_Api.G_EXCEPTION_ERROR;
164 END IF;
165
166 -- Bug 3130551
167 -- check if period from date is missing
168 IF (p_date_from IS NULL OR p_date_from = Okl_Api.G_MISS_DATE) THEN
169 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
170 p_msg_name => 'OKL_MGP_PERIOD_FROM_ERROR');
171 RAISE Okl_Api.G_EXCEPTION_ERROR;
172 ELSE
173 l_date_from := FND_DATE.DATE_TO_CANONICAL(p_date_from);
174 END IF;
175
176 -- Bug 3130551
177 -- check if period to date is missing
178 IF (p_date_to IS NULL OR p_date_from = Okl_Api.G_MISS_DATE) THEN
179 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
180 p_msg_name => 'OKL_MGP_PERIOD_TO_ERROR');
181 RAISE Okl_Api.G_EXCEPTION_ERROR;
182 ELSE
183 l_date_to := FND_DATE.DATE_TO_CANONICAL(p_date_to);
184 END IF;
185
186 -- Submit Concurrent Program Request
187 x_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OKL',
188 program => 'OKLADACALC',
189 argument1 => p_batch_name,
190 argument2 => l_date_from,
191 argument3 => l_date_to);
192
193 IF x_request_id = 0 THEN
194 -- Handle submission error
195 -- Raise Error if the request has not been submitted successfully.
196 Okl_Api.SET_MESSAGE(G_APP_NAME, 'OKL_ERROR_SUB_CONC_PROG', 'CONC_PROG', 'OKL Depreciation Adjustment for Accrual');
197 RAISE Okl_Api.G_EXCEPTION_ERROR;
198 ELSE
199 --set return status
200 x_return_status := l_return_status;
201 RETURN x_request_id;
202 END IF;
203
204 EXCEPTION
205 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
206 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
207 ,g_pkg_name
208 ,'OKL_API.G_RET_STS_ERROR'
209 ,x_msg_count
210 ,x_msg_data
211 ,'_PVT');
212 RETURN x_request_id;
213 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
214 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
215 ,g_pkg_name
216 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
217 ,x_msg_count
218 ,x_msg_data
219 ,'_PVT');
220 RETURN x_request_id;
221 WHEN OTHERS THEN
222 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
223 (l_api_name,
224 G_PKG_NAME,
225 'OTHERS',
226 x_msg_count,
227 x_msg_data,
228 '_PVT');
229 RETURN x_request_id;
230 END SUBMIT_DEPRN_ADJUSTMENT;
231
232 --This is the main accruals deprn adjustment procedure.
233
234 PROCEDURE ADJUST_DEPRECIATION(errbuf OUT NOCOPY VARCHAR2
235 ,retcode OUT NOCOPY NUMBER
236 ,p_batch_name IN VARCHAR2
237 ,p_period_from IN VARCHAR2
238 ,p_period_to IN VARCHAR2) IS
239
240 -- declare local variables
241 l_contract_id OKL_K_HEADERS_FULL_V.id%TYPE;
242 l_contract_number OKL_K_HEADERS_FULL_V.contract_number%TYPE;
243 l_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
244 l_sob_id OKL_SYS_ACCT_OPTS.set_of_books_id%TYPE;
245 l_sob_name VARCHAR2(2000);
246 l_sysdate DATE := SYSDATE;
247 l_api_version CONSTANT NUMBER := 1.0;
248 p_api_version CONSTANT NUMBER := 1.0;
249 l_api_name CONSTANT VARCHAR2(30) := 'ADJUST_DEPRECIATION';
250 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
251 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
252 l_msg_count NUMBER;
253 l_msg_data VARCHAR2(2000);
254 l_outer_error_msg_tbl Okl_Accounting_Util.Error_Message_Type;
255 l_org_id NUMBER;
256 --l_org_name VARCHAR2(100);
257 --Bug# 2706328
258 l_org_name VARCHAR2(240);
259 l_count NUMBER := 1;
260 l_asset_deprn_tbl asset_deprn_tbl_type;
261 l_securitized_yn VARCHAR2(1);
262 l_period_from DATE := FND_DATE.CANONICAL_TO_DATE(p_period_from);
263 l_period_to DATE := FND_DATE.CANONICAL_TO_DATE(p_period_to);
264 l_sty_id OKL_STRM_TYPE_V.ID%TYPE;
265
266 -- Cursor to select contracts for accrual processing
267 -- commenting where condition for Evergreen contracts processing
268 CURSOR select_contracts_csr IS
269 SELECT chr.id,
270 chr.contract_number
271 FROM OKC_K_HEADERS_B chr,
272 OKL_K_HEADERS khr
273 WHERE chr.scs_code = 'LEASE'
274 AND chr.id = khr.id
275 AND chr.sts_code IN ('BOOKED','EVERGREEN') --bug 3448057. Removing approved and under revision status
276 AND khr.deal_type = 'LEASEOP';
277 --AND l_sysdate <= NVL(end_date, l_sysdate) --Bug 3078971
278
279
280
281 -- Cursor to select transactions which have been non-accrued or caught-up
282 -- and corresponding total depreciation amounts by category
283 CURSOR asset_deprn_csr(p_khr_id NUMBER) IS
284 SELECT fad.asset_category_id,
285 trx.accrual_activity,
286 trx.date_accrual,
287 SUM(fds.deprn_amount) deprn_amount
288 FROM
289 OKL_TRX_CONTRACTS trx,
290 OKC_K_ITEMS cli,
291 OKC_K_LINES_B cle,
292 OKC_LINE_STYLES_B cls,
293 FA_BOOKS fab,
294 FA_ADDITIONS_B fad,
295 FA_BOOK_CONTROLS fbc,
296 FA_CATEGORIES_B fcb,
297 FA_DEPRN_SUMMARY fds,
298 FA_DEPRN_PERIODS fdp
299 --Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
300 WHERE trx.tsu_code = 'PROCESSED'
301 AND trx.tcn_type = 'ACL'
302 AND trx.accrual_activity IN ('CATCH-UP','NON-ACCRUAL')
303 AND trx.khr_id = p_khr_id
304 --rkuttiya added for 12.1.1 Multi GAAP Project
305 AND trx.representation_type = 'PRIMARY'
306 --
307 AND trx.date_transaction_occurred BETWEEN l_period_from AND l_period_to -- Bug 3130551
308 AND cle.dnz_chr_id = trx.khr_id
309 AND cle.id = cli.cle_id
310 AND cle.dnz_chr_id = p_khr_id
311 AND cli.dnz_chr_id = cle.dnz_chr_id
312 AND cle.lse_id = cls.id
313 AND cls.lty_code = 'FIXED_ASSET'
314 AND fad.asset_id = TO_NUMBER(cli.object1_id1)
315 AND fad.asset_category_id = fcb.category_id
316 AND fad.asset_id = fab.asset_id
317 AND fab.book_type_code = fbc.book_type_code
318 AND fab.transaction_header_id_out is null
319 AND NVL(fbc.date_ineffective,sysdate+1) > sysdate
320 AND fbc.book_class = 'CORPORATE'
321 AND fds.asset_id = fad.asset_id
322 AND fds.book_type_code = fab.book_type_code
323 AND fds.book_type_code = fdp.book_type_code
324 AND fds.period_counter = fdp.period_counter
325 AND fdp.period_name = GET_PERIOD_NAME(trx.date_accrual)
326 GROUP BY fad.asset_category_id,trx.date_accrual,trx.accrual_activity;
327
328 -- Bug 3130551
329 CURSOR get_revenue_share_csr(p_inv_id NUMBER) IS
330 SELECT SUM(kleb_rv.percent_stake) percent_stake
331 FROM okl_k_lines kleb_rv,
332 okc_k_lines_b cles_rv,
333 okc_line_styles_b lseb_rv,
334 okl_strm_type_b styb_rv,
335 okc_k_lines_b cles_iv,
336 okc_line_styles_b lseb_iv
337 WHERE cles_iv.dnz_chr_id = p_inv_id
338 AND cles_iv.lse_id = lseb_iv.id
339 AND lseb_iv.lty_code = 'INVESTMENT'
340 AND cles_rv.cle_id = cles_iv.id
341 AND cles_rv.lse_id = lseb_rv.id
342 AND lseb_rv.lty_code = 'REVENUE_SHARE'
343 AND kleb_rv.id = cles_rv.id
344 AND kleb_rv.sty_id = styb_rv.id
345 AND styb_rv.code ='RENT';
346
347 -- Cursor to get org name
348 CURSOR org_name_csr(p_org_id NUMBER) IS
349 SELECT name
350 FROM hr_operating_units
351 WHERE organization_id = p_org_id;
352
353 -- Cursor to get sty id
354 CURSOR sty_id_csr IS
355 SELECT id
356 FROM OKL_STRM_TYPE_B
357 WHERE code = 'RENT';
358
359 -- cursor to select agreement number
360 CURSOR agr_num_csr(p_agr_id NUMBER) IS
361 SELECT contract_number
362 FROM OKC_K_HEADERS_B
363 WHERE id = p_agr_id;
364
365 TYPE depr_contracts_rec_type IS RECORD
366 (contract_id OKL_K_HEADERS_FULL_V.ID%TYPE,
367 contract_number OKL_K_HEADERS_FULL_V.CONTRACT_NUMBER%TYPE);
368 TYPE depr_contracts_tbl_type IS TABLE OF depr_contracts_rec_type INDEX BY BINARY_INTEGER;
369
370 l_depr_contracts_tbl depr_contracts_tbl_type;
371
372 BEGIN
373
374 -- Find set of books id
375 l_sob_id := Okl_Accounting_Util.GET_SET_OF_BOOKS_ID;
376 IF (l_sob_id IS NULL OR l_sob_id = OKL_API.G_MISS_NUM) THEN
377 Okl_Api.set_message(p_app_name => g_app_name,
378 p_msg_name => 'OKL_AGN_SOB_ID_ERROR');
379 RAISE Okl_Api.G_EXCEPTION_ERROR;
380 END IF;
381
382 -- Find set of books name
383 l_sob_name := Okl_Accounting_Util.GET_SET_OF_BOOKS_NAME(l_sob_id);
384
385 -- Find org name for report
386 l_org_id := mo_global.get_current_org_id();
387 IF l_org_id IS NULL THEN
388 -- store SQL error message on message stack for caller
389 okl_api.set_message(p_app_name => G_APP_NAME,
390 p_msg_name => G_INVALID_VALUE,
391 p_token1 => G_COL_NAME_TOKEN,
392 p_token1_value => 'l_org_id');
393 RAISE OKL_API.G_EXCEPTION_ERROR;
394 END IF;
395
396 OPEN org_name_csr(l_org_id);
397 FETCH org_name_csr INTO l_org_name;
398 IF org_name_csr%NOTFOUND THEN
399 -- store SQL error message on message stack for caller
400 okl_api.set_message(p_app_name => G_APP_NAME,
401 p_msg_name => G_NO_MATCHING_RECORD,
402 p_token1 => G_COL_NAME_TOKEN,
403 p_token1_value => 'l_org_id');
404 RAISE OKL_API.G_EXCEPTION_ERROR;
405 END IF;
406 CLOSE org_name_csr;
407
408
409 -- Find currency code for the set of books id
410 l_currency_code := Okl_Accounting_Util.GET_FUNC_CURR_CODE;
411 IF (l_currency_code IS NULL) THEN
412 Okl_Api.set_message(p_app_name => g_app_name,
413 p_msg_name => 'OKL_AGN_CURR_CODE_ERROR');
414 RAISE Okl_Api.G_EXCEPTION_ERROR;
415 END IF;
416
417 -- Create report header
418 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||
419 FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_TITLE'));
420 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||
421 FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_TITLE_UNDERLINE'));
422 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
423 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_SOB_TITLE')
424 ||' '||RPAD(l_sob_name, 65)
425 ||FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_OU_TITLE')
426 ||' '||l_org_name);
427 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_CURR_TITLE')
428 ||' '||RPAD(l_currency_code,58)||FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_BATCH_NAME')
429 ||' '||p_batch_name);
430 -- Bug 3130551
431 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_MGP_RPT_DATE_RANGE')||' '||l_period_from||' '||FND_MESSAGE.GET_STRING('OKL','OKL_MGP_RPT_TO_FIELD')||' '||l_period_to);
432 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
433
434 -- Create Report Content
435 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_AST_CAT_PROMPT'),35)
436 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_PRD_NAME_PROMPT'),20)
437 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_ACTIVITY'),23)
438 ||LPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AMT_TITLE'),18));
439
440 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_AST_CAT_UNDERLINE'),35)
441 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_PRD_NAME_UNDERLINE'),20)
442 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_ACTIVITY_UNDERLINE'),23)
443 ||LPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AMT_LINE'),18));
444
445 -- Open cursor to select contracts for accrual processing
446 -- commenting code below for bug# 3377730. This from or bul collect
447 -- does not seem to work on 8i environment. Works brilliantly on 9i environments
448 -- Re-enable commented code in 11ix
449 --OPEN select_contracts_csr;
450 --FETCH select_contracts_csr BULK COLLECT INTO l_depr_contracts_tbl;
451 --CLOSE select_contracts_csr;
452
453 FOR x in select_contracts_csr
454 LOOP
455 l_depr_contracts_tbl(select_contracts_csr%ROWCOUNT).contract_id := x.id;
456 l_depr_contracts_tbl(select_contracts_csr%ROWCOUNT).contract_number := x.contract_number;
457 END LOOP;
458
459 OPEN sty_id_csr;
460 FETCH sty_id_csr INTO l_sty_id;
461 IF sty_id_csr%NOTFOUND THEN
462 -- store SQL error message on message stack for caller
463 okl_api.set_message(p_app_name => G_APP_NAME,
464 p_msg_name => G_NO_MATCHING_RECORD,
465 p_token1 => G_COL_NAME_TOKEN,
466 p_token1_value => 'l_sty_id');
467 RAISE OKL_API.G_EXCEPTION_ERROR;
468 END IF;
469 CLOSE sty_id_csr;
470
471 IF l_depr_contracts_tbl.COUNT > 0 THEN
472
473 FOR i IN l_depr_contracts_tbl.FIRST..l_depr_contracts_tbl.LAST
474 LOOP
475 l_contract_id := l_depr_contracts_tbl(i).contract_id;
476 l_contract_number := l_depr_contracts_tbl(i).contract_number;
477
478 DECLARE
479 -- Declare local variables which need to be re-initialized to null for each contract
480 l_error_msg_tbl Okl_Accounting_Util.Error_Message_Type;
481 l_record_status VARCHAR2(10);
482 l_deprn_amount NUMBER := 0;
483 l_agreement_id OKL_K_HEADERS_FULL_V.id%TYPE;
484 l_agreement_number OKL_K_HEADERS_FULL_V.contract_number%TYPE;
485 l_revenue_share NUMBER := 0;
486
487 -- Begin a new PL/SQL block to trap errors related to a praticular contract and to move on to the next contract
488 BEGIN
489
490 -- Bug 3130551
491 OKL_SECURITIZATION_PVT.check_sty_securitized(
492 p_api_version => l_api_version
493 ,p_init_msg_list => l_init_msg_list
494 ,x_return_status => l_return_status
495 ,x_msg_count => l_msg_count
496 ,x_msg_data => l_msg_data
497 ,p_khr_id => l_contract_id
498 ,p_effective_date => l_period_from
499 ,p_effective_date_operator => OKL_SECURITIZATION_PVT.G_GREATER_THAN_EQUAL_TO
500 ,p_sty_id => l_sty_id
501 ,x_value => l_securitized_yn
502 ,x_inv_agmt_chr_id => l_agreement_id);
503
504 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
505 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
506 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
507 RAISE Okl_Api.G_EXCEPTION_ERROR;
508 END IF;
509
510 IF l_securitized_yn = 'T' THEN
511 -- check if agreement id is available. if not throw error.
512 IF l_agreement_id IS NULL THEN
513 Okl_Api.set_message(p_app_name => g_app_name,
514 p_msg_name => 'OKL_ADA_AGR_ID_ERROR',
515 p_token1 => G_CONTRACT_NUMBER_TOKEN,
516 p_token1_value => l_contract_number);
517 RAISE OKL_API.G_EXCEPTION_ERROR;
518 END IF;
519
520 OPEN agr_num_csr(l_agreement_id);
521 FETCH agr_num_csr INTO l_agreement_number;
522 IF agr_num_csr%NOTFOUND THEN
523 -- store SQL error message on message stack for caller
524 okl_api.set_message(p_app_name => G_APP_NAME,
525 p_msg_name => G_NO_MATCHING_RECORD,
526 p_token1 => G_COL_NAME_TOKEN,
527 p_token1_value => 'contract id');
528 RAISE OKL_API.G_EXCEPTION_ERROR;
529 END IF;
530 CLOSE agr_num_csr;
531
532 -- get revenue share
533 OPEN get_revenue_share_csr(l_agreement_id);
534 FETCH get_revenue_share_csr INTO l_revenue_share;
535 IF get_revenue_share_csr%NOTFOUND OR l_revenue_share = 0 THEN
536 -- store SQL error message on message stack for caller
537 Okl_Api.set_message(p_app_name => g_app_name,
538 p_msg_name => 'OKL_ADA_REV_SHARE_ERROR',
539 p_token1 => 'AGREEMENT_NUMBER',
540 p_token1_value => l_agreement_number);
541 RAISE Okl_Api.G_EXCEPTION_ERROR;
542 END IF;
543 CLOSE get_revenue_share_csr;
544
545 END IF; --IF l_securitized_yn = 'T' THEN
546
547 -- Open cursor asset_deprn_csr
548 FOR i IN asset_deprn_csr(l_contract_id)
549 LOOP
550
551 IF l_securitized_yn = 'T' THEN
552 l_deprn_amount := ROUND((i.deprn_amount*l_revenue_share/100),2);
553 ELSE
554 l_deprn_amount := i.deprn_amount;
555 END IF;
556
557 IF l_asset_deprn_tbl.COUNT > 0 THEN
558 l_record_status := 'NOT-ADDED';
559 -- records exist in the pl/sql table
560 FOR x IN l_asset_deprn_tbl.FIRST..l_asset_deprn_tbl.LAST
561 LOOP
562 IF l_record_status <> 'ADDED' THEN
563 IF l_asset_deprn_tbl(x).category_name = GET_CATEGORY_NAME(i.asset_category_id) THEN
564 --asset category is the same
565 IF l_asset_deprn_tbl(x).period_name = GET_PERIOD_NAME(i.date_accrual) THEN
566 -- period is the same
567 IF i.accrual_activity = 'CATCH-UP' THEN
568 l_asset_deprn_tbl(x).deprn_amount := l_asset_deprn_tbl(x).deprn_amount + l_deprn_amount;
569 l_record_status := 'ADDED';
570 ELSIF i.accrual_activity = 'NON-ACCRUAL' THEN
571 l_asset_deprn_tbl(x).deprn_amount := l_asset_deprn_tbl(x).deprn_amount - l_deprn_amount;
572 l_record_status := 'ADDED';
573 END IF; -- IF i.accrual_activity = 'CATCH-UP' THEN
574 END IF; --IF l_asset_deprn_tbl(x).period_name = GET_PERIOD_NAME(i.date_accrual) THEN
575 END IF; -- IF l_asset_deprn_tbl(x).category_name = GET_CATEGORY_NAME
576 END IF; -- IF record status
577 END LOOP; --FOR x IN l_asset_deprn_tbl.FIRST..l_asset_deprn_tbl.LAST
578
579 IF l_record_status <> 'ADDED' THEN
580 -- since record was not added, add the record to the table. Bug 2807825
581 l_asset_deprn_tbl(l_count).category_name := get_category_name(i.asset_category_id);
582 -- 11-mar-04. If record not added add record with correct sign of amount.
583 IF i.accrual_activity = 'CATCH-UP' THEN
584 l_asset_deprn_tbl(l_count).deprn_amount := l_deprn_amount;
585 ELSE
586 l_asset_deprn_tbl(l_count).deprn_amount := 0 - l_deprn_amount;
587 END IF;
588 l_asset_deprn_tbl(l_count).period_name := GET_PERIOD_NAME(i.date_accrual);
589 l_count := l_count+1;
590 l_record_status := 'ADDED';
591 END IF;
592 ELSE
593 -- no records in table, so create first record
594 --Bug 2754236. Add record to table based on accrual activity.
595 l_asset_deprn_tbl(l_count).category_name := get_category_name(i.asset_category_id);
596 IF i.accrual_activity = 'CATCH-UP' THEN
597 l_asset_deprn_tbl(l_count).deprn_amount := l_deprn_amount;
598 ELSE
599 l_asset_deprn_tbl(l_count).deprn_amount := 0 - l_deprn_amount;
600 END IF;
601 l_asset_deprn_tbl(l_count).period_name := GET_PERIOD_NAME(i.date_accrual);
602 l_count := l_count+1;
603 END IF; -- IF l_asset_deprn_tbl.COUNT > 0 THEN
604 END LOOP; --FOR i IN asset_deprn_csr(l_contract_id)
605
606 EXCEPTION
607 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
608
609 --close open cursors
610 IF agr_num_csr%ISOPEN THEN
611 CLOSE agr_num_csr;
612 END IF;
613
614 IF get_revenue_share_csr%ISOPEN THEN
615 CLOSE get_revenue_share_csr;
616 END IF;
617
618 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
619
620 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_tbl);
621 IF (l_error_msg_tbl.COUNT > 0) THEN
622 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
623 LOOP
624 IF l_error_msg_tbl(i) IS NOT NULL THEN
625 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_tbl(i));
626 END IF;
627 END LOOP;
628 END IF;
629
630 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
631
632 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
633
634 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_tbl);
635 IF (l_error_msg_tbl.COUNT > 0) THEN
636 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
637 LOOP
638 IF l_error_msg_tbl(i) IS NOT NULL THEN
639 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_tbl(i));
640 END IF;
641 END LOOP;
642 END IF;
643
644 WHEN OTHERS THEN
645 -- Select the contract for error reporting
646 OKL_API.SET_MESSAGE(p_app_name => g_app_name
647 ,p_msg_name => g_unexpected_error
648 ,p_token1 => g_sqlcode_token
649 ,p_token1_value => SQLCODE
650 ,p_token2 => g_sqlerrm_token
651 ,p_token2_value => SQLERRM);
652
653 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
654
655 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_tbl);
656 IF (l_error_msg_tbl.COUNT > 0) THEN
657 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
658 LOOP
659 IF l_error_msg_tbl(i) IS NOT NULL THEN
660 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_tbl(i));
661 END IF;
662 END LOOP;
663 END IF;
664 END; -- END of sub PL/SQL Block
665
666 END LOOP;
667 END IF;
668
669 IF l_asset_deprn_tbl.COUNT > 0 THEN
670 FOR j IN l_asset_deprn_tbl.FIRST..l_asset_deprn_tbl.LAST
671 LOOP
672 IF SIGN(l_asset_deprn_tbl(j).deprn_amount) = -1 THEN
673 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_asset_deprn_tbl(j).category_name,35)||
674 RPAD(l_asset_deprn_tbl(j).period_name,20)||
675 RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_ROLLBACK_DEPRN'),23)||
676 LPAD(ABS(l_asset_deprn_tbl(j).deprn_amount),18));
677 ELSE
678 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_asset_deprn_tbl(j).category_name,35)||
679 RPAD(l_asset_deprn_tbl(j).period_name,20)||
680 RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_ADA_RPT_ADD_DEPRN'),23)||
681 LPAD(ABS(l_asset_deprn_tbl(j).deprn_amount),18));
682 END IF;
683 END LOOP;
684 END IF;
685
686 retcode := 0;
687 l_return_status := OKL_API.G_RET_STS_SUCCESS;
688
689 EXCEPTION
690 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
691 --close open cursors
692 IF org_name_csr%ISOPEN THEN
693 CLOSE org_name_csr;
694 END IF;
695
696 IF sty_id_csr%ISOPEN THEN
697 CLOSE sty_id_csr;
698 END IF;
699
700 -- set return status needed in report
701 l_return_status := Okl_Api.G_RET_STS_ERROR;
702
703 -- print the error message in the log file
704 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_STATUS')
705 ||' '||l_return_status);
706 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
707 IF (l_outer_error_msg_tbl.COUNT > 0) THEN
708 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
709 LOOP
710 IF l_outer_error_msg_tbl(i) IS NOT NULL THEN
711 FND_FILE.PUT_LINE(FND_FILE.LOG,l_outer_error_msg_tbl(i));
712 END IF;
713 END LOOP;
714 END IF;
715
716 retcode := 2;
717
718 WHEN OTHERS THEN
719 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
720
721 -- print the error message in the log file
722 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_STATUS')
723 ||' '||l_return_status);
724 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
725 IF (l_outer_error_msg_tbl.COUNT > 0) THEN
726 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
727 LOOP
728 IF l_outer_error_msg_tbl(i) IS NOT NULL THEN
729 FND_FILE.PUT_LINE(FND_FILE.LOG,l_outer_error_msg_tbl(i));
730 END IF;
731 END LOOP;
732 END IF;
733
734 errbuf := SQLERRM;
735 retcode := 2;
736 FND_FILE.PUT_LINE(FND_FILE.LOG,errbuf);
737
738 END ADJUST_DEPRECIATION;
739
740 END OKL_ACCRUAL_DEPRN_ADJ_PVT;
[Home] [Help]