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