[Home] [Help]
PACKAGE BODY: APPS.OKL_POPULATE_PRCENG_RESULT_PUB
Source
1 PACKAGE BODY OKL_POPULATE_PRCENG_RESULT_PUB AS
2 /* $Header: OKLPPERB.pls 115.22 2004/04/13 10:55:44 rnaik noship $ */
3
4
5 --------------------------------------------------------------------------------
6 -- FUNCTION: feb_date
7 -- This is a temporary fix for the wrong dates returned by SuperTrump e.g. 02/30
8 -- The function parses the given date and if the date is 02/30 returns 02/29 or
9 -- 02/28 if it is a leap year or not respectively.
10 --------------------------------------------------------------------------------
11
12 FUNCTION correct_feb_date (p_date VARCHAR2)
13 RETURN VARCHAR2
14 IS
15 l_sre_date VARCHAR2(100);
16 l_feb_date VARCHAR2(100);
17 l_year VARCHAR2(100);
18 BEGIN
19 l_sre_date := p_date;
20 l_feb_date := SUBSTR(l_sre_date, 6,5);
21 l_year := SUBSTR(l_sre_date, 1,4);
22
23 IF(l_feb_date = '02-30' OR l_feb_date = '02-29') THEN
24 l_feb_date := '02-28';
25 IF MOD (TO_NUMBER(l_year), 4) = 0 THEN
26 IF (MOD (TO_NUMBER(l_year), 100) = 0) THEN
27 IF (MOD (TO_NUMBER(l_year), 400) = 0) THEN
28 l_feb_date := '02-29';
29 END IF;
30 ELSE
31 l_feb_date := '02-29';
32 END IF;
33 END IF;
34 l_year := SUBSTR(l_sre_date, 1,5);
35 l_sre_date := l_year || l_feb_date;
36 END IF;
37 RETURN l_sre_date;
38 END correct_feb_date;
39
40
41
42 ---------------------------------------------------------------------------
43 -- PROCEDURE populate_sif_rets for: OKL_SIF_RETS_V
44 ---------------------------------------------------------------------------
45 PROCEDURE populate_sif_rets(p_api_version IN NUMBER := 1.0,
46 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_id OUT NOCOPY NUMBER,
49 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
50 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
51 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
52 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
53 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
54 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
55 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
56 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
57 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM,
58 p_date_processed IN DATE
59
60 ) IS
61 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
62 l_api_version CONSTANT NUMBER := 1;
63 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
64 l_sirv_rec sirv_rec_type;
65 x_sirv_rec sirv_rec_type;
66 x_msg_data VARCHAR2(400);
67 x_msg_count NUMBER ;
68
69
70 BEGIN
71
72 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
73 p_pkg_name => G_PKG_NAME,
74 p_init_msg_list => p_init_msg_list,
75 l_api_version => l_api_version,
76 p_api_version => p_api_version,
77 p_api_type => G_API_TYPE,
78 x_return_status => l_return_status);
79 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
80 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
81 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
82 RAISE G_EXCEPTION_ERROR;
83 END IF;
84
85 l_sirv_rec.transaction_number := p_transaction_number;
86 l_sirv_rec.srt_code := p_srt_code;
87
88 l_sirv_rec.yield_name := p_yield_name;
89 l_sirv_rec.index_number := p_index_number;
90
91 IF(p_effective_pre_tax_yield = -100)
92 THEN
93 l_sirv_rec.effective_after_tax_yield := NULL;
94 ELSE
95 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
96 END IF;
97
98 IF(p_effective_after_tax_yield = -100)
99 THEN
100 l_sirv_rec.effective_after_tax_yield := NULL;
101 ELSE
102 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
103 END IF;
104
105 IF(p_nominal_pre_tax_yield = -100)
106 THEN
107 l_sirv_rec.nominal_pre_tax_yield := NULL;
108 ELSE
109 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
110 END IF;
111
112 IF(p_nominal_after_tax_yield = -100)
113 THEN
114 l_sirv_rec.nominal_after_tax_yield := NULL;
115 ELSE
116 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
117 END IF;
118
119
120 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
121 l_sirv_rec.implicit_interest_rate := NULL;
122 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
123
124 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
125 -- l_sirv_rec.date_processed := p_date_processed;
126
127
128 /* Call main API */
129 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => p_api_version,
130 p_init_msg_list => p_init_msg_list,
131 x_return_status => l_return_status,
132 x_msg_count => x_msg_count,
133 x_msg_data => x_msg_data,
134 p_sirv_rec => l_sirv_rec,
135 x_sirv_rec => x_sirv_rec);
136 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
137 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
138 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
139 RAISE G_EXCEPTION_ERROR;
140 END IF;
141
142 -- Assign id returned to corresponding out parameter
143 x_id := x_sirv_rec.id;
144 -- Assign record returned by private api to local record
145 l_sirv_rec := x_sirv_rec;
146
147
148
149 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
150 x_msg_data => x_msg_data);
151 x_return_status := l_return_status;
152 EXCEPTION
153 WHEN G_EXCEPTION_ERROR THEN
154 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
155 p_pkg_name => G_PKG_NAME,
156 p_exc_name => G_EXC_NAME_ERROR,
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data,
159 p_api_type => G_API_TYPE);
160 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
161 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
162 p_pkg_name => G_PKG_NAME,
163 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
164 x_msg_count => x_msg_count,
165 x_msg_data => x_msg_data,
166 p_api_type => G_API_TYPE);
167 WHEN OTHERS THEN
168 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
169 p_pkg_name => G_PKG_NAME,
170 p_exc_name => G_EXC_NAME_OTHERS,
171 x_msg_count => x_msg_count,
172 x_msg_data => x_msg_data,
173 p_api_type => G_API_TYPE);
174 END populate_sif_rets;
175
176 -- mvasudev , 04/24/2002
177 ---------------------------------------------------------------------------
178 -- PROCEDURE populate_sif_rets for: OKL_SIF_RETS_V
179 ---------------------------------------------------------------------------
180 PROCEDURE populate_sif_rets(x_return_status OUT NOCOPY VARCHAR2,
181 x_id OUT NOCOPY NUMBER,
182 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
183 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
184 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
185 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
186 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
187 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
188 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
189 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
190 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM,
191 p_date_processed IN DATE
192
193 ) IS
194 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
195 l_api_version CONSTANT NUMBER := 1;
196 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
197 l_sirv_rec sirv_rec_type;
198 x_sirv_rec sirv_rec_type;
199 x_msg_data VARCHAR2(400);
200 x_msg_count NUMBER ;
201
202 l_init_msg_list VARCHAR2(1) := 'F';
203
204
205 BEGIN
206
207 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
208 p_pkg_name => G_PKG_NAME,
209 p_init_msg_list => l_init_msg_list,
210 l_api_version => l_api_version,
211 p_api_version => l_api_version,
212 p_api_type => G_API_TYPE,
213 x_return_status => l_return_status);
214 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
215 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
216 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
217 RAISE G_EXCEPTION_ERROR;
218 END IF;
219
220 l_sirv_rec.transaction_number := p_transaction_number;
221 l_sirv_rec.srt_code := p_srt_code;
222
223 l_sirv_rec.yield_name := p_yield_name;
224 l_sirv_rec.index_number := p_index_number;
225
226 IF(p_effective_pre_tax_yield = -100)
227 THEN
228 l_sirv_rec.effective_after_tax_yield := NULL;
229 ELSE
230 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
231 END IF;
232
233 IF(p_effective_after_tax_yield = -100)
234 THEN
235 l_sirv_rec.effective_after_tax_yield := NULL;
236 ELSE
237 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
238 END IF;
239
240 IF(p_nominal_pre_tax_yield = -100)
241 THEN
242 l_sirv_rec.nominal_pre_tax_yield := NULL;
243 ELSE
244 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
245 END IF;
246
247 IF(p_nominal_after_tax_yield = -100)
248 THEN
249 l_sirv_rec.nominal_after_tax_yield := NULL;
250 ELSE
251 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
252 END IF;
253
254
255 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
256 l_sirv_rec.implicit_interest_rate := NULL;
257 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
258
259 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
260 -- l_sirv_rec.date_processed := p_date_processed;
261
262
263 /* Call main API */
264 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => l_api_version,
265 p_init_msg_list => l_init_msg_list,
266 x_return_status => l_return_status,
267 x_msg_count => x_msg_count,
268 x_msg_data => x_msg_data,
269 p_sirv_rec => l_sirv_rec,
270 x_sirv_rec => x_sirv_rec);
271 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
272 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
273 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
274 RAISE G_EXCEPTION_ERROR;
275 END IF;
276
277 -- Assign id returned to corresponding out parameter
278 x_id := x_sirv_rec.id;
279 -- Assign record returned by private api to local record
280 l_sirv_rec := x_sirv_rec;
281
282
283
284 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
285 x_msg_data => x_msg_data);
286 x_return_status := l_return_status;
287 EXCEPTION
288 WHEN G_EXCEPTION_ERROR THEN
289 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
290 p_pkg_name => G_PKG_NAME,
291 p_exc_name => G_EXC_NAME_ERROR,
292 x_msg_count => x_msg_count,
293 x_msg_data => x_msg_data,
294 p_api_type => G_API_TYPE);
295 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
296 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
297 p_pkg_name => G_PKG_NAME,
298 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_api_type => G_API_TYPE);
302 WHEN OTHERS THEN
303 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
304 p_pkg_name => G_PKG_NAME,
305 p_exc_name => G_EXC_NAME_OTHERS,
306 x_msg_count => x_msg_count,
307 x_msg_data => x_msg_data,
308 p_api_type => G_API_TYPE);
309 END populate_sif_rets;
310 -- mvasudev , 04/24/2002 end
311
312 ----------------------------------------------------------------------------------------
313 -- PROCEUDRE populate_sif_rets
314 ----------------------------------------------------------------------------------------
315
316 PROCEDURE populate_sif_rets(p_api_version IN NUMBER := 1.0,
317 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_id OUT NOCOPY NUMBER,
320 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
321 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
322 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
323 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
324 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
325 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
326 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
327 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
328 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM
329 ) IS
330 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
331 l_api_version CONSTANT NUMBER := 1;
332 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
333 l_sirv_rec sirv_rec_type;
334 x_sirv_rec sirv_rec_type;
335 x_msg_data VARCHAR2(400);
336 x_msg_count NUMBER ;
337
338
339 BEGIN
340
341 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
342 p_pkg_name => G_PKG_NAME,
343 p_init_msg_list => p_init_msg_list,
344 l_api_version => l_api_version,
345 p_api_version => p_api_version,
346 p_api_type => G_API_TYPE,
347 x_return_status => l_return_status);
348 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
349 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
350 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
351 RAISE G_EXCEPTION_ERROR;
352 END IF;
353
354 l_sirv_rec.transaction_number := p_transaction_number;
355 l_sirv_rec.srt_code := p_srt_code;
356
357 l_sirv_rec.yield_name := p_yield_name;
358 l_sirv_rec.index_number := p_index_number;
359
360 IF(p_effective_pre_tax_yield = -100)
361 THEN
362 l_sirv_rec.effective_after_tax_yield := NULL;
363 ELSE
364 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
365 END IF;
366
367 IF(p_effective_after_tax_yield = -100)
368 THEN
369 l_sirv_rec.effective_after_tax_yield := NULL;
370 ELSE
371 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
372 END IF;
373
374 IF(p_nominal_pre_tax_yield = -100)
375 THEN
376 l_sirv_rec.nominal_pre_tax_yield := NULL;
377 ELSE
378 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
379 END IF;
380
381 IF(p_nominal_after_tax_yield = -100)
382 THEN
383 l_sirv_rec.nominal_after_tax_yield := NULL;
384 ELSE
385 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
386 END IF;
387
388 /*
389 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
390 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
391 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
392 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
393 */
394 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
395 l_sirv_rec.implicit_interest_rate := NULL;
396 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
397
398 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
399
400
401 /* Call main API */
402 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => p_api_version,
403 p_init_msg_list => p_init_msg_list,
404 x_return_status => l_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data,
407 p_sirv_rec => l_sirv_rec,
408 x_sirv_rec => x_sirv_rec);
409 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
410 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
411 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
412 RAISE G_EXCEPTION_ERROR;
413 END IF;
414
415 -- Assign id returned to corresponding out parameter
416 x_id := x_sirv_rec.id;
417 -- Assign record returned by private api to local record
418 l_sirv_rec := x_sirv_rec;
419
420
421
422 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
423 x_msg_data => x_msg_data);
424 x_return_status := l_return_status;
425 EXCEPTION
426 WHEN G_EXCEPTION_ERROR THEN
427 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
428 p_pkg_name => G_PKG_NAME,
429 p_exc_name => G_EXC_NAME_ERROR,
430 x_msg_count => x_msg_count,
431 x_msg_data => x_msg_data,
432 p_api_type => G_API_TYPE);
433 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
434 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
435 p_pkg_name => G_PKG_NAME,
436 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
437 x_msg_count => x_msg_count,
438 x_msg_data => x_msg_data,
439 p_api_type => G_API_TYPE);
440 WHEN OTHERS THEN
441 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
442 p_pkg_name => G_PKG_NAME,
443 p_exc_name => G_EXC_NAME_OTHERS,
444 x_msg_count => x_msg_count,
445 x_msg_data => x_msg_data,
446 p_api_type => G_API_TYPE);
447 END populate_sif_rets;
448
449 -- mvasudev , 04/24/2002
450 ----------------------------------------------------------------------------------------
451 -- PROCEUDRE populate_sif_rets
452 ----------------------------------------------------------------------------------------
453
454 PROCEDURE populate_sif_rets(x_return_status OUT NOCOPY VARCHAR2,
455 x_id OUT NOCOPY NUMBER,
456 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
457 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
458 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
459 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
460 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
461 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
462 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
463 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
464 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM
465 ) IS
466 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
467 l_api_version CONSTANT NUMBER := 1;
468 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
469 l_sirv_rec sirv_rec_type;
470 x_sirv_rec sirv_rec_type;
471 x_msg_data VARCHAR2(400);
472 x_msg_count NUMBER ;
473
474 l_init_msg_list VARCHAR2(1) := 'F';
475
476
477 BEGIN
478
479 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
480 p_pkg_name => G_PKG_NAME,
481 p_init_msg_list => l_init_msg_list,
482 l_api_version => l_api_version,
483 p_api_version => l_api_version,
484 p_api_type => G_API_TYPE,
485 x_return_status => l_return_status);
486 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
487 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
488 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
489 RAISE G_EXCEPTION_ERROR;
490 END IF;
491
492 l_sirv_rec.transaction_number := p_transaction_number;
493 l_sirv_rec.srt_code := p_srt_code;
494
495 l_sirv_rec.yield_name := p_yield_name;
496 l_sirv_rec.index_number := p_index_number;
497
498 IF(p_effective_pre_tax_yield = -100)
499 THEN
500 l_sirv_rec.effective_after_tax_yield := NULL;
501 ELSE
502 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
503 END IF;
504
505 IF(p_effective_after_tax_yield = -100)
506 THEN
507 l_sirv_rec.effective_after_tax_yield := NULL;
508 ELSE
509 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
510 END IF;
511
512 IF(p_nominal_pre_tax_yield = -100)
513 THEN
514 l_sirv_rec.nominal_pre_tax_yield := NULL;
515 ELSE
516 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
517 END IF;
518
519 IF(p_nominal_after_tax_yield = -100)
520 THEN
521 l_sirv_rec.nominal_after_tax_yield := NULL;
522 ELSE
523 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
524 END IF;
525
526 /*
527 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
528 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
529 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
530 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
531 */
532 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
533 l_sirv_rec.implicit_interest_rate := NULL;
534 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
535
536 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
537
538
539 /* Call main API */
540 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => l_api_version,
541 p_init_msg_list => l_init_msg_list,
542 x_return_status => l_return_status,
543 x_msg_count => x_msg_count,
544 x_msg_data => x_msg_data,
545 p_sirv_rec => l_sirv_rec,
546 x_sirv_rec => x_sirv_rec);
547 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
548 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
549 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
550 RAISE G_EXCEPTION_ERROR;
551 END IF;
552
553 -- Assign id returned to corresponding out parameter
554 x_id := x_sirv_rec.id;
555 -- Assign record returned by private api to local record
556 l_sirv_rec := x_sirv_rec;
557
558
559
560 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data);
562 x_return_status := l_return_status;
563 EXCEPTION
564 WHEN G_EXCEPTION_ERROR THEN
565 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
566 p_pkg_name => G_PKG_NAME,
567 p_exc_name => G_EXC_NAME_ERROR,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data,
570 p_api_type => G_API_TYPE);
571 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
572 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
573 p_pkg_name => G_PKG_NAME,
574 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
575 x_msg_count => x_msg_count,
576 x_msg_data => x_msg_data,
577 p_api_type => G_API_TYPE);
578 WHEN OTHERS THEN
579 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
580 p_pkg_name => G_PKG_NAME,
581 p_exc_name => G_EXC_NAME_OTHERS,
585 END populate_sif_rets;
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data,
584 p_api_type => G_API_TYPE);
586 -- mvasudev, 04/24/2002 end
587
588
589
590
591 -- Added by saran
592
593 PROCEDURE update_sif_rets (p_api_version IN NUMBER := 1.0,
594 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
595 x_return_status OUT NOCOPY VARCHAR2,
596 p_id IN NUMBER,
597 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM)
598 IS
599 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
600 l_api_version CONSTANT NUMBER := 1;
601 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
602 l_sirv_rec sirv_rec_type;
603 x_sirv_rec sirv_rec_type;
604 x_msg_data VARCHAR2(400);
605 x_msg_count NUMBER ;
606
607
608 BEGIN
609
610 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
611 p_pkg_name => G_PKG_NAME,
612 p_init_msg_list => p_init_msg_list,
613 l_api_version => l_api_version,
614 p_api_version => p_api_version,
615 p_api_type => G_API_TYPE,
616 x_return_status => l_return_status);
617 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
618 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
619 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
620 RAISE G_EXCEPTION_ERROR;
621 END IF;
622
623 l_sirv_rec.id := p_id;
624 l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
625
626
627
628 /* Call main API */
629 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => p_api_version,
630 p_init_msg_list => p_init_msg_list,
631 x_return_status => l_return_status,
632 x_msg_count => x_msg_count,
633 x_msg_data => x_msg_data,
634 p_sirv_rec => l_sirv_rec,
635 x_sirv_rec => x_sirv_rec);
636
637 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
638 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
639 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
640 RAISE G_EXCEPTION_ERROR;
641 END IF;
642
643 -- Assign record returned by private api to local record
644 l_sirv_rec := x_sirv_rec;
645
646
647 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data);
649 x_return_status := l_return_status;
650 EXCEPTION
651 WHEN G_EXCEPTION_ERROR THEN
652 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
653 p_pkg_name => G_PKG_NAME,
654 p_exc_name => G_EXC_NAME_ERROR,
655 x_msg_count => x_msg_count,
656 x_msg_data => x_msg_data,
657 p_api_type => G_API_TYPE);
658 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
659 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
660 p_pkg_name => G_PKG_NAME,
661 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
662 x_msg_count => x_msg_count,
663 x_msg_data => x_msg_data,
664 p_api_type => G_API_TYPE);
665 WHEN OTHERS THEN
666 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
667 p_pkg_name => G_PKG_NAME,
668 p_exc_name => G_EXC_NAME_OTHERS,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 p_api_type => G_API_TYPE);
672 END update_sif_rets;
673
674 -- mvasudev, 04/24/2002
675 PROCEDURE update_sif_rets (x_return_status OUT NOCOPY VARCHAR2,
676 p_id IN NUMBER,
677 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM)
678 IS
679 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
680
681 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
682 l_sirv_rec sirv_rec_type;
683 x_sirv_rec sirv_rec_type;
684 x_msg_data VARCHAR2(400);
685 x_msg_count NUMBER ;
686
687 l_init_msg_list VARCHAR2(1) := 'F';
688 l_api_version CONSTANT NUMBER := 1;
689
690 BEGIN
691
692 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
693 p_pkg_name => G_PKG_NAME,
694 p_init_msg_list => l_init_msg_list,
695 l_api_version => l_api_version,
696 p_api_version => l_api_version,
697 p_api_type => G_API_TYPE,
698 x_return_status => l_return_status);
699 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
700 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
701 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
702 RAISE G_EXCEPTION_ERROR;
703 END IF;
704
705 l_sirv_rec.id := p_id;
706 l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
707
708
709
710 /* Call main API */
711 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => l_api_version,
712 p_init_msg_list => l_init_msg_list,
713 x_return_status => l_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data,
716 p_sirv_rec => l_sirv_rec,
717 x_sirv_rec => x_sirv_rec);
718
719 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
720 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
721 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
722 RAISE G_EXCEPTION_ERROR;
723 END IF;
724
725 -- Assign record returned by private api to local record
726 l_sirv_rec := x_sirv_rec;
727
728
729 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
730 x_msg_data => x_msg_data);
731 x_return_status := l_return_status;
732 EXCEPTION
733 WHEN G_EXCEPTION_ERROR THEN
734 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
735 p_pkg_name => G_PKG_NAME,
736 p_exc_name => G_EXC_NAME_ERROR,
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_api_type => G_API_TYPE);
740 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
741 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
742 p_pkg_name => G_PKG_NAME,
743 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
744 x_msg_count => x_msg_count,
745 x_msg_data => x_msg_data,
746 p_api_type => G_API_TYPE);
747 WHEN OTHERS THEN
748 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
749 p_pkg_name => G_PKG_NAME,
750 p_exc_name => G_EXC_NAME_OTHERS,
751 x_msg_count => x_msg_count,
752 x_msg_data => x_msg_data,
753 p_api_type => G_API_TYPE);
754 END update_sif_rets;
755 -- mvasudev , 04/24/2002 end
756
757 ----------------------------------------------------------------------------------
758 -- PROCEDURE update_sif_rets
759 ----------------------------------------------------------------------------------
760 PROCEDURE update_sif_rets (p_api_version IN NUMBER := 1.0,
761 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
762 p_id IN NUMBER,
763 p_yield_name IN VARCHAR2,
764 p_amount IN NUMBER,
765 x_return_status OUT NOCOPY VARCHAR2)
766 IS
767 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
768 l_api_version CONSTANT NUMBER := 1;
769 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
770 l_sirv_rec sirv_rec_type;
771 lx_sirv_rec sirv_rec_type;
772 lx_msg_data VARCHAR2(400);
773 lx_msg_count NUMBER ;
774
775 BEGIN
776
777 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
778 p_pkg_name => G_PKG_NAME,
779 p_init_msg_list => p_init_msg_list,
780 l_api_version => l_api_version,
781 p_api_version => p_api_version,
782 p_api_type => G_API_TYPE,
783 x_return_status => l_return_status);
784 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
785 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
786 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
787 RAISE G_EXCEPTION_ERROR;
788 END IF;
789
790 l_sirv_rec.id := p_id;
791 l_sirv_rec.effective_pre_tax_yield := p_amount;
792 l_sirv_rec.yield_name := p_yield_name;
793
794
795
796 /* Call main API */
797 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => p_api_version,
798 p_init_msg_list => p_init_msg_list,
799 x_return_status => l_return_status,
800 x_msg_count => lx_msg_count,
801 x_msg_data => lx_msg_data,
802 p_sirv_rec => l_sirv_rec,
803 x_sirv_rec => lx_sirv_rec);
804
805 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
806 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
807 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
808 RAISE G_EXCEPTION_ERROR;
809 END IF;
810
811 -- Assign record returned by private api to local record
812 l_sirv_rec := lx_sirv_rec;
813
814
815 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
816 x_msg_data => lx_msg_data);
817 x_return_status := l_return_status;
818 EXCEPTION
819 WHEN G_EXCEPTION_ERROR THEN
820 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
821 p_pkg_name => G_PKG_NAME,
822 p_exc_name => G_EXC_NAME_ERROR,
823 x_msg_count => lx_msg_count,
824 x_msg_data => lx_msg_data,
825 p_api_type => G_API_TYPE);
826 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
827 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
828 p_pkg_name => G_PKG_NAME,
829 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
830 x_msg_count => lx_msg_count,
831 x_msg_data => lx_msg_data,
832 p_api_type => G_API_TYPE);
833 WHEN OTHERS THEN
834 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
835 p_pkg_name => G_PKG_NAME,
836 p_exc_name => G_EXC_NAME_OTHERS,
837 x_msg_count => lx_msg_count,
838 x_msg_data => lx_msg_data,
839 p_api_type => G_API_TYPE);
840 END update_sif_rets;
841
842 -- mvasudev , 04/24/2002
843 ----------------------------------------------------------------------------------
844 -- PROCEDURE update_sif_rets
845 ----------------------------------------------------------------------------------
846 PROCEDURE update_sif_rets (p_id IN NUMBER,
847 p_yield_name IN VARCHAR2,
848 p_amount IN NUMBER,
849 x_return_status OUT NOCOPY VARCHAR2)
850 IS
851 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
852 l_api_version CONSTANT NUMBER := 1;
853 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
854 l_sirv_rec sirv_rec_type;
855 lx_sirv_rec sirv_rec_type;
856 lx_msg_data VARCHAR2(400);
857 lx_msg_count NUMBER ;
858
859 l_init_msg_list VARCHAR2(1) := 'F';
860
861
862 BEGIN
863
864 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
865 p_pkg_name => G_PKG_NAME,
866 p_init_msg_list => l_init_msg_list,
867 l_api_version => l_api_version,
868 p_api_version => l_api_version,
869 p_api_type => G_API_TYPE,
870 x_return_status => l_return_status);
871 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
872 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
873 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
874 RAISE G_EXCEPTION_ERROR;
875 END IF;
876
877 l_sirv_rec.id := p_id;
878 l_sirv_rec.effective_pre_tax_yield := p_amount;
879 l_sirv_rec.yield_name := p_yield_name;
880
881
882
883 /* Call main API */
884 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => l_api_version,
885 p_init_msg_list => l_init_msg_list,
886 x_return_status => l_return_status,
887 x_msg_count => lx_msg_count,
888 x_msg_data => lx_msg_data,
889 p_sirv_rec => l_sirv_rec,
890 x_sirv_rec => lx_sirv_rec);
891
892 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
893 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
894 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
895 RAISE G_EXCEPTION_ERROR;
896 END IF;
897
898 -- Assign record returned by private api to local record
899 l_sirv_rec := lx_sirv_rec;
900
901
902 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
903 x_msg_data => lx_msg_data);
904 x_return_status := l_return_status;
905 EXCEPTION
906 WHEN G_EXCEPTION_ERROR THEN
907 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
908 p_pkg_name => G_PKG_NAME,
909 p_exc_name => G_EXC_NAME_ERROR,
910 x_msg_count => lx_msg_count,
911 x_msg_data => lx_msg_data,
912 p_api_type => G_API_TYPE);
913 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
914 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
915 p_pkg_name => G_PKG_NAME,
916 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
917 x_msg_count => lx_msg_count,
918 x_msg_data => lx_msg_data,
919 p_api_type => G_API_TYPE);
920 WHEN OTHERS THEN
921 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
922 p_pkg_name => G_PKG_NAME,
923 p_exc_name => G_EXC_NAME_OTHERS,
924 x_msg_count => lx_msg_count,
925 x_msg_data => lx_msg_data,
926 p_api_type => G_API_TYPE);
927 END update_sif_rets;
928 -- mvasudev, 04/24/2002 end
929
930 ---------------------------------------------------------------------------
931 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
932 ---------------------------------------------------------------------------
933
934 PROCEDURE populate_sif_ret_strms (x_return_status OUT NOCOPY VARCHAR2,
935 p_stream_type_name IN OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR,
936 p_description IN VARCHAR2 := OKC_API.G_MISS_CHAR,
937 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
938 p_sre_date IN VARCHAR2 := OKC_API.G_MISS_CHAR,
939 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
940 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
941 ) IS
942
943 p_api_version NUMBER := 1.0;
944 x_id NUMBER;
945 p_activity_type OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR;
946 p_se_line_number NUMBER := 1;
947 p_init_msg_list VARCHAR2(1) := 'F';
948
949 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
950 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
951 l_srsv_rec srsv_rec_type;
952 l_api_version CONSTANT NUMBER := 1;
953
954 x_srsv_rec srsv_rec_type;
955 x_msg_data VARCHAR2(400);
956 x_msg_count NUMBER ;
957 l_sre_date VARCHAR2(30);
958
959 BEGIN
960 --dbms_output.put_line('populate_sif_ret_strms start pub');
961 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
962 p_pkg_name => G_PKG_NAME,
963 p_init_msg_list => p_init_msg_list,
964 l_api_version => l_api_version,
965 p_api_version => p_api_version,
966 p_api_type => G_API_TYPE,
967 x_return_status => l_return_status);
968 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
969 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
970 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
971 RAISE G_EXCEPTION_ERROR;
972 END IF;
973 --dbms_output.put_line('populate_sif_ret_strms aft savepoint pub');
974
975 IF(p_description = 'Residual Insurance Premium')
976 THEN
977 l_srsv_rec.stream_type_name := 'RESIDUAL VALUE INSURANCE PREMIUM';
978
979 ELSIF ( p_description = 'NODESC' OR p_description = 'TEMPVALUE')
980
981 THEN
982 l_srsv_rec.stream_type_name := p_stream_type_name;
983 ELSE
984 l_srsv_rec.stream_type_name := p_description;
985 END IF;
986
987 -- IF the index number is not returned from SuperTrump
988 -- -100 is the default value for Index Number
989 IF (p_index_number = -100)
990 THEN
991 l_srsv_rec.index_number := NULL;
992 ELSE
993 l_srsv_rec.index_number := p_index_number;
994 END IF;
995 l_srsv_rec.activity_type := p_activity_type;
996 -- l_srsv_rec.sequence_number := sequence_number;
997 l_srsv_rec.sequence_number := p_se_line_number;
998 -- l_srsv_rec.sre_date := p_sre_date;
999 -- TBD
1000 -- CHECK FOR THE DATE FORMAT
1001 --dbms_output.put_line('populate_sif_ret_strms bef date conv pub '||p_sre_date);
1002
1003 -- TEMP FIX FOR 02/30 DATES
1004
1005 l_sre_date := p_sre_date;
1006 l_sre_date := correct_feb_date(l_sre_date);
1007
1008 l_srsv_rec.sre_date := TO_DATE(l_sre_date, 'YYYY-MM-DD');
1009 l_srsv_rec.amount := p_amount;
1010 l_srsv_rec.sir_id := p_sir_id;
1011
1012 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 4' );
1013 /* Call main API */
1014 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_strms (p_api_version => p_api_version,
1015 p_init_msg_list => p_init_msg_list,
1016 x_return_status => l_return_status,
1017 x_msg_count => x_msg_count,
1018 x_msg_data => x_msg_data,
1019 p_srsv_rec => l_srsv_rec,
1020 x_srsv_rec => x_srsv_rec);
1021
1022 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 5' || l_return_status);
1023 FOR i IN 1..x_msg_count
1024 LOOP
1025 fnd_msg_pub.get(p_data => x_msg_data,
1026 p_msg_index_out => x_msg_count,
1027 p_encoded => 'F',
1028 p_msg_index => fnd_msg_pub.g_next
1029 );
1030 --DBMS_OUTPUT.PUT_LINE('l_msg_text = ' || x_msg_data);
1031 END LOOP;
1032
1033
1034 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1035 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1036 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1037 RAISE G_EXCEPTION_ERROR;
1038 END IF;
1039
1040 -- Assign id returned to corresponding out parameter
1041 x_id := x_srsv_rec.id;
1042 -- Assign record returned by private api to local record
1043 l_srsv_rec := x_srsv_rec;
1044
1045
1046 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1047 x_msg_data => x_msg_data);
1048 x_return_status := l_return_status;
1049 EXCEPTION
1050 WHEN G_EXCEPTION_ERROR THEN
1051 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1055 x_msg_data => x_msg_data,
1052 p_pkg_name => G_PKG_NAME,
1053 p_exc_name => G_EXC_NAME_ERROR,
1054 x_msg_count => x_msg_count,
1056 p_api_type => G_API_TYPE);
1057 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1058 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1059 p_pkg_name => G_PKG_NAME,
1060 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data,
1063 p_api_type => G_API_TYPE);
1064 WHEN OTHERS THEN
1065 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1066 p_pkg_name => G_PKG_NAME,
1067 p_exc_name => G_EXC_NAME_OTHERS,
1068 x_msg_count => x_msg_count,
1069 x_msg_data => x_msg_data,
1070 p_api_type => G_API_TYPE);
1071 END populate_sif_ret_strms;
1072
1073 ---------------------------------------------------------------------------
1074 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
1075 ---------------------------------------------------------------------------
1076
1077 PROCEDURE populate_sif_ret_strms (p_api_version IN NUMBER := 1.0,
1078 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1079 x_return_status OUT NOCOPY VARCHAR2,
1080 x_id OUT NOCOPY NUMBER,
1081 p_stream_type_name IN OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1082 p_description IN VARCHAR2 := OKC_API.G_MISS_CHAR,
1083 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
1084 p_activity_type IN OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR,
1085 p_se_line_number IN NUMBER := OKC_API.G_MISS_NUM,
1086 p_sre_date IN VARCHAR2 := OKC_API.G_MISS_CHAR,
1087 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
1088 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
1089 ) IS
1090 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
1091 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1092 l_srsv_rec srsv_rec_type;
1093 l_api_version CONSTANT NUMBER := 1;
1094
1095 x_srsv_rec srsv_rec_type;
1096 x_msg_data VARCHAR2(400);
1097 x_msg_count NUMBER ;
1098 l_sre_date VARCHAR2(30);
1099
1100 BEGIN
1101 --dbms_output.put_line('populate_sif_ret_strms start pub');
1102 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1103 p_pkg_name => G_PKG_NAME,
1104 p_init_msg_list => p_init_msg_list,
1105 l_api_version => l_api_version,
1106 p_api_version => p_api_version,
1107 p_api_type => G_API_TYPE,
1108 x_return_status => l_return_status);
1109 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1110 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1111 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1112 RAISE G_EXCEPTION_ERROR;
1113 END IF;
1114 --dbms_output.put_line('populate_sif_ret_strms aft savepoint pub');
1115
1116 IF(p_description = 'Residual Insurance Premium')
1117 THEN
1118 l_srsv_rec.stream_type_name := 'RESIDUAL VALUE INSURANCE PREMIUM';
1119
1120 ELSIF ( p_description = 'NODESC' OR p_description = 'TEMPVALUE')
1121
1122 THEN
1123 l_srsv_rec.stream_type_name := p_stream_type_name;
1124 ELSE
1125 l_srsv_rec.stream_type_name := p_description;
1126 END IF;
1127
1128 -- IF the index number is not returned from SuperTrump
1129 -- -100 is the default value for Index Number
1130 IF (p_index_number = -100)
1131 THEN
1132 l_srsv_rec.index_number := NULL;
1133 ELSE
1134 l_srsv_rec.index_number := p_index_number;
1135 END IF;
1136 l_srsv_rec.activity_type := p_activity_type;
1137 -- l_srsv_rec.sequence_number := sequence_number;
1138 l_srsv_rec.sequence_number := p_se_line_number;
1139 -- l_srsv_rec.sre_date := p_sre_date;
1140 -- TBD
1141 -- CHECK FOR THE DATE FORMAT
1142 --dbms_output.put_line('populate_sif_ret_strms bef date conv pub '||p_sre_date);
1143
1144 -- TEMP FIX FOR 02/30 DATES
1145
1146 l_sre_date := p_sre_date;
1147 l_sre_date := correct_feb_date(l_sre_date);
1148
1149 l_srsv_rec.sre_date := TO_DATE(l_sre_date, 'YYYY-MM-DD');
1150 l_srsv_rec.amount := p_amount;
1151 l_srsv_rec.sir_id := p_sir_id;
1152
1153 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 4' );
1154 /* Call main API */
1155 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_strms (p_api_version => p_api_version,
1156 p_init_msg_list => p_init_msg_list,
1157 x_return_status => l_return_status,
1158 x_msg_count => x_msg_count,
1159 x_msg_data => x_msg_data,
1163 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 5' || l_return_status);
1160 p_srsv_rec => l_srsv_rec,
1161 x_srsv_rec => x_srsv_rec);
1162
1164 FOR i IN 1..x_msg_count
1165 LOOP
1166 fnd_msg_pub.get(p_data => x_msg_data,
1167 p_msg_index_out => x_msg_count,
1168 p_encoded => 'F',
1169 p_msg_index => fnd_msg_pub.g_next
1170 );
1171 --DBMS_OUTPUT.PUT_LINE('l_msg_text = ' || x_msg_data);
1172 END LOOP;
1173
1174
1175 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1176 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1177 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1178 RAISE G_EXCEPTION_ERROR;
1179 END IF;
1180
1181 -- Assign id returned to corresponding out parameter
1182 x_id := x_srsv_rec.id;
1183 -- Assign record returned by private api to local record
1184 l_srsv_rec := x_srsv_rec;
1185
1186
1187 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1188 x_msg_data => x_msg_data);
1189 x_return_status := l_return_status;
1190 EXCEPTION
1191 WHEN G_EXCEPTION_ERROR THEN
1192 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1193 p_pkg_name => G_PKG_NAME,
1194 p_exc_name => G_EXC_NAME_ERROR,
1195 x_msg_count => x_msg_count,
1196 x_msg_data => x_msg_data,
1197 p_api_type => G_API_TYPE);
1198 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1199 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1200 p_pkg_name => G_PKG_NAME,
1201 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1202 x_msg_count => x_msg_count,
1203 x_msg_data => x_msg_data,
1204 p_api_type => G_API_TYPE);
1205 WHEN OTHERS THEN
1206 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1207 p_pkg_name => G_PKG_NAME,
1208 p_exc_name => G_EXC_NAME_OTHERS,
1209 x_msg_count => x_msg_count,
1210 x_msg_data => x_msg_data,
1211 p_api_type => G_API_TYPE);
1212 END populate_sif_ret_strms;
1213
1214 ---------------------------------------------------------------------------
1215 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
1216 ---------------------------------------------------------------------------
1217
1218 PROCEDURE populate_sif_ret_strms (p_api_version IN NUMBER := 1.0,
1219 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1220 x_return_status OUT NOCOPY VARCHAR2,
1221 x_id OUT NOCOPY NUMBER,
1222 p_stream_type_name IN OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1223 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
1224 p_activity_type IN OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR,
1225 p_se_line_number IN NUMBER := OKC_API.G_MISS_NUM,
1226 p_sre_date IN OKL_SIF_RET_STRMS.SRE_DATE%TYPE := OKC_API.G_MISS_DATE,
1227 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
1228 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
1229 ) IS
1230 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
1231 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1232 l_srsv_rec srsv_rec_type;
1233 l_api_version CONSTANT NUMBER := 1;
1234
1235 x_srsv_rec srsv_rec_type;
1236 x_msg_data VARCHAR2(400);
1237 x_msg_count NUMBER ;
1238
1239 BEGIN
1240 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure ');
1241 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1242 p_pkg_name => G_PKG_NAME,
1243 p_init_msg_list => p_init_msg_list,
1244 l_api_version => l_api_version,
1245 p_api_version => p_api_version,
1246 p_api_type => G_API_TYPE,
1247 x_return_status => l_return_status);
1248 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1249 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1250 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1251 RAISE G_EXCEPTION_ERROR;
1252 END IF;
1253 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 2');
1254 l_srsv_rec.stream_type_name := p_stream_type_name;
1255 l_srsv_rec.index_number := p_index_number;
1256 l_srsv_rec.activity_type := p_activity_type;
1257 l_srsv_rec.sequence_number := p_se_line_number;
1258 l_srsv_rec.sre_date := p_sre_date;
1259
1260 l_srsv_rec.amount := p_amount;
1261 l_srsv_rec.sir_id := p_sir_id;
1262 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 3');
1263
1264
1265 /* Call main API */
1266 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 4');
1267 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_strms (p_api_version => p_api_version,
1268 p_init_msg_list => p_init_msg_list,
1269 x_return_status => l_return_status,
1270 x_msg_count => x_msg_count,
1271 x_msg_data => x_msg_data,
1272 p_srsv_rec => l_srsv_rec,
1273 x_srsv_rec => x_srsv_rec);
1274 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 5' || l_return_status);
1275 FOR i IN 1..x_msg_count
1276 LOOP
1277 fnd_msg_pub.get(p_data => x_msg_data,
1278 p_msg_index_out => x_msg_count,
1279 p_encoded => 'F',
1280 p_msg_index => fnd_msg_pub.g_next
1281 );
1282 --DBMS_OUTPUT.PUT_LINE('l_msg_text = ' || x_msg_data);
1283 END LOOP;
1284 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1285 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1286 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1287 RAISE G_EXCEPTION_ERROR;
1288 END IF;
1289
1290 -- Assign id returned to corresponding out parameter
1291 x_id := x_srsv_rec.id;
1292 -- Assign record returned by private api to local record
1293 l_srsv_rec := x_srsv_rec;
1294
1295
1296 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data);
1298 x_return_status := l_return_status;
1299 EXCEPTION
1300 WHEN G_EXCEPTION_ERROR THEN
1301 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1302 p_pkg_name => G_PKG_NAME,
1303 p_exc_name => G_EXC_NAME_ERROR,
1304 x_msg_count => x_msg_count,
1305 x_msg_data => x_msg_data,
1306 p_api_type => G_API_TYPE);
1307 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1308 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1309 p_pkg_name => G_PKG_NAME,
1310 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1311 x_msg_count => x_msg_count,
1312 x_msg_data => x_msg_data,
1313 p_api_type => G_API_TYPE);
1314 WHEN OTHERS THEN
1315 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1316 p_pkg_name => G_PKG_NAME,
1317 p_exc_name => G_EXC_NAME_OTHERS,
1318 x_msg_count => x_msg_count,
1319 x_msg_data => x_msg_data,
1320 p_api_type => G_API_TYPE);
1321 END populate_sif_ret_strms;
1322
1323
1324
1325
1326 ---------------------------------------------------------------------------
1327 -- PROCEDURE populate_sif_ret_errors for: OKL_SIF_RET_ERRORS_V
1328 ---------------------------------------------------------------------------
1329
1330 PROCEDURE populate_sif_ret_errors (p_api_version IN NUMBER := 1.0,
1331 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_id OUT NOCOPY NUMBER,
1334 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
1335 p_error_code IN OKL_SIF_RET_ERRORS.ERROR_CODE%TYPE := OKC_API.G_MISS_CHAR,
1336 p_error_message IN OKL_SIF_RET_ERRORS.ERROR_MESSAGE%TYPE := OKC_API.G_MISS_CHAR,
1337 p_tag_name IN OKL_SIF_RET_ERRORS.TAG_NAME%TYPE := OKC_API.G_MISS_CHAR,
1338 p_tag_attribute_name IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1339 p_tag_attribute_value IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_VALUE%TYPE := OKC_API.G_MISS_CHAR,
1340 p_description IN OKL_SIF_RET_ERRORS.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
1341 ) IS
1342 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_errors';
1343 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1344 l_srmv_rec srmv_rec_type;
1345 l_api_version CONSTANT NUMBER := 1;
1346 x_srmv_rec srmv_rec_type;
1347 x_msg_data VARCHAR2(400);
1348 x_msg_count NUMBER ;
1349
1350 BEGIN
1351 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1352 p_pkg_name => G_PKG_NAME,
1353 p_init_msg_list => p_init_msg_list,
1354 l_api_version => l_api_version,
1355 p_api_version => p_api_version,
1356 p_api_type => G_API_TYPE,
1357 x_return_status => l_return_status);
1358 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1359 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1360 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1361 RAISE G_EXCEPTION_ERROR;
1362 END IF;
1363
1364 l_srmv_rec.sir_id := p_sir_id;
1365 l_srmv_rec.error_code := p_error_code;
1366 l_srmv_rec.error_message := p_error_message;
1367 l_srmv_rec.tag_name := p_tag_name;
1368 IF(p_tag_attribute_name = 'TEMPVALUE')
1369 THEN
1370 l_srmv_rec.tag_attribute_name := NULL;
1371 ELSE
1372 l_srmv_rec.tag_attribute_name := p_tag_attribute_name;
1373 END IF;
1374 IF(p_tag_attribute_value = 'TEMPVALUE')
1375 THEN
1379 END IF;
1376 l_srmv_rec.tag_attribute_value := NULL;
1377 ELSE
1378 l_srmv_rec.tag_attribute_value := p_tag_attribute_value;
1380 IF(p_description = 'TEMPVALUE')
1381 THEN
1382 l_srmv_rec.description := NULL;
1383 ELSE
1384 l_srmv_rec.description := p_description;
1385 END IF;
1386
1387
1388
1389 /* Call main API */
1390 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_errors(p_api_version => p_api_version,
1391 p_init_msg_list => p_init_msg_list,
1392 x_return_status => l_return_status,
1393 x_msg_count => x_msg_count,
1394 x_msg_data => x_msg_data,
1395 p_srmv_rec => l_srmv_rec,
1396 x_srmv_rec => x_srmv_rec);
1397 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1398 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1399 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1400 RAISE G_EXCEPTION_ERROR;
1401 END IF;
1402
1403 -- Assign id returned to corresponding out parameter
1404 x_id := x_srmv_rec.id;
1405 -- Assign record returned by private api to local record
1406 l_srmv_rec := x_srmv_rec;
1407
1408
1409 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1410 x_msg_data => x_msg_data);
1411 x_return_status := l_return_status;
1412 EXCEPTION
1413 WHEN G_EXCEPTION_ERROR THEN
1414 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1415 p_pkg_name => G_PKG_NAME,
1416 p_exc_name => G_EXC_NAME_ERROR,
1417 x_msg_count => x_msg_count,
1418 x_msg_data => x_msg_data,
1419 p_api_type => G_API_TYPE);
1420 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1421 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1422 p_pkg_name => G_PKG_NAME,
1423 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1424 x_msg_count => x_msg_count,
1425 x_msg_data => x_msg_data,
1426 p_api_type => G_API_TYPE);
1427 WHEN OTHERS THEN
1428 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1429 p_pkg_name => G_PKG_NAME,
1430 p_exc_name => G_EXC_NAME_OTHERS,
1431 x_msg_count => x_msg_count,
1432 x_msg_data => x_msg_data,
1433 p_api_type => G_API_TYPE);
1434 END populate_sif_ret_errors;
1435
1436 -- mvasudev , 04/24/2002
1437 ---------------------------------------------------------------------------
1438 -- PROCEDURE populate_sif_ret_errors for: OKL_SIF_RET_ERRORS_V
1439 ---------------------------------------------------------------------------
1440
1441 PROCEDURE populate_sif_ret_errors (x_return_status OUT NOCOPY VARCHAR2,
1442 x_id OUT NOCOPY NUMBER,
1443 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
1444 p_error_code IN OKL_SIF_RET_ERRORS.ERROR_CODE%TYPE := OKC_API.G_MISS_CHAR,
1445 p_error_message IN OKL_SIF_RET_ERRORS.ERROR_MESSAGE%TYPE := OKC_API.G_MISS_CHAR,
1446 p_tag_name IN OKL_SIF_RET_ERRORS.TAG_NAME%TYPE := OKC_API.G_MISS_CHAR,
1447 p_tag_attribute_name IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1448 p_tag_attribute_value IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_VALUE%TYPE := OKC_API.G_MISS_CHAR,
1449 p_description IN OKL_SIF_RET_ERRORS.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
1450 ) IS
1451 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_errors';
1452 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1453 l_srmv_rec srmv_rec_type;
1454 l_api_version CONSTANT NUMBER := 1;
1455 x_srmv_rec srmv_rec_type;
1456 x_msg_data VARCHAR2(400);
1457 x_msg_count NUMBER ;
1458
1459 l_init_msg_list VARCHAR2(1) := 'F';
1460
1461
1462 BEGIN
1463 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1464 p_pkg_name => G_PKG_NAME,
1465 p_init_msg_list => l_init_msg_list,
1466 l_api_version => l_api_version,
1467 p_api_version => l_api_version,
1468 p_api_type => G_API_TYPE,
1469 x_return_status => l_return_status);
1470 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1471 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1472 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1473 RAISE G_EXCEPTION_ERROR;
1474 END IF;
1475
1476 l_srmv_rec.sir_id := p_sir_id;
1477 l_srmv_rec.error_code := p_error_code;
1478 l_srmv_rec.error_message := p_error_message;
1479 l_srmv_rec.tag_name := p_tag_name;
1480 IF(p_tag_attribute_name = 'TEMPVALUE')
1481 THEN
1482 l_srmv_rec.tag_attribute_name := NULL;
1483 ELSE
1484 l_srmv_rec.tag_attribute_name := p_tag_attribute_name;
1485 END IF;
1486 IF(p_tag_attribute_value = 'TEMPVALUE')
1487 THEN
1488 l_srmv_rec.tag_attribute_value := NULL;
1489 ELSE
1490 l_srmv_rec.tag_attribute_value := p_tag_attribute_value;
1491 END IF;
1492 IF(p_description = 'TEMPVALUE')
1493 THEN
1494 l_srmv_rec.description := NULL;
1495 ELSE
1496 l_srmv_rec.description := p_description;
1497 END IF;
1498
1499
1500
1501 /* Call main API */
1502 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_errors(p_api_version => l_api_version,
1503 p_init_msg_list => l_init_msg_list,
1504 x_return_status => l_return_status,
1505 x_msg_count => x_msg_count,
1506 x_msg_data => x_msg_data,
1507 p_srmv_rec => l_srmv_rec,
1508 x_srmv_rec => x_srmv_rec);
1509 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1510 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1511 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1512 RAISE G_EXCEPTION_ERROR;
1513 END IF;
1514
1515 -- Assign id returned to corresponding out parameter
1516 x_id := x_srmv_rec.id;
1517 -- Assign record returned by private api to local record
1518 l_srmv_rec := x_srmv_rec;
1519
1520
1521 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1522 x_msg_data => x_msg_data);
1523 x_return_status := l_return_status;
1524 EXCEPTION
1525 WHEN G_EXCEPTION_ERROR THEN
1526 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1527 p_pkg_name => G_PKG_NAME,
1528 p_exc_name => G_EXC_NAME_ERROR,
1529 x_msg_count => x_msg_count,
1530 x_msg_data => x_msg_data,
1531 p_api_type => G_API_TYPE);
1532 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1533 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1534 p_pkg_name => G_PKG_NAME,
1535 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1536 x_msg_count => x_msg_count,
1537 x_msg_data => x_msg_data,
1538 p_api_type => G_API_TYPE);
1539 WHEN OTHERS THEN
1540 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1541 p_pkg_name => G_PKG_NAME,
1542 p_exc_name => G_EXC_NAME_OTHERS,
1543 x_msg_count => x_msg_count,
1544 x_msg_data => x_msg_data,
1545 p_api_type => G_API_TYPE);
1546 END populate_sif_ret_errors;
1547 -- mvasudev , 04/24/2002 end
1548
1549 ----------------------------------------------------------------------------------------
1550 -- PROCEDURE populate_insured_residual
1551 ----------------------------------------------------------------------------------------
1552 PROCEDURE populate_insured_residual (
1553 p_api_version IN NUMBER,
1554 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1555 p_transaction_number IN NUMBER,
1556 p_amount IN NUMBER,
1557 p_sir_id IN NUMBER,
1558 x_return_status OUT NOCOPY VARCHAR2
1559 --x_msg_count OUT NOCOPY NUMBER,
1560 --x_msg_data OUT NOCOPY VARCHAR2
1561 ) IS
1562
1563 l_khr_id NUMBER;
1564 l_start_date DATE;
1565 l_stream_type_name VARCHAR2(30);
1566 l_api_name CONSTANT VARCHAR2(30) := 'populate_insured_residual';
1567 l_api_version CONSTANT NUMBER := 1;
1568 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1569 --l_srsv_rec srsv_rec_type;
1570 --lx_srsv_rec srsv_rec_type;
1571 lx_msg_count NUMBER;
1572 lx_msg_data VARCHAR2(400);
1573 lx_id NUMBER;
1574
1575 CURSOR khr_id_csr (trx_number NUMBER) IS
1576 SELECT khr_id
1577 FROM okl_stream_interfaces
1578 WHERE transaction_number = trx_number;
1579
1580 CURSOR start_date_csr (khr_id NUMBER) IS
1581 SELECT start_date
1582 FROM okc_k_headers_b
1583 WHERE id = khr_id;
1584
1585 BEGIN
1586
1587 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1588 p_pkg_name => G_PKG_NAME,
1589 p_init_msg_list => p_init_msg_list,
1590 l_api_version => l_api_version,
1591 p_api_version => p_api_version,
1592 p_api_type => G_API_TYPE,
1593 x_return_status => l_return_status);
1594 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1595 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1596 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1597 RAISE G_EXCEPTION_ERROR;
1598 END IF;
1599
1600 FOR khr_id_info IN khr_id_csr(p_transaction_number)
1601 LOOP
1602 l_khr_id := khr_id_info.khr_id;
1603 END LOOP;
1604
1605 FOR start_date_info IN start_date_csr(l_khr_id)
1606 LOOP
1607 l_start_date := start_date_info.start_date;
1608 END LOOP;
1609
1610 l_stream_type_name := 'GUARANTEED RESIDUAL INSURED';
1611
1612 --l_srsv_rec.stream_type_name := l_stream_type_name;
1613 --l_srsv_rec.index_number := to_number(NULL);
1614 --l_srsv_rec.activity_type := NULL;
1615 --l_srsv_rec.sre_date := l_start_date;
1616 --l_srsv_rec.amount := p_amount;
1617 --l_srsv_rec.sir_id := p_sir_id;
1618
1619
1620 /* Call main API */
1621 --DBMS_OUTPUT.PUT_LINE('khr_id ' || l_khr_id);
1622 --DBMS_OUTPUT.PUT_LINE('Before calling Procedure ' || l_start_date);
1623 Okl_Populate_Prceng_Result_Pub.populate_sif_ret_strms (p_api_version => p_api_version,
1624 p_init_msg_list => p_init_msg_list,
1625 x_return_status => l_return_status,
1626 x_id => lx_id,
1627 p_stream_type_name => l_stream_type_name,
1628 p_description => l_stream_type_name,
1629 p_index_number => TO_NUMBER(NULL),
1630 -- p_index_number => 1,
1631 -- p_activity_type => NULL,
1632 p_activity_type => 'TYPE',
1633 -- p_se_line_number => to_number(NULL),
1634 p_se_line_number => 1,
1635 p_sre_date => TO_CHAR(l_start_date, 'YYYY-MM-DD'),
1636 p_amount => p_amount,
1637 p_sir_id => p_sir_id);
1638
1639
1640 --DBMS_OUTPUT.PUT_LINE('After calling Procedure ' || l_return_status);
1641
1642 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1643 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1644 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1645 RAISE G_EXCEPTION_ERROR;
1646 END IF;
1647
1648
1649 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
1650 x_msg_data => lx_msg_data);
1651
1652 x_return_status := l_return_status;
1653 EXCEPTION
1654 WHEN G_EXCEPTION_ERROR THEN
1655 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1656 p_pkg_name => G_PKG_NAME,
1657 p_exc_name => G_EXC_NAME_ERROR,
1658 x_msg_count => lx_msg_count,
1659 x_msg_data => lx_msg_data,
1660 p_api_type => G_API_TYPE);
1661 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1662 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1663 p_pkg_name => G_PKG_NAME,
1664 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1665 x_msg_count => lx_msg_count,
1666 x_msg_data => lx_msg_data,
1667 p_api_type => G_API_TYPE);
1668 WHEN OTHERS THEN
1669 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1670 p_pkg_name => G_PKG_NAME,
1671 p_exc_name => G_EXC_NAME_OTHERS,
1672 x_msg_count => lx_msg_count,
1673 x_msg_data => lx_msg_data,
1674 p_api_type => G_API_TYPE);
1675 END populate_insured_residual;
1676
1677 -- mvasudev, 04/24/2002
1678 ----------------------------------------------------------------------------------------
1679 -- PROCEDURE populate_insured_residual
1680 ----------------------------------------------------------------------------------------
1681 PROCEDURE populate_insured_residual (
1682 p_transaction_number IN NUMBER,
1683 p_amount IN NUMBER,
1684 p_sir_id IN NUMBER,
1685 x_return_status OUT NOCOPY VARCHAR2
1686 ) IS
1687
1688 l_khr_id NUMBER;
1689 l_start_date DATE;
1690 l_stream_type_name VARCHAR2(30);
1691 l_api_name CONSTANT VARCHAR2(30) := 'populate_insured_residual';
1692 l_api_version CONSTANT NUMBER := 1;
1693 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1694 --l_srsv_rec srsv_rec_type;
1695 --lx_srsv_rec srsv_rec_type;
1696 lx_msg_count NUMBER;
1697 lx_msg_data VARCHAR2(400);
1698 lx_id NUMBER;
1699
1700 CURSOR khr_id_csr (trx_number NUMBER) IS
1701 SELECT khr_id
1702 FROM okl_stream_interfaces
1703 WHERE transaction_number = trx_number;
1704
1705 CURSOR start_date_csr (khr_id NUMBER) IS
1706 SELECT start_date
1707 FROM okc_k_headers_b
1708 WHERE id = khr_id;
1709
1710 l_init_msg_list VARCHAR2(1) := 'F';
1711
1712
1713 BEGIN
1714
1715 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1716 p_pkg_name => G_PKG_NAME,
1717 p_init_msg_list => l_init_msg_list,
1718 l_api_version => l_api_version,
1719 p_api_version => l_api_version,
1720 p_api_type => G_API_TYPE,
1721 x_return_status => l_return_status);
1722 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1723 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1724 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1725 RAISE G_EXCEPTION_ERROR;
1726 END IF;
1727
1731 END LOOP;
1728 FOR khr_id_info IN khr_id_csr(p_transaction_number)
1729 LOOP
1730 l_khr_id := khr_id_info.khr_id;
1732
1733 FOR start_date_info IN start_date_csr(l_khr_id)
1734 LOOP
1735 l_start_date := start_date_info.start_date;
1736 END LOOP;
1737
1738 l_stream_type_name := 'GUARANTEED RESIDUAL INSURED';
1739
1740 --l_srsv_rec.stream_type_name := l_stream_type_name;
1741 --l_srsv_rec.index_number := to_number(NULL);
1742 --l_srsv_rec.activity_type := NULL;
1743 --l_srsv_rec.sre_date := l_start_date;
1744 --l_srsv_rec.amount := p_amount;
1745 --l_srsv_rec.sir_id := p_sir_id;
1746
1747
1748 /* Call main API */
1749 --DBMS_OUTPUT.PUT_LINE('khr_id ' || l_khr_id);
1750 --DBMS_OUTPUT.PUT_LINE('Before calling Procedure ' || l_start_date);
1751 Okl_Populate_Prceng_Result_Pub.populate_sif_ret_strms (p_api_version => l_api_version,
1752 p_init_msg_list => l_init_msg_list,
1753 x_return_status => l_return_status,
1754 x_id => lx_id,
1755 p_stream_type_name => l_stream_type_name,
1756 p_description => l_stream_type_name,
1757 p_index_number => TO_NUMBER(NULL),
1758 -- p_index_number => 1,
1759 -- p_activity_type => NULL,
1760 p_activity_type => 'TYPE',
1761 -- p_se_line_number => to_number(NULL),
1762 p_se_line_number => 1,
1763 p_sre_date => TO_CHAR(l_start_date, 'YYYY-MM-DD'),
1764 p_amount => p_amount,
1765 p_sir_id => p_sir_id);
1766
1767
1768 --DBMS_OUTPUT.PUT_LINE('After calling Procedure ' || l_return_status);
1769
1770 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1771 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1772 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1773 RAISE G_EXCEPTION_ERROR;
1774 END IF;
1775
1776
1777 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
1778 x_msg_data => lx_msg_data);
1779
1780 x_return_status := l_return_status;
1781 EXCEPTION
1782 WHEN G_EXCEPTION_ERROR THEN
1783 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1784 p_pkg_name => G_PKG_NAME,
1785 p_exc_name => G_EXC_NAME_ERROR,
1786 x_msg_count => lx_msg_count,
1787 x_msg_data => lx_msg_data,
1788 p_api_type => G_API_TYPE);
1789 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1790 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1791 p_pkg_name => G_PKG_NAME,
1792 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1793 x_msg_count => lx_msg_count,
1794 x_msg_data => lx_msg_data,
1795 p_api_type => G_API_TYPE);
1796 WHEN OTHERS THEN
1797 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1798 p_pkg_name => G_PKG_NAME,
1799 p_exc_name => G_EXC_NAME_OTHERS,
1800 x_msg_count => lx_msg_count,
1801 x_msg_data => lx_msg_data,
1802 p_api_type => G_API_TYPE);
1803 END populate_insured_residual;
1804 -- mvasudev, 04/24/2002 end
1805
1806 /*
1807 PROCEDURE update_status (
1808 p_api_version IN NUMBER,
1809 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1810 p_transaction_number IN NUMBER,
1811 p_sis_code IN VARCHAR2,
1812 x_return_status OUT NOCOPY VARCHAR2
1813 ) IS
1814 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
1815 CURSOR sif_data_csr (trx_number IN NUMBER) IS
1816 SELECT
1817 ID,ORP_CODE
1818 FROM Okl_Stream_Interfaces
1819 WHERE okl_stream_interfaces.transaction_number = trx_number;
1820
1821 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1822 lp_sifv_rec sifv_rec_type;
1823 lx_sifv_rec sifv_rec_type;
1824 lx_msg_count NUMBER;
1825 lx_msg_data VARCHAR2(100);
1826
1827 BEGIN
1828
1829 -- update the status in the In bound Interface Tables
1830 FOR sif_data in sif_data_csr(p_transaction_number)
1831 LOOP
1832 lp_sifv_rec.id := sif_data.id;
1833 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
1834 END LOOP;
1835 lp_sifv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1836 lp_sifv_rec.sis_code := p_sis_code;
1837
1838 OKL_POPULATE_PRCENG_RESULT_PVT.update_status (p_api_version => p_api_version,
1839 p_init_msg_list => p_init_msg_list,
1840 p_sifv_rec => lp_sifv_rec,
1841 x_sifv_rec => lx_sifv_rec,
1842 x_msg_count => lx_msg_count,
1846 IF l_return_status = G_RET_STS_ERROR THEN
1843 x_msg_data => lx_msg_data,
1844 x_return_status => l_return_status);
1845
1847 RAISE G_EXCEPTION_ERROR;
1848 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1849 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1850 END IF;
1851
1852 x_return_status := l_return_status;
1853 EXCEPTION
1854 WHEN G_EXCEPTION_ERROR THEN
1855 x_return_status := G_RET_STS_ERROR;
1856 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1857 x_return_status := G_RET_STS_UNEXP_ERROR;
1858 WHEN OTHERS THEN
1859 x_return_status := G_RET_STS_UNEXP_ERROR;
1860
1861 END update_status;
1862 */
1863 PROCEDURE update_status (
1864 p_api_version IN NUMBER,
1865 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1866 p_transaction_number IN NUMBER,
1867 p_sis_code IN VARCHAR2, -- outbound status
1868 p_srt_code IN VARCHAR2, -- inbound status
1869 p_log_file_name IN VARCHAR2,
1870 x_return_status OUT NOCOPY VARCHAR2
1871 ) IS
1872 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
1873 CURSOR sif_data_csr (trx_number IN NUMBER) IS
1874 SELECT
1875 ID,ORP_CODE
1876 FROM Okl_Stream_Interfaces
1877 WHERE okl_stream_interfaces.transaction_number = trx_number;
1878
1879 CURSOR sir_data_csr (trx_number IN NUMBER) IS
1880 SELECT
1881 ID
1882 FROM Okl_Sif_Rets
1883 WHERE okl_sif_rets.transaction_number = trx_number;
1884
1885 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1886 lp_sifv_rec sifv_rec_type;
1887 lx_sifv_rec sifv_rec_type;
1888 lp_sirv_rec sirv_rec_type;
1889 lx_sirv_rec sirv_rec_type;
1890 lx_msg_count NUMBER;
1891 lx_msg_data VARCHAR2(100);
1892
1893 BEGIN
1894
1895 -- update the status in the In bound Interface Tables
1896 FOR sif_data IN sif_data_csr(p_transaction_number)
1897 LOOP
1898 lp_sifv_rec.id := sif_data.id;
1899 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
1900 END LOOP;
1901 lp_sifv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1902 lp_sifv_rec.sis_code := p_sis_code;
1903 IF(p_log_file_name <> 'TEMPVALUE')
1904 THEN
1905 lp_sifv_rec.log_file := p_log_file_name;
1906 END IF;
1907
1908
1909 Okl_Populate_Prceng_Result_Pvt.update_outbound_status (p_api_version => p_api_version,
1910 p_init_msg_list => p_init_msg_list,
1911 p_sifv_rec => lp_sifv_rec,
1912 x_sifv_rec => lx_sifv_rec,
1913 x_msg_count => lx_msg_count,
1914 x_msg_data => lx_msg_data,
1915 x_return_status => l_return_status);
1916
1917 IF l_return_status = G_RET_STS_ERROR THEN
1918 RAISE G_EXCEPTION_ERROR;
1919 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1920 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1921 END IF;
1922
1923 FOR sir_data IN sir_data_csr(p_transaction_number)
1924 LOOP
1925 lp_sirv_rec.id := sir_data.id;
1926 lp_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1927 lp_sirv_rec.srt_code := p_srt_code;
1928
1929 Okl_Populate_Prceng_Result_Pvt.update_sif_rets (p_api_version => p_api_version,
1930 p_init_msg_list => p_init_msg_list,
1931 p_sirv_rec => lp_sirv_rec,
1932 x_sirv_rec => lx_sirv_rec,
1933 x_msg_count => lx_msg_count,
1934 x_msg_data => lx_msg_data,
1935 x_return_status => l_return_status);
1936
1937 IF l_return_status = G_RET_STS_ERROR THEN
1938 RAISE G_EXCEPTION_ERROR;
1939 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1940 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1941 END IF;
1942
1943 END LOOP;
1944
1945 x_return_status := l_return_status;
1946 EXCEPTION
1947 WHEN G_EXCEPTION_ERROR THEN
1948 x_return_status := G_RET_STS_ERROR;
1949 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1950 x_return_status := G_RET_STS_UNEXP_ERROR;
1951 WHEN OTHERS THEN
1952 x_return_status := G_RET_STS_UNEXP_ERROR;
1953
1954 END update_status;
1955
1956 -- mvasudev , 04/24/2002
1957 PROCEDURE update_status (
1958 p_transaction_number IN NUMBER,
1959 p_sis_code IN VARCHAR2, -- outbound status
1960 p_srt_code IN VARCHAR2, -- inbound status
1961 p_log_file_name IN VARCHAR2,
1962 x_return_status OUT NOCOPY VARCHAR2
1963 ) IS
1964 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
1965 CURSOR sif_data_csr (trx_number IN NUMBER) IS
1966 SELECT
1967 ID,ORP_CODE
1968 FROM Okl_Stream_Interfaces
1969 WHERE okl_stream_interfaces.transaction_number = trx_number;
1970
1971 CURSOR sir_data_csr (trx_number IN NUMBER) IS
1972 SELECT
1973 ID
1974 FROM Okl_Sif_Rets
1975 WHERE okl_sif_rets.transaction_number = trx_number;
1976
1977 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1978 lp_sifv_rec sifv_rec_type;
1979 lx_sifv_rec sifv_rec_type;
1980 lp_sirv_rec sirv_rec_type;
1981 lx_sirv_rec sirv_rec_type;
1982 lx_msg_count NUMBER;
1983 lx_msg_data VARCHAR2(100);
1984
1985 l_init_msg_list VARCHAR2(1) := 'F';
1986 l_api_version CONSTANT NUMBER := 1;
1987
1988 BEGIN
1989
1990 -- update the status in the In bound Interface Tables
1991 FOR sif_data IN sif_data_csr(p_transaction_number)
1992 LOOP
1993 lp_sifv_rec.id := sif_data.id;
1994 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
1995 END LOOP;
1996 lp_sifv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1997 lp_sifv_rec.sis_code := p_sis_code;
1998 IF(p_log_file_name <> 'TEMPVALUE')
1999 THEN
2000 lp_sifv_rec.log_file := p_log_file_name;
2001 END IF;
2002
2003
2004 Okl_Populate_Prceng_Result_Pvt.update_outbound_status (p_api_version => l_api_version,
2005 p_init_msg_list => l_init_msg_list,
2006 p_sifv_rec => lp_sifv_rec,
2007 x_sifv_rec => lx_sifv_rec,
2008 x_msg_count => lx_msg_count,
2009 x_msg_data => lx_msg_data,
2010 x_return_status => l_return_status);
2011
2012 IF l_return_status = G_RET_STS_ERROR THEN
2013 RAISE G_EXCEPTION_ERROR;
2014 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2015 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2016 END IF;
2017
2018 FOR sir_data IN sir_data_csr(p_transaction_number)
2019 LOOP
2020 lp_sirv_rec.id := sir_data.id;
2021 lp_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2022 lp_sirv_rec.srt_code := p_srt_code;
2023
2024 Okl_Populate_Prceng_Result_Pvt.update_sif_rets (p_api_version => l_api_version,
2025 p_init_msg_list => l_init_msg_list,
2026 p_sirv_rec => lp_sirv_rec,
2027 x_sirv_rec => lx_sirv_rec,
2028 x_msg_count => lx_msg_count,
2029 x_msg_data => lx_msg_data,
2030 x_return_status => l_return_status);
2031
2032 IF l_return_status = G_RET_STS_ERROR THEN
2033 RAISE G_EXCEPTION_ERROR;
2034 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2035 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2036 END IF;
2037
2038 END LOOP;
2039
2040 x_return_status := l_return_status;
2041 EXCEPTION
2042 WHEN G_EXCEPTION_ERROR THEN
2043 x_return_status := G_RET_STS_ERROR;
2044 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2045 x_return_status := G_RET_STS_UNEXP_ERROR;
2046 WHEN OTHERS THEN
2047 x_return_status := G_RET_STS_UNEXP_ERROR;
2048
2049 END update_status;
2050
2051 -- mvasudev , 04/24/2002 end
2052 ----------------------------------------------------------------------------------------
2053 -- PROCEDURE check_status: checks outbound status and if the outbound has time out
2054 -- returns a 'N' else returns 'Y' which indicates whether
2055 -- the inbound should proceed or not.
2056 ----------------------------------------------------------------------------------------
2057
2058 PROCEDURE check_status (
2059 p_transaction_number IN NUMBER,
2060 x_ok_to_proceed OUT NOCOPY VARCHAR2,
2061 x_return_status OUT NOCOPY VARCHAR2
2062 ) IS
2063
2064 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
2065 l_api_version CONSTANT NUMBER := 1;
2066 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2067
2068 CURSOR sif_data_csr (trx_number IN NUMBER) IS
2069 SELECT
2070 SIS_CODE
2071 FROM Okl_Stream_Interfaces
2072 WHERE okl_stream_interfaces.transaction_number = trx_number;
2073 BEGIN
2074 x_ok_to_proceed := 'Y';
2075 FOR sif_data IN sif_data_csr(p_transaction_number)
2076 LOOP
2077 IF sif_data.sis_code = 'TIME_OUT' or sif_data.sis_code = 'PROCESS_ABORTED' THEN
2078 x_ok_to_proceed := 'N';
2079 END IF;
2080 END LOOP;
2081 x_return_status := l_return_status;
2082 EXCEPTION
2083 WHEN G_EXCEPTION_ERROR THEN
2084 x_return_status := G_RET_STS_ERROR;
2085 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2086 x_return_status := G_RET_STS_UNEXP_ERROR;
2087 WHEN OTHERS THEN
2088 x_return_status := G_RET_STS_UNEXP_ERROR;
2089 END check_status;
2090
2091
2092 PROCEDURE log_error_messages (
2093 p_transaction_number IN NUMBER,
2094 x_return_status OUT NOCOPY VARCHAR2
2095 ) IS
2096 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2097 l_failure_code VARCHAR2(20) := 'PROCESSING_FAILED';
2098 l_error_message_tbl LOG_MSG_TBL_TYPE;
2099 l_msg_count NUMBER;
2100 l_log_file_name_pre VARCHAR2(15) := OKL_INVOKE_PRICING_ENGINE_PVT.G_FILENAME_PRE;
2101 l_log_file_name_ext VARCHAR2(15) := OKL_INVOKE_PRICING_ENGINE_PVT.G_FILENAME_EXT;
2102
2103 BEGIN
2104 -- log error message
2105 l_error_message_tbl(1) := 'Errors while processing Streams Results :- ';
2106 Okl_Streams_Util.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
2107 p_translate => G_FALSE,
2108 p_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext ,
2109 x_return_status => l_return_status );
2110
2111 l_msg_count := fnd_msg_pub.count_msg;
2112 Okl_Streams_Util.LOG_MESSAGE(p_msg_count => l_msg_count,
2113 p_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext,
2114 x_return_status => l_return_status);
2115
2116 l_error_message_tbl(1) := 'End Errors while processing Streams Results';
2117 Okl_Streams_Util.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
2118 p_translate => G_FALSE,
2119 p_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext ,
2120 x_return_status => l_return_status );
2121
2122 -- update status to PROCESSING_FAILED
2123 update_status (p_transaction_number => p_transaction_number,
2124 p_sis_code => l_failure_code, -- outbound status
2125 p_srt_code => l_failure_code, -- inbound status
2126 p_log_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext,
2127 x_return_status => l_return_status);
2128
2129 x_return_status := l_return_status;
2130 EXCEPTION
2131 WHEN G_EXCEPTION_ERROR THEN
2132 x_return_status := G_RET_STS_ERROR;
2133 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2134 x_return_status := G_RET_STS_UNEXP_ERROR;
2135 WHEN OTHERS THEN
2136 x_return_status := G_RET_STS_UNEXP_ERROR;
2137 END log_error_messages;
2138
2139
2140 PROCEDURE populate_sif_ret_levels (
2141 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
2142 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
2143 p_level_index_number IN NUMBER := OKC_API.G_MISS_NUM,
2144 p_number_of_periods IN NUMBER := OKC_API.G_MISS_NUM,
2145 p_level_type IN OKL_SIF_RET_LEVELS.LEVEL_TYPE%TYPE := OKC_API.G_MISS_CHAR,
2146 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
2147 p_advance_or_arrears IN OKL_SIF_RET_LEVELS.ADVANCE_OR_ARREARS%TYPE := OKC_API.G_MISS_CHAR,
2148 p_period IN OKL_SIF_RET_LEVELS.PERIOD%TYPE := OKC_API.G_MISS_CHAR,
2149 p_lock_level_step IN OKL_SIF_RET_LEVELS.LOCK_LEVEL_STEP%TYPE := OKC_API.G_MISS_CHAR,
2150 p_days_in_period IN NUMBER := OKC_API.G_MISS_NUM,
2151 p_first_payment_date IN VARCHAR2,
2152 p_rate IN NUMBER := OKC_API.G_MISS_NUM,
2153 x_return_status OUT NOCOPY VARCHAR2
2154 ) IS
2155 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_levels';
2156 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2157 l_srlv_rec srlv_rec_type;
2158 l_api_version CONSTANT NUMBER := 1;
2159 l_init_msg_list VARCHAR2(1) := 'F';
2160 l_first_payment_date VARCHAR2(30);
2161 l_advance_or_arrears VARCHAR2(1);
2162 l_lock_level_step VARCHAR2(1);
2163
2164 x_srlv_rec srlv_rec_type;
2165 x_msg_data VARCHAR2(400);
2166 x_msg_count NUMBER ;
2167
2168 BEGIN
2169 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2170 p_pkg_name => G_PKG_NAME,
2171 p_init_msg_list => l_init_msg_list,
2172 l_api_version => l_api_version,
2173 p_api_version => l_api_version,
2174 p_api_type => G_API_TYPE,
2175 x_return_status => l_return_status);
2176 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2177 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2179 RAISE G_EXCEPTION_ERROR;
2180 END IF;
2181 l_srlv_rec.sir_id := p_sir_id;
2182 l_srlv_rec.index_number := p_index_number;
2183 l_srlv_rec.level_index_number := p_level_index_number;
2184 l_srlv_rec.number_of_periods := p_number_of_periods;
2185 l_srlv_rec.level_type := p_level_type;
2186 l_srlv_rec.amount := p_amount;
2187 OKL_ST_CODE_CONVERSIONS.REVERSE_TRANSLATE_YN(p_advance_or_arrears, l_advance_or_arrears);
2188 l_srlv_rec.advance_or_arrears := l_advance_or_arrears;
2189 l_srlv_rec.period := p_period;
2190 OKL_ST_CODE_CONVERSIONS.REVERSE_TRANSLATE_YN(p_lock_level_step, l_lock_level_step);
2191 l_srlv_rec.lock_level_step := l_lock_level_step;
2192 l_srlv_rec.days_in_period := p_days_in_period;
2193 l_srlv_rec.rate := p_rate; --smahapat 10/12/03
2194
2195 l_first_payment_date := p_first_payment_date;
2196 l_first_payment_date := correct_feb_date(l_first_payment_date);
2197
2198 l_srlv_rec.first_payment_date := TO_DATE(l_first_payment_date, 'YYYY-MM-DD');
2199
2200
2201 /* Call main API */
2202 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_levels(p_api_version => l_api_version,
2203 p_init_msg_list => l_init_msg_list,
2204 x_return_status => l_return_status,
2205 x_msg_count => x_msg_count,
2206 x_msg_data => x_msg_data,
2207 p_srlv_rec => l_srlv_rec,
2208 x_srlv_rec => x_srlv_rec);
2209 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2210 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2211 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2212 RAISE G_EXCEPTION_ERROR;
2213 END IF;
2214
2215 -- Assign record returned by private api to local record
2216 l_srlv_rec := x_srlv_rec;
2217
2218
2219 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2220 x_msg_data => x_msg_data);
2221 x_return_status := l_return_status;
2222 EXCEPTION
2223 WHEN G_EXCEPTION_ERROR THEN
2224 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2225 p_pkg_name => G_PKG_NAME,
2226 p_exc_name => G_EXC_NAME_ERROR,
2227 x_msg_count => x_msg_count,
2228 x_msg_data => x_msg_data,
2229 p_api_type => G_API_TYPE);
2230 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2231 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2232 p_pkg_name => G_PKG_NAME,
2233 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2234 x_msg_count => x_msg_count,
2235 x_msg_data => x_msg_data,
2236 p_api_type => G_API_TYPE);
2237 WHEN OTHERS THEN
2238 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2239 p_pkg_name => G_PKG_NAME,
2240 p_exc_name => G_EXC_NAME_OTHERS,
2241 x_msg_count => x_msg_count,
2242 x_msg_data => x_msg_data,
2243 p_api_type => G_API_TYPE);
2244 END populate_sif_ret_levels;
2245
2246 END OKL_POPULATE_PRCENG_RESULT_PUB;