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