[Home] [Help]
PACKAGE BODY: APPS.OKC_AAV_PVT
Source
1 PACKAGE BODY OKC_AAV_PVT AS
2 /* $Header: OKCSAAVB.pls 120.0 2005/05/25 22:37:45 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_ACTION_ATT_VALS
39 ---------------------------------------------------------------------------
40 FUNCTION get_rec (
41 p_aav_rec IN aav_rec_type,
42 x_no_data_found OUT NOCOPY BOOLEAN
43 ) RETURN aav_rec_type IS
44 CURSOR aav_pk_csr (p_aae_id IN NUMBER,
45 p_coe_id IN NUMBER) IS
46 SELECT
47 AAE_ID,
48 COE_ID,
49 VALUE,
50 OBJECT_VERSION_NUMBER,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATE_LOGIN
56 FROM Okc_Action_Att_Vals
57 WHERE okc_action_att_vals.aae_id = p_aae_id
58 AND okc_action_att_vals.coe_id = p_coe_id;
59 l_aav_pk aav_pk_csr%ROWTYPE;
60 l_aav_rec aav_rec_type;
61 BEGIN
62 x_no_data_found := TRUE;
63 -- Get current database values
64 OPEN aav_pk_csr (p_aav_rec.aae_id,
65 p_aav_rec.coe_id);
66 FETCH aav_pk_csr INTO
67 l_aav_rec.AAE_ID,
68 l_aav_rec.COE_ID,
69 l_aav_rec.VALUE,
70 l_aav_rec.OBJECT_VERSION_NUMBER,
71 l_aav_rec.CREATED_BY,
72 l_aav_rec.CREATION_DATE,
73 l_aav_rec.LAST_UPDATED_BY,
74 l_aav_rec.LAST_UPDATE_DATE,
75 l_aav_rec.LAST_UPDATE_LOGIN;
76 x_no_data_found := aav_pk_csr%NOTFOUND;
77 CLOSE aav_pk_csr;
78 RETURN(l_aav_rec);
79 END get_rec;
80
81 FUNCTION get_rec (
82 p_aav_rec IN aav_rec_type
83 ) RETURN aav_rec_type IS
84 l_row_notfound BOOLEAN := TRUE;
85 BEGIN
86 RETURN(get_rec(p_aav_rec, l_row_notfound));
87 END get_rec;
88 ---------------------------------------------------------------------------
89 -- FUNCTION get_rec for: OKC_ACTION_ATT_VALS_V
90 ---------------------------------------------------------------------------
91 FUNCTION get_rec (
92 p_aavv_rec IN aavv_rec_type,
93 x_no_data_found OUT NOCOPY BOOLEAN
94 ) RETURN aavv_rec_type IS
95 CURSOR okc_aavv_pk_csr (p_aae_id IN NUMBER,
96 p_coe_id IN NUMBER) IS
97 SELECT
98 AAE_ID,
99 COE_ID,
100 OBJECT_VERSION_NUMBER,
101 VALUE,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN
107 FROM Okc_Action_Att_Vals_V
108 WHERE okc_action_att_vals_v.aae_id = p_aae_id
109 AND okc_action_att_vals_v.coe_id = p_coe_id;
110 l_okc_aavv_pk okc_aavv_pk_csr%ROWTYPE;
111 l_aavv_rec aavv_rec_type;
112 BEGIN
113 x_no_data_found := TRUE;
114 -- Get current database values
115 OPEN okc_aavv_pk_csr (p_aavv_rec.aae_id,
116 p_aavv_rec.coe_id);
117 FETCH okc_aavv_pk_csr INTO
118 l_aavv_rec.AAE_ID,
119 l_aavv_rec.COE_ID,
120 l_aavv_rec.OBJECT_VERSION_NUMBER,
121 l_aavv_rec.VALUE,
122 l_aavv_rec.CREATED_BY,
123 l_aavv_rec.CREATION_DATE,
124 l_aavv_rec.LAST_UPDATED_BY,
125 l_aavv_rec.LAST_UPDATE_DATE,
126 l_aavv_rec.LAST_UPDATE_LOGIN;
127 x_no_data_found := okc_aavv_pk_csr%NOTFOUND;
128 CLOSE okc_aavv_pk_csr;
129 RETURN(l_aavv_rec);
130 END get_rec;
131
132 FUNCTION get_rec (
133 p_aavv_rec IN aavv_rec_type
134 ) RETURN aavv_rec_type IS
135 l_row_notfound BOOLEAN := TRUE;
139
136 BEGIN
137 RETURN(get_rec(p_aavv_rec, l_row_notfound));
138 END get_rec;
140 -----------------------------------------------------------
141 -- FUNCTION null_out_defaults for: OKC_ACTION_ATT_VALS_V --
142 -----------------------------------------------------------
143 FUNCTION null_out_defaults (
144 p_aavv_rec IN aavv_rec_type
145 ) RETURN aavv_rec_type IS
146 l_aavv_rec aavv_rec_type := p_aavv_rec;
147 BEGIN
148 IF (l_aavv_rec.aae_id = OKC_API.G_MISS_NUM) THEN
149 l_aavv_rec.aae_id := NULL;
150 END IF;
151 IF (l_aavv_rec.coe_id = OKC_API.G_MISS_NUM) THEN
152 l_aavv_rec.coe_id := NULL;
153 END IF;
154 IF (l_aavv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
155 l_aavv_rec.object_version_number := NULL;
156 END IF;
157 IF (l_aavv_rec.value = OKC_API.G_MISS_CHAR) THEN
158 l_aavv_rec.value := NULL;
159 END IF;
160 IF (l_aavv_rec.created_by = OKC_API.G_MISS_NUM) THEN
161 l_aavv_rec.created_by := NULL;
162 END IF;
163 IF (l_aavv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
164 l_aavv_rec.creation_date := NULL;
165 END IF;
166 IF (l_aavv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
167 l_aavv_rec.last_updated_by := NULL;
168 END IF;
169 IF (l_aavv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
170 l_aavv_rec.last_update_date := NULL;
171 END IF;
172 IF (l_aavv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
173 l_aavv_rec.last_update_login := NULL;
174 END IF;
175 RETURN(l_aavv_rec);
176 END null_out_defaults;
177 ---------------------------------------------------------------------------
178 -- PROCEDURE Validate_Attributes
179 ---------------------------------------------------------------------------
180 ---------------------------------------------------
181 -- Validate_Attributes for:OKC_ACTION_ATT_VALS_V --
182 ---------------------------------------------------
183 /* commenting out nocopy generated code in favor of hand written code
184 FUNCTION Validate_Attributes (
185 p_aavv_rec IN aavv_rec_type
186 ) RETURN VARCHAR2 IS
187 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
188 BEGIN
189 IF p_aavv_rec.aae_id = OKC_API.G_MISS_NUM OR
190 p_aavv_rec.aae_id IS NULL
191 THEN
192 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'aae_id');
193 l_return_status := OKC_API.G_RET_STS_ERROR;
194 ELSIF p_aavv_rec.coe_id = OKC_API.G_MISS_NUM OR
195 p_aavv_rec.coe_id IS NULL
196 THEN
197 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'coe_id');
198 l_return_status := OKC_API.G_RET_STS_ERROR;
199 ELSIF p_aavv_rec.object_version_number = OKC_API.G_MISS_NUM OR
200 p_aavv_rec.object_version_number IS NULL
201 THEN
202 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
203 l_return_status := OKC_API.G_RET_STS_ERROR;
204 ELSIF p_aavv_rec.value = OKC_API.G_MISS_CHAR OR
205 p_aavv_rec.value IS NULL
206 THEN
207 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'value');
208 l_return_status := OKC_API.G_RET_STS_ERROR;
209 END IF;
210 RETURN(l_return_status);
211 END Validate_Attributes;
212 */
213
214 /********************* HAND-CODED ****************************************/
215
216 ---------------------------------------------------------------------------
217 -- PROCEDURE Validate_Object_Version_Number
218 ---------------------------------------------------------------------------
219 -- Start of comments
220 --
221 -- Procedure Name : Validate_Object_Version_Number
222 -- Description :
223 -- Business Rules :
224 -- Parameters :
225 -- Version : 1.0
226 -- End of comments
227 ---------------------------------------------------------------------------
228 PROCEDURE Validate_Object_Version_Number(x_return_status OUT NOCOPY VARCHAR2
229 ,p_aavv_rec IN aavv_rec_type)
230 IS
231
232 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
233
234 BEGIN
235 -- initialize return status
236 x_return_status := OKC_API.G_RET_STS_SUCCESS;
237 -- check for data before processing
238 IF (p_aavv_rec.object_version_number IS NULL) OR
239 (p_aavv_rec.object_version_Number = OKC_API.G_MISS_NUM) THEN
240 OKC_API.SET_MESSAGE(p_app_name => g_app_name
241 ,p_msg_name => g_required_value
242 ,p_token1 => g_col_name_token
243 ,p_token1_value => 'object_version_number');
244 x_return_status := OKC_API.G_RET_STS_ERROR;
245 RAISE G_EXCEPTION_HALT_VALIDATION;
246 END IF;
247
248 EXCEPTION
249 WHEN G_EXCEPTION_HALT_VALIDATION THEN
250 -- no processing necessary; validation can continue
251 -- with the next column
252 NULL;
253
254 WHEN OTHERS THEN
255 -- store SQL error message on message stack for caller
256 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
257 p_msg_name => g_unexpected_error,
258 p_token1 => g_sqlcode_token,
259 p_token1_value => sqlcode,
260 p_token2 => g_sqlerrm_token,
261 p_token2_value => sqlerrm);
262
263 -- notify caller of an UNEXPECTED error
264 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
265
266 END Validate_Object_Version_Number;
267
268 ---------------------------------------------------------------------------
272 --
269 -- PROCEDURE Validate_Aae_Id
270 ---------------------------------------------------------------------------
271 -- Start of comments
273 -- Procedure Name : Validate_Aae_Id
274 -- Description :
275 -- Business Rules :
276 -- Parameters :
277 -- Version : 1.0
278 -- End of comments
279 ---------------------------------------------------------------------------
280 PROCEDURE Validate_Aae_Id(x_return_status OUT NOCOPY VARCHAR2
281 ,p_aavv_rec IN aavv_rec_type)
282 IS
283
284 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
285
286 BEGIN
287 -- initialize return status
288 x_return_status := OKC_API.G_RET_STS_SUCCESS;
289 -- check for data before processing
290 IF (p_aavv_rec.aae_id IS NULL) OR
291 (p_aavv_rec.aae_id = OKC_API.G_MISS_NUM) THEN
292 OKC_API.SET_MESSAGE(p_app_name => g_app_name
293 ,p_msg_name => g_required_value
294 ,p_token1 => g_col_name_token
295 ,p_token1_value => 'aae_id');
296 x_return_status := OKC_API.G_RET_STS_ERROR;
297 RAISE G_EXCEPTION_HALT_VALIDATION;
298 END IF;
299
300 EXCEPTION
301 WHEN G_EXCEPTION_HALT_VALIDATION THEN
302 -- no processing necessary; validation can continue
303 -- with the next column
304 NULL;
305
306 WHEN OTHERS THEN
307 -- store SQL error message on message stack for caller
308 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
309 p_msg_name => g_unexpected_error,
310 p_token1 => g_sqlcode_token,
311 p_token1_value => sqlcode,
312 p_token2 => g_sqlerrm_token,
313 p_token2_value => sqlerrm);
314
315 -- notify caller of an UNEXPECTED error
316 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
317
318 END Validate_Aae_Id;
319
320 ---------------------------------------------------------------------------
321 -- PROCEDURE Validate_Coe_Id
322 ---------------------------------------------------------------------------
323 -- Start of comments
324 --
325 -- Procedure Name : Validate_Coe_Id
326 -- Description :
327 -- Business Rules :
328 -- Parameters :
329 -- Version : 1.0
330 -- End of comments
331 ---------------------------------------------------------------------------
332 PROCEDURE Validate_Coe_Id(x_return_status OUT NOCOPY VARCHAR2
333 ,p_aavv_rec IN aavv_rec_type)
334 IS
335
336 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
337
338 BEGIN
339 -- initialize return status
340 x_return_status := OKC_API.G_RET_STS_SUCCESS;
341 -- check for data before processing
342 IF (p_aavv_rec.coe_id IS NULL) OR
343 (p_aavv_rec.coe_id = OKC_API.G_MISS_NUM) THEN
344 OKC_API.SET_MESSAGE(p_app_name => g_app_name
345 ,p_msg_name => g_required_value
346 ,p_token1 => g_col_name_token
347 ,p_token1_value => 'coe_id');
348 x_return_status := OKC_API.G_RET_STS_ERROR;
349 RAISE G_EXCEPTION_HALT_VALIDATION;
350 END IF;
351
352 EXCEPTION
353 WHEN G_EXCEPTION_HALT_VALIDATION THEN
354 -- no processing necessary; validation can continue
355 -- with the next column
356 NULL;
357
358 WHEN OTHERS THEN
359 -- store SQL error message on message stack for caller
360 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
361 p_msg_name => g_unexpected_error,
362 p_token1 => g_sqlcode_token,
363 p_token1_value => sqlcode,
364 p_token2 => g_sqlerrm_token,
365 p_token2_value => sqlerrm);
366
367 -- notify caller of an UNEXPECTED error
368 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
369
370 END Validate_Coe_Id;
371
372 ---------------------------------------------------------------------------
373 PROCEDURE Validate_Unique_Aav_Record(x_return_status OUT NOCOPY VARCHAR2
374 ,p_aavv_rec IN aavv_rec_type)
375 IS
376
377 --l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
378 --l_unq_tbl OKC_UTIL.unq_tbl_type;
379 l_dummy VARCHAR2(1);
380 l_row_found Boolean := False;
381 CURSOR c1(p_aae_id okc_action_att_vals_v.aae_id%TYPE,
382 p_coe_id okc_action_att_vals_v.coe_id%TYPE) is
383 SELECT 1
384 FROM okc_action_att_vals
385 WHERE aae_id = p_aae_id
386 AND coe_id = p_coe_id;
387
388
389 BEGIN
390
391 -- initialize return status
392 x_return_status := OKC_API.G_RET_STS_SUCCESS;
393
394 /*Bug 1636056:The following code commented out nocopy since it was not using bind
395 variables and parsing was taking place.Replaced with explicit cursor
396 as above
397 -- initialize columns of unique concatenated key
398
399 l_unq_tbl(1).p_col_name := 'aae_id';
400 l_unq_tbl(1).p_col_val := p_aavv_rec.aae_id;
401 l_unq_tbl(2).p_col_name := 'coe_id';
402 l_unq_tbl(2).p_col_val := p_aavv_rec.coe_id;
403 -- initialize return status
404 x_return_status := OKC_API.G_RET_STS_SUCCESS;
405
406 -- call check_comp_unique utility
407 OKC_UTIL.check_comp_unique('OKC_ACTION_ATT_VALS_V'
408 ,l_unq_tbl
412 END IF;
409 ,l_return_status);
410 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
411 x_return_status := OKC_API.G_RET_STS_ERROR;
413 */
414 OPEN c1(p_aavv_rec.aae_id,
415 p_aavv_rec.coe_id);
416 FETCH c1 into l_dummy;
417 l_row_found := c1%FOUND;
418 CLOSE c1;
419 IF l_row_found then
420 --OKC_API.set_message(G_APP_NAME,G_UNQS,G_COL_NAME_TOKEN1,'aae_id',G_COL_NAME_TOKEN2,'coe_id');
421 OKC_API.set_message(G_APP_NAME,G_UNQS);
422 x_return_status := OKC_API.G_RET_STS_ERROR;
423 END IF;
424
425 EXCEPTION
426 WHEN G_EXCEPTION_HALT_VALIDATION THEN
427 -- no processing necessary; validation can continue
428 -- with the next column
429 NULL;
430
431 WHEN OTHERS THEN
432 -- store SQL error message on message stack for caller
433 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
434 p_msg_name => g_unexpected_error,
435 p_token1 => g_sqlcode_token,
436 p_token1_value => sqlcode,
437 p_token2 => g_sqlerrm_token,
438 p_token2_value => sqlerrm);
439
440 -- notify caller of an UNEXPECTED error
441 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
442
443 END Validate_Unique_Aav_Record;
444
445 ---------------------------------------------------------------------------
446 -- FUNCTION Validate_Foreign_Keys
447 ---------------------------------------------------------------------------
448 -- Start of comments
449 --
450 -- Function Name : Validate_Foreign_Keys
451 -- Description :
452 -- Business Rules :
453 -- Parameters :
454 -- Version : 1.0
455 -- End of comments
456 ---------------------------------------------------------------------------
457 FUNCTION validate_foreign_keys (
458 p_aavv_rec IN aavv_rec_type
459 ) RETURN VARCHAR2 IS
460 item_not_found_error EXCEPTION;
461 CURSOR okc_aaev_pk_csr (p_id IN NUMBER) IS
462 SELECT '1'
463 FROM Okc_Action_Attributes_V
464 WHERE okc_action_attributes_v.id = p_id;
465
466 l_dummy_var VARCHAR2(1);
467
468 CURSOR okc_coev_pk_csr (p_id IN NUMBER) IS
469 SELECT '1'
470 FROM Okc_Condition_Occurs_V
471 WHERE okc_condition_occurs_v.id = p_id;
472
473 l_dummy VARCHAR2(1);
474
475 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
476 l_row_notfound BOOLEAN := TRUE;
477 BEGIN
478 IF (p_aavv_rec.AAE_ID IS NOT NULL)
479 THEN
480 OPEN okc_aaev_pk_csr(p_aavv_rec.AAE_ID);
481 FETCH okc_aaev_pk_csr INTO l_dummy_var;
482 l_row_notfound := okc_aaev_pk_csr%NOTFOUND;
483 CLOSE okc_aaev_pk_csr;
484 IF (l_row_notfound) THEN
485 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'AAE_ID');
486 RAISE item_not_found_error;
487 END IF;
488 END IF;
489 IF (p_aavv_rec.COE_ID IS NOT NULL)
490 THEN
491 OPEN okc_coev_pk_csr(p_aavv_rec.COE_ID);
492 FETCH okc_coev_pk_csr INTO l_dummy;
493 l_row_notfound := okc_coev_pk_csr%NOTFOUND;
494 CLOSE okc_coev_pk_csr;
495 IF (l_row_notfound) THEN
496 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'COE_ID');
497 RAISE item_not_found_error;
498 END IF;
499 END IF;
500 RETURN (l_return_status);
501 EXCEPTION
502 WHEN item_not_found_error THEN
503 l_return_status := OKC_API.G_RET_STS_ERROR;
504 RETURN (l_return_status);
505 END validate_foreign_keys;
506
507 ---------------------------------------------------------------------------
508 -- FUNCTION Validate_Attributes
509 ---------------------------------------------------------------------------
510 -- Start of comments
511 --
512 -- Procedure Name : Validate_Attributes
513 -- Description :
514 -- Business Rules :
515 -- Parameters :
516 -- Version : 1.0
517 -- End of comments
518 ---------------------------------------------------------------------------
519
520 FUNCTION Validate_Attributes (
521 p_aavv_rec IN aavv_rec_type
522 ) RETURN VARCHAR2 IS
523 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
524 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
525
526 BEGIN
527
528 -- call each column-level validation
529
530 -- Validate Object_Version_Number
531 Validate_Object_Version_Number(x_return_status,p_aavv_rec);
532 -- store the highest degree of error
533 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
534 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
535 -- need to leave
536 l_return_status := x_return_status;
537 RAISE G_EXCEPTION_HALT_VALIDATION;
538 ELSE
539 -- record that there was an error
540 l_return_status := x_return_status;
541 END IF;
542 END IF;
543
544 -- Validate Aae_Id
545 Validate_Aae_Id(x_return_status,p_aavv_rec);
546 -- store the highest degree of error
547 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
548 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
549 -- need to leave
550 l_return_status := x_return_status;
551 RAISE G_EXCEPTION_HALT_VALIDATION;
552 ELSE
553 -- record that there was an error
557
554 l_return_status := x_return_status;
555 END IF;
556 END IF;
558 -- Validate Coe_Id
559 Validate_Coe_Id(x_return_status,p_aavv_rec);
560 -- store the highest degree of error
561 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
562 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
563 -- need to leave
564 l_return_status := x_return_status;
565 RAISE G_EXCEPTION_HALT_VALIDATION;
566 ELSE
567 -- record that there was an error
568 l_return_status := x_return_status;
569 END IF;
570 END IF;
571
572 l_return_status := Validate_Foreign_Keys(p_aavv_rec);
573 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
574 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
575 -- need to leave
576 x_return_status := l_return_status;
577 RAISE G_EXCEPTION_HALT_VALIDATION;
578 ELSE
579 -- record that there was an error
580 x_return_status := l_return_status;
581 END IF;
582 END IF;
583 RETURN(l_return_status);
584 EXCEPTION
585 WHEN G_EXCEPTION_HALT_VALIDATION THEN
586 -- just come out with return status
587 NULL;
588 RETURN (l_return_status);
589 WHEN OTHERS THEN
590 -- store SQL error message on message stack for caller
591 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
592 p_msg_name => g_unexpected_error,
593 p_token1 => g_sqlcode_token,
594 p_token1_value => sqlcode,
595 p_token2 => g_sqlerrm_token,
596 p_token2_value => sqlerrm);
597 -- notify caller of an UNEXPECTED error
598 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
599 RETURN(l_return_status);
600
601 END Validate_Attributes;
602
603 ---------------------------------------------------------------------------
604 -- PROCEDURE Validate_Record
605 ---------------------------------------------------------------------------
606 -----------------------------------------------
607 -- Validate_Record for:OKC_ACTION_ATT_VALS_V --
608 -----------------------------------------------
609 /* commenting out nocopy generated code in favor of hand written code
610 FUNCTION Validate_Record (
611 p_aavv_rec IN aavv_rec_type
612 ) RETURN VARCHAR2 IS
613 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
614 ------------------------------------
615 -- FUNCTION validate_foreign_keys --
616 ------------------------------------
617 FUNCTION validate_foreign_keys (
618 p_aavv_rec IN aavv_rec_type
619 ) RETURN VARCHAR2 IS
620 item_not_found_error EXCEPTION;
621 CURSOR okc_aaev_pk_csr (p_id IN NUMBER) IS
622 SELECT
623 ID,
624 AAL_ID,
625 OBJECT_VERSION_NUMBER,
626 SFWT_FLAG,
627 ACN_ID,
628 ELEMENT_NAME,
629 NAME,
630 DESCRIPTION,
631 DATA_TYPE,
632 LIST_YN,
633 VISIBLE_YN,
634 DATE_OF_INTEREST_YN,
635 FORMAT_MASK,
636 MINIMUM_VALUE,
637 MAXIMUM_VALUE,
638 ATTRIBUTE_CATEGORY,
639 ATTRIBUTE1,
640 ATTRIBUTE2,
641 ATTRIBUTE3,
642 ATTRIBUTE4,
643 ATTRIBUTE5,
644 ATTRIBUTE6,
645 ATTRIBUTE7,
646 ATTRIBUTE8,
647 ATTRIBUTE9,
648 ATTRIBUTE10,
649 ATTRIBUTE11,
650 ATTRIBUTE12,
651 ATTRIBUTE13,
652 ATTRIBUTE14,
653 ATTRIBUTE15,
654 CREATED_BY,
655 CREATION_DATE,
656 LAST_UPDATED_BY,
657 LAST_UPDATE_DATE,
658 LAST_UPDATE_LOGIN
659 FROM Okc_Action_Attributes_V
660 WHERE okc_action_attributes_v.id = p_id;
661 l_okc_aaev_pk okc_aaev_pk_csr%ROWTYPE;
662 CURSOR okc_coev_pk_csr (p_id IN NUMBER) IS
663 SELECT
664 ID,
665 CNH_ID,
666 OBJECT_VERSION_NUMBER,
667 DATETIME,
668 CREATED_BY,
669 CREATION_DATE,
670 LAST_UPDATED_BY,
671 LAST_UPDATE_DATE,
672 LAST_UPDATE_LOGIN
673 FROM Okc_Condition_Occurs_V
674 WHERE okc_condition_occurs_v.id = p_id;
675 l_okc_coev_pk okc_coev_pk_csr%ROWTYPE;
676 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
677 l_row_notfound BOOLEAN := TRUE;
678 BEGIN
679 IF (p_aavv_rec.AAE_ID IS NOT NULL)
680 THEN
681 OPEN okc_aaev_pk_csr(p_aavv_rec.AAE_ID);
682 FETCH okc_aaev_pk_csr INTO l_okc_aaev_pk;
683 l_row_notfound := okc_aaev_pk_csr%NOTFOUND;
684 CLOSE okc_aaev_pk_csr;
685 IF (l_row_notfound) THEN
686 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'AAE_ID');
687 RAISE item_not_found_error;
688 END IF;
689 END IF;
690 IF (p_aavv_rec.COE_ID IS NOT NULL)
691 THEN
692 OPEN okc_coev_pk_csr(p_aavv_rec.COE_ID);
693 FETCH okc_coev_pk_csr INTO l_okc_coev_pk;
694 l_row_notfound := okc_coev_pk_csr%NOTFOUND;
695 CLOSE okc_coev_pk_csr;
696 IF (l_row_notfound) THEN
697 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'COE_ID');
698 RAISE item_not_found_error;
699 END IF;
700 END IF;
701 RETURN (l_return_status);
702 EXCEPTION
703 WHEN item_not_found_error THEN
704 l_return_status := OKC_API.G_RET_STS_ERROR;
705 RETURN (l_return_status);
706 END validate_foreign_keys;
707 BEGIN
708 l_return_status := validate_foreign_keys (p_aavv_rec);
709 RETURN (l_return_status);
710 END Validate_Record;
711 */
712
713 FUNCTION Validate_Record (
714 p_aavv_rec IN aavv_rec_type
715 ) RETURN VARCHAR2 IS
716 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
717 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
718 BEGIN
719 /******** Commented out nocopy , called in Insert Row ***********
720 -- Validate_Unique_Aav_Record;
721 Validate_Unique_Aav_Record(x_return_status,p_aavv_rec);
722 -- store the highest degree of error
723 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
724 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
725 -- need to leave
726 l_return_status := x_return_status;
727 RAISE G_EXCEPTION_HALT_VALIDATION;
728 ELSE
729 -- record that there was an error
730 l_return_status := x_return_status;
731 END IF;
732 END IF;
733 */
734
735 RETURN (l_return_status);
736 END Validate_Record;
737
738 ---------------------------------------------------------------------------
739 -- PROCEDURE Migrate
740 ---------------------------------------------------------------------------
741 PROCEDURE migrate (
742 p_from IN aavv_rec_type,
743 p_to OUT NOCOPY aav_rec_type
744 ) IS
745 BEGIN
746 p_to.aae_id := p_from.aae_id;
747 p_to.coe_id := p_from.coe_id;
748 p_to.value := p_from.value;
749 p_to.object_version_number := p_from.object_version_number;
750 p_to.created_by := p_from.created_by;
751 p_to.creation_date := p_from.creation_date;
752 p_to.last_updated_by := p_from.last_updated_by;
753 p_to.last_update_date := p_from.last_update_date;
754 p_to.last_update_login := p_from.last_update_login;
755 END migrate;
756 --OUT parameter chamged to IN OUT by Rajesh
757 PROCEDURE migrate (
758 p_from IN aav_rec_type,
759 p_to IN OUT NOCOPY aavv_rec_type
760 ) IS
761 BEGIN
762 p_to.aae_id := p_from.aae_id;
763 p_to.coe_id := p_from.coe_id;
764 p_to.value := p_from.value;
765 p_to.object_version_number := p_from.object_version_number;
766 p_to.created_by := p_from.created_by;
767 p_to.creation_date := p_from.creation_date;
768 p_to.last_updated_by := p_from.last_updated_by;
769 p_to.last_update_date := p_from.last_update_date;
770 p_to.last_update_login := p_from.last_update_login;
771 END migrate;
772
773 ---------------------------------------------------------------------------
774 -- PROCEDURE validate_row
775 ---------------------------------------------------------------------------
776 --------------------------------------------
777 -- validate_row for:OKC_ACTION_ATT_VALS_V --
778 --------------------------------------------
779 PROCEDURE validate_row(
780 p_api_version IN NUMBER,
781 p_init_msg_list IN VARCHAR2 ,
782 x_return_status OUT NOCOPY VARCHAR2,
783 x_msg_count OUT NOCOPY NUMBER,
784 x_msg_data OUT NOCOPY VARCHAR2,
785 p_aavv_rec IN aavv_rec_type) IS
786
787 l_api_version CONSTANT NUMBER := 1;
788 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
789 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
790 l_aavv_rec aavv_rec_type := p_aavv_rec;
791 l_aav_rec aav_rec_type;
792 BEGIN
793 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
794 G_PKG_NAME,
795 p_init_msg_list,
796 l_api_version,
797 p_api_version,
798 '_PVT',
799 x_return_status);
800 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
801 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
802 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
803 RAISE OKC_API.G_EXCEPTION_ERROR;
804 END IF;
805 --- Validate all non-missing attributes (Item Level Validation)
806 l_return_status := Validate_Attributes(l_aavv_rec);
807 --- If any errors happen abort API
808 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
809 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
810 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
811 RAISE OKC_API.G_EXCEPTION_ERROR;
812 END IF;
813 l_return_status := Validate_Record(l_aavv_rec);
814 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
815 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
817 RAISE OKC_API.G_EXCEPTION_ERROR;
818 END IF;
819 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
820 EXCEPTION
821 WHEN OKC_API.G_EXCEPTION_ERROR THEN
822 x_return_status := OKC_API.HANDLE_EXCEPTIONS
823 (
824 l_api_name,
825 G_PKG_NAME,
826 'OKC_API.G_RET_STS_ERROR',
827 x_msg_count,
828 x_msg_data,
829 '_PVT'
830 );
831 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
832 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
833 (
834 l_api_name,
835 G_PKG_NAME,
836 'OKC_API.G_RET_STS_UNEXP_ERROR',
837 x_msg_count,
838 x_msg_data,
839 '_PVT'
840 );
841 WHEN OTHERS THEN
842 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
843 (
844 l_api_name,
845 G_PKG_NAME,
846 'OTHERS',
847 x_msg_count,
848 x_msg_data,
849 '_PVT'
850 );
851 END validate_row;
852 ------------------------------------------
853 -- PL/SQL TBL validate_row for:AAVV_TBL --
854 ------------------------------------------
855 PROCEDURE validate_row(
856 p_api_version IN NUMBER,
857 p_init_msg_list IN VARCHAR2 ,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_msg_count OUT NOCOPY NUMBER,
860 x_msg_data OUT NOCOPY VARCHAR2,
861 p_aavv_tbl IN aavv_tbl_type) IS
862
863 l_api_version CONSTANT NUMBER := 1;
864 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
865 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
866 i NUMBER := 0;
867 BEGIN
868 OKC_API.init_msg_list(p_init_msg_list);
869 -- Make sure PL/SQL table has records in it before passing
870 IF (p_aavv_tbl.COUNT > 0) THEN
871 i := p_aavv_tbl.FIRST;
872 LOOP
873 validate_row (
874 p_api_version => p_api_version,
875 p_init_msg_list => OKC_API.G_FALSE,
876 x_return_status => x_return_status,
877 x_msg_count => x_msg_count,
878 x_msg_data => x_msg_data,
879 p_aavv_rec => p_aavv_tbl(i));
880 EXIT WHEN (i = p_aavv_tbl.LAST);
881 i := p_aavv_tbl.NEXT(i);
882 END LOOP;
883 END IF;
884 EXCEPTION
885 WHEN OKC_API.G_EXCEPTION_ERROR THEN
886 x_return_status := OKC_API.HANDLE_EXCEPTIONS
887 (
888 l_api_name,
889 G_PKG_NAME,
890 'OKC_API.G_RET_STS_ERROR',
891 x_msg_count,
892 x_msg_data,
893 '_PVT'
894 );
895 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
896 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
897 (
898 l_api_name,
899 G_PKG_NAME,
900 'OKC_API.G_RET_STS_UNEXP_ERROR',
901 x_msg_count,
902 x_msg_data,
903 '_PVT'
904 );
905 WHEN OTHERS THEN
906 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
907 (
908 l_api_name,
909 G_PKG_NAME,
910 'OTHERS',
911 x_msg_count,
912 x_msg_data,
913 '_PVT'
914 );
915 END validate_row;
916
917 ---------------------------------------------------------------------------
918 -- PROCEDURE insert_row
919 ---------------------------------------------------------------------------
920 ----------------------------------------
921 -- insert_row for:OKC_ACTION_ATT_VALS --
922 ----------------------------------------
923 PROCEDURE insert_row(
924 p_init_msg_list IN VARCHAR2 ,
925 x_return_status OUT NOCOPY VARCHAR2,
926 x_msg_count OUT NOCOPY NUMBER,
927 x_msg_data OUT NOCOPY VARCHAR2,
928 p_aav_rec IN aav_rec_type,
929 x_aav_rec OUT NOCOPY aav_rec_type) IS
930
931 l_api_version CONSTANT NUMBER := 1;
932 l_api_name CONSTANT VARCHAR2(30) := 'VALS_insert_row';
933 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
934 l_aav_rec aav_rec_type := p_aav_rec;
935 l_def_aav_rec aav_rec_type;
936 --------------------------------------------
937 -- Set_Attributes for:OKC_ACTION_ATT_VALS --
938 --------------------------------------------
939 FUNCTION Set_Attributes (
940 p_aav_rec IN aav_rec_type,
941 x_aav_rec OUT NOCOPY aav_rec_type
942 ) RETURN VARCHAR2 IS
943 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
944 BEGIN
945 x_aav_rec := p_aav_rec;
946 RETURN(l_return_status);
947 END Set_Attributes;
948 BEGIN
949 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
950 p_init_msg_list,
951 '_PVT',
952 x_return_status);
953 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
954 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
955 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
956 RAISE OKC_API.G_EXCEPTION_ERROR;
957 END IF;
958 --- Setting item attributes
959 l_return_status := Set_Attributes(
960 p_aav_rec, -- IN
961 l_aav_rec); -- OUT
962 --- If any errors happen abort API
963 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
964 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
965 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
966 RAISE OKC_API.G_EXCEPTION_ERROR;
967 END IF;
968 INSERT INTO OKC_ACTION_ATT_VALS(
969 aae_id,
970 coe_id,
971 value,
972 object_version_number,
973 created_by,
974 creation_date,
975 last_updated_by,
976 last_update_date,
977 last_update_login)
978 VALUES (
979 l_aav_rec.aae_id,
980 l_aav_rec.coe_id,
981 l_aav_rec.value,
982 l_aav_rec.object_version_number,
983 l_aav_rec.created_by,
984 l_aav_rec.creation_date,
985 l_aav_rec.last_updated_by,
986 l_aav_rec.last_update_date,
987 l_aav_rec.last_update_login);
988 -- Set OUT values
989 x_aav_rec := l_aav_rec;
990 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
991 EXCEPTION
992 WHEN OKC_API.G_EXCEPTION_ERROR THEN
993 x_return_status := OKC_API.HANDLE_EXCEPTIONS
994 (
995 l_api_name,
996 G_PKG_NAME,
997 'OKC_API.G_RET_STS_ERROR',
998 x_msg_count,
999 x_msg_data,
1000 '_PVT'
1001 );
1002 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1003 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1004 (
1005 l_api_name,
1006 G_PKG_NAME,
1007 'OKC_API.G_RET_STS_UNEXP_ERROR',
1008 x_msg_count,
1009 x_msg_data,
1010 '_PVT'
1011 );
1012 WHEN OTHERS THEN
1013 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1014 (
1015 l_api_name,
1016 G_PKG_NAME,
1017 'OTHERS',
1018 x_msg_count,
1019 x_msg_data,
1020 '_PVT'
1021 );
1022 END insert_row;
1023 ------------------------------------------
1024 -- insert_row for:OKC_ACTION_ATT_VALS_V --
1025 ------------------------------------------
1026 PROCEDURE insert_row(
1027 p_api_version IN NUMBER,
1028 p_init_msg_list IN VARCHAR2 ,
1029 x_return_status OUT NOCOPY VARCHAR2,
1030 x_msg_count OUT NOCOPY NUMBER,
1031 x_msg_data OUT NOCOPY VARCHAR2,
1032 p_aavv_rec IN aavv_rec_type,
1033 x_aavv_rec OUT NOCOPY aavv_rec_type) IS
1034
1035 l_api_version CONSTANT NUMBER := 1;
1036 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1037 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1038 l_aavv_rec aavv_rec_type;
1039 l_def_aavv_rec aavv_rec_type;
1040 l_aav_rec aav_rec_type;
1041 lx_aav_rec aav_rec_type;
1042 -------------------------------
1043 -- FUNCTION fill_who_columns --
1044 -------------------------------
1045 FUNCTION fill_who_columns (
1046 p_aavv_rec IN aavv_rec_type
1047 ) RETURN aavv_rec_type IS
1048 l_aavv_rec aavv_rec_type := p_aavv_rec;
1049 BEGIN
1050 l_aavv_rec.CREATION_DATE := SYSDATE;
1051 l_aavv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1052 l_aavv_rec.LAST_UPDATE_DATE := l_aavv_rec.CREATION_DATE;
1053 l_aavv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1054 l_aavv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1055 RETURN(l_aavv_rec);
1056 END fill_who_columns;
1057 ----------------------------------------------
1058 -- Set_Attributes for:OKC_ACTION_ATT_VALS_V --
1059 ----------------------------------------------
1063 ) RETURN VARCHAR2 IS
1060 FUNCTION Set_Attributes (
1061 p_aavv_rec IN aavv_rec_type,
1062 x_aavv_rec OUT NOCOPY aavv_rec_type
1064 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1065 BEGIN
1066 x_aavv_rec := p_aavv_rec;
1067 x_aavv_rec.OBJECT_VERSION_NUMBER := 1;
1068 RETURN(l_return_status);
1069 END Set_Attributes;
1070 BEGIN
1071 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1072 G_PKG_NAME,
1073 p_init_msg_list,
1074 l_api_version,
1075 p_api_version,
1076 '_PVT',
1077 x_return_status);
1078 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1079 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1080 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1081 RAISE OKC_API.G_EXCEPTION_ERROR;
1082 END IF;
1083 l_aavv_rec := null_out_defaults(p_aavv_rec);
1084 --- Setting item attributes
1085 l_return_status := Set_Attributes(
1086 l_aavv_rec, -- IN
1087 l_def_aavv_rec); -- OUT
1088 --- If any errors happen abort API
1089 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1090 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1091 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1092 RAISE OKC_API.G_EXCEPTION_ERROR;
1093 END IF;
1094 l_def_aavv_rec := fill_who_columns(l_def_aavv_rec);
1095 --- Validate all non-missing attributes (Item Level Validation)
1096 l_return_status := Validate_Attributes(l_def_aavv_rec);
1097 --- If any errors happen abort API
1098 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1099 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1100 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1101 RAISE OKC_API.G_EXCEPTION_ERROR;
1102 END IF;
1103 l_return_status := Validate_Record(l_def_aavv_rec);
1104 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1105 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1106 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1107 RAISE OKC_API.G_EXCEPTION_ERROR;
1108 END IF;
1109
1110 /************ ADDED TO CHECK THE UNIQUENESS ****************/
1111
1112 -- Validate_Unique_Aav_Record;
1113 Validate_Unique_Aav_Record(x_return_status,p_aavv_rec);
1114 -- store the highest degree of error
1115 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1116 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1117 -- need to leave
1118 l_return_status := x_return_status;
1119 RAISE G_EXCEPTION_HALT_VALIDATION;
1120 ELSE
1121 -- record that there was an error
1122 l_return_status := x_return_status;
1123 END IF;
1124 END IF;
1125
1126 /************ ADDED TO CHECK THE UNIQUENESS ****************/
1127
1128
1129 --------------------------------------
1130 -- Move VIEW record to "Child" records
1131 --------------------------------------
1132 migrate(l_def_aavv_rec, l_aav_rec);
1133 --------------------------------------------
1134 -- Call the INSERT_ROW for each child record
1135 --------------------------------------------
1136 insert_row(
1137 p_init_msg_list,
1138 x_return_status,
1139 x_msg_count,
1140 x_msg_data,
1141 l_aav_rec,
1142 lx_aav_rec
1143 );
1144 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1145 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1146 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1147 RAISE OKC_API.G_EXCEPTION_ERROR;
1148 END IF;
1149 migrate(lx_aav_rec, l_def_aavv_rec);
1150 -- Set OUT values
1151 x_aavv_rec := l_def_aavv_rec;
1152 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1153 EXCEPTION
1154 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1155 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1156 (
1157 l_api_name,
1158 G_PKG_NAME,
1159 'OKC_API.G_RET_STS_ERROR',
1160 x_msg_count,
1161 x_msg_data,
1162 '_PVT'
1163 );
1164 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1165 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1166 (
1167 l_api_name,
1168 G_PKG_NAME,
1169 'OKC_API.G_RET_STS_UNEXP_ERROR',
1170 x_msg_count,
1171 x_msg_data,
1172 '_PVT'
1173 );
1174 WHEN OTHERS THEN
1175 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1176 (
1177 l_api_name,
1178 G_PKG_NAME,
1179 'OTHERS',
1180 x_msg_count,
1181 x_msg_data,
1182 '_PVT'
1183 );
1184 END insert_row;
1185 ----------------------------------------
1186 -- PL/SQL TBL insert_row for:AAVV_TBL --
1187 ----------------------------------------
1188 PROCEDURE insert_row(
1189 p_api_version IN NUMBER,
1190 p_init_msg_list IN VARCHAR2 ,
1191 x_return_status OUT NOCOPY VARCHAR2,
1192 x_msg_count OUT NOCOPY NUMBER,
1193 x_msg_data OUT NOCOPY VARCHAR2,
1194 p_aavv_tbl IN aavv_tbl_type,
1195 x_aavv_tbl OUT NOCOPY aavv_tbl_type) IS
1196
1197 l_api_version CONSTANT NUMBER := 1;
1198 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1199 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1203 -- Make sure PL/SQL table has records in it before passing
1200 i NUMBER := 0;
1201 BEGIN
1202 OKC_API.init_msg_list(p_init_msg_list);
1204 IF (p_aavv_tbl.COUNT > 0) THEN
1205 i := p_aavv_tbl.FIRST;
1206 LOOP
1207 insert_row (
1208 p_api_version => p_api_version,
1209 p_init_msg_list => OKC_API.G_FALSE,
1210 x_return_status => x_return_status,
1211 x_msg_count => x_msg_count,
1212 x_msg_data => x_msg_data,
1213 p_aavv_rec => p_aavv_tbl(i),
1214 x_aavv_rec => x_aavv_tbl(i));
1215 EXIT WHEN (i = p_aavv_tbl.LAST);
1216 i := p_aavv_tbl.NEXT(i);
1217 END LOOP;
1218 END IF;
1219 EXCEPTION
1220 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1221 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1222 (
1223 l_api_name,
1224 G_PKG_NAME,
1225 'OKC_API.G_RET_STS_ERROR',
1226 x_msg_count,
1227 x_msg_data,
1228 '_PVT'
1229 );
1230 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1231 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1232 (
1233 l_api_name,
1234 G_PKG_NAME,
1235 'OKC_API.G_RET_STS_UNEXP_ERROR',
1236 x_msg_count,
1237 x_msg_data,
1238 '_PVT'
1239 );
1240 WHEN OTHERS THEN
1241 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1242 (
1243 l_api_name,
1244 G_PKG_NAME,
1245 'OTHERS',
1246 x_msg_count,
1247 x_msg_data,
1248 '_PVT'
1249 );
1250 END insert_row;
1251
1252 ---------------------------------------------------------------------------
1253 -- PROCEDURE lock_row
1254 ---------------------------------------------------------------------------
1255 --------------------------------------
1256 -- lock_row for:OKC_ACTION_ATT_VALS --
1257 --------------------------------------
1258 PROCEDURE lock_row(
1259 p_init_msg_list IN VARCHAR2 ,
1260 x_return_status OUT NOCOPY VARCHAR2,
1261 x_msg_count OUT NOCOPY NUMBER,
1262 x_msg_data OUT NOCOPY VARCHAR2,
1263 p_aav_rec IN aav_rec_type) IS
1264
1265 E_Resource_Busy EXCEPTION;
1266 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1267 CURSOR lock_csr (p_aav_rec IN aav_rec_type) IS
1268 SELECT OBJECT_VERSION_NUMBER
1269 FROM OKC_ACTION_ATT_VALS
1270 WHERE AAE_ID = p_aav_rec.aae_id
1271 AND COE_ID = p_aav_rec.coe_id
1272 AND OBJECT_VERSION_NUMBER = p_aav_rec.object_version_number
1273 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1274
1275 CURSOR lchk_csr (p_aav_rec IN aav_rec_type) IS
1276 SELECT OBJECT_VERSION_NUMBER
1277 FROM OKC_ACTION_ATT_VALS
1278 WHERE AAE_ID = p_aav_rec.aae_id
1279 AND COE_ID = p_aav_rec.coe_id;
1280 l_api_version CONSTANT NUMBER := 1;
1281 l_api_name CONSTANT VARCHAR2(30) := 'VALS_lock_row';
1282 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1283 l_object_version_number OKC_ACTION_ATT_VALS.OBJECT_VERSION_NUMBER%TYPE;
1284 lc_object_version_number OKC_ACTION_ATT_VALS.OBJECT_VERSION_NUMBER%TYPE;
1285 l_row_notfound BOOLEAN := FALSE;
1286 lc_row_notfound BOOLEAN := FALSE;
1287 BEGIN
1288 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1289 p_init_msg_list,
1290 '_PVT',
1291 x_return_status);
1292 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1293 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1294 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1295 RAISE OKC_API.G_EXCEPTION_ERROR;
1296 END IF;
1297 BEGIN
1298 OPEN lock_csr(p_aav_rec);
1299 FETCH lock_csr INTO l_object_version_number;
1300 l_row_notfound := lock_csr%NOTFOUND;
1301 CLOSE lock_csr;
1302 EXCEPTION
1303 WHEN E_Resource_Busy THEN
1304 IF (lock_csr%ISOPEN) THEN
1305 CLOSE lock_csr;
1306 END IF;
1307 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1308 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1309 END;
1310
1311 IF ( l_row_notfound ) THEN
1312 OPEN lchk_csr(p_aav_rec);
1313 FETCH lchk_csr INTO lc_object_version_number;
1314 lc_row_notfound := lchk_csr%NOTFOUND;
1315 CLOSE lchk_csr;
1316 END IF;
1317 IF (lc_row_notfound) THEN
1318 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1319 RAISE OKC_API.G_EXCEPTION_ERROR;
1320 ELSIF lc_object_version_number > p_aav_rec.object_version_number THEN
1321 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1322 RAISE OKC_API.G_EXCEPTION_ERROR;
1323 ELSIF lc_object_version_number <> p_aav_rec.object_version_number THEN
1324 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1325 RAISE OKC_API.G_EXCEPTION_ERROR;
1326 ELSIF lc_object_version_number = -1 THEN
1327 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1328 RAISE OKC_API.G_EXCEPTION_ERROR;
1329 END IF;
1330 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1331 EXCEPTION
1332 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1336 G_PKG_NAME,
1333 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1334 (
1335 l_api_name,
1337 'OKC_API.G_RET_STS_ERROR',
1338 x_msg_count,
1339 x_msg_data,
1340 '_PVT'
1341 );
1342 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1343 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1344 (
1345 l_api_name,
1346 G_PKG_NAME,
1347 'OKC_API.G_RET_STS_UNEXP_ERROR',
1348 x_msg_count,
1349 x_msg_data,
1350 '_PVT'
1351 );
1352 WHEN OTHERS THEN
1353 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1354 (
1355 l_api_name,
1356 G_PKG_NAME,
1357 'OTHERS',
1358 x_msg_count,
1359 x_msg_data,
1360 '_PVT'
1361 );
1362 END lock_row;
1363 ----------------------------------------
1364 -- lock_row for:OKC_ACTION_ATT_VALS_V --
1365 ----------------------------------------
1366 PROCEDURE lock_row(
1367 p_api_version IN NUMBER,
1368 p_init_msg_list IN VARCHAR2 ,
1369 x_return_status OUT NOCOPY VARCHAR2,
1370 x_msg_count OUT NOCOPY NUMBER,
1371 x_msg_data OUT NOCOPY VARCHAR2,
1372 p_aavv_rec IN aavv_rec_type) IS
1373
1374 l_api_version CONSTANT NUMBER := 1;
1375 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1376 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1377 l_aav_rec aav_rec_type;
1378 BEGIN
1379 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1380 G_PKG_NAME,
1381 p_init_msg_list,
1382 l_api_version,
1383 p_api_version,
1384 '_PVT',
1385 x_return_status);
1386 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1387 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1388 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1389 RAISE OKC_API.G_EXCEPTION_ERROR;
1390 END IF;
1391 --------------------------------------
1392 -- Move VIEW record to "Child" records
1393 --------------------------------------
1394 migrate(p_aavv_rec, l_aav_rec);
1395 --------------------------------------------
1396 -- Call the LOCK_ROW for each child record
1397 --------------------------------------------
1398 lock_row(
1399 p_init_msg_list,
1400 x_return_status,
1401 x_msg_count,
1402 x_msg_data,
1403 l_aav_rec
1404 );
1405 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1406 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1407 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1408 RAISE OKC_API.G_EXCEPTION_ERROR;
1409 END IF;
1410 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1411 EXCEPTION
1412 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1413 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1414 (
1415 l_api_name,
1416 G_PKG_NAME,
1417 'OKC_API.G_RET_STS_ERROR',
1418 x_msg_count,
1419 x_msg_data,
1420 '_PVT'
1421 );
1422 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1423 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1424 (
1425 l_api_name,
1426 G_PKG_NAME,
1427 'OKC_API.G_RET_STS_UNEXP_ERROR',
1428 x_msg_count,
1429 x_msg_data,
1430 '_PVT'
1431 );
1432 WHEN OTHERS THEN
1433 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1434 (
1435 l_api_name,
1436 G_PKG_NAME,
1437 'OTHERS',
1438 x_msg_count,
1439 x_msg_data,
1440 '_PVT'
1441 );
1442 END lock_row;
1443 --------------------------------------
1444 -- PL/SQL TBL lock_row for:AAVV_TBL --
1445 --------------------------------------
1446 PROCEDURE lock_row(
1447 p_api_version IN NUMBER,
1448 p_init_msg_list IN VARCHAR2 ,
1449 x_return_status OUT NOCOPY VARCHAR2,
1450 x_msg_count OUT NOCOPY NUMBER,
1451 x_msg_data OUT NOCOPY VARCHAR2,
1452 p_aavv_tbl IN aavv_tbl_type) IS
1453
1454 l_api_version CONSTANT NUMBER := 1;
1455 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1456 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1457 i NUMBER := 0;
1458 BEGIN
1459 OKC_API.init_msg_list(p_init_msg_list);
1460 -- Make sure PL/SQL table has records in it before passing
1461 IF (p_aavv_tbl.COUNT > 0) THEN
1462 i := p_aavv_tbl.FIRST;
1463 LOOP
1464 lock_row (
1465 p_api_version => p_api_version,
1466 p_init_msg_list => OKC_API.G_FALSE,
1467 x_return_status => x_return_status,
1468 x_msg_count => x_msg_count,
1469 x_msg_data => x_msg_data,
1470 p_aavv_rec => p_aavv_tbl(i));
1471 EXIT WHEN (i = p_aavv_tbl.LAST);
1472 i := p_aavv_tbl.NEXT(i);
1473 END LOOP;
1474 END IF;
1475 EXCEPTION
1476 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1477 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1478 (
1479 l_api_name,
1480 G_PKG_NAME,
1481 'OKC_API.G_RET_STS_ERROR',
1482 x_msg_count,
1483 x_msg_data,
1484 '_PVT'
1485 );
1486 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1487 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1488 (
1489 l_api_name,
1490 G_PKG_NAME,
1491 'OKC_API.G_RET_STS_UNEXP_ERROR',
1492 x_msg_count,
1493 x_msg_data,
1494 '_PVT'
1495 );
1496 WHEN OTHERS THEN
1497 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1498 (
1499 l_api_name,
1500 G_PKG_NAME,
1501 'OTHERS',
1502 x_msg_count,
1503 x_msg_data,
1504 '_PVT'
1505 );
1506 END lock_row;
1507
1508 ---------------------------------------------------------------------------
1509 -- PROCEDURE update_row
1510 ---------------------------------------------------------------------------
1511 ----------------------------------------
1512 -- update_row for:OKC_ACTION_ATT_VALS --
1513 ----------------------------------------
1514 PROCEDURE update_row(
1515 p_init_msg_list IN VARCHAR2 ,
1516 x_return_status OUT NOCOPY VARCHAR2,
1517 x_msg_count OUT NOCOPY NUMBER,
1518 x_msg_data OUT NOCOPY VARCHAR2,
1519 p_aav_rec IN aav_rec_type,
1520 x_aav_rec OUT NOCOPY aav_rec_type) IS
1521
1522 l_api_version CONSTANT NUMBER := 1;
1523 l_api_name CONSTANT VARCHAR2(30) := 'VALS_update_row';
1524 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1525 l_aav_rec aav_rec_type := p_aav_rec;
1526 l_def_aav_rec aav_rec_type;
1527 l_row_notfound BOOLEAN := TRUE;
1528 ----------------------------------
1529 -- FUNCTION populate_new_record --
1530 ----------------------------------
1531 FUNCTION populate_new_record (
1532 p_aav_rec IN aav_rec_type,
1533 x_aav_rec OUT NOCOPY aav_rec_type
1534 ) RETURN VARCHAR2 IS
1535 l_aav_rec aav_rec_type;
1536 l_row_notfound BOOLEAN := TRUE;
1537 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1538 BEGIN
1539 x_aav_rec := p_aav_rec;
1540 -- Get current database values
1541 l_aav_rec := get_rec(p_aav_rec, l_row_notfound);
1542 IF (l_row_notfound) THEN
1543 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1544 END IF;
1545 IF (x_aav_rec.aae_id = OKC_API.G_MISS_NUM)
1546 THEN
1547 x_aav_rec.aae_id := l_aav_rec.aae_id;
1548 END IF;
1549 IF (x_aav_rec.coe_id = OKC_API.G_MISS_NUM)
1550 THEN
1551 x_aav_rec.coe_id := l_aav_rec.coe_id;
1552 END IF;
1553 IF (x_aav_rec.value = OKC_API.G_MISS_CHAR)
1554 THEN
1555 x_aav_rec.value := l_aav_rec.value;
1556 END IF;
1557 IF (x_aav_rec.object_version_number = OKC_API.G_MISS_NUM)
1558 THEN
1559 x_aav_rec.object_version_number := l_aav_rec.object_version_number;
1560 END IF;
1561 IF (x_aav_rec.created_by = OKC_API.G_MISS_NUM)
1562 THEN
1563 x_aav_rec.created_by := l_aav_rec.created_by;
1564 END IF;
1565 IF (x_aav_rec.creation_date = OKC_API.G_MISS_DATE)
1566 THEN
1567 x_aav_rec.creation_date := l_aav_rec.creation_date;
1568 END IF;
1569 IF (x_aav_rec.last_updated_by = OKC_API.G_MISS_NUM)
1570 THEN
1571 x_aav_rec.last_updated_by := l_aav_rec.last_updated_by;
1572 END IF;
1573 IF (x_aav_rec.last_update_date = OKC_API.G_MISS_DATE)
1574 THEN
1575 x_aav_rec.last_update_date := l_aav_rec.last_update_date;
1576 END IF;
1577 IF (x_aav_rec.last_update_login = OKC_API.G_MISS_NUM)
1578 THEN
1579 x_aav_rec.last_update_login := l_aav_rec.last_update_login;
1580 END IF;
1581 RETURN(l_return_status);
1582 END populate_new_record;
1583 --------------------------------------------
1584 -- Set_Attributes for:OKC_ACTION_ATT_VALS --
1585 --------------------------------------------
1586 FUNCTION Set_Attributes (
1587 p_aav_rec IN aav_rec_type,
1588 x_aav_rec OUT NOCOPY aav_rec_type
1589 ) RETURN VARCHAR2 IS
1590 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1591 BEGIN
1592 x_aav_rec := p_aav_rec;
1593 RETURN(l_return_status);
1594 END Set_Attributes;
1595 BEGIN
1596 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1597 p_init_msg_list,
1598 '_PVT',
1599 x_return_status);
1600 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1601 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1602 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1603 RAISE OKC_API.G_EXCEPTION_ERROR;
1604 END IF;
1605 --- Setting item attributes
1606 l_return_status := Set_Attributes(
1607 p_aav_rec, -- IN
1611 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1608 l_aav_rec); -- OUT
1609 --- If any errors happen abort API
1610 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1612 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1613 RAISE OKC_API.G_EXCEPTION_ERROR;
1614 END IF;
1615 l_return_status := populate_new_record(l_aav_rec, l_def_aav_rec);
1616 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1618 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1619 RAISE OKC_API.G_EXCEPTION_ERROR;
1620 END IF;
1621 UPDATE OKC_ACTION_ATT_VALS
1622 SET VALUE = l_def_aav_rec.value,
1623 OBJECT_VERSION_NUMBER = l_def_aav_rec.object_version_number,
1624 CREATED_BY = l_def_aav_rec.created_by,
1625 CREATION_DATE = l_def_aav_rec.creation_date,
1626 LAST_UPDATED_BY = l_def_aav_rec.last_updated_by,
1627 LAST_UPDATE_DATE = l_def_aav_rec.last_update_date,
1628 LAST_UPDATE_LOGIN = l_def_aav_rec.last_update_login
1629 WHERE AAE_ID = l_def_aav_rec.aae_id
1630 AND COE_ID = l_def_aav_rec.coe_id;
1631
1632 x_aav_rec := l_def_aav_rec;
1633 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1634 EXCEPTION
1635 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1636 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1637 (
1638 l_api_name,
1639 G_PKG_NAME,
1640 'OKC_API.G_RET_STS_ERROR',
1641 x_msg_count,
1642 x_msg_data,
1643 '_PVT'
1644 );
1645 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1646 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1647 (
1648 l_api_name,
1649 G_PKG_NAME,
1650 'OKC_API.G_RET_STS_UNEXP_ERROR',
1651 x_msg_count,
1652 x_msg_data,
1653 '_PVT'
1654 );
1655 WHEN OTHERS THEN
1656 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1657 (
1658 l_api_name,
1659 G_PKG_NAME,
1660 'OTHERS',
1661 x_msg_count,
1662 x_msg_data,
1663 '_PVT'
1664 );
1665 END update_row;
1666 ------------------------------------------
1667 -- update_row for:OKC_ACTION_ATT_VALS_V --
1668 ------------------------------------------
1669 PROCEDURE update_row(
1670 p_api_version IN NUMBER,
1671 p_init_msg_list IN VARCHAR2 ,
1672 x_return_status OUT NOCOPY VARCHAR2,
1673 x_msg_count OUT NOCOPY NUMBER,
1674 x_msg_data OUT NOCOPY VARCHAR2,
1675 p_aavv_rec IN aavv_rec_type,
1676 x_aavv_rec OUT NOCOPY aavv_rec_type) IS
1677
1678 l_api_version CONSTANT NUMBER := 1;
1679 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1680 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1681 l_aavv_rec aavv_rec_type := p_aavv_rec;
1682 l_def_aavv_rec aavv_rec_type;
1683 l_aav_rec aav_rec_type;
1684 lx_aav_rec aav_rec_type;
1685 -------------------------------
1686 -- FUNCTION fill_who_columns --
1687 -------------------------------
1688 FUNCTION fill_who_columns (
1689 p_aavv_rec IN aavv_rec_type
1690 ) RETURN aavv_rec_type IS
1691 l_aavv_rec aavv_rec_type := p_aavv_rec;
1692 BEGIN
1693 l_aavv_rec.LAST_UPDATE_DATE := SYSDATE;
1694 l_aavv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1695 l_aavv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1696 RETURN(l_aavv_rec);
1697 END fill_who_columns;
1698 ----------------------------------
1699 -- FUNCTION populate_new_record --
1700 ----------------------------------
1701 FUNCTION populate_new_record (
1702 p_aavv_rec IN aavv_rec_type,
1703 x_aavv_rec OUT NOCOPY aavv_rec_type
1704 ) RETURN VARCHAR2 IS
1705 l_aavv_rec aavv_rec_type;
1706 l_row_notfound BOOLEAN := TRUE;
1707 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1708 BEGIN
1709 x_aavv_rec := p_aavv_rec;
1710 -- Get current database values
1711 l_aavv_rec := get_rec(p_aavv_rec, l_row_notfound);
1712 IF (l_row_notfound) THEN
1713 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1714 END IF;
1715 IF (x_aavv_rec.aae_id = OKC_API.G_MISS_NUM)
1716 THEN
1717 x_aavv_rec.aae_id := l_aavv_rec.aae_id;
1718 END IF;
1719 IF (x_aavv_rec.coe_id = OKC_API.G_MISS_NUM)
1720 THEN
1721 x_aavv_rec.coe_id := l_aavv_rec.coe_id;
1722 END IF;
1723 IF (x_aavv_rec.object_version_number = OKC_API.G_MISS_NUM)
1724 THEN
1725 x_aavv_rec.object_version_number := l_aavv_rec.object_version_number;
1726 END IF;
1727 IF (x_aavv_rec.value = OKC_API.G_MISS_CHAR)
1728 THEN
1729 x_aavv_rec.value := l_aavv_rec.value;
1730 END IF;
1731 IF (x_aavv_rec.created_by = OKC_API.G_MISS_NUM)
1732 THEN
1733 x_aavv_rec.created_by := l_aavv_rec.created_by;
1734 END IF;
1735 IF (x_aavv_rec.creation_date = OKC_API.G_MISS_DATE)
1736 THEN
1737 x_aavv_rec.creation_date := l_aavv_rec.creation_date;
1738 END IF;
1739 IF (x_aavv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1740 THEN
1741 x_aavv_rec.last_updated_by := l_aavv_rec.last_updated_by;
1742 END IF;
1743 IF (x_aavv_rec.last_update_date = OKC_API.G_MISS_DATE)
1744 THEN
1745 x_aavv_rec.last_update_date := l_aavv_rec.last_update_date;
1746 END IF;
1750 END IF;
1747 IF (x_aavv_rec.last_update_login = OKC_API.G_MISS_NUM)
1748 THEN
1749 x_aavv_rec.last_update_login := l_aavv_rec.last_update_login;
1751 RETURN(l_return_status);
1752 END populate_new_record;
1753 ----------------------------------------------
1754 -- Set_Attributes for:OKC_ACTION_ATT_VALS_V --
1755 ----------------------------------------------
1756 FUNCTION Set_Attributes (
1757 p_aavv_rec IN aavv_rec_type,
1758 x_aavv_rec OUT NOCOPY aavv_rec_type
1759 ) RETURN VARCHAR2 IS
1760 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1761 BEGIN
1762 x_aavv_rec := p_aavv_rec;
1763 x_aavv_rec.OBJECT_VERSION_NUMBER := NVL(x_aavv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1764 RETURN(l_return_status);
1765 END Set_Attributes;
1766 BEGIN
1767 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1768 G_PKG_NAME,
1769 p_init_msg_list,
1770 l_api_version,
1771 p_api_version,
1772 '_PVT',
1773 x_return_status);
1774 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1775 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1776 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1777 RAISE OKC_API.G_EXCEPTION_ERROR;
1778 END IF;
1779 --- Setting item attributes
1780 l_return_status := Set_Attributes(
1781 p_aavv_rec, -- IN
1782 l_aavv_rec); -- OUT
1783 --- If any errors happen abort API
1784 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1785 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1786 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1787 RAISE OKC_API.G_EXCEPTION_ERROR;
1788 END IF;
1789 l_return_status := populate_new_record(l_aavv_rec, l_def_aavv_rec);
1790 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1791 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1792 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1793 RAISE OKC_API.G_EXCEPTION_ERROR;
1794 END IF;
1795 l_def_aavv_rec := fill_who_columns(l_def_aavv_rec);
1796 --- Validate all non-missing attributes (Item Level Validation)
1797 l_return_status := Validate_Attributes(l_def_aavv_rec);
1798 --- If any errors happen abort API
1799 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1800 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1801 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1802 RAISE OKC_API.G_EXCEPTION_ERROR;
1803 END IF;
1804 l_return_status := Validate_Record(l_def_aavv_rec);
1805 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1806 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1807 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1808 RAISE OKC_API.G_EXCEPTION_ERROR;
1809 END IF;
1810
1811 --------------------------------------
1812 -- Move VIEW record to "Child" records
1813 --------------------------------------
1814 migrate(l_def_aavv_rec, l_aav_rec);
1815 --------------------------------------------
1816 -- Call the UPDATE_ROW for each child record
1817 --------------------------------------------
1818 update_row(
1819 p_init_msg_list,
1820 x_return_status,
1821 x_msg_count,
1822 x_msg_data,
1823 l_aav_rec,
1824 lx_aav_rec
1825 );
1826 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1827 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1828 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1829 RAISE OKC_API.G_EXCEPTION_ERROR;
1830 END IF;
1831 migrate(lx_aav_rec, l_def_aavv_rec);
1832 x_aavv_rec := l_def_aavv_rec;
1833 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1834 EXCEPTION
1835 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1836 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1837 (
1838 l_api_name,
1839 G_PKG_NAME,
1840 'OKC_API.G_RET_STS_ERROR',
1841 x_msg_count,
1842 x_msg_data,
1843 '_PVT'
1844 );
1845 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1846 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1847 (
1848 l_api_name,
1849 G_PKG_NAME,
1850 'OKC_API.G_RET_STS_UNEXP_ERROR',
1851 x_msg_count,
1852 x_msg_data,
1853 '_PVT'
1854 );
1855 WHEN OTHERS THEN
1856 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1857 (
1858 l_api_name,
1859 G_PKG_NAME,
1860 'OTHERS',
1861 x_msg_count,
1862 x_msg_data,
1863 '_PVT'
1864 );
1865 END update_row;
1866 ----------------------------------------
1867 -- PL/SQL TBL update_row for:AAVV_TBL --
1868 ----------------------------------------
1869 PROCEDURE update_row(
1870 p_api_version IN NUMBER,
1871 p_init_msg_list IN VARCHAR2 ,
1872 x_return_status OUT NOCOPY VARCHAR2,
1873 x_msg_count OUT NOCOPY NUMBER,
1874 x_msg_data OUT NOCOPY VARCHAR2,
1875 p_aavv_tbl IN aavv_tbl_type,
1876 x_aavv_tbl OUT NOCOPY aavv_tbl_type) IS
1877
1878 l_api_version CONSTANT NUMBER := 1;
1879 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1880 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1884 -- Make sure PL/SQL table has records in it before passing
1881 i NUMBER := 0;
1882 BEGIN
1883 OKC_API.init_msg_list(p_init_msg_list);
1885 IF (p_aavv_tbl.COUNT > 0) THEN
1886 i := p_aavv_tbl.FIRST;
1887 LOOP
1888 update_row (
1889 p_api_version => p_api_version,
1890 p_init_msg_list => OKC_API.G_FALSE,
1891 x_return_status => x_return_status,
1892 x_msg_count => x_msg_count,
1893 x_msg_data => x_msg_data,
1894 p_aavv_rec => p_aavv_tbl(i),
1895 x_aavv_rec => x_aavv_tbl(i));
1896 EXIT WHEN (i = p_aavv_tbl.LAST);
1897 i := p_aavv_tbl.NEXT(i);
1898 END LOOP;
1899 END IF;
1900 EXCEPTION
1901 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1902 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1903 (
1904 l_api_name,
1905 G_PKG_NAME,
1906 'OKC_API.G_RET_STS_ERROR',
1907 x_msg_count,
1908 x_msg_data,
1909 '_PVT'
1910 );
1911 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1912 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1913 (
1914 l_api_name,
1915 G_PKG_NAME,
1916 'OKC_API.G_RET_STS_UNEXP_ERROR',
1917 x_msg_count,
1918 x_msg_data,
1919 '_PVT'
1920 );
1921 WHEN OTHERS THEN
1922 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1923 (
1924 l_api_name,
1925 G_PKG_NAME,
1926 'OTHERS',
1927 x_msg_count,
1928 x_msg_data,
1929 '_PVT'
1930 );
1931 END update_row;
1932
1933 ---------------------------------------------------------------------------
1934 -- PROCEDURE delete_row
1935 ---------------------------------------------------------------------------
1936 ----------------------------------------
1937 -- delete_row for:OKC_ACTION_ATT_VALS --
1938 ----------------------------------------
1939 PROCEDURE delete_row(
1940 p_init_msg_list IN VARCHAR2 ,
1941 x_return_status OUT NOCOPY VARCHAR2,
1942 x_msg_count OUT NOCOPY NUMBER,
1943 x_msg_data OUT NOCOPY VARCHAR2,
1944 p_aav_rec IN aav_rec_type) IS
1945
1946 l_api_version CONSTANT NUMBER := 1;
1947 l_api_name CONSTANT VARCHAR2(30) := 'VALS_delete_row';
1948 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1949 l_aav_rec aav_rec_type:= p_aav_rec;
1950 l_row_notfound BOOLEAN := TRUE;
1951 BEGIN
1952 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1953 p_init_msg_list,
1954 '_PVT',
1955 x_return_status);
1956 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1957 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1958 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1959 RAISE OKC_API.G_EXCEPTION_ERROR;
1960 END IF;
1961 DELETE FROM OKC_ACTION_ATT_VALS
1962 WHERE AAE_ID = l_aav_rec.aae_id AND
1963 COE_ID = l_aav_rec.coe_id;
1964
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 delete_row;
1998 ------------------------------------------
1999 -- delete_row for:OKC_ACTION_ATT_VALS_V --
2000 ------------------------------------------
2001 PROCEDURE delete_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_aavv_rec IN aavv_rec_type) IS
2008
2009 l_api_version CONSTANT NUMBER := 1;
2010 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2011 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2012 l_aavv_rec aavv_rec_type := p_aavv_rec;
2013 l_aav_rec aav_rec_type;
2014 BEGIN
2015 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2016 G_PKG_NAME,
2017 p_init_msg_list,
2018 l_api_version,
2019 p_api_version,
2020 '_PVT',
2024 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2021 x_return_status);
2022 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2023 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2025 RAISE OKC_API.G_EXCEPTION_ERROR;
2026 END IF;
2027 --------------------------------------
2028 -- Move VIEW record to "Child" records
2029 --------------------------------------
2030 migrate(l_aavv_rec, l_aav_rec);
2031 --------------------------------------------
2032 -- Call the DELETE_ROW for each child record
2033 --------------------------------------------
2034 delete_row(
2035 p_init_msg_list,
2036 x_return_status,
2037 x_msg_count,
2038 x_msg_data,
2039 l_aav_rec
2040 );
2041 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2042 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2043 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2044 RAISE OKC_API.G_EXCEPTION_ERROR;
2045 END IF;
2046 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2047 EXCEPTION
2048 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2049 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2050 (
2051 l_api_name,
2052 G_PKG_NAME,
2053 'OKC_API.G_RET_STS_ERROR',
2054 x_msg_count,
2055 x_msg_data,
2056 '_PVT'
2057 );
2058 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2060 (
2061 l_api_name,
2062 G_PKG_NAME,
2063 'OKC_API.G_RET_STS_UNEXP_ERROR',
2064 x_msg_count,
2065 x_msg_data,
2066 '_PVT'
2067 );
2068 WHEN OTHERS THEN
2069 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2070 (
2071 l_api_name,
2072 G_PKG_NAME,
2073 'OTHERS',
2074 x_msg_count,
2075 x_msg_data,
2076 '_PVT'
2077 );
2078 END delete_row;
2079 ----------------------------------------
2080 -- PL/SQL TBL delete_row for:AAVV_TBL --
2081 ----------------------------------------
2082 PROCEDURE delete_row(
2083 p_api_version IN NUMBER,
2084 p_init_msg_list IN VARCHAR2 ,
2085 x_return_status OUT NOCOPY VARCHAR2,
2086 x_msg_count OUT NOCOPY NUMBER,
2087 x_msg_data OUT NOCOPY VARCHAR2,
2088 p_aavv_tbl IN aavv_tbl_type) IS
2089
2090 l_api_version CONSTANT NUMBER := 1;
2091 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2092 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2093 i NUMBER := 0;
2094 BEGIN
2095 OKC_API.init_msg_list(p_init_msg_list);
2096 -- Make sure PL/SQL table has records in it before passing
2097 IF (p_aavv_tbl.COUNT > 0) THEN
2098 i := p_aavv_tbl.FIRST;
2099 LOOP
2100 delete_row (
2101 p_api_version => p_api_version,
2102 p_init_msg_list => OKC_API.G_FALSE,
2103 x_return_status => x_return_status,
2104 x_msg_count => x_msg_count,
2105 x_msg_data => x_msg_data,
2106 p_aavv_rec => p_aavv_tbl(i));
2107 EXIT WHEN (i = p_aavv_tbl.LAST);
2108 i := p_aavv_tbl.NEXT(i);
2109 END LOOP;
2110 END IF;
2111 EXCEPTION
2112 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2113 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2114 (
2115 l_api_name,
2116 G_PKG_NAME,
2117 'OKC_API.G_RET_STS_ERROR',
2118 x_msg_count,
2119 x_msg_data,
2120 '_PVT'
2121 );
2122 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2123 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2124 (
2125 l_api_name,
2126 G_PKG_NAME,
2127 'OKC_API.G_RET_STS_UNEXP_ERROR',
2128 x_msg_count,
2129 x_msg_data,
2130 '_PVT'
2131 );
2132 WHEN OTHERS THEN
2133 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2134 (
2135 l_api_name,
2136 G_PKG_NAME,
2137 'OTHERS',
2138 x_msg_count,
2139 x_msg_data,
2140 '_PVT'
2141 );
2142 END delete_row;
2143 ---------------------------------------------------------------
2144 -- Procedure for mass insert in OKC_ACTION_ATTRIBUTES_VALS _B and TL tables
2145 ---------------------------------------------------------------
2146 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2, p_aavv_tbl aavv_tbl_type) IS
2147 l_tabsize NUMBER := p_aavv_tbl.COUNT;
2148 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
2149
2150 in_aae_id OKC_DATATYPES.NumberTabTyp;
2151 in_coe_id OKC_DATATYPES.NumberTabTyp;
2152 in_object_version_number OKC_DATATYPES.NumberTabTyp;
2153 in_value OKC_DATATYPES.Var1995TabTyp; --Changed for Bug 3408604
2154 in_created_by OKC_DATATYPES.NumberTabTyp;
2155 in_creation_date OKC_DATATYPES.DateTabTyp;
2156 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
2157 in_last_update_date OKC_DATATYPES.DateTabTyp;
2158 in_last_update_login OKC_DATATYPES.NumberTabTyp;
2159 j NUMBER := 0;
2160 i NUMBER := p_aavv_tbl.FIRST;
2161 BEGIN
2162 x_return_status := OKC_API.G_RET_STS_ERROR;
2163 -- Initializing the Return Status
2164
2165 while i is not null
2166 LOOP
2167 j := j + 1;
2168 in_aae_id (j) := p_aavv_tbl(i).aae_id;
2169 in_coe_id (j) := p_aavv_tbl(i).coe_id;
2170 in_object_version_number (j) := p_aavv_tbl(i).object_version_number;
2171 in_value (j) := p_aavv_tbl(i).value;
2172 in_created_by (j) := p_aavv_tbl(i).created_by;
2173 in_creation_date (j) := p_aavv_tbl(i).creation_date;
2174 in_last_updated_by (j) := p_aavv_tbl(i).last_updated_by;
2175 in_last_update_date (j) := p_aavv_tbl(i).last_update_date;
2176 in_last_update_login (j) := p_aavv_tbl(i).last_update_login;
2177 i := p_aavv_tbl.NEXT(i);
2178 END LOOP;
2179
2180 FORALL i in 1..l_tabsize
2181 INSERT
2182 INTO OKC_ACTION_ATT_VALS
2183 (
2184 aae_id,
2185 coe_id,
2186 value,
2187 object_version_number,
2188 created_by,
2189 creation_date,
2190 last_updated_by,
2191 last_update_date,
2192 last_update_login
2193 )
2194 VALUES (
2195 in_aae_id(i),
2196 in_coe_id(i),
2197 in_value(i),
2198 in_object_version_number(i),
2199 in_created_by(i),
2200 in_creation_date(i),
2201 in_last_updated_by(i),
2202 in_last_update_date(i),
2203 in_last_update_login(i)
2204 );
2205
2206 EXCEPTION
2207 WHEN OTHERS THEN
2208 -- store SQL error message on message stack
2209 OKC_API.SET_MESSAGE(
2210 p_app_name => G_APP_NAME,
2211 p_msg_name => G_UNEXPECTED_ERROR,
2212 p_token1 => G_SQLCODE_TOKEN,
2213 p_token1_value => SQLCODE,
2214 p_token2 => G_SQLERRM_TOKEN,
2215 p_token2_value => SQLERRM);
2216 -- notify caller of an error as UNEXPETED error
2217 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2218 -- RAISE;
2219 END INSERT_ROW_UPG;
2220 END OKC_AAV_PVT;