[Home] [Help]
PACKAGE BODY: APPS.OKL_DEAL_CHARGES_PVT
Source
1 Package body OKL_DEAL_CHARGES_PVT as
2 /* $Header: OKLRKACB.pls 120.2.12020000.2 2012/08/07 11:48:56 bkatraga ship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL OKL MESSAGES
5 -------------------------------------------------------------------------------------------------
6 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
7 G_MISSING_CONTRACT CONSTANT Varchar2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
8 G_CONTRACT_ID_TOKEN CONSTANT Varchar2(30) := 'CONTRACT_ID';
9 -------------------------------------------------------------------------------------------------
10 -- GLOBAL EXCEPTION
11 -------------------------------------------------------------------------------------------------
12 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
13 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
14 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
15 G_API_VERSION CONSTANT NUMBER := 1.0;
16 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
17 -------------------------------------------------------------------------------------------------
18
19 TYPE asset_rec_type IS RECORD (fin_asset_id OKC_K_LINES_B.id%TYPE,
20 amount OKL_K_LINES.amount%TYPE,
21 asset_number OKC_K_LINES_TL.name%TYPE,
22 description OKC_K_LINES_TL.item_description%TYPE,
23 oec OKL_K_LINES.oec%TYPE,
24 cleb_cov_asset_id OKC_K_LINES_B.id%TYPE,
25 cim_cov_asset_id OKC_K_ITEMS.id%TYPE);
26
27 TYPE asset_tbl_type IS TABLE OF asset_rec_type INDEX BY BINARY_INTEGER;
28
29 PROCEDURE delete_fee_service(
30 p_api_version IN NUMBER,
31 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 p_id IN NUMBER) IS
36 BEGIN
37 null;
38 END delete_fee_service;
39
40 PROCEDURE delete_usage(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2,
46 p_id IN NUMBER) IS
47 BEGIN
48 null;
49 END delete_usage;
50
51 PROCEDURE delete_insurance(
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_id IN NUMBER) IS
58 BEGIN
59 null;
60 END delete_insurance;
61
62 PROCEDURE allocate_amount_charges (
63 p_api_version IN NUMBER,
64 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER,
67 x_msg_data OUT NOCOPY VARCHAR2,
68 p_chr_id IN NUMBER,
69 p_cle_id IN NUMBER DEFAULT NULL,
70 p_amount IN NUMBER,
71 p_mode IN VARCHAR2,
72 x_cov_asset_tbl OUT NOCOPY cov_asset_tbl_type) IS
73
74
75 l_api_name CONSTANT VARCHAR2(30) := 'allocate_amount_charges';
76 l_api_version CONSTANT NUMBER := 1.0;
77
78 CURSOR c_assets IS
79 SELECT cleb_fin.id fin_asset_id,
80 clet_fin.name asset_number,
81 clet_fin.item_description description,
82 NVL(kle_fin.oec,0) oec
83 FROM okc_k_lines_b cleb_fin,
84 okc_k_lines_tl clet_fin,
85 okl_k_lines kle_fin,
86 okc_line_styles_b lse_fin,
87 okc_statuses_b sts
88 WHERE cleb_fin.dnz_chr_id = p_chr_id
89 AND cleb_fin.chr_id = p_chr_id
90 AND clet_fin.id = cleb_fin.id
91 AND clet_fin.language = USERENV('LANG')
92 AND cleb_fin.id = kle_fin.id
93 AND lse_fin.id = cleb_fin.lse_id
94 AND lse_fin.lty_code = 'FREE_FORM1'
95 AND cleb_fin.sts_code = sts.code
96 AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED');
97
98 CURSOR c_term_cov_asset_lines(p_chr_id IN NUMBER,
99 p_cle_id IN NUMBER) is
100 SELECT SUM(NVL(kle_cov_asset.capital_amount,kle_cov_asset.amount)) amount
101 FROM okc_k_lines_b cleb_cov_asset,
102 okl_k_lines kle_cov_asset
103 WHERE cleb_cov_asset.dnz_chr_id = p_chr_id
104 AND cleb_cov_asset.cle_id = p_cle_id
105 AND cleb_cov_asset.sts_code = 'TERMINATED'
106 AND kle_cov_asset.id = cleb_cov_asset.id;
107
108 CURSOR c_cov_asset_line(p_chr_id IN NUMBER,
109 p_cle_id IN NUMBER,
110 p_fin_ast_id IN NUMBER) IS
111 SELECT cov_ast_cle.id cov_ast_cle_id,
112 cov_ast_cim.id cov_ast_cim_id
113 FROM okc_k_lines_b cov_ast_cle,
114 okc_k_items cov_ast_cim
115 WHERE cov_ast_cle.dnz_chr_id = p_chr_id
116 AND cov_ast_cle.cle_id = p_cle_id
117 AND cov_ast_cim.cle_id = cov_ast_cle.id
118 AND cov_ast_cim.object1_id1 = TO_CHAR(p_fin_ast_id)
119 AND cov_ast_cim.object1_id2 = '#'
120 and cov_ast_cim.jtot_object1_code = 'OKX_COVASST';
121
122 l_term_lines_cov_asset_amt NUMBER;
123 i NUMBER := 0;
124 l_chr_id OKC_K_HEADERS_B.id%TYPE;
125 l_cle_id OKC_K_LINES_B.id%TYPE;
126 l_amount NUMBER := 0;
127 l_oec_total NUMBER := 0;
128 l_assoc_amount NUMBER;
129 l_assoc_total NUMBER := 0;
130 l_diff NUMBER;
131 l_currency_code OKC_K_HEADERS_B.currency_code%TYPE;
132 l_asset_tbl asset_tbl_type;
133 l_cov_asset_tbl cov_asset_tbl_type;
134
135 BEGIN
136
137 -- call START_ACTIVITY to create savepoint, check compatibility
138 -- and initialize message list
139 x_return_status := OKL_API.START_ACTIVITY(
140 p_api_name => l_api_name,
141 p_pkg_name => g_pkg_name,
142 p_init_msg_list => p_init_msg_list,
143 l_api_version => l_api_version,
144 p_api_version => p_api_version,
145 p_api_type => g_api_type,
146 x_return_status => x_return_status);
147
148 -- check if activity started successfully
149 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
150 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
151 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
152 raise OKL_API.G_EXCEPTION_ERROR;
153 End If;
154
155 l_chr_id := p_chr_id;
156 l_cle_id := p_cle_id;
157 If okl_context.get_okc_org_id is null then
158 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
159 End If;
160
161 l_amount := p_amount;
162
163 i := 0;
164
165 FOR l_asset IN c_assets LOOP
166 i := i + 1;
167
168 l_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
169 l_asset_tbl(i).asset_number := l_asset.asset_number;
170 l_asset_tbl(i).description := l_asset.description;
171 l_asset_tbl(i).oec := l_asset.oec;
172
173 l_asset_tbl(i).cleb_cov_asset_id := NULL;
174 l_asset_tbl(i).cim_cov_asset_id := NULL;
175
176 IF p_mode = 'UPDATE' THEN
177
178 OPEN c_cov_asset_line(p_chr_id => l_chr_id,
179 p_cle_id => l_cle_id,
180 p_fin_ast_id => l_asset.fin_asset_id);
181 FETCH c_cov_asset_line INTO l_asset_tbl(i).cleb_cov_asset_id,
182 l_asset_tbl(i).cim_cov_asset_id;
183 CLOSE c_cov_asset_line;
184
185 END IF;
186 END LOOP;
187
188 IF p_mode = 'UPDATE' THEN
189 -- Exclude Terminated line covered asset amounts from
190 -- total amount available for allocation
191 l_term_lines_cov_asset_amt := 0;
192 OPEN c_term_cov_asset_lines(p_chr_id => l_chr_id,
193 p_cle_id => l_cle_id);
194 FETCH c_term_cov_asset_lines INTO l_term_lines_cov_asset_amt;
195 CLOSE c_term_cov_asset_lines;
196
197 l_amount := l_amount - NVL(l_term_lines_cov_asset_amt,0);
198
199 IF l_amount < 0 THEN
200 OKL_API.SET_MESSAGE(p_app_name => g_app_name
201 ,p_msg_name => 'OKL_LA_NEGATIVE_COV_AST_AMT'
202 ,p_token1 => 'AMOUNT'
203 ,p_token1_value => TO_CHAR(NVL(l_term_lines_cov_asset_amt,0)));
204 RAISE OKL_API.G_EXCEPTION_ERROR;
205 END IF;
206 END IF;
207
208 IF (l_asset_tbl.COUNT > 0) THEN
209
210 ------------------------------------------------------------------
211 -- 1. Loop through to get OEC total of all assets being associated
212 ------------------------------------------------------------------
213 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
214
215 IF l_asset_tbl.EXISTS(i) THEN
216 l_oec_total := l_oec_total + l_asset_tbl(i).oec;
217 END IF;
218
219 END LOOP;
220
221 SELECT currency_code
222 INTO l_currency_code
223 FROM okc_k_headers_b
224 WHERE id = l_chr_id;
225
226 ----------------------------------------------------------------------------
227 -- 2. Loop through to determine associated amounts and round off the amounts
228 ----------------------------------------------------------------------------
229 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
230
231 IF l_asset_tbl.EXISTS(i) THEN
232
233 IF l_asset_tbl.COUNT = 1 THEN
234
235 l_assoc_amount := l_amount;
236
237 ELSE
238
239 l_assoc_amount := l_amount * l_asset_tbl(i).oec / l_oec_total;
240
241 END IF;
242
243 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
244 p_currency_code => l_currency_code);
245
246 l_assoc_total := l_assoc_total + l_assoc_amount;
247
248
249 l_cov_asset_tbl(i).cleb_cov_asset_id := l_asset_tbl(i).cleb_cov_asset_id;
250 l_cov_asset_tbl(i).cleb_cov_asset_cle_id := l_cle_id;
251 l_cov_asset_tbl(i).dnz_chr_id := l_chr_id;
252 l_cov_asset_tbl(i).asset_number := l_asset_tbl(i).asset_number;
253 l_cov_asset_tbl(i).description := l_asset_tbl(i).description;
254 l_cov_asset_tbl(i).capital_amount := l_assoc_amount;
255 l_cov_asset_tbl(i).cim_cov_asset_id := l_asset_tbl(i).cim_cov_asset_id;
256 l_cov_asset_tbl(i).object1_id1 := l_asset_tbl(i).fin_asset_id;
257 l_cov_asset_tbl(i).object1_id2 := '#';
258 l_cov_asset_tbl(i).jtot_object1_code := 'OKX_COVASST';
259
260 END IF;
261
262 END LOOP;
263
264 ----------------------------------------------------------------------------------------------------
265 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
266 ----------------------------------------------------------------------------------------------------
267 IF l_assoc_total <> l_amount THEN
268
269 l_diff := l_amount - l_assoc_total;
270
271 l_cov_asset_tbl(l_cov_asset_tbl.FIRST).capital_amount := l_cov_asset_tbl(l_cov_asset_tbl.FIRST).capital_amount + l_diff;
272
273 END IF;
274
275 END IF;
276
277 x_cov_asset_tbl := l_cov_asset_tbl;
278
279 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
280
281 EXCEPTION
282
283 when OKL_API.G_EXCEPTION_ERROR then
284 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
285 p_api_name => l_api_name,
286 p_pkg_name => g_pkg_name,
287 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_api_type => g_api_type);
291
292 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
293 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
294 p_api_name => l_api_name,
295 p_pkg_name => g_pkg_name,
296 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data,
299 p_api_type => g_api_type);
300
301 when OTHERS then
302 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
303 p_api_name => l_api_name,
304 p_pkg_name => g_pkg_name,
305 p_exc_name => 'OTHERS',
306 x_msg_count => x_msg_count,
307 x_msg_data => x_msg_data,
308 p_api_type => g_api_type);
309
310 END allocate_amount_charges;
311
312 PROCEDURE create_fee (
313 p_api_version IN NUMBER,
314 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_msg_count OUT NOCOPY NUMBER,
317 x_msg_data OUT NOCOPY VARCHAR2,
318 p_fee_rec IN fee_rec_type,
319 x_fee_rec OUT NOCOPY fee_rec_type) IS
320
321
322 l_api_name CONSTANT VARCHAR2(30) := 'create_fee';
323 l_api_version CONSTANT NUMBER := 1.0;
324
325 l_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
326 x_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
327
328 i NUMBER;
329 l_rgr_tbl OKL_RGRP_RULES_PROCESS_PVT.rgr_tbl_type;
330 -- Bug# 7611623
331 l_check_exception BOOLEAN := false;
332 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
333
334 BEGIN
335
336 -- call START_ACTIVITY to create savepoint, check compatibility
337 -- and initialize message list
338 x_return_status := OKL_API.START_ACTIVITY(
339 p_api_name => l_api_name,
340 p_pkg_name => g_pkg_name,
341 p_init_msg_list => p_init_msg_list,
342 l_api_version => l_api_version,
343 p_api_version => p_api_version,
344 p_api_type => g_api_type,
345 x_return_status => x_return_status);
346
347 -- check if activity started successfully
348 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
349 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
350 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
351 raise OKL_API.G_EXCEPTION_ERROR;
352 End If;
353
354 l_fee_types_rec.dnz_chr_id := p_fee_rec.dnz_chr_id;
355 l_fee_types_rec.fee_type := p_fee_rec.fee_type;
356 l_fee_types_rec.item_name := p_fee_rec.cim_fee_sty_name;
357 l_fee_types_rec.item_id1 := p_fee_rec.cim_fee_object1_id1;
358 l_fee_types_rec.item_id2 := p_fee_rec.cim_fee_object1_id2;
359 l_fee_types_rec.party_name := p_fee_rec.cplb_fee_vendor_name;
360 l_fee_types_rec.party_id1 := p_fee_rec.cplb_fee_object1_id1;
361 l_fee_types_rec.party_id2 := p_fee_rec.cplb_fee_object1_id2;
362 l_fee_types_rec.effective_from := p_fee_rec.start_date;
363 l_fee_types_rec.effective_to := p_fee_rec.end_date;
364 l_fee_types_rec.amount := p_fee_rec.amount;
365 l_fee_types_rec.initial_direct_cost := p_fee_rec.initial_direct_cost;
366 l_fee_types_rec.roll_qt := p_fee_rec.rollover_term_quote_number;
367 l_fee_types_rec.qte_id := p_fee_rec.qte_id;
368 l_fee_types_rec.funding_date := p_fee_rec.funding_date;
369 l_fee_types_rec.fee_purpose_code := p_fee_rec.fee_purpose_code;
370 l_fee_types_rec.attribute_category := p_fee_rec.attribute_category;
371 l_fee_types_rec.attribute1 := p_fee_rec.attribute1;
372 l_fee_types_rec.attribute2 := p_fee_rec.attribute2;
373 l_fee_types_rec.attribute3 := p_fee_rec.attribute3;
374 l_fee_types_rec.attribute4 := p_fee_rec.attribute4;
375 l_fee_types_rec.attribute5 := p_fee_rec.attribute5;
376 l_fee_types_rec.attribute6 := p_fee_rec.attribute6;
377 l_fee_types_rec.attribute7 := p_fee_rec.attribute7;
378 l_fee_types_rec.attribute8 := p_fee_rec.attribute8;
379 l_fee_types_rec.attribute9 := p_fee_rec.attribute9;
380 l_fee_types_rec.attribute10 := p_fee_rec.attribute10;
381 l_fee_types_rec.attribute11 := p_fee_rec.attribute11;
382 l_fee_types_rec.attribute12 := p_fee_rec.attribute12;
383 l_fee_types_rec.attribute13 := p_fee_rec.attribute13;
384 l_fee_types_rec.attribute14 := p_fee_rec.attribute14;
385 l_fee_types_rec.attribute15 := p_fee_rec.attribute15;
386 l_fee_types_rec.validate_dff_yn := p_fee_rec.validate_dff_yn;
387 --Added by jjuneja
388 l_fee_types_rec.origination_income :=p_fee_rec.origination_income;
389 --End of addition by jjuneja
390
391
392 OKL_MAINTAIN_FEE_PVT.create_fee_type(
393 p_api_version => p_api_version,
394 p_init_msg_list => p_init_msg_list,
395 x_return_status => x_return_status,
396 x_msg_count => x_msg_count,
397 x_msg_data => x_msg_data,
398 p_fee_types_rec => l_fee_types_rec,
399 x_fee_types_rec => x_fee_types_rec);
400
401 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
402 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
403 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
404 raise OKL_API.G_EXCEPTION_ERROR;
405 End If;
406
407 x_fee_rec := p_fee_rec;
408 x_fee_rec.cleb_fee_id := x_fee_types_rec.line_id;
409 x_fee_rec.cim_fee_id := x_fee_types_rec.item_id;
410 x_fee_rec.cplb_fee_id := x_fee_types_rec.party_id;
411
412 -- Bug# 7611623
413 -- Number of periods, Amount Per Period and Frequency are mandatory for
414 -- Expense, Financed and Miscellaneoud fees
415 l_check_exception := false;
416 IF (p_fee_rec.fee_type IN ('EXPENSE', 'MISCELLANEOUS', 'FINANCED')) THEN
417
418 IF (p_fee_rec.rul_lafexp_rule_information1 IS NULL OR p_fee_rec.rul_lafexp_rule_information1 = OKL_API.G_MISS_CHAR) THEN
419 OKL_API.set_message(p_app_name => G_APP_NAME,
420 p_msg_name => G_REQUIRED_VALUE,
421 p_token1 => G_COL_NAME_TOKEN,
422 p_token1_value => 'Number Of Periods');
423 l_check_exception := true;
424 END IF;
425 IF (p_fee_rec.rul_lafexp_rule_information2 IS NULL OR p_fee_rec.rul_lafexp_rule_information2 = OKL_API.G_MISS_CHAR) THEN
426 OKL_API.set_message(p_app_name => G_APP_NAME,
427 p_msg_name => G_REQUIRED_VALUE,
428 p_token1 => G_COL_NAME_TOKEN,
429 p_token1_value => 'Amount Per Period');
430 l_check_exception := true;
431 END IF;
432
433 IF (p_fee_rec.rul_lafreq_object1_id1 IS NULL OR p_fee_rec.rul_lafreq_object1_id1 = OKL_API.G_MISS_CHAR) THEN
434 OKL_API.set_message(p_app_name => G_APP_NAME,
435 p_msg_name => G_REQUIRED_VALUE,
436 p_token1 => G_COL_NAME_TOKEN,
437 p_token1_value => 'Frequency');
438 l_check_exception := true;
439 END IF;
440
441 END IF;
442
443 IF (l_check_exception) THEN
444 RAISE OKL_API.G_EXCEPTION_ERROR;
445 END IF;
446
447
448 IF (x_fee_rec.rul_lafreq_object1_id1 IS NOT NULL AND x_fee_rec.rul_lafreq_object1_id1 <> OKL_API.G_MISS_CHAR)
449 OR (x_fee_rec.rul_lafexp_rule_information1 IS NOT NULL AND x_fee_rec.rul_lafexp_rule_information1 <> OKL_API.G_MISS_CHAR)
450 OR (x_fee_rec.rul_lafexp_rule_information2 IS NOT NULL AND x_fee_rec.rul_lafexp_rule_information2 <> OKL_API.G_MISS_CHAR)
451 THEN
452
453 i := 1;
454
455 l_rgr_tbl(i).rgd_code := 'LAFEXP';
456 l_rgr_tbl(i).dnz_chr_id := x_fee_rec.dnz_chr_id;
457 l_rgr_tbl(i).sfwt_flag := OKL_API.G_FALSE;
458 l_rgr_tbl(i).std_template_yn := 'N';
459 l_rgr_tbl(i).warn_yn := 'N';
460 l_rgr_tbl(i).template_yn := 'N';
461 l_rgr_tbl(i).rule_information_category := 'LAFREQ';
462 l_rgr_tbl(i).object1_id1 := x_fee_rec.rul_lafreq_object1_id1;
463 l_rgr_tbl(i).object1_id2 := x_fee_rec.rul_lafreq_object1_id2;
464 l_rgr_tbl(i).jtot_object1_code := x_fee_rec.rul_lafreq_object1_code;
465
466 i := i + 1;
467
468 l_rgr_tbl(i).rgd_code := 'LAFEXP';
469 l_rgr_tbl(i).dnz_chr_id := x_fee_rec.dnz_chr_id;
470 l_rgr_tbl(i).sfwt_flag := OKL_API.G_FALSE;
471 l_rgr_tbl(i).std_template_yn := 'N';
472 l_rgr_tbl(i).warn_yn := 'N';
473 l_rgr_tbl(i).template_yn := 'N';
474 l_rgr_tbl(i).rule_information_category := 'LAFEXP';
475 l_rgr_tbl(i).rule_information1 := x_fee_rec.rul_lafexp_rule_information1;
476 l_rgr_tbl(i).rule_information2 := x_fee_rec.rul_lafexp_rule_information2;
477
478 OKL_RGRP_RULES_PROCESS_PVT.process_rule_group_rules(
479 p_api_version => p_api_version,
480 p_init_msg_list => p_init_msg_list,
481 x_return_status => x_return_status,
482 x_msg_count => x_msg_count,
483 x_msg_data => x_msg_data,
484 p_chr_id => x_fee_rec.dnz_chr_id,
485 p_line_id => x_fee_rec.cleb_fee_id,
486 p_cpl_id => NULL,
487 p_rrd_id => NULL,
488 p_rgr_tbl => l_rgr_tbl);
489
490 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
491 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
492 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
493 raise OKL_API.G_EXCEPTION_ERROR;
494 End If;
495
496 OKL_CONTRACT_TOP_LINE_PVT.validate_fee_expense_rule(
497 p_api_version => p_api_version,
498 p_init_msg_list => p_init_msg_list,
499 x_return_status => x_return_status,
500 x_msg_count => x_msg_count,
501 x_msg_data => x_msg_data,
502 p_chr_id => x_fee_rec.dnz_chr_id,
503 p_line_id => x_fee_rec.cleb_fee_id,
504 p_no_of_period => x_fee_rec.rul_lafexp_rule_information1,
505 p_frequency => x_fee_rec.frequency_name,
506 p_amount_per_period => x_fee_rec.rul_lafexp_rule_information2);
507
508 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
509 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
510 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
511 raise OKL_API.G_EXCEPTION_ERROR;
512 End If;
513
514 END IF;
515
516 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
517
518 EXCEPTION
519
520 when OKL_API.G_EXCEPTION_ERROR then
521 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
522 p_api_name => l_api_name,
523 p_pkg_name => g_pkg_name,
524 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
525 x_msg_count => x_msg_count,
526 x_msg_data => x_msg_data,
527 p_api_type => g_api_type);
528
529 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
530 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
531 p_api_name => l_api_name,
532 p_pkg_name => g_pkg_name,
533 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data,
536 p_api_type => g_api_type);
537
538 when OTHERS then
539 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
540 p_api_name => l_api_name,
541 p_pkg_name => g_pkg_name,
542 p_exc_name => 'OTHERS',
543 x_msg_count => x_msg_count,
544 x_msg_data => x_msg_data,
545 p_api_type => g_api_type);
546
547 END create_fee;
548
549 PROCEDURE update_fee (
550 p_api_version IN NUMBER,
551 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 p_fee_rec IN fee_rec_type,
556 x_fee_rec OUT NOCOPY fee_rec_type) IS
557
558
559 l_api_name CONSTANT VARCHAR2(30) := 'update_fee';
560 l_api_version CONSTANT NUMBER := 1.0;
561
562 l_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
563 x_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
564
565 i NUMBER;
566 l_rgr_tbl OKL_RGRP_RULES_PROCESS_PVT.rgr_tbl_type;
567 -- Bug# 7611623
568 l_check_exception BOOLEAN := false;
569 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
570
571 BEGIN
572
573 -- call START_ACTIVITY to create savepoint, check compatibility
574 -- and initialize message list
575 x_return_status := OKL_API.START_ACTIVITY(
576 p_api_name => l_api_name,
577 p_pkg_name => g_pkg_name,
578 p_init_msg_list => p_init_msg_list,
579 l_api_version => l_api_version,
580 p_api_version => p_api_version,
581 p_api_type => g_api_type,
582 x_return_status => x_return_status);
583
584 -- check if activity started successfully
585 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
586 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
587 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
588 raise OKL_API.G_EXCEPTION_ERROR;
589 End If;
590
591 l_fee_types_rec.line_id := p_fee_rec.cleb_fee_id;
592 l_fee_types_rec.dnz_chr_id := p_fee_rec.dnz_chr_id;
593 l_fee_types_rec.fee_type := p_fee_rec.fee_type;
594 l_fee_types_rec.item_id := p_fee_rec.cim_fee_id;
595 l_fee_types_rec.item_name := p_fee_rec.cim_fee_sty_name;
596 l_fee_types_rec.item_id1 := p_fee_rec.cim_fee_object1_id1;
597 l_fee_types_rec.item_id2 := p_fee_rec.cim_fee_object1_id2;
598 l_fee_types_rec.party_id := p_fee_rec.cplb_fee_id;
599 l_fee_types_rec.party_name := p_fee_rec.cplb_fee_vendor_name;
600 l_fee_types_rec.party_id1 := p_fee_rec.cplb_fee_object1_id1;
601 l_fee_types_rec.party_id2 := p_fee_rec.cplb_fee_object1_id2;
602 l_fee_types_rec.effective_from := p_fee_rec.start_date;
603 l_fee_types_rec.effective_to := p_fee_rec.end_date;
604 l_fee_types_rec.amount := p_fee_rec.amount;
605 l_fee_types_rec.initial_direct_cost := p_fee_rec.initial_direct_cost;
606 l_fee_types_rec.roll_qt := p_fee_rec.rollover_term_quote_number;
607 l_fee_types_rec.qte_id := p_fee_rec.qte_id;
608 l_fee_types_rec.funding_date := p_fee_rec.funding_date;
609 l_fee_types_rec.fee_purpose_code := p_fee_rec.fee_purpose_code;
610 l_fee_types_rec.attribute_category := p_fee_rec.attribute_category;
611 l_fee_types_rec.attribute1 := p_fee_rec.attribute1;
612 l_fee_types_rec.attribute2 := p_fee_rec.attribute2;
613 l_fee_types_rec.attribute3 := p_fee_rec.attribute3;
614 l_fee_types_rec.attribute4 := p_fee_rec.attribute4;
615 l_fee_types_rec.attribute5 := p_fee_rec.attribute5;
616 l_fee_types_rec.attribute6 := p_fee_rec.attribute6;
617 l_fee_types_rec.attribute7 := p_fee_rec.attribute7;
618 l_fee_types_rec.attribute8 := p_fee_rec.attribute8;
619 l_fee_types_rec.attribute9 := p_fee_rec.attribute9;
620 l_fee_types_rec.attribute10 := p_fee_rec.attribute10;
621 l_fee_types_rec.attribute11 := p_fee_rec.attribute11;
622 l_fee_types_rec.attribute12 := p_fee_rec.attribute12;
623 l_fee_types_rec.attribute13 := p_fee_rec.attribute13;
624 l_fee_types_rec.attribute14 := p_fee_rec.attribute14;
625 l_fee_types_rec.attribute15 := p_fee_rec.attribute15;
626 l_fee_types_rec.validate_dff_yn := p_fee_rec.validate_dff_yn;
627 --Added by jjuneja
628 l_fee_types_rec.origination_income :=p_fee_rec.origination_income;
629 --End of addition by jjuneja
630
631 OKL_MAINTAIN_FEE_PVT.update_fee_type(
632 p_api_version => p_api_version,
633 p_init_msg_list => p_init_msg_list,
634 x_return_status => x_return_status,
635 x_msg_count => x_msg_count,
636 x_msg_data => x_msg_data,
637 p_fee_types_rec => l_fee_types_rec,
638 x_fee_types_rec => x_fee_types_rec);
639
640 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
641 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
642 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
643 raise OKL_API.G_EXCEPTION_ERROR;
644 End If;
645
646 x_fee_rec := p_fee_rec;
647 x_fee_rec.cleb_fee_id := x_fee_types_rec.line_id;
648 x_fee_rec.cim_fee_id := x_fee_types_rec.item_id;
649 x_fee_rec.cplb_fee_id := x_fee_types_rec.party_id;
650
651 -- Bug# 7611623
652 -- Number of periods, Amount Per Period and Frequency are mandatory for
653 -- Expense, Financed and Miscellaneoud fees
654 l_check_exception := false;
655 IF (p_fee_rec.fee_type IN ('EXPENSE', 'MISCELLANEOUS', 'FINANCED')) THEN
656
657 IF (p_fee_rec.rul_lafexp_rule_information1 IS NULL OR p_fee_rec.rul_lafexp_rule_information1 = OKL_API.G_MISS_CHAR) THEN
658 OKL_API.set_message(p_app_name => G_APP_NAME,
659 p_msg_name => G_REQUIRED_VALUE,
660 p_token1 => G_COL_NAME_TOKEN,
661 p_token1_value => 'Number Of Periods');
662 l_check_exception := true;
663 END IF;
664 IF (p_fee_rec.rul_lafexp_rule_information2 IS NULL OR p_fee_rec.rul_lafexp_rule_information2 = OKL_API.G_MISS_CHAR) THEN
665 OKL_API.set_message(p_app_name => G_APP_NAME,
666 p_msg_name => G_REQUIRED_VALUE,
667 p_token1 => G_COL_NAME_TOKEN,
668 p_token1_value => 'Amount Per Period');
669 l_check_exception := true;
670 END IF;
671
672 IF (p_fee_rec.rul_lafreq_object1_id1 IS NULL OR p_fee_rec.rul_lafreq_object1_id1 = OKL_API.G_MISS_CHAR) THEN
673 OKL_API.set_message(p_app_name => G_APP_NAME,
674 p_msg_name => G_REQUIRED_VALUE,
675 p_token1 => G_COL_NAME_TOKEN,
676 p_token1_value => 'Frequency');
677 l_check_exception := true;
678 END IF;
679
680 END IF;
681
682 IF (l_check_exception) THEN
683 RAISE OKL_API.G_EXCEPTION_ERROR;
684 END IF;
685
686
687 IF (x_fee_rec.rul_lafreq_object1_id1 IS NOT NULL AND x_fee_rec.rul_lafreq_object1_id1 <> OKL_API.G_MISS_CHAR)
688 OR (x_fee_rec.rul_lafexp_rule_information1 IS NOT NULL AND x_fee_rec.rul_lafexp_rule_information1 <> OKL_API.G_MISS_CHAR)
689 OR (x_fee_rec.rul_lafexp_rule_information2 IS NOT NULL AND x_fee_rec.rul_lafexp_rule_information2 <> OKL_API.G_MISS_CHAR)
690 THEN
691
692 i := 1;
693
694 l_rgr_tbl(i).rgd_code := 'LAFEXP';
695 l_rgr_tbl(i).dnz_chr_id := x_fee_rec.dnz_chr_id;
696 l_rgr_tbl(i).sfwt_flag := OKL_API.G_FALSE;
697 l_rgr_tbl(i).std_template_yn := 'N';
698 l_rgr_tbl(i).warn_yn := 'N';
699 l_rgr_tbl(i).template_yn := 'N';
700 l_rgr_tbl(i).rule_information_category := 'LAFREQ';
701 l_rgr_tbl(i).object1_id1 := x_fee_rec.rul_lafreq_object1_id1;
702 l_rgr_tbl(i).object1_id2 := x_fee_rec.rul_lafreq_object1_id2;
703 l_rgr_tbl(i).jtot_object1_code := x_fee_rec.rul_lafreq_object1_code;
704
705 i := i + 1;
706
707 l_rgr_tbl(i).rgd_code := 'LAFEXP';
708 l_rgr_tbl(i).dnz_chr_id := x_fee_rec.dnz_chr_id;
709 l_rgr_tbl(i).sfwt_flag := OKL_API.G_FALSE;
710 l_rgr_tbl(i).std_template_yn := 'N';
711 l_rgr_tbl(i).warn_yn := 'N';
712 l_rgr_tbl(i).template_yn := 'N';
713 l_rgr_tbl(i).rule_information_category := 'LAFEXP';
714 l_rgr_tbl(i).rule_information1 := x_fee_rec.rul_lafexp_rule_information1;
715 l_rgr_tbl(i).rule_information2 := x_fee_rec.rul_lafexp_rule_information2;
716
717 OKL_RGRP_RULES_PROCESS_PVT.process_rule_group_rules(
718 p_api_version => p_api_version,
719 p_init_msg_list => p_init_msg_list,
720 x_return_status => x_return_status,
721 x_msg_count => x_msg_count,
722 x_msg_data => x_msg_data,
723 p_chr_id => x_fee_rec.dnz_chr_id,
724 p_line_id => x_fee_rec.cleb_fee_id,
725 p_cpl_id => NULL,
726 p_rrd_id => NULL,
727 p_rgr_tbl => l_rgr_tbl);
728
729 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
730 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
731 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
732 raise OKL_API.G_EXCEPTION_ERROR;
733 End If;
734
735 OKL_CONTRACT_TOP_LINE_PVT.validate_fee_expense_rule(
736 p_api_version => p_api_version,
737 p_init_msg_list => p_init_msg_list,
738 x_return_status => x_return_status,
739 x_msg_count => x_msg_count,
740 x_msg_data => x_msg_data,
741 p_chr_id => x_fee_rec.dnz_chr_id,
742 p_line_id => x_fee_rec.cleb_fee_id,
743 p_no_of_period => x_fee_rec.rul_lafexp_rule_information1,
744 p_frequency => x_fee_rec.frequency_name,
745 p_amount_per_period => x_fee_rec.rul_lafexp_rule_information2);
746
747 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
748 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
749 Elsif (x_return_status = OKL_API.G_RET_STS_ERROR) then
750 raise OKL_API.G_EXCEPTION_ERROR;
751 End If;
752
753 END IF;
754
755 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
756
757
758 EXCEPTION
759
760 when OKL_API.G_EXCEPTION_ERROR then
761 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
762 p_api_name => l_api_name,
763 p_pkg_name => g_pkg_name,
764 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
765 x_msg_count => x_msg_count,
766 x_msg_data => x_msg_data,
767 p_api_type => g_api_type);
768
769 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
770 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
771 p_api_name => l_api_name,
772 p_pkg_name => g_pkg_name,
773 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
774 x_msg_count => x_msg_count,
775 x_msg_data => x_msg_data,
776 p_api_type => g_api_type);
777
778 when OTHERS then
779 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
780 p_api_name => l_api_name,
781 p_pkg_name => g_pkg_name,
782 p_exc_name => 'OTHERS',
783 x_msg_count => x_msg_count,
784 x_msg_data => x_msg_data,
785 p_api_type => g_api_type);
786
787 END update_fee;
788
789 End OKL_DEAL_CHARGES_PVT;