[Home] [Help]
PACKAGE BODY: APPS.OKL_VENDOR_REFUND_PVT
Source
1 PACKAGE BODY OKL_VENDOR_REFUND_PVT AS
2 /* $Header: OKLRRFDB.pls 120.5 2007/10/17 17:41:35 vdamerla ship $ */
3 PROCEDURE get_error_message(p_all_message
4 OUT nocopy error_message_type)
5 IS
6 l_msg_text VARCHAR2(32627);
7 l_msg_count NUMBER ;
8 BEGIN
9 l_msg_count := fnd_msg_pub.count_msg;
10 FOR i IN 1..l_msg_count
11 LOOP
12 fnd_msg_pub.get
13 (p_data => p_all_message(i),
14 p_msg_index_out => l_msg_count,
15 p_encoded => fnd_api.g_false,
16 p_msg_index => fnd_msg_pub.g_next
17 );
18 END LOOP;
19 EXCEPTION
20 WHEN OTHERS THEN
21 NULL;
22 END get_error_message;
23
24 Procedure Update_cure_amounts(
25 p_contract_id IN NUMBER
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2 )
29 IS
30
31 CURSOR c_get_cure_amts (p_contract_id IN NUMBER)
32 IS
33 SELECT cure_amount_id
34 ,object_version_number
35 FROM okl_cure_amounts
36 WHERE chr_id = p_contract_id
37 AND STATUS = 'CURESINPROGRESS';
38
39 l_camv_tbl OKL_cure_amounts_pub.camv_tbl_type;
40 x_camv_tbl OKL_cure_amounts_pub.camv_tbl_type;
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(32627);
43 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
44 next_row INTEGER;
45 l_error_msg_tbl error_message_type;
46
47 BEGIN
48 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : START ');
49 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : p_contract_id : '||p_contract_id);
50
51 SAVEPOINT UPDATE_CURE_AMOUNTS;
52 FND_MSG_PUB.initialize;
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Calling Cure Amount Update Api');
55
56 --update cure amounts table
57 FOR i in c_get_cure_amts (p_contract_id)
58 LOOP
59 next_row := nvl(l_camv_tbl.LAST,0) +1;
60 l_camv_tbl(next_row).cure_amount_id := i.cure_amount_id;
61 l_camv_tbl(next_row).object_version_number := i.object_version_number;
62 l_camv_tbl(next_row).STATUS :='MOVED_TO_REFUNDS';
63
64 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : l_camv_tbl(next_row).cure_amount_id : '||l_camv_tbl(next_row).cure_amount_id);
65
66 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID),
67 DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID),
68 DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID),
69 DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
70 INTO l_camv_tbl(next_row).request_id,
71 l_camv_tbl(next_row).program_application_id,
72 l_camv_tbl(next_row).program_id,
73 l_camv_tbl(next_row).program_update_date
74 FROM DUAL;
75
76 END LOOP;
77 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
78 'no of records to be updated in Cure amounts '||l_camv_tbl.COUNT);
79 IF (l_camv_tbl.COUNT > 0)
80 THEN
81 OKL_cure_amounts_pub.update_cure_amounts
82 ( p_api_version => 1
83 ,p_init_msg_list => 'T'
84 ,x_return_status => l_return_status
85 ,x_msg_count => l_msg_count
86 ,x_msg_data => l_msg_data
87 ,p_camv_tbl => l_camv_tbl
88 ,x_camv_tbl => x_camv_tbl
89 );
90 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : OKL_cure_amounts_pub.update_cure_amounts : '||l_return_status);
91
92 IF (l_return_status <> FND_Api.G_RET_STS_SUCCESS )
93 THEN
94 RAISE Fnd_Api.G_EXC_ERROR;
95 ELSE
96 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
97 'Updation of cure amounts is Successful');
98 END IF;
99 x_return_status := l_return_status;
100 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
101 p_data => x_msg_data );
102 END IF;
103
104 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : END ');
105
106 EXCEPTION
107 WHEN Fnd_Api.G_EXC_ERROR THEN
108 ROLLBACK TO UPDATE_CURE_AMOUNTS;
109 x_return_status := Fnd_Api.G_RET_STS_ERROR;
110 x_msg_count := l_msg_count ;
111 x_msg_data := l_msg_data ;
112 Fnd_Msg_Pub.count_and_get(
113 p_count => x_msg_count
114 ,p_data => x_msg_data);
115 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
116 ROLLBACK TO UPDATE_CURE_AMOUNTS;
117 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
118 x_msg_count := l_msg_count ;
119 x_msg_data := l_msg_data ;
120 Fnd_Msg_Pub.count_and_get(
121 p_count => x_msg_count
122 ,p_data => x_msg_data);
123 WHEN OTHERS THEN
124 ROLLBACK TO UPDATE_CURE_AMOUNTS;
125 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
126 x_msg_count := l_msg_count ;
127 x_msg_data := l_msg_data ;
128 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_VENDOR_REFUND_PVT','UPDATE_CURE_AMOUNTS');
129 Fnd_Msg_Pub.count_and_get(
130 p_count => x_msg_count
131 ,p_data => x_msg_data);
132 End update_cure_amounts;
133
134 ---------------------------------------------------------------------------
135 -- PROCEDURE CALC_CURE_REFUND
136 ---------------------------------------------------------------------------
137 PROCEDURE CALC_CURE_REFUND(
138 p_api_version IN NUMBER,
139 p_init_msg_list IN VARCHAR2 DEFAULT FND_api.G_FALSE,
140 p_contract_id IN NUMBER,
141 p_contract_number IN VARCHAR2,
142 p_program_id IN NUMBER,
143 p_rule_group_code IN VARCHAR2,
144 p_vendor_id IN VARCHAR2,
145 p_times_cured IN NUMBER,
146 x_cure_amount OUT NOCOPY NUMBER,
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_msg_count OUT NOCOPY NUMBER,
149 x_msg_data OUT NOCOPY VARCHAR2
150 )
151 IS
152
153 l_return_status VARCHAR2(1) := FND_Api.G_RET_STS_SUCCESS;
154 l_repurchase_amount NUMBER := 0;
155 l_cure_amount NUMBER := 0;
156 l_payments_remaining NUMBER := 0;
157 l_effective_date DATE;
158 l_negotiated_amount NUMBER := 0;
159 l_received_amount NUMBER := 0;
160 l_outstanding_amount NUMBER := 0;
161 l_delinquent_amount NUMBER := 0;
162 l_cures_in_possession NUMBER := 0;
163
164 l_camv_rec OKL_cure_amounts_pub.camv_rec_type;
165 x_camv_rec OKL_cure_amounts_pub.camv_rec_type;
166 l_crsv_rec OKL_cure_rfnd_stage_pub.crsv_rec_type;
167 x_crsv_rec OKL_cure_rfnd_stage_pub.crsv_rec_type;
168 l_msg_count NUMBER;
169 l_msg_data VARCHAR2(32627);
170 l_error_msg_tbl error_message_type;
171
172 Cursor c_calc_refund_csr (p_contract_id IN NUMBER) is
173 select nvl(sum(negotiated_amount),0), nvl(sum(received_amount),0)
174 from okl_cure_amounts
175 where chr_id =p_contract_id
176 and nvl(negotiated_amount,0) > 0
177 and nvl(received_amount,0) > 0
178 and status ='CURESINPROGRESS';
179
180 BEGIN
181 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : START ');
182 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : p_contract_id : '||p_contract_id);
183 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : p_contract_number : '||p_contract_number);
184 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : p_program_id : '||p_program_id);
185 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : p_rule_group_code : '||p_rule_group_code);
186 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : p_vendor_id : '||p_vendor_id);
187 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : p_times_cured : '||p_times_cured);
188
189 SAVEPOINT CALC_CURE_REFUND;
190
191 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
192 'Calc refund started for ' || p_contract_number);
193 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Before Inserting Cure Amounts');
194
195 OPEN c_calc_refund_csr(p_contract_id);
196 FETCH c_calc_refund_csr INTO l_negotiated_amount, l_received_amount;
197 CLOSE c_calc_refund_csr;
198
199 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : l_negotiated_amount : '||l_negotiated_amount);
200 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : l_received_amount : '||l_received_amount);
201
202 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_negotiated_amount'||l_negotiated_amount);
203 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'l_received_amount'||l_received_amount);
204
205 IF ((l_negotiated_amount > 0)
206 AND
207 (l_received_amount > 0)
208 AND
209 (l_received_amount >= l_negotiated_amount))
210 THEN
211 -- Update status to 'MOVED_TO_REFUNDS' FOR previous contracts.
212 Update_cure_amounts( p_contract_id =>p_contract_id,
213 x_return_status =>l_return_status,
214 x_msg_count =>l_msg_count,
215 x_msg_data =>l_msg_data );
216
217 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : Update_cure_amounts : '||l_return_status);
218
219 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
220 THEN
221 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ,
222 'Error Updating Cure amounts Table for contract '|| p_contract_number );
223 GET_ERROR_MESSAGE(l_error_msg_tbl);
224 IF (l_error_msg_tbl.COUNT > 0)
225 THEN
226 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
227 LOOP
228 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, l_error_msg_tbl(i));
229 END LOOP;
230 END IF; --end of l_error_msg_tbl
231 END IF; -- update_cure_amounts
232
233 -- Populate the data in OKL_cure_refunds_stage entity for the contract.
234 l_crsv_rec.chr_id := p_contract_id;
235 l_crsv_rec.status := 'ENTERED';
236 l_crsv_rec.negotiated_amount := l_negotiated_amount;
237 l_crsv_rec.received_amount := l_received_amount;
238 l_crsv_rec.vendor_id := p_vendor_id;
239 l_crsv_rec.object_version_number := 1.0;
240
241 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID),
242 DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID),
243 DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID),
244 DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
245 INTO l_crsv_rec.request_id,
246 l_crsv_rec.program_application_id,
247 l_crsv_rec.program_id,
248 l_crsv_rec.program_update_date
249 FROM DUAL;
250
251 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Before Inserting Cure Refunds');
252
253 OKL_cure_rfnd_stage_pub.insert_cure_refunds
254 ( p_api_version => p_api_version
255 ,p_init_msg_list => p_init_msg_list
256 ,x_return_status => l_return_status
257 ,x_msg_count => l_msg_count
258 ,x_msg_data => l_msg_data
259 ,p_crsv_rec => l_crsv_rec
260 ,x_crsv_rec => x_crsv_rec);
261
262 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : OKL_cure_rfnd_stage_pub.insert_cure_refunds : '||l_return_status);
263 IF (l_return_status <> FND_Api.G_RET_STS_SUCCESS )
264 THEN
265 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM, ' Error Inserting Cure Refunds');
266 RAISE Fnd_Api.G_EXC_ERROR;
267 ELSE
268 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
269 'Done inserting cure refunds, cure received amount is '||l_received_amount || 'and negotiated amount is'||l_negotiated_amount);
270 x_cure_amount:=l_received_amount; -- bug 6487958
271 END IF;
272 ELSE
273 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
274 ' Cure refund received amount and negotiated amount not equal, cure refund record is not created');
275 END IF;
276
277 x_return_status := l_return_status;
278
279 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
280
281 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : END ');
282
283 EXCEPTION
284 WHEN Fnd_Api.G_EXC_ERROR THEN
285 ROLLBACK TO CALC_CURE_REFUND;
286 x_return_status := Fnd_Api.G_RET_STS_ERROR;
287 x_msg_count := l_msg_count ;
288 x_msg_data := l_msg_data ;
289 Fnd_Msg_Pub.count_and_get(
290 p_count => x_msg_count
291 ,p_data => x_msg_data);
292 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
293 ROLLBACK TO CALC_CURE_REFUND;
294 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
295 x_msg_count := l_msg_count ;
296 x_msg_data := l_msg_data ;
297 Fnd_Msg_Pub.count_and_get(
298 p_count => x_msg_count
299 ,p_data => x_msg_data);
300 WHEN OTHERS THEN
301 ROLLBACK TO CALC_CURE_REFUND;
302 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
303 x_msg_count := l_msg_count ;
304 x_msg_data := l_msg_data ;
305 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_VENDOR_REFUND_PVT','CALC_CURE_REFUND');
306 Fnd_Msg_Pub.count_and_get(
307 p_count => x_msg_count
308 ,p_data => x_msg_data);
309 END CALC_CURE_REFUND ;
310
311 PROCEDURE POPULATE_LOG_TBL(
312 p_contract_number IN VARCHAR2,
313 p_cure_flag IN VARCHAR2,
314 p_cure_amount IN NUMBER,
315 P_type IN VARCHAR2)
316 IS
317 BEGIN
318 IF p_type = 'ERROR'
319 THEN
320 l_error_idx := nvl(l_error_tbl.LAST,0) + 1;
321 l_error_tbl(l_error_idx).contract_number :=p_contract_number;
322 l_error_tbl(l_error_idx).cure_type :=p_cure_flag;
323 l_error_tbl(l_error_idx).cure_amount := p_cure_amount;
324 ELSE
325 l_success_idx := nvl(l_success_tbl.LAST,0) + 1;
326 l_success_tbl(l_success_idx).contract_number :=p_contract_number;
327 l_success_tbl(l_success_idx).cure_type :=p_cure_flag;
328 l_success_tbl(l_success_idx).cure_amount := p_cure_amount;
329 END IF;
330 END POPULATE_LOG_TBL;
331
332 PROCEDURE write_log( mesg_level IN NUMBER
333 ,mesg IN VARCHAR2)
334 IS
335 BEGIN
336 IF (mesg_level >= l_msgLevel)
337 THEN
338 fnd_file.put_line(FND_FILE.LOG, mesg);
339 END IF;
340 END;
341
342 PROCEDURE print_log (p_contract_number VARCHAR2)
343 IS
344 BEGIN
345 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'OKL Generate Cure Refund');
346 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Program Run Date:'||SYSDATE);
347 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
348 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'PARAMETERS');
349 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Contract Number = ' ||p_contract_number);
350 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
351
352 IF l_success_tbl.COUNT > 0
353 THEN
354 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
355 'Cure Refund Generated for '||l_success_tbl.COUNT || ' Contracts ');
356 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
357 FOR i in l_success_tbl.FIRST..l_success_tbl.LAST
358 LOOP
359 FND_FILE.PUT_LINE (FND_FILE.OUTPUT ,'Contract Number ' ||
360 l_success_tbl(i).contract_number || ' Cure Type is '||
361 l_success_tbl(i).cure_type || ' Cure Amount '||
362 l_success_tbl(i).cure_amount );
363 END LOOP;
364 END IF;
365
366 IF l_error_tbl.COUNT > 0
367 THEN
368 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
369 'Cure Refund Not Generated For '||l_error_tbl.COUNT || ' Contracts ');
370 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
371 FOR i in l_error_tbl.FIRST..l_error_tbl.LAST
372 LOOP
373 FND_FILE.PUT_LINE (FND_FILE.OUTPUT ,
374 ' Contract Number '||l_error_tbl(i).contract_number );
375 END LOOP;
376 END IF;
377 END print_log;
378
379 PROCEDURE check_contract( p_contract_id IN NUMBER
380 ,p_program_id IN NUMBER
381 ,p_contract_number IN VARCHAR2
382 ,x_return_status OUT NOCOPY VARCHAR2)
383 IS
384 l_id1 VARCHAR2(40);
385 l_id2 VARCHAR2(200);
386 l_rule_value VARCHAR2(2000);
387 l_days_allowed NUMBER :=0;
388 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
389 l_idx INTEGER;
390 l_amount_past_due NUMBER :=0;
391 l_non_del NUMBER := null;
392
393 -- check for non-delinquent contracts
394 -- ASHIM CHANGE - START
395 /*CURSOR c_amount_past_due(p_contract_id IN NUMBER,
396 p_grace_days IN NUMBER) IS
397 SELECT count(*)
398 FROM okl_cnsld_ar_strms_b ocas
399 ,ar_payment_schedules_all aps
400 WHERE ocas.khr_id = p_contract_id
401 AND ocas.receivables_invoice_id = aps.customer_trx_id
402 AND aps.class ='INV'
403 AND (aps.actual_date_closed + p_grace_days) < sysdate
404 AND NVL(aps.amount_due_remaining, 0) = 0
405 AND not exists
406 (select xls1.lsm_id from
407 okl_xtl_sell_invs_v xls1
408 ,okl_txl_ar_inv_lns_v til1
409 ,okl_trx_ar_invoices_v tai1 where
410 tai1.id = til1.tai_id and
411 til1.id = xls1.til_id and
412 tai1.cpy_id IS NOT NULL and
413 xls1.lsm_id =ocas.id);*/
414
415 CURSOR c_amount_past_due(p_contract_id IN NUMBER,
416 p_grace_days IN NUMBER)
417 IS
418 SELECT count(*)
419 FROM okl_bpd_tld_ar_lines_v ocas
420 ,ar_payment_schedules_all aps
421 WHERE ocas.khr_id = p_contract_id
422 AND ocas.customer_trx_id = aps.customer_trx_id
423 AND aps.class = 'INV'
424 AND (aps.actual_date_closed + p_grace_days) < sysdate
425 AND NVL(aps.amount_due_remaining, 0) = 0
426 AND NOT EXISTS
427 --(select xls1.lsm_id from
428 (SELECT tld.id
429 FROM --okl_xtl_sell_invs_v xls1
430 okl_txd_ar_ln_dtls_b tld
431 ,okl_txl_ar_inv_lns_v til1
432 ,okl_trx_ar_invoices_v tai1
433 WHERE tai1.id = til1.tai_id
434 AND
435 --til1.id = xls1.til_id and
436 til1.id = tld.til_id_details
437 AND tai1.cpy_id IS NOT NULL
438 AND
439 --xls1.lsm_id =ocas.id);
440 tld.id = ocas.tld_id);
441 -- ASHIM CHANGE - END
442 -- check if any pending cure invoives to be paid.
443 -- I guess, this is being taken care by
444 -- checking received amount = negotiated amount
445
446 BEGIN
447 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : START ');
448 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : p_contract_id : '||p_contract_id);
449 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : p_program_id : '||p_program_id);
450 x_return_status := FND_API.G_RET_STS_SUCCESS;
451 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Start of check_contract Procedure' );
452
453 -- Get Contract allowed value for days past due from rules
454 l_return_status := okl_contract_info.get_rule_value(
455 p_contract_id => p_program_id
456 ,p_rule_group_code => 'COCURP'
457 ,p_rule_code => 'CORFND'
458 ,p_segment_number => 2
459 ,x_id1 => l_id1
460 ,x_id2 => l_id2
461 ,x_value => l_rule_value);
462
463 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : okl_contract_info.get_rule_value : '||l_return_status);
464
465 IF l_return_status =FND_Api.G_RET_STS_SUCCESS
466 THEN
467 l_days_allowed :=nvl(l_rule_value,0);
468 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
469 'Days allowed before refund is issued from Rule is ' || l_days_allowed);
470 END IF;
471 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : l_days_allowed : '||l_days_allowed);
472
473 l_non_del := null;
474 OPEN c_amount_past_due (p_contract_id,l_days_allowed);
475 FETCH c_amount_past_due INTO l_non_del;
476 CLOSE c_amount_past_due;
477 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : l_non_del : '||l_non_del);
478
479 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
480 'Amount Past due with grace days is ' || nvl(l_days_allowed,0));
481
482 IF (l_non_del > 0)
483 THEN
484 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
485 'Contract'||p_contract_number || ' is not delinquent');
486 x_return_status := FND_API.G_RET_STS_ERROR;
487 END IF;
488
489 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : check_contract : END ');
490 END check_contract;
491
492 ---------------------------------------------------------------------------
493 -- PROCEDURE GENERATE_VENDOR_REFUND
494 -- This procedure starts the process for all contracts. It starts off by
495 -- validating if the contract is refund applicable and call populates refund
496 -- amount. If the contract does not have applicable refund, it is ignored
497 ---------------------------------------------------------------------------
498 PROCEDURE GENERATE_VENDOR_REFUND(
499 errbuf OUT NOCOPY VARCHAR2,
500 retcode OUT NOCOPY NUMBER,
501 p_contract_number IN VARCHAR2
502 )
503 IS
504 l_api_version CONSTANT NUMBER := 1.0;
505 l_api_name CONSTANT VARCHAR2(30) := 'OKL_VENDOR_REFUND_PVT';
506 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_SUCCESS;
507 l_msg_count NUMBER ;
508 l_msg_data VARCHAR2(32627);
509 l_init_msg_list VARCHAR2(1) DEFAULT fnd_api.g_false;
510
511 l_cure_flag VARCHAR2(1);
512 l_rule_group_code VARCHAR2(30) := 'COCURP';
513 l_rule_code VARCHAR2(30) := 'CORFND';
514 l_rule_name VARCHAR2(200);
515 l_rule_value VARCHAR2(2000);
516 l_days_past_due_allowed NUMBER ;
517 l_days_past_due NUMBER ;
518 l_no_of_cures NUMBER ;
519 l_no_of_cures_allowed NUMBER ;
520 l_repurchase_days_past_allowed NUMBER := -999;
521 l_cure_calc_flag VARCHAR2(30);
522 l_vendor_id VARCHAR2(40);
523
524 l_negotiated_amount NUMBER ;
525 l_repurchased_amount NUMBER ;
526
527 l_id1 VARCHAR2(40);
528 l_id2 VARCHAR2(200);
529
530 -- Cursor fetches the contracts for processing
531 CURSOR contract_csr( p_contract_number IN VARCHAR2) IS
532 SELECT prog.id program_id
533 ,prog.contract_number program_number
534 ,lease.id contract_id
535 ,lease.contract_number contract_number
536 ,rgp.rgd_code
537 ,pty.object1_id1 vendor_id
538 FROM okc_k_headers_b prog,
539 okc_k_headers_b lease,
540 okl_k_headers khr,
541 okc_rule_groups_b rgp,
542 okc_k_party_roles_v pty,
543 OKX_VENDORS_V vnd
544 WHERE khr.id = lease.id
545 AND khr.khr_id = prog.id
546 AND prog.scs_code = 'PROGRAM'
547 AND lease.scs_code in ('LEASE','LOAN')
548 AND rgp.rgd_code = 'COCURP'
549 AND rgp.dnz_chr_id = prog.id
550 AND prog.id = pty.chr_id
551 AND pty.rle_code = 'OKL_VENDOR'
552 AND pty.object1_id1 = to_char(vnd.id1)
553 AND pty.object1_id2 = vnd.id2
554 AND lease.contract_number =nvl(p_contract_number,lease.contract_number)
555 and exists (select 1 from okl_cure_amounts cam
556 where cam.chr_id = lease.id
557 and cam.status = 'CURESINPROGRESS'
558 and nvl(negotiated_amount,0) > 0);
559 /*
560 and exists (select 1 from okl_cure_reports cr
561 where cr.vendor_id = vnd.id1);
562 */
563 l_cure_amount okl_cure_amounts.cure_amount%type;
564
565 /*if the cure invoice is paid in full (i.e remaining_amount =0)
566 then it is considered to be cured */
567
568 -- ASHIM CHANGE - START
569
570
571
572 /*cursor c_get_noof_cures(p_contract_id IN NUMBER) is
573 select count( ps.payment_schedule_id)
574 from ar_payment_schedules_all ps
575 ,okl_cnsld_ar_strms_b stream
576 ,okl_xtl_sell_invs_v xls
577 ,okl_txl_ar_inv_lns_v til
578 ,okl_trx_ar_invoices_v tai
579 where ps.class ='INV'
580 and ps.amount_due_remaining = 0
581 and stream.receivables_invoice_id = ps.customer_trx_id
582 and stream.id = xls.lsm_id
583 and tai.id = til.tai_id
584 and til.id = xls.til_id
585 and tai.cpy_id IS NOT NULL
586 and tai.khr_id = p_contract_id;*/
587
588
589 cursor c_get_noof_cures(p_contract_id IN NUMBER) is
590 select count( ps.payment_schedule_id)
591 from ar_payment_schedules_all ps
592 ,okl_bpd_tld_ar_lines_v stream
593 --,okl_xtl_sell_invs_v xls
594 ,okl_txd_ar_ln_dtls_b tld
595 ,okl_txl_ar_inv_lns_v til
596 ,okl_trx_ar_invoices_v tai
597 where ps.class ='INV'
598 and ps.amount_due_remaining = 0
599 and stream.customer_trx_id = ps.customer_trx_id
600 --and stream.id = xls.lsm_id
601 and stream.tld_id = tld.id
602 and tai.id = til.tai_id
603 --and til.id = xls.til_id
604 and til.id = tld.til_id_details
605 and tai.cpy_id IS NOT NULL
606 and tai.khr_id = p_contract_id;
607
608
609 -- ASHIM CHANGE - END
610
611
612 l_error_msg_tbl error_message_type;
613
614 /* Get min due date for the contract */
615
616 -- ASHIM CHANGE - START
617
618
619
620 /*cursor l_days_past_due_cur (p_contract_id IN NUMBER) is
621 SELECT min(aps.due_date)
622 FROM okl_cnsld_ar_strms_b ocas
623 ,ar_payment_schedules_all aps
624 ,okc_k_headers_b chr
625 WHERE
626 ocas.khr_id = p_contract_id
627 AND ocas.receivables_invoice_id = aps.customer_trx_id
628 AND aps.class = 'INV'
629 AND aps.due_date < sysdate
630 AND NVL(aps.amount_due_remaining, 0) = 0
631 AND ocas.khr_id=chr.id
632 AND not exists
633 ( select xls1.lsm_id from
634 okl_xtl_sell_invs_v xls1
635 ,okl_txl_ar_inv_lns_v til1
636 ,okl_trx_ar_invoices_v tai1 where
637 tai1.id = til1.tai_id
638 and til1.id = xls1.til_id and
639 tai1.cpy_id IS NOT NULL and
640 xls1.lsm_id =ocas.id);*/
641
642 cursor l_days_past_due_cur (p_contract_id IN NUMBER) is
643 SELECT min(aps.due_date)
644 FROM okl_bpd_tld_ar_lines_v ocas
645 ,ar_payment_schedules_all aps
646 ,okc_k_headers_b chr
647 WHERE
648 ocas.khr_id = p_contract_id
649 AND ocas.customer_trx_id = aps.customer_trx_id
650 AND aps.class = 'INV'
651 AND aps.due_date < sysdate
652 AND NVL(aps.amount_due_remaining, 0) = 0
653 AND ocas.khr_id=chr.id
654 AND not exists
655 --( select xls1.lsm_id from
656 ( select tld.id from
657 --okl_xtl_sell_invs_v xls1
658 okl_txd_ar_ln_dtls_b tld
659 ,okl_txl_ar_inv_lns_v til1
660 ,okl_trx_ar_invoices_v tai1 where
661 tai1.id = til1.tai_id
662 --and til1.id = xls1.til_id and
663 and til1.id = tld.til_id_details and
664 tai1.cpy_id IS NOT NULL and
665 tld.id =ocas.tld_id);
666
667
668
669 -- ASHIM CHANGE - END
670
671
672 x_contract_number okc_k_headers_b.contract_number%TYPE;
673 l_default_date DATE :=TRUNC(SYSDATE);
674 l_days_past DATE ;
675 BEGIN
676 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : GENERATE_VENDOR_REFUND : START ');
677 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : GENERATE_VENDOR_REFUND : p_contract_number : '||p_contract_number);
678
679 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, 'OKL Generate Cure Refund');
680 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, 'Program Run Date:'||SYSDATE);
681 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, '***********************************************');
682 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, 'PARAMETERS');
683 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, 'Contract Number = ' ||p_contract_number);
684 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH, '***********************************************');
685
686 -- Open the contract cursor for process
687 FOR i IN contract_csr(p_contract_number)
688 LOOP
689 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
690 '***********************************************');
691 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
692 ' Processing: Contract Number=> '||i.contract_number);
693 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
694 ' Program number is ' ||i.program_number);
695 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
696 ' Contract Id is ' ||i.contract_id);
697 --need to process other contract if the first one errors out
698 --so introducing this while loop
699
700 WHILE TRUE
701 LOOP
702 -- Initialize the variables
703 l_rule_value := NULL;
704 l_no_of_cures := 0;
705 l_no_of_cures_allowed := 0;
706 l_repurchase_days_past_allowed := 0;
707 l_cure_calc_flag := NULL;
708 l_return_status :=FND_Api.G_RET_STS_SUCCESS;
709 l_days_past :=SYSDATE;
710 l_vendor_id := null;
711 -----------------------------------------------------------------
712 -- CHECK IF THE CONTRACT HAS CURE RULE - WE DO NOT GENERATE CURES
713 -- FOR CONTRACT THAT DOES NOT HAVE CURE AGREEMENT
714 -- we need to the pass the vendor program id to get the
715 -- cure rule values.
716 -----------------------------------------------------------------
717 l_return_status := okl_contract_info.get_rule_value(
718 p_contract_id => i.program_id
719 ,p_rule_group_code => l_rule_group_code
720 ,p_rule_code => l_rule_code
721 ,p_segment_number => 2
722 ,x_id1 => l_id1
723 ,x_id2 => l_id2
724 ,x_value => l_rule_value);
725 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : GENERATE_VENDOR_REFUND : okl_contract_info.get_rule_value : '||l_return_status);
726
727 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
728 THEN
729 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ,
730 'Did not return a value from rule for grace days allowed before cure refund starts');
731
732 POPULATE_LOG_TBL(
733 p_contract_number =>i.contract_number,
734 p_cure_flag =>NULL,
735 p_cure_amount =>NULL,
736 P_type =>'ERROR');
737 EXIT;
738
739 ELSE
740 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ,
741 'Cure Refund grace days allowed rule exists and rule value is ' || l_rule_value);
742 END IF;
743
744 IF (l_rule_value is null and to_number(l_rule_value) < 0)
745 THEN
746 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ,
747 'Cure Refund grace days allowed rule segment value does not exist and rule value is '||l_rule_value);
748 POPULATE_LOG_TBL(
749 p_contract_number =>i.contract_number,
750 p_cure_flag =>NULL,
751 p_cure_amount =>NULL,
752 P_type =>'ERROR');
753 EXIT;
754 END IF;
755
756 ------------------------------------------------------------------
757 --check if the contract is come out of delinquency
758 --if YES (due_date + gracedays(from rule) < SYSDATE )
759 --then continue cure refund process
760 ------------------------------------------------------------------
761
762 CHECK_CONTRACT(i.contract_id,
763 i.program_id,
764 i.contract_number,
765 l_return_status);
766 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : GENERATE_VENDOR_REFUND : CHECK_CONTRACT : '||l_return_status);
767
768 IF l_return_status = FND_API.G_RET_STS_SUCCESS
769 THEN
770 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ,
771 'Contract ' || i.contract_number ||'is Delinquent ');
772 --jsanju added this
773 POPULATE_LOG_TBL(
774 p_contract_number =>i.contract_number,
775 p_cure_flag =>NULL,
776 p_cure_amount =>NULL,
777 P_type =>'ERROR');
778 EXIT;
779 END IF; -- end of check_contract
780
781 CALC_CURE_REFUND( p_api_version =>l_api_version,
782 p_init_msg_list =>l_init_msg_list,
783 p_contract_id =>i.contract_id,
784 p_contract_number =>i.contract_number,
785 p_program_id =>i.program_id,
786 p_rule_group_code =>l_rule_group_code,
787 p_vendor_id =>i.vendor_id,
788 p_times_cured =>l_no_of_cures,
789 x_return_status =>l_return_status,
790 x_msg_count =>l_msg_count,
791 x_msg_data =>l_msg_data ,
792 x_cure_amount =>l_cure_amount);
793 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : GENERATE_VENDOR_REFUND : CALC_CURE_REFUND : '||l_return_status);
794
795 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM,
796 'Result of Cure Refund for contract_number '||
797 i.contract_number || ' is ' ||l_return_status);
798
799 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS)
800 THEN
801 GET_ERROR_MESSAGE(l_error_msg_tbl);
802 IF (l_error_msg_tbl.COUNT > 0)
803 THEN
804 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH,' Error in calculating Cure Refund');
805 FOR i IN l_error_msg_tbl.FIRST..l_error_msg_tbl.LAST
806 LOOP
807 write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH,l_error_msg_tbl(i));
808 END LOOP;
809 END IF;
810 POPULATE_LOG_TBL(
811 p_contract_number =>i.contract_number,
812 p_cure_flag =>l_cure_calc_flag,
813 p_cure_amount =>l_cure_amount,
814 P_type =>'ERROR');
815 ELSE
816 POPULATE_LOG_TBL(
817 p_contract_number =>i.contract_number,
818 p_cure_flag =>l_cure_calc_flag,
819 p_cure_amount =>l_cure_amount,
820 P_type =>'SUCCESS');
821 END IF;
822 EXIT; --for while loop
823 END LOOP; --end of while loop
824 END LOOP;
825 Print_log (p_contract_number);
826 retcode :=0; --success
827
828 okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : GENERATE_VENDOR_REFUND : END ');
829
830 EXCEPTION
831 WHEN OTHERS THEN
832 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS)IN OKL_CURE_CALC_PVT => '||SQLERRM);
833 retcode :=2;
834 errbuf :=SQLERRM;
835
836 END GENERATE_VENDOR_REFUND;
837
838 END OKL_VENDOR_REFUND_PVT;