[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_CONCURRENT_PVT
Source
1 PACKAGE BODY Okl_Sec_Concurrent_Pvt AS
2 /* $Header: OKLRSZOB.pls 120.8 2007/12/21 14:11:06 kthiruva noship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6
7 G_POC_STS_NEW CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_NEW;
8 G_POC_STS_ACTIVE CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_ACTIVE;
9 G_POC_STS_INACTIVE CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_INACTIVE;
10
11 G_BUYBACK_AGREEMENT_HEAD CONSTANT VARCHAR2(30) := 'OKL_BUYBACK_AGREEMENT';
12
13 G_SET_OF_BOOKS CONSTANT VARCHAR2(30) := 'OKL_SET_OF_BOOKS';
14 G_OPERATING_UNIT CONSTANT VARCHAR2(30) := 'OKL_OPERATING_UNIT';
15
16 G_INVESTOR_AGREEMENT_NUMBER CONSTANT VARCHAR2(30) := 'OKL_INVESTOR_AGREEMENT_NUMBER';
17 G_POOL_NUMBER CONSTANT VARCHAR2(15) := 'OKL_POOL_NUMBER';
18 G_BUYBACK_DATE CONSTANT VARCHAR2(30) := 'OKL_BUYBACK_DATE';
19
20 G_CURRENCY CONSTANT VARCHAR2(20) := 'OKL_AGN_RPT_CURRENCY';
21 G_PROGRAM_RUN_DATE CONSTANT VARCHAR2(30) := 'OKL_PROGRAM_RUN_DATE';
22
23 G_DETAILS CONSTANT VARCHAR2(15) := 'OKL_DETAILS';
24 G_ROW_NUMBER CONSTANT VARCHAR2(14) := 'OKL_ROW_NUMBER';
25 G_CONTRACT_NUMBER CONSTANT VARCHAR2(25) := 'OKL_GLP_RPT_CTR_NUM_TITLE';
26 G_ASSET_NUMBER CONSTANT VARCHAR2(16) := 'OKL_ASSET_NUMBER';
27 G_INVESTOR CONSTANT VARCHAR2(15) := 'OKL_INVESTOR';
28 G_STREAM_TYPE_SUBCLASS CONSTANT VARCHAR2(30) := 'OKL_STREAM_TYPE_SUBCLASS';
29 G_STREAMS_AMOUNT CONSTANT VARCHAR2(20) := 'OKL_STREAMS_AMOUNT';
30 G_BUYBACK_AMOUNT CONSTANT VARCHAR2(20) := 'OKL_BUYBACK_AMOUNT';
31
32 G_ERRORS CONSTANT VARCHAR2(30) := 'OKL_POOL_CLEANUP_ERRORS';
33 G_NONE CONSTANT VARCHAR2(30) := 'OKL_NONE';
34
35 ----------------------------------------------------------------------------
36 -- Procedures and Functions
37 ----------------------------------------------------------------------------
38 PROCEDURE get_error_message(p_all_message OUT nocopy error_message_type)
39 IS
40 l_msg_text VARCHAR2(2000);
41 l_msg_count NUMBER ;
42 BEGIN
43 l_msg_count := fnd_msg_pub.count_msg;
44 FOR i IN 1..l_msg_count
45 LOOP
46 fnd_msg_pub.get
47 (p_data => p_all_message(i),
48 p_msg_index_out => l_msg_count,
49 p_encoded => fnd_api.g_false,
50 p_msg_index => fnd_msg_pub.g_next
51 );
52 END LOOP;
53 EXCEPTION
54 WHEN OTHERS THEN
55 NULL;
56 END get_error_message;
57
58 -- mvasudev
59 FUNCTION get_message(p_current_api IN VARCHAR2
60 ,p_called_api IN VARCHAR2
61 ,p_msg_token IN VARCHAR2)
62 RETURN VARCHAR2
63 IS
64
65 BEGIN
66 RETURN (G_PKG_NAME || '.' || p_current_api || '::' ||
67 p_called_api || ':: ' ||
68 FND_MESSAGE.GET_STRING(G_APP_NAME,p_msg_token));
69
70 END get_message;
71
72
73 ----------------------------------------------------------------------------------
74 -- Start of comments
75 --
76 -- Procedure Name : BUYBACK_AGREEMENT
77 -- Description : This is a wrapper procedure for concurrent program to call private API
78 -- Business Rules :
79 -- Parameters :
80 -- Version : 1.0
81 -- End of comments
82 ----------------------------------------------------------------------------------
83
84 PROCEDURE buyback_agreement(x_errbuf OUT NOCOPY VARCHAR2
85 ,x_retcode OUT NOCOPY NUMBER
86 ,p_khr_id IN VARCHAR2)
87 IS
88
89 -- Set of Books
90 CURSOR l_okl_set_of_books_csr
91 IS
92 SELECT okls.set_of_books_id set_of_books_id
93 ,glsb.name set_of_books_name
94 FROM GL_LEDGERS_PUBLIC_V glsb
95 ,OKL_SYS_ACCT_OPTS okls
96 WHERE glsb.ledger_id = okls.set_of_books_id;
97
98 -- Operating Unit
99 CURSOR l_okl_operating_unit_csr
100 IS
101 SELECT name
102 FROM hr_operating_units
103 WHERE organization_id = mo_global.get_current_org_id();
104
105 -- Agreement Number, Pool Number
106 CURSOR l_okl_pol_khr_csr(p_khr_id IN NUMBER)
107 IS
108 SELECT chrb.contract_number agreement_number
109 ,polb.pool_number pool_number
110 ,polb.currency_code currency_code
111 FROM okl_pools polb
112 ,okc_k_headers_b chrb
113 WHERE polb.khr_id = chrb.id
114 AND polb.khr_id = p_khr_id;
115
116
117 -- Pool Contents for this Agreement
118 CURSOR l_okl_pocs_csr(p_khr_id IN NUMBER,p_effective_date DATE)
119 IS
120 SELECT pocb.id id
121 ,chrb.contract_number contract_number
122 ,clet.name asset_number
123 ,styv.name stream_type_name
124 FROM okl_pool_contents pocb
125 ,okl_pools polb
126 ,okc_k_headers_b chrb
127 ,okc_k_lines_tl clet
128 ,okl_strm_type_v styv
129 WHERE pocb.pol_id = polb.id
130 AND polb.khr_id = p_khr_id
131 AND pocb.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE
132 AND pocb.streams_to_date > p_effective_date
133 AND pocb.khr_id = chrb.id
134 AND pocb.kle_id = clet.id
135 AND pocb.sty_id = styv.id;
136 --Fixed Bug #5484903
137 CURSOR l_okl_investor_names_csr(p_id IN NUMBER)
138 IS
139 SELECT okxv.name
140 ,chrb.currency_code
141 FROM OKX_PARTIES_V okxv
142 ,okc_k_headers_all_b chrb
143 ,okc_k_lines_b cleb
144 ,okc_k_party_roles_b cplb
145 WHERE cleb.id = p_id
146 AND cleb.dnz_chr_id = chrb.id
147 AND cplb.cle_id = cleb.id
148 AND cplb.dnz_chr_id = cleb.dnz_chr_id
149 AND okxv.id1 = cplb.object1_id1
150 AND okxv.id2 = cplb.object1_id2;
151
152 -- All Lease Contracts associated to this Agreement
153 CURSOR l_okl_dnz_chrs_csr (p_khr_id IN NUMBER)
154 IS
155 SELECT DISTINCT
156 pocb.khr_id dnz_chr_id
157 FROM okl_pool_contents pocb
158 ,okl_pools polb
159 WHERE pocb.pol_id = polb.id
160 AND polb.khr_id = p_khr_id
161 AND pocb.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE;
162
163 -- Cursor
164 CURSOR l_okl_pol_csr
165 IS
166 SELECT DISTINCT
167 pocb.pol_id
168 ,pocb.khr_id
169 ,styb.stream_type_subclass
170 FROM okl_pool_contents pocb
171 ,okl_pools polb
172 ,okl_strm_type_b styb
173 WHERE pocb.pol_id = polb.id
174 AND polb.khr_id = p_khr_id
175 AND pocb.sty_id = styb.id
176 AND pocb.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
177 ORDER BY pocb.khr_id,styb.stream_type_subclass;
178
179
180 -- Cursor
181 CURSOR l_okl_poc_csr(p_pol_id IN NUMBER)
182 IS
183 SELECT pol_id
184 ,khr_id
185 ,contract_number
186 ,sty_subclass_code
187 ,sty_subclass
188 ,streams_amount
189 ,currency_code
190 FROM okl_sec_buyback_streams_uv
191 WHERE pol_id = p_pol_id;
192
193
194 l_api_name CONSTANT VARCHAR2(40) := 'BUYBACK_AGREEMENT';
195 l_api_version CONSTANT NUMBER := 1.0;
196 p_api_version CONSTANT NUMBER := 1.0;
197 l_init_msg_list VARCHAR2(1) := 'T';
198
199 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
200 l_msg_count NUMBER;
201 l_msg_data VARCHAR2(2000);
202 l_error_msg_rec Error_message_Type;
203
204 lp_effective_date DATE := SYSDATE;
205
206 -- report related fields
207 l_row_num_len NUMBER := 6;
208 l_contract_num_len NUMBER := 30;
209 /* l_asset_num_len NUMBER := 15;
210 l_investor_len NUMBER := 40;
211 l_sty_name_len NUMBER := 15;*/
212 l_sty_subclass_len NUMBER := 25;
213 l_amount_len NUMBER := 15;
214 l_max_len NUMBER := 120;
215 l_prompt_len NUMBER := 35;
216
217 l_content VARCHAR2(1000);
218 l_header_len NUMBER;
219 l_filler VARCHAR2(5) := RPAD(' ',5,' ');
220 l_total_rec_count NUMBER := 0;
221 l_buyback_amount NUMBER;
222
223 BEGIN
224
225 x_retcode := 0;
226 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
227 p_pkg_name => G_PKG_NAME,
228 p_init_msg_list => l_init_msg_list,
229 l_api_version => l_api_version,
230 p_api_version => p_api_version,
231 p_api_type => G_API_TYPE,
232 x_return_status => l_return_status);
233
234 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
235 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
236 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
237 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
238 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
239 RAISE G_EXCEPTION_ERROR;
240 END IF;
241
242 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
243 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
244
245 -- Printing the values in the log file.
246
247 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
248 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_khr_id : ' || p_khr_id);
249 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
250
251 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
252
253 -- Header
254 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_BUYBACK_AGREEMENT_HEAD);
255 l_header_len := LENGTH(l_content);
256 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2); -- center align header
257 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
258
259 l_content := RPAD('=',l_header_len,'='); -- underline header
260 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2,'='); -- center align
261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
262
263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
264 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
265
266 -- Set of Books, Operating Unit
267 FOR l_okl_set_of_books_rec IN l_okl_set_of_books_csr
268 LOOP
269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_SET_OF_BOOKS),l_prompt_len) || ' : ' || l_okl_set_of_books_rec.set_of_books_name);
270 END LOOP;
271 FOR l_okl_operating_unit_rec IN l_okl_operating_unit_csr
272 LOOP
273 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_OPERATING_UNIT),l_prompt_len) || ' : ' || l_okl_operating_unit_rec.name);
274 END LOOP;
275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
276
277 -- Investor Agreement Number, Pool Number, Buyback Date
278 FOR l_okl_pol_khr_rec IN l_okl_pol_khr_csr(p_khr_id)
279 LOOP
280 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_INVESTOR_AGREEMENT_NUMBER),l_prompt_len) || ' : ' || l_okl_pol_khr_rec.agreement_number);
281 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_NUMBER),l_prompt_len) || ' : ' || l_okl_pol_khr_rec.pool_number);
282 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_BUYBACK_DATE),l_prompt_len) || ' : ' || lp_effective_date);
283
284 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
285 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CURRENCY),l_prompt_len) || ' : ' || l_okl_pol_khr_rec.currency_code);
286 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_PROGRAM_RUN_DATE),l_prompt_len) || ' : ' || SYSDATE);
287 END LOOP;
288
289 -- sub head (Details)
290 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
291 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_DETAILS);
292 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
293 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
294 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
295
296
297
298 l_total_rec_count := 0;
299 FOR l_okl_pol_rec IN l_okl_pol_csr
300 LOOP
301 FOR l_okl_poc_rec IN l_okl_poc_csr(l_okl_pol_rec.pol_id)
302 LOOP
303 l_total_rec_count := l_total_rec_count + 1;
304 IF l_total_rec_count = 1 THEN
305 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
306 || RPAD('-',l_contract_num_len-1,'-') || ' '
307 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
308 || RPAD('-',l_amount_len,'-') || ' '
309 || RPAD('-',l_amount_len,'-');
310
311 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
312
313 l_content := RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ROW_NUMBER),l_row_num_len-1) || ' '
314 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACT_NUMBER),l_contract_num_len-1) || ' '
315 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE_SUBCLASS),l_sty_subclass_len-1) || ' '
316 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS_AMOUNT),l_amount_len) || ' '
317 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_BUYBACK_AMOUNT),l_amount_len);
318
319 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
320
321 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
322 || RPAD('-',l_contract_num_len-1,'-') || ' '
323 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
324 || RPAD('-',l_amount_len,'-') || ' '
325 || RPAD('-',l_amount_len,'-');
326
327 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
328
329 END IF; -- l_total_rec_count
330
331 Okl_Securitization_Pvt.calculate_buyback_amount(p_api_version => l_api_version
332 ,p_init_msg_list => l_init_msg_list
333 ,x_return_status => l_return_status
334 ,x_msg_count => l_msg_count
335 ,x_msg_data => l_msg_data
336 ,p_khr_id => l_okl_poc_rec.khr_id
337 ,p_pol_id => l_okl_poc_rec.pol_id
338 ,p_stream_type_subclass => l_okl_poc_rec.sty_subclass_code
339 ,x_buyback_amount => l_buyback_amount);
340
341
342
343 l_content := RPAD(l_total_rec_count,l_row_num_len)
344 || RPAD(l_okl_poc_rec.contract_number ,l_contract_num_len)
345 || RPAD(l_okl_poc_rec.sty_subclass ,l_sty_subclass_len)
346 || LPAD(okl_accounting_util.format_amount(l_okl_poc_rec.streams_amount,l_okl_poc_rec.currency_code),l_amount_len)
347 || LPAD(okl_accounting_util.format_amount(l_buyback_amount,l_okl_poc_rec.currency_code),l_amount_len);
348
349 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
350
351 l_buyback_amount := NULL;
352
353 Okl_Securitization_Pvt.buyback_pool_contents(p_api_version => l_api_version
354 ,p_init_msg_list => l_init_msg_list
355 ,x_return_status => l_return_status
356 ,x_msg_count => l_msg_count
357 ,x_msg_data => l_msg_data
358 ,p_khr_id => l_okl_poc_rec.khr_id
359 ,p_pol_id => l_okl_poc_rec.pol_id
360 ,p_stream_type_subclass => l_okl_poc_rec.sty_subclass_code
361 ,p_effective_date => lp_effective_date);
362
363 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
364 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Securitization_Pvt.buyback_pool_contents',G_UNEXPECTED_ERROR));
365 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
366 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
367 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Securitization_Pvt.buyback_pool_contents',G_EXPECTED_ERROR));
368 RAISE G_EXCEPTION_ERROR;
369 END IF;
370
371
372 END LOOP; -- l_okl_poc_csr
373 END LOOP; -- l_okl_pol_csr
374 /*
375 FOR l_okl_poc_rec IN l_okl_pocs_csr(p_khr_id,lp_effective_date)
376 LOOP
377
378 Okl_Securitization_Pvt.buyback_investor_shares(p_api_version => l_api_version
379 ,p_init_msg_list => l_init_msg_list
380 ,x_return_status => l_return_status
381 ,x_msg_count => l_msg_count
382 ,x_msg_data => l_msg_data
383 ,p_poc_id => l_okl_poc_rec.id
384 ,p_effective_date => lp_effective_date
385 ,x_investor_shares_tbl => lx_investor_shares_tbl);
386
387 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
388 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Securitization_Pvt.BUYBACK_POOL_CONTENT',G_UNEXPECTED_ERROR));
389 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
390 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
391 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Securitization_Pvt.BUYBACK_POOL_CONTENT',G_EXPECTED_ERROR));
392 RAISE G_EXCEPTION_ERROR;
393 END IF;
394
395 FOR l_row_count IN 1..lx_investor_shares_tbl.COUNT
396 LOOP
397 -- Print Table Header only the first time
398 IF l_total_rec_count = 0 THEN
399 -- Table Header
400 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
401 || RPAD('-',l_contract_num_len-1,'-') || ' '
402 || RPAD('-',l_asset_num_len-1,'-') || ' '
403 || RPAD('-',l_sty_name_len-1,'-') || ' '
404 || RPAD('-',l_investor_len-1,'-') || ' '
405 || RPAD('-',l_amount_len,'-');
406
407 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
408
409 l_content := RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ROW_NUMBER),l_row_num_len-1) || ' '
410 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACT_NUMBER),l_contract_num_len-1) || ' '
411 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ASSET_NUMBER),l_asset_num_len-1) || ' '
412 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE),l_sty_name_len-1) || ' '
413 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_INVESTOR),l_investor_len-1) || ' '
414 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_AMOUNT_DUE),l_amount_len);
415
416 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
417
418 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
419 || RPAD('-',l_contract_num_len-1,'-') || ' '
420 || RPAD('-',l_asset_num_len-1,'-') || ' '
421 || RPAD('-',l_sty_name_len-1,'-') || ' '
422 || RPAD('-',l_investor_len-1,'-') || ' '
423 || RPAD('-',l_amount_len,'-');
424
425 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
426
427 END IF; -- l_total_rec_count
428
429 l_total_rec_count := l_total_rec_count+1;
430 l_content := RPAD(l_total_rec_count,l_row_num_len)
431 || RPAD(l_okl_poc_rec.contract_number ,l_contract_num_len)
432 || RPAD(l_okl_poc_rec.asset_number ,l_asset_num_len)
433 || RPAD(l_okl_poc_rec.stream_type_name ,l_sty_name_len);
434
435 FOR l_okl_investor_names_rec IN l_okl_investor_names_csr(lx_investor_shares_tbl(l_row_count).investor_id)
436 LOOP
437 l_content := l_content || RPAD(l_okl_investor_names_rec.name ,l_investor_len)
438 || LPAD(okl_accounting_util.format_amount(lx_investor_shares_tbl(l_row_count).amount,l_okl_investor_names_rec.currency_code),l_amount_len);
439 END LOOP; -- l_okl_investor_names_csr
440
441 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_content);
442
443 END LOOP; -- lx_investor_shares_tbl
444
445 END LOOP; -- l_okl_pocs_csr
446
447
448 IF l_total_rec_count = 0 THEN
449 -- "No Records"
450 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_NONE);
451 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_filler || l_content);
452 ELSE
453 FOR l_okl_dnz_chrs_rec IN l_okl_dnz_chrs_csr(p_khr_id)
454 LOOP
455 okl_accrual_sec_pvt.cancel_streams(p_api_version => l_api_version
456 ,p_init_msg_list => l_init_msg_list
457 ,x_return_status => l_return_status
458 ,x_msg_count => l_msg_count
459 ,x_msg_data => l_msg_data
460 ,p_khr_id => l_okl_dnz_chrs_rec.dnz_chr_id
461 ,p_cancel_date => lp_effective_date);
462
463
464 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
465 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'okl_accrual_sec_pvt.cancel_streams',G_UNEXPECTED_ERROR));
466 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
467 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
468 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'okl_accrual_sec_pvt.cancel_streams',G_EXPECTED_ERROR));
469 RAISE OKL_API.G_EXCEPTION_ERROR;
470 END IF;
471 END LOOP; -- l_okl_dnz_chrs_csr
472
473 END IF; -- l_total_rec_count
474 */
475 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
476
477 -- Errors
478 -- sub head
479 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
480 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_ERRORS);
481 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
482 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
483 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
484
485
486 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
487 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
488
489 -- "No Errors"
490 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_NONE);
491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_filler || l_content);
492
493 x_retcode := 0;
494
495 EXCEPTION
496 WHEN G_EXCEPTION_ERROR THEN
497 x_retcode := 2;
498 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
499 p_pkg_name => G_PKG_NAME,
500 p_exc_name => G_EXC_NAME_ERROR,
501 x_msg_count => l_msg_count,
502 x_msg_data => l_msg_data,
503 p_api_type => G_API_TYPE);
504
505 GET_ERROR_MESSAGE(l_error_msg_rec);
506 IF (l_error_msg_rec.COUNT > 0) THEN
507 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
508 LOOP
509 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
510 END LOOP;
511 END IF;
512 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
513 x_retcode := 2;
514 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
515 p_pkg_name => G_PKG_NAME,
516 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
517 x_msg_count => l_msg_count,
518 x_msg_data => l_msg_data,
519 p_api_type => G_API_TYPE);
520
521 GET_ERROR_MESSAGE(l_error_msg_rec);
522 IF (l_error_msg_rec.COUNT > 0) THEN
523 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
524 LOOP
525 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
526 END LOOP;
527 END IF;
528 WHEN OTHERS THEN
529 x_errbuf := SQLERRM;
530 x_retcode := 2;
531 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
532 p_pkg_name => G_PKG_NAME,
533 p_exc_name => G_EXC_NAME_OTHERS,
534 x_msg_count => l_msg_count,
535 x_msg_data => l_msg_data,
536 p_api_type => G_API_TYPE);
537
538 GET_ERROR_MESSAGE(l_error_msg_rec);
539 IF (l_error_msg_rec.COUNT > 0) THEN
540 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
541 LOOP
542 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
543 END LOOP;
544 END IF;
545
546 END buyback_agreement;
547
548 -- fmiao bug: 4748514 start
549 ----------------------------------------------------------------------------------
550 -- Start of comments
551 --
552 -- Procedure Name : activate_agreement_ui
553 -- Description : Activate Investor agreement
554 -- This is a wrapper procedure for concurrent program call from jsp/UI
555 -- Business Rules :
556 -- Parameters :
557 -- Version : 1.0
558 -- End of comments
559 ----------------------------------------------------------------------------------
560
561 PROCEDURE activate_agreement_ui(
562 p_api_version IN NUMBER
563 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
564 ,x_return_status OUT nocopy VARCHAR2
565 ,x_msg_count OUT nocopy NUMBER
566 ,x_msg_data OUT nocopy VARCHAR2
567 ,x_request_id OUT nocopy NUMBER
568 -- agreement id --
569 ,p_chr_id IN NUMBER)
570 AS
571 l_api_name VARCHAR2(30) := 'activate_agreement_ui';
572 l_api_version NUMBER := 1.0;
573 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
574 l_date VARCHAR2(20) ;
575
576 BEGIN
577
578 x_return_status := G_RET_STS_SUCCESS;
579
580 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
581 p_pkg_name => G_PKG_NAME,
582 p_init_msg_list => p_init_msg_list,
583 l_api_version => l_api_version,
584 p_api_version => p_api_version,
585 p_api_type => G_API_TYPE,
586 x_return_status => l_return_status);
587
588 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
589 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
590 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
591 RAISE G_EXCEPTION_ERROR;
592 END IF;
593
594 -- Bug#4562312, fmiao, 31/10/2005
595 --call concurrent program
596 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
597 x_request_id := Fnd_Request.SUBMIT_REQUEST(
598 application => 'OKL'
599 ,program => 'OKL_ACTIVATE_INV_AGREEMENT'
600 ,argument1 => TO_CHAR(p_chr_id)
601 );
602
603 -- Added these validations to check to see if the request has been submitted successfully.
604 IF x_request_id = 0 THEN
605 OKL_API.set_message(p_app_name => G_APP_NAME,
606 p_msg_name => 'OKL_CONC_REQ_ERROR',
607 p_token1 => 'PROG_NAME',
608 p_token1_value => 'OKL Activate Investor Agreement',
609 p_token2 => 'REQUEST_ID',
610 p_token2_value => x_request_id);
611
612 RAISE G_EXCEPTION_ERROR;
613 END IF;
614
615 OKL_API.end_activity(x_msg_count, x_msg_data);
616
617 EXCEPTION
618 WHEN G_EXCEPTION_ERROR THEN
619
620 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
621 p_pkg_name => G_PKG_NAME,
622 p_exc_name => G_EXC_NAME_ERROR,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data,
625 p_api_type => G_API_TYPE);
626 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
627
628 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
629 p_pkg_name => G_PKG_NAME,
630 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data,
633 p_api_type => G_API_TYPE);
634 WHEN OTHERS THEN
635
636 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
637 p_pkg_name => G_PKG_NAME,
638 p_exc_name => G_EXC_NAME_OTHERS,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data,
641 p_api_type => G_API_TYPE);
642 END activate_agreement_ui;
643
644 ----------------------------------------------------------------------------------
645 -- Start of comments
646 --
647 -- Procedure Name : activate_agreement
648 -- Description : This is a wrapper procedure to call the activate agreement API
649 -- to activate the agreement.
650 -- Business Rules :
651 -- Parameters :
652 -- Version : 1.0
653 -- End of comments
654 ----------------------------------------------------------------------------------
655
656
657 PROCEDURE activate_agreement(x_errbuf OUT NOCOPY VARCHAR2
658 ,x_retcode OUT NOCOPY NUMBER
659 ,p_chr_id IN VARCHAR2)
660 IS
661
662 l_api_name CONSTANT VARCHAR2(40) := 'ACTIVATE_AGREEMENT';
663 l_api_version CONSTANT NUMBER := 1.0;
664 p_api_version CONSTANT NUMBER := 1.0;
665 l_init_msg_list VARCHAR2(1) := 'T';
666 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
667 l_msg_count NUMBER;
668 l_msg_data VARCHAR2(2000);
669 l_error_msg_rec Error_message_Type;
670
671 l_chr_id NUMBER;
672
673 --Added by kthiruva on 19-Dec-2007
674 --Call the relevant api to activate the investor agreement
675 --or the add contracts request
676 --Bug 6691554 - Start of Changes
677 CURSOR get_ia_status_csr(p_chr_id NUMBER)
678 IS
679 SELECT chrb.sts_code
680 FROM OKC_K_HEADERS_ALL_B CHRB
681 WHERE CHRB.ID = p_chr_id;
682
683 l_status_code OKC_K_HEADERS_ALL_B.STS_CODE%TYPE;
684 --Bug 6691554 - End of Changes
685
686 BEGIN
687
688 x_retcode := 0;
689 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
690 p_pkg_name => G_PKG_NAME,
691 p_init_msg_list => l_init_msg_list,
692 l_api_version => l_api_version,
693 p_api_version => p_api_version,
694 p_api_type => G_API_TYPE,
695 x_return_status => l_return_status);
696
697
698 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
699 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
700 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
701 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
702 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
703 RAISE G_EXCEPTION_ERROR;
704 END IF;
705
706 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
707 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
708
709 -- Printing the values in the log file.
710
711 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
712 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_chr_id : ' || p_chr_id);
713 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
714
715 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
716
717 l_chr_id := TO_NUMBER(p_chr_id);
718
719 --Bug 6691554 - Start of Changes
720 FOR get_ia_status_rec IN get_ia_status_csr(l_chr_id)
721 LOOP
722 l_status_code := get_ia_status_rec.sts_code;
723 END LOOP;
724
725 IF l_status_code = 'ACTIVE' THEN
726 okl_sec_agreement_pvt.activate_add_request(
727 p_api_version => l_api_version
728 ,p_init_msg_list => l_init_msg_list
729 ,x_return_status => l_return_status
730 ,x_msg_count => l_msg_count
731 ,x_msg_data => l_msg_data
732 ,p_khr_id => l_chr_id
733 );
734
735 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
736 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Sec_Concurrent_Pvt.Activate_Agreement',G_UNEXPECTED_ERROR));
737 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
738 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
739 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Sec_Concurrent_Pvt.Activate_Agreement',G_EXPECTED_ERROR));
740 RAISE G_EXCEPTION_ERROR;
741 END IF;
742
743 ELSE
744 okl_sec_agreement_pvt.activate_sec_agreement(
745 p_api_version => l_api_version
746 ,p_init_msg_list => l_init_msg_list
747 ,x_return_status => l_return_status
748 ,x_msg_count => l_msg_count
749 ,x_msg_data => l_msg_data
750 ,p_khr_id => l_chr_id
751 );
752
753 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
754 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Sec_Concurrent_Pvt.Activate_Agreement',G_UNEXPECTED_ERROR));
755 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
756 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
757 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Sec_Concurrent_Pvt.Activate_Agreement',G_EXPECTED_ERROR));
758 RAISE G_EXCEPTION_ERROR;
759 END IF;
760
761 END IF;
762 --Bug 6691554 - End of Changes
763 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
764
765 Okl_Api.END_ACTIVITY(l_msg_count, l_msg_data);
766 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
767
768 x_retcode := 0;
769
770 EXCEPTION
771 WHEN G_EXCEPTION_ERROR THEN
772 x_retcode := 2;
773
774 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
775 p_pkg_name => G_PKG_NAME,
776 p_exc_name => G_EXC_NAME_ERROR,
777 x_msg_count => l_msg_count,
778 x_msg_data => l_msg_data,
779 p_api_type => G_API_TYPE);
780
781 -- print the error message in the log file
782
783 GET_ERROR_MESSAGE(l_error_msg_rec);
784 IF (l_error_msg_rec.COUNT > 0) THEN
785 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
786 LOOP
787 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
788 END LOOP;
789 END IF;
790
791 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
792 x_retcode := 2;
793
794 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
795 p_pkg_name => G_PKG_NAME,
796 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
797 x_msg_count => l_msg_count,
798 x_msg_data => l_msg_data,
799 p_api_type => G_API_TYPE);
800 -- print the error message in the log file
801 GET_ERROR_MESSAGE(l_error_msg_rec);
802 IF (l_error_msg_rec.COUNT > 0) THEN
803 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
804 LOOP
805 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
806 END LOOP;
807 END IF;
808
809 WHEN OTHERS THEN
810 x_errbuf := SQLERRM;
811 x_retcode := 2;
812
813 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
814 p_pkg_name => G_PKG_NAME,
815 p_exc_name => G_EXC_NAME_OTHERS,
816 x_msg_count => l_msg_count,
817 x_msg_data => l_msg_data,
818 p_api_type => G_API_TYPE);
819
820 -- print the error message in the log file
821 GET_ERROR_MESSAGE(l_error_msg_rec);
822 IF (l_error_msg_rec.COUNT > 0) THEN
823 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
824 LOOP
825 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
826 END LOOP;
827 END IF;
828
829 END activate_agreement;
830 -- fmiao bug: 4748514 end
831
832
833
834 END Okl_Sec_Concurrent_Pvt;