[Home] [Help]
PACKAGE BODY: APPS.OKC_CLS_PVT
Source
1 PACKAGE BODY OKC_CLS_PVT AS
2 /* $Header: OKCSCLSB.pls 120.0 2005/05/25 22:34:51 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- FUNCTION get_seq_id
7 ---------------------------------------------------------------------------
8 FUNCTION get_seq_id RETURN NUMBER IS
9 BEGIN
10 RETURN(okc_p_util.raw_to_number(sys_guid()));
11 END get_seq_id;
12
13 ---------------------------------------------------------------------------
14 -- PROCEDURE qc
15 ---------------------------------------------------------------------------
16 PROCEDURE qc IS
17 BEGIN
18 null;
19 END qc;
20
21 ---------------------------------------------------------------------------
22 -- PROCEDURE change_version
23 ---------------------------------------------------------------------------
24 PROCEDURE change_version IS
25 BEGIN
26 null;
27 END change_version;
28
29 ---------------------------------------------------------------------------
30 -- PROCEDURE api_copy
31 ---------------------------------------------------------------------------
32 PROCEDURE api_copy IS
33 BEGIN
34 null;
35 END api_copy;
36
37 ---------------------------------------------------------------------------
38 -- PROCEDURE add_language
39 ---------------------------------------------------------------------------
40 PROCEDURE add_language IS
41 BEGIN
42 DELETE FROM OKC_CLASSES_TL T
43 WHERE NOT EXISTS (
44 SELECT NULL
45 FROM OKC_CLASSES_B B
46 WHERE B.CODE = T.CODE
47 );
48
49 UPDATE OKC_CLASSES_TL T SET (
50 MEANING,
51 DESCRIPTION) = (SELECT
52 B.MEANING,
53 B.DESCRIPTION
54 FROM OKC_CLASSES_TL B
55 WHERE B.CODE = T.CODE
56 AND B.LANGUAGE = T.SOURCE_LANG)
57 WHERE (
58 T.CODE,
59 T.LANGUAGE)
60 IN (SELECT
61 SUBT.CODE,
62 SUBT.LANGUAGE
63 FROM OKC_CLASSES_TL SUBB, OKC_CLASSES_TL SUBT
64 WHERE SUBB.CODE = SUBT.CODE
65 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
66 AND (SUBB.MEANING <> SUBT.MEANING
67 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
68 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
69 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
70 ));
71
72 INSERT INTO OKC_CLASSES_TL (
73 CODE,
74 LANGUAGE,
75 SOURCE_LANG,
76 SFWT_FLAG,
77 MEANING,
78 DESCRIPTION,
79 CREATED_BY,
80 CREATION_DATE,
81 LAST_UPDATED_BY,
82 LAST_UPDATE_DATE,
83 LAST_UPDATE_LOGIN)
84 SELECT
85 B.CODE,
86 L.LANGUAGE_CODE,
87 B.SOURCE_LANG,
88 B.SFWT_FLAG,
89 B.MEANING,
90 B.DESCRIPTION,
91 B.CREATED_BY,
92 B.CREATION_DATE,
93 B.LAST_UPDATED_BY,
94 B.LAST_UPDATE_DATE,
95 B.LAST_UPDATE_LOGIN
96 FROM OKC_CLASSES_TL B, FND_LANGUAGES L
97 WHERE L.INSTALLED_FLAG IN ('I', 'B')
98 AND B.LANGUAGE = USERENV('LANG')
99 AND NOT EXISTS(
100 SELECT NULL
101 FROM OKC_CLASSES_TL T
102 WHERE T.CODE = B.CODE
103 AND T.LANGUAGE = L.LANGUAGE_CODE
104 );
105
106 END add_language;
107
108 ---------------------------------------------------------------------------
109 -- FUNCTION get_rec for: OKC_CLASSES_B
110 ---------------------------------------------------------------------------
111 FUNCTION get_rec (
112 p_cls_rec IN cls_rec_type,
113 x_no_data_found OUT NOCOPY BOOLEAN
114 ) RETURN cls_rec_type IS
115 CURSOR okc_classes_b_pk_csr (p_code IN VARCHAR2) IS
116 SELECT
117 CODE,
118 FFF_FUNCTION_ID,
119 OBJECT_VERSION_NUMBER,
120 CREATED_BY,
121 CREATION_DATE,
122 LAST_UPDATED_BY,
123 LAST_UPDATE_DATE,
124 LAST_UPDATE_LOGIN,
125 APPLICATION_ID,
126 ADV_SEARCH_FORM_FUNCTION_ID
127 FROM Okc_Classes_B
128 WHERE okc_classes_b.code = p_code;
129 l_okc_classes_b_pk okc_classes_b_pk_csr%ROWTYPE;
130 l_cls_rec cls_rec_type;
131 BEGIN
132 x_no_data_found := TRUE;
133 -- Get current database values
134 OPEN okc_classes_b_pk_csr (p_cls_rec.code);
135 FETCH okc_classes_b_pk_csr INTO
136 l_cls_rec.CODE,
137 l_cls_rec.FFF_FUNCTION_ID,
138 l_cls_rec.OBJECT_VERSION_NUMBER,
139 l_cls_rec.CREATED_BY,
140 l_cls_rec.CREATION_DATE,
141 l_cls_rec.LAST_UPDATED_BY,
142 l_cls_rec.LAST_UPDATE_DATE,
143 l_cls_rec.LAST_UPDATE_LOGIN,
144 l_cls_rec.APPLICATION_ID,
145 l_cls_rec.ADV_SEARCH_FORM_FUNCTION_ID;
146 x_no_data_found := okc_classes_b_pk_csr%NOTFOUND;
147 CLOSE okc_classes_b_pk_csr;
148 RETURN(l_cls_rec);
149 END get_rec;
150
151 FUNCTION get_rec (
152 p_cls_rec IN cls_rec_type
153 ) RETURN cls_rec_type IS
154 l_row_notfound BOOLEAN := TRUE;
155 BEGIN
156 RETURN(get_rec(p_cls_rec, l_row_notfound));
157 END get_rec;
158 ---------------------------------------------------------------------------
159 -- FUNCTION get_rec for: OKC_CLASSES_TL
160 ---------------------------------------------------------------------------
161 FUNCTION get_rec (
162 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
163 x_no_data_found OUT NOCOPY BOOLEAN
164 ) RETURN okc_classes_tl_rec_type IS
165 CURSOR okc_classes_tl_pk_csr (p_code IN VARCHAR2,
166 p_language IN VARCHAR2) IS
167 SELECT
168 CODE,
169 LANGUAGE,
170 SOURCE_LANG,
171 SFWT_FLAG,
172 MEANING,
173 DESCRIPTION,
174 CREATED_BY,
175 CREATION_DATE,
176 LAST_UPDATED_BY,
177 LAST_UPDATE_DATE,
178 LAST_UPDATE_LOGIN
179 FROM Okc_Classes_Tl
180 WHERE okc_classes_tl.code = p_code
181 AND okc_classes_tl.language = p_language;
182 l_okc_classes_tl_pk okc_classes_tl_pk_csr%ROWTYPE;
183 l_okc_classes_tl_rec okc_classes_tl_rec_type;
184 BEGIN
185 x_no_data_found := TRUE;
186 -- Get current database values
187 OPEN okc_classes_tl_pk_csr (p_okc_classes_tl_rec.code,
188 p_okc_classes_tl_rec.language);
189 FETCH okc_classes_tl_pk_csr INTO
190 l_okc_classes_tl_rec.CODE,
191 l_okc_classes_tl_rec.LANGUAGE,
192 l_okc_classes_tl_rec.SOURCE_LANG,
193 l_okc_classes_tl_rec.SFWT_FLAG,
194 l_okc_classes_tl_rec.MEANING,
195 l_okc_classes_tl_rec.DESCRIPTION,
196 l_okc_classes_tl_rec.CREATED_BY,
197 l_okc_classes_tl_rec.CREATION_DATE,
198 l_okc_classes_tl_rec.LAST_UPDATED_BY,
199 l_okc_classes_tl_rec.LAST_UPDATE_DATE,
200 l_okc_classes_tl_rec.LAST_UPDATE_LOGIN;
201 x_no_data_found := okc_classes_tl_pk_csr%NOTFOUND;
202 CLOSE okc_classes_tl_pk_csr;
203 RETURN(l_okc_classes_tl_rec);
204 END get_rec;
205
206 FUNCTION get_rec (
207 p_okc_classes_tl_rec IN okc_classes_tl_rec_type
208 ) RETURN okc_classes_tl_rec_type IS
209 l_row_notfound BOOLEAN := TRUE;
210 BEGIN
211 RETURN(get_rec(p_okc_classes_tl_rec, l_row_notfound));
212 END get_rec;
213 ---------------------------------------------------------------------------
214 -- FUNCTION get_rec for: OKC_CLASSES_V
215 ---------------------------------------------------------------------------
216 FUNCTION get_rec (
217 p_clsv_rec IN clsv_rec_type,
218 x_no_data_found OUT NOCOPY BOOLEAN
219 ) RETURN clsv_rec_type IS
220 CURSOR okc_clsv_pk_csr (p_code IN VARCHAR2) IS
221 SELECT
222 CODE,
223 OBJECT_VERSION_NUMBER,
224 SFWT_FLAG,
225 FFF_FUNCTION_ID,
226 MEANING,
227 DESCRIPTION,
228 CREATED_BY,
229 CREATION_DATE,
230 LAST_UPDATED_BY,
231 LAST_UPDATE_DATE,
232 LAST_UPDATE_LOGIN,
233 APPLICATION_ID,
234 ADV_SEARCH_FORM_FUNCTION_ID
235 FROM Okc_Classes_V
236 WHERE okc_classes_v.code = p_code;
237 l_okc_clsv_pk okc_clsv_pk_csr%ROWTYPE;
238 l_clsv_rec clsv_rec_type;
239 BEGIN
240 x_no_data_found := TRUE;
241 -- Get current database values
242 OPEN okc_clsv_pk_csr (p_clsv_rec.code);
243 FETCH okc_clsv_pk_csr INTO
244 l_clsv_rec.CODE,
245 l_clsv_rec.OBJECT_VERSION_NUMBER,
246 l_clsv_rec.SFWT_FLAG,
247 l_clsv_rec.FFF_FUNCTION_ID,
248 l_clsv_rec.MEANING,
249 l_clsv_rec.DESCRIPTION,
250 l_clsv_rec.CREATED_BY,
251 l_clsv_rec.CREATION_DATE,
252 l_clsv_rec.LAST_UPDATED_BY,
253 l_clsv_rec.LAST_UPDATE_DATE,
254 l_clsv_rec.LAST_UPDATE_LOGIN,
255 l_clsv_rec.APPLICATION_ID,
256 l_clsv_rec.ADV_SEARCH_FORM_FUNCTION_ID;
257 x_no_data_found := okc_clsv_pk_csr%NOTFOUND;
258 CLOSE okc_clsv_pk_csr;
259 RETURN(l_clsv_rec);
260 END get_rec;
261
262 FUNCTION get_rec (
263 p_clsv_rec IN clsv_rec_type
264 ) RETURN clsv_rec_type IS
265 l_row_notfound BOOLEAN := TRUE;
266 BEGIN
267 RETURN(get_rec(p_clsv_rec, l_row_notfound));
268 END get_rec;
269
270 ---------------------------------------------------
271 -- FUNCTION null_out_defaults for: OKC_CLASSES_V --
272 ---------------------------------------------------
273 FUNCTION null_out_defaults (
274 p_clsv_rec IN clsv_rec_type
275 ) RETURN clsv_rec_type IS
276 l_clsv_rec clsv_rec_type := p_clsv_rec;
277 BEGIN
278 IF (l_clsv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
279 l_clsv_rec.object_version_number := NULL;
280 END IF;
281 IF (l_clsv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
282 l_clsv_rec.sfwt_flag := NULL;
283 END IF;
284 IF (l_clsv_rec.fff_function_id = OKC_API.G_MISS_NUM) THEN
285 l_clsv_rec.fff_function_id := NULL;
286 END IF;
287 IF (l_clsv_rec.meaning = OKC_API.G_MISS_CHAR) THEN
288 l_clsv_rec.meaning := NULL;
289 END IF;
290 IF (l_clsv_rec.description = OKC_API.G_MISS_CHAR) THEN
291 l_clsv_rec.description := NULL;
292 END IF;
293 IF (l_clsv_rec.created_by = OKC_API.G_MISS_NUM) THEN
294 l_clsv_rec.created_by := NULL;
295 END IF;
296 IF (l_clsv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
297 l_clsv_rec.creation_date := NULL;
298 END IF;
299 IF (l_clsv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
300 l_clsv_rec.last_updated_by := NULL;
301 END IF;
302 IF (l_clsv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
303 l_clsv_rec.last_update_date := NULL;
304 END IF;
305 IF (l_clsv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
306 l_clsv_rec.last_update_login := NULL;
307 END IF;
308 IF (l_clsv_rec.application_id = OKC_API.G_MISS_NUM) THEN
309 l_clsv_rec.application_id := NULL;
310 END IF;
311 IF (l_clsv_rec.adv_search_form_function_id = OKC_API.G_MISS_NUM) THEN
312 l_clsv_rec.adv_search_form_function_id := NULL;
313 END IF;
314 RETURN(l_clsv_rec);
315 END null_out_defaults;
316 ---------------------------------------------------------------------------
317 -- PROCEDURE Validate_Attributes
318 ---------------------------------------------------------------------------
319 -------------------------------------------
320 -- Validate_Attributes for:OKC_CLASSES_V --
321 -------------------------------------------
322 FUNCTION Validate_Attributes (
323 p_clsv_rec IN clsv_rec_type
324 ) RETURN VARCHAR2 IS
325 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
326 BEGIN
327 IF p_clsv_rec.code = OKC_API.G_MISS_CHAR OR
328 p_clsv_rec.code IS NULL
329 THEN
330 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'code');
331 l_return_status := OKC_API.G_RET_STS_ERROR;
332 ELSIF p_clsv_rec.object_version_number = OKC_API.G_MISS_NUM OR
333 p_clsv_rec.object_version_number IS NULL
334 THEN
335 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
336 l_return_status := OKC_API.G_RET_STS_ERROR;
337 ELSIF p_clsv_rec.meaning = OKC_API.G_MISS_CHAR OR
338 p_clsv_rec.meaning IS NULL
339 THEN
340 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'meaning');
341 l_return_status := OKC_API.G_RET_STS_ERROR;
342 END IF;
343 RETURN(l_return_status);
344 END Validate_Attributes;
345
346 ---------------------------------------------------------------------------
347 -- PROCEDURE Validate_Record
348 ---------------------------------------------------------------------------
349 ---------------------------------------
350 -- Validate_Record for:OKC_CLASSES_V --
351 ---------------------------------------
352 FUNCTION Validate_Record (
353 p_clsv_rec IN clsv_rec_type
354 ) RETURN VARCHAR2 IS
355 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
356 BEGIN
357 RETURN (l_return_status);
358 END Validate_Record;
359
360 ---------------------------------------------------------------------------
361 -- PROCEDURE Migrate
362 ---------------------------------------------------------------------------
363 PROCEDURE migrate (
364 p_from IN clsv_rec_type,
365 p_to OUT NOCOPY cls_rec_type
366 ) IS
367 BEGIN
368 p_to.code := p_from.code;
369 p_to.fff_function_id := p_from.fff_function_id;
370 p_to.object_version_number := p_from.object_version_number;
371 p_to.created_by := p_from.created_by;
372 p_to.creation_date := p_from.creation_date;
373 p_to.last_updated_by := p_from.last_updated_by;
374 p_to.last_update_date := p_from.last_update_date;
375 p_to.last_update_login := p_from.last_update_login;
376 p_to.application_id := p_from.application_id;
377 p_to.adv_search_form_function_id := p_from.adv_search_form_function_id;
378 END migrate;
379 PROCEDURE migrate (
380 p_from IN cls_rec_type,
381 p_to IN OUT NOCOPY clsv_rec_type
382 ) IS
383 BEGIN
384 p_to.code := p_from.code;
385 p_to.fff_function_id := p_from.fff_function_id;
386 p_to.object_version_number := p_from.object_version_number;
387 p_to.created_by := p_from.created_by;
388 p_to.creation_date := p_from.creation_date;
389 p_to.last_updated_by := p_from.last_updated_by;
390 p_to.last_update_date := p_from.last_update_date;
391 p_to.last_update_login := p_from.last_update_login;
392 p_to.application_id := p_from.application_id;
396 p_from IN clsv_rec_type,
393 p_to.adv_search_form_function_id := p_from.adv_search_form_function_id;
394 END migrate;
395 PROCEDURE migrate (
397 p_to OUT NOCOPY okc_classes_tl_rec_type
398 ) IS
399 BEGIN
400 p_to.code := p_from.code;
401 p_to.sfwt_flag := p_from.sfwt_flag;
402 p_to.meaning := p_from.meaning;
403 p_to.description := p_from.description;
404 p_to.created_by := p_from.created_by;
405 p_to.creation_date := p_from.creation_date;
406 p_to.last_updated_by := p_from.last_updated_by;
407 p_to.last_update_date := p_from.last_update_date;
408 p_to.last_update_login := p_from.last_update_login;
409 END migrate;
410 PROCEDURE migrate (
411 p_from IN okc_classes_tl_rec_type,
412 p_to IN OUT NOCOPY clsv_rec_type
413 ) IS
414 BEGIN
415 p_to.code := p_from.code;
416 p_to.sfwt_flag := p_from.sfwt_flag;
417 p_to.meaning := p_from.meaning;
418 p_to.description := p_from.description;
419 p_to.created_by := p_from.created_by;
420 p_to.creation_date := p_from.creation_date;
421 p_to.last_updated_by := p_from.last_updated_by;
422 p_to.last_update_date := p_from.last_update_date;
423 p_to.last_update_login := p_from.last_update_login;
424 END migrate;
425
426 ---------------------------------------------------------------------------
427 -- PROCEDURE validate_row
428 ---------------------------------------------------------------------------
429 ------------------------------------
430 -- validate_row for:OKC_CLASSES_V --
431 ------------------------------------
432 PROCEDURE validate_row(
433 p_api_version IN NUMBER,
434 p_init_msg_list IN VARCHAR2 ,
435 x_return_status OUT NOCOPY VARCHAR2,
436 x_msg_count OUT NOCOPY NUMBER,
437 x_msg_data OUT NOCOPY VARCHAR2,
438 p_clsv_rec IN clsv_rec_type) IS
439
440 l_api_version CONSTANT NUMBER := 1;
441 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
442 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
443 l_clsv_rec clsv_rec_type := p_clsv_rec;
444 l_cls_rec cls_rec_type;
445 l_okc_classes_tl_rec okc_classes_tl_rec_type;
446 BEGIN
447 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
448 G_PKG_NAME,
449 p_init_msg_list,
450 l_api_version,
451 p_api_version,
452 '_PVT',
453 x_return_status);
454 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
455 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
456 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
457 RAISE OKC_API.G_EXCEPTION_ERROR;
458 END IF;
459 --- Validate all non-missing attributes (Item Level Validation)
460 l_return_status := Validate_Attributes(l_clsv_rec);
461 --- If any errors happen abort API
462 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
463 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
464 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
465 RAISE OKC_API.G_EXCEPTION_ERROR;
466 END IF;
467 l_return_status := Validate_Record(l_clsv_rec);
468 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
469 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
470 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
471 RAISE OKC_API.G_EXCEPTION_ERROR;
472 END IF;
473 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
474 EXCEPTION
475 WHEN OKC_API.G_EXCEPTION_ERROR THEN
476 x_return_status := OKC_API.HANDLE_EXCEPTIONS
477 (
478 l_api_name,
479 G_PKG_NAME,
480 'OKC_API.G_RET_STS_ERROR',
481 x_msg_count,
482 x_msg_data,
483 '_PVT'
484 );
485 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
486 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
487 (
488 l_api_name,
489 G_PKG_NAME,
490 'OKC_API.G_RET_STS_UNEXP_ERROR',
491 x_msg_count,
492 x_msg_data,
493 '_PVT'
494 );
495 WHEN OTHERS THEN
496 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
497 (
498 l_api_name,
499 G_PKG_NAME,
500 'OTHERS',
501 x_msg_count,
502 x_msg_data,
503 '_PVT'
504 );
505 END validate_row;
506 ------------------------------------------
507 -- PL/SQL TBL validate_row for:CLSV_TBL --
508 ------------------------------------------
509 PROCEDURE validate_row(
510 p_api_version IN NUMBER,
511 p_init_msg_list IN VARCHAR2 ,
512 x_return_status OUT NOCOPY VARCHAR2,
513 x_msg_count OUT NOCOPY NUMBER,
514 x_msg_data OUT NOCOPY VARCHAR2,
515 p_clsv_tbl IN clsv_tbl_type) IS
516
517 l_api_version CONSTANT NUMBER := 1;
518 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
522 OKC_API.init_msg_list(p_init_msg_list);
519 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
520 i NUMBER := 0;
521 BEGIN
523 -- Make sure PL/SQL table has records in it before passing
524 IF (p_clsv_tbl.COUNT > 0) THEN
525 i := p_clsv_tbl.FIRST;
526 LOOP
527 validate_row (
528 p_api_version => p_api_version,
529 p_init_msg_list => OKC_API.G_FALSE,
530 x_return_status => x_return_status,
531 x_msg_count => x_msg_count,
532 x_msg_data => x_msg_data,
533 p_clsv_rec => p_clsv_tbl(i));
534 EXIT WHEN (i = p_clsv_tbl.LAST);
535 i := p_clsv_tbl.NEXT(i);
536 END LOOP;
537 END IF;
538 EXCEPTION
539 WHEN OKC_API.G_EXCEPTION_ERROR THEN
540 x_return_status := OKC_API.HANDLE_EXCEPTIONS
541 (
542 l_api_name,
543 G_PKG_NAME,
544 'OKC_API.G_RET_STS_ERROR',
545 x_msg_count,
546 x_msg_data,
547 '_PVT'
548 );
549 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
550 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
551 (
552 l_api_name,
553 G_PKG_NAME,
554 'OKC_API.G_RET_STS_UNEXP_ERROR',
555 x_msg_count,
556 x_msg_data,
557 '_PVT'
558 );
559 WHEN OTHERS THEN
560 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
561 (
562 l_api_name,
563 G_PKG_NAME,
564 'OTHERS',
565 x_msg_count,
566 x_msg_data,
567 '_PVT'
568 );
569 END validate_row;
570
571 ---------------------------------------------------------------------------
572 -- PROCEDURE insert_row
573 ---------------------------------------------------------------------------
574 ----------------------------------
575 -- insert_row for:OKC_CLASSES_B --
576 ----------------------------------
577 PROCEDURE insert_row(
578 p_init_msg_list IN VARCHAR2 ,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_count OUT NOCOPY NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2,
582 p_cls_rec IN cls_rec_type,
583 x_cls_rec OUT NOCOPY cls_rec_type) IS
584
585 l_api_version CONSTANT NUMBER := 1;
586 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
587 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
588 l_cls_rec cls_rec_type := p_cls_rec;
589 l_def_cls_rec cls_rec_type;
590 --------------------------------------
591 -- Set_Attributes for:OKC_CLASSES_B --
592 --------------------------------------
593 FUNCTION Set_Attributes (
594 p_cls_rec IN cls_rec_type,
595 x_cls_rec OUT NOCOPY cls_rec_type
596 ) RETURN VARCHAR2 IS
597 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
598 BEGIN
599 x_cls_rec := p_cls_rec;
600 RETURN(l_return_status);
601 END Set_Attributes;
602 BEGIN
603 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
604 p_init_msg_list,
605 '_PVT',
606 x_return_status);
607 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
608 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
609 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
610 RAISE OKC_API.G_EXCEPTION_ERROR;
611 END IF;
612 --- Setting item attributes
613 l_return_status := Set_Attributes(
614 p_cls_rec, -- IN
615 l_cls_rec); -- OUT
616 --- If any errors happen abort API
617 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
618 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
619 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
620 RAISE OKC_API.G_EXCEPTION_ERROR;
621 END IF;
622 INSERT INTO OKC_CLASSES_B(
623 code,
624 fff_function_id,
625 object_version_number,
626 created_by,
627 creation_date,
628 last_updated_by,
629 last_update_date,
630 last_update_login,
631 application_id,
632 adv_search_form_function_id)
633 VALUES (
634 l_cls_rec.code,
635 l_cls_rec.fff_function_id,
636 l_cls_rec.object_version_number,
637 l_cls_rec.created_by,
638 l_cls_rec.creation_date,
639 l_cls_rec.last_updated_by,
640 l_cls_rec.last_update_date,
641 l_cls_rec.last_update_login,
642 l_cls_rec.application_id,
643 l_cls_rec.adv_search_form_function_id);
644 -- Set OUT values
645 x_cls_rec := l_cls_rec;
646 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
647 EXCEPTION
648 WHEN OKC_API.G_EXCEPTION_ERROR THEN
649 x_return_status := OKC_API.HANDLE_EXCEPTIONS
650 (
651 l_api_name,
652 G_PKG_NAME,
656 '_PVT'
653 'OKC_API.G_RET_STS_ERROR',
654 x_msg_count,
655 x_msg_data,
657 );
658 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
659 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
660 (
661 l_api_name,
662 G_PKG_NAME,
663 'OKC_API.G_RET_STS_UNEXP_ERROR',
664 x_msg_count,
665 x_msg_data,
666 '_PVT'
667 );
668 WHEN OTHERS THEN
669 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
670 (
671 l_api_name,
672 G_PKG_NAME,
673 'OTHERS',
674 x_msg_count,
675 x_msg_data,
676 '_PVT'
677 );
678 END insert_row;
679 -----------------------------------
680 -- insert_row for:OKC_CLASSES_TL --
681 -----------------------------------
682 PROCEDURE insert_row(
683 p_init_msg_list IN VARCHAR2 ,
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2,
687 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
688 x_okc_classes_tl_rec OUT NOCOPY okc_classes_tl_rec_type) IS
689
690 l_api_version CONSTANT NUMBER := 1;
691 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
692 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
693 l_okc_classes_tl_rec okc_classes_tl_rec_type := p_okc_classes_tl_rec;
694 l_def_okc_classes_tl_rec okc_classes_tl_rec_type;
695 CURSOR get_languages IS
696 SELECT *
697 FROM FND_LANGUAGES
698 WHERE INSTALLED_FLAG IN ('I', 'B');
699 ---------------------------------------
700 -- Set_Attributes for:OKC_CLASSES_TL --
701 ---------------------------------------
702 FUNCTION Set_Attributes (
703 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
704 x_okc_classes_tl_rec OUT NOCOPY okc_classes_tl_rec_type
705 ) RETURN VARCHAR2 IS
706 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
707 BEGIN
708 x_okc_classes_tl_rec := p_okc_classes_tl_rec;
709 x_okc_classes_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
710 x_okc_classes_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
711 RETURN(l_return_status);
712 END Set_Attributes;
713 BEGIN
714 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
715 p_init_msg_list,
716 '_PVT',
717 x_return_status);
718 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
719 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
720 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
721 RAISE OKC_API.G_EXCEPTION_ERROR;
722 END IF;
723 --- Setting item attributes
724 l_return_status := Set_Attributes(
725 p_okc_classes_tl_rec, -- IN
726 l_okc_classes_tl_rec); -- OUT
727 --- If any errors happen abort API
728 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
729 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
730 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
731 RAISE OKC_API.G_EXCEPTION_ERROR;
732 END IF;
733 FOR l_lang_rec IN get_languages LOOP
734 l_okc_classes_tl_rec.language := l_lang_rec.language_code;
735 INSERT INTO OKC_CLASSES_TL(
736 code,
737 language,
738 source_lang,
739 sfwt_flag,
740 meaning,
741 description,
742 created_by,
743 creation_date,
744 last_updated_by,
745 last_update_date,
746 last_update_login)
747 VALUES (
748 l_okc_classes_tl_rec.code,
749 l_okc_classes_tl_rec.language,
750 l_okc_classes_tl_rec.source_lang,
751 l_okc_classes_tl_rec.sfwt_flag,
752 l_okc_classes_tl_rec.meaning,
753 l_okc_classes_tl_rec.description,
754 l_okc_classes_tl_rec.created_by,
755 l_okc_classes_tl_rec.creation_date,
756 l_okc_classes_tl_rec.last_updated_by,
757 l_okc_classes_tl_rec.last_update_date,
758 l_okc_classes_tl_rec.last_update_login);
759 END LOOP;
760 -- Set OUT values
761 x_okc_classes_tl_rec := l_okc_classes_tl_rec;
762 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
763 EXCEPTION
764 WHEN OKC_API.G_EXCEPTION_ERROR THEN
765 x_return_status := OKC_API.HANDLE_EXCEPTIONS
766 (
767 l_api_name,
768 G_PKG_NAME,
769 'OKC_API.G_RET_STS_ERROR',
770 x_msg_count,
771 x_msg_data,
772 '_PVT'
773 );
774 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
775 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
776 (
777 l_api_name,
778 G_PKG_NAME,
779 'OKC_API.G_RET_STS_UNEXP_ERROR',
780 x_msg_count,
781 x_msg_data,
782 '_PVT'
783 );
784 WHEN OTHERS THEN
785 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
786 (
787 l_api_name,
788 G_PKG_NAME,
789 'OTHERS',
790 x_msg_count,
794 END insert_row;
791 x_msg_data,
792 '_PVT'
793 );
795 ----------------------------------
796 -- insert_row for:OKC_CLASSES_V --
797 ----------------------------------
798 PROCEDURE insert_row(
799 p_api_version IN NUMBER,
800 p_init_msg_list IN VARCHAR2 ,
801 x_return_status OUT NOCOPY VARCHAR2,
802 x_msg_count OUT NOCOPY NUMBER,
803 x_msg_data OUT NOCOPY VARCHAR2,
804 p_clsv_rec IN clsv_rec_type,
805 x_clsv_rec OUT NOCOPY clsv_rec_type) IS
806
807 l_api_version CONSTANT NUMBER := 1;
808 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
809 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
810 l_clsv_rec clsv_rec_type;
811 l_def_clsv_rec clsv_rec_type;
812 l_cls_rec cls_rec_type;
813 lx_cls_rec cls_rec_type;
814 l_okc_classes_tl_rec okc_classes_tl_rec_type;
815 lx_okc_classes_tl_rec okc_classes_tl_rec_type;
816 -------------------------------
817 -- FUNCTION fill_who_columns --
818 -------------------------------
819 FUNCTION fill_who_columns (
820 p_clsv_rec IN clsv_rec_type
821 ) RETURN clsv_rec_type IS
822 l_clsv_rec clsv_rec_type := p_clsv_rec;
823 BEGIN
824 l_clsv_rec.CREATION_DATE := SYSDATE;
825 l_clsv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
826 l_clsv_rec.LAST_UPDATE_DATE := l_clsv_rec.CREATION_DATE;
827 l_clsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
828 l_clsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
829 RETURN(l_clsv_rec);
830 END fill_who_columns;
831 --------------------------------------
832 -- Set_Attributes for:OKC_CLASSES_V --
833 --------------------------------------
834 FUNCTION Set_Attributes (
835 p_clsv_rec IN clsv_rec_type,
836 x_clsv_rec OUT NOCOPY clsv_rec_type
837 ) RETURN VARCHAR2 IS
838 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
839 BEGIN
840 x_clsv_rec := p_clsv_rec;
841 x_clsv_rec.OBJECT_VERSION_NUMBER := 10000;
842 x_clsv_rec.SFWT_FLAG := 'N';
843 RETURN(l_return_status);
844 END Set_Attributes;
845 BEGIN
846 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
847 G_PKG_NAME,
848 p_init_msg_list,
849 l_api_version,
850 p_api_version,
851 '_PVT',
852 x_return_status);
853 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
856 RAISE OKC_API.G_EXCEPTION_ERROR;
857 END IF;
858 l_clsv_rec := null_out_defaults(p_clsv_rec);
859 --- Setting item attributes
860 l_return_status := Set_Attributes(
861 l_clsv_rec, -- IN
862 l_def_clsv_rec); -- OUT
863 --- If any errors happen abort API
864 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
865 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
866 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
867 RAISE OKC_API.G_EXCEPTION_ERROR;
868 END IF;
869 l_def_clsv_rec := fill_who_columns(l_def_clsv_rec);
870 --- Validate all non-missing attributes (Item Level Validation)
871 l_return_status := Validate_Attributes(l_def_clsv_rec);
872 --- If any errors happen abort API
873 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
874 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
875 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
876 RAISE OKC_API.G_EXCEPTION_ERROR;
877 END IF;
878 l_return_status := Validate_Record(l_def_clsv_rec);
879 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
880 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
881 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
882 RAISE OKC_API.G_EXCEPTION_ERROR;
883 END IF;
884 --------------------------------------
885 -- Move VIEW record to "Child" records
886 --------------------------------------
887 migrate(l_def_clsv_rec, l_cls_rec);
888 migrate(l_def_clsv_rec, l_okc_classes_tl_rec);
889 --------------------------------------------
890 -- Call the INSERT_ROW for each child record
891 --------------------------------------------
892 insert_row(
893 p_init_msg_list,
894 x_return_status,
895 x_msg_count,
896 x_msg_data,
897 l_cls_rec,
898 lx_cls_rec
899 );
900 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
901 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
902 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
903 RAISE OKC_API.G_EXCEPTION_ERROR;
904 END IF;
905 migrate(lx_cls_rec, l_def_clsv_rec);
906 insert_row(
907 p_init_msg_list,
908 x_return_status,
909 x_msg_count,
910 x_msg_data,
914 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
911 l_okc_classes_tl_rec,
912 lx_okc_classes_tl_rec
913 );
915 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
916 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
917 RAISE OKC_API.G_EXCEPTION_ERROR;
918 END IF;
919 migrate(lx_okc_classes_tl_rec, l_def_clsv_rec);
920 -- Set OUT values
921 x_clsv_rec := l_def_clsv_rec;
922 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
923 EXCEPTION
924 WHEN OKC_API.G_EXCEPTION_ERROR THEN
925 x_return_status := OKC_API.HANDLE_EXCEPTIONS
926 (
927 l_api_name,
928 G_PKG_NAME,
929 'OKC_API.G_RET_STS_ERROR',
930 x_msg_count,
931 x_msg_data,
932 '_PVT'
933 );
934 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
935 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
936 (
937 l_api_name,
938 G_PKG_NAME,
939 'OKC_API.G_RET_STS_UNEXP_ERROR',
940 x_msg_count,
941 x_msg_data,
942 '_PVT'
943 );
944 WHEN OTHERS THEN
945 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
946 (
947 l_api_name,
948 G_PKG_NAME,
949 'OTHERS',
950 x_msg_count,
951 x_msg_data,
952 '_PVT'
953 );
954 END insert_row;
955 ----------------------------------------
956 -- PL/SQL TBL insert_row for:CLSV_TBL --
957 ----------------------------------------
958 PROCEDURE insert_row(
959 p_api_version IN NUMBER,
960 p_init_msg_list IN VARCHAR2 ,
961 x_return_status OUT NOCOPY VARCHAR2,
962 x_msg_count OUT NOCOPY NUMBER,
963 x_msg_data OUT NOCOPY VARCHAR2,
964 p_clsv_tbl IN clsv_tbl_type,
965 x_clsv_tbl OUT NOCOPY clsv_tbl_type) IS
966
967 l_api_version CONSTANT NUMBER := 1;
968 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
969 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
970 i NUMBER := 0;
971 BEGIN
972 OKC_API.init_msg_list(p_init_msg_list);
973 -- Make sure PL/SQL table has records in it before passing
974 IF (p_clsv_tbl.COUNT > 0) THEN
975 i := p_clsv_tbl.FIRST;
976 LOOP
977 insert_row (
978 p_api_version => p_api_version,
979 p_init_msg_list => OKC_API.G_FALSE,
980 x_return_status => x_return_status,
981 x_msg_count => x_msg_count,
982 x_msg_data => x_msg_data,
983 p_clsv_rec => p_clsv_tbl(i),
984 x_clsv_rec => x_clsv_tbl(i));
985 EXIT WHEN (i = p_clsv_tbl.LAST);
986 i := p_clsv_tbl.NEXT(i);
987 END LOOP;
988 END IF;
989 EXCEPTION
990 WHEN OKC_API.G_EXCEPTION_ERROR THEN
991 x_return_status := OKC_API.HANDLE_EXCEPTIONS
992 (
993 l_api_name,
994 G_PKG_NAME,
995 'OKC_API.G_RET_STS_ERROR',
996 x_msg_count,
997 x_msg_data,
998 '_PVT'
999 );
1000 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1001 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1002 (
1003 l_api_name,
1004 G_PKG_NAME,
1005 'OKC_API.G_RET_STS_UNEXP_ERROR',
1006 x_msg_count,
1007 x_msg_data,
1008 '_PVT'
1009 );
1010 WHEN OTHERS THEN
1011 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1012 (
1013 l_api_name,
1014 G_PKG_NAME,
1015 'OTHERS',
1016 x_msg_count,
1017 x_msg_data,
1018 '_PVT'
1019 );
1020 END insert_row;
1021
1022 ---------------------------------------------------------------------------
1023 -- PROCEDURE lock_row
1024 ---------------------------------------------------------------------------
1025 --------------------------------
1026 -- lock_row for:OKC_CLASSES_B --
1027 --------------------------------
1028 PROCEDURE lock_row(
1029 p_init_msg_list IN VARCHAR2 ,
1030 x_return_status OUT NOCOPY VARCHAR2,
1031 x_msg_count OUT NOCOPY NUMBER,
1032 x_msg_data OUT NOCOPY VARCHAR2,
1033 p_cls_rec IN cls_rec_type) IS
1034
1035 E_Resource_Busy EXCEPTION;
1036 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1037 CURSOR lock_csr (p_cls_rec IN cls_rec_type) IS
1038 SELECT OBJECT_VERSION_NUMBER
1039 FROM OKC_CLASSES_B
1040 WHERE CODE = p_cls_rec.code
1041 AND OBJECT_VERSION_NUMBER = p_cls_rec.object_version_number
1042 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1043
1044 CURSOR lchk_csr (p_cls_rec IN cls_rec_type) IS
1045 SELECT OBJECT_VERSION_NUMBER
1046 FROM OKC_CLASSES_B
1047 WHERE CODE = p_cls_rec.code;
1048 l_api_version CONSTANT NUMBER := 1;
1049 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1053 l_row_notfound BOOLEAN := FALSE;
1050 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1051 l_object_version_number OKC_CLASSES_B.OBJECT_VERSION_NUMBER%TYPE;
1052 lc_object_version_number OKC_CLASSES_B.OBJECT_VERSION_NUMBER%TYPE;
1054 lc_row_notfound BOOLEAN := FALSE;
1055 BEGIN
1056 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1057 p_init_msg_list,
1058 '_PVT',
1059 x_return_status);
1060 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1062 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1063 RAISE OKC_API.G_EXCEPTION_ERROR;
1064 END IF;
1065 BEGIN
1066 OPEN lock_csr(p_cls_rec);
1067 FETCH lock_csr INTO l_object_version_number;
1068 l_row_notfound := lock_csr%NOTFOUND;
1069 CLOSE lock_csr;
1070 EXCEPTION
1071 WHEN E_Resource_Busy THEN
1072 IF (lock_csr%ISOPEN) THEN
1073 CLOSE lock_csr;
1074 END IF;
1075 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1076 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1077 END;
1078
1079 IF ( l_row_notfound ) THEN
1080 OPEN lchk_csr(p_cls_rec);
1081 FETCH lchk_csr INTO lc_object_version_number;
1082 lc_row_notfound := lchk_csr%NOTFOUND;
1083 CLOSE lchk_csr;
1084 END IF;
1085 IF (lc_row_notfound) THEN
1086 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1087 RAISE OKC_API.G_EXCEPTION_ERROR;
1088 ELSIF lc_object_version_number > p_cls_rec.object_version_number THEN
1089 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1090 RAISE OKC_API.G_EXCEPTION_ERROR;
1091 ELSIF lc_object_version_number <> p_cls_rec.object_version_number THEN
1092 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1093 RAISE OKC_API.G_EXCEPTION_ERROR;
1094 ELSIF lc_object_version_number = -1 THEN
1095 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1096 RAISE OKC_API.G_EXCEPTION_ERROR;
1097 END IF;
1098 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1099 EXCEPTION
1100 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1101 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1102 (
1103 l_api_name,
1104 G_PKG_NAME,
1105 'OKC_API.G_RET_STS_ERROR',
1106 x_msg_count,
1107 x_msg_data,
1108 '_PVT'
1109 );
1110 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1111 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1112 (
1113 l_api_name,
1114 G_PKG_NAME,
1115 'OKC_API.G_RET_STS_UNEXP_ERROR',
1116 x_msg_count,
1117 x_msg_data,
1118 '_PVT'
1119 );
1120 WHEN OTHERS THEN
1121 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1122 (
1123 l_api_name,
1124 G_PKG_NAME,
1125 'OTHERS',
1126 x_msg_count,
1127 x_msg_data,
1128 '_PVT'
1129 );
1130 END lock_row;
1131 ---------------------------------
1132 -- lock_row for:OKC_CLASSES_TL --
1133 ---------------------------------
1134 PROCEDURE lock_row(
1135 p_init_msg_list IN VARCHAR2 ,
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 x_msg_count OUT NOCOPY NUMBER,
1138 x_msg_data OUT NOCOPY VARCHAR2,
1139 p_okc_classes_tl_rec IN okc_classes_tl_rec_type) IS
1140
1141 E_Resource_Busy EXCEPTION;
1142 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1143 CURSOR lock_csr (p_okc_classes_tl_rec IN okc_classes_tl_rec_type) IS
1144 SELECT *
1145 FROM OKC_CLASSES_TL
1146 WHERE CODE = p_okc_classes_tl_rec.code
1147 FOR UPDATE NOWAIT;
1148
1149 l_api_version CONSTANT NUMBER := 1;
1150 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1151 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1152 l_lock_var lock_csr%ROWTYPE;
1153 l_row_notfound BOOLEAN := FALSE;
1154 lc_row_notfound BOOLEAN := FALSE;
1155 BEGIN
1156 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1157 p_init_msg_list,
1158 '_PVT',
1159 x_return_status);
1160 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1161 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1162 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1163 RAISE OKC_API.G_EXCEPTION_ERROR;
1164 END IF;
1165 BEGIN
1166 OPEN lock_csr(p_okc_classes_tl_rec);
1167 FETCH lock_csr INTO l_lock_var;
1168 l_row_notfound := lock_csr%NOTFOUND;
1169 CLOSE lock_csr;
1170 EXCEPTION
1171 WHEN E_Resource_Busy THEN
1172 IF (lock_csr%ISOPEN) THEN
1173 CLOSE lock_csr;
1174 END IF;
1175 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1176 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1177 END;
1178
1179 IF ( l_row_notfound ) THEN
1183 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1180 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1181 RAISE OKC_API.G_EXCEPTION_ERROR;
1182 END IF;
1184 EXCEPTION
1185 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1186 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1187 (
1188 l_api_name,
1189 G_PKG_NAME,
1190 'OKC_API.G_RET_STS_ERROR',
1191 x_msg_count,
1192 x_msg_data,
1193 '_PVT'
1194 );
1195 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1196 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1197 (
1198 l_api_name,
1199 G_PKG_NAME,
1200 'OKC_API.G_RET_STS_UNEXP_ERROR',
1201 x_msg_count,
1202 x_msg_data,
1203 '_PVT'
1204 );
1205 WHEN OTHERS THEN
1206 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1207 (
1208 l_api_name,
1209 G_PKG_NAME,
1210 'OTHERS',
1211 x_msg_count,
1212 x_msg_data,
1213 '_PVT'
1214 );
1215 END lock_row;
1216 --------------------------------
1217 -- lock_row for:OKC_CLASSES_V --
1218 --------------------------------
1219 PROCEDURE lock_row(
1220 p_api_version IN NUMBER,
1221 p_init_msg_list IN VARCHAR2 ,
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER,
1224 x_msg_data OUT NOCOPY VARCHAR2,
1225 p_clsv_rec IN clsv_rec_type) IS
1226
1227 l_api_version CONSTANT NUMBER := 1;
1228 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1229 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1230 l_cls_rec cls_rec_type;
1231 l_okc_classes_tl_rec okc_classes_tl_rec_type;
1232 BEGIN
1233 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1234 G_PKG_NAME,
1235 p_init_msg_list,
1236 l_api_version,
1237 p_api_version,
1238 '_PVT',
1239 x_return_status);
1240 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1241 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1242 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1243 RAISE OKC_API.G_EXCEPTION_ERROR;
1244 END IF;
1245 --------------------------------------
1246 -- Move VIEW record to "Child" records
1247 --------------------------------------
1248 migrate(p_clsv_rec, l_cls_rec);
1249 migrate(p_clsv_rec, l_okc_classes_tl_rec);
1250 --------------------------------------------
1251 -- Call the LOCK_ROW for each child record
1252 --------------------------------------------
1253 lock_row(
1254 p_init_msg_list,
1255 x_return_status,
1256 x_msg_count,
1257 x_msg_data,
1258 l_cls_rec
1259 );
1260 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1261 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1262 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1263 RAISE OKC_API.G_EXCEPTION_ERROR;
1264 END IF;
1265 lock_row(
1266 p_init_msg_list,
1267 x_return_status,
1268 x_msg_count,
1269 x_msg_data,
1270 l_okc_classes_tl_rec
1271 );
1272 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1273 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1274 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1275 RAISE OKC_API.G_EXCEPTION_ERROR;
1276 END IF;
1277 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1278 EXCEPTION
1279 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1280 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1281 (
1282 l_api_name,
1283 G_PKG_NAME,
1284 'OKC_API.G_RET_STS_ERROR',
1285 x_msg_count,
1286 x_msg_data,
1287 '_PVT'
1288 );
1289 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1290 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1291 (
1292 l_api_name,
1293 G_PKG_NAME,
1294 'OKC_API.G_RET_STS_UNEXP_ERROR',
1295 x_msg_count,
1296 x_msg_data,
1297 '_PVT'
1298 );
1299 WHEN OTHERS THEN
1300 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1301 (
1302 l_api_name,
1303 G_PKG_NAME,
1304 'OTHERS',
1305 x_msg_count,
1306 x_msg_data,
1307 '_PVT'
1308 );
1309 END lock_row;
1310 --------------------------------------
1311 -- PL/SQL TBL lock_row for:CLSV_TBL --
1312 --------------------------------------
1313 PROCEDURE lock_row(
1314 p_api_version IN NUMBER,
1315 p_init_msg_list IN VARCHAR2 ,
1316 x_return_status OUT NOCOPY VARCHAR2,
1317 x_msg_count OUT NOCOPY NUMBER,
1318 x_msg_data OUT NOCOPY VARCHAR2,
1319 p_clsv_tbl IN clsv_tbl_type) IS
1320
1324 i NUMBER := 0;
1321 l_api_version CONSTANT NUMBER := 1;
1322 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1323 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1325 BEGIN
1326 OKC_API.init_msg_list(p_init_msg_list);
1327 -- Make sure PL/SQL table has records in it before passing
1328 IF (p_clsv_tbl.COUNT > 0) THEN
1329 i := p_clsv_tbl.FIRST;
1330 LOOP
1331 lock_row (
1332 p_api_version => p_api_version,
1333 p_init_msg_list => OKC_API.G_FALSE,
1334 x_return_status => x_return_status,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data,
1337 p_clsv_rec => p_clsv_tbl(i));
1338 EXIT WHEN (i = p_clsv_tbl.LAST);
1339 i := p_clsv_tbl.NEXT(i);
1340 END LOOP;
1341 END IF;
1342 EXCEPTION
1343 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1344 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1345 (
1346 l_api_name,
1347 G_PKG_NAME,
1348 'OKC_API.G_RET_STS_ERROR',
1349 x_msg_count,
1350 x_msg_data,
1351 '_PVT'
1352 );
1353 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1354 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1355 (
1356 l_api_name,
1357 G_PKG_NAME,
1358 'OKC_API.G_RET_STS_UNEXP_ERROR',
1359 x_msg_count,
1360 x_msg_data,
1361 '_PVT'
1362 );
1363 WHEN OTHERS THEN
1364 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1365 (
1366 l_api_name,
1367 G_PKG_NAME,
1368 'OTHERS',
1369 x_msg_count,
1370 x_msg_data,
1371 '_PVT'
1372 );
1373 END lock_row;
1374
1375 ---------------------------------------------------------------------------
1376 -- PROCEDURE update_row
1377 ---------------------------------------------------------------------------
1378 ----------------------------------
1379 -- update_row for:OKC_CLASSES_B --
1380 ----------------------------------
1381 PROCEDURE update_row(
1382 p_init_msg_list IN VARCHAR2 ,
1383 x_return_status OUT NOCOPY VARCHAR2,
1384 x_msg_count OUT NOCOPY NUMBER,
1385 x_msg_data OUT NOCOPY VARCHAR2,
1386 p_cls_rec IN cls_rec_type,
1387 x_cls_rec OUT NOCOPY cls_rec_type) IS
1388
1389 l_api_version CONSTANT NUMBER := 1;
1390 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1391 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1392 l_cls_rec cls_rec_type := p_cls_rec;
1393 l_def_cls_rec cls_rec_type;
1394 l_row_notfound BOOLEAN := TRUE;
1395 ----------------------------------
1396 -- FUNCTION populate_new_record --
1397 ----------------------------------
1398 FUNCTION populate_new_record (
1399 p_cls_rec IN cls_rec_type,
1400 x_cls_rec OUT NOCOPY cls_rec_type
1401 ) RETURN VARCHAR2 IS
1402 l_cls_rec cls_rec_type;
1403 l_row_notfound BOOLEAN := TRUE;
1404 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1405 BEGIN
1406 x_cls_rec := p_cls_rec;
1407 -- Get current database values
1408 l_cls_rec := get_rec(p_cls_rec, l_row_notfound);
1409 IF (l_row_notfound) THEN
1410 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1411 END IF;
1412 IF (x_cls_rec.code = OKC_API.G_MISS_CHAR)
1413 THEN
1414 x_cls_rec.code := l_cls_rec.code;
1415 END IF;
1416 IF (x_cls_rec.fff_function_id = OKC_API.G_MISS_NUM)
1417 THEN
1418 x_cls_rec.fff_function_id := l_cls_rec.fff_function_id;
1419 END IF;
1420 IF (x_cls_rec.object_version_number = OKC_API.G_MISS_NUM)
1421 THEN
1422 x_cls_rec.object_version_number := l_cls_rec.object_version_number;
1423 END IF;
1424 IF (x_cls_rec.created_by = OKC_API.G_MISS_NUM)
1425 THEN
1426 x_cls_rec.created_by := l_cls_rec.created_by;
1427 END IF;
1428 IF (x_cls_rec.creation_date = OKC_API.G_MISS_DATE)
1429 THEN
1430 x_cls_rec.creation_date := l_cls_rec.creation_date;
1431 END IF;
1432 IF (x_cls_rec.last_updated_by = OKC_API.G_MISS_NUM)
1433 THEN
1434 x_cls_rec.last_updated_by := l_cls_rec.last_updated_by;
1435 END IF;
1436 IF (x_cls_rec.last_update_date = OKC_API.G_MISS_DATE)
1437 THEN
1438 x_cls_rec.last_update_date := l_cls_rec.last_update_date;
1439 END IF;
1440 IF (x_cls_rec.last_update_login = OKC_API.G_MISS_NUM)
1441 THEN
1442 x_cls_rec.last_update_login := l_cls_rec.last_update_login;
1443 END IF;
1444 IF (x_cls_rec.application_id = OKC_API.G_MISS_NUM)
1445 THEN
1446 x_cls_rec.application_id := l_cls_rec.application_id;
1447 END IF;
1448 IF (x_cls_rec.adv_search_form_function_id = OKC_API.G_MISS_NUM)
1449 THEN
1453 END populate_new_record;
1450 x_cls_rec.adv_search_form_function_id := l_cls_rec.adv_search_form_function_id;
1451 END IF;
1452 RETURN(l_return_status);
1454 --------------------------------------
1455 -- Set_Attributes for:OKC_CLASSES_B --
1456 --------------------------------------
1457 FUNCTION Set_Attributes (
1458 p_cls_rec IN cls_rec_type,
1459 x_cls_rec OUT NOCOPY cls_rec_type
1460 ) RETURN VARCHAR2 IS
1461 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1462 BEGIN
1463 x_cls_rec := p_cls_rec;
1464 RETURN(l_return_status);
1465 END Set_Attributes;
1466 BEGIN
1467 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1468 p_init_msg_list,
1469 '_PVT',
1470 x_return_status);
1471 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1472 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1473 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1474 RAISE OKC_API.G_EXCEPTION_ERROR;
1475 END IF;
1476 --- Setting item attributes
1477 l_return_status := Set_Attributes(
1478 p_cls_rec, -- IN
1479 l_cls_rec); -- OUT
1480 --- If any errors happen abort API
1481 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_ERROR;
1485 END IF;
1486 l_return_status := populate_new_record(l_cls_rec, l_def_cls_rec);
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 UPDATE OKC_CLASSES_B
1493 SET FFF_FUNCTION_ID = l_def_cls_rec.fff_function_id,
1494 OBJECT_VERSION_NUMBER = l_def_cls_rec.object_version_number,
1495 CREATED_BY = l_def_cls_rec.created_by,
1496 CREATION_DATE = l_def_cls_rec.creation_date,
1497 LAST_UPDATED_BY = l_def_cls_rec.last_updated_by,
1498 LAST_UPDATE_DATE = l_def_cls_rec.last_update_date,
1499 LAST_UPDATE_LOGIN = l_def_cls_rec.last_update_login,
1500 APPLICATION_ID=l_def_cls_rec.application_id,
1501 ADV_SEARCH_FORM_FUNCTION_ID=l_def_cls_rec.adv_search_form_function_id
1502 WHERE CODE = l_def_cls_rec.code;
1503
1504 x_cls_rec := l_def_cls_rec;
1505 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1506 EXCEPTION
1507 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1508 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1509 (
1510 l_api_name,
1511 G_PKG_NAME,
1512 'OKC_API.G_RET_STS_ERROR',
1513 x_msg_count,
1514 x_msg_data,
1515 '_PVT'
1516 );
1517 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1518 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1519 (
1520 l_api_name,
1521 G_PKG_NAME,
1522 'OKC_API.G_RET_STS_UNEXP_ERROR',
1523 x_msg_count,
1524 x_msg_data,
1525 '_PVT'
1526 );
1527 WHEN OTHERS THEN
1528 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1529 (
1530 l_api_name,
1531 G_PKG_NAME,
1532 'OTHERS',
1533 x_msg_count,
1534 x_msg_data,
1535 '_PVT'
1536 );
1537 END update_row;
1538 -----------------------------------
1539 -- update_row for:OKC_CLASSES_TL --
1540 -----------------------------------
1541 PROCEDURE update_row(
1542 p_init_msg_list IN VARCHAR2 ,
1543 x_return_status OUT NOCOPY VARCHAR2,
1544 x_msg_count OUT NOCOPY NUMBER,
1545 x_msg_data OUT NOCOPY VARCHAR2,
1546 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
1547 x_okc_classes_tl_rec OUT NOCOPY okc_classes_tl_rec_type) IS
1548
1549 l_api_version CONSTANT NUMBER := 1;
1550 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
1551 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1552 l_okc_classes_tl_rec okc_classes_tl_rec_type := p_okc_classes_tl_rec;
1553 l_def_okc_classes_tl_rec okc_classes_tl_rec_type;
1554 l_row_notfound BOOLEAN := TRUE;
1555 ----------------------------------
1556 -- FUNCTION populate_new_record --
1557 ----------------------------------
1558 FUNCTION populate_new_record (
1559 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
1560 x_okc_classes_tl_rec OUT NOCOPY okc_classes_tl_rec_type
1561 ) RETURN VARCHAR2 IS
1562 l_okc_classes_tl_rec okc_classes_tl_rec_type;
1563 l_row_notfound BOOLEAN := TRUE;
1564 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1565 BEGIN
1566 x_okc_classes_tl_rec := p_okc_classes_tl_rec;
1567 -- Get current database values
1568 l_okc_classes_tl_rec := get_rec(p_okc_classes_tl_rec, l_row_notfound);
1569 IF (l_row_notfound) THEN
1570 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1571 END IF;
1572 IF (x_okc_classes_tl_rec.code = OKC_API.G_MISS_CHAR)
1576 IF (x_okc_classes_tl_rec.language = OKC_API.G_MISS_CHAR)
1573 THEN
1574 x_okc_classes_tl_rec.code := l_okc_classes_tl_rec.code;
1575 END IF;
1577 THEN
1578 x_okc_classes_tl_rec.language := l_okc_classes_tl_rec.language;
1579 END IF;
1580 IF (x_okc_classes_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
1581 THEN
1582 x_okc_classes_tl_rec.source_lang := l_okc_classes_tl_rec.source_lang;
1583 END IF;
1584 IF (x_okc_classes_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
1585 THEN
1586 x_okc_classes_tl_rec.sfwt_flag := l_okc_classes_tl_rec.sfwt_flag;
1587 END IF;
1588 IF (x_okc_classes_tl_rec.meaning = OKC_API.G_MISS_CHAR)
1589 THEN
1590 x_okc_classes_tl_rec.meaning := l_okc_classes_tl_rec.meaning;
1591 END IF;
1592 IF (x_okc_classes_tl_rec.description = OKC_API.G_MISS_CHAR)
1593 THEN
1594 x_okc_classes_tl_rec.description := l_okc_classes_tl_rec.description;
1595 END IF;
1596 IF (x_okc_classes_tl_rec.created_by = OKC_API.G_MISS_NUM)
1597 THEN
1598 x_okc_classes_tl_rec.created_by := l_okc_classes_tl_rec.created_by;
1599 END IF;
1600 IF (x_okc_classes_tl_rec.creation_date = OKC_API.G_MISS_DATE)
1601 THEN
1602 x_okc_classes_tl_rec.creation_date := l_okc_classes_tl_rec.creation_date;
1603 END IF;
1604 IF (x_okc_classes_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
1605 THEN
1606 x_okc_classes_tl_rec.last_updated_by := l_okc_classes_tl_rec.last_updated_by;
1607 END IF;
1608 IF (x_okc_classes_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
1609 THEN
1610 x_okc_classes_tl_rec.last_update_date := l_okc_classes_tl_rec.last_update_date;
1611 END IF;
1612 IF (x_okc_classes_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
1613 THEN
1614 x_okc_classes_tl_rec.last_update_login := l_okc_classes_tl_rec.last_update_login;
1615 END IF;
1616 RETURN(l_return_status);
1617 END populate_new_record;
1618 ---------------------------------------
1619 -- Set_Attributes for:OKC_CLASSES_TL --
1620 ---------------------------------------
1621 FUNCTION Set_Attributes (
1622 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
1623 x_okc_classes_tl_rec OUT NOCOPY okc_classes_tl_rec_type
1624 ) RETURN VARCHAR2 IS
1625 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1626 BEGIN
1627 x_okc_classes_tl_rec := p_okc_classes_tl_rec;
1628 x_okc_classes_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
1629 x_okc_classes_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
1630 RETURN(l_return_status);
1631 END Set_Attributes;
1632 BEGIN
1633 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1634 p_init_msg_list,
1635 '_PVT',
1636 x_return_status);
1637 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1638 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1639 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1640 RAISE OKC_API.G_EXCEPTION_ERROR;
1641 END IF;
1642 --- Setting item attributes
1643 l_return_status := Set_Attributes(
1644 p_okc_classes_tl_rec, -- IN
1645 l_okc_classes_tl_rec); -- OUT
1646 --- If any errors happen abort API
1647 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1648 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1649 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1650 RAISE OKC_API.G_EXCEPTION_ERROR;
1651 END IF;
1652 l_return_status := populate_new_record(l_okc_classes_tl_rec, l_def_okc_classes_tl_rec);
1653 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1654 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1655 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1656 RAISE OKC_API.G_EXCEPTION_ERROR;
1657 END IF;
1658 UPDATE OKC_CLASSES_TL
1659 SET MEANING = l_def_okc_classes_tl_rec.meaning,
1660 DESCRIPTION = l_def_okc_classes_tl_rec.description,
1661 CREATED_BY = l_def_okc_classes_tl_rec.created_by,
1662 CREATION_DATE = l_def_okc_classes_tl_rec.creation_date,
1663 LAST_UPDATED_BY = l_def_okc_classes_tl_rec.last_updated_by,
1664 LAST_UPDATE_DATE = l_def_okc_classes_tl_rec.last_update_date,
1665 LAST_UPDATE_LOGIN = l_def_okc_classes_tl_rec.last_update_login
1666 WHERE CODE = l_def_okc_classes_tl_rec.code
1667 AND SOURCE_LANG = USERENV('LANG');
1668
1669 UPDATE OKC_CLASSES_TL
1670 SET SFWT_FLAG = 'Y'
1671 WHERE CODE = l_def_okc_classes_tl_rec.code
1672 AND SOURCE_LANG <> USERENV('LANG');
1673
1674 x_okc_classes_tl_rec := l_def_okc_classes_tl_rec;
1675 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1676 EXCEPTION
1677 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1678 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1679 (
1680 l_api_name,
1681 G_PKG_NAME,
1682 'OKC_API.G_RET_STS_ERROR',
1683 x_msg_count,
1684 x_msg_data,
1685 '_PVT'
1686 );
1687 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1688 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1689 (
1690 l_api_name,
1691 G_PKG_NAME,
1692 'OKC_API.G_RET_STS_UNEXP_ERROR',
1696 );
1693 x_msg_count,
1694 x_msg_data,
1695 '_PVT'
1697 WHEN OTHERS THEN
1698 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1699 (
1700 l_api_name,
1701 G_PKG_NAME,
1702 'OTHERS',
1703 x_msg_count,
1704 x_msg_data,
1705 '_PVT'
1706 );
1707 END update_row;
1708 ----------------------------------
1709 -- update_row for:OKC_CLASSES_V --
1710 ----------------------------------
1711 PROCEDURE update_row(
1712 p_api_version IN NUMBER,
1713 p_init_msg_list IN VARCHAR2 ,
1714 x_return_status OUT NOCOPY VARCHAR2,
1715 x_msg_count OUT NOCOPY NUMBER,
1716 x_msg_data OUT NOCOPY VARCHAR2,
1717 p_clsv_rec IN clsv_rec_type,
1718 x_clsv_rec OUT NOCOPY clsv_rec_type) IS
1719
1720 l_api_version CONSTANT NUMBER := 1;
1721 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1722 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1723 l_clsv_rec clsv_rec_type := p_clsv_rec;
1724 l_def_clsv_rec clsv_rec_type;
1725 l_okc_classes_tl_rec okc_classes_tl_rec_type;
1726 lx_okc_classes_tl_rec okc_classes_tl_rec_type;
1727 l_cls_rec cls_rec_type;
1728 lx_cls_rec cls_rec_type;
1729 -------------------------------
1730 -- FUNCTION fill_who_columns --
1731 -------------------------------
1732 FUNCTION fill_who_columns (
1733 p_clsv_rec IN clsv_rec_type
1734 ) RETURN clsv_rec_type IS
1735 l_clsv_rec clsv_rec_type := p_clsv_rec;
1736 BEGIN
1737 l_clsv_rec.LAST_UPDATE_DATE := SYSDATE;
1738 l_clsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1739 l_clsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1740 RETURN(l_clsv_rec);
1741 END fill_who_columns;
1742 ----------------------------------
1743 -- FUNCTION populate_new_record --
1744 ----------------------------------
1745 FUNCTION populate_new_record (
1746 p_clsv_rec IN clsv_rec_type,
1747 x_clsv_rec OUT NOCOPY clsv_rec_type
1748 ) RETURN VARCHAR2 IS
1749 l_clsv_rec clsv_rec_type;
1750 l_row_notfound BOOLEAN := TRUE;
1751 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1752 BEGIN
1753 x_clsv_rec := p_clsv_rec;
1754 -- Get current database values
1755 l_clsv_rec := get_rec(p_clsv_rec, l_row_notfound);
1756 IF (l_row_notfound) THEN
1757 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1758 END IF;
1759 IF (x_clsv_rec.code = OKC_API.G_MISS_CHAR)
1760 THEN
1761 x_clsv_rec.code := l_clsv_rec.code;
1762 END IF;
1763 IF (x_clsv_rec.object_version_number = OKC_API.G_MISS_NUM)
1764 THEN
1765 x_clsv_rec.object_version_number := l_clsv_rec.object_version_number;
1766 END IF;
1767 IF (x_clsv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
1768 THEN
1769 x_clsv_rec.sfwt_flag := l_clsv_rec.sfwt_flag;
1770 END IF;
1771 IF (x_clsv_rec.fff_function_id = OKC_API.G_MISS_NUM)
1772 THEN
1773 x_clsv_rec.fff_function_id := l_clsv_rec.fff_function_id;
1774 END IF;
1775 IF (x_clsv_rec.meaning = OKC_API.G_MISS_CHAR)
1776 THEN
1777 x_clsv_rec.meaning := l_clsv_rec.meaning;
1778 END IF;
1779 IF (x_clsv_rec.description = OKC_API.G_MISS_CHAR)
1780 THEN
1781 x_clsv_rec.description := l_clsv_rec.description;
1782 END IF;
1783 IF (x_clsv_rec.created_by = OKC_API.G_MISS_NUM)
1784 THEN
1785 x_clsv_rec.created_by := l_clsv_rec.created_by;
1786 END IF;
1787 IF (x_clsv_rec.creation_date = OKC_API.G_MISS_DATE)
1788 THEN
1789 x_clsv_rec.creation_date := l_clsv_rec.creation_date;
1790 END IF;
1791 IF (x_clsv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1792 THEN
1793 x_clsv_rec.last_updated_by := l_clsv_rec.last_updated_by;
1794 END IF;
1795 IF (x_clsv_rec.last_update_date = OKC_API.G_MISS_DATE)
1796 THEN
1797 x_clsv_rec.last_update_date := l_clsv_rec.last_update_date;
1798 END IF;
1799 IF (x_clsv_rec.last_update_login = OKC_API.G_MISS_NUM)
1800 THEN
1801 x_clsv_rec.last_update_login := l_clsv_rec.last_update_login;
1802 END IF;
1803 IF (x_clsv_rec.application_id = OKC_API.G_MISS_NUM)
1804 THEN
1805 x_clsv_rec.application_id := l_clsv_rec.application_id;
1806 END IF;
1807 IF (x_clsv_rec.adv_search_form_function_id = OKC_API.G_MISS_NUM)
1808 THEN
1809 x_clsv_rec.adv_search_form_function_id := l_clsv_rec.adv_search_form_function_id;
1810 END IF;
1811 RETURN(l_return_status);
1812 END populate_new_record;
1813 --------------------------------------
1814 -- Set_Attributes for:OKC_CLASSES_V --
1815 --------------------------------------
1816 FUNCTION Set_Attributes (
1817 p_clsv_rec IN clsv_rec_type,
1818 x_clsv_rec OUT NOCOPY clsv_rec_type
1819 ) RETURN VARCHAR2 IS
1820 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1821 BEGIN
1825 END Set_Attributes;
1822 x_clsv_rec := p_clsv_rec;
1823 x_clsv_rec.OBJECT_VERSION_NUMBER := NVL(x_clsv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1824 RETURN(l_return_status);
1826 BEGIN
1827 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1828 G_PKG_NAME,
1829 p_init_msg_list,
1830 l_api_version,
1831 p_api_version,
1832 '_PVT',
1833 x_return_status);
1834 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1835 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1836 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1837 RAISE OKC_API.G_EXCEPTION_ERROR;
1838 END IF;
1839 --- Setting item attributes
1840 l_return_status := Set_Attributes(
1841 p_clsv_rec, -- IN
1842 l_clsv_rec); -- OUT
1843 --- If any errors happen abort API
1844 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1845 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1846 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1847 RAISE OKC_API.G_EXCEPTION_ERROR;
1848 END IF;
1849 l_return_status := populate_new_record(l_clsv_rec, l_def_clsv_rec);
1850 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1851 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1852 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1853 RAISE OKC_API.G_EXCEPTION_ERROR;
1854 END IF;
1855 l_def_clsv_rec := fill_who_columns(l_def_clsv_rec);
1856 --- Validate all non-missing attributes (Item Level Validation)
1857 l_return_status := Validate_Attributes(l_def_clsv_rec);
1858 --- If any errors happen abort API
1859 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1860 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1861 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1862 RAISE OKC_API.G_EXCEPTION_ERROR;
1863 END IF;
1864 l_return_status := Validate_Record(l_def_clsv_rec);
1865 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1866 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1867 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1868 RAISE OKC_API.G_EXCEPTION_ERROR;
1869 END IF;
1870
1871 --------------------------------------
1872 -- Move VIEW record to "Child" records
1873 --------------------------------------
1874 migrate(l_def_clsv_rec, l_okc_classes_tl_rec);
1875 migrate(l_def_clsv_rec, l_cls_rec);
1876 --------------------------------------------
1877 -- Call the UPDATE_ROW for each child record
1878 --------------------------------------------
1879 update_row(
1880 p_init_msg_list,
1881 x_return_status,
1882 x_msg_count,
1883 x_msg_data,
1884 l_okc_classes_tl_rec,
1885 lx_okc_classes_tl_rec
1886 );
1887 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1888 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1889 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1890 RAISE OKC_API.G_EXCEPTION_ERROR;
1891 END IF;
1892 migrate(lx_okc_classes_tl_rec, l_def_clsv_rec);
1893 update_row(
1894 p_init_msg_list,
1895 x_return_status,
1896 x_msg_count,
1897 x_msg_data,
1898 l_cls_rec,
1899 lx_cls_rec
1900 );
1901 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1902 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1903 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1904 RAISE OKC_API.G_EXCEPTION_ERROR;
1905 END IF;
1906 migrate(lx_cls_rec, l_def_clsv_rec);
1907 x_clsv_rec := l_def_clsv_rec;
1908 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1909 EXCEPTION
1910 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1911 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1912 (
1913 l_api_name,
1914 G_PKG_NAME,
1915 'OKC_API.G_RET_STS_ERROR',
1916 x_msg_count,
1917 x_msg_data,
1918 '_PVT'
1919 );
1920 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1921 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1922 (
1923 l_api_name,
1924 G_PKG_NAME,
1925 'OKC_API.G_RET_STS_UNEXP_ERROR',
1926 x_msg_count,
1927 x_msg_data,
1928 '_PVT'
1929 );
1930 WHEN OTHERS THEN
1931 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1932 (
1933 l_api_name,
1934 G_PKG_NAME,
1935 'OTHERS',
1936 x_msg_count,
1937 x_msg_data,
1938 '_PVT'
1939 );
1940 END update_row;
1941 ----------------------------------------
1942 -- PL/SQL TBL update_row for:CLSV_TBL --
1943 ----------------------------------------
1944 PROCEDURE update_row(
1945 p_api_version IN NUMBER,
1946 p_init_msg_list IN VARCHAR2 ,
1947 x_return_status OUT NOCOPY VARCHAR2,
1948 x_msg_count OUT NOCOPY NUMBER,
1949 x_msg_data OUT NOCOPY VARCHAR2,
1950 p_clsv_tbl IN clsv_tbl_type,
1951 x_clsv_tbl OUT NOCOPY clsv_tbl_type) IS
1952
1956 i NUMBER := 0;
1953 l_api_version CONSTANT NUMBER := 1;
1954 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1957 BEGIN
1958 OKC_API.init_msg_list(p_init_msg_list);
1959 -- Make sure PL/SQL table has records in it before passing
1960 IF (p_clsv_tbl.COUNT > 0) THEN
1961 i := p_clsv_tbl.FIRST;
1962 LOOP
1963 update_row (
1964 p_api_version => p_api_version,
1965 p_init_msg_list => OKC_API.G_FALSE,
1966 x_return_status => x_return_status,
1967 x_msg_count => x_msg_count,
1968 x_msg_data => x_msg_data,
1969 p_clsv_rec => p_clsv_tbl(i),
1970 x_clsv_rec => x_clsv_tbl(i));
1971 EXIT WHEN (i = p_clsv_tbl.LAST);
1972 i := p_clsv_tbl.NEXT(i);
1973 END LOOP;
1974 END IF;
1975 EXCEPTION
1976 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1977 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1978 (
1979 l_api_name,
1980 G_PKG_NAME,
1981 'OKC_API.G_RET_STS_ERROR',
1982 x_msg_count,
1983 x_msg_data,
1984 '_PVT'
1985 );
1986 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1987 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1988 (
1989 l_api_name,
1990 G_PKG_NAME,
1991 'OKC_API.G_RET_STS_UNEXP_ERROR',
1992 x_msg_count,
1993 x_msg_data,
1994 '_PVT'
1995 );
1996 WHEN OTHERS THEN
1997 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1998 (
1999 l_api_name,
2000 G_PKG_NAME,
2001 'OTHERS',
2002 x_msg_count,
2003 x_msg_data,
2004 '_PVT'
2005 );
2006 END update_row;
2007
2008 ---------------------------------------------------------------------------
2009 -- PROCEDURE delete_row
2010 ---------------------------------------------------------------------------
2011 ----------------------------------
2012 -- delete_row for:OKC_CLASSES_B --
2013 ----------------------------------
2014 PROCEDURE delete_row(
2015 p_init_msg_list IN VARCHAR2 ,
2016 x_return_status OUT NOCOPY VARCHAR2,
2017 x_msg_count OUT NOCOPY NUMBER,
2018 x_msg_data OUT NOCOPY VARCHAR2,
2019 p_cls_rec IN cls_rec_type) IS
2020
2021 l_api_version CONSTANT NUMBER := 1;
2022 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2023 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2024 l_cls_rec cls_rec_type:= p_cls_rec;
2025 l_row_notfound BOOLEAN := TRUE;
2026 BEGIN
2027 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2028 p_init_msg_list,
2029 '_PVT',
2030 x_return_status);
2031 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2032 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2033 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2034 RAISE OKC_API.G_EXCEPTION_ERROR;
2035 END IF;
2036 DELETE FROM OKC_CLASSES_B
2037 WHERE CODE = l_cls_rec.code;
2038
2039 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2040 EXCEPTION
2041 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2042 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2043 (
2044 l_api_name,
2045 G_PKG_NAME,
2046 'OKC_API.G_RET_STS_ERROR',
2047 x_msg_count,
2048 x_msg_data,
2049 '_PVT'
2050 );
2051 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2052 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2053 (
2054 l_api_name,
2055 G_PKG_NAME,
2056 'OKC_API.G_RET_STS_UNEXP_ERROR',
2057 x_msg_count,
2058 x_msg_data,
2059 '_PVT'
2060 );
2061 WHEN OTHERS THEN
2062 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2063 (
2064 l_api_name,
2065 G_PKG_NAME,
2066 'OTHERS',
2067 x_msg_count,
2068 x_msg_data,
2069 '_PVT'
2070 );
2071 END delete_row;
2072 -----------------------------------
2073 -- delete_row for:OKC_CLASSES_TL --
2074 -----------------------------------
2075 PROCEDURE delete_row(
2076 p_init_msg_list IN VARCHAR2 ,
2077 x_return_status OUT NOCOPY VARCHAR2,
2078 x_msg_count OUT NOCOPY NUMBER,
2079 x_msg_data OUT NOCOPY VARCHAR2,
2080 p_okc_classes_tl_rec IN okc_classes_tl_rec_type) IS
2081
2082 l_api_version CONSTANT NUMBER := 1;
2083 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2084 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2085 l_okc_classes_tl_rec okc_classes_tl_rec_type:= p_okc_classes_tl_rec;
2086 l_row_notfound BOOLEAN := TRUE;
2087 ---------------------------------------
2091 p_okc_classes_tl_rec IN okc_classes_tl_rec_type,
2088 -- Set_Attributes for:OKC_CLASSES_TL --
2089 ---------------------------------------
2090 FUNCTION Set_Attributes (
2092 x_okc_classes_tl_rec OUT NOCOPY okc_classes_tl_rec_type
2093 ) RETURN VARCHAR2 IS
2094 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2095 BEGIN
2096 x_okc_classes_tl_rec := p_okc_classes_tl_rec;
2097 x_okc_classes_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2098 RETURN(l_return_status);
2099 END Set_Attributes;
2100 BEGIN
2101 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2102 p_init_msg_list,
2103 '_PVT',
2104 x_return_status);
2105 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2106 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2107 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2108 RAISE OKC_API.G_EXCEPTION_ERROR;
2109 END IF;
2110 --- Setting item attributes
2111 l_return_status := Set_Attributes(
2112 p_okc_classes_tl_rec, -- IN
2113 l_okc_classes_tl_rec); -- OUT
2114 --- If any errors happen abort API
2115 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2116 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2117 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2118 RAISE OKC_API.G_EXCEPTION_ERROR;
2119 END IF;
2120 DELETE FROM OKC_CLASSES_TL
2121 WHERE CODE = l_okc_classes_tl_rec.code;
2122
2123 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2124 EXCEPTION
2125 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2126 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2127 (
2128 l_api_name,
2129 G_PKG_NAME,
2130 'OKC_API.G_RET_STS_ERROR',
2131 x_msg_count,
2132 x_msg_data,
2133 '_PVT'
2134 );
2135 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2136 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2137 (
2138 l_api_name,
2139 G_PKG_NAME,
2140 'OKC_API.G_RET_STS_UNEXP_ERROR',
2141 x_msg_count,
2142 x_msg_data,
2143 '_PVT'
2144 );
2145 WHEN OTHERS THEN
2146 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2147 (
2148 l_api_name,
2149 G_PKG_NAME,
2150 'OTHERS',
2151 x_msg_count,
2152 x_msg_data,
2153 '_PVT'
2154 );
2155 END delete_row;
2156 ----------------------------------
2157 -- delete_row for:OKC_CLASSES_V --
2158 ----------------------------------
2159 PROCEDURE delete_row(
2160 p_api_version IN NUMBER,
2161 p_init_msg_list IN VARCHAR2 ,
2162 x_return_status OUT NOCOPY VARCHAR2,
2163 x_msg_count OUT NOCOPY NUMBER,
2164 x_msg_data OUT NOCOPY VARCHAR2,
2165 p_clsv_rec IN clsv_rec_type) IS
2166
2167 l_api_version CONSTANT NUMBER := 1;
2168 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2169 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2170 l_clsv_rec clsv_rec_type := p_clsv_rec;
2171 l_okc_classes_tl_rec okc_classes_tl_rec_type;
2172 l_cls_rec cls_rec_type;
2173 BEGIN
2174 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2175 G_PKG_NAME,
2176 p_init_msg_list,
2177 l_api_version,
2178 p_api_version,
2179 '_PVT',
2180 x_return_status);
2181 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2182 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2183 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2184 RAISE OKC_API.G_EXCEPTION_ERROR;
2185 END IF;
2186 --------------------------------------
2187 -- Move VIEW record to "Child" records
2188 --------------------------------------
2189 migrate(l_clsv_rec, l_okc_classes_tl_rec);
2190 migrate(l_clsv_rec, l_cls_rec);
2191 --------------------------------------------
2192 -- Call the DELETE_ROW for each child record
2193 --------------------------------------------
2194 delete_row(
2195 p_init_msg_list,
2196 x_return_status,
2197 x_msg_count,
2198 x_msg_data,
2199 l_okc_classes_tl_rec
2200 );
2201 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2202 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2203 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2204 RAISE OKC_API.G_EXCEPTION_ERROR;
2205 END IF;
2206 delete_row(
2207 p_init_msg_list,
2208 x_return_status,
2209 x_msg_count,
2210 x_msg_data,
2211 l_cls_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;
2220 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2217 END IF;
2218 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2219 EXCEPTION
2221 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2222 (
2223 l_api_name,
2224 G_PKG_NAME,
2225 'OKC_API.G_RET_STS_ERROR',
2226 x_msg_count,
2227 x_msg_data,
2228 '_PVT'
2229 );
2230 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2231 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2232 (
2233 l_api_name,
2234 G_PKG_NAME,
2235 'OKC_API.G_RET_STS_UNEXP_ERROR',
2236 x_msg_count,
2237 x_msg_data,
2238 '_PVT'
2239 );
2240 WHEN OTHERS THEN
2241 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2242 (
2243 l_api_name,
2244 G_PKG_NAME,
2245 'OTHERS',
2246 x_msg_count,
2247 x_msg_data,
2248 '_PVT'
2249 );
2250 END delete_row;
2251 ----------------------------------------
2252 -- PL/SQL TBL delete_row for:CLSV_TBL --
2253 ----------------------------------------
2254 PROCEDURE delete_row(
2255 p_api_version IN NUMBER,
2256 p_init_msg_list IN VARCHAR2 ,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_msg_count OUT NOCOPY NUMBER,
2259 x_msg_data OUT NOCOPY VARCHAR2,
2260 p_clsv_tbl IN clsv_tbl_type) IS
2261
2262 l_api_version CONSTANT NUMBER := 1;
2263 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2264 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2265 i NUMBER := 0;
2266 BEGIN
2267 OKC_API.init_msg_list(p_init_msg_list);
2268 -- Make sure PL/SQL table has records in it before passing
2269 IF (p_clsv_tbl.COUNT > 0) THEN
2270 i := p_clsv_tbl.FIRST;
2271 LOOP
2272 delete_row (
2273 p_api_version => p_api_version,
2274 p_init_msg_list => OKC_API.G_FALSE,
2275 x_return_status => x_return_status,
2276 x_msg_count => x_msg_count,
2277 x_msg_data => x_msg_data,
2278 p_clsv_rec => p_clsv_tbl(i));
2279 EXIT WHEN (i = p_clsv_tbl.LAST);
2280 i := p_clsv_tbl.NEXT(i);
2281 END LOOP;
2282 END IF;
2283 EXCEPTION
2284 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2285 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2286 (
2287 l_api_name,
2288 G_PKG_NAME,
2289 'OKC_API.G_RET_STS_ERROR',
2290 x_msg_count,
2291 x_msg_data,
2292 '_PVT'
2293 );
2294 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2295 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2296 (
2297 l_api_name,
2298 G_PKG_NAME,
2299 'OKC_API.G_RET_STS_UNEXP_ERROR',
2300 x_msg_count,
2301 x_msg_data,
2302 '_PVT'
2303 );
2304 WHEN OTHERS THEN
2305 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2306 (
2307 l_api_name,
2308 G_PKG_NAME,
2309 'OTHERS',
2310 x_msg_count,
2311 x_msg_data,
2312 '_PVT'
2313 );
2314 END delete_row;
2315 END OKC_CLS_PVT;