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