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