[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_GROUP_PVT
Source
1 package body okc_contract_group_pvt as
2 /* $Header: OKCCCGPB.pls 120.1 2011/05/02 04:14:16 skuchima ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 PROCEDURE add_language IS
7 BEGIN
8 okc_cgp_pvt.add_language;
9 END;
10
11 PROCEDURE create_ctr_group(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 ,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_cgpv_rec IN cgpv_rec_type,
18 x_cgpv_rec OUT NOCOPY cgpv_rec_type,
19 p_cgcv_tbl IN cgcv_tbl_type,
20 x_cgcv_tbl OUT NOCOPY cgcv_tbl_type) IS
21 l_cgcv_tbl cgcv_tbl_type := p_cgcv_tbl;
22 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
23 i NUMBER := 0;
24 BEGIN
25 create_contract_group(
26 p_api_version,
27 p_init_msg_list,
28 x_return_status,
29 x_msg_count,
30 x_msg_data,
31 p_cgpv_rec,
32 x_cgpv_rec);
33 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
34 raise G_EXCEPTION_HALT_VALIDATION;
35 ELSE
36 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
37 l_return_status := x_return_status;
38 END IF;
39 END IF;
40
41 IF (l_cgcv_tbl.COUNT > 0) THEN
42 i := l_cgcv_tbl.FIRST;
43 LOOP
44 l_cgcv_tbl(i).cgp_parent_id := x_cgpv_rec.id;
45 EXIT WHEN (i = l_cgcv_tbl.LAST);
46 i := l_cgcv_tbl.NEXT(i);
47 END LOOP;
48 END IF;
49 create_contract_grpngs(
50 p_api_version,
51 p_init_msg_list,
52 x_return_status,
53 x_msg_count,
54 x_msg_data,
55 l_cgcv_tbl,
56 x_cgcv_tbl);
57 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
58 raise G_EXCEPTION_HALT_VALIDATION;
59 ELSE
60 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
61 l_return_status := x_return_status;
62 END IF;
63 END IF;
64 x_return_status := l_return_status;
65 EXCEPTION
66 WHEN G_EXCEPTION_HALT_VALIDATION THEN
67 NULL;
68 WHEN OTHERS THEN
69 OKC_API.set_message(p_app_name => g_app_name,
70 p_msg_name => g_unexpected_error,
71 p_token1 => g_sqlcode_token,
72 p_token1_value => sqlcode,
73 p_token2 => g_sqlerrm_token,
74 p_token2_value => sqlerrm);
75 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
76 END create_ctr_group;
77
78 PROCEDURE update_ctr_group(
79 p_api_version IN NUMBER,
80 p_init_msg_list IN VARCHAR2 ,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2,
84 p_cgpv_rec IN cgpv_rec_type,
85 x_cgpv_rec OUT NOCOPY cgpv_rec_type,
86 p_cgcv_tbl IN cgcv_tbl_type,
87 x_cgcv_tbl OUT NOCOPY cgcv_tbl_type) IS
88 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
89 BEGIN
90 update_contract_group(
91 p_api_version,
92 p_init_msg_list,
93 x_return_status,
94 x_msg_count,
95 x_msg_data,
96 p_cgpv_rec,
97 x_cgpv_rec);
98 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
99 raise G_EXCEPTION_HALT_VALIDATION;
100 ELSE
101 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
102 l_return_status := x_return_status;
103 END IF;
104 END IF;
105
106 update_contract_grpngs(
107 p_api_version,
108 p_init_msg_list,
109 x_return_status,
110 x_msg_count,
111 x_msg_data,
112 p_cgcv_tbl,
113 x_cgcv_tbl);
114 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
115 raise G_EXCEPTION_HALT_VALIDATION;
116 ELSE
117 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
118 l_return_status := x_return_status;
119 END IF;
120 END IF;
121 x_return_status := l_return_status;
122 EXCEPTION
123 WHEN G_EXCEPTION_HALT_VALIDATION THEN
124 NULL;
125 WHEN OTHERS THEN
126 OKC_API.set_message(p_app_name => g_app_name,
127 p_msg_name => g_unexpected_error,
128 p_token1 => g_sqlcode_token,
129 p_token1_value => sqlcode,
130 p_token2 => g_sqlerrm_token,
131 p_token2_value => sqlerrm);
132 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
133 END update_ctr_group;
134
135 PROCEDURE validate_ctr_group(
136 p_api_version IN NUMBER,
137 p_init_msg_list IN VARCHAR2 ,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2,
141 p_cgpv_rec IN cgpv_rec_type,
142 p_cgcv_tbl IN cgcv_tbl_type) IS
143 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
144 BEGIN
145 validate_contract_group(
146 p_api_version,
147 p_init_msg_list,
148 x_return_status,
149 x_msg_count,
150 x_msg_data,
151 p_cgpv_rec);
152 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
153 raise G_EXCEPTION_HALT_VALIDATION;
154 ELSE
155 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
156 l_return_status := x_return_status;
157 END IF;
158 END IF;
159
160 validate_contract_grpngs(
161 p_api_version,
162 p_init_msg_list,
163 x_return_status,
164 x_msg_count,
165 x_msg_data,
166 p_cgcv_tbl);
167 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
168 raise G_EXCEPTION_HALT_VALIDATION;
169 ELSE
170 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
171 l_return_status := x_return_status;
172 END IF;
173 END IF;
174 x_return_status := l_return_status;
175 EXCEPTION
176 WHEN G_EXCEPTION_HALT_VALIDATION THEN
177 NULL;
178 WHEN OTHERS THEN
179 OKC_API.set_message(p_app_name => g_app_name,
180 p_msg_name => g_unexpected_error,
181 p_token1 => g_sqlcode_token,
182 p_token1_value => sqlcode,
183 p_token2 => g_sqlerrm_token,
184 p_token2_value => sqlerrm);
185 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
186 END validate_ctr_group;
187
188 PROCEDURE create_contract_group(
189 p_api_version IN NUMBER,
190 p_init_msg_list IN VARCHAR2 ,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2,
194 p_cgpv_rec IN cgpv_rec_type,
195 x_cgpv_rec OUT NOCOPY cgpv_rec_type) IS
196 BEGIN
197 okc_cgp_pvt.insert_row(
198 p_api_version,
199 p_init_msg_list,
200 x_return_status,
201 x_msg_count,
202 x_msg_data,
203 p_cgpv_rec,
204 x_cgpv_rec);
205 END create_contract_group;
206
207 PROCEDURE create_contract_group(
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 ,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_cgpv_tbl IN cgpv_tbl_type,
214 x_cgpv_tbl OUT NOCOPY cgpv_tbl_type) IS
215 BEGIN
216 okc_cgp_pvt.insert_row(
217 p_api_version,
218 p_init_msg_list,
219 x_return_status,
220 x_msg_count,
221 x_msg_data,
222 p_cgpv_tbl,
223 x_cgpv_tbl);
224 END create_contract_group;
225
226 PROCEDURE update_contract_group(
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 ,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2,
232 p_cgpv_rec IN cgpv_rec_type,
233 x_cgpv_rec OUT NOCOPY cgpv_rec_type) IS
234 BEGIN
235 okc_cgp_pvt.update_row(
236 p_api_version,
237 p_init_msg_list,
238 x_return_status,
239 x_msg_count,
240 x_msg_data,
241 p_cgpv_rec,
242 x_cgpv_rec);
243 END update_contract_group;
244
245 PROCEDURE update_contract_group(
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 ,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 p_cgpv_tbl IN cgpv_tbl_type,
252 x_cgpv_tbl OUT NOCOPY cgpv_tbl_type) IS
253 BEGIN
254 okc_cgp_pvt.update_row(
255 p_api_version,
256 p_init_msg_list,
257 x_return_status,
258 x_msg_count,
259 x_msg_data,
260 p_cgpv_tbl,
261 x_cgpv_tbl);
262 END update_contract_group;
263
264 PROCEDURE delete_contract_group(
265 p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 ,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2,
270 p_cgpv_rec IN cgpv_rec_type) IS
271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
272 ----------------------------------------------------
273 -- FUNCTION delete_parentgroups --
274 ----------------------------------------------------
275 FUNCTION delete_parentgroups(
276 p_cgpv_rec IN cgpv_rec_type
277 ) RETURN VARCHAR2 IS
278 CURSOR okc_cgcv_included_csr (p_included_cgp_id IN NUMBER) IS
279 SELECT id
280 FROM OKC_K_GRPINGS
281 WHERE included_cgp_id = p_included_cgp_id;
282 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
283 l_index NUMBER := 1;
284 l_cgcv_tbl okc_contract_group_pub.cgcv_tbl_type;
285 BEGIN
286 IF p_cgpv_rec.id IS NOT NULL THEN
287 FOR c1 IN okc_cgcv_included_csr(p_cgpv_rec.id) LOOP
288 l_cgcv_tbl(l_index).id := c1.id;
289 l_index := l_index + 1;
290 END LOOP;
291 IF l_cgcv_tbl.COUNT > 0 THEN
292 okc_contract_group_pub.delete_contract_grpngs(
293 p_api_version => p_api_version,
294 p_init_msg_list => p_init_msg_list,
295 x_return_status => l_return_status,
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data,
298 p_cgcv_tbl => l_cgcv_tbl);
299 END IF;
300 END IF;
301 RETURN (l_return_status);
302 EXCEPTION
303 WHEN OTHERS THEN
304 OKC_API.set_message(p_app_name => g_app_name,
305 p_msg_name => g_unexpected_error,
306 p_token1 => g_sqlcode_token,
307 p_token1_value => sqlcode,
308 p_token2 => g_sqlerrm_token,
309 p_token2_value => sqlerrm);
310 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
311 RETURN (l_return_status);
312 END delete_parentgroups;
313 ----------------------------------------------------
314 -- FUNCTION delete_subgroups --
315 ----------------------------------------------------
316 FUNCTION delete_subgroups(
317 p_cgpv_rec IN cgpv_rec_type
318 ) RETURN VARCHAR2 IS
319 child_record_error EXCEPTION;
320 CURSOR okc_cgcv_parent_csr (p_cgp_parent_id IN NUMBER) IS
321 SELECT id
322 FROM OKC_K_GRPINGS
323 WHERE cgp_parent_id = p_cgp_parent_id;
324 l_dummy VARCHAR2(1);
325 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
326 l_row_found BOOLEAN := FALSE;
327 l_index NUMBER := 1;
328 l_cgcv_tbl okc_contract_group_pub.cgcv_tbl_type;
329 BEGIN
330 IF p_cgpv_rec.id IS NOT NULL THEN
331 FOR c1 IN okc_cgcv_parent_csr(p_cgpv_rec.id) LOOP
332 l_cgcv_tbl(l_index).id := c1.id;
333 l_index := l_index + 1;
334 END LOOP;
335 IF l_cgcv_tbl.COUNT > 0 THEN
336 okc_contract_group_pub.delete_contract_grpngs(
337 p_api_version => p_api_version,
338 p_init_msg_list => p_init_msg_list,
339 x_return_status => l_return_status,
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_cgcv_tbl => l_cgcv_tbl);
343 END IF;
344 END IF;
345 RETURN (l_return_status);
346 EXCEPTION
347 WHEN OTHERS THEN
348 OKC_API.set_message(p_app_name => g_app_name,
349 p_msg_name => g_unexpected_error,
350 p_token1 => g_sqlcode_token,
351 p_token1_value => sqlcode,
352 p_token2 => g_sqlerrm_token,
353 p_token2_value => sqlerrm);
354 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
355 RETURN (l_return_status);
356 END delete_subgroups;
357 ----------------------------------------------------
358 BEGIN
359 l_return_status := delete_subgroups(p_cgpv_rec);
360 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
361 raise G_EXCEPTION_HALT_VALIDATION;
362 END IF;
363 l_return_status := delete_parentgroups(p_cgpv_rec);
364 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
365 raise G_EXCEPTION_HALT_VALIDATION;
366 END IF;
367 okc_cgp_pvt.delete_row(
368 p_api_version,
369 p_init_msg_list,
370 x_return_status,
371 x_msg_count,
372 x_msg_data,
373 p_cgpv_rec);
374 EXCEPTION
375 WHEN G_EXCEPTION_HALT_VALIDATION THEN
376 x_return_status := l_return_status;
377 WHEN OTHERS THEN
378 OKC_API.set_message(p_app_name => g_app_name,
379 p_msg_name => g_unexpected_error,
380 p_token1 => g_sqlcode_token,
381 p_token1_value => sqlcode,
382 p_token2 => g_sqlerrm_token,
383 p_token2_value => sqlerrm);
384 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
385 END delete_contract_group;
386
387 PROCEDURE delete_contract_group(
388 p_api_version IN NUMBER,
389 p_init_msg_list IN VARCHAR2 ,
390 x_return_status OUT NOCOPY VARCHAR2,
391 x_msg_count OUT NOCOPY NUMBER,
392 x_msg_data OUT NOCOPY VARCHAR2,
393 p_cgpv_tbl IN cgpv_tbl_type) IS
394 i NUMBER := 0;
395 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
396 BEGIN
397 x_return_status := OKC_API.G_RET_STS_SUCCESS;
398 IF (p_cgpv_tbl.COUNT > 0) THEN
399 i := p_cgpv_tbl.FIRST;
400 LOOP
401 delete_contract_group(
402 p_api_version,
403 p_init_msg_list,
404 l_return_status,
405 x_msg_count,
406 x_msg_data,
407 p_cgpv_tbl(i));
408 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
409 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
410 x_return_status := l_return_status;
411 raise G_EXCEPTION_HALT_VALIDATION;
412 ELSE
413 x_return_status := l_return_status;
414 END IF;
415 END IF;
416 EXIT WHEN (i = p_cgpv_tbl.LAST);
417 i := p_cgpv_tbl.NEXT(i);
418 END LOOP;
419 END IF;
420 EXCEPTION
421 WHEN G_EXCEPTION_HALT_VALIDATION THEN
422 NULL;
423 WHEN OTHERS THEN
424 OKC_API.set_message(p_app_name => g_app_name,
425 p_msg_name => g_unexpected_error,
426 p_token1 => g_sqlcode_token,
427 p_token1_value => sqlcode,
428 p_token2 => g_sqlerrm_token,
429 p_token2_value => sqlerrm);
430 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
431 END delete_contract_group;
432
433 PROCEDURE lock_contract_group(
434 p_api_version IN NUMBER,
435 p_init_msg_list IN VARCHAR2 ,
436 x_return_status OUT NOCOPY VARCHAR2,
437 x_msg_count OUT NOCOPY NUMBER,
438 x_msg_data OUT NOCOPY VARCHAR2,
439 p_cgpv_rec IN cgpv_rec_type) IS
440 BEGIN
441 okc_cgp_pvt.lock_row(
442 p_api_version,
443 p_init_msg_list,
444 x_return_status,
445 x_msg_count,
446 x_msg_data,
447 p_cgpv_rec);
448 END lock_contract_group;
449
450 PROCEDURE lock_contract_group(
451 p_api_version IN NUMBER,
452 p_init_msg_list IN VARCHAR2 ,
453 x_return_status OUT NOCOPY VARCHAR2,
454 x_msg_count OUT NOCOPY NUMBER,
455 x_msg_data OUT NOCOPY VARCHAR2,
456 p_cgpv_tbl IN cgpv_tbl_type) IS
457 BEGIN
458 okc_cgp_pvt.lock_row(
459 p_api_version,
460 p_init_msg_list,
461 x_return_status,
462 x_msg_count,
463 x_msg_data,
464 p_cgpv_tbl);
465 END lock_contract_group;
466
467 PROCEDURE validate_contract_group(
468 p_api_version IN NUMBER,
469 p_init_msg_list IN VARCHAR2 ,
470 x_return_status OUT NOCOPY VARCHAR2,
471 x_msg_count OUT NOCOPY NUMBER,
472 x_msg_data OUT NOCOPY VARCHAR2,
473 p_cgpv_rec IN cgpv_rec_type) IS
474 BEGIN
475 okc_cgp_pvt.validate_row(
476 p_api_version,
477 p_init_msg_list,
478 x_return_status,
479 x_msg_count,
480 x_msg_data,
481 p_cgpv_rec);
482 END validate_contract_group;
483
484 PROCEDURE validate_contract_group(
485 p_api_version IN NUMBER,
486 p_init_msg_list IN VARCHAR2 ,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2,
490 p_cgpv_tbl IN cgpv_tbl_type) IS
491 BEGIN
492 okc_cgp_pvt.validate_row(
493 p_api_version,
494 p_init_msg_list,
495 x_return_status,
496 x_msg_count,
497 x_msg_data,
498 p_cgpv_tbl);
499 END validate_contract_group;
500 ------------------------------------
501 -- FUNCTION validate_recursion --
502 ------------------------------------
503 FUNCTION validate_recursion(
504 p_cgcv_rec IN cgcv_rec_type
505 ) RETURN VARCHAR2 IS
506 recursive_error EXCEPTION;
507 CURSOR okc_cgcv_csr (p_cgp_parent_id IN NUMBER,
508 p_included_cgp_id IN NUMBER) IS
509 SELECT 'x'
510 FROM OKC_K_GRPINGS
511 WHERE included_cgp_id = p_cgp_parent_id
512 START WITH cgp_parent_id = p_included_cgp_id and included_cgp_id > 0
513 CONNECT BY PRIOR included_cgp_id = cgp_parent_id and included_cgp_id > 0; --bug 11881768
514 l_dummy VARCHAR2(1);
515 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
516 l_row_found BOOLEAN := FALSE;
517 BEGIN
518 IF (p_cgcv_rec.cgp_parent_id IS NOT NULL AND
519 p_cgcv_rec.included_cgp_id IS NOT NULL) THEN
520 IF p_cgcv_rec.cgp_parent_id = p_cgcv_rec.included_cgp_id THEN
521 OKC_API.SET_MESSAGE('OKC', 'OKC_INVALID_CGP_PARENT_ID');
522 ----OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CGP_PARENT_ID');
523 RAISE recursive_error;
524 END IF;
525 OPEN okc_cgcv_csr(p_cgcv_rec.cgp_parent_id,
526 p_cgcv_rec.included_cgp_id);
527 FETCH okc_cgcv_csr INTO l_dummy;
528 l_row_found := okc_cgcv_csr%FOUND;
529 CLOSE okc_cgcv_csr;
530 IF (l_row_found) THEN
531 OKC_API.SET_MESSAGE('OKC', 'OKC_INVALID_CGP_PARENT_ID');
532 --- OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CGP_PARENT_ID');
533 RAISE recursive_error;
534 END IF;
535 END IF;
536 RETURN (l_return_status);
537 EXCEPTION
538 WHEN recursive_error THEN
539 l_return_status := OKC_API.G_RET_STS_ERROR;
540 RETURN (l_return_status);
541 END validate_recursion;
542 ----------------------------------------------------
543 ------------------------------------
544 -- FUNCTION check_group_type --
545 ------------------------------------
546 -- Make sure that a public group consists only of public
547 -- groups. It cannot have private groups as its member.
548 FUNCTION check_group_type(
549 p_cgcv_rec IN cgcv_rec_type
550 ) RETURN VARCHAR2 IS
551 invalid_group_error EXCEPTION;
552 CURSOR okc_cgpv_csr (p_id IN NUMBER) IS
553 SELECT public_yn,
554 user_id
555 FROM OKC_K_GROUPS_B
556 WHERE id = p_id;
557 l_group_public_yn OKC_K_GROUPS_B.PUBLIC_YN%TYPE;
558 l_subgroup_public_yn OKC_K_GROUPS_B.PUBLIC_YN%TYPE;
559 l_group_user_id OKC_K_GROUPS_B.USER_ID%TYPE;
560 l_subgroup_user_id OKC_K_GROUPS_B.USER_ID%TYPE;
561 l_user_id NUMBER(15) := TO_NUMBER(fnd_profile.value('USER_ID'));
562 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
563 BEGIN
564 OPEN okc_cgpv_csr(p_cgcv_rec.cgp_parent_id);
565 FETCH okc_cgpv_csr
566 INTO l_group_public_yn,
567 l_group_user_id;
568 CLOSE okc_cgpv_csr;
569 IF p_cgcv_rec.included_cgp_id IS NOT NULL THEN
570 OPEN okc_cgpv_csr(p_cgcv_rec.included_cgp_id);
571 FETCH okc_cgpv_csr
572 INTO l_subgroup_public_yn,
573 l_subgroup_user_id;
574 CLOSE okc_cgpv_csr;
575 IF l_group_public_yn = 'Y' THEN
576 -- The parent is a public group
577 IF l_subgroup_public_yn = 'N' THEN
578 -- The member is not a public group
579 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'INCLUDED_CGP_ID');
580 RAISE invalid_group_error;
581 END IF;
582 ELSE
583 IF l_subgroup_public_yn = 'N' THEN
584 -- Make sure that only the current user can manipulate his private groups and
585 -- private subgroups.
586 IF (l_group_user_id <> l_subgroup_user_id) OR
587 (l_group_user_id <> l_user_id) OR
588 (l_subgroup_user_id <> l_user_id) THEN
589 OKC_API.set_message(G_APP_NAME, 'OKC_PRIVATE_GROUP_ERROR');
590 RAISE invalid_group_error;
591 END IF;
592 END IF;
593 END IF;
594 ELSIF p_cgcv_rec.included_chr_id IS NOT NULL THEN
595 IF (l_group_public_yn = 'N') AND
596 (l_group_user_id <> l_user_id) THEN
597 OKC_API.set_message(G_APP_NAME, 'OKC_PRIVATE_GROUP_ERROR');
598 RAISE invalid_group_error;
599 END IF;
600 END IF;
601 RETURN (l_return_status);
602 EXCEPTION
603 WHEN invalid_group_error THEN
604 l_return_status := OKC_API.G_RET_STS_ERROR;
605 RETURN (l_return_status);
606 END check_group_type;
607 ----------------------------------------------------
608 PROCEDURE create_contract_grpngs(
609 p_api_version IN NUMBER,
610 p_init_msg_list IN VARCHAR2 ,
611 x_return_status OUT NOCOPY VARCHAR2,
612 x_msg_count OUT NOCOPY NUMBER,
613 x_msg_data OUT NOCOPY VARCHAR2,
614 p_cgcv_rec IN cgcv_rec_type,
615 x_cgcv_rec OUT NOCOPY cgcv_rec_type) IS
616 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
617 BEGIN
618 l_return_status := validate_recursion(p_cgcv_rec);
619 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
620 raise G_EXCEPTION_HALT_VALIDATION;
621 END IF;
622 okc_cgc_pvt.insert_row(
623 p_api_version,
624 p_init_msg_list,
625 l_return_status,
626 x_msg_count,
627 x_msg_data,
628 p_cgcv_rec,
629 x_cgcv_rec);
630 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
631 raise G_EXCEPTION_HALT_VALIDATION;
632 END IF;
633 l_return_status := check_group_type(p_cgcv_rec);
634 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
635 raise G_EXCEPTION_HALT_VALIDATION;
636 END IF;
637 x_return_status := l_return_status;
638 EXCEPTION
639 WHEN G_EXCEPTION_HALT_VALIDATION THEN
640 x_return_status := l_return_status;
641 WHEN OTHERS THEN
642 OKC_API.set_message(p_app_name => g_app_name,
643 p_msg_name => g_unexpected_error,
644 p_token1 => g_sqlcode_token,
645 p_token1_value => sqlcode,
646 p_token2 => g_sqlerrm_token,
647 p_token2_value => sqlerrm);
648 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
649 END create_contract_grpngs;
650
651 PROCEDURE create_contract_grpngs(
652 p_api_version IN NUMBER,
653 p_init_msg_list IN VARCHAR2 ,
654 x_return_status OUT NOCOPY VARCHAR2,
655 x_msg_count OUT NOCOPY NUMBER,
656 x_msg_data OUT NOCOPY VARCHAR2,
657 p_cgcv_tbl IN cgcv_tbl_type,
658 x_cgcv_tbl OUT NOCOPY cgcv_tbl_type) IS
659 i NUMBER := 0;
660 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
661 BEGIN
662 x_return_status := OKC_API.G_RET_STS_SUCCESS;
663 IF (p_cgcv_tbl.COUNT > 0) THEN
664 i := p_cgcv_tbl.FIRST;
665 LOOP
666 create_contract_grpngs(
667 p_api_version,
668 p_init_msg_list,
669 l_return_status,
670 x_msg_count,
671 x_msg_data,
672 p_cgcv_tbl(i),
673 x_cgcv_tbl(i));
674 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
675 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
676 x_return_status := l_return_status;
677 raise G_EXCEPTION_HALT_VALIDATION;
678 ELSE
679 x_return_status := l_return_status;
680 END IF;
681 END IF;
682 EXIT WHEN (i = p_cgcv_tbl.LAST);
683 i := p_cgcv_tbl.NEXT(i);
684 END LOOP;
685 END IF;
686 EXCEPTION
687 WHEN G_EXCEPTION_HALT_VALIDATION THEN
688 NULL;
689 WHEN OTHERS THEN
690 OKC_API.set_message(p_app_name => g_app_name,
691 p_msg_name => g_unexpected_error,
692 p_token1 => g_sqlcode_token,
693 p_token1_value => sqlcode,
694 p_token2 => g_sqlerrm_token,
695 p_token2_value => sqlerrm);
696 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
697 END create_contract_grpngs;
698
699 PROCEDURE update_contract_grpngs(
700 p_api_version IN NUMBER,
701 p_init_msg_list IN VARCHAR2 ,
702 x_return_status OUT NOCOPY VARCHAR2,
703 x_msg_count OUT NOCOPY NUMBER,
704 x_msg_data OUT NOCOPY VARCHAR2,
705 p_cgcv_rec IN cgcv_rec_type,
706 x_cgcv_rec OUT NOCOPY cgcv_rec_type) IS
707 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
708 BEGIN
709 l_return_status := validate_recursion(p_cgcv_rec);
710 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
711 raise G_EXCEPTION_HALT_VALIDATION;
712 END IF;
713 okc_cgc_pvt.update_row(
714 p_api_version,
715 p_init_msg_list,
716 l_return_status,
717 x_msg_count,
718 x_msg_data,
719 p_cgcv_rec,
720 x_cgcv_rec);
721 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
722 raise G_EXCEPTION_HALT_VALIDATION;
723 END IF;
724 l_return_status := check_group_type(p_cgcv_rec);
725 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
726 raise G_EXCEPTION_HALT_VALIDATION;
727 END IF;
728 x_return_status := l_return_status;
729 EXCEPTION
730 WHEN G_EXCEPTION_HALT_VALIDATION THEN
731 x_return_status := l_return_status;
732 WHEN OTHERS THEN
733 OKC_API.set_message(p_app_name => g_app_name,
734 p_msg_name => g_unexpected_error,
735 p_token1 => g_sqlcode_token,
736 p_token1_value => sqlcode,
737 p_token2 => g_sqlerrm_token,
738 p_token2_value => sqlerrm);
739 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
740 END update_contract_grpngs;
741
742 PROCEDURE update_contract_grpngs(
743 p_api_version IN NUMBER,
744 p_init_msg_list IN VARCHAR2 ,
745 x_return_status OUT NOCOPY VARCHAR2,
746 x_msg_count OUT NOCOPY NUMBER,
747 x_msg_data OUT NOCOPY VARCHAR2,
748 p_cgcv_tbl IN cgcv_tbl_type,
749 x_cgcv_tbl OUT NOCOPY cgcv_tbl_type) IS
750 i NUMBER := 0;
751 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
752 BEGIN
753 x_return_status := OKC_API.G_RET_STS_SUCCESS;
754 IF (p_cgcv_tbl.COUNT > 0) THEN
755 i := p_cgcv_tbl.FIRST;
756 LOOP
757 update_contract_grpngs(
758 p_api_version,
759 p_init_msg_list,
760 l_return_status,
761 x_msg_count,
762 x_msg_data,
763 p_cgcv_tbl(i),
764 x_cgcv_tbl(i));
765 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
766 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
767 x_return_status := l_return_status;
768 raise G_EXCEPTION_HALT_VALIDATION;
769 ELSE
770 x_return_status := l_return_status;
771 END IF;
772 END IF;
773 EXIT WHEN (i = p_cgcv_tbl.LAST);
774 i := p_cgcv_tbl.NEXT(i);
775 END LOOP;
776 END IF;
777 EXCEPTION
778 WHEN G_EXCEPTION_HALT_VALIDATION THEN
779 NULL;
780 WHEN OTHERS THEN
781 OKC_API.set_message(p_app_name => g_app_name,
782 p_msg_name => g_unexpected_error,
783 p_token1 => g_sqlcode_token,
784 p_token1_value => sqlcode,
785 p_token2 => g_sqlerrm_token,
786 p_token2_value => sqlerrm);
787 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
788 END update_contract_grpngs;
789
790 PROCEDURE delete_contract_grpngs(
791 p_api_version IN NUMBER,
792 p_init_msg_list IN VARCHAR2 ,
793 x_return_status OUT NOCOPY VARCHAR2,
794 x_msg_count OUT NOCOPY NUMBER,
795 x_msg_data OUT NOCOPY VARCHAR2,
796 p_cgcv_rec IN cgcv_rec_type) IS
797 BEGIN
798 okc_cgc_pvt.delete_row(
799 p_api_version,
800 p_init_msg_list,
801 x_return_status,
802 x_msg_count,
803 x_msg_data,
804 p_cgcv_rec);
805 END delete_contract_grpngs;
806
807 PROCEDURE delete_contract_grpngs(
808 p_api_version IN NUMBER,
809 p_init_msg_list IN VARCHAR2 ,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_msg_count OUT NOCOPY NUMBER,
812 x_msg_data OUT NOCOPY VARCHAR2,
813 p_cgcv_tbl IN cgcv_tbl_type) IS
814 BEGIN
815 okc_cgc_pvt.delete_row(
816 p_api_version,
817 p_init_msg_list,
818 x_return_status,
819 x_msg_count,
820 x_msg_data,
821 p_cgcv_tbl);
822 END delete_contract_grpngs;
823
824 PROCEDURE lock_contract_grpngs(
825 p_api_version IN NUMBER,
826 p_init_msg_list IN VARCHAR2 ,
827 x_return_status OUT NOCOPY VARCHAR2,
828 x_msg_count OUT NOCOPY NUMBER,
829 x_msg_data OUT NOCOPY VARCHAR2,
830 p_cgcv_rec IN cgcv_rec_type) IS
831 BEGIN
832 okc_cgc_pvt.lock_row(
833 p_api_version,
834 p_init_msg_list,
835 x_return_status,
836 x_msg_count,
837 x_msg_data,
838 p_cgcv_rec);
839 END lock_contract_grpngs;
840
841 PROCEDURE lock_contract_grpngs(
842 p_api_version IN NUMBER,
843 p_init_msg_list IN VARCHAR2 ,
844 x_return_status OUT NOCOPY VARCHAR2,
845 x_msg_count OUT NOCOPY NUMBER,
846 x_msg_data OUT NOCOPY VARCHAR2,
847 p_cgcv_tbl IN cgcv_tbl_type) IS
848 BEGIN
849 okc_cgc_pvt.lock_row(
850 p_api_version,
851 p_init_msg_list,
852 x_return_status,
853 x_msg_count,
854 x_msg_data,
855 p_cgcv_tbl);
856 END lock_contract_grpngs;
857
858 PROCEDURE validate_contract_grpngs(
859 p_api_version IN NUMBER,
860 p_init_msg_list IN VARCHAR2 ,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER,
863 x_msg_data OUT NOCOPY VARCHAR2,
864 p_cgcv_rec IN cgcv_rec_type) IS
865 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
866 BEGIN
867 l_return_status := validate_recursion(p_cgcv_rec);
868 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
869 raise G_EXCEPTION_HALT_VALIDATION;
870 END IF;
871 okc_cgc_pvt.validate_row(
872 p_api_version,
873 p_init_msg_list,
874 l_return_status,
875 x_msg_count,
876 x_msg_data,
877 p_cgcv_rec);
878 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
879 raise G_EXCEPTION_HALT_VALIDATION;
880 END IF;
881 l_return_status := check_group_type(p_cgcv_rec);
882 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
883 raise G_EXCEPTION_HALT_VALIDATION;
884 END IF;
885 x_return_status := l_return_status;
886 EXCEPTION
887 WHEN G_EXCEPTION_HALT_VALIDATION THEN
888 x_return_status := l_return_status;
889 WHEN OTHERS THEN
890 OKC_API.set_message(p_app_name => g_app_name,
891 p_msg_name => g_unexpected_error,
892 p_token1 => g_sqlcode_token,
893 p_token1_value => sqlcode,
894 p_token2 => g_sqlerrm_token,
895 p_token2_value => sqlerrm);
896 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
897 END validate_contract_grpngs;
898
899 PROCEDURE validate_contract_grpngs(
900 p_api_version IN NUMBER,
901 p_init_msg_list IN VARCHAR2 ,
902 x_return_status OUT NOCOPY VARCHAR2,
903 x_msg_count OUT NOCOPY NUMBER,
904 x_msg_data OUT NOCOPY VARCHAR2,
905 p_cgcv_tbl IN cgcv_tbl_type) IS
906 i NUMBER := 0;
907 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
908 BEGIN
909 x_return_status := OKC_API.G_RET_STS_SUCCESS;
910 IF (p_cgcv_tbl.COUNT > 0) THEN
911 i := p_cgcv_tbl.FIRST;
912 LOOP
913 validate_contract_grpngs(
914 p_api_version,
915 p_init_msg_list,
916 l_return_status,
917 x_msg_count,
918 x_msg_data,
919 p_cgcv_tbl(i));
920 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
921 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
922 x_return_status := l_return_status;
923 raise G_EXCEPTION_HALT_VALIDATION;
924 ELSE
925 x_return_status := l_return_status;
926 END IF;
927 END IF;
928 EXIT WHEN (i = p_cgcv_tbl.LAST);
929 i := p_cgcv_tbl.NEXT(i);
930 END LOOP;
931 END IF;
932 EXCEPTION
933 WHEN G_EXCEPTION_HALT_VALIDATION THEN
934 NULL;
935 WHEN OTHERS THEN
936 OKC_API.set_message(p_app_name => g_app_name,
937 p_msg_name => g_unexpected_error,
938 p_token1 => g_sqlcode_token,
939 p_token1_value => sqlcode,
940 p_token2 => g_sqlerrm_token,
941 p_token2_value => sqlerrm);
942 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
943 END validate_contract_grpngs;
944
945 PROCEDURE Validate_Name(x_return_status OUT NOCOPY VARCHAR2,
946 p_cgpv_rec IN cgpv_rec_type) IS
947 BEGIN
948 okc_cgp_pvt.Validate_name(x_return_status, p_cgpv_rec);
949 END Validate_Name;
950
951 PROCEDURE Validate_Public_YN(x_return_status OUT NOCOPY VARCHAR2,
952 p_cgpv_rec IN cgpv_rec_type) IS
953 BEGIN
954 okc_cgp_pvt.Validate_Public_YN(x_return_status, p_cgpv_rec);
955 END Validate_Public_YN;
956
957 PROCEDURE Validate_Short_Description(x_return_status OUT NOCOPY VARCHAR2,
958 p_cgpv_rec IN cgpv_rec_type) IS
959 BEGIN
960 okc_cgp_pvt.Validate_Short_Description(x_return_status, p_cgpv_rec);
961 END Validate_Short_Description;
962
963 FUNCTION Validate_Record(p_cgpv_rec IN cgpv_rec_type)
964 RETURN VARCHAR2 IS
965 BEGIN
966 Return(okc_cgp_pvt.Validate_Record(p_cgpv_rec));
967 END;
968
969 END okc_contract_group_pvt;