[Home] [Help]
PACKAGE BODY: APPS.OKL_QA_SECURITIZATION
Source
1 PACKAGE BODY OKL_QA_SECURITIZATION AS
2 /* $Header: OKLRSZQB.pls 120.15 2008/03/26 08:18:36 sosharma noship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : check_functional_constraints
7 -- Description :
8 -- Business Rules :
9 -- Parameters :
10 -- Version : 1.0
11 -- End of comments
12
13 PROCEDURE check_functional_constraints(
14 x_return_status OUT NOCOPY VARCHAR2,
15 p_chr_id IN NUMBER
16 ) IS
17
18 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
19 l_dummy VARCHAR2(1) := '?';
20 l_count NUMBER := 0;
21 l_row_notfound BOOLEAN;
22 l_token VARCHAR2(2000);
23
24 p_api_version NUMBER;
25 p_init_msg_list VARCHAR2(256) DEFAULT OKC_API.G_FALSE;
26 x_msg_count NUMBER;
27 x_msg_data VARCHAR2(256);
28
29 /* Bug# 2924696
30 CURSOR l_princ_csr ( chrID NUMBER ) IS
31 select nvl( total_principal_amount, -1) princi
32 from okl_pools
33 where khr_id = chrID;
34
35 l_princ_rec l_princ_csr%ROWTYPE;
36 */
37
38 CURSOR l_contract_name ( n VARCHAR2 ) IS
39 Select count(*) cnt
40 From okc_k_headers_v where contract_number = n;
41 l_cn l_contract_name%ROWTYPE;
42
43 CURSOR l_rev_strm_check_csr(p_chr_id IN NUMBER, p_cle_id IN NUMBER) IS
44 SELECT distinct pool.khr_id, pcon.sty_id, pcon.sty_code
45 FROM okl_pools pool, okl_pool_contents pcon
46 WHERE pool.id = pcon.pol_id
47 AND pool.khr_id = p_chr_id
48 AND not exists (
49 SELECT 'Y'
50 FROM okc_line_styles_b rev_style,
51 okl_k_lines revl,
52 okc_k_lines_b rev
53 WHERE rev.lse_id = rev_style.id
54 AND rev_style.lty_code = 'REVENUE_SHARE'
55 AND revl.id = rev.id
56 AND revl.sty_id = pcon.sty_id
57 AND rev.cle_id = p_cle_id );
58
59 CURSOR l_rev_strm_comp_csr(p_cle_id IN NUMBER) IS
60 SELECT rev.id, rev.cle_id, revl.sty_id,revl.percent_stake,
61 strm.stream_type_subclass
62 FROM okc_line_styles_b rev_style,
63 okl_k_lines revl,
64 okc_k_lines_b rev,
65 okl_strm_type_v strm
66 WHERE rev.lse_id = rev_style.id
67 AND rev_style.lty_code = 'REVENUE_SHARE'
68 AND revl.id = rev.id
69 AND rev.cle_id = p_cle_id
70 AND revl.sty_id = strm.id
71 ORDER BY strm.stream_type_subclass;
72
73 CURSOR strm_name_csr1(styid IN NUMBER) IS
74 SELECT tl.name name,
75 stm.stream_type_class stream_type_class,
76 stm.stream_type_subclass stream_type_subclass,
77 tl.description ALLOC_BASIS,
78 stm.capitalize_yn capitalize_yn,
79 stm.periodic_yn periodic_yn
80 FROM okl_strm_type_b stm,
81 OKL_STRM_TYPE_TL tl
82 WHERE tl.id = stm.id
83 and tl.language = 'US'
84 and stm.id = styid;
85
86 CURSOR inv_dbrmnt_csr(p_cle_id IN NUMBER) IS
87 SELECT name, stream_type_subclass
88 FROM okl_strm_type_v strm
89 WHERE stream_type_subclass = 'INVESTOR_DISBURSEMENT'
90 AND not exists (
91 SELECT 'Y'
92 FROM okc_line_styles_b rev_style,
93 okl_k_lines revl,
94 okc_k_lines_b rev
95 WHERE rev.lse_id = rev_style.id
96 AND rev_style.lty_code = 'REVENUE_SHARE'
97 AND revl.id = rev.id
98 AND revl.sty_id = strm.id
99 AND rev.cle_id = p_cle_id);
100
101 CURSOR l_fnd_meaning_csr(p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2) IS
102 SELECT fnd.meaning
103 FROM fnd_lookups fnd
104 WHERE fnd.lookup_type = p_lookup_type
105 AND fnd.lookup_code = p_lookup_code;
106
107 -- To check if revenue shares exist for each investor.
108 CURSOR l_okl_inv_sty_subclass_csr(p_investor_id IN NUMBER, p_stream_type_subclass IN VARCHAR2) IS
109 SELECT 1
110 FROM okl_k_lines kleb,
111 okc_k_lines_b cles,
112 okc_line_styles_b lseb
113 WHERE kleb.id = cles.id
114 AND cles.lse_id = lseb.id
115 AND cles.cle_id = p_investor_id
116 AND lseb.lty_code = 'REVENUE_SHARE'
117 AND kleb.stream_type_subclass = p_stream_type_subclass;
118
119 -- To check if the stream type subclass has been securitized.
120 CURSOR l_okl_poc_sty_subclass_csr(p_agreement_id IN NUMBER, p_stream_type_subclass IN VARCHAR2) IS
121 SELECT 1
122 FROM okl_pool_contents pocb,
123 okl_pools polb,
124 okl_strm_type_b styb
125 WHERE polb.khr_id = p_agreement_id
126 AND pocb.pol_id = polb.id
127 AND pocb.sty_id = styb.id
128 AND styb.stream_type_subclass = p_stream_type_subclass;
129
130 --Bug# 5032252 --start
131 CURSOR l_rev_share_per_csr(p_investor_id IN NUMBER, p_stream_type_subclass IN VARCHAR2) IS
132 SELECT kleb.percent_stake
133 FROM okl_k_lines kleb,
134 okc_k_lines_b cles,
135 okc_line_styles_b lseb
136 WHERE kleb.id = cles.id
137 AND cles.lse_id = lseb.id
138 AND cles.cle_id = p_investor_id
139 AND lseb.lty_code = 'REVENUE_SHARE'
140 AND kleb.stream_type_subclass = p_stream_type_subclass;
141
142 --Bug# 5032252 --end
143 --CURSOR for selecting wheteher payment line exist for INCOME fee or not
144 CURSOR payment_csr (p_agreement_id IN NUMBER )is
145 select hzp.party_name partyname
146 , okl_strmtyp.name streamtypename
147 FROM okc_k_lines_b cle
148 , okc_line_styles_b lse
149 , okl_k_lines kle
150 , okc_k_party_roles_b parb
151 , hz_parties hzp
152 , okc_k_items cit
153 , okl_strmtyp_source_v okl_strmtyp
154 WHERE lse.id = cle.lse_id
155 AND kle.id = cle.id
156 and kle.fee_type='INCOME'
157 AND lse.lty_code = 'FEE'
158 AND cit.dnz_chr_id = cle.dnz_chr_id
159 AND cle.sts_code <> ('ABANDONED')
160 AND cit.cle_id = cle.id
161 AND to_char(okl_strmtyp.id1) = cit.object1_id1
162 AND to_char(okl_strmtyp.id2) = cit.object1_id2
163 AND parb.cle_id = cle.id
164 and hzp.party_id=parb.OBJECT1_ID1
165 and cle.dnz_chr_id = p_agreement_id
166 AND not EXISTS(
167 SELECT 1
168 from okc_rule_groups_b rgp
169 , okc_rules_b rule
170 where rgp.id = rule.rgp_id
171 and rgp.rgd_code = 'LALEVL'
172 and rgp.dnz_chr_id = p_agreement_id
173 and rule.rule_information_category = 'LASLL'
174 AND rgp.cle_id = cle.id);
175
176 --Added by kthiruva on 01-Feb-2008
177 --Bug 6773285 - Start of Changes
178 CURSOR l_rev_share_dtls_csr(p_investor_id IN NUMBER) IS
179 SELECT kleb.stream_type_subclass,
180 kleb.percent_stake
181 FROM okl_k_lines kleb,
182 okc_k_lines_b cles,
183 okc_line_styles_b lseb
184 WHERE kleb.id = cles.id
185 AND cles.lse_id = lseb.id
186 AND cles.cle_id = p_investor_id
187 AND lseb.lty_code = 'REVENUE_SHARE';
188 --Bug 6773285 - End of Changes
189
190 l_exists VARCHAR2(1);
191 l_hdr l_hdr_csr%ROWTYPE;
192 l_lne l_lne_csr%ROWTYPE;
193 l_sub_lne l_lne_csr1%ROWTYPE;
194 payment_rec payment_csr%ROWTYPE;
195 i NUMBER;
196 n NUMBER;
197 l_princ_value NUMBER;
198 l_stream_value NUMBER;
199 l_invstr_rec invstr_csr%ROWTYPE;
200 ind NUMBER;
201 l_prev_percent_stake NUMBER;
202 strm_name_rec strm_name_csr1%ROWTYPE;
203 l_fnd_meaning_rec l_fnd_meaning_csr%ROWTYPE;
204 l_stream_type_subclass VARCHAR2(400);
205 l_subclass_prev_value varchar2(400);
206 l_subclass_curr_value varchar2(400);
207
208 /*Bug 6660196
209 sosharma added constant l_api_version
210 */
211 l_api_version CONSTANT NUMBER := 1.0;
212
213 --Bug# 5032252
214 l_rev_share_per okl_k_lines.PERCENT_STAKE%TYPE;
215 BEGIN
216 -- initialize return status
217 x_return_status := OKL_API.G_RET_STS_SUCCESS;
218
219 OPEN l_hdr_csr(p_chr_id);
220 FETCH l_hdr_csr into l_hdr;
221 IF l_hdr_csr%NOTFOUND THEN
222 RAISE G_EXCEPTION_HALT_VALIDATION;
223 END IF;
224 CLOSE l_hdr_csr;
225
226 OPEN l_contract_name(l_hdr.contract_number);
227 FETCH l_contract_name into l_cn;
228 IF l_contract_name%NOTFOUND THEN
229 RAISE G_EXCEPTION_HALT_VALIDATION;
230 END IF;
231 CLOSE l_contract_name;
232
233 If( l_cn.cnt > 1) Then
234 OKL_API.set_message(
235 p_app_name => G_APP_NAME,
236 p_msg_name => 'OKL_QA_AGMT_NOTUNQ');
237 -- notify caller of an error
238 x_return_status := OKL_API.G_RET_STS_ERROR;
239 End If;
240
241
242 OPEN l_lne_csr('INVESTMENT', p_chr_id);
243 FETCH l_lne_csr into l_lne;
244 If( l_lne_csr%NOTFOUND ) Then
245 OKL_API.set_message(
246 p_app_name => G_APP_NAME,
247 p_msg_name => 'OKL_QA_NO_SHARE_LINES');
248 -- notify caller of an error
249 x_return_status := OKL_API.G_RET_STS_ERROR;
250 RAISE G_EXCEPTION_HALT_VALIDATION;
251 END IF;
252 CLOSE l_lne_csr;
253
254 OPEN l_lne_csr('REVENUE_SHARE', p_chr_id);
255 FETCH l_lne_csr into l_lne;
256 If( l_lne_csr%NOTFOUND ) Then
257 OKL_API.set_message(
258 p_app_name => G_APP_NAME,
259 p_msg_name => 'OKL_QA_NO_REVENUE_LINES');
260 -- notify caller of an error
261 x_return_status := OKL_API.G_RET_STS_ERROR;
262 RAISE G_EXCEPTION_HALT_VALIDATION;
263 END IF;
264 CLOSE l_lne_csr;
265
266 OPEN payment_csr(p_chr_id) ;
267 FETCH payment_csr INTO payment_rec;
268 CLOSE payment_csr;
269 IF(payment_rec.streamtypename IS NOT NULL)
270 THEN
271 OKL_API.set_message(
272 p_app_name => G_APP_NAME,
273 p_msg_name => 'OKL_QA_INCOME_NO_PAYMENT',
274 p_token1 => 'INVESTORNMAE',
275 p_token1_value => payment_rec.partyname,
276 p_token2 => 'FEENAME',
277 p_token2_value => payment_rec.streamtypename);
278 x_return_status := OKL_API.G_RET_STS_ERROR;
279 END IF;
280
281 FOR l_lne IN l_lne_csr('INVESTMENT', p_chr_id)
282 LOOP
283
284 n := 0;
285 FOR l_sub_lne in l_lne_csr1( 'REVENUE_SHARE', p_chr_id)
286 LOOP
287 If ( l_lne.id = l_sub_lne.cle_id ) Then
288 n := n + 1;
289 End If;
290 END LOOP;
291
292 OPEN invstr_csr(p_chr_id, l_lne.id);
293 FETCH invstr_csr into l_invstr_rec;
294 CLOSE invstr_csr;
295
296 If ( n = 0) Then
297
298 OKL_API.set_message(
299 p_app_name => G_APP_NAME,
300 p_msg_name => 'OKL_QA_ASST_NO_RVNUELNS',
301 p_token1 => 'INVESTOR',
302 p_token1_value => l_invstr_rec.name);
303 x_return_status := OKL_API.G_RET_STS_ERROR;
304
305 End If;
306
307 --for the investor, checking for existence of revenue shares for streams of subclass
308 --LATE_CHARGE and LATE_INTEREST.
309 l_stream_type_subclass := 'LATE_CHARGE';
310 OPEN l_okl_inv_sty_subclass_csr(l_lne.id , l_stream_type_subclass);
311 FETCH l_okl_inv_sty_subclass_csr INTO l_exists;
312 IF( l_okl_inv_sty_subclass_csr%NOTFOUND) Then
313 OKL_API.set_message(
314 p_app_name => G_APP_NAME,
315 p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
316 p_token1 => 'INVESTOR',
317 p_token1_value => l_invstr_rec.name,
318 p_token3 => 'SUB_CLASS',
319 p_token3_value => l_stream_type_subclass);
320 x_return_status := OKL_API.G_RET_STS_ERROR;
321 END IF;
322 CLOSE l_okl_inv_sty_subclass_csr;
323
324 l_stream_type_subclass := 'LATE_INTEREST';
325 OPEN l_okl_inv_sty_subclass_csr(l_lne.id , l_stream_type_subclass);
326 FETCH l_okl_inv_sty_subclass_csr INTO l_exists;
327 IF( l_okl_inv_sty_subclass_csr%NOTFOUND) Then
328 OKL_API.set_message(
329 p_app_name => G_APP_NAME,
330 p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
331 p_token1 => 'INVESTOR',
332 p_token1_value => l_invstr_rec.name,
333 p_token3 => 'SUB_CLASS',
334 p_token3_value => l_stream_type_subclass);
335 x_return_status := OKL_API.G_RET_STS_ERROR;
336 END IF;
337 CLOSE l_okl_inv_sty_subclass_csr;
338
339 --for the investor, checking for existence of streams of subclass RENT
340 --and RESIDUAL if these stream type subclasses have been securitized.
341 l_stream_type_subclass := 'RENT';
342 OPEN l_okl_poc_sty_subclass_csr(p_chr_id, l_stream_type_subclass);
343 FETCH l_okl_poc_sty_subclass_csr INTO l_exists;
344 IF (l_okl_poc_sty_subclass_csr%FOUND) Then
345 OPEN l_okl_inv_sty_subclass_csr(l_lne.id , l_stream_type_subclass);
346 FETCH l_okl_inv_sty_subclass_csr INTO l_exists;
347 IF( l_okl_inv_sty_subclass_csr%NOTFOUND) Then
348 OKL_API.set_message(
349 p_app_name => G_APP_NAME,
350 p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
351 p_token1 => 'INVESTOR',
352 p_token1_value => l_invstr_rec.name,
353 p_token3 => 'SUB_CLASS',
354 p_token3_value => l_stream_type_subclass);
355 x_return_status := OKL_API.G_RET_STS_ERROR;
356 END IF;
357 CLOSE l_okl_inv_sty_subclass_csr;
358
359 --Bug# 5032252 --start
360 OPEN l_rev_share_per_csr(l_lne.id , l_stream_type_subclass);
361 FETCH l_rev_share_per_csr INTO l_rev_share_per;
362 IF (l_rev_share_per = 0) THEN
363 OKL_API.set_message(
364 p_app_name => G_APP_NAME,
365 p_msg_name => 'OKL_QA_INV_REV_SHARE_ZERO',
366 p_token1 => 'SUB_CLASS',
367 p_token1_value => l_stream_type_subclass);
368 x_return_status := OKL_API.G_RET_STS_ERROR;
369 END IF;
370 CLOSE l_rev_share_per_csr;
371 --Bug# 5032252 --end
372
373 END IF;
374 CLOSE l_okl_poc_sty_subclass_csr;
375 l_stream_type_subclass := 'RESIDUAL';
376 OPEN l_okl_poc_sty_subclass_csr(p_chr_id, l_stream_type_subclass);
377 FETCH l_okl_poc_sty_subclass_csr INTO l_exists;
378 IF (l_okl_poc_sty_subclass_csr%FOUND) Then
379 OPEN l_okl_inv_sty_subclass_csr(l_lne.id , l_stream_type_subclass);
380 FETCH l_okl_inv_sty_subclass_csr INTO l_exists;
381 IF( l_okl_inv_sty_subclass_csr%NOTFOUND) Then
382 OKL_API.set_message(
383 p_app_name => G_APP_NAME,
384 p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
385 p_token1 => 'INVESTOR',
386 p_token1_value => l_invstr_rec.name,
387 p_token3 => 'SUB_CLASS',
388 p_token3_value => l_stream_type_subclass);
389 x_return_status := OKL_API.G_RET_STS_ERROR;
390 END IF;
391 CLOSE l_okl_inv_sty_subclass_csr;
392
393 --Bug# 5032252 --start
394 OPEN l_rev_share_per_csr(l_lne.id , l_stream_type_subclass);
395 FETCH l_rev_share_per_csr INTO l_rev_share_per;
396 IF (l_rev_share_per = 0) THEN
397 OKL_API.set_message(
398 p_app_name => G_APP_NAME,
399 p_msg_name => 'OKL_QA_INV_REV_SHARE_ZERO',
400 p_token1 => 'SUB_CLASS',
401 p_token1_value => l_stream_type_subclass);
402 x_return_status := OKL_API.G_RET_STS_ERROR;
403 END IF;
404 CLOSE l_rev_share_per_csr;
405 --Bug# 5032252 --end
406
407 END IF;
408 CLOSE l_okl_poc_sty_subclass_csr;
409 -----------------------------------------------------------------------------
410 --Bug # 674000 ssdeshpa start
411 --for the investor, checking for existence of streams of subclass LOAN_PAYMENT
412 --if these stream type subclasses have been securitized.
413 l_stream_type_subclass := 'LOAN_PAYMENT';
414 OPEN l_okl_poc_sty_subclass_csr(p_chr_id, l_stream_type_subclass);
415 FETCH l_okl_poc_sty_subclass_csr INTO l_exists;
416 IF (l_okl_poc_sty_subclass_csr%FOUND) Then
417 OPEN l_okl_inv_sty_subclass_csr(l_lne.id , l_stream_type_subclass);
418 FETCH l_okl_inv_sty_subclass_csr INTO l_exists;
419 IF( l_okl_inv_sty_subclass_csr%NOTFOUND) Then
420 OKL_API.set_message(
421 p_app_name => G_APP_NAME,
422 p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
423 p_token1 => 'INVESTOR',
424 p_token1_value => l_invstr_rec.name,
425 p_token3 => 'SUB_CLASS',
426 p_token3_value => l_stream_type_subclass);
427 x_return_status := OKL_API.G_RET_STS_ERROR;
428 END IF;
429 CLOSE l_okl_inv_sty_subclass_csr;
430 OPEN l_rev_share_per_csr(l_lne.id , l_stream_type_subclass);
431 FETCH l_rev_share_per_csr INTO l_rev_share_per;
432 IF (l_rev_share_per = 0) THEN
433 OKL_API.set_message(
434 p_app_name => G_APP_NAME,
435 p_msg_name => 'OKL_QA_INV_REV_SHARE_ZERO',
436 p_token1 => 'SUB_CLASS',
437 p_token1_value => l_stream_type_subclass);
438 x_return_status := OKL_API.G_RET_STS_ERROR;
439 END IF;
440 CLOSE l_rev_share_per_csr;
441 END IF;
442 CLOSE l_okl_poc_sty_subclass_csr;
443 --Bug 674000 ssdeshpa end
444 --------------------------------------------------------------------------
445 --Ensure that Revenue Share is defined only if the stream type class is securitised
446 FOR l_rev_share_dtls_rec IN l_rev_share_dtls_csr(l_lne.id)
447 LOOP
448 IF l_rev_share_dtls_rec.stream_type_subclass = 'RENT'
449 THEN
450 l_stream_type_subclass := 'RENT';
451 --Check if pool contents exist for this subclass, if not an error needs to be raised
452 --that revenue share has been defined without the stream being securitized
453 OPEN l_okl_poc_sty_subclass_csr(p_chr_id,l_stream_type_subclass);
454 FETCH l_okl_poc_sty_subclass_csr INTO l_exists;
455 IF (l_okl_poc_sty_subclass_csr%NOTFOUND)
456 THEN
457 OKL_API.set_message(
458 p_app_name => G_APP_NAME,
459 p_msg_name => 'OKL_QA_REV_SHARE_INVALID',
460 p_token1 => 'SUB_CLASS',
461 p_token1_value => l_stream_type_subclass);
462 END IF;
463 CLOSE l_okl_poc_sty_subclass_csr;
464 END IF;
465
466
467 IF l_rev_share_dtls_rec.stream_type_subclass = 'RESIDUAL'
468 THEN
469 l_stream_type_subclass := 'RESIDUAL';
470 --Check if pool contents exist for this subclass, if not an error needs to be raised
471 --that revenue share has been defined without the stream being securitized
472
473
474 OPEN l_okl_poc_sty_subclass_csr(p_chr_id, l_stream_type_subclass);
475 FETCH l_okl_poc_sty_subclass_csr INTO l_exists;
476 IF (l_okl_poc_sty_subclass_csr%NOTFOUND)
477 THEN
478 OKL_API.set_message(
479 p_app_name => G_APP_NAME,
480 p_msg_name => 'OKL_QA_REV_SHARE_INVALID',
481 p_token1 => 'SUB_CLASS',
482 p_token1_value => l_stream_type_subclass);
483 END IF;
484 CLOSE l_okl_poc_sty_subclass_csr;
485 END IF;
486
487 IF l_rev_share_dtls_rec.stream_type_subclass = 'LOAN_PAYMENT'
488 THEN
489 l_stream_type_subclass := 'LOAN_PAYMENT';
490 --Check if pool contents exist for this subclass, if not an error needs to be raised
491 --that revenue share has been defined without the stream being securitized
492 OPEN l_okl_poc_sty_subclass_csr(p_chr_id,l_stream_type_subclass);
493 FETCH l_okl_poc_sty_subclass_csr INTO l_exists;
494 IF (l_okl_poc_sty_subclass_csr%NOTFOUND)
495 THEN
496 OKL_API.set_message(
497 p_app_name => G_APP_NAME,
498 p_msg_name => 'OKL_QA_REV_SHARE_INVALID',
499 p_token1 => 'SUB_CLASS',
500 p_token1_value => l_stream_type_subclass);
501 END IF;
502 CLOSE l_okl_poc_sty_subclass_csr;
503 END IF;
504 END LOOP;
505
506 /*
507 *FOR l_rev_strm_check_rec IN l_rev_strm_check_csr(p_chr_id => p_chr_id, p_cle_id => l_lne.id)
508 *LOOP
509 *
510 * OPEN strm_name_csr1 (styid => l_rev_strm_check_rec.sty_id);
511 * FETCH strm_name_csr1 INTO strm_name_rec;
512 * CLOSE strm_name_csr1;
513 *
514 * OPEN l_fnd_meaning_csr(p_lookup_type => 'OKL_STREAM_TYPE_SUBCLASS',
515 * p_lookup_code => strm_name_rec.stream_type_subclass);
516 * FETCH l_fnd_meaning_csr INTO l_fnd_meaning_rec;
517 * CLOSE l_fnd_meaning_csr;
518 *
519 * -- set message and continue in loop
520 * OKL_API.set_message(
521 * p_app_name => G_APP_NAME,
522 * p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
523 * p_token1 => 'INVESTOR',
524 * p_token1_value => l_invstr_rec.name,
525 * p_token2 => 'STRM_TYPE',
526 * p_token2_value => strm_name_rec.name,
527 * p_token3 => 'SUB_CLASS',
528 * p_token3_value => l_fnd_meaning_rec.meaning);
529 * x_return_status := OKL_API.G_RET_STS_ERROR;
530 *
531 *END LOOP;
532 *
533 *FOR inv_dbrmnt_rec IN inv_dbrmnt_csr(p_cle_id => l_lne.id)
534 *LOOP
535 * OPEN l_fnd_meaning_csr(p_lookup_type => 'OKL_STREAM_TYPE_SUBCLASS',
536 * p_lookup_code => inv_dbrmnt_rec.stream_type_subclass);
537 * FETCH l_fnd_meaning_csr INTO l_fnd_meaning_rec;
538 * CLOSE l_fnd_meaning_csr;
539 *
540 * -- set message and continue in loop
541 * OKL_API.set_message(
542 * p_app_name => G_APP_NAME,
543 * p_msg_name => 'OKL_QA_INV_STRM_MISMATCH',
544 * p_token1 => 'INVESTOR',
545 * p_token1_value => l_invstr_rec.name,
546 * p_token2 => 'STRM_TYPE',
547 * p_token2_value => inv_dbrmnt_rec.name,
548 * p_token3 => 'SUB_CLASS',
549 * p_token3_value => l_fnd_meaning_rec.meaning);
550 * x_return_status := OKL_API.G_RET_STS_ERROR;
551 *END LOOP;
552 *
553 *ind := 0;
554 *
555 *FOR l_rev_strm_comp_rec IN l_rev_strm_comp_csr(p_cle_id => l_lne.id)
556 *LOOP
557 * -- the share percentages for each revenue stream within a stream
558 * -- type subclass must be the same.
559 * l_subclass_curr_value := l_rev_strm_comp_rec.stream_type_subclass;
560 * If(ind = 0) Then
561 * l_subclass_prev_value := l_rev_strm_comp_rec.stream_type_subclass;
562 * l_prev_percent_stake := l_rev_strm_comp_rec.percent_stake;
563 * ind := 1;
564 * ElsIf( l_subclass_prev_value <> l_subclass_curr_value) Then
565 * l_subclass_prev_value := l_subclass_curr_value;
566 * l_prev_percent_stake := l_rev_strm_comp_rec.percent_stake;
567 * Else
568 * If (l_prev_percent_stake <> l_rev_strm_comp_rec.percent_stake) Then
569 * OPEN strm_name_csr1 (styid => l_rev_strm_comp_rec.sty_id);
570 * FETCH strm_name_csr1 INTO strm_name_rec;
571 * CLOSE strm_name_csr1;
572 *
573 * OPEN l_fnd_meaning_csr(p_lookup_type => 'OKL_STREAM_TYPE_SUBCLASS',
574 * p_lookup_code => strm_name_rec.stream_type_subclass);
575 * FETCH l_fnd_meaning_csr INTO l_fnd_meaning_rec;
576 * CLOSE l_fnd_meaning_csr;
577 *
578 * OKL_API.set_message(
579 * p_app_name => G_APP_NAME,
580 * p_msg_name => 'OKL_QA_INV_DIFF_STAKE',
581 * p_token1 => 'INVESTOR',
582 * p_token1_value => l_invstr_rec.name,
583 * p_token2 => 'SUB_CLASS',
584 * p_token2_value => l_fnd_meaning_rec.meaning);
585 * x_return_status := OKL_API.G_RET_STS_ERROR;
586 * End If;
587 * End If;
588 *
589 *END LOOP;
590 */
591
592 END LOOP;
593
594 /* Removed as per Bug# 2924696 , 04/25/2004
595 OPEN l_princ_csr( p_chr_id );
596 FETCH l_princ_csr INTO l_princ_rec;
597 CLOSE l_princ_csr;
598
599 If( l_princ_rec.princi <= 0 ) Then
600
601 OKL_API.set_message(
602 p_app_name => G_APP_NAME,
603 p_msg_name => 'OKL_QA_PRINC_VALUE');
604 -- notify caller of an error
605 x_return_status := OKL_API.G_RET_STS_ERROR;
606 END IF;
607 */
608
609 OKL_POOL_PVT.get_tot_receivable_amt(
610 p_api_version => l_api_version , --Bug 6660196
611 p_init_msg_list => p_init_msg_list,
612 x_return_status => l_return_status,
613 x_msg_count => x_msg_count,
614 x_msg_data => x_msg_data,
615 x_value => l_stream_value,
616 p_khr_id => p_chr_id );
617
618 If( l_stream_value IS NULL OR l_stream_value = 0 ) Then
619 OKL_API.set_message(
620 p_app_name => G_APP_NAME,
621 p_msg_name => 'OKL_QA_STREAM_VALUE');
622 -- notify caller of an error
623 x_return_status := OKL_API.G_RET_STS_ERROR;
624 END IF;
625
626
627 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
628 OKL_API.set_message(
629 p_app_name => G_APP_NAME,
630 p_msg_name => G_QA_SUCCESS);
631 END IF;
632
633
634 EXCEPTION
635
636 WHEN G_EXCEPTION_HALT_VALIDATION THEN
637 -- no processing necessary; validation can continue with next column
638 IF l_lne_csr%ISOPEN THEN
639 CLOSE l_lne_csr;
640 END IF;
641 IF l_hdr_csr%ISOPEN THEN
642 CLOSE l_hdr_csr;
643 END IF;
644 IF l_okl_inv_sty_subclass_csr%ISOPEN THEN
645 CLOSE l_okl_inv_sty_subclass_csr;
646 END IF;
647 IF l_okl_poc_sty_subclass_csr%ISOPEN THEN
648 CLOSE l_okl_poc_sty_subclass_csr;
649 END IF;
650 --Bug 5032252
651 IF l_rev_share_per_csr%ISOPEN THEN
652 CLOSE l_rev_share_per_csr;
653 END IF;
654
655 IF l_okl_poc_sty_subclass_csr%ISOPEN THEN
656 CLOSE l_okl_poc_sty_subclass_csr;
657 END IF;
658 WHEN OTHERS THEN
659 -- store SQL error message on message stack
660 OKL_API.SET_MESSAGE(
661 p_app_name => G_APP_NAME,
662 p_msg_name => G_UNEXPECTED_ERROR,
663 p_token1 => G_SQLCODE_TOKEN,
664 p_token1_value => SQLCODE,
665 p_token2 => G_SQLERRM_TOKEN,
666 p_token2_value => SQLERRM);
667 -- notify caller of an error as UNEXPETED error
668 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
669 -- verify that cursor was closed
670 IF l_lne_csr%ISOPEN THEN
671 CLOSE l_lne_csr;
672 END IF;
673 IF l_hdr_csr%ISOPEN THEN
674 CLOSE l_hdr_csr;
675 END IF;
676 IF l_okl_inv_sty_subclass_csr%ISOPEN THEN
677 CLOSE l_okl_inv_sty_subclass_csr;
678 END IF;
679 IF l_okl_poc_sty_subclass_csr%ISOPEN THEN
680 CLOSE l_okl_poc_sty_subclass_csr;
681 END IF;
682 --Bug 5032252
683 IF l_rev_share_per_csr%ISOPEN THEN
684 CLOSE l_rev_share_per_csr;
685 END IF;
686
687 IF l_okl_poc_sty_subclass_csr%ISOPEN THEN
688 CLOSE l_okl_poc_sty_subclass_csr;
689 END IF;
690
691 END check_functional_constraints;
692
693 -- Start of comments
694 --
695 -- Procedure Name : check_rule_constraints
696 -- Description :
697 -- Business Rules :
698 -- Parameters :
699 -- Version : 1.0
700 -- End of comments
701
702 PROCEDURE check_rule_constraints(
703 x_return_status OUT NOCOPY VARCHAR2,
704 p_chr_id IN NUMBER
705 ) IS
706
707 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
708 l_dummy VARCHAR2(1) := '?';
709 l_count NUMBER := 0;
710 l_row_notfound BOOLEAN;
711 l_token VARCHAR2(2000);
712
713 l_hdr l_hdr_csr%ROWTYPE;
714 l_lne l_lne_csr%ROWTYPE;
715
716 l_lnerl_rec l_lnerl_csr%ROWTYPE;
717 l_hdrrl_rec l_hdrrl_csr%ROWTYPE;
718 l_fnd_rec fnd_csr%ROWTYPE;
719 l_t_and_c_rec t_and_c_csr%ROWTYPE;
720 l_invstr_rec invstr_csr%ROWTYPE;
721
722 i NUMBER;
723
724 BEGIN
725
726 -- initialize return status
727 x_return_status := OKL_API.G_RET_STS_SUCCESS;
728
729 OPEN l_hdr_csr(p_chr_id);
730 FETCH l_hdr_csr into l_hdr;
731 IF l_hdr_csr%NOTFOUND THEN
732 RAISE G_EXCEPTION_HALT_VALIDATION;
733 END IF;
734 CLOSE l_hdr_csr;
735
736 OPEN l_hdrrl_csr('LASEIR', 'LASEIR', TO_NUMBER(p_chr_id)); -- Agreement rules
737 FETCH l_hdrrl_csr into l_hdrrl_rec;
738 If( l_hdrrl_csr%NOTFOUND ) Then
739
740 OPEN t_and_c_csr('OKLLASECLASEIR');
741 FETCH t_and_c_csr into l_t_and_c_rec;
742 CLOSE t_and_c_csr;
743
744 OKL_API.set_message(
745 p_app_name => G_APP_NAME,
746 p_msg_name => 'OKL_QA_NOAGMT_RULES',
747 p_token1 => 'RULE_GROUP_NAME',
748 p_token1_value => l_t_and_c_rec.meaning);
749 -- notify caller of an error
750 x_return_status := OKL_API.G_RET_STS_ERROR;
751 Else
752 If (l_hdrrl_rec.rule_information1 = 'YIELD'
753 AND (l_hdr.after_tax_yield = 0 OR l_hdr.after_tax_yield is null)) Then
754 OKL_API.set_message(
755 p_app_name => G_APP_NAME,
756 p_msg_name => 'OKL_QA_YLD_DBRSMT');
757 x_return_status := OKL_API.G_RET_STS_ERROR;
758 End If;
759
760 End If;
761 CLOSE l_hdrrl_csr;
762
763 OPEN l_hdrrl_csr('LASEBB', 'LASEFM', TO_NUMBER(p_chr_id)); -- Agreement rules
764 FETCH l_hdrrl_csr into l_hdrrl_rec;
765 If( l_hdrrl_csr%NOTFOUND OR l_hdrrl_rec.rule_information1 IS NULL) Then
766 OKL_API.set_message(
767 p_app_name => G_APP_NAME,
768 p_msg_name => 'OKL_QA_BUYBACK_FORMULA');
769 x_return_status := OKL_API.G_RET_STS_ERROR;
770 End If;
771 CLOSE l_hdrrl_csr;
772
773 FOR l_lne IN l_lne_csr('INVESTMENT', p_chr_id)
774 LOOP
775
776 OPEN l_lnerl_csr('LASEDB', 'LASEDB', TO_NUMBER(p_chr_id), l_lne.id);
777 FETCH l_lnerl_csr into l_lnerl_rec;
778 If( l_lnerl_csr%NOTFOUND ) Then
779
780 OPEN t_and_c_csr('OKLLASECLASEIR');
781 FETCH t_and_c_csr into l_t_and_c_rec;
782 CLOSE t_and_c_csr;
783
784 OPEN invstr_csr(p_chr_id, l_lne.id);
785 FETCH invstr_csr into l_invstr_rec;
786 CLOSE invstr_csr;
787
788 OKL_API.set_message(
789 p_app_name => G_APP_NAME,
790 p_msg_name => 'OKL_QA_NOSHR_RULES',
791 p_token1 => 'RULE_GROUP_NAME',
792 p_token1_value => l_t_and_c_rec.meaning,
793 p_token2 => 'INVESTOR',
794 p_token2_value => l_invstr_rec.name);
795 x_return_status := OKL_API.G_RET_STS_ERROR;
796 End If;
797 CLOSE l_lnerl_csr;
798
799 END LOOP;
800
801 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
802 OKL_API.set_message(
803 p_app_name => G_APP_NAME,
804 p_msg_name => G_QA_SUCCESS);
805 END IF;
806
807 EXCEPTION
808 WHEN G_EXCEPTION_HALT_VALIDATION THEN
809 -- no processing necessary; validation can continue with next column
810 NULL;
811 WHEN OTHERS THEN
812 -- store SQL error message on message stack
813 OKL_API.SET_MESSAGE(
814 p_app_name => G_APP_NAME,
815 p_msg_name => G_UNEXPECTED_ERROR,
816 p_token1 => G_SQLCODE_TOKEN,
817 p_token1_value => SQLCODE,
818 p_token2 => G_SQLERRM_TOKEN,
819 p_token2_value => SQLERRM);
820 -- notify caller of an error as UNEXPETED error
821 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
822 -- verify that cursor was closed
823 IF l_lnerl_csr%ISOPEN THEN
824 CLOSE l_lnerl_csr;
825 END IF;
826 IF l_hdr_csr%ISOPEN THEN
827 CLOSE l_hdr_csr;
828 END IF;
829 IF l_lne_csr%ISOPEN THEN
830 CLOSE l_lne_csr;
831 END IF;
832 IF l_hdrrl_csr%ISOPEN THEN
833 CLOSE l_hdrrl_csr;
834 END IF;
835 IF fnd_csr%ISOPEN THEN
836 CLOSE fnd_csr;
837 END IF;
838 IF t_and_c_csr%ISOPEN THEN
839 CLOSE t_and_c_csr;
840 END IF;
841 IF invstr_csr%ISOPEN THEN
842 CLOSE invstr_csr;
843 END IF;
844 END check_rule_constraints;
845
846
847
848 -- Start of comments
849 --
850 -- Procedure Name : check_ia_type_for_strms
851 -- Description : Check whether the streams in a contract are associated
852 -- with the same type of Investor Agreements if they are in
853 -- different Investor Agreements
854 -- Business Rules :
855 -- Parameters :
856 -- Version : 1.0
857 -- End of comments
858
859 PROCEDURE check_ia_type_for_strms(
860 x_return_status OUT NOCOPY VARCHAR2,
861 p_chr_id IN NUMBER
862 ) IS
863
864 CURSOR get_chr_id (p_chr_id IN NUMBER) IS
865 SELECT DISTINCT(poc.khr_id)
866 FROM okl_pool_contents poc, okl_pools pol
867 WHERE pol.khr_id = p_chr_id
868 AND pol.id = poc.pol_id;
869
870 -- Get the sec type for the ia, convert the old product --
871 -- value SALE to SECURITIZATION and LOAN to SYNDICATION --
872 CURSOR ia_type_csr (p_khr_id IN NUMBER) IS
873 SELECT a.khr_id,
874 decode(c.securitization_type, 'SALE','SECURITIZATION','LOAN','SYNDICATION',
875 'SECURITIZATION','SECURITIZATION','SYNDICATION','SYNDICATION') securitization_type,
876 b.contract_number
877 FROM okl_pool_contents a, okc_k_headers_b b,okl_k_headers c, okl_pools d
878 WHERE a.khr_id = p_khr_id
879 AND a.pol_id=d.id
880 AND d.khr_id= c.id
881 AND b.id=c.id;
882
883 l_khr_id number;
884
885 l_previous_type okl_k_headers.securitization_type%TYPE;
886 l_previous_khr_id NUMBER;
887
888 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
889 l_token VARCHAR2(2000);
890
891 BEGIN
892
893 -- initialize return status
894 x_return_status := OKL_API.G_RET_STS_SUCCESS;
895
896 OPEN get_chr_id (p_chr_id);
897 LOOP
898 FETCH get_chr_id INTO l_khr_id;
899 EXIT WHEN get_chr_id%NOTFOUND;
900
901 FOR ia_type_rec IN ia_type_csr (l_khr_id) LOOP
902
903 IF (NVL(l_previous_khr_id,ia_type_rec.khr_id) = ia_type_rec.khr_id AND
904 NVL(l_previous_type,ia_type_rec.securitization_type) <> ia_type_rec.securitization_type) THEN
905 OKL_API.set_message(
906 p_app_name => G_APP_NAME,
907 p_msg_name => 'OKL_IA_STRM_IA_TYPES',
908 p_token1 => 'CONTRACT_NUMBER',
909 p_token1_value => ia_type_rec.contract_number);
910 -- notify caller of an error
911 x_return_status := OKL_API.G_RET_STS_ERROR;
912 END IF;
913 l_previous_khr_id := ia_type_rec.khr_id;
914 l_previous_type := ia_type_rec.securitization_type;
915
916 END LOOP;
917 END LOOP;
918
919 CLOSE get_chr_id;
920
921 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
922 OKL_API.set_message(
923 p_app_name => G_APP_NAME,
924 p_msg_name => G_QA_SUCCESS);
925 END IF;
926
927 EXCEPTION
928 WHEN G_EXCEPTION_HALT_VALIDATION THEN
929 -- no processing necessary; validation can continue with next column
930 NULL;
931 WHEN OTHERS THEN
932 -- store SQL error message on message stack
933 OKL_API.SET_MESSAGE(
934 p_app_name => G_APP_NAME,
935 p_msg_name => G_UNEXPECTED_ERROR,
936 p_token1 => G_SQLCODE_TOKEN,
937 p_token1_value => SQLCODE,
938 p_token2 => G_SQLERRM_TOKEN,
939 p_token2_value => SQLERRM);
940 -- notify caller of an error as UNEXPETED error
941 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
942 -- verify that cursor was closed
943 IF get_chr_id%ISOPEN THEN
944 CLOSE get_chr_id;
945 END IF;
946 IF ia_type_csr%ISOPEN THEN
947 CLOSE ia_type_csr;
948 END IF;
949 END check_ia_type_for_strms;
950
951
952 END OKL_QA_SECURITIZATION;