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