[Home] [Help]
PACKAGE BODY: APPS.OKL_ECC_VALUES_PVT
Source
1 PACKAGE BODY okl_ecc_values_pvt AS
2 /* $Header: OKLRECVB.pls 120.1 2005/10/30 04:58:59 appldev noship $ */
3
4 --------------------------------------------------------------------------------
5 --PACKAGE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
9 g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
10
11
12 FUNCTION validate_effective_dates(p_ecl_tbl IN okl_ecl_tbl
13 ,p_source_eff_from IN date
14 ,p_source_eff_to IN date) RETURN varchar2 IS
15
16 CURSOR l_crit_cat_name_csr(p_crit_cat_def_id IN number) IS
17 SELECT crit_cat_name
18 ,ecc_ac_flag
19 FROM okl_fe_crit_cat_def_v
20 WHERE crit_cat_def_id = p_crit_cat_def_id;
21 i number;
22 j number;
23 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_effective_dates';
24 l_crit_cat varchar2(40);
25 l_crit_cat_name okl_fe_crit_cat_def_v.crit_cat_name%TYPE;
26 l_ecc_ac_flag okl_fe_crit_cat_def_b.ecc_ac_flag%TYPE;
27 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.validate_effective_dates';
28 l_debug_enabled varchar2(10);
29 is_debug_procedure_on boolean;
30 is_debug_statement_on boolean;
31
32 BEGIN
33 l_debug_enabled := okl_debug_pub.check_log_enabled;
34 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
35 ,fnd_log.level_procedure);
36
37 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
38 okl_debug_pub.log_debug(fnd_log.level_procedure
39 ,l_module
40 ,'begin debug OKLRECCB.pls call validate_effective_dates');
41 END IF;
42
43 -- check for logging on STATEMENT level
44
45 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
46 ,fnd_log.level_statement);
47 i := p_ecl_tbl.FIRST;
48
49 WHILE(i <= p_ecl_tbl.LAST) LOOP
50
51 -- eff from and to dates of eligibility criteria should lie beween eff from and to dates of source
52
53 IF (p_ecl_tbl(i).effective_from_date NOT BETWEEN p_source_eff_from AND nvl(p_source_eff_to
54 ,to_date('01-01-9999'
55 ,'dd-mm-yyyy')))
56 OR (nvl(p_ecl_tbl(i).effective_to_date
57 ,to_date('01-01-9999', 'dd-mm-yyyy')) NOT BETWEEN p_source_eff_from AND nvl(p_source_eff_to
58 ,to_date('01-01-9999'
59 ,'dd-mm-yyyy'))) THEN
60 OPEN l_crit_cat_name_csr(p_ecl_tbl(i).crit_cat_def_id);
61 FETCH l_crit_cat_name_csr INTO l_crit_cat_name
62 ,l_ecc_ac_flag ;
63 CLOSE l_crit_cat_name_csr;
64 IF l_ecc_ac_flag = 'ECC' THEN
65 l_crit_cat := 'Eligibility Criteria Category';
66 ELSE
67 l_crit_cat := 'Adjustment Category';
68 END IF;
69 okl_api.set_message(p_app_name => g_app_name
70 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_DATES'
71 ,p_token1 => 'CRIT_CAT'
72 ,p_token1_value => l_crit_cat
73 ,p_token2 => 'NAME'
74 ,p_token2_value => l_crit_cat_name);
75 RAISE okl_api.g_exception_error;
76 END IF;
77 j := i + 1;
78 WHILE(j <= p_ecl_tbl.LAST) LOOP
79
80 IF (p_ecl_tbl(i).crit_cat_def_id = p_ecl_tbl(j).crit_cat_def_id) THEN
81
82 -- both finite
83
84 IF (p_ecl_tbl(i).effective_to_date IS NOT NULL AND p_ecl_tbl(j).effective_to_date IS NOT NULL) THEN
85 IF (p_ecl_tbl(j).effective_from_date BETWEEN p_ecl_tbl(i).effective_from_date AND p_ecl_tbl(i).effective_to_date) THEN
86 OPEN l_crit_cat_name_csr(p_ecl_tbl(i).crit_cat_def_id);
87 FETCH l_crit_cat_name_csr INTO l_crit_cat_name
88 ,l_ecc_ac_flag ;
89 CLOSE l_crit_cat_name_csr;
90 IF l_ecc_ac_flag = 'ECC' THEN
91 l_crit_cat := 'Eligibility Criteria Category';
92 ELSE
93 l_crit_cat := 'Adjustment Category';
94 END IF;
95 okl_api.set_message(p_app_name => g_app_name
96 ,p_msg_name => 'OKL_CRITCAT_OVERLAPPING_DATES'
97 ,p_token1 => 'CRIT_CAT'
98 ,p_token1_value => l_crit_cat
99 ,p_token2 => 'NAME'
100 ,p_token2_value => l_crit_cat_name);
101 RAISE okl_api.g_exception_error;
102 END IF;
103 IF (p_ecl_tbl(j).effective_to_date BETWEEN p_ecl_tbl(i).effective_from_date AND p_ecl_tbl(i).effective_to_date) THEN
104 OPEN l_crit_cat_name_csr(p_ecl_tbl(i).crit_cat_def_id);
105 FETCH l_crit_cat_name_csr INTO l_crit_cat_name
106 ,l_ecc_ac_flag ;
107 CLOSE l_crit_cat_name_csr;
108 IF l_ecc_ac_flag = 'ECC' THEN
109 l_crit_cat := 'Eligibility Criteria Category';
110 ELSE
111 l_crit_cat := 'Adjustment Category';
112 END IF;
113 okl_api.set_message(p_app_name => g_app_name
114 ,p_msg_name => 'OKL_CRITCAT_OVERLAPPING_DATES'
115 ,p_token1 => 'CRIT_CAT'
116 ,p_token1_value => l_crit_cat
117 ,p_token2 => 'NAME'
118 ,p_token2_value => l_crit_cat_name);
119 RAISE okl_api.g_exception_error;
120 END IF;
121 END IF;
122
123 -- Both Open End
124
125 IF (p_ecl_tbl(i).effective_to_date IS NULL AND p_ecl_tbl(j).effective_to_date IS NULL) THEN
126 OPEN l_crit_cat_name_csr(p_ecl_tbl(i).crit_cat_def_id);
127 FETCH l_crit_cat_name_csr INTO l_crit_cat_name
128 ,l_ecc_ac_flag ;
129 CLOSE l_crit_cat_name_csr;
130 IF l_ecc_ac_flag = 'ECC' THEN
131 l_crit_cat := 'Eligibility Criteria Category';
132 ELSE
133 l_crit_cat := 'Adjustment Category';
134 END IF;
135 okl_api.set_message(p_app_name => g_app_name
136 ,p_msg_name => 'OKL_CRITCAT_OVERLAPPING_DATES'
137 ,p_token1 => 'CRIT_CAT'
138 ,p_token1_value => l_crit_cat
139 ,p_token2 => 'NAME'
140 ,p_token2_value => l_crit_cat_name);
141 RAISE okl_api.g_exception_error;
142 END IF;
143
144 -- p_ecl_tbl(i) is open end and p_ecl_tbl(j) is finite
145
146 IF (p_ecl_tbl(i).effective_to_date IS NULL AND p_ecl_tbl(j).effective_to_date IS NOT NULL) THEN
147 IF (p_ecl_tbl(j).effective_to_date >= p_ecl_tbl(i).effective_from_date) THEN
148 OPEN l_crit_cat_name_csr(p_ecl_tbl(i).crit_cat_def_id);
149 FETCH l_crit_cat_name_csr INTO l_crit_cat_name
150 ,l_ecc_ac_flag ;
151 CLOSE l_crit_cat_name_csr;
152 IF l_ecc_ac_flag = 'ECC' THEN
153 l_crit_cat := 'Eligibility Criteria Category';
154 ELSE
155 l_crit_cat := 'Adjustment Category';
156 END IF;
157 okl_api.set_message(p_app_name => g_app_name
158 ,p_msg_name => 'OKL_CRITCAT_OVERLAPPING_DATES'
159 ,p_token1 => 'CRIT_CAT'
160 ,p_token1_value => l_crit_cat
161 ,p_token2 => 'NAME'
162 ,p_token2_value => l_crit_cat_name);
163 RAISE okl_api.g_exception_error;
164 END IF;
165 END IF;
166
167 -- p_ecl_tbl(i) is finite end and p_ecl_tbl(j) is open end
168
169 IF (p_ecl_tbl(i).effective_to_date IS NOT NULL AND p_ecl_tbl(j).effective_to_date IS NULL) THEN
170 IF (p_ecl_tbl(j).effective_from_date <= p_ecl_tbl(i).effective_to_date) THEN
171 OPEN l_crit_cat_name_csr(p_ecl_tbl(i).crit_cat_def_id);
172 FETCH l_crit_cat_name_csr INTO l_crit_cat_name
173 ,l_ecc_ac_flag ;
174 CLOSE l_crit_cat_name_csr;
175 IF l_ecc_ac_flag = 'ECC' THEN
176 l_crit_cat := 'Eligibility Criteria Category';
177 ELSE
178 l_crit_cat := 'Adjustment Category';
179 END IF;
180 okl_api.set_message(p_app_name => g_app_name
181 ,p_msg_name => 'OKL_CRITCAT_OVERLAPPING_DATES'
182 ,p_token1 => 'CRIT_CAT'
183 ,p_token1_value => l_crit_cat
184 ,p_token2 => 'NAME'
185 ,p_token2_value => l_crit_cat_name);
186 RAISE okl_api.g_exception_error;
187 END IF;
188 END IF;
189 END IF;
190 j := j + 1;
191 END LOOP;
192 i := i + 1;
193 END LOOP;
194
195 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
196 okl_debug_pub.log_debug(fnd_log.level_procedure
197 ,l_module
198 ,'end debug OKLRECVB.pls.pls call validate_effective_dates');
199 END IF;
200 RETURN okl_api.g_ret_sts_success;
201 EXCEPTION
202 WHEN okl_api.g_exception_error THEN
203 RETURN okl_api.g_ret_sts_error;
204 WHEN okl_api.g_exception_unexpected_error THEN
205 RETURN okl_api.g_ret_sts_unexp_error;
206 WHEN OTHERS THEN
207 okl_api.set_message(p_app_name => g_app_name
208 ,p_msg_name => g_db_error
209 ,p_token1 => g_prog_name_token
210 ,p_token1_value => l_api_name
211 ,p_token2 => g_sqlcode_token
212 ,p_token2_value => sqlcode
213 ,p_token3 => g_sqlerrm_token
214 ,p_token3_value => sqlerrm);
215 RETURN okl_api.g_ret_sts_unexp_error;
216 END validate_effective_dates;
217
218 /**
219 This procedure deletes the Eligibility criteria attached to a particular
220 source object identified by p_source_id and p_source_type.
221 **/
222
223 PROCEDURE delete_eligibility_criteria(p_api_version IN number
224 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
225 ,x_return_status OUT NOCOPY varchar2
226 ,x_msg_count OUT NOCOPY number
227 ,x_msg_data OUT NOCOPY varchar2
228 ,p_source_id IN number
229 ,p_source_type IN varchar2) IS
230 l_source_id number;
231 lp_ech_rec okl_ech_rec;
232 lx_ech_rec okl_ech_rec;
233 lp_ecl_tbl okl_ecl_tbl;
234 lx_ecl_tbl okl_ecl_tbl;
235 lp_ecv_tbl okl_ecv_tbl;
236 lx_ecv_tbl okl_ecv_tbl;
237
238 CURSOR get_ech_rec IS
239 SELECT criteria_set_id
240 FROM okl_fe_criteria_set
241 WHERE source_id = p_source_id AND source_object_code = p_source_type;
242
243 CURSOR get_ecl_tbl(p_criteria_set_id IN number) IS
244 SELECT criteria_id
245 FROM okl_fe_criteria
246 WHERE criteria_set_id = p_criteria_set_id;
247
248 CURSOR get_ecv_tbl(p_criteria_id IN number) IS
249 SELECT criterion_value_id
250 FROM okl_fe_criterion_values
251 WHERE criteria_id = p_criteria_id;
252 i number;
253 j number;
254 l_api_name CONSTANT varchar2(30) := 'DELETE_ELIG_CRITERIA';
255 l_api_version CONSTANT number := 1.0;
256 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
257 l_ec_found boolean;
258 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.delete_eligibility_criteria';
259 l_debug_enabled varchar2(10);
260 is_debug_procedure_on boolean;
261 is_debug_statement_on boolean;
262
263 BEGIN
264 l_debug_enabled := okl_debug_pub.check_log_enabled;
265 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
266 ,fnd_log.level_procedure);
267
268 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
269 okl_debug_pub.log_debug(fnd_log.level_procedure
270 ,l_module
271 ,'begin debug OKLRECCB.pls call delete_eligibility_criteria');
272 END IF;
273
274 -- check for logging on STATEMENT level
275
276 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
277 ,fnd_log.level_statement);
278
279 -- call START_ACTIVITY to create savepoint, check compatibility
280 -- and initialize message list
281
282 l_return_status := okl_api.start_activity(p_api_name => l_api_name
283 ,p_pkg_name => g_pkg_name
284 ,p_init_msg_list => p_init_msg_list
285 ,l_api_version => l_api_version
286 ,p_api_version => p_api_version
287 ,p_api_type => g_api_type
288 ,x_return_status => x_return_status);
289
290 -- check if activity started successfully
291
292 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
293 RAISE okl_api.g_exception_unexpected_error;
294 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
295 RAISE okl_api.g_exception_error;
296 END IF;
297 l_source_id := p_source_id;
298 OPEN get_ech_rec;
299 FETCH get_ech_rec INTO lp_ech_rec.criteria_set_id ;
300 l_ec_found := get_ech_rec%FOUND;
301 CLOSE get_ech_rec;
302 i := 1;
303 j := 1;
304
305 IF l_ec_found THEN
306
307 FOR ecl_rec IN get_ecl_tbl(lp_ech_rec.criteria_set_id) LOOP
308 lp_ecl_tbl(i).criteria_id := ecl_rec.criteria_id;
309 FOR ecv_rec IN get_ecv_tbl(lp_ecl_tbl(i).criteria_id) LOOP
310 lp_ecv_tbl(i).criterion_value_id := ecv_rec.criterion_value_id;
311 j := j + 1;
312 END LOOP;
313 i := i + 1;
314 END LOOP;
315
316 --delete header
317
318 okl_ech_pvt.delete_row(p_api_version => p_api_version
319 ,p_init_msg_list => okl_api.g_false
320 ,x_return_status => l_return_status
321 ,x_msg_count => x_msg_count
322 ,x_msg_data => x_msg_data
323 ,p_ech_rec => lp_ech_rec);
324
325 -- write to log
326
327 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
328 okl_debug_pub.log_debug(fnd_log.level_statement
329 ,l_module
330 ,'Procedure okl_ech_pvt.delete_row returned with status ' ||
331 l_return_status);
332 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
333 IF l_return_status = okl_api.g_ret_sts_error THEN
334 RAISE okl_api.g_exception_error;
335 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
336 RAISE okl_api.g_exception_unexpected_error;
337 END IF;
338 IF lp_ecl_tbl.COUNT > 0 THEN
339
340 --delete lines
341
342 okl_ecl_pvt.delete_row(p_api_version => p_api_version
343 ,p_init_msg_list => okl_api.g_false
344 ,x_return_status => l_return_status
345 ,x_msg_count => x_msg_count
346 ,x_msg_data => x_msg_data
347 ,p_ecl_tbl => lp_ecl_tbl);
348
349 -- write to log
350
351 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
352 okl_debug_pub.log_debug(fnd_log.level_statement
353 ,l_module
354 ,'Procedure okl_ecl_pvt.delete_row returned with status ' ||
355 l_return_status);
356 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
357 IF l_return_status = okl_api.g_ret_sts_error THEN
358 RAISE okl_api.g_exception_error;
359 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
360 RAISE okl_api.g_exception_unexpected_error;
361 END IF;
362 IF lp_ecv_tbl.COUNT > 0 THEN
363
364 --delete line values
365
366 okl_ecv_pvt.delete_row(p_api_version => p_api_version
367 ,p_init_msg_list => okl_api.g_false
368 ,x_return_status => l_return_status
369 ,x_msg_count => x_msg_count
370 ,x_msg_data => x_msg_data
371 ,p_ecv_tbl => lp_ecv_tbl);
372
373 -- write to log
374
375 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
376 okl_debug_pub.log_debug(fnd_log.level_statement
377 ,l_module
378 ,'Procedure okl_ecv_pvt.delete_row returned with status ' ||
379 l_return_status);
380 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
381 IF l_return_status = okl_api.g_ret_sts_error THEN
382 RAISE okl_api.g_exception_error;
383 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
384 RAISE okl_api.g_exception_unexpected_error;
385 END IF;
386 END IF;
387 END IF;
388 END IF;
389 x_return_status := l_return_status;
390
391 okl_api.end_activity(x_msg_count => x_msg_count
392 ,x_msg_data => x_msg_data);
393
394 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
395 okl_debug_pub.log_debug(fnd_log.level_procedure
396 ,l_module
397 ,'end debug OKLRECVB.pls.pls call delete_eligibility_criteria');
398 END IF;
399
400 EXCEPTION
401 WHEN okl_api.g_exception_error THEN
402 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
403 ,p_pkg_name => g_pkg_name
404 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
405 ,x_msg_count => x_msg_count
406 ,x_msg_data => x_msg_data
407 ,p_api_type => g_api_type);
408 WHEN okl_api.g_exception_unexpected_error THEN
409 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
410 ,p_pkg_name => g_pkg_name
411 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
412 ,x_msg_count => x_msg_count
413 ,x_msg_data => x_msg_data
414 ,p_api_type => g_api_type);
415 WHEN OTHERS THEN
416 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
417 ,p_pkg_name => g_pkg_name
418 ,p_exc_name => 'OTHERS'
419 ,x_msg_count => x_msg_count
420 ,x_msg_data => x_msg_data
421 ,p_api_type => g_api_type);
422 END delete_eligibility_criteria;
423 /**
424 This procedure do insert/update of eligibility criteria passed to it as
425 parameters. The is_new_flag='Y' in p_ecl_tbl(i) signifies insert of Criteria.
426 p_ech_rec.criteria_Set_id = null signifies insert of Criteria set record.
427 p_ecv_tbl(i).criterion_value_id signifies insert of criterion value record.
428 p_ecv_tbl(i).criteria_id should be pointing to p_ecl_tbl(i).criteria_id,
429 to identify the criterion value rows corressponding to a particular criterion
430 line.
431 **/
432
433 PROCEDURE handle_eligibility_criteria(p_api_version IN number
434 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
435 ,x_return_status OUT NOCOPY varchar2
436 ,x_msg_count OUT NOCOPY number
437 ,x_msg_data OUT NOCOPY varchar2
438 ,p_ech_rec IN okl_ech_rec
439 ,x_ech_rec OUT NOCOPY okl_ech_rec
440 ,p_ecl_tbl IN okl_ecl_tbl
441 ,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
442 ,p_ecv_tbl IN okl_ecv_tbl
443 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl
444 ,p_source_eff_from IN date
445 ,p_source_eff_to IN date) IS
446
447 CURSOR l_data_type_csr(p_crit_cat_def_id IN number) IS
448 SELECT data_type_code
449 ,value_type_code
450 ,source_yn
451 FROM okl_fe_crit_cat_def_b
452 WHERE crit_cat_def_id = p_crit_cat_def_id;
453 lp_ech_rec okl_ech_rec;
454 lx_ech_rec okl_ech_rec;
455 lp_ecl_tbl okl_ecl_tbl;
456 lx_ecl_tbl okl_ecl_tbl;
457 lp_ecv_tbl okl_ecv_tbl;
458 lx_ecv_tbl okl_ecv_tbl;
459 lx_ecv_cons_tbl okl_ecv_tbl;
460 lp_ecl_crt_tbl okl_ecl_tbl;
461 lx_ecl_crt_tbl okl_ecl_tbl;
462 lp_ecl_upd_tbl okl_ecl_tbl;
463 lx_ecl_upd_tbl okl_ecl_tbl;
464 l_ecl_child_tbl okl_ecl_tbl;
465 l_ecv_child_tbl okl_ecv_tbl;
466 l_data_type_code varchar2(30);
467 l_value_type_code varchar2(30);
468 l_source_yn varchar2(30);
469 l_criteria_set_id number;
470 l_validation_code varchar2(30);
471 l_mc_code varchar2(30);
472 i number;
473 j number;
474 k number;
475 l_child_ec_exists boolean;
476 l_api_name CONSTANT varchar2(30) := 'HANDLE_ELIG_CRITERIA';
477 l_api_version CONSTANT number := 1.0;
478 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
479 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.handle_eligibility_criteria';
480 l_debug_enabled varchar2(10);
481 is_debug_procedure_on boolean;
482 is_debug_statement_on boolean;
483
484 BEGIN
485 l_debug_enabled := okl_debug_pub.check_log_enabled;
486 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
487 ,fnd_log.level_procedure);
488
489 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
490 okl_debug_pub.log_debug(fnd_log.level_procedure
491 ,l_module
492 ,'begin debug OKLRECCB.pls call handle_eligibility_criteria');
493 END IF;
494
495 -- check for logging on STATEMENT level
496
497 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
498 ,fnd_log.level_statement);
499
500 -- call START_ACTIVITY to create savepoint, check compatibility
501 -- and initialize message list
502
503 l_return_status := okl_api.start_activity(p_api_name => l_api_name
504 ,p_pkg_name => g_pkg_name
505 ,p_init_msg_list => p_init_msg_list
506 ,l_api_version => l_api_version
507 ,p_api_version => p_api_version
508 ,p_api_type => g_api_type
509 ,x_return_status => x_return_status);
510
511 -- check if activity started successfully
512
513 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
514 RAISE okl_api.g_exception_unexpected_error;
515 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
516 RAISE okl_api.g_exception_error;
517 END IF;
518 lp_ech_rec := p_ech_rec;
519 lp_ecl_tbl := p_ecl_tbl;
520 lp_ecv_tbl := p_ecv_tbl;
521
522 --validate effective dates
523
524 l_return_status := validate_effective_dates(lp_ecl_tbl
525 ,p_source_eff_from
526 ,p_source_eff_to);
527
528 IF (l_return_status = okl_api.g_ret_sts_error) THEN
529 RAISE okl_api.g_exception_error;
530 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
531 RAISE okl_api.g_exception_unexpected_error;
532 END IF;
533
534 --insert/update EC only if some criteria lines are present
535
536 IF lp_ecl_tbl.COUNT > 0 THEN
537 k := 1;
538 IF lp_ech_rec.criteria_set_id IS NULL THEN
539
540 --insert criteria set header
541
542 okl_ech_pvt.insert_row(p_api_version
543 ,okl_api.g_false
544 ,l_return_status
545 ,x_msg_count
546 ,x_msg_data
547 ,lp_ech_rec
548 ,lx_ech_rec);
549
550 -- write to log
551
552 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
553 okl_debug_pub.log_debug(fnd_log.level_statement
554 ,l_module
555 ,'Procedure okl_ech_pvt.insert_row returned with status ' ||
556 l_return_status);
557 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
558 IF (l_return_status = okl_api.g_ret_sts_error) THEN
559 RAISE okl_api.g_exception_error;
560 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
561 RAISE okl_api.g_exception_unexpected_error;
562 END IF;
563 ELSE
564
565 --update criteria set header
566
567 okl_ech_pvt.update_row(p_api_version
568 ,okl_api.g_false
569 ,l_return_status
570 ,x_msg_count
571 ,x_msg_data
572 ,lp_ech_rec
573 ,lx_ech_rec);
574
575 -- write to log
576
577 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
578 okl_debug_pub.log_debug(fnd_log.level_statement
579 ,l_module
580 ,'Procedure okl_ech_pvt.update_row returned with status ' ||
581 l_return_status);
582 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
583
584 IF (l_return_status = okl_api.g_ret_sts_error) THEN
585 RAISE okl_api.g_exception_error;
586 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
587 RAISE okl_api.g_exception_unexpected_error;
588 END IF;
589 END IF;
590
591 --Copy value of OUT variable in the IN record type
592
593 lp_ech_rec := lx_ech_rec;
594
595 FOR i IN lp_ecl_tbl.FIRST..lp_ecl_tbl.LAST LOOP
596 lp_ecl_tbl(i).criteria_set_id := lx_ech_rec.criteria_set_id;
597
598 --if effective date has to get null out then initialize it to
599 --g_miss_date
600
601 IF lp_ecl_tbl(i).effective_to_date IS NULL THEN
602 lp_ecl_tbl(i).effective_to_date := okl_api.g_miss_date;
603 END IF;
604 IF lp_ecl_tbl(i).is_new_flag = 'Y' THEN
605 okl_ecl_pvt.insert_row(p_api_version
606 ,okl_api.g_false
607 ,l_return_status
608 ,x_msg_count
609 ,x_msg_data
610 ,lp_ecl_tbl(i)
611 ,lx_ecl_tbl(i));
612
613 -- write to log
614
615 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
616 okl_debug_pub.log_debug(fnd_log.level_statement
617 ,l_module
618 ,'Procedure okl_ecl_pvt.insert_row returned with status ' ||
619 l_return_status);
620 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
621 IF (l_return_status = okl_api.g_ret_sts_error) THEN
622 RAISE okl_api.g_exception_error;
623 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
624 RAISE okl_api.g_exception_unexpected_error;
625 END IF;
626 OPEN l_data_type_csr(lp_ecl_tbl(i).crit_cat_def_id);
627 FETCH l_data_type_csr INTO l_data_type_code
628 ,l_value_type_code
629 ,l_source_yn ;
630 CLOSE l_data_type_csr;
631 FOR j IN lp_ecv_tbl.FIRST..lp_ecv_tbl.LAST LOOP
632
633 --if the criteria_id in lp_ecv_tbl(i) is same as the id in the recently inserted record
634 -- in okl_ec_criteria table then
635
636 IF (lp_ecv_tbl(j).criteria_id = lp_ecl_tbl(i).criteria_id) THEN
637
638 --populate the criteria_id with the id of the recently inserted record in okl_ec_lines tablein the
639
640 lp_ecv_tbl(j).criteria_id := lx_ecl_tbl(i).criteria_id;
641 lp_ecv_tbl(j).data_type_code := l_data_type_code;
642 lp_ecv_tbl(j).value_type_code := l_value_type_code;
643 lp_ecv_tbl(j).source_yn := l_source_yn;
644 IF lp_ecv_tbl(j).criterion_value_id IS NULL THEN
645 okl_ecv_pvt.insert_row(p_api_version
646 ,okl_api.g_false
647 ,l_return_status
648 ,x_msg_count
649 ,x_msg_data
650 ,lp_ecv_tbl(j)
651 ,lx_ecv_tbl(j));
652
653 -- write to log
654
655 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
656 okl_debug_pub.log_debug(fnd_log.level_statement
657 ,l_module
658 ,'Procedure okl_ecv_pvt.insert_row returned with status ' ||
659 l_return_status);
660 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
661 IF (l_return_status = okl_api.g_ret_sts_error) THEN
662 RAISE okl_api.g_exception_error;
663 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
664 RAISE okl_api.g_exception_unexpected_error;
665 END IF;
666 ELSE
667 okl_ecv_pvt.update_row(p_api_version
668 ,okl_api.g_false
669 ,l_return_status
670 ,x_msg_count
671 ,x_msg_data
672 ,lp_ecv_tbl(j)
673 ,lx_ecv_tbl(j));
674
675 -- write to log
676
677 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
678 okl_debug_pub.log_debug(fnd_log.level_statement
679 ,l_module
680 ,'Procedure okl_ecv_pvt.update_row returned with status ' ||
681 l_return_status);
682 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
683 IF (l_return_status = okl_api.g_ret_sts_error) THEN
684 RAISE okl_api.g_exception_error;
685 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
686 RAISE okl_api.g_exception_unexpected_error;
687 END IF;
688 END IF;
689 lx_ecv_cons_tbl(k) := lx_ecv_tbl(j);
690 k := k + 1;
691 END IF;
692 END LOOP; --of Line values
693 ELSE
694 okl_ecl_pvt.update_row(p_api_version
695 ,okl_api.g_false
696 ,l_return_status
697 ,x_msg_count
698 ,x_msg_data
699 ,lp_ecl_tbl(i)
700 ,lx_ecl_tbl(i));
701
702 -- write to log
703
704 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
705 okl_debug_pub.log_debug(fnd_log.level_statement
706 ,l_module
707 ,'Procedure okl_ecl_pvt.update_row returned with status ' ||
708 l_return_status);
709 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
710 IF (l_return_status = okl_api.g_ret_sts_error) THEN
711 RAISE okl_api.g_exception_error;
712 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
713 RAISE okl_api.g_exception_unexpected_error;
714 END IF;
715 OPEN l_data_type_csr(lp_ecl_tbl(i).crit_cat_def_id);
716 FETCH l_data_type_csr INTO l_data_type_code
717 ,l_value_type_code
718 ,l_source_yn ;
719 CLOSE l_data_type_csr;
720 FOR j IN lp_ecv_tbl.FIRST..lp_ecv_tbl.LAST LOOP
721
722 --if the criteria_id in lp_ecv_tbl(i) is same as the id in the recently inserted record
723 -- in okl_ec_criteria table then
724
725 IF (lp_ecv_tbl(j).criteria_id = lp_ecl_tbl(i).criteria_id) THEN
726
727 --populate the criteria_id with the id of the recently inserted record in okl_ec_lines tablein the
728
729 lp_ecv_tbl(j).criteria_id := lx_ecl_tbl(i).criteria_id;
730 lp_ecv_tbl(j).data_type_code := l_data_type_code;
731 lp_ecv_tbl(j).value_type_code := l_value_type_code;
732 lp_ecv_tbl(j).source_yn := l_source_yn;
733 IF lp_ecv_tbl(j).criterion_value_id IS NULL THEN
734 okl_ecv_pvt.insert_row(p_api_version
735 ,okl_api.g_false
736 ,l_return_status
737 ,x_msg_count
738 ,x_msg_data
739 ,lp_ecv_tbl(j)
740 ,lx_ecv_tbl(j));
741
742 -- write to log
743
744 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
745 okl_debug_pub.log_debug(fnd_log.level_statement
746 ,l_module
747 ,'Procedure okl_ecv_pvt.insert_row returned with status ' ||
748 l_return_status);
749 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
750 IF (l_return_status = okl_api.g_ret_sts_error) THEN
751 RAISE okl_api.g_exception_error;
752 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
753 RAISE okl_api.g_exception_unexpected_error;
754 END IF;
755 ELSE
756 okl_ecv_pvt.update_row(p_api_version
757 ,okl_api.g_false
758 ,l_return_status
759 ,x_msg_count
760 ,x_msg_data
761 ,lp_ecv_tbl(j)
762 ,lx_ecv_tbl(j));
763
764 -- write to log
765
766 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
767 okl_debug_pub.log_debug(fnd_log.level_statement
768 ,l_module
769 ,'Procedure okl_ecv_pvt.update_row returned with status ' ||
770 l_return_status);
771 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
772 IF (l_return_status = okl_api.g_ret_sts_error) THEN
773 RAISE okl_api.g_exception_error;
774 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
775 RAISE okl_api.g_exception_unexpected_error;
776 END IF;
777 END IF;
778 lx_ecv_cons_tbl(k) := lx_ecv_tbl(j);
779 k := k + 1;
780 END IF;
781 END LOOP; --of Line values
782 END IF;
783 END LOOP; --of lines
784
785 ELSE
786
787 --if no lines are present delete header, if header exists
788
789 IF lp_ech_rec.criteria_set_id IS NOT NULL AND lp_ech_rec.criteria_set_id <> okl_api.g_miss_num THEN
790
791 --delete criteria set header
792
793 okl_ech_pvt.delete_row(p_api_version
794 ,okl_api.g_false
795 ,l_return_status
796 ,x_msg_count
797 ,x_msg_data
798 ,lp_ech_rec);
799
800 -- write to log
801
802 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
803 okl_debug_pub.log_debug(fnd_log.level_statement
804 ,l_module
805 ,'Procedure okl_ech_pvt.delete_row returned with status ' ||
806 l_return_status);
807 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
808 IF (l_return_status = okl_api.g_ret_sts_error) THEN
809 RAISE okl_api.g_exception_error;
810 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
811 RAISE okl_api.g_exception_unexpected_error;
812 END IF;
813 END IF;
814 END IF;
815
816 -- of If lp_ecl_tbl.counbt > 0
817 --Assign value to OUT variables
818
819 x_ech_rec := lx_ech_rec;
820 x_ecl_tbl := lx_ecl_tbl;
821 x_ecv_tbl := lx_ecv_cons_tbl;
822 x_return_status := l_return_status;
823 okl_api.end_activity(x_msg_count => x_msg_count
824 ,x_msg_data => x_msg_data);
825
826 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
827 okl_debug_pub.log_debug(fnd_log.level_procedure
828 ,l_module
829 ,'end debug OKLRECVB.pls.pls call handle_eligibility_criteria');
830 END IF;
831
832 EXCEPTION
833 WHEN okl_api.g_exception_error THEN
834 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
835 ,p_pkg_name => g_pkg_name
836 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
837 ,x_msg_count => x_msg_count
838 ,x_msg_data => x_msg_data
839 ,p_api_type => g_api_type);
840 WHEN okl_api.g_exception_unexpected_error THEN
841 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
842 ,p_pkg_name => g_pkg_name
843 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
844 ,x_msg_count => x_msg_count
845 ,x_msg_data => x_msg_data
846 ,p_api_type => g_api_type);
847 WHEN OTHERS THEN
848 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
849 ,p_pkg_name => g_pkg_name
850 ,p_exc_name => 'OTHERS'
851 ,x_msg_count => x_msg_count
852 ,x_msg_data => x_msg_data
853 ,p_api_type => g_api_type);
854 END handle_eligibility_criteria;
855 /**
856 This procedure removes the the criteria line record.
857 **/
858
859 PROCEDURE remove_ec_line(p_api_version IN number
860 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
861 ,x_return_status OUT NOCOPY varchar2
862 ,x_msg_count OUT NOCOPY number
863 ,x_msg_data OUT NOCOPY varchar2
864 ,p_ecl_rec IN okl_ecl_rec) IS
865 lp_ecl_rec okl_ecl_rec;
866 l_ecv_rec okl_ecv_rec;
867
868 CURSOR get_lines_values(p_line_id IN number) IS
869 SELECT criterion_value_id
870 FROM okl_fe_criterion_values
871 WHERE okl_fe_criterion_values.criteria_id = p_line_id;
872 l_api_name CONSTANT varchar2(30) := 'REMOVE_EC_LINE(REC)';
873 l_api_version CONSTANT number := 1.0;
874 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
875 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.remove_ec_line';
876 l_debug_enabled varchar2(10);
877 is_debug_procedure_on boolean;
878 is_debug_statement_on boolean;
879
880 BEGIN
881 l_debug_enabled := okl_debug_pub.check_log_enabled;
882 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
883 ,fnd_log.level_procedure);
884
885 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
886 okl_debug_pub.log_debug(fnd_log.level_procedure
887 ,l_module
888 ,'begin debug OKLRECCB.pls call remove_ec_line');
889 END IF;
890
891 -- check for logging on STATEMENT level
892
893 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
894 ,fnd_log.level_statement);
895
896 -- call START_ACTIVITY to create savepoint, check compatibility
897 -- and initialize message list
898
899 l_return_status := okl_api.start_activity(p_api_name => l_api_name
900 ,p_pkg_name => g_pkg_name
901 ,p_init_msg_list => p_init_msg_list
902 ,l_api_version => l_api_version
903 ,p_api_version => p_api_version
904 ,p_api_type => g_api_type
905 ,x_return_status => x_return_status);
906
907 -- check if activity started successfully
908
909 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
910 RAISE okl_api.g_exception_unexpected_error;
911 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
912 RAISE okl_api.g_exception_error;
913 END IF;
914 lp_ecl_rec := p_ecl_rec;
915
916 --delete line values
917
918 FOR l_ecv_csr_rec IN get_lines_values(lp_ecl_rec.criteria_id) LOOP
919 l_ecv_rec.criterion_value_id := l_ecv_csr_rec.criterion_value_id;
920 okl_ecv_pvt.delete_row(p_api_version
921 ,okl_api.g_false
922 ,l_return_status
923 ,x_msg_count
924 ,x_msg_data
925 ,l_ecv_rec);
926
927 IF (l_return_status = okl_api.g_ret_sts_error) THEN
928 RAISE okl_api.g_exception_error;
929 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
930 RAISE okl_api.g_exception_unexpected_error;
931 END IF;
932
933 END LOOP;
934
935 --delete line
936
937 okl_ecl_pvt.delete_row(p_api_version => p_api_version
938 ,p_init_msg_list => okl_api.g_false
939 ,x_return_status => l_return_status
940 ,x_msg_count => x_msg_count
941 ,x_msg_data => x_msg_data
942 ,p_ecl_rec => lp_ecl_rec);
943
944 IF (l_return_status = okl_api.g_ret_sts_error) THEN
945 RAISE okl_api.g_exception_error;
946 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
947 RAISE okl_api.g_exception_unexpected_error;
948 END IF;
949
950 --Assign value to OUT variables
951
952 x_return_status := l_return_status;
953 okl_api.end_activity(x_msg_count => x_msg_count
954 ,x_msg_data => x_msg_data);
955
956 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
957 okl_debug_pub.log_debug(fnd_log.level_procedure
958 ,l_module
959 ,'end debug OKLRECVB.pls.pls call remove_ec_line');
960 END IF;
961
962 EXCEPTION
963 WHEN okl_api.g_exception_error THEN
964 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
965 ,p_pkg_name => g_pkg_name
966 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
967 ,x_msg_count => x_msg_count
968 ,x_msg_data => x_msg_data
969 ,p_api_type => g_api_type);
970 WHEN okl_api.g_exception_unexpected_error THEN
971 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
972 ,p_pkg_name => g_pkg_name
973 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
974 ,x_msg_count => x_msg_count
975 ,x_msg_data => x_msg_data
976 ,p_api_type => g_api_type);
977 WHEN OTHERS THEN
978 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
979 ,p_pkg_name => g_pkg_name
980 ,p_exc_name => 'OTHERS'
981 ,x_msg_count => x_msg_count
982 ,x_msg_data => x_msg_data
983 ,p_api_type => g_api_type);
984 END remove_ec_line;
985 /**
986 This Procedure returns the eligibility criteria attached to source object
987 identified by p_source_id and p_source_type, such that there is overlap
988 between p_eff_From-p_eff_to range and the eligibility criteria eff_from
989 and eff_to range.
990 It also adjusts the effective dates of eligibility criteria to make it fall
991 within p_eff_From and p_eff_to.
992 This procedure is primarily provided for Standard Rate Template on Lease Rate
993 Set scenario, wherein all the eligibility criteria attached to Standard Rate
994 Template are queried to attach them to Lease Rate Set.
995 **/
996
997 PROCEDURE get_eligibility_criteria(p_api_version IN number
998 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
999 ,x_return_status OUT NOCOPY varchar2
1000 ,x_msg_count OUT NOCOPY number
1001 ,x_msg_data OUT NOCOPY varchar2
1002 ,p_source_id IN number
1003 ,p_source_type IN varchar2
1004 ,p_eff_from IN date
1005 ,p_eff_to IN date
1006 ,x_ech_rec OUT NOCOPY okl_ech_rec
1007 ,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
1008 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
1009 l_source_id number;
1010
1011 CURSOR get_ech_rec IS
1012 SELECT criteria_set_id
1013 ,object_version_number
1014 ,match_criteria_code
1015 ,validation_code
1016 FROM okl_fe_criteria_set
1017 WHERE source_id = p_source_id AND source_object_code = p_source_type;
1018
1019 CURSOR get_ecl_tbl(p_criteria_set_id IN number) IS
1020 SELECT criteria_id
1021 ,object_version_number
1022 ,criteria_set_id
1023 ,crit_cat_def_id
1024 ,match_criteria_code
1025 ,effective_from_date
1026 ,effective_to_date
1027 FROM okl_fe_criteria
1028 WHERE criteria_set_id = p_criteria_set_id;
1029
1030 CURSOR get_ecv_tbl(p_criteria_id IN number) IS
1031 SELECT criterion_value_id
1032 ,object_version_number
1033 ,criteria_id
1034 ,operator_code
1035 ,crit_cat_value1
1036 ,crit_cat_value2
1037 FROM okl_fe_criterion_values
1038 WHERE criteria_id = p_criteria_id;
1039 i number;
1040 j number;
1041 l_api_name CONSTANT varchar2(30) := 'GET_ELIG_CRIT_DT';
1042 l_api_version CONSTANT number := 1.0;
1043 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1044 l_ec_found boolean;
1045 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.get_eligibility_criteria';
1046 l_debug_enabled varchar2(10);
1047 is_debug_procedure_on boolean;
1048 is_debug_statement_on boolean;
1049
1050 BEGIN
1051 l_debug_enabled := okl_debug_pub.check_log_enabled;
1052 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1053 ,fnd_log.level_procedure);
1054
1055 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1056 okl_debug_pub.log_debug(fnd_log.level_procedure
1057 ,l_module
1058 ,'begin debug OKLRECCB.pls call get_eligibility_criteria');
1059 END IF;
1060
1061 -- check for logging on STATEMENT level
1062
1063 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1064 ,fnd_log.level_statement);
1065
1066 -- call START_ACTIVITY to create savepoint, check compatibility
1067 -- and initialize message list
1068
1069 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1070 ,p_pkg_name => g_pkg_name
1071 ,p_init_msg_list => p_init_msg_list
1072 ,l_api_version => l_api_version
1073 ,p_api_version => p_api_version
1074 ,p_api_type => g_api_type
1075 ,x_return_status => x_return_status);
1076
1077 -- check if activity started successfully
1078
1079 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1080 RAISE okl_api.g_exception_unexpected_error;
1081 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1082 RAISE okl_api.g_exception_error;
1083 END IF;
1084 l_source_id := p_source_id;
1085 OPEN get_ech_rec;
1086 FETCH get_ech_rec INTO x_ech_rec.criteria_set_id
1087 ,x_ech_rec.object_version_number
1088 ,x_ech_rec.match_criteria_code
1089 ,x_ech_rec.validation_code ;
1090 l_ec_found := get_ech_rec%FOUND;
1091 CLOSE get_ech_rec;
1092 i := 1;
1093 j := 1;
1094
1095 IF l_ec_found THEN
1096
1097 FOR ecl_rec IN get_ecl_tbl(x_ech_rec.criteria_set_id) LOOP
1098
1099 --if there is overlap of effective dates then only we need to copy
1100
1101 IF p_eff_from BETWEEN ecl_rec.effective_from_date AND nvl(ecl_rec.effective_to_date
1102 ,to_date('01-01-9999'
1103 ,'dd-mm-yyyy'))
1104 OR ecl_rec.effective_from_date BETWEEN p_eff_from AND nvl(p_eff_to
1105 ,to_date('01-01-9999'
1106 ,'dd-mm-yyyy')) THEN
1107 x_ecl_tbl(i).criteria_id := ecl_rec.criteria_id;
1108 x_ecl_tbl(i).object_version_number := ecl_rec.object_version_number;
1109 x_ecl_tbl(i).criteria_set_id := ecl_rec.criteria_set_id;
1110 x_ecl_tbl(i).crit_cat_def_id := ecl_rec.crit_cat_def_id;
1111 x_ecl_tbl(i).match_criteria_code := ecl_rec.match_criteria_code;
1112
1113 --make effective from consistent with p_eff_From
1114 --put greater of the two effective from dates as the effective from of fetched ec
1115
1116 IF ecl_rec.effective_from_date < p_eff_from THEN
1117 x_ecl_tbl(i).effective_from_date := p_eff_from;
1118 ELSE
1119 x_ecl_tbl(i).effective_from_date := ecl_rec.effective_from_date;
1120 END IF;
1121
1122 --make effective to consistent with p_eff_to
1123
1124 x_ecl_tbl(i).effective_to_date := NULL;
1125
1126 --if both effective dates are null then put effective to of fetched ec as null
1127
1128 IF p_eff_to IS NULL AND ecl_rec.effective_to_date IS NULL THEN
1129 x_ecl_tbl(i).effective_to_date := NULL;
1130 END IF;
1131
1132 --if one of the eff to dates is null put other as effective to of fetched ec
1133
1134 IF p_eff_to IS NOT NULL AND ecl_rec.effective_to_date IS NULL THEN
1135 x_ecl_tbl(i).effective_to_date := p_eff_to;
1136 END IF;
1137 IF p_eff_to IS NULL AND ecl_rec.effective_to_date IS NOT NULL THEN
1138 x_ecl_tbl(i).effective_to_date := ecl_rec.effective_to_date;
1139 END IF;
1140
1141 --if both dates are present put whichever is less as effective to of fetched ec
1142
1143 IF p_eff_to < ecl_rec.effective_to_date THEN
1144 x_ecl_tbl(i).effective_to_date := p_eff_to;
1145 END IF;
1146 IF p_eff_to >= ecl_rec.effective_to_date THEN
1147 x_ecl_tbl(i).effective_to_date := ecl_rec.effective_to_date;
1148 END IF;
1149 FOR ecv_rec IN get_ecv_tbl(x_ecl_tbl(i).criteria_id) LOOP
1150 x_ecv_tbl(i).criterion_value_id := ecv_rec.criterion_value_id;
1151 x_ecv_tbl(i).object_version_number := ecv_rec.object_version_number;
1152 x_ecv_tbl(i).criteria_id := ecv_rec.criteria_id;
1153 x_ecv_tbl(i).operator_code := ecv_rec.operator_code;
1154 x_ecv_tbl(i).crit_cat_value1 := ecv_rec.crit_cat_value1;
1155 x_ecv_tbl(i).crit_cat_value2 := ecv_rec.crit_cat_value2;
1156 j := j + 1;
1157 END LOOP;
1158 i := i + 1;
1159 END IF;
1160 END LOOP;
1161
1162 END IF;
1163 x_return_status := l_return_status;
1164
1165 okl_api.end_activity(x_msg_count => x_msg_count
1166 ,x_msg_data => x_msg_data);
1167
1168 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1169 okl_debug_pub.log_debug(fnd_log.level_procedure
1170 ,l_module
1171 ,'end debug OKLRECVB.pls.pls call get_eligibility_criteria');
1172 END IF;
1173
1174 EXCEPTION
1175 WHEN okl_api.g_exception_error THEN
1176 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1177 ,p_pkg_name => g_pkg_name
1178 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1179 ,x_msg_count => x_msg_count
1180 ,x_msg_data => x_msg_data
1181 ,p_api_type => g_api_type);
1182 WHEN okl_api.g_exception_unexpected_error THEN
1183 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1184 ,p_pkg_name => g_pkg_name
1185 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1186 ,x_msg_count => x_msg_count
1187 ,x_msg_data => x_msg_data
1188 ,p_api_type => g_api_type);
1189 WHEN OTHERS THEN
1190 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1191 ,p_pkg_name => g_pkg_name
1192 ,p_exc_name => 'OTHERS'
1193 ,x_msg_count => x_msg_count
1194 ,x_msg_data => x_msg_data
1195 ,p_api_type => g_api_type);
1196 END get_eligibility_criteria;
1197 /**
1198 This Procedure returns eligibility criteria attached to source object
1199 identified by p_source_id and p_source_type
1200 This procedure can be used wherein all the eligibility criteria attached
1201 to a source object are required.
1202 **/
1203
1204 PROCEDURE get_eligibility_criteria(p_api_version IN number
1205 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
1206 ,x_return_status OUT NOCOPY varchar2
1207 ,x_msg_count OUT NOCOPY number
1208 ,x_msg_data OUT NOCOPY varchar2
1209 ,p_source_id IN number
1210 ,p_source_type IN varchar2
1211 ,x_ech_rec OUT NOCOPY okl_ech_rec
1212 ,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
1213 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
1214
1215 CURSOR get_ech_rec IS
1216 SELECT criteria_set_id
1217 ,object_version_number
1218 ,match_criteria_code
1219 ,validation_code
1220 FROM okl_fe_criteria_set
1221 WHERE source_id = p_source_id AND source_object_code = p_source_type;
1222
1223 CURSOR get_ecl_tbl(p_criteria_set_id IN number) IS
1224 SELECT criteria_id
1225 ,object_version_number
1226 ,criteria_set_id
1227 ,crit_cat_def_id
1228 ,match_criteria_code
1229 ,effective_from_date
1230 ,effective_to_date
1231 FROM okl_fe_criteria
1232 WHERE criteria_set_id = p_criteria_set_id;
1233
1234 CURSOR get_ecv_tbl(p_criteria_id IN number) IS
1235 SELECT criterion_value_id
1236 ,object_version_number
1237 ,criteria_id
1238 ,operator_code
1239 ,crit_cat_value1
1240 ,crit_cat_value2
1241 FROM okl_fe_criterion_values
1242 WHERE criteria_id = p_criteria_id;
1243 i number;
1244 j number;
1245 l_source_id number;
1246 l_source_type varchar2(30);
1247 l_api_name CONSTANT varchar2(30) := 'GET_ELIG_CRITERIA';
1248 l_api_version CONSTANT number := 1.0;
1249 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1250 l_ec_found boolean;
1251 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.get_eligibility_criteria';
1252 l_debug_enabled varchar2(10);
1253 is_debug_procedure_on boolean;
1254 is_debug_statement_on boolean;
1255
1256 BEGIN
1257 l_debug_enabled := okl_debug_pub.check_log_enabled;
1258 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1259 ,fnd_log.level_procedure);
1260
1261 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1262 okl_debug_pub.log_debug(fnd_log.level_procedure
1263 ,l_module
1264 ,'begin debug OKLRECCB.pls call get_eligibility_criteria');
1265 END IF;
1266
1267 -- check for logging on STATEMENT level
1268
1269 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1270 ,fnd_log.level_statement);
1271
1272 -- call START_ACTIVITY to create savepoint, check compatibility
1273 -- and initialize message list
1274
1275 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1276 ,p_pkg_name => g_pkg_name
1277 ,p_init_msg_list => p_init_msg_list
1278 ,l_api_version => l_api_version
1279 ,p_api_version => p_api_version
1280 ,p_api_type => g_api_type
1281 ,x_return_status => x_return_status);
1282
1283 -- check if activity started successfully
1284
1285 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1286 RAISE okl_api.g_exception_unexpected_error;
1287 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1288 RAISE okl_api.g_exception_error;
1289 END IF;
1290 l_source_id := p_source_id;
1291 l_source_type := p_source_type;
1292 OPEN get_ech_rec;
1293 FETCH get_ech_rec INTO x_ech_rec.criteria_set_id
1294 ,x_ech_rec.object_version_number
1295 ,x_ech_rec.match_criteria_code
1296 ,x_ech_rec.validation_code ;
1297 l_ec_found := get_ech_rec%FOUND;
1298 CLOSE get_ech_rec;
1299 i := 1;
1300 j := 1;
1301
1302 IF l_ec_found THEN
1303 x_ech_rec.source_id := l_source_id;
1304 x_ech_rec.source_object_code := l_source_type;
1305
1306 FOR ecl_rec IN get_ecl_tbl(x_ech_rec.criteria_set_id) LOOP
1307 x_ecl_tbl(i).criteria_id := ecl_rec.criteria_id;
1308 x_ecl_tbl(i).object_version_number := ecl_rec.object_version_number;
1309 x_ecl_tbl(i).criteria_set_id := ecl_rec.criteria_set_id;
1310 x_ecl_tbl(i).crit_cat_def_id := ecl_rec.crit_cat_def_id;
1311 x_ecl_tbl(i).match_criteria_code := ecl_rec.match_criteria_code;
1312 x_ecl_tbl(i).effective_from_date := ecl_rec.effective_from_date;
1313 x_ecl_tbl(i).effective_to_date := ecl_rec.effective_to_date;
1314 x_ecl_tbl(i).is_new_flag := 'N';
1315 FOR ecv_rec IN get_ecv_tbl(x_ecl_tbl(i).criteria_id) LOOP
1316 x_ecv_tbl(i).criterion_value_id := ecv_rec.criterion_value_id;
1317 x_ecv_tbl(i).object_version_number := ecv_rec.object_version_number;
1318 x_ecv_tbl(i).criteria_id := ecv_rec.criteria_id;
1319 x_ecv_tbl(i).operator_code := ecv_rec.operator_code;
1320 x_ecv_tbl(i).crit_cat_value1 := ecv_rec.crit_cat_value1;
1321 x_ecv_tbl(i).crit_cat_value2 := ecv_rec.crit_cat_value2;
1322 j := j + 1;
1323 END LOOP;
1324 i := i + 1;
1325 END LOOP;
1326
1327 END IF;
1328 x_return_status := l_return_status;
1329
1330 okl_api.end_activity(x_msg_count => x_msg_count
1331 ,x_msg_data => x_msg_data);
1332
1333 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1334 okl_debug_pub.log_debug(fnd_log.level_procedure
1335 ,l_module
1336 ,'end debug OKLRECVB.pls.pls call get_eligibility_criteria');
1337 END IF;
1338
1339 EXCEPTION
1340 WHEN okl_api.g_exception_error THEN
1341 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1342 ,p_pkg_name => g_pkg_name
1343 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1344 ,x_msg_count => x_msg_count
1345 ,x_msg_data => x_msg_data
1346 ,p_api_type => g_api_type);
1347 WHEN okl_api.g_exception_unexpected_error THEN
1348 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1349 ,p_pkg_name => g_pkg_name
1350 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1351 ,x_msg_count => x_msg_count
1352 ,x_msg_data => x_msg_data
1353 ,p_api_type => g_api_type);
1354 WHEN OTHERS THEN
1355 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1356 ,p_pkg_name => g_pkg_name
1357 ,p_exc_name => 'OTHERS'
1358 ,x_msg_count => x_msg_count
1359 ,x_msg_data => x_msg_data
1360 ,p_api_type => g_api_type);
1361 END get_eligibility_criteria;
1362 /**
1363 This procedure puts an end date on the eligibility criteria of the source
1364 object identified by p_source_id and p_source_type.
1365 The end date is put only if the current end date is null or is greater than
1366 the end date that has to be put.
1367 **/
1368
1369 PROCEDURE end_date_eligibility_criteria(p_api_version IN number
1370 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
1371 ,x_return_status OUT NOCOPY varchar2
1372 ,x_msg_count OUT NOCOPY number
1373 ,x_msg_data OUT NOCOPY varchar2
1374 ,p_source_id IN number
1375 ,p_source_type IN varchar2
1376 ,p_end_date IN date) IS
1377
1378 CURSOR get_ecl_tbl IS
1379 SELECT ecl.criteria_id
1380 ,ecl.criteria_set_id
1381 ,ecl.crit_cat_def_id
1382 ,ecl.match_criteria_code
1383 ,ecl.effective_from_date
1384 ,ecl.effective_to_date
1385 ,ecl.object_version_number
1386 FROM okl_fe_criteria ecl
1387 ,okl_fe_criteria_set ech
1388 WHERE ech.source_id = p_source_id
1389 AND ech.source_object_code = p_source_type
1390 AND ecl.criteria_set_id = ech.criteria_set_id;
1391 i number;
1392 l_source_id number;
1393 l_source_type varchar2(30);
1394 l_ecl_tbl okl_ecl_tbl;
1395 lx_ecl_tbl okl_ecl_tbl;
1396 l_api_name CONSTANT varchar2(30) := 'END_DT_ELIG_CRIT';
1397 l_api_version CONSTANT number := 1.0;
1398 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1399 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.end_date_eligibility_criteria';
1400 l_debug_enabled varchar2(10);
1401 is_debug_procedure_on boolean;
1402 is_debug_statement_on boolean;
1403
1404 BEGIN
1405 l_debug_enabled := okl_debug_pub.check_log_enabled;
1406 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1407 ,fnd_log.level_procedure);
1408
1409 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1410 okl_debug_pub.log_debug(fnd_log.level_procedure
1411 ,l_module
1412 ,'begin debug OKLRECCB.pls call end_date_eligibility_criteria');
1413 END IF;
1414
1415 -- check for logging on STATEMENT level
1416
1417 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1418 ,fnd_log.level_statement);
1419
1420 -- call START_ACTIVITY to create savepoint, check compatibility
1421 -- and initialize message list
1422
1423 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1424 ,p_pkg_name => g_pkg_name
1425 ,p_init_msg_list => p_init_msg_list
1426 ,l_api_version => l_api_version
1427 ,p_api_version => p_api_version
1428 ,p_api_type => g_api_type
1429 ,x_return_status => x_return_status);
1430
1431 -- check if activity started successfully
1432
1433 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1434 RAISE okl_api.g_exception_unexpected_error;
1435 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1436 RAISE okl_api.g_exception_error;
1437 END IF;
1438 l_source_id := p_source_id;
1439 l_source_type := p_source_type;
1440 i := 1;
1441
1442 FOR ecl_rec IN get_ecl_tbl LOOP
1443 l_ecl_tbl(i).criteria_id := ecl_rec.criteria_id;
1444 l_ecl_tbl(i).object_version_number := ecl_rec.object_version_number;
1445 l_ecl_tbl(i).criteria_set_id := ecl_rec.criteria_set_id;
1446 l_ecl_tbl(i).crit_cat_def_id := ecl_rec.crit_cat_def_id;
1447 l_ecl_tbl(i).match_criteria_code := ecl_rec.match_criteria_code;
1448 l_ecl_tbl(i).effective_from_date := ecl_rec.effective_from_date;
1449
1450 IF ecl_rec.effective_to_date IS NULL THEN
1451 l_ecl_tbl(i).effective_to_date := p_end_date;
1452 ELSIF ecl_rec.effective_to_date > p_end_date THEN
1453 l_ecl_tbl(i).effective_to_date := p_end_date;
1454 ELSE
1455 l_ecl_tbl(i).effective_to_date := ecl_rec.effective_to_date;
1456 END IF;
1457 l_ecl_tbl(i).is_new_flag := 'N';
1458 i := i + 1;
1459 END LOOP;
1460
1461 IF l_ecl_tbl.COUNT > 0 THEN
1462 okl_ecl_pvt.update_row(p_api_version
1463 ,p_init_msg_list
1464 ,l_return_status
1465 ,x_msg_count
1466 ,x_msg_data
1467 ,l_ecl_tbl
1468 ,lx_ecl_tbl);
1469
1470 -- write to log
1471
1472 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1473 okl_debug_pub.log_debug(fnd_log.level_statement
1474 ,l_module
1475 ,'Procedure okl_ecl_pvt.update_row returned with status ' ||
1476 l_return_status);
1477 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1478 IF (l_return_status = okl_api.g_ret_sts_error) THEN
1479 RAISE okl_api.g_exception_error;
1480 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1481 RAISE okl_api.g_exception_unexpected_error;
1482 END IF;
1483 END IF;
1484 x_return_status := l_return_status;
1485
1486 okl_api.end_activity(x_msg_count => x_msg_count
1487 ,x_msg_data => x_msg_data);
1488
1489 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1490 okl_debug_pub.log_debug(fnd_log.level_procedure
1491 ,l_module
1492 ,'end debug OKLRECVB.pls.pls call end_date_eligibility_criteria');
1493 END IF;
1494
1495 EXCEPTION
1496 WHEN okl_api.g_exception_error THEN
1497 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1498 ,p_pkg_name => g_pkg_name
1499 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1500 ,x_msg_count => x_msg_count
1501 ,x_msg_data => x_msg_data
1502 ,p_api_type => g_api_type);
1503 WHEN okl_api.g_exception_unexpected_error THEN
1504 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1505 ,p_pkg_name => g_pkg_name
1506 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1507 ,x_msg_count => x_msg_count
1508 ,x_msg_data => x_msg_data
1509 ,p_api_type => g_api_type);
1510 WHEN OTHERS THEN
1511 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1512 ,p_pkg_name => g_pkg_name
1513 ,p_exc_name => 'OTHERS'
1514 ,x_msg_count => x_msg_count
1515 ,x_msg_data => x_msg_data
1516 ,p_api_type => g_api_type);
1517 END end_date_eligibility_criteria;
1518
1519 END okl_ecc_values_pvt;