[Home] [Help]
PACKAGE BODY: APPS.OKL_RESI_CAT_SETS_PVT
Source
1 PACKAGE BODY OKL_RESI_CAT_SETS_PVT as
2 /* $Header: OKLRRCSB.pls 120.4 2005/09/14 06:43:12 smadhava noship $ */
3
4 -- Function checks if there are repetitions of items or categories in a residual category set
5 FUNCTION check_existence (p_source_code VARCHAR2, p_res_tbl IN okl_res_tbl) RETURN VARCHAR2 IS
6 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
7
8 l_temp_resi_category_set_id NUMBER := NULL;
9 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'check_existence';
10
11 l_count NUMBER := 0;
12
13 BEGIN
14
15 FOR i IN p_res_tbl.FIRST..p_res_tbl.LAST
16 LOOP
17 l_count := 0;
18 IF p_source_code = G_CAT_ITEM THEN
19 FOR j IN p_res_tbl.FIRST..p_res_tbl.LAST
20 LOOP
21 IF p_res_tbl(i).inventory_item_id = p_res_tbl(j).inventory_item_id
22 AND p_res_tbl(i).organization_id = p_res_tbl(j).organization_id
23 AND p_res_tbl(i).category_set_id = p_res_tbl(j).category_set_id THEN
24 l_count := l_count + 1;
25 END IF;
26 END LOOP;
27
28 IF l_count > 1 THEN
29 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
30 p_msg_name => 'OKL_ITEM_REPEAT');
31 RAISE OKL_API.G_EXCEPTION_ERROR;
32 END IF;
33
34 ELSIF p_source_code = G_CAT_ITEM_CAT THEN
35 FOR j IN p_res_tbl.FIRST..p_res_tbl.LAST
36 LOOP
37 IF p_res_tbl(i).category_id = p_res_tbl(j).category_id
38 AND p_res_tbl(i).category_set_id = p_res_tbl(j).category_set_id THEN
39 l_count := l_count + 1;
40 END IF;
41 END LOOP;
42
43 IF l_count > 1 THEN
44 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
45 p_msg_name => 'OKL_ITEM_CAT_REPEAT');
46 RAISE OKL_API.G_EXCEPTION_ERROR;
47 END IF;
48
49 END IF; -- end of source code check
50 END LOOP; -- end of for loop
51
52 RETURN (l_return_status);
53 EXCEPTION
54 WHEN OKL_API.G_EXCEPTION_ERROR THEN
55 RETURN OKL_API.G_RET_STS_ERROR;
56 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
57 RETURN OKL_API.G_RET_STS_UNEXP_ERROR;
58 WHEN OTHERS THEN
59 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
60 p_msg_name => G_DB_ERROR,
61 p_token1 => G_PROG_NAME_TOKEN,
62 p_token1_value => l_api_name,
63 p_token2 => G_SQLCODE_TOKEN,
64 p_token2_value => sqlcode,
65 p_token3 => G_SQLERRM_TOKEN,
66 p_token3_value => sqlerrm);
67 RETURN OKL_API.G_RET_STS_UNEXP_ERROR;
68 END check_existence;
69
70 /*
71 Function checks the inventory for the presence of the items/item categories.
72 If not present, it changes the status of the residual category set to Inactive
73 */
74 FUNCTION check_update_status(
75 p_source_code IN VARCHAR2
76 , p_res_upd_tbl IN okl_res_tbl) RETURN VARCHAR2 IS
77 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_RESI_CAT_SETS_PVT.check_update_status';
78 l_debug_enabled VARCHAR2(10);
79 is_debug_procedure_on BOOLEAN;
80 is_debug_statement_on BOOLEAN;
81
82 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'check_update_status';
83 l_make_inactive VARCHAR2(3) := 'no';
84 l_temp_cnt NUMBER :=0;
85 BEGIN
86
87 l_debug_enabled := okl_debug_pub.check_log_enabled;
88 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
89 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
90 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRRCSB.pls call check_update_status');
91 END IF;
92
93
94 IF p_source_code = G_CAT_ITEM THEN
95 l_temp_cnt := 0;
96 FOR i IN p_res_upd_tbl.FIRST..p_res_upd_tbl.LAST
97 LOOP
98
99 SELECT
100 COUNT(1)
101 INTO
102 l_temp_cnt
103 FROM
104 MTL_ITEM_CATEGORIES MTL
105 WHERE
106 MTL.INVENTORY_ITEM_ID = p_res_upd_tbl(i).inventory_item_id
107 AND MTL.ORGANIZATION_ID = p_res_upd_tbl(i).organization_id;
108
109 IF l_temp_cnt = 0 THEN
110 l_make_inactive := 'yes';
111 END IF;
112
113 END LOOP;
114
115 ELSIF p_source_code = G_CAT_ITEM_CAT THEN
116 FOR i IN p_res_upd_tbl.FIRST..p_res_upd_tbl.LAST
117 LOOP
118 l_temp_cnt := 0;
119 SELECT
120 COUNT(1)
121 INTO
122 l_temp_cnt
123 FROM
124 MTL_ITEM_CATEGORIES MTL
125 WHERE
126 MTL.CATEGORY_ID = p_res_upd_tbl(i).category_id
127 AND MTL.CATEGORY_SET_ID = p_res_upd_tbl(i).category_set_id;
128
129 IF l_temp_cnt = 0 THEN
130 l_make_inactive := 'yes';
131 END IF;
132
133 END LOOP;
134
135 END IF;
136
137
138
139 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
140 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRRCSB.pls call check_update_status');
141 END IF;
142
143 RETURN l_make_inactive;
144
145 EXCEPTION
146 WHEN OKL_API.G_EXCEPTION_ERROR THEN
147 RETURN OKL_API.G_RET_STS_ERROR;
148 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
149 RETURN OKL_API.G_RET_STS_UNEXP_ERROR;
150 WHEN OTHERS THEN
151 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
152 p_msg_name => G_DB_ERROR,
153 p_token1 => G_PROG_NAME_TOKEN,
154 p_token1_value => l_api_name,
155 p_token2 => G_SQLCODE_TOKEN,
156 p_token2_value => sqlcode,
157 p_token3 => G_SQLERRM_TOKEN,
158 p_token3_value => sqlerrm);
159 RETURN OKL_API.G_RET_STS_UNEXP_ERROR;
160 END check_update_status;
161
162 procedure create_rcs (
163 p_api_version IN NUMBER
164 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
165 , x_return_status OUT NOCOPY VARCHAR2
166 , x_msg_count OUT NOCOPY NUMBER
167 , x_msg_data OUT NOCOPY VARCHAR2
168 , p_rcsv_rec IN okl_rcsv_rec
169 , p_res_tbl IN okl_res_tbl
170 , x_rcsv_rec OUT NOCOPY okl_rcsv_rec
171 , x_res_tbl OUT NOCOPY okl_res_tbl
172 ) IS
173 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_RESI_CAT_SETS_PVT.create_rcs';
174 l_debug_enabled VARCHAR2(10);
175 is_debug_procedure_on BOOLEAN;
176 is_debug_statement_on BOOLEAN;
177
178 l_api_name CONSTANT VARCHAR2(40) := 'create_rcs';
179 l_api_version CONSTANT NUMBER := p_api_version;
180 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
181 l_msg_count NUMBER := x_msg_count ;
182 l_msg_data VARCHAR2(2000);
183 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
184
185 l_rcsv_rec okl_rcsv_rec := p_rcsv_rec;
186 l_res_tbl okl_res_tbl := p_res_tbl;
187
188 i NUMBER :=0;
189 begin
190 l_debug_enabled := okl_debug_pub.check_log_enabled;
191 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
192 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
193 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRRCSB.pls call create_rcs');
194 END IF;
195
196 l_return_status := OKL_API.start_activity(l_api_name
197 ,G_PKG_NAME
198 ,p_init_msg_list
199 ,l_api_version
200 ,p_api_version
201 ,'_PVT'
202 ,x_return_status);
203 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
204 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
205 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
206 raise OKL_API.G_EXCEPTION_ERROR;
207 END IF;
208
209 -- Set the status of the residual category set to ACTIVE
210 l_rcsv_rec.sts_code := OKL_RCS_PVT.G_STS_ACTIVE;
211
212 -- Insert row in the header table
213 okl_rcs_pvt.insert_row(
214 p_api_version => p_api_version
215 , p_init_msg_list => p_init_msg_list
216 , x_return_status => l_return_status
217 , x_msg_count => x_msg_count
218 , x_msg_data => x_msg_data
219 , p_rcsv_rec => l_rcsv_rec
220 , x_rcsv_rec => x_rcsv_rec);
221
222
223 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
224 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
225 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
226 raise OKL_API.G_EXCEPTION_ERROR;
227 END IF;
228
229 -- Assign the foreign key to the child table
230 for i IN l_res_tbl.FIRST..l_res_tbl.LAST
231 LOOP
232
233 l_res_tbl(i).resi_category_set_id := x_rcsv_rec.resi_category_set_id;
234 end loop;
235
236
237 -- Check for the presence of duplicate items or item categories
238 l_return_status := check_existence(l_rcsv_rec.source_code, p_res_tbl);
239 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
240 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
241 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
242 raise OKL_API.G_EXCEPTION_ERROR;
243 END IF;
244
245 -- Insert rows in the lines table for the respective header
246 okl_res_pvt.insert_row(
247 p_api_version => p_api_version
248 , p_init_msg_list => p_init_msg_list
249 , x_return_status => l_return_status
250 , x_msg_count => x_msg_count
251 , x_msg_data => x_msg_data
252 , p_res_tbl => l_res_tbl
253 , x_res_tbl => x_res_tbl);
254 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
255 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
256 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
257 raise OKL_API.G_EXCEPTION_ERROR;
258 END IF;
259 x_return_status := l_return_status;
260 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
261
262
263 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
264 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRRCSB.pls call create_rcs');
265 END IF;
266
267 EXCEPTION
268 WHEN G_EXCEPTION_HALT_VALIDATION then
269 -- No action necessary.
270 NULL;
271
272 WHEN OKL_API.G_EXCEPTION_ERROR THEN
273
274 x_return_status := OKL_API.HANDLE_EXCEPTIONS
275 (
276 l_api_name,
277 G_PKG_NAME,
278 'OKL_API.G_RET_STS_ERROR',
279 x_msg_count,
280 x_msg_data,
281 '_PVT'
282 );
283
284 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
285
286 x_return_status := OKL_API.HANDLE_EXCEPTIONS
287 (
288 l_api_name,
289 G_PKG_NAME,
290 'OKL_API.G_RET_STS_UNEXP_ERROR',
291 x_msg_count,
292 x_msg_data,
293 '_PVT'
294 );
295
296 WHEN OTHERS THEN
297
298 x_return_status := OKL_API.HANDLE_EXCEPTIONS
299 (
300 l_api_name,
301 G_PKG_NAME,
302 'OTHERS',
303 x_msg_count,
304 x_msg_data,
305 '_PVT'
306 );
307 end create_rcs;
308
309 /*
310 Procedure to update the residual category set. It inserts new lines if any
311 into the OKL_FE_RESI_CAT_OBJECTS table. It inactivates the residual category set if
312 any of the existing lines are not present in the inventory.
313 */
314 PROCEDURE update_rcs (
315 p_api_version IN NUMBER
316 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
317 , x_return_status OUT NOCOPY VARCHAR2
318 , x_msg_count OUT NOCOPY NUMBER
319 , x_msg_data OUT NOCOPY VARCHAR2
320 , p_rcsv_rec IN okl_rcsv_rec
321 , p_res_tbl IN okl_res_tbl
322 , x_rcsv_rec OUT NOCOPY okl_rcsv_rec
323 ) IS
324 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_RESI_CAT_SETS_PVT.update_rcs';
325 l_debug_enabled VARCHAR2(10);
326 is_debug_procedure_on BOOLEAN;
327 is_debug_statement_on BOOLEAN;
328
329 l_api_name CONSTANT VARCHAR2(40) := 'update_rcs';
330 l_api_version NUMBER := p_api_version;
331 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
332 l_return_status VARCHAR2(1) := x_return_status;
333 lp_rcsv_rec okl_rcsv_rec := p_rcsv_rec;
334 lp_res_tbl okl_res_tbl := p_res_tbl;
335 lp_res_crt_tbl okl_res_tbl;
336 lx_res_crt_tbl okl_res_tbl;
337 lp_res_upd_tbl okl_res_tbl;
338 lx_res_upd_tbl okl_res_tbl;
339
340 l_make_inactive VARCHAR2(3) := 'no';
341 j NUMBER;
342 k NUMBER;
343 BEGIN
344
345 l_debug_enabled := okl_debug_pub.check_log_enabled;
346 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
347 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
348 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRRCSB.pls call update_rcs');
349 END IF;
350 l_return_status := OKL_API.start_activity(l_api_name
351 ,G_PKG_NAME
352 ,p_init_msg_list
353 ,l_api_version
354 ,p_api_version
355 ,'_PVT'
356 ,x_return_status);
357 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
358 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
359 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
360 raise OKL_API.G_EXCEPTION_ERROR;
361 END IF;
362
363 j:=1;
364 k:=1;
365 FOR i IN p_res_tbl.FIRST..p_res_tbl.LAST
366 LOOP
367
368 IF p_res_tbl(i).resi_cat_object_id IS NULL OR p_res_tbl(i).resi_cat_object_id = OKL_API.G_MISS_NUM THEN
369 lp_res_crt_tbl(j) := p_res_tbl(i);
370 lp_res_crt_tbl(j).resi_category_set_id := p_rcsv_rec.resi_category_set_id;
371 j := j+1;
372
373 ELSE
374 lp_res_upd_tbl(k) := p_res_tbl(i);
375 k := k+1;
376 END IF;
377 END LOOP;
378
379
380 -- Check for the presence of duplicate items or item categories
381 l_return_status := check_existence(lp_rcsv_rec.source_code, p_res_tbl);
382 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
383 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
384 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
385 raise OKL_API.G_EXCEPTION_ERROR;
386 END IF;
387
388 -- Update the objects if any
389 OKL_RES_PVT.update_row(
390 p_api_version => l_api_version
391 , p_init_msg_list => l_init_msg_list
392 , x_return_status => l_return_status
393 , x_msg_count => x_msg_count
394 , x_msg_data => x_msg_data
395 , p_res_tbl => lp_res_upd_tbl
396 , x_res_tbl => lx_res_upd_tbl);
397
398 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
399 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
400 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
401 raise OKL_API.G_EXCEPTION_ERROR;
402 END IF;
403
404
405 OKL_RES_PVT.insert_row(
406 p_api_version => l_api_version
407 , p_init_msg_list => l_init_msg_list
408 , x_return_status => l_return_status
409 , x_msg_count => x_msg_count
410 , x_msg_data => x_msg_data
411 , p_res_tbl => lp_res_crt_tbl
412 , x_res_tbl => lx_res_crt_tbl);
413
414 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
415 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
416 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
417 raise OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419
420 /* Call function to check if the residual category set needs to be made Inactive */
421 l_make_inactive := check_update_status( p_rcsv_rec.source_code, lp_res_upd_tbl);
422
423
424 IF l_make_inactive = 'yes' THEN
425 lp_rcsv_rec.sts_code := G_STS_INACTIVE;
426 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
427 p_msg_name => 'OKL_RES_CAT_INACTIVATED',
428 p_token1 => 'NAME',
429 p_token1_value => lp_rcsv_rec.resi_cat_name);
430 ELSE
431 -- Set the status of the residual category set to ACTIVE
432 lp_rcsv_rec.sts_code := OKL_RCS_PVT.G_STS_ACTIVE;
433 END IF;
434
435 OKL_RCS_PVT.update_row(
436 p_api_version => l_api_version
437 , p_init_msg_list => l_init_msg_list
438 , x_return_status => l_return_status
439 , x_msg_count => x_msg_count
440 , x_msg_data => x_msg_data
441 , p_rcsv_rec => lp_rcsv_rec
442 , x_rcsv_rec => x_rcsv_rec);
443
444 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
445 RAISE OKL_API.G_EXCEPTION_ERROR;
446 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
447 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
448 END IF;
449 x_return_status := l_return_status;
450 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
451
452 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
453 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRRCSB.pls call update_rcs');
454 END IF;
455 EXCEPTION
456 WHEN G_EXCEPTION_HALT_VALIDATION then
457 -- No action necessary.
458 NULL;
459
460 WHEN OKL_API.G_EXCEPTION_ERROR THEN
461
462 x_return_status := OKL_API.HANDLE_EXCEPTIONS
463 (
464 l_api_name,
465 G_PKG_NAME,
466 'OKL_API.G_RET_STS_ERROR',
467 x_msg_count,
468 x_msg_data,
469 '_PVT'
470 );
471
472 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
473
474 x_return_status := OKL_API.HANDLE_EXCEPTIONS
475 (
476 l_api_name,
477 G_PKG_NAME,
478 'OKL_API.G_RET_STS_UNEXP_ERROR',
479 x_msg_count,
480 x_msg_data,
481 '_PVT'
482 );
483
484 WHEN OTHERS THEN
485
486 x_return_status := OKL_API.HANDLE_EXCEPTIONS
487 (
488 l_api_name,
489 G_PKG_NAME,
490 'OTHERS',
491 x_msg_count,
492 x_msg_data,
493 '_PVT'
494 );
495 END update_rcs;
496
497 /*
498 Procedure to activate the residual category set. It inactivates the residual
499 category set if any of the existing lines are not present in the inventory.
500 */
501
502 PROCEDURE activate_rcs (
503 p_api_version IN NUMBER
504 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
505 , x_return_status OUT NOCOPY VARCHAR2
506 , x_msg_count OUT NOCOPY NUMBER
507 , x_msg_data OUT NOCOPY VARCHAR2
508 , p_rcsv_rec IN okl_rcsv_rec
509 , p_res_tbl IN okl_res_tbl
510 , x_rcsv_rec OUT NOCOPY okl_rcsv_rec
511 ) IS
512 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_RESI_CAT_SETS_PVT.activate_rcs';
513 l_debug_enabled VARCHAR2(10);
514 is_debug_procedure_on BOOLEAN;
515 is_debug_statement_on BOOLEAN;
516
517 l_api_name CONSTANT VARCHAR2(40) := 'activate_rcs';
518 l_api_version NUMBER := p_api_version;
519 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
520 l_return_status VARCHAR2(1) := x_return_status;
521 lp_rcsv_rec okl_rcsv_rec := p_rcsv_rec;
522
523 l_make_inactive VARCHAR2(3) := 'no';
524 BEGIN
525 l_debug_enabled := okl_debug_pub.check_log_enabled;
526 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
527 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
528 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRRCSB.pls call activate_rcs');
529 END IF;
530
531 l_return_status := OKL_API.start_activity(l_api_name
532 ,G_PKG_NAME
533 ,p_init_msg_list
534 ,l_api_version
535 ,p_api_version
536 ,'_PVT'
537 ,l_return_status);
538 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
539 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
540 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
541 raise OKL_API.G_EXCEPTION_ERROR;
542 END IF;
543
544
545
546 /* Call function to check if the residual category set needs to be made Inactive */
547 l_make_inactive := check_update_status( p_rcsv_rec.source_code, p_res_tbl);
548
549
550 IF l_make_inactive = 'yes' THEN
551 lp_rcsv_rec.sts_code := G_STS_INACTIVE;
552 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
553 p_msg_name => 'OKL_RES_CAT_INACTIVE');
554 ELSE
555 lp_rcsv_rec.sts_code := G_STS_ACTIVE;
556
557 OKL_RCS_PVT.update_row(
558 p_api_version => l_api_version
559 , p_init_msg_list => l_init_msg_list
560 , x_return_status => l_return_status
561 , x_msg_count => x_msg_count
562 , x_msg_data => x_msg_data
563 , p_rcsv_rec => lp_rcsv_rec
564 , x_rcsv_rec => x_rcsv_rec);
565 END IF;
566
567
568
569 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
570 RAISE OKL_API.G_EXCEPTION_ERROR;
571 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
572 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
573 END IF;
574 x_return_status := l_return_status;
575 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
576
577
578 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
579 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRRCSB.pls call update_rcs');
580 END IF;
581 EXCEPTION
582 WHEN G_EXCEPTION_HALT_VALIDATION then
583 -- No action necessary.
584 NULL;
585
586 WHEN OKL_API.G_EXCEPTION_ERROR THEN
587
588 x_return_status := OKL_API.HANDLE_EXCEPTIONS
589 (
590 l_api_name,
591 G_PKG_NAME,
592 'OKL_API.G_RET_STS_ERROR',
593 x_msg_count,
594 x_msg_data,
595 '_PVT'
596 );
597
598 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
599
600 x_return_status := OKL_API.HANDLE_EXCEPTIONS
601 (
602 l_api_name,
603 G_PKG_NAME,
604 'OKL_API.G_RET_STS_UNEXP_ERROR',
605 x_msg_count,
606 x_msg_data,
607 '_PVT'
608 );
609
610 WHEN OTHERS THEN
611
612 x_return_status := OKL_API.HANDLE_EXCEPTIONS
613 (
614 l_api_name,
615 G_PKG_NAME,
616 'OTHERS',
617 x_msg_count,
618 x_msg_data,
619 '_PVT'
620 );
621 END activate_rcs;
622
623 /*
624 Procedure to Inactivate the residual category set.
625 */
626
627 PROCEDURE Inactivate_rcs (
628 p_api_version IN NUMBER
629 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
630 , x_return_status OUT NOCOPY VARCHAR2
631 , x_msg_count OUT NOCOPY NUMBER
632 , x_msg_data OUT NOCOPY VARCHAR2
633 , p_rcs_id IN NUMBER
634 , p_obj_ver_number IN NUMBER
635 ) IS
636 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_RESI_CAT_SETS_PVT.Inactivate_rcs';
637 l_debug_enabled VARCHAR2(10);
638 is_debug_procedure_on BOOLEAN;
639 is_debug_statement_on BOOLEAN;
640
641 l_api_name CONSTANT VARCHAR2(40) := 'Inactivate_rcs';
642 l_api_version NUMBER := p_api_version;
643 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
644 l_return_status VARCHAR2(1) := x_return_status;
645 lp_rcsv_rec okl_rcsv_rec;
646 x_rcsv_rec okl_rcsv_rec ;
647 BEGIN
648 l_debug_enabled := okl_debug_pub.check_log_enabled;
649 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
650 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
651 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRRCSB.pls call Inactivate_rcs');
652 END IF;
653 l_return_status := OKL_API.start_activity(l_api_name
654 ,G_PKG_NAME
655 ,p_init_msg_list
656 ,l_api_version
657 ,p_api_version
658 ,'_PVT'
659 ,x_return_status);
660 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
661 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
662 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
663 raise OKL_API.G_EXCEPTION_ERROR;
664 END IF;
665
666 lp_rcsv_rec.resi_category_set_id := p_rcs_id;
667 lp_rcsv_rec.sts_code := G_STS_INACTIVE;
668 lp_rcsv_rec.object_version_number := p_obj_ver_number;
669
670 OKL_RCS_PVT.update_row(
671 p_api_version => l_api_version
672 , p_init_msg_list => l_init_msg_list
673 , x_return_status => l_return_status
674 , x_msg_count => x_msg_count
675 , x_msg_data => x_msg_data
676 , p_rcsv_rec => lp_rcsv_rec
677 , x_rcsv_rec => x_rcsv_rec);
678
679
680 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
681 RAISE OKL_API.G_EXCEPTION_ERROR;
682 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
683 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
684 END IF;
685 x_return_status := l_return_status;
686 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
687
688 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
689 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRRCSB.pls call Inactivate_rcs');
690 END IF;
691 EXCEPTION
692 WHEN G_EXCEPTION_HALT_VALIDATION then
693 -- No action necessary.
694 NULL;
695
696 WHEN OKL_API.G_EXCEPTION_ERROR THEN
697
698 x_return_status := OKL_API.HANDLE_EXCEPTIONS
699 (
700 l_api_name,
701 G_PKG_NAME,
702 'OKL_API.G_RET_STS_ERROR',
703 x_msg_count,
704 x_msg_data,
705 '_PVT'
706 );
707
708 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
709
710 x_return_status := OKL_API.HANDLE_EXCEPTIONS
711 (
712 l_api_name,
713 G_PKG_NAME,
714 'OKL_API.G_RET_STS_UNEXP_ERROR',
715 x_msg_count,
716 x_msg_data,
717 '_PVT'
718 );
719
720 WHEN OTHERS THEN
721
722 x_return_status := OKL_API.HANDLE_EXCEPTIONS
723 (
724 l_api_name,
725 G_PKG_NAME,
726 'OTHERS',
727 x_msg_count,
728 x_msg_data,
729 '_PVT'
730 );
731 END Inactivate_rcs;
732
733 PROCEDURE delete_objects(
734 p_api_version IN NUMBER
735 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
736 , x_return_status OUT NOCOPY VARCHAR2
737 , x_msg_count OUT NOCOPY NUMBER
738 , x_msg_data OUT NOCOPY VARCHAR2
739 , p_res_tbl IN okl_res_tbl) IS
740 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_RESI_CAT_SETS_PVT.delete_objects';
741 l_debug_enabled VARCHAR2(10);
742 is_debug_procedure_on BOOLEAN;
743 is_debug_statement_on BOOLEAN;
744
745 l_api_name CONSTANT VARCHAR2(40) := 'delete_objects';
746 l_api_version NUMBER := p_api_version;
747 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
748 l_return_status VARCHAR2(1) := x_return_status;
749 BEGIN
750
751
752 l_debug_enabled := okl_debug_pub.check_log_enabled;
753 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
754 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
755 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRRCSB.pls call delete_objects');
756 END IF;
757 l_return_status := OKL_API.start_activity(l_api_name
758 ,G_PKG_NAME
759 ,p_init_msg_list
760 ,l_api_version
761 ,p_api_version
762 ,'_PVT'
763 ,x_return_status);
764 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
765 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
766 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
767 raise OKL_API.G_EXCEPTION_ERROR;
768 END IF;
769
770 OKL_RES_PVT.delete_row(
771 p_api_version => l_api_version
772 , p_init_msg_list => l_init_msg_list
773 , x_return_status => l_return_status
774 , x_msg_count => x_msg_count
775 , x_msg_data => x_msg_data
776 , p_res_tbl => p_res_tbl);
777
778 if l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
779 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
780 elsif l_return_status = OKL_API.G_RET_STS_ERROR then
781 raise OKL_API.G_EXCEPTION_ERROR;
782 END IF;
783
784 x_return_status := l_return_status;
785
786 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
787
788 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
789 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRRCSB.pls call delete_objects');
790 END IF;
791 EXCEPTION
792 WHEN OKL_API.G_EXCEPTION_ERROR THEN
793 x_return_status := OKL_API.HANDLE_EXCEPTIONS
794 (
795 l_api_name,
796 G_PKG_NAME,
797 'OKL_API.G_RET_STS_ERROR',
798 x_msg_count,
799 x_msg_data,
800 '_PVT'
801 );
802
803 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
804
805 x_return_status := OKL_API.HANDLE_EXCEPTIONS
806 (
807 l_api_name,
808 G_PKG_NAME,
809 'OKL_API.G_RET_STS_UNEXP_ERROR',
810 x_msg_count,
811 x_msg_data,
812 '_PVT'
813 );
814
815 WHEN OTHERS THEN
816
817 x_return_status := OKL_API.HANDLE_EXCEPTIONS
818 (
819 l_api_name,
820 G_PKG_NAME,
821 'OTHERS',
822 x_msg_count,
823 x_msg_data,
824 '_PVT'
825 );
826 END delete_objects;
827
828 END OKL_RESI_CAT_SETS_PVT;