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