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