[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_TERMINATE_INV_AGMT_PVT
Source
1 PACKAGE BODY OKL_AM_TERMINATE_INV_AGMT_PVT AS
2 /* $Header: OKLRTIAB.pls 120.10.12010000.2 2008/10/01 22:51:12 rkuttiya ship $ */
3
4
5 -- *** Is there a need to check for POC.kle_id = STM.kle_id -- *** --
6 -- YES always, since poc are for assets only, so should get sels for assets only
7
8
9 -- GLOBAL VARIABLES
10 success_message_table message_tbl_type;
11 error_message_table message_tbl_type;
12 -- sosharma added codes for tranaction_status
13 G_POOL_TRX_STATUS_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
14
15 -- SECHAWLA 26-JAN-04 3377730: new declarations
16 msg_lines_table msg_tbl_type;
17
18 l_success_tbl_index NUMBER := 1;
19 l_error_tbl_index NUMBER := 1;
20
21 G_INV_ENDED_BY_DATE DATE;
22
23 G_ERROR VARCHAR2(1) := 'N'; -- RMUNJULU 115.4 3061748
24
25 -- Start of comments
26 --
27 -- Procedure Name : fnd_error_output
28 -- Desciption : Logs the messages in the output log
29 -- Business Rules :
30 -- Parameters :
31 -- Version : 1.0
32 -- History : RMUNJULU created
33 --
34 -- End of comments
35 PROCEDURE fnd_output (
36 p_ia_rec IN ia_rec_type,
37 p_control_flag IN VARCHAR2 ) IS
38
39 lx_error_rec OKL_API.error_rec_type;
40 l_msg_idx INTEGER := G_FIRST;
41
42 -- SECHAWLA l_msg_tbl msg_tbl_type;
43
44 BEGIN
45
46 -- Get the messages in the log
47 LOOP
48
49 FND_MSG_PUB.get(
50 p_msg_index => l_msg_idx,
51 p_encoded => G_FALSE,
52 p_data => lx_error_rec.msg_data,
53 p_msg_index_out => lx_error_rec.msg_count);
54
55 IF (lx_error_rec.msg_count IS NOT NULL) THEN
56
57 -- SECHAWLA 26-JAN-04 3377730: Store the contract id
58 msg_lines_table(lx_error_rec.msg_count).id := p_ia_rec.id;
59
60 -- SECHAWLA 26-JAN-04 3377730: populate message lines in a global pl/sql table
61 --l_msg_tbl(lx_error_rec.msg_count).msg := lx_error_rec.msg_data;
62
63 msg_lines_table(lx_error_rec.msg_count).msg := lx_error_rec.msg_data;
64
65 END IF;
66
67 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
68 OR (lx_error_rec.msg_count IS NULL));
69
70 l_msg_idx := G_NEXT;
71
72 END LOOP;
73
74 IF p_control_flag = 'PROCESSED' THEN
75
76 success_message_table(l_success_tbl_index).id := p_ia_rec.id;
77 success_message_table(l_success_tbl_index).contract_number := p_ia_rec.contract_number;
78 success_message_table(l_success_tbl_index).start_date := p_ia_rec.start_date;
79 success_message_table(l_success_tbl_index).end_date := p_ia_rec.end_date;
80 success_message_table(l_success_tbl_index).status := p_ia_rec.sts_code;
81 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
82 -- of db/Pl Sql Removed the msg_tbl field from message_rec_type
83
84 -- success_message_table(l_success_tbl_index).msg_tbl := l_msg_tbl;
85 l_success_tbl_index := l_success_tbl_index + 1;
86
87 ELSE
88
89 error_message_table(l_error_tbl_index).id := p_ia_rec.id;
90 error_message_table(l_error_tbl_index).contract_number := p_ia_rec.contract_number;
91 error_message_table(l_error_tbl_index).start_date := p_ia_rec.start_date;
92 error_message_table(l_error_tbl_index).end_date := p_ia_rec.end_date;
93 error_message_table(l_error_tbl_index).status := p_ia_rec.sts_code;
94 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
95 -- of db/Pl Sql Removed the msg_tbl field from message_rec_type
96
97 -- error_message_table(l_error_tbl_index).msg_tbl := l_msg_tbl;
98 l_error_tbl_index := l_error_tbl_index + 1;
99
100 END IF;
101
102 EXCEPTION
103
104 WHEN OTHERS THEN
105 -- Set the oracle error message
106 OKL_API.set_message(
107 p_app_name => G_APP_NAME_1,
108 p_msg_name => G_UNEXPECTED_ERROR,
109 p_token1 => G_SQLCODE_TOKEN,
110 p_token1_value => SQLCODE,
111 p_token2 => G_SQLERRM_TOKEN,
112 p_token2_value => SQLERRM);
113
114 END fnd_output;
115
116 -- Start of comments
117 --
118 -- Procedure Name : create_report
119 -- Desciption : Creates the Output and Log Reports
120 -- Business Rules :
121 -- Parameters :
122 -- Version : 1.0
123 -- History : RMUNJULU created
124 -- : RMUNJULU 115.4 3061748
125 --
126 -- End of comments
127 PROCEDURE create_report IS
128
129 i NUMBER;
130 j NUMBER;
131 l_success NUMBER;
132 l_error NUMBER;
133
134 -- Get the Org Name
135 CURSOR org_csr (p_org_id IN NUMBER) IS
136 SELECT HOU.name
137 FROM HR_OPERATING_UNITS HOU
138 WHERE HOU.organization_id = p_org_id;
139
140
141 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
142
143 l_org_name VARCHAR2(300);
144 l_orcl_logo VARCHAR2(300);
145 l_term_heading VARCHAR2(300);
146 l_set_of_books VARCHAR2(300);
147 l_set_of_books_name VARCHAR2(300);
148 l_run_date VARCHAR2(300);
149 l_oper_unit VARCHAR2(300);
150 l_type VARCHAR2(300);
151 l_processed VARCHAR2(300);
152 l_term_k VARCHAR2(300);
153 l_error_k VARCHAR2(300);
154 l_serial VARCHAR2(300);
155 l_k_num VARCHAR2(300);
156 l_start_date VARCHAR2(300);
157 l_end_date VARCHAR2(300);
158 l_status VARCHAR2(300);
159 l_messages VARCHAR2(300);
160 l_eop VARCHAR2(300);
161 l_inv_ended_by VARCHAR2(300);
162 -- RMUNJULU 115.4 3061748
163 l_inv VARCHAR2(300);
164
165 l_print VARCHAR2(1);
166
167 -- SECHAWLA 26-JAN-04 3377730: New deaclarations
168 msg_lines_table_index NUMBER;
169
170 BEGIN
171
172 l_success := success_message_table.COUNT;
173 l_error := error_message_table.COUNT;
174
175 l_orcl_logo := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_ACCT_LEASE_MANAGEMENT');
176 l_term_heading := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_TERM_INV');
177 l_set_of_books := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SET_OF_BOOKS');
178 l_run_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_RUN_DATE');
179 l_oper_unit := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_OPERUNIT');
180 l_type := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_TYPE');
181 l_processed := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_PROCESSED_ENTRIES');
182 l_term_k := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_TERMINATED_INV');
183 l_error_k := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_ERRORED_INV');
184 l_serial := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SERIAL_NUMBER');
185 l_k_num := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_INV_AGR_NUM');
186 l_start_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_START_DATE');
187 l_end_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_END_DATE');
188 l_status := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_STATUS');
189 l_messages := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_MESSAGES');
190 l_eop := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_END_OF_REPORT');
191 l_inv_ended_by := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_INV_AGR_ENDED_BY');
192 -- RMUNJULU 115.4 3061748
193 l_inv := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_INVALID_TERM_DATE');
194
195 l_set_of_books_name := OKL_ACCOUNTING_UTIL.get_set_of_books_name (OKL_ACCOUNTING_UTIL.get_set_of_books_id);
196
197 -- Get the Org Name
198 FOR org_rec IN org_csr (l_org_id) LOOP
199 l_org_name := org_rec.name;
200 END LOOP;
201
202 -- RMUNJULU 115.4 3061748
203 IF G_ERROR <> 'Y' THEN
204
205 --log
206 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
207 FND_FILE.put_line(FND_FILE.log, l_type ||
208 RPAD(' ',40-LENGTH(l_type),' ') ||
209 l_processed);
210
211 FND_FILE.put_line(FND_FILE.log, RPAD('-',77 ,'-'));
212
213 FND_FILE.put_line(FND_FILE.log, l_term_k ||
214 RPAD(' ',40-LENGTH(l_term_k),' ') ||
215 l_success);
216
217 FND_FILE.put_line(FND_FILE.log, l_error_k ||
218 RPAD(' ',40-LENGTH(l_error_k),' ') ||
219 l_error);
220 FND_FILE.put_line(FND_FILE.log,'');
221 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
222
223 -- output
224 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_orcl_logo)/2, ' ' ) ||
225 l_orcl_logo);
226
227 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_term_heading)/2, ' ' ) ||
228 l_term_heading);
229
230 FND_FILE.put_line(FND_FILE.output, RPAD(' ',128/2-LENGTH(l_term_heading)/2 , ' ' ) ||
231 RPAD('-',LENGTH(l_term_heading),'-'));
232
233 FND_FILE.put_line(FND_FILE.output, '');
234
235 FND_FILE.put_line(FND_FILE.output, l_set_of_books ||' : '||
236 l_set_of_books_name ||
237 RPAD(' ', 128-LENGTH(l_set_of_books)-LENGTH(l_set_of_books_name)-LENGTH(l_run_date)-25, ' ' ) ||
238 l_run_date ||' : ' ||
239 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
240
241 FND_FILE.put_line(FND_FILE.output, l_oper_unit || ' : ' ||
242 l_org_name ||
243 RPAD(' ', 128-LENGTH(l_oper_unit)-LENGTH(l_org_name)-LENGTH(l_inv_ended_by)-25, ' ' ) ||
244 l_inv_ended_by ||' : ' ||
245 TO_CHAR(G_INV_ENDED_BY_DATE, 'DD-MON-YYYY HH24:MI'));
246
247 FND_FILE.put_line(FND_FILE.output,'');
248 FND_FILE.put_line(FND_FILE.output,'');
249
250 FND_FILE.put_line(FND_FILE.output, l_type ||
251 RPAD(' ',40-LENGTH(l_type),' ') ||
252 l_processed);
253
254 FND_FILE.put_line(FND_FILE.output, RPAD('-',128 ,'-'));
255
256 FND_FILE.put_line(FND_FILE.output, l_term_k ||
257 RPAD(' ',40-LENGTH(l_term_k),' ') ||
258 l_success);
259
260 FND_FILE.put_line(FND_FILE.output, l_error_k ||
261 RPAD(' ',40-LENGTH(l_error_k),' ') ||
262 l_error);
263
264 FND_FILE.put_line(FND_FILE.output,'');
265 FND_FILE.put_line(FND_FILE.output, RPAD('=',128,'=' ));
266 FND_FILE.put_line(FND_FILE.output,'');
267
268 -- Print Investor Agreements Terminated Successfully
269 IF l_success > 0 THEN
270
271 FND_FILE.put_line(FND_FILE.output, l_term_k);
272 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_term_k), '-' ));
273 FND_FILE.put_line(FND_FILE.output,'');
274
275 l_print := 'N';
276
277 FOR i IN success_message_table.FIRST..success_message_table.LAST LOOP
278
279 IF l_print = 'N' THEN
280
281 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
282 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
283 l_start_date||RPAD(' ',15-LENGTH(l_start_date),' ') ||
284 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' ') ||
285 l_status||RPAD(' ',15-LENGTH(l_status),' '));
286
287 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
288 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
289 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',15-LENGTH(l_start_date),'-') ||
290 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-') ||
291 RPAD('-',LENGTH(l_status),'-')||RPAD('-',15-LENGTH(l_status),'-'));
292
293 l_print := 'Y';
294 END IF;
295
296 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
297 success_message_table(i).contract_number ||
298 RPAD(' ',35-LENGTH(success_message_table(i).contract_number),' ')||
299 success_message_table(i).start_date||
300 RPAD(' ',15-LENGTH(success_message_table(i).start_date),' ') ||
301 success_message_table(i).end_date||
302 RPAD(' ',15-LENGTH(success_message_table(i).end_date),' ') ||
303 success_message_table(i).status||
304 RPAD(' ',15-LENGTH(success_message_table(i).status),' '));
305
306 --FND_FILE.put_line(FND_FILE.output,'');
307
308 --FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
309
310 --FOR j IN success_message_table(i).msg_tbl.FIRST..success_message_table(i).msg_tbl.LAST LOOP
311 --FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || j || ': ' || success_message_table(i).msg_tbl(j).msg);
312 --FND_FILE.put_line(FND_FILE.output,'');
313 --END LOOP;
314
315 --FND_FILE.put_line(FND_FILE.output,'');
316
317 END LOOP;
318 END IF;
319
320 FND_FILE.put_line(FND_FILE.output,'');
321
322 -- Print Investor Agreements errored
323 IF l_error > 0 THEN
324
325 FND_FILE.put_line(FND_FILE.output, l_error_k);
326 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_error_k), '-' ));
327 FND_FILE.put_line(FND_FILE.output,'');
328
329 -- SECHAWLA 26-JAN-04 3377730: Initialize the table index
330 msg_lines_table_index := 1;
331
332 FOR i IN error_message_table.FIRST..error_message_table.LAST LOOP
333
334 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
335 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
336 l_start_date||RPAD(' ',15-LENGTH(l_start_date),' ') ||
337 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' ') ||
338 l_status||RPAD(' ',15-LENGTH(l_status),' '));
339
340 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
341 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
342 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',15-LENGTH(l_start_date),'-') ||
343 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-') ||
344 RPAD('-',LENGTH(l_status),'-')||RPAD('-',15-LENGTH(l_status),'-'));
345
346 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
347 error_message_table(i).contract_number ||
348 RPAD(' ',35-LENGTH(error_message_table(i).contract_number),' ')||
349 error_message_table(i).start_date||
350 RPAD(' ',15-LENGTH(error_message_table(i).start_date),' ') ||
351 error_message_table(i).end_date||
352 RPAD(' ',15-LENGTH(error_message_table(i).end_date),' ') ||
353 error_message_table(i).status||
354 RPAD(' ',15-LENGTH(error_message_table(i).status),' '));
355
356 FND_FILE.put_line(FND_FILE.output,'');
357
358 FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
359
360 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
361 -- of db/Pl Sql Removed the msg_tbl field from message_rec_type
362 /*
363 FOR j IN error_message_table(i).msg_tbl.FIRST..error_message_table(i).msg_tbl.LAST LOOP
364 FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || j || ': ' || error_message_table(i).msg_tbl(j).msg);
365 FND_FILE.put_line(FND_FILE.output,'');
366 END LOOP;
367 */
368
369 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
370 -- of db/Pl Sql Removed the msg_tbl field from message_rec_type
371
372 FOR j IN msg_lines_table_index .. msg_lines_table.LAST LOOP
373 IF msg_lines_table(j).id = error_message_table(i).id THEN
374 FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || j || ': ' || msg_lines_table(j).msg);
375 FND_FILE.put_line(FND_FILE.output,'');
376 ELSE
377 msg_lines_table_index := j ;
378 EXIT;
379 END IF;
380
381 END LOOP;
382
383 FND_FILE.put_line(FND_FILE.output,'');
384
385 END LOOP;
386
387 END IF;
388
389 FND_FILE.put_line(FND_FILE.output,'');
390 FND_FILE.put_line(FND_FILE.output,'');
391 FND_FILE.put_line(FND_FILE.output, RPAD(' ', 53 , ' ' ) || l_eop);
392
393 ELSE -- RMUNJULU 115.4 3061748
394
395 FND_FILE.put_line(FND_FILE.log,l_processed || ' = 0');
396 FND_FILE.put_line(FND_FILE.log,l_inv);
397
398 END IF;
399 EXCEPTION
400
401 WHEN OTHERS THEN
402 -- Set the oracle error message
403 OKL_API.set_message(
404 p_app_name => G_APP_NAME_1,
405 p_msg_name => G_UNEXPECTED_ERROR,
406 p_token1 => G_SQLCODE_TOKEN,
407 p_token1_value => SQLCODE,
408 p_token2 => G_SQLERRM_TOKEN,
409 p_token2_value => SQLERRM);
410
411 END create_report;
412
413 -- Start of comments
414 --
415 -- Procedure Name : get_ia_leases
416 -- Description : procedure to get the IA Pool Leases
417 -- Business Rules :
418 -- Parameters :
419 -- Version : 1.0
420 -- History : RMUNJULU Created
421 --
422 -- End of comments
423 PROCEDURE get_ia_leases(
424 p_ia_rec IN ia_rec_type,
425 x_ia_k_tbl OUT NOCOPY ia_k_tbl_type,
426 x_return_status OUT NOCOPY VARCHAR2) IS
427
428 -- Get leases of the IA Active Pool
429 CURSOR get_ia_k_csr( p_ia_id IN NUMBER) IS
430 SELECT DISTINCT CHR.id,
431 CHR.contract_number,
432 CHR.start_date,
433 CHR.end_date,
434 CHR.sts_code,
435 CHR.date_terminated
436 FROM OKL_POOLS POL,
437 OKL_POOL_CONTENTS POC,
438 OKC_K_HEADERS_B KHR,
439 OKC_K_HEADERS_B CHR
440 WHERE KHR.id = p_ia_id
441 AND KHR.id = POL.khr_id
442 AND POL.id = POC.pol_id
443 AND POL.status_code = 'ACTIVE' -- Pool status
444 AND POC.status_code = POL.status_code
445 AND POC.khr_id = CHR.id;
446
447 i NUMBER := 1;
448 l_ia_k_tbl ia_k_tbl_type;
449 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
450
451 BEGIN
452
453 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
454 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
455 'OKL_AM_TERMINATE_INV_AGMT_PVT.get_ia_leases.',
456 'Begin(+)');
457 END IF;
458
459 -- Populate the Lease tbl
460 FOR get_ia_k_rec IN get_ia_k_csr (p_ia_rec.id) LOOP
461
462 l_ia_k_tbl(i).id := get_ia_k_rec.id;
463 l_ia_k_tbl(i).contract_number := get_ia_k_rec.contract_number;
464 l_ia_k_tbl(i).start_date := get_ia_k_rec.start_date;
465 l_ia_k_tbl(i).end_date := get_ia_k_rec.end_date;
466 l_ia_k_tbl(i).sts_code := get_ia_k_rec.sts_code;
467 l_ia_k_tbl(i).date_terminated := get_ia_k_rec.date_terminated;
468
469 i := i + 1;
470
471 END LOOP;
472
473 x_return_status := l_return_status;
474 x_ia_k_tbl := l_ia_k_tbl;
475
476 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
478 'OKL_AM_TERMINATE_INV_AGMT_PVT.get_ia_leases.',
479 'End(-)');
480 END IF;
481
482 EXCEPTION
483
484 WHEN OTHERS THEN
485 -- Set the oracle error message
486 OKL_API.set_message(
487 p_app_name => G_APP_NAME_1,
488 p_msg_name => G_UNEXPECTED_ERROR,
489 p_token1 => G_SQLCODE_TOKEN,
490 p_token1_value => SQLCODE,
491 p_token2 => G_SQLERRM_TOKEN,
492 p_token2_value => SQLERRM);
493
494 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
495 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
496 'OKL_AM_TERMINATE_INV_AGMT_PVT.get_ia_leases.',
497 'EXP - OTHERS');
498 END IF;
499
500 END get_ia_leases;
501
502 -- Start of comments
503 --
504 -- Procedure Name : validate_ia_pool
505 -- Description : Checks Investor Agreement Pool contents and Pool streams valid
506 -- Business Rules :
507 -- Parameters :
508 -- Version : 1.0
509 -- History : RMUNJULU Created
510 --
511 -- End of comments
512 PROCEDURE validate_ia_pool(
513 p_ia_rec IN ia_rec_type,
514 x_return_status OUT NOCOPY VARCHAR2) IS
515
516 -- Check if any ACTIVE pool contents for the IA which are are end_dated after
517 -- IA end_date or not end_dated at all
518 CURSOR check_ia_poc_date_csr (p_ia_id IN NUMBER) IS
519 SELECT 1 id
520 FROM DUAL WHERE EXISTS (
521 SELECT POC.id
522 FROM OKL_POOLS POL,
523 OKL_POOL_CONTENTS POC,
524 OKC_K_HEADERS_B CHR
525 WHERE CHR.id = p_ia_id
526 AND CHR.id = POL.khr_id
527 AND POL.id = POC.pol_id
528 AND NVL(POC.streams_to_date, CHR.end_date+1) > CHR.end_date
529 AND POL.status_code = 'ACTIVE'
530 AND POC.status_code = POL.status_code);
531
532 -- Check if any CURRENT ACTIVE BILLABLE stream elements of IA pools
533 -- which are dated after the IA end_date
534 CURSOR check_ia_sel_date_csr( p_ia_id IN NUMBER) IS
535 SELECT 1 id
536 FROM DUAL WHERE EXISTS (
537 SELECT SEL.id
538 FROM OKL_STREAMS STM,
539 OKL_STRM_ELEMENTS SEL,
540 OKL_STRM_TYPE_B STY,
541 OKL_POOLS POL,
542 OKL_POOL_CONTENTS POC,
543 OKC_K_HEADERS_B CHR
544 WHERE CHR.id = p_ia_id
545 AND CHR.id = POL.khr_id
546 AND POL.id = POC.pol_id
547 AND POL.status_code = 'ACTIVE'
548 AND POC.status_code = POL.status_code
549 AND POC.sty_id = STM.sty_id
550 AND STM.id = SEL.stm_id
551 AND SEL.stream_element_date > CHR.end_date
552 AND POC.kle_id = STM.kle_id
553 AND STM.say_code = 'CURR' -- CURRENT
554 AND STM.active_yn = G_YES -- ACTIVE
555 AND STM.sty_id = STY.id
556 AND NVL(STY.billable_yn,G_NO) = G_YES); -- BILLABLE
557
558 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
559 l_poc_id NUMBER := G_MISS_NUM;
560 l_sel_id NUMBER := G_MISS_NUM;
561
562 BEGIN
563
564 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
565 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
566 'OKL_AM_TERMINATE_INV_AGMT_PVT.validate_ia_pool.',
567 'Begin(+)');
568 END IF;
569
570 SAVEPOINT validate_ia_pool_trx;
571
572 -- **********
573 -- Check if IA Pool Contents valid
574 -- **********
575
576 -- Get the invalid Pool Contents
577 FOR check_ia_poc_date_rec IN check_ia_poc_date_csr(p_ia_rec.id) LOOP
578 l_poc_id := check_ia_poc_date_rec.id;
579 END LOOP;
580
581
582 IF l_poc_id = 1 THEN
583 -- Pool contents exist after the Investor Agreement AGREEMENT_NUMBER
584 -- end date END_DATE.
585 OKL_API.set_message(
586 p_app_name => G_APP_NAME,
587 p_msg_name => 'OKL_AM_INV_POC_DATE',
588 p_token1 => 'AGREEMENT_NUMBER',
589 p_token1_value => p_ia_rec.contract_number,
590 p_token2 => 'END_DATE',
591 p_token2_value => p_ia_rec.end_date);
592
593 RAISE G_EXCEPTION_ERROR;
594 END IF;
595
596 -- **********
597 -- Check if IA Pool Stream Elements valid
598 -- **********
599
600 -- Get the invalid Pool Stream Elements
601 FOR check_ia_sel_date_rec IN check_ia_sel_date_csr(p_ia_rec.id) LOOP
602 l_sel_id := check_ia_sel_date_rec.id;
603 END LOOP;
604
605 IF l_sel_id = 1 THEN
606 -- Streams associated with Investor Agreement AGREEMENT_NUMBER has
607 -- due date after end date END_DATE.
608 OKL_API.set_message(
609 p_app_name => G_APP_NAME,
610 p_msg_name => 'OKL_AM_INV_SEL_DATE',
611 p_token1 => 'AGREEMENT_NUMBER',
612 p_token1_value => p_ia_rec.contract_number,
613 p_token2 => 'END_DATE',
614 p_token2_value => p_ia_rec.end_date);
615
616 RAISE G_EXCEPTION_ERROR;
617 END IF;
618
619 -- Set return status
620 x_return_status := l_return_status;
621
622 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
623 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
624 'OKL_AM_TERMINATE_INV_AGMT_PVT.validate_ia_pool.',
625 'End(-)');
626 END IF;
627
628 EXCEPTION
629
630 WHEN G_EXCEPTION_ERROR THEN
631 ROLLBACK TO validate_ia_pool_trx;
632 x_return_status := G_RET_STS_ERROR;
633
634 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
636 'OKL_AM_TERMINATE_INV_AGMT_PVT.validate_ia_pool.',
637 'EXP - G_EXCEPTION_ERROR');
638 END IF;
639
640 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
641 ROLLBACK TO validate_ia_pool_trx;
642 x_return_status := G_RET_STS_UNEXP_ERROR;
643
644 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
646 'OKL_AM_TERMINATE_INV_AGMT_PVT.validate_ia_pool.',
647 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
648 END IF;
649
650 WHEN OTHERS THEN
651 ROLLBACK TO validate_ia_pool_trx;
652
653 -- Set the oracle error message
654 OKL_API.set_message(
655 p_app_name => G_APP_NAME_1,
656 p_msg_name => G_UNEXPECTED_ERROR,
657 p_token1 => G_SQLCODE_TOKEN,
658 p_token1_value => SQLCODE,
659 p_token2 => G_SQLERRM_TOKEN,
660 p_token2_value => SQLERRM);
661
662 x_return_status := G_RET_STS_UNEXP_ERROR;
663
664 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
665 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
666 'OKL_AM_TERMINATE_INV_AGMT_PVT.validate_ia_pool.',
667 'EXP - OTHERS');
668 END IF;
669
670 END validate_ia_pool;
671
672 -- Start of comments
673 --
674 -- Procedure Name : check_unbilled_streams_of_pool
675 -- Description : procedure to check if any unbilled streams of investor agreement
676 -- Business Rules :
677 -- Parameters :
678 -- Version : 1.0
679 -- History : RMUNJULU Created
680 --
681 -- End of comments
682 PROCEDURE check_unbilled_streams_of_pool(
683 p_ia_rec IN ia_rec_type,
684 x_return_status OUT NOCOPY VARCHAR2) IS
685
686 -- Get unbilled stream elements
687 CURSOR get_unbilled_sel_csr( p_ia_id IN NUMBER) IS
688 SELECT 1 id
689 FROM DUAL WHERE EXISTS (
690 SELECT SEL.id
691 FROM OKL_STREAMS STM,
692 OKL_STRM_ELEMENTS SEL,
693 OKL_STRM_TYPE_B STY,
694 OKL_POOLS POL,
695 OKL_POOL_CONTENTS POC,
696 OKC_K_HEADERS_B KHR
697 WHERE KHR.id = p_ia_id
698 AND KHR.id = POL.khr_id
699 AND POL.id = POC.pol_id
700 AND POL.status_code = 'ACTIVE'
701 AND POC.status_code = POL.status_code
702 AND POC.sty_id = STM.sty_id
703 AND STM.id = SEL.stm_id
704 AND (SEL.stream_element_date BETWEEN POC.streams_from_date
705 AND POC.streams_to_date)
706 AND POC.kle_id = STM.kle_id
707 AND STM.say_code = 'CURR' -- CURRENT
708 AND STM.active_yn = G_YES -- ACTIVE
709 AND SEL.date_billed IS NULL -- Not billed
710 AND STM.sty_id = STY.id
711 AND NVL(STY.billable_yn,G_NO) = G_YES); -- BILLABLE
712
713 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
714 l_strm_id NUMBER;
715
716 BEGIN
717
718 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
720 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_unbilled_streams_of_pool.',
721 'Begin(+)');
722 END IF;
723
724 -- *********
725 -- Unbilled Stream elements
726 -- *********
727
728 SAVEPOINT check_unbilled_trx;
729
730 -- Get the unbilled stream elements
731 FOR get_unbilled_sel_rec IN get_unbilled_sel_csr(p_ia_rec.id) LOOP
732 l_strm_id := get_unbilled_sel_rec.id;
733 END LOOP;
734
735 -- If unbilled stream elements then error
736 IF l_strm_id = 1 THEN
737
738 -- Streams associated with Investor Agreement AGREEMENT_NUMBER have
739 -- not been billed.
740 OKL_API.set_message(
741 p_app_name => G_APP_NAME,
742 p_msg_name => 'OKL_AM_INV_UNBILL_STRM',
743 p_token1 => 'AGREEMENT_NUMBER',
744 p_token1_value => p_ia_rec.contract_number);
745
746 RAISE G_EXCEPTION_ERROR;
747
748 END IF;
749
750 -- Set return status
751 x_return_status := l_return_status;
752
753 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
755 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_unbilled_streams_of_pool.',
756 'End(-)');
757 END IF;
758
759 EXCEPTION
760
761 WHEN G_EXCEPTION_ERROR THEN
762
763 ROLLBACK TO check_unbilled_trx;
764 x_return_status := G_RET_STS_ERROR;
765
766 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
767 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
768 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_unbilled_streams_of_pool.',
769 'EXP - G_EXCEPTION_ERROR');
770 END IF;
771
772 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
773
774 ROLLBACK TO check_unbilled_trx;
775 x_return_status := G_RET_STS_UNEXP_ERROR;
776
777 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
778 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
779 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_unbilled_streams_of_pool.',
780 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
781 END IF;
782
783 WHEN OTHERS THEN
784
785 ROLLBACK TO check_unbilled_trx;
786
787 -- Set the oracle error message
788 OKL_API.set_message(
789 p_app_name => G_APP_NAME_1,
790 p_msg_name => G_UNEXPECTED_ERROR,
791 p_token1 => G_SQLCODE_TOKEN,
792 p_token1_value => SQLCODE,
793 p_token2 => G_SQLERRM_TOKEN,
794 p_token2_value => SQLERRM);
795
796 x_return_status := G_RET_STS_UNEXP_ERROR;
797
798 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
800 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_unbilled_streams_of_pool.',
801 'EXP - OTHERS');
802 END IF;
803
804 END check_unbilled_streams_of_pool;
805
806 -- Start of comments
807 --
808 -- Procedure Name : check_pending_disb_for_ia
809 -- Description : procedure to check if any pending disbursements for the
810 -- Investor Agreement
811 -- Business Rules :
812 -- Parameters :
813 -- Version : 1.0
814 -- History : RMUNJULU Created
815 --
816 -- End of comments
817 PROCEDURE check_pending_disb_for_ia(
818 p_ia_rec IN ia_rec_type,
819 x_return_status OUT NOCOPY VARCHAR2) IS
820
821 -- stream type INVESTOR RENT DISBURSEMENT BASIS streams generated during
822 -- activation of investor agreement
823 -- stream type INVESTOR RENT PAYABLE streams generated when disbursement of
824 -- INVESTOR RENT DISBURSEMENT BASIS streams is done
825 -- some of stream type subclass INVESTOR_DISBURSEMENT streams generated when
826 -- disbursement of INVESTOR RENT DISBURSEMENT BASIS streams is done
827 -- some of stream type subclass INVESTOR_DISBURSEMENT streams generated when
828 -- early termination of contract which is securitized is done
829
830 -- stream_type_subclass INVESTOR_DISBURSEMENT should cover following stream types
831 --
832 -- INVESTOR CONTRACT OBLIGATION PAYABLE
833 -- Stream created by AM for payment of rent amount on termination.
834 -- INVESTOR RESIDUAL PAYABLE
835 -- Stream created by AM for payment of residual amount on termination.
836 -- INVESTOR LATE CHARGE PAYABLE
837 -- Stream created by BPD for payment of late charge to investor.
838 -- INVESTOR LATE FEE PAYABLE
839 -- Stream created by BPD for payment of late interest to investor.
840 -- INVESTOR RENT BUYBACK
841 -- Stream created by Securitization for payment of rent buy back to investor.
842 -- INVESTOR RESIDUAL BUYBACK
843 -- Stream created by Securitization for payment of residual buy back to investor.
844
845 -- Get the undisbursed streams for IA
846 -- RMUNJULU 21-OCT-03 3061748 Changed the cursor to look at right source_id
847 -- SMODUGA 11-Oct-04 Bug 3925469
848 -- Modified cursor by passing sty_id based on the stream purpose
849 CURSOR get_undisb_sel_csr( p_ia_id IN NUMBER,p_invdisbas_sty_id IN NUMBER,p_invpbl_sty_id IN NUMBER,
850 p_prindisbas_sty_id IN NUMBER,p_prinpbl_sty_id IN NUMBER,
851 p_intdisbas_sty_id IN NUMBER,p_intpbl_sty_id IN NUMBER,
852 p_ppddisbas_sty_id IN NUMBER,p_ppdpbl_sty_id IN NUMBER) IS
853 SELECT 1 id
854 FROM DUAL WHERE EXISTS (
855 SELECT
856 ste.id sel_id
857 FROM
858 okl_strm_elements ste,
859 okl_streams stm,
860 okl_strm_type_v sty
861 WHERE ste.amount <> 0
862 AND stm.id = ste.stm_id
863 AND sty.stream_type_subclass IS NULL
864 AND sty.id IN (p_invdisbas_sty_id,p_invpbl_sty_id,p_prindisbas_sty_id,
865 p_prinpbl_sty_id,p_intdisbas_sty_id,p_intpbl_sty_id, p_ppddisbas_sty_id,p_ppdpbl_sty_id)
866 AND ste.date_billed IS NULL -- Once disb is done date_billed is populated
867 AND stm.active_yn = 'Y'
868 AND stm.say_code = 'CURR'
869 AND sty.id = stm.sty_id
870 AND sty.billable_yn = 'N'
871 AND stm.source_id = p_ia_id); -- Investor Agreement is now stored on disb stream
872
873 -- Get the undisbursed streams for IA
874 -- RMUNJULU 21-OCT-03 3061748 Changed the cursor to look at right source_id
875 CURSOR get_undisb_csr( p_ia_id IN NUMBER) IS
876 SELECT 1 id
877 FROM DUAL WHERE EXISTS (
878 SELECT
879 ste.id sel_id
880 FROM
881 okl_strm_elements ste,
882 okl_streams stm,
883 okl_strm_type_v sty
884 WHERE ste.amount <> 0
885 AND stm.id = ste.stm_id
886 AND sty.stream_type_subclass = 'INVESTOR_DISBURSEMENT'
887 AND ste.date_billed IS NULL -- Once disb is done date_billed is populated
888 AND stm.active_yn = 'Y'
889 AND stm.say_code = 'CURR'
890 AND sty.id = stm.sty_id
891 AND sty.billable_yn = 'N'
892 AND stm.source_id = p_ia_id); -- Investor Agreement is now stored on disb stream
893
894 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
895 l_disb_id NUMBER ;
896 l_ia_k_tbl ia_k_tbl_type;
897 l_contract_number VARCHAR2(300);
898
899 -- SMODUGA added variable for userdefined streams 3925469
900 lx_invdisbas_sty_id NUMBER;
901 lx_invpbl_sty_id NUMBER;
902
903 -- sosharma added variable for loan disbersement stream types
904 lx_prindisbas_sty_id NUMBER;
905 lx_prinpbl_sty_id NUMBER;
906
907 lx_intdisbas_sty_id NUMBER;
908 lx_intpbl_sty_id NUMBER;
909
910 lx_ppddisbas_sty_id NUMBER;
911 lx_ppdpbl_sty_id NUMBER;
912
913
914 BEGIN
915
916 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
917 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
918 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_pending_disb_for_ia.',
919 'Begin(+)');
920 END IF;
921
922 -- *********
923 -- Pending Disbursements
924 -- *********
925
926 SAVEPOINT check_pending_disb_trx;
927
928 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
929 /* OKL_STREAMS_UTIL.get_dependent_stream_type(p_ia_rec.id,
930 'RENT',
931 'INVESTOR_RENT_DISBURSEMENT_BASIS',
932 l_return_status,
933 lx_invdisbas_sty_id);
934
935 OKL_STREAMS_UTIL.get_dependent_stream_type(p_ia_rec.id,
936 'RENT',
937 'INVESTOR_RENT_PAYABLE',
938 l_return_status,
939 lx_invpbl_sty_id);
940 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++ */
941
942 -- gkadarka fix for bug 4609338 - start
943 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
944 'INVESTOR_RENT_DISB_BASIS',-- 'INVESTOR_RENT_DISBURSEMENT_BASIS', --GKADARKA CHANGED FOR TEST
945 l_return_status,
946 lx_invdisbas_sty_id);
947
948 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
949 'INVESTOR_RENT_PAYABLE',
950 l_return_status,
951 lx_invpbl_sty_id);
952 /*Sosharma
953 14-01-2008
954 Changes to included loan type of contracts in Investor Agreement
955 Start Changes*/
956 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
957 'INVESTOR_PRINCIPAL_DISB_BASIS',-- 'INVESTOR_LOAN_DISBURSEMENT_BASIS', --GKADARKA CHANGED FOR TEST
958 l_return_status,
959 lx_prindisbas_sty_id);
960
961 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
962 'INVESTOR_INTEREST_DISB_BASIS',-- 'INVESTOR_LOAN_DISBURSEMENT_BASIS', --GKADARKA CHANGED FOR TEST
963 l_return_status,
964 lx_intdisbas_sty_id);
965
966
967 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
968 'INVESTOR_PRINCIPAL_PAYABLE',
969 l_return_status,
970 lx_prinpbl_sty_id);
971
972 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
973 'INVESTOR_INTEREST_PAYABLE',
974 l_return_status,
975 lx_intpbl_sty_id);
976
977
978 -- Principal Paydown streams
979
980 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
981 'INVESTOR_PPD_DISB_BASIS',
982 l_return_status,
983 lx_ppddisbas_sty_id);
984 OKL_STREAMS_UTIL.get_primary_stream_type(p_ia_rec.id,
985 'INVESTOR_PAYDOWN_PAYABLE',
986 l_return_status,
987 lx_ppdpbl_sty_id);
988
989
990
991 -- gkadarka fix for bug 4609338 - End
992
993
994 -- Get all stream elements which have not been disbursed for the Lease
995 FOR get_undisb_sel_rec IN get_undisb_sel_csr(p_ia_rec.id,lx_invdisbas_sty_id,lx_invpbl_sty_id,
996 lx_prindisbas_sty_id,lx_prinpbl_sty_id,lx_intdisbas_sty_id,lx_intpbl_sty_id ,lx_ppddisbas_sty_id,lx_ppdpbl_sty_id) LOOP
997 l_disb_id := get_undisb_sel_rec.id;
998 END LOOP;
999 /* sosharma end changes */
1000 -- If undisbursed stream elements then error
1001 IF l_disb_id = 1 THEN
1002 --Pending disbursements exists for the investor agreement AGREEMENT_NUMBER.
1003 OKL_API.set_message(
1004 p_app_name => G_APP_NAME,
1005 p_msg_name => 'OKL_AM_INV_PENDING_DISB',
1006 p_token1 => 'AGREEMENT_NUMBER',
1007 p_token1_value => p_ia_rec.contract_number);
1008
1009 RAISE G_EXCEPTION_ERROR;
1010 END IF;
1011
1012 -- Get all stream elements which have not been disbursed for the Lease
1013 FOR get_undisb_rec IN get_undisb_csr(p_ia_rec.id) LOOP
1014 l_disb_id := get_undisb_rec.id;
1015 END LOOP;
1016
1017 -- If undisbursed stream elements then error
1018 IF l_disb_id = 1 THEN
1019 --Pending disbursements exists for the investor agreement AGREEMENT_NUMBER.
1020 OKL_API.set_message(
1021 p_app_name => G_APP_NAME,
1022 p_msg_name => 'OKL_AM_INV_PENDING_DISB',
1023 p_token1 => 'AGREEMENT_NUMBER',
1024 p_token1_value => p_ia_rec.contract_number);
1025
1026 RAISE G_EXCEPTION_ERROR;
1027 END IF;
1028
1029 -- Set return status
1030 x_return_status := l_return_status;
1031
1032 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1033 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1034 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_pending_disb_for_ia.',
1035 'End(-)');
1036 END IF;
1037
1038 EXCEPTION
1039
1040 WHEN G_EXCEPTION_ERROR THEN
1041
1042 ROLLBACK TO check_pending_disb_trx;
1043 x_return_status := G_RET_STS_ERROR;
1044
1045 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1046 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1047 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_pending_disb_for_ia.',
1048 'EXP - G_EXCEPTION_ERROR');
1049 END IF;
1050
1051 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1052
1053 ROLLBACK TO check_pending_disb_trx;
1054 x_return_status := G_RET_STS_UNEXP_ERROR;
1055
1056 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1057 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1058 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_pending_disb_for_ia.',
1059 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1060 END IF;
1061
1062 WHEN OTHERS THEN
1063
1064 ROLLBACK TO check_pending_disb_trx;
1065
1066 -- Set the oracle error message
1067 OKL_API.set_message(
1068 p_app_name => G_APP_NAME_1,
1069 p_msg_name => G_UNEXPECTED_ERROR,
1070 p_token1 => G_SQLCODE_TOKEN,
1071 p_token1_value => SQLCODE,
1072 p_token2 => G_SQLERRM_TOKEN,
1073 p_token2_value => SQLERRM);
1074
1075 x_return_status := G_RET_STS_UNEXP_ERROR;
1076
1077 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1079 'OKL_AM_TERMINATE_INV_AGMT_PVT.check_pending_disb_for_ia.',
1080 'EXP - OTHERS');
1081 END IF;
1082
1083 END check_pending_disb_for_ia;
1084
1085 -- Start of comments
1086 --
1087 -- Procedure Name : pop_investor_agreement
1088 -- Description : procedure to populate investor agreement details
1089 -- Business Rules :
1090 -- Parameters :
1091 -- Version : 1.0
1092 -- History : RMUNJULU Created
1093 --
1094 -- End of comments
1095 PROCEDURE pop_investor_agreement(
1096 p_ia_rec IN ia_rec_type,
1097 x_ia_rec OUT NOCOPY ia_rec_type,
1098 x_return_status OUT NOCOPY VARCHAR2) IS
1099
1100 -- Get the details of the IA
1101 CURSOR get_ia_details_csr (p_ia_id IN NUMBER) IS
1102 SELECT CHR.id,
1103 CHR.contract_number,
1104 CHR.START_DATE,
1105 CHR.end_date,
1106 CHR.sts_code, -- Should be ACTIVE
1107 CHR.scs_code, -- should be INVESTOR
1108 KHR.pdt_id,
1109 POL.id pool_id,
1110 POL.pool_number
1111 FROM OKC_K_HEADERS_B CHR,
1112 OKL_K_HEADERS KHR,
1113 OKL_POOLS POL
1114 WHERE CHR.id = p_ia_id
1115 AND CHR.id = KHR.id
1116 AND CHR.id = POL.khr_id;
1117
1118 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1119 l_ia_number VARCHAR2(300);
1120 l_ia_id NUMBER;
1121 l_pdt_id NUMBER;
1122 l_start_date DATE;
1123 l_end_date DATE;
1124 l_type VARCHAR2(300);
1125 l_status VARCHAR2(300);
1126
1127 BEGIN
1128
1129 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1130 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1131 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_investor_agreement.',
1132 'Begin(+)');
1133 END IF;
1134
1135 -- **********
1136 -- Get IA details
1137 -- **********
1138
1139 -- Get the K details
1140 FOR get_ia_details_rec IN get_ia_details_csr(p_ia_rec.id) LOOP
1141 x_ia_rec.id := get_ia_details_rec.id;
1142 x_ia_rec.contract_number := get_ia_details_rec.contract_number;
1143 x_ia_rec.START_DATE := get_ia_details_rec.start_date;
1144 x_ia_rec.end_date := get_ia_details_rec.end_date;
1145 x_ia_rec.sts_code := get_ia_details_rec.sts_code;
1146 x_ia_rec.scs_code := get_ia_details_rec.scs_code;
1147 x_ia_rec.pdt_id := get_ia_details_rec.pdt_id;
1148 x_ia_rec.pool_id := get_ia_details_rec.pool_id;
1149 x_ia_rec.pool_number := get_ia_details_rec.pool_number;
1150 END LOOP;
1151
1152 -- Set return values
1153 x_return_status := l_return_status;
1154
1155 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1157 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_investor_agreement.',
1158 'End(-)');
1159 END IF;
1160
1161 EXCEPTION
1162
1163 WHEN OTHERS THEN
1164
1165 -- Set the oracle error message
1166 OKL_API.set_message(
1167 p_app_name => G_APP_NAME_1,
1168 p_msg_name => G_UNEXPECTED_ERROR,
1169 p_token1 => G_SQLCODE_TOKEN,
1170 p_token1_value => SQLCODE,
1171 p_token2 => G_SQLERRM_TOKEN,
1172 p_token2_value => SQLERRM);
1173
1174 x_return_status := G_RET_STS_UNEXP_ERROR;
1175
1176 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1177 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1178 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_investor_agreement.',
1179 'EXP - OTHERS');
1180 END IF;
1181
1182 END pop_investor_agreement;
1183
1184 -- Start of comments
1185 --
1186 -- Procedure Name : val_pop_investor_agreement
1187 -- Description : procedure to validate investor agreement and Populate
1188 -- IA Lines
1189 -- Business Rules :
1190 -- Parameters :
1191 -- Version : 1.0
1192 -- History : RMUNJULU Created
1193 --
1194 -- End of comments
1195 PROCEDURE val_pop_investor_agreement(
1196 p_ia_rec IN ia_rec_type,
1197 x_ia_rec OUT NOCOPY ia_rec_type,
1198 x_ialn_tbl OUT NOCOPY ialn_tbl_type,
1199 x_return_status OUT NOCOPY VARCHAR2) IS
1200
1201 -- Get the IA TRN for Termination ie TCN_TYPE = 'IAT'
1202 CURSOR get_trn_csr (p_ia_id IN NUMBER) IS
1203 SELECT TRN.tsu_code
1204 FROM OKL_TRX_CONTRACTS TRN
1205 WHERE TRN.khr_id = p_ia_id
1206 --rkuttiya added for 12.1.1 Multi GAAP
1207 AND TRN.representation_type = 'PRIMARY'
1208 --
1209 AND TRN.tcn_type = 'IAT';
1210
1211 -- Get the IA Lines
1212 CURSOR get_ia_lines_csr (p_ia_id IN NUMBER) IS
1213 SELECT CLE.id,
1214 CLE.name,
1215 CLE.sts_code
1216 FROM OKC_K_HEADERS_B CHR,
1217 OKC_K_LINES_V CLE
1218 WHERE CHR.id = p_ia_id
1219 AND CHR.id = CLE.dnz_chr_id
1220 AND CLE.sts_code = CHR.sts_code;
1221
1222 /*sosharma 14-01-2008
1223 Cursor to validate if transiend pool contents are present for the IA being terminated
1224 Start Changes*/
1225
1226 CURSOR get_trans_pox_cont_csr (p_ia_id IN NUMBER) IS
1227 SELECT POX.id
1228 FROM OKL_POOL_TRANSACTIONS POX,
1229 OKL_POOLS POL
1230 WHERE POL.id=POX.pol_id
1231 AND POX.transaction_status <> 'COMPLETE'
1232 AND POL.khr_id =p_ia_id
1233 AND POX.transaction_type='ADD'
1234 AND POX.transaction_reason='ADJUSTMENTS';
1235
1236 /* sosharma end changes*/
1237
1238 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1239 l_ia_number VARCHAR2(300);
1240 l_start_date DATE;
1241 l_end_date DATE;
1242 l_status VARCHAR2(300);
1243 l_type VARCHAR2(300);
1244 l_pdt_id NUMBER;
1245 l_ialn_tbl ialn_tbl_type;
1246 i NUMBER := 0;
1247 l_tsu_code VARCHAR2(300);
1248 l_ia_rec ia_rec_type;
1249 l_trans_pool_id NUMBER;
1250
1251 BEGIN
1252
1253 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1254 -- Begin Logic
1255 -- 0 Get the IA details
1256 -- 1.1 Throw error if IA ID/Number not valid
1257 -- 1.2 Throw error if IA type not valid
1258 -- 1.3 Throw error if IA End Date not valid
1259 -- 1.4 Throw error if IA Trn is already Processed
1260 -- 2.1 Throw error if any billable stream elements of IA pools end dated
1261 -- after the IA end date
1262 -- 2.2 Throw error if any pool contents of IA pool end dated after the IA
1263 -- end date
1264 -- 3. Throw error if any unbilled stream elements of the IA pool exists
1265 -- 4. Throw error if any pending disbursements for IA exists.
1266 -- 5. Throw error if any undisbursed stream elements of the lease contracts
1267 -- of IA exists - these are the new disbursement streams created when
1268 -- lease is securitized and terminated
1269 -- 6. Get IA lines
1270 --
1271 -- End Logic
1272 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1273
1274 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1275 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1276 'OKL_AM_TERMINATE_INV_AGMT_PVT.val_pop_investor_agreement.',
1277 'Begin(+)');
1278 END IF;
1279
1280 SAVEPOINT validate_ia_trx;
1281
1282 -- *************
1283 -- Populate IA Values
1284 -- *************
1285
1286 pop_investor_agreement(
1287 p_ia_rec => p_ia_rec,
1288 x_ia_rec => l_ia_rec,
1289 x_return_status => l_return_status);
1290
1291 -- **********
1292 -- Check if IA valid
1293 -- **********
1294
1295 -- invalid id
1296 IF l_ia_rec.id IS NULL
1297 OR l_ia_rec.contract_number IS NULL THEN
1298
1299 -- Invalid Value
1300 OKL_API.set_message(
1301 p_app_name => G_APP_NAME_1,
1302 p_msg_name => G_INVALID_VALUE,
1303 p_token1 => G_COL_NAME_TOKEN,
1304 p_token1_value => 'id');
1305
1306 RAISE G_EXCEPTION_ERROR;
1307
1308 END IF;
1309
1310 -- If not investor agreeement
1311 IF l_ia_rec.scs_code <> 'INVESTOR' THEN
1312
1313 -- AGREEMENT_NUMBER is not a valid Investor Agreement.
1314 OKL_API.set_message(
1315 p_app_name => G_APP_NAME,
1316 p_msg_name => 'OKL_AM_INV_NOT_INV',
1317 p_token1 => 'AGREEMENT_NUMBER',
1318 p_token1_value => l_ia_rec.contract_number);
1319
1320 RAISE G_EXCEPTION_ERROR;
1321
1322 END IF;
1323
1324 -- If not active
1325 IF l_ia_rec.sts_code <> 'ACTIVE' THEN
1326
1327 -- Investor Agreement AGREEMENT_NUMBER is not in active status.
1328 OKL_API.set_message(
1329 p_app_name => G_APP_NAME,
1330 p_msg_name => 'OKL_AM_INV_NOT_ACTIVE',
1331 p_token1 => 'AGREEMENT_NUMBER',
1332 p_token1_value => l_ia_rec.contract_number);
1333
1334 RAISE G_EXCEPTION_ERROR;
1335
1336 END IF;
1337
1338 -- If not end dated
1339 IF l_ia_rec.end_date IS NULL THEN
1340
1341 -- End date is not available for Investor Agreement AGREEMENT_NUMBER.
1342 OKL_API.set_message(
1343 p_app_name => G_APP_NAME,
1344 p_msg_name => 'OKL_AM_INV_NOT_ENDED',
1345 p_token1 => 'AGREEMENT_NUMBER',
1346 p_token1_value => l_ia_rec.contract_number);
1347
1348 RAISE G_EXCEPTION_ERROR;
1349
1350 END IF;
1351
1352 -- Get TRN Details
1353 FOR get_trn_rec IN get_trn_csr(l_ia_rec.id) LOOP
1354
1355 l_tsu_code := get_trn_rec.tsu_code;
1356
1357 END LOOP;
1358
1359 -- If TRN exists and was PROCESSED then error
1360 IF l_tsu_code = 'PROCESSED' THEN
1361 -- The transaction status and agreement status are mismatched for
1362 -- Investor Agreement AGREEMENT_NUMBER.
1363 OKL_API.set_message(
1364 p_app_name => G_APP_NAME,
1365 p_msg_name => 'OKL_AM_INV_PRS_TRN_EXIST',
1366 p_token1 => 'AGREEMENT_NUMBER',
1367 p_token1_value => l_ia_rec.contract_number);
1368
1369 RAISE G_EXCEPTION_ERROR;
1370 END IF;
1371
1372 /*sosharma 14-01-2008
1373 Validate for condition - transient pool contents are present for the IA being terminated
1374 Start Changes*/
1375
1376 FOR get_trans_pox_cont_rec IN get_trans_pox_cont_csr(l_ia_rec.id) LOOP
1377
1378 l_trans_pool_id := get_trans_pox_cont_rec.id;
1379
1380 END LOOP;
1381 IF l_trans_pool_id is not null THEN
1382
1383 -- Pending pool contents and transactions are present
1384 OKL_API.set_message(
1385 p_app_name => G_APP_NAME,
1386 p_msg_name => 'OKL_AM_INV_PEND_REQ_EXIST',
1387 p_token1 => 'AGREEMENT_NUMBER',
1388 p_token1_value => l_ia_rec.contract_number);
1389
1390 RAISE G_EXCEPTION_ERROR;
1391
1392 END IF;
1393
1394 -- **********
1395 -- Validate IA Pool Contents
1396 -- Throw error if any billable stream elements of IA pools end dated after
1397 -- the IA end date
1398 -- Throw error if any pool contents of IA pool end dated after the IA end date
1399 -- **********
1400
1401 validate_ia_pool(
1402 p_ia_rec => l_ia_rec,
1403 x_return_status => l_return_status);
1404
1405 -- raise exception if api returns error
1406 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1407 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1408 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1409 RAISE G_EXCEPTION_ERROR;
1410 END IF;
1411
1412 -- **********
1413 -- Check Unbilled Streams of Pool
1414 -- Throw error if any unbilled stream elements of the IA pool exists
1415 -- **********
1416
1417 check_unbilled_streams_of_pool(
1418 p_ia_rec => l_ia_rec,
1419 x_return_status => l_return_status);
1420
1421 -- raise exception if api returns error
1422 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1423 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1424 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1425 RAISE G_EXCEPTION_ERROR;
1426 END IF;
1427
1428 -- *************
1429 -- Check Pending Disbursements for IA
1430 -- Throw error if any pending disbursements for IA exists.
1431 -- *************
1432
1433 check_pending_disb_for_ia(
1434 p_ia_rec => l_ia_rec,
1435 x_return_status => l_return_status);
1436
1437 -- raise exception if api returns error
1438 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1439 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1440 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1441 RAISE G_EXCEPTION_ERROR;
1442 END IF;
1443
1444 -- *********
1445 -- Populate the IA Lines
1446 -- *********
1447
1448 i := 1;
1449 FOR get_ia_lines_rec IN get_ia_lines_csr (l_ia_rec.id ) LOOP
1450
1451 -- If ACTIVE line then Add to IA Lines table
1452 l_ialn_tbl(i).id := get_ia_lines_rec.id;
1453 l_ialn_tbl(i).name := get_ia_lines_rec.name;
1454 i := i + 1;
1455
1456 END LOOP;
1457
1458 -- Set the success message
1459 -- Investor Agreement AGREEMENT_NUMBER is valid.
1460 OKL_API.set_message(
1461 p_app_name => G_APP_NAME,
1462 p_msg_name => 'OKL_AM_INV_VALIDATE_SUCC',
1463 p_token1 => 'AGREEMENT_NUMBER',
1464 p_token1_value => l_ia_rec.contract_number);
1465
1466 -- Set return values
1467 x_return_status := l_return_status;
1468 x_ialn_tbl := l_ialn_tbl;
1469 x_ia_rec := l_ia_rec;
1470
1471 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1472 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1473 'OKL_AM_TERMINATE_INV_AGMT_PVT.val_pop_investor_agreement.',
1474 'End(-)');
1475 END IF;
1476
1477 EXCEPTION
1478
1479 WHEN G_EXCEPTION_ERROR THEN
1480 ROLLBACK TO validate_ia_trx;
1481 x_return_status := G_RET_STS_ERROR;
1482
1483 x_ialn_tbl := l_ialn_tbl;
1484 x_ia_rec := l_ia_rec;
1485
1486 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1488 'OKL_AM_TERMINATE_INV_AGMT_PVT.val_pop_investor_agreement.',
1489 'EXP - G_EXCEPTION_ERROR');
1490 END IF;
1491
1492 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1493 ROLLBACK TO validate_ia_trx;
1494 x_return_status := G_RET_STS_UNEXP_ERROR;
1495
1496 x_ialn_tbl := l_ialn_tbl;
1497 x_ia_rec := l_ia_rec;
1498
1499 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1500 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1501 'OKL_AM_TERMINATE_INV_AGMT_PVT.val_pop_investor_agreement.',
1502 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1503 END IF;
1504
1505 WHEN OTHERS THEN
1506 ROLLBACK TO validate_ia_trx;
1507 -- Set the oracle error message
1508 OKL_API.set_message(
1509 p_app_name => G_APP_NAME_1,
1510 p_msg_name => G_UNEXPECTED_ERROR,
1511 p_token1 => G_SQLCODE_TOKEN,
1512 p_token1_value => SQLCODE,
1513 p_token2 => G_SQLERRM_TOKEN,
1514 p_token2_value => SQLERRM);
1515
1516 x_return_status := G_RET_STS_UNEXP_ERROR;
1517
1518 x_ialn_tbl := l_ialn_tbl;
1519 x_ia_rec := l_ia_rec;
1520
1521 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1523 'OKL_AM_TERMINATE_INV_AGMT_PVT.val_pop_investor_agreement.',
1524 'EXP - OTHERS');
1525 END IF;
1526
1527 END val_pop_investor_agreement;
1528
1529 -- Start of comments
1530 --
1531 -- Procedure Name : pop_or_insert_transaction
1532 -- Description : procedure to insert termination transaction for the investor
1533 -- agreement if does not exist or else if exists then populate
1534 -- Business Rules :
1535 -- Parameters :
1536 -- Version : 1.0
1537 -- History : RMUNJULU Created
1538 --
1539 -- End of comments
1540 PROCEDURE pop_or_insert_transaction(
1541 p_ia_rec IN ia_rec_type,
1542 p_sys_date IN DATE,
1543 x_trn_already_yn OUT NOCOPY VARCHAR2,
1544 px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
1545 p_validate_success IN VARCHAR2,
1546 x_return_status OUT NOCOPY VARCHAR2) IS
1547
1548 -- Get the trn if exists
1549 CURSOR get_trn_csr ( p_ia_id IN NUMBER ) IS
1550 SELECT TRN.id,
1551 TRN.trx_number,
1552 TRN.tsu_code,
1553 TRN.tcn_type,
1554 TRN.try_id,
1555 TRN.khr_id,
1556 TRN.tmt_validated_yn,
1557 TRN.tmt_accounting_entries_yn,
1558 TRN.tmt_contract_updated_yn,
1559 TRN.tmt_recycle_yn,
1560 TRN.tmt_generic_flag1_yn,
1561 TRN.tmt_generic_flag2_yn,
1562 TRN.tmt_generic_flag3_yn,
1563 TRN.legal_entity_id
1564 FROM OKL_TRX_CONTRACTS TRN
1565 WHERE TRN.khr_id = p_ia_id
1566 --rkuttiya added for 12.1.1 Multi GAAP
1567 AND TRN.representation_type = 'PRIMARY'
1568 --
1569 AND TRN.tcn_type = 'IAT';
1570
1571 -- get legal_entity_id
1572 CURSOR get_le_csr (p_ia_id IN NUMBER)is
1573 SELECT legal_entity_id
1574 FROM Okl_k_headers
1575 WHERE khr_id = p_ia_id;
1576
1577 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1578 lp_tcnv_rec tcnv_rec_type ;
1579 lx_tcnv_rec tcnv_rec_type ;
1580 l_try_id NUMBER;
1581 l_currency_code VARCHAR2(2000);
1582 l_trans_meaning VARCHAR2(2000);
1583 l_trn_already_yn VARCHAR2(1) := G_NO;
1584
1585 l_api_version CONSTANT NUMBER := G_API_VERSION;
1586 l_msg_count NUMBER := G_MISS_NUM;
1587 l_msg_data VARCHAR2(2000);
1588
1589 BEGIN
1590
1591 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1592 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1593 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
1594 'Begin(+)');
1595 END IF;
1596
1597 SAVEPOINT pop_insert_trn_trx;
1598
1599 -- *************
1600 -- Populate TRN if exists
1601 -- *************
1602
1603 FOR get_trn_rec IN get_trn_csr ( p_ia_rec.id ) LOOP
1604
1605 IF p_validate_success = G_RET_STS_SUCCESS THEN
1606 lx_tcnv_rec.tmt_validated_yn := G_YES;
1607 ELSE
1608 lx_tcnv_rec.tmt_validated_yn := G_NO;
1609 END IF;
1610
1611 lx_tcnv_rec.id := get_trn_rec.id;
1612 lx_tcnv_rec.tsu_code := get_trn_rec.tsu_code;
1613 lx_tcnv_rec.trx_number := get_trn_rec.trx_number;
1614 lx_tcnv_rec.tcn_type := get_trn_rec.tcn_type;
1615 lx_tcnv_rec.try_id := get_trn_rec.try_id;
1616 lx_tcnv_rec.khr_id := get_trn_rec.khr_id;
1617 lx_tcnv_rec.tmt_accounting_entries_yn := get_trn_rec.tmt_accounting_entries_yn;
1618 lx_tcnv_rec.tmt_contract_updated_yn := get_trn_rec.tmt_contract_updated_yn;
1619 lx_tcnv_rec.tmt_recycle_yn := get_trn_rec.tmt_recycle_yn;
1620 lx_tcnv_rec.tmt_generic_flag1_yn := get_trn_rec.tmt_generic_flag1_yn;
1621 lx_tcnv_rec.tmt_generic_flag2_yn := get_trn_rec.tmt_generic_flag2_yn;
1622 lx_tcnv_rec.tmt_generic_flag3_yn := get_trn_rec.tmt_generic_flag3_yn;
1623 lx_tcnv_rec.legal_entity_id := get_trn_rec.legal_entity_id;
1624 l_trn_already_yn := G_YES;
1625
1626 END LOOP;
1627
1628
1629 -- *************
1630 -- Insert TRN if not exists
1631 -- *************
1632
1633 IF lx_tcnv_rec.id IS NULL
1634 OR lx_tcnv_rec.id = G_MISS_NUM THEN
1635
1636 -- Get the Transaction Id
1637 OKL_AM_UTIL_PVT.get_transaction_id (
1638 p_try_name => 'Termination',
1639 x_return_status => l_return_status,
1640 x_try_id => l_try_id);
1641
1642 -- Get the meaning of lookup OKL_ACCOUNTING_EVENT_TYPE
1643 l_trans_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
1644 p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
1645 p_lookup_code => 'TERMINATION',
1646 p_validate_yn => G_YES);
1647
1648 IF l_return_status <> G_RET_STS_SUCCESS THEN
1649
1650 -- Message: Unable to find a transaction type for the transaction TRY_NAME -- Seeded
1651 OKL_API.set_message(
1652 p_app_name => G_APP_NAME,
1653 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
1654 p_token1 => 'TRY_NAME',
1655 p_token1_value => l_trans_meaning);
1656
1657 RAISE G_EXCEPTION_ERROR;
1658
1659 END IF;
1660
1661 -- Get the contract currency code -- *** will IA have currency code
1662 l_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(p_ia_rec.id);
1663
1664 -- Set the TRN rec
1665 lp_tcnv_rec.khr_id := p_ia_rec.id;
1666
1667 IF p_validate_success = G_RET_STS_SUCCESS THEN
1668
1669 lp_tcnv_rec.tmt_validated_yn := G_YES;
1670 lp_tcnv_rec.tsu_code := 'ENTERED';
1671
1672 ELSE
1673 lp_tcnv_rec.tmt_validated_yn := G_NO;
1674 lp_tcnv_rec.tsu_code := 'ERROR';
1675 END IF;
1676 FOR get_le_rec IN get_le_csr(p_ia_rec.id) LOOP
1677 lp_tcnv_rec.legal_entity_id := get_le_rec.legal_entity_id;
1678 END LOOP;
1679 lp_tcnv_rec.tcn_type := 'IAT';
1680 lp_tcnv_rec.try_id := l_try_id;
1681 lp_tcnv_rec.currency_code := l_currency_code;
1682 lp_tcnv_rec.date_transaction_occurred := p_sys_date;
1683
1684 -- Call create_trx_contracts to create transaction
1685 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(
1686 p_api_version => l_api_version,
1687 p_init_msg_list => G_FALSE,
1688 x_return_status => l_return_status,
1689 x_msg_count => l_msg_count,
1690 x_msg_data => l_msg_data,
1691 p_tcnv_rec => lp_tcnv_rec,
1692 x_tcnv_rec => lx_tcnv_rec);
1693
1694 -- Set msg if error
1695 IF l_return_status <> G_RET_STS_SUCCESS THEN
1696 -- Error occured while creating termination transaction for the
1697 -- Investor Agreement AGREEMENT_NUMBER..
1698 OKL_API.set_message(
1699 p_app_name => G_APP_NAME,
1700 p_msg_name => 'OKL_AM_INV_TRN_CREATE_ERR');
1701 END IF;
1702
1703 -- raise exception if create failed
1704 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1705 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1706 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1707 RAISE G_EXCEPTION_ERROR;
1708 END IF;
1709
1710 -- Set if TRN was already existing
1711 l_trn_already_yn := G_NO;
1712
1713 END IF;
1714
1715 -- Set return values
1716 x_return_status := l_return_status;
1717 px_tcnv_rec := lx_tcnv_rec;
1718 x_trn_already_yn := l_trn_already_yn;
1719
1720 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1721 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1722 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
1723 'End(-)');
1724 END IF;
1725
1726 EXCEPTION
1727
1728 WHEN G_EXCEPTION_ERROR THEN
1729 ROLLBACK TO pop_insert_trn_trx;
1730 x_return_status := G_RET_STS_ERROR;
1731
1732 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1733 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1734 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
1735 'EXP - G_EXCEPTION_ERROR');
1736 END IF;
1737
1738 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1739 ROLLBACK TO pop_insert_trn_trx;
1740 x_return_status := G_RET_STS_UNEXP_ERROR;
1741
1742 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1743 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1744 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
1745 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1746 END IF;
1747
1748 WHEN OTHERS THEN
1749 ROLLBACK TO pop_insert_trn_trx;
1750
1751 -- Set the oracle error message
1752 OKL_API.set_message(
1753 p_app_name => G_APP_NAME_1,
1754 p_msg_name => G_UNEXPECTED_ERROR,
1755 p_token1 => G_SQLCODE_TOKEN,
1756 p_token1_value => SQLCODE,
1757 p_token2 => G_SQLERRM_TOKEN,
1758 p_token2_value => SQLERRM);
1759
1760 x_return_status := G_RET_STS_UNEXP_ERROR;
1761
1762 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1763 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1764 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
1765 'EXP - OTHERS');
1766 END IF;
1767
1768 END pop_or_insert_transaction;
1769
1770 -- Start of comments
1771 --
1772 -- Procedure Name : reverse_loss_provisions
1773 -- Description : procedure to do reversal of loss provisions of investor agreement
1774 -- Business Rules :
1775 -- Parameters :
1776 -- Version : 1.0
1777 -- History : RMUNJULU Created
1778 --
1779 -- End of comments
1780 PROCEDURE reverse_loss_provisions(
1781 p_ia_rec IN ia_rec_type,
1782 p_termination_date IN DATE,
1783 p_gl_date IN DATE,
1784 x_return_status OUT NOCOPY VARCHAR2) IS
1785
1786
1787 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1788 l_lprv_rec OKL_REV_LOSS_PROV_PUB.lprv_rec_type;
1789 l_api_version CONSTANT NUMBER := G_API_VERSION;
1790 l_msg_count NUMBER := G_MISS_NUM;
1791 l_msg_data VARCHAR2(2000);
1792
1793 BEGIN
1794
1795 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1796 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1797 'OKL_AM_TERMINATE_INV_AGMT_PVT.reverse_loss_provisions.',
1798 'Begin(+)');
1799 END IF;
1800
1801 -- *********
1802 -- Reverse Loss Provisions
1803 -- *********
1804
1805 SAVEPOINT reverse_loss_trx;
1806
1807 l_lprv_rec.cntrct_num := p_ia_rec.contract_number; --Agreement Number
1808 l_lprv_rec.reversal_type := NULL; --PGL for reversal of General Loss and PSP for reversal of Specific Loss
1809 l_lprv_rec.reversal_date := p_gl_date; -- Loss Provision Reversal Date in a valid open period.
1810
1811 OKL_REV_LOSS_PROV_PUB.reverse_loss_provisions(
1812 p_api_version => l_api_version,
1813 p_init_msg_list => G_FALSE,
1814 x_return_status => l_return_status,
1815 x_msg_count => l_msg_count,
1816 x_msg_data => l_msg_data,
1817 p_lprv_rec => l_lprv_rec);
1818
1819 IF l_return_status <> G_RET_STS_SUCCESS THEN
1820 -- Error occured during reversal of loss provisions for Investor
1821 -- Agreement AGREEMENT_NUMBER.
1822 OKL_API.set_message(
1823 p_app_name => G_APP_NAME,
1824 p_msg_name => 'OKL_AM_INV_REVERSE_ERR',
1825 p_token1 => 'AGREEMENT_NUMBER',
1826 p_token1_value => p_ia_rec.contract_number);
1827 END IF;
1828
1829 -- Raise exception if error
1830 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1831 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1832 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1833 RAISE G_EXCEPTION_ERROR;
1834 END IF;
1835
1836 -- Set the success message
1837 -- Loss provisions have been reversed for Investor Agreement AGREEMENT_NUMBER.
1838 OKL_API.set_message(
1839 p_app_name => G_APP_NAME,
1840 p_msg_name => 'OKL_AM_INV_REVERSE_SUCC',
1841 p_token1 => 'AGREEMENT_NUMBER',
1842 p_token1_value => p_ia_rec.contract_number);
1843
1844 -- Set return status
1845 x_return_status := l_return_status;
1846
1847 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1848 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1849 'OKL_AM_TERMINATE_INV_AGMT_PVT.reverse_loss_provisions.',
1850 'End(-)');
1851 END IF;
1852
1853 EXCEPTION
1854
1855 WHEN G_EXCEPTION_ERROR THEN
1856 ROLLBACK TO reverse_loss_trx;
1857 x_return_status := G_RET_STS_ERROR;
1858
1859 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1860 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1861 'OKL_AM_TERMINATE_INV_AGMT_PVT.reverse_loss_provisions.',
1862 'EXP - G_EXCEPTION_ERROR');
1863 END IF;
1864
1865 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1866 ROLLBACK TO reverse_loss_trx;
1867 x_return_status := G_RET_STS_UNEXP_ERROR;
1868
1869 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1871 'OKL_AM_TERMINATE_INV_AGMT_PVT.reverse_loss_provisions.',
1872 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1873 END IF;
1874
1875 WHEN OTHERS THEN
1876 ROLLBACK TO reverse_loss_trx;
1877
1878 -- Set the oracle error message
1879 OKL_API.set_message(
1880 p_app_name => G_APP_NAME_1,
1881 p_msg_name => G_UNEXPECTED_ERROR,
1882 p_token1 => G_SQLCODE_TOKEN,
1883 p_token1_value => SQLCODE,
1884 p_token2 => G_SQLERRM_TOKEN,
1885 p_token2_value => SQLERRM);
1886
1887 x_return_status := G_RET_STS_UNEXP_ERROR;
1888
1889 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1890 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1891 'OKL_AM_TERMINATE_INV_AGMT_PVT.reverse_loss_provisions.',
1892 'EXP - OTHERS');
1893 END IF;
1894
1895 END reverse_loss_provisions;
1896
1897 -- Start of comments
1898 --
1899 -- Procedure Name : accounting_entries
1900 -- Description : procedure to do accounting
1901 -- Business Rules :
1902 -- Parameters :
1903 -- Version : 1.0
1904 -- History : RMUNJULU Created
1905 --
1906 -- End of comments
1907 PROCEDURE accounting_entries(
1908 p_ia_rec IN ia_rec_type,
1909 p_termination_date IN DATE,
1910 p_gl_date IN DATE,
1911 px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
1912 x_return_status OUT NOCOPY VARCHAR2) IS
1913
1914 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1915
1916 BEGIN
1917
1918 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1919 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1920 'OKL_AM_TERMINATE_INV_AGMT_PVT.accounting_entries.',
1921 'Begin(+)');
1922 END IF;
1923
1924 SAVEPOINT accounting_entries_trx;
1925
1926 IF NVL(px_tcnv_rec.tmt_accounting_entries_yn, G_NO) = G_NO THEN
1927
1928 -- *************
1929 -- Reversal of Loss Provisions
1930 -- *************
1931
1932 reverse_loss_provisions(
1933 p_ia_rec => p_ia_rec,
1934 p_termination_date => p_termination_date,
1935 p_gl_date => p_gl_date,
1936 x_return_status => l_return_status);
1937
1938 -- raise exception if api failed
1939 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1940 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1941 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1942 RAISE G_EXCEPTION_ERROR;
1943 END IF;
1944
1945 -- Set return status
1946 x_return_status := l_return_status;
1947
1948 px_tcnv_rec.tmt_accounting_entries_yn := G_YES;
1949
1950 END IF;
1951
1952 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1953 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1954 'OKL_AM_TERMINATE_INV_AGMT_PVT.accounting_entries.',
1955 'End(-)');
1956 END IF;
1957
1958 EXCEPTION
1959
1960 WHEN G_EXCEPTION_ERROR THEN
1961 ROLLBACK TO accounting_entries_trx;
1962 x_return_status := G_RET_STS_ERROR;
1963 px_tcnv_rec.tmt_accounting_entries_yn := G_NO;
1964 px_tcnv_rec.tsu_code := 'ERROR';
1965
1966 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1967 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1968 'OKL_AM_TERMINATE_INV_AGMT_PVT.accounting_entries.',
1969 'EXP - G_EXCEPTION_ERROR');
1970 END IF;
1971
1972 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1973 ROLLBACK TO accounting_entries_trx;
1974 x_return_status := G_RET_STS_UNEXP_ERROR;
1975 px_tcnv_rec.tmt_accounting_entries_yn := G_NO;
1976 px_tcnv_rec.tsu_code := 'ERROR';
1977
1978 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1979 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1980 'OKL_AM_TERMINATE_INV_AGMT_PVT.accounting_entries.',
1981 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1982 END IF;
1983
1984 WHEN OTHERS THEN
1985 ROLLBACK TO accounting_entries_trx;
1986
1987 -- Set the oracle error message
1988 OKL_API.set_message(
1989 p_app_name => G_APP_NAME_1,
1990 p_msg_name => G_UNEXPECTED_ERROR,
1991 p_token1 => G_SQLCODE_TOKEN,
1992 p_token1_value => SQLCODE,
1993 p_token2 => G_SQLERRM_TOKEN,
1994 p_token2_value => SQLERRM);
1995
1996 x_return_status := G_RET_STS_UNEXP_ERROR;
1997 px_tcnv_rec.tmt_accounting_entries_yn := G_NO;
1998 px_tcnv_rec.tsu_code := 'ERROR';
1999
2000 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2001 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2002 'OKL_AM_TERMINATE_INV_AGMT_PVT.accounting_entries.',
2003 'EXP - OTHERS');
2004 END IF;
2005
2006 END accounting_entries;
2007
2008 -- Start of comments
2009 --
2010 -- Procedure Name : update_ia_and_lines
2011 -- Description : procedure to update investor agreement and lines
2012 -- Business Rules :
2013 -- Parameters :
2014 -- Version : 1.0
2015 -- History : RMUNJULU Created
2016 --
2017 -- End of comments
2018 PROCEDURE update_ia_and_lines(
2019 p_ia_rec IN ia_rec_type,
2020 p_termination_date IN DATE,
2021 p_ialn_tbl IN ialn_tbl_type,
2022 x_return_status OUT NOCOPY VARCHAR2) IS
2023
2024 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2025 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2026 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2027 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2028
2029 lp_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
2030 lp_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
2031 lx_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
2032 lx_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
2033
2034 l_trn_reason_code VARCHAR2(30) := 'EXP';
2035 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2036
2037 l_api_version CONSTANT NUMBER := G_API_VERSION;
2038 l_msg_count NUMBER := G_MISS_NUM;
2039 l_msg_data VARCHAR2(2000);
2040 i NUMBER;
2041
2042 BEGIN
2043
2044 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2045 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2046 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
2047 'Begin(+)');
2048 END IF;
2049
2050 -- *********
2051 -- Expire IA lines
2052 -- *********
2053
2054 SAVEPOINT update_ia_and_lines_trx;
2055
2056 -- If lines exists then update
2057 IF p_ialn_tbl.COUNT > 0 THEN
2058
2059 -- Loop thru the IA lines and expire them
2060 FOR i IN p_ialn_tbl.FIRST..p_ialn_tbl.LAST LOOP
2061
2062 -- Set the rec types
2063 lp_clev_rec.id := p_ialn_tbl(i).id;
2064 lp_klev_rec.id := p_ialn_tbl(i).id;
2065 lp_clev_rec.date_terminated := p_termination_date;
2066 lp_clev_rec.sts_code := 'EXPIRED';
2067 lp_clev_rec.trn_code := l_trn_reason_code;
2068
2069 -- Call update lines to expire lines
2070 OKL_CONTRACT_PUB.update_contract_line(
2071 p_api_version => l_api_version,
2072 p_init_msg_list => G_FALSE,
2073 x_return_status => l_return_status,
2074 x_msg_count => l_msg_count,
2075 x_msg_data => l_msg_data,
2076 p_clev_rec => lp_clev_rec,
2077 p_klev_rec => lp_klev_rec,
2078 x_clev_rec => lx_clev_rec,
2079 x_klev_rec => lx_klev_rec);
2080
2081 IF l_return_status <> G_RET_STS_SUCCESS THEN
2082 -- Error occured during update of Investor Agreement AGREEMENT_NUMBER lines.
2083 OKL_API.set_message(
2084 p_app_name => G_APP_NAME,
2085 p_msg_name => 'OKL_AM_INV_TRMT_LINE_ERR',
2086 p_token1 => 'AGREEMENT_NUMBER',
2087 p_token1_value => p_ia_rec.contract_number);
2088 END IF;
2089
2090 -- raise exception if update failed
2091 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2092 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2093 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2094 RAISE G_EXCEPTION_ERROR;
2095 END IF;
2096
2097
2098 END LOOP;
2099
2100 END IF;
2101
2102 -- *********
2103 -- Expire IA hdr
2104 -- *********
2105
2106 -- Set the rec types
2107 lp_chrv_rec.id := p_ia_rec.id;
2108 lp_khrv_rec.id := p_ia_rec.id;
2109 lp_chrv_rec.date_terminated := p_termination_date;
2110 lp_chrv_rec.sts_code := 'EXPIRED';
2111 lp_chrv_rec.trn_code := l_trn_reason_code;
2112
2113 -- Call update hdr to expire hdr
2114 OKL_CONTRACT_PUB.update_contract_header(
2115 p_api_version => l_api_version,
2116 p_init_msg_list => G_FALSE,
2117 x_return_status => l_return_status,
2118 x_msg_count => l_msg_count,
2119 x_msg_data => l_msg_data,
2120 p_chrv_rec => lp_chrv_rec,
2121 p_khrv_rec => lp_khrv_rec,
2122 x_chrv_rec => lx_chrv_rec,
2123 x_khrv_rec => lx_khrv_rec);
2124
2125 IF l_return_status <> G_RET_STS_SUCCESS THEN
2126 -- Error occured during update of investor agreement AGREEMENT_NUMBER.
2127 OKL_API.set_message(
2128 p_app_name => G_APP_NAME,
2129 p_msg_name => 'OKL_AM_INV_TRMT_ERR',
2130 p_token1 => 'AGREEMENT_NUMBER',
2131 p_token1_value => p_ia_rec.contract_number);
2132 END IF;
2133
2134 -- raise exception if update failed
2135 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2136 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2137 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2138 RAISE G_EXCEPTION_ERROR;
2139 END IF;
2140
2141 -- Set return status
2142 x_return_status := l_return_status;
2143
2144 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2145 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2146 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
2147 'End(-)');
2148 END IF;
2149
2150 EXCEPTION
2151
2152 WHEN G_EXCEPTION_ERROR THEN
2153 ROLLBACK TO update_ia_and_lines_trx;
2154 x_return_status := G_RET_STS_ERROR;
2155
2156 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2158 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
2159 'EXP - G_EXCEPTION_ERROR');
2160 END IF;
2161
2162 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2163 ROLLBACK TO update_ia_and_lines_trx;
2164 x_return_status := G_RET_STS_UNEXP_ERROR;
2165
2166 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2168 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
2169 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
2170 END IF;
2171
2172 WHEN OTHERS THEN
2173 ROLLBACK TO update_ia_and_lines_trx;
2174
2175 -- Set the oracle error message
2176 OKL_API.set_message(
2177 p_app_name => G_APP_NAME_1,
2178 p_msg_name => G_UNEXPECTED_ERROR,
2179 p_token1 => G_SQLCODE_TOKEN,
2180 p_token1_value => SQLCODE,
2181 p_token2 => G_SQLERRM_TOKEN,
2182 p_token2_value => SQLERRM);
2183
2184 x_return_status := G_RET_STS_UNEXP_ERROR;
2185
2186 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2187 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2188 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
2189 'EXP - OTHERS');
2190 END IF;
2191
2192 END update_ia_and_lines;
2193
2194 -- Start of comments
2195 --
2196 -- Procedure Name : update_pools
2197 -- Description : procedure to update investor agreement pools
2198 -- Business Rules :
2199 -- Parameters :
2200 -- Version : 1.0
2201 -- History : RMUNJULU Created
2202 -- RMUNJULU Bug 3455354 Added code to set the lease contract
2203 -- to NOT SECURITIZED if no other pool has it
2204 --
2205 -- End of comments
2206 PROCEDURE update_pools(
2207 p_ia_rec IN ia_rec_type,
2208 p_termination_date IN DATE,
2209 x_return_status OUT NOCOPY VARCHAR2) IS
2210
2211 -- get the active pools for the IA
2212 CURSOR get_pools_csr(p_ia_id IN NUMBER) IS
2213 SELECT POL.id
2214 FROM OKL_POOLS POL,
2215 OKC_K_HEADERS_B KHR
2216 WHERE KHR.id = p_ia_id
2217 AND KHR.id = POL.khr_id
2218 AND POL.status_code = 'ACTIVE'; -- ACTIVE
2219
2220 -- get the pool contents for the pool ( Cannot use POC status as it is
2221 -- already set to EXPIRED when control comes here )
2222 CURSOR get_pool_contents_csr(p_pol_id IN NUMBER) IS
2223 SELECT POC.id,
2224 POC.transaction_number_in
2225 FROM OKL_POOLS POL,
2226 OKL_POOL_CONTENTS POC
2227 WHERE POL.id = p_pol_id
2228 AND POC.pol_id = POL.id
2229 AND POC.status_code = 'ACTIVE';
2230
2231 -- RMUNJULU Bug 3455354
2232 -- get lease contract which is associated to this pool but not to any other ACTIVE ones
2233 CURSOR get_k_update_csr(p_pol_id IN NUMBER) IS
2234 SELECT DISTINCT POCA.khr_id
2235 FROM OKL_POOL_CONTENTS POCA
2236 WHERE POCA.pol_id = p_pol_id
2237 AND NOT EXISTS (
2238 SELECT POCB.khr_id
2239 FROM OKL_POOL_CONTENTS POCB
2240 WHERE POCB.pol_id <> POCA.pol_id
2241 AND POCB.khr_id = POCA.khr_id
2242 AND POCB.status_code = 'ACTIVE'
2243 );
2244
2245 --sosharma added cursor to fetch legal entity bug 6791390
2246 CURSOR c_pool(p_pool_id IN NUMBER) IS
2247 SELECT pol.legal_entity_id
2248 FROM okl_pools pol
2249 WHERE pol.id = p_pool_id;
2250
2251
2252
2253 lp_polv_rec OKL_POL_PVT.polv_rec_type;
2254 lx_polv_rec OKL_POL_PVT.polv_rec_type;
2255 lp_pocv_rec OKL_POC_PVT.pocv_rec_type;
2256 lx_pocv_rec OKL_POC_PVT.pocv_rec_type;
2257 lp_poxv_rec OKL_POX_PVT.poxv_rec_type;
2258 lx_poxv_rec OKL_POX_PVT.poxv_rec_type;
2259
2260 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2261
2262 l_api_version CONSTANT NUMBER := G_API_VERSION;
2263 l_msg_count NUMBER := G_MISS_NUM;
2264 l_msg_data VARCHAR2(2000);
2265
2266 -- sosharma added gor bug 6791390
2267 l_row_found BOOLEAN := FALSE;
2268 l_legal_entity_id NUMBER;
2269
2270 -- RMUNJULU Bug 3455354
2271 l_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2272 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2273
2274 l_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2275 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2276
2277
2278 BEGIN
2279
2280 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2281 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2282 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
2283 'Begin(+)');
2284 END IF;
2285
2286 SAVEPOINT update_pools_trx;
2287
2288 -- Loop thru the ACTIVE pools for the IA
2289 FOR get_pools_rec IN get_pools_csr(p_ia_rec.id) LOOP
2290
2291 -- ********
2292 -- Update Pools
2293 -- ********
2294
2295 lp_polv_rec.id := get_pools_rec.id;
2296 lp_polv_rec.status_code := 'EXPIRED';
2297
2298 -- Call update_row to expire pools
2299 OKL_POL_PVT.update_row(
2300 p_api_version => l_api_version,
2301 p_init_msg_list => G_FALSE,
2302 x_return_status => l_return_status,
2303 x_msg_count => l_msg_count,
2304 x_msg_data => l_msg_data,
2305 p_polv_rec => lp_polv_rec,
2306 x_polv_rec => lx_polv_rec);
2307
2308 IF l_return_status <> G_RET_STS_SUCCESS THEN
2309 -- Error occurred during update of pool for the
2310 -- Investor Agreement AGREEMENT_NUMBER.
2311 OKL_API.set_message(
2312 p_app_name => G_APP_NAME,
2313 p_msg_name => 'OKL_AM_INV_UPD_POOL_ERR',
2314 p_token1 => 'AGREEMENT_NUMBER',
2315 p_token1_value => p_ia_rec.contract_number);
2316 END IF;
2317
2318 -- raise exception if update failed
2319 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2320 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2321 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2322 RAISE G_EXCEPTION_ERROR;
2323 END IF;
2324
2325 -- *********
2326 -- create pool transaction
2327 -- *********
2328
2329 lp_poxv_rec.pol_id := get_pools_rec.id;
2330 lp_poxv_rec.transaction_date := p_termination_date;
2331 lp_poxv_rec.transaction_type := 'REMOVE';
2332 lp_poxv_rec.transaction_reason := 'AGREEMENT_TERMINATION';
2333 --sosharma 04/12/2007 added to enable status on pool transaction
2334 lp_poxv_rec.transaction_status := G_POOL_TRX_STATUS_COMPLETE;
2335 -- Fixed bug 6791390 Legal entity got getting passed
2336 OPEN c_pool(get_pools_rec.id);
2337 FETCH c_pool INTO l_legal_entity_id;
2338 l_row_found := c_pool%FOUND;
2339 CLOSE c_pool;
2340 IF l_row_found THEN
2341 lp_poxv_rec.legal_entity_id := l_legal_entity_id;
2342 END IF;
2343 -- Call insert_row to create pool transaction
2344 OKL_POX_PVT.insert_row(
2345 p_api_version => l_api_version,
2346 p_init_msg_list => G_FALSE,
2347 x_return_status => l_return_status,
2348 x_msg_count => l_msg_count,
2349 x_msg_data => l_msg_data,
2350 p_poxv_rec => lp_poxv_rec,
2351 x_poxv_rec => lx_poxv_rec);
2352
2353 IF l_return_status <> G_RET_STS_SUCCESS THEN
2354 -- Error occurred during creation of pool transaction for
2355 -- the Investor Agreement AGREEMENT_NUMBER.
2356 OKL_API.set_message(
2357 p_app_name => G_APP_NAME,
2358 p_msg_name => 'OKL_AM_INV_POOL_TRN_CRT_ERR',
2359 p_token1 => 'AGREEMENT_NUMBER',
2360 p_token1_value => p_ia_rec.contract_number);
2361 END IF;
2362
2363 -- raise exception if update failed
2364 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2365 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2366 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2367 RAISE G_EXCEPTION_ERROR;
2368 END IF;
2369
2370 -- ********
2371 -- Update Pool Contents
2372 -- ********
2373
2374 -- Loop thru the ACTIVE pool contents for the pool
2375 FOR get_pool_contents_rec IN get_pool_contents_csr(get_pools_rec.id) LOOP
2376
2377
2378 lp_pocv_rec.id := get_pool_contents_rec.id;
2379 lp_pocv_rec.status_code := 'EXPIRED';
2380 lp_pocv_rec.transaction_number_out := lx_poxv_rec.transaction_number;
2381
2382 -- Call update_row to expire pool contents
2383 OKL_POC_PVT.update_row(
2384 p_api_version => l_api_version,
2385 p_init_msg_list => G_FALSE,
2386 x_return_status => l_return_status,
2387 x_msg_count => l_msg_count,
2388 x_msg_data => l_msg_data,
2389 p_pocv_rec => lp_pocv_rec,
2390 x_pocv_rec => lx_pocv_rec);
2391
2392 IF l_return_status <> G_RET_STS_SUCCESS THEN
2393 -- Error occurred during update of pool contents for
2394 -- the Investor Agreement AGREEMENT_NUMBER.
2395 OKL_API.set_message(
2396 p_app_name => G_APP_NAME,
2397 p_msg_name => 'OKL_AM_INV_UPD_POC_IA_ERR',
2398 p_token1 => 'AGREEMENT_NUMBER',
2399 p_token1_value => p_ia_rec.contract_number);
2400 END IF;
2401
2402 -- raise exception if update failed
2403 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2404 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2405 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2406 RAISE G_EXCEPTION_ERROR;
2407 END IF;
2408 END LOOP;
2409
2410 -- RMUNJULU Bug 3455354
2411 -- ****
2412 -- For the pool get the contract and update Contract SECURITIZED_CODE
2413 -- to 'N' if the contract is not attached to any other active pool
2414 -- ****
2415
2416 FOR get_k_update_rec IN get_k_update_csr (get_pools_rec.id)LOOP
2417
2418 l_chrv_rec.id := get_k_update_rec.khr_id;
2419
2420 l_khrv_rec.id := get_k_update_rec.khr_id;
2421 l_khrv_rec.securitized_code := 'N';
2422
2423 OKL_CONTRACT_PUB.update_contract_header(
2424 p_api_version => l_api_version,
2425 p_init_msg_list => OKL_API.G_FALSE,
2426 x_return_status => l_return_status,
2427 x_msg_count => l_msg_count,
2428 x_msg_data => l_msg_data,
2429 p_chrv_rec => l_chrv_rec,
2430 p_khrv_rec => l_khrv_rec,
2431 x_chrv_rec => lx_chrv_rec,
2432 x_khrv_rec => lx_khrv_rec);
2433
2434 IF l_return_status <> G_RET_STS_SUCCESS THEN
2435 -- Error occurred during update of pool for the
2436 -- Investor Agreement AGREEMENT_NUMBER.
2437 OKL_API.set_message(
2438 p_app_name => G_APP_NAME,
2439 p_msg_name => 'OKL_AM_INV_UPD_POOL_ERR',
2440 p_token1 => 'AGREEMENT_NUMBER',
2441 p_token1_value => p_ia_rec.contract_number);
2442 END IF;
2443
2444 -- raise exception if update failed
2445 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2446 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2447 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2448 RAISE G_EXCEPTION_ERROR;
2449 END IF;
2450
2451 END LOOP;
2452
2453 END LOOP;
2454
2455 -- Set return status
2456 x_return_status := l_return_status;
2457
2458 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2459 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2460 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
2461 'End(-)');
2462 END IF;
2463
2464 EXCEPTION
2465
2466 WHEN G_EXCEPTION_ERROR THEN
2467 ROLLBACK TO update_pools_trx;
2468 x_return_status := G_RET_STS_ERROR;
2469
2470 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2471 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2472 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
2473 'EXP - G_EXCEPTION_ERROR');
2474 END IF;
2475
2476 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2477 ROLLBACK TO update_pools_trx;
2478 x_return_status := G_RET_STS_UNEXP_ERROR;
2479
2480 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2481 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2482 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
2483 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
2484 END IF;
2485
2486 WHEN OTHERS THEN
2487 ROLLBACK TO update_pools_trx;
2488
2489 -- Set the oracle error message
2490 OKL_API.set_message(
2491 p_app_name => G_APP_NAME_1,
2492 p_msg_name => G_UNEXPECTED_ERROR,
2493 p_token1 => G_SQLCODE_TOKEN,
2494 p_token1_value => SQLCODE,
2495 p_token2 => G_SQLERRM_TOKEN,
2496 p_token2_value => SQLERRM);
2497
2498 x_return_status := G_RET_STS_UNEXP_ERROR;
2499
2500 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2501 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2502 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
2503 'EXP - OTHERS');
2504 END IF;
2505
2506 END update_pools;
2507
2508 -- Start of comments
2509 --
2510 -- Procedure Name : update_investor_agreement
2511 -- Description : procedure to update investor agreement and lines and pools
2512 -- Business Rules :
2513 -- Parameters :
2514 -- Version : 1.0
2515 -- History : RMUNJULU Created
2516 --
2517 -- End of comments
2518 PROCEDURE update_investor_agreement(
2519 p_ia_rec IN ia_rec_type,
2520 p_termination_date IN DATE,
2521 p_ialn_tbl IN ialn_tbl_type,
2522 px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
2523 p_overall_status IN VARCHAR2,
2524 x_return_status OUT NOCOPY VARCHAR2) IS
2525
2526 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2527
2528 BEGIN
2529
2530 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2531 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2532 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
2533 'Begin(+)');
2534 END IF;
2535
2536 SAVEPOINT update_ia_trx;
2537
2538 -- If all steps successful then do update_ia
2539 IF p_overall_status = G_RET_STS_SUCCESS THEN
2540
2541 -- *************
2542 -- Update Pool, Create Pool Trn and Update Pool contents
2543 -- *************
2544
2545 update_pools(
2546 p_ia_rec => p_ia_rec,
2547 p_termination_date => p_termination_date,
2548 x_return_status => l_return_status);
2549
2550 -- raise exception if api failed
2551 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2552 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2553 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2554 RAISE G_EXCEPTION_ERROR;
2555 END IF;
2556
2557 -- *************
2558 -- Update IA and Lines to Expired
2559 -- *************
2560
2561 update_ia_and_lines(
2562 p_ia_rec => p_ia_rec,
2563 p_termination_date => p_termination_date,
2564 p_ialn_tbl => p_ialn_tbl,
2565 x_return_status => l_return_status);
2566
2567 -- raise exception if api failed
2568 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2569 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2570 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2571 RAISE G_EXCEPTION_ERROR;
2572 END IF;
2573
2574 -- Set the success message
2575 -- Investor agreement AGREEMENT_NUMBER expired successfully.
2576 OKL_API.set_message(
2577 p_app_name => G_APP_NAME,
2578 p_msg_name => 'OKL_AM_INV_TRMNT_SUCC',
2579 p_token1 => 'AGREEMENT_NUMBER',
2580 p_token1_value => p_ia_rec.contract_number);
2581
2582 -- Set return status
2583 x_return_status := l_return_status;
2584
2585 px_tcnv_rec.tmt_contract_updated_yn := G_YES;
2586 px_tcnv_rec.tsu_code := 'PROCESSED';
2587
2588 END IF;
2589
2590 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2591 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2592 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
2593 'End(-)');
2594 END IF;
2595
2596 EXCEPTION
2597
2598 WHEN G_EXCEPTION_ERROR THEN
2599 ROLLBACK TO update_ia_trx;
2600 x_return_status := G_RET_STS_ERROR;
2601 px_tcnv_rec.tmt_contract_updated_yn := G_NO;
2602 px_tcnv_rec.tsu_code := 'ERROR';
2603
2604 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2606 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
2607 'EXP - G_EXCEPTION_ERROR');
2608 END IF;
2609
2610 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2611 ROLLBACK TO update_ia_trx;
2612 x_return_status := G_RET_STS_UNEXP_ERROR;
2613 px_tcnv_rec.tmt_contract_updated_yn := G_NO;
2614 px_tcnv_rec.tsu_code := 'ERROR';
2615
2616 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2618 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
2619 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
2620 END IF;
2621
2622 WHEN OTHERS THEN
2623 ROLLBACK TO update_ia_trx;
2624
2625 -- Set the oracle error message
2626 OKL_API.set_message(
2627 p_app_name => G_APP_NAME_1,
2628 p_msg_name => G_UNEXPECTED_ERROR,
2629 p_token1 => G_SQLCODE_TOKEN,
2630 p_token1_value => SQLCODE,
2631 p_token2 => G_SQLERRM_TOKEN,
2632 p_token2_value => SQLERRM);
2633
2634 x_return_status := G_RET_STS_UNEXP_ERROR;
2635 px_tcnv_rec.tmt_contract_updated_yn := G_NO;
2636 px_tcnv_rec.tsu_code := 'ERROR';
2637
2638 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2639 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2640 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
2641 'EXP - OTHERS');
2642 END IF;
2643
2644 END update_investor_agreement;
2645
2646 -- Start of comments
2647 --
2648 -- Procedure Name : update_transaction
2649 -- Description : procedure to update termination transaction for the investor agreement
2650 -- Business Rules :
2651 -- Parameters :
2652 -- Version : 1.0
2653 -- History : RMUNJULU Created
2654 --
2655 -- End of comments
2656 PROCEDURE update_transaction(
2657 p_ia_rec IN ia_rec_type,
2658 p_termination_date IN DATE,
2659 p_tcnv_rec IN tcnv_rec_type,
2660 x_return_status OUT NOCOPY VARCHAR2) IS
2661
2662 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2663 lp_tcnv_rec tcnv_rec_type := p_tcnv_rec;
2664 lx_tcnv_rec tcnv_rec_type ;
2665
2666 l_api_version CONSTANT NUMBER := G_API_VERSION;
2667 l_msg_count NUMBER := G_MISS_NUM;
2668 l_msg_data VARCHAR2(2000);
2669
2670 BEGIN
2671
2672 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2673 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2674 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
2675 'Begin(+)');
2676 END IF;
2677
2678 SAVEPOINT update_transaction_trx;
2679
2680 -- Call update_trx_contracts to update transaction
2681 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
2682 p_api_version => l_api_version,
2683 p_init_msg_list => G_FALSE,
2684 x_return_status => l_return_status,
2685 x_msg_count => l_msg_count,
2686 x_msg_data => l_msg_data,
2687 p_tcnv_rec => lp_tcnv_rec,
2688 x_tcnv_rec => lx_tcnv_rec);
2689
2690 -- Set msg if error
2691 IF l_return_status <> G_RET_STS_SUCCESS THEN
2692 -- Error occured during update of termination transaction
2693 -- for Investor Agreement AGREEMENT_NUMBER.
2694 OKL_API.set_message(
2695 p_app_name => G_APP_NAME,
2696 p_msg_name => 'OKL_AM_INV_TRN_UPD_IA_ERR',
2697 p_token1 => 'AGREEMENT_NUMBER',
2698 p_token1_value => p_ia_rec.contract_number);
2699 END IF;
2700
2701 -- raise exception if update failed
2702 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2703 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2704 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2705 RAISE G_EXCEPTION_ERROR;
2706 END IF;
2707
2708 -- Set return status
2709 x_return_status := l_return_status;
2710
2711 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2712 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2713 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
2714 'End(-)');
2715 END IF;
2716
2717 EXCEPTION
2718
2719 WHEN G_EXCEPTION_ERROR THEN
2720 ROLLBACK TO update_transaction_trx;
2721 x_return_status := G_RET_STS_ERROR;
2722
2723 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2724 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2725 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
2726 'EXP - G_EXCEPTION_ERROR');
2727 END IF;
2728
2729 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2730 ROLLBACK TO update_transaction_trx;
2731 x_return_status := G_RET_STS_UNEXP_ERROR;
2732
2733 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2735 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
2736 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
2737 END IF;
2738
2739 WHEN OTHERS THEN
2740 ROLLBACK TO update_transaction_trx;
2741
2742 -- Set the oracle error message
2743 OKL_API.set_message(
2744 p_app_name => G_APP_NAME_1,
2745 p_msg_name => G_UNEXPECTED_ERROR,
2746 p_token1 => G_SQLCODE_TOKEN,
2747 p_token1_value => SQLCODE,
2748 p_token2 => G_SQLERRM_TOKEN,
2749 p_token2_value => SQLERRM);
2750
2751 x_return_status := G_RET_STS_UNEXP_ERROR;
2752
2753 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2754 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2755 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
2756 'EXP - OTHERS');
2757 END IF;
2758
2759 END update_transaction;
2760
2761 -- Start of comments
2762 --
2763 -- Procedure Name : terminate_investor_agreement
2764 -- Description : procedure to terminate investor agreement
2765 -- Business Rules :
2766 -- Parameters :
2767 -- Version : 1.0
2768 -- History : RMUNJULU Created
2769 -- : RMUNJULU 22-OCT-03 Changed code to do proper processing
2770 --
2771 -- End of comments
2772 PROCEDURE terminate_investor_agreement(
2773 p_api_version IN NUMBER,
2774 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
2775 x_return_status OUT NOCOPY VARCHAR2,
2776 x_msg_count OUT NOCOPY NUMBER,
2777 x_msg_data OUT NOCOPY VARCHAR2,
2778 p_ia_rec IN ia_rec_type,
2779 p_control_flag IN VARCHAR2 DEFAULT NULL) IS
2780
2781 -- Get the latest IA Status
2782 CURSOR ia_status_csr (p_ia_id IN NUMBER) IS
2783 SELECT CHR.sts_code status
2784 FROM OKC_K_HEADERS_B CHR
2785 WHERE CHR.id = p_ia_id;
2786
2787 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2788 l_overall_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2789 l_trx_id NUMBER;
2790 l_pdt_id NUMBER;
2791 l_ia_rec ia_rec_type := p_ia_rec;
2792 l_ialn_tbl ialn_tbl_type;
2793 l_sys_date DATE;
2794 l_tcnv_rec tcnv_rec_type;
2795 l_trn_already_yn VARCHAR2(1);
2796 l_end_date DATE;
2797 l_start_date DATE;
2798 l_type VARCHAR2(300);
2799 l_status VARCHAR2(300);
2800 l_control_flag VARCHAR2(300);
2801 l_valid_gl_date DATE;
2802
2803 lx_error_rec OKL_API.error_rec_type;
2804 l_msg_idx INTEGER := G_FIRST;
2805 l_msg_tbl msg_tbl_type;
2806 l_api_name VARCHAR2(30) := 'terminate_investor_ag';
2807 l_api_version CONSTANT NUMBER := G_API_VERSION;
2808
2809 G_EXCEPTION EXCEPTION;
2810
2811 BEGIN
2812
2813 -- Create a Termination Transaction -- tcn_type = 'IAT' first time --
2814 -- Uses the created termination transaction later on, but does validate every time
2815 -- will not do Accounting Entries if already done
2816
2817 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2818 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2819 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
2820 'Begin(+)');
2821 END IF;
2822
2823 -- *************
2824 -- Check API version, initialize message list and create savepoint
2825 -- *************
2826
2827 l_return_status := OKL_API.start_activity(
2828 p_api_name => l_api_name,
2829 p_pkg_name => G_PKG_NAME,
2830 p_init_msg_list => p_init_msg_list,
2831 l_api_version => l_api_version,
2832 p_api_version => p_api_version,
2833 p_api_type => '_PVT',
2834 x_return_status => x_return_status);
2835
2836 -- Rollback if error setting activity for api
2837 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2838 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2839 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2840 RAISE G_EXCEPTION_ERROR;
2841 END IF;
2842
2843 SELECT sysdate INTO l_sys_date FROM DUAL;
2844
2845 -- Get valid GL Date for Termination_Date
2846 l_valid_gl_date := OKL_ACCOUNTING_UTIL.get_valid_gl_date(
2847 p_gl_date => l_sys_date);
2848
2849 -- *************
2850 -- Validate IA
2851 -- *************
2852
2853 val_pop_investor_agreement(
2854 p_ia_rec => p_ia_rec,
2855 x_ia_rec => l_ia_rec,
2856 x_ialn_tbl => l_ialn_tbl,
2857 x_return_status => l_return_status);
2858
2859 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2860 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2861 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
2862 'val_pop_investor_agreement = '||l_return_status );
2863 END IF;
2864
2865 -- Set Overall Status
2866 IF l_overall_status = G_RET_STS_SUCCESS
2867 AND l_overall_status <> G_RET_STS_UNEXP_ERROR THEN
2868 l_overall_status := l_return_status;
2869 END IF;
2870
2871 -- *************
2872 -- Populate or Insert IA Transaction based on need
2873 -- *************
2874 pop_or_insert_transaction(
2875 p_ia_rec => l_ia_rec,
2876 p_sys_date => l_sys_date,
2877 x_trn_already_yn => l_trn_already_yn,
2878 px_tcnv_rec => l_tcnv_rec,
2879 p_validate_success => l_overall_status,
2880 x_return_status => l_return_status);
2881
2882 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2883 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2884 'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
2885 'pop_or_insert_transaction = '||l_return_status );
2886 END IF;
2887
2888 -- raise exception if api failed
2889 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2890 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2891 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2892 RAISE G_EXCEPTION_ERROR;
2893 END IF;
2894
2895 -- If validation was not successful
2896 IF l_overall_status <> G_RET_STS_SUCCESS THEN
2897
2898 -- *************
2899 -- Update IA Transaction
2900 -- *************
2901
2902 update_transaction(
2903 p_ia_rec => l_ia_rec,
2904 p_termination_date => l_sys_date,
2905 p_tcnv_rec => l_tcnv_rec,
2906 x_return_status => l_return_status);
2907
2908 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2909 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2910 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
2911 'update_transaction = '||l_return_status );
2912 END IF;
2913
2914 -- raise exception if api failed
2915 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2916 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2917 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2918 RAISE G_EXCEPTION_ERROR;
2919 END IF;
2920
2921 -- Raise exception to come out since validation failed
2922 RAISE G_EXCEPTION;
2923 END IF;
2924
2925 -- *************
2926 -- Do Accounting Entries
2927 -- *************
2928
2929 accounting_entries(
2930 p_ia_rec => l_ia_rec,
2931 p_termination_date => l_sys_date,
2932 p_gl_date => l_valid_gl_date,
2933 px_tcnv_rec => l_tcnv_rec,
2934 x_return_status => l_return_status);
2935
2936 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2937 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2938 'OKL_AM_TERMINATE_INV_AGMT_PVT.accounting_entries.',
2939 'accounting_entries = '||l_return_status );
2940 END IF;
2941
2942 -- Set Overall Status
2943 IF l_overall_status = G_RET_STS_SUCCESS
2944 AND l_overall_status <> G_RET_STS_UNEXP_ERROR THEN
2945 l_overall_status := l_return_status;
2946 END IF;
2947
2948 -- *************
2949 -- Update IA, Lines, Pools, Pool Contents
2950 -- *************
2951
2952 update_investor_agreement(
2953 p_ia_rec => l_ia_rec,
2954 p_termination_date => l_sys_date,
2955 p_ialn_tbl => l_ialn_tbl,
2956 px_tcnv_rec => l_tcnv_rec,
2957 p_overall_status => l_overall_status,
2958 x_return_status => l_return_status);
2959
2960 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2961 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2962 'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
2963 'update_investor_agreement = '||l_return_status );
2964 END IF;
2965
2966 -- *************
2967 -- Update IA Transaction
2968 -- *************
2969
2970 update_transaction(
2971 p_ia_rec => l_ia_rec,
2972 p_termination_date => l_sys_date,
2973 p_tcnv_rec => l_tcnv_rec,
2974 x_return_status => l_return_status);
2975
2976 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2977 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2978 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
2979 'update_transaction = '||l_return_status );
2980 END IF;
2981
2982 -- raise exception if api failed
2983 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2984 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2985 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2986 RAISE G_EXCEPTION_ERROR;
2987 END IF;
2988
2989 -- Set the return status
2990 x_return_status := l_return_status;
2991
2992 -- Store messages in TRX_MSGS
2993 OKL_AM_UTIL_PVT.process_messages(
2994 p_trx_source_table => 'OKL_TRX_CONTRACTS',
2995 p_trx_id => l_tcnv_rec.id,
2996 x_return_status => l_return_status);
2997
2998 -- Set the output log if request from BATCH
2999 IF p_control_flag LIKE 'BATCH%' THEN
3000
3001 -- get the latest status
3002 FOR ia_status_rec IN ia_status_csr( l_ia_rec.id) LOOP
3003 l_ia_rec.sts_code := ia_status_rec.status;
3004 END LOOP;
3005
3006 fnd_output (
3007 p_ia_rec => l_ia_rec,
3008 p_control_flag => l_tcnv_rec.tsu_code);
3009
3010 END IF;
3011
3012 -- End Activity
3013 OKL_API.end_activity (x_msg_count, x_msg_data);
3014
3015 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3016 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3017 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
3018 'End(-)');
3019 END IF;
3020
3021 EXCEPTION
3022
3023 WHEN G_EXCEPTION THEN
3024
3025 -- Store messages in TRX_MSGS
3026 OKL_AM_UTIL_PVT.process_messages(
3027 p_trx_source_table => 'OKL_TRX_CONTRACTS',
3028 p_trx_id => l_tcnv_rec.id,
3029 x_return_status => l_return_status);
3030
3031 x_return_status := G_RET_STS_SUCCESS;
3032
3033 -- Set the output log if request from BATCH
3034 IF p_control_flag LIKE 'BATCH%' THEN
3035 fnd_output (
3036 p_ia_rec => l_ia_rec,
3037 p_control_flag => 'ERROR');
3038 END IF;
3039
3040 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3041 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3042 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
3043 'EXP - G_EXCEPTION');
3044 END IF;
3045
3046 WHEN G_EXCEPTION_ERROR THEN
3047
3048 -- Set the output log if request from BATCH
3049 IF p_control_flag LIKE 'BATCH%' THEN
3050 fnd_output (
3051 p_ia_rec => l_ia_rec,
3052 p_control_flag => 'ERROR');
3053 END IF;
3054
3055 x_return_status := OKL_API.handle_exceptions(
3056 p_api_name => l_api_name,
3057 p_pkg_name => G_PKG_NAME,
3058 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3059 x_msg_count => x_msg_count,
3060 x_msg_data => x_msg_data,
3061 p_api_type => '_PVT');
3062
3063 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3064 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3065 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
3066 'EXP - G_EXCEPTION_ERROR');
3067 END IF;
3068
3069 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3070
3071 -- Set the output log if request from BATCH
3072 IF p_control_flag LIKE 'BATCH%' THEN
3073 fnd_output (
3074 p_ia_rec => l_ia_rec,
3075 p_control_flag => 'ERROR');
3076 END IF;
3077
3078 x_return_status := OKL_API.handle_exceptions(
3079 p_api_name => l_api_name,
3080 p_pkg_name => G_PKG_NAME,
3081 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3082 x_msg_count => x_msg_count,
3083 x_msg_data => x_msg_data,
3084 p_api_type => '_PVT');
3085
3086 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3087 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3088 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
3089 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
3090 END IF;
3091
3092 WHEN OTHERS THEN
3093
3094 -- Set the output log if request from BATCH
3095 IF p_control_flag LIKE 'BATCH%' THEN
3096 fnd_output (
3097 p_ia_rec => l_ia_rec,
3098 p_control_flag => 'ERROR');
3099 END IF;
3100
3101 x_return_status := OKL_API.handle_exceptions(
3102 p_api_name => l_api_name,
3103 p_pkg_name => G_PKG_NAME,
3104 p_exc_name => 'OTHERS',
3105 x_msg_count => x_msg_count,
3106 x_msg_data => x_msg_data,
3107 p_api_type => '_PVT');
3108
3109 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3110 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3111 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_investor_agreement.',
3112 'EXP - OTHERS');
3113 END IF;
3114
3115 END terminate_investor_agreement;
3116
3117 -- Start of comments
3118 --
3119 -- Procedure Name : concurrent_expire_inv_agrmt
3120 -- Description : This procedure is called by concurrent manager to terminate
3121 -- ended investor agreements. When running the concurrent
3122 -- manager request, a request can be made for a single IA to
3123 -- be terminated or else all the ended IAs will be picked
3124 -- If No End Date is Passed Defaulted to SysDate
3125 -- Business Rules :
3126 -- Parameters :
3127 -- Version : 1.0
3128 -- History : RMUNJULU Created
3129 -- : RMUNJULU 115.4 3061748 Added code to throw error if
3130 -- Termination Date is invalid
3131 --
3132 -- End of comments
3133 PROCEDURE concurrent_expire_inv_agrmt(
3134 errbuf OUT NOCOPY VARCHAR2,
3135 retcode OUT NOCOPY VARCHAR2,
3136 p_api_version IN VARCHAR2,
3137 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
3138 p_ia_id IN VARCHAR2 DEFAULT NULL,
3139 p_date IN VARCHAR2 DEFAULT NULL) IS
3140
3141 -- Get the IAs which have reached their end_date and still active and no processed trn exists
3142 CURSOR get_expired_ia_csr (p_date IN DATE) IS
3143 SELECT CHR.id,
3144 CHR.contract_number
3145 FROM OKC_K_HEADERS_B CHR
3146 WHERE CHR.scs_code = 'INVESTOR' -- IA
3147 AND CHR.sts_code = 'ACTIVE' -- ACTIVE
3148 AND CHR.date_terminated IS NULL -- Not Terminated
3149 AND CHR.end_date <= TRUNC(p_date) -- Ended -- RMUNJULU 115.4 3061748 Changed to pick equal dates
3150 AND CHR.id NOT IN (SELECT TRX.khr_id FROM OKL_TRX_CONTRACTS TRX -- Dont get IA's with Processed TRN
3151 WHERE TRX.tsu_code = 'PROCESSED'
3152 --rkuttiya added for 12.1.1 Multi GAAP
3153 AND TRX.representation_type = 'PRIMARY'
3154 --
3155 AND TRX.tcn_type = 'IAT');
3156
3157 l_return_status VARCHAR2(1);
3158 l_msg_count NUMBER;
3159 l_msg_data VARCHAR2(2000);
3160 l_sys_date DATE;
3161 l_date DATE;
3162 l_api_version NUMBER;
3163 l_ia_id NUMBER;
3164 l_ia_rec ia_rec_type;
3165
3166 BEGIN
3167
3168 -- Initialize message list
3169 OKL_API.init_msg_list('T');
3170
3171 SELECT SYSDATE INTO l_sys_date FROM DUAL;
3172
3173 IF p_date IS NULL THEN
3174 l_date := l_sys_date;
3175 ELSE
3176 l_date := TO_DATE(SUBSTR(p_date,1,10),'RRRR/MM/DD');
3177 IF l_date > TRUNC(l_sys_date) THEN
3178 -- RMUNJULU 115.4 3061748
3179 G_ERROR := 'Y';
3180 -- l_date := l_sys_date;
3181 END IF;
3182 END IF;
3183
3184 -- RMUNJULU 115.4 3061748
3185 IF G_ERROR <> 'Y' THEN
3186
3187 G_INV_ENDED_BY_DATE := TRUNC(l_date);
3188
3189 l_api_version := TO_NUMBER(p_api_version);
3190 l_ia_id := TO_NUMBER(p_ia_id);
3191
3192 -- Check if a single IA termination request
3193 IF l_ia_id IS NOT NULL THEN
3194
3195 l_ia_rec.id := l_ia_id;
3196
3197 -- Terminate the IA
3198 terminate_investor_agreement(
3199 p_api_version => l_api_version,
3200 p_init_msg_list => G_FALSE,
3201 x_return_status => l_return_status,
3202 x_msg_count => l_msg_count,
3203 x_msg_data => l_msg_data,
3204 p_ia_rec => l_ia_rec,
3205 p_control_flag => 'BATCH_SINGLE');
3206
3207 ELSE -- No IA passed, so scheduled request to terminate all expired IAs
3208
3209 -- Loop thru the expired IAs
3210 FOR get_expired_ia_rec IN get_expired_ia_csr(G_INV_ENDED_BY_DATE) LOOP
3211
3212 l_ia_rec.id := get_expired_ia_rec.id;
3213 l_ia_rec.contract_number := get_expired_ia_rec.contract_number;
3214
3215 -- Terminate the IA
3216 terminate_investor_agreement(
3217 p_api_version => l_api_version,
3218 p_init_msg_list => G_TRUE,
3219 x_return_status => l_return_status,
3220 x_msg_count => l_msg_count,
3221 x_msg_data => l_msg_data,
3222 p_ia_rec => l_ia_rec,
3223 p_control_flag => 'BATCH_MULTIPLE');
3224
3225 END LOOP;
3226 END IF;
3227 END IF;
3228
3229 -- Create the Output Report
3230 create_report;
3231
3232 EXCEPTION
3233
3234 WHEN OTHERS THEN
3235 -- Set the oracle error message
3236 OKL_API.set_message(
3237 p_app_name => G_APP_NAME_1,
3238 p_msg_name => G_UNEXPECTED_ERROR,
3239 p_token1 => G_SQLCODE_TOKEN,
3240 p_token1_value => SQLCODE,
3241 p_token2 => G_SQLERRM_TOKEN,
3242 p_token2_value => SQLERRM);
3243
3244 END concurrent_expire_inv_agrmt;
3245
3246 END OKL_AM_TERMINATE_INV_AGMT_PVT;