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