[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_BAL_WRITEOFF_PVT
Source
1 PACKAGE BODY OKL_AM_BAL_WRITEOFF_PVT AS
2 /* $Header: OKLRBWRB.pls 120.11 2007/08/10 11:53:42 dpsingh noship $ */
3
4 -- *********************
5 -- GLOBAL DATASTRUCTURES
6 -- *********************
7
8 -- Rec Type to Store Messages
9 TYPE msg_rec_type IS RECORD (
10 id NUMBER, -- Added
11 msg VARCHAR2(2000));
12
13 -- Table Type to Messages Rec
14 TYPE msg_tbl_type IS TABLE OF msg_rec_type INDEX BY BINARY_INTEGER;
15
16 -- Rec Type to Store Message details with IA details
17 TYPE message_rec_type IS RECORD (
18 id NUMBER,
19 contract_number VARCHAR2(300),
20 start_date DATE,
21 end_date DATE,
22 status VARCHAR2(300) );
23
24 -- Table Type to Store Recs of Message details with IA details
25 TYPE message_tbl_type IS TABLE OF message_rec_type INDEX BY BINARY_INTEGER;
26
27 -- *********************
28 -- GLOBAL MESSAGE CONSTANTS
29 -- *********************
30 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
31 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
32 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
33 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
34 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
35 G_CONTRACT_NUMBER_TOKEN CONSTANT VARCHAR2(2000) := 'CONTRACT_NUMBER';
36 -- *********************
37 -- GLOBAL VARIABLES
38 -- *********************
39 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_AM_BAL_WRITEOFF_PVT';
40 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
41 G_APP_NAME_1 CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
42 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
44 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 G_API_VERSION CONSTANT NUMBER := 1;
46 G_MISS_CHAR CONSTANT VARCHAR2(1) := OKL_API.G_MISS_CHAR;
47 G_MISS_NUM CONSTANT NUMBER := OKL_API.G_MISS_NUM;
48 G_MISS_DATE CONSTANT DATE := OKL_API.G_MISS_DATE;
49 G_TRUE CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
50 G_FALSE CONSTANT VARCHAR2(1) := OKL_API.G_FALSE;
51 G_YES CONSTANT VARCHAR2(1) := 'Y';
52 G_NO CONSTANT VARCHAR2(1) := 'N';
53 G_FIRST CONSTANT NUMBER := FND_MSG_PUB.G_FIRST;
54 G_NEXT CONSTANT NUMBER := FND_MSG_PUB.G_NEXT;
55 G_ERROR VARCHAR2(1) := 'N';
56 G_KHR_ENDED_BY_DATE DATE;
57 success_message_table message_tbl_type;
58 error_message_table message_tbl_type;
59 l_error_count NUMBER := 1;
60
61 -- *********************
62 -- GLOBAL EXCEPTIONS
63 -- *********************
64 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
65 G_EXCEPTION_ERROR EXCEPTION;
66 G_EXCEPTION_HALT EXCEPTION;
67
68 -- *********************
69 -- GLOBAL DECLARATIONS
70 -- *********************
71 msg_lines_table msg_tbl_type;
72 l_success_tbl_index NUMBER := 1;
73 l_error_tbl_index NUMBER := 1;
74
75 -- Start of comments
76 --
77 -- Procedure Name: fnd_error_output
78 -- Desciption : Logs the messages in the output log
79 -- Business Rules:
80 -- Parameters :
81 -- Version: 1.0
82 -- History : RMUNJULU created
83 --
84 -- End of comments
85 PROCEDURE fnd_output (
86 p_khr_rec IN khr_rec_type,
87 p_control_flag IN VARCHAR2 ) IS
88
89 lx_error_rec OKL_API.error_rec_type;
90 l_msg_idx INTEGER := G_FIRST;
91
92 BEGIN
93
94 -- Get the messages in the log
95 LOOP
96
97 FND_MSG_PUB.get(
98 p_msg_index => l_msg_idx,
99 p_encoded => G_FALSE,
100 p_data => lx_error_rec.msg_data,
101 p_msg_index_out => lx_error_rec.msg_count);
102
103 IF (lx_error_rec.msg_count IS NOT NULL) THEN
104
105 -- Store the contract id
106 msg_lines_table(l_error_count).id := p_khr_rec.id;
107 msg_lines_table(l_error_count).msg := lx_error_rec.msg_data;
108
109 l_error_count := l_error_count + 1;
110 END IF;
111
112 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
113 OR (lx_error_rec.msg_count IS NULL));
114
115 l_msg_idx:= G_NEXT;
116
117 END LOOP;
118
119
120 IF p_control_flag = 'PROCESSED' THEN
121
122 success_message_table(l_success_tbl_index).id := p_khr_rec.id;
123 success_message_table(l_success_tbl_index).contract_number := p_khr_rec.contract_number;
124 success_message_table(l_success_tbl_index).start_date := p_khr_rec.start_date;
125 success_message_table(l_success_tbl_index).end_date := p_khr_rec.end_date;
126 success_message_table(l_success_tbl_index).status := p_khr_rec.sts_code;
127 l_success_tbl_index := l_success_tbl_index + 1;
128
129 ELSE
130
131 error_message_table(l_error_tbl_index).id := p_khr_rec.id;
132 error_message_table(l_error_tbl_index).contract_number := p_khr_rec.contract_number;
133 error_message_table(l_error_tbl_index).start_date := p_khr_rec.start_date;
134 error_message_table(l_error_tbl_index).end_date := p_khr_rec.end_date;
135 error_message_table(l_error_tbl_index).status := p_khr_rec.sts_code;
136 l_error_tbl_index := l_error_tbl_index + 1;
137
138 END IF;
139
140 EXCEPTION
141
142 WHEN OTHERS THEN
143 -- Set the oracle error message
144 OKL_API.set_message(
145 p_app_name => G_APP_NAME_1,
146 p_msg_name => G_UNEXPECTED_ERROR,
147 p_token1 => G_SQLCODE_TOKEN,
148 p_token1_value => SQLCODE,
149 p_token2 => G_SQLERRM_TOKEN,
150 p_token2_value => SQLERRM);
151
152 END fnd_output;
153
154 -- Start of comments
155 --
156 -- Procedure Name: create_report
157 -- Desciption : Creates the Output and Log Reports
158 -- Business Rules:
159 -- Parameters :
160 -- Version: 1.0
161 -- History : RMUNJULU created
162 --
163 -- End of comments
164 PROCEDURE create_report IS
165
166 i NUMBER;
167 j NUMBER;
168 k NUMBER;
169 l_success NUMBER;
170 l_error NUMBER;
171
172 -- Get the Org Name
173 CURSOR org_csr (p_org_id IN NUMBER) IS
174 SELECT HOU.name
175 FROM HR_OPERATING_UNITS HOU
176 WHERE HOU.organization_id = p_org_id;
177
178
179 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
180
181 l_org_name VARCHAR2(300);
182 l_orcl_logo VARCHAR2(300);
183 l_term_heading VARCHAR2(300);
184 l_set_of_books VARCHAR2(300);
185 l_set_of_books_name VARCHAR2(300);
186 l_run_date VARCHAR2(300);
187 l_oper_unit VARCHAR2(300);
188 l_type VARCHAR2(300);
189 l_processed VARCHAR2(300);
190 l_term_k VARCHAR2(300);
191 l_error_k VARCHAR2(300);
192 l_serial VARCHAR2(300);
193 l_k_num VARCHAR2(300);
194 l_start_date VARCHAR2(300);
195 l_end_date VARCHAR2(300);
196 l_status VARCHAR2(300);
197 l_messages VARCHAR2(300);
198 l_eop VARCHAR2(300);
199 l_inv_ended_by VARCHAR2(300);
200 l_inv VARCHAR2(300);
201 l_print VARCHAR2(1);
202 msg_lines_table_index NUMBER;
203
204 BEGIN
205
206 l_success := success_message_table.COUNT;
207 l_error := error_message_table.COUNT;
208
209 l_orcl_logo := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_ACCT_LEASE_MANAGEMENT');
210 --l_term_heading := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_TERM_INV');
211
212 l_term_heading := 'Balance Writeoff for Terminated and Expired Contracts';
213
214 l_set_of_books := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SET_OF_BOOKS');
215 l_run_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_RUN_DATE');
216 l_oper_unit := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_OPERUNIT');
217 l_type := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_TYPE');
218 l_processed := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_PROCESSED_ENTRIES');
219 --l_term_k := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_TERMINATED_INV');
220
221 l_term_k := 'Successful Writeoffs';
222
223 --l_error_k := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_ERRORED_INV');
224
225 l_error_k := 'Errored Writeoffs';
226
227 l_serial := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SERIAL_NUMBER');
228 --l_k_num := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_INV_AGR_NUM');
229
230 l_k_num := 'Contract Number';
231 l_start_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_START_DATE');
232 l_end_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_END_DATE');
233 --l_status := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_STATUS');
234
235 l_status := 'Contract Status';
236
237 l_messages := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_MESSAGES');
238 l_eop := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_END_OF_REPORT');
239 -- l_inv_ended_by := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_INV_AGR_ENDED_BY');
240
241 l_inv_ended_by := 'Contracts Ended By';
242
243 l_inv := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_INVALID_TERM_DATE');
244
245 l_set_of_books_name := OKL_ACCOUNTING_UTIL.get_set_of_books_name (OKL_ACCOUNTING_UTIL.get_set_of_books_id);
246
247 -- Get the Org Name
248 FOR org_rec IN org_csr (l_org_id) LOOP
249 l_org_name := org_rec.name;
250 END LOOP;
251
252 -- Valid Vendor Prg Chosen
253 IF G_ERROR <> 'Y' THEN
254
255 --log
256 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
257 FND_FILE.put_line(FND_FILE.log, l_type ||
258 RPAD(' ',40-LENGTH(l_type),' ') ||
259 l_processed);
260
261 FND_FILE.put_line(FND_FILE.log, RPAD('-',77 ,'-'));
262
263 FND_FILE.put_line(FND_FILE.log, l_term_k ||
264 RPAD(' ',40-LENGTH(l_term_k),' ') ||
265 l_success);
266
267 FND_FILE.put_line(FND_FILE.log, l_error_k ||
268 RPAD(' ',40-LENGTH(l_error_k),' ') ||
269 l_error);
270 FND_FILE.put_line(FND_FILE.log,'');
271 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
272
273 -- output
274 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_orcl_logo)/2, ' ' ) ||
275 l_orcl_logo);
276
277 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_term_heading)/2, ' ' ) ||
278 l_term_heading);
279
280 FND_FILE.put_line(FND_FILE.output, RPAD(' ',128/2-LENGTH(l_term_heading)/2 , ' ' ) ||
281 RPAD('-',LENGTH(l_term_heading),'-'));
282
283 FND_FILE.put_line(FND_FILE.output, '');
284
285 FND_FILE.put_line(FND_FILE.output, l_set_of_books ||' : '||
286 l_set_of_books_name ||
287 RPAD(' ', 128-LENGTH(l_set_of_books)-LENGTH(l_set_of_books_name)-LENGTH(l_run_date)-25, ' ' ) ||
288 l_run_date ||' : ' ||
289 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
290
291 FND_FILE.put_line(FND_FILE.output, l_oper_unit || ' : ' ||
292 l_org_name ||
293 RPAD(' ', 128-LENGTH(l_oper_unit)-LENGTH(l_org_name)-LENGTH(l_inv_ended_by)-25, ' ' ) ||
294 l_inv_ended_by ||' : ' ||
295 TO_CHAR(G_KHR_ENDED_BY_DATE, 'DD-MON-YYYY HH24:MI'));
296
297 FND_FILE.put_line(FND_FILE.output,'');
298 FND_FILE.put_line(FND_FILE.output,'');
299
300 FND_FILE.put_line(FND_FILE.output, l_type ||
301 RPAD(' ',40-LENGTH(l_type),' ') ||
302 l_processed);
303
304 FND_FILE.put_line(FND_FILE.output, RPAD('-',128 ,'-'));
305
306 FND_FILE.put_line(FND_FILE.output, l_term_k ||
307 RPAD(' ',40-LENGTH(l_term_k),' ') ||
308 l_success);
309
310 FND_FILE.put_line(FND_FILE.output, l_error_k ||
311 RPAD(' ',40-LENGTH(l_error_k),' ') ||
312 l_error);
313
314 FND_FILE.put_line(FND_FILE.output,'');
315 FND_FILE.put_line(FND_FILE.output, RPAD('=',128,'=' ));
316 FND_FILE.put_line(FND_FILE.output,'');
317
318 -- Print VPAs Terminated Successfully
319 IF l_success > 0 THEN
320
321 FND_FILE.put_line(FND_FILE.output, l_term_k);
322 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_term_k), '-' ));
323 FND_FILE.put_line(FND_FILE.output,'');
324
325 l_print := 'N';
326
327 FOR i IN success_message_table.FIRST..success_message_table.LAST LOOP
328
329 IF l_print = 'N' THEN
330
331 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
332 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
333 l_start_date||RPAD(' ',15-LENGTH(l_start_date),' ') ||
334 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' ') ||
335 l_status||RPAD(' ',15-LENGTH(l_status),' '));
336
337 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
338 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
339 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',15-LENGTH(l_start_date),'-') ||
340 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-') ||
341 RPAD('-',LENGTH(l_status),'-')||RPAD('-',15-LENGTH(l_status),'-'));
342
343 l_print := 'Y';
344 END IF;
345
346 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
347 success_message_table(i).contract_number ||
348 RPAD(' ',35-LENGTH(success_message_table(i).contract_number),' ')||
349 success_message_table(i).start_date||
350 RPAD(' ',15-LENGTH(success_message_table(i).start_date),' ') ||
351 success_message_table(i).end_date||
352 RPAD(' ',15-LENGTH(success_message_table(i).end_date),' ') ||
353 success_message_table(i).status||
354 RPAD(' ',15-LENGTH(success_message_table(i).status),' '));
355
356 END LOOP;
357 END IF;
358
359 FND_FILE.put_line(FND_FILE.output,'');
360
361 -- Print VPAs errored
362 IF l_error > 0 THEN
363
364 FND_FILE.put_line(FND_FILE.output, l_error_k);
365 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_error_k), '-' ));
366 FND_FILE.put_line(FND_FILE.output,'');
367
368 -- Initialize the table index
369 msg_lines_table_index := 1;
370
371 FOR i IN error_message_table.FIRST..error_message_table.LAST LOOP
372
373 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
374 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
375 l_start_date||RPAD(' ',15-LENGTH(l_start_date),' ') ||
376 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' ') ||
377 l_status||RPAD(' ',15-LENGTH(l_status),' '));
378
379 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
380 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
381 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',15-LENGTH(l_start_date),'-') ||
382 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-') ||
383 RPAD('-',LENGTH(l_status),'-')||RPAD('-',15-LENGTH(l_status),'-'));
384
385 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
386 error_message_table(i).contract_number ||
387 RPAD(' ',35-LENGTH(error_message_table(i).contract_number),' ')||
388 error_message_table(i).start_date||
389 RPAD(' ',15-LENGTH(error_message_table(i).start_date),' ') ||
390 error_message_table(i).end_date||
391 RPAD(' ',15-LENGTH(error_message_table(i).end_date),' ') ||
392 error_message_table(i).status||
393 RPAD(' ',15-LENGTH(error_message_table(i).status),' '));
394
395 FND_FILE.put_line(FND_FILE.output,'');
396
397 FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
398
399 k := 1;
400 FOR j IN msg_lines_table_index .. msg_lines_table.LAST LOOP
401 IF msg_lines_table(j).id = error_message_table(i).id THEN
402 FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || k || ': ' || msg_lines_table(j).msg);
403 FND_FILE.put_line(FND_FILE.output,'');
404 k := k + 1;
405 ELSE
406 msg_lines_table_index := j ;
407 EXIT;
408 END IF;
409
410 END LOOP;
411
412 FND_FILE.put_line(FND_FILE.output,'');
413
414 END LOOP;
415 END IF;
416
417 FND_FILE.put_line(FND_FILE.output,'');
418 FND_FILE.put_line(FND_FILE.output,'');
419 FND_FILE.put_line(FND_FILE.output, RPAD(' ', 53 , ' ' ) || l_eop);
420
421 ELSE
422
423 FND_FILE.put_line(FND_FILE.log,l_processed || ' = 0');
424 FND_FILE.put_line(FND_FILE.log,l_inv);
425
426 END IF;
427 EXCEPTION
428
429 WHEN OTHERS THEN
430 -- Set the oracle error message
431 OKL_API.set_message(
432 p_app_name => G_APP_NAME_1,
433 p_msg_name => G_UNEXPECTED_ERROR,
434 p_token1 => G_SQLCODE_TOKEN,
435 p_token1_value => SQLCODE,
436 p_token2 => G_SQLERRM_TOKEN,
437 p_token2_value => SQLERRM);
438
439 END create_report;
440
441 -- Start of comments
442 --
443 -- Procedure Name : populate_khr_prg
444 -- Description : procedure to validate khr rec
445 -- Business Rules :
446 -- Parameters :
447 -- Version : 1.0
448 -- History : RMUNJULU Created
449 --
450 -- End of comments
451 PROCEDURE populate_khr_prg(
452 p_khr_rec IN khr_rec_type,
453 x_khr_rec OUT NOCOPY khr_rec_type,
454 x_return_status OUT NOCOPY VARCHAR2) IS
455
456 -- Get the details of the VPA
457 CURSOR get_khr_details_csr (p_khr_id IN NUMBER) IS
458 SELECT CHR.id,
459 CHR.contract_number contract_number,
460 CHR.START_DATE,
461 CHR.end_date,
462 CHR.sts_code,
463 CHR.scs_code,
464 CHR.date_terminated
465 FROM OKC_K_HEADERS_B CHR,
466 OKL_K_HEADERS KHR
467 WHERE CHR.id = p_khr_id
468 AND CHR.id = KHR.id;
469
470 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
471 l_khr_rec khr_rec_type;
472 get_khr_details_rec get_khr_details_csr%ROWTYPE;
473
474 BEGIN
475
476 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
478 'OKL_AM_BAL_WRITEOFF_PVT.populate_khr_prg.',
479 'Begin(+)');
480 END IF;
481
482 SAVEPOINT populate_khr_trx;
483
484 -- Get VPA details
485 OPEN get_khr_details_csr (p_khr_rec.id);
486 FETCH get_khr_details_csr INTO get_khr_details_rec;
487 CLOSE get_khr_details_csr;
488
489 -- Set the va rec with VPA details
490 l_khr_rec.id := get_khr_details_rec.id;
491 l_khr_rec.contract_number := get_khr_details_rec.contract_number;
492 l_khr_rec.start_date := get_khr_details_rec.start_date;
493 l_khr_rec.end_date := get_khr_details_rec.end_date;
494 l_khr_rec.sts_code := get_khr_details_rec.sts_code;
495 l_khr_rec.date_terminated := get_khr_details_rec.date_terminated;
496 l_khr_rec.scs_code := get_khr_details_rec.scs_code;
497
498 -- Set return values
499 x_return_status := l_return_status;
500 x_khr_rec := l_khr_rec;
501
502 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
503 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
504 'OKL_AM_BAL_WRITEOFF_PVT.populate_khr_prg.',
505 'End(-)');
506 END IF;
507
508 EXCEPTION
509
510 WHEN OTHERS THEN
511 ROLLBACK TO populate_khr_trx;
512 -- Set the oracle error message
513 OKL_API.set_message(
514 p_app_name => G_APP_NAME_1,
515 p_msg_name => G_UNEXPECTED_ERROR,
516 p_token1 => G_SQLCODE_TOKEN,
517 p_token1_value => SQLCODE,
518 p_token2 => G_SQLERRM_TOKEN,
519 p_token2_value => SQLERRM);
520
521 x_return_status := G_RET_STS_UNEXP_ERROR;
522
523 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
524 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
525 'OKL_AM_BAL_WRITEOFF_PVT.populate_khr_prg.',
526 'EXP - OTHERS');
527 END IF;
528
529 END populate_khr_prg;
530
531
532 -- Start of comments
533 --
534 -- Procedure Name : validate_khr_prg
535 -- Description : procedure to validate khr rec
536 -- Business Rules :
537 -- Parameters :
538 -- Version : 1.0
539 -- History : RMUNJULU Created
540 --
541 -- End of comments
542 PROCEDURE validate_khr_prg(
543 p_khr_rec IN khr_rec_type,
544 p_control_flag IN VARCHAR2,
545 x_return_status OUT NOCOPY VARCHAR2) IS
546
547 -- Check if close balances already done thru termination
548 CURSOR get_termination_trn_csr (p_khr_id IN NUMBER) IS
549 SELECT TRN.id
550 FROM OKL_TRX_AR_ADJSTS_V BAL,
551 OKL_TRX_CONTRACTS TRN
552 WHERE TRN.khr_id = p_khr_id
553 AND TRN.tcn_type = 'TMT'
554 AND BAL.tcn_id = TRN.id;
555
556 -- Check if close balances already done thru termination
557 CURSOR get_writeoff_trn_csr (p_khr_id IN NUMBER) IS
558 SELECT TRN.id
559 FROM OKL_TRX_CONTRACTS TRN
560 WHERE TRN.khr_id = p_khr_id
561 AND TRN.tcn_type = 'BWO';
562
563 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
564 l_ia_number VARCHAR2(300);
565 l_start_date DATE;
566 l_end_date DATE;
567 l_status VARCHAR2(300);
568 l_type VARCHAR2(300);
569 l_pdt_id NUMBER;
570 l_trn_id NUMBER;
571 i NUMBER := 0;
572 l_tsu_code VARCHAR2(300);
573
574 BEGIN
575
576 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
577 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
578 'OKL_AM_BAL_WRITEOFF_PVT.validate_khr_prg.',
579 'Begin(+)');
580 END IF;
581
582 SAVEPOINT validate_khr_trx;
583
584 -- If single request then do additional validations
585 IF p_control_flag = 'BATCH_SINGLE' THEN -- Do additional checks
586
587 -- Check for ID
588 IF p_khr_rec.id IS NULL
589 OR p_khr_rec.id = OKL_API.G_MISS_NUM THEN
590
591 -- Message:
592 OKL_API.set_message(
593 p_app_name => G_APP_NAME,
594 p_msg_name => 'OKL_AM_BWR_KHR_ID_INV');
595
596 RAISE G_EXCEPTION_ERROR;
597 END IF;
598
599 -- Check for type
600 IF p_khr_rec.scs_code NOT IN ('LEASE','LOAN') THEN -- *** CHECK
601
602 -- Message:
603 OKL_API.set_message(
604 p_app_name => G_APP_NAME,
605 p_msg_name => 'OKL_AM_BWR_KHR_TYPE_INV',
606 p_token1 => 'CONTRACT_NUMBER',
607 p_token1_value => p_khr_rec.contract_number);
608
609 RAISE G_EXCEPTION_ERROR;
610 END IF;
611
612 -- Check for status
613 IF p_khr_rec.sts_code NOT IN ('TERMINATED', 'EXPIRED') THEN
614
615 -- Message:
616 OKL_API.set_message(
617 p_app_name => G_APP_NAME,
618 p_msg_name => 'OKL_AM_BWR_KHR_STATUS_INV',
619 p_token1 => 'CONTRACT_NUMBER',
620 p_token1_value => p_khr_rec.contract_number);
621
622 RAISE G_EXCEPTION_ERROR;
623 END IF;
624
625 -- Check for end date
626 IF p_khr_rec.end_date IS NULL THEN
627
628 -- Message:
629 OKL_API.set_message(
630 p_app_name => G_APP_NAME,
631 p_msg_name => 'OKL_AM_BWR_KHR_END_DATE_INV',
632 p_token1 => 'CONTRACT_NUMBER',
633 p_token1_value => p_khr_rec.contract_number);
634
635 RAISE G_EXCEPTION_ERROR;
636 END IF;
637
638 -- Check if balance writeoff already done through termination
639 OPEN get_termination_trn_csr (p_khr_rec.id);
640 FETCH get_termination_trn_csr INTO l_trn_id;
641 CLOSE get_termination_trn_csr;
642
643 IF l_trn_id IS NOT NULL
644 AND l_trn_id <> OKL_API.G_MISS_NUM THEN
645
646 -- Message:
647 OKL_API.set_message(
648 p_app_name => G_APP_NAME,
649 p_msg_name => 'OKL_AM_BWR_KHR_DONE',
650 p_token1 => 'CONTRACT_NUMBER',
651 p_token1_value => p_khr_rec.contract_number);
652
653 RAISE G_EXCEPTION_ERROR;
654
655 END IF;
656
657 -- Check if balance writeoff already done
658 OPEN get_writeoff_trn_csr (p_khr_rec.id);
659 FETCH get_writeoff_trn_csr INTO l_trn_id;
660 CLOSE get_writeoff_trn_csr;
661
662 IF l_trn_id IS NOT NULL
663 AND l_trn_id <> OKL_API.G_MISS_NUM THEN
664
665 -- Message:
666 OKL_API.set_message(
667 p_app_name => G_APP_NAME,
668 p_msg_name => 'OKL_AM_BWR_KHR_DONE',
669 p_token1 => 'CONTRACT_NUMBER',
670 p_token1_value => p_khr_rec.contract_number);
671
672 RAISE G_EXCEPTION_ERROR;
673
674 END IF;
675
676 END IF;
677
678 -- Set return values
679 x_return_status := l_return_status;
680
681 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
683 'OKL_AM_BAL_WRITEOFF_PVT.validate_khr_prg.',
684 'End(-)');
685 END IF;
686
687 EXCEPTION
688
689 WHEN G_EXCEPTION_ERROR THEN
690 ROLLBACK TO validate_khr_trx;
691 x_return_status := G_RET_STS_ERROR;
692
693 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
694 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
695 'OKL_AM_BAL_WRITEOFF_PVT.validate_khr_prg.',
696 'EXP - G_EXCEPTION_ERROR');
697 END IF;
698
699 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
700 ROLLBACK TO validate_khr_trx;
701 x_return_status := G_RET_STS_UNEXP_ERROR;
702
703 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
704 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
705 'OKL_AM_BAL_WRITEOFF_PVT.validate_khr_prg.',
706 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
707 END IF;
708
709 WHEN OTHERS THEN
710 ROLLBACK TO validate_khr_trx;
711 -- Set the oracle error message
712 OKL_API.set_message(
713 p_app_name => G_APP_NAME_1,
714 p_msg_name => G_UNEXPECTED_ERROR,
715 p_token1 => G_SQLCODE_TOKEN,
716 p_token1_value => SQLCODE,
717 p_token2 => G_SQLERRM_TOKEN,
718 p_token2_value => SQLERRM);
719
720 x_return_status := G_RET_STS_UNEXP_ERROR;
721
722 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
723 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
724 'OKL_AM_BAL_WRITEOFF_PVT.validate_khr_prg.',
725 'EXP - OTHERS');
726 END IF;
727
728 END validate_khr_prg;
729
730
731 -- Start of comments
732 --
733 -- Procedure Name: create_report
734 -- Desciption : Creates the Output and Log Reports
735 -- Business Rules:
736 -- Parameters :
737 -- Version: 1.0
738 -- History : RMUNJULU created
739 --
740 -- End of comments
741 PROCEDURE write_off_balances(
742 p_api_version IN NUMBER,
743 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_count OUT NOCOPY NUMBER,
746 x_msg_data OUT NOCOPY VARCHAR2,
747 p_khr_rec IN khr_rec_type,
748 p_sys_date IN DATE,
749 p_control_flag IN VARCHAR2) IS
750
751 -- Cursor to get the balances of contract
752 /* rmunjulu R12 Fixes - Billing fixes -- replaced with new cursor below
753 CURSOR k_balances_csr (p_khr_id IN NUMBER, p_trn_date DATE) IS
754 SELECT SUM(amount_due_remaining)
755 FROM OKL_BPD_LEASING_PAYMENT_TRX_V
756 WHERE contract_id = p_khr_id
757 AND invoice_date <= sysdate;
758 */
759
760 -- rmunjulu R12 Fixes - Billing fixes -- changes to this cursor as old bpd view does not work anymore
761 CURSOR k_balances_csr (p_khr_id IN NUMBER, p_trn_date DATE) IS
762 SELECT SUM(amount_due_remaining)
763 FROM OKL_BPD_TLD_AR_LINES_V
764 WHERE khr_id = p_khr_id
765 AND invoice_date <= sysdate;
766
767 /* rmunjulu R12 Fixes - Billing fixes -- replaced with new cursor below
768 -- Cursor to get the lines with amount due and payment schedule id for the balances
769 CURSOR k_bal_lns_csr (p_khr_id IN NUMBER, p_trn_date DATE) IS
770 SELECT OBLP.amount_due_remaining AMOUNT,
771 OBLP.stream_type_id STREAM_TYPE_ID,
772 OSTY.name STREAM_MEANING,
773 OBLP.payment_schedule_id SCHEDULE_ID,
774 OBLP.receivables_invoice_number AR_INVOICE_NUMBER,
775 OTIL.id TIL_ID,
776 -999 TLD_ID
777 FROM OKL_BPD_LEASING_PAYMENT_TRX_V OBLP,
778 OKL_TXL_AR_INV_LNS_V OTIL,
779 OKL_STRM_TYPE_V OSTY
780 WHERE OBLP.contract_id = p_khr_id
781 AND OBLP.receivables_invoice_id = OTIL.receivables_invoice_id
782 AND OBLP.stream_type_id = OSTY.id
783 AND OBLP.amount_due_remaining > 0
784 AND OBLP.invoice_date <= p_trn_date
785 UNION
786 SELECT OBLP.amount_due_remaining AMOUNT,
787 OBLP.stream_type_id STREAM_TYPE_ID,
788 OSTY.name STREAM_MEANING,
789 OBLP.payment_schedule_id SCHEDULE_ID,
790 OBLP.receivables_invoice_number AR_INVOICE_NUMBER,
791 OTAI.til_id_details TIL_ID,
792 OTAI.id TLD_ID
793 FROM OKL_BPD_LEASING_PAYMENT_TRX_V OBLP,
794 OKL_TXD_AR_LN_DTLS_V OTAI,
795 OKL_STRM_TYPE_V OSTY
796 WHERE OBLP.contract_id = p_khr_id
797 AND OBLP.receivables_invoice_id = OTAI.receivables_invoice_id
798 AND OBLP.stream_type_id = OSTY.id
799 AND OBLP.amount_due_remaining > 0
800 AND OBLP.invoice_date <= p_trn_date;
801 */
802
803 -- rmunjulu R12 Fixes - Billing fixes -- changes to this cursor as old bpd view does not work anymore
804 CURSOR k_bal_lns_csr (p_khr_id IN NUMBER, p_trn_date DATE) IS
805 SELECT RACTRL.amount_due_remaining AMOUNT,
806 RACTRL.STY_ID STREAM_TYPE_ID,
807 --Bug 6316320 dpsingh start
808 RACTRL.KLE_ID KLE_ID,
809 --Bug 6316320 dpsingh end
810 RACTRL.STREAM_TYPE STREAM_MEANING,
811 APS.payment_schedule_id SCHEDULE_ID,
812 RACTRL.CUSTOMER_TRX_ID AR_INVOICE_NUMBER,
813 RACTRL.til_id_details TIL_ID,
814 RACTRL.TLD_ID TLD_ID
815 FROM OKL_BPD_TLD_AR_LINES_V RACTRL,
816 AR_PAYMENT_SCHEDULES_ALL APS
817 WHERE RACTRL.khr_id = p_khr_id
818 AND RACTRL.amount_due_remaining > 0
819 AND RACTRL.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
820 AND RACTRL.invoice_date <= p_trn_date;
821
822
823 -- Cursor to get the product of the contract
824 CURSOR prod_id_csr (p_khr_id IN NUMBER) IS
825 SELECT pdt_id
826 FROM OKL_K_HEADERS_V
827 WHERE id = p_khr_id;
828
829 -- Cursor to get the code_combination_id for the transaction id and
830 -- transaction table
831 -- RMUNJULU 03-JAN-03 2683876 Added code to
832 -- make sure we get the debit distribution and also it is 100percent
833 CURSOR code_combination_id_csr(p_source_id IN NUMBER,
834 p_source_table IN VARCHAR2) IS
835 SELECT DST.code_combination_id
836 FROM OKL_TRNS_ACC_DSTRS DST
837 WHERE DST.source_id = p_source_id
838 AND DST.source_table = p_source_table
839 AND DST.cr_dr_flag = 'D'
840 AND DST.percentage = 100;
841
842 -- get tolerance profile name
843 CURSOR get_profile_name_csr IS
844 SELECT user_profile_option_name
845 FROM fnd_profile_options_vl
846 WHERE profile_option_name = 'OKL_SMALL_BALANCE_TOLERANCE';
847
848 k_bal_lns_rec k_bal_lns_csr%ROWTYPE;
849 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
850 lp_adjv_rec OKL_TRX_AR_ADJSTS_PUB.adjv_rec_type;
851 lx_adjv_rec OKL_TRX_AR_ADJSTS_PUB.adjv_rec_type;
852 l_early_termination_yn VARCHAR2(1) := OKL_API.G_FALSE;
853 l_total_amount_due NUMBER := -1;
854 l_code_combination_id NUMBER := -1;
855 i NUMBER := 1;
856 l_tolerance_amt NUMBER := -1;
857 l_api_name VARCHAR2(30) := 'write_off_balances';
858 l_pdt_id NUMBER := 0;
859 lp_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PUB.acc_gen_primary_key;
860
861 --Bug 6316320 dpsingh start
862 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
863 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_type;
864 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
865 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
866 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
867 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
868 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
869
870 TYPE ajlv_id_rec_type IS RECORD (
871 id NUMBER,
872 amount OKL_BPD_TLD_AR_LINES_V.AMOUNT%TYPE,
873 ar_invoice_number OKL_BPD_TLD_AR_LINES_V.CUSTOMER_TRX_ID%TYPE,
874 stream_meaning OKL_BPD_TLD_AR_LINES_V.STREAM_TYPE%TYPE);
875
876 TYPE ajlv_id_tbl_type IS TABLE OF ajlv_id_rec_type INDEX BY BINARY_INTEGER;
877 ajlv_id_tbl ajlv_id_tbl_type;
878
879 CURSOR get_account_derivation_meth IS
880 SELECT ACCOUNT_DERIVATION
881 FROM OKL_SYS_ACCT_OPTS;
882
883 l_account_derivation OKL_SYS_ACCT_OPTS.ACCOUNT_DERIVATION%TYPE;
884 --Bug 6316320 dpsingh end
885
886 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
887 l_writeoff_try_id NUMBER;
888 l_trn_try_id NUMBER;
889 l_trans_meaning VARCHAR2(200);
890 l_currency_code VARCHAR2(200);
891 l_formatted_bal_amt VARCHAR2(200);
892 l_formatted_tol_amt VARCHAR2(200);
893 l_formatted_adj_amt VARCHAR2(200);
894 l_api_version VARCHAR2(3) := G_API_VERSION;
895
896 l_functional_currency_code VARCHAR2(15);
897 l_contract_currency_code VARCHAR2(15);
898 l_currency_conversion_type VARCHAR2(30);
899 l_currency_conversion_rate NUMBER;
900 l_currency_conversion_date DATE;
901 l_converted_amount NUMBER;
902
903 -- Since we do not use the amount or converted amount
904 -- set a hardcoded value for the amount (and pass to to
905 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
906 -- conversion values )
907 l_hard_coded_amount CONSTANT NUMBER := 100;
908
909 lp_ajlv_rec OKL_TXL_ADJSTS_LNS_PUB.ajlv_rec_type;
910 lx_ajlv_rec OKL_TXL_ADJSTS_LNS_PUB.ajlv_rec_type;
911 l_ajlv_rec OKL_TXL_ADJSTS_LNS_PUB.ajlv_rec_type;
912 lp_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
913 lx_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
914 l_tol_profile_name VARCHAR2(300);
915
916 -- rmunjulu 4622198
917 l_fact_synd_code FND_LOOKUPS.lookup_code%TYPE;
918 l_inv_acct_code OKC_RULES_B.rule_information1%TYPE;
919
920 l_total_amount NUMBER; -- rmunjulu 4917286
921 lpp_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type; -- rmunjulu 4917286
922 lxx_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type; -- rmunjulu 4917286
923
924 BEGIN
925 ---
926 --get the tolerance limit from profile
927 -- get the total balances of ARs for the contract
928 -- if total balance amount within the tolerance limit then
929 -- close balances
930 -- end if
931
932 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
933 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
934 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
935 'Begin(+)');
936 END IF;
937
938 -- *************
939 -- Check API version, initialize message list and create savepoint
940 -- *************
941 l_return_status := OKL_API.start_activity(
942 p_api_name => l_api_name,
943 p_pkg_name => G_PKG_NAME,
944 p_init_msg_list => p_init_msg_list,
945 l_api_version => l_api_version,
946 p_api_version => p_api_version,
947 p_api_type => '_PVT',
948 x_return_status => x_return_status);
949
950 -- Rollback if error setting activity for api
951 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
952 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
953 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
954 RAISE G_EXCEPTION_ERROR;
955 END IF;
956
957 -- get the total balances of ARs for the contract
958 OPEN k_balances_csr(p_khr_rec.id,p_sys_date);
959 FETCH k_balances_csr INTO l_total_amount_due;
960 CLOSE k_balances_csr;
961
962 -- set the total amount if it is null
963 IF l_total_amount_due IS NULL THEN
964 l_total_amount_due := 0;
965 END IF;
966
967 -- rmunjulu 4622198 SPECIAL_ACCNT Get special accounting details
968 OKL_SECURITIZATION_PVT.check_khr_ia_associated(
969 p_api_version => p_api_version
970 ,p_init_msg_list => OKL_API.G_FALSE
971 ,x_return_status => l_return_status
972 ,x_msg_count => x_msg_count
973 ,x_msg_data => x_msg_data
974 ,p_khr_id => p_khr_rec.id
975 ,p_scs_code => p_khr_rec.scs_code
976 ,p_trx_date => p_sys_date
977 ,x_fact_synd_code => l_fact_synd_code
978 ,x_inv_acct_code => l_inv_acct_code
979 );
980
981 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
982 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
983 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
984 RAISE G_EXCEPTION_ERROR;
985 END IF;
986
987 -- Check if total amount due is +ve else set message and exit
988 IF l_total_amount_due > 0 THEN
989
990 --get the tolerance limit from profile
991 FND_PROFILE.get('OKL_SMALL_BALANCE_TOLERANCE',l_tolerance_amt);
992
993 -- if no tolerance amt then assume tolerance amt = 0 ,
994 -- raise warning msg and proceed
995 -- RMUNJULU 07-APR-03 2883292 Changed IF to check for NULL instead of -1
996 IF l_tolerance_amt IS NULL THEN
997
998 -- get the profile option name
999 OPEN get_profile_name_csr;
1000 FETCH get_profile_name_csr INTO l_tol_profile_name;
1001 CLOSE get_profile_name_csr;
1002
1003 -- No tolerance amount found for closing of balances.
1004 OKL_API.set_message( p_app_name => G_APP_NAME,
1005 p_msg_name => 'OKL_AM_NO_TOL_AMT');
1006
1007 -- To set tolerance amount, set value for profile option PROFILE_NAME.
1008 OKL_API.set_message( p_app_name => G_APP_NAME,
1009 p_msg_name => 'OKL_AM_TOL_AMT',
1010 p_token1 => 'PROFILE_NAME',
1011 p_token1_value => l_tol_profile_name);
1012
1013 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1014 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1015 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1016 'NO TOLERANCE');
1017 END IF;
1018
1019 RAISE OKL_API.G_EXCEPTION_ERROR;
1020
1021 END IF;
1022
1023 -- IF total balance amount within the tolerance limit and amount due>0 then
1024 IF (l_total_amount_due <= l_tolerance_amt) THEN
1025 -- ******** GET PRODUCT ID *************** START ****************
1026 -- get the product id
1027 OPEN prod_id_csr(p_khr_rec.id);
1028 FETCH prod_id_csr INTO l_pdt_id;
1029 CLOSE prod_id_csr;
1030 -- raise error message if no pdt_id
1031 IF l_pdt_id IS NULL OR l_pdt_id = 0 THEN
1032 -- Error: Unable to create accounting entries because of a missing
1033 -- Product Type for the contract CONTRACT_NUMBER.
1034 OKL_API.set_message(
1035 p_app_name => G_APP_NAME,
1036 p_msg_name => 'OKL_AM_PRODUCT_ID_ERROR',
1037 p_token1 => 'CONTRACT_NUMBER',
1038 p_token1_value=> p_khr_rec.contract_number);
1039
1040 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1041 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1042 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1043 'product_id_error = '||l_return_status );
1044 END IF;
1045
1046 RAISE OKL_API.G_EXCEPTION_ERROR;
1047 END IF;
1048
1049 -- ******** GET PRODUCT ID *************** END ****************
1050
1051 -- ******** GET BAL WRITE OFF TRY ID *************** START ******
1052
1053 -- Get the transaction id for adjustments
1054 OKL_AM_UTIL_PVT.get_transaction_id(
1055 p_try_name => 'Balance Write off',
1056 x_return_status => l_return_status,
1057 x_try_id => l_writeoff_try_id);
1058
1059 -- Get the meaning of lookup BALANCE_WRITE_OFF
1060 l_trans_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
1061 p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
1062 p_lookup_code=> 'BALANCE_WRITE_OFF',
1063 p_validate_yn => 'Y');
1064
1065 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1066
1067 -- Message: Unable to find a transaction type for
1068 -- the transaction TRY_NAME
1069 OKL_API.set_message(
1070 p_app_name => G_APP_NAME,
1071 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
1072 p_token1 => 'TRY_NAME',
1073 p_token1_value => l_trans_meaning);
1074
1075 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1077 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1078 'balance_writeoff_trn_error = '||l_return_status );
1079 END IF;
1080
1081 END IF;
1082
1083 -- Raise exception to rollback this whole block
1084 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1085 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1086 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1087 RAISE OKL_API.G_EXCEPTION_ERROR;
1088 END IF;
1089
1090 -- *** GET CURRENCY CODES ***************************** START ****
1091
1092 -- Get the functional currency from AM_Util
1093 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency;
1094
1095 -- Get the contract currency code
1096 l_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(p_khr_rec.id);
1097
1098 -- *** GET CURRENCY CODES ***************************** END ****
1099
1100 -- *** CONVERT CURRENCIES **************************** START ****
1101
1102 -- Get the currency conversion details from ACCOUNTING_Util
1103 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
1104 p_khr_id => p_khr_rec.id,
1105 p_to_currency => l_functional_currency_code,
1106 p_transaction_date => p_sys_date, -- rmunjulu EDAT
1107 p_amount => l_hard_coded_amount,
1108 x_return_status => l_return_status,
1109 x_contract_currency => l_contract_currency_code,
1110 x_currency_conversion_type => l_currency_conversion_type,
1111 x_currency_conversion_rate => l_currency_conversion_rate,
1112 x_currency_conversion_date => l_currency_conversion_date,
1113 x_converted_amount => l_converted_amount);
1114 -- If error from OKL_ACCOUNTING_UTIL
1115 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1116
1117 -- Error occurred when creating accounting entries for
1118 -- transaction TRX_TYPE.
1119 OKL_API.set_message(
1120 p_app_name => G_APP_NAME,
1121 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
1122 p_token1 => 'TRX_TYPE',
1123 p_token1_value => l_trans_meaning);
1124
1125 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1126 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1127 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1128 'currency_conv_error = '||l_return_status );
1129 END IF;
1130
1131 RAISE OKL_API.G_EXCEPTION_ERROR;
1132
1133 END IF;
1134
1135 -- *** CONVERT CURRENCIES **************************** END ****
1136
1137 l_total_amount := 0; --rmunjulu 4917286
1138
1139 -- *** CREATE WRITEOFF TRANSACTION IN OKL_TRX_CONTRACTS ** START *
1140
1141 -- initialize the transaction rec
1142 lp_tcnv_rec.khr_id := p_khr_rec.id;
1143 lp_tcnv_rec.tcn_type := 'BWO';
1144 lp_tcnv_rec.try_id := l_writeoff_try_id;
1145 lp_tcnv_rec.currency_code := l_currency_code;
1146 lp_tcnv_rec.tsu_code := 'ENTERED';
1147 lp_tcnv_rec.date_transaction_occurred := p_sys_date;
1148
1149 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(
1150 p_api_version=> p_api_version,
1151 p_init_msg_list=> OKL_API.G_FALSE,
1152 x_return_status => l_return_status,
1153 x_msg_count => x_msg_count,
1154 x_msg_data => x_msg_data,
1155 p_tcnv_rec => lp_tcnv_rec,
1156 x_tcnv_rec => lx_tcnv_rec);
1157
1158 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1159
1160 -- Error occurred when creating adjustment records to write off balances.
1161 OKL_API.set_message( p_app_name => G_APP_NAME,
1162 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1163
1164 END IF;
1165
1166 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1167 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1168 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1169 'OKL_TRX_CONTRACTS_PUB.create_trx_contracts = '||l_return_status );
1170 END IF;
1171
1172 -- Raise exception to rollback this whole block
1173 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1174 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1175 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1176 RAISE OKL_API.G_EXCEPTION_ERROR;
1177 END IF;
1178
1179 -- *** CREATE WRITEOFF TRANSACTION IN OKL_TRX_CONTRACTS ** END **
1180
1181 -- *** CREATE ADJUSTMENT HEADER TRN IN OKL_TRX_ADJSTS ** START ***
1182 -- set the adjusts rec
1183 lp_adjv_rec.trx_status_code := 'WORKING'; -- tsu_code
1184 lp_adjv_rec.tcn_id := lx_tcnv_rec.id; -- ID of new Writeoff transaction
1185 -- adjustment_reason_code comes from OKL_ADJUSTMENT_REASON
1186 lp_adjv_rec.adjustment_reason_code := 'SMALL AMT REMAINING';
1187 lp_adjv_rec.apply_date := p_sys_date;
1188 lp_adjv_rec.gl_date := p_sys_date;
1189 --Bug 6316320 dpsingh start
1190 lp_adjv_rec.try_id := l_writeoff_try_id;
1191 --Bug 6316320 dpsingh end
1192 -- call the adjusts api
1193 OKL_TRX_AR_ADJSTS_PUB.insert_trx_ar_adjsts(
1194 p_api_version => p_api_version,
1195 p_init_msg_list => OKL_API.G_FALSE,
1196 x_return_status => l_return_status,
1197 x_msg_count => x_msg_count,
1198 x_msg_data => x_msg_data,
1199 p_adjv_rec => lp_adjv_rec,
1200 x_adjv_rec => lx_adjv_rec);
1201 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1202
1203 -- Error occurred when creating adjustment records to write off balances.
1204 OKL_API.set_message( p_app_name => G_APP_NAME,
1205 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1206
1207 END IF;
1208
1209 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1210 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1211 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1212 'OKL_TRX_AR_ADJSTS_PUB.insert_trx_ar_adjsts = '||l_return_status );
1213 END IF;
1214
1215 -- Raise exception to rollback this whole block
1216 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1217 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1218 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1219 RAISE OKL_API.G_EXCEPTION_ERROR;
1220 END IF;
1221
1222 -- *** CREATE ADJUSTMENT HEADER TRN IN OKL_TRX_ADJSTS ** END ****
1223
1224 -- loop thru AR balances for contract
1225 i := 1;
1226 FOR k_bal_lns_rec IN k_bal_lns_csr ( p_khr_rec.id,p_sys_date) LOOP
1227 -- *** CREATE TRN LINE IN OKL_TXL_ADJSTS_LN ***** START *******
1228
1229 -- set the rec for adjsts lns
1230 lp_ajlv_rec.adj_id := lx_adjv_rec.id;
1231 lp_ajlv_rec.til_id := k_bal_lns_rec.til_id;
1232 --Bug 6316320 dpsingh start
1233 lp_ajlv_rec.khr_id := p_khr_rec.id;
1234 lp_ajlv_rec.kle_id := k_bal_lns_rec.kle_id ;
1235 lp_ajlv_rec.sty_id := k_bal_lns_rec.stream_type_id;
1236 --Bug 6316320 dpsingh end
1237 IF k_bal_lns_rec.tld_id <> -999
1238 AND k_bal_lns_rec.tld_id IS NOT NULL
1239 AND k_bal_lns_rec.tld_id <> OKL_API.G_MISS_NUM THEN
1240 lp_ajlv_rec.tld_id := k_bal_lns_rec.tld_id;
1241 END IF;
1242
1243 lp_ajlv_rec.amount := k_bal_lns_rec.amount;
1244 lp_ajlv_rec.psl_id := k_bal_lns_rec.schedule_id;
1245
1246 l_total_amount := l_total_amount + k_bal_lns_rec.amount; --rmunjulu 4917286 -- keep track of total
1247
1248 --call the txl_lns_adjsts
1249 OKL_TXL_ADJSTS_LNS_PUB.insert_txl_adjsts_lns(
1250 p_api_version => p_api_version,
1251 p_init_msg_list => OKL_API.G_FALSE,
1252 x_return_status => l_return_status,
1253 x_msg_count => x_msg_count,
1254 x_msg_data => x_msg_data,
1255 p_ajlv_rec => lp_ajlv_rec,
1256 x_ajlv_rec => lx_ajlv_rec);
1257
1258 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1259 -- Error occurred when creating adjustment records to write
1260 -- off balances.
1261 OKL_API.set_message( p_app_name => G_APP_NAME,
1262 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1263 END IF;
1264
1265 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1267 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1268 'OKL_TRX_AR_ADJSTS_PUB.insert_txl_adjsts_lns = '||l_return_status );
1269 END IF;
1270
1271 -- Raise exception to rollback this whole block
1272 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1273 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1274 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1275 RAISE OKL_API.G_EXCEPTION_ERROR;
1276 END IF;
1277
1278 -- *** CREATE TRN LINE IN OKL_TXL_ADJSTS_LN ***** END *******
1279
1280 -- *** CREATE ACCOUNTING DISTRIBUTIONS ******* START ***********
1281
1282 -- do accounting entries to get code_combination_id
1283 --Bug 6316320 dpsingh start
1284 -- Set the tmpl_identify_tbl in parameter
1285 l_tmpl_identify_tbl(i).product_id := l_pdt_id;
1286 l_tmpl_identify_tbl(i).transaction_type_id := l_writeoff_try_id;
1287 l_tmpl_identify_tbl(i).memo_yn := 'N';
1288 l_tmpl_identify_tbl(i).prior_year_yn := 'N';
1289 l_tmpl_identify_tbl(i).stream_type_id := k_bal_lns_rec.stream_type_id;
1290
1291 -- Set the dist_info_tbl in parameter
1292 l_dist_info_tbl(i).source_id := lx_ajlv_rec.id;
1293 l_dist_info_tbl(i).source_table := 'OKL_TXL_ADJSTS_LNS_B';
1294 l_dist_info_tbl(i).accounting_date := p_sys_date;
1295 l_dist_info_tbl(i).gl_reversal_flag := 'N';
1296 l_dist_info_tbl(i).post_to_gl := 'N';
1297 l_dist_info_tbl(i).contract_id := p_khr_rec.id;
1298 l_dist_info_tbl(i).amount := k_bal_lns_rec.amount;
1299
1300 -- Set the p_dist_info_rec for currency code
1301 l_dist_info_tbl(i).currency_code := l_contract_currency_code;
1302
1303 -- If the functional currency code is different
1304 -- from contract currency code
1305 -- then set the rest of the currency conversion columns
1306 IF l_functional_currency_code <> l_contract_currency_code THEN
1307
1308 -- Set the p_dist_info_rec currency conversion columns
1309 l_dist_info_tbl(i).currency_conversion_type := l_currency_conversion_type;
1310 l_dist_info_tbl(i).currency_conversion_rate := l_currency_conversion_rate;
1311 l_dist_info_tbl(i).currency_conversion_date := l_currency_conversion_date;
1312
1313 END IF;
1314
1315 -- Set lp_acc_gen_primary_key_tbl for account generator
1316 OKL_ACC_CALL_PVT.okl_populate_acc_gen (
1317 p_contract_id => p_khr_rec.id,
1318 p_contract_line_id => NULL,
1319 x_acc_gen_tbl => lp_acc_gen_primary_key_tbl,
1320 x_return_status => l_return_status);
1321
1322 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1323
1324 -- Error occurred when creating adjustment records to write off balances.
1325 OKL_API.set_message( p_app_name => G_APP_NAME,
1326 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1327
1328 END IF;
1329
1330 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1332 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1333 'OKL_ACC_CALL_PVT.okl_populate_acc_gen = '||l_return_status );
1334 END IF;
1335
1336 -- Raise exception to rollback to savepoint for this block
1337 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1338 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1339 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1340 RAISE OKL_API.G_EXCEPTION_ERROR;
1341 END IF;
1342
1343 l_acc_gen_tbl(i).acc_gen_key_tbl := lp_acc_gen_primary_key_tbl;
1344 l_acc_gen_tbl(i).source_id := lx_ajlv_rec.id;
1345 -- rmunjulu 4622198 SPECIAL_ACCNT set the special accounting parameters
1346 l_tmpl_identify_tbl(i).factoring_synd_flag := l_fact_synd_code;
1347 l_tmpl_identify_tbl(i).investor_code := l_inv_acct_code;
1348 ajlv_id_tbl(i).id := lx_ajlv_rec.id ;
1349 ajlv_id_tbl(i).amount := k_bal_lns_rec.amount ;
1350 ajlv_id_tbl(i).ar_invoice_number := k_bal_lns_rec.ar_invoice_number ;
1351 ajlv_id_tbl(i).stream_meaning := k_bal_lns_rec.stream_meaning;
1352 END LOOP; -- balances res
1353 -- Call Okl_Account_Dist_Pub API to create accounting entries for this transaction
1354 -- Call new signature
1355 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
1356 p_api_version => p_api_version,
1357 p_init_msg_list => OKL_API.G_FALSE,
1358 x_return_status => l_return_status,
1359 x_msg_count => x_msg_count,
1360 x_msg_data => x_msg_data,
1361 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
1362 p_dist_info_tbl => l_dist_info_tbl,
1363 p_ctxt_val_tbl => l_ctxt_tbl,
1364 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
1365 x_template_tbl => l_template_out_tbl,
1366 x_amount_tbl => l_amount_out_tbl,
1367 p_trx_header_id => lx_adjv_rec.id,
1368 p_trx_header_table =>'OKL_TRX_AR_ADJSTS_B');
1369
1370 -- store the highest degree of error
1371 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1372 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1373 -- need to leave
1374 Okl_Api.set_message(p_app_name => g_app_name,
1375 p_msg_name => 'OKL_AGN_CRE_DIST_ERROR',
1376 p_token1 => g_contract_number_token,
1377 p_token1_value => p_khr_rec.contract_number);
1378 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1379 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1380 -- record that there was an error
1381 Okl_Api.set_message(p_app_name => g_app_name,
1382 p_msg_name => 'OKL_AGN_CRE_DIST_ERROR',
1383 p_token1 => g_contract_number_token,
1384 p_token1_value => p_khr_rec.contract_number);
1385 RAISE OKL_API.G_EXCEPTION_ERROR;
1386 END IF;
1387 END IF;
1388
1389 -- rmunjulu 4917286 update writeoff transaction with total amount
1390 -- *** UPDATE WRITEOFF TRANSACTION IN OKL_TRX_CONTRACTS ** START *
1391 lpp_tcnv_rec.id := lx_tcnv_rec.id;
1392 lpp_tcnv_rec.amount := l_total_amount;
1393
1394 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
1395 p_api_version=> p_api_version,
1396 p_init_msg_list=> OKL_API.G_FALSE,
1397 x_return_status => l_return_status,
1398 x_msg_count => x_msg_count,
1399 x_msg_data => x_msg_data,
1400 p_tcnv_rec => lpp_tcnv_rec,
1401 x_tcnv_rec => lxx_tcnv_rec);
1402
1403 -- Raise exception to rollback this whole block
1404 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1405 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1406 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1407 RAISE OKL_API.G_EXCEPTION_ERROR;
1408 END IF;
1409
1410 OPEN get_account_derivation_meth;
1411 FETCH get_account_derivation_meth INTO l_account_derivation;
1412 CLOSE get_account_derivation_meth;
1413
1414 -- *** CREATE ACCOUNTING DISTRIBUTIONS ******* END ***********
1415 FOR i IN ajlv_id_tbl.FIRST..ajlv_id_tbl.LAST
1416 LOOP
1417 IF l_account_derivation = 'ATS' THEN
1418 -- *** GET CCID FROM ACCOUNTING DISTRIBUTIONS **** START *******
1419 -- Get the first code_combination_id for the transaction
1420 -- from OKL_TRNS_ACC_DSTRS_V
1421 OPEN code_combination_id_csr(ajlv_id_tbl(i).id, 'OKL_TXL_ADJSTS_LNS_B');
1422 FETCH code_combination_id_csr INTO l_code_combination_id;
1423 CLOSE code_combination_id_csr;
1424 -- if code_combination_id not found then raise error
1425 IF l_code_combination_id = -1 OR l_code_combination_id IS NULL THEN
1426
1427 -- Error: Unable to process small balance
1428 -- adjustments because of a missing Code Combination ID for the
1429 -- contract CONTRACT_NUMBER.
1430 OKL_API.set_message(
1431 p_app_name => G_APP_NAME,
1432 p_msg_name => 'OKL_AM_CODE_CMB_ERROR',
1433 p_token1 => 'CONTRACT_NUMBER',
1434 p_token1_value=> p_khr_rec.contract_number);
1435
1436 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1437 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1438 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1439 'code_comb_err = '||l_return_status );
1440 END IF;
1441
1442 RAISE OKL_API.G_EXCEPTION_ERROR;
1443
1444 END IF;
1445
1446 -- *** GET CCID FROM ACCOUNTING DISTRIBUTIONS **** END *******
1447
1448 -- ******** UPDATE TRN LINE WITH CCID ************* START ******
1449
1450 lp_ajlv_rec := l_ajlv_rec; -- Empty the rec
1451
1452 -- Set the rec with CCID got from accounting distibutions
1453 lp_ajlv_rec.id := ajlv_id_tbl(i).id;
1454 lp_ajlv_rec.code_combination_id := l_code_combination_id;
1455
1456 lx_ajlv_rec := l_ajlv_rec; -- Empty the rec
1457
1458 --call the txl_lns_adjsts
1459 OKL_TXL_ADJSTS_LNS_PUB.update_txl_adjsts_lns(
1460 p_api_version => p_api_version,
1461 p_init_msg_list => OKL_API.G_FALSE,
1462 x_return_status => l_return_status,
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data,
1465 p_ajlv_rec => lp_ajlv_rec,
1466 x_ajlv_rec => lx_ajlv_rec);
1467
1468 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1469 -- Error occurred when creating adjustment records to write
1470 -- off balances.
1471 OKL_API.set_message( p_app_name => G_APP_NAME,
1472 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1473 END IF;
1474
1475 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1476 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1477 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1478 'OKL_TXL_ADJSTS_LNS_PUB.update_txl_adjsts_lns = '||l_return_status );
1479 END IF;
1480
1481 -- Raise exception to rollback this whole block
1482 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1483 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1484 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1485 RAISE OKL_API.G_EXCEPTION_ERROR;
1486 END IF;
1487 END IF;
1488 -- ******** UPDATE TRN LINE WITH CCID ************* END ******
1489 -- Format the adjustment amt
1490 l_formatted_adj_amt := OKL_ACCOUNTING_UTIL.format_amount(
1491 ajlv_id_tbl(i).amount,
1492 l_currency_code);
1493
1494 -- Append adjustment amt with currency code
1495 l_formatted_adj_amt := l_formatted_adj_amt || ' ' ||l_currency_code;
1496
1497 -- Adjustment transaction for AR invoice AR_INVOICE_NUM of amount AMOUNT
1498 -- has been created.
1499 OKL_API.set_message(
1500 p_app_name => G_APP_NAME,
1501 p_msg_name => 'OKL_AM_ACC_ENT_AR_INV_MSG',
1502 p_token1 => 'AR_INVOICE_NUM',
1503 p_token1_value => ajlv_id_tbl(i).ar_invoice_number,
1504 p_token2 => 'AMOUNT',
1505 p_token2_value => l_formatted_adj_amt);
1506
1507 -- Accounting entries created for transaction type TRX_TYPE
1508 -- and stream type STREAM_TYPE.
1509 OKL_API.set_message(
1510 p_app_name => G_APP_NAME,
1511 p_msg_name => 'OKL_AM_ACC_ENT_CREATED_MSG',
1512 p_token1 => 'TRX_TYPE',
1513 p_token1_value => l_trans_meaning,
1514 p_token2 => 'STREAM_TYPE',
1515 p_token2_value => ajlv_id_tbl(i).stream_meaning);
1516 END LOOP;
1517 ELSE --(cannot close all balances since tolerance amt is less)
1518
1519 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1520 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1521 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1522 'cannot_close_bal = '||'E' );
1523 END IF;
1524
1525 -- Get the currency code for contract
1526 l_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(p_khr_rec.id);
1527
1528 -- Format the balance amt
1529 l_formatted_bal_amt := OKL_ACCOUNTING_UTIL.format_amount(l_total_amount_due,l_currency_code);
1530
1531 -- Append balance amt with currency code
1532 l_formatted_bal_amt := l_formatted_bal_amt || ' ' ||l_currency_code;
1533
1534 -- Format the tolerance amt
1535 l_formatted_tol_amt := OKL_ACCOUNTING_UTIL.format_amount(l_tolerance_amt,l_currency_code);
1536
1537 -- Append tolerance amt with currency code
1538 l_formatted_tol_amt := l_formatted_tol_amt || ' ' ||l_currency_code;
1539
1540 -- Outstanding balance BALANCE_AMT exceeds Tolerance Amount TOLERANCE_AMT.
1541 OKL_API.set_message( p_app_name => G_APP_NAME,
1542 p_msg_name => 'OKL_AM_BAL_GTR_TOL',
1543 p_token1 => 'BALANCE_AMT',
1544 p_token1_value => l_formatted_bal_amt,
1545 p_token2 => 'TOLERANCE_AMT',
1546 p_token2_value => l_formatted_tol_amt);
1547
1548 RAISE OKL_API.G_EXCEPTION_ERROR;
1549 END IF;
1550 ELSE
1551
1552 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1553 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1554 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1555 'no_balance = '||'E' );
1556 END IF;
1557
1558 -- No outstanding balance.
1559 OKL_API.set_message( p_app_name => G_APP_NAME,
1560 p_msg_name => 'OKL_AM_BAL_TOT_ZERO');
1561
1562 RAISE OKL_API.G_EXCEPTION_ERROR;
1563 END IF;
1564
1565 x_return_status := l_return_status;
1566
1567 -- End Activity
1568 OKL_API.end_activity (x_msg_count, x_msg_data);
1569
1570 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1571 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1572 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1573 'End(-)');
1574 END IF;
1575
1576 EXCEPTION
1577 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1578 IF k_balances_csr%ISOPEN THEN
1579 CLOSE k_balances_csr;
1580 END IF;
1581 IF k_bal_lns_csr%ISOPEN THEN
1582 CLOSE k_bal_lns_csr;
1583 END IF;
1584 IF code_combination_id_csr%ISOPEN THEN
1585 CLOSE code_combination_id_csr;
1586 END IF;
1587
1588 x_return_status := OKL_API.handle_exceptions(
1589 p_api_name => l_api_name,
1590 p_pkg_name => G_PKG_NAME,
1591 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data,
1594 p_api_type => '_PVT');
1595
1596 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1597 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1598 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1599 'EXP - ERROR');
1600 END IF;
1601
1602
1603 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1604 IF k_balances_csr%ISOPEN THEN
1605 CLOSE k_balances_csr;
1606 END IF;
1607 IF k_bal_lns_csr%ISOPEN THEN
1608 CLOSE k_bal_lns_csr;
1609 END IF;
1610 IF code_combination_id_csr%ISOPEN THEN
1611 CLOSE code_combination_id_csr;
1612 END IF;
1613
1614 x_return_status := OKL_API.handle_exceptions(
1615 p_api_name => l_api_name,
1616 p_pkg_name => G_PKG_NAME,
1617 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1618 x_msg_count => x_msg_count,
1619 x_msg_data => x_msg_data,
1620 p_api_type => '_PVT');
1621
1622 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1624 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1625 'EXP - UNEXCP ERROR');
1626 END IF;
1627
1628 WHEN OTHERS THEN
1629 IF k_balances_csr%ISOPEN THEN
1630 CLOSE k_balances_csr;
1631 END IF;
1632 IF k_bal_lns_csr%ISOPEN THEN
1633 CLOSE k_bal_lns_csr;
1634 END IF;
1635 IF code_combination_id_csr%ISOPEN THEN
1636 CLOSE code_combination_id_csr;
1637 END IF;
1638
1639 x_return_status := OKL_API.handle_exceptions(
1640 p_api_name => l_api_name,
1641 p_pkg_name => G_PKG_NAME,
1642 p_exc_name => 'OTHERS',
1643 x_msg_count => x_msg_count,
1644 x_msg_data => x_msg_data,
1645 p_api_type => '_PVT');
1646
1647 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1648 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1649 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1650 'EXP - OTHERS');
1651 END IF;
1652
1653 END write_off_balances;
1654
1655 -- Start of comments
1656 --
1657 -- Procedure Name : do_write_off_balances
1658 -- Description : procedure to terminate Vendor Program
1659 -- Business Rules :
1660 -- Parameters :
1661 -- Version : 1.0
1662 -- History : RMUNJULU Created
1663 --
1664 -- End of comments
1665 PROCEDURE do_write_off_balances(
1666 p_api_version IN NUMBER,
1667 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1668 x_return_status OUT NOCOPY VARCHAR2,
1669 x_msg_count OUT NOCOPY NUMBER,
1670 x_msg_data OUT NOCOPY VARCHAR2,
1671 p_khr_rec IN khr_rec_type,
1672 p_control_flag IN VARCHAR2 DEFAULT NULL) IS
1673
1674 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1675 l_overall_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1676 l_trx_id NUMBER;
1677 l_pdt_id NUMBER;
1678 l_khr_rec khr_rec_type := p_khr_rec;
1679 l_sys_date DATE;
1680 l_trn_already_yn VARCHAR2(1);
1681 l_end_date DATE;
1682 l_start_date DATE;
1683 l_type VARCHAR2(300);
1684 l_status VARCHAR2(300);
1685 l_control_flag VARCHAR2(300);
1686 l_valid_gl_date DATE;
1687 l_step VARCHAR2(50);
1688 l_validate_status VARCHAR2(3);
1689 l_update_status VARCHAR2(3);
1690
1691 lx_error_rec OKL_API.error_rec_type;
1692 l_msg_idx INTEGER := G_FIRST;
1693 l_msg_tbl msg_tbl_type;
1694 l_api_name VARCHAR2(30) := 'do_write_off_balances';
1695 l_api_version CONSTANT NUMBER := G_API_VERSION;
1696
1697 G_EXCEPTION EXCEPTION;
1698
1699 BEGIN
1700
1701 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1702 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1703 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1704 'Begin(+)');
1705 END IF;
1706
1707 -- *************
1708 -- Check API version, initialize message list and create savepoint
1709 -- *************
1710 l_return_status := OKL_API.start_activity(
1711 p_api_name => l_api_name,
1712 p_pkg_name => G_PKG_NAME,
1713 p_init_msg_list => p_init_msg_list,
1714 l_api_version => l_api_version,
1715 p_api_version => p_api_version,
1716 p_api_type => '_PVT',
1717 x_return_status => x_return_status);
1718
1719 -- Rollback if error setting activity for api
1720 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1721 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1722 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1723 RAISE G_EXCEPTION_ERROR;
1724 END IF;
1725
1726 SELECT sysdate INTO l_sys_date FROM DUAL;
1727
1728 -- populate khr rec
1729 populate_khr_prg(
1730 p_khr_rec => p_khr_rec,
1731 x_khr_rec => l_khr_rec,
1732 x_return_status => l_return_status);
1733
1734 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1735 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1736 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1737 'populate_khr_prg = '||l_return_status );
1738 END IF;
1739
1740 IF l_return_status <> G_RET_STS_SUCCESS THEN
1741 l_khr_rec := p_khr_rec;
1742 END IF;
1743
1744 -- raise exception if api failed
1745 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1746 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1747 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1748 RAISE G_EXCEPTION_ERROR;
1749 END IF;
1750
1751 -- validate khr if single contract
1752 validate_khr_prg(
1753 p_khr_rec => l_khr_rec,
1754 p_control_flag => p_control_flag,
1755 x_return_status => l_return_status);
1756
1757 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1758 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1759 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1760 'validate_khr_prg = '||l_return_status );
1761 END IF;
1762
1763 -- raise exception if api failed
1764 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1765 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1766 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1767 RAISE G_EXCEPTION_ERROR;
1768 END IF;
1769
1770 -- write off balances
1771 write_off_balances(
1772 p_api_version => p_api_version,
1773 p_init_msg_list => OKL_API.G_FALSE,
1774 x_return_status => l_return_status,
1775 x_msg_count => x_msg_count,
1776 x_msg_data => x_msg_data,
1777 p_khr_rec => l_khr_rec,
1778 p_sys_date => l_sys_date,
1779 p_control_flag => p_control_flag);
1780
1781 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1782 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1783 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1784 'write_off_balances = '||l_return_status );
1785 END IF;
1786
1787 -- raise exception if api failed
1788 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1789 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1790 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1791 RAISE G_EXCEPTION_ERROR;
1792 END IF;
1793
1794 -- Set the output log if request from BATCH
1795 IF p_control_flag LIKE 'BATCH%' THEN
1796
1797 fnd_output (
1798 p_khr_rec => l_khr_rec,
1799 p_control_flag => 'PROCESSED');
1800
1801 END IF;
1802
1803 -- set return status
1804 x_return_status := l_return_status;
1805
1806 -- End Activity
1807 OKL_API.end_activity (x_msg_count, x_msg_data);
1808
1809 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1810 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1811 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1812 'End(-)');
1813 END IF;
1814
1815 EXCEPTION
1816
1817 WHEN G_EXCEPTION_ERROR THEN
1818
1819 -- Set the output log if request from BATCH
1820 IF p_control_flag LIKE 'BATCH%' THEN
1821 fnd_output (
1822 p_khr_rec => l_khr_rec,
1823 p_control_flag => 'ERROR');
1824 END IF;
1825
1826 x_return_status := OKL_API.handle_exceptions(
1827 p_api_name => l_api_name,
1828 p_pkg_name => G_PKG_NAME,
1829 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1830 x_msg_count => x_msg_count,
1831 x_msg_data => x_msg_data,
1832 p_api_type => '_PVT');
1833
1834 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1835 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1836 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1837 'EXP - G_EXCEPTION_ERROR');
1838 END IF;
1839
1840 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1841
1842 -- Set the output log if request from BATCH
1843 IF p_control_flag LIKE 'BATCH%' THEN
1844 fnd_output (
1845 p_khr_rec => l_khr_rec,
1846 p_control_flag => 'ERROR');
1847 END IF;
1848
1849 x_return_status := OKL_API.handle_exceptions(
1850 p_api_name => l_api_name,
1851 p_pkg_name => G_PKG_NAME,
1852 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1853 x_msg_count => x_msg_count,
1854 x_msg_data => x_msg_data,
1855 p_api_type => '_PVT');
1856
1857 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1858 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1859 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1860 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1861 END IF;
1862
1863 WHEN OTHERS THEN
1864
1865 -- Set the output log if request from BATCH
1866 IF p_control_flag LIKE 'BATCH%' THEN
1867 fnd_output (
1868 p_khr_rec => l_khr_rec,
1869 p_control_flag => 'ERROR');
1870 END IF;
1871
1872 x_return_status := OKL_API.handle_exceptions(
1873 p_api_name => l_api_name,
1874 p_pkg_name => G_PKG_NAME,
1875 p_exc_name => 'OTHERS',
1876 x_msg_count => x_msg_count,
1877 x_msg_data => x_msg_data,
1878 p_api_type => '_PVT');
1879
1880 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1881 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1882 'OKL_AM_BAL_WRITEOFF_PVT.do_write_off_balances.',
1883 'EXP - OTHERS');
1884 END IF;
1885
1886 END do_write_off_balances;
1887
1888 -- Start of comments
1889 --
1890 -- Procedure Name : concurrent_bal_writeoff_prg
1891 -- Description : This procedure is called by concurrent manager to do balance writeoff
1892 -- for terminated/expired contracts. When running the concurrent
1893 -- manager request, a request can be made for a single contract
1894 -- or else all the terminated contracts will be picked
1895 -- If No End Date is Passed Defaulted to SysDate
1896 -- Business Rules :
1897 -- Parameters :
1898 -- Version : 1.0
1899 -- History : RMUNJULU Created
1900 --
1901 -- End of comments
1902 PROCEDURE concurrent_bal_writeoff_prg(
1903 errbuf OUT NOCOPY VARCHAR2,
1904 retcode OUT NOCOPY VARCHAR2,
1905 p_api_version IN VARCHAR2,
1906 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1907 p_khr_id IN VARCHAR2 DEFAULT NULL,
1908 p_date IN VARCHAR2 DEFAULT NULL) IS
1909
1910 -- Get the terminated/expired contracts for the org
1911 CURSOR get_expired_khr_csr (p_date IN DATE) IS
1912 SELECT CHR.id,
1913 CHR.contract_number contract_number
1914 FROM OKC_K_HEADERS_B CHR,
1915 OKL_K_HEADERS KHR
1916 WHERE KHR.ID = CHR.ID
1917 AND CHR.sts_code IN ('TERMINATED','EXPIRED') -- TERMINATED/EXPIRED
1918 AND trunc(CHR.date_terminated) <= TRUNC(p_date) -- Ended
1919 AND CHR.id NOT IN (-- balance writeoffs already done when contract was fully terminated
1920 SELECT TRN.khr_id
1921 FROM OKL_TRX_AR_ADJSTS_V BAL,
1922 OKL_TRX_CONTRACTS TRN
1923 WHERE BAL.tcn_id = TRN.id
1924 AND TRN.tcn_type = 'TMT' -- full termination
1925 AND TRN.khr_id = CHR.id
1926 -- No need to check for actual step as tcn_id is recorded in AR_ADJSTS table
1927 )
1928 AND CHR.id NOT IN (-- new balance writeoffs done as part of this concurrent program
1929 SELECT TRN.khr_id
1930 FROM OKL_TRX_CONTRACTS TRN
1931 WHERE TRN.tcn_type = 'BWO' -- new transaction type SEED ***
1932 AND TRN.khr_id = CHR.id
1933 )
1934 AND 0 < ( -- Check that invoices with balances greater than 0 exists and dated before today
1935 /* rmunjulu R12 Fixes - Billing fixes -- replaced with new select below
1936 SELECT sum(BPD.amount_due_remaining)
1937 FROM OKL_BPD_LEASING_PAYMENT_TRX_V BPD
1938 WHERE BPD.invoice_date <= sysdate
1939 AND BPD.contract_id = CHR.id
1940 */
1941 -- rmunjulu R12 Fixes - Billing fixes -- changes to this select as old bpd view does not work anymore
1942 SELECT sum(RACTRL.amount_due_remaining)
1943 FROM OKL_BPD_TLD_AR_LINES_V RACTRL
1944 WHERE RACTRL.invoice_date <= sysdate
1945 AND RACTRL.khr_id = CHR.id
1946 );
1947
1948
1949 l_return_status VARCHAR2(3);
1950 l_msg_count NUMBER;
1951 l_msg_data VARCHAR2(2000);
1952 l_date DATE;
1953 l_api_version NUMBER;
1954 l_khr_id NUMBER;
1955 l_khr_rec khr_rec_type;
1956
1957 TYPE get_expired_khr_tbl_type IS TABLE OF get_expired_khr_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1958 get_expired_khr_tbl get_expired_khr_tbl_type;
1959 i NUMBER;
1960
1961 BEGIN
1962
1963 -- Initialize message list
1964 OKL_API.init_msg_list('T');
1965
1966 -- Set Processing date
1967 IF p_date IS NULL THEN
1968 l_date := sysdate;
1969 ELSE
1970 l_date := TO_DATE(p_date, 'MM/DD/YYYY');
1971 IF l_date > TRUNC(sysdate) THEN
1972 G_ERROR := 'Y';
1973 END IF;
1974 END IF;
1975
1976 -- If no error then
1977 IF G_ERROR <> 'Y' THEN
1978
1979 G_KHR_ENDED_BY_DATE := TRUNC(l_date);
1980
1981 l_api_version := TO_NUMBER(p_api_version);
1982 l_khr_id := TO_NUMBER(p_khr_id);
1983
1984 -- Check if a single IA termination request
1985 IF l_khr_id IS NOT NULL THEN
1986
1987 l_khr_rec.id := l_khr_id;
1988
1989 -- do balance writeoff
1990 do_write_off_balances(
1991 p_api_version => l_api_version,
1992 p_init_msg_list => p_init_msg_list,
1993 x_return_status => l_return_status,
1994 x_msg_count => l_msg_count,
1995 x_msg_data => l_msg_data,
1996 p_khr_rec => l_khr_rec,
1997 p_control_flag => 'BATCH_SINGLE');
1998
1999 ELSE -- no contract passed
2000
2001 -- Do a bulk fetch of all eligible contracts
2002 OPEN get_expired_khr_csr (G_KHR_ENDED_BY_DATE);
2003 FETCH get_expired_khr_csr BULK COLLECT INTO get_expired_khr_tbl;
2004 CLOSE get_expired_khr_csr;
2005
2006 -- for each contract call do writeoff
2007 IF get_expired_khr_tbl.count > 0 THEN
2008 FOR i IN get_expired_khr_tbl.first..get_expired_khr_tbl.last LOOP
2009
2010 l_khr_rec.id := get_expired_khr_tbl(i).id;
2011 l_khr_rec.contract_number := get_expired_khr_tbl(i).contract_number;
2012
2013 -- Do balance writeoff
2014 do_write_off_balances(
2015 p_api_version => l_api_version,
2016 p_init_msg_list => p_init_msg_list,
2017 x_return_status => l_return_status,
2018 x_msg_count => l_msg_count,
2019 x_msg_data => l_msg_data,
2020 p_khr_rec => l_khr_rec,
2021 p_control_flag => 'BATCH_MULTIPLE');
2022
2023 END LOOP;
2024 END IF;
2025 END IF;
2026 END IF;
2027
2028 -- Create the Output Report
2029 create_report;
2030
2031 EXCEPTION
2032
2033 WHEN OTHERS THEN
2034 -- Set the oracle error message
2035 OKL_API.set_message(
2036 p_app_name => G_APP_NAME_1,
2037 p_msg_name => G_UNEXPECTED_ERROR,
2038 p_token1 => G_SQLCODE_TOKEN,
2039 p_token1_value => SQLCODE,
2040 p_token2 => G_SQLERRM_TOKEN,
2041 p_token2_value => SQLERRM);
2042
2043 END concurrent_bal_writeoff_prg;
2044
2045 END OKL_AM_BAL_WRITEOFF_PVT;