[Home] [Help]
PACKAGE BODY: APPS.OKL_ECC_DEF_PVT
Source
1 PACKAGE BODY okl_ecc_def_pvt AS
2 /* $Header: OKLRECCB.pls 120.1 2005/10/30 04:58:54 appldev noship $ */
3
4 -------------------
5 ---parse_sql
6 -------------------
7 FUNCTION parse_sql(p_sql IN varchar2) RETURN varchar2 IS
8 cur integer;
9 i number;
10 lp_sql varchar2(4000);
11
12 BEGIN
13 lp_sql := p_sql;
14 cur := DBMS_SQL.open_cursor;
15 DBMS_SQL.parse(cur, lp_sql, DBMS_SQL.native);
16 DBMS_SQL.close_cursor (cur);
17 lp_sql := upper(lp_sql);
18
19 SELECT instr(lp_sql, ' ID,')
20 INTO i
21 FROM dual;
22
23 IF i = 0 THEN
24 RAISE okl_api.g_exception_error;
25 END IF;
26
27 SELECT instr(lp_sql, ' NAME ')
28 INTO i
29 FROM dual;
30
31 IF i = 0 THEN
32 RAISE okl_api.g_exception_error;
33 END IF;
34 RETURN okl_api.g_ret_sts_success;
35 EXCEPTION
36 WHEN okl_api.g_exception_error THEN
37 RETURN okl_api.g_ret_sts_error;
38 WHEN OTHERS THEN
39 DBMS_SQL.close_cursor (cur);
40 RETURN okl_api.g_ret_sts_error;
41 END parse_sql; /*header is validated only while creating criteria category as no fields except
42 enabled_yn are updatable after that*/
43
44 FUNCTION validate_header(p_eccv_rec IN okl_eccv_rec) RETURN varchar2 IS
45
46 CURSOR l_ecc_csr IS
47 SELECT 'x'
48 FROM okl_fe_crit_cat_def_v
49 WHERE crit_cat_name = p_eccv_rec.crit_cat_name
50 AND ecc_ac_flag = p_eccv_rec.ecc_ac_flag;
51 l_dummy_var varchar2(1) := '?';
52 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
53 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
54 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
55
56 BEGIN --name,ecc_ac_flag combination should be unique
57 OPEN l_ecc_csr;
58 FETCH l_ecc_csr INTO l_dummy_var ;
59 CLOSE l_ecc_csr; -- if l_dummy_var is 'x' then name already exists
60
61 IF (l_dummy_var = 'x') THEN
62 okl_api.set_message(p_app_name => g_app_name
63 ,p_msg_name => 'OKL_DUPLICATE_NAME'
64 ,p_token1 => 'NAME'
65 ,p_token1_value => p_eccv_rec.crit_cat_name);
66 RAISE okl_api.g_exception_error;
67 END IF; --if value type=range data type should not be varchar2
68
69 IF p_eccv_rec.value_type_code = 'RANGE' AND p_eccv_rec.data_type_code = 'VARCHAR2' THEN
70 okl_api.set_message(p_app_name => g_app_name
71 ,p_msg_name => 'OKL_INVALID_DATATYPE_RANGE_CMB');
72 RAISE okl_api.g_exception_error;
73 END IF; --if value type=range source should not be Yes
74
75 IF p_eccv_rec.value_type_code = 'RANGE' AND p_eccv_rec.source_yn = 'Y' THEN
76 okl_api.set_message(p_app_name => g_app_name
77 ,p_msg_name => 'OKL_INVALID_SOURCE_RANGE_CMB');
78 RAISE okl_api.g_exception_error;
79 END IF; --source=yes data type should be varchar2
80
81 IF p_eccv_rec.source_yn = 'Y' AND NOT p_eccv_rec.data_type_code = 'VARCHAR2' THEN
82 okl_api.set_message(p_app_name => g_app_name
83 ,p_msg_name => 'OKL_INVALID_DATATYPE_SRC_CMB');
84 RAISE okl_api.g_exception_error;
85 END IF;
86
87 --if source is yes VALID sql_statement should be present
88 --sql statement shuuld have alias ID and NAME in SELECT clause and commma (,)
89 -- should immediately follow ID
90
91 IF ((p_eccv_rec.source_yn IS NOT NULL) OR (p_eccv_rec.source_yn <> okl_api.g_miss_char)) THEN
92 IF p_eccv_rec.source_yn = 'Y' THEN --sql_statement should be present
93 IF p_eccv_rec.sql_statement IS NULL THEN
94 okl_api.set_message(p_app_name => g_app_name
95 ,p_msg_name => 'OKL_INVALID_SQL_STATEMENT');
96 RAISE okl_api.g_exception_error; --sql statement should contain ID and NAME as alias in Select clause
97 ELSE
98 l_return_status := parse_sql(p_eccv_rec.sql_statement);
99 IF l_return_status = okl_api.g_ret_sts_error THEN
100 okl_api.set_message(p_app_name => g_app_name
101 ,p_msg_name => 'OKL_MANDATORY_SQL_STATEMENT');
102 RAISE okl_api.g_exception_error;
103 END IF;
104 END IF;
105 END IF;
106 END IF;
107 RETURN(x_return_status);
108 EXCEPTION
109 WHEN okl_api.g_exception_error THEN
110 RETURN okl_api.g_ret_sts_error;
111 WHEN okl_api.g_exception_unexpected_error THEN
112 RETURN okl_api.g_ret_sts_unexp_error;
113 WHEN OTHERS THEN
114 RETURN okl_api.g_ret_sts_unexp_error;
115 END validate_header;
116
117 PROCEDURE create_ecc(p_api_version IN number
118 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
119 ,x_return_status OUT NOCOPY varchar2
120 ,x_msg_count OUT NOCOPY number
121 ,x_msg_data OUT NOCOPY varchar2
122 ,p_eccv_rec IN okl_eccv_rec
123 ,x_eccv_rec OUT NOCOPY okl_eccv_rec
124 ,p_eco_tbl IN okl_eco_tbl
125 ,x_eco_tbl OUT NOCOPY okl_eco_tbl) IS
126 lp_eccv_rec okl_eccv_rec;
127 lx_eccv_rec okl_eccv_rec;
128 lp_eco_tbl okl_eco_tbl;
129 lx_eco_tbl okl_eco_tbl;
130 i number;
131 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_ECC_DEF_PVT.CREATE_ECC';
132 l_debug_enabled varchar2(10);
133 is_debug_procedure_on boolean;
134 is_debug_statement_on boolean;
135 l_api_name CONSTANT varchar2(30) := 'create_ecc';
136 l_api_version CONSTANT number := 1.0;
137 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
138
139 BEGIN
140 l_debug_enabled := okl_debug_pub.check_log_enabled;
141 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
142 ,fnd_log.level_procedure);
143
144 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
145 okl_debug_pub.log_debug(fnd_log.level_procedure
146 ,l_module
147 ,'begin debug OKLRECCB.pls call create_ecc');
148 END IF;
149 -- check for logging on STATEMENT level
150 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
151 ,fnd_log.level_statement);
152
153 -- call START_ACTIVITY to create savepoint, check compatibility
154 -- and initialize message list
155
156 l_return_status := okl_api.start_activity(p_api_name => l_api_name
157 ,p_pkg_name => g_pkg_name
158 ,p_init_msg_list => p_init_msg_list
159 ,l_api_version => l_api_version
160 ,p_api_version => p_api_version
161 ,p_api_type => g_api_type
162 ,x_return_status => x_return_status); -- check if activity started successfully
163
164 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
165 RAISE okl_api.g_exception_unexpected_error;
166 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
167 RAISE okl_api.g_exception_error;
168 END IF;
169 lp_eccv_rec := p_eccv_rec;
170 lp_eco_tbl := p_eco_tbl; --validate header
171 l_return_status := validate_header(lp_eccv_rec); -- write to log
172
173 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
174 okl_debug_pub.log_debug(fnd_log.level_statement
175 ,l_module
176 ,'Function okl_ecc_def_pvt.validate_header returned with status ' ||
177 l_return_status);
178 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
179
180 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
181 RAISE okl_api.g_exception_unexpected_error;
182 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
183 RAISE okl_api.g_exception_error;
184 END IF; --if crit_cat_def_id is present it means we are duplicating so populate orig_crit_Cat_Def_id
185
186 IF ((lp_eccv_rec.crit_cat_def_id IS NOT NULL) AND (lp_eccv_rec.crit_cat_def_id <> okl_api.g_miss_num)) THEN
187 lp_eccv_rec.orig_crit_cat_def_id := lp_eccv_rec.crit_cat_def_id;
188 END IF;
189 okl_ecc_pvt.insert_row(p_api_version
190 ,okl_api.g_false
191 ,l_return_status
192 ,x_msg_count
193 ,x_msg_data
194 ,lp_eccv_rec
195 ,lx_eccv_rec); -- write to log
196
197 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
198 okl_debug_pub.log_debug(fnd_log.level_statement
199 ,l_module
200 ,'okl_ecc_pvt.insert_row returned with status ' ||
201 l_return_status ||
202 ' x_msg_data ' ||
203 x_msg_data);
204 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
205
206 IF (l_return_status = okl_api.g_ret_sts_error) THEN
207 RAISE okl_api.g_exception_error;
208 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
209 RAISE okl_api.g_exception_unexpected_error;
210 END IF; --Copy value of OUT variable in the IN record type
211 lp_eccv_rec := lx_eccv_rec;
212
213 IF lp_eco_tbl.COUNT > 0 THEN
214
215 FOR i IN lp_eco_tbl.FIRST..lp_eco_tbl.LAST LOOP
216 lp_eco_tbl(i).crit_cat_def_id := lp_eccv_rec.crit_cat_def_id;
217 END LOOP;
218 okl_eco_pvt.insert_row(p_api_version
219 ,okl_api.g_false
220 ,l_return_status
221 ,x_msg_count
222 ,x_msg_data
223 ,lp_eco_tbl
224 ,lx_eco_tbl); -- write to log
225 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
226 okl_debug_pub.log_debug(fnd_log.level_statement
227 ,l_module
228 ,'okl_eco_pvt.insert_row returned with status ' ||
229 l_return_status ||
230 ' x_msg_data ' ||
231 x_msg_data);
232 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
233 IF (l_return_status = okl_api.g_ret_sts_error) THEN
234 RAISE okl_api.g_exception_error;
235 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
236 RAISE okl_api.g_exception_unexpected_error;
237 END IF;
238 END IF; --Assign value to OUT variables
239 x_eccv_rec := lx_eccv_rec;
240 x_eco_tbl := lx_eco_tbl;
241 x_return_status := l_return_status;
242 okl_api.end_activity(x_msg_count => x_msg_count
243 ,x_msg_data => x_msg_data);
244
245 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
246 okl_debug_pub.log_debug(fnd_log.level_procedure
247 ,l_module
248 ,'end debug OKLRECCB.pls call create_ecc');
249 END IF;
250
251 EXCEPTION
252 WHEN okl_api.g_exception_error THEN
253 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
254 ,p_pkg_name => g_pkg_name
255 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
256 ,x_msg_count => x_msg_count
257 ,x_msg_data => x_msg_data
258 ,p_api_type => g_api_type);
259 WHEN okl_api.g_exception_unexpected_error THEN
260 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
261 ,p_pkg_name => g_pkg_name
262 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
263 ,x_msg_count => x_msg_count
264 ,x_msg_data => x_msg_data
265 ,p_api_type => g_api_type);
266 WHEN OTHERS THEN
267 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
268 ,p_pkg_name => g_pkg_name
269 ,p_exc_name => 'OTHERS'
270 ,x_msg_count => x_msg_count
271 ,x_msg_data => x_msg_data
272 ,p_api_type => g_api_type);
273 END create_ecc;
274
275 PROCEDURE update_ecc(p_api_version IN number
279 ,x_msg_data OUT NOCOPY varchar2
276 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
277 ,x_return_status OUT NOCOPY varchar2
278 ,x_msg_count OUT NOCOPY number
280 ,p_eccv_rec IN okl_eccv_rec
281 ,x_eccv_rec OUT NOCOPY okl_eccv_rec
282 ,p_eco_tbl IN okl_eco_tbl
283 ,x_eco_tbl OUT NOCOPY okl_eco_tbl) IS
284 lp_eccv_rec okl_eccv_rec;
285 lx_eccv_rec okl_eccv_rec;
286 lp_eco_crt_tbl okl_eco_tbl;
287 lx_eco_crt_tbl okl_eco_tbl;
288 lp_eco_rmv_tbl okl_eco_tbl;
289 lp_eco_tbl okl_eco_tbl;
290 lx_eco_tbl okl_eco_tbl;
291 i number;
292 j number;
293 l number;
294 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_ECC_DEF_PVT.UPDATE_ECC';
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_ecc';
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 OKLRECCB.pls call update_ecc');
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_eccv_rec := p_eccv_rec;
332 lp_eco_tbl := p_eco_tbl;
333 okl_ecc_pvt.update_row(p_api_version
334 ,okl_api.g_false
335 ,l_return_status
336 ,x_msg_count
337 ,x_msg_data
338 ,lp_eccv_rec
339 ,lx_eccv_rec); -- write to log
340
341 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
342 okl_debug_pub.log_debug(fnd_log.level_statement
343 ,l_module
344 ,'okl_ecc_pvt.update_row returned with status ' ||
345 l_return_status ||
346 ' x_msg_data ' ||
347 x_msg_data);
348 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
349
350 IF (l_return_status = okl_api.g_ret_sts_error) THEN
351 RAISE okl_api.g_exception_error;
352 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
353 RAISE okl_api.g_exception_unexpected_error;
354 END IF; --Copy value of OUT variable in the IN record type
355 lp_eccv_rec := lx_eccv_rec;
356 j := 1;
357 l := 1;
358
359 --lp_eco_tbl contains objects to be newly applied (created) or to be un applied (removed)
360 --there is no need to update existing applicable objects
361
362 IF lp_eco_tbl.COUNT > 0 THEN
363
364 FOR i IN lp_eco_tbl.FIRST..lp_eco_tbl.LAST LOOP
365 IF lp_eco_tbl(i).is_applicable = 'Y' THEN
366 lp_eco_crt_tbl(j) := lp_eco_tbl(i);
367 lp_eco_crt_tbl(j).crit_cat_def_id := lp_eccv_rec.crit_cat_def_id;
368 j := j + 1;
369 ELSIF lp_eco_tbl(i).is_applicable = 'N' THEN
370 lp_eco_rmv_tbl(l) := lp_eco_tbl(i);
371 lp_eco_rmv_tbl(l).crit_cat_def_id := lp_eccv_rec.crit_cat_def_id;
372 l := l + 1;
373 END IF;
374 END LOOP;
375
376 IF lp_eco_crt_tbl.COUNT > 0 THEN
377 okl_eco_pvt.insert_row(p_api_version
378 ,okl_api.g_false
379 ,l_return_status
380 ,x_msg_count
381 ,x_msg_data
382 ,lp_eco_crt_tbl
383 ,lx_eco_crt_tbl); -- write to log
384 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
385 okl_debug_pub.log_debug(fnd_log.level_statement
386 ,l_module
387 ,'okl_eco_pvt.insert_row returned with status ' ||
388 l_return_status ||
389 ' x_msg_data ' ||
390 x_msg_data);
391 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
392 IF (l_return_status = okl_api.g_ret_sts_error) THEN
393 RAISE okl_api.g_exception_error;
394 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
395 RAISE okl_api.g_exception_unexpected_error;
396 END IF;
397 END IF;
398 IF lp_eco_rmv_tbl.COUNT > 0 THEN
399 okl_eco_pvt.delete_row(p_api_version
400 ,okl_api.g_false
401 ,l_return_status
402 ,x_msg_count
403 ,x_msg_data
404 ,lp_eco_rmv_tbl); -- write to log
405 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
406 okl_debug_pub.log_debug(fnd_log.level_statement
407 ,l_module
408 ,'okl_eco_pvt.delete_row returned with status ' ||
409 l_return_status ||
410 ' x_msg_data ' ||
411 x_msg_data);
412 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
413 IF (l_return_status = okl_api.g_ret_sts_error) THEN
414 RAISE okl_api.g_exception_error;
415 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
416 RAISE okl_api.g_exception_unexpected_error;
417 END IF;
418 END IF;
419 END IF; --Assign value to OUT variables
420 x_eccv_rec := lx_eccv_rec;
421 x_eco_tbl := lx_eco_crt_tbl;
422 x_return_status := l_return_status;
423 okl_api.end_activity(x_msg_count => x_msg_count
424 ,x_msg_data => x_msg_data);
425
426 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
427 okl_debug_pub.log_debug(fnd_log.level_procedure
428 ,l_module
429 ,'end debug OKLRECCB.pls call update_ecc');
430 END IF;
431
432 EXCEPTION
433 WHEN okl_api.g_exception_error THEN
434 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
435 ,p_pkg_name => g_pkg_name
436 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
437 ,x_msg_count => x_msg_count
438 ,x_msg_data => x_msg_data
439 ,p_api_type => g_api_type);
440 WHEN okl_api.g_exception_unexpected_error THEN
441 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
442 ,p_pkg_name => g_pkg_name
443 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
444 ,x_msg_count => x_msg_count
445 ,x_msg_data => x_msg_data
446 ,p_api_type => g_api_type);
447 WHEN OTHERS THEN
448 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
449 ,p_pkg_name => g_pkg_name
450 ,p_exc_name => 'OTHERS'
451 ,x_msg_count => x_msg_count
452 ,x_msg_data => x_msg_data
453 ,p_api_type => g_api_type);
454 END update_ecc;
455
456 END okl_ecc_def_pvt;