[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.16.12010000.2 2008/10/03 18:37:21 rkuttiya 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 -- akrangan added for debug feature start
2077 l_module_name VARCHAR2 (500) := g_module_name || 'fnd_output';
2078 is_debug_exception_on BOOLEAN
2079 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
2080 is_debug_procedure_on BOOLEAN
2081 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
2082 is_debug_statement_on BOOLEAN
2083 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
2084 -- akrangan added for debug feature end
2085 BEGIN
2086 IF (is_debug_procedure_on) THEN
2087 okl_debug_pub.log_debug (g_level_procedure,
2088 l_module_name,
2089 'Begin(+)'
2090 );
2091 END IF;
2092
2093 IF (is_debug_statement_on) THEN
2094 okl_debug_pub.log_debug (g_level_statement,
2095 l_module_name,
2096 'p_contract_id =' || p_contract_id
2097 );
2098 END IF;
2099
2100 x_return_status := okl_api.g_ret_sts_success;
2101
2102 SELECT SYSDATE
2103 INTO db_sysdate
2104 FROM DUAL;
2105
2106 -- Check if value passed for contract number
2107 IF p_contract_id IS NOT NULL AND p_contract_id <> okl_api.g_miss_num THEN
2108 FOR single_k_rec IN single_k_csr (p_contract_id)
2109 LOOP
2110 -- set the term_rec_type of terminate_contract
2111 lp_term_rec.p_contract_id := p_contract_id;
2112 lp_term_rec.p_contract_number := single_k_rec.contract_number;
2113 lp_term_rec.p_termination_date := db_sysdate;
2114 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
2115
2116 -- rmunjulu 4016497
2117 -- check if contract expired
2118 OPEN exp_chr_csr (p_contract_id, db_sysdate);
2119
2120 FETCH exp_chr_csr
2121 INTO exp_chr_rec;
2122
2123 IF exp_chr_csr%FOUND THEN
2124 l_exp_chr_yn := 'Y';
2125 END IF;
2126
2127 CLOSE exp_chr_csr;
2128
2129 -- rmunjulu 4016497
2130 -- check if contract recycled
2131 OPEN recy_chr_csr (p_contract_id, db_sysdate);
2132
2133 FETCH recy_chr_csr
2134 INTO recy_chr_rec;
2135
2136 IF recy_chr_csr%FOUND THEN
2137 l_recy_chr_yn := 'Y';
2138 END IF;
2139
2140 CLOSE recy_chr_csr;
2141
2142 -- rmunjulu 4016497
2143 IF NVL (l_exp_chr_yn, 'N') = 'Y' THEN -- Contract has expired
2144 -- rmunjulu LOANS_ENHANCEMENTS
2145 OPEN k_details_csr (lp_term_rec.p_contract_id);
2146
2147 FETCH k_details_csr
2148 INTO l_deal_type;
2149
2150 CLOSE k_details_csr;
2151
2152 -- rmunjulu LOANS_ENHANCEMENTS
2153 IF l_deal_type LIKE 'LOAN%' THEN
2154 IF (is_debug_statement_on) THEN
2155 okl_debug_pub.log_debug
2156 (g_level_statement,
2157 l_module_name,
2158 'Before OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
2159 );
2160 okl_debug_pub.log_debug (g_level_statement,
2161 l_module_name,
2162 'In param, p_contract_id: '
2163 || lp_term_rec.p_contract_id
2164 );
2165 END IF;
2166
2167 -- get principal balance of loan contract
2168 l_prin_bal :=
2169 okl_variable_int_util_pvt.get_principal_bal
2170 (x_return_status => l_return_status,
2171 p_khr_id => lp_term_rec.p_contract_id,
2172 p_kle_id => NULL,
2173 p_date => SYSDATE
2174 );
2175
2176 IF (is_debug_statement_on) THEN
2177 okl_debug_pub.log_debug
2178 (g_level_statement,
2179 l_module_name,
2180 'After OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
2181 );
2182 okl_debug_pub.log_debug (g_level_statement,
2183 l_module_name,
2184 'l_prin_bal: ' || l_prin_bal
2185 );
2186 okl_debug_pub.log_debug (g_level_statement,
2187 l_module_name,
2188 'l_return_status: '
2189 || l_return_status
2190 );
2191 END IF;
2192 END IF;
2193
2194 -- rmunjulu LOANS_ENHANCEMENTS
2195 IF NVL (l_prin_bal, 0) <= 0 THEN
2196 -- rmunjulu 5058848 check for prin bal <= 0
2197 -- set the out tbl
2198 lx_term_tbl (i).p_contract_id := lp_term_rec.p_contract_id;
2199 lx_term_tbl (i).p_contract_number :=
2200 lp_term_rec.p_contract_number;
2201
2202 IF (is_debug_statement_on) THEN
2203 okl_debug_pub.log_debug (g_level_statement,
2204 l_module_name,
2205 'Before process_termination'
2206 );
2207 okl_debug_pub.log_debug
2208 (g_level_statement,
2209 l_module_name,
2210 'In param, p_term_rec.p_contract_id: '
2211 || lp_term_rec.p_contract_id
2212 );
2213 okl_debug_pub.log_debug
2214 (g_level_statement,
2215 l_module_name,
2216 'In param, p_term_rec.p_contract_number: '
2217 || lp_term_rec.p_contract_number
2218 );
2219 END IF;
2220
2221 process_termination (p_api_version => p_api_version,
2222 p_init_msg_list => okl_api.g_true,
2223 x_return_status => l_return_status,
2224 x_msg_count => x_msg_count,
2225 x_msg_data => x_msg_data,
2226 p_term_rec => lp_term_rec,
2227 x_tcnv_rec => lx_tcnv_rec,
2228 x_term_rec => lx_term_rec
2229 );
2230
2231 IF (is_debug_statement_on) THEN
2232 okl_debug_pub.log_debug (g_level_statement,
2233 l_module_name,
2234 'After process_termination '
2235 );
2236 okl_debug_pub.log_debug (g_level_statement,
2237 l_module_name,
2238 'l_return_status: '
2239 || l_return_status
2240 );
2241 END IF;
2242
2243 IF (is_debug_statement_on) THEN
2244 okl_debug_pub.log_debug (g_level_statement,
2245 l_module_name,
2246 'Before check_contract'
2247 );
2248 okl_debug_pub.log_debug (g_level_statement,
2249 l_module_name,
2250 'In param, p_contract_id: '
2251 || lp_term_rec.p_contract_id
2252 );
2253 END IF;
2254
2255 -- RMUNJULU 2730738 For proper output file
2256 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2257 x_start_date => l_start_date,
2258 x_end_date => l_end_date,
2259 x_status => l_status,
2260 x_control_flag => l_control_flag
2261 );
2262
2263 IF (is_debug_statement_on) THEN
2264 okl_debug_pub.log_debug (g_level_statement,
2265 l_module_name,
2266 'After check_contract '
2267 );
2268 okl_debug_pub.log_debug (g_level_statement,
2269 l_module_name,
2270 'x_start_date: ' || l_start_date
2271 );
2272 okl_debug_pub.log_debug (g_level_statement,
2273 l_module_name,
2274 'x_end_date: ' || l_end_date
2275 );
2276 okl_debug_pub.log_debug (g_level_statement,
2277 l_module_name,
2278 'x_status: ' || l_status
2279 );
2280 okl_debug_pub.log_debug (g_level_statement,
2281 l_module_name,
2282 'x_control_flag: '
2283 || l_control_flag
2284 );
2285 okl_debug_pub.log_debug (g_level_statement,
2286 l_module_name,
2287 'l_return_status: '
2288 || l_return_status
2289 );
2290 END IF;
2291
2292 -- RMUNJULU 2730738 For proper output file
2293 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2294 p_chr_number => lp_term_rec.p_contract_number,
2295 p_start_date => l_start_date,
2296 p_end_date => l_end_date,
2297 p_status => l_status,
2298 p_exp_recy => 'EXP',
2299 p_control_flag => l_control_flag
2300 );
2301
2302 IF (is_debug_statement_on) THEN
2303 okl_debug_pub.log_debug (g_level_statement,
2304 l_module_name,
2305 'After fnd_output '
2306 );
2307 okl_debug_pub.log_debug (g_level_statement,
2308 l_module_name,
2309 'l_return_status: '
2310 || l_return_status
2311 );
2312 END IF;
2313
2314 -- RMUNJULU 2730738 For proper output file
2315 reset_asset_msg_tbl;
2316 -- set the out tbl termination date
2317 lx_term_tbl (i) := lx_term_rec;
2318
2319 -- update the overall status only if l_return_status is not success
2320 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
2321 l_overall_status := l_return_status;
2322 END IF;
2323
2324 -- rmunjulu 4016497
2325 create_report;
2326 ELSE
2327 IF (is_debug_statement_on) THEN
2328 okl_debug_pub.log_debug (g_level_statement,
2329 l_module_name,
2330 'Before check_contract'
2331 );
2332 okl_debug_pub.log_debug (g_level_statement,
2333 l_module_name,
2334 'In param, p_contract_id: '
2335 || lp_term_rec.p_contract_id
2336 );
2337 END IF;
2338
2339 -- RMUNJULU 2730738 For proper output file
2340 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2341 x_start_date => l_start_date,
2342 x_end_date => l_end_date,
2343 x_status => l_status,
2344 x_control_flag => l_control_flag
2345 );
2346
2347 IF (is_debug_statement_on) THEN
2348 okl_debug_pub.log_debug (g_level_statement,
2349 l_module_name,
2350 'After check_contract '
2351 );
2352 okl_debug_pub.log_debug (g_level_statement,
2353 l_module_name,
2354 'x_start_date: ' || l_start_date
2355 );
2356 okl_debug_pub.log_debug (g_level_statement,
2357 l_module_name,
2358 'x_end_date: ' || l_end_date
2359 );
2360 okl_debug_pub.log_debug (g_level_statement,
2361 l_module_name,
2362 'x_status: ' || l_status
2363 );
2364 okl_debug_pub.log_debug (g_level_statement,
2365 l_module_name,
2366 'x_control_flag: '
2367 || l_control_flag
2368 );
2369 okl_debug_pub.log_debug (g_level_statement,
2370 l_module_name,
2371 'l_return_status: '
2372 || l_return_status
2373 );
2374 END IF;
2375
2376 IF (is_debug_statement_on) THEN
2377 okl_debug_pub.log_debug (g_level_statement,
2378 l_module_name,
2379 'Before fnd_output '
2380 );
2381 okl_debug_pub.log_debug
2382 (g_level_statement,
2383 l_module_name,
2384 'In param, p_term_rec.p_contract_id: '
2385 || lp_term_rec.p_contract_id
2386 );
2387 okl_debug_pub.log_debug
2388 (g_level_statement,
2389 l_module_name,
2390 'In param, p_term_rec.p_contract_number: '
2391 || lp_term_rec.p_contract_number
2392 );
2393 END IF;
2394
2395 -- RMUNJULU 2730738 For proper output file
2396 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2397 p_chr_number => lp_term_rec.p_contract_number,
2398 p_start_date => l_start_date,
2399 p_end_date => l_end_date,
2400 p_status => l_status,
2401 p_exp_recy => 'EXP',
2402 p_control_flag => 'FAIL'
2403 );
2404
2405 IF (is_debug_statement_on) THEN
2406 okl_debug_pub.log_debug (g_level_statement,
2407 l_module_name,
2408 'After fnd_output '
2409 );
2410 okl_debug_pub.log_debug (g_level_statement,
2411 l_module_name,
2412 'l_return_status: '
2413 || l_return_status
2414 );
2415 END IF;
2416
2417 -- RMUNJULU 2730738 For proper output file
2418 reset_asset_msg_tbl;
2419 g_prin_bal_zero := 'Y';
2420 create_report;
2421 END IF;
2422 ELSIF NVL (l_recy_chr_yn, 'N') = 'Y' THEN
2423 -- Contract has been recycled
2424 IF (is_debug_statement_on) THEN
2425 okl_debug_pub.log_debug (g_level_statement,
2426 l_module_name,
2427 'Before process_termination'
2428 );
2429 okl_debug_pub.log_debug
2430 (g_level_statement,
2431 l_module_name,
2432 'In param, p_term_rec.p_contract_id: '
2433 || lp_term_rec.p_contract_id
2434 );
2435 okl_debug_pub.log_debug
2436 (g_level_statement,
2437 l_module_name,
2438 'In param, p_term_rec.p_contract_number: '
2439 || lp_term_rec.p_contract_number
2440 );
2441 END IF;
2442
2443 process_termination (p_api_version => p_api_version,
2444 p_init_msg_list => okl_api.g_true,
2445 x_return_status => l_return_status,
2446 x_msg_count => x_msg_count,
2447 x_msg_data => x_msg_data,
2448 p_term_rec => lp_term_rec,
2449 p_trn_id => recy_chr_rec.trn_id,
2450 x_tcnv_rec => lx_tcnv_rec,
2451 x_term_rec => lx_term_rec
2452 );
2453
2454 IF (is_debug_statement_on) THEN
2455 okl_debug_pub.log_debug (g_level_statement,
2456 l_module_name,
2457 'After process_termination '
2458 );
2459 okl_debug_pub.log_debug (g_level_statement,
2460 l_module_name,
2461 'l_return_status: '
2462 || l_return_status
2463 );
2464 END IF;
2465
2466 IF (is_debug_statement_on) THEN
2467 okl_debug_pub.log_debug (g_level_statement,
2468 l_module_name,
2469 'Before check_contract'
2470 );
2471 okl_debug_pub.log_debug (g_level_statement,
2472 l_module_name,
2473 'In param, p_contract_id: '
2474 || lp_term_rec.p_contract_id
2475 );
2476 END IF;
2477
2478 -- RMUNJULU 2730738 For proper output file
2479 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2480 x_start_date => l_start_date,
2481 x_end_date => l_end_date,
2482 x_status => l_status,
2483 x_control_flag => l_control_flag
2484 );
2485
2486 IF (is_debug_statement_on) THEN
2487 okl_debug_pub.log_debug (g_level_statement,
2488 l_module_name,
2489 'After check_contract '
2490 );
2491 okl_debug_pub.log_debug (g_level_statement,
2492 l_module_name,
2493 'x_start_date: ' || l_start_date
2494 );
2495 okl_debug_pub.log_debug (g_level_statement,
2496 l_module_name,
2497 'x_end_date: ' || l_end_date
2498 );
2499 okl_debug_pub.log_debug (g_level_statement,
2500 l_module_name,
2501 'x_status: ' || l_status
2502 );
2503 okl_debug_pub.log_debug (g_level_statement,
2504 l_module_name,
2505 'x_control_flag: '
2506 || l_control_flag
2507 );
2508 okl_debug_pub.log_debug (g_level_statement,
2509 l_module_name,
2510 'l_return_status: '
2511 || l_return_status
2512 );
2513 END IF;
2514
2515 IF (is_debug_statement_on) THEN
2516 okl_debug_pub.log_debug (g_level_statement,
2517 l_module_name,
2518 'Before fnd_output '
2519 );
2520 okl_debug_pub.log_debug
2521 (g_level_statement,
2522 l_module_name,
2523 'In param, p_term_rec.p_contract_id: '
2524 || lp_term_rec.p_contract_id
2525 );
2526 okl_debug_pub.log_debug
2527 (g_level_statement,
2528 l_module_name,
2529 'In param, p_term_rec.p_contract_number: '
2530 || lp_term_rec.p_contract_number
2531 );
2532 END IF;
2533
2534 -- RMUNJULU 2730738 For proper output file
2535 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2536 p_chr_number => lp_term_rec.p_contract_number,
2537 p_start_date => l_start_date,
2538 p_end_date => l_end_date,
2539 p_status => l_status,
2540 p_exp_recy => 'RECY',
2541 p_control_flag => l_control_flag
2542 );
2543
2544 IF (is_debug_statement_on) THEN
2545 okl_debug_pub.log_debug (g_level_statement,
2546 l_module_name,
2547 'After fnd_output '
2548 );
2549 okl_debug_pub.log_debug (g_level_statement,
2550 l_module_name,
2551 'l_return_status: '
2552 || l_return_status
2553 );
2554 END IF;
2555
2556 -- RMUNJULU 2730738 For proper output file
2557 reset_asset_msg_tbl;
2558 -- set the out tbl termination date
2559 lx_term_tbl (i) := lx_term_rec;
2560
2561 -- update the overall status only if l_return_status is not success
2562 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
2563 l_overall_status := l_return_status;
2564 END IF;
2565
2566 -- rmunjulu 4016497
2567 create_report;
2568 ELSE
2569 -- Either the contract is invalid or contract has not reached its end date
2570 -- or there is no recycled termination transaction for the contract.
2571
2572 -- Set the message
2573 fnd_message.set_name ('OKL', 'OKL_AM_BTCH_ERR');
2574 -- Get the message
2575 l_message := fnd_message.get;
2576
2577 IF (is_debug_statement_on) THEN
2578 okl_debug_pub.log_debug (g_level_statement,
2579 l_module_name,
2580 'Before check_contract'
2581 );
2582 okl_debug_pub.log_debug (g_level_statement,
2583 l_module_name,
2584 'In param, p_contract_id: '
2585 || lp_term_rec.p_contract_id
2586 );
2587 END IF;
2588
2589 -- RMUNJULU 2730738 For proper output file
2590 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2591 x_start_date => l_start_date,
2592 x_end_date => l_end_date,
2593 x_status => l_status,
2594 x_control_flag => l_control_flag
2595 );
2596
2597 IF (is_debug_statement_on) THEN
2598 okl_debug_pub.log_debug (g_level_statement,
2599 l_module_name,
2600 'After check_contract '
2601 );
2602 okl_debug_pub.log_debug (g_level_statement,
2603 l_module_name,
2604 'x_start_date: ' || l_start_date
2605 );
2606 okl_debug_pub.log_debug (g_level_statement,
2607 l_module_name,
2608 'x_end_date: ' || l_end_date
2609 );
2610 okl_debug_pub.log_debug (g_level_statement,
2611 l_module_name,
2612 'x_status: ' || l_status
2613 );
2614 okl_debug_pub.log_debug (g_level_statement,
2615 l_module_name,
2616 'x_control_flag: '
2617 || l_control_flag
2618 );
2619 okl_debug_pub.log_debug (g_level_statement,
2620 l_module_name,
2621 'l_return_status: '
2622 || l_return_status
2623 );
2624 END IF;
2625
2626 IF (is_debug_statement_on) THEN
2627 okl_debug_pub.log_debug (g_level_statement,
2628 l_module_name,
2629 'Before fnd_output '
2630 );
2631 okl_debug_pub.log_debug
2632 (g_level_statement,
2633 l_module_name,
2634 'In param, p_term_rec.p_contract_id: '
2635 || lp_term_rec.p_contract_id
2636 );
2637 okl_debug_pub.log_debug
2638 (g_level_statement,
2639 l_module_name,
2640 'In param, p_term_rec.p_contract_number: '
2641 || lp_term_rec.p_contract_number
2642 );
2643 END IF;
2644
2645 -- RMUNJULU 2730738 For proper output file
2646 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2647 p_chr_number => lp_term_rec.p_contract_number,
2648 p_start_date => l_start_date,
2649 p_end_date => l_end_date,
2650 p_status => l_status,
2651 p_exp_recy => 'RECY',
2652 p_control_flag => NULL
2653 ); -- Pass NULL so that it goes to ELSE of this API
2654
2655 IF (is_debug_statement_on) THEN
2656 okl_debug_pub.log_debug (g_level_statement,
2657 l_module_name,
2658 'After fnd_output '
2659 );
2660 okl_debug_pub.log_debug (g_level_statement,
2661 l_module_name,
2662 'l_return_status: '
2663 || l_return_status
2664 );
2665 END IF;
2666
2667 -- rmunjulu 4016497
2668 create_report (p_source => 'NOTNULL', p_message => l_message);
2669 END IF;
2670
2671 -- increment i
2672 i := i + 1;
2673 END LOOP;
2674 ELSE -- no value passed for p contract number
2675 /* RMUNJULU 2730738
2676 fnd_file.put_line(fnd_file.log, '');
2677 fnd_file.put_line(fnd_file.output, '');
2678 fnd_file.put_line(fnd_file.log, 'Processing the expired contracts.');
2679 fnd_file.put_line(fnd_file.output, 'Processing the expired contracts.');
2680 fnd_file.put_line(fnd_file.log, '');
2681 fnd_file.put_line(fnd_file.output, '');
2682 */ -- for leases/loans whose end date is less than today and which are not already
2683 -- terminated call the lease_loan_terminate api
2684 FOR ter_cnt_rec IN ter_cnt_csr (db_sysdate)
2685 LOOP
2686 -- set the term_rec_type of terminate_contract
2687 lp_term_rec.p_contract_id := ter_cnt_rec.ID;
2688 lp_term_rec.p_contract_number := ter_cnt_rec.contract_number;
2689 lp_term_rec.p_termination_date := db_sysdate;
2690 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
2691
2692 -- rmunjulu LOANS_ENHANCEMENTSS
2693 OPEN k_details_csr (lp_term_rec.p_contract_id);
2694
2695 FETCH k_details_csr
2696 INTO l_deal_type;
2697
2698 CLOSE k_details_csr;
2699
2700 -- rmunjulu LOANS_ENHANCEMENTS
2701 IF l_deal_type LIKE 'LOAN%' THEN
2702 -- get principal balance of loan contract
2703 l_prin_bal :=
2704 okl_variable_int_util_pvt.get_principal_bal
2705 (x_return_status => l_return_status,
2706 p_khr_id => lp_term_rec.p_contract_id,
2707 p_kle_id => NULL,
2708 p_date => SYSDATE
2709 );
2710 END IF;
2711
2712 -- rmunjulu LOANS_ENHANCEMENTS
2713 IF NVL (l_prin_bal, 0) <= 0 THEN
2714 -- rmunjulu 5058848 check for prin bal <= 0
2715 -- set the out tbl
2716 lx_term_tbl (i).p_contract_id := lp_term_rec.p_contract_id;
2717 lx_term_tbl (i).p_contract_number :=
2718 lp_term_rec.p_contract_number;
2719
2720 IF (is_debug_statement_on) THEN
2721 okl_debug_pub.log_debug (g_level_statement,
2722 l_module_name,
2723 'Before process_termination'
2724 );
2725 okl_debug_pub.log_debug
2726 (g_level_statement,
2727 l_module_name,
2728 'In param, p_term_rec.p_contract_id: '
2729 || lp_term_rec.p_contract_id
2730 );
2731 okl_debug_pub.log_debug
2732 (g_level_statement,
2733 l_module_name,
2734 'In param, p_term_rec.p_contract_number: '
2735 || lp_term_rec.p_contract_number
2736 );
2737 END IF;
2738
2739 process_termination (p_api_version => p_api_version,
2740 p_init_msg_list => okl_api.g_true,
2741 x_return_status => l_return_status,
2742 x_msg_count => x_msg_count,
2743 x_msg_data => x_msg_data,
2744 p_term_rec => lp_term_rec,
2745 x_tcnv_rec => lx_tcnv_rec,
2746 x_term_rec => lx_term_rec
2747 );
2748
2749 IF (is_debug_statement_on) THEN
2750 okl_debug_pub.log_debug (g_level_statement,
2751 l_module_name,
2752 'After process_termination '
2753 );
2754 okl_debug_pub.log_debug (g_level_statement,
2755 l_module_name,
2756 'l_return_status: '
2757 || l_return_status
2758 );
2759 END IF;
2760
2761 -- RMUNJULU 2730738 For proper output file
2762 IF (is_debug_statement_on) THEN
2763 okl_debug_pub.log_debug (g_level_statement,
2764 l_module_name,
2765 'Before check_contract'
2766 );
2767 okl_debug_pub.log_debug (g_level_statement,
2768 l_module_name,
2769 'In param, p_contract_id: '
2770 || lp_term_rec.p_contract_id
2771 );
2772 END IF;
2773
2774 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2775 x_start_date => l_start_date,
2776 x_end_date => l_end_date,
2777 x_status => l_status,
2778 x_control_flag => l_control_flag
2779 );
2780
2781 IF (is_debug_statement_on) THEN
2782 okl_debug_pub.log_debug (g_level_statement,
2783 l_module_name,
2784 'After check_contract '
2785 );
2786 okl_debug_pub.log_debug (g_level_statement,
2787 l_module_name,
2788 'x_start_date: ' || l_start_date
2789 );
2790 okl_debug_pub.log_debug (g_level_statement,
2791 l_module_name,
2792 'x_end_date: ' || l_end_date
2793 );
2794 okl_debug_pub.log_debug (g_level_statement,
2795 l_module_name,
2796 'x_status: ' || l_status
2797 );
2798 okl_debug_pub.log_debug (g_level_statement,
2799 l_module_name,
2800 'x_control_flag: '
2801 || l_control_flag
2802 );
2803 okl_debug_pub.log_debug (g_level_statement,
2804 l_module_name,
2805 'l_return_status: '
2806 || l_return_status
2807 );
2808 END IF;
2809
2810 IF (is_debug_statement_on) THEN
2811 okl_debug_pub.log_debug (g_level_statement,
2812 l_module_name,
2813 'Before fnd_output '
2814 );
2815 okl_debug_pub.log_debug
2816 (g_level_statement,
2817 l_module_name,
2818 'In param, p_term_rec.p_contract_id: '
2819 || lp_term_rec.p_contract_id
2820 );
2821 okl_debug_pub.log_debug
2822 (g_level_statement,
2823 l_module_name,
2824 'In param, p_term_rec.p_contract_number: '
2825 || lp_term_rec.p_contract_number
2826 );
2827 END IF;
2828
2829 -- RMUNJULU 2730738 For proper output file
2830 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2831 p_chr_number => lp_term_rec.p_contract_number,
2832 p_start_date => l_start_date,
2833 p_end_date => l_end_date,
2834 p_status => l_status,
2835 p_exp_recy => 'EXP',
2836 p_control_flag => l_control_flag
2837 );
2838
2839 IF (is_debug_statement_on) THEN
2840 okl_debug_pub.log_debug (g_level_statement,
2841 l_module_name,
2842 'After fnd_output '
2843 );
2844 okl_debug_pub.log_debug (g_level_statement,
2845 l_module_name,
2846 'l_return_status: '
2847 || l_return_status
2848 );
2849 END IF;
2850
2851 -- RMUNJULU 2730738 For proper output file
2852 reset_asset_msg_tbl;
2853 -- set the out tbl termination date
2854 lx_term_tbl (i) := lx_term_rec;
2855
2856 -- update the overall status only if l_return_status is not success
2857 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
2858 l_overall_status := l_return_status;
2859 END IF;
2860
2861 -- increment i
2862 i := i + 1;
2863 END IF;
2864 END LOOP;
2865
2866 /* RMUNJULU 2730738
2867 fnd_file.put_line(fnd_file.log, '');
2868 fnd_file.put_line(fnd_file.output, '');
2869 fnd_file.put_line(fnd_file.log, 'Processing the recycled termination transactions.');
2870 fnd_file.put_line(fnd_file.output, 'Processing the recycled termination transactions.');
2871 fnd_file.put_line(fnd_file.log, '');
2872 fnd_file.put_line(fnd_file.output, '');
2873 */ -- for leases/loans which have been recycled call the
2874 -- lease_loan_termination api
2875 FOR ter_cnt_recy_rec IN ter_cnt_recy_csr (db_sysdate)
2876 LOOP
2877 -- set the term_rec_type of terminate_contract
2878 lp_term_rec.p_contract_id := ter_cnt_recy_rec.ID;
2879 lp_term_rec.p_contract_number := ter_cnt_recy_rec.contract_number;
2880 lp_term_rec.p_termination_date := db_sysdate;
2881 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
2882 -- set the out tbl
2883 lx_term_tbl (i + j).p_contract_id := lp_term_rec.p_contract_id;
2884 lx_term_tbl (i + j).p_contract_number :=
2885 lp_term_rec.p_contract_number;
2886
2887 IF (is_debug_statement_on) THEN
2888 okl_debug_pub.log_debug (g_level_statement,
2889 l_module_name,
2890 'Before process_termination'
2891 );
2892 okl_debug_pub.log_debug
2893 (g_level_statement,
2894 l_module_name,
2895 'In param, p_term_rec.p_contract_id: '
2896 || lp_term_rec.p_contract_id
2897 );
2898 okl_debug_pub.log_debug
2899 (g_level_statement,
2900 l_module_name,
2901 'In param, p_term_rec.p_contract_number: '
2902 || lp_term_rec.p_contract_number
2903 );
2904 END IF;
2905
2906 process_termination (p_api_version => p_api_version,
2907 p_init_msg_list => okl_api.g_true,
2908 x_return_status => l_return_status,
2909 x_msg_count => x_msg_count,
2910 x_msg_data => x_msg_data,
2911 p_term_rec => lp_term_rec,
2912 p_trn_id => ter_cnt_recy_rec.trn_id,
2913 --RMUNJULU 17-NOV-02 Bug # 2484327 Added
2914 x_tcnv_rec => lx_tcnv_rec,
2915 x_term_rec => lx_term_rec
2916 );
2917
2918 IF (is_debug_statement_on) THEN
2919 okl_debug_pub.log_debug (g_level_statement,
2920 l_module_name,
2921 'After process_termination '
2922 );
2923 okl_debug_pub.log_debug (g_level_statement,
2924 l_module_name,
2925 'l_return_status: ' || l_return_status
2926 );
2927 END IF;
2928
2929 IF (is_debug_statement_on) THEN
2930 okl_debug_pub.log_debug (g_level_statement,
2931 l_module_name,
2932 'Before check_contract'
2933 );
2934 okl_debug_pub.log_debug (g_level_statement,
2935 l_module_name,
2936 'In param, p_contract_id: '
2937 || lp_term_rec.p_contract_id
2938 );
2939 END IF;
2940
2941 -- RMUNJULU 2730738 For proper output file
2942 check_contract (p_chr_id => lp_term_rec.p_contract_id,
2943 x_start_date => l_start_date,
2944 x_end_date => l_end_date,
2945 x_status => l_status,
2946 x_control_flag => l_control_flag
2947 );
2948
2949 IF (is_debug_statement_on) THEN
2950 okl_debug_pub.log_debug (g_level_statement,
2951 l_module_name,
2952 'After check_contract '
2953 );
2954 okl_debug_pub.log_debug (g_level_statement,
2955 l_module_name,
2956 'x_start_date: ' || l_start_date
2957 );
2958 okl_debug_pub.log_debug (g_level_statement,
2959 l_module_name,
2960 'x_end_date: ' || l_end_date
2961 );
2962 okl_debug_pub.log_debug (g_level_statement,
2963 l_module_name,
2964 'x_status: ' || l_status
2965 );
2966 okl_debug_pub.log_debug (g_level_statement,
2967 l_module_name,
2968 'x_control_flag: ' || l_control_flag
2969 );
2970 okl_debug_pub.log_debug (g_level_statement,
2971 l_module_name,
2972 'l_return_status: ' || l_return_status
2973 );
2974 END IF;
2975
2976 IF (is_debug_statement_on) THEN
2977 okl_debug_pub.log_debug (g_level_statement,
2978 l_module_name,
2979 'Before fnd_output '
2980 );
2981 okl_debug_pub.log_debug
2982 (g_level_statement,
2983 l_module_name,
2984 'In param, p_term_rec.p_contract_id: '
2985 || lp_term_rec.p_contract_id
2986 );
2987 okl_debug_pub.log_debug
2988 (g_level_statement,
2989 l_module_name,
2990 'In param, p_term_rec.p_contract_number: '
2991 || lp_term_rec.p_contract_number
2992 );
2993 END IF;
2994
2995 -- RMUNJULU 2730738 For proper output file
2996 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
2997 p_chr_number => lp_term_rec.p_contract_number,
2998 p_start_date => l_start_date,
2999 p_end_date => l_end_date,
3000 p_status => l_status,
3001 p_exp_recy => 'RECY',
3002 p_control_flag => l_control_flag
3003 );
3004
3005 IF (is_debug_statement_on) THEN
3006 okl_debug_pub.log_debug (g_level_statement,
3007 l_module_name,
3008 'After fnd_output '
3009 );
3010 okl_debug_pub.log_debug (g_level_statement,
3011 l_module_name,
3012 'l_return_status: ' || l_return_status
3013 );
3014 END IF;
3015
3016 -- RMUNJULU 2730738 For proper output file
3017 reset_asset_msg_tbl;
3018 -- set the out tbl termination date
3019 lx_term_tbl (i + j) := lx_term_rec;
3020
3021 -- update the overall status only if l_return_status is not success
3022 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
3023 l_overall_status := l_return_status;
3024 END IF;
3025
3026 -- increment i
3027 j := j + 1;
3028 END LOOP;
3029 END IF;
3030
3031 -- set the out parameters
3032 x_term_tbl := lx_term_tbl;
3033 x_return_status := l_overall_status;
3034
3035 IF (is_debug_procedure_on) THEN
3036 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3037 END IF;
3038 EXCEPTION
3039 WHEN okl_api.g_exception_error THEN
3040 IF (is_debug_exception_on) THEN
3041 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'G_EXCEPTION_ERROR :' );
3042 END IF;
3043 x_return_status := okl_api.g_ret_sts_error;
3044 WHEN okl_api.g_exception_unexpected_error THEN
3045 IF (is_debug_exception_on) THEN
3046 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR' );
3047 END IF;
3048 x_return_status := okl_api.g_ret_sts_unexp_error;
3049 WHEN OTHERS THEN
3050 IF (is_debug_exception_on) THEN
3051 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
3052 || sqlcode || ' , SQLERRM : ' || sqlerrm);
3053 END IF;
3054 okl_api.set_message (p_app_name => g_app_name_1,
3055 p_msg_name => g_unexpected_error,
3056 p_token1 => g_sqlcode_token,
3057 p_token1_value => SQLCODE,
3058 p_token2 => g_sqlerrm_token,
3059 p_token2_value => SQLERRM
3060 );
3061 x_return_status := okl_api.g_ret_sts_unexp_error;
3062 END batch_expire_lease_loan;
3063
3064 -- Start of comments
3065 --
3066 -- Procedure Name : concurrent_expire_lease_loan
3067 -- Description : This procedure calls batch_expire_lease_loan procedure, used
3068 -- by concurrent program
3069 -- Business Rules :
3070 -- Parameters :
3071 -- Version : 1.0
3072 -- History : rmunjulu 4016497 Changed to not check validity (it will be checked in batch_expire_lease_loan)
3073 -- End of comments
3074 PROCEDURE concurrent_expire_lease_loan (
3075 errbuf OUT NOCOPY VARCHAR2,
3076 retcode OUT NOCOPY VARCHAR2,
3077 p_api_version IN NUMBER,
3078 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
3079 p_contract_id IN NUMBER DEFAULT okl_api.g_miss_num
3080 ) IS
3081 l_return_status VARCHAR2 (1);
3082 l_msg_count NUMBER;
3083 l_msg_data VARCHAR2 (2000);
3084 l_mesg VARCHAR2 (4000);
3085 l_mesg_len NUMBER;
3086 l_term_tbl term_tbl_type;
3087 lx_contract_status VARCHAR2 (200);
3088 lx_error_rec okl_api.error_rec_type;
3089 l_msg_idx INTEGER := fnd_msg_pub.g_first;
3090 -- akrangan added for debug feature start
3091 l_module_name VARCHAR2 (500)
3092 := g_module_name || 'concurrent_expire_lease_loan';
3093 is_debug_exception_on BOOLEAN
3094 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3095 is_debug_procedure_on BOOLEAN
3096 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3097 is_debug_statement_on BOOLEAN
3098 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3099 -- akrangan added for debug feature end
3100 BEGIN
3101 IF (is_debug_procedure_on) THEN
3102 okl_debug_pub.log_debug (g_level_procedure,
3103 l_module_name,
3104 'Begin(+)'
3105 );
3106 END IF;
3107
3108 IF (is_debug_statement_on) THEN
3109 okl_debug_pub.log_debug (g_level_statement,
3110 l_module_name,
3111 'p_contract_id =' || p_contract_id
3112 );
3113 END IF;
3114
3115 -- Check if a single contract termination request
3116 IF p_contract_id IS NOT NULL AND p_contract_id <> okl_api.g_miss_num THEN
3117 /* -- rmunjulu BUG 4016497
3118 -- Check the validity of the contract
3119 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
3120 p_api_version => p_api_version,
3121 p_init_msg_list => p_init_msg_list,
3122 x_return_status => l_return_status,
3123 x_msg_count => l_msg_count,
3124 x_msg_data => l_msg_data,
3125 p_contract_id => p_contract_id,
3126 p_control_flag => 'BATCH_PROCESS_CHR',
3127 x_contract_status => lx_contract_status);
3128 */
3129
3130 /*
3131 -- Check if contract valid or not
3132 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN -- Contract validity failed
3133
3134 fnd_file.put_line(fnd_file.log, 'Processing termination request from concurrent manager.');
3135 fnd_file.put_line(fnd_file.output, 'Processing termination request from concurrent manager.');
3136
3137 fnd_file.put_line(fnd_file.log, '');
3138 fnd_file.put_line(fnd_file.output, '');
3139
3140 LOOP
3141
3142 fnd_msg_pub.get(
3143 p_msg_index => l_msg_idx,
3144 p_encoded => FND_API.G_FALSE,
3145 p_data => lx_error_rec.msg_data,
3146 p_msg_index_out => lx_error_rec.msg_count);
3147
3148 IF (lx_error_rec.msg_count IS NOT NULL) THEN
3149
3150 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
3151 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
3152
3153 END IF;
3154
3155 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
3156 OR (lx_error_rec.msg_count IS NULL));
3157
3158 l_msg_idx := FND_MSG_PUB.G_NEXT;
3159 END LOOP;
3160
3161 fnd_file.put_line(fnd_file.log, '');
3162 fnd_file.put_line(fnd_file.output, '');
3163 fnd_file.put_line(fnd_file.log, 'Termination of contract failed');
3164 fnd_file.put_line(fnd_file.output, 'Termination of contract failed');
3165
3166 ELSE -- Contract valid
3167 */
3168 /* RMUNJULU 2730738
3169 fnd_file.put_line(fnd_file.log, 'Processing termination request from concurrent manager.');
3170 fnd_file.put_line(fnd_file.output, 'Processing termination request from concurrent manager.');
3171
3172 fnd_file.put_line(fnd_file.log, '');
3173 fnd_file.put_line(fnd_file.output, '');
3174 */
3175 -- Terminate the contract
3176 IF (is_debug_statement_on) THEN
3177 okl_debug_pub.log_debug (g_level_statement,
3178 l_module_name,
3179 'before batch_expire_lease_loan'
3180 );
3181 okl_debug_pub.log_debug (g_level_statement,
3182 l_module_name,
3183 'p_contract_id =' || p_contract_id
3184 );
3185 END IF;
3186
3187 batch_expire_lease_loan (p_api_version => p_api_version,
3188 p_init_msg_list => p_init_msg_list,
3189 x_return_status => l_return_status,
3190 x_msg_count => l_msg_count,
3191 x_msg_data => l_msg_data,
3192 p_contract_id => p_contract_id,
3193 x_term_tbl => l_term_tbl
3194 );
3195
3196 IF (is_debug_statement_on) THEN
3197 okl_debug_pub.log_debug (g_level_statement,
3198 l_module_name,
3199 'after batch_expire_lease_loan'
3200 );
3201 okl_debug_pub.log_debug (g_level_statement,
3202 l_module_name,
3203 'l_return_status ='
3204 || l_return_status
3205 );
3206 END IF;
3207 -- rmunjulu 4016497 Report will be generated inside ABOVE API
3208 /*
3209 -- RMUNJULU 2730738
3210 create_report;
3211
3212 END IF;
3213 */
3214 ELSE -- No contract passed, so scheduled request
3215 /* RMUNJULU 2730738
3216 fnd_file.put_line(fnd_file.log, 'Processing termination request from concurrent manager.');
3217 fnd_file.put_line(fnd_file.output, 'Processing termination request from concurrent manager.');
3218
3219 fnd_file.put_line(fnd_file.log, '');
3220 fnd_file.put_line(fnd_file.output, '');
3221 */
3222 IF (is_debug_statement_on) THEN
3223 okl_debug_pub.log_debug (g_level_statement,
3224 l_module_name,
3225 'before batch_expire_lease_loan'
3226 );
3227 okl_debug_pub.log_debug (g_level_statement,
3228 l_module_name,
3229 'p_contract_id =' || p_contract_id
3230 );
3231 END IF;
3232
3233 -- Terminate the contract
3234 batch_expire_lease_loan (p_api_version => p_api_version,
3235 p_init_msg_list => p_init_msg_list,
3236 x_return_status => l_return_status,
3237 x_msg_count => l_msg_count,
3238 x_msg_data => l_msg_data,
3239 p_contract_id => p_contract_id,
3240 x_term_tbl => l_term_tbl
3241 );
3242
3243 IF (is_debug_statement_on) THEN
3244 okl_debug_pub.log_debug (g_level_statement,
3245 l_module_name,
3246 'after batch_expire_lease_loan'
3247 );
3248 okl_debug_pub.log_debug (g_level_statement,
3249 l_module_name,
3250 'l_return_status ='
3251 || l_return_status
3252 );
3253 END IF;
3254
3255 -- RMUNJULU 2730738
3256 create_report;
3257 END IF;
3258
3259 IF (is_debug_procedure_on) THEN
3260 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3261 END IF;
3262 END concurrent_expire_lease_loan;
3263
3264 PROCEDURE write_to_log (p_message IN VARCHAR2) IS
3265 -- akrangan added for debug feature start
3266 l_module_name VARCHAR2 (500)
3267 := g_module_name || 'write_to_log';
3268 is_debug_exception_on BOOLEAN
3269 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3270 is_debug_procedure_on BOOLEAN
3271 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3272 is_debug_statement_on BOOLEAN
3273 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3274 -- akrangan added for debug feature end
3275 BEGIN
3276 IF (is_debug_procedure_on) THEN
3277 okl_debug_pub.log_debug (g_level_procedure,
3278 l_module_name,
3279 'Begin(+)'
3280 );
3281 END IF;
3282
3283 IF (is_debug_statement_on) THEN
3284 okl_debug_pub.log_debug (g_level_statement,
3285 l_module_name,
3286 'p_message =' || p_message
3287 );
3288 END IF;
3289
3290 -- dbms_output.put_line(p_message);
3291 fnd_file.put_line (fnd_file.output, p_message);
3292
3293 IF (is_debug_procedure_on) THEN
3294 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3295 END IF;
3296 END write_to_log;
3297
3298 -- RMUNJULU PERF
3299 PROCEDURE batch_expire_lease_loan (
3300 p_api_version IN NUMBER,
3301 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
3302 x_return_status OUT NOCOPY VARCHAR2,
3303 x_msg_count OUT NOCOPY NUMBER,
3304 x_msg_data OUT NOCOPY VARCHAR2,
3305 p_assigned_processes IN VARCHAR2,
3306 x_term_tbl OUT NOCOPY term_tbl_type
3307 ) IS
3308 -- get contracts assigned to current process
3309 CURSOR get_contracts_of_process_csr (p_assigned_process IN VARCHAR2) IS
3310 SELECT opp.khr_id khr_id,
3311 opp.object_value contract_number,
3312 opp.trx_id trx_id
3313 FROM okl_parallel_processes opp
3314 WHERE opp.assigned_process = p_assigned_process;
3315
3316 -- rmunjulu LOANS_ENHANCEMENTS
3317 CURSOR k_details_csr (p_khr_id IN NUMBER) IS
3318 SELECT deal_type
3319 FROM okl_k_headers
3320 WHERE ID = p_khr_id;
3321
3322 lp_term_rec term_rec_type;
3323 lx_term_rec term_rec_type;
3324 lx_term_tbl term_tbl_type;
3325 lp_tcnv_rec tcnv_rec_type;
3326 lx_tcnv_rec tcnv_rec_type;
3327 db_sysdate DATE;
3328 i NUMBER := 1;
3329 j NUMBER := 1;
3330 l_chr_id NUMBER;
3331 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
3332 l_api_name VARCHAR2 (200) := 'batch_expire_lease_loan';
3333 l_overall_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
3334 l_start_date DATE;
3335 l_end_date DATE;
3336 l_status VARCHAR2 (200);
3337 l_control_flag VARCHAR2 (10);
3338 l_exp_chr_yn VARCHAR2 (3);
3339 l_recy_chr_yn VARCHAR2 (3);
3340 l_message VARCHAR2 (30000);
3341 -- rmunjulu LOANS_ENHANCEMENTS
3342 l_prin_bal NUMBER := 0;
3343 l_deal_type VARCHAR2 (300);
3344 -- akrangan added for debug feature start
3345 l_module_name VARCHAR2 (500) := g_module_name || 'fnd_output';
3346 is_debug_exception_on BOOLEAN
3347 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3348 is_debug_procedure_on BOOLEAN
3349 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3350 is_debug_statement_on BOOLEAN
3351 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3352 -- akrangan added for debug feature end
3353 BEGIN
3354 FOR get_contracts_of_process_rec IN
3355 get_contracts_of_process_csr (p_assigned_processes)
3356 LOOP
3357 -- set the term_rec_type of terminate_contract
3358 lp_term_rec.p_contract_id := get_contracts_of_process_rec.khr_id;
3359 lp_term_rec.p_contract_number :=
3360 get_contracts_of_process_rec.contract_number;
3361 lp_term_rec.p_termination_date := SYSDATE;
3362 lp_term_rec.p_control_flag := 'BATCH_PROCESS';
3363
3364 -- rmunjulu LOANS_ENHANCEMENTS
3365 OPEN k_details_csr (lp_term_rec.p_contract_id);
3366
3367 FETCH k_details_csr
3368 INTO l_deal_type;
3369
3370 CLOSE k_details_csr;
3371
3372 -- rmunjulu LOANS_ENHANCEMENTS
3373 IF l_deal_type LIKE 'LOAN%' THEN
3374 IF (is_debug_statement_on) THEN
3375 okl_debug_pub.log_debug
3376 (g_level_statement,
3377 l_module_name,
3378 'Before OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
3379 );
3380 okl_debug_pub.log_debug (g_level_statement,
3381 l_module_name,
3382 'In param, p_contract_id: '
3383 || lp_term_rec.p_contract_id
3384 );
3385 END IF;
3386
3387 -- get principal balance of loan contract
3388 l_prin_bal :=
3389 okl_variable_int_util_pvt.get_principal_bal
3390 (x_return_status => l_return_status,
3391 p_khr_id => lp_term_rec.p_contract_id,
3392 p_kle_id => NULL,
3393 p_date => SYSDATE
3394 );
3395
3396 IF (is_debug_statement_on) THEN
3397 okl_debug_pub.log_debug
3398 (g_level_statement,
3399 l_module_name,
3400 'After OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal '
3401 );
3402 okl_debug_pub.log_debug (g_level_statement,
3403 l_module_name,
3404 'l_prin_bal: ' || l_prin_bal
3405 );
3406 okl_debug_pub.log_debug (g_level_statement,
3407 l_module_name,
3408 'l_return_status: ' || l_return_status
3409 );
3410 END IF;
3411 END IF;
3412
3413 -- rmunjulu LOANS_ENHANCEMENTS
3414 IF NVL (l_prin_bal, 0) <= 0 THEN
3415 --rmunjulu 5058848 check for prin bal <= 0
3416 -- set the out tbl
3417 lx_term_tbl (i).p_contract_id := lp_term_rec.p_contract_id;
3418 lx_term_tbl (i).p_contract_number :=
3419 lp_term_rec.p_contract_number;
3420
3421 IF (is_debug_statement_on) THEN
3422 okl_debug_pub.log_debug (g_level_statement,
3423 l_module_name,
3424 'Before process_termination'
3425 );
3426 okl_debug_pub.log_debug
3427 (g_level_statement,
3428 l_module_name,
3429 'In param, p_term_rec.p_contract_id: '
3430 || lp_term_rec.p_contract_id
3431 );
3432 okl_debug_pub.log_debug
3433 (g_level_statement,
3434 l_module_name,
3435 'In param, p_term_rec.p_contract_number: '
3436 || lp_term_rec.p_contract_number
3437 );
3438 okl_debug_pub.log_debug (g_level_statement,
3439 l_module_name,
3440 'In param, p_trn_id: '
3441 || get_contracts_of_process_rec.trx_id
3442 );
3443 END IF;
3444
3445 process_termination
3446 (p_api_version => p_api_version,
3447 p_init_msg_list => okl_api.g_true,
3448 x_return_status => l_return_status,
3449 x_msg_count => x_msg_count,
3450 x_msg_data => x_msg_data,
3451 p_term_rec => lp_term_rec,
3452 p_trn_id => get_contracts_of_process_rec.trx_id,
3453 -- rmunjulu added to pass trx_id if exists
3454 x_tcnv_rec => lx_tcnv_rec,
3455 x_term_rec => lx_term_rec
3456 );
3457
3458 IF (is_debug_statement_on) THEN
3459 okl_debug_pub.log_debug (g_level_statement,
3460 l_module_name,
3461 'After process_termination '
3462 );
3463 okl_debug_pub.log_debug (g_level_statement,
3464 l_module_name,
3465 'l_return_status: ' || l_return_status
3466 );
3467 END IF;
3468
3469 IF (is_debug_statement_on) THEN
3470 okl_debug_pub.log_debug (g_level_statement,
3471 l_module_name,
3472 'Before check_contract'
3473 );
3474 okl_debug_pub.log_debug (g_level_statement,
3475 l_module_name,
3476 'In param, p_contract_id: '
3477 || lp_term_rec.p_contract_id
3478 );
3479 END IF;
3480
3481 -- RMUNJULU 2730738 For proper output file
3482 check_contract (p_chr_id => lp_term_rec.p_contract_id,
3483 x_start_date => l_start_date,
3484 x_end_date => l_end_date,
3485 x_status => l_status,
3486 x_control_flag => l_control_flag
3487 );
3488
3489 IF (is_debug_statement_on) THEN
3490 okl_debug_pub.log_debug (g_level_statement,
3491 l_module_name,
3492 'After check_contract '
3493 );
3494 okl_debug_pub.log_debug (g_level_statement,
3495 l_module_name,
3496 'x_start_date: ' || l_start_date
3497 );
3498 okl_debug_pub.log_debug (g_level_statement,
3499 l_module_name,
3500 'x_end_date: ' || l_end_date
3501 );
3502 okl_debug_pub.log_debug (g_level_statement,
3503 l_module_name,
3504 'x_status: ' || l_status
3505 );
3506 okl_debug_pub.log_debug (g_level_statement,
3507 l_module_name,
3508 'x_control_flag: ' || l_control_flag
3509 );
3510 okl_debug_pub.log_debug (g_level_statement,
3511 l_module_name,
3512 'l_return_status: ' || l_return_status
3513 );
3514 END IF;
3515
3516 -- rmunjulu for proper output need to tell if recy or exp
3517 IF get_contracts_of_process_rec.trx_id IS NOT NULL
3518 AND get_contracts_of_process_rec.trx_id <> okl_api.g_miss_num THEN
3519 IF (is_debug_statement_on) THEN
3520 okl_debug_pub.log_debug (g_level_statement,
3521 l_module_name,
3522 'before fnd_output '
3523 );
3524 END IF;
3525
3526 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
3527 p_chr_number => lp_term_rec.p_contract_number,
3528 p_start_date => l_start_date,
3529 p_end_date => l_end_date,
3530 p_status => l_status,
3531 p_exp_recy => 'RECY',
3532 p_control_flag => l_control_flag
3533 );
3534
3535 IF (is_debug_statement_on) THEN
3536 okl_debug_pub.log_debug (g_level_statement,
3537 l_module_name,
3538 'After fnd_output '
3539 );
3540 okl_debug_pub.log_debug (g_level_statement,
3541 l_module_name,
3542 'l_return_status: '
3543 || l_return_status
3544 );
3545 END IF;
3546 ELSE
3547 IF (is_debug_statement_on) THEN
3548 okl_debug_pub.log_debug (g_level_statement,
3549 l_module_name,
3550 'before fnd_output '
3551 );
3552 END IF;
3553
3554 fnd_output (p_chr_id => lp_term_rec.p_contract_id,
3555 p_chr_number => lp_term_rec.p_contract_number,
3556 p_start_date => l_start_date,
3557 p_end_date => l_end_date,
3558 p_status => l_status,
3559 p_exp_recy => 'EXP',
3560 p_control_flag => l_control_flag
3561 );
3562
3563 IF (is_debug_statement_on) THEN
3564 okl_debug_pub.log_debug (g_level_statement,
3565 l_module_name,
3566 'After fnd_output '
3567 );
3568 okl_debug_pub.log_debug (g_level_statement,
3569 l_module_name,
3570 'l_return_status: '
3571 || l_return_status
3572 );
3573 END IF;
3574 END IF;
3575
3576 -- RMUNJULU 2730738 For proper output file
3577 reset_asset_msg_tbl;
3578 -- set the out tbl termination date
3579 lx_term_tbl (i) := lx_term_rec;
3580
3581 -- update the overall status only if l_return_status is not success
3582 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
3583 l_overall_status := l_return_status;
3584 END IF;
3585
3586 -- increment i
3587 i := i + 1;
3588 END IF;
3589 END LOOP;
3590
3591 x_term_tbl := lx_term_tbl;
3592
3593 IF (is_debug_procedure_on) THEN
3594 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3595 END IF;
3596 END batch_expire_lease_loan;
3597
3598 -- RMUNJULU PERF
3599 -- THIS PROCESS IS CALLED FROM CHILD PROCESS WHICH WAS SPAWNED FROM MAIN
3600 -- PROCESSESOR
3601 -- GETS THE CONTRACTS ASSIGNED TO PROCESSOR AND LAUNCHES TERMINATION FOR THOSE
3602 PROCEDURE child_process (
3603 errbuf OUT NOCOPY VARCHAR2,
3604 retcode OUT NOCOPY NUMBER,
3605 p_assigned_processes IN VARCHAR2 --,
3606 --p_api_version IN NUMBER,
3607 --p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3608 ) IS
3609 l_return_status VARCHAR2 (1);
3610 l_msg_count NUMBER;
3611 l_msg_data VARCHAR2 (2000);
3612 l_term_tbl term_tbl_type;
3613 l_api_version NUMBER := 1;
3614 -- akrangan added for debug feature start
3615 l_module_name VARCHAR2 (500)
3616 := g_module_name || 'write_to_log';
3617 is_debug_exception_on BOOLEAN
3618 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3619 is_debug_procedure_on BOOLEAN
3620 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3621 is_debug_statement_on BOOLEAN
3622 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3623 -- akrangan added for debug feature end
3624 BEGIN
3625 IF (is_debug_procedure_on) THEN
3626 okl_debug_pub.log_debug (g_level_procedure,
3627 l_module_name,
3628 'Begin(+)'
3629 );
3630 END IF;
3631
3632 IF (is_debug_statement_on) THEN
3633 okl_debug_pub.log_debug (g_level_statement,
3634 l_module_name,
3635 'before batch_expire_lease_loan '
3636 );
3637 END IF;
3638
3639 -- Terminate the contract -- call the new batch_expire procedure
3640 batch_expire_lease_loan (p_api_version => l_api_version,
3641 p_init_msg_list => okl_api.g_true,
3642 x_return_status => l_return_status,
3643 x_msg_count => l_msg_count,
3644 x_msg_data => l_msg_data,
3645 p_assigned_processes => p_assigned_processes,
3646 x_term_tbl => l_term_tbl
3647 );
3648
3649 IF (is_debug_statement_on) THEN
3650 okl_debug_pub.log_debug (g_level_statement,
3651 l_module_name,
3652 'after batch_expire_lease_loan '
3653 );
3654 okl_debug_pub.log_debug (g_level_statement,
3655 l_module_name,
3656 'l_return_status = ' || l_return_status
3657 );
3658 END IF;
3659
3660 -- create report
3661 create_report;
3662
3663 -- Do clean up of parallel processes
3664 DELETE FROM okl_parallel_processes
3665 WHERE assigned_process = p_assigned_processes;
3666
3667 IF (is_debug_procedure_on) THEN
3668 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
3669 END IF;
3670 END child_process;
3671
3672 -- RMUNJULU PERF
3673 -- MASTER PROGRAM WHICH DOES PARALLEL LOAD BALANCE AND SPAWNS CHILD PROCESSES
3674 -- THIS PROCEDURE IS CALLED FROM MASTER CONCURRENT PROGRAM
3675 PROCEDURE process_spawner (
3676 errbuf OUT NOCOPY VARCHAR2,
3677 retcode OUT NOCOPY NUMBER,
3678 p_num_processes IN NUMBER,
3679 p_term_date IN VARCHAR2
3680 ) IS
3681 request_id NUMBER := 0;
3682
3683 -- GETS CONTRACTS
3684 CURSOR chk_update_header_csr IS
3685 SELECT contract_number,
3686 khr_id,
3687 trx_id
3688 FROM (SELECT khr.contract_number contract_number,
3689 khr.ID khr_id,
3690 NULL trx_id
3691 FROM okc_k_headers_b khr
3692 WHERE TRUNC (khr.end_date) < TRUNC (SYSDATE)
3693 AND NVL (khr.sts_code, '?') IN ('BOOKED')
3694 AND khr.scs_code IN ('LEASE', 'LOAN')
3695 -- rmunjulu --start -- added the following or else same record picked twice
3696 AND khr.ID NOT IN (
3697 -- Contracts which have unprocessed transactions
3698 SELECT NVL (tcn.khr_id, -9999) khr_id
3699 FROM okl_trx_contracts tcn
3700 WHERE NVL (tcn.tcn_type, '?') IN
3701 ('TMT', 'ALT', 'EVG')
3702 -- akrangan bug 5354501 fix added 'EVG'
3703 AND tcn.tmt_status_code NOT IN
3704 ('CANCELED', 'PROCESSED')
3705 --akrangan changed for sla tmt_status_Code cr
3706 --rkuttiya added for 12.1.1 Multi GAAP
3707 AND tcn.representation_type = 'PRIMARY'
3708 --
3709 AND tcn.khr_id = khr.ID) -- rmunjulu PERF
3710 AND khr.ID NOT IN (
3711 -- Contracts which have accepted quotes with no transactions
3712 SELECT NVL (qte.khr_id, -9999) khr_id
3713 FROM okl_trx_quotes_v qte
3714 WHERE NVL (qte.accepted_yn, 'N') = 'Y'
3715 AND NVL (qte.consolidated_yn, 'N') = 'N'
3716 AND qte.khr_id = khr.ID -- rmunjulu PERF
3717 AND qte.ID NOT IN (
3718 SELECT NVL (tcn.qte_id, -9999) qte_id
3719 FROM okl_trx_contracts tcn
3720 WHERE NVL (tcn.tcn_type, '?') IN
3721 ('TMT', 'ALT', 'EVG')
3722 -- akrangan bug 5354501 fix added 'EVG'
3723 --rkuttiya added for 12.1.1 Multi GAAP
3724 AND tcn.representation_type = 'PRIMARY'
3725 --
3726 AND tcn.qte_id = qte.ID))
3727 -- rmunjulu PERF
3728 -- rmunjulu -- end
3729 UNION
3730 SELECT k.contract_number,
3731 k.ID khr_id,
3732 t.ID trx_id
3733 FROM okc_k_headers_b k, okl_trx_contracts t
3734 WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
3735 AND NVL (t.tmt_status_code, '?') NOT IN
3736 ('PROCESSED', 'CANCELED')
3737 --akrangan changed for sla tmt_status_Code cr
3738 AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
3739 -- akrangan bug 5354501 fix added 'EVG'
3740 AND k.scs_code IN ('LEASE', 'LOAN')
3741 --rkuttiya added for 12.1.1 Multi GAAP
3742 AND t.representation_type = 'PRIMARY'
3743 --
3744 AND k.ID = t.khr_id)
3745 WHERE NOT EXISTS (
3746 SELECT '1'
3747 FROM okl_parallel_processes opp
3748 WHERE contract_number = opp.object_value
3749 AND opp.object_type = 'CONT_TERM'
3750 AND opp.process_status IN
3751 ('PENDING_ASSIGNMENT', 'ASSIGNED'));
3752
3753 -- GET DATA VOLUME BASED ON WHICH WILL DECIDE WHICH PARALLEL PROCESS TO USE.
3754 --Bug # 6174484 fixed for SQL Performance ssdehpa start
3755 CURSOR chk_data_volume_csr (p_seq_next VARCHAR2) IS
3756 /* SELECT opp.object_value contract_number , count(KLE.id) line_count
3757 FROM OKC_K_LINES_B KLE,
3758 okl_parallel_processes opp
3759 WHERE opp.khr_id = KLE.dnz_chr_id
3760 AND KLE.sts_code = ('BOOKED')
3761 AND opp.khr_id NOT IN(
3762 SELECT NVL(TCN.khr_id,-9999) khr_id
3763 FROM OKL_TRX_CONTRACTS TCN
3764 WHERE NVL(TCN.tcn_type,'?') IN ('TMT','ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
3765 AND TCN.tmt_status_code NOT IN ('CANCELED','PROCESSED')--akrangan changed for sla tmt_status_Code cr
3766 AND TCN.khr_id = opp.khr_id)
3767 AND opp.khr_id NOT IN (
3768 SELECT NVL(QTE.khr_id,-9999) khr_id
3769 FROM OKL_TRX_QUOTES_V QTE
3770 WHERE NVL(QTE.accepted_yn,'N') = 'Y'
3771 AND NVL(QTE.consolidated_yn,'N') = 'N'
3772 AND QTE.khr_id = opp.khr_id
3773 AND QTE.id NOT IN (
3774 SELECT NVL(TCN.qte_id,-9999) qte_id
3775 FROM OKL_TRX_CONTRACTS TCN
3776 WHERE NVL(TCN.tcn_type,'?') IN ('TMT','ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
3777 AND TCN.qte_id = QTE.id))
3778 AND opp.object_type = 'CONT_TERM'
3779 AND opp.assigned_process = p_seq_next
3780 GROUP BY opp.object_value
3781 UNION
3782 SELECT opp.object_value contract_number, count(KLE.id) line_count
3783 FROM okl_parallel_processes opp,
3784 OKL_TRX_CONTRACTS T,
3785 OKC_K_LINES_B KLE
3786 WHERE NVL(T.tmt_recycle_yn,'?') = 'Y'
3787 AND opp.khr_id = KLE.dnz_chr_id
3788 AND KLE.sts_code = 'BOOKED'
3789 AND NVL(T.tmt_status_code,'?') NOT IN('PROCESSED', 'CANCELED')--akrangan changed for sla tmt_status_Code cr
3790 AND NVL(T.tcn_type,'?') IN( 'TMT', 'ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
3791 AND opp.khr_id = T.khr_id
3792 AND opp.object_type = 'CONT_TERM'
3793 AND opp.assigned_process = p_seq_next
3794 GROUP BY opp.object_value; */
3795 SELECT opp.object_value contract_number,
3796 COUNT (kle.ID) line_count
3797 FROM okc_k_lines_b kle, okl_parallel_processes opp
3798 WHERE opp.khr_id = kle.dnz_chr_id
3799 AND kle.sts_code = ('BOOKED')
3800 AND opp.khr_id NOT IN (
3801 SELECT NVL (tcn.khr_id, -9999) khr_id
3802 FROM okl_trx_contracts_all tcn
3803 WHERE tcn.tcn_type IN ('TMT', 'ALT', 'EVG')
3804 -- akrangan bug 5354501 fix added 'EVG'
3805 AND tcn.tmt_status_code NOT IN
3806 ('CANCELED', 'PROCESSED')
3807 --akrangan changed for sla tmt_status_Code cr
3808 --rkuttiya added for 12.1.1 Multi GAAP
3809 AND tcn.representation_Type = 'PRIMARY'
3810 --
3811 AND tcn.khr_id = opp.khr_id)
3812 AND opp.khr_id NOT IN (
3813 SELECT NVL (qte.khr_id, -9999) khr_id
3814 FROM okl_trx_quotes_b qte
3815 WHERE NVL (qte.accepted_yn, 'N') = 'Y'
3816 AND NVL (qte.consolidated_yn, 'N') = 'N'
3817 AND qte.khr_id = opp.khr_id
3818 AND qte.ID NOT IN (
3819 SELECT NVL (tcn.qte_id, -9999) qte_id
3820 FROM okl_trx_contracts_all tcn
3821 WHERE tcn.tcn_type IN ('TMT', 'ALT', 'EVG')
3822 -- akrangan bug 5354501 fix added 'EVG'
3823 --rkuttiya added for 12.1.1 Multi GAAP
3824 AND tcn.representation_type = 'PRIMARY'
3825 --
3826 AND tcn.qte_id = qte.ID))
3827 AND opp.object_type = 'CONT_TERM'
3828 AND opp.assigned_process = p_seq_next
3829 GROUP BY opp.object_value
3830 UNION
3831 SELECT opp.object_value contract_number,
3832 COUNT (kle.ID) line_count
3833 FROM okl_parallel_processes opp,
3834 okl_trx_contracts t,
3835 okc_k_lines_b kle
3836 WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
3837 AND opp.khr_id = kle.dnz_chr_id
3838 AND kle.sts_code = 'BOOKED'
3839 AND NVL (t.tmt_status_code, '?') NOT IN
3840 ('PROCESSED', 'CANCELED')
3841 --akrangan changed for sla tmt_status_Code cr
3842 AND t.tcn_type IN ('TMT', 'ALT', 'EVG')
3843 -- akrangan bug 5354501 fix added 'EVG'
3844 AND opp.khr_id = t.khr_id
3845 --rkuttiya added for 12.1.1. Multi GAAP
3846 AND t.representation_type = 'PRIMARY'
3847 --
3848 AND opp.object_type = 'CONT_TERM'
3849 AND opp.assigned_process = p_seq_next
3850 GROUP BY opp.object_value;
3851
3852 --Bug # 6174484 fixed for SQL Performance ssdehpa end
3853 TYPE l_contract_rec IS RECORD (
3854 batch_number VARCHAR2 (60),
3855 contract_number VARCHAR2 (60),
3856 line_count NUMBER,
3857 worker_number NUMBER,
3858 khr_id NUMBER,
3859 trx_id NUMBER
3860 );
3861
3862 TYPE contract_tab IS TABLE OF l_contract_rec
3863 INDEX BY PLS_INTEGER;
3864
3865 TYPE worker_load_rec IS RECORD (
3866 worker_number NUMBER,
3867 worker_load NUMBER
3868 );
3869
3870 TYPE worker_load_tab IS TABLE OF worker_load_rec
3871 INDEX BY PLS_INTEGER;
3872
3873 TYPE contract_list IS RECORD (
3874 contract_number VARCHAR2 (60)
3875 );
3876
3877 TYPE contract_list_tab IS TABLE OF contract_list
3878 INDEX BY PLS_INTEGER;
3879
3880 l_contract_list contract_list_tab;
3881 l_worker_load worker_load_tab;
3882 l_contract_tab contract_tab;
3883 l_sort_tab1 contract_tab;
3884 l_temp_tab contract_tab;
3885 l_int_counter INTEGER;
3886 l_max_lines NUMBER;
3887 l_init_loop BOOLEAN := TRUE;
3888 l_sort_int_counter INTEGER;
3889 l_next_highest_val NUMBER;
3890 l_lightest_worker NUMBER;
3891 l_lightest_load NUMBER;
3892 l_seq_next NUMBER;
3893 l_data_found BOOLEAN := FALSE;
3894 lp_term_date DATE;
3895 l_return_status VARCHAR2 (1);
3896 l_msg_count NUMBER;
3897 l_msg_data VARCHAR2 (2000);
3898 l_term_tbl term_tbl_type;
3899 -- akrangan added for debug feature start
3900 l_module_name VARCHAR2 (500)
3901 := g_module_name || 'write_to_log';
3902 is_debug_exception_on BOOLEAN
3903 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
3904 is_debug_procedure_on BOOLEAN
3905 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
3906 is_debug_statement_on BOOLEAN
3907 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
3908 -- akrangan added for debug feature end
3909 BEGIN
3910 IF (is_debug_procedure_on) THEN
3911 okl_debug_pub.log_debug (g_level_procedure,
3912 l_module_name,
3913 'Begin(+)'
3914 );
3915 END IF;
3916
3917 lp_term_date := fnd_date.canonical_to_date (p_term_date);
3918
3919 IF NVL (p_num_processes, 0) = 0 THEN
3920 write_to_log ('Number of parallel processors should be provided.');
3921 RETURN;
3922 ELSIF NVL (p_num_processes, 0) = 1 THEN
3923 --write_to_log('Single worker requested');
3924 -- no need to run algorithm, directly call single thread program here
3925 IF (is_debug_statement_on) THEN
3926 okl_debug_pub.log_debug (g_level_statement,
3927 l_module_name,
3928 'before batch_expire_lease_loan '
3929 );
3930 END IF;
3931
3932 -- Terminate the contract
3933 batch_expire_lease_loan (p_api_version => 1,
3934 p_init_msg_list => 'T',
3935 x_return_status => l_return_status,
3936 x_msg_count => l_msg_count,
3937 x_msg_data => l_msg_data,
3938 p_contract_id => NULL,
3939 x_term_tbl => l_term_tbl
3940 );
3941
3942 IF (is_debug_statement_on) THEN
3943 okl_debug_pub.log_debug (g_level_statement,
3944 l_module_name,
3945 'after batch_expire_lease_loan '
3946 );
3947 okl_debug_pub.log_debug (g_level_statement,
3948 l_module_name,
3949 'l_return_status = ' || l_return_status
3950 );
3951 END IF;
3952
3953 -- RMUNJULU
3954 create_report;
3955 RETURN;
3956 END IF;
3957
3958 l_int_counter := 0;
3959 l_max_lines := 0;
3960
3961 -- Select sequence for marking processes
3962 SELECT okl_opp_seq.NEXTVAL
3963 INTO l_seq_next
3964 FROM DUAL;
3965
3966 -- mark records for processing
3967 FOR chk_update_header_csr_rec IN chk_update_header_csr
3968 LOOP
3969 INSERT INTO okl_parallel_processes
3970 (object_type,
3971 object_value,
3972 assigned_process,
3973 process_status,
3974 start_date,
3975 khr_id,
3976 trx_id
3977 )
3978 VALUES ('CONT_TERM',
3979 chk_update_header_csr_rec.contract_number,
3980 TO_CHAR (l_seq_next),
3981 'PENDING_ASSIGNMENT',
3982 SYSDATE,
3983 chk_update_header_csr_rec.khr_id,
3984 chk_update_header_csr_rec.trx_id
3985 );
3986
3987 COMMIT;
3988 l_data_found := TRUE;
3989 END LOOP;
3990
3991 IF l_data_found THEN
3992 FOR chk_data_volume_csr_rec IN chk_data_volume_csr (l_seq_next)
3993 LOOP
3994 l_int_counter := l_int_counter + 1;
3995
3996 IF l_init_loop THEN -- initialize minimum and maximum lines
3997 l_init_loop := FALSE;
3998 l_max_lines := chk_data_volume_csr_rec.line_count;
3999 END IF;
4000
4001 l_contract_tab (l_int_counter).contract_number :=
4002 chk_data_volume_csr_rec.contract_number;
4003 l_contract_tab (l_int_counter).line_count :=
4004 chk_data_volume_csr_rec.line_count;
4005
4006 IF chk_data_volume_csr_rec.line_count > l_max_lines THEN
4007 l_max_lines := chk_data_volume_csr_rec.line_count;
4008 END IF;
4009 END LOOP;
4010
4011 -- reset, ready for use again
4012 l_init_loop := TRUE;
4013
4014 IF l_int_counter = 0 THEN
4015 write_to_log ('No Data Found for criteria passed ');
4016 END IF;
4017
4018 -- find the maximum line count from the original table and delete it
4019 -- put this as the first element of the new sorted table
4020 l_sort_int_counter := 0;
4021
4022 FOR i IN 1 .. l_int_counter
4023 LOOP
4024 IF l_contract_tab (i).line_count = l_max_lines THEN
4025 l_sort_int_counter := l_sort_int_counter + 1;
4026 l_sort_tab1 (l_sort_int_counter).contract_number :=
4027 l_contract_tab (i).contract_number;
4028 l_sort_tab1 (l_sort_int_counter).line_count :=
4029 l_contract_tab (i).line_count;
4030 l_contract_tab.DELETE (i);
4031 END IF;
4032 END LOOP;
4033
4034 -- start sorting
4035 IF l_contract_tab.FIRST IS NOT NULL THEN
4036 FOR i IN 1 .. l_contract_tab.COUNT
4037 LOOP
4038 -- find the next highest value in original table
4039 FOR i IN 1 .. l_contract_tab.LAST
4040 LOOP
4041 IF l_init_loop THEN
4042 IF l_contract_tab.EXISTS (i) THEN
4043 l_next_highest_val := l_contract_tab (i).line_count;
4044 l_init_loop := FALSE;
4045 END IF;
4046 END IF;
4047
4048 IF l_contract_tab.EXISTS (i)
4049 AND l_contract_tab (i).line_count > l_next_highest_val THEN
4050 l_next_highest_val := l_contract_tab (i).line_count;
4051 END IF;
4052 END LOOP;
4053
4054 -- reset flag, ready for use again
4055 l_init_loop := TRUE;
4056
4057 -- continue populating sort table in order
4058 FOR i IN 1 .. l_contract_tab.LAST
4059 LOOP
4060 IF l_contract_tab.EXISTS (i)
4061 AND l_contract_tab (i).line_count = l_next_highest_val THEN
4062 l_sort_int_counter := l_sort_int_counter + 1;
4063 l_sort_tab1 (l_sort_int_counter).contract_number :=
4064 l_contract_tab (i).contract_number;
4065 l_sort_tab1 (l_sort_int_counter).line_count :=
4066 l_contract_tab (i).line_count;
4067 l_contract_tab.DELETE (i);
4068 END IF;
4069 END LOOP;
4070
4071 EXIT WHEN l_contract_tab.LAST IS NULL;
4072 END LOOP;
4073 END IF; -- end sorting
4074
4075 -- begin processing load for workers
4076 FOR i IN 1 .. p_num_processes
4077 LOOP -- put all workers into a table
4078 l_worker_load (i).worker_number := i;
4079 l_worker_load (i).worker_load := 0; -- initialize load with zero
4080 END LOOP;
4081
4082 IF p_num_processes > 0 THEN
4083 l_lightest_worker := 1;
4084
4085 IF l_sort_tab1.COUNT > 0 THEN -- rmunjulu
4086 -- loop through the sorted table and ensure each contract has a worker
4087 FOR i IN 1 .. l_sort_tab1.COUNT
4088 LOOP
4089 l_sort_tab1 (i).worker_number := l_lightest_worker;
4090
4091 -- put current contract into the lightest worker
4092 IF l_worker_load.EXISTS (l_lightest_worker) THEN
4093 l_worker_load (l_lightest_worker).worker_load :=
4094 l_worker_load (l_lightest_worker).worker_load
4095 + l_sort_tab1 (i).line_count;
4096 END IF;
4097
4098 -- default the lighest load with the first element as a starting point
4099 IF l_worker_load.EXISTS (1) THEN
4100 l_lightest_load := l_worker_load (1).worker_load;
4101 l_lightest_worker := l_worker_load (1).worker_number;
4102
4103 -- logic to find lightest load
4104 FOR i IN 1 .. l_worker_load.COUNT
4105 LOOP
4106 IF (l_worker_load (i).worker_load = 0)
4107 OR (l_worker_load (i).worker_load < l_lightest_load
4108 ) THEN
4109 l_lightest_load := l_worker_load (i).worker_load;
4110 l_lightest_worker :=
4111 l_worker_load (i).worker_number;
4112 END IF;
4113 END LOOP;
4114 END IF;
4115 END LOOP;
4116 END IF; -- rmunjulu
4117 END IF;
4118
4119 l_sort_int_counter := 0;
4120
4121 IF l_worker_load.COUNT > 0 THEN -- rmunjulu
4122 FOR j IN 1 .. l_worker_load.LAST
4123 LOOP
4124 IF l_sort_tab1.COUNT > 0 THEN -- rmunjulu
4125 FOR i IN 1 .. l_sort_tab1.LAST
4126 LOOP
4127 IF l_sort_tab1.EXISTS (i)
4128 AND (l_sort_tab1 (i).worker_number =
4129 l_worker_load (j).worker_number
4130 ) THEN
4131 UPDATE okl_parallel_processes
4132 SET assigned_process =
4133 l_seq_next
4134 || '-'
4135 || l_sort_tab1 (i).worker_number,
4136 volume = l_sort_tab1 (i).line_count,
4137 process_status = 'ASSIGNED'
4138 WHERE object_type = 'CONT_TERM'
4139 AND object_value = l_sort_tab1 (i).contract_number
4140 AND process_status = 'PENDING_ASSIGNMENT';
4141
4142 COMMIT;
4143 l_sort_tab1.DELETE (i);
4144 END IF;
4145 END LOOP;
4146 END IF; -- rmunjulu
4147 END LOOP;
4148 END IF; -- rmunjulu
4149
4150 -- SPAWN THE CHILD CONCURRENT PROGRAM WHICH WILL DO THE ACTUAL EXPIRATION PROCESSING
4151 FOR j IN l_worker_load.FIRST .. l_worker_load.LAST
4152 LOOP
4153 -- Do not spawn a worker if theres no data to process
4154 -- This occurs if more workers are requested and the
4155 -- distribution of data does not utilize them all
4156 IF l_worker_load (j).worker_load > 0 THEN
4157 fnd_request.set_org_id (mo_global.get_current_org_id);
4158 --MOAC- Concurrent request
4159 request_id :=
4160 fnd_request.submit_request (application => 'OKL',
4161 program => 'OKL_AM_CHILD_TERM',
4162 sub_request => FALSE,
4163 argument1 => l_seq_next
4164 || '-'
4165 || j
4166 );
4167 write_to_log ( 'Launching Process '
4168 || l_seq_next
4169 || '-'
4170 || j
4171 || ' with Request ID '
4172 || request_id
4173 );
4174
4175 IF (request_id = 0) THEN
4176 errbuf := fnd_message.get;
4177 retcode := 2;
4178 END IF;
4179 END IF;
4180 END LOOP;
4181
4182 -- clean up
4183 -- Delete records from in chk_update_header_csr that were unassigned
4184 DELETE okl_parallel_processes
4185 WHERE process_status = 'PENDING_ASSIGNMENT'
4186 AND assigned_process = TO_CHAR (l_seq_next);
4187
4188 COMMIT;
4189 ELSE
4190 write_to_log
4191 ('No workers assigned due to no data found for prcocesing');
4192 END IF; -- l_data_found
4193
4194 IF (is_debug_procedure_on) THEN
4195 okl_debug_pub.log_debug (g_level_procedure, l_module_name, 'End(-)');
4196 END IF;
4197 EXCEPTION
4198 WHEN OTHERS THEN
4199 IF (is_debug_exception_on) THEN
4200 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
4201 || sqlcode || ' , SQLERRM : ' || sqlerrm);
4202 END IF;
4203 write_to_log ('Unhandled Exception ' || SQLERRM);
4204 END process_spawner;
4205 END okl_am_btch_exp_lease_loan_pvt;