[Home] [Help]
PACKAGE BODY: APPS.OKL_STRM_GEN_TEMPLATE_PVT
Source
1 Package Body okl_strm_gen_template_pvt as
2 /* $Header: OKLRTSGB.pls 120.57 2008/05/21 23:41:25 skkoppul noship $ */
3
4 FUNCTION GET_LOOKUP_MEANING( p_lookup_type FND_LOOKUPS.LOOKUP_TYPE%TYPE
5 ,p_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE)
6 RETURN VARCHAR
7 IS
8 CURSOR fnd_lookup_csr( p_lookup_type fnd_lookups.lookup_type%type
9 ,p_lookup_code fnd_lookups.lookup_code%type)
10 IS
11 SELECT MEANING
12 FROM FND_LOOKUPS FND
13 WHERE FND.LOOKUP_TYPE = p_lookup_type
14 AND FND.LOOKUP_CODE = p_lookup_code;
15
16 l_return_value VARCHAR2(200) := OKL_API.G_MISS_CHAR;
17 BEGIN
18 IF ( p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL )
19 THEN
20 OPEN fnd_lookup_csr( p_lookup_type, p_lookup_code );
21 FETCH fnd_lookup_csr INTO l_return_value;
22 CLOSE fnd_lookup_csr;
23 END IF;
24 return l_return_value;
25 END;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE create_strm_gen_template
29 ---------------------------------------------------------------------------
30 -- Start of comments
31 --
32 -- Procedure Name : create_strm_gen_template
33 -- Description : Create Stream Generation Template
34 -- Business Rules :
35 -- Parameters :
36 -- Version : 1.0
37 -- End of comments
38 ---------------------------------------------------------------------------
39 Procedure create_strm_gen_template(
40 p_api_version IN NUMBER
41 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
42 ,x_return_status OUT NOCOPY VARCHAR2
43 ,x_msg_count OUT NOCOPY NUMBER
44 ,x_msg_data OUT NOCOPY VARCHAR2
45 ,p_gtsv_rec IN gtsv_rec_type
46 ,p_gttv_rec IN gttv_rec_type
47 ,p_gtpv_tbl IN gtpv_tbl_type
48 ,p_gtlv_tbl IN gtlv_tbl_type
49 ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
50 ) IS
51
52 l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
55
56 l_init_msg_list VARCHAR2(1);
57 l_msg_count NUMBER;
58 l_msg_data VARCHAR2(2000);
59
60 l_gtsv_rec_in gtsv_rec_type;
61 l_gttv_rec_in gttv_rec_type;
62 l_gtpv_tbl_in gtpv_tbl_type;
63 l_gtlv_tbl_in gtlv_tbl_type;
64
65 l_gtsv_rec_out gtsv_rec_type;
66 l_gttv_rec_out gttv_rec_type;
67 l_gtpv_tbl_out gtpv_tbl_type;
68 l_gtlv_tbl_out gtlv_tbl_type;
69 i NUMBER;
70 BEGIN
71 -- Perform the Initializations
72 x_return_status := OKL_API.G_RET_STS_SUCCESS;
73
74 l_return_status := OKL_API.START_ACTIVITY( l_api_name
75 ,g_pkg_name
76 ,p_init_msg_list
77 ,l_api_version
78 ,p_api_version
79 ,'_PVT'
80 ,x_return_status);
81 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
82 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
83 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
84 RAISE OKL_API.G_EXCEPTION_ERROR;
85 END IF;
86
87 l_gtsv_rec_in := p_gtsv_rec;
88 -- Call the insert method of the Stream Generation Template Sets
89 okl_gts_pvt.insert_row(
90 p_api_version => l_api_version
91 ,p_init_msg_list => p_init_msg_list
92 ,x_return_status => l_return_status
93 ,x_msg_count => l_msg_count
94 ,x_msg_data => l_msg_data
95 ,p_gtsv_rec => l_gtsv_rec_in
96 ,x_gtsv_rec => l_gtsv_rec_out
97 );
98 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
99 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
100 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
101 RAISE OKL_API.G_EXCEPTION_ERROR;
102 END IF;
103
104 -- Populate the Stream Generate Template Records GTS_ID
105 -- with the ID returned into the l_gtsv_rec_out
106 l_gttv_rec_in := p_gttv_rec;
107 l_gttv_rec_in.gts_id := l_gtsv_rec_out.id;
108 l_gttv_rec_in.version := '1.0';
109 l_gttv_rec_in.tmpt_status := G_INIT_TMPT_STATUS;
110
111 -- Call the insert method of the Stream Generation Template
112 okl_gtt_pvt.insert_row(
113 p_api_version => l_api_version
114 ,p_init_msg_list => p_init_msg_list
115 ,x_return_status => l_return_status
116 ,x_msg_count => l_msg_count
117 ,x_msg_data => l_msg_data
118 ,p_gttv_rec => l_gttv_rec_in
119 ,x_gttv_rec => l_gttv_rec_out
120 );
121 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
123 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
124 RAISE OKL_API.G_EXCEPTION_ERROR;
125 END IF;
126
127
128 -- Now Need to loop through the entire table and update the gtt_id
129 -- in the pricing parameters.
130 -- Making sure PL/SQL table has records in it before passing
131 IF (p_gtpv_tbl.COUNT > 0) THEN
132
133 i := p_gtpv_tbl.FIRST;
134 LOOP
135 l_gtpv_tbl_in(i) := p_gtpv_tbl(i);
136 l_gtpv_tbl_in(i).gtt_id := l_gttv_rec_out.id;
137 EXIT WHEN (i = p_gtpv_tbl.LAST);
138 i := p_gtpv_tbl.NEXT(i);
139 END LOOP;
140
141 -- Call the TAPI Procedcure to perform the actual inserts
142 okl_gtp_pvt.insert_row(
143 p_api_version => l_api_version
144 ,p_init_msg_list => p_init_msg_list
145 ,x_return_status => l_return_status
146 ,x_msg_count => l_msg_count
147 ,x_msg_data => l_msg_data
148 ,p_gtpv_tbl => l_gtpv_tbl_in
149 ,x_gtpv_tbl => l_gtpv_tbl_out
150 );
151 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
152 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
153 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
154 RAISE OKL_API.G_EXCEPTION_ERROR;
155 END IF;
156 END IF;
157
158 -- Making sure PL/SQL table has records in it before passing
159 IF (p_gtlv_tbl.COUNT > 0) THEN
160 i := p_gtlv_tbl.FIRST;
161 LOOP
162 l_gtlv_tbl_in(i) := p_gtlv_tbl(i);
163 l_gtlv_tbl_in(i).gtt_id := l_gttv_rec_out.id;
164 l_gtlv_tbl_in(i).primary_yn := G_INIT_PRIMARY_YN_YES;
165 EXIT WHEN (i = p_gtlv_tbl.LAST);
166 i := p_gtlv_tbl.NEXT(i);
167 END LOOP;
168
169 -- Call the TAPI Procedcure to perform the actual inserts
170 okl_gtl_pvt.insert_row(
171 p_api_version => l_api_version
172 ,p_init_msg_list => p_init_msg_list
173 ,x_return_status => l_return_status
174 ,x_msg_count => l_msg_count
175 ,x_msg_data => l_msg_data
176 ,p_gtlv_tbl => l_gtlv_tbl_in
177 ,x_gtlv_tbl => l_gtlv_tbl_out
178 );
179 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
180 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
181 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
182 RAISE OKL_API.G_EXCEPTION_ERROR;
183 END IF;
184 END IF;
185
186 x_gttv_rec := l_gttv_rec_out;
187 x_return_status := l_return_status;
188 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
189
190
191 EXCEPTION
192 WHEN OKL_API.G_EXCEPTION_ERROR THEN
193 x_return_status := OKL_API.HANDLE_EXCEPTIONS
194 (l_api_name,
195 G_PKG_NAME,
196 'OKL_API.G_RET_STS_ERROR',
197 x_msg_count,
198 x_msg_data,
199 '_PVT');
200 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
201 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
202 ( l_api_name,
203 G_PKG_NAME,
204 'OKL_API.G_RET_STS_UNEXP_ERROR',
205 x_msg_count,
206 x_msg_data,
207 '_PVT');
208 WHEN OTHERS THEN
209 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
210 ( l_api_name,
211 G_PKG_NAME,
212 'OTHERS',
213 x_msg_count,
214 x_msg_data,
215 '_PVT');
216 END;
217
218 ---------------------------------------------------------------------------
219 -- PROCEDURE update_strm_gen_template
220 ---------------------------------------------------------------------------
221 -- Start of comments
222 --
223 -- Procedure Name : update_strm_gen_template
224 -- Description : Update a Stream Generation Template
225 -- Business Rules :
226 -- Parameters :
227 -- Version : 1.0
228 -- End of comments
229 ---------------------------------------------------------------------------
230
231 PROCEDURE update_strm_gen_template(
232 p_api_version IN NUMBER
233 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
234 ,x_return_status OUT NOCOPY VARCHAR2
235 ,x_msg_count OUT NOCOPY NUMBER
236 ,x_msg_data OUT NOCOPY VARCHAR2
237 ,p_gtsv_rec IN gtsv_rec_type
238 ,p_gttv_rec IN gttv_rec_type
239 ,p_gtpv_tbl IN gtpv_tbl_type
240 ,p_gtlv_tbl IN gtlv_tbl_type
241 ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
242 )IS
243 l_api_name CONSTANT VARCHAR2(40) := 'update_strm_gen_template';
244 l_api_version CONSTANT NUMBER := 1.0;
245 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
246
247 l_init_msg_list VARCHAR2(1);
248 l_msg_count NUMBER;
249 l_msg_data VARCHAR2(2000);
250
251 l_gtsv_rec_in gtsv_rec_type;
252 l_gttv_rec_in gttv_rec_type;
253 l_new_gttv_in_rec gttv_rec_type;
254 l_new_gttv_out_rec gttv_rec_type;
255 l_gtpv_tbl_upd_in gtpv_tbl_type;
256 l_gtpv_tbl_ins_in gtpv_tbl_type;
257 l_gtlv_tbl_upd_in gtlv_tbl_type;
258 l_gtlv_tbl_ins_in gtlv_tbl_type;
259
260 l_gtsv_rec_out gtsv_rec_type;
261 l_gttv_rec_out gttv_rec_type;
262 l_gtpv_tbl_upd_out gtpv_tbl_type;
263 l_gtpv_tbl_ins_out gtpv_tbl_type;
264 l_gtlv_tbl_upd_out gtlv_tbl_type;
265 l_gtlv_tbl_ins_out gtlv_tbl_type;
266 i NUMBER;
267 ins_table_count NUMBER;
268 upd_table_count NUMBER;
269
270 -- For validating the template
271 l_error_msgs_tbl error_msgs_tbl_type;
272 l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
273
274 -- Modified by RGOOTY
275 -- Bug 4094361: Start
276 CURSOR pdt_for_active_sgt_csr( p_gts_id OKL_ST_GEN_TMPT_SETS.ID%TYPE )
277 IS
278 SELECT PDT.ID PDT_ID
279 ,PRODUCT_STATUS_CODE
280 FROM OKL_PRODUCTS PDT,
281 OKL_AE_TMPT_SETS ATS, OKL_ST_GEN_TMPT_SETS SGT
282 WHERE PDT.AES_ID = ATS.ID
283 AND ATS.GTS_ID = SGT.ID
284 AND SGT.ID = p_gts_id;
285
286 CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
287 IS
288 SELECT GTT.GTS_ID
289 FROM OKL_ST_GEN_TEMPLATES GTT
290 WHERE GTT.ID = p_gtt_id;
291
292 CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
293 IS
294 SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
295 FROM OKL_ST_GEN_TEMPLATES GTT
296 WHERE GTT.ID = p_gtt_id;
297
298 CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
299 p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
300 IS
301 SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
302 FROM OKL_ST_GEN_TEMPLATES GTT
303 WHERE GTT.gts_ID = p_gts_id
304 AND version = p_version;
305
306 CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
307 select max(chr.start_date) MAX_START_DATE
308 from okc_k_headers_b chr,
309 okl_k_headers khr,
310 okl_ae_tmpt_Sets aes,
311 okl_st_gen_templates gtt,
312 okl_products pdt
313 where pdt.id = khr.pdt_id
314 and pdt.aes_id = aes.id
315 and aes.gts_id = gtt.gts_id
316 and khr.id = chr.id
317 and chr.start_date >= gtt.start_date
318 and gtt.id = p_gtt_id
319 --srsreeni Bug5996170 start
320 and chr.sts_code in ('APPROVED','BOOKED','COMPLETE','EVERGREEN','BANKRUPTCY_HOLD','UNDER REVISION','LITIGATION_HOLD','TERMINATION_HOLD')
321 --srsreeni Bug5996170 end
322 ;
323
324 l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
325 l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
326 l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
327 l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
328 l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
329 l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
330
331 -- Bug 4094361: End
332 BEGIN
333 -- Perform the Initializations
334 x_return_status := OKL_API.G_RET_STS_SUCCESS;
335 l_return_status := OKL_API.START_ACTIVITY( l_api_name
336 ,g_pkg_name
337 ,p_init_msg_list
338 ,l_api_version
339 ,p_api_version
340 ,'_PVT'
341 ,x_return_status);
342 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
343 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
344 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
345 RAISE OKL_API.G_EXCEPTION_ERROR;
346 END IF;
347
348 l_gtsv_rec_in := p_gtsv_rec;
349 l_gttv_rec_in := p_gttv_rec;
350 IF ( l_gtsv_rec_in.ID IS NOT NULL OR l_gtsv_rec_in.id <> OKL_API.G_MISS_NUM
351 AND l_gttv_rec_in.tmpt_status = G_INIT_TMPT_STATUS )
352 THEN
353 -- Allowing the Updation of the Template sets only in the case of
354 -- Duplicated Templates.
355 okl_gts_pvt.update_row(
356 p_api_version => l_api_version
357 ,p_init_msg_list => p_init_msg_list
358 ,x_return_status => l_return_status
359 ,x_msg_count => l_msg_count
360 ,x_msg_data => l_msg_data
361 ,p_gtsv_rec => l_gtsv_rec_in
362 ,x_gtsv_rec => l_gtsv_rec_out
363 );
364 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
365 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
366 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
367 RAISE OKL_API.G_EXCEPTION_ERROR;
368 END IF;
369 END IF;
370
371 -- On Updation of anything change the status of the template will be
372 -- Before Update - Status after Update
373 -- NEW - NEW
374 -- COMPLETE - INCOMPLETE
375 -- INCOMPLETE - INCOMPLETE
376 -- ACTIVE - ACTIVE
377 IF (l_gttv_rec_in.tmpt_status = G_STATUS_COMPLETE)
378 THEN
379 l_gttv_rec_in.tmpt_status := G_STATUS_INCOMPLETE;
380 END IF;
381
382
383 /*
384 * Bug 6911509 -> Validation of Eff From and Eff to dates
385 * for versions of SGT
386 */
387 FOR gts_id_rec IN fetch_gtt_dtls_csr( l_gttv_rec_in.id )
388 LOOP
389 l_sgt_set_id := gts_id_rec.gts_id;
390 l_version := gts_id_rec.version;
391 l_tmpt_status := gts_id_rec.tmpt_status;
392 l_start_date := gts_id_rec.start_date;
393 l_end_date := gts_id_rec.end_date;
394 END LOOP;
395
396 /**
397 *
398 1. From > To Date. For any active version, the Eff From is RO and "Eff To" is allowed only if next version, if any, is not "Active"
399 2. For any new version > 1.0 , the From date can only greater than old value not less
400 3. If any new versions From date changes, UPDATE the prev versions end date to one less than this value
401 4. If any active versions "to date" changes, it can only be greater than old vlaue and UPDATE next versions "From date" to this val+1 and "To Date" to NULL
402 */
403
404 --1.
405 IF l_gttv_rec_in.end_date IS NOT NULL AND l_gttv_rec_in.end_date <> G_MISS_DATE AND
406 trunc(l_gttv_rec_in.start_date) > trunc(l_gttv_rec_in.end_date)
407 THEN
408 okl_api.SET_MESSAGE( p_app_name => g_app_name,
409 p_msg_name => 'OKL_START_DT_LESS_END_DT'
410 );
411 RAISE OKL_API.G_EXCEPTION_ERROR;
412 END IF;
413
414 --2.
415 IF l_tmpt_status = G_INIT_TMPT_STATUS AND l_gttv_rec_in.version <> '1.0' --meaning its greater than 1.0
416 THEN
417 l_version := TRIM(TO_CHAR( TO_NUMBER( l_gttv_rec_in.version ) -1 ) ) || '.0';
418 --Fetch prev version and check for start date violations
419 FOR gts_id_rec IN fetch_gtt_ver_dtls_csr( l_gttv_rec_in.gts_id,l_version)
420 LOOP
421 IF trunc(l_gttv_rec_in.start_date) <= trunc(gts_id_rec.end_date) --(prev vers startdate)
422 THEN
423 --Error from condition 2 above
424 okl_api.SET_MESSAGE( p_app_name => g_app_name,
425 p_msg_name => 'OKL_SGT_PREV_VER_DT_CNFLT'
426 );
427 RAISE OKL_API.G_EXCEPTION_ERROR;
428 END IF;
429 END LOOP;
430 END IF;
431
432 --3.
433 IF l_tmpt_status = G_INIT_TMPT_STATUS AND l_gttv_rec_in.version <> '1.0' --meaning its greater than 1.0
434 THEN
435 --Fetch previous version end date and set to one less than this start date
436 l_version := TRIM(TO_CHAR( TO_NUMBER( l_gttv_rec_in.version ) -1 ) ) || '.0';
437 FOR gts_id_rec IN fetch_gtt_ver_dtls_csr( l_gttv_rec_in.gts_id,l_version)
438 LOOP
439 l_new_gttv_in_rec.id := gts_id_rec.id;
440 l_new_gttv_in_rec.gts_id := gts_id_rec.gts_id;
441 l_new_gttv_in_rec.version := gts_id_rec.version;
442 l_new_gttv_in_rec.end_date := l_gttv_rec_in.start_date - 1;
443
444 okl_gtt_pvt.update_row
445 (
446 p_api_version => l_api_version
447 ,p_init_msg_list => p_init_msg_list
448 ,x_return_status => l_return_status
449 ,x_msg_count => l_msg_count
450 ,x_msg_data => l_msg_data
451 ,p_gttv_rec => l_new_gttv_in_rec
452 ,x_gttv_rec => l_new_gttv_out_rec
453 );
454 END LOOP;
455 END IF;
456
457 --4.
458 IF l_tmpt_status = G_STATUS_ACTIVE THEN
459
460 --Max contract start date which used this SGT
461 OPEN okl_new_version_date_csr(l_gttv_rec_in.id);
462 FETCH okl_new_version_date_csr INTO l_okl_new_version_date_rec ;
463 CLOSE okl_new_version_date_csr;
464
465 IF l_okl_new_version_date_rec.max_start_date IS NULL THEN
466 l_okl_new_version_date_rec.max_start_date := l_start_date+1;
467 END IF;
468 --If SGT date is less than max khr date, error out
469 IF l_gttv_rec_in.end_date IS NOT NULL AND l_gttv_rec_in.end_date <> G_MISS_DATE AND
470 trunc(l_gttv_rec_in.end_date) < trunc(l_okl_new_version_date_rec.max_start_date)
471 THEN
472 --Error from condition 4 above
473 okl_api.SET_MESSAGE( p_app_name => g_app_name,
474 p_msg_name => 'OKL_SGT_TO_DATE_BEF_KHR'
475 );
476 RAISE OKL_API.G_EXCEPTION_ERROR;
477 END IF;
478 END IF;
479
480 IF l_gttv_rec_in.end_date IS NOT NULL AND (trunc(l_gttv_rec_in.end_date) <> trunc(l_end_date)) THEN --Eff to date has changed on this version
481 --Fetch next version start date and set to one greater than this end date
482 l_version := TRIM(TO_CHAR( TO_NUMBER( l_gttv_rec_in.version ) +1 ) ) || '.0';
483 FOR gts_id_rec IN fetch_gtt_ver_dtls_csr( l_gttv_rec_in.gts_id,l_version)
484 LOOP
485 --Cant update Eff To if there are successive versions which are active
486 IF gts_id_rec.tmpt_status = G_STATUS_ACTIVE THEN
487 okl_api.SET_MESSAGE( p_app_name => g_app_name,
488 p_msg_name => 'OKL_SGT_NO_UPD_SUCC_ACT_VER'
489 );
490 RAISE OKL_API.G_EXCEPTION_ERROR;
491 END IF;
492
493 l_new_gttv_in_rec.id := gts_id_rec.id;
494 l_new_gttv_in_rec.gts_id := gts_id_rec.gts_id;
495 l_new_gttv_in_rec.version := gts_id_rec.version;
496 l_new_gttv_in_rec.start_date := l_gttv_rec_in.end_date + 1;
497
498 okl_gtt_pvt.update_row
499 (
500 p_api_version => l_api_version
501 ,p_init_msg_list => p_init_msg_list
502 ,x_return_status => l_return_status
503 ,x_msg_count => l_msg_count
504 ,x_msg_data => l_msg_data
505 ,p_gttv_rec => l_new_gttv_in_rec
506 ,x_gttv_rec => l_new_gttv_out_rec
507 );
508 END LOOP;
509 END IF;
510
511 /* end
512 * Bug 6911509 -> Validation of Eff From and Eff to dates
513 * for versions of SGT
514 */
515
516
517 -- Call the update method of the Stream Generation Template
518 okl_gtt_pvt.update_row(
519 p_api_version => l_api_version
520 ,p_init_msg_list => p_init_msg_list
521 ,x_return_status => l_return_status
522 ,x_msg_count => l_msg_count
523 ,x_msg_data => l_msg_data
524 ,p_gttv_rec => l_gttv_rec_in
525 ,x_gttv_rec => l_gttv_rec_out
526 );
527 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
528 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
529 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
530 RAISE OKL_API.G_EXCEPTION_ERROR;
531 END IF;
532
533 -- Updating the Templates Pricing Parameters
534 IF (p_gtpv_tbl.COUNT > 0) THEN
535 ins_table_count :=0;
536 upd_table_count :=0;
537 FOR i IN p_gtpv_tbl.FIRST .. p_gtpv_tbl.LAST
538 LOOP
539 -- Decide whether we need to update or insert the Pricing Parameters
540 -- record.
541 IF (p_gtpv_tbl(i).id = Okl_Api.G_MISS_NUM OR p_gtpv_tbl(i).id IS NULL
542 OR p_gtpv_tbl(i).id = 0 )
543 THEN
544 -- Copy into the Insert table
545 l_gtpv_tbl_ins_in(ins_table_count) := p_gtpv_tbl(i);
546 l_gtpv_tbl_ins_in(ins_table_count).gtt_id := l_gttv_rec_out.id;
547 ins_table_count :=ins_table_count + 1;
548 ELSE
549 -- Copy into the Update table
550 l_gtpv_tbl_upd_in(upd_table_count) := p_gtpv_tbl(i);
551 upd_table_count := upd_table_count + 1;
552 END IF;
553 END LOOP;
554 IF (l_gtpv_tbl_ins_in.COUNT > 0 )
555 THEN
556 -- Call the TAPI Procedcure to perform the actual inserts
557 okl_gtp_pvt.insert_row(
558 p_api_version => l_api_version
559 ,p_init_msg_list => p_init_msg_list
560 ,x_return_status => l_return_status
561 ,x_msg_count => l_msg_count
562 ,x_msg_data => l_msg_data
563 ,p_gtpv_tbl => l_gtpv_tbl_ins_in
564 ,x_gtpv_tbl => l_gtpv_tbl_ins_out
565 );
566 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
567 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
568 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
569 RAISE OKL_API.G_EXCEPTION_ERROR;
570 END IF;
571 END IF;
572 IF (l_gtpv_tbl_upd_in.COUNT > 0)
573 THEN
574 -- Call the TAPI Procedcure to perform the actual updates
575 okl_gtp_pvt.update_row(
576 p_api_version => l_api_version
577 ,p_init_msg_list => p_init_msg_list
578 ,x_return_status => l_return_status
579 ,x_msg_count => l_msg_count
580 ,x_msg_data => l_msg_data
581 ,p_gtpv_tbl => l_gtpv_tbl_upd_in
582 ,x_gtpv_tbl => l_gtpv_tbl_upd_out
583 );
584 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
585 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
586 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
587 RAISE OKL_API.G_EXCEPTION_ERROR;
588 END IF;
589 END IF;
590 END IF;
591
592 -- Updating the Template Lines
593 IF (p_gtlv_tbl.COUNT > 0) THEN
594 ins_table_count :=0;
595 upd_table_count :=0;
596 FOR i IN p_gtlv_tbl.FIRST .. p_gtlv_tbl.LAST
597 LOOP
598 -- Decide whether we need to update or insert the Template Lines
599 IF (p_gtlv_tbl(i).id = Okl_Api.G_MISS_NUM OR p_gtlv_tbl(i).id IS NULL
600 OR p_gtlv_tbl(i).id = 0 )
601 THEN
602 -- Copy into the Insert table
603 l_gtlv_tbl_ins_in(ins_table_count) := p_gtlv_tbl(i);
604 l_gtlv_tbl_ins_in(ins_table_count).gtt_id := l_gttv_rec_out.id;
605 l_gtlv_tbl_ins_in(ins_table_count).primary_yn := G_INIT_PRIMARY_YN_YES;
606 ins_table_count := ins_table_count + 1;
607 ELSE
608 -- Copy into the Update table
609 l_gtlv_tbl_upd_in(upd_table_count) := p_gtlv_tbl(i);
610 upd_table_count := upd_table_count + 1;
611 END IF;
612 END LOOP;
613 IF (l_gtlv_tbl_ins_in.COUNT > 0 )
614 THEN
615 -- Call the TAPI Procedcure to perform the actual inserts
616 okl_gtl_pvt.insert_row(
617 p_api_version => l_api_version
618 ,p_init_msg_list => p_init_msg_list
619 ,x_return_status => l_return_status
620 ,x_msg_count => l_msg_count
621 ,x_msg_data => l_msg_data
622 ,p_gtlv_tbl => l_gtlv_tbl_ins_in
623 ,x_gtlv_tbl => l_gtlv_tbl_ins_out
624 );
625 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
626 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
627 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
628 RAISE OKL_API.G_EXCEPTION_ERROR;
629 END IF;
630 END IF;
631 IF (l_gtlv_tbl_upd_in.count > 0 )
632 THEN
633 -- Call the TAPI Procedcure to perform the actual updates
634 okl_gtl_pvt.update_row(
635 p_api_version => l_api_version
636 ,p_init_msg_list => p_init_msg_list
637 ,x_return_status => l_return_status
638 ,x_msg_count => l_msg_count
639 ,x_msg_data => l_msg_data
640 ,p_gtlv_tbl => l_gtlv_tbl_upd_in
641 ,x_gtlv_tbl => l_gtlv_tbl_upd_out
642 );
643 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_ERROR;
647 END IF;
648 END IF;
649 END IF;
650 /*
651 -- Call the validate_template for an active template
652 IF( l_gttv_rec_out.tmpt_status = G_STATUS_ACTIVE )
653 THEN
654 -- Call the validate_template
655 validate_template(
656 p_api_version => l_api_version
657 ,p_init_msg_list => p_init_msg_list
658 ,x_return_status => l_return_status
659 ,x_msg_count => l_msg_count
660 ,x_msg_data => l_msg_data
661 ,p_gtt_id => l_gttv_rec_out.id
662 ,x_error_msgs_tbl => l_error_msgs_tbl
663 ,x_return_tmpt_status => l_return_tmpt_status
664 ,p_during_upd_flag => 'T'
665 );
666 -- Modified by RGOOTY
667 -- Bug 4054596: Issue 4: Start
668 IF ( l_error_msgs_tbl.count > 0 )
669 THEN
670 x_return_status := Okl_Api.G_RET_STS_ERROR;
671 RAISE Okl_Api.G_EXCEPTION_ERROR;
672 END If;
673 -- Bug 4054596: Issue 4: End
674
675 -- Modified by RGOOTY
676 -- Bug 4094361: Start
677 FOR gts_id_rec IN fetch_gts_id_csr( l_gttv_rec_in.id )
678 LOOP
679 l_sgt_set_id := gts_id_rec.gts_id;
680 END LOOP;
681 -- Need to invalidate all the products which use this SGT.
682 FOR pdt_rec IN pdt_for_active_sgt_csr( l_sgt_set_id )
683 LOOP
684 OKL_SETUPPRODUCTS_PVT.update_product_status(
685 p_api_version => p_api_version,
686 p_init_msg_list => p_init_msg_list,
687 x_return_status => l_return_Status,
688 x_msg_count => x_msg_count,
689 x_msg_data => x_msg_data,
690 p_pdt_status => OKL_SETUPPRODUCTS_PVT.G_PDT_STS_INVALID,
691 p_pdt_id => pdt_rec.pdt_id );
692 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
693 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
694 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
695 RAISE OKL_API.G_EXCEPTION_ERROR;
696 END IF;
697 END LOOP;
698 -- Bug 4094361: End
699 END IF;
700 */
701 x_gttv_rec := l_gttv_rec_out;
702 x_return_status := l_return_status;
703 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
704 EXCEPTION
705 WHEN OKL_API.G_EXCEPTION_ERROR THEN
706 x_return_status := OKL_API.HANDLE_EXCEPTIONS
707 (l_api_name,
708 G_PKG_NAME,
709 'OKL_API.G_RET_STS_ERROR',
710 x_msg_count,
711 x_msg_data,
712 '_PVT');
713 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
714 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
715 ( l_api_name,
716 G_PKG_NAME,
717 'OKL_API.G_RET_STS_UNEXP_ERROR',
718 x_msg_count,
719 x_msg_data,
720 '_PVT');
721 WHEN OTHERS THEN
722 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
723 ( l_api_name,
724 G_PKG_NAME,
725 'OTHERS',
726 x_msg_count,
727 x_msg_data,
728 '_PVT');
729 END;
730
731 ---------------------------------------------------------------------------
732 -- PROCEDURE create_version_duplicate
733 ---------------------------------------------------------------------------
734 -- Start of comments
735 --
736 -- Procedure Name : create_version_duplicate
737 -- Description : Create a new version of a Template or a Duplicate of
738 -- Template Set.
739 -- Business Rules :
740 -- Parameters :
741 -- Version : 1.0
742 -- End of comments
743 ---------------------------------------------------------------------------
744
745 Procedure create_version_duplicate(
746 p_api_version IN NUMBER
747 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
748 ,x_return_status OUT NOCOPY VARCHAR2
749 ,x_msg_count OUT NOCOPY NUMBER
750 ,x_msg_data OUT NOCOPY VARCHAR2
751 ,p_gtt_id IN okl_st_gen_templates.id%type
752 ,p_mode IN VARCHAR2 DEFAULT G_DEFAULT_MODE
753 ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
754 ) IS
755
756 l_gtt_id OKL_ST_GEN_TEMPLATES.id%type := p_gtt_id;
757 l_mode VARCHAR2(10) := p_mode;
758 l_api_name CONSTANT VARCHAR2(40) := 'create_version_duplicate';
759 l_api_version CONSTANT NUMBER := 1.0;
760 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
761
762 l_init_msg_list VARCHAR2(1);
763 l_msg_count NUMBER;
764 l_msg_data VARCHAR2(2000);
765 l_max_chr_start_date DATE := SYSDATE;
766
767 l_gtsv_rec_in gtsv_rec_type;
768 l_gttv_rec_in gttv_rec_type;
769 l_gttv_rec_prev_version_in gttv_rec_type;
770 l_gtpv_tbl_in gtpv_tbl_type;
771 l_gtlv_tbl_pri_in gtlv_tbl_type;
772 l_gtlv_tbl_dep_in gtlv_tbl_type;
773
774 l_gtsv_rec_out gtsv_rec_type;
775 l_gttv_rec_out gttv_rec_type;
776 l_gttv_rec_prev_version_out gttv_rec_type;
777 l_gtpv_tbl_out gtpv_tbl_type;
778 l_gtlv_tbl_pri_out gtlv_tbl_type;
779 l_gtlv_tbl_dep_out gtlv_tbl_type;
780 i NUMBER;
781 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
782
783 CURSOR okl_st_gen_tmpt_sets_csr(p_gts_id IN OKL_ST_GEN_TMPT_SETS.id%type) IS
784 SELECT ID
785 ,NAME
786 ,DESCRIPTION
787 ,PRODUCT_TYPE
788 ,TAX_OWNER
789 ,DEAL_TYPE
790 ,PRICING_ENGINE
791 ,interest_calc_meth_code
792 ,revenue_recog_meth_code
793 ,days_in_month_code
794 ,days_in_yr_code
795 -- Added new field by DPSINGH for ER 6274342
796 ,isg_arrears_pay_dates_option
797 FROM OKL_ST_GEN_TMPT_SETS
798 WHERE ID = p_gts_id;
799
800 CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
801 SELECT ID
802 ,GTS_ID
803 ,VERSION
804 ,START_DATE
805 ,END_DATE
806 ,TMPT_STATUS
807 FROM OKL_ST_GEN_TEMPLATES
808 where ID = p_gtt_id;
809
810 CURSOR okl_st_gen_prc_params_csr(p_gtt_id IN OKL_ST_GEN_PRC_PARAMS.gtt_id%type) IS
811 SELECT ID
812 ,NAME
813 ,DESCRIPTION
814 ,DISPLAY_YN
815 ,UPDATE_YN
816 ,PRC_ENG_IDENT
817 ,DEFAULT_VALUE
818 ,GTT_ID
819 FROM OKL_ST_GEN_PRC_PARAMS
820 where GTT_ID = p_gtt_id;
821
822 CURSOR okl_st_gen_tmpt_lns_csr( p_gtt_id IN OKL_ST_GEN_TMPT_LNS.gtt_id%type
823 ,p_primary_yn IN OKL_ST_GEN_TMPT_LNS.primary_yn%TYPE) IS
824 SELECT ID
825 ,GTT_ID
826 ,PRIMARY_YN
827 ,PRIMARY_STY_ID
828 ,DEPENDENT_STY_ID
829 ,PRICING_NAME
830 FROM OKL_ST_GEN_TMPT_LNS
831 where GTT_ID = p_gtt_id
832 AND PRIMARY_YN = p_primary_yn;
833
834 CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
835 select max(chr.start_date) MAX_START_DATE
836 from okc_k_headers_b chr,
837 okl_k_headers khr,
838 okl_ae_tmpt_Sets aes,
839 okl_st_gen_templates gtt,
840 okl_products pdt
841 where pdt.id = khr.pdt_id
842 and pdt.aes_id = aes.id
843 and aes.gts_id = gtt.gts_id
844 and khr.id = chr.id
845 and chr.start_date >= gtt.start_date
846 and gtt.id = p_gtt_id
847 --srsreeni Bug5996170 start
848 and chr.sts_code in ('APPROVED','BOOKED','COMPLETE','EVERGREEN','BANKRUPTCY_HOLD','UNDER REVISION','LITIGATION_HOLD','TERMINATION_HOLD')
849 --srsreeni Bug5996170 end
850 ;
851
852 l_new_seq_value NUMBER;
853 CURSOR okl_new_tmpt_set_copy_no IS
854 SELECT OKL_GTS_NAME_SEQ.NEXTVAL next_number
855 FROM DUAL;
856
857 BEGIN
858
859 -- Perform the Initializations
860 x_return_status := OKL_API.G_RET_STS_SUCCESS;
861 l_return_status := OKL_API.START_ACTIVITY( l_api_name
862 ,g_pkg_name
863 ,p_init_msg_list
864 ,l_api_version
865 ,p_api_version
866 ,'_PVT'
867 ,x_return_status);
868 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
869 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
870 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
871 RAISE OKL_API.G_EXCEPTION_ERROR;
872 END IF;
873
874 l_dummy := Okl_Api.G_FALSE;
875 FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
876 LOOP
877 l_gttv_rec_in.id := gttv_rec_temp.id;
878 l_gttv_rec_in.gts_id := gttv_rec_temp.gts_id;
879 l_gttv_rec_in.version := gttv_rec_temp.version;
880 l_gttv_rec_in.start_date := gttv_rec_temp.start_date;
881 l_gttv_rec_in.end_date := gttv_rec_temp.end_date;
882 l_gttv_rec_in.tmpt_status := gttv_rec_temp.tmpt_status;
883 l_dummy := Okl_Api.G_TRUE;
884 END LOOP;
885 IF (l_dummy = okl_api.g_false) THEN
886 okl_api.SET_MESSAGE( p_app_name => g_app_name,
887 p_msg_name => g_invalid_value,
888 p_token1 => g_col_name_token,
889 p_token1_value => 'GTT_ID');
890 RAISE OKL_API.G_EXCEPTION_ERROR;
891 END IF;
892
893 l_dummy := Okl_Api.G_FALSE;
894 FOR gtsv_rec_temp IN okl_st_gen_tmpt_sets_csr(l_gttv_rec_in.gts_id)
895 LOOP
896 l_gtsv_rec_in.id := gtsv_rec_temp.id;
897 l_gtsv_rec_in.name := gtsv_rec_temp.name;
898 l_gtsv_rec_in.description := gtsv_rec_temp.description;
899 l_gtsv_rec_in.product_type := gtsv_rec_temp.product_type;
900 l_gtsv_rec_in.tax_owner := gtsv_rec_temp.tax_owner;
901 l_gtsv_rec_in.deal_type := gtsv_rec_temp.deal_type;
902 l_gtsv_rec_in.pricing_engine := gtsv_rec_temp.pricing_engine;
903 l_gtsv_rec_in.interest_calc_meth_code:= gtsv_rec_temp.interest_calc_meth_code;
904 l_gtsv_rec_in.revenue_recog_meth_code:= gtsv_rec_temp.revenue_recog_meth_code;
905 l_gtsv_rec_in.days_in_month_code:= gtsv_rec_temp.days_in_month_code;
906 l_gtsv_rec_in.days_in_yr_code:= gtsv_rec_temp.days_in_yr_code;
907 -- Added new field by DJANASWA for ER 6274359(H)
908 l_gtsv_rec_in.isg_arrears_pay_dates_option := gtsv_rec_temp.isg_arrears_pay_dates_option;
909
910 l_dummy := Okl_Api.G_TRUE;
911 END LOOP;
912 IF (l_dummy = okl_api.g_false) THEN
913 okl_api.SET_MESSAGE( p_app_name => g_app_name,
914 p_msg_name => g_invalid_value,
915 p_token1 => g_col_name_token,
916 p_token1_value => 'GTS_ID');
917 RAISE OKL_API.G_EXCEPTION_ERROR;
918 END IF;
919
920 IF p_mode IS NULL OR p_mode = G_DEFAULT_MODE
921 THEN
922 -- For Duplicate
923 l_gtsv_rec_in.id := G_MISS_NUM; -- New ID will be created
924 FOR new_tmpt_set_copy_no In okl_new_tmpt_set_copy_no LOOP
925 l_new_seq_value := new_tmpt_set_copy_no.next_number;
926 END LOOP;
927 l_gtsv_rec_in.name := ( SUBSTR(l_gtsv_rec_in.name, 1, 135) || '-COPY')
928 || l_new_seq_value;
929 OKL_GTS_PVT.insert_row(
930 p_api_version => l_api_version
931 ,p_init_msg_list => p_init_msg_list
932 ,x_return_status => l_return_status
933 ,x_msg_count => l_msg_count
934 ,x_msg_data => l_msg_data
935 ,p_gtsv_rec => l_gtsv_rec_in
936 ,x_gtsv_rec => l_gtsv_rec_out
937 );
938 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
941 RAISE okl_api.G_EXCEPTION_ERROR;
942 END IF;
943 -- For Duplicate
944 l_gttv_rec_in.gts_id := l_gtsv_rec_out.id; -- Id of the New GTS Created
945 l_gttv_rec_in.version := '1.0'; -- VERSION TO 1.0
946 ELSE
947 -- Fetch the Maximum of Start Date of the contracts using this template
948
949 FOR new_date_rec IN okl_new_version_date_csr(l_gtt_id)
950 LOOP
951 l_max_chr_start_date := new_date_rec.max_start_date;
952 END LOOP;
953 IF ( l_max_chr_start_date IS NULL )
954 THEN
955 -- If no start_date is found, then take the previous version start_date
956 -- as the max_Start_date
957 l_max_chr_start_date := l_gttv_rec_in.start_date;
958 END IF;
959 -- Modifications to be done for the current template version
960 -- Current version template end_date will be updated with l_max_chr_start_date + 1
961 l_gttv_rec_prev_version_in := l_gttv_rec_in;
962 l_gttv_rec_prev_version_in.id := l_gtt_id;
963 l_gttv_rec_prev_version_in.end_date := l_max_chr_start_date + 1;
964 okl_gtt_pvt.update_row(
965 p_api_version => l_api_version
966 ,p_init_msg_list => p_init_msg_list
967 ,x_return_status => l_return_status
968 ,x_msg_count => l_msg_count
969 ,x_msg_data => l_msg_data
970 ,p_gttv_rec => l_gttv_rec_prev_version_in
971 ,x_gttv_rec => l_gttv_rec_prev_version_out
972 );
973 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
974 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
975 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
976 RAISE OKL_API.G_EXCEPTION_ERROR;
977 END IF;
978 -- Modifications for New Version
979 l_gttv_rec_in.version := TRIM(TO_CHAR( TO_NUMBER( l_gttv_rec_in.version ) + 1 ) ) || '.0';
980 l_gttv_rec_in.start_date := l_gttv_rec_prev_version_out.end_date + 1;
981
982 END IF;
983 l_gttv_rec_in.id := G_MISS_NUM;
984 l_gttv_rec_in.tmpt_status := G_INIT_TMPT_STATUS; -- Make the New Template Status to NEW
985 l_gttv_rec_in.end_date := G_MISS_DATE; -- END_DATE as G_MISS_DATE
986 okl_gtt_pvt.insert_row(
987 p_api_version => l_api_version
988 ,p_init_msg_list => p_init_msg_list
989 ,x_return_status => l_return_status
990 ,x_msg_count => l_msg_count
991 ,x_msg_data => l_msg_data
992 ,p_gttv_rec => l_gttv_rec_in
993 ,x_gttv_rec => l_gttv_rec_out
994 );
995 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
996 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
997 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
998 RAISE OKL_API.G_EXCEPTION_ERROR;
999 END IF;
1000 -- Fetch all the pricing parameters and call the API
1001 i := 0;
1002 FOR gtpv_rec_temp IN okl_st_gen_prc_params_csr(l_gtt_id)
1003 LOOP
1004 l_gtpv_tbl_in(i).name := gtpv_rec_temp.name;
1005 l_gtpv_tbl_in(i).description := gtpv_rec_temp.description ;
1006 l_gtpv_tbl_in(i).display_yn := gtpv_rec_temp.display_yn ;
1007 l_gtpv_tbl_in(i).update_yn := gtpv_rec_temp.update_yn ;
1008 l_gtpv_tbl_in(i).prc_eng_ident := gtpv_rec_temp.prc_eng_ident ;
1009 l_gtpv_tbl_in(i).default_value := gtpv_rec_temp.default_value ;
1010 -- Populate records with the new Template ID
1011 l_gtpv_tbl_in(i).gtt_id := l_gttv_rec_out.id;
1012 i := i + 1;
1013 END LOOP;
1014 IF (l_gtpv_tbl_in.COUNT > 0 )
1015 THEN
1016 -- Call the TAPI Procedcure to perform the actual inserts
1017 okl_gtp_pvt.insert_row(
1018 p_api_version => l_api_version
1019 ,p_init_msg_list => p_init_msg_list
1020 ,x_return_status => l_return_status
1021 ,x_msg_count => l_msg_count
1022 ,x_msg_data => l_msg_data
1023 ,p_gtpv_tbl => l_gtpv_tbl_in
1024 ,x_gtpv_tbl => l_gtpv_tbl_out
1025 );
1026 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1027 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1029 RAISE OKL_API.G_EXCEPTION_ERROR;
1030 END IF;
1031 END IF;
1032
1033 -- Fetch all the primary streams and call the insert API
1034 i := 0;
1035 FOR gtlv_rec_temp IN okl_st_gen_tmpt_lns_csr(l_gtt_id, G_INIT_PRIMARY_YN_YES )
1036 LOOP
1037 l_gtlv_tbl_pri_in(i).primary_yn := G_INIT_PRIMARY_YN_YES;
1038 l_gtlv_tbl_pri_in(i).primary_sty_id := gtlv_rec_temp.primary_sty_id;
1039 -- Dependent Streams wont be present for this Stream
1040 --l_gtlv_tbl_in(i).dependent_sty_id := gtlv_rec_temp.dependent_sty_id;
1041 l_gtlv_tbl_pri_in(i).pricing_name := gtlv_rec_temp.pricing_name;
1042 -- Populate the Template ID as the new Template Created
1043 l_gtlv_tbl_pri_in(i).gtt_id := l_gttv_rec_out.id;
1044 i := i + 1;
1045 END LOOP;
1046 IF (l_gtlv_tbl_pri_in.COUNT > 0 )
1047 THEN
1048 -- Call the TAPI Procedcure to perform the actual inserts
1049 okl_gtl_pvt.insert_row(
1050 p_api_version => l_api_version
1051 ,p_init_msg_list => p_init_msg_list
1052 ,x_return_status => l_return_status
1053 ,x_msg_count => l_msg_count
1054 ,x_msg_data => l_msg_data
1055 ,p_gtlv_tbl => l_gtlv_tbl_pri_in
1056 ,x_gtlv_tbl => l_gtlv_tbl_pri_out
1057 );
1058 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1059 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1060 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1061 RAISE OKL_API.G_EXCEPTION_ERROR;
1062 END IF;
1063 END IF;
1064
1065 -- Fetch all the dependent streams and call the insert API
1066 i := 0;
1067 FOR gtlv_rec_temp IN okl_st_gen_tmpt_lns_csr(l_gtt_id, G_INIT_PRIMARY_YN_NO )
1068 LOOP
1069 l_gtlv_tbl_dep_in(i).primary_yn := G_INIT_PRIMARY_YN_NO;
1070 l_gtlv_tbl_dep_in(i).primary_sty_id := gtlv_rec_temp.primary_sty_id;
1071 l_gtlv_tbl_dep_in(i).dependent_sty_id := gtlv_rec_temp.dependent_sty_id;
1072 l_gtlv_tbl_dep_in(i).pricing_name := gtlv_rec_temp.pricing_name;
1073 -- Populate the Template ID as the new Template Created
1074 l_gtlv_tbl_dep_in(i).gtt_id := l_gttv_rec_out.id;
1075 i := i + 1;
1076 END LOOP;
1077 IF (l_gtlv_tbl_dep_in.COUNT > 0 )
1078 THEN
1079 -- Call the TAPI Procedcure to perform the actual inserts
1080 okl_gtl_pvt.insert_row(
1081 p_api_version => l_api_version
1082 ,p_init_msg_list => p_init_msg_list
1083 ,x_return_status => l_return_status
1084 ,x_msg_count => l_msg_count
1085 ,x_msg_data => l_msg_data
1086 ,p_gtlv_tbl => l_gtlv_tbl_dep_in
1087 ,x_gtlv_tbl => l_gtlv_tbl_dep_out
1088 );
1089 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1090 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1091 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1092 RAISE OKL_API.G_EXCEPTION_ERROR;
1093 END IF;
1094 END IF;
1095
1096 x_gttv_rec := l_gttv_rec_out;
1097 x_return_status := l_return_status;
1098 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1099 EXCEPTION
1100 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1101 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1102 (l_api_name,
1103 G_PKG_NAME,
1104 'OKL_API.G_RET_STS_ERROR',
1105 x_msg_count,
1106 x_msg_data,
1107 '_PVT');
1108 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1109 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1110 ( l_api_name,
1111 G_PKG_NAME,
1112 'OKL_API.G_RET_STS_UNEXP_ERROR',
1113 x_msg_count,
1114 x_msg_data,
1115 '_PVT');
1116 WHEN OTHERS THEN
1117 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1118 ( l_api_name,
1119 G_PKG_NAME,
1120 'OTHERS',
1121 x_msg_count,
1122 x_msg_data,
1123 '_PVT');
1124 END;
1125
1126 ---------------------------------------------------------------------------
1127 -- PROCEDURE delete_tmpt_prc_params
1128 ---------------------------------------------------------------------------
1129 -- Start of comments
1130 --
1131 -- Procedure Name : delete_tmpt_prc_params
1132 -- Description : Deletes the Template Pricing Parameters
1133 -- Business Rules :
1134 -- Parameters :
1135 -- Version : 1.0
1136 -- End of comments
1137 ---------------------------------------------------------------------------
1138
1139 Procedure delete_tmpt_prc_params(
1140 p_api_version IN NUMBER
1141 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1142 ,x_return_status OUT NOCOPY VARCHAR2
1143 ,x_msg_count OUT NOCOPY NUMBER
1144 ,x_msg_data OUT NOCOPY VARCHAR2
1145 ,p_gtpv_tbl IN gtpv_tbl_type
1146 ) IS
1147 l_gtt_id okl_st_gen_templates.id%type ;
1148 l_api_name CONSTANT VARCHAR2(40) := 'delete_tmpt_prc_params';
1149 l_api_version CONSTANT NUMBER := 1.0;
1150 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1151
1152 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1153 l_msg_count NUMBER;
1154 l_msg_data VARCHAR2(2000);
1155
1156 l_gttv_rec gttv_rec_type;
1157 l_gtpv_tbl_del_in gtpv_tbl_type := p_gtpv_tbl;
1158
1159 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
1160
1161 BEGIN
1162 -- Perform the Initializations
1163 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1164 l_return_status := OKL_API.START_ACTIVITY( l_api_name
1165 ,g_pkg_name
1166 ,p_init_msg_list
1167 ,l_api_version
1168 ,p_api_version
1169 ,'_PVT'
1170 ,x_return_status);
1171 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1172 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1173 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1174 RAISE OKL_API.G_EXCEPTION_ERROR;
1175 END IF;
1176 OKL_GTP_PVT.delete_row(
1177 p_api_version => l_api_version
1178 ,p_init_msg_list => l_init_msg_list
1179 ,x_return_status => l_return_status
1180 ,x_msg_count => l_msg_count
1181 ,x_msg_data => l_msg_data
1182 ,p_gtpv_tbl => l_gtpv_tbl_del_in
1183 );
1184 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1185 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1186 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1187 RAISE OKL_API.G_EXCEPTION_ERROR;
1188 END IF;
1189
1190 x_return_status := l_return_status;
1191 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1192 EXCEPTION
1193 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1194 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1195 (l_api_name,
1196 G_PKG_NAME,
1197 'OKL_API.G_RET_STS_ERROR',
1198 x_msg_count,
1199 x_msg_data,
1200 '_PVT');
1201 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1202 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1203 ( l_api_name,
1204 G_PKG_NAME,
1205 'OKL_API.G_RET_STS_UNEXP_ERROR',
1206 x_msg_count,
1207 x_msg_data,
1208 '_PVT');
1209 WHEN OTHERS THEN
1210 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1211 ( l_api_name,
1212 G_PKG_NAME,
1213 'OTHERS',
1214 x_msg_count,
1215 x_msg_data,
1216 '_PVT');
1217 END;
1218
1219 ---------------------------------------------------------------------------
1220 -- PROCEDURE delete_pri_tmpt_lns
1221 ---------------------------------------------------------------------------
1222 -- Start of comments
1223 --
1224 -- Procedure Name : delete_pri_tmpt_lns
1225 -- Description : Deletes the Primary Template Lines
1226 -- Business Rules :
1227 -- Parameters :
1228 -- Version : 1.0
1229 -- End of comments
1230 ---------------------------------------------------------------------------
1231
1232 Procedure delete_pri_tmpt_lns(
1233 p_api_version IN NUMBER
1234 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1235 ,x_return_status OUT NOCOPY VARCHAR2
1236 ,x_msg_count OUT NOCOPY NUMBER
1237 ,x_msg_data OUT NOCOPY VARCHAR2
1238 ,p_gtlv_tbl IN gtlv_tbl_type
1239 )IS
1240 l_gtt_id okl_st_gen_templates.id%type;
1241 l_api_name CONSTANT VARCHAR2(40) := 'delete_pri_tmpt_lns';
1242 l_api_version CONSTANT NUMBER := 1.0;
1243 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1244
1245 l_init_msg_list VARCHAR2(1);
1246 l_msg_count NUMBER;
1247 l_msg_data VARCHAR2(2000);
1248 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
1249
1250 l_gttv_rec gttv_rec_type;
1251 l_gtlv_pri_tbl_del_in gtlv_tbl_type;
1252 l_gtlv_dep_tbl_del_in gtlv_tbl_type;
1253 i NUMBER := 0;
1254
1255 CURSOR okl_st_gen_tmpt_dep_lns_csr( p_gtt_id IN OKL_ST_GEN_TMPT_LNS.gtt_id%type
1256 ,p_primary_sty_id IN OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID %type) IS
1257 SELECT ID
1258 ,PRIMARY_STY_ID
1259 ,DEPENDENT_STY_ID
1260 ,GTT_ID
1261 FROM OKL_ST_GEN_TMPT_LNS
1262 WHERE PRIMARY_YN = 'N'
1263 AND PRIMARY_STY_ID = p_primary_sty_id
1264 AND GTT_ID = p_gtt_id;
1265 BEGIN
1266 -- Perform the Initializations
1267 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1268 l_return_status := OKL_API.START_ACTIVITY( l_api_name
1269 ,g_pkg_name
1270 ,p_init_msg_list
1271 ,l_api_version
1272 ,p_api_version
1273 ,'_PVT'
1274 ,x_return_status);
1275 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1276 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1277 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1278 RAISE OKL_API.G_EXCEPTION_ERROR;
1279 END IF;
1280
1281 l_gtlv_pri_tbl_del_in := p_gtlv_tbl;
1282 FOR J in l_gtlv_pri_tbl_del_in.FIRST .. l_gtlv_pri_tbl_del_in.LAST
1283 LOOP
1284 -- Open the cursor for this particular primary_sty_id
1285 -- and build l_gtlv_dep_tbl_del_in and call the table API.
1286 i := 1;
1287 FOR l_gtlv_dep_rec in okl_st_gen_tmpt_dep_lns_csr( l_gtlv_pri_tbl_del_in(j).gtt_id,
1288 l_gtlv_pri_tbl_del_in(j).primary_sty_id
1289 )
1290 LOOP
1291 l_gtlv_dep_tbl_del_in(i).id := l_gtlv_dep_rec.id;
1292 l_gtlv_dep_tbl_del_in(i).gtt_id := l_gtlv_dep_rec.gtt_id;
1293 l_gtlv_dep_tbl_del_in(i).primary_sty_id := l_gtlv_dep_rec.primary_sty_id;
1294 l_gtlv_dep_tbl_del_in(i).dependent_sty_id := l_gtlv_dep_rec.dependent_sty_id;
1295 i := i + 1;
1296 END LOOP;
1297 IF l_gtlv_dep_tbl_del_in.COUNT > 0
1298 THEN
1299 Okl_Gtl_Pvt.delete_row(
1300 p_api_version => l_api_version,
1301 p_init_msg_list => l_init_msg_list,
1302 x_return_status => l_return_status,
1303 x_msg_count => l_msg_count,
1304 x_msg_data => l_msg_data,
1305 p_gtlv_tbl => l_gtlv_dep_tbl_del_in);
1306
1307 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1308 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1309 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1310 RAISE OKL_API.G_EXCEPTION_ERROR;
1311 END IF;
1312 l_gtlv_dep_tbl_del_in.DELETE;
1313 END IF;
1314 END LOOP;
1315 Okl_Gtl_Pvt.delete_row(
1316 p_api_version => l_api_version,
1317 p_init_msg_list => l_init_msg_list,
1318 x_return_status => l_return_status,
1319 x_msg_count => l_msg_count,
1320 x_msg_data => l_msg_data,
1321 p_gtlv_tbl => l_gtlv_pri_tbl_del_in);
1322
1323 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1324 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1325 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1326 RAISE OKL_API.G_EXCEPTION_ERROR;
1327 END IF;
1328
1329 x_return_status := l_return_status;
1330 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1331 EXCEPTION
1332 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1333 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1334 (l_api_name,
1335 G_PKG_NAME,
1336 'OKL_API.G_RET_STS_ERROR',
1337 x_msg_count,
1338 x_msg_data,
1339 '_PVT');
1340 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1341 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1342 ( l_api_name,
1343 G_PKG_NAME,
1344 'OKL_API.G_RET_STS_UNEXP_ERROR',
1345 x_msg_count,
1346 x_msg_data,
1347 '_PVT');
1348 WHEN OTHERS THEN
1349 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1350 ( l_api_name,
1351 G_PKG_NAME,
1352 'OTHERS',
1353 x_msg_count,
1354 x_msg_data,
1355 '_PVT');
1356 END;
1357
1358 ---------------------------------------------------------------------------
1359 -- PROCEDURE delete_dep_tmpt_lns
1360 ---------------------------------------------------------------------------
1361 -- Start of comments
1362 --
1363 -- Procedure Name : delete_dep_tmpt_lns
1364 -- Description : Deletes the Dependent Lines of a Template
1365 -- Business Rules :
1366 -- Parameters :
1367 -- Version : 1.0
1368 -- End of comments
1369 ---------------------------------------------------------------------------
1370
1371 Procedure delete_dep_tmpt_lns(
1372 p_api_version IN NUMBER
1373 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1374 ,x_return_status OUT NOCOPY VARCHAR2
1375 ,x_msg_count OUT NOCOPY NUMBER
1376 ,x_msg_data OUT NOCOPY VARCHAR2
1377 ,p_gtlv_tbl IN gtlv_tbl_type
1378 )IS
1379 l_gtt_id okl_st_gen_templates.id%type;
1380 l_api_name CONSTANT VARCHAR2(40) := 'delete_dep_tmpt_lns';
1381 l_api_version CONSTANT NUMBER := 1.0;
1382 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1383
1384 l_init_msg_list VARCHAR2(1);
1385 l_msg_count NUMBER;
1386 l_msg_data VARCHAR2(2000);
1387 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
1388
1389 l_gttv_rec gttv_rec_type;
1390 l_gtlv_tbl_del_in gtlv_tbl_type := p_gtlv_tbl;
1391 BEGIN
1392 -- Perform the Initializations
1393 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1394 l_return_status := OKL_API.START_ACTIVITY( l_api_name
1395 ,g_pkg_name
1396 ,p_init_msg_list
1397 ,l_api_version
1398 ,p_api_version
1399 ,'_PVT'
1400 ,x_return_status);
1401 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1402 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1403 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1404 RAISE OKL_API.G_EXCEPTION_ERROR;
1405 END IF;
1406
1407 Okl_Gtl_Pvt.delete_row(
1408 p_api_version => l_api_version,
1409 p_init_msg_list => l_init_msg_list,
1410 x_return_status => l_return_status,
1411 x_msg_count => l_msg_count,
1412 x_msg_data => l_msg_data,
1413 p_gtlv_tbl => l_gtlv_tbl_del_in);
1414
1415 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1418 RAISE OKL_API.G_EXCEPTION_ERROR;
1419 END IF;
1420
1421 x_return_status := l_return_status;
1422 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1423 EXCEPTION
1424 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1425 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1426 (l_api_name,
1427 G_PKG_NAME,
1428 'OKL_API.G_RET_STS_ERROR',
1429 x_msg_count,
1430 x_msg_data,
1431 '_PVT');
1432 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1433 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1434 ( l_api_name,
1435 G_PKG_NAME,
1436 'OKL_API.G_RET_STS_UNEXP_ERROR',
1437 x_msg_count,
1438 x_msg_data,
1439 '_PVT');
1440 WHEN OTHERS THEN
1441 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1442 ( l_api_name,
1443 G_PKG_NAME,
1444 'OTHERS',
1445 x_msg_count,
1446 x_msg_data,
1447 '_PVT');
1448 END;
1449
1450 ---------------------------------------------------------------------------
1451 -- PROCEDURE validate_template
1452 ---------------------------------------------------------------------------
1453 -- Start of comments
1454 --
1455 -- Procedure Name : validate_template
1456 -- Description : Validate a Stream Generation Template
1457 -- Business Rules :
1458 -- Parameters :
1459 -- Version : 1.0
1460 -- End of comments
1461 ---------------------------------------------------------------------------
1462 FUNCTION belongs_to_ls( p_book_classification IN VARCHAR2 )
1463 RETURN BOOLEAN
1464 IS
1465 l_book_classification OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE := UPPER( p_book_classification );
1466 BEGIN
1467 IF ( l_book_classification = G_LEASEDF_DEAL_TYPE OR
1468 l_book_classification = G_LEASEOP_DEAL_TYPE OR
1469 l_book_classification = G_LEASEST_DEAL_TYPE )
1470 THEN
1471 RETURN TRUE;
1472 ELSE
1473 RETURN FALSE;
1474 END IF;
1475 END;
1476
1477 FUNCTION belongs_to_ln( p_book_classification IN VARCHAR2 )
1478 RETURN BOOLEAN
1479 IS
1480 l_book_classification OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE := UPPER( p_book_classification );
1481 BEGIN
1482 IF ( l_book_classification = G_LOAN_DEAL_TYPE OR
1483 l_book_classification = G_LOAN_REV_DEAL_TYPE )
1484 THEN
1485 RETURN TRUE;
1486 ELSE
1487 RETURN FALSE;
1488 END IF;
1489 END;
1490
1491 FUNCTION belongs_to_df_st( p_book_classification IN VARCHAR2 )
1492 RETURN BOOLEAN
1493 IS
1494 l_book_classification OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE := UPPER( p_book_classification );
1495 BEGIN
1496 IF ( l_book_classification = G_LEASEDF_DEAL_TYPE OR
1497 l_book_classification = G_LEASEST_DEAL_TYPE )
1498 THEN
1499 RETURN TRUE;
1500 ELSE
1501 RETURN FALSE;
1502 END IF;
1503 END;
1504
1505 FUNCTION belongs_to_op( p_book_classification IN VARCHAR2 )
1506 RETURN BOOLEAN
1507 IS
1508 l_book_classification OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE := UPPER( p_book_classification );
1509 BEGIN
1510 IF ( l_book_classification = G_LEASEOP_DEAL_TYPE )
1511 THEN
1512 RETURN TRUE;
1513 ELSE
1514 RETURN FALSE;
1515 END IF;
1516 END;
1517
1518 PROCEDURE put_messages_in_table( p_msg_name IN VARCHAR2
1519 ,p_during_upd_flag IN VARCHAR
1520 ,x_msg_out OUT NOCOPY VARCHAR2
1521 ,p_token1 IN VARCHAR2 DEFAULT NULL
1522 ,p_value1 IN VARCHAR2 DEFAULT NULL
1523 ,p_token2 IN VARCHAR2 DEFAULT NULL
1524 ,p_value2 IN VARCHAR2 DEFAULT NULL
1525 )
1526 IS
1527 l_msg VARCHAR2(2700);
1528 BEGIN
1529 FND_MESSAGE.SET_NAME( g_app_name, p_msg_name );
1530 IF ( p_token1 IS NOT NULL )
1531 THEN
1532 FND_MESSAGE.SET_TOKEN( TOKEN => p_token1,
1533 VALUE => p_value1);
1534 END IF;
1535 IF ( p_token2 IS NOT NULL )
1536 THEN
1537 FND_MESSAGE.SET_TOKEN( TOKEN => p_token2,
1538 VALUE => p_value2 );
1539 END IF;
1540 l_msg := FND_MESSAGE.GET;
1541 IF ( UPPER(p_during_upd_flag) = 'T' )
1542 THEN
1543 OKL_API.SET_MESSAGE( g_app_name
1544 ,p_msg_name
1545 ,p_token1
1546 ,p_value1
1547 ,p_token2
1548 ,p_value2
1549 );
1550 END IF;
1551 -- Return the Message
1552 x_msg_out := l_msg;
1553 END put_messages_in_table;
1554
1555 ---------------------------------------------------------------------------
1556 -- PROCEDURE val_ic_rr_day_con_methods
1557 ---------------------------------------------------------------------------
1558 -- Start of comments
1559 --
1560 -- Procedure Name : val_ic_rr_day_con_methods
1561 -- Description : Validates a Stream Generation Template based on IC / Rev Rec methods
1562 -- Business Rules : Rule 0. Day convention validations
1563 -- Rule 1. If Interest Calculation Method is not Fixed
1564 -- or if Revenue Recognition Method is not equal Actual,
1565 -- Pricing Engine must be External
1566 -- Rule 2/3. If Book classification is LOAN / REVLOAN, billable flag is checked
1567 -- Rule 4. For Lease classification, Rent stream must be billable and
1568 -- RR only can be Streams and IC can only be Reamort and Float Factor
1569 --
1570 -- Parameters :
1571 -- Version : 1.0
1572 -- End of comments
1573 ---------------------------------------------------------------------------
1574
1575 PROCEDURE val_ic_rr_day_con_methods(
1576 p_api_version IN NUMBER
1577 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1578 ,x_return_status OUT NOCOPY VARCHAR2
1579 ,x_msg_count OUT NOCOPY NUMBER
1580 ,x_msg_data OUT NOCOPY VARCHAR2
1581 ,p_gtt_id IN okl_st_gen_templates.id%type
1582 ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
1583 ,p_during_upd_flag IN VARCHAR
1584 ,p_book_classification IN VARCHAR
1585 )
1586 IS
1587 CURSOR get_revrec_intcalc_meth IS
1588 SELECT interest_calc_meth_code,
1589 revenue_recog_meth_code,
1590 pricing_engine ,
1591 days_in_month_code ,
1592 days_in_yr_code
1593 FROM OKL_ST_GEN_TMPT_SETS
1594 WHERE ID = (
1595 SELECT gts_id
1596 FROM okl_st_gen_templates
1597 WHERE id = p_gtt_id
1598 );
1599
1600 CURSOR get_billable_flag IS
1601 SELECT stb.stream_type_purpose purpose,
1602 stb.billable_yn
1603 FROM OKL_ST_GEN_TMPT_LNS gtl
1604 ,okl_strm_type_b stb
1605 WHERE
1606 gtl.gtt_id = p_gtt_id
1607 AND gtl.DEPENDENT_STY_ID = stb.id
1608 AND gtl.primary_yn = 'N'
1609 AND stb.stream_type_purpose IN
1610 (
1611 'PRINCIPAL_PAYMENT'
1612 ,'INTEREST_PAYMENT'
1613 ,'LOAN_PAYMENT'
1614 )
1615 AND gtl.PRIMARY_STY_ID IN
1616 (
1617 SELECT id
1618 FROM okl_strm_type_b
1619 WHERE stream_type_purpose='RENT'
1620 );
1621
1622 CURSOR get_rent_flag IS
1623 SELECT stb.stream_type_purpose purpose, stb.billable_yn
1624 FROM OKL_ST_GEN_TMPT_LNS gtl
1625 ,okl_strm_type_b stb
1626 WHERE
1627 gtl.gtt_id = p_gtt_id
1628 AND gtl.PRIMARY_STY_ID = stb.id
1629 AND gtl.primary_yn = 'Y'
1630 AND stb.stream_type_purpose ='RENT';
1631 --Local variable declarations
1632 l_interest_calc_meth OKL_ST_GEN_TMPT_SETS.interest_calc_meth_code%TYPE;
1633 l_revenue_recog_meth OKL_ST_GEN_TMPT_SETS.revenue_recog_meth_code%TYPE;
1634 l_purpose okl_strm_type_b.billable_yn%TYPE;
1635 l_billable_yn okl_strm_type_b.stream_type_purpose%TYPE;
1636 l_pricing_engine OKL_ST_GEN_TMPT_SETS.pricing_engine%TYPE;
1637 l_days_in_month_code OKL_ST_GEN_TMPT_SETS.days_in_month_code %TYPE;
1638 l_days_in_yr_code OKL_ST_GEN_TMPT_SETS.days_in_yr_code%TYPE;
1639 l_days_month_yr_concat VARCHAR2(2700);
1640 l_msgs_count NUMBER := 0;
1641 l_message VARCHAR2(2700);
1642 get_billable_flag_rec get_billable_flag%ROWTYPE;
1643
1644 BEGIN
1645 OPEN get_revrec_intcalc_meth;
1646 FETCH get_revrec_intcalc_meth INTO l_interest_calc_meth,
1647 l_revenue_recog_meth,
1648 l_pricing_engine,
1649 l_days_in_month_code,
1650 l_days_in_yr_code;
1651 CLOSE get_revrec_intcalc_meth;
1652 l_days_month_yr_concat := l_days_in_month_code ||'*'|| l_days_in_yr_code;
1653 --Rule 0. Day convention validations
1654 IF NVL(l_pricing_engine,'*') = 'EXTERNAL'
1655 THEN
1656 IF (l_days_month_yr_concat <> '30*360')
1657 AND (l_days_month_yr_concat <> 'ACTUAL*365')
1658 AND (l_days_month_yr_concat <> 'ACTUAL*360')
1659 AND (l_days_month_yr_concat <> '30*365')
1660 AND (l_days_month_yr_concat <> 'ACTUAL*ACTUAL')
1661 THEN
1662 /*Add error to table */
1663 put_messages_in_table(G_OKL_DAY_CONVEN_VAL_EXT
1664 ,p_during_upd_flag
1665 ,l_message
1666 );
1667 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1668 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1669 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1670 l_msgs_count := l_msgs_count + 1;
1671 END IF;
1672 ELSIF NVL(l_pricing_engine,'*') = 'INTERNAL'
1673 THEN
1674 IF (l_days_month_yr_concat <> '30*360')
1675 AND (l_days_month_yr_concat <> 'ACTUAL*360')
1676 AND (l_days_month_yr_concat <> 'ACTUAL*ACTUAL')
1677 THEN
1678 /*Add error to table */
1679 put_messages_in_table(G_OKL_DAY_CONVEN_VAL_INT
1680 ,p_during_upd_flag
1681 ,l_message
1682 );
1683 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1684 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1685 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1686 l_msgs_count := l_msgs_count + 1;
1687 END IF;
1688 END IF;
1689 --Rule 1. If Interest Calculation Method is not Fixed or if Revenue Recognition Method is not equal Actual,
1690 -- Pricing Engine must be External
1691 -- VR Upgrade change. Bug 4756154
1692 -- Modified Rule is
1693 -- "If Interest Calculation Method is not Fixed, Fixed-Upgrade or if Revenue Recognition Method
1694 -- is not STREAMS, then Pricing Engine Must be EXTERNAL."
1695 IF ( NVL(l_interest_calc_meth,'*') <> 'FIXED' AND
1696 NVL( l_interest_calc_meth, '*') <> 'FIXED_UPGRADE' )
1697 OR NVL(l_revenue_recog_meth,'*') <> 'STREAMS'
1698 THEN
1699 IF NVL(l_pricing_engine,'*') <> 'EXTERNAL'
1700 THEN
1701 put_messages_in_table(G_OKL_IC_RR_PRC_ENG_EXT
1702 ,p_during_upd_flag
1703 ,l_message
1704 );
1705 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1706 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1707 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1708 l_msgs_count := l_msgs_count + 1;
1709 END IF;
1710 END IF;
1711 --Rule 2. Loan classification is LOAN / REV LOAN
1712 IF (belongs_to_ln(p_book_classification)) AND
1713 l_interest_calc_meth <> 'FIXED_UPGRADE' -- VR Upgrade. Bug 4756154
1714 THEN
1715 FOR get_billable_flag_rec IN get_billable_flag
1716 LOOP
1717 /*If Rev Rec is STREAMS, principal, loan payment etc should be defined with appropriate flags*/
1718 IF l_revenue_recog_meth = 'STREAMS'
1719 THEN
1720 /*Check billable flags*/
1721 IF get_billable_flag_rec.purpose = 'PRINCIPAL_PAYMENT' AND
1722 get_billable_flag_rec.billable_yn = 'N'
1723 THEN
1724 /*Add to error table*/
1725 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1726 ,p_during_upd_flag
1727 ,l_message
1728 ,p_token1 => 'STREAM'
1729 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'PRINCIPAL_PAYMENT' )
1730 ,p_token2 => 'BILL_YN'
1731 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'Y' )
1732 );
1733 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1734 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1735 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1736 l_msgs_count := l_msgs_count + 1;
1737 ELSIF get_billable_flag_rec.purpose = 'INTEREST_PAYMENT' AND
1738 get_billable_flag_rec.billable_yn = 'N'
1739 THEN
1740 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1741 ,p_during_upd_flag
1742 ,l_message
1743 ,p_token1 => 'STREAM'
1744 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'INTEREST_PAYMENT' )
1745 ,p_token2 => 'BILL_YN'
1746 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'Y' )
1747 );
1748 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1749 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1750 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1751 l_msgs_count := l_msgs_count + 1;
1752 ELSIF get_billable_flag_rec.purpose = 'LOAN_PAYMENT' AND
1753 get_billable_flag_rec.billable_yn = 'Y'
1754 THEN
1755 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1756 ,p_during_upd_flag
1757 ,l_message
1758 ,p_token1 => 'STREAM'
1759 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'LOAN_PAYMENT' )
1760 ,p_token2 => 'BILL_YN'
1761 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1762 );
1763 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1764 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1765 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1766 l_msgs_count := l_msgs_count + 1;
1767 END IF;
1768 /*If Rev Rec is ACTUAL, int calc = FIXED OR REAMORT principal payment etc should be defined with appropriate flags*/
1769 ELSIF l_revenue_recog_meth = 'ACTUAL' AND (l_interest_calc_meth = 'FIXED' OR l_interest_calc_meth = 'REAMORT')
1770 THEN
1771 /*Check billable flags*/
1772 IF get_billable_flag_rec.purpose = 'PRINCIPAL_PAYMENT' AND
1773 get_billable_flag_rec.billable_yn = 'Y'
1774 THEN
1775 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1776 ,p_during_upd_flag
1777 ,l_message
1778 ,p_token1 => 'STREAM'
1779 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'PRINCIPAL_PAYMENT' )
1780 ,p_token2 => 'BILL_YN'
1781 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1782 );
1783
1784 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1785 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1786 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1787 l_msgs_count := l_msgs_count + 1;
1788 ELSIF get_billable_flag_rec.purpose = 'INTEREST_PAYMENT' AND
1789 get_billable_flag_rec.billable_yn = 'Y'
1790 THEN
1791 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1792 ,p_during_upd_flag
1793 ,l_message
1794 ,p_token1 => 'STREAM'
1795 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'INTEREST_PAYMENT' )
1796 ,p_token2 => 'BILL_YN'
1797 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1798 );
1799
1800 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1801 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1802 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1803 l_msgs_count := l_msgs_count + 1;
1804 ELSIF get_billable_flag_rec.purpose = 'LOAN_PAYMENT' AND
1805 get_billable_flag_rec.billable_yn = 'N'
1806 THEN
1807 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1808 ,p_during_upd_flag
1809 ,l_message
1810 ,p_token1 => 'STREAM'
1811 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'LOAN_PAYMENT' )
1812 ,p_token2 => 'BILL_YN'
1813 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'Y' )
1814 );
1815
1816 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1817 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1818 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1819 l_msgs_count := l_msgs_count + 1;
1820 END IF;
1821 ELSIF l_revenue_recog_meth = 'ACTUAL' AND l_interest_calc_meth = 'FLOAT'
1822 THEN
1823 /*Check billable flags*/
1824 IF get_billable_flag_rec.purpose = 'PRINCIPAL_PAYMENT' AND
1825 get_billable_flag_rec.billable_yn = 'Y'
1826 THEN
1827 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1828 ,p_during_upd_flag
1829 ,l_message
1830 ,p_token1 => 'STREAM'
1831 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'PRINCIPAL_PAYMENT' )
1832 ,p_token2 => 'BILL_YN'
1833 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1834 );
1835 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1836 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1837 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1838 l_msgs_count := l_msgs_count + 1;
1839 ELSIF get_billable_flag_rec.purpose = 'INTEREST_PAYMENT' AND
1840 get_billable_flag_rec.billable_yn = 'Y'
1841 THEN
1842 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1843 ,p_during_upd_flag
1844 ,l_message
1845 ,p_token1 => 'STREAM'
1846 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'INTEREST_PAYMENT' )
1847 ,p_token2 => 'BILL_YN'
1848 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1849 );
1850 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1851 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1852 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1853 l_msgs_count := l_msgs_count + 1;
1854 ELSIF get_billable_flag_rec.purpose = 'LOAN_PAYMENT' AND
1855 get_billable_flag_rec.billable_yn = 'Y'
1856 THEN
1857 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1858 ,p_during_upd_flag
1859 ,l_message
1860 ,p_token1 => 'STREAM'
1861 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'LOAN_PAYMENT' )
1862 ,p_token2 => 'BILL_YN'
1863 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1864 );
1865 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1866 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1867 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1868 l_msgs_count := l_msgs_count + 1;
1869 END IF;
1870 ELSIF l_revenue_recog_meth = 'ESTIMATED_AND_BILLED'
1871 THEN
1872 /*Check billable flags*/
1873 IF get_billable_flag_rec.purpose = 'PRINCIPAL_PAYMENT' AND
1874 get_billable_flag_rec.billable_yn = 'N'
1875 THEN
1876 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1877 ,p_during_upd_flag
1878 ,l_message
1879 ,p_token1 => 'STREAM'
1880 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'PRINCIPAL_PAYMENT' )
1881 ,p_token2 => 'BILL_YN'
1882 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'Y' )
1883 );
1884 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1885 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1886 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1887 l_msgs_count := l_msgs_count + 1;
1888 ELSIF get_billable_flag_rec.purpose = 'INTEREST_PAYMENT' AND
1889 get_billable_flag_rec.billable_yn = 'Y'
1890 THEN
1891 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1892 ,p_during_upd_flag
1893 ,l_message
1894 ,p_token1 => 'STREAM'
1895 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'INTEREST_PAYMENT' )
1896 ,p_token2 => 'BILL_YN'
1897 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1898 );
1899 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1900 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1901 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1902 l_msgs_count := l_msgs_count + 1;
1903 ELSIF get_billable_flag_rec.purpose = 'LOAN_PAYMENT' AND
1904 get_billable_flag_rec.billable_yn = 'Y'
1905 THEN
1906 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1907 ,p_during_upd_flag
1908 ,l_message
1909 ,p_token1 => 'STREAM'
1910 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_DEPENDENT_PURPOSES, 'LOAN_PAYMENT' )
1911 ,p_token2 => 'BILL_YN'
1912 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'N' )
1913 );
1914 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1915 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1916 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1917 l_msgs_count := l_msgs_count + 1;
1918 END IF;
1919 END IF;
1920 END LOOP;
1921 ELSIF belongs_to_ls(p_book_classification) AND
1922 l_interest_calc_meth <> 'FIXED_UPGRADE' -- VR Upgrade. Bug 4756154
1923 THEN
1924 --Rule 4. For Lease classification, Rent stream must be billable and
1925 --RR only can be Streams and IC can only be Reamort and Float Factor
1926 /* Rent stream must be billable*/
1927 OPEN get_rent_flag;
1928 FETCH get_rent_flag INTO l_purpose,l_billable_yn;
1929 CLOSE get_rent_flag;
1930
1931 IF l_billable_yn = 'N'
1932 THEN
1933 put_messages_in_table( G_OKL_STRM_BILL_FLAG_YN
1934 ,p_during_upd_flag
1935 ,l_message
1936 ,p_token1 => 'STREAM'
1937 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( G_OKL_FIN_PRIMARY_PURPOSES, 'RENT' )
1938 ,p_token2 => 'BILL_YN'
1939 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_YES_NO', 'Y' )
1940 );
1941 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1942 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1943 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1944 l_msgs_count := l_msgs_count + 1;
1945 END IF;
1946 /*RR only can be Streams and IC can only be Reamort and Float Factor*/
1947 IF NVL(l_revenue_recog_meth,'*') <> 'STREAMS' OR
1948 (NVL(l_interest_calc_meth,'*') <> 'REAMORT' AND
1949 NVL(l_interest_calc_meth,'*') <> 'FIXED' AND
1950 NVL(l_interest_calc_meth,'*') <> 'FLOAT_FACTORS' )
1951 THEN
1952 put_messages_in_table( G_OKL_IC_RR_METH_FOR_LS
1953 ,p_during_upd_flag
1954 ,l_message
1955 );
1956 x_error_msgs_tbl(l_msgs_count).error_message := l_message;
1957 x_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1958 x_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1959 l_msgs_count := l_msgs_count + 1;
1960 END IF;
1961 END IF;
1962
1963 END val_ic_rr_day_con_methods;
1964 ---------------------------------------------------------------------------
1965 -- PROCEDURE validate_financial_template
1966 ---------------------------------------------------------------------------
1967 -- Start of comments
1968 --
1969 -- Procedure Name : validate_financial_template
1970 -- Description : Validates a Stream Generation Template with Financial
1971 -- as Product Type
1972 -- Business Rules :
1973 -- Parameters :
1974 -- Version : 1.0
1975 -- End of comments
1976 ---------------------------------------------------------------------------
1977
1978 Procedure validate_financial_template(
1979 p_api_version IN NUMBER
1980 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1981 ,x_return_status OUT NOCOPY VARCHAR2
1982 ,x_msg_count OUT NOCOPY NUMBER
1983 ,x_msg_data OUT NOCOPY VARCHAR2
1984 ,p_gtt_id IN okl_st_gen_templates.id%type
1985 ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
1986 ,p_during_upd_flag IN VARCHAR
1987 ,p_book_classification IN VARCHAR
1988
1989 )IS
1990 l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
1991 l_api_name CONSTANT VARCHAR2(40) := 'validate_financial_template';
1992 l_api_version CONSTANT NUMBER := 1.0;
1993 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1994 l_error_msgs_tbl error_msgs_tbl_type;
1995 l_error_msgs_tbl_val_bill error_msgs_tbl_type;
1996 l_msgs_count NUMBER := 1;
1997
1998 l_init_msg_list VARCHAR2(1);
1999 l_msg_count NUMBER;
2000 l_msg_data VARCHAR2(2000);
2001 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
2002 l_ins_strms_count NUMBER := 0;
2003
2004 l_gttv_rec_in gttv_rec_type;
2005 l_gttv_rec_out gttv_rec_type;
2006
2007 l_message VARCHAR2(2700);
2008
2009
2010 -- 1. If user selects one of the following Insurance purposes then all the
2011 -- insurance stream purposes must be defined.
2012 CURSOR okl_ins_purposes_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2013 SELECT count(*) ins_strms_count
2014 FROM OKL_ST_GEN_TMPT_LNS gtl
2015 , okl_strm_type_b stb
2016 WHERE gtl.primary_sty_id = stb.id
2017 AND GTL.GTT_ID = p_gtt_id
2018 AND GTL.primary_yn = 'Y'
2019 AND stb.stream_type_purpose IN
2020 (
2021 'INSURANCE_RECEIVABLE'
2022 -- Modified by RGOOTY
2023 -- Bug 4096853: Start
2024 ,'INSURANCE_ADJUSTMENT'
2025 -- Bug 4096853: End
2026 ,'INSURANCE_PAYABLE'
2027 ,'INSURANCE_ACCRUAL_ADJUSTMENT'
2028 ,'INSURANCE_EXPENSE_ACCRUAL'
2029 ,'INSURANCE_INCOME_ACCRUAL'
2030 ,'INSURANCE_REFUND'
2031 );
2032
2033 -- 2. Only one stream type of the following primary purposes should be defined.
2034 -- The List contains Purposes for all Book Classifications.
2035 -- The sql statement checkes whether the purposes mentioned in the set are repeated
2036 -- or not. So, If they are not defined then that doesnot violoate the logic of the
2037 -- SQL statement.
2038 CURSOR fin_only_one_as_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2039 SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
2040 FROM OKL_ST_GEN_TMPT_LNS gtl
2041 ,okl_strm_type_b stb
2042 WHERE gtl.primary_sty_id = stb.id
2043 AND gtl.primary_yn = 'Y'
2044 AND stb.stream_type_purpose IN
2045 (
2046 'ACTUAL_PROPERTY_TAX'
2047 ,'REBOOK_BILLING_ADJUSTMENT'
2048 ,'CURE'
2049 ,'INSURANCE_ACCRUAL_ADJUSTMENT'
2050 ,'INSURANCE_ADJUSTMENT'
2051 ,'INSURANCE_EXPENSE_ACCRUAL'
2052 ,'INSURANCE_INCOME_ACCRUAL'
2053 ,'INSURANCE_PAYABLE'
2054 ,'INSURANCE_RECEIVABLE'
2055 ,'INSURANCE_REFUND'
2056 ,'LATE_FEE'
2057 ,'LATE_INTEREST'
2058 ,'PREFUNDING_INTEREST_PAYMENT'
2059 ,'REPAIR_CHARGE'
2060 ,'USAGE_PAYMENT'
2061 ,'SERVICE_FEE_AMORT_SCHEDULE'
2062 ,'SERVICE_FEE_AUDIT_LETTER'
2063 ,'SERVICE_FEE_VAT_SCHEDULE'
2064 ,'SERVICE_FEE_VAR_RATE_STMNT'
2065 ,'SERVICE_FEE_INVOICE_REPRINT'
2066 ,'SERVICE_FEE_INVOICE_DEMAND'
2067 ,'SERVICE_FEE_REST_REQUEST'
2068 ,'SERVICE_FEE_TERM_REQUEST'
2069 ,'SERVICE_FEE_EXCHG_REQUEST'
2070 ,'SERVICE_FEE_TRANS_REQUEST'
2071 ,'SERVICE_FEE_PMT_CHANGE'
2072 ,'SERVICE_FEE_INTEREST_CONV'
2073 ,'SERVICE_FEE_GENERAL'
2074 ,'SERVICE_FEE_DOCUMENT_REQ'
2075 ,'AMBSPR'
2076 ,'AMAFEE'
2077 ,'AMBCOC'
2078 ,'AMYFEE'
2079 ,'AMCQDR'
2080 ,'AMPRTX'
2081 ,'AMEFEE'
2082 ,'AMFFEE'
2083 ,'AMGFEE'
2084 ,'AMIFEE'
2085 ,'AMCMIS'
2086 ,'AMMFEE'
2087 ,'AMPFEE'
2088 ,'AMCTOC'
2089 ,'AMBPOC'
2090 ,'AMCQFE'
2091 ,'AMCRFE'
2092 ,'AMCRIN'
2093 ,'AMYSAM'
2094 ,'AMCTPE'
2095 ,'AMCTUR'
2096 --bug 4176696 fixed by smahapat
2097 --,'BILL_ADJST'
2098 ,'VARIABLE_INTEREST'
2099 ,'BOOK_DEPRECIATION'
2100 ,'FEDERAL_DEPRECIATION'
2101 ,'INVESTOR_PRETAX_INCOME'
2102 ,'INVESTOR_RENTAL_ACCRUAL'
2103 ,'RESIDUAL_VALUE'
2104 ,'STATE_DEPRECIATION'
2105 ,'VARIABLE_INTEREST_SCHEDULE'
2106 -- Modified by RGOOTY
2107 -- Bug 4050701: Start
2108 ,'SERVICE_EXPENSE'
2109 -- Bug 4050701: End
2110 -- Bug 4062730: Start
2111 ,'RENT'
2112 -- Bug 4062730: End
2113 -- Bug 4110239: Start
2114 ,'GENERAL_LOSS_PROVISION'
2115 -- Bug 4110239: End
2116 ,'VENDOR_RESIDUAL_SHARING'
2117 --Bug 4616460 added new stream type purpose
2118 ,'ASSET_SALE_RECEIVABLE'
2119 --Bug 4616460 end
2120 -- Bug 5730462: Add a new purpose for EB Tax R12 IA
2121 ,'UPFRONT_TAX_FINANCED'
2122 ,'UPFRONT_TAX_CAPITALIZED'
2123 -- Bug 5730462 End
2124 , 'UPFRONT_TAX_BILLED' -- bug6619311
2125 )
2126 AND GTL.GTT_ID = p_gtt_id
2127 group by stb.stream_type_purpose
2128 having count(stb.stream_type_purpose) > 1;
2129
2130 -- 3. All of the following primary purposes must be defined
2131 CURSOR fin_mandatory_pri_all_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2132 SELECT LOOKUP_CODE PURPOSE
2133 FROM FND_LOOKUPS
2134 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2135 AND LOOKUP_CODE IN
2136 (
2137 'CURE'
2138 ,'FUNDING'
2139 ,'LATE_FEE'
2140 ,'LATE_INTEREST'
2141 ,'PREFUNDING_INTEREST_PAYMENT'
2142 ,'RENT'
2143 ,'REPAIR_CHARGE'
2144 ,'SERVICE_FEE_AMORT_SCHEDULE'
2145 ,'SERVICE_FEE_AUDIT_LETTER'
2146 ,'SERVICE_FEE_VAT_SCHEDULE'
2147 ,'SERVICE_FEE_VAR_RATE_STMNT'
2148 ,'SERVICE_FEE_INVOICE_REPRINT'
2149 ,'SERVICE_FEE_INVOICE_DEMAND'
2150 ,'SERVICE_FEE_REST_REQUEST'
2151 ,'SERVICE_FEE_TERM_REQUEST'
2152 ,'SERVICE_FEE_EXCHG_REQUEST'
2153 ,'SERVICE_FEE_TRANS_REQUEST'
2154 ,'SERVICE_FEE_PMT_CHANGE'
2155 ,'SERVICE_FEE_INTEREST_CONV'
2156 ,'SERVICE_FEE_GENERAL'
2157 ,'SERVICE_FEE_DOCUMENT_REQ'
2158 ,'QUOTE_PER_DIEM'
2159 ,'REBOOK_BILLING_ADJUSTMENT' -- VR Upgrade.
2160 )
2161 MINUS
2162 SELECT distinct stb.stream_type_purpose purpose
2163 FROM OKL_ST_GEN_TMPT_LNS gtl
2164 , okl_strm_type_b stb
2165 WHERE gtl.primary_sty_id = stb.id
2166 AND gtl.primary_yn = 'Y'
2167 AND stb.stream_type_purpose IN
2168 (
2169 'CURE'
2170 ,'FUNDING'
2171 ,'LATE_FEE'
2172 ,'LATE_INTEREST'
2173 ,'PREFUNDING_INTEREST_PAYMENT'
2174 ,'RENT'
2175 ,'REPAIR_CHARGE'
2176 ,'SERVICE_FEE_AMORT_SCHEDULE'
2177 ,'SERVICE_FEE_AUDIT_LETTER'
2178 ,'SERVICE_FEE_VAT_SCHEDULE'
2179 ,'SERVICE_FEE_VAR_RATE_STMNT'
2180 ,'SERVICE_FEE_INVOICE_REPRINT'
2181 ,'SERVICE_FEE_INVOICE_DEMAND'
2182 ,'SERVICE_FEE_REST_REQUEST'
2183 ,'SERVICE_FEE_TERM_REQUEST'
2184 ,'SERVICE_FEE_EXCHG_REQUEST'
2185 ,'SERVICE_FEE_TRANS_REQUEST'
2186 ,'SERVICE_FEE_PMT_CHANGE'
2187 ,'SERVICE_FEE_INTEREST_CONV'
2188 ,'SERVICE_FEE_GENERAL'
2189 ,'SERVICE_FEE_DOCUMENT_REQ'
2190 ,'QUOTE_PER_DIEM'
2191 ,'REBOOK_BILLING_ADJUSTMENT' -- VR Upgrade.
2192 )
2193 and GTL.GTT_ID = p_gtt_id;
2194
2195 CURSOR fin_mandatory_pri_ln_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2196 SELECT LOOKUP_CODE PURPOSE
2197 FROM FND_LOOKUPS
2198 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2199 AND LOOKUP_CODE IN
2200 (
2201 'VARIABLE_INTEREST'
2202 )
2203 MINUS
2204 SELECT distinct stb.stream_type_purpose purpose
2205 FROM OKL_ST_GEN_TMPT_LNS gtl
2206 , okl_strm_type_b stb
2207 WHERE gtl.primary_sty_id = stb.id
2208 AND gtl.primary_yn = 'Y'
2209 AND stb.stream_type_purpose IN
2210 (
2211 'VARIABLE_INTEREST'
2212 )
2213 and GTL.GTT_ID = p_gtt_id;
2214
2215 CURSOR fin_mandatory_pri_ls_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2216 SELECT LOOKUP_CODE PURPOSE
2217 FROM FND_LOOKUPS
2218 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2219 AND LOOKUP_CODE IN
2220 (
2221 'RESIDUAL_VALUE'
2222 )
2223 MINUS
2224 SELECT distinct stb.stream_type_purpose purpose
2225 FROM OKL_ST_GEN_TMPT_LNS gtl
2226 , okl_strm_type_b stb
2227 WHERE gtl.primary_sty_id = stb.id
2228 AND gtl.primary_yn = 'Y'
2229 AND stb.stream_type_purpose IN
2230 (
2231 'RESIDUAL_VALUE'
2232 )
2233 and GTL.GTT_ID = p_gtt_id;
2234
2235 --Bug 5139013 dpsingh start
2236 CURSOR fin_mandatory_pri_ic_float_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2237 SELECT LOOKUP_CODE PURPOSE
2238 FROM FND_LOOKUPS
2239 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2240 AND LOOKUP_CODE ='VARIABLE_INTEREST_SCHEDULE'
2241 MINUS
2242 SELECT distinct stb.stream_type_purpose purpose
2243 FROM OKL_ST_GEN_TMPT_LNS gtl
2244 , okl_strm_type_b stb
2245 WHERE gtl.primary_sty_id = stb.id
2246 AND gtl.primary_yn = 'Y'
2247 AND stb.stream_type_purpose ='VARIABLE_INTEREST_SCHEDULE'
2248 and GTL.GTT_ID = p_gtt_id;
2249 --Bug 5139013 dpsingh end
2250
2251 -- Modified by RGOOTY
2252 -- Bug 4111081: Start
2253 CURSOR fin_mandatory_pri_op_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2254 SELECT LOOKUP_CODE PURPOSE
2255 FROM FND_LOOKUPS
2256 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2257 AND LOOKUP_CODE IN
2258 (
2259 'INVESTOR_RENTAL_ACCRUAL'
2260 )
2261 MINUS
2262 SELECT distinct stb.stream_type_purpose purpose
2263 FROM OKL_ST_GEN_TMPT_LNS gtl
2264 , okl_strm_type_b stb
2265 WHERE gtl.primary_sty_id = stb.id
2266 AND gtl.primary_yn = 'Y'
2267 AND stb.stream_type_purpose IN
2268 (
2269 'INVESTOR_RENTAL_ACCRUAL'
2270 )
2271 and GTL.GTT_ID = p_gtt_id;
2272
2273 CURSOR fin_mandatory_pri_df_n_st_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2274 SELECT LOOKUP_CODE PURPOSE
2275 FROM FND_LOOKUPS
2276 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2277 AND LOOKUP_CODE IN
2278 (
2279 'INVESTOR_PRETAX_INCOME'
2280 )
2281 MINUS
2282 SELECT distinct stb.stream_type_purpose purpose
2283 FROM OKL_ST_GEN_TMPT_LNS gtl
2284 , okl_strm_type_b stb
2285 WHERE gtl.primary_sty_id = stb.id
2286 AND gtl.primary_yn = 'Y'
2287 AND stb.stream_type_purpose IN
2288 (
2289 'INVESTOR_PRETAX_INCOME'
2290 )
2291 and GTL.GTT_ID = p_gtt_id;
2292 -- Bug 4111081: End
2293
2294 -- Modified by RGOOTY
2295 -- Bug 4129154: Start
2296
2297 -- 4. Mandatory Dependent Streams
2298 CURSOR man_dep_all_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2299 SELECT LOOKUP_CODE PURPOSE
2300 FROM FND_LOOKUPS
2301 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2302 AND LOOKUP_CODE IN
2303 (
2304 'ADVANCE_RENT'
2305 )
2306 MINUS
2307 SELECT distinct stb.stream_type_purpose purpose
2308 FROM OKL_ST_GEN_TMPT_LNS gtl
2309 , okl_strm_type_b stb
2310 , okl_strm_type_b sstb
2311 WHERE gtl.dependent_sty_id = stb.id
2312 AND gtl.primary_yn = 'N'
2313 AND stb.stream_type_purpose IN
2314 (
2315 'ADVANCE_RENT'
2316 )
2317 and GTL.GTT_ID = p_gtt_id
2318 and sstb.id = gtl.primary_sty_id
2319 and sstb.stream_Type_purpose IN
2320 (
2321 'RENT'
2322 )
2323 AND EXISTS
2324 (
2325 SELECT 1
2326 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2327 WHERE primary_yn = 'Y'
2328 AND gtlpri.gtt_id = p_gtt_id
2329 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2330 );
2331
2332 CURSOR man_dep_ln_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2333 SELECT LOOKUP_CODE PURPOSE
2334 FROM FND_LOOKUPS
2335 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2336 AND LOOKUP_CODE IN
2337 (
2338 'INTEREST_INCOME'
2339 ,'INTEREST_PAYMENT'
2340 ,'LOAN_PAYMENT'
2341 ,'PRINCIPAL_BALANCE'
2342 ,'PRINCIPAL_CATCHUP'
2343 ,'PRINCIPAL_PAYMENT'
2344 ,'UNSCHEDULED_PRINCIPAL_PAYMENT'
2345 )
2346 MINUS
2347 SELECT distinct stb.stream_type_purpose purpose
2348 FROM OKL_ST_GEN_TMPT_LNS gtl
2349 , okl_strm_type_b stb
2350 , okl_strm_type_b sstb
2351 WHERE gtl.dependent_sty_id = stb.id
2352 AND gtl.primary_yn = 'N'
2353 AND stb.stream_type_purpose IN
2354 (
2355 'INTEREST_INCOME'
2356 ,'INTEREST_PAYMENT'
2357 ,'LOAN_PAYMENT'
2358 ,'PRINCIPAL_BALANCE'
2359 ,'PRINCIPAL_CATCHUP'
2360 ,'PRINCIPAL_PAYMENT'
2361 ,'UNSCHEDULED_PRINCIPAL_PAYMENT'
2362 )
2363 and GTL.GTT_ID = p_gtt_id
2364 and sstb.id = gtl.primary_sty_id
2365 and sstb.stream_Type_purpose IN
2366 (
2367 'RENT'
2368 )
2369 AND EXISTS
2370 (
2371 SELECT 1
2372 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2373 WHERE primary_yn = 'Y'
2374 AND gtlpri.gtt_id = p_gtt_id
2375 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2376 );
2377
2378 CURSOR man_dep_ln_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2379 SELECT LOOKUP_CODE PURPOSE
2380 FROM FND_LOOKUPS
2381 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2382 AND LOOKUP_CODE IN
2383 (
2384 'DAILY_INTEREST_PRINCIPAL'
2385 ,'DAILY_INTEREST_INTEREST'
2386 ,'UNSCHEDULED_LOAN_PAYMENT'
2387 ,'VARIABLE_LOAN_PAYMENT'
2388 ,'EXCESS_PRINCIPAL_PAID'
2389 ,'EXCESS_INTEREST_PAID'
2390 ,'EXCESS_LOAN_PAYMENT_PAID'
2391 ,'ACTUAL_INCOME_ACCRUAL'
2392 )
2393 MINUS
2394 SELECT distinct stb.stream_type_purpose purpose
2395 FROM OKL_ST_GEN_TMPT_LNS gtl
2396 , okl_strm_type_b stb
2397 , okl_strm_type_b ptb
2398 WHERE gtl.dependent_sty_id = stb.id
2399 AND gtl.primary_yn = 'N'
2400 AND stb.stream_type_purpose IN
2401 (
2402 'DAILY_INTEREST_PRINCIPAL'
2403 ,'DAILY_INTEREST_INTEREST'
2404 ,'UNSCHEDULED_LOAN_PAYMENT'
2405 ,'VARIABLE_LOAN_PAYMENT'
2406 ,'EXCESS_PRINCIPAL_PAID'
2407 ,'EXCESS_INTEREST_PAID'
2408 ,'EXCESS_LOAN_PAYMENT_PAID'
2409 ,'ACTUAL_INCOME_ACCRUAL'
2410 )
2411 and GTL.GTT_ID = p_gtt_id
2412 and ptb.id = gtl.primary_sty_id
2413 and ptb.stream_Type_purpose IN
2414 (
2415 'RENT'
2416 )
2417 AND EXISTS
2418 (
2419 SELECT 1
2420 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2421 WHERE primary_yn = 'Y'
2422 AND gtlpri.gtt_id = p_gtt_id
2423 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2424 );
2425
2426 CURSOR man_dep_dfstop_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2427 SELECT LOOKUP_CODE PURPOSE
2428 FROM FND_LOOKUPS
2429 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2430 AND LOOKUP_CODE IN
2431 (
2432 'FLOAT_FACTOR_ADJUSTMENT'
2433 )
2434 MINUS
2435 SELECT distinct stb.stream_type_purpose purpose
2436 FROM OKL_ST_GEN_TMPT_LNS gtl
2437 , okl_strm_type_b stb
2438 , okl_strm_type_b ptb
2439 WHERE gtl.dependent_sty_id = stb.id
2440 AND gtl.primary_yn = 'N'
2441 AND stb.stream_type_purpose IN
2442 (
2443 'FLOAT_FACTOR_ADJUSTMENT'
2444 )
2445 and GTL.GTT_ID = p_gtt_id
2446 and ptb.id = gtl.primary_sty_id
2447 and ptb.stream_Type_purpose IN
2448 (
2449 'RENT'
2450 )
2451 AND EXISTS
2452 (
2453 SELECT 1
2454 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2455 WHERE primary_yn = 'Y'
2456 AND gtlpri.gtt_id = p_gtt_id
2457 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2458 );
2459
2460 CURSOR man_dep_ln_icc_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2461 SELECT LOOKUP_CODE PURPOSE
2462 FROM FND_LOOKUPS
2463 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2464 AND LOOKUP_CODE IN
2465 (
2466 'INTEREST_CATCHUP'
2467 )
2468 MINUS
2469 SELECT distinct stb.stream_type_purpose purpose
2470 FROM OKL_ST_GEN_TMPT_LNS gtl
2471 , okl_strm_type_b stb
2472 , okl_strm_type_b ptb
2473 WHERE gtl.dependent_sty_id = stb.id
2474 AND gtl.primary_yn = 'N'
2475 AND stb.stream_type_purpose IN
2476 (
2477 'INTEREST_CATCHUP'
2478 )
2479 and GTL.GTT_ID = p_gtt_id
2480 and ptb.id = gtl.primary_sty_id
2481 and ptb.stream_Type_purpose IN
2482 (
2483 'RENT'
2484 )
2485 AND EXISTS
2486 (
2487 SELECT 1
2488 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2489 WHERE primary_yn = 'Y'
2490 AND gtlpri.gtt_id = p_gtt_id
2491 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2492 );
2493
2494 -- rgooty BUG#4290143 start
2495 CURSOR man_dep_ln_var_int_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2496 SELECT LOOKUP_CODE PURPOSE
2497 FROM FND_LOOKUPS
2498 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2499 AND LOOKUP_CODE = 'VARIABLE_INTEREST_INCOME'
2500 MINUS
2501 SELECT distinct stb.stream_type_purpose purpose
2502 FROM OKL_ST_GEN_TMPT_LNS gtl
2503 , okl_strm_type_b stb
2504 , okl_strm_type_b sstb
2505 WHERE gtl.dependent_sty_id = stb.id
2506 AND gtl.primary_yn = 'N'
2507 AND stb.stream_type_purpose = 'VARIABLE_INTEREST_INCOME'
2508 and GTL.GTT_ID = p_gtt_id
2509 and sstb.id = gtl.primary_sty_id
2510 and sstb.stream_Type_purpose IN
2511 (
2512 'VARIABLE_INTEREST'
2513 )
2514 AND EXISTS
2515 (
2516 SELECT 1
2517 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2518 WHERE primary_yn = 'Y'
2519 AND gtlpri.gtt_id = p_gtt_id
2520 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2521 );
2522 -- rgooty BUG#4290143 end
2523
2524 CURSOR man_dep_df_st_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2525 SELECT LOOKUP_CODE PURPOSE
2526 FROM FND_LOOKUPS
2527 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2528 AND LOOKUP_CODE IN
2529 (
2530 'LEASE_INCOME'
2531 )
2532 MINUS
2533 SELECT distinct stb.stream_type_purpose purpose
2534 FROM OKL_ST_GEN_TMPT_LNS gtl
2535 , okl_strm_type_b stb
2536 , okl_strm_type_b sstb
2537 WHERE gtl.dependent_sty_id = stb.id
2538 AND gtl.primary_yn = 'N'
2539 AND stb.stream_type_purpose IN
2540 (
2541 'LEASE_INCOME'
2542 )
2543 and GTL.GTT_ID = p_gtt_id
2544 and sstb.id = gtl.primary_sty_id
2545 and sstb.stream_Type_purpose IN
2546 (
2547 'RENT'
2548 )
2549 AND EXISTS
2550 (
2551 SELECT 1
2552 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2553 WHERE primary_yn = 'Y'
2554 AND gtlpri.gtt_id = p_gtt_id
2555 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2556 );
2557
2558 CURSOR man_dep_ls_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2559 SELECT LOOKUP_CODE PURPOSE
2560 FROM FND_LOOKUPS
2561 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2562 AND LOOKUP_CODE IN
2563 (
2564 'PASS_THROUGH_EVERGREEN_RENT'
2565 ,'PV_RENT'
2566 ,'RENEWAL_RENT'
2567 )
2568 MINUS
2569 SELECT distinct stb.stream_type_purpose purpose
2570 FROM OKL_ST_GEN_TMPT_LNS gtl
2571 , okl_strm_type_b stb
2572 , okl_strm_type_b sstb
2573 WHERE gtl.dependent_sty_id = stb.id
2574 AND gtl.primary_yn = 'N'
2575 AND stb.stream_type_purpose IN
2576 (
2577 'PASS_THROUGH_EVERGREEN_RENT'
2578 ,'PV_RENT'
2579 ,'RENEWAL_RENT'
2580 )
2581 and GTL.GTT_ID = p_gtt_id
2582 and sstb.id = gtl.primary_sty_id
2583 and sstb.stream_Type_purpose IN
2584 (
2585 'RENT'
2586 )
2587 AND EXISTS
2588 (
2589 SELECT 1
2590 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2591 WHERE primary_yn = 'Y'
2592 AND gtlpri.gtt_id = p_gtt_id
2593 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2594 );
2595
2596 CURSOR man_dep_ls_rv_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2597 SELECT LOOKUP_CODE PURPOSE
2598 FROM FND_LOOKUPS
2599 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2600 AND LOOKUP_CODE IN
2601 (
2602 'PV_RV'
2603 ,'PV_RV_GUARANTEED'
2604 ,'PV_RV_INSURED'
2605 ,'PV_RV_UNGUARANTEED'
2606 ,'PV_RV_UNINSURED'
2607 ,'RESIDUAL_GUARANTEED'
2608 ,'RV_INSURANCE_PREMIUM'
2609 )
2610 MINUS
2611 SELECT distinct stb.stream_type_purpose purpose
2612 FROM OKL_ST_GEN_TMPT_LNS gtl
2613 , okl_strm_type_b stb
2614 , okl_strm_type_b sstb
2615 WHERE gtl.dependent_sty_id = stb.id
2616 AND gtl.primary_yn = 'N'
2617 AND stb.stream_type_purpose IN
2618 (
2619 'PV_RV'
2620 ,'PV_RV_GUARANTEED'
2621 ,'PV_RV_INSURED'
2622 ,'PV_RV_UNGUARANTEED'
2623 ,'PV_RV_UNINSURED'
2624 ,'RESIDUAL_GUARANTEED'
2625 ,'RV_INSURANCE_PREMIUM'
2626 )
2627 and GTL.GTT_ID = p_gtt_id
2628 and sstb.id = gtl.primary_sty_id
2629 and sstb.stream_Type_purpose IN
2630 (
2631 'RESIDUAL_VALUE'
2632 )
2633 AND EXISTS
2634 (
2635 SELECT 1
2636 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2637 WHERE primary_yn = 'Y'
2638 AND gtlpri.gtt_id = p_gtt_id
2639 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2640 );
2641
2642 CURSOR man_dep_op_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2643 SELECT LOOKUP_CODE PURPOSE
2644 FROM FND_LOOKUPS
2645 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2646 AND LOOKUP_CODE IN
2647 (
2648 'RENT_ACCRUAL'
2649 )
2650 MINUS
2651 SELECT distinct stb.stream_type_purpose purpose
2652 FROM OKL_ST_GEN_TMPT_LNS gtl
2653 , okl_strm_type_b stb
2654 , okl_strm_type_b sstb
2655 WHERE gtl.dependent_sty_id = stb.id
2656 AND gtl.primary_yn = 'N'
2657 AND stb.stream_type_purpose IN
2658 (
2659 'RENT_ACCRUAL'
2660 )
2661 and GTL.GTT_ID = p_gtt_id
2662 and sstb.id = gtl.primary_sty_id
2663 and sstb.stream_Type_purpose IN
2664 (
2665 'RENT'
2666 )
2667 AND EXISTS
2668 (
2669 SELECT 1
2670 FROM OKL_ST_GEN_TMPT_LNS gtlpri
2671 WHERE primary_yn = 'Y'
2672 AND gtlpri.gtt_id = p_gtt_id
2673 AND gtlpri.primary_sty_id = gtl.primary_sty_id
2674 );
2675 -- Bug 4129154: End
2676
2677 -- Rule 6. Certain Book classifications can have certain Purposes only.
2678 CURSOR purposes_for_df_and_st_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2679 (
2680 -- Retrieve the List of Primary Stream Purposes in the Template
2681 SELECT distinct stb.stream_type_purpose purpose
2682 FROM OKL_ST_GEN_TMPT_LNS gtl
2683 , okl_strm_type_b stb
2684 WHERE gtl.primary_sty_id = stb.id
2685 AND gtl.primary_yn = 'Y'
2686 AND GTL.GTT_ID = p_gtt_id
2687 UNION
2688 -- Retrieve the List of Dependent Stream Purposes in the Template
2689 SELECT distinct stb.stream_type_purpose purpose
2690 FROM OKL_ST_GEN_TMPT_LNS gtl
2691 , okl_strm_type_b stb
2692 WHERE gtl.dependent_sty_id = stb.id
2693 AND gtl.primary_yn = 'N'
2694 and GTL.GTT_ID = p_gtt_id
2695 )
2696 MINUS
2697 SELECT LOOKUP_CODE
2698 FROM FND_LOOKUPS
2699 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2700 AND LOOKUP_CODE in
2701 (
2702 'ACCOUNTING'
2703 ,'ACCRUED_FEE_EXPENSE'
2704 ,'ACCRUED_FEE_INCOME'
2705 ,'ACTUAL_PROPERTY_TAX'
2706 ,'ADJUSTED_PROPERTY_TAX'
2707 ,'ADVANCE_RENT'
2708 ,'AMORTIZED_FEE_EXPENSE'
2709 ,'AMORTIZE_FEE_INCOME'
2710 ,'REBOOK_BILLING_ADJUSTMENT'
2711 ,'CURE'
2712 ,'ESTIMATED_PROPERTY_TAX'
2713 ,'EXPENSE'
2714 ,'FEE_PAYMENT'
2715 ,'FUNDING'
2716 ,'INSURANCE_ACCRUAL_ADJUSTMENT'
2717 ,'INSURANCE_ADJUSTMENT'
2718 ,'INSURANCE_EXPENSE_ACCRUAL'
2719 ,'INSURANCE_INCOME_ACCRUAL'
2720 ,'INSURANCE_PAYABLE'
2721 ,'INSURANCE_RECEIVABLE'
2722 ,'INSURANCE_REFUND'
2723 ,'LATE_FEE'
2724 ,'LATE_INTEREST'
2725 ,'PASS_THRU_EXP_ACCRUAL'
2726 ,'PASS_THROUGH_FEE'
2727 ,'PASS_THRU_REV_ACCRUAL'
2728 ,'PASS_THROUGH_SERVICE'
2729 ,'PASS_THRU_SVC_EXP_ACCRUAL'
2730 ,'PASS_THRU_SVC_REV_ACCRUAL'
2731 ,'PREFUNDING_INTEREST_PAYMENT'
2732 -- Bug 4110239: Start
2733 --,'PROVISION'
2734 ,'SPECIFIC_LOSS_PROVISION'
2735 ,'GENERAL_LOSS_PROVISION'
2736 -- Bug 4110239: End
2737 ,'RENT'
2738 ,'GENERAL'
2739 ,'REPAIR_CHARGE'
2740 ,'SECURITY_DEPOSIT'
2741 ,'SERVICE_EXPENSE'
2742 ,'SERVICE_INCOME'
2743 ,'SERVICE_PAYMENT'
2744 ,'SERVICE_RENEWAL'
2745 ,'SUBSIDY'
2746 ,'SUBSIDY_INCOME'
2747 ,'USAGE_PAYMENT'
2748 ,'SERVICE_FEE_AMORT_SCHEDULE'
2749 ,'SERVICE_FEE_AUDIT_LETTER'
2750 ,'SERVICE_FEE_VAT_SCHEDULE'
2751 ,'SERVICE_FEE_VAR_RATE_STMNT'
2752 ,'SERVICE_FEE_INVOICE_REPRINT'
2753 ,'SERVICE_FEE_INVOICE_DEMAND'
2754 ,'SERVICE_FEE_REST_REQUEST'
2755 ,'SERVICE_FEE_TERM_REQUEST'
2756 ,'SERVICE_FEE_EXCHG_REQUEST'
2757 ,'SERVICE_FEE_TRANS_REQUEST'
2758 ,'SERVICE_FEE_PMT_CHANGE'
2759 ,'SERVICE_FEE_INTEREST_CONV'
2760 ,'SERVICE_FEE_GENERAL'
2761 ,'SERVICE_FEE_DOCUMENT_REQ'
2762 ,'AMBSPR'
2763 ,'AMAFEE'
2764 ,'AMBCOC'
2765 ,'AMYFEE'
2766 ,'AMCQDR'
2767 ,'AMPRTX'
2768 ,'AMEFEE'
2769 ,'AMFFEE'
2770 ,'AMGFEE'
2771 ,'AMIFEE'
2772 ,'AMCMIS'
2773 ,'AMMFEE'
2774 ,'AMPFEE'
2775 ,'AMCTOC'
2776 ,'AMBPOC'
2777 ,'AMCQFE'
2778 ,'AMCRFE'
2779 ,'AMCRIN'
2780 ,'AMYSAM'
2781 ,'AMCTPE'
2782 ,'AMCTUR'
2783 --bug 4176696 fixed by smahapat
2784 --,'BILL_ADJST'
2785 -- Missed out in the first
2786 ,'FINANCED_FEE_PAYMENT'
2787 ,'PREFUNDING'
2788 -- Added as per Satyas Mail
2789 ,'INTEREST_INCOME'
2790 ,'INTEREST_PAYMENT'
2791 ,'LOAN_PAYMENT'
2792 ,'PRINCIPAL_BALANCE'
2793 ,'PRINCIPAL_PAYMENT'
2794 -- Purposes specific to LS Type
2795 ,'BOOK_DEPRECIATION'
2796 ,'FEDERAL_DEPRECIATION'
2797 ,'FEE_RENEWAL'
2798 ,'INVESTOR_PRETAX_INCOME'
2799 ,'INVESTOR_RENTAL_ACCRUAL'
2800 ,'PASS_THROUGH_EVERGREEN_FEE'
2801 ,'PASS_THROUGH_EVERGREEN_RENT'
2802 ,'PASS_THROUGH_EVERGREEN_SERVICE'
2803 ,'PV_RENT'
2804 ,'PV_RENT_SECURITIZED'
2805 ,'PV_RV'
2806 ,'PV_RV_GUARANTEED'
2807 ,'PV_RV_INSURED'
2808 ,'PV_RV_SECURITIZED'
2809 ,'PV_RV_UNGUARANTEED'
2810 ,'PV_RV_UNINSURED'
2811 ,'RENEWAL_PROPERTY_TAX'
2812 ,'RENEWAL_RENT'
2813 ,'RESIDUAL_GUARANTEED'
2814 ,'RV_INSURANCE_PREMIUM'
2815 ,'RESIDUAL_VALUE'
2816 ,'STATE_DEPRECIATION'
2817 ,'STIP_LOSS_VALUE'
2818 ,'TERMINATION_VALUE'
2819 -- Purposes specific to DF,ST type
2820 ,'LEASE_INCOME'
2821 ,'PROCESSING_FEE'
2822 ,'PROCESSING_FEE_ACCRUAL'
2823 ,'DOWN_PAYMENT'
2824 ,'INSURANCE_ESTIMATE_PAYMENT'
2825 ,'VENDOR_RESIDUAL_SHARING'
2826 ,'QUOTE_PER_DIEM'
2827 --Bug 4616460 added new stream type purpose
2828 ,'ASSET_SALE_RECEIVABLE'
2829 ,'FLOAT_FACTOR_ADJUSTMENT'
2830 --Bug 4616460 end
2831 ,'CAPITAL_REDUCTION'
2832 --srsreeni 6117982 added
2833 ,'UPFRONT_TAX_FINANCED'
2834 ,'UPFRONT_TAX_CAPITALIZED'
2835 --srsreeni 6117982 ends
2836 , 'UPFRONT_TAX_BILLED' -- bug6619311
2837 );
2838
2839 CURSOR purposes_for_op_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2840 (
2841 -- Retrieve the List of Primary Stream Purposes in the Template
2842 SELECT distinct stb.stream_type_purpose purpose
2843 FROM OKL_ST_GEN_TMPT_LNS gtl
2844 , okl_strm_type_b stb
2845 WHERE gtl.primary_sty_id = stb.id
2846 AND gtl.primary_yn = 'Y'
2847 AND GTL.GTT_ID = p_gtt_id
2848 UNION
2849 -- Retrieve the List of Dependent Stream Purposes in the Template
2850 SELECT distinct stb.stream_type_purpose purpose
2851 FROM OKL_ST_GEN_TMPT_LNS gtl
2852 , okl_strm_type_b stb
2853 WHERE gtl.dependent_sty_id = stb.id
2854 AND gtl.primary_yn = 'N'
2855 and GTL.GTT_ID = p_gtt_id
2856 )
2857 MINUS
2858 SELECT LOOKUP_CODE
2859 FROM FND_LOOKUPS
2860 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2861 AND LOOKUP_CODE in
2862 (
2863 'ACCOUNTING'
2864 ,'ACCRUED_FEE_EXPENSE'
2865 ,'ACCRUED_FEE_INCOME'
2866 ,'ACTUAL_PROPERTY_TAX'
2867 ,'ADJUSTED_PROPERTY_TAX'
2868 ,'ADVANCE_RENT'
2869 ,'AMORTIZED_FEE_EXPENSE'
2870 ,'AMORTIZE_FEE_INCOME'
2871 ,'REBOOK_BILLING_ADJUSTMENT'
2872 ,'CURE'
2873 ,'ESTIMATED_PROPERTY_TAX'
2874 ,'EXPENSE'
2875 ,'FEE_PAYMENT'
2876 ,'FUNDING'
2877 ,'INSURANCE_ACCRUAL_ADJUSTMENT'
2878 ,'INSURANCE_ADJUSTMENT'
2879 ,'INSURANCE_EXPENSE_ACCRUAL'
2880 ,'INSURANCE_INCOME_ACCRUAL'
2881 ,'INSURANCE_PAYABLE'
2882 ,'INSURANCE_RECEIVABLE'
2883 ,'INSURANCE_REFUND'
2884 ,'LATE_FEE'
2885 ,'LATE_INTEREST'
2886 ,'PASS_THRU_EXP_ACCRUAL'
2887 ,'PASS_THROUGH_FEE'
2888 ,'PASS_THRU_REV_ACCRUAL'
2889 ,'PASS_THROUGH_SERVICE'
2890 ,'PASS_THRU_SVC_EXP_ACCRUAL'
2891 ,'PASS_THRU_SVC_REV_ACCRUAL'
2892 ,'PREFUNDING_INTEREST_PAYMENT'
2893 -- Bug 4110239: Start
2894 --,'PROVISION'
2895 ,'SPECIFIC_LOSS_PROVISION'
2896 ,'GENERAL_LOSS_PROVISION'
2897 -- Bug 4110239: End
2898 ,'RENT'
2899 ,'GENERAL'
2900 ,'REPAIR_CHARGE'
2901 ,'SECURITY_DEPOSIT'
2902 ,'SERVICE_EXPENSE'
2903 ,'SERVICE_INCOME'
2904 ,'SERVICE_PAYMENT'
2905 ,'SERVICE_RENEWAL'
2906 ,'SUBSIDY'
2907 ,'SUBSIDY_INCOME'
2908 ,'USAGE_PAYMENT'
2909 ,'SERVICE_FEE_AMORT_SCHEDULE'
2910 ,'SERVICE_FEE_AUDIT_LETTER'
2911 ,'SERVICE_FEE_VAT_SCHEDULE'
2912 ,'SERVICE_FEE_VAR_RATE_STMNT'
2913 ,'SERVICE_FEE_INVOICE_REPRINT'
2914 ,'SERVICE_FEE_INVOICE_DEMAND'
2915 ,'SERVICE_FEE_REST_REQUEST'
2916 ,'SERVICE_FEE_TERM_REQUEST'
2917 ,'SERVICE_FEE_EXCHG_REQUEST'
2918 ,'SERVICE_FEE_TRANS_REQUEST'
2919 ,'SERVICE_FEE_PMT_CHANGE'
2920 ,'SERVICE_FEE_INTEREST_CONV'
2921 ,'SERVICE_FEE_GENERAL'
2922 ,'SERVICE_FEE_DOCUMENT_REQ'
2923 ,'AMBSPR'
2924 ,'AMAFEE'
2925 ,'AMBCOC'
2926 ,'AMYFEE'
2927 ,'AMCQDR'
2928 ,'AMPRTX'
2929 ,'AMEFEE'
2930 ,'AMFFEE'
2931 ,'AMGFEE'
2932 ,'AMIFEE'
2933 ,'AMCMIS'
2934 ,'AMMFEE'
2935 ,'AMPFEE'
2936 ,'AMCTOC'
2937 ,'AMBPOC'
2938 ,'AMCQFE'
2939 ,'AMCRFE'
2940 ,'AMCRIN'
2941 ,'AMYSAM'
2942 ,'AMCTPE'
2943 ,'AMCTUR'
2944 --bug 4176696 fixed by smahapat
2945 --,'BILL_ADJST'
2946 -- Missed out in the first
2947 ,'FINANCED_FEE_PAYMENT'
2948 ,'PREFUNDING'
2949 -- Added as per Satyas Mail
2950 ,'INTEREST_INCOME'
2951 ,'INTEREST_PAYMENT'
2952 ,'LOAN_PAYMENT'
2953 ,'PRINCIPAL_BALANCE'
2954 ,'PRINCIPAL_PAYMENT'
2955 -- Purposes specific to LS Type
2956 ,'BOOK_DEPRECIATION'
2957 ,'FEDERAL_DEPRECIATION'
2958 ,'FEE_RENEWAL'
2959 ,'INVESTOR_PRETAX_INCOME'
2960 ,'INVESTOR_RENTAL_ACCRUAL'
2961 ,'PASS_THROUGH_EVERGREEN_FEE'
2962 ,'PASS_THROUGH_EVERGREEN_RENT'
2963 ,'PASS_THROUGH_EVERGREEN_SERVICE'
2964 ,'PV_RENT'
2965 ,'PV_RENT_SECURITIZED'
2966 ,'PV_RV'
2967 ,'PV_RV_GUARANTEED'
2968 ,'PV_RV_INSURED'
2969 ,'PV_RV_SECURITIZED'
2970 ,'PV_RV_UNGUARANTEED'
2971 ,'PV_RV_UNINSURED'
2972 ,'RENEWAL_PROPERTY_TAX'
2973 ,'RENEWAL_RENT'
2974 ,'RESIDUAL_GUARANTEED'
2975 ,'RV_INSURANCE_PREMIUM'
2976 ,'RESIDUAL_VALUE'
2977 ,'STATE_DEPRECIATION'
2978 ,'STIP_LOSS_VALUE'
2979 ,'TERMINATION_VALUE'
2980 -- Purposes specific to OP Type
2981 ,'RENT_ACCRUAL'
2982 ,'PROCESSING_FEE'
2983 ,'PROCESSING_FEE_ACCRUAL'
2984 ,'DOWN_PAYMENT'
2985 ,'INSURANCE_ESTIMATE_PAYMENT'
2986 ,'VENDOR_RESIDUAL_SHARING'
2987 ,'QUOTE_PER_DIEM'
2988 --Bug 4616460 added new stream type purpose
2989 ,'ASSET_SALE_RECEIVABLE'
2990 --Bug 4616460 end
2991 ,'FLOAT_FACTOR_ADJUSTMENT'
2992 ,'CAPITAL_REDUCTION'
2993 --srsreeni 6117982 added
2994 ,'UPFRONT_TAX_FINANCED'
2995 ,'UPFRONT_TAX_CAPITALIZED'
2996 --srsreeni 6117982 ends
2997 , 'UPFRONT_TAX_BILLED' -- bug 6619311
2998 );
2999
3000 CURSOR purposes_for_ln_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3001 (
3002 -- Retrieve the List of Primary Stream Purposes in the Template
3003 SELECT distinct stb.stream_type_purpose purpose
3004 FROM OKL_ST_GEN_TMPT_LNS gtl
3005 , okl_strm_type_b stb
3006 WHERE gtl.primary_sty_id = stb.id
3007 AND gtl.primary_yn = 'Y'
3008 AND GTL.GTT_ID = p_gtt_id
3009 UNION
3010 -- Retrieve the List of Dependent Stream Purposes in the Template
3011 SELECT distinct stb.stream_type_purpose purpose
3012 FROM OKL_ST_GEN_TMPT_LNS gtl
3013 , okl_strm_type_b stb
3014 WHERE gtl.dependent_sty_id = stb.id
3015 AND gtl.primary_yn = 'N'
3016 and GTL.GTT_ID = p_gtt_id
3017 )
3018 MINUS
3019 SELECT LOOKUP_CODE
3020 FROM FND_LOOKUPS
3021 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
3022 AND LOOKUP_CODE in
3023 (
3024 'ACCOUNTING'
3025 ,'ACCRUED_FEE_EXPENSE'
3026 ,'ACCRUED_FEE_INCOME'
3027 ,'ACTUAL_PROPERTY_TAX'
3028 ,'ADJUSTED_PROPERTY_TAX'
3029 ,'ADVANCE_RENT'
3030 ,'AMORTIZED_FEE_EXPENSE'
3031 ,'AMORTIZE_FEE_INCOME'
3032 ,'REBOOK_BILLING_ADJUSTMENT'
3033 ,'CURE'
3034 ,'ESTIMATED_PROPERTY_TAX'
3035 ,'EXPENSE'
3036 ,'FEE_PAYMENT'
3037 ,'FUNDING'
3038 ,'INSURANCE_ACCRUAL_ADJUSTMENT'
3039 ,'INSURANCE_ADJUSTMENT'
3040 ,'INSURANCE_EXPENSE_ACCRUAL'
3041 ,'INSURANCE_INCOME_ACCRUAL'
3042 ,'INSURANCE_PAYABLE'
3043 ,'INSURANCE_RECEIVABLE'
3044 ,'INSURANCE_REFUND'
3045 ,'LATE_FEE'
3046 ,'LATE_INTEREST'
3047 ,'PASS_THRU_EXP_ACCRUAL'
3048 ,'PASS_THROUGH_FEE'
3049 ,'PASS_THRU_REV_ACCRUAL'
3050 ,'PASS_THROUGH_SERVICE'
3051 ,'PASS_THRU_SVC_EXP_ACCRUAL'
3052 ,'PASS_THRU_SVC_REV_ACCRUAL'
3053 ,'PREFUNDING_INTEREST_PAYMENT'
3054 -- Bug 4110239: Start
3055 --,'PROVISION'
3056 ,'SPECIFIC_LOSS_PROVISION'
3057 ,'GENERAL_LOSS_PROVISION'
3058 -- Bug 4110239: End
3059 ,'RENT'
3060 ,'GENERAL'
3061 ,'REPAIR_CHARGE'
3062 ,'SECURITY_DEPOSIT'
3063 ,'SERVICE_EXPENSE'
3064 ,'SERVICE_INCOME'
3065 ,'SERVICE_PAYMENT'
3066 ,'SERVICE_RENEWAL'
3067 ,'SUBSIDY'
3068 ,'SUBSIDY_INCOME'
3069 ,'USAGE_PAYMENT'
3070 ,'SERVICE_FEE_AMORT_SCHEDULE'
3071 ,'SERVICE_FEE_AUDIT_LETTER'
3072 ,'SERVICE_FEE_VAT_SCHEDULE'
3073 ,'SERVICE_FEE_VAR_RATE_STMNT'
3074 ,'SERVICE_FEE_INVOICE_REPRINT'
3075 ,'SERVICE_FEE_INVOICE_DEMAND'
3076 ,'SERVICE_FEE_REST_REQUEST'
3077 ,'SERVICE_FEE_TERM_REQUEST'
3078 ,'SERVICE_FEE_EXCHG_REQUEST'
3079 ,'SERVICE_FEE_TRANS_REQUEST'
3080 ,'SERVICE_FEE_PMT_CHANGE'
3081 ,'SERVICE_FEE_INTEREST_CONV'
3082 ,'SERVICE_FEE_GENERAL'
3083 ,'SERVICE_FEE_DOCUMENT_REQ'
3084 ,'AMBSPR'
3085 ,'AMAFEE'
3086 ,'AMBCOC'
3087 ,'AMYFEE'
3088 ,'AMCQDR'
3089 ,'AMPRTX'
3090 ,'AMEFEE'
3091 ,'AMFFEE'
3092 ,'AMGFEE'
3093 ,'AMIFEE'
3094 ,'AMCMIS'
3095 ,'AMMFEE'
3096 ,'AMPFEE'
3097 ,'AMCTOC'
3098 ,'AMBPOC'
3099 ,'AMCQFE'
3100 ,'AMCRFE'
3101 ,'AMCRIN'
3102 ,'AMYSAM'
3103 ,'AMCTPE'
3104 ,'AMCTUR'
3105 --bug 4176696 fixed by smahapat
3106 --,'BILL_ADJST'
3107 -- Missed out in the first
3108 ,'FINANCED_FEE_PAYMENT'
3109 ,'PREFUNDING'
3110 -- Specific Purposes for Loan And Loan Revolving Deal Type
3111 ,'INTEREST_INCOME'
3112 ,'INTEREST_PAYMENT'
3113 ,'LOAN_PAYMENT'
3114 ,'PRINCIPAL_BALANCE'
3115 ,'PRINCIPAL_CATCHUP'
3116 ,'PRINCIPAL_PAYMENT'
3117 ,'UNSCHEDULED_PRINCIPAL_PAYMENT'
3118 ,'VARIABLE_INCOME_NONACCRUAL'
3119 ,'VARIABLE_INTEREST'
3120 ,'VARIABLE_INTEREST_INCOME'
3121 ,'VARIABLE_INTEREST_SCHEDULE'
3122 -- Bug 4137045: Start
3123 ,'INVESTOR_INTEREST_INCOME'
3124 ,'INVESTOR_VARIABLE_INTEREST'
3125 -- Bug 4137045: End
3126 ,'PROCESSING_FEE'
3127 ,'PROCESSING_FEE_ACCRUAL'
3128 ,'DOWN_PAYMENT'
3129 ,'INSURANCE_ESTIMATE_PAYMENT'
3130 ,'DAILY_INTEREST_PRINCIPAL'
3131 ,'DAILY_INTEREST_INTEREST'
3132 ,'INTEREST_CATCHUP'
3133 ,'UNSCHEDULED_LOAN_PAYMENT'
3134 ,'EXCESS_PRINCIPAL_PAID'
3135 ,'EXCESS_INTEREST_PAID'
3136 ,'EXCESS_LOAN_PAYMENT_PAID'
3137 ,'QUOTE_PER_DIEM'
3138 --Bug 4616460 added new stream type purpose
3139 ,'ASSET_SALE_RECEIVABLE'
3140 --Bug 4616460 end
3141 --Bug 4664317 adds ACTUAL_INCOME_ACCRUAL
3142 ,'ACTUAL_INCOME_ACCRUAL'
3143 --Bug 4677496 adds ACTUAL_INCOME_ACCRUAL
3144 ,'VARIABLE_LOAN_PAYMENT'
3145 ,'CAPITAL_REDUCTION'
3146 --srsreeni 6117982 added
3147 ,'UPFRONT_TAX_FINANCED'
3148 ,'UPFRONT_TAX_CAPITALIZED'
3149 --srsreeni 6117982 ends
3150 , 'UPFRONT_TAX_BILLED' -- bug 6619311
3151 );
3152 -- Modified by RGOOTY
3153 -- Bug 4129154: Start
3154 CURSOR fetch_sgt_info( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type)
3155 IS
3156 SELECT gts.revenue_recog_meth_code revenue_recog_meth_code,
3157 gts.interest_calc_meth_code interest_calc_meth_code
3158 FROM OKL_ST_GEN_TMPT_SETS gts,
3159 OKL_ST_GEN_TEMPLATES gtt
3160 WHERE gtt.gts_id = gts.id
3161 AND gtt.id = p_gtt_id;
3162 l_rrm OKL_ST_GEN_TMPT_SETS.revenue_recog_meth_code%TYPE;
3163 l_icm OKL_ST_GEN_TMPT_SETS.interest_calc_meth_code%TYPE;
3164
3165 l_purpose_list VARCHAR2(10000);
3166 l_purpose_list_rent VARCHAR2(10000); -- For Rent Dependents
3167 l_purpose_list_rv VARCHAR2(10000); -- For Residual Value Dependents
3168 l_purpose_list_vi VARCHAR2(10000); -- For Variable Interest Dependents
3169 i NUMBER := 0;
3170 -- Bug 4129154: End
3171 BEGIN
3172 -- Perform the Initializations
3173 l_return_status := OKL_API.G_RET_STS_SUCCESS;
3174 -- Get the Interest Calculation Method and Revenue Recognition Method from the SGT
3175 FOR t_rec IN fetch_sgt_info( p_gtt_id => p_gtt_id )
3176 LOOP
3177 l_rrm := t_rec.revenue_recog_meth_code;
3178 l_icm := t_rec.interest_calc_meth_code;
3179 END LOOP;
3180 -- 1. If user selects one of the following Insurance purposes then all the
3181 -- insurance stream purposes must be defined.
3182 l_ins_strms_count := 0;
3183 FOR ins_strm_types_count_rec IN okl_ins_purposes_csr(l_gtt_id)
3184 LOOP
3185 l_ins_strms_count := ins_strm_types_count_rec.ins_strms_count;
3186 END LOOP;
3187 IF( l_ins_strms_count IS NOT NULL AND l_ins_strms_count <> 0 AND l_ins_strms_count <> 7 ) -- Bug 4096853
3188 THEN
3189 -- Violated first condition
3190 put_messages_in_table( G_OKL_ST_ALL_INS_PURPOSES
3191 ,p_during_upd_flag
3192 ,l_message
3193 );
3194 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3195 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3196 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3197 l_msgs_count := l_msgs_count + 1;
3198 END IF;
3199 -- 2. Only one stream type of the following primary purposes should be defined.
3200 l_purpose_list := '';
3201 FOR fin_only_one_as_primary_rec IN fin_only_one_as_primary_csr(l_gtt_id)
3202 LOOP
3203 IF l_icm = 'FIXED_UPGRADE' AND
3204 fin_only_one_as_primary_rec.purpose = 'VENDOR_RESIDUAL_SHARING'
3205 THEN
3206 -- VR Upgrade. Bug 4756154
3207 -- OKL.H only introduced the VENDOR_RESIDUAL_SHARING, OKL.G doesnot have this purpose at all.
3208 -- So, for SGTs with ICM = "Fixed Upgrade", we are removing the validation
3209 -- which checks the mandatory presence of the VENDOR_RESIDUAL_SHARING purpose streams.
3210 NULL;
3211 ELSE
3212 l_purpose_list := l_purpose_list ||
3213 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3214 ( G_OKL_FIN_PRIMARY_PURPOSES, fin_only_one_as_primary_rec.purpose )
3215 || ',';
3216 END IF;
3217 END LOOP;
3218 l_purpose_list := rtrim( l_purpose_list, ',' );
3219 IF( length(l_purpose_list) > 0 )
3220 THEN
3221 put_messages_in_table( G_OKL_ST_STRM_ONE_PRI_PURPOSE
3222 ,p_during_upd_flag
3223 ,l_message
3224 ,p_token1 => G_PURPOSE_TOKEN
3225 ,p_value1 => l_purpose_list
3226 );
3227 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3228 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3229 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3230 l_msgs_count := l_msgs_count + 1;
3231 END IF;
3232
3233 -- 3. All of the following primary purposes must be defined
3234 l_purpose_list := '';
3235 FOR fin_mandatory_pri_all_rec IN fin_mandatory_pri_all_csr(l_gtt_id)
3236 LOOP
3237 -- VR Upgrade. Bug 4756154
3238 -- In OKL.G, Quote Per Diem is not mandatory and REBOOK_BILLING_ADJUSTMENT is mandatory.
3239 -- In OKL.H, Quote Per Diem is mandatory and REBOOK_BILLING_ADJUSTMENT is not mandatory.
3240 IF ( l_icm = 'FIXED_UPGRADE' AND
3241 fin_mandatory_pri_all_rec.purpose = 'QUOTE_PER_DIEM' ) -- OKL.H Introduced this.
3242 OR
3243 ( l_icm <> 'FIXED_UPGRADE' AND
3244 fin_mandatory_pri_all_rec.purpose = 'REBOOK_BILLING_ADJUSTMENT' ) -- OKL.H Deleted this purpose from the list
3245 THEN
3246 -- Donot populate this Purpose to the error list.
3247 NULL;
3248 ELSE
3249 l_purpose_list := l_purpose_list ||
3250 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3251 ( G_OKL_FIN_PRIMARY_PURPOSES,fin_mandatory_pri_all_rec.purpose )
3252 || ',';
3253 END IF;
3254 END LOOP;
3255
3256 IF ( belongs_to_ln( p_book_classification ) = TRUE )
3257 THEN
3258 FOR fin_mandatory_pri_ln_rec IN fin_mandatory_pri_ln_csr(l_gtt_id)
3259 LOOP
3260 l_purpose_list := l_purpose_list ||
3261 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3262 ( G_OKL_FIN_PRIMARY_PURPOSES,fin_mandatory_pri_ln_rec.purpose )
3263 || ',';
3264 END LOOP;
3265
3266 --Bug 5139013 dpsingh start
3267 IF l_icm = 'FLOAT' THEN
3268 FOR fin_mandatory_pri_ic_float_rec IN fin_mandatory_pri_ic_float_csr(l_gtt_id)
3269 LOOP
3270 l_purpose_list := l_purpose_list ||
3271 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3272 ( G_OKL_FIN_PRIMARY_PURPOSES,fin_mandatory_pri_ic_float_rec.purpose )
3273 || ',';
3274 END LOOP;
3275 END IF;
3276 --Bug 5139013 dpsingh end
3277 END IF;
3278 IF ( belongs_to_ls( p_book_classification ) = TRUE )
3279 THEN
3280 FOR fin_mandatory_pri_ls_rec IN fin_mandatory_pri_ls_csr(l_gtt_id)
3281 LOOP
3282 l_purpose_list := l_purpose_list ||
3283 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3284 ( G_OKL_FIN_PRIMARY_PURPOSES ,fin_mandatory_pri_ls_rec.purpose )
3285 || ',';
3286 END LOOP;
3287 END IF;
3288 -- Modified by RGOOTY
3289 -- Bug 4111081: Start
3290 IF ( p_book_classification = G_LEASEOP_DEAL_TYPE )
3291 THEN
3292 FOR fin_mandatory_pri_op_rec IN fin_mandatory_pri_op_csr(l_gtt_id)
3293 LOOP
3294 l_purpose_list := l_purpose_list ||
3295 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3296 ( G_OKL_FIN_PRIMARY_PURPOSES ,fin_mandatory_pri_op_rec.purpose )
3297 || ',';
3298 END LOOP;
3299 END IF;
3300
3301 IF ( p_book_classification = G_LEASEDF_DEAL_TYPE OR
3302 p_book_classification = G_LEASEST_DEAL_TYPE )
3303 THEN
3304 FOR fin_mandatory_pri_df_n_st_rec IN fin_mandatory_pri_df_n_st_csr(l_gtt_id)
3305 LOOP
3306 l_purpose_list := l_purpose_list ||
3307 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3308 ( G_OKL_FIN_PRIMARY_PURPOSES ,fin_mandatory_pri_df_n_st_rec.purpose )
3309 || ',';
3310 END LOOP;
3311 END IF;
3312 -- Bug 4111081: End
3313
3314 l_purpose_list := rtrim( l_purpose_list,',' );
3315 IF( length(l_purpose_list) > 0 )
3316 THEN
3317 put_messages_in_table( G_OKL_ST_MANDATORY_PRI_PURPOSE
3318 ,p_during_upd_flag
3319 ,l_message
3320 ,p_token1 => G_PURPOSE_TOKEN
3321 ,p_value1 => l_purpose_list
3322 );
3323 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3324 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3325 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3326 l_msgs_count := l_msgs_count + 1;
3327 END IF;
3328
3329 -- 4. Dependent Stream Types expected for all these stream types
3330 -- Modified by RGOOTY
3331 -- Bug 4129154: Start
3332 l_purpose_list_rent := '';
3333 l_purpose_list_rv := '';
3334 l_purpose_list_vi := '';
3335 FOR man_dep_all_rent_rec IN man_dep_all_rent_csr(l_gtt_id)
3336 LOOP
3337 l_purpose_list_rent := l_purpose_list_rent ||
3338 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3339 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_all_rent_rec.purpose )
3340 || ',';
3341 END LOOP;
3342
3343 IF ( belongs_to_ln( p_book_classification ) = TRUE )
3344 THEN
3345 FOR man_dep_ln_rent_rec IN man_dep_ln_rent_csr(l_gtt_id)
3346 LOOP
3347 --BUG :5052810, Modified by dpsingh
3348 IF NOT (p_book_classification = G_LOAN_REV_DEAL_TYPE and man_dep_ln_rent_rec.purpose = 'PRINCIPAL_CATCHUP') THEN
3349 l_purpose_list_rent := l_purpose_list_rent ||
3350 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3351 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_ln_rent_rec.purpose )
3352 || ',';
3353 END IF;
3354 --BUG :5052810
3355 END LOOP;
3356
3357 FOR man_dep_ln_var_int_rec IN man_dep_ln_var_int_csr(l_gtt_id)
3358 LOOP
3359 l_purpose_list_vi := l_purpose_list_vi ||
3360 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3361 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_ln_var_int_rec.purpose )
3362 || ',';
3363 END LOOP;
3364 END IF;
3365
3366 IF ( belongs_to_df_st( p_book_classification ) = TRUE )
3367 THEN
3368 FOR man_dep_df_st_rent_rec IN man_dep_df_st_rent_csr(l_gtt_id)
3369 LOOP
3370 l_purpose_list_rent := l_purpose_list_rent ||
3371 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3372 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_df_st_rent_rec.purpose )
3373 || ',';
3374 END LOOP;
3375 END IF;
3376
3377 IF ( belongs_to_ls( p_book_classification ) = TRUE )
3378 THEN
3379 FOR man_dep_ls_rent_rec IN man_dep_ls_rent_csr(l_gtt_id)
3380 LOOP
3381 l_purpose_list_rent := l_purpose_list_rent ||
3382 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3383 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_ls_rent_rec.purpose )
3384 || ',';
3385 END LOOP;
3386
3387 FOR man_dep_ls_rv_rec IN man_dep_ls_rv_csr(l_gtt_id)
3388 LOOP
3389 l_purpose_list_rv := l_purpose_list_rv ||
3390 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3391 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_ls_rv_rec.purpose )
3392 || ',';
3393 END LOOP;
3394 END IF;
3395
3396 IF ( belongs_to_op( p_book_classification ) = TRUE )
3397 THEN
3398 FOR man_dep_op_rent_rec IN man_dep_op_rent_csr(l_gtt_id)
3399 LOOP
3400 l_purpose_list_rent := l_purpose_list_rent ||
3401 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3402 ( G_OKL_FIN_DEPENDENT_PURPOSES, man_dep_op_rent_rec.purpose )
3403 || ',';
3404 END LOOP;
3405 END IF;
3406
3407 l_purpose_list_rent := rtrim( l_purpose_list_rent,',' );
3408 l_purpose_list_rv := rtrim( l_purpose_list_rv,',' );
3409 l_purpose_list_vi := rtrim( l_purpose_list_vi,',' );
3410
3411 IF( length(l_purpose_list_rent) > 0 )
3412 THEN
3413 put_messages_in_table( G_OKL_ST_MANDATORY_DEP_PURPOSE
3414 ,p_during_upd_flag
3415 ,l_message
3416 ,p_token1 => 'PRISTREAM'
3417 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3418 ( G_OKL_FIN_PRIMARY_PURPOSES, 'RENT' )
3419 ,p_token2 => G_PURPOSE_TOKEN
3420 ,p_value2 => l_purpose_list_rent
3421 );
3422 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3423 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3424 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3425 l_msgs_count := l_msgs_count + 1;
3426 END IF;
3427
3428 IF( length(l_purpose_list_rv) > 0 )
3429 THEN
3430 put_messages_in_table( G_OKL_ST_MANDATORY_DEP_PURPOSE
3431 ,p_during_upd_flag
3432 ,l_message
3433 ,p_token1 => 'PRISTREAM'
3434 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3435 ( G_OKL_FIN_PRIMARY_PURPOSES, 'RESIDUAL_VALUE' )
3436 ,p_token2 => G_PURPOSE_TOKEN
3437 ,p_value2 => l_purpose_list_rv
3438 );
3439 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3440 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3441 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3442 l_msgs_count := l_msgs_count + 1;
3443 END IF;
3444
3445 IF( length(l_purpose_list_vi) > 0 )
3446 THEN
3447 put_messages_in_table( G_OKL_ST_MANDATORY_DEP_PURPOSE
3448 ,p_during_upd_flag
3449 ,l_message
3450 ,p_token1 => 'PRISTREAM'
3451 ,p_value1 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3452 ( G_OKL_FIN_PRIMARY_PURPOSES, 'VARIABLE_INTEREST' )
3453 ,p_token2 => G_PURPOSE_TOKEN
3454 ,p_value2 => l_purpose_list_vi
3455 );
3456 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3457 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3458 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3459 l_msgs_count := l_msgs_count + 1;
3460 END IF;
3461 -- Bug 4129154: End
3462 -- Rule 6. Certain Book Classifications can have certain Purposes Only.
3463 l_purpose_list := '';
3464 IF ( p_book_classification = G_LEASEDF_DEAL_TYPE OR
3465 p_book_classification = G_LEASEST_DEAL_TYPE )
3466 THEN
3467 FOR purposes_for_df_and_st_rec IN purposes_for_df_and_st_csr(l_gtt_id)
3468 LOOP
3469 l_purpose_list := l_purpose_list ||
3470 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3471 ( G_OKL_STREAM_TYPE_PURPOSE, purposes_for_df_and_st_rec.purpose )
3472 || ',';
3473 END LOOP;
3474 ELSIF ( p_book_classification = G_LEASEOP_DEAL_TYPE )
3475 THEN
3476 FOR purposes_for_op_rec IN purposes_for_op_csr(l_gtt_id)
3477 LOOP
3478 l_purpose_list := l_purpose_list ||
3479 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3480 ( G_OKL_STREAM_TYPE_PURPOSE, purposes_for_op_rec.purpose )
3481 || ',';
3482 END LOOP;
3483 ELSIF ( p_book_classification = G_LOAN_DEAL_TYPE OR p_book_classification = G_LOAN_REV_DEAL_TYPE )
3484 THEN
3485 FOR purposes_for_ln_rec IN purposes_for_ln_csr(l_gtt_id)
3486 LOOP
3487 l_purpose_list := l_purpose_list ||
3488 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3489 ( G_OKL_STREAM_TYPE_PURPOSE, purposes_for_ln_rec.purpose )
3490 || ',';
3491 END LOOP;
3492 END IF;
3493 l_purpose_list := rtrim( l_purpose_list ,',');
3494 IF( length(l_purpose_list) > 0 )
3495 THEN
3496 put_messages_in_table( G_OKL_ST_INVALID_PURPOSES
3497 ,p_during_upd_flag
3498 ,l_message
3499 ,p_token1 => G_PURPOSE_TOKEN
3500 ,p_value1 => l_purpose_list
3501 ,p_token2 => G_DEAL_TYPE_TOKEN
3502 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3503 ( G_OKL_STREAM_ALL_BOOK_CLASS, p_book_classification )
3504 );
3505 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3506 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3507 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3508 l_msgs_count := l_msgs_count + 1;
3509 END IF;
3510 -- Rule 7: Part - I
3511 -- For Loan/Loan-Revolving SGTs with Revenue Recognition = ACTUAL
3512 -- Following are mandatory dependents of RENT Stream
3513 -- DAILY_INTEREST_PRINCIPAL,DAILY_INTEREST_INTEREST ,
3514 -- UNSCHEDULED_LOAN_PAYMENT,VARIABLE_LOAN_PAYMENT
3515 -- EXCESS_PRINCIPAL_PAID, EXCESS_INTEREST_PAID,
3516 -- EXCESS_LOAN_PAYMENT_PAID,ACTUAL_INCOME_ACCRUAL
3517 IF ( p_book_classification = G_LOAN_DEAL_TYPE OR
3518 p_book_classification = G_LOAN_REV_DEAL_TYPE ) AND
3519 l_rrm = 'ACTUAL'
3520 THEN
3521 l_purpose_list := '';
3522 FOR t_rec IN man_dep_ln_vrs_rent_csr( p_gtt_id => p_gtt_id)
3523 LOOP
3524 l_purpose_list := l_purpose_list ||
3525 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3526 ( G_OKL_STREAM_TYPE_PURPOSE, t_rec.purpose )|| ',';
3527 END LOOP;
3528 -- Populate the error messages table if needed.
3529 l_purpose_list := rtrim( l_purpose_list ,',');
3530 IF( length(l_purpose_list) > 0 )
3531 THEN
3532 put_messages_in_table(
3533 'OKL_ST_LN_LNR_VR_MAN_DEP_RENT'
3534 ,p_during_upd_flag
3535 ,l_message
3536 ,p_token1 => 'DEPPURPOSELIST'
3537 ,p_value1 => l_purpose_list
3538 ,p_token2 => 'PRIMRENTSTRM'
3539 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING(G_OKL_STREAM_TYPE_PURPOSE,'RENT'));
3540 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3541 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3542 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3543 l_msgs_count := l_msgs_count + 1;
3544 END IF;
3545 END IF;
3546 -- Rule 7: Part - II
3547 -- For Direct Finance/Deal Type/Operating Lease and Interest Calculation Method = Float Factor
3548 -- The following dependents should be defined for the Rent Primary Stream Type
3549 -- FLOAT_FACTOR_ADJUSTMENT
3550 IF ( p_book_classification = G_LEASEDF_DEAL_TYPE OR
3551 p_book_classification = G_LEASEST_DEAL_TYPE OR
3552 p_book_classification = G_LEASEOP_DEAL_TYPE ) AND
3553 l_icm = 'FLOAT_FACTORS'
3554 THEN
3555 l_purpose_list := '';
3556 FOR t_rec IN man_dep_dfstop_vrs_rent_csr( p_gtt_id => p_gtt_id)
3557 LOOP
3558 l_purpose_list := l_purpose_list ||
3559 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3560 ( G_OKL_STREAM_TYPE_PURPOSE, t_rec.purpose )|| ',';
3561 END LOOP;
3562 -- Populate the error messages table if needed.
3563 l_purpose_list := rtrim( l_purpose_list ,',');
3564 IF( length(l_purpose_list) > 0 )
3565 THEN
3566 put_messages_in_table(
3567 'OKL_ST_OPDFST_VR_MAN_DEP_RENT'
3568 ,p_during_upd_flag
3569 ,l_message
3570 ,p_token1 => 'DEPPURPOSELIST'
3571 ,p_value1 => l_purpose_list
3572 ,p_token2 => 'PRIMRENTSTRM'
3573 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING(G_OKL_STREAM_TYPE_PURPOSE,'RENT'));
3574 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3575 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3576 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3577 l_msgs_count := l_msgs_count + 1;
3578 END IF;
3579 END IF;
3580 -- Rule 7: Part - III
3581 -- For Loans and Interest Calculation Method = Catchup/Cleanup
3582 -- The following dependents should be defined for the Rent Primary Stream Type
3583 -- INTEREST_CATCHUP
3584 IF p_book_classification = G_LOAN_DEAL_TYPE AND
3585 l_icm = 'CATCHUP/CLEANUP'
3586 THEN
3587 l_purpose_list := '';
3588 FOR t_rec IN man_dep_ln_icc_vrs_rent_csr( p_gtt_id => p_gtt_id)
3589 LOOP
3590 l_purpose_list := l_purpose_list ||
3591 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING
3592 ( G_OKL_STREAM_TYPE_PURPOSE, t_rec.purpose )|| ',';
3593 END LOOP;
3594 -- Populate the error messages table if needed.
3595 l_purpose_list := rtrim( l_purpose_list ,',');
3596 IF( length(l_purpose_list) > 0 )
3597 THEN
3598 put_messages_in_table(
3599 'OKL_ST_LN_VR_MAN_DEP_RENT'
3600 ,p_during_upd_flag
3601 ,l_message
3602 ,p_token1 => 'DEPPURPOSELIST'
3603 ,p_value1 => l_purpose_list
3604 ,p_token2 => 'PRIMRENTSTRM'
3605 ,p_value2 => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING(G_OKL_STREAM_TYPE_PURPOSE,'RENT'));
3606 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3607 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3608 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3609 l_msgs_count := l_msgs_count + 1;
3610 END IF;
3611 END IF;
3612 --Rule 8 : Revenue Rec Meth / IC method validation as part of VR build
3613 val_ic_rr_day_con_methods(
3614 p_api_version => p_api_version
3615 ,p_init_msg_list => p_init_msg_list
3616 ,x_return_status => x_return_status
3617 ,x_msg_count => x_msg_count
3618 ,x_msg_data => x_msg_data
3619 ,p_gtt_id => p_gtt_id
3620 ,x_error_msgs_tbl => l_error_msgs_tbl_val_bill
3621 ,p_during_upd_flag => p_during_upd_flag
3622 ,p_book_classification => p_book_classification);
3623 IF (l_error_msgs_tbl_val_bill.COUNT > 0) THEN
3624 i := l_error_msgs_tbl_val_bill.FIRST;
3625 LOOP
3626 l_error_msgs_tbl(l_msgs_count) := l_error_msgs_tbl_val_bill(i);
3627 l_msgs_count := l_msgs_count + 1;
3628 EXIT WHEN (i = l_error_msgs_tbl_val_bill.LAST);
3629 i := l_error_msgs_tbl_val_bill.NEXT(i);
3630 END LOOP;
3631 END IF;
3632 x_error_msgs_tbl := l_error_msgs_tbl;
3633 x_return_status := l_return_status;
3634 EXCEPTION
3635 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3636 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3637 (l_api_name,
3638 G_PKG_NAME,
3639 'OKL_API.G_RET_STS_ERROR',
3640 x_msg_count,
3641 x_msg_data,
3642 '_PVT');
3643 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3644 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3645 ( l_api_name,
3646 G_PKG_NAME,
3647 'OKL_API.G_RET_STS_UNEXP_ERROR',
3648 x_msg_count,
3649 x_msg_data,
3650 '_PVT');
3651 WHEN OTHERS THEN
3652 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3653 ( l_api_name,
3654 G_PKG_NAME,
3655 'OTHERS',
3656 x_msg_count,
3657 x_msg_data,
3658 '_PVT');
3659 end validate_financial_template;
3660
3661 ---------------------------------------------------------------------------
3662 -- PROCEDURE validate_investor_template
3663 ---------------------------------------------------------------------------
3664 -- Start of comments
3665 --
3666 -- Procedure Name : validate_investor_template
3667 -- Description : Validates a Stream Generation Template with Investor
3668 -- as Product Type
3669 -- Business Rules :
3670 -- Parameters :
3671 -- Version : 1.0
3672 -- End of comments
3673 ---------------------------------------------------------------------------
3674
3675 Procedure validate_investor_template(
3676 p_api_version IN NUMBER
3677 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3678 ,x_return_status OUT NOCOPY VARCHAR2
3679 ,x_msg_count OUT NOCOPY NUMBER
3680 ,x_msg_data OUT NOCOPY VARCHAR2
3681 ,p_gtt_id IN okl_st_gen_templates.id%type
3682 ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
3683 ,p_during_upd_flag IN VARCHAR
3684 )IS
3685 l_api_name CONSTANT VARCHAR2(40) := 'validate_investor_template';
3686 l_api_version CONSTANT NUMBER := 1.0;
3687 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3688 l_error_msgs_tbl error_msgs_tbl_type;
3689 l_msgs_count NUMBER := 1;
3690
3691 l_init_msg_list VARCHAR2(1);
3692 l_msg_count NUMBER;
3693 l_msg_data VARCHAR2(2000);
3694 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
3695
3696 -- 1. Only one stream type of the following primary purposes should be defined.
3697 CURSOR inv_only_one_as_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3698 SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
3699 FROM OKL_ST_GEN_TMPT_LNS gtl
3700 ,okl_strm_type_b stb
3701 WHERE gtl.primary_sty_id = stb.id
3702 AND gtl.primary_yn = 'Y'
3703 AND stb.stream_type_purpose IN
3704 (
3705 'INVESTOR_CNTRCT_OBLIGATION_PAY'
3706 ,'INVESTOR_DISB_ADJUSTMENT'
3707 ,'INVESTOR_EVERGREEN_RENT_PAY'
3708 ,'INVESTOR_INTEREST_PAYABLE'
3709 ,'INVESTOR_LATE_FEE_PAYABLE'
3710 ,'INVESTOR_LATE_INTEREST_PAY'
3711 ,'INVESTOR_PAYABLE'
3712 ,'INVESTOR_PRINCIPAL_PAYABLE'
3713 ,'INVESTOR_RECEIVABLE'
3714 ,'INVESTOR_RENT_BUYBACK'
3715 ,'INVESTOR_RENT_DISB_BASIS'
3716 ,'INVESTOR_RENT_PAYABLE'
3717 ,'INVESTOR_RESIDUAL_BUYBACK'
3718 ,'INVESTOR_RESIDUAL_DISB_BASIS'
3719 ,'INVESTOR_RESIDUAL_PAY'
3720 )
3721 AND GTL.GTT_ID = p_gtt_id
3722 group by stb.stream_type_purpose
3723 having count(stb.stream_type_purpose) > 1;
3724
3725 -- 3. All of the following primary purposes must be defined
3726 CURSOR inv_mandatory_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3727 SELECT LOOKUP_CODE PURPOSE
3728 FROM FND_LOOKUPS
3729 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
3730 AND LOOKUP_CODE IN
3731 (
3732 'INVESTOR_CNTRCT_OBLIGATION_PAY'
3733 ,'INVESTOR_DISB_ADJUSTMENT'
3734 ,'INVESTOR_EVERGREEN_RENT_PAY'
3735 ,'INVESTOR_LATE_FEE_PAYABLE'
3736 ,'INVESTOR_LATE_INTEREST_PAY'
3737 ,'INVESTOR_PAYABLE'
3738 -- Modified by RGOOTY
3739 -- Bug 4111470: Start
3740 --,'INVESTOR_INTEREST_PAYABLE'
3741 --,'INVESTOR_PRINCIPAL_PAYABLE'
3742 -- Bug 4111470: End
3743 ,'INVESTOR_RECEIVABLE'
3744 ,'INVESTOR_RENT_BUYBACK'
3745 ,'INVESTOR_RENT_DISB_BASIS'
3746 ,'INVESTOR_RENT_PAYABLE'
3747 ,'INVESTOR_RESIDUAL_BUYBACK'
3748 ,'INVESTOR_RESIDUAL_DISB_BASIS'
3749 ,'INVESTOR_RESIDUAL_PAY'
3750 -- Change requested by Satya on Nov 10
3751 ,'PV_RENT_SECURITIZED'
3752 ,'PV_RV_SECURITIZED'
3753 )
3754 MINUS
3755 SELECT distinct stb.stream_type_purpose purpose
3756 FROM OKL_ST_GEN_TMPT_LNS gtl
3757 , okl_strm_type_b stb
3758 WHERE gtl.primary_sty_id = stb.id
3759 AND gtl.primary_yn = 'Y'
3760 AND stb.stream_type_purpose IN
3761 (
3762 'INVESTOR_CNTRCT_OBLIGATION_PAY'
3763 ,'INVESTOR_DISB_ADJUSTMENT'
3764 ,'INVESTOR_EVERGREEN_RENT_PAY'
3765 ,'INVESTOR_LATE_FEE_PAYABLE'
3766 ,'INVESTOR_LATE_INTEREST_PAY'
3767 ,'INVESTOR_PAYABLE'
3768 -- Modified by RGOOTY
3769 -- Bug 4111470: Start
3770 --,'INVESTOR_INTEREST_PAYABLE'
3771 --,'INVESTOR_PRINCIPAL_PAYABLE'
3772 -- Bug 4111470: End
3773 ,'INVESTOR_RECEIVABLE'
3774 ,'INVESTOR_RENT_BUYBACK'
3775 ,'INVESTOR_RENT_DISB_BASIS'
3776 ,'INVESTOR_RENT_PAYABLE'
3777 ,'INVESTOR_RESIDUAL_BUYBACK'
3778 ,'INVESTOR_RESIDUAL_DISB_BASIS'
3779 ,'INVESTOR_RESIDUAL_PAY'
3780 -- Change requested by Satya on Nov 10
3781 ,'PV_RENT_SECURITIZED'
3782 ,'PV_RV_SECURITIZED'
3783 )
3784 and GTL.GTT_ID = p_gtt_id;
3785 l_message VARCHAR2(2700);
3786 l_purpose_list VARCHAR2(10000);
3787 BEGIN
3788 -- Perform the Initializations
3789 l_return_status := OKL_API.G_RET_STS_SUCCESS;
3790 -- Only one stream type of the following primary purposes should be defined.
3791 l_purpose_list := '';
3792 FOR only_one_as_primary_rec IN inv_only_one_as_primary_csr(p_gtt_id)
3793 LOOP
3794 l_purpose_list := l_purpose_list ||
3795 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING(
3796 G_OKL_INV_PRIMARY_PURPOSES, only_one_as_primary_rec.purpose )
3797 || ',';
3798 END LOOP;
3799 l_purpose_list := rtrim( l_purpose_list,',' );
3800 IF( length(l_purpose_list) > 0 )
3801 THEN
3802 put_messages_in_table( G_OKL_ST_STRM_ONE_PRI_PURPOSE
3803 ,p_during_upd_flag
3804 ,l_message
3805 ,p_token1 => G_PURPOSE_TOKEN
3806 ,p_value1 => l_purpose_list
3807 );
3808 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3809 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3810 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3811 l_msgs_count := l_msgs_count + 1;
3812 END IF;
3813
3814 -- All of the following primary purposes must be defined
3815 l_purpose_list := '';
3816 FOR okl_mandatory_primary_rec IN inv_mandatory_primary_csr(p_gtt_id)
3817 LOOP
3818 l_purpose_list := l_purpose_list ||
3819 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING(
3820 G_OKL_INV_PRIMARY_PURPOSES, okl_mandatory_primary_rec.purpose )
3821 || ',';
3822 END LOOP;
3823 l_purpose_list := rtrim( l_purpose_list,',' );
3824 IF( length(l_purpose_list) > 0 )
3825 THEN
3826 put_messages_in_table( G_OKL_ST_MANDATORY_PRI_PURPOSE
3827 ,p_during_upd_flag
3828 ,l_message
3829 ,p_token1 => G_PURPOSE_TOKEN
3830 ,p_value1 => l_purpose_list
3831 );
3832 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
3833 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
3834 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
3835 l_msgs_count := l_msgs_count + 1;
3836 END IF;
3837 x_error_msgs_tbl := l_error_msgs_tbl;
3838 x_return_status := l_return_status;
3839 EXCEPTION
3840 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3841 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3842 (l_api_name,
3843 G_PKG_NAME,
3844 'OKL_API.G_RET_STS_ERROR',
3845 x_msg_count,
3846 x_msg_data,
3847 '_PVT');
3848 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3849 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3850 ( l_api_name,
3851 G_PKG_NAME,
3852 'OKL_API.G_RET_STS_UNEXP_ERROR',
3853 x_msg_count,
3854 x_msg_data,
3855 '_PVT');
3856 WHEN OTHERS THEN
3857 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3858 ( l_api_name,
3859 G_PKG_NAME,
3860 'OTHERS',
3861 x_msg_count,
3862 x_msg_data,
3863 '_PVT');
3864 end validate_investor_template;
3865
3866
3867
3868 ---------------------------------------------------------------------------
3869 -- PROCEDURE activate_template
3870 ---------------------------------------------------------------------------
3871 -- Start of comments
3872 --
3873 -- Procedure Name : activate_template
3874 -- Description : Activate a Template
3875 -- Business Rules :
3876 -- Parameters :
3877 -- Version : 1.0
3878 -- End of comments
3879 ---------------------------------------------------------------------------
3880 Procedure activate_template(
3881 p_api_version IN NUMBER
3882 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3883 ,x_return_status OUT NOCOPY VARCHAR2
3884 ,x_msg_count OUT NOCOPY NUMBER
3885 ,x_msg_data OUT NOCOPY VARCHAR2
3886 ,p_gtt_id IN okl_st_gen_templates.id%type
3887 )IS
3888
3889 l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
3890 l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
3891 l_api_version CONSTANT NUMBER := 1.0;
3892 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3893
3894 l_init_msg_list VARCHAR2(1);
3895 l_msg_count NUMBER;
3896 l_msg_data VARCHAR2(2000);
3897 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
3898 l_gttv_rec_in gttv_rec_type;
3899 l_gttv_rec_out gttv_rec_type;
3900
3901 CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3902 SELECT ID
3903 ,GTS_ID
3904 ,VERSION
3905 ,START_DATE
3906 ,END_DATE
3907 ,TMPT_STATUS
3908 FROM OKL_ST_GEN_TEMPLATES
3909 where ID = p_gtt_id;
3910 BEGIN
3911 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3912 l_return_status := OKL_API.START_ACTIVITY( l_api_name
3913 ,g_pkg_name
3914 ,p_init_msg_list
3915 ,l_api_version
3916 ,p_api_version
3917 ,'_PVT'
3918 ,x_return_status);
3919 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3920 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3921 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3922 RAISE OKL_API.G_EXCEPTION_ERROR;
3923 END IF;
3924
3925 l_dummy := Okl_Api.G_FALSE;
3926 FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
3927 LOOP
3928 l_gttv_rec_in.id := gttv_rec_temp.id;
3929 l_gttv_rec_in.gts_id := gttv_rec_temp.gts_id;
3930 l_gttv_rec_in.version := gttv_rec_temp.version;
3931 l_gttv_rec_in.start_date := gttv_rec_temp.start_date;
3932 l_gttv_rec_in.end_date := gttv_rec_temp.end_date;
3933 l_gttv_rec_in.tmpt_status := gttv_rec_temp.tmpt_status;
3934 l_dummy := Okl_Api.G_TRUE;
3935 END LOOP;
3936
3937 IF (l_dummy = okl_api.g_false) THEN
3938 okl_api.SET_MESSAGE( p_app_name => g_app_name,
3939 p_msg_name => g_invalid_value,
3940 p_token1 => g_col_name_token,
3941 p_token1_value => 'GTT_ID');
3942 RAISE OKL_API.G_EXCEPTION_ERROR;
3943 END IF;
3944
3945 -- Validations checked until now
3946 -- 1. Template status should be 'COMPLETE'
3947 IF ( l_gttv_rec_in.tmpt_status = G_STATUS_COMPLETE )
3948 THEN
3949 l_gttv_rec_in.tmpt_status := G_STATUS_ACTIVE;
3950 okl_gtt_pvt.update_row(
3951 p_api_version => l_api_version
3952 ,p_init_msg_list => p_init_msg_list
3953 ,x_return_status => l_return_status
3954 ,x_msg_count => l_msg_count
3955 ,x_msg_data => l_msg_data
3956 ,p_gttv_rec => l_gttv_rec_in
3957 ,x_gttv_rec => l_gttv_rec_out
3958 );
3959 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3960 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3961 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3962 RAISE OKL_API.G_EXCEPTION_ERROR;
3963 END IF;
3964 ELSE
3965 -- Show a message saying that template status is not complete
3966 okl_api.SET_MESSAGE( p_app_name => g_app_name,
3967 p_msg_name => g_invalid_value,
3968 p_token1 => g_col_name_token,
3969 p_token1_value => 'TMPT_STATUS');
3970 RAISE OKL_API.G_EXCEPTION_ERROR;
3971 END IF;
3972 x_return_status := l_return_status;
3973 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3974 EXCEPTION
3975 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3976 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3977 (l_api_name,
3978 G_PKG_NAME,
3979 'OKL_API.G_RET_STS_ERROR',
3980 x_msg_count,
3981 x_msg_data,
3982 '_PVT');
3983 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3984 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3985 ( l_api_name,
3986 G_PKG_NAME,
3987 'OKL_API.G_RET_STS_UNEXP_ERROR',
3988 x_msg_count,
3989 x_msg_data,
3990 '_PVT');
3991 WHEN OTHERS THEN
3992 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3993 ( l_api_name,
3994 G_PKG_NAME,
3995 'OTHERS',
3996 x_msg_count,
3997 x_msg_data,
3998 '_PVT');
3999 END;
4000
4001 ---------------------------------------------------------------------------
4002 -- PROCEDURE validate_mandatory_dep
4003 ---------------------------------------------------------------------------
4004 -- Start of comments
4005 --
4006 -- Procedure Name : validate_for_warnings
4007 -- Description : Validates the SGT for any warnings
4008 -- Business Rules :
4009 -- Parameters :
4010 -- Version : 1.0
4011 -- End of comments
4012 ---------------------------------------------------------------------------
4013 Procedure validate_mandatory_dep(
4014 p_api_version IN NUMBER
4015 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4016 ,x_return_status OUT NOCOPY VARCHAR2
4017 ,x_msg_count OUT NOCOPY NUMBER
4018 ,x_msg_data OUT NOCOPY VARCHAR2
4019 ,p_gtt_id IN okl_st_gen_templates.id%type
4020 ,p_pri_sty_id IN okl_st_gen_tmpt_lns.primary_Sty_id%type
4021 ,x_missing_deps OUT NOCOPY VARCHAR2
4022 ,x_show_warn_flag OUT NOCOPY VARCHAR2
4023 ,p_deal_type IN VARCHAR2
4024 )IS
4025 l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4026 l_api_name CONSTANT VARCHAR2(40) := 'validate_mandatory_dep';
4027 l_api_version CONSTANT NUMBER := 1.0;
4028 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4029
4030 l_purpose_list VARCHAR2(10000);
4031 -- Cursor to fetch the missed dependent streams for a Primary Stream type
4032 -- with purpose as 'Fee Payment'
4033 CURSOR fee_payment_man_dep_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type
4034 ,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.primary_sty_id%TYPE
4035 ,p_exclude_fr IN VARCHAR2 ) IS
4036 SELECT LOOKUP_CODE PURPOSE
4037 FROM FND_LOOKUPS
4038 WHERE LOOKUP_TYPE = G_OKL_FIN_DEPENDENT_PURPOSES
4039 AND LOOKUP_CODE IN
4040 (
4041 'AMORTIZE_FEE_INCOME'
4042 ,'ACCRUED_FEE_INCOME'
4043 ,'PRINCIPAL_PAYMENT'
4044 ,'INTEREST_PAYMENT'
4045 ,'PRINCIPAL_BALANCE'
4046 ,'INTEREST_INCOME'
4047 ,'LOAN_PAYMENT'
4048 ,'FEE_RENEWAL'
4049 ,'PASS_THRU_REV_ACCRUAL'
4050 ,'PASS_THRU_EXP_ACCRUAL'
4051 )
4052 MINUS
4053 (
4054 SELECT STB.STREAM_TYPE_PURPOSE PURPOSE
4055 FROM OKL_ST_GEN_TMPT_LNS GTL
4056 ,OKL_STRM_TYPE_B STB
4057 WHERE GTL.GTT_ID = p_gtt_id
4058 AND GTL.PRIMARY_YN = 'N'
4059 AND GTL.DEPENDENT_STY_ID = STB.ID
4060 AND GTL.PRIMARY_STY_ID = p_pri_sty_id
4061 )
4062 MINUS
4063 (
4064 SELECT 'FEE_RENEWAL' PURPOSE
4065 FROM DUAL
4066 WHERE p_exclude_fr = 'T'
4067 );
4068 l_exclude_fr VARCHAR2(1) := OKL_API.G_TRUE;
4069 BEGIN
4070 l_return_status := OKL_API.G_RET_STS_SUCCESS;
4071
4072 x_show_warn_flag := OKL_API.G_FALSE;
4073 l_purpose_list := '';
4074 IF ( belongs_to_ls(p_deal_type) = TRUE )
4075 THEN
4076 l_exclude_fr := 'F';
4077 END IF;
4078 FOR fee_payment_man_dep_rec IN fee_payment_man_dep_csr(p_gtt_id,p_pri_sty_id,l_exclude_fr)
4079 LOOP
4080 l_purpose_list := l_purpose_list ||
4081 OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING(
4082 G_OKL_FIN_DEPENDENT_PURPOSES, fee_payment_man_dep_rec.purpose )
4083 || ',';
4084 x_show_warn_flag := OKL_API.G_TRUE;
4085 END LOOP;
4086
4087 l_purpose_list := rtrim( l_purpose_list,',' );
4088 x_missing_deps := l_purpose_list;
4089 x_return_status := l_return_status;
4090 EXCEPTION
4091 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4092 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4093 (l_api_name,
4094 G_PKG_NAME,
4095 'OKL_API.G_RET_STS_ERROR',
4096 x_msg_count,
4097 x_msg_data,
4098 '_PVT');
4099 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4100 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4101 ( l_api_name,
4102 G_PKG_NAME,
4103 'OKL_API.G_RET_STS_UNEXP_ERROR',
4104 x_msg_count,
4105 x_msg_data,
4106 '_PVT');
4107 WHEN OTHERS THEN
4108 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4109 ( l_api_name,
4110 G_PKG_NAME,
4111 'OTHERS',
4112 x_msg_count,
4113 x_msg_data,
4114 '_PVT');
4115 END;
4116
4117 ---------------------------------------------------------------------------
4118 -- PROCEDURE validate_for_warnings
4119 ---------------------------------------------------------------------------
4120 -- Start of comments
4121 --
4122 -- Procedure Name : validate_for_warnings
4123 -- Description : Validates the SGT for any warnings
4124 -- Business Rules :
4125 -- Parameters :
4126 -- Version : 1.0
4127 -- End of comments
4128 ---------------------------------------------------------------------------
4129 Procedure validate_for_warnings(
4130 p_api_version IN NUMBER
4131 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4132 ,x_return_status OUT NOCOPY VARCHAR2
4133 ,x_msg_count OUT NOCOPY NUMBER
4134 ,x_msg_data OUT NOCOPY VARCHAR2
4135 ,p_gtt_id IN okl_st_gen_templates.id%type
4136 ,x_wrn_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4137 ,p_during_upd_flag IN VARCHAR
4138 ,x_pri_purpose_list OUT NOCOPY VARCHAR
4139 )IS
4140
4141 l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4142 l_api_name CONSTANT VARCHAR2(40) := 'validate_for_warnings';
4143 l_api_version CONSTANT NUMBER := 1.0;
4144 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4145 l_missing_deps VARCHAR2(10000);
4146 l_show_warn_flag VARCHAR2(1) := OKL_API.G_FALSE;
4147 l_wrn_msgs_tbl error_msgs_tbl_type;
4148 l_msgs_count NUMBER := 1;
4149 l_message VARCHAR2(2700);
4150 l_pri_purpose_list VARCHAR2(10000);
4151
4152 -- Cursor to fetch the Primary Stream Types with purpose as 'FEE PAYMENT'
4153 CURSOR fee_payment_pri_strms_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
4154 SELECT GTL.PRIMARY_STY_ID PRI_STY_ID
4155 ,GTS.DEAL_TYPE DEAL_TYPE
4156 ,STB.ID STRM_TYPE_ID
4157 ,STB.CODE STRM_NAME
4158 FROM OKL_ST_GEN_TMPT_LNS GTL
4159 ,OKL_ST_GEN_TMPT_SETS GTS
4160 ,OKL_ST_GEN_TEMPLATES GTT
4161 ,OKL_STRM_TYPE_B STB
4162 WHERE GTL.GTT_ID = p_gtt_id
4163 AND GTL.GTT_ID = GTT.ID
4164 AND GTS.ID = GTT.GTS_ID
4165 AND GTL.PRIMARY_YN = 'Y'
4166 AND GTL.PRIMARY_STY_ID = STB.ID
4167 AND STB.STREAM_TYPE_PURPOSE = 'FEE_PAYMENT';
4168 BEGIN
4169 l_return_status := OKL_API.G_RET_STS_SUCCESS;
4170 l_pri_purpose_list := '';
4171 FOR fee_payment_pri_strms_rec IN fee_payment_pri_strms_csr(p_gtt_id)
4172 LOOP
4173 validate_mandatory_dep(
4174 p_api_version => p_api_version
4175 ,p_init_msg_list => p_init_msg_list
4176 ,x_return_status => l_return_status
4177 ,x_msg_count => x_msg_count
4178 ,x_msg_data => x_msg_data
4179 ,p_gtt_id => p_gtt_id
4180 ,p_pri_sty_id => fee_payment_pri_strms_rec.strm_type_id
4181 ,x_missing_deps => l_missing_deps
4182 ,x_show_warn_flag => l_show_warn_flag
4183 ,p_deal_type => fee_payment_pri_strms_rec.deal_type
4184 );
4185 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4186 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4187 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4188 RAISE OKL_API.G_EXCEPTION_ERROR;
4189 END IF;
4190 IF ( l_show_warn_flag <> OKL_API.G_FALSE )
4191 THEN
4192 put_messages_in_table( 'OKL_ST_SGT_MAN_DEP_MISSING_WRN'
4193 ,p_during_upd_flag
4194 ,l_message
4195 ,p_token1 => 'PRI_STRM'
4196 ,p_value1 => fee_payment_pri_strms_rec.STRM_NAME
4197 ,p_token2 => 'DEP_STRM'
4198 ,p_value2 => l_missing_deps
4199 );
4200 l_wrn_msgs_tbl(l_msgs_count).error_message := l_message;
4201 l_wrn_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_WARNING;
4202 l_wrn_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_WARNING);
4203 l_msgs_count := l_msgs_count + 1;
4204 l_pri_purpose_list := l_pri_purpose_list ||
4205 fee_payment_pri_strms_rec.STRM_NAME ||
4206 ',';
4207 END IF;
4208 END LOOP;
4209
4210 x_wrn_msgs_tbl := l_wrn_msgs_tbl;
4211 x_pri_purpose_list := rtrim( l_pri_purpose_list,',' );
4212 x_return_status := l_return_status;
4213 EXCEPTION
4214 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4215 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4216 (l_api_name,
4217 G_PKG_NAME,
4218 'OKL_API.G_RET_STS_ERROR',
4219 x_msg_count,
4220 x_msg_data,
4221 '_PVT');
4222 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4223 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4224 ( l_api_name,
4225 G_PKG_NAME,
4226 'OKL_API.G_RET_STS_UNEXP_ERROR',
4227 x_msg_count,
4228 x_msg_data,
4229 '_PVT');
4230 WHEN OTHERS THEN
4231 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4232 ( l_api_name,
4233 G_PKG_NAME,
4234 'OTHERS',
4235 x_msg_count,
4236 x_msg_data,
4237 '_PVT');
4238 END;
4239 ---------------------------------------------------------------------------
4240 -- PROCEDURE validate_template
4241 ---------------------------------------------------------------------------
4242 -- Start of comments
4243 --
4244 -- Procedure Name : validate_template
4245 -- Description : Validates a Stream Generation Template
4246 -- Business Rules :
4247 -- Parameters :
4248 -- Version : 1.0
4249 -- End of comments
4250 ---------------------------------------------------------------------------
4251
4252 Procedure validate_template(
4253 p_api_version IN NUMBER
4254 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4255 ,x_return_status OUT NOCOPY VARCHAR2
4256 ,x_msg_count OUT NOCOPY NUMBER
4257 ,x_msg_data OUT NOCOPY VARCHAR2
4258 ,p_gtt_id IN okl_st_gen_templates.id%type
4259 ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4260 ,x_return_tmpt_status OUT NOCOPY VARCHAR2
4261 ,p_during_upd_flag IN VARCHAR2
4262 )IS
4263 l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4264 l_api_name CONSTANT VARCHAR2(40) := 'validate_template';
4265 l_api_version CONSTANT NUMBER := 1.0;
4266 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4267 l_error_msgs_tbl error_msgs_tbl_type;
4268 l_wrn_msgs_tbl error_msgs_tbl_type;
4269
4270 l_init_msg_list VARCHAR2(1);
4271 l_msg_count NUMBER;
4272 l_msg_data VARCHAR2(2000);
4273
4274 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
4275 l_pri_purpose_list VARCHAR2(2000);
4276
4277 l_gttv_rec_in gttv_rec_type;
4278 l_gttv_rec_out gttv_rec_type;
4279
4280 CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
4281 SELECT GTT.ID ID
4282 ,GTT.GTS_ID GTS_ID
4283 ,GTS.NAME NAME
4284 ,GTS.PRODUCT_TYPE PRODUCT_TYPE
4285 ,GTS.DEAL_TYPE DEAL_TYPE
4286 ,GTT.START_DATE START_DATE
4287 ,GTT.END_DATE END_DATE
4288 ,GTT.TMPT_STATUS TMPT_STATUS
4289 ,GTT.VERSION VERSION
4290 FROM OKL_ST_GEN_TEMPLATES GTT
4291 ,OKL_ST_GEN_TMPT_SETS GTS
4292 WHERE GTT.GTS_ID = GTS.ID
4293 AND GTT.ID = p_gtt_id;
4294 l_tmpt_set_rec okl_st_gen_templates_csr%ROWTYPE;
4295 BEGIN
4296 -- Perform the Initializations
4297 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4298 l_return_status := OKL_API.START_ACTIVITY( l_api_name
4299 ,g_pkg_name
4300 ,p_init_msg_list
4301 ,l_api_version
4302 ,p_api_version
4303 ,'_PVT'
4304 ,x_return_status);
4305 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4306 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4307 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4308 RAISE OKL_API.G_EXCEPTION_ERROR;
4309 END IF;
4310 l_dummy := Okl_Api.G_FALSE;
4311 FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
4312 LOOP
4313 l_tmpt_set_rec.id := gttv_rec_temp.id;
4314 l_tmpt_set_rec.gts_id := gttv_rec_temp.gts_id;
4315 l_tmpt_set_rec.start_date := gttv_rec_temp.start_date;
4316 l_tmpt_set_rec.end_date := gttv_rec_temp.end_date;
4317 l_tmpt_set_rec.tmpt_status := gttv_rec_temp.tmpt_status;
4318 l_tmpt_set_rec.deal_type := gttv_rec_temp.deal_type;
4319 l_tmpt_set_rec.product_type := gttv_rec_temp.product_type;
4320 l_tmpt_set_rec.version := gttv_rec_temp.version;
4321 l_dummy := Okl_Api.G_TRUE;
4322 END LOOP;
4323 IF (l_dummy = okl_api.g_false) THEN
4324 okl_api.SET_MESSAGE( p_app_name => g_app_name,
4325 p_msg_name => g_invalid_value,
4326 p_token1 => g_col_name_token,
4327 p_token1_value => 'GTT_ID');
4328 RAISE OKL_API.G_EXCEPTION_ERROR;
4329 END IF;
4330 -- If the Product Type is INVESTOR call the validate_investor_template api
4331 IF ( l_tmpt_set_rec.product_type = G_INVESTOR_PRODUCT )
4332 THEN
4333 -- Validate the Investor Template
4334 validate_investor_template(
4335 1.0
4336 ,'T'
4337 ,l_return_status
4338 ,x_msg_count
4339 ,x_msg_data
4340 ,l_gtt_id
4341 ,l_error_msgs_tbl
4342 ,p_during_upd_flag );
4343 ELSE
4344 -- Validate the Financial Template
4345 validate_financial_template(
4346 1.0
4347 ,'T'
4348 ,l_return_status
4349 ,x_msg_count
4350 ,x_msg_data
4351 ,l_gtt_id
4352 ,l_error_msgs_tbl
4353 ,p_during_upd_flag
4354 ,l_tmpt_set_rec.deal_type);
4355 END IF;
4356 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4357 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4358 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4359 RAISE OKL_API.G_EXCEPTION_ERROR;
4360 END IF;
4361 -- Update the template status based on the x_return_status
4362 -- or l_error_msgs_tbl.COUNT
4363 l_gttv_rec_in.id := l_tmpt_set_rec.id;
4364 l_gttv_rec_in.gts_id := l_tmpt_set_rec.gts_id;
4365 l_gttv_rec_in.version := l_tmpt_set_rec.version;
4366 l_gttv_rec_in.start_date := l_tmpt_set_rec.start_date;
4367 l_gttv_rec_in.end_date := l_tmpt_set_rec.end_date;
4368 -- Determine the status of the Template after the Validation
4369 IF ( l_error_msgs_tbl.COUNT IS NULL OR
4370 l_error_msgs_tbl.COUNT = 0 )
4371 THEN
4372 -- When the status of the template getting updated is Active, after
4373 -- successful validation , the status remains at Active.
4374 IF (l_tmpt_set_rec.tmpt_status = G_STATUS_ACTIVE)
4375 THEN
4376 l_gttv_rec_in.tmpt_status := G_STATUS_ACTIVE;
4377 ELSE
4378 -- In all other cases the Template status should be COMPLETE
4379 l_gttv_rec_in.tmpt_status := G_STATUS_COMPLETE;
4380 END IF;
4381 ELSE
4382 -- Change the template status to INCOMPLETE
4383 -- Note there is not status as INCOMPLETE available as of now.
4384 IF (l_tmpt_set_rec.tmpt_status = G_STATUS_ACTIVE)
4385 THEN
4386 l_gttv_rec_in.tmpt_status := G_STATUS_ACTIVE;
4387 ELSE
4388 -- In all other cases the Template status should be INCOMPLETE
4389 l_gttv_rec_in.tmpt_status := G_STATUS_INCOMPLETE;
4390 END IF;
4391 END IF;
4392 -- Call the update method of the Stream Generation Template
4393 okl_gtt_pvt.update_row(
4394 p_api_version => l_api_version
4395 ,p_init_msg_list => p_init_msg_list
4396 ,x_return_status => l_return_status
4397 ,x_msg_count => x_msg_count
4398 ,x_msg_data => x_msg_data
4399 ,p_gttv_rec => l_gttv_rec_in
4400 ,x_gttv_rec => l_gttv_rec_out
4401 );
4402 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4403 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4404 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4405 RAISE OKL_API.G_EXCEPTION_ERROR;
4406 END IF;
4407 -- Call the validate_for_Warnings only when
4408 -- a. If the template is Financial, and its not during Update mode
4409 IF ( p_during_upd_flag = OKL_API.G_FALSE
4410 AND l_tmpt_set_rec.product_type <> G_INVESTOR_PRODUCT )
4411 THEN
4412 validate_for_warnings(
4413 p_api_version => p_api_version
4414 ,p_init_msg_list => p_init_msg_list
4415 ,x_return_status => l_return_status
4416 ,x_msg_count => x_msg_count
4417 ,x_msg_data => x_msg_data
4418 ,p_gtt_id => l_gtt_id
4419 ,x_wrn_msgs_tbl => l_wrn_msgs_tbl
4420 ,p_during_upd_flag => OKL_API.G_FALSE
4421 ,x_pri_purpose_list => l_pri_purpose_list
4422 );
4423 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4424 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4425 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4426 RAISE OKL_API.G_EXCEPTION_ERROR;
4427 END IF;
4428 -- Add the messages to the errors table
4429 l_msg_count := l_error_msgs_tbl.COUNT;
4430 IF ( l_wrn_msgs_tbl.COUNT > 0 )
4431 THEN
4432 FOR i in l_wrn_msgs_tbl.FIRST .. l_wrn_msgs_tbl.LAST
4433 LOOP
4434 l_msg_count := l_msg_count + 1;
4435 l_error_msgs_tbl( l_msg_count ).error_message := l_wrn_msgs_tbl(i).error_message;
4436 l_error_msgs_tbl( l_msg_count ).error_type_code := l_wrn_msgs_tbl(i).error_type_code;
4437 l_error_msgs_tbl( l_msg_count ).error_type_meaning := l_wrn_msgs_tbl(i).error_type_meaning;
4438 END LOOP;
4439 END IF;
4440 END IF;
4441 x_error_msgs_tbl := l_error_msgs_tbl;
4442 x_return_tmpt_status := l_gttv_rec_out.tmpt_status;
4443 x_return_status := l_return_status;
4444 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4445 EXCEPTION
4446 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4447 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4448 (l_api_name,
4449 G_PKG_NAME,
4450 'OKL_API.G_RET_STS_ERROR',
4451 x_msg_count,
4452 x_msg_data,
4453 '_PVT');
4454 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4455 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4456 ( l_api_name,
4457 G_PKG_NAME,
4458 'OKL_API.G_RET_STS_UNEXP_ERROR',
4459 x_msg_count,
4460 x_msg_data,
4461 '_PVT');
4462 WHEN OTHERS THEN
4463 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4464 ( l_api_name,
4465 G_PKG_NAME,
4466 'OTHERS',
4467 x_msg_count,
4468 x_msg_data,
4469 '_PVT');
4470 END validate_template;
4471
4472 ---------------------------------------------------------------------------
4473 -- PROCEDURE update_dep_strms
4474 ---------------------------------------------------------------------------
4475 -- Start of comments
4476 --
4477 -- Procedure Name : update_dep_strms
4478 -- Description : Update Dependent Streams of a Template
4479 -- Business Rules :
4480 -- Parameters :
4481 -- Version : 1.0
4482 -- End of comments
4483 ---------------------------------------------------------------------------
4484
4485 Procedure update_dep_strms(
4486 p_api_version IN NUMBER
4487 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4488 ,x_return_status OUT NOCOPY VARCHAR2
4489 ,x_msg_count OUT NOCOPY NUMBER
4490 ,x_msg_data OUT NOCOPY VARCHAR2
4491 ,p_gtt_id IN OKL_ST_GEN_TEMPLATES.ID%type
4492 ,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE
4493 ,p_gtlv_tbl IN gtlv_tbl_type
4494 ,x_missing_deps OUT NOCOPY VARCHAR2
4495 ,x_show_warn_flag OUT NOCOPY VARCHAR2
4496 )IS
4497 l_api_name CONSTANT VARCHAR2(40) := 'update_dep_strms';
4498 l_api_version CONSTANT NUMBER := 1.0;
4499 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4500
4501 l_init_msg_list VARCHAR2(1);
4502 l_msg_count NUMBER;
4503 l_msg_data VARCHAR2(2000);
4504
4505 l_gttv_rec_in gttv_rec_type;
4506 l_gtlv_tbl_upd_in gtlv_tbl_type;
4507 l_gtlv_tbl_ins_in gtlv_tbl_type;
4508
4509 l_gttv_rec_out gttv_rec_type;
4510 l_gtlv_tbl_upd_out gtlv_tbl_type;
4511 l_gtlv_tbl_ins_out gtlv_tbl_type;
4512 i NUMBER;
4513 ins_table_count NUMBER;
4514 upd_table_count NUMBER;
4515
4516 --Added by bkatraga on 05-Apr-2005
4517 l_error_msgs_tbl error_msgs_tbl_type;
4518 l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4519 --end changes
4520
4521 -- Modified by RGOOTY
4522 -- Bug 4054596: Issue No. 5: Start
4523 CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE
4524 ,p_pri_id IN OKL_ST_GEN_TMPT_LNS.primary_Sty_id%TYPE) IS
4525 SELECT STY.STREAM_TYPE_PURPOSE PURPOSE_CODE,
4526 ( SELECT MEANING FROM
4527 FND_LOOKUPS
4528 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
4529 AND LOOKUP_CODE = STY.STREAM_TYPE_PURPOSE
4530 ) PURPOSE_MEANING,
4531 COUNT( STY.STREAM_TYPE_PURPOSE) streams_count
4532 FROM OKL_ST_GEN_TMPT_LNS GTL
4533 ,okl_Strm_Type_b STY
4534 WHERE GTL.DEPENDENT_STY_ID = STY.ID
4535 AND PRIMARY_YN = 'N'
4536 AND GTL.GTT_ID = p_gtt_id
4537 AND GTL.PRIMARY_STY_ID = p_pri_id
4538 GROUP BY STY.STREAM_TYPE_PURPOSE
4539 HAVING COUNT( STREAM_TYPE_PURPOSE) > 1;
4540
4541 l_found VARCHAR2(1) := OKL_API.G_FALSE;
4542 l_gtt_id OKL_ST_GEN_TMPT_LNS.GTT_ID%TYPE;
4543 l_pri_sty_id OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE;
4544 l_purp_list VARCHAR2(250);
4545 -- Bug 4054596: Issue No. 5: End
4546
4547 l_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4548 l_deal_type OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE;
4549
4550 CURSOR tmpt_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE ) IS
4551 SELECT GTS.DEAL_TYPE DEAL_TYPE
4552 ,GTT.TMPT_STATUS TMPT_STATUS
4553 ,GTS.ID GTS_ID -- Bug 4094361, Modified by RGOOTY
4554 FROM OKL_ST_GEN_TMPT_SETS GTS
4555 ,OKL_ST_GEN_TEMPLATES GTT
4556 WHERE GTS.ID = GTT.GTS_ID
4557 AND GTT.ID = p_gtt_id;
4558
4559 CURSOR tmpt_pri_strm_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE
4560 ,p_pri_sty_id OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE)
4561 IS
4562 SELECT STY.STREAM_TYPE_PURPOSE PRI_STRM_PURPOSE
4563 FROM OKL_ST_GEN_TMPT_LNS GTL
4564 ,OKL_STRM_TYPE_B STY
4565 WHERE STY.ID = GTL.PRIMARY_sTY_ID
4566 AND GTL.PRIMARY_YN = 'Y'
4567 AND GTL.GTT_ID = p_gtt_id
4568 AND GTL.PRIMARY_STY_ID = p_pri_sty_id;
4569
4570 l_missing_deps VARCHAR2(10000) := OKL_API.G_MISS_CHAR;
4571 l_show_warn_flag VARCHAR2(1):= OKL_API.G_FALSE;
4572 l_pri_strm_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%TYPE;
4573
4574 -- Modified by RGOOTY
4575 -- Bug 4094361: Start
4576 CURSOR pdt_for_active_sgt_csr( p_gts_id OKL_ST_GEN_TMPT_SETS.ID%TYPE )
4577 IS
4578 SELECT PDT.ID PDT_ID
4579 ,PRODUCT_STATUS_CODE
4580 FROM OKL_PRODUCTS PDT,
4581 OKL_AE_TMPT_SETS ATS, OKL_ST_GEN_TMPT_SETS SGT
4582 WHERE PDT.AES_ID = ATS.ID
4583 AND ATS.GTS_ID = SGT.ID
4584 AND SGT.ID = p_gts_id;
4585
4586 l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
4587 -- Bug 4094361: End
4588 BEGIN
4589 -- Perform the Initializations
4590 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4591 l_return_status := OKL_API.START_ACTIVITY( l_api_name
4592 ,g_pkg_name
4593 ,p_init_msg_list
4594 ,l_api_version
4595 ,p_api_version
4596 ,'_PVT'
4597 ,x_return_status);
4598 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4599 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4600 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4601 RAISE OKL_API.G_EXCEPTION_ERROR;
4602 END IF;
4603 -- Updating/Inserting the Template Lines
4604 IF (p_gtlv_tbl.COUNT > 0) THEN
4605 ins_table_count :=0;
4606 upd_table_count :=0;
4607 FOR i IN p_gtlv_tbl.FIRST .. p_gtlv_tbl.LAST
4608 LOOP
4609 -- Decide whether we need to update or insert the Template Lines.
4610 IF (p_gtlv_tbl(i).id = Okl_Api.G_MISS_NUM OR p_gtlv_tbl(i).id IS NULL
4611 OR p_gtlv_tbl(i).id = 0 )
4612 THEN
4613 -- Copy into the Insert table
4614 l_gtlv_tbl_ins_in(ins_table_count) := p_gtlv_tbl(i);
4615 ins_table_count := ins_table_count + 1;
4616 ELSE
4617 -- Copy into the Update table
4618 l_gtlv_tbl_upd_in(upd_table_count) := p_gtlv_tbl(i);
4619 upd_table_count := upd_table_count + 1;
4620 END IF;
4621 END LOOP;
4622 IF (l_gtlv_tbl_ins_in.COUNT > 0 )
4623 THEN
4624 -- Call the TAPI Procedcure to perform the actual inserts
4625 okl_gtl_pvt.insert_row(
4626 p_api_version => l_api_version
4627 ,p_init_msg_list => p_init_msg_list
4628 ,x_return_status => l_return_status
4629 ,x_msg_count => l_msg_count
4630 ,x_msg_data => l_msg_data
4631 ,p_gtlv_tbl => l_gtlv_tbl_ins_in
4632 ,x_gtlv_tbl => l_gtlv_tbl_ins_out
4633 );
4634 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4635 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4636 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4637 RAISE OKL_API.G_EXCEPTION_ERROR;
4638 END IF;
4639 END IF;
4640 IF (l_gtlv_tbl_upd_in.count > 0 )
4641 THEN
4642 -- Call the TAPI Procedcure to perform the actual updates
4643 okl_gtl_pvt.update_row(
4644 p_api_version => l_api_version
4645 ,p_init_msg_list => p_init_msg_list
4646 ,x_return_status => l_return_status
4647 ,x_msg_count => l_msg_count
4648 ,x_msg_data => l_msg_data
4649 ,p_gtlv_tbl => l_gtlv_tbl_upd_in
4650 ,x_gtlv_tbl => l_gtlv_tbl_upd_out
4651 );
4652 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4653 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4654 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4655 RAISE OKL_API.G_EXCEPTION_ERROR;
4656 END IF;
4657 END IF;
4658 END IF;
4659 /*
4660
4661 -- Modified by RGOOTY
4662 -- Bug 4054596: Issue No. 5: Start
4663 l_found := OKL_API.G_FALSE;
4664 l_gtt_id := p_gtt_id;--p_gtlv_tbl( p_gtlv_tbl.FIRST ).gtt_id;
4665 l_pri_sty_id := p_pri_sty_id; --p_gtlv_tbl( p_gtlv_tbl.FIRST).primary_sty_id;
4666 l_purp_list := '';
4667 FOR okl_dep_dup_rec In okl_dep_purpose_dup_csr( l_gtt_id, l_pri_sty_id )
4668 LOOP
4669 l_found := OKL_API.G_TRUE;
4670 l_purp_list := l_purp_list || okl_dep_dup_rec.purpose_meaning || ',';
4671 END LOOP;
4672 IF (l_found = Okl_Api.G_TRUE) THEN
4673 l_purp_list := RTRIM( l_purp_list,',' );
4674 Okl_Api.SET_MESSAGE( p_app_name => g_app_name,
4675 p_msg_name => 'OKL_ST_SGT_DUP_DEP_PURPOSE',
4676 p_token1 => 'TEXT',
4677 p_token1_value => l_purp_list);
4678 l_return_status := Okl_Api.G_RET_STS_ERROR;
4679 RAISE Okl_Api.G_EXCEPTION_ERROR;
4680 END IF;
4681 -- Bug 4054596: Issue No. 5: End
4682
4683 IF( p_gtt_id IS NOT NULL AND p_gtt_id <> OKL_API.G_MISS_NUM AND
4684 p_pri_sty_id IS NOT NULL AND p_pri_sty_id <> OKL_API.G_MISS_NUM )
4685 THEN
4686 FOR tmpt_det_rec IN tmpt_det_csr( p_gtt_id )
4687 LOOP
4688 l_deal_type := tmpt_det_rec.deal_type;
4689 l_tmpt_status := tmpt_det_rec.tmpt_status;
4690 l_sgt_set_id := tmpt_det_rec.gts_id;
4691 END LOOP;
4692
4693 -- Modified by RGOOTY
4694 -- Bug 4094361: Start
4695 -- Need to invalidate all the products which use this Active SGT.
4696 IF ( l_tmpt_status = G_STATUS_ACTIVE )
4697 THEN
4698
4699 -- Added by bkatraga on 05-Apr-2005
4700 -- Validating the SGT
4701 validate_template(
4702 p_api_version => l_api_version
4703 ,p_init_msg_list => p_init_msg_list
4704 ,x_return_status => l_return_status
4705 ,x_msg_count => l_msg_count
4706 ,x_msg_data => l_msg_data
4707 ,p_gtt_id => p_gtt_id
4708 ,x_error_msgs_tbl => l_error_msgs_tbl
4709 ,x_return_tmpt_status => l_return_tmpt_status
4710 ,p_during_upd_flag => 'T'
4711 );
4712 IF ( l_error_msgs_tbl.count > 0 )
4713 THEN
4714 x_return_status := Okl_Api.G_RET_STS_ERROR;
4715 RAISE Okl_Api.G_EXCEPTION_ERROR;
4716 END If;
4717 -- end changes by bkatraga
4718
4719 FOR pdt_rec IN pdt_for_active_sgt_csr( l_sgt_set_id )
4720 LOOP
4721 OKL_SETUPPRODUCTS_PVT.update_product_status(
4722 p_api_version => p_api_version,
4723 p_init_msg_list => p_init_msg_list,
4724 x_return_status => l_return_Status,
4725 x_msg_count => x_msg_count,
4726 x_msg_data => x_msg_data,
4727 p_pdt_status => OKL_SETUPPRODUCTS_PVT.G_PDT_STS_INVALID,
4728 p_pdt_id => pdt_rec.pdt_id );
4729 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4730 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4731 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4732 RAISE OKL_API.G_EXCEPTION_ERROR;
4733 END IF;
4734 END LOOP;
4735 -- Bug 4094361: End
4736 END IF;
4737 l_pri_strm_purpose := '';
4738 FOR tmpt_pri_strm_det_rec IN tmpt_pri_strm_det_csr( p_gtt_id,p_pri_sty_id)
4739 LOOP
4740 l_pri_strm_purpose := tmpt_pri_strm_det_rec.pri_strm_purpose;
4741 END LOOP;
4742
4743 IF ( l_tmpt_status = G_STATUS_ACTIVE AND l_pri_strm_purpose = 'FEE_PAYMENT' )
4744 THEN
4745 validate_mandatory_dep(
4746 p_api_version => p_api_version
4747 ,p_init_msg_list => p_init_msg_list
4748 ,x_return_status => l_return_status
4749 ,x_msg_count => x_msg_count
4750 ,x_msg_data => x_msg_data
4751 ,p_gtt_id => p_gtt_id
4752 ,p_pri_sty_id => p_pri_sty_id
4753 ,x_missing_deps => l_missing_deps
4754 ,x_show_warn_flag => l_show_warn_flag
4755 ,p_deal_type => l_deal_type
4756 );
4757 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4758 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4759 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4760 RAISE OKL_API.G_EXCEPTION_ERROR;
4761 END IF;
4762 x_missing_deps := l_missing_deps;
4763 x_show_warn_flag := l_show_warn_flag;
4764
4765 END IF;
4766 END IF;
4767 */
4768 x_return_status := l_return_status;
4769 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4770 EXCEPTION
4771 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4772 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4773 (l_api_name,
4774 G_PKG_NAME,
4775 'OKL_API.G_RET_STS_ERROR',
4776 x_msg_count,
4777 x_msg_data,
4778 '_PVT');
4779 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4780 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4781 ( l_api_name,
4782 G_PKG_NAME,
4783 'OKL_API.G_RET_STS_UNEXP_ERROR',
4784 x_msg_count,
4785 x_msg_data,
4786 '_PVT');
4787 WHEN OTHERS THEN
4788 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4789 ( l_api_name,
4790 G_PKG_NAME,
4791 'OTHERS',
4792 x_msg_count,
4793 x_msg_data,
4794 '_PVT');
4795 END;
4796
4797 ------------------------------------------------------------------------------------
4798 -- This API does the following:
4799 -- 1/ Updates the SGT Set, Version, Pricing Params Info and Primary Stream Types
4800 -- 2/ Deletes the Dependents, if needed
4801 -- 3/ Updates/Inserts the Dependants for all Primary
4802 -- 4/ If Template is in Active Status, Validates it, if any errors found, rollbacks
4803 -- the entire stuff
4804 -- 5/ If no validation errors found, proceeds checking the Warnings,
4805 -- If errors found pushes the warning message onto the FND Stack,
4806 -- but doesnot rollback
4807 -- 6/ Updates all the products which use the current SGT, to INVALID status
4808 ------------------------------------------------------------------------------------
4809 PROCEDURE update_pri_dep_of_sgt(
4810 p_api_version IN NUMBER
4811 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4812 ,x_return_status OUT NOCOPY VARCHAR2
4813 ,x_msg_count OUT NOCOPY NUMBER
4814 ,x_msg_data OUT NOCOPY VARCHAR2
4815 ,p_gtsv_rec IN gtsv_rec_type
4816 ,p_gttv_rec IN gttv_rec_type
4817 ,p_gtpv_tbl IN gtpv_tbl_type
4818 ,p_pri_gtlv_tbl IN gtlv_tbl_type
4819 ,p_del_dep_gtlv_tbl IN gtlv_tbl_type
4820 ,p_ins_dep_gtlv_tbl IN gtlv_tbl_type
4821 ,x_gttv_rec OUT NOCOPY gttv_rec_type
4822 ,x_pri_purpose_list OUT NOCOPY VARCHAR2)
4823 IS
4824 -- Cursor to fetch the Stream Generation Template Set ID
4825 CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
4826 IS
4827 SELECT GTT.GTS_ID
4828 FROM OKL_ST_GEN_TEMPLATES GTT
4829 WHERE GTT.ID = p_gtt_id;
4830 -- Cursor to get the Dependent Stream Purpose Meaning which has been included more than once
4831 -- for a particular primary Stream
4832 CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE )
4833 IS
4834 SELECT STYP.CODE PRI_STRM_TYPE_NAME,
4835 -- gboomina Bug 4874272 - Added - Start
4836 STYP.STREAM_TYPE_PURPOSE PRI_STRM_TYPE_PURPOSE,
4837 -- gboomina Bug 4874272 - Added - End
4838 STY.STREAM_TYPE_PURPOSE DEP_PURPOSE_CODE,
4839 ( SELECT MEANING
4840 FROM FND_LOOKUPS
4841 WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
4842 AND LOOKUP_CODE = STY.STREAM_TYPE_PURPOSE
4843 ) DEP_PURPOSE_MEANING,
4844 COUNT( STY.STREAM_TYPE_PURPOSE) streams_count
4845 FROM OKL_ST_GEN_TMPT_LNS GTL
4846 ,OKL_ST_GEN_TMPT_LNS GTLP
4847 ,OKL_STRM_TYPE_B STY
4848 ,OKL_STRM_TYPE_B STYP
4849 WHERE GTL.DEPENDENT_STY_ID = STY.ID
4850 AND GTLP.PRIMARY_STY_ID = STYP.ID
4851 AND GTL.PRIMARY_YN = 'N'
4852 AND GTLP.PRIMARY_YN = 'Y'
4853 AND GTLP.primary_sty_id = GTL.primary_sty_id
4854 AND GTL.GTT_ID = p_gtt_id
4855 AND GTLP.GTT_ID = p_gtt_id
4856 -- gboomina Bug 4874272 - Added STREAM_TYPE_PURPOSE in Group by - Start
4857 GROUP BY STYP.STREAM_TYPE_PURPOSE, STYP.CODE, STY.STREAM_TYPE_PURPOSE
4858 -- gboomina Bug 4874272 - Added STREAM_TYPE_PURPOSE in Group by - End
4859 HAVING COUNT( STY.STREAM_TYPE_PURPOSE) > 1;
4860 -- Cursor to fetch all the products which uses this SGT
4861 CURSOR pdt_for_active_sgt_csr( p_gts_id OKL_ST_GEN_TMPT_SETS.ID%TYPE )
4862 IS
4863 SELECT PDT.ID PDT_ID
4864 ,PRODUCT_STATUS_CODE
4865 FROM OKL_PRODUCTS PDT,
4866 OKL_AE_TMPT_SETS ATS,
4867 OKL_ST_GEN_TMPT_SETS SGT
4868 WHERE PDT.AES_ID = ATS.ID
4869 AND ATS.GTS_ID = SGT.ID
4870 AND SGT.ID = p_gts_id;
4871
4872 l_api_name CONSTANT VARCHAR2(40) := 'update_pri_dep_of_sgt';
4873 l_api_version CONSTANT NUMBER := 1.0;
4874 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4875 -- Local Variables Declaration
4876 l_missing_deps VARCHAR2(10000);
4877 l_show_warn_flag VARCHAR2(30);
4878 l_error_msgs_tbl error_msgs_tbl_type;
4879 l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4880 l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
4881 l_pri_purpose_list VARCHAR2(10000);
4882 l_found VARCHAR2(30);
4883 l_msg VARCHAR2(10000);
4884 l_pri_stream_name OKL_STRM_TYPE_B.CODE%TYPE;
4885 l_dep_purp_list VARCHAR2(10000);
4886 BEGIN
4887 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4888 l_return_status := OKL_API.START_ACTIVITY( l_api_name
4889 ,g_pkg_name
4890 ,p_init_msg_list
4891 ,l_api_version
4892 ,p_api_version
4893 ,'_PVT'
4894 ,x_return_status);
4895 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4896 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4897 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4898 RAISE OKL_API.G_EXCEPTION_ERROR;
4899 END IF;
4900 -- Call the update api which takes care of updating the
4901 -- SGT Set, Version, Pricing Params and Primary Lines
4902 -- This API should not validate even the SGT status is active or not !
4903 IF p_pri_gtlv_tbl IS NOT NULL AND p_pri_gtlv_tbl.COUNT > 0 THEN
4904 update_strm_gen_template(
4905 p_api_version => p_api_version,
4906 p_init_msg_list => p_init_msg_list,
4907 x_return_status => l_return_status,
4908 x_msg_count => x_msg_count,
4909 x_msg_data => x_msg_data,
4910 p_gtsv_rec => p_gtsv_rec,
4911 p_gttv_rec => p_gttv_rec,
4912 p_gtpv_tbl => p_gtpv_tbl,
4913 p_gtlv_tbl => p_pri_gtlv_tbl,
4914 x_gttv_rec => x_gttv_rec);
4915 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4916 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4917 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4918 RAISE OKL_API.G_EXCEPTION_ERROR;
4919 END IF;
4920 END IF;
4921 -- Call the API which deletes the Dependents
4922 -- Dependents will be deleted only if the SGT was not in Active Status
4923 IF p_del_dep_gtlv_tbl IS NOT NULL AND p_del_dep_gtlv_tbl.COUNT > 0
4924 THEN
4925 delete_dep_tmpt_lns(
4926 p_api_version => p_api_version,
4927 p_init_msg_list => p_init_msg_list,
4928 x_return_status => l_return_status,
4929 x_msg_count => x_msg_count,
4930 x_msg_data => x_msg_data,
4931 p_gtlv_tbl => p_del_dep_gtlv_tbl);
4932 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4934 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4935 RAISE OKL_API.G_EXCEPTION_ERROR;
4936 END IF;
4937 END IF;
4938 -- Call the update API which takes care of updating/inserting the
4939 -- dependent streams of the Stream Generation Template
4940 -- This API should not validate even the SGT status is active or not !
4941 IF p_ins_dep_gtlv_tbl IS NOT NULL AND p_ins_dep_gtlv_tbl.COUNT > 0
4942 THEN
4943 update_dep_strms(
4944 p_api_version => p_api_version,
4945 p_init_msg_list => p_init_msg_list,
4946 x_return_status => l_return_status,
4947 x_msg_count => x_msg_count,
4948 x_msg_data => x_msg_data,
4949 p_gtt_id => x_gttv_rec.id, -- SGT Version ID
4950 p_pri_sty_id => NULL, -- Not mandatory
4951 p_gtlv_tbl => p_ins_dep_gtlv_tbl,
4952 x_missing_deps => l_missing_deps,
4953 x_show_warn_flag => l_show_warn_flag);
4954 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4955 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4956 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4957 RAISE OKL_API.G_EXCEPTION_ERROR;
4958 END IF;
4959 END IF;
4960 -- Imposing the following Validation:
4961 -- RULE:
4962 -- For a particular Primary Stream, we shouldnot have
4963 -- two dependant streams with same purpose.
4964 -- Loop through all the Primary Stream types of this SGT
4965 l_found := OKL_API.G_FALSE;
4966 l_pri_stream_name := NULL;
4967 l_dep_purp_list := ' ';
4968 FOR t_rec IN okl_dep_purpose_dup_csr( x_gttv_rec.id )
4969 LOOP
4970 l_found := OKL_API.G_TRUE;
4971 IF l_pri_stream_name IS NULL
4972 THEN
4973 l_pri_stream_name := t_rec.pri_strm_type_name;
4974 l_dep_purp_list := t_rec.dep_purpose_meaning;
4975 END IF;
4976 IF l_pri_stream_name <> t_rec.pri_strm_type_name
4977 THEN
4978 -- Push the messsage into the FND stack
4979 put_messages_in_table(
4980 p_msg_name => 'OKL_ST_SGT_DUP_DEPENDANTS',
4981 p_during_upd_flag => 'T',
4982 x_msg_out => l_msg,
4983 p_token1 => 'PRISTRMCODE',
4984 p_value1 => l_pri_stream_name,
4985 p_token2 => 'DEPPURPOSELIST',
4986 p_value2 => l_dep_purp_list);
4987 -- Initialize the pri stream name and the dep purpose list ..
4988 l_pri_stream_name := t_rec.pri_strm_type_name;
4989 l_dep_purp_list := t_rec.dep_purpose_meaning;
4990 ELSIF l_dep_purp_list <> t_rec.dep_purpose_meaning
4991 THEN
4992 l_dep_purp_list := l_dep_purp_list || ',' || t_rec.dep_purpose_meaning;
4993 END IF;
4994 END LOOP;
4995 IF l_found = Okl_Api.G_TRUE
4996 THEN
4997 -- Push the last messsage into the FND stack
4998 put_messages_in_table(
4999 p_msg_name => 'OKL_ST_SGT_DUP_DEPENDANTS',
5000 p_during_upd_flag => 'T',
5001 x_msg_out => l_msg,
5002 p_token1 => 'PRISTRMCODE',
5003 p_value1 => l_pri_stream_name,
5004 p_token2 => 'DEPPURPOSELIST',
5005 p_value2 => l_dep_purp_list);
5006 l_return_status := Okl_Api.G_RET_STS_ERROR;
5007 RAISE Okl_Api.G_EXCEPTION_ERROR;
5008 END IF;
5009 -- Insertion/Updation/Deletion has been done successful.
5010 -- Now need to check for errors and warnings.
5011 IF x_gttv_rec.tmpt_status = G_STATUS_ACTIVE
5012 THEN
5013 -- Call the Validate API to check for errors only if the SGT is in Active Status
5014 validate_template(
5015 p_api_version => p_api_version,
5016 p_init_msg_list => p_init_msg_list,
5017 x_return_status => l_return_status,
5018 x_msg_count => x_msg_count,
5019 x_msg_data => x_msg_data,
5020 p_gtt_id => x_gttv_rec.id,
5021 x_error_msgs_tbl => l_error_msgs_tbl,
5022 x_return_tmpt_status => l_return_tmpt_status,
5023 p_during_upd_flag => 'T' ); -- This Wrapper API will be called only in Update Mode
5024 -- Check whether the Validate API has been ran successfull or not ?
5025 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5026 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5027 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5028 RAISE OKL_API.G_EXCEPTION_ERROR;
5029 END IF;
5030 -- Now check whether there are no validation errors or not?
5031 IF l_error_msgs_tbl.count > 0
5032 THEN
5033 x_return_status := Okl_Api.G_RET_STS_ERROR;
5034 RAISE Okl_Api.G_EXCEPTION_ERROR;
5035 END If;
5036 -- Now if there is no Validation Error, check for the Warnings,
5037 -- but the code shouldnot error out in this case.
5038 validate_for_warnings(
5039 p_api_version => p_api_version,
5040 p_init_msg_list => p_init_msg_list,
5041 x_return_status => l_return_status,
5042 x_msg_count => x_msg_count,
5043 x_msg_data => x_msg_data,
5044 p_gtt_id => x_gttv_rec.id,
5045 x_wrn_msgs_tbl => l_error_msgs_tbl,
5046 p_during_upd_flag => 'F', -- Dont push any warnings into the Stack
5047 x_pri_purpose_list => l_pri_purpose_list );
5048 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5049 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5050 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5051 RAISE OKL_API.G_EXCEPTION_ERROR;
5052 END IF;
5053 -- Whenever the SGT is updated successfull and if its in ACTIVE status
5054 -- the corresponding products should be made to INVALID status
5055 FOR gts_id_rec IN fetch_gts_id_csr( x_gttv_rec.id )
5056 LOOP
5057 l_sgt_set_id := gts_id_rec.gts_id;
5058 END LOOP;
5059 -- Need to invalidate all the products which use this SGT.
5060 FOR pdt_rec IN pdt_for_active_sgt_csr( l_sgt_set_id )
5061 LOOP
5062 OKL_SETUPPRODUCTS_PVT.update_product_status(
5063 p_api_version => p_api_version,
5064 p_init_msg_list => p_init_msg_list,
5065 x_return_status => l_return_Status,
5066 x_msg_count => x_msg_count,
5067 x_msg_data => x_msg_data,
5068 p_pdt_status => OKL_SETUPPRODUCTS_PVT.G_PDT_STS_INVALID,
5069 p_pdt_id => pdt_rec.pdt_id );
5070 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5071 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5072 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5073 RAISE OKL_API.G_EXCEPTION_ERROR;
5074 END IF;
5075 END LOOP;
5076 END IF; -- IF x_gttv_rec.tmpt_status = G_ACTIVE
5077 x_pri_purpose_list := l_pri_purpose_list;
5078 x_return_status := l_return_status;
5079 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5080 EXCEPTION
5081 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5082 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5083 (l_api_name,
5084 G_PKG_NAME,
5085 'OKL_API.G_RET_STS_ERROR',
5086 x_msg_count,
5087 x_msg_data,
5088 '_PVT');
5089 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5090 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5091 ( l_api_name,
5092 G_PKG_NAME,
5093 'OKL_API.G_RET_STS_UNEXP_ERROR',
5094 x_msg_count,
5095 x_msg_data,
5096 '_PVT');
5097 WHEN OTHERS THEN
5098 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5099 ( l_api_name,
5100 G_PKG_NAME,
5101 'OTHERS',
5102 x_msg_count,
5103 x_msg_data,
5104 '_PVT');
5105 END update_pri_dep_of_sgt;
5106
5107 End okl_strm_gen_template_pvt;