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