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