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