[Home] [Help]
PACKAGE BODY: APPS.OKC_OCE_PVT
Source
1 PACKAGE BODY OKC_OCE_PVT AS
2 /* $Header: OKCSOCEB.pls 120.0 2005/05/30 04:11:08 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- Define a local variable to get the value of USERENV('LANG')
7 ---------------------------------------------------------------------------
8 l_lang VARCHAR2(12) := OKC_UTIL.get_userenv_lang;
9
10 ---------------------------------------------------------------------------
11 -- FUNCTION get_seq_id
12 ---------------------------------------------------------------------------
13 FUNCTION get_seq_id RETURN NUMBER IS
14 BEGIN
15 RETURN(okc_p_util.raw_to_number(sys_guid()));
16 END get_seq_id;
17
18 ---------------------------------------------------------------------------
19 -- PROCEDURE qc
20 ---------------------------------------------------------------------------
21 PROCEDURE qc IS
22 BEGIN
23 null;
24 END qc;
25
26 ---------------------------------------------------------------------------
27 -- PROCEDURE change_version
28 ---------------------------------------------------------------------------
29 PROCEDURE change_version IS
30 BEGIN
31 null;
32 END change_version;
33
34 ---------------------------------------------------------------------------
35 -- PROCEDURE api_copy
36 ---------------------------------------------------------------------------
37 PROCEDURE api_copy IS
38 BEGIN
39 null;
40 END api_copy;
41
42 ---------------------------------------------------------------------------
43 -- PROCEDURE add_language
44 ---------------------------------------------------------------------------
45 PROCEDURE add_language IS
46 BEGIN
47 DELETE FROM OKC_OUTCOMES_TL T
48 WHERE NOT EXISTS (
49 SELECT NULL
50 FROM OKC_OUTCOMES_B B
51 WHERE B.ID = T.ID
52 );
53
54 UPDATE OKC_OUTCOMES_TL T SET (
55 COMMENTS) = (SELECT
56 B.COMMENTS
57 FROM OKC_OUTCOMES_TL B
58 WHERE B.ID = T.ID
59 AND B.LANGUAGE = T.SOURCE_LANG)
60 WHERE (
61 T.ID,
62 T.LANGUAGE)
63 IN (SELECT
64 SUBT.ID,
65 SUBT.LANGUAGE
66 FROM OKC_OUTCOMES_TL SUBB, OKC_OUTCOMES_TL SUBT
67 WHERE SUBB.ID = SUBT.ID
68 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
69 AND (SUBB.COMMENTS <> SUBT.COMMENTS
70 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
71 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
72 ));
73
74 INSERT INTO OKC_OUTCOMES_TL (
75 ID,
76 LANGUAGE,
77 SOURCE_LANG,
78 SFWT_FLAG,
79 COMMENTS,
80 CREATED_BY,
81 CREATION_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATE_LOGIN)
85 SELECT
86 B.ID,
87 L.LANGUAGE_CODE,
88 B.SOURCE_LANG,
89 B.SFWT_FLAG,
90 B.COMMENTS,
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_OUTCOMES_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_OUTCOMES_TL T
102 WHERE T.ID = B.ID
103 AND T.LANGUAGE = L.LANGUAGE_CODE
104 );
105 DELETE FROM OKC_OUTCOMES_TLH T
106 WHERE NOT EXISTS (
107 SELECT NULL
108 FROM OKC_OUTCOMES_BH B
109 WHERE B.ID = T.ID
110 AND B.MAJOR_VERSION = T.MAJOR_VERSION
111 );
112
113 UPDATE OKC_OUTCOMES_TLH T SET (
114 COMMENTS) = (SELECT
115 B.COMMENTS
116 FROM OKC_OUTCOMES_TLH B
117 WHERE B.ID = T.ID
118 AND B.MAJOR_VERSION = T.MAJOR_VERSION
119 AND B.LANGUAGE = T.SOURCE_LANG)
120 WHERE (
121 T.ID,
122 T.MAJOR_VERSION,
123 T.LANGUAGE)
124 IN (SELECT
125 SUBT.ID,
126 SUBT.MAJOR_VERSION,
127 SUBT.LANGUAGE
128 FROM OKC_OUTCOMES_TLH SUBB, OKC_OUTCOMES_TLH SUBT
129 WHERE SUBB.ID = SUBT.ID
130 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
131 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
132 AND (SUBB.COMMENTS <> SUBT.COMMENTS
133 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
134 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
135 ));
136
137 INSERT INTO OKC_OUTCOMES_TLH (
138 ID,
139 LANGUAGE,
140 MAJOR_VERSION,
141 SOURCE_LANG,
142 SFWT_FLAG,
143 COMMENTS,
144 CREATED_BY,
145 CREATION_DATE,
146 LAST_UPDATED_BY,
147 LAST_UPDATE_DATE,
148 LAST_UPDATE_LOGIN)
149 SELECT
150 B.ID,
151 L.LANGUAGE_CODE,
152 B.MAJOR_VERSION,
153 B.SOURCE_LANG,
154 B.SFWT_FLAG,
155 B.COMMENTS,
156 B.CREATED_BY,
157 B.CREATION_DATE,
158 B.LAST_UPDATED_BY,
159 B.LAST_UPDATE_DATE,
160 B.LAST_UPDATE_LOGIN
161 FROM OKC_OUTCOMES_TLH B, FND_LANGUAGES L
162 WHERE L.INSTALLED_FLAG IN ('I', 'B')
163 AND B.LANGUAGE = USERENV('LANG')
164 AND NOT EXISTS(
165 SELECT NULL
166 FROM OKC_OUTCOMES_TLH T
167 WHERE T.ID = B.ID
168 AND T.MAJOR_VERSION = B.MAJOR_VERSION
169 AND T.LANGUAGE = L.LANGUAGE_CODE
170 );
171
172
173 END add_language;
174
175 ---------------------------------------------------------------------------
176 -- FUNCTION get_rec for: OKC_OUTCOMES_B
177 ---------------------------------------------------------------------------
178 FUNCTION get_rec (
179 p_oce_rec IN oce_rec_type,
180 x_no_data_found OUT NOCOPY BOOLEAN
181 ) RETURN oce_rec_type IS
182 CURSOR okc_outcomes_b_pk_csr (p_id IN NUMBER) IS
183 SELECT
184 ID,
185 PDF_ID,
186 CNH_ID,
187 DNZ_CHR_ID,
188 SUCCESS_resource_ID,
189 FAILURE_resource_ID,
190 ENABLED_YN,
191 OBJECT_VERSION_NUMBER,
192 CREATED_BY,
193 CREATION_DATE,
194 LAST_UPDATED_BY,
195 LAST_UPDATE_DATE,
196 LAST_UPDATE_LOGIN,
197 ATTRIBUTE_CATEGORY,
198 ATTRIBUTE1,
199 ATTRIBUTE2,
200 ATTRIBUTE3,
201 ATTRIBUTE4,
202 ATTRIBUTE5,
203 ATTRIBUTE6,
204 ATTRIBUTE7,
205 ATTRIBUTE8,
206 ATTRIBUTE9,
207 ATTRIBUTE10,
208 ATTRIBUTE11,
209 ATTRIBUTE12,
210 ATTRIBUTE13,
211 ATTRIBUTE14,
212 ATTRIBUTE15,
213 APPLICATION_ID,
214 SEEDED_FLAG
215 FROM Okc_Outcomes_B
216 WHERE okc_outcomes_b.id = p_id;
217 l_okc_outcomes_b_pk okc_outcomes_b_pk_csr%ROWTYPE;
218 l_oce_rec oce_rec_type;
219 BEGIN
220 x_no_data_found := TRUE;
221 -- Get current database values
222 OPEN okc_outcomes_b_pk_csr (p_oce_rec.id);
223 FETCH okc_outcomes_b_pk_csr INTO
224 l_oce_rec.ID,
225 l_oce_rec.PDF_ID,
226 l_oce_rec.CNH_ID,
227 l_oce_rec.DNZ_CHR_ID,
228 l_oce_rec.SUCCESS_resource_ID,
229 l_oce_rec.FAILURE_resource_ID,
230 l_oce_rec.ENABLED_YN,
231 l_oce_rec.OBJECT_VERSION_NUMBER,
232 l_oce_rec.CREATED_BY,
233 l_oce_rec.CREATION_DATE,
234 l_oce_rec.LAST_UPDATED_BY,
235 l_oce_rec.LAST_UPDATE_DATE,
236 l_oce_rec.LAST_UPDATE_LOGIN,
237 l_oce_rec.ATTRIBUTE_CATEGORY,
238 l_oce_rec.ATTRIBUTE1,
239 l_oce_rec.ATTRIBUTE2,
240 l_oce_rec.ATTRIBUTE3,
241 l_oce_rec.ATTRIBUTE4,
242 l_oce_rec.ATTRIBUTE5,
243 l_oce_rec.ATTRIBUTE6,
244 l_oce_rec.ATTRIBUTE7,
245 l_oce_rec.ATTRIBUTE8,
246 l_oce_rec.ATTRIBUTE9,
247 l_oce_rec.ATTRIBUTE10,
248 l_oce_rec.ATTRIBUTE11,
249 l_oce_rec.ATTRIBUTE12,
250 l_oce_rec.ATTRIBUTE13,
251 l_oce_rec.ATTRIBUTE14,
252 l_oce_rec.ATTRIBUTE15,
253 l_oce_rec.APPLICATION_ID,
254 l_oce_rec.SEEDED_FLAG;
255 x_no_data_found := okc_outcomes_b_pk_csr%NOTFOUND;
256 CLOSE okc_outcomes_b_pk_csr;
257 RETURN(l_oce_rec);
258 END get_rec;
259
260 FUNCTION get_rec (
261 p_oce_rec IN oce_rec_type
262 ) RETURN oce_rec_type IS
263 l_row_notfound BOOLEAN := TRUE;
264 BEGIN
265 RETURN(get_rec(p_oce_rec, l_row_notfound));
266 END get_rec;
267 ---------------------------------------------------------------------------
268 -- FUNCTION get_rec for: OKC_OUTCOMES_TL
269 ---------------------------------------------------------------------------
270 FUNCTION get_rec (
271 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
272 x_no_data_found OUT NOCOPY BOOLEAN
273 ) RETURN okc_outcomes_tl_rec_type IS
274 CURSOR okc_outcomes_tl_pk_csr (p_id IN NUMBER,
275 p_language IN VARCHAR2) IS
276 SELECT
277 ID,
278 LANGUAGE,
279 SOURCE_LANG,
280 SFWT_FLAG,
281 COMMENTS,
282 CREATED_BY,
283 CREATION_DATE,
284 LAST_UPDATED_BY,
285 LAST_UPDATE_DATE,
286 LAST_UPDATE_LOGIN
287 FROM Okc_Outcomes_Tl
288 WHERE okc_outcomes_tl.id = p_id
289 AND okc_outcomes_tl.language = p_language;
290 l_okc_outcomes_tl_pk okc_outcomes_tl_pk_csr%ROWTYPE;
291 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
292 BEGIN
293 x_no_data_found := TRUE;
294 -- Get current database values
295 OPEN okc_outcomes_tl_pk_csr (p_okc_outcomes_tl_rec.id,
296 p_okc_outcomes_tl_rec.language);
297 FETCH okc_outcomes_tl_pk_csr INTO
298 l_okc_outcomes_tl_rec.ID,
299 l_okc_outcomes_tl_rec.LANGUAGE,
300 l_okc_outcomes_tl_rec.SOURCE_LANG,
301 l_okc_outcomes_tl_rec.SFWT_FLAG,
302 l_okc_outcomes_tl_rec.COMMENTS,
303 l_okc_outcomes_tl_rec.CREATED_BY,
304 l_okc_outcomes_tl_rec.CREATION_DATE,
305 l_okc_outcomes_tl_rec.LAST_UPDATED_BY,
306 l_okc_outcomes_tl_rec.LAST_UPDATE_DATE,
307 l_okc_outcomes_tl_rec.LAST_UPDATE_LOGIN;
308 x_no_data_found := okc_outcomes_tl_pk_csr%NOTFOUND;
309 CLOSE okc_outcomes_tl_pk_csr;
310 RETURN(l_okc_outcomes_tl_rec);
311 END get_rec;
312
313 FUNCTION get_rec (
314 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type
315 ) RETURN okc_outcomes_tl_rec_type IS
316 l_row_notfound BOOLEAN := TRUE;
317 BEGIN
318 RETURN(get_rec(p_okc_outcomes_tl_rec, l_row_notfound));
319 END get_rec;
320 ---------------------------------------------------------------------------
321 -- FUNCTION get_rec for: OKC_OUTCOMES_V
322 ---------------------------------------------------------------------------
323 FUNCTION get_rec (
324 p_ocev_rec IN ocev_rec_type,
325 x_no_data_found OUT NOCOPY BOOLEAN
326 ) RETURN ocev_rec_type IS
327 CURSOR okc_ocev_pk_csr (p_id IN NUMBER) IS
328 SELECT
329 ID,
330 OBJECT_VERSION_NUMBER,
331 SFWT_FLAG,
332 PDF_ID,
333 CNH_ID,
334 DNZ_CHR_ID,
335 SUCCESS_resource_ID,
336 FAILURE_resource_ID,
337 ENABLED_YN,
338 COMMENTS,
339 APPLICATION_ID,
340 SEEDED_FLAG,
341 ATTRIBUTE_CATEGORY,
342 ATTRIBUTE1,
343 ATTRIBUTE2,
344 ATTRIBUTE3,
345 ATTRIBUTE4,
346 ATTRIBUTE5,
347 ATTRIBUTE6,
348 ATTRIBUTE7,
349 ATTRIBUTE8,
350 ATTRIBUTE9,
351 ATTRIBUTE10,
352 ATTRIBUTE11,
353 ATTRIBUTE12,
354 ATTRIBUTE13,
355 ATTRIBUTE14,
356 ATTRIBUTE15,
357 CREATED_BY,
358 CREATION_DATE,
359 LAST_UPDATED_BY,
360 LAST_UPDATE_DATE,
361 LAST_UPDATE_LOGIN
362 FROM Okc_Outcomes_V
363 WHERE okc_outcomes_v.id = p_id;
364 l_okc_ocev_pk okc_ocev_pk_csr%ROWTYPE;
365 l_ocev_rec ocev_rec_type;
366 BEGIN
367 x_no_data_found := TRUE;
368 -- Get current database values
369 OPEN okc_ocev_pk_csr (p_ocev_rec.id);
370 FETCH okc_ocev_pk_csr INTO
371 l_ocev_rec.ID,
372 l_ocev_rec.OBJECT_VERSION_NUMBER,
373 l_ocev_rec.SFWT_FLAG,
374 l_ocev_rec.PDF_ID,
375 l_ocev_rec.CNH_ID,
376 l_ocev_rec.DNZ_CHR_ID,
377 l_ocev_rec.SUCCESS_resource_ID,
378 l_ocev_rec.FAILURE_resource_ID,
379 l_ocev_rec.ENABLED_YN,
380 l_ocev_rec.COMMENTS,
381 l_ocev_rec.APPLICATION_ID,
382 l_ocev_rec.SEEDED_FLAG,
383 l_ocev_rec.ATTRIBUTE_CATEGORY,
384 l_ocev_rec.ATTRIBUTE1,
385 l_ocev_rec.ATTRIBUTE2,
386 l_ocev_rec.ATTRIBUTE3,
387 l_ocev_rec.ATTRIBUTE4,
388 l_ocev_rec.ATTRIBUTE5,
389 l_ocev_rec.ATTRIBUTE6,
390 l_ocev_rec.ATTRIBUTE7,
391 l_ocev_rec.ATTRIBUTE8,
392 l_ocev_rec.ATTRIBUTE9,
393 l_ocev_rec.ATTRIBUTE10,
394 l_ocev_rec.ATTRIBUTE11,
395 l_ocev_rec.ATTRIBUTE12,
396 l_ocev_rec.ATTRIBUTE13,
397 l_ocev_rec.ATTRIBUTE14,
398 l_ocev_rec.ATTRIBUTE15,
399 l_ocev_rec.CREATED_BY,
400 l_ocev_rec.CREATION_DATE,
401 l_ocev_rec.LAST_UPDATED_BY,
402 l_ocev_rec.LAST_UPDATE_DATE,
403 l_ocev_rec.LAST_UPDATE_LOGIN;
404 x_no_data_found := okc_ocev_pk_csr%NOTFOUND;
405 CLOSE okc_ocev_pk_csr;
406 RETURN(l_ocev_rec);
407 END get_rec;
408
409 FUNCTION get_rec (
410 p_ocev_rec IN ocev_rec_type
411 ) RETURN ocev_rec_type IS
412 l_row_notfound BOOLEAN := TRUE;
413 BEGIN
414 RETURN(get_rec(p_ocev_rec, l_row_notfound));
415 END get_rec;
416
417 ----------------------------------------------------
418 -- FUNCTION null_out_defaults for: OKC_OUTCOMES_V --
419 ----------------------------------------------------
420 FUNCTION null_out_defaults (
421 p_ocev_rec IN ocev_rec_type
422 ) RETURN ocev_rec_type IS
423 l_ocev_rec ocev_rec_type := p_ocev_rec;
424 BEGIN
425 IF (l_ocev_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
426 l_ocev_rec.object_version_number := NULL;
427 END IF;
428 IF (l_ocev_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
429 l_ocev_rec.sfwt_flag := NULL;
430 END IF;
431 IF (l_ocev_rec.pdf_id = OKC_API.G_MISS_NUM) THEN
432 l_ocev_rec.pdf_id := NULL;
433 END IF;
434 IF (l_ocev_rec.cnh_id = OKC_API.G_MISS_NUM) THEN
435 l_ocev_rec.cnh_id := NULL;
436 END IF;
437 IF (l_ocev_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
438 l_ocev_rec.dnz_chr_id := NULL;
439 END IF;
440 IF (l_ocev_rec.success_resource_id = OKC_API.G_MISS_NUM) THEN
441 l_ocev_rec.success_resource_id := NULL;
442 END IF;
443 IF (l_ocev_rec.failure_resource_id = OKC_API.G_MISS_NUM) THEN
444 l_ocev_rec.failure_resource_id := NULL;
445 END IF;
446 IF (l_ocev_rec.enabled_yn = OKC_API.G_MISS_CHAR) THEN
447 l_ocev_rec.enabled_yn := NULL;
448 END IF;
449 IF (l_ocev_rec.comments = OKC_API.G_MISS_CHAR) THEN
450 l_ocev_rec.comments := NULL;
451 END IF;
452 IF (l_ocev_rec.application_id = OKC_API.G_MISS_NUM) THEN
453 l_ocev_rec.application_id := NULL;
454 END IF;
455 IF (l_ocev_rec.seeded_flag = OKC_API.G_MISS_CHAR) THEN
456 l_ocev_rec.seeded_flag := NULL;
457 END IF;
458 IF (l_ocev_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
459 l_ocev_rec.attribute_category := NULL;
460 END IF;
461 IF (l_ocev_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
462 l_ocev_rec.attribute1 := NULL;
463 END IF;
464 IF (l_ocev_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
465 l_ocev_rec.attribute2 := NULL;
466 END IF;
467 IF (l_ocev_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
468 l_ocev_rec.attribute3 := NULL;
469 END IF;
470 IF (l_ocev_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
471 l_ocev_rec.attribute4 := NULL;
472 END IF;
473 IF (l_ocev_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
474 l_ocev_rec.attribute5 := NULL;
475 END IF;
476 IF (l_ocev_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
477 l_ocev_rec.attribute6 := NULL;
478 END IF;
479 IF (l_ocev_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
480 l_ocev_rec.attribute7 := NULL;
481 END IF;
482 IF (l_ocev_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
483 l_ocev_rec.attribute8 := NULL;
484 END IF;
485 IF (l_ocev_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
486 l_ocev_rec.attribute9 := NULL;
487 END IF;
488 IF (l_ocev_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
489 l_ocev_rec.attribute10 := NULL;
490 END IF;
491 IF (l_ocev_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
492 l_ocev_rec.attribute11 := NULL;
493 END IF;
494 IF (l_ocev_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
495 l_ocev_rec.attribute12 := NULL;
496 END IF;
497 IF (l_ocev_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
498 l_ocev_rec.attribute13 := NULL;
499 END IF;
500 IF (l_ocev_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
501 l_ocev_rec.attribute14 := NULL;
502 END IF;
503 IF (l_ocev_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
504 l_ocev_rec.attribute15 := NULL;
505 END IF;
506 IF (l_ocev_rec.created_by = OKC_API.G_MISS_NUM) THEN
507 l_ocev_rec.created_by := NULL;
508 END IF;
509 IF (l_ocev_rec.creation_date = OKC_API.G_MISS_DATE) THEN
510 l_ocev_rec.creation_date := NULL;
511 END IF;
512 IF (l_ocev_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
513 l_ocev_rec.last_updated_by := NULL;
514 END IF;
515 IF (l_ocev_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
516 l_ocev_rec.last_update_date := NULL;
517 END IF;
518 IF (l_ocev_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
519 l_ocev_rec.last_update_login := NULL;
520 END IF;
521 RETURN(l_ocev_rec);
522 END null_out_defaults;
523
524 /********* Commented out nocopy generated code in favor of hand written code ****
525 ---------------------------------------------------------------------------
526 -- PROCEDURE Validate_Attributes
527 ---------------------------------------------------------------------------
528 --------------------------------------------
529 -- Validate_Attributes for:OKC_OUTCOMES_V --
530 --------------------------------------------
531 FUNCTION Validate_Attributes (
532 p_ocev_rec IN ocev_rec_type
533 ) RETURN VARCHAR2 IS
534 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
535 BEGIN
536 IF p_ocev_rec.id = OKC_API.G_MISS_NUM OR
537 p_ocev_rec.id IS NULL
538 THEN
539 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
540 l_return_status := OKC_API.G_RET_STS_ERROR;
541 ELSIF p_ocev_rec.object_version_number = OKC_API.G_MISS_NUM OR
542 p_ocev_rec.object_version_number IS NULL
543 THEN
544 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
545 l_return_status := OKC_API.G_RET_STS_ERROR;
546 ELSIF p_ocev_rec.pdf_id = OKC_API.G_MISS_NUM OR
547 p_ocev_rec.pdf_id IS NULL
548 THEN
549 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'pdf_id');
550 l_return_status := OKC_API.G_RET_STS_ERROR;
551 ELSIF p_ocev_rec.cnh_id = OKC_API.G_MISS_NUM OR
552 p_ocev_rec.cnh_id IS NULL
553 THEN
554 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cnh_id');
555 l_return_status := OKC_API.G_RET_STS_ERROR;
556 ELSIF p_ocev_rec.enabled_yn = OKC_API.G_MISS_CHAR OR
557 p_ocev_rec.enabled_yn IS NULL
558 THEN
559 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'enabled_yn');
560 l_return_status := OKC_API.G_RET_STS_ERROR;
561 END IF;
562 RETURN(l_return_status);
563 END Validate_Attributes;
564
565 ---------------------------------------------------------------------------
566 -- PROCEDURE Validate_Record
567 ---------------------------------------------------------------------------
568 ----------------------------------------
569 -- Validate_Record for:OKC_OUTCOMES_V --
570 ----------------------------------------
571 FUNCTION Validate_Record (
572 p_ocev_rec IN ocev_rec_type
573 ) RETURN VARCHAR2 IS
574 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
575 ------------------------------------
576 -- FUNCTION validate_foreign_keys --
577 ------------------------------------
578 FUNCTION validate_foreign_keys (
579 p_ocev_rec IN ocev_rec_type
580 ) RETURN VARCHAR2 IS
581 item_not_found_error EXCEPTION;
582 CURSOR okc_rulv_pk_csr (p_id IN NUMBER) IS
583 SELECT
584 ID,
585 OBJECT_VERSION_NUMBER,
586 SFWT_FLAG,
587 OBJECT1_ID1,
588 OBJECT2_ID1,
589 OBJECT3_ID1,
590 OBJECT1_ID2,
591 OBJECT2_ID2,
592 OBJECT3_ID2,
593 JTOT_OBJECT1_CODE,
594 JTOT_OBJECT2_CODE,
595 JTOT_OBJECT3_CODE,
596 DNZ_CHR_ID,
597 RGP_ID,
598 PRIORITY,
599 STD_TEMPLATE_YN,
600 COMMENTS,
601 WARN_YN,
602 ATTRIBUTE_CATEGORY,
603 ATTRIBUTE1,
604 ATTRIBUTE2,
605 ATTRIBUTE3,
606 ATTRIBUTE4,
607 ATTRIBUTE5,
608 ATTRIBUTE6,
609 ATTRIBUTE7,
610 ATTRIBUTE8,
611 ATTRIBUTE9,
612 ATTRIBUTE10,
613 ATTRIBUTE11,
614 ATTRIBUTE12,
615 ATTRIBUTE13,
616 ATTRIBUTE14,
617 ATTRIBUTE15,
618 CREATED_BY,
619 CREATION_DATE,
620 LAST_UPDATED_BY,
621 LAST_UPDATE_DATE,
622 LAST_UPDATE_LOGIN,
623 TEXT,
624 RULE_INFORMATION_CATEGORY,
625 RULE_INFORMATION1,
626 RULE_INFORMATION2,
627 RULE_INFORMATION3,
628 RULE_INFORMATION4,
629 RULE_INFORMATION5,
630 RULE_INFORMATION6,
631 RULE_INFORMATION7,
632 RULE_INFORMATION8,
633 RULE_INFORMATION9,
634 RULE_INFORMATION10,
635 RULE_INFORMATION11,
636 RULE_INFORMATION12,
637 RULE_INFORMATION13,
638 RULE_INFORMATION14,
639 RULE_INFORMATION15
640 FROM Okc_Rules_V
641 WHERE okc_rules_v.id = p_id;
642 l_okc_rulv_pk okc_rulv_pk_csr%ROWTYPE;
643 CURSOR okc_pdfv_pk_csr (p_id IN NUMBER) IS
644 SELECT
645 ID,
646 OBJECT_VERSION_NUMBER,
647 SFWT_FLAG,
648 DESCRIPTION,
649 SHORT_DESCRIPTION,
650 COMMENTS,
651 USAGE,
652 NAME,
653 WF_NAME,
654 WF_PROCESS_NAME,
655 PROCEDURE_NAME,
656 PACKAGE_NAME,
657 PDF_TYPE,
658 APPLICATION_ID,
659 SEEDED_FLAG,
660 ATTRIBUTE_CATEGORY,
661 ATTRIBUTE1,
662 ATTRIBUTE2,
663 ATTRIBUTE3,
664 ATTRIBUTE4,
665 ATTRIBUTE5,
666 ATTRIBUTE6,
667 ATTRIBUTE7,
668 ATTRIBUTE8,
669 ATTRIBUTE9,
670 ATTRIBUTE10,
671 ATTRIBUTE11,
672 ATTRIBUTE12,
673 ATTRIBUTE13,
674 ATTRIBUTE14,
675 ATTRIBUTE15,
676 CREATED_BY,
677 BEGIN_DATE,
678 END_DATE,
679 CREATION_DATE,
680 LAST_UPDATED_BY,
681 LAST_UPDATE_DATE,
682 LAST_UPDATE_LOGIN
683 FROM Okc_Process_Defs_V
684 WHERE okc_process_defs_v.id = p_id;
685 l_okc_pdfv_pk okc_pdfv_pk_csr%ROWTYPE;
686 CURSOR okc_cnhv_pk_csr (p_id IN NUMBER) IS
687 SELECT
688 ID,
689 OBJECT_VERSION_NUMBER,
690 SFWT_FLAG,
691 ACN_ID,
692 COUNTER_GROUP_ID,
693 DESCRIPTION,
694 SHORT_DESCRIPTION,
695 COMMENTS,
696 ONE_TIME_YN,
697 NAME,
698 CONDITION_VALID_YN,
699 BEFORE_AFTER,
700 TRACKED_YN,
701 CNH_VARIANCE,
702 DNZ_CHR_ID,
703 TEMPLATE_YN,
704 DATE_ACTIVE,
705 OBJECT_ID,
706 DATE_INACTIVE,
707 JTOT_OBJECT_CODE,
708 TASK_OWNER_ID,
709 CNH_TYPE,
710 APPLICATION_ID,
711 SEEDED_FLAG,
712 ATTRIBUTE_CATEGORY,
713 ATTRIBUTE1,
714 ATTRIBUTE2,
715 ATTRIBUTE3,
716 ATTRIBUTE4,
717 ATTRIBUTE5,
718 ATTRIBUTE6,
719 ATTRIBUTE7,
720 ATTRIBUTE8,
721 ATTRIBUTE9,
722 ATTRIBUTE10,
723 ATTRIBUTE11,
724 ATTRIBUTE12,
725 ATTRIBUTE13,
726 ATTRIBUTE14,
727 ATTRIBUTE15,
728 CREATED_BY,
729 CREATION_DATE,
730 LAST_UPDATED_BY,
731 LAST_UPDATE_DATE,
732 LAST_UPDATE_LOGIN
733 FROM Okc_Condition_Headers_V
734 WHERE okc_condition_headers_v.id = p_id;
735 l_okc_cnhv_pk okc_cnhv_pk_csr%ROWTYPE;
736 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
737 l_row_notfound BOOLEAN := TRUE;
738 BEGIN
739 IF (p_ocev_rec.PDF_ID IS NOT NULL)
740 THEN
741 OPEN okc_pdfv_pk_csr(p_ocev_rec.PDF_ID);
742 FETCH okc_pdfv_pk_csr INTO l_okc_pdfv_pk;
743 l_row_notfound := okc_pdfv_pk_csr%NOTFOUND;
744 CLOSE okc_pdfv_pk_csr;
745 IF (l_row_notfound) THEN
746 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PDF_ID');
747 RAISE item_not_found_error;
748 END IF;
749 END IF;
750 IF (p_ocev_rec.CNH_ID IS NOT NULL)
751 THEN
752 OPEN okc_cnhv_pk_csr(p_ocev_rec.CNH_ID);
753 FETCH okc_cnhv_pk_csr INTO l_okc_cnhv_pk;
754 l_row_notfound := okc_cnhv_pk_csr%NOTFOUND;
755 CLOSE okc_cnhv_pk_csr;
756 IF (l_row_notfound) THEN
757 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CNH_ID');
758 RAISE item_not_found_error;
759 END IF;
760 END IF;
761 RETURN (l_return_status);
762 EXCEPTION
763 WHEN item_not_found_error THEN
764 l_return_status := OKC_API.G_RET_STS_ERROR;
765 RETURN (l_return_status);
766 END validate_foreign_keys;
767 BEGIN
768 l_return_status := validate_foreign_keys (p_ocev_rec);
769 RETURN (l_return_status);
770 END Validate_Record;
771
772 ***************** End Commented out nocopy generated code ***********************/
773 /**************** Begin Hand Written Code ********************************/
774
775 ---------------------------------------------------------------------------
776 -- PROCEDURE Validate_Attributes
777 ---------------------------------------------------------------------------
778 --------------------------------------------
779 -- Validate_Attributes for:OKC_OUTCOMES_V --
780 --------------------------------------------
781 -- Start of comments
782 -- Procedure Name : validate_object_version_number
783 -- Description : Check if object_version_number is not null
784 -- Business Rules :
785 -- Parameters :
786 -- Version : 1.0
787 -- End of comments
788
789 PROCEDURE validate_object_version_number(
790 x_return_status OUT NOCOPY VARCHAR2,
791 p_ocev_rec IN ocev_rec_type) IS
792 BEGIN
793 -- initialize return status
794 x_return_status := OKC_API.G_RET_STS_SUCCESS;
795
796 IF p_ocev_rec.object_version_number = OKC_API.G_MISS_NUM OR
797 p_ocev_rec.object_version_number IS NULL THEN
798 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
799 p_msg_name => g_required_value,
800 p_token1 => g_col_name_token,
801 p_token1_value => 'object_version_number');
802 x_return_status := OKC_API.G_RET_STS_ERROR;
803 raise G_EXCEPTION_HALT_VALIDATION;
804 ELSE
805 x_return_status := OKC_API.G_RET_STS_SUCCESS;
806 END IF;
807 EXCEPTION
808 when G_EXCEPTION_HALT_VALIDATION then
809 -- no processing necessary; validation can continue
810 -- with the next column
811 null;
812
813 when OTHERS then
814 -- store SQL error message on message stack for caller
815 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
816 p_msg_name => g_unexpected_error,
817 p_token1 => g_sqlcode_token,
818 p_token1_value => sqlcode,
819 p_token2 => g_sqlerrm_token,
820 p_token2_value => sqlerrm);
821
822 -- notify caller of an UNEXPECTED error
823 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
824 END validate_object_version_number;
825
826 -- Start of comments
827 -- Procedure Name : validate_sfwt_flag
828 -- Description : Checks if column SFWT_FLAG is 'Y' or 'N' only
829 -- Business Rules :
830 -- Parameters :
831 -- Version : 1.0
832 -- End of comments
833
834 PROCEDURE Validate_sfwt_flag(x_return_status OUT NOCOPY VARCHAR2
835 ,p_ocev_rec IN ocev_rec_type) IS
836 BEGIN
837 -- initialize return status
838 x_return_status := OKC_API.G_RET_STS_SUCCESS;
839
840 --Check if sfwt_flag is null
841 IF p_ocev_rec.sfwt_flag = OKC_API.G_MISS_CHAR OR
842 p_ocev_rec.sfwt_flag IS NULL THEN
843 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
844 p_msg_name => g_required_value,
845 p_token1 => g_col_name_token,
846 p_token1_value => 'sfwt_flag');
847 x_return_status := OKC_API.G_RET_STS_ERROR;
848 raise G_EXCEPTION_HALT_VALIDATION;
849 END IF;
850
851 --Check if the sfwt_flag is 'Y' or 'N'
852 IF p_ocev_rec.sfwt_flag NOT IN ('Y', 'N') THEN
853 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
854 p_msg_name => g_invalid_value,
855 p_token1 => g_col_name_token,
856 p_token1_value => 'sfwt_flag');
857 x_return_status := OKC_API.G_RET_STS_ERROR;
858 END IF;
859
860 --Check if the data is in upper case
861 IF p_ocev_rec.sfwt_flag <> UPPER(p_ocev_rec.sfwt_flag) THEN
862 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
863 p_msg_name => g_uppercase_required,
864 p_token1 => g_col_name_token,
865 p_token1_value => 'sfwt_flag');
866 x_return_status := OKC_API.G_RET_STS_ERROR;
867 END IF;
868 EXCEPTION
869 when G_EXCEPTION_HALT_VALIDATION then
870 -- no processing necessary; validation can continue
871 -- with the next column
872 null;
873
874 when OTHERS then
875 -- store SQL error message on message stack for caller
876 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
877 p_msg_name => g_unexpected_error,
878 p_token1 => g_sqlcode_token,
879 p_token1_value => sqlcode,
880 p_token2 => g_sqlerrm_token,
881 p_token2_value => sqlerrm);
882 -- notify caller of an UNEXPECTED error
883 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
884 END validate_sfwt_flag;
885
886 -- Start of comments
887 -- Procedure Name : validate_seeded_flag
888 -- Description : Checks if column SEEDED_FLAG is 'Y' or 'N' only
889 -- Version : 1.0
890 -- End of comments
891
892 PROCEDURE validate_seeded_flag(
893 x_return_status OUT NOCOPY VARCHAR2,
894 p_ocev_rec IN ocev_rec_type) IS
895 l_y VARCHAR2(1) := 'Y';
896 l_n VARCHAR2(1) := 'N';
897 BEGIN
898 -- initialize return status
899 x_return_status := OKC_API.G_RET_STS_SUCCESS;
900 -- Check if seeded_flag is Y or N
901 IF UPPER(p_ocev_rec.seeded_flag) NOT IN ('Y', 'N') THEN
902 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
903 p_msg_name => g_invalid_value,
904 p_token1 => g_col_name_token,
905 p_token1_value => 'seeded_flag');
906 x_return_status := OKC_API.G_RET_STS_ERROR;
907 raise G_EXCEPTION_HALT_VALIDATION;
908 END IF;
909
910 --Check if the data is in upper case
911 IF p_ocev_rec.seeded_flag <> UPPER(p_ocev_rec.seeded_flag) THEN
912 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
913 p_msg_name => g_uppercase_required,
914 p_token1 => g_col_name_token,
915 p_token1_value => 'seeded_flag');
916 x_return_status := OKC_API.G_RET_STS_ERROR;
917 raise G_EXCEPTION_HALT_VALIDATION;
918 END IF;
919 EXCEPTION
920 when G_EXCEPTION_HALT_VALIDATION then
921 -- no processing necessary; validation can continue
922 -- with the next column
923 null;
924
925 when OTHERS then
926 -- store SQL error message on message stack for caller
927 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
928 p_msg_name => g_unexpected_error,
929 p_token1 => g_sqlcode_token,
930 p_token1_value => sqlcode,
931 p_token2 => g_sqlerrm_token,
932 p_token2_value => sqlerrm);
933 -- notify caller of an UNEXPECTED error
934 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
935 END validate_seeded_flag;
936 -- Start of comments
937 -- Procedure Name : validate_application_id
938 -- Description : Checks if application_id exists in fnd_application
939 -- Version : 1.0
940 -- End of comments
941
942 PROCEDURE validate_application_id(
943 x_return_status OUT NOCOPY VARCHAR2,
944 p_ocev_rec IN ocev_rec_type) IS
945 Cursor application_id_cur(p_application_id IN NUMBER) IS
946 select '1'
947 from fnd_application
948 where application_id = p_application_id;
949 l_dummy VARCHAR2(1) := '?';
950 BEGIN
951 -- initialize return status
952 x_return_status := OKC_API.G_RET_STS_SUCCESS;
953
954 IF p_ocev_rec.application_id IS NOT NULL THEN
955 --Check if application id exists in the fnd_application or not
956 OPEN application_id_cur(p_ocev_rec.application_id);
957 FETCH application_id_cur INTO l_dummy;
958 CLOSE application_id_cur ;
959 IF l_dummy = '?' THEN
960 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
961 p_msg_name => g_invalid_value,
962 p_token1 => g_col_name_token,
963 p_token1_value => 'application_id');
964 x_return_status := OKC_API.G_RET_STS_ERROR;
965 raise G_EXCEPTION_HALT_VALIDATION;
966 END IF;
967 END IF;
968 EXCEPTION
969 when G_EXCEPTION_HALT_VALIDATION then
970 -- no processing necessary; validation can continue
971 -- with the next column
972 null;
973
974 when OTHERS then
975 -- store SQL error message on message stack for caller
976 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
977 p_msg_name => g_unexpected_error,
978 p_token1 => g_sqlcode_token,
979 p_token1_value => sqlcode,
980 p_token2 => g_sqlerrm_token,
981 p_token2_value => sqlerrm);
982 -- notify caller of an UNEXPECTED error
983 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
984 END validate_application_id;
985
986 --************************************************************************
987 -- Start of comments
988 -- Procedure Name : validate_success_resource_id
989 -- Description : Checks if success_resource_id exists in okx_resources
990 -- Version : 1.0
991 -- End of comments
992
993 PROCEDURE validate_success_resource_id(
994 x_return_status OUT NOCOPY VARCHAR2,
995 p_ocev_rec IN ocev_rec_type) IS
996 Cursor success_resource_id_cur(p_success_resource_id IN NUMBER) IS
997 select '1'
998 from okx_resources_v
999 where id1 = p_success_resource_id
1000 and resource_type = 'EMPLOYEE'
1001 and status = 'A';
1002 l_dummy VARCHAR2(1) := '?';
1003 BEGIN
1004 -- initialize return status
1005 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1006
1007 IF p_ocev_rec.success_resource_id IS NOT NULL THEN
1008 --Check if id exists in the okx_resources_v or not
1009 OPEN success_resource_id_cur(p_ocev_rec.success_resource_id);
1010 FETCH success_resource_id_cur INTO l_dummy;
1011 CLOSE success_resource_id_cur ;
1012 IF l_dummy = '?' THEN
1013 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1014 p_msg_name => g_invalid_value,
1015 p_token1 => g_col_name_token,
1016 p_token1_value => 'success_resource_id');
1017 x_return_status := OKC_API.G_RET_STS_ERROR;
1018 raise G_EXCEPTION_HALT_VALIDATION;
1019 END IF;
1020 END IF;
1021 EXCEPTION
1022 when G_EXCEPTION_HALT_VALIDATION then
1023 -- no processing necessary; validation can continue
1024 -- with the next column
1025 null;
1026
1027 when OTHERS then
1028 -- store SQL error message on message stack for caller
1029 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1030 p_msg_name => g_unexpected_error,
1031 p_token1 => g_sqlcode_token,
1032 p_token1_value => sqlcode,
1033 p_token2 => g_sqlerrm_token,
1034 p_token2_value => sqlerrm);
1035 -- notify caller of an UNEXPECTED error
1036 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1037 END validate_success_resource_id;
1038
1039
1040 -- Start of comments
1041 -- Procedure Name : validate_failure_resource_id
1042 -- Description : Checks if failure_resource_id exists in okx_resources
1043 -- Version : 1.0
1044 -- End of comments
1045
1046 PROCEDURE validate_failure_resource_id(
1047 x_return_status OUT NOCOPY VARCHAR2,
1048 p_ocev_rec IN ocev_rec_type) IS
1049 Cursor failure_resource_id_cur(p_failure_resource_id IN NUMBER) IS
1050 select '1'
1051 from okx_resources_v
1052 where id1 = p_failure_resource_id
1053 and resource_type = 'EMPLOYEE'
1054 and status = 'A';
1055 l_dummy VARCHAR2(1) := '?';
1056 BEGIN
1057 -- initialize return status
1058 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1059
1060 IF p_ocev_rec.failure_resource_id IS NOT NULL THEN
1061 --Check if id exists in the okx_resources_v or not
1062 OPEN failure_resource_id_cur(p_ocev_rec.failure_resource_id);
1063 FETCH failure_resource_id_cur INTO l_dummy;
1064 CLOSE failure_resource_id_cur ;
1065 IF l_dummy = '?' THEN
1066 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1067 p_msg_name => g_invalid_value,
1068 p_token1 => g_col_name_token,
1069 p_token1_value => 'failure_resource_id');
1070 x_return_status := OKC_API.G_RET_STS_ERROR;
1071 raise G_EXCEPTION_HALT_VALIDATION;
1072 END IF;
1073 END IF;
1074 EXCEPTION
1075 when G_EXCEPTION_HALT_VALIDATION then
1076 -- no processing necessary; validation can continue
1077 -- with the next column
1078 null;
1079
1080 when OTHERS then
1081 -- store SQL error message on message stack for caller
1082 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1083 p_msg_name => g_unexpected_error,
1084 p_token1 => g_sqlcode_token,
1085 p_token1_value => sqlcode,
1086 p_token2 => g_sqlerrm_token,
1087 p_token2_value => sqlerrm);
1088 -- notify caller of an UNEXPECTED error
1089 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1090 END validate_failure_resource_id;
1091
1092 --***********************************************************************
1093 -- Start of comments
1094 -- Procedure Name : validate_cnh_id
1095 -- Description : Check if cnh_id is valid
1096 -- Business Rules :
1097 -- Parameters :
1098 -- Version : 1.0
1099 -- End of comments
1100
1101 PROCEDURE validate_cnh_id(
1102 x_return_status OUT NOCOPY VARCHAR2,
1103 p_ocev_rec IN ocev_rec_type) IS
1104
1105 CURSOR okc_cnhv_pk_csr IS
1106 SELECT '1'
1107 FROM Okc_condition_headers_v
1108 WHERE okc_condition_headers_v.id = p_ocev_rec.cnh_id;
1109
1110 l_dummy VARCHAR2(1) := '?';
1111
1112 Begin
1113 -- initialize return status
1114 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1115
1116 -- Check if cnh_id is null
1117 IF p_ocev_rec.cnh_id = OKC_API.G_MISS_NUM OR p_ocev_rec.pdf_id IS NULL THEN
1118 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1119 p_msg_name => g_required_value,
1120 p_token1 => g_col_name_token,
1121 p_token1_value => 'cnh_id');
1122 x_return_status := OKC_API.G_RET_STS_ERROR;
1123 raise G_EXCEPTION_HALT_VALIDATION;
1124 END IF;
1125
1126 -- Enforce foreign key
1127 OPEN okc_cnhv_pk_csr;
1128 FETCH okc_cnhv_pk_csr INTO l_dummy;
1129 CLOSE okc_cnhv_pk_csr;
1130
1131 -- If l_dummy is still set to default, data was not found
1132 IF (l_dummy = '?') THEN
1133 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1134 p_msg_name => g_no_parent_record,
1135 p_token1 => g_col_name_token,
1136 p_token1_value => 'cnh_id',
1137 p_token2 => g_child_table_token,
1138 p_token2_value => 'OKC_OUTCOMES_V',
1139 p_token3 => g_parent_table_token,
1140 p_token3_value => 'OKC_CONDITION_HEADERS_V');
1141 x_return_status := OKC_API.G_RET_STS_ERROR;
1142 RAISE G_EXCEPTION_HALT_VALIDATION;
1143 END IF;
1144 EXCEPTION
1145 when G_EXCEPTION_HALT_VALIDATION then
1146 -- no processing necessary; validation can continue
1147 -- with the next column
1148 null;
1149
1150 -- verify that cursor was closed
1151 if okc_cnhv_pk_csr %ISOPEN then
1152 close okc_cnhv_pk_csr ;
1153 end if;
1154
1155 when OTHERS then
1156 -- store SQL error message on message stack for caller
1157 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1158 p_msg_name => g_unexpected_error,
1159 p_token1 => g_sqlcode_token,
1160 p_token1_value => sqlcode,
1161 p_token2 => g_sqlerrm_token,
1162 p_token2_value => sqlerrm);
1163
1164 -- notify caller of an UNEXPECTED error
1165 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1166 END validate_cnh_id;
1167
1168 -- Start of comments
1169 -- Procedure Name : validate_pdf_id
1170 -- Description : Check if pdf_id is null and enforce foreign key
1171 -- Business Rules :
1172 -- Parameters :
1173 -- Version : 1.0
1174 -- End of comments
1175
1176 PROCEDURE validate_pdf_id(
1177 x_return_status OUT NOCOPY VARCHAR2,
1178 p_ocev_rec IN ocev_rec_type) IS
1179
1180 CURSOR okc_pdfv_pk_csr IS
1181 SELECT '1'
1182 FROM Okc_Process_Defs_V
1183 WHERE okc_process_defs_v.id = p_ocev_rec.pdf_id;
1184
1185 CURSOR sync_flag_yn_csr(p_cnh_id IN NUMBER) IS
1186 Select acn.sync_allowed_yn
1187 from okc_condition_headers_v cnh, okc_actions_v acn
1188 where cnh.id = p_cnh_id
1189 and cnh.acn_id = acn.id;
1190
1191 CURSOR pdf_type_csr(p_pdf_id IN NUMBER) IS
1192 Select pdf.usage, pdf.pdf_type
1193 From okc_process_defs_v pdf, okc_outcomes_v oce
1194 where pdf.id = p_pdf_id
1195 and pdf.id = oce.pdf_id;
1196
1197 l_dummy VARCHAR2(1) := '?';
1198 l_sync_flag okc_process_defs_v.pdf_type%TYPE;
1199 l_usage okc_process_defs_v.usage%TYPE;
1200 l_pdf_type okc_process_defs_v.pdf_type%TYPE;
1201 BEGIN
1202 -- initialize return status
1203 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1204
1205 -- Check if pdf_id is null
1206 IF p_ocev_rec.pdf_id = OKC_API.G_MISS_NUM OR p_ocev_rec.pdf_id IS NULL THEN
1207 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1208 p_msg_name => g_required_value,
1209 p_token1 => g_col_name_token,
1210 p_token1_value => 'pdf_id');
1211 x_return_status := OKC_API.G_RET_STS_ERROR;
1212 raise G_EXCEPTION_HALT_VALIDATION;
1213 END IF;
1214
1215 -- Enforce foreign key
1216 OPEN okc_pdfv_pk_csr;
1217 FETCH okc_pdfv_pk_csr INTO l_dummy;
1218 CLOSE okc_pdfv_pk_csr;
1219
1220 -- If l_dummy is still set to default, data was not found
1221 IF (l_dummy = '?') THEN
1222 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1223 p_msg_name => g_no_parent_record,
1224 p_token1 => g_col_name_token,
1225 p_token1_value => 'pdf_id',
1226 p_token2 => g_child_table_token,
1227 p_token2_value => 'OKC_OUTCOMES_V',
1228 p_token3 => g_parent_table_token,
1229 p_token3_value => 'OKC_PROCESS_DEFS_V');
1230 x_return_status := OKC_API.G_RET_STS_ERROR;
1231 RAISE G_EXCEPTION_HALT_VALIDATION;
1232 END IF;
1233
1234 --If sync_allowed_flag is 'N' then outcome cannot be 'Alert' or 'Script'
1235 OPEN sync_flag_yn_csr(p_ocev_rec.cnh_id);
1236 FETCH sync_flag_yn_csr INTO l_sync_flag;
1237 CLOSE sync_flag_yn_csr;
1238
1239 OPEN pdf_type_csr(p_ocev_rec.pdf_id);
1240 FETCH pdf_type_csr INTO l_usage, l_pdf_type;
1241 CLOSE pdf_type_csr;
1242
1243 IF l_sync_flag = 'N' then
1244 IF l_usage IN ('OUTCOME') THEN
1245 IF l_pdf_type IN ('ALERT', 'SCRIPT') THEN
1246 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1247 p_msg_name => g_invalid_value,
1248 p_token1 => g_col_name_token,
1249 p_token1_value => 'pdf_id');
1250 RAISE G_EXCEPTION_HALT_VALIDATION;
1251 END IF;
1252 END IF;
1253 END IF;
1254 EXCEPTION
1255 when G_EXCEPTION_HALT_VALIDATION then
1256 -- no processing necessary; validation can continue
1257 -- with the next column
1258 null;
1259
1260 -- verify that cursor was closed
1261 if okc_pdfv_pk_csr%ISOPEN then
1262 close okc_pdfv_pk_csr;
1263 end if;
1264
1265 when OTHERS then
1266 -- store SQL error message on message stack for caller
1267 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1268 p_msg_name => g_unexpected_error,
1269 p_token1 => g_sqlcode_token,
1270 p_token1_value => sqlcode,
1271 p_token2 => g_sqlerrm_token,
1272 p_token2_value => sqlerrm);
1273 -- notify caller of an UNEXPECTED error
1274 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1275 END validate_pdf_id;
1276
1277 -- Start of comments
1278 -- Procedure Name : validate_enabled_yn
1279 -- Description : Checks if column ENABLED_YN is 'Y' or 'N' only
1280 -- Business Rules :
1281 -- Parameters :
1282 -- Version : 1.0
1283 -- End of comments
1284
1285 PROCEDURE Validate_enabled_yn(x_return_status OUT NOCOPY VARCHAR2
1286 ,p_ocev_rec IN ocev_rec_type) IS
1287
1288 BEGIN
1289 -- initialize return status
1290 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1291
1292 -- Check if enabled_yn is null
1293 IF p_ocev_rec.enabled_yn = OKC_API.G_MISS_CHAR OR p_ocev_rec.enabled_yn IS NULL THEN
1294 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1295 p_msg_name => g_required_value,
1296 p_token1 => g_col_name_token,
1297 p_token1_value => 'enabled_yn');
1298 x_return_status := OKC_API.G_RET_STS_ERROR;
1299 raise G_EXCEPTION_HALT_VALIDATION;
1300 END IF;
1301
1302 IF UPPER(p_ocev_rec.enabled_yn) IN ('Y', 'N') THEN
1303 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1304 ELSE
1305 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1306 p_msg_name => g_invalid_value,
1307 p_token1 => g_col_name_token,
1308 p_token1_value => 'enabled_yn');
1309 x_return_status := OKC_API.G_RET_STS_ERROR;
1310 END IF;
1311
1312 --Check if the data is in upper case
1313 IF p_ocev_rec.enabled_yn <> UPPER(p_ocev_rec.enabled_yn) THEN
1314 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1315 p_msg_name => g_uppercase_required,
1316 p_token1 => g_col_name_token,
1317 p_token1_value => 'enabled_yn');
1318 x_return_status := OKC_API.G_RET_STS_ERROR;
1319 END IF;
1320 EXCEPTION
1321 when G_EXCEPTION_HALT_VALIDATION then
1322 -- no processing necessary; validation can continue
1323 -- with the next column
1324 null;
1325
1326 when OTHERS then
1327 -- store SQL error message on message stack for caller
1328 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1329 p_msg_name => g_unexpected_error,
1330 p_token1 => g_sqlcode_token,
1331 p_token1_value => sqlcode,
1332 p_token2 => g_sqlerrm_token,
1333 p_token2_value => sqlerrm);
1334 -- notify caller of an UNEXPECTED error
1335 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1336 END validate_enabled_yn;
1337
1338 FUNCTION Validate_Attributes (
1339 p_ocev_rec IN ocev_rec_type
1340 ) RETURN VARCHAR2 IS
1341 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1342 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1343
1344 BEGIN
1345
1346 validate_object_version_number(x_return_status => l_return_status
1347 ,p_ocev_rec => p_ocev_rec);
1348 -- store the highest degree of error
1349 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1350 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1351 l_return_status := x_return_status;
1352 RAISE G_EXCEPTION_HALT_VALIDATION;
1353 ELSE
1354 l_return_status := x_return_status; -- record that there was an error
1355 END IF;
1356 END IF;
1357
1358 validate_sfwt_flag(x_return_status => l_return_status
1359 ,p_ocev_rec => p_ocev_rec);
1360 -- store the highest degree of error
1361 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1362 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1363 l_return_status := x_return_status;
1364 RAISE G_EXCEPTION_HALT_VALIDATION;
1365 ELSE
1366 l_return_status := x_return_status; -- record that there was an error
1367 END IF;
1368 END IF;
1369
1370
1371 validate_seeded_flag(x_return_status => l_return_status
1372 ,p_ocev_rec => p_ocev_rec);
1373 -- store the highest degree of error
1374 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1375 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1376 l_return_status := x_return_status;
1377 RAISE G_EXCEPTION_HALT_VALIDATION;
1378 ELSE
1379 l_return_status := x_return_status; -- record that there was an error
1380 END IF;
1381 END IF;
1382
1383 validate_application_id(x_return_status => l_return_status
1384 ,p_ocev_rec => p_ocev_rec);
1385 -- store the highest degree of error
1386 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1387 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1388 l_return_status := x_return_status;
1389 RAISE G_EXCEPTION_HALT_VALIDATION;
1390 ELSE
1391 l_return_status := x_return_status; -- record that there was an error
1392 END IF;
1393 END IF;
1394
1395 validate_success_resource_id(x_return_status => l_return_status
1396 ,p_ocev_rec => p_ocev_rec);
1397 -- store the highest degree of error
1398 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1399 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1400 l_return_status := x_return_status;
1401 RAISE G_EXCEPTION_HALT_VALIDATION;
1402 ELSE
1403 l_return_status := x_return_status; -- record that there was an error
1404 END IF;
1405 END IF;
1406
1407 validate_failure_resource_id(x_return_status => l_return_status
1408 ,p_ocev_rec => p_ocev_rec);
1409 -- store the highest degree of error
1410 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1411 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1412 l_return_status := x_return_status;
1413 RAISE G_EXCEPTION_HALT_VALIDATION;
1414 ELSE
1415 l_return_status := x_return_status; -- record that there was an error
1416 END IF;
1417 END IF;
1418
1419 validate_cnh_id(x_return_status => l_return_status
1420 ,p_ocev_rec => p_ocev_rec);
1421 -- store the highest degree of error
1422 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1423 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1424 l_return_status := x_return_status;
1425 RAISE G_EXCEPTION_HALT_VALIDATION;
1426 ELSE
1427 l_return_status := x_return_status; -- record that there was an error
1428 END IF;
1429 END IF;
1430
1431 /*validate_rul_id(x_return_status => l_return_status
1432 ,p_ocev_rec => p_ocev_rec);
1433 -- store the highest degree of error
1434 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1435 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1436 l_return_status := x_return_status;
1437 RAISE G_EXCEPTION_HALT_VALIDATION;
1438 ELSE
1439 l_return_status := x_return_status; -- record that there was an error
1440 END IF;
1441 END IF;*/
1442
1443 validate_pdf_id(x_return_status => l_return_status
1444 ,p_ocev_rec => p_ocev_rec);
1445 -- store the highest degree of error
1446 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1447 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1448 l_return_status := x_return_status;
1449 RAISE G_EXCEPTION_HALT_VALIDATION;
1450 ELSE
1451 l_return_status := x_return_status; -- record that there was an error
1452 END IF;
1453 END IF;
1454
1455 validate_enabled_yn(x_return_status => l_return_status
1456 ,p_ocev_rec => p_ocev_rec);
1457 -- store the highest degree of error
1458 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1459 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1460 l_return_status := x_return_status;
1461 RAISE G_EXCEPTION_HALT_VALIDATION;
1462 ELSE
1463 l_return_status := x_return_status; -- record that there was an error
1464 END IF;
1465 END IF;
1466
1467 RETURN(l_return_status);
1468
1469 EXCEPTION
1470 When G_EXCEPTION_HALT_VALIDATION then
1471 --just come out with return status
1472 null;
1473 RETURN(l_return_status);
1474
1475 when OTHERS then
1476 -- store SQL error message on message stack for caller
1477 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1478 p_msg_name => g_unexpected_error,
1479 p_token1 => g_sqlcode_token,
1480 p_token1_value => sqlcode,
1481 p_token2 => g_sqlerrm_token,
1482 p_token2_value => sqlerrm);
1483
1484 -- notify caller of an UNEXPECTED error
1485 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1486 RETURN(l_return_status);
1487 END Validate_Attributes;
1488
1489 ---------------------------------------------------------------------------
1490 -- PROCEDURE Validate_Record
1491 ---------------------------------------------------------------------------
1492 ----------------------------------------
1493 -- Validate_Record for:OKC_OUTCOMES_V --
1494 ----------------------------------------
1495 FUNCTION Validate_Record (
1496 p_ocev_rec IN ocev_rec_type
1497 ) RETURN VARCHAR2 IS
1498 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1499 BEGIN
1500 RETURN (l_return_status);
1501 END Validate_Record;
1502
1503 ---------------------------------------------------------------------------
1504 -- PROCEDURE Migrate
1505 ---------------------------------------------------------------------------
1506 PROCEDURE migrate (
1507 p_from IN ocev_rec_type,
1508 p_to OUT NOCOPY oce_rec_type
1509 ) IS
1510 BEGIN
1511 p_to.id := p_from.id;
1512 p_to.pdf_id := p_from.pdf_id;
1513 p_to.cnh_id := p_from.cnh_id;
1514 p_to.dnz_chr_id := p_from.dnz_chr_id;
1515 p_to.success_resource_id := p_from.success_resource_id;
1516 p_to.failure_resource_id := p_from.failure_resource_id;
1517 p_to.enabled_yn := p_from.enabled_yn;
1518 p_to.object_version_number := p_from.object_version_number;
1519 p_to.created_by := p_from.created_by;
1520 p_to.creation_date := p_from.creation_date;
1521 p_to.last_updated_by := p_from.last_updated_by;
1522 p_to.last_update_date := p_from.last_update_date;
1523 p_to.last_update_login := p_from.last_update_login;
1524 p_to.attribute_category := p_from.attribute_category;
1525 p_to.attribute1 := p_from.attribute1;
1526 p_to.attribute2 := p_from.attribute2;
1527 p_to.attribute3 := p_from.attribute3;
1528 p_to.attribute4 := p_from.attribute4;
1529 p_to.attribute5 := p_from.attribute5;
1530 p_to.attribute6 := p_from.attribute6;
1531 p_to.attribute7 := p_from.attribute7;
1532 p_to.attribute8 := p_from.attribute8;
1533 p_to.attribute9 := p_from.attribute9;
1534 p_to.attribute10 := p_from.attribute10;
1535 p_to.attribute11 := p_from.attribute11;
1536 p_to.attribute12 := p_from.attribute12;
1537 p_to.attribute13 := p_from.attribute13;
1538 p_to.attribute14 := p_from.attribute14;
1539 p_to.attribute15 := p_from.attribute15;
1540 p_to.application_id := p_from.application_id;
1541 p_to.seeded_flag := p_from.seeded_flag;
1542 END migrate;
1543 PROCEDURE migrate (
1544 p_from IN oce_rec_type,
1545 p_to IN OUT NOCOPY ocev_rec_type
1546 ) IS
1547 BEGIN
1548 p_to.id := p_from.id;
1549 p_to.pdf_id := p_from.pdf_id;
1550 p_to.cnh_id := p_from.cnh_id;
1551 p_to.dnz_chr_id := p_from.dnz_chr_id;
1552 p_to.success_resource_id := p_from.success_resource_id;
1553 p_to.failure_resource_id := p_from.failure_resource_id;
1554 p_to.enabled_yn := p_from.enabled_yn;
1555 p_to.object_version_number := p_from.object_version_number;
1556 p_to.created_by := p_from.created_by;
1557 p_to.creation_date := p_from.creation_date;
1558 p_to.last_updated_by := p_from.last_updated_by;
1559 p_to.last_update_date := p_from.last_update_date;
1560 p_to.last_update_login := p_from.last_update_login;
1561 p_to.attribute_category := p_from.attribute_category;
1562 p_to.attribute1 := p_from.attribute1;
1563 p_to.attribute2 := p_from.attribute2;
1564 p_to.attribute3 := p_from.attribute3;
1565 p_to.attribute4 := p_from.attribute4;
1566 p_to.attribute5 := p_from.attribute5;
1567 p_to.attribute6 := p_from.attribute6;
1568 p_to.attribute7 := p_from.attribute7;
1569 p_to.attribute8 := p_from.attribute8;
1570 p_to.attribute9 := p_from.attribute9;
1571 p_to.attribute10 := p_from.attribute10;
1572 p_to.attribute11 := p_from.attribute11;
1573 p_to.attribute12 := p_from.attribute12;
1574 p_to.attribute13 := p_from.attribute13;
1575 p_to.attribute14 := p_from.attribute14;
1576 p_to.attribute15 := p_from.attribute15;
1577 p_to.application_id := p_from.application_id;
1578 p_to.seeded_flag := p_from.seeded_flag;
1579 END migrate;
1580 PROCEDURE migrate (
1581 p_from IN ocev_rec_type,
1582 p_to OUT NOCOPY okc_outcomes_tl_rec_type
1583 ) IS
1584 BEGIN
1585 p_to.id := p_from.id;
1586 p_to.sfwt_flag := p_from.sfwt_flag;
1587 p_to.comments := p_from.comments;
1588 p_to.created_by := p_from.created_by;
1589 p_to.creation_date := p_from.creation_date;
1590 p_to.last_updated_by := p_from.last_updated_by;
1591 p_to.last_update_date := p_from.last_update_date;
1592 p_to.last_update_login := p_from.last_update_login;
1593 END migrate;
1594 PROCEDURE migrate (
1595 p_from IN okc_outcomes_tl_rec_type,
1596 p_to IN OUT NOCOPY ocev_rec_type
1597 ) IS
1598 BEGIN
1599 p_to.id := p_from.id;
1600 p_to.sfwt_flag := p_from.sfwt_flag;
1601 p_to.comments := p_from.comments;
1602 p_to.created_by := p_from.created_by;
1603 p_to.creation_date := p_from.creation_date;
1604 p_to.last_updated_by := p_from.last_updated_by;
1605 p_to.last_update_date := p_from.last_update_date;
1606 p_to.last_update_login := p_from.last_update_login;
1607 END migrate;
1608
1609 ---------------------------------------------------------------------------
1610 -- PROCEDURE validate_row
1611 ---------------------------------------------------------------------------
1612 -------------------------------------
1613 -- validate_row for:OKC_OUTCOMES_V --
1614 -------------------------------------
1615 PROCEDURE validate_row(
1616 p_api_version IN NUMBER,
1617 p_init_msg_list IN VARCHAR2 ,
1618 x_return_status OUT NOCOPY VARCHAR2,
1619 x_msg_count OUT NOCOPY NUMBER,
1620 x_msg_data OUT NOCOPY VARCHAR2,
1621 p_ocev_rec IN ocev_rec_type) IS
1622
1623 l_api_version CONSTANT NUMBER := 1;
1624 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1625 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1626 l_ocev_rec ocev_rec_type := p_ocev_rec;
1627 l_oce_rec oce_rec_type;
1628 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
1629 BEGIN
1630 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1631 G_PKG_NAME,
1632 p_init_msg_list,
1633 l_api_version,
1634 p_api_version,
1635 '_PVT',
1636 x_return_status);
1637 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1638 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1639 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1640 RAISE OKC_API.G_EXCEPTION_ERROR;
1641 END IF;
1642 --- Validate all non-missing attributes (Item Level Validation)
1643 l_return_status := Validate_Attributes(l_ocev_rec);
1644 --- If any errors happen abort API
1645 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1646 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1647 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1648 RAISE OKC_API.G_EXCEPTION_ERROR;
1649 END IF;
1650 l_return_status := Validate_Record(l_ocev_rec);
1651 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1652 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1653 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1654 RAISE OKC_API.G_EXCEPTION_ERROR;
1655 END IF;
1656 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1657 EXCEPTION
1658 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1659 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1660 (
1661 l_api_name,
1662 G_PKG_NAME,
1663 'OKC_API.G_RET_STS_ERROR',
1664 x_msg_count,
1665 x_msg_data,
1666 '_PVT'
1667 );
1668 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1669 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1670 (
1671 l_api_name,
1672 G_PKG_NAME,
1673 'OKC_API.G_RET_STS_UNEXP_ERROR',
1674 x_msg_count,
1675 x_msg_data,
1676 '_PVT'
1677 );
1678 WHEN OTHERS THEN
1679 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1680 (
1681 l_api_name,
1682 G_PKG_NAME,
1683 'OTHERS',
1684 x_msg_count,
1685 x_msg_data,
1686 '_PVT'
1687 );
1688 END validate_row;
1689 ------------------------------------------
1690 -- PL/SQL TBL validate_row for:OCEV_TBL --
1691 ------------------------------------------
1692 PROCEDURE validate_row(
1693 p_api_version IN NUMBER,
1694 p_init_msg_list IN VARCHAR2 ,
1695 x_return_status OUT NOCOPY VARCHAR2,
1696 x_msg_count OUT NOCOPY NUMBER,
1697 x_msg_data OUT NOCOPY VARCHAR2,
1698 p_ocev_tbl IN ocev_tbl_type) IS
1699
1700 l_api_version CONSTANT NUMBER := 1;
1701 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1703 i NUMBER := 0;
1704 BEGIN
1705 OKC_API.init_msg_list(p_init_msg_list);
1706 -- Make sure PL/SQL table has records in it before passing
1707 IF (p_ocev_tbl.COUNT > 0) THEN
1708 i := p_ocev_tbl.FIRST;
1709 LOOP
1710 validate_row (
1711 p_api_version => p_api_version,
1712 p_init_msg_list => OKC_API.G_FALSE,
1713 x_return_status => x_return_status,
1714 x_msg_count => x_msg_count,
1715 x_msg_data => x_msg_data,
1716 p_ocev_rec => p_ocev_tbl(i));
1717 EXIT WHEN (i = p_ocev_tbl.LAST);
1718 i := p_ocev_tbl.NEXT(i);
1719 END LOOP;
1720 END IF;
1721 EXCEPTION
1722 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1723 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1724 (
1725 l_api_name,
1726 G_PKG_NAME,
1727 'OKC_API.G_RET_STS_ERROR',
1728 x_msg_count,
1729 x_msg_data,
1730 '_PVT'
1731 );
1732 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1733 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1734 (
1735 l_api_name,
1736 G_PKG_NAME,
1737 'OKC_API.G_RET_STS_UNEXP_ERROR',
1738 x_msg_count,
1739 x_msg_data,
1740 '_PVT'
1741 );
1742 WHEN OTHERS THEN
1743 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1744 (
1745 l_api_name,
1746 G_PKG_NAME,
1747 'OTHERS',
1748 x_msg_count,
1749 x_msg_data,
1750 '_PVT'
1751 );
1752 END validate_row;
1753
1754 ---------------------------------------------------------------------------
1755 -- PROCEDURE insert_row
1756 ---------------------------------------------------------------------------
1757 -----------------------------------
1758 -- insert_row for:OKC_OUTCOMES_B --
1759 -----------------------------------
1760 PROCEDURE insert_row(
1761 p_init_msg_list IN VARCHAR2 ,
1762 x_return_status OUT NOCOPY VARCHAR2,
1763 x_msg_count OUT NOCOPY NUMBER,
1764 x_msg_data OUT NOCOPY VARCHAR2,
1765 p_oce_rec IN oce_rec_type,
1766 x_oce_rec OUT NOCOPY oce_rec_type) IS
1767
1768 l_api_version CONSTANT NUMBER := 1;
1769 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1770 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1771 l_oce_rec oce_rec_type := p_oce_rec;
1772 l_def_oce_rec oce_rec_type;
1773 ---------------------------------------
1774 -- Set_Attributes for:OKC_OUTCOMES_B --
1775 ---------------------------------------
1776 FUNCTION Set_Attributes (
1777 p_oce_rec IN oce_rec_type,
1778 x_oce_rec OUT NOCOPY oce_rec_type
1779 ) RETURN VARCHAR2 IS
1780 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1781 BEGIN
1782 x_oce_rec := p_oce_rec;
1783 RETURN(l_return_status);
1784 END Set_Attributes;
1785 BEGIN
1786 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1787 p_init_msg_list,
1788 '_PVT',
1789 x_return_status);
1790 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1791 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1792 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1793 RAISE OKC_API.G_EXCEPTION_ERROR;
1794 END IF;
1795 --- Setting item attributes
1796 l_return_status := Set_Attributes(
1797 p_oce_rec, -- IN
1798 l_oce_rec); -- OUT
1799 --- If any errors happen abort API
1800 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1801 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1802 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1803 RAISE OKC_API.G_EXCEPTION_ERROR;
1804 END IF;
1805 INSERT INTO OKC_OUTCOMES_B(
1806 id,
1807 pdf_id,
1808 cnh_id,
1809 dnz_chr_id,
1810 success_resource_id,
1811 failure_resource_id,
1812 enabled_yn,
1813 object_version_number,
1814 created_by,
1815 creation_date,
1816 last_updated_by,
1817 last_update_date,
1818 last_update_login,
1819 attribute_category,
1820 attribute1,
1821 attribute2,
1822 attribute3,
1823 attribute4,
1824 attribute5,
1825 attribute6,
1826 attribute7,
1827 attribute8,
1828 attribute9,
1829 attribute10,
1830 attribute11,
1831 attribute12,
1832 attribute13,
1833 attribute14,
1834 attribute15,
1835 application_id,
1836 seeded_flag)
1837 VALUES (
1838 l_oce_rec.id,
1839 l_oce_rec.pdf_id,
1840 l_oce_rec.cnh_id,
1841 l_oce_rec.dnz_chr_id,
1842 l_oce_rec.success_resource_id,
1843 l_oce_rec.failure_resource_id,
1844 l_oce_rec.enabled_yn,
1845 l_oce_rec.object_version_number,
1846 l_oce_rec.created_by,
1847 l_oce_rec.creation_date,
1848 l_oce_rec.last_updated_by,
1849 l_oce_rec.last_update_date,
1850 l_oce_rec.last_update_login,
1851 l_oce_rec.attribute_category,
1852 l_oce_rec.attribute1,
1853 l_oce_rec.attribute2,
1854 l_oce_rec.attribute3,
1855 l_oce_rec.attribute4,
1856 l_oce_rec.attribute5,
1857 l_oce_rec.attribute6,
1858 l_oce_rec.attribute7,
1859 l_oce_rec.attribute8,
1860 l_oce_rec.attribute9,
1861 l_oce_rec.attribute10,
1862 l_oce_rec.attribute11,
1863 l_oce_rec.attribute12,
1864 l_oce_rec.attribute13,
1865 l_oce_rec.attribute14,
1866 l_oce_rec.attribute15,
1867 l_oce_rec.application_id,
1868 l_oce_rec.seeded_flag);
1869 -- Set OUT values
1870 x_oce_rec := l_oce_rec;
1871 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1872 EXCEPTION
1873 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1874 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1875 (
1876 l_api_name,
1877 G_PKG_NAME,
1878 'OKC_API.G_RET_STS_ERROR',
1879 x_msg_count,
1880 x_msg_data,
1881 '_PVT'
1882 );
1883 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1884 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1885 (
1886 l_api_name,
1887 G_PKG_NAME,
1888 'OKC_API.G_RET_STS_UNEXP_ERROR',
1889 x_msg_count,
1890 x_msg_data,
1891 '_PVT'
1892 );
1893 WHEN OTHERS THEN
1894 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1895 (
1896 l_api_name,
1897 G_PKG_NAME,
1898 'OTHERS',
1899 x_msg_count,
1900 x_msg_data,
1901 '_PVT'
1902 );
1903 END insert_row;
1904 ------------------------------------
1905 -- insert_row for:OKC_OUTCOMES_TL --
1906 ------------------------------------
1907 PROCEDURE insert_row(
1908 p_init_msg_list IN VARCHAR2 ,
1909 x_return_status OUT NOCOPY VARCHAR2,
1910 x_msg_count OUT NOCOPY NUMBER,
1911 x_msg_data OUT NOCOPY VARCHAR2,
1912 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
1913 x_okc_outcomes_tl_rec OUT NOCOPY okc_outcomes_tl_rec_type) IS
1914
1915 l_api_version CONSTANT NUMBER := 1;
1916 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1917 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1918 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type := p_okc_outcomes_tl_rec;
1919 l_def_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
1920 CURSOR get_languages IS
1921 SELECT *
1922 FROM FND_LANGUAGES
1923 WHERE INSTALLED_FLAG IN ('I', 'B');
1924 ----------------------------------------
1925 -- Set_Attributes for:OKC_OUTCOMES_TL --
1926 ----------------------------------------
1927 FUNCTION Set_Attributes (
1928 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
1929 x_okc_outcomes_tl_rec OUT NOCOPY okc_outcomes_tl_rec_type
1930 ) RETURN VARCHAR2 IS
1931 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1932 BEGIN
1933 x_okc_outcomes_tl_rec := p_okc_outcomes_tl_rec;
1934 x_okc_outcomes_tl_rec.LANGUAGE :=l_lang;
1935 x_okc_outcomes_tl_rec.SOURCE_LANG := l_lang;
1936 RETURN(l_return_status);
1937 END Set_Attributes;
1938 BEGIN
1939 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1940 p_init_msg_list,
1941 '_PVT',
1942 x_return_status);
1943 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1944 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1945 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1946 RAISE OKC_API.G_EXCEPTION_ERROR;
1947 END IF;
1948 --- Setting item attributes
1949 l_return_status := Set_Attributes(
1950 p_okc_outcomes_tl_rec, -- IN
1951 l_okc_outcomes_tl_rec); -- OUT
1952 --- If any errors happen abort API
1953 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1954 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1955 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1956 RAISE OKC_API.G_EXCEPTION_ERROR;
1957 END IF;
1958 FOR l_lang_rec IN get_languages LOOP
1959 l_okc_outcomes_tl_rec.language := l_lang_rec.language_code;
1960 INSERT INTO OKC_OUTCOMES_TL(
1961 id,
1962 language,
1963 source_lang,
1964 sfwt_flag,
1965 comments,
1966 created_by,
1967 creation_date,
1968 last_updated_by,
1969 last_update_date,
1970 last_update_login)
1971 VALUES (
1972 l_okc_outcomes_tl_rec.id,
1973 l_okc_outcomes_tl_rec.language,
1974 l_okc_outcomes_tl_rec.source_lang,
1975 l_okc_outcomes_tl_rec.sfwt_flag,
1976 l_okc_outcomes_tl_rec.comments,
1977 l_okc_outcomes_tl_rec.created_by,
1978 l_okc_outcomes_tl_rec.creation_date,
1979 l_okc_outcomes_tl_rec.last_updated_by,
1980 l_okc_outcomes_tl_rec.last_update_date,
1981 l_okc_outcomes_tl_rec.last_update_login);
1982 END LOOP;
1983 -- Set OUT values
1984 x_okc_outcomes_tl_rec := l_okc_outcomes_tl_rec;
1985 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1986 EXCEPTION
1987 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1988 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1989 (
1990 l_api_name,
1991 G_PKG_NAME,
1992 'OKC_API.G_RET_STS_ERROR',
1993 x_msg_count,
1994 x_msg_data,
1995 '_PVT'
1996 );
1997 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1998 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1999 (
2000 l_api_name,
2001 G_PKG_NAME,
2002 'OKC_API.G_RET_STS_UNEXP_ERROR',
2003 x_msg_count,
2004 x_msg_data,
2005 '_PVT'
2006 );
2007 WHEN OTHERS THEN
2008 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2009 (
2010 l_api_name,
2011 G_PKG_NAME,
2012 'OTHERS',
2013 x_msg_count,
2014 x_msg_data,
2015 '_PVT'
2016 );
2017 END insert_row;
2018 -----------------------------------
2019 -- insert_row for:OKC_OUTCOMES_V --
2020 -----------------------------------
2021 PROCEDURE insert_row(
2022 p_api_version IN NUMBER,
2023 p_init_msg_list IN VARCHAR2 ,
2024 x_return_status OUT NOCOPY VARCHAR2,
2025 x_msg_count OUT NOCOPY NUMBER,
2026 x_msg_data OUT NOCOPY VARCHAR2,
2027 p_ocev_rec IN ocev_rec_type,
2028 x_ocev_rec OUT NOCOPY ocev_rec_type) IS
2029
2030 l_id NUMBER ;
2031 l_api_version CONSTANT NUMBER := 1;
2032 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2033 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2034 l_ocev_rec ocev_rec_type;
2035 l_def_ocev_rec ocev_rec_type;
2036 l_oce_rec oce_rec_type;
2037 lx_oce_rec oce_rec_type;
2038 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
2039 lx_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
2040 -------------------------------
2041 -- FUNCTION fill_who_columns --
2042 -------------------------------
2043 FUNCTION fill_who_columns (
2044 p_ocev_rec IN ocev_rec_type
2045 ) RETURN ocev_rec_type IS
2046 l_ocev_rec ocev_rec_type := p_ocev_rec;
2047 BEGIN
2048 l_ocev_rec.CREATION_DATE := SYSDATE;
2049 l_ocev_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2050 l_ocev_rec.LAST_UPDATE_DATE := l_ocev_rec.CREATION_DATE;
2051 l_ocev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2052 l_ocev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2053 RETURN(l_ocev_rec);
2054 END fill_who_columns;
2055 ---------------------------------------
2056 -- Set_Attributes for:OKC_OUTCOMES_V --
2057 ---------------------------------------
2058 FUNCTION Set_Attributes (
2059 p_ocev_rec IN ocev_rec_type,
2060 x_ocev_rec OUT NOCOPY ocev_rec_type
2061 ) RETURN VARCHAR2 IS
2062 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2063 BEGIN
2064 x_ocev_rec := p_ocev_rec;
2065 x_ocev_rec.OBJECT_VERSION_NUMBER := 1;
2066 x_ocev_rec.SFWT_FLAG := 'N';
2067 RETURN(l_return_status);
2068 END Set_Attributes;
2069 BEGIN
2070 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2071 G_PKG_NAME,
2072 p_init_msg_list,
2073 l_api_version,
2074 p_api_version,
2075 '_PVT',
2076 x_return_status);
2077 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2078 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2079 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2080 RAISE OKC_API.G_EXCEPTION_ERROR;
2081 END IF;
2082 l_ocev_rec := null_out_defaults(p_ocev_rec);
2083 -- Set primary key value
2084 -- If outcome record is created by seed then use sequence generated id
2085 IF l_ocev_rec.CREATED_BY = 1 THEN
2086 SELECT OKC_OUTCOMES_S1.nextval INTO l_id FROM dual;
2087 l_ocev_rec.ID := l_id;
2088 l_ocev_rec.seeded_flag := 'Y';
2089 ELSE
2090 l_ocev_rec.ID := get_seq_id;
2091 l_ocev_rec.seeded_flag := 'N';
2092 END IF;
2093
2094 --l_ocev_rec.ID := get_seq_id;
2095 --- Setting item attributes
2096 l_return_status := Set_Attributes(
2097 l_ocev_rec, -- IN
2098 l_def_ocev_rec); -- OUT
2099 --- If any errors happen abort API
2100 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2101 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2102 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2103 RAISE OKC_API.G_EXCEPTION_ERROR;
2104 END IF;
2105 l_def_ocev_rec := fill_who_columns(l_def_ocev_rec);
2106 --- Validate all non-missing attributes (Item Level Validation)
2107 l_return_status := Validate_Attributes(l_def_ocev_rec);
2108 --- If any errors happen abort API
2109 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2110 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2111 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2112 RAISE OKC_API.G_EXCEPTION_ERROR;
2113 END IF;
2114 l_return_status := Validate_Record(l_def_ocev_rec);
2115 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2116 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2117 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2118 RAISE OKC_API.G_EXCEPTION_ERROR;
2119 END IF;
2120 --------------------------------------
2121 -- Move VIEW record to "Child" records
2122 --------------------------------------
2123 migrate(l_def_ocev_rec, l_oce_rec);
2124 migrate(l_def_ocev_rec, l_okc_outcomes_tl_rec);
2125 --------------------------------------------
2126 -- Call the INSERT_ROW for each child record
2127 --------------------------------------------
2128 insert_row(
2129 p_init_msg_list,
2130 x_return_status,
2131 x_msg_count,
2132 x_msg_data,
2133 l_oce_rec,
2134 lx_oce_rec
2135 );
2136 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2137 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2138 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2139 RAISE OKC_API.G_EXCEPTION_ERROR;
2140 END IF;
2141 migrate(lx_oce_rec, l_def_ocev_rec);
2142 insert_row(
2143 p_init_msg_list,
2144 x_return_status,
2145 x_msg_count,
2146 x_msg_data,
2147 l_okc_outcomes_tl_rec,
2148 lx_okc_outcomes_tl_rec
2149 );
2150 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2151 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2152 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2153 RAISE OKC_API.G_EXCEPTION_ERROR;
2154 END IF;
2155 migrate(lx_okc_outcomes_tl_rec, l_def_ocev_rec);
2156 -- Set OUT values
2157 x_ocev_rec := l_def_ocev_rec;
2158 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2159 EXCEPTION
2160 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2161 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2162 (
2163 l_api_name,
2164 G_PKG_NAME,
2165 'OKC_API.G_RET_STS_ERROR',
2166 x_msg_count,
2167 x_msg_data,
2168 '_PVT'
2169 );
2170 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2171 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2172 (
2173 l_api_name,
2174 G_PKG_NAME,
2175 'OKC_API.G_RET_STS_UNEXP_ERROR',
2176 x_msg_count,
2177 x_msg_data,
2178 '_PVT'
2179 );
2180 WHEN OTHERS THEN
2181 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2182 (
2183 l_api_name,
2184 G_PKG_NAME,
2185 'OTHERS',
2186 x_msg_count,
2187 x_msg_data,
2188 '_PVT'
2189 );
2190 END insert_row;
2191 ----------------------------------------
2192 -- PL/SQL TBL insert_row for:OCEV_TBL --
2193 ----------------------------------------
2194 PROCEDURE insert_row(
2195 p_api_version IN NUMBER,
2196 p_init_msg_list IN VARCHAR2 ,
2197 x_return_status OUT NOCOPY VARCHAR2,
2198 x_msg_count OUT NOCOPY NUMBER,
2199 x_msg_data OUT NOCOPY VARCHAR2,
2200 p_ocev_tbl IN ocev_tbl_type,
2201 x_ocev_tbl OUT NOCOPY ocev_tbl_type) IS
2202
2203 l_api_version CONSTANT NUMBER := 1;
2204 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2205 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2206 i NUMBER := 0;
2207 BEGIN
2208 OKC_API.init_msg_list(p_init_msg_list);
2209 -- Make sure PL/SQL table has records in it before passing
2210 IF (p_ocev_tbl.COUNT > 0) THEN
2211 i := p_ocev_tbl.FIRST;
2212 LOOP
2213 insert_row (
2214 p_api_version => p_api_version,
2215 p_init_msg_list => OKC_API.G_FALSE,
2216 x_return_status => x_return_status,
2217 x_msg_count => x_msg_count,
2218 x_msg_data => x_msg_data,
2219 p_ocev_rec => p_ocev_tbl(i),
2220 x_ocev_rec => x_ocev_tbl(i));
2221 EXIT WHEN (i = p_ocev_tbl.LAST);
2222 i := p_ocev_tbl.NEXT(i);
2223 END LOOP;
2224 END IF;
2225 EXCEPTION
2226 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2227 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2228 (
2229 l_api_name,
2230 G_PKG_NAME,
2231 'OKC_API.G_RET_STS_ERROR',
2232 x_msg_count,
2233 x_msg_data,
2234 '_PVT'
2235 );
2236 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2237 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2238 (
2239 l_api_name,
2240 G_PKG_NAME,
2241 'OKC_API.G_RET_STS_UNEXP_ERROR',
2242 x_msg_count,
2243 x_msg_data,
2244 '_PVT'
2245 );
2246 WHEN OTHERS THEN
2247 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2248 (
2249 l_api_name,
2250 G_PKG_NAME,
2251 'OTHERS',
2252 x_msg_count,
2253 x_msg_data,
2254 '_PVT'
2255 );
2256 END insert_row;
2257
2258 ---------------------------------------------------------------------------
2259 -- PROCEDURE lock_row
2260 ---------------------------------------------------------------------------
2261 ---------------------------------
2262 -- lock_row for:OKC_OUTCOMES_B --
2263 ---------------------------------
2264 PROCEDURE lock_row(
2265 p_init_msg_list IN VARCHAR2 ,
2266 x_return_status OUT NOCOPY VARCHAR2,
2267 x_msg_count OUT NOCOPY NUMBER,
2268 x_msg_data OUT NOCOPY VARCHAR2,
2269 p_oce_rec IN oce_rec_type) IS
2270
2271 E_Resource_Busy EXCEPTION;
2272 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2273 CURSOR lock_csr (p_oce_rec IN oce_rec_type) IS
2274 SELECT OBJECT_VERSION_NUMBER
2275 FROM OKC_OUTCOMES_B
2276 WHERE ID = p_oce_rec.id
2277 AND OBJECT_VERSION_NUMBER = p_oce_rec.object_version_number
2278 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2279
2280 CURSOR lchk_csr (p_oce_rec IN oce_rec_type) IS
2281 SELECT OBJECT_VERSION_NUMBER
2282 FROM OKC_OUTCOMES_B
2283 WHERE ID = p_oce_rec.id;
2284 l_api_version CONSTANT NUMBER := 1;
2285 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2286 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2287 l_object_version_number OKC_OUTCOMES_B.OBJECT_VERSION_NUMBER%TYPE;
2288 lc_object_version_number OKC_OUTCOMES_B.OBJECT_VERSION_NUMBER%TYPE;
2289 l_row_notfound BOOLEAN := FALSE;
2290 lc_row_notfound BOOLEAN := FALSE;
2291 BEGIN
2292 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2293 p_init_msg_list,
2294 '_PVT',
2295 x_return_status);
2296 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2297 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2298 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2299 RAISE OKC_API.G_EXCEPTION_ERROR;
2300 END IF;
2301 BEGIN
2302 OPEN lock_csr(p_oce_rec);
2303 FETCH lock_csr INTO l_object_version_number;
2304 l_row_notfound := lock_csr%NOTFOUND;
2305 CLOSE lock_csr;
2306 EXCEPTION
2307 WHEN E_Resource_Busy THEN
2308 IF (lock_csr%ISOPEN) THEN
2309 CLOSE lock_csr;
2310 END IF;
2311 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2312 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2313 END;
2314
2315 IF ( l_row_notfound ) THEN
2316 OPEN lchk_csr(p_oce_rec);
2317 FETCH lchk_csr INTO lc_object_version_number;
2318 lc_row_notfound := lchk_csr%NOTFOUND;
2319 CLOSE lchk_csr;
2320 END IF;
2321 IF (lc_row_notfound) THEN
2322 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2323 RAISE OKC_API.G_EXCEPTION_ERROR;
2324 ELSIF lc_object_version_number > p_oce_rec.object_version_number THEN
2325 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2326 RAISE OKC_API.G_EXCEPTION_ERROR;
2327 ELSIF lc_object_version_number <> p_oce_rec.object_version_number THEN
2328 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2329 RAISE OKC_API.G_EXCEPTION_ERROR;
2330 ELSIF lc_object_version_number = -1 THEN
2331 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2332 RAISE OKC_API.G_EXCEPTION_ERROR;
2333 END IF;
2334 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2335 EXCEPTION
2336 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2337 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2338 (
2339 l_api_name,
2340 G_PKG_NAME,
2341 'OKC_API.G_RET_STS_ERROR',
2342 x_msg_count,
2343 x_msg_data,
2344 '_PVT'
2345 );
2346 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2347 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2348 (
2349 l_api_name,
2350 G_PKG_NAME,
2351 'OKC_API.G_RET_STS_UNEXP_ERROR',
2352 x_msg_count,
2353 x_msg_data,
2354 '_PVT'
2355 );
2356 WHEN OTHERS THEN
2357 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2358 (
2359 l_api_name,
2360 G_PKG_NAME,
2361 'OTHERS',
2362 x_msg_count,
2363 x_msg_data,
2364 '_PVT'
2365 );
2366 END lock_row;
2367 ----------------------------------
2368 -- lock_row for:OKC_OUTCOMES_TL --
2369 ----------------------------------
2370 PROCEDURE lock_row(
2371 p_init_msg_list IN VARCHAR2 ,
2372 x_return_status OUT NOCOPY VARCHAR2,
2373 x_msg_count OUT NOCOPY NUMBER,
2374 x_msg_data OUT NOCOPY VARCHAR2,
2375 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type) IS
2376
2377 E_Resource_Busy EXCEPTION;
2378 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2379 CURSOR lock_csr (p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type) IS
2380 SELECT *
2381 FROM OKC_OUTCOMES_TL
2382 WHERE ID = p_okc_outcomes_tl_rec.id
2383 FOR UPDATE NOWAIT;
2384
2385 l_api_version CONSTANT NUMBER := 1;
2386 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2387 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2388 l_lock_var lock_csr%ROWTYPE;
2389 l_row_notfound BOOLEAN := FALSE;
2390 lc_row_notfound BOOLEAN := FALSE;
2391 BEGIN
2392 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2393 p_init_msg_list,
2394 '_PVT',
2395 x_return_status);
2396 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2397 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2398 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2399 RAISE OKC_API.G_EXCEPTION_ERROR;
2400 END IF;
2401 BEGIN
2402 OPEN lock_csr(p_okc_outcomes_tl_rec);
2403 FETCH lock_csr INTO l_lock_var;
2404 l_row_notfound := lock_csr%NOTFOUND;
2405 CLOSE lock_csr;
2406 EXCEPTION
2407 WHEN E_Resource_Busy THEN
2408 IF (lock_csr%ISOPEN) THEN
2409 CLOSE lock_csr;
2410 END IF;
2411 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2412 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2413 END;
2414
2415 IF ( l_row_notfound ) THEN
2416 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2417 RAISE OKC_API.G_EXCEPTION_ERROR;
2418 END IF;
2419 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2420 EXCEPTION
2421 WHEN OKC_API.G_EXCEPTION_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_ERROR',
2427 x_msg_count,
2428 x_msg_data,
2429 '_PVT'
2430 );
2431 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2432 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2433 (
2434 l_api_name,
2435 G_PKG_NAME,
2436 'OKC_API.G_RET_STS_UNEXP_ERROR',
2437 x_msg_count,
2438 x_msg_data,
2439 '_PVT'
2440 );
2441 WHEN OTHERS THEN
2442 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2443 (
2444 l_api_name,
2445 G_PKG_NAME,
2446 'OTHERS',
2447 x_msg_count,
2448 x_msg_data,
2449 '_PVT'
2450 );
2451 END lock_row;
2452 ---------------------------------
2453 -- lock_row for:OKC_OUTCOMES_V --
2454 ---------------------------------
2455 PROCEDURE lock_row(
2456 p_api_version IN NUMBER,
2457 p_init_msg_list IN VARCHAR2 ,
2458 x_return_status OUT NOCOPY VARCHAR2,
2459 x_msg_count OUT NOCOPY NUMBER,
2460 x_msg_data OUT NOCOPY VARCHAR2,
2461 p_ocev_rec IN ocev_rec_type) IS
2462
2463 l_api_version CONSTANT NUMBER := 1;
2464 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2465 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2466 l_oce_rec oce_rec_type;
2467 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
2468 BEGIN
2469 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2470 G_PKG_NAME,
2471 p_init_msg_list,
2472 l_api_version,
2473 p_api_version,
2474 '_PVT',
2475 x_return_status);
2476 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2477 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2479 RAISE OKC_API.G_EXCEPTION_ERROR;
2480 END IF;
2481 --------------------------------------
2482 -- Move VIEW record to "Child" records
2483 --------------------------------------
2484 migrate(p_ocev_rec, l_oce_rec);
2485 migrate(p_ocev_rec, l_okc_outcomes_tl_rec);
2486 --------------------------------------------
2487 -- Call the LOCK_ROW for each child record
2488 --------------------------------------------
2489 lock_row(
2490 p_init_msg_list,
2491 x_return_status,
2492 x_msg_count,
2493 x_msg_data,
2494 l_oce_rec
2495 );
2496 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2497 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2498 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2499 RAISE OKC_API.G_EXCEPTION_ERROR;
2500 END IF;
2501 lock_row(
2502 p_init_msg_list,
2503 x_return_status,
2504 x_msg_count,
2505 x_msg_data,
2506 l_okc_outcomes_tl_rec
2507 );
2508 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2509 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2510 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2511 RAISE OKC_API.G_EXCEPTION_ERROR;
2512 END IF;
2513 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2514 EXCEPTION
2515 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2516 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2517 (
2518 l_api_name,
2519 G_PKG_NAME,
2520 'OKC_API.G_RET_STS_ERROR',
2521 x_msg_count,
2522 x_msg_data,
2523 '_PVT'
2524 );
2525 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2526 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2527 (
2528 l_api_name,
2529 G_PKG_NAME,
2530 'OKC_API.G_RET_STS_UNEXP_ERROR',
2531 x_msg_count,
2532 x_msg_data,
2533 '_PVT'
2534 );
2535 WHEN OTHERS THEN
2536 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2537 (
2538 l_api_name,
2539 G_PKG_NAME,
2540 'OTHERS',
2541 x_msg_count,
2542 x_msg_data,
2543 '_PVT'
2544 );
2545 END lock_row;
2546 --------------------------------------
2547 -- PL/SQL TBL lock_row for:OCEV_TBL --
2548 --------------------------------------
2549 PROCEDURE lock_row(
2550 p_api_version IN NUMBER,
2551 p_init_msg_list IN VARCHAR2 ,
2552 x_return_status OUT NOCOPY VARCHAR2,
2553 x_msg_count OUT NOCOPY NUMBER,
2554 x_msg_data OUT NOCOPY VARCHAR2,
2555 p_ocev_tbl IN ocev_tbl_type) IS
2556
2557 l_api_version CONSTANT NUMBER := 1;
2558 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2559 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2560 i NUMBER := 0;
2561 BEGIN
2562 OKC_API.init_msg_list(p_init_msg_list);
2563 -- Make sure PL/SQL table has records in it before passing
2564 IF (p_ocev_tbl.COUNT > 0) THEN
2565 i := p_ocev_tbl.FIRST;
2566 LOOP
2567 lock_row (
2568 p_api_version => p_api_version,
2569 p_init_msg_list => OKC_API.G_FALSE,
2570 x_return_status => x_return_status,
2571 x_msg_count => x_msg_count,
2572 x_msg_data => x_msg_data,
2573 p_ocev_rec => p_ocev_tbl(i));
2574 EXIT WHEN (i = p_ocev_tbl.LAST);
2575 i := p_ocev_tbl.NEXT(i);
2576 END LOOP;
2577 END IF;
2578 EXCEPTION
2579 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2580 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2581 (
2582 l_api_name,
2583 G_PKG_NAME,
2584 'OKC_API.G_RET_STS_ERROR',
2585 x_msg_count,
2586 x_msg_data,
2587 '_PVT'
2588 );
2589 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2590 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2591 (
2592 l_api_name,
2593 G_PKG_NAME,
2594 'OKC_API.G_RET_STS_UNEXP_ERROR',
2595 x_msg_count,
2596 x_msg_data,
2597 '_PVT'
2598 );
2599 WHEN OTHERS THEN
2600 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2601 (
2602 l_api_name,
2603 G_PKG_NAME,
2604 'OTHERS',
2605 x_msg_count,
2606 x_msg_data,
2607 '_PVT'
2608 );
2609 END lock_row;
2610
2611 ---------------------------------------------------------------------------
2612 -- PROCEDURE update_row
2613 ---------------------------------------------------------------------------
2614 -----------------------------------
2615 -- update_row for:OKC_OUTCOMES_B --
2616 -----------------------------------
2617 PROCEDURE update_row(
2618 p_init_msg_list IN VARCHAR2 ,
2619 x_return_status OUT NOCOPY VARCHAR2,
2620 x_msg_count OUT NOCOPY NUMBER,
2621 x_msg_data OUT NOCOPY VARCHAR2,
2622 p_oce_rec IN oce_rec_type,
2623 x_oce_rec OUT NOCOPY oce_rec_type) IS
2624
2625 l_api_version CONSTANT NUMBER := 1;
2626 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2627 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2628 l_oce_rec oce_rec_type := p_oce_rec;
2629 l_def_oce_rec oce_rec_type;
2630 l_row_notfound BOOLEAN := TRUE;
2631 ----------------------------------
2632 -- FUNCTION populate_new_record --
2633 ----------------------------------
2634 FUNCTION populate_new_record (
2635 p_oce_rec IN oce_rec_type,
2636 x_oce_rec OUT NOCOPY oce_rec_type
2637 ) RETURN VARCHAR2 IS
2638 l_oce_rec oce_rec_type;
2639 l_row_notfound BOOLEAN := TRUE;
2640 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2641 BEGIN
2642 x_oce_rec := p_oce_rec;
2643 -- Get current database values
2644 l_oce_rec := get_rec(p_oce_rec, l_row_notfound);
2645 IF (l_row_notfound) THEN
2646 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2647 END IF;
2648 IF (x_oce_rec.id = OKC_API.G_MISS_NUM)
2649 THEN
2650 x_oce_rec.id := l_oce_rec.id;
2651 END IF;
2652 IF (x_oce_rec.pdf_id = OKC_API.G_MISS_NUM)
2653 THEN
2654 x_oce_rec.pdf_id := l_oce_rec.pdf_id;
2655 END IF;
2656 IF (x_oce_rec.cnh_id = OKC_API.G_MISS_NUM)
2657 THEN
2658 x_oce_rec.cnh_id := l_oce_rec.cnh_id;
2659 END IF;
2660 IF (x_oce_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2661 THEN
2662 x_oce_rec.dnz_chr_id := l_oce_rec.dnz_chr_id;
2663 END IF;
2664 IF (x_oce_rec.success_resource_id = OKC_API.G_MISS_NUM)
2665 THEN
2666 x_oce_rec.success_resource_id := l_oce_rec.success_resource_id;
2667 END IF;
2668 IF (x_oce_rec.failure_resource_id = OKC_API.G_MISS_NUM)
2669 THEN
2670 x_oce_rec.failure_resource_id := l_oce_rec.failure_resource_id;
2671 END IF;
2672 IF (x_oce_rec.enabled_yn = OKC_API.G_MISS_CHAR)
2673 THEN
2674 x_oce_rec.enabled_yn := l_oce_rec.enabled_yn;
2675 END IF;
2676 IF (x_oce_rec.object_version_number = OKC_API.G_MISS_NUM)
2677 THEN
2678 x_oce_rec.object_version_number := l_oce_rec.object_version_number;
2679 END IF;
2680 IF (x_oce_rec.created_by = OKC_API.G_MISS_NUM)
2681 THEN
2682 x_oce_rec.created_by := l_oce_rec.created_by;
2683 END IF;
2684 IF (x_oce_rec.creation_date = OKC_API.G_MISS_DATE)
2685 THEN
2686 x_oce_rec.creation_date := l_oce_rec.creation_date;
2687 END IF;
2688 IF (x_oce_rec.last_updated_by = OKC_API.G_MISS_NUM)
2689 THEN
2690 x_oce_rec.last_updated_by := l_oce_rec.last_updated_by;
2691 END IF;
2692 IF (x_oce_rec.last_update_date = OKC_API.G_MISS_DATE)
2693 THEN
2694 x_oce_rec.last_update_date := l_oce_rec.last_update_date;
2695 END IF;
2696 IF (x_oce_rec.last_update_login = OKC_API.G_MISS_NUM)
2697 THEN
2698 x_oce_rec.last_update_login := l_oce_rec.last_update_login;
2699 END IF;
2700 IF (x_oce_rec.attribute_category = OKC_API.G_MISS_CHAR)
2701 THEN
2702 x_oce_rec.attribute_category := l_oce_rec.attribute_category;
2703 END IF;
2704 IF (x_oce_rec.attribute1 = OKC_API.G_MISS_CHAR)
2705 THEN
2706 x_oce_rec.attribute1 := l_oce_rec.attribute1;
2707 END IF;
2708 IF (x_oce_rec.attribute2 = OKC_API.G_MISS_CHAR)
2709 THEN
2710 x_oce_rec.attribute2 := l_oce_rec.attribute2;
2711 END IF;
2712 IF (x_oce_rec.attribute3 = OKC_API.G_MISS_CHAR)
2713 THEN
2714 x_oce_rec.attribute3 := l_oce_rec.attribute3;
2715 END IF;
2716 IF (x_oce_rec.attribute4 = OKC_API.G_MISS_CHAR)
2717 THEN
2718 x_oce_rec.attribute4 := l_oce_rec.attribute4;
2719 END IF;
2720 IF (x_oce_rec.attribute5 = OKC_API.G_MISS_CHAR)
2721 THEN
2722 x_oce_rec.attribute5 := l_oce_rec.attribute5;
2723 END IF;
2724 IF (x_oce_rec.attribute6 = OKC_API.G_MISS_CHAR)
2725 THEN
2726 x_oce_rec.attribute6 := l_oce_rec.attribute6;
2727 END IF;
2728 IF (x_oce_rec.attribute7 = OKC_API.G_MISS_CHAR)
2729 THEN
2730 x_oce_rec.attribute7 := l_oce_rec.attribute7;
2731 END IF;
2732 IF (x_oce_rec.attribute8 = OKC_API.G_MISS_CHAR)
2733 THEN
2734 x_oce_rec.attribute8 := l_oce_rec.attribute8;
2735 END IF;
2736 IF (x_oce_rec.attribute9 = OKC_API.G_MISS_CHAR)
2737 THEN
2738 x_oce_rec.attribute9 := l_oce_rec.attribute9;
2739 END IF;
2740 IF (x_oce_rec.attribute10 = OKC_API.G_MISS_CHAR)
2741 THEN
2742 x_oce_rec.attribute10 := l_oce_rec.attribute10;
2743 END IF;
2744 IF (x_oce_rec.attribute11 = OKC_API.G_MISS_CHAR)
2745 THEN
2746 x_oce_rec.attribute11 := l_oce_rec.attribute11;
2747 END IF;
2748 IF (x_oce_rec.attribute12 = OKC_API.G_MISS_CHAR)
2749 THEN
2750 x_oce_rec.attribute12 := l_oce_rec.attribute12;
2751 END IF;
2752 IF (x_oce_rec.attribute13 = OKC_API.G_MISS_CHAR)
2753 THEN
2754 x_oce_rec.attribute13 := l_oce_rec.attribute13;
2755 END IF;
2756 IF (x_oce_rec.attribute14 = OKC_API.G_MISS_CHAR)
2757 THEN
2758 x_oce_rec.attribute14 := l_oce_rec.attribute14;
2759 END IF;
2760 IF (x_oce_rec.attribute15 = OKC_API.G_MISS_CHAR)
2761 THEN
2762 x_oce_rec.attribute15 := l_oce_rec.attribute15;
2763 END IF;
2764 IF (x_oce_rec.application_id = OKC_API.G_MISS_NUM)
2765 THEN
2766 x_oce_rec.application_id := l_oce_rec.application_id;
2767 END IF;
2768 IF (x_oce_rec.seeded_flag = OKC_API.G_MISS_CHAR)
2769 THEN
2770 x_oce_rec.seeded_flag := l_oce_rec.seeded_flag;
2771 END IF;
2772 RETURN(l_return_status);
2773 END populate_new_record;
2774 ---------------------------------------
2775 -- Set_Attributes for:OKC_OUTCOMES_B --
2776 ---------------------------------------
2777 FUNCTION Set_Attributes (
2778 p_oce_rec IN oce_rec_type,
2779 x_oce_rec OUT NOCOPY oce_rec_type
2780 ) RETURN VARCHAR2 IS
2781 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2782 BEGIN
2783 x_oce_rec := p_oce_rec;
2784 RETURN(l_return_status);
2785 END Set_Attributes;
2786 BEGIN
2787 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2788 p_init_msg_list,
2789 '_PVT',
2790 x_return_status);
2791 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2792 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2793 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2794 RAISE OKC_API.G_EXCEPTION_ERROR;
2795 END IF;
2796 --- Setting item attributes
2797 l_return_status := Set_Attributes(
2798 p_oce_rec, -- IN
2799 l_oce_rec); -- OUT
2800 --- If any errors happen abort API
2801 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2802 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2803 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2804 RAISE OKC_API.G_EXCEPTION_ERROR;
2805 END IF;
2806 l_return_status := populate_new_record(l_oce_rec, l_def_oce_rec);
2807 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2808 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2809 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2810 RAISE OKC_API.G_EXCEPTION_ERROR;
2811 END IF;
2812 UPDATE OKC_OUTCOMES_B
2813 SET PDF_ID = l_def_oce_rec.pdf_id,
2814 CNH_ID = l_def_oce_rec.cnh_id,
2815 DNZ_CHR_ID = l_def_oce_rec.dnz_chr_id,
2816 SUCCESS_resource_ID = l_def_oce_rec.success_resource_id,
2817 FAILURE_resource_ID = l_def_oce_rec.failure_resource_id,
2818 ENABLED_YN = l_def_oce_rec.enabled_yn,
2819 OBJECT_VERSION_NUMBER = l_def_oce_rec.object_version_number,
2820 CREATED_BY = l_def_oce_rec.created_by,
2821 CREATION_DATE = l_def_oce_rec.creation_date,
2822 LAST_UPDATED_BY = l_def_oce_rec.last_updated_by,
2823 LAST_UPDATE_DATE = l_def_oce_rec.last_update_date,
2824 LAST_UPDATE_LOGIN = l_def_oce_rec.last_update_login,
2825 ATTRIBUTE_CATEGORY = l_def_oce_rec.attribute_category,
2826 ATTRIBUTE1 = l_def_oce_rec.attribute1,
2827 ATTRIBUTE2 = l_def_oce_rec.attribute2,
2828 ATTRIBUTE3 = l_def_oce_rec.attribute3,
2829 ATTRIBUTE4 = l_def_oce_rec.attribute4,
2830 ATTRIBUTE5 = l_def_oce_rec.attribute5,
2831 ATTRIBUTE6 = l_def_oce_rec.attribute6,
2832 ATTRIBUTE7 = l_def_oce_rec.attribute7,
2833 ATTRIBUTE8 = l_def_oce_rec.attribute8,
2834 ATTRIBUTE9 = l_def_oce_rec.attribute9,
2835 ATTRIBUTE10 = l_def_oce_rec.attribute10,
2836 ATTRIBUTE11 = l_def_oce_rec.attribute11,
2837 ATTRIBUTE12 = l_def_oce_rec.attribute12,
2838 ATTRIBUTE13 = l_def_oce_rec.attribute13,
2839 ATTRIBUTE14 = l_def_oce_rec.attribute14,
2840 ATTRIBUTE15 = l_def_oce_rec.attribute15,
2841 APPLICATION_ID = l_def_oce_rec.application_id,
2842 SEEDED_FLAG = l_def_oce_rec.seeded_flag
2843 WHERE ID = l_def_oce_rec.id;
2844
2845 x_oce_rec := l_def_oce_rec;
2846 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2847 EXCEPTION
2848 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2849 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2850 (
2851 l_api_name,
2852 G_PKG_NAME,
2853 'OKC_API.G_RET_STS_ERROR',
2854 x_msg_count,
2855 x_msg_data,
2856 '_PVT'
2857 );
2858 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2859 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2860 (
2861 l_api_name,
2862 G_PKG_NAME,
2863 'OKC_API.G_RET_STS_UNEXP_ERROR',
2864 x_msg_count,
2865 x_msg_data,
2866 '_PVT'
2867 );
2868 WHEN OTHERS THEN
2869 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2870 (
2871 l_api_name,
2872 G_PKG_NAME,
2873 'OTHERS',
2874 x_msg_count,
2875 x_msg_data,
2876 '_PVT'
2877 );
2878 END update_row;
2879 ------------------------------------
2880 -- update_row for:OKC_OUTCOMES_TL --
2881 ------------------------------------
2882 PROCEDURE update_row(
2883 p_init_msg_list IN VARCHAR2 ,
2884 x_return_status OUT NOCOPY VARCHAR2,
2885 x_msg_count OUT NOCOPY NUMBER,
2886 x_msg_data OUT NOCOPY VARCHAR2,
2887 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
2888 x_okc_outcomes_tl_rec OUT NOCOPY okc_outcomes_tl_rec_type) IS
2889
2890 l_api_version CONSTANT NUMBER := 1;
2891 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2892 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2893 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type := p_okc_outcomes_tl_rec;
2894 l_def_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
2895 l_row_notfound BOOLEAN := TRUE;
2896 ----------------------------------
2897 -- FUNCTION populate_new_record --
2898 ----------------------------------
2899 FUNCTION populate_new_record (
2900 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
2901 x_okc_outcomes_tl_rec OUT NOCOPY okc_outcomes_tl_rec_type
2902 ) RETURN VARCHAR2 IS
2903 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
2904 l_row_notfound BOOLEAN := TRUE;
2905 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2906 BEGIN
2907 x_okc_outcomes_tl_rec := p_okc_outcomes_tl_rec;
2908 -- Get current database values
2909 l_okc_outcomes_tl_rec := get_rec(p_okc_outcomes_tl_rec, l_row_notfound);
2910 IF (l_row_notfound) THEN
2911 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2912 END IF;
2913 IF (x_okc_outcomes_tl_rec.id = OKC_API.G_MISS_NUM)
2914 THEN
2915 x_okc_outcomes_tl_rec.id := l_okc_outcomes_tl_rec.id;
2916 END IF;
2917 IF (x_okc_outcomes_tl_rec.language = OKC_API.G_MISS_CHAR)
2918 THEN
2919 x_okc_outcomes_tl_rec.language := l_okc_outcomes_tl_rec.language;
2920 END IF;
2921 IF (x_okc_outcomes_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2922 THEN
2923 x_okc_outcomes_tl_rec.source_lang := l_okc_outcomes_tl_rec.source_lang;
2924 END IF;
2925 IF (x_okc_outcomes_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2926 THEN
2927 x_okc_outcomes_tl_rec.sfwt_flag := l_okc_outcomes_tl_rec.sfwt_flag;
2928 END IF;
2929 IF (x_okc_outcomes_tl_rec.comments = OKC_API.G_MISS_CHAR)
2930 THEN
2931 x_okc_outcomes_tl_rec.comments := l_okc_outcomes_tl_rec.comments;
2932 END IF;
2933 IF (x_okc_outcomes_tl_rec.created_by = OKC_API.G_MISS_NUM)
2934 THEN
2935 x_okc_outcomes_tl_rec.created_by := l_okc_outcomes_tl_rec.created_by;
2936 END IF;
2937 IF (x_okc_outcomes_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2938 THEN
2939 x_okc_outcomes_tl_rec.creation_date := l_okc_outcomes_tl_rec.creation_date;
2940 END IF;
2941 IF (x_okc_outcomes_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2942 THEN
2943 x_okc_outcomes_tl_rec.last_updated_by := l_okc_outcomes_tl_rec.last_updated_by;
2944 END IF;
2945 IF (x_okc_outcomes_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2946 THEN
2947 x_okc_outcomes_tl_rec.last_update_date := l_okc_outcomes_tl_rec.last_update_date;
2948 END IF;
2949 IF (x_okc_outcomes_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2950 THEN
2951 x_okc_outcomes_tl_rec.last_update_login := l_okc_outcomes_tl_rec.last_update_login;
2952 END IF;
2953 RETURN(l_return_status);
2954 END populate_new_record;
2955 ----------------------------------------
2956 -- Set_Attributes for:OKC_OUTCOMES_TL --
2957 ----------------------------------------
2958 FUNCTION Set_Attributes (
2959 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
2960 x_okc_outcomes_tl_rec OUT NOCOPY okc_outcomes_tl_rec_type
2961 ) RETURN VARCHAR2 IS
2962 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2963 BEGIN
2964 x_okc_outcomes_tl_rec := p_okc_outcomes_tl_rec;
2965 x_okc_outcomes_tl_rec.LANGUAGE := l_lang;
2966 x_okc_outcomes_tl_rec.SOURCE_LANG := l_lang;
2967 RETURN(l_return_status);
2968 END Set_Attributes;
2969 BEGIN
2970 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2971 p_init_msg_list,
2972 '_PVT',
2973 x_return_status);
2974 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2975 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2976 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2977 RAISE OKC_API.G_EXCEPTION_ERROR;
2978 END IF;
2979 --- Setting item attributes
2980 l_return_status := Set_Attributes(
2981 p_okc_outcomes_tl_rec, -- IN
2982 l_okc_outcomes_tl_rec); -- OUT
2983 --- If any errors happen abort API
2984 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2985 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2986 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2987 RAISE OKC_API.G_EXCEPTION_ERROR;
2988 END IF;
2989 l_return_status := populate_new_record(l_okc_outcomes_tl_rec, l_def_okc_outcomes_tl_rec);
2990 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2991 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2992 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2993 RAISE OKC_API.G_EXCEPTION_ERROR;
2994 END IF;
2995 UPDATE OKC_OUTCOMES_TL
2996 SET COMMENTS = l_def_okc_outcomes_tl_rec.comments,
2997 SOURCE_LANG = l_def_okc_outcomes_tl_rec.source_lang,
2998 CREATED_BY = l_def_okc_outcomes_tl_rec.created_by,
2999 CREATION_DATE = l_def_okc_outcomes_tl_rec.creation_date,
3000 LAST_UPDATED_BY = l_def_okc_outcomes_tl_rec.last_updated_by,
3001 LAST_UPDATE_DATE = l_def_okc_outcomes_tl_rec.last_update_date,
3002 LAST_UPDATE_LOGIN = l_def_okc_outcomes_tl_rec.last_update_login
3003 WHERE ID = l_def_okc_outcomes_tl_rec.id
3004 AND USERENV('LANG') IN (SOURCE_LANG,LANGUAGE);
3005 --AND SOURCE_LANG = USERENV('LANG');
3006
3007 UPDATE OKC_OUTCOMES_TL
3008 SET SFWT_FLAG = 'Y'
3009 WHERE ID = l_def_okc_outcomes_tl_rec.id
3010 AND SOURCE_LANG <> USERENV('LANG');
3011
3012 x_okc_outcomes_tl_rec := l_def_okc_outcomes_tl_rec;
3013 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3014 EXCEPTION
3015 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3016 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3017 (
3018 l_api_name,
3019 G_PKG_NAME,
3020 'OKC_API.G_RET_STS_ERROR',
3021 x_msg_count,
3022 x_msg_data,
3023 '_PVT'
3024 );
3025 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3026 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3027 (
3028 l_api_name,
3029 G_PKG_NAME,
3030 'OKC_API.G_RET_STS_UNEXP_ERROR',
3031 x_msg_count,
3032 x_msg_data,
3033 '_PVT'
3034 );
3035 WHEN OTHERS THEN
3036 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3037 (
3038 l_api_name,
3039 G_PKG_NAME,
3040 'OTHERS',
3041 x_msg_count,
3042 x_msg_data,
3043 '_PVT'
3044 );
3045 END update_row;
3046 -----------------------------------
3047 -- update_row for:OKC_OUTCOMES_V --
3048 -----------------------------------
3049 PROCEDURE update_row(
3050 p_api_version IN NUMBER,
3051 p_init_msg_list IN VARCHAR2 ,
3052 x_return_status OUT NOCOPY VARCHAR2,
3053 x_msg_count OUT NOCOPY NUMBER,
3054 x_msg_data OUT NOCOPY VARCHAR2,
3055 p_ocev_rec IN ocev_rec_type,
3056 x_ocev_rec OUT NOCOPY ocev_rec_type) IS
3057
3058 l_api_version CONSTANT NUMBER := 1;
3059 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3060 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3061 l_ocev_rec ocev_rec_type := p_ocev_rec;
3062 l_def_ocev_rec ocev_rec_type;
3063 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
3064 lx_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
3065 l_oce_rec oce_rec_type;
3066 lx_oce_rec oce_rec_type;
3067 -------------------------------
3068 -- FUNCTION fill_who_columns --
3069 -------------------------------
3070 FUNCTION fill_who_columns (
3071 p_ocev_rec IN ocev_rec_type
3072 ) RETURN ocev_rec_type IS
3073 l_ocev_rec ocev_rec_type := p_ocev_rec;
3074 BEGIN
3075 l_ocev_rec.LAST_UPDATE_DATE := SYSDATE;
3076 l_ocev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3077 l_ocev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3078 RETURN(l_ocev_rec);
3079 END fill_who_columns;
3080 ----------------------------------
3081 -- FUNCTION populate_new_record --
3082 ----------------------------------
3083 FUNCTION populate_new_record (
3084 p_ocev_rec IN ocev_rec_type,
3085 x_ocev_rec OUT NOCOPY ocev_rec_type
3086 ) RETURN VARCHAR2 IS
3087 l_ocev_rec ocev_rec_type;
3088 l_row_notfound BOOLEAN := TRUE;
3089 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3090 BEGIN
3091 x_ocev_rec := p_ocev_rec;
3092 -- Get current database values
3093 l_ocev_rec := get_rec(p_ocev_rec, l_row_notfound);
3094 IF (l_row_notfound) THEN
3095 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3096 END IF;
3097 IF (x_ocev_rec.id = OKC_API.G_MISS_NUM)
3098 THEN
3099 x_ocev_rec.id := l_ocev_rec.id;
3100 END IF;
3101 IF (x_ocev_rec.object_version_number = OKC_API.G_MISS_NUM)
3102 THEN
3103 x_ocev_rec.object_version_number := l_ocev_rec.object_version_number;
3104 END IF;
3105 IF (x_ocev_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3106 THEN
3107 x_ocev_rec.sfwt_flag := l_ocev_rec.sfwt_flag;
3108 END IF;
3109 IF (x_ocev_rec.pdf_id = OKC_API.G_MISS_NUM)
3110 THEN
3111 x_ocev_rec.pdf_id := l_ocev_rec.pdf_id;
3112 END IF;
3113 IF (x_ocev_rec.cnh_id = OKC_API.G_MISS_NUM)
3114 THEN
3115 x_ocev_rec.cnh_id := l_ocev_rec.cnh_id;
3116 END IF;
3117 IF (x_ocev_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
3118 THEN
3119 x_ocev_rec.dnz_chr_id := l_ocev_rec.dnz_chr_id;
3120 END IF;
3121 IF (x_ocev_rec.success_resource_id = OKC_API.G_MISS_NUM)
3122 THEN
3123 x_ocev_rec.success_resource_id := l_ocev_rec.success_resource_id;
3124 END IF;
3125 IF (x_ocev_rec.failure_resource_id = OKC_API.G_MISS_NUM)
3126 THEN
3127 x_ocev_rec.failure_resource_id := l_ocev_rec.failure_resource_id;
3128 END IF;
3129 IF (x_ocev_rec.enabled_yn = OKC_API.G_MISS_CHAR)
3130 THEN
3131 x_ocev_rec.enabled_yn := l_ocev_rec.enabled_yn;
3132 END IF;
3133 IF (x_ocev_rec.comments = OKC_API.G_MISS_CHAR)
3134 THEN
3135 x_ocev_rec.comments := l_ocev_rec.comments;
3136 END IF;
3137 IF (x_ocev_rec.application_id = OKC_API.G_MISS_NUM)
3138 THEN
3139 x_ocev_rec.application_id := l_ocev_rec.application_id;
3140 END IF;
3141 IF (x_ocev_rec.seeded_flag = OKC_API.G_MISS_CHAR)
3142 THEN
3143 x_ocev_rec.seeded_flag := l_ocev_rec.seeded_flag;
3144 END IF;
3145 IF (x_ocev_rec.attribute_category = OKC_API.G_MISS_CHAR)
3146 THEN
3147 x_ocev_rec.attribute_category := l_ocev_rec.attribute_category;
3148 END IF;
3149 IF (x_ocev_rec.attribute1 = OKC_API.G_MISS_CHAR)
3150 THEN
3151 x_ocev_rec.attribute1 := l_ocev_rec.attribute1;
3152 END IF;
3153 IF (x_ocev_rec.attribute2 = OKC_API.G_MISS_CHAR)
3154 THEN
3155 x_ocev_rec.attribute2 := l_ocev_rec.attribute2;
3156 END IF;
3157 IF (x_ocev_rec.attribute3 = OKC_API.G_MISS_CHAR)
3158 THEN
3159 x_ocev_rec.attribute3 := l_ocev_rec.attribute3;
3160 END IF;
3161 IF (x_ocev_rec.attribute4 = OKC_API.G_MISS_CHAR)
3162 THEN
3163 x_ocev_rec.attribute4 := l_ocev_rec.attribute4;
3164 END IF;
3165 IF (x_ocev_rec.attribute5 = OKC_API.G_MISS_CHAR)
3166 THEN
3167 x_ocev_rec.attribute5 := l_ocev_rec.attribute5;
3168 END IF;
3169 IF (x_ocev_rec.attribute6 = OKC_API.G_MISS_CHAR)
3170 THEN
3171 x_ocev_rec.attribute6 := l_ocev_rec.attribute6;
3172 END IF;
3173 IF (x_ocev_rec.attribute7 = OKC_API.G_MISS_CHAR)
3174 THEN
3175 x_ocev_rec.attribute7 := l_ocev_rec.attribute7;
3176 END IF;
3177 IF (x_ocev_rec.attribute8 = OKC_API.G_MISS_CHAR)
3178 THEN
3179 x_ocev_rec.attribute8 := l_ocev_rec.attribute8;
3180 END IF;
3181 IF (x_ocev_rec.attribute9 = OKC_API.G_MISS_CHAR)
3182 THEN
3183 x_ocev_rec.attribute9 := l_ocev_rec.attribute9;
3184 END IF;
3185 IF (x_ocev_rec.attribute10 = OKC_API.G_MISS_CHAR)
3186 THEN
3187 x_ocev_rec.attribute10 := l_ocev_rec.attribute10;
3188 END IF;
3189 IF (x_ocev_rec.attribute11 = OKC_API.G_MISS_CHAR)
3190 THEN
3191 x_ocev_rec.attribute11 := l_ocev_rec.attribute11;
3192 END IF;
3193 IF (x_ocev_rec.attribute12 = OKC_API.G_MISS_CHAR)
3194 THEN
3195 x_ocev_rec.attribute12 := l_ocev_rec.attribute12;
3196 END IF;
3197 IF (x_ocev_rec.attribute13 = OKC_API.G_MISS_CHAR)
3198 THEN
3199 x_ocev_rec.attribute13 := l_ocev_rec.attribute13;
3200 END IF;
3201 IF (x_ocev_rec.attribute14 = OKC_API.G_MISS_CHAR)
3202 THEN
3203 x_ocev_rec.attribute14 := l_ocev_rec.attribute14;
3204 END IF;
3205 IF (x_ocev_rec.attribute15 = OKC_API.G_MISS_CHAR)
3206 THEN
3207 x_ocev_rec.attribute15 := l_ocev_rec.attribute15;
3208 END IF;
3209 IF (x_ocev_rec.created_by = OKC_API.G_MISS_NUM)
3210 THEN
3211 x_ocev_rec.created_by := l_ocev_rec.created_by;
3212 END IF;
3213 IF (x_ocev_rec.creation_date = OKC_API.G_MISS_DATE)
3214 THEN
3215 x_ocev_rec.creation_date := l_ocev_rec.creation_date;
3216 END IF;
3217 IF (x_ocev_rec.last_updated_by = OKC_API.G_MISS_NUM)
3218 THEN
3219 x_ocev_rec.last_updated_by := l_ocev_rec.last_updated_by;
3220 END IF;
3221 IF (x_ocev_rec.last_update_date = OKC_API.G_MISS_DATE)
3222 THEN
3223 x_ocev_rec.last_update_date := l_ocev_rec.last_update_date;
3224 END IF;
3225 IF (x_ocev_rec.last_update_login = OKC_API.G_MISS_NUM)
3226 THEN
3227 x_ocev_rec.last_update_login := l_ocev_rec.last_update_login;
3228 END IF;
3229 RETURN(l_return_status);
3230 END populate_new_record;
3231 ---------------------------------------
3232 -- Set_Attributes for:OKC_OUTCOMES_V --
3233 ---------------------------------------
3234 FUNCTION Set_Attributes (
3235 p_ocev_rec IN ocev_rec_type,
3236 x_ocev_rec OUT NOCOPY ocev_rec_type
3237 ) RETURN VARCHAR2 IS
3238 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3239 BEGIN
3240 x_ocev_rec := p_ocev_rec;
3241 x_ocev_rec.OBJECT_VERSION_NUMBER := NVL(x_ocev_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3242 RETURN(l_return_status);
3243 END Set_Attributes;
3244 BEGIN
3245 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3246 G_PKG_NAME,
3247 p_init_msg_list,
3248 l_api_version,
3249 p_api_version,
3250 '_PVT',
3251 x_return_status);
3252 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3253 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3254 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3255 RAISE OKC_API.G_EXCEPTION_ERROR;
3256 END IF;
3257 -- Seed data should not be updated unless user is DATAMERGE
3258 IF l_ocev_rec.last_updated_by = 1 THEN
3259 IF l_ocev_rec.seeded_flag = 'Y' THEN
3260 OKC_API.set_message(p_app_name => G_APP_NAME,
3261 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
3262 RAISE OKC_API.G_EXCEPTION_ERROR;
3263 END IF;
3264 END IF;
3265 --- Setting item attributes
3266 l_return_status := Set_Attributes(
3267 p_ocev_rec, -- IN
3268 l_ocev_rec); -- OUT
3269 --- If any errors happen abort API
3270 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3272 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3273 RAISE OKC_API.G_EXCEPTION_ERROR;
3274 END IF;
3275 l_return_status := populate_new_record(l_ocev_rec, l_def_ocev_rec);
3276 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3277 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3278 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3279 RAISE OKC_API.G_EXCEPTION_ERROR;
3280 END IF;
3281 l_def_ocev_rec := fill_who_columns(l_def_ocev_rec);
3282 --- Validate all non-missing attributes (Item Level Validation)
3283 l_return_status := Validate_Attributes(l_def_ocev_rec);
3284 --- If any errors happen abort API
3285 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3286 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3287 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3288 RAISE OKC_API.G_EXCEPTION_ERROR;
3289 END IF;
3290 l_return_status := Validate_Record(l_def_ocev_rec);
3291 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3292 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3293 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3294 RAISE OKC_API.G_EXCEPTION_ERROR;
3295 END IF;
3296
3297 --------------------------------------
3298 -- Move VIEW record to "Child" records
3299 --------------------------------------
3300 migrate(l_def_ocev_rec, l_okc_outcomes_tl_rec);
3301 migrate(l_def_ocev_rec, l_oce_rec);
3302 --------------------------------------------
3303 -- Call the UPDATE_ROW for each child record
3304 --------------------------------------------
3305 update_row(
3306 p_init_msg_list,
3307 x_return_status,
3308 x_msg_count,
3309 x_msg_data,
3310 l_okc_outcomes_tl_rec,
3311 lx_okc_outcomes_tl_rec
3312 );
3313 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3314 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3315 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3316 RAISE OKC_API.G_EXCEPTION_ERROR;
3317 END IF;
3318 migrate(lx_okc_outcomes_tl_rec, l_def_ocev_rec);
3319 update_row(
3320 p_init_msg_list,
3321 x_return_status,
3322 x_msg_count,
3323 x_msg_data,
3324 l_oce_rec,
3325 lx_oce_rec
3326 );
3327 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3328 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3329 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3330 RAISE OKC_API.G_EXCEPTION_ERROR;
3331 END IF;
3332 migrate(lx_oce_rec, l_def_ocev_rec);
3333 x_ocev_rec := l_def_ocev_rec;
3334 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3335 EXCEPTION
3336 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3337 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3338 (
3339 l_api_name,
3340 G_PKG_NAME,
3341 'OKC_API.G_RET_STS_ERROR',
3342 x_msg_count,
3343 x_msg_data,
3344 '_PVT'
3345 );
3346 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3347 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3348 (
3349 l_api_name,
3350 G_PKG_NAME,
3351 'OKC_API.G_RET_STS_UNEXP_ERROR',
3352 x_msg_count,
3353 x_msg_data,
3354 '_PVT'
3355 );
3356 WHEN OTHERS THEN
3357 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3358 (
3359 l_api_name,
3360 G_PKG_NAME,
3361 'OTHERS',
3362 x_msg_count,
3363 x_msg_data,
3364 '_PVT'
3365 );
3366 END update_row;
3367 ----------------------------------------
3368 -- PL/SQL TBL update_row for:OCEV_TBL --
3369 ----------------------------------------
3370 PROCEDURE update_row(
3371 p_api_version IN NUMBER,
3372 p_init_msg_list IN VARCHAR2 ,
3373 x_return_status OUT NOCOPY VARCHAR2,
3374 x_msg_count OUT NOCOPY NUMBER,
3375 x_msg_data OUT NOCOPY VARCHAR2,
3376 p_ocev_tbl IN ocev_tbl_type,
3377 x_ocev_tbl OUT NOCOPY ocev_tbl_type) IS
3378
3379 l_api_version CONSTANT NUMBER := 1;
3380 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3381 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3382 i NUMBER := 0;
3383 BEGIN
3384 OKC_API.init_msg_list(p_init_msg_list);
3385 -- Make sure PL/SQL table has records in it before passing
3386 IF (p_ocev_tbl.COUNT > 0) THEN
3387 i := p_ocev_tbl.FIRST;
3388 LOOP
3389 update_row (
3390 p_api_version => p_api_version,
3391 p_init_msg_list => OKC_API.G_FALSE,
3392 x_return_status => x_return_status,
3393 x_msg_count => x_msg_count,
3394 x_msg_data => x_msg_data,
3395 p_ocev_rec => p_ocev_tbl(i),
3396 x_ocev_rec => x_ocev_tbl(i));
3397 EXIT WHEN (i = p_ocev_tbl.LAST);
3398 i := p_ocev_tbl.NEXT(i);
3399 END LOOP;
3400 END IF;
3401 EXCEPTION
3402 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3403 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3404 (
3405 l_api_name,
3406 G_PKG_NAME,
3407 'OKC_API.G_RET_STS_ERROR',
3408 x_msg_count,
3409 x_msg_data,
3410 '_PVT'
3411 );
3412 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3413 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3414 (
3415 l_api_name,
3416 G_PKG_NAME,
3417 'OKC_API.G_RET_STS_UNEXP_ERROR',
3418 x_msg_count,
3419 x_msg_data,
3420 '_PVT'
3421 );
3422 WHEN OTHERS THEN
3423 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3424 (
3425 l_api_name,
3426 G_PKG_NAME,
3427 'OTHERS',
3428 x_msg_count,
3429 x_msg_data,
3430 '_PVT'
3431 );
3432 END update_row;
3433
3434 ---------------------------------------------------------------------------
3435 -- PROCEDURE delete_row
3436 ---------------------------------------------------------------------------
3437 -----------------------------------
3438 -- delete_row for:OKC_OUTCOMES_B --
3439 -----------------------------------
3440 PROCEDURE delete_row(
3441 p_init_msg_list IN VARCHAR2 ,
3442 x_return_status OUT NOCOPY VARCHAR2,
3443 x_msg_count OUT NOCOPY NUMBER,
3444 x_msg_data OUT NOCOPY VARCHAR2,
3445 p_oce_rec IN oce_rec_type) IS
3446
3447 l_api_version CONSTANT NUMBER := 1;
3448 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3449 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3450 l_oce_rec oce_rec_type:= p_oce_rec;
3451 l_row_notfound BOOLEAN := TRUE;
3452 BEGIN
3453 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3454 p_init_msg_list,
3455 '_PVT',
3456 x_return_status);
3457 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3458 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3459 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3460 RAISE OKC_API.G_EXCEPTION_ERROR;
3461 END IF;
3462 DELETE FROM OKC_OUTCOMES_B
3463 WHERE ID = l_oce_rec.id;
3464
3465 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3466 EXCEPTION
3467 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3468 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3469 (
3470 l_api_name,
3471 G_PKG_NAME,
3472 'OKC_API.G_RET_STS_ERROR',
3473 x_msg_count,
3474 x_msg_data,
3475 '_PVT'
3476 );
3477 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3478 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3479 (
3480 l_api_name,
3481 G_PKG_NAME,
3482 'OKC_API.G_RET_STS_UNEXP_ERROR',
3483 x_msg_count,
3484 x_msg_data,
3485 '_PVT'
3486 );
3487 WHEN OTHERS THEN
3488 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3489 (
3490 l_api_name,
3491 G_PKG_NAME,
3492 'OTHERS',
3493 x_msg_count,
3494 x_msg_data,
3495 '_PVT'
3496 );
3497 END delete_row;
3498 ------------------------------------
3499 -- delete_row for:OKC_OUTCOMES_TL --
3500 ------------------------------------
3501 PROCEDURE delete_row(
3502 p_init_msg_list IN VARCHAR2 ,
3503 x_return_status OUT NOCOPY VARCHAR2,
3504 x_msg_count OUT NOCOPY NUMBER,
3505 x_msg_data OUT NOCOPY VARCHAR2,
3506 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type) IS
3507
3508 l_api_version CONSTANT NUMBER := 1;
3509 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3510 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3511 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type:= p_okc_outcomes_tl_rec;
3512 l_row_notfound BOOLEAN := TRUE;
3513 ----------------------------------------
3514 -- Set_Attributes for:OKC_OUTCOMES_TL --
3515 ----------------------------------------
3516 FUNCTION Set_Attributes (
3517 p_okc_outcomes_tl_rec IN okc_outcomes_tl_rec_type,
3518 x_okc_outcomes_tl_rec OUT NOCOPY okc_outcomes_tl_rec_type
3519 ) RETURN VARCHAR2 IS
3520 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3521 BEGIN
3522 x_okc_outcomes_tl_rec := p_okc_outcomes_tl_rec;
3523 x_okc_outcomes_tl_rec.LANGUAGE := l_lang;
3524 RETURN(l_return_status);
3525 END Set_Attributes;
3526 BEGIN
3527 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3528 p_init_msg_list,
3529 '_PVT',
3530 x_return_status);
3531 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3532 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3533 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3534 RAISE OKC_API.G_EXCEPTION_ERROR;
3535 END IF;
3536 --- Setting item attributes
3537 l_return_status := Set_Attributes(
3538 p_okc_outcomes_tl_rec, -- IN
3539 l_okc_outcomes_tl_rec); -- OUT
3540 --- If any errors happen abort API
3541 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3542 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3543 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3544 RAISE OKC_API.G_EXCEPTION_ERROR;
3545 END IF;
3546 DELETE FROM OKC_OUTCOMES_TL
3547 WHERE ID = l_okc_outcomes_tl_rec.id;
3548
3549 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3550 EXCEPTION
3551 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3552 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3553 (
3554 l_api_name,
3555 G_PKG_NAME,
3556 'OKC_API.G_RET_STS_ERROR',
3557 x_msg_count,
3558 x_msg_data,
3559 '_PVT'
3560 );
3561 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3562 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3563 (
3564 l_api_name,
3565 G_PKG_NAME,
3566 'OKC_API.G_RET_STS_UNEXP_ERROR',
3567 x_msg_count,
3568 x_msg_data,
3569 '_PVT'
3570 );
3571 WHEN OTHERS THEN
3572 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3573 (
3574 l_api_name,
3575 G_PKG_NAME,
3576 'OTHERS',
3577 x_msg_count,
3578 x_msg_data,
3579 '_PVT'
3580 );
3581 END delete_row;
3582 -----------------------------------
3583 -- delete_row for:OKC_OUTCOMES_V --
3584 -----------------------------------
3585 PROCEDURE delete_row(
3586 p_api_version IN NUMBER,
3587 p_init_msg_list IN VARCHAR2 ,
3588 x_return_status OUT NOCOPY VARCHAR2,
3589 x_msg_count OUT NOCOPY NUMBER,
3590 x_msg_data OUT NOCOPY VARCHAR2,
3591 p_ocev_rec IN ocev_rec_type) IS
3592
3593 l_api_version CONSTANT NUMBER := 1;
3594 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3595 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3596 l_ocev_rec ocev_rec_type := p_ocev_rec;
3597 l_okc_outcomes_tl_rec okc_outcomes_tl_rec_type;
3598 l_oce_rec oce_rec_type;
3599 BEGIN
3600 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3601 G_PKG_NAME,
3602 p_init_msg_list,
3603 l_api_version,
3604 p_api_version,
3605 '_PVT',
3606 x_return_status);
3607 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3608 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3609 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3610 RAISE OKC_API.G_EXCEPTION_ERROR;
3611 END IF;
3612 -- Seed data should not be deleted unless user is DATAMERGE
3613 IF l_ocev_rec.last_updated_by <> 1 THEN
3614 IF l_ocev_rec.seeded_flag = 'Y' THEN
3615 OKC_API.set_message(p_app_name => G_APP_NAME,
3616 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
3617 RAISE OKC_API.G_EXCEPTION_ERROR;
3618 END IF;
3619 END IF;
3620 --------------------------------------
3621 -- Move VIEW record to "Child" records
3622 --------------------------------------
3623 migrate(l_ocev_rec, l_okc_outcomes_tl_rec);
3624 migrate(l_ocev_rec, l_oce_rec);
3625 --------------------------------------------
3626 -- Call the DELETE_ROW for each child record
3627 --------------------------------------------
3628 delete_row(
3629 p_init_msg_list,
3630 x_return_status,
3631 x_msg_count,
3632 x_msg_data,
3633 l_okc_outcomes_tl_rec
3634 );
3635 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3636 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3637 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3638 RAISE OKC_API.G_EXCEPTION_ERROR;
3639 END IF;
3640 delete_row(
3641 p_init_msg_list,
3642 x_return_status,
3643 x_msg_count,
3644 x_msg_data,
3645 l_oce_rec
3646 );
3647 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3648 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3649 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3650 RAISE OKC_API.G_EXCEPTION_ERROR;
3651 END IF;
3652 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3653 EXCEPTION
3654 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3655 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3656 (
3657 l_api_name,
3658 G_PKG_NAME,
3659 'OKC_API.G_RET_STS_ERROR',
3660 x_msg_count,
3661 x_msg_data,
3662 '_PVT'
3663 );
3664 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3665 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3666 (
3667 l_api_name,
3668 G_PKG_NAME,
3669 'OKC_API.G_RET_STS_UNEXP_ERROR',
3670 x_msg_count,
3671 x_msg_data,
3672 '_PVT'
3673 );
3674 WHEN OTHERS THEN
3675 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3676 (
3677 l_api_name,
3678 G_PKG_NAME,
3679 'OTHERS',
3680 x_msg_count,
3681 x_msg_data,
3682 '_PVT'
3683 );
3684 END delete_row;
3685 ----------------------------------------
3686 -- PL/SQL TBL delete_row for:OCEV_TBL --
3687 ----------------------------------------
3688 PROCEDURE delete_row(
3689 p_api_version IN NUMBER,
3690 p_init_msg_list IN VARCHAR2 ,
3691 x_return_status OUT NOCOPY VARCHAR2,
3692 x_msg_count OUT NOCOPY NUMBER,
3693 x_msg_data OUT NOCOPY VARCHAR2,
3694 p_ocev_tbl IN ocev_tbl_type) IS
3695
3696 l_api_version CONSTANT NUMBER := 1;
3697 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3698 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3699 i NUMBER := 0;
3700 BEGIN
3701 OKC_API.init_msg_list(p_init_msg_list);
3702 -- Make sure PL/SQL table has records in it before passing
3703 IF (p_ocev_tbl.COUNT > 0) THEN
3704 i := p_ocev_tbl.FIRST;
3705 LOOP
3706 delete_row (
3707 p_api_version => p_api_version,
3708 p_init_msg_list => OKC_API.G_FALSE,
3709 x_return_status => x_return_status,
3710 x_msg_count => x_msg_count,
3711 x_msg_data => x_msg_data,
3712 p_ocev_rec => p_ocev_tbl(i));
3713 EXIT WHEN (i = p_ocev_tbl.LAST);
3714 i := p_ocev_tbl.NEXT(i);
3715 END LOOP;
3716 END IF;
3717 EXCEPTION
3718 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3719 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3720 (
3721 l_api_name,
3722 G_PKG_NAME,
3723 'OKC_API.G_RET_STS_ERROR',
3724 x_msg_count,
3725 x_msg_data,
3726 '_PVT'
3727 );
3728 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3729 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3730 (
3731 l_api_name,
3732 G_PKG_NAME,
3733 'OKC_API.G_RET_STS_UNEXP_ERROR',
3734 x_msg_count,
3735 x_msg_data,
3736 '_PVT'
3737 );
3738 WHEN OTHERS THEN
3739 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3740 (
3741 l_api_name,
3742 G_PKG_NAME,
3743 'OTHERS',
3744 x_msg_count,
3745 x_msg_data,
3746 '_PVT'
3747 );
3748 END delete_row;
3749 ---------------------------------------------------------------
3750 -- Procedure for mass insert in OKC_OUTCOMES _B and TL tables
3751 ---------------------------------------------------------------
3752 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_ocev_tbl ocev_tbl_type) IS
3753 l_tabsize NUMBER := p_ocev_tbl.COUNT;
3754 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3755
3756 in_id OKC_DATATYPES.NumberTabTyp;
3757 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3758 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
3759 in_pdf_id OKC_DATATYPES.NumberTabTyp;
3760 in_cnh_id OKC_DATATYPES.NumberTabTyp;
3761 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3762 in_success_resource_id OKC_DATATYPES.NumberTabTyp;
3763 in_failure_resource_id OKC_DATATYPES.NumberTabTyp;
3764 in_enabled_yn OKC_DATATYPES.Var3TabTyp;
3765 in_comments OKC_DATATYPES.Var1995TabTyp;
3766 in_application_id OKC_DATATYPES.NumberTabTyp;
3767 in_seeded_flag OKC_DATATYPES.Var3TabTyp;
3768 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3769 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3770 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3771 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3772 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3773 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3774 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3775 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3776 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3777 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3778 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3779 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3780 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3781 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3782 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3783 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3784 in_created_by OKC_DATATYPES.NumberTabTyp;
3785 in_creation_date OKC_DATATYPES.DateTabTyp;
3786 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3787 in_last_update_date OKC_DATATYPES.DateTabTyp;
3788 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3789 j NUMBER := 0;
3790 i NUMBER := p_ocev_tbl.FIRST;
3791 BEGIN
3792
3793 --Initialize return status
3794 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3795
3796 while i is not null
3797 LOOP
3798 j := j + 1;
3799 in_id (j) := p_ocev_tbl(i).id;
3800 in_object_version_number (j) := p_ocev_tbl(i).object_version_number;
3801 in_sfwt_flag (j) := p_ocev_tbl(i).sfwt_flag;
3802 in_pdf_id (j) := p_ocev_tbl(i).pdf_id;
3803 in_cnh_id (j) := p_ocev_tbl(i).cnh_id;
3804 in_dnz_chr_id (j) := p_ocev_tbl(i).dnz_chr_id;
3805 in_success_resource_id (j) := p_ocev_tbl(i).success_resource_id;
3806 in_failure_resource_id (j) := p_ocev_tbl(i).failure_resource_id;
3807 in_enabled_yn (j) := p_ocev_tbl(i).enabled_yn;
3808 in_comments (j) := p_ocev_tbl(i).comments;
3809 in_application_id (j) := p_ocev_tbl(i).application_id;
3810 in_seeded_flag (j) := p_ocev_tbl(i).seeded_flag;
3811 in_attribute_category (j) := p_ocev_tbl(i).attribute_category;
3812 in_attribute1 (j) := p_ocev_tbl(i).attribute1;
3813 in_attribute2 (j) := p_ocev_tbl(i).attribute2;
3814 in_attribute3 (j) := p_ocev_tbl(i).attribute3;
3815 in_attribute4 (j) := p_ocev_tbl(i).attribute4;
3816 in_attribute5 (j) := p_ocev_tbl(i).attribute5;
3817 in_attribute6 (j) := p_ocev_tbl(i).attribute6;
3818 in_attribute7 (j) := p_ocev_tbl(i).attribute7;
3819 in_attribute8 (j) := p_ocev_tbl(i).attribute8;
3820 in_attribute9 (j) := p_ocev_tbl(i).attribute9;
3821 in_attribute10 (j) := p_ocev_tbl(i).attribute10;
3822 in_attribute11 (j) := p_ocev_tbl(i).attribute11;
3823 in_attribute12 (j) := p_ocev_tbl(i).attribute12;
3824 in_attribute13 (j) := p_ocev_tbl(i).attribute13;
3825 in_attribute14 (j) := p_ocev_tbl(i).attribute14;
3826 in_attribute15 (j) := p_ocev_tbl(i).attribute15;
3827 in_created_by (j) := p_ocev_tbl(i).created_by;
3828 in_creation_date (j) := p_ocev_tbl(i).creation_date;
3829 in_last_updated_by (j) := p_ocev_tbl(i).last_updated_by;
3830 in_last_update_date (j) := p_ocev_tbl(i).last_update_date;
3831 in_last_update_login (j) := p_ocev_tbl(i).last_update_login;
3832 i := p_ocev_tbl.NEXT(i);
3833 END LOOP;
3834
3835 FORALL i in 1..l_tabsize
3836 INSERT
3837 INTO OKC_OUTCOMES_B
3838 (
3839 id,
3840 pdf_id,
3841 cnh_id,
3842 dnz_chr_id,
3843 success_resource_id,
3844 failure_resource_id,
3845 enabled_yn,
3846 object_version_number,
3847 created_by,
3848 creation_date,
3849 last_updated_by,
3850 last_update_date,
3851 last_update_login,
3852 attribute_category,
3853 attribute1,
3854 attribute2,
3855 attribute3,
3856 attribute4,
3857 attribute5,
3858 attribute6,
3859 attribute7,
3860 attribute8,
3861 attribute9,
3862 attribute10,
3863 attribute11,
3864 attribute12,
3865 attribute13,
3866 attribute14,
3867 attribute15,
3868 application_id,
3869 seeded_flag
3870 )
3871 VALUES (
3872 in_id(i),
3873 in_pdf_id(i),
3874 in_cnh_id(i),
3875 in_dnz_chr_id(i),
3876 in_success_resource_id(i),
3877 in_failure_resource_id(i),
3878 in_enabled_yn(i),
3879 in_object_version_number(i),
3880 in_created_by(i),
3881 in_creation_date(i),
3882 in_last_updated_by(i),
3883 in_last_update_date(i),
3884 in_last_update_login(i),
3885 in_attribute_category(i),
3886 in_attribute1(i),
3887 in_attribute2(i),
3888 in_attribute3(i),
3889 in_attribute4(i),
3890 in_attribute5(i),
3891 in_attribute6(i),
3892 in_attribute7(i),
3893 in_attribute8(i),
3894 in_attribute9(i),
3895 in_attribute10(i),
3896 in_attribute11(i),
3897 in_attribute12(i),
3898 in_attribute13(i),
3899 in_attribute14(i),
3900 in_attribute15(i),
3901 in_application_id(i),
3902 in_seeded_flag(i)
3903 );
3904
3905 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
3906 FORALL i in 1..l_tabsize
3907 INSERT INTO OKC_OUTCOMES_TL(
3908 id,
3909 language,
3910 source_lang,
3911 sfwt_flag,
3912 comments,
3913 created_by,
3914 creation_date,
3915 last_updated_by,
3916 last_update_date,
3917 last_update_login
3918 )
3919 VALUES (
3920 in_id(i),
3921 OKC_UTIL.g_language_code(lang_i),
3922 l_source_lang,
3923 in_sfwt_flag(i),
3924 in_comments(i),
3925 in_created_by(i),
3926 in_creation_date(i),
3927 in_last_updated_by(i),
3928 in_last_update_date(i),
3929 in_last_update_login(i)
3930 );
3931 END LOOP;
3932 EXCEPTION
3933 WHEN OTHERS THEN
3934
3935 -- store SQL error message on message stack
3936 OKC_API.SET_MESSAGE(
3937 p_app_name => G_APP_NAME,
3938 p_msg_name => G_UNEXPECTED_ERROR,
3939 p_token1 => G_SQLCODE_TOKEN,
3940 p_token1_value => SQLCODE,
3941 p_token2 => G_SQLERRM_TOKEN,
3942 p_token2_value => SQLERRM);
3943 -- notify caller of an error as UNEXPECTED error
3944 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3945
3946 -- RAISE;
3947 END INSERT_ROW_UPG;
3948
3949 --This function is called from versioning API OKC_VERSION_PVT
3950 --Old Location: OKCRVERB.pls
3951 --New Location: Base Table API
3952
3953 FUNCTION create_version(
3954 p_chr_id IN NUMBER,
3955 p_major_version IN NUMBER
3956 ) RETURN VARCHAR2 IS
3957
3958 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3959
3960 BEGIN
3961 INSERT INTO okc_outcomes_bh
3962 (
3963 major_version,
3964 id,
3965 pdf_id,
3966 cnh_id,
3967 dnz_chr_id,
3968 success_resource_id,
3969 failure_resource_id,
3970 enabled_yn,
3971 object_version_number,
3972 created_by,
3973 creation_date,
3974 last_updated_by,
3975 last_update_date,
3976 last_update_login,
3977 attribute_category,
3978 attribute1,
3979 attribute2,
3980 attribute3,
3981 attribute4,
3982 attribute5,
3983 attribute6,
3984 attribute7,
3985 attribute8,
3986 attribute9,
3987 attribute10,
3988 attribute11,
3989 attribute12,
3990 attribute13,
3991 attribute14,
3992 attribute15,
3993 application_id,
3994 seeded_flag
3995 )
3996 SELECT
3997 p_major_version,
3998 id,
3999 pdf_id,
4000 cnh_id,
4001 dnz_chr_id,
4002 success_resource_id,
4003 failure_resource_id,
4004 enabled_yn,
4005 object_version_number,
4006 created_by,
4007 creation_date,
4008 last_updated_by,
4009 last_update_date,
4010 last_update_login,
4011 attribute_category,
4012 attribute1,
4013 attribute2,
4014 attribute3,
4015 attribute4,
4016 attribute5,
4017 attribute6,
4018 attribute7,
4019 attribute8,
4020 attribute9,
4021 attribute10,
4022 attribute11,
4023 attribute12,
4024 attribute13,
4025 attribute14,
4026 attribute15,
4027 application_id,
4028 seeded_flag
4029 FROM okc_outcomes_b
4030 WHERE dnz_chr_id = p_chr_id;
4031
4032 ------------------------------
4033 -- Versioning TL Table
4034 ------------------------------
4035
4036 INSERT INTO okc_outcomes_tlh
4037 (
4038 major_version,
4039 id,
4040 language,
4041 source_lang,
4042 sfwt_flag,
4043 comments,
4044 created_by,
4045 creation_date,
4046 last_updated_by,
4047 last_update_date,
4048 last_update_login
4049 )
4050 SELECT
4051 p_major_version,
4052 id,
4053 language,
4054 source_lang,
4055 sfwt_flag,
4056 comments,
4057 created_by,
4058 creation_date,
4059 last_updated_by,
4060 last_update_date,
4061 last_update_login
4062 FROM okc_outcomes_tl
4063 WHERE id in (select id from okc_outcomes_b
4064 where dnz_chr_id = p_chr_id);
4065
4066 RETURN l_return_status;
4067 EXCEPTION
4068 -- other appropriate handlers
4069 WHEN OTHERS THEN
4070 -- store SQL error message on message stack
4071 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4072 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4073 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4074 p_token1_value => sqlcode,
4075 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4076 p_token2_value => sqlerrm);
4077
4078 -- notify UNEXPECTED error
4079 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4080 return l_return_status;
4081 END create_version;
4082
4083 --This Function is called from Versioning API OKC_VERSION_PVT
4084 --Old Location:OKCRVERB.pls
4085 --New Location:Base Table API
4086
4087 FUNCTION restore_version(
4088 p_chr_id IN NUMBER,
4089 p_major_version IN NUMBER
4090 ) RETURN VARCHAR2 IS
4091
4092 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4093
4094 BEGIN
4095 INSERT INTO okc_outcomes_tl
4096 (
4097 id,
4098 language,
4099 source_lang,
4100 sfwt_flag,
4101 comments,
4102 created_by,
4103 creation_date,
4104 last_updated_by,
4105 last_update_date,
4106 last_update_login
4107 )
4108 SELECT
4109 id,
4110 language,
4111 source_lang,
4112 sfwt_flag,
4113 comments,
4114 created_by,
4115 creation_date,
4116 last_updated_by,
4117 last_update_date,
4118 last_update_login
4119 FROM okc_outcomes_tlh
4120 WHERE id in (select id from okc_outcomes_b where dnz_chr_id = p_chr_id)
4121 AND major_version = p_major_version;
4122
4123 --------------------------------------
4124 -- Restoring Base Table
4125 --------------------------------------
4126
4127 INSERT INTO okc_outcomes_b
4128 (
4129 id,
4130 pdf_id,
4131 cnh_id,
4132 dnz_chr_id,
4133 enabled_yn,
4134 object_version_number,
4135 created_by,
4136 creation_date,
4137 last_updated_by,
4138 last_update_date,
4139 last_update_login,
4140 attribute_category,
4141 attribute1,
4142 attribute2,
4143 attribute3,
4144 attribute4,
4145 attribute5,
4146 attribute6,
4147 attribute7,
4148 attribute8,
4149 attribute9,
4150 attribute10,
4151 attribute11,
4152 attribute12,
4153 attribute13,
4154 attribute14,
4155 attribute15,
4156 success_resource_id,
4157 failure_resource_id,
4158 application_id,
4159 seeded_flag
4160 )
4161 SELECT
4162 id,
4163 pdf_id,
4164 cnh_id,
4165 dnz_chr_id,
4166 enabled_yn,
4167 object_version_number,
4168 created_by,
4169 creation_date,
4170 last_updated_by,
4171 last_update_date,
4172 last_update_login,
4173 attribute_category,
4174 attribute1,
4175 attribute2,
4176 attribute3,
4177 attribute4,
4178 attribute5,
4179 attribute6,
4180 attribute7,
4181 attribute8,
4182 attribute9,
4183 attribute10,
4184 attribute11,
4185 attribute12,
4186 attribute13,
4187 attribute14,
4188 attribute15,
4189 success_resource_id,
4190 failure_resource_id,
4191 application_id,
4192 seeded_flag
4193 FROM okc_outcomes_bh
4194 WHERE dnz_chr_id = p_chr_id
4195 AND major_version = p_major_version;
4196
4197 RETURN l_return_status;
4198 EXCEPTION
4199 -- other appropriate handlers
4200 WHEN OTHERS THEN
4201 -- store SQL error message on message stack
4202 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4203 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4204 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4205 p_token1_value => sqlcode,
4206 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4207 p_token2_value => sqlerrm);
4208
4209 -- notify UNEXPECTED error
4210 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4211 return l_return_status;
4212 END restore_version;
4213 --
4214
4215 END OKC_OCE_PVT;