[Home] [Help]
PACKAGE BODY: APPS.OKL_POPULATE_PRCENG_RST_PUB
Source
1 PACKAGE BODY OKL_POPULATE_PRCENG_RST_PUB AS
2 /*$Header: OKLPPRSB.pls 120.9.12020000.2 2013/02/20 11:20:03 racheruv ship $*/
3 --G_MODULE VARCHAR2(40) := 'LEASE.STREAMS';
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_populate_prceng_rst_pub';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_EXCEPTION_ON BOOLEAN;
7 G_IS_DEBUG_ERROR_ON BOOLEAN;
8 G_IS_DEBUG_PROCEDURE_ON BOOLEAN;
9
10 G_TRANSACTION_NUMBER NUMBER;
11
12
13 --------------------------------------------------------------------------------
14 -- FUNCTION: feb_date
15 -- This is a temporary fix for the wrong dates returned by SuperTrump e.g. 02/30
16 -- The function parses the given date and if the date is 02/30 returns 02/29 or
17 -- 02/28 if it is a leap year or not respectively.
18 --------------------------------------------------------------------------------
19
20 FUNCTION correct_feb_date (p_date VARCHAR2)
21 RETURN VARCHAR2
22 IS
23 l_sre_date VARCHAR2(100);
24 l_feb_date VARCHAR2(100);
25 l_year VARCHAR2(100);
26 BEGIN
27 l_sre_date := p_date;
28 l_feb_date := SUBSTR(l_sre_date, 6,5);
29 l_year := SUBSTR(l_sre_date, 1,4);
30
31 IF(l_feb_date = '02-30' OR l_feb_date = '02-29') THEN
32 l_feb_date := '02-28';
33 IF MOD (TO_NUMBER(l_year), 4) = 0 THEN
34 IF (MOD (TO_NUMBER(l_year), 100) = 0) THEN
35 IF (MOD (TO_NUMBER(l_year), 400) = 0) THEN
36 l_feb_date := '02-29';
37 END IF;
38 ELSE
39 l_feb_date := '02-29';
40 END IF;
41 END IF;
42 l_year := SUBSTR(l_sre_date, 1,5);
43 l_sre_date := l_year || l_feb_date;
44 END IF;
45 RETURN l_sre_date;
46 END correct_feb_date;
47
48 --Added by BKATRAGA.
49 --This procedure is used in the populate_sif_ret_strms procedure prior to the bulk insert call.
50
51 ---------------------------------------------------------------------------
52 -- PROCEDURE populate_sif_rets for: OKL_SIF_RETS_V
53 ---------------------------------------------------------------------------
54 PROCEDURE populate_sif_rets(p_api_version IN NUMBER := 1.0,
55 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_id OUT NOCOPY NUMBER,
58 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
59 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
60 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
61 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
62 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
63 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
64 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
65 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
66 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM,
67 p_date_processed IN DATE
68
69 ) IS
70 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
71 l_api_version CONSTANT NUMBER := 1;
72 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
73 l_sirv_rec sirv_rec_type;
74 x_sirv_rec sirv_rec_type;
75 x_msg_data VARCHAR2(400);
76 x_msg_count NUMBER ;
77 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
78
79 BEGIN
80 G_TRANSACTION_NUMBER := p_transaction_number;
81
82 IF(G_DEBUG_ENABLED = 'Y') THEN
83 G_IS_DEBUG_EXCEPTION_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_EXCEPTION);
84 END IF;
85
86 IF(G_DEBUG_ENABLED = 'Y') THEN
87 G_IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_PROCEDURE);
88 END IF;
89
90 IF(G_DEBUG_ENABLED = 'Y') THEN
91 G_IS_DEBUG_ERROR_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_ERROR);
92 END IF;
93
94 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
95 p_pkg_name => G_PKG_NAME,
96 p_init_msg_list => p_init_msg_list,
97 l_api_version => l_api_version,
98 p_api_version => p_api_version,
99 p_api_type => G_API_TYPE,
100 x_return_status => l_return_status);
101 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
102 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
103 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
104 RAISE G_EXCEPTION_ERROR;
105 END IF;
106
107 l_sirv_rec.transaction_number := p_transaction_number;
108 l_sirv_rec.srt_code := p_srt_code;
109
110 l_sirv_rec.yield_name := p_yield_name;
111 l_sirv_rec.index_number := p_index_number;
112
113 IF(p_effective_pre_tax_yield = -100)
114 THEN
115 l_sirv_rec.effective_after_tax_yield := NULL;
116 ELSE
117 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
118 END IF;
119
120 IF(p_effective_after_tax_yield = -100)
121 THEN
122 l_sirv_rec.effective_after_tax_yield := NULL;
123 ELSE
124 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
125 END IF;
126
127 IF(p_nominal_pre_tax_yield = -100)
128 THEN
129 l_sirv_rec.nominal_pre_tax_yield := NULL;
130 ELSE
131 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
132 END IF;
133
134 IF(p_nominal_after_tax_yield = -100)
135 THEN
136 l_sirv_rec.nominal_after_tax_yield := NULL;
137 ELSE
138 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
139 END IF;
140
141
142 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
143 l_sirv_rec.implicit_interest_rate := NULL;
144 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
145
146 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
147 -- l_sirv_rec.date_processed := p_date_processed;
148
149
150 /* Call main API */
151 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => p_api_version,
152 p_init_msg_list => p_init_msg_list,
153 x_return_status => l_return_status,
154 x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data,
156 p_sirv_rec => l_sirv_rec,
157 x_sirv_rec => x_sirv_rec);
158 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
159 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
160 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
161 RAISE G_EXCEPTION_ERROR;
162 END IF;
163
164 -- Assign id returned to corresponding out parameter
165 x_id := x_sirv_rec.id;
166 -- Assign record returned by private api to local record
167 l_sirv_rec := x_sirv_rec;
168
169
170
171 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
172 x_msg_data => x_msg_data);
173 x_return_status := l_return_status;
174 EXCEPTION
175 WHEN G_EXCEPTION_ERROR THEN
176
177 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
178 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
179 END IF;
180
181 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
182 p_pkg_name => G_PKG_NAME,
183 p_exc_name => G_EXC_NAME_ERROR,
184 x_msg_count => x_msg_count,
185 x_msg_data => x_msg_data,
186 p_api_type => G_API_TYPE);
187 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
188
189 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
190 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
191 END IF;
192
193 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
194 p_pkg_name => G_PKG_NAME,
195 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
196 x_msg_count => x_msg_count,
197 x_msg_data => x_msg_data,
198 p_api_type => G_API_TYPE);
199 WHEN OTHERS THEN
200
201 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
202 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||SQLERRM(SQLCODE));
203 END IF;
204
205 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
206 p_pkg_name => G_PKG_NAME,
207 p_exc_name => G_EXC_NAME_OTHERS,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data,
210 p_api_type => G_API_TYPE);
211 END populate_sif_rets;
212
213 -- mvasudev , 04/24/2002
214 ---------------------------------------------------------------------------
215 -- PROCEDURE populate_sif_rets for: OKL_SIF_RETS_V
216 ---------------------------------------------------------------------------
217 PROCEDURE populate_sif_rets(x_return_status OUT NOCOPY VARCHAR2,
218 x_id OUT NOCOPY NUMBER,
219 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
220 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
221 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
222 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
223 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
224 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
225 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
226 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
227 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM,
228 p_date_processed IN DATE
229
230 ) IS
231 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
232 l_api_version CONSTANT NUMBER := 1;
233 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
234 l_sirv_rec sirv_rec_type;
235 x_sirv_rec sirv_rec_type;
236 x_msg_data VARCHAR2(400);
237 x_msg_count NUMBER ;
238
239 l_init_msg_list VARCHAR2(1) := 'F';
240
241 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
242 BEGIN
243
244 G_TRANSACTION_NUMBER := p_transaction_number;
245
246 IF(G_DEBUG_ENABLED = 'Y') THEN
247 G_IS_DEBUG_EXCEPTION_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_EXCEPTION);
248 END IF;
249
250 IF(G_DEBUG_ENABLED = 'Y') THEN
251 G_IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_PROCEDURE);
252 END IF;
253
254 IF(G_DEBUG_ENABLED = 'Y') THEN
255 G_IS_DEBUG_ERROR_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_ERROR);
256 END IF;
257
258 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
259 p_pkg_name => G_PKG_NAME,
260 p_init_msg_list => l_init_msg_list,
261 l_api_version => l_api_version,
262 p_api_version => l_api_version,
263 p_api_type => G_API_TYPE,
264 x_return_status => l_return_status);
265 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
266 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
267 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
268 RAISE G_EXCEPTION_ERROR;
269 END IF;
270
271 l_sirv_rec.transaction_number := p_transaction_number;
272 l_sirv_rec.srt_code := p_srt_code;
273
274 l_sirv_rec.yield_name := p_yield_name;
275 l_sirv_rec.index_number := p_index_number;
276
277 IF(p_effective_pre_tax_yield = -100)
278 THEN
279 l_sirv_rec.effective_after_tax_yield := NULL;
280 ELSE
281 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
282 END IF;
283
284 IF(p_effective_after_tax_yield = -100)
285 THEN
286 l_sirv_rec.effective_after_tax_yield := NULL;
287 ELSE
288 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
289 END IF;
290
291 IF(p_nominal_pre_tax_yield = -100)
292 THEN
293 l_sirv_rec.nominal_pre_tax_yield := NULL;
294 ELSE
295 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
296 END IF;
297
298 IF(p_nominal_after_tax_yield = -100)
299 THEN
300 l_sirv_rec.nominal_after_tax_yield := NULL;
301 ELSE
302 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
303 END IF;
304
305
306 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
307 l_sirv_rec.implicit_interest_rate := NULL;
308 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
309
310 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
311 -- l_sirv_rec.date_processed := p_date_processed;
312
313
314 /* Call main API */
315 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => l_api_version,
316 p_init_msg_list => l_init_msg_list,
317 x_return_status => l_return_status,
318 x_msg_count => x_msg_count,
319 x_msg_data => x_msg_data,
320 p_sirv_rec => l_sirv_rec,
321 x_sirv_rec => x_sirv_rec);
322 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
323 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
324 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
325 RAISE G_EXCEPTION_ERROR;
326 END IF;
327
328 -- Assign id returned to corresponding out parameter
329 x_id := x_sirv_rec.id;
330 -- Assign record returned by private api to local record
331 l_sirv_rec := x_sirv_rec;
332
333
334
335 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
336 x_msg_data => x_msg_data);
337 x_return_status := l_return_status;
338 EXCEPTION
339 WHEN G_EXCEPTION_ERROR THEN
340
341 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
342 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
343 END IF;
344
345 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
346 p_pkg_name => G_PKG_NAME,
347 p_exc_name => G_EXC_NAME_ERROR,
348 x_msg_count => x_msg_count,
349 x_msg_data => x_msg_data,
350 p_api_type => G_API_TYPE);
351
352 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
353
354 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
355 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
356 END IF;
357
358 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
359 p_pkg_name => G_PKG_NAME,
360 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
361 x_msg_count => x_msg_count,
362 x_msg_data => x_msg_data,
363 p_api_type => G_API_TYPE);
364 WHEN OTHERS THEN
365 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
366 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||SQLERRM(SQLCODE));
367 END IF;
368
369 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
370 p_pkg_name => G_PKG_NAME,
371 p_exc_name => G_EXC_NAME_OTHERS,
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data,
374 p_api_type => G_API_TYPE);
375 END populate_sif_rets;
376 -- mvasudev , 04/24/2002 end
377
378 ----------------------------------------------------------------------------------------
379 -- PROCEUDRE populate_sif_rets
380 ----------------------------------------------------------------------------------------
381
382 PROCEDURE populate_sif_rets(p_api_version IN NUMBER := 1.0,
383 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_id OUT NOCOPY NUMBER,
386 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
387 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
388 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
389 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
390 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
391 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
392 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
393 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
394 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM
395 ) IS
396 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
397 l_api_version CONSTANT NUMBER := 1;
398 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
399 l_sirv_rec sirv_rec_type;
400 x_sirv_rec sirv_rec_type;
401 x_msg_data VARCHAR2(400);
402 x_msg_count NUMBER ;
403
404 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
405 BEGIN
406
407 G_TRANSACTION_NUMBER := p_transaction_number;
408
409 IF(G_DEBUG_ENABLED = 'Y') THEN
410 G_IS_DEBUG_EXCEPTION_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_EXCEPTION);
411 END IF;
412
413 IF(G_DEBUG_ENABLED = 'Y') THEN
414 G_IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_PROCEDURE);
415 END IF;
416
417 IF(G_DEBUG_ENABLED = 'Y') THEN
418 G_IS_DEBUG_ERROR_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_ERROR);
419 END IF;
420
421 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
422 p_pkg_name => G_PKG_NAME,
423 p_init_msg_list => p_init_msg_list,
424 l_api_version => l_api_version,
425 p_api_version => p_api_version,
426 p_api_type => G_API_TYPE,
427 x_return_status => l_return_status);
428 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
429 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
430 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
431 RAISE G_EXCEPTION_ERROR;
432 END IF;
433
434 l_sirv_rec.transaction_number := p_transaction_number;
435 l_sirv_rec.srt_code := p_srt_code;
436
437 l_sirv_rec.yield_name := p_yield_name;
438 l_sirv_rec.index_number := p_index_number;
439
440 IF(p_effective_pre_tax_yield = -100)
441 THEN
442 l_sirv_rec.effective_after_tax_yield := NULL;
443 ELSE
444 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
445 END IF;
446
447 IF(p_effective_after_tax_yield = -100)
448 THEN
449 l_sirv_rec.effective_after_tax_yield := NULL;
450 ELSE
451 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
452 END IF;
453
454 IF(p_nominal_pre_tax_yield = -100)
455 THEN
456 l_sirv_rec.nominal_pre_tax_yield := NULL;
457 ELSE
458 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
459 END IF;
460
461 IF(p_nominal_after_tax_yield = -100)
462 THEN
463 l_sirv_rec.nominal_after_tax_yield := NULL;
464 ELSE
465 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
466 END IF;
467
468 /*
469 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
470 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
471 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
472 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
473 */
474 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
475 l_sirv_rec.implicit_interest_rate := NULL;
476 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
477
478 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
479
480
481 /* Call main API */
482 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => p_api_version,
483 p_init_msg_list => p_init_msg_list,
484 x_return_status => l_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_sirv_rec => l_sirv_rec,
488 x_sirv_rec => x_sirv_rec);
489 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
490 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
491 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
492 RAISE G_EXCEPTION_ERROR;
493 END IF;
494
495 -- Assign id returned to corresponding out parameter
496 x_id := x_sirv_rec.id;
497 -- Assign record returned by private api to local record
498 l_sirv_rec := x_sirv_rec;
499
500
501
502 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
503 x_msg_data => x_msg_data);
504 x_return_status := l_return_status;
505 EXCEPTION
506 WHEN G_EXCEPTION_ERROR THEN
507
508 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
509 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
510 END IF;
511
512 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
513 p_pkg_name => G_PKG_NAME,
514 p_exc_name => G_EXC_NAME_ERROR,
515 x_msg_count => x_msg_count,
516 x_msg_data => x_msg_data,
517 p_api_type => G_API_TYPE);
518 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
519
520 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
521 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
522 END IF;
523
524 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
525 p_pkg_name => G_PKG_NAME,
526 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
527 x_msg_count => x_msg_count,
528 x_msg_data => x_msg_data,
529 p_api_type => G_API_TYPE);
530 WHEN OTHERS THEN
531 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
532 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||SQLERRM(SQLCODE));
533 END IF;
534
535 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
536 p_pkg_name => G_PKG_NAME,
537 p_exc_name => G_EXC_NAME_OTHERS,
538 x_msg_count => x_msg_count,
539 x_msg_data => x_msg_data,
540 p_api_type => G_API_TYPE);
541 END populate_sif_rets;
542
543 -- mvasudev , 04/24/2002
544 ----------------------------------------------------------------------------------------
545 -- PROCEUDRE populate_sif_rets
546 ----------------------------------------------------------------------------------------
547
548 PROCEDURE populate_sif_rets(x_return_status OUT NOCOPY VARCHAR2,
549 x_id OUT NOCOPY NUMBER,
550 p_transaction_number IN NUMBER := OKC_API.G_MISS_NUM,
551 p_srt_code IN OKL_SIF_RETS.SRT_CODE%TYPE := OKC_API.G_MISS_CHAR,
552 p_effective_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
553 p_yield_name IN OKL_SIF_RETS.YIELD_NAME%TYPE := OKC_API.G_MISS_CHAR,
554 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
555 p_effective_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
556 p_nominal_pre_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
557 p_nominal_after_tax_yield IN NUMBER := OKC_API.G_MISS_NUM,
558 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM
559 ) IS
560 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
561 l_api_version CONSTANT NUMBER := 1;
562 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
563 l_sirv_rec sirv_rec_type;
564 x_sirv_rec sirv_rec_type;
565 x_msg_data VARCHAR2(400);
566 x_msg_count NUMBER ;
567
568 l_init_msg_list VARCHAR2(1) := 'F';
569
570 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
571 BEGIN
572
573 G_TRANSACTION_NUMBER := p_transaction_number;
574
575 IF(G_DEBUG_ENABLED = 'Y') THEN
576 G_IS_DEBUG_EXCEPTION_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_EXCEPTION);
577 END IF;
578
579 IF(G_DEBUG_ENABLED = 'Y') THEN
580 G_IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_PROCEDURE);
581 END IF;
582
583 IF(G_DEBUG_ENABLED = 'Y') THEN
584 G_IS_DEBUG_ERROR_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_ERROR);
585 END IF;
586
587 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
588 p_pkg_name => G_PKG_NAME,
589 p_init_msg_list => l_init_msg_list,
590 l_api_version => l_api_version,
591 p_api_version => l_api_version,
592 p_api_type => G_API_TYPE,
593 x_return_status => l_return_status);
594 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
595 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
596 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
597 RAISE G_EXCEPTION_ERROR;
598 END IF;
599
600 l_sirv_rec.transaction_number := p_transaction_number;
601 l_sirv_rec.srt_code := p_srt_code;
602
603 l_sirv_rec.yield_name := p_yield_name;
604 l_sirv_rec.index_number := p_index_number;
605
606 IF(p_effective_pre_tax_yield = -100)
607 THEN
608 l_sirv_rec.effective_after_tax_yield := NULL;
609 ELSE
610 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
611 END IF;
612
613 IF(p_effective_after_tax_yield = -100)
614 THEN
615 l_sirv_rec.effective_after_tax_yield := NULL;
616 ELSE
617 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
618 END IF;
619
620 IF(p_nominal_pre_tax_yield = -100)
621 THEN
622 l_sirv_rec.nominal_pre_tax_yield := NULL;
623 ELSE
624 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
625 END IF;
626
627 IF(p_nominal_after_tax_yield = -100)
628 THEN
629 l_sirv_rec.nominal_after_tax_yield := NULL;
630 ELSE
631 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
632 END IF;
633
634 /*
635 l_sirv_rec.effective_after_tax_yield := p_effective_after_tax_yield;
636 l_sirv_rec.nominal_pre_tax_yield := p_nominal_pre_tax_yield;
637 l_sirv_rec.nominal_after_tax_yield := p_nominal_after_tax_yield;
638 l_sirv_rec.effective_pre_tax_yield := p_effective_pre_tax_yield;
639 */
640 -- l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
641 l_sirv_rec.implicit_interest_rate := NULL;
642 -- DATE TIME THIS TRANSACTION IS PROCESSED YYYYMMDD HH24MISS
643
644 l_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
645
646
647 /* Call main API */
648 Okl_Populate_Prceng_Result_Pvt.populate_sif_rets (p_api_version => l_api_version,
649 p_init_msg_list => l_init_msg_list,
650 x_return_status => l_return_status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data,
653 p_sirv_rec => l_sirv_rec,
654 x_sirv_rec => x_sirv_rec);
655 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
656 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
657 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
658 RAISE G_EXCEPTION_ERROR;
659 END IF;
660
661 -- Assign id returned to corresponding out parameter
662 x_id := x_sirv_rec.id;
663 -- Assign record returned by private api to local record
664 l_sirv_rec := x_sirv_rec;
665
666
667
668 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
669 x_msg_data => x_msg_data);
670 x_return_status := l_return_status;
671 EXCEPTION
672 WHEN G_EXCEPTION_ERROR THEN
673
674 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
675 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
676 END IF;
677
678 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
679 p_pkg_name => G_PKG_NAME,
680 p_exc_name => G_EXC_NAME_ERROR,
681 x_msg_count => x_msg_count,
682 x_msg_data => x_msg_data,
683 p_api_type => G_API_TYPE);
684 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
685 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
686 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
687 END IF;
688
689 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
690 p_pkg_name => G_PKG_NAME,
691 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
692 x_msg_count => x_msg_count,
693 x_msg_data => x_msg_data,
694 p_api_type => G_API_TYPE);
695 WHEN OTHERS THEN
696 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
697 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||x_msg_data);
698 END IF;
699
700 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
701 p_pkg_name => G_PKG_NAME,
702 p_exc_name => G_EXC_NAME_OTHERS,
703 x_msg_count => x_msg_count,
704 x_msg_data => x_msg_data,
705 p_api_type => G_API_TYPE);
706 END populate_sif_rets;
707 -- mvasudev, 04/24/2002 end
708
709
710
711
712 -- Added by saran
713
714 PROCEDURE update_sif_rets (p_api_version IN NUMBER := 1.0,
715 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
716 x_return_status OUT NOCOPY VARCHAR2,
717 p_id IN NUMBER,
718 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM)
719 IS
720 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
721 l_api_version CONSTANT NUMBER := 1;
722 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
723 l_sirv_rec sirv_rec_type;
724 x_sirv_rec sirv_rec_type;
725 x_msg_data VARCHAR2(400);
726 x_msg_count NUMBER ;
727
728 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
729 BEGIN
730
731 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
732 p_pkg_name => G_PKG_NAME,
733 p_init_msg_list => p_init_msg_list,
734 l_api_version => l_api_version,
735 p_api_version => p_api_version,
736 p_api_type => G_API_TYPE,
737 x_return_status => l_return_status);
738 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
739 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
740 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
741 RAISE G_EXCEPTION_ERROR;
742 END IF;
743
744 l_sirv_rec.id := p_id;
745 l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
746
747
748
749 /* Call main API */
750 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => p_api_version,
751 p_init_msg_list => p_init_msg_list,
752 x_return_status => l_return_status,
753 x_msg_count => x_msg_count,
754 x_msg_data => x_msg_data,
755 p_sirv_rec => l_sirv_rec,
756 x_sirv_rec => x_sirv_rec);
757
758 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
759 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
760 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
761 RAISE G_EXCEPTION_ERROR;
762 END IF;
763
764 -- Assign record returned by private api to local record
765 l_sirv_rec := x_sirv_rec;
766
767
768 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
769 x_msg_data => x_msg_data);
770 x_return_status := l_return_status;
771 EXCEPTION
772 WHEN G_EXCEPTION_ERROR THEN
773
774 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
775 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
776 END IF;
777
778 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
779 p_pkg_name => G_PKG_NAME,
780 p_exc_name => G_EXC_NAME_ERROR,
781 x_msg_count => x_msg_count,
782 x_msg_data => x_msg_data,
783 p_api_type => G_API_TYPE);
784 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
785
786 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
787 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
788 END IF;
789
790 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
791 p_pkg_name => G_PKG_NAME,
792 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 p_api_type => G_API_TYPE);
796 WHEN OTHERS THEN
797
798 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
799 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
800 END IF;
801
802 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
803 p_pkg_name => G_PKG_NAME,
804 p_exc_name => G_EXC_NAME_OTHERS,
805 x_msg_count => x_msg_count,
806 x_msg_data => x_msg_data,
807 p_api_type => G_API_TYPE);
808 END update_sif_rets;
809
810 -- mvasudev, 04/24/2002
811 PROCEDURE update_sif_rets (x_return_status OUT NOCOPY VARCHAR2,
812 p_id IN NUMBER,
813 p_implicit_interest_rate IN NUMBER := OKC_API.G_MISS_NUM)
814 IS
815 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
816
817 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
818 l_sirv_rec sirv_rec_type;
819 x_sirv_rec sirv_rec_type;
820 x_msg_data VARCHAR2(400);
821 x_msg_count NUMBER ;
822
823 l_init_msg_list VARCHAR2(1) := 'F';
824 l_api_version CONSTANT NUMBER := 1;
825
826 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
827 BEGIN
828
829 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
830 p_pkg_name => G_PKG_NAME,
831 p_init_msg_list => l_init_msg_list,
832 l_api_version => l_api_version,
833 p_api_version => l_api_version,
834 p_api_type => G_API_TYPE,
835 x_return_status => l_return_status);
836 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
837 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
838 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
839 RAISE G_EXCEPTION_ERROR;
840 END IF;
841
842 l_sirv_rec.id := p_id;
843 l_sirv_rec.implicit_interest_rate := p_implicit_interest_rate;
844
845
846
847 /* Call main API */
848 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => l_api_version,
849 p_init_msg_list => l_init_msg_list,
850 x_return_status => l_return_status,
851 x_msg_count => x_msg_count,
852 x_msg_data => x_msg_data,
853 p_sirv_rec => l_sirv_rec,
854 x_sirv_rec => x_sirv_rec);
855
856 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
857 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
858 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
859 RAISE G_EXCEPTION_ERROR;
860 END IF;
861
862 -- Assign record returned by private api to local record
863 l_sirv_rec := x_sirv_rec;
864
865
866 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
867 x_msg_data => x_msg_data);
868 x_return_status := l_return_status;
869 EXCEPTION
870 WHEN G_EXCEPTION_ERROR THEN
871
872 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
873 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
874 END IF;
875
876 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
877 p_pkg_name => G_PKG_NAME,
878 p_exc_name => G_EXC_NAME_ERROR,
879 x_msg_count => x_msg_count,
880 x_msg_data => x_msg_data,
881 p_api_type => G_API_TYPE);
882 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
883
884 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
885 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
886 END IF;
887
888 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
889 p_pkg_name => G_PKG_NAME,
890 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data,
893 p_api_type => G_API_TYPE);
894 WHEN OTHERS THEN
895
896 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
897 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
898 END IF;
899
900 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
901 p_pkg_name => G_PKG_NAME,
902 p_exc_name => G_EXC_NAME_OTHERS,
903 x_msg_count => x_msg_count,
904 x_msg_data => x_msg_data,
905 p_api_type => G_API_TYPE);
906 END update_sif_rets;
907 -- mvasudev , 04/24/2002 end
908
909 ----------------------------------------------------------------------------------
910 -- PROCEDURE update_sif_rets
911 ----------------------------------------------------------------------------------
912 PROCEDURE update_sif_rets (p_api_version IN NUMBER := 1.0,
913 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
914 p_id IN NUMBER,
915 p_yield_name IN VARCHAR2,
916 p_amount IN NUMBER,
917 x_return_status OUT NOCOPY VARCHAR2)
918 IS
919 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
920 l_api_version CONSTANT NUMBER := 1;
921 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
922 l_sirv_rec sirv_rec_type;
923 lx_sirv_rec sirv_rec_type;
924 lx_msg_data VARCHAR2(400);
925 lx_msg_count NUMBER ;
926
927 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
928 BEGIN
929
930 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
931 p_pkg_name => G_PKG_NAME,
932 p_init_msg_list => p_init_msg_list,
933 l_api_version => l_api_version,
934 p_api_version => p_api_version,
935 p_api_type => G_API_TYPE,
936 x_return_status => l_return_status);
937 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
938 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
939 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
940 RAISE G_EXCEPTION_ERROR;
941 END IF;
942
943 l_sirv_rec.id := p_id;
944 l_sirv_rec.effective_pre_tax_yield := p_amount;
945 l_sirv_rec.yield_name := p_yield_name;
946
947
948
949 /* Call main API */
950 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => p_api_version,
951 p_init_msg_list => p_init_msg_list,
952 x_return_status => l_return_status,
953 x_msg_count => lx_msg_count,
954 x_msg_data => lx_msg_data,
955 p_sirv_rec => l_sirv_rec,
956 x_sirv_rec => lx_sirv_rec);
957
958 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
959 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
960 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
961 RAISE G_EXCEPTION_ERROR;
962 END IF;
963
964 -- Assign record returned by private api to local record
965 l_sirv_rec := lx_sirv_rec;
966
967
968 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
969 x_msg_data => lx_msg_data);
970 x_return_status := l_return_status;
971 EXCEPTION
972 WHEN G_EXCEPTION_ERROR THEN
973
974 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
975 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||lx_msg_data);
976 END IF;
977
978 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
979 p_pkg_name => G_PKG_NAME,
980 p_exc_name => G_EXC_NAME_ERROR,
981 x_msg_count => lx_msg_count,
982 x_msg_data => lx_msg_data,
983 p_api_type => G_API_TYPE);
984 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
985
986 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
987 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||lx_msg_data);
988 END IF;
989
990 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
991 p_pkg_name => G_PKG_NAME,
992 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
993 x_msg_count => lx_msg_count,
994 x_msg_data => lx_msg_data,
995 p_api_type => G_API_TYPE);
996 WHEN OTHERS THEN
997
998 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
999 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||lx_msg_data);
1000 END IF;
1001
1002 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1003 p_pkg_name => G_PKG_NAME,
1004 p_exc_name => G_EXC_NAME_OTHERS,
1005 x_msg_count => lx_msg_count,
1006 x_msg_data => lx_msg_data,
1007 p_api_type => G_API_TYPE);
1008 END update_sif_rets;
1009
1010 -- mvasudev , 04/24/2002
1011 ----------------------------------------------------------------------------------
1012 -- PROCEDURE update_sif_rets
1013 ----------------------------------------------------------------------------------
1014 PROCEDURE update_sif_rets (p_id IN NUMBER,
1015 p_yield_name IN VARCHAR2,
1016 p_amount IN NUMBER,
1017 x_return_status OUT NOCOPY VARCHAR2)
1018 IS
1019 l_api_name CONSTANT VARCHAR2(30) := 'update_sif_rets';
1020 l_api_version CONSTANT NUMBER := 1;
1021 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1022 l_sirv_rec sirv_rec_type;
1023 lx_sirv_rec sirv_rec_type;
1024 lx_msg_data VARCHAR2(400);
1025 lx_msg_count NUMBER ;
1026
1027 l_init_msg_list VARCHAR2(1) := 'F';
1028
1029 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1030 BEGIN
1031
1032 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1033 p_pkg_name => G_PKG_NAME,
1034 p_init_msg_list => l_init_msg_list,
1035 l_api_version => l_api_version,
1036 p_api_version => l_api_version,
1037 p_api_type => G_API_TYPE,
1038 x_return_status => l_return_status);
1039 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1040 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1041 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1042 RAISE G_EXCEPTION_ERROR;
1043 END IF;
1044
1045 l_sirv_rec.id := p_id;
1046 l_sirv_rec.effective_pre_tax_yield := p_amount;
1047 l_sirv_rec.yield_name := p_yield_name;
1048
1049
1050
1051 /* Call main API */
1052 Okl_Populate_Prceng_Result_Pvt.update_sif_rets(p_api_version => l_api_version,
1053 p_init_msg_list => l_init_msg_list,
1054 x_return_status => l_return_status,
1055 x_msg_count => lx_msg_count,
1056 x_msg_data => lx_msg_data,
1057 p_sirv_rec => l_sirv_rec,
1058 x_sirv_rec => lx_sirv_rec);
1059
1060 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1062 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1063 RAISE G_EXCEPTION_ERROR;
1064 END IF;
1065
1066 -- Assign record returned by private api to local record
1067 l_sirv_rec := lx_sirv_rec;
1068
1069
1070 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
1071 x_msg_data => lx_msg_data);
1072 x_return_status := l_return_status;
1073 EXCEPTION
1074 WHEN G_EXCEPTION_ERROR THEN
1075
1076 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1077 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||lx_msg_count);
1078 END IF;
1079
1080 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1081 p_pkg_name => G_PKG_NAME,
1082 p_exc_name => G_EXC_NAME_ERROR,
1083 x_msg_count => lx_msg_count,
1084 x_msg_data => lx_msg_data,
1085 p_api_type => G_API_TYPE);
1086 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1087
1088 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1089 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||lx_msg_count);
1090 END IF;
1091
1092 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1093 p_pkg_name => G_PKG_NAME,
1094 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1095 x_msg_count => lx_msg_count,
1096 x_msg_data => lx_msg_data,
1097 p_api_type => G_API_TYPE);
1098 WHEN OTHERS THEN
1099 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1100 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||lx_msg_count);
1101 END IF;
1102
1103 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1104 p_pkg_name => G_PKG_NAME,
1105 p_exc_name => G_EXC_NAME_OTHERS,
1106 x_msg_count => lx_msg_count,
1107 x_msg_data => lx_msg_data,
1108 p_api_type => G_API_TYPE);
1109 END update_sif_rets;
1110 -- mvasudev, 04/24/2002 end
1111
1112 ---------------------------------------------------------------------------
1113 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
1114 ---------------------------------------------------------------------------
1115 -- Added by BKATRAGA on 02/24/2005
1116 -- This procedure has been modified to accept a table of records in place of
1117 -- a single record.
1118 -- Bug - Start of Changes
1119 PROCEDURE populate_sif_ret_strms (x_return_status OUT NOCOPY VARCHAR2,
1120 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
1121 p_strm_tbl IN strm_tbl_type,
1122 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
1123 ) IS
1124
1125 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1126 l_sre_date VARCHAR2(30);
1127 l_amount NUMBER;
1128 i NUMBER := 0;
1129 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
1130 x_msg_data VARCHAR2(400);
1131 x_msg_count NUMBER;
1132 p_init_msg_list VARCHAR2(1) := 'F';
1133 p_api_version NUMBER := 1.0;
1134 l_api_version CONSTANT NUMBER := 1;
1135 l_strm_rec strm_rec_type;
1136
1137 l_srsv_rec srsv_rec_type;
1138 l_srsv_tbl srsv_tbl_type;
1139 p_activity_type OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR;
1140 p_se_line_number NUMBER := 1;
1141 l_counter NUMBER := 0;
1142 l_stream_type_name OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR;
1143 l_description VARCHAR2(150) := OKC_API.G_MISS_CHAR;
1144
1145 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1146 BEGIN
1147
1148 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1149 p_pkg_name => G_PKG_NAME,
1150 p_init_msg_list => p_init_msg_list,
1151 l_api_version => l_api_version,
1152 p_api_version => p_api_version,
1153 p_api_type => G_API_TYPE,
1154 x_return_status => l_return_status);
1155 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1156 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1157 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1158 RAISE G_EXCEPTION_ERROR;
1159 END IF;
1160
1161 IF (p_strm_tbl.COUNT > 0) THEN
1162 i := p_strm_tbl.FIRST;
1163 LOOP
1164 l_strm_rec := p_strm_tbl(i);
1165 l_stream_type_name := l_strm_rec.strm_name;
1166 l_description := l_strm_rec.strm_desc;
1167 l_sre_date := l_strm_rec.sre_date;
1168 l_amount := l_strm_rec.amount;
1169
1170
1171 IF(l_description = 'Residual Insurance Premium') THEN
1172 l_srsv_rec.stream_type_name := 'RESIDUAL VALUE INSURANCE PREMIUM';
1173 ELSIF ( l_description = 'NODESC' OR l_description = 'TEMPVALUE') THEN
1174 l_srsv_rec.stream_type_name := l_stream_type_name;
1175 ELSE
1176 l_srsv_rec.stream_type_name := l_description;
1177 END IF;
1178
1179 --Added by kthiruva for Streams Performance
1180 --Bug 4346646 - Start of Changes
1181 IF (l_strm_rec.index_number = NULL) THEN
1182 l_strm_rec.index_number := p_index_number;
1183 END IF;
1184
1185 -- IF the index number is not returned from SuperTrump
1186 -- -100 is the default value for Index Number
1187 IF (p_index_number = -100) THEN
1188 l_srsv_rec.index_number := NULL;
1189 ELSE
1190 l_srsv_rec.index_number := l_strm_rec.index_number;
1191 END IF;
1192 --Bug 4346646 - End of Changes
1193 l_srsv_rec.activity_type := p_activity_type;
1194
1195 l_srsv_rec.sequence_number := p_se_line_number;
1196
1197 l_sre_date := correct_feb_date(l_sre_date);
1198
1199 l_srsv_rec.sre_date := TO_DATE(l_sre_date, 'YYYY-MM-DD');
1200 l_srsv_rec.amount := l_amount;
1201 l_srsv_rec.sir_id := p_sir_id;
1202
1203 l_counter := l_counter + 1;
1204 l_srsv_tbl(l_counter) := l_srsv_rec;
1205
1206 EXIT WHEN (i = p_strm_tbl.LAST);
1207 i := p_strm_tbl.NEXT(i);
1208 END LOOP;
1209 END IF;
1210
1211 okl_srs_pvt.insert_row_upg(p_srsv_tbl => l_srsv_tbl);
1212
1213 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1214 x_msg_data => x_msg_data);
1215 x_return_status := l_return_status;
1216
1217 EXCEPTION
1218 WHEN G_EXCEPTION_ERROR THEN
1219
1220 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1221 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1222 END IF;
1223
1224 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1225 p_pkg_name => G_PKG_NAME,
1226 p_exc_name => G_EXC_NAME_ERROR,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data,
1229 p_api_type => G_API_TYPE);
1230 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1231
1232 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1233 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1234 END IF;
1235
1236 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1237 p_pkg_name => G_PKG_NAME,
1238 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1239 x_msg_count => x_msg_count,
1240 x_msg_data => x_msg_data,
1241 p_api_type => G_API_TYPE);
1242 WHEN OTHERS THEN
1243
1244 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1245 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1246 END IF;
1247
1248 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1249 p_pkg_name => G_PKG_NAME,
1250 p_exc_name => G_EXC_NAME_OTHERS,
1251 x_msg_count => x_msg_count,
1252 x_msg_data => x_msg_data,
1253 p_api_type => G_API_TYPE);
1254
1255 END populate_sif_ret_strms;
1256 --Bug -End of Changes
1257
1258 ---------------------------------------------------------------------------
1259 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
1260 ---------------------------------------------------------------------------
1261
1262 PROCEDURE populate_sif_ret_strms (x_return_status OUT NOCOPY VARCHAR2,
1263 p_stream_type_name IN OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1264 p_description IN VARCHAR2 := OKC_API.G_MISS_CHAR,
1265 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
1266 p_sre_date IN VARCHAR2 := OKC_API.G_MISS_CHAR,
1267 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
1268 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
1269 ) IS
1270
1271 p_api_version NUMBER := 1.0;
1272 x_id NUMBER;
1273 p_activity_type OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR;
1274 p_se_line_number NUMBER := 1;
1275 p_init_msg_list VARCHAR2(1) := 'F';
1276
1277 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
1278 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1279 l_srsv_rec srsv_rec_type;
1280 l_api_version CONSTANT NUMBER := 1;
1281
1282 x_srsv_rec srsv_rec_type;
1283 x_msg_data VARCHAR2(400);
1284 x_msg_count NUMBER ;
1285 l_sre_date VARCHAR2(30);
1286
1287 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1288 BEGIN
1289 --dbms_output.put_line('populate_sif_ret_strms start pub');
1290 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1291 p_pkg_name => G_PKG_NAME,
1292 p_init_msg_list => p_init_msg_list,
1293 l_api_version => l_api_version,
1294 p_api_version => p_api_version,
1295 p_api_type => G_API_TYPE,
1296 x_return_status => l_return_status);
1297 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1298 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1299 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1300 RAISE G_EXCEPTION_ERROR;
1301 END IF;
1302 --dbms_output.put_line('populate_sif_ret_strms aft savepoint pub');
1303
1304 IF(p_description = 'Residual Insurance Premium')
1305 THEN
1306 l_srsv_rec.stream_type_name := 'RESIDUAL VALUE INSURANCE PREMIUM';
1307
1308 ELSIF ( p_description = 'NODESC' OR p_description = 'TEMPVALUE')
1309
1310 THEN
1311 l_srsv_rec.stream_type_name := p_stream_type_name;
1312 ELSE
1313 l_srsv_rec.stream_type_name := p_description;
1314 END IF;
1315
1316 -- IF the index number is not returned from SuperTrump
1317 -- -100 is the default value for Index Number
1318 IF (p_index_number = -100)
1319 THEN
1320 l_srsv_rec.index_number := NULL;
1321 ELSE
1322 l_srsv_rec.index_number := p_index_number;
1323 END IF;
1324 l_srsv_rec.activity_type := p_activity_type;
1325 -- l_srsv_rec.sequence_number := sequence_number;
1326 l_srsv_rec.sequence_number := p_se_line_number;
1327 -- l_srsv_rec.sre_date := p_sre_date;
1328 -- TBD
1329 -- CHECK FOR THE DATE FORMAT
1330 --dbms_output.put_line('populate_sif_ret_strms bef date conv pub '||p_sre_date);
1331
1332 -- TEMP FIX FOR 02/30 DATES
1333
1334 l_sre_date := p_sre_date;
1335 l_sre_date := correct_feb_date(l_sre_date);
1336
1337 l_srsv_rec.sre_date := TO_DATE(l_sre_date, 'YYYY-MM-DD');
1338 l_srsv_rec.amount := p_amount;
1339 l_srsv_rec.sir_id := p_sir_id;
1340
1341 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 4' );
1342 /* Call main API */
1343 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_strms (p_api_version => p_api_version,
1344 p_init_msg_list => p_init_msg_list,
1345 x_return_status => l_return_status,
1346 x_msg_count => x_msg_count,
1347 x_msg_data => x_msg_data,
1348 p_srsv_rec => l_srsv_rec,
1349 x_srsv_rec => x_srsv_rec);
1350
1351 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 5' || l_return_status);
1352 FOR i IN 1..x_msg_count
1353 LOOP
1354 fnd_msg_pub.get(p_data => x_msg_data,
1355 p_msg_index_out => x_msg_count,
1356 p_encoded => 'F',
1357 p_msg_index => fnd_msg_pub.g_next
1358 );
1359 --DBMS_OUTPUT.PUT_LINE('l_msg_text = ' || x_msg_data);
1360 END LOOP;
1361
1362
1363 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1364 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1365 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1366 RAISE G_EXCEPTION_ERROR;
1367 END IF;
1368
1369 -- Assign id returned to corresponding out parameter
1370 x_id := x_srsv_rec.id;
1371 -- Assign record returned by private api to local record
1372 l_srsv_rec := x_srsv_rec;
1373
1374
1375 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data);
1377 x_return_status := l_return_status;
1378 EXCEPTION
1379 WHEN G_EXCEPTION_ERROR THEN
1380
1381 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1382 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1383 END IF;
1384
1385 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1386 p_pkg_name => G_PKG_NAME,
1387 p_exc_name => G_EXC_NAME_ERROR,
1388 x_msg_count => x_msg_count,
1389 x_msg_data => x_msg_data,
1390 p_api_type => G_API_TYPE);
1391 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1392
1393 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1394 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1395 END IF;
1396
1397 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1398 p_pkg_name => G_PKG_NAME,
1399 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1400 x_msg_count => x_msg_count,
1401 x_msg_data => x_msg_data,
1402 p_api_type => G_API_TYPE);
1403 WHEN OTHERS THEN
1404
1405 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1406 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1407 END IF;
1408
1409 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1410 p_pkg_name => G_PKG_NAME,
1411 p_exc_name => G_EXC_NAME_OTHERS,
1412 x_msg_count => x_msg_count,
1413 x_msg_data => x_msg_data,
1414 p_api_type => G_API_TYPE);
1415 END populate_sif_ret_strms;
1416
1417 ---------------------------------------------------------------------------
1418 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
1419 ---------------------------------------------------------------------------
1420
1421 PROCEDURE populate_sif_ret_strms (p_api_version IN NUMBER := 1.0,
1422 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1423 x_return_status OUT NOCOPY VARCHAR2,
1424 x_id OUT NOCOPY NUMBER,
1425 p_stream_type_name IN OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1426 p_description IN VARCHAR2 := OKC_API.G_MISS_CHAR,
1427 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
1428 p_activity_type IN OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR,
1429 p_se_line_number IN NUMBER := OKC_API.G_MISS_NUM,
1430 p_sre_date IN VARCHAR2 := OKC_API.G_MISS_CHAR,
1431 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
1432 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
1433 ) IS
1434 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
1435 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1436 l_srsv_rec srsv_rec_type;
1437 l_api_version CONSTANT NUMBER := 1;
1438
1439 x_srsv_rec srsv_rec_type;
1440 x_msg_data VARCHAR2(400);
1441 x_msg_count NUMBER ;
1442 l_sre_date VARCHAR2(30);
1443
1444 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1445 BEGIN
1446 --dbms_output.put_line('populate_sif_ret_strms start pub');
1447 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1448 p_pkg_name => G_PKG_NAME,
1449 p_init_msg_list => p_init_msg_list,
1450 l_api_version => l_api_version,
1451 p_api_version => p_api_version,
1452 p_api_type => G_API_TYPE,
1453 x_return_status => l_return_status);
1454 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1455 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1456 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1457 RAISE G_EXCEPTION_ERROR;
1458 END IF;
1459 --dbms_output.put_line('populate_sif_ret_strms aft savepoint pub');
1460
1461 IF(p_description = 'Residual Insurance Premium')
1462 THEN
1463 l_srsv_rec.stream_type_name := 'RESIDUAL VALUE INSURANCE PREMIUM';
1464
1465 ELSIF ( p_description = 'NODESC' OR p_description = 'TEMPVALUE')
1466
1467 THEN
1468 l_srsv_rec.stream_type_name := p_stream_type_name;
1469 ELSE
1470 l_srsv_rec.stream_type_name := p_description;
1471 END IF;
1472
1473 -- IF the index number is not returned from SuperTrump
1474 -- -100 is the default value for Index Number
1475 IF (p_index_number = -100)
1476 THEN
1477 l_srsv_rec.index_number := NULL;
1478 ELSE
1479 l_srsv_rec.index_number := p_index_number;
1480 END IF;
1481 l_srsv_rec.activity_type := p_activity_type;
1482 -- l_srsv_rec.sequence_number := sequence_number;
1483 l_srsv_rec.sequence_number := p_se_line_number;
1484 -- l_srsv_rec.sre_date := p_sre_date;
1485 -- TBD
1486 -- CHECK FOR THE DATE FORMAT
1487 --dbms_output.put_line('populate_sif_ret_strms bef date conv pub '||p_sre_date);
1488
1489 -- TEMP FIX FOR 02/30 DATES
1490
1491 l_sre_date := p_sre_date;
1492 l_sre_date := correct_feb_date(l_sre_date);
1493
1494 l_srsv_rec.sre_date := TO_DATE(l_sre_date, 'YYYY-MM-DD');
1495 l_srsv_rec.amount := p_amount;
1496 l_srsv_rec.sir_id := p_sir_id;
1497
1498 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 4' );
1499 /* Call main API */
1500 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_strms (p_api_version => p_api_version,
1501 p_init_msg_list => p_init_msg_list,
1502 x_return_status => l_return_status,
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data,
1505 p_srsv_rec => l_srsv_rec,
1506 x_srsv_rec => x_srsv_rec);
1507
1508 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 5' || l_return_status);
1509 FOR i IN 1..x_msg_count
1510 LOOP
1511 fnd_msg_pub.get(p_data => x_msg_data,
1512 p_msg_index_out => x_msg_count,
1513 p_encoded => 'F',
1514 p_msg_index => fnd_msg_pub.g_next
1515 );
1516 --DBMS_OUTPUT.PUT_LINE('l_msg_text = ' || x_msg_data);
1517 END LOOP;
1518
1519
1520 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1521 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1522 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1523 RAISE G_EXCEPTION_ERROR;
1524 END IF;
1525
1526 -- Assign id returned to corresponding out parameter
1527 x_id := x_srsv_rec.id;
1528 -- Assign record returned by private api to local record
1529 l_srsv_rec := x_srsv_rec;
1530
1531
1532 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data);
1534 x_return_status := l_return_status;
1535 EXCEPTION
1536 WHEN G_EXCEPTION_ERROR THEN
1537
1538 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1539 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1540 END IF;
1541
1542 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1543 p_pkg_name => G_PKG_NAME,
1544 p_exc_name => G_EXC_NAME_ERROR,
1545 x_msg_count => x_msg_count,
1546 x_msg_data => x_msg_data,
1547 p_api_type => G_API_TYPE);
1548 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1549
1550 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1551 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1552 END IF;
1553
1554 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1555 p_pkg_name => G_PKG_NAME,
1556 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1557 x_msg_count => x_msg_count,
1558 x_msg_data => x_msg_data,
1559 p_api_type => G_API_TYPE);
1560 WHEN OTHERS THEN
1561
1562 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1563 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1564 END IF;
1565
1566 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1567 p_pkg_name => G_PKG_NAME,
1568 p_exc_name => G_EXC_NAME_OTHERS,
1569 x_msg_count => x_msg_count,
1570 x_msg_data => x_msg_data,
1571 p_api_type => G_API_TYPE);
1572 END populate_sif_ret_strms;
1573
1574 ---------------------------------------------------------------------------
1575 -- PROCEDURE populate_sif_ret_strms for: OKL_SIF_RET_STREAMS_V
1576 ---------------------------------------------------------------------------
1577
1578 PROCEDURE populate_sif_ret_strms (p_api_version IN NUMBER := 1.0,
1579 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1580 x_return_status OUT NOCOPY VARCHAR2,
1581 x_id OUT NOCOPY NUMBER,
1582 p_stream_type_name IN OKL_SIF_RET_STRMS.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1583 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
1584 p_activity_type IN OKL_SIF_RET_STRMS.ACTIVITY_TYPE%TYPE := OKC_API.G_MISS_CHAR,
1585 p_se_line_number IN NUMBER := OKC_API.G_MISS_NUM,
1586 p_sre_date IN OKL_SIF_RET_STRMS.SRE_DATE%TYPE := OKC_API.G_MISS_DATE,
1587 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
1588 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM
1589 ) IS
1590 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_strms';
1591 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1592 l_srsv_rec srsv_rec_type;
1593 l_api_version CONSTANT NUMBER := 1;
1594
1595 x_srsv_rec srsv_rec_type;
1596 x_msg_data VARCHAR2(400);
1597 x_msg_count NUMBER ;
1598
1599 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1600
1601 BEGIN
1602 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure ');
1603 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1604 p_pkg_name => G_PKG_NAME,
1605 p_init_msg_list => p_init_msg_list,
1606 l_api_version => l_api_version,
1607 p_api_version => p_api_version,
1608 p_api_type => G_API_TYPE,
1609 x_return_status => l_return_status);
1610 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1611 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1612 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1613 RAISE G_EXCEPTION_ERROR;
1614 END IF;
1615 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 2');
1616 l_srsv_rec.stream_type_name := p_stream_type_name;
1617 l_srsv_rec.index_number := p_index_number;
1618 l_srsv_rec.activity_type := p_activity_type;
1619 l_srsv_rec.sequence_number := p_se_line_number;
1620 l_srsv_rec.sre_date := p_sre_date;
1621
1622 l_srsv_rec.amount := p_amount;
1623 l_srsv_rec.sir_id := p_sir_id;
1624 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 3');
1625
1626
1627 /* Call main API */
1628 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 4');
1629 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_strms (p_api_version => p_api_version,
1630 p_init_msg_list => p_init_msg_list,
1631 x_return_status => l_return_status,
1632 x_msg_count => x_msg_count,
1633 x_msg_data => x_msg_data,
1634 p_srsv_rec => l_srsv_rec,
1635 x_srsv_rec => x_srsv_rec);
1636 --DBMS_OUTPUT.PUT_LINE('INSIDE PUB Procedure 5' || l_return_status);
1637 FOR i IN 1..x_msg_count
1638 LOOP
1639 fnd_msg_pub.get(p_data => x_msg_data,
1640 p_msg_index_out => x_msg_count,
1641 p_encoded => 'F',
1642 p_msg_index => fnd_msg_pub.g_next
1643 );
1644 --DBMS_OUTPUT.PUT_LINE('l_msg_text = ' || x_msg_data);
1645 END LOOP;
1646 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1647 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1648 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1649 RAISE G_EXCEPTION_ERROR;
1650 END IF;
1651
1652 -- Assign id returned to corresponding out parameter
1653 x_id := x_srsv_rec.id;
1654 -- Assign record returned by private api to local record
1655 l_srsv_rec := x_srsv_rec;
1656
1657
1658 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1659 x_msg_data => x_msg_data);
1660 x_return_status := l_return_status;
1661 EXCEPTION
1662 WHEN G_EXCEPTION_ERROR THEN
1663 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1664 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1665 END IF;
1666
1667 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1668 p_pkg_name => G_PKG_NAME,
1669 p_exc_name => G_EXC_NAME_ERROR,
1670 x_msg_count => x_msg_count,
1671 x_msg_data => x_msg_data,
1672 p_api_type => G_API_TYPE);
1673 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1674 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1675 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1676 END IF;
1677
1678 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1679 p_pkg_name => G_PKG_NAME,
1680 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1681 x_msg_count => x_msg_count,
1682 x_msg_data => x_msg_data,
1683 p_api_type => G_API_TYPE);
1684 WHEN OTHERS THEN
1685 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1686 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1687 END IF;
1688
1689 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1690 p_pkg_name => G_PKG_NAME,
1691 p_exc_name => G_EXC_NAME_OTHERS,
1692 x_msg_count => x_msg_count,
1693 x_msg_data => x_msg_data,
1694 p_api_type => G_API_TYPE);
1695 END populate_sif_ret_strms;
1696
1697
1698
1699 ---------------------------------------------------------------------------
1700 -- PROCEDURE populate_sif_ret_errors for: OKL_SIF_RET_ERRORS_V
1701 ---------------------------------------------------------------------------
1702
1703 --Added by BKATRAGA on 03/03/2005
1704 --This procedure has been modified to accept a table of records instead of a
1705 --single record.
1706 --Bug - Start of Changes
1707 PROCEDURE populate_sif_ret_errors (
1708 x_return_status OUT NOCOPY VARCHAR2,
1709 x_id OUT NOCOPY NUMBER,
1710 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
1711 p_strm_excp_tbl IN strm_excp_tbl_type,
1712 p_tag_attribute_name IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1713 p_tag_attribute_value IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_VALUE%TYPE := OKC_API.G_MISS_CHAR,
1714 p_description IN OKL_SIF_RET_ERRORS.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
1715 ) IS
1716
1717 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_errors';
1718 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1719 l_api_version CONSTANT NUMBER := 1;
1720 l_init_msg_list VARCHAR2(1) := 'F';
1721 x_msg_data VARCHAR2(400);
1722 x_msg_count NUMBER ;
1723 l_strm_excp_rec strm_excp_rec_type;
1724 i NUMBER := 0;
1725 l_error_code OKL_SIF_RET_ERRORS.ERROR_CODE%TYPE := OKC_API.G_MISS_CHAR;
1726 l_error_message OKL_SIF_RET_ERRORS.ERROR_MESSAGE%TYPE := OKC_API.G_MISS_CHAR;
1727 l_description OKL_SIF_RET_ERRORS.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR;
1728 l_tag_name OKL_SIF_RET_ERRORS.TAG_NAME%TYPE := OKC_API.G_MISS_CHAR;
1729
1730 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1731
1732 BEGIN
1733 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1734 p_pkg_name => G_PKG_NAME,
1735 p_init_msg_list => l_init_msg_list,
1736 l_api_version => l_api_version,
1737 p_api_version => l_api_version,
1738 p_api_type => G_API_TYPE,
1739 x_return_status => l_return_status);
1740 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1741 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1742 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1743 RAISE G_EXCEPTION_ERROR;
1744 END IF;
1745
1746
1747 IF (p_strm_excp_tbl.COUNT > 0) THEN
1748 i := p_strm_excp_tbl.FIRST;
1749 LOOP
1750 l_strm_excp_rec := p_strm_excp_tbl(i);
1751 l_error_code := l_strm_excp_rec.error_code;
1752 l_error_message := l_strm_excp_rec.error_message;
1753 --l_description := l_strm_excp_rec.description;
1754 l_tag_name := l_strm_excp_rec.tag_name;
1755
1756 populate_sif_ret_errors (l_return_status,
1757 x_id => x_id,
1758 p_sir_id => p_sir_id,
1759 p_error_code => l_error_code,
1760 p_error_message => l_error_message,
1761 p_tag_name => l_tag_name,
1762 p_tag_attribute_name => p_tag_attribute_name,
1763 p_tag_attribute_value => p_tag_attribute_value,
1764 p_description => p_description
1765 --p_description => l_description
1766 );
1767
1768 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1769 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1770 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1771 RAISE G_EXCEPTION_ERROR;
1772 END IF;
1773
1774 EXIT WHEN (i = p_strm_excp_tbl.LAST);
1775 i := p_strm_excp_tbl.NEXT(i);
1776 END LOOP;
1777 END IF;
1778
1779
1780 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1781 x_msg_data => x_msg_data);
1782 x_return_status := l_return_status;
1783 EXCEPTION
1784 WHEN G_EXCEPTION_ERROR THEN
1785
1786 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1787 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1788 END IF;
1789
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_ERROR,
1793 x_msg_count => x_msg_count,
1794 x_msg_data => x_msg_data,
1795 p_api_type => G_API_TYPE);
1796 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1797
1798 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1799 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1800 END IF;
1801
1802 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1803 p_pkg_name => G_PKG_NAME,
1804 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1805 x_msg_count => x_msg_count,
1806 x_msg_data => x_msg_data,
1807 p_api_type => G_API_TYPE);
1808 WHEN OTHERS THEN
1809
1810 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1811 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1812 END IF;
1813
1814 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1815 p_pkg_name => G_PKG_NAME,
1816 p_exc_name => G_EXC_NAME_OTHERS,
1817 x_msg_count => x_msg_count,
1818 x_msg_data => x_msg_data,
1819 p_api_type => G_API_TYPE);
1820
1821 END populate_sif_ret_errors;
1822 --Bug - End of Changes
1823
1824 ---------------------------------------------------------------------------
1825 -- PROCEDURE populate_sif_ret_errors for: OKL_SIF_RET_ERRORS_V
1826 ---------------------------------------------------------------------------
1827
1828 PROCEDURE populate_sif_ret_errors (p_api_version IN NUMBER := 1.0,
1829 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1830 x_return_status OUT NOCOPY VARCHAR2,
1831 x_id OUT NOCOPY NUMBER,
1832 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
1833 p_error_code IN OKL_SIF_RET_ERRORS.ERROR_CODE%TYPE := OKC_API.G_MISS_CHAR,
1834 p_error_message IN OKL_SIF_RET_ERRORS.ERROR_MESSAGE%TYPE := OKC_API.G_MISS_CHAR,
1835 p_tag_name IN OKL_SIF_RET_ERRORS.TAG_NAME%TYPE := OKC_API.G_MISS_CHAR,
1836 p_tag_attribute_name IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1837 p_tag_attribute_value IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_VALUE%TYPE := OKC_API.G_MISS_CHAR,
1838 p_description IN OKL_SIF_RET_ERRORS.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
1839 ) IS
1840 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_errors';
1841 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1842 l_srmv_rec srmv_rec_type;
1843 l_api_version CONSTANT NUMBER := 1;
1844 x_srmv_rec srmv_rec_type;
1845 x_msg_data VARCHAR2(400);
1846 x_msg_count NUMBER ;
1847
1848 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1849
1850 BEGIN
1851 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1852 p_pkg_name => G_PKG_NAME,
1853 p_init_msg_list => p_init_msg_list,
1854 l_api_version => l_api_version,
1855 p_api_version => p_api_version,
1856 p_api_type => G_API_TYPE,
1857 x_return_status => l_return_status);
1858 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1859 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1860 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1861 RAISE G_EXCEPTION_ERROR;
1862 END IF;
1863
1864 l_srmv_rec.sir_id := p_sir_id;
1865 l_srmv_rec.error_code := p_error_code;
1866 l_srmv_rec.error_message := p_error_message;
1867 --l_srmv_rec.description := p_description;
1868 l_srmv_rec.tag_name := p_tag_name;
1869 IF(p_tag_attribute_name = 'TEMPVALUE')
1870 THEN
1871 l_srmv_rec.tag_attribute_name := NULL;
1872 ELSE
1873 l_srmv_rec.tag_attribute_name := p_tag_attribute_name;
1874 END IF;
1875 IF(p_tag_attribute_value = 'TEMPVALUE')
1876 THEN
1877 l_srmv_rec.tag_attribute_value := NULL;
1878 ELSE
1879 l_srmv_rec.tag_attribute_value := p_tag_attribute_value;
1880 END IF;
1881 IF(p_description = 'TEMPVALUE')
1882 THEN
1883 l_srmv_rec.description := NULL;
1884 ELSE
1885 l_srmv_rec.description := p_description;
1886 END IF;
1887
1888
1889
1890 /* Call main API */
1891 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_errors(p_api_version => p_api_version,
1892 p_init_msg_list => p_init_msg_list,
1893 x_return_status => l_return_status,
1894 x_msg_count => x_msg_count,
1895 x_msg_data => x_msg_data,
1896 p_srmv_rec => l_srmv_rec,
1897 x_srmv_rec => x_srmv_rec);
1898 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1899 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1900 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1901 RAISE G_EXCEPTION_ERROR;
1902 END IF;
1903
1904 -- Assign id returned to corresponding out parameter
1905 x_id := x_srmv_rec.id;
1906 -- Assign record returned by private api to local record
1907 l_srmv_rec := x_srmv_rec;
1908
1909
1910 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1911 x_msg_data => x_msg_data);
1912 x_return_status := l_return_status;
1913 EXCEPTION
1914 WHEN G_EXCEPTION_ERROR THEN
1915
1916 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1917 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1918 END IF;
1919
1920 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1921 p_pkg_name => G_PKG_NAME,
1922 p_exc_name => G_EXC_NAME_ERROR,
1923 x_msg_count => x_msg_count,
1924 x_msg_data => x_msg_data,
1925 p_api_type => G_API_TYPE);
1926 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1927
1928 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1929 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1930 END IF;
1931
1932 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1933 p_pkg_name => G_PKG_NAME,
1934 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data,
1937 p_api_type => G_API_TYPE);
1938 WHEN OTHERS THEN
1939
1940 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
1941 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
1942 END IF;
1943
1944 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1945 p_pkg_name => G_PKG_NAME,
1946 p_exc_name => G_EXC_NAME_OTHERS,
1947 x_msg_count => x_msg_count,
1948 x_msg_data => x_msg_data,
1949 p_api_type => G_API_TYPE);
1950 END populate_sif_ret_errors;
1951
1952 -- mvasudev , 04/24/2002
1953 ---------------------------------------------------------------------------
1954 -- PROCEDURE populate_sif_ret_errors for: OKL_SIF_RET_ERRORS_V
1955 ---------------------------------------------------------------------------
1956
1957 PROCEDURE populate_sif_ret_errors (x_return_status OUT NOCOPY VARCHAR2,
1958 x_id OUT NOCOPY NUMBER,
1959 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
1960 p_error_code IN OKL_SIF_RET_ERRORS.ERROR_CODE%TYPE := OKC_API.G_MISS_CHAR,
1961 p_error_message IN OKL_SIF_RET_ERRORS.ERROR_MESSAGE%TYPE := OKC_API.G_MISS_CHAR,
1962 p_tag_name IN OKL_SIF_RET_ERRORS.TAG_NAME%TYPE := OKC_API.G_MISS_CHAR,
1963 p_tag_attribute_name IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_NAME%TYPE := OKC_API.G_MISS_CHAR,
1964 p_tag_attribute_value IN OKL_SIF_RET_ERRORS.TAG_ATTRIBUTE_VALUE%TYPE := OKC_API.G_MISS_CHAR,
1965 p_description IN OKL_SIF_RET_ERRORS.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR
1966 ) IS
1967 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_errors';
1968 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1969 l_srmv_rec srmv_rec_type;
1970 l_api_version CONSTANT NUMBER := 1;
1971 x_srmv_rec srmv_rec_type;
1972 x_msg_data VARCHAR2(400);
1973 x_msg_count NUMBER ;
1974
1975 l_init_msg_list VARCHAR2(1) := 'F';
1976
1977 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
1978
1979 BEGIN
1980 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1981 p_pkg_name => G_PKG_NAME,
1982 p_init_msg_list => l_init_msg_list,
1983 l_api_version => l_api_version,
1984 p_api_version => l_api_version,
1985 p_api_type => G_API_TYPE,
1986 x_return_status => l_return_status);
1987 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1988 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1989 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1990 RAISE G_EXCEPTION_ERROR;
1991 END IF;
1992
1993 l_srmv_rec.sir_id := p_sir_id;
1994 l_srmv_rec.error_code := p_error_code;
1995 l_srmv_rec.error_message := p_error_message;
1996 l_srmv_rec.tag_name := p_tag_name;
1997 IF(p_tag_attribute_name = 'TEMPVALUE')
1998 THEN
1999 l_srmv_rec.tag_attribute_name := NULL;
2000 ELSE
2001 l_srmv_rec.tag_attribute_name := p_tag_attribute_name;
2002 END IF;
2003 IF(p_tag_attribute_value = 'TEMPVALUE')
2004 THEN
2005 l_srmv_rec.tag_attribute_value := NULL;
2006 ELSE
2007 l_srmv_rec.tag_attribute_value := p_tag_attribute_value;
2008 END IF;
2009 IF(p_description = 'TEMPVALUE')
2010 THEN
2011 l_srmv_rec.description := NULL;
2012 ELSE
2013 l_srmv_rec.description := p_description;
2014 END IF;
2015
2016
2017
2018 /* Call main API */
2019 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_errors(p_api_version => l_api_version,
2020 p_init_msg_list => l_init_msg_list,
2021 x_return_status => l_return_status,
2022 x_msg_count => x_msg_count,
2023 x_msg_data => x_msg_data,
2024 p_srmv_rec => l_srmv_rec,
2025 x_srmv_rec => x_srmv_rec);
2026 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2027 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2028 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2029 RAISE G_EXCEPTION_ERROR;
2030 END IF;
2031
2032 -- Assign id returned to corresponding out parameter
2033 x_id := x_srmv_rec.id;
2034 -- Assign record returned by private api to local record
2035 l_srmv_rec := x_srmv_rec;
2036
2037
2038 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2039 x_msg_data => x_msg_data);
2040 x_return_status := l_return_status;
2041 EXCEPTION
2042 WHEN G_EXCEPTION_ERROR THEN
2043
2044 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2045 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
2046 END IF;
2047
2048 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2049 p_pkg_name => G_PKG_NAME,
2050 p_exc_name => G_EXC_NAME_ERROR,
2051 x_msg_count => x_msg_count,
2052 x_msg_data => x_msg_data,
2053 p_api_type => G_API_TYPE);
2054 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2055
2056 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2057 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
2058 END IF;
2059
2060 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2061 p_pkg_name => G_PKG_NAME,
2062 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2063 x_msg_count => x_msg_count,
2064 x_msg_data => x_msg_data,
2065 p_api_type => G_API_TYPE);
2066 WHEN OTHERS THEN
2067
2068 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2069 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
2070 END IF;
2071
2072 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2073 p_pkg_name => G_PKG_NAME,
2074 p_exc_name => G_EXC_NAME_OTHERS,
2075 x_msg_count => x_msg_count,
2076 x_msg_data => x_msg_data,
2077 p_api_type => G_API_TYPE);
2078 END populate_sif_ret_errors;
2079 -- mvasudev , 04/24/2002 end
2080
2081 ----------------------------------------------------------------------------------------
2082 -- PROCEDURE populate_insured_residual
2083 ----------------------------------------------------------------------------------------
2084 PROCEDURE populate_insured_residual (
2085 p_api_version IN NUMBER,
2086 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2087 p_transaction_number IN NUMBER,
2088 p_amount IN NUMBER,
2089 p_sir_id IN NUMBER,
2090 x_return_status OUT NOCOPY VARCHAR2
2091 --x_msg_count OUT NOCOPY NUMBER,
2092 --x_msg_data OUT NOCOPY VARCHAR2
2093 ) IS
2094
2095 l_khr_id NUMBER;
2096 l_start_date DATE;
2097 l_stream_type_name VARCHAR2(30);
2098 l_api_name CONSTANT VARCHAR2(30) := 'populate_insured_residual';
2099 l_api_version CONSTANT NUMBER := 1;
2100 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2101 --l_srsv_rec srsv_rec_type;
2102 --lx_srsv_rec srsv_rec_type;
2103 lx_msg_count NUMBER;
2104 lx_msg_data VARCHAR2(400);
2105 lx_id NUMBER;
2106
2107 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
2108
2109 CURSOR khr_id_csr (trx_number NUMBER) IS
2110 SELECT khr_id
2111 FROM okl_stream_interfaces
2112 WHERE transaction_number = trx_number;
2113
2114 CURSOR start_date_csr (khr_id NUMBER) IS
2115 SELECT start_date
2116 FROM okc_k_headers_b
2117 WHERE id = khr_id;
2118
2119 BEGIN
2120
2121 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2122 p_pkg_name => G_PKG_NAME,
2123 p_init_msg_list => p_init_msg_list,
2124 l_api_version => l_api_version,
2125 p_api_version => p_api_version,
2126 p_api_type => G_API_TYPE,
2127 x_return_status => l_return_status);
2128 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2129 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2130 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2131 RAISE G_EXCEPTION_ERROR;
2132 END IF;
2133
2134 FOR khr_id_info IN khr_id_csr(p_transaction_number)
2135 LOOP
2136 l_khr_id := khr_id_info.khr_id;
2137 END LOOP;
2138
2139 FOR start_date_info IN start_date_csr(l_khr_id)
2140 LOOP
2141 l_start_date := start_date_info.start_date;
2142 END LOOP;
2143
2144 l_stream_type_name := 'GUARANTEED RESIDUAL INSURED';
2145
2146 --l_srsv_rec.stream_type_name := l_stream_type_name;
2147 --l_srsv_rec.index_number := to_number(NULL);
2148 --l_srsv_rec.activity_type := NULL;
2149 --l_srsv_rec.sre_date := l_start_date;
2150 --l_srsv_rec.amount := p_amount;
2151 --l_srsv_rec.sir_id := p_sir_id;
2152
2153
2154 /* Call main API */
2155 --DBMS_OUTPUT.PUT_LINE('khr_id ' || l_khr_id);
2156 --DBMS_OUTPUT.PUT_LINE('Before calling Procedure ' || l_start_date);
2157 Okl_POPULATE_PRCENG_RST_PUB.populate_sif_ret_strms (p_api_version => p_api_version,
2158 p_init_msg_list => p_init_msg_list,
2159 x_return_status => l_return_status,
2160 x_id => lx_id,
2161 p_stream_type_name => l_stream_type_name,
2162 p_description => l_stream_type_name,
2163 p_index_number => TO_NUMBER(NULL),
2164 -- p_index_number => 1,
2165 -- p_activity_type => NULL,
2166 p_activity_type => 'TYPE',
2167 -- p_se_line_number => to_number(NULL),
2168 p_se_line_number => 1,
2169 p_sre_date => TO_CHAR(l_start_date, 'YYYY-MM-DD'),
2170 p_amount => p_amount,
2171 p_sir_id => p_sir_id);
2172
2173
2174 --DBMS_OUTPUT.PUT_LINE('After calling Procedure ' || l_return_status);
2175
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
2182
2183 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
2184 x_msg_data => lx_msg_data);
2185
2186 x_return_status := l_return_status;
2187 EXCEPTION
2188 WHEN G_EXCEPTION_ERROR THEN
2189
2190 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2191 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||lx_msg_data);
2192 END IF;
2193
2194 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2195 p_pkg_name => G_PKG_NAME,
2196 p_exc_name => G_EXC_NAME_ERROR,
2197 x_msg_count => lx_msg_count,
2198 x_msg_data => lx_msg_data,
2199 p_api_type => G_API_TYPE);
2200 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2201
2202 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2203 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||lx_msg_data);
2204 END IF;
2205
2206 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2207 p_pkg_name => G_PKG_NAME,
2208 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2209 x_msg_count => lx_msg_count,
2210 x_msg_data => lx_msg_data,
2211 p_api_type => G_API_TYPE);
2212 WHEN OTHERS THEN
2213
2214 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2215 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||lx_msg_data);
2216 END IF;
2217
2218 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2219 p_pkg_name => G_PKG_NAME,
2220 p_exc_name => G_EXC_NAME_OTHERS,
2221 x_msg_count => lx_msg_count,
2222 x_msg_data => lx_msg_data,
2223 p_api_type => G_API_TYPE);
2224 END populate_insured_residual;
2225
2226 -- mvasudev, 04/24/2002
2227 ----------------------------------------------------------------------------------------
2228 -- PROCEDURE populate_insured_residual
2229 ----------------------------------------------------------------------------------------
2230 PROCEDURE populate_insured_residual (
2231 p_transaction_number IN NUMBER,
2232 p_amount IN NUMBER,
2233 p_sir_id IN NUMBER,
2234 x_return_status OUT NOCOPY VARCHAR2
2235 ) IS
2236
2237 l_khr_id NUMBER;
2238 l_start_date DATE;
2239 l_stream_type_name VARCHAR2(30);
2240 l_api_name CONSTANT VARCHAR2(30) := 'populate_insured_residual';
2241 l_api_version CONSTANT NUMBER := 1;
2242 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2243 --l_srsv_rec srsv_rec_type;
2244 --lx_srsv_rec srsv_rec_type;
2245 lx_msg_count NUMBER;
2246 lx_msg_data VARCHAR2(400);
2247 lx_id NUMBER;
2248
2249 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
2250
2251 CURSOR khr_id_csr (trx_number NUMBER) IS
2252 SELECT khr_id
2253 FROM okl_stream_interfaces
2254 WHERE transaction_number = trx_number;
2255
2256 CURSOR start_date_csr (khr_id NUMBER) IS
2257 SELECT start_date
2258 FROM okc_k_headers_b
2259 WHERE id = khr_id;
2260
2261 l_init_msg_list VARCHAR2(1) := 'F';
2262
2263
2264 BEGIN
2265
2266 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2267 p_pkg_name => G_PKG_NAME,
2268 p_init_msg_list => l_init_msg_list,
2269 l_api_version => l_api_version,
2270 p_api_version => l_api_version,
2271 p_api_type => G_API_TYPE,
2272 x_return_status => l_return_status);
2273 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2274 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2275 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2276 RAISE G_EXCEPTION_ERROR;
2277 END IF;
2278
2279 FOR khr_id_info IN khr_id_csr(p_transaction_number)
2280 LOOP
2281 l_khr_id := khr_id_info.khr_id;
2282 END LOOP;
2283
2284 FOR start_date_info IN start_date_csr(l_khr_id)
2285 LOOP
2286 l_start_date := start_date_info.start_date;
2287 END LOOP;
2288
2289 l_stream_type_name := 'GUARANTEED RESIDUAL INSURED';
2290
2291 --l_srsv_rec.stream_type_name := l_stream_type_name;
2292 --l_srsv_rec.index_number := to_number(NULL);
2293 --l_srsv_rec.activity_type := NULL;
2294 --l_srsv_rec.sre_date := l_start_date;
2295 --l_srsv_rec.amount := p_amount;
2296 --l_srsv_rec.sir_id := p_sir_id;
2297
2298
2299 /* Call main API */
2300 --DBMS_OUTPUT.PUT_LINE('khr_id ' || l_khr_id);
2301 --DBMS_OUTPUT.PUT_LINE('Before calling Procedure ' || l_start_date);
2302 Okl_POPULATE_PRCENG_RST_PUB.populate_sif_ret_strms (p_api_version => l_api_version,
2303 p_init_msg_list => l_init_msg_list,
2304 x_return_status => l_return_status,
2305 x_id => lx_id,
2306 p_stream_type_name => l_stream_type_name,
2307 p_description => l_stream_type_name,
2308 p_index_number => TO_NUMBER(NULL),
2309 -- p_index_number => 1,
2310 -- p_activity_type => NULL,
2311 p_activity_type => 'TYPE',
2312 -- p_se_line_number => to_number(NULL),
2313 p_se_line_number => 1,
2314 p_sre_date => TO_CHAR(l_start_date, 'YYYY-MM-DD'),
2315 p_amount => p_amount,
2316 p_sir_id => p_sir_id);
2317
2318
2319 --DBMS_OUTPUT.PUT_LINE('After calling Procedure ' || l_return_status);
2320
2321 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2322 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2323 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2324 RAISE G_EXCEPTION_ERROR;
2325 END IF;
2326
2327
2328 OKL_API.END_ACTIVITY(x_msg_count => lx_msg_count,
2329 x_msg_data => lx_msg_data);
2330
2331 x_return_status := l_return_status;
2332 EXCEPTION
2333 WHEN G_EXCEPTION_ERROR THEN
2334
2335 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2336 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||lx_msg_data);
2337 END IF;
2338
2339 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2340 p_pkg_name => G_PKG_NAME,
2341 p_exc_name => G_EXC_NAME_ERROR,
2342 x_msg_count => lx_msg_count,
2343 x_msg_data => lx_msg_data,
2344 p_api_type => G_API_TYPE);
2345 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2346
2347 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2348 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||lx_msg_data);
2349 END IF;
2350
2351 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2352 p_pkg_name => G_PKG_NAME,
2353 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2354 x_msg_count => lx_msg_count,
2355 x_msg_data => lx_msg_data,
2356 p_api_type => G_API_TYPE);
2357 WHEN OTHERS THEN
2358
2359 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2360 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,p_transaction_number ||': '||lx_msg_data);
2361 END IF;
2362
2363 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2364 p_pkg_name => G_PKG_NAME,
2365 p_exc_name => G_EXC_NAME_OTHERS,
2366 x_msg_count => lx_msg_count,
2367 x_msg_data => lx_msg_data,
2368 p_api_type => G_API_TYPE);
2369 END populate_insured_residual;
2370 -- mvasudev, 04/24/2002 end
2371
2372 /*
2373 PROCEDURE update_status (
2374 p_api_version IN NUMBER,
2375 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2376 p_transaction_number IN NUMBER,
2377 p_sis_code IN VARCHAR2,
2378 x_return_status OUT NOCOPY VARCHAR2
2379 ) IS
2380 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
2381 CURSOR sif_data_csr (trx_number IN NUMBER) IS
2382 SELECT
2383 ID,ORP_CODE
2384 FROM Okl_Stream_Interfaces
2385 WHERE okl_stream_interfaces.transaction_number = trx_number;
2386
2387 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2388 lp_sifv_rec sifv_rec_type;
2389 lx_sifv_rec sifv_rec_type;
2390 lx_msg_count NUMBER;
2391 lx_msg_data VARCHAR2(100);
2392
2393 BEGIN
2394
2395 -- update the status in the In bound Interface Tables
2396 FOR sif_data in sif_data_csr(p_transaction_number)
2397 LOOP
2398 lp_sifv_rec.id := sif_data.id;
2399 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
2400 END LOOP;
2401 lp_sifv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2402 lp_sifv_rec.sis_code := p_sis_code;
2403
2404 OKL_POPULATE_PRCENG_RESULT_PVT.update_status (p_api_version => p_api_version,
2405 p_init_msg_list => p_init_msg_list,
2406 p_sifv_rec => lp_sifv_rec,
2407 x_sifv_rec => lx_sifv_rec,
2408 x_msg_count => lx_msg_count,
2409 x_msg_data => lx_msg_data,
2410 x_return_status => l_return_status);
2411
2412 IF l_return_status = G_RET_STS_ERROR THEN
2413 RAISE G_EXCEPTION_ERROR;
2414 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2415 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2416 END IF;
2417
2418 x_return_status := l_return_status;
2419 EXCEPTION
2420 WHEN G_EXCEPTION_ERROR THEN
2421 x_return_status := G_RET_STS_ERROR;
2422 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2423 x_return_status := G_RET_STS_UNEXP_ERROR;
2424 WHEN OTHERS THEN
2425 x_return_status := G_RET_STS_UNEXP_ERROR;
2426
2427 END update_status;
2428 */
2429 PROCEDURE update_status (
2430 p_api_version IN NUMBER,
2431 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2432 p_transaction_number IN NUMBER,
2433 p_sis_code IN VARCHAR2, -- outbound status
2434 p_srt_code IN VARCHAR2, -- inbound status
2435 p_log_file_name IN VARCHAR2,
2436 x_return_status OUT NOCOPY VARCHAR2
2437 ) IS
2438 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
2439 CURSOR sif_data_csr (trx_number IN NUMBER) IS
2440 SELECT
2441 ID,ORP_CODE
2442 FROM Okl_Stream_Interfaces
2443 WHERE okl_stream_interfaces.transaction_number = trx_number;
2444
2445 CURSOR sir_data_csr (trx_number IN NUMBER) IS
2446 SELECT
2447 ID
2448 FROM Okl_Sif_Rets
2449 WHERE okl_sif_rets.transaction_number = trx_number;
2450
2451 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2452 lp_sifv_rec sifv_rec_type;
2453 lx_sifv_rec sifv_rec_type;
2454 lp_sirv_rec sirv_rec_type;
2455 lx_sirv_rec sirv_rec_type;
2456 lx_msg_count NUMBER;
2457 lx_msg_data VARCHAR2(100);
2458
2459 BEGIN
2460
2461 -- update the status in the In bound Interface Tables
2462 FOR sif_data IN sif_data_csr(p_transaction_number)
2463 LOOP
2464 lp_sifv_rec.id := sif_data.id;
2465 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
2466 END LOOP;
2467 lp_sifv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2468 lp_sifv_rec.sis_code := p_sis_code;
2469 IF(p_log_file_name <> 'TEMPVALUE')
2470 THEN
2471 lp_sifv_rec.log_file := p_log_file_name;
2472 END IF;
2473
2474
2475 Okl_Populate_Prceng_Result_Pvt.update_outbound_status (p_api_version => p_api_version,
2476 p_init_msg_list => p_init_msg_list,
2477 p_sifv_rec => lp_sifv_rec,
2478 x_sifv_rec => lx_sifv_rec,
2479 x_msg_count => lx_msg_count,
2480 x_msg_data => lx_msg_data,
2481 x_return_status => l_return_status);
2482
2483 IF l_return_status = G_RET_STS_ERROR THEN
2484 RAISE G_EXCEPTION_ERROR;
2485 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2486 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2487 END IF;
2488
2489 FOR sir_data IN sir_data_csr(p_transaction_number)
2490 LOOP
2491 lp_sirv_rec.id := sir_data.id;
2492 lp_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2493 lp_sirv_rec.srt_code := p_srt_code;
2494
2495 Okl_Populate_Prceng_Result_Pvt.update_sif_rets (p_api_version => p_api_version,
2496 p_init_msg_list => p_init_msg_list,
2497 p_sirv_rec => lp_sirv_rec,
2498 x_sirv_rec => lx_sirv_rec,
2499 x_msg_count => lx_msg_count,
2500 x_msg_data => lx_msg_data,
2501 x_return_status => l_return_status);
2502
2503 IF l_return_status = G_RET_STS_ERROR THEN
2504 RAISE G_EXCEPTION_ERROR;
2505 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2506 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2507 END IF;
2508
2509 END LOOP;
2510
2511 x_return_status := l_return_status;
2512 EXCEPTION
2513 WHEN G_EXCEPTION_ERROR THEN
2514 x_return_status := G_RET_STS_ERROR;
2515 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2516 x_return_status := G_RET_STS_UNEXP_ERROR;
2517 WHEN OTHERS THEN
2518 x_return_status := G_RET_STS_UNEXP_ERROR;
2519
2520 END update_status;
2521
2522 -- mvasudev , 04/24/2002
2523 PROCEDURE update_status (
2524 p_transaction_number IN NUMBER,
2525 p_sis_code IN VARCHAR2, -- outbound status
2526 p_srt_code IN VARCHAR2, -- inbound status
2527 p_log_file_name IN VARCHAR2,
2528 x_return_status OUT NOCOPY VARCHAR2
2529 ) IS
2530 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
2531 CURSOR sif_data_csr (trx_number IN NUMBER) IS
2532 SELECT
2533 ID,ORP_CODE
2534 FROM Okl_Stream_Interfaces
2535 WHERE okl_stream_interfaces.transaction_number = trx_number;
2536
2537 CURSOR sir_data_csr (trx_number IN NUMBER) IS
2538 SELECT
2539 ID
2540 FROM Okl_Sif_Rets
2541 WHERE okl_sif_rets.transaction_number = trx_number;
2542
2543 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2544 lp_sifv_rec sifv_rec_type;
2545 lx_sifv_rec sifv_rec_type;
2546 lp_sirv_rec sirv_rec_type;
2547 lx_sirv_rec sirv_rec_type;
2548 lx_msg_count NUMBER;
2549 lx_msg_data VARCHAR2(100);
2550
2551 l_init_msg_list VARCHAR2(1) := 'F';
2552 l_api_version CONSTANT NUMBER := 1;
2553
2554 BEGIN
2555
2556 -- update the status in the In bound Interface Tables
2557 FOR sif_data IN sif_data_csr(p_transaction_number)
2558 LOOP
2559 lp_sifv_rec.id := sif_data.id;
2560 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
2561 END LOOP;
2562 lp_sifv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2563 lp_sifv_rec.sis_code := p_sis_code;
2564 IF(p_log_file_name <> 'TEMPVALUE')
2565 THEN
2566 lp_sifv_rec.log_file := p_log_file_name;
2567 END IF;
2568
2569
2570 Okl_Populate_Prceng_Result_Pvt.update_outbound_status (p_api_version => l_api_version,
2571 p_init_msg_list => l_init_msg_list,
2572 p_sifv_rec => lp_sifv_rec,
2573 x_sifv_rec => lx_sifv_rec,
2574 x_msg_count => lx_msg_count,
2575 x_msg_data => lx_msg_data,
2576 x_return_status => l_return_status);
2577
2578 IF l_return_status = G_RET_STS_ERROR THEN
2579 RAISE G_EXCEPTION_ERROR;
2580 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2581 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2582 END IF;
2583
2584 FOR sir_data IN sir_data_csr(p_transaction_number)
2585 LOOP
2586 lp_sirv_rec.id := sir_data.id;
2587 lp_sirv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2588 lp_sirv_rec.srt_code := p_srt_code;
2589
2590 Okl_Populate_Prceng_Result_Pvt.update_sif_rets (p_api_version => l_api_version,
2591 p_init_msg_list => l_init_msg_list,
2592 p_sirv_rec => lp_sirv_rec,
2593 x_sirv_rec => lx_sirv_rec,
2594 x_msg_count => lx_msg_count,
2595 x_msg_data => lx_msg_data,
2596 x_return_status => l_return_status);
2597
2598 IF l_return_status = G_RET_STS_ERROR THEN
2599 RAISE G_EXCEPTION_ERROR;
2600 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2601 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2602 END IF;
2603
2604 END LOOP;
2605
2606 x_return_status := l_return_status;
2607 EXCEPTION
2608 WHEN G_EXCEPTION_ERROR THEN
2609 x_return_status := G_RET_STS_ERROR;
2610 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2611 x_return_status := G_RET_STS_UNEXP_ERROR;
2612 WHEN OTHERS THEN
2613 x_return_status := G_RET_STS_UNEXP_ERROR;
2614
2615 END update_status;
2616
2617 -- mvasudev , 04/24/2002 end
2618 ----------------------------------------------------------------------------------------
2619 -- PROCEDURE check_status: checks outbound status and if the outbound has time out
2620 -- returns a 'N' else returns 'Y' which indicates whether
2621 -- the inbound should proceed or not.
2622 ----------------------------------------------------------------------------------------
2623
2624 PROCEDURE check_status (
2625 p_transaction_number IN NUMBER,
2626 x_ok_to_proceed OUT NOCOPY VARCHAR2,
2627 x_return_status OUT NOCOPY VARCHAR2
2628 ) IS
2629
2630 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_rets';
2631 l_api_version CONSTANT NUMBER := 1;
2632 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2633
2634 CURSOR sif_data_csr (trx_number IN NUMBER) IS
2635 SELECT
2636 SIS_CODE
2637 FROM Okl_Stream_Interfaces
2638 WHERE okl_stream_interfaces.transaction_number = trx_number;
2639 BEGIN
2640 x_ok_to_proceed := 'Y';
2641 FOR sif_data IN sif_data_csr(p_transaction_number)
2642 LOOP
2643 IF sif_data.sis_code = 'TIME_OUT' or sif_data.sis_code = 'PROCESS_ABORTED' THEN
2644 x_ok_to_proceed := 'N';
2645 END IF;
2646 END LOOP;
2647 x_return_status := l_return_status;
2648 EXCEPTION
2649 WHEN G_EXCEPTION_ERROR THEN
2650 x_return_status := G_RET_STS_ERROR;
2651 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2652 x_return_status := G_RET_STS_UNEXP_ERROR;
2653 WHEN OTHERS THEN
2654 x_return_status := G_RET_STS_UNEXP_ERROR;
2655 END check_status;
2656
2657
2658 PROCEDURE log_error_messages (
2659 p_transaction_number IN NUMBER,
2660 x_return_status OUT NOCOPY VARCHAR2
2661 ) IS
2662 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2663 l_failure_code VARCHAR2(20) := 'PROCESSING_FAILED';
2664 l_error_message_tbl LOG_MSG_TBL_TYPE;
2665 l_msg_count NUMBER;
2666 l_log_file_name_pre VARCHAR2(15) := OKL_INVOKE_PRICING_ENGINE_PVT.G_FILENAME_PRE;
2667 l_log_file_name_ext VARCHAR2(15) := OKL_INVOKE_PRICING_ENGINE_PVT.G_FILENAME_EXT;
2668
2669 BEGIN
2670 -- log error message
2671 l_error_message_tbl(1) := 'Errors while processing Streams Results :- ';
2672 Okl_Streams_Util.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
2673 p_translate => G_FALSE,
2674 p_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext ,
2675 x_return_status => l_return_status );
2676
2677 l_msg_count := fnd_msg_pub.count_msg;
2678 Okl_Streams_Util.LOG_MESSAGE(p_msg_count => l_msg_count,
2679 p_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext,
2680 x_return_status => l_return_status);
2681
2682 l_error_message_tbl(1) := 'End Errors while processing Streams Results';
2683 Okl_Streams_Util.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
2684 p_translate => G_FALSE,
2685 p_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext ,
2686 x_return_status => l_return_status );
2687
2688 -- update status to PROCESSING_FAILED
2689 update_status (p_transaction_number => p_transaction_number,
2690 p_sis_code => l_failure_code, -- outbound status
2691 p_srt_code => l_failure_code, -- inbound status
2692 p_log_file_name => l_log_file_name_pre || p_transaction_number || l_log_file_name_ext,
2693 x_return_status => l_return_status);
2694
2695 x_return_status := l_return_status;
2696 EXCEPTION
2697 WHEN G_EXCEPTION_ERROR THEN
2698 x_return_status := G_RET_STS_ERROR;
2699 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2700 x_return_status := G_RET_STS_UNEXP_ERROR;
2701 WHEN OTHERS THEN
2702 x_return_status := G_RET_STS_UNEXP_ERROR;
2703 END log_error_messages;
2704
2705
2706 PROCEDURE populate_sif_ret_levels (
2707 p_sir_id IN NUMBER := OKC_API.G_MISS_NUM,
2708 p_index_number IN NUMBER := OKC_API.G_MISS_NUM,
2709 p_level_index_number IN NUMBER := OKC_API.G_MISS_NUM,
2710 p_number_of_periods IN NUMBER := OKC_API.G_MISS_NUM,
2711 p_level_type IN OKL_SIF_RET_LEVELS.LEVEL_TYPE%TYPE := OKC_API.G_MISS_CHAR,
2712 p_amount IN NUMBER := OKC_API.G_MISS_NUM,
2713 p_advance_or_arrears IN OKL_SIF_RET_LEVELS.ADVANCE_OR_ARREARS%TYPE := OKC_API.G_MISS_CHAR,
2714 p_period IN OKL_SIF_RET_LEVELS.PERIOD%TYPE := OKC_API.G_MISS_CHAR,
2715 p_lock_level_step IN OKL_SIF_RET_LEVELS.LOCK_LEVEL_STEP%TYPE := OKC_API.G_MISS_CHAR,
2716 p_days_in_period IN NUMBER := OKC_API.G_MISS_NUM,
2717 p_first_payment_date IN VARCHAR2,
2718 p_rate IN NUMBER := OKC_API.G_MISS_NUM,
2719 p_reamort_balance IN NUMBER := OKC_API.G_MISS_NUM,
2720 p_reamort_date IN VARCHAR2,
2721 x_return_status OUT NOCOPY VARCHAR2
2722 ) IS
2723 l_api_name CONSTANT VARCHAR2(30) := 'populate_sif_ret_levels';
2724 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2725 l_srlv_rec srlv_rec_type;
2726 l_api_version CONSTANT NUMBER := 1;
2727 l_init_msg_list VARCHAR2(1) := 'F';
2728 l_first_payment_date VARCHAR2(30);
2729 l_advance_or_arrears VARCHAR2(1);
2730 l_lock_level_step VARCHAR2(1);
2731
2732 x_srlv_rec srlv_rec_type;
2733 x_msg_data VARCHAR2(400);
2734 x_msg_count NUMBER ;
2735
2736 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||G_TRANSACTION_NUMBER;
2737 BEGIN
2738 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2739 p_pkg_name => G_PKG_NAME,
2740 p_init_msg_list => l_init_msg_list,
2741 l_api_version => l_api_version,
2742 p_api_version => l_api_version,
2743 p_api_type => G_API_TYPE,
2744 x_return_status => l_return_status);
2745 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2746 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2747 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2748 RAISE G_EXCEPTION_ERROR;
2749 END IF;
2750 l_srlv_rec.sir_id := p_sir_id;
2751 l_srlv_rec.index_number := p_index_number;
2752 l_srlv_rec.level_index_number := p_level_index_number;
2753 l_srlv_rec.number_of_periods := p_number_of_periods;
2754 l_srlv_rec.level_type := p_level_type;
2755 l_srlv_rec.amount := p_amount;
2756 --Modified by kthiruva on 11-Nov-2005
2757 --Bug 4726209 - Start of Changes
2758 OKL_ST_CODE_CONVERSIONS.REVERSE_TRANSLATE_ADV_OR_ARR(p_advance_or_arrears, l_advance_or_arrears);
2759 --Bug 4726209 - End of Changes
2760 l_srlv_rec.advance_or_arrears := l_advance_or_arrears;
2761 l_srlv_rec.period := p_period;
2762 OKL_ST_CODE_CONVERSIONS.REVERSE_TRANSLATE_YN(p_lock_level_step, l_lock_level_step);
2763 l_srlv_rec.lock_level_step := l_lock_level_step;
2764 l_srlv_rec.days_in_period := p_days_in_period;
2765 l_srlv_rec.rate := p_rate; --smahapat 10/12/03
2766 l_srlv_rec.reamort_balance := p_reamort_balance; --racheruv 18.02.2013
2767 l_srlv_rec.reamort_date := to_date(p_reamort_date, 'YYYY-MM-DD'); --racheruv 18.02.2013
2768
2769 l_first_payment_date := p_first_payment_date;
2770 l_first_payment_date := correct_feb_date(l_first_payment_date);
2771
2772 l_srlv_rec.first_payment_date := TO_DATE(l_first_payment_date, 'YYYY-MM-DD');
2773
2774
2775 /* Call main API */
2776 Okl_Populate_Prceng_Result_Pvt.populate_sif_ret_levels(p_api_version => l_api_version,
2777 p_init_msg_list => l_init_msg_list,
2778 x_return_status => l_return_status,
2779 x_msg_count => x_msg_count,
2780 x_msg_data => x_msg_data,
2781 p_srlv_rec => l_srlv_rec,
2782 x_srlv_rec => x_srlv_rec);
2783 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2784 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2785 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2786 RAISE G_EXCEPTION_ERROR;
2787 END IF;
2788
2789 -- Assign record returned by private api to local record
2790 l_srlv_rec := x_srlv_rec;
2791
2792
2793 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2794 x_msg_data => x_msg_data);
2795 x_return_status := l_return_status;
2796 EXCEPTION
2797 WHEN G_EXCEPTION_ERROR THEN
2798
2799 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2800 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
2801 END IF;
2802
2803 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2804 p_pkg_name => G_PKG_NAME,
2805 p_exc_name => G_EXC_NAME_ERROR,
2806 x_msg_count => x_msg_count,
2807 x_msg_data => x_msg_data,
2808 p_api_type => G_API_TYPE);
2809 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2810
2811 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2812 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
2813 END IF;
2814
2815 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2816 p_pkg_name => G_PKG_NAME,
2817 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2818 x_msg_count => x_msg_count,
2819 x_msg_data => x_msg_data,
2820 p_api_type => G_API_TYPE);
2821 WHEN OTHERS THEN
2822
2823 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
2824 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module ,G_TRANSACTION_NUMBER ||': '||x_msg_data);
2825 END IF;
2826
2827 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2828 p_pkg_name => G_PKG_NAME,
2829 p_exc_name => G_EXC_NAME_OTHERS,
2830 x_msg_count => x_msg_count,
2831 x_msg_data => x_msg_data,
2832 p_api_type => G_API_TYPE);
2833 END populate_sif_ret_levels;
2834
2835 ----------------------------------------------------------------------
2836 -- Procedure to call Okl_process_streams_pvt.Process_Streams_Result
2837 ----------------------------------------------------------------------
2838
2839 --Added by RIRAWAT
2840 -- This procedure has been added to replace the call to the Inbound Workflow.
2841 -- The method Okl_Process_Streams_Pvt.process_stream_results is now being called
2842 -- directly instead of invoking it through the workflow.
2843 -- Bug - Start of Changes
2844 PROCEDURE process( p_transaction_number IN NUMBER,
2845 resultout OUT NOCOPY VARCHAR2
2846 )
2847 IS
2848 l_transaction_number VARCHAR2(240);
2849 document_id VARCHAR2(240);
2850
2851 l_error_msg VARCHAR2(2000);
2852 result VARCHAR2(30);
2853 l_orp_code VARCHAR2(10);
2854
2855 l_api_version NUMBER := 1;
2856 l_api_name CONSTANT VARCHAR2(30) := 'process';
2857 l_init_msg_list VARCHAR2(1) := OKC_API.G_FALSE;
2858 l_return_status VARCHAR2(1);
2859 l_msg_count NUMBER;
2860 --srsreeni Bug 5599821 start
2861 l_msg_index_out NUMBER;
2862 --srsreeni Bug 5599821 end
2863 l_msg_data VARCHAR2(4000);
2864 l_msg_text VARCHAR2(4000);
2865 l_attr_name VARCHAR2(15) := 'ERROR_MSG';
2866 G_SIS_CODE VARCHAR2(50) := 'PROCESSING_FAILED';
2867 G_SRT_CODE VARCHAR2(50) := 'PROCESSING_FAILED';
2868 l_error_message_tbl LOG_MSG_TBL_TYPE;
2869 l_error_message_line VARCHAR2(4000) := NULL;
2870
2871 -- smahapat added khr_id to cursor for bug 3145238
2872 CURSOR strm_interfaces_data_csr (p_trx_number NUMBER) IS
2873 SELECT
2874 ORP_CODE, KHR_ID
2875 FROM okl_stream_interfaces
2876 WHERE okl_stream_interfaces.transaction_number = p_trx_number;
2877
2878 -- smahapat added for bug 3145238
2879 -- cursor to provide information for setting context
2880 CURSOR l_hdr_csr(chrId NUMBER)
2881 IS
2882 SELECT chr.orig_system_source_code,
2883 chr.start_date,
2884 chr.end_date,
2885 chr.template_yn,
2886 chr.authoring_org_id,
2887 chr.inv_organization_id,
2888 khr.deal_type,
2889 pdt.id pid,
2890 NVL(pdt.reporting_pdt_id, -1) report_pdt_id,
2891 chr.currency_code currency_code,
2892 khr.term_duration term
2893 --srsreeni Bug6004114 start
2894 -- FROM okc_k_headers_v chr,
2895 FROM okc_k_headers_all_b chr,
2896 --srsreeni Bug6004114 end
2897 okl_k_headers khr,
2898 okl_products_v pdt
2899 WHERE khr.id = chr.id
2900 AND chr.id = chrId
2901 AND khr.pdt_id = pdt.id(+);
2902
2903 l_hdr_rec l_hdr_csr%ROWTYPE;
2904 l_khr_id NUMBER;
2905 -- end code for setting context
2906 l_new_line VARCHAR2(10) := FND_GLOBAL.NEWLINE;
2907
2908 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
2909
2910 BEGIN
2911
2912 G_TRANSACTION_NUMBER := p_transaction_number;
2913
2914 IF(G_DEBUG_ENABLED = 'Y') THEN
2915 G_IS_DEBUG_EXCEPTION_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_EXCEPTION);
2916 END IF;
2917
2918 IF(G_DEBUG_ENABLED = 'Y') THEN
2919 G_IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_PROCEDURE);
2920 END IF;
2921
2922 IF(G_DEBUG_ENABLED = 'Y') THEN
2923 G_IS_DEBUG_ERROR_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_ERROR);
2924 END IF;
2925
2926 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
2927 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module,p_transaction_number||': begin okl_populate_prceng_rst_pub.process');
2928 END IF;
2929
2930 -- Do nothing in cancel or timeout mode
2931 l_transaction_number:=p_transaction_number;
2932
2933 -- Invoke Process Stream Results API
2934 FOR strm_interfaces_data in strm_interfaces_data_csr(l_transaction_number)
2935 LOOP
2936 l_orp_code := strm_interfaces_data.orp_code;
2937 l_khr_id := strm_interfaces_data.khr_id; -- added by smahapat for setting context (bug 3145238)
2938 END LOOP;
2939
2940 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
2941 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': l_orp_code = '||l_orp_code);
2942 END IF;
2943
2944 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
2945 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': l_khr_id = '||l_khr_id);
2946 END IF;
2947
2948 -- set context (bug 3145238)
2949 OPEN l_hdr_csr( l_khr_id );
2950 FETCH l_hdr_csr INTO l_hdr_rec;
2951 CLOSE l_hdr_csr;
2952 okl_context.set_okc_org_context(l_hdr_rec.authoring_org_id,l_hdr_rec.inv_organization_id);
2953 -- end set context
2954
2955
2956 -- Booking
2957 IF (l_orp_code = 'AUTH')
2958 THEN
2959 OKL_PROCESS_STREAMS_PVT.PROCESS_STREAM_RESULTS (l_api_version
2960 ,l_init_msg_list
2961 ,l_transaction_number
2962 ,l_return_status
2963 ,l_msg_count
2964 ,l_msg_data);
2965 -- Restrucutres
2966 ELSIF(l_orp_code = 'RSAM')
2967 THEN
2968 OKL_PROCESS_STREAMS_PVT.PROCESS_REST_STRM_RESLTS(
2969 p_api_version => l_api_version
2970 ,p_init_msg_list => l_init_msg_list
2971 ,p_transaction_number => l_transaction_number
2972 ,x_return_status => l_return_status
2973 ,x_msg_count => l_msg_count
2974 ,x_msg_data => l_msg_data);
2975 -- Quotes
2976 ELSIF(l_orp_code = 'QUOT')
2977 THEN
2978 OKL_PROCESS_STREAMS_PVT.PROCESS_QUOT_STRM_RESLTS(
2979 p_api_version => l_api_version
2980 ,p_init_msg_list => l_init_msg_list
2981 ,p_transaction_number => l_transaction_number
2982 ,x_return_status => l_return_status
2983 ,x_msg_count => l_msg_count
2984 ,x_msg_data => l_msg_data);
2985 -- Renewals
2986 ELSIF(l_orp_code = 'RENW')
2987 THEN
2988 OKL_PROCESS_STREAMS_PVT.PROCESS_RENW_STRM_RESLTS(
2989 p_api_version => l_api_version
2990 ,p_init_msg_list => l_init_msg_list
2991 ,p_transaction_number => l_transaction_number
2992 ,x_return_status => l_return_status
2993 ,x_msg_count => l_msg_count
2994 ,x_msg_data => l_msg_data);
2995 -- Variable Interest Rate Processing
2996 ELSIF(l_orp_code = 'VIRP')
2997 THEN
2998 OKL_PROCESS_STREAMS_PVT.PROCESS_VIRP_STRM_RESLTS(
2999 p_api_version => l_api_version
3000 ,p_init_msg_list => l_init_msg_list
3001 ,p_transaction_number => l_transaction_number
3002 ,x_return_status => l_return_status
3003 ,x_msg_count => l_msg_count
3004 ,x_msg_data => l_msg_data);
3005
3006
3007 END IF;
3008
3009 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
3010 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': l_return_status = '||l_return_status);
3011 END IF;
3012
3013
3014 -- if error
3015 IF(l_return_status <> G_RET_STS_SUCCESS)
3016 THEN
3017 l_error_msg := ' ';
3018 FOR i IN 1..l_msg_count
3019 LOOP
3020 --srsreeni Bug5599821 start
3021 /* fnd_msg_pub.get(p_data => l_msg_text,
3022 p_msg_index_out => l_msg_count,
3023 p_encoded => G_FALSE,
3024 p_msg_index => fnd_msg_pub.g_next);*/
3025 fnd_msg_pub.get (p_msg_index => i,
3026 p_encoded => 'F',
3027 p_data => l_msg_text,
3028 p_msg_index_out => l_msg_index_out);
3029 --srsreeni Bug5599821 end
3030 IF i = 1 THEN
3031 l_error_msg := l_msg_text;
3032 ELSE
3033 l_error_msg := l_error_msg || l_new_line || l_msg_text;
3034 END IF;
3035 END LOOP;
3036
3037 IF(G_IS_DEBUG_ERROR_ON) THEN
3038 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_ERROR, l_module, p_transaction_number||': '||l_error_msg);
3039 END IF;
3040
3041 l_error_message_tbl(1) := 'Errors while processing Streams Results :- ';
3042
3043 Okl_Streams_Util.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
3044 p_translate => G_FALSE,
3045 p_file_name => 'OKLSTXMLG_' || l_transaction_number || '.log' ,
3046 x_return_status => l_return_status );
3047
3048 --srsreeni Bug5599821 start
3049 /* Okl_Streams_Util.LOG_MESSAGE(p_msg_count => l_msg_count,
3050 p_file_name => 'OKLSTXMLG_' || l_transaction_number || '.log',
3051 x_return_status => l_return_status
3052 );*/
3053 Okl_Streams_Util.LOG_MESSAGE(
3054 p_msg_name => l_error_msg,
3055 p_translate => G_FALSE,
3056 p_file_name => 'OKLSTXMLG_' || l_transaction_number || '.log',
3057 x_return_status => l_return_status
3058 );
3059 --srsreeni Bug5599821 end
3060 l_error_message_tbl(1) := 'End Errors while processing Streams Results';
3061 Okl_Streams_Util.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
3062 p_translate => G_FALSE,
3063 p_file_name => 'OKLSTXMLG_' || l_transaction_number || '.log' ,
3064 x_return_status => l_return_status );
3065
3066
3067 OKL_POPULATE_PRCENG_RST_PUB.UPDATE_STATUS(--p_api_version => l_api_version,
3068 --p_init_msg_list => l_init_msg_list,
3069 p_transaction_number => l_transaction_number,
3070 p_sis_code => G_SIS_CODE,
3071 p_srt_code => G_SRT_CODE,
3072 p_log_file_name => 'OKLSTXMLG_' || l_transaction_number || '.log',
3073 x_return_status => l_return_status
3074 );
3075 --srsreeni Bug6011651 starts.Updates to ERROR when the processing fails
3076 OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
3077 p_api_version => l_api_version,
3078 p_init_msg_list => l_init_msg_list,
3079 x_return_status => l_return_status,
3080 x_msg_count => l_msg_count,
3081 x_msg_data => l_msg_data,
3082 p_khr_id => l_khr_id,
3083 p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
3084 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
3085 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3086 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3087 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3088 RAISE OKL_API.G_EXCEPTION_ERROR;
3089 END IF;
3090 --srsreeni Bug6011651 ends
3091 resultout := 'F';
3092 ELSE
3093 resultout := 'T';
3094 END IF;
3095
3096 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
3097 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module,p_transaction_number||': resultout = '||resultout);
3098 END IF;
3099
3100 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
3101 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module,p_transaction_number||': end okl_populate_prceng_rst_pub.process');
3102 END IF;
3103
3104 EXCEPTION
3105 WHEN OTHERS THEN
3106 --srsreeni Bug6011651 starts.Updates to ERROR when exception occurs
3107 OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
3108 p_api_version => l_api_version,
3109 p_init_msg_list => l_init_msg_list,
3110 x_return_status => l_return_status,
3111 x_msg_count => l_msg_count,
3112 x_msg_data => l_msg_data,
3113 p_khr_id => l_khr_id,
3114 p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
3115 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
3116 --srsreeni Bug6011651 ends
3117 IF(G_IS_DEBUG_EXCEPTION_ON) THEN
3118 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION, l_module, p_transaction_number||': '||SQLERRM(SQLCODE));
3119 END IF;
3120
3121 RAISE;
3122 END process;
3123 -- Bug End of Changes
3124 --Added by KTHIRUVA
3125 -- This procedure has been added to raise a business event once the call to
3126 -- Okl_Process_Streams_Pvt.process_stream_results completes
3127 PROCEDURE raise_business_event(p_transaction_number IN NUMBER,
3128 x_return_status OUT NOCOPY VARCHAR2)
3129 IS
3130
3131 BEGIN
3132 x_return_status := G_RET_STS_SUCCESS;
3133 -- raise the event
3134 -- The parameter list being passed is empty
3135 wf_event.RAISE(p_event_name => G_XMLG_RECEIVE_EVENT,
3136 p_event_key => TO_CHAR(p_transaction_number)
3137 );
3138
3139 END raise_business_event;
3140
3141 END OKL_POPULATE_PRCENG_RST_PUB;