[Home] [Help]
PACKAGE BODY: APPS.OKL_ISG_UTILS_PVT
Source
1 PACKAGE BODY OKL_ISG_UTILS_PVT AS
2 /* $Header: OKLRIGUB.pls 120.8 2007/10/12 20:10:08 djanaswa ship $ */
3
4
5 -- Start of comments
6 --
7 -- Procedure Name : get_primary_stream_type
8 -- Description : Return Primary Stream type for given purpose code
9 -- Business Rules :
10 -- Parameters : khr_id
11 -- : Primary_sty_purpose
12 -- Version : 1.0
13 -- : 2.0 Now passing contract deal type to get the stream type
14 ---- 3.0 Added code to support multi GAPP product
15 -- End of comments
16
17 PROCEDURE get_primary_stream_type
18 (
19 p_khr_id IN NUMBER,
20 p_pdt_id IN NUMBER,
21 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_primary_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
24 x_primary_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE
25 )
26
27 IS
28
29 CURSOR get_k_info_csr( l_khr_id NUMBER ) IS
30 SELECT
31
32 pdt.id pdt_id,
33 chr.start_date,
34 khr.deal_type,
35 nvl(pdt.reporting_pdt_id, -1) report_pdt_id
36 FROM okc_k_headers_v chr,
37 okl_k_headers khr,
38 okl_products_v pdt
39 WHERE khr.id = chr.id
40 AND chr.id = l_khr_id
41 AND khr.pdt_id = pdt.id(+);
42
43 CURSOR get_primary_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
44 SELECT
45 TLN.PRIMARY_STY_ID,
46 STY.NAME PRIMARY_STY_NAME
47 FROM
48 OKL_ST_GEN_TMPT_LNS TLN,
49 OKL_ST_GEN_TEMPLATES TMPT,
50 OKL_ST_GEN_TMPT_SETS Tst,
51 OKL_AE_TMPT_SETS AES,
52 OKL_PRODUCTS_V PDT,
53 OKL_STRM_TYPE_v STY
54
55 WHERE
56 TLN.GTT_ID = TMPT.ID AND
57 TMPT.GTS_ID = Tst.ID AND
58 Tst.ID = AES.GTS_ID AND
59 --TST.deal_type = p_deal_type AND
60 AES.ID = PDT.AES_ID AND
61 TLN.PRIMARY_STY_ID = STY.ID
62 AND TLN.PRIMARY_YN = 'Y'
63 AND PDT.ID = l_pdt_id
64 AND (TMPT.START_DATE <= l_contract_start_date)
65 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
66 AND STY.STREAM_TYPE_PURPOSE = p_primary_sty_purpose;
67
68 l_product_id NUMBER;
69 l_deal_type okl_k_headers.deal_type%Type;
70 l_report_product_id NUMBER;
71 l_contract_start_date DATE;
72 l_primary_sty_id NUMBER;
73 l_primary_strm_name OKL_STRM_TYPE_v.name%Type;
74
75 BEGIN
76
77 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
78
79 OPEN get_k_info_csr (p_khr_id);
80 FETCH get_k_info_csr INTO l_product_id, l_contract_start_date,l_deal_type,l_report_product_id;
81 CLOSE get_k_info_csr;
82
83 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
84
85 OPEN get_primary_strm_type_csr (l_product_id, l_contract_start_date);
86 FETCH get_primary_strm_type_csr INTO l_primary_sty_id,l_primary_strm_name;
87 IF get_primary_strm_type_csr%NOTFOUND THEN
88 x_primary_sty_id := null;
89 x_primary_sty_name := null;
90 ELSE
91 x_primary_sty_id := l_primary_sty_id;
92 x_primary_sty_name := l_primary_strm_name;
93 END IF;
94 CLOSE get_primary_strm_type_csr;
95
96 ELSE
97
98 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
99 p_msg_name => 'OKL_NO_PDT_FOUND');
100 RAISE Okl_Api.G_EXCEPTION_ERROR;
101
102 END IF;
103 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
104
105 EXCEPTION
106 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
107 IF get_k_info_csr%ISOPEN THEN
108 CLOSE get_k_info_csr;
109 END IF;
110 IF get_primary_strm_type_csr%ISOPEN THEN
111 CLOSE get_primary_strm_type_csr;
112 END IF;
113 x_return_status := Okl_Api.G_RET_STS_ERROR ;
114
115 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
116 IF get_k_info_csr%ISOPEN THEN
117 CLOSE get_k_info_csr;
118 END IF;
119 IF get_primary_strm_type_csr%ISOPEN THEN
120 CLOSE get_primary_strm_type_csr;
121 END IF;
122 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
123
124 WHEN OTHERS THEN
125 IF get_k_info_csr%ISOPEN THEN
126 CLOSE get_k_info_csr;
127 END IF;
128 IF get_primary_strm_type_csr%ISOPEN THEN
129 CLOSE get_primary_strm_type_csr;
130 END IF;
131 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
132
133 END get_primary_stream_type;
134
135 PROCEDURE get_primary_stream_type
136 (
137 p_khr_id IN NUMBER,
138 p_deal_type IN OKL_ST_GEN_TMPT_SETS.deal_type%TYPE,
139 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_primary_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
142 x_primary_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE
143 )
144
145 IS
146
147 CURSOR get_k_info_csr( l_khr_id NUMBER ) IS
148 SELECT
149
150 pdt.id pdt_id,
151 chr.start_date,
152 khr.deal_type,
153 nvl(pdt.reporting_pdt_id, -1) report_pdt_id
154 FROM okc_k_headers_v chr,
155 okl_k_headers khr,
156 okl_products_v pdt
157 WHERE khr.id = chr.id
158 AND chr.id = l_khr_id
159 AND khr.pdt_id = pdt.id(+);
160
161 CURSOR get_primary_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
162 SELECT
163 TLN.PRIMARY_STY_ID,
164 STY.NAME PRIMARY_STY_NAME
165 FROM
166 OKL_ST_GEN_TMPT_LNS TLN,
167 OKL_ST_GEN_TEMPLATES TMPT,
168 OKL_ST_GEN_TMPT_SETS Tst,
169 OKL_AE_TMPT_SETS AES,
170 OKL_PRODUCTS_V PDT,
171 OKL_STRM_TYPE_v STY
172
173 WHERE
174 TLN.GTT_ID = TMPT.ID AND
175 TMPT.GTS_ID = Tst.ID AND
176 Tst.ID = AES.GTS_ID AND
177 TST.deal_type = p_deal_type AND
178 AES.ID = PDT.AES_ID AND
179 TLN.PRIMARY_STY_ID = STY.ID
180 AND TLN.PRIMARY_YN = 'Y'
181 AND PDT.ID = l_pdt_id
182 AND (TMPT.START_DATE <= l_contract_start_date)
183 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
184 AND STY.STREAM_TYPE_PURPOSE = p_primary_sty_purpose;
185
186 l_product_id NUMBER;
187 l_deal_type okl_k_headers.deal_type%Type;
188 l_report_product_id NUMBER;
189 l_contract_start_date DATE;
190 l_primary_sty_id NUMBER;
191 l_primary_strm_name OKL_STRM_TYPE_v.name%Type;
192
193 BEGIN
194
195 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
196
197
198 OPEN get_k_info_csr (p_khr_id);
199 FETCH get_k_info_csr INTO l_product_id, l_contract_start_date,l_deal_type,l_report_product_id;
200 CLOSE get_k_info_csr;
201
202 IF (l_deal_type <> p_deal_type) THEN
203 l_product_id := l_report_product_id;
204 END IF;
205
206
207 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
208
209 OPEN get_primary_strm_type_csr (l_product_id, l_contract_start_date);
210 FETCH get_primary_strm_type_csr INTO l_primary_sty_id,l_primary_strm_name;
211 IF get_primary_strm_type_csr%NOTFOUND THEN
212 x_primary_sty_id := null;
213 x_primary_sty_name := null;
214 ELSE
215 x_primary_sty_id := l_primary_sty_id;
216 x_primary_sty_name := l_primary_strm_name;
217 END IF;
218 CLOSE get_primary_strm_type_csr;
219
220 ELSE
221
222 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
223 p_msg_name => 'OKL_NO_PDT_FOUND');
224 RAISE Okl_Api.G_EXCEPTION_ERROR;
225
226 END IF;
227 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
228
229 EXCEPTION
230 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
231 IF get_k_info_csr%ISOPEN THEN
232 CLOSE get_k_info_csr;
233 END IF;
234 IF get_primary_strm_type_csr%ISOPEN THEN
235 CLOSE get_primary_strm_type_csr;
236 END IF;
237 x_return_status := Okl_Api.G_RET_STS_ERROR ;
238
239 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
240 IF get_k_info_csr%ISOPEN THEN
241 CLOSE get_k_info_csr;
242 END IF;
243 IF get_primary_strm_type_csr%ISOPEN THEN
244 CLOSE get_primary_strm_type_csr;
245 END IF;
246 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
247
248 WHEN OTHERS THEN
249 IF get_k_info_csr%ISOPEN THEN
250 CLOSE get_k_info_csr;
251 END IF;
252 IF get_primary_strm_type_csr%ISOPEN THEN
253 CLOSE get_primary_strm_type_csr;
254 END IF;
255 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
256
257 END get_primary_stream_type;
258
259 PROCEDURE get_dependent_stream_type(
260 p_khr_id IN NUMBER,
261 p_pdt_id IN NUMBER,
262 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
265 x_dependent_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE) AS
266
267 /*CURSOR get_k_info_csr (l_khr_id NUMBER)IS
268 SELECT pdt_id, start_date
269 FROM okl_k_headers_full_v
270 WHERE id = l_khr_id; */
271 -- 30-NOV-04 GKADARKA V115.3 -- Fixes for bug 4036231
272 -- Added support for multi GAPP product
273 -- Changed the below cursor to get report product id also
274
275 CURSOR get_k_info_csr( l_khr_id NUMBER ) IS
276 SELECT
277
278 pdt.id pdt_id,
279 chr.start_date,
280 khr.deal_type,
281 nvl(pdt.reporting_pdt_id, -1) report_pdt_id
282 FROM okc_k_headers_v chr,
283 okl_k_headers khr,
284 okl_products_v pdt
285 WHERE khr.id = chr.id
286 AND chr.id = l_khr_id
287 AND khr.pdt_id = pdt.id(+);
288
289 CURSOR get_depend_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
290 SELECT
291 TLN.DEPENDENT_STY_ID,
292 STY1.NAME DEPENDENT_STY_NAME
293 FROM
294 OKL_ST_GEN_TMPT_LNS TLN,
295 OKL_ST_GEN_TEMPLATES TMPT,
296 OKL_ST_GEN_TMPT_SETS Tst,
297 OKL_AE_TMPT_SETS AES,
298 OKL_PRODUCTS_V PDT,
299 OKL_STRM_TYPE_v STY
300 , OKL_STRM_TYPE_v STY1
301 WHERE
302 TLN.GTT_ID = TMPT.ID AND
303 TMPT.GTS_ID = Tst.ID AND
304 Tst.ID = AES.GTS_ID AND
305 --TST.deal_type = p_deal_type AND
306 AES.ID = PDT.AES_ID AND
307 TLN.PRIMARY_STY_ID = STY.ID
308 AND TLN.DEPENDENT_STY_ID = STY1.ID (+)
309 AND TLN.PRIMARY_YN = 'N'
310 AND PDT.ID = l_pdt_id
311 AND (TMPT.START_DATE <= l_contract_start_date)
312 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
313 AND STY1.STREAM_TYPE_PURPOSE = p_dependent_sty_purpose;
314
315 l_product_id NUMBER;
316 l_deal_type okl_k_headers.deal_type%Type;
317 l_report_product_id NUMBER;
318 l_contract_start_date DATE;
319 l_dependetn_sty_id NUMBER;
320 l_dependetn_sty_name OKL_STRM_TYPE_v.name%Type;
321
322 BEGIN
323 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
324
325
326 OPEN get_k_info_csr (p_khr_id);
327 FETCH get_k_info_csr INTO l_product_id, l_contract_start_date,l_deal_type,l_report_product_id;
328 CLOSE get_k_info_csr;
329
330 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
331
332 OPEN get_depend_strm_type_csr (l_product_id, l_contract_start_date);
333 FETCH get_depend_strm_type_csr INTO l_dependetn_sty_id,l_dependetn_sty_name;
334 IF get_depend_strm_type_csr%NOTFOUND THEN
335 x_dependent_sty_id := null;
336 x_dependent_sty_name := null;
337
338 ELSE
339 x_dependent_sty_id := l_dependetn_sty_id;
340 x_dependent_sty_name := l_dependetn_sty_name;
341 END IF;
342 CLOSE get_depend_strm_type_csr;
343
344 ELSE
345
346 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
347 p_msg_name => 'OKL_NO_PDT_FOUND');
348 RAISE Okl_Api.G_EXCEPTION_ERROR;
349
350 END IF;
351 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
352 EXCEPTION
353 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
354 IF get_k_info_csr%ISOPEN THEN
355 CLOSE get_k_info_csr;
356 END IF;
357 IF get_depend_strm_type_csr%ISOPEN THEN
358 CLOSE get_depend_strm_type_csr;
359 END IF;
360 x_return_status := Okl_Api.G_RET_STS_ERROR ;
361
362 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
363 IF get_k_info_csr%ISOPEN THEN
364 CLOSE get_k_info_csr;
365 END IF;
366 IF get_depend_strm_type_csr%ISOPEN THEN
367 CLOSE get_depend_strm_type_csr;
368 END IF;
369 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
370
371 WHEN OTHERS THEN
372 IF get_k_info_csr%ISOPEN THEN
373 CLOSE get_k_info_csr;
374 END IF;
375 IF get_depend_strm_type_csr%ISOPEN THEN
376 CLOSE get_depend_strm_type_csr;
377 END IF;
378 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
379
380
381 END get_dependent_stream_type;
382
383
384 -- Start of comments
385 --
386 -- Procedure Name : get_dependent_stream_type
387 -- Description : Return dependent Stream type for given purpose code
388 -- Business Rules :
389 -- Parameters : khr_id
390 -- : dependent_sty_purpose
391 -- Version : 1.0
392 -- : 2.0 Now passing contract deal type to get the stream type
393 ---- 3.0 Added code to support multi GAPP product
394 -- End of comments
395
396 PROCEDURE get_dependent_stream_type(
397 p_khr_id IN NUMBER,
398 p_deal_type IN OKL_ST_GEN_TMPT_SETS.deal_type%TYPE,
399 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
400 x_return_status OUT NOCOPY VARCHAR2,
401 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
402 x_dependent_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE) AS
403
404 /*CURSOR get_k_info_csr (l_khr_id NUMBER)IS
405 SELECT pdt_id, start_date
406 FROM okl_k_headers_full_v
407 WHERE id = l_khr_id; */
408 -- 30-NOV-04 GKADARKA V115.3 -- Fixes for bug 4036231
409 -- Added support for multi GAPP product
410 -- Changed the below cursor to get report product id also
411
412 CURSOR get_k_info_csr( l_khr_id NUMBER ) IS
413 SELECT
414
415 pdt.id pdt_id,
416 chr.start_date,
417 khr.deal_type,
418 nvl(pdt.reporting_pdt_id, -1) report_pdt_id
419 FROM okc_k_headers_v chr,
420 okl_k_headers khr,
421 okl_products_v pdt
422 WHERE khr.id = chr.id
423 AND chr.id = l_khr_id
424 AND khr.pdt_id = pdt.id(+);
425
426 CURSOR get_depend_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
427 SELECT
428 TLN.DEPENDENT_STY_ID,
429 STY1.NAME DEPENDENT_STY_NAME
430 FROM
431 OKL_ST_GEN_TMPT_LNS TLN,
432 OKL_ST_GEN_TEMPLATES TMPT,
433 OKL_ST_GEN_TMPT_SETS Tst,
434 OKL_AE_TMPT_SETS AES,
435 OKL_PRODUCTS_V PDT,
436 OKL_STRM_TYPE_v STY
437 , OKL_STRM_TYPE_v STY1
438 WHERE
439 TLN.GTT_ID = TMPT.ID AND
440 TMPT.GTS_ID = Tst.ID AND
441 Tst.ID = AES.GTS_ID AND
442 TST.deal_type = p_deal_type AND
443 AES.ID = PDT.AES_ID AND
444 TLN.PRIMARY_STY_ID = STY.ID
445 AND TLN.DEPENDENT_STY_ID = STY1.ID (+)
446 AND TLN.PRIMARY_YN = 'N'
447 AND PDT.ID = l_pdt_id
448 AND (TMPT.START_DATE <= l_contract_start_date)
449 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
450 AND STY1.STREAM_TYPE_PURPOSE = p_dependent_sty_purpose;
451
452 l_product_id NUMBER;
453 l_deal_type okl_k_headers.deal_type%Type;
454 l_report_product_id NUMBER;
455 l_contract_start_date DATE;
456 l_dependetn_sty_id NUMBER;
457 l_dependetn_sty_name OKL_STRM_TYPE_v.name%Type;
458
459 BEGIN
460 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
461
462
463 /*OPEN get_k_info_csr (p_khr_id);
464 FETCH get_k_info_csr INTO l_product_id, l_contract_start_date;
465 CLOSE get_k_info_csr; */
466
467 OPEN get_k_info_csr (p_khr_id);
468 FETCH get_k_info_csr INTO l_product_id, l_contract_start_date,l_deal_type,l_report_product_id;
469 CLOSE get_k_info_csr;
470
471 IF (l_deal_type <> p_deal_type) THEN
472 l_product_id := l_report_product_id;
473 END IF;
474
475 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
476
477 OPEN get_depend_strm_type_csr (l_product_id, l_contract_start_date);
478 FETCH get_depend_strm_type_csr INTO l_dependetn_sty_id,l_dependetn_sty_name;
479 IF get_depend_strm_type_csr%NOTFOUND THEN
480 x_dependent_sty_id := null;
481 x_dependent_sty_name := null;
482
483 ELSE
484 x_dependent_sty_id := l_dependetn_sty_id;
485 x_dependent_sty_name := l_dependetn_sty_name;
486 END IF;
487 CLOSE get_depend_strm_type_csr;
488
489 ELSE
490
491 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
492 p_msg_name => 'OKL_NO_PDT_FOUND');
493 RAISE Okl_Api.G_EXCEPTION_ERROR;
494
495 END IF;
496 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
497 EXCEPTION
498 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
499 IF get_k_info_csr%ISOPEN THEN
500 CLOSE get_k_info_csr;
501 END IF;
502 IF get_depend_strm_type_csr%ISOPEN THEN
503 CLOSE get_depend_strm_type_csr;
504 END IF;
505 x_return_status := Okl_Api.G_RET_STS_ERROR ;
506
507 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
508 IF get_k_info_csr%ISOPEN THEN
509 CLOSE get_k_info_csr;
510 END IF;
511 IF get_depend_strm_type_csr%ISOPEN THEN
512 CLOSE get_depend_strm_type_csr;
513 END IF;
514 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
515
516 WHEN OTHERS THEN
517 IF get_k_info_csr%ISOPEN THEN
518 CLOSE get_k_info_csr;
519 END IF;
520 IF get_depend_strm_type_csr%ISOPEN THEN
521 CLOSE get_depend_strm_type_csr;
522 END IF;
523 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
524
525
526 END get_dependent_stream_type;
527
528 -- Start of comments
529 --
530 -- Procedure Name : validate_strm_gen_template
531 -- Description : Procedure to validate stream generation template
532 -- Business Rules :
533 -- Parameters : khr_id
534 -- :
535 -- Version : 1.0
536 -- End of comments
537
538 PROCEDURE validate_strm_gen_template(
539 p_api_version IN NUMBER,
540 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2,
544 p_khr_id IN NUMBER
545 ) AS
546
547 CURSOR get_k_info_csr (l_khr_id NUMBER)IS
548 SELECT deal_type
549 FROM okl_k_headers_h
550 WHERE id = l_khr_id;
551
552 CURSOR get_strm_type_purpose(l_lookup_code VARCHAR2) IS
553 SELECT LOOKUP_CODE FROM fnd_lookups
554 WHERE lookup_type = 'OKL_STREAM_TYPE_PURPOSE' AND
555 lookup_code = l_lookup_code;
556
557 -- cursor to get all lines in a contract which line type is in
558 --('FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET', 'FREE_FORM1', 'LINK_FEE_ASSET')
559
560 cursor l_get_k_lines_csr( p_chr_id NUMBER) is
561 select kle.id,
562 lse.lty_code,
563 kle.oec,
564 kle.residual_code,
565 kle.capital_amount,
566 kle.delivered_date,
567 kle.date_funding_required,
568 kle.residual_grnty_amount,
569 kle.date_funding,
570 kle.residual_value,
571 kle.amount,
572 kle.price_negotiated,
573 kle.start_date,
574 kle.end_date,
575 kle.orig_system_id1,
576 kle.fee_type,
577 kle.initial_direct_cost,
578 tl.item_description,
579 tl.name
580 from okl_k_lines_full_v kle,
581 okc_line_styles_b lse,
582 okc_k_lines_tl tl,
583 okc_statuses_b sts
584 where KLE.LSE_ID = LSE.ID
585 and lse.lty_code IN ('FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET', 'FREE_FORM1', 'LINK_FEE_ASSET')
586 and tl.id = kle.id
587 and tl.language = userenv('LANG')
588 and kle.dnz_chr_id = p_chr_id
589 and sts.code = kle.sts_code
590 and sts.ste_code not in ('HOLD', 'TERMINATED', 'EXPIRED','CANCELLED');
591
592 -- Cursor to get all payments for a asset line
593
594 cursor l_rl_csr( chrId NUMBER,cleId NUMBER ) IS
595 select crl.id slh_id,
596 crl.object1_id1, --stream type id
597 crl.RULE_INFORMATION1,
598 crl.RULE_INFORMATION2,
599 crl.RULE_INFORMATION3,
600 crl.RULE_INFORMATION5,
601 crl.RULE_INFORMATION6,
602 crl.RULE_INFORMATION10
603 from OKC_RULE_GROUPS_B crg,
604 OKC_RULES_B crl
605 where crl.rgp_id = crg.id
606 and crg.RGD_CODE = 'LALEVL'
607 and crl.RULE_INFORMATION_CATEGORY = 'LASLH'
608 and crg.dnz_chr_id = chrId
609 and crg.cle_id = cleId
610 order by crl.RULE_INFORMATION1;
611
612 -- Cursor to get stream_type_purpose
613
614 cursor l_strm_purpose_code(p_sty_id NUMBER) IS
615 SELECT STREAM_TYPE_PURPOSE from okl_strm_type_b
616 where id = p_sty_id;
617
618 -- Cursor to get passthrought percentage
619
620 CURSOR c_pt_perc( kleid NUMBER) IS
621 SELECT NVL(TO_NUMBER(rul.rule_information1), 100) pass_through_percentage
622 FROM okc_rule_groups_b rgp,
623 okc_rules_b rul
624 WHERE rgp.cle_id = kleid
625 AND rgp.rgd_code = 'LAPSTH'
626 AND rgp.id = rul.rgp_id
627 AND rul.rule_information_category = 'LAPTPR';
628
629 -- cursor to get line expense
630
631 CURSOR c_rec_exp (p_khr_id NUMBER, p_kle_id NUMBER) IS
632 SELECT TO_NUMBER(rul.rule_information1) periods,
633 TO_NUMBER(rul.rule_information2) amount --,
634 FROM okc_rules_b rul,
635 okc_rules_b rul2,
636 okc_rule_groups_b rgp,
637 okc_k_lines_b cle,
638 okl_k_lines kle
639 WHERE rgp.dnz_chr_id = p_khr_id
640 AND rgp.cle_id = cle.id
641 AND kle.id = p_kle_id
642 AND cle.sts_code IN ('PASSED', 'COMPLETE')
643 AND kle.fee_type <> 'FINANCED'
644 AND kle.fee_type <> 'ABSORBED'
645 AND kle.fee_type <> 'ROLLOVER'
646 AND rgp.rgd_code = 'LAFEXP'
647 AND rgp.id = rul.rgp_id
648 AND rgp.id = rul2.rgp_id
649 AND rul.rule_information_category = 'LAFEXP'
650 AND rul2.rule_information_category = 'LAFREQ';
651
652 -- cursoer to get initial direct cost
653
654 CURSOR c_fee_idc (p_kle_id NUMBER) IS
655 SELECT NVL(initial_direct_cost, 0)
656 FROM okl_k_lines
657 WHERE id = p_kle_id;
658
659
660 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_STRM_GEN_TEMPLATE';
661 l_product_id NUMBER;
662 l_contract_start_date DATE;
663 l_deal_type VARCHAR2(30);
664 l_kle_id NUMBER;
665 l_lty_code VARCHAR(30);
666 l_sty_id NUMBER;
667 l_sty_name_purpose OKL_STRM_TYPE_B.stream_type_purpose%TYPE;
668 l_primary_flag VARCHAR2(3) := OKL_API.G_FALSE;
669 l_dep_flag VARCHAR2(3) := OKL_API.G_FALSE;
670 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
671 l_primary_sty_id okl_strm_type_b.ID%TYPE;
672 l_primary_sty_name OKL_STRM_TYPE_v.name%TYPE;
673 l_dependent_sty_id okl_strm_type_b.ID%TYPE;
674 l_dependent_sty_name OKL_STRM_TYPE_v.name%TYPE;
675 l_fee_type VARCHAR2(30);
676 l_recurr_yn VARCHAR2(3):= OKL_API.G_FALSE;
677 l_pass_through_percentage NUMBER;
678 l_rec_period NUMBER;
679 l_expense_amount NUMBER;
680 l_idc NUMBER;
681 l_lookup_strm_purpose OKL_STRM_TYPE_B.stream_type_purpose%TYPE;
682
683
684 BEGIN
685 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
686
687 -- Call start_activity to create savepoint, check compatibility
688 -- and initialize message list
689 x_return_status := okl_api.start_activity (
690 l_api_name
691 ,p_init_msg_list
692 ,'_PVT'
693 ,x_return_status);
694 -- Check if activity started successfully
695 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
696 RAISE okl_api.g_exception_unexpected_error;
697 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
698 RAISE okl_api.g_exception_error;
699 END IF;
700
701 OPEN get_k_info_csr (p_khr_id);
702 FETCH get_k_info_csr INTO l_deal_type;
703 CLOSE get_k_info_csr;
704
705 FOR l_get_k_lines_rec IN l_get_k_lines_csr(p_khr_id) LOOP
706
707 l_kle_id := l_get_k_lines_rec.id;
708 l_lty_code := l_get_k_lines_rec.lty_code;
709 l_fee_type := l_get_k_lines_rec.fee_type;
710
711 IF (l_lty_code = 'FEE') THEN
712
713 OPEN c_pt_perc(l_kle_id); -- only for fees
714 FETCH c_pt_perc INTO l_pass_through_percentage;
715 CLOSE c_pt_perc;
716
717 OPEN c_rec_exp(p_khr_id,l_kle_id); -- only for fees
718 FETCH c_rec_exp INTO l_rec_period,l_expense_amount;
719 CLOSE c_rec_exp;
720
721 OPEN c_fee_idc(l_kle_id); --only for fees
722 FETCH c_fee_idc INTO l_idc;
723 CLOSE c_fee_idc;
724 END IF;
725
726 FOR l_rl_rec IN l_rl_csr(p_khr_id,l_kle_id) LOOP
727 l_sty_id := l_rl_rec.object1_id1;
728
729
730 OPEN l_strm_purpose_code (l_sty_id);
731 FETCH l_strm_purpose_code INTO l_sty_name_purpose;
732 CLOSE l_strm_purpose_code;
733
734
735
736
737 l_return_status := Okl_Api.G_RET_STS_SUCCESS;
738
739 IF (l_sty_name_purpose = 'RENT') THEN
740
741 l_primary_flag := OKL_API.G_TRUE;
742
743 get_primary_stream_type(
744 p_khr_id => p_khr_id,
745 p_deal_type => l_deal_type,
746 p_primary_sty_purpose => l_sty_name_purpose,
747 x_return_status => l_return_status,
748 x_primary_sty_id => l_primary_sty_id,
749 x_primary_sty_name => l_primary_sty_name);
750
751 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
752
753 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
754 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
755 p_token1 => 'PURPOSE_CODE',
756 p_token1_value => l_sty_name_purpose,
757 p_token2 => 'PRODUCT',
758 p_token2_value => l_deal_type);
759
760 RAISE OKL_API.G_EXCEPTION_ERROR;
761 END IF;
762
763
764
765 IF (l_deal_type = 'LEASEOP' AND (l_lty_code = 'FREE_FORM1' OR l_lty_code IS NULL)) THEN
766
767 get_dependent_stream_type(
768 p_khr_id => p_khr_id,
769 p_deal_type => l_deal_type,
770 p_dependent_sty_purpose => 'RENT_ACCRUAL',
771 x_return_status => l_return_status,
772 x_dependent_sty_id => l_dependent_sty_id,
773 x_dependent_sty_name =>l_dependent_sty_name);
774
775 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
776
777 OPEN get_strm_type_purpose ('RENT_ACCRUAL');
778 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
779 CLOSE get_strm_type_purpose;
780
781 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
782 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
783 p_token1 => 'PURPOSE_CODE',
784 p_token1_value => l_lookup_strm_purpose,
785 p_token2 => 'PRODUCT',
786 p_token2_value => l_deal_type);
787
788
789
790 RAISE OKL_API.G_EXCEPTION_ERROR;
791 END IF;
792 END IF;
793
794 IF (l_deal_type IN ('LOAN', 'LOAN-REVOLVING')) THEN
795
796 get_dependent_stream_type(
797 p_khr_id => p_khr_id,
798 p_deal_type => l_deal_type,
799 p_dependent_sty_purpose => 'LOAN_PAYMENT',
800 x_return_status => l_return_status,
801 x_dependent_sty_id => l_dependent_sty_id,
802 x_dependent_sty_name =>l_dependent_sty_name);
803
804 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
805 OPEN get_strm_type_purpose ('LOAN_PAYMENT');
806 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
807 CLOSE get_strm_type_purpose;
808
809 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
810 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
811 p_token1 => 'PURPOSE_CODE',
812 p_token1_value => l_lookup_strm_purpose,
813 p_token2 => 'PRODUCT',
814 p_token2_value => l_deal_type);
815
816
817
818 END IF;
819 END IF;
820
821 END IF;
822
823 IF (l_deal_type IN ('LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING')) OR
824 ( l_fee_type = 'FINANCED' OR l_fee_type = 'ROLLOVER' ) THEN
825
826 get_dependent_stream_type(
827 p_khr_id => p_khr_id,
828 p_deal_type => l_deal_type,
829 p_dependent_sty_purpose => 'LEASE_INCOME', -- PRE-TAX INCOME
830 x_return_status => l_return_status,
831 x_dependent_sty_id => l_dependent_sty_id,
832 x_dependent_sty_name =>l_dependent_sty_name);
833
834 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
835
836 OPEN get_strm_type_purpose ('LEASE_INCOME');
837 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
838 CLOSE get_strm_type_purpose;
839
840 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
841 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
842 p_token1 => 'PURPOSE_CODE',
843 p_token1_value => l_lookup_strm_purpose,
844 p_token2 => 'PRODUCT',
845 p_token2_value => l_deal_type);
846
847 RAISE OKL_API.G_EXCEPTION_ERROR;
848 END IF;
849 END IF;
850
851 IF (l_deal_type IN ('LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING')) THEN
852
853 get_dependent_stream_type(
854 p_khr_id => p_khr_id,
855 p_deal_type => l_deal_type,
856 p_dependent_sty_purpose => 'PRINCIPAL_PAYMENT', -- PRINCIPAL PAYMENT
857 x_return_status => l_return_status,
858 x_dependent_sty_id => l_dependent_sty_id,
859 x_dependent_sty_name =>l_dependent_sty_name);
860
861 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
862
863 OPEN get_strm_type_purpose ('PRINCIPAL_PAYMENT');
864 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
865 CLOSE get_strm_type_purpose;
866
867 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
868 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
869 p_token1 => 'PURPOSE_CODE',
870 p_token1_value => l_lookup_strm_purpose,
871 p_token2 => 'PRODUCT',
872 p_token2_value => l_deal_type);
873
874 END IF;
875
876 get_dependent_stream_type(
877 p_khr_id => p_khr_id,
878 p_deal_type => l_deal_type,
879 p_dependent_sty_purpose => 'INTEREST_PAYMENT', -- INTEREST PAYMENT
880 x_return_status => l_return_status,
881 x_dependent_sty_id => l_dependent_sty_id,
882 x_dependent_sty_name =>l_dependent_sty_name);
883
884 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
885 OPEN get_strm_type_purpose ('INTEREST_PAYMENT');
886 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
887 CLOSE get_strm_type_purpose;
888
889 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
890 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
891 p_token1 => 'PURPOSE_CODE',
892 p_token1_value => l_lookup_strm_purpose,
893 p_token2 => 'PRODUCT',
894 p_token2_value => l_deal_type);
895
896
897 END IF;
898 get_dependent_stream_type(
899 p_khr_id => p_khr_id,
900 p_deal_type => l_deal_type,
901 p_dependent_sty_purpose => 'PRINCIPAL_BALANCE', -- PRINCIPAL_BALANCE
902 x_return_status => l_return_status,
903 x_dependent_sty_id => l_dependent_sty_id,
904 x_dependent_sty_name =>l_dependent_sty_name);
905
906 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
907
908 OPEN get_strm_type_purpose ('PRINCIPAL_BALANCE');
909 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
910 CLOSE get_strm_type_purpose;
911
912 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
913 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
914 p_token1 => 'PURPOSE_CODE',
915 p_token1_value => l_lookup_strm_purpose,
916 p_token2 => 'PRODUCT',
917 p_token2_value => l_deal_type);
918
919 END IF;
920
921 END IF;
922
923
924 IF (l_sty_name_purpose = 'RESIDUAL') THEN
925
926
927 get_primary_stream_type(
928 p_khr_id => p_khr_id,
929 p_deal_type => l_deal_type,
930 p_primary_sty_purpose => l_sty_name_purpose,
931 x_return_status => l_return_status,
932 x_primary_sty_id => l_primary_sty_id,
933 x_primary_sty_name => l_primary_sty_name);
934
935 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
936
937
938 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
939 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
940 p_token1 => 'PURPOSE_CODE',
941 p_token1_value => l_sty_name_purpose,
942 p_token2 => 'PRODUCT',
943 p_token2_value => l_deal_type);
944
945 END IF;
946
947
948
949 END IF;
950
951
952
953 IF ((l_lty_code = 'SOLD_SERVICE') OR (l_lty_code = 'LINK_SERV_ASSET')) THEN
954
955 get_dependent_stream_type(
956 p_khr_id => p_khr_id,
957 p_deal_type => l_deal_type,
958 p_dependent_sty_purpose => 'SERVICE_INCOME', -- SERVICE INCOME
959 x_return_status => l_return_status,
960 x_dependent_sty_id => l_dependent_sty_id,
961 x_dependent_sty_name =>l_dependent_sty_name);
962
963 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
964
965 OPEN get_strm_type_purpose ('SERVICE_INCOME');
966 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
967 CLOSE get_strm_type_purpose;
968
969 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
970 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
971 p_token1 => 'PURPOSE_CODE',
972 p_token1_value => l_lookup_strm_purpose,
973 p_token2 => 'PRODUCT',
974 p_token2_value => l_deal_type);
975
976 END IF;
977 END IF;
978 IF (l_lty_code IN ('FEE', 'FREE_FORM1') OR l_lty_code IS NULL) AND
979 (l_sty_name_purpose <> 'SECURITY_DEPOSIT') THEN
980
981 If ( l_fee_type = 'INCOME' AND l_rec_period IS NULL OR l_rec_period <= 1 ) Then
982 get_dependent_stream_type(
983 p_khr_id => p_khr_id,
984 p_deal_type => l_deal_type,
985 p_dependent_sty_purpose => 'AMORTIZED_FEE_EXPENSE', -- AMORTIZED_FEE_EXPENSE
986 x_return_status => l_return_status,
987 x_dependent_sty_id => l_dependent_sty_id,
988 x_dependent_sty_name =>l_dependent_sty_name);
989
990 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
991
992 OPEN get_strm_type_purpose ('AMORTIZED_FEE_EXPENSE');
993 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
994 CLOSE get_strm_type_purpose;
995
996 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
997 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
998 p_token1 => 'PURPOSE_CODE',
999 p_token1_value => l_lookup_strm_purpose,
1000 p_token2 => 'PRODUCT',
1001 p_token2_value => l_deal_type);
1002
1003 END IF;
1004 ELSIF (l_fee_type <> 'FINANCED' OR l_fee_type = 'ROLLOVER') THEN
1005 get_dependent_stream_type(
1006 p_khr_id => p_khr_id,
1007 p_deal_type => l_deal_type,
1008 p_dependent_sty_purpose => 'FEE_INCOME', -- AMORTIZED_FEE_EXPENSE
1009 x_return_status => l_return_status,
1010 x_dependent_sty_id => l_dependent_sty_id,
1011 x_dependent_sty_name =>l_dependent_sty_name);
1012
1013 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1014
1015 OPEN get_strm_type_purpose ('FEE_INCOME');
1016 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
1017 CLOSE get_strm_type_purpose;
1018
1019 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
1020 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
1021 p_token1 => 'PURPOSE_CODE',
1022 p_token1_value => l_lookup_strm_purpose,
1023 p_token2 => 'PRODUCT',
1024 p_token2_value => l_deal_type);
1025 END IF;
1026
1027 END IF;
1028
1029 IF (l_lty_code = 'FEE') THEN
1030 IF ((l_idc IS NOT NULL) AND (l_idc >=0)) THEN
1031
1032 get_dependent_stream_type(
1033 p_khr_id => p_khr_id,
1034 p_deal_type => l_deal_type,
1035 p_dependent_sty_purpose => 'AMORTIZED_FEE_EXPENSE', -- PASS_THRU_REV_ACCRUAL
1036 x_return_status => l_return_status,
1037 x_dependent_sty_id => l_dependent_sty_id,
1038 x_dependent_sty_name =>l_dependent_sty_name);
1039
1040 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1041
1042 OPEN get_strm_type_purpose ('AMORTIZED_FEE_EXPENSE');
1043 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
1044 CLOSE get_strm_type_purpose;
1045
1046 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
1047 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
1048 p_token1 => 'PURPOSE_CODE',
1049 p_token1_value => l_lookup_strm_purpose,
1050 p_token2 => 'PRODUCT',
1051 p_token2_value => l_deal_type);
1052 END IF;
1053 END IF;
1054
1055 IF ((l_expense_amount IS NOT NULL) AND (l_expense_amount >=0 )) THEN
1056 get_dependent_stream_type(
1057 p_khr_id => p_khr_id,
1058 p_deal_type => l_deal_type,
1059 p_dependent_sty_purpose => 'PERIODIC_EXPENSE_PAYABLE', -- PERIODIC EXPENSE PAYABLE
1060 x_return_status => l_return_status,
1061 x_dependent_sty_id => l_dependent_sty_id,
1062 x_dependent_sty_name =>l_dependent_sty_name);
1063
1064 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1065
1066 OPEN get_strm_type_purpose ('PERIODIC_EXPENSE_PAYABLE');
1067 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
1068 CLOSE get_strm_type_purpose;
1069
1070 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
1071 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
1072 p_token1 => 'PURPOSE_CODE',
1073 p_token1_value => l_lookup_strm_purpose,
1074 p_token2 => 'PRODUCT',
1075 p_token2_value => l_deal_type);
1076
1077 END IF;
1078
1079
1080 IF l_pass_through_percentage IS NOT NULL THEN
1081 get_dependent_stream_type(
1082 p_khr_id => p_khr_id,
1083 p_deal_type => l_deal_type,
1084 p_dependent_sty_purpose => 'PASS_THRU_REV_ACCRUAL', -- PASS_THRU_REV_ACCRUAL
1085 x_return_status => l_return_status,
1086 x_dependent_sty_id => l_dependent_sty_id,
1087 x_dependent_sty_name =>l_dependent_sty_name);
1088
1089 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1090
1091 OPEN get_strm_type_purpose ('PASS_THRU_REV_ACCRUAL');
1092 FETCH get_strm_type_purpose INTO l_lookup_strm_purpose;
1093 CLOSE get_strm_type_purpose;
1094
1095 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
1096 p_msg_name => 'OKL_ISG_STRM_TMPL_VAL_MSG',
1097 p_token1 => 'PURPOSE_CODE',
1098 p_token1_value => l_lookup_strm_purpose,
1099 p_token2 => 'PRODUCT',
1100 p_token2_value => l_deal_type);
1101 END IF;
1102 END IF;
1103
1104
1105 END IF;
1106 END IF;
1107
1108
1109 END IF;
1110
1111
1112
1113
1114
1115 END LOOP;
1116
1117
1118
1119
1120
1121 END LOOP;
1122
1123 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1124
1125 okl_api.end_activity(x_msg_count => x_msg_count,
1126 x_msg_data => x_msg_data);
1127
1128 EXCEPTION
1129
1130 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1131
1132 IF get_k_info_csr%ISOPEN THEN
1133 CLOSE get_k_info_csr;
1134 END IF;
1135
1136 IF get_strm_type_purpose%ISOPEN THEN
1137 CLOSE get_strm_type_purpose;
1138 END IF;
1139
1140 IF l_get_k_lines_csr%ISOPEN THEN
1141 CLOSE l_get_k_lines_csr;
1142 END IF;
1143
1144 IF l_rl_csr%ISOPEN THEN
1145 CLOSE l_rl_csr;
1146 END IF;
1147
1148 IF l_strm_purpose_code%ISOPEN THEN
1149 CLOSE l_strm_purpose_code;
1150 END IF;
1151
1152 IF c_pt_perc%ISOPEN THEN
1153 CLOSE c_pt_perc;
1154 END IF;
1155
1156 IF c_rec_exp%ISOPEN THEN
1157 CLOSE c_rec_exp;
1158 END IF;
1159
1160 IF c_fee_idc%ISOPEN THEN
1161 CLOSE c_fee_idc;
1162 END IF;
1163
1164 x_return_status := OKL_API.G_RET_STS_ERROR;
1165
1166 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1167
1168 IF get_k_info_csr%ISOPEN THEN
1169 CLOSE get_k_info_csr;
1170 END IF;
1171
1172 IF get_strm_type_purpose%ISOPEN THEN
1173 CLOSE get_strm_type_purpose;
1174 END IF;
1175
1176 IF l_get_k_lines_csr%ISOPEN THEN
1177 CLOSE l_get_k_lines_csr;
1178 END IF;
1179
1180 IF l_rl_csr%ISOPEN THEN
1181 CLOSE l_rl_csr;
1182 END IF;
1183
1184 IF l_strm_purpose_code%ISOPEN THEN
1185 CLOSE l_strm_purpose_code;
1186 END IF;
1187
1188 IF c_pt_perc%ISOPEN THEN
1189 CLOSE c_pt_perc;
1190 END IF;
1191
1192 IF c_rec_exp%ISOPEN THEN
1193 CLOSE c_rec_exp;
1194 END IF;
1195
1196 IF c_fee_idc%ISOPEN THEN
1197 CLOSE c_fee_idc;
1198 END IF;
1199
1200 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1201
1202 WHEN OTHERS THEN
1203
1204 IF get_k_info_csr%ISOPEN THEN
1205 CLOSE get_k_info_csr;
1206 END IF;
1207
1208 IF get_strm_type_purpose%ISOPEN THEN
1209 CLOSE get_strm_type_purpose;
1210 END IF;
1211
1212 IF l_get_k_lines_csr%ISOPEN THEN
1213 CLOSE l_get_k_lines_csr;
1214 END IF;
1215
1216 IF l_rl_csr%ISOPEN THEN
1217 CLOSE l_rl_csr;
1218 END IF;
1219
1220 IF l_strm_purpose_code%ISOPEN THEN
1221 CLOSE l_strm_purpose_code;
1222 END IF;
1223
1224 IF c_pt_perc%ISOPEN THEN
1225 CLOSE c_pt_perc;
1226 END IF;
1227
1228 IF c_rec_exp%ISOPEN THEN
1229 CLOSE c_rec_exp;
1230 END IF;
1231
1232 IF c_fee_idc%ISOPEN THEN
1233 CLOSE c_fee_idc;
1234 END IF;
1235
1236 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1237
1238
1239 END validate_strm_gen_template;
1240
1241 -- Start of comments
1242 --
1243 -- Procedure Name : get_dependent_stream_type
1244 -- Description : Return dependent Stream type for given purpose code and primary stream type id
1245 -- Business Rules :
1246 -- Parameters : khr_id
1247 -- : Primary Stram Type id
1248 -- : dependent_sty_purpose
1249 -- Version : 1.0
1250 -- : 2.0 Now passing contract deal type to get the stream type
1251 -- End of comments
1252
1253 PROCEDURE get_dependent_stream_type(
1254 p_khr_id IN NUMBER,
1255 p_deal_type IN OKL_ST_GEN_TMPT_SETS.deal_type%TYPE,
1256 p_primary_sty_id IN okl_strm_type_b.ID%TYPE,
1257 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
1258 x_return_status OUT NOCOPY VARCHAR2,
1259 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
1260 x_dependent_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE) AS
1261
1262 CURSOR get_k_info_csr (l_khr_id NUMBER)IS
1263 SELECT pdt_id, start_date
1264 FROM okl_k_headers_full_v
1265 WHERE id = l_khr_id;
1266
1267 CURSOR get_depend_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
1268 SELECT
1269 TLN.DEPENDENT_STY_ID,
1270 STY1.NAME DEPENDENT_STY_NAME
1271 FROM
1272 OKL_ST_GEN_TMPT_LNS TLN,
1273 OKL_ST_GEN_TEMPLATES TMPT,
1274 OKL_ST_GEN_TMPT_SETS Tst,
1275 OKL_AE_TMPT_SETS AES,
1276 OKL_PRODUCTS_V PDT,
1277 OKL_STRM_TYPE_v STY
1278 , OKL_STRM_TYPE_v STY1
1279 WHERE
1280 TLN.GTT_ID = TMPT.ID AND
1281 TMPT.GTS_ID = Tst.ID AND
1282 Tst.ID = AES.GTS_ID AND
1283 TST.deal_type = p_deal_type AND
1284 AES.ID = PDT.AES_ID AND
1285 TLN.PRIMARY_STY_ID = STY.ID AND
1286 TLN.PRIMARY_STY_ID = p_primary_sty_id
1287 AND TLN.DEPENDENT_STY_ID = STY1.ID (+)
1288 AND TLN.PRIMARY_YN = 'N'
1289 AND PDT.ID = l_pdt_id
1290 AND (TMPT.START_DATE <= l_contract_start_date)
1291 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
1292 AND STY1.STREAM_TYPE_PURPOSE = p_dependent_sty_purpose;
1293
1294 l_product_id NUMBER;
1295 l_contract_start_date DATE;
1296 l_dependetn_sty_id NUMBER;
1297 l_dependetn_sty_name OKL_STRM_TYPE_v.name%Type;
1298 -- kthiruva bug#4371472 start
1299 l_deal_type okl_k_headers.deal_type%Type;
1300 l_report_product_id NUMBER;
1301 -- kthiruva bug#4371472 end
1302 BEGIN
1303 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1304
1305 -- kthiruva bug#4371472 start
1306 FOR tmp_rec in G_GET_K_INFO_CSR (p_khr_id)
1307 LOOP
1308 l_product_id := tmp_rec.pdt_id;
1309 l_contract_start_date := tmp_rec.start_date;
1310 l_deal_type := tmp_rec.deal_type;
1311 l_report_product_id := tmp_rec.report_pdt_id;
1312 END LOOP;
1313
1314 IF (l_deal_type <> p_deal_type) THEN
1315 l_product_id := l_report_product_id;
1316 END IF;
1317 -- kthiruva bug#4371472 end
1318
1319 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
1320
1321 OPEN get_depend_strm_type_csr (l_product_id, l_contract_start_date);
1322 FETCH get_depend_strm_type_csr INTO l_dependetn_sty_id,l_dependetn_sty_name;
1323 IF get_depend_strm_type_csr%NOTFOUND THEN
1324 x_dependent_sty_id := null;
1325 x_dependent_sty_name := null;
1326
1327 ELSE
1328 x_dependent_sty_id := l_dependetn_sty_id;
1329 x_dependent_sty_name := l_dependetn_sty_name;
1330 END IF;
1331 CLOSE get_depend_strm_type_csr;
1332
1333 ELSE
1334
1335 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
1336 p_msg_name => 'OKL_NO_PDT_FOUND');
1337 RAISE Okl_Api.G_EXCEPTION_ERROR;
1338
1339 END IF;
1340 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1341 EXCEPTION
1342 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1343 IF get_k_info_csr%ISOPEN THEN
1344 CLOSE get_k_info_csr;
1345 END IF;
1346 IF get_depend_strm_type_csr%ISOPEN THEN
1347 CLOSE get_depend_strm_type_csr;
1348 END IF;
1349 x_return_status := Okl_Api.G_RET_STS_ERROR ;
1350
1351 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1352 IF get_k_info_csr%ISOPEN THEN
1353 CLOSE get_k_info_csr;
1354 END IF;
1355 IF get_depend_strm_type_csr%ISOPEN THEN
1356 CLOSE get_depend_strm_type_csr;
1357 END IF;
1358 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1359
1360 WHEN OTHERS THEN
1361 IF get_k_info_csr%ISOPEN THEN
1362 CLOSE get_k_info_csr;
1363 END IF;
1364 IF get_depend_strm_type_csr%ISOPEN THEN
1365 CLOSE get_depend_strm_type_csr;
1366 END IF;
1367 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1368
1369
1370 END get_dependent_stream_type;
1371
1372 -- Added by RGOOTY: Start
1373 -- Functions Added for improving the performance for ISG
1374 PROCEDURE get_dep_stream_type(
1375 p_khr_id IN NUMBER,
1376 p_deal_type IN OKL_ST_GEN_TMPT_SETS.deal_type%TYPE,
1377 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
1378 x_return_status OUT NOCOPY VARCHAR2,
1379 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
1380 x_dependent_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE,
1381 p_get_k_info_rec IN G_GET_K_INFO_CSR%ROWTYPE) AS
1382
1383 CURSOR get_depend_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
1384 SELECT
1385 TLN.DEPENDENT_STY_ID,
1386 STY1.NAME DEPENDENT_STY_NAME
1387 FROM
1388 OKL_ST_GEN_TMPT_LNS TLN,
1389 OKL_ST_GEN_TEMPLATES TMPT,
1390 OKL_ST_GEN_TMPT_SETS Tst,
1391 OKL_AE_TMPT_SETS AES,
1392 OKL_PRODUCTS_V PDT,
1393 OKL_STRM_TYPE_v STY
1394 , OKL_STRM_TYPE_v STY1
1395 WHERE
1396 TLN.GTT_ID = TMPT.ID AND
1397 TMPT.GTS_ID = Tst.ID AND
1398 Tst.ID = AES.GTS_ID AND
1399 TST.deal_type = p_deal_type AND
1400 AES.ID = PDT.AES_ID AND
1401 TLN.PRIMARY_STY_ID = STY.ID
1402 AND TLN.DEPENDENT_STY_ID = STY1.ID (+)
1403 AND TLN.PRIMARY_YN = 'N'
1404 AND PDT.ID = l_pdt_id
1405 AND (TMPT.START_DATE <= l_contract_start_date)
1406 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
1407 AND STY1.STREAM_TYPE_PURPOSE = p_dependent_sty_purpose;
1408
1409 l_product_id NUMBER;
1410 l_deal_type okl_k_headers.deal_type%Type;
1411 l_report_product_id NUMBER;
1412 l_contract_start_date DATE;
1413 l_dependetn_sty_id NUMBER;
1414 l_dependetn_sty_name OKL_STRM_TYPE_v.name%Type;
1415
1416 BEGIN
1417 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1418
1419 IF p_get_k_info_rec.pdt_id IS NULL
1420 THEN
1421 OPEN G_GET_K_INFO_CSR (p_khr_id);
1422 FETCH G_GET_K_INFO_CSR INTO l_product_id, l_contract_start_date,l_deal_type,l_report_product_id;
1423 CLOSE G_GET_K_INFO_CSR;
1424 ELSE
1425 l_product_id := p_get_k_info_rec.pdt_id;
1426 l_contract_start_date := p_get_k_info_rec.start_date;
1427 l_deal_type := p_get_k_info_rec.deal_type;
1428 l_report_product_id := p_get_k_info_rec.report_pdt_id;
1429 END IF;
1430
1431 IF (l_deal_type <> p_deal_type) THEN
1432 l_product_id := l_report_product_id;
1433 END IF;
1434
1435 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
1436 OPEN get_depend_strm_type_csr (l_product_id, l_contract_start_date);
1437 FETCH get_depend_strm_type_csr INTO l_dependetn_sty_id,l_dependetn_sty_name;
1438 IF get_depend_strm_type_csr%NOTFOUND THEN
1439 x_dependent_sty_id := null;
1440 x_dependent_sty_name := null;
1441 ELSE
1442 x_dependent_sty_id := l_dependetn_sty_id;
1443 x_dependent_sty_name := l_dependetn_sty_name;
1444 END IF;
1445 CLOSE get_depend_strm_type_csr;
1446
1447 ELSE
1448 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
1449 p_msg_name => 'OKL_NO_PDT_FOUND');
1450 RAISE Okl_Api.G_EXCEPTION_ERROR;
1451 END IF;
1452 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1453 EXCEPTION
1454 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1455 IF G_GET_K_INFO_CSR%ISOPEN THEN
1456 CLOSE G_GET_K_INFO_CSR;
1457 END IF;
1458 IF get_depend_strm_type_csr%ISOPEN THEN
1459 CLOSE get_depend_strm_type_csr;
1460 END IF;
1461 x_return_status := Okl_Api.G_RET_STS_ERROR ;
1462
1463 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1464 IF G_GET_K_INFO_CSR%ISOPEN THEN
1465 CLOSE G_GET_K_INFO_CSR;
1466 END IF;
1467 IF get_depend_strm_type_csr%ISOPEN THEN
1468 CLOSE get_depend_strm_type_csr;
1469 END IF;
1470 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1471
1472 WHEN OTHERS THEN
1473 IF G_GET_K_INFO_CSR%ISOPEN THEN
1474 CLOSE G_GET_K_INFO_CSR;
1475 END IF;
1476 IF get_depend_strm_type_csr%ISOPEN THEN
1477 CLOSE get_depend_strm_type_csr;
1478 END IF;
1479 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1480 END get_dep_stream_type;
1481
1482 -- Overloaded get_dep_stream_type
1483 PROCEDURE get_dep_stream_type(
1484 p_khr_id IN NUMBER,
1485 p_deal_type IN OKL_ST_GEN_TMPT_SETS.deal_type%TYPE,
1486 p_primary_sty_id IN okl_strm_type_b.ID%TYPE,
1487 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
1488 x_return_status OUT NOCOPY VARCHAR2,
1489 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE,
1490 x_dependent_sty_name OUT NOCOPY OKL_STRM_TYPE_v.name%TYPE,
1491 p_get_k_info_rec IN G_GET_K_INFO_CSR%ROWTYPE) AS
1492
1493 CURSOR get_depend_strm_type_csr (l_pdt_id NUMBER, l_contract_start_date DATE) IS
1494 SELECT
1495 TLN.DEPENDENT_STY_ID,
1496 STY1.NAME DEPENDENT_STY_NAME
1497 FROM
1498 OKL_ST_GEN_TMPT_LNS TLN,
1499 OKL_ST_GEN_TEMPLATES TMPT,
1500 OKL_ST_GEN_TMPT_SETS Tst,
1501 OKL_AE_TMPT_SETS AES,
1502 OKL_PRODUCTS_V PDT,
1503 OKL_STRM_TYPE_v STY
1504 , OKL_STRM_TYPE_v STY1
1505 WHERE
1506 TLN.GTT_ID = TMPT.ID AND
1507 TMPT.GTS_ID = Tst.ID AND
1508 Tst.ID = AES.GTS_ID AND
1509 TST.deal_type = p_deal_type AND
1510 AES.ID = PDT.AES_ID AND
1511 TLN.PRIMARY_STY_ID = STY.ID AND
1512 TLN.PRIMARY_STY_ID = p_primary_sty_id
1513 AND TLN.DEPENDENT_STY_ID = STY1.ID (+)
1514 AND TLN.PRIMARY_YN = 'N'
1515 AND PDT.ID = l_pdt_id
1516 AND (TMPT.START_DATE <= l_contract_start_date)
1517 AND (TMPT.END_DATE >= l_contract_start_date OR TMPT.END_DATE IS NULL)
1518 AND STY1.STREAM_TYPE_PURPOSE = p_dependent_sty_purpose;
1519
1520 l_product_id NUMBER;
1521 l_contract_start_date DATE;
1522 l_dependetn_sty_id NUMBER;
1523 l_dependetn_sty_name OKL_STRM_TYPE_v.name%Type;
1524 --bug#4371472 kthiruva start
1525 l_deal_type okl_k_headers.deal_type%Type;
1526 l_report_product_id NUMBER;
1527 --bug#4371472 kthiruva end
1528
1529 BEGIN
1530 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1531 IF p_get_k_info_rec.pdt_id IS NULL
1532 THEN
1533 FOR tmp_rec in G_GET_K_INFO_CSR (p_khr_id)
1534 LOOP
1535 l_product_id := tmp_rec.pdt_id;
1536 l_contract_start_date := tmp_rec.start_date;
1537 END LOOP;
1538 ELSE
1539 l_product_id := p_get_k_info_rec.pdt_id;
1540 l_contract_start_date := p_get_k_info_rec.start_date;
1541 --bug#4371472 kthiruva start
1542 l_deal_type := p_get_k_info_rec.deal_type;
1543 l_report_product_id := p_get_k_info_rec.report_pdt_id;
1544 --bug#4371472 kthiruva end
1545 END IF;
1546 --bug#4371472 kthiruva start
1547 IF (l_deal_type <> p_deal_type) THEN
1548 l_product_id := l_report_product_id;
1549 END IF;
1550 --bug#4371472 kthiruva end
1551
1552 IF (l_product_id IS NOT NULL) AND (l_contract_start_date IS NOT NULL) THEN
1553 OPEN get_depend_strm_type_csr (l_product_id, l_contract_start_date);
1554 FETCH get_depend_strm_type_csr INTO l_dependetn_sty_id,l_dependetn_sty_name;
1555 IF get_depend_strm_type_csr%NOTFOUND THEN
1556 x_dependent_sty_id := null;
1557 x_dependent_sty_name := null;
1558 ELSE
1559 x_dependent_sty_id := l_dependetn_sty_id;
1560 x_dependent_sty_name := l_dependetn_sty_name;
1561 END IF;
1562 CLOSE get_depend_strm_type_csr;
1563 ELSE
1564 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
1565 p_msg_name => 'OKL_NO_PDT_FOUND');
1566 RAISE Okl_Api.G_EXCEPTION_ERROR;
1567 END IF;
1568 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1569 EXCEPTION
1570 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1571 IF G_GET_K_INFO_CSR%ISOPEN THEN
1572 CLOSE G_GET_K_INFO_CSR;
1573 END IF;
1574 IF get_depend_strm_type_csr%ISOPEN THEN
1575 CLOSE get_depend_strm_type_csr;
1576 END IF;
1577 x_return_status := Okl_Api.G_RET_STS_ERROR ;
1578
1579 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1580 IF G_GET_K_INFO_CSR%ISOPEN THEN
1581 CLOSE G_GET_K_INFO_CSR;
1582 END IF;
1583 IF get_depend_strm_type_csr%ISOPEN THEN
1584 CLOSE get_depend_strm_type_csr;
1585 END IF;
1586 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1587
1588 WHEN OTHERS THEN
1589 IF G_GET_K_INFO_CSR%ISOPEN THEN
1590 CLOSE G_GET_K_INFO_CSR;
1591 END IF;
1592 IF get_depend_strm_type_csr%ISOPEN THEN
1593 CLOSE get_depend_strm_type_csr;
1594 END IF;
1595 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1596 END get_dep_stream_type;
1597 -- Added by RGOOTY: End
1598
1599
1600 -- Added by DJANASWA: Start 6274342
1601 -- Start of comments
1602 -- API name : get_arrears_pay_dates_option
1603 -- Pre-reqs : None
1604 -- Function : Gets the Arrears Payment Dates Option set at
1605 -- Setup/System Options/Accounting Options
1606 -- or overwritten at STG
1607 -- Parameters :
1608 -- IN : p_khr_id IN NUMBER Required
1609 -- Corresponds to the column ID
1610 -- in the table okl_k_headers.
1611 -- Version : 1.0
1612 -- History : Added by DJANASWA for ER 6274342
1613 -- End of comments
1614
1615
1616 PROCEDURE get_arrears_pay_dates_option(
1617 p_khr_id IN NUMBER,
1618 x_arrears_pay_dates_option OUT NOCOPY VARCHAR2,
1619 x_return_status OUT NOCOPY VARCHAR2)
1620 IS
1621 l_arrears_pay_dates_option VARCHAR2(60);
1622 l_api_name VARCHAR2(30) := 'get_arrears_pay_dates_option';
1623 l_return_status VARCHAR2(1);
1624
1625
1626 CURSOR c_arrears_option_at_sgt_csr (p_khr_id NUMBER)
1627 IS
1628 SELECT
1629 tst.isg_arrears_pay_dates_option
1630 FROM
1631 okl_st_gen_tmpt_sets tst,
1632 okl_ae_tmpt_sets_all aes,
1633 okl_products_v pdt,
1634 okl_k_headers khr
1635 WHERE
1636 khr.id = p_khr_id
1637 AND pdt.id = khr.pdt_id
1638 AND aes.id = pdt.aes_id
1639 AND tst.id = aes.gts_id
1640 ;
1641
1642 BEGIN
1643
1644
1645 -- Initialize the status
1646 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1647
1648 OPEN c_arrears_option_at_sgt_csr (p_khr_id => p_khr_id);
1649 FETCH c_arrears_option_at_sgt_csr INTO l_arrears_pay_dates_option;
1650 CLOSE c_arrears_option_at_sgt_csr;
1651
1652 -- Return things
1653 x_arrears_pay_dates_option := l_arrears_pay_dates_option;
1654 x_return_status := l_return_status;
1655
1656 EXCEPTION
1657 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1658 IF c_arrears_option_at_sgt_csr%ISOPEN THEN
1659 CLOSE c_arrears_option_at_sgt_csr;
1660 END IF;
1661 x_return_status := Okl_Api.G_RET_STS_ERROR ;
1662
1663 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1664 IF c_arrears_option_at_sgt_csr%ISOPEN THEN
1665 CLOSE c_arrears_option_at_sgt_csr;
1666 END IF;
1667 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1668
1669 WHEN OTHERS THEN
1670 IF c_arrears_option_at_sgt_csr%ISOPEN THEN
1671 CLOSE c_arrears_option_at_sgt_csr;
1672 END IF;
1673
1674 OKL_API.SET_MESSAGE (
1675 p_app_name => G_APP_NAME,
1676 p_msg_name => G_DB_ERROR,
1677 p_token1 => G_PROG_NAME_TOKEN,
1678 p_token1_value => l_api_name,
1679 p_token2 => G_SQLCODE_TOKEN,
1680 p_token2_value => sqlcode,
1681 p_token3 => G_SQLERRM_TOKEN,
1682 p_token3_value => sqlerrm);
1683 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1684 END get_arrears_pay_dates_option;
1685 -- end DJANASWA ER6274342
1686
1687 END OKL_ISG_UTILS_PVT;