[Home] [Help]
PACKAGE BODY: APPS.OKL_LOSS_PROV_PVT
Source
1 package body OKL_LOSS_PROV_PVT AS
2 /* $Header: OKLRLPVB.pls 120.28.12010000.3 2008/09/05 22:46:35 smereddy ship $ */
3
4 -- Bug 5935176 dpsingh for AE signature Uptake start
5 CURSOR get_dff_fields(p_khr_id NUMBER) IS
6 SELECT ATTRIBUTE_CATEGORY,
7 ATTRIBUTE1,
8 ATTRIBUTE2,
9 ATTRIBUTE3,
10 ATTRIBUTE4,
11 ATTRIBUTE5,
12 ATTRIBUTE6,
13 ATTRIBUTE7,
14 ATTRIBUTE8,
15 ATTRIBUTE9,
16 ATTRIBUTE10,
17 ATTRIBUTE11,
18 ATTRIBUTE12,
19 ATTRIBUTE13,
20 ATTRIBUTE14,
21 ATTRIBUTE15
22 FROM OKL_K_HEADERS
23 WHERE ID = p_khr_id ;
24 -- Bug 5935176 dpsingh for AE signature Uptake end
25 -- this function is used to calculate net book value for a contract
26 FUNCTION calculate_cntrct_nbv (p_cntrct_id IN NUMBER) RETURN NUMBER
27 IS
28 l_asset_id OKX_ASSET_LINES_V.ASSET_ID%TYPE;
29 l_original_cost OKX_ASSET_LINES_V.ORIGINAL_COST%TYPE;
30 l_deprn_amount NUMBER := 0;
31 l_asset_book_value NUMBER := 0;
32 l_net_book_value NUMBER := 0;
33 l_asset_number OKX_ASSET_LINES_V.ASSET_NUMBER%TYPE;
34 l_corporate_book OKX_ASSET_LINES_V.CORPORATE_BOOK%TYPE;
35
36 /* cursor for getting the assets and their original cost */
37 CURSOR okl_cntrct_assets_csr(p_khr_id IN NUMBER) IS
38 SELECT asset_id, original_cost, asset_number, corporate_book
39 FROM okx_asset_lines_v ast
40 WHERE ast.dnz_chr_id = p_khr_id;
41
42 /* cursor to get the depreciation amount */
43 CURSOR depr_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2) IS
44 SELECT MAX(dpr.deprn_reserve)
45 FROM OKX_AST_DPRTNS_V dpr
46 WHERE asset_id = p_asset_id
47 AND book_type_code = p_book_type_code;
48
49 BEGIN
50
51 /* loop for assets for a contract */
52 FOR cntrct_assets_rec IN okl_cntrct_assets_csr(p_cntrct_id)
53 LOOP
54 /* get the asset id and original cost */
55 l_asset_id := cntrct_assets_rec.asset_id;
56 l_original_cost := cntrct_assets_rec.original_cost;
57 l_asset_number := cntrct_assets_rec.asset_number;
58 l_corporate_book := cntrct_assets_rec.corporate_book;
59
60 /* retrieve the depreciation amount */
61 OPEN depr_csr(l_asset_id, l_corporate_book);
62 FETCH depr_csr INTO l_deprn_amount;
63 CLOSE depr_csr;
64 IF l_deprn_amount IS NULL THEN
65 Okl_Api.set_message(p_app_name => g_app_name,
66 p_msg_name => 'OKL_GLP_DEPR_ERROR',
67 p_token1 => 'ASSET_NUMBER',
68 p_token1_value => l_asset_number);
69 RAISE OKL_API.G_EXCEPTION_ERROR;
70 END IF;
71
72 /* calculate the asset book value */
73 l_asset_book_value := NVL(l_original_cost,0) - NVL(l_deprn_amount,0);
74
75 /* add the asset book value to the contract net book value */
76 l_net_book_value := l_net_book_value + l_asset_book_value;
77
78 END LOOP; /* end of loop for assets for a contract */
79
80 /* return the calculated net book value */
81 RETURN(l_net_book_value);
82
83 EXCEPTION
84 WHEN OKL_API.G_EXCEPTION_ERROR THEN
85 -- return null because of error
86 RETURN(NULL);
87
88 WHEN OTHERS THEN
89 IF depr_csr%ISOPEN THEN
90 CLOSE depr_csr;
91 END IF;
92 -- return null because of error
93 RETURN(NULL);
94 END calculate_cntrct_nbv;
95
96
97 -- this function is used to calculate total reserve amount for a contract
98 FUNCTION calculate_cntrct_rsrv_amt (p_cntrct_id IN NUMBER) RETURN NUMBER
99 IS
100 l_tot_rsrv_amt NUMBER := 0;
101
102 /* cursor to get the total reserve amount */
103 CURSOR rsrv_amt_csr(p_khr_id NUMBER) IS
104 SELECT SUM(NVL(AMOUNT,0)) tot_res_amt
105 FROM OKL_TRX_CONTRACTS
106 WHERE KHR_ID = p_khr_id
107 AND tcn_type = 'PSP'
108 --Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
109 AND tsu_code = 'PROCESSED'
110 -- SGIYER.MGAAP Changes for Bug 7263041
111 AND representation_type = 'PRIMARY';
112
113 BEGIN
114
115 /* retrieve the residual amount */
116 OPEN rsrv_amt_csr(p_cntrct_id);
117 FETCH rsrv_amt_csr INTO l_tot_rsrv_amt;
118 CLOSE rsrv_amt_csr;
119
120 RETURN(l_tot_rsrv_amt);
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 IF rsrv_amt_csr%ISOPEN THEN
125 CLOSE rsrv_amt_csr;
126 END IF;
127 /* return null because of error */
128 RETURN(NULL);
129
130 END calculate_cntrct_rsrv_amt;
131
132 -- this function is used to calculate net investment value for a contract
133 FUNCTION calculate_cntrct_niv (
134 p_cntrct_id IN NUMBER
135 ,p_loss_date IN DATE) RETURN NUMBER
136 IS
137 l_formula_name CONSTANT VARCHAR2(30) := 'LP_NET_INVESTMENT_VALUE';
138 l_api_version CONSTANT NUMBER := 1.0;
139 l_init_msg_list VARCHAR2(20) DEFAULT Okl_Api.G_FALSE;
140 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
141 l_msg_count NUMBER;
142 l_msg_data VARCHAR2(2000);
143 l_net_invest_value NUMBER := 0;
144 l_ctxt_val_tbl Okl_Account_Dist_Pub.ctxt_val_tbl_type;
145
146 BEGIN
147
148 l_ctxt_val_tbl(1).NAME := 'p_provision_date';
149 l_ctxt_val_tbl(1).VALUE := TO_CHAR(p_loss_date, 'MM/DD/YYYY');
150
151 /* use the formula engine for the calculation */
152 Okl_Execute_Formula_Pub.EXECUTE
153 (p_api_version => l_api_version
154 ,p_init_msg_list => l_init_msg_list
155 ,x_return_status => l_return_status
156 ,x_msg_count => l_msg_count
157 ,x_msg_data => l_msg_data
158 ,p_formula_name => l_formula_name
159 ,p_contract_id => p_cntrct_id
160 ,p_line_id => NULL
161 ,p_additional_parameters => l_ctxt_val_tbl
162 ,x_value => l_net_invest_value);
163
164 -- store the highest degree of error
165 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
166 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
167 -- need to leave
168 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
169 ELSE
170 RAISE OKL_API.G_EXCEPTION_ERROR;
171 END IF;
172 END IF;
173
174
175 /* return the calculated net book value */
176 RETURN(l_net_invest_value);
177
178 EXCEPTION
179 WHEN OKL_API.G_EXCEPTION_ERROR THEN
180 -- return null because of error
181 RETURN(NULL);
182 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
183 -- return null because of error
184 RETURN(NULL);
185
186 WHEN OTHERS THEN
187 -- return null because of error
188 RETURN(NULL);
189
190 END calculate_cntrct_niv;
191
192 FUNCTION get_contract_principal_balance(p_cntrct_id IN NUMBER,
193 p_period_start_date IN DATE,
194 p_period_end_date IN DATE)
195 RETURN NUMBER IS
196
197 l_period_start_date DATE := p_period_start_date;
198 l_period_end_date DATE := p_period_end_date;
199 l_principal_bal NUMBER;
200 l_contract_number VARCHAR2(2000);
201 l_last_int_calc_date DATE;
202 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
203 l_kle_id NUMBER;
204 l_prin_bal_id NUMBER;
205
206 -- cursor to get the contract number
207 CURSOR contract_num_csr IS
208 SELECT contract_number
209 FROM OKC_K_HEADERS_B
210 WHERE id = p_cntrct_id;
211
212 CURSOR principal_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_end_date DATE, p_prin_bal_id NUMBER) IS
213 SELECT SUM(ste.amount)
214 FROM OKL_STRM_TYPE_B sty,
215 OKL_STREAMS stm,
216 OKL_STRM_ELEMENTS ste
217 WHERE stm.khr_id = p_ctr_id
218 AND stm.active_yn = 'Y'
219 AND stm.say_code = 'CURR'
220 AND sty.id = p_prin_bal_id
221 AND stm.sty_id = sty.id
222 AND ste.stm_id = stm.id
223 AND ste.stream_element_date BETWEEN p_start_date AND p_end_date;
224
225 -- cursor for retrieveing earlier principal balance amount if principal balance
226 -- for given period is not found
227 CURSOR prior_prin_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_prin_bal_id NUMBER) IS
228 SELECT SUM(ste.amount)
229 FROM OKL_STRM_TYPE_B sty,
230 OKL_STREAMS stm,
231 OKL_STRM_ELEMENTS ste
232 WHERE stm.khr_id = p_ctr_id
233 AND stm.active_yn = 'Y'
234 AND stm.say_code = 'CURR'
235 AND sty.id = p_prin_bal_id
236 AND stm.sty_id = sty.id
237 AND ste.stm_id = stm.id
238 AND ste.stream_element_date = (SELECT MAX(stream_element_date)
239 FROM OKL_STRM_TYPE_B sty,
240 OKL_STREAMS stm,
241 OKL_STRM_ELEMENTS ste
242 WHERE stm.khr_id = p_ctr_id
243 AND stm.active_yn = 'Y'
244 AND stm.say_code = 'CURR'
245 AND sty.id = p_prin_bal_id
246 AND stm.sty_id = sty.id
247 AND ste.stm_id = stm.id
248 AND stream_element_date < p_start_date);
249
250 BEGIN
251
252 FOR i IN contract_num_csr
253 LOOP
254 l_contract_number := i.contract_number;
255 END LOOP;
256
257 IF l_contract_number IS NULL THEN
258 Okl_Api.set_message(p_app_name => g_app_name,
259 p_msg_name => 'OKL_REV_LPV_CNTRCT_NUM_ERROR');
260 RAISE OKL_API.G_EXCEPTION_ERROR;
261 END IF;
262
263 IF l_period_end_date IS NULL THEN
264 Okl_Api.Set_Message(p_app_name => g_app_name,
265 p_msg_name => 'OKL_AGN_FE_PERD_END_DATE');
266 RAISE Okl_Api.G_EXCEPTION_ERROR;
267 END IF;
268
269 IF l_period_start_date IS NULL THEN
270 Okl_Api.Set_Message(p_app_name => g_app_name,
271 p_msg_name => 'OKL_AGN_FE_PERD_START_DATE');
272 RAISE Okl_Api.G_EXCEPTION_ERROR;
273 END IF;
274
275 OKL_STREAMS_UTIL.get_dependent_stream_type(
276 p_khr_id => p_cntrct_id,
277 p_primary_sty_purpose => 'RENT',
278 p_dependent_sty_purpose => 'PRINCIPAL_BALANCE',
279 x_return_status => l_return_status,
280 x_dependent_sty_id => l_prin_bal_id);
281
282 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
283 Okl_Api.set_message(p_app_name => g_app_name,
284 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
285 p_token1 => 'STREAM_NAME',
286 p_token1_value => 'PRINCIPAL BALANCE');
287 RAISE Okl_Api.G_EXCEPTION_ERROR;
288 END IF;
289
290 OPEN principal_bal_csr (p_cntrct_id, l_period_start_date, l_period_end_date, l_prin_bal_id);
291 FETCH principal_bal_csr INTO l_principal_bal;
292 CLOSE principal_bal_csr;
293
294 -- If principal balance not found for date range, get prior principal balance.
295 -- As per MMITTAL.
296 IF l_principal_bal IS NULL THEN
297 OPEN prior_prin_bal_csr(p_cntrct_id, l_period_start_date,l_prin_bal_id);
298 FETCH prior_prin_bal_csr INTO l_principal_bal;
299 CLOSE prior_prin_bal_csr;
300
301 IF l_principal_bal IS NULL THEN
302 Okl_Api.Set_Message(p_app_name => g_app_name,
303 p_msg_name => 'OKL_AGN_FE_PRIN_BAL',
304 p_token1 => 'CONTRACT_NUMBER',
305 p_token1_value => l_contract_number);
306 RAISE Okl_Api.G_EXCEPTION_ERROR;
307 END IF;
308 END IF;
309
310 RETURN l_principal_bal;
311
312 EXCEPTION
313 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
314 RETURN NULL;
315
316 WHEN OTHERS THEN
317 Okl_Api.SET_MESSAGE(
318 p_app_name => G_APP_NAME,
319 p_msg_name => G_UNEXPECTED_ERROR,
320 p_token1 => G_SQLCODE_TOKEN,
321 p_token1_value => SQLCODE,
322 p_token2 => G_SQLERRM_TOKEN,
323 p_token2_value => SQLERRM);
324 RETURN NULL;
325 END get_contract_principal_balance;
326
327 -- this function is used to calculate principal balance for a contract
328 FUNCTION calculate_cntrct_prin_bal (
329 p_cntrct_id IN NUMBER
330 ,p_period_start_date IN DATE
331 ,p_period_end_date IN DATE ) RETURN NUMBER
332 IS
333 l_ctxt_val_tbl Okl_Execute_Formula_Pub.ctxt_val_tbl_type;
334 l_principal_balance NUMBER;
335 BEGIN
336 --changed pl/sql table structure from l_ctxt_val_tbl to g_additional_parameters
337 -- Bug 3348162. Added format mask.
338 Okl_Execute_Formula_Pub.g_additional_parameters(1).name := 'p_period_start_date';
339 Okl_Execute_Formula_Pub.g_additional_parameters(1).value := TO_CHAR(p_period_start_date, 'MM/DD/YYYY');
340 Okl_Execute_Formula_Pub.g_additional_parameters(2).name := 'p_period_end_date';
341 Okl_Execute_Formula_Pub.g_additional_parameters(2).value := TO_CHAR(p_period_end_date, 'MM/DD/YYYY');
342
343 l_principal_balance := OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE
344 (p_khr_id => p_cntrct_id,
345 p_kle_id => NULL);
346
347 RETURN l_principal_balance;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 /* return null because of error */
352 RETURN NULL;
353
354 END calculate_cntrct_prin_bal;
355
356
357 -- this function is used to calculate net book value or net investment value
358 -- or principal balance for a contract based on the deal type provided.
359 FUNCTION calculate_capital_balance(p_cntrct_id IN NUMBER
360 ,p_deal_type IN VARCHAR2) RETURN NUMBER
361 IS
362
363 l_oper_lease CONSTANT OKL_K_HEADERS.DEAL_TYPE%TYPE := 'LEASEOP';
364 l_df_lease CONSTANT OKL_K_HEADERS.DEAL_TYPE%TYPE := 'LEASEDF';
365 l_sales_lease CONSTANT OKL_K_HEADERS.DEAL_TYPE%TYPE := 'LEASEST';
366 l_loan_lease CONSTANT OKL_K_HEADERS.DEAL_TYPE%TYPE := 'LOAN';
367 l_capital_bal NUMBER := 0;
368 l_original_amt NUMBER := 0;
369 l_converted_amt NUMBER := 0;
370 l_loss_date DATE := TRUNC(sysdate);
371 l_period_start_date DATE;
372 l_period_end_date DATE;
373 l_period_name VARCHAR2(2000);
374 l_func_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
375 l_khr_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
376 x_contract_currency OKL_K_HEADERS_FULL_V.currency_code%TYPE;
377 x_currency_conversion_type OKL_K_HEADERS_FULL_V.currency_conversion_type%TYPE;
378 x_currency_conversion_rate OKL_K_HEADERS_FULL_V.currency_conversion_rate%TYPE;
379 x_currency_conversion_date OKL_K_HEADERS_FULL_V.currency_conversion_date%TYPE;
380
381 -- Cursor to select currency information for the contract. Bug 2712001
382 CURSOR currency_info_csr(p_khr_id NUMBER) IS
383 SELECT currency_code
384 FROM OKC_K_HEADERS_B
385 WHERE id = p_khr_id;
386
387 -- cursor to get the rev rec method .. racheruv. Bug 6342556
388 CURSOR get_rev_rec_method_csr(p_chr_id NUMBER) IS
389 SELECT pdt.quality_val revenue_recognition_method
390 FROM OKL_PROD_QLTY_VAL_UV pdt,
391 OKL_K_HEADERS_FULL_V khr
392 WHERE khr.id = p_chr_id
393 AND khr.pdt_id = pdt.pdt_id
394 AND pdt.quality_name = 'REVENUE_RECOGNITION_METHOD';
395
396 -- cursor to get the last interest calc date .. racheruv. Bug 6342556
397 CURSOR last_int_date_csr IS
398 SELECT TRUNC(DATE_LAST_INTERIM_INTEREST_CAL)
399 FROM OKL_K_HEADERS
400 WHERE ID = p_cntrct_id;
401
402 l_last_int_calc_date date;
403 l_rev_rec_method_code varchar2(30);
404
405 BEGIN
406 /* calculate the nbv loss if contract is an operating lease */
407 IF p_deal_type = l_oper_lease THEN
408
409 OPEN currency_info_csr(p_cntrct_id);
410 FETCH currency_info_csr INTO l_khr_currency_code;
411 CLOSE currency_info_csr;
412 IF l_khr_currency_code IS NULL THEN
413 RAISE OKL_API.G_EXCEPTION_ERROR;
414 END IF;
415
416 -- retrieve the functional currency code
417 l_func_currency_code := Okl_Accounting_Util.GET_FUNC_CURR_CODE;
418 IF l_func_currency_code IS NULL THEN
419 RAISE OKL_API.G_EXCEPTION_ERROR;
420 END IF;
421
422 l_original_amt := calculate_cntrct_nbv (p_cntrct_id => p_cntrct_id);
423
424 -- convert NBV to contract currency. Bug 2712001
425 IF l_func_currency_code <> l_khr_currency_code THEN
426 OKL_ACCOUNTING_UTIL.convert_to_contract_currency
427 (p_khr_id => p_cntrct_id,
428 p_from_currency => l_func_currency_code,
429 p_transaction_date => l_loss_date,
430 p_amount => l_original_amt,
431 x_contract_currency => x_contract_currency,
432 x_currency_conversion_type => x_currency_conversion_type,
433 x_currency_conversion_rate => x_currency_conversion_rate,
434 x_currency_conversion_date => x_currency_conversion_date,
435 x_converted_amount => l_converted_amt);
436 IF l_converted_amt IS NULL THEN
437 RAISE Okl_Api.G_EXCEPTION_ERROR;
438 ELSE
439 l_capital_bal := l_converted_amt;
440 END IF;
441 ELSE
442 l_capital_bal := l_original_amt;
443 END IF;
444
445 /* calculate the niv loss if contract is a direct finance lease or sales lease */
446 ELSIF p_deal_type = l_df_lease OR p_deal_type = l_sales_lease THEN
447 l_capital_bal := calculate_cntrct_niv (p_cntrct_id => p_cntrct_id
448 ,p_loss_date => l_loss_date);
449
450 /* calculate the pb loss loss if contract is loan */
451 ELSIF p_deal_type = l_loan_lease THEN
452 -- get the rev rec method
453 OPEN get_rev_rec_method_csr(p_cntrct_id);
454 FETCH get_rev_rec_method_csr INTO l_rev_rec_method_code;
455 CLOSE get_rev_rec_method_csr;
456
457 -- if rev_rec_method is 'STREAMS' then use the period start and end dates else use
458 -- the last interest calc date and get the principal balance.. racheruv. Bug 6342556
459 IF l_rev_rec_method_code = 'STREAMS' THEN
460 -- Get period end date for principal balance
461 Okl_Accounting_Util.GET_PERIOD_INFO(l_loss_date,l_period_name,l_period_start_date,l_period_end_date);
462 l_capital_bal := get_contract_principal_balance(p_cntrct_id => p_cntrct_id
463 ,p_period_start_date => l_period_start_date
464 ,p_period_end_date => l_period_end_date);
465
466 ELSIF l_rev_rec_method_code in ('ESTIMATED_AND_BILLED', 'ACTUAL') THEN
467 -- get the last interest calc date.
468 OPEN last_int_date_csr;
469 FETCH last_int_date_csr into l_last_int_calc_date;
470 CLOSE last_int_date_csr;
471
472 Okl_Execute_Formula_Pub.g_additional_parameters(1).name := 'p_last_int_calc_date';
473 Okl_Execute_Formula_Pub.g_additional_parameters(1).value := TO_CHAR(l_last_int_calc_date, 'MM/DD/YYYY');
474
475 l_capital_bal := OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE
476 (p_khr_id => p_cntrct_id,
477 p_kle_id => NULL);
478 END IF;
479
480 END IF;
481
482 /* return the calculated net book value */
483 RETURN(l_capital_bal);
484
485 EXCEPTION
486 WHEN OKL_API.G_EXCEPTION_ERROR THEN
487 /* return null because of error */
488 RETURN(NULL);
489
490 WHEN OTHERS THEN
491 IF currency_info_csr%ISOPEN THEN
492 CLOSE currency_info_csr;
493 END IF;
494 /* return null because of error */
495 RETURN(NULL);
496
497 END calculate_capital_balance;
498
499 PROCEDURE GET_ACCOUNT_GEN_DETAILS(
500 p_contract_id IN NUMBER,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_acc_gen_primary_key_tbl OUT NOCOPY Okl_Account_Dist_Pub.acc_gen_primary_key) IS
503
504 -- Get Contract Salesperson
505 -- 30-Apr-2004. Bug 3596651. Cursor provided by Sarvanan.
506 CURSOR l_salesperson_csr (cp_chr_id IN NUMBER) IS
507 SELECT con.object1_id1
508 FROM OKC_K_HEADERS_B CHR,
509 OKC_CONTACT_SOURCES cso,
510 OKC_K_PARTY_ROLES_B kpr,
511 OKC_CONTACTS con
512 WHERE CHR.id = cp_chr_id
513 AND cso.cro_code = 'SALESPERSON'
514 AND cso.rle_code = 'LESSOR'
515 AND cso.buy_or_sell = CHR.buy_or_sell
516 AND kpr.chr_id = CHR.id
517 AND kpr.dnz_chr_id = CHR.id
518 AND kpr.rle_code = cso.rle_code
519 AND con.cpl_id = kpr.id
520 AND con.dnz_chr_id = CHR.id
521 AND con.cro_code = cso.cro_code
522 AND con.jtot_object1_code = cso.jtot_object_code;
523
524 CURSOR l_fin_sys_parms_csr IS
525 SELECT mo_global.get_current_org_id()
526 FROM dual;
527
528 -- Get Receivables Transaction Type
529 CURSOR l_cust_trx_type_csr IS
530 SELECT ctt.cust_trx_type_id
531 FROM ra_cust_trx_types ctt
532 WHERE ctt.name = 'Invoice-OKL';
533
534 -- cursor to get bill-to-site of customer at contract level
535 CURSOR chr_bill_to_site_csr (p_chr_id NUMBER) IS
536 SELECT bill_to_site_use_id
537 FROM OKC_K_HEADERS_B
538 WHERE id = p_chr_id;
539
540 l_sales_person_id OKC_CONTACTS_V.OBJECT1_ID1%TYPE;
541 l_counter NUMBER := 1;
542 l_org_id NUMBER;
543 l_receivables_trx_type VARCHAR2(2000);
544 l_bill_to_site VARCHAR2(2000);
545
546 BEGIN
547
548 x_return_status := OKL_API.G_RET_STS_SUCCESS;
549
550 -- Bug 3596651
551 -- **************************************************
552 -- Populate the account generator table with Contract Salesperson
553 -- **************************************************
554
555 OPEN l_salesperson_csr (p_contract_id);
556 FETCH l_salesperson_csr INTO l_sales_person_id;
557 CLOSE l_salesperson_csr;
558
559 IF l_sales_person_id IS NOT NULL THEN
560 x_acc_gen_primary_key_tbl(l_counter).source_table := 'JTF_RS_SALESREPS_MO_V';
561 x_acc_gen_primary_key_tbl(l_counter).primary_key_column := l_sales_person_id;
562 l_counter := l_counter + 1;
563 END IF;
564
565 -- Bug 3596651
566 -- **************************************************
567 -- Populate the account generator table with Operating Unit Identifier
568 -- **************************************************
569
570 OPEN l_fin_sys_parms_csr;
571 FETCH l_fin_sys_parms_csr INTO l_org_id;
572 CLOSE l_fin_sys_parms_csr;
573
574 IF l_org_id IS NOT NULL THEN
575 x_acc_gen_primary_key_tbl(l_counter).source_table:= 'FINANCIALS_SYSTEM_PARAMETERS';
576 x_acc_gen_primary_key_tbl(l_counter).primary_key_column := to_char(l_org_id);
577 l_counter := l_counter + 1;
578 END IF;
579
580 -- ********************************
581 -- Get Receivables Transaction Type
582 -- ********************************
583
584 OPEN l_cust_trx_type_csr;
585 FETCH l_cust_trx_type_csr INTO l_receivables_trx_type;
586 CLOSE l_cust_trx_type_csr;
587
588 IF l_receivables_trx_type IS NOT NULL THEN
589 x_acc_gen_primary_key_tbl(l_counter).source_table:= 'RA_CUST_TRX_TYPES';
590 x_acc_gen_primary_key_tbl(l_counter).primary_key_column := l_receivables_trx_type;
591 l_counter := l_counter + 1;
592 END IF;
593
594 OPEN chr_bill_to_site_csr(p_contract_id);
595 FETCH chr_bill_to_site_csr INTO l_bill_to_site;
596 CLOSE chr_bill_to_site_csr;
597
598 IF l_bill_to_site IS NOT NULL THEN
599 x_acc_gen_primary_key_tbl(l_counter).source_table:= 'AR_SITE_USES_V';
600 x_acc_gen_primary_key_tbl(l_counter).primary_key_column := l_bill_to_site;
601 END IF;
602
603 EXCEPTION
604 WHEN OTHERS THEN
605 IF l_salesperson_csr%ISOPEN THEN
606 CLOSE l_salesperson_csr;
607 END IF;
608
609 IF l_fin_sys_parms_csr%ISOPEN THEN
610 CLOSE l_fin_sys_parms_csr;
611 END IF;
612
613 IF l_cust_trx_type_csr%ISOPEN THEN
614 CLOSE l_cust_trx_type_csr;
615 END IF;
616
617 IF chr_bill_to_site_csr%ISOPEN THEN
618 CLOSE chr_bill_to_site_csr;
619 END IF;
620
621
622 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
623 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
624 p_msg_name => g_unexpected_error,
625 p_token1 => g_sqlcode_token,
626 p_token1_value => SQLCODE,
627 p_token2 => g_sqlerrm_token,
628 p_token2_value => SQLERRM);
629
630 END GET_ACCOUNT_GEN_DETAILS;
631
632 PROCEDURE CREATE_GEN_LOSS_TRX(
633 p_api_version IN NUMBER,
634 p_init_msg_list IN VARCHAR2,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_msg_count OUT NOCOPY NUMBER,
637 x_msg_data OUT NOCOPY VARCHAR2,
638 p_contract_id IN OKL_K_HEADERS_FULL_V.ID%TYPE,
639 p_contract_number IN OKL_K_HEADERS_FULL_V.CONTRACT_NUMBER%TYPE,
640 p_fact_synd_code IN VARCHAR2,
641 p_inv_acct_code IN VARCHAR2,
642 p_tcnv_rec IN OKL_TRX_CONTRACTS_PUB.tcnv_rec_type,
643 p_tclv_tbl IN OKL_TRX_CONTRACTS_PUB.tclv_tbl_type,
644 x_tcnv_rec OUT NOCOPY OKL_TRX_CONTRACTS_PUB.tcnv_rec_type,
645 x_tclv_tbl OUT NOCOPY OKL_TRX_CONTRACTS_PUB.tclv_tbl_type) IS
646
647 --local variables
648 l_api_name VARCHAR2(20) := 'CREATE_GEN_LOSS_TRX';
649 l_api_version CONSTANT NUMBER := 1.0;
650 l_tmpl_identify_rec Okl_Account_Dist_Pub.tmpl_identify_rec_type;
651 l_dist_info_rec Okl_Account_Dist_Pub.dist_info_rec_type;
652 l_ctxt_val_tbl Okl_Account_Dist_Pub.ctxt_val_tbl_type;
653 l_template_tbl Okl_Account_Dist_Pub.avlv_tbl_type;
654 l_amount_tbl Okl_Account_Dist_Pub.amount_tbl_type;
655 l_source_table CONSTANT OKL_TRNS_ACC_DSTRS.SOURCE_TABLE%TYPE := 'OKL_TXL_CNTRCT_LNS';
656 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
657 --START: Added by nikshah 19-Feb-2007 for SLA Uptake, Bug #5707866
658 l_tcn_id NUMBER;
659 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
660 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_type;
661 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
662 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
663 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
664 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
665 --END: Added by nikshah 19-Feb-2007 for SLA Uptake, Bug #5707866
666
667
668 BEGIN
669
670 -- Set save point
671 x_return_status := Okl_Api.START_ACTIVITY(l_api_name
672 ,G_PKG_NAME
673 ,p_init_msg_list
674 ,l_api_version
675 ,p_api_version
676 ,'_PVT'
677 ,x_return_status);
678
679 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
680 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
681 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
682 RAISE Okl_Api.G_EXCEPTION_ERROR;
683 END IF;
684
685 -- Call Transaction Public API to insert transaction header and line records
686 Okl_Trx_Contracts_Pub.create_trx_contracts
687 (p_api_version => p_api_version
688 ,p_init_msg_list => p_init_msg_list
689 ,x_return_status => x_return_status
690 ,x_msg_count => x_msg_count
691 ,x_msg_data => x_msg_data
692 ,p_tcnv_rec => p_tcnv_rec
693 ,p_tclv_tbl => p_tclv_tbl
694 ,x_tcnv_rec => x_tcnv_rec
695 ,x_tclv_tbl => x_tclv_tbl );
696 -- store the highest degree of error
697 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
698 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
699 -- need to leave
700 Okl_Api.set_message(p_app_name => g_app_name,
701 p_msg_name => 'OKL_AGN_TRX_CRE_ERROR',
702 p_token1 => g_contract_number_token,
703 p_token1_value => p_contract_number);
704 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
705 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
706 Okl_Api.set_message(p_app_name => g_app_name,
707 p_msg_name => 'OKL_AGN_TRX_CRE_ERROR',
708 p_token1 => g_contract_number_token,
709 p_token1_value => p_contract_number);
710 RAISE OKL_API.G_EXCEPTION_ERROR;
711 END IF;
712 END IF;
713
714 --get acc gen sources and value. Bug 3596651
715 GET_ACCOUNT_GEN_DETAILS(
716 p_contract_id => p_contract_id,
717 x_return_status => x_return_status,
718 x_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
719 --check for error
720 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
721 Okl_Api.set_message(p_app_name => g_app_name,
722 p_msg_name => 'OKL_AGN_ACC_GEN_ERROR',
723 p_token1 => g_contract_number_token,
724 p_token1_value => p_contract_number);
725 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
726 END IF;
727
728 --START: Changes by nikshah 19-Feb-2007 for SLA Uptake, Bug #5707866
729 l_tcn_id := x_tcnv_rec.id;
730
731 -- Build Accounting Record for creating actual entries for the catchup transactions
732 FOR i IN x_tclv_tbl.FIRST..x_tclv_tbl.LAST
733 LOOP
734 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
735 l_acc_gen_tbl(i).source_id := x_tclv_tbl(i).id;
736
737 l_tmpl_identify_tbl(i).product_id := x_tcnv_rec.pdt_id;
738 l_tmpl_identify_tbl(i).stream_type_id := x_tclv_tbl(i).sty_id;
739 l_tmpl_identify_tbl(i).transaction_type_id := x_tcnv_rec.try_id;
740 l_tmpl_identify_tbl(i).advance_arrears := NULL;
741 l_tmpl_identify_tbl(i).prior_year_yn := 'N';
742 l_tmpl_identify_tbl(i).memo_yn := 'N';
743 l_tmpl_identify_tbl(i).factoring_synd_flag := p_fact_synd_code;
744 l_tmpl_identify_tbl(i).investor_code := p_inv_acct_code;
745
746 l_dist_info_tbl(i).amount := x_tclv_tbl(i).amount;
747 l_dist_info_tbl(i).accounting_date := x_tcnv_rec.date_transaction_occurred;
748 l_dist_info_tbl(i).source_table := l_source_table;
749 l_dist_info_tbl(i).currency_code := x_tcnv_rec.currency_code;
750 l_dist_info_tbl(i).currency_conversion_type := x_tcnv_rec.currency_conversion_type;
751 l_dist_info_tbl(i).currency_conversion_rate := x_tcnv_rec.currency_conversion_rate;
752 l_dist_info_tbl(i).currency_conversion_date := x_tcnv_rec.currency_conversion_date;
753 l_dist_info_tbl(i).source_id := x_tclv_tbl(i).id;
754 l_dist_info_tbl(i).post_to_gl := 'Y';
755 l_dist_info_tbl(i).gl_reversal_flag := 'N';
756
757 END LOOP;
758
759 -- Call Okl_Account_Dist_Pub API to create accounting entries for this transaction
760 -- Call new signature
761 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
762 p_api_version => p_api_version,
763 p_init_msg_list => p_init_msg_list,
764 x_return_status => x_return_status,
765 x_msg_count => x_msg_count,
766 x_msg_data => x_msg_data,
767 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
768 p_dist_info_tbl => l_dist_info_tbl,
769 p_ctxt_val_tbl => l_ctxt_tbl,
770 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
771 x_template_tbl => l_template_out_tbl,
772 x_amount_tbl => l_amount_out_tbl,
773 p_trx_header_id => l_tcn_id);
774
775 -- store the highest degree of error
776 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
777 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
778 -- need to leave
779 Okl_Api.set_message(p_app_name => g_app_name,
780 p_msg_name => 'OKL_AGN_CRE_DIST_ERROR',
781 p_token1 => g_contract_number_token,
782 p_token1_value => p_contract_number);
783 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
784 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
785 -- record that there was an error
786 Okl_Api.set_message(p_app_name => g_app_name,
787 p_msg_name => 'OKL_AGN_CRE_DIST_ERROR',
788 p_token1 => g_contract_number_token,
789 p_token1_value => p_contract_number);
790 RAISE OKL_API.G_EXCEPTION_ERROR;
791 END IF;
792 END IF;
793 --END: Changes by nikshah 19-Feb-2007 for SLA Uptake, Bug #5707866
794
795 OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX
796 (p_api_version => p_api_version
797 ,p_init_msg_list => p_init_msg_list
798 ,x_return_status => x_return_status
799 ,x_msg_count => x_msg_count
800 ,x_msg_data => x_msg_data
801 ,P_TCNV_REC => x_tcnv_rec
802 ,P_TCLV_TBL => x_tclv_tbl
803 ,p_ctxt_val_tbl => l_ctxt_tbl
804 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
805
806 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
807 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
808 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
809 RAISE Okl_Api.G_EXCEPTION_ERROR;
810 END IF;
811
812 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
813 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
814
815 EXCEPTION
816 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
817 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
818 ,g_pkg_name
819 ,'OKL_API.G_RET_STS_ERROR'
820 ,x_msg_count
821 ,x_msg_data
822 ,'_PVT');
823 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
824 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
825 ,g_pkg_name
826 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
827 ,x_msg_count
828 ,x_msg_data
829 ,'_PVT');
830 WHEN OTHERS THEN
831 x_return_status := OKL_API.HANDLE_EXCEPTIONS
832 (l_api_name,
833 G_PKG_NAME,
834 'OTHERS',
835 x_msg_count,
836 x_msg_data,
837 '_PVT');
838
839 END CREATE_GEN_LOSS_TRX;
840
841 -- Function to call the General Loss Provisions Procedure
842 FUNCTION SUBMIT_GENERAL_LOSS(
843 x_return_status OUT NOCOPY VARCHAR2,
844 x_msg_count OUT NOCOPY NUMBER,
845 x_msg_data OUT NOCOPY VARCHAR2,
846 p_api_version IN NUMBER,
847 p_init_msg_list IN VARCHAR2,
848 p_glpv_rec IN glpv_rec_type
849 ) RETURN NUMBER IS
850
851 x_request_id NUMBER;
852 l_api_version CONSTANT NUMBER := 1;
853 l_api_name VARCHAR2(2000) := 'SUBMIT_GENERAL_LOSS';
854 l_product_id VARCHAR2(2000);
855 l_sty_id VARCHAR2(2000);
856 l_bucket_id VARCHAR2(2000);
857 l_entry_date VARCHAR2(2000);
858 l_tax_deductible_local VARCHAR2(2000);
859 l_tax_deductible_corporate VARCHAR2(2000);
860 l_description VARCHAR2(2000);
861 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
862 BEGIN
863
864 l_return_status := OKL_API.START_ACTIVITY(l_api_name
865 ,G_PKG_NAME
866 ,p_init_msg_list
867 ,l_api_version
868 ,p_api_version
869 ,'_PVT'
870 ,l_return_status);
871 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
872 RAISE okl_api.g_exception_unexpected_error;
873 ELSIF (l_return_Status = okl_api.g_ret_sts_error) THEN
874 RAISE okl_api.g_exception_error;
875 END IF;
876 -- validate input parameters
877 IF (p_glpv_rec.product_id = OKL_API.G_MISS_NUM OR
878 p_glpv_rec.product_id is NULL) THEN
879 Okl_Api.set_message(p_app_name => g_app_name,
880 p_msg_name => 'OKL_GLP_PDT_ERROR');
881 RAISE OKL_API.G_EXCEPTION_ERROR;
882 ELSE
883 l_product_id := to_char(p_glpv_rec.product_id);
884 END IF;
885
886 -- Bug 4110239. sty_id is not supported from 11.5.10+ version
887 --
888 -- IF (p_glpv_rec.sty_id = OKL_API.G_MISS_NUM OR
889 -- p_glpv_rec.sty_id is NULL) THEN
890 -- Okl_Api.set_message(p_app_name => g_app_name,
891 -- p_msg_name => 'OKL_GLP_PVN_ERROR');
892 -- RAISE OKL_API.G_EXCEPTION_ERROR;
893 -- ELSE
894 -- l_sty_id := to_char(p_glpv_rec.sty_id);
895 -- END IF;
896
897 IF (p_glpv_rec.bucket_id = OKL_API.G_MISS_NUM OR
898 p_glpv_rec.bucket_id is NULL) THEN
899 Okl_Api.set_message(p_app_name => g_app_name,
900 p_msg_name => 'OKL_GLP_BKT_ERROR');
901 RAISE OKL_API.G_EXCEPTION_ERROR;
902 ELSE
903 l_bucket_id := to_char(p_glpv_rec.bucket_id);
904 END IF;
905
906 IF (p_glpv_rec.entry_date = OKL_API.G_MISS_DATE OR
907 p_glpv_rec.entry_date IS NULL) THEN
908 Okl_Api.set_message(p_app_name => g_app_name,
909 p_msg_name => 'OKL_GLP_DATE_ERROR');
910 RAISE OKL_API.G_EXCEPTION_ERROR;
911 ELSE
912 l_entry_date := FND_DATE.DATE_TO_CANONICAL(p_glpv_rec.entry_date);
913 END IF;
914
915 IF (p_glpv_rec.tax_deductible_local = OKL_API.G_MISS_CHAR OR
916 p_glpv_rec.tax_deductible_local is NULL) THEN
917 l_tax_deductible_local := 'N';
918 ELSE
919 l_tax_deductible_local := p_glpv_rec.tax_deductible_local;
920 END IF;
921
922 IF (p_glpv_rec.tax_deductible_corporate = OKL_API.G_MISS_CHAR OR
923 p_glpv_rec.tax_deductible_corporate is NULL) THEN
924 l_tax_deductible_corporate := 'N';
925 ELSE
926 l_tax_deductible_corporate := p_glpv_rec.tax_deductible_corporate;
927 END IF;
928
929 l_description := p_glpv_rec.description;
930
931 -- Bug 4110239. sty_id is not supported from 11.5.10+ version
932 -- Submit Concurrent Program Request
933
934 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
935
936 x_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OKL',
937 program => 'OKLGLPCALC',
938 argument1 => l_product_id,
939 --argument2 => l_sty_id,
940 argument3 => l_bucket_id,
941 argument4 => l_entry_date,
942 argument5 => l_tax_deductible_local,
943 argument6 => l_tax_deductible_corporate,
944 argument7 => l_description);
945
946 IF x_request_id = 0 THEN
947 -- Handle submission error
948 -- Raise Error if the request has not been submitted successfully.
949 Okl_Api.SET_MESSAGE(G_APP_NAME, 'OKL_ERROR_SUB_CONC_PROG', 'CONC_PROG', 'General Loss Provision');
950 RAISE Okl_Api.G_EXCEPTION_ERROR;
951 ELSE
952 --set return status
953 x_return_status := l_return_status;
954 RETURN x_request_id;
955 END IF;
956
957 EXCEPTION
958 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
959 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
960 ,g_pkg_name
961 ,'OKL_API.G_RET_STS_ERROR'
962 ,x_msg_count
963 ,x_msg_data
964 ,'_PVT');
965 RETURN x_request_id;
966
967 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
968 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
969 ,g_pkg_name
970 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
971 ,x_msg_count
972 ,x_msg_data
973 ,'_PVT');
974 RETURN x_request_id;
975 WHEN OTHERS THEN
976 x_return_status := OKL_API.HANDLE_EXCEPTIONS
977 (l_api_name,
978 G_PKG_NAME,
979 'OTHERS',
980 x_msg_count,
981 x_msg_data,
982 '_PVT');
983 RETURN x_request_id;
984 END SUBMIT_GENERAL_LOSS;
985
986 -- Bug 4110239. p_sty_id is not supported from 11.5.10+ version
987 -- Removing p_sty_id parameter as API is not published.
988 PROCEDURE GENERAL_LOSS_PROVISION ( errbuf OUT NOCOPY VARCHAR2
989 ,retcode OUT NOCOPY NUMBER
990 ,p_product_id IN VARCHAR2
991 --,p_sty_id IN VARCHAR2
992 ,p_bucket_id IN VARCHAR2
993 ,p_entry_date IN VARCHAR2
994 ,p_tax_deductible_local IN VARCHAR2
995 ,p_tax_deductible_corporate IN VARCHAR2
996 ,p_description IN VARCHAR2)
997 IS
998
999 -- constants
1000 l_api_name CONSTANT VARCHAR2(40) := 'GENERAL_LOSS_PROVISION';
1001 l_api_version CONSTANT NUMBER := 1.0;
1002 l_try_name CONSTANT OKL_TRX_TYPES_V.NAME%TYPE := 'General Loss Provision';
1003 l_tcn_type CONSTANT OKL_TRX_CONTRACTS.TCN_TYPE%TYPE := 'PGL';
1004 l_tcl_type CONSTANT OKL_TXL_CNTRCT_LNS.TCL_TYPE%TYPE := 'PGL';
1005 ----Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
1006 l_tsu_code CONSTANT OKL_TRX_CONTRACTS.TSU_CODE%TYPE := 'PROCESSED';
1007 l_oper_lease CONSTANT OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE := 'LEASEOP';
1008 l_df_lease CONSTANT OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE := 'LEASEDF';
1009 l_sales_lease CONSTANT OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE := 'LEASEST';
1010 l_loan_lease CONSTANT OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE := 'LOAN';
1011 l_sysdate DATE := SYSDATE;
1012 l_init_msg_list VARCHAR2(2000) := OKL_API.G_FALSE;
1013 l_sob_name VARCHAR2(2000);
1014 l_org_id NUMBER;
1015 l_org_name VARCHAR2(2000);
1016 l_product_id OKL_TRX_CONTRACTS.PDT_ID%TYPE := to_number(p_product_id);
1017 l_product_name OKL_PRODUCTS_V.NAME%TYPE;
1018 l_bucket_id OKX_AGING_BUCKETS_V.AGING_BUCKET_ID%TYPE := to_number(p_bucket_id);
1019 l_entry_date DATE := FND_DATE.CANONICAL_TO_DATE(p_entry_date);
1020 l_cntrct_id OKL_K_HEADERS_FULL_V.ID%TYPE;
1021 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1022 l_try_id OKL_TRX_TYPES_V.ID%TYPE;
1023 l_deal_type OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE;
1024 l_least_bucket_rate OKL_BUCKETS_V.LOSS_RATE%TYPE;
1025 l_func_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
1026 l_khr_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
1027 l_currency_conv_type OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_TYPE%TYPE;
1028 l_currency_conv_rate OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_RATE%TYPE;
1029 l_currency_conv_date OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_DATE%TYPE;
1030 l_set_of_books_id OKL_SYS_ACCT_OPTS.SET_OF_BOOKS_ID%TYPE;
1031 l_error_cnt NUMBER :=1;
1032 l_period_start_date DATE;
1033 l_period_end_date DATE;
1034 l_period_name VARCHAR2(2000);
1035 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1036 l_precision VARCHAR2(2000);
1037 l_period_status VARCHAR2(1);
1038 l_product_subclass VARCHAR2(2000);
1039 l_counter NUMBER := 1;
1040 l_fact_sync_code VARCHAR2(2000);
1041 l_inv_acct_code VARCHAR2(2000);
1042 -- last interest calculation date .. racheruv. Bug 6342556
1043 l_last_int_calc_date DATE;
1044 l_rev_rec_method_code VARCHAR2(30);
1045
1046 -- record and table structure variables
1047 TYPE contract_error_tbl_type IS TABLE OF okl_k_headers_full_v.CONTRACT_NUMBER%TYPE INDEX BY BINARY_INTEGER;
1048 -- Bug# 3020763
1049 TYPE pdt_contracts_rec_type IS RECORD (
1050 contract_id OKL_K_HEADERS_FULL_V.ID%TYPE
1051 ,contract_number OKL_K_HEADERS_FULL_V.CONTRACT_NUMBER%TYPE
1052 ,deal_type OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE
1053 ,currency_code OKL_K_HEADERS_FULL_V.CURRENCY_CODE%TYPE
1054 ,currency_conversion_type OKL_K_HEADERS_FULL_V.CURRENCY_CONVERSION_TYPE%TYPE
1055 ,currency_conversion_rate OKL_K_HEADERS_FULL_V.CURRENCY_CONVERSION_RATE%TYPE
1056 ,currency_conversion_date OKL_K_HEADERS_FULL_V.CURRENCY_CONVERSION_DATE%TYPE);
1057
1058 TYPE pool_contents_rec_type is RECORD(
1059 lease_contract_id OKL_K_HEADERS_FULL_V.ID%TYPE
1060 ,sty_id OKL_STRM_TYPE_V.ID%TYPE
1061 ,sty_subclass OKL_STRM_TYPE_V.STREAM_TYPE_SUBCLASS%TYPE
1062 ,streams_to_date OKL_POOL_CONTENTS_V.streams_to_date%TYPE);
1063
1064 TYPE pdt_contracts_tbl_type IS TABLE OF pdt_contracts_rec_type INDEX BY BINARY_INTEGER;
1065 TYPE pool_contents_tbl_type IS TABLE OF pool_contents_rec_type INDEX BY BINARY_INTEGER;
1066
1067 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1068 l_int_calc_date_tbl date_tbl_type;
1069
1070 l_contract_error_tbl contract_error_tbl_type;
1071 l_outer_error_msg_tbl Okl_Accounting_Util.ERROR_MESSAGE_TYPE;
1072 l_bktv_tbl bucket_tbl_type;
1073 l_pdt_contracts_rec pdt_contracts_rec_type;
1074 l_pdt_contracts_tbl pdt_contracts_tbl_type;
1075 pool_contents_tbl pool_contents_tbl_type;
1076 --Added by dpsingh for LE Uptake
1077 l_legal_entity_id NUMBER;
1078
1079 -- cursor for getting contracts belonging to a lease product
1080 -- 29-MAY-02 sgiyer added where clause to select only active contracts
1081 -- 13-DEC-02 sgiyer added where clause to select lease contracts only
1082 -- changed where clause for performance
1083 -- and added multi-currency columns in select criteria
1084 -- bug 3448020. removing under revision status
1085 CURSOR okl_pdt_cntrcts_csr (p_pdt_id OKL_TRX_CONTRACTS.PDT_ID%TYPE) IS
1086 SELECT chr.id
1087 ,chr.contract_number
1088 ,khr.deal_type
1089 ,chr.currency_code
1090 ,khr.currency_conversion_type
1091 ,khr.currency_conversion_rate
1092 ,khr.currency_conversion_date
1093 -- get the last interest calc date .. racheruv. Bug 6342556
1094 ,TRUNC(nvl(khr.date_last_interim_interest_cal, chr.start_date)) last_int_calc_date
1095 FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr
1096 WHERE khr.pdt_id = p_pdt_id
1097 AND chr.id = khr.id
1098 AND sts_code IN ('BOOKED','EVERGREEN')
1099 AND chr.scs_code = 'LEASE';
1100
1101
1102 -- cursor for getting a contract belonging to a Investor product
1103 CURSOR investor_cntrcts_csr (p_pdt_id OKL_TRX_CONTRACTS.PDT_ID%TYPE) IS
1104 SELECT chr.id
1105 ,chr.contract_number
1106 ,khr.deal_type
1107 ,chr.currency_code
1108 ,khr.currency_conversion_type
1109 ,khr.currency_conversion_rate
1110 ,khr.currency_conversion_date
1111 FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr
1112 WHERE khr.pdt_id = p_pdt_id
1113 AND chr.id = khr.id
1114 AND sts_code = 'ACTIVE'
1115 AND chr.scs_code = 'INVESTOR';
1116
1117
1118 -- cursor to retrieve the aging bucket lines
1119 CURSOR bucket_lines_csr (l_bucket_id OKX_AGING_BUCKETS_V.AGING_BUCKET_ID%TYPE) IS
1120 SELECT okx.aging_bucket_line_id, bkt.id bkt_id, okx.bucket_name, okx.days_start, okx.days_to, bkt.loss_rate
1121 FROM OKX_AGING_BUCKETS_V okx, OKL_BUCKETS_V bkt
1122 WHERE okx.aging_bucket_id = l_bucket_id
1123 AND okx.aging_bucket_line_id = bkt.ibc_id
1124 AND bkt.end_date IS NULL
1125 ORDER by okx.bucket_sequence_num;
1126
1127 -- cursor to get transaction type id
1128 CURSOR trx_types_csr IS
1129 SELECT id
1130 FROM OKL_TRX_TYPES_TL
1131 WHERE NAME = l_try_name
1132 AND LANGUAGE = 'US';
1133
1134 -- cursor to get stream type name
1135 -- CURSOR sty_type_csr IS
1136 -- SELECT stytl.NAME
1137 -- FROM OKL_STRM_TYPE_TL stytl
1138 -- WHERE stytl.id = l_sty_id
1139 -- AND stytl.LANGUAGE = USERENV('LANG');
1140
1141 -- cursor to get the least loss rate for the bucket
1142 CURSOR least_rate_csr (p_aging_bucket_line_id OKX_AGING_BUCKETS_V.AGING_BUCKET_LINE_ID%TYPE) IS
1143 SELECT loss_rate
1144 FROM OKL_BUCKETS_V
1145 WHERE IBC_ID = p_aging_bucket_line_id
1146 AND end_date IS NULL;
1147
1148 -- cursor to get product name
1149 --Used base tables in query for performance by dkagrawa
1150 CURSOR pdt_csr IS
1151 SELECT pdt.name name, pqy.name product_subclass
1152 FROM okl_products pdt,
1153 okl_pdt_pqy_vals pqv,
1154 okl_pdt_qualitys pqy,
1155 okl_pqy_values qve
1156 WHERE pdt.id = pqv.pdt_id
1157 AND pqv.qve_id = qve.id
1158 AND qve.pqy_id = pqy.id
1159 AND pqy.name IN ('LEASE','INVESTOR')
1160 AND pdt.id = l_product_id;
1161
1162 -- cursor to get precision
1163 CURSOR precision_csr(p_curr_code FND_CURRENCIES_VL.currency_code%TYPE) IS
1164 SELECT PRECISION
1165 FROM fnd_currencies_vl
1166 WHERE currency_code = p_curr_code
1167 AND enabled_flag = 'Y'
1168 AND NVL(start_date_active, l_sysdate) <= l_sysdate
1169 AND NVL(end_date_active, l_sysdate) >= l_sysdate;
1170
1171 -- Cursor to select currency conversion information
1172 -- CURSOR currency_conv_csr(p_conversion_type VARCHAR2, p_from_currency VARCHAR2, p_to_currency VARCHAR2, p_conversion_date DATE) IS
1173 -- SELECT conversion_rate
1174 -- FROM GL_DAILY_RATES
1175 -- WHERE conversion_type = p_conversion_type
1176 -- AND conversion_date = p_conversion_date
1177 -- AND from_currency = p_from_currency
1178 -- AND to_currency = p_to_currency
1179 -- AND status_code = 'C';
1180
1181 -- cursor to check whether the contract has a specific loss
1182 CURSOR sp_loss_trx_csr (p_khr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
1183 SELECT 'Y'
1184 FROM OKL_TRX_CONTRACTS
1185 WHERE khr_id = p_khr_id
1186 AND tcn_type = 'PSP'
1187 AND tsu_code = l_tsu_code
1188 AND representation_type = 'PRIMARY'; -- SGIYER MGAAP Changes Bug 7263041
1189
1190 -- cursor to check whether the contract has a general loss
1191 CURSOR gen_loss_trx_csr (p_khr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
1192 SELECT 'Y'
1193 FROM OKL_TRX_CONTRACTS
1194 WHERE khr_id = p_khr_id
1195 AND tcn_type = 'PGL'
1196 AND tsu_code = l_tsu_code
1197 AND representation_type = 'PRIMARY'; -- SGIYER MGAAP Changes Bug 7263041
1198
1199 -- cursor for retrieving the unpaid invoices for a contract
1200 --Bug 2969989. Not all outstanding invoices need to be considered.Consider
1201 --only those less than or equal to provision date.
1202 -- Bug 4058948. Changing view from okl_bpd_leasing_payment_trx_v to okl_bpd_contract_invoices_v.
1203 -- Also adding new where condition to exclude invoices subject to cash receipt.
1204 CURSOR cntrct_invcs_csr (p_khr_id OKL_K_HEADERS_FULL_V.ID%TYPE, p_entry_date DATE) IS
1205 SELECT amount_due_remaining, due_date
1206 FROM okl_bpd_contract_invoices_v
1207 WHERE contract_id = p_khr_id
1208 AND status = 'OP'
1209 AND amount_due_remaining > 0
1210 AND revenue_rec_basis <> 'CASH_RECEIPT'
1211 AND due_date <= p_entry_date;
1212
1213 -- Cursor to select lease contracts within a pool for an investor agreement.
1214 CURSOR pool_contents_csr (p_agr_id NUMBER) IS
1215 SELECT chr.id chr_id,
1216 sty.id sty_id,
1217 sty.stream_type_subclass sty_subclass,
1218 poc.streams_to_date streams_to_date
1219 FROM OKL_POOLS pol,
1220 OKL_POOL_CONTENTS poc,
1221 OKL_STRM_TYPE_B sty,
1222 OKC_K_HEADERS_B chr,
1223 OKL_K_HEADERS khr
1224 WHERE pol.khr_id = p_agr_id
1225 AND pol.id = poc.pol_id
1226 AND poc.status_code = 'ACTIVE'
1227 AND poc.sty_id = sty.id
1228 --Bug 6740000 ssdeshpa Impact for Loan Contracts added into the pool start
1229 AND sty.stream_type_subclass IN ('RENT', 'RESIDUAL', 'LOAN_PAYMENT')
1230 --Bug 6740000 ssdeshpa End
1231 AND poc.khr_id = chr.id
1232 AND chr.id = khr.id;
1233
1234
1235 -- GSCC validation error. Removing hardcoded schema name APPS
1236 -- cursor to select open items for a contract
1237 -- Bug 4058948. Changing view from okl_bpd_leasing_payment_trx_v to okl_bpd_contract_invoices_v.
1238 -- Also adding new where condition to exclude invoices subject to cash receipt.
1239 CURSOR open_items_csr (p_khr_id NUMBER, p_sty_id NUMBER) IS
1240 SELECT amount_due_remaining, due_date
1241 FROM okl_bpd_contract_invoices_v
1242 WHERE contract_id = p_khr_id
1243 AND status = 'OP'
1244 AND amount_due_remaining > 0
1245 AND revenue_rec_basis <> 'CASH_RECEIPT'
1246 AND due_date <= l_entry_date
1247 AND stream_type_id = p_sty_id;
1248
1249 -- cursor to select residual value amount
1250 CURSOR residual_value_csr (p_khr_id NUMBER, p_sty_id NUMBER) IS
1251 SELECT ste.amount
1252 FROM OKL_STRM_TYPE_B sty,
1253 OKL_STREAMS stm,
1254 OKL_STRM_ELEMENTS ste
1255 WHERE stm.khr_id = p_khr_id
1256 AND stm.sty_id = sty.id
1257 AND sty.id = p_sty_id
1258 AND stm.id = ste.stm_id
1259 AND stm.active_yn = 'Y'
1260 AND stm.say_code = 'CURR';
1261
1262 -- cursor to get org name
1263 CURSOR org_name_csr(p_org_id NUMBER) IS
1264 SELECT name
1265 FROM hr_operating_units
1266 WHERE organization_id = p_org_id;
1267
1268 -- cursor to get the revenue recognition method.. racheruv. Bug 6342556
1269 CURSOR get_rev_rec_method_csr(p_chr_id NUMBER) IS
1270 SELECT pdt.quality_val revenue_recognition_method
1271 FROM OKL_PROD_QLTY_VAL_UV pdt,
1272 OKL_K_HEADERS_FULL_V khr
1273 WHERE khr.id = p_chr_id
1274 AND khr.pdt_id = pdt.pdt_id
1275 AND pdt.quality_name = 'REVENUE_RECOGNITION_METHOD';
1276
1277 BEGIN
1278
1279 -- get the set of books id
1280 l_set_of_books_id := OKL_ACCOUNTING_UTIL.GET_SET_OF_BOOKS_ID;
1281 IF (l_set_of_books_id IS NULL) THEN
1282 Okl_Api.set_message(p_app_name => g_app_name,
1283 p_msg_name => 'OKL_AGN_SOB_ID_ERROR');
1284 RAISE OKL_API.G_EXCEPTION_ERROR;
1285 END IF;
1286
1287 -- Find set of books name for report
1288 l_sob_name := Okl_Accounting_Util.GET_SET_OF_BOOKS_NAME(l_set_of_books_id);
1289
1290 -- Find org name for report
1291 l_org_id := mo_global.get_current_org_id();
1292 IF l_org_id IS NULL THEN
1293 -- store SQL error message on message stack for caller
1294 okl_api.set_message(p_app_name => G_APP_NAME,
1295 p_msg_name => G_INVALID_VALUE,
1296 p_token1 => G_COL_NAME_TOKEN,
1297 p_token1_value => 'l_org_id');
1298 RAISE OKL_API.G_EXCEPTION_ERROR;
1299 END IF;
1300
1301 OPEN org_name_csr(l_org_id);
1302 FETCH org_name_csr INTO l_org_name;
1303 CLOSE org_name_csr;
1304 IF l_org_name IS NULL THEN
1305 -- store SQL error message on message stack for caller
1306 okl_api.set_message(p_app_name => G_APP_NAME,
1307 p_msg_name => G_NO_MATCHING_RECORD,
1308 p_token1 => G_COL_NAME_TOKEN,
1309 p_token1_value => 'ORG_ID');
1310 RAISE OKL_API.G_EXCEPTION_ERROR;
1311 END IF;
1312
1313 -- Get Period info for PB
1314 Okl_Accounting_Util.GET_PERIOD_INFO(l_entry_date,l_period_name,l_period_start_date,l_period_end_date);
1315 IF l_period_name IS NULL THEN
1316 Okl_Api.set_message(p_app_name => g_app_name,
1317 p_msg_name => 'OKL_AGN_PERIOD_END_DATE');
1318 RAISE Okl_Api.G_EXCEPTION_ERROR;
1319 END IF;
1320
1321 --Bug# 2781593
1322 -- check for open period
1323 l_period_status := Okl_Accounting_Util.GET_OKL_PERIOD_STATUS(l_period_name);
1324 IF l_period_status IS NULL THEN
1325 Okl_Api.set_message(p_app_name => g_app_name,
1326 p_msg_name => 'OKL_AGN_PERIOD_STATUS_ERROR',
1327 p_token1 => 'PERIOD_NAME',
1328 p_token1_value => l_period_name);
1329 RAISE Okl_Api.G_EXCEPTION_ERROR;
1330 END IF;
1331
1332 IF l_period_status NOT IN('O','F') THEN
1333 Okl_Api.set_message(p_app_name => g_app_name,
1334 p_msg_name => 'OKL_AGN_OPEN_PERIOD_ERROR');
1335 RAISE Okl_Api.G_EXCEPTION_ERROR;
1336 END IF;
1337
1338 -- Find product details
1339 OPEN pdt_csr;
1340 FETCH pdt_csr INTO l_product_name, l_product_subclass;
1341 CLOSE pdt_csr;
1342 IF l_product_name IS NULL THEN
1343 -- store SQL error message on message stack for caller
1344 okl_api.set_message(p_app_name => G_APP_NAME,
1345 p_msg_name => G_NO_MATCHING_RECORD,
1346 p_token1 => G_COL_NAME_TOKEN,
1347 p_token1_value => 'l_product_id');
1348 RAISE OKL_API.G_EXCEPTION_ERROR;
1349 END IF;
1350
1351 IF l_product_subclass IS NULL THEN
1352 Okl_Api.set_message(p_app_name => g_app_name,
1353 p_msg_name => 'OKL_GLP_PDT_SUBCLASS');
1354 RAISE Okl_Api.G_EXCEPTION_ERROR;
1355 END IF;
1356
1357 -- Find currency code for the set of books id
1358 l_func_currency_code := Okl_Accounting_Util.GET_FUNC_CURR_CODE;
1359 IF (l_func_currency_code IS NULL) THEN
1360 -- store SQL error message on message stack for caller
1361 Okl_Api.set_message(p_app_name => g_app_name,
1362 p_msg_name => 'OKL_AGN_CURR_CODE_ERROR');
1363 RAISE OKL_API.G_EXCEPTION_ERROR;
1364 END IF;
1365
1366 -- retrieve the transaction type id
1367 OPEN trx_types_csr;
1368 FETCH trx_types_csr INTO l_try_id;
1369 CLOSE trx_types_csr;
1370 IF l_try_id IS NULL THEN
1371 -- store SQL error message on message stack for caller
1372 Okl_Api.set_message(p_app_name => g_app_name,
1373 p_msg_name => 'OKL_AGN_TRX_TYPE_ERROR',
1374 p_token1 => 'TRANSACTION_TYPE',
1375 p_token1_value => l_try_name);
1376 RAISE OKL_API.G_EXCEPTION_ERROR;
1377 END IF;
1378
1379 -- retrieve the bucket lines
1380 FOR i IN bucket_lines_csr(l_bucket_id)
1381 LOOP
1382 l_bktv_tbl(l_counter).aging_bucket_line_id := i.aging_bucket_line_id;
1383 l_bktv_tbl(l_counter).bkt_id := i.bkt_id;
1384 l_bktv_tbl(l_counter).bucket_name := i.bucket_name;
1385 l_bktv_tbl(l_counter).days_start := i.days_start;
1386 l_bktv_tbl(l_counter).days_to := i.days_to;
1387 l_bktv_tbl(l_counter).loss_rate := i.loss_rate;
1388 l_counter := l_counter + 1;
1389 END LOOP;
1390
1391 IF l_bktv_tbl.COUNT = 0 THEN
1392 -- store SQL error message on message stack for caller
1393 Okl_Api.set_message(p_app_name => g_app_name,
1394 p_msg_name => 'OKL_GLP_BKT_NULL_ERROR');
1395 RAISE OKL_API.G_EXCEPTION_ERROR;
1396 END IF;
1397
1398 -- Bug 3557647. Invalid cursor error. Forgot to close cursor
1399 -- find the loss rate for the lowest bucket
1400 OPEN least_rate_csr(l_bktv_tbl(1).aging_bucket_line_id);
1401 FETCH least_rate_csr INTO l_least_bucket_rate;
1402 CLOSE least_rate_csr;
1403 IF l_least_bucket_rate IS NULL THEN
1404 -- store SQL error message on message stack for caller
1405 Okl_Api.set_message(p_app_name => g_app_name,
1406 p_msg_name => 'OKL_GLP_LOSS_RATE_ERROR');
1407 RAISE OKL_API.G_EXCEPTION_ERROR;
1408 END IF;
1409
1410 l_counter := 1;
1411 IF l_product_subclass = 'LEASE' THEN
1412 -- loop for contracts for a product
1413 -- Bug# 3020763 Looping and fetching into tbl to avoid rollback segment error.
1414 FOR pdt_cntrcts_rec IN okl_pdt_cntrcts_csr(l_product_id) LOOP
1415 l_pdt_contracts_tbl(l_counter).contract_id := pdt_cntrcts_rec.id;
1416 l_pdt_contracts_tbl(l_counter).deal_type := pdt_cntrcts_rec.deal_type;
1417 l_pdt_contracts_tbl(l_counter).contract_number := pdt_cntrcts_rec.contract_number;
1418 l_pdt_contracts_tbl(l_counter).currency_code := pdt_cntrcts_rec.currency_code;
1419 l_pdt_contracts_tbl(l_counter).currency_conversion_type := pdt_cntrcts_rec.currency_conversion_type;
1420 l_pdt_contracts_tbl(l_counter).currency_conversion_date := pdt_cntrcts_rec.currency_conversion_date;
1421 l_pdt_contracts_tbl(l_counter).currency_conversion_rate := pdt_cntrcts_rec.currency_conversion_rate;
1422 l_int_calc_date_tbl(l_counter) := pdt_cntrcts_rec.last_int_calc_date;
1423 l_counter := l_counter + 1;
1424 END LOOP;
1425
1426 ELSIF l_product_subclass = 'INVESTOR' THEN
1427
1428 -- Bug 3557647. Fixed invalid cursor error.
1429 FOR pdt_cntrcts_rec IN investor_cntrcts_csr(l_product_id)
1430 LOOP
1431
1432 l_pdt_contracts_tbl(l_counter).contract_id := pdt_cntrcts_rec.id;
1433 l_pdt_contracts_tbl(l_counter).deal_type := pdt_cntrcts_rec.deal_type;
1434 l_pdt_contracts_tbl(l_counter).contract_number := pdt_cntrcts_rec.contract_number;
1435 l_pdt_contracts_tbl(l_counter).currency_code := pdt_cntrcts_rec.currency_code;
1436 l_pdt_contracts_tbl(l_counter).currency_conversion_type := pdt_cntrcts_rec.currency_conversion_type;
1437 l_pdt_contracts_tbl(l_counter).currency_conversion_date := pdt_cntrcts_rec.currency_conversion_date;
1438 l_pdt_contracts_tbl(l_counter).currency_conversion_rate := pdt_cntrcts_rec.currency_conversion_rate;
1439 l_counter := l_counter + 1;
1440 END LOOP;
1441
1442 ELSE
1443 Okl_Api.set_message(p_app_name => g_app_name,
1444 p_msg_name => 'OKL_GLP_INVALID_SUBCLASS');
1445 RAISE OKL_API.G_EXCEPTION_ERROR;
1446 END IF;
1447
1448
1449 -- Create report header
1450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_HEADER'));
1451 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' '||FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_HEADER_LINE'));
1452 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
1453 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_SOB_TITLE')
1454 ||' '||RPAD(l_sob_name, 65)
1455 ||FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_OU_TITLE')
1456 ||' '||l_org_name);
1457 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_PROG_DATE_TITLE')
1458 ||' '||RPAD(l_sysdate, 61)||FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_PROV_DATE_TITLE')
1459 ||' '||l_entry_date);
1460 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_CURR_TITLE')
1461 ||' '||RPAD(l_func_currency_code,65));
1462 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
1463
1464 -- Create Report Content
1465 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_PROD_TITLE')||' '||l_product_name);
1466 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_STY_TITLE')||' '||l_sty_name);
1467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
1468
1469 IF l_product_subclass = 'LEASE' THEN
1470 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_CTR_NUM_TITLE'),28)
1471 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TRX_NUM_TITLE'),22)
1472 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TAXLOCAL_TITLE'),23)
1473 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TAXCORP_TITLE'),26)
1474 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_CURRENCY'),9)
1475 ||LPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AMT_TITLE'),17));
1476 ELSE
1477 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AGR_NUM_TITLE'),28)
1478 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TRX_NUM_TITLE'),22)
1479 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TAXLOCAL_TITLE'),23)
1480 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TAXCORP_TITLE'),26)
1481 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_CURRENCY'),9)
1482 ||LPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AMT_TITLE'),17));
1483 END IF;
1484
1485 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_CTR_LINE'),28)
1486 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TRX_LINE'),22)
1487 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TAXLOCAL_LINE'),23)
1488 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_TAXCORP_LINE'),26)
1489 ||RPAD(FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_CURR_UNDERLINE'),9)
1490 ||LPAD(FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AMT_LINE'),17));
1491
1492
1493 IF l_pdt_contracts_tbl.COUNT > 0 THEN
1494 -- Bug# 3020763 Processing within a pl/sql tbl instead of within the cursor itself.
1495 FOR x IN l_pdt_contracts_tbl.FIRST..l_pdt_contracts_tbl.LAST
1496 LOOP
1497
1498 l_cntrct_id := l_pdt_contracts_tbl(x).contract_id;
1499 l_deal_type := l_pdt_contracts_tbl(x).deal_type;
1500 l_cntrct_number := l_pdt_contracts_tbl(x).contract_number;
1501 l_khr_currency_code := l_pdt_contracts_tbl(x).currency_code;
1502 l_currency_conv_type := l_pdt_contracts_tbl(x).currency_conversion_type;
1503 l_currency_conv_date := l_pdt_contracts_tbl(x).currency_conversion_date;
1504 l_currency_conv_rate := l_pdt_contracts_tbl(x).currency_conversion_rate;
1505
1506 DECLARE
1507 -- Declare local variables which need to be re-initialized to null for each contract
1508 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1509 l_init_msg_list VARCHAR2(2000) := OKL_API.G_FALSE;
1510 x_msg_count NUMBER;
1511 x_msg_data VARCHAR2(2000);
1512 l_net_book_value NUMBER;
1513 l_net_invest_value NUMBER;
1514 l_principal_balance NUMBER;
1515 l_nbv_loss_amt NUMBER := 0;
1516 l_niv_loss_amt NUMBER := 0;
1517 l_pb_loss_amt NUMBER := 0;
1518 l_residual_loss_amt NUMBER := 0;
1519 l_residual_amt NUMBER := 0;
1520 l_total_residual_amt NUMBER := 0;
1521 l_line_count NUMBER := 1;
1522 l_due_date DATE;
1523 l_sp_loss_fnd VARCHAR2(1) := 'N';
1524 l_gen_loss_fnd VARCHAR2(1) := 'N';
1525 l_no_of_days NUMBER;
1526 l_due_amt NUMBER;
1527 l_inv_loss_amt NUMBER :=0;
1528 l_loss_rate OKL_BUCKETS_V.LOSS_RATE%TYPE;
1529 l_trx_header_total OKL_TRX_CONTRACTS.AMOUNT%TYPE := 0;
1530 l_converted_net_book_value NUMBER;
1531 l_sty_id OKL_TXL_CNTRCT_LNS.STY_ID%TYPE; --:= to_number(p_sty_id);
1532 l_sty_name OKL_STRM_TYPE_V.NAME%TYPE;
1533
1534
1535 -- creating variable required by okl_accounting_util.convert_to_contract_currency
1536 -- these variables not used anywhere.
1537 x_contract_currency OKL_K_HEADERS_FULL_V.currency_code%TYPE;
1538 x_currency_conversion_type OKL_K_HEADERS_FULL_V.currency_conversion_type%TYPE;
1539 x_currency_conversion_rate OKL_K_HEADERS_FULL_V.currency_conversion_rate%TYPE;
1540 x_currency_conversion_date OKL_K_HEADERS_FULL_V.currency_conversion_date%TYPE;
1541
1542 -- record and table structure variables
1543 l_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
1544 l_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
1545 x_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
1546 x_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
1547 l_lprv_rec OKL_REV_LOSS_PROV_PUB.lprv_rec_type;
1548 l_error_msg_tbl Okl_Accounting_Util.ERROR_MESSAGE_TYPE;
1549
1550 -- Begin a new PL/SQL block to trap errors related to a praticular contract and to move on to the next contract
1551 BEGIN
1552
1553 OKL_STREAMS_UTIL.get_primary_stream_type(
1554 p_khr_id => l_cntrct_id,
1555 p_primary_sty_purpose => 'GENERAL_LOSS_PROVISION',
1556 x_return_status => l_return_status,
1557 x_primary_sty_id => l_sty_id);
1558
1559 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1560 -- store SQL error message on message stack for caller and entry in log file
1561 Okl_Api.set_message(p_app_name => g_app_name,
1562 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1563 p_token1 => g_stream_name_token,
1564 p_token1_value => 'General Loss Provision');
1565 RAISE Okl_Api.G_EXCEPTION_ERROR;
1566 END IF;
1567
1568 -- Bug 4110239. No need for sty_name
1569 -- -- get the stream type name
1570 -- OPEN sty_type_csr;
1571 -- FETCH sty_type_csr INTO l_sty_name;
1572 -- CLOSE sty_type_csr;
1573 -- IF l_sty_name IS NULL THEN
1574 -- -- store SQL error message on message stack for caller
1575 -- okl_api.set_message(p_app_name => G_APP_NAME,
1576 -- p_msg_name => G_NO_MATCHING_RECORD,
1577 -- p_token1 => G_COL_NAME_TOKEN,
1578 -- p_token1_value => 'l_sty_id');
1579 -- RAISE OKL_API.G_EXCEPTION_ERROR;
1580 -- END IF;
1581
1582 -- Check contract currency against functional currency
1583 IF l_func_currency_code <> l_khr_currency_code THEN
1584 --validate data
1585 IF l_currency_conv_type IS NULL THEN
1586 Okl_Api.set_message(p_app_name => g_app_name,
1587 p_msg_name => 'OKL_AGN_CURR_TYPE_ERROR',
1588 p_token1 => g_contract_number_token,
1589 p_token1_value => l_cntrct_number);
1590 RAISE Okl_Api.G_EXCEPTION_ERROR;
1591 END IF;
1592 IF l_currency_conv_date IS NULL THEN
1593 Okl_Api.set_message(p_app_name => g_app_name,
1594 p_msg_name => 'OKL_AGN_CURR_DATE_ERROR',
1595 p_token1 => g_contract_number_token,
1596 p_token1_value => l_cntrct_number);
1597 RAISE Okl_Api.G_EXCEPTION_ERROR;
1598 END IF;
1599 IF l_currency_conv_type = 'User' THEN
1600 IF l_currency_conv_rate IS NULL THEN
1601 Okl_Api.set_message(p_app_name => g_app_name,
1602 p_msg_name => 'OKL_AGN_CURR_USER_RATE_ERROR',
1603 p_token1 => g_contract_number_token,
1604 p_token1_value => l_cntrct_number);
1605 RAISE Okl_Api.G_EXCEPTION_ERROR;
1606 END IF;
1607 ELSE
1608 -- OPEN currency_conv_csr(l_currency_conv_type, l_khr_currency_code, l_func_currency_code, l_currency_conv_date);
1609 -- FETCH currency_conv_csr INTO l_currency_conv_rate;
1610 -- IF currency_conv_csr%NOTFOUND THEN
1611 -- CLOSE currency_conv_csr;
1612 -- Okl_Api.set_message(p_app_name => g_app_name,
1613 -- p_msg_name => 'OKL_AGN_CURR_RATE_ERROR',
1614 -- p_token1 => 'CONVERSION_TYPE',
1615 -- p_token1_value => l_currency_conv_type,
1616 -- p_token2 => 'FROM_CURRENCY',
1617 -- p_token2_value => l_khr_currency_code,
1618 -- p_token3 => 'TO_CURRENCY',
1619 -- p_token3_value => l_func_currency_code
1620 -- );
1621 -- RAISE Okl_Api.G_EXCEPTION_ERROR;
1622 -- END IF;
1623 -- CLOSE currency_conv_csr;
1624 l_currency_conv_rate := OKL_ACCOUNTING_UTIL.get_curr_con_rate
1625 (p_from_curr_code => l_khr_currency_code,
1626 p_to_curr_code => l_func_currency_code,
1627 p_con_date => l_entry_date,--Bug 6970675
1628 p_con_type => l_currency_conv_type);
1629 IF l_currency_conv_rate IS NULL THEN
1630 Okl_Api.set_message(p_app_name => g_app_name,
1631 p_msg_name => 'OKL_AGN_CURR_RATE_ERROR',
1632 p_token1 => 'FROM_CURRENCY',
1633 p_token1_value => l_khr_currency_code,
1634 p_token2 => 'TO_CURRENCY',
1635 p_token2_value => l_func_currency_code,
1636 p_token3 => 'CONVERSION_TYPE',
1637 p_token3_value => l_currency_conv_type
1638 );
1639 RAISE Okl_Api.G_EXCEPTION_ERROR;
1640 END IF;
1641 l_currency_conv_date := l_entry_date; -- Bug 6970675
1642 END IF;
1643 END IF;
1644
1645 -- Find precision for the currency code
1646 OPEN precision_csr(l_khr_currency_code);
1647 FETCH precision_csr INTO l_precision;
1648 CLOSE precision_csr;
1649
1650 IF l_precision IS NULL THEN
1651 -- store SQL error message on message stack for caller
1652 Okl_Api.set_message(p_app_name => g_app_name,
1653 p_msg_name => 'OKL_GLP_PRECISION_ERROR',
1654 p_token1 => 'CURRENCY_CODE',
1655 p_token1_value => l_khr_currency_code);
1656 RAISE OKL_API.G_EXCEPTION_ERROR;
1657 END IF;
1658
1659
1660 -- check if the contract id has a specific loss trx
1661 OPEN sp_loss_trx_csr(l_cntrct_id);
1662 FETCH sp_loss_trx_csr INTO l_sp_loss_fnd;
1663 CLOSE sp_loss_trx_csr;
1664
1665 -- proceed if there were no transactions for specific loss provision
1666 IF l_sp_loss_fnd='N' THEN
1667 IF l_product_subclass = 'LEASE' THEN
1668 -- calculate the nbv loss if contract is an operating lease
1669 IF l_deal_type = l_oper_lease THEN
1670 l_net_book_value := calculate_cntrct_nbv (p_cntrct_id => l_cntrct_id);
1671 IF l_net_book_value IS NULL THEN
1672 Okl_Api.set_message(p_app_name => g_app_name,
1673 p_msg_name => 'OKL_NET_BOOK_VALUE_ERROR',
1674 p_token1 => g_contract_number_token,
1675 p_token1_value => l_cntrct_number);
1676 RAISE OKL_API.G_EXCEPTION_ERROR;
1677 END IF;
1678
1679 -- convert NBV to contract currency. Bug 2712001
1680 IF l_net_book_value <> 0 THEN
1681 IF l_func_currency_code <> l_khr_currency_code THEN
1682 OKL_ACCOUNTING_UTIL.convert_to_contract_currency
1683 (p_khr_id => l_cntrct_id,
1684 p_from_currency => l_func_currency_code,
1685 p_transaction_date => l_entry_date,
1686 p_amount => l_net_book_value,
1687 x_contract_currency => x_contract_currency,
1688 x_currency_conversion_type => x_currency_conversion_type,
1689 x_currency_conversion_rate => x_currency_conversion_rate,
1690 x_currency_conversion_date => x_currency_conversion_date,
1691 x_converted_amount => l_converted_net_book_value);
1692
1693 IF l_converted_net_book_value IS NULL THEN
1694 Okl_Api.set_message(p_app_name => g_app_name,
1695 p_msg_name => 'OKL_AGN_CONV_ERROR',
1696 p_token1 => g_contract_number_token,
1697 p_token1_value => l_cntrct_number);
1698 RAISE Okl_Api.G_EXCEPTION_ERROR;
1699 END IF;
1700 l_nbv_loss_amt := l_converted_net_book_value * l_least_bucket_rate/100;
1701 ELSE
1702 l_nbv_loss_amt := l_net_book_value * l_least_bucket_rate/100;
1703 END IF;
1704 END IF;
1705
1706 -- calculate the niv loss if contract is a direct finance lease or sales lease
1707 ELSIF l_deal_type IN (l_df_lease,l_sales_lease) THEN
1708 l_net_invest_value := calculate_cntrct_niv (p_cntrct_id => l_cntrct_id
1709 ,p_loss_date => l_entry_date);
1710 IF l_net_invest_value IS NULL THEN
1711 Okl_Api.set_message(p_app_name => g_app_name,
1712 p_msg_name => 'OKL_NET_INVEST_VALUE_ERROR',
1713 p_token1 => g_contract_number_token,
1714 p_token1_value => l_cntrct_number);
1715 RAISE OKL_API.G_EXCEPTION_ERROR;
1716 END IF;
1717
1718 IF l_net_invest_value <> 0 THEN
1719 l_niv_loss_amt := l_net_invest_value * l_least_bucket_rate/100;
1720 END IF;
1721
1722 -- calculate the pb loss loss if contract is loan
1723 ELSIF l_deal_type = l_loan_lease THEN
1724 -- get the rev rec method .. racheruv. Bug 6342556
1725 OPEN get_rev_rec_method_csr(l_cntrct_id);
1726 FETCH get_rev_rec_method_csr INTO l_rev_rec_method_code;
1727 CLOSE get_rev_rec_method_csr;
1728
1729 -- if rev rec method is 'Streams' then use the period start and end dates
1730 -- else use the last interest calculation date to obtain contract principal balance.
1731 IF l_rev_rec_method_code = 'STREAMS' THEN
1732
1733 l_principal_balance := get_contract_principal_balance
1734 (p_cntrct_id => l_cntrct_id
1735 ,p_period_start_date => l_period_start_date
1736 ,p_period_end_date => l_period_end_date);
1737
1738 ELSIF l_rev_rec_method_code IN ('ESTIMATED_AND_BILLED', 'ACTUAL') THEN
1739 l_last_int_calc_date := l_int_calc_date_tbl(x);
1740 Okl_Execute_Formula_Pub.g_additional_parameters(1).name := 'p_last_int_calc_date';
1741 Okl_Execute_Formula_Pub.g_additional_parameters(1).value := TO_CHAR(l_last_int_calc_date, 'MM/DD/YYYY');
1742 l_principal_balance := OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE
1743 (p_khr_id => l_cntrct_id,
1744 p_kle_id => NULL);
1745 END IF;
1746
1747 IF l_principal_balance IS NULL THEN
1748 Okl_Api.set_message(p_app_name => g_app_name,
1749 p_msg_name => 'OKL_PRIN_BAL_VALUE_ERROR',
1750 p_token1 => g_contract_number_token,
1751 p_token1_value => l_cntrct_number);
1752 RAISE OKL_API.G_EXCEPTION_ERROR;
1753 END IF;
1754
1755 IF l_principal_balance <> 0 THEN
1756 l_pb_loss_amt := l_principal_balance * l_least_bucket_rate/100;
1757 END IF;
1758
1759 END IF;
1760 END IF; -- IF l_product_subclass = 'LEASE'
1761
1762 -- check if the contract id has a general loss trx
1763 OPEN gen_loss_trx_csr(l_cntrct_id);
1764 FETCH gen_loss_trx_csr INTO l_gen_loss_fnd;
1765 CLOSE gen_loss_trx_csr;
1766
1767 -- if there was a transaction for general loss
1768 IF l_gen_loss_fnd = 'Y' THEN
1769 l_lprv_rec.cntrct_num := l_cntrct_number;
1770 l_lprv_rec.reversal_type := 'PGL';
1771 l_lprv_rec.reversal_date := l_entry_date;
1772
1773 -- reverse the general loss trx
1774 OKL_REV_LOSS_PROV_PUB.REVERSE_LOSS_PROVISIONS (
1775 p_api_version => l_api_version
1776 ,p_init_msg_list => l_init_msg_list
1777 ,x_return_status => l_return_status
1778 ,x_msg_count => x_msg_count
1779 ,x_msg_data => x_msg_data
1780 ,p_lprv_rec => l_lprv_rec);
1781 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1782 Okl_Api.set_message(p_app_name => g_app_name,
1783 p_msg_name => 'OKL_GLP_REV_ERROR',
1784 p_token1 => g_contract_number_token,
1785 p_token1_value => l_cntrct_number);
1786 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1787 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1788 Okl_Api.set_message(p_app_name => g_app_name,
1789 p_msg_name => 'OKL_GLP_REV_ERROR',
1790 p_token1 => g_contract_number_token,
1791 p_token1_value => l_cntrct_number);
1792 RAISE OKL_API.G_EXCEPTION_ERROR;
1793 END IF;
1794 END IF; -- IF l_gen_loss_fnd = 'Y'
1795
1796 -- initialize
1797 FOR x IN l_bktv_tbl.FIRST..l_bktv_tbl.LAST
1798 LOOP
1799 l_bktv_tbl(x).loss_amount := 0;
1800 END LOOP;
1801
1802 IF l_product_subclass = 'LEASE' THEN
1803 -- loop for unpaid invoices for a contract
1804 -- Bug 2969989. Pass provision date to cursor.
1805 FOR cntrct_invcs_rec IN cntrct_invcs_csr(l_cntrct_id, l_entry_date)
1806 LOOP
1807 -- get the invoice details from the cursor
1808 l_due_amt := cntrct_invcs_rec.amount_due_remaining;
1809 l_due_date := cntrct_invcs_rec.due_date;
1810 -- calculate unpaid days
1811 -- Bug 2969989. Unpaid days is provision date less due date.
1812 l_no_of_days := l_entry_date - l_due_date;
1813 --calculate total loss amount for each bucket
1814 FOR l_count IN l_bktv_tbl.FIRST..l_bktv_tbl.LAST
1815 LOOP
1816 IF l_no_of_days BETWEEN l_bktv_tbl(l_count).days_start AND l_bktv_tbl(l_count).days_to THEN
1817 --calculate loss amount
1818 l_inv_loss_amt := l_due_amt * l_bktv_tbl(l_count).loss_rate/100;
1819 -- total up loss amount
1820 l_bktv_tbl(l_count).loss_amount := l_bktv_tbl(l_count).loss_amount + l_inv_loss_amt;
1821 l_trx_header_total := l_trx_header_total + l_inv_loss_amt;
1822 END IF;
1823 END LOOP;
1824 END LOOP; -- end of loop for unpaid invoices for a contract
1825
1826 -- add the nbv loss if the contract is operating lease
1827 IF l_deal_type = l_oper_lease THEN
1828 l_trx_header_total := l_trx_header_total + l_nbv_loss_amt;
1829 -- add the niv loss if the contract is a direct finance lease
1830 ELSIF l_deal_type = l_df_lease OR l_deal_type = l_sales_lease THEN
1831 l_trx_header_total := l_trx_header_total + l_niv_loss_amt;
1832 -- add the pb loss if the contract is a loan
1833 ELSIF l_deal_type = l_loan_lease THEN
1834 l_trx_header_total := l_trx_header_total + l_pb_loss_amt;
1835 END IF;
1836
1837 ELSIF l_product_subclass = 'INVESTOR' THEN
1838 -- commenting below code for bug 3377730
1839 -- uncomment and use in 11ix
1840 --OPEN pool_contents_csr (l_cntrct_id);
1841 --FETCH pool_contents_csr BULK COLLECT INTO pool_contents_tbl;
1842 --CLOSE pool_contents_csr;
1843
1844 l_counter := 1;
1845 FOR i IN pool_contents_csr(l_cntrct_id)
1846 LOOP
1847 pool_contents_tbl(l_counter).lease_contract_id := i.chr_id;
1848 pool_contents_tbl(l_counter).sty_id := i.sty_id;
1849 pool_contents_tbl(l_counter).sty_subclass := i.sty_subclass;
1850 pool_contents_tbl(l_counter).streams_to_date := i.streams_to_date;
1851 l_counter := l_counter + 1;
1852 END LOOP;
1853
1854 IF pool_contents_tbl.COUNT > 0 THEN
1855 FOR i IN pool_contents_tbl.FIRST..pool_contents_tbl.LAST
1856 LOOP
1857 IF pool_contents_tbl(i).sty_subclass IN ('RENT','LOAN_PAYMENT') THEN
1858 -- for each lease contractid fetch open items
1859 FOR j IN open_items_csr(pool_contents_tbl(i).lease_contract_id, pool_contents_tbl(i).sty_id)
1860 LOOP
1861 -- get the invoice details from the cursor
1862 l_due_amt := j.amount_due_remaining;
1863 l_due_date := j.due_date;
1864 -- calculate unpaid days
1865 l_no_of_days := l_entry_date - l_due_date;
1866
1867 --calculate total loss amount for each bucket
1868 FOR l_count IN l_bktv_tbl.FIRST..l_bktv_tbl.LAST
1869 LOOP
1870
1871 IF l_no_of_days BETWEEN l_bktv_tbl(l_count).days_start AND l_bktv_tbl(l_count).days_to THEN
1872 --calculate loss amount
1873 l_inv_loss_amt := l_due_amt * l_bktv_tbl(l_count).loss_rate/100;
1874 -- total up loss amount
1875 l_bktv_tbl(l_count).loss_amount := l_bktv_tbl(l_count).loss_amount + l_inv_loss_amt;
1876 l_trx_header_total := l_trx_header_total + l_inv_loss_amt;
1877 END IF;
1878 END LOOP; -- FOR l_count IN l_bktv_tbl.FIRST..l_bktv_tbl.LAST
1879
1880 END LOOP; -- FOR j IN open_items_csr(pool_contents_tbl(i).lease_contract_id, pool_contents_tbl(i).stream_type_id)
1881
1882 ELSIF pool_contents_tbl(i).sty_subclass = 'RESIDUAL' THEN
1883 IF (pool_contents_tbl(i).streams_to_date IS NULL OR pool_contents_tbl(i).streams_to_date >= l_entry_date) THEN
1884
1885 FOR x IN residual_value_csr (pool_contents_tbl(i).lease_contract_id, pool_contents_tbl(i).sty_id)
1886 LOOP
1887 l_residual_amt := x.amount;
1888 l_total_residual_amt := l_total_residual_amt + l_residual_amt;
1889 END LOOP;
1890
1891 l_residual_loss_amt := l_total_residual_amt * l_least_bucket_rate/100;
1892 l_trx_header_total := l_trx_header_total + l_residual_loss_amt;
1893
1894 END IF;
1895 END IF; -- IF pool_contents_tbl(i).sty_subclass = 'RENT' THEN
1896
1897 END LOOP; -- FOR i IN pool_contents_tbl.FIRST..pool_contents_tbl.LAST
1898
1899 END IF; -- IF pool_contents_tbl.COUNT > 0 THEN
1900
1901 END IF; -- ELSIF l_product_subclass = 'INVESTOR' THEN
1902
1903 -- populate the transaction structure
1904 -- 28-MAY-2002 added if condition to avoid pl/sql numeric or value error while
1905 -- creating transaction
1906 IF (l_trx_header_total IS NOT NULL AND l_trx_header_total > 0) THEN
1907 l_tcnv_rec.tcn_type := l_tcn_type;
1908 l_tcnv_rec.khr_id := l_cntrct_id;
1909 l_tcnv_rec.tsu_code := l_tsu_code;
1910 l_tcnv_rec.pdt_id := l_product_id;
1911 l_tcnv_rec.try_id := l_try_id;
1912 l_tcnv_rec.tax_deductible_local := p_tax_deductible_local;
1913 l_tcnv_rec.tax_deductible_corporate := p_tax_deductible_corporate;
1914 l_tcnv_rec.amount := ROUND(l_trx_header_total,l_precision);
1915 l_tcnv_rec.currency_code := l_khr_currency_code;
1916 l_tcnv_rec.currency_conversion_type := l_currency_conv_type;
1917 l_tcnv_rec.currency_conversion_rate := l_currency_conv_rate;
1918 l_tcnv_rec.currency_conversion_date := l_currency_conv_date;
1919 l_tcnv_rec.set_of_books_id := l_set_of_books_id;
1920 l_tcnv_rec.description := p_description;
1921 l_tcnv_rec.date_transaction_occurred := l_entry_date;
1922 -- Bug 5935176 dpsingh for AE signature Uptake start
1923 OPEN get_dff_fields(l_tcnv_rec.khr_id);
1924 FETCH get_dff_fields into l_tcnv_rec.ATTRIBUTE_CATEGORY,
1925 l_tcnv_rec.ATTRIBUTE1,
1926 l_tcnv_rec.ATTRIBUTE2,
1927 l_tcnv_rec.ATTRIBUTE3,
1928 l_tcnv_rec.ATTRIBUTE4,
1929 l_tcnv_rec.ATTRIBUTE5,
1930 l_tcnv_rec.ATTRIBUTE6,
1931 l_tcnv_rec.ATTRIBUTE7,
1932 l_tcnv_rec.ATTRIBUTE8,
1933 l_tcnv_rec.ATTRIBUTE9,
1934 l_tcnv_rec.ATTRIBUTE10,
1935 l_tcnv_rec.ATTRIBUTE11,
1936 l_tcnv_rec.ATTRIBUTE12,
1937 l_tcnv_rec.ATTRIBUTE13,
1938 l_tcnv_rec.ATTRIBUTE14,
1939 l_tcnv_rec.ATTRIBUTE15;
1940 CLOSE get_dff_fields;
1941 -- Bug 5935176 dpsingh for AE signature Uptake end
1942 --Added by dpsingh for LE Uptake
1943 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_cntrct_id) ;
1944 IF l_legal_entity_id IS NOT NULL THEN
1945 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
1946 ELSE
1947 Okl_Api.set_message(p_app_name => g_app_name,
1948 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
1949 p_token1 => 'CONTRACT_NUMBER',
1950 p_token1_value => l_cntrct_number);
1951 RAISE OKL_API.G_EXCEPTION_ERROR;
1952 END IF;
1953
1954 FOR x IN l_bktv_tbl.FIRST..l_bktv_tbl.LAST
1955 LOOP
1956 IF l_bktv_tbl(x).loss_amount > 0 THEN
1957 -- populate the transaction line structure
1958 l_tclv_tbl(l_line_count).line_number := l_line_count;
1959 l_tclv_tbl(l_line_count).khr_id := l_cntrct_id;
1960 l_tclv_tbl(l_line_count).description := p_description;
1961 l_tclv_tbl(l_line_count).amount := ROUND(l_bktv_tbl(x).loss_amount,l_precision);
1962 l_tclv_tbl(l_line_count).currency_code := l_khr_currency_code;
1963 l_tclv_tbl(l_line_count).tcl_type := l_tcl_type;
1964 l_tclv_tbl(l_line_count).bkt_id := l_bktv_tbl(x).bkt_id;
1965 l_tclv_tbl(l_line_count).sty_id := l_sty_id;
1966 l_line_count := l_line_count+1;
1967 END IF;
1968 END LOOP;
1969
1970 IF l_product_subclass = 'LEASE' THEN
1971 IF l_deal_type = l_oper_lease THEN
1972 IF l_nbv_loss_amt IS NOT NULL AND l_nbv_loss_amt > 0 THEN
1973 l_tclv_tbl(l_line_count).amount := ROUND(l_nbv_loss_amt,l_precision);
1974 l_tclv_tbl(l_line_count).line_number := l_line_count;
1975 l_tclv_tbl(l_line_count).khr_id := l_cntrct_id;
1976 l_tclv_tbl(l_line_count).description := p_description;
1977 l_tclv_tbl(l_line_count).currency_code := l_khr_currency_code;
1978 l_tclv_tbl(l_line_count).bkt_id := l_bktv_tbl(1).bkt_id;
1979 l_tclv_tbl(l_line_count).sty_id := l_sty_id;
1980 l_tclv_tbl(l_line_count).tcl_type := l_tcl_type;
1981 END IF;
1982 ELSIF (l_deal_type = l_df_lease OR l_deal_type = l_sales_lease) THEN
1983 IF l_niv_loss_amt IS NOT NULL AND l_niv_loss_amt > 0 THEN
1984 l_tclv_tbl(l_line_count).amount := ROUND(l_niv_loss_amt,l_precision);
1985 l_tclv_tbl(l_line_count).line_number := l_line_count;
1986 l_tclv_tbl(l_line_count).khr_id := l_cntrct_id;
1987 l_tclv_tbl(l_line_count).description := p_description;
1988 l_tclv_tbl(l_line_count).currency_code := l_khr_currency_code;
1989 l_tclv_tbl(l_line_count).bkt_id := l_bktv_tbl(1).bkt_id;
1990 l_tclv_tbl(l_line_count).sty_id := l_sty_id;
1991 l_tclv_tbl(l_line_count).tcl_type := l_tcl_type;
1992 END IF;
1993 ELSIF l_deal_type = l_loan_lease THEN
1994 IF l_pb_loss_amt IS NOT NULL AND l_pb_loss_amt > 0 THEN
1995 l_tclv_tbl(l_line_count).amount := ROUND(l_pb_loss_amt,l_precision);
1996 l_tclv_tbl(l_line_count).line_number := l_line_count;
1997 l_tclv_tbl(l_line_count).khr_id := l_cntrct_id;
1998 l_tclv_tbl(l_line_count).description := p_description;
1999 l_tclv_tbl(l_line_count).currency_code := l_khr_currency_code;
2000 l_tclv_tbl(l_line_count).bkt_id := l_bktv_tbl(1).bkt_id;
2001 l_tclv_tbl(l_line_count).sty_id := l_sty_id;
2002 l_tclv_tbl(l_line_count).tcl_type := l_tcl_type;
2003 END IF;
2004 END IF;
2005
2006 --Bug 4622198.
2007 OKL_SECURITIZATION_PVT.check_khr_ia_associated(
2008 p_api_version => l_api_version
2009 ,p_init_msg_list => l_init_msg_list
2010 ,x_return_status => l_return_status
2011 ,x_msg_count => x_msg_count
2012 ,x_msg_data => x_msg_data
2013 ,p_khr_id => l_cntrct_id
2014 ,p_scs_code => l_product_subclass
2015 ,p_trx_date => l_entry_date
2016 ,x_fact_synd_code => l_fact_sync_code
2017 ,x_inv_acct_code => l_inv_acct_code
2018 );
2019
2020 -- store the highest degree of error
2021 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2022 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2023 -- need to leave
2024 Okl_Api.set_message(p_app_name => g_app_name,
2025 p_msg_name => 'OKL_ACC_SEC_PVT_ERROR');
2026 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2027 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2028 Okl_Api.set_message(p_app_name => g_app_name,
2029 p_msg_name => 'OKL_ACC_SEC_PVT_ERROR');
2030 RAISE OKL_API.G_EXCEPTION_ERROR;
2031 END IF;
2032 END IF;
2033
2034 ELSIF l_product_subclass = 'INVESTOR' THEN
2035 IF l_residual_loss_amt IS NOT NULL AND l_residual_loss_amt > 0 THEN
2036 l_tclv_tbl(l_line_count).amount := ROUND(l_residual_loss_amt,l_precision);
2037 l_tclv_tbl(l_line_count).line_number := l_line_count;
2038 l_tclv_tbl(l_line_count).khr_id := l_cntrct_id;
2039 l_tclv_tbl(l_line_count).description := p_description;
2040 l_tclv_tbl(l_line_count).currency_code := l_khr_currency_code;
2041 l_tclv_tbl(l_line_count).bkt_id := l_bktv_tbl(1).bkt_id;
2042 l_tclv_tbl(l_line_count).sty_id := l_sty_id;
2043 l_tclv_tbl(l_line_count).tcl_type := l_tcl_type;
2044 END IF;
2045 --Bug 4622198.
2046 OKL_SECURITIZATION_PVT.check_khr_ia_associated(
2047 p_api_version => l_api_version
2048 ,p_init_msg_list => l_init_msg_list
2049 ,x_return_status => l_return_status
2050 ,x_msg_count => x_msg_count
2051 ,x_msg_data => x_msg_data
2052 ,p_khr_id => l_cntrct_id
2053 ,p_scs_code => l_product_subclass
2054 ,p_trx_date => l_entry_date
2055 ,x_fact_synd_code => l_fact_sync_code
2056 ,x_inv_acct_code => l_inv_acct_code
2057 );
2058
2059 -- store the highest degree of error
2060 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2061 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2062 -- need to leave
2063 Okl_Api.set_message(p_app_name => g_app_name,
2064 p_msg_name => 'OKL_ACC_SEC_PVT_ERROR');
2065 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2067 Okl_Api.set_message(p_app_name => g_app_name,
2068 p_msg_name => 'OKL_ACC_SEC_PVT_ERROR');
2069 RAISE OKL_API.G_EXCEPTION_ERROR;
2070 END IF;
2071 END IF;
2072
2073 END IF; -- IF l_product_subclass = 'LEASE'
2074
2075
2076 -- create transaction and transaction lines
2077 CREATE_GEN_LOSS_TRX(
2078 p_api_version => l_api_version
2079 ,p_init_msg_list => l_init_msg_list
2080 ,x_return_status => l_return_status
2081 ,x_msg_count => x_msg_count
2082 ,x_msg_data => x_msg_data
2083 ,p_contract_id => l_cntrct_id
2084 ,p_contract_number=> l_cntrct_number
2085 ,p_fact_synd_code => l_fact_sync_code
2086 ,p_inv_acct_code => l_inv_acct_code
2087 ,p_tcnv_rec => l_tcnv_rec
2088 ,p_tclv_tbl => l_tclv_tbl
2089 ,x_tcnv_rec => x_tcnv_rec
2090 ,x_tclv_tbl => x_tclv_tbl);
2091
2092 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2093 Okl_Api.set_message(p_app_name => g_app_name,
2094 p_msg_name => 'OKL_GLP_TRX_CRE_ERROR',
2095 p_token1 => g_contract_number_token,
2096 p_token1_value => l_cntrct_number);
2097 RAISE OKL_API.G_EXCEPTION_ERROR;
2098 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2099 Okl_Api.set_message(p_app_name => g_app_name,
2100 p_msg_name => 'OKL_GLP_TRX_CRE_ERROR',
2101 p_token1 => g_contract_number_token,
2102 p_token1_value => l_cntrct_number);
2103 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2104 END IF;
2105
2106 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_cntrct_number,28)||
2107 RPAD(x_tcnv_rec.trx_number,22)||
2108 RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',x_tcnv_rec.tax_deductible_local,0,0),23)||
2109 RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',x_tcnv_rec.tax_deductible_corporate,0,0),26)||
2110 RPAD(x_tcnv_rec.currency_code,9)||
2111 -- Bug# 2774187. Commenting precision as accounting util takes care of formatting.
2112 --LPAD(x_tcnv_rec.amount,17));
2113 LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(x_tcnv_rec.amount,x_tcnv_rec.currency_code),17));
2114 END IF;
2115 END IF; -- end if for contract did not have a specific loss trx
2116 EXCEPTION
2117
2118 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2119 l_return_status := Okl_Api.G_RET_STS_ERROR;
2120 -- Select the contract for error reporting
2121 FND_FILE.PUT_LINE(FND_FILE.LOG,l_cntrct_number||', '||'Error Status: '||l_return_status);
2122 l_contract_error_tbl(l_error_cnt) := l_cntrct_number;
2123 l_error_cnt := l_error_cnt + 1; Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_tbl);
2124 IF (l_error_msg_tbl.COUNT > 0) THEN
2125 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
2126 LOOP
2127 IF l_error_msg_tbl(i) IS NOT NULL THEN
2128 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_tbl(i));
2129 END IF;
2130 END LOOP;
2131 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
2132 END IF;
2133
2134 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2135 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2136 -- Select the contract for error reporting
2137 FND_FILE.PUT_LINE(FND_FILE.LOG,l_cntrct_number||', '||'Error Status: '||l_return_status);
2138 l_contract_error_tbl(l_error_cnt) := l_cntrct_number;
2139 l_error_cnt := l_error_cnt + 1;
2140 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_tbl);
2141 IF (l_error_msg_tbl.COUNT > 0) THEN
2142 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
2143 LOOP
2144 IF l_error_msg_tbl(i) IS NOT NULL THEN
2145 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_tbl(i));
2146 END IF;
2147 END LOOP;
2148 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
2149 END IF;
2150
2151 WHEN OTHERS THEN
2152 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2153 -- IF currency_conv_csr%ISOPEN THEN
2154 -- CLOSE currency_conv_csr;
2155 -- END IF;
2156
2157 IF precision_csr%ISOPEN THEN
2158 CLOSE precision_csr;
2159 END IF;
2160
2161 IF sp_loss_trx_csr%ISOPEN THEN
2162 CLOSE sp_loss_trx_csr;
2163 END IF;
2164
2165 IF gen_loss_trx_csr%ISOPEN THEN
2166 CLOSE gen_loss_trx_csr;
2167 END IF;
2168
2169 -- Select the contract for error reporting
2170 FND_FILE.PUT_LINE(FND_FILE.LOG,l_cntrct_number||', '||'Error Status: '||l_return_status);
2171 l_contract_error_tbl(l_error_cnt) := l_cntrct_number;
2172 l_error_cnt := l_error_cnt + 1;
2173 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_tbl);
2174 IF (l_error_msg_tbl.COUNT > 0) THEN
2175 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
2176 LOOP
2177 IF l_error_msg_tbl(i) IS NOT NULL THEN
2178 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_tbl(i));
2179 END IF;
2180 END LOOP;
2181 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
2182 END IF;
2183 END;
2184
2185 END LOOP; -- for contracts within a product
2186 END IF; -- IF l_pdt_contracts_tbl.COUNT > 0 THEN
2187
2188 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
2189 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
2190 IF l_product_subclass = 'LEASE' THEN
2191 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_CNTRCT_ERROR_TITLE'));
2192 ELSE
2193 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_GLP_RPT_AGR_ERROR_TITLE'));
2194 END IF;
2195 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_CNT_ERR_UNDERLINE'));
2196 IF l_contract_error_tbl.COUNT > 0 THEN
2197 FOR x IN l_contract_error_tbl.FIRST..l_contract_error_tbl.LAST
2198 LOOP
2199 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_contract_error_tbl(x));
2200 END LOOP;
2201 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');
2202 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_ADD_INFO'));
2203 END IF;
2204 retcode := 0;
2205
2206 EXCEPTION
2207
2208 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2209 l_return_status := Okl_Api.G_RET_STS_ERROR;
2210
2211 -- print the overall status in the log file
2212 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Overall Program Status = '||l_return_status);
2213 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
2214 IF (l_outer_error_msg_tbl.COUNT > 0) THEN
2215 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
2216 LOOP
2217 IF l_outer_error_msg_tbl(i) IS NOT NULL THEN
2218 FND_FILE.PUT_LINE(FND_FILE.LOG,l_outer_error_msg_tbl(i));
2219 END IF;
2220 END LOOP;
2221 END IF;
2222 retcode := 2;
2223
2224 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2225 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2226
2227 -- print the overall status in the log file
2228 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Overall Program Status = '||l_return_status);
2229 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
2230 IF (l_outer_error_msg_tbl.COUNT > 0) THEN
2231 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
2232 LOOP
2233 IF l_outer_error_msg_tbl(i) IS NOT NULL THEN
2234 FND_FILE.PUT_LINE(FND_FILE.LOG,l_outer_error_msg_tbl(i));
2235 END IF;
2236 END LOOP;
2237 END IF;
2238 retcode := 2;
2239
2240 WHEN OTHERS THEN
2241 IF org_name_csr%ISOPEN THEN
2242 CLOSE org_name_csr;
2243 END IF;
2244
2245 IF pdt_csr%ISOPEN THEN
2246 CLOSE pdt_csr;
2247 END IF;
2248
2249 IF trx_types_csr%ISOPEN THEN
2250 CLOSE trx_types_csr;
2251 END IF;
2252
2253 -- IF sty_type_csr%ISOPEN THEN
2254 -- CLOSE sty_type_csr;
2255 -- END IF;
2256
2257 IF least_rate_csr%ISOPEN THEN
2258 CLOSE least_rate_csr;
2259 END IF;
2260
2261 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2262 errbuf := SQLERRM;
2263 retcode := 2;
2264 -- print the overall status in the log file
2265 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Overall Program Status = '||l_return_status);
2266 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
2267 IF (l_outer_error_msg_tbl.COUNT > 0) THEN
2268 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
2269 LOOP
2270 IF l_outer_error_msg_tbl(i) IS NOT NULL THEN
2271 FND_FILE.PUT_LINE(FND_FILE.LOG,l_outer_error_msg_tbl(i));
2272 END IF;
2273 END LOOP;
2274 END IF;
2275
2276 END GENERAL_LOSS_PROVISION;
2277
2278 -- this procedure is used create a transaction for specific loss provision
2279 PROCEDURE SPECIFIC_LOSS_PROVISION(
2280 p_api_version IN NUMBER
2281 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2282 ,x_msg_count OUT NOCOPY NUMBER
2283 ,x_msg_data OUT NOCOPY VARCHAR2
2284 ,x_return_status OUT NOCOPY VARCHAR2
2285 ,p_slpv_rec IN slpv_rec_type)
2286
2287 IS
2288 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2289 l_try_id OKL_TRX_TYPES_V.ID%TYPE;
2290 l_dummy_var VARCHAR2(1) := '?';
2291 l_dummy2_var VARCHAR2(1) := '?';
2292 l_line_count NUMBER := 0;
2293
2294 -- constants
2295 l_api_name CONSTANT VARCHAR2(40) := 'SPECIFIC_LOSS_PROVISION';
2296 l_api_version CONSTANT NUMBER := 1.0;
2297 l_tcn_type CONSTANT OKL_TRX_CONTRACTS.TCN_TYPE%TYPE := 'PSP';
2298 l_try_name CONSTANT OKL_TRX_TYPES_TL.NAME%TYPE := 'Specific Loss Provision';
2299 l_tcl_type CONSTANT OKL_TXL_CNTRCT_LNS.TCL_TYPE%TYPE := 'PSP';
2300 l_source_table CONSTANT OKL_TRNS_ACC_DSTRS.SOURCE_TABLE%TYPE := 'OKL_TXL_CNTRCT_LNS';
2301 --Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
2302 l_tsu_code CONSTANT OKL_TRX_CONTRACTS.TSU_CODE%TYPE := 'PROCESSED';
2303
2304 --variables
2305 l_func_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
2306 l_khr_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE;
2307 l_currency_conv_type OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_TYPE%TYPE;
2308 l_currency_conv_rate OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_RATE%TYPE;
2309 l_currency_conv_date OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_DATE%TYPE;
2310 l_set_of_books_id OKL_SYS_ACCT_OPTS.SET_OF_BOOKS_ID%TYPE;
2311 l_product_id OKL_K_HEADERS_FULL_V.PDT_ID%TYPE;
2312 l_sty_id OKL_STRM_TYPE_V.ID%TYPE;
2313 l_sysdate DATE := SYSDATE;
2314 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
2315 l_period_start_date DATE;
2316 l_period_end_date DATE;
2317 l_period_name VARCHAR2(2000);
2318 l_period_status VARCHAR2(1);
2319 l_fact_sync_code VARCHAR2(2000);
2320 l_inv_acct_code VARCHAR2(2000);
2321 l_scs_code VARCHAR2(2000);
2322
2323 -- record and table structure variables
2324 l_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
2325 l_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
2326 x_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
2327 x_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
2328 l_lprv_rec OKL_REV_LOSS_PROV_PUB.lprv_rec_type;
2329 l_tmpl_identify_rec Okl_Account_Dist_Pub.tmpl_identify_rec_type;
2330 l_dist_info_rec Okl_Account_Dist_Pub.dist_info_rec_type;
2331 l_ctxt_val_tbl Okl_Account_Dist_Pub.ctxt_val_tbl_type;
2332 l_template_tbl Okl_Account_Dist_Pub.avlv_tbl_type;
2333 l_amount_tbl Okl_Account_Dist_Pub.amount_tbl_type;
2334 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
2335
2336 --Added by dpsingh for LE Uptake
2337 l_legal_entity_id NUMBER;
2338
2339 --Added by kthiruva for SLA Uptake on 14-Feb-2007
2340 --Bug 5707866 - Start of Changes
2341 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
2342 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_type;
2343 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
2344 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
2345 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
2346 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
2347 l_tcn_id NUMBER;
2348 --Bug 5707866 - End of Changes
2349
2350 -- cursor to get transaction type id
2351 CURSOR trx_types_csr IS
2352 SELECT id
2353 FROM OKL_TRX_TYPES_TL
2354 WHERE NAME = l_try_name
2355 AND LANGUAGE = 'US';
2356
2357 -- cursor to get scs_code
2358 CURSOR scs_code_csr IS
2359 SELECT scs_code
2360 FROM OKL_K_HEADERS_FULL_V
2361 WHERE id = p_slpv_rec.khr_id;
2362
2363
2364 -- Cursor to verify if the stream type selected is the same as used earlier
2365 -- CURSOR sty_id_csr(p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
2366 -- SELECT DISTINCT sty.id id
2367 -- FROM OKL_STRM_TYPE_B sty, OKL_TRX_CONTRACTS_V trx, OKL_TXL_CNTRCT_LNS_V txl
2368 -- WHERE trx.khr_id = p_ctr_id
2369 -- AND trx.tcn_type = 'PSP'
2370 -- AND trx.tsu_code = l_tsu_code
2371 -- AND trx.id = txl.tcn_id
2372 -- AND txl.sty_id = sty.id;
2373
2374 -- cursor to check whether the contract has a general loss
2375 CURSOR gen_loss_trx_csr (p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
2376 SELECT 'Y'
2377 FROM OKL_TRX_CONTRACTS
2378 WHERE khr_id = p_ctr_id
2379 AND tcn_type = 'PGL'
2380 AND tsu_code = l_tsu_code
2381 AND representation_type='PRIMARY';
2382
2383 -- cursor to check whether the contract has a specific loss
2384 CURSOR spec_loss_trx_csr (p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
2385 SELECT 'Y'
2386 FROM OKL_TRX_CONTRACTS
2387 WHERE khr_id = p_ctr_id
2388 AND tcn_type = 'PSP'
2389 AND tsu_code = l_tsu_code
2390 AND representation_type='PRIMARY';
2391
2392 -- cursor to get the contract number
2393 CURSOR contract_num_csr (p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
2394 SELECT contract_number
2395 FROM OKL_K_HEADERS_FULL_V
2396 WHERE id = p_ctr_id;
2397
2398 -- cursor to get the product id for the given contract id
2399 CURSOR pdt_id_csr(p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
2400 SELECT pdt_id
2401 FROM OKL_K_HEADERS_FULL_V
2402 WHERE id = p_ctr_id;
2403
2404 -- Cursor to select currency information for the contract
2405 CURSOR currency_info_csr(p_khr_id NUMBER) IS
2406 SELECT chr.currency_code currency_code,
2407 khr.currency_conversion_type currency_conversion_type,
2408 khr.currency_conversion_date currency_conversion_date,
2409 khr.currency_conversion_rate currency_conversion_rate
2410 FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr
2411 WHERE chr.id = p_khr_id
2412 AND chr.id = khr.id;
2413
2414
2415 -- Cursor to select currency conversion information
2416 -- CURSOR currency_conv_csr(p_conversion_type VARCHAR2, p_from_currency VARCHAR2, p_to_currency VARCHAR2, p_conversion_date DATE) IS
2417 -- SELECT conversion_rate
2418 -- FROM GL_DAILY_RATES
2419 -- WHERE conversion_type = p_conversion_type
2420 -- AND conversion_date = p_conversion_date
2421 -- AND from_currency = p_from_currency
2422 -- AND to_currency = p_to_currency
2423 -- AND status_code = 'C';
2424
2425 BEGIN
2426
2427 l_return_status := OKL_API.START_ACTIVITY(l_api_name
2428 ,G_PKG_NAME
2429 ,p_init_msg_list
2430 ,l_api_version
2431 ,p_api_version
2432 ,'_PVT'
2433 ,l_return_status);
2434
2435 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2436 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2437 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2438 RAISE OKL_API.G_EXCEPTION_ERROR;
2439 END IF;
2440
2441 -- validate input parameters
2442 IF (p_slpv_rec.sty_id IS NULL OR p_slpv_rec.sty_id = OKL_API.G_MISS_NUM) THEN
2443 Okl_Api.set_message(p_app_name => g_app_name,
2444 p_msg_name => 'OKL_GLP_PVN_ERROR');
2445 RAISE OKL_API.G_EXCEPTION_ERROR;
2446 END IF;
2447
2448 IF (p_slpv_rec.khr_id IS NULL OR p_slpv_rec.khr_id = OKL_API.G_MISS_NUM) THEN
2449 Okl_Api.set_message(p_app_name => g_app_name,
2450 p_msg_name => 'OKL_REV_LPV_CNTRCT_ERROR');
2451 RAISE OKL_API.G_EXCEPTION_ERROR;
2452 END IF;
2453
2454 IF (p_slpv_rec.amount IS NULL OR p_slpv_rec.amount = OKL_API.G_MISS_NUM) THEN
2455 Okl_Api.set_message(p_app_name => g_app_name,
2456 p_msg_name => 'OKL_SLP_AMOUNT_ERROR');
2457 RAISE OKL_API.G_EXCEPTION_ERROR;
2458 END IF;
2459
2460 IF (p_slpv_rec.provision_date IS NULL OR p_slpv_rec.provision_date = OKL_API.G_MISS_DATE) THEN
2461 Okl_Api.set_message(p_app_name => g_app_name,
2462 p_msg_name => 'OKL_SLP_PROV_DATE_ERROR');
2463 RAISE OKL_API.G_EXCEPTION_ERROR;
2464 END IF;
2465
2466 -- get the contract number
2467 OPEN contract_num_csr(p_slpv_rec.khr_id);
2468 FETCH contract_num_csr INTO l_cntrct_number;
2469 CLOSE contract_num_csr;
2470 IF l_cntrct_number IS NULL THEN
2471 Okl_Api.set_message(p_app_name => g_app_name,
2472 p_msg_name => 'OKL_REV_LPV_CNTRCT_NUM_ERROR');
2473 RAISE OKL_API.G_EXCEPTION_ERROR;
2474 END IF;
2475
2476 -- 01-Apr-04. Discussed with Vikas.
2477 -- Removing validation. Product Management requires that SLP be created for
2478 -- contracts using different provision types.
2479 --IF (p_slpv_rec.reverse_flag = 'N' OR p_slpv_rec.reverse_flag IS NULL) THEN
2480 -- -- validate sty_id to make sure provision used is same as this trx is an addition
2481 -- FOR x IN sty_id_csr(p_slpv_rec.khr_id)
2482 -- LOOP
2483 -- IF p_slpv_rec.sty_id <> x.id THEN
2484 -- Okl_Api.set_message(p_app_name => g_app_name,
2485 -- p_msg_name => 'OKL_SLP_PVN_TYPE_ERROR');
2486 -- RAISE OKL_API.G_EXCEPTION_ERROR;
2487 -- END IF;
2488 -- END LOOP;
2489 --END IF;
2490
2491 -- get period info
2492 Okl_Accounting_Util.GET_PERIOD_INFO(p_slpv_rec.provision_date,l_period_name,l_period_start_date,l_period_end_date);
2493 IF l_period_name IS NULL THEN
2494 Okl_Api.set_message(p_app_name => g_app_name,
2495 p_msg_name => 'OKL_AGN_PERIOD_END_DATE');
2496 RAISE Okl_Api.G_EXCEPTION_ERROR;
2497 END IF;
2498
2499 -- check for open period
2500 l_period_status := Okl_Accounting_Util.GET_OKL_PERIOD_STATUS(l_period_name);
2501 IF l_period_status IS NULL THEN
2502 Okl_Api.set_message(p_app_name => g_app_name,
2503 p_msg_name => 'OKL_AGN_PERIOD_STATUS_ERROR',
2504 p_token1 => 'PERIOD_NAME',
2505 p_token1_value => l_period_name);
2506 RAISE Okl_Api.G_EXCEPTION_ERROR;
2507 END IF;
2508
2509 IF l_period_status NOT IN('O','F') THEN
2510 Okl_Api.set_message(p_app_name => g_app_name,
2511 p_msg_name => 'OKL_AGN_OPEN_PERIOD_ERROR');
2512 RAISE Okl_Api.G_EXCEPTION_ERROR;
2513 END IF;
2514
2515 -- get the set of books id
2516 l_set_of_books_id := OKL_ACCOUNTING_UTIL.GET_SET_OF_BOOKS_ID;
2517 IF l_set_of_books_id IS NULL THEN
2518 Okl_Api.set_message(p_app_name => g_app_name,
2519 p_msg_name => 'OKL_AGN_SOB_ID_ERROR');
2520 RAISE OKL_API.G_EXCEPTION_ERROR;
2521 END IF;
2522
2523 -- get scs_code
2524 FOR x IN scs_code_csr
2525 LOOP
2526 l_scs_code := x.scs_code;
2527 END LOOP;
2528 IF l_scs_code IS NULL THEN
2529 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SCS_CODE');
2530 RAISE OKL_API.G_EXCEPTION_ERROR;
2531 END IF;
2532
2533 -- retrieve the functional currency code
2534 l_func_currency_code := Okl_Accounting_Util.GET_FUNC_CURR_CODE;
2535 IF l_func_currency_code IS NULL THEN
2536 Okl_Api.set_message(p_app_name => g_app_name,
2537 p_msg_name => 'OKL_AGN_CURR_CODE_ERROR');
2538 RAISE OKL_API.G_EXCEPTION_ERROR;
2539 END IF;
2540
2541 -- retrieve the currency code for the contract
2542 -- bug 2712001. Chneged from functional currency to contract currency.
2543 OPEN currency_info_csr(p_slpv_rec.khr_id);
2544 FETCH currency_info_csr INTO l_khr_currency_code,l_currency_conv_type,l_currency_conv_date,l_currency_conv_rate;
2545 CLOSE currency_info_csr;
2546 IF l_khr_currency_code IS NULL THEN
2547 Okl_Api.set_message(p_app_name => g_app_name,
2548 p_msg_name => 'OKL_SLP_CURR_CODE_ERROR',
2549 p_token1 => g_contract_number_token,
2550 p_token1_value => l_cntrct_number);
2551 RAISE OKL_API.G_EXCEPTION_ERROR;
2552 END IF;
2553
2554 -- bug 2712001. Enabled Multi Currency changes
2555 -- Check contract currency against functional currency
2556 IF l_func_currency_code <> l_khr_currency_code THEN
2557 --validate data
2558 IF l_currency_conv_type IS NULL THEN
2559 Okl_Api.set_message(p_app_name => g_app_name,
2560 p_msg_name => 'OKL_AGN_CURR_TYPE_ERROR',
2561 p_token1 => g_contract_number_token,
2562 p_token1_value => l_cntrct_number);
2563 RAISE Okl_Api.G_EXCEPTION_ERROR;
2564 END IF;
2565 IF l_currency_conv_date IS NULL THEN
2566 Okl_Api.set_message(p_app_name => g_app_name,
2567 p_msg_name => 'OKL_AGN_CURR_DATE_ERROR',
2568 p_token1 => g_contract_number_token,
2569 p_token1_value => l_cntrct_number);
2570 RAISE Okl_Api.G_EXCEPTION_ERROR;
2571 END IF;
2572 IF l_currency_conv_type = 'User' THEN
2573 IF l_currency_conv_rate IS NULL THEN
2574 Okl_Api.set_message(p_app_name => g_app_name,
2575 p_msg_name => 'OKL_AGN_CURR_USER_RATE_ERROR',
2576 p_token1 => g_contract_number_token,
2577 p_token1_value => l_cntrct_number);
2578 RAISE Okl_Api.G_EXCEPTION_ERROR;
2579 END IF;
2580 ELSE
2581 -- OPEN currency_conv_csr(l_currency_conv_type, l_khr_currency_code, l_func_currency_code, l_currency_conv_date);
2582 -- FETCH currency_conv_csr INTO l_currency_conv_rate;
2583 -- IF currency_conv_csr%NOTFOUND THEN
2584 -- CLOSE currency_conv_csr;
2585 -- Okl_Api.set_message(p_app_name => g_app_name,
2586 -- p_msg_name => 'OKL_AGN_CURR_RATE_ERROR',
2587 -- p_token1 => 'FROM_CURRENCY',
2588 -- p_token1_value => l_khr_currency_code,
2589 -- p_token2 => 'TO_CURRENCY',
2590 -- p_token2_value => l_func_currency_code,
2591 -- p_token3 => 'CONVERSION_TYPE',
2592 -- p_token3_value => l_currency_conv_type
2593 -- );
2594 -- RAISE Okl_Api.G_EXCEPTION_ERROR;
2595 -- END IF;
2596 -- CLOSE currency_conv_csr;
2597 l_currency_conv_rate := OKL_ACCOUNTING_UTIL.get_curr_con_rate
2598 (p_from_curr_code => l_khr_currency_code,
2599 p_to_curr_code => l_func_currency_code,
2600 p_con_date => p_slpv_rec.provision_date, -- Bug 6970654
2601 p_con_type => l_currency_conv_type);
2602
2603 IF l_currency_conv_rate IS NULL THEN
2604 Okl_Api.set_message(p_app_name => g_app_name,
2605 p_msg_name => 'OKL_AGN_CURR_RATE_ERROR',
2606 p_token1 => 'FROM_CURRENCY',
2607 p_token1_value => l_khr_currency_code,
2608 p_token2 => 'TO_CURRENCY',
2609 p_token2_value => l_func_currency_code,
2610 p_token3 => 'CONVERSION_TYPE',
2611 p_token3_value => l_currency_conv_type
2612 );
2613 RAISE Okl_Api.G_EXCEPTION_ERROR;
2614 END IF;
2615 l_currency_conv_date := p_slpv_rec.provision_date;--Bug 6970654
2616 END IF;
2617 END IF;
2618
2619 -- retrieve the transaction type id
2620 OPEN trx_types_csr;
2621 FETCH trx_types_csr INTO l_try_id;
2622 CLOSE trx_types_csr;
2623 IF l_try_id IS NULL THEN
2624 -- store SQL error message on message stack for caller
2625 Okl_Api.set_message(p_app_name => g_app_name,
2626 p_msg_name => 'OKL_AGN_TRX_TYPE_ERROR',
2627 p_token1 => 'TRANSACTION_TYPE',
2628 p_token1_value => l_try_name);
2629 RAISE OKL_API.G_EXCEPTION_ERROR;
2630 END IF;
2631
2632 -- get the product id for the contract
2633 OPEN pdt_id_csr(p_slpv_rec.khr_id);
2634 FETCH pdt_id_csr INTO l_product_id;
2635 CLOSE pdt_id_csr;
2636 IF l_product_id IS NULL THEN
2637 Okl_Api.set_message(p_app_name => g_app_name,
2638 p_msg_name => 'OKL_SLP_PDT_ID_ERROR',
2639 p_token1 => g_contract_number_token,
2640 p_token1_value => l_cntrct_number);
2641 RAISE OKL_API.G_EXCEPTION_ERROR;
2642 END IF;
2643
2644 -- check if the contract id has a general loss trx
2645 OPEN gen_loss_trx_csr(p_slpv_rec.khr_id);
2646 FETCH gen_loss_trx_csr INTO l_dummy_var;
2647 CLOSE gen_loss_trx_csr;
2648
2649 -- if there was a transaction for general loss
2650 IF l_dummy_var = 'Y' THEN
2651 l_lprv_rec.cntrct_num := l_cntrct_number;
2652 l_lprv_rec.reversal_type := 'PGL';
2653 l_lprv_rec.reversal_date := p_slpv_rec.provision_date;
2654
2655 -- reverse the general loss trx
2656 OKL_REV_LOSS_PROV_PUB.REVERSE_LOSS_PROVISIONS (
2657 p_api_version => l_api_version
2658 ,p_init_msg_list => p_init_msg_list
2659 ,x_return_status => l_return_status
2660 ,x_msg_count => x_msg_count
2661 ,x_msg_data => x_msg_data
2662 ,p_lprv_rec => l_lprv_rec);
2663
2664 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2665 Okl_Api.set_message(p_app_name => g_app_name,
2666 p_msg_name => 'OKL_GLP_REV_ERROR',
2667 p_token1 => g_contract_number_token,
2668 p_token1_value => l_cntrct_number);
2669 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2670 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2671 Okl_Api.set_message(p_app_name => g_app_name,
2672 p_msg_name => 'OKL_GLP_REV_ERROR',
2673 p_token1 => g_contract_number_token,
2674 p_token1_value => l_cntrct_number);
2675 RAISE OKL_API.G_EXCEPTION_ERROR;
2676 END IF;
2677 END IF;
2678
2679 -- reverse all previous specific loss transactions as this is a new trx with new provision type
2680 IF p_slpv_rec.reverse_flag = 'Y' THEN
2681 -- check is specific loss exists and make a call for reversal only if exists
2682 OPEN spec_loss_trx_csr(p_slpv_rec.khr_id);
2683 FETCH spec_loss_trx_csr INTO l_dummy2_var;
2684 CLOSE spec_loss_trx_csr;
2685
2686 IF l_dummy2_var = 'Y' THEN
2687 -- initialize variables
2688 l_lprv_rec.cntrct_num := l_cntrct_number;
2689 l_lprv_rec.reversal_type := 'PSP';
2690 l_lprv_rec.reversal_date := p_slpv_rec.provision_date;
2691
2692 -- reverse the spec loss trx
2693 OKL_REV_LOSS_PROV_PUB.REVERSE_LOSS_PROVISIONS (
2694 p_api_version => p_api_version
2695 ,p_init_msg_list => p_init_msg_list
2696 ,x_return_status => l_return_status
2697 ,x_msg_count => x_msg_count
2698 ,x_msg_data => x_msg_data
2699 ,p_lprv_rec => l_lprv_rec);
2700
2701 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2702 Okl_Api.set_message(p_app_name => g_app_name,
2703 p_msg_name => 'OKL_SLP_REV_ERROR',
2704 p_token1 => g_contract_number_token,
2705 p_token1_value => l_cntrct_number);
2706 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2707 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2708 Okl_Api.set_message(p_app_name => g_app_name,
2709 p_msg_name => 'OKL_SLP_REV_ERROR',
2710 p_token1 => g_contract_number_token,
2711 p_token1_value => l_cntrct_number);
2712 RAISE OKL_API.G_EXCEPTION_ERROR;
2713 END IF;
2714 END IF; -- for l_dummy2_var
2715 END IF;
2716
2717 --Added by dpsingh for LE Uptake
2718 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_slpv_rec.khr_id) ;
2719 IF l_legal_entity_id IS NOT NULL THEN
2720 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
2721 ELSE
2722 Okl_Api.set_message(p_app_name => g_app_name,
2723 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
2724 p_token1 => 'CONTRACT_NUMBER',
2725 p_token1_value => l_cntrct_number);
2726 RAISE OKL_API.G_EXCEPTION_ERROR;
2727 END IF;
2728
2729 -- populate the transaction structure
2730 l_tcnv_rec.tcn_type := l_tcn_type;
2731 l_tcnv_rec.tsu_code := l_tsu_code;
2732 l_tcnv_rec.khr_id := p_slpv_rec.khr_id;
2733 l_tcnv_rec.description := p_slpv_rec.description;
2734 l_tcnv_rec.try_id := l_try_id;
2735 l_tcnv_rec.tax_deductible_local := p_slpv_rec.tax_deductible_local;
2736 l_tcnv_rec.tax_deductible_corporate := p_slpv_rec.tax_deductible_corporate;
2737 l_tcnv_rec.amount := p_slpv_rec.amount;
2738 l_tcnv_rec.currency_code := l_khr_currency_code;
2739 l_tcnv_rec.currency_conversion_type := l_currency_conv_type;
2740 l_tcnv_rec.currency_conversion_rate := l_currency_conv_rate;
2741 l_tcnv_rec.currency_conversion_date := l_currency_conv_date;
2742 l_tcnv_rec.set_of_books_id := l_set_of_books_id;
2743 l_tcnv_rec.description := p_slpv_rec.description;
2744 l_tcnv_rec.date_transaction_occurred := p_slpv_rec.provision_date;
2745 -- Bug 5935176 dpsingh for AE signature Uptake start
2746 OPEN get_dff_fields(l_tcnv_rec.khr_id);
2747 FETCH get_dff_fields into l_tcnv_rec.ATTRIBUTE_CATEGORY,
2748 l_tcnv_rec.ATTRIBUTE1,
2749 l_tcnv_rec.ATTRIBUTE2,
2750 l_tcnv_rec.ATTRIBUTE3,
2751 l_tcnv_rec.ATTRIBUTE4,
2752 l_tcnv_rec.ATTRIBUTE5,
2753 l_tcnv_rec.ATTRIBUTE6,
2754 l_tcnv_rec.ATTRIBUTE7,
2755 l_tcnv_rec.ATTRIBUTE8,
2756 l_tcnv_rec.ATTRIBUTE9,
2757 l_tcnv_rec.ATTRIBUTE10,
2758 l_tcnv_rec.ATTRIBUTE11,
2759 l_tcnv_rec.ATTRIBUTE12,
2760 l_tcnv_rec.ATTRIBUTE13,
2761 l_tcnv_rec.ATTRIBUTE14,
2762 l_tcnv_rec.ATTRIBUTE15;
2763 CLOSE get_dff_fields;
2764 -- Bug 5935176 dpsingh for AE signature Uptake end
2765 -- populate the transaction line structure
2766 l_tclv_tbl(1).khr_id := p_slpv_rec.khr_id;
2767 l_tclv_tbl(1).line_number := 1;
2768 l_tclv_tbl(1).description := p_slpv_rec.description;
2769 l_tclv_tbl(1).amount := p_slpv_rec.amount;
2770 l_tclv_tbl(1).currency_code := l_khr_currency_code;
2771 l_tclv_tbl(1).tcl_type := l_tcl_type;
2772 l_tclv_tbl(1).sty_id := p_slpv_rec.sty_id;
2773
2774 -- create transaction and transaction lines
2775 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(
2776 p_api_version => p_api_version
2777 ,p_init_msg_list => p_init_msg_list
2778 ,x_return_status => l_return_status
2779 ,x_msg_count => x_msg_count
2780 ,x_msg_data => x_msg_data
2781 ,p_tcnv_rec => l_tcnv_rec
2782 ,p_tclv_tbl => l_tclv_tbl
2783 ,x_tcnv_rec => x_tcnv_rec
2784 ,x_tclv_tbl => x_tclv_tbl);
2785 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2786 Okl_Api.set_message(p_app_name => g_app_name,
2787 p_msg_name => 'OKL_AGN_TRX_CRE_ERROR',
2788 p_token1 => g_contract_number_token,
2789 p_token1_value => l_cntrct_number);
2790 RAISE OKL_API.G_EXCEPTION_ERROR;
2791 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2792 Okl_Api.set_message(p_app_name => g_app_name,
2793 p_msg_name => 'OKL_AGN_TRX_CRE_ERROR',
2794 p_token1 => g_contract_number_token,
2795 p_token1_value => l_cntrct_number);
2796 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2797 END IF;
2798
2799 --get acc gen sources and value. Bug 3596651
2800 GET_ACCOUNT_GEN_DETAILS(
2801 p_contract_id => p_slpv_rec.khr_id,
2802 x_return_status => l_return_status,
2803 x_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
2804 --check for error
2805 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2806 Okl_Api.set_message(p_app_name => g_app_name,
2807 p_msg_name => 'OKL_AGN_ACC_GEN_ERROR',
2808 p_token1 => g_contract_number_token,
2809 p_token1_value => l_cntrct_number);
2810 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2811 END IF;
2812
2813
2814 --Bug 4622198.
2815 OKL_SECURITIZATION_PVT.check_khr_ia_associated(
2816 p_api_version => p_api_version
2817 ,p_init_msg_list => p_init_msg_list
2818 ,x_return_status => l_return_status
2819 ,x_msg_count => x_msg_count
2820 ,x_msg_data => x_msg_data
2821 ,p_khr_id => p_slpv_rec.khr_id
2822 ,p_scs_code => l_scs_code
2823 ,p_trx_date => p_slpv_rec.provision_date
2824 ,x_fact_synd_code => l_fact_sync_code
2825 ,x_inv_acct_code => l_inv_acct_code
2826 );
2827
2828 -- store the highest degree of error
2829 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2830 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2831 -- need to leave
2832 Okl_Api.set_message(p_app_name => g_app_name,
2833 p_msg_name => 'OKL_ACC_SEC_PVT_ERROR');
2834 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2835 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2836 Okl_Api.set_message(p_app_name => g_app_name,
2837 p_msg_name => 'OKL_ACC_SEC_PVT_ERROR');
2838 RAISE OKL_API.G_EXCEPTION_ERROR;
2839 END IF;
2840 END IF;
2841
2842 --Added by kthiruva for SLA Uptake
2843 --Bug 5707866 - Start of Changes
2844 FOR i IN x_tclv_tbl.FIRST..x_tclv_tbl.LAST
2845 LOOP
2846 --Assigning the account generator table
2847 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
2848 l_acc_gen_tbl(i).source_id := x_tclv_tbl(i).id;
2849
2850 l_tmpl_identify_tbl(i).product_id := l_product_id;
2851 l_tmpl_identify_tbl(i).stream_type_id := x_tclv_tbl(i).sty_id;
2852 l_tmpl_identify_tbl(i).transaction_type_id := l_try_id;
2853 l_tmpl_identify_tbl(i).advance_arrears := NULL;
2854 l_tmpl_identify_tbl(i).prior_year_yn := 'N';
2855 l_tmpl_identify_tbl(i).memo_yn := 'N';
2856 --Bug 4622198.
2857 l_tmpl_identify_tbl(i).factoring_synd_flag := l_fact_sync_code;
2858 l_tmpl_identify_tbl(i).investor_code := l_inv_acct_code;
2859
2860 l_dist_info_tbl(i).amount := x_tclv_tbl(i).amount;
2861 l_dist_info_tbl(i).accounting_date := x_tcnv_rec.date_transaction_occurred;
2862 l_dist_info_tbl(i).source_table := l_source_table;
2863 l_dist_info_tbl(i).currency_code := x_tcnv_rec.currency_code;
2864 l_dist_info_tbl(i).currency_conversion_type := x_tcnv_rec.currency_conversion_type;
2865 l_dist_info_tbl(i).currency_conversion_rate := x_tcnv_rec.currency_conversion_rate;
2866 l_dist_info_tbl(i).currency_conversion_date := x_tcnv_rec.currency_conversion_date;
2867 l_dist_info_tbl(i).source_id := x_tclv_tbl(i).id;
2868 l_dist_info_tbl(i).post_to_gl := 'Y';
2869 l_dist_info_tbl(i).gl_reversal_flag := 'N';
2870 END LOOP;
2871
2872 l_tcn_id := x_tcnv_rec.id;
2873 -- Calling the new Accounting Engine Signature
2874 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
2875 p_api_version => l_api_version,
2876 p_init_msg_list => p_init_msg_list,
2877 x_return_status => l_return_status,
2878 x_msg_count => x_msg_count,
2879 x_msg_data => x_msg_data,
2880 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
2881 p_dist_info_tbl => l_dist_info_tbl,
2882 p_ctxt_val_tbl => l_ctxt_tbl,
2883 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
2884 x_template_tbl => l_template_out_tbl,
2885 x_amount_tbl => l_amount_out_tbl,
2886 p_trx_header_id => l_tcn_id);
2887
2888 -- store the highest degree of error
2889 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2890 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2891 -- need to leave
2892 Okl_Api.set_message(p_app_name => g_app_name,
2893 p_msg_name => 'OKL_AGN_CRE_DIST_ERROR',
2894 p_token1 => g_contract_number_token,
2895 p_token1_value => l_cntrct_number);
2896 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2897 ELSE
2898 -- record that there was an error
2899 Okl_Api.set_message(p_app_name => g_app_name,
2900 p_msg_name => 'OKL_AGN_CRE_DIST_ERROR',
2901 p_token1 => g_contract_number_token,
2902 p_token1_value => l_cntrct_number);
2903 RAISE OKL_API.G_EXCEPTION_ERROR;
2904 END IF;
2905 END IF;
2906 --Bug 5707866 - End of Changes
2907
2908 -- SGIYER - MGAAP BUG 7263041
2909 OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX
2910 (p_api_version => p_api_version
2911 ,p_init_msg_list => p_init_msg_list
2912 ,x_return_status => x_return_status
2913 ,x_msg_count => x_msg_count
2914 ,x_msg_data => x_msg_data
2915 ,P_TCNV_REC => x_tcnv_rec
2916 ,P_TCLV_TBL => x_tclv_tbl
2917 ,p_ctxt_val_tbl => l_ctxt_tbl
2918 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
2919
2920 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2921 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2922 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2923 RAISE Okl_Api.G_EXCEPTION_ERROR;
2924 END IF;
2925
2926 -- set the return status
2927 x_return_status := l_return_status;
2928
2929 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2930
2931 EXCEPTION
2932 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2933 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
2934 ,g_pkg_name
2935 ,'OKL_API.G_RET_STS_ERROR'
2936 ,x_msg_count
2937 ,x_msg_data
2938 ,'_PVT');
2939
2940 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2941 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
2942 ,g_pkg_name
2943 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2944 ,x_msg_count
2945 ,x_msg_data
2946 ,'_PVT');
2947
2948 WHEN OTHERS THEN
2949 IF pdt_id_csr%ISOPEN THEN
2950 CLOSE pdt_id_csr;
2951 END IF;
2952
2953 IF contract_num_csr%ISOPEN THEN
2954 CLOSE contract_num_csr;
2955 END IF;
2956
2957 IF currency_info_csr%ISOPEN THEN
2958 CLOSE currency_info_csr;
2959 END IF;
2960
2961 IF trx_types_csr%ISOPEN THEN
2962 CLOSE trx_types_csr;
2963 END IF;
2964
2965 IF gen_loss_trx_csr%ISOPEN THEN
2966 CLOSE gen_loss_trx_csr;
2967 END IF;
2968
2969 IF spec_loss_trx_csr%ISOPEN THEN
2970 CLOSE spec_loss_trx_csr;
2971 END IF;
2972
2973 -- store SQL error message on message stack for caller
2974 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
2975 p_msg_name => g_unexpected_error,
2976 p_token1 => g_sqlcode_token,
2977 p_token1_value => SQLCODE,
2978 p_token2 => g_sqlerrm_token,
2979 p_token2_value => SQLERRM);
2980
2981 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2982 (l_api_name,
2983 G_PKG_NAME,
2984 'OTHERS',
2985 x_msg_count,
2986 x_msg_data,
2987 '_PVT');
2988
2989 END SPECIFIC_LOSS_PROVISION;
2990
2991 END OKL_LOSS_PROV_PVT;