[Home] [Help]
PACKAGE BODY: APPS.OKC_CNH_PVT
Source
1 PACKAGE BODY OKC_CNH_PVT AS
2 /* $Header: OKCSCNHB.pls 120.0 2005/05/25 19:34:59 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 l_lang VARCHAR2(12) := okc_util.get_userenv_lang;
7 ---------------------------------------------------------------------------
8 -- FUNCTION get_seq_id
9 ---------------------------------------------------------------------------
10 FUNCTION get_seq_id RETURN NUMBER IS
11 BEGIN
12 RETURN(okc_p_util.raw_to_number(sys_guid()));
13 END get_seq_id;
14
15 ---------------------------------------------------------------------------
16 -- PROCEDURE qc
17 ---------------------------------------------------------------------------
18 PROCEDURE qc IS
19 BEGIN
20 null;
21 END qc;
22
23 ---------------------------------------------------------------------------
24 -- PROCEDURE change_version
25 ---------------------------------------------------------------------------
26 PROCEDURE change_version IS
27 BEGIN
28 null;
29 END change_version;
30
31 ---------------------------------------------------------------------------
32 -- PROCEDURE api_copy
33 ---------------------------------------------------------------------------
34 PROCEDURE api_copy IS
35 BEGIN
36 null;
37 END api_copy;
38
39 ---------------------------------------------------------------------------
40 -- PROCEDURE add_language
41 ---------------------------------------------------------------------------
42 PROCEDURE add_language IS
43 BEGIN
44 /* Mar/19/03 requested by Ric Ginsberg */
45 /* The following delete and update statements are commented out */
46 /* as a quick workaround to fix the time-consuming table handler issue */
47 /* Eventually we'll need to turn them into a separate fix_language procedure */
48 /*
49
50 DELETE FROM OKC_CONDITION_HEADERS_TL T
51 WHERE NOT EXISTS (
52 SELECT NULL
53 FROM OKC_CONDITION_HEADERS_B B
54 WHERE B.ID = T.ID
55 );
56
57 UPDATE OKC_CONDITION_HEADERS_TL T SET (
58 NAME,
59 DESCRIPTION,
60 SHORT_DESCRIPTION,
61 COMMENTS) = (SELECT
62 B.NAME,
63 B.DESCRIPTION,
64 B.SHORT_DESCRIPTION,
65 B.COMMENTS
66 FROM OKC_CONDITION_HEADERS_TL B
67 WHERE B.ID = T.ID
68 AND B.LANGUAGE = T.SOURCE_LANG)
69 WHERE (
70 T.ID,
71 T.LANGUAGE)
72 IN (SELECT
73 SUBT.ID,
74 SUBT.LANGUAGE
75 FROM OKC_CONDITION_HEADERS_TL SUBB, OKC_CONDITION_HEADERS_TL SUBT
76 WHERE SUBB.ID = SUBT.ID
77 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
78 AND (SUBB.NAME <> SUBT.NAME
79 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
80 OR SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
81 OR SUBB.COMMENTS <> SUBT.COMMENTS
82 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
83 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
84 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
85 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
86 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
87 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
88 ));
89 */
90
91 INSERT INTO OKC_CONDITION_HEADERS_TL (
92 ID,
93 LANGUAGE,
94 SOURCE_LANG,
95 SFWT_FLAG,
96 NAME,
97 DESCRIPTION,
98 SHORT_DESCRIPTION,
99 COMMENTS,
100 CREATED_BY,
101 CREATION_DATE,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATE_LOGIN)
105 SELECT
106 B.ID,
107 L.LANGUAGE_CODE,
108 B.SOURCE_LANG,
109 B.SFWT_FLAG,
110 B.NAME,
111 B.DESCRIPTION,
112 B.SHORT_DESCRIPTION,
113 B.COMMENTS,
114 B.CREATED_BY,
115 B.CREATION_DATE,
116 B.LAST_UPDATED_BY,
117 B.LAST_UPDATE_DATE,
118 B.LAST_UPDATE_LOGIN
119 FROM OKC_CONDITION_HEADERS_TL B, FND_LANGUAGES L
120 WHERE L.INSTALLED_FLAG IN ('I', 'B')
121 AND B.LANGUAGE = USERENV('LANG')
122 AND NOT EXISTS(
123 SELECT NULL
124 FROM OKC_CONDITION_HEADERS_TL T
125 WHERE T.ID = B.ID
126 AND T.LANGUAGE = L.LANGUAGE_CODE
127 );
128 DELETE FROM OKC_CONDITION_HEADERS_TLH T
129 WHERE NOT EXISTS (
130 SELECT NULL
131 FROM OKC_CONDITION_HEADERS_BH B
132 WHERE B.ID = T.ID
133 AND B.MAJOR_VERSION = T.MAJOR_VERSION
134 );
135 UPDATE OKC_CONDITION_HEADERS_TLH T SET (
136 NAME,
137 DESCRIPTION,
138 SHORT_DESCRIPTION,
139 COMMENTS) = (SELECT
140 B.NAME,
141 B.DESCRIPTION,
142 B.SHORT_DESCRIPTION,
143 B.COMMENTS
144 FROM OKC_CONDITION_HEADERS_TLH B
145 WHERE B.ID = T.ID
146 AND B.MAJOR_VERSION = T.MAJOR_VERSION
147 AND B.LANGUAGE = T.SOURCE_LANG)
148 WHERE (
149 T.ID,
150 T.MAJOR_VERSION,
151 T.LANGUAGE)
152 IN (SELECT
153 SUBT.ID,
154 SUBT.MAJOR_VERSION,
155 SUBT.LANGUAGE
156 FROM OKC_CONDITION_HEADERS_TLH SUBB, OKC_CONDITION_HEADERS_TLH SUBT
157 WHERE SUBB.ID = SUBT.ID
158 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
159 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
160 AND (SUBB.NAME <> SUBT.NAME
161 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
162 OR SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
163 OR SUBB.COMMENTS <> SUBT.COMMENTS
164 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
165 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
166 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
167 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
168 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
169 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
170 ));
171 INSERT INTO OKC_CONDITION_HEADERS_TLH (
172 ID,
173 LANGUAGE,
174 MAJOR_VERSION,
175 SOURCE_LANG,
176 SFWT_FLAG,
177 NAME,
178 DESCRIPTION,
179 SHORT_DESCRIPTION,
180 COMMENTS,
181 CREATED_BY,
182 CREATION_DATE,
183 LAST_UPDATED_BY,
184 LAST_UPDATE_DATE,
185 LAST_UPDATE_LOGIN)
186 SELECT
187 B.ID,
188 L.LANGUAGE_CODE,
189 B.MAJOR_VERSION,
190 B.SOURCE_LANG,
191 B.SFWT_FLAG,
192 B.NAME,
193 B.DESCRIPTION,
194 B.SHORT_DESCRIPTION,
195 B.COMMENTS,
196 B.CREATED_BY,
197 B.CREATION_DATE,
198 B.LAST_UPDATED_BY,
199 B.LAST_UPDATE_DATE,
200 B.LAST_UPDATE_LOGIN
201 FROM OKC_CONDITION_HEADERS_TLH B, FND_LANGUAGES L
202 WHERE L.INSTALLED_FLAG IN ('I', 'B')
203 AND B.LANGUAGE = USERENV('LANG')
204 AND NOT EXISTS(
205 SELECT NULL
206 FROM OKC_CONDITION_HEADERS_TLH T
207 WHERE T.ID = B.ID
208 AND T.MAJOR_VERSION = B.MAJOR_VERSION
209 AND T.LANGUAGE = L.LANGUAGE_CODE
210 );
211
212
213
214 END add_language;
215
216 ---------------------------------------------------------------------------
217 -- FUNCTION get_rec for: OKC_CONDITION_HEADERS_B
218 ---------------------------------------------------------------------------
219 FUNCTION get_rec (
220 p_cnh_rec IN cnh_rec_type,
221 x_no_data_found OUT NOCOPY BOOLEAN
222 ) RETURN cnh_rec_type IS
223 CURSOR okc_condition_headers_b_pk_csr (p_id IN NUMBER) IS
224 SELECT
225 ID,
226 ACN_ID,
227 COUNTER_GROUP_ID,
228 ONE_TIME_YN,
229 BEFORE_AFTER,
230 CNH_VARIANCE,
231 CONDITION_VALID_YN,
232 TRACKED_YN,
233 DATE_ACTIVE,
234 DATE_INACTIVE,
235 CNH_TYPE,
236 TEMPLATE_YN,
237 DNZ_CHR_ID,
238 OBJECT_ID,
239 JTOT_OBJECT_CODE,
240 OBJECT_VERSION_NUMBER,
241 TASK_OWNER_ID,
242 CREATED_BY,
243 CREATION_DATE,
244 LAST_UPDATED_BY,
245 LAST_UPDATE_DATE,
246 LAST_UPDATE_LOGIN,
247 ATTRIBUTE_CATEGORY,
248 ATTRIBUTE1,
249 ATTRIBUTE2,
250 ATTRIBUTE3,
251 ATTRIBUTE4,
252 ATTRIBUTE5,
253 ATTRIBUTE6,
254 ATTRIBUTE7,
255 ATTRIBUTE8,
256 ATTRIBUTE9,
257 ATTRIBUTE10,
258 ATTRIBUTE11,
259 ATTRIBUTE12,
260 ATTRIBUTE13,
261 ATTRIBUTE14,
262 ATTRIBUTE15,
263 APPLICATION_ID,
264 SEEDED_FLAG,
265 LAST_RUNDATE
266 FROM Okc_Condition_Headers_B
267 WHERE okc_condition_headers_b.id = p_id;
268 l_okc_condition_headers_b_pk okc_condition_headers_b_pk_csr%ROWTYPE;
269 l_cnh_rec cnh_rec_type;
270 BEGIN
271 x_no_data_found := TRUE;
272 -- Get current database values
273 OPEN okc_condition_headers_b_pk_csr (p_cnh_rec.id);
274 FETCH okc_condition_headers_b_pk_csr INTO
275 l_cnh_rec.ID,
276 l_cnh_rec.ACN_ID,
277 l_cnh_rec.COUNTER_GROUP_ID,
278 l_cnh_rec.ONE_TIME_YN,
279 l_cnh_rec.BEFORE_AFTER,
280 l_cnh_rec.CNH_VARIANCE,
281 l_cnh_rec.CONDITION_VALID_YN,
282 l_cnh_rec.TRACKED_YN,
283 l_cnh_rec.DATE_ACTIVE,
284 l_cnh_rec.DATE_INACTIVE,
285 l_cnh_rec.CNH_TYPE,
286 l_cnh_rec.TEMPLATE_YN,
287 l_cnh_rec.DNZ_CHR_ID,
288 l_cnh_rec.OBJECT_ID,
289 l_cnh_rec.JTOT_OBJECT_CODE,
290 l_cnh_rec.OBJECT_VERSION_NUMBER,
291 l_cnh_rec.TASK_OWNER_ID,
292 l_cnh_rec.CREATED_BY,
293 l_cnh_rec.CREATION_DATE,
294 l_cnh_rec.LAST_UPDATED_BY,
295 l_cnh_rec.LAST_UPDATE_DATE,
296 l_cnh_rec.LAST_UPDATE_LOGIN,
297 l_cnh_rec.ATTRIBUTE_CATEGORY,
298 l_cnh_rec.ATTRIBUTE1,
299 l_cnh_rec.ATTRIBUTE2,
300 l_cnh_rec.ATTRIBUTE3,
301 l_cnh_rec.ATTRIBUTE4,
302 l_cnh_rec.ATTRIBUTE5,
303 l_cnh_rec.ATTRIBUTE6,
304 l_cnh_rec.ATTRIBUTE7,
305 l_cnh_rec.ATTRIBUTE8,
306 l_cnh_rec.ATTRIBUTE9,
307 l_cnh_rec.ATTRIBUTE10,
308 l_cnh_rec.ATTRIBUTE11,
309 l_cnh_rec.ATTRIBUTE12,
310 l_cnh_rec.ATTRIBUTE13,
311 l_cnh_rec.ATTRIBUTE14,
312 l_cnh_rec.ATTRIBUTE15,
313 l_cnh_rec.APPLICATION_ID,
314 l_cnh_rec.SEEDED_FLAG,
315 l_cnh_rec.LAST_RUNDATE;
316 x_no_data_found := okc_condition_headers_b_pk_csr%NOTFOUND;
317 CLOSE okc_condition_headers_b_pk_csr;
318 RETURN(l_cnh_rec);
319 END get_rec;
320
321 FUNCTION get_rec (
322 p_cnh_rec IN cnh_rec_type
323 ) RETURN cnh_rec_type IS
324 l_row_notfound BOOLEAN := TRUE;
325 BEGIN
326 RETURN(get_rec(p_cnh_rec, l_row_notfound));
327 END get_rec;
328 ---------------------------------------------------------------------------
329 -- FUNCTION get_rec for: OKC_CONDITION_HEADERS_TL
330 ---------------------------------------------------------------------------
331 FUNCTION get_rec (
332 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
333 x_no_data_found OUT NOCOPY BOOLEAN
334 ) RETURN OkcConditionHeadersTlRecType IS
335 CURSOR okc_condition_header1_csr (p_id IN NUMBER,
336 p_language IN VARCHAR2) IS
337 SELECT
338 ID,
339 LANGUAGE,
340 SOURCE_LANG,
341 SFWT_FLAG,
342 NAME,
343 DESCRIPTION,
344 SHORT_DESCRIPTION,
345 COMMENTS,
346 CREATED_BY,
347 CREATION_DATE,
348 LAST_UPDATED_BY,
349 LAST_UPDATE_DATE,
350 LAST_UPDATE_LOGIN
351 FROM Okc_Condition_Headers_Tl
352 WHERE okc_condition_headers_tl.id = p_id
353 AND okc_condition_headers_tl.language = p_language;
354 l_okc_condition_headers_tl_pk okc_condition_header1_csr%ROWTYPE;
355 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
356 BEGIN
357 x_no_data_found := TRUE;
358 -- Get current database values
359 OPEN okc_condition_header1_csr (p_okc_condition_headers_tl_rec.id,
360 p_okc_condition_headers_tl_rec.language);
361 FETCH okc_condition_header1_csr INTO
362 l_okc_condition_headers_tl_rec.ID,
363 l_okc_condition_headers_tl_rec.LANGUAGE,
364 l_okc_condition_headers_tl_rec.SOURCE_LANG,
365 l_okc_condition_headers_tl_rec.SFWT_FLAG,
366 l_okc_condition_headers_tl_rec.NAME,
367 l_okc_condition_headers_tl_rec.DESCRIPTION,
368 l_okc_condition_headers_tl_rec.SHORT_DESCRIPTION,
369 l_okc_condition_headers_tl_rec.COMMENTS,
370 l_okc_condition_headers_tl_rec.CREATED_BY,
371 l_okc_condition_headers_tl_rec.CREATION_DATE,
372 l_okc_condition_headers_tl_rec.LAST_UPDATED_BY,
373 l_okc_condition_headers_tl_rec.LAST_UPDATE_DATE,
374 l_okc_condition_headers_tl_rec.LAST_UPDATE_LOGIN;
375 x_no_data_found := okc_condition_header1_csr%NOTFOUND;
376 CLOSE okc_condition_header1_csr;
377 RETURN(l_okc_condition_headers_tl_rec);
378 END get_rec;
379
380 FUNCTION get_rec (
381 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType
382 ) RETURN OkcConditionHeadersTlRecType IS
383 l_row_notfound BOOLEAN := TRUE;
384 BEGIN
385 RETURN(get_rec(p_okc_condition_headers_tl_rec, l_row_notfound));
386 END get_rec;
387 ---------------------------------------------------------------------------
388 -- FUNCTION get_rec for: OKC_CONDITION_HEADERS_V
389 ---------------------------------------------------------------------------
390 FUNCTION get_rec (
391 p_cnhv_rec IN cnhv_rec_type,
392 x_no_data_found OUT NOCOPY BOOLEAN
393 ) RETURN cnhv_rec_type IS
394 CURSOR okc_cnhv_pk_csr (p_id IN NUMBER) IS
395 SELECT
396 ID,
397 OBJECT_VERSION_NUMBER,
398 SFWT_FLAG,
399 ACN_ID,
400 COUNTER_GROUP_ID,
401 DESCRIPTION,
402 SHORT_DESCRIPTION,
403 COMMENTS,
404 ONE_TIME_YN,
405 NAME,
406 CONDITION_VALID_YN,
407 BEFORE_AFTER,
408 TRACKED_YN,
409 CNH_VARIANCE,
410 DNZ_CHR_ID,
411 TEMPLATE_YN,
412 DATE_ACTIVE,
413 OBJECT_ID,
414 DATE_INACTIVE,
415 JTOT_OBJECT_CODE,
416 TASK_OWNER_ID,
417 CNH_TYPE,
418 APPLICATION_ID,
419 SEEDED_FLAG,
420 LAST_RUNDATE,
421 ATTRIBUTE_CATEGORY,
422 ATTRIBUTE1,
423 ATTRIBUTE2,
424 ATTRIBUTE3,
425 ATTRIBUTE4,
426 ATTRIBUTE5,
427 ATTRIBUTE6,
428 ATTRIBUTE7,
429 ATTRIBUTE8,
430 ATTRIBUTE9,
431 ATTRIBUTE10,
432 ATTRIBUTE11,
433 ATTRIBUTE12,
434 ATTRIBUTE13,
435 ATTRIBUTE14,
436 ATTRIBUTE15,
437 CREATED_BY,
438 CREATION_DATE,
439 LAST_UPDATED_BY,
440 LAST_UPDATE_DATE,
441 LAST_UPDATE_LOGIN
442 FROM Okc_Condition_Headers_V
443 WHERE okc_condition_headers_v.id = p_id;
444 l_okc_cnhv_pk okc_cnhv_pk_csr%ROWTYPE;
445 l_cnhv_rec cnhv_rec_type;
446 BEGIN
447 x_no_data_found := TRUE;
448 -- Get current database values
449 OPEN okc_cnhv_pk_csr (p_cnhv_rec.id);
450 FETCH okc_cnhv_pk_csr INTO
451 l_cnhv_rec.ID,
452 l_cnhv_rec.OBJECT_VERSION_NUMBER,
453 l_cnhv_rec.SFWT_FLAG,
454 l_cnhv_rec.ACN_ID,
455 l_cnhv_rec.COUNTER_GROUP_ID,
456 l_cnhv_rec.DESCRIPTION,
457 l_cnhv_rec.SHORT_DESCRIPTION,
458 l_cnhv_rec.COMMENTS,
459 l_cnhv_rec.ONE_TIME_YN,
460 l_cnhv_rec.NAME,
461 l_cnhv_rec.CONDITION_VALID_YN,
462 l_cnhv_rec.BEFORE_AFTER,
463 l_cnhv_rec.TRACKED_YN,
464 l_cnhv_rec.CNH_VARIANCE,
465 l_cnhv_rec.DNZ_CHR_ID,
466 l_cnhv_rec.TEMPLATE_YN,
467 l_cnhv_rec.DATE_ACTIVE,
468 l_cnhv_rec.OBJECT_ID,
469 l_cnhv_rec.DATE_INACTIVE,
470 l_cnhv_rec.JTOT_OBJECT_CODE,
471 l_cnhv_rec.TASK_OWNER_ID,
472 l_cnhv_rec.CNH_TYPE,
473 l_cnhv_rec.APPLICATION_ID,
474 l_cnhv_rec.SEEDED_FLAG,
475 l_cnhv_rec.LAST_RUNDATE,
476 l_cnhv_rec.ATTRIBUTE_CATEGORY,
477 l_cnhv_rec.ATTRIBUTE1,
478 l_cnhv_rec.ATTRIBUTE2,
479 l_cnhv_rec.ATTRIBUTE3,
480 l_cnhv_rec.ATTRIBUTE4,
481 l_cnhv_rec.ATTRIBUTE5,
482 l_cnhv_rec.ATTRIBUTE6,
483 l_cnhv_rec.ATTRIBUTE7,
484 l_cnhv_rec.ATTRIBUTE8,
485 l_cnhv_rec.ATTRIBUTE9,
486 l_cnhv_rec.ATTRIBUTE10,
487 l_cnhv_rec.ATTRIBUTE11,
488 l_cnhv_rec.ATTRIBUTE12,
489 l_cnhv_rec.ATTRIBUTE13,
490 l_cnhv_rec.ATTRIBUTE14,
491 l_cnhv_rec.ATTRIBUTE15,
492 l_cnhv_rec.CREATED_BY,
493 l_cnhv_rec.CREATION_DATE,
494 l_cnhv_rec.LAST_UPDATED_BY,
495 l_cnhv_rec.LAST_UPDATE_DATE,
496 l_cnhv_rec.LAST_UPDATE_LOGIN;
497 x_no_data_found := okc_cnhv_pk_csr%NOTFOUND;
498 CLOSE okc_cnhv_pk_csr;
499 RETURN(l_cnhv_rec);
500 END get_rec;
501
502 FUNCTION get_rec (
503 p_cnhv_rec IN cnhv_rec_type
504 ) RETURN cnhv_rec_type IS
505 l_row_notfound BOOLEAN := TRUE;
506 BEGIN
507 RETURN(get_rec(p_cnhv_rec, l_row_notfound));
508 END get_rec;
509
510 -------------------------------------------------------------
511 -- FUNCTION null_out_defaults for: OKC_CONDITION_HEADERS_V --
512 -------------------------------------------------------------
513 FUNCTION null_out_defaults (
514 p_cnhv_rec IN cnhv_rec_type
515 ) RETURN cnhv_rec_type IS
516 l_cnhv_rec cnhv_rec_type := p_cnhv_rec;
517 BEGIN
518 IF (l_cnhv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
519 l_cnhv_rec.object_version_number := NULL;
520 END IF;
521 IF (l_cnhv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
522 l_cnhv_rec.sfwt_flag := NULL;
523 END IF;
524 IF (l_cnhv_rec.acn_id = OKC_API.G_MISS_NUM) THEN
525 l_cnhv_rec.acn_id := NULL;
526 END IF;
527 IF (l_cnhv_rec.counter_group_id = OKC_API.G_MISS_NUM) THEN
528 l_cnhv_rec.counter_group_id := NULL;
529 END IF;
530 IF (l_cnhv_rec.description = OKC_API.G_MISS_CHAR) THEN
531 l_cnhv_rec.description := NULL;
532 END IF;
533 IF (l_cnhv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
534 l_cnhv_rec.short_description := NULL;
535 END IF;
536 IF (l_cnhv_rec.comments = OKC_API.G_MISS_CHAR) THEN
537 l_cnhv_rec.comments := NULL;
538 END IF;
539 IF (l_cnhv_rec.one_time_yn = OKC_API.G_MISS_CHAR) THEN
540 l_cnhv_rec.one_time_yn := NULL;
541 END IF;
542 IF (l_cnhv_rec.name = OKC_API.G_MISS_CHAR) THEN
543 l_cnhv_rec.name := NULL;
544 END IF;
545 IF (l_cnhv_rec.condition_valid_yn = OKC_API.G_MISS_CHAR) THEN
546 l_cnhv_rec.condition_valid_yn := NULL;
547 END IF;
548 IF (l_cnhv_rec.before_after = OKC_API.G_MISS_CHAR) THEN
549 l_cnhv_rec.before_after := NULL;
550 END IF;
551 IF (l_cnhv_rec.tracked_yn = OKC_API.G_MISS_CHAR) THEN
552 l_cnhv_rec.tracked_yn := NULL;
553 END IF;
554 IF (l_cnhv_rec.cnh_variance = OKC_API.G_MISS_NUM) THEN
555 l_cnhv_rec.cnh_variance := NULL;
556 END IF;
557 IF (l_cnhv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
558 l_cnhv_rec.dnz_chr_id := NULL;
559 END IF;
560 IF (l_cnhv_rec.template_yn = OKC_API.G_MISS_CHAR) THEN
561 l_cnhv_rec.template_yn := NULL;
562 END IF;
563 IF (l_cnhv_rec.date_active = OKC_API.G_MISS_DATE) THEN
564 l_cnhv_rec.date_active := NULL;
565 END IF;
566 IF (l_cnhv_rec.object_id = OKC_API.G_MISS_NUM) THEN
567 l_cnhv_rec.object_id := NULL;
568 END IF;
569 IF (l_cnhv_rec.date_inactive = OKC_API.G_MISS_DATE) THEN
570 l_cnhv_rec.date_inactive := NULL;
571 END IF;
572 IF (l_cnhv_rec.jtot_object_code = OKC_API.G_MISS_CHAR) THEN
573 l_cnhv_rec.jtot_object_code := NULL;
574 END IF;
575 IF (l_cnhv_rec.task_owner_id = OKC_API.G_MISS_NUM) THEN
576 l_cnhv_rec.task_owner_id := NULL;
577 END IF;
578 IF (l_cnhv_rec.cnh_type = OKC_API.G_MISS_CHAR) THEN
579 l_cnhv_rec.cnh_type := NULL;
580 END IF;
581 IF (l_cnhv_rec.application_id = OKC_API.G_MISS_NUM) THEN
582 l_cnhv_rec.application_id := NULL;
583 END IF;
584 IF (l_cnhv_rec.seeded_flag = OKC_API.G_MISS_CHAR) THEN
585 l_cnhv_rec.seeded_flag := NULL;
586 END IF;
587 IF (l_cnhv_rec.last_rundate = OKC_API.G_MISS_DATE) THEN
588 l_cnhv_rec.last_rundate := NULL;
589 END IF;
590 IF (l_cnhv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
591 l_cnhv_rec.attribute_category := NULL;
592 END IF;
593 IF (l_cnhv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
594 l_cnhv_rec.attribute1 := NULL;
595 END IF;
596 IF (l_cnhv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
597 l_cnhv_rec.attribute2 := NULL;
598 END IF;
599 IF (l_cnhv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
600 l_cnhv_rec.attribute3 := NULL;
601 END IF;
602 IF (l_cnhv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
603 l_cnhv_rec.attribute4 := NULL;
604 END IF;
605 IF (l_cnhv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
606 l_cnhv_rec.attribute5 := NULL;
607 END IF;
608 IF (l_cnhv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
609 l_cnhv_rec.attribute6 := NULL;
610 END IF;
611 IF (l_cnhv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
612 l_cnhv_rec.attribute7 := NULL;
613 END IF;
614 IF (l_cnhv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
615 l_cnhv_rec.attribute8 := NULL;
616 END IF;
617 IF (l_cnhv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
618 l_cnhv_rec.attribute9 := NULL;
619 END IF;
620 IF (l_cnhv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
621 l_cnhv_rec.attribute10 := NULL;
622 END IF;
623 IF (l_cnhv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
624 l_cnhv_rec.attribute11 := NULL;
625 END IF;
626 IF (l_cnhv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
627 l_cnhv_rec.attribute12 := NULL;
628 END IF;
629 IF (l_cnhv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
630 l_cnhv_rec.attribute13 := NULL;
631 END IF;
632 IF (l_cnhv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
633 l_cnhv_rec.attribute14 := NULL;
634 END IF;
635 IF (l_cnhv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
636 l_cnhv_rec.attribute15 := NULL;
637 END IF;
638 IF (l_cnhv_rec.created_by = OKC_API.G_MISS_NUM) THEN
639 l_cnhv_rec.created_by := NULL;
640 END IF;
641 IF (l_cnhv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
642 l_cnhv_rec.creation_date := NULL;
643 END IF;
644 IF (l_cnhv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
645 l_cnhv_rec.last_updated_by := NULL;
646 END IF;
647 IF (l_cnhv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
648 l_cnhv_rec.last_update_date := NULL;
649 END IF;
650 IF (l_cnhv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
651 l_cnhv_rec.last_update_login := NULL;
652 END IF;
653 RETURN(l_cnhv_rec);
654 END null_out_defaults;
655
656 /******** Commented out nocopy generated code in favor of hand written code *****
657 ---------------------------------------------------------------------------
658 -- PROCEDURE Validate_Attributes
659 ---------------------------------------------------------------------------
660 -----------------------------------------------------
661 -- Validate_Attributes for:OKC_CONDITION_HEADERS_V --
662 -----------------------------------------------------
663 FUNCTION Validate_Attributes (
664 p_cnhv_rec IN cnhv_rec_type
665 ) RETURN VARCHAR2 IS
666 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
667 BEGIN
668 IF p_cnhv_rec.id = OKC_API.G_MISS_NUM OR
669 p_cnhv_rec.id IS NULL
670 THEN
671 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
672 l_return_status := OKC_API.G_RET_STS_ERROR;
673 ELSIF p_cnhv_rec.object_version_number = OKC_API.G_MISS_NUM OR
674 p_cnhv_rec.object_version_number IS NULL
675 THEN
676 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
677 l_return_status := OKC_API.G_RET_STS_ERROR;
678 ELSIF p_cnhv_rec.acn_id = OKC_API.G_MISS_NUM OR
679 p_cnhv_rec.acn_id IS NULL
680 THEN
681 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'acn_id');
682 l_return_status := OKC_API.G_RET_STS_ERROR;
683 ELSIF p_cnhv_rec.one_time_yn = OKC_API.G_MISS_CHAR OR
684 p_cnhv_rec.one_time_yn IS NULL
685 THEN
686 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'one_time_yn');
687 l_return_status := OKC_API.G_RET_STS_ERROR;
688 ELSIF p_cnhv_rec.name = OKC_API.G_MISS_CHAR OR
689 p_cnhv_rec.name IS NULL
690 THEN
691 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'name');
692 l_return_status := OKC_API.G_RET_STS_ERROR;
693 ELSIF p_cnhv_rec.condition_valid_yn = OKC_API.G_MISS_CHAR OR
694 p_cnhv_rec.condition_valid_yn IS NULL
695 THEN
696 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'condition_valid_yn');
697 l_return_status := OKC_API.G_RET_STS_ERROR;
698 ELSIF p_cnhv_rec.tracked_yn = OKC_API.G_MISS_CHAR OR
699 p_cnhv_rec.tracked_yn IS NULL
700 THEN
701 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'tracked_yn');
702 l_return_status := OKC_API.G_RET_STS_ERROR;
703 ELSIF p_cnhv_rec.template_yn = OKC_API.G_MISS_CHAR OR
704 p_cnhv_rec.template_yn IS NULL
705 THEN
706 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'template_yn');
707 l_return_status := OKC_API.G_RET_STS_ERROR;
708 ELSIF p_cnhv_rec.date_active = OKC_API.G_MISS_DATE OR
709 p_cnhv_rec.date_active IS NULL
710 THEN
711 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'date_active');
712 l_return_status := OKC_API.G_RET_STS_ERROR;
713 ELSIF p_cnhv_rec.cnh_type = OKC_API.G_MISS_CHAR OR
714 p_cnhv_rec.cnh_type IS NULL
715 THEN
716 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cnh_type');
717 l_return_status := OKC_API.G_RET_STS_ERROR;
718 END IF;
719 RETURN(l_return_status);
720 END Validate_Attributes;
721
722 ---------------------------------------------------------------------------
723 -- PROCEDURE Validate_Record
724 ---------------------------------------------------------------------------
725 -------------------------------------------------
726 -- Validate_Record for:OKC_CONDITION_HEADERS_V --
727 -------------------------------------------------
728 FUNCTION Validate_Record (
729 p_cnhv_rec IN cnhv_rec_type
730 ) RETURN VARCHAR2 IS
731 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
732 ------------------------------------
733 -- FUNCTION validate_foreign_keys --
734 ------------------------------------
735 FUNCTION validate_foreign_keys (
736 p_cnhv_rec IN cnhv_rec_type
737 ) RETURN VARCHAR2 IS
738 item_not_found_error EXCEPTION;
739 CURSOR okc_acnv_pk_csr (p_id IN NUMBER) IS
740 SELECT
741 ID,
742 CORRELATION,
743 OBJECT_VERSION_NUMBER,
744 SFWT_FLAG,
745 NAME,
746 DESCRIPTION,
747 SHORT_DESCRIPTION,
748 COMMENTS,
749 ENABLED_YN,
750 FACTORY_ENABLED_YN,
751 COUNTER_ACTION_YN,
752 ACN_TYPE,
753 SYNC_ALLOWED_YN,
754 APPLICATION_ID,
755 SEEDED_FLAG,
756 ATTRIBUTE_CATEGORY,
757 ATTRIBUTE1,
758 ATTRIBUTE2,
759 ATTRIBUTE3,
760 ATTRIBUTE4,
761 ATTRIBUTE5,
762 ATTRIBUTE6,
763 ATTRIBUTE7,
764 ATTRIBUTE8,
765 ATTRIBUTE9,
766 ATTRIBUTE10,
767 ATTRIBUTE11,
768 ATTRIBUTE12,
769 ATTRIBUTE13,
770 ATTRIBUTE14,
771 ATTRIBUTE15,
772 CREATED_BY,
773 CREATION_DATE,
774 LAST_UPDATED_BY,
775 LAST_UPDATE_DATE,
776 LAST_UPDATE_LOGIN
777 FROM Okc_Actions_V
778 WHERE okc_actions_v.id = p_id;
779 l_okc_acnv_pk okc_acnv_pk_csr%ROWTYPE;
780 CURSOR okx_counter_groups_v_pk_csr (p_counter_group_id IN NUMBER) IS
781 SELECT
782 COUNTER_GROUP_ID,
783 NAME,
784 DESCRIPTION,
785 TEMPLATE_FLAG,
786 START_DATE_ACTIVE,
787 END_DATE_ACTIVE,
788 ASSOCIATION_TYPE,
789 SOURCE_OBJECT_CODE,
790 SOURCE_OBJECT_ID,
791 CREATED_FROM_CTR_GRP_TMPL_ID,
792 SOURCE_COUNTER_GROUP_ID
793 FROM Okx_Counter_Groups_V
794 WHERE okx_counter_groups_v.counter_group_id = p_counter_group_id;
795 l_okx_counter_groups_v_pk okx_counter_groups_v_pk_csr%ROWTYPE;
796 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
797 l_row_notfound BOOLEAN := TRUE;
798 BEGIN
799 IF (p_cnhv_rec.ACN_ID IS NOT NULL)
800 THEN
801 OPEN okc_acnv_pk_csr(p_cnhv_rec.ACN_ID);
802 FETCH okc_acnv_pk_csr INTO l_okc_acnv_pk;
803 l_row_notfound := okc_acnv_pk_csr%NOTFOUND;
804 CLOSE okc_acnv_pk_csr;
805 IF (l_row_notfound) THEN
806 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ACN_ID');
807 RAISE item_not_found_error;
808 END IF;
809 END IF;
810 IF (p_cnhv_rec.COUNTER_GROUP_ID IS NOT NULL)
811 THEN
812 OPEN okx_counter_groups_v_pk_csr(p_cnhv_rec.COUNTER_GROUP_ID);
813 FETCH okx_counter_groups_v_pk_csr INTO l_okx_counter_groups_v_pk;
814 l_row_notfound := okx_counter_groups_v_pk_csr%NOTFOUND;
815 CLOSE okx_counter_groups_v_pk_csr;
816 IF (l_row_notfound) THEN
817 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'COUNTER_GROUP_ID');
818 RAISE item_not_found_error;
819 END IF;
820 END IF;
821 RETURN (l_return_status);
822 EXCEPTION
823 WHEN item_not_found_error THEN
824 l_return_status := OKC_API.G_RET_STS_ERROR;
825 RETURN (l_return_status);
826 END validate_foreign_keys;
827 BEGIN
828 l_return_status := validate_foreign_keys (p_cnhv_rec);
829 RETURN (l_return_status);
830 END Validate_Record;
831
832 ******* End Commented out nocopy generated code in favor of hand written code ***/
833
834 /****************Begin Hand Written Code ********************************/
835
836 ---------------------------------------------------------------------------
837 -- PROCEDURE Validate_Acn_id
838 ---------------------------------------------------------------------------
839 -- Start of comments
840 --
841 -- Procedure Name : Validate_acn_id
842 -- Description :
843 -- Business Rules :
844 -- Parameters :
845 -- Version : 1.0
846 -- End of comments
847 ---------------------------------------------------------------------------
848
849 PROCEDURE Validate_acn_id( x_return_status OUT NOCOPY VARCHAR2
850 ,p_cnhv_rec IN cnhv_rec_type)
851 IS
852
853 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
854 l_temp NUMBER ;
855 acn_rec VARCHAR2(1);
856 CURSOR acn_cur IS
857 SELECT 'X' FROM okc_actions_v acn
858 WHERE acn.id = p_cnhv_rec.acn_id;
859 BEGIN
860 -- initialize return status
861 x_return_status := OKC_API.G_RET_STS_SUCCESS;
862
863 -- data is required
864 IF (p_cnhv_rec.acn_id IS NULL) OR
865 (p_cnhv_rec.acn_id = OKC_API.G_MISS_NUM)
866 THEN
867 OKC_API.SET_MESSAGE(p_app_name => g_app_name
868 ,p_msg_name => g_required_value
869 ,p_token1 => g_col_name_token
870 ,p_token1_value => 'acn_id');
871 -- notify caller of an error
872 x_return_status := OKC_API.G_RET_STS_ERROR;
873
874 -- halt further validation of this column
875 RAISE G_EXCEPTION_HALT_VALIDATION;
876 ELSE
877 OPEN acn_cur;
878 FETCH acn_cur INTO acn_rec;
879 IF acn_cur%NOTFOUND THEN
880 OKC_API.SET_MESSAGE(p_app_name => g_app_name
881 ,p_msg_name => g_required_value
882 ,p_token1 => g_col_name_token
883 ,p_token1_value => 'acn_id');
884 -- notify caller of an error
885 x_return_status := OKC_API.G_RET_STS_ERROR;
886
887 -- halt further validation of this column
888 RAISE G_EXCEPTION_HALT_VALIDATION;
889 END IF;
890 CLOSE acn_cur;
891 END IF;
892
893 EXCEPTION
894 WHEN G_EXCEPTION_HALT_VALIDATION THEN
895 -- no processing necessary; validation can continue
896 -- with the next column
897 NULL;
898
899 WHEN OTHERS THEN
900 -- store SQL error message on message stack for caller
901 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
902 p_msg_name => g_unexpected_error,
903 p_token1 => g_sqlcode_token,
904 p_token1_value => sqlcode,
905 p_token2 => g_sqlerrm_token,
906 p_token2_value => sqlerrm);
907
908 -- notify caller of an UNEXPECTED error
909 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
910 END Validate_Acn_id;
911
912 ---------------------------------------------------------------------------
913 -- PROCEDURE Validate_counter_group_id
914 ---------------------------------------------------------------------------
915 -- Start of comments
916 --
917 -- Procedure Name : Validate_counter_group_id
918 -- Description :
919 -- Business Rules :
920 -- Parameters :
921 -- Version : 1.0
922 -- End of comments
923 ---------------------------------------------------------------------------
924
925 PROCEDURE Validate_counter_group_id( x_return_status OUT NOCOPY VARCHAR2
926 ,p_cnhv_rec IN cnhv_rec_type)
927 IS
928
929 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
930 l_temp NUMBER ;
931 CURSOR cgp_cur IS
932 SELECT 'X' FROM okx_counter_groups_v cgp
933 WHERE cgp.counter_group_id = p_cnhv_rec.counter_group_id;
934 cgp_rec cgp_cur%ROWTYPE;
935 CURSOR acn_cur IS
936 SELECT acn.counter_action_yn counter_action_yn
937 FROM okc_actions_b acn,
938 okc_condition_headers_b cnh
939 WHERE acn.id = cnh.acn_id
940 AND cnh.id = p_cnhv_rec.id;
941 acn_rec acn_cur%ROWTYPE;
942 BEGIN
943 -- initialize return status
944 x_return_status := OKC_API.G_RET_STS_SUCCESS;
945
946 IF p_cnhv_rec.counter_group_id IS NOT NULL OR
947 p_cnhv_rec.counter_group_id <> OKC_API.G_MISS_NUM THEN
948 OPEN cgp_cur;
949 FETCH cgp_cur INTO cgp_rec;
950 IF cgp_cur%NOTFOUND THEN
951 OKC_API.SET_MESSAGE(p_app_name => g_app_name
952 ,p_msg_name => g_invalid_value
953 ,p_token1 => g_col_name_token
954 ,p_token1_value => 'counter_group_id');
955 -- notify caller of an error
956 x_return_status := OKC_API.G_RET_STS_ERROR;
957
958 -- halt further validation of this column
959 RAISE G_EXCEPTION_HALT_VALIDATION;
960 END IF;
961 CLOSE cgp_cur;
962
963 OPEN acn_cur;
964 FETCH acn_cur INTO acn_rec;
965 IF acn_rec.counter_action_yn = 'N' THEN
966 OKC_API.SET_MESSAGE(p_app_name => g_app_name
967 ,p_msg_name => g_invalid_value
968 ,p_token1 => g_col_name_token
969 ,p_token1_value => 'counter_group_id');
970 -- notify caller of an error
971 x_return_status := OKC_API.G_RET_STS_ERROR;
972
973 -- halt further validation of this column
974 RAISE G_EXCEPTION_HALT_VALIDATION;
975 END IF;
976 CLOSE acn_cur;
977 ELSIF p_cnhv_rec.counter_group_id IS NULL OR
978 p_cnhv_rec.counter_group_id = OKC_API.G_MISS_NUM THEN
979 OPEN acn_cur;
980 FETCH acn_cur INTO acn_rec;
981 IF acn_rec.counter_action_yn = 'Y' THEN
982 OKC_API.SET_MESSAGE(p_app_name => g_app_name
983 ,p_msg_name => g_required_value
984 ,p_token1 => g_col_name_token
985 ,p_token1_value => 'counter_group_id');
986 -- notify caller of an error
987 x_return_status := OKC_API.G_RET_STS_ERROR;
988
989 -- halt further validation of this column
990 RAISE G_EXCEPTION_HALT_VALIDATION;
991 END IF;
992 CLOSE acn_cur;
993 END IF;
994
995 EXCEPTION
996 WHEN G_EXCEPTION_HALT_VALIDATION THEN
997 -- no processing necessary; validation can continue
998 -- with the next column
999 NULL;
1000
1001 WHEN OTHERS THEN
1002 -- store SQL error message on message stack for caller
1003 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1004 p_msg_name => g_unexpected_error,
1005 p_token1 => g_sqlcode_token,
1006 p_token1_value => sqlcode,
1007 p_token2 => g_sqlerrm_token,
1008 p_token2_value => sqlerrm);
1009
1010 -- notify caller of an UNEXPECTED error
1011 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1012 END Validate_counter_group_id;
1013
1014 ---------------------------------------------------------------------------
1015 -- PROCEDURE Validate_Task_Owner_id
1016 ---------------------------------------------------------------------------
1017 -- Start of comments
1018 --
1019 -- Procedure Name : Validate_Task_Owner_id
1020 -- Description :
1021 -- Business Rules :
1022 -- Parameters :
1023 -- Version : 1.0
1024 -- End of comments : Validation is required for foreign key check
1025 ---------------------------------------------------------------------------
1026
1027 PROCEDURE Validate_Task_Owner_id( x_return_status OUT NOCOPY VARCHAR2
1028 ,p_cnhv_rec IN cnhv_rec_type)
1029 IS
1030
1031 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1032
1033 BEGIN
1034 -- initialize return status
1035 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1036
1037 -- data is required when tracked_yn is 'Y'
1038 IF p_cnhv_rec.tracked_yn = 'Y'
1039 THEN
1040 IF (p_cnhv_rec.task_owner_id IS NULL) OR
1041 (p_cnhv_rec.task_owner_id = OKC_API.G_MISS_NUM)
1042 THEN
1043 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1044 ,p_msg_name => g_required_value
1045 ,p_token1 => g_col_name_token
1046 ,p_token1_value => 'task_owner_id');
1047
1048 -- notify caller of an error
1049 x_return_status := OKC_API.G_RET_STS_ERROR;
1050
1051 -- halt further validation of this column
1052 RAISE G_EXCEPTION_HALT_VALIDATION;
1053 END IF;
1054 END IF;
1055
1056 /**** Validation is required for foreign key check ************/
1057
1058 EXCEPTION
1059 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1060 -- no processing necessary; validation can continue
1061 -- with the next column
1062 NULL;
1063
1064 WHEN OTHERS THEN
1065 -- store SQL error message on message stack for caller
1066 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1067 p_msg_name => g_unexpected_error,
1068 p_token1 => g_sqlcode_token,
1069 p_token1_value => sqlcode,
1070 p_token2 => g_sqlerrm_token,
1071 p_token2_value => sqlerrm);
1072
1073 -- notify caller of an UNEXPECTED error
1074 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1075 END Validate_Task_Owner_id;
1076
1077 ---------------------------------------------------------------------------
1078 -- PROCEDURE Validate_Object_id
1079 ---------------------------------------------------------------------------
1080 -- Start of comments
1081 --
1082 -- Procedure Name : Validate_Object_id
1083 -- Description :
1084 -- Business Rules :
1085 -- Parameters :
1086 -- Version : 1.0
1087 -- End of comments
1088 ---------------------------------------------------------------------------
1089
1090 PROCEDURE Validate_Object_id( x_return_status OUT NOCOPY VARCHAR2
1091 ,p_cnhv_rec IN cnhv_rec_type)
1092 IS
1093
1094 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1095 l_temp NUMBER ;
1096
1097 BEGIN
1098 -- initialize return status
1099 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1100
1101 NULL;
1102
1103 EXCEPTION
1104 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1105 -- no processing necessary; validation can continue
1106 -- with the next column
1107 NULL;
1108
1109 WHEN OTHERS THEN
1110 -- store SQL error message on message stack for caller
1111 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1112 p_msg_name => g_unexpected_error,
1113 p_token1 => g_sqlcode_token,
1114 p_token1_value => sqlcode,
1115 p_token2 => g_sqlerrm_token,
1116 p_token2_value => sqlerrm);
1117
1118 -- notify caller of an UNEXPECTED error
1119 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1120 END Validate_Object_id;
1121
1122 ---------------------------------------------------------------------------
1123 -- PROCEDURE Validate_Sfwt_Flag
1124 ---------------------------------------------------------------------------
1125 -- Start of comments
1126 --
1127 -- Procedure Name : Validate_Sfwt_Flag
1128 -- Description :
1129 -- Business Rules :
1130 -- Parameters :
1131 -- Version : 1.0
1132 -- End of comments
1133 ---------------------------------------------------------------------------
1134 PROCEDURE Validate_Sfwt_Flag(x_return_status OUT NOCOPY VARCHAR2
1135 ,p_cnhv_rec IN cnhv_rec_type)
1136 IS
1137
1138 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1139
1140 BEGIN
1141 -- initialize return status
1142 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1143
1144 -- data is required
1145 IF (p_cnhv_rec.sfwt_flag IS NULL) OR
1146 (p_cnhv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
1147 THEN
1148 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1149 ,p_msg_name => g_required_value
1150 ,p_token1 => g_col_name_token
1151 ,p_token1_value => 'sfwt_flag');
1152
1153 -- notify caller of an error
1154 x_return_status := OKC_API.G_RET_STS_ERROR;
1155
1156 -- halt further validation of this column
1157 RAISE G_EXCEPTION_HALT_VALIDATION;
1158 END IF;
1159
1160 -- check if sfwt_flag is in uppercase
1161 IF (p_cnhv_rec.sfwt_flag) <> UPPER(p_cnhv_rec.sfwt_flag) THEN
1162 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1163 ,p_msg_name => g_uppercase_required
1164 ,p_token1 => g_col_name_token
1165 ,p_token1_value => 'sfwt_flag');
1166 x_return_status := OKC_API.G_RET_STS_ERROR;
1167 RAISE G_EXCEPTION_HALT_VALIDATION;
1168 END IF;
1169
1170 -- verify that data is within allowable values
1171 IF (UPPER(p_cnhv_rec.sfwt_flag) NOT IN ('Y','N')) THEN
1172 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1173 p_msg_name => g_invalid_value,
1174 p_token1 => g_col_name_token,
1175 p_token1_value => 'sfwt_flag');
1176 -- notify caller of an error
1177 x_return_status := OKC_API.G_RET_STS_ERROR;
1178 RAISE G_EXCEPTION_HALT_VALIDATION;
1179 END IF;
1180
1181 EXCEPTION
1182 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1183 -- no processing necessary; validation can continue
1184 -- with the next column
1185 NULL;
1186
1187 WHEN OTHERS THEN
1188 -- store SQL error message on message stack for caller
1189 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1190 p_msg_name => g_unexpected_error,
1191 p_token1 => g_sqlcode_token,
1192 p_token1_value => sqlcode,
1193 p_token2 => g_sqlerrm_token,
1194 p_token2_value => sqlerrm);
1195
1196 -- notify caller of an UNEXPECTED error
1197 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1198
1199 END Validate_Sfwt_Flag;
1200
1201 ---------------------------------------------------------------------------
1202 -- PROCEDURE Validate_Seeded_Flag
1203 ---------------------------------------------------------------------------
1204 -- Start of comments
1205 --
1206 -- Procedure Name : Validate_Seeded_Flag
1207 -- Description : Checks if column SEEDED_FLAG is 'Y' or 'N' only
1208 -- Business Rules :
1209 -- Parameters :
1210 -- Version : 1.0
1211 -- End of comments
1212 ---------------------------------------------------------------------------
1213
1214 PROCEDURE validate_seeded_flag(
1215 x_return_status OUT NOCOPY VARCHAR2,
1216 p_cnhv_rec IN cnhv_rec_type) IS
1217 BEGIN
1218 -- initialize return status
1219 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1220 -- Check if seeded_flag is Y or N
1221 IF UPPER(p_cnhv_rec.seeded_flag) NOT IN ('Y', 'N') THEN
1222 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1223 p_msg_name => g_invalid_value,
1224 p_token1 => g_col_name_token,
1225 p_token1_value => 'seeded_flag');
1226 x_return_status := OKC_API.G_RET_STS_ERROR;
1227 raise G_EXCEPTION_HALT_VALIDATION;
1228 END IF;
1229
1230 --Check if the data is in upper case
1231 IF p_cnhv_rec.seeded_flag <> UPPER(p_cnhv_rec.seeded_flag) THEN
1232 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1233 p_msg_name => g_uppercase_required,
1234 p_token1 => g_col_name_token,
1235 p_token1_value => 'seeded_flag');
1236 x_return_status := OKC_API.G_RET_STS_ERROR;
1237 raise G_EXCEPTION_HALT_VALIDATION;
1238 END IF;
1239 EXCEPTION
1240 when G_EXCEPTION_HALT_VALIDATION then
1241 -- no processing necessary; validation can continue
1242 -- with the next column
1243 null;
1244
1245 when OTHERS then
1246 -- store SQL error message on message stack for caller
1247 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1248 p_msg_name => g_unexpected_error,
1249 p_token1 => g_sqlcode_token,
1250 p_token1_value => sqlcode,
1251 p_token2 => g_sqlerrm_token,
1252 p_token2_value => sqlerrm);
1253 -- notify caller of an UNEXPECTED error
1254 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1255 END validate_seeded_flag;
1256 ---------------------------------------------------------------------------
1257 -- PROCEDURE Validate_Application_id
1258 ---------------------------------------------------------------------------
1259 -- Start of comments
1260 --
1261 -- Procedure Name : Validate_Application_id
1262 -- Description : Checks id application id exists in fnd_application
1263 -- Business Rules :
1264 -- Parameters :
1265 -- Version : 1.0
1266 -- End of comments
1267 ---------------------------------------------------------------------------
1268
1269 PROCEDURE validate_application_id(
1270 x_return_status OUT NOCOPY VARCHAR2,
1271 p_cnhv_rec IN cnhv_rec_type) IS
1272 Cursor application_id_cur(p_application_id IN NUMBER) IS
1273 select '1'
1274 from fnd_application
1275 where application_id = p_application_id;
1276 l_dummy VARCHAR2(1) := '?';
1277 BEGIN
1278 -- initialize return status
1279 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1280
1281 IF p_cnhv_rec.application_id IS NOT NULL THEN
1282 --Check if application id exists in the fnd_application or not
1283 OPEN application_id_cur(p_cnhv_rec.application_id);
1284 FETCH application_id_cur INTO l_dummy;
1285 CLOSE application_id_cur ;
1286 IF l_dummy = '?' THEN
1287 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1288 p_msg_name => g_invalid_value,
1289 p_token1 => g_col_name_token,
1290 p_token1_value => 'application_id');
1291 x_return_status := OKC_API.G_RET_STS_ERROR;
1292 raise G_EXCEPTION_HALT_VALIDATION;
1293 END IF;
1294 END IF;
1295 EXCEPTION
1296 when G_EXCEPTION_HALT_VALIDATION then
1297 -- no processing necessary; validation can continue
1298 -- with the next column
1299 null;
1300
1301 when OTHERS then
1302 -- store SQL error message on message stack for caller
1303 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1304 p_msg_name => g_unexpected_error,
1305 p_token1 => g_sqlcode_token,
1306 p_token1_value => sqlcode,
1307 p_token2 => g_sqlerrm_token,
1308 p_token2_value => sqlerrm);
1309 -- notify caller of an UNEXPECTED error
1310 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1311 END validate_application_id;
1312
1313 ---------------------------------------------------------------------------
1314 -- PROCEDURE Validate_Name
1315 ---------------------------------------------------------------------------
1316 -- Start of comments
1317 --
1318 -- Procedure Name : Validate_Name
1319 -- Description :
1320 -- Business Rules :
1321 -- Parameters :
1322 -- Version : 1.0
1323 -- End of comments
1324 ---------------------------------------------------------------------------
1325 PROCEDURE Validate_Name(x_return_status OUT NOCOPY VARCHAR2
1326 ,p_cnhv_rec IN cnhv_rec_type)
1327 IS
1328
1329 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1330 BEGIN
1331 -- initialize return status
1332 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1333
1334 -- data is required
1335 IF (p_cnhv_rec.name IS NULL) OR
1336 (p_cnhv_rec.name = OKC_API.G_MISS_CHAR)
1337 THEN
1338 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1339 ,p_msg_name => g_required_value
1340 ,p_token1 => g_col_name_token
1341 ,p_token1_value => 'name');
1342
1343 -- notify caller of an error
1344 x_return_status := OKC_API.G_RET_STS_ERROR;
1345 -- halt further validation of this column
1346 RAISE G_EXCEPTION_HALT_VALIDATION;
1347 END IF;
1348
1349 EXCEPTION
1350 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1351 -- no processing necessary; validation can continue
1352 -- with the next column
1353 NULL;
1354
1355 WHEN OTHERS THEN
1356 -- store SQL error message on message stack for caller
1357 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1358 p_msg_name => g_unexpected_error,
1359 p_token1 => g_sqlcode_token,
1360 p_token1_value => sqlcode,
1361 p_token2 => g_sqlerrm_token,
1362 p_token2_value => sqlerrm);
1363
1364 -- notify caller of an UNEXPECTED error
1365 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1366
1367 END Validate_Name;
1368
1369 ---------------------------------------------------------------------------
1370 -- PROCEDURE Validate_condition_valid_YN
1371 ---------------------------------------------------------------------------
1372 -- Start of comments
1373 --
1374 -- Procedure Name : Validate_condition_valid_YN
1375 -- Description :
1376 -- Business Rules :
1377 -- Parameters :
1378 -- Version : 1.0
1379 -- End of comments
1380 ---------------------------------------------------------------------------
1381 PROCEDURE Validate_condition_valid_YN(x_return_status OUT NOCOPY VARCHAR2
1382 ,p_cnhv_rec IN cnhv_rec_type)
1383 IS
1384
1385 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1386
1387 BEGIN
1388 -- initialize return status
1389 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1390
1391 -- data is required
1392 IF (p_cnhv_rec.condition_valid_yn IS NULL) OR
1393 (p_cnhv_rec.condition_valid_yn = OKC_API.G_MISS_CHAR)
1394 THEN
1395 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1396 ,p_msg_name => g_required_value
1397 ,p_token1 => g_col_name_token
1398 ,p_token1_value => 'condition_valid_yn');
1399
1400 -- notify caller of an error
1401 x_return_status := OKC_API.G_RET_STS_ERROR;
1402
1403 -- halt further validation of this column
1404 RAISE G_EXCEPTION_HALT_VALIDATION;
1405 END IF;
1406
1407 -- check if condition_valid_yn is in uppercase
1408 IF (p_cnhv_rec.condition_valid_yn) <> UPPER(p_cnhv_rec.condition_valid_yn) THEN
1409 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1410 ,p_msg_name => g_uppercase_required
1411 ,p_token1 => g_col_name_token
1412 ,p_token1_value => 'condition_valid_yn');
1413 x_return_status := OKC_API.G_RET_STS_ERROR;
1414 RAISE G_EXCEPTION_HALT_VALIDATION;
1415 END IF;
1416
1417 -- verify that data is within allowable values
1418 IF (UPPER(p_cnhv_rec.condition_valid_yn) NOT IN ('Y','N')) THEN
1419 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1420 p_msg_name => g_invalid_value,
1421 p_token1 => g_col_name_token,
1422 p_token1_value => 'condition_valid_yn');
1423 -- notify caller of an error
1424 x_return_status := OKC_API.G_RET_STS_ERROR;
1425
1426 -- halt further validation of this column
1427 RAISE G_EXCEPTION_HALT_VALIDATION;
1428 END IF;
1429
1430 EXCEPTION
1431 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1432 -- no processing neccessary; validation can continue
1433 -- with next column
1434 NULL;
1435
1436 WHEN OTHERS THEN
1437 -- store SQL error message on message stack for caller
1438 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1439 p_msg_name => g_unexpected_error,
1440 p_token1 => g_sqlcode_token,
1441 p_token1_value => sqlcode,
1442 p_token2 => g_sqlerrm_token,
1443 p_token2_value => sqlerrm);
1444
1445 -- notify caller of an UNEXPECTED error
1446 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1447
1448 END Validate_condition_valid_YN;
1449
1450 ---------------------------------------------------------------------------
1451 -- PROCEDURE Validate_Tracked_YN
1452 ---------------------------------------------------------------------------
1453 -- Start of comments
1454 --
1455 -- Procedure Name : Validate_Tracked_YN
1456 -- Description :
1457 -- Business Rules :
1458 -- Parameters :
1459 -- Version : 1.0
1460 -- End of comments
1461 ---------------------------------------------------------------------------
1462 PROCEDURE Validate_Tracked_YN(x_return_status OUT NOCOPY VARCHAR2
1463 ,p_cnhv_rec IN cnhv_rec_type)
1464 IS
1465
1466 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1467
1468 BEGIN
1469 -- initialize return status
1470 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1471
1472 -- data is required
1473 IF (p_cnhv_rec.tracked_yn IS NULL) OR
1474 (p_cnhv_rec.tracked_yn = OKC_API.G_MISS_CHAR)
1475 THEN
1476 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1477 ,p_msg_name => g_required_value
1478 ,p_token1 => g_col_name_token
1479 ,p_token1_value => 'tracked_yn');
1480
1481 -- notify caller of an error
1482 x_return_status := OKC_API.G_RET_STS_ERROR;
1483
1484 -- halt further validation of this column
1485 RAISE G_EXCEPTION_HALT_VALIDATION;
1486 END IF;
1487
1488 -- check if tracked_yn is in uppercase
1489 IF (p_cnhv_rec.tracked_yn) <> UPPER(p_cnhv_rec.tracked_yn) THEN
1490 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1491 ,p_msg_name => g_uppercase_required
1492 ,p_token1 => g_col_name_token
1493 ,p_token1_value => 'tracked_yn');
1494 x_return_status := OKC_API.G_RET_STS_ERROR;
1495 RAISE G_EXCEPTION_HALT_VALIDATION;
1496 END IF;
1497
1498 -- verify that data is within allowable values
1499 IF (UPPER(p_cnhv_rec.tracked_yn) NOT IN ('Y','N')) THEN
1500 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1501 p_msg_name => g_invalid_value,
1502 p_token1 => g_col_name_token,
1503 p_token1_value => 'tracked_yn');
1504 -- notify caller of an error
1505 x_return_status := OKC_API.G_RET_STS_ERROR;
1506
1507 -- halt further validation of this column
1508 RAISE G_EXCEPTION_HALT_VALIDATION;
1509 END IF;
1510
1511 EXCEPTION
1512 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1513 -- no processing neccessary; validation can continue
1514 -- with next column
1515 NULL;
1516
1517 WHEN OTHERS THEN
1518 -- store SQL error message on message stack for caller
1519 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1520 p_msg_name => g_unexpected_error,
1521 p_token1 => g_sqlcode_token,
1522 p_token1_value => sqlcode,
1523 p_token2 => g_sqlerrm_token,
1524 p_token2_value => sqlerrm);
1525
1526 -- notify caller of an UNEXPECTED error
1527 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1528
1529 END Validate_tracked_YN;
1530
1531 ---------------------------------------------------------------------------
1532 -- PROCEDURE Validate_Template_YN
1533 ---------------------------------------------------------------------------
1534 -- Start of comments
1535 --
1536 -- Procedure Name : Validate_Template_YN
1537 -- Description :
1538 -- Business Rules :
1539 -- Parameters :
1540 -- Version : 1.0
1541 -- End of comments
1542 ---------------------------------------------------------------------------
1543 PROCEDURE Validate_Template_YN(x_return_status OUT NOCOPY VARCHAR2
1544 ,p_cnhv_rec IN cnhv_rec_type)
1545 IS
1546
1547 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1548
1549 BEGIN
1550 -- initialize return status
1551 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1552
1553 -- data is required
1554 IF (p_cnhv_rec.template_yn IS NULL) OR
1555 (p_cnhv_rec.template_yn = OKC_API.G_MISS_CHAR)
1556 THEN
1557 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1558 ,p_msg_name => g_required_value
1559 ,p_token1 => g_col_name_token
1560 ,p_token1_value => 'template_yn');
1561
1562 -- notify caller of an error
1563 x_return_status := OKC_API.G_RET_STS_ERROR;
1564
1565 -- halt further validation of this column
1566 RAISE G_EXCEPTION_HALT_VALIDATION;
1567 END IF;
1568
1569 -- check if template_yn is in uppercase
1570 IF (p_cnhv_rec.template_yn) <> UPPER(p_cnhv_rec.template_yn) THEN
1571 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1572 ,p_msg_name => g_uppercase_required
1573 ,p_token1 => g_col_name_token
1574 ,p_token1_value => 'template_yn');
1575 x_return_status := OKC_API.G_RET_STS_ERROR;
1576 RAISE G_EXCEPTION_HALT_VALIDATION;
1577 END IF;
1578
1579 -- verify that data is within allowable values
1580 IF (UPPER(p_cnhv_rec.template_yn) NOT IN ('Y','N')) THEN
1581 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1582 p_msg_name => g_invalid_value,
1583 p_token1 => g_col_name_token,
1584 p_token1_value => 'template_yn');
1585 -- notify caller of an error
1586 x_return_status := OKC_API.G_RET_STS_ERROR;
1587
1588 -- halt further validation of this column
1589 RAISE G_EXCEPTION_HALT_VALIDATION;
1590 END IF;
1591
1592 EXCEPTION
1593 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1594 -- no processing neccessary; validation can continue
1595 -- with next column
1596 NULL;
1597
1598 WHEN OTHERS THEN
1599 -- store SQL error message on message stack for caller
1600 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1601 p_msg_name => g_unexpected_error,
1602 p_token1 => g_sqlcode_token,
1603 p_token1_value => sqlcode,
1604 p_token2 => g_sqlerrm_token,
1605 p_token2_value => sqlerrm);
1606
1607 -- notify caller of an UNEXPECTED error
1608 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1609
1610 END Validate_Template_YN;
1611
1612 ---------------------------------------------------------------------------
1613 -- PROCEDURE Validate_One_Time_YN
1614 ---------------------------------------------------------------------------
1615 -- Start of comments
1616 --
1617 -- Procedure Name : Validate_one_time_YN
1618 -- Description :
1619 -- Business Rules :
1620 -- Parameters :
1621 -- Version : 1.0
1622 -- End of comments
1623 ---------------------------------------------------------------------------
1624 PROCEDURE Validate_One_Time_YN(x_return_status OUT NOCOPY VARCHAR2
1625 ,p_cnhv_rec IN cnhv_rec_type)
1626 IS
1627
1628 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1629
1630 BEGIN
1631 -- initialize return status
1632 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1633
1634 -- data is required when cnh_type is 'ABC'
1635 IF (p_cnhv_rec.one_time_yn IS NULL) OR
1636 (p_cnhv_rec.one_time_yn = OKC_API.G_MISS_CHAR) THEN
1637 IF (p_cnhv_rec.cnh_type = 'ABC') THEN
1638 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1639 ,p_msg_name => g_required_value
1640 ,p_token1 => g_col_name_token
1641 ,p_token1_value => 'one_time_yn');
1642
1643 -- notify caller of an error
1644 x_return_status := OKC_API.G_RET_STS_ERROR;
1645
1646 -- halt further validation of this column
1647 RAISE G_EXCEPTION_HALT_VALIDATION;
1648 END IF;
1649 END IF;
1650
1651 IF (p_cnhv_rec.one_time_yn IS NOT NULL) OR
1652 (p_cnhv_rec.one_time_yn <> OKC_API.G_MISS_CHAR)
1653 THEN
1654 -- check if one_time_yn is in uppercase
1655 IF (p_cnhv_rec.one_time_yn) <> UPPER(p_cnhv_rec.one_time_yn)
1656 THEN
1657 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1658 ,p_msg_name => g_uppercase_required
1659 ,p_token1 => g_col_name_token
1660 ,p_token1_value => 'one_time_yn');
1661 x_return_status := OKC_API.G_RET_STS_ERROR;
1662 RAISE G_EXCEPTION_HALT_VALIDATION;
1663
1664 -- verify that data is within allowable values
1665 ELSIF (UPPER(p_cnhv_rec.one_time_yn) NOT IN ('Y','N')) THEN
1666 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1667 p_msg_name => g_invalid_value,
1668 p_token1 => g_col_name_token,
1669 p_token1_value => 'one_time_yn');
1670 -- notify caller of an error
1671 x_return_status := OKC_API.G_RET_STS_ERROR;
1672
1673 -- halt further validation of this column
1674 RAISE G_EXCEPTION_HALT_VALIDATION;
1675 END IF;
1676 END IF;
1677
1678 EXCEPTION
1679 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1680 -- no processing neccessary; validation can continue
1681 -- with next column
1682 NULL;
1683
1684 WHEN OTHERS THEN
1685 -- store SQL error message on message stack for caller
1686 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1687 p_msg_name => g_unexpected_error,
1688 p_token1 => g_sqlcode_token,
1689 p_token1_value => sqlcode,
1690 p_token2 => g_sqlerrm_token,
1691 p_token2_value => sqlerrm);
1692
1693 -- notify caller of an UNEXPECTED error
1694 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1695
1696 END Validate_One_Time_YN;
1697
1698 ---------------------------------------------------------------------------
1699 -- PROCEDURE Validate_Cnh_Type
1700 ---------------------------------------------------------------------------
1701 -- Start of comments
1702 --
1703 -- Procedure Name : Validate_Cnh_Type
1704 -- Description :
1705 -- Business Rules :
1706 -- Parameters :
1707 -- Version : 1.0
1708 -- End of comments
1709 ---------------------------------------------------------------------------
1710 PROCEDURE Validate_Cnh_Type(x_return_status OUT NOCOPY VARCHAR2
1711 ,p_cnhv_rec IN cnhv_rec_type)
1712 IS
1713
1714 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1715 l_acn_type okc_actions_v.acn_type%TYPE;
1716 BEGIN
1717 -- initialize return status
1718 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1719
1720 -- data is required
1721 IF (p_cnhv_rec.cnh_type IS NULL) OR
1722 (p_cnhv_rec.cnh_type = OKC_API.G_MISS_CHAR)
1723 THEN
1724 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1725 ,p_msg_name => g_required_value
1726 ,p_token1 => g_col_name_token
1727 ,p_token1_value => 'cnh_type');
1728
1729 -- notify caller of an error
1730 x_return_status := OKC_API.G_RET_STS_ERROR;
1731
1732 -- halt further validation of this column
1733 RAISE G_EXCEPTION_HALT_VALIDATION;
1734 END IF;
1735
1736 -- check if cnh_type is in uppercase
1737 IF (p_cnhv_rec.cnh_type) <> UPPER(p_cnhv_rec.cnh_type)
1738 THEN
1739 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1740 ,p_msg_name => g_uppercase_required
1741 ,p_token1 => g_col_name_token
1742 ,p_token1_value => 'cnh_type');
1743 x_return_status := OKC_API.G_RET_STS_ERROR;
1744 RAISE G_EXCEPTION_HALT_VALIDATION;
1745 END IF;
1746
1747 -- verify that data is within allowable values
1748 IF (UPPER(p_cnhv_rec.cnh_type) NOT IN ('ABC','DBC')) THEN
1749 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1750 p_msg_name => g_invalid_value,
1751 p_token1 => g_col_name_token,
1752 p_token1_value => 'cnh_type');
1753 -- notify caller of an error
1754 x_return_status := OKC_API.G_RET_STS_ERROR;
1755
1756 -- halt further validation of this column
1757 RAISE G_EXCEPTION_HALT_VALIDATION;
1758 END IF;
1759
1760 -- verify if cnh_type corresponds to acn_type
1761 IF p_cnhv_rec.cnh_type = 'ABC' THEN
1762 select acn.acn_type into l_acn_type from okc_actions_v acn
1763 where acn.id = p_cnhv_rec.acn_id;
1764 IF l_acn_type <> 'ABA' THEN
1765 -- notify caller of an error
1766 x_return_status := OKC_API.G_RET_STS_ERROR;
1767 -- halt further validation of this column
1768 RAISE G_EXCEPTION_HALT_VALIDATION;
1769 END IF;
1770 ELSIF p_cnhv_rec.cnh_type = 'DBC' THEN
1771 select acn.acn_type into l_acn_type from okc_actions_v acn
1772 where acn.id = p_cnhv_rec.acn_id;
1773 IF l_acn_type <> 'DBA' THEN
1774 -- notify caller of an error
1775 x_return_status := OKC_API.G_RET_STS_ERROR;
1776 -- halt further validation of this column
1777 RAISE G_EXCEPTION_HALT_VALIDATION;
1778 END IF;
1779 END IF;
1780
1781 EXCEPTION
1782 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1783 -- no processing neccessary; validation can continue
1784 -- with next column
1785 NULL;
1786
1787 WHEN OTHERS THEN
1788 -- store SQL error message on message stack for caller
1789 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1790 p_msg_name => g_unexpected_error,
1791 p_token1 => g_sqlcode_token,
1792 p_token1_value => sqlcode,
1793 p_token2 => g_sqlerrm_token,
1794 p_token2_value => sqlerrm);
1795
1796 -- notify caller of an UNEXPECTED error
1797 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1798
1799 END Validate_Cnh_Type;
1800
1801 ---------------------------------------------------------------------------
1802 -- PROCEDURE Validate_Before_After
1803 ---------------------------------------------------------------------------
1804 -- Start of comments
1805 --
1806 -- Procedure Name : Validate_Before_After
1807 -- Description :
1808 -- Business Rules :
1809 -- Parameters :
1810 -- Version : 1.0
1811 -- End of comments
1812 ---------------------------------------------------------------------------
1813 PROCEDURE Validate_Before_After(x_return_status OUT NOCOPY VARCHAR2
1814 ,p_cnhv_rec IN cnhv_rec_type)
1815 IS
1816
1817 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1818
1819 BEGIN
1820 -- initialize return status
1821 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1822
1823 -- data is required when cnh_type is 'DBC'
1824 IF p_cnhv_rec.cnh_type = 'DBC'
1825 THEN
1826 IF (p_cnhv_rec.before_after IS NULL) OR
1827 (p_cnhv_rec.before_after = OKC_API.G_MISS_CHAR)
1828 THEN
1829 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1830 ,p_msg_name => g_required_value
1831 ,p_token1 => g_col_name_token
1832 ,p_token1_value => 'before_after');
1833
1834 -- notify caller of an error
1835 x_return_status := OKC_API.G_RET_STS_ERROR;
1836
1837 -- halt further validation of this column
1838 RAISE G_EXCEPTION_HALT_VALIDATION;
1839 END IF;
1840 ELSIF
1841 p_cnhv_rec.cnh_type = 'ABC'
1842 THEN
1843 IF (p_cnhv_rec.before_after IS NOT NULL) OR
1844 (p_cnhv_rec.before_after <> OKC_API.G_MISS_CHAR)
1845 THEN
1846 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1847 ,p_msg_name => g_required_value
1848 ,p_token1 => g_col_name_token
1849 ,p_token1_value => 'before_after');
1850
1851 -- notify caller of an error
1852 x_return_status := OKC_API.G_RET_STS_ERROR;
1853
1854 -- halt further validation of this column
1855 RAISE G_EXCEPTION_HALT_VALIDATION;
1856 END IF;
1857 END IF;
1858
1859 -- check if before_after is in uppercase
1860 IF (p_cnhv_rec.before_after) <> UPPER(p_cnhv_rec.before_after)
1861 THEN
1862 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1863 ,p_msg_name => g_uppercase_required
1864 ,p_token1 => g_col_name_token
1865 ,p_token1_value => 'before_after');
1866 x_return_status := OKC_API.G_RET_STS_ERROR;
1867 RAISE G_EXCEPTION_HALT_VALIDATION;
1868 END IF;
1869
1870 -- verify that data is within allowable values
1871 IF (UPPER(p_cnhv_rec.before_after) NOT IN ('B','A')) THEN
1872 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1873 p_msg_name => g_invalid_value,
1874 p_token1 => g_col_name_token,
1875 p_token1_value => 'before_after');
1876 -- notify caller of an error
1877 x_return_status := OKC_API.G_RET_STS_ERROR;
1878
1879 -- halt further validation of this column
1880 RAISE G_EXCEPTION_HALT_VALIDATION;
1881 END IF;
1882
1883 EXCEPTION
1884 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1885 -- no processing neccessary; validation can continue
1886 -- with next column
1887 NULL;
1888
1889 WHEN OTHERS THEN
1890 -- store SQL error message on message stack for caller
1891 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1892 p_msg_name => g_unexpected_error,
1893 p_token1 => g_sqlcode_token,
1894 p_token1_value => sqlcode,
1895 p_token2 => g_sqlerrm_token,
1896 p_token2_value => sqlerrm);
1897
1898 -- notify caller of an UNEXPECTED error
1899 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1900
1901 END Validate_Before_After;
1902
1903 ---------------------------------------------------------------------------
1904 -- PROCEDURE Validate_Cnh_Variance
1905 ---------------------------------------------------------------------------
1906 -- Start of comments
1907 --
1908 -- Procedure Name : Validate_Cnh_Variance
1909 -- Description :
1910 -- Business Rules :
1911 -- Parameters :
1912 -- Version : 1.0
1913 -- End of comments
1914 ---------------------------------------------------------------------------
1915 PROCEDURE Validate_Cnh_Variance(x_return_status OUT NOCOPY VARCHAR2
1916 ,p_cnhv_rec IN cnhv_rec_type)
1917 IS
1918
1919 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1920
1921 BEGIN
1922 -- initialize return status
1923 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1924
1925 -- data is required when cnh_type is 'DBC'
1926 IF p_cnhv_rec.cnh_type = 'DBC'
1927 THEN
1928 IF (p_cnhv_rec.cnh_variance IS NULL) OR
1929 (p_cnhv_rec.cnh_variance = OKC_API.G_MISS_NUM)
1930 THEN
1931 -- pnayani 11-AUG-2004 Bug#3824277 changed the message for cnh_variance
1932 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1933 ,p_msg_name =>'OKC_NUM_DAYS_REQUIRED'
1934 );
1935
1936 -- notify caller of an error
1937 x_return_status := OKC_API.G_RET_STS_ERROR;
1938
1939 -- halt further validation of this column
1940 RAISE G_EXCEPTION_HALT_VALIDATION;
1941 END IF;
1942 ELSIF
1943 p_cnhv_rec.cnh_type = 'ABC'
1944 THEN
1945 IF (p_cnhv_rec.cnh_variance IS NOT NULL) OR
1946 (p_cnhv_rec.cnh_variance <> OKC_API.G_MISS_NUM)
1947 THEN
1948 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1949 ,p_msg_name => g_required_value
1950 ,p_token1 => g_col_name_token
1951 ,p_token1_value => 'cnh_variance');
1952
1953 -- notify caller of an error
1954 x_return_status := OKC_API.G_RET_STS_ERROR;
1955
1956 -- halt further validation of this column
1957 RAISE G_EXCEPTION_HALT_VALIDATION;
1958 END IF;
1959 END IF;
1960
1961 EXCEPTION
1962 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1963 -- no processing neccessary; validation can continue
1964 -- with next column
1965 NULL;
1966
1967 WHEN OTHERS THEN
1968 -- store SQL error message on message stack for caller
1969 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1970 p_msg_name => g_unexpected_error,
1971 p_token1 => g_sqlcode_token,
1972 p_token1_value => sqlcode,
1973 p_token2 => g_sqlerrm_token,
1974 p_token2_value => sqlerrm);
1975
1976 -- notify caller of an UNEXPECTED error
1977 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1978
1979 END Validate_Cnh_Variance;
1980
1981 ---------------------------------------------------------------------------
1982 -- PROCEDURE Validate_date_active
1983 ---------------------------------------------------------------------------
1984 -- Start of comments
1985 --
1986 -- Procedure date_active : Validate_Date_Active
1987 -- Description :
1988 -- Business Rules :
1989 -- Parameters :
1990 -- Version : 1.0
1991 -- End of comments
1992 ---------------------------------------------------------------------------
1993 PROCEDURE Validate_date_active(x_return_status OUT NOCOPY VARCHAR2
1994 ,p_cnhv_rec IN cnhv_rec_type)
1995 IS
1996
1997 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1998 CURSOR cnh_cur IS
1999 SELECT 'X'
2000 FROM okc_condition_headers_v cnh
2001 WHERE cnh.id = p_cnhv_rec.id;
2002 cnh_rec cnh_cur%ROWTYPE;
2003
2004 BEGIN
2005 -- initialize return status
2006 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2007
2008 -- data is required
2009 IF (p_cnhv_rec.date_active IS NULL) OR
2010 (p_cnhv_rec.date_active = OKC_API.G_MISS_DATE)
2011 THEN
2012 OKC_API.SET_MESSAGE(p_app_name => g_app_name
2013 ,p_msg_name => g_required_value
2014 ,p_token1 => g_col_name_token
2015 ,p_token1_value => 'date_active');
2016
2017 -- notify caller of an error
2018 x_return_status := OKC_API.G_RET_STS_ERROR;
2019 -- halt further validation of this column
2020 RAISE G_EXCEPTION_HALT_VALIDATION;
2021 ELSIF TRUNC(p_cnhv_rec.date_active) < TRUNC(SYSDATE)
2022 THEN OPEN cnh_cur;
2023 IF cnh_cur%NOTFOUND THEN
2024 OKC_API.SET_MESSAGE(p_app_name => g_app_name
2025 ,p_msg_name => g_invalid_value
2026 ,p_token1 => g_col_name_token
2027 ,p_token1_value => 'date_active');
2028
2029 -- notify caller of an error
2030 x_return_status := OKC_API.G_RET_STS_ERROR;
2031 -- halt further validation of this column
2032 RAISE G_EXCEPTION_HALT_VALIDATION;
2033 END IF;
2034 END IF;
2035
2036
2037 EXCEPTION
2038 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2039 -- no processing necessary; validation can continue
2040 -- with the next column
2041 NULL;
2042
2043 WHEN OTHERS THEN
2044 -- store SQL error message on message stack for caller
2045 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2046 p_msg_name => g_unexpected_error,
2047 p_token1 => g_sqlcode_token,
2048 p_token1_value => sqlcode,
2049 p_token2 => g_sqlerrm_token,
2050 p_token2_value => sqlerrm);
2051
2052 -- notify caller of an UNEXPECTED error
2053 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2054
2055 END Validate_date_active;
2056
2057 ---------------------------------------------------------------------------
2058 -- PROCEDURE Validate_date_inactive
2059 ---------------------------------------------------------------------------
2060 -- Start of comments
2061 --
2062 -- Procedure date_inactive : Validate_Date_Inactive
2063 -- Description :
2064 -- Business Rules :
2065 -- Parameters :
2066 -- Version : 1.0
2067 -- End of comments
2068 ---------------------------------------------------------------------------
2069 PROCEDURE Validate_date_inactive(x_return_status OUT NOCOPY VARCHAR2
2070 ,p_cnhv_rec IN cnhv_rec_type)
2071 IS
2072
2073 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2074
2075 BEGIN
2076 -- initialize return status
2077 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2078
2079
2080 -- check for data before processing
2081 IF p_cnhv_rec.date_inactive IS NOT NULL OR
2082 p_cnhv_rec.date_inactive <> OKC_API.G_MISS_DATE THEN
2083 IF TRUNC(p_cnhv_rec.date_inactive) < TRUNC(p_cnhv_rec.date_active) THEN
2084
2085 OKC_API.SET_MESSAGE(p_app_name => g_app_name
2086 ,p_msg_name => g_invalid_value
2087 ,p_token1 => g_col_name_token
2088 ,p_token1_value => 'date_inactive');
2089
2090 -- notify caller of an error
2091 x_return_status := OKC_API.G_RET_STS_ERROR;
2092 -- halt further validation of this column
2093 RAISE G_EXCEPTION_HALT_VALIDATION;
2094
2095 END IF;
2096 END IF;
2097
2098
2099 EXCEPTION
2100 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2101 -- no processing necessary; validation can continue
2102 -- with the next column
2103 NULL;
2104
2105 WHEN OTHERS THEN
2106 -- store SQL error message on message stack for caller
2107 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2108 p_msg_name => g_unexpected_error,
2109 p_token1 => g_sqlcode_token,
2110 p_token1_value => sqlcode,
2111 p_token2 => g_sqlerrm_token,
2112 p_token2_value => sqlerrm);
2113
2114 -- notify caller of an UNEXPECTED error
2115 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2116
2117 END Validate_date_inactive;
2118
2119 ---------------------------------------------------------------------------
2120 -- PROCEDURE Validate_Unique_Cnh_Record
2121 ---------------------------------------------------------------------------
2122 -- Start of comments
2123 --
2124 -- Procedure Name : Validate_Unique_Cnh_Record
2125 -- Description :
2126 -- Business Rules :
2127 -- Parameters :
2128 -- Version : 1.0
2129 -- End of comments
2130 ---------------------------------------------------------------------------
2131 PROCEDURE Validate_Unique_Cnh_Record(x_return_status OUT NOCOPY VARCHAR2
2132 ,p_cnhv_rec IN cnhv_rec_type) IS
2133
2134
2135 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2136 --l_unq_tbl OKC_UTIL.unq_tbl_type;
2137 l_dummy VARCHAR2(1);
2138 l_row_found Boolean := False;
2139 CURSOR c1 IS
2140 SELECT 'x'
2141 FROM okc_condition_headers_v
2142 WHERE name = p_cnhv_rec.name
2143 AND object_id = p_cnhv_rec.object_id
2144 AND jtot_object_code = p_cnhv_rec.jtot_object_code
2145 AND id <> nvl(p_cnhv_rec.id,-99999);
2146
2147 CURSOR c2 is
2148 SELECT 'x'
2149 FROM okc_condition_headers_v
2150 WHERE name = p_cnhv_rec.name
2151 AND object_id is null
2152 AND jtot_object_code is null
2153 AND id <> nvl(p_cnhv_rec.id,-99999);
2154
2155 BEGIN
2156
2157 -- initialize return status
2158 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2159
2160 /*Bug 1636056:The following code commented out nocopy since it was not using bind
2161 variables and parsing was taking place.Replaced with explicit cursor
2162 as above
2163
2164 l_unq_tbl(1).p_col_name := 'name';
2165 l_unq_tbl(1).p_col_val := p_cnhv_rec.name;
2166 l_unq_tbl(2).p_col_name := 'object_id';
2167 l_unq_tbl(2).p_col_val := p_cnhv_rec.object_id;
2168 l_unq_tbl(3).p_col_name := 'jtot_object_code';
2169 l_unq_tbl(3).p_col_val := p_cnhv_rec.jtot_object_code;
2170 -- initialize return status
2171 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2172
2173 -- call check_comp_unique utility
2174 OKC_UTIL.CHECK_COMP_UNIQUE(p_view_name => 'OKC_CONDITION_HEADERS_V'
2175 ,p_col_tbl => l_unq_tbl
2176 ,p_id => p_cnhv_rec.id
2177 ,x_return_status => l_return_status);
2178
2179
2180 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2181 -- notify caller of an error
2182 x_return_status := OKC_API.G_RET_STS_ERROR;
2183 -- halt further validation of this column
2184 RAISE G_EXCEPTION_HALT_VALIDATION;
2185 END IF;
2186 */
2187 IF p_cnhv_rec.OBJECT_ID IS NOT NULL AND
2188 p_cnhv_rec.JTOT_OBJECT_CODE IS NOT NULL
2189 THEN
2190 OPEN c1;
2191 FETCH c1 into l_dummy;
2192 l_row_found := c1%FOUND;
2193 CLOSE c1;
2194 ELSE
2195 OPEN c2;
2196 FETCH c2 into l_dummy;
2197 l_row_found := c2%FOUND;
2198 CLOSE c2;
2199 END IF;
2200 IF l_row_found then
2201 --OKC_API.set_message(G_APP_NAME,G_UNQS,G_COL_NAME_TOKEN1,'name',G_COL_NAME_TOKEN2,'object_id',G_COL_NAME_TOKEN3,'jtot_object_code');
2202 OKC_API.set_message(G_APP_NAME,G_UNQS);
2203 x_return_status := OKC_API.G_RET_STS_ERROR;
2204 RAISE G_EXCEPTION_HALT_VALIDATION;
2205 END IF;
2206
2207 EXCEPTION
2208 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2209 -- no processing necessary; validation can continue
2210 -- with the next column
2211 NULL;
2212
2213 WHEN OTHERS THEN
2214 -- store SQL error message on message stack for caller
2215 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2216 p_msg_name => g_unexpected_error,
2217 p_token1 => g_sqlcode_token,
2218 p_token1_value => sqlcode,
2219 p_token2 => g_sqlerrm_token,
2220 p_token2_value => sqlerrm);
2221
2222 -- notify caller of an UNEXPECTED error
2223 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2224
2225 END Validate_Unique_Cnh_Record;
2226
2227 ---------------------------------------------------------------------------
2228 -- FUNCTION Validate_Foreign_Keys
2229 ---------------------------------------------------------------------------
2230 -- Start of comments
2231 --
2232 -- Function Name : Validate_Foreign_Keys
2233 -- Description :
2234 -- Business Rules :
2235 -- Parameters :
2236 -- Version : 1.0
2237 -- End of comments
2238 ---------------------------------------------------------------------------
2239 FUNCTION Validate_Foreign_Keys (p_cnhv_rec IN cnhv_rec_type)
2240 RETURN VARCHAR2 IS
2241 item_not_found_error EXCEPTION;
2242
2243 CURSOR okc_acnv_pk_csr (p_id IN NUMBER) IS
2244 SELECT '1'
2245 FROM Okc_Actions_V
2246 WHERE okc_actions_v.id = p_id;
2247 l_dummy_var VARCHAR2(1);
2248 l_dummy VARCHAR2(1);
2249 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2250 l_row_notfound BOOLEAN := TRUE;
2251
2252 BEGIN
2253 IF (p_cnhv_rec.ACN_ID IS NOT NULL)
2254 THEN
2255 OPEN okc_acnv_pk_csr(p_cnhv_rec.ACN_ID);
2256 FETCH okc_acnv_pk_csr INTO l_dummy_var;
2257 l_row_notfound := okc_acnv_pk_csr%NOTFOUND;
2258 CLOSE okc_acnv_pk_csr;
2259 IF (l_row_notfound) THEN
2260 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ACN_ID');
2261 RAISE item_not_found_error;
2262 END IF;
2263 END IF;
2264 RETURN (l_return_status);
2265 EXCEPTION
2266 WHEN item_not_found_error THEN
2267 l_return_status := OKC_API.G_RET_STS_ERROR;
2268 RETURN (l_return_status);
2269 END Validate_Foreign_Keys;
2270
2271 ---------------------------------------------------------------------------
2272 -- PROCEDURE Validate_Attributes
2273 ---------------------------------------------------------------------------
2274 -----------------------------------------------------
2275 -- Validate_Attributes for:OKC_CONDITION_HEADERS_V --
2276 -----------------------------------------------------
2277 FUNCTION Validate_Attributes (
2278 p_cnhv_rec IN cnhv_rec_type
2279 ) RETURN VARCHAR2 IS
2280 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2281 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2282 BEGIN
2283
2284 -- call each column-level validation
2285
2286 -- Validate Sfwt_Flag
2287 Validate_Sfwt_Flag(x_return_status,p_cnhv_rec);
2288 -- store the highest degree of error
2289 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2290 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2291 -- need to leave
2292 l_return_status := x_return_status;
2293 RAISE G_EXCEPTION_HALT_VALIDATION;
2294 ELSE
2295 -- record that there was an error
2296 l_return_status := x_return_status;
2297 END IF;
2298 END IF;
2299
2300 -- Validate Seeded_Flag
2301 Validate_Seeded_Flag(x_return_status,p_cnhv_rec);
2302 -- store the highest degree of error
2303 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2304 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2305 -- need to leave
2306 l_return_status := x_return_status;
2307 RAISE G_EXCEPTION_HALT_VALIDATION;
2308 ELSE
2309 -- record that there was an error
2310 l_return_status := x_return_status;
2311 END IF;
2312 END IF;
2313
2314 -- Validate application_id
2315 Validate_application_id(x_return_status,p_cnhv_rec);
2316 -- store the highest degree of error
2317 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2318 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2319 -- need to leave
2320 l_return_status := x_return_status;
2321 RAISE G_EXCEPTION_HALT_VALIDATION;
2322 ELSE
2323 -- record that there was an error
2324 l_return_status := x_return_status;
2325 END IF;
2326 END IF;
2327
2328
2329 -- Validate Acn_Id
2330 Validate_Acn_Id(x_return_status,p_cnhv_rec);
2331 -- store the highest degree of error
2332 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2333 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2334 -- need to leave
2335 l_return_status := x_return_status;
2336 RAISE G_EXCEPTION_HALT_VALIDATION;
2337 ELSE
2338 -- record that there was an error
2339 l_return_status := x_return_status;
2340 END IF;
2341 END IF;
2342
2343 -- Validate Counter_Group_Id
2344 Validate_Counter_Group_Id(x_return_status,p_cnhv_rec);
2345 -- store the highest degree of error
2346 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2347 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2348 -- need to leave
2349 l_return_status := x_return_status;
2350 RAISE G_EXCEPTION_HALT_VALIDATION;
2351 ELSE
2352 -- record that there was an error
2353 l_return_status := x_return_status;
2354 END IF;
2355 END IF;
2356
2357 -- Validate One_Time_YN
2358 Validate_One_Time_YN(x_return_status,p_cnhv_rec);
2359 -- store the highest degree of error
2360 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2361 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2362 -- need to leave
2363 l_return_status := x_return_status;
2364 RAISE G_EXCEPTION_HALT_VALIDATION;
2365 ELSE
2366 -- record that there was an error
2367 l_return_status := x_return_status;
2368 END IF;
2369 END IF;
2370
2371 -- Validate Name
2372 Validate_Name(x_return_status,p_cnhv_rec);
2373 -- store the highest degree of error
2374 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2375 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2376 -- need to leave
2377 l_return_status := x_return_status;
2378 RAISE G_EXCEPTION_HALT_VALIDATION;
2379 ELSE
2380 -- record that there was an error
2381 l_return_status := x_return_status;
2382 END IF;
2383 END IF;
2384
2385 -- Validate Condition_Valid_YN
2386 Validate_Condition_Valid_YN(x_return_status,p_cnhv_rec);
2387 -- store the highest degree of error
2388 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2389 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2390 -- need to leave
2391 l_return_status := x_return_status;
2392 RAISE G_EXCEPTION_HALT_VALIDATION;
2393 ELSE
2394 -- record that there was an error
2395 l_return_status := x_return_status;
2396 END IF;
2397 END IF;
2398
2399 -- Validate Before_After
2400 Validate_Before_After(x_return_status,p_cnhv_rec);
2401 -- store the highest degree of error
2402 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2403 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2404 -- need to leave
2405 l_return_status := x_return_status;
2406 RAISE G_EXCEPTION_HALT_VALIDATION;
2407 ELSE
2408 -- record that there was an error
2409 l_return_status := x_return_status;
2410 END IF;
2411 END IF;
2412
2413 -- Validate Template_YN
2414 Validate_Template_YN(x_return_status,p_cnhv_rec);
2415 -- store the highest degree of error
2416 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2417 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2418 -- need to leave
2419 l_return_status := x_return_status;
2420 RAISE G_EXCEPTION_HALT_VALIDATION;
2421 ELSE
2422 -- record that there was an error
2423 l_return_status := x_return_status;
2424 END IF;
2425 END IF;
2426
2427 -- Validate Tracked_YN
2428 Validate_Tracked_YN(x_return_status,p_cnhv_rec);
2429 -- store the highest degree of error
2430 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2431 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2432 -- need to leave
2433 l_return_status := x_return_status;
2434 RAISE G_EXCEPTION_HALT_VALIDATION;
2435 ELSE
2436 -- record that there was an error
2437 l_return_status := x_return_status;
2438 END IF;
2439 END IF;
2440
2441 -- Validate Cnh_Variance
2442 Validate_Cnh_Variance(x_return_status,p_cnhv_rec);
2443 -- store the highest degree of error
2444 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2445 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2446 -- need to leave
2447 l_return_status := x_return_status;
2448 RAISE G_EXCEPTION_HALT_VALIDATION;
2449 ELSE
2450 -- record that there was an error
2451 l_return_status := x_return_status;
2452 END IF;
2453 END IF;
2454
2455 -- Validate Date_Active
2456 Validate_Date_Active(x_return_status,p_cnhv_rec);
2457 -- store the highest degree of error
2458 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2459 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2460 -- need to leave
2461 l_return_status := x_return_status;
2462 RAISE G_EXCEPTION_HALT_VALIDATION;
2463 ELSE
2464 -- record that there was an error
2465 l_return_status := x_return_status;
2466 END IF;
2467 END IF;
2468
2469 -- Validate Date_Inactive
2470 Validate_Date_Inactive(x_return_status,p_cnhv_rec);
2471 -- store the highest degree of error
2472 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2473 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2474 -- need to leave
2475 l_return_status := x_return_status;
2476 RAISE G_EXCEPTION_HALT_VALIDATION;
2477 ELSE
2478 -- record that there was an error
2479 l_return_status := x_return_status;
2480 END IF;
2481 END IF;
2482
2483 -- Validate Task_Owner_id
2484 Validate_Task_Owner_id(x_return_status,p_cnhv_rec);
2485 -- store the highest degree of error
2486 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2487 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2488 -- need to leave
2489 l_return_status := x_return_status;
2490 RAISE G_EXCEPTION_HALT_VALIDATION;
2491 ELSE
2492 -- record that there was an error
2493 l_return_status := x_return_status;
2494 END IF;
2495 END IF;
2496
2497 -- Validate Object_Id
2498 Validate_Object_id(x_return_status,p_cnhv_rec);
2499 -- store the highest degree of error
2500 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2501 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2502 -- need to leave
2503 l_return_status := x_return_status;
2504 RAISE G_EXCEPTION_HALT_VALIDATION;
2505 ELSE
2506 -- record that there was an error
2507 l_return_status := x_return_status;
2508 END IF;
2509 END IF;
2510
2511 -- Validate Cnh_Type
2512 Validate_Cnh_Type(x_return_status,p_cnhv_rec);
2513 -- store the highest degree of error
2514 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2515 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2516 -- need to leave
2517 l_return_status := x_return_status;
2518 RAISE G_EXCEPTION_HALT_VALIDATION;
2519 ELSE
2520 -- record that there was an error
2521 l_return_status := x_return_status;
2522 END IF;
2523 END IF;
2524
2525 RETURN(l_return_status);
2526 EXCEPTION
2527 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2528 -- just come out with return status
2529 NULL;
2530 RETURN (l_return_status);
2531 WHEN OTHERS THEN
2532 -- store SQL error message on message stack for caller
2533 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2534 p_msg_name => g_unexpected_error,
2535 p_token1 => g_sqlcode_token,
2536 p_token1_value => sqlcode,
2537 p_token2 => g_sqlerrm_token,
2538 p_token2_value => sqlerrm);
2539 -- notify caller of an UNEXPECTED error
2540 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2541 RETURN(l_return_status);
2542 END Validate_Attributes;
2543
2544 ---------------------------------------------------------------------------
2545 -- PROCEDURE Validate_Record
2546 ---------------------------------------------------------------------------
2547 -------------------------------------------------
2548 -- Validate_Record for:OKC_CONDITION_HEADERS_V --
2549 -------------------------------------------------
2550 FUNCTION Validate_Record (
2551 p_cnhv_rec IN cnhv_rec_type
2552 ) RETURN VARCHAR2 IS
2553 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2554 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2555 BEGIN
2556 l_return_status := validate_foreign_keys (p_cnhv_rec);
2557 IF p_cnhv_rec.date_inactive IS NOT NULL OR
2558 p_cnhv_rec.date_inactive <> OKC_API.G_MISS_DATE THEN
2559 IF TRUNC(p_cnhv_rec.date_inactive) < TRUNC(p_cnhv_rec.date_active) THEN
2560 OKC_API.SET_MESSAGE(p_app_name => g_app_name
2561 ,p_msg_name => g_invalid_value
2562 ,p_token1 => g_col_name_token
2563 ,p_token1_value => 'date_inactive');
2564
2565 -- notify caller of an error
2566 l_return_status := OKC_API.G_RET_STS_ERROR;
2567 -- halt further validation of this column
2568 RAISE G_EXCEPTION_HALT_VALIDATION;
2569 END IF;
2570 END IF;
2571 /*
2572 -- Validate_Unique_Cnh_Record
2573 Validate_Unique_Cnh_Record(x_return_status,p_cnhv_rec);
2574 l_return_status := x_return_status;
2575 -- store the highest degree of error
2576 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2577 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2578 ELSIF
2579 (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2580 RAISE OKC_API.G_EXCEPTION_ERROR;
2581 END IF;
2582 */
2583 RETURN (l_return_status);
2584
2585 EXCEPTION
2586 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2587 -- just come out with return status
2588 NULL;
2589 RETURN (l_return_status);
2590 WHEN OTHERS THEN
2591 -- store SQL error message on message stack for caller
2592 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2593 p_msg_name => g_unexpected_error,
2594 p_token1 => g_sqlcode_token,
2595 p_token1_value => sqlcode,
2596 p_token2 => g_sqlerrm_token,
2597 p_token2_value => sqlerrm);
2598 -- notify caller of an UNEXPECTED error
2599 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2600 END Validate_Record;
2601
2602 /****************End Hand Written Code **********************************/
2603
2604 ---------------------------------------------------------------------------
2605 -- PROCEDURE Migrate
2606 ---------------------------------------------------------------------------
2607 PROCEDURE migrate (
2608 p_from IN cnhv_rec_type,
2609 p_to OUT NOCOPY cnh_rec_type
2610 ) IS
2611 BEGIN
2612 p_to.id := p_from.id;
2613 p_to.acn_id := p_from.acn_id;
2614 p_to.counter_group_id := p_from.counter_group_id;
2615 p_to.one_time_yn := p_from.one_time_yn;
2616 p_to.before_after := p_from.before_after;
2617 p_to.cnh_variance := p_from.cnh_variance;
2618 p_to.condition_valid_yn := p_from.condition_valid_yn;
2619 p_to.tracked_yn := p_from.tracked_yn;
2620 p_to.date_active := p_from.date_active;
2621 p_to.date_inactive := p_from.date_inactive;
2622 p_to.cnh_type := p_from.cnh_type;
2623 p_to.template_yn := p_from.template_yn;
2624 p_to.dnz_chr_id := p_from.dnz_chr_id;
2625 p_to.object_id := p_from.object_id;
2626 p_to.jtot_object_code := p_from.jtot_object_code;
2627 p_to.object_version_number := p_from.object_version_number;
2628 p_to.task_owner_id := p_from.task_owner_id;
2629 p_to.created_by := p_from.created_by;
2630 p_to.creation_date := p_from.creation_date;
2631 p_to.last_updated_by := p_from.last_updated_by;
2632 p_to.last_update_date := p_from.last_update_date;
2633 p_to.last_update_login := p_from.last_update_login;
2634 p_to.attribute_category := p_from.attribute_category;
2635 p_to.attribute1 := p_from.attribute1;
2636 p_to.attribute2 := p_from.attribute2;
2637 p_to.attribute3 := p_from.attribute3;
2638 p_to.attribute4 := p_from.attribute4;
2639 p_to.attribute5 := p_from.attribute5;
2640 p_to.attribute6 := p_from.attribute6;
2641 p_to.attribute7 := p_from.attribute7;
2642 p_to.attribute8 := p_from.attribute8;
2643 p_to.attribute9 := p_from.attribute9;
2644 p_to.attribute10 := p_from.attribute10;
2645 p_to.attribute11 := p_from.attribute11;
2646 p_to.attribute12 := p_from.attribute12;
2647 p_to.attribute13 := p_from.attribute13;
2648 p_to.attribute14 := p_from.attribute14;
2649 p_to.attribute15 := p_from.attribute15;
2650 p_to.application_id := p_from.application_id;
2651 p_to.seeded_flag := p_from.seeded_flag;
2652 p_to.last_rundate := p_from.last_rundate;
2653 END migrate;
2654 PROCEDURE migrate (
2655 p_from IN cnh_rec_type,
2656 p_to IN OUT NOCOPY cnhv_rec_type
2657 ) IS
2658 BEGIN
2659 p_to.id := p_from.id;
2660 p_to.acn_id := p_from.acn_id;
2661 p_to.counter_group_id := p_from.counter_group_id;
2662 p_to.one_time_yn := p_from.one_time_yn;
2663 p_to.before_after := p_from.before_after;
2664 p_to.cnh_variance := p_from.cnh_variance;
2665 p_to.condition_valid_yn := p_from.condition_valid_yn;
2666 p_to.tracked_yn := p_from.tracked_yn;
2667 p_to.date_active := p_from.date_active;
2668 p_to.date_inactive := p_from.date_inactive;
2669 p_to.cnh_type := p_from.cnh_type;
2670 p_to.template_yn := p_from.template_yn;
2671 p_to.dnz_chr_id := p_from.dnz_chr_id;
2672 p_to.object_id := p_from.object_id;
2673 p_to.jtot_object_code := p_from.jtot_object_code;
2674 p_to.object_version_number := p_from.object_version_number;
2675 p_to.task_owner_id := p_from.task_owner_id;
2676 p_to.created_by := p_from.created_by;
2677 p_to.creation_date := p_from.creation_date;
2678 p_to.last_updated_by := p_from.last_updated_by;
2679 p_to.last_update_date := p_from.last_update_date;
2680 p_to.last_update_login := p_from.last_update_login;
2681 p_to.attribute_category := p_from.attribute_category;
2682 p_to.attribute1 := p_from.attribute1;
2683 p_to.attribute2 := p_from.attribute2;
2684 p_to.attribute3 := p_from.attribute3;
2685 p_to.attribute4 := p_from.attribute4;
2686 p_to.attribute5 := p_from.attribute5;
2687 p_to.attribute6 := p_from.attribute6;
2688 p_to.attribute7 := p_from.attribute7;
2689 p_to.attribute8 := p_from.attribute8;
2690 p_to.attribute9 := p_from.attribute9;
2691 p_to.attribute10 := p_from.attribute10;
2692 p_to.attribute11 := p_from.attribute11;
2693 p_to.attribute12 := p_from.attribute12;
2694 p_to.attribute13 := p_from.attribute13;
2695 p_to.attribute14 := p_from.attribute14;
2696 p_to.attribute15 := p_from.attribute15;
2697 p_to.application_id := p_from.application_id;
2698 p_to.seeded_flag := p_from.seeded_flag;
2699 p_to.last_rundate := p_from.last_rundate;
2700 END migrate;
2701 PROCEDURE migrate (
2702 p_from IN cnhv_rec_type,
2703 p_to OUT NOCOPY OkcConditionHeadersTlRecType
2704 ) IS
2705 BEGIN
2706 p_to.id := p_from.id;
2707 p_to.sfwt_flag := p_from.sfwt_flag;
2708 p_to.name := p_from.name;
2709 p_to.description := p_from.description;
2710 p_to.short_description := p_from.short_description;
2711 p_to.comments := p_from.comments;
2712 p_to.created_by := p_from.created_by;
2713 p_to.creation_date := p_from.creation_date;
2714 p_to.last_updated_by := p_from.last_updated_by;
2715 p_to.last_update_date := p_from.last_update_date;
2716 p_to.last_update_login := p_from.last_update_login;
2717 END migrate;
2718 PROCEDURE migrate (
2719 p_from IN OkcConditionHeadersTlRecType,
2720 p_to IN OUT NOCOPY cnhv_rec_type
2721 ) IS
2722 BEGIN
2723 p_to.id := p_from.id;
2724 p_to.sfwt_flag := p_from.sfwt_flag;
2725 p_to.name := p_from.name;
2726 p_to.description := p_from.description;
2727 p_to.short_description := p_from.short_description;
2728 p_to.comments := p_from.comments;
2729 p_to.created_by := p_from.created_by;
2730 p_to.creation_date := p_from.creation_date;
2731 p_to.last_updated_by := p_from.last_updated_by;
2732 p_to.last_update_date := p_from.last_update_date;
2733 p_to.last_update_login := p_from.last_update_login;
2734 END migrate;
2735
2736 ---------------------------------------------------------------------------
2737 -- PROCEDURE validate_row
2738 ---------------------------------------------------------------------------
2739 ----------------------------------------------
2740 -- validate_row for:OKC_CONDITION_HEADERS_V --
2741 ----------------------------------------------
2742 PROCEDURE validate_row(
2743 p_api_version IN NUMBER,
2744 p_init_msg_list IN VARCHAR2 ,
2745 x_return_status OUT NOCOPY VARCHAR2,
2746 x_msg_count OUT NOCOPY NUMBER,
2747 x_msg_data OUT NOCOPY VARCHAR2,
2748 p_cnhv_rec IN cnhv_rec_type) IS
2749
2750 l_api_version CONSTANT NUMBER := 1;
2751 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2752 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2753 l_cnhv_rec cnhv_rec_type := p_cnhv_rec;
2754 l_cnh_rec cnh_rec_type;
2755 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
2756 BEGIN
2757 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2758 G_PKG_NAME,
2759 p_init_msg_list,
2760 l_api_version,
2761 p_api_version,
2762 '_PVT',
2763 x_return_status);
2764 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2765 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2766 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2767 RAISE OKC_API.G_EXCEPTION_ERROR;
2768 END IF;
2769 --- Validate all non-missing attributes (Item Level Validation)
2770 l_return_status := Validate_Attributes(l_cnhv_rec);
2771 --- If any errors happen abort API
2772 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2773 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2774 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2775 RAISE OKC_API.G_EXCEPTION_ERROR;
2776 END IF;
2777 l_return_status := Validate_Record(l_cnhv_rec);
2778 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2779 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2780 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2781 RAISE OKC_API.G_EXCEPTION_ERROR;
2782 END IF;
2783 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2784 EXCEPTION
2785 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2786 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2787 (
2788 l_api_name,
2789 G_PKG_NAME,
2790 'OKC_API.G_RET_STS_ERROR',
2791 x_msg_count,
2792 x_msg_data,
2793 '_PVT'
2794 );
2795 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2796 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2797 (
2798 l_api_name,
2799 G_PKG_NAME,
2800 'OKC_API.G_RET_STS_UNEXP_ERROR',
2801 x_msg_count,
2802 x_msg_data,
2803 '_PVT'
2804 );
2805 WHEN OTHERS THEN
2806 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2807 (
2808 l_api_name,
2809 G_PKG_NAME,
2810 'OTHERS',
2811 x_msg_count,
2812 x_msg_data,
2813 '_PVT'
2814 );
2815 END validate_row;
2816 ------------------------------------------
2817 -- PL/SQL TBL validate_row for:CNHV_TBL --
2818 ------------------------------------------
2819 PROCEDURE validate_row(
2820 p_api_version IN NUMBER,
2821 p_init_msg_list IN VARCHAR2 ,
2822 x_return_status OUT NOCOPY VARCHAR2,
2823 x_msg_count OUT NOCOPY NUMBER,
2824 x_msg_data OUT NOCOPY VARCHAR2,
2825 p_cnhv_tbl IN cnhv_tbl_type) IS
2826
2827 l_api_version CONSTANT NUMBER := 1;
2828 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2829 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2830 i NUMBER := 0;
2831 BEGIN
2832 OKC_API.init_msg_list(p_init_msg_list);
2833 -- Make sure PL/SQL table has records in it before passing
2834 IF (p_cnhv_tbl.COUNT > 0) THEN
2835 i := p_cnhv_tbl.FIRST;
2836 LOOP
2837 validate_row (
2838 p_api_version => p_api_version,
2839 p_init_msg_list => OKC_API.G_FALSE,
2840 x_return_status => x_return_status,
2841 x_msg_count => x_msg_count,
2842 x_msg_data => x_msg_data,
2843 p_cnhv_rec => p_cnhv_tbl(i));
2844 EXIT WHEN (i = p_cnhv_tbl.LAST);
2845 i := p_cnhv_tbl.NEXT(i);
2846 END LOOP;
2847 END IF;
2848 EXCEPTION
2849 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2850 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2851 (
2852 l_api_name,
2853 G_PKG_NAME,
2854 'OKC_API.G_RET_STS_ERROR',
2855 x_msg_count,
2856 x_msg_data,
2857 '_PVT'
2858 );
2859 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2860 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2861 (
2862 l_api_name,
2863 G_PKG_NAME,
2864 'OKC_API.G_RET_STS_UNEXP_ERROR',
2865 x_msg_count,
2866 x_msg_data,
2867 '_PVT'
2868 );
2869 WHEN OTHERS THEN
2870 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2871 (
2872 l_api_name,
2873 G_PKG_NAME,
2874 'OTHERS',
2875 x_msg_count,
2876 x_msg_data,
2877 '_PVT'
2878 );
2879 END validate_row;
2880
2881 ---------------------------------------------------------------------------
2882 -- PROCEDURE insert_row
2883 ---------------------------------------------------------------------------
2884 --------------------------------------------
2885 -- insert_row for:OKC_CONDITION_HEADERS_B --
2886 --------------------------------------------
2887 PROCEDURE insert_row(
2888 p_init_msg_list IN VARCHAR2 ,
2889 x_return_status OUT NOCOPY VARCHAR2,
2890 x_msg_count OUT NOCOPY NUMBER,
2891 x_msg_data OUT NOCOPY VARCHAR2,
2892 p_cnh_rec IN cnh_rec_type,
2893 x_cnh_rec OUT NOCOPY cnh_rec_type) IS
2894
2895 l_api_version CONSTANT NUMBER := 1;
2896 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2897 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2898 l_cnh_rec cnh_rec_type := p_cnh_rec;
2899 l_def_cnh_rec cnh_rec_type;
2900 ------------------------------------------------
2901 -- Set_Attributes for:OKC_CONDITION_HEADERS_B --
2902 ------------------------------------------------
2903 FUNCTION Set_Attributes (
2904 p_cnh_rec IN cnh_rec_type,
2905 x_cnh_rec OUT NOCOPY cnh_rec_type
2906 ) RETURN VARCHAR2 IS
2907 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2908 BEGIN
2909 x_cnh_rec := p_cnh_rec;
2910 RETURN(l_return_status);
2911 END Set_Attributes;
2912 BEGIN
2913 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2914 p_init_msg_list,
2915 '_PVT',
2916 x_return_status);
2917 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2918 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2919 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2920 RAISE OKC_API.G_EXCEPTION_ERROR;
2921 END IF;
2922 --- Setting item attributes
2923 l_return_status := Set_Attributes(
2924 p_cnh_rec, -- IN
2925 l_cnh_rec); -- OUT
2926 --- If any errors happen abort API
2927 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2928 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2929 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2930 RAISE OKC_API.G_EXCEPTION_ERROR;
2931 END IF;
2932 INSERT INTO OKC_CONDITION_HEADERS_B(
2933 id,
2934 acn_id,
2935 counter_group_id,
2936 one_time_yn,
2937 before_after,
2938 cnh_variance,
2939 condition_valid_yn,
2940 tracked_yn,
2941 date_active,
2942 date_inactive,
2943 cnh_type,
2944 template_yn,
2945 dnz_chr_id,
2946 object_id,
2947 jtot_object_code,
2948 object_version_number,
2949 task_owner_id,
2950 created_by,
2951 creation_date,
2952 last_updated_by,
2953 last_update_date,
2954 last_update_login,
2955 attribute_category,
2956 attribute1,
2957 attribute2,
2958 attribute3,
2959 attribute4,
2960 attribute5,
2961 attribute6,
2962 attribute7,
2963 attribute8,
2964 attribute9,
2965 attribute10,
2966 attribute11,
2967 attribute12,
2968 attribute13,
2969 attribute14,
2970 attribute15,
2971 application_id,
2972 seeded_flag,
2973 last_rundate)
2974 VALUES (
2975 l_cnh_rec.id,
2976 l_cnh_rec.acn_id,
2977 l_cnh_rec.counter_group_id,
2978 l_cnh_rec.one_time_yn,
2979 l_cnh_rec.before_after,
2980 l_cnh_rec.cnh_variance,
2981 l_cnh_rec.condition_valid_yn,
2982 l_cnh_rec.tracked_yn,
2983 l_cnh_rec.date_active,
2984 l_cnh_rec.date_inactive,
2985 l_cnh_rec.cnh_type,
2986 l_cnh_rec.template_yn,
2987 l_cnh_rec.dnz_chr_id,
2988 l_cnh_rec.object_id,
2989 l_cnh_rec.jtot_object_code,
2990 l_cnh_rec.object_version_number,
2991 l_cnh_rec.task_owner_id,
2992 l_cnh_rec.created_by,
2993 l_cnh_rec.creation_date,
2994 l_cnh_rec.last_updated_by,
2995 l_cnh_rec.last_update_date,
2996 l_cnh_rec.last_update_login,
2997 l_cnh_rec.attribute_category,
2998 l_cnh_rec.attribute1,
2999 l_cnh_rec.attribute2,
3000 l_cnh_rec.attribute3,
3001 l_cnh_rec.attribute4,
3002 l_cnh_rec.attribute5,
3003 l_cnh_rec.attribute6,
3004 l_cnh_rec.attribute7,
3005 l_cnh_rec.attribute8,
3006 l_cnh_rec.attribute9,
3007 l_cnh_rec.attribute10,
3008 l_cnh_rec.attribute11,
3009 l_cnh_rec.attribute12,
3010 l_cnh_rec.attribute13,
3011 l_cnh_rec.attribute14,
3012 l_cnh_rec.attribute15,
3013 l_cnh_rec.application_id,
3014 l_cnh_rec.seeded_flag,
3015 l_cnh_rec.last_rundate);
3016 -- Set OUT values
3017 x_cnh_rec := l_cnh_rec;
3018 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3019 EXCEPTION
3020 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3021 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3022 (
3023 l_api_name,
3024 G_PKG_NAME,
3025 'OKC_API.G_RET_STS_ERROR',
3026 x_msg_count,
3027 x_msg_data,
3028 '_PVT'
3029 );
3030 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3031 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3032 (
3033 l_api_name,
3034 G_PKG_NAME,
3035 'OKC_API.G_RET_STS_UNEXP_ERROR',
3036 x_msg_count,
3037 x_msg_data,
3038 '_PVT'
3039 );
3040 WHEN OTHERS THEN
3041 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3042 (
3043 l_api_name,
3044 G_PKG_NAME,
3045 'OTHERS',
3046 x_msg_count,
3047 x_msg_data,
3048 '_PVT'
3049 );
3050 END insert_row;
3051 ---------------------------------------------
3052 -- insert_row for:OKC_CONDITION_HEADERS_TL --
3053 ---------------------------------------------
3054 PROCEDURE insert_row(
3055 p_init_msg_list IN VARCHAR2 ,
3056 x_return_status OUT NOCOPY VARCHAR2,
3057 x_msg_count OUT NOCOPY NUMBER,
3058 x_msg_data OUT NOCOPY VARCHAR2,
3059 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
3060 x_okc_condition_headers_tl_rec OUT NOCOPY OkcConditionHeadersTlRecType) IS
3061
3062 l_api_version CONSTANT NUMBER := 1;
3063 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
3064 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3065 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType := p_okc_condition_headers_tl_rec;
3066 ldefokcconditionheaderstlrec OkcConditionHeadersTlRecType;
3067 CURSOR get_languages IS
3068 SELECT *
3069 FROM FND_LANGUAGES
3070 WHERE INSTALLED_FLAG IN ('I', 'B');
3071 -------------------------------------------------
3072 -- Set_Attributes for:OKC_CONDITION_HEADERS_TL --
3073 -------------------------------------------------
3074 FUNCTION Set_Attributes (
3075 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
3076 x_okc_condition_headers_tl_rec OUT NOCOPY OkcConditionHeadersTlRecType
3077 ) RETURN VARCHAR2 IS
3078 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3079 BEGIN
3080 x_okc_condition_headers_tl_rec := p_okc_condition_headers_tl_rec;
3081 x_okc_condition_headers_tl_rec.LANGUAGE := l_lang;
3082 x_okc_condition_headers_tl_rec.SOURCE_LANG := l_lang;
3083 RETURN(l_return_status);
3084 END Set_Attributes;
3085 BEGIN
3086 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3087 p_init_msg_list,
3088 '_PVT',
3089 x_return_status);
3090 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3091 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3092 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3093 RAISE OKC_API.G_EXCEPTION_ERROR;
3094 END IF;
3095 --- Setting item attributes
3096 l_return_status := Set_Attributes(
3097 p_okc_condition_headers_tl_rec, -- IN
3098 l_okc_condition_headers_tl_rec); -- OUT
3099 --- If any errors happen abort API
3100 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3101 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3102 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3103 RAISE OKC_API.G_EXCEPTION_ERROR;
3104 END IF;
3105 FOR l_lang_rec IN get_languages LOOP
3106 l_okc_condition_headers_tl_rec.language := l_lang_rec.language_code;
3107 INSERT INTO OKC_CONDITION_HEADERS_TL(
3108 id,
3109 language,
3110 source_lang,
3111 sfwt_flag,
3112 name,
3113 description,
3114 short_description,
3115 comments,
3116 created_by,
3117 creation_date,
3118 last_updated_by,
3119 last_update_date,
3120 last_update_login)
3121 VALUES (
3122 l_okc_condition_headers_tl_rec.id,
3123 l_okc_condition_headers_tl_rec.language,
3124 l_okc_condition_headers_tl_rec.source_lang,
3125 l_okc_condition_headers_tl_rec.sfwt_flag,
3126 l_okc_condition_headers_tl_rec.name,
3127 l_okc_condition_headers_tl_rec.description,
3128 l_okc_condition_headers_tl_rec.short_description,
3129 l_okc_condition_headers_tl_rec.comments,
3130 l_okc_condition_headers_tl_rec.created_by,
3131 l_okc_condition_headers_tl_rec.creation_date,
3132 l_okc_condition_headers_tl_rec.last_updated_by,
3133 l_okc_condition_headers_tl_rec.last_update_date,
3134 l_okc_condition_headers_tl_rec.last_update_login);
3135 END LOOP;
3136 -- Set OUT values
3137 x_okc_condition_headers_tl_rec := l_okc_condition_headers_tl_rec;
3138 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3139 EXCEPTION
3140 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3141 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3142 (
3143 l_api_name,
3144 G_PKG_NAME,
3145 'OKC_API.G_RET_STS_ERROR',
3146 x_msg_count,
3147 x_msg_data,
3148 '_PVT'
3149 );
3150 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3151 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3152 (
3153 l_api_name,
3154 G_PKG_NAME,
3155 'OKC_API.G_RET_STS_UNEXP_ERROR',
3156 x_msg_count,
3157 x_msg_data,
3158 '_PVT'
3159 );
3160 WHEN OTHERS THEN
3161 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3162 (
3163 l_api_name,
3164 G_PKG_NAME,
3165 'OTHERS',
3166 x_msg_count,
3167 x_msg_data,
3168 '_PVT'
3169 );
3170 END insert_row;
3171 --------------------------------------------
3172 -- insert_row for:OKC_CONDITION_HEADERS_V --
3173 --------------------------------------------
3174 PROCEDURE insert_row(
3175 p_api_version IN NUMBER,
3176 p_init_msg_list IN VARCHAR2 ,
3177 x_return_status OUT NOCOPY VARCHAR2,
3178 x_msg_count OUT NOCOPY NUMBER,
3179 x_msg_data OUT NOCOPY VARCHAR2,
3180 p_cnhv_rec IN cnhv_rec_type,
3181 x_cnhv_rec OUT NOCOPY cnhv_rec_type) IS
3182
3183 l_id NUMBER ;
3184 l_api_version CONSTANT NUMBER := 1;
3185 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3186 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3187 l_cnhv_rec cnhv_rec_type;
3188 l_def_cnhv_rec cnhv_rec_type;
3189 l_cnh_rec cnh_rec_type;
3190 lx_cnh_rec cnh_rec_type;
3191 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
3192 LxOkcConditionHeadersTlRec OkcConditionHeadersTlRecType;
3193 -------------------------------
3194 -- FUNCTION fill_who_columns --
3195 -------------------------------
3196 FUNCTION fill_who_columns (
3197 p_cnhv_rec IN cnhv_rec_type
3198 ) RETURN cnhv_rec_type IS
3199 l_cnhv_rec cnhv_rec_type := p_cnhv_rec;
3200 BEGIN
3201 l_cnhv_rec.CREATION_DATE := SYSDATE;
3202 l_cnhv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3203 l_cnhv_rec.LAST_UPDATE_DATE := l_cnhv_rec.CREATION_DATE;
3204 l_cnhv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3205 l_cnhv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3206 RETURN(l_cnhv_rec);
3207 END fill_who_columns;
3208 ------------------------------------------------
3209 -- Set_Attributes for:OKC_CONDITION_HEADERS_V --
3210 ------------------------------------------------
3211 FUNCTION Set_Attributes (
3212 p_cnhv_rec IN cnhv_rec_type,
3213 x_cnhv_rec OUT NOCOPY cnhv_rec_type
3214 ) RETURN VARCHAR2 IS
3215 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3216 BEGIN
3217 x_cnhv_rec := p_cnhv_rec;
3218 x_cnhv_rec.OBJECT_VERSION_NUMBER := 1;
3219 x_cnhv_rec.SFWT_FLAG := 'N';
3220 RETURN(l_return_status);
3221 END Set_Attributes;
3222 BEGIN
3223 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3224 G_PKG_NAME,
3225 p_init_msg_list,
3226 l_api_version,
3227 p_api_version,
3228 '_PVT',
3229 x_return_status);
3230 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3231 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3232 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3233 RAISE OKC_API.G_EXCEPTION_ERROR;
3234 END IF;
3235 l_cnhv_rec := null_out_defaults(p_cnhv_rec);
3236 -- Set primary key value
3237 -- If condition header is created by seed then use sequence generated id
3238 IF l_cnhv_rec.CREATED_BY = 1 THEN
3239 SELECT OKC_CONDITION_HEADERS_S1.nextval INTO l_id FROM dual;
3240 l_cnhv_rec.ID := l_id;
3241 l_cnhv_rec.seeded_flag := 'Y';
3242 ELSE
3243 l_cnhv_rec.ID := get_seq_id;
3244 l_cnhv_rec.seeded_flag := 'N';
3245 END IF;
3246
3247 --l_cnhv_rec.ID := get_seq_id;
3248 --- Setting item attributes
3249 l_return_status := Set_Attributes(
3250 l_cnhv_rec, -- IN
3251 l_def_cnhv_rec); -- OUT
3252 --- If any errors happen abort API
3253 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3254 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3255 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3256 RAISE OKC_API.G_EXCEPTION_ERROR;
3257 END IF;
3258 l_def_cnhv_rec := fill_who_columns(l_def_cnhv_rec);
3259 --- Validate all non-missing attributes (Item Level Validation)
3260 l_return_status := Validate_Attributes(l_def_cnhv_rec);
3261 --- If any errors happen abort API
3262 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3263 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3264 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3265 RAISE OKC_API.G_EXCEPTION_ERROR;
3266 END IF;
3267 l_return_status := Validate_Record(l_def_cnhv_rec);
3268 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3269 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3270 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3271 RAISE OKC_API.G_EXCEPTION_ERROR;
3272 END IF;
3273
3274 /************ ADDED TO CHECK THE UNIQUENESS ***********************/
3275
3276 -- Validate_Unique_Cnh_Record
3277 Validate_Unique_Cnh_Record(x_return_status,p_cnhv_rec);
3278 l_return_status := x_return_status;
3279 -- store the highest degree of error
3280 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3281 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3282 ELSIF
3283 (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3284 RAISE OKC_API.G_EXCEPTION_ERROR;
3285 END IF;
3286
3287 /*********** ADDED TO CHECK THE UNIQUENESS *************************/
3288
3289 --------------------------------------
3290 -- Move VIEW record to "Child" records
3291 --------------------------------------
3292 migrate(l_def_cnhv_rec, l_cnh_rec);
3293 migrate(l_def_cnhv_rec, l_okc_condition_headers_tl_rec);
3294 --------------------------------------------
3295 -- Call the INSERT_ROW for each child record
3296 --------------------------------------------
3297 insert_row(
3298 p_init_msg_list,
3299 x_return_status,
3300 x_msg_count,
3301 x_msg_data,
3302 l_cnh_rec,
3303 lx_cnh_rec
3304 );
3305 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3306 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3307 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3308 RAISE OKC_API.G_EXCEPTION_ERROR;
3309 END IF;
3310 migrate(lx_cnh_rec, l_def_cnhv_rec);
3311 insert_row(
3312 p_init_msg_list,
3313 x_return_status,
3314 x_msg_count,
3315 x_msg_data,
3316 l_okc_condition_headers_tl_rec,
3317 LxOkcConditionHeadersTlRec
3318 );
3319 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3320 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3321 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3322 RAISE OKC_API.G_EXCEPTION_ERROR;
3323 END IF;
3324 migrate(LxOkcConditionHeadersTlRec, l_def_cnhv_rec);
3325 -- Set OUT values
3326 x_cnhv_rec := l_def_cnhv_rec;
3327 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3328 EXCEPTION
3329 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3330 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3331 (
3332 l_api_name,
3333 G_PKG_NAME,
3334 'OKC_API.G_RET_STS_ERROR',
3335 x_msg_count,
3336 x_msg_data,
3337 '_PVT'
3338 );
3339 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3340 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3341 (
3342 l_api_name,
3343 G_PKG_NAME,
3344 'OKC_API.G_RET_STS_UNEXP_ERROR',
3345 x_msg_count,
3346 x_msg_data,
3347 '_PVT'
3348 );
3349 WHEN OTHERS THEN
3350 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3351 (
3352 l_api_name,
3353 G_PKG_NAME,
3354 'OTHERS',
3355 x_msg_count,
3356 x_msg_data,
3357 '_PVT'
3358 );
3359 END insert_row;
3360 ----------------------------------------
3361 -- PL/SQL TBL insert_row for:CNHV_TBL --
3362 ----------------------------------------
3363 PROCEDURE insert_row(
3364 p_api_version IN NUMBER,
3365 p_init_msg_list IN VARCHAR2 ,
3366 x_return_status OUT NOCOPY VARCHAR2,
3367 x_msg_count OUT NOCOPY NUMBER,
3368 x_msg_data OUT NOCOPY VARCHAR2,
3369 p_cnhv_tbl IN cnhv_tbl_type,
3370 x_cnhv_tbl OUT NOCOPY cnhv_tbl_type) IS
3371
3372 l_api_version CONSTANT NUMBER := 1;
3373 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3374 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3375 i NUMBER := 0;
3376 BEGIN
3377 OKC_API.init_msg_list(p_init_msg_list);
3378 -- Make sure PL/SQL table has records in it before passing
3379 IF (p_cnhv_tbl.COUNT > 0) THEN
3380 i := p_cnhv_tbl.FIRST;
3381 LOOP
3382 insert_row (
3383 p_api_version => p_api_version,
3384 p_init_msg_list => OKC_API.G_FALSE,
3385 x_return_status => x_return_status,
3386 x_msg_count => x_msg_count,
3387 x_msg_data => x_msg_data,
3388 p_cnhv_rec => p_cnhv_tbl(i),
3389 x_cnhv_rec => x_cnhv_tbl(i));
3390 EXIT WHEN (i = p_cnhv_tbl.LAST);
3391 i := p_cnhv_tbl.NEXT(i);
3392 END LOOP;
3393 END IF;
3394 EXCEPTION
3395 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3396 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3397 (
3398 l_api_name,
3399 G_PKG_NAME,
3400 'OKC_API.G_RET_STS_ERROR',
3401 x_msg_count,
3402 x_msg_data,
3403 '_PVT'
3404 );
3405 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3406 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3407 (
3408 l_api_name,
3409 G_PKG_NAME,
3410 'OKC_API.G_RET_STS_UNEXP_ERROR',
3411 x_msg_count,
3412 x_msg_data,
3413 '_PVT'
3414 );
3415 WHEN OTHERS THEN
3416 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3417 (
3418 l_api_name,
3419 G_PKG_NAME,
3420 'OTHERS',
3421 x_msg_count,
3422 x_msg_data,
3423 '_PVT'
3424 );
3425 END insert_row;
3426
3427 ---------------------------------------------------------------------------
3428 -- PROCEDURE lock_row
3429 ---------------------------------------------------------------------------
3430 ------------------------------------------
3431 -- lock_row for:OKC_CONDITION_HEADERS_B --
3432 ------------------------------------------
3433 PROCEDURE lock_row(
3434 p_init_msg_list IN VARCHAR2 ,
3435 x_return_status OUT NOCOPY VARCHAR2,
3436 x_msg_count OUT NOCOPY NUMBER,
3437 x_msg_data OUT NOCOPY VARCHAR2,
3438 p_cnh_rec IN cnh_rec_type) IS
3439
3440 E_Resource_Busy EXCEPTION;
3441 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3442 CURSOR lock_csr (p_cnh_rec IN cnh_rec_type) IS
3443 SELECT OBJECT_VERSION_NUMBER
3444 FROM OKC_CONDITION_HEADERS_B
3445 WHERE ID = p_cnh_rec.id
3446 AND OBJECT_VERSION_NUMBER = p_cnh_rec.object_version_number
3447 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3448
3449 CURSOR lchk_csr (p_cnh_rec IN cnh_rec_type) IS
3450 SELECT OBJECT_VERSION_NUMBER
3451 FROM OKC_CONDITION_HEADERS_B
3452 WHERE ID = p_cnh_rec.id;
3453 l_api_version CONSTANT NUMBER := 1;
3454 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3455 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3456 l_object_version_number OKC_CONDITION_HEADERS_B.OBJECT_VERSION_NUMBER%TYPE;
3457 lc_object_version_number OKC_CONDITION_HEADERS_B.OBJECT_VERSION_NUMBER%TYPE;
3458 l_row_notfound BOOLEAN := FALSE;
3459 lc_row_notfound BOOLEAN := FALSE;
3460 BEGIN
3461 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3462 p_init_msg_list,
3463 '_PVT',
3464 x_return_status);
3465 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3466 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3467 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3468 RAISE OKC_API.G_EXCEPTION_ERROR;
3469 END IF;
3470 BEGIN
3471 OPEN lock_csr(p_cnh_rec);
3472 FETCH lock_csr INTO l_object_version_number;
3473 l_row_notfound := lock_csr%NOTFOUND;
3474 CLOSE lock_csr;
3475 EXCEPTION
3476 WHEN E_Resource_Busy THEN
3477 IF (lock_csr%ISOPEN) THEN
3478 CLOSE lock_csr;
3479 END IF;
3480 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3481 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3482 END;
3483
3484 IF ( l_row_notfound ) THEN
3485 OPEN lchk_csr(p_cnh_rec);
3486 FETCH lchk_csr INTO lc_object_version_number;
3487 lc_row_notfound := lchk_csr%NOTFOUND;
3488 CLOSE lchk_csr;
3489 END IF;
3490 IF (lc_row_notfound) THEN
3491 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3492 RAISE OKC_API.G_EXCEPTION_ERROR;
3493 ELSIF lc_object_version_number > p_cnh_rec.object_version_number THEN
3494 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3495 RAISE OKC_API.G_EXCEPTION_ERROR;
3496 ELSIF lc_object_version_number <> p_cnh_rec.object_version_number THEN
3497 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3498 RAISE OKC_API.G_EXCEPTION_ERROR;
3499 ELSIF lc_object_version_number = -1 THEN
3500 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3501 RAISE OKC_API.G_EXCEPTION_ERROR;
3502 END IF;
3503 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3504 EXCEPTION
3505 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3506 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3507 (
3508 l_api_name,
3509 G_PKG_NAME,
3510 'OKC_API.G_RET_STS_ERROR',
3511 x_msg_count,
3512 x_msg_data,
3513 '_PVT'
3514 );
3515 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3516 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3517 (
3518 l_api_name,
3519 G_PKG_NAME,
3520 'OKC_API.G_RET_STS_UNEXP_ERROR',
3521 x_msg_count,
3522 x_msg_data,
3523 '_PVT'
3524 );
3525 WHEN OTHERS THEN
3526 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3527 (
3528 l_api_name,
3529 G_PKG_NAME,
3530 'OTHERS',
3531 x_msg_count,
3532 x_msg_data,
3533 '_PVT'
3534 );
3535 END lock_row;
3536 -------------------------------------------
3537 -- lock_row for:OKC_CONDITION_HEADERS_TL --
3538 -------------------------------------------
3539 PROCEDURE lock_row(
3540 p_init_msg_list IN VARCHAR2 ,
3541 x_return_status OUT NOCOPY VARCHAR2,
3542 x_msg_count OUT NOCOPY NUMBER,
3543 x_msg_data OUT NOCOPY VARCHAR2,
3544 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType) IS
3545
3546 E_Resource_Busy EXCEPTION;
3547 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3548 CURSOR lock_csr (p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType) IS
3549 SELECT *
3550 FROM OKC_CONDITION_HEADERS_TL
3551 WHERE ID = p_okc_condition_headers_tl_rec.id
3552 FOR UPDATE NOWAIT;
3553
3554 l_api_version CONSTANT NUMBER := 1;
3555 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3556 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3557 l_lock_var lock_csr%ROWTYPE;
3558 l_row_notfound BOOLEAN := FALSE;
3559 lc_row_notfound BOOLEAN := FALSE;
3560 BEGIN
3561 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3562 p_init_msg_list,
3563 '_PVT',
3564 x_return_status);
3565 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3566 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3567 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3568 RAISE OKC_API.G_EXCEPTION_ERROR;
3569 END IF;
3570 BEGIN
3571 OPEN lock_csr(p_okc_condition_headers_tl_rec);
3572 FETCH lock_csr INTO l_lock_var;
3573 l_row_notfound := lock_csr%NOTFOUND;
3574 CLOSE lock_csr;
3575 EXCEPTION
3576 WHEN E_Resource_Busy THEN
3577 IF (lock_csr%ISOPEN) THEN
3578 CLOSE lock_csr;
3579 END IF;
3580 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3581 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3582 END;
3583
3584 IF ( l_row_notfound ) THEN
3585 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3586 RAISE OKC_API.G_EXCEPTION_ERROR;
3587 END IF;
3588 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3589 EXCEPTION
3590 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3591 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3592 (
3593 l_api_name,
3594 G_PKG_NAME,
3595 'OKC_API.G_RET_STS_ERROR',
3596 x_msg_count,
3597 x_msg_data,
3598 '_PVT'
3599 );
3600 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3601 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3602 (
3603 l_api_name,
3604 G_PKG_NAME,
3605 'OKC_API.G_RET_STS_UNEXP_ERROR',
3606 x_msg_count,
3607 x_msg_data,
3608 '_PVT'
3609 );
3610 WHEN OTHERS THEN
3611 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3612 (
3613 l_api_name,
3614 G_PKG_NAME,
3615 'OTHERS',
3616 x_msg_count,
3617 x_msg_data,
3618 '_PVT'
3619 );
3620 END lock_row;
3621 ------------------------------------------
3622 -- lock_row for:OKC_CONDITION_HEADERS_V --
3623 ------------------------------------------
3624 PROCEDURE lock_row(
3625 p_api_version IN NUMBER,
3626 p_init_msg_list IN VARCHAR2 ,
3627 x_return_status OUT NOCOPY VARCHAR2,
3628 x_msg_count OUT NOCOPY NUMBER,
3629 x_msg_data OUT NOCOPY VARCHAR2,
3630 p_cnhv_rec IN cnhv_rec_type) IS
3631
3632 l_api_version CONSTANT NUMBER := 1;
3633 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3634 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3635 l_cnh_rec cnh_rec_type;
3636 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
3637 BEGIN
3638 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3639 G_PKG_NAME,
3640 p_init_msg_list,
3641 l_api_version,
3642 p_api_version,
3643 '_PVT',
3644 x_return_status);
3645 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3646 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3647 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3648 RAISE OKC_API.G_EXCEPTION_ERROR;
3649 END IF;
3650 --------------------------------------
3651 -- Move VIEW record to "Child" records
3652 --------------------------------------
3653 migrate(p_cnhv_rec, l_cnh_rec);
3654 migrate(p_cnhv_rec, l_okc_condition_headers_tl_rec);
3655 --------------------------------------------
3656 -- Call the LOCK_ROW for each child record
3657 --------------------------------------------
3658 lock_row(
3659 p_init_msg_list,
3660 x_return_status,
3661 x_msg_count,
3662 x_msg_data,
3663 l_cnh_rec
3664 );
3665 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3666 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3667 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3668 RAISE OKC_API.G_EXCEPTION_ERROR;
3669 END IF;
3670 lock_row(
3671 p_init_msg_list,
3672 x_return_status,
3673 x_msg_count,
3674 x_msg_data,
3675 l_okc_condition_headers_tl_rec
3676 );
3677 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3678 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3679 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3680 RAISE OKC_API.G_EXCEPTION_ERROR;
3681 END IF;
3682 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3683 EXCEPTION
3684 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3685 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3686 (
3687 l_api_name,
3688 G_PKG_NAME,
3689 'OKC_API.G_RET_STS_ERROR',
3690 x_msg_count,
3691 x_msg_data,
3692 '_PVT'
3693 );
3694 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3695 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3696 (
3697 l_api_name,
3698 G_PKG_NAME,
3699 'OKC_API.G_RET_STS_UNEXP_ERROR',
3700 x_msg_count,
3701 x_msg_data,
3702 '_PVT'
3703 );
3704 WHEN OTHERS THEN
3705 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3706 (
3707 l_api_name,
3708 G_PKG_NAME,
3709 'OTHERS',
3710 x_msg_count,
3711 x_msg_data,
3712 '_PVT'
3713 );
3714 END lock_row;
3715 --------------------------------------
3716 -- PL/SQL TBL lock_row for:CNHV_TBL --
3717 --------------------------------------
3718 PROCEDURE lock_row(
3719 p_api_version IN NUMBER,
3720 p_init_msg_list IN VARCHAR2 ,
3721 x_return_status OUT NOCOPY VARCHAR2,
3722 x_msg_count OUT NOCOPY NUMBER,
3723 x_msg_data OUT NOCOPY VARCHAR2,
3724 p_cnhv_tbl IN cnhv_tbl_type) IS
3725
3726 l_api_version CONSTANT NUMBER := 1;
3727 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3728 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3729 i NUMBER := 0;
3730 BEGIN
3731 OKC_API.init_msg_list(p_init_msg_list);
3732 -- Make sure PL/SQL table has records in it before passing
3733 IF (p_cnhv_tbl.COUNT > 0) THEN
3734 i := p_cnhv_tbl.FIRST;
3735 LOOP
3736 lock_row (
3737 p_api_version => p_api_version,
3738 p_init_msg_list => OKC_API.G_FALSE,
3739 x_return_status => x_return_status,
3740 x_msg_count => x_msg_count,
3741 x_msg_data => x_msg_data,
3742 p_cnhv_rec => p_cnhv_tbl(i));
3743 EXIT WHEN (i = p_cnhv_tbl.LAST);
3744 i := p_cnhv_tbl.NEXT(i);
3745 END LOOP;
3746 END IF;
3747 EXCEPTION
3748 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3749 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3750 (
3751 l_api_name,
3752 G_PKG_NAME,
3753 'OKC_API.G_RET_STS_ERROR',
3754 x_msg_count,
3755 x_msg_data,
3756 '_PVT'
3757 );
3758 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3759 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3760 (
3761 l_api_name,
3762 G_PKG_NAME,
3763 'OKC_API.G_RET_STS_UNEXP_ERROR',
3764 x_msg_count,
3765 x_msg_data,
3766 '_PVT'
3767 );
3768 WHEN OTHERS THEN
3769 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3770 (
3771 l_api_name,
3772 G_PKG_NAME,
3773 'OTHERS',
3774 x_msg_count,
3775 x_msg_data,
3776 '_PVT'
3777 );
3778 END lock_row;
3779
3780 ---------------------------------------------------------------------------
3781 -- PROCEDURE update_row
3782 ---------------------------------------------------------------------------
3783 --------------------------------------------
3784 -- update_row for:OKC_CONDITION_HEADERS_B --
3785 --------------------------------------------
3786 PROCEDURE update_row(
3787 p_init_msg_list IN VARCHAR2 ,
3788 x_return_status OUT NOCOPY VARCHAR2,
3789 x_msg_count OUT NOCOPY NUMBER,
3790 x_msg_data OUT NOCOPY VARCHAR2,
3791 p_cnh_rec IN cnh_rec_type,
3792 x_cnh_rec OUT NOCOPY cnh_rec_type) IS
3793
3794 l_api_version CONSTANT NUMBER := 1;
3795 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3796 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3797 l_cnh_rec cnh_rec_type := p_cnh_rec;
3798 l_def_cnh_rec cnh_rec_type;
3799 l_row_notfound BOOLEAN := TRUE;
3800 ----------------------------------
3801 -- FUNCTION populate_new_record --
3802 ----------------------------------
3803 FUNCTION populate_new_record (
3804 p_cnh_rec IN cnh_rec_type,
3805 x_cnh_rec OUT NOCOPY cnh_rec_type
3806 ) RETURN VARCHAR2 IS
3807 l_cnh_rec cnh_rec_type;
3808 l_row_notfound BOOLEAN := TRUE;
3809 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3810 BEGIN
3811 x_cnh_rec := p_cnh_rec;
3812 -- Get current database values
3813 l_cnh_rec := get_rec(p_cnh_rec, l_row_notfound);
3814 IF (l_row_notfound) THEN
3815 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3816 END IF;
3817 IF (x_cnh_rec.id = OKC_API.G_MISS_NUM)
3818 THEN
3819 x_cnh_rec.id := l_cnh_rec.id;
3820 END IF;
3821 IF (x_cnh_rec.acn_id = OKC_API.G_MISS_NUM)
3822 THEN
3823 x_cnh_rec.acn_id := l_cnh_rec.acn_id;
3824 END IF;
3825 IF (x_cnh_rec.counter_group_id = OKC_API.G_MISS_NUM)
3826 THEN
3827 x_cnh_rec.counter_group_id := l_cnh_rec.counter_group_id;
3828 END IF;
3829 IF (x_cnh_rec.one_time_yn = OKC_API.G_MISS_CHAR)
3830 THEN
3831 x_cnh_rec.one_time_yn := l_cnh_rec.one_time_yn;
3832 END IF;
3833 IF (x_cnh_rec.before_after = OKC_API.G_MISS_CHAR)
3834 THEN
3835 x_cnh_rec.before_after := l_cnh_rec.before_after;
3836 END IF;
3837 IF (x_cnh_rec.cnh_variance = OKC_API.G_MISS_NUM)
3838 THEN
3839 x_cnh_rec.cnh_variance := l_cnh_rec.cnh_variance;
3840 END IF;
3841 IF (x_cnh_rec.condition_valid_yn = OKC_API.G_MISS_CHAR)
3842 THEN
3843 x_cnh_rec.condition_valid_yn := l_cnh_rec.condition_valid_yn;
3844 END IF;
3845 IF (x_cnh_rec.tracked_yn = OKC_API.G_MISS_CHAR)
3846 THEN
3847 x_cnh_rec.tracked_yn := l_cnh_rec.tracked_yn;
3848 END IF;
3849 IF (x_cnh_rec.date_active = OKC_API.G_MISS_DATE)
3850 THEN
3851 x_cnh_rec.date_active := l_cnh_rec.date_active;
3852 END IF;
3853 IF (x_cnh_rec.date_inactive = OKC_API.G_MISS_DATE)
3854 THEN
3855 x_cnh_rec.date_inactive := l_cnh_rec.date_inactive;
3856 END IF;
3857 IF (x_cnh_rec.cnh_type = OKC_API.G_MISS_CHAR)
3858 THEN
3859 x_cnh_rec.cnh_type := l_cnh_rec.cnh_type;
3860 END IF;
3861 IF (x_cnh_rec.template_yn = OKC_API.G_MISS_CHAR)
3862 THEN
3863 x_cnh_rec.template_yn := l_cnh_rec.template_yn;
3864 END IF;
3865 IF (x_cnh_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
3866 THEN
3867 x_cnh_rec.dnz_chr_id := l_cnh_rec.dnz_chr_id;
3868 END IF;
3869 IF (x_cnh_rec.object_id = OKC_API.G_MISS_NUM)
3870 THEN
3871 x_cnh_rec.object_id := l_cnh_rec.object_id;
3872 END IF;
3873 IF (x_cnh_rec.jtot_object_code = OKC_API.G_MISS_CHAR)
3874 THEN
3875 x_cnh_rec.jtot_object_code := l_cnh_rec.jtot_object_code;
3876 END IF;
3877 IF (x_cnh_rec.object_version_number = OKC_API.G_MISS_NUM)
3878 THEN
3879 x_cnh_rec.object_version_number := l_cnh_rec.object_version_number;
3880 END IF;
3881 IF (x_cnh_rec.task_owner_id = OKC_API.G_MISS_NUM)
3882 THEN
3883 x_cnh_rec.task_owner_id := l_cnh_rec.task_owner_id;
3884 END IF;
3885 IF (x_cnh_rec.created_by = OKC_API.G_MISS_NUM)
3886 THEN
3887 x_cnh_rec.created_by := l_cnh_rec.created_by;
3888 END IF;
3889 IF (x_cnh_rec.creation_date = OKC_API.G_MISS_DATE)
3890 THEN
3891 x_cnh_rec.creation_date := l_cnh_rec.creation_date;
3892 END IF;
3893 IF (x_cnh_rec.last_updated_by = OKC_API.G_MISS_NUM)
3894 THEN
3895 x_cnh_rec.last_updated_by := l_cnh_rec.last_updated_by;
3896 END IF;
3897 IF (x_cnh_rec.last_update_date = OKC_API.G_MISS_DATE)
3898 THEN
3899 x_cnh_rec.last_update_date := l_cnh_rec.last_update_date;
3900 END IF;
3901 IF (x_cnh_rec.last_update_login = OKC_API.G_MISS_NUM)
3902 THEN
3903 x_cnh_rec.last_update_login := l_cnh_rec.last_update_login;
3904 END IF;
3905 IF (x_cnh_rec.attribute_category = OKC_API.G_MISS_CHAR)
3906 THEN
3907 x_cnh_rec.attribute_category := l_cnh_rec.attribute_category;
3908 END IF;
3909 IF (x_cnh_rec.attribute1 = OKC_API.G_MISS_CHAR)
3910 THEN
3911 x_cnh_rec.attribute1 := l_cnh_rec.attribute1;
3912 END IF;
3913 IF (x_cnh_rec.attribute2 = OKC_API.G_MISS_CHAR)
3914 THEN
3915 x_cnh_rec.attribute2 := l_cnh_rec.attribute2;
3916 END IF;
3917 IF (x_cnh_rec.attribute3 = OKC_API.G_MISS_CHAR)
3918 THEN
3919 x_cnh_rec.attribute3 := l_cnh_rec.attribute3;
3920 END IF;
3921 IF (x_cnh_rec.attribute4 = OKC_API.G_MISS_CHAR)
3922 THEN
3923 x_cnh_rec.attribute4 := l_cnh_rec.attribute4;
3924 END IF;
3925 IF (x_cnh_rec.attribute5 = OKC_API.G_MISS_CHAR)
3926 THEN
3927 x_cnh_rec.attribute5 := l_cnh_rec.attribute5;
3928 END IF;
3929 IF (x_cnh_rec.attribute6 = OKC_API.G_MISS_CHAR)
3930 THEN
3931 x_cnh_rec.attribute6 := l_cnh_rec.attribute6;
3932 END IF;
3933 IF (x_cnh_rec.attribute7 = OKC_API.G_MISS_CHAR)
3934 THEN
3935 x_cnh_rec.attribute7 := l_cnh_rec.attribute7;
3936 END IF;
3937 IF (x_cnh_rec.attribute8 = OKC_API.G_MISS_CHAR)
3938 THEN
3939 x_cnh_rec.attribute8 := l_cnh_rec.attribute8;
3940 END IF;
3941 IF (x_cnh_rec.attribute9 = OKC_API.G_MISS_CHAR)
3942 THEN
3943 x_cnh_rec.attribute9 := l_cnh_rec.attribute9;
3944 END IF;
3945 IF (x_cnh_rec.attribute10 = OKC_API.G_MISS_CHAR)
3946 THEN
3947 x_cnh_rec.attribute10 := l_cnh_rec.attribute10;
3948 END IF;
3949 IF (x_cnh_rec.attribute11 = OKC_API.G_MISS_CHAR)
3950 THEN
3951 x_cnh_rec.attribute11 := l_cnh_rec.attribute11;
3952 END IF;
3953 IF (x_cnh_rec.attribute12 = OKC_API.G_MISS_CHAR)
3954 THEN
3955 x_cnh_rec.attribute12 := l_cnh_rec.attribute12;
3956 END IF;
3957 IF (x_cnh_rec.attribute13 = OKC_API.G_MISS_CHAR)
3958 THEN
3959 x_cnh_rec.attribute13 := l_cnh_rec.attribute13;
3960 END IF;
3961 IF (x_cnh_rec.attribute14 = OKC_API.G_MISS_CHAR)
3962 THEN
3963 x_cnh_rec.attribute14 := l_cnh_rec.attribute14;
3964 END IF;
3965 IF (x_cnh_rec.attribute15 = OKC_API.G_MISS_CHAR)
3966 THEN
3967 x_cnh_rec.attribute15 := l_cnh_rec.attribute15;
3968 END IF;
3969 IF (x_cnh_rec.application_id = OKC_API.G_MISS_NUM)
3970 THEN
3971 x_cnh_rec.application_id := l_cnh_rec.application_id;
3972 END IF;
3973 IF (x_cnh_rec.seeded_flag = OKC_API.G_MISS_CHAR)
3974 THEN
3975 x_cnh_rec.seeded_flag := l_cnh_rec.seeded_flag;
3976 END IF;
3977 IF (x_cnh_rec.last_rundate = OKC_API.G_MISS_DATE)
3978 THEN
3979 x_cnh_rec.last_rundate := l_cnh_rec.last_rundate;
3980 END IF;
3981 RETURN(l_return_status);
3982 END populate_new_record;
3983 ------------------------------------------------
3984 -- Set_Attributes for:OKC_CONDITION_HEADERS_B --
3985 ------------------------------------------------
3986 FUNCTION Set_Attributes (
3987 p_cnh_rec IN cnh_rec_type,
3988 x_cnh_rec OUT NOCOPY cnh_rec_type
3989 ) RETURN VARCHAR2 IS
3990 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3991 BEGIN
3992 x_cnh_rec := p_cnh_rec;
3993 RETURN(l_return_status);
3994 END Set_Attributes;
3995 BEGIN
3996 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3997 p_init_msg_list,
3998 '_PVT',
3999 x_return_status);
4000 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4001 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4002 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4003 RAISE OKC_API.G_EXCEPTION_ERROR;
4004 END IF;
4005 --- Setting item attributes
4006 l_return_status := Set_Attributes(
4007 p_cnh_rec, -- IN
4008 l_cnh_rec); -- OUT
4009 --- If any errors happen abort API
4010 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4011 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4012 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4013 RAISE OKC_API.G_EXCEPTION_ERROR;
4014 END IF;
4015 l_return_status := populate_new_record(l_cnh_rec, l_def_cnh_rec);
4016 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4017 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4018 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4019 RAISE OKC_API.G_EXCEPTION_ERROR;
4020 END IF;
4021 UPDATE OKC_CONDITION_HEADERS_B
4022 SET ACN_ID = l_def_cnh_rec.acn_id,
4023 COUNTER_GROUP_ID = l_def_cnh_rec.counter_group_id,
4024 ONE_TIME_YN = l_def_cnh_rec.one_time_yn,
4025 BEFORE_AFTER = l_def_cnh_rec.before_after,
4026 CNH_VARIANCE = l_def_cnh_rec.cnh_variance,
4027 CONDITION_VALID_YN = l_def_cnh_rec.condition_valid_yn,
4028 TRACKED_YN = l_def_cnh_rec.tracked_yn,
4029 DATE_ACTIVE = l_def_cnh_rec.date_active,
4030 DATE_INACTIVE = l_def_cnh_rec.date_inactive,
4031 CNH_TYPE = l_def_cnh_rec.cnh_type,
4032 TEMPLATE_YN = l_def_cnh_rec.template_yn,
4033 DNZ_CHR_ID = l_def_cnh_rec.dnz_chr_id,
4034 OBJECT_ID = l_def_cnh_rec.object_id,
4035 JTOT_OBJECT_CODE = l_def_cnh_rec.jtot_object_code,
4036 OBJECT_VERSION_NUMBER = l_def_cnh_rec.object_version_number,
4037 TASK_OWNER_ID = l_def_cnh_rec.task_owner_id,
4038 CREATED_BY = l_def_cnh_rec.created_by,
4039 CREATION_DATE = l_def_cnh_rec.creation_date,
4040 LAST_UPDATED_BY = l_def_cnh_rec.last_updated_by,
4041 LAST_UPDATE_DATE = l_def_cnh_rec.last_update_date,
4042 LAST_UPDATE_LOGIN = l_def_cnh_rec.last_update_login,
4043 ATTRIBUTE_CATEGORY = l_def_cnh_rec.attribute_category,
4044 ATTRIBUTE1 = l_def_cnh_rec.attribute1,
4045 ATTRIBUTE2 = l_def_cnh_rec.attribute2,
4046 ATTRIBUTE3 = l_def_cnh_rec.attribute3,
4047 ATTRIBUTE4 = l_def_cnh_rec.attribute4,
4048 ATTRIBUTE5 = l_def_cnh_rec.attribute5,
4049 ATTRIBUTE6 = l_def_cnh_rec.attribute6,
4050 ATTRIBUTE7 = l_def_cnh_rec.attribute7,
4051 ATTRIBUTE8 = l_def_cnh_rec.attribute8,
4052 ATTRIBUTE9 = l_def_cnh_rec.attribute9,
4053 ATTRIBUTE10 = l_def_cnh_rec.attribute10,
4054 ATTRIBUTE11 = l_def_cnh_rec.attribute11,
4055 ATTRIBUTE12 = l_def_cnh_rec.attribute12,
4056 ATTRIBUTE13 = l_def_cnh_rec.attribute13,
4057 ATTRIBUTE14 = l_def_cnh_rec.attribute14,
4058 ATTRIBUTE15 = l_def_cnh_rec.attribute15,
4059 APPLICATION_ID = l_def_cnh_rec.application_id,
4060 SEEDED_FLAG = l_def_cnh_rec.seeded_flag,
4061 LAST_RUNDATE = l_def_cnh_rec.last_rundate
4062 WHERE ID = l_def_cnh_rec.id;
4063
4064 x_cnh_rec := l_def_cnh_rec;
4065 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4066 EXCEPTION
4067 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4068 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4069 (
4070 l_api_name,
4071 G_PKG_NAME,
4072 'OKC_API.G_RET_STS_ERROR',
4073 x_msg_count,
4074 x_msg_data,
4075 '_PVT'
4076 );
4077 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4078 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4079 (
4080 l_api_name,
4081 G_PKG_NAME,
4082 'OKC_API.G_RET_STS_UNEXP_ERROR',
4083 x_msg_count,
4084 x_msg_data,
4085 '_PVT'
4086 );
4087 WHEN OTHERS THEN
4088 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4089 (
4090 l_api_name,
4091 G_PKG_NAME,
4092 'OTHERS',
4093 x_msg_count,
4094 x_msg_data,
4095 '_PVT'
4096 );
4097 END update_row;
4098 ---------------------------------------------
4099 -- update_row for:OKC_CONDITION_HEADERS_TL --
4100 ---------------------------------------------
4101 PROCEDURE update_row(
4102 p_init_msg_list IN VARCHAR2 ,
4103 x_return_status OUT NOCOPY VARCHAR2,
4104 x_msg_count OUT NOCOPY NUMBER,
4105 x_msg_data OUT NOCOPY VARCHAR2,
4106 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
4107 x_okc_condition_headers_tl_rec OUT NOCOPY OkcConditionHeadersTlRecType) IS
4108
4109 l_api_version CONSTANT NUMBER := 1;
4110 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4111 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4112 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType := p_okc_condition_headers_tl_rec;
4113 ldefokcconditionheaderstlrec OkcConditionHeadersTlRecType;
4114 l_row_notfound BOOLEAN := TRUE;
4115 ----------------------------------
4116 -- FUNCTION populate_new_record --
4117 ----------------------------------
4118 FUNCTION populate_new_record (
4119 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
4120 x_okc_condition_headers_tl_rec OUT NOCOPY OkcConditionHeadersTlRecType
4121 ) RETURN VARCHAR2 IS
4122 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
4123 l_row_notfound BOOLEAN := TRUE;
4124 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4125 BEGIN
4126 x_okc_condition_headers_tl_rec := p_okc_condition_headers_tl_rec;
4127 -- Get current database values
4128 l_okc_condition_headers_tl_rec := get_rec(p_okc_condition_headers_tl_rec, l_row_notfound);
4129 IF (l_row_notfound) THEN
4130 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4131 END IF;
4132 IF (x_okc_condition_headers_tl_rec.id = OKC_API.G_MISS_NUM)
4133 THEN
4134 x_okc_condition_headers_tl_rec.id := l_okc_condition_headers_tl_rec.id;
4135 END IF;
4136 IF (x_okc_condition_headers_tl_rec.language = OKC_API.G_MISS_CHAR)
4137 THEN
4138 x_okc_condition_headers_tl_rec.language := l_okc_condition_headers_tl_rec.language;
4139 END IF;
4140 IF (x_okc_condition_headers_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
4141 THEN
4142 x_okc_condition_headers_tl_rec.source_lang := l_okc_condition_headers_tl_rec.source_lang;
4143 END IF;
4144 IF (x_okc_condition_headers_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4145 THEN
4146 x_okc_condition_headers_tl_rec.sfwt_flag := l_okc_condition_headers_tl_rec.sfwt_flag;
4147 END IF;
4148 IF (x_okc_condition_headers_tl_rec.name = OKC_API.G_MISS_CHAR)
4149 THEN
4150 x_okc_condition_headers_tl_rec.name := l_okc_condition_headers_tl_rec.name;
4151 END IF;
4152 IF (x_okc_condition_headers_tl_rec.description = OKC_API.G_MISS_CHAR)
4153 THEN
4154 x_okc_condition_headers_tl_rec.description := l_okc_condition_headers_tl_rec.description;
4155 END IF;
4156 IF (x_okc_condition_headers_tl_rec.short_description = OKC_API.G_MISS_CHAR)
4157 THEN
4158 x_okc_condition_headers_tl_rec.short_description := l_okc_condition_headers_tl_rec.short_description;
4159 END IF;
4160 IF (x_okc_condition_headers_tl_rec.comments = OKC_API.G_MISS_CHAR)
4161 THEN
4162 x_okc_condition_headers_tl_rec.comments := l_okc_condition_headers_tl_rec.comments;
4163 END IF;
4164 IF (x_okc_condition_headers_tl_rec.created_by = OKC_API.G_MISS_NUM)
4165 THEN
4166 x_okc_condition_headers_tl_rec.created_by := l_okc_condition_headers_tl_rec.created_by;
4167 END IF;
4168 IF (x_okc_condition_headers_tl_rec.creation_date = OKC_API.G_MISS_DATE)
4169 THEN
4170 x_okc_condition_headers_tl_rec.creation_date := l_okc_condition_headers_tl_rec.creation_date;
4171 END IF;
4172 IF (x_okc_condition_headers_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
4173 THEN
4174 x_okc_condition_headers_tl_rec.last_updated_by := l_okc_condition_headers_tl_rec.last_updated_by;
4175 END IF;
4176 IF (x_okc_condition_headers_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
4177 THEN
4178 x_okc_condition_headers_tl_rec.last_update_date := l_okc_condition_headers_tl_rec.last_update_date;
4179 END IF;
4180 IF (x_okc_condition_headers_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
4181 THEN
4182 x_okc_condition_headers_tl_rec.last_update_login := l_okc_condition_headers_tl_rec.last_update_login;
4183 END IF;
4184 RETURN(l_return_status);
4185 END populate_new_record;
4186 -------------------------------------------------
4187 -- Set_Attributes for:OKC_CONDITION_HEADERS_TL --
4188 -------------------------------------------------
4189 FUNCTION Set_Attributes (
4190 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
4191 x_okc_condition_headers_tl_rec OUT NOCOPY OkcConditionHeadersTlRecType
4192 ) RETURN VARCHAR2 IS
4193 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4194 BEGIN
4195 x_okc_condition_headers_tl_rec := p_okc_condition_headers_tl_rec;
4196 x_okc_condition_headers_tl_rec.LANGUAGE := l_lang;
4197 x_okc_condition_headers_tl_rec.SOURCE_LANG := l_lang;
4198 RETURN(l_return_status);
4199 END Set_Attributes;
4200 BEGIN
4201 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4202 p_init_msg_list,
4203 '_PVT',
4204 x_return_status);
4205 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4206 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4207 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4208 RAISE OKC_API.G_EXCEPTION_ERROR;
4209 END IF;
4210 --- Setting item attributes
4211 l_return_status := Set_Attributes(
4212 p_okc_condition_headers_tl_rec, -- IN
4213 l_okc_condition_headers_tl_rec); -- OUT
4214 --- If any errors happen abort API
4215 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4216 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4217 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4218 RAISE OKC_API.G_EXCEPTION_ERROR;
4219 END IF;
4220 l_return_status := populate_new_record(l_okc_condition_headers_tl_rec, ldefokcconditionheaderstlrec);
4221 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4222 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4223 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4224 RAISE OKC_API.G_EXCEPTION_ERROR;
4225 END IF;
4226 UPDATE OKC_CONDITION_HEADERS_TL
4227 SET NAME = ldefokcconditionheaderstlrec.name,
4228 SOURCE_LANG = ldefokcconditionheaderstlrec.source_lang,
4229 DESCRIPTION = ldefokcconditionheaderstlrec.description,
4230 SHORT_DESCRIPTION = ldefokcconditionheaderstlrec.short_description,
4231 COMMENTS = ldefokcconditionheaderstlrec.comments,
4232 CREATED_BY = ldefokcconditionheaderstlrec.created_by,
4233 CREATION_DATE = ldefokcconditionheaderstlrec.creation_date,
4234 LAST_UPDATED_BY = ldefokcconditionheaderstlrec.last_updated_by,
4235 LAST_UPDATE_DATE = ldefokcconditionheaderstlrec.last_update_date,
4236 LAST_UPDATE_LOGIN = ldefokcconditionheaderstlrec.last_update_login
4237 WHERE ID = ldefokcconditionheaderstlrec.id
4238 AND USERENV('LANG') IN (SOURCE_LANG,LANGUAGE);
4239 --AND SOURCE_LANG = USERENV('LANG');
4240
4241 UPDATE OKC_CONDITION_HEADERS_TL
4242 SET SFWT_FLAG = 'Y'
4243 WHERE ID = ldefokcconditionheaderstlrec.id
4244 AND SOURCE_LANG <> USERENV('LANG');
4245
4246 x_okc_condition_headers_tl_rec := ldefokcconditionheaderstlrec;
4247 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4248 EXCEPTION
4249 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4250 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4251 (
4252 l_api_name,
4253 G_PKG_NAME,
4254 'OKC_API.G_RET_STS_ERROR',
4255 x_msg_count,
4256 x_msg_data,
4257 '_PVT'
4258 );
4259 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4260 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4261 (
4262 l_api_name,
4263 G_PKG_NAME,
4264 'OKC_API.G_RET_STS_UNEXP_ERROR',
4265 x_msg_count,
4266 x_msg_data,
4267 '_PVT'
4268 );
4269 WHEN OTHERS THEN
4270 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4271 (
4272 l_api_name,
4273 G_PKG_NAME,
4274 'OTHERS',
4275 x_msg_count,
4276 x_msg_data,
4277 '_PVT'
4278 );
4279 END update_row;
4280 --------------------------------------------
4281 -- update_row for:OKC_CONDITION_HEADERS_V --
4282 --------------------------------------------
4283 PROCEDURE update_row(
4284 p_api_version IN NUMBER,
4285 p_init_msg_list IN VARCHAR2 ,
4286 x_return_status OUT NOCOPY VARCHAR2,
4287 x_msg_count OUT NOCOPY NUMBER,
4288 x_msg_data OUT NOCOPY VARCHAR2,
4289 p_cnhv_rec IN cnhv_rec_type,
4290 x_cnhv_rec OUT NOCOPY cnhv_rec_type) IS
4291
4292 l_api_version CONSTANT NUMBER := 1;
4293 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4294 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4295 l_cnhv_rec cnhv_rec_type := p_cnhv_rec;
4296 l_def_cnhv_rec cnhv_rec_type;
4297 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
4298 LxOkcConditionHeadersTlRec OkcConditionHeadersTlRecType;
4299 l_cnh_rec cnh_rec_type;
4300 lx_cnh_rec cnh_rec_type;
4301 -------------------------------
4302 -- FUNCTION fill_who_columns --
4303 -------------------------------
4304 FUNCTION fill_who_columns (
4305 p_cnhv_rec IN cnhv_rec_type
4306 ) RETURN cnhv_rec_type IS
4307 l_cnhv_rec cnhv_rec_type := p_cnhv_rec;
4308 BEGIN
4309 l_cnhv_rec.LAST_UPDATE_DATE := SYSDATE;
4310 l_cnhv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4311 l_cnhv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4312 RETURN(l_cnhv_rec);
4313 END fill_who_columns;
4314 ----------------------------------
4315 -- FUNCTION populate_new_record --
4316 ----------------------------------
4317 FUNCTION populate_new_record (
4318 p_cnhv_rec IN cnhv_rec_type,
4319 x_cnhv_rec OUT NOCOPY cnhv_rec_type
4320 ) RETURN VARCHAR2 IS
4321 l_cnhv_rec cnhv_rec_type;
4322 l_row_notfound BOOLEAN := TRUE;
4323 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4324 BEGIN
4325 x_cnhv_rec := p_cnhv_rec;
4326 -- Get current database values
4327 l_cnhv_rec := get_rec(p_cnhv_rec, l_row_notfound);
4328 IF (l_row_notfound) THEN
4329 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4330 END IF;
4331 IF (x_cnhv_rec.id = OKC_API.G_MISS_NUM)
4332 THEN
4333 x_cnhv_rec.id := l_cnhv_rec.id;
4334 END IF;
4335 IF (x_cnhv_rec.object_version_number = OKC_API.G_MISS_NUM)
4336 THEN
4337 x_cnhv_rec.object_version_number := l_cnhv_rec.object_version_number;
4338 END IF;
4339 IF (x_cnhv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4340 THEN
4341 x_cnhv_rec.sfwt_flag := l_cnhv_rec.sfwt_flag;
4342 END IF;
4343 IF (x_cnhv_rec.acn_id = OKC_API.G_MISS_NUM)
4344 THEN
4345 x_cnhv_rec.acn_id := l_cnhv_rec.acn_id;
4346 END IF;
4347 IF (x_cnhv_rec.counter_group_id = OKC_API.G_MISS_NUM)
4348 THEN
4349 x_cnhv_rec.counter_group_id := l_cnhv_rec.counter_group_id;
4350 END IF;
4351 IF (x_cnhv_rec.description = OKC_API.G_MISS_CHAR)
4352 THEN
4353 x_cnhv_rec.description := l_cnhv_rec.description;
4354 END IF;
4355 IF (x_cnhv_rec.short_description = OKC_API.G_MISS_CHAR)
4356 THEN
4357 x_cnhv_rec.short_description := l_cnhv_rec.short_description;
4358 END IF;
4359 IF (x_cnhv_rec.comments = OKC_API.G_MISS_CHAR)
4360 THEN
4361 x_cnhv_rec.comments := l_cnhv_rec.comments;
4362 END IF;
4363 IF (x_cnhv_rec.one_time_yn = OKC_API.G_MISS_CHAR)
4364 THEN
4365 x_cnhv_rec.one_time_yn := l_cnhv_rec.one_time_yn;
4366 END IF;
4367 IF (x_cnhv_rec.name = OKC_API.G_MISS_CHAR)
4368 THEN
4369 x_cnhv_rec.name := l_cnhv_rec.name;
4370 END IF;
4371 IF (x_cnhv_rec.condition_valid_yn = OKC_API.G_MISS_CHAR)
4372 THEN
4373 x_cnhv_rec.condition_valid_yn := l_cnhv_rec.condition_valid_yn;
4374 END IF;
4375 IF (x_cnhv_rec.before_after = OKC_API.G_MISS_CHAR)
4376 THEN
4377 x_cnhv_rec.before_after := l_cnhv_rec.before_after;
4378 END IF;
4379 IF (x_cnhv_rec.tracked_yn = OKC_API.G_MISS_CHAR)
4380 THEN
4381 x_cnhv_rec.tracked_yn := l_cnhv_rec.tracked_yn;
4382 END IF;
4383 IF (x_cnhv_rec.cnh_variance = OKC_API.G_MISS_NUM)
4384 THEN
4385 x_cnhv_rec.cnh_variance := l_cnhv_rec.cnh_variance;
4386 END IF;
4387 IF (x_cnhv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
4388 THEN
4389 x_cnhv_rec.dnz_chr_id := l_cnhv_rec.dnz_chr_id;
4390 END IF;
4391 IF (x_cnhv_rec.template_yn = OKC_API.G_MISS_CHAR)
4392 THEN
4393 x_cnhv_rec.template_yn := l_cnhv_rec.template_yn;
4394 END IF;
4395 IF (x_cnhv_rec.date_active = OKC_API.G_MISS_DATE)
4396 THEN
4397 x_cnhv_rec.date_active := l_cnhv_rec.date_active;
4398 END IF;
4399 IF (x_cnhv_rec.object_id = OKC_API.G_MISS_NUM)
4400 THEN
4401 x_cnhv_rec.object_id := l_cnhv_rec.object_id;
4402 END IF;
4403 IF (x_cnhv_rec.date_inactive = OKC_API.G_MISS_DATE)
4404 THEN
4405 x_cnhv_rec.date_inactive := l_cnhv_rec.date_inactive;
4406 END IF;
4407 IF (x_cnhv_rec.jtot_object_code = OKC_API.G_MISS_CHAR)
4408 THEN
4409 x_cnhv_rec.jtot_object_code := l_cnhv_rec.jtot_object_code;
4410 END IF;
4411 IF (x_cnhv_rec.task_owner_id = OKC_API.G_MISS_NUM)
4412 THEN
4413 x_cnhv_rec.task_owner_id := l_cnhv_rec.task_owner_id;
4414 END IF;
4415 IF (x_cnhv_rec.cnh_type = OKC_API.G_MISS_CHAR)
4416 THEN
4417 x_cnhv_rec.cnh_type := l_cnhv_rec.cnh_type;
4418 END IF;
4419 IF (x_cnhv_rec.application_id = OKC_API.G_MISS_NUM)
4420 THEN
4421 x_cnhv_rec.application_id := l_cnhv_rec.application_id;
4422 END IF;
4423 IF (x_cnhv_rec.seeded_flag = OKC_API.G_MISS_CHAR)
4424 THEN
4425 x_cnhv_rec.seeded_flag := l_cnhv_rec.seeded_flag;
4426 END IF;
4427 IF (x_cnhv_rec.last_rundate = OKC_API.G_MISS_DATE)
4428 THEN
4429 x_cnhv_rec.last_rundate := l_cnhv_rec.last_rundate;
4430 END IF;
4431 IF (x_cnhv_rec.attribute_category = OKC_API.G_MISS_CHAR)
4432 THEN
4433 x_cnhv_rec.attribute_category := l_cnhv_rec.attribute_category;
4434 END IF;
4435 IF (x_cnhv_rec.attribute1 = OKC_API.G_MISS_CHAR)
4436 THEN
4437 x_cnhv_rec.attribute1 := l_cnhv_rec.attribute1;
4438 END IF;
4439 IF (x_cnhv_rec.attribute2 = OKC_API.G_MISS_CHAR)
4440 THEN
4441 x_cnhv_rec.attribute2 := l_cnhv_rec.attribute2;
4442 END IF;
4443 IF (x_cnhv_rec.attribute3 = OKC_API.G_MISS_CHAR)
4444 THEN
4445 x_cnhv_rec.attribute3 := l_cnhv_rec.attribute3;
4446 END IF;
4447 IF (x_cnhv_rec.attribute4 = OKC_API.G_MISS_CHAR)
4448 THEN
4449 x_cnhv_rec.attribute4 := l_cnhv_rec.attribute4;
4450 END IF;
4451 IF (x_cnhv_rec.attribute5 = OKC_API.G_MISS_CHAR)
4452 THEN
4453 x_cnhv_rec.attribute5 := l_cnhv_rec.attribute5;
4454 END IF;
4455 IF (x_cnhv_rec.attribute6 = OKC_API.G_MISS_CHAR)
4456 THEN
4457 x_cnhv_rec.attribute6 := l_cnhv_rec.attribute6;
4458 END IF;
4459 IF (x_cnhv_rec.attribute7 = OKC_API.G_MISS_CHAR)
4460 THEN
4461 x_cnhv_rec.attribute7 := l_cnhv_rec.attribute7;
4462 END IF;
4463 IF (x_cnhv_rec.attribute8 = OKC_API.G_MISS_CHAR)
4464 THEN
4465 x_cnhv_rec.attribute8 := l_cnhv_rec.attribute8;
4466 END IF;
4467 IF (x_cnhv_rec.attribute9 = OKC_API.G_MISS_CHAR)
4468 THEN
4469 x_cnhv_rec.attribute9 := l_cnhv_rec.attribute9;
4470 END IF;
4471 IF (x_cnhv_rec.attribute10 = OKC_API.G_MISS_CHAR)
4472 THEN
4473 x_cnhv_rec.attribute10 := l_cnhv_rec.attribute10;
4474 END IF;
4475 IF (x_cnhv_rec.attribute11 = OKC_API.G_MISS_CHAR)
4476 THEN
4477 x_cnhv_rec.attribute11 := l_cnhv_rec.attribute11;
4478 END IF;
4479 IF (x_cnhv_rec.attribute12 = OKC_API.G_MISS_CHAR)
4480 THEN
4481 x_cnhv_rec.attribute12 := l_cnhv_rec.attribute12;
4482 END IF;
4483 IF (x_cnhv_rec.attribute13 = OKC_API.G_MISS_CHAR)
4484 THEN
4485 x_cnhv_rec.attribute13 := l_cnhv_rec.attribute13;
4486 END IF;
4487 IF (x_cnhv_rec.attribute14 = OKC_API.G_MISS_CHAR)
4488 THEN
4489 x_cnhv_rec.attribute14 := l_cnhv_rec.attribute14;
4490 END IF;
4491 IF (x_cnhv_rec.attribute15 = OKC_API.G_MISS_CHAR)
4492 THEN
4493 x_cnhv_rec.attribute15 := l_cnhv_rec.attribute15;
4494 END IF;
4495 IF (x_cnhv_rec.created_by = OKC_API.G_MISS_NUM)
4496 THEN
4497 x_cnhv_rec.created_by := l_cnhv_rec.created_by;
4498 END IF;
4499 IF (x_cnhv_rec.creation_date = OKC_API.G_MISS_DATE)
4500 THEN
4501 x_cnhv_rec.creation_date := l_cnhv_rec.creation_date;
4502 END IF;
4503 IF (x_cnhv_rec.last_updated_by = OKC_API.G_MISS_NUM)
4504 THEN
4505 x_cnhv_rec.last_updated_by := l_cnhv_rec.last_updated_by;
4506 END IF;
4507 IF (x_cnhv_rec.last_update_date = OKC_API.G_MISS_DATE)
4508 THEN
4509 x_cnhv_rec.last_update_date := l_cnhv_rec.last_update_date;
4510 END IF;
4511 IF (x_cnhv_rec.last_update_login = OKC_API.G_MISS_NUM)
4512 THEN
4513 x_cnhv_rec.last_update_login := l_cnhv_rec.last_update_login;
4514 END IF;
4515 RETURN(l_return_status);
4516 END populate_new_record;
4517 ------------------------------------------------
4518 -- Set_Attributes for:OKC_CONDITION_HEADERS_V --
4519 ------------------------------------------------
4520 FUNCTION Set_Attributes (
4521 p_cnhv_rec IN cnhv_rec_type,
4522 x_cnhv_rec OUT NOCOPY cnhv_rec_type
4523 ) RETURN VARCHAR2 IS
4524 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4525 BEGIN
4526 x_cnhv_rec := p_cnhv_rec;
4527 x_cnhv_rec.OBJECT_VERSION_NUMBER := NVL(x_cnhv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
4528 RETURN(l_return_status);
4529 END Set_Attributes;
4530 BEGIN
4531 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4532 G_PKG_NAME,
4533 p_init_msg_list,
4534 l_api_version,
4535 p_api_version,
4536 '_PVT',
4537 x_return_status);
4538 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4540 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4541 RAISE OKC_API.G_EXCEPTION_ERROR;
4542 END IF;
4543 /*
4544 -- Seed data should not be updated
4545 IF l_cnhv_rec.last_updated_by <> 1 THEN
4546 IF l_cnhv_rec.seeded_flag = 'Y' THEN
4547 OKC_API.set_message(p_app_name => G_APP_NAME,
4548 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
4549 RAISE OKC_API.G_EXCEPTION_ERROR;
4550 END IF;
4551 END IF;*/
4552 --- Setting item attributes
4553 l_return_status := Set_Attributes(
4554 p_cnhv_rec, -- IN
4555 l_cnhv_rec); -- OUT
4556 --- If any errors happen abort API
4557 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4558 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4559 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4560 RAISE OKC_API.G_EXCEPTION_ERROR;
4561 END IF;
4562 l_return_status := populate_new_record(l_cnhv_rec, l_def_cnhv_rec);
4563 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4564 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4565 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4566 RAISE OKC_API.G_EXCEPTION_ERROR;
4567 END IF;
4568 l_def_cnhv_rec := fill_who_columns(l_def_cnhv_rec);
4569 --- Validate all non-missing attributes (Item Level Validation)
4570 l_return_status := Validate_Attributes(l_def_cnhv_rec);
4571 --- If any errors happen abort API
4572 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4573 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4574 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4575 RAISE OKC_API.G_EXCEPTION_ERROR;
4576 END IF;
4577 l_return_status := Validate_Record(l_def_cnhv_rec);
4578 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4579 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4580 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4581 RAISE OKC_API.G_EXCEPTION_ERROR;
4582 END IF;
4583
4584 /************ ADDED TO CHECK THE UNIQUENESS **********************/
4585
4586 -- Validate_Unique_Cnh_Record
4587 Validate_Unique_Cnh_Record(x_return_status,p_cnhv_rec);
4588 l_return_status := x_return_status;
4589 -- store the highest degree of error
4590 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4591 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4592 ELSIF
4593 (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4594 RAISE OKC_API.G_EXCEPTION_ERROR;
4595 END IF;
4596
4597 /********** ADDED TO CHECK THE UNIQUENESS *************************/
4598
4599 --------------------------------------
4600 -- Move VIEW record to "Child" records
4601 --------------------------------------
4602 migrate(l_def_cnhv_rec, l_okc_condition_headers_tl_rec);
4603 migrate(l_def_cnhv_rec, l_cnh_rec);
4604 --------------------------------------------
4605 -- Call the UPDATE_ROW for each child record
4606 --------------------------------------------
4607 update_row(
4608 p_init_msg_list,
4609 x_return_status,
4610 x_msg_count,
4611 x_msg_data,
4612 l_okc_condition_headers_tl_rec,
4613 LxOkcConditionHeadersTlRec
4614 );
4615 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4616 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4617 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4618 RAISE OKC_API.G_EXCEPTION_ERROR;
4619 END IF;
4620 migrate(LxOkcConditionHeadersTlRec, l_def_cnhv_rec);
4621 update_row(
4622 p_init_msg_list,
4623 x_return_status,
4624 x_msg_count,
4625 x_msg_data,
4626 l_cnh_rec,
4627 lx_cnh_rec
4628 );
4629 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4630 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4631 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4632 RAISE OKC_API.G_EXCEPTION_ERROR;
4633 END IF;
4634 migrate(lx_cnh_rec, l_def_cnhv_rec);
4635 x_cnhv_rec := l_def_cnhv_rec;
4636 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4637 EXCEPTION
4638 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4639 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4640 (
4641 l_api_name,
4642 G_PKG_NAME,
4643 'OKC_API.G_RET_STS_ERROR',
4644 x_msg_count,
4645 x_msg_data,
4646 '_PVT'
4647 );
4648 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4649 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4650 (
4651 l_api_name,
4652 G_PKG_NAME,
4653 'OKC_API.G_RET_STS_UNEXP_ERROR',
4654 x_msg_count,
4655 x_msg_data,
4656 '_PVT'
4657 );
4658 WHEN OTHERS THEN
4659 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4660 (
4661 l_api_name,
4662 G_PKG_NAME,
4663 'OTHERS',
4664 x_msg_count,
4665 x_msg_data,
4666 '_PVT'
4667 );
4668 END update_row;
4669 ----------------------------------------
4670 -- PL/SQL TBL update_row for:CNHV_TBL --
4671 ----------------------------------------
4672 PROCEDURE update_row(
4673 p_api_version IN NUMBER,
4674 p_init_msg_list IN VARCHAR2 ,
4675 x_return_status OUT NOCOPY VARCHAR2,
4676 x_msg_count OUT NOCOPY NUMBER,
4677 x_msg_data OUT NOCOPY VARCHAR2,
4678 p_cnhv_tbl IN cnhv_tbl_type,
4679 x_cnhv_tbl OUT NOCOPY cnhv_tbl_type) IS
4680
4681 l_api_version CONSTANT NUMBER := 1;
4682 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4683 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4684 i NUMBER := 0;
4685 BEGIN
4686 OKC_API.init_msg_list(p_init_msg_list);
4687 -- Make sure PL/SQL table has records in it before passing
4688 IF (p_cnhv_tbl.COUNT > 0) THEN
4689 i := p_cnhv_tbl.FIRST;
4690 LOOP
4691 update_row (
4692 p_api_version => p_api_version,
4693 p_init_msg_list => OKC_API.G_FALSE,
4694 x_return_status => x_return_status,
4695 x_msg_count => x_msg_count,
4696 x_msg_data => x_msg_data,
4697 p_cnhv_rec => p_cnhv_tbl(i),
4698 x_cnhv_rec => x_cnhv_tbl(i));
4699 EXIT WHEN (i = p_cnhv_tbl.LAST);
4700 i := p_cnhv_tbl.NEXT(i);
4701 END LOOP;
4702 END IF;
4703 EXCEPTION
4704 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4705 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4706 (
4707 l_api_name,
4708 G_PKG_NAME,
4709 'OKC_API.G_RET_STS_ERROR',
4710 x_msg_count,
4711 x_msg_data,
4712 '_PVT'
4713 );
4714 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4715 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4716 (
4717 l_api_name,
4718 G_PKG_NAME,
4719 'OKC_API.G_RET_STS_UNEXP_ERROR',
4720 x_msg_count,
4721 x_msg_data,
4722 '_PVT'
4723 );
4724 WHEN OTHERS THEN
4725 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4726 (
4727 l_api_name,
4728 G_PKG_NAME,
4729 'OTHERS',
4730 x_msg_count,
4731 x_msg_data,
4732 '_PVT'
4733 );
4734 END update_row;
4735
4736 ---------------------------------------------------------------------------
4737 -- PROCEDURE delete_row
4738 ---------------------------------------------------------------------------
4739 --------------------------------------------
4740 -- delete_row for:OKC_CONDITION_HEADERS_B --
4741 --------------------------------------------
4742 PROCEDURE delete_row(
4743 p_init_msg_list IN VARCHAR2 ,
4744 x_return_status OUT NOCOPY VARCHAR2,
4745 x_msg_count OUT NOCOPY NUMBER,
4746 x_msg_data OUT NOCOPY VARCHAR2,
4747 p_cnh_rec IN cnh_rec_type) IS
4748
4749 l_api_version CONSTANT NUMBER := 1;
4750 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
4751 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4752 l_cnh_rec cnh_rec_type:= p_cnh_rec;
4753 l_row_notfound BOOLEAN := TRUE;
4754 BEGIN
4755 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4756 p_init_msg_list,
4757 '_PVT',
4758 x_return_status);
4759 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4760 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4761 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4762 RAISE OKC_API.G_EXCEPTION_ERROR;
4763 END IF;
4764 DELETE FROM OKC_CONDITION_HEADERS_B
4765 WHERE ID = l_cnh_rec.id;
4766
4767 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4768 EXCEPTION
4769 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4770 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4771 (
4772 l_api_name,
4773 G_PKG_NAME,
4774 'OKC_API.G_RET_STS_ERROR',
4775 x_msg_count,
4776 x_msg_data,
4777 '_PVT'
4778 );
4779 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4780 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4781 (
4782 l_api_name,
4783 G_PKG_NAME,
4784 'OKC_API.G_RET_STS_UNEXP_ERROR',
4785 x_msg_count,
4786 x_msg_data,
4787 '_PVT'
4788 );
4789 WHEN OTHERS THEN
4790 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4791 (
4792 l_api_name,
4793 G_PKG_NAME,
4794 'OTHERS',
4795 x_msg_count,
4796 x_msg_data,
4797 '_PVT'
4798 );
4799 END delete_row;
4800 ---------------------------------------------
4801 -- delete_row for:OKC_CONDITION_HEADERS_TL --
4802 ---------------------------------------------
4803 PROCEDURE delete_row(
4804 p_init_msg_list IN VARCHAR2 ,
4805 x_return_status OUT NOCOPY VARCHAR2,
4806 x_msg_count OUT NOCOPY NUMBER,
4807 x_msg_data OUT NOCOPY VARCHAR2,
4808 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType) IS
4809
4810 l_api_version CONSTANT NUMBER := 1;
4811 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
4812 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4813 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType:= p_okc_condition_headers_tl_rec;
4814 l_row_notfound BOOLEAN := TRUE;
4815 -------------------------------------------------
4816 -- Set_Attributes for:OKC_CONDITION_HEADERS_TL --
4817 -------------------------------------------------
4818 FUNCTION Set_Attributes (
4819 p_okc_condition_headers_tl_rec IN OkcConditionHeadersTlRecType,
4820 x_okc_condition_headers_tl_rec OUT NOCOPY OkcConditionHeadersTlRecType
4821 ) RETURN VARCHAR2 IS
4822 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4823 BEGIN
4824 x_okc_condition_headers_tl_rec := p_okc_condition_headers_tl_rec;
4825 x_okc_condition_headers_tl_rec.LANGUAGE := l_lang;
4826 RETURN(l_return_status);
4827 END Set_Attributes;
4828 BEGIN
4829 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4830 p_init_msg_list,
4831 '_PVT',
4832 x_return_status);
4833 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4835 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4836 RAISE OKC_API.G_EXCEPTION_ERROR;
4837 END IF;
4838 --- Setting item attributes
4839 l_return_status := Set_Attributes(
4840 p_okc_condition_headers_tl_rec, -- IN
4841 l_okc_condition_headers_tl_rec); -- OUT
4842 --- If any errors happen abort API
4843 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4844 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4845 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4846 RAISE OKC_API.G_EXCEPTION_ERROR;
4847 END IF;
4848 DELETE FROM OKC_CONDITION_HEADERS_TL
4849 WHERE ID = l_okc_condition_headers_tl_rec.id;
4850
4851 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4852 EXCEPTION
4853 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4854 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4855 (
4856 l_api_name,
4857 G_PKG_NAME,
4858 'OKC_API.G_RET_STS_ERROR',
4859 x_msg_count,
4860 x_msg_data,
4861 '_PVT'
4862 );
4863 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4864 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4865 (
4866 l_api_name,
4867 G_PKG_NAME,
4868 'OKC_API.G_RET_STS_UNEXP_ERROR',
4869 x_msg_count,
4870 x_msg_data,
4871 '_PVT'
4872 );
4873 WHEN OTHERS THEN
4874 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4875 (
4876 l_api_name,
4877 G_PKG_NAME,
4878 'OTHERS',
4879 x_msg_count,
4880 x_msg_data,
4881 '_PVT'
4882 );
4883 END delete_row;
4884 --------------------------------------------
4885 -- delete_row for:OKC_CONDITION_HEADERS_V --
4886 --------------------------------------------
4887 PROCEDURE delete_row(
4888 p_api_version IN NUMBER,
4889 p_init_msg_list IN VARCHAR2 ,
4890 x_return_status OUT NOCOPY VARCHAR2,
4891 x_msg_count OUT NOCOPY NUMBER,
4892 x_msg_data OUT NOCOPY VARCHAR2,
4893 p_cnhv_rec IN cnhv_rec_type) IS
4894
4895 l_api_version CONSTANT NUMBER := 1;
4896 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
4897 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4898 l_cnhv_rec cnhv_rec_type := p_cnhv_rec;
4899 l_okc_condition_headers_tl_rec OkcConditionHeadersTlRecType;
4900 l_cnh_rec cnh_rec_type;
4901 BEGIN
4902 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4903 G_PKG_NAME,
4904 p_init_msg_list,
4905 l_api_version,
4906 p_api_version,
4907 '_PVT',
4908 x_return_status);
4909 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4910 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4911 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4912 RAISE OKC_API.G_EXCEPTION_ERROR;
4913 END IF;
4914 /*
4915 -- Seed data should not be deleted
4916 IF l_cnhv_rec.last_updated_by <> 1 THEN
4917 IF l_cnhv_rec.seeded_flag = 'Y' THEN
4918 OKC_API.set_message(p_app_name => G_APP_NAME,
4919 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
4920 RAISE OKC_API.G_EXCEPTION_ERROR;
4921 END IF;
4922 END IF;*/
4923 --------------------------------------
4924 -- Move VIEW record to "Child" records
4925 --------------------------------------
4926 migrate(l_cnhv_rec, l_okc_condition_headers_tl_rec);
4927 migrate(l_cnhv_rec, l_cnh_rec);
4928 --------------------------------------------
4929 -- Call the DELETE_ROW for each child record
4930 --------------------------------------------
4931 delete_row(
4932 p_init_msg_list,
4933 x_return_status,
4934 x_msg_count,
4935 x_msg_data,
4936 l_okc_condition_headers_tl_rec
4937 );
4938 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4939 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4940 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4941 RAISE OKC_API.G_EXCEPTION_ERROR;
4942 END IF;
4943 delete_row(
4944 p_init_msg_list,
4945 x_return_status,
4946 x_msg_count,
4947 x_msg_data,
4948 l_cnh_rec
4949 );
4950 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4951 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4952 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4953 RAISE OKC_API.G_EXCEPTION_ERROR;
4954 END IF;
4955 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4956 EXCEPTION
4957 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4958 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4959 (
4960 l_api_name,
4961 G_PKG_NAME,
4962 'OKC_API.G_RET_STS_ERROR',
4963 x_msg_count,
4964 x_msg_data,
4965 '_PVT'
4966 );
4967 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4968 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4969 (
4970 l_api_name,
4971 G_PKG_NAME,
4972 'OKC_API.G_RET_STS_UNEXP_ERROR',
4973 x_msg_count,
4974 x_msg_data,
4975 '_PVT'
4976 );
4977 WHEN OTHERS THEN
4978 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4979 (
4980 l_api_name,
4981 G_PKG_NAME,
4982 'OTHERS',
4983 x_msg_count,
4984 x_msg_data,
4985 '_PVT'
4986 );
4987 END delete_row;
4988 ----------------------------------------
4989 -- PL/SQL TBL delete_row for:CNHV_TBL --
4990 ----------------------------------------
4991 PROCEDURE delete_row(
4992 p_api_version IN NUMBER,
4993 p_init_msg_list IN VARCHAR2 ,
4994 x_return_status OUT NOCOPY VARCHAR2,
4995 x_msg_count OUT NOCOPY NUMBER,
4996 x_msg_data OUT NOCOPY VARCHAR2,
4997 p_cnhv_tbl IN cnhv_tbl_type) IS
4998
4999 l_api_version CONSTANT NUMBER := 1;
5000 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5001 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5002 i NUMBER := 0;
5003 BEGIN
5004 OKC_API.init_msg_list(p_init_msg_list);
5005 -- Make sure PL/SQL table has records in it before passing
5006 IF (p_cnhv_tbl.COUNT > 0) THEN
5007 i := p_cnhv_tbl.FIRST;
5008 LOOP
5009 delete_row (
5010 p_api_version => p_api_version,
5011 p_init_msg_list => OKC_API.G_FALSE,
5012 x_return_status => x_return_status,
5013 x_msg_count => x_msg_count,
5014 x_msg_data => x_msg_data,
5015 p_cnhv_rec => p_cnhv_tbl(i));
5016 EXIT WHEN (i = p_cnhv_tbl.LAST);
5017 i := p_cnhv_tbl.NEXT(i);
5018 END LOOP;
5019 END IF;
5020 EXCEPTION
5021 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5022 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5023 (
5024 l_api_name,
5025 G_PKG_NAME,
5026 'OKC_API.G_RET_STS_ERROR',
5027 x_msg_count,
5028 x_msg_data,
5029 '_PVT'
5030 );
5031 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5032 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5033 (
5034 l_api_name,
5035 G_PKG_NAME,
5036 'OKC_API.G_RET_STS_UNEXP_ERROR',
5037 x_msg_count,
5038 x_msg_data,
5039 '_PVT'
5040 );
5041 WHEN OTHERS THEN
5042 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5043 (
5044 l_api_name,
5045 G_PKG_NAME,
5046 'OTHERS',
5047 x_msg_count,
5048 x_msg_data,
5049 '_PVT'
5050 );
5051 END delete_row;
5052
5053 ---------------------------------------------------------------
5054 -- Procedure for mass insert in OKC_CONDITION_HEADERS _B and TL tables
5055 ---------------------------------------------------------------
5056 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2, p_cnhv_tbl cnhv_tbl_type) IS
5057 l_tabsize NUMBER := p_cnhv_tbl.COUNT;
5058 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
5059
5060 in_id OKC_DATATYPES.NumberTabTyp;
5061 in_object_version_number OKC_DATATYPES.NumberTabTyp;
5062 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
5063 in_acn_id OKC_DATATYPES.NumberTabTyp;
5064 in_counter_group_id OKC_DATATYPES.NumberTabTyp;
5065 in_description OKC_DATATYPES.Var1995TabTyp;
5066 in_short_description OKC_DATATYPES.Var600TabTyp;
5067 in_comments OKC_DATATYPES.Var1995TabTyp;
5068 in_one_time_yn OKC_DATATYPES.Var3TabTyp;
5069 in_name OKC_DATATYPES.Var150TabTyp;
5070 in_condition_valid_yn OKC_DATATYPES.Var3TabTyp;
5071 in_before_after OKC_DATATYPES.Var3TabTyp;
5072 in_tracked_yn OKC_DATATYPES.Var3TabTyp;
5073 in_cnh_variance OKC_DATATYPES.NumberTabTyp;
5074 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
5075 in_template_yn OKC_DATATYPES.Var3TabTyp;
5076 in_date_active OKC_DATATYPES.DateTabTyp;
5077 in_object_id OKC_DATATYPES.NumberTabTyp;
5078 in_date_inactive OKC_DATATYPES.DateTabTyp;
5079 in_jtot_object_code OKC_DATATYPES.Var30TabTyp;
5080 in_task_owner_id OKC_DATATYPES.NumberTabTyp;
5081 in_cnh_type OKC_DATATYPES.Var30TabTyp;
5082 in_attribute_category OKC_DATATYPES.NumberTabTyp;
5083 in_application_id OKC_DATATYPES.Var3TabTyp;
5084 in_seeded_flag OKC_DATATYPES.Var90TabTyp;
5085 in_last_rundate OKC_DATATYPES.DateTabTyp;
5086 in_attribute1 OKC_DATATYPES.Var450TabTyp;
5087 in_attribute2 OKC_DATATYPES.Var450TabTyp;
5088 in_attribute3 OKC_DATATYPES.Var450TabTyp;
5089 in_attribute4 OKC_DATATYPES.Var450TabTyp;
5090 in_attribute5 OKC_DATATYPES.Var450TabTyp;
5091 in_attribute6 OKC_DATATYPES.Var450TabTyp;
5092 in_attribute7 OKC_DATATYPES.Var450TabTyp;
5093 in_attribute8 OKC_DATATYPES.Var450TabTyp;
5094 in_attribute9 OKC_DATATYPES.Var450TabTyp;
5095 in_attribute10 OKC_DATATYPES.Var450TabTyp;
5096 in_attribute11 OKC_DATATYPES.Var450TabTyp;
5097 in_attribute12 OKC_DATATYPES.Var450TabTyp;
5098 in_attribute13 OKC_DATATYPES.Var450TabTyp;
5099 in_attribute14 OKC_DATATYPES.Var450TabTyp;
5100 in_attribute15 OKC_DATATYPES.Var450TabTyp;
5101 in_created_by OKC_DATATYPES.NumberTabTyp;
5102 in_creation_date OKC_DATATYPES.DateTabTyp;
5103 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
5104 in_last_update_date OKC_DATATYPES.DateTabTyp;
5105 in_last_update_login OKC_DATATYPES.NumberTabTyp;
5106 j NUMBER := 0;
5107 i NUMBER := p_cnhv_tbl.FIRST;
5108 BEGIN
5109 -- Initializing return status
5110 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5111 while i is not null
5112 LOOP
5113 j := j + 1;
5114 in_id (j) := p_cnhv_tbl(i).id;
5115 in_object_version_number (j) := p_cnhv_tbl(i).object_version_number;
5116 in_sfwt_flag (j) := p_cnhv_tbl(i).sfwt_flag;
5117 in_acn_id (j) := p_cnhv_tbl(i).acn_id;
5118 in_counter_group_id (j) := p_cnhv_tbl(i).counter_group_id;
5119 in_description (j) := p_cnhv_tbl(i).description;
5120 in_short_description (j) := p_cnhv_tbl(i).short_description;
5121 in_comments (j) := p_cnhv_tbl(i).comments;
5122 in_one_time_yn (j) := p_cnhv_tbl(i).one_time_yn;
5123 in_name (j) := p_cnhv_tbl(i).name;
5124 in_condition_valid_yn (j) := p_cnhv_tbl(i).condition_valid_yn;
5125 in_before_after (j) := p_cnhv_tbl(i).before_after;
5126 in_tracked_yn (j) := p_cnhv_tbl(i).tracked_yn;
5127 in_cnh_variance (j) := p_cnhv_tbl(i).cnh_variance;
5128 in_dnz_chr_id (j) := p_cnhv_tbl(i).dnz_chr_id;
5129 in_template_yn (j) := p_cnhv_tbl(i).template_yn;
5130 in_date_active (j) := p_cnhv_tbl(i).date_active;
5131 in_object_id (j) := p_cnhv_tbl(i).object_id;
5132 in_date_inactive (j) := p_cnhv_tbl(i).date_inactive;
5133 in_jtot_object_code (j) := p_cnhv_tbl(i).jtot_object_code;
5134 in_task_owner_id (j) := p_cnhv_tbl(i).task_owner_id;
5135 in_cnh_type (j) := p_cnhv_tbl(i).cnh_type;
5136 in_attribute_category (j) := p_cnhv_tbl(i).attribute_category;
5137 in_application_id (j) := p_cnhv_tbl(i).application_id;
5138 in_seeded_flag (j) := p_cnhv_tbl(i).seeded_flag;
5139 in_last_rundate (j) := p_cnhv_tbl(i).last_rundate;
5140 in_attribute1 (j) := p_cnhv_tbl(i).attribute1;
5141 in_attribute2 (j) := p_cnhv_tbl(i).attribute2;
5142 in_attribute3 (j) := p_cnhv_tbl(i).attribute3;
5143 in_attribute4 (j) := p_cnhv_tbl(i).attribute4;
5144 in_attribute5 (j) := p_cnhv_tbl(i).attribute5;
5145 in_attribute6 (j) := p_cnhv_tbl(i).attribute6;
5146 in_attribute7 (j) := p_cnhv_tbl(i).attribute7;
5147 in_attribute8 (j) := p_cnhv_tbl(i).attribute8;
5148 in_attribute9 (j) := p_cnhv_tbl(i).attribute9;
5149 in_attribute10 (j) := p_cnhv_tbl(i).attribute10;
5150 in_attribute11 (j) := p_cnhv_tbl(i).attribute11;
5151 in_attribute12 (j) := p_cnhv_tbl(i).attribute12;
5152 in_attribute13 (j) := p_cnhv_tbl(i).attribute13;
5153 in_attribute14 (j) := p_cnhv_tbl(i).attribute14;
5154 in_attribute15 (j) := p_cnhv_tbl(i).attribute15;
5155 in_created_by (j) := p_cnhv_tbl(i).created_by;
5156 in_creation_date (j) := p_cnhv_tbl(i).creation_date;
5157 in_last_updated_by (j) := p_cnhv_tbl(i).last_updated_by;
5158 in_last_update_date (j) := p_cnhv_tbl(i).last_update_date;
5159 in_last_update_login (j) := p_cnhv_tbl(i).last_update_login;
5160 i := p_cnhv_tbl.NEXT(i);
5161 END LOOP;
5162
5163 FORALL i in 1..l_tabsize
5164 INSERT
5165 INTO OKC_CONDITION_HEADERS_B
5166 (
5167 id,
5168 acn_id,
5169 counter_group_id,
5170 one_time_yn,
5171 before_after,
5172 cnh_variance,
5173 condition_valid_yn,
5174 tracked_yn,
5175 date_active,
5176 date_inactive,
5177 cnh_type,
5178 template_yn,
5179 dnz_chr_id,
5180 object_id,
5181 jtot_object_code,
5182 object_version_number,
5183 created_by,
5184 creation_date,
5185 last_updated_by,
5186 last_update_date,
5187 last_update_login,
5188 attribute_category,
5189 attribute1,
5190 attribute2,
5191 attribute3,
5192 attribute4,
5193 attribute5,
5194 attribute6,
5195 attribute7,
5196 attribute8,
5197 attribute9,
5198 attribute10,
5199 attribute11,
5200 attribute12,
5201 attribute13,
5202 attribute14,
5203 attribute15,
5204 task_owner_id,
5205 application_id,
5206 seeded_flag,
5207 last_rundate
5208 )
5209 VALUES (
5210 in_id(i),
5211 in_acn_id(i),
5212 in_counter_group_id(i),
5213 in_one_time_yn(i),
5214 in_before_after(i),
5215 in_cnh_variance(i),
5216 in_condition_valid_yn(i),
5217 in_tracked_yn(i),
5218 in_date_active(i),
5219 in_date_inactive(i),
5220 in_cnh_type(i),
5221 in_template_yn(i),
5222 in_dnz_chr_id(i),
5223 in_object_id(i),
5224 in_jtot_object_code(i),
5225 in_object_version_number(i),
5226 in_created_by(i),
5227 in_creation_date(i),
5228 in_last_updated_by(i),
5229 in_last_update_date(i),
5230 in_last_update_login(i),
5231 in_attribute_category(i),
5232 in_attribute1(i),
5233 in_attribute2(i),
5234 in_attribute3(i),
5235 in_attribute4(i),
5236 in_attribute5(i),
5237 in_attribute6(i),
5238 in_attribute7(i),
5239 in_attribute8(i),
5240 in_attribute9(i),
5241 in_attribute10(i),
5242 in_attribute11(i),
5243 in_attribute12(i),
5244 in_attribute13(i),
5245 in_attribute14(i),
5246 in_attribute15(i),
5247 in_task_owner_id(i),
5248 in_application_id(i),
5249 in_seeded_flag(i),
5250 in_last_rundate(i)
5251 );
5252
5253 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
5254 FORALL i in 1..l_tabsize
5255 INSERT INTO OKC_CONDITION_HEADERS_TL(
5256 id,
5257 language,
5258 source_lang,
5259 sfwt_flag,
5260 description,
5261 short_description,
5262 comments,
5263 created_by,
5264 creation_date,
5265 last_updated_by,
5266 last_update_date,
5267 last_update_login,
5268 name
5269 )
5270 VALUES (
5271 in_id(i),
5272 OKC_UTIL.g_language_code(lang_i),
5273 l_source_lang,
5274 in_sfwt_flag(i),
5275 in_description(i),
5276 in_short_description(i),
5277 in_comments(i),
5278 in_created_by(i),
5279 in_creation_date(i),
5280 in_last_updated_by(i),
5281 in_last_update_date(i),
5282 in_last_update_login(i),
5283 in_name(i)
5284 );
5285 END LOOP;
5286 EXCEPTION
5287 WHEN OTHERS THEN
5288 --RAISE;
5289 -- store SQL error message on message stack
5290 OKC_API.SET_MESSAGE(
5291 p_app_name => G_APP_NAME,
5292 p_msg_name => G_UNEXPECTED_ERROR,
5293 p_token1 => G_SQLCODE_TOKEN,
5294 p_token1_value => SQLCODE,
5295 p_token2 => G_SQLERRM_TOKEN,
5296 p_token2_value => SQLERRM);
5297 -- notify caller of an error as UNEXPETED error
5298 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5299
5300 END INSERT_ROW_UPG;
5301
5302 --This function is called from versioning API OKC_VERSION_PVT
5303 --Old Location: OKCRVERB.pls
5304 --New Location: Base Table API
5305
5306 FUNCTION create_version(
5307 p_chr_id IN NUMBER,
5308 p_major_version IN NUMBER
5309 ) RETURN VARCHAR2 IS
5310
5311
5312 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
5313
5314 BEGIN
5315 INSERT INTO okc_condition_headers_bh
5316 (
5317 major_version,
5318 id,
5319 acn_id,
5320 counter_group_id,
5321 one_time_yn,
5322 before_after,
5323 cnh_variance,
5324 condition_valid_yn,
5325 tracked_yn,
5326 date_active,
5327 date_inactive,
5328 cnh_type,
5329 template_yn,
5330 dnz_chr_id,
5331 object_id,
5332 jtot_object_code,
5333 object_version_number,
5334 created_by,
5335 creation_date,
5336 last_updated_by,
5337 last_update_date,
5338 last_update_login,
5339 attribute_category,
5340 attribute1,
5341 attribute2,
5342 attribute3,
5343 attribute4,
5344 attribute5,
5345 attribute6,
5346 attribute7,
5347 attribute8,
5348 attribute9,
5349 attribute10,
5350 attribute11,
5351 attribute12,
5352 attribute13,
5353 attribute14,
5354 attribute15,
5355 task_owner_id,
5356 application_id, -- These 3 columns are now included in the HISTORY table
5357 seeded_flag, --
5358 last_rundate --
5359 )
5360 SELECT
5361 p_major_version,
5362 id,
5363 acn_id,
5364 counter_group_id,
5365 one_time_yn,
5366 before_after,
5367 cnh_variance,
5368 condition_valid_yn,
5369 tracked_yn,
5370 date_active,
5371 date_inactive,
5372 cnh_type,
5373 template_yn,
5374 dnz_chr_id,
5375 object_id,
5376 jtot_object_code,
5377 object_version_number,
5378 created_by,
5379 creation_date,
5380 last_updated_by,
5381 last_update_date,
5382 last_update_login,
5383 attribute_category,
5384 attribute1,
5385 attribute2,
5386 attribute3,
5387 attribute4,
5388 attribute5,
5389 attribute6,
5390 attribute7,
5391 attribute8,
5392 attribute9,
5393 attribute10,
5394 attribute11,
5395 attribute12,
5396 attribute13,
5397 attribute14,
5398 attribute15,
5399 task_owner_id,
5400 application_id,
5401 seeded_flag,
5402 last_rundate
5403 FROM okc_condition_headers_b
5404 WHERE dnz_chr_id = p_chr_id;
5405
5406 ------------------------------
5407 -- Versioning TL Table
5408 ------------------------------
5409
5410 INSERT INTO okc_condition_headers_tlh
5411 (
5412 major_version,
5413 id,
5414 language,
5415 source_lang,
5416 sfwt_flag,
5417 description,
5418 short_description,
5419 comments,
5420 created_by,
5421 creation_date,
5422 last_updated_by,
5423 last_update_date,
5424 last_update_login,
5425 name
5426 )
5427 SELECT
5428 p_major_version,
5429 id,
5430 language,
5431 source_lang,
5432 sfwt_flag,
5433 description,
5434 short_description,
5435 comments,
5436 created_by,
5437 creation_date,
5438 last_updated_by,
5439 last_update_date,
5440 last_update_login,
5441 name
5442 FROM okc_condition_headers_tl
5443 WHERE id in ( select id from okc_condition_headers_b
5444 where dnz_chr_id = p_chr_id );
5445
5446 RETURN l_return_status;
5447 EXCEPTION
5448 -- other appropriate handlers
5449 WHEN OTHERS THEN
5450 -- store SQL error message on message stack
5451 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
5452 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
5453 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
5454 p_token1_value => sqlcode,
5455 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
5456 p_token2_value => sqlerrm);
5457
5458 -- notify UNEXPECTED error
5459 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5460 return l_return_status;
5461 END create_version;
5462 --
5463 --This Function is called from Versioning API OKC_VERSION_PVT
5464 --Old Location:OKCRVERB.pls
5465 --New Location:Base Table API
5466
5467 FUNCTION restore_version(
5468 p_chr_id IN NUMBER,
5469 p_major_version IN NUMBER
5470 ) RETURN VARCHAR2 IS
5471
5472 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
5473
5474 BEGIN
5475 INSERT INTO okc_condition_headers_tl
5476 (
5477 id,
5478 language,
5479 source_lang,
5480 sfwt_flag,
5481 description,
5482 short_description,
5483 comments,
5484 created_by,
5485 creation_date,
5486 last_updated_by,
5487 last_update_date,
5488 last_update_login,
5489 name
5490 )
5491 SELECT
5492 id,
5493 language,
5494 source_lang,
5495 sfwt_flag,
5496 description,
5497 short_description,
5498 comments,
5499 created_by,
5500 creation_date,
5501 last_updated_by,
5502 last_update_date,
5503 last_update_login,
5504 name
5505 FROM okc_condition_headers_tlh
5506 WHERE id in (SELECT id
5507 FROM okc_condition_headers_bh
5508 WHERE dnz_chr_id = p_chr_id)
5509 AND major_version = p_major_version;
5510
5511 ------------------------------------------
5512 -- Restoring Base Table
5513 ------------------------------------------
5514
5515 INSERT INTO okc_condition_headers_b
5516 (
5517 id,
5518 acn_id,
5519 counter_group_id,
5520 one_time_yn,
5521 before_after,
5522 cnh_variance,
5523 condition_valid_yn,
5524 tracked_yn,
5525 date_active,
5526 date_inactive,
5527 cnh_type,
5528 template_yn,
5529 dnz_chr_id,
5530 object_id,
5531 jtot_object_code,
5532 object_version_number,
5533 created_by,
5534 creation_date,
5535 last_updated_by,
5536 last_update_date,
5537 last_update_login,
5538 attribute_category,
5539 attribute1,
5540 attribute2,
5541 attribute3,
5542 attribute4,
5543 attribute5,
5544 attribute6,
5545 attribute7,
5546 attribute8,
5547 attribute9,
5548 attribute10,
5549 attribute11,
5550 attribute12,
5551 attribute13,
5552 attribute14,
5553 attribute15,
5554 task_owner_id,
5555 application_id,
5556 seeded_flag,
5557 last_rundate
5558 )
5559 SELECT
5560 id,
5561 acn_id,
5562 counter_group_id,
5563 one_time_yn,
5564 before_after,
5565 cnh_variance,
5566 condition_valid_yn,
5567 tracked_yn,
5568 date_active,
5569 date_inactive,
5570 cnh_type,
5571 template_yn,
5572 dnz_chr_id,
5573 object_id,
5574 jtot_object_code,
5575 object_version_number,
5576 created_by,
5577 creation_date,
5578 last_updated_by,
5579 last_update_date,
5580 last_update_login,
5581 attribute_category,
5582 attribute1,
5583 attribute2,
5584 attribute3,
5585 attribute4,
5586 attribute5,
5587 attribute6,
5588 attribute7,
5589 attribute8,
5590 attribute9,
5591 attribute10,
5592 attribute11,
5593 attribute12,
5594 attribute13,
5595 attribute14,
5596 attribute15,
5597 task_owner_id,
5598 application_id,
5599 seeded_flag,
5600 last_rundate
5601 FROM okc_condition_headers_bh
5602 WHERE dnz_chr_id = p_chr_id
5603 AND major_version = p_major_version;
5604
5605 RETURN l_return_status;
5606 EXCEPTION
5607 -- other appropriate handlers
5608 WHEN OTHERS THEN
5609 -- store SQL error message on message stack
5610 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
5611 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
5612 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
5613 p_token1_value => sqlcode,
5614 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
5615 p_token2_value => sqlerrm);
5616
5617 -- notify UNEXPECTED error
5618 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5619 return l_return_status;
5620 END restore_version;
5621 --
5622 END OKC_CNH_PVT;