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