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