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