[Home] [Help]
PACKAGE BODY: APPS.OKL_IMA_PVT
Source
1 PACKAGE BODY OKL_IMA_PVT AS
2 /* $Header: OKLSIMAB.pls 115.7 2002/12/18 12:58:38 kjinger noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Global Variables
6 -- Post-Generation Change
7 -- By RDRAGUIL on 24-APR-2001
8 ---------------------------------------------------------------------------
9 --GLOBAL MESSAGES
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_UNEXPECTED_ERROR';
11 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKL_NO_PARENT_RECORD';
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
14 G_NOT_SAME CONSTANT VARCHAR2(200) := 'OKL_CANNOT_BE_SAME';
15
16 --GLOBAL VARIABLES
17 G_VIEW CONSTANT VARCHAR2(30) := 'OKL_INV_MSSG_ATT_V';
18 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
19
20 ---------------------------------------------------------------------------
21 -- FUNCTION get_seq_id
22 ---------------------------------------------------------------------------
23 FUNCTION get_seq_id RETURN NUMBER IS
24 BEGIN
25 RETURN(okc_p_util.raw_to_number(sys_guid()));
26 END get_seq_id;
27
28 ---------------------------------------------------------------------------
29 -- PROCEDURE qc
30 ---------------------------------------------------------------------------
31 PROCEDURE qc IS
32 BEGIN
33 null;
34 END qc;
35
36 ---------------------------------------------------------------------------
37 -- PROCEDURE change_version
38 ---------------------------------------------------------------------------
39 PROCEDURE change_version IS
40 BEGIN
41 null;
42 END change_version;
43
44 ---------------------------------------------------------------------------
45 -- PROCEDURE api_copy
46 ---------------------------------------------------------------------------
47 PROCEDURE api_copy IS
48 BEGIN
49 null;
50 END api_copy;
51
52 ---------------------------------------------------------------------------
53 -- FUNCTION get_rec for: OKL_INV_MSSG_ATT
54 ---------------------------------------------------------------------------
55 FUNCTION get_rec (
56 p_ima_rec IN ima_rec_type,
57 x_no_data_found OUT NOCOPY BOOLEAN
58 ) RETURN ima_rec_type IS
59 CURSOR ima_pk_csr (p_id IN NUMBER) IS
60 SELECT
61 ID,
62 CNR_ID,
63 IMS_ID,
64 OBJECT_VERSION_NUMBER,
65 ATTRIBUTE_CATEGORY,
66 ATTRIBUTE1,
67 ATTRIBUTE2,
68 ATTRIBUTE3,
69 ATTRIBUTE4,
70 ATTRIBUTE5,
71 ATTRIBUTE6,
72 ATTRIBUTE7,
73 ATTRIBUTE8,
74 ATTRIBUTE9,
75 ATTRIBUTE10,
76 ATTRIBUTE11,
77 ATTRIBUTE12,
78 ATTRIBUTE13,
79 ATTRIBUTE14,
80 ATTRIBUTE15,
81 CREATED_BY,
82 CREATION_DATE,
83 LAST_UPDATED_BY,
84 LAST_UPDATE_DATE,
85 LAST_UPDATE_LOGIN
86 FROM Okl_Inv_Mssg_Att
87 WHERE okl_inv_mssg_att.id = p_id;
88 l_ima_pk ima_pk_csr%ROWTYPE;
89 l_ima_rec ima_rec_type;
90 BEGIN
91 x_no_data_found := TRUE;
92 -- Get current database values
93 OPEN ima_pk_csr (p_ima_rec.id);
94 FETCH ima_pk_csr INTO
95 l_ima_rec.ID,
96 l_ima_rec.CNR_ID,
97 l_ima_rec.IMS_ID,
98 l_ima_rec.OBJECT_VERSION_NUMBER,
99 l_ima_rec.ATTRIBUTE_CATEGORY,
100 l_ima_rec.ATTRIBUTE1,
101 l_ima_rec.ATTRIBUTE2,
102 l_ima_rec.ATTRIBUTE3,
103 l_ima_rec.ATTRIBUTE4,
104 l_ima_rec.ATTRIBUTE5,
105 l_ima_rec.ATTRIBUTE6,
106 l_ima_rec.ATTRIBUTE7,
107 l_ima_rec.ATTRIBUTE8,
108 l_ima_rec.ATTRIBUTE9,
109 l_ima_rec.ATTRIBUTE10,
110 l_ima_rec.ATTRIBUTE11,
111 l_ima_rec.ATTRIBUTE12,
112 l_ima_rec.ATTRIBUTE13,
113 l_ima_rec.ATTRIBUTE14,
114 l_ima_rec.ATTRIBUTE15,
115 l_ima_rec.CREATED_BY,
116 l_ima_rec.CREATION_DATE,
117 l_ima_rec.LAST_UPDATED_BY,
118 l_ima_rec.LAST_UPDATE_DATE,
119 l_ima_rec.LAST_UPDATE_LOGIN;
120 x_no_data_found := ima_pk_csr%NOTFOUND;
121 CLOSE ima_pk_csr;
122 RETURN(l_ima_rec);
123 END get_rec;
124
125 FUNCTION get_rec (
126 p_ima_rec IN ima_rec_type
127 ) RETURN ima_rec_type IS
128 l_row_notfound BOOLEAN := TRUE;
129 BEGIN
130 RETURN(get_rec(p_ima_rec, l_row_notfound));
131 END get_rec;
132 ---------------------------------------------------------------------------
133 -- FUNCTION get_rec for: OKL_INV_MSSG_ATT_V
134 ---------------------------------------------------------------------------
135 FUNCTION get_rec (
136 p_imav_rec IN imav_rec_type,
137 x_no_data_found OUT NOCOPY BOOLEAN
138 ) RETURN imav_rec_type IS
139 CURSOR okl_imav_pk_csr (p_id IN NUMBER) IS
140 SELECT
141 ID,
142 OBJECT_VERSION_NUMBER,
143 CNR_ID,
144 IMS_ID,
145 ATTRIBUTE_CATEGORY,
146 ATTRIBUTE1,
147 ATTRIBUTE2,
148 ATTRIBUTE3,
149 ATTRIBUTE4,
150 ATTRIBUTE5,
151 ATTRIBUTE6,
152 ATTRIBUTE7,
153 ATTRIBUTE8,
154 ATTRIBUTE9,
155 ATTRIBUTE10,
156 ATTRIBUTE11,
157 ATTRIBUTE12,
158 ATTRIBUTE13,
159 ATTRIBUTE14,
160 ATTRIBUTE15,
161 CREATED_BY,
162 CREATION_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATE_LOGIN
166 FROM Okl_Inv_Mssg_Att_V
167 WHERE okl_inv_mssg_att_v.id = p_id;
168 l_okl_imav_pk okl_imav_pk_csr%ROWTYPE;
169 l_imav_rec imav_rec_type;
170 BEGIN
171 x_no_data_found := TRUE;
172 -- Get current database values
173 OPEN okl_imav_pk_csr (p_imav_rec.id);
174 FETCH okl_imav_pk_csr INTO
175 l_imav_rec.ID,
176 l_imav_rec.OBJECT_VERSION_NUMBER,
177 l_imav_rec.CNR_ID,
178 l_imav_rec.IMS_ID,
179 l_imav_rec.ATTRIBUTE_CATEGORY,
180 l_imav_rec.ATTRIBUTE1,
181 l_imav_rec.ATTRIBUTE2,
182 l_imav_rec.ATTRIBUTE3,
183 l_imav_rec.ATTRIBUTE4,
184 l_imav_rec.ATTRIBUTE5,
185 l_imav_rec.ATTRIBUTE6,
186 l_imav_rec.ATTRIBUTE7,
187 l_imav_rec.ATTRIBUTE8,
188 l_imav_rec.ATTRIBUTE9,
189 l_imav_rec.ATTRIBUTE10,
190 l_imav_rec.ATTRIBUTE11,
191 l_imav_rec.ATTRIBUTE12,
192 l_imav_rec.ATTRIBUTE13,
193 l_imav_rec.ATTRIBUTE14,
194 l_imav_rec.ATTRIBUTE15,
195 l_imav_rec.CREATED_BY,
196 l_imav_rec.CREATION_DATE,
197 l_imav_rec.LAST_UPDATED_BY,
198 l_imav_rec.LAST_UPDATE_DATE,
199 l_imav_rec.LAST_UPDATE_LOGIN;
200 x_no_data_found := okl_imav_pk_csr%NOTFOUND;
201 CLOSE okl_imav_pk_csr;
202 RETURN(l_imav_rec);
203 END get_rec;
204
205 FUNCTION get_rec (
206 p_imav_rec IN imav_rec_type
207 ) RETURN imav_rec_type IS
208 l_row_notfound BOOLEAN := TRUE;
209 BEGIN
210 RETURN(get_rec(p_imav_rec, l_row_notfound));
211 END get_rec;
212
213 --------------------------------------------------------
214 -- FUNCTION null_out_defaults for: OKL_INV_MSSG_ATT_V --
215 --------------------------------------------------------
216 FUNCTION null_out_defaults (
217 p_imav_rec IN imav_rec_type
218 ) RETURN imav_rec_type IS
219 l_imav_rec imav_rec_type := p_imav_rec;
220 BEGIN
221 IF (l_imav_rec.object_version_number = OKL_API.G_MISS_NUM) THEN
222 l_imav_rec.object_version_number := NULL;
223 END IF;
224 IF (l_imav_rec.cnr_id = OKL_API.G_MISS_NUM) THEN
225 l_imav_rec.cnr_id := NULL;
226 END IF;
227 IF (l_imav_rec.ims_id = OKL_API.G_MISS_NUM) THEN
228 l_imav_rec.ims_id := NULL;
229 END IF;
230 IF (l_imav_rec.attribute_category = OKL_API.G_MISS_CHAR) THEN
231 l_imav_rec.attribute_category := NULL;
232 END IF;
233 IF (l_imav_rec.attribute1 = OKL_API.G_MISS_CHAR) THEN
234 l_imav_rec.attribute1 := NULL;
235 END IF;
236 IF (l_imav_rec.attribute2 = OKL_API.G_MISS_CHAR) THEN
237 l_imav_rec.attribute2 := NULL;
238 END IF;
239 IF (l_imav_rec.attribute3 = OKL_API.G_MISS_CHAR) THEN
240 l_imav_rec.attribute3 := NULL;
241 END IF;
242 IF (l_imav_rec.attribute4 = OKL_API.G_MISS_CHAR) THEN
243 l_imav_rec.attribute4 := NULL;
244 END IF;
245 IF (l_imav_rec.attribute5 = OKL_API.G_MISS_CHAR) THEN
246 l_imav_rec.attribute5 := NULL;
247 END IF;
248 IF (l_imav_rec.attribute6 = OKL_API.G_MISS_CHAR) THEN
249 l_imav_rec.attribute6 := NULL;
250 END IF;
251 IF (l_imav_rec.attribute7 = OKL_API.G_MISS_CHAR) THEN
252 l_imav_rec.attribute7 := NULL;
253 END IF;
254 IF (l_imav_rec.attribute8 = OKL_API.G_MISS_CHAR) THEN
255 l_imav_rec.attribute8 := NULL;
256 END IF;
257 IF (l_imav_rec.attribute9 = OKL_API.G_MISS_CHAR) THEN
258 l_imav_rec.attribute9 := NULL;
259 END IF;
260 IF (l_imav_rec.attribute10 = OKL_API.G_MISS_CHAR) THEN
261 l_imav_rec.attribute10 := NULL;
262 END IF;
263 IF (l_imav_rec.attribute11 = OKL_API.G_MISS_CHAR) THEN
264 l_imav_rec.attribute11 := NULL;
265 END IF;
266 IF (l_imav_rec.attribute12 = OKL_API.G_MISS_CHAR) THEN
267 l_imav_rec.attribute12 := NULL;
268 END IF;
269 IF (l_imav_rec.attribute13 = OKL_API.G_MISS_CHAR) THEN
270 l_imav_rec.attribute13 := NULL;
271 END IF;
272 IF (l_imav_rec.attribute14 = OKL_API.G_MISS_CHAR) THEN
273 l_imav_rec.attribute14 := NULL;
274 END IF;
275 IF (l_imav_rec.attribute15 = OKL_API.G_MISS_CHAR) THEN
276 l_imav_rec.attribute15 := NULL;
277 END IF;
278 IF (l_imav_rec.created_by = OKL_API.G_MISS_NUM) THEN
279 l_imav_rec.created_by := NULL;
280 END IF;
281 IF (l_imav_rec.creation_date = OKL_API.G_MISS_DATE) THEN
282 l_imav_rec.creation_date := NULL;
283 END IF;
284 IF (l_imav_rec.last_updated_by = OKL_API.G_MISS_NUM) THEN
285 l_imav_rec.last_updated_by := NULL;
286 END IF;
287 IF (l_imav_rec.last_update_date = OKL_API.G_MISS_DATE) THEN
288 l_imav_rec.last_update_date := NULL;
289 END IF;
290 IF (l_imav_rec.last_update_login = OKL_API.G_MISS_NUM) THEN
291 l_imav_rec.last_update_login := NULL;
292 END IF;
293 RETURN(l_imav_rec);
294 END null_out_defaults;
295 ---------------------------------------------------------------------------
296 -- PROCEDURE Validate_Id
297 -- Post-Generation Change
298 -- By RDRAGUIL on 24-APR-2001
299 ---------------------------------------------------------------------------
300
301 PROCEDURE Validate_Id (
302 x_return_status OUT NOCOPY VARCHAR2,
303 p_imav_rec IN imav_rec_type) IS
304
305 BEGIN
306
307 -- initialize return status
308 x_return_status := OKL_API.G_RET_STS_SUCCESS;
309
310 -- data is required
311 IF p_imav_rec.id = OKL_API.G_MISS_NUM
312 OR p_imav_rec.id IS NULL
313 THEN
314
315 -- display error message
316 OKL_API.set_message(
317 p_app_name => G_APP_NAME,
318 p_msg_name => G_REQUIRED_VALUE,
319 p_token1 => G_COL_NAME_TOKEN,
320 p_token1_value => 'id');
321
322 -- notify caller of en error
323 x_return_status := OKL_API.G_RET_STS_ERROR;
324 -- halt furhter validation of the column
325 raise G_EXCEPTION_HALT_VALIDATION;
326
327 END IF;
328
329 EXCEPTION
330
331 when G_EXCEPTION_HALT_VALIDATION then
332 -- no processing necessary; continue validation
333 null;
334
335 when OTHERS then
336 -- display error message
337 OKL_API.set_message(
338 p_app_name => G_APP_NAME,
339 p_msg_name => G_UNEXPECTED_ERROR,
340 p_token1 => G_SQLCODE_TOKEN,
341 p_token1_value => SQLCODE,
342 p_token2 => G_SQLERRM_TOKEN,
343 p_token2_value => SQLERRM);
344 -- notify caller of an unexpected error
345 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
346
347 END Validate_Id;
348
349 ---------------------------------------------------------------------------
350 -- PROCEDURE Validate_Object_Version_Number
351 -- Post-Generation Change
352 -- By RDRAGUIL on 24-APR-2001
353 ---------------------------------------------------------------------------
354
355 PROCEDURE Validate_Object_Version_Number (
356 x_return_status OUT NOCOPY VARCHAR2,
357 p_imav_rec IN imav_rec_type) IS
358
359 BEGIN
360
361 -- initialize return status
362 x_return_status := OKL_API.G_RET_STS_SUCCESS;
363
364 -- data is required
365 IF p_imav_rec.object_version_number = OKL_API.G_MISS_NUM
366 OR p_imav_rec.object_version_number IS NULL
367 THEN
368
369 -- display error message
370 OKL_API.set_message(
371 p_app_name => G_APP_NAME,
372 p_msg_name => G_REQUIRED_VALUE,
373 p_token1 => G_COL_NAME_TOKEN,
374 p_token1_value => 'object_version_number');
375
376 -- notify caller of en error
377 x_return_status := OKL_API.G_RET_STS_ERROR;
378 -- halt furhter validation of the column
379 raise G_EXCEPTION_HALT_VALIDATION;
380
381 END IF;
382
383 EXCEPTION
384
385 when G_EXCEPTION_HALT_VALIDATION then
386 -- no processing necessary; continue validation
387 null;
388
389 when OTHERS then
390 -- display error message
391 OKL_API.set_message(
392 p_app_name => G_APP_NAME,
393 p_msg_name => G_UNEXPECTED_ERROR,
394 p_token1 => G_SQLCODE_TOKEN,
395 p_token1_value => SQLCODE,
396 p_token2 => G_SQLERRM_TOKEN,
397 p_token2_value => SQLERRM);
398 -- notify caller of an unexpected error
399 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
400
401 END Validate_Object_Version_Number;
402
403 ---------------------------------------------------------------------------
404 -- PROCEDURE Validate_Ims_Id
405 -- Post-Generation Change
406 -- By RDRAGUIL on 24-APR-2001
407 ---------------------------------------------------------------------------
408
409 PROCEDURE Validate_Ims_Id (
410 x_return_status OUT NOCOPY VARCHAR2,
411 p_imav_rec IN imav_rec_type) IS
412
413 l_dummy_var VARCHAR2(1) := '?';
414
415 CURSOR l_imsv_csr IS
416 SELECT 'x'
417 FROM okl_invoice_mssgs_v
418 WHERE id = p_imav_rec.ims_id;
419
420 BEGIN
421
422 -- initialize return status
423 x_return_status := OKL_API.G_RET_STS_SUCCESS;
424
425 -- data is required
426 IF p_imav_rec.ims_id = OKL_API.G_MISS_NUM
427 OR p_imav_rec.ims_id IS NULL
428 THEN
429
430 -- display error message
431 OKL_API.set_message(
432 p_app_name => G_APP_NAME,
433 p_msg_name => G_REQUIRED_VALUE,
434 p_token1 => G_COL_NAME_TOKEN,
435 p_token1_value => 'ims_id');
436
437 -- notify caller of en error
438 x_return_status := OKL_API.G_RET_STS_ERROR;
439 -- halt furhter validation of the column
440 raise G_EXCEPTION_HALT_VALIDATION;
441
442 END IF;
443
444 -- enforce foreign key
445 OPEN l_imsv_csr;
446 FETCH l_imsv_csr INTO l_dummy_var;
447 CLOSE l_imsv_csr;
448
449 -- if dummy value is still set to default, data was not found
450 IF (l_dummy_var = '?') THEN
451
452 -- display error message
453 OKL_API.set_message(
454 p_app_name => G_APP_NAME,
455 p_msg_name => G_NO_PARENT_RECORD,
456 p_token1 => G_COL_NAME_TOKEN,
457 p_token1_value => 'ims_id',
458 p_token2 => G_CHILD_TABLE_TOKEN,
459 p_token2_value => 'OKL_CONDITION_MSSGS_V',
460 p_token3 => G_PARENT_TABLE_TOKEN,
461 p_token3_value => 'OKL_INVOICE_MSSGS_V');
462
463 -- notify caller of en error
464 x_return_status := OKL_API.G_RET_STS_ERROR;
465
466 END IF;
467
468 EXCEPTION
469
470 when G_EXCEPTION_HALT_VALIDATION then
471 -- no processing necessary; continue validation
472 null;
473
474 when OTHERS then
475 -- display error message
476 OKL_API.set_message(
477 p_app_name => G_APP_NAME,
478 p_msg_name => G_UNEXPECTED_ERROR,
479 p_token1 => G_SQLCODE_TOKEN,
480 p_token1_value => SQLCODE,
481 p_token2 => G_SQLERRM_TOKEN,
482 p_token2_value => SQLERRM);
483 -- notify caller of an unexpected error
484 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
485 -- verify the cursor is closed
486 IF l_imsv_csr%ISOPEN THEN
487 CLOSE l_imsv_csr;
488 END IF;
489
490 END Validate_Ims_Id;
491
492 ---------------------------------------------------------------------------
493 -- PROCEDURE Validate_Cnr_Id
494 -- Post-Generation Change
495 -- By RDRAGUIL on 24-APR-2001
496 ---------------------------------------------------------------------------
497
498 PROCEDURE Validate_Cnr_Id (
499 x_return_status OUT NOCOPY VARCHAR2,
500 p_imav_rec IN imav_rec_type) IS
501
502 l_dummy_var VARCHAR2(1) := '?';
503
504 CURSOR l_cnrv_csr IS
505 SELECT 'x'
506 FROM okl_cnsld_ar_hdrs_v
507 WHERE id = p_imav_rec.cnr_id;
508
509 BEGIN
510
511 -- initialize return status
512 x_return_status := OKL_API.G_RET_STS_SUCCESS;
513
514 -- data is required
515 IF p_imav_rec.cnr_id = OKL_API.G_MISS_NUM
516 OR p_imav_rec.cnr_id IS NULL
517 THEN
518
519 -- display error message
520 OKL_API.set_message(
521 p_app_name => G_APP_NAME,
522 p_msg_name => G_REQUIRED_VALUE,
523 p_token1 => G_COL_NAME_TOKEN,
524 p_token1_value => 'cnr_id');
525
526 -- notify caller of en error
527 x_return_status := OKL_API.G_RET_STS_ERROR;
528 -- halt furhter validation of the column
529 raise G_EXCEPTION_HALT_VALIDATION;
530
531 END IF;
532
533 -- enforce foreign key
534 OPEN l_cnrv_csr;
535 FETCH l_cnrv_csr INTO l_dummy_var;
536 CLOSE l_cnrv_csr;
537
538 -- if dummy value is still set to default, data was not found
539 IF (l_dummy_var = '?') THEN
540
541 -- display error message
542 OKL_API.set_message(
543 p_app_name => G_APP_NAME,
544 p_msg_name => G_NO_PARENT_RECORD,
545 p_token1 => G_COL_NAME_TOKEN,
546 p_token1_value => 'cnr_id',
547 p_token2 => G_CHILD_TABLE_TOKEN,
548 p_token2_value => 'OKL_CONDITION_MSSGS_V',
549 p_token3 => G_PARENT_TABLE_TOKEN,
550 p_token3_value => 'OKL_CNSLD_AR_HDRS_V');
551
552 -- notify caller of en error
553 x_return_status := OKL_API.G_RET_STS_ERROR;
554
555 END IF;
556
557 EXCEPTION
558
559 when G_EXCEPTION_HALT_VALIDATION then
560 -- no processing necessary; continue validation
561 null;
562
563 when OTHERS then
564 -- display error message
565 OKL_API.set_message(
566 p_app_name => G_APP_NAME,
567 p_msg_name => G_UNEXPECTED_ERROR,
568 p_token1 => G_SQLCODE_TOKEN,
569 p_token1_value => SQLCODE,
570 p_token2 => G_SQLERRM_TOKEN,
571 p_token2_value => SQLERRM);
572 -- notify caller of an unexpected error
573 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
574 -- verify the cursor is closed
575 IF l_cnrv_csr%ISOPEN THEN
576 CLOSE l_cnrv_csr;
577 END IF;
578
579 END Validate_Cnr_Id;
580
581 ---------------------------------------------------------------------------
582 -- PROCEDURE Is_Unique
583 -- Post-Generation Change
584 -- By RDRAGUIL on 24-APR-2001
585 ---------------------------------------------------------------------------
586 FUNCTION Is_Unique (
587 p_imav_rec IN imav_rec_type
588 ) RETURN VARCHAR2 IS
589
590 CURSOR l_imav_csr IS
591 SELECT 'x'
592 FROM okl_inv_mssg_att_v
593 WHERE ims_id = p_imav_rec.ims_id
594 AND cnr_id = p_imav_rec.cnr_id
595 AND id <> nvl (p_imav_rec.id, -99999);
596
597 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
598 l_dummy VARCHAR2(1);
599 l_found BOOLEAN;
600
601 BEGIN
602
603 -- check for unique IMS_ID + CNR_ID
604 OPEN l_imav_csr;
605 FETCH l_imav_csr INTO l_dummy;
606 l_found := l_imav_csr%FOUND;
607 CLOSE l_imav_csr;
608
609 IF (l_found) THEN
610
611 -- display error message
612 OKL_API.set_message(
613 p_app_name => G_APP_NAME,
614 p_msg_name => G_NOT_SAME,
615 p_token1 => 'IMS_ID',
616 p_token1_value => p_imav_rec.ims_id,
617 p_token2 => 'CNR_ID',
618 p_token2_value => p_imav_rec.cnr_id);
619
620 -- notify caller of an error
621 l_return_status := OKL_API.G_RET_STS_ERROR;
622
623 END IF;
624
625 -- return status to the caller
626 RETURN l_return_status;
627
628 EXCEPTION
629
630 when OTHERS then
631 -- display error message
632 OKL_API.set_message(
633 p_app_name => G_APP_NAME,
634 p_msg_name => G_UNEXPECTED_ERROR,
635 p_token1 => G_SQLCODE_TOKEN,
636 p_token1_value => SQLCODE,
637 p_token2 => G_SQLERRM_TOKEN,
638 p_token2_value => SQLERRM);
639 -- notify caller of an unexpected error
640 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
641 -- verify the cursor is closed
642 IF l_imav_csr%ISOPEN THEN
643 CLOSE l_imav_csr;
644 END IF;
645 -- return status to the caller
646 RETURN l_return_status;
647
648 END Is_Unique;
649
650 ---------------------------------------------------------------------------
651 -- PROCEDURE Validate_Attributes
652 -- Post-Generation Change
653 -- By RDRAGUIL on 24-APR-2001
654 ---------------------------------------------------------------------------
655 ---------------------------------------------------
656 -- Validate_Attributes for:OKL_INV_MSSG_ATT_V --
657 ---------------------------------------------------
658 FUNCTION Validate_Attributes (
659 p_imav_rec IN imav_rec_type
660 ) RETURN VARCHAR2 IS
661
662 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
663 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
664
665 BEGIN
666
667 -- call each column-level validation
668
669 validate_id (
670 x_return_status => l_return_status,
671 p_imav_rec => p_imav_rec);
672
673 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
674 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
675 x_return_status := l_return_status;
676 END IF;
677 END IF;
678
679 validate_object_version_number (
680 x_return_status => l_return_status,
681 p_imav_rec => p_imav_rec);
682
683 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
684 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
685 x_return_status := l_return_status;
686 END IF;
687 END IF;
688
689 validate_ims_id (
690 x_return_status => l_return_status,
691 p_imav_rec => p_imav_rec);
692
693 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
694 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
695 x_return_status := l_return_status;
696 END IF;
697 END IF;
698
699 validate_cnr_id (
700 x_return_status => l_return_status,
701 p_imav_rec => p_imav_rec);
702
703 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
704 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
705 x_return_status := l_return_status;
706 END IF;
707 END IF;
708
709 RETURN x_return_status;
710
711 EXCEPTION
712
713 when OTHERS then
714 -- display error message
715 OKL_API.set_message(
716 p_app_name => G_APP_NAME,
717 p_msg_name => G_UNEXPECTED_ERROR,
718 p_token1 => G_SQLCODE_TOKEN,
719 p_token1_value => SQLCODE,
720 p_token2 => G_SQLERRM_TOKEN,
721 p_token2_value => SQLERRM);
722 -- notify caller of an unexpected error
723 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
724 -- return status to the caller
725 RETURN x_return_status;
726
727 END Validate_Attributes;
728
729 ---------------------------------------------------------------------------
730 -- PROCEDURE Validate_Record
731 -- Post-Generation Change
732 -- By RDRAGUIL on 24-APR-2001
733 ---------------------------------------------------------------------------
734 -----------------------------------------------
735 -- Validate_Record for:OKL_INV_MSSG_ATT_V --
736 -----------------------------------------------
737 FUNCTION Validate_Record (
738 p_imav_rec IN imav_rec_type
739 ) RETURN VARCHAR2 IS
740 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
741 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
742 BEGIN
743
744 -- call each record-level validation
745 l_return_status := is_unique (p_imav_rec);
746
747 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
748 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
749 x_return_status := l_return_status;
750 END IF;
751 END IF;
752
753 RETURN x_return_status;
754
755 EXCEPTION
756
757 when OTHERS then
758 -- display error message
759 OKL_API.set_message(
760 p_app_name => G_APP_NAME,
761 p_msg_name => G_UNEXPECTED_ERROR,
762 p_token1 => G_SQLCODE_TOKEN,
763 p_token1_value => SQLCODE,
764 p_token2 => G_SQLERRM_TOKEN,
765 p_token2_value => SQLERRM);
766 -- notify caller of an unexpected error
767 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
768 -- return status to the caller
769 RETURN x_return_status;
770
771 END Validate_Record;
772
773 ---------------------------------------------------------------------------
774 -- PROCEDURE Migrate
775 ---------------------------------------------------------------------------
776 PROCEDURE migrate (
777 p_from IN imav_rec_type,
778 p_to IN OUT NOCOPY ima_rec_type
779 ) IS
780 BEGIN
781 p_to.id := p_from.id;
782 p_to.cnr_id := p_from.cnr_id;
783 p_to.ims_id := p_from.ims_id;
784 p_to.object_version_number := p_from.object_version_number;
785 p_to.attribute_category := p_from.attribute_category;
786 p_to.attribute1 := p_from.attribute1;
787 p_to.attribute2 := p_from.attribute2;
788 p_to.attribute3 := p_from.attribute3;
789 p_to.attribute4 := p_from.attribute4;
790 p_to.attribute5 := p_from.attribute5;
791 p_to.attribute6 := p_from.attribute6;
792 p_to.attribute7 := p_from.attribute7;
793 p_to.attribute8 := p_from.attribute8;
794 p_to.attribute9 := p_from.attribute9;
795 p_to.attribute10 := p_from.attribute10;
796 p_to.attribute11 := p_from.attribute11;
797 p_to.attribute12 := p_from.attribute12;
798 p_to.attribute13 := p_from.attribute13;
799 p_to.attribute14 := p_from.attribute14;
800 p_to.attribute15 := p_from.attribute15;
801 p_to.created_by := p_from.created_by;
802 p_to.creation_date := p_from.creation_date;
803 p_to.last_updated_by := p_from.last_updated_by;
804 p_to.last_update_date := p_from.last_update_date;
805 p_to.last_update_login := p_from.last_update_login;
806 END migrate;
807 PROCEDURE migrate (
808 p_from IN ima_rec_type,
809 p_to IN OUT NOCOPY imav_rec_type
810 ) IS
811 BEGIN
812 p_to.id := p_from.id;
813 p_to.cnr_id := p_from.cnr_id;
814 p_to.ims_id := p_from.ims_id;
815 p_to.object_version_number := p_from.object_version_number;
816 p_to.attribute_category := p_from.attribute_category;
817 p_to.attribute1 := p_from.attribute1;
818 p_to.attribute2 := p_from.attribute2;
819 p_to.attribute3 := p_from.attribute3;
820 p_to.attribute4 := p_from.attribute4;
821 p_to.attribute5 := p_from.attribute5;
822 p_to.attribute6 := p_from.attribute6;
823 p_to.attribute7 := p_from.attribute7;
824 p_to.attribute8 := p_from.attribute8;
825 p_to.attribute9 := p_from.attribute9;
826 p_to.attribute10 := p_from.attribute10;
827 p_to.attribute11 := p_from.attribute11;
828 p_to.attribute12 := p_from.attribute12;
829 p_to.attribute13 := p_from.attribute13;
830 p_to.attribute14 := p_from.attribute14;
831 p_to.attribute15 := p_from.attribute15;
832 p_to.created_by := p_from.created_by;
833 p_to.creation_date := p_from.creation_date;
834 p_to.last_updated_by := p_from.last_updated_by;
835 p_to.last_update_date := p_from.last_update_date;
836 p_to.last_update_login := p_from.last_update_login;
837 END migrate;
838
839 ---------------------------------------------------------------------------
840 -- PROCEDURE validate_row
841 ---------------------------------------------------------------------------
842 -----------------------------------------
843 -- validate_row for:OKL_INV_MSSG_ATT_V --
844 -----------------------------------------
845 PROCEDURE validate_row(
846 p_api_version IN NUMBER,
847 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
848 x_return_status OUT NOCOPY VARCHAR2,
849 x_msg_count OUT NOCOPY NUMBER,
850 x_msg_data OUT NOCOPY VARCHAR2,
851 p_imav_rec IN imav_rec_type) IS
852
853 l_api_version CONSTANT NUMBER := 1;
854 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
855 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
856 l_imav_rec imav_rec_type := p_imav_rec;
857 l_ima_rec ima_rec_type;
858 BEGIN
859 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
860 G_PKG_NAME,
861 p_init_msg_list,
862 l_api_version,
863 p_api_version,
864 '_PVT',
865 x_return_status);
866 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
867 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
868 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
869 RAISE OKL_API.G_EXCEPTION_ERROR;
870 END IF;
871 --- Validate all non-missing attributes (Item Level Validation)
872 l_return_status := Validate_Attributes(l_imav_rec);
873 --- If any errors happen abort API
874 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
875 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
877 RAISE OKL_API.G_EXCEPTION_ERROR;
878 END IF;
879 l_return_status := Validate_Record(l_imav_rec);
880 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
881 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
883 RAISE OKL_API.G_EXCEPTION_ERROR;
884 END IF;
885 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
886 EXCEPTION
887 WHEN OKL_API.G_EXCEPTION_ERROR THEN
888 x_return_status := OKL_API.HANDLE_EXCEPTIONS
889 (
890 l_api_name,
891 G_PKG_NAME,
892 'OKL_API.G_RET_STS_ERROR',
893 x_msg_count,
894 x_msg_data,
895 '_PVT'
896 );
897 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
898 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
899 (
900 l_api_name,
901 G_PKG_NAME,
902 'OKL_API.G_RET_STS_UNEXP_ERROR',
903 x_msg_count,
904 x_msg_data,
905 '_PVT'
906 );
907 WHEN OTHERS THEN
908 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
909 (
910 l_api_name,
911 G_PKG_NAME,
912 'OTHERS',
913 x_msg_count,
914 x_msg_data,
915 '_PVT'
916 );
917 END validate_row;
918 ------------------------------------------
919 -- PL/SQL TBL validate_row for:IMAV_TBL --
920 ------------------------------------------
921 PROCEDURE validate_row(
922 p_api_version IN NUMBER,
923 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
924 x_return_status OUT NOCOPY VARCHAR2,
925 x_msg_count OUT NOCOPY NUMBER,
926 x_msg_data OUT NOCOPY VARCHAR2,
927 p_imav_tbl IN imav_tbl_type) IS
928
929 l_api_version CONSTANT NUMBER := 1;
930 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
931 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
932 i NUMBER := 0;
933 -- Begin Post-Generation Change
934 -- overall error status
935 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
936 -- End Post-Generation Change
937
938 BEGIN
939 OKL_API.init_msg_list(p_init_msg_list);
940 -- Make sure PL/SQL table has records in it before passing
941 IF (p_imav_tbl.COUNT > 0) THEN
942 i := p_imav_tbl.FIRST;
943 LOOP
944 validate_row (
945 p_api_version => p_api_version,
946 p_init_msg_list => OKL_API.G_FALSE,
947 x_return_status => x_return_status,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 p_imav_rec => p_imav_tbl(i));
951 -- Begin Post-Generation Change
952 -- store the highest degree of error
953 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
954 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
955 l_overall_status := x_return_status;
956 END IF;
957 END IF;
958 -- End Post-Generation Change
959 EXIT WHEN (i = p_imav_tbl.LAST);
960 i := p_imav_tbl.NEXT(i);
961 END LOOP;
962 -- Begin Post-Generation Change
963 -- return overall status
964 x_return_status := l_overall_status;
965 -- End Post-Generation Change
966 END IF;
967 EXCEPTION
968 WHEN OKL_API.G_EXCEPTION_ERROR THEN
969 x_return_status := OKL_API.HANDLE_EXCEPTIONS
970 (
971 l_api_name,
972 G_PKG_NAME,
973 'OKL_API.G_RET_STS_ERROR',
974 x_msg_count,
975 x_msg_data,
976 '_PVT'
977 );
978 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
979 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
980 (
981 l_api_name,
982 G_PKG_NAME,
983 'OKL_API.G_RET_STS_UNEXP_ERROR',
984 x_msg_count,
985 x_msg_data,
986 '_PVT'
987 );
988 WHEN OTHERS THEN
989 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
990 (
991 l_api_name,
992 G_PKG_NAME,
993 'OTHERS',
994 x_msg_count,
995 x_msg_data,
996 '_PVT'
997 );
998 END validate_row;
999
1000 ---------------------------------------------------------------------------
1001 -- PROCEDURE insert_row
1002 ---------------------------------------------------------------------------
1003 -------------------------------------
1004 -- insert_row for:OKL_INV_MSSG_ATT --
1005 -------------------------------------
1006 PROCEDURE insert_row(
1007 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1008 x_return_status OUT NOCOPY VARCHAR2,
1009 x_msg_count OUT NOCOPY NUMBER,
1010 x_msg_data OUT NOCOPY VARCHAR2,
1011 p_ima_rec IN ima_rec_type,
1012 x_ima_rec OUT NOCOPY ima_rec_type) IS
1013
1014 l_api_version CONSTANT NUMBER := 1;
1015 l_api_name CONSTANT VARCHAR2(30) := 'ATT_insert_row';
1016 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1017 l_ima_rec ima_rec_type := p_ima_rec;
1018 l_def_ima_rec ima_rec_type;
1019 -----------------------------------------
1020 -- Set_Attributes for:OKL_INV_MSSG_ATT --
1021 -----------------------------------------
1022 FUNCTION Set_Attributes (
1023 p_ima_rec IN ima_rec_type,
1024 x_ima_rec OUT NOCOPY ima_rec_type
1025 ) RETURN VARCHAR2 IS
1026 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1027 BEGIN
1028 x_ima_rec := p_ima_rec;
1029 RETURN(l_return_status);
1030 END Set_Attributes;
1031 BEGIN
1032 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1033 p_init_msg_list,
1034 '_PVT',
1035 x_return_status);
1036 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1037 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1038 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1039 RAISE OKL_API.G_EXCEPTION_ERROR;
1040 END IF;
1041 --- Setting item attributes
1042 l_return_status := Set_Attributes(
1043 p_ima_rec, -- IN
1044 l_ima_rec); -- OUT
1045 --- If any errors happen abort API
1046 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1047 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1048 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1049 RAISE OKL_API.G_EXCEPTION_ERROR;
1050 END IF;
1051 INSERT INTO OKL_INV_MSSG_ATT(
1052 id,
1053 cnr_id,
1054 ims_id,
1055 object_version_number,
1056 attribute_category,
1057 attribute1,
1058 attribute2,
1059 attribute3,
1060 attribute4,
1061 attribute5,
1062 attribute6,
1063 attribute7,
1064 attribute8,
1065 attribute9,
1066 attribute10,
1067 attribute11,
1068 attribute12,
1069 attribute13,
1070 attribute14,
1071 attribute15,
1072 created_by,
1073 creation_date,
1074 last_updated_by,
1075 last_update_date,
1076 last_update_login)
1077 VALUES (
1078 l_ima_rec.id,
1079 l_ima_rec.cnr_id,
1080 l_ima_rec.ims_id,
1081 l_ima_rec.object_version_number,
1082 l_ima_rec.attribute_category,
1083 l_ima_rec.attribute1,
1084 l_ima_rec.attribute2,
1085 l_ima_rec.attribute3,
1086 l_ima_rec.attribute4,
1087 l_ima_rec.attribute5,
1088 l_ima_rec.attribute6,
1089 l_ima_rec.attribute7,
1090 l_ima_rec.attribute8,
1091 l_ima_rec.attribute9,
1092 l_ima_rec.attribute10,
1093 l_ima_rec.attribute11,
1094 l_ima_rec.attribute12,
1095 l_ima_rec.attribute13,
1096 l_ima_rec.attribute14,
1097 l_ima_rec.attribute15,
1098 l_ima_rec.created_by,
1099 l_ima_rec.creation_date,
1100 l_ima_rec.last_updated_by,
1101 l_ima_rec.last_update_date,
1102 l_ima_rec.last_update_login);
1103 -- Set OUT values
1104 x_ima_rec := l_ima_rec;
1105 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1106 EXCEPTION
1107 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1108 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1109 (
1110 l_api_name,
1111 G_PKG_NAME,
1112 'OKL_API.G_RET_STS_ERROR',
1113 x_msg_count,
1114 x_msg_data,
1115 '_PVT'
1116 );
1117 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1118 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1119 (
1120 l_api_name,
1121 G_PKG_NAME,
1122 'OKL_API.G_RET_STS_UNEXP_ERROR',
1123 x_msg_count,
1124 x_msg_data,
1125 '_PVT'
1126 );
1127 WHEN OTHERS THEN
1128 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1129 (
1130 l_api_name,
1131 G_PKG_NAME,
1132 'OTHERS',
1133 x_msg_count,
1134 x_msg_data,
1135 '_PVT'
1136 );
1137 END insert_row;
1138 ---------------------------------------
1139 -- insert_row for:OKL_INV_MSSG_ATT_V --
1140 ---------------------------------------
1141 PROCEDURE insert_row(
1142 p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1144 x_return_status OUT NOCOPY VARCHAR2,
1145 x_msg_count OUT NOCOPY NUMBER,
1146 x_msg_data OUT NOCOPY VARCHAR2,
1147 p_imav_rec IN imav_rec_type,
1148 x_imav_rec OUT NOCOPY imav_rec_type) IS
1149
1150 l_api_version CONSTANT NUMBER := 1;
1151 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1152 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1153 l_imav_rec imav_rec_type;
1154 l_def_imav_rec imav_rec_type;
1155 l_ima_rec ima_rec_type;
1156 lx_ima_rec ima_rec_type;
1157 -------------------------------
1158 -- FUNCTION fill_who_columns --
1159 -------------------------------
1160 FUNCTION fill_who_columns (
1161 p_imav_rec IN imav_rec_type
1162 ) RETURN imav_rec_type IS
1163 l_imav_rec imav_rec_type := p_imav_rec;
1164 BEGIN
1165 l_imav_rec.CREATION_DATE := SYSDATE;
1166 l_imav_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1167 l_imav_rec.LAST_UPDATE_DATE := l_imav_rec.CREATION_DATE;
1168 l_imav_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1169 l_imav_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1170 RETURN(l_imav_rec);
1171 END fill_who_columns;
1172 -------------------------------------------
1173 -- Set_Attributes for:OKL_INV_MSSG_ATT_V --
1174 -------------------------------------------
1175 FUNCTION Set_Attributes (
1176 p_imav_rec IN imav_rec_type,
1177 x_imav_rec OUT NOCOPY imav_rec_type
1178 ) RETURN VARCHAR2 IS
1179 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1180 BEGIN
1181 x_imav_rec := p_imav_rec;
1182 x_imav_rec.OBJECT_VERSION_NUMBER := 1;
1183 RETURN(l_return_status);
1184 END Set_Attributes;
1185 BEGIN
1186 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1187 G_PKG_NAME,
1188 p_init_msg_list,
1189 l_api_version,
1190 p_api_version,
1191 '_PVT',
1192 x_return_status);
1193 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1194 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1195 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1196 RAISE OKL_API.G_EXCEPTION_ERROR;
1197 END IF;
1198 l_imav_rec := null_out_defaults(p_imav_rec);
1199 -- Set primary key value
1200 l_imav_rec.ID := get_seq_id;
1201 --- Setting item attributes
1202 l_return_status := Set_Attributes(
1203 l_imav_rec, -- IN
1204 l_def_imav_rec); -- OUT
1205 --- If any errors happen abort API
1206 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1207 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1208 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1209 RAISE OKL_API.G_EXCEPTION_ERROR;
1210 END IF;
1211 l_def_imav_rec := fill_who_columns(l_def_imav_rec);
1212 --- Validate all non-missing attributes (Item Level Validation)
1213 l_return_status := Validate_Attributes(l_def_imav_rec);
1214 --- If any errors happen abort API
1215 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1216 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1217 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1218 RAISE OKL_API.G_EXCEPTION_ERROR;
1219 END IF;
1220 l_return_status := Validate_Record(l_def_imav_rec);
1221 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1223 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1224 RAISE OKL_API.G_EXCEPTION_ERROR;
1225 END IF;
1226 --------------------------------------
1227 -- Move VIEW record to "Child" records
1228 --------------------------------------
1229 migrate(l_def_imav_rec, l_ima_rec);
1230 --------------------------------------------
1231 -- Call the INSERT_ROW for each child record
1232 --------------------------------------------
1233 insert_row(
1234 p_init_msg_list,
1235 x_return_status,
1236 x_msg_count,
1237 x_msg_data,
1238 l_ima_rec,
1239 lx_ima_rec
1240 );
1241 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1242 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1243 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1244 RAISE OKL_API.G_EXCEPTION_ERROR;
1245 END IF;
1246 migrate(lx_ima_rec, l_def_imav_rec);
1247 -- Set OUT values
1248 x_imav_rec := l_def_imav_rec;
1249 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1250 EXCEPTION
1251 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1252 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1253 (
1254 l_api_name,
1255 G_PKG_NAME,
1256 'OKL_API.G_RET_STS_ERROR',
1257 x_msg_count,
1258 x_msg_data,
1259 '_PVT'
1260 );
1261 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1262 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1263 (
1264 l_api_name,
1265 G_PKG_NAME,
1266 'OKL_API.G_RET_STS_UNEXP_ERROR',
1267 x_msg_count,
1268 x_msg_data,
1269 '_PVT'
1270 );
1271 WHEN OTHERS THEN
1272 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1273 (
1274 l_api_name,
1275 G_PKG_NAME,
1276 'OTHERS',
1277 x_msg_count,
1278 x_msg_data,
1279 '_PVT'
1280 );
1281 END insert_row;
1282 ----------------------------------------
1283 -- PL/SQL TBL insert_row for:IMAV_TBL --
1284 ----------------------------------------
1285 PROCEDURE insert_row(
1286 p_api_version IN NUMBER,
1287 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1288 x_return_status OUT NOCOPY VARCHAR2,
1289 x_msg_count OUT NOCOPY NUMBER,
1290 x_msg_data OUT NOCOPY VARCHAR2,
1291 p_imav_tbl IN imav_tbl_type,
1292 x_imav_tbl OUT NOCOPY imav_tbl_type) IS
1293
1294 l_api_version CONSTANT NUMBER := 1;
1295 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1296 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1297 i NUMBER := 0;
1298 -- Begin Post-Generation Change
1299 -- overall error status
1300 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1301 -- End Post-Generation Change
1302
1303 BEGIN
1304 OKL_API.init_msg_list(p_init_msg_list);
1305 -- Make sure PL/SQL table has records in it before passing
1306 IF (p_imav_tbl.COUNT > 0) THEN
1307 i := p_imav_tbl.FIRST;
1308 LOOP
1309 insert_row (
1310 p_api_version => p_api_version,
1311 p_init_msg_list => OKL_API.G_FALSE,
1312 x_return_status => x_return_status,
1313 x_msg_count => x_msg_count,
1314 x_msg_data => x_msg_data,
1315 p_imav_rec => p_imav_tbl(i),
1316 x_imav_rec => x_imav_tbl(i));
1317 -- Begin Post-Generation Change
1318 -- store the highest degree of error
1319 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1320 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1321 l_overall_status := x_return_status;
1322 END IF;
1323 END IF;
1324 -- End Post-Generation Change
1325 EXIT WHEN (i = p_imav_tbl.LAST);
1326 i := p_imav_tbl.NEXT(i);
1327 END LOOP;
1328 -- Begin Post-Generation Change
1329 -- return overall status
1330 x_return_status := l_overall_status;
1331 -- End Post-Generation Change
1332 END IF;
1333 EXCEPTION
1334 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1335 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1336 (
1337 l_api_name,
1338 G_PKG_NAME,
1339 'OKL_API.G_RET_STS_ERROR',
1340 x_msg_count,
1341 x_msg_data,
1342 '_PVT'
1343 );
1344 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1345 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1346 (
1347 l_api_name,
1348 G_PKG_NAME,
1349 'OKL_API.G_RET_STS_UNEXP_ERROR',
1350 x_msg_count,
1351 x_msg_data,
1352 '_PVT'
1353 );
1354 WHEN OTHERS THEN
1355 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1356 (
1357 l_api_name,
1358 G_PKG_NAME,
1359 'OTHERS',
1360 x_msg_count,
1361 x_msg_data,
1362 '_PVT'
1363 );
1364 END insert_row;
1365
1366 ---------------------------------------------------------------------------
1367 -- PROCEDURE lock_row
1368 ---------------------------------------------------------------------------
1369 -----------------------------------
1370 -- lock_row for:OKL_INV_MSSG_ATT --
1371 -----------------------------------
1372 PROCEDURE lock_row(
1373 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1374 x_return_status OUT NOCOPY VARCHAR2,
1375 x_msg_count OUT NOCOPY NUMBER,
1376 x_msg_data OUT NOCOPY VARCHAR2,
1377 p_ima_rec IN ima_rec_type) IS
1378
1379 E_Resource_Busy EXCEPTION;
1380 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1381 CURSOR lock_csr (p_ima_rec IN ima_rec_type) IS
1382 SELECT OBJECT_VERSION_NUMBER
1383 FROM OKL_INV_MSSG_ATT
1384 WHERE ID = p_ima_rec.id
1385 AND OBJECT_VERSION_NUMBER = p_ima_rec.object_version_number
1386 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1387
1388 CURSOR lchk_csr (p_ima_rec IN ima_rec_type) IS
1389 SELECT OBJECT_VERSION_NUMBER
1390 FROM OKL_INV_MSSG_ATT
1391 WHERE ID = p_ima_rec.id;
1392 l_api_version CONSTANT NUMBER := 1;
1393 l_api_name CONSTANT VARCHAR2(30) := 'ATT_lock_row';
1394 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1395 l_object_version_number OKL_INV_MSSG_ATT.OBJECT_VERSION_NUMBER%TYPE;
1396 lc_object_version_number OKL_INV_MSSG_ATT.OBJECT_VERSION_NUMBER%TYPE;
1397 l_row_notfound BOOLEAN := FALSE;
1398 lc_row_notfound BOOLEAN := FALSE;
1399 BEGIN
1400 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1401 p_init_msg_list,
1402 '_PVT',
1403 x_return_status);
1404 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1405 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1406 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1407 RAISE OKL_API.G_EXCEPTION_ERROR;
1408 END IF;
1409 BEGIN
1410 OPEN lock_csr(p_ima_rec);
1411 FETCH lock_csr INTO l_object_version_number;
1412 l_row_notfound := lock_csr%NOTFOUND;
1413 CLOSE lock_csr;
1414 EXCEPTION
1415 WHEN E_Resource_Busy THEN
1416 IF (lock_csr%ISOPEN) THEN
1417 CLOSE lock_csr;
1418 END IF;
1419 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1420 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1421 END;
1422
1423 IF ( l_row_notfound ) THEN
1424 OPEN lchk_csr(p_ima_rec);
1425 FETCH lchk_csr INTO lc_object_version_number;
1426 lc_row_notfound := lchk_csr%NOTFOUND;
1427 CLOSE lchk_csr;
1428 END IF;
1429 IF (lc_row_notfound) THEN
1430 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1431 RAISE OKL_API.G_EXCEPTION_ERROR;
1432 ELSIF lc_object_version_number > p_ima_rec.object_version_number THEN
1433 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1434 RAISE OKL_API.G_EXCEPTION_ERROR;
1435 ELSIF lc_object_version_number <> p_ima_rec.object_version_number THEN
1436 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1437 RAISE OKL_API.G_EXCEPTION_ERROR;
1438 ELSIF lc_object_version_number = -1 THEN
1439 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1440 RAISE OKL_API.G_EXCEPTION_ERROR;
1441 END IF;
1442 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1443 EXCEPTION
1444 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1445 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1446 (
1447 l_api_name,
1448 G_PKG_NAME,
1449 'OKL_API.G_RET_STS_ERROR',
1450 x_msg_count,
1451 x_msg_data,
1452 '_PVT'
1453 );
1454 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1455 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1456 (
1457 l_api_name,
1458 G_PKG_NAME,
1459 'OKL_API.G_RET_STS_UNEXP_ERROR',
1460 x_msg_count,
1461 x_msg_data,
1462 '_PVT'
1463 );
1464 WHEN OTHERS THEN
1465 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1466 (
1467 l_api_name,
1468 G_PKG_NAME,
1469 'OTHERS',
1470 x_msg_count,
1471 x_msg_data,
1472 '_PVT'
1473 );
1474 END lock_row;
1475 -------------------------------------
1476 -- lock_row for:OKL_INV_MSSG_ATT_V --
1477 -------------------------------------
1478 PROCEDURE lock_row(
1479 p_api_version IN NUMBER,
1480 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1481 x_return_status OUT NOCOPY VARCHAR2,
1482 x_msg_count OUT NOCOPY NUMBER,
1483 x_msg_data OUT NOCOPY VARCHAR2,
1484 p_imav_rec IN imav_rec_type) IS
1485
1486 l_api_version CONSTANT NUMBER := 1;
1487 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1488 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1489 l_ima_rec ima_rec_type;
1490 BEGIN
1491 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1492 G_PKG_NAME,
1493 p_init_msg_list,
1494 l_api_version,
1495 p_api_version,
1496 '_PVT',
1497 x_return_status);
1498 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1499 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1500 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1501 RAISE OKL_API.G_EXCEPTION_ERROR;
1502 END IF;
1503 --------------------------------------
1504 -- Move VIEW record to "Child" records
1505 --------------------------------------
1506 migrate(p_imav_rec, l_ima_rec);
1507 --------------------------------------------
1508 -- Call the LOCK_ROW for each child record
1509 --------------------------------------------
1510 lock_row(
1511 p_init_msg_list,
1512 x_return_status,
1513 x_msg_count,
1514 x_msg_data,
1515 l_ima_rec
1516 );
1517 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1518 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1519 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1520 RAISE OKL_API.G_EXCEPTION_ERROR;
1521 END IF;
1522 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1523 EXCEPTION
1524 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1525 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1526 (
1527 l_api_name,
1528 G_PKG_NAME,
1529 'OKL_API.G_RET_STS_ERROR',
1530 x_msg_count,
1531 x_msg_data,
1532 '_PVT'
1533 );
1534 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1535 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1536 (
1537 l_api_name,
1538 G_PKG_NAME,
1539 'OKL_API.G_RET_STS_UNEXP_ERROR',
1540 x_msg_count,
1541 x_msg_data,
1542 '_PVT'
1543 );
1544 WHEN OTHERS THEN
1545 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1546 (
1547 l_api_name,
1548 G_PKG_NAME,
1549 'OTHERS',
1550 x_msg_count,
1551 x_msg_data,
1552 '_PVT'
1553 );
1554 END lock_row;
1555 --------------------------------------
1556 -- PL/SQL TBL lock_row for:IMAV_TBL --
1557 --------------------------------------
1558 PROCEDURE lock_row(
1559 p_api_version IN NUMBER,
1560 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1561 x_return_status OUT NOCOPY VARCHAR2,
1562 x_msg_count OUT NOCOPY NUMBER,
1563 x_msg_data OUT NOCOPY VARCHAR2,
1564 p_imav_tbl IN imav_tbl_type) IS
1565
1566 l_api_version CONSTANT NUMBER := 1;
1567 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1568 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1569 i NUMBER := 0;
1570 -- Begin Post-Generation Change
1571 -- overall error status
1572 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1573 -- End Post-Generation Change
1574
1575 BEGIN
1576 OKL_API.init_msg_list(p_init_msg_list);
1577 -- Make sure PL/SQL table has records in it before passing
1578 IF (p_imav_tbl.COUNT > 0) THEN
1579 i := p_imav_tbl.FIRST;
1580 LOOP
1581 lock_row (
1582 p_api_version => p_api_version,
1583 p_init_msg_list => OKL_API.G_FALSE,
1584 x_return_status => x_return_status,
1585 x_msg_count => x_msg_count,
1586 x_msg_data => x_msg_data,
1587 p_imav_rec => p_imav_tbl(i));
1588 -- Begin Post-Generation Change
1589 -- store the highest degree of error
1590 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1591 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1592 l_overall_status := x_return_status;
1593 END IF;
1594 END IF;
1595 -- End Post-Generation Change
1596 EXIT WHEN (i = p_imav_tbl.LAST);
1597 i := p_imav_tbl.NEXT(i);
1598 END LOOP;
1599 -- Begin Post-Generation Change
1600 -- return overall status
1601 x_return_status := l_overall_status;
1602 -- End Post-Generation Change
1603 END IF;
1604 EXCEPTION
1605 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1606 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1607 (
1608 l_api_name,
1609 G_PKG_NAME,
1610 'OKL_API.G_RET_STS_ERROR',
1611 x_msg_count,
1612 x_msg_data,
1613 '_PVT'
1614 );
1615 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1616 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1617 (
1618 l_api_name,
1619 G_PKG_NAME,
1620 'OKL_API.G_RET_STS_UNEXP_ERROR',
1621 x_msg_count,
1622 x_msg_data,
1623 '_PVT'
1624 );
1625 WHEN OTHERS THEN
1626 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1627 (
1628 l_api_name,
1629 G_PKG_NAME,
1630 'OTHERS',
1631 x_msg_count,
1632 x_msg_data,
1633 '_PVT'
1634 );
1635 END lock_row;
1636
1637 ---------------------------------------------------------------------------
1638 -- PROCEDURE update_row
1639 ---------------------------------------------------------------------------
1640 -------------------------------------
1641 -- update_row for:OKL_INV_MSSG_ATT --
1642 -------------------------------------
1643 PROCEDURE update_row(
1644 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1645 x_return_status OUT NOCOPY VARCHAR2,
1646 x_msg_count OUT NOCOPY NUMBER,
1647 x_msg_data OUT NOCOPY VARCHAR2,
1648 p_ima_rec IN ima_rec_type,
1649 x_ima_rec OUT NOCOPY ima_rec_type) IS
1650
1651 l_api_version CONSTANT NUMBER := 1;
1652 l_api_name CONSTANT VARCHAR2(30) := 'ATT_update_row';
1653 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1654 l_ima_rec ima_rec_type := p_ima_rec;
1655 l_def_ima_rec ima_rec_type;
1656 l_row_notfound BOOLEAN := TRUE;
1657 ----------------------------------
1658 -- FUNCTION populate_new_record --
1659 ----------------------------------
1660 FUNCTION populate_new_record (
1661 p_ima_rec IN ima_rec_type,
1662 x_ima_rec OUT NOCOPY ima_rec_type
1663 ) RETURN VARCHAR2 IS
1664 l_ima_rec ima_rec_type;
1665 l_row_notfound BOOLEAN := TRUE;
1666 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1667 BEGIN
1668 x_ima_rec := p_ima_rec;
1669 -- Get current database values
1670 l_ima_rec := get_rec(p_ima_rec, l_row_notfound);
1671 IF (l_row_notfound) THEN
1672 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1673 END IF;
1674 IF (x_ima_rec.id = OKL_API.G_MISS_NUM)
1675 THEN
1676 x_ima_rec.id := l_ima_rec.id;
1677 END IF;
1678 IF (x_ima_rec.cnr_id = OKL_API.G_MISS_NUM)
1679 THEN
1680 x_ima_rec.cnr_id := l_ima_rec.cnr_id;
1681 END IF;
1682 IF (x_ima_rec.ims_id = OKL_API.G_MISS_NUM)
1683 THEN
1684 x_ima_rec.ims_id := l_ima_rec.ims_id;
1685 END IF;
1686 IF (x_ima_rec.object_version_number = OKL_API.G_MISS_NUM)
1687 THEN
1688 x_ima_rec.object_version_number := l_ima_rec.object_version_number;
1689 END IF;
1690 IF (x_ima_rec.attribute_category = OKL_API.G_MISS_CHAR)
1691 THEN
1692 x_ima_rec.attribute_category := l_ima_rec.attribute_category;
1693 END IF;
1694 IF (x_ima_rec.attribute1 = OKL_API.G_MISS_CHAR)
1695 THEN
1696 x_ima_rec.attribute1 := l_ima_rec.attribute1;
1697 END IF;
1698 IF (x_ima_rec.attribute2 = OKL_API.G_MISS_CHAR)
1699 THEN
1700 x_ima_rec.attribute2 := l_ima_rec.attribute2;
1701 END IF;
1702 IF (x_ima_rec.attribute3 = OKL_API.G_MISS_CHAR)
1703 THEN
1704 x_ima_rec.attribute3 := l_ima_rec.attribute3;
1705 END IF;
1706 IF (x_ima_rec.attribute4 = OKL_API.G_MISS_CHAR)
1707 THEN
1708 x_ima_rec.attribute4 := l_ima_rec.attribute4;
1709 END IF;
1710 IF (x_ima_rec.attribute5 = OKL_API.G_MISS_CHAR)
1711 THEN
1712 x_ima_rec.attribute5 := l_ima_rec.attribute5;
1713 END IF;
1714 IF (x_ima_rec.attribute6 = OKL_API.G_MISS_CHAR)
1715 THEN
1716 x_ima_rec.attribute6 := l_ima_rec.attribute6;
1717 END IF;
1718 IF (x_ima_rec.attribute7 = OKL_API.G_MISS_CHAR)
1719 THEN
1720 x_ima_rec.attribute7 := l_ima_rec.attribute7;
1721 END IF;
1722 IF (x_ima_rec.attribute8 = OKL_API.G_MISS_CHAR)
1723 THEN
1724 x_ima_rec.attribute8 := l_ima_rec.attribute8;
1725 END IF;
1726 IF (x_ima_rec.attribute9 = OKL_API.G_MISS_CHAR)
1727 THEN
1728 x_ima_rec.attribute9 := l_ima_rec.attribute9;
1729 END IF;
1730 IF (x_ima_rec.attribute10 = OKL_API.G_MISS_CHAR)
1731 THEN
1732 x_ima_rec.attribute10 := l_ima_rec.attribute10;
1733 END IF;
1734 IF (x_ima_rec.attribute11 = OKL_API.G_MISS_CHAR)
1735 THEN
1736 x_ima_rec.attribute11 := l_ima_rec.attribute11;
1737 END IF;
1738 IF (x_ima_rec.attribute12 = OKL_API.G_MISS_CHAR)
1739 THEN
1740 x_ima_rec.attribute12 := l_ima_rec.attribute12;
1741 END IF;
1742 IF (x_ima_rec.attribute13 = OKL_API.G_MISS_CHAR)
1743 THEN
1744 x_ima_rec.attribute13 := l_ima_rec.attribute13;
1745 END IF;
1746 IF (x_ima_rec.attribute14 = OKL_API.G_MISS_CHAR)
1747 THEN
1748 x_ima_rec.attribute14 := l_ima_rec.attribute14;
1749 END IF;
1750 IF (x_ima_rec.attribute15 = OKL_API.G_MISS_CHAR)
1751 THEN
1752 x_ima_rec.attribute15 := l_ima_rec.attribute15;
1753 END IF;
1754 IF (x_ima_rec.created_by = OKL_API.G_MISS_NUM)
1755 THEN
1756 x_ima_rec.created_by := l_ima_rec.created_by;
1757 END IF;
1758 IF (x_ima_rec.creation_date = OKL_API.G_MISS_DATE)
1759 THEN
1760 x_ima_rec.creation_date := l_ima_rec.creation_date;
1761 END IF;
1762 IF (x_ima_rec.last_updated_by = OKL_API.G_MISS_NUM)
1763 THEN
1764 x_ima_rec.last_updated_by := l_ima_rec.last_updated_by;
1765 END IF;
1766 IF (x_ima_rec.last_update_date = OKL_API.G_MISS_DATE)
1767 THEN
1768 x_ima_rec.last_update_date := l_ima_rec.last_update_date;
1769 END IF;
1770 IF (x_ima_rec.last_update_login = OKL_API.G_MISS_NUM)
1771 THEN
1772 x_ima_rec.last_update_login := l_ima_rec.last_update_login;
1773 END IF;
1774 RETURN(l_return_status);
1775 END populate_new_record;
1776 -----------------------------------------
1777 -- Set_Attributes for:OKL_INV_MSSG_ATT --
1778 -----------------------------------------
1779 FUNCTION Set_Attributes (
1780 p_ima_rec IN ima_rec_type,
1781 x_ima_rec OUT NOCOPY ima_rec_type
1782 ) RETURN VARCHAR2 IS
1783 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1784 BEGIN
1785 x_ima_rec := p_ima_rec;
1786 RETURN(l_return_status);
1787 END Set_Attributes;
1788 BEGIN
1789 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1790 p_init_msg_list,
1791 '_PVT',
1792 x_return_status);
1793 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1794 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1795 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1796 RAISE OKL_API.G_EXCEPTION_ERROR;
1797 END IF;
1798 --- Setting item attributes
1799 l_return_status := Set_Attributes(
1800 p_ima_rec, -- IN
1801 l_ima_rec); -- OUT
1802 --- If any errors happen abort API
1803 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1804 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1805 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1806 RAISE OKL_API.G_EXCEPTION_ERROR;
1807 END IF;
1808 l_return_status := populate_new_record(l_ima_rec, l_def_ima_rec);
1809 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1810 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1811 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1812 RAISE OKL_API.G_EXCEPTION_ERROR;
1813 END IF;
1814 UPDATE OKL_INV_MSSG_ATT
1815 SET CNR_ID = l_def_ima_rec.cnr_id,
1816 IMS_ID = l_def_ima_rec.ims_id,
1817 OBJECT_VERSION_NUMBER = l_def_ima_rec.object_version_number,
1818 ATTRIBUTE_CATEGORY = l_def_ima_rec.attribute_category,
1819 ATTRIBUTE1 = l_def_ima_rec.attribute1,
1820 ATTRIBUTE2 = l_def_ima_rec.attribute2,
1821 ATTRIBUTE3 = l_def_ima_rec.attribute3,
1822 ATTRIBUTE4 = l_def_ima_rec.attribute4,
1823 ATTRIBUTE5 = l_def_ima_rec.attribute5,
1824 ATTRIBUTE6 = l_def_ima_rec.attribute6,
1825 ATTRIBUTE7 = l_def_ima_rec.attribute7,
1826 ATTRIBUTE8 = l_def_ima_rec.attribute8,
1827 ATTRIBUTE9 = l_def_ima_rec.attribute9,
1828 ATTRIBUTE10 = l_def_ima_rec.attribute10,
1829 ATTRIBUTE11 = l_def_ima_rec.attribute11,
1830 ATTRIBUTE12 = l_def_ima_rec.attribute12,
1831 ATTRIBUTE13 = l_def_ima_rec.attribute13,
1832 ATTRIBUTE14 = l_def_ima_rec.attribute14,
1833 ATTRIBUTE15 = l_def_ima_rec.attribute15,
1834 CREATED_BY = l_def_ima_rec.created_by,
1835 CREATION_DATE = l_def_ima_rec.creation_date,
1836 LAST_UPDATED_BY = l_def_ima_rec.last_updated_by,
1837 LAST_UPDATE_DATE = l_def_ima_rec.last_update_date,
1838 LAST_UPDATE_LOGIN = l_def_ima_rec.last_update_login
1839 WHERE ID = l_def_ima_rec.id;
1840
1841 x_ima_rec := l_def_ima_rec;
1842 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1843 EXCEPTION
1844 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1845 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1846 (
1847 l_api_name,
1848 G_PKG_NAME,
1849 'OKL_API.G_RET_STS_ERROR',
1850 x_msg_count,
1851 x_msg_data,
1852 '_PVT'
1853 );
1854 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1855 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1856 (
1857 l_api_name,
1858 G_PKG_NAME,
1859 'OKL_API.G_RET_STS_UNEXP_ERROR',
1860 x_msg_count,
1861 x_msg_data,
1862 '_PVT'
1863 );
1864 WHEN OTHERS THEN
1865 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1866 (
1867 l_api_name,
1868 G_PKG_NAME,
1869 'OTHERS',
1870 x_msg_count,
1871 x_msg_data,
1872 '_PVT'
1873 );
1874 END update_row;
1875 ---------------------------------------
1876 -- update_row for:OKL_INV_MSSG_ATT_V --
1877 ---------------------------------------
1878 PROCEDURE update_row(
1879 p_api_version IN NUMBER,
1880 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 x_msg_count OUT NOCOPY NUMBER,
1883 x_msg_data OUT NOCOPY VARCHAR2,
1884 p_imav_rec IN imav_rec_type,
1885 x_imav_rec OUT NOCOPY imav_rec_type) IS
1886
1887 l_api_version CONSTANT NUMBER := 1;
1888 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1889 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1890 l_imav_rec imav_rec_type := p_imav_rec;
1891 l_def_imav_rec imav_rec_type;
1892 l_ima_rec ima_rec_type;
1893 lx_ima_rec ima_rec_type;
1894 -------------------------------
1895 -- FUNCTION fill_who_columns --
1896 -------------------------------
1897 FUNCTION fill_who_columns (
1898 p_imav_rec IN imav_rec_type
1899 ) RETURN imav_rec_type IS
1900 l_imav_rec imav_rec_type := p_imav_rec;
1901 BEGIN
1902 l_imav_rec.LAST_UPDATE_DATE := SYSDATE;
1903 l_imav_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1904 l_imav_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1905 RETURN(l_imav_rec);
1906 END fill_who_columns;
1907 ----------------------------------
1908 -- FUNCTION populate_new_record --
1909 ----------------------------------
1910 FUNCTION populate_new_record (
1911 p_imav_rec IN imav_rec_type,
1912 x_imav_rec OUT NOCOPY imav_rec_type
1913 ) RETURN VARCHAR2 IS
1914 l_imav_rec imav_rec_type;
1915 l_row_notfound BOOLEAN := TRUE;
1916 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1917 BEGIN
1918 x_imav_rec := p_imav_rec;
1919 -- Get current database values
1920 l_imav_rec := get_rec(p_imav_rec, l_row_notfound);
1921 IF (l_row_notfound) THEN
1922 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1923 END IF;
1924 IF (x_imav_rec.id = OKL_API.G_MISS_NUM)
1925 THEN
1926 x_imav_rec.id := l_imav_rec.id;
1927 END IF;
1928 IF (x_imav_rec.object_version_number = OKL_API.G_MISS_NUM)
1929 THEN
1930 x_imav_rec.object_version_number := l_imav_rec.object_version_number;
1931 END IF;
1932 IF (x_imav_rec.cnr_id = OKL_API.G_MISS_NUM)
1933 THEN
1934 x_imav_rec.cnr_id := l_imav_rec.cnr_id;
1935 END IF;
1936 IF (x_imav_rec.ims_id = OKL_API.G_MISS_NUM)
1937 THEN
1938 x_imav_rec.ims_id := l_imav_rec.ims_id;
1939 END IF;
1940 IF (x_imav_rec.attribute_category = OKL_API.G_MISS_CHAR)
1941 THEN
1942 x_imav_rec.attribute_category := l_imav_rec.attribute_category;
1943 END IF;
1944 IF (x_imav_rec.attribute1 = OKL_API.G_MISS_CHAR)
1945 THEN
1946 x_imav_rec.attribute1 := l_imav_rec.attribute1;
1947 END IF;
1948 IF (x_imav_rec.attribute2 = OKL_API.G_MISS_CHAR)
1949 THEN
1950 x_imav_rec.attribute2 := l_imav_rec.attribute2;
1951 END IF;
1952 IF (x_imav_rec.attribute3 = OKL_API.G_MISS_CHAR)
1953 THEN
1954 x_imav_rec.attribute3 := l_imav_rec.attribute3;
1955 END IF;
1956 IF (x_imav_rec.attribute4 = OKL_API.G_MISS_CHAR)
1957 THEN
1958 x_imav_rec.attribute4 := l_imav_rec.attribute4;
1959 END IF;
1960 IF (x_imav_rec.attribute5 = OKL_API.G_MISS_CHAR)
1961 THEN
1962 x_imav_rec.attribute5 := l_imav_rec.attribute5;
1963 END IF;
1964 IF (x_imav_rec.attribute6 = OKL_API.G_MISS_CHAR)
1965 THEN
1966 x_imav_rec.attribute6 := l_imav_rec.attribute6;
1967 END IF;
1968 IF (x_imav_rec.attribute7 = OKL_API.G_MISS_CHAR)
1969 THEN
1970 x_imav_rec.attribute7 := l_imav_rec.attribute7;
1971 END IF;
1972 IF (x_imav_rec.attribute8 = OKL_API.G_MISS_CHAR)
1973 THEN
1974 x_imav_rec.attribute8 := l_imav_rec.attribute8;
1975 END IF;
1976 IF (x_imav_rec.attribute9 = OKL_API.G_MISS_CHAR)
1977 THEN
1978 x_imav_rec.attribute9 := l_imav_rec.attribute9;
1979 END IF;
1980 IF (x_imav_rec.attribute10 = OKL_API.G_MISS_CHAR)
1981 THEN
1982 x_imav_rec.attribute10 := l_imav_rec.attribute10;
1983 END IF;
1984 IF (x_imav_rec.attribute11 = OKL_API.G_MISS_CHAR)
1985 THEN
1986 x_imav_rec.attribute11 := l_imav_rec.attribute11;
1987 END IF;
1988 IF (x_imav_rec.attribute12 = OKL_API.G_MISS_CHAR)
1989 THEN
1990 x_imav_rec.attribute12 := l_imav_rec.attribute12;
1991 END IF;
1992 IF (x_imav_rec.attribute13 = OKL_API.G_MISS_CHAR)
1993 THEN
1994 x_imav_rec.attribute13 := l_imav_rec.attribute13;
1995 END IF;
1996 IF (x_imav_rec.attribute14 = OKL_API.G_MISS_CHAR)
1997 THEN
1998 x_imav_rec.attribute14 := l_imav_rec.attribute14;
1999 END IF;
2000 IF (x_imav_rec.attribute15 = OKL_API.G_MISS_CHAR)
2001 THEN
2002 x_imav_rec.attribute15 := l_imav_rec.attribute15;
2003 END IF;
2004 IF (x_imav_rec.created_by = OKL_API.G_MISS_NUM)
2005 THEN
2006 x_imav_rec.created_by := l_imav_rec.created_by;
2007 END IF;
2008 IF (x_imav_rec.creation_date = OKL_API.G_MISS_DATE)
2009 THEN
2010 x_imav_rec.creation_date := l_imav_rec.creation_date;
2011 END IF;
2012 IF (x_imav_rec.last_updated_by = OKL_API.G_MISS_NUM)
2013 THEN
2014 x_imav_rec.last_updated_by := l_imav_rec.last_updated_by;
2015 END IF;
2016 IF (x_imav_rec.last_update_date = OKL_API.G_MISS_DATE)
2017 THEN
2018 x_imav_rec.last_update_date := l_imav_rec.last_update_date;
2019 END IF;
2020 IF (x_imav_rec.last_update_login = OKL_API.G_MISS_NUM)
2021 THEN
2022 x_imav_rec.last_update_login := l_imav_rec.last_update_login;
2023 END IF;
2024 RETURN(l_return_status);
2025 END populate_new_record;
2026 -------------------------------------------
2027 -- Set_Attributes for:OKL_INV_MSSG_ATT_V --
2028 -------------------------------------------
2029 FUNCTION Set_Attributes (
2030 p_imav_rec IN imav_rec_type,
2031 x_imav_rec OUT NOCOPY imav_rec_type
2032 ) RETURN VARCHAR2 IS
2033 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2034 BEGIN
2035 x_imav_rec := p_imav_rec;
2036 x_imav_rec.OBJECT_VERSION_NUMBER := NVL(x_imav_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2037 RETURN(l_return_status);
2038 END Set_Attributes;
2039 BEGIN
2040 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2041 G_PKG_NAME,
2042 p_init_msg_list,
2043 l_api_version,
2044 p_api_version,
2045 '_PVT',
2046 x_return_status);
2047 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2048 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2049 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2050 RAISE OKL_API.G_EXCEPTION_ERROR;
2051 END IF;
2052 --- Setting item attributes
2053 l_return_status := Set_Attributes(
2054 p_imav_rec, -- IN
2055 l_imav_rec); -- OUT
2056 --- If any errors happen abort API
2057 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2058 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2059 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2060 RAISE OKL_API.G_EXCEPTION_ERROR;
2061 END IF;
2062 l_return_status := populate_new_record(l_imav_rec, l_def_imav_rec);
2063 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2064 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2065 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2066 RAISE OKL_API.G_EXCEPTION_ERROR;
2067 END IF;
2068 l_def_imav_rec := fill_who_columns(l_def_imav_rec);
2069 --- Validate all non-missing attributes (Item Level Validation)
2070 l_return_status := Validate_Attributes(l_def_imav_rec);
2071 --- If any errors happen abort API
2072 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2073 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2074 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2075 RAISE OKL_API.G_EXCEPTION_ERROR;
2076 END IF;
2077 l_return_status := Validate_Record(l_def_imav_rec);
2078 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2080 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2081 RAISE OKL_API.G_EXCEPTION_ERROR;
2082 END IF;
2083
2084 --------------------------------------
2085 -- Move VIEW record to "Child" records
2086 --------------------------------------
2087 migrate(l_def_imav_rec, l_ima_rec);
2088 --------------------------------------------
2089 -- Call the UPDATE_ROW for each child record
2090 --------------------------------------------
2091 update_row(
2092 p_init_msg_list,
2093 x_return_status,
2094 x_msg_count,
2095 x_msg_data,
2096 l_ima_rec,
2097 lx_ima_rec
2098 );
2099 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2100 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2101 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2102 RAISE OKL_API.G_EXCEPTION_ERROR;
2103 END IF;
2104 migrate(lx_ima_rec, l_def_imav_rec);
2105 x_imav_rec := l_def_imav_rec;
2106 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2107 EXCEPTION
2108 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2109 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2110 (
2111 l_api_name,
2112 G_PKG_NAME,
2113 'OKL_API.G_RET_STS_ERROR',
2114 x_msg_count,
2115 x_msg_data,
2116 '_PVT'
2117 );
2118 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2119 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2120 (
2121 l_api_name,
2122 G_PKG_NAME,
2123 'OKL_API.G_RET_STS_UNEXP_ERROR',
2124 x_msg_count,
2125 x_msg_data,
2126 '_PVT'
2127 );
2128 WHEN OTHERS THEN
2129 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2130 (
2131 l_api_name,
2132 G_PKG_NAME,
2133 'OTHERS',
2134 x_msg_count,
2135 x_msg_data,
2136 '_PVT'
2137 );
2138 END update_row;
2139 ----------------------------------------
2140 -- PL/SQL TBL update_row for:IMAV_TBL --
2141 ----------------------------------------
2142 PROCEDURE update_row(
2143 p_api_version IN NUMBER,
2144 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2145 x_return_status OUT NOCOPY VARCHAR2,
2146 x_msg_count OUT NOCOPY NUMBER,
2147 x_msg_data OUT NOCOPY VARCHAR2,
2148 p_imav_tbl IN imav_tbl_type,
2149 x_imav_tbl OUT NOCOPY imav_tbl_type) IS
2150
2151 l_api_version CONSTANT NUMBER := 1;
2152 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2153 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2154 i NUMBER := 0;
2155 -- Begin Post-Generation Change
2156 -- overall error status
2157 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2158 -- End Post-Generation Change
2159
2160 BEGIN
2161 OKL_API.init_msg_list(p_init_msg_list);
2162 -- Make sure PL/SQL table has records in it before passing
2163 IF (p_imav_tbl.COUNT > 0) THEN
2164 i := p_imav_tbl.FIRST;
2165 LOOP
2166 update_row (
2167 p_api_version => p_api_version,
2168 p_init_msg_list => OKL_API.G_FALSE,
2169 x_return_status => x_return_status,
2170 x_msg_count => x_msg_count,
2171 x_msg_data => x_msg_data,
2172 p_imav_rec => p_imav_tbl(i),
2173 x_imav_rec => x_imav_tbl(i));
2174 -- Begin Post-Generation Change
2175 -- store the highest degree of error
2176 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2177 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
2178 l_overall_status := x_return_status;
2179 END IF;
2180 END IF;
2181 -- End Post-Generation Change
2182 EXIT WHEN (i = p_imav_tbl.LAST);
2183 i := p_imav_tbl.NEXT(i);
2184 END LOOP;
2185 -- Begin Post-Generation Change
2186 -- return overall status
2187 x_return_status := l_overall_status;
2188 -- End Post-Generation Change
2189 END IF;
2190 EXCEPTION
2191 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2192 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2193 (
2194 l_api_name,
2195 G_PKG_NAME,
2196 'OKL_API.G_RET_STS_ERROR',
2197 x_msg_count,
2198 x_msg_data,
2199 '_PVT'
2200 );
2201 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2202 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2203 (
2204 l_api_name,
2205 G_PKG_NAME,
2206 'OKL_API.G_RET_STS_UNEXP_ERROR',
2207 x_msg_count,
2208 x_msg_data,
2209 '_PVT'
2210 );
2211 WHEN OTHERS THEN
2212 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2213 (
2214 l_api_name,
2215 G_PKG_NAME,
2216 'OTHERS',
2217 x_msg_count,
2218 x_msg_data,
2219 '_PVT'
2220 );
2221 END update_row;
2222
2223 ---------------------------------------------------------------------------
2224 -- PROCEDURE delete_row
2225 ---------------------------------------------------------------------------
2226 -------------------------------------
2227 -- delete_row for:OKL_INV_MSSG_ATT --
2228 -------------------------------------
2229 PROCEDURE delete_row(
2230 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2231 x_return_status OUT NOCOPY VARCHAR2,
2232 x_msg_count OUT NOCOPY NUMBER,
2233 x_msg_data OUT NOCOPY VARCHAR2,
2234 p_ima_rec IN ima_rec_type) IS
2235
2236 l_api_version CONSTANT NUMBER := 1;
2237 l_api_name CONSTANT VARCHAR2(30) := 'ATT_delete_row';
2238 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2239 l_ima_rec ima_rec_type:= p_ima_rec;
2240 l_row_notfound BOOLEAN := TRUE;
2241 BEGIN
2242 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2243 p_init_msg_list,
2244 '_PVT',
2245 x_return_status);
2246 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2247 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2248 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2249 RAISE OKL_API.G_EXCEPTION_ERROR;
2250 END IF;
2251 DELETE FROM OKL_INV_MSSG_ATT
2252 WHERE ID = l_ima_rec.id;
2253
2254 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2255 EXCEPTION
2256 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2257 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2258 (
2259 l_api_name,
2260 G_PKG_NAME,
2261 'OKL_API.G_RET_STS_ERROR',
2262 x_msg_count,
2263 x_msg_data,
2264 '_PVT'
2265 );
2266 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2267 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2268 (
2269 l_api_name,
2270 G_PKG_NAME,
2271 'OKL_API.G_RET_STS_UNEXP_ERROR',
2272 x_msg_count,
2273 x_msg_data,
2274 '_PVT'
2275 );
2276 WHEN OTHERS THEN
2277 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2278 (
2279 l_api_name,
2280 G_PKG_NAME,
2281 'OTHERS',
2282 x_msg_count,
2283 x_msg_data,
2284 '_PVT'
2285 );
2286 END delete_row;
2287 ---------------------------------------
2288 -- delete_row for:OKL_INV_MSSG_ATT_V --
2289 ---------------------------------------
2290 PROCEDURE delete_row(
2291 p_api_version IN NUMBER,
2292 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2293 x_return_status OUT NOCOPY VARCHAR2,
2294 x_msg_count OUT NOCOPY NUMBER,
2295 x_msg_data OUT NOCOPY VARCHAR2,
2296 p_imav_rec IN imav_rec_type) IS
2297
2298 l_api_version CONSTANT NUMBER := 1;
2299 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2300 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2301 l_imav_rec imav_rec_type := p_imav_rec;
2302 l_ima_rec ima_rec_type;
2303 BEGIN
2304 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2305 G_PKG_NAME,
2306 p_init_msg_list,
2307 l_api_version,
2308 p_api_version,
2309 '_PVT',
2310 x_return_status);
2311 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2312 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2313 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2314 RAISE OKL_API.G_EXCEPTION_ERROR;
2315 END IF;
2316 --------------------------------------
2317 -- Move VIEW record to "Child" records
2318 --------------------------------------
2319 migrate(l_imav_rec, l_ima_rec);
2320 --------------------------------------------
2321 -- Call the DELETE_ROW for each child record
2322 --------------------------------------------
2323 delete_row(
2324 p_init_msg_list,
2325 x_return_status,
2326 x_msg_count,
2327 x_msg_data,
2328 l_ima_rec
2329 );
2330 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2331 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2332 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2333 RAISE OKL_API.G_EXCEPTION_ERROR;
2334 END IF;
2335 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2336 EXCEPTION
2337 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2338 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2339 (
2340 l_api_name,
2341 G_PKG_NAME,
2342 'OKL_API.G_RET_STS_ERROR',
2343 x_msg_count,
2344 x_msg_data,
2345 '_PVT'
2346 );
2347 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2348 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2349 (
2350 l_api_name,
2351 G_PKG_NAME,
2352 'OKL_API.G_RET_STS_UNEXP_ERROR',
2353 x_msg_count,
2354 x_msg_data,
2355 '_PVT'
2356 );
2357 WHEN OTHERS THEN
2358 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2359 (
2360 l_api_name,
2361 G_PKG_NAME,
2362 'OTHERS',
2363 x_msg_count,
2364 x_msg_data,
2365 '_PVT'
2366 );
2367 END delete_row;
2368 ----------------------------------------
2369 -- PL/SQL TBL delete_row for:IMAV_TBL --
2370 ----------------------------------------
2371 PROCEDURE delete_row(
2372 p_api_version IN NUMBER,
2373 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2374 x_return_status OUT NOCOPY VARCHAR2,
2375 x_msg_count OUT NOCOPY NUMBER,
2376 x_msg_data OUT NOCOPY VARCHAR2,
2377 p_imav_tbl IN imav_tbl_type) IS
2378
2379 l_api_version CONSTANT NUMBER := 1;
2380 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2381 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2382 i NUMBER := 0;
2383 -- Begin Post-Generation Change
2384 -- overall error status
2385 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2386 -- End Post-Generation Change
2387
2388 BEGIN
2389 OKL_API.init_msg_list(p_init_msg_list);
2390 -- Make sure PL/SQL table has records in it before passing
2391 IF (p_imav_tbl.COUNT > 0) THEN
2392 i := p_imav_tbl.FIRST;
2393 LOOP
2394 delete_row (
2395 p_api_version => p_api_version,
2396 p_init_msg_list => OKL_API.G_FALSE,
2397 x_return_status => x_return_status,
2398 x_msg_count => x_msg_count,
2399 x_msg_data => x_msg_data,
2400 p_imav_rec => p_imav_tbl(i));
2401 -- Begin Post-Generation Change
2402 -- store the highest degree of error
2403 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2404 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
2405 l_overall_status := x_return_status;
2406 END IF;
2407 END IF;
2408 -- End Post-Generation Change
2409 EXIT WHEN (i = p_imav_tbl.LAST);
2410 i := p_imav_tbl.NEXT(i);
2411 END LOOP;
2412 -- Begin Post-Generation Change
2413 -- return overall status
2414 x_return_status := l_overall_status;
2415 -- End Post-Generation Change
2416 END IF;
2417 EXCEPTION
2418 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2419 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2420 (
2421 l_api_name,
2422 G_PKG_NAME,
2423 'OKL_API.G_RET_STS_ERROR',
2424 x_msg_count,
2425 x_msg_data,
2426 '_PVT'
2427 );
2428 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2429 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2430 (
2431 l_api_name,
2432 G_PKG_NAME,
2433 'OKL_API.G_RET_STS_UNEXP_ERROR',
2434 x_msg_count,
2435 x_msg_data,
2436 '_PVT'
2437 );
2438 WHEN OTHERS THEN
2439 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2440 (
2441 l_api_name,
2442 G_PKG_NAME,
2443 'OTHERS',
2444 x_msg_count,
2445 x_msg_data,
2446 '_PVT'
2447 );
2448 END delete_row;
2449 END OKL_IMA_PVT;