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