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