[Home] [Help]
PACKAGE BODY: APPS.OZF_MASS_SETTLEMENT_PVT
Source
1 PACKAGE BODY OZF_MASS_SETTLEMENT_PVT AS
2 /* $Header: ozfvmstb.pls 120.15.12010000.5 2008/11/26 05:15:42 bkunjan 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 ,x_return_status => l_return_status
751 ,x_msg_data => x_msg_data
752 ,x_msg_count => x_msg_count
753 );
754 IF l_return_status = FND_API.g_ret_sts_error THEN
755 RAISE FND_API.g_exc_error;
756 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
757 RAISE FND_API.g_exc_unexpected_error;
758 END IF;
759 ELSE
760 IF OZF_DEBUG_HIGH_ON THEN
761 OZF_Utility_PVT.debug_message('Invoice Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
762 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Reapply creit memo on receipt');
763 END IF;
764
765 l_settlement_amount := (p_credit_memo_amount * -1);--Bug4308188
766 -- 2. Reapply credit memo on receipt
767 arp_deduction_cover2.reapply_credit_memo(
768 p_customer_trx_id => p_payment_reference_id ,
769 p_cash_receipt_id => p_claim_rec.receipt_id,
770 p_amount_applied => l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1),
771 p_init_msg_list => FND_API.g_false,
772 x_return_status => l_return_status,
773 x_msg_count => x_msg_count,
774 x_msg_data => x_msg_data);
775 IF l_return_status = FND_API.g_ret_sts_error THEN
776 RAISE FND_API.g_exc_error;
777 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
778 RAISE FND_API.g_exc_unexpected_error;
779 END IF;
780 END IF;
781
782 IF OZF_DEBUG_HIGH_ON THEN
783 OZF_Utility_PVT.debug_message('Invoice Deduction -> 3. Unapply claim investigation');
784 OZF_Utility_PVT.debug_message('original invoice deduction amount = '||l_old_applied_invoice.amount_applied);
785 OZF_Utility_PVT.debug_message('reapply invoice deduction amount = '||(l_old_applied_invoice.amount_applied + p_credit_memo_amount));
786 END IF;
787 -- 3. Reapply claim investigation
788 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
789 p_claim_rec => p_claim_rec
790 ,p_reapply_amount => l_old_applied_invoice.amount_applied + p_credit_memo_amount
791 ,x_return_status => l_return_status
792 ,x_msg_data => x_msg_data
793 ,x_msg_count => x_msg_count
794 );
795 IF l_return_status = FND_API.g_ret_sts_error THEN
796 RAISE FND_API.g_exc_error;
797 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
798 RAISE FND_API.g_exc_unexpected_error;
799 END IF;
800
801 l_online_upd_ded_status := TRUE;
802
803 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
804 IF l_cm_applied_on_rec_amt IS NULL THEN
805 /*------------------------------------------------------------*
806 | Receipt Deduction -> 1. Apply credit memo on receipt.
807 | 2. Unapply claim investigation
808 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
809 | Receipt Deduction -> 0.5. Unapply credit memo on receipt
810 | 1. Apply credit memo with increased amount on receipt
811 | 2. Unapply claim investigation
812 *------------------------------------------------------------*/
813 IF OZF_DEBUG_HIGH_ON THEN
814 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Apply creit memo on receipt');
815 END IF;
816 -- 1. Apply creit memo on receipt
817 OZF_AR_Payment_PVT.Apply_on_Receipt(
818 p_cash_receipt_id => p_claim_rec.receipt_id
819 ,p_customer_trx_id => p_payment_reference_id
820 ,p_new_applied_amount => (p_credit_memo_amount * -1)
821 ,p_comments => p_claim_rec.comments
822 ,p_customer_reference => p_claim_rec.customer_ref_number
823 ,x_return_status => l_return_status
824 ,x_msg_data => x_msg_data
825 ,x_msg_count => x_msg_count
826 );
827 IF l_return_status = FND_API.g_ret_sts_error THEN
828 RAISE FND_API.g_exc_error;
829 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
830 RAISE FND_API.g_exc_unexpected_error;
831 END IF;
832 IF OZF_DEBUG_HIGH_ON THEN
833 OZF_Utility_PVT.debug_message('Receipt Deduction -> 2. Unapply claim investigation');
834 END IF;
835 ELSE
836 /*------------------------------------------------------------*
837 | Receipt Deduction
838 *------------------------------------------------------------*/
839 IF OZF_DEBUG_HIGH_ON THEN
840 OZF_Utility_PVT.debug_message('Receipt Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
841 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Reapply credit memo with increased amount on receipt');
842 END IF;
843
844 l_settlement_amount := (p_credit_memo_amount * -1); --Bug4308188
845 -- 2. Reapply credit memo on receipt
846 arp_deduction_cover2.reapply_credit_memo(
847 p_customer_trx_id => p_payment_reference_id ,
848 p_cash_receipt_id => p_claim_rec.receipt_id,
849 p_amount_applied => l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1),
850 p_init_msg_list => FND_API.g_false,
851 x_return_status => l_return_status,
852 x_msg_count => x_msg_count,
853 x_msg_data => x_msg_data);
854 IF l_return_status = FND_API.g_ret_sts_error THEN
855 RAISE FND_API.g_exc_error;
856 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
857 RAISE FND_API.g_exc_unexpected_error;
858 END IF;
859 END IF;
860
861 -- 2. Unapply claim investigation
862 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
863 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
864 CLOSE csr_claim_investigation_amount;
865
866 IF OZF_DEBUG_HIGH_ON THEN
867 OZF_Utility_PVT.debug_message('original claim investigation amount = '||l_orig_dispute_amount);
868 OZF_Utility_PVT.debug_message('reapply claim investigation amount = '||(l_orig_dispute_amount + p_credit_memo_amount));
869 END IF;
870 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
871 p_claim_rec => p_claim_rec
872 ,p_reapply_amount => l_orig_dispute_amount + p_credit_memo_amount --(l_orig_reapply_amount - p_credit_memo_amount) * -1
873 ,x_return_status => l_return_status
874 ,x_msg_data => x_msg_data
875 ,x_msg_count => x_msg_count
876 );
877 IF l_return_status = FND_API.g_ret_sts_error THEN
878 RAISE FND_API.g_exc_error;
879 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
880 RAISE FND_API.g_exc_unexpected_error;
881 END IF;
882 l_online_upd_ded_status := TRUE;
883 END IF; -- end if p_deduction_type = 'SOURCE_DED', elsif p_deduction_type = 'DEDUCTION_DED'
884 END IF; -- end if payment_reference_id is null
885
886 /*------------------------------------------------------------*
887 | Update payment detail
888 *------------------------------------------------------------*/
889 IF l_online_upd_ded_status THEN
890 -- Update Deduction payment detail
891 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
892 p_api_version => l_api_version
893 ,p_init_msg_list => FND_API.g_false
894 ,p_commit => FND_API.g_false
895 ,p_validation_level => FND_API.g_valid_level_full
896 ,x_return_status => l_return_status
897 ,x_msg_data => x_msg_data
898 ,x_msg_count => x_msg_count
899 ,p_claim_id => p_claim_rec.claim_id
900 ,p_payment_method => 'PREV_OPEN_CREDIT'
901 ,p_deduction_type => p_deduction_type
902 ,p_cash_receipt_id => p_claim_rec.receipt_id
903 ,p_customer_trx_id => p_payment_reference_id
904 ,p_adjust_id => NULL
905 ,p_settlement_doc_id => p_settlement_doc_id
906 ,p_settlement_mode => 'MASS_SETTLEMENT'
907 ,p_settlement_amount => l_settlement_amount --Bug4308188
908 );
909 IF l_return_status = FND_API.g_ret_sts_error THEN
910 RAISE FND_API.g_exc_error;
911 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
912 RAISE FND_API.g_exc_unexpected_error;
913 END IF;
914 END IF;
915
916 END IF;
917
918 IF OZF_DEBUG_HIGH_ON THEN
919 OZF_Utility_PVT.debug_message(l_full_name||': end');
920 END IF;
921 EXCEPTION
922 WHEN FND_API.g_exc_error THEN
923 x_return_status := FND_API.g_ret_sts_error;
924
925 WHEN FND_API.g_exc_unexpected_error THEN
926 x_return_status := FND_API.g_ret_sts_unexp_error ;
927
928 WHEN OTHERS THEN
929 x_return_status := FND_API.g_ret_sts_unexp_error ;
930 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
931 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
932 END IF;
933
934 END Pay_by_Credit_Memo;
935
936
937 /*=======================================================================*
938 | PROCEDURE
939 | Pay_by_Debit_Memo
940 |
941 | NOTES
942 |
943 | HISTORY
944 | 15-MAR-2002 mchang Create.
945 *=======================================================================*/
946 PROCEDURE Pay_by_Debit_Memo(
947 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
948 ,p_deduction_type IN VARCHAR2
949 ,p_payment_reference_id IN NUMBER
950 ,p_debit_memo_amount IN NUMBER
951 ,p_settlement_doc_id IN NUMBER
952
953 ,x_return_status OUT NOCOPY VARCHAR2
954 ,x_msg_data OUT NOCOPY VARCHAR2
955 ,x_msg_count OUT NOCOPY NUMBER
956 )
957 IS
958 l_api_version CONSTANT NUMBER := 1.0;
959 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Debit_Memo';
960 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
961 l_return_status VARCHAR2(1);
962
963 l_dm_trx_type_id NUMBER;
964 l_online_upd_ded_status BOOLEAN := FALSE;
965 l_orig_dispute_amount NUMBER;
966 l_payment_trx_number VARCHAR2(30);
967 l_dm_applied_on_rec_amt NUMBER;
968 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
969
970 CURSOR csr_dm_trx_type_id(cv_claim_type_id IN NUMBER) IS
971 SELECT dm_trx_type_id
972 FROM ozf_claim_types_all_b
973 WHERE claim_type_id = cv_claim_type_id;
974
975 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
976 SELECT amount_applied
977 FROM ar_receivable_applications
978 WHERE application_ref_type = 'CLAIM'
979 AND applied_payment_schedule_id = -4
980 AND display = 'Y'
981 AND secondary_application_ref_id = cv_root_claim_id;
982
983 CURSOR csr_payment_trx_number(cv_customer_trx_id IN NUMBER) IS
984 SELECT trx_number
985 FROM ra_customer_trx
986 WHERE customer_trx_id = cv_customer_trx_id;
987
988 CURSOR csr_dm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
989 SELECT amount_applied, apply_date -- Fix for Bug 3091401. TM passes old apply date
990 FROM ar_receivable_applications
991 WHERE cash_receipt_id = cv_cash_receipt_id
992 AND applied_customer_trx_id = cv_customer_trx_id
993 AND display = 'Y'
994 AND status = 'APP';
995
996 l_settlement_amount NUMBER := NULL;
997
998 BEGIN
999 -------------------- initialize -----------------------
1000 IF OZF_DEBUG_HIGH_ON THEN
1001 OZF_Utility_PVT.debug_message(l_full_name||': start');
1002 END IF;
1003
1004 x_return_status := FND_API.g_ret_sts_success;
1005
1006 ------------------------ start -------------------------
1007 IF p_deduction_type = 'RECEIPT_OPM' THEN
1008 IF p_payment_reference_id IS NULL THEN
1009 /*------------------------------------------------------------*
1010 | OVERPAYMENT -> No open debit memo specified --> AutoInvoice
1011 *------------------------------------------------------------*/
1012 -- 1. AutoInvoice
1013 OZF_AR_INTERFACE_PVT.Interface_Claim(
1014 p_api_version => l_api_version
1015 ,p_init_msg_list => FND_API.g_false
1016 ,p_commit => FND_API.g_false
1017 ,p_validation_level => FND_API.g_valid_level_full
1018 ,x_return_status => l_return_status
1019 ,x_msg_data => x_msg_data
1020 ,x_msg_count => x_msg_count
1021 ,p_claim_id => p_claim_rec.claim_id
1022 );
1023 IF l_return_status = FND_API.g_ret_sts_error THEN
1024 RAISE FND_API.g_exc_error;
1025 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1026 RAISE FND_API.g_exc_unexpected_error;
1027 END IF;
1028
1029 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
1030 l_online_upd_ded_status := FALSE;
1031 ELSE
1032 /*------------------------------------------------------------*
1033 | Update Claim Status to CLOSED.
1034 *------------------------------------------------------------*/
1035 /*
1036 Close_Claim(
1037 p_claim_rec => p_claim_rec
1038 ,x_return_status => l_return_status
1039 ,x_msg_data => x_msg_data
1040 ,x_msg_count => x_msg_count
1041 );
1042 IF l_return_status = FND_API.g_ret_sts_error THEN
1043 RAISE FND_API.g_exc_error;
1044 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1045 RAISE FND_API.g_exc_unexpected_error;
1046 END IF;
1047 */
1048
1049 /*------------------------------------------------------------*
1050 | Overpayment -> 1. Unapply claim investigation
1051 | 2. Apply debit memo on receipt.
1052 | <<Pay by Previous Open Debit Memo which already exists on the receipt>>:
1053 | Overpayment -> 1. Unapply claim investigation
1054 | 1.5. Unapply debit memo on receipt
1055 | 2. Apply debit memo on receipt.
1056 *------------------------------------------------------------*/
1057 OPEN csr_dm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
1058 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
1059 CLOSE csr_dm_exist_on_rec;
1060
1061 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation');
1062 -- 1. Unapply claim investigation
1063 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
1064 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
1065 CLOSE csr_claim_investigation_amount;
1066
1067 OZF_Utility_PVT.debug_message('original overpayment amount = '||l_orig_dispute_amount);
1068 OZF_Utility_PVT.debug_message('reapply overpayment amount = '||(l_orig_dispute_amount + p_debit_memo_amount));
1069
1070 OZF_AR_Payment_PVT.Unapply_Claim_Investigation(
1071 p_claim_rec => p_claim_rec
1072 ,p_reapply_amount => (l_orig_dispute_amount + p_debit_memo_amount)
1073 ,x_return_status => l_return_status
1074 ,x_msg_data => x_msg_data
1075 ,x_msg_count => x_msg_count
1076 );
1077 IF l_return_status = FND_API.g_ret_sts_error THEN
1078 RAISE FND_API.g_exc_error;
1079 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1080 RAISE FND_API.g_exc_unexpected_error;
1081 END IF;
1082
1083
1084 IF l_dm_applied_on_rec_amt IS NULL THEN
1085 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo on receipt');
1086 -- 2. Apply debit memo on receipt
1087 OZF_AR_Payment_PVT.Apply_on_Receipt(
1088 p_cash_receipt_id => p_claim_rec.receipt_id
1089 ,p_customer_trx_id => p_payment_reference_id
1090 ,p_new_applied_amount => p_debit_memo_amount * -1
1091 ,p_comments => p_claim_rec.comments
1092 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
1093 ,x_return_status => l_return_status
1094 ,x_msg_data => x_msg_data
1095 ,x_msg_count => x_msg_count
1096 );
1097 IF l_return_status = FND_API.g_ret_sts_error THEN
1098 RAISE FND_API.g_exc_error;
1099 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1100 RAISE FND_API.g_exc_unexpected_error;
1101 END IF;
1102 ELSE
1103 OZF_Utility_PVT.debug_message('Overpayment: Pay by Previous Open Debit Memo which already exists on the receipt');
1104 OZF_Utility_PVT.debug_message('Overpayment -> 1.5. Unapply debit memo on receipt');
1105 -- 1.5. Unapply creit memo on receipt
1106 OZF_AR_Payment_PVT.Unapply_from_Receipt(
1107 p_cash_receipt_id => p_claim_rec.receipt_id
1108 ,p_customer_trx_id => p_payment_reference_id
1109 ,x_return_status => l_return_status
1110 ,x_msg_data => x_msg_data
1111 ,x_msg_count => x_msg_count
1112 );
1113 IF l_return_status = FND_API.g_ret_sts_error THEN
1114 RAISE FND_API.g_exc_error;
1115 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1116 RAISE FND_API.g_exc_unexpected_error;
1117 END IF;
1118
1119 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo with increased amount on receipt');
1120 l_settlement_amount := (p_debit_memo_amount * -1); --Bug4308188
1121 -- 2. Apply creit memo on receipt
1122 OZF_AR_Payment_PVT.Apply_on_Receipt(
1123 p_cash_receipt_id => p_claim_rec.receipt_id
1124 ,p_customer_trx_id => p_payment_reference_id
1125 ,p_new_applied_amount => l_dm_applied_on_rec_amt + (p_debit_memo_amount * -1)
1126 ,p_comments => p_claim_rec.comments
1127 ,p_apply_date => l_apply_date -- Fix for Bug 3091401. TM passes old apply date
1128 ,x_return_status => l_return_status
1129 ,x_msg_data => x_msg_data
1130 ,x_msg_count => x_msg_count
1131 );
1132 IF l_return_status = FND_API.g_ret_sts_error THEN
1133 RAISE FND_API.g_exc_error;
1134 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1135 RAISE FND_API.g_exc_unexpected_error;
1136 END IF;
1137 END IF;
1138
1139 l_online_upd_ded_status := TRUE;
1140 END IF;
1141 ELSE
1142 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1143 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
1144 FND_MSG_PUB.add;
1145 END IF;
1146 END IF;
1147
1148
1149 /*------------------------------------------------------------*
1150 | Update Deduction payment detail
1151 *------------------------------------------------------------*/
1152 IF l_online_upd_ded_status THEN
1153 -- Update Deduction payment detail
1154 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1155 p_api_version => l_api_version
1156 ,p_init_msg_list => FND_API.g_false
1157 ,p_commit => FND_API.g_false
1158 ,p_validation_level => FND_API.g_valid_level_full
1159 ,x_return_status => l_return_status
1160 ,x_msg_data => x_msg_data
1161 ,x_msg_count => x_msg_count
1162 ,p_claim_id => p_claim_rec.claim_id
1163 ,p_payment_method => 'PREV_OPEN_DEBIT'
1164 ,p_deduction_type => p_deduction_type
1165 ,p_cash_receipt_id => p_claim_rec.receipt_id
1166 ,p_customer_trx_id => p_payment_reference_id
1167 ,p_adjust_id => NULL
1168 ,p_settlement_doc_id => p_settlement_doc_id
1169 ,p_settlement_mode => 'MASS_SETTLEMENT'
1170 ,p_settlement_amount => l_settlement_amount --Bug4308188
1171 );
1172 IF l_return_status = FND_API.g_ret_sts_error THEN
1173 RAISE FND_API.g_exc_error;
1174 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1175 RAISE FND_API.g_exc_unexpected_error;
1176 END IF;
1177 END IF;
1178
1179 IF OZF_DEBUG_HIGH_ON THEN
1180 OZF_Utility_PVT.debug_message(l_full_name||': end');
1181 END IF;
1182 EXCEPTION
1183 WHEN FND_API.g_exc_error THEN
1184 x_return_status := FND_API.g_ret_sts_error;
1185
1186 WHEN FND_API.g_exc_unexpected_error THEN
1187 x_return_status := FND_API.g_ret_sts_unexp_error ;
1188
1189 WHEN OTHERS THEN
1190 x_return_status := FND_API.g_ret_sts_unexp_error ;
1191 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1192 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1193 END IF;
1194
1195 END Pay_by_Debit_Memo;
1196
1197
1198 /*=======================================================================*
1199 | PROCEDURE
1200 | Pay_by_Chargeback
1201 |
1202 | NOTES
1203 |
1204 | HISTORY
1205 | 15-MAR-2002 mchang Create.
1206 *=======================================================================*/
1207 PROCEDURE Pay_by_Chargeback(
1208 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1209 ,p_deduction_type IN VARCHAR2
1210 ,p_chargeback_amount IN NUMBER
1211 ,p_settlement_doc_id IN NUMBER
1212 ,p_gl_date IN DATE
1213
1214 ,x_return_status OUT NOCOPY VARCHAR2
1215 ,x_msg_data OUT NOCOPY VARCHAR2
1216 ,x_msg_count OUT NOCOPY NUMBER
1217 )
1218 IS
1219 l_api_version CONSTANT NUMBER := 1.0;
1220 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Chargeback';
1221 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1222 l_return_status VARCHAR2(1);
1223
1224 l_cb_customer_trx_id NUMBER;
1225 l_chargeback_amount NUMBER;
1226
1227 BEGIN
1228 -------------------- initialize -----------------------
1229 IF OZF_DEBUG_HIGH_ON THEN
1230 OZF_Utility_PVT.debug_message(l_full_name||': start');
1231 END IF;
1232
1233 x_return_status := FND_API.g_ret_sts_success;
1234
1235 ------------------------ start -------------------------
1236 /*------------------------------------------------------------*
1237 | Update Claim Status to CLOSED.
1238 *------------------------------------------------------------*/
1239 /*
1240 Close_Claim(
1241 p_claim_rec => p_claim_rec
1242 ,x_return_status => l_return_status
1243 ,x_msg_data => x_msg_data
1244 ,x_msg_count => x_msg_count
1245 );
1246 IF l_return_status = FND_API.g_ret_sts_error THEN
1247 RAISE FND_API.g_exc_error;
1248 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1249 RAISE FND_API.g_exc_unexpected_error;
1250 END IF;
1251 */
1252 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED') THEN
1253 IF p_deduction_type = 'SOURCE_DED'THEN
1254 l_chargeback_amount := p_chargeback_amount;
1255 ELSIF p_deduction_type = 'RECEIPT_DED'THEN
1256 l_chargeback_amount := p_chargeback_amount * -1;
1257 END IF;
1258
1259 OZF_AR_Payment_PVT.Create_AR_Chargeback(
1260 p_claim_rec => p_claim_rec
1261 ,p_chargeback_amount => l_chargeback_amount
1262 ,p_gl_date => p_gl_date
1263 ,x_cb_customer_trx_id => l_cb_customer_trx_id
1264 ,x_return_status => l_return_status
1265 ,x_msg_data => x_msg_data
1266 ,x_msg_count => x_msg_count
1267 );
1268 IF l_return_status = FND_API.g_ret_sts_error THEN
1269 RAISE FND_API.g_exc_error;
1270 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1271 RAISE FND_API.g_exc_unexpected_error;
1272 END IF;
1273
1274 IF OZF_DEBUG_HIGH_ON THEN
1275 OZF_Utility_PVT.debug_message('x_cb_customer_trx_id = '||l_cb_customer_trx_id);
1276 END IF;
1277
1278 /*------------------------------------------------------------*
1279 | Update Deduction payment detail
1280 *------------------------------------------------------------*/
1281 IF l_cb_customer_trx_id IS NOT NULL THEN
1282 -- Update Deduction payment detail
1283 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1284 p_api_version => l_api_version
1285 ,p_init_msg_list => FND_API.g_false
1286 ,p_commit => FND_API.g_false
1287 ,p_validation_level => FND_API.g_valid_level_full
1288 ,x_return_status => l_return_status
1289 ,x_msg_data => x_msg_data
1290 ,x_msg_count => x_msg_count
1291 ,p_claim_id => p_claim_rec.claim_id
1292 ,p_payment_method => 'CHARGEBACK'
1293 ,p_deduction_type => p_deduction_type
1294 ,p_cash_receipt_id => p_claim_rec.receipt_id
1295 ,p_customer_trx_id => l_cb_customer_trx_id
1296 ,p_adjust_id => NULL
1297 ,p_settlement_doc_id => p_settlement_doc_id
1298 ,p_settlement_mode => 'MASS_SETTLEMENT'
1299 );
1300 IF l_return_status = FND_API.g_ret_sts_error THEN
1301 RAISE FND_API.g_exc_error;
1302 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1303 RAISE FND_API.g_exc_unexpected_error;
1304 END IF;
1305 END IF;
1306
1307 ELSE
1308 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1309 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
1310 FND_MSG_PUB.add;
1311 END IF;
1312 END IF;
1313
1314
1315 IF OZF_DEBUG_HIGH_ON THEN
1316 OZF_Utility_PVT.debug_message(l_full_name||': end');
1317 END IF;
1318 EXCEPTION
1319 WHEN FND_API.g_exc_error THEN
1320 x_return_status := FND_API.g_ret_sts_error;
1321
1322 WHEN FND_API.g_exc_unexpected_error THEN
1323 x_return_status := FND_API.g_ret_sts_unexp_error ;
1324
1325 WHEN OTHERS THEN
1326 x_return_status := FND_API.g_ret_sts_unexp_error ;
1327 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1328 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1329 END IF;
1330
1331 END Pay_by_Chargeback;
1332
1333
1334 /*=======================================================================*
1335 | PROCEDURE
1336 | Pay_by_Write_Off
1337 |
1338 | NOTES
1339 |
1340 | HISTORY
1341 | 15-MAR-2002 mchang Create.
1342 *=======================================================================*/
1343 PROCEDURE Pay_by_Write_Off(
1344 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1345 ,p_deduction_type IN VARCHAR2
1346 ,p_write_off_amount IN NUMBER
1347 ,p_settlement_doc_id IN NUMBER
1348 ,p_gl_date IN DATE
1349 ,p_wo_rec_trx_id IN NUMBER
1350
1351 ,x_return_status OUT NOCOPY VARCHAR2
1352 ,x_msg_data OUT NOCOPY VARCHAR2
1353 ,x_msg_count OUT NOCOPY NUMBER
1354 )
1355 IS
1356 l_api_version CONSTANT NUMBER := 1.0;
1357 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Write_Off';
1358 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1359 l_return_status VARCHAR2(1);
1360
1361 l_wo_adjust_id NUMBER;
1362
1363 BEGIN
1364 -------------------- initialize -----------------------
1365 IF OZF_DEBUG_HIGH_ON THEN
1366 OZF_Utility_PVT.debug_message(l_full_name||': start');
1367 END IF;
1368
1369 x_return_status := FND_API.g_ret_sts_success;
1370
1371 ------------------------ start -------------------------
1372 IF p_deduction_type = 'RECEIPT_DED' AND
1373 NOT ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed THEN
1374 /*------------------------------------------------------------
1375 | Receipt Deduction -> Invoke Settlement Workflow
1376 *-----------------------------------------------------------*/
1377 OZF_AR_PAYMENT_PVT.Process_Settlement_WF(
1378 p_claim_id => p_claim_rec.claim_id
1379 ,x_return_status => l_return_status
1380 ,x_msg_data => x_msg_data
1381 ,x_msg_count => x_msg_count
1382 );
1383 IF l_return_status = FND_API.g_ret_sts_error THEN
1384 RAISE FND_API.g_exc_error;
1385 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1386 RAISE FND_API.g_exc_unexpected_error;
1387 END IF;
1388
1389 BEGIN
1390 UPDATE ozf_claims_all
1391 SET payment_status = 'PENDING'
1392 , status_code = 'PENDING_CLOSE'
1393 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
1394 'OZF_CLAIM_STATUS'
1395 ,'PENDING_CLOSE'
1396 )
1397 WHERE claim_id = p_claim_rec.claim_id;
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1401 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
1402 FND_MSG_PUB.add;
1403 END IF;
1404 IF OZF_DEBUG_LOW_ON THEN
1405 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1406 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1407 FND_MSG_PUB.Add;
1408 END IF;
1409 RAISE FND_API.g_exc_unexpected_error;
1410 END;
1411
1412 ELSIF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED', 'RECEIPT_OPM') THEN
1413 /*------------------------------------------------------------*
1414 | Update Claim Status to CLOSED.
1415 *------------------------------------------------------------*/
1416 /*
1417 Close_Claim(
1418 p_claim_rec => p_claim_rec
1419 ,x_return_status => l_return_status
1420 ,x_msg_data => x_msg_data
1421 ,x_msg_count => x_msg_count
1422 );
1423 IF l_return_status = FND_API.g_ret_sts_error THEN
1424 RAISE FND_API.g_exc_error;
1425 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1426 RAISE FND_API.g_exc_unexpected_error;
1427 END IF;
1428 */
1429
1430 OZF_AR_Payment_PVT.Create_AR_Write_Off(
1431 p_claim_rec => p_claim_rec
1432 ,p_deduction_type => p_deduction_type
1433 ,p_write_off_amount => p_write_off_amount
1434 ,p_gl_date => p_gl_date
1435 ,p_wo_rec_trx_id => p_wo_rec_trx_id
1436 ,x_wo_adjust_id => l_wo_adjust_id
1437 ,x_return_status => l_return_status
1438 ,x_msg_data => x_msg_data
1439 ,x_msg_count => x_msg_count
1440 );
1441 IF l_return_status = FND_API.g_ret_sts_error THEN
1442 RAISE FND_API.g_exc_error;
1443 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1444 RAISE FND_API.g_exc_unexpected_error;
1445 END IF;
1446 END IF;
1447
1448 IF OZF_DEBUG_HIGH_ON THEN
1449 OZF_Utility_PVT.debug_message(l_full_name||': end');
1450 END IF;
1451 EXCEPTION
1452 WHEN FND_API.g_exc_error THEN
1453 x_return_status := FND_API.g_ret_sts_error;
1454
1455 WHEN FND_API.g_exc_unexpected_error THEN
1456 x_return_status := FND_API.g_ret_sts_unexp_error ;
1457
1458 WHEN OTHERS THEN
1459 x_return_status := FND_API.g_ret_sts_unexp_error ;
1460 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1461 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1462 END IF;
1463
1464 END Pay_by_Write_Off;
1465
1466
1467 /*=======================================================================*
1468 | PROCEDURE
1469 | Pay_by_On_Account_Credit
1470 |
1471 | NOTES
1472 |
1473 | HISTORY
1474 | 15-MAR-2002 mchang Create.
1475 *=======================================================================*/
1476 PROCEDURE Pay_by_On_Account_Credit(
1477 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1478 ,p_deduction_type IN VARCHAR2
1479 ,p_credit_amount IN NUMBER
1480 ,p_settlement_doc_id IN NUMBER
1481
1482 ,x_return_status OUT NOCOPY VARCHAR2
1483 ,x_msg_data OUT NOCOPY VARCHAR2
1484 ,x_msg_count OUT NOCOPY NUMBER
1485 )
1486 IS
1487 l_api_version CONSTANT NUMBER := 1.0;
1488 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_On_Account_Credit';
1489 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1490 l_return_status VARCHAR2(1);
1491
1492 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
1493 , cv_root_claim_id IN NUMBER
1494 ) IS
1495 SELECT rec.amount_applied
1496 FROM ar_receivable_applications rec
1497 WHERE rec.applied_payment_schedule_id = -4
1498 AND rec.cash_receipt_id = cv_cash_receipt_id
1499 AND rec.application_ref_type = 'CLAIM'
1500 AND rec.display = 'Y'
1501 AND rec.secondary_application_ref_id = cv_root_claim_id;
1502
1503 l_old_applied_claim_amount NUMBER;
1504 l_reapply_claim_amount NUMBER;
1505
1506 BEGIN
1507 -------------------- initialize -----------------------
1508 IF OZF_DEBUG_HIGH_ON THEN
1509 OZF_Utility_PVT.debug_message(l_full_name||': start');
1510 END IF;
1511
1512 x_return_status := FND_API.g_ret_sts_success;
1513
1514 ------------------------ start -------------------------
1515 /*------------------------------------------------------------*
1516 | Update Claim Status to CLOSED.
1517 *------------------------------------------------------------*/
1518 /*
1519 Close_Claim(
1520 p_claim_rec => p_claim_rec
1521 ,x_return_status => l_return_status
1522 ,x_msg_data => x_msg_data
1523 ,x_msg_count => x_msg_count
1524 );
1525 IF l_return_status = FND_API.g_ret_sts_error THEN
1526 RAISE FND_API.g_exc_error;
1527 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1528 RAISE FND_API.g_exc_unexpected_error;
1529 END IF;
1530 */
1531
1532 IF p_deduction_type = 'RECEIPT_OPM' THEN
1533 /*------------------------------------------------------------*
1534 | Overpayment -> 1. Unapply claim investigation
1535 | 2. Apply On Account Credit
1536 *------------------------------------------------------------*/
1537 IF OZF_DEBUG_HIGH_ON THEN
1538 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation.');
1539 END IF;
1540 -- 1. Unapply claim investigation
1541 OPEN csr_old_claim_investigation(p_claim_rec.receipt_id, p_claim_rec.root_claim_id);
1542 FETCH csr_old_claim_investigation INTO l_old_applied_claim_amount;
1543 CLOSE csr_old_claim_investigation;
1544
1545 l_reapply_claim_amount := l_old_applied_claim_amount - (p_credit_amount * -1);
1546
1547 OZF_AR_PAYMENT_PVT.Unapply_Claim_Investigation(
1548 p_claim_rec => p_claim_rec
1549 ,p_reapply_amount => l_reapply_claim_amount --0
1550 ,x_return_status => l_return_status
1551 ,x_msg_data => x_msg_data
1552 ,x_msg_count => x_msg_count
1553 );
1554 IF l_return_status = FND_API.g_ret_sts_error THEN
1555 RAISE FND_API.g_exc_error;
1556 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1557 RAISE FND_API.g_exc_unexpected_error;
1558 END IF;
1559
1560 IF OZF_DEBUG_HIGH_ON THEN
1561 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply On Account Credit.');
1562 END IF;
1563 --2. Apply On Account Credit
1564 OZF_AR_PAYMENT_PVT.Apply_On_Account_Credit(
1565 p_claim_rec => p_claim_rec
1566 ,p_credit_amount => p_credit_amount
1567 ,x_return_status => l_return_status
1568 ,x_msg_data => x_msg_data
1569 ,x_msg_count => x_msg_count
1570 );
1571 IF l_return_status = FND_API.g_ret_sts_error THEN
1572 RAISE FND_API.g_exc_error;
1573 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1574 RAISE FND_API.g_exc_unexpected_error;
1575 END IF;
1576
1577 /*------------------------------------------------------------*
1578 | Update Deduction payment detail
1579 *------------------------------------------------------------*/
1580 -- Update Deduction payment detail
1581 IF OZF_DEBUG_HIGH_ON THEN
1582 OZF_Utility_PVT.debug_message('cash_receipt_id = '||p_claim_rec.receipt_id);
1583 END IF;
1584 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1585 p_api_version => l_api_version
1586 ,p_init_msg_list => FND_API.g_false
1587 ,p_commit => FND_API.g_false
1588 ,p_validation_level => FND_API.g_valid_level_full
1589 ,x_return_status => l_return_status
1590 ,x_msg_data => x_msg_data
1591 ,x_msg_count => x_msg_count
1592 ,p_claim_id => p_claim_rec.claim_id
1593 ,p_payment_method => p_claim_rec.payment_method
1594 ,p_deduction_type => p_deduction_type
1595 ,p_cash_receipt_id => p_claim_rec.receipt_id
1596 ,p_customer_trx_id => NULL --p_claim_rec.payment_reference_id
1597 ,p_adjust_id => NULL
1598 ,p_settlement_doc_id => p_settlement_doc_id
1599 ,p_settlement_mode => 'MASS_SETTLEMENT'
1600 );
1601 IF l_return_status = FND_API.g_ret_sts_error THEN
1602 RAISE FND_API.g_exc_error;
1603 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1604 RAISE FND_API.g_exc_unexpected_error;
1605 END IF;
1606
1607 ELSE
1608 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1609 FND_MESSAGE.set_name('AMS', 'AMS_AR_PAYMENT_NOTMATCH');
1610 FND_MSG_PUB.add;
1611 END IF;
1612 RAISE FND_API.g_exc_error;
1613 END IF;
1614
1615 IF OZF_DEBUG_HIGH_ON THEN
1616 OZF_Utility_PVT.debug_message(l_full_name||': end');
1617 END IF;
1618 EXCEPTION
1619 WHEN FND_API.g_exc_error THEN
1620 x_return_status := FND_API.g_ret_sts_error;
1621
1622 WHEN FND_API.g_exc_unexpected_error THEN
1623 x_return_status := FND_API.g_ret_sts_unexp_error ;
1624
1625 WHEN OTHERS THEN
1626 x_return_status := FND_API.g_ret_sts_unexp_error ;
1627 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1628 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1629 END IF;
1630
1631 END Pay_by_On_Account_Credit;
1632
1633
1634 /*=======================================================================*
1635 | PROCEDURE
1636 | Break_Mass_Settlement
1637 |
1638 | NOTES
1639 |
1640 | HISTORY
1641 | 20-OCT-2003 mchang Create.
1642 *=======================================================================*/
1643 PROCEDURE Break_Mass_Settlement(
1644 p_group_claim_id IN NUMBER,
1645 p_settlement_type IN VARCHAR2,
1646 x_return_status OUT NOCOPY VARCHAR2,
1647 x_msg_data OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER
1649 )
1650 IS
1651 l_api_version CONSTANT NUMBER := 1.0;
1652 l_api_name CONSTANT VARCHAR2(30) := 'Break_Mass_Settlement';
1653 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1654 l_return_status VARCHAR2(1);
1655 l_source_object_id NUMBER;
1656 l_wo_rec_trx_id NUMBER;
1657 ---
1658
1659 CURSOR csr_claims( cv_group_claim_id IN NUMBER
1660 , cv_claim_class IN VARCHAR2
1661 ) IS
1662 SELECT claim_id
1663 , claim_number
1664 , claim_class
1665 , receipt_id
1666 , receipt_number
1667 , source_object_id
1668 , source_object_number
1669 , amount_remaining
1670 FROM ozf_claims_all
1671 WHERE group_claim_id = cv_group_claim_id
1672 AND claim_class = cv_claim_class
1673 ORDER BY claim_date, group_claim_id;
1674
1675
1676 CURSOR csr_settle_docs_group(cv_group_claim_id IN NUMBER) IS
1677 SELECT settlement_doc_id
1678 , payment_method
1679 , settlement_id
1680 , settlement_number
1681 , settlement_type_id
1682 , settlement_amount
1683 , gl_date
1684 , wo_rec_trx_id
1685 FROM ozf_settlement_docs_all
1686 WHERE claim_id = cv_group_claim_id;
1687 -- add addtional order by clause for mass settlement ordering rule criteria
1688
1689 CURSOR csr_get_source_object_id(cv_claim_id IN NUMBER) IS
1690 SELECT source_object_id
1691 FROM ozf_claims
1692 WHERE claim_id = cv_claim_id;
1693
1694 TYPE l_csr_claim_tbl IS TABLE OF csr_claims%ROWTYPE
1695 INDEX BY BINARY_INTEGER;
1696 --l_claim_group_tbl l_csr_claim_tbl;
1697 --l_claim_netting_tbl l_csr_claim_tbl;
1698 l_claim_group_tbl OZF_Claim_PVT.claim_tbl_type;
1699 l_claim_netting_tbl OZF_Claim_PVT.claim_tbl_type;
1700 l_settle_doc_group_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1701 l_settle_doc_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1702 l_settle_doc_tbl2 OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1703 l_settle_doc_id_tbl JTF_NUMBER_TABLE;
1704 l_group_claim_line_rec OZF_Claim_line_Pvt.claim_line_rec_type;
1705 l_group_claim_line_tbl OZF_Claim_line_Pvt.claim_line_tbl_type;
1706 l_idx_netting_claim NUMBER;
1707 l_idx_settle_doc_group NUMBER;
1708 l_idx NUMBER := 1;
1709 l_idx_setl_doc NUMBER := 1;
1710 l_idx_setl_doc2 NUMBER := 1;
1711 l_amount_settled NUMBER;
1712 i BINARY_INTEGER;
1713 l_err_idx NUMBER;
1714
1715 BEGIN
1716 -------------------- initialize -----------------------
1717 SAVEPOINT Break_Mass_Settlement;
1718
1719 IF OZF_DEBUG_HIGH_ON THEN
1720 OZF_Utility_PVT.debug_message(l_full_name||': start');
1721 END IF;
1722
1723 x_return_status := FND_API.G_RET_STS_SUCCESS;
1724
1725 ------------------------ start -------------------------
1726 IF OZF_DEBUG_HIGH_ON THEN
1727 OZF_Utility_PVT.debug_message('Settlement Type = '||p_settlement_type);
1728 END IF;
1729
1730 IF p_settlement_type <> 'NETTING' THEN
1731 l_idx := 1;
1732 OPEN csr_settle_docs_group(p_group_claim_id);
1733 LOOP
1734 FETCH csr_settle_docs_group INTO l_settle_doc_group_tbl(l_idx).settlement_doc_id
1735 , l_settle_doc_group_tbl(l_idx).payment_method
1736 , l_settle_doc_group_tbl(l_idx).settlement_id
1737 , l_settle_doc_group_tbl(l_idx).settlement_number
1738 , l_settle_doc_group_tbl(l_idx).settlement_type_id
1739 , l_settle_doc_group_tbl(l_idx).settlement_amount
1740 , l_settle_doc_group_tbl(l_idx).gl_date
1741 , l_settle_doc_group_tbl(l_idx).wo_rec_trx_id;
1742 EXIT WHEN csr_settle_docs_group%NOTFOUND;
1743 l_idx := l_idx + 1;
1744 END LOOP;
1745 CLOSE csr_settle_docs_group;
1746 END IF;
1747
1748
1749 l_idx := 1;
1750 IF p_settlement_type = 'NETTING' THEN
1751 OPEN csr_claims(p_group_claim_id, 'DEDUCTION');
1752 LOOP
1753 FETCH csr_claims INTO l_claim_group_tbl(l_idx).claim_id
1754 , l_claim_group_tbl(l_idx).claim_number
1755 , l_claim_group_tbl(l_idx).claim_class
1756 , l_claim_group_tbl(l_idx).receipt_id
1757 , l_claim_group_tbl(l_idx).receipt_number
1758 , l_claim_group_tbl(l_idx).source_object_id
1759 , l_claim_group_tbl(l_idx).source_object_number
1760 , l_claim_group_tbl(l_idx).amount_remaining;
1761 EXIT WHEN csr_claims%NOTFOUND;
1762 l_idx := l_idx + 1;
1763 END LOOP;
1764 CLOSE csr_claims;
1765 ELSE
1766 OPEN csr_claims(p_group_claim_id, p_settlement_type);
1767 LOOP
1768 FETCH csr_claims INTO l_claim_group_tbl(l_idx).claim_id
1769 , l_claim_group_tbl(l_idx).claim_number
1770 , l_claim_group_tbl(l_idx).claim_class
1771 , l_claim_group_tbl(l_idx).receipt_id
1772 , l_claim_group_tbl(l_idx).receipt_number
1773 , l_claim_group_tbl(l_idx).source_object_id
1774 , l_claim_group_tbl(l_idx).source_object_number
1775 , l_claim_group_tbl(l_idx).amount_remaining;
1776 EXIT WHEN csr_claims%NOTFOUND;
1777 l_idx := l_idx + 1;
1778 END LOOP;
1779 CLOSE csr_claims;
1780 END IF;
1781
1782 l_idx := 1;
1783 IF p_settlement_type IN ('DEDUCTION', 'NETTING') THEN
1784 OPEN csr_claims(p_group_claim_id, 'OVERPAYMENT');
1785 LOOP
1786 FETCH csr_claims INTO l_claim_netting_tbl(l_idx).claim_id
1787 , l_claim_netting_tbl(l_idx).claim_number
1788 , l_claim_netting_tbl(l_idx).claim_class
1789 , l_claim_netting_tbl(l_idx).receipt_id
1790 , l_claim_netting_tbl(l_idx).receipt_number
1791 , l_claim_netting_tbl(l_idx).source_object_id
1792 , l_claim_netting_tbl(l_idx).source_object_number
1793 , l_claim_netting_tbl(l_idx).amount_remaining;
1794 EXIT WHEN csr_claims%NOTFOUND;
1795 l_idx := l_idx + 1;
1796 END LOOP;
1797 CLOSE csr_claims;
1798 ELSIF p_settlement_type = 'OVERPAYMENT' THEN
1799 OPEN csr_claims(p_group_claim_id, 'DEDUCTION');
1800 LOOP
1801 FETCH csr_claims INTO l_claim_netting_tbl(l_idx).claim_id
1802 , l_claim_netting_tbl(l_idx).claim_number
1803 , l_claim_netting_tbl(l_idx).claim_class
1804 , l_claim_netting_tbl(l_idx).receipt_id
1805 , l_claim_netting_tbl(l_idx).receipt_number
1806 , l_claim_netting_tbl(l_idx).source_object_id
1807 , l_claim_netting_tbl(l_idx).source_object_number
1808 , l_claim_netting_tbl(l_idx).amount_remaining;
1809 EXIT WHEN csr_claims%NOTFOUND;
1810 l_idx := l_idx + 1;
1811 END LOOP;
1812 CLOSE csr_claims;
1813 END IF;
1814
1815 l_idx_netting_claim := l_claim_netting_tbl.FIRST;
1816 l_idx_settle_doc_group := l_settle_doc_group_tbl.FIRST;
1817
1818 l_idx_setl_doc := 1;
1819 i := l_claim_group_tbl.FIRST;
1820 IF i IS NOT NULL THEN
1821 LOOP
1822 l_group_claim_line_tbl(i).claim_id := p_group_claim_id;
1823 l_group_claim_line_tbl(i).claim_currency_amount := l_claim_group_tbl(i).amount_remaining;
1824 l_group_claim_line_tbl(i).payment_reference_id := l_claim_group_tbl(i).claim_id;
1825 l_group_claim_line_tbl(i).payment_reference_number := l_claim_group_tbl(i).claim_number;
1826 l_group_claim_line_tbl(i).payment_method := 'MASS_SETTLEMENT';
1827
1828 --------------------------------------
1829 -- Assign Settlement Docs for Claim --
1830 --------------------------------------
1831 l_amount_settled := l_claim_group_tbl(i).amount_remaining;
1832
1833 WHILE ABS(l_amount_settled) > 0 LOOP
1834 -- Get settlement docs from netting claims
1835 WHILE l_idx_netting_claim IS NOT NULL AND
1836 l_amount_settled <> 0 LOOP
1837 l_settle_doc_tbl(l_idx_setl_doc).claim_id := l_claim_group_tbl(i).claim_id;
1838 l_settle_doc_tbl(l_idx_setl_doc).settlement_id := l_claim_netting_tbl(l_idx_netting_claim).receipt_id;
1839 --l_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := ??;
1840 l_settle_doc_tbl(l_idx_setl_doc).settlement_number := l_claim_netting_tbl(l_idx_netting_claim).receipt_number;
1841 l_settle_doc_tbl(l_idx_setl_doc).payment_method := l_claim_netting_tbl(l_idx_netting_claim).claim_class;
1842 l_settle_doc_tbl(l_idx_setl_doc).payment_reference_id := l_claim_netting_tbl(l_idx_netting_claim).claim_id;
1843 l_settle_doc_tbl(l_idx_setl_doc).payment_reference_number := l_claim_netting_tbl(l_idx_netting_claim).claim_number;
1844 l_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
1845 l_settle_doc_tbl(l_idx_setl_doc).group_claim_id := p_group_claim_id;
1846
1847 l_settle_doc_tbl2(l_idx_setl_doc2).claim_id := l_claim_netting_tbl(l_idx_netting_claim).claim_id;
1848 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_id := l_claim_group_tbl(i).receipt_id;
1849 --l_settle_doc_tbl2(l_idx_setl_doc2).settlement_type_id := ??;
1850 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_number := l_claim_group_tbl(i).receipt_number;
1851 l_settle_doc_tbl2(l_idx_setl_doc2).payment_method := l_claim_group_tbl(i).claim_class;
1852 l_settle_doc_tbl2(l_idx_setl_doc2).payment_reference_id := l_claim_group_tbl(i).claim_id;
1853 l_settle_doc_tbl2(l_idx_setl_doc2).payment_reference_number := l_claim_group_tbl(i).claim_number;
1854 l_settle_doc_tbl2(l_idx_setl_doc2).payment_status := 'PENDING';
1855 l_settle_doc_tbl2(l_idx_setl_doc2).group_claim_id := p_group_claim_id;
1856
1857
1858 IF l_claim_netting_tbl(l_idx_netting_claim).amount_remaining = 0 THEN
1859 -- Bug4386869: Amount is already utilized
1860
1861 l_settle_doc_tbl2.delete(l_idx_setl_doc2);
1862 l_idx_setl_doc2 := l_idx_setl_doc2 - 1;
1863 EXIT WHEN l_idx_netting_claim = l_claim_netting_tbl.LAST;
1864 l_idx_netting_claim := l_claim_netting_tbl.NEXT(l_idx_netting_claim);
1865
1866 ELSIF ABS(l_amount_settled) >= ABS(l_claim_netting_tbl(l_idx_netting_claim).amount_remaining) THEN
1867 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_claim_netting_tbl(l_idx_netting_claim).amount_remaining;
1868
1869 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_amount:= l_settle_doc_tbl(l_idx_setl_doc).settlement_amount * -1;
1870
1871 l_amount_settled := l_amount_settled
1872 - (l_claim_netting_tbl(l_idx_netting_claim).amount_remaining * -1);
1873
1874
1875 l_claim_netting_tbl(l_idx_netting_claim).amount_remaining := 0;
1876
1877 IF OZF_DEBUG_HIGH_ON THEN
1878 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1879 l_group_claim_line_tbl(i).payment_reference_number||
1880 ' ... '||
1881 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1882 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_reference_number||
1883 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1884 ')'
1885 );
1886 END IF;
1887
1888 l_idx_setl_doc := l_idx_setl_doc + 1;
1889 l_idx_setl_doc2 := l_idx_setl_doc2 + 1;
1890
1891 EXIT WHEN l_idx_netting_claim = l_claim_netting_tbl.LAST;
1892 l_idx_netting_claim := l_claim_netting_tbl.NEXT(l_idx_netting_claim);
1893 l_idx_setl_doc2 := 1;
1894 ELSE
1895 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_amount_settled * -1;
1896
1897 l_settle_doc_tbl2(l_idx_setl_doc2).settlement_amount:= l_settle_doc_tbl(l_idx_setl_doc).settlement_amount * -1;
1898
1899 l_claim_netting_tbl(l_idx_netting_claim).amount_remaining := l_claim_netting_tbl(l_idx_netting_claim).amount_remaining
1900 - (l_amount_settled * -1);
1901
1902
1903 l_amount_settled := 0;
1904
1905 IF OZF_DEBUG_HIGH_ON THEN
1906 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1907 l_group_claim_line_tbl(i).payment_reference_number||
1908 ' ... '||
1909 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1910 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_reference_number||
1911 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1912 ')'
1913 );
1914 END IF;
1915
1916 l_idx_setl_doc := l_idx_setl_doc + 1;
1917 l_idx_setl_doc2 := l_idx_setl_doc2 + 1;
1918
1919 EXIT WHEN l_amount_settled = 0;
1920 EXIT WHEN l_idx_netting_claim = l_claim_netting_tbl.LAST;
1921 --l_idx_netting_claim := l_claim_netting_tbl.NEXT(l_idx_netting_claim);
1922 END IF;
1923 END lOOP;
1924
1925 -- Get settlement docs from open/new transactions
1926 WHILE l_idx_settle_doc_group IS NOT NULL AND
1927 l_amount_settled <> 0 LOOP
1928 l_settle_doc_tbl(l_idx_setl_doc).claim_id := l_claim_group_tbl(i).claim_id;
1929 l_settle_doc_tbl(l_idx_setl_doc).settlement_id := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_id;
1930 l_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_type_id;
1931 l_settle_doc_tbl(l_idx_setl_doc).settlement_number := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_number;
1932 l_settle_doc_tbl(l_idx_setl_doc).payment_method := l_settle_doc_group_tbl(l_idx_settle_doc_group).payment_method;
1933 l_settle_doc_tbl(l_idx_setl_doc).gl_date := l_settle_doc_group_tbl(l_idx_settle_doc_group).gl_date;
1934 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;
1935 l_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
1936 l_settle_doc_tbl(l_idx_setl_doc).group_claim_id := p_group_claim_id;
1937
1938 IF l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount = 0 THEN
1939 -- Bug4386869: Amount is already utilized
1940
1941 EXIT WHEN l_idx_settle_doc_group = l_settle_doc_group_tbl.LAST;
1942 l_idx_settle_doc_group := l_settle_doc_group_tbl.NEXT(l_idx_settle_doc_group);
1943
1944 ELSIF ABS(l_amount_settled) >= ABS(l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount) THEN
1945 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount;
1946
1947 l_amount_settled := l_amount_settled + l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount;
1948
1949 l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount := 0;
1950
1951 IF OZF_DEBUG_HIGH_ON THEN
1952 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1953 l_group_claim_line_tbl(i).payment_reference_number||
1954 ' ... '||
1955 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1956 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_method||
1957 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1958 ')'
1959 );
1960 END IF;
1961
1962 l_idx_setl_doc := l_idx_setl_doc + 1;
1963
1964 EXIT WHEN l_idx_settle_doc_group = l_settle_doc_group_tbl.LAST;
1965 l_idx_settle_doc_group := l_settle_doc_group_tbl.NEXT(l_idx_settle_doc_group);
1966 ELSE
1967
1968 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount := l_amount_settled * -1;
1969
1970 l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount := l_settle_doc_group_tbl(l_idx_settle_doc_group).settlement_amount
1971 + l_amount_settled;
1972
1973 l_amount_settled := 0;
1974
1975 IF OZF_DEBUG_HIGH_ON THEN
1976 OZF_Utility_PVT.debug_message('('||l_idx_setl_doc||')'||
1977 l_group_claim_line_tbl(i).payment_reference_number||
1978 ' ... '||
1979 l_settle_doc_tbl(l_idx_setl_doc).settlement_amount||
1980 ' >>> '||l_settle_doc_tbl(l_idx_setl_doc).payment_method||
1981 '('||l_settle_doc_tbl(l_idx_setl_doc).settlement_number||
1982 ')'
1983 );
1984 END IF;
1985
1986 l_idx_setl_doc := l_idx_setl_doc + 1;
1987
1988 EXIT WHEN l_amount_settled = 0;
1989 EXIT WHEN l_idx_settle_doc_group = l_settle_doc_group_tbl.LAST;
1990 l_idx_settle_doc_group := l_settle_doc_group_tbl.NEXT(l_idx_settle_doc_group);
1991 END IF;
1992
1993 END lOOP;
1994 END LOOP;
1995 EXIT WHEN i = l_claim_group_tbl.LAST;
1996 i := l_claim_group_tbl.NEXT(i);
1997 END LOOP;
1998 END IF;
1999 --//Bug 5345095
2000 i := l_settle_doc_tbl.FIRST;
2001 IF i IS NOT NULL THEN
2002 LOOP
2003 IF(l_settle_doc_tbl(i).payment_method = 'WRITE_OFF') THEN
2004 OPEN csr_get_source_object_id(l_settle_doc_tbl(i).claim_id);
2005 FETCH csr_get_source_object_id INTO l_source_object_id;
2006 CLOSE csr_get_source_object_id;
2007
2008
2009 IF(l_source_object_id IS NULL) THEN
2010 l_wo_rec_trx_id := l_settle_doc_tbl(i).wo_rec_trx_id;
2011 ELSE
2012 l_wo_rec_trx_id := l_settle_doc_tbl(i).settlement_type_id;
2013 END IF;
2014
2015 IF l_wo_rec_trx_id IS NOT NULL THEN
2016 l_settle_doc_tbl(i).wo_rec_trx_id := l_wo_rec_trx_id;
2017 l_settle_doc_tbl(i).settlement_type_id := null;
2018 END IF;
2019 END IF;
2020
2021 EXIT WHEN i = l_settle_doc_tbl.LAST;
2022 i := l_settle_doc_tbl.NEXT(i);
2023 END LOOP;
2024 END IF;
2025
2026
2027 IF OZF_DEBUG_HIGH_ON THEN
2028 i := l_group_claim_line_tbl.FIRST;
2029 IF i IS NOT NULL THEN
2030 OZF_Utility_PVT.debug_message('--- Mass Settlement Claims ---');
2031 LOOP
2032 OZF_Utility_PVT.debug_message('--- '||i||' ---');
2033 OZF_Utility_PVT.debug_message('l_group_claim_line_tbl('||i||').claim_currency_amount = '||l_group_claim_line_tbl(i).claim_currency_amount);
2034 OZF_Utility_PVT.debug_message('l_group_claim_line_tbl('||i||').payment_reference_number = '||l_group_claim_line_tbl(i).payment_reference_number);
2035 EXIT WHEN i = l_group_claim_line_tbl.LAST;
2036 i := l_group_claim_line_tbl.NEXT(i);
2037 END LOOP;
2038 END IF;
2039 END IF;
2040
2041 -----------------------------------------------
2042 -- Create break Claim Lines for Master Claim --
2043 -----------------------------------------------
2044 IF l_group_claim_line_tbl.COUNT IS NOT NULL AND
2045 l_group_claim_line_tbl.COUNT > 0 THEN
2046 OZF_Claim_Line_PVT.Create_Claim_Line_Tbl(
2047 p_api_version => l_api_version
2048 ,p_init_msg_list => FND_API.g_false
2049 ,p_commit => FND_API.g_false
2050 ,p_validation_level => FND_API.g_valid_level_full
2051 ,x_return_status => l_return_status
2052 ,x_msg_data => x_msg_data
2053 ,x_msg_count => x_msg_count
2054 ,p_claim_line_tbl => l_group_claim_line_tbl
2055 ,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
2056 ,x_error_index => l_err_idx
2057 );
2058 IF l_return_status = FND_API.g_ret_sts_error THEN
2059 RAISE FND_API.g_exc_error;
2060 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2061 RAISE FND_API.g_exc_unexpected_error;
2062 END IF;
2063 END IF;
2064
2065
2066 IF OZF_DEBUG_HIGH_ON THEN
2067 i := l_settle_doc_tbl.FIRST;
2068 IF i IS NOT NULL THEN
2069 OZF_Utility_PVT.debug_message('--- Mass Settlement Payment Details ---');
2070 LOOP
2071 OZF_Utility_PVT.debug_message('--- '||i||' ---');
2072 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').claim_id = '||l_settle_doc_tbl(i).claim_id);
2073 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').settlement_id = '||l_settle_doc_tbl(i).settlement_id);
2074 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').settlement_number = '||l_settle_doc_tbl(i).settlement_number);
2075 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').settlement_amount = '||l_settle_doc_tbl(i).settlement_amount);
2076 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').payment_method = '||l_settle_doc_tbl(i).payment_method);
2077 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').payment_reference_id = '||l_settle_doc_tbl(i).payment_reference_id);
2078 OZF_Utility_PVT.debug_message('l_settle_doc_tbl('||i||').payment_reference_number = '||l_settle_doc_tbl(i).payment_reference_number);
2079 EXIT WHEN i = l_settle_doc_tbl.LAST;
2080 i := l_settle_doc_tbl.NEXT(i);
2081 END LOOP;
2082 END IF;
2083 i := l_settle_doc_tbl2.FIRST;
2084 IF i IS NOT NULL THEN
2085 OZF_Utility_PVT.debug_message('--- Mass Settlement Group Payment Details ---');
2086 LOOP
2087 OZF_Utility_PVT.debug_message('--- '||i||' ---');
2088 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').claim_id = '||l_settle_doc_tbl2(i).claim_id);
2089 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').settlement_id = '||l_settle_doc_tbl2(i).settlement_id);
2090 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').settlement_number = '||l_settle_doc_tbl2(i).settlement_number);
2091 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').settlement_amount = '||l_settle_doc_tbl2(i).settlement_amount);
2092 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').payment_method = '||l_settle_doc_tbl2(i).payment_method);
2093 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').payment_reference_id = '||l_settle_doc_tbl2(i).payment_reference_id);
2094 OZF_Utility_PVT.debug_message('l_settle_doc_tbl2('||i||').payment_reference_number = '||l_settle_doc_tbl2(i).payment_reference_number);
2095 EXIT WHEN i = l_settle_doc_tbl2.LAST;
2096 i := l_settle_doc_tbl2.NEXT(i);
2097 END LOOP;
2098 END IF;
2099 END IF;
2100
2101 ----------------------------------
2102 -- Create break Settlement Docs --
2103 ----------------------------------
2104 IF l_settle_doc_tbl.COUNT IS NOT NULL AND
2105 l_settle_doc_tbl.COUNT > 0 THEN
2106 OZF_Settlement_Doc_PVT.Create_Settlement_Doc_Tbl(
2107 p_api_version_number => l_api_version,
2108 p_init_msg_list => FND_API.G_FALSE,
2109 p_commit => FND_API.G_FALSE,
2110 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2111 x_return_status => l_return_status,
2112 x_msg_count => x_msg_count,
2113 x_msg_data => x_msg_data,
2114 p_settlement_doc_tbl => l_settle_doc_tbl,
2115 x_settlement_doc_id_tbl => l_settle_doc_id_tbl
2116 );
2117 IF l_return_status = FND_API.g_ret_sts_error THEN
2118 RAISE FND_API.g_exc_error;
2119 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2120 RAISE FND_API.g_exc_unexpected_error;
2121 END IF;
2122 END IF;
2123
2124 IF l_settle_doc_tbl2.COUNT IS NOT NULL AND
2125 l_settle_doc_tbl2.COUNT > 0 THEN
2126 OZF_Settlement_Doc_PVT.Create_Settlement_Doc_Tbl(
2127 p_api_version_number => l_api_version,
2128 p_init_msg_list => FND_API.G_FALSE,
2129 p_commit => FND_API.G_FALSE,
2130 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2131 x_return_status => l_return_status,
2132 x_msg_count => x_msg_count,
2133 x_msg_data => x_msg_data,
2134 p_settlement_doc_tbl => l_settle_doc_tbl2,
2135 x_settlement_doc_id_tbl => l_settle_doc_id_tbl
2136 );
2137 IF l_return_status = FND_API.g_ret_sts_error THEN
2138 RAISE FND_API.g_exc_error;
2139 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2140 RAISE FND_API.g_exc_unexpected_error;
2141 END IF;
2142 END IF;
2143
2144 IF OZF_DEBUG_HIGH_ON THEN
2145 OZF_Utility_PVT.debug_message(l_full_name||': end');
2146 END IF;
2147
2148 EXCEPTION
2149 WHEN FND_API.g_exc_error THEN
2150 ROLLBACK TO Break_Mass_Settlement;
2151 x_return_status := FND_API.g_ret_sts_error;
2152 FND_MSG_PUB.count_and_get (
2153 p_encoded => FND_API.g_false
2154 ,p_count => x_msg_count
2155 ,p_data => x_msg_data
2156 );
2157
2158 WHEN FND_API.g_exc_unexpected_error THEN
2159 ROLLBACK TO Break_Mass_Settlement;
2160 x_return_status := FND_API.g_ret_sts_unexp_error ;
2161 FND_MSG_PUB.count_and_get (
2162 p_encoded => FND_API.g_false
2163 ,p_count => x_msg_count
2164 ,p_data => x_msg_data
2165 );
2166
2167 WHEN OTHERS THEN
2168 ROLLBACK TO Break_Mass_Settlement;
2169 x_return_status := FND_API.g_ret_sts_unexp_error ;
2170 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2171 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2172 END IF;
2173 FND_MSG_PUB.count_and_get(
2174 p_encoded => FND_API.g_false
2175 ,p_count => x_msg_count
2176 ,p_data => x_msg_data
2177 );
2178
2179 END Break_Mass_Settlement;
2180
2181
2182 /*=======================================================================*
2183 | PROCEDURE
2184 | Complete_Mass_Settlement
2185 |
2186 | NOTES
2187 |
2188 | HISTORY
2189 | 20-OCT-2003 mchang Create.
2190 *=======================================================================*/
2191 PROCEDURE Complete_Mass_Settlement(
2192 p_group_claim_id IN NUMBER,
2193 x_claim_tbl OUT NOCOPY OZF_Claim_PVT.claim_tbl_type,
2194 x_return_status OUT NOCOPY VARCHAR2,
2195 x_msg_data OUT NOCOPY VARCHAR2,
2196 x_msg_count OUT NOCOPY NUMBER
2197 )
2198 IS
2199 l_api_version CONSTANT NUMBER := 1.0;
2200 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Mass_Settlement';
2201 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2202 l_return_status VARCHAR2(1);
2203 ---
2204
2205 CURSOR csr_claims_group( cv_group_claim_id IN NUMBER) IS
2206 SELECT claim_id
2207 , claim_number
2208 , object_version_number
2209 FROM ozf_claims_all
2210 WHERE group_claim_id = cv_group_claim_id;
2211
2212 -- Fix for 5376466
2213 CURSOR csr_sysparam_defaults IS
2214 SELECT gl_date_type
2215 FROM ozf_sys_parameters;
2216
2217 i NUMBER := 1;
2218 l_claim_obj_ver_num NUMBER;
2219 l_gl_date_type VARCHAR2(30);
2220
2221 l_claim_pvt_rec OZF_Claim_PVT.claim_rec_type;
2222
2223
2224 BEGIN
2225 -------------------- initialize -----------------------
2226 SAVEPOINT Complete_Mass_Settlement;
2227
2228 IF OZF_DEBUG_HIGH_ON THEN
2229 OZF_Utility_PVT.debug_message(l_full_name||': start');
2230 END IF;
2231
2232 x_return_status := FND_API.G_RET_STS_SUCCESS;
2233
2234 ------------------------ start -------------------------
2235
2236 OPEN csr_sysparam_defaults;
2237 FETCH csr_sysparam_defaults INTO l_gl_date_type;
2238 CLOSE csr_sysparam_defaults;
2239
2240 OPEN csr_claims_group(p_group_claim_id);
2241 LOOP
2242 FETCH csr_claims_group INTO x_claim_tbl(i).claim_id
2243 , x_claim_tbl(i).claim_number
2244 , x_claim_tbl(i).object_version_number;
2245 EXIT WHEN csr_claims_group%NOTFOUND;
2246
2247 IF l_gl_date_type IS NULL THEN
2248 x_claim_tbl(i).gl_date := sysdate;
2249 END IF;
2250
2251 x_claim_tbl(i).payment_method := 'MASS_SETTLEMENT';
2252 x_claim_tbl(i).status_code := 'COMPLETE';
2253 x_claim_tbl(i).user_status_id := OZF_UTILITY_PVT.get_default_user_status(
2254 'OZF_CLAIM_STATUS'
2255 ,'COMPLETE'
2256 );
2257 i := i + 1;
2258 END LOOP;
2259 CLOSE csr_claims_group;
2260
2261
2262
2263 i := x_claim_tbl.FIRST;
2264 IF i IS NOT NULL THEN
2265 LOOP
2266 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2267 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2268 FND_MESSAGE.Set_Token('TEXT', x_claim_tbl(i).claim_number);
2269 FND_MSG_PUB.Add;
2270 END IF;
2271
2272 OZF_Claim_PVT.Update_Claim (
2273 p_api_version => l_api_version
2274 ,p_init_msg_list => FND_API.G_FALSE
2275 ,p_commit => FND_API.G_FALSE
2276 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2277 ,x_return_status => l_return_status
2278 ,x_msg_data => x_msg_data
2279 ,x_msg_count => x_msg_count
2280 ,p_claim => x_claim_tbl(i)
2281 ,p_event => 'UPDATE'
2282 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
2283 ,x_object_version_number => l_claim_obj_ver_num
2284 );
2285 IF l_return_status = FND_API.g_ret_sts_error THEN
2286 RAISE FND_API.g_exc_error;
2287 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2288 RAISE FND_API.g_exc_unexpected_error;
2289 END IF;
2290 x_claim_tbl(i).object_version_number := l_claim_obj_ver_num;
2291 EXIT WHEN i = x_claim_tbl.LAST;
2292 i := x_claim_tbl.NEXT(i);
2293 END LOOP;
2294 END IF;
2295
2296 IF OZF_DEBUG_HIGH_ON THEN
2297 OZF_Utility_PVT.debug_message(l_full_name||': end');
2298 END IF;
2299
2300 EXCEPTION
2301 WHEN FND_API.g_exc_error THEN
2302 ROLLBACK TO Complete_Mass_Settlement;
2303 x_return_status := FND_API.g_ret_sts_error;
2304 FND_MSG_PUB.count_and_get (
2305 p_encoded => FND_API.g_false
2306 ,p_count => x_msg_count
2307 ,p_data => x_msg_data
2308 );
2309
2310 WHEN FND_API.g_exc_unexpected_error THEN
2311 ROLLBACK TO Complete_Mass_Settlement;
2312 x_return_status := FND_API.g_ret_sts_unexp_error ;
2313 FND_MSG_PUB.count_and_get (
2314 p_encoded => FND_API.g_false
2315 ,p_count => x_msg_count
2316 ,p_data => x_msg_data
2317 );
2318
2319 WHEN OTHERS THEN
2320 ROLLBACK TO Complete_Mass_Settlement;
2321 x_return_status := FND_API.g_ret_sts_unexp_error ;
2322 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2323 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2324 END IF;
2325 FND_MSG_PUB.count_and_get(
2326 p_encoded => FND_API.g_false
2327 ,p_count => x_msg_count
2328 ,p_data => x_msg_data
2329 );
2330
2331 END Complete_Mass_Settlement;
2332
2333
2334 /*=======================================================================*
2335 | PROCEDURE
2336 | Approve_Mass_Settlement
2337 |
2338 | NOTES
2339 |
2340 | HISTORY
2341 | 20-OCT-2003 mchang Create.
2342 *=======================================================================*/
2343 PROCEDURE Approve_Mass_Settlement(
2344 p_group_claim_id IN NUMBER,
2345 p_complete_claim_group_tbl IN OZF_Claim_PVT.claim_tbl_type,
2346 x_return_status OUT NOCOPY VARCHAR2,
2347 x_msg_data OUT NOCOPY VARCHAR2,
2348 x_msg_count OUT NOCOPY NUMBER
2349 )
2350 IS
2351 l_api_version CONSTANT NUMBER := 1.0;
2352 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Mass_Settlement';
2353 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2354 l_return_status VARCHAR2(1);
2355 ---
2356 l_complete_claim_group_tbl OZF_Claim_PVT.claim_tbl_type := p_complete_claim_group_tbl;
2357 l_claim_pvt_rec OZF_Claim_PVT.claim_rec_type;
2358 l_claim_obj_ver_num NUMBER;
2359 i BINARY_INTEGER;
2360 l_group_claim_obj_ver NUMBER;
2361 l_orig_status_id NUMBER;
2362 l_new_status_id NUMBER;
2363 l_reject_status_id NUMBER;
2364 l_owner_id NUMBER;
2365 l_appr_req VARCHAR2(1);
2366
2367 CURSOR csr_claim_obj_ver(cv_claim_id IN NUMBER) IS
2368 SELECT object_version_number
2369 FROM ozf_claims_all
2370 WHERE claim_id = cv_claim_id;
2371
2372 CURSOR csr_mass_setl_appr_req IS
2373 SELECT NVL(attr_available_flag, 'N')
2374 FROM ams_custom_setup_attr
2375 WHERE object_attribute = 'APPR'
2376 AND custom_setup_id =( SELECT custom_setup_id
2377 FROM ams_custom_setups_b
2378 WHERE activity_type_code = 'GROUP'
2379 AND object_type = 'CLAM'
2380 AND enabled_flag = 'Y' );
2381
2382 BEGIN
2383 -------------------- initialize -----------------------
2384 SAVEPOINT Approve_Mass_Settlement;
2385
2386 IF OZF_DEBUG_HIGH_ON THEN
2387 OZF_Utility_PVT.debug_message(l_full_name||': start');
2388 END IF;
2389
2390 x_return_status := FND_API.G_RET_STS_SUCCESS;
2391
2392 ------------------------ start -------------------------
2393
2394 OPEN csr_mass_setl_appr_req;
2395 FETCH csr_mass_setl_appr_req INTO l_appr_req;
2396 CLOSE csr_mass_setl_appr_req;
2397
2398 i := p_complete_claim_group_tbl.FIRST;
2399 IF i IS NOT NULL THEN
2400 LOOP
2401 l_claim_pvt_rec := p_complete_claim_group_tbl(i);
2402 OPEN csr_claim_obj_ver(p_complete_claim_group_tbl(i).claim_id);
2403 FETCH csr_claim_obj_ver INTO l_claim_pvt_rec.object_version_number;
2404 CLOSE csr_claim_obj_ver;
2405
2406 l_claim_pvt_rec.user_status_id := OZF_UTILITY_PVT.get_default_user_status(
2407 'OZF_CLAIM_STATUS'
2408 ,'PENDING_APPROVAL'
2409 );
2410 l_claim_pvt_rec.status_code := 'PENDING_APPROVAL';
2411
2412 OZF_Claim_PVT.Update_Claim (
2413 p_api_version => l_api_version
2414 ,p_init_msg_list => FND_API.G_FALSE
2415 ,p_commit => FND_API.G_FALSE
2416 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2417 ,x_return_status => l_return_status
2418 ,x_msg_data => x_msg_data
2419 ,x_msg_count => x_msg_count
2420 ,p_claim => l_claim_pvt_rec --l_complete_claim_group_tbl(i)
2421 ,p_event => 'UPDATE'
2422 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
2423 ,x_object_version_number => l_claim_obj_ver_num
2424 );
2425 IF l_return_status = FND_API.g_ret_sts_error THEN
2426 RAISE FND_API.g_exc_error;
2427 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2428 RAISE FND_API.g_exc_unexpected_error;
2429 END IF;
2430 EXIT WHEN i = p_complete_claim_group_tbl.LAST;
2431 i := p_complete_claim_group_tbl.NEXT(i);
2432 END LOOP;
2433 END IF;
2434
2435 ------------------------------
2436 -- Mass Settlement Approval --
2437 ------------------------------
2438 IF l_appr_req = 'Y' THEN
2439 OPEN csr_claim_obj_ver(p_group_claim_id);
2440 FETCH csr_claim_obj_ver INTO l_group_claim_obj_ver;
2441 CLOSE csr_claim_obj_ver;
2442
2443 l_orig_status_id := OZF_UTILITY_PVT.get_default_user_status(
2444 'OZF_CLAIM_STATUS'
2445 ,'OPEN'
2446 );
2447
2448 l_new_status_id := OZF_UTILITY_PVT.get_default_user_status(
2449 'OZF_CLAIM_STATUS'
2450 ,'CLOSED'
2451 );
2452
2453 l_reject_status_id := OZF_UTILITY_PVT.get_default_user_status(
2454 'OZF_CLAIM_STATUS'
2455 ,'REJECTED'
2456 );
2457
2458 l_owner_id := OZF_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
2459
2460 --//Added by BKUNJAN Bug#5686706
2461 UPDATE ozf_claims_all
2462 SET payment_status = 'PENDING'
2463 , status_code = 'PENDING_APPROVAL'
2464 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
2465 'OZF_CLAIM_STATUS'
2466 ,'PENDING_APPROVAL'
2467 )
2468 WHERE claim_id = p_group_claim_id;
2469 --End of Addition
2470
2471 AMS_GEN_APPROVAL_PVT.StartProcess(
2472 p_activity_type => 'CLAM'
2473 ,p_activity_id => p_group_claim_id
2474 ,p_approval_type => 'CLAIM'
2475 ,p_object_version_number => l_group_claim_obj_ver
2476 ,p_orig_stat_id => l_orig_status_id
2477 ,p_new_stat_id => l_new_status_id
2478 ,p_reject_stat_id => l_reject_status_id
2479 ,p_requester_userid => l_owner_id
2480 ,p_notes_from_requester => null
2481 ,p_workflowprocess => 'AMSGAPP'
2482 ,p_item_type => 'AMSGAPP'
2483 );
2484 IF l_return_status = FND_API.g_ret_sts_error THEN
2485 RAISE FND_API.g_exc_error;
2486 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2487 RAISE FND_API.g_exc_unexpected_error;
2488 END IF;
2489
2490 --//Commented by BKUNJAN Bug#5686706
2491 /*
2492 UPDATE ozf_claims_all
2493 SET payment_status = 'PENDING'
2494 , status_code = 'PENDING_APPROVAL'
2495 , user_status_id = OZF_UTILITY_PVT.get_default_user_status(
2496 'OZF_CLAIM_STATUS'
2497 ,'PENDING_APPROVAL'
2498 )
2499 WHERE claim_id = p_group_claim_id;
2500 */
2501 -- End of Comments
2502
2503 END IF;
2504
2505 IF OZF_DEBUG_HIGH_ON THEN
2506 OZF_Utility_PVT.debug_message(l_full_name||': end');
2507 END IF;
2508
2509
2510 EXCEPTION
2511 WHEN FND_API.g_exc_error THEN
2512 ROLLBACK TO Approve_Mass_Settlement;
2513 x_return_status := FND_API.g_ret_sts_error;
2514 FND_MSG_PUB.count_and_get (
2515 p_encoded => FND_API.g_false
2516 ,p_count => x_msg_count
2517 ,p_data => x_msg_data
2518 );
2519
2520 WHEN FND_API.g_exc_unexpected_error THEN
2521 ROLLBACK TO Approve_Mass_Settlement;
2522 x_return_status := FND_API.g_ret_sts_unexp_error ;
2523 FND_MSG_PUB.count_and_get (
2524 p_encoded => FND_API.g_false
2525 ,p_count => x_msg_count
2526 ,p_data => x_msg_data
2527 );
2528
2529 WHEN OTHERS THEN
2530 ROLLBACK TO Approve_Mass_Settlement;
2531 x_return_status := FND_API.g_ret_sts_unexp_error ;
2532 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2533 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2534 END IF;
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 END Approve_Mass_Settlement;
2542
2543 /*=======================================================================*
2544 | PROCEDURE
2545 | Reject_Mass_Payment
2546 |
2547 | NOTES
2548 |
2549 | HISTORY
2550 | 17-FEB-2006 sshivali Create.
2551 *=======================================================================*/
2552 PROCEDURE Reject_Mass_Payment(
2553 p_group_claim_id IN NUMBER,
2554 x_return_status OUT NOCOPY VARCHAR2,
2555 x_msg_data OUT NOCOPY VARCHAR2,
2556 x_msg_count OUT NOCOPY NUMBER
2557 )
2558 IS
2559 l_api_version CONSTANT NUMBER := 1.0;
2560 l_api_name CONSTANT VARCHAR2(30) := 'Reject_Mass_Payment';
2561 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2562 l_return_status VARCHAR2(1);
2563 l_rejected_user_status_id NUMBER;
2564 l_open_user_status_id NUMBER;
2565
2566 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
2567 SELECT user_status_id
2568 FROM ams_user_statuses_vl
2569 WHERE system_status_type = 'OZF_CLAIM_STATUS'
2570 AND default_flag = 'Y'
2571 AND system_status_code = cv_status_code;
2572
2573 BEGIN
2574 IF OZF_DEBUG_HIGH_ON THEN
2575 OZF_Utility_PVT.debug_message(l_full_name||': start');
2576 END IF;
2577
2578 OPEN csr_user_status_id('REJECTED');
2579 FETCH csr_user_status_id INTO l_rejected_user_status_id;
2580 CLOSE csr_user_status_id;
2581
2582 OPEN csr_user_status_id('OPEN');
2583 FETCH csr_user_status_id INTO l_open_user_status_id;
2584 CLOSE csr_user_status_id;
2585
2586 BEGIN
2587 SAVEPOINT Reject_Mass_Payment;
2588
2589 UPDATE ozf_claims_all
2590 SET status_code = 'REJECTED'
2591 , user_status_id = l_rejected_user_status_id
2592 WHERE claim_id = p_group_claim_id;
2593
2594 --bug5460095
2595 UPDATE ozf_claims_all
2596 SET status_code = 'OPEN'
2597 , user_status_id = l_open_user_status_id
2598 , amount_remaining = amount - NVL(amount_adjusted,0)
2599 , acctd_amount_remaining = acctd_amount - NVL(acctd_amount_adjusted,0)
2600 , amount_settled = 0
2601 , acctd_amount_settled = 0
2602 , group_claim_id = null
2603 , payment_method = null
2604 WHERE group_claim_id = p_group_claim_id;
2605
2606 UPDATE ozf_settlement_docs_all
2607 SET payment_status = 'CANCELLED'
2608 WHERE group_claim_id = p_group_claim_id;
2609
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 ROLLBACK TO Reject_Mass_Payment;
2613 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2614 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
2615 FND_MSG_PUB.add;
2616 END IF;
2617 IF OZF_DEBUG_LOW_ON THEN
2618 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2619 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2620 FND_MSG_PUB.Add;
2621 END IF;
2622 RAISE FND_API.g_exc_unexpected_error;
2623 END;
2624
2625 IF OZF_DEBUG_HIGH_ON THEN
2626 OZF_Utility_PVT.debug_message(l_full_name||': end');
2627 END IF;
2628
2629 EXCEPTION
2630 WHEN FND_API.g_exc_error THEN
2631 x_return_status := FND_API.g_ret_sts_error;
2632 FND_MSG_PUB.count_and_get (
2633 p_encoded => FND_API.g_false
2634 ,p_count => x_msg_count
2635 ,p_data => x_msg_data
2636 );
2637
2638 WHEN FND_API.g_exc_unexpected_error THEN
2639 x_return_status := FND_API.g_ret_sts_unexp_error ;
2640 FND_MSG_PUB.count_and_get (
2641 p_encoded => FND_API.g_false
2642 ,p_count => x_msg_count
2643 ,p_data => x_msg_data
2644 );
2645
2646 WHEN OTHERS THEN
2647 x_return_status := FND_API.g_ret_sts_unexp_error ;
2648 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2649 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2650 END IF;
2651 FND_MSG_PUB.count_and_get(
2652 p_encoded => FND_API.g_false
2653 ,p_count => x_msg_count
2654 ,p_data => x_msg_data
2655 );
2656
2657 END Reject_Mass_Payment;
2658
2659 /*=======================================================================*
2660 | PROCEDURE
2661 | Start_Mass_Payment
2662 |
2663 | NOTES
2664 |
2665 | HISTORY
2666 | 20-OCT-2003 mchang Create.
2667 *=======================================================================*/
2668 PROCEDURE Start_Mass_Payment(
2669 p_group_claim_id IN NUMBER,
2670 x_return_status OUT NOCOPY VARCHAR2,
2671 x_msg_data OUT NOCOPY VARCHAR2,
2672 x_msg_count OUT NOCOPY NUMBER
2673 )
2674 IS
2675 l_api_version CONSTANT NUMBER := 1.0;
2676 l_api_name CONSTANT VARCHAR2(30) := 'Start_Mass_Payment';
2677 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2678 l_return_status VARCHAR2(1);
2679 ---
2680
2681 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
2682 SELECT user_status_id
2683 FROM ams_user_statuses_vl
2684 WHERE system_status_type = 'OZF_CLAIM_STATUS'
2685 AND default_flag = 'Y'
2686 AND system_status_code = cv_status_code;
2687
2688 CURSOR csr_master_claim_lines(cv_group_claim_id IN NUMBER) IS
2689 SELECT payment_reference_id
2690 FROM ozf_claim_lines_all
2691 WHERE claim_id = cv_group_claim_id;
2692
2693 CURSOR csr_settle_docs(cv_claim_id IN NUMBER, cv_group_claim_id IN NUMBER) IS
2694 SELECT settlement_doc_id
2695 , payment_method
2696 , settlement_id
2697 , settlement_number
2698 , settlement_type_id
2699 , settlement_amount
2700 , payment_reference_id
2701 , payment_reference_number
2702 , group_claim_id
2703 , gl_date
2704 , wo_rec_trx_id
2705 FROM ozf_settlement_docs_all
2706 WHERE claim_id = cv_claim_id
2707 AND group_claim_id = cv_group_claim_id
2708 AND payment_status <> 'CANCELLED';
2709
2710 TYPE number_tbl IS TABLE OF NUMBER
2711 INDEX BY BINARY_INTEGER;
2712
2713 l_claim_id_tbl number_tbl;
2714 l_claim_rec OZF_Claim_PVT.claim_rec_type;
2715 l_settlement_doc_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
2716 l_deduction_type VARCHAR2(30);
2717 l_settlement_id NUMBER;
2718 i NUMBER := 1;
2719 j NUMBER := 1;
2720 l_close_user_status_id NUMBER;
2721
2722
2723 BEGIN
2724 -------------------- initialize -----------------------
2725 SAVEPOINT Start_Mass_Payment;
2726
2727 IF OZF_DEBUG_HIGH_ON THEN
2728 OZF_Utility_PVT.debug_message(l_full_name||': start');
2729 END IF;
2730
2731 x_return_status := FND_API.G_RET_STS_SUCCESS;
2732
2733 ------------------------ start -------------------------
2734 OPEN csr_master_claim_lines(p_group_claim_id);
2735 LOOP
2736 FETCH csr_master_claim_lines INTO l_claim_id_tbl(i);
2737 EXIT WHEN csr_master_claim_lines%NOTFOUND;
2738 i := i + 1;
2739 END LOOP;
2740 CLOSE csr_master_claim_lines;
2741
2742 i := l_claim_id_tbl.FIRST;
2743 IF i IS NOT NULL THEN
2744 LOOP
2745 OZF_AR_Payment_PVT.Query_Claim(
2746 p_claim_id => l_claim_id_tbl(i)
2747 ,x_claim_rec => l_claim_rec
2748 ,x_return_status => l_return_status
2749 );
2750 IF l_return_status = FND_API.g_ret_sts_error THEN
2751 RAISE FND_API.g_exc_error;
2752 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2753 RAISE FND_API.g_exc_unexpected_error;
2754 END IF;
2755
2756 -- Bug435194: Close claim before making payment
2757 Close_Claim(
2758 p_group_claim_id => p_group_claim_id
2759 ,p_claim_id => l_claim_id_tbl(i)
2760 ,x_return_status => l_return_status
2761 );
2762 IF l_return_status = FND_API.g_ret_sts_error THEN
2763 RAISE FND_API.g_exc_error;
2764 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2765 RAISE FND_API.g_exc_unexpected_error;
2766 END IF;
2767
2768 IF l_claim_rec.claim_class = 'DEDUCTION' THEN
2769 IF l_claim_rec.source_object_class IS NULL AND
2770 l_claim_rec.source_object_id IS NULL THEN
2771 l_deduction_type := 'RECEIPT_DED';
2772 ELSE
2773 l_deduction_type := 'SOURCE_DED';
2774 END IF;
2775 ELSIF l_claim_rec.claim_class = 'OVERPAYMENT' THEN
2776 IF l_claim_rec.source_object_class IS NULL AND
2777 l_claim_rec.source_object_id IS NULL THEN
2778 l_deduction_type := 'RECEIPT_OPM';
2779 ELSE
2780 l_deduction_type := 'SOURCE_OPM';
2781 END IF;
2782 END IF;
2783
2784 j := 1;
2785 OPEN csr_settle_docs(l_claim_id_tbl(i), p_group_claim_id);
2786 LOOP
2787 FETCH csr_settle_docs INTO l_settlement_doc_tbl(j).settlement_doc_id
2788 , l_settlement_doc_tbl(j).payment_method
2789 , l_settlement_doc_tbl(j).settlement_id
2790 , l_settlement_doc_tbl(j).settlement_number
2791 , l_settlement_doc_tbl(j).settlement_type_id
2792 , l_settlement_doc_tbl(j).settlement_amount
2793 , l_settlement_doc_tbl(j).payment_reference_id
2794 , l_settlement_doc_tbl(j).payment_reference_number
2795 , l_settlement_doc_tbl(j).group_claim_id
2796 , l_settlement_doc_tbl(j).gl_date
2797 , l_settlement_doc_tbl(j).wo_rec_trx_id;
2798 EXIT WHEN csr_settle_docs%NOTFOUND;
2799 j := j + 1;
2800 END LOOP;
2801 CLOSE csr_settle_docs;
2802
2803
2804 j := l_settlement_doc_tbl.FIRST;
2805 IF j IS NOT NULL THEN
2806 LOOP
2807 IF OZF_DEBUG_HIGH_ON THEN
2808 OZF_Utility_PVT.debug_message('Payment -- ('||j||')');
2809 OZF_Utility_PVT.debug_message('Payment -- payment_method = '||l_settlement_doc_tbl(j).payment_method);
2810 OZF_Utility_PVT.debug_message('Payment -- settlement_doc_id = '||l_settlement_doc_tbl(j).settlement_doc_id);
2811 OZF_Utility_PVT.debug_message('Payment -- settlement_id = '||l_settlement_doc_tbl(j).settlement_id);
2812 OZF_Utility_PVT.debug_message('Payment -- settlement_number = '||l_settlement_doc_tbl(j).settlement_number);
2813 OZF_Utility_PVT.debug_message('Payment -- settlement_amount = '||l_settlement_doc_tbl(j).settlement_amount);
2814 OZF_Utility_PVT.debug_message('Payment -- payment_reference_id = '||l_settlement_doc_tbl(j).payment_reference_id);
2815 OZF_Utility_PVT.debug_message('Payment -- payment_reference_number = '||l_settlement_doc_tbl(j).payment_reference_number);
2816 END IF;
2817
2818 BEGIN
2819 UPDATE ozf_settlement_docs_all
2820 SET payment_status = 'PENDING_PAID'
2821 WHERE settlement_doc_id = l_settlement_doc_tbl(j).settlement_doc_id;
2822 EXCEPTION
2823 WHEN OTHERS THEN
2824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2825 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_ERR');
2826 FND_MSG_PUB.add;
2827 END IF;
2828 IF OZF_DEBUG_LOW_ON THEN
2829 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2830 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2831 FND_MSG_PUB.Add;
2832 END IF;
2833 RAISE FND_API.g_exc_unexpected_error;
2834 END;
2835
2836 IF l_settlement_doc_tbl(j).payment_method = 'CREDIT_MEMO' THEN
2837 Pay_by_Credit_Memo(
2838 p_claim_rec => l_claim_rec
2839 ,p_deduction_type => l_deduction_type
2840 ,p_payment_reference_id => l_settlement_doc_tbl(j).settlement_id
2841 ,p_credit_memo_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2842 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2843 ,x_return_status => l_return_status
2844 ,x_msg_data => x_msg_data
2845 ,x_msg_count => x_msg_count
2846 );
2847 IF l_return_status = FND_API.g_ret_sts_error THEN
2848 RAISE FND_API.g_exc_error;
2849 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2850 RAISE FND_API.g_exc_unexpected_error;
2851 END IF;
2852
2853 ELSIF l_settlement_doc_tbl(j).payment_method = 'DEBIT_MEMO' THEN
2854 Pay_by_Debit_Memo(
2855 p_claim_rec => l_claim_rec
2856 ,p_deduction_type => l_deduction_type
2857 ,p_payment_reference_id => l_settlement_doc_tbl(j).settlement_id
2858 ,p_debit_memo_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2859 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2860 ,x_return_status => l_return_status
2861 ,x_msg_data => x_msg_data
2862 ,x_msg_count => x_msg_count
2863 );
2864 IF l_return_status = FND_API.g_ret_sts_error THEN
2865 RAISE FND_API.g_exc_error;
2866 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2867 RAISE FND_API.g_exc_unexpected_error;
2868 END IF;
2869
2870 ELSIF l_settlement_doc_tbl(j).payment_method = 'CHARGEBACK' THEN
2871 Pay_by_Chargeback(
2872 p_claim_rec => l_claim_rec
2873 ,p_deduction_type => l_deduction_type
2874 ,p_chargeback_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2875 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2876 ,p_gl_date => l_settlement_doc_tbl(j).gl_date
2877 ,x_return_status => l_return_status
2878 ,x_msg_data => x_msg_data
2879 ,x_msg_count => x_msg_count
2880 );
2881 IF l_return_status = FND_API.g_ret_sts_error THEN
2882 RAISE FND_API.g_exc_error;
2883 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2884 RAISE FND_API.g_exc_unexpected_error;
2885 END IF;
2886
2887 ELSIF l_settlement_doc_tbl(j).payment_method = 'WRITE_OFF' THEN
2888 Pay_by_Write_Off(
2889 p_claim_rec => l_claim_rec
2890 ,p_deduction_type => l_deduction_type
2891 ,p_write_off_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2892 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2893 ,p_gl_date => l_settlement_doc_tbl(j).gl_date
2894 ,p_wo_rec_trx_id => l_settlement_doc_tbl(j).wo_rec_trx_id
2895 ,x_return_status => l_return_status
2896 ,x_msg_data => x_msg_data
2897 ,x_msg_count => x_msg_count
2898 );
2899 IF l_return_status = FND_API.g_ret_sts_error THEN
2900 RAISE FND_API.g_exc_error;
2901 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2902 RAISE FND_API.g_exc_unexpected_error;
2903 END IF;
2904
2905 ELSIF l_settlement_doc_tbl(j).payment_method = 'ON_ACCT_CREDIT' THEN
2906 Pay_by_On_Account_Credit(
2907 p_claim_rec => l_claim_rec
2908 ,p_deduction_type => l_deduction_type
2909 ,p_credit_amount => (l_settlement_doc_tbl(j).settlement_amount * -1)
2910 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2911 ,x_return_status => l_return_status
2912 ,x_msg_data => x_msg_data
2913 ,x_msg_count => x_msg_count
2914 );
2915 IF l_return_status = FND_API.g_ret_sts_error THEN
2916 RAISE FND_API.g_exc_error;
2917 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2918 RAISE FND_API.g_exc_unexpected_error;
2919 END IF;
2920
2921 ELSIF l_settlement_doc_tbl(j).payment_method IN ('DEDUCTION', 'OVERPAYMENT') THEN
2922 Pay_by_Open_Receipt(
2923 p_claim_rec => l_claim_rec
2924 ,p_deduction_type => l_deduction_type
2925 ,p_open_receipt_id => l_settlement_doc_tbl(j).settlement_id
2926 ,p_payment_claim_id => l_settlement_doc_tbl(j).payment_reference_id
2927 ,p_amount_applied => l_settlement_doc_tbl(j).settlement_amount
2928 ,p_settlement_doc_id => l_settlement_doc_tbl(j).settlement_doc_id
2929 ,x_return_status => l_return_status
2930 ,x_msg_data => x_msg_data
2931 ,x_msg_count => x_msg_count
2932 );
2933 IF l_return_status = FND_API.g_ret_sts_error THEN
2934 RAISE FND_API.g_exc_error;
2935 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2936 RAISE FND_API.g_exc_unexpected_error;
2937 END IF;
2938
2939 -- Bug4124810: Close payment claim
2940 Close_Claim(
2941 p_group_claim_id => p_group_claim_id
2942 ,p_claim_id => l_settlement_doc_tbl(j).payment_reference_id
2943 ,x_return_status => l_return_status
2944 );
2945 IF l_return_status = FND_API.g_ret_sts_error THEN
2946 RAISE FND_API.g_exc_error;
2947 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2948 RAISE FND_API.g_exc_unexpected_error;
2949 END IF;
2950
2951 END IF;
2952 EXIT WHEN j = l_settlement_doc_tbl.LAST;
2953 j := l_settlement_doc_tbl.NEXT(j);
2954 END LOOP;
2955 END IF;
2956 EXIT WHEN i = l_claim_id_tbl.LAST;
2957 i := l_claim_id_tbl.NEXT(i);
2958 l_settlement_doc_tbl.DELETE;
2959 END LOOP;
2960 END IF;
2961
2962 -----------------------
2963 -- Close Claim Group --
2964 -----------------------
2965 OPEN csr_user_status_id('CLOSED');
2966 FETCH csr_user_status_id INTO l_close_user_status_id;
2967 CLOSE csr_user_status_id;
2968
2969 BEGIN
2970 UPDATE ozf_claims_all
2971 SET payment_status = 'PAID'
2972 , status_code = 'CLOSED'
2973 , user_status_id = l_close_user_status_id
2974 WHERE claim_id = p_group_claim_id;
2975 EXCEPTION
2976 WHEN OTHERS THEN
2977 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2978 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
2979 FND_MSG_PUB.add;
2980 END IF;
2981 IF OZF_DEBUG_LOW_ON THEN
2982 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2983 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2984 FND_MSG_PUB.Add;
2985 END IF;
2986 RAISE FND_API.g_exc_unexpected_error;
2987 END;
2988
2989 IF OZF_DEBUG_HIGH_ON THEN
2990 OZF_Utility_PVT.debug_message(l_full_name||': end');
2991 END IF;
2992
2993 EXCEPTION
2994 WHEN FND_API.g_exc_error THEN
2995 ROLLBACK TO Start_Mass_Payment;
2996 x_return_status := FND_API.g_ret_sts_error;
2997 FND_MSG_PUB.count_and_get (
2998 p_encoded => FND_API.g_false
2999 ,p_count => x_msg_count
3000 ,p_data => x_msg_data
3001 );
3002
3003 WHEN FND_API.g_exc_unexpected_error THEN
3004 ROLLBACK TO Start_Mass_Payment;
3005 x_return_status := FND_API.g_ret_sts_unexp_error ;
3006 FND_MSG_PUB.count_and_get (
3007 p_encoded => FND_API.g_false
3008 ,p_count => x_msg_count
3009 ,p_data => x_msg_data
3010 );
3011
3012 WHEN OTHERS THEN
3013 ROLLBACK TO Start_Mass_Payment;
3014 x_return_status := FND_API.g_ret_sts_unexp_error ;
3015 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3016 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3017 END IF;
3018 FND_MSG_PUB.count_and_get(
3019 p_encoded => FND_API.g_false
3020 ,p_count => x_msg_count
3021 ,p_data => x_msg_data
3022 );
3023
3024 END Start_Mass_Payment;
3025
3026
3027
3028 /*=======================================================================
3029 | PROCEDURE
3030 | Settle_Mass_Settlement
3031 |
3032 | NOTES
3033 |
3034 | HISTORY
3035 | 20-OCT-2003 mchang Create.
3036 *=======================================================================*/
3037 PROCEDURE Settle_Mass_Settlement(
3038 p_api_version IN NUMBER
3039 ,p_init_msg_list IN VARCHAR2
3040 ,p_commit IN VARCHAR2
3041 ,p_validation_level IN NUMBER
3042
3043 ,x_return_status OUT NOCOPY VARCHAR2
3044 ,x_msg_data OUT NOCOPY VARCHAR2
3045 ,x_msg_count OUT NOCOPY NUMBER
3046
3047 ,p_group_claim_rec IN group_claim_rec
3048 ,p_open_claim_tbl IN open_claim_tbl
3049 ,p_open_transaction_tbl IN open_transaction_tbl
3050 ,p_payment_method_tbl IN claim_payment_method_tbl
3051
3052 ,x_claim_group_id OUT NOCOPY NUMBER
3053 ,x_claim_group_number OUT NOCOPY VARCHAR2
3054 --,x_split_claim_tbl OUT NOCOPY open_claim_tbl
3055 )
3056 IS
3057 l_api_version CONSTANT NUMBER := 1.0;
3058 l_api_name CONSTANT VARCHAR2(30) := 'Settle_Mass_Settlement';
3059 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3060 l_return_status VARCHAR2(1);
3061 ---
3062
3063 CURSOR csr_claim_amount_rem(cv_claim_id IN NUMBER) IS
3064 SELECT amount_remaining
3065 , object_version_number
3066 FROM ozf_claims_all
3067 WHERE claim_id = cv_claim_id;
3068
3069 CURSOR csr_get_set_of_books IS
3070 SELECT set_of_books_id
3071 FROM ozf_sys_parameters;
3072
3073 CURSOR csr_mass_setl_appr_req IS
3074 SELECT NVL(attr_available_flag, 'N')
3075 FROM ams_custom_setup_attr
3076 WHERE object_attribute = 'APPR'
3077 AND custom_setup_id = ( SELECT custom_setup_id
3078 FROM ams_custom_setups_b
3079 WHERE activity_type_code = 'GROUP'
3080 AND object_type = 'CLAM'
3081 AND enabled_flag = 'Y' );
3082
3083
3084 CURSOR csr_get_gl_date_type(cv_set_of_books_id IN NUMBER) IS
3085 SELECT gl_date_type
3086 FROM ozf_sys_parameters
3087 WHERE set_of_books_id = cv_set_of_books_id;
3088
3089 --//Bugfix : 7439145
3090 CURSOR p_acctd_claim_amts(p_claim_id IN NUMBER) IS
3091 SELECT acctd_amount,
3092 acctd_amount_adjusted
3093 FROM ozf_claims_all
3094 WHERE claim_id =p_claim_id;
3095
3096 l_ded_claim_tbl OZF_Claim_PVT.claim_tbl_type;
3097 l_opm_claim_tbl OZF_Claim_PVT.claim_tbl_type;
3098 l_settle_doc_tbl OZF_Settlement_Doc_Pvt.settlement_doc_tbl_type;
3099 l_split_claim_tbl OZF_Split_Claim_PVT.child_claim_tbl_type;
3100 l_idx_ded NUMBER := 1;
3101 l_idx_opm NUMBER := 1;
3102
3103 l_group_claim_rec OZF_Claim_PVT.claim_rec_type;
3104 l_group_claim_id NUMBER;
3105 l_group_claim_line_tbl OZF_Claim_Line_PVT.claim_line_tbl_type;
3106 l_group_settle_doc_tbl OZF_Settlement_Doc_Pvt.settlement_doc_tbl_type;
3107 l_group_settle_doc_id_tbl JTF_NUMBER_TABLE;
3108 l_idx_claim_line NUMBER := 1;
3109 l_idx_setl_doc NUMBER := 1;
3110
3111 l_open_claim_tbl open_claim_tbl := p_open_claim_tbl;
3112
3113 l_open_claim_amt NUMBER := 0;
3114 l_open_trx_amt NUMBER := 0;
3115 l_pay_method_amt NUMBER := 0;
3116
3117 l_group_claim_amt NUMBER := 0;
3118 l_group_trx_amt NUMBER := 0;
3119 l_group_settle_amt NUMBER := 0;
3120 l_group_rem_amt NUMBER := 0;
3121
3122 l_settlement_type VARCHAR2(30);
3123 l_claim_amount_rem NUMBER;
3124 l_obj_ver_num NUMBER;
3125
3126 l_complete_claim_group_tbl OZF_Claim_PVT.claim_tbl_type;
3127 i BINARY_INTEGER;
3128 l_appr_req VARCHAR2(1);
3129
3130
3131 l_overpay_amt NUMBER := 0;
3132 l_total_settle_amt NUMBER := 0;
3133 l_gl_date_type VARCHAR2(30);
3134
3135 --bug4768031
3136 l_deduction_count NUMBER := 0;
3137 l_overpayment_count NUMBER := 0;
3138
3139 --//Bugfix : 7439145
3140 l_act_amt NUMBER :=0;
3141 l_act_amt_settled NUMBER :=0;
3142 l_act_amt_adjusted NUMBER :=0;
3143
3144 /*
3145 l_gl_date DATE;
3146 l_wo_rec_trx_id NUMBER;
3147 */
3148
3149 BEGIN
3150 -------------------- initialize -----------------------
3151 SAVEPOINT Settle_Mass_Settlement;
3152
3153 IF OZF_DEBUG_HIGH_ON THEN
3154 OZF_Utility_PVT.debug_message(l_full_name||': start');
3155 END IF;
3156
3157 -- Standard call to check for call compatibility.
3158 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3159 p_api_version,
3160 l_api_name,
3161 G_PKG_NAME ) THEN
3162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3163 END IF;
3164
3165 -- Initialize message list if p_init_msg_list is set to TRUE.
3166 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3167 FND_MSG_PUB.initialize;
3168 END IF;
3169
3170 -- Initialize API return status to SUCCESS
3171 x_return_status := FND_API.G_RET_STS_SUCCESS;
3172
3173 ------------------------ start -------------------------
3174 -------------------
3175 -- 1. Validation --
3176 -------------------
3177 IF OZF_DEBUG_HIGH_ON THEN
3178 OZF_Utility_PVT.debug_message('1. Validation');
3179 END IF;
3180
3181 i := p_open_claim_tbl.FIRST;
3182 IF i IS NOT NULL THEN
3183 LOOP
3184 l_open_claim_amt := l_open_claim_amt + p_open_claim_tbl(i).amount_settled;
3185
3186 IF OZF_DEBUG_HIGH_ON THEN
3187 OZF_Utility_PVT.debug_message('open_claim_amt('||i||') = '||p_open_claim_tbl(i).amount_settled);
3188 END IF;
3189 IF p_open_claim_tbl(i).claim_class = l_settlement_type THEN
3190 l_overpay_amt := l_overpay_amt + l_open_claim_tbl(i).amount_settled * -1 ;
3191 END IF;
3192
3193 --bug4768031
3194 IF p_open_claim_tbl(i).claim_class = 'DEDUCTION' THEN
3195 l_deduction_count := l_deduction_count + 1;
3196 ELSIF p_open_claim_tbl(i).claim_class = 'OVERPAYMENT' THEN
3197 l_overpayment_count := l_overpayment_count + 1;
3198 END IF;
3199
3200 EXIT WHEN i = p_open_claim_tbl.LAST;
3201 i := p_open_claim_tbl.NEXT(i);
3202 END LOOP;
3203 END IF;
3204
3205 IF p_open_claim_tbl.LAST > 1 AND
3206 NOT ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed THEN
3207 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3208 FND_MESSAGE.Set_Name('OZF','OZF_SETL_MUL_CLA_NS');
3209 FND_MSG_PUB.Add;
3210 END IF;
3211 RAISE FND_API.g_exc_error;
3212 END IF;
3213
3214 IF SIGN(l_open_claim_amt) = 1 THEN
3215 l_settlement_type := 'DEDUCTION';
3216 ELSIF SIGN(l_open_claim_amt) = -1 THEN
3217 l_settlement_type := 'OVERPAYMENT';
3218 ELSIF SIGN(l_open_claim_amt) = 0 THEN
3219 l_settlement_type := 'NETTING';
3220 END IF;
3221
3222 i := p_open_transaction_tbl.FIRST;
3223 IF i IS NOT NULL THEN
3224 LOOP
3225 IF l_settlement_type = 'DEDUCTION' AND
3226 p_open_transaction_tbl(i).trx_class <> 'CM' THEN
3227 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3228 FND_MESSAGE.Set_Name('OZF','OZF_SETL_DED_POCM');
3229 FND_MSG_PUB.Add;
3230 END IF;
3231 RAISE FND_API.g_exc_error;
3232 ELSIF l_settlement_type = 'OVERPAYMENT' AND
3233 p_open_transaction_tbl(i).trx_class NOT IN ('INVOICE', 'INV', 'DM', 'CB') THEN
3234 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3235 FND_MESSAGE.Set_Name('OZF','OZF_SETL_OPM_PODM');
3236 FND_MSG_PUB.Add;
3237 END IF;
3238 RAISE FND_API.g_exc_error;
3239 END IF;
3240
3241 l_open_trx_amt := l_open_trx_amt + p_open_transaction_tbl(i).amount_settled;
3242 EXIT WHEN i = p_open_transaction_tbl.LAST;
3243 IF OZF_DEBUG_HIGH_ON THEN
3244 OZF_Utility_PVT.debug_message('open_trx_amt('||i||') = '||p_open_transaction_tbl(i).amount_settled);
3245 END IF;
3246 i := p_open_transaction_tbl.NEXT(i);
3247 END LOOP;
3248 END IF;
3249
3250 IF ABS(l_open_claim_amt) < ABS(l_open_trx_amt) THEN
3251 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3252 FND_MESSAGE.Set_Name('OZF','OZF_SETL_TRX_AMT_ERR');
3253 FND_MSG_PUB.Add;
3254 END IF;
3255 RAISE FND_API.g_exc_error;
3256 END IF;
3257
3258 i := p_payment_method_tbl.FIRST;
3259 IF i IS NOT NULL THEN
3260 LOOP
3261 IF l_settlement_type = 'DEDUCTION' AND
3262 p_payment_method_tbl(i).payment_method NOT IN ('WRITE_OFF', 'CHARGEBACK') THEN
3263 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3264 FND_MESSAGE.Set_Name('OZF','OZF_SETL_DED_ERR');
3265 FND_MSG_PUB.Add;
3266 END IF;
3267 RAISE FND_API.g_exc_error;
3268 ELSIF l_settlement_type = 'OVERPAYMENT' AND
3269 p_payment_method_tbl(i).payment_method NOT IN ('WRITE_OFF', 'ON_ACCT_CREDIT') THEN
3270 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3271 FND_MESSAGE.Set_Name('OZF','OZF_SETL_OPM_ERR');
3272 FND_MSG_PUB.Add;
3273 END IF;
3274 RAISE FND_API.g_exc_error;
3275 END IF;
3276
3277 l_pay_method_amt := l_pay_method_amt + (p_payment_method_tbl(i).amount_settled * -1);
3278 EXIT WHEN i = p_payment_method_tbl.LAST;
3279 IF OZF_DEBUG_HIGH_ON THEN
3280 OZF_Utility_PVT.debug_message('pay_method_amt('||i||') = '||p_payment_method_tbl(i).amount_settled);
3281 END IF;
3282 /*
3283 l_gl_date := p_payment_method_tbl(i).gl_date;
3284 l_wo_rec_trx_id := p_payment_method_tbl(i).wo_rec_trx_id;
3285 */
3286 i := p_payment_method_tbl.NEXT(i);
3287 END LOOP;
3288 END IF;
3289
3290
3291
3292 IF ABS(l_open_claim_amt) < (ABS(l_open_trx_amt) + ABS(l_pay_method_amt)) THEN
3293 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3294 FND_MESSAGE.Set_Name('OZF','OZF_SETL_OPN_CLA_AMT_ERR');
3295 FND_MSG_PUB.Add;
3296 END IF;
3297 RAISE FND_API.g_exc_error;
3298 END IF;
3299
3300 --bug4768031
3301 IF (ABS(l_open_trx_amt) = 0 AND ABS(l_pay_method_amt) = 0 AND
3302 ((l_overpayment_count = 0 AND l_deduction_count > 0) OR
3303 (l_overpayment_count > 0 AND l_deduction_count = 0))) THEN
3304 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3305 FND_MESSAGE.Set_Name('OZF','OZF_SETL_DED_OPM_ERR');
3306 FND_MSG_PUB.Add;
3307 END IF;
3308 RAISE FND_API.g_exc_error;
3309 END IF;
3310
3311 /* Bug4079177: Claims should be split if the settlement amount is less then
3312 the settlement amount on the group. If the group is a deduction group,
3313 then the deductions should be split. Else the overpayments should be
3314 split.*/
3315
3316 i := l_open_claim_tbl.FIRST;
3317 IF i IS NOT NULL AND l_settlement_type <> 'NETTING' THEN
3318 LOOP
3319 IF l_open_claim_tbl(i).claim_class <> l_settlement_type THEN
3320 l_overpay_amt := l_overpay_amt - ABS(l_open_claim_tbl(i).amount_settled) ;
3321 END IF;
3322 EXIT WHEN i = l_open_claim_tbl.LAST;
3323 i := l_open_claim_tbl.NEXT(i);
3324 END LOOP;
3325 END IF;
3326
3327 l_total_settle_amt := ABS(l_open_trx_amt) + ABS(l_pay_method_amt) + ABS(l_overpay_amt);
3328 i := l_open_claim_tbl.FIRST;
3329 IF i IS NOT NULL THEN
3330 LOOP
3331 IF l_open_claim_tbl(i).claim_class = l_settlement_type THEN
3332 IF ABS(l_open_claim_tbl(i).amount_settled) < l_total_settle_amt THEN
3333 l_total_settle_amt := l_total_settle_amt - ABS(l_open_claim_tbl(i).amount_settled) ;
3334 ELSIF l_total_settle_amt = 0 THEN
3335 l_open_claim_amt := l_open_claim_amt - l_open_claim_tbl(i).amount_settled;
3336 l_open_claim_tbl.DELETE(i);
3337 ELSIF ABS(l_open_claim_tbl(i).amount_settled) > l_total_settle_amt THEN
3338 IF l_open_claim_tbl(i).claim_class = 'DEDUCTION' THEN
3339 l_open_claim_tbl(i).amount_settled := l_total_settle_amt ;
3340 ELSE
3341 l_open_claim_tbl(i).amount_settled := l_total_settle_amt * -1 ;
3342 END IF;
3343 l_total_settle_amt := 0;
3344 END IF;
3345 END IF;
3346 EXIT WHEN i = p_open_claim_tbl.LAST;
3347 i := l_open_claim_tbl.NEXT(i);
3348 END LOOP;
3349 END IF;
3350
3351 IF OZF_DEBUG_HIGH_ON THEN
3352 OZF_Utility_PVT.debug_message('total open_claim_amt = '||l_open_claim_amt);
3353 OZF_Utility_PVT.debug_message('total open_trx_amt = '||l_open_trx_amt);
3354 OZF_Utility_PVT.debug_message('total pay_method_amt = '||l_pay_method_amt);
3355 OZF_Utility_PVT.debug_message('total claim_pay_amt = '||l_overpay_amt);
3356 END IF;
3357
3358
3359 ----------------------------
3360 -- 2. Create Master Claim --
3361 ----------------------------
3362 IF OZF_DEBUG_HIGH_ON THEN
3363 OZF_Utility_PVT.debug_message('2. Create Master Claim');
3364 END IF;
3365
3366 -- Get Claim Type/Reason from Claim Defaulting Rule
3367 l_group_claim_rec.claim_class := 'GROUP';
3368 l_group_claim_rec.cust_account_id := p_group_claim_rec.cust_account_id;
3369 l_group_claim_rec.claim_type_id := p_group_claim_rec.claim_type_id;
3370 l_group_claim_rec.reason_code_id := p_group_claim_rec.reason_code_id;
3371 l_group_claim_rec.amount := l_open_claim_amt; --l_open_trx_amt + l_pay_method_amt;
3372 l_group_claim_rec.currency_code := p_group_claim_rec.currency_code;
3373 l_group_claim_rec.cust_billto_acct_site_id := p_group_claim_rec.bill_to_site_id;
3374 l_group_claim_rec.org_id := p_group_claim_rec.org_id;
3375 l_group_claim_rec.user_status_id := 2006;
3376
3377 IF OZF_DEBUG_HIGH_ON THEN
3378 OZF_Utility_PVT.debug_message('group claim amount = '||l_group_claim_rec.amount);
3379 END IF;
3380
3381 l_group_claim_rec.claim_date := SYSDATE;
3382 OPEN csr_get_set_of_books;
3383 FETCH csr_get_set_of_books INTO l_group_claim_rec.set_of_books_id;
3384 CLOSE csr_get_set_of_books;
3385 --l_group_claim_rec.currency_code := 'USD';
3386 l_group_claim_rec.payment_method := 'MASS_SETTLEMENT';
3387
3388 OZF_Claim_PVT.Create_Claim (
3389 p_api_version => l_api_version
3390 ,p_init_msg_list => FND_API.G_FALSE
3391 ,p_commit => FND_API.G_FALSE
3392 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3393 ,x_return_status => l_return_status
3394 ,x_msg_data => x_msg_data
3395 ,x_msg_count => x_msg_count
3396 ,p_claim => l_group_claim_rec
3397 ,x_claim_id => l_group_claim_id
3398 );
3399 IF OZF_DEBUG_HIGH_ON THEN
3400 OZF_Utility_PVT.debug_message('group claim id = '||l_group_claim_id);
3401 END IF;
3402
3403 IF l_return_status = FND_API.g_ret_sts_error THEN
3404 RAISE FND_API.g_exc_error;
3405 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3406 RAISE FND_API.g_exc_unexpected_error;
3407 END IF;
3408
3409
3410 ---------------------
3411 -- 3. Group Claims --
3412 ---------------------
3413 IF OZF_DEBUG_HIGH_ON THEN
3414 OZF_Utility_PVT.debug_message('3. Group Claims');
3415 END IF;
3416
3417 i := l_open_claim_tbl.FIRST;
3418 IF i IS NOT NULL THEN
3419 LOOP
3420 IF OZF_DEBUG_HIGH_ON THEN
3421 OZF_Utility_PVT.debug_message('Update Claim Id:'||l_open_claim_tbl(i).claim_id);
3422 END IF;
3423
3424 BEGIN
3425 UPDATE ozf_claims_all
3426 SET group_claim_id = l_group_claim_id
3427 --, gl_date = l_gl_date
3428 --, wo_rec_trx_id = l_wo_rec_trx_id
3429 WHERE claim_id = l_open_claim_tbl(i).claim_id;
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3433 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
3434 FND_MSG_PUB.add;
3435 END IF;
3436 IF OZF_DEBUG_LOW_ON THEN
3437 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3438 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3439 FND_MSG_PUB.Add;
3440 END IF;
3441 RAISE FND_API.g_exc_unexpected_error;
3442 END;
3443
3444 OPEN csr_claim_amount_rem(l_open_claim_tbl(i).claim_id);
3445 FETCH csr_claim_amount_rem INTO l_claim_amount_rem
3446 , l_obj_ver_num;
3447 CLOSE csr_claim_amount_rem;
3448
3449 l_group_claim_amt := l_group_claim_amt + l_claim_amount_rem;
3450
3451 -- -------------
3452 -- Split Claims
3453 -- -------------
3454 --ozf_utility_pvt.debug_message(l_open_claim_tbl(i).amount_settled||'amount settled');
3455 IF ABS(l_open_claim_tbl(i).amount_settled) < ABS(l_claim_amount_rem) THEN
3456 --l_split_claim_tbl(1).claim_type_id := p_claim_rec.claim_type_id;
3457 --l_split_claim_tbl(1).reason_code_id := p_claim_rec.reason_code_id;
3458 l_split_claim_tbl(1).amount := l_claim_amount_rem
3459 - l_open_claim_tbl(i).amount_settled;
3460 l_split_claim_tbl(1).line_amount_sum := 0;
3461 l_split_claim_tbl(1).parent_claim_id := l_open_claim_tbl(i).claim_id;
3462 l_split_claim_tbl(1).parent_object_ver_num := l_obj_ver_num;
3463 l_split_claim_tbl(1).line_table := NULL;
3464
3465 OZF_Utility_PVT.debug_message('Split Claim '||l_open_claim_tbl(i).claim_number||' for '||l_split_claim_tbl(1).amount);
3466
3467 l_group_rem_amt := l_group_rem_amt + l_split_claim_tbl(1).amount;
3468
3469 OZF_SPLIT_CLAIM_PVT.create_child_claim_tbl (
3470 p_api_version => l_api_version
3471 ,p_init_msg_list => FND_API.g_false
3472 ,p_commit => FND_API.g_false
3473 ,p_validation_level => FND_API.g_valid_level_full
3474 ,x_return_status => l_return_status
3475 ,x_msg_data => x_msg_data
3476 ,x_msg_count => x_msg_count
3477 ,p_child_claim_tbl => l_split_claim_tbl
3478 ,p_mode => 'AUTO'
3479 );
3480 IF l_return_status = FND_API.g_ret_sts_error THEN
3481 RAISE FND_API.g_exc_error;
3482 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3483 RAISE FND_API.g_exc_unexpected_error;
3484 END IF;
3485 ELSIF ABS(l_open_claim_tbl(i).amount_settled) > ABS(l_claim_amount_rem) THEN
3486 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3487 FND_MESSAGE.Set_Name('OZF','OZF_SETL_CLA_AMT_ERR');
3488 FND_MSG_PUB.Add;
3489 END IF;
3490 RAISE FND_API.g_exc_error;
3491 END IF;
3492 EXIT WHEN i = l_open_claim_tbl.LAST;
3493 i := l_open_claim_tbl.NEXT(i);
3494 END LOOP;
3495 END IF;
3496
3497 ------------------------------
3498 -- 4. Group Settlement Docs --
3499 ------------------------------
3500 IF OZF_DEBUG_HIGH_ON THEN
3501 OZF_Utility_PVT.debug_message('4. Group Settlement Docs');
3502 END IF;
3503
3504 i := p_open_transaction_tbl.FIRST;
3505 IF i IS NOT NULL THEN
3506 LOOP
3507 l_group_trx_amt := l_group_trx_amt + p_open_transaction_tbl(i).amount_settled;
3508
3509 IF p_open_transaction_tbl(i).trx_class IN ('INV', 'DM', 'CB') THEN
3510 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method := 'DEBIT_MEMO';
3511 ELSIF p_open_transaction_tbl(i).trx_class IN ('CM') THEN
3512 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method := 'CREDIT_MEMO';
3513 END IF;
3514 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_id := p_open_transaction_tbl(i).customer_trx_id;
3515 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_number := p_open_transaction_tbl(i).trx_number;
3516 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := p_open_transaction_tbl(i).cust_trx_type_id;
3517 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_amount := p_open_transaction_tbl(i).amount_settled;
3518 l_group_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
3519 l_group_settle_doc_tbl(l_idx_setl_doc).claim_id := l_group_claim_id;
3520 l_idx_setl_doc := l_idx_setl_doc + 1;
3521 EXIT WHEN i = p_open_transaction_tbl.LAST;
3522 i := p_open_transaction_tbl.NEXT(i);
3523 END LOOP;
3524 END IF;
3525
3526 i := p_payment_method_tbl.FIRST;
3527 IF i IS NOT NULL THEN
3528 LOOP
3529 l_group_settle_amt := l_group_settle_amt + p_payment_method_tbl(i).amount_settled;
3530
3531 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method := p_payment_method_tbl(i).payment_method;
3532 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_amount := p_payment_method_tbl(i).amount_settled * -1;
3533 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := p_payment_method_tbl(i).gl_date;
3534 --//Bug 5345095
3535 IF p_payment_method_tbl(i).payment_method = 'WRITE_OFF' THEN
3536 l_group_settle_doc_tbl(l_idx_setl_doc).settlement_type_id := p_payment_method_tbl(i).wo_adj_trx_id;
3537 END IF;
3538 l_group_settle_doc_tbl(l_idx_setl_doc).wo_rec_trx_id := p_payment_method_tbl(i).wo_rec_trx_id;
3539 l_group_settle_doc_tbl(l_idx_setl_doc).payment_status := 'PENDING';
3540 l_group_settle_doc_tbl(l_idx_setl_doc).claim_id := l_group_claim_id;
3541
3542 IF l_group_settle_doc_tbl(l_idx_setl_doc).gl_date IS NULL AND
3543 l_group_settle_doc_tbl(l_idx_setl_doc).payment_method IN ('WRITE_OFF', 'CHARGEBACK') THEN
3544 OPEN csr_get_gl_date_type(l_group_claim_rec.set_of_books_id);
3545 FETCH csr_get_gl_date_type INTO l_gl_date_type;
3546 CLOSE csr_get_gl_date_type;
3547
3548 IF l_gl_date_type = 'CLAIM_DATE' THEN
3549 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := l_group_claim_rec.claim_date;
3550 ELSIF l_gl_date_type = 'DUE_DATE' THEN
3551 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := l_group_claim_rec.due_date;
3552 ELSIF l_gl_date_type = 'SYSTEM_DATE' THEN
3553 l_group_settle_doc_tbl(l_idx_setl_doc).gl_date := SYSDATE;
3554 END IF;
3555
3556 IF l_group_settle_doc_tbl(l_idx_setl_doc).gl_date IS NULL THEN
3557 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3558 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_NO_GL_DATE');
3559 FND_MSG_PUB.Add;
3560 END IF;
3561 RAISE FND_API.g_exc_error;
3562 END IF;
3563 END IF;
3564
3565 l_idx_setl_doc := l_idx_setl_doc + 1;
3566 EXIT WHEN i = p_payment_method_tbl.LAST;
3567 i := p_payment_method_tbl.NEXT(i);
3568 END LOOP;
3569 END IF;
3570
3571 IF OZF_DEBUG_HIGH_ON THEN
3572 i := l_group_settle_doc_tbl.FIRST;
3573 IF i IS NOT NULL THEN
3574 LOOP
3575 OZF_Utility_PVT.debug_message('--- '||i||' ---');
3576 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').claim_id (group) = '||l_group_settle_doc_tbl(i).claim_id);
3577 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').settlement_id = '||l_group_settle_doc_tbl(i).settlement_id);
3578 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').settlement_number = '||l_group_settle_doc_tbl(i).settlement_number);
3579 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').settlement_amount = '||l_group_settle_doc_tbl(i).settlement_amount);
3580 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').payment_method = '||l_group_settle_doc_tbl(i).payment_method);
3581 OZF_Utility_PVT.debug_message('l_group_settle_doc_tbl('||i||').payment_status = '||l_group_settle_doc_tbl(i).payment_status);
3582 EXIT WHEN i = l_group_settle_doc_tbl.LAST;
3583 i := l_group_settle_doc_tbl.NEXT(i);
3584 END LOOP;
3585 END IF;
3586 END IF;
3587
3588 ---------------------------------------------
3589 -- 5. Create Settlement Docs of Mass Claim --
3590 ---------------------------------------------
3591 IF OZF_DEBUG_HIGH_ON THEN
3592 OZF_Utility_PVT.debug_message('5. Create Settlement Docs of Mass Claim');
3593 END IF;
3594
3595 OZF_Settlement_Doc_PVT.Create_Settlement_Doc_Tbl(
3596 p_api_version_number => l_api_version,
3597 p_init_msg_list => FND_API.G_FALSE,
3598 p_commit => FND_API.G_FALSE,
3599 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3600 x_return_status => l_return_status,
3601 x_msg_count => x_msg_count,
3602 x_msg_data => x_msg_data,
3603 p_settlement_doc_tbl => l_group_settle_doc_tbl,
3604 x_settlement_doc_id_tbl => l_group_settle_doc_id_tbl
3605 );
3606 IF l_return_status = FND_API.g_ret_sts_error THEN
3607 RAISE FND_API.g_exc_error;
3608 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3609 RAISE FND_API.g_exc_unexpected_error;
3610 END IF;
3611
3612 ------------------------------
3613 -- 6. Break Mass Settlement --
3614 ------------------------------
3615 IF OZF_DEBUG_HIGH_ON THEN
3616 OZF_Utility_PVT.debug_message('6. Break Mass Settlement');
3617 END IF;
3618
3619 Break_Mass_Settlement(
3620 p_group_claim_id => l_group_claim_id,
3621 p_settlement_type => l_settlement_type,
3622 x_return_status => l_return_status,
3623 x_msg_data => x_msg_data,
3624 x_msg_count => x_msg_count
3625 );
3626 IF l_return_status = FND_API.g_ret_sts_error THEN
3627 RAISE FND_API.g_exc_error;
3628 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3629 RAISE FND_API.g_exc_unexpected_error;
3630 END IF;
3631
3632
3633 ---------------------------------
3634 -- 7. Complete Mass Settlement --
3635 ---------------------------------
3636 IF OZF_DEBUG_HIGH_ON THEN
3637 OZF_Utility_PVT.debug_message('7. Complete Mass Settlement');
3638 END IF;
3639
3640 Complete_Mass_Settlement(
3641 p_group_claim_id => l_group_claim_id,
3642 x_claim_tbl => l_complete_claim_group_tbl,
3643 x_return_status => l_return_status,
3644 x_msg_data => x_msg_data,
3645 x_msg_count => x_msg_count
3646 );
3647 IF l_return_status = FND_API.g_ret_sts_error THEN
3648 RAISE FND_API.g_exc_error;
3649 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3650 RAISE FND_API.g_exc_unexpected_error;
3651 END IF;
3652
3653 BEGIN
3654 UPDATE ozf_claims_all
3655 SET amount = l_group_claim_amt
3656 , amount_remaining = l_group_trx_amt
3657 , amount_settled = l_group_settle_amt
3658 , amount_adjusted = l_group_rem_amt
3659 WHERE claim_id = l_group_claim_id;
3660
3661 --//Bugfix : 7439145
3662 i := p_open_claim_tbl.FIRST;
3663 IF i IS NOT NULL THEN
3664 LOOP
3665 BEGIN
3666 OPEN p_acctd_claim_amts(p_open_claim_tbl(i).claim_id);
3667 FETCH p_acctd_claim_amts INTO l_act_amt,l_act_amt_adjusted;
3668 CLOSE p_acctd_claim_amts;
3669
3670 l_act_amt_settled := l_act_amt - l_act_amt_adjusted;
3671
3672 UPDATE ozf_claims_all
3673 SET amount_settled = p_open_claim_tbl(i).amount_settled,
3674 amount_remaining = amount -(amount_adjusted + p_open_claim_tbl(i).amount_settled),
3675 acctd_amount_settled = l_act_amt_settled,
3676 acctd_amount_remaining = l_act_amt - (l_act_amt_adjusted + l_act_amt_settled)
3677 WHERE claim_id =p_open_claim_tbl(i).claim_id;
3678
3679 EXCEPTION
3680 WHEN Others THEN
3681 RAISE FND_API.g_exc_unexpected_error;
3682 END;
3683 EXIT WHEN i = p_open_claim_tbl.LAST;
3684 i := p_open_claim_tbl.NEXT(i);
3685 END LOOP;
3686 END IF;
3687 --//End Bugfix# 7439145
3688
3689 EXCEPTION
3690 WHEN OTHERS THEN
3691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3692 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DOC_UPD_CLAM_ERR');
3693 FND_MSG_PUB.add;
3694 END IF;
3695 IF OZF_DEBUG_LOW_ON THEN
3696 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3697 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3698 FND_MSG_PUB.Add;
3699 END IF;
3700 RAISE FND_API.g_exc_unexpected_error;
3701 END;
3702
3703
3704 /* --------------------------------
3705 -- 8. Approve Mass Settlement --
3706 --------------------------------
3707 IF OZF_DEBUG_HIGH_ON THEN
3708 OZF_Utility_PVT.debug_message('8. Approve Mass Settlement');
3709 END IF;
3710
3711 Approve_Mass_Settlement(
3712 p_group_claim_id => l_group_claim_id,
3713 p_complete_claim_group_tbl => l_complete_claim_group_tbl,
3714 x_return_status => l_return_status,
3715 x_msg_data => x_msg_data,
3716 x_msg_count => x_msg_count
3717 );
3718 IF l_return_status = FND_API.g_ret_sts_error THEN
3719 RAISE FND_API.g_exc_error;
3720 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3721 RAISE FND_API.g_exc_unexpected_error;
3722 END IF;*/
3723
3724 OPEN csr_mass_setl_appr_req;
3725 FETCH csr_mass_setl_appr_req INTO l_appr_req;
3726 CLOSE csr_mass_setl_appr_req;
3727
3728
3729 IF l_appr_req = 'N' THEN
3730 ---------------------------
3731 -- 9. Start Mass Payment --
3732 ---------------------------
3733 IF OZF_DEBUG_HIGH_ON THEN
3734 OZF_Utility_PVT.debug_message('9. Start Mass Payment');
3735 END IF;
3736
3737 Start_Mass_Payment(
3738 p_group_claim_id => l_group_claim_id,
3739 x_return_status => l_return_status,
3740 x_msg_data => x_msg_data,
3741 x_msg_count => x_msg_count
3742 );
3743 IF l_return_status = FND_API.g_ret_sts_error THEN
3744 RAISE FND_API.g_exc_error;
3745 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3746 RAISE FND_API.g_exc_unexpected_error;
3747 END IF;
3748 ELSE
3749 IF OZF_DEBUG_HIGH_ON THEN
3750 OZF_Utility_PVT.debug_message('8. Approve Mass Settlement');
3751 END IF;
3752
3753 Approve_Mass_Settlement(
3754 p_group_claim_id => l_group_claim_id,
3755 p_complete_claim_group_tbl => l_complete_claim_group_tbl,
3756 x_return_status => l_return_status,
3757 x_msg_data => x_msg_data,
3758 x_msg_count => x_msg_count
3759 );
3760
3761 IF l_return_status = FND_API.g_ret_sts_error THEN
3762 RAISE FND_API.g_exc_error;
3763 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3764 RAISE FND_API.g_exc_unexpected_error;
3765 END IF;
3766 END IF;
3767
3768 IF OZF_DEBUG_HIGH_ON THEN
3769 OZF_Utility_PVT.debug_message(l_full_name||': end');
3770 END IF;
3771
3772
3773 ------------------------ finish ------------------------
3774 x_return_status := FND_API.G_RET_STS_SUCCESS;
3775
3776 -- Standard check for p_commit
3777 IF FND_API.to_Boolean(p_commit) THEN
3778 COMMIT WORK;
3779 END IF;
3780
3781 FND_MSG_PUB.count_and_get(
3782 p_encoded => FND_API.g_false,
3783 p_count => x_msg_count,
3784 p_data => x_msg_data
3785 );
3786
3787
3788
3789 EXCEPTION
3790 WHEN FND_API.g_exc_error THEN
3791 ROLLBACK TO Settle_Mass_Settlement;
3792 x_return_status := FND_API.g_ret_sts_error;
3793 FND_MSG_PUB.count_and_get (
3794 p_encoded => FND_API.g_false
3795 ,p_count => x_msg_count
3796 ,p_data => x_msg_data
3797 );
3798
3799 WHEN FND_API.g_exc_unexpected_error THEN
3800 ROLLBACK TO Settle_Mass_Settlement;
3801 x_return_status := FND_API.g_ret_sts_unexp_error ;
3802 FND_MSG_PUB.count_and_get (
3803 p_encoded => FND_API.g_false
3804 ,p_count => x_msg_count
3805 ,p_data => x_msg_data
3806 );
3807
3808 WHEN OTHERS THEN
3809 ROLLBACK TO Settle_Mass_Settlement;
3810 x_return_status := FND_API.g_ret_sts_unexp_error ;
3811 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3812 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3813 END IF;
3814 FND_MSG_PUB.count_and_get(
3815 p_encoded => FND_API.g_false
3816 ,p_count => x_msg_count
3817 ,p_data => x_msg_data
3818 );
3819
3820 END Settle_Mass_Settlement;
3821
3822
3823 END OZF_MASS_SETTLEMENT_PVT;