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