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