[Home] [Help]
PACKAGE BODY: APPS.OKL_VALIDATION_SET_PVT
Source
1 PACKAGE BODY OKL_VALIDATION_SET_PVT AS
2 /* $Header: OKLRVLSB.pls 120.6 2006/07/07 10:37:17 adagur noship $ */
3
4 FUNCTION validate_header(p_vlsv_rec IN vlsv_rec_type) RETURN varchar2 IS
5
6 CURSOR l_vls_csr IS
7 SELECT 'x'
8 FROM OKL_VALIDATION_SETS_V
9 WHERE validation_set_name = p_vlsv_rec.validation_set_name
10 AND org_id = mo_global.get_current_org_id();
11
12 l_dummy_var varchar2(1) := '?';
13 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
14 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
15 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_header';
16
17 BEGIN
18 IF p_vlsv_rec.effective_from > p_vlsv_rec.effective_to THEN
19
20 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
21 p_msg_name => 'OKL_INVALID_VALID_TO');
22
23 RAISE OKL_API.G_EXCEPTION_ERROR;
24 END IF;
25 --name,vls_ac_flag combination should be unique
26 OPEN l_vls_csr;
27 FETCH l_vls_csr INTO l_dummy_var ;
28 CLOSE l_vls_csr; -- if l_dummy_var is 'x' then name already exists
29
30 IF (l_dummy_var = 'x') THEN
31 okl_api.set_message(p_app_name => g_app_name
32 ,p_msg_name => 'OKL_DUPLICATE_NAME'
33 ,p_token1 => 'NAME'
34 ,p_token1_value => p_vlsv_rec.validation_set_name);
35 RAISE okl_api.g_exception_error;
36 END IF; --if value type=range data type should not be varchar2
37
38 RETURN(x_return_status);
39 EXCEPTION
40 WHEN okl_api.g_exception_error THEN
41 RETURN okl_api.g_ret_sts_error;
42 WHEN okl_api.g_exception_unexpected_error THEN
43 RETURN okl_api.g_ret_sts_unexp_error;
44 WHEN OTHERS THEN
45 RETURN okl_api.g_ret_sts_unexp_error;
46 END validate_header;
47
48 FUNCTION validate_duplicates(p_vldv_tbl IN vldv_tbl_type) RETURN varchar2 IS
49
50 lp_vldv_tbl1 vldv_tbl_type;
51 lp_vldv_tbl2 vldv_tbl_type;
52 l_dummy_var varchar2(1) := '?';
53 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
54 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
55 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_duplicate';
56 i INTEGER:=1;
57 j INTEGER:=1;
58 breakLoop boolean:=false;
59 BEGIN --name,vls_ac_flag combination should be unique
60 lp_vldv_tbl1:=p_vldv_tbl;
61 lp_vldv_tbl2:=p_vldv_tbl;
62 IF p_vldv_tbl.COUNT > 0 THEN
63 FOR i IN lp_vldv_tbl1.FIRST..lp_vldv_tbl1.LAST-1 LOOP
64 FOR j IN i+1..lp_vldv_tbl2.LAST Loop
65 --IF(j!=i AND lp_vldv_tbl2(j).function_id=lp_vldv_tbl1(i).function_id) THEN
66 IF(lp_vldv_tbl2(j).function_id=lp_vldv_tbl1(i).function_id) THEN
67 breakLoop:=true;
68 l_dummy_var:='x';
69 END IF;
70 EXIT WHEN breakLoop;
71 END LOOP;
72 EXIT WHEN breakLoop;
73 END LOOP;
74 END IF;
75
76 -- if l_dummy_var is 'x' then name already exists
77
78 IF (l_dummy_var = 'x') THEN
79 okl_api.set_message(p_app_name => g_app_name
80 ,p_msg_name => 'OKL_DUPLICATE_VALIDATION'
81 ,p_token1 => 'NAME'
82 ,p_token1_value => lp_vldv_tbl2(j).description);
83 RAISE okl_api.g_exception_error;
84 END IF; --if value type=range data type should not be varchar2
85 RETURN(x_return_status);
86 EXCEPTION
87 WHEN okl_api.g_exception_error THEN
88 RETURN okl_api.g_ret_sts_error;
89 WHEN okl_api.g_exception_unexpected_error THEN
90 RETURN okl_api.g_ret_sts_unexp_error;
91 WHEN OTHERS THEN
92 RETURN okl_api.g_ret_sts_unexp_error;
93 END validate_duplicates;
94
95 PROCEDURE create_vls(p_api_version IN number
96 ,p_init_msg_list IN varchar2
97 ,x_return_status OUT NOCOPY varchar2
98 ,x_msg_count OUT NOCOPY number
99 ,x_msg_data OUT NOCOPY varchar2
100 ,p_vlsv_rec IN vlsv_rec_type
101 ,x_vlsv_rec OUT NOCOPY vlsv_rec_type
102 ,p_vldv_tbl IN vldv_tbl_type
103 ,x_vldv_tbl OUT NOCOPY vldv_tbl_type) IS
104 lp_vlsv_rec vlsv_rec_type;
105 lx_vlsv_rec vlsv_rec_type;
106 lp_vldv_tbl vldv_tbl_type;
107 lx_vldv_tbl vldv_tbl_type;
108 i number;
109 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VALIDATION_SET_PVT.create_vls';
110 l_debug_enabled varchar2(10);
111 is_debug_procedure_on boolean;
112 is_debug_statement_on boolean;
113 l_api_name CONSTANT varchar2(30) := 'create_vls';
114 l_api_version CONSTANT number := 1.0;
115 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
116
117 BEGIN
118
119 l_debug_enabled := okl_debug_pub.check_log_enabled;
120 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
121 ,fnd_log.level_procedure);
122
123
124 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
125 okl_debug_pub.log_debug(fnd_log.level_procedure
126 ,l_module
127 ,'begin debug OKLRVLSB.pls call create_vls');
128 END IF; -- check for logging on STATEMENT level
129 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
130 ,fnd_log.level_statement);
131
132 -- call START_ACTIVITY to create savepoint, check compatibility
133 -- and initialize message list
134
135 l_return_status := okl_api.start_activity(p_api_name=>l_api_name
136 ,p_pkg_name=>G_PKG_NAME
137 ,p_init_msg_list=>p_init_msg_list
138 ,l_api_version=>l_api_version
139 ,p_api_version=>p_api_version
140 ,p_api_type=>G_API_TYPE
141 ,x_return_status=>x_return_status); -- check if activity started successfully
142
143 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
144 RAISE okl_api.g_exception_unexpected_error;
145 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
146 RAISE okl_api.g_exception_error;
147 END IF;
148 lp_vlsv_rec := p_vlsv_rec;
149 lp_vldv_tbl := p_vldv_tbl; --validate header
150 l_return_status := validate_header(lp_vlsv_rec); -- write to log
151
152 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
153 okl_debug_pub.log_debug(fnd_log.level_statement
154 ,l_module
155 ,'Function okl_ecc_def_pvt.validate_header returned with status ' ||
156 l_return_status);
157 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
158
159 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
160 RAISE okl_api.g_exception_unexpected_error;
161 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
162 RAISE okl_api.g_exception_error;
163 END IF;
164
165 l_return_status := validate_duplicates(lp_vldv_tbl); -- write to log
166
167 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
168 okl_debug_pub.log_debug(fnd_log.level_statement
169 ,l_module
170 ,'Function okl_ecc_def_pvt.validate_duplicates returned with status ' ||
171 l_return_status);
172 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
173
174 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
175 RAISE okl_api.g_exception_unexpected_error;
176 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
177 RAISE okl_api.g_exception_error;
178 END IF;
179
180 okl_vls_pvt.insert_row(p_api_version
181 ,okl_api.g_false
182 ,l_return_status
183 ,x_msg_count
184 ,x_msg_data
185 ,lp_vlsv_rec
186 ,lx_vlsv_rec); -- write to log
187
188 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
189 okl_debug_pub.log_debug(fnd_log.level_statement
190 ,l_module
191 ,'okl_vls_pvt.insert_row returned with status ' ||
192 l_return_status ||
193 ' x_msg_data ' ||
194 x_msg_data);
195 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
196
197 IF (l_return_status = okl_api.g_ret_sts_error) THEN
198 RAISE okl_api.g_exception_error;
199 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
200 RAISE okl_api.g_exception_unexpected_error;
201 END IF; --Copy value of OUT variable in the IN rvldrd type
202
203 lp_vlsv_rec := lx_vlsv_rec;
204
205 IF lp_vldv_tbl.COUNT > 0 THEN
206
207 FOR i IN lp_vldv_tbl.FIRST..lp_vldv_tbl.LAST LOOP
208 lp_vldv_tbl(i).validation_set_id := lp_vlsv_rec.id;
209 END LOOP;
210 okl_vld_pvt.insert_row(p_api_version
211 ,okl_api.g_false
212 ,l_return_status
213 ,x_msg_count
214 ,x_msg_data
215 ,lp_vldv_tbl
216 ,lx_vldv_tbl); -- write to log
217
218 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
219 okl_debug_pub.log_debug(fnd_log.level_statement
220 ,l_module
221 ,'okl_vld_pvt.insert_row returned with status ' ||
222 l_return_status ||
223 ' x_msg_data ' ||
224 x_msg_data);
225 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
226 IF (l_return_status = okl_api.g_ret_sts_error) THEN
227 RAISE okl_api.g_exception_error;
228 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
229 RAISE okl_api.g_exception_unexpected_error;
230 END IF;
231 END IF; --Assign value to OUT variables
232 x_vlsv_rec := lx_vlsv_rec;
233 x_vldv_tbl := lx_vldv_tbl;
234 x_return_status := l_return_status;
235 okl_api.end_activity(x_msg_count => x_msg_count
236 ,x_msg_data => x_msg_data);
237
238 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
239 okl_debug_pub.log_debug(fnd_log.level_procedure
240 ,l_module
241 ,'end debug OKL_VALIDATION_SET_PVT.pls call create_vls');
242 END IF;
243
244 EXCEPTION
245 WHEN okl_api.g_exception_error THEN
246
247 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
248 ,p_pkg_name => G_PKG_NAME
249 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
250 ,x_msg_count => x_msg_count
251 ,x_msg_data => x_msg_data
252 ,p_api_type => G_API_TYPE);
253
254 WHEN okl_api.g_exception_unexpected_error THEN
255
256 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
257 ,p_pkg_name => G_PKG_NAME
258 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
259 ,x_msg_count => x_msg_count
260 ,x_msg_data => x_msg_data
261 ,p_api_type => G_API_TYPE);
262
263 WHEN OTHERS THEN
264
265 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
266 ,p_pkg_name => G_PKG_NAME
267 ,p_exc_name => 'OTHERS'
268 ,x_msg_count => x_msg_count
269 ,x_msg_data => x_msg_data
270 ,p_api_type => G_API_TYPE);
271
272 END create_vls;
273
274 PROCEDURE update_vls(p_api_version IN number
275 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
276 ,x_return_status OUT NOCOPY varchar2
277 ,x_msg_count OUT NOCOPY number
278 ,x_msg_data OUT NOCOPY varchar2
279 ,p_vlsv_rec IN vlsv_rec_type
280 ,x_vlsv_rec OUT NOCOPY vlsv_rec_type
281 ,p_vldv_tbl IN vldv_tbl_type
282 ,x_vldv_tbl OUT NOCOPY vldv_tbl_type) IS
283 lp_vlsv_rec vlsv_rec_type;
284 lx_vlsv_rec vlsv_rec_type;
285 lp_vldv_crt_tbl vldv_tbl_type;
286 lx_vldv_crt_tbl vldv_tbl_type;
287 lp_vldv_rmv_tbl vldv_tbl_type;
288 lx_vldv_rmv_tbl vldv_tbl_type;
289 lp_vldv_tbl vldv_tbl_type;
290 lx_vldv_tbl vldv_tbl_type;
291 i number;
292 j number;
293 l number;
294 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VALIDATION_SET_PVT.update_vls';
295 l_debug_enabled varchar2(10);
296 is_debug_procedure_on boolean;
297 is_debug_statement_on boolean;
298 l_api_name CONSTANT varchar2(30) := 'update_vls';
299 l_api_version CONSTANT number := 1.0;
300 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
301
302 BEGIN
303 l_debug_enabled := okl_debug_pub.check_log_enabled;
304 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
305 ,fnd_log.level_procedure);
306
307 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
308 okl_debug_pub.log_debug(fnd_log.level_procedure
309 ,l_module
310 ,'begin debug OKL_VALIDATION_SET_PVT.pls call update_vls');
311 END IF; -- check for logging on STATEMENT level
312 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
313 ,fnd_log.level_statement);
314
315 -- call START_ACTIVITY to create savepoint, check compatibility
316 -- and initialize message list
317
318 l_return_status := okl_api.start_activity(p_api_name => l_api_name
319 ,p_pkg_name => g_pkg_name
320 ,p_init_msg_list => p_init_msg_list
321 ,l_api_version => l_api_version
322 ,p_api_version => p_api_version
323 ,p_api_type => g_api_type
324 ,x_return_status => x_return_status); -- check if activity started successfully
325
326 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
327 RAISE okl_api.g_exception_unexpected_error;
328 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
329 RAISE okl_api.g_exception_error;
330 END IF;
331 lp_vlsv_rec := p_vlsv_rec;
332 lp_vldv_tbl := p_vldv_tbl;
333
334 IF lp_vlsv_rec.effective_from > lp_vlsv_rec.effective_to THEN
335
336 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
337 p_msg_name => 'OKL_INVALID_VALID_TO');
338
339 RAISE OKL_API.G_EXCEPTION_ERROR;
340 END IF;
341
342 l_return_status :=validate_duplicates(lp_vldv_tbl); -- write to log
343
344 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
345 okl_debug_pub.log_debug(fnd_log.level_statement
346 ,l_module
347 ,'Function okl_ecc_def_pvt.validate_header returned with status ' ||
348 l_return_status);
349 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
350
351 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
352 RAISE okl_api.g_exception_unexpected_error;
353 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
354 RAISE okl_api.g_exception_error;
355 END IF;
356 okl_vls_pvt.update_row(p_api_version
357 ,okl_api.g_false
358 ,l_return_status
359 ,x_msg_count
360 ,x_msg_data
361 ,lp_vlsv_rec
362 ,lx_vlsv_rec); -- write to log
363
364 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
365 okl_debug_pub.log_debug(fnd_log.level_statement
366 ,l_module
367 ,'okl_vls_pvt.update_row returned with status ' ||
368 l_return_status ||
369 ' x_msg_data ' ||
370 x_msg_data);
371 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
372
373 IF (l_return_status = okl_api.g_ret_sts_error) THEN
374 RAISE okl_api.g_exception_error;
375 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
376 RAISE okl_api.g_exception_unexpected_error;
377 END IF; --Copy value of OUT variable in the IN rvldrd type
378 lp_vlsv_rec := lx_vlsv_rec;
379 j := 1;
380 l := 1;
381
382
383 /*
384 --lp_vld_tbl contains objects to be newly applied (created) or to be un applied (removed)
385 --there is no need to update existing applicable objects
386 */
387 IF lp_vldv_tbl.COUNT > 0 THEN
388
389 FOR i IN lp_vldv_tbl.FIRST..lp_vldv_tbl.LAST LOOP
390 IF lp_vldv_tbl(i).id IS NULL THEN
391 lp_vldv_crt_tbl(j) := lp_vldv_tbl(i);
392 lp_vldv_crt_tbl(j).validation_set_id := lp_vlsv_rec.id;
393 j := j + 1;
394 ELSIF lp_vldv_tbl(i).id IS NOT NULL THEN
395 lp_vldv_rmv_tbl(l) := lp_vldv_tbl(i);
396 lp_vldv_rmv_tbl(l).validation_set_id := lp_vlsv_rec.id;
397 l := l + 1;
398 END IF;
399 END LOOP;
400
401 IF lp_vldv_crt_tbl.COUNT > 0 THEN
402 okl_vld_pvt.insert_row(p_api_version
403 ,okl_api.g_false
404 ,l_return_status
405 ,x_msg_count
406 ,x_msg_data
407 ,lp_vldv_crt_tbl
408 ,lx_vldv_crt_tbl); -- write to log
409 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
410 okl_debug_pub.log_debug(fnd_log.level_statement
411 ,l_module
412 ,'okl_vld_pvt.insert_row returned with status ' ||
413 l_return_status ||
414 ' x_msg_data ' ||
415 x_msg_data);
416 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
417 IF (l_return_status = okl_api.g_ret_sts_error) THEN
418 RAISE okl_api.g_exception_error;
419 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
420 RAISE okl_api.g_exception_unexpected_error;
421 END IF;
422 END IF;
423 IF lp_vldv_rmv_tbl.COUNT > 0 THEN
424 okl_vld_pvt.update_row(p_api_version
425 ,okl_api.g_false
426 ,l_return_status
427 ,x_msg_count
428 ,x_msg_data
429 ,lp_vldv_rmv_tbl
430 ,lx_vldv_rmv_tbl); -- write to log
431 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
432 okl_debug_pub.log_debug(fnd_log.level_statement
433 ,l_module
434 ,'okl_vld_pvt.delete_row returned with status ' ||
435 l_return_status ||
436 ' x_msg_data ' ||
437 x_msg_data);
438 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
439 IF (l_return_status = okl_api.g_ret_sts_error) THEN
440 RAISE okl_api.g_exception_error;
441 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
442 RAISE okl_api.g_exception_unexpected_error;
443 END IF;
444 END IF;
445 END IF; --Assign value to OUT variables
446
447 x_vlsv_rec := lx_vlsv_rec;
448 x_vldv_tbl := lx_vldv_crt_tbl;
449 x_return_status := l_return_status;
450 okl_api.end_activity(x_msg_count => x_msg_count
451 ,x_msg_data => x_msg_data);
452
453 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
454 okl_debug_pub.log_debug(fnd_log.level_procedure
455 ,l_module
456 ,'end debug OKLRvlsB.pls call update_vls');
457 END IF;
458
459 EXCEPTION
460 WHEN okl_api.g_exception_error THEN
461 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
462 ,p_pkg_name => g_pkg_name
463 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
464 ,x_msg_count => x_msg_count
465 ,x_msg_data => x_msg_data
466 ,p_api_type => g_api_type);
467 WHEN okl_api.g_exception_unexpected_error THEN
468 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
469 ,p_pkg_name => g_pkg_name
470 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
471 ,x_msg_count => x_msg_count
472 ,x_msg_data => x_msg_data
473 ,p_api_type => g_api_type);
474 WHEN OTHERS THEN
475 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
476 ,p_pkg_name => g_pkg_name
477 ,p_exc_name => 'OTHERS'
478 ,x_msg_count => x_msg_count
479 ,x_msg_data => x_msg_data
480 ,p_api_type => g_api_type);
481 END update_vls;
482
483 PROCEDURE delete_vls(p_api_version IN number
484 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
485 ,x_return_status OUT NOCOPY varchar2
486 ,x_msg_count OUT NOCOPY number
487 ,x_msg_data OUT NOCOPY varchar2
488 ,p_vlsv_rec IN vlsv_rec_type) IS
489
490 CURSOR l_vldv_csr(p_validation_set_id number) IS
491 SELECT id
492 ,object_version_number
493 ,attribute_category
494 ,attribute1
495 ,attribute2
496 ,attribute3
497 ,attribute4
498 ,attribute5
499 ,attribute6
500 ,attribute7
501 ,attribute8
502 ,attribute9
503 ,attribute10
504 ,attribute11
505 ,attribute12
506 ,attribute13
507 ,attribute14
508 ,attribute15
509 ,validation_set_id
510 ,function_id
511 ,failure_severity
512 ,short_description
513 ,description
514 ,comments
515 FROM OKL_VALIDATIONS_V
516 WHERE validation_set_id=p_validation_set_id;
517 i number:=0;
518 lp_vlsv_rec vlsv_rec_type;
519 lx_vlsv_rec vlsv_rec_type;
520 lx_vldv_rec vldv_rec_type;
521 lx_vldv_tbl vldv_tbl_type;
522 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VALIDATION_SET_PVT.delete_vls';
523 l_debug_enabled varchar2(10);
524 is_debug_procedure_on boolean;
525 is_debug_statement_on boolean;
526 l_api_name CONSTANT varchar2(30) := 'delete_vls';
527 l_api_version CONSTANT number := 1.0;
528 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
529
530 BEGIN
531 l_debug_enabled := okl_debug_pub.check_log_enabled;
532 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
533 ,fnd_log.level_procedure);
534
535 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
536 okl_debug_pub.log_debug(fnd_log.level_procedure
537 ,l_module
538 ,'begin debug OKL_VALIDATION_SET_PVT.pls call delete_vls');
539 END IF; -- check for logging on STATEMENT level
540 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
541 ,fnd_log.level_statement);
542
543 -- call START_ACTIVITY to create savepoint, check compatibility
544 -- and initialize message list
545
546 l_return_status := okl_api.start_activity(p_api_name => l_api_name
547 ,p_pkg_name => g_pkg_name
548 ,p_init_msg_list => p_init_msg_list
549 ,l_api_version => l_api_version
550 ,p_api_version => p_api_version
551 ,p_api_type => g_api_type
552 ,x_return_status => x_return_status); -- check if activity started successfully
553
554 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
555 RAISE okl_api.g_exception_unexpected_error;
556 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
557 RAISE okl_api.g_exception_error;
558 END IF;
559 lp_vlsv_rec := p_vlsv_rec;
560
561 l_return_status := OKL_VALIDATION_SET_PVT.validate_header(lp_vlsv_rec); -- write to log
562
563 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
564 okl_debug_pub.log_debug(fnd_log.level_statement
565 ,l_module
566 ,'Function okl_ecc_def_pvt.validate_header returned with status ' ||
567 l_return_status);
568 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
569
570 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
571 RAISE okl_api.g_exception_unexpected_error;
572 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
573 RAISE okl_api.g_exception_error;
574 END IF; --if crit_cat_def_id is present it means we are duplicating so populate orig_crit_Cat_Def_id
575
576 --Delete all the Records for Validation Set
577 OPEN l_vldv_csr(lp_vlsv_rec.id);
578 LOOP
579 FETCH l_vldv_csr INTO lx_vldv_rec;
580 EXIT WHEN l_vldv_csr%NOTFOUND;
581 lx_vldv_tbl(i):=lx_vldv_rec;
582 i:=i+1;
583 END LOOP;
584 okl_vld_pvt.delete_row(p_api_version
585 ,okl_api.g_false
586 ,l_return_status
587 ,x_msg_count
588 ,x_msg_data
589 ,lx_vldv_tbl); -- write to log
590
591 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
592 okl_debug_pub.log_debug(fnd_log.level_statement
593 ,l_module
594 ,'okl_vls_pvt.delete_row returned with status ' ||
595 l_return_status ||
596 ' x_msg_data ' ||
597 x_msg_data);
598 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
599
600 IF (l_return_status = okl_api.g_ret_sts_error) THEN
601 RAISE okl_api.g_exception_error;
602 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
603 RAISE okl_api.g_exception_unexpected_error;
604 END IF; --Copy value of OUT variable in the IN rvldrd type
605 --Delete Parent If Child Are successfully Deleted
606 okl_vls_pvt.delete_row(p_api_version
607 ,okl_api.g_false
608 ,l_return_status
609 ,x_msg_count
610 ,x_msg_data
611 ,lp_vlsv_rec); -- write to log
612
613 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
614 okl_debug_pub.log_debug(fnd_log.level_statement
615 ,l_module
616 ,'okl_vls_pvt.delete_row returned with status ' ||
617 l_return_status ||
618 ' x_msg_data ' ||
619 x_msg_data);
620 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
621
622 IF (l_return_status = okl_api.g_ret_sts_error) THEN
623 RAISE okl_api.g_exception_error;
624 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
625 RAISE okl_api.g_exception_unexpected_error;
626 END IF; --Copy value of OUT variable in the IN rvldrd type
627 okl_api.end_activity(x_msg_count => x_msg_count
628 ,x_msg_data => x_msg_data);
629
630 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
631 okl_debug_pub.log_debug(fnd_log.level_procedure
632 ,l_module
633 ,'end debug OKL_VALIDATION_SET_PVT.pls call delete_vls');
634 END IF;
635
636
637
638 EXCEPTION
639 WHEN okl_api.g_exception_error THEN
640 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
641 ,p_pkg_name => g_pkg_name
642 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
643 ,x_msg_count => x_msg_count
644 ,x_msg_data => x_msg_data
645 ,p_api_type => g_api_type);
646 WHEN okl_api.g_exception_unexpected_error THEN
647 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
648 ,p_pkg_name => g_pkg_name
649 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
650 ,x_msg_count => x_msg_count
651 ,x_msg_data => x_msg_data
652 ,p_api_type => g_api_type);
653 WHEN OTHERS THEN
654 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
655 ,p_pkg_name => g_pkg_name
656 ,p_exc_name => 'OTHERS'
657 ,x_msg_count => x_msg_count
658 ,x_msg_data => x_msg_data
659 ,p_api_type => g_api_type);
660
661
662 END delete_vls;
663
664 PROCEDURE delete_vld(p_api_version IN number
665 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
666 ,x_return_status OUT NOCOPY varchar2
667 ,x_msg_count OUT NOCOPY number
668 ,x_msg_data OUT NOCOPY varchar2
669 ,p_vldv_rec IN vldv_rec_type) IS
670
671 lp_vldv_rec vldv_rec_type;
672 lx_vldv_rec vldv_rec_type;
673 i number;
674 j number;
675 l number;
676 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VALIDATION_SET_PVT.delete_vld';
677 l_debug_enabled varchar2(10);
678 is_debug_procedure_on boolean;
679 is_debug_statement_on boolean;
680 l_api_name CONSTANT varchar2(30) := 'delete_vld';
681 l_api_version CONSTANT number := 1.0;
682 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
683 BEGIN
684 l_debug_enabled := okl_debug_pub.check_log_enabled;
685 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
686 ,fnd_log.level_procedure);
687
688 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
689 okl_debug_pub.log_debug(fnd_log.level_procedure
690 ,l_module
691 ,'begin debug OKL_VALIDATION_SET_PVT.pls call delete_vls');
692 END IF; -- check for logging on STATEMENT level
693 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
694 ,fnd_log.level_statement);
695
696 -- call START_ACTIVITY to create savepoint, check compatibility
697 -- and initialize message list
698
699 l_return_status := okl_api.start_activity(p_api_name => l_api_name
700 ,p_pkg_name => g_pkg_name
701 ,p_init_msg_list => p_init_msg_list
702 ,l_api_version => l_api_version
703 ,p_api_version => p_api_version
704 ,p_api_type => g_api_type
705 ,x_return_status => x_return_status); -- check if activity started successfully
706
707 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
708 RAISE okl_api.g_exception_unexpected_error;
709 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
710 RAISE okl_api.g_exception_error;
711 END IF;
712
713 lp_vldv_rec := p_vldv_rec;
714 okl_vld_pvt.delete_row(p_api_version
715 ,okl_api.g_false
716 ,l_return_status
717 ,x_msg_count
718 ,x_msg_data
719 ,lp_vldv_rec); -- write to log
720
721 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
722 okl_debug_pub.log_debug(fnd_log.level_statement
723 ,l_module
724 ,'okl_vls_pvt.delete_row returned with status ' ||
725 l_return_status ||
726 ' x_msg_data ' ||
727 x_msg_data);
728 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
729
730 IF (l_return_status = okl_api.g_ret_sts_error) THEN
731 RAISE okl_api.g_exception_error;
732 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
733 RAISE okl_api.g_exception_unexpected_error;
734 END IF; --Copy value of OUT variable in the IN rvldrd type
735
736
737 okl_api.end_activity(x_msg_count => x_msg_count
738 ,x_msg_data => x_msg_data);
739
740 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
741 okl_debug_pub.log_debug(fnd_log.level_procedure
742 ,l_module
743 ,'end debug OKL_VALIDATION_SET_PVT.pls call create_vls');
744 END IF;
745
746 EXCEPTION
747 WHEN okl_api.g_exception_error THEN
748 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
749 ,p_pkg_name => g_pkg_name
750 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
751 ,x_msg_count => x_msg_count
752 ,x_msg_data => x_msg_data
753 ,p_api_type => g_api_type);
754 WHEN okl_api.g_exception_unexpected_error THEN
755 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
756 ,p_pkg_name => g_pkg_name
757 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
758 ,x_msg_count => x_msg_count
759 ,x_msg_data => x_msg_data
760 ,p_api_type => g_api_type);
761 WHEN OTHERS THEN
762 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
763 ,p_pkg_name => g_pkg_name
764 ,p_exc_name => 'OTHERS'
765 ,x_msg_count => x_msg_count
766 ,x_msg_data => x_msg_data
767 ,p_api_type => g_api_type);
768
769
770
771
772 END delete_vld;
773
774
775 END OKL_VALIDATION_SET_PVT;