[Home] [Help]
PACKAGE BODY: APPS.OKC_OAT_PVT
Source
1 PACKAGE BODY OKC_OAT_PVT AS
2 /* $Header: OKCSOATB.pls 120.0.12010000.2 2008/10/24 08:04:08 ssreekum ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- FUNCTION get_seq_id
7 ---------------------------------------------------------------------------
8 FUNCTION get_seq_id RETURN NUMBER IS
9 BEGIN
10 RETURN(okc_p_util.raw_to_number(sys_guid()));
11 END get_seq_id;
12
13 ---------------------------------------------------------------------------
14 -- PROCEDURE qc
15 ---------------------------------------------------------------------------
16 PROCEDURE qc IS
17 BEGIN
18 null;
19 END qc;
20
21 ---------------------------------------------------------------------------
22 -- PROCEDURE change_version
23 ---------------------------------------------------------------------------
24 PROCEDURE change_version IS
25 BEGIN
26 null;
27 END change_version;
28
29 ---------------------------------------------------------------------------
30 -- PROCEDURE api_copy
31 ---------------------------------------------------------------------------
32 PROCEDURE api_copy IS
33 BEGIN
34 null;
35 END api_copy;
36
37 ---------------------------------------------------------------------------
38 -- FUNCTION get_rec for: OKC_OUTCOME_ARGUMENTS
39 ---------------------------------------------------------------------------
40 FUNCTION get_rec (
41 p_oat_rec IN oat_rec_type,
42 x_no_data_found OUT NOCOPY BOOLEAN
43 ) RETURN oat_rec_type IS
44 CURSOR okc_outcome_arguments_pk_csr (p_id IN NUMBER) IS
45 SELECT
46 ID,
47 OCE_ID,
48 PDP_ID,
49 AAE_ID,
50 DNZ_CHR_ID,
51 OBJECT_VERSION_NUMBER,
52 CREATED_BY,
53 CREATION_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_DATE,
56 VALUE,
57 LAST_UPDATE_LOGIN,
58 APPLICATION_ID,
59 SEEDED_FLAG
60 FROM Okc_Outcome_Arguments
61 WHERE okc_outcome_arguments.id = p_id;
62 l_okc_outcome_arguments_pk okc_outcome_arguments_pk_csr%ROWTYPE;
63 l_oat_rec oat_rec_type;
64 BEGIN
65 x_no_data_found := TRUE;
66 -- Get current database values
67 OPEN okc_outcome_arguments_pk_csr (p_oat_rec.id);
68 FETCH okc_outcome_arguments_pk_csr INTO
69 l_oat_rec.ID,
70 l_oat_rec.OCE_ID,
71 l_oat_rec.PDP_ID,
72 l_oat_rec.AAE_ID,
73 l_oat_rec.DNZ_CHR_ID,
74 l_oat_rec.OBJECT_VERSION_NUMBER,
75 l_oat_rec.CREATED_BY,
76 l_oat_rec.CREATION_DATE,
77 l_oat_rec.LAST_UPDATED_BY,
78 l_oat_rec.LAST_UPDATE_DATE,
79 l_oat_rec.VALUE,
80 l_oat_rec.LAST_UPDATE_LOGIN,
81 l_oat_rec.APPLICATION_ID,
82 l_oat_rec.SEEDED_FLAG;
83 x_no_data_found := okc_outcome_arguments_pk_csr%NOTFOUND;
84 CLOSE okc_outcome_arguments_pk_csr;
85 RETURN(l_oat_rec);
86 END get_rec;
87
88 FUNCTION get_rec (
89 p_oat_rec IN oat_rec_type
90 ) RETURN oat_rec_type IS
91 l_row_notfound BOOLEAN := TRUE;
92 BEGIN
93 RETURN(get_rec(p_oat_rec, l_row_notfound));
94 END get_rec;
95 ---------------------------------------------------------------------------
96 -- FUNCTION get_rec for: OKC_OUTCOME_ARGUMENTS_V
97 ---------------------------------------------------------------------------
98 FUNCTION get_rec (
99 p_oatv_rec IN oatv_rec_type,
100 x_no_data_found OUT NOCOPY BOOLEAN
101 ) RETURN oatv_rec_type IS
102 CURSOR okc_oatv_pk_csr (p_id IN NUMBER) IS
103 SELECT
104 ID,
105 PDP_ID,
106 OCE_ID,
107 AAE_ID,
108 DNZ_CHR_ID,
109 OBJECT_VERSION_NUMBER,
110 VALUE,
111 APPLICATION_ID,
112 SEEDED_FLAG,
113 CREATED_BY,
114 CREATION_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATE_LOGIN
118 FROM Okc_Outcome_Arguments_V
119 WHERE okc_outcome_arguments_v.id = p_id;
120 l_okc_oatv_pk okc_oatv_pk_csr%ROWTYPE;
121 l_oatv_rec oatv_rec_type;
122 BEGIN
123 x_no_data_found := TRUE;
124 -- Get current database values
125 OPEN okc_oatv_pk_csr (p_oatv_rec.id);
126 FETCH okc_oatv_pk_csr INTO
127 l_oatv_rec.ID,
128 l_oatv_rec.PDP_ID,
129 l_oatv_rec.OCE_ID,
130 l_oatv_rec.AAE_ID,
131 l_oatv_rec.DNZ_CHR_ID,
132 l_oatv_rec.OBJECT_VERSION_NUMBER,
133 l_oatv_rec.VALUE,
134 l_oatv_rec.APPLICATION_ID,
135 l_oatv_rec.SEEDED_FLAG,
136 l_oatv_rec.CREATED_BY,
137 l_oatv_rec.CREATION_DATE,
138 l_oatv_rec.LAST_UPDATED_BY,
139 l_oatv_rec.LAST_UPDATE_DATE,
140 l_oatv_rec.LAST_UPDATE_LOGIN;
141 x_no_data_found := okc_oatv_pk_csr%NOTFOUND;
142 CLOSE okc_oatv_pk_csr;
143 RETURN(l_oatv_rec);
144 END get_rec;
145
146 FUNCTION get_rec (
147 p_oatv_rec IN oatv_rec_type
148 ) RETURN oatv_rec_type IS
149 l_row_notfound BOOLEAN := TRUE;
150 BEGIN
151 RETURN(get_rec(p_oatv_rec, l_row_notfound));
152 END get_rec;
153
154 -------------------------------------------------------------
155 -- FUNCTION null_out_defaults for: OKC_OUTCOME_ARGUMENTS_V --
156 -------------------------------------------------------------
157 FUNCTION null_out_defaults (
158 p_oatv_rec IN oatv_rec_type
159 ) RETURN oatv_rec_type IS
160 l_oatv_rec oatv_rec_type := p_oatv_rec;
161 BEGIN
162 IF (l_oatv_rec.pdp_id = OKC_API.G_MISS_NUM) THEN
163 l_oatv_rec.pdp_id := NULL;
164 END IF;
165 IF (l_oatv_rec.oce_id = OKC_API.G_MISS_NUM) THEN
166 l_oatv_rec.oce_id := NULL;
167 END IF;
168 IF (l_oatv_rec.aae_id = OKC_API.G_MISS_NUM) THEN
169 l_oatv_rec.aae_id := NULL;
170 END IF;
171 IF (l_oatv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
172 l_oatv_rec.dnz_chr_id := NULL;
173 END IF;
174 IF (l_oatv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
175 l_oatv_rec.object_version_number := NULL;
176 END IF;
177 IF (l_oatv_rec.value = OKC_API.G_MISS_CHAR) THEN
178 l_oatv_rec.value := NULL;
179 END IF;
180 IF (l_oatv_rec.application_id = OKC_API.G_MISS_NUM) THEN
181 l_oatv_rec.application_id := NULL;
182 END IF;
183 IF (l_oatv_rec.seeded_flag = OKC_API.G_MISS_CHAR) THEN
184 l_oatv_rec.seeded_flag := NULL;
185 END IF;
186 IF (l_oatv_rec.created_by = OKC_API.G_MISS_NUM) THEN
187 l_oatv_rec.created_by := NULL;
188 END IF;
189 IF (l_oatv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
190 l_oatv_rec.creation_date := NULL;
191 END IF;
192 IF (l_oatv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
193 l_oatv_rec.last_updated_by := NULL;
194 END IF;
195 IF (l_oatv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
196 l_oatv_rec.last_update_date := NULL;
197 END IF;
198 IF (l_oatv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
199 l_oatv_rec.last_update_login := NULL;
200 END IF;
201 RETURN(l_oatv_rec);
202 END null_out_defaults;
203
204 /******* Commented out nocopy generated code in favor of hand written code *******
205 ---------------------------------------------------------------------------
206 -- PROCEDURE Validate_Attributes
207 ---------------------------------------------------------------------------
208 -----------------------------------------------------
209 -- Validate_Attributes for:OKC_OUTCOME_ARGUMENTS_V --
210 -----------------------------------------------------
211 FUNCTION Validate_Attributes (
212 p_oatv_rec IN oatv_rec_type
213 ) RETURN VARCHAR2 IS
214 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
215 BEGIN
216 IF p_oatv_rec.id = OKC_API.G_MISS_NUM OR
217 p_oatv_rec.id IS NULL
218 THEN
219 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
220 l_return_status := OKC_API.G_RET_STS_ERROR;
221 ELSIF p_oatv_rec.pdp_id = OKC_API.G_MISS_NUM OR
222 p_oatv_rec.pdp_id IS NULL
223 THEN
224 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'pdp_id');
225 l_return_status := OKC_API.G_RET_STS_ERROR;
226 ELSIF p_oatv_rec.oce_id = OKC_API.G_MISS_NUM OR
227 p_oatv_rec.oce_id IS NULL
228 THEN
229 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'oce_id');
230 l_return_status := OKC_API.G_RET_STS_ERROR;
231 ELSIF p_oatv_rec.object_version_number = OKC_API.G_MISS_NUM OR
232 p_oatv_rec.object_version_number IS NULL
233 THEN
234 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
235 l_return_status := OKC_API.G_RET_STS_ERROR;
236 END IF;
237 RETURN(l_return_status);
238 END Validate_Attributes;
239
240 ---------------------------------------------------------------------------
241 -- PROCEDURE Validate_Record
242 ---------------------------------------------------------------------------
243 -------------------------------------------------
244 -- Validate_Record for:OKC_OUTCOME_ARGUMENTS_V --
245 -------------------------------------------------
246 FUNCTION Validate_Record (
247 p_oatv_rec IN oatv_rec_type
248 ) RETURN VARCHAR2 IS
249 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
250 ------------------------------------
251 -- FUNCTION validate_foreign_keys --
252 ------------------------------------
253 FUNCTION validate_foreign_keys (
254 p_oatv_rec IN oatv_rec_type
255 ) RETURN VARCHAR2 IS
256 item_not_found_error EXCEPTION;
257 CURSOR okc_ocev_pk_csr (p_id IN NUMBER) IS
258 SELECT
259 ID,
260 OBJECT_VERSION_NUMBER,
261 SFWT_FLAG,
262 PDF_ID,
263 CNH_ID,
264 DNZ_CHR_ID,
265 ENABLED_YN,
266 COMMENTS,
267 APPLICATION_ID,
268 SEEDED_FLAG,
269 ATTRIBUTE_CATEGORY,
270 ATTRIBUTE1,
271 ATTRIBUTE2,
272 ATTRIBUTE3,
273 ATTRIBUTE4,
274 ATTRIBUTE5,
275 ATTRIBUTE6,
276 ATTRIBUTE7,
277 ATTRIBUTE8,
278 ATTRIBUTE9,
279 ATTRIBUTE10,
280 ATTRIBUTE11,
281 ATTRIBUTE12,
282 ATTRIBUTE13,
283 ATTRIBUTE14,
284 ATTRIBUTE15,
285 CREATED_BY,
286 CREATION_DATE,
287 LAST_UPDATED_BY,
288 LAST_UPDATE_DATE,
289 LAST_UPDATE_LOGIN
290 FROM Okc_Outcomes_V
291 WHERE okc_outcomes_v.id = p_id;
292 l_okc_ocev_pk okc_ocev_pk_csr%ROWTYPE;
293 CURSOR okc_pdpv_pk_csr (p_id IN NUMBER) IS
294 SELECT
295 ID,
296 OBJECT_VERSION_NUMBER,
297 SFWT_FLAG,
298 PDF_ID,
299 NAME,
300 DATA_TYPE,
301 DEFAULT_VALUE,
302 REQUIRED_YN,
303 DESCRIPTION,
304 APPLICATION_ID,
305 SEEDED_FLAG,
306 CREATED_BY,
307 CREATION_DATE,
308 LAST_UPDATED_BY,
309 LAST_UPDATE_DATE,
310 LAST_UPDATE_LOGIN
311 FROM Okc_Process_Def_Parameters_V
312 WHERE okc_process_def_parameters_v.id = p_id;
313 l_okc_pdpv_pk okc_pdpv_pk_csr%ROWTYPE;
314 CURSOR okc_aaev_pk_csr (p_id IN NUMBER) IS
315 SELECT
316 ID,
317 AAL_ID,
318 OBJECT_VERSION_NUMBER,
319 SFWT_FLAG,
320 ACN_ID,
321 ELEMENT_NAME,
322 NAME,
323 DESCRIPTION,
324 DATA_TYPE,
325 LIST_YN,
326 VISIBLE_YN,
327 DATE_OF_INTEREST_YN,
328 FORMAT_MASK,
329 MINIMUM_VALUE,
330 MAXIMUM_VALUE,
331 APPLICATION_ID,
332 SEEDED_FLAG,
333 ATTRIBUTE_CATEGORY,
334 ATTRIBUTE1,
335 ATTRIBUTE2,
336 ATTRIBUTE3,
337 ATTRIBUTE4,
338 ATTRIBUTE5,
339 ATTRIBUTE6,
340 ATTRIBUTE7,
341 ATTRIBUTE8,
342 ATTRIBUTE9,
343 ATTRIBUTE10,
344 ATTRIBUTE11,
345 ATTRIBUTE12,
346 ATTRIBUTE13,
347 ATTRIBUTE14,
348 ATTRIBUTE15,
349 CREATED_BY,
350 CREATION_DATE,
351 LAST_UPDATED_BY,
352 LAST_UPDATE_DATE,
353 LAST_UPDATE_LOGIN
354 FROM Okc_Action_Attributes_V
355 WHERE okc_action_attributes_v.id = p_id;
356 l_okc_aaev_pk okc_aaev_pk_csr%ROWTYPE;
357 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
358 l_row_notfound BOOLEAN := TRUE;
359 BEGIN
360 IF (p_oatv_rec.OCE_ID IS NOT NULL)
361 THEN
362 OPEN okc_ocev_pk_csr(p_oatv_rec.OCE_ID);
363 FETCH okc_ocev_pk_csr INTO l_okc_ocev_pk;
364 l_row_notfound := okc_ocev_pk_csr%NOTFOUND;
365 CLOSE okc_ocev_pk_csr;
366 IF (l_row_notfound) THEN
367 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'OCE_ID');
368 RAISE item_not_found_error;
369 END IF;
370 END IF;
371 IF (p_oatv_rec.PDP_ID IS NOT NULL)
372 THEN
373 OPEN okc_pdpv_pk_csr(p_oatv_rec.PDP_ID);
374 FETCH okc_pdpv_pk_csr INTO l_okc_pdpv_pk;
375 l_row_notfound := okc_pdpv_pk_csr%NOTFOUND;
376 CLOSE okc_pdpv_pk_csr;
377 IF (l_row_notfound) THEN
378 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PDP_ID');
379 RAISE item_not_found_error;
380 END IF;
381 END IF;
382 IF (p_oatv_rec.AAE_ID IS NOT NULL)
383 THEN
384 OPEN okc_aaev_pk_csr(p_oatv_rec.AAE_ID);
385 FETCH okc_aaev_pk_csr INTO l_okc_aaev_pk;
386 l_row_notfound := okc_aaev_pk_csr%NOTFOUND;
387 CLOSE okc_aaev_pk_csr;
388 IF (l_row_notfound) THEN
389 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'AAE_ID');
390 RAISE item_not_found_error;
391 END IF;
392 END IF;
393 RETURN (l_return_status);
394 EXCEPTION
395 WHEN item_not_found_error THEN
396 l_return_status := OKC_API.G_RET_STS_ERROR;
397 RETURN (l_return_status);
398 END validate_foreign_keys;
399 BEGIN
400 l_return_status := validate_foreign_keys (p_oatv_rec);
401 RETURN (l_return_status);
402 END Validate_Record;
403 /******* End Commented out nocopy generated code in favor of hand written code **/
404
405 /********* Begin Hand Written Code ***************************************/
406
407 ---------------------------------------------------------------------------
408 -- PROCEDURE Validate_Attributes
409 ---------------------------------------------------------------------------
410 -----------------------------------------------------
411 -- Validate_Attributes for:OKC_OUTCOME_ARGUMENTS_V --
412 -----------------------------------------------------
413 -- Start of comments
414 -- Procedure Name : validate_object_version_number
415 -- Description : Check if object_version_number is null
416 -- Business Rules :
417 -- Parameters :
418 -- Version : 1.0
419 -- End of comments
420
421 PROCEDURE validate_object_version_number(
422 x_return_status OUT NOCOPY VARCHAR2,
423 p_oatv_rec IN oatv_rec_type) IS
424
425 BEGIN
426 -- initialize return status
427 x_return_status := OKC_API.G_RET_STS_SUCCESS;
428
429 -- Check if object version number is null
430 IF p_oatv_rec.object_version_number = OKC_API.G_MISS_NUM OR
431 p_oatv_rec.object_version_number IS NULL THEN
432 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
433 p_msg_name => g_required_value,
434 p_token1 => g_col_name_token,
435 p_token1_value => 'object_version_number');
436 x_return_status := OKC_API.G_RET_STS_ERROR;
437 raise G_EXCEPTION_HALT_VALIDATION;
438 END IF;
439
440 EXCEPTION
441 when G_EXCEPTION_HALT_VALIDATION then
442 -- no processing necessary; validation can continue
443 -- with the next column
444 null;
445
446 when OTHERS then
447 -- store SQL error message on message stack for caller
448 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
449 p_msg_name => g_unexpected_error,
450 p_token1 => g_sqlcode_token,
451 p_token1_value => sqlcode,
452 p_token2 => g_sqlerrm_token,
453 p_token2_value => sqlerrm);
454 -- notify caller of an UNEXPECTED error
455 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
456
457 END validate_object_version_number;
458
459 -- Start of comments
460 -- Procedure Name : validate_seeded_flag
461 -- Description : Checks if column SEEDED_FLAG is 'Y' or 'N' only
462 -- Version : 1.0
463 -- End of comments
464
465 PROCEDURE validate_seeded_flag(
466 x_return_status OUT NOCOPY VARCHAR2,
467 p_oatv_rec IN oatv_rec_type) IS
468 l_y VARCHAR2(1) := 'Y';
469 l_n VARCHAR2(1) := 'N';
470 BEGIN
471 -- initialize return status
472 x_return_status := OKC_API.G_RET_STS_SUCCESS;
473 -- Check if seeded_flag is Y or N
474 IF UPPER(p_oatv_rec.seeded_flag) NOT IN ('Y', 'N') THEN
475 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
476 p_msg_name => g_invalid_value,
477 p_token1 => g_col_name_token,
478 p_token1_value => 'seeded_flag');
479 x_return_status := OKC_API.G_RET_STS_ERROR;
480 raise G_EXCEPTION_HALT_VALIDATION;
481 END IF;
482
483 --Check if the data is in upper case
484 IF p_oatv_rec.seeded_flag <> UPPER(p_oatv_rec.seeded_flag) THEN
485 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
486 p_msg_name => g_uppercase_required,
487 p_token1 => g_col_name_token,
488 p_token1_value => 'seeded_flag');
489 x_return_status := OKC_API.G_RET_STS_ERROR;
490 raise G_EXCEPTION_HALT_VALIDATION;
491 END IF;
492 EXCEPTION
493 when G_EXCEPTION_HALT_VALIDATION then
494 -- no processing necessary; validation can continue
495 -- with the next column
496 null;
497
498 when OTHERS then
499 -- store SQL error message on message stack for caller
500 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
501 p_msg_name => g_unexpected_error,
502 p_token1 => g_sqlcode_token,
503 p_token1_value => sqlcode,
504 p_token2 => g_sqlerrm_token,
505 p_token2_value => sqlerrm);
506 -- notify caller of an UNEXPECTED error
507 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
508 END validate_seeded_flag;
509
510 -- Start of comments
511 -- Procedure Name : validate_application_id
512 -- Description : Checks if application_id exists in fnd_application
513 -- Version : 1.0
514 -- End of comments
515
516 PROCEDURE validate_application_id(
517 x_return_status OUT NOCOPY VARCHAR2,
518 p_oatv_rec IN oatv_rec_type) IS
519 Cursor application_id_cur(p_application_id IN NUMBER) IS
520 select '1'
521 from fnd_application
522 where application_id = p_application_id;
523 l_dummy VARCHAR2(1) := '?';
524 BEGIN
525 -- initialize return status
526 x_return_status := OKC_API.G_RET_STS_SUCCESS;
527
528 IF p_oatv_rec.application_id IS NOT NULL THEN
529 --Check if application id exists in the fnd_application or not
530 OPEN application_id_cur(p_oatv_rec.application_id);
531 FETCH application_id_cur INTO l_dummy;
532 CLOSE application_id_cur ;
533 IF l_dummy = '?' THEN
534 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
535 p_msg_name => g_invalid_value,
536 p_token1 => g_col_name_token,
537 p_token1_value => 'application_id');
538 x_return_status := OKC_API.G_RET_STS_ERROR;
539 raise G_EXCEPTION_HALT_VALIDATION;
540 END IF;
541 END IF;
542 EXCEPTION
543 when G_EXCEPTION_HALT_VALIDATION then
544 -- no processing necessary; validation can continue
545 -- with the next column
546 null;
547
548 when OTHERS then
549 -- store SQL error message on message stack for caller
550 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
551 p_msg_name => g_unexpected_error,
552 p_token1 => g_sqlcode_token,
553 p_token1_value => sqlcode,
554 p_token2 => g_sqlerrm_token,
555 p_token2_value => sqlerrm);
556 -- notify caller of an UNEXPECTED error
557 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
558 END validate_application_id;
559
560 -- Start of comments
561 -- Procedure Name : validate_pdp_id
562 -- Description : Check if pdp_id is not null and enforce foreign key
563 -- Business Rules :
564 -- Parameters :
565 -- Version : 1.0
566 -- End of comments
567
568 PROCEDURE validate_pdp_id(
569 x_return_status OUT NOCOPY VARCHAR2,
570 p_oatv_rec IN oatv_rec_type) IS
571
572 CURSOR okc_pdpv_pk_csr IS
573 SELECT '1'
574 FROM Okc_process_def_parameters_v, okc_process_defs_v
575 WHERE okc_process_def_parameters_v.id = p_oatv_rec.pdp_id
576 and okc_process_def_parameters_v.pdf_id = okc_process_defs_v.id;
577
578 l_dummy VARCHAR2(1) := '?';
579 BEGIN
580 -- initialize return status
581 x_return_status := OKC_API.G_RET_STS_SUCCESS;
582
583 IF p_oatv_rec.pdp_id = OKC_API.G_MISS_NUM OR p_oatv_rec.pdp_id IS NULL THEN
584 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
585 p_msg_name => g_required_value,
586 p_token1 => g_col_name_token,
587 p_token1_value => 'pdp_id');
588 x_return_status := OKC_API.G_RET_STS_ERROR;
589 raise G_EXCEPTION_HALT_VALIDATION;
590 END IF;
591
592 -- Enforce foreign key
593 OPEN okc_pdpv_pk_csr;
594 FETCH okc_pdpv_pk_csr INTO l_dummy;
595 CLOSE okc_pdpv_pk_csr;
596
597 -- If l_dummy is still set to default, data was not found
598 IF (l_dummy = '?') THEN
599 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
600 p_msg_name => g_no_parent_record,
601 p_token1 => g_col_name_token,
602 p_token1_value => 'pdp_id',
603 p_token2 => g_child_table_token,
604 p_token2_value => 'OKC_PROCESS_DEF_PARAMETERS_V',
605 p_token3 => g_parent_table_token,
606 p_token3_value => 'OKC_OUTCOMES_V');
607 x_return_status := OKC_API.G_RET_STS_ERROR;
608 RAISE G_EXCEPTION_HALT_VALIDATION;
609 END IF;
610 EXCEPTION
611 when G_EXCEPTION_HALT_VALIDATION then
612 -- no processing necessary; validation can continue
613 -- with the next column
614 null;
615
616 -- verify that cursor was closed
617 if okc_pdpv_pk_csr %ISOPEN then
618 close okc_pdpv_pk_csr ;
619 end if;
620
621 when OTHERS then
622 -- store SQL error message on message stack for caller
623 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
624 p_msg_name => g_unexpected_error,
625 p_token1 => g_sqlcode_token,
626 p_token1_value => sqlcode,
627 p_token2 => g_sqlerrm_token,
628 p_token2_value => sqlerrm);
629 -- notify caller of an UNEXPECTED error
630 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
631 END validate_pdp_id;
632
633 -- Start of comments
634 -- Procedure Name : validate_oce_id
635 -- Description : Check if oce_id is null
636 -- Business Rules :
637 -- Parameters :
638 -- Version : 1.0
639 -- End of comments
640
641 PROCEDURE validate_oce_id(
642 x_return_status OUT NOCOPY VARCHAR2,
643 p_oatv_rec IN oatv_rec_type) IS
644
645 CURSOR okc_ocev_pk_csr IS
646 SELECT '1'
647 FROM Okc_outcomes_v
648 WHERE okc_outcomes_v.id = p_oatv_rec.oce_id;
649
650 l_dummy VARCHAR2(1) := '?';
651 BEGIN
652 -- initialize return status
653 x_return_status := OKC_API.G_RET_STS_SUCCESS;
654
655 -- Check if oce_id is null
656 IF p_oatv_rec.oce_id = OKC_API.G_MISS_NUM OR p_oatv_rec.oce_id IS NULL THEN
657 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
658 p_msg_name => g_required_value,
659 p_token1 => g_col_name_token,
660 p_token1_value => 'oce_id');
661 x_return_status := OKC_API.G_RET_STS_ERROR;
662 raise G_EXCEPTION_HALT_VALIDATION;
663 END IF;
664
665 -- Enforce foreign key
666 OPEN okc_ocev_pk_csr;
667 FETCH okc_ocev_pk_csr INTO l_dummy;
668 CLOSE okc_ocev_pk_csr;
669
670 -- If l_dummy is still set to default, data was not found
671 IF (l_dummy = '?') THEN
672 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
673 p_msg_name => g_no_parent_record,
674 p_token1 => g_col_name_token,
675 p_token1_value => 'oce_id',
676 p_token2 => g_child_table_token,
677 p_token2_value => 'OKC_OUTCOME_ARGUMENTS_V',
678 p_token3 => g_parent_table_token,
679 p_token3_value => 'OKC_OUTCOMES_V');
680 x_return_status := OKC_API.G_RET_STS_ERROR;
681 RAISE G_EXCEPTION_HALT_VALIDATION;
682 END IF;
683 EXCEPTION
684 when G_EXCEPTION_HALT_VALIDATION then
685 -- no processing necessary; validation can continue
686 -- with the next column
687 null;
688
689 -- verify that cursor was closed
690 if okc_ocev_pk_csr %ISOPEN then
691 close okc_ocev_pk_csr ;
692 end if;
693
694 when OTHERS then
695 -- store SQL error message on message stack for caller
696 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
697 p_msg_name => g_unexpected_error,
698 p_token1 => g_sqlcode_token,
699 p_token1_value => sqlcode,
700 p_token2 => g_sqlerrm_token,
701 p_token2_value => sqlerrm);
702 -- notify caller of an UNEXPECTED error
703 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
704 END validate_oce_id;
705
706 -- Start of comments
707 -- Procedure Name : validate_aae_id
708 -- Description : Check if aae_id is valid
709 -- Business Rules :
710 -- Parameters :
711 -- Version : 1.0
712 -- End of comments
713
714 PROCEDURE validate_aae_id(
715 x_return_status OUT NOCOPY VARCHAR2,
716 p_oatv_rec IN oatv_rec_type) IS
717
718 CURSOR okc_aaev_pk_csr IS
719 SELECT '1'
720 FROM Okc_action_attributes_v
721 WHERE okc_action_attributes_v.id = p_oatv_rec.aae_id;
722
723 l_dummy VARCHAR2(1) := '?';
724 Begin
725 IF p_oatv_rec.value IS NULL THEN
726 -- Enforce foreign key
727 OPEN okc_aaev_pk_csr;
728 FETCH okc_aaev_pk_csr INTO l_dummy;
729 CLOSE okc_aaev_pk_csr;
730
731 -- If l_dummy is still set to default, data was not found
732 IF (l_dummy = '?') THEN
733 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
734 p_msg_name => g_no_parent_record,
735 p_token1 => g_col_name_token,
736 p_token1_value => 'aae_id',
737 p_token2 => g_child_table_token,
738 p_token2_value => 'OKC_OUTCOME_ARGUMENTS_V',
739 p_token3 => g_parent_table_token,
740 p_token3_value => 'OKC_ACTION_ATTRIBUTES_V');
741 x_return_status := OKC_API.G_RET_STS_ERROR;
742 RAISE G_EXCEPTION_HALT_VALIDATION;
743 END IF;
744 END IF;
745 EXCEPTION
746 when G_EXCEPTION_HALT_VALIDATION then
747 -- no processing necessary; validation can continue
748 -- with the next column
749 null;
750
751 -- verify that cursor was closed
752 if okc_aaev_pk_csr %ISOPEN then
753 close okc_aaev_pk_csr ;
754 end if;
755
756 when OTHERS then
757 -- store SQL error message on message stack for caller
758 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
759 p_msg_name => g_unexpected_error,
760 p_token1 => g_sqlcode_token,
761 p_token1_value => sqlcode,
762 p_token2 => g_sqlerrm_token,
763 p_token2_value => sqlerrm);
764 -- notify caller of an UNEXPECTED error
765 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
766 END validate_aae_id;
767
768 FUNCTION Validate_Attributes (
769 p_oatv_rec IN oatv_rec_type
770 ) RETURN VARCHAR2 IS
771
772 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
773 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
774
775 BEGIN
776 validate_object_version_number(x_return_status => l_return_status
777 ,p_oatv_rec => p_oatv_rec);
778 -- store the highest degree of error
779 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
780 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
781 l_return_status := x_return_status;
782 RAISE G_EXCEPTION_HALT_VALIDATION;
783 ELSE
784 l_return_status := x_return_status; -- record that there was an error
785 END IF;
786 END IF;
787
788 validate_seeded_flag(x_return_status => l_return_status
789 ,p_oatv_rec => p_oatv_rec);
790 -- store the highest degree of error
791 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
792 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
793 l_return_status := x_return_status;
794 RAISE G_EXCEPTION_HALT_VALIDATION;
795 ELSE
796 l_return_status := x_return_status; -- record that there was an error
797 END IF;
798 END IF;
799
800 validate_application_id(x_return_status => l_return_status
801 ,p_oatv_rec => p_oatv_rec);
802 -- store the highest degree of error
803 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
804 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
805 l_return_status := x_return_status;
806 RAISE G_EXCEPTION_HALT_VALIDATION;
807 ELSE
808 l_return_status := x_return_status; -- record that there was an error
809 END IF;
810 END IF;
811
812 validate_pdp_id(x_return_status => l_return_status
813 ,p_oatv_rec => p_oatv_rec);
814 -- store the highest degree of error
815 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
816 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
817 l_return_status := x_return_status;
818 RAISE G_EXCEPTION_HALT_VALIDATION;
819 ELSE
820 l_return_status := x_return_status; -- record that there was an error
821 END IF;
822 END IF;
823
824 validate_oce_id(x_return_status => l_return_status
825 ,p_oatv_rec => p_oatv_rec);
826 -- store the highest degree of error
827 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
828 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
829 l_return_status := x_return_status;
830 RAISE G_EXCEPTION_HALT_VALIDATION;
831 ELSE
832 l_return_status := x_return_status; -- record that there was an error
833 END IF;
834 END IF;
835
836 validate_aae_id(x_return_status => l_return_status
837 ,p_oatv_rec => p_oatv_rec);
838 -- store the highest degree of error
839 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
840 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
841 l_return_status := x_return_status;
842 RAISE G_EXCEPTION_HALT_VALIDATION;
843 ELSE
844 l_return_status := x_return_status; -- record that there was an error
845 END IF;
846 END IF;
847 RETURN(l_return_status);
848 EXCEPTION
849 When G_EXCEPTION_HALT_VALIDATION then
850 --just come out with return status
851 null;
852 RETURN(l_return_status);
853
854 when OTHERS then
855 -- store SQL error message on message stack for caller
856 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
857 p_msg_name => g_unexpected_error,
858 p_token1 => g_sqlcode_token,
859 p_token1_value => sqlcode,
860 p_token2 => g_sqlerrm_token,
861 p_token2_value => sqlerrm);
862 -- notify caller of an UNEXPECTED error
863 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
864 RETURN(l_return_status);
865 END Validate_Attributes;
866
867 ---------------------------------------------------------------------------
868 -- PROCEDURE Validate_Record
869 ---------------------------------------------------------------------------
870 -------------------------------------------------
871 -- Validate_Record for:OKC_OUTCOME_ARGUMENTS_V --
872 -------------------------------------------------
873 -- Start of comments
874 -- Procedure Name : validate_aaeid_value
875 -- Description : Check if one of this aae_id or value has value
876 -- Business Rules :
877 -- Parameters :
878 -- Version : 1.0
879 -- End of comments
880
881 PROCEDURE validate_aaeid_value(
882 x_return_status OUT NOCOPY VARCHAR2,
883 p_oatv_rec IN oatv_rec_type) IS
884
885 BEGIN
886 -- initialize return status
887 x_return_status := OKC_API.G_RET_STS_SUCCESS;
888
889 -- Check if aae_id or value has value
890 IF p_oatv_rec.aae_id is not null and p_oatv_rec.value is not null then
891 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
892 p_msg_name => g_arc_violated,
893 p_token1 => g_col_name_token,
894 p_token1_value => 'aae_id',
895 p_token2 => g_col_name_token,
896 p_token2_value => 'value');
897 x_return_status := OKC_API.G_RET_STS_ERROR;
898 raise G_EXCEPTION_HALT_VALIDATION;
899 END IF;
900
901 EXCEPTION
902 when G_EXCEPTION_HALT_VALIDATION then
903 -- no processing necessary; validation can continue
904 -- with the next column
905 null;
906
907 when OTHERS then
908 -- store SQL error message on message stack for caller
909 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
910 p_msg_name => g_unexpected_error,
911 p_token1 => g_sqlcode_token,
912 p_token1_value => sqlcode,
913 p_token2 => g_sqlerrm_token,
914 p_token2_value => sqlerrm);
915 -- notify caller of an UNEXPECTED error
916 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
917 END validate_aaeid_value;
918
919 -- Start of comments
920 -- Procedure Name : validate_aaeid_datatype
921 -- Description : Check if aae_id is of the right datatype
922 -- Business Rules :
923 -- Parameters :
924 -- Version : 1.0
925 -- End of comments
926
927 PROCEDURE validate_aaeid_datatype(
928 x_return_status OUT NOCOPY VARCHAR2,
929 p_oatv_rec IN oatv_rec_type) IS
930
931 cursor pdp_cur(p_pdp_id IN NUMBER) is
932 select data_type
933 from okc_process_def_parameters_v
934 where id = p_pdp_id;
935
936 cursor aae_cur(p_aae_id IN NUMBER) is
937 select data_type
938 from okc_action_attributes_v
939 where id = p_aae_id;
940
941 v_aae_data_type VARCHAr2(90);
942 v_pdp_data_type VARCHAR2(90);
943
944 BEGIN
945 -- initialize return status
946 x_return_status := OKC_API.G_RET_STS_SUCCESS;
947
948 -- Check if aae_id is of the right datatype
949 IF p_oatv_rec.aae_id is not null and p_oatv_rec.pdp_id is not null then
950 OPEN pdp_cur(p_oatv_rec.pdp_id);
951 FETCH pdp_cur into v_pdp_data_type;
952 CLOSE pdp_cur;
953
954 OPEN aae_cur(p_oatv_rec.aae_id);
955 FETCH aae_cur into v_aae_data_type;
956 CLOSE aae_cur;
957
958 IF v_pdp_data_type <> v_aae_data_type THEN
959 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
960 p_msg_name => g_invalid_value,
961 p_token1 => g_col_name_token,
962 p_token1_value => 'aae_id',
963 p_token2 => g_col_name_token,
964 p_token2_value => 'pdp_id');
965 x_return_status := OKC_API.G_RET_STS_ERROR;
966 raise G_EXCEPTION_HALT_VALIDATION;
967 END IF;
968 END IF;
969
970 EXCEPTION
971 when G_EXCEPTION_HALT_VALIDATION then
972 -- no processing necessary; validation can continue
973 -- with the next column
974 null;
975
976 when OTHERS then
977 -- store SQL error message on message stack for caller
978 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
979 p_msg_name => g_unexpected_error,
980 p_token1 => g_sqlcode_token,
981 p_token1_value => sqlcode,
982 p_token2 => g_sqlerrm_token,
983 p_token2_value => sqlerrm);
984 -- notify caller of an UNEXPECTED error
985 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
986 END validate_aaeid_datatype;
987
988 -- Start of comments
989 -- Procedure Name : validate if the value is of the proper datatype(C, N, D)
990 -- Description : Check if value is of the right datatype
991 -- Business Rules :
992 -- Parameters :
993 -- Version : 1.0
994 -- End of comments
995
996 FUNCTION val_num(p_stg_in IN VARCHAR2)
997 RETURN BOOLEAN IS
998 l_val NUMBER;
999 BEGIN
1000 l_val := TO_NUMBER(p_stg_in);
1001 RETURN TRUE;
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 RETURN FALSE;
1005 END val_num;
1006
1007 FUNCTION val_date(p_stg_in IN VARCHAR2)
1008 RETURN BOOLEAN IS
1009 l_val DATE;
1010 BEGIN
1011 l_val := TO_DATE(p_stg_in,'YYYY/MM/DD');
1012 RETURN TRUE;
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 RETURN FALSE;
1016 END val_date;
1017
1018 -- Start of comments
1019 -- Procedure Name : validate_value_datatype
1020 -- Description : Check if value is of the right datatype
1021 -- Business Rules :
1022 -- Parameters :
1023 -- Version : 1.0
1024 -- End of comments
1025
1026 PROCEDURE validate_value_datatype(
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 p_oatv_rec IN oatv_rec_type) IS
1029
1030 cursor pdp_cur(p_pdp_id IN NUMBER) is
1031 select data_type
1032 from okc_process_def_parameters_v
1033 where id = p_pdp_id;
1034
1035 l_pdp_data_type VARCHAR2(90);
1036 l_func_datatype BOOLEAN;
1037
1038 BEGIN
1039 -- initialize return status
1040 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1041
1042 -- Check if value is of the right datatype
1043 IF p_oatv_rec.value is not null and p_oatv_rec.pdp_id is not null then
1044 OPEN pdp_cur(p_oatv_rec.pdp_id);
1045 FETCH pdp_cur into l_pdp_data_type;
1046 CLOSE pdp_cur;
1047
1048 IF l_pdp_data_type = 'N' THEN
1049 l_func_datatype := val_num(p_oatv_rec.value);
1050 IF l_func_datatype = FALSE THEN
1051 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1052 p_msg_name => g_invalid_value,
1053 p_token1 => g_col_name_token,
1054 p_token1_value => 'value',
1055 p_token2 => g_col_name_token,
1056 p_token2_value => 'pdp_id');
1057 x_return_status := OKC_API.G_RET_STS_ERROR;
1058 raise G_EXCEPTION_HALT_VALIDATION;
1059 END IF;
1060 END IF;
1061
1062 IF l_pdp_data_type = 'C' THEN
1063 l_func_datatype := val_num(p_oatv_rec.value);
1064 IF l_func_datatype = TRUE THEN
1065 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1066 p_msg_name => g_invalid_value,
1067 p_token1 => g_col_name_token,
1068 p_token1_value => 'value',
1069 p_token2 => g_col_name_token,
1070 p_token2_value => 'pdp_id');
1071 x_return_status := OKC_API.G_RET_STS_ERROR;
1072 raise G_EXCEPTION_HALT_VALIDATION;
1073 END IF;
1074 END IF;
1075
1076 IF l_pdp_data_type = 'D' THEN
1077 l_func_datatype := val_date(p_oatv_rec.value);
1078 IF l_func_datatype = FALSE THEN
1079 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1080 p_msg_name => g_invalid_value,
1081 p_token1 => g_col_name_token,
1082 p_token1_value => 'value',
1083 p_token2 => g_col_name_token,
1084 p_token2_value => 'pdp_id');
1085 x_return_status := OKC_API.G_RET_STS_ERROR;
1086 raise G_EXCEPTION_HALT_VALIDATION;
1087 END IF;
1088 END IF;
1089 END IF;
1090
1091 EXCEPTION
1092 when G_EXCEPTION_HALT_VALIDATION then
1093 -- no processing necessary; validation can continue
1094 -- with the next column
1095 null;
1096
1097 when OTHERS then
1098 -- store SQL error message on message stack for caller
1099 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1100 p_msg_name => g_unexpected_error,
1101 p_token1 => g_sqlcode_token,
1102 p_token1_value => sqlcode,
1103 p_token2 => g_sqlerrm_token,
1104 p_token2_value => sqlerrm);
1105 -- notify caller of an UNEXPECTED error
1106 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1107 END validate_value_datatype;
1108
1109 FUNCTION Validate_Record (
1110 p_oatv_rec IN oatv_rec_type
1111 ) RETURN VARCHAR2 IS
1112 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1113 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1114 BEGIN
1115 validate_aaeid_value(x_return_status => l_return_status
1116 ,p_oatv_rec => p_oatv_rec);
1117 -- store the highest degree of error
1118 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1119 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1120 l_return_status := x_return_status;
1121 RAISE G_EXCEPTION_HALT_VALIDATION;
1122 ELSE
1123 l_return_status := x_return_status; -- record that there was an error
1124 END IF;
1125 END IF;
1126
1127 validate_aaeid_datatype(x_return_status => l_return_status
1128 ,p_oatv_rec => p_oatv_rec);
1129 -- store the highest degree of error
1130 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1131 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1132 l_return_status := x_return_status;
1133 RAISE G_EXCEPTION_HALT_VALIDATION;
1134 ELSE
1135 l_return_status := x_return_status; -- record that there was an error
1136 END IF;
1137 END IF;
1138
1139 validate_value_datatype(x_return_status => l_return_status
1140 ,p_oatv_rec => p_oatv_rec);
1141 -- store the highest degree of error
1142 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1143 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1144 l_return_status := x_return_status;
1145 RAISE G_EXCEPTION_HALT_VALIDATION;
1146 ELSE
1147 l_return_status := x_return_status; -- record that there was an error
1148 END IF;
1149 END IF;
1150 RETURN (l_return_status);
1151 EXCEPTION
1152 When G_EXCEPTION_HALT_VALIDATION then
1153 --just come out with return status
1154 null;
1155 RETURN(l_return_status);
1156
1157 when OTHERS then
1158 -- store SQL error message on message stack for caller
1159 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1160 p_msg_name => g_unexpected_error,
1161 p_token1 => g_sqlcode_token,
1162 p_token1_value => sqlcode,
1163 p_token2 => g_sqlerrm_token,
1164 p_token2_value => sqlerrm);
1165
1166 -- notify caller of an UNEXPECTED error
1167 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1168 RETURN(l_return_status);
1169 END Validate_Record;
1170
1171
1172 /********* End Hand Written Code ***************************************/
1173
1174 ---------------------------------------------------------------------------
1175 -- PROCEDURE Migrate
1176 ---------------------------------------------------------------------------
1177 PROCEDURE migrate (
1178 p_from IN oatv_rec_type,
1179 p_to OUT NOCOPY oat_rec_type
1180 ) IS
1181 BEGIN
1182 p_to.id := p_from.id;
1183 p_to.oce_id := p_from.oce_id;
1184 p_to.pdp_id := p_from.pdp_id;
1185 p_to.aae_id := p_from.aae_id;
1186 p_to.dnz_chr_id := p_from.dnz_chr_id;
1187 p_to.object_version_number := p_from.object_version_number;
1188 p_to.created_by := p_from.created_by;
1189 p_to.creation_date := p_from.creation_date;
1190 p_to.last_updated_by := p_from.last_updated_by;
1191 p_to.last_update_date := p_from.last_update_date;
1192 p_to.value := p_from.value;
1193 p_to.last_update_login := p_from.last_update_login;
1194 p_to.application_id := p_from.application_id;
1195 p_to.seeded_flag := p_from.seeded_flag;
1196 END migrate;
1197 PROCEDURE migrate (
1198 p_from IN oat_rec_type,
1199 p_to IN OUT NOCOPY oatv_rec_type
1200 ) IS
1201 BEGIN
1202 p_to.id := p_from.id;
1203 p_to.oce_id := p_from.oce_id;
1204 p_to.pdp_id := p_from.pdp_id;
1205 p_to.aae_id := p_from.aae_id;
1206 p_to.dnz_chr_id := p_from.dnz_chr_id;
1207 p_to.object_version_number := p_from.object_version_number;
1208 p_to.created_by := p_from.created_by;
1209 p_to.creation_date := p_from.creation_date;
1210 p_to.last_updated_by := p_from.last_updated_by;
1211 p_to.last_update_date := p_from.last_update_date;
1212 p_to.value := p_from.value;
1213 p_to.last_update_login := p_from.last_update_login;
1214 p_to.application_id := p_from.application_id;
1215 p_to.seeded_flag := p_from.seeded_flag;
1216 END migrate;
1217
1218 ---------------------------------------------------------------------------
1219 -- PROCEDURE validate_row
1220 ---------------------------------------------------------------------------
1221 ----------------------------------------------
1222 -- validate_row for:OKC_OUTCOME_ARGUMENTS_V --
1223 ----------------------------------------------
1224 PROCEDURE validate_row(
1225 p_api_version IN NUMBER,
1226 p_init_msg_list IN VARCHAR2 ,
1227 x_return_status OUT NOCOPY VARCHAR2,
1228 x_msg_count OUT NOCOPY NUMBER,
1229 x_msg_data OUT NOCOPY VARCHAR2,
1230 p_oatv_rec IN oatv_rec_type) IS
1231
1232 l_api_version CONSTANT NUMBER := 1;
1233 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1234 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1235 l_oatv_rec oatv_rec_type := p_oatv_rec;
1236 l_oat_rec oat_rec_type;
1237 BEGIN
1238 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1239 G_PKG_NAME,
1240 p_init_msg_list,
1241 l_api_version,
1242 p_api_version,
1243 '_PVT',
1244 x_return_status);
1245 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1246 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1247 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1248 RAISE OKC_API.G_EXCEPTION_ERROR;
1249 END IF;
1250 --- Validate all non-missing attributes (Item Level Validation)
1251 l_return_status := Validate_Attributes(l_oatv_rec);
1252 --- If any errors happen abort API
1253 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1254 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1255 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1256 RAISE OKC_API.G_EXCEPTION_ERROR;
1257 END IF;
1258 l_return_status := Validate_Record(l_oatv_rec);
1259 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1260 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1261 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1262 RAISE OKC_API.G_EXCEPTION_ERROR;
1263 END IF;
1264 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1265 EXCEPTION
1266 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1267 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1268 (
1269 l_api_name,
1270 G_PKG_NAME,
1271 'OKC_API.G_RET_STS_ERROR',
1272 x_msg_count,
1273 x_msg_data,
1274 '_PVT'
1275 );
1276 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1277 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1278 (
1279 l_api_name,
1280 G_PKG_NAME,
1281 'OKC_API.G_RET_STS_UNEXP_ERROR',
1282 x_msg_count,
1283 x_msg_data,
1284 '_PVT'
1285 );
1286 WHEN OTHERS THEN
1287 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1288 (
1289 l_api_name,
1290 G_PKG_NAME,
1291 'OTHERS',
1292 x_msg_count,
1293 x_msg_data,
1294 '_PVT'
1295 );
1296 END validate_row;
1297 ------------------------------------------
1298 -- PL/SQL TBL validate_row for:OATV_TBL --
1299 ------------------------------------------
1300 PROCEDURE validate_row(
1301 p_api_version IN NUMBER,
1302 p_init_msg_list IN VARCHAR2 ,
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 x_msg_count OUT NOCOPY NUMBER,
1305 x_msg_data OUT NOCOPY VARCHAR2,
1306 p_oatv_tbl IN oatv_tbl_type) IS
1307
1308 l_api_version CONSTANT NUMBER := 1;
1309 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1310 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1311 i NUMBER := 0;
1312 BEGIN
1313 OKC_API.init_msg_list(p_init_msg_list);
1314 -- Make sure PL/SQL table has records in it before passing
1315 IF (p_oatv_tbl.COUNT > 0) THEN
1316 i := p_oatv_tbl.FIRST;
1317 LOOP
1318 validate_row (
1319 p_api_version => p_api_version,
1320 p_init_msg_list => OKC_API.G_FALSE,
1321 x_return_status => x_return_status,
1322 x_msg_count => x_msg_count,
1323 x_msg_data => x_msg_data,
1324 p_oatv_rec => p_oatv_tbl(i));
1325 EXIT WHEN (i = p_oatv_tbl.LAST);
1326 i := p_oatv_tbl.NEXT(i);
1327 END LOOP;
1328 END IF;
1329 EXCEPTION
1330 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1331 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1332 (
1333 l_api_name,
1334 G_PKG_NAME,
1335 'OKC_API.G_RET_STS_ERROR',
1336 x_msg_count,
1337 x_msg_data,
1338 '_PVT'
1339 );
1340 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1341 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1342 (
1343 l_api_name,
1344 G_PKG_NAME,
1345 'OKC_API.G_RET_STS_UNEXP_ERROR',
1346 x_msg_count,
1347 x_msg_data,
1348 '_PVT'
1349 );
1350 WHEN OTHERS THEN
1351 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1352 (
1353 l_api_name,
1354 G_PKG_NAME,
1355 'OTHERS',
1356 x_msg_count,
1357 x_msg_data,
1358 '_PVT'
1359 );
1360 END validate_row;
1361
1362 ---------------------------------------------------------------------------
1363 -- PROCEDURE insert_row
1364 ---------------------------------------------------------------------------
1365 ------------------------------------------
1366 -- insert_row for:OKC_OUTCOME_ARGUMENTS --
1367 ------------------------------------------
1368 PROCEDURE insert_row(
1369 p_init_msg_list IN VARCHAR2 ,
1370 x_return_status OUT NOCOPY VARCHAR2,
1371 x_msg_count OUT NOCOPY NUMBER,
1372 x_msg_data OUT NOCOPY VARCHAR2,
1373 p_oat_rec IN oat_rec_type,
1374 x_oat_rec OUT NOCOPY oat_rec_type) IS
1375
1376 l_api_version CONSTANT NUMBER := 1;
1377 l_api_name CONSTANT VARCHAR2(30) := 'ARGUMENTS_insert_row';
1378 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1379 l_oat_rec oat_rec_type := p_oat_rec;
1380 l_def_oat_rec oat_rec_type;
1381 ----------------------------------------------
1382 -- Set_Attributes for:OKC_OUTCOME_ARGUMENTS --
1383 ----------------------------------------------
1384 FUNCTION Set_Attributes (
1385 p_oat_rec IN oat_rec_type,
1386 x_oat_rec OUT NOCOPY oat_rec_type
1387 ) RETURN VARCHAR2 IS
1388 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1389 BEGIN
1390 x_oat_rec := p_oat_rec;
1391 RETURN(l_return_status);
1392 END Set_Attributes;
1393 BEGIN
1394 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1395 p_init_msg_list,
1396 '_PVT',
1397 x_return_status);
1398 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1399 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1400 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1401 RAISE OKC_API.G_EXCEPTION_ERROR;
1402 END IF;
1403 --- Setting item attributes
1404 l_return_status := Set_Attributes(
1405 p_oat_rec, -- IN
1406 l_oat_rec); -- OUT
1407 --- If any errors happen abort API
1408 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1409 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1410 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1411 RAISE OKC_API.G_EXCEPTION_ERROR;
1412 END IF;
1413 INSERT INTO OKC_OUTCOME_ARGUMENTS(
1414 id,
1415 oce_id,
1416 pdp_id,
1417 aae_id,
1418 dnz_chr_id,
1419 object_version_number,
1420 created_by,
1421 creation_date,
1422 last_updated_by,
1423 last_update_date,
1424 value,
1425 last_update_login,
1426 application_id,
1427 seeded_flag)
1428 VALUES (
1429 l_oat_rec.id,
1430 l_oat_rec.oce_id,
1431 l_oat_rec.pdp_id,
1432 l_oat_rec.aae_id,
1433 l_oat_rec.dnz_chr_id,
1434 l_oat_rec.object_version_number,
1435 l_oat_rec.created_by,
1436 l_oat_rec.creation_date,
1437 l_oat_rec.last_updated_by,
1438 l_oat_rec.last_update_date,
1439 l_oat_rec.value,
1440 l_oat_rec.last_update_login,
1441 l_oat_rec.application_id,
1442 l_oat_rec.seeded_flag);
1443 -- Set OUT values
1444 x_oat_rec := l_oat_rec;
1445 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1446 EXCEPTION
1447 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1448 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1449 (
1450 l_api_name,
1451 G_PKG_NAME,
1452 'OKC_API.G_RET_STS_ERROR',
1453 x_msg_count,
1454 x_msg_data,
1455 '_PVT'
1456 );
1457 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1458 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1459 (
1460 l_api_name,
1461 G_PKG_NAME,
1462 'OKC_API.G_RET_STS_UNEXP_ERROR',
1463 x_msg_count,
1464 x_msg_data,
1465 '_PVT'
1466 );
1467 WHEN OTHERS THEN
1468 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1469 (
1470 l_api_name,
1471 G_PKG_NAME,
1472 'OTHERS',
1473 x_msg_count,
1474 x_msg_data,
1475 '_PVT'
1476 );
1477 END insert_row;
1478 --------------------------------------------
1479 -- insert_row for:OKC_OUTCOME_ARGUMENTS_V --
1480 --------------------------------------------
1481 PROCEDURE insert_row(
1482 p_api_version IN NUMBER,
1483 p_init_msg_list IN VARCHAR2 ,
1484 x_return_status OUT NOCOPY VARCHAR2,
1485 x_msg_count OUT NOCOPY NUMBER,
1486 x_msg_data OUT NOCOPY VARCHAR2,
1487 p_oatv_rec IN oatv_rec_type,
1488 x_oatv_rec OUT NOCOPY oatv_rec_type) IS
1489
1490 l_id NUMBER ;
1491 l_api_version CONSTANT NUMBER := 1;
1492 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1494 l_oatv_rec oatv_rec_type;
1495 l_def_oatv_rec oatv_rec_type;
1496 l_oat_rec oat_rec_type;
1497 lx_oat_rec oat_rec_type;
1498 -------------------------------
1499 -- FUNCTION fill_who_columns --
1500 -------------------------------
1501 FUNCTION fill_who_columns (
1502 p_oatv_rec IN oatv_rec_type
1503 ) RETURN oatv_rec_type IS
1504 l_oatv_rec oatv_rec_type := p_oatv_rec;
1505 BEGIN
1506 l_oatv_rec.CREATION_DATE := SYSDATE;
1507 l_oatv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1508 l_oatv_rec.LAST_UPDATE_DATE := l_oatv_rec.CREATION_DATE;
1509 l_oatv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1510 l_oatv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1511 RETURN(l_oatv_rec);
1512 END fill_who_columns;
1513 ------------------------------------------------
1514 -- Set_Attributes for:OKC_OUTCOME_ARGUMENTS_V --
1515 ------------------------------------------------
1516 FUNCTION Set_Attributes (
1517 p_oatv_rec IN oatv_rec_type,
1518 x_oatv_rec OUT NOCOPY oatv_rec_type
1519 ) RETURN VARCHAR2 IS
1520 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1521 BEGIN
1522 x_oatv_rec := p_oatv_rec;
1523 x_oatv_rec.OBJECT_VERSION_NUMBER := 1;
1524 RETURN(l_return_status);
1525 END Set_Attributes;
1526 BEGIN
1527 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1528 G_PKG_NAME,
1529 p_init_msg_list,
1530 l_api_version,
1531 p_api_version,
1532 '_PVT',
1533 x_return_status);
1534 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1535 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1536 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1537 RAISE OKC_API.G_EXCEPTION_ERROR;
1538 END IF;
1539 l_oatv_rec := null_out_defaults(p_oatv_rec);
1540 -- Set primary key value
1541 -- If outcome argument is created by seed then use sequence generated id
1542 IF l_oatv_rec.CREATED_BY = 1 THEN
1543 SELECT OKC_OUTCOME_ARGUMENTS_S1.nextval INTO l_id FROM dual;
1544 l_oatv_rec.ID := l_id;
1545 l_oatv_rec.seeded_flag := 'Y';
1546 ELSE
1547 l_oatv_rec.ID := get_seq_id;
1548 l_oatv_rec.seeded_flag := 'N';
1549 END IF;
1550
1551 --l_oatv_rec.ID := get_seq_id;
1552 --- Setting item attributes
1553 l_return_status := Set_Attributes(
1554 l_oatv_rec, -- IN
1555 l_def_oatv_rec); -- OUT
1556 --- If any errors happen abort API
1557 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1558 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1559 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1560 RAISE OKC_API.G_EXCEPTION_ERROR;
1561 END IF;
1562 l_def_oatv_rec := fill_who_columns(l_def_oatv_rec);
1563 --- Validate all non-missing attributes (Item Level Validation)
1564 l_return_status := Validate_Attributes(l_def_oatv_rec);
1565 --- If any errors happen abort API
1566 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1567 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1568 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1569 RAISE OKC_API.G_EXCEPTION_ERROR;
1570 END IF;
1571 l_return_status := Validate_Record(l_def_oatv_rec);
1572 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1573 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1574 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1575 RAISE OKC_API.G_EXCEPTION_ERROR;
1576 END IF;
1577 --------------------------------------
1578 -- Move VIEW record to "Child" records
1579 --------------------------------------
1580 migrate(l_def_oatv_rec, l_oat_rec);
1581 --------------------------------------------
1582 -- Call the INSERT_ROW for each child record
1583 --------------------------------------------
1584 insert_row(
1585 p_init_msg_list,
1586 x_return_status,
1587 x_msg_count,
1588 x_msg_data,
1589 l_oat_rec,
1590 lx_oat_rec
1591 );
1592 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1593 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1594 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1595 RAISE OKC_API.G_EXCEPTION_ERROR;
1596 END IF;
1597 migrate(lx_oat_rec, l_def_oatv_rec);
1598 -- Set OUT values
1599 x_oatv_rec := l_def_oatv_rec;
1600 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1601 EXCEPTION
1602 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1603 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1604 (
1605 l_api_name,
1606 G_PKG_NAME,
1607 'OKC_API.G_RET_STS_ERROR',
1608 x_msg_count,
1609 x_msg_data,
1610 '_PVT'
1611 );
1612 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1613 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1614 (
1615 l_api_name,
1616 G_PKG_NAME,
1617 'OKC_API.G_RET_STS_UNEXP_ERROR',
1618 x_msg_count,
1619 x_msg_data,
1620 '_PVT'
1621 );
1622 WHEN OTHERS THEN
1623 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1624 (
1625 l_api_name,
1626 G_PKG_NAME,
1627 'OTHERS',
1628 x_msg_count,
1629 x_msg_data,
1630 '_PVT'
1631 );
1632 END insert_row;
1633 ----------------------------------------
1634 -- PL/SQL TBL insert_row for:OATV_TBL --
1635 ----------------------------------------
1636 PROCEDURE insert_row(
1637 p_api_version IN NUMBER,
1638 p_init_msg_list IN VARCHAR2 ,
1639 x_return_status OUT NOCOPY VARCHAR2,
1640 x_msg_count OUT NOCOPY NUMBER,
1641 x_msg_data OUT NOCOPY VARCHAR2,
1642 p_oatv_tbl IN oatv_tbl_type,
1643 x_oatv_tbl OUT NOCOPY oatv_tbl_type) IS
1644
1645 l_api_version CONSTANT NUMBER := 1;
1646 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1647 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1648 i NUMBER := 0;
1649 BEGIN
1650 OKC_API.init_msg_list(p_init_msg_list);
1651 -- Make sure PL/SQL table has records in it before passing
1652 IF (p_oatv_tbl.COUNT > 0) THEN
1653 i := p_oatv_tbl.FIRST;
1654 LOOP
1655 insert_row (
1656 p_api_version => p_api_version,
1657 p_init_msg_list => OKC_API.G_FALSE,
1658 x_return_status => x_return_status,
1659 x_msg_count => x_msg_count,
1660 x_msg_data => x_msg_data,
1661 p_oatv_rec => p_oatv_tbl(i),
1662 x_oatv_rec => x_oatv_tbl(i));
1663 EXIT WHEN (i = p_oatv_tbl.LAST);
1664 i := p_oatv_tbl.NEXT(i);
1665 END LOOP;
1666 END IF;
1667 EXCEPTION
1668 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1669 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1670 (
1671 l_api_name,
1672 G_PKG_NAME,
1673 'OKC_API.G_RET_STS_ERROR',
1674 x_msg_count,
1675 x_msg_data,
1676 '_PVT'
1677 );
1678 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1679 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1680 (
1681 l_api_name,
1682 G_PKG_NAME,
1683 'OKC_API.G_RET_STS_UNEXP_ERROR',
1684 x_msg_count,
1685 x_msg_data,
1686 '_PVT'
1687 );
1688 WHEN OTHERS THEN
1689 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1690 (
1691 l_api_name,
1692 G_PKG_NAME,
1693 'OTHERS',
1694 x_msg_count,
1695 x_msg_data,
1696 '_PVT'
1697 );
1698 END insert_row;
1699
1700 ---------------------------------------------------------------------------
1701 -- PROCEDURE lock_row
1702 ---------------------------------------------------------------------------
1703 ----------------------------------------
1704 -- lock_row for:OKC_OUTCOME_ARGUMENTS --
1705 ----------------------------------------
1706 PROCEDURE lock_row(
1707 p_init_msg_list IN VARCHAR2 ,
1708 x_return_status OUT NOCOPY VARCHAR2,
1709 x_msg_count OUT NOCOPY NUMBER,
1710 x_msg_data OUT NOCOPY VARCHAR2,
1711 p_oat_rec IN oat_rec_type) IS
1712
1713 E_Resource_Busy EXCEPTION;
1714 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1715 CURSOR lock_csr (p_oat_rec IN oat_rec_type) IS
1716 SELECT OBJECT_VERSION_NUMBER
1717 FROM OKC_OUTCOME_ARGUMENTS
1718 WHERE ID = p_oat_rec.id
1719 AND OBJECT_VERSION_NUMBER = p_oat_rec.object_version_number
1720 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1721
1722 CURSOR lchk_csr (p_oat_rec IN oat_rec_type) IS
1723 SELECT OBJECT_VERSION_NUMBER
1724 FROM OKC_OUTCOME_ARGUMENTS
1725 WHERE ID = p_oat_rec.id;
1726 l_api_version CONSTANT NUMBER := 1;
1727 l_api_name CONSTANT VARCHAR2(30) := 'ARGUMENTS_lock_row';
1728 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1729 l_object_version_number OKC_OUTCOME_ARGUMENTS.OBJECT_VERSION_NUMBER%TYPE;
1730 lc_object_version_number OKC_OUTCOME_ARGUMENTS.OBJECT_VERSION_NUMBER%TYPE;
1731 l_row_notfound BOOLEAN := FALSE;
1732 lc_row_notfound BOOLEAN := FALSE;
1733 BEGIN
1734 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1735 p_init_msg_list,
1736 '_PVT',
1737 x_return_status);
1738 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1739 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1740 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1741 RAISE OKC_API.G_EXCEPTION_ERROR;
1742 END IF;
1743 BEGIN
1744 OPEN lock_csr(p_oat_rec);
1745 FETCH lock_csr INTO l_object_version_number;
1746 l_row_notfound := lock_csr%NOTFOUND;
1747 CLOSE lock_csr;
1748 EXCEPTION
1749 WHEN E_Resource_Busy THEN
1750 IF (lock_csr%ISOPEN) THEN
1751 CLOSE lock_csr;
1752 END IF;
1753 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1754 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1755 END;
1756
1757 IF ( l_row_notfound ) THEN
1758 OPEN lchk_csr(p_oat_rec);
1759 FETCH lchk_csr INTO lc_object_version_number;
1760 lc_row_notfound := lchk_csr%NOTFOUND;
1761 CLOSE lchk_csr;
1762 END IF;
1763 IF (lc_row_notfound) THEN
1764 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1765 RAISE OKC_API.G_EXCEPTION_ERROR;
1766 ELSIF lc_object_version_number > p_oat_rec.object_version_number THEN
1767 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1768 RAISE OKC_API.G_EXCEPTION_ERROR;
1769 ELSIF lc_object_version_number <> p_oat_rec.object_version_number THEN
1770 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1771 RAISE OKC_API.G_EXCEPTION_ERROR;
1772 ELSIF lc_object_version_number = -1 THEN
1773 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1774 RAISE OKC_API.G_EXCEPTION_ERROR;
1775 END IF;
1776 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1777 EXCEPTION
1778 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1779 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1780 (
1781 l_api_name,
1782 G_PKG_NAME,
1783 'OKC_API.G_RET_STS_ERROR',
1784 x_msg_count,
1785 x_msg_data,
1786 '_PVT'
1787 );
1788 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1789 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1790 (
1791 l_api_name,
1792 G_PKG_NAME,
1793 'OKC_API.G_RET_STS_UNEXP_ERROR',
1794 x_msg_count,
1795 x_msg_data,
1796 '_PVT'
1797 );
1798 WHEN OTHERS THEN
1799 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1800 (
1801 l_api_name,
1802 G_PKG_NAME,
1803 'OTHERS',
1804 x_msg_count,
1805 x_msg_data,
1806 '_PVT'
1807 );
1808 END lock_row;
1809 ------------------------------------------
1810 -- lock_row for:OKC_OUTCOME_ARGUMENTS_V --
1811 ------------------------------------------
1812 PROCEDURE lock_row(
1813 p_api_version IN NUMBER,
1814 p_init_msg_list IN VARCHAR2 ,
1815 x_return_status OUT NOCOPY VARCHAR2,
1816 x_msg_count OUT NOCOPY NUMBER,
1817 x_msg_data OUT NOCOPY VARCHAR2,
1818 p_oatv_rec IN oatv_rec_type) IS
1819
1820 l_api_version CONSTANT NUMBER := 1;
1821 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1822 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1823 l_oat_rec oat_rec_type;
1824 BEGIN
1825 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1826 G_PKG_NAME,
1827 p_init_msg_list,
1828 l_api_version,
1829 p_api_version,
1830 '_PVT',
1831 x_return_status);
1832 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1833 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1834 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1835 RAISE OKC_API.G_EXCEPTION_ERROR;
1836 END IF;
1837 --------------------------------------
1838 -- Move VIEW record to "Child" records
1839 --------------------------------------
1840 migrate(p_oatv_rec, l_oat_rec);
1841 --------------------------------------------
1842 -- Call the LOCK_ROW for each child record
1843 --------------------------------------------
1844 lock_row(
1845 p_init_msg_list,
1846 x_return_status,
1847 x_msg_count,
1848 x_msg_data,
1849 l_oat_rec
1850 );
1851 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1852 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1853 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1854 RAISE OKC_API.G_EXCEPTION_ERROR;
1855 END IF;
1856 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1857 EXCEPTION
1858 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1859 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1860 (
1861 l_api_name,
1862 G_PKG_NAME,
1863 'OKC_API.G_RET_STS_ERROR',
1864 x_msg_count,
1865 x_msg_data,
1866 '_PVT'
1867 );
1868 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1869 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1870 (
1871 l_api_name,
1872 G_PKG_NAME,
1873 'OKC_API.G_RET_STS_UNEXP_ERROR',
1874 x_msg_count,
1875 x_msg_data,
1876 '_PVT'
1877 );
1878 WHEN OTHERS THEN
1879 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1880 (
1881 l_api_name,
1882 G_PKG_NAME,
1883 'OTHERS',
1884 x_msg_count,
1885 x_msg_data,
1886 '_PVT'
1887 );
1888 END lock_row;
1889 --------------------------------------
1890 -- PL/SQL TBL lock_row for:OATV_TBL --
1891 --------------------------------------
1892 PROCEDURE lock_row(
1893 p_api_version IN NUMBER,
1894 p_init_msg_list IN VARCHAR2 ,
1895 x_return_status OUT NOCOPY VARCHAR2,
1896 x_msg_count OUT NOCOPY NUMBER,
1897 x_msg_data OUT NOCOPY VARCHAR2,
1898 p_oatv_tbl IN oatv_tbl_type) IS
1899
1900 l_api_version CONSTANT NUMBER := 1;
1901 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1902 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1903 i NUMBER := 0;
1904 BEGIN
1905 OKC_API.init_msg_list(p_init_msg_list);
1906 -- Make sure PL/SQL table has records in it before passing
1907 IF (p_oatv_tbl.COUNT > 0) THEN
1908 i := p_oatv_tbl.FIRST;
1909 LOOP
1910 lock_row (
1911 p_api_version => p_api_version,
1912 p_init_msg_list => OKC_API.G_FALSE,
1913 x_return_status => x_return_status,
1914 x_msg_count => x_msg_count,
1915 x_msg_data => x_msg_data,
1916 p_oatv_rec => p_oatv_tbl(i));
1917 EXIT WHEN (i = p_oatv_tbl.LAST);
1918 i := p_oatv_tbl.NEXT(i);
1919 END LOOP;
1920 END IF;
1921 EXCEPTION
1922 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1923 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1924 (
1925 l_api_name,
1926 G_PKG_NAME,
1927 'OKC_API.G_RET_STS_ERROR',
1928 x_msg_count,
1929 x_msg_data,
1930 '_PVT'
1931 );
1932 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1933 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1934 (
1935 l_api_name,
1936 G_PKG_NAME,
1937 'OKC_API.G_RET_STS_UNEXP_ERROR',
1938 x_msg_count,
1939 x_msg_data,
1940 '_PVT'
1941 );
1942 WHEN OTHERS THEN
1943 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1944 (
1945 l_api_name,
1946 G_PKG_NAME,
1947 'OTHERS',
1948 x_msg_count,
1949 x_msg_data,
1950 '_PVT'
1951 );
1952 END lock_row;
1953
1954 ---------------------------------------------------------------------------
1955 -- PROCEDURE update_row
1956 ---------------------------------------------------------------------------
1957 ------------------------------------------
1958 -- update_row for:OKC_OUTCOME_ARGUMENTS --
1959 ------------------------------------------
1960 PROCEDURE update_row(
1961 p_init_msg_list IN VARCHAR2 ,
1962 x_return_status OUT NOCOPY VARCHAR2,
1963 x_msg_count OUT NOCOPY NUMBER,
1964 x_msg_data OUT NOCOPY VARCHAR2,
1965 p_oat_rec IN oat_rec_type,
1966 x_oat_rec OUT NOCOPY oat_rec_type) IS
1967
1968 l_api_version CONSTANT NUMBER := 1;
1969 l_api_name CONSTANT VARCHAR2(30) := 'ARGUMENTS_update_row';
1970 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1971 l_oat_rec oat_rec_type := p_oat_rec;
1972 l_def_oat_rec oat_rec_type;
1973 l_row_notfound BOOLEAN := TRUE;
1974 ----------------------------------
1975 -- FUNCTION populate_new_record --
1976 ----------------------------------
1977 FUNCTION populate_new_record (
1978 p_oat_rec IN oat_rec_type,
1979 x_oat_rec OUT NOCOPY oat_rec_type
1980 ) RETURN VARCHAR2 IS
1981 l_oat_rec oat_rec_type;
1982 l_row_notfound BOOLEAN := TRUE;
1983 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1984 BEGIN
1985 x_oat_rec := p_oat_rec;
1986 -- Get current database values
1987 l_oat_rec := get_rec(p_oat_rec, l_row_notfound);
1988 IF (l_row_notfound) THEN
1989 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1990 END IF;
1991 IF (x_oat_rec.id = OKC_API.G_MISS_NUM)
1992 THEN
1993 x_oat_rec.id := l_oat_rec.id;
1994 END IF;
1995 IF (x_oat_rec.oce_id = OKC_API.G_MISS_NUM)
1996 THEN
1997 x_oat_rec.oce_id := l_oat_rec.oce_id;
1998 END IF;
1999 IF (x_oat_rec.pdp_id = OKC_API.G_MISS_NUM)
2000 THEN
2001 x_oat_rec.pdp_id := l_oat_rec.pdp_id;
2002 END IF;
2003 IF (x_oat_rec.aae_id = OKC_API.G_MISS_NUM)
2004 THEN
2005 x_oat_rec.aae_id := l_oat_rec.aae_id;
2006 END IF;
2007 IF (x_oat_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2008 THEN
2009 x_oat_rec.dnz_chr_id := l_oat_rec.dnz_chr_id;
2010 END IF;
2011 IF (x_oat_rec.object_version_number = OKC_API.G_MISS_NUM)
2012 THEN
2013 x_oat_rec.object_version_number := l_oat_rec.object_version_number;
2014 END IF;
2015 IF (x_oat_rec.created_by = OKC_API.G_MISS_NUM)
2016 THEN
2017 x_oat_rec.created_by := l_oat_rec.created_by;
2018 END IF;
2019 IF (x_oat_rec.creation_date = OKC_API.G_MISS_DATE)
2020 THEN
2021 x_oat_rec.creation_date := l_oat_rec.creation_date;
2022 END IF;
2023 IF (x_oat_rec.last_updated_by = OKC_API.G_MISS_NUM)
2024 THEN
2025 x_oat_rec.last_updated_by := l_oat_rec.last_updated_by;
2026 END IF;
2027 IF (x_oat_rec.last_update_date = OKC_API.G_MISS_DATE)
2028 THEN
2029 x_oat_rec.last_update_date := l_oat_rec.last_update_date;
2030 END IF;
2031 IF (x_oat_rec.value = OKC_API.G_MISS_CHAR)
2032 THEN
2033 x_oat_rec.value := l_oat_rec.value;
2034 END IF;
2035 IF (x_oat_rec.last_update_login = OKC_API.G_MISS_NUM)
2036 THEN
2037 x_oat_rec.last_update_login := l_oat_rec.last_update_login;
2038 END IF;
2039 IF (x_oat_rec.application_id = OKC_API.G_MISS_NUM)
2040 THEN
2041 x_oat_rec.application_id := l_oat_rec.application_id;
2042 END IF;
2043 IF (x_oat_rec.seeded_flag = OKC_API.G_MISS_CHAR)
2044 THEN
2045 x_oat_rec.seeded_flag := l_oat_rec.seeded_flag;
2046 END IF;
2047 RETURN(l_return_status);
2048 END populate_new_record;
2049 ----------------------------------------------
2050 -- Set_Attributes for:OKC_OUTCOME_ARGUMENTS --
2051 ----------------------------------------------
2052 FUNCTION Set_Attributes (
2053 p_oat_rec IN oat_rec_type,
2054 x_oat_rec OUT NOCOPY oat_rec_type
2055 ) RETURN VARCHAR2 IS
2056 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2057 BEGIN
2058 x_oat_rec := p_oat_rec;
2059 RETURN(l_return_status);
2060 END Set_Attributes;
2061 BEGIN
2062 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2063 p_init_msg_list,
2064 '_PVT',
2065 x_return_status);
2066 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2067 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2068 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2069 RAISE OKC_API.G_EXCEPTION_ERROR;
2070 END IF;
2071 --- Setting item attributes
2072 l_return_status := Set_Attributes(
2073 p_oat_rec, -- IN
2074 l_oat_rec); -- OUT
2075 --- If any errors happen abort API
2076 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2077 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2078 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2079 RAISE OKC_API.G_EXCEPTION_ERROR;
2080 END IF;
2081 l_return_status := populate_new_record(l_oat_rec, l_def_oat_rec);
2082 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2083 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2084 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2085 RAISE OKC_API.G_EXCEPTION_ERROR;
2086 END IF;
2087 UPDATE OKC_OUTCOME_ARGUMENTS
2088 SET OCE_ID = l_def_oat_rec.oce_id,
2089 PDP_ID = l_def_oat_rec.pdp_id,
2090 AAE_ID = l_def_oat_rec.aae_id,
2091 DNZ_CHR_ID = l_def_oat_rec.dnz_chr_id,
2092 OBJECT_VERSION_NUMBER = l_def_oat_rec.object_version_number,
2093 CREATED_BY = l_def_oat_rec.created_by,
2094 CREATION_DATE = l_def_oat_rec.creation_date,
2095 LAST_UPDATED_BY = l_def_oat_rec.last_updated_by,
2096 LAST_UPDATE_DATE = l_def_oat_rec.last_update_date,
2097 VALUE = l_def_oat_rec.value,
2098 LAST_UPDATE_LOGIN = l_def_oat_rec.last_update_login,
2099 APPLICATION_ID = l_def_oat_rec.application_id,
2100 SEEDED_FLAG = l_def_oat_rec.seeded_flag
2101 WHERE ID = l_def_oat_rec.id;
2102
2103 x_oat_rec := l_def_oat_rec;
2104 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2105 EXCEPTION
2106 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2107 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2108 (
2109 l_api_name,
2110 G_PKG_NAME,
2111 'OKC_API.G_RET_STS_ERROR',
2112 x_msg_count,
2113 x_msg_data,
2114 '_PVT'
2115 );
2116 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2117 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2118 (
2119 l_api_name,
2120 G_PKG_NAME,
2121 'OKC_API.G_RET_STS_UNEXP_ERROR',
2122 x_msg_count,
2123 x_msg_data,
2124 '_PVT'
2125 );
2126 WHEN OTHERS THEN
2127 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2128 (
2129 l_api_name,
2130 G_PKG_NAME,
2131 'OTHERS',
2132 x_msg_count,
2133 x_msg_data,
2134 '_PVT'
2135 );
2136 END update_row;
2137 --------------------------------------------
2138 -- update_row for:OKC_OUTCOME_ARGUMENTS_V --
2139 --------------------------------------------
2140 PROCEDURE update_row(
2141 p_api_version IN NUMBER,
2142 p_init_msg_list IN VARCHAR2 ,
2143 x_return_status OUT NOCOPY VARCHAR2,
2144 x_msg_count OUT NOCOPY NUMBER,
2145 x_msg_data OUT NOCOPY VARCHAR2,
2146 p_oatv_rec IN oatv_rec_type,
2147 x_oatv_rec OUT NOCOPY oatv_rec_type) IS
2148
2149 l_api_version CONSTANT NUMBER := 1;
2150 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2151 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2152 l_oatv_rec oatv_rec_type := p_oatv_rec;
2153 l_def_oatv_rec oatv_rec_type;
2154 l_oat_rec oat_rec_type;
2155 lx_oat_rec oat_rec_type;
2156 -------------------------------
2157 -- FUNCTION fill_who_columns --
2158 -------------------------------
2159 FUNCTION fill_who_columns (
2160 p_oatv_rec IN oatv_rec_type
2161 ) RETURN oatv_rec_type IS
2162 l_oatv_rec oatv_rec_type := p_oatv_rec;
2163 BEGIN
2164 l_oatv_rec.LAST_UPDATE_DATE := SYSDATE;
2165 l_oatv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2166 l_oatv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2167 RETURN(l_oatv_rec);
2168 END fill_who_columns;
2169 ----------------------------------
2170 -- FUNCTION populate_new_record --
2171 ----------------------------------
2172 FUNCTION populate_new_record (
2173 p_oatv_rec IN oatv_rec_type,
2174 x_oatv_rec OUT NOCOPY oatv_rec_type
2175 ) RETURN VARCHAR2 IS
2176 l_oatv_rec oatv_rec_type;
2177 l_row_notfound BOOLEAN := TRUE;
2178 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2179 BEGIN
2180 x_oatv_rec := p_oatv_rec;
2181 -- Get current database values
2182 l_oatv_rec := get_rec(p_oatv_rec, l_row_notfound);
2183 IF (l_row_notfound) THEN
2184 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2185 END IF;
2186 IF (x_oatv_rec.id = OKC_API.G_MISS_NUM)
2187 THEN
2188 x_oatv_rec.id := l_oatv_rec.id;
2189 END IF;
2190 IF (x_oatv_rec.pdp_id = OKC_API.G_MISS_NUM)
2191 THEN
2192 x_oatv_rec.pdp_id := l_oatv_rec.pdp_id;
2193 END IF;
2194 IF (x_oatv_rec.oce_id = OKC_API.G_MISS_NUM)
2195 THEN
2196 x_oatv_rec.oce_id := l_oatv_rec.oce_id;
2197 END IF;
2198 IF (x_oatv_rec.aae_id = OKC_API.G_MISS_NUM)
2199 THEN
2200 x_oatv_rec.aae_id := l_oatv_rec.aae_id;
2201 END IF;
2202 IF (x_oatv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2203 THEN
2204 x_oatv_rec.dnz_chr_id := l_oatv_rec.dnz_chr_id;
2205 END IF;
2206 IF (x_oatv_rec.object_version_number = OKC_API.G_MISS_NUM)
2207 THEN
2208 x_oatv_rec.object_version_number := l_oatv_rec.object_version_number;
2209 END IF;
2210 IF (x_oatv_rec.value = OKC_API.G_MISS_CHAR)
2211 THEN
2212 x_oatv_rec.value := l_oatv_rec.value;
2213 END IF;
2214 IF (x_oatv_rec.application_id = OKC_API.G_MISS_NUM)
2215 THEN
2216 x_oatv_rec.application_id := l_oatv_rec.application_id;
2217 END IF;
2218 IF (x_oatv_rec.seeded_flag = OKC_API.G_MISS_CHAR)
2219 THEN
2220 x_oatv_rec.seeded_flag := l_oatv_rec.seeded_flag;
2221 END IF;
2222 IF (x_oatv_rec.created_by = OKC_API.G_MISS_NUM)
2223 THEN
2224 x_oatv_rec.created_by := l_oatv_rec.created_by;
2225 END IF;
2226 IF (x_oatv_rec.creation_date = OKC_API.G_MISS_DATE)
2227 THEN
2228 x_oatv_rec.creation_date := l_oatv_rec.creation_date;
2229 END IF;
2230 IF (x_oatv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2231 THEN
2232 x_oatv_rec.last_updated_by := l_oatv_rec.last_updated_by;
2233 END IF;
2234 IF (x_oatv_rec.last_update_date = OKC_API.G_MISS_DATE)
2235 THEN
2236 x_oatv_rec.last_update_date := l_oatv_rec.last_update_date;
2237 END IF;
2238 IF (x_oatv_rec.last_update_login = OKC_API.G_MISS_NUM)
2239 THEN
2240 x_oatv_rec.last_update_login := l_oatv_rec.last_update_login;
2241 END IF;
2242 RETURN(l_return_status);
2243 END populate_new_record;
2244 ------------------------------------------------
2245 -- Set_Attributes for:OKC_OUTCOME_ARGUMENTS_V --
2246 ------------------------------------------------
2247 FUNCTION Set_Attributes (
2248 p_oatv_rec IN oatv_rec_type,
2249 x_oatv_rec OUT NOCOPY oatv_rec_type
2250 ) RETURN VARCHAR2 IS
2251 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2252 BEGIN
2253 x_oatv_rec := p_oatv_rec;
2254 x_oatv_rec.OBJECT_VERSION_NUMBER := NVL(x_oatv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2255 RETURN(l_return_status);
2256 END Set_Attributes;
2257 BEGIN
2258 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2259 G_PKG_NAME,
2260 p_init_msg_list,
2261 l_api_version,
2262 p_api_version,
2263 '_PVT',
2264 x_return_status);
2265 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2266 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2267 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2268 RAISE OKC_API.G_EXCEPTION_ERROR;
2269 END IF;
2270 -- Seed data should not be updated unless user is DATAMERGE
2271 IF l_oatv_rec.last_updated_by <> 1 THEN
2272 IF l_oatv_rec.seeded_flag = 'Y' THEN
2273 OKC_API.set_message(p_app_name => G_APP_NAME,
2274 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
2275 RAISE OKC_API.G_EXCEPTION_ERROR;
2276 END IF;
2277 END IF;
2278 --- Setting item attributes
2279 l_return_status := Set_Attributes(
2280 p_oatv_rec, -- IN
2281 l_oatv_rec); -- OUT
2282 --- If any errors happen abort API
2283 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2284 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2285 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2286 RAISE OKC_API.G_EXCEPTION_ERROR;
2287 END IF;
2288 l_return_status := populate_new_record(l_oatv_rec, l_def_oatv_rec);
2289 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2291 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2292 RAISE OKC_API.G_EXCEPTION_ERROR;
2293 END IF;
2294 l_def_oatv_rec := fill_who_columns(l_def_oatv_rec);
2295 --- Validate all non-missing attributes (Item Level Validation)
2296 l_return_status := Validate_Attributes(l_def_oatv_rec);
2297 --- If any errors happen abort API
2298 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2299 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2300 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2301 RAISE OKC_API.G_EXCEPTION_ERROR;
2302 END IF;
2303 l_return_status := Validate_Record(l_def_oatv_rec);
2304 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2305 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2306 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2307 RAISE OKC_API.G_EXCEPTION_ERROR;
2308 END IF;
2309
2310 --------------------------------------
2311 -- Move VIEW record to "Child" records
2312 --------------------------------------
2313 migrate(l_def_oatv_rec, l_oat_rec);
2314 --------------------------------------------
2315 -- Call the UPDATE_ROW for each child record
2316 --------------------------------------------
2317 update_row(
2318 p_init_msg_list,
2319 x_return_status,
2320 x_msg_count,
2321 x_msg_data,
2322 l_oat_rec,
2323 lx_oat_rec
2324 );
2325 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2327 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2328 RAISE OKC_API.G_EXCEPTION_ERROR;
2329 END IF;
2330 migrate(lx_oat_rec, l_def_oatv_rec);
2331 x_oatv_rec := l_def_oatv_rec;
2332 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2333 EXCEPTION
2334 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2335 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2336 (
2337 l_api_name,
2338 G_PKG_NAME,
2339 'OKC_API.G_RET_STS_ERROR',
2340 x_msg_count,
2341 x_msg_data,
2342 '_PVT'
2343 );
2344 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2345 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2346 (
2347 l_api_name,
2348 G_PKG_NAME,
2349 'OKC_API.G_RET_STS_UNEXP_ERROR',
2350 x_msg_count,
2351 x_msg_data,
2352 '_PVT'
2353 );
2354 WHEN OTHERS THEN
2355 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2356 (
2357 l_api_name,
2358 G_PKG_NAME,
2359 'OTHERS',
2360 x_msg_count,
2361 x_msg_data,
2362 '_PVT'
2363 );
2364 END update_row;
2365 ----------------------------------------
2366 -- PL/SQL TBL update_row for:OATV_TBL --
2367 ----------------------------------------
2368 PROCEDURE update_row(
2369 p_api_version IN NUMBER,
2370 p_init_msg_list IN VARCHAR2 ,
2371 x_return_status OUT NOCOPY VARCHAR2,
2372 x_msg_count OUT NOCOPY NUMBER,
2373 x_msg_data OUT NOCOPY VARCHAR2,
2374 p_oatv_tbl IN oatv_tbl_type,
2375 x_oatv_tbl OUT NOCOPY oatv_tbl_type) IS
2376
2377 l_api_version CONSTANT NUMBER := 1;
2378 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2379 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2380 i NUMBER := 0;
2381 BEGIN
2382 OKC_API.init_msg_list(p_init_msg_list);
2383 -- Make sure PL/SQL table has records in it before passing
2384 IF (p_oatv_tbl.COUNT > 0) THEN
2385 i := p_oatv_tbl.FIRST;
2386 LOOP
2387 update_row (
2388 p_api_version => p_api_version,
2389 p_init_msg_list => OKC_API.G_FALSE,
2390 x_return_status => x_return_status,
2391 x_msg_count => x_msg_count,
2392 x_msg_data => x_msg_data,
2393 p_oatv_rec => p_oatv_tbl(i),
2394 x_oatv_rec => x_oatv_tbl(i));
2395 EXIT WHEN (i = p_oatv_tbl.LAST);
2396 i := p_oatv_tbl.NEXT(i);
2397 END LOOP;
2398 END IF;
2399 EXCEPTION
2400 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2401 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2402 (
2403 l_api_name,
2404 G_PKG_NAME,
2405 'OKC_API.G_RET_STS_ERROR',
2406 x_msg_count,
2407 x_msg_data,
2408 '_PVT'
2409 );
2410 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2411 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2412 (
2413 l_api_name,
2414 G_PKG_NAME,
2415 'OKC_API.G_RET_STS_UNEXP_ERROR',
2416 x_msg_count,
2417 x_msg_data,
2418 '_PVT'
2419 );
2420 WHEN OTHERS THEN
2421 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2422 (
2423 l_api_name,
2424 G_PKG_NAME,
2425 'OTHERS',
2426 x_msg_count,
2427 x_msg_data,
2428 '_PVT'
2429 );
2430 END update_row;
2431
2432 ---------------------------------------------------------------------------
2433 -- PROCEDURE delete_row
2434 ---------------------------------------------------------------------------
2435 ------------------------------------------
2436 -- delete_row for:OKC_OUTCOME_ARGUMENTS --
2437 ------------------------------------------
2438 PROCEDURE delete_row(
2439 p_init_msg_list IN VARCHAR2 ,
2440 x_return_status OUT NOCOPY VARCHAR2,
2441 x_msg_count OUT NOCOPY NUMBER,
2442 x_msg_data OUT NOCOPY VARCHAR2,
2443 p_oat_rec IN oat_rec_type) IS
2444
2445 l_api_version CONSTANT NUMBER := 1;
2446 l_api_name CONSTANT VARCHAR2(30) := 'ARGUMENTS_delete_row';
2447 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2448 l_oat_rec oat_rec_type:= p_oat_rec;
2449 l_row_notfound BOOLEAN := TRUE;
2450 BEGIN
2451 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2452 p_init_msg_list,
2453 '_PVT',
2454 x_return_status);
2455 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2456 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2457 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2458 RAISE OKC_API.G_EXCEPTION_ERROR;
2459 END IF;
2460 DELETE FROM OKC_OUTCOME_ARGUMENTS
2461 WHERE ID = l_oat_rec.id;
2462
2463 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2464 EXCEPTION
2465 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2466 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2467 (
2468 l_api_name,
2469 G_PKG_NAME,
2470 'OKC_API.G_RET_STS_ERROR',
2471 x_msg_count,
2472 x_msg_data,
2473 '_PVT'
2474 );
2475 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_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_UNEXP_ERROR',
2481 x_msg_count,
2482 x_msg_data,
2483 '_PVT'
2484 );
2485 WHEN OTHERS THEN
2486 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2487 (
2488 l_api_name,
2489 G_PKG_NAME,
2490 'OTHERS',
2491 x_msg_count,
2492 x_msg_data,
2493 '_PVT'
2494 );
2495 END delete_row;
2496 --------------------------------------------
2497 -- delete_row for:OKC_OUTCOME_ARGUMENTS_V --
2498 --------------------------------------------
2499 PROCEDURE delete_row(
2500 p_api_version IN NUMBER,
2501 p_init_msg_list IN VARCHAR2 ,
2502 x_return_status OUT NOCOPY VARCHAR2,
2503 x_msg_count OUT NOCOPY NUMBER,
2504 x_msg_data OUT NOCOPY VARCHAR2,
2505 p_oatv_rec IN oatv_rec_type) IS
2506
2507 l_api_version CONSTANT NUMBER := 1;
2508 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2509 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2510 l_oatv_rec oatv_rec_type := p_oatv_rec;
2511 l_oat_rec oat_rec_type;
2512 BEGIN
2513 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2514 G_PKG_NAME,
2515 p_init_msg_list,
2516 l_api_version,
2517 p_api_version,
2518 '_PVT',
2519 x_return_status);
2520 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2521 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2522 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2523 RAISE OKC_API.G_EXCEPTION_ERROR;
2524 END IF;
2525 -- Seed data should not be deleted unless user is DATAMERGE
2526 IF l_oatv_rec.last_updated_by <> 1 THEN
2527 IF l_oatv_rec.seeded_flag = 'Y' THEN
2528 OKC_API.set_message(p_app_name => G_APP_NAME,
2529 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
2530 RAISE OKC_API.G_EXCEPTION_ERROR;
2531 END IF;
2532 END IF;
2533 --------------------------------------
2534 -- Move VIEW record to "Child" records
2535 --------------------------------------
2536 migrate(l_oatv_rec, l_oat_rec);
2537 --------------------------------------------
2538 -- Call the DELETE_ROW for each child record
2539 --------------------------------------------
2540 delete_row(
2541 p_init_msg_list,
2542 x_return_status,
2543 x_msg_count,
2544 x_msg_data,
2545 l_oat_rec
2546 );
2547 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2548 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2549 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2550 RAISE OKC_API.G_EXCEPTION_ERROR;
2551 END IF;
2552 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2553 EXCEPTION
2554 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2555 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2556 (
2557 l_api_name,
2558 G_PKG_NAME,
2559 'OKC_API.G_RET_STS_ERROR',
2560 x_msg_count,
2561 x_msg_data,
2562 '_PVT'
2563 );
2564 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2565 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2566 (
2567 l_api_name,
2568 G_PKG_NAME,
2569 'OKC_API.G_RET_STS_UNEXP_ERROR',
2570 x_msg_count,
2571 x_msg_data,
2572 '_PVT'
2573 );
2574 WHEN OTHERS THEN
2575 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2576 (
2577 l_api_name,
2578 G_PKG_NAME,
2579 'OTHERS',
2580 x_msg_count,
2581 x_msg_data,
2582 '_PVT'
2583 );
2584 END delete_row;
2585 ----------------------------------------
2586 -- PL/SQL TBL delete_row for:OATV_TBL --
2587 ----------------------------------------
2588 PROCEDURE delete_row(
2589 p_api_version IN NUMBER,
2590 p_init_msg_list IN VARCHAR2 ,
2591 x_return_status OUT NOCOPY VARCHAR2,
2592 x_msg_count OUT NOCOPY NUMBER,
2593 x_msg_data OUT NOCOPY VARCHAR2,
2594 p_oatv_tbl IN oatv_tbl_type) IS
2595
2596 l_api_version CONSTANT NUMBER := 1;
2597 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2598 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2599 i NUMBER := 0;
2600 BEGIN
2601 OKC_API.init_msg_list(p_init_msg_list);
2602 -- Make sure PL/SQL table has records in it before passing
2603 IF (p_oatv_tbl.COUNT > 0) THEN
2604 i := p_oatv_tbl.FIRST;
2605 LOOP
2606 delete_row (
2607 p_api_version => p_api_version,
2608 p_init_msg_list => OKC_API.G_FALSE,
2609 x_return_status => x_return_status,
2610 x_msg_count => x_msg_count,
2611 x_msg_data => x_msg_data,
2612 p_oatv_rec => p_oatv_tbl(i));
2613 EXIT WHEN (i = p_oatv_tbl.LAST);
2614 i := p_oatv_tbl.NEXT(i);
2615 END LOOP;
2616 END IF;
2617 EXCEPTION
2618 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2619 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2620 (
2621 l_api_name,
2622 G_PKG_NAME,
2623 'OKC_API.G_RET_STS_ERROR',
2624 x_msg_count,
2625 x_msg_data,
2626 '_PVT'
2627 );
2628 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2629 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2630 (
2631 l_api_name,
2632 G_PKG_NAME,
2633 'OKC_API.G_RET_STS_UNEXP_ERROR',
2634 x_msg_count,
2635 x_msg_data,
2636 '_PVT'
2637 );
2638 WHEN OTHERS THEN
2639 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2640 (
2641 l_api_name,
2642 G_PKG_NAME,
2643 'OTHERS',
2644 x_msg_count,
2645 x_msg_data,
2646 '_PVT'
2647 );
2648 END delete_row;
2649
2650 ---------------------------------------------------------------
2651 -- Procedure for mass insert in OKC_OUTCOME_ARGUMENTS _B and TL tables
2652 ---------------------------------------------------------------
2653 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_oatv_tbl oatv_tbl_type) IS
2654 l_tabsize NUMBER := p_oatv_tbl.COUNT;
2655 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
2656
2657 in_id OKC_DATATYPES.NumberTabTyp;
2658 in_pdp_id OKC_DATATYPES.NumberTabTyp;
2659 in_oce_id OKC_DATATYPES.NumberTabTyp;
2660 in_aae_id OKC_DATATYPES.NumberTabTyp;
2661 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
2662 in_object_version_number OKC_DATATYPES.NumberTabTyp;
2663 in_value OKC_DATATYPES.Var1995TabTyp;
2664 in_application_id OKC_DATATYPES.NumberTabTyp;
2665 in_seeded_flag OKC_DATATYPES.Var3TabTyp;
2666 in_created_by OKC_DATATYPES.NumberTabTyp;
2667 in_creation_date OKC_DATATYPES.DateTabTyp;
2668 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
2669 in_last_update_date OKC_DATATYPES.DateTabTyp;
2670 in_last_update_login OKC_DATATYPES.NumberTabTyp;
2671 j NUMBER := 0;
2672 i NUMBER := p_oatv_tbl.FIRST;
2673 BEGIN
2674
2675 --Initialize return status
2676 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2677 while i is not null
2678 LOOP
2679 j := j + 1;
2680 in_id (j) := p_oatv_tbl(i).id;
2681 in_pdp_id (j) := p_oatv_tbl(i).pdp_id;
2682 in_oce_id (j) := p_oatv_tbl(i).oce_id;
2683 in_aae_id (j) := p_oatv_tbl(i).aae_id;
2684 in_dnz_chr_id (j) := p_oatv_tbl(i).dnz_chr_id;
2685 in_object_version_number (j) := p_oatv_tbl(i).object_version_number;
2686 in_value (j) := p_oatv_tbl(i).value;
2687 in_application_id (j) := p_oatv_tbl(i).application_id;
2688 in_seeded_flag (j) := p_oatv_tbl(i).seeded_flag;
2689 in_created_by (j) := p_oatv_tbl(i).created_by;
2690 in_creation_date (j) := p_oatv_tbl(i).creation_date;
2691 in_last_updated_by (j) := p_oatv_tbl(i).last_updated_by;
2692 in_last_update_date (j) := p_oatv_tbl(i).last_update_date;
2693 in_last_update_login (j) := p_oatv_tbl(i).last_update_login;
2694 i := p_oatv_tbl.NEXT(i);
2695 END LOOP;
2696
2697 FORALL i in 1..l_tabsize
2698 INSERT
2699 INTO OKC_OUTCOME_ARGUMENTS
2700 (
2701 id,
2702 oce_id,
2703 pdp_id,
2704 aae_id,
2705 dnz_chr_id,
2706 object_version_number,
2707 created_by,
2708 creation_date,
2709 last_updated_by,
2710 last_update_date,
2711 value,
2712 last_update_login,
2713 application_id,
2714 seeded_flag
2715 )
2716 VALUES (
2717 in_id(i),
2718 in_oce_id(i),
2719 in_pdp_id(i),
2720 in_aae_id(i),
2721 in_dnz_chr_id(i),
2722 in_object_version_number(i),
2723 in_created_by(i),
2724 in_creation_date(i),
2725 in_last_updated_by(i),
2726 in_last_update_date(i),
2727 in_value(i),
2728 in_last_update_login(i),
2729 in_application_id(i),
2730 in_seeded_flag(i)
2731 );
2732 EXCEPTION
2733 WHEN OTHERS THEN
2734 -- store SQL error message on message stack
2735 OKC_API.SET_MESSAGE(
2736 p_app_name => G_APP_NAME,
2737 p_msg_name => G_UNEXPECTED_ERROR,
2738 p_token1 => G_SQLCODE_TOKEN,
2739 p_token1_value => SQLCODE,
2740 p_token2 => G_SQLERRM_TOKEN,
2741 p_token2_value => SQLERRM);
2742 -- notify caller of an error as UNEXPECTED error
2743 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2744
2745 -- RAISE;
2746 END INSERT_ROW_UPG;
2747
2748 --This function is called from versioning API OKC_VERSION_PVT
2749 --Old Location: OKCRVERB.pls
2750 --New Location: Base Table API
2751
2752 FUNCTION create_version(
2753 p_chr_id IN NUMBER,
2754 p_major_version IN NUMBER
2755 ) RETURN VARCHAR2 IS
2756
2757 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2758
2759 BEGIN
2760 INSERT INTO okc_outcome_arguments_h
2761 (
2762 major_version,
2763 id,
2764 oce_id,
2765 pdp_id,
2766 aae_id,
2767 dnz_chr_id,
2768 object_version_number,
2769 created_by,
2770 creation_date,
2771 last_updated_by,
2772 last_update_date,
2773 value,
2774 last_update_login,
2775 application_id,
2776 seeded_flag
2777 )
2778 SELECT
2779 p_major_version,
2780 id,
2781 oce_id,
2782 pdp_id,
2783 aae_id,
2784 dnz_chr_id,
2785 object_version_number,
2786 created_by,
2787 creation_date,
2788 last_updated_by,
2789 last_update_date,
2790 value,
2791 last_update_login,
2792 application_id,
2793 seeded_flag
2794 FROM okc_outcome_arguments
2795 WHERE dnz_chr_id = p_chr_id;
2796
2797 RETURN l_return_status;
2798 EXCEPTION
2799 -- other appropriate handlers
2800 WHEN OTHERS THEN
2801 -- store SQL error message on message stack
2802 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2803 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2804 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2805 p_token1_value => sqlcode,
2806 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2807 p_token2_value => sqlerrm);
2808
2809 -- notify UNEXPECTED error
2810 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2811 return l_return_status;
2812 END create_version;
2813
2814 --This Function is called from Versioning API OKC_VERSION_PVT
2815 --Old Location:OKCRVERB.pls
2816 --New Location:Base Table API
2817
2818 FUNCTION restore_version(
2819 p_chr_id IN NUMBER,
2820 p_major_version IN NUMBER
2821 ) RETURN VARCHAR2 IS
2822
2823 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2824
2825 BEGIN
2826 INSERT INTO okc_outcome_arguments
2827 (
2828 id,
2829 oce_id,
2830 pdp_id,
2831 aae_id,
2832 dnz_chr_id,
2833 object_version_number,
2834 created_by,
2835 creation_date,
2836 last_updated_by,
2837 last_update_date,
2838 value,
2839 last_update_login,
2840 application_id,
2841 seeded_flag
2842 )
2843 SELECT
2844 id,
2845 oce_id,
2846 pdp_id,
2847 aae_id,
2848 dnz_chr_id,
2849 object_version_number,
2850 created_by,
2851 creation_date,
2852 last_updated_by,
2853 last_update_date,
2854 value,
2855 last_update_login,
2856 application_id,
2857 seeded_flag
2858 FROM okc_outcome_arguments_h
2859 WHERE dnz_chr_id = p_chr_id
2860 AND major_version = p_major_version;
2861
2862 RETURN l_return_status;
2863 EXCEPTION
2864 -- other appropriate handlers
2865 WHEN OTHERS THEN
2866 -- store SQL error message on message stack
2867 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2868 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2869 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2870 p_token1_value => sqlcode,
2871 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2872 p_token2_value => sqlerrm);
2873
2874 -- notify UNEXPECTED error
2875 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2876 return l_return_status;
2877 END restore_version;
2878 --
2879 END OKC_OAT_PVT;