[Home] [Help]
PACKAGE BODY: APPS.OKL_MAINTAIN_FEE_PVT
Source
1 package body OKL_MAINTAIN_FEE_PVT as
2 /* $Header: OKLRFEEB.pls 120.56 2008/03/26 08:24:56 rpillay noship $ */
3
4 /*
5 -- vthiruva, 09/01/2004
6 -- Added Constants to enable Business Event
7 */
8 G_WF_EVT_FEE_REMOVED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.lease_contract.remove_fee';
9 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(30) := 'CONTRACT_ID';
10 G_WF_ITM_FEE_LINE_ID CONSTANT VARCHAR2(30) := 'FEE_LINE_ID';
11 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(30) := 'CONTRACT_PROCESS';
12
13 --Bug# 4899328
14 TYPE link_asset_rec_type IS RECORD (link_line_id NUMBER,
15 link_item_id NUMBER,
16 fin_asset_id NUMBER,
17 amount NUMBER,
18 asset_number VARCHAR2(15));
19
20 TYPE link_asset_tbl_type IS TABLE OF link_asset_rec_type INDEX BY BINARY_INTEGER;
21
22 --Murthy passthru changes begin
23 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
24 subtype pphv_rec_type is OKL_PARTY_PAYMENTS_PVT.pphv_rec_type;
25 subtype ppydv_rec_type is OKL_PYD_PVT.ppydv_rec_type;
26 subtype ppydv_tbl_type is OKL_PYD_PVT.ppydv_tbl_type;
27
28 CURSOR party_payment_csr(p_dnz_chr_id NUMBER, p_cle_id NUMBER, p_passthru_term VARCHAR2) IS
29 select id, passthru_term
30 from okl_party_payment_hdr
31 where
32 dnz_chr_id = p_dnz_chr_id and cle_id = p_cle_id and
33 passthru_term = p_passthru_term;
34 party_payment_rec party_payment_csr%ROWTYPE;
35
36 -- Modified by zrehman on 18-Jan-2008 Bug#6763287
37 -- Check whether contract or IA
38 CURSOR chk_inv_csr(p_dnz_chr_id NUMBER) IS
39 select 1
40 from okc_k_headers_all_b
41 where id = p_dnz_chr_id
42 and scs_code = 'INVESTOR';
43
44 PROCEDURE get_base_evg_recs(p_from IN passthru_dtl_rec_type,
45 x_base OUT NOCOPY ppydv_rec_type,
46 x_evg OUT NOCOPY ppydv_rec_type) IS
47 BEGIN
48 --base record population
49 x_base.object_version_number := OKL_API.G_MISS_NUM;
50 x_base.created_by := OKL_API.G_MISS_NUM;
51 x_base.creation_date := OKL_API.G_MISS_DATE;
52 x_base.last_updated_by := OKL_API.G_MISS_NUM;
53 x_base.last_update_date := OKL_API.G_MISS_DATE;
54 x_base.last_update_login := OKL_API.G_MISS_NUM;
55 x_base.id := p_from.b_payment_dtls_id;
56 x_base.cpl_id := p_from.b_cpl_id;
57 x_base.pay_site_id := p_from.b_pay_site_id;
58 x_base.pay_group_code := p_from.b_pay_group_code;
59 x_base.payment_hdr_id := p_from.b_payment_hdr_id;
60 x_base.payment_term_id := p_from.b_payment_term_id;
61 x_base.payment_method_code := p_from.b_payment_method_code;
62 x_base.payment_basis := p_from.b_payment_basis;
63 x_base.payment_start_date := p_from.b_payment_start_date;
64 x_base.payment_frequency := p_from.b_payment_frequency;
65 x_base.remit_days := p_from.b_remit_days;
66 x_base.disbursement_basis := p_from.b_disbursement_basis;
67 x_base.disbursement_fixed_amount := p_from.b_disbursement_fixed_amount;
68 x_base.disbursement_percent := p_from.b_disbursement_percent;
69 x_base.processing_fee_basis := p_from.b_processing_fee_basis;
70 x_base.processing_fee_fixed_amount := p_from.b_processing_fee_fixed_amount;
71 x_base.processing_fee_percent := p_from.b_processing_fee_percent;
72 --x_base.processing_fee_formula := p_from.b_processing_fee_formula;
73 -- evergreen record population
74 x_evg.object_version_number := OKL_API.G_MISS_NUM;
75 x_evg.created_by := OKL_API.G_MISS_NUM;
76 x_evg.creation_date := OKL_API.G_MISS_DATE;
77 x_evg.last_updated_by := OKL_API.G_MISS_NUM;
78 x_evg.last_update_date := OKL_API.G_MISS_DATE;
79 x_evg.last_update_login := OKL_API.G_MISS_NUM;
80 x_evg.id := p_from.e_payment_dtls_id;
81 x_evg.cpl_id := p_from.e_cpl_id;
82 x_evg.pay_site_id := p_from.e_pay_site_id;
83 x_evg.pay_group_code := p_from.e_pay_group_code;
84 x_evg.payment_hdr_id := p_from.e_payment_hdr_id;
85 x_evg.payment_term_id := p_from.e_payment_term_id;
86 x_evg.payment_method_code := p_from.e_payment_method_code;
87 x_evg.payment_basis := p_from.e_payment_basis;
88 x_evg.payment_start_date := p_from.e_payment_start_date;
89 x_evg.payment_frequency := p_from.e_payment_frequency;
90 x_evg.remit_days := p_from.e_remit_days;
91 x_evg.disbursement_basis := p_from.e_disbursement_basis;
92 x_evg.disbursement_fixed_amount := p_from.e_disbursement_fixed_amount;
93 x_evg.disbursement_percent := p_from.e_disbursement_percent;
94 x_evg.processing_fee_basis := p_from.e_processing_fee_basis;
95 x_evg.processing_fee_fixed_amount:= p_from.e_processing_fee_fixed_amount;
96 x_evg.processing_fee_percent := p_from.e_processing_fee_percent;
97 --x_evg.processing_fee_formula := p_from.e_processing_fee_formula;
98 END get_base_evg_recs;
99
100 PROCEDURE delete_passthru_party(
101 p_api_version IN NUMBER,
102 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_cpl_id IN NUMBER
107 ) IS
108
109 /* not required as cpl_id is passed
110 CURSOR cpl_csr (p_chr_id NUMBER,
111 p_cle_id NUMBER,
112 p_vendor_id NUMBER) IS
113 SELECT id
114 FROM okc_k_party_roles_v
115 WHERE dnz_chr_id = p_chr_id
116 AND cle_id = p_cle_id
117 AND object1_id1 = 6
118 AND rle_code = 'OKL_VENDOR';
119 */
120 CURSOR pmnt_dtl_csr (p_cpl_id NUMBER) IS
121 SELECT id
122 FROM okl_party_payment_dtls
123 WHERE cpl_id = p_cpl_id;
124
125 i NUMBER;
126 j NUMBER;
127 l_cplv_rec OKL_OKC_MIGRATION_PVT.CPLV_REC_TYPE;
128 l_ppydv_tbl ppydv_tbl_type;
129
130 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PASSTHRU_PARTY';
131 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
132
133 --Bug# 4558486
134 l_kplv_rec OKL_K_PARTY_ROLES_PVT.kplv_rec_type;
135 BEGIN
136 x_return_status := l_return_status;
137 -- Call start_activity to create savepoint, check compatibility
138 -- and initialize message list
139 l_return_status := OKC_API.START_ACTIVITY (l_api_name
140 ,p_init_msg_list
141 ,'_PVT'
142 ,x_return_status);
143 -- Check if activity started successfully
144 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
145 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
146 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
147 RAISE OKC_API.G_EXCEPTION_ERROR;
148 END IF;
149
150 IF (p_cpl_id IS NULL) THEN
151 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
152 END IF;
153
154 i := 0;
155 l_cplv_rec.id := p_cpl_id;
156
157 FOR pmnt_dtl_rec IN pmnt_dtl_csr (p_cpl_id)
158 LOOP
159 i := i + 1;
160 l_ppydv_tbl(i).id := pmnt_dtl_rec.id;
161 -- delete party payment details
162 END LOOP;
163
164 IF (l_ppydv_tbl.COUNT > 0) THEN
165 OKL_PARTY_PAYMENTS_PVT.delete_party_payment_dtls(
166 p_api_version => p_api_version,
167 p_init_msg_list => p_init_msg_list,
168 x_return_status => x_return_status,
169 x_msg_count => x_msg_count,
170 x_msg_data => x_msg_data,
171 p_ppydv_tbl => l_ppydv_tbl);
172
173 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
174 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
175 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
176 raise OKL_API.G_EXCEPTION_ERROR;
177 End If;
178 l_ppydv_tbl.DELETE;
179 END IF;
180
181 IF (l_cplv_rec.id IS NOT NULL) THEN
182 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
183 -- to delete records in tables
184 -- okc_k_party_roles_b and okl_k_party_roles
185 /*
186 okl_okc_migration_pvt.delete_k_party_role(
187 p_api_version => p_api_version,
188 p_init_msg_list => p_init_msg_list,
189 x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data,
192 p_cplv_rec => l_cplv_rec);
193 */
194
195 l_kplv_rec.id := l_cplv_rec.id;
196 okl_k_party_roles_pvt.delete_k_party_role(
197 p_api_version => p_api_version,
198 p_init_msg_list => p_init_msg_list,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data,
202 p_cplv_rec => l_cplv_rec,
203 p_kplv_rec => l_kplv_rec);
204
205 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
206 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
207 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
208 raise OKL_API.G_EXCEPTION_ERROR;
209 End If;
210 END IF;
211
212 OKC_API.END_ACTIVITY (x_msg_count
213 ,x_msg_data );
214
215 EXCEPTION
216 WHEN OKC_API.G_EXCEPTION_ERROR THEN
217 x_return_status := OKC_API.HANDLE_EXCEPTIONS
218 (l_api_name,
219 G_PKG_NAME,
220 'OKC_API.G_RET_STS_ERROR',
221 x_msg_count,
222 x_msg_data,
223 '_PVT');
224 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
225 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
226 (l_api_name,
227 G_PKG_NAME,
228 'OKC_API.G_RET_STS_UNEXP_ERROR',
229 x_msg_count,
230 x_msg_data,
231 '_PVT');
232 WHEN OTHERS THEN
233 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
234 (l_api_name,
235 G_PKG_NAME,
236 'OTHERS',
237 x_msg_count,
238 x_msg_data,
239 '_PVT');
240 END delete_passthru_party;
241
242
243 PROCEDURE create_payment_dtls(
244 p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER,
248 x_msg_data OUT NOCOPY VARCHAR2,
249 p_passthru_dtl_rec IN passthru_dtl_rec_type,
250 x_passthru_dtl_rec OUT NOCOPY passthru_dtl_rec_type) IS
251
252 l_api_name CONSTANT VARCHAR2(30) := 'create_payment_dtls';
253 l_api_version CONSTANT NUMBER := 1.0;
254 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
255 l_base_create boolean := TRUE;
256 l_evg_create boolean := TRUE;
257
258 l_passthru_dtl_rec passthru_dtl_rec_type := p_passthru_dtl_rec;
259 l_base ppydv_rec_type ;
260 l_evg ppydv_rec_type ;
261 x_ppydv_rec ppydv_rec_type;
262 l_exists VARCHAR2(1);
263
264 CURSOR party_object1_id1(p_cpl_id NUMBER) IS
265 select object1_id1
266 from okc_k_party_roles_b
267 where
268 id = p_cpl_id;
269 id1 NUMBER;
270
271 BEGIN
272
273 -- call START_ACTIVITY to create savepoint, check compatibility
274 -- and initialize message list
275 l_return_status := OKC_API.START_ACTIVITY(
276 p_api_name => l_api_name,
277 p_pkg_name => g_pkg_name,
278 p_init_msg_list => p_init_msg_list,
279 l_api_version => l_api_version,
280 p_api_version => p_api_version,
281 p_api_type => g_api_type,
282 x_return_status => x_return_status);
283
284 get_base_evg_recs(p_from => l_passthru_dtl_rec, x_base => l_base, x_evg => l_evg);
285
286 l_exists := 'N';
287 OPEN party_object1_id1(l_base.cpl_id);
288 FETCH party_object1_id1 INTO id1;
289 IF party_object1_id1%FOUND THEN
290 l_exists := 'Y';
291 END IF;
292 CLOSE party_object1_id1;
293
294 if(l_exists = 'N') Then
295 RAISE G_EXCEPTION_HALT_VALIDATION;
296 end if;
297
298 --Murthy check for rows found here
299
300 l_base.vendor_id := id1;
301 l_evg.vendor_id := id1;
302
303 --Base details created or updated only if Base header exists for the line.
304
305 l_base_create := l_base.pay_site_id IS NOT NULL
306 OR l_base.pay_group_code IS NOT NULL
307 OR l_base.payment_term_id IS NOT NULL
308 OR l_base.payment_basis IS NOT NULL
309 OR l_base.payment_start_date IS NOT NULL
310 OR l_base.payment_method_code IS NOT NULL
311 OR l_base.payment_frequency IS NOT NULL
312 OR l_base.remit_days IS NOT NULL
313 OR l_base.disbursement_basis IS NOT NULL
314 OR l_base.disbursement_fixed_amount IS NOT NULL
315 OR l_base.disbursement_percent IS NOT NULL
316 OR l_base.processing_fee_basis IS NOT NULL
317 OR l_base.processing_fee_fixed_amount IS NOT NULL
318 OR l_base.processing_fee_percent IS NOT NULL;
319
320
321 If( l_base_create AND l_base.payment_hdr_id IS NOT NULL AND (l_base.id IS NULL OR l_base.id = OKL_API.G_MISS_NUM) ) Then
322 okl_party_payments_pvt.create_party_payment_dtls(
323 p_api_version => p_api_version,
324 p_init_msg_list => p_init_msg_list,
325 x_return_status => l_return_status,
326 x_msg_count => x_msg_count,
327 x_msg_data => x_msg_data,
328 p_ppydv_rec => l_base,
329 x_ppydv_rec => x_ppydv_rec);
330 null;
331
332 -- check if activity started successfully
333 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
334 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
335 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
336 raise OKC_API.G_EXCEPTION_ERROR;
337 End If;
338 ElsIf ( l_base.payment_hdr_id IS NOT NULL AND l_base.id IS NOT NULL) Then
339 okl_party_payments_pvt.update_party_payment_dtls(
340 p_api_version => p_api_version,
341 p_init_msg_list => p_init_msg_list,
342 x_return_status => l_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data,
345 p_ppydv_rec => l_base,
346 x_ppydv_rec => x_ppydv_rec);
347 null;
348
349 -- check if activity started successfully
350 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
351 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
352 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
353 raise OKC_API.G_EXCEPTION_ERROR;
354 End If;
355 End If;
356
357
358 --Evergreen details created or updated only if Evergreen header exists for the line.
359
360 l_evg_create := l_evg.pay_site_id IS NOT NULL
361 OR l_evg.pay_group_code IS NOT NULL
362 OR l_evg.payment_term_id IS NOT NULL
363 OR l_evg.payment_basis IS NOT NULL
364 OR l_evg.payment_start_date IS NOT NULL
365 OR l_evg.payment_method_code IS NOT NULL
366 OR l_evg.payment_frequency IS NOT NULL
367 OR l_evg.remit_days IS NOT NULL
368 OR l_evg.disbursement_basis IS NOT NULL
369 OR l_evg.disbursement_fixed_amount IS NOT NULL
370 OR l_evg.disbursement_percent IS NOT NULL
371 OR l_evg.processing_fee_basis IS NOT NULL
372 OR l_evg.processing_fee_fixed_amount IS NOT NULL
373 OR l_evg.processing_fee_percent IS NOT NULL;
374
375
376 If( l_evg_create AND l_evg.payment_hdr_id IS NOT NULL AND l_evg.id IS NULL) Then
377 okl_party_payments_pvt.create_party_payment_dtls(
378 p_api_version => p_api_version,
379 p_init_msg_list => p_init_msg_list,
380 x_return_status => l_return_status,
381 x_msg_count => x_msg_count,
382 x_msg_data => x_msg_data,
383 p_ppydv_rec => l_evg,
384 x_ppydv_rec => x_ppydv_rec);
385 null;
386
387 -- check if activity started successfully
388 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
389 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
390 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
391 raise OKC_API.G_EXCEPTION_ERROR;
392 End If;
393 ElsIf ( l_evg.payment_hdr_id IS NOT NULL AND l_evg.id IS NOT NULL) Then
394 okl_party_payments_pvt.update_party_payment_dtls(
395 p_api_version => p_api_version,
396 p_init_msg_list => p_init_msg_list,
397 x_return_status => l_return_status,
398 x_msg_count => x_msg_count,
399 x_msg_data => x_msg_data,
400 p_ppydv_rec => l_evg,
401 x_ppydv_rec => x_ppydv_rec);
402
403 -- check if activity started successfully
404 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
405 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
406 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
407 raise OKC_API.G_EXCEPTION_ERROR;
408 End If;
409 End If;
410
411 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
412
413
414 EXCEPTION
415 when OKC_API.G_EXCEPTION_ERROR then
416 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
417 p_api_name => l_api_name,
418 p_pkg_name => g_pkg_name,
419 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
420 x_msg_count => x_msg_count,
421 x_msg_data => x_msg_data,
422 p_api_type => g_api_type);
423
424 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
425 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
426 p_api_name => l_api_name,
427 p_pkg_name => g_pkg_name,
428 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
429 x_msg_count => x_msg_count,
430 x_msg_data => x_msg_data,
431 p_api_type => g_api_type);
432
433 when OTHERS then
434 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
435 p_api_name => l_api_name,
436 p_pkg_name => g_pkg_name,
437 p_exc_name => 'OTHERS',
438 x_msg_count => x_msg_count,
439 x_msg_data => x_msg_data,
440 p_api_type => g_api_type);
441 END create_payment_dtls;
442
443
444 ----------------------------
445 PROCEDURE create_payment_hdrs(
446 p_api_version IN NUMBER,
447 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_msg_count OUT NOCOPY NUMBER,
450 x_msg_data OUT NOCOPY VARCHAR2,
451 p_passthru_rec IN passthru_rec_type,
452 x_passthru_rec OUT NOCOPY passthru_rec_type) IS
453
454
455 l_api_name CONSTANT VARCHAR2(30) := 'create_payment_hdrs';
456 l_api_version CONSTANT NUMBER := 1.0;
457 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
458
459 l_passthru_rec passthru_rec_type := p_passthru_rec;
460 l_pphv_rec pphv_rec_type ;
461 l_pphv_evg_rec pphv_rec_type ;
462 x_pphv_rec pphv_rec_type;
463 l_party_payment_rec party_payment_csr%ROWTYPE;
464
465 --Bug# 4884423
466 CURSOR l_old_passthru_csr(p_pph_id IN NUMBER) IS
467 SELECT payout_basis
468 FROM okl_party_payment_hdr
469 WHERE id = p_pph_id;
470
471 CURSOR l_cle_csr(p_cle_id IN NUMBER) IS
472 SELECT orig_system_id1
473 FROM okc_k_lines_b
474 WHERE id = p_cle_id;
475
476 l_cle_rec l_cle_csr%ROWTYPE;
477 l_chk_rebook_chr VARCHAR2(1);
478 l_base_payout_basis_upd VARCHAR2(1);
479 l_evgn_payout_basis_upd VARCHAR2(1);
480 --Bug# 4884423
481 BEGIN
482
483 -- call START_ACTIVITY to create savepoint, check compatibility
484 -- and initialize message list
485 l_return_status := OKC_API.START_ACTIVITY(
486 p_api_name => l_api_name,
487 p_pkg_name => g_pkg_name,
488 p_init_msg_list => p_init_msg_list,
489 l_api_version => l_api_version,
490 p_api_version => p_api_version,
491 p_api_type => g_api_type,
492 x_return_status => x_return_status);
493
494 -- check if activity started successfully
495 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
496 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
497 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
498 raise OKC_API.G_EXCEPTION_ERROR;
499 End If;
500
501 --Bug# 4884423
502 -- Update of Payout Basis is not allowed during Rebook
503 l_chk_rebook_chr := OKL_LLA_UTIL_PVT.check_rebook_contract(p_chr_id => l_passthru_rec.dnz_chr_id);
504 IF (NVL(l_chk_rebook_chr,OKL_API.G_MISS_CHAR) = OKL_API.G_TRUE) THEN
505
506 OPEN l_cle_csr(p_cle_id => l_passthru_rec.cle_id);
507 FETCH l_cle_csr INTO l_cle_rec;
508 CLOSE l_cle_csr;
509
510 -- Validation required for only existing fee lines, not
511 -- for newly added fee lines
512 IF (l_cle_rec.orig_system_id1 IS NOT NULL) THEN
513
514 l_base_payout_basis_upd := OKL_API.G_FALSE;
515 l_evgn_payout_basis_upd := OKL_API.G_FALSE;
516
517 IF ((l_passthru_rec.base_id IS NULL OR l_passthru_rec.base_id = OKL_API.G_MISS_NUM )
518 AND l_passthru_rec.payout_basis IS NOT NULL) THEN
519 l_base_payout_basis_upd := OKL_API.G_TRUE;
520
521 ELSIF NOT (l_passthru_rec.base_id IS NULL OR l_passthru_rec.base_id = OKL_API.G_MISS_NUM)
522 THEN
523 FOR l_old_passthru_rec IN l_old_passthru_csr(p_pph_id => l_passthru_rec.base_id) LOOP
524 IF (NVL(l_old_passthru_rec.payout_basis,OKL_API.G_MISS_CHAR) <>
525 NVL(l_passthru_rec.payout_basis,OKL_API.G_MISS_CHAR)) THEN
526
527 l_base_payout_basis_upd := OKL_API.G_TRUE;
528
529 END IF;
530 END LOOP;
531 END IF;
532
533 IF (l_base_payout_basis_upd = OKL_API.G_TRUE) THEN
534 OKL_API.set_message(p_app_name => G_APP_NAME,
535 p_msg_name => 'OKL_LA_RBK_BASE_PYT_BS_UPD');
536 RAISE OKL_API.G_EXCEPTION_ERROR;
537 END IF;
538
539 IF ((l_passthru_rec.evergreen_id IS NULL OR l_passthru_rec.evergreen_id = OKL_API.G_MISS_NUM )
540 AND l_passthru_rec.evergreen_payout_basis IS NOT NULL) THEN
541 l_evgn_payout_basis_upd := OKL_API.G_TRUE;
542
543 ELSIF NOT (l_passthru_rec.evergreen_id IS NULL OR l_passthru_rec.evergreen_id = OKL_API.G_MISS_NUM)
544 THEN
545 FOR l_old_passthru_rec IN l_old_passthru_csr(p_pph_id => l_passthru_rec.evergreen_id) LOOP
546 IF (NVL(l_old_passthru_rec.payout_basis,OKL_API.G_MISS_CHAR) <>
547 NVL(l_passthru_rec.evergreen_payout_basis,OKL_API.G_MISS_CHAR)) THEN
548
549 l_evgn_payout_basis_upd := OKL_API.G_TRUE;
550
551 END IF;
552 END LOOP;
553 END IF;
554
555 IF (l_evgn_payout_basis_upd = OKL_API.G_TRUE) THEN
556 OKL_API.set_message(p_app_name => G_APP_NAME,
557 p_msg_name => 'OKL_LA_RBK_EVGN_PYT_BS_UPD');
558 RAISE OKL_API.G_EXCEPTION_ERROR;
559 END IF;
560 END IF;
561 END IF;
562 --Bug# 4884423
563
564 ------------------------------------------------------------------
565 --Create base and evergreen if we are actually in Create mode
566 ------------------------------------------------------------------
567 --create payment header base term
568 If ( (l_passthru_rec.base_id IS NULL OR l_passthru_rec.base_id = OKL_API.G_MISS_NUM )
569 AND l_passthru_rec.payout_basis IS NOT NULL) Then
570
571 l_pphv_rec.dnz_chr_id := l_passthru_rec.dnz_chr_id;
572 l_pphv_rec.cle_id := l_passthru_rec.cle_id;
573 l_pphv_rec.passthru_start_date := l_passthru_rec.passthru_start_date;
574 l_pphv_rec.payout_basis := l_passthru_rec.payout_basis;
575 l_pphv_rec.passthru_term := 'BASE';
576 l_pphv_rec.passthru_stream_type_id := l_passthru_rec.base_stream_type_id;
577 --l_pphv_rec.passthru_stream_type_id := l_passthru_rec.passthru_stream_type_id;
578
579
580 okl_party_payments_pvt.create_party_payment_hdr(
581 p_api_version => p_api_version,
582 p_init_msg_list => p_init_msg_list,
583 x_return_status => x_return_status,
584 x_msg_count => x_msg_count,
585 x_msg_data => x_msg_data,
586 p_pphv_rec => l_pphv_rec,
587 x_pphv_rec => x_pphv_rec);
588
589 --setting the out record
590 x_passthru_rec.base_id := x_pphv_rec.id;
591 x_passthru_rec.base_stream_type_id := x_pphv_rec.passthru_stream_type_id;
592
593 -- check return status
594 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
595 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
596 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
597 raise OKC_API.G_EXCEPTION_ERROR;
598 End If;
599 End If;
600
601 --create payment header evergreen term
602 If ( (l_passthru_rec.evergreen_id IS NULL OR l_passthru_rec.evergreen_id = OKL_API.G_MISS_NUM )
603 AND l_passthru_rec.evergreen_payout_basis IS NOT NULL) Then
604 --AND l_passthru_rec.evergreen_eligible_yn = 'Y') Then
605 --create payment header evergreen term
606 l_pphv_evg_rec.dnz_chr_id := l_passthru_rec.dnz_chr_id;
607 l_pphv_evg_rec.cle_id := l_passthru_rec.cle_id;
608 l_pphv_evg_rec.passthru_start_date := l_passthru_rec.passthru_start_date;
609 l_pphv_evg_rec.payout_basis := l_passthru_rec.evergreen_payout_basis;
610 l_pphv_evg_rec.passthru_term := 'EVERGREEN';
611 l_pphv_evg_rec.payout_basis_formula := l_passthru_rec.evergreen_payout_basis_formula;
612 l_pphv_evg_rec.passthru_stream_type_id := l_passthru_rec.evg_stream_type_id;
613 --l_pphv_rec.passthru_stream_type_id := l_passthru_rec.passthru_stream_type_id;
614
615 okl_party_payments_pvt.create_party_payment_hdr(
616 p_api_version => p_api_version,
617 p_init_msg_list => p_init_msg_list,
618 x_return_status => x_return_status,
619 x_msg_count => x_msg_count,
620 x_msg_data => x_msg_data,
621 p_pphv_rec => l_pphv_evg_rec,
622 x_pphv_rec => x_pphv_rec);
623
624 --setting the out record
625 x_passthru_rec.evergreen_id := x_pphv_rec.id;
626 x_passthru_rec.evg_stream_type_id := x_pphv_rec.passthru_stream_type_id;
627
628 -- check return status
629 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
630 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
631 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
632 raise OKC_API.G_EXCEPTION_ERROR;
633 End If;
634 End If;
635
636
637 ------------------------------------------------------------------
638 --Update base and evergreen if we are actually in update mode
639 ------------------------------------------------------------------
640 --Update base
641 If NOT (l_passthru_rec.base_id IS NULL OR l_passthru_rec.base_id = OKL_API.G_MISS_NUM) Then
642 --l_pphv_rec.id := l_party_payment_rec.id;
643 l_pphv_rec.id := l_passthru_rec.base_id;
644 l_pphv_rec.passthru_term := 'BASE';
645 l_pphv_rec.dnz_chr_id := l_passthru_rec.dnz_chr_id;
646 l_pphv_rec.cle_id := l_passthru_rec.cle_id;
647 l_pphv_rec.passthru_start_date := l_passthru_rec.passthru_start_date;
648 l_pphv_rec.payout_basis := l_passthru_rec.payout_basis;
649 l_pphv_rec.passthru_stream_type_id := l_passthru_rec.base_stream_type_id;
650 --l_pphv_rec.passthru_stream_type_id := l_passthru_rec.passthru_stream_type_id;
651
652 okl_party_payments_pvt.update_party_payment_hdr(
653 p_api_version => p_api_version,
654 p_init_msg_list => p_init_msg_list,
655 x_return_status => x_return_status,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data,
658 p_pphv_rec => l_pphv_rec,
659 x_pphv_rec => x_pphv_rec);
660
661 --setting the out record
662 x_passthru_rec.base_id := x_pphv_rec.id;
663 x_passthru_rec.base_stream_type_id := x_pphv_rec.passthru_stream_type_id;
664
665 -- check return status
666 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
667 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
668 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
669 raise OKC_API.G_EXCEPTION_ERROR;
670 End If;
671 End If;
672 --Update evergreen
673 If NOT (l_passthru_rec.evergreen_id IS NULL OR l_passthru_rec.evergreen_id = OKL_API.G_MISS_NUM) Then
674 --l_pphv_rec.id := l_party_payment_rec.id;
675 l_pphv_rec.id := l_passthru_rec.evergreen_id;
676 l_pphv_rec.passthru_term := 'EVERGREEN';
677 l_pphv_rec.dnz_chr_id := l_passthru_rec.dnz_chr_id;
678 l_pphv_rec.cle_id := l_passthru_rec.cle_id;
679 l_pphv_rec.passthru_start_date := l_passthru_rec.passthru_start_date;
680 l_pphv_rec.payout_basis := l_passthru_rec.evergreen_payout_basis;
681 l_pphv_rec.payout_basis_formula := l_passthru_rec.evergreen_payout_basis_formula;
682 l_pphv_rec.passthru_stream_type_id := l_passthru_rec.evg_stream_type_id;
683 --l_pphv_rec.passthru_stream_type_id := l_passthru_rec.passthru_stream_type_id;
684
685
686 okl_party_payments_pvt.update_party_payment_hdr(
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_pphv_rec => l_pphv_rec,
693 x_pphv_rec => x_pphv_rec);
694
695 --setting the out record
696 x_passthru_rec.evergreen_id := x_pphv_rec.id;
697 x_passthru_rec.evg_stream_type_id := x_pphv_rec.passthru_stream_type_id;
698
699 -- check return status
700 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
701 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
702 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
703 raise OKC_API.G_EXCEPTION_ERROR;
704 End If;
705 End If;
706
707 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
708
709 EXCEPTION
710 when OKC_API.G_EXCEPTION_ERROR then
711 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
712 p_api_name => l_api_name,
713 p_pkg_name => g_pkg_name,
714 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
715 x_msg_count => x_msg_count,
716 x_msg_data => x_msg_data,
717 p_api_type => g_api_type);
718
719 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
720 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
721 p_api_name => l_api_name,
722 p_pkg_name => g_pkg_name,
723 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_api_type => g_api_type);
727
728 when OTHERS then
729 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
730 p_api_name => l_api_name,
731 p_pkg_name => g_pkg_name,
732 p_exc_name => 'OTHERS',
733 x_msg_count => x_msg_count,
734 x_msg_data => x_msg_data,
735 p_api_type => g_api_type);
736 END create_payment_hdrs;
737
738
739 PROCEDURE delete_payment_hdrs(
740 p_api_version IN NUMBER,
741 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
742 x_return_status OUT NOCOPY VARCHAR2,
743 x_msg_count OUT NOCOPY NUMBER,
744 x_msg_data OUT NOCOPY VARCHAR2,
745 p_passthru_rec IN passthru_rec_type) IS
746
747
748 l_api_name CONSTANT VARCHAR2(30) := 'delete_payment_hdrs';
749 l_api_version CONSTANT NUMBER := 1.0;
750 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
751
752 l_passthru_rec passthru_rec_type := p_passthru_rec;
753 l_pphv_rec pphv_rec_type := null;
754 l_party_payment_rec party_payment_csr%ROWTYPE;
755 l_row_found BOOLEAN := FALSE;
756 BEGIN
757 --delete payment header base term
758
759 OPEN party_payment_csr(l_passthru_rec.dnz_chr_id,l_passthru_rec.cle_id, 'BASE');
760 FETCH party_payment_csr INTO l_party_payment_rec;
761 l_row_found := party_payment_csr%FOUND;
762 CLOSE party_payment_csr;
763 l_pphv_rec.id := l_party_payment_rec.id;
764
765 If (l_row_found) Then
766 okl_party_payments_pvt.delete_party_payment_hdr(
767 p_api_version => p_api_version,
768 p_init_msg_list => p_init_msg_list,
769 x_return_status => x_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data,
772 p_pphv_rec => l_pphv_rec);
773
774 -- check return status
775 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
776 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
777 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
778 raise OKC_API.G_EXCEPTION_ERROR;
779 End If;
780 End If;
781
782 --delete payment header evergreen term
783
784 If (l_passthru_rec.evergreen_eligible_yn = 'Y') Then
785 OPEN party_payment_csr(l_passthru_rec.dnz_chr_id,l_passthru_rec.cle_id, 'EVERGREEN');
786 FETCH party_payment_csr INTO l_party_payment_rec;
787 l_row_found := party_payment_csr%FOUND;
788 CLOSE party_payment_csr;
789 l_pphv_rec.id := l_party_payment_rec.id;
790
791 --If (l_row_found) Then
792 okl_party_payments_pvt.delete_party_payment_hdr(
793 p_api_version => p_api_version,
794 p_init_msg_list => p_init_msg_list,
795 x_return_status => x_return_status,
796 x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data,
798 p_pphv_rec => l_pphv_rec);
799
800 -- check return status
801 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
802 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
803 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
804 raise OKC_API.G_EXCEPTION_ERROR;
805 End If;
806 --End If;
807 End If;
808
809 EXCEPTION
810 when OKC_API.G_EXCEPTION_ERROR then
811 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
812 p_api_name => l_api_name,
813 p_pkg_name => g_pkg_name,
814 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
815 x_msg_count => x_msg_count,
816 x_msg_data => x_msg_data,
817 p_api_type => g_api_type);
818
819 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
820 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
821 p_api_name => l_api_name,
822 p_pkg_name => g_pkg_name,
823 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
824 x_msg_count => x_msg_count,
825 x_msg_data => x_msg_data,
826 p_api_type => g_api_type);
827
828 when OTHERS then
829 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
830 p_api_name => l_api_name,
831 p_pkg_name => g_pkg_name,
832 p_exc_name => 'OTHERS',
833 x_msg_count => x_msg_count,
834 x_msg_data => x_msg_data,
835 p_api_type => g_api_type);
836 END delete_payment_hdrs;
837
838 --Murthy passthru changes end
839
840 /*
841 -- vthiruva, 09/01/2004
842 -- START, Added PROCEDURE to enable Business Event
843 */
844 -- Start of comments
845 --
846 -- Procedure Name : raise_business_event
847 -- Description : local_procedure, raises business event by making a call to
848 -- okl_wf_pvt.raise_event
849 -- Business Rules :
850 -- Parameters :
851 -- Version : 1.0
852 -- End of comments
853 --
854 PROCEDURE raise_business_event(
855 p_api_version IN NUMBER,
856 p_init_msg_list IN VARCHAR2,
857 x_return_status OUT NOCOPY VARCHAR2,
858 x_msg_count OUT NOCOPY NUMBER,
859 x_msg_data OUT NOCOPY VARCHAR2,
860 p_chr_id IN okc_k_headers_b.id%TYPE,
861 p_fee_line_id IN okc_k_lines_b.id%TYPE,
862 p_event_name IN wf_events.name%TYPE) IS
863
864 l_parameter_list wf_parameter_list_t;
865 l_contract_process VARCHAR2(30);
866 BEGIN
867 --create the parameter list to pass to raise_event
868 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_chr_id,l_parameter_list);
869 wf_event.AddParameterToList(G_WF_ITM_FEE_LINE_ID,p_fee_line_id,l_parameter_list);
870
871 -- wrapper API to get contract process. this API determines in which status the
872 -- contract in question is.
873 l_contract_process := okl_lla_util_pvt.get_contract_process(p_chr_id => p_chr_id);
874 -- add the contract status to the event parameter list
875 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS,l_contract_process,l_parameter_list);
876
877 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
878 p_init_msg_list => p_init_msg_list,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_event_name => p_event_name,
883 p_parameters => l_parameter_list);
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
888 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
889 END raise_business_event;
890
891 /*
892 -- vthiruva, 09/01/2004
893 -- END, PROCEDURE to enable Business Event
894 */
895
896
897 -- Start of comments
898 --
899 -- Procedure Name : create_contract_line
900 -- Description : creates contract line for shadowed contract
901 -- Business Rules :
902 -- Parameters :
903 -- Version : 1.0
904 -- End of comments
905
906
907 FUNCTION GET_AK_PROMPT(p_ak_region IN VARCHAR2, p_ak_attribute IN VARCHAR2)
908 RETURN VARCHAR2 IS
909
910 CURSOR ak_prompt_csr(p_ak_region VARCHAR2, p_ak_attribute VARCHAR2) IS
911 SELECT a.attribute_label_long
912 FROM ak_region_items ri, AK_REGIONS r, AK_ATTRIBUTES_vL a
913 WHERE ri.region_code = r.region_code
914 AND ri.attribute_code = a.attribute_code
915 AND ri.attribute_application_id = a.attribute_application_id
916 AND ri.region_application_id = r.region_application_id
917 AND ri.attribute_code = p_ak_attribute
918 AND ri.region_code = p_ak_region;
919
920 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
921 BEGIN
922 OPEN ak_prompt_csr(p_ak_region, p_ak_attribute);
923 FETCH ak_prompt_csr INTO l_ak_prompt;
924 CLOSE ak_prompt_csr;
925 return(l_ak_prompt);
926 END;
927
928 -- Start of comments
929 --
930 -- Procedure Name : validate_fee_type
931 -- Description :
932 -- Business Rules :
933 -- Parameters :
934 -- Version : 1.0
935 -- End of comments
936 PROCEDURE validate_fee_type(
937 p_api_version IN NUMBER,
938 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
939 x_return_status OUT NOCOPY VARCHAR2,
940 x_msg_count OUT NOCOPY NUMBER,
941 x_msg_data OUT NOCOPY VARCHAR2,
942 p_fee_types_rec IN fee_types_rec_type,
943 x_fee_types_rec OUT NOCOPY fee_types_rec_type
944 ) IS
945
946 l_api_name CONSTANT VARCHAR2(30) := 'validate_fee_type';
947 l_api_version CONSTANT NUMBER := 1.0;
948 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
949
950 l_supp_id okx_vendors_v.id1%type := null;
951 l_party_name okx_vendors_v.name%type := null;
952 l_item_id NUMBER:= null;
953 l_item_name OKL_STRMTYP_SOURCE_V.name%type := null;
954 l_start_date okc_k_headers_b.start_date%type := null;
955 l_pdt_id okl_k_headers.pdt_id%type := null;
956 l_qte_id okl_k_lines.qte_id%type := null;
957 l_chk_qte_id okl_k_lines.qte_id%type := null;
958 l_roll_qt OKL_TRX_QUOTES_B.QUOTE_NUMBER%type := null;
959 l_khr_id okl_k_headers.khr_id%type := null;
960
961 CURSOR l_supp_name_csr IS
962 select id1
963 from okx_vendors_v
964 where name = p_fee_types_rec.party_name;
965
966 CURSOR l_supp_id1_csr IS
967 select name
968 from okx_vendors_v
969 where id1 = p_fee_types_rec.party_id1;
970
971 CURSOR l_ft_general_item_id_csr IS
972 select OKL_STRMTYP.id1
973 from OKL_STRMTYP_SOURCE_V OKL_STRMTYP
974 where OKL_STRMTYP.name = p_fee_types_rec.item_name
975 and OKL_STRMTYP.STATUS = 'A';
976
977 Cursor l_ft_capitalized_item_id_csr(p_pdt_id okl_k_headers.pdt_id%type, p_start_date date) IS
978 SELECT sty_id
979 FROM okl_strm_tmpt_full_uv
980 WHERE nvl(CAPITALIZE_YN,'N') = 'Y'
981 AND STY_PURPOSE = 'EXPENSE'
982 AND pdt_id = p_pdt_id
983 AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
984 AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
985 AND sty_name = p_fee_types_rec.item_name;
986
987 Cursor l_ft_income_item_id_csr(p_pdt_id okl_k_headers.pdt_id%type, p_start_date date) IS
988 SELECT sty_id
989 FROM okl_strm_tmpt_full_uv
990 WHERE STY_PURPOSE = 'FEE_PAYMENT'
991 AND pdt_id = p_pdt_id
992 AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
993 AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
994 AND sty_name = p_fee_types_rec.item_name;
995
996 Cursor l_ft_passthrough_item_id_csr(p_pdt_id okl_k_headers.pdt_id%type, p_start_date date) IS
997 SELECT sty_id
998 FROM okl_strm_tmpt_full_uv
999 WHERE STY_PURPOSE = 'PASS_THROUGH_FEE'
1000 AND pdt_id = p_pdt_id
1001 AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
1002 AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
1003 AND sty_name = p_fee_types_rec.item_name;
1004
1005 Cursor l_ft_secdeposit_item_id_csr(p_pdt_id okl_k_headers.pdt_id%type, p_start_date date) IS
1006 SELECT sty_id
1007 FROM okl_strm_tmpt_full_uv
1008 WHERE STY_PURPOSE = 'SECURITY_DEPOSIT'
1009 AND pdt_id = p_pdt_id
1010 AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
1011 AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
1012 AND sty_name = p_fee_types_rec.item_name;
1013
1014 Cursor l_ft_others_item_id_csr(p_pdt_id okl_k_headers.pdt_id%type, p_start_date date) IS
1015 SELECT sty_id
1016 FROM okl_strm_tmpt_full_uv
1017 WHERE STY_PURPOSE = 'EXPENSE'
1018 AND pdt_id = p_pdt_id
1019 AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
1020 AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
1021 AND sty_name = p_fee_types_rec.item_name;
1022
1023 CURSOR l_item_name_csr IS
1024 select name
1025 from OKL_STRMTYP_SOURCE_V OKL_STRMTYP
1026 where OKL_STRMTYP.id1 = p_fee_types_rec.item_id1
1027 and OKL_STRMTYP.STATUS = 'A';
1028
1029 cursor l_start_date_csr IS
1030 select chr.start_date, khr.pdt_id
1031 from okc_k_headers_b chr, okl_k_headers khr
1032 where chr.id = p_fee_types_rec.dnz_chr_id
1033 and chr.id = khr.id;
1034
1035 CURSOR l_roll_qt_csr(p_start_date okc_k_lines_b.start_date%type) IS
1036 SELECT rqt.qte_id
1037 FROM OKL_LA_ROLLOVER_FEE_UV rqt,
1038 OKC_K_HEADERS_B chr
1039 WHERE CHR.CUST_ACCT_ID = rqt.cust_acct_id
1040 AND rqt.rollover_quote = p_fee_types_rec.roll_qt
1041 AND trunc(p_start_date) between nvl(trunc(date_effective_from),trunc(p_start_date))
1042 and nvl(trunc(date_effective_to),trunc(p_start_date+1))
1043 AND chr.id = p_fee_types_rec.dnz_chr_id;
1044
1045 CURSOR l_qte_id_csr(p_start_date okc_k_lines_b.start_date%type) IS
1046 SELECT rqt.rollover_quote
1047 FROM OKL_LA_ROLLOVER_FEE_UV rqt,
1048 OKC_K_HEADERS_B chr,
1049 okl_k_headers khr
1050 WHERE chr.id = khr.id
1051 and chr.currency_code = rqt.currency_code
1052 and CHR.CUST_ACCT_ID = rqt.cust_acct_id
1053 AND rqt.qte_id = p_fee_types_rec.qte_id
1054 AND trunc(p_start_date) between nvl(trunc(date_effective_from),trunc(p_start_date))
1055 and nvl(trunc(date_effective_to),trunc(p_start_date+1))
1056 AND chr.id = p_fee_types_rec.dnz_chr_id;
1057
1058 CURSOR l_qte_id_prog_csr(p_start_date okc_k_lines_b.start_date%type) IS
1059 SELECT rqt.rollover_quote
1060 FROM OKL_LA_ROLLOVER_FEE_UV rqt,
1061 OKC_K_HEADERS_B chr,
1062 okl_k_headers khr
1063 WHERE chr.id = khr.id
1064 and chr.currency_code = rqt.currency_code
1065 and CHR.CUST_ACCT_ID = rqt.cust_acct_id
1066 AND rqt.qte_id = p_fee_types_rec.qte_id
1067 AND trunc(p_start_date) between nvl(trunc(date_effective_from),trunc(p_start_date))
1068 and nvl(trunc(date_effective_to),trunc(p_start_date+1))
1069 and khr.khr_id = rqt.khr_id
1070 and chr.id = p_fee_types_rec.dnz_chr_id;
1071
1072 CURSOR l_khr_id_csr IS
1073 select khr_id
1074 from okl_k_headers
1075 where id = p_fee_types_rec.dnz_chr_id;
1076
1077 CURSOR l_chk_roll_qt_upd_csr(p_qte_id okl_k_lines.qte_id%type) IS
1078 select 1
1079 from okl_k_lines kle,
1080 okc_k_lines_b cle
1081 where cle.id = kle.id
1082 and kle.qte_id = p_qte_id
1083 and cle.dnz_chr_id = p_fee_types_rec.dnz_chr_id
1084 and cle.id <> p_fee_types_rec.line_id;
1085
1086 CURSOR l_chk_roll_qt_crt_csr(p_qte_id okl_k_lines.qte_id%type) IS
1087 select 1
1088 from okl_k_lines kle,
1089 okc_k_lines_b cle
1090 where cle.id = kle.id
1091 and kle.qte_id = p_qte_id
1092 and cle.dnz_chr_id = p_fee_types_rec.dnz_chr_id;
1093
1094 CURSOR l_fee_purpose_csr(p_fee_purpose_code okl_k_lines.FEE_PURPOSE_CODE%type) IS
1095 select lookup_code
1096 from fnd_lookups fnd
1097 where fnd.lookup_type = G_OKL_FEE_PURPOSE_LOOKUP_TYPE
1098 and fnd.lookup_code = p_fee_purpose_code;
1099 l_fee_purpose_code okl_k_lines.fee_purpose_code%type := null;
1100
1101 -- Modified by zrehman for Bug#6763287 on 17-Jan-2008 start
1102 CURSOR l_inv_id_csr IS
1103 select hz.party_name
1104 from
1105 okc_k_lines_b cle
1106 , okc_k_headers_all_b chr
1107 , hz_parties hz
1108 , hz_cust_accounts hca
1109 where
1110 cle.chr_id = chr.id
1111 and chr.scs_code = 'INVESTOR'
1112 -- and chr.sts_code='NEW'
1113 and hca.cust_account_id = cle.cust_acct_id
1114 and hz.party_id = hca.party_id
1115 and hz.party_id = p_fee_types_rec.party_id1
1116 and chr.id = p_fee_types_rec.dnz_chr_id;
1117
1118 CURSOR l_inv_name_csr IS
1119 select hz.party_id
1120 from
1121 okc_k_lines_b cle
1122 , okc_k_headers_all_b chr
1123 , hz_parties hz
1124 , hz_cust_accounts hca
1125 where
1126 cle.chr_id = chr.id
1127 and chr.scs_code = 'INVESTOR'
1128 -- and chr.sts_code='NEW'
1129 and hca.cust_account_id = cle.cust_acct_id
1130 and hz.party_id = hca.party_id
1131 and hz.party_name = p_fee_types_rec.party_name
1132 and chr.id = p_fee_types_rec.dnz_chr_id;
1133
1134 l_investor_name HZ_PARTIES.PARTY_NAME%TYPE;
1135 l_investor_id HZ_PARTIES.PARTY_ID%TYPE;
1136 l_is_ivestor NUMBER;
1137 -- Modified by zrehman for Bug#6763287 on 17-Jan-2008 end
1138 BEGIN
1139
1140 -- call START_ACTIVITY to create savepoint, check compatibility
1141 -- and initialize message list
1142 x_return_status := OKC_API.START_ACTIVITY(
1143 p_api_name => l_api_name,
1144 p_pkg_name => g_pkg_name,
1145 p_init_msg_list => p_init_msg_list,
1146 l_api_version => l_api_version,
1147 p_api_version => p_api_version,
1148 p_api_type => g_api_type,
1149 x_return_status => x_return_status);
1150
1151 -- check if activity started successfully
1152 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1153 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1154 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1155 raise OKC_API.G_EXCEPTION_ERROR;
1156 End If;
1157
1158 x_fee_types_rec := p_fee_types_rec;
1159
1160 If ( p_fee_types_rec.dnz_chr_id is null or p_fee_types_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) Then
1161 x_return_status := OKC_API.g_ret_sts_error;
1162 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_FEE_TYPE');
1163 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1164 , p_msg_name => 'OKL_REQUIRED_VALUE'
1165 , p_token1 => 'COL_NAME'
1166 , p_token1_value => 'dnz_chr_id'
1167 );
1168 raise OKC_API.G_EXCEPTION_ERROR;
1169 End If;
1170
1171 If ( p_fee_types_rec.fee_type is null or p_fee_types_rec.fee_type = OKC_API.G_MISS_CHAR ) Then
1172 x_return_status := OKC_API.g_ret_sts_error;
1173 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_FEE_TYPE');
1174 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1175 , p_msg_name => 'OKL_REQUIRED_VALUE'
1176 , p_token1 => 'COL_NAME'
1177 , p_token1_value => l_ak_prompt
1178 );
1179 raise OKC_API.G_EXCEPTION_ERROR;
1180 End If;
1181
1182 -- Modified by zrehman for Bug#6763287 on 17-Jan-2008 start
1183 OPEN chk_inv_csr(p_fee_types_rec.dnz_chr_id);
1184 FETCH chk_inv_csr INTO l_is_ivestor;
1185 CLOSE chk_inv_csr;
1186
1187 open l_start_date_csr;
1188 fetch l_start_date_csr into l_start_date, l_pdt_id;
1189 close l_start_date_csr;
1190
1191 --Bug# 6917539: Investor agreement validation causes regression issues
1192 -- for contracts as NULL value is not handled
1193 IF NVL(l_is_ivestor,0) <> 1 THEN
1194 -- start validation for Contracts
1195 -- Modified by zrehman on 17-Jan-2008 end
1196
1197 If (
1198 p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
1199 p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1200 p_fee_types_rec.fee_type = G_FT_EXPENSE or
1201 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS or
1202 p_fee_types_rec.fee_type = G_FT_FINANCED or
1203 p_fee_types_rec.fee_type = G_FT_INCOME or
1204 p_fee_types_rec.fee_type = G_FT_ABSORBED or
1205 p_fee_types_rec.fee_type = G_FT_SECDEPOSIT or
1206 p_fee_types_rec.fee_type = G_FT_ROLLOVER or
1207 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1208 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1209 )
1210 ) Then
1211
1212 If ( NOT (p_fee_types_rec.item_id1 is null or p_fee_types_rec.item_id1 = OKC_API.G_MISS_CHAR )) Then
1213
1214 l_item_name := null;
1215 open l_item_name_csr;
1216 fetch l_item_name_csr into l_item_name;
1217 close l_item_name_csr;
1218
1219 If(l_item_name is null) Then
1220 x_return_status := OKC_API.g_ret_sts_error;
1221 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE');
1222 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1223 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1224 , p_token1 => 'COL_NAME'
1225 , p_token1_value => l_ak_prompt
1226 );
1227 raise OKC_API.G_EXCEPTION_ERROR;
1228 End If;
1229
1230 x_fee_types_rec.item_name := l_item_name;
1231 x_fee_types_rec.item_id1 := p_fee_types_rec.item_id1;
1232
1233 ElsIf (NOT( p_fee_types_rec.item_name is null or p_fee_types_rec.item_name = OKC_API.G_MISS_CHAR )) Then
1234
1235 /*open l_start_date_csr;
1236 fetch l_start_date_csr into l_start_date, l_pdt_id;
1237 close l_start_date_csr;*/
1238
1239 If(p_fee_types_rec.fee_type = G_FT_CAPITALIZED) Then
1240
1241 l_item_id := null;
1242 open l_ft_capitalized_item_id_csr(l_pdt_id, l_start_date);
1243 fetch l_ft_capitalized_item_id_csr into l_item_id;
1244 close l_ft_capitalized_item_id_csr;
1245
1246 ElsIf(p_fee_types_rec.fee_type = G_FT_INCOME) Then
1247
1248 l_item_id := null;
1249 open l_ft_income_item_id_csr(l_pdt_id, l_start_date);
1250 fetch l_ft_income_item_id_csr into l_item_id;
1251 close l_ft_income_item_id_csr;
1252
1253 ElsIf(p_fee_types_rec.fee_type = G_FT_PASSTHROUGH) Then
1254
1255 l_item_id := null;
1256 open l_ft_passthrough_item_id_csr(l_pdt_id, l_start_date);
1257 fetch l_ft_passthrough_item_id_csr into l_item_id;
1258 close l_ft_passthrough_item_id_csr;
1259
1260 ElsIf(p_fee_types_rec.fee_type = G_FT_SECDEPOSIT) Then
1261
1262 l_item_id := null;
1263 open l_ft_secdeposit_item_id_csr( l_pdt_id, l_start_date);
1264 fetch l_ft_secdeposit_item_id_csr into l_item_id;
1265 close l_ft_secdeposit_item_id_csr;
1266
1267 ElsIf(p_fee_types_rec.fee_type = G_FT_GENERAL) Then
1268
1269 l_item_id := null;
1270 open l_ft_general_item_id_csr;
1271 fetch l_ft_general_item_id_csr into l_item_id;
1272 close l_ft_general_item_id_csr;
1273
1274 Else
1275
1276 l_item_id := null;
1277 open l_ft_others_item_id_csr(l_pdt_id, l_start_date);
1278 fetch l_ft_others_item_id_csr into l_item_id;
1279 close l_ft_others_item_id_csr;
1280
1281 End IF;
1282
1283 If(l_item_id is null) Then
1284 x_return_status := OKC_API.g_ret_sts_error;
1285 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE');
1286 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1287 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1288 , p_token1 => 'COL_NAME'
1289 , p_token1_value => l_ak_prompt
1290 );
1291 raise OKC_API.G_EXCEPTION_ERROR;
1292 End If;
1293
1294 x_fee_types_rec.item_id1 := l_item_id;
1295
1296 Else
1297
1298 x_return_status := OKC_API.g_ret_sts_error;
1299 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE');
1300 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1301 , p_msg_name => 'OKL_REQUIRED_VALUE'
1302 , p_token1 => 'COL_NAME'
1303 , p_token1_value => l_ak_prompt
1304 );
1305 raise OKC_API.G_EXCEPTION_ERROR;
1306
1307 End If;
1308
1309 Else
1310
1311 x_return_status := OKC_API.g_ret_sts_error;
1312 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1313 , p_msg_name => 'OKL_INVALID_FEE_TYPE'
1314 , p_token1 => 'COL_NAME'
1315 , p_token1_value => p_fee_types_rec.fee_type
1316 );
1317 raise OKC_API.G_EXCEPTION_ERROR;
1318
1319 End If;
1320
1321 If (
1322 --Murthy
1323 --p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
1324 p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1325 p_fee_types_rec.fee_type = G_FT_EXPENSE or
1326 p_fee_types_rec.fee_type = G_FT_FINANCED or
1327 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS or
1328 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1329 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1330 )
1331 ) Then
1332
1333 If ( NOT (p_fee_types_rec.party_id1 is null or p_fee_types_rec.party_id1 = OKC_API.G_MISS_CHAR )) Then
1334
1335 l_party_name := null;
1336 open l_supp_id1_csr;
1337 fetch l_supp_id1_csr into l_party_name;
1338 close l_supp_id1_csr;
1339
1340 If(l_party_name is null) Then
1341 x_return_status := OKC_API.g_ret_sts_error;
1342 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
1343 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1344 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1345 , p_token1 => 'COL_NAME'
1346 , p_token1_value => l_ak_prompt
1347 );
1348 raise OKC_API.G_EXCEPTION_ERROR;
1349 End If;
1350
1351 x_fee_types_rec.party_name := l_party_name;
1352 x_fee_types_rec.party_id1 := p_fee_types_rec.party_id1;
1353
1354
1355 ElsIf (NOT( p_fee_types_rec.party_name is null or p_fee_types_rec.party_name = OKC_API.G_MISS_CHAR )) Then
1356
1357 l_supp_id := null;
1358 open l_supp_name_csr;
1359 fetch l_supp_name_csr into l_supp_id;
1360 close l_supp_name_csr;
1361
1362 If(l_supp_id is null) Then
1363 x_return_status := OKC_API.g_ret_sts_error;
1364 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
1365 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1366 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1367 , p_token1 => 'COL_NAME'
1368 , p_token1_value => l_ak_prompt
1369 );
1370 raise OKC_API.G_EXCEPTION_ERROR;
1371 End If;
1372
1373 x_fee_types_rec.party_id1 := l_supp_id;
1374
1375 Else
1376
1377 If (NOT(p_fee_types_rec.fee_type = G_FT_CAPITALIZED or p_fee_types_rec.fee_type = G_FT_FINANCED)) Then
1378
1379 x_return_status := OKC_API.g_ret_sts_error;
1380 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
1381 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1382 , p_msg_name => 'OKL_REQUIRED_VALUE'
1383 , p_token1 => 'COL_NAME'
1384 , p_token1_value => l_ak_prompt
1385 );
1386 raise OKC_API.G_EXCEPTION_ERROR;
1387
1388 End If;
1389
1390 End If;
1391
1392 ElsIf (p_fee_types_rec.party_name is not null and p_fee_types_rec.fee_type = G_FT_SECDEPOSIT) Then
1393
1394 x_return_status := OKC_API.g_ret_sts_error;
1395 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1396 , p_msg_name => 'OKL_INVLD_FT_PRTY'
1397 );
1398 raise OKC_API.G_EXCEPTION_ERROR;
1399
1400 End If;
1401
1402 If (
1403 p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
1404 p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1405 p_fee_types_rec.fee_type = G_FT_EXPENSE or
1406 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS or
1407 p_fee_types_rec.fee_type = G_FT_FINANCED or
1408 p_fee_types_rec.fee_type = G_FT_ROLLOVER or
1409 p_fee_types_rec.fee_type = G_FT_INCOME or
1410 p_fee_types_rec.fee_type = G_FT_ABSORBED or
1411 p_fee_types_rec.fee_type = G_FT_SECDEPOSIT or
1412 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1413 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1414 )
1415 ) Then
1416 If ( p_fee_types_rec.effective_from is null or p_fee_types_rec.effective_from = OKC_API.G_MISS_DATE ) Then
1417 x_return_status := OKC_API.g_ret_sts_error;
1418 l_ak_prompt := GET_AK_PROMPT('OKL_LA_MLA_DTAIL', 'OKL_START_DATE');
1419 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1420 , p_msg_name => 'OKL_REQUIRED_VALUE'
1421 , p_token1 => 'COL_NAME'
1422 , p_token1_value => l_ak_prompt
1423 );
1424 raise OKC_API.G_EXCEPTION_ERROR;
1425 End If;
1426
1427 End If;
1428
1429 If (
1430 p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
1431 p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1432 p_fee_types_rec.fee_type = G_FT_EXPENSE or
1433 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS or
1434 p_fee_types_rec.fee_type = G_FT_FINANCED or
1435 p_fee_types_rec.fee_type = G_FT_ROLLOVER or
1436 p_fee_types_rec.fee_type = G_FT_INCOME or
1437 p_fee_types_rec.fee_type = G_FT_ABSORBED or
1438 p_fee_types_rec.fee_type = G_FT_SECDEPOSIT or
1439 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1440 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1441 )
1442 ) Then
1443
1444 If ( p_fee_types_rec.effective_to is null or p_fee_types_rec.effective_to = OKC_API.G_MISS_DATE ) Then
1445 x_return_status := OKC_API.g_ret_sts_error;
1446 l_ak_prompt := GET_AK_PROMPT('OKL_LA_MLA_DTAIL', 'OKL_END_DATE');
1447 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1448 , p_msg_name => 'OKL_REQUIRED_VALUE'
1449 , p_token1 => 'COL_NAME'
1450 , p_token1_value => l_ak_prompt
1451 );
1452 raise OKC_API.G_EXCEPTION_ERROR;
1453 End If;
1454
1455 End If;
1456
1457
1458 If ( p_fee_types_rec.fee_type = G_FT_ROLLOVER) Then
1459
1460 If ( NOT (p_fee_types_rec.qte_id is null or p_fee_types_rec.qte_id = OKC_API.G_MISS_NUM )) Then
1461
1462 l_khr_id := null;
1463 open l_khr_id_csr;
1464 fetch l_khr_id_csr into l_khr_id;
1465 close l_khr_id_csr;
1466
1467 If( l_khr_id is null) Then
1468
1469 l_roll_qt := null;
1470 open l_qte_id_csr(p_fee_types_rec.effective_from);
1471 fetch l_qte_id_csr into l_roll_qt;
1472 close l_qte_id_csr;
1473
1474 Else -- program agreement attached to the contract
1475
1476 l_roll_qt := null;
1477 open l_qte_id_prog_csr(p_fee_types_rec.effective_from);
1478 fetch l_qte_id_prog_csr into l_roll_qt;
1479 close l_qte_id_prog_csr;
1480
1481 End If;
1482
1483 If(l_roll_qt is null) Then
1484
1485 x_return_status := OKC_API.g_ret_sts_error;
1486 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ROLL_QT');
1487 If(l_ak_prompt is null) Then
1488 l_ak_prompt := 'QTE_ID';
1489 End if;
1490
1491 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1492 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1493 , p_token1 => 'COL_NAME'
1494 , p_token1_value => l_ak_prompt
1495 );
1496 raise OKC_API.G_EXCEPTION_ERROR;
1497
1498 End If;
1499
1500 x_fee_types_rec.qte_id := p_fee_types_rec.qte_id;
1501 l_qte_id := p_fee_types_rec.qte_id;
1502
1503 ElsIf(NOT ( p_fee_types_rec.roll_qt is null or p_fee_types_rec.roll_qt = OKC_API.G_MISS_NUM )) Then
1504
1505 l_qte_id := null;
1506
1507
1508 open l_roll_qt_csr(p_fee_types_rec.effective_from);
1509 fetch l_roll_qt_csr into l_qte_id;
1510 close l_roll_qt_csr;
1511
1512 If(l_qte_id is null) Then
1513 x_return_status := OKC_API.g_ret_sts_error;
1514 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ROLL_QT');
1515 If(l_ak_prompt is null) then
1516 l_ak_prompt := 'Rollover Quote';
1517 End If;
1518 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1519 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1520 , p_token1 => 'COL_NAME'
1521 , p_token1_value => l_ak_prompt
1522 );
1523 raise OKC_API.G_EXCEPTION_ERROR;
1524 End If;
1525
1526 x_fee_types_rec.qte_id := l_qte_id;
1527
1528
1529 Else
1530
1531 x_return_status := OKC_API.g_ret_sts_error;
1532 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ROLL_QT');
1533 If(l_ak_prompt is null) then
1534 l_ak_prompt := 'Rollover Quote';
1535 End If;
1536
1537 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1538 , p_msg_name => 'OKL_REQUIRED_VALUE'
1539 , p_token1 => 'COL_NAME'
1540 , p_token1_value => l_ak_prompt
1541 );
1542 raise OKC_API.G_EXCEPTION_ERROR;
1543
1544 End If;
1545
1546
1547 If(NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) Then
1548
1549 -- check if rollover quote already associated to a line
1550 l_chk_qte_id := null;
1551 open l_chk_roll_qt_upd_csr(l_qte_id);
1552 fetch l_chk_roll_qt_upd_csr into l_chk_qte_id;
1553 close l_chk_roll_qt_upd_csr;
1554
1555 If(l_chk_qte_id is not null ) Then
1556
1557 x_return_status := OKC_API.g_ret_sts_error;
1558 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1559 , p_msg_name => 'OKL_QA_DUP_TERM_QUOTE'
1560 );
1561 raise OKC_API.G_EXCEPTION_ERROR;
1562 End If;
1563
1564 Else
1565
1566 l_chk_qte_id := null;
1567 open l_chk_roll_qt_crt_csr(l_qte_id);
1568 fetch l_chk_roll_qt_crt_csr into l_chk_qte_id;
1569 close l_chk_roll_qt_crt_csr;
1570
1571 If(l_chk_qte_id is not null ) Then
1572
1573 x_return_status := OKC_API.g_ret_sts_error;
1574 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1575 , p_msg_name => 'OKL_QA_DUP_TERM_QUOTE'
1576 );
1577 raise OKC_API.G_EXCEPTION_ERROR;
1578 End If;
1579
1580 End If;
1581
1582 End If;
1583
1584 If (
1585 p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
1586 p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1587 p_fee_types_rec.fee_type = G_FT_EXPENSE or
1588 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS or
1589 p_fee_types_rec.fee_type = G_FT_FINANCED or
1590 p_fee_types_rec.fee_type = G_FT_ROLLOVER or
1591 p_fee_types_rec.fee_type = G_FT_INCOME or
1592 p_fee_types_rec.fee_type = G_FT_ABSORBED or
1593 p_fee_types_rec.fee_type = G_FT_SECDEPOSIT or
1594 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1595 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1596 )
1597 ) Then
1598
1599 If ( p_fee_types_rec.amount is null or p_fee_types_rec.amount = OKC_API.G_MISS_NUM ) Then
1600 x_return_status := OKC_API.g_ret_sts_error;
1601 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
1602 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1603 , p_msg_name => 'OKL_REQUIRED_VALUE'
1604 , p_token1 => 'COL_NAME'
1605 , p_token1_value => l_ak_prompt
1606 );
1607 raise OKC_API.G_EXCEPTION_ERROR;
1608 End If;
1609
1610 End If;
1611
1612
1613 If (
1614 p_fee_types_rec.fee_type = G_FT_EXPENSE or
1615 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS or
1616 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1617 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1618 )
1619 ) Then
1620
1621 If( p_fee_types_rec.initial_direct_cost is not null and p_fee_types_rec.initial_direct_cost > p_fee_types_rec.amount) Then
1622
1623 x_return_status := OKC_API.g_ret_sts_error;
1624 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1625 , p_msg_name => 'OKL_FEE_IDC_AMT'
1626 );
1627 raise OKC_API.G_EXCEPTION_ERROR;
1628
1629 End If;
1630
1631 ElsIf (
1632 (( NOT ( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM)) and
1633 (p_fee_types_rec.fee_type = G_FT_GENERAL)
1634 )
1635 ) Then
1636
1637 If( NOT (p_fee_types_rec.initial_direct_cost is not null and p_fee_types_rec.initial_direct_cost = p_fee_types_rec.amount)) Then
1638
1639 x_return_status := OKC_API.g_ret_sts_error;
1640 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1641 , p_msg_name => 'OKL_FEE_IDC_AMT_ABSORBED'
1642 );
1643 raise OKC_API.G_EXCEPTION_ERROR;
1644
1645 End If;
1646
1647 Else
1648
1649 If (p_fee_types_rec.initial_direct_cost is not null and
1650 (
1651 p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
1652 p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1653 p_fee_types_rec.fee_type = G_FT_FINANCED or
1654 p_fee_types_rec.fee_type = G_FT_INCOME or
1655 p_fee_types_rec.fee_type = G_FT_SECDEPOSIT
1656 )
1657 ) Then
1658 x_return_status := OKC_API.g_ret_sts_error;
1659 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE_IDC');
1660 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1661 , p_msg_name => 'OKL_INVALID_VALUE'
1662 , p_token1 => 'COL_NAME'
1663 , p_token1_value => l_ak_prompt
1664 );
1665 raise OKC_API.G_EXCEPTION_ERROR;
1666 End If;
1667
1668 End If;
1669
1670 If ( p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
1671 p_fee_types_rec.fee_type = G_FT_FINANCED
1672 ) Then
1673
1674 IF (p_fee_types_rec.fee_purpose_code is not null AND p_fee_types_rec.fee_purpose_code <> OKL_API.G_MISS_CHAR) THEN
1675
1676 l_fee_purpose_code := null;
1677 open l_fee_purpose_csr(p_fee_types_rec.fee_purpose_code);
1678 fetch l_fee_purpose_csr into l_fee_purpose_code;
1679 close l_fee_purpose_csr;
1680 /*
1681 If ( l_fee_purpose_code is null) Then
1682 x_return_status := OKC_API.g_ret_sts_error;
1683 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE_PURPOSE_MEANING');
1684 if(l_ak_prompt is null) Then l_ak_prompt := 'Fee Purpose Code'; End if;
1685 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1686 , p_msg_name => 'OKL_INVALID_VALUE'
1687 , p_token1 => 'COL_NAME'
1688 , p_token1_value => l_ak_prompt
1689 );
1690 raise OKC_API.G_EXCEPTION_ERROR;
1691 End If;
1692 */
1693 End If;
1694
1695 End If;
1696
1697 -- x_return_status := OKC_API.G_RET_STS_SUCCESS;
1698
1699
1700 -- Modified by zrehman for Bug#6763287 on 17-Jan-2008 start
1701 --Bug# 6917539: Investor agreement validation causes regression issues
1702 -- for contracts as NULL value is not handled
1703 ELSIF NVL(l_is_ivestor,0) = 1 THEN -- start validation of fee type for Investor Agreement
1704 If (
1705 p_fee_types_rec.fee_type = G_FT_INCOME or
1706 p_fee_types_rec.fee_type = G_FT_EXPENSE
1707 ) Then
1708
1709 If ( NOT (p_fee_types_rec.item_id1 is null or p_fee_types_rec.item_id1 = OKC_API.G_MISS_CHAR )) Then
1710 l_item_name := null;
1711 open l_item_name_csr;
1712 fetch l_item_name_csr into l_item_name;
1713 close l_item_name_csr;
1714 If(l_item_name is null) Then
1715 x_return_status := OKC_API.g_ret_sts_error;
1716 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE');
1717 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1718 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1719 , p_token1 => 'COL_NAME'
1720 , p_token1_value => l_ak_prompt
1721 );
1722 raise OKC_API.G_EXCEPTION_ERROR;
1723 End If;
1724
1725 x_fee_types_rec.item_name := l_item_name;
1726 x_fee_types_rec.item_id1 := p_fee_types_rec.item_id1;
1727 ElsIf (NOT( p_fee_types_rec.item_name is null or p_fee_types_rec.item_name = OKC_API.G_MISS_CHAR )) Then
1728
1729 If(p_fee_types_rec.fee_type = G_FT_EXPENSE) Then
1730 l_item_id := null;
1731 open l_ft_others_item_id_csr(l_pdt_id, l_start_date);
1732 fetch l_ft_others_item_id_csr into l_item_id;
1733 close l_ft_others_item_id_csr;
1734
1735 ElsIf(p_fee_types_rec.fee_type = G_FT_INCOME) Then
1736 l_item_id := null;
1737 open l_ft_income_item_id_csr(l_pdt_id, l_start_date);
1738 fetch l_ft_income_item_id_csr into l_item_id;
1739 close l_ft_income_item_id_csr;
1740 End If;
1741
1742 If(l_item_id is null) Then
1743 x_return_status := OKC_API.g_ret_sts_error;
1744 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE');
1745 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1746 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1747 , p_token1 => 'COL_NAME'
1748 , p_token1_value => l_ak_prompt
1749 );
1750 raise OKC_API.G_EXCEPTION_ERROR;
1751 End If;
1752 x_fee_types_rec.item_id1 := l_item_id;
1753
1754 Else -- item name, item id are null
1755 x_return_status := OKC_API.g_ret_sts_error;
1756 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_FEE');
1757 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1758 , p_msg_name => 'OKL_REQUIRED_VALUE'
1759 , p_token1 => 'COL_NAME'
1760 , p_token1_value => l_ak_prompt
1761 );
1762 raise OKC_API.G_EXCEPTION_ERROR;
1763 End If; -- check for item_id1 and item_name
1764
1765 -- validation for party_id1 start
1766 If ( NOT(p_fee_types_rec.party_id1 is null or p_fee_types_rec.party_id1 = OKC_API.G_MISS_NUM)) Then
1767 OPEN l_inv_id_csr;
1768 FETCH l_inv_id_csr into l_investor_name;
1769 CLOSE l_inv_id_csr;
1770
1771 If l_investor_name IS NULL THEN
1772 x_return_status := OKC_API.g_ret_sts_error;
1773 l_ak_prompt := GET_AK_PROMPT('OKL_CONTRACT_DTLS', 'OKL_LA_INVESTOR');
1774 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1775 , p_msg_name => 'OKL_INVALID_VALUE'
1776 , p_token1 => 'COL_NAME'
1777 , p_token1_value => l_ak_prompt
1778 );
1779 raise OKC_API.G_EXCEPTION_ERROR;
1780 End If;
1781 x_fee_types_rec.party_name := l_investor_name;
1782 ElsIf ( NOT(p_fee_types_rec.party_name is null or p_fee_types_rec.party_name = OKC_API.G_MISS_CHAR)) Then
1783 OPEN l_inv_name_csr;
1784 FETCH l_inv_name_csr into l_investor_id;
1785 CLOSE l_inv_name_csr;
1786 IF l_investor_id is NULL THEN
1787 l_ak_prompt := GET_AK_PROMPT('OKL_CONTRACT_DTLS', 'OKL_LA_INVESTOR');
1788 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1789 , p_msg_name => 'OKL_INVALID_VALUE'
1790 , p_token1 => 'COL_NAME'
1791 , p_token1_value => l_ak_prompt
1792 );
1793 raise OKC_API.G_EXCEPTION_ERROR;
1794 END IF;
1795
1796 x_fee_types_rec.party_id1 := l_investor_id;
1797 End If;
1798 -- validation for party_id1 end
1799 --validation for feeamount for investor
1800 If ( (l_investor_name is not null and p_fee_types_rec.fee_type = G_FT_EXPENSE) and (p_fee_types_rec.amount < 0) ) Then
1801 x_return_status := OKC_API.g_ret_sts_error;
1802 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1803 , p_msg_name => 'OKL_INV_AMOUNT_CHECK'
1804 );
1805 raise OKC_API.G_EXCEPTION_ERROR;
1806 End If;
1807
1808 -- validation for effective_from start
1809 If ( p_fee_types_rec.effective_from is null or p_fee_types_rec.effective_from = OKC_API.G_MISS_DATE ) Then
1810 x_return_status := OKC_API.g_ret_sts_error;
1811 l_ak_prompt := GET_AK_PROMPT('OKL_LA_MLA_DTAIL', 'OKL_START_DATE');
1812 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1813 , p_msg_name => 'OKL_REQUIRED_VALUE'
1814 , p_token1 => 'COL_NAME'
1815 , p_token1_value => l_ak_prompt
1816 );
1817 raise OKC_API.G_EXCEPTION_ERROR;
1818 End If;
1819 -- validation for effective_from end
1820
1821 -- validation for effective_to start
1822 If ( p_fee_types_rec.effective_to is null or p_fee_types_rec.effective_to = OKC_API.G_MISS_DATE ) Then
1823 x_return_status := OKC_API.g_ret_sts_error;
1824 l_ak_prompt := GET_AK_PROMPT('OKL_LA_MLA_DTAIL', 'OKL_END_DATE');
1825 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1826 , p_msg_name => 'OKL_REQUIRED_VALUE'
1827 , p_token1 => 'COL_NAME'
1828 , p_token1_value => l_ak_prompt
1829 );
1830 raise OKC_API.G_EXCEPTION_ERROR;
1831 End If;
1832 -- validation for effective_to end
1833
1834 -- validation for amount start
1835 If ( p_fee_types_rec.amount is null or p_fee_types_rec.amount = OKC_API.G_MISS_NUM ) Then
1836 x_return_status := OKC_API.g_ret_sts_error;
1837 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
1838 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1839 , p_msg_name => 'OKL_REQUIRED_VALUE'
1840 , p_token1 => 'COL_NAME'
1841 , p_token1_value => l_ak_prompt
1842 );
1843 raise OKC_API.G_EXCEPTION_ERROR;
1844 End If;
1845 -- validation for amount end
1846
1847 --End If;
1848 Else -- fee type is not among valid ones
1849 x_return_status := OKC_API.g_ret_sts_error;
1850 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1851 , p_msg_name => 'OKL_INVALID_FEE_TYPE'
1852 , p_token1 => 'COL_NAME'
1853 , p_token1_value => p_fee_types_rec.fee_type
1854 );
1855 raise OKC_API.G_EXCEPTION_ERROR;
1856
1857 END IF; -- end validation of fee type for Investor Agreement
1858 END IF; -- Check if contract or investor
1859
1860 -- Modified by zrehman on 17-Jan-2008 end
1861 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1862
1863 EXCEPTION
1864 when OKC_API.G_EXCEPTION_ERROR then
1865 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1866 p_api_name => l_api_name,
1867 p_pkg_name => g_pkg_name,
1868 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1869 x_msg_count => x_msg_count,
1870 x_msg_data => x_msg_data,
1871 p_api_type => g_api_type);
1872
1873 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1874 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1875 p_api_name => l_api_name,
1876 p_pkg_name => g_pkg_name,
1877 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1878 x_msg_count => x_msg_count,
1879 x_msg_data => x_msg_data,
1880 p_api_type => g_api_type);
1881
1882 when OTHERS then
1883 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1884 p_api_name => l_api_name,
1885 p_pkg_name => g_pkg_name,
1886 p_exc_name => 'OTHERS',
1887 x_msg_count => x_msg_count,
1888 x_msg_data => x_msg_data,
1889 p_api_type => g_api_type);
1890 END validate_fee_type;
1891
1892 -- Start of comments
1893 --
1894 -- Procedure Name : fill_fee_type_info
1895 -- Description :
1896 -- Business Rules :
1897 -- Parameters :
1898 -- Version : 1.0
1899 -- End of comments
1900 PROCEDURE fill_fee_type_info(
1901 p_api_version IN NUMBER,
1902 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1903 x_return_status OUT NOCOPY VARCHAR2,
1904 x_msg_count OUT NOCOPY NUMBER,
1905 x_msg_data OUT NOCOPY VARCHAR2,
1906 p_fee_types_rec IN fee_types_rec_type,
1907 x_clev_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type,
1908 x_klev_rec OUT NOCOPY okl_kle_pvt.klev_rec_type,
1909 x_cimv_rec OUT NOCOPY okl_okc_migration_pvt.cimv_rec_type,
1910 x_cplv_rec OUT NOCOPY okl_okc_migration_pvt.cplv_rec_type) IS
1911
1912 l_api_name CONSTANT VARCHAR2(30) := 'fill_fee_type_info';
1913 l_api_version CONSTANT NUMBER := 1.0;
1914 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
1915
1916 l_lse_id OKC_LINE_STYLES_B.id%type := null;
1917 l_currency_code okc_k_headers_b.currency_code%type := null;
1918 l_sts_code okc_k_headers_b.sts_code%type := null;
1919
1920 CURSOR get_lse_id_csr IS
1921 select id
1922 from okc_line_styles_v
1923 where lty_code = 'FEE';
1924
1925 CURSOR get_cur_sts_code_csr(chr_id NUMBER) IS
1926 select currency_code,sts_code
1927 from okc_k_headers_b
1928 where id = chr_id;
1929
1930 -- Modified by zrehman for Bug#6763287 on 17-Jan-2008 start
1931 l_is_investor NUMBER;
1932
1933 -- Modified by zrehman on 17-Jan-2008 end
1934 BEGIN
1935
1936 -- call START_ACTIVITY to create savepoint, check compatibility
1937 -- and initialize message list
1938 x_return_status := OKC_API.START_ACTIVITY(
1939 p_api_name => l_api_name,
1940 p_pkg_name => g_pkg_name,
1941 p_init_msg_list => p_init_msg_list,
1942 l_api_version => l_api_version,
1943 p_api_version => p_api_version,
1944 p_api_type => g_api_type,
1945 x_return_status => x_return_status);
1946
1947 -- check if activity started successfully
1948 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1949 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1950 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1951 raise OKC_API.G_EXCEPTION_ERROR;
1952 End If;
1953
1954 l_lse_id := null;
1955 open get_lse_id_csr;
1956 fetch get_lse_id_csr into l_lse_id;
1957 close get_lse_id_csr;
1958
1959 If(l_lse_id is null) Then
1960 x_return_status := OKC_API.g_ret_sts_error;
1961 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1962 , p_msg_name => 'OKL_INVALID_VALUE'
1963 , p_token1 => 'COL_NAME'
1964 , p_token1_value => 'LSE_ID'
1965 );
1966 raise OKC_API.G_EXCEPTION_ERROR;
1967 End If;
1968
1969 l_currency_code := null;
1970 l_sts_code := null;
1971 open get_cur_sts_code_csr(p_fee_types_rec.dnz_chr_id);
1972 fetch get_cur_sts_code_csr into l_currency_code,l_sts_code;
1973 close get_cur_sts_code_csr;
1974
1975 If(l_currency_code is null) Then
1976 x_return_status := OKC_API.g_ret_sts_error;
1977 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1978 , p_msg_name => 'OKL_INVALID_VALUE'
1979 , p_token1 => 'COL_NAME'
1980 , p_token1_value => 'CURRENCY_CODE'
1981 );
1982 raise OKC_API.G_EXCEPTION_ERROR;
1983 End If;
1984
1985 If(l_sts_code is null) Then
1986 x_return_status := OKC_API.g_ret_sts_error;
1987 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1988 , p_msg_name => 'OKL_INVALID_VALUE'
1989 , p_token1 => 'COL_NAME'
1990 , p_token1_value => 'STS_CODE'
1991 );
1992 raise OKC_API.G_EXCEPTION_ERROR;
1993 End If;
1994
1995 -- Modified by zrehman on 17-Jan-2008 start
1996 OPEN chk_inv_csr(p_fee_types_rec.dnz_chr_id);
1997 FETCH chk_inv_csr INTO l_is_investor;
1998 CLOSE chk_inv_csr;
1999 -- Modified by zrehman on 17-Jan-2008 end
2000
2001 x_clev_rec.currency_code := l_currency_code;
2002 x_clev_rec.sts_code := l_sts_code;
2003 x_clev_rec.lse_id := l_lse_id;
2004
2005 x_clev_rec.line_number := '1';
2006 x_clev_rec.exception_yn := 'N';
2007 x_clev_rec.display_sequence := 1;
2008 x_clev_rec.cle_id := null;
2009 x_clev_rec.dnz_chr_id := p_fee_types_rec.dnz_chr_id;
2010 x_clev_rec.chr_id := p_fee_types_rec.dnz_chr_id;
2011 x_clev_rec.name := p_fee_types_rec.item_name;
2012 x_clev_rec.id := p_fee_types_rec.line_id;
2013 x_clev_rec.start_date := p_fee_types_rec.effective_from;
2014 x_clev_rec.end_date := p_fee_types_rec.effective_to;
2015
2016 x_klev_rec.kle_id := x_clev_rec.id;
2017 x_klev_rec.initial_direct_cost := p_fee_types_rec.initial_direct_cost;
2018 x_klev_rec.amount := p_fee_types_rec.amount;
2019 x_klev_rec.fee_type := p_fee_types_rec.fee_type;
2020
2021 If( p_fee_types_rec.fee_type = G_FT_ROLLOVER ) Then
2022 x_klev_rec.qte_id := p_fee_types_rec.qte_id;
2023 End If;
2024
2025 x_klev_rec.funding_date := p_fee_types_rec.funding_date;
2026
2027 If( p_fee_types_rec.fee_type = G_FT_CAPITALIZED ) Then
2028 x_klev_rec.capital_amount := p_fee_types_rec.amount;
2029 End If;
2030
2031 If( p_fee_types_rec.fee_type = G_FT_ABSORBED) Then
2032 x_klev_rec.initial_direct_cost := p_fee_types_rec.amount;
2033 End If;
2034
2035 -- sales tax changes
2036 --Bug# 6917539: Investor agreement validation causes regression issues
2037 -- for contracts as NULL value is not handled
2038 If( NVL(l_is_investor,0) <> 1 AND (p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKL_API.G_MISS_NUM) and
2039 (p_fee_types_rec.fee_type = G_FT_CAPITALIZED OR p_fee_types_rec.fee_type = G_FT_FINANCED)
2040 OR (p_fee_types_rec.fee_type = G_FT_ABSORBED AND p_fee_types_rec.fee_purpose_code
2041 <> OKL_API.G_MISS_CHAR AND p_fee_types_rec.fee_purpose_code = 'RVI') ) Then
2042 x_klev_rec.fee_purpose_code := p_fee_types_rec.fee_purpose_code;
2043 End If;
2044
2045 --Bug# 4558486
2046 x_klev_rec.attribute_category := p_fee_types_rec.attribute_category;
2047 x_klev_rec.attribute1 := p_fee_types_rec.attribute1;
2048 x_klev_rec.attribute2 := p_fee_types_rec.attribute2;
2049 x_klev_rec.attribute3 := p_fee_types_rec.attribute3;
2050 x_klev_rec.attribute4 := p_fee_types_rec.attribute4;
2051 x_klev_rec.attribute5 := p_fee_types_rec.attribute5;
2052 x_klev_rec.attribute6 := p_fee_types_rec.attribute6;
2053 x_klev_rec.attribute7 := p_fee_types_rec.attribute7;
2054 x_klev_rec.attribute8 := p_fee_types_rec.attribute8;
2055 x_klev_rec.attribute9 := p_fee_types_rec.attribute9;
2056 x_klev_rec.attribute10 := p_fee_types_rec.attribute10;
2057 x_klev_rec.attribute11 := p_fee_types_rec.attribute11;
2058 x_klev_rec.attribute12 := p_fee_types_rec.attribute12;
2059 x_klev_rec.attribute13 := p_fee_types_rec.attribute13;
2060 x_klev_rec.attribute14 := p_fee_types_rec.attribute14;
2061 x_klev_rec.attribute15 := p_fee_types_rec.attribute15;
2062 x_klev_rec.validate_dff_yn := p_fee_types_rec.validate_dff_yn;
2063
2064 x_cimv_rec.cle_id := x_clev_rec.id;
2065 x_cimv_rec.cle_id_for := null;
2066 x_cimv_rec.chr_id := null;
2067 x_cimv_rec.exception_yn := 'N';
2068 x_cimv_rec.number_of_items := 1;
2069 x_cimv_rec.dnz_chr_id := p_fee_types_rec.dnz_chr_id;
2070 x_cimv_rec.id := p_fee_types_rec.item_id;
2071 x_cimv_rec.object1_id1 := p_fee_types_rec.item_id1;
2072 x_cimv_rec.object1_id2 := '#';
2073 x_cimv_rec.jtot_object1_code := 'OKL_STRMTYP';
2074
2075 x_cplv_rec.dnz_chr_id := p_fee_types_rec.dnz_chr_id;
2076 x_cplv_rec.cle_id := x_clev_rec.id;
2077 x_cplv_rec.id := p_fee_types_rec.party_id;
2078 x_cplv_rec.object1_id1 := p_fee_types_rec.party_id1;
2079 x_cplv_rec.object1_id2 := '#';
2080 x_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
2081 x_cplv_rec.rle_code := 'OKL_VENDOR';
2082
2083 -- Modified by zrehman on 17-Jan-2008 start
2084 --Bug# 6917539: Investor agreement validation causes regression issues
2085 -- for contracts as NULL value is not handled
2086 IF (NVL(l_is_investor,0) = 1) THEN
2087 x_clev_rec.chr_id := p_fee_types_rec.dnz_chr_id;
2088 x_klev_rec.kle_id := x_clev_rec.cle_id;
2089 x_klev_rec.initial_direct_cost := null;
2090 x_cplv_rec.jtot_object1_code := 'OKX_PARTY';
2091 x_cplv_rec.rle_code := 'INVESTOR';
2092 END IF;
2093 -- Modified by zrehman on 17-Jan-2008 end
2094
2095 -- Bug# 4721428
2096 -- Reverted commenting out NOT predicate, as this is causes UI issues
2097 --Bug# 6917539: Investor agreement validation causes regression issues
2098 -- for contracts as NULL value is not handled
2099 If ( NVL(l_is_investor,0) <> 1 AND (
2100 p_fee_types_rec.fee_type = G_FT_PASSTHROUGH or
2101 --p_fee_types_rec.fee_type = G_FT_CAPITALIZED or
2102 p_fee_types_rec.fee_type = G_FT_EXPENSE or
2103 --p_fee_types_rec.fee_type = G_FT_FINANCED or
2104 p_fee_types_rec.fee_type = G_FT_MISCELLANEOUS)
2105 and ( (NOT ( p_fee_types_rec.party_id is null or p_fee_types_rec.party_id = OKC_API.G_MISS_NUM)) and
2106 p_fee_types_rec.party_name is null)
2107
2108 ) Then
2109
2110 x_return_status := OKC_API.g_ret_sts_error;
2111 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
2112 OKC_API.SET_MESSAGE( p_app_name => g_app_name
2113 , p_msg_name => 'OKL_REQUIRED_VALUE'
2114 , p_token1 => 'COL_NAME'
2115 , p_token1_value => l_ak_prompt
2116 );
2117 raise OKC_API.G_EXCEPTION_ERROR;
2118
2119 End If;
2120
2121 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2122
2123 EXCEPTION
2124 when OKC_API.G_EXCEPTION_ERROR then
2125 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2126 p_api_name => l_api_name,
2127 p_pkg_name => g_pkg_name,
2128 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2129 x_msg_count => x_msg_count,
2130 x_msg_data => x_msg_data,
2131 p_api_type => g_api_type);
2132
2133 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2134 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2135 p_api_name => l_api_name,
2136 p_pkg_name => g_pkg_name,
2137 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2138 x_msg_count => x_msg_count,
2139 x_msg_data => x_msg_data,
2140 p_api_type => g_api_type);
2141
2142 when OTHERS then
2143 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2144 p_api_name => l_api_name,
2145 p_pkg_name => g_pkg_name,
2146 p_exc_name => 'OTHERS',
2147 x_msg_count => x_msg_count,
2148 x_msg_data => x_msg_data,
2149 p_api_type => g_api_type);
2150
2151 END fill_fee_type_info;
2152
2153
2154 PROCEDURE create_fee_top_line(
2155 p_api_version IN NUMBER,
2156 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2157 x_return_status OUT NOCOPY VARCHAR2,
2158 x_msg_count OUT NOCOPY NUMBER,
2159 x_msg_data OUT NOCOPY VARCHAR2,
2160 p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
2161 p_klev_rec IN okl_kle_pvt.klev_rec_type,
2162 p_cimv_rec IN okl_okc_migration_pvt.cimv_rec_type,
2163 p_cplv_rec IN okl_okc_migration_pvt.cplv_rec_type,
2164 x_clev_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type,
2165 x_klev_rec OUT NOCOPY okl_kle_pvt.klev_rec_type,
2166 x_cimv_rec OUT NOCOPY okl_okc_migration_pvt.cimv_rec_type,
2167 x_cplv_rec OUT NOCOPY okl_okc_migration_pvt.cplv_rec_type) IS
2168
2169 l_clev_rec okl_okc_migration_pvt.clev_rec_type := p_clev_rec;
2170 l_klev_rec okl_kle_pvt.klev_rec_type := p_klev_rec;
2171 l_cimv_rec okl_okc_migration_pvt.cimv_rec_type := p_cimv_rec;
2172 l_cplv_rec okl_okc_migration_pvt.cplv_rec_type := p_cplv_rec;
2173
2174 l_chr_id l_clev_rec.dnz_chr_id%type;
2175
2176 l_api_name CONSTANT VARCHAR2(30) := 'create_fee_top_line';
2177 l_api_version CONSTANT NUMBER := 1.0;
2178
2179 --Bug# 4558486
2180 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
2181 x_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
2182
2183 BEGIN
2184
2185 l_chr_id := l_clev_rec.dnz_chr_id;
2186 If okl_context.get_okc_org_id is null then
2187 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
2188 End If;
2189
2190 -- call START_ACTIVITY to create savepoint, check compatibility
2191 -- and initialize message list
2192 x_return_status := OKC_API.START_ACTIVITY(
2193 p_api_name => l_api_name,
2194 p_pkg_name => g_pkg_name,
2195 p_init_msg_list => p_init_msg_list,
2196 l_api_version => l_api_version,
2197 p_api_version => p_api_version,
2198 p_api_type => g_api_type,
2199 x_return_status => x_return_status);
2200
2201 -- check if activity started successfully
2202 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2203 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2204 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2205 raise OKC_API.G_EXCEPTION_ERROR;
2206 End If;
2207
2208 okl_contract_pvt.create_contract_line(
2209 p_api_version => p_api_version,
2210 p_init_msg_list => p_init_msg_list,
2211 x_return_status => x_return_status,
2212 x_msg_count => x_msg_count,
2213 x_msg_data => x_msg_data,
2214 p_clev_rec => l_clev_rec,
2215 p_klev_rec => l_klev_rec,
2216 x_clev_rec => x_clev_rec,
2217 x_klev_rec => x_klev_rec);
2218
2219 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2220 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2221 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2222 raise OKC_API.G_EXCEPTION_ERROR;
2223 END IF;
2224
2225 l_cimv_rec.cle_id := x_clev_rec.id;
2226
2227 okl_okc_migration_pvt.create_contract_item(
2228 p_api_version => p_api_version,
2229 p_init_msg_list => p_init_msg_list,
2230 x_return_status => x_return_status,
2231 x_msg_count => x_msg_count,
2232 x_msg_data => x_msg_data,
2233 p_cimv_rec => l_cimv_rec,
2234 x_cimv_rec => x_cimv_rec);
2235
2236 -- check return status
2237 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2238 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2239 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2240 raise OKC_API.G_EXCEPTION_ERROR;
2241 End If;
2242
2243 l_cplv_rec.cle_id := x_clev_rec.id;
2244
2245 If ( l_cplv_rec.object1_id1 is not null and l_cplv_rec.object1_id2 is not null) Then
2246
2247 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
2248 -- to create records in tables
2249 -- okc_k_party_roles_b and okl_k_party_roles
2250 /*
2251 okl_okc_migration_pvt.create_k_party_role(
2252 p_api_version => p_api_version,
2253 p_init_msg_list => p_init_msg_list,
2254 x_return_status => x_return_status,
2255 x_msg_count => x_msg_count,
2256 x_msg_data => x_msg_data,
2257 p_cplv_rec => l_cplv_rec,
2258 x_cplv_rec => x_cplv_rec);
2259 */
2260
2261 okl_k_party_roles_pvt.create_k_party_role(
2262 p_api_version => p_api_version,
2263 p_init_msg_list => p_init_msg_list,
2264 x_return_status => x_return_status,
2265 x_msg_count => x_msg_count,
2266 x_msg_data => x_msg_data,
2267 p_cplv_rec => l_cplv_rec,
2268 x_cplv_rec => x_cplv_rec,
2269 p_kplv_rec => l_kplv_rec,
2270 x_kplv_rec => x_kplv_rec);
2271
2272 -- check return status
2273 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2274 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2275 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2276 raise OKC_API.G_EXCEPTION_ERROR;
2277 End If;
2278
2279 End if;
2280
2281 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2282
2283 EXCEPTION
2284 when OKC_API.G_EXCEPTION_ERROR then
2285 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2286 p_api_name => l_api_name,
2287 p_pkg_name => g_pkg_name,
2288 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2289 x_msg_count => x_msg_count,
2290 x_msg_data => x_msg_data,
2291 p_api_type => g_api_type);
2292
2293 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2294 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2295 p_api_name => l_api_name,
2296 p_pkg_name => g_pkg_name,
2297 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2298 x_msg_count => x_msg_count,
2299 x_msg_data => x_msg_data,
2300 p_api_type => g_api_type);
2301
2302 when OTHERS then
2303 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2304 p_api_name => l_api_name,
2305 p_pkg_name => g_pkg_name,
2306 p_exc_name => 'OTHERS',
2307 x_msg_count => x_msg_count,
2308 x_msg_data => x_msg_data,
2309 p_api_type => g_api_type);
2310 END create_fee_top_line;
2311
2312
2313
2314 -- Start of comments
2315 --
2316 -- Procedure Name : create_fee_type
2317 -- Description :
2318 -- Business Rules :
2319 -- Parameters :
2320 -- Version : 1.0
2321 -- End of comments
2322 PROCEDURE create_fee_type(
2323 p_api_version IN NUMBER,
2324 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2325 x_return_status OUT NOCOPY VARCHAR2,
2326 x_msg_count OUT NOCOPY NUMBER,
2327 x_msg_data OUT NOCOPY VARCHAR2,
2328 p_fee_types_rec IN fee_types_rec_type,
2329 x_fee_types_rec OUT NOCOPY fee_types_rec_type
2330 ) IS
2331
2332 lp_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type := p_fee_types_rec;
2333 lx_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
2334
2335 lp_klev_rec okl_kle_pvt.klev_rec_type;
2336 lp_clev_rec okl_okc_migration_pvt.clev_rec_type;
2337 lp_cimv_rec okl_okc_migration_pvt.cimv_rec_type;
2338 lp_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
2339
2340 lx_klev_rec okl_kle_pvt.klev_rec_type;
2341 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
2342 lx_cimv_rec okl_okc_migration_pvt.cimv_rec_type;
2343 lx_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
2344
2345 l_chr_id okc_k_headers_b.id%type := p_fee_types_rec.dnz_chr_id;
2346
2347 l_api_name CONSTANT VARCHAR2(30) := 'create_fee_type';
2348 l_api_version CONSTANT NUMBER := 1.0;
2349 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
2350
2351 BEGIN
2352
2353 -- call START_ACTIVITY to create savepoint, check compatibility
2354 -- and initialize message list
2355 x_return_status := OKC_API.START_ACTIVITY(
2356 p_api_name => l_api_name,
2357 p_pkg_name => g_pkg_name,
2358 p_init_msg_list => p_init_msg_list,
2359 l_api_version => l_api_version,
2360 p_api_version => p_api_version,
2361 p_api_type => g_api_type,
2362 x_return_status => x_return_status);
2363
2364 -- check if activity started successfully
2365 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2366 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2367 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2368 raise OKC_API.G_EXCEPTION_ERROR;
2369 End If;
2370
2371 OKL_MAINTAIN_FEE_PVT.validate_fee_type(
2372 p_api_version => p_api_version,
2373 p_init_msg_list => p_init_msg_list,
2374 x_return_status => x_return_status,
2375 x_msg_count => x_msg_count,
2376 x_msg_data => x_msg_data,
2377 p_fee_types_rec => lp_fee_types_rec,
2378 x_fee_types_rec => lx_fee_types_rec
2379 );
2380
2381 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2382 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2383 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2384 raise OKC_API.G_EXCEPTION_ERROR;
2385 End If;
2386
2387 fill_fee_type_info(
2388 p_api_version => p_api_version,
2389 p_init_msg_list => p_init_msg_list,
2390 x_return_status => x_return_status,
2391 x_msg_count => x_msg_count,
2392 x_msg_data => x_msg_data,
2393 p_fee_types_rec => lx_fee_types_rec,
2394 x_clev_rec => lx_clev_rec,
2395 x_klev_rec => lx_klev_rec,
2396 x_cimv_rec => lx_cimv_rec,
2397 x_cplv_rec => lx_cplv_rec);
2398
2399 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2400 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2401 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2402 raise OKC_API.G_EXCEPTION_ERROR;
2403 End If;
2404
2405 lp_clev_rec := lx_clev_rec;
2406 lp_klev_rec := lx_klev_rec;
2407 lp_cimv_rec := lx_cimv_rec;
2408 lp_cplv_rec := lx_cplv_rec;
2409
2410 If okl_context.get_okc_org_id is null then
2411 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
2412 End If;
2413
2414 OKL_MAINTAIN_FEE_PVT.create_fee_top_line(
2415 p_api_version => p_api_version,
2416 p_init_msg_list => p_init_msg_list,
2417 x_return_status => x_return_status,
2418 x_msg_count => x_msg_count,
2419 x_msg_data => x_msg_data,
2420 p_clev_rec => lp_clev_rec,
2421 p_klev_rec => lp_klev_rec,
2422 p_cimv_rec => lp_cimv_rec,
2423 p_cplv_rec => lp_cplv_rec,
2424 x_clev_rec => lx_clev_rec,
2425 x_klev_rec => lx_klev_rec,
2426 x_cimv_rec => lx_cimv_rec,
2427 x_cplv_rec => lx_cplv_rec
2428 );
2429
2430 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2431 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2432 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2433 raise OKC_API.G_EXCEPTION_ERROR;
2434 End If;
2435
2436 x_fee_types_rec.line_id := lx_clev_rec.id;
2437 x_fee_types_rec.item_id := lx_cimv_rec.id;
2438 x_fee_types_rec.party_id := lx_cplv_rec.id;
2439
2440 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2441
2442 EXCEPTION
2443 when OKC_API.G_EXCEPTION_ERROR then
2444 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2445 p_api_name => l_api_name,
2446 p_pkg_name => g_pkg_name,
2447 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2448 x_msg_count => x_msg_count,
2449 x_msg_data => x_msg_data,
2450 p_api_type => g_api_type);
2451
2452 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2453 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2454 p_api_name => l_api_name,
2455 p_pkg_name => g_pkg_name,
2456 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2457 x_msg_count => x_msg_count,
2458 x_msg_data => x_msg_data,
2459 p_api_type => g_api_type);
2460
2461 when OTHERS then
2462 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2463 p_api_name => l_api_name,
2464 p_pkg_name => g_pkg_name,
2465 p_exc_name => 'OTHERS',
2466 x_msg_count => x_msg_count,
2467 x_msg_data => x_msg_data,
2468 p_api_type => g_api_type);
2469
2470 END;
2471
2472
2473 PROCEDURE update_fee_top_line(
2474 p_api_version IN NUMBER,
2475 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2476 x_return_status OUT NOCOPY VARCHAR2,
2477 x_msg_count OUT NOCOPY NUMBER,
2478 x_msg_data OUT NOCOPY VARCHAR2,
2479 p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
2480 p_klev_rec IN okl_kle_pvt.klev_rec_type,
2481 p_cimv_rec IN okl_okc_migration_pvt.cimv_rec_type,
2482 p_cplv_rec IN okl_okc_migration_pvt.cplv_rec_type,
2483 x_clev_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type,
2484 x_klev_rec OUT NOCOPY okl_kle_pvt.klev_rec_type,
2485 x_cimv_rec OUT NOCOPY okl_okc_migration_pvt.cimv_rec_type,
2486 x_cplv_rec OUT NOCOPY okl_okc_migration_pvt.cplv_rec_type) IS
2487
2488 l_clev_rec okl_okc_migration_pvt.clev_rec_type := p_clev_rec;
2489 l_klev_rec okl_kle_pvt.klev_rec_type := p_klev_rec;
2490 l_cimv_rec okl_okc_migration_pvt.cimv_rec_type := p_cimv_rec;
2491 l_cplv_rec okl_okc_migration_pvt.cplv_rec_type := p_cplv_rec;
2492
2493 l_chr_id l_clev_rec.dnz_chr_id%type;
2494
2495 l_api_name CONSTANT VARCHAR2(30) := 'update_fee_top_line';
2496 l_api_version CONSTANT NUMBER := 1.0;
2497
2498 --Bug# 4558486
2499 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
2500 x_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
2501
2502 --Bug# 4721428
2503 CURSOR fee_subline_csr (p_cle_id IN NUMBER,
2504 p_chr_id IN NUMBER) IS
2505 SELECT cle.id,
2506 cle.start_date,
2507 cle.end_date
2508 FROM okc_k_lines_b cle
2509 WHERE cle.cle_id = p_cle_id
2510 AND cle.dnz_chr_id = p_chr_id;
2511
2512 l_sub_clev_rec okl_okc_migration_pvt.clev_rec_type;
2513 l_sub_klev_rec okl_kle_pvt.klev_rec_type;
2514
2515 x_sub_clev_rec okl_okc_migration_pvt.clev_rec_type;
2516 x_sub_klev_rec okl_kle_pvt.klev_rec_type;
2517
2518 BEGIN
2519
2520 l_chr_id := l_clev_rec.dnz_chr_id;
2521 If okl_context.get_okc_org_id is null then
2522 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
2523 End If;
2524
2525 -- call START_ACTIVITY to create savepoint, check compatibility
2526 -- and initialize message list
2527 x_return_status := OKC_API.START_ACTIVITY(
2528 p_api_name => l_api_name,
2529 p_pkg_name => g_pkg_name,
2530 p_init_msg_list => p_init_msg_list,
2531 l_api_version => l_api_version,
2532 p_api_version => p_api_version,
2533 p_api_type => g_api_type,
2534 x_return_status => x_return_status);
2535
2536 -- check if activity started successfully
2537 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2538 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2539 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2540 raise OKC_API.G_EXCEPTION_ERROR;
2541 End If;
2542
2543 okl_contract_pvt.update_contract_line(
2544 p_api_version => p_api_version,
2545 p_init_msg_list => p_init_msg_list,
2546 x_return_status => x_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_clev_rec => l_clev_rec,
2550 p_klev_rec => l_klev_rec,
2551 x_clev_rec => x_clev_rec,
2552 x_klev_rec => x_klev_rec);
2553
2554 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2555 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2556 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2557 raise OKC_API.G_EXCEPTION_ERROR;
2558 END IF;
2559
2560 --Bug# 4721428
2561 For fee_subline_rec In fee_subline_csr(p_cle_id => l_clev_rec.id,
2562 p_chr_id => l_clev_rec.dnz_chr_id) Loop
2563
2564 If ( (NVL(l_clev_rec.start_date,OKL_API.G_MISS_DATE) <> OKL_API.G_MISS_DATE AND
2565 fee_subline_rec.start_date <> l_clev_rec.start_date) OR
2566 (NVL(l_clev_rec.end_date,OKL_API.G_MISS_DATE) <> OKL_API.G_MISS_DATE AND
2567 fee_subline_rec.end_date <> l_clev_rec.end_date) ) Then
2568
2569 l_sub_clev_rec.id := fee_subline_rec.id;
2570 l_sub_klev_rec.id := fee_subline_rec.id;
2571 l_sub_clev_rec.start_date :=l_clev_rec.start_date;
2572 l_sub_clev_rec.end_date :=l_clev_rec.end_date;
2573
2574 OKL_CONTRACT_PVT.update_contract_line(
2575 p_api_version => p_api_version,
2576 p_init_msg_list => p_init_msg_list,
2577 x_return_status => x_return_status,
2578 x_msg_count => x_msg_count,
2579 x_msg_data => x_msg_data,
2580 p_clev_rec => l_sub_clev_rec,
2581 p_klev_rec => l_sub_klev_rec,
2582 x_clev_rec => x_sub_clev_rec,
2583 x_klev_rec => x_sub_klev_rec
2584 );
2585
2586 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2587 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2588 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2589 RAISE OKL_API.G_EXCEPTION_ERROR;
2590 END IF;
2591 End If;
2592 End Loop;
2593 --Bug# 4721428
2594
2595 l_cimv_rec.cle_id := x_clev_rec.id;
2596
2597 okl_okc_migration_pvt.update_contract_item(
2598 p_api_version => p_api_version,
2599 p_init_msg_list => p_init_msg_list,
2600 x_return_status => x_return_status,
2601 x_msg_count => x_msg_count,
2602 x_msg_data => x_msg_data,
2603 p_cimv_rec => l_cimv_rec,
2604 x_cimv_rec => x_cimv_rec);
2605
2606 -- check return status
2607 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2608 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2609 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2610 raise OKC_API.G_EXCEPTION_ERROR;
2611 End If;
2612
2613 l_cplv_rec.cle_id := x_clev_rec.id;
2614
2615
2616 If ( (l_cplv_rec.id is null or l_cplv_rec.id = OKC_API.G_MISS_NUM ) and ( l_cplv_rec.object1_id1 is not null )) Then
2617
2618 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
2619 -- to create records in tables
2620 -- okc_k_party_roles_b and okl_k_party_roles
2621 /*
2622 okl_okc_migration_pvt.create_k_party_role(
2623 p_api_version => p_api_version,
2624 p_init_msg_list => p_init_msg_list,
2625 x_return_status => x_return_status,
2626 x_msg_count => x_msg_count,
2627 x_msg_data => x_msg_data,
2628 p_cplv_rec => l_cplv_rec,
2629 x_cplv_rec => x_cplv_rec);
2630 */
2631 okl_k_party_roles_pvt.create_k_party_role(
2632 p_api_version => p_api_version,
2633 p_init_msg_list => p_init_msg_list,
2634 x_return_status => x_return_status,
2635 x_msg_count => x_msg_count,
2636 x_msg_data => x_msg_data,
2637 p_cplv_rec => l_cplv_rec,
2638 x_cplv_rec => x_cplv_rec,
2639 p_kplv_rec => l_kplv_rec,
2640 x_kplv_rec => x_kplv_rec);
2641
2642 -- check return status
2643 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2644 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2645 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2646 raise OKC_API.G_EXCEPTION_ERROR;
2647 End If;
2648
2649 ElsIf ( l_cplv_rec.id is not null and l_cplv_rec.object1_id1 is not null ) Then
2650
2651 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
2652 -- to update records in tables
2653 -- okc_k_party_roles_b and okl_k_party_roles
2654 /*
2655 okl_okc_migration_pvt.update_k_party_role(
2656 p_api_version => p_api_version,
2657 p_init_msg_list => p_init_msg_list,
2658 x_return_status => x_return_status,
2659 x_msg_count => x_msg_count,
2660 x_msg_data => x_msg_data,
2661 p_cplv_rec => l_cplv_rec,
2662 x_cplv_rec => x_cplv_rec);
2663 */
2664
2665 l_kplv_rec.id := l_cplv_rec.id;
2666 okl_k_party_roles_pvt.update_k_party_role(
2667 p_api_version => p_api_version,
2668 p_init_msg_list => p_init_msg_list,
2669 x_return_status => x_return_status,
2670 x_msg_count => x_msg_count,
2671 x_msg_data => x_msg_data,
2672 p_cplv_rec => l_cplv_rec,
2673 x_cplv_rec => x_cplv_rec,
2674 p_kplv_rec => l_kplv_rec,
2675 x_kplv_rec => x_kplv_rec );
2676
2677 -- check return status
2678 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2679 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2680 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2681 raise OKC_API.G_EXCEPTION_ERROR;
2682 End If;
2683
2684 Elsif ( l_cplv_rec.id is not null and l_cplv_rec.object1_id1 is null ) Then
2685
2686 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
2687 -- to delete records in tables
2688 -- okc_k_party_roles_b and okl_k_party_roles
2689 /*
2690 okl_okc_migration_pvt.delete_k_party_role(
2691 p_api_version => p_api_version,
2692 p_init_msg_list => p_init_msg_list,
2693 x_return_status => x_return_status,
2694 x_msg_count => x_msg_count,
2695 x_msg_data => x_msg_data,
2696 p_cplv_rec => l_cplv_rec);
2697 */
2698
2699 l_kplv_rec.id := l_cplv_rec.id;
2700 okl_k_party_roles_pvt.delete_k_party_role(
2701 p_api_version => p_api_version,
2702 p_init_msg_list => p_init_msg_list,
2703 x_return_status => x_return_status,
2704 x_msg_count => x_msg_count,
2705 x_msg_data => x_msg_data,
2706 p_cplv_rec => l_cplv_rec,
2707 p_kplv_rec => l_kplv_rec);
2708
2709 -- check return status
2710 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2711 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2712 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2713 raise OKC_API.G_EXCEPTION_ERROR;
2714 End If;
2715
2716 End if;
2717
2718 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2719
2720 EXCEPTION
2721 when OKC_API.G_EXCEPTION_ERROR then
2722 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2723 p_api_name => l_api_name,
2724 p_pkg_name => g_pkg_name,
2725 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2726 x_msg_count => x_msg_count,
2727 x_msg_data => x_msg_data,
2728 p_api_type => g_api_type);
2729
2730 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2731 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2732 p_api_name => l_api_name,
2733 p_pkg_name => g_pkg_name,
2734 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2735 x_msg_count => x_msg_count,
2736 x_msg_data => x_msg_data,
2737 p_api_type => g_api_type);
2738
2739 when OTHERS then
2740 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2741 p_api_name => l_api_name,
2742 p_pkg_name => g_pkg_name,
2743 p_exc_name => 'OTHERS',
2744 x_msg_count => x_msg_count,
2745 x_msg_data => x_msg_data,
2746 p_api_type => g_api_type);
2747 END update_fee_top_line;
2748
2749
2750 -- Start of comments
2751 --
2752 -- Procedure Name : update_fee_type
2753 -- Description :
2754 -- Business Rules :
2755 -- Parameters :
2756 -- Version : 1.0
2757 -- End of comments
2758 PROCEDURE update_fee_type(
2759 p_api_version IN NUMBER,
2760 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2761 x_return_status OUT NOCOPY VARCHAR2,
2762 x_msg_count OUT NOCOPY NUMBER,
2763 x_msg_data OUT NOCOPY VARCHAR2,
2764 p_fee_types_rec IN fee_types_rec_type,
2765 x_fee_types_rec OUT NOCOPY fee_types_rec_type
2766 ) IS
2767
2768 lp_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type := p_fee_types_rec;
2769 lx_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
2770
2771 lp_klev_rec okl_kle_pvt.klev_rec_type;
2772 lp_clev_rec okl_okc_migration_pvt.clev_rec_type;
2773 lp_cimv_rec okl_okc_migration_pvt.cimv_rec_type;
2774 lp_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
2775
2776 lx_klev_rec okl_kle_pvt.klev_rec_type;
2777 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
2778 lx_cimv_rec okl_okc_migration_pvt.cimv_rec_type;
2779 lx_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
2780
2781 l_chr_id okc_k_headers_b.id%type := p_fee_types_rec.dnz_chr_id;
2782 l_rgp_id okc_rules_v.id%type := null;
2783 l_rul_id okc_rules_v.id%type := null;
2784
2785 l_api_name CONSTANT VARCHAR2(30) := 'update_fee_type';
2786 l_api_version CONSTANT NUMBER := 1.0;
2787 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
2788
2789 CURSOR l_strm_type_rul_csr IS
2790 select rgp.id,
2791 rul.id
2792 from okc_rules_v rul,
2793 okc_rule_groups_v rgp
2794 where rgp.id = rul.rgp_id
2795 and rgp.rgd_code = 'LAPSTH'
2796 and rul.rule_information_category = 'LASTRM'
2797 and rgp.cle_id = p_fee_types_rec.line_id
2798 and rul.dnz_chr_id = p_fee_types_rec.dnz_chr_id
2799 and rgp.dnz_chr_id = p_fee_types_rec.dnz_chr_id;
2800
2801 --Bug# 4899328
2802 l_chk_rebook_chr VARCHAR2(1);
2803
2804 CURSOR l_line_csr(p_cle_id IN NUMBER) IS
2805 SELECT kle.qte_id,
2806 cle.orig_system_id1
2807 FROM okl_k_lines kle,
2808 okc_k_lines_b cle
2809 WHERE cle.id = p_cle_id
2810 AND kle.id = cle.id;
2811
2812 -- Bug# 6438785
2813 CURSOR c_orig_cle_csr(p_cle_id IN NUMBER) IS
2814 SELECT cle.start_date
2815 FROM okc_k_lines_b cle
2816 WHERE cle.id = p_cle_id;
2817
2818 l_orig_cle_rec c_orig_cle_csr%ROWTYPE;
2819
2820 BEGIN
2821
2822 -- call START_ACTIVITY to create savepoint, check compatibility
2823 -- and initialize message list
2824 x_return_status := OKC_API.START_ACTIVITY(
2825 p_api_name => l_api_name,
2826 p_pkg_name => g_pkg_name,
2827 p_init_msg_list => p_init_msg_list,
2828 l_api_version => l_api_version,
2829 p_api_version => p_api_version,
2830 p_api_type => g_api_type,
2831 x_return_status => x_return_status);
2832
2833 -- check if activity started successfully
2834 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2835 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2836 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2837 raise OKC_API.G_EXCEPTION_ERROR;
2838 End If;
2839
2840 --Bug# 4959361
2841 OKL_LLA_UTIL_PVT.check_line_update_allowed
2842 (p_api_version => p_api_version,
2843 p_init_msg_list => p_init_msg_list,
2844 x_return_status => x_return_status,
2845 x_msg_count => x_msg_count,
2846 x_msg_data => x_msg_data,
2847 p_cle_id => lp_fee_types_rec.line_id);
2848
2849 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2850 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2851 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2852 RAISE OKL_API.G_EXCEPTION_ERROR;
2853 END IF;
2854 --Bug# 4959361
2855
2856 OKL_MAINTAIN_FEE_PVT.validate_fee_type(
2857 p_api_version => p_api_version,
2858 p_init_msg_list => p_init_msg_list,
2859 x_return_status => x_return_status,
2860 x_msg_count => x_msg_count,
2861 x_msg_data => x_msg_data,
2862 p_fee_types_rec => lp_fee_types_rec,
2863 x_fee_types_rec => lx_fee_types_rec
2864 );
2865
2866 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2867 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2868 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2869 raise OKC_API.G_EXCEPTION_ERROR;
2870 End If;
2871
2872 --Bug# 4899328
2873 l_chk_rebook_chr := OKL_LLA_UTIL_PVT.check_rebook_contract(p_chr_id => lp_fee_types_rec.dnz_chr_id);
2874 If (l_chk_rebook_chr = OKL_API.G_TRUE and lp_fee_types_rec.fee_type = G_FT_ROLLOVER) Then
2875
2876 For l_line_rec In l_line_csr(p_cle_id => lp_fee_types_rec.line_id) Loop
2877
2878 If (l_line_rec.orig_system_id1 IS NOT NULL And
2879 l_line_rec.qte_id <> lp_fee_types_rec.qte_id) Then
2880
2881 OKL_API.set_message(p_app_name => G_APP_NAME,
2882 p_msg_name => 'OKL_LA_RBK_ROLL_QT_UPDATE');
2883 RAISE OKL_API.G_EXCEPTION_ERROR;
2884
2885 End If;
2886 End Loop;
2887 End If;
2888 --Bug# 4899328
2889
2890 fill_fee_type_info(
2891 p_api_version => p_api_version,
2892 p_init_msg_list => p_init_msg_list,
2893 x_return_status => x_return_status,
2894 x_msg_count => x_msg_count,
2895 x_msg_data => x_msg_data,
2896 p_fee_types_rec => lp_fee_types_rec,
2897 x_clev_rec => lx_clev_rec,
2898 x_klev_rec => lx_klev_rec,
2899 x_cimv_rec => lx_cimv_rec,
2900 x_cplv_rec => lx_cplv_rec);
2901
2902 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2903 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2904 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2905 raise OKC_API.G_EXCEPTION_ERROR;
2906 End If;
2907
2908 lp_clev_rec := lx_clev_rec;
2909 lp_klev_rec := lx_klev_rec;
2910 lp_cimv_rec := lx_cimv_rec;
2911 lp_cplv_rec := lx_cplv_rec;
2912
2913 If okl_context.get_okc_org_id is null then
2914 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
2915 End If;
2916
2917 -- Bug# 6438785
2918 -- Fetch original fee line start date for checking
2919 -- whether start date has been changed
2920 OPEN c_orig_cle_csr(p_cle_id => lp_clev_rec.id);
2921 FETCH c_orig_cle_csr INTO l_orig_cle_rec;
2922 CLOSE c_orig_cle_csr;
2923
2924 OKL_MAINTAIN_FEE_PVT.update_fee_top_line(
2925 p_api_version => p_api_version,
2926 p_init_msg_list => p_init_msg_list,
2927 x_return_status => x_return_status,
2928 x_msg_count => x_msg_count,
2929 x_msg_data => x_msg_data,
2930 p_clev_rec => lp_clev_rec,
2931 p_klev_rec => lp_klev_rec,
2932 p_cimv_rec => lp_cimv_rec,
2933 p_cplv_rec => lp_cplv_rec,
2934 x_clev_rec => lx_clev_rec,
2935 x_klev_rec => lx_klev_rec,
2936 x_cimv_rec => lx_cimv_rec,
2937 x_cplv_rec => lx_cplv_rec
2938 );
2939
2940 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2941 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2942 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2943 raise OKC_API.G_EXCEPTION_ERROR;
2944 End If;
2945
2946 x_fee_types_rec.line_id := lx_clev_rec.id;
2947 x_fee_types_rec.item_id := lx_cimv_rec.id;
2948 x_fee_types_rec.party_id := lx_cplv_rec.id;
2949
2950 -- Bug# 6438785
2951 -- When the fee line start date is changed, update the
2952 -- start dates for all fee and sub-line payments based on
2953 -- the new line start date
2954
2955 IF (lx_clev_rec.start_date <> l_orig_cle_rec.start_date) THEN
2956
2957 OKL_LA_PAYMENTS_PVT.update_pymt_start_date
2958 (p_api_version => p_api_version,
2959 p_init_msg_list => p_init_msg_list,
2960 x_return_status => x_return_status,
2961 x_msg_count => x_msg_count,
2962 x_msg_data => x_msg_data,
2963 p_chr_id => lx_clev_rec.dnz_chr_id,
2964 p_cle_id => lx_clev_rec.id);
2965
2966 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
2967 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2968 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
2969 raise OKL_API.G_EXCEPTION_ERROR;
2970 End If;
2971
2972 END IF;
2973 -- Bug# 6438785
2974
2975
2976 l_rgp_id := null;
2977 l_rul_id := null;
2978 open l_strm_type_rul_csr;
2979 fetch l_strm_type_rul_csr into l_rgp_id,l_rul_id;
2980 close l_strm_type_rul_csr;
2981
2982 If( l_rgp_id is not null and l_rul_id is not null) Then
2983
2984 update_strmtp_rul(
2985 p_api_version => p_api_version,
2986 p_init_msg_list => p_init_msg_list,
2987 x_return_status => x_return_status,
2988 x_msg_count => x_msg_count,
2989 x_msg_data => x_msg_data,
2990 p_chr_id => p_fee_types_rec.dnz_chr_id,
2991 p_cle_id => p_fee_types_rec.line_id,
2992 p_rgp_id => l_rgp_id,
2993 p_rul_id => l_rul_id
2994 );
2995
2996 -- check return status
2997 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2998 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2999 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3000 raise OKC_API.G_EXCEPTION_ERROR;
3001 End If;
3002
3003 End If;
3004
3005
3006 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3007
3008 EXCEPTION
3009 when OKC_API.G_EXCEPTION_ERROR then
3010 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3011 p_api_name => l_api_name,
3012 p_pkg_name => g_pkg_name,
3013 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3014 x_msg_count => x_msg_count,
3015 x_msg_data => x_msg_data,
3016 p_api_type => g_api_type);
3017
3018 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3019 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3020 p_api_name => l_api_name,
3021 p_pkg_name => g_pkg_name,
3022 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3023 x_msg_count => x_msg_count,
3024 x_msg_data => x_msg_data,
3025 p_api_type => g_api_type);
3026
3027 when OTHERS then
3028 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3029 p_api_name => l_api_name,
3030 p_pkg_name => g_pkg_name,
3031 p_exc_name => 'OTHERS',
3032 x_msg_count => x_msg_count,
3033 x_msg_data => x_msg_data,
3034 p_api_type => g_api_type);
3035
3036 END;
3037
3038 -- Start of comments
3039 --
3040 -- Procedure Name : delete_fee_type
3041 -- Description :
3042 -- Business Rules :
3043 -- Parameters :
3044 -- Version : 1.0
3045 -- End of comments
3046 PROCEDURE delete_fee_type(
3047 p_api_version IN NUMBER,
3048 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3049 x_return_status OUT NOCOPY VARCHAR2,
3050 x_msg_count OUT NOCOPY NUMBER,
3051 x_msg_data OUT NOCOPY VARCHAR2,
3052 p_fee_types_rec IN fee_types_rec_type
3053 ) IS
3054
3055 l_clev_rec okl_okc_migration_pvt.clev_rec_type ;
3056 l_klev_rec okl_kle_pvt.klev_rec_type ;
3057 x_clev_rec okl_okc_migration_pvt.clev_rec_type ;
3058 x_klev_rec okl_kle_pvt.klev_rec_type ;
3059
3060 l_sl_clev_rec okl_okc_migration_pvt.clev_rec_type ;
3061 l_sl_klev_rec okl_kle_pvt.klev_rec_type ;
3062 x_sl_clev_rec okl_okc_migration_pvt.clev_rec_type ;
3063 x_sl_klev_rec okl_kle_pvt.klev_rec_type ;
3064
3065 l_chr_id l_clev_rec.dnz_chr_id%type;
3066 l_line_id l_clev_rec.dnz_chr_id%type;
3067
3068 l_api_name CONSTANT VARCHAR2(30) := 'delete_fee_type';
3069 l_api_version CONSTANT NUMBER := 1.0;
3070 l_fee_type okl_k_lines.fee_type%type := null;
3071 l_rebook_yn varchar2(5) := null;
3072
3073 CURSOR get_fee_purpose_code_csr (p_line_id IN NUMBER) IS
3074 SELECT fee_purpose_code
3075 FROM okl_k_lines
3076 WHERE ID = p_line_id;
3077 l_fee_purpose_code okl_k_lines.fee_purpose_code%type;
3078
3079 CURSOR l_rebook_csr IS
3080 select 'Y'
3081 from OKC_K_HEADERS_B chr
3082 where chr.id = p_fee_types_rec.dnz_chr_id
3083 and chr.orig_system_source_code = 'OKL_REBOOK';
3084
3085 CURSOR c_sub_line_csr( p_line_id IN NUMBER, p_chr_id IN NUMBER) IS
3086 select cle.id
3087 from okc_k_lines_b cle
3088 where cle.dnz_chr_id = p_chr_id
3089 and cle.cle_id = p_line_id;
3090
3091 --Bug# 3877032
3092 --Bug# 6787858: Allow delete of 'GENERAL' fee and fees newly added during rebook
3093 CURSOR l_fee_type_csr IS
3094 select kle.fee_type,
3095 cle.orig_system_id1
3096 from okc_k_lines_b cle,
3097 okl_k_lines kle
3098 where cle.id = kle.id
3099 and cle.dnz_chr_id = p_fee_types_rec.dnz_chr_id
3100 and cle.id = p_fee_types_rec.line_id;
3101
3102 --Bug# 3877032 : cursor to determine line type and to find covered assets if any
3103 cursor l_cov_ast_csr (p_cle_id in number) is
3104 select kle_fee.fee_type,
3105 cim.object1_id1,
3106 cim.dnz_chr_id
3107 from okc_k_items cim,
3108 okc_k_lines_b cleb,
3109 okl_k_lines kle_fee,
3110 okc_k_lines_b cleb_fee,
3111 okc_line_styles_b lseb_fee
3112 where cim.cle_id = cleb.id
3113 and cim.dnz_chr_id = cleb.dnz_chr_id
3114 and cim.jtot_object1_code = 'OKX_COVASST'
3115 and cleb.cle_id = cleb_fee.id
3116 and cleb.dnz_chr_id = cleb_fee.dnz_chr_id
3117 and kle_fee.id = cleb_fee.id
3118 and lseb_fee.id = cleb_fee.lse_id
3119 and lseb_fee.lty_code = 'FEE'
3120 and cleb_fee.id = p_cle_id
3121 --Bug# 6512668: Exclude asset lines in Abandoned status
3122 and cleb.sts_code <> 'ABANDONED';
3123
3124 l_cov_ast_rec l_cov_ast_csr%ROWTYPE;
3125
3126 l_fin_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
3127 l_fin_klev_tbl okl_contract_pub.klev_tbl_type;
3128 lx_fin_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
3129 lx_fin_klev_tbl okl_contract_pub.klev_tbl_type;
3130 i number;
3131 --End Bug# 3877032
3132
3133 --Bug# 6787858
3134 l_orig_system_id1 okc_k_lines_b.orig_system_id1%TYPE;
3135
3136 BEGIN
3137
3138 l_line_id := p_fee_types_rec.line_id;
3139 l_chr_id := p_fee_types_rec.dnz_chr_id;
3140 If okl_context.get_okc_org_id is null then
3141 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
3142 End If;
3143
3144 -- call START_ACTIVITY to create savepoint, check compatibility
3145 -- and initialize message list
3146 x_return_status := OKC_API.START_ACTIVITY(
3147 p_api_name => l_api_name,
3148 p_pkg_name => g_pkg_name,
3149 p_init_msg_list => p_init_msg_list,
3150 l_api_version => l_api_version,
3151 p_api_version => p_api_version,
3152 p_api_type => g_api_type,
3153 x_return_status => x_return_status);
3154
3155 -- check if activity started successfully
3156 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3157 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3158 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3159 raise OKC_API.G_EXCEPTION_ERROR;
3160 End If;
3161
3162 If( p_fee_types_rec.line_id is null or p_fee_types_rec.line_id = OKC_API.G_MISS_NUM) Then
3163 x_return_status := OKC_API.g_ret_sts_error;
3164 OKC_API.SET_MESSAGE( p_app_name => g_app_name
3165 , p_msg_name => 'OKL_REQUIRED_VALUE'
3166 , p_token1 => 'COL_NAME'
3167 , p_token1_value => 'CLE_ID'
3168 );
3169 raise OKC_API.G_EXCEPTION_ERROR;
3170 End If;
3171
3172 If( p_fee_types_rec.dnz_chr_id is null or p_fee_types_rec.dnz_chr_id = OKC_API.G_MISS_NUM) Then
3173 x_return_status := OKC_API.g_ret_sts_error;
3174 OKC_API.SET_MESSAGE( p_app_name => g_app_name
3175 , p_msg_name => 'OKL_REQUIRED_VALUE'
3176 , p_token1 => 'COL_NAME'
3177 , p_token1_value => 'CHR_ID'
3178 );
3179 raise OKC_API.G_EXCEPTION_ERROR;
3180 End If;
3181
3182 --Bug#4552772
3183 open get_fee_purpose_code_csr(l_line_id);
3184 fetch get_fee_purpose_code_csr into l_fee_purpose_code;
3185 close get_fee_purpose_code_csr;
3186
3187 IF(( l_fee_purpose_code is not null) and (l_fee_purpose_code <> OKC_API.G_MISS_CHAR) and (l_fee_purpose_code = 'RVI')) Then
3188 IF (p_fee_types_rec.fee_purpose_code is not null) and (p_fee_types_rec.fee_purpose_code <> 'RVI_DUMMY') THEN
3189 x_return_status := OKC_API.g_ret_sts_error;
3190 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
3191 p_msg_name => 'OKL_LA_RVI_DELETE_NOT_ALLOWED');
3192
3193 RAISE OKC_API.G_EXCEPTION_ERROR;
3194 End If;
3195 END IF;
3196
3197 l_rebook_yn := null;
3198 open l_rebook_csr;
3199 fetch l_rebook_csr into l_rebook_yn;
3200 close l_rebook_csr;
3201
3202 If(l_rebook_yn = 'Y'
3203 and p_fee_types_rec.line_id is not null
3204 and p_fee_types_rec.line_id <> OKC_API.G_MISS_NUM) Then
3205
3206 l_fee_type := null;
3207 l_orig_system_id1 := null;
3208
3209 --Bug# : 3877032
3210 --Bug# 6787858: Allow delete of 'GENERAL' fee and fees newly added during rebook
3211 open l_fee_type_csr;
3212 fetch l_fee_type_csr into l_fee_type, l_orig_system_id1;
3213 close l_fee_type_csr;
3214
3215 --Bug# 6787858: Allow delete of 'GENERAL' fee and fees newly added during rebook
3216 If (l_fee_type = 'GENERAL' OR l_orig_system_id1 IS NULL) Then
3217 NULL;
3218 ELSE
3219 x_return_status := OKC_API.g_ret_sts_error;
3220 OKC_API.SET_MESSAGE( p_app_name => g_app_name
3221 , p_msg_name => 'OKL_FEE_REBK_DEL_ERR' -- seed an error message
3222 );
3223 raise OKC_API.G_EXCEPTION_ERROR;
3224 End If;
3225
3226 l_clev_rec.chr_id := p_fee_types_rec.dnz_chr_id;
3227 l_clev_rec.id := p_fee_types_rec.line_id;
3228 l_klev_rec.id := l_clev_rec.id;
3229 l_clev_rec.sts_code := 'ABANDONED';
3230
3231 okl_contract_pvt.update_contract_line(
3232 p_api_version => p_api_version,
3233 p_init_msg_list => p_init_msg_list,
3234 x_return_status => x_return_status,
3235 x_msg_count => x_msg_count,
3236 x_msg_data => x_msg_data,
3237 p_clev_rec => l_clev_rec,
3238 p_klev_rec => l_klev_rec,
3239 x_clev_rec => x_clev_rec,
3240 x_klev_rec => x_klev_rec);
3241
3242 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3243 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3244 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3245 raise OKC_API.G_EXCEPTION_ERROR;
3246 END IF;
3247
3248 FOR l_sub_line_csr IN c_sub_line_csr(p_line_id => p_fee_types_rec.line_id,p_chr_id => p_fee_types_rec.dnz_chr_id)
3249 LOOP
3250
3251 l_sl_clev_rec.id := l_sub_line_csr.id;
3252 l_sl_clev_rec.sts_code := 'ABANDONED';
3253 l_sl_klev_rec.id := l_sub_line_csr.id;
3254
3255 okl_contract_pvt.update_contract_line(
3256 p_api_version => p_api_version,
3257 p_init_msg_list => p_init_msg_list,
3258 x_return_status => x_return_status,
3259 x_msg_count => x_msg_count,
3260 x_msg_data => x_msg_data,
3261 p_clev_rec => l_sl_clev_rec,
3262 p_klev_rec => l_sl_klev_rec,
3263 x_clev_rec => x_sl_clev_rec,
3264 x_klev_rec => x_sl_klev_rec);
3265
3266 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3267 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3268 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3269 raise OKC_API.G_EXCEPTION_ERROR;
3270 END IF;
3271
3272 END LOOP;
3273
3274 ElsIf( l_rebook_yn is null and p_fee_types_rec.line_id is not null and p_fee_types_rec.line_id <> OKC_API.G_MISS_NUM) Then
3275
3276 --bug# 3877032
3277 i := 0;
3278 For l_cov_Ast_rec in l_cov_ast_csr (p_cle_id => p_fee_types_rec.line_id)
3279 Loop
3280 IF l_cov_ast_rec.fee_type = 'CAPITALIZED' and l_cov_ast_rec.object1_id1 is not NULL then
3281 i := i+1;
3282 l_fin_clev_tbl(i).id := to_number(l_cov_ast_rec.object1_id1);
3283 l_fin_klev_tbl(i).id := to_number(l_cov_ast_rec.object1_id1);
3284 l_fin_clev_tbl(i).dnz_chr_id := l_cov_ast_rec.dnz_chr_id;
3285 End If;
3286 End Loop;
3287 -- Bug# 3877032
3288
3289 okl_contract_pub.delete_contract_line(
3290 p_api_version => p_api_version,
3291 p_init_msg_list => p_init_msg_list,
3292 x_return_status => x_return_status,
3293 x_msg_count => x_msg_count,
3294 x_msg_data => x_msg_data,
3295 p_line_id => l_line_id
3296 );
3297
3298 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3299 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3300 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3301 raise OKC_API.G_EXCEPTION_ERROR;
3302 END IF;
3303
3304 End If;
3305
3306 --Bug# 3877032
3307 If l_fin_klev_tbl.COUNT > 0 then
3308 For i in l_fin_klev_tbl.FIRST..l_fin_klev_tbl.LAST
3309 Loop
3310 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
3311 p_init_msg_list => p_init_msg_list,
3312 x_return_status => x_return_status,
3313 x_msg_count => x_msg_count,
3314 x_msg_data => x_msg_data,
3315 p_formula_name => 'LINE_CAP_AMNT',
3316 p_contract_id => l_fin_clev_tbl(i).dnz_chr_id,
3317 p_line_id => l_fin_clev_tbl(i).id,
3318 x_value => l_fin_klev_tbl(i).capital_amount);
3319 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
3320 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3321 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3322 raise OKC_API.G_EXCEPTION_ERROR;
3323 End If;
3324 End Loop;
3325
3326 okl_contract_pub.update_contract_line(p_api_version => p_api_version,
3327 p_init_msg_list => p_init_msg_list,
3328 x_return_status => x_return_status,
3329 x_msg_count => x_msg_count,
3330 x_msg_data => x_msg_data,
3331 p_clev_tbl => l_fin_clev_tbl,
3332 p_klev_tbl => l_fin_klev_tbl,
3333 x_clev_tbl => lx_fin_clev_tbl,
3334 x_klev_tbl => lx_fin_klev_tbl);
3335
3336 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
3337 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3338 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3339 raise OKC_API.G_EXCEPTION_ERROR;
3340 End If;
3341 End If;
3342 --Bug# 3877032
3343
3344 /*
3345 -- vthiruva, 09/01/2004
3346 -- START, Code change to enable Business Event
3347 */
3348 --raise the business event for remove fee if its a lease contract
3349 IF(OKL_LLA_UTIL_PVT.is_lease_contract(l_chr_id)= OKL_API.G_TRUE)THEN
3350 raise_business_event(p_api_version => p_api_version,
3351 p_init_msg_list => p_init_msg_list,
3352 x_return_status => x_return_status,
3353 x_msg_count => x_msg_count,
3354 x_msg_data => x_msg_data,
3355 p_chr_id => l_chr_id,
3356 p_fee_line_id => l_line_id,
3357 p_event_name => G_WF_EVT_FEE_REMOVED);
3358
3359 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3360 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3361 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3362 RAISE OKL_API.G_EXCEPTION_ERROR;
3363 END IF;
3364 END IF;
3365 /*
3366 -- vthiruva, 09/01/2004
3367 -- END, Code change to enable Business Event
3368 */
3369
3370 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3371
3372 EXCEPTION
3373 when OKC_API.G_EXCEPTION_ERROR then
3374 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3375 p_api_name => l_api_name,
3376 p_pkg_name => g_pkg_name,
3377 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3378 x_msg_count => x_msg_count,
3379 x_msg_data => x_msg_data,
3380 p_api_type => g_api_type);
3381
3382 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3383 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3384 p_api_name => l_api_name,
3385 p_pkg_name => g_pkg_name,
3386 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3387 x_msg_count => x_msg_count,
3388 x_msg_data => x_msg_data,
3389 p_api_type => g_api_type);
3390
3391 when OTHERS then
3392 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3393 p_api_name => l_api_name,
3394 p_pkg_name => g_pkg_name,
3395 p_exc_name => 'OTHERS',
3396 x_msg_count => x_msg_count,
3397 x_msg_data => x_msg_data,
3398 p_api_type => g_api_type);
3399
3400
3401 END;
3402
3403 PROCEDURE process_strmtp_rul(
3404 p_api_version IN NUMBER,
3405 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3406 x_return_status OUT NOCOPY VARCHAR2,
3407 x_msg_count OUT NOCOPY NUMBER,
3408 x_msg_data OUT NOCOPY VARCHAR2,
3409 p_chr_id IN NUMBER,
3410 p_cle_id IN NUMBER,
3411 p_object1_id1 IN VARCHAR2
3412 ) IS
3413
3414 lp_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
3415 lx_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
3416 lp_lastrm_rulv_rec Okl_Rule_Pub.rulv_rec_type;
3417 lx_lastrm_rulv_rec Okl_Rule_Pub.rulv_rec_type;
3418
3419 l_chr_id okc_k_headers_b.id%type;
3420 l_rul_id okc_rules_v.id%type := null;
3421 l_rgp_id okc_rules_v.id%type := null;
3422 l_lapsth_rgp_id okc_rules_v.id%type := null;
3423
3424 l_api_name CONSTANT VARCHAR2(30) := 'process_strmtp_rul';
3425 l_api_version CONSTANT NUMBER := 1.0;
3426
3427 CURSOR l_lapsth_rgp_csr IS
3428 select id
3429 from okc_rule_groups_v
3430 where cle_id = p_cle_id
3431 and chr_id is null
3432 and dnz_chr_id = p_chr_id
3433 and rgd_code = 'LAPSTH';
3434
3435 CURSOR l_strm_type_rul_csr IS
3436 select rgp.id,
3437 rul.id
3438 from okc_rules_v rul,
3439 okc_rule_groups_v rgp
3440 where rgp.id = rul.rgp_id
3441 and rgp.rgd_code = 'LAPSTH'
3442 and rul.rule_information_category = 'LASTRM'
3443 and rgp.cle_id = p_cle_id
3444 and rgp.chr_id is null
3445 and rul.dnz_chr_id = p_chr_id
3446 and rgp.dnz_chr_id = p_chr_id;
3447
3448 BEGIN
3449
3450 l_chr_id := p_chr_id;
3451 If okl_context.get_okc_org_id is null then
3452 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
3453 End If;
3454
3455 -- call START_ACTIVITY to create savepoint, check compatibility
3456 -- and initialize message list
3457 x_return_status := OKC_API.START_ACTIVITY(
3458 p_api_name => l_api_name,
3459 p_pkg_name => g_pkg_name,
3460 p_init_msg_list => p_init_msg_list,
3461 l_api_version => l_api_version,
3462 p_api_version => p_api_version,
3463 p_api_type => g_api_type,
3464 x_return_status => x_return_status);
3465
3466 -- check if activity started successfully
3467 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3468 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3469 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3470 raise OKC_API.G_EXCEPTION_ERROR;
3471 End If;
3472
3473 l_rgp_id := null;
3474 l_rul_id := null;
3475 open l_strm_type_rul_csr;
3476 fetch l_strm_type_rul_csr into l_rgp_id,l_rul_id;
3477 close l_strm_type_rul_csr;
3478
3479 l_lapsth_rgp_id := null;
3480 open l_lapsth_rgp_csr;
3481 fetch l_lapsth_rgp_csr into l_lapsth_rgp_id;
3482 close l_lapsth_rgp_csr;
3483
3484 If (l_rgp_id is not null and l_rul_id is not null and p_object1_id1 is not null) Then
3485
3486 lp_lapsth_rgpv_rec.id := l_rgp_id;
3487 lp_lapsth_rgpv_rec.rgd_code := 'LAPSTH';
3488 lp_lapsth_rgpv_rec.dnz_chr_id := p_chr_id;
3489 lp_lapsth_rgpv_rec.chr_id := null;
3490 lp_lapsth_rgpv_rec.cle_id := p_cle_id;
3491 lp_lapsth_rgpv_rec.rgp_type := 'KRG';
3492
3493
3494 OKL_RULE_PUB.update_rule_group(
3495 p_api_version => p_api_version,
3496 p_init_msg_list => p_init_msg_list,
3497 x_return_status => x_return_status,
3498 x_msg_count => x_msg_count,
3499 x_msg_data => x_msg_data,
3500 p_rgpv_rec => lp_lapsth_rgpv_rec,
3501 x_rgpv_rec => lx_lapsth_rgpv_rec);
3502
3503 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3504 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3505 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3506 raise OKC_API.G_EXCEPTION_ERROR;
3507 End If;
3508
3509 lp_lastrm_rulv_rec.id := l_rul_id;
3510 lp_lastrm_rulv_rec.rgp_id := lx_lapsth_rgpv_rec.id;
3511 lp_lastrm_rulv_rec.rule_information_category := 'LASTRM';
3512 lp_lastrm_rulv_rec.dnz_chr_id := p_chr_id;
3513 lp_lastrm_rulv_rec.object1_id1 := p_object1_id1;
3514 lp_lastrm_rulv_rec.object1_id2 := '#';
3515 lp_lastrm_rulv_rec.jtot_object1_code := 'OKL_STRMTYP';
3516 lp_lastrm_rulv_rec.WARN_YN := 'N';
3517 lp_lastrm_rulv_rec.STD_TEMPLATE_YN := 'N';
3518
3519 OKL_RULE_PUB.update_rule(
3520 p_api_version => p_api_version,
3521 p_init_msg_list => p_init_msg_list,
3522 x_return_status => x_return_status,
3523 x_msg_count => x_msg_count,
3524 x_msg_data => x_msg_data,
3525 p_rulv_rec => lp_lastrm_rulv_rec,
3526 x_rulv_rec => lx_lastrm_rulv_rec);
3527
3528 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3529 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3530 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3531 raise OKC_API.G_EXCEPTION_ERROR;
3532 End If;
3533
3534 ElsIf (l_rgp_id is null and l_rul_id is null and p_object1_id1 is not null) Then
3535
3536 If( l_lapsth_rgp_id is null) Then
3537
3538 lp_lapsth_rgpv_rec.id := null;
3539 lp_lapsth_rgpv_rec.rgd_code := 'LAPSTH';
3540 lp_lapsth_rgpv_rec.dnz_chr_id := p_chr_id;
3541 lp_lapsth_rgpv_rec.chr_id := null;
3542 lp_lapsth_rgpv_rec.cle_id := p_cle_id;
3543 lp_lapsth_rgpv_rec.rgp_type := 'KRG';
3544
3545 OKL_RULE_PUB.create_rule_group(
3546 p_api_version => p_api_version,
3547 p_init_msg_list => p_init_msg_list,
3548 x_return_status => x_return_status,
3549 x_msg_count => x_msg_count,
3550 x_msg_data => x_msg_data,
3551 p_rgpv_rec => lp_lapsth_rgpv_rec,
3552 x_rgpv_rec => lx_lapsth_rgpv_rec);
3553
3554 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3555 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3556 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3557 raise OKC_API.G_EXCEPTION_ERROR;
3558 End If;
3559
3560 End If;
3561
3562 lp_lastrm_rulv_rec.id := null;
3563
3564 If(l_lapsth_rgp_id is not null) Then
3565 lp_lastrm_rulv_rec.rgp_id := l_lapsth_rgp_id;
3566 Else
3567 lp_lastrm_rulv_rec.rgp_id := lx_lapsth_rgpv_rec.id;
3568 End If;
3569
3570 lp_lastrm_rulv_rec.rule_information_category := 'LASTRM';
3571 lp_lastrm_rulv_rec.dnz_chr_id := p_chr_id;
3572 lp_lastrm_rulv_rec.object1_id1 := p_object1_id1;
3573 lp_lastrm_rulv_rec.object1_id2 := '#';
3574 lp_lastrm_rulv_rec.jtot_object1_code := 'OKL_STRMTYP';
3575 lp_lastrm_rulv_rec.WARN_YN := 'N';
3576 lp_lastrm_rulv_rec.STD_TEMPLATE_YN := 'N';
3577
3578 OKL_RULE_PUB.create_rule(
3579 p_api_version => p_api_version,
3580 p_init_msg_list => p_init_msg_list,
3581 x_return_status => x_return_status,
3582 x_msg_count => x_msg_count,
3583 x_msg_data => x_msg_data,
3584 p_rulv_rec => lp_lastrm_rulv_rec,
3585 x_rulv_rec => lx_lastrm_rulv_rec);
3586
3587 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3588 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3589 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3590 raise OKC_API.G_EXCEPTION_ERROR;
3591 End If;
3592
3593 ElsIf (l_rgp_id is not null and l_rul_id is not null and p_object1_id1 is null) Then
3594
3595 -- call the package to create rule
3596 lp_lastrm_rulv_rec.id := l_rul_id;
3597
3598 OKL_RULE_PUB.delete_rule(
3599 p_api_version => p_api_version,
3600 p_init_msg_list => p_init_msg_list,
3601 x_return_status => x_return_status,
3602 x_msg_count => x_msg_count,
3603 x_msg_data => x_msg_data,
3604 p_rulv_rec => lp_lastrm_rulv_rec);
3605
3606 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3607 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3608 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3609 raise OKC_API.G_EXCEPTION_ERROR;
3610 End If;
3611 /*
3612 lp_lapsth_rgpv_rec.id := l_rgp_id;
3613
3614 OKL_RULE_PUB.delete_rule_group(
3615 p_api_version => p_api_version,
3616 p_init_msg_list => p_init_msg_list,
3617 x_return_status => x_return_status,
3618 x_msg_count => x_msg_count,
3619 x_msg_data => x_msg_data,
3620 p_rgpv_rec => lp_lapsth_rgpv_rec);
3621
3622 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3623 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3624 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3625 raise OKC_API.G_EXCEPTION_ERROR;
3626 End If;
3627 */
3628
3629
3630 End If;
3631
3632
3633 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3634
3635 EXCEPTION
3636 when OKC_API.G_EXCEPTION_ERROR then
3637 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3638 p_api_name => l_api_name,
3639 p_pkg_name => g_pkg_name,
3640 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3641 x_msg_count => x_msg_count,
3642 x_msg_data => x_msg_data,
3643 p_api_type => g_api_type);
3644
3645 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3646 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3647 p_api_name => l_api_name,
3648 p_pkg_name => g_pkg_name,
3649 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3650 x_msg_count => x_msg_count,
3651 x_msg_data => x_msg_data,
3652 p_api_type => g_api_type);
3653
3654 when OTHERS then
3655 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3656 p_api_name => l_api_name,
3657 p_pkg_name => g_pkg_name,
3658 p_exc_name => 'OTHERS',
3659 x_msg_count => x_msg_count,
3660 x_msg_data => x_msg_data,
3661 p_api_type => g_api_type);
3662
3663
3664 END;
3665
3666 PROCEDURE create_strmtp_rul(
3667 p_api_version IN NUMBER,
3668 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3669 x_return_status OUT NOCOPY VARCHAR2,
3670 x_msg_count OUT NOCOPY NUMBER,
3671 x_msg_data OUT NOCOPY VARCHAR2,
3672 p_chr_id IN NUMBER,
3673 p_cle_id IN NUMBER
3674 ) IS
3675
3676 lp_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
3677 lx_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
3678 lp_lastrm_rulv_rec Okl_Rule_Pub.rulv_rec_type;
3679 lx_lastrm_rulv_rec Okl_Rule_Pub.rulv_rec_type;
3680
3681 l_chr_id okc_k_headers_b.id%type;
3682 l_object1_id1 okc_k_items_v.object1_id1%type := null;
3683 l_object1_id2 okc_k_items_v.object1_id2%type := null;
3684
3685 l_api_name CONSTANT VARCHAR2(30) := 'create_strmtp_rul';
3686 l_api_version CONSTANT NUMBER := 1.0;
3687
3688 CURSOR l_strm_type_item_csr IS
3689 select object1_id1,object1_id2
3690 from okc_k_items_v
3691 where cle_id = p_cle_id
3692 and dnz_chr_id = p_chr_id;
3693
3694 CURSOR l_strm_type_rul_csr IS
3695 select rul.object1_id1,
3696 rul.object1_id2
3697 from okc_rules_v rul,
3698 okc_rule_groups_v rgp
3699 where rgp.id = rul.rgp_id
3700 and rgp.rgd_code = 'LAPSTH'
3701 and rul.rule_information_category = 'LASTRM'
3702 and rgp.cle_id = p_cle_id
3703 and rgp.chr_id is null
3704 and rul.dnz_chr_id = p_chr_id
3705 and rgp.dnz_chr_id = p_chr_id;
3706
3707
3708 BEGIN
3709
3710 l_chr_id := p_chr_id;
3711 If okl_context.get_okc_org_id is null then
3712 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
3713 End If;
3714
3715 -- call START_ACTIVITY to create savepoint, check compatibility
3716 -- and initialize message list
3717 x_return_status := OKC_API.START_ACTIVITY(
3718 p_api_name => l_api_name,
3719 p_pkg_name => g_pkg_name,
3720 p_init_msg_list => p_init_msg_list,
3721 l_api_version => l_api_version,
3722 p_api_version => p_api_version,
3723 p_api_type => g_api_type,
3724 x_return_status => x_return_status);
3725
3726 -- check if activity started successfully
3727 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3728 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3729 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3730 raise OKC_API.G_EXCEPTION_ERROR;
3731 End If;
3732
3733 l_object1_id1 := null;
3734 l_object1_id2 := null;
3735
3736 open l_strm_type_rul_csr;
3737 fetch l_strm_type_rul_csr into l_object1_id1,l_object1_id2;
3738 close l_strm_type_rul_csr;
3739
3740 If (l_object1_id1 is not null and l_object1_id2 is not null) Then
3741 return;
3742 End If;
3743
3744 l_object1_id1 := null;
3745 l_object1_id2 := null;
3746
3747 open l_strm_type_item_csr;
3748 fetch l_strm_type_item_csr into l_object1_id1,l_object1_id2;
3749 close l_strm_type_item_csr;
3750
3751 If( l_object1_id1 is null or l_object1_id2 is null) Then
3752 -- Not a valid record, Item object1_id1 not found
3753 null;
3754 End If;
3755
3756 -- call the package to create rule
3757
3758 lp_lapsth_rgpv_rec.id := null;
3759 lp_lapsth_rgpv_rec.rgd_code := 'LAPSTH';
3760 lp_lapsth_rgpv_rec.dnz_chr_id := p_chr_id;
3761 lp_lapsth_rgpv_rec.chr_id := null;
3762 lp_lapsth_rgpv_rec.cle_id := p_cle_id;
3763 lp_lapsth_rgpv_rec.rgp_type := 'KRG';
3764
3765 OKL_RULE_PUB.create_rule_group(
3766 p_api_version => p_api_version,
3767 p_init_msg_list => p_init_msg_list,
3768 x_return_status => x_return_status,
3769 x_msg_count => x_msg_count,
3770 x_msg_data => x_msg_data,
3771 p_rgpv_rec => lp_lapsth_rgpv_rec,
3772 x_rgpv_rec => lx_lapsth_rgpv_rec);
3773
3774 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3775 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3776 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3777 raise OKC_API.G_EXCEPTION_ERROR;
3778 End If;
3779
3780 lp_lastrm_rulv_rec.id := null;
3781 lp_lastrm_rulv_rec.rgp_id := lx_lapsth_rgpv_rec.id;
3782 lp_lastrm_rulv_rec.rule_information_category := 'LASTRM';
3783 lp_lastrm_rulv_rec.dnz_chr_id := p_chr_id;
3784 lp_lastrm_rulv_rec.object1_id1 := l_object1_id1;
3785 lp_lastrm_rulv_rec.object1_id2 := l_object1_id2;
3786 lp_lastrm_rulv_rec.jtot_object1_code := 'OKL_STRMTYP';
3787 lp_lastrm_rulv_rec.WARN_YN := 'N';
3788 lp_lastrm_rulv_rec.STD_TEMPLATE_YN := 'N';
3789
3790 OKL_RULE_PUB.create_rule(
3791 p_api_version => p_api_version,
3792 p_init_msg_list => p_init_msg_list,
3793 x_return_status => x_return_status,
3794 x_msg_count => x_msg_count,
3795 x_msg_data => x_msg_data,
3796 p_rulv_rec => lp_lastrm_rulv_rec,
3797 x_rulv_rec => lx_lastrm_rulv_rec);
3798
3799 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3800 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3801 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3802 raise OKC_API.G_EXCEPTION_ERROR;
3803 End If;
3804
3805
3806 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3807
3808 EXCEPTION
3809 when OKC_API.G_EXCEPTION_ERROR then
3810 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3811 p_api_name => l_api_name,
3812 p_pkg_name => g_pkg_name,
3813 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3814 x_msg_count => x_msg_count,
3815 x_msg_data => x_msg_data,
3816 p_api_type => g_api_type);
3817
3818 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3819 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3820 p_api_name => l_api_name,
3821 p_pkg_name => g_pkg_name,
3822 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3823 x_msg_count => x_msg_count,
3824 x_msg_data => x_msg_data,
3825 p_api_type => g_api_type);
3826
3827 when OTHERS then
3828 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3829 p_api_name => l_api_name,
3830 p_pkg_name => g_pkg_name,
3831 p_exc_name => 'OTHERS',
3832 x_msg_count => x_msg_count,
3833 x_msg_data => x_msg_data,
3834 p_api_type => g_api_type);
3835
3836
3837 END;
3838
3839 PROCEDURE update_strmtp_rul(
3840 p_api_version IN NUMBER,
3841 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3842 x_return_status OUT NOCOPY VARCHAR2,
3843 x_msg_count OUT NOCOPY NUMBER,
3844 x_msg_data OUT NOCOPY VARCHAR2,
3845 p_chr_id IN NUMBER,
3846 p_cle_id IN NUMBER,
3847 p_rgp_id IN NUMBER,
3848 p_rul_id IN NUMBER
3849
3850 ) IS
3851
3852 lp_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
3853 lx_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
3854 lp_lastrm_rulv_rec Okl_Rule_Pub.rulv_rec_type;
3855 lx_lastrm_rulv_rec Okl_Rule_Pub.rulv_rec_type;
3856
3857 l_chr_id okc_k_headers_b.id%type;
3858 l_rgp_id okc_k_headers_b.id%type;
3859 l_object1_id1 okc_k_items_v.object1_id1%type := null;
3860 l_object1_id2 okc_k_items_v.object1_id2%type := null;
3861
3862 l_api_name CONSTANT VARCHAR2(30) := 'update_strmtp_rul';
3863 l_api_version CONSTANT NUMBER := 1.0;
3864
3865 CURSOR l_strm_type_item_csr IS
3866 select object1_id1,object1_id2
3867 from okc_k_items_v
3868 where cle_id = p_cle_id
3869 and dnz_chr_id = p_chr_id;
3870
3871
3872 BEGIN
3873
3874 l_chr_id := p_chr_id;
3875 If okl_context.get_okc_org_id is null then
3876 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
3877 End If;
3878
3879 -- call START_ACTIVITY to create savepoint, check compatibility
3880 -- and initialize message list
3881 x_return_status := OKC_API.START_ACTIVITY(
3882 p_api_name => l_api_name,
3883 p_pkg_name => g_pkg_name,
3884 p_init_msg_list => p_init_msg_list,
3885 l_api_version => l_api_version,
3886 p_api_version => p_api_version,
3887 p_api_type => g_api_type,
3888 x_return_status => x_return_status);
3889
3890 -- check if activity started successfully
3891 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3892 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3893 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3894 raise OKC_API.G_EXCEPTION_ERROR;
3895 End If;
3896
3897 l_object1_id1 := null;
3898 l_object1_id2 := null;
3899
3900 open l_strm_type_item_csr;
3901 fetch l_strm_type_item_csr into l_object1_id1,l_object1_id2;
3902 close l_strm_type_item_csr;
3903
3904 If( l_object1_id1 is null or l_object1_id2 is null) Then
3905 -- Not a valid record, Item object1_id1 not found
3906 null;
3907 End If;
3908
3909 -- call the package to create rule
3910 lp_lastrm_rulv_rec.id := p_rul_id;
3911 lp_lastrm_rulv_rec.rgp_id := p_rgp_id;
3912 lp_lastrm_rulv_rec.rule_information_category := 'LASTRM';
3913 lp_lastrm_rulv_rec.dnz_chr_id := p_chr_id;
3914 lp_lastrm_rulv_rec.object1_id1 := l_object1_id1;
3915 lp_lastrm_rulv_rec.object1_id2 := l_object1_id2;
3916 lp_lastrm_rulv_rec.jtot_object1_code := 'OKL_STRMTYP';
3917 lp_lastrm_rulv_rec.warn_yn := 'N';
3918 lp_lastrm_rulv_rec.std_template_yn := 'N';
3919
3920 OKL_RULE_PUB.update_rule(
3921 p_api_version => p_api_version,
3922 p_init_msg_list => p_init_msg_list,
3923 x_return_status => x_return_status,
3924 x_msg_count => x_msg_count,
3925 x_msg_data => x_msg_data,
3926 p_rulv_rec => lp_lastrm_rulv_rec,
3927 x_rulv_rec => lx_lastrm_rulv_rec);
3928
3929 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3930 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3931 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3932 raise OKC_API.G_EXCEPTION_ERROR;
3933 End If;
3934
3935
3936 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3937
3938 EXCEPTION
3939 when OKC_API.G_EXCEPTION_ERROR then
3940 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3941 p_api_name => l_api_name,
3942 p_pkg_name => g_pkg_name,
3943 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3944 x_msg_count => x_msg_count,
3945 x_msg_data => x_msg_data,
3946 p_api_type => g_api_type);
3947
3948 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3949 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3950 p_api_name => l_api_name,
3951 p_pkg_name => g_pkg_name,
3952 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3953 x_msg_count => x_msg_count,
3954 x_msg_data => x_msg_data,
3955 p_api_type => g_api_type);
3956
3957 when OTHERS then
3958 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3959 p_api_name => l_api_name,
3960 p_pkg_name => g_pkg_name,
3961 p_exc_name => 'OTHERS',
3962 x_msg_count => x_msg_count,
3963 x_msg_data => x_msg_data,
3964 p_api_type => g_api_type);
3965
3966
3967 END;
3968
3969 ----------------------------------------------------------------------------
3970 --start of comments
3971 --API Name : validate_rollover_feeLine
3972 --Description : API called to validate the rollover quote on a contract.
3973 -- Check if the Rollover fee amount is equal to Rollover
3974 -- qupte amount.
3975 --Parameters : IN - p_chr_id - Contract Number
3976 -- p_qte_id - Rollover Quote Number
3977 -- OUT - x_return_status - Return Status
3978 --History : 16-Aug-2004 Manu Created
3979 --
3980 --
3981 --end of comments
3982 -----------------------------------------------------------------------------
3983
3984 PROCEDURE validate_rollover_feeLine(
3985 p_api_version IN NUMBER,
3986 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3987 x_return_status OUT NOCOPY VARCHAR2,
3988 x_msg_count OUT NOCOPY NUMBER,
3989 x_msg_data OUT NOCOPY VARCHAR2,
3990 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
3991 p_qte_id IN OKL_K_LINES.QTE_ID%TYPE,
3992 p_for_qa_check IN BOOLEAN DEFAULT FALSE) IS
3993
3994 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_ROLLOVER_FEELINE';
3995 l_api_version CONSTANT NUMBER := 1.0;
3996
3997 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3998
3999 l_not_found BOOLEAN := FALSE;
4000 l_amt NUMBER;
4001 l_found VARCHAR2(1);
4002 l_k_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
4003 l_q_num OKL_TRX_QUOTES_B.QUOTE_NUMBER%TYPE;
4004 l_fee_name OKC_K_LINES_V.NAME%TYPE;
4005
4006 l_rebook_yn VARCHAR2(1) := null;
4007 l_ln_orig_sys_id1 okc_k_lines_b.orig_system_id1%type := null;
4008 l_mass_rebook_cnt NUMBER := null;
4009 l_do_validation VARCHAR2(1);
4010
4011 /* Cursor to get the contract number and quote number. */
4012
4013 CURSOR l_con_qte_csr ( chrID OKC_K_HEADERS_B.ID%TYPE,
4014 qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4015 SELECT khr.contract_number, qte.quote_number
4016 FROM okc_k_headers_v khr,okl_trx_quotes_b qte
4017 WHERE khr.id = chrID
4018 AND qte.id = qteID;
4019
4020 /* Cursor to get the Fee Name. */
4021
4022 CURSOR l_fee_name_csr ( chrID OKC_K_HEADERS_B.ID%TYPE,
4023 qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4024 SELECT cle.name
4025 FROM okc_k_headers_b khr,
4026 okl_k_lines kle,
4027 okc_k_lines_v cle,
4028 okl_trx_quotes_b qte
4029 WHERE cle.id = kle.id
4030 AND khr.id = chrID
4031 AND khr.id = cle.dnz_chr_id
4032 AND qte.id = qteID
4033 AND kle.qte_id = qte.id;
4034
4035 /* Cursor to check if the Customer Account on the current Contract
4036 and the contract on the quote match, if not throw an error. */
4037
4038 CURSOR l_cust_accnt_csr ( chrID OKC_K_HEADERS_B.ID%TYPE,
4039 qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4040 SELECT 1 FROM okc_k_headers_b
4041 WHERE id = chrID
4042 AND cust_acct_id = (SELECT khr.cust_acct_id FROM okc_k_headers_b khr,okl_trx_quotes_b qte
4043 WHERE khr.id = qte.khr_id
4044 AND qte.id = qteID);
4045
4046 /* Cursor to check if the Currency Code on the current Contract
4047 and the contract on the quote match, if not throw an error. */
4048
4049 CURSOR l_curr_code_csr ( chrID OKC_K_HEADERS_B.ID%TYPE,
4050 qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4051 SELECT 1 FROM okc_k_headers_b
4052 WHERE id = chrID
4053 AND currency_code = (SELECT khr.currency_code FROM okc_k_headers_b khr,okl_trx_quotes_b qte
4054 WHERE khr.id = qte.khr_id
4055 AND qte.id = qteID);
4056
4057 /* Cursor to check if the Quote status is Approved, if not throw an error. */
4058
4059 CURSOR l_qts_code_csr ( qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4060 SELECT 1 FROM okl_trx_quotes_b
4061 WHERE id = qteID
4062 AND qst_code = 'APPROVED';
4063
4064 /* Cursor to check if the Quote Consolidate flag is set to N, if not throw an error. */
4065
4066 CURSOR l_con_yn_csr ( qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4067 SELECT 1 FROM okl_trx_quotes_b
4068 WHERE id = qteID
4069 AND consolidated_yn = 'N';
4070
4071 /* Cursor to check if the Quote Type is either TER_ROLL_PURCHASE or
4072 TER_ROLL_WO_PURCHASE, if not throw an error. */
4073
4074 CURSOR l_qte_typ_csr ( qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4075 SELECT 1 FROM okl_trx_quotes_b
4076 WHERE id = qteID
4077 AND qtp_code IN ('TER_ROLL_PURCHASE' , 'TER_ROLL_WO_PURCHASE');
4078
4079 /* Cursor to check if the Rollover Fee Start date is between Quote
4080 effective dates, if not throw an error. */
4081
4082 CURSOR l_rq_fee_check_csr ( chrID OKC_K_HEADERS_B.ID%TYPE,
4083 qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4084 SELECT 1
4085 FROM okc_k_headers_b khr,
4086 okl_k_lines kle,
4087 okc_k_lines_b cle,
4088 okl_trx_quotes_b qte
4089 WHERE cle.id = kle.id
4090 AND khr.id = chrID
4091 AND khr.id = cle.dnz_chr_id
4092 AND qte.id = qteID
4093 AND kle.qte_id = qte.id
4094 AND trunc(qte.date_effective_from) <= cle.start_date
4095 AND nvl(trunc(qte.date_effective_to), cle.start_date) >= cle.start_date
4096 AND NOT EXISTS (
4097 SELECT 'Y'
4098 FROM okc_statuses_v okcsts
4099 WHERE okcsts.code = cle.sts_code
4100 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'))
4101 AND NOT EXISTS (
4102 SELECT 'Y'
4103 FROM okc_k_headers_b khr1
4104 WHERE khr1.id = chrID
4105 AND khr1.orig_system_source_code IN ('OKL_RELEASE'));
4106
4107 /* Cursor to check if the Rollover Quote Amount is equal to
4108 Rollover Fee line amount on the contract, if not throw an error. */
4109
4110 -- AKJAIN fixed bug 4198968
4111 CURSOR l_rq_amt_check_csr ( chrID OKC_K_HEADERS_B.ID%TYPE,
4112 qteID OKL_K_LINES.QTE_ID%TYPE ) IS
4113 (SELECT SUM(tql.amount)
4114 FROM okl_trx_quotes_b qte, okl_txl_quote_lines_b tql
4115 WHERE qte.id = qteID
4116 AND tql.qte_id= qte.id
4117 AND tql.qlt_code not in ('AMCFIA', 'AMCTAX', 'AMYOUB', 'BILL_ADJST'))
4118 INTERSECT
4119 (SELECT SUM(KLE1.amount) FROM okc_k_lines_b cleb, okl_k_lines kle1
4120 WHERE cleb.dnz_chr_id = chrID
4121 AND kle1.ID = cleb.ID
4122 AND kle1.fee_type = 'ROLLOVER'
4123 AND kle1.qte_id = qteID
4124 AND NOT EXISTS (
4125 SELECT 'Y'
4126 FROM okc_statuses_v okcsts
4127 WHERE okcsts.code = cleb.sts_code
4128 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'))
4129 /* Added to exclude this check for Re-lease contracts. */
4130 AND NOT EXISTS (
4131 SELECT 'Y'
4132 FROM okc_k_headers_b khr1
4133 WHERE khr1.id = chrID
4134 AND khr1.orig_system_source_code IN ('OKL_RELEASE')));
4135
4136 /* Cursor to check if it is a Re-book contract.
4137 If yes, ignore approval step else continue all the checks */
4138 CURSOR l_rebook_chk_csr ( p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
4139 SELECT 'Y'
4140 FROM okc_k_headers_b
4141 WHERE orig_system_source_code = 'OKL_REBOOK'
4142 AND id = p_chr_id;
4143
4144 /* Cursor to check if orig_system_id1 exists for a re-book contract
4145 If yes, ignore approval step else continue all the checks */
4146 CURSOR l_orig_sys_id1_csr ( p_chr_id OKC_K_HEADERS_B.ID%TYPE, p_qte_id OKL_K_LINES.QTE_ID%TYPE ) IS
4147 SELECT orig_system_id1
4148 FROM okc_k_lines_b cle,
4149 okl_k_lines kle
4150 WHERE cle.id = kle.id
4151 AND dnz_chr_id = p_chr_id
4152 AND kle.qte_id = p_qte_id;
4153
4154 /* Check if it a mass re-book contract */
4155 CURSOR l_mass_rebook_csr ( p_chr_id OKC_K_HEADERS_B.ID%TYPE ) IS
4156 SELECT COUNT(1)
4157 FROM okl_rbk_selected_contract
4158 WHERE khr_id = p_chr_id
4159 AND NVL(status,'NEW') = 'UNDER REVISION';
4160
4161
4162
4163 BEGIN
4164
4165 IF (NOT p_for_qa_check) THEN
4166 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4167 END IF;
4168 /*
4169 x_return_status := OKL_API.START_ACTIVITY(
4170 p_api_name => l_api_name,
4171 p_pkg_name => g_pkg_name,
4172 p_init_msg_list => p_init_msg_list,
4173 l_api_version => l_api_version,
4174 p_api_version => p_api_version,
4175 p_api_type => G_API_TYPE,
4176 x_return_status => x_return_status);
4177
4178 -- check if activity started successfully
4179 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4180 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4181 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4182 raise OKL_API.G_EXCEPTION_ERROR;
4183 END IF;
4184 */
4185
4186 /* check if it is a mass rebook contract */
4187 l_mass_rebook_cnt := null;
4188 OPEN l_mass_rebook_csr ( p_chr_id );
4189 FETCH l_mass_rebook_csr INTO l_mass_rebook_cnt;
4190 CLOSE l_mass_rebook_csr;
4191
4192 /* find if it is a re-book contract */
4193
4194 l_rebook_yn := null;
4195 OPEN l_rebook_chk_csr ( p_chr_id );
4196 FETCH l_rebook_chk_csr INTO l_rebook_yn;
4197 CLOSE l_rebook_chk_csr;
4198
4199 /* find if it is a copied line */
4200
4201 l_ln_orig_sys_id1 := null;
4202 OPEN l_orig_sys_id1_csr ( p_chr_id, p_qte_id );
4203 FETCH l_orig_sys_id1_csr INTO l_ln_orig_sys_id1;
4204 CLOSE l_orig_sys_id1_csr;
4205
4206
4207 /* Get Contract Number and Quote Number. */
4208
4209 OPEN l_con_qte_csr ( p_chr_id, p_qte_id );
4210 FETCH l_con_qte_csr INTO l_k_num, l_q_num;
4211 CLOSE l_con_qte_csr;
4212
4213 /* Get FeeName. */
4214
4215 OPEN l_fee_name_csr ( p_chr_id, p_qte_id );
4216 FETCH l_fee_name_csr INTO l_fee_name;
4217 CLOSE l_fee_name_csr;
4218
4219 /* Check if the Customer Account on the current Contract
4220 and the contract on the quote match, if not throw an error. */
4221
4222 OPEN l_cust_accnt_csr ( p_chr_id, p_qte_id );
4223 FETCH l_cust_accnt_csr INTO l_found;
4224 l_not_found := l_cust_accnt_csr%NOTFOUND;
4225 CLOSE l_cust_accnt_csr;
4226
4227 IF( l_not_found ) THEN
4228 x_return_status := OKL_API.G_RET_STS_ERROR;
4229 l_not_found := NULL;
4230 l_found := NULL;
4231 OKL_API.set_message(
4232 p_app_name => G_APP_NAME,
4233 p_msg_name => 'OKL_LLA_RQ_CUST_NO_MATCH',
4234 p_token1 => 'CONTRACT_NUMBER',
4235 p_token1_value => l_k_num,
4236 p_token2 => 'QUOTE_NUMBER',
4237 p_token2_value => l_q_num,
4238 p_token3 => 'FEE_LINE',
4239 p_token3_value => l_fee_name);
4240
4241 IF (NOT p_for_qa_check) THEN
4242 RAISE OKL_API.G_EXCEPTION_ERROR;
4243 END IF;
4244 END IF;
4245
4246 /* Check if the Currency Code on the current Contract
4247 and the contract on the quote match, if not throw an error. */
4248
4249 OPEN l_curr_code_csr ( p_chr_id, p_qte_id );
4250 FETCH l_curr_code_csr INTO l_found;
4251 l_not_found := l_curr_code_csr%NOTFOUND;
4252 CLOSE l_curr_code_csr;
4253
4254 IF( l_not_found ) THEN
4255 x_return_status := OKL_API.G_RET_STS_ERROR;
4256 l_not_found := NULL;
4257 l_found := NULL;
4258 OKL_API.set_message(
4259 p_app_name => G_APP_NAME,
4260 p_msg_name => 'OKL_LLA_RQ_CURR_NO_MATCH',
4261 p_token1 => 'CONTRACT_NUMBER',
4262 p_token1_value => l_k_num,
4263 p_token2 => 'QUOTE_NUMBER',
4264 p_token2_value => l_q_num,
4265 p_token3 => 'FEE_LINE',
4266 p_token3_value => l_fee_name);
4267
4268 IF (NOT p_for_qa_check) THEN
4269 RAISE OKL_API.G_EXCEPTION_ERROR;
4270 END IF;
4271 END IF;
4272
4273
4274 IF l_rebook_yn is not null THEN
4275 IF l_ln_orig_sys_id1 is not null THEN
4276 l_do_validation := 'N';
4277 ELSE
4278 l_do_validation := 'Y';
4279 END IF;
4280 ELSIF (l_mass_rebook_cnt > 0) THEN
4281 l_do_validation := 'N';
4282 ELSE
4283 l_do_validation := 'Y';
4284 END IF;
4285
4286 /* Check if quote status is Approved, if not throw an error. */
4287
4288 IF (l_do_validation = 'Y') THEN
4289
4290 OPEN l_qts_code_csr ( p_qte_id );
4291 FETCH l_qts_code_csr INTO l_found;
4292 l_not_found := l_qts_code_csr%NOTFOUND;
4293 CLOSE l_qts_code_csr;
4294
4295 IF( l_not_found ) THEN
4296 x_return_status := OKL_API.G_RET_STS_ERROR;
4297 l_not_found := NULL;
4298 l_found := NULL;
4299 OKL_API.set_message(
4300 p_app_name => G_APP_NAME,
4301 p_msg_name => 'OKL_LLA_RQ_STS_NOT_APPROVED',
4302 p_token1 => 'QUOTE_NUMBER',
4303 p_token1_value => l_q_num,
4304 p_token2 => 'FEE_LINE',
4305 p_token2_value => l_fee_name);
4306
4307 IF (NOT p_for_qa_check) THEN
4308 RAISE OKL_API.G_EXCEPTION_ERROR;
4309 END IF;
4310 END IF;
4311
4312 END IF;
4313
4314 /* Check if the Quote Consolidate flag is set to N, if not throw an error. */
4315
4316 OPEN l_con_yn_csr ( p_qte_id );
4317 FETCH l_con_yn_csr INTO l_found;
4318 l_not_found := l_con_yn_csr%NOTFOUND;
4319 CLOSE l_con_yn_csr;
4320
4321 IF( l_not_found ) THEN
4322 x_return_status := OKL_API.G_RET_STS_ERROR;
4323 l_not_found := NULL;
4324 l_found := NULL;
4325 OKL_API.set_message(
4326 p_app_name => G_APP_NAME,
4327 p_msg_name => 'OKL_LLA_RQ_CON_FLG_NOT_Y',
4328 p_token1 => 'QUOTE_NUMBER',
4329 p_token1_value => l_q_num);
4330
4331 IF ( NOT p_for_qa_check) THEN
4332 RAISE OKL_API.G_EXCEPTION_ERROR;
4333 END IF;
4334 END IF;
4335
4336 /* Check if the Quote Type is either TER_ROLL_PURCHASE or
4337 TER_ROLL_WO_PURCHASE, if not throw an error. */
4338
4339 OPEN l_qte_typ_csr ( p_qte_id );
4340 FETCH l_qte_typ_csr INTO l_found;
4341 l_not_found := l_qte_typ_csr%NOTFOUND;
4342 CLOSE l_qte_typ_csr;
4343
4344 IF( l_not_found ) THEN
4345 x_return_status := OKL_API.G_RET_STS_ERROR;
4346 l_not_found := NULL;
4347 l_found := NULL;
4348 OKL_API.set_message(
4349 p_app_name => G_APP_NAME,
4350 p_msg_name => 'OKL_LLA_RQ_TYP_NOT_CORRECT',
4351 p_token1 => 'QUOTE_NUMBER',
4352 p_token1_value => l_q_num);
4353
4354 IF ( NOT p_for_qa_check) THEN
4355 RAISE OKL_API.G_EXCEPTION_ERROR;
4356 END IF;
4357 END IF;
4358
4359 /* Check if the Rollover Fee Start date is between Quote
4360 effective dates, if not throw an error. */
4361
4362 OPEN l_rq_fee_check_csr ( p_chr_id, p_qte_id );
4363 FETCH l_rq_fee_check_csr INTO l_found;
4364 l_not_found := l_rq_fee_check_csr%NOTFOUND;
4365 CLOSE l_rq_fee_check_csr;
4366
4367 IF( l_not_found ) THEN
4368 x_return_status := OKL_API.G_RET_STS_ERROR;
4369 l_not_found := NULL;
4370 l_found := NULL;
4371 OKL_API.set_message(
4372 p_app_name => G_APP_NAME,
4373 p_msg_name => 'OKL_LLA_RQ_FEE_NOT_CORRECT',
4374 p_token1 => 'FEE_LINE',
4375 p_token1_value => l_fee_name,
4376 p_token2 => 'QUOTE_NUMBER',
4377 p_token2_value => l_q_num);
4378
4379 IF (NOT p_for_qa_check) THEN
4380 RAISE OKL_API.G_EXCEPTION_ERROR;
4381 END IF;
4382 END IF;
4383
4384 /* Check if the Rollover Quote Amount is equal to
4385 Rollover Fee line amount on the contract, if not throw an error. */
4386
4387 OPEN l_rq_amt_check_csr ( p_chr_id, p_qte_id );
4388 FETCH l_rq_amt_check_csr INTO l_amt;
4389 l_not_found := l_rq_amt_check_csr%NOTFOUND;
4390 CLOSE l_rq_amt_check_csr;
4391
4392 IF( l_not_found ) THEN
4393 x_return_status := OKL_API.G_RET_STS_ERROR;
4394 OKL_API.set_message(
4395 p_app_name => G_APP_NAME,
4396 p_msg_name => 'OKL_LLA_RQ_AMT_NOT_EQUAL',
4397 p_token1 => 'FEE_LINE',
4398 p_token1_value => l_fee_name,
4399 p_token2 => 'QUOTE_NUMBER',
4400 p_token2_value => l_q_num);
4401
4402 IF ( NOT p_for_qa_check) THEN
4403 RAISE OKL_API.G_EXCEPTION_ERROR;
4404 END IF;
4405 END IF;
4406
4407
4408 EXCEPTION
4409
4410 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4411 x_return_status := OKL_API.G_RET_STS_ERROR;
4412
4413
4414 IF l_con_qte_csr%ISOPEN THEN
4415 CLOSE l_con_qte_csr;
4416 END IF;
4417
4418 IF l_fee_name_csr%ISOPEN THEN
4419 CLOSE l_fee_name_csr;
4420 END IF;
4421
4422 IF l_cust_accnt_csr%ISOPEN THEN
4423 CLOSE l_cust_accnt_csr;
4424 END IF;
4425
4426 IF l_curr_code_csr%ISOPEN THEN
4427 CLOSE l_curr_code_csr;
4428 END IF;
4429
4430 IF l_qts_code_csr%ISOPEN THEN
4431 CLOSE l_qts_code_csr;
4432 END IF;
4433
4434 IF l_con_yn_csr%ISOPEN THEN
4435 CLOSE l_con_yn_csr;
4436 END IF;
4437
4438 IF l_qte_typ_csr%ISOPEN THEN
4439 CLOSE l_qte_typ_csr;
4440 END IF;
4441
4442 IF l_rq_fee_check_csr%ISOPEN THEN
4443 CLOSE l_rq_fee_check_csr;
4444 END IF;
4445
4446 IF l_rq_amt_check_csr%ISOPEN THEN
4447 CLOSE l_rq_amt_check_csr;
4448 END IF;
4449
4450 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4451 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4452
4453
4454 IF l_con_qte_csr%ISOPEN THEN
4455 CLOSE l_con_qte_csr;
4456 END IF;
4457
4458 IF l_fee_name_csr%ISOPEN THEN
4459 CLOSE l_fee_name_csr;
4460 END IF;
4461
4462 IF l_cust_accnt_csr%ISOPEN THEN
4463 CLOSE l_cust_accnt_csr;
4464 END IF;
4465
4466 IF l_curr_code_csr%ISOPEN THEN
4467 CLOSE l_curr_code_csr;
4468 END IF;
4469
4470 IF l_qts_code_csr%ISOPEN THEN
4471 CLOSE l_qts_code_csr;
4472 END IF;
4473
4474 IF l_con_yn_csr%ISOPEN THEN
4475 CLOSE l_con_yn_csr;
4476 END IF;
4477
4478 IF l_qte_typ_csr%ISOPEN THEN
4479 CLOSE l_qte_typ_csr;
4480 END IF;
4481
4482 IF l_rq_fee_check_csr%ISOPEN THEN
4483 CLOSE l_rq_fee_check_csr;
4484 END IF;
4485
4486 IF l_rq_amt_check_csr%ISOPEN THEN
4487 CLOSE l_rq_amt_check_csr;
4488 END IF;
4489
4490 WHEN OTHERS THEN
4491
4492
4493 IF l_con_qte_csr%ISOPEN THEN
4494 CLOSE l_con_qte_csr;
4495 END IF;
4496
4497 IF l_fee_name_csr%ISOPEN THEN
4498 CLOSE l_fee_name_csr;
4499 END IF;
4500
4501 IF l_cust_accnt_csr%ISOPEN THEN
4502 CLOSE l_cust_accnt_csr;
4503 END IF;
4504
4505 IF l_curr_code_csr%ISOPEN THEN
4506 CLOSE l_curr_code_csr;
4507 END IF;
4508
4509 IF l_qts_code_csr%ISOPEN THEN
4510 CLOSE l_qts_code_csr;
4511 END IF;
4512
4513 IF l_con_yn_csr%ISOPEN THEN
4514 CLOSE l_con_yn_csr;
4515 END IF;
4516
4517 IF l_qte_typ_csr%ISOPEN THEN
4518 CLOSE l_qte_typ_csr;
4519 END IF;
4520
4521 IF l_rq_fee_check_csr%ISOPEN THEN
4522 CLOSE l_rq_fee_check_csr;
4523 END IF;
4524
4525 IF l_rq_amt_check_csr%ISOPEN THEN
4526 CLOSE l_rq_amt_check_csr;
4527 END IF;
4528
4529 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4530 p_api_name => l_api_name,
4531 p_pkg_name => g_pkg_name,
4532 p_exc_name => 'OTHERS',
4533 x_msg_count => x_msg_count,
4534 x_msg_data => x_msg_data,
4535 p_api_type => g_api_type);
4536
4537 End validate_rollover_feeLine;
4538
4539 ----------------------------------------------------------------------------
4540 --start of comments
4541 --API Name : rollover_fee
4542 --Description : API called to update the the rollover quote amount on a contract
4543 -- to the referencing creditline contract column tot_cl_transfer_amt
4544 --
4545 --Parameters : IN - p_chr_id - Contract Number
4546 -- p_cl_id - Referenced Creditline contract
4547 -- OUT x_return_status - Return Status
4548 --
4549 --History : 10-Nov-2004 smereddy Created
4550 --
4551 --
4552 --end of comments
4553 -----------------------------------------------------------------------------
4554
4555 PROCEDURE rollover_fee(
4556 p_api_version IN NUMBER,
4557 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4558 x_return_status OUT NOCOPY VARCHAR2,
4559 x_msg_count OUT NOCOPY NUMBER,
4560 x_msg_data OUT NOCOPY VARCHAR2,
4561 p_chr_id IN NUMBER, -- contract id
4562 p_cl_id IN NUMBER, -- creditline id
4563 x_rem_amt OUT NOCOPY NUMBER
4564 ) IS
4565
4566 cursor c_roll_fee(p_chr_id number) is
4567 select nvl(sum(kle.amount),0) amt
4568 from okc_k_lines_b cle,
4569 okl_k_lines kle,
4570 okc_line_styles_b lse
4571 where cle.id = kle.id
4572 and cle.dnz_chr_id = p_chr_id
4573 and lse.id = cle.lse_id
4574 and lse.lty_code = 'FEE'
4575 and fee_type = 'ROLLOVER';
4576
4577 l_chr_id okc_k_headers_b.id%type;
4578 l_prev_roll_amount number := null;
4579 l_roll_amt number := null;
4580 l_tot_roll_amt number := null;
4581
4582 l_api_name CONSTANT VARCHAR2(30) := 'rollover_fee';
4583 l_api_version CONSTANT NUMBER := 1.0;
4584
4585 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
4586 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
4587
4588 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
4589 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
4590
4591 BEGIN
4592
4593 l_chr_id := p_chr_id;
4594 If okl_context.get_okc_org_id is null then
4595 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
4596 End If;
4597
4598 -- call START_ACTIVITY to create savepoint, check compatibility
4599 -- and initialize message list
4600 x_return_status := OKC_API.START_ACTIVITY(
4601 p_api_name => l_api_name,
4602 p_pkg_name => g_pkg_name,
4603 p_init_msg_list => p_init_msg_list,
4604 l_api_version => l_api_version,
4605 p_api_version => p_api_version,
4606 p_api_type => g_api_type,
4607 x_return_status => x_return_status);
4608
4609 -- check if activity started successfully
4610 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4611 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4612 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4613 raise OKC_API.G_EXCEPTION_ERROR;
4614 End If;
4615
4616 -- get the previous booked contracts rollover amount;pull data from new column and set to 0 if it is null
4617 l_prev_roll_amount := OKL_SEEDED_FUNCTIONS_PVT.rollover_fee(p_cl_id);
4618
4619 If(l_prev_roll_amount is null) Then
4620 l_prev_roll_amount := 0;
4621 End If;
4622
4623 -- get the current rollover amount
4624 open c_roll_fee(l_chr_id);
4625 fetch c_roll_fee into l_roll_amt;
4626 close c_roll_fee;
4627
4628 -- total rollover amount for the creditline
4629 l_tot_roll_amt := l_prev_roll_amount + l_roll_amt;
4630
4631 lp_khrv_rec.id := p_cl_id;
4632 lp_chrv_rec.id := p_cl_id;
4633 lp_khrv_rec.tot_cl_transfer_amt := l_tot_roll_amt;
4634
4635 -- update contract header for the tot. rollover creditline
4636 OKL_CONTRACT_PUB.update_contract_header(
4637 p_api_version => p_api_version,
4638 p_init_msg_list => p_init_msg_list,
4639 x_return_status => x_return_status,
4640 x_msg_count => x_msg_count,
4641 x_msg_data => x_msg_data,
4642 p_restricted_update => 'F',
4643 p_chrv_rec => lp_chrv_rec,
4644 p_khrv_rec => lp_khrv_rec,
4645 p_edit_mode => 'N',
4646 x_chrv_rec => lx_chrv_rec,
4647 x_khrv_rec => lx_khrv_rec);
4648
4649 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4650 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4651 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4652 RAISE OKC_API.G_EXCEPTION_ERROR;
4653 END IF;
4654
4655 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
4656
4657 EXCEPTION
4658 when OKC_API.G_EXCEPTION_ERROR then
4659 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4660 p_api_name => l_api_name,
4661 p_pkg_name => g_pkg_name,
4662 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
4663 x_msg_count => x_msg_count,
4664 x_msg_data => x_msg_data,
4665 p_api_type => g_api_type);
4666
4667 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
4668 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4669 p_api_name => l_api_name,
4670 p_pkg_name => g_pkg_name,
4671 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
4672 x_msg_count => x_msg_count,
4673 x_msg_data => x_msg_data,
4674 p_api_type => g_api_type);
4675
4676 when OTHERS then
4677 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4678 p_api_name => l_api_name,
4679 p_pkg_name => g_pkg_name,
4680 p_exc_name => 'OTHERS',
4681 x_msg_count => x_msg_count,
4682 x_msg_data => x_msg_data,
4683 p_api_type => g_api_type);
4684
4685
4686 END;
4687
4688 ----------------------------------------------------------------------------
4689 --start of comments
4690 --API Name : rollover_fee
4691 --Description : API called to throw warning message if the rollove amount
4692 -- exceeds the total available/remaining credit limit amount
4693 --
4694 --Parameters : IN - p_chr_id - Contract Number
4695 -- OUT x_rem_amt - Return Status
4696 --
4697 --History : 10-Nov-2004 smereddy Created
4698 --
4699 --
4700 --end of comments
4701 -----------------------------------------------------------------------------
4702
4703 PROCEDURE rollover_fee(
4704 p_api_version IN NUMBER,
4705 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4706 x_return_status OUT NOCOPY VARCHAR2,
4707 x_msg_count OUT NOCOPY NUMBER,
4708 x_msg_data OUT NOCOPY VARCHAR2,
4709 p_chr_id IN NUMBER, -- contract id
4710 x_rem_amt OUT NOCOPY NUMBER
4711 ) IS
4712
4713 /* smereddy 09-Nov-2004 Start
4714 Cursor to get the rollover fee lines for a contract
4715 that is booked for the first time. */
4716
4717 CURSOR l_rq_fee_lns_bkg_csr ( p_chr_id number ) IS
4718 SELECT kle.qte_id
4719 FROM okc_k_headers_b khr, okc_k_lines_b cleb, okl_k_lines kle
4720 WHERE khr.id = p_chr_id
4721 AND cleb.dnz_chr_id = khr.id
4722 AND kle.ID = cleb.ID
4723 AND kle.fee_type = 'ROLLOVER';
4724
4725 CURSOR l_orig_src_code_csr ( p_chr_id number ) IS
4726 SELECT nvl(ORIG_SYSTEM_SOURCE_CODE,'XXX')
4727 FROM okc_k_headers_b khr
4728 WHERE khr.id = p_chr_id;
4729
4730 l_orig_src_code okc_k_headers_b.orig_system_source_code%type := null;
4731 l_cl_roll_amt NUMBER := 0;
4732 l_cl_tot_roll_amt NUMBER := 0;
4733 l_chr_id okc_k_headers_b.id%type;
4734 l_cl_id okc_k_headers_b.id%type := null;
4735 l_qte_id number := null;
4736 l_cl_rem_amt number := null;
4737 l_prev_roll_amount number := null;
4738 l_roll_amt number := null;
4739 l_tot_roll_amt number := null;
4740 l_tot_cl_rem_amt number := null;
4741
4742 l_api_name CONSTANT VARCHAR2(30) := 'rollover_fee';
4743 l_api_version CONSTANT NUMBER := 1.0;
4744
4745 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
4746 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
4747
4748 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
4749 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
4750
4751 BEGIN
4752
4753 l_chr_id := p_chr_id;
4754 If okl_context.get_okc_org_id is null then
4755 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
4756 End If;
4757
4758 -- call START_ACTIVITY to create savepoint, check compatibility
4759 -- and initialize message list
4760 x_return_status := OKC_API.START_ACTIVITY(
4761 p_api_name => l_api_name,
4762 p_pkg_name => g_pkg_name,
4763 p_init_msg_list => p_init_msg_list,
4764 l_api_version => l_api_version,
4765 p_api_version => p_api_version,
4766 p_api_type => g_api_type,
4767 x_return_status => x_return_status);
4768
4769 -- check if activity started successfully
4770 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4771 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4772 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4773 raise OKC_API.G_EXCEPTION_ERROR;
4774 End If;
4775
4776 l_orig_src_code := null;
4777
4778 open l_orig_src_code_csr(p_chr_id);
4779 fetch l_orig_src_code_csr into l_orig_src_code;
4780 close l_orig_src_code_csr;
4781
4782 If(l_orig_src_code = 'OKL_SPLIT' OR l_orig_src_code = 'OKL_RELEASE') Then
4783 x_rem_amt := 0;
4784 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4785 return;
4786 End If;
4787
4788 open l_rq_fee_lns_bkg_csr(p_chr_id);
4789 fetch l_rq_fee_lns_bkg_csr into l_qte_id;
4790 close l_rq_fee_lns_bkg_csr;
4791
4792 -- check whether rollover quote and cleditline exists
4793 If(l_qte_id is null OR l_qte_id = OKC_API.G_MISS_NUM) Then
4794 x_rem_amt := 0;
4795 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4796 return;
4797 End If;
4798
4799 -- check whether creditline exists
4800 l_cl_id := OKL_CREDIT_PUB.get_creditline_by_chrid(p_chr_id);
4801
4802 If(l_cl_id is null OR l_cl_id = OKC_API.G_MISS_NUM) Then
4803 x_rem_amt := 0;
4804 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4805 return;
4806 End If;
4807
4808 -- get the remaining amount
4809 l_cl_rem_amt := OKL_SEEDED_FUNCTIONS_PVT.creditline_total_remaining(l_cl_id,null);
4810
4811 -- get the previous booked contracts rollover amount;pull data from new column and set to 0 if it is null
4812 l_prev_roll_amount := OKL_SEEDED_FUNCTIONS_PVT.rollover_fee(l_cl_id);
4813
4814 If(l_prev_roll_amount is null) Then
4815 l_prev_roll_amount := 0;
4816 Else
4817 l_roll_amt := l_prev_roll_amount;
4818 End If;
4819
4820 -- total rollover amount for the creditline
4821 l_tot_roll_amt := l_roll_amt;
4822
4823 -- total credit limit
4824 l_tot_cl_rem_amt := l_cl_rem_amt - l_tot_roll_amt;
4825
4826 x_rem_amt := l_tot_cl_rem_amt;
4827
4828 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
4829
4830 EXCEPTION
4831 when OKC_API.G_EXCEPTION_ERROR then
4832 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4833 p_api_name => l_api_name,
4834 p_pkg_name => g_pkg_name,
4835 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
4836 x_msg_count => x_msg_count,
4837 x_msg_data => x_msg_data,
4838 p_api_type => g_api_type);
4839
4840 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
4841 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4842 p_api_name => l_api_name,
4843 p_pkg_name => g_pkg_name,
4844 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
4845 x_msg_count => x_msg_count,
4846 x_msg_data => x_msg_data,
4847 p_api_type => g_api_type);
4848
4849 when OTHERS then
4850 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4851 p_api_name => l_api_name,
4852 p_pkg_name => g_pkg_name,
4853 p_exc_name => 'OTHERS',
4854 x_msg_count => x_msg_count,
4855 x_msg_data => x_msg_data,
4856 p_api_type => g_api_type);
4857
4858 END;
4859
4860 --Bug# 4899328
4861 PROCEDURE create_update_link_assets (p_api_version IN NUMBER,
4862 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
4863 p_cle_id IN NUMBER,
4864 p_chr_id IN NUMBER,
4865 p_capitalize_yn IN VARCHAR2,
4866 p_link_asset_tbl IN link_asset_tbl_type,
4867 p_derive_assoc_amt IN VARCHAR2,
4868 x_return_status OUT NOCOPY VARCHAR2,
4869 x_msg_count OUT NOCOPY NUMBER,
4870 x_msg_data OUT NOCOPY VARCHAR2) IS
4871
4872 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'create_update_link_assets';
4873
4874 l_create_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
4875 l_update_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
4876 lx_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
4877
4878 l_link_asset_tbl link_asset_tbl_type;
4879
4880 k BINARY_INTEGER := 1; -- create table index
4881 m BINARY_INTEGER := 1; -- update table index
4882
4883 l_line_amount NUMBER;
4884 l_asset_oec NUMBER;
4885 l_oec_total NUMBER := 0;
4886 l_assoc_amount NUMBER;
4887 l_assoc_total NUMBER := 0;
4888 l_currency_code VARCHAR2(15);
4889 l_compare_amt NUMBER;
4890 l_diff NUMBER;
4891 l_adj_rec BINARY_INTEGER;
4892 lx_return_status VARCHAR2(1);
4893
4894 CURSOR c_asset_number(p_fin_asset_id IN NUMBER,
4895 p_chr_id IN NUMBER) IS
4896 SELECT txl.asset_number
4897 FROM okc_k_lines_b cle,
4898 okc_line_styles_b lse,
4899 okl_txl_assets_b txl
4900 WHERE cle.id = txl.kle_id
4901 AND cle.lse_id = lse.id
4902 AND lse.lty_code = 'FIXED_ASSET'
4903 AND cle.cle_id = p_fin_asset_id
4904 AND cle.dnz_chr_id = p_chr_id
4905 AND txl.dnz_khr_id = p_chr_id;
4906
4907 CURSOR c_term_sub_lines(p_cle_id IN NUMBER,
4908 p_chr_id IN NUMBER) is
4909 SELECT SUM(NVL(kle.capital_amount,kle.amount)) amount
4910 FROM okc_k_lines_b cle,
4911 okl_k_lines kle
4912 WHERE cle.cle_id = p_cle_id
4913 AND cle.dnz_chr_id = p_chr_id
4914 AND cle.sts_code = 'TERMINATED'
4915 AND kle.id = cle.id;
4916
4917 l_term_sub_lines_amt NUMBER;
4918 l_release_contract_yn VARCHAR2(1);
4919
4920 BEGIN
4921
4922 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4923
4924 SELECT NVL(amount, 0)
4925 INTO l_line_amount
4926 FROM okl_k_lines
4927 WHERE id = p_cle_id;
4928
4929 -- Exclude Terminated sub-line amounts from
4930 -- total amount available for allocation
4931 l_term_sub_lines_amt := 0;
4932 OPEN c_term_sub_lines(p_cle_id => p_cle_id,
4933 p_chr_id => p_chr_id);
4934 FETCH c_term_sub_lines INTO l_term_sub_lines_amt;
4935 CLOSE c_term_sub_lines;
4936
4937 l_line_amount := l_line_amount - NVL(l_term_sub_lines_amt,0);
4938
4939 IF l_line_amount < 0 THEN
4940 OKL_API.SET_MESSAGE(p_app_name => g_app_name
4941 ,p_msg_name => 'OKL_LA_NEGATIVE_COV_AST_AMT'
4942 ,p_token1 => 'AMOUNT'
4943 ,p_token1_value => TO_CHAR(NVL(l_term_sub_lines_amt,0)));
4944 RAISE OKL_API.G_EXCEPTION_ERROR;
4945 END IF;
4946
4947 SELECT currency_code
4948 INTO l_currency_code
4949 FROM okc_k_headers_b
4950 WHERE id = p_chr_id;
4951
4952 l_link_asset_tbl := p_link_asset_tbl;
4953
4954 IF (l_link_asset_tbl.COUNT > 0) THEN
4955
4956 l_release_contract_yn := okl_api.g_false;
4957 l_release_contract_yn := okl_lla_util_pvt.check_release_contract(p_chr_id => p_chr_id);
4958
4959 ------------------------------------------------------------------
4960 -- 1. Loop through to get OEC total of all assets being associated
4961 ------------------------------------------------------------------
4962 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
4963
4964 IF l_link_asset_tbl.EXISTS(i) THEN
4965
4966 --Bug# 4631549
4967 If l_release_contract_yn = okl_api.g_true then
4968 SELECT NVL(expected_asset_cost, 0)
4969 INTO l_asset_oec
4970 FROM okl_k_lines
4971 WHERE id = l_link_asset_tbl(i).fin_asset_id;
4972 else
4973 SELECT NVL(oec, 0)
4974 INTO l_asset_oec
4975 FROM okl_k_lines
4976 WHERE id = l_link_asset_tbl(i).fin_asset_id;
4977 end if;
4978
4979 l_oec_total := l_oec_total + l_asset_oec;
4980
4981 END IF;
4982
4983 END LOOP;
4984
4985 ----------------------------------------------------------------------------
4986 -- 2. Loop through to determine associated amounts and round off the amounts
4987 ----------------------------------------------------------------------------
4988 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
4989
4990 IF l_link_asset_tbl.EXISTS(i) THEN
4991
4992 IF p_derive_assoc_amt = 'N' THEN
4993
4994 l_assoc_amount := l_link_asset_tbl(i).amount;
4995
4996 ELSIF l_oec_total = 0 THEN
4997
4998 l_assoc_amount := l_line_amount / l_link_asset_tbl.COUNT;
4999
5000 ELSE
5001
5002 -- LLA APIs ensure asset OEC and line amount are rounded
5003 --Bug# 4631549
5004 If l_release_contract_yn = okl_api.g_true then
5005 SELECT NVL(expected_asset_cost, 0)
5006 INTO l_asset_oec
5007 FROM okl_k_lines
5008 WHERE id = l_link_asset_tbl(i).fin_asset_id;
5009 Else
5010 SELECT NVL(oec, 0)
5011 INTO l_asset_oec
5012 FROM okl_k_lines
5013 WHERE id = l_link_asset_tbl(i).fin_asset_id;
5014 End If;
5015
5016 IF l_link_asset_tbl.COUNT = 1 THEN
5017
5018 l_assoc_amount := l_line_amount;
5019
5020 ELSE
5021
5022 l_assoc_amount := l_line_amount * l_asset_oec / l_oec_total;
5023
5024 END IF;
5025 END IF;
5026
5027 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
5028 p_currency_code => l_currency_code);
5029
5030 l_assoc_total := l_assoc_total + l_assoc_amount;
5031
5032 l_link_asset_tbl(i).amount := l_assoc_amount;
5033 END IF;
5034
5035 END LOOP;
5036
5037 ----------------------------------------------------------------------------------------------------
5038 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
5039 ----------------------------------------------------------------------------------------------------
5040 IF l_assoc_total <> l_line_amount THEN
5041
5042 l_diff := ABS(l_assoc_total - l_line_amount);
5043
5044 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
5045
5046 IF l_link_asset_tbl.EXISTS(i) THEN
5047
5048 -- if the total split amount is less than line amount add the difference amount to the
5049 -- asset with less amount and if the total split amount is greater than the line amount
5050 -- than subtract the difference amount from the asset with highest amount
5051
5052 IF i = l_link_asset_tbl.FIRST THEN
5053
5054 l_adj_rec := i; -- Bug#3404844
5055 l_compare_amt := l_link_asset_tbl(i).amount;
5056
5057 ELSIF (l_assoc_total < l_line_amount) AND (l_link_asset_tbl(i).amount <= l_compare_amt) OR
5058 (l_assoc_total > l_line_amount) AND (l_link_asset_tbl(i).amount >= l_compare_amt) THEN
5059
5060 l_adj_rec := i;
5061 l_compare_amt := l_link_asset_tbl(i).amount;
5062
5063 END IF;
5064
5065 END IF;
5066
5067 END LOOP;
5068
5069 IF l_assoc_total < l_line_amount THEN
5070
5071 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount + l_diff;
5072
5073 ELSE
5074
5075 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount - l_diff;
5076
5077 END IF;
5078
5079 END IF;
5080
5081 ------------------------------------------------------
5082 -- 4. Prepare arrays to pass to create and update APIs
5083 ------------------------------------------------------
5084 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
5085
5086 IF l_link_asset_tbl.EXISTS(i) THEN
5087
5088 l_assoc_amount := l_link_asset_tbl(i).amount;
5089
5090 IF l_link_asset_tbl(i).link_line_id IS NULL THEN
5091
5092 l_create_line_item_tbl(k).chr_id := p_chr_id;
5093 l_create_line_item_tbl(k).parent_cle_id := p_cle_id;
5094 l_create_line_item_tbl(k).item_id1 := l_link_asset_tbl(i).fin_asset_id;
5095 l_create_line_item_tbl(k).item_id2 := '#';
5096 l_create_line_item_tbl(k).item_object1_code := 'OKX_COVASST';
5097 l_create_line_item_tbl(k).serv_cov_prd_id := NULL;
5098
5099 -- The linked amount is always passed in as 'capital_amount' even though capital amount
5100 -- is applicable only for CAPITALIZED fee types. The LLA API will ensure that
5101 -- the linked amount is stored in the appropriate column (AMOUNT vs CAPITAL_AMOUNT)
5102 l_create_line_item_tbl(k).capital_amount := l_assoc_amount;
5103
5104 IF l_link_asset_tbl(i).asset_number IS NOT NULL THEN
5105 l_create_line_item_tbl(k).name := l_link_asset_tbl(i).asset_number;
5106 ELSE
5107 OPEN c_asset_number(p_fin_asset_id => l_link_asset_tbl(i).fin_asset_id,
5108 p_chr_id => p_chr_id);
5109 FETCH c_asset_number INTO l_create_line_item_tbl(k).name;
5110 CLOSE c_asset_number;
5111 END IF;
5112
5113 k := k + 1;
5114
5115 ELSE
5116
5117 l_update_line_item_tbl(m).cle_id := l_link_asset_tbl(i).link_line_id;
5118 l_update_line_item_tbl(m).item_id := l_link_asset_tbl(i).link_item_id;
5119 l_update_line_item_tbl(m).chr_id := p_chr_id;
5120 l_update_line_item_tbl(m).parent_cle_id := p_cle_id;
5121 l_update_line_item_tbl(m).item_id1 := l_link_asset_tbl(i).fin_asset_id;
5122 l_update_line_item_tbl(m).item_id2 := '#';
5123 l_update_line_item_tbl(m).item_object1_code := 'OKX_COVASST';
5124 l_update_line_item_tbl(m).serv_cov_prd_id := NULL;
5125
5126 -- The linked amount is always passed in as 'capital_amount' even though capital amount
5127 -- is applicable only for CAPITALIZED fee types. The LLA API will ensure that
5128 -- the linked amount is stored in the appropriate column (AMOUNT vs CAPITAL_AMOUNT)
5129 l_update_line_item_tbl(m).capital_amount := l_assoc_amount;
5130
5131 IF l_link_asset_tbl(i).asset_number IS NOT NULL THEN
5132 l_update_line_item_tbl(m).name := l_link_asset_tbl(i).asset_number;
5133 ELSE
5134 OPEN c_asset_number(p_fin_asset_id => l_link_asset_tbl(i).fin_asset_id,
5135 p_chr_id => p_chr_id);
5136 FETCH c_asset_number INTO l_update_line_item_tbl(m).name;
5137 CLOSE c_asset_number;
5138 END IF;
5139
5140 m := m + 1;
5141
5142 END IF;
5143
5144 END IF;
5145
5146 END LOOP;
5147
5148 IF l_create_line_item_tbl.COUNT > 0 THEN
5149
5150 okl_contract_line_item_pvt.create_contract_line_item( p_api_version => p_api_version,
5151 p_init_msg_list => p_init_msg_list,
5152 x_return_status => lx_return_status,
5153 x_msg_count => x_msg_count,
5154 x_msg_data => x_msg_data,
5155 p_line_item_tbl => l_create_line_item_tbl,
5156 x_line_item_tbl => lx_line_item_tbl);
5157
5158 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5159 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5160 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
5161 RAISE OKL_API.G_EXCEPTION_ERROR;
5162 END IF;
5163
5164 END IF;
5165
5166 IF l_update_line_item_tbl.COUNT > 0 THEN
5167
5168 okl_contract_line_item_pvt.update_contract_line_item( p_api_version => p_api_version,
5169 p_init_msg_list => p_init_msg_list,
5170 x_return_status => lx_return_status,
5171 x_msg_count => x_msg_count,
5172 x_msg_data => x_msg_data,
5173 p_line_item_tbl => l_update_line_item_tbl,
5174 x_line_item_tbl => lx_line_item_tbl);
5175
5176 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5177 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5178 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
5179 RAISE OKL_API.G_EXCEPTION_ERROR;
5180 END IF;
5181
5182 END IF;
5183
5184 END IF;
5185
5186 EXCEPTION
5187
5188 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5189
5190 x_return_status := OKL_API.G_RET_STS_ERROR;
5191
5192 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5193
5194 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
5195
5196 WHEN OTHERS THEN
5197
5198 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
5199
5200 END create_update_link_assets;
5201 --Bug# 4899328
5202
5203 PROCEDURE allocate_amount(p_api_version IN NUMBER,
5204 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
5205 p_transaction_control IN VARCHAR2 DEFAULT G_TRUE,
5206 p_cle_id IN NUMBER,
5207 p_chr_id IN NUMBER,
5208 p_capitalize_yn IN VARCHAR2,
5209 x_cle_id OUT NOCOPY NUMBER,
5210 x_chr_id OUT NOCOPY NUMBER,
5211 x_return_status OUT NOCOPY VARCHAR2,
5212 x_msg_count OUT NOCOPY NUMBER,
5213 x_msg_data OUT NOCOPY VARCHAR2) IS
5214
5215
5216 l_chr_id okc_k_headers_b.id%type := null;
5217 l_cl_id okc_k_headers_b.id%type := null;
5218
5219 l_api_name CONSTANT VARCHAR2(30) := 'allocate_amount';
5220 l_api_version CONSTANT NUMBER := 1.0;
5221
5222 --Bug# 4899328
5223 CURSOR c_assets(p_chr_id IN NUMBER) IS
5224 SELECT cle.id fin_asset_id,
5225 cle.name asset_number
5226 FROM okc_k_lines_v cle,
5227 okc_line_styles_b lse,
5228 okc_statuses_b sts
5229 WHERE cle.chr_id = p_chr_id
5230 AND cle.dnz_chr_id = p_chr_id
5231 AND cle.lse_id = lse.id
5232 AND lse.lty_code = 'FREE_FORM1'
5233 AND cle.sts_code = sts.code
5234 AND sts.ste_code NOT IN ('CANCELLED','TERMINATED');
5235
5236 CURSOR c_cov_asset_line(p_chr_id IN NUMBER,
5237 p_fee_cle_id IN NUMBER,
5238 p_fin_ast_id IN NUMBER) IS
5239 SELECT cov_ast_cle.id cov_ast_cle_id,
5240 cov_ast_cim.id cov_ast_cim_id
5241 FROM okc_k_lines_b cov_ast_cle,
5242 okc_k_items cov_ast_cim
5243 WHERE cov_ast_cle.dnz_chr_id = p_chr_id
5244 AND cov_ast_cle.cle_id = p_fee_cle_id
5245 AND cov_ast_cim.cle_id = cov_ast_cle.id
5246 AND cov_ast_cim.object1_id1 = TO_CHAR(p_fin_ast_id)
5247 AND cov_ast_cim.object1_id2 = '#'
5248 and cov_ast_cim.jtot_object1_code = 'OKX_COVASST';
5249
5250 l_link_asset_tbl link_asset_tbl_type;
5251
5252 i NUMBER;
5253
5254 BEGIN
5255
5256 l_chr_id := p_chr_id;
5257 If okl_context.get_okc_org_id is null then
5258 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
5259 End If;
5260
5261 -- call START_ACTIVITY to create savepoint, check compatibility
5262 -- and initialize message list
5263 x_return_status := OKC_API.START_ACTIVITY(
5264 p_api_name => l_api_name,
5265 p_pkg_name => g_pkg_name,
5266 p_init_msg_list => p_init_msg_list,
5267 l_api_version => l_api_version,
5268 p_api_version => p_api_version,
5269 p_api_type => g_api_type,
5270 x_return_status => x_return_status);
5271
5272 -- check if activity started successfully
5273 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5274 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5275 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5276 raise OKC_API.G_EXCEPTION_ERROR;
5277 End If;
5278
5279 --Bug# 4899328: Start
5280 /*
5281 OKL_SALES_QUOTE_LINES_PVT.allocate_amount(
5282 p_api_version => p_api_version,
5283 p_init_msg_list => p_init_msg_list,
5284 p_transaction_control => p_transaction_control,
5285 p_cle_id => p_cle_id,
5286 p_chr_id => p_chr_id,
5287 p_capitalize_yn => p_capitalize_yn,
5288 x_cle_id => x_cle_id,
5289 x_chr_id => x_chr_id,
5290 x_return_status => x_return_status,
5291 x_msg_count => x_msg_count,
5292 x_msg_data => x_msg_data
5293 );
5294
5295 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5296 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5297 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5298 RAISE OKC_API.G_EXCEPTION_ERROR;
5299 END IF;
5300 */
5301
5302 i := 0;
5303 FOR l_asset IN c_assets(p_chr_id => p_chr_id) LOOP
5304 i := i + 1;
5305
5306 l_link_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
5307 l_link_asset_tbl(i).asset_number := l_asset.asset_number;
5308
5309 l_link_asset_tbl(i).link_line_id := NULL;
5310 l_link_asset_tbl(i).link_item_id := NULL;
5311 FOR r_cov_asset_line IN c_cov_asset_line(p_chr_id => p_chr_id,
5312 p_fee_cle_id => p_cle_id,
5313 p_fin_ast_id => l_asset.fin_asset_id)
5314 LOOP
5315 l_link_asset_tbl(i).link_line_id := r_cov_asset_line.cov_ast_cle_id;
5316 l_link_asset_tbl(i).link_item_id := r_cov_asset_line.cov_ast_cim_id;
5317 END LOOP;
5318 END LOOP;
5319
5320 IF l_link_asset_tbl.COUNT > 0 THEN
5321
5322 create_update_link_assets (p_api_version => p_api_version,
5323 p_init_msg_list => p_init_msg_list,
5324 p_cle_id => p_cle_id,
5325 p_chr_id => p_chr_id,
5326 p_capitalize_yn => p_capitalize_yn,
5327 p_link_asset_tbl => l_link_asset_tbl,
5328 p_derive_assoc_amt => 'Y',
5329 x_return_status => x_return_status,
5330 x_msg_count => x_msg_count,
5331 x_msg_data => x_msg_data);
5332
5333 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5334 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5335 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
5336 RAISE OKL_API.G_EXCEPTION_ERROR;
5337 END IF;
5338
5339 END IF;
5340 --Bug# 4899328: End
5341
5342 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
5343
5344 EXCEPTION
5345 when OKC_API.G_EXCEPTION_ERROR then
5346 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5347 p_api_name => l_api_name,
5348 p_pkg_name => g_pkg_name,
5349 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
5350 x_msg_count => x_msg_count,
5351 x_msg_data => x_msg_data,
5352 p_api_type => g_api_type);
5353
5354 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
5355 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5356 p_api_name => l_api_name,
5357 p_pkg_name => g_pkg_name,
5358 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
5359 x_msg_count => x_msg_count,
5360 x_msg_data => x_msg_data,
5361 p_api_type => g_api_type);
5362
5363 when OTHERS then
5364 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5365 p_api_name => l_api_name,
5366 p_pkg_name => g_pkg_name,
5367 p_exc_name => 'OTHERS',
5368 x_msg_count => x_msg_count,
5369 x_msg_data => x_msg_data,
5370 p_api_type => g_api_type);
5371
5372 END allocate_amount;
5373
5374 -- Guru added the following api for RVI
5375
5376 PROCEDURE process_rvi_stream(
5377 p_api_version IN NUMBER,
5378 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5379 x_return_status OUT NOCOPY VARCHAR2,
5380 x_msg_count OUT NOCOPY NUMBER,
5381 x_msg_data OUT NOCOPY VARCHAR2,
5382 p_check_box_value IN VARCHAR2,
5383 p_fee_types_rec IN fee_types_rec_type,
5384 x_fee_types_rec OUT NOCOPY fee_types_rec_type
5385 ) IS
5386
5387 -- cursor to get contract details
5388
5389 --Bug# 4524091
5390 CURSOR get_k_details_csr ( p_chr_id IN number ) IS
5391 SELECT
5392 START_DATE, END_DATE, ORIG_SYSTEM_SOURCE_CODE
5393 FROM OKL_K_HEADERS_FULL_V
5394 WHERE id = p_chr_id;
5395
5396
5397
5398
5399 -- cursor to get pricing engine
5400
5401 CURSOR get_pricing_engine_csr (p_chr_id IN NUMBER) IS
5402 SELECT
5403 gts.pricing_engine
5404 FROM
5405 okl_k_headers khr,
5406 okl_products_v pdt,
5407 okl_ae_tmpt_sets_v aes,
5408 OKL_ST_GEN_TMPT_SETS gts
5409 WHERE
5410 khr.pdt_id = pdt.id AND
5411 pdt.aes_id = aes.id AND
5412 aes.gts_id = gts.id AND
5413 khr.id = p_chr_id;
5414
5415 -- cursor to get check box value
5416 CURSOR get_rvi_check_value_csr (p_chr_id in NUMBER) IS
5417 SELECT rule_information1
5418 FROM okc_rules_v rul, okc_rule_groups_v rgb
5419 WHERE rul.dnz_chr_id = p_chr_id AND
5420 rule_information_category = 'LARVAU' AND
5421 rgb.id = rul.rgp_id AND rgd_code like 'LARVIN';
5422
5423 -- cursor to get fee line id and amount
5424 CURSOR get_fee_line_id (p_chr_id in NUMBER) IS
5425 SELECT kleb.id,Kleb.amount
5426 FROM
5427 okc_k_lines_b cleb,okl_k_lines kleb,okc_line_styles_b lseb
5428 WHERE cleb.dnz_chr_id = p_chr_id AND
5429 kleb.id = cleb.id AND
5430 cleb.lse_id = lseb.id AND
5431 lseb.lty_code = 'FEE' AND
5432 kleb.fee_purpose_code = 'RVI';
5433
5434 -- cursor to get stream name and stream id
5435
5436 CURSOR get_stream_name_csr (p_chr_id in NUMBER) IS
5437 SELECT styb.id, styb.code
5438 FROM okl_strm_type_b styb,
5439 okc_k_items cim,
5440 okc_k_lines_b cleb,
5441 okl_k_lines kle
5442 WHERE styb.id = cim.object1_id1
5443 AND '#' = cim.object1_id2
5444 AND cim.jtot_object1_code = 'OKL_STRMTYP'
5445 AND cim.cle_id = cleb.id
5446 AND cim.dnz_chr_id = cleb.dnz_chr_id
5447 AND cleb.lse_id = 52
5448 AND kle.id = cleb.id
5449 AND kle.fee_type = 'ABSORBED'
5450 AND kle.fee_purpose_code = 'RVI'
5451 AND cleb.dnz_chr_id = p_chr_id;
5452
5453 -- cursor to get okc_k_items id
5454
5455 CURSOR get_okc_k_items_csr (fee_line_id IN NUMBER) IS
5456 SELECT id
5457 FROM okc_k_items_v
5458 WHERE cle_id = fee_line_id;
5459
5460
5461 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5462 l_api_name VARCHAR2(200) := 'process_rvi_stream';
5463
5464 l_fee_types_rec fee_types_rec_type := p_fee_types_rec;
5465 l_line_id okc_k_lines_b.id%type;
5466 l_chr_id okc_k_lines_b.dnz_chr_id%type;
5467 l_check_box_value VARCHAR2(450);
5468 l_strm_id okl_strm_type_b.id%type;
5469 l_strm_name okl_strm_type_b.code%type;
5470 l_fee_line_id okl_k_lines.id%type;
5471 l_amount okl_k_lines.amount%type;
5472
5473 l_start_date DATE;
5474 l_end_date DATE;
5475
5476 l_api_version CONSTANT NUMBER := 1.0;
5477 l_price_engine OKL_ST_GEN_TMPT_SETS.pricing_engine%type;
5478
5479 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
5480 l_check_box_val VARCHAR2(450);
5481 l_cim_id NUMBER;
5482 --Bug# 4524091
5483 l_orig_system_source_code okc_k_headers_b.orig_system_source_code%type;
5484
5485 BEGIN
5486
5487 l_chr_id := p_fee_types_rec.dnz_chr_id;
5488 l_line_id := p_fee_types_rec.line_id;
5489 l_check_box_val := p_check_box_value;
5490 If okl_context.get_okc_org_id is null then
5491 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
5492 End If;
5493
5494 -- call START_ACTIVITY to create savepoint, check compatibility
5495 -- and initialize message list
5496 l_return_status := OKC_API.START_ACTIVITY(
5497 p_api_name => l_api_name,
5498 p_pkg_name => g_pkg_name,
5499 p_init_msg_list => p_init_msg_list,
5500 l_api_version => l_api_version,
5501 p_api_version => p_api_version,
5502 p_api_type => g_api_type,
5503 x_return_status => l_return_status);
5504
5505
5506 -- check if activity started successfully
5507 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5508 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5509 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5510 raise OKC_API.G_EXCEPTION_ERROR;
5511 End If;
5512
5513 -- initialize return variables
5514 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5515 -- added for bug 4524091
5516
5517 open get_k_details_csr (l_chr_id);
5518 fetch get_k_details_csr into l_start_date, l_end_date,l_orig_system_source_code;
5519 close get_k_details_csr;
5520
5521 if ((l_orig_system_source_code is not null) and (l_orig_system_source_code = 'OKL_REBOOK')) then
5522 -- not allowed to change RVI in T and C during rebook.
5523 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
5524 p_msg_name => 'OKL_RVI_STRM_CANNOT_CHANGED');
5525 RAISE OKC_API.G_EXCEPTION_ERROR;
5526 end if;
5527 -- end 4524091
5528
5529 If ( p_fee_types_rec.dnz_chr_id is null or p_fee_types_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) Then
5530 x_return_status := OKC_API.g_ret_sts_error;
5531 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_FEE_TYPE');
5532 OKC_API.SET_MESSAGE( p_app_name => g_app_name
5533 , p_msg_name => 'OKL_REQUIRED_VALUE'
5534 , p_token1 => 'COL_NAME'
5535 , p_token1_value => 'dnz_chr_id'
5536 );
5537 raise OKC_API.G_EXCEPTION_ERROR;
5538 End If;
5539
5540
5541
5542 open get_rvi_check_value_csr(l_chr_id);
5543 fetch get_rvi_check_value_csr into l_check_box_value;
5544 close get_rvi_check_value_csr;
5545
5546
5547 open get_pricing_engine_csr(l_chr_id);
5548 fetch get_pricing_engine_csr into l_price_engine;
5549 close get_pricing_engine_csr;
5550
5551
5552 open get_stream_name_csr(l_chr_id);
5553 fetch get_stream_name_csr into l_strm_id, l_strm_name;
5554 close get_stream_name_csr;
5555
5556
5557 open get_fee_line_id (l_chr_id);
5558 fetch get_fee_line_id into l_fee_line_id,l_amount;
5559 close get_fee_line_id;
5560
5561 -- p_check_box_value1 := 'Y';
5562 if (l_check_box_val = 'Y') then
5563 if ((l_price_engine is not null) and (l_price_engine = 'INTERNAL' ))THEN
5564 -- not allowed for internal stream generation
5565 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
5566 p_msg_name => 'OKL_LA_RVI_NO_ISG');
5567 RAISE OKC_API.G_EXCEPTION_ERROR;
5568 elsif ((l_price_engine is not null) and (l_price_engine = 'EXTERNAL' ))THEN
5569 -- it is external stream generation
5570
5571
5572 if ((p_fee_types_rec.item_id1 is null) or (p_fee_types_rec.item_id1 = OKL_API.G_MISS_CHAR)) THEN
5573 -- throw error message stm name is manditory
5574 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
5575 p_msg_name => 'OKL_RVI_STREAM_REQD');
5576 RAISE OKC_API.G_EXCEPTION_ERROR;
5577 end if;
5578
5579 if ((l_strm_id IS NOT NULL) AND (l_strm_id <> OKL_API.G_MISS_NUM)) THEN
5580
5581 open get_okc_k_items_csr (l_fee_line_id);
5582 fetch get_okc_k_items_csr into l_cim_id;
5583 close get_okc_k_items_csr;
5584
5585 l_fee_types_rec.line_id := l_fee_line_id;
5586 l_fee_types_rec.item_id := l_cim_id;
5587 -- l_fee_types_rec.fee_type := 'ABSORBED';
5588 -- l_fee_types_rec.dnz_chr_id := l_chr_id;
5589 l_fee_types_rec.item_name := p_fee_types_rec.item_name;
5590 l_fee_types_rec.item_id1 := to_char(p_fee_types_rec.item_id1);
5591 l_fee_types_rec.fee_purpose_code := 'RVI';
5592 l_fee_types_rec.amount := 0;
5593 l_fee_types_rec.effective_from := l_start_date;
5594 l_fee_types_rec.effective_to := l_end_date;
5595
5596 l_fee_types_rec.PARTY_ID := NULL;
5597 l_fee_types_rec.PARTY_NAME := NULL;
5598 l_fee_types_rec.PARTY_ID1 := NULL;
5599 l_fee_types_rec.PARTY_ID2 := NULL;
5600
5601 update_fee_type(
5602 p_api_version => p_api_version,
5603 p_init_msg_list => p_init_msg_list,
5604 x_return_status => l_return_status,
5605 x_msg_count => x_msg_count,
5606 x_msg_data => x_msg_data,
5607 p_fee_types_rec => l_fee_types_rec,
5608 x_fee_types_rec => x_fee_types_rec);
5609
5610
5611 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5612 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5613 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5614 RAISE OKC_API.G_EXCEPTION_ERROR;
5615 END IF;
5616
5617 ELSIF (p_fee_types_rec.item_id1 IS NOT NULL) THEN
5618
5619
5620 l_fee_types_rec.fee_type := 'ABSORBED';
5621 l_fee_types_rec.fee_purpose_code := 'RVI';
5622 l_fee_types_rec.amount := 0;
5623 l_fee_types_rec.effective_from := l_start_date;
5624 l_fee_types_rec.effective_to := l_end_date;
5625
5626 l_fee_types_rec.PARTY_ID := NULL;
5627 l_fee_types_rec.PARTY_NAME := NULL;
5628 l_fee_types_rec.PARTY_ID1 := NULL;
5629 l_fee_types_rec.PARTY_ID2 := NULL;
5630 l_fee_types_rec.INITIAL_DIRECT_COST := NULL;
5631
5632 create_fee_type(
5633 p_api_version => p_api_version,
5634 p_init_msg_list => p_init_msg_list,
5635 x_return_status => l_return_status,
5636 x_msg_count => x_msg_count,
5637 x_msg_data => x_msg_data,
5638 p_fee_types_rec => l_fee_types_rec,
5639 x_fee_types_rec => x_fee_types_rec);
5640
5641
5642 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5643 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5644 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5645 RAISE OKC_API.G_EXCEPTION_ERROR;
5646 END IF;
5647 end if; -- end of create
5648 end if; -- else
5649
5650 elsif (l_check_box_val = 'N') then
5651 if ((l_price_engine is not null) and (l_price_engine = 'EXTERNAL' ))THEN
5652
5653 if ((l_strm_id IS NOT NULL) AND (l_strm_id <> OKL_API.G_MISS_NUM)) THEN
5654 IF (l_check_box_value = 'Y') THEN
5655
5656 open get_okc_k_items_csr (l_fee_line_id);
5657 fetch get_okc_k_items_csr into l_cim_id;
5658 close get_okc_k_items_csr;
5659
5660 l_fee_types_rec.line_id := l_fee_line_id;
5661 l_fee_types_rec.item_id := l_cim_id;
5662 l_fee_types_rec.fee_purpose_code:= 'RVI_DUMMY'; -- since rvi can't be delted from delte screen
5663
5664 delete_fee_type(
5665 p_api_version => p_api_version,
5666 p_init_msg_list => p_init_msg_list,
5667 x_return_status => l_return_status,
5668 x_msg_count => x_msg_count,
5669 x_msg_data => x_msg_data,
5670 p_fee_types_rec => l_fee_types_rec);
5671
5672
5673 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5674 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5675 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5676 RAISE OKC_API.G_EXCEPTION_ERROR;
5677 END IF;
5678 end if;
5679 elsif ((p_fee_types_rec.item_id1 IS NOT null) AND (p_fee_types_rec.item_id1 <> OKL_API.G_MISS_CHAR)) then
5680 -- throw error message check box has to be checkd
5681 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
5682 p_msg_name => 'OKL_RVI_CHECK_BOX_REQD');
5683 RAISE OKC_API.G_EXCEPTION_ERROR;
5684 end if;
5685 end if;
5686 end if;
5687 -- set return variables
5688 x_return_status := l_return_status;
5689
5690 -- end the transaction
5691
5692 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
5693
5694 EXCEPTION
5695 when OKC_API.G_EXCEPTION_ERROR then
5696 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5697 p_api_name => l_api_name,
5698 p_pkg_name => g_pkg_name,
5699 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
5700 x_msg_count => x_msg_count,
5701 x_msg_data => x_msg_data,
5702 p_api_type => g_api_type);
5703
5704 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
5705 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5706 p_api_name => l_api_name,
5707 p_pkg_name => g_pkg_name,
5708 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
5709 x_msg_count => x_msg_count,
5710 x_msg_data => x_msg_data,
5711 p_api_type => g_api_type);
5712
5713 when OTHERS then
5714 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5715 p_api_name => l_api_name,
5716 p_pkg_name => g_pkg_name,
5717 p_exc_name => 'OTHERS',
5718 x_msg_count => x_msg_count,
5719 x_msg_data => x_msg_data,
5720 p_api_type => g_api_type);
5721
5722 END process_rvi_stream;
5723
5724
5725 PROCEDURE create_party(
5726 p_api_version IN NUMBER,
5727 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5728 x_return_status OUT NOCOPY VARCHAR2,
5729 x_msg_count OUT NOCOPY NUMBER,
5730 x_msg_data OUT NOCOPY VARCHAR2,
5731 p_kpl_rec IN party_rec_type,
5732 x_kpl_rec OUT NOCOPY party_rec_type
5733 ) AS
5734
5735 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5736 l_api_name CONSTANT varchar2(30) := 'create_party';
5737 l_api_version CONSTANT NUMBER := 1.0;
5738
5739 lp_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
5740 lx_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
5741 lp_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
5742 lx_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
5743
5744 Begin
5745 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5746 -- Call start_activity to create savepoint, check compatibility
5747 -- and initialize message list
5748 x_return_status := OKL_API.START_ACTIVITY (
5749 l_api_name
5750 ,p_init_msg_list
5751 ,'_PVT'
5752 ,x_return_status);
5753 -- Check if activity started successfully
5754 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5755 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5756 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5757 RAISE OKL_API.G_EXCEPTION_ERROR;
5758 END IF;
5759
5760 lp_cplv_rec.id := p_kpl_rec.id;
5761 lp_cplv_rec.object1_id1 := p_kpl_rec.object1_id1;
5762 lp_cplv_rec.object1_id2 := p_kpl_rec.object1_id2;
5763 lp_cplv_rec.jtot_object1_code := p_kpl_rec.jtot_object1_code;
5764 lp_cplv_rec.rle_code := p_kpl_rec.rle_code;
5765 lp_cplv_rec.dnz_chr_id := p_kpl_rec.dnz_chr_id;
5766 lp_cplv_rec.cle_id := p_kpl_rec.cle_id;
5767 lp_kplv_rec.attribute_category := p_kpl_rec.attribute_category;
5768 lp_kplv_rec.attribute1 := p_kpl_rec.attribute1;
5769 lp_kplv_rec.attribute2 := p_kpl_rec.attribute2;
5770 lp_kplv_rec.attribute3 := p_kpl_rec.attribute3;
5771 lp_kplv_rec.attribute4 := p_kpl_rec.attribute4;
5772 lp_kplv_rec.attribute5 := p_kpl_rec.attribute5;
5773 lp_kplv_rec.attribute6 := p_kpl_rec.attribute6;
5774 lp_kplv_rec.attribute7 := p_kpl_rec.attribute7;
5775 lp_kplv_rec.attribute8 := p_kpl_rec.attribute8;
5776 lp_kplv_rec.attribute9 := p_kpl_rec.attribute9;
5777 lp_kplv_rec.attribute10 := p_kpl_rec.attribute10;
5778 lp_kplv_rec.attribute11 := p_kpl_rec.attribute11;
5779 lp_kplv_rec.attribute12 := p_kpl_rec.attribute12;
5780 lp_kplv_rec.attribute13 := p_kpl_rec.attribute13;
5781 lp_kplv_rec.attribute14 := p_kpl_rec.attribute14;
5782 lp_kplv_rec.attribute15 := p_kpl_rec.attribute15;
5783
5784 IF(p_kpl_rec.rle_code IS NOT NULL AND
5785 NOT (p_kpl_rec.rle_code = 'LESSEE' OR p_kpl_rec.rle_code = 'LESSOR')) THEN
5786 lp_kplv_rec.validate_dff_yn := 'Y';
5787 END IF;
5788
5789 okl_k_party_roles_pvt.create_k_party_role(
5790 p_api_version => p_api_version,
5791 p_init_msg_list => p_init_msg_list,
5792 x_return_status => x_return_status,
5793 x_msg_count => x_msg_count,
5794 x_msg_data => x_msg_data,
5795 p_cplv_rec => lp_cplv_rec,
5796 x_cplv_rec => lx_cplv_rec,
5797 p_kplv_rec => lp_kplv_rec,
5798 x_kplv_rec => lx_kplv_rec);
5799
5800 x_kpl_rec.id := lx_cplv_rec.id;
5801
5802 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5803 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5804 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5805 RAISE OKL_API.G_EXCEPTION_ERROR;
5806 END IF;
5807
5808 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
5809
5810 EXCEPTION
5811 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5812 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5813 l_api_name,
5814 G_PKG_NAME,
5815 'OKL_API.G_RET_STS_ERROR',
5816 x_msg_count,
5817 x_msg_data,
5818 '_PVT');
5819 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5820 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5821 l_api_name,
5822 G_PKG_NAME,
5823 'OKL_API.G_RET_STS_UNEXP_ERROR',
5824 x_msg_count,
5825 x_msg_data,
5826 '_PVT');
5827 WHEN OTHERS THEN
5828 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5829 l_api_name,
5830 G_PKG_NAME,
5831 'OTHERS',
5832 x_msg_count,
5833 x_msg_data,
5834 '_PVT');
5835
5836 end;
5837
5838 PROCEDURE update_party(
5839 p_api_version IN NUMBER,
5840 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5841 x_return_status OUT NOCOPY VARCHAR2,
5842 x_msg_count OUT NOCOPY NUMBER,
5843 x_msg_data OUT NOCOPY VARCHAR2,
5844 p_kpl_rec IN party_rec_type,
5845 x_kpl_rec OUT NOCOPY party_rec_type
5846 ) AS
5847
5848 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5849 l_api_name CONSTANT varchar2(30) := 'update_party';
5850 l_api_version CONSTANT NUMBER := 1.0;
5851
5852 lp_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
5853 lx_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
5854 lp_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
5855 lx_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
5856
5857 Begin
5858 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5859 -- Call start_activity to create savepoint, check compatibility
5860 -- and initialize message list
5861 x_return_status := OKL_API.START_ACTIVITY (
5862 l_api_name
5863 ,p_init_msg_list
5864 ,'_PVT'
5865 ,x_return_status);
5866 -- Check if activity started successfully
5867 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5869 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5870 RAISE OKL_API.G_EXCEPTION_ERROR;
5871 END IF;
5872
5873 lp_cplv_rec.id := p_kpl_rec.id;
5874 lp_cplv_rec.object1_id1 := p_kpl_rec.object1_id1;
5875 lp_cplv_rec.object1_id2 := p_kpl_rec.object1_id2;
5876 lp_cplv_rec.rle_code := p_kpl_rec.rle_code;
5877 lp_cplv_rec.dnz_chr_id := p_kpl_rec.dnz_chr_id;
5878 lp_cplv_rec.cle_id := p_kpl_rec.cle_id;
5879 lp_kplv_rec.attribute_category := p_kpl_rec.attribute_category;
5880 lp_kplv_rec.attribute1 := p_kpl_rec.attribute1;
5881 lp_kplv_rec.attribute2 := p_kpl_rec.attribute2;
5882 lp_kplv_rec.attribute3 := p_kpl_rec.attribute3;
5883 lp_kplv_rec.attribute4 := p_kpl_rec.attribute4;
5884 lp_kplv_rec.attribute5 := p_kpl_rec.attribute5;
5885 lp_kplv_rec.attribute6 := p_kpl_rec.attribute6;
5886 lp_kplv_rec.attribute7 := p_kpl_rec.attribute7;
5887 lp_kplv_rec.attribute8 := p_kpl_rec.attribute8;
5888 lp_kplv_rec.attribute9 := p_kpl_rec.attribute9;
5889 lp_kplv_rec.attribute10 := p_kpl_rec.attribute10;
5890 lp_kplv_rec.attribute11 := p_kpl_rec.attribute11;
5891 lp_kplv_rec.attribute12 := p_kpl_rec.attribute12;
5892 lp_kplv_rec.attribute13 := p_kpl_rec.attribute13;
5893 lp_kplv_rec.attribute14 := p_kpl_rec.attribute14;
5894 lp_kplv_rec.attribute15 := p_kpl_rec.attribute15;
5895 lp_kplv_rec.validate_dff_yn := 'Y';
5896
5897 okl_k_party_roles_pvt.update_k_party_role(
5898 p_api_version => p_api_version,
5899 p_init_msg_list => p_init_msg_list,
5900 x_return_status => x_return_status,
5901 x_msg_count => x_msg_count,
5902 x_msg_data => x_msg_data,
5903 p_cplv_rec => lp_cplv_rec,
5904 x_cplv_rec => lx_cplv_rec,
5905 p_kplv_rec => lp_kplv_rec,
5906 x_kplv_rec => lx_kplv_rec);
5907
5908
5909 x_kpl_rec.id := lx_cplv_rec.id;
5910
5911 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5912 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5913 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5914 RAISE OKL_API.G_EXCEPTION_ERROR;
5915 END IF;
5916
5917 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
5918
5919 EXCEPTION
5920 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5921 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5922 l_api_name,
5923 G_PKG_NAME,
5924 'OKL_API.G_RET_STS_ERROR',
5925 x_msg_count,
5926 x_msg_data,
5927 '_PVT');
5928 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5929 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5930 l_api_name,
5931 G_PKG_NAME,
5932 'OKL_API.G_RET_STS_UNEXP_ERROR',
5933 x_msg_count,
5934 x_msg_data,
5935 '_PVT');
5936 WHEN OTHERS THEN
5937 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5938 l_api_name,
5939 G_PKG_NAME,
5940 'OTHERS',
5941 x_msg_count,
5942 x_msg_data,
5943 '_PVT');
5944
5945 end;
5946
5947 END OKL_MAINTAIN_FEE_PVT;