[Home] [Help]
PACKAGE BODY: APPS.OZF_MASS_SETTLEMENT_PVT
Source
1 PACKAGE BODY OZF_MASS_SETTLEMENT_PVT AS
2 /* $Header: ozfvmstb.pls 120.25.12020000.5 2013/02/19 05:13:32 kdass ship $ */
3 -- Start of Comments
4 -- Package name : OZF_MASS_SETTLEMENT_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_MASS_SETTLEMENT_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvmstb.pls';
12
13 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
14 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
15
16 /*=======================================================================*
17 | PROCEDURE
18 | Close_Claim
19 |
20 | NOTES
21 |
22 | HISTORY
23 | 6-May-2005 Sahana Create
24 *=======================================================================*/
25 PROCEDURE close_claim( p_group_claim_id IN NUMBER
26 ,p_claim_id IN NUMBER
27 ,x_return_status OUT NOCOPY VARCHAR2)
28 IS
29 BEGIN
30 x_return_status := FND_API.g_ret_sts_success;
31
32
33 UPDATE ozf_claims_all
34 SET payment_status = 'PAID'
35 , status_code = 'CLOSED'
36 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
37 'OZF_CLAIM_STATUS'
38 ,'CLOSED'
39 )
40 WHERE group_claim_id = p_group_claim_id
41 AND claim_id = p_claim_id;
42 EXCEPTION
43 WHEN OTHERS THEN
44 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
45 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
46 FND_MSG_PUB.add;
47 END IF;
48 IF OZF_DEBUG_LOW_ON THEN
49 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
50 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
51 FND_MSG_PUB.Add;
52 END IF;
53 x_return_status := FND_API.g_ret_sts_unexp_error;
54 END;
55
56
57
58
59 /*=======================================================================*
60 | PROCEDURE
61 | Pay_by_Open_Receipt
62 |
63 | NOTES
64 |
65 | HISTORY
66 | 30-OCT-2003 mchang Create.
67 *=======================================================================*/
68 PROCEDURE Pay_by_Open_Receipt(
69 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
70 ,p_deduction_type IN VARCHAR2
71 ,p_open_receipt_id IN NUMBER
72
73 ,p_payment_claim_id IN NUMBER
74 ,p_amount_applied IN NUMBER
75 ,p_settlement_doc_id IN NUMBER
76
77 ,x_return_status OUT NOCOPY VARCHAR2
78 ,x_msg_data OUT NOCOPY VARCHAR2
79 ,x_msg_count OUT NOCOPY NUMBER
80 )
81 IS
82 l_api_version CONSTANT NUMBER := 1.0;
83 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Open_Receipt';
84 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
85 l_return_status VARCHAR2(1);
86 ---
87 l_application_ref_num AR_RECEIVABLE_APPLICATIONS.application_ref_num%TYPE;
88 l_receivable_application_id NUMBER;
89 l_applied_rec_app_id NUMBER;
90
91 l_payment_claim_rec OZF_Claim_PVT.claim_rec_type;
92
93 l_recpt_old_applied_amt NUMBER;
94 l_recpt_new_applied_amt NUMBER;
95
96 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
97 , cv_root_claim_id IN NUMBER) IS
98 SELECT rec.amount_applied
99 FROM ar_receivable_applications rec
100 WHERE rec.applied_payment_schedule_id = -4
101 AND rec.cash_receipt_id = cv_cash_receipt_id
102 AND rec.application_ref_type = 'CLAIM'
103 AND rec.display = 'Y'
104 AND rec.secondary_application_ref_id = cv_root_claim_id;
105
106 CURSOR csr_old_applied_invoice( cv_cash_receipt_id IN NUMBER
107 , cv_customer_trx_id IN NUMBER
108 , cv_root_claim_id IN NUMBER ) IS
109 SELECT rec.comments
110 , rec.payment_set_id
111 , rec.application_ref_type
112 , rec.application_ref_id
113 , rec.application_ref_num
114 , rec.secondary_application_ref_id
115 , rec.application_ref_reason
116 , rec.customer_reference
117 , rec.amount_applied
118 , pay.amount_due_remaining
119 FROM ar_receivable_applications rec
120 , ar_payment_schedules pay
121 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
122 AND rec.cash_receipt_id = cv_cash_receipt_id
123 AND pay.customer_trx_id = cv_customer_trx_id
124 AND rec.application_ref_type = 'CLAIM'
125 AND rec.display = 'Y'
126 AND rec.secondary_application_ref_id = cv_root_claim_id;
127
128 l_old_applied_invoice csr_old_applied_invoice%ROWTYPE;
129 l_new_applied_amount NUMBER;
130
131 BEGIN
132 -------------------- initialize -----------------------
133 IF OZF_DEBUG_HIGH_ON THEN
134 OZF_Utility_PVT.debug_message(l_full_name||': start');
135 END IF;
136 x_return_status := FND_API.g_ret_sts_success;
137
138 ------------------------ start -------------------------
139 OZF_AR_Payment_PVT.Query_Claim(
140 p_claim_id => p_payment_claim_id
141 ,x_claim_rec => l_payment_claim_rec
142 ,x_return_status => l_return_status
143 );
144 IF l_return_status = FND_API.g_ret_sts_error THEN
145 RAISE FND_API.g_exc_error;
146 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
147 RAISE FND_API.g_exc_unexpected_error;
148 END IF;
149
150 IF OZF_DEBUG_HIGH_ON THEN
151 OZF_Utility_PVT.debug_message('p_deduction_type ='||p_deduction_type);
152 END IF;
153
154
155 IF ( p_deduction_type IN ( 'RECEIPT_DED', 'SOURCE_DED') AND
156 l_payment_claim_rec.claim_class = 'OVERPAYMENT' ) THEN
157 -- ------------------------------
158 -- 1. Unapply Claim Investigation(Payment) from Open Receipt
159 -- ------------------------------
160 OPEN csr_old_claim_investigation( l_payment_claim_rec.receipt_id
161 , l_payment_claim_rec.root_claim_id
162 );
163 FETCH csr_old_claim_investigation INTO l_recpt_old_applied_amt;
164 CLOSE csr_old_claim_investigation;
165
166 l_recpt_new_applied_amt := l_recpt_old_applied_amt
167 - (p_amount_applied * -1);
168 IF OZF_DEBUG_HIGH_ON THEN
169 OZF_Utility_PVT.debug_message('1. Unapply overpayment claim investigation');
170 OZF_Utility_PVT.debug_message('original overpayment amount = '||l_recpt_old_applied_amt);
171 END IF;
172
173 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
174 p_claim_rec => l_payment_claim_rec
175 ,p_reapply_amount => l_recpt_new_applied_amt
176 ,x_return_status => l_return_status
177 ,x_msg_data => x_msg_data
178 ,x_msg_count => x_msg_count
179 );
180 IF l_return_status = FND_API.g_ret_sts_error THEN
181 RAISE FND_API.g_exc_error;
182 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
183 RAISE FND_API.g_exc_unexpected_error;
184 END IF;
185
186
187 -- ------------------------------
188 -- 2. Apply Open Receipt
189 -- Bug4079177: Apply open receipt before unapplying claim.
190 -- ------------------------------
191 IF OZF_DEBUG_HIGH_ON THEN
192 OZF_Utility_PVT.debug_message('2. Apply open receipt ');
193 OZF_Utility_PVT.debug_message('Amount applied = '||p_amount_applied);
194 OZF_Utility_PVT.debug_message('Payment Cash Receipt Id = '|| l_payment_claim_rec.receipt_id);
195 OZF_Utility_PVT.debug_message('Claim Receipt Id = '|| p_claim_rec.receipt_id);
196 END IF;
197
198 --Fix for bug 5325645
199 IF l_payment_claim_rec.receipt_id <> p_claim_rec.receipt_id THEN
200 AR_RECEIPT_API_COVER.Apply_Open_Receipt(
201 -- Standard API parameters.
202 p_api_version => l_api_version,
203 p_init_msg_list => FND_API.G_FALSE,
204 p_commit => FND_API.G_FALSE,
205 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
206 x_return_status => l_return_status,
207 x_msg_count => x_msg_count,
208 x_msg_data => x_msg_data,
209 -- Receipt application parameters.
210 p_cash_receipt_id => p_claim_rec.receipt_id,
211 p_receipt_number => NULL,
212 p_applied_payment_schedule_id=> NULL,
213 p_open_cash_receipt_id => l_payment_claim_rec.receipt_id,
214 p_open_receipt_number => NULL,
215 p_open_rec_app_id => NULL,
216 p_amount_applied => p_amount_applied,
217 p_apply_date => SYSDATE,
218 p_apply_gl_date => NULL,
219 p_ussgl_transaction_code => NULL,
220 p_called_from => 'CLAIM',
221 p_attribute_rec => NULL,
222 /*
223 -- ******* Global Flexfield parameters *******
224 p_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
225 p_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL,
226 */
227 x_application_ref_num => l_application_ref_num,
228 x_receivable_application_id => l_receivable_application_id,
229 x_applied_rec_app_id => l_applied_rec_app_id
230 );
231 IF l_return_status = FND_API.g_ret_sts_error THEN
232 RAISE FND_API.g_exc_error;
233 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
234 RAISE FND_API.g_exc_unexpected_error;
235 END IF;
236 END IF;
237
238
239 -- ------------------------------
240 -- 3. Unapply Claim Investigation(Deduction) from Receipt
241 -- ------------------------------
242 IF p_deduction_type = 'SOURCE_DED' THEN
243
244 OPEN csr_old_applied_invoice( p_claim_rec.receipt_id
245 , p_claim_rec.source_object_id
246 , p_claim_rec.root_claim_id
247 );
248 FETCH csr_old_applied_invoice INTO l_old_applied_invoice;
249 CLOSE csr_old_applied_invoice;
250
251 -- 3.1. Update amount in dispute
252 OZF_AR_Payment_PVT.Update_dispute_amount(
253 p_claim_rec => p_claim_rec
254 ,p_dispute_amount => (p_amount_applied * -1)
255 ,x_return_status => l_return_status
256 ,x_msg_data => x_msg_data
257 ,x_msg_count => x_msg_count
258 );
259 IF l_return_status = FND_API.g_ret_sts_error THEN
260 RAISE FND_API.g_exc_error;
261 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
262 RAISE FND_API.g_exc_unexpected_error;
263 END IF;
264
265 IF OZF_DEBUG_HIGH_ON THEN
266 OZF_Utility_PVT.debug_message('3. Unapply Invoice Deduction');
267 OZF_Utility_PVT.debug_message('original amount applied to invoice = '||l_old_applied_invoice.amount_applied);
268 END IF;
269
270 -- 3. Reapply claim investigation
271 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
272 p_claim_rec => p_claim_rec
273 ,p_reapply_amount => l_old_applied_invoice.amount_applied +(p_amount_applied * -1)
274 ,x_return_status => l_return_status
275 ,x_msg_data => x_msg_data
276 ,x_msg_count => x_msg_count
277 );
278 IF l_return_status = FND_API.g_ret_sts_error THEN
279 RAISE FND_API.g_exc_error;
280 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
281 RAISE FND_API.g_exc_unexpected_error;
282 END IF;
283
284
285 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
286 OPEN csr_old_claim_investigation( p_claim_rec.receipt_id
287 , p_claim_rec.root_claim_id
288 );
289 FETCH csr_old_claim_investigation INTO l_recpt_old_applied_amt;
290 CLOSE csr_old_claim_investigation;
291
292 l_recpt_new_applied_amt := l_recpt_old_applied_amt + (p_amount_applied * -1);
293
294 IF OZF_DEBUG_HIGH_ON THEN
295 OZF_Utility_PVT.debug_message('3. Unapply Non Invoice Deduction');
296 OZF_Utility_PVT.debug_message('original claim investigation amount = '||l_recpt_old_applied_amt);
297 END IF;
298
299
300 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
301 p_claim_rec => p_claim_rec
302 ,p_reapply_amount => l_recpt_new_applied_amt
303 ,x_return_status => l_return_status
304 ,x_msg_data => x_msg_data
305 ,x_msg_count => x_msg_count
306 );
307 IF l_return_status = FND_API.g_ret_sts_error THEN
308 RAISE FND_API.g_exc_error;
309 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
310 RAISE FND_API.g_exc_unexpected_error;
311 END IF;
312 END IF; -- end if p_deduction_type
313
314 -- ----------------------------------------------------------
315 -- 4. Update Payment Detail
316 -- ----------------------------------------------------------
317 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
318 p_api_version => l_api_version
319 ,p_init_msg_list => FND_API.g_false
320 ,p_commit => FND_API.g_false
321 ,p_validation_level => FND_API.g_valid_level_full
322 ,x_return_status => l_return_status
323 ,x_msg_data => x_msg_data
324 ,x_msg_count => x_msg_count
325 ,p_claim_id => p_claim_rec.claim_id
326 ,p_payment_method => 'RECEIPT'
327 ,p_deduction_type => p_deduction_type
328 ,p_cash_receipt_id => p_claim_rec.receipt_id
329 ,p_customer_trx_id => NULL
330 ,p_adjust_id => NULL
331 ,p_settlement_doc_id => NULL
332 ,p_settlement_mode => 'MASS_SETTLEMENT'
333 );
334 IF l_return_status = FND_API.g_ret_sts_error THEN
335 RAISE FND_API.g_exc_error;
336 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
337 RAISE FND_API.g_exc_unexpected_error;
338 END IF;
339
340
341 /* Bug4079177: Receipt Application error when netting */
342 ELSIF ( p_deduction_type = 'RECEIPT_OPM' AND
343 l_payment_claim_rec.claim_class = 'DEDUCTION' ) THEN
344
345 -- ------------------------------
346 -- 1. Unapply Claim Investigation(Payment) from Open Receipt
347 -- ------------------------------
348
349 OPEN csr_old_claim_investigation( p_claim_rec.receipt_id
350 , p_claim_rec.root_claim_id
351 );
352 FETCH csr_old_claim_investigation INTO l_recpt_old_applied_amt;
353 CLOSE csr_old_claim_investigation;
354
355 l_recpt_new_applied_amt := l_recpt_old_applied_amt
356 + (p_amount_applied * -1);
357 IF OZF_DEBUG_HIGH_ON THEN
358 OZF_Utility_PVT.debug_message('1. Unapply overpayment claim investigation');
359 OZF_Utility_PVT.debug_message('original overpayment amount = '||l_recpt_old_applied_amt);
360 END IF;
361
362 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
363 p_claim_rec => p_claim_rec
364 ,p_reapply_amount => l_recpt_new_applied_amt
365 ,x_return_status => l_return_status
366 ,x_msg_data => x_msg_data
367 ,x_msg_count => x_msg_count
368 );
369 IF l_return_status = FND_API.g_ret_sts_error THEN
370 RAISE FND_API.g_exc_error;
371 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
372 RAISE FND_API.g_exc_unexpected_error;
373 END IF;
374
375
376 -- ------------------------------
377 -- 2. Apply Open Receipt
378 -- ------------------------------
379 IF OZF_DEBUG_HIGH_ON THEN
380 OZF_Utility_PVT.debug_message('2. Apply open receipt ');
381 OZF_Utility_PVT.debug_message('Amount applied = '||p_amount_applied);
382 END IF;
383
384 --Fix for bug 5325645
385 IF l_payment_claim_rec.receipt_id <> p_claim_rec.receipt_id THEN
386 AR_RECEIPT_API_COVER.Apply_Open_Receipt(
387 -- Standard API parameters.
388 p_api_version => l_api_version,
389 p_init_msg_list => FND_API.G_FALSE,
390 p_commit => FND_API.G_FALSE,
391 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
392 x_return_status => l_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data,
395 -- Receipt application parameters.
396 p_cash_receipt_id => l_payment_claim_rec.receipt_id,
397 p_receipt_number => NULL,
398 p_applied_payment_schedule_id=> NULL,
399 p_open_cash_receipt_id => p_claim_rec.receipt_id,
400 p_open_receipt_number => NULL,
401 p_open_rec_app_id => NULL,
402 p_amount_applied => p_amount_applied * -1,
403 p_apply_date => SYSDATE,
404 p_apply_gl_date => NULL,
405 p_ussgl_transaction_code => NULL,
406 p_called_from => 'CLAIM',
407 p_attribute_rec => NULL,
408 /*
409 -- ******* Global Flexfield parameters *******
410 p_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
411 p_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL,
412 */
413 x_application_ref_num => l_application_ref_num,
414 x_receivable_application_id => l_receivable_application_id,
415 x_applied_rec_app_id => l_applied_rec_app_id
416 );
417 IF l_return_status = FND_API.g_ret_sts_error THEN
418 RAISE FND_API.g_exc_error;
419 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
420 RAISE FND_API.g_exc_unexpected_error;
421 END IF;
422 END IF;
423
424
425 -- ------------------------------
426 -- 3. Unapply Claim Investigation(Deduction) from Receipt
427 -- ------------------------------
428 -- ------------------------------
429 IF l_payment_claim_rec.source_object_id IS NOT NULL THEN
430
431 OPEN csr_old_applied_invoice( l_payment_claim_rec.receipt_id
432 , l_payment_claim_rec.source_object_id
433 , l_payment_claim_rec.root_claim_id
434 );
435 FETCH csr_old_applied_invoice INTO l_old_applied_invoice;
436 CLOSE csr_old_applied_invoice;
437
438 -- 3.1. Update amount in dispute
439 OZF_AR_Payment_PVT.Update_dispute_amount(
440 p_claim_rec => l_payment_claim_rec
441 ,p_dispute_amount => (p_amount_applied * -1)
442 ,x_return_status => l_return_status
443 ,x_msg_data => x_msg_data
444 ,x_msg_count => x_msg_count
445 );
446 IF l_return_status = FND_API.g_ret_sts_error THEN
447 RAISE FND_API.g_exc_error;
448 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
449 RAISE FND_API.g_exc_unexpected_error;
450 END IF;
451
452 IF OZF_DEBUG_HIGH_ON THEN
453 OZF_Utility_PVT.debug_message('3. Unapply Invoice Deduction');
454 OZF_Utility_PVT.debug_message('original amount applied to invoice = '||l_old_applied_invoice.amount_applied);
455 END IF;
456
457 -- 3. Reapply claim investigation
458 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
459 p_claim_rec => l_payment_claim_rec
460 ,p_reapply_amount => l_old_applied_invoice.amount_applied -(p_amount_applied * -1)
461 ,x_return_status => l_return_status
462 ,x_msg_data => x_msg_data
463 ,x_msg_count => x_msg_count
464 );
465 IF l_return_status = FND_API.g_ret_sts_error THEN
466 RAISE FND_API.g_exc_error;
467 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
468 RAISE FND_API.g_exc_unexpected_error;
469 END IF;
470
471
472 ELSIF l_payment_claim_rec.source_object_id IS NULL THEN
473 OPEN csr_old_claim_investigation( l_payment_claim_rec.receipt_id
474 , l_payment_claim_rec.root_claim_id
475 );
476 FETCH csr_old_claim_investigation INTO l_recpt_old_applied_amt;
477 CLOSE csr_old_claim_investigation;
478
479 l_recpt_new_applied_amt := l_recpt_old_applied_amt - (p_amount_applied * -1);
480
481 IF OZF_DEBUG_HIGH_ON THEN
482 OZF_Utility_PVT.debug_message('3. Unapply Non Invoice Deduction');
483 OZF_Utility_PVT.debug_message('original claim investigation amount = '||l_recpt_old_applied_amt);
484 END IF;
485
486 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
487 p_claim_rec => l_payment_claim_rec
488 ,p_reapply_amount => l_recpt_new_applied_amt
489 ,x_return_status => l_return_status
490 ,x_msg_data => x_msg_data
491 ,x_msg_count => x_msg_count
492 );
493 IF l_return_status = FND_API.g_ret_sts_error THEN
494 RAISE FND_API.g_exc_error;
495 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
496 RAISE FND_API.g_exc_unexpected_error;
497 END IF;
498 END IF; -- end if l_payment_claim_rec.claim_class
499
500
501 -- ----------------------------------------------------------
502 -- 4. Update Payment Detail
503 -- ----------------------------------------------------------
504 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
505 p_api_version => l_api_version
506 ,p_init_msg_list => FND_API.g_false
507 ,p_commit => FND_API.g_false
508 ,p_validation_level => FND_API.g_valid_level_full
509 ,x_return_status => l_return_status
510 ,x_msg_data => x_msg_data
511 ,x_msg_count => x_msg_count
512 ,p_claim_id => p_claim_rec.claim_id
513 ,p_payment_method => 'RECEIPT'
514 ,p_deduction_type => p_deduction_type
515 ,p_cash_receipt_id => p_claim_rec.receipt_id
516 ,p_customer_trx_id => NULL
517 ,p_adjust_id => NULL
518 ,p_settlement_doc_id => NULL
519 ,p_settlement_mode => 'MASS_SETTLEMENT'
520 );
521 IF l_return_status = FND_API.g_ret_sts_error THEN
522 RAISE FND_API.g_exc_error;
523 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
524 RAISE FND_API.g_exc_unexpected_error;
525 END IF;
526
527 END IF;
528
529
530 IF OZF_DEBUG_HIGH_ON THEN
531 OZF_Utility_PVT.debug_message(l_full_name||': end');
532 END IF;
533 EXCEPTION
534 WHEN FND_API.g_exc_error THEN
535 x_return_status := FND_API.g_ret_sts_error;
536
537 WHEN FND_API.g_exc_unexpected_error THEN
538 x_return_status := FND_API.g_ret_sts_unexp_error ;
539
540 WHEN OTHERS THEN
541 x_return_status := FND_API.g_ret_sts_unexp_error ;
542 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
543 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
544 END IF;
545
546 END Pay_by_Open_Receipt;
547
548
549 /*=======================================================================*
550 | PROCEDURE
551 | Pay_by_Credit_Memo
552 |
553 | NOTES
554 |
555 | HISTORY
556 | 15-MAR-2002 mchang Create.
557 *=======================================================================*/
558 PROCEDURE Pay_by_Credit_Memo(
559 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
560 ,p_deduction_type IN VARCHAR2
561 ,p_payment_reference_id IN NUMBER
562 ,p_credit_memo_amount IN NUMBER
563 ,p_settlement_doc_id IN NUMBER
564
565 ,x_return_status OUT NOCOPY VARCHAR2
566 ,x_msg_data OUT NOCOPY VARCHAR2
567 ,x_msg_count OUT NOCOPY NUMBER
568 )
569 IS
570 l_api_version CONSTANT NUMBER := 1.0;
571 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Credit_Memo';
572 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
573 l_return_status VARCHAR2(1);
574 ---
575 l_invoice_applied_count NUMBER;
576 -- l_old_applied_amount NUMBER;
577 l_new_applied_amount NUMBER;
578 l_cm_customer_trx_id NUMBER;
579 l_cm_amount NUMBER;
580 l_online_upd_ded_status BOOLEAN := FALSE;
581 l_orig_dispute_amount NUMBER;
582 l_cm_applied_on_rec_amt NUMBER;
583 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
584
585 CURSOR csr_old_applied_invoice( cv_cash_receipt_id IN NUMBER
586 , cv_customer_trx_id IN NUMBER
587 , cv_root_claim_id IN NUMBER ) IS
588 SELECT rec.comments
589 , rec.payment_set_id
590 , rec.application_ref_type
591 , rec.application_ref_id
592 , rec.application_ref_num
593 , rec.secondary_application_ref_id
594 , rec.application_ref_reason
595 , rec.customer_reference
596 , rec.amount_applied
597 , pay.amount_due_remaining
598 FROM ar_receivable_applications rec
599 , ar_payment_schedules pay
600 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
601 AND rec.cash_receipt_id = cv_cash_receipt_id
602 AND pay.customer_trx_id = cv_customer_trx_id
603 AND rec.application_ref_type = 'CLAIM'
604 AND rec.display = 'Y'
605 AND rec.secondary_application_ref_id = cv_root_claim_id;
606
607 l_old_applied_invoice csr_old_applied_invoice%ROWTYPE;
608
609 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
610 SELECT amount_applied
611 FROM ar_receivable_applications
612 WHERE application_ref_type = 'CLAIM'
613 AND applied_payment_schedule_id = -4
614 AND display = 'Y'
615 AND secondary_application_ref_id = cv_root_claim_id;
616
617 CURSOR csr_cm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
618 SELECT amount_applied, apply_date -- Fix for Bug 3091401. TM passes old apply date
619 FROM ar_receivable_applications
620 WHERE cash_receipt_id = cv_cash_receipt_id
621 AND applied_customer_trx_id = cv_customer_trx_id
622 AND display = 'Y'
623 AND status = 'APP';
624
625 l_settlement_amount NUMBER := NULL;
626
627 BEGIN
628 -------------------- initialize -----------------------
629 IF OZF_DEBUG_HIGH_ON THEN
630 OZF_Utility_PVT.debug_message(l_full_name||': start');
631 END IF;
632 x_return_status := FND_API.g_ret_sts_success;
633
634 ------------------------ start -------------------------
635 IF p_deduction_type = 'RECEIPT_OPM' THEN
636 /*------------------------------------------------------------*
637 | OVERPAYMENT -> Settle By Credit Memo --> Process Settlement Workflow
638 *------------------------------------------------------------*/
639 /*
640 Process_Settlement_WF(
641 p_claim_id => p_claim_rec.claim_id
642 ,x_return_status => l_return_status
643 ,x_msg_data => x_msg_data
644 ,x_msg_count => x_msg_count
645 );
646 */
647 IF l_return_status = FND_API.g_ret_sts_error THEN
648 RAISE FND_API.g_exc_error;
649 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
650 RAISE FND_API.g_exc_unexpected_error;
651 END IF;
652 ELSE
653 IF p_payment_reference_id IS NULL OR
654 p_payment_reference_id = FND_API.g_miss_num THEN
655 /*------------------------------------------------------------*
656 | No payment reference specified (No open credit memo specified) -> AutoInvoice
657 *------------------------------------------------------------*/
658 IF OZF_DEBUG_HIGH_ON THEN
659 OZF_Utility_PVT.debug_message('No payment reference specified (No open credit memo specified) -> AutoInvoice.');
660 END IF;
661 -- 1. AutoInvoice
662 OZF_AR_INTERFACE_PVT.Interface_Claim(
663 p_api_version => l_api_version
664 ,p_init_msg_list => FND_API.g_false
665 ,p_commit => FND_API.g_false
666 ,p_validation_level => FND_API.g_valid_level_full
667 ,x_return_status => l_return_status
668 ,x_msg_data => x_msg_data
669 ,x_msg_count => x_msg_count
670 ,p_claim_id => p_claim_rec.claim_id
671 );
672 IF l_return_status = FND_API.g_ret_sts_error THEN
673 RAISE FND_API.g_exc_error;
674 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
675 RAISE FND_API.g_exc_unexpected_error;
676 END IF;
677
678 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
679 l_online_upd_ded_status := FALSE;
680
681 ELSE -- payment_reference_id is not null and deduction_type IN ('SOURCE_DED', 'RECEIPT_DED')
682 /*------------------------------------------------------------*
683 | Update Claim Status to CLOSED.
684 *------------------------------------------------------------*/
685 /*
686 Close_Claim(
687 p_claim_rec => p_claim_rec
688 ,x_return_status => l_return_status
689 ,x_msg_data => x_msg_data
690 ,x_msg_count => x_msg_count
691 );
692 IF l_return_status = FND_API.g_ret_sts_error THEN
693 RAISE FND_API.g_exc_error;
694 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
695 RAISE FND_API.g_exc_unexpected_error;
696 END IF;
697 */
698
699 OPEN csr_cm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
700 FETCH csr_cm_exist_on_rec INTO l_cm_applied_on_rec_amt, l_apply_date; -- Fix for Bug 3091401. TM passes old apply date
701 CLOSE csr_cm_exist_on_rec;
702
703
704 IF p_deduction_type = 'SOURCE_DED' THEN
705 /*------------------------------------------------------------*
706 | Invoice Deduction -> 1. Update amount in dispute
707 | 2. Apply credit memo with amount_settled on receipt.
708 | 3. Reapply invoice related deduction.
709 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
710 | Invoice Deduction -> 1. Update amount in dispute
711 | 2. Reapply credit memo with increase amount on receipt.
712 | 3. Reapply invoice related deduction.
713 *------------------------------------------------------------*/
714
715 OPEN csr_old_applied_invoice( p_claim_rec.receipt_id
716 , p_claim_rec.source_object_id
717 , p_claim_rec.root_claim_id
718 );
719 FETCH csr_old_applied_invoice INTO l_old_applied_invoice;
720 CLOSE csr_old_applied_invoice;
721
722 IF OZF_DEBUG_HIGH_ON THEN
723 OZF_Utility_PVT.debug_message('Invoice Deduction -> 1. Update amount in dispute');
724 END IF;
725 -- 1. Update amount in dispute
726 OZF_AR_Payment_PVT.Update_dispute_amount(
727 p_claim_rec => p_claim_rec
728 ,p_dispute_amount => (p_credit_memo_amount * -1)
729 ,x_return_status => l_return_status
730 ,x_msg_data => x_msg_data
731 ,x_msg_count => x_msg_count
732 );
733 IF l_return_status = FND_API.g_ret_sts_error THEN
734 RAISE FND_API.g_exc_error;
735 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
736 RAISE FND_API.g_exc_unexpected_error;
737 END IF;
738
739 IF l_cm_applied_on_rec_amt IS NULL THEN
740 IF OZF_DEBUG_HIGH_ON THEN
741 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Apply creit memo on receipt');
742 END IF;
743 -- 2. Apply creit memo on receipt
744 OZF_AR_Payment_PVT.Apply_on_Receipt(
745 p_cash_receipt_id => p_claim_rec.receipt_id
746 ,p_customer_trx_id => p_payment_reference_id
747 ,p_new_applied_amount => (p_credit_memo_amount * -1)
748 ,p_comments => p_claim_rec.comments
749 ,p_customer_reference => p_claim_rec.customer_ref_number
750 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
751 ,x_return_status => l_return_status
752 ,x_msg_data => x_msg_data
753 ,x_msg_count => x_msg_count
754 );
755 IF l_return_status = FND_API.g_ret_sts_error THEN
756 RAISE FND_API.g_exc_error;
757 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
758 RAISE FND_API.g_exc_unexpected_error;
759 END IF;
760 ELSE
761 IF OZF_DEBUG_HIGH_ON THEN
762 OZF_Utility_PVT.debug_message('Invoice Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
763 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Reapply creit memo on receipt');
764 END IF;
765
766 l_settlement_amount := (p_credit_memo_amount * -1);--Bug4308188
767 -- 2. Reapply credit memo on receipt
768 arp_deduction_cover2.reapply_credit_memo(
769 p_customer_trx_id => p_payment_reference_id ,
770 p_cash_receipt_id => p_claim_rec.receipt_id,
771 p_amount_applied => l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1),
772 p_init_msg_list => FND_API.g_false,
773 x_return_status => l_return_status,
774 x_msg_count => x_msg_count,
775 x_msg_data => x_msg_data);
776 IF l_return_status = FND_API.g_ret_sts_error THEN
777 RAISE FND_API.g_exc_error;
778 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
779 RAISE FND_API.g_exc_unexpected_error;
780 END IF;
781 END IF;
782
783 IF OZF_DEBUG_HIGH_ON THEN
784 OZF_Utility_PVT.debug_message('Invoice Deduction -> 3. Unapply claim investigation');
785 OZF_Utility_PVT.debug_message('original invoice deduction amount = '||l_old_applied_invoice.amount_applied);
786 OZF_Utility_PVT.debug_message('reapply invoice deduction amount = '||(l_old_applied_invoice.amount_applied + p_credit_memo_amount));
787 END IF;
788 -- 3. Reapply claim investigation
789 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
790 p_claim_rec => p_claim_rec
791 ,p_reapply_amount => l_old_applied_invoice.amount_applied + p_credit_memo_amount
792 ,x_return_status => l_return_status
793 ,x_msg_data => x_msg_data
794 ,x_msg_count => x_msg_count
795 );
796 IF l_return_status = FND_API.g_ret_sts_error THEN
797 RAISE FND_API.g_exc_error;
798 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
799 RAISE FND_API.g_exc_unexpected_error;
800 END IF;
801
802 l_online_upd_ded_status := TRUE;
803
804 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
805 IF l_cm_applied_on_rec_amt IS NULL THEN
806 /*------------------------------------------------------------*
807 | Receipt Deduction -> 1. Apply credit memo on receipt.
808 | 2. Unapply claim investigation
809 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
810 | Receipt Deduction -> 0.5. Unapply credit memo on receipt
811 | 1. Apply credit memo with increased amount on receipt
812 | 2. Unapply claim investigation
813 *------------------------------------------------------------*/
814 IF OZF_DEBUG_HIGH_ON THEN
815 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Apply creit memo on receipt');
816 END IF;
817 -- 1. Apply creit memo on receipt
818 OZF_AR_Payment_PVT.Apply_on_Receipt(
819 p_cash_receipt_id => p_claim_rec.receipt_id
820 ,p_customer_trx_id => p_payment_reference_id
821 ,p_new_applied_amount => (p_credit_memo_amount * -1)
822 ,p_comments => p_claim_rec.comments
823 ,p_customer_reference => p_claim_rec.customer_ref_number
824 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
825 ,x_return_status => l_return_status
826 ,x_msg_data => x_msg_data
827 ,x_msg_count => x_msg_count
828 );
829 IF l_return_status = FND_API.g_ret_sts_error THEN
830 RAISE FND_API.g_exc_error;
831 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
832 RAISE FND_API.g_exc_unexpected_error;
833 END IF;
834 IF OZF_DEBUG_HIGH_ON THEN
835 OZF_Utility_PVT.debug_message('Receipt Deduction -> 2. Unapply claim investigation');
836 END IF;
837 ELSE
838 /*------------------------------------------------------------*
839 | Receipt Deduction
840 *------------------------------------------------------------*/
841 IF OZF_DEBUG_HIGH_ON THEN
842 OZF_Utility_PVT.debug_message('Receipt Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
843 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Reapply credit memo with increased amount on receipt');
844 END IF;
845
846 l_settlement_amount := (p_credit_memo_amount * -1); --Bug4308188
847 -- 2. Reapply credit memo on receipt
848 arp_deduction_cover2.reapply_credit_memo(
849 p_customer_trx_id => p_payment_reference_id ,
850 p_cash_receipt_id => p_claim_rec.receipt_id,
851 p_amount_applied => l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1),
852 p_init_msg_list => FND_API.g_false,
853 x_return_status => l_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data);
856 IF l_return_status = FND_API.g_ret_sts_error THEN
857 RAISE FND_API.g_exc_error;
858 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
859 RAISE FND_API.g_exc_unexpected_error;
860 END IF;
861 END IF;
862
863 -- 2. Unapply claim investigation
864 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
865 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
866 CLOSE csr_claim_investigation_amount;
867
868 IF OZF_DEBUG_HIGH_ON THEN
869 OZF_Utility_PVT.debug_message('original claim investigation amount = '||l_orig_dispute_amount);
870 OZF_Utility_PVT.debug_message('reapply claim investigation amount = '||(l_orig_dispute_amount + p_credit_memo_amount));
871 END IF;
872 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
873 p_claim_rec => p_claim_rec
874 ,p_reapply_amount => l_orig_dispute_amount + p_credit_memo_amount --(l_orig_reapply_amount - p_credit_memo_amount) * -1
875 ,x_return_status => l_return_status
876 ,x_msg_data => x_msg_data
877 ,x_msg_count => x_msg_count
878 );
879 IF l_return_status = FND_API.g_ret_sts_error THEN
880 RAISE FND_API.g_exc_error;
881 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
882 RAISE FND_API.g_exc_unexpected_error;
883 END IF;
884 l_online_upd_ded_status := TRUE;
885 END IF; -- end if p_deduction_type = 'SOURCE_DED', elsif p_deduction_type = 'DEDUCTION_DED'
886 END IF; -- end if payment_reference_id is null
887
888 /*------------------------------------------------------------*
889 | Update payment detail
890 *------------------------------------------------------------*/
891 IF l_online_upd_ded_status THEN
892 -- Update Deduction payment detail
893 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
894 p_api_version => l_api_version
895 ,p_init_msg_list => FND_API.g_false
896 ,p_commit => FND_API.g_false
897 ,p_validation_level => FND_API.g_valid_level_full
898 ,x_return_status => l_return_status
899 ,x_msg_data => x_msg_data
900 ,x_msg_count => x_msg_count
901 ,p_claim_id => p_claim_rec.claim_id
902 ,p_payment_method => 'PREV_OPEN_CREDIT'
903 ,p_deduction_type => p_deduction_type
904 ,p_cash_receipt_id => p_claim_rec.receipt_id
905 ,p_customer_trx_id => p_payment_reference_id
906 ,p_adjust_id => NULL
907 ,p_settlement_doc_id => p_settlement_doc_id
908 ,p_settlement_mode => 'MASS_SETTLEMENT'
909 ,p_settlement_amount => l_settlement_amount --Bug4308188
910 );
911 IF l_return_status = FND_API.g_ret_sts_error THEN
912 RAISE FND_API.g_exc_error;
913 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
914 RAISE FND_API.g_exc_unexpected_error;
915 END IF;
916 END IF;
917
918 END IF;
919
920 IF OZF_DEBUG_HIGH_ON THEN
921 OZF_Utility_PVT.debug_message(l_full_name||': end');
922 END IF;
923 EXCEPTION
924 WHEN FND_API.g_exc_error THEN
925 x_return_status := FND_API.g_ret_sts_error;
926
927 WHEN FND_API.g_exc_unexpected_error THEN
928 x_return_status := FND_API.g_ret_sts_unexp_error ;
929
930 WHEN OTHERS THEN
931 x_return_status := FND_API.g_ret_sts_unexp_error ;
932 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
933 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
934 END IF;
935
936 END Pay_by_Credit_Memo;
937
938
939 /*=======================================================================*
940 | PROCEDURE
941 | Pay_by_Debit_Memo
942 |
943 | NOTES
944 |
945 | HISTORY
946 | 15-MAR-2002 mchang Create.
947 *=======================================================================*/
948 PROCEDURE Pay_by_Debit_Memo(
949 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
950 ,p_deduction_type IN VARCHAR2
951 ,p_payment_reference_id IN NUMBER
952 ,p_debit_memo_amount IN NUMBER
953 ,p_settlement_doc_id IN NUMBER
954
955 ,x_return_status OUT NOCOPY VARCHAR2
956 ,x_msg_data OUT NOCOPY VARCHAR2
957 ,x_msg_count OUT NOCOPY NUMBER
958 )
959 IS
960 l_api_version CONSTANT NUMBER := 1.0;
961 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Debit_Memo';
962 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
963 l_return_status VARCHAR2(1);
964
965 l_dm_trx_type_id NUMBER;
966 l_online_upd_ded_status BOOLEAN := FALSE;
967 l_orig_dispute_amount NUMBER;
968 l_payment_trx_number VARCHAR2(30);
969 l_dm_applied_on_rec_amt NUMBER;
970 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
971
972 CURSOR csr_dm_trx_type_id(cv_claim_type_id IN NUMBER) IS
973 SELECT dm_trx_type_id
974 FROM ozf_claim_types_all_b
975 WHERE claim_type_id = cv_claim_type_id;
976
977 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
978 SELECT amount_applied
979 FROM ar_receivable_applications
980 WHERE application_ref_type = 'CLAIM'
981 AND applied_payment_schedule_id = -4
982 AND display = 'Y'
983 AND secondary_application_ref_id = cv_root_claim_id;
984
985 CURSOR csr_payment_trx_number(cv_customer_trx_id IN NUMBER) IS
986 SELECT trx_number
987 FROM ra_customer_trx
988 WHERE customer_trx_id = cv_customer_trx_id;
989
990 CURSOR csr_dm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
991 SELECT amount_applied, apply_date -- Fix for Bug 3091401. TM passes old apply date
992 FROM ar_receivable_applications
993 WHERE cash_receipt_id = cv_cash_receipt_id
994 AND applied_customer_trx_id = cv_customer_trx_id
995 AND display = 'Y'
996 AND status = 'APP';
997
998 l_settlement_amount NUMBER := NULL;
999
1000 BEGIN
1001 -------------------- initialize -----------------------
1002 IF OZF_DEBUG_HIGH_ON THEN
1003 OZF_Utility_PVT.debug_message(l_full_name||': start');
1004 END IF;
1005
1006 x_return_status := FND_API.g_ret_sts_success;
1007
1008 ------------------------ start -------------------------
1009 IF p_deduction_type = 'RECEIPT_OPM' THEN
1010 IF p_payment_reference_id IS NULL THEN
1011 /*------------------------------------------------------------*
1012 | OVERPAYMENT -> No open debit memo specified --> AutoInvoice
1013 *------------------------------------------------------------*/
1014 -- 1. AutoInvoice
1015 OZF_AR_INTERFACE_PVT.Interface_Claim(
1016 p_api_version => l_api_version
1017 ,p_init_msg_list => FND_API.g_false
1018 ,p_commit => FND_API.g_false
1019 ,p_validation_level => FND_API.g_valid_level_full
1020 ,x_return_status => l_return_status
1021 ,x_msg_data => x_msg_data
1022 ,x_msg_count => x_msg_count
1023 ,p_claim_id => p_claim_rec.claim_id
1024 );
1025 IF l_return_status = FND_API.g_ret_sts_error THEN
1026 RAISE FND_API.g_exc_error;
1027 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1028 RAISE FND_API.g_exc_unexpected_error;
1029 END IF;
1030
1031 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
1032 l_online_upd_ded_status := FALSE;
1033 ELSE
1034 /*------------------------------------------------------------*
1035 | Update Claim Status to CLOSED.
1036 *------------------------------------------------------------*/
1037 /*
1038 Close_Claim(
1039 p_claim_rec => p_claim_rec
1040 ,x_return_status => l_return_status
1041 ,x_msg_data => x_msg_data
1042 ,x_msg_count => x_msg_count
1043 );
1044 IF l_return_status = FND_API.g_ret_sts_error THEN
1045 RAISE FND_API.g_exc_error;
1046 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1047 RAISE FND_API.g_exc_unexpected_error;
1048 END IF;
1049 */
1050
1051 /*------------------------------------------------------------*
1052 | Overpayment -> 1. Unapply claim investigation
1053 | 2. Apply debit memo on receipt.
1054 | <<Pay by Previous Open Debit Memo which already exists on the receipt>>:
1055 | Overpayment -> 1. Unapply claim investigation
1056 | 1.5. Unapply debit memo on receipt
1057 | 2. Apply debit memo on receipt.
1058 *------------------------------------------------------------*/
1059 OPEN csr_dm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
1060 FETCH csr_dm_exist_on_rec INTO l_dm_applied_on_rec_amt, l_apply_date; -- Fix for Bug 3091401. TM passes old apply date
1061 CLOSE csr_dm_exist_on_rec;
1062
1063 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation');
1064 -- 1. Unapply claim investigation
1065 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
1066 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
1067 CLOSE csr_claim_investigation_amount;
1068
1069 OZF_Utility_PVT.debug_message('original overpayment amount = '||l_orig_dispute_amount);
1070 OZF_Utility_PVT.debug_message('reapply overpayment amount = '||(l_orig_dispute_amount + p_debit_memo_amount));
1071
1072 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
1073 p_claim_rec => p_claim_rec
1074 ,p_reapply_amount => (l_orig_dispute_amount + p_debit_memo_amount)
1075 ,x_return_status => l_return_status
1076 ,x_msg_data => x_msg_data
1077 ,x_msg_count => x_msg_count
1078 );
1079 IF l_return_status = FND_API.g_ret_sts_error THEN
1080 RAISE FND_API.g_exc_error;
1081 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1082 RAISE FND_API.g_exc_unexpected_error;
1083 END IF;
1084
1085
1086 IF l_dm_applied_on_rec_amt IS NULL THEN
1087 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo on receipt');
1088 -- 2. Apply debit memo on receipt
1089 OZF_AR_Payment_PVT.Apply_on_Receipt(
1090 p_cash_receipt_id => p_claim_rec.receipt_id
1091 ,p_customer_trx_id => p_payment_reference_id
1092 ,p_new_applied_amount => p_debit_memo_amount * -1
1093 ,p_comments => p_claim_rec.comments
1094 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
1095 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
1096 ,x_return_status => l_return_status
1097 ,x_msg_data => x_msg_data
1098 ,x_msg_count => x_msg_count
1099 );
1100 IF l_return_status = FND_API.g_ret_sts_error THEN
1101 RAISE FND_API.g_exc_error;
1102 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1103 RAISE FND_API.g_exc_unexpected_error;
1104 END IF;
1105 ELSE
1106 OZF_Utility_PVT.debug_message('Overpayment: Pay by Previous Open Debit Memo which already exists on the receipt');
1107 OZF_Utility_PVT.debug_message('Overpayment -> 1.5. Unapply debit memo on receipt');
1108 -- 1.5. Unapply creit memo on receipt
1109 OZF_AR_Payment_PVT.Unapply_from_Receipt(
1110 p_cash_receipt_id => p_claim_rec.receipt_id
1111 ,p_customer_trx_id => p_payment_reference_id
1112 ,x_return_status => l_return_status
1113 ,x_msg_data => x_msg_data
1114 ,x_msg_count => x_msg_count
1115 );
1116 IF l_return_status = FND_API.g_ret_sts_error THEN
1117 RAISE FND_API.g_exc_error;
1118 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1119 RAISE FND_API.g_exc_unexpected_error;
1120 END IF;
1121
1122 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo with increased amount on receipt');
1123 l_settlement_amount := (p_debit_memo_amount * -1); --Bug4308188
1124 -- 2. Apply creit memo on receipt
1125 OZF_AR_Payment_PVT.Apply_on_Receipt(
1126 p_cash_receipt_id => p_claim_rec.receipt_id
1127 ,p_customer_trx_id => p_payment_reference_id
1128 ,p_new_applied_amount => l_dm_applied_on_rec_amt + (p_debit_memo_amount * -1)
1129 ,p_comments => p_claim_rec.comments
1130 ,p_apply_date => l_apply_date -- Fix for Bug 3091401. TM passes old apply date
1131 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
1132 ,x_return_status => l_return_status
1133 ,x_msg_data => x_msg_data
1134 ,x_msg_count => x_msg_count
1135 );
1136 IF l_return_status = FND_API.g_ret_sts_error THEN
1137 RAISE FND_API.g_exc_error;
1138 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1139 RAISE FND_API.g_exc_unexpected_error;
1140 END IF;
1141 END IF;
1142
1143 l_online_upd_ded_status := TRUE;
1144 END IF;
1145 ELSE
1146 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1147 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
1148 FND_MSG_PUB.add;
1149 END IF;
1150 END IF;
1151
1152
1153 /*------------------------------------------------------------*
1154 | Update Deduction payment detail
1155 *------------------------------------------------------------*/
1156 IF l_online_upd_ded_status THEN
1157 -- Update Deduction payment detail
1158 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1159 p_api_version => l_api_version
1160 ,p_init_msg_list => FND_API.g_false
1161 ,p_commit => FND_API.g_false
1162 ,p_validation_level => FND_API.g_valid_level_full
1163 ,x_return_status => l_return_status
1164 ,x_msg_data => x_msg_data
1165 ,x_msg_count => x_msg_count
1166 ,p_claim_id => p_claim_rec.claim_id
1167 ,p_payment_method => 'PREV_OPEN_DEBIT'
1168 ,p_deduction_type => p_deduction_type
1169 ,p_cash_receipt_id => p_claim_rec.receipt_id
1170 ,p_customer_trx_id => p_payment_reference_id
1171 ,p_adjust_id => NULL
1172 ,p_settlement_doc_id => p_settlement_doc_id
1173 ,p_settlement_mode => 'MASS_SETTLEMENT'
1174 ,p_settlement_amount => l_settlement_amount --Bug4308188
1175 );
1176 IF l_return_status = FND_API.g_ret_sts_error THEN
1177 RAISE FND_API.g_exc_error;
1178 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1179 RAISE FND_API.g_exc_unexpected_error;
1180 END IF;
1181 END IF;
1182
1183 IF OZF_DEBUG_HIGH_ON THEN
1184 OZF_Utility_PVT.debug_message(l_full_name||': end');
1185 END IF;
1186 EXCEPTION
1187 WHEN FND_API.g_exc_error THEN
1188 x_return_status := FND_API.g_ret_sts_error;
1189
1190 WHEN FND_API.g_exc_unexpected_error THEN
1191 x_return_status := FND_API.g_ret_sts_unexp_error ;
1192
1193 WHEN OTHERS THEN
1194 x_return_status := FND_API.g_ret_sts_unexp_error ;
1195 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1196 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1197 END IF;
1198
1199 END Pay_by_Debit_Memo;
1200
1201
1202 /*=======================================================================*
1203 | PROCEDURE
1204 | Pay_by_Chargeback
1205 |
1206 | NOTES
1207 |
1208 | HISTORY
1209 | 15-MAR-2002 mchang Create.
1210 *=======================================================================*/
1211 PROCEDURE Pay_by_Chargeback(
1212 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1213 ,p_deduction_type IN VARCHAR2
1214 ,p_chargeback_amount IN NUMBER
1215 ,p_settlement_doc_id IN NUMBER
1216 ,p_gl_date IN DATE
1217
1218 ,x_return_status OUT NOCOPY VARCHAR2
1219 ,x_msg_data OUT NOCOPY VARCHAR2
1220 ,x_msg_count OUT NOCOPY NUMBER
1221 )
1222 IS
1223 l_api_version CONSTANT NUMBER := 1.0;
1224 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Chargeback';
1225 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1226 l_return_status VARCHAR2(1);
1227
1228 l_cb_customer_trx_id NUMBER;
1229 l_chargeback_amount NUMBER;
1230
1231 BEGIN
1232 -------------------- initialize -----------------------
1233 IF OZF_DEBUG_HIGH_ON THEN
1234 OZF_Utility_PVT.debug_message(l_full_name||': start');
1235 END IF;
1236
1237 x_return_status := FND_API.g_ret_sts_success;
1238
1239 ------------------------ start -------------------------
1240 /*------------------------------------------------------------*
1241 | Update Claim Status to CLOSED.
1242 *------------------------------------------------------------*/
1243 /*
1244 Close_Claim(
1245 p_claim_rec => p_claim_rec
1246 ,x_return_status => l_return_status
1247 ,x_msg_data => x_msg_data
1248 ,x_msg_count => x_msg_count
1249 );
1250 IF l_return_status = FND_API.g_ret_sts_error THEN
1251 RAISE FND_API.g_exc_error;
1252 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1253 RAISE FND_API.g_exc_unexpected_error;
1254 END IF;
1255 */
1256 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED') THEN
1257 IF p_deduction_type = 'SOURCE_DED'THEN
1258 l_chargeback_amount := p_chargeback_amount;
1259 ELSIF p_deduction_type = 'RECEIPT_DED'THEN
1260 l_chargeback_amount := p_chargeback_amount * -1;
1261 END IF;
1262
1263 OZF_AR_Payment_PVT.Create_AR_Chargeback(
1264 p_claim_rec => p_claim_rec
1265 ,p_chargeback_amount => l_chargeback_amount
1266 ,p_gl_date => p_gl_date
1267 ,x_cb_customer_trx_id => l_cb_customer_trx_id
1268 ,x_return_status => l_return_status
1269 ,x_msg_data => x_msg_data
1270 ,x_msg_count => x_msg_count
1271 );
1272 IF l_return_status = FND_API.g_ret_sts_error THEN
1273 RAISE FND_API.g_exc_error;
1274 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1275 RAISE FND_API.g_exc_unexpected_error;
1276 END IF;
1277
1278 IF OZF_DEBUG_HIGH_ON THEN
1279 OZF_Utility_PVT.debug_message('x_cb_customer_trx_id = '||l_cb_customer_trx_id);
1280 END IF;
1281
1282 /*------------------------------------------------------------*
1283 | Update Deduction payment detail
1284 *------------------------------------------------------------*/
1285 IF l_cb_customer_trx_id IS NOT NULL THEN
1286 -- Update Deduction payment detail
1287 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1288 p_api_version => l_api_version
1289 ,p_init_msg_list => FND_API.g_false
1290 ,p_commit => FND_API.g_false
1291 ,p_validation_level => FND_API.g_valid_level_full
1292 ,x_return_status => l_return_status
1293 ,x_msg_data => x_msg_data
1294 ,x_msg_count => x_msg_count
1295 ,p_claim_id => p_claim_rec.claim_id
1296 ,p_payment_method => 'CHARGEBACK'
1297 ,p_deduction_type => p_deduction_type
1298 ,p_cash_receipt_id => p_claim_rec.receipt_id
1299 ,p_customer_trx_id => l_cb_customer_trx_id
1300 ,p_adjust_id => NULL
1301 ,p_settlement_doc_id => p_settlement_doc_id
1302 ,p_settlement_mode => 'MASS_SETTLEMENT'
1303 );
1304 IF l_return_status = FND_API.g_ret_sts_error THEN
1305 RAISE FND_API.g_exc_error;
1306 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1307 RAISE FND_API.g_exc_unexpected_error;
1308 END IF;
1309 END IF;
1310
1311 ELSE
1312 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1313 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
1314 FND_MSG_PUB.add;
1315 END IF;
1316 END IF;
1317
1318
1319 IF OZF_DEBUG_HIGH_ON THEN
1320 OZF_Utility_PVT.debug_message(l_full_name||': end');
1321 END IF;
1322 EXCEPTION
1323 WHEN FND_API.g_exc_error THEN
1324 x_return_status := FND_API.g_ret_sts_error;
1325
1326 WHEN FND_API.g_exc_unexpected_error THEN
1327 x_return_status := FND_API.g_ret_sts_unexp_error ;
1328
1329 WHEN OTHERS THEN
1330 x_return_status := FND_API.g_ret_sts_unexp_error ;
1331 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1332 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1333 END IF;
1334
1335 END Pay_by_Chargeback;
1336
1337
1338 /*=======================================================================*
1339 | PROCEDURE
1340 | Pay_by_Write_Off
1341 |
1342 | NOTES
1343 |
1344 | HISTORY
1345 | 15-MAR-2002 mchang Create.
1346 *=======================================================================*/
1347 PROCEDURE Pay_by_Write_Off(
1348 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1349 ,p_deduction_type IN VARCHAR2
1350 ,p_write_off_amount IN NUMBER
1351 ,p_settlement_doc_id IN NUMBER
1352 ,p_gl_date IN DATE
1353 ,p_wo_rec_trx_id IN NUMBER
1354
1355 ,x_return_status OUT NOCOPY VARCHAR2
1356 ,x_msg_data OUT NOCOPY VARCHAR2
1357 ,x_msg_count OUT NOCOPY NUMBER
1358 )
1359 IS
1360 l_api_version CONSTANT NUMBER := 1.0;
1361 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Write_Off';
1362 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1363 l_return_status VARCHAR2(1);
1364
1365 l_wo_adjust_id NUMBER;
1366
1367 BEGIN
1368 -------------------- initialize -----------------------
1369 IF OZF_DEBUG_HIGH_ON THEN
1370 OZF_Utility_PVT.debug_message(l_full_name||': start');
1371 END IF;
1372
1373 x_return_status := FND_API.g_ret_sts_success;
1374
1375 ------------------------ start -------------------------
1376 IF p_deduction_type = 'RECEIPT_DED' AND
1377 NOT ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed THEN
1378 /*------------------------------------------------------------
1379 | Receipt Deduction -> Invoke Settlement Workflow
1380 *-----------------------------------------------------------*/
1381 OZF_AR_PAYMENT_PVT.Process_Settlement_WF(
1382 p_claim_id => p_claim_rec.claim_id
1383 ,x_return_status => l_return_status
1384 ,x_msg_data => x_msg_data
1385 ,x_msg_count => x_msg_count
1386 );
1387 IF l_return_status = FND_API.g_ret_sts_error THEN
1388 RAISE FND_API.g_exc_error;
1389 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1390 RAISE FND_API.g_exc_unexpected_error;
1391 END IF;
1392
1393 BEGIN
1394 UPDATE ozf_claims_all
1395 SET payment_status = 'PENDING'
1396 , status_code = 'PENDING_CLOSE'
1397 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
1398 'OZF_CLAIM_STATUS'
1399 ,'PENDING_CLOSE'
1400 )
1401 WHERE claim_id = p_claim_rec.claim_id;
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1405 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
1406 FND_MSG_PUB.add;
1407 END IF;
1408 IF OZF_DEBUG_LOW_ON THEN
1409 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1410 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1411 FND_MSG_PUB.Add;
1412 END IF;
1413 RAISE FND_API.g_exc_unexpected_error;
1414 END;
1415
1416 -- Added For ER#14109799
1417 ELSIF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED', 'RECEIPT_OPM','SOURCE_OPM') THEN
1418 /*------------------------------------------------------------*
1419 | Update Claim Status to CLOSED.
1420 *------------------------------------------------------------*/
1421 /*
1422 Close_Claim(
1423 p_claim_rec => p_claim_rec
1424 ,x_return_status => l_return_status
1425 ,x_msg_data => x_msg_data
1426 ,x_msg_count => x_msg_count
1427 );
1428 IF l_return_status = FND_API.g_ret_sts_error THEN
1429 RAISE FND_API.g_exc_error;
1430 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1431 RAISE FND_API.g_exc_unexpected_error;
1432 END IF;
1433 */
1434
1435 OZF_AR_Payment_PVT.Create_AR_Write_Off(
1436 p_claim_rec => p_claim_rec
1437 ,p_deduction_type => p_deduction_type
1438 ,p_write_off_amount => p_write_off_amount
1439 ,p_gl_date => p_gl_date
1440 ,p_wo_rec_trx_id => p_wo_rec_trx_id
1441 ,p_settlement_doc_id => p_settlement_doc_id -- Added for Bug 13948201
1442 ,x_wo_adjust_id => l_wo_adjust_id
1443 ,x_return_status => l_return_status
1444 ,x_msg_data => x_msg_data
1445 ,x_msg_count => x_msg_count
1446 );
1447 IF l_return_status = FND_API.g_ret_sts_error THEN
1448 RAISE FND_API.g_exc_error;
1449 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1450 RAISE FND_API.g_exc_unexpected_error;
1451 END IF;
1452 END IF;
1453
1454 IF OZF_DEBUG_HIGH_ON THEN
1455 OZF_Utility_PVT.debug_message(l_full_name||': end');
1456 END IF;
1457 EXCEPTION
1458 WHEN FND_API.g_exc_error THEN
1459 x_return_status := FND_API.g_ret_sts_error;
1460
1461 WHEN FND_API.g_exc_unexpected_error THEN
1462 x_return_status := FND_API.g_ret_sts_unexp_error ;
1463
1464 WHEN OTHERS THEN
1465 x_return_status := FND_API.g_ret_sts_unexp_error ;
1466 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1467 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1468 END IF;
1469
1470 END Pay_by_Write_Off;
1471
1472
1473 /*=======================================================================*
1474 | PROCEDURE
1475 | Pay_by_On_Account_Credit
1476 |
1477 | NOTES
1478 |
1479 | HISTORY
1480 | 15-MAR-2002 mchang Create.
1481 *=======================================================================*/
1482 PROCEDURE Pay_by_On_Account_Credit(
1483 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1484 ,p_deduction_type IN VARCHAR2
1485 ,p_credit_amount IN NUMBER
1486 ,p_settlement_doc_id IN NUMBER
1487
1488 ,x_return_status OUT NOCOPY VARCHAR2
1489 ,x_msg_data OUT NOCOPY VARCHAR2
1490 ,x_msg_count OUT NOCOPY NUMBER
1491 )
1492 IS
1493 l_api_version CONSTANT NUMBER := 1.0;
1494 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_On_Account_Credit';
1495 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1496 l_return_status VARCHAR2(1);
1497
1498 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
1499 , cv_root_claim_id IN NUMBER
1500 ) IS
1501 SELECT rec.amount_applied
1502 FROM ar_receivable_applications rec
1503 WHERE rec.applied_payment_schedule_id = -4
1504 AND rec.cash_receipt_id = cv_cash_receipt_id
1505 AND rec.application_ref_type = 'CLAIM'
1506 AND rec.display = 'Y'
1507 AND rec.secondary_application_ref_id = cv_root_claim_id;
1508
1509 l_old_applied_claim_amount NUMBER;
1510 l_reapply_claim_amount NUMBER;
1511
1512 BEGIN
1513 -------------------- initialize -----------------------
1514 IF OZF_DEBUG_HIGH_ON THEN
1515 OZF_Utility_PVT.debug_message(l_full_name||': start');
1516 END IF;
1517
1518 x_return_status := FND_API.g_ret_sts_success;
1519
1520 ------------------------ start -------------------------
1521 /*------------------------------------------------------------*
1522 | Update Claim Status to CLOSED.
1523 *------------------------------------------------------------*/
1524 /*
1525 Close_Claim(
1526 p_claim_rec => p_claim_rec
1527 ,x_return_status => l_return_status
1528 ,x_msg_data => x_msg_data
1529 ,x_msg_count => x_msg_count
1530 );
1531 IF l_return_status = FND_API.g_ret_sts_error THEN
1532 RAISE FND_API.g_exc_error;
1533 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1534 RAISE FND_API.g_exc_unexpected_error;
1535 END IF;
1536 */
1537
1538 IF p_deduction_type = 'RECEIPT_OPM' THEN
1539 /*------------------------------------------------------------*
1540 | Overpayment -> 1. Unapply claim investigation
1541 | 2. Apply On Account Credit
1542 *------------------------------------------------------------*/
1543 IF OZF_DEBUG_HIGH_ON THEN
1544 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation.');
1545 END IF;
1546 -- 1. Unapply claim investigation
1547 OPEN csr_old_claim_investigation(p_claim_rec.receipt_id, p_claim_rec.root_claim_id);
1548 FETCH csr_old_claim_investigation INTO l_old_applied_claim_amount;
1549 CLOSE csr_old_claim_investigation;
1550
1551 l_reapply_claim_amount := l_old_applied_claim_amount - (p_credit_amount * -1);
1552
1553 OZF_AR_PAYMENT_PVT.Unapply_Claim_Investigation(
1554 p_claim_rec => p_claim_rec
1555 ,p_reapply_amount => l_reapply_claim_amount --0
1556 ,x_return_status => l_return_status
1557 ,x_msg_data => x_msg_data
1558 ,x_msg_count => x_msg_count
1559 );
1560 IF l_return_status = FND_API.g_ret_sts_error THEN
1561 RAISE FND_API.g_exc_error;
1562 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1563 RAISE FND_API.g_exc_unexpected_error;
1564 END IF;
1565
1566 IF OZF_DEBUG_HIGH_ON THEN
1567 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply On Account Credit.');
1568 END IF;
1569 --2. Apply On Account Credit
1570 OZF_AR_PAYMENT_PVT.Apply_On_Account_Credit(
1571 p_claim_rec => p_claim_rec
1572 ,p_credit_amount => p_credit_amount
1573 ,x_return_status => l_return_status
1574 ,x_msg_data => x_msg_data
1575 ,x_msg_count => x_msg_count
1576 );
1577 IF l_return_status = FND_API.g_ret_sts_error THEN
1578 RAISE FND_API.g_exc_error;
1579 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1580 RAISE FND_API.g_exc_unexpected_error;
1581 END IF;
1582
1583 /*------------------------------------------------------------*
1584 | Update Deduction payment detail
1585 *------------------------------------------------------------*/
1586 -- Update Deduction payment detail
1587 IF OZF_DEBUG_HIGH_ON THEN
1588 OZF_Utility_PVT.debug_message('cash_receipt_id = '||p_claim_rec.receipt_id);
1589 END IF;
1590 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1591 p_api_version => l_api_version
1592 ,p_init_msg_list => FND_API.g_false
1593 ,p_commit => FND_API.g_false
1594 ,p_validation_level => FND_API.g_valid_level_full
1595 ,x_return_status => l_return_status
1596 ,x_msg_data => x_msg_data
1597 ,x_msg_count => x_msg_count
1598 ,p_claim_id => p_claim_rec.claim_id
1599 ,p_payment_method => p_claim_rec.payment_method
1600 ,p_deduction_type => p_deduction_type
1601 ,p_cash_receipt_id => p_claim_rec.receipt_id
1602 ,p_customer_trx_id => NULL --p_claim_rec.payment_reference_id
1603 ,p_adjust_id => NULL
1604 ,p_settlement_doc_id => p_settlement_doc_id
1605 ,p_settlement_mode => 'MASS_SETTLEMENT'
1606 );
1607 IF l_return_status = FND_API.g_ret_sts_error THEN
1608 RAISE FND_API.g_exc_error;
1609 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1610 RAISE FND_API.g_exc_unexpected_error;
1611 END IF;
1612
1613 ELSE
1614 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1615 FND_MESSAGE.set_name('AMS', 'AMS_AR_PAYMENT_NOTMATCH');
1616 FND_MSG_PUB.add;
1617 END IF;
1618 RAISE FND_API.g_exc_error;
1619 END IF;
1620
1621 IF OZF_DEBUG_HIGH_ON THEN
1622 OZF_Utility_PVT.debug_message(l_full_name||': end');
1623 END IF;
1624 EXCEPTION
1625 WHEN FND_API.g_exc_error THEN
1626 x_return_status := FND_API.g_ret_sts_error;
1627
1628 WHEN FND_API.g_exc_unexpected_error THEN
1629 x_return_status := FND_API.g_ret_sts_unexp_error ;
1630
1631 WHEN OTHERS THEN
1632 x_return_status := FND_API.g_ret_sts_unexp_error ;
1633 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1634 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1635 END IF;
1636
1637 END Pay_by_On_Account_Credit;
1638
1639
1640 /*=======================================================================*
1641 | PROCEDURE
1642 | Break_Mass_Settlement
1643 |
1644 | NOTES
1645 |
1646 | HISTORY
1647 | 20-OCT-2003 mchang Create.
1648 *=======================================================================*/
1649 PROCEDURE Break_Mass_Settlement(
1650 p_group_claim_id IN NUMBER,
1651 p_settlement_type IN VARCHAR2,
1652 x_return_status OUT NOCOPY VARCHAR2,
1653 x_msg_data OUT NOCOPY VARCHAR2,
1654 x_msg_count OUT NOCOPY NUMBER
1655 )
1656 IS
1657 l_api_version CONSTANT NUMBER := 1.0;
1658 l_api_name CONSTANT VARCHAR2(30) := 'Break_Mass_Settlement';
1659 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1660 l_return_status VARCHAR2(1);
1661 l_source_object_id NUMBER;
1662 l_wo_rec_trx_id NUMBER;
1663 ---
1664
1665 CURSOR csr_claims( cv_group_claim_id IN NUMBER
1666 , cv_claim_class IN VARCHAR2
1667 ) IS
1668 SELECT claim_id
1669 , claim_number
1670 , claim_class
1671 , receipt_id
1672 , receipt_number
1673 , source_object_id
1674 , source_object_number
1675 , amount_remaining
1676 FROM ozf_claims_all
1677 WHERE group_claim_id = cv_group_claim_id
1678 AND claim_class = cv_claim_class
1679 ORDER BY claim_date, group_claim_id;
1680
1681
1682 CURSOR csr_settle_docs_group(cv_group_claim_id IN NUMBER) IS
1683 SELECT settlement_doc_id
1684 , payment_method
1685 , settlement_id
1686 , settlement_number
1687 , settlement_type_id
1688 , settlement_amount
1689 , gl_date
1690 , wo_rec_trx_id
1691 FROM ozf_settlement_docs_all
1692 WHERE claim_id = cv_group_claim_id;
1693 -- add addtional order by clause for mass settlement ordering rule criteria
1694
1695 CURSOR csr_get_source_object_id(cv_claim_id IN NUMBER) IS
1696 SELECT source_object_id
1697 FROM ozf_claims
1698 WHERE claim_id = cv_claim_id;
1699
1700 TYPE l_csr_claim_tbl IS TABLE OF csr_claims%ROWTYPE
1701 INDEX BY BINARY_INTEGER;
1702 --l_claim_group_tbl l_csr_claim_tbl;
1703 --l_claim_netting_tbl l_csr_claim_tbl;
1704 l_claim_group_tbl OZF_Claim_PVT.claim_tbl_type;
1705 l_claim_netting_tbl OZF_Claim_PVT.claim_tbl_type;
1706 l_settle_doc_group_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1707 l_settle_doc_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1708 l_settle_doc_tbl2 OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1709 l_settle_doc_id_tbl JTF_NUMBER_TABLE;
1710 l_group_claim_line_rec OZF_Claim_line_Pvt.claim_line_rec_type;
1711 l_group_claim_line_tbl OZF_Claim_line_Pvt.claim_line_tbl_type;
1712 l_idx_netting_claim NUMBER;
1713 l_idx_settle_doc_group NUMBER;
1714 l_idx NUMBER := 1;
1715 l_idx_setl_doc NUMBER := 1;
1716 l_idx_setl_doc2 NUMBER := 1;
1717 l_amount_settled NUMBER;
1718 i BINARY_INTEGER;
1719 l_err_idx NUMBER;
1720
1721 BEGIN
1722 -------------------- initialize -----------------------
1723 SAVEPOINT Break_Mass_Settlement;
1724
1725 IF OZF_DEBUG_HIGH_ON THEN
1726 OZF_Utility_PVT.debug_message(l_full_name||': start');
1727 END IF;
1728
1729 x_return_status := FND_API.G_RET_STS_SUCCESS;
1730
1731 ------------------------ start -------------------------
1732 IF OZF_DEBUG_HIGH_ON THEN
1733 OZF_Utility_PVT.debug_message('Settlement Type = '||p_settlement_type);
1734 END IF;
1735
1736 IF p_settlement_type <> 'NETTING' THEN
1737 l_idx := 1;
1738 OPEN csr_settle_docs_group(p_group_claim_id);
1739 LOOP
1740 FETCH csr_settle_docs_group INTO l_settle_doc_group_tbl(l_idx).settlement_doc_id
1741 , l_settle_doc_group_tbl(l_idx).payment_method
1742 , l_settle_doc_group_tbl(l_idx).settlement_id
1743 , l_settle_doc_group_tbl(l_idx).settlement_number
1744 , l_settle_doc_group_tbl(l_idx).settlement_type_id
1745 , l_settle_doc_group_tbl(l_idx).settlement_amount
1746 , l_settle_doc_group_tbl(l_idx).gl_date
1747 , l_settle_doc_group_tbl(l_idx).wo_rec_trx_id;
1748 EXIT WHEN csr_settle_docs_group%NOTFOUND;
1749 l_idx := l_idx + 1;
1750 END LOOP;
1751 CLOSE csr_settle_docs_group;
1752 END IF;
1753
1754
1755 l_idx := 1;
1756 IF p_settlement_type = 'NETTING' THEN
1757 OPEN csr_claims(p_group_claim_id, 'DEDUCTION');
1758 LOOP
1759 FETCH csr_claims INTO l_claim_group_tbl(l_idx).claim_id
1760 , l_claim_group_tbl(l_idx).claim_number
1761 , l_claim_group_tbl(l_idx).claim_class
1762 , l_claim_group_tbl(l_idx).receipt_id
1763 , l_claim_group_tbl(l_idx).receipt_number
1764 , l_claim_group_tbl(l_idx).source_object_id
1765 , l_claim_group_tbl(l_idx).source_object_number
1766 , l_claim_group_tbl(l_idx).amount_remaining;
1767 EXIT WHEN csr_claims%NOTFOUND;
1768 l_idx := l_idx + 1;
1769 END LOOP;
1770 CLOSE csr_claims;
1771 ELSE
1772 OPEN csr_claims(p_group_claim_id, p_settlement_type);
1773 LOOP
1774 FETCH csr_claims INTO l_claim_group_tbl(l_idx).claim_id
1775 , l_claim_group_tbl(l_idx).claim_number
1776 , l_claim_group_tbl(l_idx).claim_class
1777 , l_claim_group_tbl(l_idx).receipt_id
1778 , l_claim_group_tbl(l_idx).receipt_number
1779 , l_claim_group_tbl(l_idx).source_object_id
1780 , l_claim_group_tbl(l_idx).source_object_number
1781 , l_claim_group_tbl(l_idx).amount_remaining;
1782 EXIT WHEN csr_claims%NOTFOUND;
1783 l_idx := l_idx + 1;
1784 END LOOP;
1785 CLOSE csr_claims;
1786 END IF;
1787
1788 l_idx := 1;
1789 IF p_settlement_type IN ('DEDUCTION', 'NETTING') THEN
1790 OPEN csr_claims(p_group_claim_id, 'OVERPAYMENT');
1791 LOOP
1792 FETCH csr_claims INTO l_claim_netting_tbl(l_idx).claim_id
1793 , l_claim_netting_tbl(l_idx).claim_number
1794 , l_claim_netting_tbl(l_idx).claim_class
1795 , l_claim_netting_tbl(l_idx).receipt_id
1796 , l_claim_netting_tbl(l_idx).receipt_number
1797 , l_claim_netting_tbl(l_idx).source_object_id
1798 , l_claim_netting_tbl(l_idx).source_object_number
1799 , l_claim_netting_tbl(l_idx).amount_remaining;
1800 EXIT WHEN csr_claims%NOTFOUND;
1801 l_idx := l_idx + 1;
1802 END LOOP;
1803 CLOSE csr_claims;
1804 ELSIF p_settlement_type = 'OVERPAYMENT' THEN
1805 OPEN csr_claims(p_group_claim_id, 'DEDUCTION');
1806 LOOP
1807 FETCH csr_claims INTO l_claim_netting_tbl(l_idx).claim_id
1808 , l_claim_netting_tbl(l_idx).claim_number
1809 , l_claim_netting_tbl(l_idx).claim_class
1810 , l_claim_netting_tbl(l_idx).receipt_id
1811 , l_claim_netting_tbl(l_idx).receipt_number
1812 , l_claim_netting_tbl(l_idx).source_object_id
1813 , l_claim_netting_tbl(l_idx).source_object_number
1814 , l_claim_netting_tbl(l_idx).amount_remaining;
1815 EXIT WHEN csr_claims%NOTFOUND;
1816 l_idx := l_idx + 1;
1817 END LOOP;
1818 CLOSE csr_claims;
1819 END IF;
1820
1821 l_idx_netting_claim := l_claim_netting_tbl.FIRST;
1822 l_idx_settle_doc_group := l_settle_doc_group_tbl.FIRST;
1823
1824 l_idx_setl_doc := 1;
1825 i := l_claim_group_tbl.FIRST;
1826 IF i IS NOT NULL THEN
1827 LOOP
1828 l_group_claim_line_tbl(i).claim_id := p_group_claim_id;
1829 l_group_claim_line_tbl(i).claim_currency_amount := l_claim_group_tbl(i).amount_remaining;
1830 l_group_claim_line_tbl(i).payment_reference_id := l_claim_group_tbl(i).claim_id;
1831 l_group_claim_line_tbl(i).payment_reference_number := l_claim_group_tbl(i).claim_number;
1832 l_group_claim_line_tbl(i).payment_method := 'MASS_SETTLEMENT';
1833
1834 --------------------------------------
1835 -- Assign Settlement Docs for Claim --
1836 --------------------------------------
1837 l_amount_settled := l_claim_group_tbl(i).amount_remaining;
1838
1839 WHILE ABS(l_amount_settled) > 0 LOOP
1840 -- Get settlement docs from netting claims
1841 WHILE l_idx_netting_claim IS NOT NULL AND
1842 l_amount_settled <> 0 LOOP
1843 l_settle_doc_tbl(l_idx_setl_doc).claim_id := l_claim_group_tbl(i).claim_id;
1844 l_settle_doc_tbl(l_idx_setl_doc).settlement_id := l_claim_netting_tbl(l_idx_netting_claim).receipt_id;
1845 --l_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := ??;
1846 l_settle_doc_tbl(l_idx_setl_doc).settlement_number := l_claim_netting_tbl(l_idx_netting_claim).receipt_number;
1847 l_settle_doc_tbl(l_idx_setl_doc).payment_method := l_claim_netting_tbl(l_idx_netting_claim).claim_class;
1848 l_settle_doc_tbl(l_idx_setl_doc).payment_reference_id := l_claim_netting_tbl(l_idx_netting_claim).claim_id;
1849 l_settle_doc_tbl(l_idx_setl_doc).payment_reference_number := l_claim_netting_tbl(l_idx_netting_claim).claim_number;
1850 l_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
1851 l_settle_doc_tbl(l_idx_setl_doc).group_claim_id := p_group_claim_id;
1852
1853 l_settle_doc_tbl2(l_idx_setl_doc2).claim_id := l_claim_netting_tbl(l_idx_netting_claim).claim_id;
1854 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_id := l_claim_group_tbl(i).receipt_id;
1855 --l_settle_doc_tbl2(l_idx_setl_doc2).settlement_type_id := ??;
1856 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_number := l_claim_group_tbl(i).receipt_number;
1857 l_settle_doc_tbl2(l_idx_setl_doc2).payment_method := l_claim_group_tbl(i).claim_class;
1858 l_settle_doc_tbl2(l_idx_setl_doc2).payment_reference_id := l_claim_group_tbl(i).claim_id;
1859 l_settle_doc_tbl2(l_idx_setl_doc2).payment_reference_number := l_claim_group_tbl(i).claim_number;
1860 l_settle_doc_tbl2(l_idx_setl_doc2).payment_status := 'PENDING';
1861 l_settle_doc_tbl2(l_idx_setl_doc2).group_claim_id := p_group_claim_id;
1862
1863
1864 IF l_claim_netting_tbl(l_idx_netting_claim).amount_remaining = 0 THEN
1865 -- Bug4386869: Amount is already utilized
1866
1867 l_settle_doc_tbl2.delete(l_idx_setl_doc2);
1868 -- l_idx_setl_doc2 := l_idx_setl_doc2 - 1; --AMITAMKU, Fix for Bug 14572311
1869 EXIT WHEN l_idx_netting_claim = l_claim_netting_tbl.LAST;
1870 l_idx_netting_claim := l_claim_netting_tbl.NEXT(l_idx_netting_claim);
1871
1872 ELSIF ABS(l_amount_settled) >= ABS(l_claim_netting_tbl(l_idx_netting_claim).amount_remaining) THEN
1873 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_claim_netting_tbl(l_idx_netting_claim).amount_remaining;
1874
1875 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_amount:= l_settle_doc_tbl(l_idx_setl_doc).settlement_amount * -1;
1876
1877 l_amount_settled := l_amount_settled
1878 - (l_claim_netting_tbl(l_idx_netting_claim).amount_remaining * -1);
1879
1880
1881 l_claim_netting_tbl(l_idx_netting_claim).amount_remaining := 0;
1882
1883 IF OZF_DEBUG_HIGH_ON THEN
1884 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1885 l_group_claim_line_tbl(i).payment_reference_number||
1886 ' ... '||
1887 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1888 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_reference_number||
1889 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1890 ')'
1891 );
1892 END IF;
1893
1894 l_idx_setl_doc := l_idx_setl_doc + 1;
1895 l_idx_setl_doc2 := l_idx_setl_doc2 + 1;
1896
1897 EXIT WHEN l_idx_netting_claim = l_claim_netting_tbl.LAST;
1898 l_idx_netting_claim := l_claim_netting_tbl.NEXT(l_idx_netting_claim);
1899 -- l_idx_setl_doc2 := 1; --AMITAMKU, Fix for Bug 14572311
1900 ELSE
1901 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_amount_settled * -1;
1902
1903 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_amount:= l_settle_doc_tbl(l_idx_setl_doc).settlement_amount * -1;
1904
1905 l_claim_netting_tbl(l_idx_netting_claim).amount_remaining := l_claim_netting_tbl(l_idx_netting_claim).amount_remaining
1906 - (l_amount_settled * -1);
1907
1908
1909 l_amount_settled := 0;
1910
1911 IF OZF_DEBUG_HIGH_ON THEN
1912 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1913 l_group_claim_line_tbl(i).payment_reference_number||
1914 ' ... '||
1915 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1916 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_reference_number||
1917 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1918 ')'
1919 );
1920 END IF;
1921
1922 l_idx_setl_doc := l_idx_setl_doc + 1;
1923 l_idx_setl_doc2 := l_idx_setl_doc2 + 1;
1924
1925 EXIT WHEN l_amount_settled = 0;
1926 EXIT WHEN l_idx_netting_claim = l_claim_netting_tbl.LAST;
1927 --l_idx_netting_claim := l_claim_netting_tbl.NEXT(l_idx_netting_claim);
1928 END IF;
1929 END lOOP;
1930
1931 -- Get settlement docs from open/new transactions
1932 WHILE l_idx_settle_doc_group IS NOT NULL AND
1933 l_amount_settled <> 0 LOOP
1934 l_settle_doc_tbl(l_idx_setl_doc).claim_id := l_claim_group_tbl(i).claim_id;
1935 l_settle_doc_tbl(l_idx_setl_doc).settlement_id := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_id;
1936 l_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_type_id;
1937 l_settle_doc_tbl(l_idx_setl_doc).settlement_number := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_number;
1938 l_settle_doc_tbl(l_idx_setl_doc).payment_method := l_settle_doc_group_tbl(l_idx_settle_doc_group).payment_method;
1939 l_settle_doc_tbl(l_idx_setl_doc).gl_date := l_settle_doc_group_tbl(l_idx_settle_doc_group).gl_date;
1940 l_settle_doc_tbl(l_idx_setl_doc).wo_rec_trx_id := l_settle_doc_group_tbl(l_idx_settle_doc_group).wo_rec_trx_id;
1941 l_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
1942 l_settle_doc_tbl(l_idx_setl_doc).group_claim_id := p_group_claim_id;
1943
1944 IF l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount = 0 THEN
1945 -- Bug4386869: Amount is already utilized
1946
1947 EXIT WHEN l_idx_settle_doc_group = l_settle_doc_group_tbl.LAST;
1948 l_idx_settle_doc_group := l_settle_doc_group_tbl.NEXT(l_idx_settle_doc_group);
1949
1950 ELSIF ABS(l_amount_settled) >= ABS(l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount) THEN
1951 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount;
1952
1953 l_amount_settled := l_amount_settled + l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount;
1954
1955 l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount := 0;
1956
1957 IF OZF_DEBUG_HIGH_ON THEN
1958 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1959 l_group_claim_line_tbl(i).payment_reference_number||
1960 ' ... '||
1961 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1962 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_method||
1963 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1964 ')'
1965 );
1966 END IF;
1967
1968 l_idx_setl_doc := l_idx_setl_doc + 1;
1969
1970 EXIT WHEN l_idx_settle_doc_group = l_settle_doc_group_tbl.LAST;
1971 l_idx_settle_doc_group := l_settle_doc_group_tbl.NEXT(l_idx_settle_doc_group);
1972 ELSE
1973
1974 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_amount_settled * -1;
1975
1976 l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount
1977 + l_amount_settled;
1978
1979 l_amount_settled := 0;
1980
1981 IF OZF_DEBUG_HIGH_ON THEN
1982 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1983 l_group_claim_line_tbl(i).payment_reference_number||
1984 ' ... '||
1985 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1986 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_method||
1987 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1988 ')'
1989 );
1990 END IF;
1991
1992 l_idx_setl_doc := l_idx_setl_doc + 1;
1993
1994 EXIT WHEN l_amount_settled = 0;
1995 EXIT WHEN l_idx_settle_doc_group = l_settle_doc_group_tbl.LAST;
1996 l_idx_settle_doc_group := l_settle_doc_group_tbl.NEXT(l_idx_settle_doc_group);
1997 END IF;
1998
1999 END lOOP;
2000 END LOOP;
2001 EXIT WHEN i = l_claim_group_tbl.LAST;
2002 i := l_claim_group_tbl.NEXT(i);
2003 END LOOP;
2004 END IF;
2005 --//Bug 5345095
2006 i := l_settle_doc_tbl.FIRST;
2007 IF i IS NOT NULL THEN
2008 LOOP
2009 IF(l_settle_doc_tbl(i).payment_method = 'WRITE_OFF') THEN
2010 OPEN csr_get_source_object_id(l_settle_doc_tbl(i).claim_id);
2011 FETCH csr_get_source_object_id INTO l_source_object_id;
2012 CLOSE csr_get_source_object_id;
2013
2014
2015 IF(l_source_object_id IS NULL) THEN
2016 l_wo_rec_trx_id := l_settle_doc_tbl(i).wo_rec_trx_id;
2017 ELSE
2018 l_wo_rec_trx_id := l_settle_doc_tbl(i).settlement_type_id;
2019 END IF;
2020
2021 IF l_wo_rec_trx_id IS NOT NULL THEN
2022 l_settle_doc_tbl(i).wo_rec_trx_id := l_wo_rec_trx_id;
2023 l_settle_doc_tbl(i).settlement_type_id := null;
2024 END IF;
2025 END IF;
2026
2027 EXIT WHEN i = l_settle_doc_tbl.LAST;
2028 i := l_settle_doc_tbl.NEXT(i);
2029 END LOOP;
2030 END IF;
2031
2032
2033 IF OZF_DEBUG_HIGH_ON THEN
2034 i := l_group_claim_line_tbl.FIRST;
2035 IF i IS NOT NULL THEN
2036 OZF_Utility_PVT.debug_message('--- Mass Settlement Claims ---');
2037 LOOP
2038 OZF_Utility_PVT.debug_message('--- '||i||' ---');
2039 OZF_Utility_PVT.debug_message('l_group_claim_line_tbl('||i||').claim_currency_amount = '||l_group_claim_line_tbl(i).claim_currency_amount);
2040 OZF_Utility_PVT.debug_message('l_group_claim_line_tbl('||i||').payment_reference_number = '||l_group_claim_line_tbl(i).payment_reference_number);
2041 EXIT WHEN i = l_group_claim_line_tbl.LAST;
2042 i := l_group_claim_line_tbl.NEXT(i);
2043 END LOOP;
2044 END IF;
2045 END IF;
2046
2047 -----------------------------------------------
2048 -- Create break Claim Lines for Master Claim --
2049 -----------------------------------------------
2050 IF l_group_claim_line_tbl.COUNT IS NOT NULL AND
2051 l_group_claim_line_tbl.COUNT > 0 THEN
2052 OZF_Claim_Line_PVT.Create_Claim_Line_Tbl(
2053 p_api_version => l_api_version
2054 ,p_init_msg_list => FND_API.g_false
2055 ,p_commit => FND_API.g_false
2056 ,p_validation_level => FND_API.g_valid_level_full
2057 ,x_return_status => l_return_status
2058 ,x_msg_data => x_msg_data
2059 ,x_msg_count => x_msg_count
2060 ,p_claim_line_tbl => l_group_claim_line_tbl
2061 ,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
2062 ,x_error_index => l_err_idx
2063 );
2064 IF l_return_status = FND_API.g_ret_sts_error THEN
2065 RAISE FND_API.g_exc_error;
2066 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2067 RAISE FND_API.g_exc_unexpected_error;
2068 END IF;
2069 END IF;
2070
2071
2072 IF OZF_DEBUG_HIGH_ON THEN
2073 i := l_settle_doc_tbl.FIRST;
2074 IF i IS NOT NULL THEN
2075 OZF_Utility_PVT.debug_message('--- Mass Settlement Payment Details ---');
2076 LOOP
2077 OZF_Utility_PVT.debug_message('--- '||i||' ---');
2078 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').claim_id = '||l_settle_doc_tbl(i).claim_id);
2079 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').settlement_id = '||l_settle_doc_tbl(i).settlement_id);
2080 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').settlement_number = '||l_settle_doc_tbl(i).settlement_number);
2081 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').settlement_amount = '||l_settle_doc_tbl(i).settlement_amount);
2082 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').payment_method = '||l_settle_doc_tbl(i).payment_method);
2083 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').payment_reference_id = '||l_settle_doc_tbl(i).payment_reference_id);
2084 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').payment_reference_number = '||l_settle_doc_tbl(i).payment_reference_number);
2085 EXIT WHEN i = l_settle_doc_tbl.LAST;
2086 i := l_settle_doc_tbl.NEXT(i);
2087 END LOOP;
2088 END IF;
2089 i := l_settle_doc_tbl2.FIRST;
2090 IF i IS NOT NULL THEN
2091 OZF_Utility_PVT.debug_message('--- Mass Settlement Group Payment Details ---');
2092 LOOP
2093 OZF_Utility_PVT.debug_message('--- '||i||' ---');
2094 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').claim_id = '||l_settle_doc_tbl2(i).claim_id);
2095 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').settlement_id = '||l_settle_doc_tbl2(i).settlement_id);
2096 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').settlement_number = '||l_settle_doc_tbl2(i).settlement_number);
2097 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').settlement_amount = '||l_settle_doc_tbl2(i).settlement_amount);
2098 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').payment_method = '||l_settle_doc_tbl2(i).payment_method);
2099 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').payment_reference_id = '||l_settle_doc_tbl2(i).payment_reference_id);
2100 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').payment_reference_number = '||l_settle_doc_tbl2(i).payment_reference_number);
2101 EXIT WHEN i = l_settle_doc_tbl2.LAST;
2102 i := l_settle_doc_tbl2.NEXT(i);
2103 END LOOP;
2104 END IF;
2105 END IF;
2106
2107 ----------------------------------
2108 -- Create break Settlement Docs --
2109 ----------------------------------
2110 IF l_settle_doc_tbl.COUNT IS NOT NULL AND
2111 l_settle_doc_tbl.COUNT > 0 THEN
2112 OZF_Settlement_Doc_PVT.Create_Settlement_Doc_Tbl(
2113 p_api_version_number => l_api_version,
2114 p_init_msg_list => FND_API.G_FALSE,
2115 p_commit => FND_API.G_FALSE,
2116 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2117 x_return_status => l_return_status,
2118 x_msg_count => x_msg_count,
2119 x_msg_data => x_msg_data,
2120 p_settlement_doc_tbl => l_settle_doc_tbl,
2121 x_settlement_doc_id_tbl => l_settle_doc_id_tbl
2122 );
2123 IF l_return_status = FND_API.g_ret_sts_error THEN
2124 RAISE FND_API.g_exc_error;
2125 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2126 RAISE FND_API.g_exc_unexpected_error;
2127 END IF;
2128 END IF;
2129
2130 IF l_settle_doc_tbl2.COUNT IS NOT NULL AND
2131 l_settle_doc_tbl2.COUNT > 0 THEN
2132 OZF_Settlement_Doc_PVT.Create_Settlement_Doc_Tbl(
2133 p_api_version_number => l_api_version,
2134 p_init_msg_list => FND_API.G_FALSE,
2135 p_commit => FND_API.G_FALSE,
2136 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2137 x_return_status => l_return_status,
2138 x_msg_count => x_msg_count,
2139 x_msg_data => x_msg_data,
2140 p_settlement_doc_tbl => l_settle_doc_tbl2,
2141 x_settlement_doc_id_tbl => l_settle_doc_id_tbl
2142 );
2143 IF l_return_status = FND_API.g_ret_sts_error THEN
2144 RAISE FND_API.g_exc_error;
2145 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2146 RAISE FND_API.g_exc_unexpected_error;
2147 END IF;
2148 END IF;
2149
2150 IF OZF_DEBUG_HIGH_ON THEN
2151 OZF_Utility_PVT.debug_message(l_full_name||': end');
2152 END IF;
2153
2154 EXCEPTION
2155 WHEN FND_API.g_exc_error THEN
2156 ROLLBACK TO Break_Mass_Settlement;
2157 x_return_status := FND_API.g_ret_sts_error;
2158 FND_MSG_PUB.count_and_get (
2159 p_encoded => FND_API.g_false
2160 ,p_count => x_msg_count
2161 ,p_data => x_msg_data
2162 );
2163
2164 WHEN FND_API.g_exc_unexpected_error THEN
2165 ROLLBACK TO Break_Mass_Settlement;
2166 x_return_status := FND_API.g_ret_sts_unexp_error ;
2167 FND_MSG_PUB.count_and_get (
2168 p_encoded => FND_API.g_false
2169 ,p_count => x_msg_count
2170 ,p_data => x_msg_data
2171 );
2172
2173 WHEN OTHERS THEN
2174 ROLLBACK TO Break_Mass_Settlement;
2175 x_return_status := FND_API.g_ret_sts_unexp_error ;
2176 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2177 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2178 END IF;
2179 FND_MSG_PUB.count_and_get(
2180 p_encoded => FND_API.g_false
2181 ,p_count => x_msg_count
2182 ,p_data => x_msg_data
2183 );
2184
2185 END Break_Mass_Settlement;
2186
2187
2188 /*=======================================================================*
2189 | PROCEDURE
2190 | Complete_Mass_Settlement
2191 |
2192 | NOTES
2193 |
2194 | HISTORY
2195 | 20-OCT-2003 mchang Create.
2196 *=======================================================================*/
2197 PROCEDURE Complete_Mass_Settlement(
2198 p_group_claim_id IN NUMBER,
2199 x_claim_tbl OUT NOCOPY OZF_Claim_PVT.claim_tbl_type,
2200 x_return_status OUT NOCOPY VARCHAR2,
2201 x_msg_data OUT NOCOPY VARCHAR2,
2202 x_msg_count OUT NOCOPY NUMBER
2203 )
2204 IS
2205 l_api_version CONSTANT NUMBER := 1.0;
2206 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Mass_Settlement';
2207 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2208 l_return_status VARCHAR2(1);
2209 ---
2210
2211 CURSOR csr_claims_group( cv_group_claim_id IN NUMBER) IS
2212 SELECT claim_id
2213 , claim_number
2214 , object_version_number
2215 FROM ozf_claims_all
2216 WHERE group_claim_id = cv_group_claim_id;
2217
2218 -- Fix for 5376466
2219 CURSOR csr_sysparam_defaults IS
2220 SELECT gl_date_type
2221 FROM ozf_sys_parameters;
2222
2223 i NUMBER := 1;
2224 l_claim_obj_ver_num NUMBER;
2225 l_gl_date_type VARCHAR2(30);
2226
2227 l_claim_pvt_rec OZF_Claim_PVT.claim_rec_type;
2228
2229
2230 BEGIN
2231 -------------------- initialize -----------------------
2232 SAVEPOINT Complete_Mass_Settlement;
2233
2234 IF OZF_DEBUG_HIGH_ON THEN
2235 OZF_Utility_PVT.debug_message(l_full_name||': start');
2236 END IF;
2237
2238 x_return_status := FND_API.G_RET_STS_SUCCESS;
2239
2240 ------------------------ start -------------------------
2241
2242 OPEN csr_sysparam_defaults;
2243 FETCH csr_sysparam_defaults INTO l_gl_date_type;
2244 CLOSE csr_sysparam_defaults;
2245
2246 OPEN csr_claims_group(p_group_claim_id);
2247 LOOP
2248 FETCH csr_claims_group INTO x_claim_tbl(i).claim_id
2249 , x_claim_tbl(i).claim_number
2250 , x_claim_tbl(i).object_version_number;
2251 EXIT WHEN csr_claims_group%NOTFOUND;
2252
2253 IF l_gl_date_type IS NULL THEN
2254 x_claim_tbl(i).gl_date := sysdate;
2255 END IF;
2256
2257 x_claim_tbl(i).payment_method := 'MASS_SETTLEMENT';
2258 x_claim_tbl(i).status_code := 'COMPLETE';
2259 x_claim_tbl(i).user_status_id := OZF_UTILITY_PVT.get_default_user_status(
2260 'OZF_CLAIM_STATUS'
2261 ,'COMPLETE'
2262 );
2263 i := i + 1;
2264 END LOOP;
2265 CLOSE csr_claims_group;
2266
2267
2268
2269 i := x_claim_tbl.FIRST;
2270 IF i IS NOT NULL THEN
2271 LOOP
2272 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2273 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2274 FND_MESSAGE.Set_Token('TEXT', x_claim_tbl(i).claim_number);
2275 FND_MSG_PUB.Add;
2276 END IF;
2277
2278 OZF_Claim_PVT.Update_Claim (
2279 p_api_version => l_api_version
2280 ,p_init_msg_list => FND_API.G_FALSE
2281 ,p_commit => FND_API.G_FALSE
2282 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2283 ,x_return_status => l_return_status
2284 ,x_msg_data => x_msg_data
2285 ,x_msg_count => x_msg_count
2286 ,p_claim => x_claim_tbl(i)
2287 ,p_event => 'UPDATE'
2288 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
2289 ,x_object_version_number => l_claim_obj_ver_num
2290 );
2291 IF l_return_status = FND_API.g_ret_sts_error THEN
2292 RAISE FND_API.g_exc_error;
2293 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2294 RAISE FND_API.g_exc_unexpected_error;
2295 END IF;
2296 x_claim_tbl(i).object_version_number := l_claim_obj_ver_num;
2297 EXIT WHEN i = x_claim_tbl.LAST;
2298 i := x_claim_tbl.NEXT(i);
2299 END LOOP;
2300 END IF;
2301
2302 IF OZF_DEBUG_HIGH_ON THEN
2303 OZF_Utility_PVT.debug_message(l_full_name||': end');
2304 END IF;
2305
2306 EXCEPTION
2307 WHEN FND_API.g_exc_error THEN
2308 ROLLBACK TO Complete_Mass_Settlement;
2309 x_return_status := FND_API.g_ret_sts_error;
2310 FND_MSG_PUB.count_and_get (
2311 p_encoded => FND_API.g_false
2312 ,p_count => x_msg_count
2313 ,p_data => x_msg_data
2314 );
2315
2316 WHEN FND_API.g_exc_unexpected_error THEN
2317 ROLLBACK TO Complete_Mass_Settlement;
2318 x_return_status := FND_API.g_ret_sts_unexp_error ;
2319 FND_MSG_PUB.count_and_get (
2320 p_encoded => FND_API.g_false
2321 ,p_count => x_msg_count
2322 ,p_data => x_msg_data
2323 );
2324
2325 WHEN OTHERS THEN
2326 ROLLBACK TO Complete_Mass_Settlement;
2327 x_return_status := FND_API.g_ret_sts_unexp_error ;
2328 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2329 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2330 END IF;
2331 FND_MSG_PUB.count_and_get(
2332 p_encoded => FND_API.g_false
2333 ,p_count => x_msg_count
2334 ,p_data => x_msg_data
2335 );
2336
2337 END Complete_Mass_Settlement;
2338
2339
2340 /*=======================================================================*
2341 | PROCEDURE
2342 | Approve_Mass_Settlement
2343 |
2344 | NOTES
2345 |
2346 | HISTORY
2347 | 20-OCT-2003 mchang Create.
2348 *=======================================================================*/
2349 PROCEDURE Approve_Mass_Settlement(
2350 p_group_claim_id IN NUMBER,
2351 p_complete_claim_group_tbl IN OZF_Claim_PVT.claim_tbl_type,
2352 x_return_status OUT NOCOPY VARCHAR2,
2353 x_msg_data OUT NOCOPY VARCHAR2,
2354 x_msg_count OUT NOCOPY NUMBER
2355 )
2356 IS
2357 l_api_version CONSTANT NUMBER := 1.0;
2358 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Mass_Settlement';
2359 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2360 l_return_status VARCHAR2(1);
2361 ---
2362 l_complete_claim_group_tbl OZF_Claim_PVT.claim_tbl_type := p_complete_claim_group_tbl;
2363 l_claim_pvt_rec OZF_Claim_PVT.claim_rec_type;
2364 l_claim_obj_ver_num NUMBER;
2365 i BINARY_INTEGER;
2366 l_group_claim_obj_ver NUMBER;
2367 l_orig_status_id NUMBER;
2368 l_new_status_id NUMBER;
2369 l_reject_status_id NUMBER;
2370 l_owner_id NUMBER;
2371 l_appr_req VARCHAR2(1);
2372
2373 CURSOR csr_claim_obj_ver(cv_claim_id IN NUMBER) IS
2374 SELECT object_version_number
2375 FROM ozf_claims_all
2376 WHERE claim_id = cv_claim_id;
2377 /*
2378 CURSOR csr_mass_setl_appr_req IS
2379 SELECT NVL(attr_available_flag, 'N')
2380 FROM ams_custom_setup_attr
2381 WHERE object_attribute = 'APPR'
2382 AND custom_setup_id =( SELECT custom_setup_id
2383 FROM ams_custom_setups_b
2384 WHERE activity_type_code = 'GROUP'
2385 AND object_type = 'CLAM'
2386 AND enabled_flag = 'Y' );
2387 */
2388
2389 -- Added by amitamku for Bug 13897890
2390 CURSOR csr_mass_setl_appr_req IS
2391 SELECT NVL(attr_available_flag, 'N')
2392 FROM ams_custom_setup_attr
2393 WHERE object_attribute = 'APPR'
2394 AND custom_setup_id = ( SELECT acs.custom_setup_id
2395 FROM ams_custom_setups_b acs,
2396 ozf_claim_def_rules_ALL ocd
2397 WHERE acs.activity_type_code = 'GROUP'
2398 AND acs.object_type = 'CLAM'
2399 AND acs.enabled_flag = 'Y'
2400 AND acs.custom_setup_id = ocd.custom_setup_id
2401 AND ocd.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID() );
2402
2403 BEGIN
2404 -------------------- initialize -----------------------
2405 SAVEPOINT Approve_Mass_Settlement;
2406
2407 IF OZF_DEBUG_HIGH_ON THEN
2408 OZF_Utility_PVT.debug_message(l_full_name||': start');
2409 END IF;
2410
2411 x_return_status := FND_API.G_RET_STS_SUCCESS;
2412
2413 ------------------------ start -------------------------
2414
2415 OPEN csr_mass_setl_appr_req;
2416 FETCH csr_mass_setl_appr_req INTO l_appr_req;
2417 CLOSE csr_mass_setl_appr_req;
2418
2419 i := p_complete_claim_group_tbl.FIRST;
2420 IF i IS NOT NULL THEN
2421 LOOP
2422 l_claim_pvt_rec := p_complete_claim_group_tbl(i);
2423 OPEN csr_claim_obj_ver(p_complete_claim_group_tbl(i).claim_id);
2424 FETCH csr_claim_obj_ver INTO l_claim_pvt_rec.object_version_number;
2425 CLOSE csr_claim_obj_ver;
2426
2427 l_claim_pvt_rec.user_status_id := OZF_UTILITY_PVT.get_default_user_status(
2428 'OZF_CLAIM_STATUS'
2429 ,'PENDING_APPROVAL'
2430 );
2431 l_claim_pvt_rec.status_code := 'PENDING_APPROVAL';
2432
2433 OZF_Claim_PVT.Update_Claim (
2434 p_api_version => l_api_version
2435 ,p_init_msg_list => FND_API.G_FALSE
2436 ,p_commit => FND_API.G_FALSE
2437 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2438 ,x_return_status => l_return_status
2439 ,x_msg_data => x_msg_data
2440 ,x_msg_count => x_msg_count
2441 ,p_claim => l_claim_pvt_rec --l_complete_claim_group_tbl(i)
2442 ,p_event => 'UPDATE'
2443 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
2444 ,x_object_version_number => l_claim_obj_ver_num
2445 );
2446 IF l_return_status = FND_API.g_ret_sts_error THEN
2447 RAISE FND_API.g_exc_error;
2448 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2449 RAISE FND_API.g_exc_unexpected_error;
2450 END IF;
2451 EXIT WHEN i = p_complete_claim_group_tbl.LAST;
2452 i := p_complete_claim_group_tbl.NEXT(i);
2453 END LOOP;
2454 END IF;
2455
2456 ------------------------------
2457 -- Mass Settlement Approval --
2458 ------------------------------
2459 IF l_appr_req = 'Y' THEN
2460 OPEN csr_claim_obj_ver(p_group_claim_id);
2461 FETCH csr_claim_obj_ver INTO l_group_claim_obj_ver;
2462 CLOSE csr_claim_obj_ver;
2463
2464 l_orig_status_id := OZF_UTILITY_PVT.get_default_user_status(
2465 'OZF_CLAIM_STATUS'
2466 ,'OPEN'
2467 );
2468
2469 l_new_status_id := OZF_UTILITY_PVT.get_default_user_status(
2470 'OZF_CLAIM_STATUS'
2471 ,'CLOSED'
2472 );
2473
2474 l_reject_status_id := OZF_UTILITY_PVT.get_default_user_status(
2475 'OZF_CLAIM_STATUS'
2476 ,'REJECTED'
2477 );
2478
2479 l_owner_id := OZF_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
2480
2481 --//Added by BKUNJAN Bug#5686706
2482 UPDATE ozf_claims_all
2483 SET payment_status = 'PENDING'
2484 , status_code = 'PENDING_APPROVAL'
2485 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
2486 'OZF_CLAIM_STATUS'
2487 ,'PENDING_APPROVAL'
2488 )
2489 WHERE claim_id = p_group_claim_id;
2490 --End of Addition
2491
2492 AMS_GEN_APPROVAL_PVT.StartProcess(
2493 p_activity_type => 'CLAM'
2494 ,p_activity_id => p_group_claim_id
2495 ,p_approval_type => 'CLAIM'
2496 ,p_object_version_number => l_group_claim_obj_ver
2497 ,p_orig_stat_id => l_orig_status_id
2498 ,p_new_stat_id => l_new_status_id
2499 ,p_reject_stat_id => l_reject_status_id
2500 ,p_requester_userid => l_owner_id
2501 ,p_notes_from_requester => null
2502 ,p_workflowprocess => 'AMSGAPP'
2503 ,p_item_type => 'AMSGAPP'
2504 );
2505 IF l_return_status = FND_API.g_ret_sts_error THEN
2506 RAISE FND_API.g_exc_error;
2507 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2508 RAISE FND_API.g_exc_unexpected_error;
2509 END IF;
2510
2511 --//Commented by BKUNJAN Bug#5686706
2512 /*
2513 UPDATE ozf_claims_all
2514 SET payment_status = 'PENDING'
2515 , status_code = 'PENDING_APPROVAL'
2516 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
2517 'OZF_CLAIM_STATUS'
2518 ,'PENDING_APPROVAL'
2519 )
2520 WHERE claim_id = p_group_claim_id;
2521 */
2522 -- End of Comments
2523
2524 END IF;
2525
2526 IF OZF_DEBUG_HIGH_ON THEN
2527 OZF_Utility_PVT.debug_message(l_full_name||': end');
2528 END IF;
2529
2530
2531 EXCEPTION
2532 WHEN FND_API.g_exc_error THEN
2533 ROLLBACK TO Approve_Mass_Settlement;
2534 x_return_status := FND_API.g_ret_sts_error;
2535 FND_MSG_PUB.count_and_get (
2536 p_encoded => FND_API.g_false
2537 ,p_count => x_msg_count
2538 ,p_data => x_msg_data
2539 );
2540
2541 WHEN FND_API.g_exc_unexpected_error THEN
2542 ROLLBACK TO Approve_Mass_Settlement;
2543 x_return_status := FND_API.g_ret_sts_unexp_error ;
2544 FND_MSG_PUB.count_and_get (
2545 p_encoded => FND_API.g_false
2546 ,p_count => x_msg_count
2547 ,p_data => x_msg_data
2548 );
2549
2550 WHEN OTHERS THEN
2551 ROLLBACK TO Approve_Mass_Settlement;
2552 x_return_status := FND_API.g_ret_sts_unexp_error ;
2553 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2554 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2555 END IF;
2556 FND_MSG_PUB.count_and_get(
2557 p_encoded => FND_API.g_false
2558 ,p_count => x_msg_count
2559 ,p_data => x_msg_data
2560 );
2561
2562 END Approve_Mass_Settlement;
2563
2564 /*=======================================================================*
2565 | PROCEDURE
2566 | Reject_Mass_Payment
2567 |
2568 | NOTES
2569 |
2570 | HISTORY
2571 | 17-FEB-2006 sshivali Create.
2572 *=======================================================================*/
2573 PROCEDURE Reject_Mass_Payment(
2574 p_group_claim_id IN NUMBER,
2575 x_return_status OUT NOCOPY VARCHAR2,
2576 x_msg_data OUT NOCOPY VARCHAR2,
2577 x_msg_count OUT NOCOPY NUMBER
2578 )
2579 IS
2580 l_api_version CONSTANT NUMBER := 1.0;
2581 l_api_name CONSTANT VARCHAR2(30) := 'Reject_Mass_Payment';
2582 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2583 l_return_status VARCHAR2(1);
2584 l_rejected_user_status_id NUMBER;
2585 l_open_user_status_id NUMBER;
2586
2587 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
2588 SELECT user_status_id
2589 FROM ams_user_statuses_vl
2590 WHERE system_status_type = 'OZF_CLAIM_STATUS'
2591 AND default_flag = 'Y'
2592 AND system_status_code = cv_status_code;
2593
2594 BEGIN
2595 IF OZF_DEBUG_HIGH_ON THEN
2596 OZF_Utility_PVT.debug_message(l_full_name||': start');
2597 END IF;
2598
2599 OPEN csr_user_status_id('REJECTED');
2600 FETCH csr_user_status_id INTO l_rejected_user_status_id;
2601 CLOSE csr_user_status_id;
2602
2603 OPEN csr_user_status_id('OPEN');
2604 FETCH csr_user_status_id INTO l_open_user_status_id;
2605 CLOSE csr_user_status_id;
2606
2607 BEGIN
2608 SAVEPOINT Reject_Mass_Payment;
2609
2610 UPDATE ozf_claims_all
2611 SET status_code = 'REJECTED'
2612 , user_status_id = l_rejected_user_status_id
2613 WHERE claim_id = p_group_claim_id;
2614
2615 --bug5460095
2616 UPDATE ozf_claims_all
2617 SET status_code = 'OPEN'
2618 , user_status_id = l_open_user_status_id
2619 , amount_remaining = amount - NVL(amount_adjusted,0)
2620 , acctd_amount_remaining = acctd_amount - NVL(acctd_amount_adjusted,0)
2621 , amount_settled = 0
2622 , acctd_amount_settled = 0
2623 , group_claim_id = null
2624 , payment_method = null
2625 WHERE group_claim_id = p_group_claim_id;
2626
2627 UPDATE ozf_settlement_docs_all
2628 SET payment_status = 'CANCELLED'
2629 WHERE group_claim_id = p_group_claim_id;
2630
2631 EXCEPTION
2632 WHEN OTHERS THEN
2633 ROLLBACK TO Reject_Mass_Payment;
2634 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2635 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
2636 FND_MSG_PUB.add;
2637 END IF;
2638 IF OZF_DEBUG_LOW_ON THEN
2639 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2640 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2641 FND_MSG_PUB.Add;
2642 END IF;
2643 RAISE FND_API.g_exc_unexpected_error;
2644 END;
2645
2646 IF OZF_DEBUG_HIGH_ON THEN
2647 OZF_Utility_PVT.debug_message(l_full_name||': end');
2648 END IF;
2649
2650 EXCEPTION
2651 WHEN FND_API.g_exc_error THEN
2652 x_return_status := FND_API.g_ret_sts_error;
2653 FND_MSG_PUB.count_and_get (
2654 p_encoded => FND_API.g_false
2655 ,p_count => x_msg_count
2656 ,p_data => x_msg_data
2657 );
2658
2659 WHEN FND_API.g_exc_unexpected_error THEN
2660 x_return_status := FND_API.g_ret_sts_unexp_error ;
2661 FND_MSG_PUB.count_and_get (
2662 p_encoded => FND_API.g_false
2663 ,p_count => x_msg_count
2664 ,p_data => x_msg_data
2665 );
2666
2667 WHEN OTHERS THEN
2668 x_return_status := FND_API.g_ret_sts_unexp_error ;
2669 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2670 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2671 END IF;
2672 FND_MSG_PUB.count_and_get(
2673 p_encoded => FND_API.g_false
2674 ,p_count => x_msg_count
2675 ,p_data => x_msg_data
2676 );
2677
2678 END Reject_Mass_Payment;
2679
2680 /*=======================================================================*
2681 | PROCEDURE
2682 | Start_Mass_Payment
2683 |
2684 | NOTES
2685 |
2686 | HISTORY
2687 | 20-OCT-2003 mchang Create.
2688 *=======================================================================*/
2689 PROCEDURE Start_Mass_Payment(
2690 p_group_claim_id IN NUMBER,
2691 x_return_status OUT NOCOPY VARCHAR2,
2692 x_msg_data OUT NOCOPY VARCHAR2,
2693 x_msg_count OUT NOCOPY NUMBER
2694 )
2695 IS
2696 l_api_version CONSTANT NUMBER := 1.0;
2697 l_api_name CONSTANT VARCHAR2(30) := 'Start_Mass_Payment';
2698 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2699 l_return_status VARCHAR2(1);
2700 ---
2701
2702 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
2703 SELECT user_status_id
2704 FROM ams_user_statuses_vl
2705 WHERE system_status_type = 'OZF_CLAIM_STATUS'
2706 AND default_flag = 'Y'
2707 AND system_status_code = cv_status_code;
2708
2709 CURSOR csr_master_claim_lines(cv_group_claim_id IN NUMBER) IS
2710 SELECT payment_reference_id
2711 FROM ozf_claim_lines_all
2712 WHERE claim_id = cv_group_claim_id;
2713
2714 CURSOR csr_settle_docs(cv_claim_id IN NUMBER, cv_group_claim_id IN NUMBER) IS
2715 SELECT settlement_doc_id
2716 , payment_method
2717 , settlement_id
2718 , settlement_number
2719 , settlement_type_id
2720 , settlement_amount
2721 , payment_reference_id
2722 , payment_reference_number
2723 , group_claim_id
2724 , gl_date
2725 , wo_rec_trx_id
2726 FROM ozf_settlement_docs_all
2727 WHERE claim_id = cv_claim_id
2728 AND group_claim_id = cv_group_claim_id
2729 AND payment_status <> 'CANCELLED';
2730
2731 TYPE number_tbl IS TABLE OF NUMBER
2732 INDEX BY BINARY_INTEGER;
2733
2734 l_claim_id_tbl number_tbl;
2735 l_claim_rec OZF_Claim_PVT.claim_rec_type;
2736 l_settlement_doc_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
2737 l_deduction_type VARCHAR2(30);
2738 l_settlement_id NUMBER;
2739 i NUMBER := 1;
2740 j NUMBER := 1;
2741 l_close_user_status_id NUMBER;
2742
2743 --//Bugfix :8202109
2744 l_history_event_description VARCHAR2(2000);
2745 l_history_event VARCHAR2(30);
2746 l_needed_to_create VARCHAR2(1) := 'N';
2747 l_claim_history_id NUMBER := null;
2748
2749 l_status_code VARCHAR2(30) := NULL;
2750 l_user_status_id NUMBER := NULL;
2751 l_claim_rec_hist OZF_CLAIM_PVT.claim_rec_type;
2752
2753 CURSOR cur_current_status (p_claim_id IN NUMBER)IS
2754 SELECT status_code,
2755 user_status_id
2756 FROM ozf_claims_all
2757 WHERE claim_id =p_claim_id;
2758
2759 BEGIN
2760 -------------------- initialize -----------------------
2761 SAVEPOINT Start_Mass_Payment;
2762
2763 IF OZF_DEBUG_HIGH_ON THEN
2764 OZF_Utility_PVT.debug_message(l_full_name||': start');
2765 END IF;
2766
2767 x_return_status := FND_API.G_RET_STS_SUCCESS;
2768
2769 ------------------------ start -------------------------
2770 OPEN csr_master_claim_lines(p_group_claim_id);
2771 LOOP
2772 FETCH csr_master_claim_lines INTO l_claim_id_tbl(i);
2773 EXIT WHEN csr_master_claim_lines%NOTFOUND;
2774 i := i + 1;
2775 END LOOP;
2776 CLOSE csr_master_claim_lines;
2777
2778 i := l_claim_id_tbl.FIRST;
2779 IF i IS NOT NULL THEN
2780 LOOP
2781 OZF_AR_Payment_PVT.Query_Claim(
2782 p_claim_id => l_claim_id_tbl(i)
2783 ,x_claim_rec => l_claim_rec
2784 ,x_return_status => l_return_status
2785 );
2786 IF l_return_status = FND_API.g_ret_sts_error THEN
2787 RAISE FND_API.g_exc_error;
2788 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2789 RAISE FND_API.g_exc_unexpected_error;
2790 END IF;
2791
2792 -- Bug435194: Close claim before making payment
2793 Close_Claim(
2794 p_group_claim_id => p_group_claim_id
2795 ,p_claim_id => l_claim_id_tbl(i)
2796 ,x_return_status => l_return_status
2797 );
2798 IF l_return_status = FND_API.g_ret_sts_error THEN
2799 RAISE FND_API.g_exc_error;
2800 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2801 RAISE FND_API.g_exc_unexpected_error;
2802 END IF;
2803 --//Bugfix :8202109
2804 --//Populate History tables after Closing the Claim
2805 --===================
2806
2807 OPEN cur_current_status(l_claim_id_tbl(i));
2808 FETCH cur_current_status INTO l_status_code,l_user_status_id;
2809 CLOSE cur_current_status;
2810
2811 IF l_status_code ='CLOSED' THEN
2812 l_claim_rec.status_code :=l_status_code;
2813 l_claim_rec.user_status_id :=l_user_status_id;
2814
2815 OZF_claims_history_PVT.Check_Create_History(
2816 p_claim => l_claim_rec,
2817 p_event => 'UPDATE',
2818 x_history_event => l_history_event,
2819 x_history_event_description => l_history_event_description,
2820 x_needed_to_create => l_needed_to_create,
2821 x_return_status => l_return_status
2822 );
2823
2824 IF l_return_status = FND_API.g_ret_sts_error THEN
2825 RAISE FND_API.g_exc_error;
2826 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2827 RAISE FND_API.g_exc_unexpected_error;
2828 END IF;
2829
2830 IF (l_needed_to_create = 'Y') THEN
2831 -- CREATE history
2832 OZF_claims_history_PVT.Create_History(
2833 p_claim_id => l_claim_id_tbl(i),
2834 p_history_event => l_history_event,
2835 p_history_event_description => l_history_event_description,
2836 x_claim_history_id => l_claim_history_id,
2837 x_return_status => l_return_status
2838 );
2839 IF l_return_status = FND_API.g_ret_sts_error THEN
2840 RAISE FND_API.g_exc_error;
2841 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2842 RAISE FND_API.g_exc_unexpected_error;
2843 END IF;
2844 END IF;
2845 END IF;
2846 --//End
2847
2848 IF l_claim_rec.claim_class = 'DEDUCTION' THEN
2849 IF l_claim_rec.source_object_class IS NULL AND
2850 l_claim_rec.source_object_id IS NULL THEN
2851 l_deduction_type := 'RECEIPT_DED';
2852 ELSE
2853 l_deduction_type := 'SOURCE_DED';
2854 END IF;
2855 ELSIF l_claim_rec.claim_class = 'OVERPAYMENT' THEN
2856 IF l_claim_rec.source_object_class IS NULL AND
2857 l_claim_rec.source_object_id IS NULL THEN
2858 l_deduction_type := 'RECEIPT_OPM';
2859 ELSE
2860 l_deduction_type := 'SOURCE_OPM';
2861 END IF;
2862 END IF;
2863
2864 j := 1;
2865 OPEN csr_settle_docs(l_claim_id_tbl(i), p_group_claim_id);
2866 LOOP
2867 FETCH csr_settle_docs INTO l_settlement_doc_tbl(j).settlement_doc_id
2868 , l_settlement_doc_tbl(j).payment_method
2869 , l_settlement_doc_tbl(j).settlement_id
2870 , l_settlement_doc_tbl(j).settlement_number
2871 , l_settlement_doc_tbl(j).settlement_type_id
2872 , l_settlement_doc_tbl(j).settlement_amount
2873 , l_settlement_doc_tbl(j).payment_reference_id
2874 , l_settlement_doc_tbl(j).payment_reference_number
2875 , l_settlement_doc_tbl(j).group_claim_id
2876 , l_settlement_doc_tbl(j).gl_date
2877 , l_settlement_doc_tbl(j).wo_rec_trx_id;
2878 EXIT WHEN csr_settle_docs%NOTFOUND;
2879 j := j + 1;
2880 END LOOP;
2881 CLOSE csr_settle_docs;
2882
2883
2884 j := l_settlement_doc_tbl.FIRST;
2885 IF j IS NOT NULL THEN
2886 LOOP
2887 IF OZF_DEBUG_HIGH_ON THEN
2888 OZF_Utility_PVT.debug_message('Payment -- ('||j||')');
2889 OZF_Utility_PVT.debug_message('Payment -- payment_method = '||l_settlement_doc_tbl(j).payment_method);
2890 OZF_Utility_PVT.debug_message('Payment -- settlement_doc_id = '||l_settlement_doc_tbl(j).settlement_doc_id);
2891 OZF_Utility_PVT.debug_message('Payment -- settlement_id = '||l_settlement_doc_tbl(j).settlement_id);
2892 OZF_Utility_PVT.debug_message('Payment -- settlement_number = '||l_settlement_doc_tbl(j).settlement_number);
2893 OZF_Utility_PVT.debug_message('Payment -- settlement_amount = '||l_settlement_doc_tbl(j).settlement_amount);
2894 OZF_Utility_PVT.debug_message('Payment -- payment_reference_id = '||l_settlement_doc_tbl(j).payment_reference_id);
2895 OZF_Utility_PVT.debug_message('Payment -- payment_reference_number = '||l_settlement_doc_tbl(j).payment_reference_number);
2896 END IF;
2897
2898 BEGIN
2899 UPDATE ozf_settlement_docs_all
2900 SET payment_status = 'PENDING_PAID'
2901 WHERE settlement_doc_id = l_settlement_doc_tbl(j).settlement_doc_id;
2902 EXCEPTION
2903 WHEN OTHERS THEN
2904 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2905 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_ERR');
2906 FND_MSG_PUB.add;
2907 END IF;
2908 IF OZF_DEBUG_LOW_ON THEN
2909 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2910 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2911 FND_MSG_PUB.Add;
2912 END IF;
2913 RAISE FND_API.g_exc_unexpected_error;
2914 END;
2915
2916 IF l_settlement_doc_tbl(j).payment_method = 'CREDIT_MEMO' THEN
2917 Pay_by_Credit_Memo(
2918 p_claim_rec => l_claim_rec
2919 ,p_deduction_type => l_deduction_type
2920 ,p_payment_reference_id => l_settlement_doc_tbl(j).settlement_id
2921 ,p_credit_memo_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2922 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2923 ,x_return_status => l_return_status
2924 ,x_msg_data => x_msg_data
2925 ,x_msg_count => x_msg_count
2926 );
2927 IF l_return_status = FND_API.g_ret_sts_error THEN
2928 RAISE FND_API.g_exc_error;
2929 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2930 RAISE FND_API.g_exc_unexpected_error;
2931 END IF;
2932
2933 ELSIF l_settlement_doc_tbl(j).payment_method = 'DEBIT_MEMO' THEN
2934 Pay_by_Debit_Memo(
2935 p_claim_rec => l_claim_rec
2936 ,p_deduction_type => l_deduction_type
2937 ,p_payment_reference_id => l_settlement_doc_tbl(j).settlement_id
2938 ,p_debit_memo_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2939 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2940 ,x_return_status => l_return_status
2941 ,x_msg_data => x_msg_data
2942 ,x_msg_count => x_msg_count
2943 );
2944 IF l_return_status = FND_API.g_ret_sts_error THEN
2945 RAISE FND_API.g_exc_error;
2946 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2947 RAISE FND_API.g_exc_unexpected_error;
2948 END IF;
2949
2950 ELSIF l_settlement_doc_tbl(j).payment_method = 'CHARGEBACK' THEN
2951 Pay_by_Chargeback(
2952 p_claim_rec => l_claim_rec
2953 ,p_deduction_type => l_deduction_type
2954 ,p_chargeback_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2955 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2956 ,p_gl_date => l_settlement_doc_tbl(j).gl_date
2957 ,x_return_status => l_return_status
2958 ,x_msg_data => x_msg_data
2959 ,x_msg_count => x_msg_count
2960 );
2961 IF l_return_status = FND_API.g_ret_sts_error THEN
2962 RAISE FND_API.g_exc_error;
2963 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2964 RAISE FND_API.g_exc_unexpected_error;
2965 END IF;
2966
2967 ELSIF l_settlement_doc_tbl(j).payment_method = 'WRITE_OFF' THEN
2968 Pay_by_Write_Off(
2969 p_claim_rec => l_claim_rec
2970 ,p_deduction_type => l_deduction_type
2971 ,p_write_off_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2972 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2973 ,p_gl_date => l_settlement_doc_tbl(j).gl_date
2974 ,p_wo_rec_trx_id => l_settlement_doc_tbl(j).wo_rec_trx_id
2975 ,x_return_status => l_return_status
2976 ,x_msg_data => x_msg_data
2977 ,x_msg_count => x_msg_count
2978 );
2979 IF l_return_status = FND_API.g_ret_sts_error THEN
2980 RAISE FND_API.g_exc_error;
2981 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2982 RAISE FND_API.g_exc_unexpected_error;
2983 END IF;
2984
2985 ELSIF l_settlement_doc_tbl(j).payment_method = 'ON_ACCT_CREDIT' THEN
2986 Pay_by_On_Account_Credit(
2987 p_claim_rec => l_claim_rec
2988 ,p_deduction_type => l_deduction_type
2989 ,p_credit_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2990 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2991 ,x_return_status => l_return_status
2992 ,x_msg_data => x_msg_data
2993 ,x_msg_count => x_msg_count
2994 );
2995 IF l_return_status = FND_API.g_ret_sts_error THEN
2996 RAISE FND_API.g_exc_error;
2997 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2998 RAISE FND_API.g_exc_unexpected_error;
2999 END IF;
3000
3001 ELSIF l_settlement_doc_tbl(j).payment_method IN ('DEDUCTION', 'OVERPAYMENT') THEN
3002 Pay_by_Open_Receipt(
3003 p_claim_rec => l_claim_rec
3004 ,p_deduction_type => l_deduction_type
3005 ,p_open_receipt_id => l_settlement_doc_tbl(j).settlement_id
3006 ,p_payment_claim_id => l_settlement_doc_tbl(j).payment_reference_id
3007 ,p_amount_applied => l_settlement_doc_tbl(j).settlement_amount
3008 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
3009 ,x_return_status => l_return_status
3010 ,x_msg_data => x_msg_data
3011 ,x_msg_count => x_msg_count
3012 );
3013 IF l_return_status = FND_API.g_ret_sts_error THEN
3014 RAISE FND_API.g_exc_error;
3015 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3016 RAISE FND_API.g_exc_unexpected_error;
3017 END IF;
3018 --//Bugfix: 8202109
3019 OPEN cur_current_status(l_settlement_doc_tbl(j).payment_reference_id);
3020 FETCH cur_current_status INTO l_status_code,l_user_status_id;
3021 CLOSE cur_current_status;
3022
3023 -- Bug4124810: Close payment claim
3024 Close_Claim(
3025 p_group_claim_id => p_group_claim_id
3026 ,p_claim_id => l_settlement_doc_tbl(j).payment_reference_id
3027 ,x_return_status => l_return_status
3028 );
3029 IF l_return_status = FND_API.g_ret_sts_error THEN
3030 RAISE FND_API.g_exc_error;
3031 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3032 RAISE FND_API.g_exc_unexpected_error;
3033 END IF;
3034
3035 --//Update Claim History
3036 IF l_status_code <>'CLOSED' THEN
3037 l_claim_rec_hist.claim_id :=l_settlement_doc_tbl(j).payment_reference_id;
3038 l_claim_rec_hist.status_code :='CLOSED';
3039 l_claim_rec_hist.user_status_id :=OZF_UTILITY_PVT.get_default_user_status('OZF_CLAIM_STATUS','CLOSED');
3040
3041 OZF_claims_history_PVT.Check_Create_History(
3042 p_claim => l_claim_rec_hist,
3043 p_event => 'UPDATE',
3044 x_history_event => l_history_event,
3045 x_history_event_description => l_history_event_description,
3046 x_needed_to_create => l_needed_to_create,
3047 x_return_status => l_return_status
3048 );
3049
3050 IF l_return_status = FND_API.g_ret_sts_error THEN
3051 RAISE FND_API.g_exc_error;
3052 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3053 RAISE FND_API.g_exc_unexpected_error;
3054 END IF;
3055
3056 IF (l_needed_to_create = 'Y') THEN
3057 -- CREATE history
3058 OZF_claims_history_PVT.Create_History(
3059 p_claim_id => l_settlement_doc_tbl(j).payment_reference_id,
3060 p_history_event => l_history_event,
3061 p_history_event_description => l_history_event_description,
3062 x_claim_history_id => l_claim_history_id,
3063 x_return_status => l_return_status
3064 );
3065 IF l_return_status = FND_API.g_ret_sts_error THEN
3066 RAISE FND_API.g_exc_error;
3067 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3068 RAISE FND_API.g_exc_unexpected_error;
3069 END IF;
3070 END IF;
3071 END IF;
3072 --//End
3073
3074 END IF;
3075 EXIT WHEN j = l_settlement_doc_tbl.LAST;
3076 j := l_settlement_doc_tbl.NEXT(j);
3077 END LOOP;
3078 END IF;
3079 EXIT WHEN i = l_claim_id_tbl.LAST;
3080 i := l_claim_id_tbl.NEXT(i);
3081 l_settlement_doc_tbl.DELETE;
3082 END LOOP;
3083 END IF;
3084
3085 -----------------------
3086 -- Close Claim Group --
3087 -----------------------
3088 OPEN csr_user_status_id('CLOSED');
3089 FETCH csr_user_status_id INTO l_close_user_status_id;
3090 CLOSE csr_user_status_id;
3091
3092 BEGIN
3093 UPDATE ozf_claims_all
3094 SET payment_status = 'PAID'
3095 , status_code = 'CLOSED'
3096 , user_status_id = l_close_user_status_id
3097 WHERE claim_id = p_group_claim_id;
3098 EXCEPTION
3099 WHEN OTHERS THEN
3100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3101 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
3102 FND_MSG_PUB.add;
3103 END IF;
3104 IF OZF_DEBUG_LOW_ON THEN
3105 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3106 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3107 FND_MSG_PUB.Add;
3108 END IF;
3109 RAISE FND_API.g_exc_unexpected_error;
3110 END;
3111
3112 IF OZF_DEBUG_HIGH_ON THEN
3113 OZF_Utility_PVT.debug_message(l_full_name||': end');
3114 END IF;
3115
3116 EXCEPTION
3117 WHEN FND_API.g_exc_error THEN
3118 ROLLBACK TO Start_Mass_Payment;
3119 x_return_status := FND_API.g_ret_sts_error;
3120 FND_MSG_PUB.count_and_get (
3121 p_encoded => FND_API.g_false
3122 ,p_count => x_msg_count
3123 ,p_data => x_msg_data
3124 );
3125
3126 WHEN FND_API.g_exc_unexpected_error THEN
3127 ROLLBACK TO Start_Mass_Payment;
3128 x_return_status := FND_API.g_ret_sts_unexp_error ;
3129 FND_MSG_PUB.count_and_get (
3130 p_encoded => FND_API.g_false
3131 ,p_count => x_msg_count
3132 ,p_data => x_msg_data
3133 );
3134
3135 WHEN OTHERS THEN
3136 ROLLBACK TO Start_Mass_Payment;
3137 x_return_status := FND_API.g_ret_sts_unexp_error ;
3138 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3139 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3140 END IF;
3141 FND_MSG_PUB.count_and_get(
3142 p_encoded => FND_API.g_false
3143 ,p_count => x_msg_count
3144 ,p_data => x_msg_data
3145 );
3146
3147 END Start_Mass_Payment;
3148
3149
3150
3151 /*=======================================================================
3152 | PROCEDURE
3153 | Settle_Mass_Settlement
3154 |
3155 | NOTES
3156 |
3157 | HISTORY
3158 | 20-OCT-2003 mchang Create.
3159 *=======================================================================*/
3160 PROCEDURE Settle_Mass_Settlement(
3161 p_api_version IN NUMBER
3162 ,p_init_msg_list IN VARCHAR2
3163 ,p_commit IN VARCHAR2
3164 ,p_validation_level IN NUMBER
3165
3166 ,x_return_status OUT NOCOPY VARCHAR2
3167 ,x_msg_data OUT NOCOPY VARCHAR2
3168 ,x_msg_count OUT NOCOPY NUMBER
3169
3170 ,p_group_claim_rec IN group_claim_rec
3171 ,p_open_claim_tbl IN open_claim_tbl
3172 ,p_open_transaction_tbl IN open_transaction_tbl
3173 ,p_payment_method_tbl IN claim_payment_method_tbl
3174
3175 ,x_claim_group_id OUT NOCOPY NUMBER
3176 ,x_claim_group_number OUT NOCOPY VARCHAR2
3177 --,x_split_claim_tbl OUT NOCOPY open_claim_tbl
3178 )
3179 IS
3180 l_api_version CONSTANT NUMBER := 1.0;
3181 l_api_name CONSTANT VARCHAR2(30) := 'Settle_Mass_Settlement';
3182 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3183 l_return_status VARCHAR2(1);
3184 ---
3185
3186 CURSOR csr_claim_amount_rem(cv_claim_id IN NUMBER) IS
3187 SELECT amount_remaining
3188 , object_version_number
3189 FROM ozf_claims_all
3190 WHERE claim_id = cv_claim_id;
3191
3192 CURSOR csr_get_set_of_books IS
3193 SELECT set_of_books_id
3194 FROM ozf_sys_parameters;
3195 /*
3196 CURSOR csr_mass_setl_appr_req IS
3197 SELECT NVL(attr_available_flag, 'N')
3198 FROM ams_custom_setup_attr
3199 WHERE object_attribute = 'APPR'
3200 AND custom_setup_id = ( SELECT custom_setup_id
3201 FROM ams_custom_setups_b
3202 WHERE activity_type_code = 'GROUP'
3203 AND object_type = 'CLAM'
3204 AND enabled_flag = 'Y' );
3205
3206 */
3207 -- Added by amitamku for Bug 13897890
3208 CURSOR csr_mass_setl_appr_req IS
3209 SELECT NVL(attr_available_flag, 'N')
3210 FROM ams_custom_setup_attr
3211 WHERE object_attribute = 'APPR'
3212 AND custom_setup_id = ( SELECT acs.custom_setup_id
3213 FROM ams_custom_setups_b acs,
3214 ozf_claim_def_rules_ALL ocd
3215 WHERE acs.activity_type_code = 'GROUP'
3216 AND acs.object_type = 'CLAM'
3217 AND acs.enabled_flag = 'Y'
3218 AND acs.custom_setup_id = ocd.custom_setup_id
3219 AND ocd.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID() );
3220
3221
3222 CURSOR csr_get_gl_date_type(cv_set_of_books_id IN NUMBER) IS
3223 SELECT gl_date_type
3224 FROM ozf_sys_parameters
3225 WHERE set_of_books_id = cv_set_of_books_id;
3226
3227 --//Bugfix : 7661712
3228 CURSOR p_acctd_claim_amts(p_claim_id IN NUMBER) IS
3229 SELECT acctd_amount,
3230 acctd_amount_adjusted,
3231 amount,
3232 amount_adjusted
3233 FROM ozf_claims_all
3234 WHERE claim_id =p_claim_id;
3235
3236 l_ded_claim_tbl OZF_Claim_PVT.claim_tbl_type;
3237 l_opm_claim_tbl OZF_Claim_PVT.claim_tbl_type;
3238 l_settle_doc_tbl OZF_Settlement_Doc_Pvt.settlement_doc_tbl_type;
3239 l_split_claim_tbl OZF_Split_Claim_PVT.child_claim_tbl_type;
3240 l_idx_ded NUMBER := 1;
3241 l_idx_opm NUMBER := 1;
3242
3243 l_group_claim_rec OZF_Claim_PVT.claim_rec_type;
3244 l_group_claim_id NUMBER;
3245 l_group_claim_line_tbl OZF_Claim_Line_PVT.claim_line_tbl_type;
3246 l_group_settle_doc_tbl OZF_Settlement_Doc_Pvt.settlement_doc_tbl_type;
3247 l_group_settle_doc_id_tbl JTF_NUMBER_TABLE;
3248 l_idx_claim_line NUMBER := 1;
3249 l_idx_setl_doc NUMBER := 1;
3250
3251 l_open_claim_tbl open_claim_tbl := p_open_claim_tbl;
3252
3253 l_open_claim_amt NUMBER := 0;
3254 l_open_trx_amt NUMBER := 0;
3255 l_pay_method_amt NUMBER := 0;
3256
3257 l_group_claim_amt NUMBER := 0;
3258 l_group_trx_amt NUMBER := 0;
3259 l_group_settle_amt NUMBER := 0;
3260 l_group_rem_amt NUMBER := 0;
3261
3262 l_settlement_type VARCHAR2(30);
3263 l_claim_amount_rem NUMBER;
3264 l_obj_ver_num NUMBER;
3265
3266 l_complete_claim_group_tbl OZF_Claim_PVT.claim_tbl_type;
3267 i BINARY_INTEGER;
3268 l_appr_req VARCHAR2(1);
3269
3270
3271 l_overpay_amt NUMBER := 0;
3272 l_total_settle_amt NUMBER := 0;
3273 l_gl_date_type VARCHAR2(30);
3274
3275 --bug4768031
3276 l_deduction_count NUMBER := 0;
3277 l_overpayment_count NUMBER := 0;
3278
3279 --//Bugfix : 7439145
3280 l_act_amt NUMBER :=0;
3281 l_act_amt_settled NUMBER :=0;
3282 l_act_amt_adjusted NUMBER :=0;
3283
3284 --//Bugfix : 7661712
3285 l_amt NUMBER :=0;
3286 l_amt_settled NUMBER :=0;
3287 l_amt_adjusted NUMBER :=0;
3288
3289 /*
3290 l_gl_date DATE;
3291 l_wo_rec_trx_id NUMBER;
3292 */
3293 l_comments VARCHAR2(2000); --hbandi added for the BUG #9961068
3294
3295 BEGIN
3296 -------------------- initialize -----------------------
3297 SAVEPOINT Settle_Mass_Settlement;
3298
3299 IF OZF_DEBUG_HIGH_ON THEN
3300 OZF_Utility_PVT.debug_message(l_full_name||': start');
3301 END IF;
3302
3303 -- Standard call to check for call compatibility.
3304 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3305 p_api_version,
3306 l_api_name,
3307 G_PKG_NAME ) THEN
3308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3309 END IF;
3310
3311 -- Initialize message list if p_init_msg_list is set to TRUE.
3312 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3313 FND_MSG_PUB.initialize;
3314 END IF;
3315
3316 -- Initialize API return status to SUCCESS
3317 x_return_status := FND_API.G_RET_STS_SUCCESS;
3318
3319 ------------------------ start -------------------------
3320 -------------------
3321 -- 1. Validation --
3322 -------------------
3323 IF OZF_DEBUG_HIGH_ON THEN
3324 OZF_Utility_PVT.debug_message('1. Validation');
3325 END IF;
3326
3327 i := p_open_claim_tbl.FIRST;
3328 IF i IS NOT NULL THEN
3329 LOOP
3330 l_open_claim_amt := l_open_claim_amt + p_open_claim_tbl(i).amount_settled;
3331
3332 IF OZF_DEBUG_HIGH_ON THEN
3333 OZF_Utility_PVT.debug_message('open_claim_amt('||i||') = '||p_open_claim_tbl(i).amount_settled);
3334 END IF;
3335 IF p_open_claim_tbl(i).claim_class = l_settlement_type THEN
3336 l_overpay_amt := l_overpay_amt + l_open_claim_tbl(i).amount_settled * -1 ;
3337 END IF;
3338
3339 l_comments := p_open_claim_tbl(i).comments; --hbandi added for the BUG #9961068
3340 --bug4768031
3341 IF p_open_claim_tbl(i).claim_class = 'DEDUCTION' THEN
3342 l_deduction_count := l_deduction_count + 1;
3343 ELSIF p_open_claim_tbl(i).claim_class = 'OVERPAYMENT' THEN
3344 l_overpayment_count := l_overpayment_count + 1;
3345 END IF;
3346
3347 EXIT WHEN i = p_open_claim_tbl.LAST;
3348 i := p_open_claim_tbl.NEXT(i);
3349 END LOOP;
3350 END IF;
3351
3352 IF p_open_claim_tbl.LAST > 1 AND
3353 NOT ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed THEN
3354 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3355 FND_MESSAGE.Set_Name('OZF','OZF_SETL_MUL_CLA_NS');
3356 FND_MSG_PUB.Add;
3357 END IF;
3358 RAISE FND_API.g_exc_error;
3359 END IF;
3360
3361 IF SIGN(l_open_claim_amt) = 1 THEN
3362 l_settlement_type := 'DEDUCTION';
3363 ELSIF SIGN(l_open_claim_amt) = -1 THEN
3364 l_settlement_type := 'OVERPAYMENT';
3365 ELSIF SIGN(l_open_claim_amt) = 0 THEN
3366 l_settlement_type := 'NETTING';
3367 END IF;
3368
3369 i := p_open_transaction_tbl.FIRST;
3370 IF i IS NOT NULL THEN
3371 LOOP
3372 IF l_settlement_type = 'DEDUCTION' AND
3373 p_open_transaction_tbl(i).trx_class <> 'CM' THEN
3374 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3375 FND_MESSAGE.Set_Name('OZF','OZF_SETL_DED_POCM');
3376 FND_MSG_PUB.Add;
3377 END IF;
3378 RAISE FND_API.g_exc_error;
3379 ELSIF l_settlement_type = 'OVERPAYMENT' AND
3380 p_open_transaction_tbl(i).trx_class NOT IN ('INVOICE', 'INV', 'DM', 'CB') THEN
3381 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3382 FND_MESSAGE.Set_Name('OZF','OZF_SETL_OPM_PODM');
3383 FND_MSG_PUB.Add;
3384 END IF;
3385 RAISE FND_API.g_exc_error;
3386 END IF;
3387
3388 l_open_trx_amt := l_open_trx_amt + p_open_transaction_tbl(i).amount_settled;
3389 EXIT WHEN i = p_open_transaction_tbl.LAST;
3390 IF OZF_DEBUG_HIGH_ON THEN
3391 OZF_Utility_PVT.debug_message('open_trx_amt('||i||') = '||p_open_transaction_tbl(i).amount_settled);
3392 END IF;
3393 i := p_open_transaction_tbl.NEXT(i);
3394 END LOOP;
3395 END IF;
3396
3397 IF ABS(l_open_claim_amt) < ABS(l_open_trx_amt) THEN
3398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3399 FND_MESSAGE.Set_Name('OZF','OZF_SETL_TRX_AMT_ERR');
3400 FND_MSG_PUB.Add;
3401 END IF;
3402 RAISE FND_API.g_exc_error;
3403 END IF;
3404
3405 i := p_payment_method_tbl.FIRST;
3406 IF i IS NOT NULL THEN
3407 LOOP
3408 IF l_settlement_type = 'DEDUCTION' AND
3409 p_payment_method_tbl(i).payment_method NOT IN ('WRITE_OFF', 'CHARGEBACK') THEN
3410 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3411 FND_MESSAGE.Set_Name('OZF','OZF_SETL_DED_ERR');
3412 FND_MSG_PUB.Add;
3413 END IF;
3414 RAISE FND_API.g_exc_error;
3415 ELSIF l_settlement_type = 'OVERPAYMENT' AND
3416 p_payment_method_tbl(i).payment_method NOT IN ('WRITE_OFF', 'ON_ACCT_CREDIT') THEN
3417 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3418 FND_MESSAGE.Set_Name('OZF','OZF_SETL_OPM_ERR');
3419 FND_MSG_PUB.Add;
3420 END IF;
3421 RAISE FND_API.g_exc_error;
3422 END IF;
3423
3424 l_pay_method_amt := l_pay_method_amt + (p_payment_method_tbl(i).amount_settled * -1);
3425 EXIT WHEN i = p_payment_method_tbl.LAST;
3426 IF OZF_DEBUG_HIGH_ON THEN
3427 OZF_Utility_PVT.debug_message('pay_method_amt('||i||') = '||p_payment_method_tbl(i).amount_settled);
3428 END IF;
3429 /*
3430 l_gl_date := p_payment_method_tbl(i).gl_date;
3431 l_wo_rec_trx_id := p_payment_method_tbl(i).wo_rec_trx_id;
3432 */
3433 i := p_payment_method_tbl.NEXT(i);
3434 END LOOP;
3435 END IF;
3436
3437
3438
3439 IF ABS(l_open_claim_amt) < (ABS(l_open_trx_amt) + ABS(l_pay_method_amt)) THEN
3440 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3441 FND_MESSAGE.Set_Name('OZF','OZF_SETL_OPN_CLA_AMT_ERR');
3442 FND_MSG_PUB.Add;
3443 END IF;
3444 RAISE FND_API.g_exc_error;
3445 END IF;
3446
3447 --bug4768031
3448 IF (ABS(l_open_trx_amt) = 0 AND ABS(l_pay_method_amt) = 0 AND
3449 ((l_overpayment_count = 0 AND l_deduction_count > 0) OR
3450 (l_overpayment_count > 0 AND l_deduction_count = 0))) THEN
3451 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3452 FND_MESSAGE.Set_Name('OZF','OZF_SETL_DED_OPM_ERR');
3453 FND_MSG_PUB.Add;
3454 END IF;
3455 RAISE FND_API.g_exc_error;
3456 END IF;
3457
3458 /* Bug4079177: Claims should be split if the settlement amount is less then
3459 the settlement amount on the group. If the group is a deduction group,
3460 then the deductions should be split. Else the overpayments should be
3461 split.*/
3462
3463 i := l_open_claim_tbl.FIRST;
3464 IF i IS NOT NULL AND l_settlement_type <> 'NETTING' THEN
3465 LOOP
3466 IF l_open_claim_tbl(i).claim_class <> l_settlement_type THEN
3467 l_overpay_amt := l_overpay_amt - ABS(l_open_claim_tbl(i).amount_settled) ;
3468 END IF;
3469 EXIT WHEN i = l_open_claim_tbl.LAST;
3470 i := l_open_claim_tbl.NEXT(i);
3471 END LOOP;
3472 END IF;
3473
3474 l_total_settle_amt := ABS(l_open_trx_amt) + ABS(l_pay_method_amt) + ABS(l_overpay_amt);
3475 i := l_open_claim_tbl.FIRST;
3476 IF i IS NOT NULL THEN
3477 LOOP
3478 /*hbandi added for the BUG #9961068(+)*/
3479 IF l_comments <> NULL THEN
3480 l_open_claim_tbl(i).comments := l_comments;
3481 END IF;
3482 /*hbandi added for the BUG #9961068(-)*/
3483
3484 IF l_open_claim_tbl(i).claim_class = l_settlement_type THEN
3485 IF ABS(l_open_claim_tbl(i).amount_settled) < l_total_settle_amt THEN
3486 l_total_settle_amt := l_total_settle_amt - ABS(l_open_claim_tbl(i).amount_settled) ;
3487 ELSIF l_total_settle_amt = 0 THEN
3488 l_open_claim_amt := l_open_claim_amt - l_open_claim_tbl(i).amount_settled;
3489 l_open_claim_tbl.DELETE(i);
3490 ELSIF ABS(l_open_claim_tbl(i).amount_settled) > l_total_settle_amt THEN
3491 IF l_open_claim_tbl(i).claim_class = 'DEDUCTION' THEN
3492 l_open_claim_tbl(i).amount_settled := l_total_settle_amt ;
3493 ELSE
3494 l_open_claim_tbl(i).amount_settled := l_total_settle_amt * -1 ;
3495 END IF;
3496 l_total_settle_amt := 0;
3497 END IF;
3498 END IF;
3499 EXIT WHEN i = p_open_claim_tbl.LAST;
3500 i := l_open_claim_tbl.NEXT(i);
3501 END LOOP;
3502 END IF;
3503
3504 IF OZF_DEBUG_HIGH_ON THEN
3505 OZF_Utility_PVT.debug_message('total open_claim_amt = '||l_open_claim_amt);
3506 OZF_Utility_PVT.debug_message('total open_trx_amt = '||l_open_trx_amt);
3507 OZF_Utility_PVT.debug_message('total pay_method_amt = '||l_pay_method_amt);
3508 OZF_Utility_PVT.debug_message('total claim_pay_amt = '||l_overpay_amt);
3509 END IF;
3510
3511
3512 ----------------------------
3513 -- 2. Create Master Claim --
3514 ----------------------------
3515 IF OZF_DEBUG_HIGH_ON THEN
3516 OZF_Utility_PVT.debug_message('2. Create Master Claim');
3517 END IF;
3518
3519 -- Get Claim Type/Reason from Claim Defaulting Rule
3520 l_group_claim_rec.claim_class := 'GROUP';
3521 l_group_claim_rec.cust_account_id := p_group_claim_rec.cust_account_id;
3522 l_group_claim_rec.claim_type_id := p_group_claim_rec.claim_type_id;
3523 l_group_claim_rec.reason_code_id := p_group_claim_rec.reason_code_id;
3524 l_group_claim_rec.amount := l_open_claim_amt; --l_open_trx_amt + l_pay_method_amt;
3525 l_group_claim_rec.currency_code := p_group_claim_rec.currency_code;
3526 l_group_claim_rec.cust_billto_acct_site_id := p_group_claim_rec.bill_to_site_id;
3527 l_group_claim_rec.org_id := p_group_claim_rec.org_id;
3528 l_group_claim_rec.user_status_id := 2006;
3529
3530 l_group_claim_rec.comments := p_group_claim_rec.comments; --hbandi added for the BUG #9961068
3531
3532 IF OZF_DEBUG_HIGH_ON THEN
3533 OZF_Utility_PVT.debug_message('group claim amount = '||l_group_claim_rec.amount);
3534 END IF;
3535
3536 l_group_claim_rec.claim_date := SYSDATE;
3537 OPEN csr_get_set_of_books;
3538 FETCH csr_get_set_of_books INTO l_group_claim_rec.set_of_books_id;
3539 CLOSE csr_get_set_of_books;
3540 --l_group_claim_rec.currency_code := 'USD';
3541 l_group_claim_rec.payment_method := 'MASS_SETTLEMENT';
3542
3543 OZF_Claim_PVT.Create_Claim (
3544 p_api_version => l_api_version
3545 ,p_init_msg_list => FND_API.G_FALSE
3546 ,p_commit => FND_API.G_FALSE
3547 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3548 ,x_return_status => l_return_status
3549 ,x_msg_data => x_msg_data
3550 ,x_msg_count => x_msg_count
3551 ,p_claim => l_group_claim_rec
3552 ,x_claim_id => l_group_claim_id
3553 );
3554 IF OZF_DEBUG_HIGH_ON THEN
3555 OZF_Utility_PVT.debug_message('group claim id = '||l_group_claim_id);
3556 END IF;
3557
3558 IF l_return_status = FND_API.g_ret_sts_error THEN
3559 RAISE FND_API.g_exc_error;
3560 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3561 RAISE FND_API.g_exc_unexpected_error;
3562 END IF;
3563
3564
3565 ---------------------
3566 -- 3. Group Claims --
3567 ---------------------
3568 IF OZF_DEBUG_HIGH_ON THEN
3569 OZF_Utility_PVT.debug_message('3. Group Claims');
3570 END IF;
3571
3572 i := l_open_claim_tbl.FIRST;
3573 IF i IS NOT NULL THEN
3574 LOOP
3575 IF OZF_DEBUG_HIGH_ON THEN
3576 OZF_Utility_PVT.debug_message('Update Claim Id:'||l_open_claim_tbl(i).claim_id);
3577 END IF;
3578
3579 BEGIN
3580 UPDATE ozf_claims_all
3581 SET group_claim_id = l_group_claim_id
3582 , comments = l_comments
3583 --, gl_date = l_gl_date
3584 --, wo_rec_trx_id = l_wo_rec_trx_id
3585 WHERE claim_id = l_open_claim_tbl(i).claim_id;
3586 EXCEPTION
3587 WHEN OTHERS THEN
3588 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3589 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
3590 FND_MSG_PUB.add;
3591 END IF;
3592 IF OZF_DEBUG_LOW_ON THEN
3593 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3594 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3595 FND_MSG_PUB.Add;
3596 END IF;
3597 RAISE FND_API.g_exc_unexpected_error;
3598 END;
3599
3600 OPEN csr_claim_amount_rem(l_open_claim_tbl(i).claim_id);
3601 FETCH csr_claim_amount_rem INTO l_claim_amount_rem
3602 , l_obj_ver_num;
3603 CLOSE csr_claim_amount_rem;
3604
3605 l_group_claim_amt := l_group_claim_amt + l_claim_amount_rem;
3606
3607 -- -------------
3608 -- Split Claims
3609 -- -------------
3610 --ozf_utility_pvt.debug_message(l_open_claim_tbl(i).amount_settled||'amount settled');
3611 IF ABS(l_open_claim_tbl(i).amount_settled) < ABS(l_claim_amount_rem) THEN
3612 --l_split_claim_tbl(1).claim_type_id := p_claim_rec.claim_type_id;
3613 --l_split_claim_tbl(1).reason_code_id := p_claim_rec.reason_code_id;
3614 l_split_claim_tbl(1).amount := l_claim_amount_rem
3615 - l_open_claim_tbl(i).amount_settled;
3616 l_split_claim_tbl(1).line_amount_sum := 0;
3617 l_split_claim_tbl(1).parent_claim_id := l_open_claim_tbl(i).claim_id;
3618 l_split_claim_tbl(1).parent_object_ver_num := l_obj_ver_num;
3619 l_split_claim_tbl(1).line_table := NULL;
3620
3621 OZF_Utility_PVT.debug_message('Split Claim '||l_open_claim_tbl(i).claim_number||' for '||l_split_claim_tbl(1).amount);
3622
3623 l_group_rem_amt := l_group_rem_amt + l_split_claim_tbl(1).amount;
3624
3625 OZF_SPLIT_CLAIM_PVT.create_child_claim_tbl (
3626 p_api_version => l_api_version
3627 ,p_init_msg_list => FND_API.g_false
3628 ,p_commit => FND_API.g_false
3629 ,p_validation_level => FND_API.g_valid_level_full
3630 ,x_return_status => l_return_status
3631 ,x_msg_data => x_msg_data
3632 ,x_msg_count => x_msg_count
3633 ,px_child_claim_tbl => l_split_claim_tbl --12985686
3634 ,p_mode => 'AUTO'
3635 );
3636 IF l_return_status = FND_API.g_ret_sts_error THEN
3637 RAISE FND_API.g_exc_error;
3638 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3639 RAISE FND_API.g_exc_unexpected_error;
3640 END IF;
3641 ELSIF ABS(l_open_claim_tbl(i).amount_settled) > ABS(l_claim_amount_rem) THEN
3642 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3643 FND_MESSAGE.Set_Name('OZF','OZF_SETL_CLA_AMT_ERR');
3644 FND_MSG_PUB.Add;
3645 END IF;
3646 RAISE FND_API.g_exc_error;
3647 END IF;
3648 EXIT WHEN i = l_open_claim_tbl.LAST;
3649 i := l_open_claim_tbl.NEXT(i);
3650 END LOOP;
3651 END IF;
3652
3653 ------------------------------
3654 -- 4. Group Settlement Docs --
3655 ------------------------------
3656 IF OZF_DEBUG_HIGH_ON THEN
3657 OZF_Utility_PVT.debug_message('4. Group Settlement Docs');
3658 END IF;
3659
3660 i := p_open_transaction_tbl.FIRST;
3661 IF i IS NOT NULL THEN
3662 LOOP
3663 l_group_trx_amt := l_group_trx_amt + p_open_transaction_tbl(i).amount_settled;
3664
3665 IF p_open_transaction_tbl(i).trx_class IN ('INV', 'DM', 'CB') THEN
3666 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method := 'DEBIT_MEMO';
3667 ELSIF p_open_transaction_tbl(i).trx_class IN ('CM') THEN
3668 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method := 'CREDIT_MEMO';
3669 END IF;
3670 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_id := p_open_transaction_tbl(i).customer_trx_id;
3671 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_number := p_open_transaction_tbl(i).trx_number;
3672 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := p_open_transaction_tbl(i).cust_trx_type_id;
3673 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_amount := p_open_transaction_tbl(i).amount_settled;
3674 l_group_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
3675 l_group_settle_doc_tbl(l_idx_setl_doc).claim_id := l_group_claim_id;
3676 l_idx_setl_doc := l_idx_setl_doc + 1;
3677 EXIT WHEN i = p_open_transaction_tbl.LAST;
3678 i := p_open_transaction_tbl.NEXT(i);
3679 END LOOP;
3680 END IF;
3681
3682 i := p_payment_method_tbl.FIRST;
3683 IF i IS NOT NULL THEN
3684 LOOP
3685 l_group_settle_amt := l_group_settle_amt + p_payment_method_tbl(i).amount_settled;
3686
3687 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method := p_payment_method_tbl(i).payment_method;
3688 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_amount := p_payment_method_tbl(i).amount_settled * -1;
3689 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := p_payment_method_tbl(i).gl_date;
3690 --//Bug 5345095
3691 IF p_payment_method_tbl(i).payment_method = 'WRITE_OFF' THEN
3692 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := p_payment_method_tbl(i).wo_adj_trx_id;
3693 END IF;
3694 l_group_settle_doc_tbl(l_idx_setl_doc).wo_rec_trx_id := p_payment_method_tbl(i).wo_rec_trx_id;
3695 l_group_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
3696 l_group_settle_doc_tbl(l_idx_setl_doc).claim_id := l_group_claim_id;
3697
3698 IF l_group_settle_doc_tbl(l_idx_setl_doc).gl_date IS NULL AND
3699 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method IN ('WRITE_OFF', 'CHARGEBACK') THEN
3700 OPEN csr_get_gl_date_type(l_group_claim_rec.set_of_books_id);
3701 FETCH csr_get_gl_date_type INTO l_gl_date_type;
3702 CLOSE csr_get_gl_date_type;
3703
3704 IF l_gl_date_type = 'CLAIM_DATE' THEN
3705 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := l_group_claim_rec.claim_date;
3706 ELSIF l_gl_date_type = 'DUE_DATE' THEN
3707 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := l_group_claim_rec.due_date;
3708 ELSIF l_gl_date_type = 'SYSTEM_DATE' THEN
3709 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := SYSDATE;
3710 END IF;
3711
3712 IF l_group_settle_doc_tbl(l_idx_setl_doc).gl_date IS NULL THEN
3713 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3714 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_NO_GL_DATE');
3715 FND_MSG_PUB.Add;
3716 END IF;
3717 RAISE FND_API.g_exc_error;
3718 END IF;
3719 END IF;
3720
3721 l_idx_setl_doc := l_idx_setl_doc + 1;
3722 EXIT WHEN i = p_payment_method_tbl.LAST;
3723 i := p_payment_method_tbl.NEXT(i);
3724 END LOOP;
3725 END IF;
3726
3727 IF OZF_DEBUG_HIGH_ON THEN
3728 i := l_group_settle_doc_tbl.FIRST;
3729 IF i IS NOT NULL THEN
3730 LOOP
3731 OZF_Utility_PVT.debug_message('--- '||i||' ---');
3732 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').claim_id (group) = '||l_group_settle_doc_tbl(i).claim_id);
3733 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').settlement_id = '||l_group_settle_doc_tbl(i).settlement_id);
3734 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').settlement_number = '||l_group_settle_doc_tbl(i).settlement_number);
3735 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').settlement_amount = '||l_group_settle_doc_tbl(i).settlement_amount);
3736 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').payment_method = '||l_group_settle_doc_tbl(i).payment_method);
3737 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').payment_status = '||l_group_settle_doc_tbl(i).payment_status);
3738 EXIT WHEN i = l_group_settle_doc_tbl.LAST;
3739 i := l_group_settle_doc_tbl.NEXT(i);
3740 END LOOP;
3741 END IF;
3742 END IF;
3743
3744 ---------------------------------------------
3745 -- 5. Create Settlement Docs of Mass Claim --
3746 ---------------------------------------------
3747 IF OZF_DEBUG_HIGH_ON THEN
3748 OZF_Utility_PVT.debug_message('5. Create Settlement Docs of Mass Claim');
3749 END IF;
3750
3751 OZF_Settlement_Doc_PVT.Create_Settlement_Doc_Tbl(
3752 p_api_version_number => l_api_version,
3753 p_init_msg_list => FND_API.G_FALSE,
3754 p_commit => FND_API.G_FALSE,
3755 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3756 x_return_status => l_return_status,
3757 x_msg_count => x_msg_count,
3758 x_msg_data => x_msg_data,
3759 p_settlement_doc_tbl => l_group_settle_doc_tbl,
3760 x_settlement_doc_id_tbl => l_group_settle_doc_id_tbl
3761 );
3762 IF l_return_status = FND_API.g_ret_sts_error THEN
3763 RAISE FND_API.g_exc_error;
3764 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3765 RAISE FND_API.g_exc_unexpected_error;
3766 END IF;
3767
3768 ------------------------------
3769 -- 6. Break Mass Settlement --
3770 ------------------------------
3771 IF OZF_DEBUG_HIGH_ON THEN
3772 OZF_Utility_PVT.debug_message('6. Break Mass Settlement');
3773 END IF;
3774
3775 Break_Mass_Settlement(
3776 p_group_claim_id => l_group_claim_id,
3777 p_settlement_type => l_settlement_type,
3778 x_return_status => l_return_status,
3779 x_msg_data => x_msg_data,
3780 x_msg_count => x_msg_count
3781 );
3782 IF l_return_status = FND_API.g_ret_sts_error THEN
3783 RAISE FND_API.g_exc_error;
3784 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3785 RAISE FND_API.g_exc_unexpected_error;
3786 END IF;
3787
3788
3789 ---------------------------------
3790 -- 7. Complete Mass Settlement --
3791 ---------------------------------
3792 IF OZF_DEBUG_HIGH_ON THEN
3793 OZF_Utility_PVT.debug_message('7. Complete Mass Settlement');
3794 END IF;
3795
3796 Complete_Mass_Settlement(
3797 p_group_claim_id => l_group_claim_id,
3798 x_claim_tbl => l_complete_claim_group_tbl,
3799 x_return_status => l_return_status,
3800 x_msg_data => x_msg_data,
3801 x_msg_count => x_msg_count
3802 );
3803 IF l_return_status = FND_API.g_ret_sts_error THEN
3804 RAISE FND_API.g_exc_error;
3805 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3806 RAISE FND_API.g_exc_unexpected_error;
3807 END IF;
3808
3809 BEGIN
3810 UPDATE ozf_claims_all
3811 SET amount = l_group_claim_amt
3812 , amount_remaining = l_group_trx_amt
3813 , amount_settled = l_group_settle_amt
3814 , amount_adjusted = l_group_rem_amt
3815 , settled_by = NVL(FND_GLOBAL.user_id,-1) --//Bugfix : 8202109
3816 , settled_date = TRUNC(SYSDATE)
3817 WHERE claim_id = l_group_claim_id;
3818
3819 --//Bugfix : 7439145
3820 i := p_open_claim_tbl.FIRST;
3821 IF i IS NOT NULL THEN
3822 LOOP
3823 BEGIN
3824 --//Bugfix : 7661712
3825 OPEN p_acctd_claim_amts(p_open_claim_tbl(i).claim_id);
3826 FETCH p_acctd_claim_amts INTO l_act_amt,l_act_amt_adjusted,l_amt,l_amt_adjusted;
3827 CLOSE p_acctd_claim_amts;
3828
3829 l_amt_settled := l_act_amt - l_act_amt_adjusted;
3830 l_act_amt_settled := l_act_amt - l_act_amt_adjusted;
3831
3832 UPDATE ozf_claims_all
3833 SET amount_settled = l_amt_settled,
3834 amount_remaining = l_amt -(l_amt_adjusted + l_amt_settled),
3835 acctd_amount_settled = l_act_amt_settled,
3836 acctd_amount_remaining = l_act_amt - (l_act_amt_adjusted + l_act_amt_settled),
3837 settled_by = NVL(FND_GLOBAL.user_id,-1), --//Bugfix : 8202109
3838 settled_date = TRUNC(SYSDATE)
3839 WHERE claim_id =p_open_claim_tbl(i).claim_id;
3840
3841 EXCEPTION
3842 WHEN Others THEN
3843 RAISE FND_API.g_exc_unexpected_error;
3844 END;
3845 EXIT WHEN i = p_open_claim_tbl.LAST;
3846 i := p_open_claim_tbl.NEXT(i);
3847 END LOOP;
3848 END IF;
3849 --//End Bugfix# 7439145
3850
3851 EXCEPTION
3852 WHEN OTHERS THEN
3853 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3854 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
3855 FND_MSG_PUB.add;
3856 END IF;
3857 IF OZF_DEBUG_LOW_ON THEN
3858 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3859 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3860 FND_MSG_PUB.Add;
3861 END IF;
3862 RAISE FND_API.g_exc_unexpected_error;
3863 END;
3864
3865
3866 /* --------------------------------
3867 -- 8. Approve Mass Settlement --
3868 --------------------------------
3869 IF OZF_DEBUG_HIGH_ON THEN
3870 OZF_Utility_PVT.debug_message('8. Approve Mass Settlement');
3871 END IF;
3872
3873 Approve_Mass_Settlement(
3874 p_group_claim_id => l_group_claim_id,
3875 p_complete_claim_group_tbl => l_complete_claim_group_tbl,
3876 x_return_status => l_return_status,
3877 x_msg_data => x_msg_data,
3878 x_msg_count => x_msg_count
3879 );
3880 IF l_return_status = FND_API.g_ret_sts_error THEN
3881 RAISE FND_API.g_exc_error;
3882 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3883 RAISE FND_API.g_exc_unexpected_error;
3884 END IF;*/
3885
3886 OPEN csr_mass_setl_appr_req;
3887 FETCH csr_mass_setl_appr_req INTO l_appr_req;
3888 CLOSE csr_mass_setl_appr_req;
3889
3890
3891 IF l_appr_req = 'N' THEN
3892 ---------------------------
3893 -- 9. Start Mass Payment --
3894 ---------------------------
3895 IF OZF_DEBUG_HIGH_ON THEN
3896 OZF_Utility_PVT.debug_message('9. Start Mass Payment');
3897 END IF;
3898
3899 Start_Mass_Payment(
3900 p_group_claim_id => l_group_claim_id,
3901 x_return_status => l_return_status,
3902 x_msg_data => x_msg_data,
3903 x_msg_count => x_msg_count
3904 );
3905 IF l_return_status = FND_API.g_ret_sts_error THEN
3906 RAISE FND_API.g_exc_error;
3907 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3908 RAISE FND_API.g_exc_unexpected_error;
3909 END IF;
3910 ELSE
3911 IF OZF_DEBUG_HIGH_ON THEN
3912 OZF_Utility_PVT.debug_message('8. Approve Mass Settlement');
3913 END IF;
3914
3915 Approve_Mass_Settlement(
3916 p_group_claim_id => l_group_claim_id,
3917 p_complete_claim_group_tbl => l_complete_claim_group_tbl,
3918 x_return_status => l_return_status,
3919 x_msg_data => x_msg_data,
3920 x_msg_count => x_msg_count
3921 );
3922
3923 IF l_return_status = FND_API.g_ret_sts_error THEN
3924 RAISE FND_API.g_exc_error;
3925 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3926 RAISE FND_API.g_exc_unexpected_error;
3927 END IF;
3928 END IF;
3929
3930 IF OZF_DEBUG_HIGH_ON THEN
3931 OZF_Utility_PVT.debug_message(l_full_name||': end');
3932 END IF;
3933
3934
3935 ------------------------ finish ------------------------
3936 x_return_status := FND_API.G_RET_STS_SUCCESS;
3937
3938 -- Standard check for p_commit
3939 IF FND_API.to_Boolean(p_commit) THEN
3940 COMMIT WORK;
3941 END IF;
3942
3943 FND_MSG_PUB.count_and_get(
3944 p_encoded => FND_API.g_false,
3945 p_count => x_msg_count,
3946 p_data => x_msg_data
3947 );
3948
3949
3950
3951 EXCEPTION
3952 WHEN FND_API.g_exc_error THEN
3953 ROLLBACK TO Settle_Mass_Settlement;
3954 x_return_status := FND_API.g_ret_sts_error;
3955 FND_MSG_PUB.count_and_get (
3956 p_encoded => FND_API.g_false
3957 ,p_count => x_msg_count
3958 ,p_data => x_msg_data
3959 );
3960
3961 WHEN FND_API.g_exc_unexpected_error THEN
3962 ROLLBACK TO Settle_Mass_Settlement;
3963 x_return_status := FND_API.g_ret_sts_unexp_error ;
3964 FND_MSG_PUB.count_and_get (
3965 p_encoded => FND_API.g_false
3966 ,p_count => x_msg_count
3967 ,p_data => x_msg_data
3968 );
3969
3970 WHEN OTHERS THEN
3971 ROLLBACK TO Settle_Mass_Settlement;
3972 x_return_status := FND_API.g_ret_sts_unexp_error ;
3973 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3974 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3975 END IF;
3976 FND_MSG_PUB.count_and_get(
3977 p_encoded => FND_API.g_false
3978 ,p_count => x_msg_count
3979 ,p_data => x_msg_data
3980 );
3981
3982 END Settle_Mass_Settlement;
3983
3984
3985 END OZF_MASS_SETTLEMENT_PVT;