[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_BTCH_EXP_LEASE_LOAN_PVT
Source
1 PACKAGE BODY okl_am_btch_exp_lease_loan_pvt AS
2 /* $Header: OKLRLLBB.pls 120.18 2011/04/05 07:22:21 bkatraga ship $ */
3
4 -- RMUNJULU PERF
5 TYPE req_tab_type IS TABLE OF NUMBER
6 INDEX BY BINARY_INTEGER;
7
8 TYPE batch_tab_type IS TABLE OF VARCHAR2 (30)
9 INDEX BY BINARY_INTEGER;
10
11 -- RMUNJULU 2730738 Added GLOBAL PACKAGE BODY VARIABLES for proper output file
12 success_exp_message_table message_tbl_type;
13 error_exp_message_table message_tbl_type;
14 success_recy_message_table message_tbl_type;
15 error_recy_message_table message_tbl_type;
16 l_success_exp_tbl_index NUMBER := 0;
17 l_error_exp_tbl_index NUMBER := 0;
18 l_success_recy_tbl_index NUMBER := 0;
19 l_error_recy_tbl_index NUMBER := 0;
20 g_first CONSTANT NUMBER := fnd_msg_pub.g_first;
21 g_next CONSTANT NUMBER := fnd_msg_pub.g_next;
22 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
23 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
24 g_prin_bal_zero VARCHAR2 (3);
25 --akrangan added for debug logging begin
26 g_module_name VARCHAR2 (255)
27 := 'okl.am.plsql.okl_am_btch_exp_lease_loan_pvt';
28 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
29 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
30 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
31
32 --akrangan added for debug logging end
33
34 -- Start of comments
35 --
36 -- Procedure Name : RESET_ASSET_MSG_TBL
37 -- Desciption : Resets the ASSET_MSG_TBL
38 -- Business Rules :
39 -- Parameters :
40 -- Version : 1.0
41 -- History : RMUNJULU 2730738 created for proper output file
42 --
43 -- End of comments
44 PROCEDURE reset_asset_msg_tbl IS
45 BEGIN
46 asset_msg_tbl.DELETE;
47 g_msg_tbl_counter := 1;
48 END reset_asset_msg_tbl;
49
50 -- Start of comments
51 --
52 -- Procedure Name : POP_ASSET_MSG_TBL
53 -- Desciption : Populates the ASSET_MSG_TBL
54 -- Business Rules :
55 -- Parameters :
56 -- Version : 1.0
57 -- History : RMUNJULU 2730738 created for proper output file
58 --
59 -- End of comments
60 PROCEDURE pop_asset_msg_tbl IS
61 lx_error_rec okl_api.error_rec_type;
62 l_msg_idx INTEGER := g_first;
63 l_msg_tbl msg_tbl_type;
64 BEGIN
65 -- Get the messages in the log
66 LOOP
67 fnd_msg_pub.get (p_msg_index => l_msg_idx,
68 p_encoded => g_false,
69 p_data => lx_error_rec.msg_data,
70 p_msg_index_out => lx_error_rec.msg_count
71 );
72
73 IF (lx_error_rec.msg_count IS NOT NULL) THEN
74 asset_msg_tbl (g_msg_tbl_counter).msg := lx_error_rec.msg_data;
75 g_msg_tbl_counter := g_msg_tbl_counter + 1;
76 END IF;
77
78 EXIT WHEN ( (lx_error_rec.msg_count = fnd_msg_pub.count_msg)
79 OR (lx_error_rec.msg_count IS NULL)
80 );
81 l_msg_idx := g_next;
82 END LOOP;
83 END pop_asset_msg_tbl;
84
85 -- Start of comments
86 --
87 -- Procedure Name : fnd_output
88 -- Desciption : Logs the messages in the output log
89 -- Business Rules :
90 -- Parameters :
91 -- Version : 1.0
92 -- History : RMUNJULU 2730738 created for proper output file
93 -- : rmunjulu 4016497 Changed to cater for NON EXPIRED/NON RECYCLED Contract
94 --
95 -- End of comments
96 PROCEDURE fnd_output (
97 p_chr_id IN NUMBER,
98 p_chr_number IN VARCHAR2,
99 p_start_date IN DATE,
100 p_end_date IN DATE,
101 p_status IN VARCHAR2,
102 p_exp_recy IN VARCHAR2,
103 p_control_flag IN VARCHAR2
104 ) IS
105 lx_error_rec okl_api.error_rec_type;
106 l_msg_idx INTEGER := g_first;
107 l_msg_tbl msg_tbl_type;
108 -- akrangan added for debug feature start
109 l_module_name VARCHAR2 (500) := g_module_name || 'fnd_output';
110 is_debug_exception_on BOOLEAN
111 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
112 is_debug_procedure_on BOOLEAN
113 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
114 is_debug_statement_on BOOLEAN
115 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
116 -- akrangan added for debug feature end
117 BEGIN
118 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
119 -- of db/Pl Sql Commented out the code that populates the message table as it was not being used to display messages.
120 /*
121 -- Get the messages in the log
122 LOOP
123
124 FND_MSG_PUB.get(
125 p_msg_index => l_msg_idx,
126 p_encoded => G_FALSE,
127 p_data => lx_error_rec.msg_data,
128 p_msg_index_out => lx_error_rec.msg_count);
129
130
131 IF (lx_error_rec.msg_count IS NOT NULL) THEN
132 l_msg_tbl(lx_error_rec.msg_count).msg := lx_error_rec.msg_data;
133 END IF;
134
135
136 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
137 OR (lx_error_rec.msg_count IS NULL));
138
139 l_msg_idx := G_NEXT;
140
141 END LOOP;
142 */
143 IF (is_debug_procedure_on) THEN
144 okl_debug_pub.log_debug (g_level_procedure,
145 l_module_name,
146 'Begin(+)'
147 );
148 END IF;
149
150 IF (is_debug_statement_on) THEN
151 okl_debug_pub.log_debug (g_level_statement,
152 l_module_name,
153 'p_chr_id =' || p_chr_id
154 );
155 okl_debug_pub.log_debug (g_level_statement,
156 l_module_name,
157 'p_chr_number =' || p_chr_number
158 );
159 okl_debug_pub.log_debug (g_level_statement,
160 l_module_name,
161 'p_start_date =' || p_start_date
162 );
163 okl_debug_pub.log_debug (g_level_statement,
164 l_module_name,
165 'p_end_date =' || p_end_date
166 );
167 okl_debug_pub.log_debug (g_level_statement,
168 l_module_name,
169 'p_status =' || p_status
170 );
171 okl_debug_pub.log_debug (g_level_statement,
172 l_module_name,
173 'p_exp_recy =' || p_exp_recy
174 );
175 okl_debug_pub.log_debug (g_level_statement,
176 l_module_name,
177 'p_control_flag =' || p_control_flag
178 );
179 END IF;
180
181 IF p_control_flag = 'SUCCESS' THEN
182 IF p_exp_recy = 'EXP' THEN
183 l_success_exp_tbl_index := l_success_exp_tbl_index + 1;
184 success_exp_message_table (l_success_exp_tbl_index).ID :=
185 p_chr_id;
186 success_exp_message_table (l_success_exp_tbl_index).contract_number :=
187 p_chr_number;
188 success_exp_message_table (l_success_exp_tbl_index).start_date :=
189 p_start_date;
190 success_exp_message_table (l_success_exp_tbl_index).end_date :=
191 p_end_date;
192 success_exp_message_table (l_success_exp_tbl_index).status :=
193 p_status;
194 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
195 -- of db/Pl Sql Removed the msg_tbl field as it was not being used to display messages.
196
197 -- success_exp_message_table(l_success_exp_tbl_index).msg_tbl := l_msg_tbl;
198 ELSE -- RECY
199 IF asset_msg_tbl.COUNT > 0 THEN -- Partial Termination
200 l_success_recy_tbl_index := l_success_recy_tbl_index + 1;
201 success_recy_message_table (l_success_recy_tbl_index).ID :=
202 p_chr_id;
203 success_recy_message_table (l_success_recy_tbl_index).contract_number :=
204 p_chr_number;
205 success_recy_message_table (l_success_recy_tbl_index).start_date :=
206 p_start_date;
207 success_recy_message_table (l_success_recy_tbl_index).end_date :=
208 p_end_date;
209 success_recy_message_table (l_success_recy_tbl_index).status :=
210 p_status;
211 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
212 -- of db/Pl Sql Removed the msg_tbl field as it was not being used to display messages.
213
214 --success_recy_message_table(l_success_recy_tbl_index).msg_tbl := ASSET_MSG_TBL;
215 ELSE -- Full Termination
216 l_success_recy_tbl_index := l_success_recy_tbl_index + 1;
217 success_recy_message_table (l_success_recy_tbl_index).ID :=
218 p_chr_id;
219 success_recy_message_table (l_success_recy_tbl_index).contract_number :=
220 p_chr_number;
221 success_recy_message_table (l_success_recy_tbl_index).start_date :=
222 p_start_date;
223 success_recy_message_table (l_success_recy_tbl_index).end_date :=
224 p_end_date;
225 success_recy_message_table (l_success_recy_tbl_index).status :=
226 p_status;
227 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
228 -- of db/Pl Sql Removed the msg_tbl field as it was not being used to display messages.
229
230 --success_recy_message_table(l_success_recy_tbl_index).msg_tbl := l_msg_tbl;
231 END IF;
232 END IF;
233 ELSIF p_control_flag = 'FAIL' THEN -- FAIL
234 IF p_exp_recy = 'EXP' THEN
235 l_error_exp_tbl_index := l_error_exp_tbl_index + 1;
236 error_exp_message_table (l_error_exp_tbl_index).ID := p_chr_id;
237 error_exp_message_table (l_error_exp_tbl_index).contract_number :=
238 p_chr_number;
239 error_exp_message_table (l_error_exp_tbl_index).start_date :=
240 p_start_date;
241 error_exp_message_table (l_error_exp_tbl_index).end_date :=
242 p_end_date;
243 error_exp_message_table (l_error_exp_tbl_index).status :=
244 p_status;
245 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
246 -- of db/Pl Sql Removed the msg_tbl field as it was not being used to display messages.
247 --error_exp_message_table(l_error_exp_tbl_index).msg_tbl := l_msg_tbl;
248 ELSE -- RECY
249 IF asset_msg_tbl.COUNT > 0 THEN -- Partial Termination
250 l_error_recy_tbl_index := l_error_recy_tbl_index + 1;
251 error_recy_message_table (l_error_recy_tbl_index).ID :=
252 p_chr_id;
253 error_recy_message_table (l_error_recy_tbl_index).contract_number :=
254 p_chr_number;
255 error_recy_message_table (l_error_recy_tbl_index).start_date :=
256 p_start_date;
257 error_recy_message_table (l_error_recy_tbl_index).end_date :=
258 p_end_date;
259 error_recy_message_table (l_error_recy_tbl_index).status :=
260 p_status;
261 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
262 -- of db/Pl Sql Removed the msg_tbl field as it was not being used to display messages.
263 --error_recy_message_table(l_error_recy_tbl_index).msg_tbl := ASSET_MSG_TBL;
264 ELSE -- Full Termination
265 l_error_recy_tbl_index := l_error_recy_tbl_index + 1;
266 error_recy_message_table (l_error_recy_tbl_index).ID :=
267 p_chr_id;
268 error_recy_message_table (l_error_recy_tbl_index).contract_number :=
269 p_chr_number;
270 error_recy_message_table (l_error_recy_tbl_index).start_date :=
271 p_start_date;
272 error_recy_message_table (l_error_recy_tbl_index).end_date :=
273 p_end_date;
274 error_recy_message_table (l_error_recy_tbl_index).status :=
275 p_status;
276 -- SECHAWLA 26-JAN-04 3377730: A table can not have a table or record with composite fields on lower versions
277 -- of db/Pl Sql Removed the msg_tbl field as it was not being used to display messages.
278 --error_recy_message_table(l_error_recy_tbl_index).msg_tbl := l_msg_tbl;
279 END IF;
280 END IF;
281 ELSE -- Other than SUCCESS and FAIL
282 l_error_exp_tbl_index := l_error_exp_tbl_index + 1;
283 error_exp_message_table (l_error_exp_tbl_index).ID := p_chr_id;
284 error_exp_message_table (l_error_exp_tbl_index).contract_number :=
285 p_chr_number;
286 error_exp_message_table (l_error_exp_tbl_index).start_date :=
287 p_start_date;
288 error_exp_message_table (l_error_exp_tbl_index).end_date :=
289 p_end_date;
290 error_exp_message_table (l_error_exp_tbl_index).status := p_status;
291 END IF;
292
293 IF (is_debug_procedure_on) THEN
294 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
295 END IF;
296 EXCEPTION
297 WHEN OTHERS THEN
298 IF (is_debug_exception_on) THEN
299 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
300 || sqlcode || ' , SQLERRM : ' || sqlerrm);
301 END IF;
302 -- Set the oracle error message
303 okl_api.set_message (p_app_name => okc_api.g_app_name,
304 p_msg_name => g_unexpected_error,
305 p_token1 => g_sqlcode_token,
306 p_token1_value => SQLCODE,
307 p_token2 => g_sqlerrm_token,
308 p_token2_value => SQLERRM
309 );
310 END fnd_output;
311
312 -- Start of comments
313 --
314 -- Procedure Name : create_report
315 -- Desciption : Creates the Output and Log Reports
316 -- Business Rules :
317 -- Parameters :
318 -- Version : 1.0
319 -- History : RMUNJULU 2730738 created for proper output file
320 -- rmunjulu 4016497 Added parameters and modified to check for parameters
321 --
322 -- End of comments
323 PROCEDURE create_report (
324 p_source IN VARCHAR2 DEFAULT NULL,
325 -- rmunjulu 4016497 Added parameter
326 p_message IN VARCHAR2 DEFAULT NULL
327 ) IS -- rmunjulu 4016497 Added parameter
328 i NUMBER;
329 j NUMBER;
330 l_success_exp NUMBER;
331 l_success_recy NUMBER;
332 l_error_exp NUMBER;
333 l_error_recy NUMBER;
334 l_total_exp NUMBER;
335 l_total_recy NUMBER;
336
337 -- Get the Org Name
338 CURSOR org_csr (p_org_id IN NUMBER) IS
339 SELECT hou.NAME
340 FROM hr_operating_units hou
341 WHERE hou.organization_id = p_org_id;
342
343 l_org_id NUMBER := mo_global.get_current_org_id
344 ();
345 l_org_name VARCHAR2 (300);
346 l_orcl_logo VARCHAR2 (300);
347 l_term_heading VARCHAR2 (300);
348 l_set_of_books VARCHAR2 (300);
349 l_set_of_books_name VARCHAR2 (300);
350 l_run_date VARCHAR2 (300);
351 l_oper_unit VARCHAR2 (300);
352 l_type VARCHAR2 (300);
353 l_expired_k VARCHAR2 (300);
354 l_recy_k VARCHAR2 (300);
355 l_k_term_succ VARCHAR2 (300);
356 l_k_not_term VARCHAR2 (300);
357 l_exp_k_err VARCHAR2 (300);
358 l_serial VARCHAR2 (300);
359 l_k_num VARCHAR2 (300);
360 l_start_date VARCHAR2 (300);
361 l_end_date VARCHAR2 (300);
362 l_status VARCHAR2 (300);
363 l_messages VARCHAR2 (300);
364 l_recy_k_err VARCHAR2 (300);
365 l_succ_exp_k VARCHAR2 (300);
366 l_succ_recy_k VARCHAR2 (300);
367 l_eop VARCHAR2 (300);
368 l_printed VARCHAR2 (1);
369 -- akrangan added for debug feature start
370 l_module_name VARCHAR2 (500)
371 := g_module_name || 'create_report';
372 is_debug_exception_on BOOLEAN
373 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
374 is_debug_procedure_on BOOLEAN
375 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
376 is_debug_statement_on BOOLEAN
377 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
378 -- akrangan added for debug feature end
379 BEGIN
380 IF (is_debug_procedure_on) THEN
381 okl_debug_pub.log_debug (g_level_procedure,
382 l_module_name,
383 'Begin(+)'
384 );
385 END IF;
386
387 IF (is_debug_statement_on) THEN
388 okl_debug_pub.log_debug (g_level_statement,
389 l_module_name,
390 'p_source =' || p_source
391 );
392 okl_debug_pub.log_debug (g_level_statement,
393 l_module_name,
394 'p_message =' || p_message
395 );
396 END IF;
397
398 l_success_exp := success_exp_message_table.COUNT;
399 l_success_recy := success_recy_message_table.COUNT;
400 l_error_exp := error_exp_message_table.COUNT;
401 l_error_recy := error_recy_message_table.COUNT;
402 l_total_exp := l_success_exp + l_error_exp;
403 l_total_recy := l_success_recy + l_error_recy;
404
405 -- Get the Org Name
406 FOR org_rec IN org_csr (l_org_id)
407 LOOP
408 l_org_name := org_rec.NAME;
409 END LOOP;
410
411 l_set_of_books_name :=
412 okl_accounting_util.get_set_of_books_name
413 (okl_accounting_util.get_set_of_books_id);
414 -- Get all the tokens
415 l_orcl_logo :=
416 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
417 'OKL_ACCT_LEASE_MANAGEMENT'
418 );
419 l_term_heading :=
420 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
421 'OKL_AM_TERM_EXP_K'
422 );
423 -- 'Terminate Expired Contracts';
424 l_set_of_books :=
425 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
426 'OKL_SET_OF_BOOKS'
427 );
428 l_run_date :=
429 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
430 'OKL_RUN_DATE'
431 );
432 l_oper_unit :=
433 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
434 'OKL_OPERUNIT'
435 );
436 l_type :=
437 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
438 'OKL_TYPE'
439 );
440 l_expired_k :=
441 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
442 'OKL_AM_EXP_K'
443 ); -- 'Expired Contracts';
444 l_recy_k :=
445 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
446 'OKL_AM_RECY_K'
447 ); -- 'Recycled Contracts';
448 l_k_term_succ :=
449 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
450 'OKL_AM_SUCCESS'
451 ); -- 'Successful';
452 l_k_not_term :=
453 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
454 'OKL_AM_ERROR'
455 ); -- 'Errored';
456 l_exp_k_err :=
457 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
458 'OKL_AM_EXP_ERROR'
459 );
460 -- 'Expired Contracts With Errors';
461 l_serial :=
462 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
463 'OKL_SERIAL_NUMBER'
464 );
465 l_k_num :=
466 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
467 'OKL_AM_K_NUMBER'
468 ); -- 'Contract Number';
469 l_start_date :=
470 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
471 'OKL_START_DATE'
472 );
473 l_end_date :=
474 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
475 'OKL_END_DATE'
476 );
477 l_status :=
478 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
479 'OKL_STATUS'
480 );
481 l_messages :=
482 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
483 'OKL_MESSAGES'
484 );
485 l_recy_k_err :=
486 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
487 'OKL_AM_RECY_ERROR'
488 );
489 -- 'Recycled Contracts With Errors';
490 l_succ_exp_k :=
491 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
492 'OKL_AM_SUCCESS_EXP'
493 );
494 -- 'Successfully Expired Contracts';
495 l_eop :=
496 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
497 'OKL_END_OF_REPORT'
498 );
499 l_succ_recy_k :=
500 okl_accounting_util.get_message_token ('OKL_AM_CONC_OUTPUT',
501 'OKL_AM_SUCCESS_RECY'
502 );
503 --'Successfully Recycled Contracts';
504 -- Log --
505 fnd_file.put_line (fnd_file.LOG, '');
506 fnd_file.put_line (fnd_file.LOG, RPAD ('=', 128, '='));
507 fnd_file.put_line (fnd_file.LOG, '');
508 fnd_file.put_line (fnd_file.LOG,
509 l_type
510 || RPAD (' ', 40 - LENGTH (l_type), ' ')
511 || l_expired_k
512 || RPAD (' ', 35 - LENGTH (l_expired_k), ' ')
513 || l_recy_k
514 || RPAD (' ', 35 - LENGTH (l_recy_k), ' ')
515 );
516 fnd_file.put_line (fnd_file.LOG, RPAD ('-', 128, '-'));
517 fnd_file.put_line (fnd_file.LOG,
518 l_k_term_succ
519 || RPAD (' ', 40 - LENGTH (l_k_term_succ), ' ')
520 || l_success_exp
521 || RPAD (' ', 35 - LENGTH (l_success_exp), ' ')
522 || l_success_recy
523 || RPAD (' ', 35 - LENGTH (l_success_recy), ' ')
524 );
525 fnd_file.put_line (fnd_file.LOG,
526 l_k_not_term
527 || RPAD (' ', 40 - LENGTH (l_k_not_term), ' ')
528 || l_error_exp
529 || RPAD (' ', 35 - LENGTH (l_error_exp), ' ')
530 || l_error_recy
531 || RPAD (' ', 35 - LENGTH (l_error_recy), ' ')
532 );
533 fnd_file.put_line (fnd_file.LOG, '');
534 fnd_file.put_line (fnd_file.LOG, RPAD ('=', 128, '='));
535 -- Output --
536 fnd_file.put_line (fnd_file.output,
537 RPAD (' ', 128 / 2 - LENGTH (l_orcl_logo) / 2,
538 ' ')
539 || l_orcl_logo
540 );
541 fnd_file.put_line (fnd_file.output,
542 RPAD (' ',
543 128 / 2 - LENGTH (l_term_heading) / 2,
544 ' '
545 )
546 || l_term_heading
547 );
548 fnd_file.put_line (fnd_file.output,
549 RPAD (' ',
550 128 / 2 - LENGTH (l_term_heading) / 2,
551 ' '
552 )
553 || RPAD ('-', LENGTH (l_term_heading), '-')
554 );
555 fnd_file.put_line (fnd_file.output, '');
556 fnd_file.put_line (fnd_file.output,
557 l_set_of_books
558 || ': '
559 || RPAD (SUBSTR (l_set_of_books_name, 1, 60), 60,
560 ' ')
561 || LPAD (' ', 25, ' ')
562 || l_run_date
563 || ':'
564 || SUBSTR (TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI'),
565 1,
566 27
567 )
568 );
569 fnd_file.put_line (fnd_file.output,
570 l_oper_unit || ':' || SUBSTR (l_org_name, 1, 30)
571 );
572 fnd_file.put_line (fnd_file.output, '');
573 fnd_file.put_line (fnd_file.output, '');
574 fnd_file.put_line (fnd_file.output,
575 l_type
576 || RPAD (' ', 40 - LENGTH (l_type), ' ')
577 || l_expired_k
578 || RPAD (' ', 35 - LENGTH (l_expired_k), ' ')
579 || l_recy_k
580 || RPAD (' ', 35 - LENGTH (l_recy_k), ' ')
581 );
582 fnd_file.put_line (fnd_file.output, RPAD ('-', 128, '-'));
583 fnd_file.put_line (fnd_file.output,
584 l_k_term_succ
585 || RPAD (' ', 40 - LENGTH (l_k_term_succ), ' ')
586 || l_success_exp
587 || RPAD (' ', 35 - LENGTH (l_success_exp), ' ')
588 || l_success_recy
589 || RPAD (' ', 35 - LENGTH (l_success_recy), ' ')
590 );
591 fnd_file.put_line (fnd_file.output,
592 l_k_not_term
593 || RPAD (' ', 40 - LENGTH (l_k_not_term), ' ')
594 || l_error_exp
595 || RPAD (' ', 35 - LENGTH (l_error_exp), ' ')
596 || l_error_recy
597 || RPAD (' ', 35 - LENGTH (l_error_recy), ' ')
598 );
599 fnd_file.put_line (fnd_file.output, '');
600 fnd_file.put_line (fnd_file.output, RPAD ('=', 128, '='));
601 fnd_file.put_line (fnd_file.output, '');
602
603 IF p_source IS NULL THEN -- rmunjulu 4016497
604 -- errored expired contracts
605 IF l_error_exp > 0 THEN
606 fnd_file.put_line (fnd_file.output, '');
607 fnd_file.put_line (fnd_file.output, l_exp_k_err);
608 fnd_file.put_line (fnd_file.output,
609 RPAD ('-', LENGTH (l_exp_k_err), '-')
610 );
611 fnd_file.put_line (fnd_file.output, '');
612 l_printed := 'N';
613
614 -- Display the contract details
615 FOR i IN
616 error_exp_message_table.FIRST .. error_exp_message_table.LAST
617 LOOP
618 -- Print Header only once
619 IF l_printed = 'N' THEN
620 fnd_file.put_line (fnd_file.output,
621 l_serial
622 || RPAD (' ', 15 - LENGTH (l_serial),
623 ' ')
624 || l_k_num
625 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
626 || l_start_date
627 || RPAD (' ',
628 15 - LENGTH (l_start_date),
629 ' '
630 )
631 || l_end_date
632 || RPAD (' ',
633 15 - LENGTH (l_end_date),
634 ' '
635 )
636 || l_status
637 || RPAD (' ', 15 - LENGTH (l_status),
638 ' ')
639 );
640 fnd_file.put_line (fnd_file.output,
641 RPAD ('-', LENGTH (l_serial), '-')
642 || RPAD (' ', 15 - LENGTH (l_serial),
643 ' ')
644 || RPAD ('-', LENGTH (l_k_num), '-')
645 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
646 || RPAD ('-', LENGTH (l_start_date), '-')
647 || RPAD (' ',
648 15 - LENGTH (l_start_date),
649 ' '
650 )
651 || RPAD ('-', LENGTH (l_end_date), '-')
652 || RPAD (' ',
653 15 - LENGTH (l_end_date),
654 ' '
655 )
656 || RPAD ('-', LENGTH (l_status), '-')
657 || RPAD (' ', 15 - LENGTH (l_status),
658 ' ')
659 );
660 END IF;
661
662 l_printed := 'Y';
663 fnd_file.put_line
664 (fnd_file.output,
665 i
666 || RPAD (' ', 15 - LENGTH (i), ' ')
667 || error_exp_message_table (i).contract_number
668 || RPAD
669 (' ',
670 35
671 - LENGTH
672 (error_exp_message_table (i).contract_number
673 ),
674 ' '
675 )
676 || error_exp_message_table (i).start_date
677 || RPAD (' ',
678 15
679 - LENGTH (error_exp_message_table (i).start_date),
680 ' '
681 )
682 || error_exp_message_table (i).end_date
683 || RPAD (' ',
684 15 - LENGTH (error_exp_message_table (i).end_date),
685 ' '
686 )
687 || error_exp_message_table (i).status
688 || RPAD (' ',
689 15 - LENGTH (error_exp_message_table (i).status),
690 ' '
691 )
692 );
693
694 --FND_FILE.put_line(FND_FILE.output,'');
695
696 --FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
697
698 -- Get the messages in the log
699 --FOR j IN error_exp_message_table(i).msg_tbl.FIRST..error_exp_message_table(i).msg_tbl.LAST LOOP
700 --FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || j || ': ' || error_exp_message_table(i).msg_tbl(j).msg);
701 --END LOOP;
702
703 --FND_FILE.put_line(FND_FILE.output,'');
704 IF NVL (g_prin_bal_zero, 'N') = 'Y' THEN
705 fnd_file.put_line (fnd_file.output, '');
706 fnd_file.put_line (fnd_file.output,
707 RPAD (' ', 5, ' ') || l_messages || ' :'
708 );
709 fnd_file.put
710 (fnd_file.output,
711 RPAD (' ', 5, ' ')
712 || 1
713 || ': '
714 || 'Principal Balance for this contract is not Zero.'
715 );
716 fnd_file.put_line (fnd_file.output, '');
717 END IF;
718 END LOOP;
719 END IF;
720
721 -- errorred recycled contracts
722 IF l_error_recy > 0 THEN
723 fnd_file.put_line (fnd_file.output, '');
724 fnd_file.put_line (fnd_file.output, l_recy_k_err);
725 fnd_file.put_line (fnd_file.output,
726 RPAD ('-', LENGTH (l_recy_k_err), '-')
727 );
728 fnd_file.put_line (fnd_file.output, '');
729 l_printed := 'N';
730
731 -- Display the contract details
732 FOR i IN
733 error_recy_message_table.FIRST .. error_recy_message_table.LAST
734 LOOP
735 -- Print Header only once
736 IF l_printed = 'N' THEN
737 fnd_file.put_line (fnd_file.output,
738 l_serial
739 || RPAD (' ', 15 - LENGTH (l_serial),
740 ' ')
741 || l_k_num
742 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
743 || l_start_date
744 || RPAD (' ',
745 15 - LENGTH (l_start_date),
746 ' '
747 )
748 || l_end_date
749 || RPAD (' ',
750 15 - LENGTH (l_end_date),
751 ' '
752 )
753 || l_status
754 || RPAD (' ', 15 - LENGTH (l_status),
755 ' ')
756 );
757 fnd_file.put_line (fnd_file.output,
758 RPAD ('-', LENGTH (l_serial), '-')
759 || RPAD (' ', 15 - LENGTH (l_serial),
760 ' ')
761 || RPAD ('-', LENGTH (l_k_num), '-')
762 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
763 || RPAD ('-', LENGTH (l_start_date), '-')
764 || RPAD (' ',
765 15 - LENGTH (l_start_date),
766 ' '
767 )
768 || RPAD ('-', LENGTH (l_end_date), '-')
769 || RPAD (' ',
770 15 - LENGTH (l_end_date),
771 ' '
772 )
773 || RPAD ('-', LENGTH (l_status), '-')
774 || RPAD (' ', 15 - LENGTH (l_status),
775 ' ')
776 );
777 END IF;
778
779 l_printed := 'Y';
780 fnd_file.put_line
781 (fnd_file.output,
782 i
783 || RPAD (' ', 15 - LENGTH (i), ' ')
784 || error_recy_message_table (i).contract_number
785 || RPAD
786 (' ',
787 35
788 - LENGTH
789 (error_recy_message_table (i).contract_number
790 ),
791 ' '
792 )
793 || error_recy_message_table (i).start_date
794 || RPAD (' ',
795 15
796 - LENGTH (error_recy_message_table (i).start_date),
797 ' '
798 )
799 || error_recy_message_table (i).end_date
800 || RPAD (' ',
801 15
802 - LENGTH (error_recy_message_table (i).end_date),
803 ' '
804 )
805 || error_recy_message_table (i).status
806 || RPAD (' ',
807 15 - LENGTH (error_recy_message_table (i).status),
808 ' '
809 )
810 );
811 --FND_FILE.put_line(FND_FILE.output,'');
812
813 --FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
814
815 -- Get the messages in the log
816 --FOR j IN error_recy_message_table(i).msg_tbl.FIRST..error_recy_message_table(i).msg_tbl.LAST LOOP
817 --FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || j || ': ' || error_recy_message_table(i).msg_tbl(j).msg);
818 --END LOOP;
819
820 --FND_FILE.put_line(FND_FILE.output,'');
821 END LOOP;
822 END IF;
823
824 -- successfully expired contracts
825 IF l_success_exp > 0 THEN
826 fnd_file.put_line (fnd_file.output, '');
827 fnd_file.put_line (fnd_file.output, l_succ_exp_k);
828 fnd_file.put_line (fnd_file.output,
829 RPAD ('-', LENGTH (l_succ_exp_k), '-')
830 );
831 fnd_file.put_line (fnd_file.output, '');
832 l_printed := 'N';
833
834 -- Display the contract details
835 FOR i IN
836 success_exp_message_table.FIRST .. success_exp_message_table.LAST
837 LOOP
838 -- Print Header only once
839 IF l_printed = 'N' THEN
840 fnd_file.put_line (fnd_file.output,
841 l_serial
842 || RPAD (' ', 15 - LENGTH (l_serial),
843 ' ')
844 || l_k_num
845 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
846 || l_start_date
847 || RPAD (' ',
848 15 - LENGTH (l_start_date),
849 ' '
850 )
851 || l_end_date
852 || RPAD (' ',
853 15 - LENGTH (l_end_date),
854 ' '
855 )
856 || l_status
857 || RPAD (' ', 15 - LENGTH (l_status),
858 ' ')
859 );
860 fnd_file.put_line (fnd_file.output,
861 RPAD ('-', LENGTH (l_serial), '-')
862 || RPAD (' ', 15 - LENGTH (l_serial),
863 ' ')
864 || RPAD ('-', LENGTH (l_k_num), '-')
865 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
866 || RPAD ('-', LENGTH (l_start_date), '-')
867 || RPAD (' ',
868 15 - LENGTH (l_start_date),
869 ' '
870 )
871 || RPAD ('-', LENGTH (l_end_date), '-')
872 || RPAD (' ',
873 15 - LENGTH (l_end_date),
874 ' '
875 )
876 || RPAD ('-', LENGTH (l_status), '-')
877 || RPAD (' ', 15 - LENGTH (l_status),
878 ' ')
879 );
880 END IF;
881
882 l_printed := 'Y';
883 fnd_file.put_line
884 (fnd_file.output,
885 i
886 || RPAD (' ', 15 - LENGTH (i), ' ')
887 || success_exp_message_table (i).contract_number
888 || RPAD
889 (' ',
890 35
891 - LENGTH
892 (success_exp_message_table (i).contract_number
893 ),
894 ' '
895 )
896 || success_exp_message_table (i).start_date
897 || RPAD (' ',
898 15
899 - LENGTH (success_exp_message_table (i).start_date),
900 ' '
901 )
902 || success_exp_message_table (i).end_date
903 || RPAD (' ',
904 15
905 - LENGTH (success_exp_message_table (i).end_date),
906 ' '
907 )
908 || success_exp_message_table (i).status
909 || RPAD (' ',
910 15 - LENGTH (success_exp_message_table (i).status),
911 ' '
912 )
913 );
914 END LOOP;
915 END IF;
916
917 -- successfully recycled contracts
918 IF l_success_recy > 0 THEN
919 fnd_file.put_line (fnd_file.output, '');
920 fnd_file.put_line (fnd_file.output, l_succ_recy_k);
921 fnd_file.put_line (fnd_file.output,
922 RPAD ('-', LENGTH (l_succ_recy_k), '-')
923 );
924 fnd_file.put_line (fnd_file.output, '');
925 l_printed := 'N';
926
927 -- Display the contract details
928 FOR i IN
929 success_recy_message_table.FIRST .. success_recy_message_table.LAST
930 LOOP
931 -- Print Header only once
932 IF l_printed = 'N' THEN
933 fnd_file.put_line (fnd_file.output,
934 l_serial
935 || RPAD (' ', 15 - LENGTH (l_serial),
936 ' ')
937 || l_k_num
938 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
939 || l_start_date
940 || RPAD (' ',
941 15 - LENGTH (l_start_date),
942 ' '
943 )
944 || l_end_date
945 || RPAD (' ',
946 15 - LENGTH (l_end_date),
947 ' '
948 )
949 || l_status
950 || RPAD (' ', 15 - LENGTH (l_status),
951 ' ')
952 );
953 fnd_file.put_line (fnd_file.output,
954 RPAD ('-', LENGTH (l_serial), '-')
955 || RPAD (' ', 15 - LENGTH (l_serial),
956 ' ')
957 || RPAD ('-', LENGTH (l_k_num), '-')
958 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
959 || RPAD ('-', LENGTH (l_start_date), '-')
960 || RPAD (' ',
961 15 - LENGTH (l_start_date),
962 ' '
963 )
964 || RPAD ('-', LENGTH (l_end_date), '-')
965 || RPAD (' ',
966 15 - LENGTH (l_end_date),
967 ' '
968 )
969 || RPAD ('-', LENGTH (l_status), '-')
970 || RPAD (' ', 15 - LENGTH (l_status),
971 ' ')
972 );
973 END IF;
974
975 l_printed := 'Y';
976 fnd_file.put_line
977 (fnd_file.output,
978 i
979 || RPAD (' ', 15 - LENGTH (i), ' ')
980 || success_recy_message_table (i).contract_number
981 || RPAD
982 (' ',
983 35
984 - LENGTH
985 (success_recy_message_table (i).contract_number
986 ),
987 ' '
988 )
989 || success_recy_message_table (i).start_date
990 || RPAD (' ',
991 15
992 - LENGTH (success_recy_message_table (i).start_date
993 ),
994 ' '
995 )
996 || success_recy_message_table (i).end_date
997 || RPAD (' ',
998 15
999 - LENGTH (success_recy_message_table (i).end_date),
1000 ' '
1001 )
1002 || success_recy_message_table (i).status
1003 || RPAD (' ',
1004 15
1005 - LENGTH (success_recy_message_table (i).status),
1006 ' '
1007 )
1008 );
1009 END LOOP;
1010 END IF;
1011 ELSE -- p_source IS NOT NULL THEN -- rmunjulu 4016497
1012 fnd_file.put_line (fnd_file.output, '');
1013 fnd_file.put_line (fnd_file.output, l_exp_k_err);
1014 fnd_file.put_line (fnd_file.output,
1015 RPAD ('-', LENGTH (l_exp_k_err), '-')
1016 );
1017 fnd_file.put_line (fnd_file.output, '');
1018 fnd_file.put_line (fnd_file.output,
1019 l_serial
1020 || RPAD (' ', 15 - LENGTH (l_serial), ' ')
1021 || l_k_num
1022 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
1023 || l_start_date
1024 || RPAD (' ', 15 - LENGTH (l_start_date), ' ')
1025 || l_end_date
1026 || RPAD (' ', 15 - LENGTH (l_end_date), ' ')
1027 || l_status
1028 || RPAD (' ', 15 - LENGTH (l_status), ' ')
1029 );
1030 fnd_file.put_line (fnd_file.output,
1031 RPAD ('-', LENGTH (l_serial), '-')
1032 || RPAD (' ', 15 - LENGTH (l_serial), ' ')
1033 || RPAD ('-', LENGTH (l_k_num), '-')
1034 || RPAD (' ', 35 - LENGTH (l_k_num), ' ')
1035 || RPAD ('-', LENGTH (l_start_date), '-')
1036 || RPAD (' ', 15 - LENGTH (l_start_date), ' ')
1037 || RPAD ('-', LENGTH (l_end_date), '-')
1038 || RPAD (' ', 15 - LENGTH (l_end_date), ' ')
1039 || RPAD ('-', LENGTH (l_status), '-')
1040 || RPAD (' ', 15 - LENGTH (l_status), ' ')
1041 );
1042 fnd_file.put_line
1043 (fnd_file.output,
1044 '1'
1045 || RPAD (' ', 14, ' ')
1046 || error_exp_message_table (1).contract_number
1047 || RPAD
1048 (' ',
1049 35
1050 - LENGTH
1051 (error_exp_message_table (1).contract_number
1052 ),
1053 ' '
1054 )
1055 || error_exp_message_table (1).start_date
1056 || RPAD (' ',
1057 15
1058 - LENGTH (error_exp_message_table (1).start_date),
1059 ' '
1060 )
1061 || error_exp_message_table (1).end_date
1062 || RPAD (' ',
1063 15 - LENGTH (error_exp_message_table (1).end_date),
1064 ' '
1065 )
1066 || error_exp_message_table (1).status
1067 || RPAD (' ',
1068 15 - LENGTH (error_exp_message_table (1).status),
1069 ' '
1070 )
1071 );
1072 -- Set message
1073 fnd_file.put_line (fnd_file.output, p_message);
1074 END IF;
1075
1076 fnd_file.put_line (fnd_file.output, '');
1077 fnd_file.put_line (fnd_file.output, '');
1078 fnd_file.put_line (fnd_file.output, RPAD (' ', 53, ' ') || l_eop);
1079
1080 IF (is_debug_procedure_on) THEN
1081 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
1082 END IF;
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 IF (is_debug_exception_on) THEN
1086 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1087 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1088 END IF;
1089 -- Set the oracle error message
1090 okl_api.set_message (p_app_name => okc_api.g_app_name,
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 END create_report;
1098
1099 -- Start of comments
1100 --
1101 -- Procedure Name : check_contract
1102 -- Desciption : checks if contract termination was successful or failure
1103 -- Business Rules :
1104 -- Parameters :
1105 -- Version : 1.0
1106 -- History : RMUNJULU 2730738 created for proper output file
1107 --
1108 -- End of comments
1109 PROCEDURE check_contract (
1110 p_chr_id IN NUMBER,
1111 x_start_date OUT NOCOPY DATE,
1112 x_end_date OUT NOCOPY DATE,
1113 x_status OUT NOCOPY VARCHAR2,
1114 x_control_flag OUT NOCOPY VARCHAR2
1115 ) IS
1116 -- Get contract details
1117 CURSOR l_get_k_dtls_csr (p_chr_id IN NUMBER) IS
1118 SELECT CHR.start_date,
1119 CHR.end_date,
1120 CHR.sts_code
1121 FROM okc_k_headers_b CHR
1122 WHERE CHR.ID = p_chr_id;
1123
1124 -- Get contract termination transaction which is not PROCESSED or CANCELED ie ERROR
1125 CURSOR l_get_k_trn_csr (p_chr_id IN NUMBER) IS
1126 SELECT trn.tmt_status_code status
1127 --akrangan changes for sla tmt_status_code cr
1128 FROM okl_trx_contracts trn
1129 WHERE trn.khr_id = p_chr_id
1130 AND trn.tcn_type IN ('TMT', 'ALT')
1131 --rkuttiya added for 12.1.1 Multi GAAP
1132 AND trn.representation_type = 'PRIMARY'
1133 --
1134 AND trn.tmt_status_code NOT IN ('PROCESSED', 'CANCELED');
1135
1136 --akrangan changes for sla tmt_status_code cr
1137
1138 -- akrangan added for debug feature start
1139 l_module_name VARCHAR2 (500)
1140 := g_module_name || 'check_contract';
1141 is_debug_exception_on BOOLEAN
1142 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
1143 is_debug_procedure_on BOOLEAN
1144 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
1145 is_debug_statement_on BOOLEAN
1146 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
1147 -- akrangan added for debug feature end
1148 BEGIN
1149 IF (is_debug_procedure_on) THEN
1150 okl_debug_pub.log_debug (g_level_procedure,
1151 l_module_name,
1152 'Begin(+)'
1153 );
1154 END IF;
1155
1156 IF (is_debug_statement_on) THEN
1157 okl_debug_pub.log_debug (g_level_statement,
1158 l_module_name,
1159 'p_chr_id =' || p_chr_id
1160 );
1161 END IF;
1162
1163 x_control_flag := 'SUCCESS';
1164
1165 -- Get K Details
1166 FOR l_get_k_dtls_rec IN l_get_k_dtls_csr (p_chr_id)
1167 LOOP
1168 x_start_date := l_get_k_dtls_rec.start_date;
1169 x_end_date := l_get_k_dtls_rec.end_date;
1170 x_status := l_get_k_dtls_rec.sts_code;
1171
1172 -- If Non Processed/Non Canceled TRN exists then Error
1173 FOR l_get_k_trn_rec IN l_get_k_trn_csr (p_chr_id)
1174 LOOP
1175 x_control_flag := 'FAIL';
1176 END LOOP;
1177 END LOOP;
1178
1179 IF (is_debug_procedure_on) THEN
1180 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
1181 END IF;
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 IF (is_debug_exception_on) THEN
1185 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1186 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1187 END IF;
1188 -- Set the oracle error message
1189 okl_api.set_message (p_app_name => okc_api.g_app_name,
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 END check_contract;
1197
1198 -- Start of comments
1199 --
1200 -- Procedure Name : check_if_quotes_existing
1201 -- Description : procedure to check if accepted quotes exist for contract
1202 -- being terminated
1203 -- Business Rules :
1204 -- Parameters :
1205 -- Version : 1.0
1206 -- End of comments
1207 PROCEDURE check_if_quotes_existing (
1208 p_term_rec IN term_rec_type,
1209 x_return_status OUT NOCOPY VARCHAR2,
1210 x_quotes_found OUT NOCOPY VARCHAR2
1211 ) IS
1212 -- Check if Termination or Restructure Quotes Exists
1213 CURSOR k_quotes_csr (p_khr_id IN NUMBER) IS
1214 SELECT ID
1215 FROM okl_trx_quotes_v
1216 WHERE khr_id = p_khr_id
1217 AND qst_code = 'ACCEPTED'
1218 AND (qtp_code LIKE 'TER%' OR qtp_code LIKE 'RES%');
1219
1220 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
1221 l_id NUMBER;
1222 l_quotes_found VARCHAR2 (1) := 'N';
1223 -- akrangan added for debug feature start
1224 l_module_name VARCHAR2 (500)
1225 := g_module_name || 'check_if_quotes_existing';
1226 is_debug_exception_on BOOLEAN
1227 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
1228 is_debug_procedure_on BOOLEAN
1229 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
1230 is_debug_statement_on BOOLEAN
1231 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
1232 -- akrangan added for debug feature end
1233 BEGIN
1234 IF (is_debug_procedure_on) THEN
1235 okl_debug_pub.log_debug (g_level_procedure,
1236 l_module_name,
1237 'Begin(+)'
1238 );
1239 END IF;
1240
1241 IF (is_debug_statement_on) THEN
1242 okl_debug_pub.log_debug (g_level_statement,
1243 l_module_name,
1244 'p_term_rec.p_contract_id ='
1245 || p_term_rec.p_contract_id
1246 );
1247 END IF;
1248
1249 -- Check if Termination quotes or Restructure quotes exist
1250 OPEN k_quotes_csr (p_term_rec.p_contract_id);
1251
1252 FETCH k_quotes_csr
1253 INTO l_id;
1254
1255 IF k_quotes_csr%FOUND THEN
1256 l_quotes_found := 'Y';
1257 END IF;
1258
1259 CLOSE k_quotes_csr;
1260
1261 x_return_status := l_return_status;
1262 x_quotes_found := l_quotes_found;
1263
1264 IF (is_debug_procedure_on) THEN
1265 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
1266 END IF;
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 IF (is_debug_exception_on) THEN
1270 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1271 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1272 END IF;
1273 IF k_quotes_csr%ISOPEN THEN
1274 CLOSE k_quotes_csr;
1275 END IF;
1276
1277 okl_api.set_message (p_app_name => g_app_name_1,
1278 p_msg_name => g_unexpected_error,
1279 p_token1 => g_sqlcode_token,
1280 p_token1_value => SQLCODE,
1281 p_token2 => g_sqlerrm_token,
1282 p_token2_value => SQLERRM
1283 );
1284 x_return_status := okl_api.g_ret_sts_unexp_error;
1285 END check_if_quotes_existing;
1286
1287 -- Start of comments
1288 --
1289 -- Procedure Name : get_trn_rec
1290 -- Description : procedure to get the transaction record for the contract
1291 -- Business Rules :
1292 -- Parameters :
1293 -- Version : 1.0
1294 -- History : RMUNJULU -- 26-NOV-02: Bug # 2484327 : Changed cursor to
1295 -- get asset level termination transactions
1296 -- RMUNJULU 17-DEC-02 Bug # 2484327: Added tmt_split_asset_yn
1297 -- to cursor
1298 -- RMUNJULU 02-OCT-03 2757312 Added tmt_generic_flag1_yn,
1299 -- tmt_generic_flag2_yn, tmt_generic_flag3_yn to select
1300 -- End of comments
1301 PROCEDURE get_trn_rec (
1302 p_contract_id IN NUMBER,
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 x_trn_exists OUT NOCOPY VARCHAR2,
1305 x_tcnv_rec OUT NOCOPY tcnv_rec_type
1306 ) IS
1307 -- Cursor to get the termination transaction details for the contract
1308
1309 -- RMUNJULU -- Bug # 2484327 : Added ALT to get asset level termination trns
1310 -- And tsu_code since a contract can have multiple transactions
1311 -- RMUNJULU 17-DEC-02 Bug # 2484327 Added tmt_split_asset_yn to cursor select
1312 CURSOR trn_rec_csr (p_khr_id IN NUMBER) IS
1313 SELECT ID,
1314 tcn_type,
1315 khr_id,
1316 try_id,
1317 tmt_status_code, --akrangan changes for sla tmt_status_code cr
1318 date_transaction_occurred,
1319 tmt_evergreen_yn,
1320 tmt_close_balances_yn,
1321 tmt_accounting_entries_yn,
1322 tmt_cancel_insurance_yn,
1323 tmt_asset_disposition_yn,
1324 tmt_amortization_yn,
1325 tmt_asset_return_yn,
1326 tmt_contract_updated_yn,
1327 tmt_recycle_yn,
1328 tmt_validated_yn,
1329 tmt_streams_updated_yn,
1330 tmt_split_asset_yn,
1331 tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
1332 tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
1333 tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
1334 qte_id
1335 FROM okl_trx_contracts
1336 WHERE khr_id = p_khr_id
1337 AND tcn_type IN ('TMT', 'ALT', 'EVG')
1338 --rkuttiya added for 12.1.1. Multi GAAP
1339 AND representation_type = 'PRIMARY'
1340 --
1341 -- akrangan bug 5354501 fix ADDED 'EVG'
1342 AND tmt_status_code NOT IN ('PROCESSED', 'CANCELED');
1343
1344 --akrangan changes for sla tmt_status_Code cr
1345 lp_tcnv_rec tcnv_rec_type;
1346 l_trn_exists VARCHAR2 (1) := 'N';
1347 -- akrangan added for debug feature start
1348 l_module_name VARCHAR2 (500) := g_module_name || 'get_trn_rec';
1349 is_debug_exception_on BOOLEAN
1350 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
1351 is_debug_procedure_on BOOLEAN
1352 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
1353 is_debug_statement_on BOOLEAN
1354 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
1355 -- akrangan added for debug feature end
1356 BEGIN
1357 IF (is_debug_procedure_on) THEN
1358 okl_debug_pub.log_debug (g_level_procedure,
1359 l_module_name,
1360 'Begin(+)'
1361 );
1362 END IF;
1363
1364 IF (is_debug_statement_on) THEN
1365 okl_debug_pub.log_debug (g_level_statement,
1366 l_module_name,
1367 'p_contract_id =' || p_contract_id
1368 );
1369 END IF;
1370
1371 x_return_status := okl_api.g_ret_sts_success;
1372
1373 -- set the trn_rec
1374 OPEN trn_rec_csr (p_contract_id);
1375
1376 FETCH trn_rec_csr
1377 INTO lp_tcnv_rec.ID,
1378 lp_tcnv_rec.tcn_type,
1379 lp_tcnv_rec.khr_id,
1380 lp_tcnv_rec.try_id,
1381 lp_tcnv_rec.tmt_status_code,
1382 --akrangan changes for sla tmt_status_code cr
1383 lp_tcnv_rec.date_transaction_occurred,
1384 lp_tcnv_rec.tmt_evergreen_yn,
1385 lp_tcnv_rec.tmt_close_balances_yn,
1386 lp_tcnv_rec.tmt_accounting_entries_yn,
1387 lp_tcnv_rec.tmt_cancel_insurance_yn,
1388 lp_tcnv_rec.tmt_asset_disposition_yn,
1389 lp_tcnv_rec.tmt_amortization_yn,
1390 lp_tcnv_rec.tmt_asset_return_yn,
1391 lp_tcnv_rec.tmt_contract_updated_yn,
1392 lp_tcnv_rec.tmt_recycle_yn,
1393 lp_tcnv_rec.tmt_validated_yn,
1394 lp_tcnv_rec.tmt_streams_updated_yn,
1395 lp_tcnv_rec.tmt_split_asset_yn,
1396 --RMUNJULU 17-DEC-02 Bug # 2484327 Added
1397 lp_tcnv_rec.tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
1398 lp_tcnv_rec.tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
1399 lp_tcnv_rec.tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
1400 lp_tcnv_rec.qte_id;
1401
1402 IF trn_rec_csr%FOUND THEN
1403 l_trn_exists := 'Y';
1404 END IF;
1405
1406 CLOSE trn_rec_csr;
1407
1408 x_tcnv_rec := lp_tcnv_rec;
1409 x_trn_exists := l_trn_exists;
1410
1411 IF (is_debug_procedure_on) THEN
1412 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
1413 END IF;
1414 EXCEPTION
1415 WHEN OTHERS THEN
1416 IF (is_debug_exception_on) THEN
1417 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1418 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1419 END IF;
1420 IF trn_rec_csr%ISOPEN THEN
1421 CLOSE trn_rec_csr;
1422 END IF;
1423
1424 x_return_status := okl_api.g_ret_sts_error;
1425 x_tcnv_rec := lp_tcnv_rec;
1426 x_trn_exists := l_trn_exists;
1427 END get_trn_rec;
1428
1429 -- Start of comments
1430 --
1431 -- Procedure Name : get_trn_rec
1432 -- Description : procedure to get the transaction record for the transaction id
1433 -- Business Rules :
1434 -- Parameters :
1435 -- Version : 1.0
1436 -- History : RMUNJULU 17-NOV-02: Bug # 2484327 : Created
1437 -- RMUNJULU 20-DEC-02 2484327 Changed cursor to get trn_id
1438 -- RMUNJULU 02-OCT-03 2757312 Added tmt_generic_flag1_yn,
1439 -- tmt_generic_flag2_yn, tmt_generic_flag3_yn to select
1440 -- End of comments
1441 PROCEDURE get_trn_rec (
1442 p_trn_id IN NUMBER,
1443 x_return_status OUT NOCOPY VARCHAR2,
1444 x_trn_exists OUT NOCOPY VARCHAR2,
1445 x_tcnv_rec OUT NOCOPY tcnv_rec_type
1446 ) IS
1447 -- Cursor to get the termination transaction details for the transaction id
1448 -- RMUNJULU 20-DEC-02 2484327 Changed cursor to get trn rec for trn_id not khr_id
1449 CURSOR trn_rec_csr (p_trn_id IN NUMBER) IS
1450 SELECT trx.ID,
1451 trx.tcn_type,
1452 trx.khr_id,
1453 trx.try_id,
1454 trx.tmt_status_code,
1455 trx.date_transaction_occurred,
1456 trx.tmt_evergreen_yn,
1457 trx.tmt_close_balances_yn,
1458 trx.tmt_accounting_entries_yn,
1459 trx.tmt_cancel_insurance_yn,
1460 trx.tmt_asset_disposition_yn,
1461 trx.tmt_amortization_yn,
1462 trx.tmt_asset_return_yn,
1463 trx.tmt_contract_updated_yn,
1464 trx.tmt_recycle_yn,
1465 trx.tmt_validated_yn,
1466 trx.tmt_streams_updated_yn,
1467 trx.tmt_split_asset_yn,
1468 trx.tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
1469 trx.tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
1470 trx.tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
1471 trx.qte_id
1472 FROM okl_trx_contracts trx
1473 WHERE trx.ID = p_trn_id;
1474
1475 lp_tcnv_rec tcnv_rec_type;
1476 l_trn_exists VARCHAR2 (1) := 'N';
1477 -- akrangan added for debug feature start
1478 l_module_name VARCHAR2 (500) := g_module_name || 'get_trn_rec';
1479 is_debug_exception_on BOOLEAN
1480 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
1481 is_debug_procedure_on BOOLEAN
1482 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
1483 is_debug_statement_on BOOLEAN
1484 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
1485 -- akrangan added for debug feature end
1486 BEGIN
1487 x_return_status := okl_api.g_ret_sts_success;
1488
1489 IF (is_debug_procedure_on) THEN
1490 okl_debug_pub.log_debug (g_level_procedure,
1491 l_module_name,
1492 'Begin(+)'
1493 );
1494 END IF;
1495
1496 IF (is_debug_statement_on) THEN
1497 okl_debug_pub.log_debug (g_level_statement,
1498 l_module_name,
1499 'p_trn_id =' || p_trn_id
1500 );
1501 END IF;
1502
1503 -- set the trn_rec
1504 OPEN trn_rec_csr (p_trn_id);
1505
1506 FETCH trn_rec_csr
1507 INTO lp_tcnv_rec.ID,
1508 lp_tcnv_rec.tcn_type,
1509 lp_tcnv_rec.khr_id,
1510 lp_tcnv_rec.try_id,
1511 lp_tcnv_rec.tmt_status_code,
1512 --akrangan changes for sla tmt_status_code cr
1513 lp_tcnv_rec.date_transaction_occurred,
1514 lp_tcnv_rec.tmt_evergreen_yn,
1515 lp_tcnv_rec.tmt_close_balances_yn,
1516 lp_tcnv_rec.tmt_accounting_entries_yn,
1517 lp_tcnv_rec.tmt_cancel_insurance_yn,
1518 lp_tcnv_rec.tmt_asset_disposition_yn,
1519 lp_tcnv_rec.tmt_amortization_yn,
1520 lp_tcnv_rec.tmt_asset_return_yn,
1521 lp_tcnv_rec.tmt_contract_updated_yn,
1522 lp_tcnv_rec.tmt_recycle_yn,
1523 lp_tcnv_rec.tmt_validated_yn,
1524 lp_tcnv_rec.tmt_streams_updated_yn,
1525 lp_tcnv_rec.tmt_split_asset_yn,
1526 lp_tcnv_rec.tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
1527 lp_tcnv_rec.tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
1528 lp_tcnv_rec.tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
1529 lp_tcnv_rec.qte_id;
1530
1531 IF trn_rec_csr%FOUND THEN
1532 l_trn_exists := 'Y';
1533 END IF;
1534
1535 CLOSE trn_rec_csr;
1536
1537 x_tcnv_rec := lp_tcnv_rec;
1538 x_trn_exists := l_trn_exists;
1539
1540 IF (is_debug_procedure_on) THEN
1541 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
1542 END IF;
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 IF (is_debug_exception_on) THEN
1546 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1547 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1548 END IF;
1549 IF trn_rec_csr%ISOPEN THEN
1550 CLOSE trn_rec_csr;
1551 END IF;
1552
1553 x_return_status := okl_api.g_ret_sts_error;
1554 x_tcnv_rec := lp_tcnv_rec;
1555 x_trn_exists := l_trn_exists;
1556 END get_trn_rec;
1557
1558 -- Start of comments
1559 --
1560 -- Procedure Name : process_termination
1561 -- Description : procedure which calls lease_loan_termination api after checks
1562 -- Business Rules :
1563 -- Parameters :
1564 -- Version : 1.0
1565 -- History : RMUNJULU 17-NOV-02 Bug # 2484327 : Added parameter p_trn_id
1566 -- Changed logic to call the new get_trn_rec based on trn_id
1567 -- if trn_id is passed
1568 -- End of comments
1569 PROCEDURE process_termination (
1570 p_api_version IN NUMBER,
1571 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1572 x_return_status OUT NOCOPY VARCHAR2,
1573 x_msg_count OUT NOCOPY NUMBER,
1574 x_msg_data OUT NOCOPY VARCHAR2,
1575 p_term_rec IN term_rec_type,
1576 p_trn_id IN NUMBER DEFAULT NULL,
1577 --RMUNJULU 17-NOV-02: Bug # 2484327 Added
1578 x_tcnv_rec OUT NOCOPY tcnv_rec_type,
1579 x_term_rec OUT NOCOPY term_rec_type
1580 ) IS
1581 lp_term_rec term_rec_type := p_term_rec;
1582 lx_term_rec term_rec_type;
1583 lp_tcnv_rec tcnv_rec_type;
1584 lx_tcnv_rec tcnv_rec_type;
1585 l_quotes_found VARCHAR2 (1) := 'N';
1586 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
1587 l_trn_exists VARCHAR2 (1);
1588 lx_error_rec okl_api.error_rec_type;
1589 l_msg_idx INTEGER := fnd_msg_pub.g_first;
1590 l_quote_type VARCHAR2 (200);
1591 l_quote_reason VARCHAR2 (200);
1592 -- akrangan added for debug feature start
1593 l_module_name VARCHAR2 (500)
1594 := g_module_name || 'process_termination';
1595 is_debug_exception_on BOOLEAN
1596 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
1597 is_debug_procedure_on BOOLEAN
1598 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
1599 is_debug_statement_on BOOLEAN
1600 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
1601 -- akrangan added for debug feature end
1602 BEGIN
1603 okl_api.init_msg_list (p_init_msg_list);
1604
1605 IF (is_debug_procedure_on) THEN
1606 okl_debug_pub.log_debug (g_level_procedure,
1607 l_module_name,
1608 'Begin(+)'
1609 );
1610 END IF;
1611
1612 IF (is_debug_statement_on) THEN
1613 okl_debug_pub.log_debug (g_level_statement,
1614 l_module_name,
1615 'In param, p_term_rec.p_contract_id: '
1616 || p_term_rec.p_contract_id
1617 );
1618 okl_debug_pub.log_debug
1619 (g_level_statement,
1620 l_module_name,
1621 'In param, p_term_rec.p_contract_number: '
1622 || p_term_rec.p_contract_number
1623 );
1624 okl_debug_pub.log_debug
1625 (g_level_statement,
1626 l_module_name,
1627 'In param, p_term_rec.p_contract_modifier: '
1628 || p_term_rec.p_contract_modifier
1629 );
1630 okl_debug_pub.log_debug (g_level_statement,
1631 l_module_name,
1632 'In param, p_term_rec.p_orig_end_date: '
1633 || p_term_rec.p_orig_end_date
1634 );
1635 okl_debug_pub.log_debug
1636 (g_level_statement,
1637 l_module_name,
1638 'In param, p_term_rec.p_contract_version: '
1639 || p_term_rec.p_contract_version
1640 );
1641 okl_debug_pub.log_debug
1642 (g_level_statement,
1643 l_module_name,
1644 'In param, p_term_rec.p_termination_date: '
1645 || p_term_rec.p_termination_date
1646 );
1647 okl_debug_pub.log_debug
1648 (g_level_statement,
1649 l_module_name,
1650 'In param, p_term_rec.p_termination_reason: '
1651 || p_term_rec.p_termination_reason
1652 );
1653 okl_debug_pub.log_debug (g_level_statement,
1654 l_module_name,
1655 'In param, p_term_rec.p_quote_id: '
1656 || p_term_rec.p_quote_id
1657 );
1658 okl_debug_pub.log_debug (g_level_statement,
1659 l_module_name,
1660 'In param, p_term_rec.p_quote_type: '
1661 || p_term_rec.p_quote_type
1662 );
1663 okl_debug_pub.log_debug (g_level_statement,
1664 l_module_name,
1665 'In param, p_term_rec.p_quote_reason: '
1666 || p_term_rec.p_quote_reason
1667 );
1668 okl_debug_pub.log_debug
1669 (g_level_statement,
1670 l_module_name,
1671 'In param, p_term_rec.p_early_termination_yn: '
1672 || p_term_rec.p_early_termination_yn
1673 );
1674 okl_debug_pub.log_debug (g_level_statement,
1675 l_module_name,
1676 'In param, p_term_rec.p_control_flag: '
1677 || p_term_rec.p_control_flag
1678 );
1679 okl_debug_pub.log_debug (g_level_statement,
1680 l_module_name,
1681 'In param, p_term_rec.p_recycle_flag: '
1682 || p_term_rec.p_recycle_flag
1683 );
1684 END IF;
1685
1686 -- RMUNJULU 17-NOV-02 Bug # 2484327 Added the if condition
1687 -- If the p_trn_id is passed then from recycle
1688 IF p_trn_id IS NOT NULL AND p_trn_id <> okl_api.g_miss_num THEN
1689 IF (is_debug_statement_on) THEN
1690 okl_debug_pub.log_debug
1691 (g_level_statement,
1692 l_module_name,
1693 'Before get_trn_rec In param, p_trn_id: '
1694 || p_trn_id
1695 );
1696 END IF;
1697
1698 -- Get transaction rec
1699 get_trn_rec (p_trn_id => p_trn_id,
1700 x_return_status => l_return_status,
1701 x_trn_exists => l_trn_exists,
1702 x_tcnv_rec => lp_tcnv_rec
1703 );
1704
1705 IF (is_debug_statement_on) THEN
1706 okl_debug_pub.log_debug
1707 (g_level_statement,
1708 l_module_name,
1709 'After get_trn_rec In param, l_return_status: '
1710 || l_return_status
1711 );
1712 END IF;
1713 ELSE
1714 IF (is_debug_statement_on) THEN
1715 okl_debug_pub.log_debug
1716 (g_level_statement,
1717 l_module_name,
1718 'Before get_trn_rec In param, p_trn_id: '
1719 || p_trn_id
1720 );
1721 END IF;
1722
1723 -- Get transaction if exists
1724 get_trn_rec (p_contract_id => lp_term_rec.p_contract_id,
1725 x_return_status => l_return_status,
1726 x_trn_exists => l_trn_exists,
1727 x_tcnv_rec => lp_tcnv_rec
1728 );
1729
1730 IF (is_debug_statement_on) THEN
1731 okl_debug_pub.log_debug
1732 (g_level_statement,
1733 l_module_name,
1734 'After get_trn_rec l_return_status: '
1735 || l_return_status
1736 );
1737 END IF;
1738 END IF;
1739
1740 -- If error then abort this contract
1741 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1742 -- Error retrieving transactions for the contract CONTRACT_NUMBER.
1743 okl_api.set_message (p_app_name => 'OKL',
1744 p_msg_name => 'OKL_AM_ERR_GETTING_TRN',
1745 p_token1 => 'CONTRACT_NUMBER',
1746 p_token1_value => p_term_rec.p_contract_number
1747 );
1748 RAISE g_exception_halt;
1749 END IF;
1750
1751 -- If trn exists then set the out tcnv_rec
1752 -- (have to do this or else tcnv_rec set wrong)
1753 IF (l_trn_exists = 'Y') THEN
1754 lx_tcnv_rec := lp_tcnv_rec;
1755 -- Also set the qte_id of term_Rec
1756 lp_term_rec.p_quote_id := lx_tcnv_rec.qte_id;
1757 ELSE
1758 lx_tcnv_rec.ID := okl_api.g_miss_num;
1759 END IF;
1760
1761 IF (is_debug_statement_on) THEN
1762 okl_debug_pub.log_debug (g_level_statement,
1763 l_module_name,
1764 'After get_trn_rec l_return_status: '
1765 || l_return_status
1766 );
1767 END IF;
1768
1769 IF (is_debug_statement_on) THEN
1770 okl_debug_pub.log_debug
1771 (g_level_statement,
1772 l_module_name,
1773 'before OKL_AM_LEASE_LOAN_TRMNT_PUB.lease_loan_termination '
1774 || l_return_status
1775 );
1776 END IF;
1777
1778 -- Call the lease loan terminate api
1779 okl_am_lease_loan_trmnt_pub.lease_loan_termination
1780 (p_api_version => p_api_version,
1781 p_init_msg_list => okl_api.g_false,
1782 x_return_status => l_return_status,
1783 x_msg_count => x_msg_count,
1784 x_msg_data => x_msg_data,
1785 p_term_rec => lp_term_rec,
1786 p_tcnv_rec => lx_tcnv_rec
1787 );
1788
1789 IF (is_debug_statement_on) THEN
1790 okl_debug_pub.log_debug
1791 (g_level_statement,
1792 l_module_name,
1793 'After OKL_AM_LEASE_LOAN_TRMNT_PUB.lease_loan_termination l_return_status: '
1794 || l_return_status
1795 );
1796 END IF;
1797
1798 /* RMUNJULU 2730738
1799 -- Add couple of blank lines
1800 fnd_file.put_line(fnd_file.log, '');
1801 fnd_file.put_line(fnd_file.output, '');
1802 fnd_file.put_line(fnd_file.log, '');
1803 fnd_file.put_line(fnd_file.output, '');
1804
1805 -- Get the messages in the log
1806 LOOP
1807
1808 fnd_msg_pub.get(
1809 p_msg_index => l_msg_idx,
1810 p_encoded => FND_API.G_FALSE,
1811 p_data => lx_error_rec.msg_data,
1812 p_msg_index_out => lx_error_rec.msg_count);
1813
1814 IF (lx_error_rec.msg_count IS NOT NULL) THEN
1815
1816 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
1817 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
1818
1819 END IF;
1820
1821 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
1822 OR (lx_error_rec.msg_count IS NULL));
1823
1824 l_msg_idx := FND_MSG_PUB.G_NEXT;
1825 END LOOP;
1826 */
1827
1828 -- If error then abort this contract
1829 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1830 RAISE g_exception_halt;
1831 END IF;
1832
1833 x_return_status := l_return_status;
1834 x_term_rec := lp_term_rec;
1835 x_tcnv_rec := lx_tcnv_rec;
1836
1837 IF (is_debug_procedure_on) THEN
1838 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
1839 END IF;
1840 EXCEPTION
1841 WHEN g_exception_halt THEN
1842 IF (is_debug_exception_on) THEN
1843 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'G_EXCEPTION_HALT');
1844 END IF;
1845 x_return_status := l_return_status;
1846 x_term_rec := lp_term_rec;
1847 x_tcnv_rec := lx_tcnv_rec;
1848 WHEN OTHERS THEN
1849 IF (is_debug_exception_on) THEN
1850 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1851 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1852 END IF;
1853 x_return_status := l_return_status;
1854 x_term_rec := lp_term_rec;
1855 x_tcnv_rec := lx_tcnv_rec;
1856 END process_termination;
1857
1858 -- Start of comments
1859 --
1860 -- Procedure Name : batch_expire_lease_loan
1861 -- Description : procedure to terminate the contract in batch process
1862 -- checks if any open quotes for the contract and if not then calls
1863 -- lease_loan_terminate api to terminate the lease/loan
1864 -- Business Rules :
1865 -- Parameters :
1866 -- Version : 1.0
1867 -- History : RMUNJULU -- 26-NOV-02: Bug # 2484327 : Changed cursor
1868 -- ter_cnt_recy_csr to get asset level termination trns
1869 -- Changed cursor ter_cnt_csr asset level termination trns
1870 -- : RMUNJULU 17-NOV-02 Bug # 2484327 Added trn_id to cursor
1871 -- : RMUNJULU 20-DEC-02 2484327 Changed ter_cnt_csr cursor
1872 -- : RMUNJULU 23-DEC-02 2484327 Changed ter_cnt_csr cursor
1873 -- to get the correct data
1874 -- : RMUNJULU 25-FEB-03 2818866 Changed ter_cnt_recy_csr cursor
1875 -- : RMUNJULU 05-MAR-03 Performance Fix Replaced K_HDR_FULL
1876 -- : rmunjulu 01-Dec-04 4016497 Added code to do processing when single K
1877 -- End of comments
1878 PROCEDURE batch_expire_lease_loan (
1879 p_api_version IN NUMBER,
1880 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 x_msg_count OUT NOCOPY NUMBER,
1883 x_msg_data OUT NOCOPY VARCHAR2,
1884 p_contract_id IN NUMBER DEFAULT okl_api.g_miss_num,
1885 x_term_tbl OUT NOCOPY term_tbl_type
1886 ) IS
1887 -- Get the contract details for contract number passed
1888 -- RMUNJULU 05-MAR-03 Performance Fix Replaced K_HDR_FULL
1889 CURSOR single_k_csr (p_khr_id IN NUMBER) IS
1890 SELECT k.contract_number
1891 FROM okc_k_headers_v k
1892 WHERE k.ID = p_khr_id;
1893
1894 -- rmunjulu Added for bug 4385077 to do org strip
1895
1896 --Pick Contracts which have reached their end date and booked
1897 --and only Lease or Loan and no termination transaction with status
1898 --other than cancelled exists and no accepted quotes exists
1899 --and non templates
1900
1901 -- RMUNJULU -- Bug # 2484327 : Changed to check tsu_code not in PROCESSED or
1902 -- CANCELED. Also the Accepted quote is checked using the QST_CODE instead of
1903 -- accepted_yn flag
1904
1905 -- RMUNJULU 20-DEC-02 2484327
1906 -- Changed the cursor to get the contracts which have reached the end date
1907 -- and which do not have unprocessed transactions and also which do not have
1908 -- accepted quotes with no transactions
1909 -- RMUNJULU 23-DEC-02 2484327
1910 -- Added NVLs to get the correct data
1911 -- RMUNJULU 05-MAR-03 Performance Fix Replaced K_HDR_FULL
1912 CURSOR ter_cnt_csr (p_sysdate IN DATE) IS
1913 SELECT khr.ID,
1914 khr.contract_number
1915 FROM okc_k_headers_v khr
1916 WHERE TRUNC (khr.end_date) < TRUNC (p_sysdate)
1917 AND NVL (khr.sts_code, '?') IN ('BOOKED')
1918 AND khr.scs_code IN ('LEASE', 'LOAN')
1919 AND khr.ID NOT IN (
1920 -- Contracts which have unprocessed transactions
1921 SELECT NVL (tcn.khr_id, -9999) khr_id
1922 FROM okl_trx_contracts tcn
1923 WHERE NVL (tcn.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
1924 -- akrangan bug 5354501 fix added 'EVG'
1925 AND tcn.tmt_status_code NOT IN
1926 ('CANCELED', 'PROCESSED')
1927 --rkuttiya added for 12.1.1 Multi GAAP
1928 AND tcn.representation_type = 'PRIMARY'
1929 --
1930 --akrangan changed for sla tmt_status_Code cr
1931 AND tcn.khr_id = khr.ID) -- rmunjulu PERF
1932 AND khr.ID NOT IN (
1933 -- Contracts which have accepted quotes with no transactions
1934 SELECT NVL (qte.khr_id, -9999) khr_id
1935 FROM okl_trx_quotes_v qte
1936 WHERE NVL (qte.accepted_yn, 'N') = 'Y'
1937 AND NVL (qte.consolidated_yn, 'N') = 'N'
1938 AND qte.khr_id = khr.ID -- rmunjulu PERF
1939 AND qte.ID NOT IN (
1940 SELECT NVL (tcn.qte_id, -9999) qte_id
1941 FROM okl_trx_contracts tcn
1942 WHERE NVL (tcn.tcn_type, '?') IN
1943 ('TMT', 'ALT', 'EVG')
1944 --rkuttiya added for 12.1.1 Multi GAAP
1945 AND tcn.representation_type = 'PRIMARY'
1946 --
1947 -- akrangan bug 5354501 fix added 'EVG'
1948 AND tcn.qte_id = qte.ID)); -- rmunjulu PERF
1949
1950 -- rmunjulu Added for bug 4385077 to do org strip
1951
1952 -- Pick Contracts set for recycle and booked and only Lease or Loan
1953
1954 -- RMUNJULU -- Bug # 2484327 : Added ALT to get asset level termination trns
1955 -- And check tsu_code not in CANCELED along with PROCESSED
1956 -- RMUNJULU 17-NOV-02 Bug # 2484327 Added trn_id to cursor select
1957 -- RMUNJULU 25-FEB-03 2818866 Changed cursor to NON BOOKED contracts
1958 -- since a quote can be created and TRN recycled for other types of contracts
1959 -- like EVERGREEN etc
1960 -- RMUNJULU 05-MAR-03 Performance Fix Replaced K_HDR_FULL
1961 CURSOR ter_cnt_recy_csr (p_sysdate IN DATE) IS
1962 SELECT k.ID,
1963 k.contract_number,
1964 t.ID trn_id -- RMUNJULU 17-NOV-02 Bug # 2484327 Added
1965 FROM okc_k_headers_v k, okl_trx_contracts t
1966 WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
1967 AND NVL (t.tmt_status_code, '?') NOT IN ('PROCESSED', 'CANCELED')
1968 --rkuttiya added for 12.1.1 Multi GAAP
1969 AND t.representation_type = 'PRIMARY'
1970 --
1971 --akrangan changed for sla tmt_status_Code cr
1972 AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
1973 -- akrangan bug 5354501 fix added 'EVG'
1974 --AND NVL(K.sts_code,'?') IN('BOOKED')
1975 AND k.scs_code IN ('LEASE', 'LOAN')
1976 AND k.ID = t.khr_id;
1977
1978 -- rmunjulu Added for bug 4385077 to do org strip
1979
1980 -- rmunjulu 4016497
1981 -- Cursor to check if contract has expired and no unprocessed trn
1982 -- and no accepted quotes.
1983 CURSOR exp_chr_csr (p_khr_id IN NUMBER, p_sysdate IN DATE) IS
1984 SELECT khr.ID,
1985 khr.contract_number
1986 FROM okc_k_headers_v khr
1987 WHERE khr.ID = p_khr_id
1988 AND TRUNC (khr.end_date) < TRUNC (p_sysdate)
1989 AND NVL (khr.sts_code, '?') IN ('BOOKED')
1990 AND khr.scs_code IN ('LEASE', 'LOAN')
1991 AND khr.ID NOT IN (
1992 -- Contracts which have unprocessed transactions
1993 SELECT NVL (tcn.khr_id, -9999) khr_id
1994 FROM okl_trx_contracts tcn
1995 WHERE NVL (tcn.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
1996 -- akrangan bug 5354501 fix added 'EVG'
1997 AND tcn.tmt_status_code NOT IN
1998 ('CANCELED', 'PROCESSED')
1999 --akrangan changed for sla tmt_status_Code cr
2000 --rkuttiya added for 12.1.1 Multi GAAP
2001 AND tcn.representation_type = 'PRIMARY'
2002 AND tcn.khr_id = khr.ID) -- rmunjulu PERF
2003 AND khr.ID NOT IN (
2004 -- Contracts which have accepted quotes with no transactions
2005 SELECT NVL (qte.khr_id, -9999) khr_id
2006 FROM okl_trx_quotes_v qte
2007 WHERE NVL (qte.accepted_yn, 'N') = 'Y'
2008 AND NVL (qte.consolidated_yn, 'N') = 'N'
2009 AND qte.khr_id = khr.ID -- rmunjulu PERF
2010 AND qte.ID NOT IN (
2011 SELECT NVL (tcn.qte_id, -9999) qte_id
2012 FROM okl_trx_contracts tcn
2013 WHERE NVL (tcn.tcn_type, '?') IN
2014 ('TMT', 'ALT', 'EVG')
2015 -- akrangan bug 5354501 fix added 'EVG'
2016 --rkuttiya added for 12.1.1. Multi GAAP
2017 AND representation_type = 'PRIMARY'
2018 --
2019 AND tcn.qte_id = qte.ID)); -- rmunjulu PERF
2020
2021 -- rmunjulu Added for bug 4385077 to do org strip
2022
2023 -- rmunjulu 4016497
2024 -- Cursor to get the recycled termination transaction of contract if exists
2025 CURSOR recy_chr_csr (p_khr_id IN NUMBER, p_sysdate IN DATE) IS
2026 SELECT k.ID,
2027 k.contract_number,
2028 t.ID trn_id
2029 FROM okc_k_headers_v k, okl_trx_contracts t
2030 WHERE k.ID = p_khr_id
2031 AND NVL (t.tmt_recycle_yn, '?') = 'Y'
2032 AND NVL (t.tmt_status_code, '?') NOT IN ('PROCESSED', 'CANCELED')
2033 --akrangan changed for sla tmt_status_Code cr
2034 AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
2035 -- akrangan bug 5354501 fix added 'EVG'
2036 AND k.scs_code IN ('LEASE', 'LOAN')
2037 --rkuttiya added for 12.1.1 Multi GAAP
2038 AND t.representation_type = 'PRIMARY'
2039 --
2040 AND k.ID = t.khr_id;
2041
2042 -- rmunjulu Added for bug 4385077 to do org strip
2043
2044 -- rmunjulu LOANS_ENHANCEMENTS
2045 CURSOR k_details_csr (p_khr_id IN NUMBER) IS
2046 SELECT deal_type
2047 FROM okl_k_headers
2048 WHERE ID = p_khr_id;
2049
2050 lp_term_rec term_rec_type;
2051 lx_term_rec term_rec_type;
2052 lx_term_tbl term_tbl_type;
2053 lp_tcnv_rec tcnv_rec_type;
2054 lx_tcnv_rec tcnv_rec_type;
2055 db_sysdate DATE;
2056 i NUMBER := 1;
2057 j NUMBER := 1;
2058 l_chr_id NUMBER;
2059 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
2060 l_api_name VARCHAR2 (200) := 'batch_expire_lease_loan';
2061 l_overall_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
2062 -- RMUNJULU 2730738
2063 l_start_date DATE;
2064 l_end_date DATE;
2065 l_status VARCHAR2 (200);
2066 l_control_flag VARCHAR2 (10);
2067 -- rmunjulu 4016497
2068 exp_chr_rec exp_chr_csr%ROWTYPE;
2069 recy_chr_rec recy_chr_csr%ROWTYPE;
2070 l_exp_chr_yn VARCHAR2 (3);
2071 l_recy_chr_yn VARCHAR2 (3);
2072 l_message VARCHAR2 (30000);
2073 -- rmunjulu LOANS_ENHANCEMENTS
2074 l_prin_bal NUMBER := 0;
2075 l_deal_type VARCHAR2 (300);
2076 l_prin_stream_flag NUMBER; --Added by bkatraga for bug 11924386
2077 -- akrangan added for debug feature start
2078 l_module_name VARCHAR2 (500) := g_module_name || 'fnd_output';
2079 is_debug_exception_on BOOLEAN
2080 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
2081 is_debug_procedure_on BOOLEAN
2082 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
2083 is_debug_statement_on BOOLEAN
2084 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
2085 -- akrangan added for debug feature end
2086 BEGIN
2087 IF (is_debug_procedure_on) THEN
2088 okl_debug_pub.log_debug (g_level_procedure,
2089 l_module_name,
2090 'Begin(+)'
2091 );
2092 END IF;
2093
2094 IF (is_debug_statement_on) THEN
2095 okl_debug_pub.log_debug (g_level_statement,
2096 l_module_name,
2097 'p_contract_id =' || p_contract_id
2098 );
2099 END IF;
2100
2101 x_return_status := okl_api.g_ret_sts_success;
2102
2103 SELECT SYSDATE
2104 INTO db_sysdate
2105 FROM DUAL;
2106
2107 -- Check if value passed for contract number
2108 IF p_contract_id IS NOT NULL AND p_contract_id <> okl_api.g_miss_num THEN
2109 FOR single_k_rec IN single_k_csr (p_contract_id)
2110 LOOP
2111 -- set the term_rec_type of terminate_contract
2112 lp_term_rec.p_contract_id := p_contract_id;
2113 lp_term_rec.p_contract_number := single_k_rec.contract_number;
2114 lp_term_rec.p_termination_date := db_sysdate;
2115 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
2116
2117 -- rmunjulu 4016497
2118 -- check if contract expired
2119 OPEN exp_chr_csr (p_contract_id, db_sysdate);
2120
2121 FETCH exp_chr_csr
2122 INTO exp_chr_rec;
2123
2124 IF exp_chr_csr%FOUND THEN
2125 l_exp_chr_yn := 'Y';
2126 END IF;
2127
2128 CLOSE exp_chr_csr;
2129
2130 -- rmunjulu 4016497
2131 -- check if contract recycled
2132 OPEN recy_chr_csr (p_contract_id, db_sysdate);
2133
2134 FETCH recy_chr_csr
2135 INTO recy_chr_rec;
2136
2137 IF recy_chr_csr%FOUND THEN
2138 l_recy_chr_yn := 'Y';
2139 END IF;
2140
2141 CLOSE recy_chr_csr;
2142
2143 -- rmunjulu 4016497
2144 IF NVL (l_exp_chr_yn, 'N') = 'Y' THEN -- Contract has expired
2145 -- rmunjulu LOANS_ENHANCEMENTS
2146 OPEN k_details_csr (lp_term_rec.p_contract_id);
2147
2148 FETCH k_details_csr
2149 INTO l_deal_type;
2150
2151 CLOSE k_details_csr;
2152
2153 --Added by bkatraga for bug 11924386
2154 IF l_deal_type = 'LOAN' THEN
2155 OKL_VARIABLE_INT_UTIL_PVT.check_principal_stream(
2156 p_khr_id => lp_term_rec.p_contract_id,
2157 x_return_status => l_return_status,
2158 x_prin_stream_flag => l_prin_stream_flag);
2159
2160 IF (is_debug_statement_on) THEN
2161 okl_debug_pub.log_debug
2162 (g_level_statement,
2163 l_module_name,
2164 'After OKL_VARIABLE_INT_UTIL_PVT.check_principal_stream '
2165 );
2166 okl_debug_pub.log_debug (g_level_statement,
2167 l_module_name,
2168 'l_prin_stream_flag: ' || l_prin_stream_flag
2169 );
2170 okl_debug_pub.log_debug (g_level_statement,
2171 l_module_name,
2172 'l_return_status: '
2173 || l_return_status
2174 );
2175 END IF;
2176 END IF;
2177 --end bkatraga
2178
2179 --Modified IF condition by bkatraga for bug 11924386
2180 -- rmunjulu LOANS_ENHANCEMENTS
2181 IF (l_deal_type = 'LOAN-REVOLVING' OR (l_deal_type = 'LOAN' AND l_prin_stream_flag = 0)) THEN
2182 IF (is_debug_statement_on) THEN
2183 okl_debug_pub.log_debug
2184 (g_level_statement,
2185 l_module_name,
2186 'Before OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
2187 );
2188 okl_debug_pub.log_debug (g_level_statement,
2189 l_module_name,
2190 'In param, p_contract_id: '
2191 || lp_term_rec.p_contract_id
2192 );
2193 END IF;
2194
2195 -- get principal balance of loan contract
2196 l_prin_bal :=
2197 okl_variable_int_util_pvt.get_principal_bal
2198 (x_return_status => l_return_status,
2199 p_khr_id => lp_term_rec.p_contract_id,
2200 p_kle_id => NULL,
2201 p_date => SYSDATE
2202 );
2203
2204 IF (is_debug_statement_on) THEN
2205 okl_debug_pub.log_debug
2206 (g_level_statement,
2207 l_module_name,
2208 'After OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
2209 );
2210 okl_debug_pub.log_debug (g_level_statement,
2211 l_module_name,
2212 'l_prin_bal: ' || l_prin_bal
2213 );
2214 okl_debug_pub.log_debug (g_level_statement,
2215 l_module_name,
2216 'l_return_status: '
2217 || l_return_status
2218 );
2219 END IF;
2220 END IF;
2221
2222 -- rmunjulu LOANS_ENHANCEMENTS
2223 IF NVL (l_prin_bal, 0) <= 0 THEN
2224 -- rmunjulu 5058848 check for prin bal <= 0
2225 -- set the out tbl
2226 lx_term_tbl (i).p_contract_id := lp_term_rec.p_contract_id;
2227 lx_term_tbl (i).p_contract_number :=
2228 lp_term_rec.p_contract_number;
2229
2230 IF (is_debug_statement_on) THEN
2231 okl_debug_pub.log_debug (g_level_statement,
2232 l_module_name,
2233 'Before process_termination'
2234 );
2235 okl_debug_pub.log_debug
2236 (g_level_statement,
2237 l_module_name,
2238 'In param, p_term_rec.p_contract_id: '
2239 || lp_term_rec.p_contract_id
2240 );
2241 okl_debug_pub.log_debug
2242 (g_level_statement,
2243 l_module_name,
2244 'In param, p_term_rec.p_contract_number: '
2245 || lp_term_rec.p_contract_number
2246 );
2247 END IF;
2248
2249 process_termination (p_api_version => p_api_version,
2250 p_init_msg_list => okl_api.g_true,
2251 x_return_status => l_return_status,
2252 x_msg_count => x_msg_count,
2253 x_msg_data => x_msg_data,
2254 p_term_rec => lp_term_rec,
2255 x_tcnv_rec => lx_tcnv_rec,
2256 x_term_rec => lx_term_rec
2257 );
2258
2259 IF (is_debug_statement_on) THEN
2260 okl_debug_pub.log_debug (g_level_statement,
2261 l_module_name,
2262 'After process_termination '
2263 );
2264 okl_debug_pub.log_debug (g_level_statement,
2265 l_module_name,
2266 'l_return_status: '
2267 || l_return_status
2268 );
2269 END IF;
2270
2271 IF (is_debug_statement_on) THEN
2272 okl_debug_pub.log_debug (g_level_statement,
2273 l_module_name,
2274 'Before check_contract'
2275 );
2276 okl_debug_pub.log_debug (g_level_statement,
2277 l_module_name,
2278 'In param, p_contract_id: '
2279 || lp_term_rec.p_contract_id
2280 );
2281 END IF;
2282
2283 -- RMUNJULU 2730738 For proper output file
2284 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2285 x_start_date => l_start_date,
2286 x_end_date => l_end_date,
2287 x_status => l_status,
2288 x_control_flag => l_control_flag
2289 );
2290
2291 IF (is_debug_statement_on) THEN
2292 okl_debug_pub.log_debug (g_level_statement,
2293 l_module_name,
2294 'After check_contract '
2295 );
2296 okl_debug_pub.log_debug (g_level_statement,
2297 l_module_name,
2298 'x_start_date: ' || l_start_date
2299 );
2300 okl_debug_pub.log_debug (g_level_statement,
2301 l_module_name,
2302 'x_end_date: ' || l_end_date
2303 );
2304 okl_debug_pub.log_debug (g_level_statement,
2305 l_module_name,
2306 'x_status: ' || l_status
2307 );
2308 okl_debug_pub.log_debug (g_level_statement,
2309 l_module_name,
2310 'x_control_flag: '
2311 || l_control_flag
2312 );
2313 okl_debug_pub.log_debug (g_level_statement,
2314 l_module_name,
2315 'l_return_status: '
2316 || l_return_status
2317 );
2318 END IF;
2319
2320 -- RMUNJULU 2730738 For proper output file
2321 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2322 p_chr_number => lp_term_rec.p_contract_number,
2323 p_start_date => l_start_date,
2324 p_end_date => l_end_date,
2325 p_status => l_status,
2326 p_exp_recy => 'EXP',
2327 p_control_flag => l_control_flag
2328 );
2329
2330 IF (is_debug_statement_on) THEN
2331 okl_debug_pub.log_debug (g_level_statement,
2332 l_module_name,
2333 'After fnd_output '
2334 );
2335 okl_debug_pub.log_debug (g_level_statement,
2336 l_module_name,
2337 'l_return_status: '
2338 || l_return_status
2339 );
2340 END IF;
2341
2342 -- RMUNJULU 2730738 For proper output file
2343 reset_asset_msg_tbl;
2344 -- set the out tbl termination date
2345 lx_term_tbl (i) := lx_term_rec;
2346
2347 -- update the overall status only if l_return_status is not success
2348 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
2349 l_overall_status := l_return_status;
2350 END IF;
2351
2352 -- rmunjulu 4016497
2353 create_report;
2354 ELSE
2355 IF (is_debug_statement_on) THEN
2356 okl_debug_pub.log_debug (g_level_statement,
2357 l_module_name,
2358 'Before check_contract'
2359 );
2360 okl_debug_pub.log_debug (g_level_statement,
2361 l_module_name,
2362 'In param, p_contract_id: '
2363 || lp_term_rec.p_contract_id
2364 );
2365 END IF;
2366
2367 -- RMUNJULU 2730738 For proper output file
2368 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2369 x_start_date => l_start_date,
2370 x_end_date => l_end_date,
2371 x_status => l_status,
2372 x_control_flag => l_control_flag
2373 );
2374
2375 IF (is_debug_statement_on) THEN
2376 okl_debug_pub.log_debug (g_level_statement,
2377 l_module_name,
2378 'After check_contract '
2379 );
2380 okl_debug_pub.log_debug (g_level_statement,
2381 l_module_name,
2382 'x_start_date: ' || l_start_date
2383 );
2384 okl_debug_pub.log_debug (g_level_statement,
2385 l_module_name,
2386 'x_end_date: ' || l_end_date
2387 );
2388 okl_debug_pub.log_debug (g_level_statement,
2389 l_module_name,
2390 'x_status: ' || l_status
2391 );
2392 okl_debug_pub.log_debug (g_level_statement,
2393 l_module_name,
2394 'x_control_flag: '
2395 || l_control_flag
2396 );
2397 okl_debug_pub.log_debug (g_level_statement,
2398 l_module_name,
2399 'l_return_status: '
2400 || l_return_status
2401 );
2402 END IF;
2403
2404 IF (is_debug_statement_on) THEN
2405 okl_debug_pub.log_debug (g_level_statement,
2406 l_module_name,
2407 'Before fnd_output '
2408 );
2409 okl_debug_pub.log_debug
2410 (g_level_statement,
2411 l_module_name,
2412 'In param, p_term_rec.p_contract_id: '
2413 || lp_term_rec.p_contract_id
2414 );
2415 okl_debug_pub.log_debug
2416 (g_level_statement,
2417 l_module_name,
2418 'In param, p_term_rec.p_contract_number: '
2419 || lp_term_rec.p_contract_number
2420 );
2421 END IF;
2422
2423 -- RMUNJULU 2730738 For proper output file
2424 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2425 p_chr_number => lp_term_rec.p_contract_number,
2426 p_start_date => l_start_date,
2427 p_end_date => l_end_date,
2428 p_status => l_status,
2429 p_exp_recy => 'EXP',
2430 p_control_flag => 'FAIL'
2431 );
2432
2433 IF (is_debug_statement_on) THEN
2434 okl_debug_pub.log_debug (g_level_statement,
2435 l_module_name,
2436 'After fnd_output '
2437 );
2438 okl_debug_pub.log_debug (g_level_statement,
2439 l_module_name,
2440 'l_return_status: '
2441 || l_return_status
2442 );
2443 END IF;
2444
2445 -- RMUNJULU 2730738 For proper output file
2446 reset_asset_msg_tbl;
2447 g_prin_bal_zero := 'Y';
2448 create_report;
2449 END IF;
2450 ELSIF NVL (l_recy_chr_yn, 'N') = 'Y' THEN
2451 -- Contract has been recycled
2452 IF (is_debug_statement_on) THEN
2453 okl_debug_pub.log_debug (g_level_statement,
2454 l_module_name,
2455 'Before process_termination'
2456 );
2457 okl_debug_pub.log_debug
2458 (g_level_statement,
2459 l_module_name,
2460 'In param, p_term_rec.p_contract_id: '
2461 || lp_term_rec.p_contract_id
2462 );
2463 okl_debug_pub.log_debug
2464 (g_level_statement,
2465 l_module_name,
2466 'In param, p_term_rec.p_contract_number: '
2467 || lp_term_rec.p_contract_number
2468 );
2469 END IF;
2470
2471 process_termination (p_api_version => p_api_version,
2472 p_init_msg_list => okl_api.g_true,
2473 x_return_status => l_return_status,
2474 x_msg_count => x_msg_count,
2475 x_msg_data => x_msg_data,
2476 p_term_rec => lp_term_rec,
2477 p_trn_id => recy_chr_rec.trn_id,
2478 x_tcnv_rec => lx_tcnv_rec,
2479 x_term_rec => lx_term_rec
2480 );
2481
2482 IF (is_debug_statement_on) THEN
2483 okl_debug_pub.log_debug (g_level_statement,
2484 l_module_name,
2485 'After process_termination '
2486 );
2487 okl_debug_pub.log_debug (g_level_statement,
2488 l_module_name,
2489 'l_return_status: '
2490 || l_return_status
2491 );
2492 END IF;
2493
2494 IF (is_debug_statement_on) THEN
2495 okl_debug_pub.log_debug (g_level_statement,
2496 l_module_name,
2497 'Before check_contract'
2498 );
2499 okl_debug_pub.log_debug (g_level_statement,
2500 l_module_name,
2501 'In param, p_contract_id: '
2502 || lp_term_rec.p_contract_id
2503 );
2504 END IF;
2505
2506 -- RMUNJULU 2730738 For proper output file
2507 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2508 x_start_date => l_start_date,
2509 x_end_date => l_end_date,
2510 x_status => l_status,
2511 x_control_flag => l_control_flag
2512 );
2513
2514 IF (is_debug_statement_on) THEN
2515 okl_debug_pub.log_debug (g_level_statement,
2516 l_module_name,
2517 'After check_contract '
2518 );
2519 okl_debug_pub.log_debug (g_level_statement,
2520 l_module_name,
2521 'x_start_date: ' || l_start_date
2522 );
2523 okl_debug_pub.log_debug (g_level_statement,
2524 l_module_name,
2525 'x_end_date: ' || l_end_date
2526 );
2527 okl_debug_pub.log_debug (g_level_statement,
2528 l_module_name,
2529 'x_status: ' || l_status
2530 );
2531 okl_debug_pub.log_debug (g_level_statement,
2532 l_module_name,
2533 'x_control_flag: '
2534 || l_control_flag
2535 );
2536 okl_debug_pub.log_debug (g_level_statement,
2537 l_module_name,
2538 'l_return_status: '
2539 || l_return_status
2540 );
2541 END IF;
2542
2543 IF (is_debug_statement_on) THEN
2544 okl_debug_pub.log_debug (g_level_statement,
2545 l_module_name,
2546 'Before fnd_output '
2547 );
2548 okl_debug_pub.log_debug
2549 (g_level_statement,
2550 l_module_name,
2551 'In param, p_term_rec.p_contract_id: '
2552 || lp_term_rec.p_contract_id
2553 );
2554 okl_debug_pub.log_debug
2555 (g_level_statement,
2556 l_module_name,
2557 'In param, p_term_rec.p_contract_number: '
2558 || lp_term_rec.p_contract_number
2559 );
2560 END IF;
2561
2562 -- RMUNJULU 2730738 For proper output file
2563 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2564 p_chr_number => lp_term_rec.p_contract_number,
2565 p_start_date => l_start_date,
2566 p_end_date => l_end_date,
2567 p_status => l_status,
2568 p_exp_recy => 'RECY',
2569 p_control_flag => l_control_flag
2570 );
2571
2572 IF (is_debug_statement_on) THEN
2573 okl_debug_pub.log_debug (g_level_statement,
2574 l_module_name,
2575 'After fnd_output '
2576 );
2577 okl_debug_pub.log_debug (g_level_statement,
2578 l_module_name,
2579 'l_return_status: '
2580 || l_return_status
2581 );
2582 END IF;
2583
2584 -- RMUNJULU 2730738 For proper output file
2585 reset_asset_msg_tbl;
2586 -- set the out tbl termination date
2587 lx_term_tbl (i) := lx_term_rec;
2588
2589 -- update the overall status only if l_return_status is not success
2590 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
2591 l_overall_status := l_return_status;
2592 END IF;
2593
2594 -- rmunjulu 4016497
2595 create_report;
2596 ELSE
2597 -- Either the contract is invalid or contract has not reached its end date
2598 -- or there is no recycled termination transaction for the contract.
2599
2600 -- Set the message
2601 fnd_message.set_name ('OKL', 'OKL_AM_BTCH_ERR');
2602 -- Get the message
2603 l_message := fnd_message.get;
2604
2605 IF (is_debug_statement_on) THEN
2606 okl_debug_pub.log_debug (g_level_statement,
2607 l_module_name,
2608 'Before check_contract'
2609 );
2610 okl_debug_pub.log_debug (g_level_statement,
2611 l_module_name,
2612 'In param, p_contract_id: '
2613 || lp_term_rec.p_contract_id
2614 );
2615 END IF;
2616
2617 -- RMUNJULU 2730738 For proper output file
2618 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2619 x_start_date => l_start_date,
2620 x_end_date => l_end_date,
2621 x_status => l_status,
2622 x_control_flag => l_control_flag
2623 );
2624
2625 IF (is_debug_statement_on) THEN
2626 okl_debug_pub.log_debug (g_level_statement,
2627 l_module_name,
2628 'After check_contract '
2629 );
2630 okl_debug_pub.log_debug (g_level_statement,
2631 l_module_name,
2632 'x_start_date: ' || l_start_date
2633 );
2634 okl_debug_pub.log_debug (g_level_statement,
2635 l_module_name,
2636 'x_end_date: ' || l_end_date
2637 );
2638 okl_debug_pub.log_debug (g_level_statement,
2639 l_module_name,
2640 'x_status: ' || l_status
2641 );
2642 okl_debug_pub.log_debug (g_level_statement,
2643 l_module_name,
2644 'x_control_flag: '
2645 || l_control_flag
2646 );
2647 okl_debug_pub.log_debug (g_level_statement,
2648 l_module_name,
2649 'l_return_status: '
2650 || l_return_status
2651 );
2652 END IF;
2653
2654 IF (is_debug_statement_on) THEN
2655 okl_debug_pub.log_debug (g_level_statement,
2656 l_module_name,
2657 'Before fnd_output '
2658 );
2659 okl_debug_pub.log_debug
2660 (g_level_statement,
2661 l_module_name,
2662 'In param, p_term_rec.p_contract_id: '
2663 || lp_term_rec.p_contract_id
2664 );
2665 okl_debug_pub.log_debug
2666 (g_level_statement,
2667 l_module_name,
2668 'In param, p_term_rec.p_contract_number: '
2669 || lp_term_rec.p_contract_number
2670 );
2671 END IF;
2672
2673 -- RMUNJULU 2730738 For proper output file
2674 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2675 p_chr_number => lp_term_rec.p_contract_number,
2676 p_start_date => l_start_date,
2677 p_end_date => l_end_date,
2678 p_status => l_status,
2679 p_exp_recy => 'RECY',
2680 p_control_flag => NULL
2681 ); -- Pass NULL so that it goes to ELSE of this API
2682
2683 IF (is_debug_statement_on) THEN
2684 okl_debug_pub.log_debug (g_level_statement,
2685 l_module_name,
2686 'After fnd_output '
2687 );
2688 okl_debug_pub.log_debug (g_level_statement,
2689 l_module_name,
2690 'l_return_status: '
2691 || l_return_status
2692 );
2693 END IF;
2694
2695 -- rmunjulu 4016497
2696 create_report (p_source => 'NOTNULL', p_message => l_message);
2697 END IF;
2698
2699 -- increment i
2700 i := i + 1;
2701 END LOOP;
2702 ELSE -- no value passed for p contract number
2703 /* RMUNJULU 2730738
2704 fnd_file.put_line(fnd_file.log, '');
2705 fnd_file.put_line(fnd_file.output, '');
2706 fnd_file.put_line(fnd_file.log, 'Processing the expired contracts.');
2707 fnd_file.put_line(fnd_file.output, 'Processing the expired contracts.');
2708 fnd_file.put_line(fnd_file.log, '');
2709 fnd_file.put_line(fnd_file.output, '');
2710 */ -- for leases/loans whose end date is less than today and which are not already
2711 -- terminated call the lease_loan_terminate api
2712 FOR ter_cnt_rec IN ter_cnt_csr (db_sysdate)
2713 LOOP
2714 -- set the term_rec_type of terminate_contract
2715 lp_term_rec.p_contract_id := ter_cnt_rec.ID;
2716 lp_term_rec.p_contract_number := ter_cnt_rec.contract_number;
2717 lp_term_rec.p_termination_date := db_sysdate;
2718 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
2719
2720 -- rmunjulu LOANS_ENHANCEMENTSS
2721 OPEN k_details_csr (lp_term_rec.p_contract_id);
2722
2723 FETCH k_details_csr
2724 INTO l_deal_type;
2725
2726 CLOSE k_details_csr;
2727
2728 --Added by bkatraga for bug 11924386
2729 IF l_deal_type = 'LOAN' THEN
2730 OKL_VARIABLE_INT_UTIL_PVT.check_principal_stream(
2731 p_khr_id => lp_term_rec.p_contract_id,
2732 x_return_status => l_return_status,
2733 x_prin_stream_flag => l_prin_stream_flag);
2734
2735 IF (is_debug_statement_on) THEN
2736 okl_debug_pub.log_debug
2737 (g_level_statement,
2738 l_module_name,
2739 'After OKL_VARIABLE_INT_UTIL_PVT.check_principal_stream '
2740 );
2741 okl_debug_pub.log_debug (g_level_statement,
2742 l_module_name,
2743 'l_prin_stream_flag: ' || l_prin_stream_flag
2744 );
2745 okl_debug_pub.log_debug (g_level_statement,
2746 l_module_name,
2747 'l_return_status: '
2748 || l_return_status
2749 );
2750 END IF;
2751 END IF;
2752 --end bkatraga
2753
2754 --Modified IF condition by bkatraga for bug 11924386
2755 -- rmunjulu LOANS_ENHANCEMENTS
2756 IF (l_deal_type = 'LOAN-REVOLVING' OR (l_deal_type = 'LOAN' AND l_prin_stream_flag = 0)) THEN
2757 -- get principal balance of loan contract
2758 l_prin_bal :=
2759 okl_variable_int_util_pvt.get_principal_bal
2760 (x_return_status => l_return_status,
2761 p_khr_id => lp_term_rec.p_contract_id,
2762 p_kle_id => NULL,
2763 p_date => SYSDATE
2764 );
2765 END IF;
2766
2767 -- rmunjulu LOANS_ENHANCEMENTS
2768 IF NVL (l_prin_bal, 0) <= 0 THEN
2769 -- rmunjulu 5058848 check for prin bal <= 0
2770 -- set the out tbl
2771 lx_term_tbl (i).p_contract_id := lp_term_rec.p_contract_id;
2772 lx_term_tbl (i).p_contract_number :=
2773 lp_term_rec.p_contract_number;
2774
2775 IF (is_debug_statement_on) THEN
2776 okl_debug_pub.log_debug (g_level_statement,
2777 l_module_name,
2778 'Before process_termination'
2779 );
2780 okl_debug_pub.log_debug
2781 (g_level_statement,
2782 l_module_name,
2783 'In param, p_term_rec.p_contract_id: '
2784 || lp_term_rec.p_contract_id
2785 );
2786 okl_debug_pub.log_debug
2787 (g_level_statement,
2788 l_module_name,
2789 'In param, p_term_rec.p_contract_number: '
2790 || lp_term_rec.p_contract_number
2791 );
2792 END IF;
2793
2794 process_termination (p_api_version => p_api_version,
2795 p_init_msg_list => okl_api.g_true,
2796 x_return_status => l_return_status,
2797 x_msg_count => x_msg_count,
2798 x_msg_data => x_msg_data,
2799 p_term_rec => lp_term_rec,
2800 x_tcnv_rec => lx_tcnv_rec,
2801 x_term_rec => lx_term_rec
2802 );
2803
2804 IF (is_debug_statement_on) THEN
2805 okl_debug_pub.log_debug (g_level_statement,
2806 l_module_name,
2807 'After process_termination '
2808 );
2809 okl_debug_pub.log_debug (g_level_statement,
2810 l_module_name,
2811 'l_return_status: '
2812 || l_return_status
2813 );
2814 END IF;
2815
2816 -- RMUNJULU 2730738 For proper output file
2817 IF (is_debug_statement_on) THEN
2818 okl_debug_pub.log_debug (g_level_statement,
2819 l_module_name,
2820 'Before check_contract'
2821 );
2822 okl_debug_pub.log_debug (g_level_statement,
2823 l_module_name,
2824 'In param, p_contract_id: '
2825 || lp_term_rec.p_contract_id
2826 );
2827 END IF;
2828
2829 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2830 x_start_date => l_start_date,
2831 x_end_date => l_end_date,
2832 x_status => l_status,
2833 x_control_flag => l_control_flag
2834 );
2835
2836 IF (is_debug_statement_on) THEN
2837 okl_debug_pub.log_debug (g_level_statement,
2838 l_module_name,
2839 'After check_contract '
2840 );
2841 okl_debug_pub.log_debug (g_level_statement,
2842 l_module_name,
2843 'x_start_date: ' || l_start_date
2844 );
2845 okl_debug_pub.log_debug (g_level_statement,
2846 l_module_name,
2847 'x_end_date: ' || l_end_date
2848 );
2849 okl_debug_pub.log_debug (g_level_statement,
2850 l_module_name,
2851 'x_status: ' || l_status
2852 );
2853 okl_debug_pub.log_debug (g_level_statement,
2854 l_module_name,
2855 'x_control_flag: '
2856 || l_control_flag
2857 );
2858 okl_debug_pub.log_debug (g_level_statement,
2859 l_module_name,
2860 'l_return_status: '
2861 || l_return_status
2862 );
2863 END IF;
2864
2865 IF (is_debug_statement_on) THEN
2866 okl_debug_pub.log_debug (g_level_statement,
2867 l_module_name,
2868 'Before fnd_output '
2869 );
2870 okl_debug_pub.log_debug
2871 (g_level_statement,
2872 l_module_name,
2873 'In param, p_term_rec.p_contract_id: '
2874 || lp_term_rec.p_contract_id
2875 );
2876 okl_debug_pub.log_debug
2877 (g_level_statement,
2878 l_module_name,
2879 'In param, p_term_rec.p_contract_number: '
2880 || lp_term_rec.p_contract_number
2881 );
2882 END IF;
2883
2884 -- RMUNJULU 2730738 For proper output file
2885 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2886 p_chr_number => lp_term_rec.p_contract_number,
2887 p_start_date => l_start_date,
2888 p_end_date => l_end_date,
2889 p_status => l_status,
2890 p_exp_recy => 'EXP',
2891 p_control_flag => l_control_flag
2892 );
2893
2894 IF (is_debug_statement_on) THEN
2895 okl_debug_pub.log_debug (g_level_statement,
2896 l_module_name,
2897 'After fnd_output '
2898 );
2899 okl_debug_pub.log_debug (g_level_statement,
2900 l_module_name,
2901 'l_return_status: '
2902 || l_return_status
2903 );
2904 END IF;
2905
2906 -- RMUNJULU 2730738 For proper output file
2907 reset_asset_msg_tbl;
2908 -- set the out tbl termination date
2909 lx_term_tbl (i) := lx_term_rec;
2910
2911 -- update the overall status only if l_return_status is not success
2912 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
2913 l_overall_status := l_return_status;
2914 END IF;
2915
2916 -- increment i
2917 i := i + 1;
2918 END IF;
2919 END LOOP;
2920
2921 /* RMUNJULU 2730738
2922 fnd_file.put_line(fnd_file.log, '');
2923 fnd_file.put_line(fnd_file.output, '');
2924 fnd_file.put_line(fnd_file.log, 'Processing the recycled termination transactions.');
2925 fnd_file.put_line(fnd_file.output, 'Processing the recycled termination transactions.');
2926 fnd_file.put_line(fnd_file.log, '');
2927 fnd_file.put_line(fnd_file.output, '');
2928 */ -- for leases/loans which have been recycled call the
2929 -- lease_loan_termination api
2930 FOR ter_cnt_recy_rec IN ter_cnt_recy_csr (db_sysdate)
2931 LOOP
2932 -- set the term_rec_type of terminate_contract
2933 lp_term_rec.p_contract_id := ter_cnt_recy_rec.ID;
2934 lp_term_rec.p_contract_number := ter_cnt_recy_rec.contract_number;
2935 lp_term_rec.p_termination_date := db_sysdate;
2936 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
2937 -- set the out tbl
2938 lx_term_tbl (i + j).p_contract_id := lp_term_rec.p_contract_id;
2939 lx_term_tbl (i + j).p_contract_number :=
2940 lp_term_rec.p_contract_number;
2941
2942 IF (is_debug_statement_on) THEN
2943 okl_debug_pub.log_debug (g_level_statement,
2944 l_module_name,
2945 'Before process_termination'
2946 );
2947 okl_debug_pub.log_debug
2948 (g_level_statement,
2949 l_module_name,
2950 'In param, p_term_rec.p_contract_id: '
2951 || lp_term_rec.p_contract_id
2952 );
2953 okl_debug_pub.log_debug
2954 (g_level_statement,
2955 l_module_name,
2956 'In param, p_term_rec.p_contract_number: '
2957 || lp_term_rec.p_contract_number
2958 );
2959 END IF;
2960
2961 process_termination (p_api_version => p_api_version,
2962 p_init_msg_list => okl_api.g_true,
2963 x_return_status => l_return_status,
2964 x_msg_count => x_msg_count,
2965 x_msg_data => x_msg_data,
2966 p_term_rec => lp_term_rec,
2967 p_trn_id => ter_cnt_recy_rec.trn_id,
2968 --RMUNJULU 17-NOV-02 Bug # 2484327 Added
2969 x_tcnv_rec => lx_tcnv_rec,
2970 x_term_rec => lx_term_rec
2971 );
2972
2973 IF (is_debug_statement_on) THEN
2974 okl_debug_pub.log_debug (g_level_statement,
2975 l_module_name,
2976 'After process_termination '
2977 );
2978 okl_debug_pub.log_debug (g_level_statement,
2979 l_module_name,
2980 'l_return_status: ' || l_return_status
2981 );
2982 END IF;
2983
2984 IF (is_debug_statement_on) THEN
2985 okl_debug_pub.log_debug (g_level_statement,
2986 l_module_name,
2987 'Before check_contract'
2988 );
2989 okl_debug_pub.log_debug (g_level_statement,
2990 l_module_name,
2991 'In param, p_contract_id: '
2992 || lp_term_rec.p_contract_id
2993 );
2994 END IF;
2995
2996 -- RMUNJULU 2730738 For proper output file
2997 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2998 x_start_date => l_start_date,
2999 x_end_date => l_end_date,
3000 x_status => l_status,
3001 x_control_flag => l_control_flag
3002 );
3003
3004 IF (is_debug_statement_on) THEN
3005 okl_debug_pub.log_debug (g_level_statement,
3006 l_module_name,
3007 'After check_contract '
3008 );
3009 okl_debug_pub.log_debug (g_level_statement,
3010 l_module_name,
3011 'x_start_date: ' || l_start_date
3012 );
3013 okl_debug_pub.log_debug (g_level_statement,
3014 l_module_name,
3015 'x_end_date: ' || l_end_date
3016 );
3017 okl_debug_pub.log_debug (g_level_statement,
3018 l_module_name,
3019 'x_status: ' || l_status
3020 );
3021 okl_debug_pub.log_debug (g_level_statement,
3022 l_module_name,
3023 'x_control_flag: ' || l_control_flag
3024 );
3025 okl_debug_pub.log_debug (g_level_statement,
3026 l_module_name,
3027 'l_return_status: ' || l_return_status
3028 );
3029 END IF;
3030
3031 IF (is_debug_statement_on) THEN
3032 okl_debug_pub.log_debug (g_level_statement,
3033 l_module_name,
3034 'Before fnd_output '
3035 );
3036 okl_debug_pub.log_debug
3037 (g_level_statement,
3038 l_module_name,
3039 'In param, p_term_rec.p_contract_id: '
3040 || lp_term_rec.p_contract_id
3041 );
3042 okl_debug_pub.log_debug
3043 (g_level_statement,
3044 l_module_name,
3045 'In param, p_term_rec.p_contract_number: '
3046 || lp_term_rec.p_contract_number
3047 );
3048 END IF;
3049
3050 -- RMUNJULU 2730738 For proper output file
3051 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
3052 p_chr_number => lp_term_rec.p_contract_number,
3053 p_start_date => l_start_date,
3054 p_end_date => l_end_date,
3055 p_status => l_status,
3056 p_exp_recy => 'RECY',
3057 p_control_flag => l_control_flag
3058 );
3059
3060 IF (is_debug_statement_on) THEN
3061 okl_debug_pub.log_debug (g_level_statement,
3062 l_module_name,
3063 'After fnd_output '
3064 );
3065 okl_debug_pub.log_debug (g_level_statement,
3066 l_module_name,
3067 'l_return_status: ' || l_return_status
3068 );
3069 END IF;
3070
3071 -- RMUNJULU 2730738 For proper output file
3072 reset_asset_msg_tbl;
3073 -- set the out tbl termination date
3074 lx_term_tbl (i + j) := lx_term_rec;
3075
3076 -- update the overall status only if l_return_status is not success
3077 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
3078 l_overall_status := l_return_status;
3079 END IF;
3080
3081 -- increment i
3082 j := j + 1;
3083 END LOOP;
3084 END IF;
3085
3086 -- set the out parameters
3087 x_term_tbl := lx_term_tbl;
3088 x_return_status := l_overall_status;
3089
3090 IF (is_debug_procedure_on) THEN
3091 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3092 END IF;
3093 EXCEPTION
3094 WHEN okl_api.g_exception_error THEN
3095 IF (is_debug_exception_on) THEN
3096 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'G_EXCEPTION_ERROR :' );
3097 END IF;
3098 x_return_status := okl_api.g_ret_sts_error;
3099 WHEN okl_api.g_exception_unexpected_error THEN
3100 IF (is_debug_exception_on) THEN
3101 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR' );
3102 END IF;
3103 x_return_status := okl_api.g_ret_sts_unexp_error;
3104 WHEN OTHERS THEN
3105 IF (is_debug_exception_on) THEN
3106 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
3107 || sqlcode || ' , SQLERRM : ' || sqlerrm);
3108 END IF;
3109 okl_api.set_message (p_app_name => g_app_name_1,
3110 p_msg_name => g_unexpected_error,
3111 p_token1 => g_sqlcode_token,
3112 p_token1_value => SQLCODE,
3113 p_token2 => g_sqlerrm_token,
3114 p_token2_value => SQLERRM
3115 );
3116 x_return_status := okl_api.g_ret_sts_unexp_error;
3117 END batch_expire_lease_loan;
3118
3119 -- Start of comments
3120 --
3121 -- Procedure Name : concurrent_expire_lease_loan
3122 -- Description : This procedure calls batch_expire_lease_loan procedure, used
3123 -- by concurrent program
3124 -- Business Rules :
3125 -- Parameters :
3126 -- Version : 1.0
3127 -- History : rmunjulu 4016497 Changed to not check validity (it will be checked in batch_expire_lease_loan)
3128 -- End of comments
3129 PROCEDURE concurrent_expire_lease_loan (
3130 errbuf OUT NOCOPY VARCHAR2,
3131 retcode OUT NOCOPY VARCHAR2,
3132 p_api_version IN NUMBER,
3133 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
3134 p_contract_id IN NUMBER DEFAULT okl_api.g_miss_num
3135 ) IS
3136 l_return_status VARCHAR2 (1);
3137 l_msg_count NUMBER;
3138 l_msg_data VARCHAR2 (2000);
3139 l_mesg VARCHAR2 (4000);
3140 l_mesg_len NUMBER;
3141 l_term_tbl term_tbl_type;
3142 lx_contract_status VARCHAR2 (200);
3143 lx_error_rec okl_api.error_rec_type;
3144 l_msg_idx INTEGER := fnd_msg_pub.g_first;
3145 -- akrangan added for debug feature start
3146 l_module_name VARCHAR2 (500)
3147 := g_module_name || 'concurrent_expire_lease_loan';
3148 is_debug_exception_on BOOLEAN
3149 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3150 is_debug_procedure_on BOOLEAN
3151 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3152 is_debug_statement_on BOOLEAN
3153 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3154 -- akrangan added for debug feature end
3155 BEGIN
3156 IF (is_debug_procedure_on) THEN
3157 okl_debug_pub.log_debug (g_level_procedure,
3158 l_module_name,
3159 'Begin(+)'
3160 );
3161 END IF;
3162
3163 IF (is_debug_statement_on) THEN
3164 okl_debug_pub.log_debug (g_level_statement,
3165 l_module_name,
3166 'p_contract_id =' || p_contract_id
3167 );
3168 END IF;
3169
3170 -- Check if a single contract termination request
3171 IF p_contract_id IS NOT NULL AND p_contract_id <> okl_api.g_miss_num THEN
3172 /* -- rmunjulu BUG 4016497
3173 -- Check the validity of the contract
3174 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
3175 p_api_version => p_api_version,
3176 p_init_msg_list => p_init_msg_list,
3177 x_return_status => l_return_status,
3178 x_msg_count => l_msg_count,
3179 x_msg_data => l_msg_data,
3180 p_contract_id => p_contract_id,
3181 p_control_flag => 'BATCH_PROCESS_CHR',
3182 x_contract_status => lx_contract_status);
3183 */
3184
3185 /*
3186 -- Check if contract valid or not
3187 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN -- Contract validity failed
3188
3189 fnd_file.put_line(fnd_file.log, 'Processing termination request from concurrent manager.');
3190 fnd_file.put_line(fnd_file.output, 'Processing termination request from concurrent manager.');
3191
3192 fnd_file.put_line(fnd_file.log, '');
3193 fnd_file.put_line(fnd_file.output, '');
3194
3195 LOOP
3196
3197 fnd_msg_pub.get(
3198 p_msg_index => l_msg_idx,
3199 p_encoded => FND_API.G_FALSE,
3200 p_data => lx_error_rec.msg_data,
3201 p_msg_index_out => lx_error_rec.msg_count);
3202
3203 IF (lx_error_rec.msg_count IS NOT NULL) THEN
3204
3205 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
3206 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
3207
3208 END IF;
3209
3210 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
3211 OR (lx_error_rec.msg_count IS NULL));
3212
3213 l_msg_idx := FND_MSG_PUB.G_NEXT;
3214 END LOOP;
3215
3216 fnd_file.put_line(fnd_file.log, '');
3217 fnd_file.put_line(fnd_file.output, '');
3218 fnd_file.put_line(fnd_file.log, 'Termination of contract failed');
3219 fnd_file.put_line(fnd_file.output, 'Termination of contract failed');
3220
3221 ELSE -- Contract valid
3222 */
3223 /* RMUNJULU 2730738
3224 fnd_file.put_line(fnd_file.log, 'Processing termination request from concurrent manager.');
3225 fnd_file.put_line(fnd_file.output, 'Processing termination request from concurrent manager.');
3226
3227 fnd_file.put_line(fnd_file.log, '');
3228 fnd_file.put_line(fnd_file.output, '');
3229 */
3230 -- Terminate the contract
3231 IF (is_debug_statement_on) THEN
3232 okl_debug_pub.log_debug (g_level_statement,
3233 l_module_name,
3234 'before batch_expire_lease_loan'
3235 );
3236 okl_debug_pub.log_debug (g_level_statement,
3237 l_module_name,
3238 'p_contract_id =' || p_contract_id
3239 );
3240 END IF;
3241
3242 batch_expire_lease_loan (p_api_version => p_api_version,
3243 p_init_msg_list => p_init_msg_list,
3244 x_return_status => l_return_status,
3245 x_msg_count => l_msg_count,
3246 x_msg_data => l_msg_data,
3247 p_contract_id => p_contract_id,
3248 x_term_tbl => l_term_tbl
3249 );
3250
3251 IF (is_debug_statement_on) THEN
3252 okl_debug_pub.log_debug (g_level_statement,
3253 l_module_name,
3254 'after batch_expire_lease_loan'
3255 );
3256 okl_debug_pub.log_debug (g_level_statement,
3257 l_module_name,
3258 'l_return_status ='
3259 || l_return_status
3260 );
3261 END IF;
3262 -- rmunjulu 4016497 Report will be generated inside ABOVE API
3263 /*
3264 -- RMUNJULU 2730738
3265 create_report;
3266
3267 END IF;
3268 */
3269 ELSE -- No contract passed, so scheduled request
3270 /* RMUNJULU 2730738
3271 fnd_file.put_line(fnd_file.log, 'Processing termination request from concurrent manager.');
3272 fnd_file.put_line(fnd_file.output, 'Processing termination request from concurrent manager.');
3273
3274 fnd_file.put_line(fnd_file.log, '');
3275 fnd_file.put_line(fnd_file.output, '');
3276 */
3277 IF (is_debug_statement_on) THEN
3278 okl_debug_pub.log_debug (g_level_statement,
3279 l_module_name,
3280 'before batch_expire_lease_loan'
3281 );
3282 okl_debug_pub.log_debug (g_level_statement,
3283 l_module_name,
3284 'p_contract_id =' || p_contract_id
3285 );
3286 END IF;
3287
3288 -- Terminate the contract
3289 batch_expire_lease_loan (p_api_version => p_api_version,
3290 p_init_msg_list => p_init_msg_list,
3291 x_return_status => l_return_status,
3292 x_msg_count => l_msg_count,
3293 x_msg_data => l_msg_data,
3294 p_contract_id => p_contract_id,
3295 x_term_tbl => l_term_tbl
3296 );
3297
3298 IF (is_debug_statement_on) THEN
3299 okl_debug_pub.log_debug (g_level_statement,
3300 l_module_name,
3301 'after batch_expire_lease_loan'
3302 );
3303 okl_debug_pub.log_debug (g_level_statement,
3304 l_module_name,
3305 'l_return_status ='
3306 || l_return_status
3307 );
3308 END IF;
3309
3310 -- RMUNJULU 2730738
3311 create_report;
3312 END IF;
3313
3314 IF (is_debug_procedure_on) THEN
3315 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3316 END IF;
3317 END concurrent_expire_lease_loan;
3318
3319 PROCEDURE write_to_log (p_message IN VARCHAR2) IS
3320 -- akrangan added for debug feature start
3321 l_module_name VARCHAR2 (500)
3322 := g_module_name || 'write_to_log';
3323 is_debug_exception_on BOOLEAN
3324 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3325 is_debug_procedure_on BOOLEAN
3326 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3327 is_debug_statement_on BOOLEAN
3328 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3329 -- akrangan added for debug feature end
3330 BEGIN
3331 IF (is_debug_procedure_on) THEN
3332 okl_debug_pub.log_debug (g_level_procedure,
3333 l_module_name,
3334 'Begin(+)'
3335 );
3336 END IF;
3337
3338 IF (is_debug_statement_on) THEN
3339 okl_debug_pub.log_debug (g_level_statement,
3340 l_module_name,
3341 'p_message =' || p_message
3342 );
3343 END IF;
3344
3345 -- dbms_output.put_line(p_message);
3346 fnd_file.put_line (fnd_file.output, p_message);
3347
3348 IF (is_debug_procedure_on) THEN
3349 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3350 END IF;
3351 END write_to_log;
3352
3353 -- RMUNJULU PERF
3354 PROCEDURE batch_expire_lease_loan (
3355 p_api_version IN NUMBER,
3356 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
3357 x_return_status OUT NOCOPY VARCHAR2,
3358 x_msg_count OUT NOCOPY NUMBER,
3359 x_msg_data OUT NOCOPY VARCHAR2,
3360 p_assigned_processes IN VARCHAR2,
3361 x_term_tbl OUT NOCOPY term_tbl_type
3362 ) IS
3363 -- get contracts assigned to current process
3364 CURSOR get_contracts_of_process_csr (p_assigned_process IN VARCHAR2) IS
3365 SELECT opp.khr_id khr_id,
3366 opp.object_value contract_number,
3367 opp.trx_id trx_id
3368 FROM okl_parallel_processes opp
3369 WHERE opp.assigned_process = p_assigned_process;
3370
3371 -- rmunjulu LOANS_ENHANCEMENTS
3372 CURSOR k_details_csr (p_khr_id IN NUMBER) IS
3373 SELECT deal_type
3374 FROM okl_k_headers
3375 WHERE ID = p_khr_id;
3376
3377 lp_term_rec term_rec_type;
3378 lx_term_rec term_rec_type;
3379 lx_term_tbl term_tbl_type;
3380 lp_tcnv_rec tcnv_rec_type;
3381 lx_tcnv_rec tcnv_rec_type;
3382 db_sysdate DATE;
3383 i NUMBER := 1;
3384 j NUMBER := 1;
3385 l_chr_id NUMBER;
3386 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
3387 l_api_name VARCHAR2 (200) := 'batch_expire_lease_loan';
3388 l_overall_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
3389 l_start_date DATE;
3390 l_end_date DATE;
3391 l_status VARCHAR2 (200);
3392 l_control_flag VARCHAR2 (10);
3393 l_exp_chr_yn VARCHAR2 (3);
3394 l_recy_chr_yn VARCHAR2 (3);
3395 l_message VARCHAR2 (30000);
3396 -- rmunjulu LOANS_ENHANCEMENTS
3397 l_prin_bal NUMBER := 0;
3398 l_deal_type VARCHAR2 (300);
3399 l_prin_stream_flag NUMBER; --Added by bkatraga for bug 11924386
3400 -- akrangan added for debug feature start
3401 l_module_name VARCHAR2 (500) := g_module_name || 'fnd_output';
3402 is_debug_exception_on BOOLEAN
3403 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3404 is_debug_procedure_on BOOLEAN
3405 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3406 is_debug_statement_on BOOLEAN
3407 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3408 -- akrangan added for debug feature end
3409 BEGIN
3410 FOR get_contracts_of_process_rec IN
3411 get_contracts_of_process_csr (p_assigned_processes)
3412 LOOP
3413 -- set the term_rec_type of terminate_contract
3414 lp_term_rec.p_contract_id := get_contracts_of_process_rec.khr_id;
3415 lp_term_rec.p_contract_number :=
3416 get_contracts_of_process_rec.contract_number;
3417 lp_term_rec.p_termination_date := SYSDATE;
3418 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
3419
3420 -- rmunjulu LOANS_ENHANCEMENTS
3421 OPEN k_details_csr (lp_term_rec.p_contract_id);
3422
3423 FETCH k_details_csr
3424 INTO l_deal_type;
3425
3426 CLOSE k_details_csr;
3427
3428 --Added by bkatraga for bug 11924386
3429 IF l_deal_type = 'LOAN' THEN
3430 OKL_VARIABLE_INT_UTIL_PVT.check_principal_stream(
3431 p_khr_id => lp_term_rec.p_contract_id,
3432 x_return_status => l_return_status,
3433 x_prin_stream_flag => l_prin_stream_flag);
3434
3435 IF (is_debug_statement_on) THEN
3436 okl_debug_pub.log_debug
3437 (g_level_statement,
3438 l_module_name,
3439 'After OKL_VARIABLE_INT_UTIL_PVT.check_principal_stream '
3440 );
3441 okl_debug_pub.log_debug (g_level_statement,
3442 l_module_name,
3443 'l_prin_stream_flag: ' || l_prin_stream_flag
3444 );
3445 okl_debug_pub.log_debug (g_level_statement,
3446 l_module_name,
3447 'l_return_status: '
3448 || l_return_status
3449 );
3450 END IF;
3451 END IF;
3452 --end bkatraga
3453
3454 --Modified IF condition by bkatraga for bug 11924386
3455 -- rmunjulu LOANS_ENHANCEMENTS
3456 IF (l_deal_type = 'LOAN-REVOLVING' OR (l_deal_type = 'LOAN' AND l_prin_stream_flag = 0)) THEN
3457 IF (is_debug_statement_on) THEN
3458 okl_debug_pub.log_debug
3459 (g_level_statement,
3460 l_module_name,
3461 'Before OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
3462 );
3463 okl_debug_pub.log_debug (g_level_statement,
3464 l_module_name,
3465 'In param, p_contract_id: '
3466 || lp_term_rec.p_contract_id
3467 );
3468 END IF;
3469
3470 -- get principal balance of loan contract
3471 l_prin_bal :=
3472 okl_variable_int_util_pvt.get_principal_bal
3473 (x_return_status => l_return_status,
3474 p_khr_id => lp_term_rec.p_contract_id,
3475 p_kle_id => NULL,
3476 p_date => SYSDATE
3477 );
3478
3479 IF (is_debug_statement_on) THEN
3480 okl_debug_pub.log_debug
3481 (g_level_statement,
3482 l_module_name,
3483 'After OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
3484 );
3485 okl_debug_pub.log_debug (g_level_statement,
3486 l_module_name,
3487 'l_prin_bal: ' || l_prin_bal
3488 );
3489 okl_debug_pub.log_debug (g_level_statement,
3490 l_module_name,
3491 'l_return_status: ' || l_return_status
3492 );
3493 END IF;
3494 END IF;
3495
3496 -- rmunjulu LOANS_ENHANCEMENTS
3497 IF NVL (l_prin_bal, 0) <= 0 THEN
3498 --rmunjulu 5058848 check for prin bal <= 0
3499 -- set the out tbl
3500 lx_term_tbl (i).p_contract_id := lp_term_rec.p_contract_id;
3501 lx_term_tbl (i).p_contract_number :=
3502 lp_term_rec.p_contract_number;
3503
3504 IF (is_debug_statement_on) THEN
3505 okl_debug_pub.log_debug (g_level_statement,
3506 l_module_name,
3507 'Before process_termination'
3508 );
3509 okl_debug_pub.log_debug
3510 (g_level_statement,
3511 l_module_name,
3512 'In param, p_term_rec.p_contract_id: '
3513 || lp_term_rec.p_contract_id
3514 );
3515 okl_debug_pub.log_debug
3516 (g_level_statement,
3517 l_module_name,
3518 'In param, p_term_rec.p_contract_number: '
3519 || lp_term_rec.p_contract_number
3520 );
3521 okl_debug_pub.log_debug (g_level_statement,
3522 l_module_name,
3523 'In param, p_trn_id: '
3524 || get_contracts_of_process_rec.trx_id
3525 );
3526 END IF;
3527
3528 process_termination
3529 (p_api_version => p_api_version,
3530 p_init_msg_list => okl_api.g_true,
3531 x_return_status => l_return_status,
3532 x_msg_count => x_msg_count,
3533 x_msg_data => x_msg_data,
3534 p_term_rec => lp_term_rec,
3535 p_trn_id => get_contracts_of_process_rec.trx_id,
3536 -- rmunjulu added to pass trx_id if exists
3537 x_tcnv_rec => lx_tcnv_rec,
3538 x_term_rec => lx_term_rec
3539 );
3540
3541 IF (is_debug_statement_on) THEN
3542 okl_debug_pub.log_debug (g_level_statement,
3543 l_module_name,
3544 'After process_termination '
3545 );
3546 okl_debug_pub.log_debug (g_level_statement,
3547 l_module_name,
3548 'l_return_status: ' || l_return_status
3549 );
3550 END IF;
3551
3552 IF (is_debug_statement_on) THEN
3553 okl_debug_pub.log_debug (g_level_statement,
3554 l_module_name,
3555 'Before check_contract'
3556 );
3557 okl_debug_pub.log_debug (g_level_statement,
3558 l_module_name,
3559 'In param, p_contract_id: '
3560 || lp_term_rec.p_contract_id
3561 );
3562 END IF;
3563
3564 -- RMUNJULU 2730738 For proper output file
3565 check_contract (p_chr_id => lp_term_rec.p_contract_id,
3566 x_start_date => l_start_date,
3567 x_end_date => l_end_date,
3568 x_status => l_status,
3569 x_control_flag => l_control_flag
3570 );
3571
3572 IF (is_debug_statement_on) THEN
3573 okl_debug_pub.log_debug (g_level_statement,
3574 l_module_name,
3575 'After check_contract '
3576 );
3577 okl_debug_pub.log_debug (g_level_statement,
3578 l_module_name,
3579 'x_start_date: ' || l_start_date
3580 );
3581 okl_debug_pub.log_debug (g_level_statement,
3582 l_module_name,
3583 'x_end_date: ' || l_end_date
3584 );
3585 okl_debug_pub.log_debug (g_level_statement,
3586 l_module_name,
3587 'x_status: ' || l_status
3588 );
3589 okl_debug_pub.log_debug (g_level_statement,
3590 l_module_name,
3591 'x_control_flag: ' || l_control_flag
3592 );
3593 okl_debug_pub.log_debug (g_level_statement,
3594 l_module_name,
3595 'l_return_status: ' || l_return_status
3596 );
3597 END IF;
3598
3599 -- rmunjulu for proper output need to tell if recy or exp
3600 IF get_contracts_of_process_rec.trx_id IS NOT NULL
3601 AND get_contracts_of_process_rec.trx_id <> okl_api.g_miss_num THEN
3602 IF (is_debug_statement_on) THEN
3603 okl_debug_pub.log_debug (g_level_statement,
3604 l_module_name,
3605 'before fnd_output '
3606 );
3607 END IF;
3608
3609 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
3610 p_chr_number => lp_term_rec.p_contract_number,
3611 p_start_date => l_start_date,
3612 p_end_date => l_end_date,
3613 p_status => l_status,
3614 p_exp_recy => 'RECY',
3615 p_control_flag => l_control_flag
3616 );
3617
3618 IF (is_debug_statement_on) THEN
3619 okl_debug_pub.log_debug (g_level_statement,
3620 l_module_name,
3621 'After fnd_output '
3622 );
3623 okl_debug_pub.log_debug (g_level_statement,
3624 l_module_name,
3625 'l_return_status: '
3626 || l_return_status
3627 );
3628 END IF;
3629 ELSE
3630 IF (is_debug_statement_on) THEN
3631 okl_debug_pub.log_debug (g_level_statement,
3632 l_module_name,
3633 'before fnd_output '
3634 );
3635 END IF;
3636
3637 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
3638 p_chr_number => lp_term_rec.p_contract_number,
3639 p_start_date => l_start_date,
3640 p_end_date => l_end_date,
3641 p_status => l_status,
3642 p_exp_recy => 'EXP',
3643 p_control_flag => l_control_flag
3644 );
3645
3646 IF (is_debug_statement_on) THEN
3647 okl_debug_pub.log_debug (g_level_statement,
3648 l_module_name,
3649 'After fnd_output '
3650 );
3651 okl_debug_pub.log_debug (g_level_statement,
3652 l_module_name,
3653 'l_return_status: '
3654 || l_return_status
3655 );
3656 END IF;
3657 END IF;
3658
3659 -- RMUNJULU 2730738 For proper output file
3660 reset_asset_msg_tbl;
3661 -- set the out tbl termination date
3662 lx_term_tbl (i) := lx_term_rec;
3663
3664 -- update the overall status only if l_return_status is not success
3665 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
3666 l_overall_status := l_return_status;
3667 END IF;
3668
3669 -- increment i
3670 i := i + 1;
3671 END IF;
3672 END LOOP;
3673
3674 x_term_tbl := lx_term_tbl;
3675
3676 IF (is_debug_procedure_on) THEN
3677 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3678 END IF;
3679 END batch_expire_lease_loan;
3680
3681 -- RMUNJULU PERF
3682 -- THIS PROCESS IS CALLED FROM CHILD PROCESS WHICH WAS SPAWNED FROM MAIN
3683 -- PROCESSESOR
3684 -- GETS THE CONTRACTS ASSIGNED TO PROCESSOR AND LAUNCHES TERMINATION FOR THOSE
3685 PROCEDURE child_process (
3686 errbuf OUT NOCOPY VARCHAR2,
3687 retcode OUT NOCOPY NUMBER,
3688 p_assigned_processes IN VARCHAR2 --,
3689 --p_api_version IN NUMBER,
3690 --p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3691 ) IS
3692 l_return_status VARCHAR2 (1);
3693 l_msg_count NUMBER;
3694 l_msg_data VARCHAR2 (2000);
3695 l_term_tbl term_tbl_type;
3696 l_api_version NUMBER := 1;
3697 -- akrangan added for debug feature start
3698 l_module_name VARCHAR2 (500)
3699 := g_module_name || 'write_to_log';
3700 is_debug_exception_on BOOLEAN
3701 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3702 is_debug_procedure_on BOOLEAN
3703 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3704 is_debug_statement_on BOOLEAN
3705 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3706 -- akrangan added for debug feature end
3707 BEGIN
3708 IF (is_debug_procedure_on) THEN
3709 okl_debug_pub.log_debug (g_level_procedure,
3710 l_module_name,
3711 'Begin(+)'
3712 );
3713 END IF;
3714
3715 IF (is_debug_statement_on) THEN
3716 okl_debug_pub.log_debug (g_level_statement,
3717 l_module_name,
3718 'before batch_expire_lease_loan '
3719 );
3720 END IF;
3721
3722 -- Terminate the contract -- call the new batch_expire procedure
3723 batch_expire_lease_loan (p_api_version => l_api_version,
3724 p_init_msg_list => okl_api.g_true,
3725 x_return_status => l_return_status,
3726 x_msg_count => l_msg_count,
3727 x_msg_data => l_msg_data,
3728 p_assigned_processes => p_assigned_processes,
3729 x_term_tbl => l_term_tbl
3730 );
3731
3732 IF (is_debug_statement_on) THEN
3733 okl_debug_pub.log_debug (g_level_statement,
3734 l_module_name,
3735 'after batch_expire_lease_loan '
3736 );
3737 okl_debug_pub.log_debug (g_level_statement,
3738 l_module_name,
3739 'l_return_status = ' || l_return_status
3740 );
3741 END IF;
3742
3743 -- create report
3744 create_report;
3745
3746 -- Do clean up of parallel processes
3747 DELETE FROM okl_parallel_processes
3748 WHERE assigned_process = p_assigned_processes;
3749
3750 IF (is_debug_procedure_on) THEN
3751 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3752 END IF;
3753 END child_process;
3754
3755 -- RMUNJULU PERF
3756 -- MASTER PROGRAM WHICH DOES PARALLEL LOAD BALANCE AND SPAWNS CHILD PROCESSES
3757 -- THIS PROCEDURE IS CALLED FROM MASTER CONCURRENT PROGRAM
3758 PROCEDURE process_spawner (
3759 errbuf OUT NOCOPY VARCHAR2,
3760 retcode OUT NOCOPY NUMBER,
3761 p_num_processes IN NUMBER,
3762 p_term_date IN VARCHAR2
3763 ) IS
3764 request_id NUMBER := 0;
3765
3766 -- GETS CONTRACTS
3767 CURSOR chk_update_header_csr IS
3768 SELECT contract_number,
3769 khr_id,
3770 trx_id
3771 FROM (SELECT khr.contract_number contract_number,
3772 khr.ID khr_id,
3773 NULL trx_id
3774 FROM okc_k_headers_b khr
3775 WHERE TRUNC (khr.end_date) < TRUNC (SYSDATE)
3776 AND NVL (khr.sts_code, '?') IN ('BOOKED')
3777 AND khr.scs_code IN ('LEASE', 'LOAN')
3778 -- rmunjulu --start -- added the following or else same record picked twice
3779 AND khr.ID NOT IN (
3780 -- Contracts which have unprocessed transactions
3781 SELECT NVL (tcn.khr_id, -9999) khr_id
3782 FROM okl_trx_contracts tcn
3783 WHERE NVL (tcn.tcn_type, '?') IN
3784 ('TMT', 'ALT', 'EVG')
3785 -- akrangan bug 5354501 fix added 'EVG'
3786 AND tcn.tmt_status_code NOT IN
3787 ('CANCELED', 'PROCESSED')
3788 --akrangan changed for sla tmt_status_Code cr
3789 --rkuttiya added for 12.1.1 Multi GAAP
3790 AND tcn.representation_type = 'PRIMARY'
3791 --
3792 AND tcn.khr_id = khr.ID) -- rmunjulu PERF
3793 AND khr.ID NOT IN (
3794 -- Contracts which have accepted quotes with no transactions
3795 SELECT NVL (qte.khr_id, -9999) khr_id
3796 FROM okl_trx_quotes_v qte
3797 WHERE NVL (qte.accepted_yn, 'N') = 'Y'
3798 AND NVL (qte.consolidated_yn, 'N') = 'N'
3799 AND qte.khr_id = khr.ID -- rmunjulu PERF
3800 AND qte.ID NOT IN (
3801 SELECT NVL (tcn.qte_id, -9999) qte_id
3802 FROM okl_trx_contracts tcn
3803 WHERE NVL (tcn.tcn_type, '?') IN
3804 ('TMT', 'ALT', 'EVG')
3805 -- akrangan bug 5354501 fix added 'EVG'
3806 --rkuttiya added for 12.1.1 Multi GAAP
3807 AND tcn.representation_type = 'PRIMARY'
3808 --
3809 AND tcn.qte_id = qte.ID))
3810 -- rmunjulu PERF
3811 -- rmunjulu -- end
3812 UNION
3813 SELECT k.contract_number,
3814 k.ID khr_id,
3815 t.ID trx_id
3816 FROM okc_k_headers_b k, okl_trx_contracts t
3817 WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
3818 AND NVL (t.tmt_status_code, '?') NOT IN
3819 ('PROCESSED', 'CANCELED')
3820 --akrangan changed for sla tmt_status_Code cr
3821 AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
3822 -- akrangan bug 5354501 fix added 'EVG'
3823 AND k.scs_code IN ('LEASE', 'LOAN')
3824 --rkuttiya added for 12.1.1 Multi GAAP
3825 AND t.representation_type = 'PRIMARY'
3826 --
3827 AND k.ID = t.khr_id)
3828 WHERE NOT EXISTS (
3829 SELECT '1'
3830 FROM okl_parallel_processes opp
3831 WHERE contract_number = opp.object_value
3832 AND opp.object_type = 'CONT_TERM'
3833 AND opp.process_status IN
3834 ('PENDING_ASSIGNMENT', 'ASSIGNED'));
3835
3836 -- GET DATA VOLUME BASED ON WHICH WILL DECIDE WHICH PARALLEL PROCESS TO USE.
3837 --Bug # 6174484 fixed for SQL Performance ssdehpa start
3838 CURSOR chk_data_volume_csr (p_seq_next VARCHAR2) IS
3839 /* SELECT opp.object_value contract_number , count(KLE.id) line_count
3840 FROM OKC_K_LINES_B KLE,
3841 okl_parallel_processes opp
3842 WHERE opp.khr_id = KLE.dnz_chr_id
3843 AND KLE.sts_code = ('BOOKED')
3844 AND opp.khr_id NOT IN(
3845 SELECT NVL(TCN.khr_id,-9999) khr_id
3846 FROM OKL_TRX_CONTRACTS TCN
3847 WHERE NVL(TCN.tcn_type,'?') IN ('TMT','ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
3848 AND TCN.tmt_status_code NOT IN ('CANCELED','PROCESSED')--akrangan changed for sla tmt_status_Code cr
3849 AND TCN.khr_id = opp.khr_id)
3850 AND opp.khr_id NOT IN (
3851 SELECT NVL(QTE.khr_id,-9999) khr_id
3852 FROM OKL_TRX_QUOTES_V QTE
3853 WHERE NVL(QTE.accepted_yn,'N') = 'Y'
3854 AND NVL(QTE.consolidated_yn,'N') = 'N'
3855 AND QTE.khr_id = opp.khr_id
3856 AND QTE.id NOT IN (
3857 SELECT NVL(TCN.qte_id,-9999) qte_id
3858 FROM OKL_TRX_CONTRACTS TCN
3859 WHERE NVL(TCN.tcn_type,'?') IN ('TMT','ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
3860 AND TCN.qte_id = QTE.id))
3861 AND opp.object_type = 'CONT_TERM'
3862 AND opp.assigned_process = p_seq_next
3863 GROUP BY opp.object_value
3864 UNION
3865 SELECT opp.object_value contract_number, count(KLE.id) line_count
3866 FROM okl_parallel_processes opp,
3867 OKL_TRX_CONTRACTS T,
3868 OKC_K_LINES_B KLE
3869 WHERE NVL(T.tmt_recycle_yn,'?') = 'Y'
3870 AND opp.khr_id = KLE.dnz_chr_id
3871 AND KLE.sts_code = 'BOOKED'
3872 AND NVL(T.tmt_status_code,'?') NOT IN('PROCESSED', 'CANCELED')--akrangan changed for sla tmt_status_Code cr
3873 AND NVL(T.tcn_type,'?') IN( 'TMT', 'ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
3874 AND opp.khr_id = T.khr_id
3875 AND opp.object_type = 'CONT_TERM'
3876 AND opp.assigned_process = p_seq_next
3877 GROUP BY opp.object_value; */
3878 SELECT opp.object_value contract_number,
3879 COUNT (kle.ID) line_count
3880 FROM okc_k_lines_b kle, okl_parallel_processes opp
3881 WHERE opp.khr_id = kle.dnz_chr_id
3882 AND kle.sts_code = ('BOOKED')
3883 AND opp.khr_id NOT IN (
3884 SELECT NVL (tcn.khr_id, -9999) khr_id
3885 FROM okl_trx_contracts_all tcn
3886 WHERE tcn.tcn_type IN ('TMT', 'ALT', 'EVG')
3887 -- akrangan bug 5354501 fix added 'EVG'
3888 AND tcn.tmt_status_code NOT IN
3889 ('CANCELED', 'PROCESSED')
3890 --akrangan changed for sla tmt_status_Code cr
3891 --rkuttiya added for 12.1.1 Multi GAAP
3892 AND tcn.representation_Type = 'PRIMARY'
3893 --
3894 AND tcn.khr_id = opp.khr_id)
3895 AND opp.khr_id NOT IN (
3896 SELECT NVL (qte.khr_id, -9999) khr_id
3897 FROM okl_trx_quotes_b qte
3898 WHERE NVL (qte.accepted_yn, 'N') = 'Y'
3899 AND NVL (qte.consolidated_yn, 'N') = 'N'
3900 AND qte.khr_id = opp.khr_id
3901 AND qte.ID NOT IN (
3902 SELECT NVL (tcn.qte_id, -9999) qte_id
3903 FROM okl_trx_contracts_all tcn
3904 WHERE tcn.tcn_type IN ('TMT', 'ALT', 'EVG')
3905 -- akrangan bug 5354501 fix added 'EVG'
3906 --rkuttiya added for 12.1.1 Multi GAAP
3907 AND tcn.representation_type = 'PRIMARY'
3908 --
3909 AND tcn.qte_id = qte.ID))
3910 AND opp.object_type = 'CONT_TERM'
3911 AND opp.assigned_process = p_seq_next
3912 GROUP BY opp.object_value
3913 UNION
3914 SELECT opp.object_value contract_number,
3915 COUNT (kle.ID) line_count
3916 FROM okl_parallel_processes opp,
3917 okl_trx_contracts t,
3918 okc_k_lines_b kle
3919 WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
3920 AND opp.khr_id = kle.dnz_chr_id
3921 AND kle.sts_code = 'BOOKED'
3922 AND NVL (t.tmt_status_code, '?') NOT IN
3923 ('PROCESSED', 'CANCELED')
3924 --akrangan changed for sla tmt_status_Code cr
3925 AND t.tcn_type IN ('TMT', 'ALT', 'EVG')
3926 -- akrangan bug 5354501 fix added 'EVG'
3927 AND opp.khr_id = t.khr_id
3928 --rkuttiya added for 12.1.1. Multi GAAP
3929 AND t.representation_type = 'PRIMARY'
3930 --
3931 AND opp.object_type = 'CONT_TERM'
3932 AND opp.assigned_process = p_seq_next
3933 GROUP BY opp.object_value;
3934
3935 --Bug # 6174484 fixed for SQL Performance ssdehpa end
3936 TYPE l_contract_rec IS RECORD (
3937 batch_number VARCHAR2 (60),
3938 contract_number VARCHAR2 (60),
3939 line_count NUMBER,
3940 worker_number NUMBER,
3941 khr_id NUMBER,
3942 trx_id NUMBER
3943 );
3944
3945 TYPE contract_tab IS TABLE OF l_contract_rec
3946 INDEX BY PLS_INTEGER;
3947
3948 TYPE worker_load_rec IS RECORD (
3949 worker_number NUMBER,
3950 worker_load NUMBER
3951 );
3952
3953 TYPE worker_load_tab IS TABLE OF worker_load_rec
3954 INDEX BY PLS_INTEGER;
3955
3956 TYPE contract_list IS RECORD (
3957 contract_number VARCHAR2 (60)
3958 );
3959
3960 TYPE contract_list_tab IS TABLE OF contract_list
3961 INDEX BY PLS_INTEGER;
3962
3963 l_contract_list contract_list_tab;
3964 l_worker_load worker_load_tab;
3965 l_contract_tab contract_tab;
3966 l_sort_tab1 contract_tab;
3967 l_temp_tab contract_tab;
3968 l_int_counter INTEGER;
3969 l_max_lines NUMBER;
3970 l_init_loop BOOLEAN := TRUE;
3971 l_sort_int_counter INTEGER;
3972 l_next_highest_val NUMBER;
3973 l_lightest_worker NUMBER;
3974 l_lightest_load NUMBER;
3975 l_seq_next NUMBER;
3976 l_data_found BOOLEAN := FALSE;
3977 lp_term_date DATE;
3978 l_return_status VARCHAR2 (1);
3979 l_msg_count NUMBER;
3980 l_msg_data VARCHAR2 (2000);
3981 l_term_tbl term_tbl_type;
3982 -- akrangan added for debug feature start
3983 l_module_name VARCHAR2 (500)
3984 := g_module_name || 'write_to_log';
3985 is_debug_exception_on BOOLEAN
3986 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3987 is_debug_procedure_on BOOLEAN
3988 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3989 is_debug_statement_on BOOLEAN
3990 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3991 -- akrangan added for debug feature end
3992 BEGIN
3993 IF (is_debug_procedure_on) THEN
3994 okl_debug_pub.log_debug (g_level_procedure,
3995 l_module_name,
3996 'Begin(+)'
3997 );
3998 END IF;
3999
4000 lp_term_date := fnd_date.canonical_to_date (p_term_date);
4001
4002 IF NVL (p_num_processes, 0) = 0 THEN
4003 write_to_log ('Number of parallel processors should be provided.');
4004 RETURN;
4005 ELSIF NVL (p_num_processes, 0) = 1 THEN
4006 --write_to_log('Single worker requested');
4007 -- no need to run algorithm, directly call single thread program here
4008 IF (is_debug_statement_on) THEN
4009 okl_debug_pub.log_debug (g_level_statement,
4010 l_module_name,
4011 'before batch_expire_lease_loan '
4012 );
4013 END IF;
4014
4015 -- Terminate the contract
4016 batch_expire_lease_loan (p_api_version => 1,
4017 p_init_msg_list => 'T',
4018 x_return_status => l_return_status,
4019 x_msg_count => l_msg_count,
4020 x_msg_data => l_msg_data,
4021 p_contract_id => NULL,
4022 x_term_tbl => l_term_tbl
4023 );
4024
4025 IF (is_debug_statement_on) THEN
4026 okl_debug_pub.log_debug (g_level_statement,
4027 l_module_name,
4028 'after batch_expire_lease_loan '
4029 );
4030 okl_debug_pub.log_debug (g_level_statement,
4031 l_module_name,
4032 'l_return_status = ' || l_return_status
4033 );
4034 END IF;
4035
4036 -- RMUNJULU
4037 create_report;
4038 RETURN;
4039 END IF;
4040
4041 l_int_counter := 0;
4042 l_max_lines := 0;
4043
4044 -- Select sequence for marking processes
4045 SELECT okl_opp_seq.NEXTVAL
4046 INTO l_seq_next
4047 FROM DUAL;
4048
4049 -- mark records for processing
4050 FOR chk_update_header_csr_rec IN chk_update_header_csr
4051 LOOP
4052 INSERT INTO okl_parallel_processes
4053 (object_type,
4054 object_value,
4055 assigned_process,
4056 process_status,
4057 start_date,
4058 khr_id,
4059 trx_id
4060 )
4061 VALUES ('CONT_TERM',
4062 chk_update_header_csr_rec.contract_number,
4063 TO_CHAR (l_seq_next),
4064 'PENDING_ASSIGNMENT',
4065 SYSDATE,
4066 chk_update_header_csr_rec.khr_id,
4067 chk_update_header_csr_rec.trx_id
4068 );
4069
4070 COMMIT;
4071 l_data_found := TRUE;
4072 END LOOP;
4073
4074 IF l_data_found THEN
4075 FOR chk_data_volume_csr_rec IN chk_data_volume_csr (l_seq_next)
4076 LOOP
4077 l_int_counter := l_int_counter + 1;
4078
4079 IF l_init_loop THEN -- initialize minimum and maximum lines
4080 l_init_loop := FALSE;
4081 l_max_lines := chk_data_volume_csr_rec.line_count;
4082 END IF;
4083
4084 l_contract_tab (l_int_counter).contract_number :=
4085 chk_data_volume_csr_rec.contract_number;
4086 l_contract_tab (l_int_counter).line_count :=
4087 chk_data_volume_csr_rec.line_count;
4088
4089 IF chk_data_volume_csr_rec.line_count > l_max_lines THEN
4090 l_max_lines := chk_data_volume_csr_rec.line_count;
4091 END IF;
4092 END LOOP;
4093
4094 -- reset, ready for use again
4095 l_init_loop := TRUE;
4096
4097 IF l_int_counter = 0 THEN
4098 write_to_log ('No Data Found for criteria passed ');
4099 END IF;
4100
4101 -- find the maximum line count from the original table and delete it
4102 -- put this as the first element of the new sorted table
4103 l_sort_int_counter := 0;
4104
4105 FOR i IN 1 .. l_int_counter
4106 LOOP
4107 IF l_contract_tab (i).line_count = l_max_lines THEN
4108 l_sort_int_counter := l_sort_int_counter + 1;
4109 l_sort_tab1 (l_sort_int_counter).contract_number :=
4110 l_contract_tab (i).contract_number;
4111 l_sort_tab1 (l_sort_int_counter).line_count :=
4112 l_contract_tab (i).line_count;
4113 l_contract_tab.DELETE (i);
4114 END IF;
4115 END LOOP;
4116
4117 -- start sorting
4118 IF l_contract_tab.FIRST IS NOT NULL THEN
4119 FOR i IN 1 .. l_contract_tab.COUNT
4120 LOOP
4121 -- find the next highest value in original table
4122 FOR i IN 1 .. l_contract_tab.LAST
4123 LOOP
4124 IF l_init_loop THEN
4125 IF l_contract_tab.EXISTS (i) THEN
4126 l_next_highest_val := l_contract_tab (i).line_count;
4127 l_init_loop := FALSE;
4128 END IF;
4129 END IF;
4130
4131 IF l_contract_tab.EXISTS (i)
4132 AND l_contract_tab (i).line_count > l_next_highest_val THEN
4133 l_next_highest_val := l_contract_tab (i).line_count;
4134 END IF;
4135 END LOOP;
4136
4137 -- reset flag, ready for use again
4138 l_init_loop := TRUE;
4139
4140 -- continue populating sort table in order
4141 FOR i IN 1 .. l_contract_tab.LAST
4142 LOOP
4143 IF l_contract_tab.EXISTS (i)
4144 AND l_contract_tab (i).line_count = l_next_highest_val THEN
4145 l_sort_int_counter := l_sort_int_counter + 1;
4146 l_sort_tab1 (l_sort_int_counter).contract_number :=
4147 l_contract_tab (i).contract_number;
4148 l_sort_tab1 (l_sort_int_counter).line_count :=
4149 l_contract_tab (i).line_count;
4150 l_contract_tab.DELETE (i);
4151 END IF;
4152 END LOOP;
4153
4154 EXIT WHEN l_contract_tab.LAST IS NULL;
4155 END LOOP;
4156 END IF; -- end sorting
4157
4158 -- begin processing load for workers
4159 FOR i IN 1 .. p_num_processes
4160 LOOP -- put all workers into a table
4161 l_worker_load (i).worker_number := i;
4162 l_worker_load (i).worker_load := 0; -- initialize load with zero
4163 END LOOP;
4164
4165 IF p_num_processes > 0 THEN
4166 l_lightest_worker := 1;
4167
4168 IF l_sort_tab1.COUNT > 0 THEN -- rmunjulu
4169 -- loop through the sorted table and ensure each contract has a worker
4170 FOR i IN 1 .. l_sort_tab1.COUNT
4171 LOOP
4172 l_sort_tab1 (i).worker_number := l_lightest_worker;
4173
4174 -- put current contract into the lightest worker
4175 IF l_worker_load.EXISTS (l_lightest_worker) THEN
4176 l_worker_load (l_lightest_worker).worker_load :=
4177 l_worker_load (l_lightest_worker).worker_load
4178 + l_sort_tab1 (i).line_count;
4179 END IF;
4180
4181 -- default the lighest load with the first element as a starting point
4182 IF l_worker_load.EXISTS (1) THEN
4183 l_lightest_load := l_worker_load (1).worker_load;
4184 l_lightest_worker := l_worker_load (1).worker_number;
4185
4186 -- logic to find lightest load
4187 FOR i IN 1 .. l_worker_load.COUNT
4188 LOOP
4189 IF (l_worker_load (i).worker_load = 0)
4190 OR (l_worker_load (i).worker_load < l_lightest_load
4191 ) THEN
4192 l_lightest_load := l_worker_load (i).worker_load;
4193 l_lightest_worker :=
4194 l_worker_load (i).worker_number;
4195 END IF;
4196 END LOOP;
4197 END IF;
4198 END LOOP;
4199 END IF; -- rmunjulu
4200 END IF;
4201
4202 l_sort_int_counter := 0;
4203
4204 IF l_worker_load.COUNT > 0 THEN -- rmunjulu
4205 FOR j IN 1 .. l_worker_load.LAST
4206 LOOP
4207 IF l_sort_tab1.COUNT > 0 THEN -- rmunjulu
4208 FOR i IN 1 .. l_sort_tab1.LAST
4209 LOOP
4210 IF l_sort_tab1.EXISTS (i)
4211 AND (l_sort_tab1 (i).worker_number =
4212 l_worker_load (j).worker_number
4213 ) THEN
4214 UPDATE okl_parallel_processes
4215 SET assigned_process =
4216 l_seq_next
4217 || '-'
4218 || l_sort_tab1 (i).worker_number,
4219 volume = l_sort_tab1 (i).line_count,
4220 process_status = 'ASSIGNED'
4221 WHERE object_type = 'CONT_TERM'
4222 AND object_value = l_sort_tab1 (i).contract_number
4223 AND process_status = 'PENDING_ASSIGNMENT';
4224
4225 COMMIT;
4226 l_sort_tab1.DELETE (i);
4227 END IF;
4228 END LOOP;
4229 END IF; -- rmunjulu
4230 END LOOP;
4231 END IF; -- rmunjulu
4232
4233 -- SPAWN THE CHILD CONCURRENT PROGRAM WHICH WILL DO THE ACTUAL EXPIRATION PROCESSING
4234 FOR j IN l_worker_load.FIRST .. l_worker_load.LAST
4235 LOOP
4236 -- Do not spawn a worker if theres no data to process
4237 -- This occurs if more workers are requested and the
4238 -- distribution of data does not utilize them all
4239 IF l_worker_load (j).worker_load > 0 THEN
4240 fnd_request.set_org_id (mo_global.get_current_org_id);
4241 --MOAC- Concurrent request
4242 request_id :=
4243 fnd_request.submit_request (application => 'OKL',
4244 program => 'OKL_AM_CHILD_TERM',
4245 sub_request => FALSE,
4246 argument1 => l_seq_next
4247 || '-'
4248 || j
4249 );
4250 write_to_log ( 'Launching Process '
4251 || l_seq_next
4252 || '-'
4253 || j
4254 || ' with Request ID '
4255 || request_id
4256 );
4257
4258 IF (request_id = 0) THEN
4259 errbuf := fnd_message.get;
4260 retcode := 2;
4261 END IF;
4262 END IF;
4263 END LOOP;
4264
4265 -- clean up
4266 -- Delete records from in chk_update_header_csr that were unassigned
4267 DELETE okl_parallel_processes
4268 WHERE process_status = 'PENDING_ASSIGNMENT'
4269 AND assigned_process = TO_CHAR (l_seq_next);
4270
4271 COMMIT;
4272 ELSE
4273 write_to_log
4274 ('No workers assigned due to no data found for prcocesing');
4275 END IF; -- l_data_found
4276
4277 IF (is_debug_procedure_on) THEN
4278 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
4279 END IF;
4280 EXCEPTION
4281 WHEN OTHERS THEN
4282 IF (is_debug_exception_on) THEN
4283 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
4284 || sqlcode || ' , SQLERRM : ' || sqlerrm);
4285 END IF;
4286 write_to_log ('Unhandled Exception ' || SQLERRM);
4287 END process_spawner;
4288 END okl_am_btch_exp_lease_loan_pvt;