[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_INVESTOR_REVENUE_PVT
Source
1 PACKAGE BODY Okl_Sec_Investor_Revenue_Pvt AS
2 /* $Header: OKLRSZRB.pls 120.2 2005/10/30 04:38:35 appldev noship $ */
3
4 PROCEDURE VALIDATE(p_szr_rec IN szr_rec_type,
5 x_return_status OUT NOCOPY VARCHAR2
6 )
7 IS
8
9 -- mvasudev, 10/12/2004, Bug#3909240
10 /*
11 CURSOR l_okl_sty_percent_csr(p_khr_id IN NUMBER, p_sty_id IN NUMBER)
12 IS
13 SELECT kleb.percent_stake percent_stake
14 FROM okl_k_lines kleb,
15 okc_k_lines_b cles
16 WHERE kleb.id = cles.id
17 AND cles.dnz_chr_id = p_khr_id
18 AND cles.cle_id IS NOT NULL
19 AND kleb.sty_id = p_sty_id
20 AND kleb.id <> p_szr_rec.id;
21 */
22 CURSOR l_okl_sty_percent_csr(p_khr_id IN NUMBER, p_sty_subclass IN VARCHAR2)
23 IS
24 SELECT kleb.percent_stake percent_stake
25 FROM okl_k_lines kleb,
26 okc_k_lines_b cles
27 WHERE kleb.id = cles.id
28 AND cles.dnz_chr_id = p_khr_id
29 AND cles.cle_id IS NOT NULL
30 AND kleb.stream_type_subclass = p_sty_subclass
31 AND kleb.id <> p_szr_rec.id;
32
33 -- To check only one row exists for a subclass for an Investor
34 CURSOR l_okl_sty_subclass_csr(p_khr_id IN NUMBER, p_top_line_id IN NUMBER,p_sty_subclass IN VARCHAR2)
35 IS
36 SELECT '1'
37 FROM okl_k_lines kleb,
38 okc_k_lines_b cles
39 WHERE kleb.id = cles.id
40 AND cles.dnz_chr_id = p_khr_id
41 AND cles.cle_id = p_top_line_id
42 AND kleb.stream_type_subclass = p_sty_subclass
43 AND kleb.id <> p_szr_rec.id;
44
45 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
46 l_total_percent NUMBER := 0;
47
48 BEGIN
49 -- mvasudev, 10/12/2004, Bug#3909240
50 /*
51 -- check for stream type
52 IF(p_szr_rec.kle_sty_id IS NULL OR p_szr_rec.kle_sty_id = OKC_API.G_MISS_NUM) THEN
53 x_return_status := G_RET_STS_ERROR;
54 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
55 p_region_code => G_AK_REGION_NAME,
56 p_attribute_code => 'OKL_STREAM_TYPE');
57 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
58 p_msg_name => 'OKL_REQUIRED_VALUE',
59 p_token1 => 'COL_NAME',
60 p_token1_value => l_ak_prompt);
61 RAISE OKC_API.G_EXCEPTION_ERROR;
62 END IF;
63 */
64 -- check for stream type subclass
65 IF(p_szr_rec.kle_sty_subclass IS NULL OR p_szr_rec.kle_sty_subclass = OKC_API.G_MISS_CHAR) THEN
66 x_return_status := G_RET_STS_ERROR;
67 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
68 p_region_code => G_AK_REGION_NAME,
69 p_attribute_code => 'OKL_STREAM_TYPE_SUBCLASS');
70 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
71 p_msg_name => 'OKL_REQUIRED_VALUE',
72 p_token1 => 'COL_NAME',
73 p_token1_value => l_ak_prompt);
74 RAISE OKC_API.G_EXCEPTION_ERROR;
75 END IF;
76 -- end,mvasudev, 10/12/2004, Bug#3909240
77
78 -- check for top line id
79 IF(p_szr_rec.top_line_id IS NULL OR p_szr_rec.top_line_id = OKC_API.G_MISS_NUM) THEN
80 x_return_status := G_RET_STS_ERROR;
81 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
82 p_region_code => G_AK_REGION_NAME,
83 p_attribute_code => 'OKL_LA_SEC_INVESTOR');
84 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
85 p_msg_name => 'OKL_REQUIRED_VALUE',
86 p_token1 => 'COL_NAME',
87 p_token1_value => l_ak_prompt);
88 RAISE OKC_API.G_EXCEPTION_ERROR;
89 END IF;
90
91 -- check for percent_stake
92 IF(p_szr_rec.kle_percent_stake IS NULL OR p_szr_rec.kle_percent_stake = OKC_API.G_MISS_NUM) THEN
93 x_return_status := G_RET_STS_ERROR;
94 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
95 p_region_code => G_AK_REGION_NAME,
96 p_attribute_code => 'OKL_SHARE_PERCENT');
97 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
98 p_msg_name => 'OKL_REQUIRED_VALUE',
99 p_token1 => 'COL_NAME',
100 p_token1_value => l_ak_prompt);
101 RAISE OKC_API.G_EXCEPTION_ERROR;
102 ELSIF p_szr_rec.kle_percent_stake > 100 THEN
103 x_return_status := G_RET_STS_ERROR;
104 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
105 p_msg_name => 'OKL_LLA_PERCENT');
106 RAISE OKC_API.G_EXCEPTION_ERROR;
107 END IF;
108
109 -- mvasudev, 10/12/2004, Bug#3909240
110 -- Check for unique Subclass for an Investor
111 FOR l_okl_sty_subclass_rec IN l_okl_sty_subclass_csr(p_szr_rec.dnz_chr_id, p_szr_rec.top_line_id,p_szr_rec.kle_sty_subclass)
112 LOOP
113 x_return_status := G_RET_STS_ERROR;
114 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
115 p_region_code => G_AK_REGION_NAME,
116 p_attribute_code => 'OKL_STREAM_TYPE_SUBCLASS');
117 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
118 p_msg_name => 'OKL_INV_STY_UNIQUE',
119 p_token1 => 'STY_SUBCLASS',
120 p_token1_value => l_ak_prompt);
121 RAISE OKC_API.G_EXCEPTION_ERROR;
122 END LOOP;
123
124
125 -- mvasudev, v115.5
126 l_total_percent := p_szr_rec.kle_percent_stake;
127 -- mvasudev, 10/12/2004, Bug#3909240
128 --FOR l_okl_sty_percent_rec IN l_okl_sty_percent_csr(p_szr_rec.dnz_chr_id, p_szr_rec.kle_sty_id)
129 FOR l_okl_sty_percent_rec IN l_okl_sty_percent_csr(p_szr_rec.dnz_chr_id, p_szr_rec.kle_sty_subclass)
130 LOOP
131 l_total_percent := l_total_percent + l_okl_sty_percent_rec.percent_stake;
132 END LOOP;
133
134 IF l_total_percent > 100 THEN
135 x_return_status := G_RET_STS_ERROR;
136 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
137 p_region_code => G_AK_REGION_NAME,
138 p_attribute_code => 'OKL_STREAM_TYPE');
139 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
140 p_msg_name => 'OKL_REV_SHARE_PERCENT',
141 p_token1 => 'TITLE',
142 p_token1_value => l_ak_prompt);
143 RAISE OKC_API.G_EXCEPTION_ERROR;
144 END IF;
145 END VALIDATE;
146
147 PROCEDURE migrate_records(p_szr_rec IN szr_rec_type,
148 x_clev_rec OUT NOCOPY clev_rec_type,
149 x_klev_rec OUT NOCOPY klev_rec_type)
150 IS
151
152 CURSOR l_okl_top_line_details_csr(p_tl_id IN NUMBER)
153 IS
154 SELECT clet.start_date
155 ,clet.end_date
156 ,clet.currency_code
157 FROM okc_k_lines_b clet
158 ,okc_line_styles_b lseb
159 WHERE clet.id = p_tl_id
160 AND clet.lse_id = lseb.id
161 AND lseb.lty_code = 'INVESTMENT';
162
163 l_clev_rec clev_rec_type;
164 l_klev_rec klev_rec_type;
165
166 BEGIN
167
168 l_clev_rec.id := p_szr_rec.id;
169 l_clev_rec.cle_id := p_szr_rec.top_line_id;
170 l_clev_rec.dnz_chr_id := p_szr_rec.dnz_chr_id;
171 -- other implicit details
172 FOR l_okl_top_line_details_rec IN l_okl_top_line_details_csr(p_szr_rec.top_line_id)
173 LOOP
174 l_clev_rec.start_date := l_okl_top_line_details_rec.start_date;
175 l_clev_rec.end_date := l_okl_top_line_details_rec.end_date;
176 l_clev_rec.currency_code := l_okl_top_line_details_rec.currency_code;
177 END LOOP;
178
179 l_klev_rec.id := p_szr_rec.id;
180
181 -- mvasudev, 10/12/2004, Bug#3909240
182 --l_klev_rec.sty_id := p_szr_rec.kle_sty_id;
183 l_klev_rec.stream_type_subclass := p_szr_rec.kle_sty_subclass;
184
185 l_klev_rec.percent_stake := p_szr_rec.kle_percent_stake;
186
187 x_clev_rec := l_clev_rec;
188 x_klev_rec := l_klev_rec;
189
190 END migrate_records;
191
192 PROCEDURE CREATE_INVESTOR_REVENUE(p_api_version IN NUMBER,
193 p_init_msg_list IN VARCHAR2,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 p_szr_rec IN szr_rec_type,
198 x_szr_rec OUT NOCOPY szr_rec_type)
199 IS
200 CURSOR l_okl_lse_csr(p_lty_code IN VARCHAR2) IS
201 SELECT ID
202 FROM okc_line_styles_v
203 WHERE lty_code = p_lty_code;
204
205 -- mvasudev, 10/12/2004, Bug#3909240
206 /*
207 CURSOR l_okl_cle_sty_csr(p_cle_id IN NUMBER, p_sty_id IN NUMBER) IS
208 SELECT '1'
209 FROM
210 OKL_K_LINES KLEB,
211 OKC_K_LINES_B CLET,
212 OKC_K_LINES_B CLES
213 WHERE
214 KLEB.ID = CLES.ID
215 AND CLES.CLE_ID = CLET.ID
216 AND CLET.ID = p_cle_id
217 AND KLEB.STY_ID = p_sty_id;
218 */
219 CURSOR l_okl_cle_sty_csr(p_cle_id IN NUMBER, p_sty_subclass IN VARCHAR2) IS
220 SELECT '1'
221 FROM
222 OKL_K_LINES KLEB,
223 OKC_K_LINES_B CLET,
224 OKC_K_LINES_B CLES
225 WHERE
226 KLEB.ID = CLES.ID
227 AND CLES.CLE_ID = CLET.ID
228 AND CLET.ID = p_cle_id
229 AND KLEB.stream_type_subclass = p_sty_subclass;
230
231
232 /* Taken care in migrate function
233 CURSOR l_okl_cle_start_date_csr(p_cle_id IN NUMBER) IS
234 SELECT CLET.START_DATE
235 FROM
236 OKL_K_LINES KLEB,
237 OKC_K_LINES_B CLET,
238 OKC_K_LINES_B CLES
239 WHERE
240 KLEB.ID = CLES.ID
241 AND CLES.CLE_ID = CLET.ID
242 AND CLET.ID = p_cle_id;
243 */
244 l_clev_rec clev_rec_type;
245 l_klev_rec klev_rec_type;
246 lx_clev_rec clev_rec_type;
247 lx_klev_rec klev_rec_type;
248 l_szr_rec szr_rec_type;
249 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INVESTOR_REVENUE';
250 l_api_version CONSTANT NUMBER := 1.0;
251 l_found BOOLEAN := FALSE;
252 l_dummy VARCHAR2(1) := '?';
253 BEGIN
254
255 x_return_status := OKC_API.G_RET_STS_SUCCESS;
256 -- call START_ACTIVITY to create savepoint, check compatibility
257 -- and initialize message list
258 x_return_status := OKC_API.START_ACTIVITY(
259 p_api_name => l_api_name,
260 p_pkg_name => G_PKG_NAME,
261 p_init_msg_list => p_init_msg_list,
262 l_api_version => l_api_version,
263 p_api_version => p_api_version,
264 p_api_type => G_API_TYPE,
265 x_return_status => x_return_status);
266
267 -- check if activity started successfully
268 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
269 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
270 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
271 RAISE OKC_API.G_EXCEPTION_ERROR;
272 END IF;
273
274 VALIDATE(p_szr_rec => p_szr_rec,
275 x_return_status => x_return_status);
276 IF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
277 RAISE OKC_API.G_EXCEPTION_ERROR;
278 END IF;
279
280 OPEN l_okl_cle_sty_csr(p_szr_rec.top_line_id,p_szr_rec.kle_sty_subclass);
281 FETCH l_okl_cle_sty_csr INTO l_dummy;
282 l_found := l_okl_cle_sty_csr%FOUND;
283 CLOSE l_okl_cle_sty_csr;
284
285 IF(l_found) THEN
286 OKL_API.set_message(G_APP_NAME,'OKL_STY_NOT_UNIQUE');
287 x_return_status := G_RET_STS_ERROR;
288 RAISE OKC_API.G_EXCEPTION_ERROR;
289 END IF;
290
291 migrate_records(p_szr_rec => p_szr_rec,
292 x_clev_rec => l_clev_rec,
293 x_klev_rec => l_klev_rec);
294
295 OPEN l_okl_lse_csr('REVENUE_SHARE');
296 FETCH l_okl_lse_csr INTO l_clev_rec.lse_id;
297 CLOSE l_okl_lse_csr;
298
299 /* taken care in migrate function
300 OPEN l_okl_cle_start_date_csr(p_szr_rec.top_line_id);
301 FETCH l_okl_cle_start_date_csr INTO l_clev_rec.start_date;
302 CLOSE l_okl_cle_start_date_csr;
303 */
304 l_clev_rec.line_number := '1';
305 l_clev_rec.display_sequence := 1;
306 l_clev_rec.exception_yn := 'N';
307 l_clev_rec.sts_code := 'NEW';
308
309 OKL_CONTRACT_PUB.create_contract_line(
310 p_api_version => p_api_version,
311 p_init_msg_list => p_init_msg_list,
312 x_return_status => x_return_status,
313 x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data,
315 p_clev_rec => l_clev_rec,
316 p_klev_rec => l_klev_rec,
317 x_clev_rec => lx_clev_rec,
318 x_klev_rec => lx_klev_rec);
319
320 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
321 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
322 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
323 RAISE OKL_API.G_EXCEPTION_ERROR;
324 END IF;
325
326 --Call End Activity
327 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data);
329
330
331 EXCEPTION
332 WHEN OKC_API.G_EXCEPTION_ERROR THEN
333 IF l_okl_lse_csr%ISOPEN
334 THEN
335 CLOSE l_okl_lse_csr;
336 END IF;
337 IF l_okl_cle_sty_csr%ISOPEN
338 THEN
339 CLOSE l_okl_cle_sty_csr;
340 END IF;
341 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
342 p_api_name => l_api_name,
343 p_pkg_name => G_PKG_NAME,
344 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data,
347 p_api_type => G_API_TYPE);
348
349 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
350 IF l_okl_lse_csr%ISOPEN
351 THEN
352 CLOSE l_okl_lse_csr;
353 END IF;
354 IF l_okl_cle_sty_csr%ISOPEN
355 THEN
356 CLOSE l_okl_cle_sty_csr;
357 END IF;
358 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
359 p_api_name => l_api_name,
360 p_pkg_name => G_PKG_NAME,
361 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
362 x_msg_count => x_msg_count,
363 x_msg_data => x_msg_data,
364 p_api_type => G_API_TYPE);
365
366 WHEN OTHERS THEN
367 IF l_okl_lse_csr%ISOPEN
368 THEN
369 CLOSE l_okl_lse_csr;
370 END IF;
371 IF l_okl_cle_sty_csr%ISOPEN
372 THEN
373 CLOSE l_okl_cle_sty_csr;
374 END IF;
375 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
376 p_api_name => l_api_name,
377 p_pkg_name => G_PKG_NAME,
378 p_exc_name => 'OTHERS',
379 x_msg_count => x_msg_count,
380 x_msg_data => x_msg_data,
381 p_api_type => G_API_TYPE);
382
383 END CREATE_INVESTOR_REVENUE;
384
385 PROCEDURE UPDATE_INVESTOR_REVENUE(p_api_version IN NUMBER,
386 p_init_msg_list IN VARCHAR2,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2,
390 p_szr_rec IN szr_rec_type,
391 x_szr_rec OUT NOCOPY szr_rec_type)
392 IS
393 l_clev_rec clev_rec_type;
394 l_klev_rec klev_rec_type;
395
396 lx_clev_rec clev_rec_type;
397 lx_klev_rec klev_rec_type;
398
399
400 l_szr_rec szr_rec_type;
401
402 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
403 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INVESTOR_REVENUE';
404 l_api_version CONSTANT NUMBER := 1.0;
405
406 BEGIN
407 x_return_status := OKC_API.G_RET_STS_SUCCESS;
408 -- call START_ACTIVITY to create savepoint, check compatibility
409 -- and initialize message list
410 x_return_status := OKC_API.START_ACTIVITY(
411 p_api_name => l_api_name,
412 p_pkg_name => G_PKG_NAME,
413 p_init_msg_list => p_init_msg_list,
414 l_api_version => l_api_version,
415 p_api_version => p_api_version,
416 p_api_type => G_API_TYPE,
417 x_return_status => x_return_status);
418
419 -- check if activity started successfully
420 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
421 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
422 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
423 RAISE OKC_API.G_EXCEPTION_ERROR;
424 END IF;
425
426 VALIDATE(p_szr_rec => p_szr_rec,
427 x_return_status => x_return_status);
428 IF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
429 RAISE OKC_API.G_EXCEPTION_ERROR;
430 END IF;
431
432 migrate_records(p_szr_rec => p_szr_rec,
433 x_clev_rec => l_clev_rec,
434 x_klev_rec => l_klev_rec);
435
436 OKL_CONTRACT_PUB.update_contract_line(
437 p_api_version => p_api_version,
438 p_init_msg_list => p_init_msg_list,
439 x_return_status => x_return_status,
440 x_msg_count => x_msg_count,
441 x_msg_data => x_msg_data,
442 p_clev_rec => l_clev_rec,
443 p_klev_rec => l_klev_rec,
444 x_clev_rec => lx_clev_rec,
445 x_klev_rec => lx_klev_rec);
446
447 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
448 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
449 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
450 RAISE OKL_API.G_EXCEPTION_ERROR;
451 END IF;
452
453 --Call End Activity
454 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
455 x_msg_data => x_msg_data);
456
457
458 EXCEPTION
459 WHEN OKC_API.G_EXCEPTION_ERROR THEN
460 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
461 p_api_name => l_api_name,
462 p_pkg_name => G_PKG_NAME,
463 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
464 x_msg_count => x_msg_count,
465 x_msg_data => x_msg_data,
466 p_api_type => G_API_TYPE);
467
468 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
469 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
470 p_api_name => l_api_name,
471 p_pkg_name => G_PKG_NAME,
472 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
473 x_msg_count => x_msg_count,
474 x_msg_data => x_msg_data,
475 p_api_type => G_API_TYPE);
476
477 WHEN OTHERS THEN
478 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
479 p_api_name => l_api_name,
480 p_pkg_name => G_PKG_NAME,
481 p_exc_name => 'OTHERS',
482 x_msg_count => x_msg_count,
483 x_msg_data => x_msg_data,
484 p_api_type => G_API_TYPE);
485
486
487
488 END UPDATE_INVESTOR_REVENUE;
489
490 PROCEDURE DELETE_INVESTOR_REVENUE(p_api_version IN NUMBER,
491 p_init_msg_list IN VARCHAR2,
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_msg_data OUT NOCOPY VARCHAR2,
495 p_szr_rec IN szr_rec_type)
496 IS
497 l_clev_rec clev_rec_type;
498 l_klev_rec klev_rec_type;
499
500 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
501 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INVESTOR_REVENUE';
502 l_api_version CONSTANT NUMBER := 1.0;
503
504 BEGIN
505 x_return_status := OKC_API.G_RET_STS_SUCCESS;
506 -- call START_ACTIVITY to create savepoint, check compatibility
507 -- and initialize message list
508 x_return_status := OKC_API.START_ACTIVITY(
509 p_api_name => l_api_name,
510 p_pkg_name => G_PKG_NAME,
511 p_init_msg_list => p_init_msg_list,
512 l_api_version => l_api_version,
513 p_api_version => p_api_version,
514 p_api_type => G_API_TYPE,
515 x_return_status => x_return_status);
516
517 -- check if activity started successfully
518 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
519 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
520 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
521 RAISE OKC_API.G_EXCEPTION_ERROR;
522 END IF;
523
524 migrate_records(p_szr_rec => p_szr_rec,
525 x_clev_rec => l_clev_rec,
526 x_klev_rec => l_klev_rec);
527
528 OKL_CONTRACT_PUB.delete_contract_line(
529 p_api_version => p_api_version,
530 p_init_msg_list => p_init_msg_list,
531 x_return_status => x_return_status,
532 x_msg_count => x_msg_count,
533 x_msg_data => x_msg_data,
534 p_clev_rec => l_clev_rec,
535 p_klev_rec => l_klev_rec);
536
537 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
538 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
539 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
540 RAISE OKL_API.G_EXCEPTION_ERROR;
541 END IF;
542
543 --Call End Activity
544 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
545 x_msg_data => x_msg_data);
546 EXCEPTION
547 WHEN OKC_API.G_EXCEPTION_ERROR THEN
548 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
549 p_api_name => l_api_name,
550 p_pkg_name => G_PKG_NAME,
551 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
552 x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data,
554 p_api_type => G_API_TYPE);
555
556 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
557 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
558 p_api_name => l_api_name,
559 p_pkg_name => G_PKG_NAME,
560 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
561 x_msg_count => x_msg_count,
562 x_msg_data => x_msg_data,
563 p_api_type => G_API_TYPE);
564
565 WHEN OTHERS THEN
566 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
567 p_api_name => l_api_name,
568 p_pkg_name => G_PKG_NAME,
569 p_exc_name => 'OTHERS',
570 x_msg_count => x_msg_count,
571 x_msg_data => x_msg_data,
572 p_api_type => G_API_TYPE);
573
574 END DELETE_INVESTOR_REVENUE;
575
576 PROCEDURE CREATE_INVESTOR_REVENUE(p_api_version IN NUMBER,
577 p_init_msg_list IN VARCHAR2,
578 x_return_status OUT NOCOPY VARCHAR2,
579 x_msg_count OUT NOCOPY NUMBER,
580 x_msg_data OUT NOCOPY VARCHAR2,
581 p_szr_tbl IN szr_tbl_type,
582 x_szr_tbl OUT NOCOPY szr_tbl_type)
583 IS
584
585 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INVESTOR_REVENUE_TBL';
586 rec_num INTEGER := 0;
587 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
588 l_api_version CONSTANT NUMBER := 1;
589 BEGIN
590
591 FOR rec_num IN 1..p_szr_tbl.COUNT
592 LOOP
593 create_investor_revenue(
594 p_api_version => p_api_version,
595 p_init_msg_list => p_init_msg_list,
596 x_return_status => l_return_status,
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data,
599 p_szr_rec => p_szr_tbl(rec_num),
600 x_szr_rec => x_szr_tbl(rec_num) );
601 IF l_return_status = G_RET_STS_ERROR THEN
602 RAISE G_EXCEPTION_ERROR;
603 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
604 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
605 END IF;
606 END LOOP;
607 x_return_status := l_return_status;
608 EXCEPTION
609 WHEN G_EXCEPTION_ERROR THEN
610 x_return_status := G_RET_STS_ERROR;
611 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
612 x_return_status := G_RET_STS_UNEXP_ERROR;
613 WHEN OTHERS THEN
614 -- store SQL error message on message stack for caller
615 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
616 p_msg_name => G_UNEXPECTED_ERROR,
617 p_token1 => G_SQLCODE_TOKEN,
618 p_token1_value => SQLCODE,
619 p_token2 => G_SQLERRM_TOKEN,
620 p_token2_value => SQLERRM );
621 -- notify caller of an UNEXPECTED error
622 x_return_status := G_RET_STS_UNEXP_ERROR;
623
624 END CREATE_INVESTOR_REVENUE;
625
626 PROCEDURE UPDATE_INVESTOR_REVENUE(p_api_version IN NUMBER,
627 p_init_msg_list IN VARCHAR2,
628 x_return_status OUT NOCOPY VARCHAR2,
629 x_msg_count OUT NOCOPY NUMBER,
630 x_msg_data OUT NOCOPY VARCHAR2,
631 p_szr_tbl IN szr_tbl_type,
632 x_szr_tbl OUT NOCOPY szr_tbl_type)
633 IS
634 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INVESTOR_REVENUE_TBL';
635 rec_num INTEGER := 0;
636 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
637 l_api_version CONSTANT NUMBER := 1;
638 BEGIN
639
640 FOR rec_num IN 1..p_szr_tbl.COUNT
641 LOOP
642 update_investor_revenue(
643 p_api_version => p_api_version,
644 p_init_msg_list => p_init_msg_list,
645 x_return_status => l_return_status,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data,
648 p_szr_rec => p_szr_tbl(rec_num),
649 x_szr_rec => x_szr_tbl(rec_num) );
650 IF l_return_status = G_RET_STS_ERROR THEN
651 RAISE G_EXCEPTION_ERROR;
652 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
653 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
654 END IF;
655 END LOOP;
656 x_return_status := l_return_status;
657 EXCEPTION
658 WHEN G_EXCEPTION_ERROR THEN
659 x_return_status := G_RET_STS_ERROR;
660 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
661 x_return_status := G_RET_STS_UNEXP_ERROR;
662 WHEN OTHERS THEN
663 -- store SQL error message on message stack for caller
664 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
665 p_msg_name => G_UNEXPECTED_ERROR,
666 p_token1 => G_SQLCODE_TOKEN,
667 p_token1_value => SQLCODE,
668 p_token2 => G_SQLERRM_TOKEN,
669 p_token2_value => SQLERRM );
670 -- notify caller of an UNEXPECTED error
671 x_return_status := G_RET_STS_UNEXP_ERROR;
672
673 END UPDATE_INVESTOR_REVENUE;
674
675 PROCEDURE DELETE_INVESTOR_REVENUE(p_api_version IN NUMBER,
676 p_init_msg_list IN VARCHAR2,
677 x_return_status OUT NOCOPY VARCHAR2,
678 x_msg_count OUT NOCOPY NUMBER,
679 x_msg_data OUT NOCOPY VARCHAR2,
680 p_szr_tbl IN szr_tbl_type)
681 IS
682 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INVESTOR_REVENUE_TBL';
683 rec_num INTEGER := 0;
684 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
685 l_api_version CONSTANT NUMBER := 1;
686 BEGIN
687
688 FOR rec_num IN 1..p_szr_tbl.COUNT
689 LOOP
690 DELETE_INVESTOR_REVENUE(
691 p_api_version => p_api_version,
692 p_init_msg_list => p_init_msg_list,
693 x_return_status => l_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data,
696 p_szr_rec => p_szr_tbl(rec_num));
697 IF l_return_status = G_RET_STS_ERROR THEN
698 RAISE G_EXCEPTION_ERROR;
699 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
700 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
701 END IF;
702 END LOOP;
703 x_return_status := l_return_status;
704 EXCEPTION
705 WHEN G_EXCEPTION_ERROR THEN
706 x_return_status := G_RET_STS_ERROR;
707 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
708 x_return_status := G_RET_STS_UNEXP_ERROR;
709 WHEN OTHERS THEN
710 -- store SQL error message on message stack for caller
711 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
712 p_msg_name => G_UNEXPECTED_ERROR,
713 p_token1 => G_SQLCODE_TOKEN,
714 p_token1_value => SQLCODE,
715 p_token2 => G_SQLERRM_TOKEN,
716 p_token2_value => SQLERRM );
717 -- notify caller of an UNEXPECTED error
718 x_return_status := G_RET_STS_UNEXP_ERROR;
719
720 END DELETE_INVESTOR_REVENUE;
721
722 END Okl_Sec_Investor_Revenue_Pvt;