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