[Home] [Help]
PACKAGE BODY: APPS.OKC_CGP_PVT
Source
1 PACKAGE BODY OKC_CGP_PVT AS
2 /* $Header: OKCSCGPB.pls 120.0 2005/05/25 19:20:47 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- FUNCTION get_seq_id
7 ---------------------------------------------------------------------------
8 FUNCTION get_seq_id RETURN NUMBER IS
9 l_id Number;
10 -- for customer's data (for non-seeded date id should be 50000 or above)
11 cursor nonseed_c is
12 select
13 OKC_K_GROUPS_S1.nextval
14 from
15 dual;
16 -- for datamerge's data (for seeded date id should be greater than or equal to 11000 and less than 50000)
17 cursor seed_c is
18 select
19 nvl(max(id), 11000) + 1
20 from
21 OKC_K_GROUPS_V
22 where
23 id >= 11000 AND id < 50000;
24 BEGIN
25 if fnd_global.user_id = 1 then
26 open seed_c;
27 fetch seed_c into l_id;
28 close seed_c;
29 else
30 open nonseed_c;
31 fetch nonseed_c into l_id;
32 close nonseed_c;
33 end if;
34 /*
35 SELECT OKC_K_GROUPS_S1.nextval
36 INTO l_id
37 FROM DUAL;
38 */
39 RETURN(l_id);
40 -- RETURN(okc_p_util.raw_to_number(sys_guid()));
41 END get_seq_id;
42
43 ---------------------------------------------------------------------------
44 -- PROCEDURE qc
45 ---------------------------------------------------------------------------
46 PROCEDURE qc IS
47 BEGIN
48 null;
49 END qc;
50
51 ---------------------------------------------------------------------------
52 -- PROCEDURE change_version
53 ---------------------------------------------------------------------------
54 PROCEDURE change_version IS
55 BEGIN
56 null;
57 END change_version;
58
59 ---------------------------------------------------------------------------
60 -- PROCEDURE api_copy
61 ---------------------------------------------------------------------------
62 PROCEDURE api_copy IS
63 BEGIN
64 null;
65 END api_copy;
66
67 ---------------------------------------------------------------------------
68 -- PROCEDURE add_language
69 ---------------------------------------------------------------------------
70 PROCEDURE add_language IS
71 BEGIN
72 DELETE FROM OKC_K_GROUPS_TL T
73 WHERE NOT EXISTS (
74 SELECT NULL
75 FROM OKC_K_GROUPS_B B
76 WHERE B.ID = T.ID
77 );
78
79 UPDATE OKC_K_GROUPS_TL T SET (
80 NAME,
81 SHORT_DESCRIPTION) = (SELECT
82 B.NAME,
83 B.SHORT_DESCRIPTION
84 FROM OKC_K_GROUPS_TL B
85 WHERE B.ID = T.ID
86 AND B.LANGUAGE = T.SOURCE_LANG)
87 WHERE (
88 T.ID,
89 T.LANGUAGE)
90 IN (SELECT
91 SUBT.ID,
92 SUBT.LANGUAGE
93 FROM OKC_K_GROUPS_TL SUBB, OKC_K_GROUPS_TL SUBT
94 WHERE SUBB.ID = SUBT.ID
95 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
96 AND (SUBB.NAME <> SUBT.NAME
97 OR SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
98 ));
99
100 INSERT INTO OKC_K_GROUPS_TL (
101 ID,
102 LANGUAGE,
103 SOURCE_LANG,
104 SFWT_FLAG,
105 NAME,
106 SHORT_DESCRIPTION,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATE_LOGIN)
112 SELECT
113 B.ID,
114 L.LANGUAGE_CODE,
115 B.SOURCE_LANG,
116 B.SFWT_FLAG,
117 B.NAME,
118 B.SHORT_DESCRIPTION,
119 B.CREATED_BY,
120 B.CREATION_DATE,
121 B.LAST_UPDATED_BY,
122 B.LAST_UPDATE_DATE,
123 B.LAST_UPDATE_LOGIN
124 FROM OKC_K_GROUPS_TL B, FND_LANGUAGES L
125 WHERE L.INSTALLED_FLAG IN ('I', 'B')
126 AND B.LANGUAGE = USERENV('LANG')
127 AND NOT EXISTS(
128 SELECT NULL
129 FROM OKC_K_GROUPS_TL T
130 WHERE T.ID = B.ID
131 AND T.LANGUAGE = L.LANGUAGE_CODE
132 );
133
134 END add_language;
135
136 ---------------------------------------------------------------------------
137 -- FUNCTION get_rec for: OKC_K_GROUPS_B
138 ---------------------------------------------------------------------------
139 FUNCTION get_rec (
140 p_cgp_rec IN cgp_rec_type,
141 x_no_data_found OUT NOCOPY BOOLEAN
142 ) RETURN cgp_rec_type IS
143 CURSOR cgp_pk_csr (p_id IN NUMBER) IS
144 SELECT
145 ID,
146 PUBLIC_YN,
147 OBJECT_VERSION_NUMBER,
148 CREATED_BY,
149 CREATION_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_DATE,
152 USER_ID,
153 LAST_UPDATE_LOGIN,
154 ATTRIBUTE_CATEGORY,
155 ATTRIBUTE1,
156 ATTRIBUTE2,
157 ATTRIBUTE3,
158 ATTRIBUTE4,
159 ATTRIBUTE5,
160 ATTRIBUTE6,
161 ATTRIBUTE7,
162 ATTRIBUTE8,
163 ATTRIBUTE9,
164 ATTRIBUTE10,
165 ATTRIBUTE11,
166 ATTRIBUTE12,
167 ATTRIBUTE13,
168 ATTRIBUTE14,
169 ATTRIBUTE15
170 FROM Okc_K_Groups_B
171 WHERE okc_K_groups_b.id = p_id;
172 l_cgp_pk cgp_pk_csr%ROWTYPE;
173 l_cgp_rec cgp_rec_type;
174 BEGIN
175 x_no_data_found := TRUE;
176 -- Get current database values
177 OPEN cgp_pk_csr (p_cgp_rec.id);
178 FETCH cgp_pk_csr INTO
179 l_cgp_rec.ID,
180 l_cgp_rec.PUBLIC_YN,
181 l_cgp_rec.OBJECT_VERSION_NUMBER,
182 l_cgp_rec.CREATED_BY,
183 l_cgp_rec.CREATION_DATE,
184 l_cgp_rec.LAST_UPDATED_BY,
185 l_cgp_rec.LAST_UPDATE_DATE,
186 l_cgp_rec.USER_ID,
187 l_cgp_rec.LAST_UPDATE_LOGIN,
188 l_cgp_rec.ATTRIBUTE_CATEGORY,
189 l_cgp_rec.ATTRIBUTE1,
190 l_cgp_rec.ATTRIBUTE2,
191 l_cgp_rec.ATTRIBUTE3,
192 l_cgp_rec.ATTRIBUTE4,
193 l_cgp_rec.ATTRIBUTE5,
194 l_cgp_rec.ATTRIBUTE6,
195 l_cgp_rec.ATTRIBUTE7,
196 l_cgp_rec.ATTRIBUTE8,
197 l_cgp_rec.ATTRIBUTE9,
198 l_cgp_rec.ATTRIBUTE10,
199 l_cgp_rec.ATTRIBUTE11,
200 l_cgp_rec.ATTRIBUTE12,
201 l_cgp_rec.ATTRIBUTE13,
202 l_cgp_rec.ATTRIBUTE14,
203 l_cgp_rec.ATTRIBUTE15;
204 x_no_data_found := cgp_pk_csr%NOTFOUND;
205 CLOSE cgp_pk_csr;
206 RETURN(l_cgp_rec);
207 END get_rec;
208
209 FUNCTION get_rec (
210 p_cgp_rec IN cgp_rec_type
211 ) RETURN cgp_rec_type IS
212 l_row_notfound BOOLEAN := TRUE;
213 BEGIN
214 RETURN(get_rec(p_cgp_rec, l_row_notfound));
215 END get_rec;
216 ---------------------------------------------------------------------------
217 -- FUNCTION get_rec for: OKC_K_GROUPS_TL
218 ---------------------------------------------------------------------------
219 FUNCTION get_rec (
220 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
221 x_no_data_found OUT NOCOPY BOOLEAN
222 ) RETURN OkcContractGroupsTlRecType IS
223 CURSOR cgp_pktl_csr (p_id IN NUMBER,
224 p_language IN VARCHAR2) IS
225 SELECT
226 ID,
227 LANGUAGE,
228 SOURCE_LANG,
229 SFWT_FLAG,
230 NAME,
231 SHORT_DESCRIPTION,
232 CREATED_BY,
233 CREATION_DATE,
234 LAST_UPDATED_BY,
235 LAST_UPDATE_DATE,
236 LAST_UPDATE_LOGIN
237 FROM Okc_K_Groups_Tl
238 WHERE okc_K_groups_tl.id = p_id
239 AND okc_K_groups_tl.language = p_language;
240 l_cgp_pktl cgp_pktl_csr%ROWTYPE;
241 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
242 BEGIN
243 x_no_data_found := TRUE;
244 -- Get current database values
245 OPEN cgp_pktl_csr (p_okc_K_groups_tl_rec.id,
246 p_okc_K_groups_tl_rec.language);
247 FETCH cgp_pktl_csr INTO
248 l_okc_K_groups_tl_rec.ID,
249 l_okc_K_groups_tl_rec.LANGUAGE,
250 l_okc_K_groups_tl_rec.SOURCE_LANG,
251 l_okc_K_groups_tl_rec.SFWT_FLAG,
252 l_okc_K_groups_tl_rec.NAME,
253 l_okc_K_groups_tl_rec.SHORT_DESCRIPTION,
254 l_okc_K_groups_tl_rec.CREATED_BY,
255 l_okc_K_groups_tl_rec.CREATION_DATE,
256 l_okc_K_groups_tl_rec.LAST_UPDATED_BY,
257 l_okc_K_groups_tl_rec.LAST_UPDATE_DATE,
258 l_okc_K_groups_tl_rec.LAST_UPDATE_LOGIN;
259 x_no_data_found := cgp_pktl_csr%NOTFOUND;
260 CLOSE cgp_pktl_csr;
261 RETURN(l_okc_K_groups_tl_rec);
262 END get_rec;
263
264 FUNCTION get_rec (
265 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType
266 ) RETURN OkcContractGroupsTlRecType IS
267 l_row_notfound BOOLEAN := TRUE;
268 BEGIN
269 RETURN(get_rec(p_okc_K_groups_tl_rec, l_row_notfound));
270 END get_rec;
271 ---------------------------------------------------------------------------
272 -- FUNCTION get_rec for: OKC_K_GROUPS_V
273 ---------------------------------------------------------------------------
274 FUNCTION get_rec (
275 p_cgpv_rec IN cgpv_rec_type,
276 x_no_data_found OUT NOCOPY BOOLEAN
277 ) RETURN cgpv_rec_type IS
278 CURSOR okc_cgpv_pk_csr (p_id IN NUMBER) IS
279 SELECT
280 ID,
281 OBJECT_VERSION_NUMBER,
282 SFWT_FLAG,
283 NAME,
284 PUBLIC_YN,
285 SHORT_DESCRIPTION,
286 ATTRIBUTE_CATEGORY,
287 ATTRIBUTE1,
288 ATTRIBUTE2,
289 ATTRIBUTE3,
290 ATTRIBUTE4,
291 ATTRIBUTE5,
292 ATTRIBUTE6,
293 ATTRIBUTE7,
294 ATTRIBUTE8,
295 ATTRIBUTE9,
296 ATTRIBUTE10,
297 ATTRIBUTE11,
298 ATTRIBUTE12,
299 ATTRIBUTE13,
300 ATTRIBUTE14,
301 ATTRIBUTE15,
302 CREATED_BY,
303 CREATION_DATE,
304 LAST_UPDATED_BY,
305 LAST_UPDATE_DATE,
306 USER_ID,
307 LAST_UPDATE_LOGIN
308 FROM Okc_K_Groups_V
309 WHERE okc_K_groups_v.id = p_id;
310 l_okc_cgpv_pk okc_cgpv_pk_csr%ROWTYPE;
311 l_cgpv_rec cgpv_rec_type;
312 BEGIN
313 x_no_data_found := TRUE;
314 -- Get current database values
315 OPEN okc_cgpv_pk_csr (p_cgpv_rec.id);
316 FETCH okc_cgpv_pk_csr INTO
317 l_cgpv_rec.ID,
318 l_cgpv_rec.OBJECT_VERSION_NUMBER,
319 l_cgpv_rec.SFWT_FLAG,
320 l_cgpv_rec.NAME,
321 l_cgpv_rec.PUBLIC_YN,
322 l_cgpv_rec.SHORT_DESCRIPTION,
323 l_cgpv_rec.ATTRIBUTE_CATEGORY,
324 l_cgpv_rec.ATTRIBUTE1,
325 l_cgpv_rec.ATTRIBUTE2,
326 l_cgpv_rec.ATTRIBUTE3,
327 l_cgpv_rec.ATTRIBUTE4,
328 l_cgpv_rec.ATTRIBUTE5,
329 l_cgpv_rec.ATTRIBUTE6,
330 l_cgpv_rec.ATTRIBUTE7,
331 l_cgpv_rec.ATTRIBUTE8,
332 l_cgpv_rec.ATTRIBUTE9,
333 l_cgpv_rec.ATTRIBUTE10,
334 l_cgpv_rec.ATTRIBUTE11,
335 l_cgpv_rec.ATTRIBUTE12,
336 l_cgpv_rec.ATTRIBUTE13,
337 l_cgpv_rec.ATTRIBUTE14,
338 l_cgpv_rec.ATTRIBUTE15,
339 l_cgpv_rec.CREATED_BY,
340 l_cgpv_rec.CREATION_DATE,
341 l_cgpv_rec.LAST_UPDATED_BY,
342 l_cgpv_rec.LAST_UPDATE_DATE,
343 l_cgpv_rec.USER_ID,
344 l_cgpv_rec.LAST_UPDATE_LOGIN;
345 x_no_data_found := okc_cgpv_pk_csr%NOTFOUND;
346 CLOSE okc_cgpv_pk_csr;
347 RETURN(l_cgpv_rec);
348 END get_rec;
349
350 FUNCTION get_rec (
351 p_cgpv_rec IN cgpv_rec_type
352 ) RETURN cgpv_rec_type IS
353 l_row_notfound BOOLEAN := TRUE;
354 BEGIN
355 RETURN(get_rec(p_cgpv_rec, l_row_notfound));
356 END get_rec;
357
358 -----------------------------------------------------------
359 -- FUNCTION null_out_defaults for: OKC_K_GROUPS_V --
360 -----------------------------------------------------------
361 FUNCTION null_out_defaults (
362 p_cgpv_rec IN cgpv_rec_type
363 ) RETURN cgpv_rec_type IS
364 l_cgpv_rec cgpv_rec_type := p_cgpv_rec;
365 BEGIN
366 IF (l_cgpv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
367 l_cgpv_rec.object_version_number := NULL;
368 END IF;
369 IF (l_cgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
370 l_cgpv_rec.sfwt_flag := NULL;
371 END IF;
372 IF (l_cgpv_rec.name = OKC_API.G_MISS_CHAR) THEN
373 l_cgpv_rec.name := NULL;
374 END IF;
375 IF (l_cgpv_rec.public_yn = OKC_API.G_MISS_CHAR) THEN
376 l_cgpv_rec.public_yn := NULL;
377 END IF;
378 IF (l_cgpv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
379 l_cgpv_rec.short_description := NULL;
380 END IF;
381 IF (l_cgpv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
382 l_cgpv_rec.attribute_category := NULL;
383 END IF;
384 IF (l_cgpv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
385 l_cgpv_rec.attribute1 := NULL;
386 END IF;
387 IF (l_cgpv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
388 l_cgpv_rec.attribute2 := NULL;
389 END IF;
390 IF (l_cgpv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
391 l_cgpv_rec.attribute3 := NULL;
392 END IF;
393 IF (l_cgpv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
394 l_cgpv_rec.attribute4 := NULL;
395 END IF;
396 IF (l_cgpv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
397 l_cgpv_rec.attribute5 := NULL;
398 END IF;
399 IF (l_cgpv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
400 l_cgpv_rec.attribute6 := NULL;
401 END IF;
402 IF (l_cgpv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
403 l_cgpv_rec.attribute7 := NULL;
404 END IF;
405 IF (l_cgpv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
406 l_cgpv_rec.attribute8 := NULL;
407 END IF;
408 IF (l_cgpv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
409 l_cgpv_rec.attribute9 := NULL;
410 END IF;
411 IF (l_cgpv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
412 l_cgpv_rec.attribute10 := NULL;
413 END IF;
414 IF (l_cgpv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
415 l_cgpv_rec.attribute11 := NULL;
416 END IF;
417 IF (l_cgpv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
418 l_cgpv_rec.attribute12 := NULL;
419 END IF;
420 IF (l_cgpv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
421 l_cgpv_rec.attribute13 := NULL;
422 END IF;
423 IF (l_cgpv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
424 l_cgpv_rec.attribute14 := NULL;
425 END IF;
429 IF (l_cgpv_rec.created_by = OKC_API.G_MISS_NUM) THEN
426 IF (l_cgpv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
427 l_cgpv_rec.attribute15 := NULL;
428 END IF;
430 l_cgpv_rec.created_by := NULL;
431 END IF;
432 IF (l_cgpv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
433 l_cgpv_rec.creation_date := NULL;
434 END IF;
435 IF (l_cgpv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
436 l_cgpv_rec.last_updated_by := NULL;
437 END IF;
438 IF (l_cgpv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
439 l_cgpv_rec.last_update_date := NULL;
440 END IF;
441 IF (l_cgpv_rec.user_id = OKC_API.G_MISS_NUM) THEN
442 l_cgpv_rec.user_id := NULL;
443 END IF;
444 IF (l_cgpv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
445 l_cgpv_rec.last_update_login := NULL;
446 END IF;
447 RETURN(l_cgpv_rec);
448 END null_out_defaults;
449 ---------------------------------------------------------------------------
450 ---------------------------------------------------
451 PROCEDURE Check_Pub_Group_Privilege(x_return_status OUT NOCOPY VARCHAR2,
452 p_cgpv_rec IN cgpv_rec_type) IS
453 l_pub_grp_creator VARCHAR2(3);
454 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
455 BEGIN
456 x_return_status := OKC_API.G_RET_STS_SUCCESS;
457 IF p_cgpv_rec.public_yn = 'Y' THEN
458 l_pub_grp_creator := fnd_profile.value('OKC_PUB_GRP_CREATOR');
459 IF Nvl(l_pub_grp_creator, 'N') = 'N' Then
460 OKC_API.set_message(G_APP_NAME, 'OKC_PUBLIC_GROUP_ERROR');
461 x_return_status := OKC_API.G_RET_STS_ERROR;
462 raise G_EXCEPTION_HALT_VALIDATION;
463 END IF;
464 END IF;
465 EXCEPTION
466 WHEN G_EXCEPTION_HALT_VALIDATION THEN
467 NULL;
468 WHEN OTHERS THEN
469 OKC_API.set_message(p_app_name => g_app_name,
470 p_msg_name => g_unexpected_error,
471 p_token1 => g_sqlcode_token,
472 p_token1_value => sqlcode,
473 p_token2 => g_sqlerrm_token,
474 p_token2_value => sqlerrm);
475 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
476 END Check_Pub_Group_Privilege;
477 ---------------------------------------------------------------------------
478 PROCEDURE Check_User_Privilege(x_return_status OUT NOCOPY VARCHAR2,
479 p_cgpv_rec IN cgpv_rec_type) IS
480 l_user_id NUMBER(15);
481 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
482 BEGIN
483 x_return_status := OKC_API.G_RET_STS_SUCCESS;
484 l_user_id := TO_NUMBER(fnd_profile.value('USER_ID'));
485 IF p_cgpv_rec.public_yn = 'N' THEN
486 IF l_user_id <> p_cgpv_rec.user_id THEN
487 OKC_API.set_message(G_APP_NAME, 'OKC_PRIVATE_GROUP_ERROR');
488 x_return_status := OKC_API.G_RET_STS_ERROR;
489 raise G_EXCEPTION_HALT_VALIDATION;
490 END IF;
491 END IF;
492 EXCEPTION
493 WHEN G_EXCEPTION_HALT_VALIDATION THEN
494 NULL;
495 WHEN OTHERS THEN
496 OKC_API.set_message(p_app_name => g_app_name,
497 p_msg_name => g_unexpected_error,
498 p_token1 => g_sqlcode_token,
499 p_token1_value => sqlcode,
500 p_token2 => g_sqlerrm_token,
501 p_token2_value => sqlerrm);
502 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
503 END Check_User_Privilege;
504 ---------------------------------------------------------------------------
505 PROCEDURE Validate_Id(x_return_status OUT NOCOPY VARCHAR2,
506 p_cgpv_rec IN cgpv_rec_type) IS
507 BEGIN
508 x_return_status := OKC_API.G_RET_STS_SUCCESS;
509 IF p_cgpv_rec.id = OKC_API.G_MISS_NUM OR
510 p_cgpv_rec.id IS NULL
511 THEN
512 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
513 x_return_status := OKC_API.G_RET_STS_ERROR;
514 raise G_EXCEPTION_HALT_VALIDATION;
515 END IF;
516 EXCEPTION
517 WHEN G_EXCEPTION_HALT_VALIDATION THEN
518 NULL;
519 WHEN OTHERS THEN
520 OKC_API.set_message(p_app_name => g_app_name,
521 p_msg_name => g_unexpected_error,
522 p_token1 => g_sqlcode_token,
523 p_token1_value => sqlcode,
524 p_token2 => g_sqlerrm_token,
525 p_token2_value => sqlerrm);
526 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
527 END Validate_Id;
528 ---------------------------------------------------------------------------
529 PROCEDURE Validate_Obj_Version_Num(x_return_status OUT NOCOPY VARCHAR2,
530 p_cgpv_rec IN cgpv_rec_type) IS
531 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
532 BEGIN
533 x_return_status := OKC_API.G_RET_STS_SUCCESS;
534 IF p_cgpv_rec.object_version_number = OKC_API.G_MISS_NUM OR
535 p_cgpv_rec.object_version_number IS NULL
536 THEN
537 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
541 EXCEPTION
538 x_return_status := OKC_API.G_RET_STS_ERROR;
539 raise G_EXCEPTION_HALT_VALIDATION;
540 END IF;
542 WHEN G_EXCEPTION_HALT_VALIDATION THEN
543 NULL;
544 WHEN OTHERS THEN
545 OKC_API.set_message(p_app_name => g_app_name,
546 p_msg_name => g_unexpected_error,
547 p_token1 => g_sqlcode_token,
548 p_token1_value => sqlcode,
549 p_token2 => g_sqlerrm_token,
550 p_token2_value => sqlerrm);
551 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
552 END Validate_Obj_Version_Num;
553 ---------------------------------------------------------------------------
554 PROCEDURE Validate_Sfwt_Flag(x_return_status OUT NOCOPY VARCHAR2,
555 p_cgpv_rec IN cgpv_rec_type) IS
556 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
557 BEGIN
558 x_return_status := OKC_API.G_RET_STS_SUCCESS;
559 IF p_cgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR OR
560 p_cgpv_rec.sfwt_flag IS NULL
561 THEN
562 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
563 x_return_status := OKC_API.G_RET_STS_ERROR;
564 raise G_EXCEPTION_HALT_VALIDATION;
565 END IF;
566 IF UPPER(p_cgpv_rec.sfwt_flag) IN ('Y', 'N') Then
567 IF p_cgpv_rec.sfwt_flag <> UPPER(p_cgpv_rec.sfwt_flag) Then
568 OKC_API.set_message(G_APP_NAME, G_UPPERCASE_REQUIRED, G_COL_NAME_TOKEN, 'sfwt_flag');
569 x_return_status := OKC_API.G_RET_STS_ERROR;
570 raise G_EXCEPTION_HALT_VALIDATION;
571 END IF;
572 ELSE
573 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
574 x_return_status := OKC_API.G_RET_STS_ERROR;
575 raise G_EXCEPTION_HALT_VALIDATION;
576 END IF;
577 EXCEPTION
578 WHEN G_EXCEPTION_HALT_VALIDATION THEN
579 NULL;
580 WHEN OTHERS THEN
581 OKC_API.set_message(p_app_name => g_app_name,
582 p_msg_name => g_unexpected_error,
583 p_token1 => g_sqlcode_token,
584 p_token1_value => sqlcode,
585 p_token2 => g_sqlerrm_token,
586 p_token2_value => sqlerrm);
587 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
588 END Validate_Sfwt_Flag;
589 ---------------------------------------------------------------------------
590 PROCEDURE Validate_Name(x_return_status OUT NOCOPY VARCHAR2,
591 p_cgpv_rec IN cgpv_rec_type) IS
592 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
593 BEGIN
594 x_return_status := OKC_API.G_RET_STS_SUCCESS;
595 IF p_cgpv_rec.name = OKC_API.G_MISS_CHAR OR
596 p_cgpv_rec.name IS NULL
597 THEN
598 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'name');
599 x_return_status := OKC_API.G_RET_STS_ERROR;
600 raise G_EXCEPTION_HALT_VALIDATION;
601 END IF;
602 -- Check the validate_record for uniqueness rule of name
603 EXCEPTION
604 WHEN G_EXCEPTION_HALT_VALIDATION THEN
605 NULL;
606 WHEN OTHERS THEN
607 OKC_API.set_message(p_app_name => g_app_name,
608 p_msg_name => g_unexpected_error,
609 p_token1 => g_sqlcode_token,
610 p_token1_value => sqlcode,
611 p_token2 => g_sqlerrm_token,
612 p_token2_value => sqlerrm);
613 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
614 END Validate_Name;
615 ---------------------------------------------------------------------------
616 PROCEDURE Validate_Public_YN(x_return_status OUT NOCOPY VARCHAR2,
617 p_cgpv_rec IN cgpv_rec_type) IS
618 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
619 l_dummy VARCHAR2(1);
620 l_row_found BOOLEAN;
621 cursor c1 (p_cgp_parent_id okc_k_grpings_v.cgp_parent_id%TYPE) is
622 select 'x'
623 from okc_k_groups_v a,
624 okc_k_grpings_v b
625 where a.public_yn = 'N'
626 and a.id = b.included_cgp_id
627 and b.cgp_parent_id = p_cgp_parent_id;
628 cursor c2 (p_included_cgp_id okc_k_grpings_v.included_cgp_id%TYPE) is
629 select 'x'
630 from okc_k_groups_v a,
631 okc_k_grpings_v b
632 where a.public_yn = 'Y'
633 and a.id = b.cgp_parent_id
634 and b.included_cgp_id = p_included_cgp_id;
635 BEGIN
636 x_return_status := OKC_API.G_RET_STS_SUCCESS;
637 IF p_cgpv_rec.public_yn = OKC_API.G_MISS_CHAR OR
638 p_cgpv_rec.public_yn IS NULL
639 THEN
640 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'public_yn');
641 x_return_status := OKC_API.G_RET_STS_ERROR;
642 raise G_EXCEPTION_HALT_VALIDATION;
643 END IF;
644 IF UPPER(p_cgpv_rec.public_yn) IN ('Y', 'N') Then
645 IF p_cgpv_rec.public_yn <> UPPER(p_cgpv_rec.public_yn) Then
646 OKC_API.set_message(G_APP_NAME, G_UPPERCASE_REQUIRED, G_COL_NAME_TOKEN, 'public_yn');
647 x_return_status := OKC_API.G_RET_STS_ERROR;
648 raise G_EXCEPTION_HALT_VALIDATION;
649 END IF;
650 ELSE
651 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'public_yn');
655 If p_cgpv_rec.public_yn = 'Y' Then
652 x_return_status := OKC_API.G_RET_STS_ERROR;
653 raise G_EXCEPTION_HALT_VALIDATION;
654 END IF;
656 --
657 -- When a private group is switched to public, make sure it does
658 -- not have any private group as its children
659 --
660 Open c1(p_cgpv_rec.id);
661 Fetch c1 Into l_dummy;
662 l_row_found := c1%FOUND;
663 Close c1;
664 If l_row_found Then
665 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'public_yn');
666 x_return_status := OKC_API.G_RET_STS_ERROR;
667 raise G_EXCEPTION_HALT_VALIDATION;
668 END IF;
669 Else
670 --
671 -- When a public group is switched to private, make sure it does
672 -- not have any public group as its parent
673 --
674 Open c2(p_cgpv_rec.id);
675 Fetch c2 Into l_dummy;
676 l_row_found := c2%FOUND;
677 Close c2;
678 If l_row_found Then
679 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'public_yn');
680 x_return_status := OKC_API.G_RET_STS_ERROR;
681 raise G_EXCEPTION_HALT_VALIDATION;
682 END IF;
683 END IF;
684 EXCEPTION
685 WHEN G_EXCEPTION_HALT_VALIDATION THEN
686 Null;
687 WHEN OTHERS THEN
688 OKC_API.set_message(p_app_name => g_app_name,
689 p_msg_name => g_unexpected_error,
690 p_token1 => g_sqlcode_token,
691 p_token1_value => sqlcode,
692 p_token2 => g_sqlerrm_token,
693 p_token2_value => sqlerrm);
694 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
695 END Validate_Public_YN;
696 ---------------------------------------------------------------------------
697 PROCEDURE Validate_Short_Description(x_return_status OUT NOCOPY VARCHAR2,
698 p_cgpv_rec IN cgpv_rec_type) IS
699 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
700 BEGIN
701 x_return_status := OKC_API.G_RET_STS_SUCCESS;
702 IF p_cgpv_rec.short_description = OKC_API.G_MISS_CHAR OR
703 p_cgpv_rec.short_description IS NULL
704 THEN
705 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'short_description');
706 x_return_status := OKC_API.G_RET_STS_ERROR;
707 raise G_EXCEPTION_HALT_VALIDATION;
708 END IF;
709 EXCEPTION
710 WHEN G_EXCEPTION_HALT_VALIDATION THEN
711 NULL;
712 WHEN OTHERS THEN
713 OKC_API.set_message(p_app_name => g_app_name,
714 p_msg_name => g_unexpected_error,
715 p_token1 => g_sqlcode_token,
716 p_token1_value => sqlcode,
717 p_token2 => g_sqlerrm_token,
718 p_token2_value => sqlerrm);
719 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
720 END Validate_Short_Description;
721 ---------------------------------------------------------------------------
722 ---------------------------------------------------------------------------
723 -- PROCEDURE Validate_Attributes
724 ---------------------------------------------------------------------------
725 ---------------------------------------------------
726 -- Validate_Attributes for:OKC_K_GROUPS_V --
727 ---------------------------------------------------
728 FUNCTION Validate_Attributes (
729 p_cgpv_rec IN cgpv_rec_type
730 ) RETURN VARCHAR2 IS
731 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
732 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
733 BEGIN
734 Validate_Id(l_return_status, p_cgpv_rec);
735 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
736 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
737 x_return_status := l_return_status;
738 raise G_EXCEPTION_HALT_VALIDATION;
739 ELSE
740 x_return_status := l_return_status;
741 END IF;
742 END IF;
743 Validate_Obj_Version_Num(l_return_status, p_cgpv_rec);
744 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
745 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
746 x_return_status := l_return_status;
747 raise G_EXCEPTION_HALT_VALIDATION;
748 ELSE
749 x_return_status := l_return_status;
750 END IF;
751 END IF;
752 Validate_Sfwt_Flag(l_return_status, p_cgpv_rec);
753 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
754 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
755 x_return_status := l_return_status;
756 raise G_EXCEPTION_HALT_VALIDATION;
757 ELSE
758 x_return_status := l_return_status;
759 END IF;
760 END IF;
761 Validate_Name(l_return_status, p_cgpv_rec);
762 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
763 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
764 x_return_status := l_return_status;
765 raise G_EXCEPTION_HALT_VALIDATION;
766 ELSE
767 x_return_status := l_return_status;
768 END IF;
769 END IF;
770 Validate_Public_YN(l_return_status, p_cgpv_rec);
771 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
775 ELSE
772 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
773 x_return_status := l_return_status;
774 raise G_EXCEPTION_HALT_VALIDATION;
776 x_return_status := l_return_status;
777 END IF;
778 END IF;
779 Validate_Short_Description(l_return_status, p_cgpv_rec);
780 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
781 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
782 x_return_status := l_return_status;
783 raise G_EXCEPTION_HALT_VALIDATION;
784 ELSE
785 x_return_status := l_return_status;
786 END IF;
787 END IF;
788 RETURN(x_return_status);
789 EXCEPTION
790 WHEN G_EXCEPTION_HALT_VALIDATION THEN
791 RETURN(x_return_status);
792 WHEN OTHERS THEN
793 OKC_API.set_message(p_app_name => g_app_name,
794 p_msg_name => g_unexpected_error,
795 p_token1 => g_sqlcode_token,
796 p_token1_value => sqlcode,
797 p_token2 => g_sqlerrm_token,
798 p_token2_value => sqlerrm);
799 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
800 RETURN(x_return_status);
801 END Validate_Attributes;
802 ---------------------------------------------------------------------------
803 -- PROCEDURE Validate_Record
804 ---------------------------------------------------------------------------
805 -----------------------------------------------
806 -- Validate_Record for:OKC_K_GROUPS_V --
807 -----------------------------------------------
808 FUNCTION Validate_Record (
809 p_cgpv_rec IN cgpv_rec_type
810 ) RETURN VARCHAR2 IS
811 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
812 ------------------------------------
813 -- FUNCTION validate_foreign_keys --
814 ------------------------------------
815 FUNCTION validate_foreign_keys (
816 p_cgpv_rec IN cgpv_rec_type
817 ) RETURN VARCHAR2 IS
818 item_not_found_error EXCEPTION;
819 CURSOR fnd_userv_pk_csr (p_user_id IN NUMBER) IS
820 SELECT '1'
821 FROM Fnd_User_View
822 WHERE fnd_user_view.user_id = p_user_id;
823 l_dummy VARCHAR2(1);
824 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
825 l_row_notfound BOOLEAN := TRUE;
826 BEGIN
827 IF (p_cgpv_rec.USER_ID IS NOT NULL)
828 THEN
829 OPEN fnd_userv_pk_csr(p_cgpv_rec.USER_ID);
830 FETCH fnd_userv_pk_csr INTO l_dummy;
831 l_row_notfound := fnd_userv_pk_csr%NOTFOUND;
832 CLOSE fnd_userv_pk_csr;
833 IF (l_row_notfound) THEN
834 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'USER_ID');
835 RAISE item_not_found_error;
836 END IF;
837 END IF;
838 RETURN (l_return_status);
839 EXCEPTION
840 WHEN item_not_found_error THEN
841 l_return_status := OKC_API.G_RET_STS_ERROR;
842 RETURN (l_return_status);
843 WHEN G_EXCEPTION_HALT_VALIDATION THEN
844 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
845 RETURN (l_return_status);
846 WHEN OTHERS THEN
847 OKC_API.set_message(p_app_name => g_app_name,
848 p_msg_name => g_unexpected_error,
849 p_token1 => g_sqlcode_token,
850 p_token1_value => sqlcode,
851 p_token2 => g_sqlerrm_token,
852 p_token2_value => sqlerrm);
853 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
854 RETURN (l_return_status);
855 END validate_foreign_keys;
856 ----------------------------------------------------
857 FUNCTION validate_unique_keys (
858 p_cgpv_rec IN cgpv_rec_type
859 ) RETURN VARCHAR2 IS
860 unique_key_error EXCEPTION;
861 CURSOR c1 (p_id IN okc_k_groups_v.id%TYPE,
862 p_name IN okc_k_groups_v.name%TYPE,
863 p_user_id IN okc_k_groups_v.user_id%TYPE,
864 p_public_yn IN okc_k_groups_v.public_yn%TYPE) IS
865 SELECT 'x'
866 FROM Okc_K_Groups_V
867 WHERE ((p_id IS NULL)
868 OR (p_id IS NOT NULL
869 AND Okc_K_Groups_V.id <> p_id))
870 AND Okc_K_Groups_V.name = p_name
871 AND Okc_K_Groups_V.public_yn = p_public_yn
872 AND ((p_user_id IS NULL)
873 OR (p_user_id IS NOT NULL
874 AND Okc_K_Groups_V.user_id = p_user_id));
875 l_dummy VARCHAR2(1);
876 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
877 l_row_found BOOLEAN := FALSE;
878 BEGIN
879 -- A public group name is always unique, across all public groups.
880 -- A private group name is always unique, across all private groups owned by the user.
881 -- 2 users can have the same private groups with the same name.
882 -- A private group name can be same as a public group name.
883 -- In case of a public group, the user_id must be passed as null since a public group
884 -- is not owned by anyone.
885 OPEN c1(p_cgpv_rec.id,
886 p_cgpv_rec.name,
887 p_cgpv_rec.user_id,
888 p_cgpv_rec.public_yn);
892 IF (l_row_found) THEN
889 FETCH c1 INTO l_dummy;
890 l_row_found := c1%FOUND;
891 CLOSE c1;
893 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'NAME');
894 RAISE unique_key_error;
895 END IF;
896 RETURN (l_return_status);
897 EXCEPTION
898 WHEN unique_key_error THEN
899 l_return_status := OKC_API.G_RET_STS_ERROR;
900 RETURN (l_return_status);
901 END validate_unique_keys;
902 BEGIN
903 l_return_status := validate_foreign_keys (p_cgpv_rec);
904 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
905 raise G_EXCEPTION_HALT_VALIDATION;
906 END IF;
907 l_return_status := validate_unique_keys (p_cgpv_rec);
908 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
909 raise G_EXCEPTION_HALT_VALIDATION;
910 END IF;
911 RETURN (l_return_status);
912 EXCEPTION
913 WHEN G_EXCEPTION_HALT_VALIDATION THEN
914 RETURN(l_return_status);
915 WHEN OTHERS THEN
916 OKC_API.set_message(p_app_name => g_app_name,
917 p_msg_name => g_unexpected_error,
918 p_token1 => g_sqlcode_token,
919 p_token1_value => sqlcode,
920 p_token2 => g_sqlerrm_token,
921 p_token2_value => sqlerrm);
922 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
923 RETURN(l_return_status);
924 END Validate_Record;
925
926 ---------------------------------------------------------------------------
927 -- PROCEDURE Migrate
928 ---------------------------------------------------------------------------
929 PROCEDURE migrate (
930 p_from IN cgpv_rec_type,
931 p_to OUT NOCOPY cgp_rec_type
932 ) IS
933 BEGIN
934 p_to.id := p_from.id;
935 p_to.public_yn := p_from.public_yn;
936 p_to.object_version_number := p_from.object_version_number;
937 p_to.created_by := p_from.created_by;
938 p_to.creation_date := p_from.creation_date;
939 p_to.last_updated_by := p_from.last_updated_by;
940 p_to.last_update_date := p_from.last_update_date;
941 p_to.user_id := p_from.user_id;
942 p_to.last_update_login := p_from.last_update_login;
943 p_to.attribute_category := p_from.attribute_category;
944 p_to.attribute1 := p_from.attribute1;
945 p_to.attribute2 := p_from.attribute2;
946 p_to.attribute3 := p_from.attribute3;
947 p_to.attribute4 := p_from.attribute4;
948 p_to.attribute5 := p_from.attribute5;
949 p_to.attribute6 := p_from.attribute6;
950 p_to.attribute7 := p_from.attribute7;
951 p_to.attribute8 := p_from.attribute8;
952 p_to.attribute9 := p_from.attribute9;
953 p_to.attribute10 := p_from.attribute10;
954 p_to.attribute11 := p_from.attribute11;
955 p_to.attribute12 := p_from.attribute12;
956 p_to.attribute13 := p_from.attribute13;
957 p_to.attribute14 := p_from.attribute14;
958 p_to.attribute15 := p_from.attribute15;
959 END migrate;
960 PROCEDURE migrate (
961 p_from IN cgp_rec_type,
962 p_to IN OUT NOCOPY cgpv_rec_type
963 ) IS
964 BEGIN
965 p_to.id := p_from.id;
966 p_to.public_yn := p_from.public_yn;
967 p_to.object_version_number := p_from.object_version_number;
968 p_to.created_by := p_from.created_by;
969 p_to.creation_date := p_from.creation_date;
970 p_to.last_updated_by := p_from.last_updated_by;
971 p_to.last_update_date := p_from.last_update_date;
972 p_to.user_id := p_from.user_id;
973 p_to.last_update_login := p_from.last_update_login;
974 p_to.attribute_category := p_from.attribute_category;
975 p_to.attribute1 := p_from.attribute1;
976 p_to.attribute2 := p_from.attribute2;
977 p_to.attribute3 := p_from.attribute3;
978 p_to.attribute4 := p_from.attribute4;
979 p_to.attribute5 := p_from.attribute5;
980 p_to.attribute6 := p_from.attribute6;
981 p_to.attribute7 := p_from.attribute7;
982 p_to.attribute8 := p_from.attribute8;
983 p_to.attribute9 := p_from.attribute9;
984 p_to.attribute10 := p_from.attribute10;
985 p_to.attribute11 := p_from.attribute11;
986 p_to.attribute12 := p_from.attribute12;
987 p_to.attribute13 := p_from.attribute13;
988 p_to.attribute14 := p_from.attribute14;
989 p_to.attribute15 := p_from.attribute15;
990 END migrate;
991 PROCEDURE migrate (
992 p_from IN cgpv_rec_type,
993 p_to OUT NOCOPY OkcContractGroupsTlRecType
994 ) IS
995 BEGIN
996 p_to.id := p_from.id;
997 p_to.sfwt_flag := p_from.sfwt_flag;
998 p_to.name := p_from.name;
999 p_to.short_description := p_from.short_description;
1000 p_to.created_by := p_from.created_by;
1001 p_to.creation_date := p_from.creation_date;
1002 p_to.last_updated_by := p_from.last_updated_by;
1003 p_to.last_update_date := p_from.last_update_date;
1004 p_to.last_update_login := p_from.last_update_login;
1005 END migrate;
1006 PROCEDURE migrate (
1007 p_from IN OkcContractGroupsTlRecType,
1008 p_to IN OUT NOCOPY cgpv_rec_type
1009 ) IS
1010 BEGIN
1011 p_to.id := p_from.id;
1012 p_to.sfwt_flag := p_from.sfwt_flag;
1013 p_to.name := p_from.name;
1014 p_to.short_description := p_from.short_description;
1015 p_to.created_by := p_from.created_by;
1019 p_to.last_update_login := p_from.last_update_login;
1016 p_to.creation_date := p_from.creation_date;
1017 p_to.last_updated_by := p_from.last_updated_by;
1018 p_to.last_update_date := p_from.last_update_date;
1020 END migrate;
1021
1022 ---------------------------------------------------------------------------
1023 -- PROCEDURE validate_row
1024 ---------------------------------------------------------------------------
1025 --------------------------------------------
1026 -- validate_row for:OKC_K_GROUPS_V --
1027 --------------------------------------------
1028 PROCEDURE validate_row(
1029 p_api_version IN NUMBER,
1030 p_init_msg_list IN VARCHAR2 ,
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2,
1034 p_cgpv_rec IN cgpv_rec_type) IS
1035
1036 l_api_version CONSTANT NUMBER := 1;
1037 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1038 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1039 l_cgpv_rec cgpv_rec_type := p_cgpv_rec;
1040 l_cgp_rec cgp_rec_type;
1041 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
1042 BEGIN
1043 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1044 G_PKG_NAME,
1045 p_init_msg_list,
1046 l_api_version,
1047 p_api_version,
1048 '_PVT',
1049 x_return_status);
1050 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1051 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1052 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1053 RAISE OKC_API.G_EXCEPTION_ERROR;
1054 END IF;
1055 --- Validate all non-missing attributes (Item Level Validation)
1056 l_return_status := Validate_Attributes(l_cgpv_rec);
1057 --- If any errors happen abort API
1058 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1059 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1060 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1061 RAISE OKC_API.G_EXCEPTION_ERROR;
1062 END IF;
1063 l_return_status := Validate_Record(l_cgpv_rec);
1064 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1066 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1067 RAISE OKC_API.G_EXCEPTION_ERROR;
1068 END IF;
1069 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1070 EXCEPTION
1071 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1072 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1073 (
1074 l_api_name,
1075 G_PKG_NAME,
1076 'OKC_API.G_RET_STS_ERROR',
1077 x_msg_count,
1078 x_msg_data,
1079 '_PVT'
1080 );
1081 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1082 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1083 (
1084 l_api_name,
1085 G_PKG_NAME,
1086 'OKC_API.G_RET_STS_UNEXP_ERROR',
1087 x_msg_count,
1088 x_msg_data,
1089 '_PVT'
1090 );
1091 WHEN OTHERS THEN
1092 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1093 (
1094 l_api_name,
1095 G_PKG_NAME,
1096 'OTHERS',
1097 x_msg_count,
1098 x_msg_data,
1099 '_PVT'
1100 );
1101 END validate_row;
1102 ------------------------------------------
1103 -- PL/SQL TBL validate_row for:CGPV_TBL --
1104 ------------------------------------------
1105 PROCEDURE validate_row(
1106 p_api_version IN NUMBER,
1107 p_init_msg_list IN VARCHAR2 ,
1108 x_return_status OUT NOCOPY VARCHAR2,
1109 x_msg_count OUT NOCOPY NUMBER,
1110 x_msg_data OUT NOCOPY VARCHAR2,
1111 p_cgpv_tbl IN cgpv_tbl_type) IS
1112
1113 l_api_version CONSTANT NUMBER := 1;
1114 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1115 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1116 i NUMBER := 0;
1117 BEGIN
1118 -- Make sure PL/SQL table has records in it before passing
1119 IF (p_cgpv_tbl.COUNT > 0) THEN
1120 i := p_cgpv_tbl.FIRST;
1121 LOOP
1122 validate_row (
1123 p_api_version => p_api_version,
1124 p_init_msg_list => p_init_msg_list,
1125 x_return_status => x_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_cgpv_rec => p_cgpv_tbl(i));
1129 EXIT WHEN (i = p_cgpv_tbl.LAST);
1130 i := p_cgpv_tbl.NEXT(i);
1131 END LOOP;
1132 END IF;
1133 EXCEPTION
1134 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1135 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1136 (
1137 l_api_name,
1138 G_PKG_NAME,
1139 'OKC_API.G_RET_STS_ERROR',
1143 );
1140 x_msg_count,
1141 x_msg_data,
1142 '_PVT'
1144 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1145 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1146 (
1147 l_api_name,
1148 G_PKG_NAME,
1149 'OKC_API.G_RET_STS_UNEXP_ERROR',
1150 x_msg_count,
1151 x_msg_data,
1152 '_PVT'
1153 );
1154 WHEN OTHERS THEN
1155 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1156 (
1157 l_api_name,
1158 G_PKG_NAME,
1159 'OTHERS',
1160 x_msg_count,
1161 x_msg_data,
1162 '_PVT'
1163 );
1164 END validate_row;
1165
1166 ---------------------------------------------------------------------------
1167 -- PROCEDURE insert_row
1168 ---------------------------------------------------------------------------
1169 ------------------------------------------
1170 -- insert_row for:OKC_K_GROUPS_B --
1171 ------------------------------------------
1172 PROCEDURE insert_row(
1173 p_init_msg_list IN VARCHAR2 ,
1174 x_return_status OUT NOCOPY VARCHAR2,
1175 x_msg_count OUT NOCOPY NUMBER,
1176 x_msg_data OUT NOCOPY VARCHAR2,
1177 p_cgp_rec IN cgp_rec_type,
1178 x_cgp_rec OUT NOCOPY cgp_rec_type) IS
1179
1180 l_api_version CONSTANT NUMBER := 1;
1181 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1183 l_cgp_rec cgp_rec_type := p_cgp_rec;
1184 l_def_cgp_rec cgp_rec_type;
1185 ----------------------------------------------
1186 -- Set_Attributes for:OKC_K_GROUPS_B --
1187 ----------------------------------------------
1188 FUNCTION Set_Attributes (
1189 p_cgp_rec IN cgp_rec_type,
1190 x_cgp_rec OUT NOCOPY cgp_rec_type
1191 ) RETURN VARCHAR2 IS
1192 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1193 BEGIN
1194 x_cgp_rec := p_cgp_rec;
1195 RETURN(l_return_status);
1196 END Set_Attributes;
1197 BEGIN
1198 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1199 p_init_msg_list,
1200 '_PVT',
1201 x_return_status);
1202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1203 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1204 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1205 RAISE OKC_API.G_EXCEPTION_ERROR;
1206 END IF;
1207 --- Setting item attributes
1208 l_return_status := Set_Attributes(
1209 p_cgp_rec, -- IN
1210 l_cgp_rec); -- OUT
1211 --- If any errors happen abort API
1212 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1214 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1215 RAISE OKC_API.G_EXCEPTION_ERROR;
1216 END IF;
1217 INSERT INTO OKC_K_GROUPS_B(
1218 id,
1219 public_yn,
1220 object_version_number,
1221 created_by,
1222 creation_date,
1223 last_updated_by,
1224 last_update_date,
1225 user_id,
1226 last_update_login,
1227 attribute_category,
1228 attribute1,
1229 attribute2,
1230 attribute3,
1231 attribute4,
1232 attribute5,
1233 attribute6,
1234 attribute7,
1235 attribute8,
1236 attribute9,
1237 attribute10,
1238 attribute11,
1239 attribute12,
1240 attribute13,
1241 attribute14,
1242 attribute15)
1243 VALUES (
1244 l_cgp_rec.id,
1245 l_cgp_rec.public_yn,
1246 l_cgp_rec.object_version_number,
1247 l_cgp_rec.created_by,
1248 l_cgp_rec.creation_date,
1249 l_cgp_rec.last_updated_by,
1250 l_cgp_rec.last_update_date,
1251 l_cgp_rec.user_id,
1252 l_cgp_rec.last_update_login,
1253 l_cgp_rec.attribute_category,
1254 l_cgp_rec.attribute1,
1255 l_cgp_rec.attribute2,
1256 l_cgp_rec.attribute3,
1257 l_cgp_rec.attribute4,
1258 l_cgp_rec.attribute5,
1259 l_cgp_rec.attribute6,
1260 l_cgp_rec.attribute7,
1261 l_cgp_rec.attribute8,
1262 l_cgp_rec.attribute9,
1263 l_cgp_rec.attribute10,
1264 l_cgp_rec.attribute11,
1265 l_cgp_rec.attribute12,
1266 l_cgp_rec.attribute13,
1267 l_cgp_rec.attribute14,
1268 l_cgp_rec.attribute15);
1269 -- Set OUT values
1270 x_cgp_rec := l_cgp_rec;
1271 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1272 EXCEPTION
1273 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1274 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1275 (
1276 l_api_name,
1277 G_PKG_NAME,
1278 'OKC_API.G_RET_STS_ERROR',
1279 x_msg_count,
1280 x_msg_data,
1281 '_PVT'
1282 );
1286 l_api_name,
1283 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1284 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1285 (
1287 G_PKG_NAME,
1288 'OKC_API.G_RET_STS_UNEXP_ERROR',
1289 x_msg_count,
1290 x_msg_data,
1291 '_PVT'
1292 );
1293 WHEN OTHERS THEN
1294 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1295 (
1296 l_api_name,
1297 G_PKG_NAME,
1298 'OTHERS',
1299 x_msg_count,
1300 x_msg_data,
1301 '_PVT'
1302 );
1303 END insert_row;
1304 -------------------------------------------
1305 -- insert_row for:OKC_K_GROUPS_TL --
1306 -------------------------------------------
1307 PROCEDURE insert_row(
1308 p_init_msg_list IN VARCHAR2 ,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1311 x_msg_data OUT NOCOPY VARCHAR2,
1312 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
1313 x_okc_K_groups_tl_rec OUT NOCOPY OkcContractGroupsTlRecType) IS
1314
1315 l_api_version CONSTANT NUMBER := 1;
1316 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1317 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1318 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType := p_okc_K_groups_tl_rec;
1319 ldefokccontractgroupstlrec OkcContractGroupsTlRecType;
1320 CURSOR get_languages IS
1321 SELECT *
1322 FROM FND_LANGUAGES
1323 WHERE INSTALLED_FLAG IN ('I', 'B');
1324 -----------------------------------------------
1325 -- Set_Attributes for:OKC_K_GROUPS_TL --
1326 -----------------------------------------------
1327 FUNCTION Set_Attributes (
1328 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
1329 x_okc_K_groups_tl_rec OUT NOCOPY OkcContractGroupsTlRecType
1330 ) RETURN VARCHAR2 IS
1331 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1332 BEGIN
1333 x_okc_K_groups_tl_rec := p_okc_K_groups_tl_rec;
1334 x_okc_K_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
1335 x_okc_K_groups_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
1336 RETURN(l_return_status);
1337 END Set_Attributes;
1338 BEGIN
1339 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1340 p_init_msg_list,
1341 '_PVT',
1342 x_return_status);
1343 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1344 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1346 RAISE OKC_API.G_EXCEPTION_ERROR;
1347 END IF;
1348 --- Setting item attributes
1349 l_return_status := Set_Attributes(
1350 p_okc_K_groups_tl_rec, -- IN
1351 l_okc_K_groups_tl_rec); -- OUT
1352 --- If any errors happen abort API
1353 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1354 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1355 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1356 RAISE OKC_API.G_EXCEPTION_ERROR;
1357 END IF;
1358 FOR l_lang_rec IN get_languages LOOP
1359 l_okc_K_groups_tl_rec.language := l_lang_rec.language_code;
1360 INSERT INTO OKC_K_GROUPS_TL(
1361 id,
1362 language,
1363 source_lang,
1364 sfwt_flag,
1365 name,
1366 short_description,
1367 created_by,
1368 creation_date,
1369 last_updated_by,
1370 last_update_date,
1371 last_update_login)
1372 VALUES (
1373 l_okc_K_groups_tl_rec.id,
1374 l_okc_K_groups_tl_rec.language,
1375 l_okc_K_groups_tl_rec.source_lang,
1376 l_okc_K_groups_tl_rec.sfwt_flag,
1377 l_okc_K_groups_tl_rec.name,
1378 l_okc_K_groups_tl_rec.short_description,
1379 l_okc_K_groups_tl_rec.created_by,
1380 l_okc_K_groups_tl_rec.creation_date,
1381 l_okc_K_groups_tl_rec.last_updated_by,
1382 l_okc_K_groups_tl_rec.last_update_date,
1383 l_okc_K_groups_tl_rec.last_update_login);
1384 END LOOP;
1385 -- Set OUT values
1386 x_okc_K_groups_tl_rec := l_okc_K_groups_tl_rec;
1387 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1388 EXCEPTION
1389 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1390 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1391 (
1392 l_api_name,
1393 G_PKG_NAME,
1394 'OKC_API.G_RET_STS_ERROR',
1395 x_msg_count,
1396 x_msg_data,
1397 '_PVT'
1398 );
1399 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1400 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1401 (
1402 l_api_name,
1403 G_PKG_NAME,
1404 'OKC_API.G_RET_STS_UNEXP_ERROR',
1405 x_msg_count,
1406 x_msg_data,
1407 '_PVT'
1408 );
1409 WHEN OTHERS THEN
1410 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1411 (
1412 l_api_name,
1413 G_PKG_NAME,
1414 'OTHERS',
1415 x_msg_count,
1419 END insert_row;
1416 x_msg_data,
1417 '_PVT'
1418 );
1420 ------------------------------------------
1421 -- insert_row for:OKC_K_GROUPS_V --
1422 ------------------------------------------
1423 PROCEDURE insert_row(
1424 p_api_version IN NUMBER,
1425 p_init_msg_list IN VARCHAR2 ,
1426 x_return_status OUT NOCOPY VARCHAR2,
1427 x_msg_count OUT NOCOPY NUMBER,
1428 x_msg_data OUT NOCOPY VARCHAR2,
1429 p_cgpv_rec IN cgpv_rec_type,
1430 x_cgpv_rec OUT NOCOPY cgpv_rec_type) IS
1431
1432 l_api_version CONSTANT NUMBER := 1;
1433 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1434 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1435 l_cgpv_rec cgpv_rec_type;
1436 l_def_cgpv_rec cgpv_rec_type;
1437 l_cgp_rec cgp_rec_type;
1438 lx_cgp_rec cgp_rec_type;
1439 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
1440 lx_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
1441 -------------------------------
1442 -- FUNCTION fill_who_columns --
1443 -------------------------------
1444 FUNCTION fill_who_columns (
1445 p_cgpv_rec IN cgpv_rec_type
1446 ) RETURN cgpv_rec_type IS
1447 l_cgpv_rec cgpv_rec_type := p_cgpv_rec;
1448 BEGIN
1449 l_cgpv_rec.CREATION_DATE := SYSDATE;
1450 l_cgpv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1451 l_cgpv_rec.LAST_UPDATE_DATE := l_cgpv_rec.CREATION_DATE;
1452 l_cgpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1453 l_cgpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1454 RETURN(l_cgpv_rec);
1455 END fill_who_columns;
1456 ----------------------------------------------
1457 -- Set_Attributes for:OKC_K_GROUPS_V --
1458 ----------------------------------------------
1459 FUNCTION Set_Attributes (
1460 p_cgpv_rec IN cgpv_rec_type,
1461 x_cgpv_rec OUT NOCOPY cgpv_rec_type
1462 ) RETURN VARCHAR2 IS
1463 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1464 BEGIN
1465 x_cgpv_rec := p_cgpv_rec;
1466 x_cgpv_rec.OBJECT_VERSION_NUMBER := 1;
1467 x_cgpv_rec.SFWT_FLAG := 'N';
1468 RETURN(l_return_status);
1469 END Set_Attributes;
1470 BEGIN
1471 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1472 G_PKG_NAME,
1473 p_init_msg_list,
1474 l_api_version,
1475 p_api_version,
1476 '_PVT',
1477 x_return_status);
1478 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1479 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1480 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1481 RAISE OKC_API.G_EXCEPTION_ERROR;
1482 END IF;
1483 l_cgpv_rec := null_out_defaults(p_cgpv_rec);
1484 -- Set primary key value
1485 l_cgpv_rec.ID := get_seq_id;
1486 --- Setting item attributes
1487 l_return_status := Set_Attributes(
1488 l_cgpv_rec, -- IN
1489 l_def_cgpv_rec); -- OUT
1490 --- If any errors happen abort API
1491 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1492 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1493 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1494 RAISE OKC_API.G_EXCEPTION_ERROR;
1495 END IF;
1496 l_def_cgpv_rec := fill_who_columns(l_def_cgpv_rec);
1497 --- Validate all non-missing attributes (Item Level Validation)
1498 l_return_status := Validate_Attributes(l_def_cgpv_rec);
1499 --- If any errors happen abort API
1500 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1501 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1502 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1503 RAISE OKC_API.G_EXCEPTION_ERROR;
1504 END IF;
1505 --- Check user has got the privilege to create public groups
1506 Check_Pub_Group_Privilege(l_return_status, l_def_cgpv_rec);
1507 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1508 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1509 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1510 RAISE OKC_API.G_EXCEPTION_ERROR;
1511 END IF;
1512 l_return_status := Validate_Record(l_def_cgpv_rec);
1513 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1514 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1515 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1516 RAISE OKC_API.G_EXCEPTION_ERROR;
1517 END IF;
1518 --------------------------------------
1519 -- Move VIEW record to "Child" records
1520 --------------------------------------
1521 migrate(l_def_cgpv_rec, l_cgp_rec);
1522 migrate(l_def_cgpv_rec, l_okc_K_groups_tl_rec);
1523 --------------------------------------------
1524 -- Call the INSERT_ROW for each child record
1525 --------------------------------------------
1526 insert_row(
1527 p_init_msg_list,
1528 x_return_status,
1529 x_msg_count,
1530 x_msg_data,
1531 l_cgp_rec,
1532 lx_cgp_rec
1536 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1533 );
1534 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1535 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1537 RAISE OKC_API.G_EXCEPTION_ERROR;
1538 END IF;
1539 migrate(lx_cgp_rec, l_def_cgpv_rec);
1540 insert_row(
1541 p_init_msg_list,
1542 x_return_status,
1543 x_msg_count,
1544 x_msg_data,
1545 l_okc_K_groups_tl_rec,
1546 lx_okc_K_groups_tl_rec
1547 );
1548 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1550 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1551 RAISE OKC_API.G_EXCEPTION_ERROR;
1552 END IF;
1553 migrate(lx_okc_K_groups_tl_rec, l_def_cgpv_rec);
1554 -- Set OUT values
1555 x_cgpv_rec := l_def_cgpv_rec;
1556 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1557 EXCEPTION
1558 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1559 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1560 (
1561 l_api_name,
1562 G_PKG_NAME,
1563 'OKC_API.G_RET_STS_ERROR',
1564 x_msg_count,
1565 x_msg_data,
1566 '_PVT'
1567 );
1568 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1569 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1570 (
1571 l_api_name,
1572 G_PKG_NAME,
1573 'OKC_API.G_RET_STS_UNEXP_ERROR',
1574 x_msg_count,
1575 x_msg_data,
1576 '_PVT'
1577 );
1578 WHEN OTHERS THEN
1579 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1580 (
1581 l_api_name,
1582 G_PKG_NAME,
1583 'OTHERS',
1584 x_msg_count,
1585 x_msg_data,
1586 '_PVT'
1587 );
1588 END insert_row;
1589 ----------------------------------------
1590 -- PL/SQL TBL insert_row for:CGPV_TBL --
1591 ----------------------------------------
1592 PROCEDURE insert_row(
1593 p_api_version IN NUMBER,
1594 p_init_msg_list IN VARCHAR2 ,
1595 x_return_status OUT NOCOPY VARCHAR2,
1596 x_msg_count OUT NOCOPY NUMBER,
1597 x_msg_data OUT NOCOPY VARCHAR2,
1598 p_cgpv_tbl IN cgpv_tbl_type,
1599 x_cgpv_tbl OUT NOCOPY cgpv_tbl_type) IS
1600
1601 l_api_version CONSTANT NUMBER := 1;
1602 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1603 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1604 i NUMBER := 0;
1605 BEGIN
1606 -- Make sure PL/SQL table has records in it before passing
1607 IF (p_cgpv_tbl.COUNT > 0) THEN
1608 i := p_cgpv_tbl.FIRST;
1609 LOOP
1610 insert_row (
1611 p_api_version => p_api_version,
1612 p_init_msg_list => p_init_msg_list,
1613 x_return_status => x_return_status,
1614 x_msg_count => x_msg_count,
1615 x_msg_data => x_msg_data,
1616 p_cgpv_rec => p_cgpv_tbl(i),
1617 x_cgpv_rec => x_cgpv_tbl(i));
1618 EXIT WHEN (i = p_cgpv_tbl.LAST);
1619 i := p_cgpv_tbl.NEXT(i);
1620 END LOOP;
1621 END IF;
1622 EXCEPTION
1623 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1624 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1625 (
1626 l_api_name,
1627 G_PKG_NAME,
1628 'OKC_API.G_RET_STS_ERROR',
1629 x_msg_count,
1630 x_msg_data,
1631 '_PVT'
1632 );
1633 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1634 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1635 (
1636 l_api_name,
1637 G_PKG_NAME,
1638 'OKC_API.G_RET_STS_UNEXP_ERROR',
1639 x_msg_count,
1640 x_msg_data,
1641 '_PVT'
1642 );
1643 WHEN OTHERS THEN
1644 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1645 (
1646 l_api_name,
1647 G_PKG_NAME,
1648 'OTHERS',
1649 x_msg_count,
1650 x_msg_data,
1651 '_PVT'
1652 );
1653 END insert_row;
1654
1655 ---------------------------------------------------------------------------
1656 -- PROCEDURE lock_row
1657 ---------------------------------------------------------------------------
1658 ----------------------------------------
1659 -- lock_row for:OKC_K_GROUPS_B --
1660 ----------------------------------------
1661 PROCEDURE lock_row(
1662 p_init_msg_list IN VARCHAR2 ,
1663 x_return_status OUT NOCOPY VARCHAR2,
1664 x_msg_count OUT NOCOPY NUMBER,
1665 x_msg_data OUT NOCOPY VARCHAR2,
1666 p_cgp_rec IN cgp_rec_type) IS
1667
1668 E_Resource_Busy EXCEPTION;
1669 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1670 CURSOR lock_csr (p_cgp_rec IN cgp_rec_type) IS
1671 SELECT OBJECT_VERSION_NUMBER
1672 FROM OKC_K_GROUPS_B
1673 WHERE ID = p_cgp_rec.id
1677 CURSOR lchk_csr (p_cgp_rec IN cgp_rec_type) IS
1674 AND OBJECT_VERSION_NUMBER = p_cgp_rec.object_version_number
1675 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1676
1678 SELECT OBJECT_VERSION_NUMBER
1679 FROM OKC_K_GROUPS_B
1680 WHERE ID = p_cgp_rec.id;
1681 l_api_version CONSTANT NUMBER := 1;
1682 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1683 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1684 l_object_version_number OKC_K_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
1685 lc_object_version_number OKC_K_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
1686 l_row_notfound BOOLEAN := FALSE;
1687 lc_row_notfound BOOLEAN := FALSE;
1688 BEGIN
1689 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1690 p_init_msg_list,
1691 '_PVT',
1692 x_return_status);
1693 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1694 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1695 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1696 RAISE OKC_API.G_EXCEPTION_ERROR;
1697 END IF;
1698 BEGIN
1699 OPEN lock_csr(p_cgp_rec);
1700 FETCH lock_csr INTO l_object_version_number;
1701 l_row_notfound := lock_csr%NOTFOUND;
1702 CLOSE lock_csr;
1703 EXCEPTION
1704 WHEN E_Resource_Busy THEN
1705 IF (lock_csr%ISOPEN) THEN
1706 CLOSE lock_csr;
1707 END IF;
1708 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1709 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1710 END;
1711
1712 IF ( l_row_notfound ) THEN
1713 OPEN lchk_csr(p_cgp_rec);
1714 FETCH lchk_csr INTO lc_object_version_number;
1715 lc_row_notfound := lchk_csr%NOTFOUND;
1716 CLOSE lchk_csr;
1717 END IF;
1718 IF (lc_row_notfound) THEN
1719 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1720 RAISE OKC_API.G_EXCEPTION_ERROR;
1721 ELSIF lc_object_version_number > p_cgp_rec.object_version_number THEN
1722 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1723 RAISE OKC_API.G_EXCEPTION_ERROR;
1724 ELSIF lc_object_version_number <> p_cgp_rec.object_version_number THEN
1725 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1726 RAISE OKC_API.G_EXCEPTION_ERROR;
1727 ELSIF lc_object_version_number = -1 THEN
1728 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1729 RAISE OKC_API.G_EXCEPTION_ERROR;
1730 END IF;
1731 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1732 EXCEPTION
1733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1734 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1735 (
1736 l_api_name,
1737 G_PKG_NAME,
1738 'OKC_API.G_RET_STS_ERROR',
1739 x_msg_count,
1740 x_msg_data,
1741 '_PVT'
1742 );
1743 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1744 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1745 (
1746 l_api_name,
1747 G_PKG_NAME,
1748 'OKC_API.G_RET_STS_UNEXP_ERROR',
1749 x_msg_count,
1750 x_msg_data,
1751 '_PVT'
1752 );
1753 WHEN OTHERS THEN
1754 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1755 (
1756 l_api_name,
1757 G_PKG_NAME,
1758 'OTHERS',
1759 x_msg_count,
1760 x_msg_data,
1761 '_PVT'
1762 );
1763 END lock_row;
1764 -----------------------------------------
1765 -- lock_row for:OKC_K_GROUPS_TL --
1766 -----------------------------------------
1767 PROCEDURE lock_row(
1768 p_init_msg_list IN VARCHAR2 ,
1769 x_return_status OUT NOCOPY VARCHAR2,
1770 x_msg_count OUT NOCOPY NUMBER,
1771 x_msg_data OUT NOCOPY VARCHAR2,
1772 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType) IS
1773
1774 E_Resource_Busy EXCEPTION;
1775 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1776 CURSOR lock_csr (p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType) IS
1777 SELECT *
1778 FROM OKC_K_GROUPS_TL
1779 WHERE ID = p_okc_K_groups_tl_rec.id
1780 FOR UPDATE NOWAIT;
1781
1782 l_api_version CONSTANT NUMBER := 1;
1783 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1784 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1785 l_lock_var lock_csr%ROWTYPE;
1786 l_row_notfound BOOLEAN := FALSE;
1787 lc_row_notfound BOOLEAN := FALSE;
1788 BEGIN
1789 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1790 p_init_msg_list,
1791 '_PVT',
1792 x_return_status);
1793 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1794 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1795 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1796 RAISE OKC_API.G_EXCEPTION_ERROR;
1797 END IF;
1798 BEGIN
1799 OPEN lock_csr(p_okc_K_groups_tl_rec);
1800 FETCH lock_csr INTO l_lock_var;
1801 l_row_notfound := lock_csr%NOTFOUND;
1805 IF (lock_csr%ISOPEN) THEN
1802 CLOSE lock_csr;
1803 EXCEPTION
1804 WHEN E_Resource_Busy THEN
1806 CLOSE lock_csr;
1807 END IF;
1808 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1809 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1810 END;
1811
1812 IF ( l_row_notfound ) THEN
1813 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1814 RAISE OKC_API.G_EXCEPTION_ERROR;
1815 END IF;
1816 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1817 EXCEPTION
1818 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1819 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1820 (
1821 l_api_name,
1822 G_PKG_NAME,
1823 'OKC_API.G_RET_STS_ERROR',
1824 x_msg_count,
1825 x_msg_data,
1826 '_PVT'
1827 );
1828 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1829 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1830 (
1831 l_api_name,
1832 G_PKG_NAME,
1833 'OKC_API.G_RET_STS_UNEXP_ERROR',
1834 x_msg_count,
1835 x_msg_data,
1836 '_PVT'
1837 );
1838 WHEN OTHERS THEN
1839 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1840 (
1841 l_api_name,
1842 G_PKG_NAME,
1843 'OTHERS',
1844 x_msg_count,
1845 x_msg_data,
1846 '_PVT'
1847 );
1848 END lock_row;
1849 ----------------------------------------
1850 -- lock_row for:OKC_K_GROUPS_V --
1851 ----------------------------------------
1852 PROCEDURE lock_row(
1853 p_api_version IN NUMBER,
1854 p_init_msg_list IN VARCHAR2 ,
1855 x_return_status OUT NOCOPY VARCHAR2,
1856 x_msg_count OUT NOCOPY NUMBER,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 p_cgpv_rec IN cgpv_rec_type) IS
1859
1860 l_api_version CONSTANT NUMBER := 1;
1861 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1862 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1863 l_cgp_rec cgp_rec_type;
1864 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
1865 BEGIN
1866 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1867 G_PKG_NAME,
1868 p_init_msg_list,
1869 l_api_version,
1870 p_api_version,
1871 '_PVT',
1872 x_return_status);
1873 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1874 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1875 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1876 RAISE OKC_API.G_EXCEPTION_ERROR;
1877 END IF;
1878 --------------------------------------
1879 -- Move VIEW record to "Child" records
1880 --------------------------------------
1881 migrate(p_cgpv_rec, l_cgp_rec);
1882 migrate(p_cgpv_rec, l_okc_K_groups_tl_rec);
1883 --------------------------------------------
1884 -- Call the LOCK_ROW for each child record
1885 --------------------------------------------
1886 lock_row(
1887 p_init_msg_list,
1888 x_return_status,
1889 x_msg_count,
1890 x_msg_data,
1891 l_cgp_rec
1892 );
1893 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1894 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1895 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1896 RAISE OKC_API.G_EXCEPTION_ERROR;
1897 END IF;
1898 lock_row(
1899 p_init_msg_list,
1900 x_return_status,
1901 x_msg_count,
1902 x_msg_data,
1903 l_okc_K_groups_tl_rec
1904 );
1905 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1906 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1907 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1908 RAISE OKC_API.G_EXCEPTION_ERROR;
1909 END IF;
1910 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1911 EXCEPTION
1912 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1913 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1914 (
1915 l_api_name,
1916 G_PKG_NAME,
1917 'OKC_API.G_RET_STS_ERROR',
1918 x_msg_count,
1919 x_msg_data,
1920 '_PVT'
1921 );
1922 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1923 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1924 (
1925 l_api_name,
1926 G_PKG_NAME,
1927 'OKC_API.G_RET_STS_UNEXP_ERROR',
1928 x_msg_count,
1929 x_msg_data,
1930 '_PVT'
1931 );
1932 WHEN OTHERS THEN
1933 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1934 (
1935 l_api_name,
1936 G_PKG_NAME,
1937 'OTHERS',
1938 x_msg_count,
1939 x_msg_data,
1940 '_PVT'
1941 );
1942 END lock_row;
1943 --------------------------------------
1944 -- PL/SQL TBL lock_row for:CGPV_TBL --
1945 --------------------------------------
1949 x_return_status OUT NOCOPY VARCHAR2,
1946 PROCEDURE lock_row(
1947 p_api_version IN NUMBER,
1948 p_init_msg_list IN VARCHAR2 ,
1950 x_msg_count OUT NOCOPY NUMBER,
1951 x_msg_data OUT NOCOPY VARCHAR2,
1952 p_cgpv_tbl IN cgpv_tbl_type) IS
1953
1954 l_api_version CONSTANT NUMBER := 1;
1955 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1956 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1957 i NUMBER := 0;
1958 BEGIN
1959 -- Make sure PL/SQL table has records in it before passing
1960 IF (p_cgpv_tbl.COUNT > 0) THEN
1961 i := p_cgpv_tbl.FIRST;
1962 LOOP
1963 lock_row (
1964 p_api_version => p_api_version,
1965 p_init_msg_list => p_init_msg_list,
1966 x_return_status => x_return_status,
1967 x_msg_count => x_msg_count,
1968 x_msg_data => x_msg_data,
1969 p_cgpv_rec => p_cgpv_tbl(i));
1970 EXIT WHEN (i = p_cgpv_tbl.LAST);
1971 i := p_cgpv_tbl.NEXT(i);
1972 END LOOP;
1973 END IF;
1974 EXCEPTION
1975 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1976 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1977 (
1978 l_api_name,
1979 G_PKG_NAME,
1980 'OKC_API.G_RET_STS_ERROR',
1981 x_msg_count,
1982 x_msg_data,
1983 '_PVT'
1984 );
1985 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1986 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1987 (
1988 l_api_name,
1989 G_PKG_NAME,
1990 'OKC_API.G_RET_STS_UNEXP_ERROR',
1991 x_msg_count,
1992 x_msg_data,
1993 '_PVT'
1994 );
1995 WHEN OTHERS THEN
1996 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1997 (
1998 l_api_name,
1999 G_PKG_NAME,
2000 'OTHERS',
2001 x_msg_count,
2002 x_msg_data,
2003 '_PVT'
2004 );
2005 END lock_row;
2006
2007 ---------------------------------------------------------------------------
2008 -- PROCEDURE update_row
2009 ---------------------------------------------------------------------------
2010 ------------------------------------------
2011 -- update_row for:OKC_K_GROUPS_B --
2012 ------------------------------------------
2013 PROCEDURE update_row(
2014 p_init_msg_list IN VARCHAR2 ,
2015 x_return_status OUT NOCOPY VARCHAR2,
2016 x_msg_count OUT NOCOPY NUMBER,
2017 x_msg_data OUT NOCOPY VARCHAR2,
2018 p_cgp_rec IN cgp_rec_type,
2019 x_cgp_rec OUT NOCOPY cgp_rec_type) IS
2020
2021 l_api_version CONSTANT NUMBER := 1;
2022 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2023 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2024 l_cgp_rec cgp_rec_type := p_cgp_rec;
2025 l_def_cgp_rec cgp_rec_type;
2026 l_row_notfound BOOLEAN := TRUE;
2027 ----------------------------------
2028 -- FUNCTION populate_new_record --
2029 ----------------------------------
2030 FUNCTION populate_new_record (
2031 p_cgp_rec IN cgp_rec_type,
2032 x_cgp_rec OUT NOCOPY cgp_rec_type
2033 ) RETURN VARCHAR2 IS
2034 l_cgp_rec cgp_rec_type;
2035 l_row_notfound BOOLEAN := TRUE;
2036 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2037 BEGIN
2038 x_cgp_rec := p_cgp_rec;
2039 -- Get current database values
2040 l_cgp_rec := get_rec(p_cgp_rec, l_row_notfound);
2041 IF (l_row_notfound) THEN
2042 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2043 END IF;
2044 IF (x_cgp_rec.id = OKC_API.G_MISS_NUM)
2045 THEN
2046 x_cgp_rec.id := l_cgp_rec.id;
2047 END IF;
2048 IF (x_cgp_rec.public_yn = OKC_API.G_MISS_CHAR)
2049 THEN
2050 x_cgp_rec.public_yn := l_cgp_rec.public_yn;
2051 END IF;
2052 IF (x_cgp_rec.object_version_number = OKC_API.G_MISS_NUM)
2053 THEN
2054 x_cgp_rec.object_version_number := l_cgp_rec.object_version_number;
2055 END IF;
2056 IF (x_cgp_rec.created_by = OKC_API.G_MISS_NUM)
2057 THEN
2058 x_cgp_rec.created_by := l_cgp_rec.created_by;
2059 END IF;
2060 IF (x_cgp_rec.creation_date = OKC_API.G_MISS_DATE)
2061 THEN
2062 x_cgp_rec.creation_date := l_cgp_rec.creation_date;
2063 END IF;
2064 IF (x_cgp_rec.last_updated_by = OKC_API.G_MISS_NUM)
2065 THEN
2066 x_cgp_rec.last_updated_by := l_cgp_rec.last_updated_by;
2067 END IF;
2068 IF (x_cgp_rec.last_update_date = OKC_API.G_MISS_DATE)
2069 THEN
2070 x_cgp_rec.last_update_date := l_cgp_rec.last_update_date;
2071 END IF;
2072 IF (x_cgp_rec.user_id = OKC_API.G_MISS_NUM)
2073 THEN
2074 x_cgp_rec.user_id := l_cgp_rec.user_id;
2075 END IF;
2079 END IF;
2076 IF (x_cgp_rec.last_update_login = OKC_API.G_MISS_NUM)
2077 THEN
2078 x_cgp_rec.last_update_login := l_cgp_rec.last_update_login;
2080 IF (x_cgp_rec.attribute_category = OKC_API.G_MISS_CHAR)
2081 THEN
2082 x_cgp_rec.attribute_category := l_cgp_rec.attribute_category;
2083 END IF;
2084 IF (x_cgp_rec.attribute1 = OKC_API.G_MISS_CHAR)
2085 THEN
2086 x_cgp_rec.attribute1 := l_cgp_rec.attribute1;
2087 END IF;
2088 IF (x_cgp_rec.attribute2 = OKC_API.G_MISS_CHAR)
2089 THEN
2090 x_cgp_rec.attribute2 := l_cgp_rec.attribute2;
2091 END IF;
2092 IF (x_cgp_rec.attribute3 = OKC_API.G_MISS_CHAR)
2093 THEN
2094 x_cgp_rec.attribute3 := l_cgp_rec.attribute3;
2095 END IF;
2096 IF (x_cgp_rec.attribute4 = OKC_API.G_MISS_CHAR)
2097 THEN
2098 x_cgp_rec.attribute4 := l_cgp_rec.attribute4;
2099 END IF;
2100 IF (x_cgp_rec.attribute5 = OKC_API.G_MISS_CHAR)
2101 THEN
2102 x_cgp_rec.attribute5 := l_cgp_rec.attribute5;
2103 END IF;
2104 IF (x_cgp_rec.attribute6 = OKC_API.G_MISS_CHAR)
2105 THEN
2106 x_cgp_rec.attribute6 := l_cgp_rec.attribute6;
2107 END IF;
2108 IF (x_cgp_rec.attribute7 = OKC_API.G_MISS_CHAR)
2109 THEN
2110 x_cgp_rec.attribute7 := l_cgp_rec.attribute7;
2111 END IF;
2112 IF (x_cgp_rec.attribute8 = OKC_API.G_MISS_CHAR)
2113 THEN
2114 x_cgp_rec.attribute8 := l_cgp_rec.attribute8;
2115 END IF;
2116 IF (x_cgp_rec.attribute9 = OKC_API.G_MISS_CHAR)
2117 THEN
2118 x_cgp_rec.attribute9 := l_cgp_rec.attribute9;
2119 END IF;
2120 IF (x_cgp_rec.attribute10 = OKC_API.G_MISS_CHAR)
2121 THEN
2122 x_cgp_rec.attribute10 := l_cgp_rec.attribute10;
2123 END IF;
2124 IF (x_cgp_rec.attribute11 = OKC_API.G_MISS_CHAR)
2125 THEN
2126 x_cgp_rec.attribute11 := l_cgp_rec.attribute11;
2127 END IF;
2128 IF (x_cgp_rec.attribute12 = OKC_API.G_MISS_CHAR)
2129 THEN
2130 x_cgp_rec.attribute12 := l_cgp_rec.attribute12;
2131 END IF;
2132 IF (x_cgp_rec.attribute13 = OKC_API.G_MISS_CHAR)
2133 THEN
2134 x_cgp_rec.attribute13 := l_cgp_rec.attribute13;
2135 END IF;
2136 IF (x_cgp_rec.attribute14 = OKC_API.G_MISS_CHAR)
2137 THEN
2138 x_cgp_rec.attribute14 := l_cgp_rec.attribute14;
2139 END IF;
2140 IF (x_cgp_rec.attribute15 = OKC_API.G_MISS_CHAR)
2141 THEN
2142 x_cgp_rec.attribute15 := l_cgp_rec.attribute15;
2143 END IF;
2144 RETURN(l_return_status);
2145 END populate_new_record;
2146 ----------------------------------------------
2147 -- Set_Attributes for:OKC_K_GROUPS_B --
2148 ----------------------------------------------
2149 FUNCTION Set_Attributes (
2150 p_cgp_rec IN cgp_rec_type,
2151 x_cgp_rec OUT NOCOPY cgp_rec_type
2152 ) RETURN VARCHAR2 IS
2153 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2154 BEGIN
2155 x_cgp_rec := p_cgp_rec;
2156 RETURN(l_return_status);
2157 END Set_Attributes;
2158 BEGIN
2159 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2160 p_init_msg_list,
2161 '_PVT',
2162 x_return_status);
2163 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2164 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2165 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2166 RAISE OKC_API.G_EXCEPTION_ERROR;
2167 END IF;
2168 --- Setting item attributes
2169 l_return_status := Set_Attributes(
2170 p_cgp_rec, -- IN
2171 l_cgp_rec); -- OUT
2172 --- If any errors happen abort API
2173 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2174 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2175 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2176 RAISE OKC_API.G_EXCEPTION_ERROR;
2177 END IF;
2178 l_return_status := populate_new_record(l_cgp_rec, l_def_cgp_rec);
2179 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2180 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2181 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2182 RAISE OKC_API.G_EXCEPTION_ERROR;
2183 END IF;
2184 UPDATE OKC_K_GROUPS_B
2185 SET PUBLIC_YN = l_def_cgp_rec.public_yn,
2186 OBJECT_VERSION_NUMBER = l_def_cgp_rec.object_version_number,
2187 CREATED_BY = l_def_cgp_rec.created_by,
2188 CREATION_DATE = l_def_cgp_rec.creation_date,
2189 LAST_UPDATED_BY = l_def_cgp_rec.last_updated_by,
2190 LAST_UPDATE_DATE = l_def_cgp_rec.last_update_date,
2191 USER_ID = l_def_cgp_rec.user_id,
2192 LAST_UPDATE_LOGIN = l_def_cgp_rec.last_update_login,
2193 ATTRIBUTE_CATEGORY = l_def_cgp_rec.attribute_category,
2194 ATTRIBUTE1 = l_def_cgp_rec.attribute1,
2195 ATTRIBUTE2 = l_def_cgp_rec.attribute2,
2196 ATTRIBUTE3 = l_def_cgp_rec.attribute3,
2197 ATTRIBUTE4 = l_def_cgp_rec.attribute4,
2198 ATTRIBUTE5 = l_def_cgp_rec.attribute5,
2199 ATTRIBUTE6 = l_def_cgp_rec.attribute6,
2200 ATTRIBUTE7 = l_def_cgp_rec.attribute7,
2204 ATTRIBUTE11 = l_def_cgp_rec.attribute11,
2201 ATTRIBUTE8 = l_def_cgp_rec.attribute8,
2202 ATTRIBUTE9 = l_def_cgp_rec.attribute9,
2203 ATTRIBUTE10 = l_def_cgp_rec.attribute10,
2205 ATTRIBUTE12 = l_def_cgp_rec.attribute12,
2206 ATTRIBUTE13 = l_def_cgp_rec.attribute13,
2207 ATTRIBUTE14 = l_def_cgp_rec.attribute14,
2208 ATTRIBUTE15 = l_def_cgp_rec.attribute15
2209 WHERE ID = l_def_cgp_rec.id;
2210
2211 x_cgp_rec := l_def_cgp_rec;
2212 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2213 EXCEPTION
2214 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2215 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2216 (
2217 l_api_name,
2218 G_PKG_NAME,
2219 'OKC_API.G_RET_STS_ERROR',
2220 x_msg_count,
2221 x_msg_data,
2222 '_PVT'
2223 );
2224 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2225 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2226 (
2227 l_api_name,
2228 G_PKG_NAME,
2229 'OKC_API.G_RET_STS_UNEXP_ERROR',
2230 x_msg_count,
2231 x_msg_data,
2232 '_PVT'
2233 );
2234 WHEN OTHERS THEN
2235 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2236 (
2237 l_api_name,
2238 G_PKG_NAME,
2239 'OTHERS',
2240 x_msg_count,
2241 x_msg_data,
2242 '_PVT'
2243 );
2244 END update_row;
2245 -------------------------------------------
2246 -- update_row for:OKC_K_GROUPS_TL --
2247 -------------------------------------------
2248 PROCEDURE update_row(
2249 p_init_msg_list IN VARCHAR2 ,
2250 x_return_status OUT NOCOPY VARCHAR2,
2251 x_msg_count OUT NOCOPY NUMBER,
2252 x_msg_data OUT NOCOPY VARCHAR2,
2253 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
2254 x_okc_K_groups_tl_rec OUT NOCOPY OkcContractGroupsTlRecType) IS
2255
2256 l_api_version CONSTANT NUMBER := 1;
2257 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2258 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2259 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType := p_okc_K_groups_tl_rec;
2260 ldefokccontractgroupstlrec OkcContractGroupsTlRecType;
2261 l_row_notfound BOOLEAN := TRUE;
2262 ----------------------------------
2263 -- FUNCTION populate_new_record --
2264 ----------------------------------
2265 FUNCTION populate_new_record (
2266 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
2267 x_okc_K_groups_tl_rec OUT NOCOPY OkcContractGroupsTlRecType
2268 ) RETURN VARCHAR2 IS
2269 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
2270 l_row_notfound BOOLEAN := TRUE;
2271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2272 BEGIN
2273 x_okc_K_groups_tl_rec := p_okc_K_groups_tl_rec;
2274 -- Get current database values
2275 l_okc_K_groups_tl_rec := get_rec(p_okc_K_groups_tl_rec, l_row_notfound);
2276 IF (l_row_notfound) THEN
2277 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2278 END IF;
2279 IF (x_okc_K_groups_tl_rec.id = OKC_API.G_MISS_NUM)
2280 THEN
2281 x_okc_K_groups_tl_rec.id := l_okc_K_groups_tl_rec.id;
2282 END IF;
2283 IF (x_okc_K_groups_tl_rec.language = OKC_API.G_MISS_CHAR)
2284 THEN
2285 x_okc_K_groups_tl_rec.language := l_okc_K_groups_tl_rec.language;
2286 END IF;
2287 IF (x_okc_K_groups_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2288 THEN
2289 x_okc_K_groups_tl_rec.source_lang := l_okc_K_groups_tl_rec.source_lang;
2290 END IF;
2291 IF (x_okc_K_groups_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2292 THEN
2293 x_okc_K_groups_tl_rec.sfwt_flag := l_okc_K_groups_tl_rec.sfwt_flag;
2294 END IF;
2295 IF (x_okc_K_groups_tl_rec.name = OKC_API.G_MISS_CHAR)
2296 THEN
2297 x_okc_K_groups_tl_rec.name := l_okc_K_groups_tl_rec.name;
2298 END IF;
2299 IF (x_okc_K_groups_tl_rec.short_description = OKC_API.G_MISS_CHAR)
2300 THEN
2301 x_okc_K_groups_tl_rec.short_description := l_okc_K_groups_tl_rec.short_description;
2302 END IF;
2303 IF (x_okc_K_groups_tl_rec.created_by = OKC_API.G_MISS_NUM)
2304 THEN
2305 x_okc_K_groups_tl_rec.created_by := l_okc_K_groups_tl_rec.created_by;
2306 END IF;
2307 IF (x_okc_K_groups_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2308 THEN
2309 x_okc_K_groups_tl_rec.creation_date := l_okc_K_groups_tl_rec.creation_date;
2310 END IF;
2311 IF (x_okc_K_groups_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2312 THEN
2313 x_okc_K_groups_tl_rec.last_updated_by := l_okc_K_groups_tl_rec.last_updated_by;
2314 END IF;
2315 IF (x_okc_K_groups_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2316 THEN
2317 x_okc_K_groups_tl_rec.last_update_date := l_okc_K_groups_tl_rec.last_update_date;
2318 END IF;
2319 IF (x_okc_K_groups_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2320 THEN
2321 x_okc_K_groups_tl_rec.last_update_login := l_okc_K_groups_tl_rec.last_update_login;
2322 END IF;
2323 RETURN(l_return_status);
2324 END populate_new_record;
2325 -----------------------------------------------
2329 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
2326 -- Set_Attributes for:OKC_K_GROUPS_TL --
2327 -----------------------------------------------
2328 FUNCTION Set_Attributes (
2330 x_okc_K_groups_tl_rec OUT NOCOPY OkcContractGroupsTlRecType
2331 ) RETURN VARCHAR2 IS
2332 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2333 BEGIN
2334 x_okc_K_groups_tl_rec := p_okc_K_groups_tl_rec;
2335 x_okc_K_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2336 x_okc_K_groups_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
2337 RETURN(l_return_status);
2338 END Set_Attributes;
2339 BEGIN
2340 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2341 p_init_msg_list,
2342 '_PVT',
2343 x_return_status);
2344 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2345 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2346 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2347 RAISE OKC_API.G_EXCEPTION_ERROR;
2348 END IF;
2349 --- Setting item attributes
2350 l_return_status := Set_Attributes(
2351 p_okc_K_groups_tl_rec, -- IN
2352 l_okc_K_groups_tl_rec); -- OUT
2353 --- If any errors happen abort API
2354 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2355 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2356 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2357 RAISE OKC_API.G_EXCEPTION_ERROR;
2358 END IF;
2359 l_return_status := populate_new_record(l_okc_K_groups_tl_rec, ldefokccontractgroupstlrec);
2360 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2361 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2362 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2363 RAISE OKC_API.G_EXCEPTION_ERROR;
2364 END IF;
2365 UPDATE OKC_K_GROUPS_TL
2366 SET NAME = ldefokccontractgroupstlrec.name,
2367 SHORT_DESCRIPTION = ldefokccontractgroupstlrec.short_description,
2368 CREATED_BY = ldefokccontractgroupstlrec.created_by,
2369 CREATION_DATE = ldefokccontractgroupstlrec.creation_date,
2370 LAST_UPDATED_BY = ldefokccontractgroupstlrec.last_updated_by,
2371 LAST_UPDATE_DATE = ldefokccontractgroupstlrec.last_update_date,
2372 LAST_UPDATE_LOGIN = ldefokccontractgroupstlrec.last_update_login
2373 WHERE ID = ldefokccontractgroupstlrec.id
2374 AND SOURCE_LANG = USERENV('LANG');
2375
2376 UPDATE OKC_K_GROUPS_TL
2377 SET SFWT_FLAG = 'Y'
2378 WHERE ID = ldefokccontractgroupstlrec.id
2379 AND SOURCE_LANG <> USERENV('LANG');
2380
2381 x_okc_K_groups_tl_rec := ldefokccontractgroupstlrec;
2382 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2383 EXCEPTION
2384 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2385 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2386 (
2387 l_api_name,
2388 G_PKG_NAME,
2389 'OKC_API.G_RET_STS_ERROR',
2390 x_msg_count,
2391 x_msg_data,
2392 '_PVT'
2393 );
2394 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2395 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2396 (
2397 l_api_name,
2398 G_PKG_NAME,
2399 'OKC_API.G_RET_STS_UNEXP_ERROR',
2400 x_msg_count,
2401 x_msg_data,
2402 '_PVT'
2403 );
2404 WHEN OTHERS THEN
2405 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2406 (
2407 l_api_name,
2408 G_PKG_NAME,
2409 'OTHERS',
2410 x_msg_count,
2411 x_msg_data,
2412 '_PVT'
2413 );
2414 END update_row;
2415 ------------------------------------------
2416 -- update_row for:OKC_K_GROUPS_V --
2417 ------------------------------------------
2418 PROCEDURE update_row(
2419 p_api_version IN NUMBER,
2420 p_init_msg_list IN VARCHAR2 ,
2421 x_return_status OUT NOCOPY VARCHAR2,
2422 x_msg_count OUT NOCOPY NUMBER,
2423 x_msg_data OUT NOCOPY VARCHAR2,
2424 p_cgpv_rec IN cgpv_rec_type,
2425 x_cgpv_rec OUT NOCOPY cgpv_rec_type) IS
2426
2427 l_api_version CONSTANT NUMBER := 1;
2428 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2429 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2430 l_cgpv_rec cgpv_rec_type := p_cgpv_rec;
2431 l_def_cgpv_rec cgpv_rec_type;
2432 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
2433 lx_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
2434 l_cgp_rec cgp_rec_type;
2435 lx_cgp_rec cgp_rec_type;
2436 -------------------------------
2437 -- FUNCTION fill_who_columns --
2438 -------------------------------
2439 FUNCTION fill_who_columns (
2440 p_cgpv_rec IN cgpv_rec_type
2441 ) RETURN cgpv_rec_type IS
2442 l_cgpv_rec cgpv_rec_type := p_cgpv_rec;
2443 BEGIN
2444 l_cgpv_rec.LAST_UPDATE_DATE := SYSDATE;
2445 l_cgpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2446 l_cgpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2447 RETURN(l_cgpv_rec);
2448 END fill_who_columns;
2449 ----------------------------------
2453 p_cgpv_rec IN cgpv_rec_type,
2450 -- FUNCTION populate_new_record --
2451 ----------------------------------
2452 FUNCTION populate_new_record (
2454 x_cgpv_rec OUT NOCOPY cgpv_rec_type
2455 ) RETURN VARCHAR2 IS
2456 l_cgpv_rec cgpv_rec_type;
2457 l_row_notfound BOOLEAN := TRUE;
2458 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2459 BEGIN
2460 x_cgpv_rec := p_cgpv_rec;
2461 -- Get current database values
2462 l_cgpv_rec := get_rec(p_cgpv_rec, l_row_notfound);
2463 IF (l_row_notfound) THEN
2464 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2465 END IF;
2466 IF (x_cgpv_rec.id = OKC_API.G_MISS_NUM)
2467 THEN
2468 x_cgpv_rec.id := l_cgpv_rec.id;
2469 END IF;
2470 IF (x_cgpv_rec.object_version_number = OKC_API.G_MISS_NUM)
2471 THEN
2472 x_cgpv_rec.object_version_number := l_cgpv_rec.object_version_number;
2473 END IF;
2474 IF (x_cgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2475 THEN
2476 x_cgpv_rec.sfwt_flag := l_cgpv_rec.sfwt_flag;
2477 END IF;
2478 IF (x_cgpv_rec.name = OKC_API.G_MISS_CHAR)
2479 THEN
2480 x_cgpv_rec.name := l_cgpv_rec.name;
2481 END IF;
2482 IF (x_cgpv_rec.public_yn = OKC_API.G_MISS_CHAR)
2483 THEN
2484 x_cgpv_rec.public_yn := l_cgpv_rec.public_yn;
2485 END IF;
2486 IF (x_cgpv_rec.short_description = OKC_API.G_MISS_CHAR)
2487 THEN
2488 x_cgpv_rec.short_description := l_cgpv_rec.short_description;
2489 END IF;
2490 IF (x_cgpv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2491 THEN
2492 x_cgpv_rec.attribute_category := l_cgpv_rec.attribute_category;
2493 END IF;
2494 IF (x_cgpv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2495 THEN
2496 x_cgpv_rec.attribute1 := l_cgpv_rec.attribute1;
2497 END IF;
2498 IF (x_cgpv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2499 THEN
2500 x_cgpv_rec.attribute2 := l_cgpv_rec.attribute2;
2501 END IF;
2502 IF (x_cgpv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2503 THEN
2504 x_cgpv_rec.attribute3 := l_cgpv_rec.attribute3;
2505 END IF;
2506 IF (x_cgpv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2507 THEN
2508 x_cgpv_rec.attribute4 := l_cgpv_rec.attribute4;
2509 END IF;
2510 IF (x_cgpv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2511 THEN
2512 x_cgpv_rec.attribute5 := l_cgpv_rec.attribute5;
2513 END IF;
2514 IF (x_cgpv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2515 THEN
2516 x_cgpv_rec.attribute6 := l_cgpv_rec.attribute6;
2517 END IF;
2518 IF (x_cgpv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2519 THEN
2520 x_cgpv_rec.attribute7 := l_cgpv_rec.attribute7;
2521 END IF;
2522 IF (x_cgpv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2523 THEN
2524 x_cgpv_rec.attribute8 := l_cgpv_rec.attribute8;
2525 END IF;
2526 IF (x_cgpv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2527 THEN
2528 x_cgpv_rec.attribute9 := l_cgpv_rec.attribute9;
2529 END IF;
2530 IF (x_cgpv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2531 THEN
2532 x_cgpv_rec.attribute10 := l_cgpv_rec.attribute10;
2533 END IF;
2534 IF (x_cgpv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2535 THEN
2536 x_cgpv_rec.attribute11 := l_cgpv_rec.attribute11;
2537 END IF;
2538 IF (x_cgpv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2539 THEN
2540 x_cgpv_rec.attribute12 := l_cgpv_rec.attribute12;
2541 END IF;
2542 IF (x_cgpv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2543 THEN
2544 x_cgpv_rec.attribute13 := l_cgpv_rec.attribute13;
2545 END IF;
2546 IF (x_cgpv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2547 THEN
2548 x_cgpv_rec.attribute14 := l_cgpv_rec.attribute14;
2549 END IF;
2550 IF (x_cgpv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2551 THEN
2552 x_cgpv_rec.attribute15 := l_cgpv_rec.attribute15;
2553 END IF;
2554 IF (x_cgpv_rec.created_by = OKC_API.G_MISS_NUM)
2555 THEN
2556 x_cgpv_rec.created_by := l_cgpv_rec.created_by;
2557 END IF;
2558 IF (x_cgpv_rec.creation_date = OKC_API.G_MISS_DATE)
2559 THEN
2560 x_cgpv_rec.creation_date := l_cgpv_rec.creation_date;
2561 END IF;
2562 IF (x_cgpv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2563 THEN
2564 x_cgpv_rec.last_updated_by := l_cgpv_rec.last_updated_by;
2565 END IF;
2566 IF (x_cgpv_rec.last_update_date = OKC_API.G_MISS_DATE)
2567 THEN
2568 x_cgpv_rec.last_update_date := l_cgpv_rec.last_update_date;
2569 END IF;
2570 IF (x_cgpv_rec.user_id = OKC_API.G_MISS_NUM)
2571 THEN
2572 x_cgpv_rec.user_id := l_cgpv_rec.user_id;
2573 END IF;
2574 IF (x_cgpv_rec.last_update_login = OKC_API.G_MISS_NUM)
2575 THEN
2576 x_cgpv_rec.last_update_login := l_cgpv_rec.last_update_login;
2577 END IF;
2578 RETURN(l_return_status);
2579 END populate_new_record;
2580 ----------------------------------------------
2581 -- Set_Attributes for:OKC_K_GROUPS_V --
2582 ----------------------------------------------
2583 FUNCTION Set_Attributes (
2587 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2584 p_cgpv_rec IN cgpv_rec_type,
2585 x_cgpv_rec OUT NOCOPY cgpv_rec_type
2586 ) RETURN VARCHAR2 IS
2588 BEGIN
2589 x_cgpv_rec := p_cgpv_rec;
2590 x_cgpv_rec.OBJECT_VERSION_NUMBER := NVL(x_cgpv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2591 RETURN(l_return_status);
2592 END Set_Attributes;
2593 BEGIN
2594 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2595 G_PKG_NAME,
2596 p_init_msg_list,
2597 l_api_version,
2598 p_api_version,
2599 '_PVT',
2600 x_return_status);
2601 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2602 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2603 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2604 RAISE OKC_API.G_EXCEPTION_ERROR;
2605 END IF;
2606 --- Setting item attributes
2607 l_return_status := Set_Attributes(
2608 p_cgpv_rec, -- IN
2609 l_cgpv_rec); -- OUT
2610 --- If any errors happen abort API
2611 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2612 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2613 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2614 RAISE OKC_API.G_EXCEPTION_ERROR;
2615 END IF;
2616 l_return_status := populate_new_record(l_cgpv_rec, l_def_cgpv_rec);
2617 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2618 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2619 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2620 RAISE OKC_API.G_EXCEPTION_ERROR;
2621 END IF;
2622 l_def_cgpv_rec := fill_who_columns(l_def_cgpv_rec);
2623 --- Validate all non-missing attributes (Item Level Validation)
2624 l_return_status := Validate_Attributes(l_def_cgpv_rec);
2625 --- If any errors happen abort API
2626 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2627 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2628 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2629 RAISE OKC_API.G_EXCEPTION_ERROR;
2630 END IF;
2631 --- Check user has got the privilege to update the public group
2632 Check_Pub_Group_Privilege(l_return_status, l_def_cgpv_rec);
2633 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2634 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2635 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2636 RAISE OKC_API.G_EXCEPTION_ERROR;
2637 END IF;
2638 --- Check user is not updating other user's group
2639 Check_User_Privilege(l_return_status, l_def_cgpv_rec);
2640 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2641 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2642 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2643 RAISE OKC_API.G_EXCEPTION_ERROR;
2644 END IF;
2645 l_return_status := Validate_Record(l_def_cgpv_rec);
2646 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2647 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2648 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2649 RAISE OKC_API.G_EXCEPTION_ERROR;
2650 END IF;
2651
2652 --------------------------------------
2653 -- Move VIEW record to "Child" records
2654 --------------------------------------
2655 migrate(l_def_cgpv_rec, l_okc_K_groups_tl_rec);
2656 migrate(l_def_cgpv_rec, l_cgp_rec);
2657 --------------------------------------------
2658 -- Call the UPDATE_ROW for each child record
2659 --------------------------------------------
2660 update_row(
2661 p_init_msg_list,
2662 x_return_status,
2663 x_msg_count,
2664 x_msg_data,
2665 l_okc_K_groups_tl_rec,
2666 lx_okc_K_groups_tl_rec
2667 );
2668 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2669 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2670 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2671 RAISE OKC_API.G_EXCEPTION_ERROR;
2672 END IF;
2673 migrate(lx_okc_K_groups_tl_rec, l_def_cgpv_rec);
2674 update_row(
2675 p_init_msg_list,
2676 x_return_status,
2677 x_msg_count,
2678 x_msg_data,
2679 l_cgp_rec,
2680 lx_cgp_rec
2681 );
2682 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2683 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2684 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2685 RAISE OKC_API.G_EXCEPTION_ERROR;
2686 END IF;
2687 migrate(lx_cgp_rec, l_def_cgpv_rec);
2688 x_cgpv_rec := l_def_cgpv_rec;
2689 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2690 EXCEPTION
2691 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2692 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2693 (
2694 l_api_name,
2695 G_PKG_NAME,
2696 'OKC_API.G_RET_STS_ERROR',
2697 x_msg_count,
2698 x_msg_data,
2699 '_PVT'
2700 );
2701 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2702 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2703 (
2704 l_api_name,
2705 G_PKG_NAME,
2706 'OKC_API.G_RET_STS_UNEXP_ERROR',
2707 x_msg_count,
2708 x_msg_data,
2709 '_PVT'
2713 (
2710 );
2711 WHEN OTHERS THEN
2712 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2714 l_api_name,
2715 G_PKG_NAME,
2716 'OTHERS',
2717 x_msg_count,
2718 x_msg_data,
2719 '_PVT'
2720 );
2721 END update_row;
2722 ----------------------------------------
2723 -- PL/SQL TBL update_row for:CGPV_TBL --
2724 ----------------------------------------
2725 PROCEDURE update_row(
2726 p_api_version IN NUMBER,
2727 p_init_msg_list IN VARCHAR2 ,
2728 x_return_status OUT NOCOPY VARCHAR2,
2729 x_msg_count OUT NOCOPY NUMBER,
2730 x_msg_data OUT NOCOPY VARCHAR2,
2731 p_cgpv_tbl IN cgpv_tbl_type,
2732 x_cgpv_tbl OUT NOCOPY cgpv_tbl_type) IS
2733
2734 l_api_version CONSTANT NUMBER := 1;
2735 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2736 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2737 i NUMBER := 0;
2738 BEGIN
2739 -- Make sure PL/SQL table has records in it before passing
2740 IF (p_cgpv_tbl.COUNT > 0) THEN
2741 i := p_cgpv_tbl.FIRST;
2742 LOOP
2743 update_row (
2744 p_api_version => p_api_version,
2745 p_init_msg_list => p_init_msg_list,
2746 x_return_status => x_return_status,
2747 x_msg_count => x_msg_count,
2748 x_msg_data => x_msg_data,
2749 p_cgpv_rec => p_cgpv_tbl(i),
2750 x_cgpv_rec => x_cgpv_tbl(i));
2751 EXIT WHEN (i = p_cgpv_tbl.LAST);
2752 i := p_cgpv_tbl.NEXT(i);
2753 END LOOP;
2754 END IF;
2755 EXCEPTION
2756 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2757 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2758 (
2759 l_api_name,
2760 G_PKG_NAME,
2761 'OKC_API.G_RET_STS_ERROR',
2762 x_msg_count,
2763 x_msg_data,
2764 '_PVT'
2765 );
2766 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2767 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2768 (
2769 l_api_name,
2770 G_PKG_NAME,
2771 'OKC_API.G_RET_STS_UNEXP_ERROR',
2772 x_msg_count,
2773 x_msg_data,
2774 '_PVT'
2775 );
2776 WHEN OTHERS THEN
2777 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2778 (
2779 l_api_name,
2780 G_PKG_NAME,
2781 'OTHERS',
2782 x_msg_count,
2783 x_msg_data,
2784 '_PVT'
2785 );
2786 END update_row;
2787
2788 ---------------------------------------------------------------------------
2789 -- PROCEDURE delete_row
2790 ---------------------------------------------------------------------------
2791 ------------------------------------------
2792 -- delete_row for:OKC_K_GROUPS_B --
2793 ------------------------------------------
2794 PROCEDURE delete_row(
2795 p_init_msg_list IN VARCHAR2 ,
2796 x_return_status OUT NOCOPY VARCHAR2,
2797 x_msg_count OUT NOCOPY NUMBER,
2798 x_msg_data OUT NOCOPY VARCHAR2,
2799 p_cgp_rec IN cgp_rec_type) IS
2800
2801 l_api_version CONSTANT NUMBER := 1;
2802 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2803 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2804 l_cgp_rec cgp_rec_type:= p_cgp_rec;
2805 l_row_notfound BOOLEAN := TRUE;
2806 BEGIN
2807 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2808 p_init_msg_list,
2809 '_PVT',
2810 x_return_status);
2811 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2812 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2813 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2814 RAISE OKC_API.G_EXCEPTION_ERROR;
2815 END IF;
2816 DELETE FROM OKC_K_GROUPS_B
2817 WHERE ID = l_cgp_rec.id;
2818
2819 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2820 EXCEPTION
2821 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2822 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2823 (
2824 l_api_name,
2825 G_PKG_NAME,
2826 'OKC_API.G_RET_STS_ERROR',
2827 x_msg_count,
2828 x_msg_data,
2829 '_PVT'
2830 );
2831 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2832 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2833 (
2834 l_api_name,
2835 G_PKG_NAME,
2836 'OKC_API.G_RET_STS_UNEXP_ERROR',
2837 x_msg_count,
2838 x_msg_data,
2839 '_PVT'
2840 );
2841 WHEN OTHERS THEN
2842 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2843 (
2844 l_api_name,
2845 G_PKG_NAME,
2846 'OTHERS',
2847 x_msg_count,
2848 x_msg_data,
2849 '_PVT'
2850 );
2851 END delete_row;
2855 PROCEDURE delete_row(
2852 -------------------------------------------
2853 -- delete_row for:OKC_K_GROUPS_TL --
2854 -------------------------------------------
2856 p_init_msg_list IN VARCHAR2 ,
2857 x_return_status OUT NOCOPY VARCHAR2,
2858 x_msg_count OUT NOCOPY NUMBER,
2859 x_msg_data OUT NOCOPY VARCHAR2,
2860 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType) IS
2861
2862 l_api_version CONSTANT NUMBER := 1;
2863 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2864 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2865 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType:= p_okc_K_groups_tl_rec;
2866 l_row_notfound BOOLEAN := TRUE;
2867 -----------------------------------------------
2868 -- Set_Attributes for:OKC_K_GROUPS_TL --
2869 -----------------------------------------------
2870 FUNCTION Set_Attributes (
2871 p_okc_K_groups_tl_rec IN OkcContractGroupsTlRecType,
2872 x_okc_K_groups_tl_rec OUT NOCOPY OkcContractGroupsTlRecType
2873 ) RETURN VARCHAR2 IS
2874 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2875 BEGIN
2876 x_okc_K_groups_tl_rec := p_okc_K_groups_tl_rec;
2877 x_okc_K_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2878 RETURN(l_return_status);
2879 END Set_Attributes;
2880 BEGIN
2881 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2882 p_init_msg_list,
2883 '_PVT',
2884 x_return_status);
2885 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2886 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2887 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2888 RAISE OKC_API.G_EXCEPTION_ERROR;
2889 END IF;
2890 --- Setting item attributes
2891 l_return_status := Set_Attributes(
2892 p_okc_K_groups_tl_rec, -- IN
2893 l_okc_K_groups_tl_rec); -- OUT
2894 --- If any errors happen abort API
2895 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2896 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2897 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2898 RAISE OKC_API.G_EXCEPTION_ERROR;
2899 END IF;
2900 DELETE FROM OKC_K_GROUPS_TL
2901 WHERE ID = l_okc_K_groups_tl_rec.id;
2902
2903 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2904 EXCEPTION
2905 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2906 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2907 (
2908 l_api_name,
2909 G_PKG_NAME,
2910 'OKC_API.G_RET_STS_ERROR',
2911 x_msg_count,
2912 x_msg_data,
2913 '_PVT'
2914 );
2915 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2916 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2917 (
2918 l_api_name,
2919 G_PKG_NAME,
2920 'OKC_API.G_RET_STS_UNEXP_ERROR',
2921 x_msg_count,
2922 x_msg_data,
2923 '_PVT'
2924 );
2925 WHEN OTHERS THEN
2926 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2927 (
2928 l_api_name,
2929 G_PKG_NAME,
2930 'OTHERS',
2931 x_msg_count,
2932 x_msg_data,
2933 '_PVT'
2934 );
2935 END delete_row;
2936 ------------------------------------------
2937 -- delete_row for:OKC_K_GROUPS_V --
2938 ------------------------------------------
2939 PROCEDURE delete_row(
2940 p_api_version IN NUMBER,
2941 p_init_msg_list IN VARCHAR2 ,
2942 x_return_status OUT NOCOPY VARCHAR2,
2943 x_msg_count OUT NOCOPY NUMBER,
2944 x_msg_data OUT NOCOPY VARCHAR2,
2945 p_cgpv_rec IN cgpv_rec_type) IS
2946
2947 l_api_version CONSTANT NUMBER := 1;
2948 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2949 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2950 l_cgpv_rec cgpv_rec_type := p_cgpv_rec;
2951 l_okc_K_groups_tl_rec OkcContractGroupsTlRecType;
2952 l_cgp_rec cgp_rec_type;
2953 BEGIN
2954 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2955 G_PKG_NAME,
2956 p_init_msg_list,
2957 l_api_version,
2958 p_api_version,
2959 '_PVT',
2960 x_return_status);
2961 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2962 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2963 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2964 RAISE OKC_API.G_EXCEPTION_ERROR;
2965 END IF;
2966 --- Check user has got the privilege to delete the public group
2967 Check_Pub_Group_Privilege(l_return_status, l_cgpv_rec);
2968 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2969 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2970 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2974 Check_User_Privilege(l_return_status, l_cgpv_rec);
2971 RAISE OKC_API.G_EXCEPTION_ERROR;
2972 END IF;
2973 --- Check user is not deleting other user's group
2975 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2976 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2977 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2978 RAISE OKC_API.G_EXCEPTION_ERROR;
2979 END IF;
2980 --------------------------------------
2981 -- Move VIEW record to "Child" records
2982 --------------------------------------
2983 migrate(l_cgpv_rec, l_okc_K_groups_tl_rec);
2984 migrate(l_cgpv_rec, l_cgp_rec);
2985 --------------------------------------------
2986 -- Call the DELETE_ROW for each child record
2987 --------------------------------------------
2988 delete_row(
2989 p_init_msg_list,
2990 x_return_status,
2991 x_msg_count,
2992 x_msg_data,
2993 l_okc_K_groups_tl_rec
2994 );
2995 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2996 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2997 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2998 RAISE OKC_API.G_EXCEPTION_ERROR;
2999 END IF;
3000 delete_row(
3001 p_init_msg_list,
3002 x_return_status,
3003 x_msg_count,
3004 x_msg_data,
3005 l_cgp_rec
3006 );
3007 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3008 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3009 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3010 RAISE OKC_API.G_EXCEPTION_ERROR;
3011 END IF;
3012 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3013 EXCEPTION
3014 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3015 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3016 (
3017 l_api_name,
3018 G_PKG_NAME,
3019 'OKC_API.G_RET_STS_ERROR',
3020 x_msg_count,
3021 x_msg_data,
3022 '_PVT'
3023 );
3024 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3025 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3026 (
3027 l_api_name,
3028 G_PKG_NAME,
3029 'OKC_API.G_RET_STS_UNEXP_ERROR',
3030 x_msg_count,
3031 x_msg_data,
3032 '_PVT'
3033 );
3034 WHEN OTHERS THEN
3035 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3036 (
3037 l_api_name,
3038 G_PKG_NAME,
3039 'OTHERS',
3040 x_msg_count,
3041 x_msg_data,
3042 '_PVT'
3043 );
3044 END delete_row;
3045 ----------------------------------------
3046 -- PL/SQL TBL delete_row for:CGPV_TBL --
3047 ----------------------------------------
3048 PROCEDURE delete_row(
3049 p_api_version IN NUMBER,
3050 p_init_msg_list IN VARCHAR2 ,
3051 x_return_status OUT NOCOPY VARCHAR2,
3052 x_msg_count OUT NOCOPY NUMBER,
3053 x_msg_data OUT NOCOPY VARCHAR2,
3054 p_cgpv_tbl IN cgpv_tbl_type) IS
3055
3056 l_api_version CONSTANT NUMBER := 1;
3057 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3058 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3059 i NUMBER := 0;
3060 BEGIN
3061 -- Make sure PL/SQL table has records in it before passing
3062 IF (p_cgpv_tbl.COUNT > 0) THEN
3063 i := p_cgpv_tbl.FIRST;
3064 LOOP
3065 delete_row (
3066 p_api_version => p_api_version,
3067 p_init_msg_list => p_init_msg_list,
3068 x_return_status => x_return_status,
3069 x_msg_count => x_msg_count,
3070 x_msg_data => x_msg_data,
3071 p_cgpv_rec => p_cgpv_tbl(i));
3072 EXIT WHEN (i = p_cgpv_tbl.LAST);
3073 i := p_cgpv_tbl.NEXT(i);
3074 END LOOP;
3075 END IF;
3076 EXCEPTION
3077 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3078 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3079 (
3080 l_api_name,
3081 G_PKG_NAME,
3082 'OKC_API.G_RET_STS_ERROR',
3083 x_msg_count,
3084 x_msg_data,
3085 '_PVT'
3086 );
3087 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3088 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3089 (
3090 l_api_name,
3091 G_PKG_NAME,
3092 'OKC_API.G_RET_STS_UNEXP_ERROR',
3093 x_msg_count,
3094 x_msg_data,
3095 '_PVT'
3096 );
3097 WHEN OTHERS THEN
3098 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3099 (
3100 l_api_name,
3101 G_PKG_NAME,
3102 'OTHERS',
3103 x_msg_count,
3104 x_msg_data,
3105 '_PVT'
3106 );
3107 END delete_row;
3108
3109 PROCEDURE Build_Groups_Tbl(x_id_tbl OUT NOCOPY id_tbl,
3110 x_public_yn_tbl OUT NOCOPY public_yn_tbl,
3111 x_name_tbl OUT NOCOPY name_tbl,
3112 x_return_status OUT NOCOPY Varchar2) IS
3116 FROM OKC_K_GROUPS_V
3113 l_user_id Number := TO_NUMBER(fnd_profile.value('USER_ID'));
3114 CURSOR c1 IS
3115 SELECT id, public_yn, name
3117 WHERE ((public_yn = 'Y')
3118 OR (public_yn = 'N'
3119 AND user_id = l_user_id))
3120 ORDER BY public_yn, name;
3121 BEGIN
3122 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3123 Open c1;
3124 Fetch c1 Bulk Collect Into
3125 x_id_tbl, x_public_yn_tbl, x_name_tbl;
3126 Close c1;
3127 EXCEPTION
3128 When OTHERS Then
3129 OKC_API.set_message(p_app_name => g_app_name,
3130 p_msg_name => g_unexpected_error,
3131 p_token1 => g_sqlcode_token,
3132 p_token1_value => sqlcode,
3133 p_token2 => g_sqlerrm_token,
3134 p_token2_value => sqlerrm);
3135 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3136
3137 END Build_Groups_Tbl;
3138
3139 PROCEDURE Populate_Groups_Temp_Tbl(p_cgp_parent_id_tbl IN id_tbl,
3140 p_included_cgp_id_tbl IN id_tbl,
3141 p_included_public_yn_tbl IN public_yn_tbl,
3142 p_included_name_tbl IN name_tbl,
3143 x_return_status OUT NOCOPY Varchar2) IS
3144 l_cgp_parent_id_tbl id_tbl := p_cgp_parent_id_tbl;
3145 l_included_cgp_id_tbl id_tbl := p_included_cgp_id_tbl;
3146 l_included_public_yn_tbl public_yn_tbl := p_included_public_yn_tbl;
3147 l_included_name_tbl name_tbl := p_included_name_tbl;
3148 BEGIN
3149 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3150 If l_cgp_parent_id_tbl.COUNT > 0 Then
3151 FORALL i IN l_cgp_parent_id_tbl.FIRST .. l_cgp_parent_id_tbl.LAST
3152 -- FOR i IN l_cgp_parent_id_tbl.FIRST .. l_cgp_parent_id_tbl.LAST
3153 -- LOOP
3154 insert into okc_grpings_temp
3155 (cgp_parent_id,
3156 included_cgp_id,
3157 included_cgp_public_yn,
3158 included_cgp_name)
3159 values (l_cgp_parent_id_tbl(i),
3160 l_included_cgp_id_tbl(i),
3161 l_included_public_yn_tbl(i),
3162 l_included_name_tbl(i));
3163 -- END LOOP;
3164 End If;
3165 l_cgp_parent_id_tbl.delete;
3166 l_included_cgp_id_tbl.delete;
3167 l_included_public_yn_tbl.delete;
3168 l_included_name_tbl.delete;
3169 EXCEPTION
3170 When OTHERS Then
3171 OKC_API.set_message(p_app_name => g_app_name,
3172 p_msg_name => g_unexpected_error,
3173 p_token1 => g_sqlcode_token,
3174 p_token1_value => sqlcode,
3175 p_token2 => g_sqlerrm_token,
3176 p_token2_value => sqlerrm);
3177 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3178 END Populate_Groups_Temp_Tbl;
3179 END OKC_CGP_PVT;