[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_INVESTOR_PVT
Source
1 PACKAGE BODY Okl_Sec_Investor_Pvt AS
2 /* $Header: OKLRSZIB.pls 120.5 2008/02/15 05:47:48 gboomina noship $ */
3 /* *********************************************** */
4 --G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
5 --G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
6 G_EXCEPTION_HALT_PROCESSING EXCEPTION;
7 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
8
9
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SEC_PARTIES_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13 l_api_name VARCHAR2(35) := 'SEC_PARTIES';
14
15
16 PROCEDURE migrate_records(
17 p_inv_rec IN inv_rec_type,
18 x_clev_rec OUT NOCOPY clev_rec_type,
19 x_klev_rec OUT NOCOPY klev_rec_type) IS
20
21 l_clev_rec clev_rec_type;
22 l_klev_rec klev_rec_type;
23
24
25 BEGIN
26
27 l_clev_rec.id := p_inv_rec.cle_id;
28 l_clev_rec.lse_id := p_inv_rec.cle_lse_id;
29 l_clev_rec.line_number := p_inv_rec.cle_line_number;
30 --l_clev_rec.sts_code := p_inv_rec.cle_sts_code;
31 l_clev_rec.comments := p_inv_rec.cle_comments;
32 l_clev_rec.date_terminated := p_inv_rec.cle_date_terminated;
33 l_clev_rec.start_date := p_inv_rec.cle_start_date;
34 l_clev_rec.end_date := p_inv_rec.cle_end_date;
35 l_clev_rec.start_date := p_inv_rec.START_DATE;
36 -- akjain, added for Rules Migration
37 l_clev_rec.bill_to_site_use_id := p_inv_rec.bill_to_site_use_id;
38 l_clev_rec.cust_acct_id := p_inv_rec.cust_acct_id;
39
40 l_klev_rec.ID := p_inv_rec.KLE_ID;
41 l_klev_rec.PERCENT_STAKE := p_inv_rec.KLE_PERCENT_STAKE;
42 l_klev_rec.PERCENT := p_inv_rec.KLE_PERCENT;
43 l_klev_rec.EVERGREEN_PERCENT := p_inv_rec.KLE_EVERGREEN_PERCENT;
44 l_klev_rec.AMOUNT_STAKE := p_inv_rec.KLE_AMOUNT_STAKE;
45 l_klev_rec.DATE_SOLD := p_inv_rec.KLE_DATE_SOLD;
46 l_klev_rec.DELIVERED_DATE := p_inv_rec.KLE_DELIVERED_DATE;
47 l_klev_rec.AMOUNT := p_inv_rec.KLE_AMOUNT;
48 l_klev_rec.DATE_FUNDING := p_inv_rec.KLE_DATE_FUNDING;
49 l_klev_rec.DATE_FUNDING_REQUIRED := p_inv_rec.KLE_DATE_FUNDING_REQUIRED;
50 l_klev_rec.DATE_ACCEPTED := p_inv_rec.KLE_DATE_ACCEPTED;
51 l_klev_rec.DATE_DELIVERY_EXPECTED := p_inv_rec.KLE_DATE_DELIVERY_EXPECTED;
52 l_klev_rec.CAPITAL_AMOUNT := p_inv_rec.KLE_CAPITAL_AMOUNT;
53
54 l_klev_rec.DATE_PAY_INVESTOR_START := p_inv_rec.DATE_PAY_INVESTOR_START;
55 l_klev_rec.PAY_INVESTOR_FREQUENCY := p_inv_rec.PAY_INVESTOR_FREQUENCY;
56 l_klev_rec.PAY_INVESTOR_EVENT := p_inv_rec.PAY_INVESTOR_EVENT;
57 l_klev_rec.PAY_INVESTOR_REMITTANCE_DAYS := p_inv_rec.PAY_INVESTOR_REMITTANCE_DAYS;
58
59 x_clev_rec := l_clev_rec;
60 x_klev_rec := l_klev_rec;
61
62 END migrate_records;
63
64
65
66
67 PROCEDURE migrate_records(
68 p_inv_rec IN inv_rec_type,
69 x_cplv_rec OUT NOCOPY cplv_rec_type,
70 x_clev_rec OUT NOCOPY clev_rec_type,
71 x_klev_rec OUT NOCOPY klev_rec_type) IS
72
73 l_cplv_rec cplv_rec_type;
74 lx_clev_rec clev_rec_type;
75 lx_klev_rec klev_rec_type;
76
77 BEGIN
78
79 l_cplv_rec.id := p_inv_rec.cpl_id;
80 l_cplv_rec.chr_id := p_inv_rec.cpl_chr_id;
81 l_cplv_rec.cle_id := p_inv_rec.cpl_cle_id;
82 l_cplv_rec.rle_code := p_inv_rec.cpl_rle_code;
83 l_cplv_rec.dnz_chr_id := p_inv_rec.cpl_dnz_chr_id;
84
85
86 --dbms_output.put_line('p_inv_rec.cpl_dnz_chr_id'||p_inv_rec.cpl_dnz_chr_id);
87
88 l_cplv_rec.object1_id1 := p_inv_rec.cpl_object1_id1;
89 l_cplv_rec.object1_id2 := p_inv_rec.cpl_object1_id2;
90 l_cplv_rec.jtot_object1_code := p_inv_rec.cpl_jtot_object1_code;
91
92 --insert into okl_sec_temp values (p_inv_rec.cpl_chr_id, 'migrate_records. '||p_inv_rec.cpl_object1_id1||p_inv_rec.cpl_object1_id2);
93
94
95 migrate_records(
96 p_inv_rec => p_inv_rec,
97 x_clev_rec => lx_clev_rec,
98 x_klev_rec => lx_klev_rec);
99
100
101 x_cplv_rec := l_cplv_rec;
102 x_clev_Rec := lx_clev_rec;
103 x_klev_rec := lx_klev_rec;
104
105 END migrate_records;
106
107
108 FUNCTION check_parties(
109 p_api_version IN NUMBER,
110 p_init_msg_list IN VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2,
114 p_cplv_rec IN cplv_rec_type)
115 RETURN BOOLEAN IS
116
117 CURSOR PARTIES_CSR
118 ( p_chr_id IN NUMBER,
119 p_party_id1 IN VARCHAR2) IS
120 SELECT 'x'
121 FROM okl_sec_investors_uv
122 WHERE chr_id = p_chr_id
123 AND id1 = p_party_id1;
124
125 l_fetched BOOLEAN := FALSE;
126 l_temp VARCHAR2(1);
127
128
129 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
130 l_api_name CONSTANT VARCHAR2(30) := 'check_parties';
131 l_api_version CONSTANT NUMBER := 1.0;
132
133 i NUMBER := 0;
134
135 BEGIN
136
137 --insert into okl_sec_temp values (p_cplv_rec.dnz_chr_id, 'in count parties '||p_cplv_rec.object1_id1);
138
139 OPEN PARTIES_CSR(p_cplv_rec.dnz_chr_id,
140 p_cplv_rec.object1_id1);
141
142 FETCH PARTIES_CSR INTO l_temp;
143
144 --insert into okl_sec_temp values (0, 'l_fetched '||l_fetched);
145 IF PARTIES_CSR%NOTFOUND THEN
146 l_fetched := FALSE;
147 ELSE
148 l_fetched := TRUE;
149 END IF;
150
151 --insert into okl_sec_temp values (0, 'l_fetched '||l_fetched);
152
153 CLOSE PARTIES_CSR;
154 x_return_status := 'S';
155 RETURN l_fetched;
156
157 EXCEPTION
158 WHEN OKC_API.G_EXCEPTION_ERROR THEN
159 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
160 p_api_name => l_api_name,
161 p_pkg_name => G_PKG_NAME,
162 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
163 x_msg_count => x_msg_count,
164 x_msg_data => x_msg_data,
165 p_api_type => G_API_TYPE);
166
167 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
168 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
169 p_api_name => l_api_name,
170 p_pkg_name => G_PKG_NAME,
171 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
172 x_msg_count => x_msg_count,
173 x_msg_data => x_msg_data,
174 p_api_type => G_API_TYPE);
175
176 WHEN OTHERS THEN
177 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
178 p_api_name => l_api_name,
179 p_pkg_name => G_PKG_NAME,
180 p_exc_name => 'OTHERS',
181 x_msg_count => x_msg_count,
182 x_msg_data => x_msg_data,
183 p_api_type => G_API_TYPE);
184
185
186 END check_parties;
187
188
189 FUNCTION get_lse_id(
190 p_api_version IN NUMBER,
191 p_init_msg_list IN VARCHAR2,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 p_lty_code IN VARCHAR2)
196 RETURN NUMBER IS
197
198 CURSOR LINE_STYLES_CSR
199 (p_lty_code IN VARCHAR2) IS
200 SELECT ID
201 FROM okc_line_styles_v
202 WHERE lty_code = p_lty_code;
203
204
205 l_fetched BOOLEAN := FALSE;
206 l_lse_id NUMBER;
207
208 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
209 l_api_name CONSTANT VARCHAR2(30) := 'check_parties';
210 l_api_version CONSTANT NUMBER := 1.0;
211
212 BEGIN
213
214 OPEN LINE_STYLES_CSR(p_lty_code);
215
216 FETCH LINE_STYLES_CSR INTO l_lse_id;
217 CLOSE LINE_STYLES_CSR;
218
219 x_return_status := 'S';
220
221 RETURN l_lse_id;
222
223 EXCEPTION
224 WHEN OKC_API.G_EXCEPTION_ERROR THEN
225 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
226 p_api_name => l_api_name,
227 p_pkg_name => G_PKG_NAME,
228 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
229 x_msg_count => x_msg_count,
230 x_msg_data => x_msg_data,
231 p_api_type => G_API_TYPE);
232
233 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
234 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
235 p_api_name => l_api_name,
236 p_pkg_name => G_PKG_NAME,
237 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
238 x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data,
240 p_api_type => G_API_TYPE);
241
242 WHEN OTHERS THEN
243 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
244 p_api_name => l_api_name,
245 p_pkg_name => G_PKG_NAME,
246 p_exc_name => 'OTHERS',
247 x_msg_count => x_msg_count,
248 x_msg_data => x_msg_data,
249 p_api_type => G_API_TYPE);
250
251 END get_lse_id;
252
253
254 PROCEDURE get_header_details(
255 p_api_version IN NUMBER,
256 p_init_msg_list IN VARCHAR2,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2,
260 p_chr_id IN NUMBER,
261 x_currency_code OUT NOCOPY VARCHAR2,
262 x_org_id OUT NOCOPY VARCHAR2,
263 x_end_date OUT NOCOPY DATE)
264 IS
265
266 CURSOR CURRENCY_CSR
267 (p_chr_id IN VARCHAR2) IS
268 SELECT CURRENCY_CODE,
269 AUTHORING_ORG_ID,
270 END_DATE
271 FROM okc_k_headers_b
272 WHERE id = p_chr_id;
273
274
275 l_fetched BOOLEAN := FALSE;
276 l_currency_code VARCHAR2(15);
277 l_org_id VARCHAR2(15);
278 l_end_date DATE;
279
280 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
281 l_api_name CONSTANT VARCHAR2(30) := 'GET_HEADER_DETAILS';
282 l_api_version CONSTANT NUMBER := 1.0;
283
284 BEGIN
285
286 OPEN CURRENCY_CSR(p_chr_id);
287
288 FETCH CURRENCY_CSR INTO l_currency_code, l_org_id, l_end_date;
289 CLOSE CURRENCY_CSR;
290
291 x_currency_code := l_currency_code;
292 x_org_id := l_org_id;
293 x_end_date := l_end_date;
294
295 x_return_status := 'S';
296
297
298 EXCEPTION
299 WHEN OKC_API.G_EXCEPTION_ERROR THEN
300 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
301 p_api_name => l_api_name,
302 p_pkg_name => G_PKG_NAME,
303 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data,
306 p_api_type => G_API_TYPE);
307
308 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
309 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
310 p_api_name => l_api_name,
311 p_pkg_name => G_PKG_NAME,
312 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
313 x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data,
315 p_api_type => G_API_TYPE);
316
317 WHEN OTHERS THEN
318 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
319 p_api_name => l_api_name,
320 p_pkg_name => G_PKG_NAME,
321 p_exc_name => 'OTHERS',
322 x_msg_count => x_msg_count,
323 x_msg_data => x_msg_data,
324 p_api_type => G_API_TYPE);
325
326 END get_header_details;
327
328
329 PROCEDURE validate_investor(
330 p_api_version IN NUMBER,
331 p_init_msg_list IN VARCHAR2,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_msg_count OUT NOCOPY NUMBER,
334 x_msg_data OUT NOCOPY VARCHAR2,
335 p_inv_tbl IN inv_tbl_type) IS
336
337
338 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
339 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_INVESTOR';
340 l_api_version CONSTANT NUMBER := 1.0;
341
342 i NUMBER := 0;
343 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
344
345
346 BEGIN
347
348 -- dbms_output.put_line('begin ');
349 x_return_status := OKC_API.G_RET_STS_SUCCESS;
350 -- call START_ACTIVITY to create savepoint, check compatibility
351 -- and initialize message list
352 x_return_status := OKC_API.START_ACTIVITY(
353 p_api_name => l_api_name,
354 p_pkg_name => G_PKG_NAME,
355 p_init_msg_list => p_init_msg_list,
356 l_api_version => l_api_version,
357 p_api_version => p_api_version,
358 p_api_type => G_API_TYPE,
359 x_return_status => x_return_status);
360
361 -- check if activity started successfully
362 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
363 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
364 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
365 RAISE OKC_API.G_EXCEPTION_ERROR;
366 END IF;
367
368
369 LOOP
370 i := i + 1;
371
372 --dbms_output.put_line('begin 1');
373
374 IF(p_inv_tbl(i).cpl_object1_id1 IS NULL OR
375 p_inv_tbl(i).cpl_object1_id1 = '' OR
376 p_inv_tbl(i).cpl_object1_id1 = OKC_API.G_MISS_CHAR) THEN
377
378 x_return_status := OKC_API.g_ret_sts_error;
379 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
380 p_region_code => G_AK_REGION_NAME,
381 p_attribute_code => 'OKL_LA_SEC_INVESTOR');
382 OKC_API.SET_MESSAGE( p_app_name => g_app_name
383 , p_msg_name => 'OKL_REQUIRED_VALUE'
384 , p_token1 => 'COL_NAME'
385 , p_token1_value => l_ak_prompt
386 );
387
388 RAISE OKC_API.G_EXCEPTION_ERROR;
389 END IF;
390
391 --dbms_output.put_line('begin 2');
392
393 IF(p_inv_tbl(i).kle_amount IS NULL OR
394 p_inv_tbl(i).kle_amount = '' OR
395 p_inv_tbl(i).kle_amount = OKC_API.G_MISS_NUM) THEN
396
397 x_return_status := OKC_API.g_ret_sts_error;
398 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
399 p_region_code => G_AK_REGION_NAME,
400 p_attribute_code => 'OKL_LA_SEC_AMT_STAKE');
401 OKC_API.SET_MESSAGE( p_app_name => g_app_name
402 , p_msg_name => 'OKL_REQUIRED_VALUE'
403 , p_token1 => 'COL_NAME'
404 , p_token1_value => l_ak_prompt
405 );
406 RAISE OKC_API.G_EXCEPTION_ERROR;
407 END IF;
408
409 --dbms_output.put_line('begin 3');
410 IF(p_inv_tbl(i).start_date IS NULL OR
411 p_inv_tbl(i).start_date = '' OR
412 p_inv_tbl(i).start_date = OKC_API.G_MISS_DATE) THEN
413
414 x_return_status := OKC_API.g_ret_sts_error;
415 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
416 p_region_code => G_AK_REGION_NAME,
417 p_attribute_code => 'OKL_LA_SEC_INV_DATE');
418
419 OKC_API.SET_MESSAGE( p_app_name => g_app_name
420 , p_msg_name => 'OKL_REQUIRED_VALUE'
421 , p_token1 => 'COL_NAME'
422 , p_token1_value => l_ak_prompt
423 );
424 RAISE OKC_API.G_EXCEPTION_ERROR;
425 END IF;
426
427 IF(p_inv_tbl(i).PAY_INVESTOR_FREQUENCY IS NULL OR
428 p_inv_tbl(i).PAY_INVESTOR_FREQUENCY = '' OR
429 p_inv_tbl(i).PAY_INVESTOR_FREQUENCY = OKC_API.G_MISS_CHAR) THEN
430
431 x_return_status := OKC_API.g_ret_sts_error;
432 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
433 p_region_code => G_AK_REGION_NAME,
434 p_attribute_code => 'OKL_LA_SEC_PAY_FREQ');
435 OKC_API.SET_MESSAGE( p_app_name => g_app_name
436 , p_msg_name => 'OKL_REQUIRED_VALUE'
437 , p_token1 => 'COL_NAME'
438 , p_token1_value => l_ak_prompt
439 );
440 RAISE OKC_API.G_EXCEPTION_ERROR;
441 END IF;
442
443 IF(p_inv_tbl(i).date_pay_investor_start IS NULL OR
444 p_inv_tbl(i).date_pay_investor_start = '' OR
445 p_inv_tbl(i).date_pay_investor_start = OKC_API.G_MISS_DATE) THEN
446
447 x_return_status := OKC_API.g_ret_sts_error;
448 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
449 p_region_code => G_AK_REGION_NAME,
450 p_attribute_code => 'OKL_LA_SEC_PAY_START');
451 OKC_API.SET_MESSAGE( p_app_name => g_app_name
452 , p_msg_name => 'OKL_REQUIRED_VALUE'
453 , p_token1 => 'COL_NAME'
454 , p_token1_value => l_ak_prompt
455 );
456 RAISE OKC_API.G_EXCEPTION_ERROR;
457 -- mvasudev, 02/06/2004
458 ELSIF p_inv_tbl(i).date_pay_investor_start < p_inv_tbl(i).start_date
459 THEN
460 x_return_status := OKC_API.g_ret_sts_error;
461 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME
462 ,p_msg_name => 'OKL_SEC_INVALID_PAYOUT_DATE');
463 RAISE OKC_API.G_EXCEPTION_ERROR;
464 END IF;
465
466 IF(p_inv_tbl(i).PAY_INVESTOR_EVENT IS NULL OR
467 p_inv_tbl(i).PAY_INVESTOR_EVENT = '' OR
468 p_inv_tbl(i).PAY_INVESTOR_EVENT = OKC_API.G_MISS_CHAR) THEN
469
470 x_return_status := OKC_API.g_ret_sts_error;
471 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
472 p_region_code => G_AK_REGION_NAME,
473 p_attribute_code => 'OKL_LA_SEC_PAY_EVENT');
474 OKC_API.SET_MESSAGE( p_app_name => g_app_name
475 , p_msg_name => 'OKL_REQUIRED_VALUE'
476 , p_token1 => 'COL_NAME'
477 , p_token1_value => l_ak_prompt
478 );
479 RAISE OKC_API.G_EXCEPTION_ERROR;
480 END IF;
481
482
483 IF(p_inv_tbl(i).PAY_INVESTOR_REMITTANCE_DAYS IS NULL OR
484 p_inv_tbl(i).PAY_INVESTOR_REMITTANCE_DAYS = '' OR
485 p_inv_tbl(i).PAY_INVESTOR_REMITTANCE_DAYS = OKC_API.G_MISS_NUM) THEN
486
487 x_return_status := OKC_API.g_ret_sts_error;
488 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
489 p_region_code => G_AK_REGION_NAME,
490 p_attribute_code => 'OKL_LA_SEC_REMIT');
491 OKC_API.SET_MESSAGE( p_app_name => g_app_name
492 , p_msg_name => 'OKL_REQUIRED_VALUE'
493 , p_token1 => 'COL_NAME'
494 , p_token1_value => l_ak_prompt
495 );
496 RAISE OKC_API.G_EXCEPTION_ERROR;
497 END IF;
498
499 IF(p_inv_tbl(i).BILL_TO_SITE_USE_ID IS NULL OR
500 p_inv_tbl(i).BILL_TO_SITE_USE_ID = '' OR
501 p_inv_tbl(i).BILL_TO_SITE_USE_ID = OKC_API.G_MISS_NUM) THEN
502
503 x_return_status := OKC_API.g_ret_sts_error;
504 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
505 p_region_code => G_AK_REGION_NAME,
506 p_attribute_code => 'OKL_LA_SEC_INV_BILL_TO');
507
508 OKC_API.SET_MESSAGE( p_app_name => g_app_name
509 , p_msg_name => 'OKL_REQUIRED_VALUE'
510 , p_token1 => 'COL_NAME'
511 , p_token1_value => l_ak_prompt
512 );
513 RAISE OKC_API.G_EXCEPTION_ERROR;
514 END IF;
515
516 IF(p_inv_tbl(i).CUST_ACCT_ID IS NULL OR
517 p_inv_tbl(i).CUST_ACCT_ID = '' OR
518 p_inv_tbl(i).CUST_ACCT_ID = OKC_API.G_MISS_NUM) THEN
519
520 x_return_status := OKC_API.g_ret_sts_error;
521 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
522 p_region_code => G_AK_REGION_NAME,
523 p_attribute_code => 'OKL_LA_SEC_INV_CUST_ACCOUNT');
524
525 OKC_API.SET_MESSAGE( p_app_name => g_app_name
526 , p_msg_name => 'OKL_REQUIRED_VALUE'
527 , p_token1 => 'COL_NAME'
528 , p_token1_value => l_ak_prompt
529 );
530 RAISE OKC_API.G_EXCEPTION_ERROR;
531 END IF;
532
533 EXIT WHEN (i >= p_inv_tbl.last);
534 END LOOP;
535
536 --Call End Activity
537 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
538 x_msg_data => x_msg_data);
539
540
541 EXCEPTION
542 WHEN OKC_API.G_EXCEPTION_ERROR THEN
543 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
544 p_api_name => l_api_name,
545 p_pkg_name => G_PKG_NAME,
546 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 p_api_type => G_API_TYPE);
550
551 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
552 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
553 p_api_name => l_api_name,
554 p_pkg_name => G_PKG_NAME,
555 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
556 x_msg_count => x_msg_count,
557 x_msg_data => x_msg_data,
558 p_api_type => G_API_TYPE);
559
560 WHEN OTHERS THEN
561 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
562 p_api_name => l_api_name,
563 p_pkg_name => G_PKG_NAME,
564 p_exc_name => 'OTHERS',
565 x_msg_count => x_msg_count,
566 x_msg_data => x_msg_data,
567 p_api_type => G_API_TYPE);
568
569
570 END validate_investor;
571
572
573
574 PROCEDURE create_investor(
575 p_api_version IN NUMBER,
576 p_init_msg_list IN VARCHAR2,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_msg_count OUT NOCOPY NUMBER,
579 x_msg_data OUT NOCOPY VARCHAR2,
580 p_inv_tbl IN inv_tbl_type,
581 x_inv_tbl OUT NOCOPY inv_tbl_type) IS
582
583 l_cplv_rec cplv_rec_type;
584 l_clev_rec clev_rec_type;
585 l_klev_rec klev_rec_type;
586
587 lx_cplv_rec cplv_rec_type;
588 lx_clev_rec clev_rec_type;
589 lx_klev_rec klev_rec_type;
590
591
592 l_inv_rec inv_rec_type;
593
594 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
595 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONTRACT_PARTY';
596 l_api_version CONSTANT NUMBER := 1.0;
597
598 i NUMBER := 0;
599 l_exists BOOLEAN := FALSE;
600 lse_id NUMBER;
601 lx_currency_code VARCHAR2(15) := '';
602 lx_org_id VARCHAR2(15) := '';
603 lx_end_date DATE;
604
605 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
606
607 BEGIN
608
609 --dbms_output.put_line('begin .......... ..');
610 x_return_status := OKC_API.G_RET_STS_SUCCESS;
611 -- call START_ACTIVITY to create savepoint, check compatibility
612 -- and initialize message list
613 x_return_status := OKC_API.START_ACTIVITY(
614 p_api_name => l_api_name,
615 p_pkg_name => G_PKG_NAME,
616 p_init_msg_list => p_init_msg_list,
617 l_api_version => l_api_version,
618 p_api_version => p_api_version,
619 p_api_type => G_API_TYPE,
620 x_return_status => x_return_status);
621
622 -- check if activity started successfully
623 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
624 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
625 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
626 RAISE OKC_API.G_EXCEPTION_ERROR;
627 END IF;
628
629 --delete from okl_sec_temp;
630
631 validate_investor(
632 p_api_version => p_api_version,
633 p_init_msg_list => p_init_msg_list,
634 x_return_status => x_return_status,
635 x_msg_count => x_msg_count,
636 x_msg_data => x_msg_data,
637 p_inv_tbl => p_inv_tbl);
638
639 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
640 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
641 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
642 RAISE OKC_API.G_EXCEPTION_ERROR;
643 END IF;
644
645
646 lse_id := get_lse_id(
647 p_api_version => p_api_version,
648 p_init_msg_list => p_init_msg_list,
649 x_return_status => x_return_status,
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data,
652 p_lty_code => G_TOPLINE_LTY_CODE);
653
654 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
655 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
656 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
657 RAISE OKC_API.G_EXCEPTION_ERROR;
658 END IF;
659
660 IF(p_inv_tbl.COUNT > 0) THEN
661
662 get_header_details(
663 p_api_version => p_api_version,
664 p_init_msg_list => p_init_msg_list,
665 x_return_status => x_return_status,
666 x_msg_count => x_msg_count,
667 x_msg_data => x_msg_data,
668 p_chr_id => p_inv_tbl(1).cpl_dnz_chr_id,
669 x_currency_code => lx_currency_code,
670 x_org_id => lx_org_id,
671 x_end_date => lx_end_date);
672
673 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
674 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
675 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
676 RAISE OKC_API.G_EXCEPTION_ERROR;
677 END IF;
678
679 --dbms_output.put_line('lx_currency_code '||lx_currency_code);
680 --dbms_output.put_line('lx_end_date '||lx_end_date);
681 END IF;
682
683
684 LOOP
685 i := i + 1;
686
687 migrate_records(
688 p_inv_rec => p_inv_tbl(i),
689 x_cplv_rec => l_cplv_rec,
690 x_clev_rec => l_clev_rec,
691 x_klev_rec => l_klev_rec);
692
693 --insert into okl_sec_temp values (l_cplv_rec.dnz_chr_id, 'create ');
694 --insert into okl_sec_temp values (l_cplv_rec.dnz_chr_id, 'before count '||l_cplv_rec.object1_id1||l_cplv_rec.object1_id2);
695
696 l_exists := check_parties(
697 p_api_version => p_api_version,
698 p_init_msg_list => p_init_msg_list,
699 x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_cplv_rec => l_cplv_rec);
703
704
705 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
706 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
707 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
708 RAISE OKL_API.G_EXCEPTION_ERROR;
709 END IF;
710
711
712 --dbms_output.put_line('l_count '||l_count);
713
714 IF(l_exists) THEN
715 x_return_status := OKC_API.g_ret_sts_error;
716 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
717 p_region_code => G_AK_REGION_NAME,
718 p_attribute_code => 'OKL_LA_SEC_INVESTOR');
719 OKC_API.SET_MESSAGE( p_app_name => g_app_name
720 , p_msg_name => 'OKL_DUP_PARTY'
721 , p_token1 => 'COL_NAME'
722 , p_token1_value => l_ak_prompt
723 );
724 RAISE OKC_API.G_EXCEPTION_ERROR;
725 END IF;
726
727
728 l_clev_rec.dnz_chr_id := l_cplv_rec.dnz_chr_id;
729 l_clev_rec.chr_id := l_cplv_rec.dnz_chr_id;
730 l_clev_rec.lse_id := lse_id;
731 l_clev_rec.display_sequence := 1;
732 l_clev_rec.exception_yn := 'N';
733 l_clev_rec.sts_code := 'NEW';
734
735 l_clev_rec.currency_code := lx_currency_code;
736 l_clev_rec.end_date := lx_end_date;
737
738
739 -- insert into okl_sec_temp values ('1 ',l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
740
741 l_klev_rec.AMOUNT := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(p_amount => TO_NUMBER(l_klev_rec.AMOUNT),
742 p_currency_code => lx_currency_code);
743
744 -- insert into okl_sec_temp values ('2 ', l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
745
746 --dbms_output.put_line('l_cplv_rec.dnz_chr_id '||l_cplv_rec.dnz_chr_id);
747
748
749 OKL_CONTRACT_PUB.create_contract_line(
750 p_api_version => p_api_version,
751 p_init_msg_list => p_init_msg_list,
752 x_return_status => x_return_status,
753 x_msg_count => x_msg_count,
754 x_msg_data => x_msg_data,
755 p_clev_rec => l_clev_rec,
756 p_klev_rec => l_klev_rec,
757 x_clev_rec => lx_clev_rec,
758 x_klev_rec => lx_klev_rec);
759
760 l_cplv_rec.cle_id := lx_clev_rec.id;
761
762 --dbms_output.put_line('lx_clev_rec.id '||lx_clev_rec.id);
763
764 --dbms_output.put_line('x_return_status '||x_return_status);
765 --dbms_output.put_line('x_msg_count '||x_msg_count);
766
767 x_inv_tbl(1).cle_id := lx_clev_rec.id;
768 x_inv_tbl(1).cle_sts_code := lx_clev_rec.sts_code;
769 --x_inv_tbl(1).description :=
770
771 -- insert into okl_sec_temp values ('3 ',l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
772
773 x_inv_tbl(1).description := OKL_ACCOUNTING_UTIL.format_amount(p_amount => TO_NUMBER(l_klev_rec.AMOUNT),
774 p_currency_code => lx_currency_code);
775
776 -- insert into okl_sec_temp values ('4 ',l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
777
778
779 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
780 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
781 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
782 RAISE OKL_API.G_EXCEPTION_ERROR;
783 END IF;
784
785 l_cplv_rec.rle_code := G_INVESTOR_RLE_CODE;
786 l_cplv_rec.jtot_object1_code := G_INVESTOR_OBJECT_CODE;
787
788
789 OKL_OKC_MIGRATION_PVT.create_k_party_role(p_api_version,
790 p_init_msg_list,
791 x_return_status,
792 x_msg_count,
793 x_msg_data,
794 l_cplv_rec,
795 lx_cplv_rec);
796
797 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
798 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
799 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
800 RAISE OKL_API.G_EXCEPTION_ERROR;
801 END IF;
802 EXIT WHEN (i >= p_inv_tbl.last);
803 END LOOP;
804
805 --Call End Activity
806 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
807 x_msg_data => x_msg_data);
808
809
810 EXCEPTION
811 WHEN OKC_API.G_EXCEPTION_ERROR THEN
812 -- insert into okl_sec_temp values (751, 'here ');
813 --COMMIT;
814 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
815 p_api_name => l_api_name,
816 p_pkg_name => G_PKG_NAME,
817 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
818 x_msg_count => x_msg_count,
819 x_msg_data => x_msg_data,
820 p_api_type => G_API_TYPE);
821
822 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
823 -- COMMIT;
824 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
825 p_api_name => l_api_name,
826 p_pkg_name => G_PKG_NAME,
827 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
828 x_msg_count => x_msg_count,
829 x_msg_data => x_msg_data,
830 p_api_type => G_API_TYPE);
831
832 WHEN OTHERS THEN
833 -- COMMIT;
834 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
835 p_api_name => l_api_name,
836 p_pkg_name => G_PKG_NAME,
837 p_exc_name => 'OTHERS',
838 x_msg_count => x_msg_count,
839 x_msg_data => x_msg_data,
840 p_api_type => G_API_TYPE);
841
842
843 END create_investor;
844
845
846
847 PROCEDURE update_investor(
848 p_api_version IN NUMBER,
849 p_init_msg_list IN VARCHAR2,
850 x_return_status OUT NOCOPY VARCHAR2,
851 x_msg_count OUT NOCOPY NUMBER,
852 x_msg_data OUT NOCOPY VARCHAR2,
853 p_inv_tbl IN inv_tbl_type,
854 x_inv_tbl OUT NOCOPY inv_tbl_type) IS
855
856 -- Cursor for getting the status of the open transaction
857 CURSOR l_trans_status_csr(p_ia_id IN NUMBER)
858 IS
859 SELECT pools.transaction_status,pools.id,pools.pol_id FROM OKL_POOL_TRANSACTIONS pools,OKL_POOLS header
860 where pools.transaction_status <> 'COMPLETE'
861 and pools.transaction_type='ADD' and pools.transaction_reason='ADJUSTMENTS'
862 and pools.pol_id=header.id and header.khr_id=p_ia_id ;
863
864
865 l_cplv_rec cplv_rec_type;
866 l_clev_rec clev_rec_type;
867 l_klev_rec klev_rec_type;
868
869 lx_cplv_rec cplv_rec_type;
870 lx_clev_rec clev_rec_type;
871 lx_klev_rec klev_rec_type;
872 l_transaction_status VARCHAR2(30);
873 l_trx_id NUMBER;
874 l_pol_id NUMBER;
875
876 l_inv_rec inv_rec_type;
877
878 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
879 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_PARTY';
880 l_api_version CONSTANT NUMBER := 1.0;
881
882 i NUMBER := 0;
883 l_count NUMBER := 0;
884 lse_id NUMBER;
885
886 lx_currency_code VARCHAR2(15) := '';
887 lx_org_id VARCHAR2(15) := '';
888 lx_end_date DATE;
889
890 lp_poxv_rec OKL_POX_PVT.poxv_rec_type;
891 lx_poxv_rec OKL_POX_PVT.poxv_rec_type;
892
893 BEGIN
894
895
896 x_return_status := OKC_API.G_RET_STS_SUCCESS;
897 -- call START_ACTIVITY to create savepoint, check compatibility
898 -- and initialize message list
899 x_return_status := OKC_API.START_ACTIVITY(
900 p_api_name => l_api_name,
901 p_pkg_name => G_PKG_NAME,
902 p_init_msg_list => p_init_msg_list,
903 l_api_version => l_api_version,
904 p_api_version => p_api_version,
905 p_api_type => G_API_TYPE,
906 x_return_status => x_return_status);
907
908 -- check if activity started successfully
909 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
910 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
911 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
912 RAISE OKC_API.G_EXCEPTION_ERROR;
913 END IF;
914
915 validate_investor(
916 p_api_version => p_api_version,
917 p_init_msg_list => p_init_msg_list,
918 x_return_status => x_return_status,
919 x_msg_count => x_msg_count,
920 x_msg_data => x_msg_data,
921 p_inv_tbl => p_inv_tbl);
922
923 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
924 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
925 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
926 RAISE OKC_API.G_EXCEPTION_ERROR;
927 END IF;
928
929 -- insert into okl_sec_temp values ('u-1 ',0, lx_currency_code, lx_org_id, '');
930
931 get_header_details(
932 p_api_version => p_api_version,
933 p_init_msg_list => p_init_msg_list,
934 x_return_status => x_return_status,
935 x_msg_count => x_msg_count,
936 x_msg_data => x_msg_data,
937 p_chr_id => p_inv_tbl(1).cpl_dnz_chr_id,
938 x_currency_code => lx_currency_code,
939 x_org_id => lx_org_id,
940 x_end_date => lx_end_date);
941
942 -- insert into okl_sec_temp values ('u-2 ',0, lx_currency_code, lx_org_id, '');
943
944 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
945 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
946 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
947 RAISE OKC_API.G_EXCEPTION_ERROR;
948 END IF;
949
950 -- insert into okl_sec_temp values ('u-3 ',0, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
951 LOOP
952 i := i + 1;
953
954 migrate_records(
955 p_inv_rec => p_inv_tbl(i),
956 x_cplv_rec => l_cplv_rec,
957 x_clev_rec => l_clev_rec,
958 x_klev_rec => l_klev_rec);
959
960
961 --dbms_output.put_line('l_cplv_rec.dnz_chr_id '||l_cplv_rec.dnz_chr_id);
962
963 l_clev_rec.currency_code := lx_currency_code;
964 l_clev_rec.end_date := lx_end_date;
965
966
967 -- insert into okl_sec_temp values ('u1 ',l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
968
969
970 l_klev_rec.AMOUNT := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(p_amount => TO_NUMBER(l_klev_rec.AMOUNT),
971 p_currency_code => lx_currency_code);
972
973 --insert into okl_sec_temp values ('u2 ', l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
974
975
976
977 OKL_CONTRACT_PUB.update_contract_line(
978 p_api_version => p_api_version,
979 p_init_msg_list => p_init_msg_list,
980 x_return_status => x_return_status,
981 x_msg_count => x_msg_count,
982 x_msg_data => x_msg_data,
983 p_clev_rec => l_clev_rec,
984 p_klev_rec => l_klev_rec,
985 x_clev_rec => lx_clev_rec,
986 x_klev_rec => lx_klev_rec);
987
988 l_cplv_rec.cle_id := lx_clev_rec.id;
989
990 --dbms_output.put_line('lx_clev_rec.id '||lx_clev_rec.id);
991
992 --dbms_output.put_line('x_return_status '||x_return_status);
993 --dbms_output.put_line('x_msg_count '||x_msg_count);
994
995 x_inv_tbl(1).cle_id := lx_clev_rec.id;
996
997
998 x_inv_tbl(1).cle_sts_code := lx_clev_rec.sts_code;
999 --x_inv_tbl(1).description :=
1000
1001 -- insert into okl_sec_temp values ('u3 ',l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, to_char(l_klev_rec.AMOUNT));
1002
1003 x_inv_tbl(1).description := OKL_ACCOUNTING_UTIL.format_amount(p_amount => TO_NUMBER(l_klev_rec.AMOUNT),
1004 p_currency_code => lx_currency_code);
1005
1006 -- insert into okl_sec_temp values ('u4 ',l_cplv_rec.dnz_chr_id, lx_currency_code, lx_org_id, x_inv_tbl(1).description);
1007
1008
1009 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1010 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1012 RAISE OKL_API.G_EXCEPTION_ERROR;
1013 END IF;
1014
1015 EXIT WHEN (i >= p_inv_tbl.last);
1016 END LOOP;
1017 -- get existing the transaction status
1018 OPEN l_trans_status_csr(p_inv_tbl(1).cpl_dnz_chr_id);
1019 FETCH l_trans_status_csr INTO l_transaction_status,l_trx_id,l_pol_id;
1020 CLOSE l_trans_status_csr;
1021
1022 IF l_transaction_status = 'APPROVAL_REJECTED' THEN
1023 lp_poxv_rec.TRANSACTION_STATUS := 'INCOMPLETE';
1024 lp_poxv_rec.POL_ID := l_pol_id;
1025 lp_poxv_rec.ID := l_trx_id;
1026
1027 -- create ADD transaction for Adjustment
1028 Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
1029 ,p_init_msg_list => p_init_msg_list
1030 ,x_return_status => l_return_status
1031 ,x_msg_count => x_msg_count
1032 ,x_msg_data => x_msg_data
1033 ,p_poxv_rec => lp_poxv_rec
1034 ,x_poxv_rec => lx_poxv_rec);
1035
1036 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1037 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1038 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1039 RAISE Okl_Api.G_EXCEPTION_ERROR;
1040 END IF;
1041 END IF;
1042
1043 --Call End Activity
1044 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1045 x_msg_data => x_msg_data);
1046
1047
1048 EXCEPTION
1049 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1050 -- commit;
1051 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1052 p_api_name => l_api_name,
1053 p_pkg_name => G_PKG_NAME,
1054 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1055 x_msg_count => x_msg_count,
1056 x_msg_data => x_msg_data,
1057 p_api_type => G_API_TYPE);
1058
1059 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1060 -- commit;
1061 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1062 p_api_name => l_api_name,
1063 p_pkg_name => G_PKG_NAME,
1064 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1065 x_msg_count => x_msg_count,
1066 x_msg_data => x_msg_data,
1067 p_api_type => G_API_TYPE);
1068
1069 WHEN OTHERS THEN
1070 -- commit;
1071 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1072 p_api_name => l_api_name,
1073 p_pkg_name => G_PKG_NAME,
1074 p_exc_name => 'OTHERS',
1075 x_msg_count => x_msg_count,
1076 x_msg_data => x_msg_data,
1077 p_api_type => G_API_TYPE);
1078
1079
1080 END update_investor;
1081
1082
1083 PROCEDURE delete_investor(
1084 p_api_version IN NUMBER,
1085 p_init_msg_list IN VARCHAR2,
1086 x_return_status OUT NOCOPY VARCHAR2,
1087 x_msg_count OUT NOCOPY NUMBER,
1088 x_msg_data OUT NOCOPY VARCHAR2,
1089 p_inv_tbl IN inv_tbl_type) IS
1090
1091 l_clev_rec clev_rec_type;
1092 l_klev_rec klev_rec_type;
1093
1094 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1095 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_PARTY';
1096 l_api_version CONSTANT NUMBER := 1.0;
1097
1098 i NUMBER := 0;
1099 l_count NUMBER := 0;
1100 lse_id NUMBER;
1101
1102 -- gboomina Bug 6814331 - Start
1103 -- Cursor to get Fee lines defined for an Investor
1104 CURSOR investor_fee_line_csr ( p_investor_line_id NUMBER) IS
1105 SELECT okc_fee_line.id ,
1106 okc_fee_line.chr_id
1107 FROM okc_k_lines_b okc_fee_line ,
1108 okl_k_lines okl_fee_line ,
1109 okc_line_styles_b lse ,
1110 okc_k_party_roles_b inv_line_role ,
1111 okc_k_party_roles_b fee_line_role
1112 WHERE inv_line_role.cle_id = p_investor_line_id
1113 AND inv_line_role.object1_id1 = fee_line_role.object1_id1
1114 AND inv_line_role.rle_code = fee_line_role.rle_code
1115 AND inv_line_role.dnz_chr_id = fee_line_role.dnz_chr_id
1116 AND fee_line_role.cle_id = okc_fee_line.id
1117 AND okc_fee_line.lse_id = lse.id
1118 AND lse.lty_code = 'FEE'
1119 AND okc_fee_line.chr_id = fee_line_role.dnz_chr_id
1120 AND okc_fee_line.id = okl_fee_line.id;
1121
1122 investor_fee_line_rec investor_fee_line_csr%ROWTYPE;
1123 l_fee_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
1124 -- gboomina Bug 6814331 - End
1125
1126 BEGIN
1127
1128 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1129 -- call START_ACTIVITY to create savepoint, check compatibility
1130 -- and initialize message list
1131 x_return_status := OKC_API.START_ACTIVITY(
1132 p_api_name => l_api_name,
1133 p_pkg_name => G_PKG_NAME,
1134 p_init_msg_list => p_init_msg_list,
1135 l_api_version => l_api_version,
1136 p_api_version => p_api_version,
1137 p_api_type => G_API_TYPE,
1138 x_return_status => x_return_status);
1139
1140 -- check if activity started successfully
1141 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1142 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1143 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1144 RAISE OKC_API.G_EXCEPTION_ERROR;
1145 END IF;
1146
1147 LOOP
1148 i := i + 1;
1149
1150 -- gboomina Bug 6814331 - Start
1151 -- Delete fees attached to the investor before deleting the
1152 -- investor line
1153 FOR investor_fee_line_rec IN investor_fee_line_csr(p_inv_tbl(i).cle_id)
1154 LOOP
1155 l_fee_rec.line_id := investor_fee_line_rec.id;
1156 l_fee_rec.dnz_chr_id := investor_fee_line_rec.chr_id;
1157
1158 OKL_MAINTAIN_FEE_PVT.delete_fee_type (
1159 p_api_version => p_api_version,
1160 p_init_msg_list => p_init_msg_list,
1161 x_return_status => x_return_status,
1162 x_msg_count => x_msg_count,
1163 x_msg_data => x_msg_data,
1164 p_fee_types_rec => l_fee_rec) ;
1165
1166 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1167 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1168 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1169 RAISE OKL_API.G_EXCEPTION_ERROR;
1170 END IF;
1171
1172 END LOOP;
1173 -- gboomina Bug 6814331 - End
1174
1175 -- Delete investor line now after all the fees attached to the
1176 -- investor got deleted successfully
1177 OKL_CONTRACT_PUB.delete_contract_line(
1178 p_api_version => p_api_version,
1179 p_init_msg_list => p_init_msg_list,
1180 x_return_status => x_return_status,
1181 x_msg_count => x_msg_count,
1182 x_msg_data => x_msg_data,
1183 p_line_id => p_inv_tbl(i).cle_id);
1184
1185 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1187 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1188 RAISE OKL_API.G_EXCEPTION_ERROR;
1189 END IF;
1190
1191 EXIT WHEN (i >= p_inv_tbl.last);
1192 END LOOP;
1193
1194 --Call End Activity
1195 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1196 x_msg_data => x_msg_data);
1197
1198
1199 EXCEPTION
1200 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1201 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1202 p_api_name => l_api_name,
1203 p_pkg_name => G_PKG_NAME,
1204 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1205 x_msg_count => x_msg_count,
1206 x_msg_data => x_msg_data,
1207 p_api_type => G_API_TYPE);
1208
1209 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1210 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1211 p_api_name => l_api_name,
1212 p_pkg_name => G_PKG_NAME,
1213 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1214 x_msg_count => x_msg_count,
1215 x_msg_data => x_msg_data,
1216 p_api_type => G_API_TYPE);
1217
1218 WHEN OTHERS THEN
1219 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1220 p_api_name => l_api_name,
1221 p_pkg_name => G_PKG_NAME,
1222 p_exc_name => 'OTHERS',
1223 x_msg_count => x_msg_count,
1224 x_msg_data => x_msg_data,
1225 p_api_type => G_API_TYPE);
1226
1227
1228 END delete_investor;
1229
1230
1231
1232 /*
1233
1234 Procedure get_sec_header_info(
1235 p_api_version IN NUMBER,
1236 p_init_msg_list IN VARCHAR2,
1237 x_return_status OUT NOCOPY VARCHAR2,
1238 x_msg_count OUT NOCOPY NUMBER,
1239 x_msg_data OUT NOCOPY VARCHAR2,
1240 p_chr_id IN NUMBER,
1241 p_chr_id_old IN NUMBER,
1242 x_hdr_tbl OUT NOCOPY hdr_tbl_type) IS
1243
1244 l_api_name CONSTANT VARCHAR2(30) := 'GET_SEC_HEADER_INFO';
1245 l_api_version CONSTANT NUMBER := 1.0;
1246
1247 Cursor chr_csr ( chrId NUMBER ) IS
1248 SELECT
1249 CHR.ID ID,
1250 CHR.CONTRACT_NUMBER CONTRACT_NUMBER,
1251 TL.SHORT_DESCRIPTION SHORT_DESCRIPTION,
1252 CHR.START_DATE START_DATE,
1253 CHR.END_DATE END_DATE,
1254 CHR.AUTHORING_ORG_ID AUTHORING_ORG_ID,
1255 CHR.INV_ORGANIZATION_ID INV_ORG_ID,
1256 CHR.STS_CODE STS_CODE,
1257 STL.MEANING MEANING,
1258 FND.CURRENCY_CODE CURRENCY_CODE,
1259 FND.NAME CURRENCY,
1260 KLP.ID PDT_ID,
1261 KLP.NAME PRODUCT_NAME,
1262 KLP.DESCRIPTION PRODUCT_DESCRIPTION,
1263 POL.POOL_NUMBER,
1264 POL.TOTAL_PRINCIPAL_AMOUNT,
1265 POL.TOTAL_RECEIVABLE_AMOUNT
1266 FROM
1267 OKC_K_HEADERS_B CHR,OKC_STATUSES_TL STL,OKC_K_HEADERS_TL TL,OKL_K_HEADERS KHR,
1268 OKL_PRODUCTS KLP, OKL_POOLS POL, FND_CURRENCIES_VL FND
1269 WHERE
1270 TL.ID = CHR.ID AND STL.CODE = CHR.STS_CODE AND TL.LANGUAGE = USERENV('LANG')
1271 AND KHR.ID = CHR.ID AND KLP.ID(+) = KHR.PDT_ID
1272 AND CHR.CURRENCY_CODE = FND.CURRENCY_CODE
1273 AND CHR.SCS_CODE = 'INVESTOR_AGREEMENT'
1274 AND CHR.ID = POL.KHR_ID
1275 AND CHR.ID = chrId;
1276
1277 CHR_REC chr_csr%ROWTYPE;
1278
1279 BEGIN
1280
1281 If ( ( nvl(p_chr_id,0) <> 0 )
1282 AND ((nvl(p_chr_id_old,0) = 0)
1283 OR ( p_chr_id <> p_chr_id_old) )) Then
1284
1285 OPEN chr_csr( p_chr_id );
1286 FETCH chr_csr INTO CHR_REC;
1287 if (chr_csr%NOTFOUND ) Then
1288 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1289 End If;
1290 CLOSE chr_csr;
1291
1292 x_hdr_tbl(1) := CHR_REC.ID;
1293 x_hdr_tbl(2) := CHR_REC.CONTRACT_NUMBER;
1294 x_hdr_tbl(3) := CHR_REC.SHORT_DESCRIPTION;
1295 x_hdr_tbl(4) := CHR_REC.START_DATE;
1296 x_hdr_tbl(5) := CHR_REC.END_DATE;
1297 x_hdr_tbl(6) := CHR_REC.AUTHORING_ORG_ID;
1298 x_hdr_tbl(7) := CHR_REC.INV_ORG_ID;
1299 x_hdr_tbl(8) := CHR_REC.STS_CODE;
1300 x_hdr_tbl(9) := CHR_REC.MEANING;
1301 x_hdr_tbl(10) := CHR_REC.CURRENCY_CODE;
1302 x_hdr_tbl(11) := CHR_REC.CURRENCY;
1303 x_hdr_tbl(12) := CHR_REC.PDT_ID;
1304 x_hdr_tbl(13) := CHR_REC.PRODUCT_NAME;
1305 x_hdr_tbl(14) := CHR_REC.PRODUCT_DESCRIPTION;
1306 x_hdr_tbl(15) := CHR_REC.POOL_NUMBER;
1307 x_hdr_tbl(16) := CHR_REC.TOTAL_PRINCIPAL_AMOUNT;
1308 x_hdr_tbl(17) := CHR_REC.TOTAL_RECEIVABLE_AMOUNT;
1309
1310 Else
1311 x_hdr_tbl(1) := 'GET_FROM_REQUEST';
1312 End if;
1313
1314 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1315
1316 Exception
1317 when OKL_API.G_EXCEPTION_ERROR then
1318 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1319 p_api_name => l_api_name,
1320 p_pkg_name => g_pkg_name,
1321 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1322 x_msg_count => x_msg_count,
1323 x_msg_data => x_msg_data,
1324 p_api_type => g_api_type);
1325
1326 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1327 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1328 p_api_name => l_api_name,
1329 p_pkg_name => g_pkg_name,
1330 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1331 x_msg_count => x_msg_count,
1332 x_msg_data => x_msg_data,
1333 p_api_type => g_api_type);
1334
1335 when OTHERS then
1336 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1337 p_api_name => l_api_name,
1338 p_pkg_name => g_pkg_name,
1339 p_exc_name => 'OTHERS',
1340 x_msg_count => x_msg_count,
1341 x_msg_data => x_msg_data,
1342 p_api_type => g_api_type);
1343
1344
1345 END get_sec_header_info;
1346 */
1347
1348
1349 END Okl_Sec_Investor_Pvt;