[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_SETTLEMENT_PVT
Source
1 PACKAGE BODY OZF_CLAIM_SETTLEMENT_PVT AS
2 /* $Header: ozfvcstb.pls 120.15.12020000.2 2012/07/18 04:40:44 ninarasi ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_CLAIM_SETTLEMENT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvcstb.pls';
6 G_CLAIM_CLASS_NAME CONSTANT VARCHAR2(30) := 'CLAIM';
7 G_DEDUCTION_CLASS_NAME CONSTANT VARCHAR2(30) := 'DEDUCTION';
8 G_OVERPAYMENT_CLASS_NAME CONSTANT VARCHAR2(30) := 'OVERPAYMENT';
9 G_CHARGE_CLASS_NAME CONSTANT VARCHAR2(30) := 'CHARGE';
10 G_APPROVAL_TYPE CONSTANT VARCHAR2(30) := 'CLAIM';
11
12 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
13 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
14
15 /*=======================================================================*
16 | PROCEDURE
17 | Process_Settlement_WF
18 |
19 | NOTES
20 |
21 | HISTORY
22 | 15-MAR-2002 mchang Create.
23 *=======================================================================*/
24 PROCEDURE Process_Settlement_WF(
25 p_claim_id IN NUMBER
26
27 ,x_return_status OUT NOCOPY VARCHAR2
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 ,x_msg_count OUT NOCOPY NUMBER
30 )
31 IS
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'Process_Settlement_WF';
34 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
35 l_return_status VARCHAR2(1);
36
37 BEGIN
38 IF OZF_DEBUG_HIGH_ON THEN
39 OZF_Utility_PVT.debug_message(l_full_name||': start');
40 END IF;
41
42 -------------------- initialize -----------------------
43 x_return_status := FND_API.g_ret_sts_success;
44
45 ------------------------ start -------------------------
46 BEGIN
47 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
48 p_claim_id => p_claim_id
49 ,p_prev_status => 'APPROVED'
50 ,p_curr_status => 'PENDING_CLOSE'
51 ,p_next_status => 'CLOSED'
52 );
53 EXCEPTION
54 WHEN OTHERS THEN
55 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
56 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
57 FND_MSG_PUB.Add;
58 RAISE FND_API.g_exc_unexpected_error;
59 END;
60
61 IF OZF_DEBUG_HIGH_ON THEN
62 OZF_Utility_PVT.debug_message(l_full_name||': end');
63 END IF;
64 EXCEPTION
65 WHEN FND_API.g_exc_unexpected_error THEN
66 x_return_status := FND_API.g_ret_sts_unexp_error ;
67
68 WHEN OTHERS THEN
69 x_return_status := FND_API.g_ret_sts_unexp_error ;
70 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
71 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
72 END IF;
73
74 END Process_Settlement_WF;
75
76
77 /*=======================================================================*
78 | Procedure
79 | Check_Transaction_Balance
80 |
81 | NOTES
82 |
83 | HISTORY
84 | 23-JUL-2002 mchang Create.
85 *=======================================================================*/
86 PROCEDURE Check_Transaction_Balance(
87 p_customer_trx_id IN NUMBER
88 ,p_claim_amount IN NUMBER
89 ,p_claim_number IN VARCHAR2
90 ,x_return_status OUT NOCOPY VARCHAR2
91 )
92 IS
93 l_invoice_balance_due NUMBER;
94 l_trx_number AR_PAYMENT_SCHEDULES_ALL.TRX_NUMBER%TYPE;
95
96 CURSOR csr_invoice_balance(cv_trx_id IN NUMBER) IS
97 SELECT SUM(amount_due_remaining), trx_number
98 FROM ar_payment_schedules pay
99 WHERE customer_trx_id = cv_trx_id
100 GROUP BY trx_number;
101
102 BEGIN
103 -- Initialize API return status to sucess
104 x_return_status := FND_API.g_ret_sts_success;
105
106 -- Check Claim Line number
107 OPEN csr_invoice_balance(p_customer_trx_id);
108 FETCH csr_invoice_balance INTO l_invoice_balance_due,l_trx_number;
109 CLOSE csr_invoice_balance;
110
111 IF p_claim_amount > l_invoice_balance_due THEN
112 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
113 FND_MESSAGE.set_name('OZF', 'OZF_SETL_INVOICE_BAL_ERR');
114 FND_MESSAGE.set_token('TRX_NUMBER', l_trx_number);
115 FND_MSG_PUB.add;
116 END IF;
117 RAISE FND_API.G_EXC_ERROR;
118 END IF;
119
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 x_return_status := FND_API.g_ret_sts_error;
123 WHEN OTHERS THEN
124 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
125 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CHK_TRANS_BAL_UERR');
126 FND_MSG_PUB.add;
127 END IF;
128 x_return_status := FND_API.g_ret_sts_unexp_error;
129 END Check_Transaction_Balance;
130
131
132
133 /*=======================================================================*
134 | Procedure
135 | Reopen_Claim_for_Completion
136 |
137 | Return
138 | x_claim_rec
139 |
140 | NOTES
141 |
142 | HISTORY
143 | 08-JUL-2002 mchang Create.
144 *=======================================================================*/
145 PROCEDURE Reopen_Claim_for_Completion(
146 x_return_status OUT NOCOPY VARCHAR2
147 ,x_msg_count OUT NOCOPY NUMBER
148 ,x_msg_data OUT NOCOPY VARCHAR2
149
150 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
151 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
152 )
153 IS
154 l_return_status VARCHAR2(1);
155 l_api_name CONSTANT VARCHAR2(30) := 'Reopen_Claim_for_Completion';
156
157 BEGIN
158 -- Initialize API return status to sucess
159 x_return_status := FND_API.g_ret_sts_success;
160
161 x_claim_rec := p_claim_rec;
162
163 -- move amount_settled back to amount_remaining, and calculated acctd_amount_remaining also.
164 x_claim_rec.amount_settled := 0;
165
166 x_claim_rec.acctd_amount_settled := 0;
167
168 x_claim_rec.amount_remaining := x_claim_rec.amount - x_claim_rec.amount_adjusted - x_claim_rec.amount_settled - NVL(x_claim_rec.tax_amount, 0);
169
170 OZF_UTILITY_PVT.Convert_Currency(
171 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
172 P_FROM_CURRENCY => p_claim_rec.currency_code,
173 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
174 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
175 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
176 P_AMOUNT => x_claim_rec.amount_remaining,
177 X_RETURN_STATUS => l_return_status,
178 X_ACC_AMOUNT => x_claim_rec.acctd_amount_remaining,
179 X_RATE => x_claim_rec.exchange_rate
180 );
181 IF l_return_status = FND_API.g_ret_sts_error THEN
182 RAISE FND_API.g_exc_error;
183 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
184 RAISE FND_API.g_exc_unexpected_error;
185 END IF;
186
187 x_claim_rec.settled_by := NULL;
188 x_claim_rec.settled_date := NULL;
189
190 x_claim_rec.approved_by := NULL;
191 x_claim_rec.approved_date := NULL;
192
193 x_claim_rec.payment_status := NULL;
194
195 EXCEPTION
196 WHEN FND_API.G_EXC_ERROR THEN
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200 WHEN OTHERS THEN
201 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
202 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
203 END IF;
204 x_return_status := FND_API.g_ret_sts_unexp_error;
205
206 END Reopen_Claim_for_Completion;
207
208 ---------------------------------------------------------------------
209 -- PROCEDURE
210 -- Check_Create_Trd_Prfl
211 --
212 -- PURPOSE
213 -- For 'CHECK' and 'CONTRA_CHARGE' payment_method, Create a trade profile
214 -- for a customer if it does not has a trade profile existing.
215 --
216 -- PARAMETERS
217 -- p_claim_id
218 --
219 -- NOTES
220 --
221 ----------------------------------------------------------------------
222 PROCEDURE Check_Create_Trd_Prfl (
223 p_claim_id IN NUMBER
224 ,x_return_status OUT NOCOPY VARCHAR2
225 ,x_msg_count IN NUMBER
226 ,x_msg_data IN VARCHAR2
227 )
228 IS
229 l_api_version CONSTANT NUMBER := 1.0;
230 l_api_name CONSTANT VARCHAR2(30) := 'Check_Create_Trd_Prfl';
231 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
232 l_return_status VARCHAR2(1);
233 l_msg_count NUMBER;
234 l_msg_data VARCHAR2(2000);
235
236 --//Bugfix : 8774738
237 CURSOR trd_prfl_count_csr(p_id IN NUMBER) is
238 SELECT NVL(COUNT(trade_profile_id), 0)
239 FROM ozf_cust_trd_prfls cust, hz_cust_accounts hz
240 WHERE cust.party_id = hz.party_id
241 AND hz.cust_account_id = p_id;
242
243 l_count NUMBER := 0;
244 l_trd_prfl OZF_Trade_Profile_PVT.trade_profile_rec_type;
245 l_trade_profile_id NUMBER;
246
247 CURSOR sys_parameter_csr IS
248 SELECT autopay_flag
249 , autopay_periodicity
250 , autopay_periodicity_type
251 , days_due
252 FROM ozf_sys_parameters;
253
254 l_autopay_flag VARCHAR2(1);
255 l_autopay_periodicity NUMBER;
256 l_autopay_periodicity_type VARCHAR2(30);
257 l_days_due NUMBER;
258
259 CURSOR cust_info_csr (p_id in NUMBER)IS
260 SELECT payment_method
261 , cust_account_id
262 , cust_billto_acct_site_id
263 , vendor_id
264 , vendor_site_id
265 FROM ozf_claims
266 WHERE claim_id = p_id;
267
268 l_payment_method VARCHAR2(30);
269 l_cust_account_id NUMBER;
270 l_cust_billto_acct_site_id NUMBER;
271 l_vendor_id NUMBER;
272 l_vendor_site_id NUMBER;
273
274 BEGIN
275 IF OZF_DEBUG_HIGH_ON THEN
276 OZF_Utility_PVT.debug_message(l_full_name||' : start');
277 END IF;
278
279 -- Initialize API return status to sucess
280 x_return_status := FND_API.g_ret_sts_success;
281
282 OPEN cust_info_csr(p_claim_id);
283 FETCH cust_info_csr INTO l_payment_method
284 , l_cust_account_id
285 , l_cust_billto_acct_site_id
286 , l_vendor_id
287 , l_vendor_site_id;
288 CLOSE cust_info_csr;
289
290 IF l_payment_method IN ('CHECK', 'CONTRA_CHARGE','EFT','WIRE','AP_DEBIT','AP_DEFAULT') THEN
291 OPEN trd_prfl_count_csr (l_cust_account_id);
292 FETCH trd_prfl_count_csr INTO l_count;
293 CLOSE trd_prfl_count_csr;
294
295 -- IF customer doesn't have a trade profile, create one.
296 IF l_count = 0 THEN
297 OPEN sys_parameter_csr;
298 FETCH sys_parameter_csr INTO l_autopay_flag
299 , l_autopay_periodicity
300 , l_autopay_periodicity_type
301 , l_days_due;
302 CLOSE sys_parameter_csr;
303
304 l_trd_prfl.autopay_flag := l_autopay_flag;
305 l_trd_prfl.autopay_periodicity := l_autopay_periodicity;
306 l_trd_prfl.autopay_periodicity_type := l_autopay_periodicity_type;
307 l_trd_prfl.days_due := l_days_due;
308 l_trd_prfl.cust_account_id := l_cust_account_id;
309 l_trd_prfl.site_use_id := l_cust_billto_acct_site_id;
310 l_trd_prfl.vendor_id := l_vendor_id;
311 l_trd_prfl.vendor_site_id := l_vendor_site_id;
312 l_trd_prfl.payment_method := l_payment_method;
313
314 OZF_Trade_Profile_PVT.Create_Trade_Profile(
315 p_api_version_number => l_api_version,
316 p_init_msg_list => FND_API.G_FALSE,
317 p_commit => FND_API.G_FALSE,
318 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
319 x_return_status => l_return_status,
320 x_msg_count => l_msg_count,
321 x_msg_data => l_msg_data,
322 p_trade_profile_rec => l_trd_prfl,
323 x_trade_profile_id => l_trade_profile_id
324 );
325 IF l_return_status = FND_API.g_ret_sts_error THEN
326 RAISE FND_API.g_exc_error;
327 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
328 RAISE FND_API.g_exc_unexpected_error;
329 END IF;
330 END IF;
331 END IF;
332
333 IF OZF_DEBUG_HIGH_ON THEN
334 OZF_Utility_PVT.debug_message(l_full_name||' : end');
335 END IF;
336
337 EXCEPTION
338 WHEN FND_API.G_EXC_ERROR THEN
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 WHEN OTHERS THEN
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
345 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
346 END IF;
347
348 END Check_Create_Trd_Prfl;
349
350
351 ---------------------------------------------------------------------
352 -- PROCEDURE
353 -- Remove_Utilization
354 --
355 -- NOTES
356 --
357 -- HISTORY
358 -- 03/22/2001 mchang Create.
359 -- 10/24/2001 mchang Remove_Utilization: Init claim_line rec before update_claim_line
360 ---------------------------------------------------------------------
361 PROCEDURE Remove_Utilization (
362 x_return_status OUT NOCOPY VARCHAR2
363 ,x_msg_count OUT NOCOPY NUMBER
364 ,x_msg_data OUT NOCOPY VARCHAR2
365
366 ,p_claim_id IN NUMBER
367 )
368 IS
369 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Utilization';
370 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
371 l_api_version CONSTANT NUMBER := 1.0;
372 l_return_status VARCHAR2(1);
373
374 CURSOR csr_claim_line_asso(cv_claim_id IN NUMBER) IS
375 SELECT claim_line_id
376 , object_version_number
377 FROM ozf_claim_lines
378 WHERE claim_id = cv_claim_id;
379
380 TYPE claim_line_asso_tbl IS TABLE OF csr_claim_line_asso%ROWTYPE
381 INDEX BY BINARY_INTEGER;
382 l_claim_line_asso claim_line_asso_tbl;
383 l_counter NUMBER :=1;
384 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
385 l_line_obj_ver NUMBER;
386
387 BEGIN
388 IF OZF_DEBUG_HIGH_ON THEN
389 OZF_Utility_PVT.debug_message(l_full_name||' : start');
390 END IF;
391
392 -- Initialize API return status to sucess
393 x_return_status := FND_API.g_ret_sts_success;
394
395 OPEN csr_claim_line_asso(p_claim_id);
396 LOOP
397 FETCH csr_claim_line_asso INTO l_claim_line_asso(l_counter);
398 EXIT WHEN csr_claim_line_asso%NOTFOUND;
399 l_counter := l_counter + 1;
400 END LOOP;
401 CLOSE csr_claim_line_asso;
402
403 IF l_claim_line_asso.count > 0 THEN
404 FOR i IN 1..l_claim_line_asso.count LOOP
405 OZF_Claim_Line_Pvt.Init_Claim_Line_Rec(
406 x_claim_line_rec => l_claim_line_rec
407 );
408
409 l_claim_line_rec.claim_line_id := l_claim_line_asso(i).claim_line_id;
410 l_claim_line_rec.object_version_number := l_claim_line_asso(i).object_version_number;
411 l_claim_line_rec.earnings_associated_flag := 'F';
412
413 OZF_CLAIM_LINE_PVT.Update_Claim_Line(
414 p_api_version => l_api_version
415 ,p_init_msg_list => FND_API.g_false
416 ,p_commit => FND_API.g_false
417 ,p_validation_level => FND_API.g_valid_level_full
418 ,x_return_status => l_return_status
419 ,x_msg_count => x_msg_count --ninarasi fix for bug 14333514
420 ,x_msg_data => x_msg_data
421 ,p_claim_line_rec => l_claim_line_rec
422 ,x_object_version => l_line_obj_ver
423 );
424 IF l_return_status = FND_API.g_ret_sts_error THEN
425 RAISE FND_API.g_exc_error;
426 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
427 RAISE FND_API.g_exc_unexpected_error;
428 END IF;
429 END LOOP;
430 END IF;
431
432 IF OZF_DEBUG_HIGH_ON THEN
433 OZF_Utility_PVT.debug_message(l_full_name||' : end');
434 END IF;
435
436 EXCEPTION
437 WHEN FND_API.G_EXC_ERROR THEN
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 WHEN OTHERS THEN
442 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
443 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
444 END IF;
445 x_return_status := FND_API.g_ret_sts_unexp_error;
446
447 END Remove_Utilization;
448
449
450 ---------------------------------------------------------------------
451 -- PROCEDURE
452 -- Duplicate_Claim_for_Completion
453 --
454 -- NOTES
455 --
456 -- HISTORY
457 -- 04/10/2001 mchang Create.
458 ---------------------------------------------------------------------
459 PROCEDURE Duplicate_Claim_for_Completion (
460 x_return_status OUT NOCOPY VARCHAR2
461 ,x_msg_count OUT NOCOPY NUMBER
462 ,x_msg_data OUT NOCOPY VARCHAR2
463
464 ,p_claim_id IN NUMBER
465 )
466 IS
467 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Claim_for_Completion';
468 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
469
470 l_return_status VARCHAR2(1);
471
472 BEGIN
473 IF OZF_DEBUG_HIGH_ON THEN
474 OZF_Utility_PVT.debug_message(l_full_name||' : start');
475 END IF;
476
477 -- Initialize API return status to sucess
478 x_return_status := FND_API.g_ret_sts_success;
479
480 Remove_Utilization (
481 x_return_status => l_return_status
482 ,x_msg_count => x_msg_count
483 ,x_msg_data => x_msg_data
484 ,p_claim_id => p_claim_id
485
486 );
487 IF l_return_status = FND_API.g_ret_sts_error THEN
488 RAISE FND_API.g_exc_error;
489 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
490 RAISE FND_API.g_exc_unexpected_error;
491 END IF;
492
493 IF OZF_DEBUG_HIGH_ON THEN
494 OZF_Utility_PVT.debug_message(l_full_name||' : end');
495 END IF;
496 EXCEPTION
497 WHEN FND_API.G_EXC_ERROR THEN
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
501 WHEN OTHERS THEN
502 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
503 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
504 END IF;
505 x_return_status := FND_API.g_ret_sts_unexp_error;
506
507 END Duplicate_Claim_for_Completion;
508
509
510 ---------------------------------------------------------------------
511 -- PROCEDURE
512 -- Cancel_Claim_for_Completion
513 --
514 -- NOTES
515 --
516 -- HISTORY
517 -- 03/26/2001 mchang Create.
518 ---------------------------------------------------------------------
519 PROCEDURE Cancel_Claim_for_Completion (
520 x_return_status OUT NOCOPY VARCHAR2
521 ,x_msg_count OUT NOCOPY NUMBER
522 ,x_msg_data OUT NOCOPY VARCHAR2
523
524 ,p_claim_id IN NUMBER
525 )
526 IS
527 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_Claim_for_Completion';
528 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
529
530 l_return_status VARCHAR2(1);
531
532 BEGIN
533 IF OZF_DEBUG_HIGH_ON THEN
534 OZF_Utility_PVT.debug_message(l_full_name||' : start');
535 END IF;
536
537 -- Initialize API return status to sucess
538 x_return_status := FND_API.g_ret_sts_success;
539
540 Remove_Utilization (
541 x_return_status => l_return_status
542 ,x_msg_count => x_msg_count
543 ,x_msg_data => x_msg_data
544 ,p_claim_id => p_claim_id
545
546 );
547 IF l_return_status = FND_API.g_ret_sts_error THEN
548 RAISE FND_API.g_exc_error;
549 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
550 RAISE FND_API.g_exc_unexpected_error;
551 END IF;
552
553 IF OZF_DEBUG_HIGH_ON THEN
554 OZF_Utility_PVT.debug_message(l_full_name||' : end');
555 END IF;
556 EXCEPTION
557 WHEN FND_API.G_EXC_ERROR THEN
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 WHEN OTHERS THEN
562 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
563 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
564 END IF;
565 x_return_status := FND_API.g_ret_sts_unexp_error;
566
567 END Cancel_Claim_for_Completion;
568
569
570 ---------------------------------------------------------------------
571 -- PROCEDURE
572 -- Cancel_Claim_for_Settlement
573 --
574 -- NOTES
575 --
576 -- HISTORY
577 -- 10/29/2001 mchang Create.
578 ---------------------------------------------------------------------
579 PROCEDURE Cancel_Claim_for_Settlement (
580 x_return_status OUT NOCOPY VARCHAR2
581 ,x_msg_count OUT NOCOPY NUMBER
582 ,x_msg_data OUT NOCOPY VARCHAR2
583
584 ,p_claim_id IN NUMBER
585 ,p_prev_status IN VARCHAR2
586 ,p_curr_status IN VARCHAR2
587 )
588 IS
589 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_Claim_for_Settlement';
590 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
591
592 l_return_status VARCHAR2(1);
593
594 CURSOR csr_claim_class(cv_claim_id IN NUMBER) IS
595 SELECT claim_class
596 FROM ozf_claims
597 WHERE claim_id = cv_claim_id;
598
599 l_claim_class VARCHAR2(30);
600
601 BEGIN
602 IF OZF_DEBUG_HIGH_ON THEN
603 OZF_Utility_PVT.debug_message(l_full_name||' : start');
604 END IF;
605
606 -- Initialize API return status to sucess
607 x_return_status := FND_API.g_ret_sts_success;
608 /*
609 OPEN csr_claim_class(p_claim_id);
610 FETCH csr_claim_class INTO l_claim_class;
611 CLOSE csr_claim_class;
612
613 IF l_claim_class = G_DEDUCTION_CLASS_NAME THEN
614 --------- Settlement Wrokflow : Send Notificatino to AR ---------------
615 BEGIN
616 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
617 p_claim_id => p_claim_id
618 ,p_prev_status => p_prev_status
619 ,p_curr_status => 'CANCELLED'
620 ,p_next_status => p_curr_status
621 );
622 EXCEPTION
623 WHEN OTHERS THEN
624 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
625 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
626 FND_MSG_PUB.Add;
627 RAISE FND_API.g_exc_unexpected_error;
628 END;
629 END IF;
630 */
631 IF OZF_DEBUG_HIGH_ON THEN
632 OZF_Utility_PVT.debug_message(l_full_name||' : end');
633 END IF;
634 EXCEPTION
635 WHEN FND_API.G_EXC_ERROR THEN
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 WHEN OTHERS THEN
640 IF OZF_DEBUG_LOW_ON THEN
641 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
642 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
643 FND_MSG_PUB.Add;
644 END IF;
645 x_return_status := FND_API.g_ret_sts_unexp_error;
646
647 END Cancel_Claim_for_Settlement;
648
649
650 ---------------------------------------------------------------------
651 -- PROCEDURE
652 -- Reject_Claim_for_Completion
653 --
654 -- NOTES
655 -- 1. Update amount_settled, acctd_amount_settled
656 -- , amount_remaining, acctd_amount_remaining
657 --
658 -- HISTORY
659 -- 03/26/2001 mchang Create.
660 -- 02/07/2001 mchang revert amount_remaining to amount_settled while claim status from REJECT to OPEN.
661 ---------------------------------------------------------------------
662 PROCEDURE Reject_Claim_for_Completion (
663 x_return_status OUT NOCOPY VARCHAR2
664 ,x_msg_count OUT NOCOPY NUMBER
665 ,x_msg_data OUT NOCOPY VARCHAR2
666
667 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
668 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
669 )
670 IS
671 l_api_name CONSTANT VARCHAR2(30) := 'Reject_Claim_for_Completion';
672 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
673
674 l_return_status VARCHAR2(1);
675
676 BEGIN
677 IF OZF_DEBUG_HIGH_ON THEN
678 OZF_Utility_PVT.debug_message(l_full_name||' : start');
679 END IF;
680
681 -- Initialize API return status to sucess
682 x_return_status := FND_API.g_ret_sts_success;
683 x_claim_rec := p_claim_rec;
684
685 -- move amount_settled back to amount_remaining, and calculated acctd_amount_remaining also.
686 x_claim_rec.amount_settled := 0;
687
688 x_claim_rec.acctd_amount_settled := 0;
689
690 x_claim_rec.amount_remaining := x_claim_rec.amount - x_claim_rec.amount_adjusted - x_claim_rec.amount_settled - NVL(x_claim_rec.tax_amount, 0);
691
692 OZF_UTILITY_PVT.Convert_Currency(
693 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
694 P_FROM_CURRENCY => p_claim_rec.currency_code,
695 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
696 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
697 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
698 P_AMOUNT => x_claim_rec.amount_remaining,
699 X_RETURN_STATUS => l_return_status,
700 X_ACC_AMOUNT => x_claim_rec.acctd_amount_remaining,
701 X_RATE => x_claim_rec.exchange_rate
702 );
703 IF l_return_status = FND_API.g_ret_sts_error THEN
704 RAISE FND_API.g_exc_error;
705 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
706 RAISE FND_API.g_exc_unexpected_error;
707 END IF;
708
709 IF OZF_DEBUG_HIGH_ON THEN
710 OZF_Utility_PVT.debug_message(l_full_name||' : end');
711 END IF;
712 EXCEPTION
713 WHEN FND_API.G_EXC_ERROR THEN
714 x_return_status := FND_API.G_RET_STS_ERROR;
715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 WHEN OTHERS THEN
718 x_return_status := FND_API.g_ret_sts_unexp_error;
719 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
720 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
721 END IF;
722
723 END Reject_Claim_for_Completion;
724
725
726 ---------------------------------------------------------------------
727 -- PROCEDURE
728 -- Reject_Claim_for_Settlement
729 --
730 -- NOTES
731 --
732 -- HISTORY
733 -- 10/29/2001 mchang Create.
734 ---------------------------------------------------------------------
735 PROCEDURE Reject_Claim_for_Settlement (
736 x_return_status OUT NOCOPY VARCHAR2
737 ,x_msg_count OUT NOCOPY NUMBER
738 ,x_msg_data OUT NOCOPY VARCHAR2
739
740 ,p_claim_id IN NUMBER
741 ,p_prev_status IN VARCHAR2
742 ,p_curr_status IN VARCHAR2
743 )
744 IS
745 l_api_name CONSTANT VARCHAR2(30) := 'Reject_Claim_for_Settlement';
746 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
747
748 l_return_status VARCHAR2(1);
749
750 CURSOR csr_claim_class(cv_claim_id IN NUMBER) IS
751 SELECT claim_class
752 FROM ozf_claims
753 WHERE claim_id = cv_claim_id;
754
755 l_claim_class VARCHAR2(30);
756
757 BEGIN
758 IF OZF_DEBUG_HIGH_ON THEN
759 OZF_Utility_PVT.debug_message(l_full_name||' : start');
760 END IF;
761
762 -- Initialize API return status to sucess
763 x_return_status := FND_API.g_ret_sts_success;
764 /*
765 OPEN csr_claim_class(p_claim_id);
766 FETCH csr_claim_class INTO l_claim_class;
767 CLOSE csr_claim_class;
768
769 IF l_claim_class = G_DEDUCTION_CLASS_NAME THEN
770 --------- Settlement Wrokflow : Send Notificatino to AR ---------------
771 BEGIN
772 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
773 p_claim_id => p_claim_id
774 ,p_prev_status => p_prev_status
775 ,p_curr_status => 'REJECTED'
776 ,p_next_status => p_curr_status
777 );
778 EXCEPTION
779 WHEN OTHERS THEN
780 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
781 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
782 FND_MSG_PUB.Add;
783 RAISE FND_API.g_exc_unexpected_error;
784 END;
785 END IF;
786 */
787 IF OZF_DEBUG_HIGH_ON THEN
788 OZF_Utility_PVT.debug_message(l_full_name||' : end');
789 END IF;
790 EXCEPTION
791 WHEN FND_API.G_EXC_ERROR THEN
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 WHEN OTHERS THEN
796 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
797 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
798 END IF;
799 x_return_status := FND_API.g_ret_sts_unexp_error;
800
801 END Reject_Claim_for_Settlement;
802
803
804 ---------------------------------------------------------------------
805 -- PROCEDURE
806 -- Complete_Claim_for_Completion
807 --
808 -- NOTES
809 --
810 -- HISTORY
811 -- 26-MAR-2001 mchang Create.
812 -- 09-AUG-2001 mchang add validation: gl_date should fall into an open period if entered.
813 -- 24-JAN-2001 slkrishn added reason_code validation:
814 -- reason_code passed to AR should be
815 -- invoicing_reason for CM and
816 -- adjust_reason for Writeoff
817 ---------------------------------------------------------------------
818 PROCEDURE Complete_Claim_for_Completion (
819 x_return_status OUT NOCOPY VARCHAR2
820 ,x_msg_count OUT NOCOPY NUMBER
821 ,x_msg_data OUT NOCOPY VARCHAR2
822
823 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
824 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
825 )
826 IS
827 l_api_version CONSTANT NUMBER := 1.0;
828 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Claim_for_Completion';
829 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
830 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
831
832 BEGIN
833 IF OZF_DEBUG_HIGH_ON THEN
834 OZF_Utility_PVT.debug_message(l_full_name||' : start');
835 END IF;
836
837 -- Initialize API return status to sucess
838 x_return_status := FND_API.g_ret_sts_success;
839
840 x_claim_rec := p_claim_rec;
841
842 OZF_CLAIM_SETTLEMENT_VAL_PVT.Complete_Claim_Validation(
843 p_api_version => l_api_version
844 ,p_init_msg_list => FND_API.g_false
845 ,p_validation_level => FND_API.g_valid_level_full
846 ,x_return_status => l_return_status
847 ,x_msg_data => x_msg_data
848 ,x_msg_count => x_msg_count
849 ,p_claim_rec => p_claim_rec
850 ,x_claim_rec => x_claim_rec
851 );
852 IF l_return_status = FND_API.g_ret_sts_error THEN
853 RAISE FND_API.g_exc_error;
854 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
855 RAISE FND_API.g_exc_unexpected_error;
856 END IF;
857
858 IF OZF_DEBUG_HIGH_ON THEN
859 OZF_Utility_PVT.debug_message(l_full_name||' : end');
860 END IF;
861 EXCEPTION
862 WHEN FND_API.G_EXC_ERROR THEN
863 x_return_status := FND_API.G_RET_STS_ERROR;
864 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 WHEN OTHERS THEN
867 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
868 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
869 END IF;
870 x_return_status := FND_API.g_ret_sts_unexp_error;
871
872 END Complete_Claim_for_Completion;
873
874
875 ---------------------------------------------------------------------
876 -- PROCEDURE
877 -- Complete_Claim_for_Settlement
878 --
879 -- NOTES
880 --
881 -- HISTORY
882 -- 03/26/2001 mchang Create.
883 ---------------------------------------------------------------------
884 PROCEDURE Complete_Claim_for_Settlement (
885 x_return_status OUT NOCOPY VARCHAR2
886 ,x_msg_count OUT NOCOPY NUMBER
887 ,x_msg_data OUT NOCOPY VARCHAR2
888
889 ,p_claim_id IN NUMBER
890 )
891 IS
892 l_api_version CONSTANT NUMBER := 1.0;
893 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Claim_for_Settlement';
894 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
895 l_return_status VARCHAR2(1);
896
897 BEGIN
898 IF OZF_DEBUG_HIGH_ON THEN
899 OZF_Utility_PVT.debug_message(l_full_name||' : start');
900 END IF;
901 -- Initialize API return status to sucess
902 x_return_status := FND_API.g_ret_sts_success;
903
904 -- create trade profile if need
905 Check_Create_Trd_Prfl (
906 p_claim_id => p_claim_id
907 ,x_return_status => l_return_status
908 ,x_msg_count => x_msg_count
909 ,x_msg_data => x_msg_data
910 );
911 IF l_return_status = FND_API.g_ret_sts_error THEN
912 RAISE FND_API.g_exc_error;
913 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
914 RAISE FND_API.g_exc_unexpected_error;
915 END IF;
916
917 IF OZF_DEBUG_HIGH_ON THEN
918 OZF_Utility_PVT.debug_message(l_full_name||' : end');
919 END IF;
920
921 EXCEPTION
922 WHEN FND_API.G_EXC_ERROR THEN
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 WHEN OTHERS THEN
927 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
928 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
929 END IF;
930 x_return_status := FND_API.g_ret_sts_unexp_error;
931
932 END Complete_Claim_for_Settlement;
933
934
935 ---------------------------------------------------------------------
936 -- PROCEDURE
937 -- Approve_Claim_for_Completion
938 --
939 -- NOTES
940 -- 1. Update status_code, user_status_id
941 -- , amount_settled, acctd_amount_settled
942 -- , amount_remaining, acctd_amount_remaining
943 -- , settled_by, settled_date
944 --
945 -- HISTORY
946 -- 03/26/2001 mchang Create.
947 ---------------------------------------------------------------------
948 PROCEDURE Approve_Claim_for_Completion (
949 x_return_status OUT NOCOPY VARCHAR2
950 ,x_msg_count OUT NOCOPY NUMBER
951 ,x_msg_data OUT NOCOPY VARCHAR2
952
953 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
954 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
955 )
956 IS
957 l_api_version CONSTANT NUMBER := 1.0;
958 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Claim_for_Completion';
959 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
960
961 l_return_status VARCHAR2(1);
962
963 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
964 SELECT user_status_id
965 FROM ams_user_statuses_vl
966 WHERE system_status_type = 'OZF_CLAIM_STATUS'
967 AND default_flag = 'Y'
968 AND system_status_code = cv_status_code;
969
970 CURSOR csr_line_sum_amt(cv_claim_id IN NUMBER) IS
971 SELECT SUM(claim_currency_amount)
972 FROM ozf_claim_lines
973 WHERE claim_id = cv_claim_id;
974
975 CURSOR csr_claim_perf_appr_req(cv_custom_setup_id IN NUMBER) IS
976 SELECT NVL(attr_available_flag, 'N')
977 FROM ams_custom_setup_attr
978 WHERE custom_setup_id = cv_custom_setup_id
979 AND object_attribute = 'PAPR';
980
981
982 l_claim_amount_remaining NUMBER;
983 l_claim_lines_sum NUMBER;
984 l_perf_appr_req VARCHAR2(1);
985
986 BEGIN
987 IF OZF_DEBUG_HIGH_ON THEN
988 OZF_Utility_PVT.debug_message(l_full_name||' : start');
989 END IF;
990 -- Initialize API return status to sucess
991 x_return_status := FND_API.g_ret_sts_success;
992
993 x_claim_rec := p_claim_rec;
994
995 OPEN csr_claim_perf_appr_req(p_claim_rec.custom_setup_id);
996 FETCH csr_claim_perf_appr_req INTO l_perf_appr_req;
997 CLOSE csr_claim_perf_appr_req;
998
999 IF p_claim_rec.approved_flag = 'N' AND
1000 l_perf_appr_req = 'N' THEN
1001 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1002 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_PERF_APPR_REQ');
1003 FND_MSG_PUB.add;
1004 END IF;
1005 RAISE FND_API.g_exc_error;
1006 END IF;
1007
1008 -- status_code/user_status_id : update claim_status to 'PENDING_APPROVAL'
1009 OPEN csr_user_status_id('PENDING_APPROVAL');
1010 FETCH csr_user_status_id INTO x_claim_rec.user_status_id;
1011 CLOSE csr_user_status_id;
1012 x_claim_rec.status_code := 'PENDING_APPROVAL';
1013
1014 -- amount_settled
1015 -- R12.1 Enhancement : Checking for payment method ACCOUNTING_ONLY
1016 IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO','AP_DEBIT','CHECK','EFT','WIRE','AP_DEFAULT','RMA','ACCOUNTING_ONLY') THEN
1017 OPEN csr_line_sum_amt(p_claim_rec.claim_id);
1018 FETCH csr_line_sum_amt INTO x_claim_rec.amount_settled;
1019 CLOSE csr_line_sum_amt;
1020 ELSE
1021 x_claim_rec.amount_settled := p_claim_rec.amount_remaining;
1022 END IF;
1023
1024 -- acctd_amount_settled
1025 OZF_UTILITY_PVT.Convert_Currency(
1026 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
1027 P_FROM_CURRENCY => p_claim_rec.currency_code,
1028 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
1029 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
1030 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
1031 P_AMOUNT => x_claim_rec.amount_settled,
1032 X_RETURN_STATUS => l_return_status,
1033 X_ACC_AMOUNT => x_claim_rec.acctd_amount_settled,
1034 X_RATE => x_claim_rec.exchange_rate
1035 );
1036 IF l_return_status = FND_API.g_ret_sts_error THEN
1037 RAISE FND_API.g_exc_error;
1038 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1039 RAISE FND_API.g_exc_unexpected_error;
1040 END IF;
1041
1042 -- amount_remaining
1043 x_claim_rec.amount_remaining := x_claim_rec.amount - x_claim_rec.amount_adjusted - x_claim_rec.amount_settled - NVL(x_claim_rec.tax_amount, 0);
1044
1045 -- acctd_amount_remaining
1046 OZF_UTILITY_PVT.Convert_Currency(
1047 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
1048 P_FROM_CURRENCY => p_claim_rec.currency_code,
1049 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
1050 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
1051 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
1052 P_AMOUNT => x_claim_rec.amount_remaining,
1053 X_RETURN_STATUS => l_return_status,
1054 X_ACC_AMOUNT => x_claim_rec.acctd_amount_remaining,
1055 X_RATE => x_claim_rec.exchange_rate
1056 );
1057 IF l_return_status = FND_API.g_ret_sts_error THEN
1058 RAISE FND_API.g_exc_error;
1059 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1060 RAISE FND_API.g_exc_unexpected_error;
1061 END IF;
1062
1063 ------- amount remaining checking --------
1064 l_claim_amount_remaining := x_claim_rec.amount - x_claim_rec.amount_adjusted - x_claim_rec.amount_settled - NVL(x_claim_rec.tax_amount, 0);
1065 /*
1066 -- ABS(l_claim_amount_remaining) should be >= 0 now
1067
1068 IF ABS(l_claim_amount_remaining) THEN
1069 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1070 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AMT_ERR');
1071 FND_MSG_PUB.add;
1072 END IF;
1073 RAISE FND_API.g_exc_error;
1074 END IF;
1075 */
1076
1077 -- settled_by / settled_date
1078 x_claim_rec.settled_by := OZF_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
1079 x_claim_rec.settled_date := SYSDATE;
1080
1081 IF OZF_DEBUG_HIGH_ON THEN
1082 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1083 END IF;
1084 EXCEPTION
1085 WHEN FND_API.G_EXC_ERROR THEN
1086 x_return_status := FND_API.G_RET_STS_ERROR;
1087 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1089 WHEN OTHERS THEN
1090 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1091 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1092 END IF;
1093 x_return_status := FND_API.g_ret_sts_unexp_error;
1094
1095 END Approve_Claim_for_Completion;
1096
1097
1098 ---------------------------------------------------------------------
1099 -- PROCEDURE
1100 -- Approve_Claim_for_Settlement
1101 --
1102 -- NOTES
1103 --
1104 -- HISTORY
1105 -- 03/26/2001 mchang Create.
1106 ---------------------------------------------------------------------
1107 PROCEDURE Approve_Claim_for_Settlement (
1108 x_return_status OUT NOCOPY VARCHAR2
1109 ,x_msg_count OUT NOCOPY NUMBER
1110 ,x_msg_data OUT NOCOPY VARCHAR2
1111
1112 ,p_claim_id IN NUMBER
1113 ,p_prev_status IN VARCHAR2
1114 ,p_curr_status IN VARCHAR2
1115 )
1116 IS
1117 l_api_version CONSTANT NUMBER := 1.0;
1118 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Claim_for_Settlement';
1119 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1120
1121 l_return_status VARCHAR2(1);
1122
1123 l_object_version_number NUMBER;
1124 l_requester_id NUMBER; -- Fix for bug 4754509
1125 l_comments VARCHAR2(2000);
1126 l_orig_status_id NUMBER;
1127 l_orig_status_code VARCHAR2(30) := p_prev_status;
1128 l_new_status_id NUMBER;
1129 l_reject_status_code VARCHAR2(30) := 'REJECTED';
1130 l_reject_status_id NUMBER;
1131
1132 l_status_type VARCHAR2(30) := 'OZF_CLAIM_STATUS';
1133 l_approval_workflow VARCHAR2(30) := 'AMSGAPP';
1134 l_item_type VARCHAR2(30) := 'AMSGAPP';
1135 l_approval_type VARCHAR2(30);
1136 l_approval_require VARCHAR2(1);
1137 CURSOR claim_rec_csr(l_claim_id in number) IS
1138 SELECT object_version_number
1139 --, user_status_id
1140 , settled_by -- Fix for Bug 4754509
1141 , comments
1142 FROM ozf_claims
1143 WHERE claim_id = l_claim_id;
1144
1145 BEGIN
1146 IF OZF_DEBUG_HIGH_ON THEN
1147 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1148 END IF;
1149 -- Initialize API return status to sucess
1150 x_return_status := FND_API.g_ret_sts_success;
1151
1152 OPEN claim_rec_csr(p_claim_id);
1153 FETCH claim_rec_csr INTO l_object_version_number,
1154 --l_new_status_id,
1155 l_requester_id, -- Fix for Bug 4754509
1156 l_comments;
1157 CLOSE claim_rec_csr;
1158
1159 l_orig_status_id := OZF_UTILITY_PVT.get_default_user_status(
1160 l_status_type
1161 ,l_orig_status_code
1162 );
1163
1164 l_new_status_id := OZF_UTILITY_PVT.get_default_user_status(
1165 l_status_type
1166 ,p_curr_status
1167 );
1168
1169 l_reject_status_id := OZF_UTILITY_PVT.get_default_user_status(
1170 l_status_type
1171 ,l_reject_status_code
1172 );
1173 IF OZF_Claim_Accrual_PVT.Earnings_Approval_Required(p_claim_id) = FND_API.g_true THEN
1174 l_approval_type := 'EARNING';
1175 ELSIF OZF_Claim_Accrual_PVT.Perform_Approval_Required(p_claim_id) = FND_API.g_true THEN
1176 l_approval_type := 'PERFORMANCE';
1177 ELSE
1178 l_approval_type := 'CLAIM';
1179 END IF;
1180
1181 ----------------------------
1182 -- Call Approval Workflow --
1183 ----------------------------
1184 -- the approval API would start claim approval process
1185 AMS_GEN_APPROVAL_PVT.StartProcess(
1186 p_activity_type => 'CLAM'
1187 ,p_activity_id => p_claim_id
1188 ,p_approval_type => l_approval_type
1189 ,p_object_version_number => l_object_version_number
1190 ,p_orig_stat_id => l_orig_status_id
1191 ,p_new_stat_id => l_new_status_id
1192 ,p_reject_stat_id => l_reject_status_id
1193 ,p_requester_userid => l_requester_id -- Fix for Bug 4754509
1194 ,p_notes_from_requester => l_comments
1195 ,p_workflowprocess => l_approval_workflow
1196 ,p_item_type => l_item_type
1197 );
1198 IF l_return_status = FND_API.g_ret_sts_error THEN
1199 RAISE FND_API.g_exc_error;
1200 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1201 RAISE FND_API.g_exc_unexpected_error;
1202 END IF;
1203
1204 IF OZF_DEBUG_HIGH_ON THEN
1205 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1206 END IF;
1207 EXCEPTION
1208 WHEN FND_API.G_EXC_ERROR THEN
1209 x_return_status := FND_API.G_RET_STS_ERROR;
1210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1212 WHEN OTHERS THEN
1213 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1214 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1215 END IF;
1216 x_return_status := FND_API.g_ret_sts_unexp_error;
1217
1218 END Approve_Claim_for_Settlement;
1219
1220
1221 ---------------------------------------------------------------------
1222 -- PROCEDURE
1223 -- Create_Payment_for_Completion
1224 --
1225 -- NOTES
1226 -- 1. If General Approval Workflow skipped,
1227 -- Update amount_settled, acctd_amount_settled
1228 -- , amount_remaining, acctd_amount_remaining
1229 -- , settled_by, settled_date
1230 -- --2. Adjust over utilization for scan data
1231 -- 3. Default GL Date based on System Parameters
1232 -- 4. Credit Memo/Debit Memo: open balance amount checking
1233 -- 5. Credit Memo-Invoice settlement: Invoice balance checking.
1234 -- 6. Update user_status_id and status_code to PENDING_CLOSE
1235 -- 7. Update payment_status to PENDING.
1236 --
1237 -- HISTORY
1238 -- 04/04/2001 mchang Create.
1239 ---------------------------------------------------------------------
1240 PROCEDURE Create_Payment_for_Completion (
1241 x_return_status OUT NOCOPY VARCHAR2
1242 ,x_msg_count OUT NOCOPY NUMBER
1243 ,x_msg_data OUT NOCOPY VARCHAR2
1244
1245 ,p_approval_require IN VARCHAR2
1246 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1247 ,p_prev_status IN VARCHAR2
1248 ,p_curr_status IN VARCHAR2
1249
1250 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
1251 )
1252 IS
1253 l_api_version CONSTANT NUMBER := 1.0;
1254 l_api_name CONSTANT VARCHAR2(30) := 'Create_Payment_for_Completion';
1255 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1256 l_return_status VARCHAR2(1);
1257
1258 CURSOR csr_payment_term(cv_books_id IN NUMBER) IS
1259 SELECT ap_payment_term_id
1260 FROM ozf_sys_parameters
1261 WHERE set_of_books_id = cv_books_id;
1262
1263 CURSOR csr_get_gl_date_type(cv_set_of_books_id IN NUMBER) IS
1264 SELECT gl_date_type
1265 FROM ozf_sys_parameters
1266 WHERE set_of_books_id = cv_set_of_books_id;
1267
1268 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
1269 SELECT user_status_id
1270 FROM ams_user_statuses_vl
1271 WHERE system_status_type = 'OZF_CLAIM_STATUS'
1272 AND default_flag = 'Y'
1273 AND system_status_code = cv_status_code;
1274
1275 CURSOR csr_line_sum_amt(cv_claim_id IN NUMBER) IS
1276 SELECT SUM(claim_currency_amount)
1277 FROM ozf_claim_lines
1278 WHERE claim_id = cv_claim_id;
1279
1280 CURSOR csr_trx_balance(cv_customer_trx_id IN NUMBER) IS
1281 SELECT SUM(amount_due_remaining),
1282 invoice_currency_code
1283 FROM ar_payment_schedules
1284 WHERE customer_trx_id = cv_customer_trx_id
1285 GROUP BY invoice_currency_code;
1286
1287 l_gl_date_type VARCHAR2(30);
1288 l_claim_amount_remaining NUMBER;
1289 l_trx_balance NUMBER;
1290 l_process_setl_wf VARCHAR2(1);
1291 l_invoice_id NUMBER;
1292 l_deduction_type VARCHAR2(20);
1293 l_adj_util_result_status VARCHAR2(15);
1294 l_trx_currency VARCHAR2(15);
1295
1296 BEGIN
1297 IF OZF_DEBUG_HIGH_ON THEN
1298 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1299 END IF;
1300 -- Initialize API return status to sucess
1301 x_return_status := FND_API.g_ret_sts_success;
1302
1303 x_claim_rec := p_claim_rec;
1304
1305 /*----------------------------------------------------------
1306 | 1. If General Approval Workflow skipped,
1307 | Update claim settled/remaining amount
1308 | and settled by/date information
1309 |
1310 | mchang fix @11.5.9: the following logic needs to confirm for the status flow
1311 | PENDING_APPROVAL
1312 | --(approval workflow)--> CLOSED
1313 | -> PENDING_APPROVAL
1314 | --(adjust over util workflow)--> CLOSED
1315 *---------------------------------------------------------*/
1316 IF p_approval_require = 'N' AND
1317 p_prev_status <> 'APPROVED' THEN
1318 -- claim settled/remaining amount is been updated already
1319 -- for status order PENDING_APPROVAL -> (CLOSED) -> APPROVED
1320
1321 -- update amount_settled
1322 -- R12.1 Enhancement: Checking for ACCOUNTING_ONLY payment method
1323 IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO','AP_DEBIT','CHECK','EFT','WIRE','AP_DEFAULT','RMA','ACCOUNTING_ONLY') THEN
1324 OPEN csr_line_sum_amt(p_claim_rec.claim_id);
1325 FETCH csr_line_sum_amt INTO x_claim_rec.amount_settled;
1326 CLOSE csr_line_sum_amt;
1327 ELSE
1328 x_claim_rec.amount_settled := p_claim_rec.amount_remaining;
1329 END IF;
1330
1331 -- update acctd_amount_settled
1332 OZF_UTILITY_PVT.Convert_Currency(
1333 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
1334 P_FROM_CURRENCY => p_claim_rec.currency_code,
1335 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
1336 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
1337 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
1338 P_AMOUNT => x_claim_rec.amount_settled,
1339 X_RETURN_STATUS => l_return_status,
1340 X_ACC_AMOUNT => x_claim_rec.acctd_amount_settled,
1341 X_RATE => x_claim_rec.exchange_rate
1342 );
1343 IF l_return_status = FND_API.g_ret_sts_error THEN
1344 RAISE FND_API.g_exc_error;
1345 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1346 RAISE FND_API.g_exc_unexpected_error;
1347 END IF;
1348
1349 -- update amount_remaining
1350 x_claim_rec.amount_remaining := x_claim_rec.amount
1351 - x_claim_rec.amount_adjusted
1352 - x_claim_rec.amount_settled
1353 - NVL(x_claim_rec.tax_amount,0);
1354
1355 -- update acctd_amount_remaining
1356 OZF_UTILITY_PVT.Convert_Currency(
1357 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
1358 P_FROM_CURRENCY => p_claim_rec.currency_code,
1359 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
1360 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
1361 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
1362 P_AMOUNT => x_claim_rec.amount_remaining,
1363 X_RETURN_STATUS => l_return_status,
1364 X_ACC_AMOUNT => x_claim_rec.acctd_amount_remaining,
1365 X_RATE => x_claim_rec.exchange_rate
1366 );
1367 IF l_return_status = FND_API.g_ret_sts_error THEN
1368 RAISE FND_API.g_exc_error;
1369 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1370 RAISE FND_API.g_exc_unexpected_error;
1371 END IF;
1372
1373 ------ check amount_remaining ------
1374 -- l_claim_amount_remaining should be >= 0 now
1375 -- (ps: convert amount sign for overpayment)
1376 l_claim_amount_remaining := x_claim_rec.amount
1377 - x_claim_rec.amount_adjusted
1378 - x_claim_rec.amount_settled
1379 - NVL(x_claim_rec.tax_amount, 0);
1380 /*
1381 IF x_claim_rec.claim_class IN ('OVERPAYMENT', 'CHARGE') THEN
1382 l_claim_amount_remaining := l_claim_amount_remaining * -1;
1383 END IF;
1384
1385 IF (l_claim_amount_remaining < 0) THEN
1386 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1387 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AMT_ERR');
1388 FND_MSG_PUB.add;
1389 END IF;
1390 RAISE FND_API.g_exc_error;
1391 END IF;
1392 */
1393
1394 x_claim_rec.settled_by := OZF_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
1395 x_claim_rec.settled_date := SYSDATE;
1396
1397 END IF;
1398 /*
1399 -- settled_by/date needs to update for claim status order
1400 -- for status order PENDING_APPROVAL
1401 -- -> (CLOSED)
1402 -- -> APPROVED
1403 -- --(time gap)--> CLOSED
1404 IF p_prev_status = 'APPROVED' THEN
1405 x_claim_rec.settled_by := OZF_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
1406 x_claim_rec.settled_date := SYSDATE;
1407 END IF;
1408 */
1409
1410 -- -----------------------------------------------
1411 -- Referral Claim Status Order:
1412 -- OPEN --> (CLOSED) --> PENDING_APPROVAL <claim approval> -- (time gap) --> (CLOSED)
1413 -- --> PENDING_APPROVAL <partner approval> -- (time gap)
1414 -- --> (APPROVED) <ozf_partner_claim_grp> --> (CLOSED)
1415 -- --> PENDING_CLOSE --> CLOSED
1416 -- Referral Claim Payment Status Order:
1417 -- WAITING_ACCEPTANCE --> PENDING --> INTERFACED --> PAID
1418 -- ------------------------------------------------
1419 IF p_claim_rec.source_object_class = 'REFERRAL' AND
1420 p_claim_rec.source_object_id IS NOT NULL AND
1421 p_prev_status <> 'APPROVED' THEN
1422 /*----------------------------------------------------------
1423 | 2. Raise Business Event for Referral Claim Approval
1424 *---------------------------------------------------------*/
1425 Raise_Business_Event(
1426 p_api_version => l_api_version
1427 ,p_init_msg_list => FND_API.g_false
1428 ,p_commit => FND_API.g_false
1429 ,p_validation_level => FND_API.g_valid_level_full
1430 ,x_return_status => l_return_status
1431 ,x_msg_data => x_msg_data
1432 ,x_msg_count => x_msg_count
1433 ,p_claim_id => p_claim_rec.claim_id
1434 ,p_old_status => p_prev_status
1435 ,p_new_status => 'PENDING_APPROVAL'
1436 ,p_event_name => 'oracle.apps.ozf.claim.referralApproval'
1437 );
1438 IF l_return_status = FND_API.g_ret_sts_error THEN
1439 RAISE FND_API.g_exc_error;
1440 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1441 RAISE FND_API.g_exc_unexpected_error;
1442 END IF;
1443
1444 x_claim_rec.status_code := 'PENDING_APPROVAL';
1445 x_claim_rec.payment_status := 'WAITING_ACCEPTANCE';
1446
1447 -- -----------------------------------------------
1448 -- Mass Settlement
1449 -- ----------------------------------------------
1450 ELSIF p_claim_rec.payment_method = 'MASS_SETTLEMENT' THEN
1451 IF p_claim_rec.gl_date IS NULL THEN
1452 OPEN csr_get_gl_date_type(p_claim_rec.set_of_books_id);
1453 FETCH csr_get_gl_date_type INTO l_gl_date_type;
1454 CLOSE csr_get_gl_date_type;
1455
1456 IF l_gl_date_type = 'CLAIM_DATE' THEN
1457 x_claim_rec.gl_date := p_claim_rec.claim_date;
1458 ELSIF l_gl_date_type = 'DUE_DATE' THEN
1459 x_claim_rec.gl_date := p_claim_rec.due_date;
1460 ELSIF l_gl_date_type = 'SYSTEM_DATE' THEN
1461 x_claim_rec.gl_date := SYSDATE;
1462 END IF;
1463 END IF;
1464
1465 x_claim_rec.status_code := 'PENDING_CLOSE';
1466 x_claim_rec.payment_status := 'PENDING';
1467
1468 ELSE
1469 /*----------------------------------------------------------
1470 | 3. Adjust over utilization for scan data purpose.
1471 | Settlement(payment) will continue only when
1472 | Adjust_Fund_Utilization is fully completed.
1473 *---------------------------------------------------------*/
1474 Ozf_Claim_Accrual_Pvt.Adjust_Fund_Utilization(
1475 p_api_version => l_api_version
1476 ,p_init_msg_list => FND_API.g_false
1477 ,p_commit => FND_API.g_false
1478 ,p_validation_level => FND_API.g_valid_level_full
1479 ,x_return_status => l_return_status
1480 ,x_msg_count => x_msg_count
1481 ,x_msg_data => x_msg_data
1482 ,p_claim_id => p_claim_rec.claim_id
1483 ,p_mode => 'ADJ_FUND'
1484 ,x_next_status => l_adj_util_result_status
1485 );
1486 IF l_return_status = FND_API.g_ret_sts_error THEN
1487 RAISE FND_API.g_exc_error;
1488 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1489 RAISE FND_API.g_exc_unexpected_error;
1490 END IF;
1491
1492 IF l_adj_util_result_status = 'PENDING_APPROVAL' THEN
1493 x_claim_rec.status_code := 'PENDING_APPROVAL';
1494
1495 ELSIF l_adj_util_result_status = 'CLOSED' THEN
1496
1497 /*----------------------------------------------------------
1498 | 4. Defautl GL_Date: GL Date has to be defaulted based on the system parameters setup.
1499 *---------------------------------------------------------*/
1500 IF p_claim_rec.gl_date IS NULL THEN
1501 OPEN csr_get_gl_date_type(p_claim_rec.set_of_books_id);
1502 FETCH csr_get_gl_date_type INTO l_gl_date_type;
1503 CLOSE csr_get_gl_date_type;
1504
1505 IF l_gl_date_type = 'CLAIM_DATE' THEN
1506 x_claim_rec.gl_date := p_claim_rec.claim_date;
1507 END IF;
1508 IF l_gl_date_type = 'DUE_DATE' THEN
1509 x_claim_rec.gl_date := p_claim_rec.due_date;
1510 END IF;
1511 IF l_gl_date_type = 'SYSTEM_DATE' THEN
1512 x_claim_rec.gl_date := SYSDATE;
1513 END IF;
1514 END IF;
1515
1516 /*----------------------------------------------------------
1517 | 5. Previouse Open Credit Memo/Debit Memo: balance checking
1518 *---------------------------------------------------------*/
1519 IF p_claim_rec.payment_method IN ('PREV_CREDIT_MEMO', 'PREV_DEBIT_MEMO') THEN
1520 IF p_claim_rec.payment_reference_id IS NULL THEN
1521 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1522 FND_MESSAGE.set_name('OZF', 'OZF_PAY_REFERENCE_REQD');
1523 FND_MSG_PUB.add;
1524 END IF;
1525 RAISE FND_API.G_EXC_ERROR;
1526 ELSE
1527 OPEN csr_trx_balance(p_claim_rec.payment_reference_id);
1528 FETCH csr_trx_balance INTO l_trx_balance, l_trx_currency;
1529 CLOSE csr_trx_balance;
1530
1531 IF x_claim_rec.currency_code = l_trx_currency AND
1532 ABS(p_claim_rec.amount_remaining) > ABS(l_trx_balance) THEN
1533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1534 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CM_DM_OP_BAL_ERR');
1535 FND_MSG_PUB.add;
1536 END IF;
1537 RAISE FND_API.G_EXC_ERROR;
1538 END IF;
1539 END IF;
1540 END IF;
1541
1542 /*----------------------------------------------------------
1543 | 6. Credit Memo-Invoice settlement: invoice balance checking
1544 *---------------------------------------------------------*/
1545 IF p_claim_rec.payment_method = 'REG_CREDIT_MEMO' THEN
1546 -- Validation for non invoice deductions is done during payment.
1547 IF p_claim_rec.claim_class = 'CLAIM' OR
1548 ( p_claim_rec.claim_class = 'DEDUCTION' AND p_claim_rec.source_object_id IS NOT NULL ) THEN
1549 OZF_AR_VALIDATION_PVT.Validate_CreditTo_Information(
1550 p_claim_rec => p_claim_rec
1551 ,x_return_status => l_return_status
1552 );
1553 IF l_return_status = FND_API.g_ret_sts_error THEN
1554 RAISE FND_API.g_exc_error;
1555 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1556 RAISE FND_API.g_exc_unexpected_error;
1557 END IF;
1558 END IF;
1559 END IF;
1560
1561 /*----------------------------------------------------------
1562 | 7. Update payment_status and claim status_code by settlement method.
1563 *---------------------------------------------------------*/
1564 x_claim_rec.status_code := 'PENDING_CLOSE';
1565 /*----------------------------------------------------------
1566 | 8. Update payment_status to PENDING
1567 *---------------------------------------------------------*/
1568 x_claim_rec.payment_status := 'PENDING';
1569
1570 ELSE
1571 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1572 FND_MESSAGE.set_name('OZF', 'OZF_SETL_ADJ_OVER_UTIZ_ERR');
1573 FND_MSG_PUB.add;
1574 END IF;
1575 IF OZF_DEBUG_LOW_ON THEN
1576 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1577 FND_MESSAGE.Set_Token('TEXT', 'Status return from OZF_CLAIM_LINE_PVT.Adjust_Fund_Utilization = '||l_adj_util_result_status);
1578 FND_MSG_PUB.Add;
1579 END IF;
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1581 END IF; -- end of if l_adj_util_result_status
1582 END IF; -- enf of if referral claim approval
1583
1584 -- Set returning user_status_id
1585 OPEN csr_user_status_id(x_claim_rec.status_code);
1586 FETCH csr_user_status_id INTO x_claim_rec.user_status_id;
1587 CLOSE csr_user_status_id;
1588
1589 IF OZF_DEBUG_HIGH_ON THEN
1590 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1591 END IF;
1592 EXCEPTION
1593 WHEN FND_API.G_EXC_ERROR THEN
1594 x_return_status := FND_API.G_RET_STS_ERROR;
1595
1596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1598
1599 WHEN OTHERS THEN
1600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1601 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1602 END IF;
1603 x_return_status := FND_API.g_ret_sts_unexp_error;
1604
1605 END Create_Payment_for_Completion;
1606
1607
1608 ---------------------------------------------------------------------
1609 -- PROCEDURE
1610 -- Create_Payment_for_Settlement
1611 --
1612 -- NOTES
1613 --
1614 -- HISTORY
1615 -- 04/04/2001 mchang Create.
1616 -- 11/14/2001 mchang Call GL interface API to create GL entry for all settlement_method
1617 ---------------------------------------------------------------------
1618 PROCEDURE Create_Payment_for_Settlement (
1619 x_return_status OUT NOCOPY VARCHAR2
1620 ,x_msg_count OUT NOCOPY NUMBER
1621 ,x_msg_data OUT NOCOPY VARCHAR2
1622
1623 ,p_claim_id IN NUMBER
1624 ,p_prev_status IN VARCHAR2
1625 ,p_curr_status IN VARCHAR2
1626 )
1627 IS
1628 l_api_version CONSTANT NUMBER := 1.0;
1629 l_api_name CONSTANT VARCHAR2(30) := 'Create_Payment_for_Settlement';
1630 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1631 l_return_status VARCHAR2(1);
1632
1633 CURSOR csr_payment_term(cv_books_id IN NUMBER) IS
1634 SELECT ap_payment_term_id
1635 FROM ozf_sys_parameters
1636 WHERE set_of_books_id = cv_books_id;
1637
1638 --R12.1 Price Protection Enhancement : Added the source object class
1639 CURSOR csr_claim_settle(cv_claim_id IN NUMBER) IS
1640 SELECT claim_number
1641 , object_version_number
1642 , settled_date
1643 , effective_date
1644 , vendor_id
1645 , vendor_site_id
1646 , amount_settled
1647 , currency_code
1648 , exchange_rate
1649 , exchange_rate_type
1650 , exchange_rate_date
1651 , payment_method
1652 , set_of_books_id
1653 , gl_date
1654 , claim_class
1655 , payment_reference_id
1656 , source_object_class
1657 FROM ozf_claims
1658 WHERE claim_id = cv_claim_id;
1659
1660 CURSOR csr_payment_ref_settlement(cv_customer_trx_id IN NUMBER) IS
1661 SELECT cust_trx_type_id
1662 , trx_number
1663 , status
1664 FROM ar_payment_schedules
1665 WHERE customer_trx_id = cv_customer_trx_id;
1666
1667 l_automate_settlement VARCHAR2(1);
1668
1669 CURSOR promo_claim_csr(p_id IN NUMBER) IS
1670 SELECT NVL(SUM(lu.amount), 0)
1671 FROM ozf_claim_lines_util lu
1672 WHERE lu.claim_line_id IN (
1673 SELECT l.claim_line_id
1674 FROM ozf_claim_lines l
1675 WHERE l.claim_id = p_id);
1676
1677 l_asso_amount NUMBER;
1678 l_bg_process VARCHAR2(1) := 'N';
1679 l_bg_process_mode VARCHAR2(3) := 'Y';
1680 l_claim_settle csr_claim_settle%ROWTYPE;
1681 l_payment_term NUMBER;
1682 l_settlement_doc_rec OZF_SETTLEMENT_DOC_PVT.settlement_doc_rec_type;
1683
1684 -- Bug4308173
1685 CURSOR claim_gl_posting_csr(p_id in number) IS
1686 SELECT osp.post_to_gl
1687 FROM ozf_sys_parameters_all osp
1688 , ozf_claims_all oc
1689 WHERE osp.org_id = oc.org_id
1690 AND oc.claim_id = p_id;
1691 l_post_to_gl VARCHAR2(1);
1692
1693 l_event_id NUMBER;
1694 l_ccid NUMBER;
1695
1696
1697 BEGIN
1698 IF OZF_DEBUG_HIGH_ON THEN
1699 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1700 END IF;
1701
1702 -- Initialize API return status to sucess
1703 x_return_status := FND_API.g_ret_sts_success;
1704
1705 OPEN csr_claim_settle(p_claim_id);
1706 FETCH csr_claim_settle INTO l_claim_settle;
1707 CLOSE csr_claim_settle;
1708
1709 l_bg_process_mode := NVL(FND_PROFILE.value('OZF_CLAIM_SETL_ACCT_BG'),'Y');
1710
1711 OPEN promo_claim_csr(p_claim_id);
1712 FETCH promo_claim_csr INTO l_asso_amount;
1713 CLOSE promo_claim_csr;
1714
1715 OPEN claim_gl_posting_csr(p_claim_id);
1716 FETCH claim_gl_posting_csr INTO l_post_to_gl;
1717 CLOSE claim_gl_posting_csr;
1718
1719 -- Added for Multi Currency
1720
1721 IF OZF_DEBUG_HIGH_ON THEN
1722 OZF_Utility_PVT.debug_message('l_bg_process_mode 1:'||l_bg_process_mode);
1723 OZF_Utility_PVT.debug_message('l_asso_amount 1:'||l_asso_amount);
1724 OZF_Utility_PVT.debug_message('l_post_to_gl 1:'||l_post_to_gl);
1725 END IF;
1726
1727 IF l_bg_process_mode = 'Y' THEN
1728 IF l_asso_amount <> 0 AND NVL(l_post_to_gl,'F') = 'T' THEN -- Bug4308173
1729 l_bg_process := 'Y';
1730 ELSE
1731 l_bg_process := 'N';
1732 END IF;
1733 END IF;
1734
1735 -- Added for Multi Currency
1736 IF OZF_DEBUG_HIGH_ON THEN
1737 OZF_Utility_PVT.debug_message('l_bg_process_mode 2:'||l_bg_process_mode);
1738 OZF_Utility_PVT.debug_message('l_claim_settle.payment_method:'||l_claim_settle.payment_method);
1739 END IF;
1740
1741 --R12.1 Enhancement:Checking for claim which have accrual and payment method ACCOUNTING_ONLY
1742 IF ((l_bg_process = 'Y') and (l_claim_settle.payment_method <> 'ACCOUNTING_ONLY'))THEN
1743 IF OZF_DEBUG_HIGH_ON THEN
1744 OZF_Utility_PVT.debug_message(l_full_name||' : promo claim');
1745 END IF;
1746 -- promotional claims settled by workflow
1747 BEGIN
1748 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
1749 p_claim_id => p_claim_id
1750 ,p_prev_status => p_prev_status
1751 ,p_curr_status => 'PENDING_CLOSE'
1752 ,p_next_status => p_curr_status
1753 ,p_promotional_claim => 'Y'
1754 );
1755 EXCEPTION
1756 WHEN OTHERS THEN
1757 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1758 FND_MESSAGE.Set_Token('TEXT','Promo Err: ' || sqlerrm);
1759 FND_MSG_PUB.Add;
1760 RAISE FND_API.g_exc_unexpected_error;
1761 END;
1762 ELSE -- non promotional claims
1763 /*---------------------------------------------------------*
1764 | CHECK (AP) |
1765 *---------------------------------------------------------*/
1766 IF l_claim_settle.payment_method IN ( 'CHECK','EFT','WIRE','AP_DEFAULT','AP_DEBIT') THEN
1767
1768 --R12.1 Price Protection changes: Bypass GL for vendor claim
1769 -- Fix for bug 7443072
1770 -- fix for 7654529
1771 -- ER#9382547 ChRM-SLA Uptake: Removed the Event_ID, x_clear_code_combination_id
1772 -- Out Parameter and claim_class as IN Parameter
1773 IF ((l_claim_settle.source_object_class <> 'PPVENDOR'
1774 OR l_claim_settle.source_object_class <> 'PPINCVENDOR')
1775 OR l_claim_settle.source_object_class IS NULL) THEN
1776 OZF_GL_INTERFACE_PVT.Post_Claim_To_GL (
1777 p_api_version => 1.0,
1778 x_return_status => l_return_status,
1779 x_msg_data => x_msg_data,
1780 x_msg_count => x_msg_count,
1781 p_claim_id => p_claim_id,
1782 p_settlement_method => l_claim_settle.payment_method
1783 );
1784 END IF;
1785 IF l_return_status = FND_API.g_ret_sts_error THEN
1786 RAISE FND_API.g_exc_error;
1787 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1788 RAISE FND_API.g_exc_unexpected_error;
1789 END IF;
1790 IF OZF_DEBUG_HIGH_ON THEN
1791 OZF_Utility_PVT.debug_message('Accounting Event Id :'||l_event_id );
1792 OZF_Utility_PVT.debug_message('Code Combination Id :'||l_ccid );
1793 END IF;
1794
1795 OPEN csr_payment_term(l_claim_settle.set_of_books_id);
1796 FETCH csr_payment_term INTO l_payment_term;
1797 CLOSE csr_payment_term;
1798
1799 -- create AP invoice
1800 OZF_AP_INTERFACE_PVT.Create_AP_Invoice (
1801 p_api_version => l_api_version
1802 ,p_init_msg_list => FND_API.g_false
1803 ,p_commit => FND_API.g_false
1804 ,p_validation_level => FND_API.g_valid_level_full
1805 ,x_return_status => l_return_status
1806 ,x_msg_data => x_msg_data
1807 ,x_msg_count => x_msg_count
1808 ,p_claim_id => p_claim_id
1809 );
1810 IF l_return_status = FND_API.g_ret_sts_error THEN
1811 RAISE FND_API.g_exc_error;
1812 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1813 RAISE FND_API.g_exc_unexpected_error;
1814 END IF;
1815
1816 /*---------------------------------------------------------*
1817 | AR Settlement
1818 | CREDIT_MEMO / REG_CREDIT_MEMO / DEBIT_MEMO / ON_ACCT_CREDIT/ CHARGEBACK / WRITE_OFF
1819 *---------------------------------------------------------*/
1820 ELSIF l_claim_settle.payment_method IN ( 'CREDIT_MEMO'
1821 , 'REG_CREDIT_MEMO'
1822 , 'ON_ACCT_CREDIT'
1823 , 'DEBIT_MEMO'
1824 , 'CHARGEBACK'
1825 , 'WRITE_OFF'
1826 , 'PREV_OPEN_CREDIT'
1827 , 'PREV_OPEN_DEBIT'
1828 , 'ACCOUNTING_ONLY' --R12.1 Enhancement
1829 ) THEN
1830
1831 --R12.1 Price Protection: Bypass the Gl entry for Customer Claim
1832 -- fix for 7654529
1833 -- ER#9382547 ChRM-SLA Uptake: Removed the Event_ID, x_clear_code_combination_id
1834 -- Out Parameter and claim_class as IN Parameter
1835 IF (l_claim_settle.source_object_class <> 'PPCUSTOMER'
1836 OR l_claim_settle.source_object_class IS NULL) THEN
1837 OZF_GL_INTERFACE_PVT.Post_Claim_To_GL (
1838 p_api_version => 1.0,
1839 x_return_status => l_return_status,
1840 x_msg_data => x_msg_data,
1841 x_msg_count => x_msg_count,
1842 p_claim_id => p_claim_id,
1843 p_settlement_method => l_claim_settle.payment_method
1844 );
1845 END IF;
1846 IF l_return_status = FND_API.g_ret_sts_error THEN
1847 RAISE FND_API.g_exc_error;
1848 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1849 RAISE FND_API.g_exc_unexpected_error;
1850 END IF;
1851 IF OZF_DEBUG_HIGH_ON THEN
1852 OZF_Utility_PVT.debug_message('Accounting Event Id :'||l_event_id );
1853 OZF_Utility_PVT.debug_message('Code Combination Id :'||l_ccid );
1854 END IF;
1855
1856 l_automate_settlement := NVL(FND_PROFILE.value('OZF_CLAIM_USE_AR_AUTOMATION'), 'Y');
1857
1858 --R12.1 Enhancement: Checking for pyment_method as ACCOUNTING_ONLY
1859 IF ((l_automate_settlement = 'Y') OR (l_claim_settle.payment_method = 'ACCOUNTING_ONLY'))
1860 THEN
1861 OZF_AR_PAYMENT_PVT.Create_AR_Payment(
1862 p_api_version => l_api_version
1863 ,p_init_msg_list => FND_API.g_false
1864 ,p_commit => FND_API.g_false
1865 ,p_validation_level => FND_API.g_valid_level_full
1866 ,x_return_status => l_return_status
1867 ,x_msg_data => x_msg_data
1868 ,x_msg_count => x_msg_count
1869 ,p_claim_id => p_claim_id
1870 );
1871 IF l_return_status = FND_API.g_ret_sts_error THEN
1872 RAISE FND_API.g_exc_error;
1873 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1874 RAISE FND_API.g_exc_unexpected_error;
1875 END IF;
1876 ELSE
1877 Process_Settlement_WF(
1878 p_claim_id => p_claim_id
1879 ,x_return_status => l_return_status
1880 ,x_msg_data => x_msg_data
1881 ,x_msg_count => x_msg_count
1882 );
1883 IF l_return_status = FND_API.g_ret_sts_error THEN
1884 RAISE FND_API.g_exc_error;
1885 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1886 RAISE FND_API.g_exc_unexpected_error;
1887 END IF;
1888 END IF;
1889
1890 /*---------------------------------------------------------*
1891 | RMA (OM) |
1892 *---------------------------------------------------------*/
1893 ELSIF l_claim_settle.payment_method = 'RMA' THEN
1894 l_automate_settlement := NVL(FND_PROFILE.value('OZF_CLAIM_USE_OM_AUTOMATION'), 'Y');
1895
1896 IF l_automate_settlement = 'Y' THEN
1897 OZF_OM_PAYMENT_PVT.Create_OM_Payment(
1898 p_api_version => l_api_version
1899 ,p_init_msg_list => FND_API.g_false
1900 ,p_commit => FND_API.g_false
1901 ,p_validation_level => FND_API.g_valid_level_full
1902 ,x_return_status => l_return_status
1903 ,x_msg_data => x_msg_data
1904 ,x_msg_count => x_msg_count
1905 ,p_claim_id => p_claim_id
1906 );
1907 IF l_return_status = FND_API.g_ret_sts_error THEN
1908 RAISE FND_API.g_exc_error;
1909 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1910 RAISE FND_API.g_exc_unexpected_error;
1911 END IF;
1912 ELSE
1913 Process_Settlement_WF(
1914 p_claim_id => p_claim_id
1915 ,x_return_status => l_return_status
1916 ,x_msg_data => x_msg_data
1917 ,x_msg_count => x_msg_count
1918 );
1919 IF l_return_status = FND_API.g_ret_sts_error THEN
1920 RAISE FND_API.g_exc_error;
1921 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1922 RAISE FND_API.g_exc_unexpected_error;
1923 END IF;
1924 END IF;
1925
1926 /*---------------------------------------------------------*
1927 | CONTRA_CHARGE |
1928 *---------------------------------------------------------*/
1929 ELSIF l_claim_settle.payment_method = 'CONTRA_CHARGE' THEN
1930
1931 -- ER#9382547 ChRM-SLA Uptake: Removed the Event_ID, x_clear_code_combination_id
1932 -- Out Parameter and claim_class as IN Parameter
1933 OZF_GL_INTERFACE_PVT.Post_Claim_To_GL (
1934 p_api_version => 1.0,
1935 x_return_status => l_return_status,
1936 x_msg_data => x_msg_data,
1937 x_msg_count => x_msg_count,
1938 p_claim_id => p_claim_id,
1939 p_settlement_method => l_claim_settle.payment_method
1940 );
1941 IF l_return_status = FND_API.g_ret_sts_error THEN
1942 RAISE FND_API.g_exc_error;
1943 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1944 RAISE FND_API.g_exc_unexpected_error;
1945 END IF;
1946 IF OZF_DEBUG_HIGH_ON THEN
1947 OZF_Utility_PVT.debug_message('Accounting Event Id :'||l_event_id );
1948 OZF_Utility_PVT.debug_message('Code Combination Id :'||l_ccid );
1949 END IF;
1950
1951 -- Kickoff Settlement Workflow for contra charge
1952 Process_Settlement_WF(
1953 p_claim_id => p_claim_id
1954 ,x_return_status => l_return_status
1955 ,x_msg_data => x_msg_data
1956 ,x_msg_count => x_msg_count
1957 );
1958 IF l_return_status = FND_API.g_ret_sts_error THEN
1959 RAISE FND_API.g_exc_error;
1960 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1961 RAISE FND_API.g_exc_unexpected_error;
1962 END IF;
1963
1964 /*---------------------------------------------------------*
1965 | MASS_SETTLEMENT |
1966 *---------------------------------------------------------*/
1967 ELSIF l_claim_settle.payment_method = 'MASS_SETTLEMENT' THEN
1968 OZF_Mass_Settlement_PVT.Start_Mass_Payment(
1969 p_group_claim_id => p_claim_id
1970 ,x_return_status => l_return_status
1971 ,x_msg_data => x_msg_data
1972 ,x_msg_count => x_msg_count
1973 );
1974 IF l_return_status = FND_API.g_ret_sts_error THEN
1975 RAISE FND_API.g_exc_error;
1976 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1977 RAISE FND_API.g_exc_unexpected_error;
1978 END IF;
1979
1980 /*---------------------------------------------------------*
1981 | Non-seeded payment_method |
1982 *---------------------------------------------------------*/
1983 -- Ivoke Claim Settlement Workflow to support non-seeded payment_method
1984 ELSE
1985 Process_Settlement_WF(
1986 p_claim_id => p_claim_id
1987 ,x_return_status => l_return_status
1988 ,x_msg_data => x_msg_data
1989 ,x_msg_count => x_msg_count
1990 );
1991 IF l_return_status = FND_API.g_ret_sts_error THEN
1992 RAISE FND_API.g_exc_error;
1993 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1994 RAISE FND_API.g_exc_unexpected_error;
1995 END IF;
1996
1997 END IF;
1998
1999 END IF; -- end l_bg_process = 'Y'
2000
2001 IF OZF_DEBUG_HIGH_ON THEN
2002 OZF_Utility_PVT.debug_message(l_full_name||' : end');
2003 END IF;
2004 EXCEPTION
2005 WHEN FND_API.G_EXC_ERROR THEN
2006 x_return_status := FND_API.G_RET_STS_ERROR;
2007
2008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2010
2011 WHEN OTHERS THEN
2012 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2013 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2014 END IF;
2015 x_return_status := FND_API.g_ret_sts_unexp_error;
2016
2017 END Create_Payment_for_Settlement;
2018
2019
2020 ---------------------------------------------------------------------
2021 -- PROCEDURE
2022 -- Update_Claim_Remaining_Amount
2023 --
2024 -- PURPOSE
2025 --
2026 -- NOTES
2027 --
2028 -- HISTORY
2029 -- 28-MAR-2002 mchang Create.
2030 ---------------------------------------------------------------------
2031 PROCEDURE Update_Claim_Remaining_Amount(
2032 x_return_status OUT NOCOPY VARCHAR2
2033 ,x_msg_count OUT NOCOPY NUMBER
2034 ,x_msg_data OUT NOCOPY VARCHAR2
2035 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
2036 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
2037 )
2038 IS
2039 l_api_version CONSTANT NUMBER := 1.0;
2040 l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Remaining_Amount';
2041 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2042 l_return_status VARCHAR2(1);
2043
2044 CURSOR csr_calcuate_tax(cv_claim_id IN NUMBER) IS
2045 SELECT ABS(pay.tax_original)
2046 FROM ar_payment_schedules pay
2047 , ozf_settlement_docs sd
2048 WHERE pay.customer_trx_id = sd.settlement_id
2049 AND sd.claim_id = cv_claim_id;
2050
2051 CURSOR csr_sum_line_amount(cv_claim_id IN NUMBER) IS
2052 SELECT SUM(claim_currency_amount)
2053 FROM ozf_claim_lines
2054 WHERE claim_id = cv_claim_id;
2055
2056
2057 BEGIN
2058 IF OZF_DEBUG_HIGH_ON THEN
2059 OZF_Utility_PVT.debug_message(l_full_name||' : start');
2060 END IF;
2061 -- Initialize API return status to sucess
2062 x_return_status := FND_API.g_ret_sts_success;
2063
2064 x_claim_rec := p_claim_rec;
2065
2066 /*
2067 IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO') AND
2068 p_claim_rec.payment_reference_id IS NULL THEN -- calculate tax
2069 OPEN csr_sum_line_amount(p_claim_rec.claim_id);
2070 FETCH csr_sum_line_amount INTO x_claim_rec.amount_settled;
2071 CLOSE csr_sum_line_amount;
2072
2073 OPEN csr_calcuate_tax(p_claim_rec.claim_id);
2074 FETCH csr_calcuate_tax INTO x_claim_rec.tax_amount;
2075 CLOSE csr_calcuate_tax;
2076 END IF;
2077
2078 x_claim_rec.amount_remaining := x_claim_rec.amount - x_claim_rec.amount_adjusted - x_claim_rec.amount_settled - NVL(x_claim_rec.tax_amount, 0);
2079
2080 OZF_UTILITY_PVT.Convert_Currency(
2081 P_SET_OF_BOOKS_ID => p_claim_rec.set_of_books_id,
2082 P_FROM_CURRENCY => p_claim_rec.currency_code,
2083 P_CONVERSION_DATE => p_claim_rec.exchange_rate_date,
2084 P_CONVERSION_TYPE => p_claim_rec.exchange_rate_type,
2085 P_CONVERSION_RATE => p_claim_rec.exchange_rate,
2086 P_AMOUNT => x_claim_rec.amount_remaining,
2087 X_RETURN_STATUS => l_return_status,
2088 X_ACC_AMOUNT => x_claim_rec.acctd_amount_remaining,
2089 X_RATE => x_claim_rec.exchange_rate
2090 );
2091 IF l_return_status = FND_API.g_ret_sts_error THEN
2092 RAISE FND_API.g_exc_error;
2093 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2094 RAISE FND_API.g_exc_unexpected_error;
2095 END IF;
2096 */
2097
2098 IF OZF_DEBUG_HIGH_ON THEN
2099 OZF_Utility_PVT.debug_message(l_full_name||' : end');
2100 END IF;
2101 EXCEPTION
2102 WHEN FND_API.G_EXC_ERROR THEN
2103 x_return_status := FND_API.G_RET_STS_ERROR;
2104 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2106 WHEN OTHERS THEN
2107 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2108 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2109 END IF;
2110 x_return_status := FND_API.g_ret_sts_unexp_error;
2111
2112 END Update_Claim_Remaining_Amount;
2113
2114
2115 ---------------------------------------------------------------------
2116 -- PROCEDURE
2117 -- Dispose_Invalid_Status_Order
2118 --
2119 -- PURPOSE
2120 -- This procedure will populate error message for invalid claim status order.
2121 --
2122 -- NOTES
2123 --
2124 -- HISTORY
2125 -- 08-FEB-2002 mchang Create.
2126 ---------------------------------------------------------------------
2127 PROCEDURE Dispose_Invalid_Status_Order(
2128 p_prev_status IN VARCHAR2
2129 ,p_curr_status IN VARCHAR2
2130 )
2131 IS
2132 l_prev_status_meaning VARCHAR2(80);
2133 l_curr_status_meaning VARCHAR2(80);
2134
2135 CURSOR csr_status_meaning(c_status_code IN VARCHAR2) IS
2136 SELECT meaning
2137 FROM ozf_lookups
2138 WHERE lookup_type = 'OZF_CLAIM_STATUS'
2139 AND lookup_code = c_status_code;
2140
2141 BEGIN
2142 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2143 OPEN csr_status_meaning(p_prev_status);
2144 FETCH csr_status_meaning INTO l_prev_status_meaning;
2145 IF csr_status_meaning%NOTFOUND THEN
2146 CLOSE csr_status_meaning;
2147 l_prev_status_meaning := p_prev_status;
2148 ELSE
2149 CLOSE csr_status_meaning;
2150 END IF;
2151
2152 OPEN csr_status_meaning(p_curr_status);
2153 FETCH csr_status_meaning INTO l_curr_status_meaning;
2154 IF csr_status_meaning%NOTFOUND THEN
2155 CLOSE csr_status_meaning;
2156 l_curr_status_meaning := p_curr_status;
2157 ELSE
2158 CLOSE csr_status_meaning;
2159 END IF;
2160
2161 FND_MESSAGE.set_name('OZF', 'OZF_SETL_STATUS_RULE_ERR');
2162 FND_MESSAGE.set_token('PREV_STATUS', l_prev_status_meaning);
2163 FND_MESSAGE.set_token('CURR_STATUS', l_curr_status_meaning);
2164 FND_MSG_PUB.add;
2165 END IF;
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 IF csr_status_meaning%ISOPEN THEN
2169 CLOSE csr_status_meaning;
2170 END IF;
2171 END Dispose_Invalid_Status_Order;
2172
2173 /*=======================================================================*
2174 | PROCEDURE
2175 | Claim_Approval_Required
2176 |
2177 | RETURN
2178 | Y / N
2179 |
2180 | NOTES
2181 |
2182 | HISTORY
2183 | 09-AUG-2005 SSHIVALI Create
2184 *=======================================================================*/
2185 PROCEDURE Claim_Approval_Required(
2186 p_claim_id IN NUMBER
2187
2188 ,x_return_status OUT NOCOPY VARCHAR2
2189 ,x_msg_data OUT NOCOPY VARCHAR2
2190 ,x_msg_count OUT NOCOPY NUMBER
2191
2192 ,x_approval_require OUT NOCOPY VARCHAR2
2193 )
2194 IS
2195 CURSOR csr_check_approval_require(cv_custom_setup_id IN NUMBER) IS
2196 SELECT NVL(attr_available_flag, 'N')
2197 FROM ams_custom_setup_attr
2198 WHERE custom_setup_id = cv_custom_setup_id
2199 AND object_attribute = 'APPR';
2200
2201 l_approval_require VARCHAR2(1) := 'Y'; --//Bugfix : 8479176 , Changed from T to Y
2202 l_auto_wrtoff_appr_req VARCHAR2(1);
2203
2204 CURSOR csr_claim_approval_attr(cv_claim_id IN NUMBER) IS
2205 SELECT custom_setup_id
2206 , payment_method
2207 , write_off_flag
2208 , under_write_off_threshold
2209 , status_code
2210 FROM ozf_claims
2211 WHERE claim_id = cv_claim_id;
2212
2213 l_claim_approval_attr csr_claim_approval_attr%ROWTYPE;
2214 BEGIN
2215 OPEN csr_claim_approval_attr(p_claim_id);
2216 FETCH csr_claim_approval_attr INTO l_claim_approval_attr.custom_setup_id
2217 , l_claim_approval_attr.payment_method
2218 , l_claim_approval_attr.write_off_flag
2219 , l_claim_approval_attr.under_write_off_threshold
2220 , l_claim_approval_attr.status_code;
2221 CLOSE csr_claim_approval_attr;
2222
2223 OPEN csr_check_approval_require(l_claim_approval_attr.custom_setup_id);
2224 FETCH csr_check_approval_require INTO l_approval_require;
2225 CLOSE csr_check_approval_require;
2226
2227 -- ----------------------------------------------------------------------------
2228 -- Comments : Check if versus writeoff threshold is UNDER or OVER
2229 -- Notes : 1.Check value of Profile option to determine whether claims under
2230 -- the writeofff threshold will require approval:
2231 -- If YES, approval will be required as dictated by custom setup.
2232 -- If NO, approaval will not be required even if custom setup dictated approval.
2233 -- 2.For Claims over the write off threshold, users are allowed to include them
2234 -- in the auto-writeoffs. These claims will require approval as dictated by
2235 -- custom setup.
2236 -- ----------------------------------------------------------------------------
2237 IF l_claim_approval_attr.payment_method = 'WRITE_OFF' AND
2238 l_claim_approval_attr.write_off_flag = 'T' AND
2239 l_claim_approval_attr.under_write_off_threshold = 'UNDER' THEN
2240 l_auto_wrtoff_appr_req := FND_PROFILE.value('OZF_UNDER_WRITEOFF_THRESHOLD_APPROVAL');
2241 IF l_auto_wrtoff_appr_req = 'N' THEN
2242 l_approval_require := 'N';
2243 END IF;
2244 ELSIF l_claim_approval_attr.payment_method = 'MASS_SETTLEMENT' THEN
2245 l_approval_require := 'N';
2246 END IF;
2247
2248 x_approval_require := l_approval_require;
2249 EXCEPTION
2250 WHEN OTHERS THEN
2251 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2252 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CHK_APPR_UNEXPERR');
2253 FND_MSG_PUB.add;
2254 END IF;
2255 x_return_status := FND_API.g_ret_sts_unexp_error;
2256 END Claim_Approval_Required;
2257
2258
2259 ---------------------------------------------------------------------
2260 -- PROCEDURE
2261 -- Recalculate_FXGL
2262 --
2263 -- NOTES
2264 --
2265 -- HISTORY
2266 -- 05/26/2009 psomyaju Create.
2267 -- 06/08/2009 kpatro Updated.
2268 -- 06/25/2009 kpatro Changed the Parameter set
2269 -- Removed the updation of claim line and header
2270 -- 03/08/2010 kpatro ER#9382547 ChRM-SLA Uptake
2271 ---------------------------------------------------------------------
2272
2273 PROCEDURE Recalculate_FXGL ( p_claim_id NUMBER
2274 , p_claim_org_id NUMBER
2275 , p_settled_date DATE
2276 , p_claim_exchange_date IN OUT NOCOPY DATE
2277 , p_claim_rate IN OUT NOCOPY NUMBER
2278 , x_return_status OUT NOCOPY VARCHAR2
2279 )
2280 IS
2281
2282 l_api_version CONSTANT NUMBER := 1.0;
2283 l_api_name CONSTANT VARCHAR2(30) := 'Recalculate_FXGL';
2284 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2285 l_return_status VARCHAR2(1);
2286
2287 l_claim_line_id NUMBER;
2288 l_line_util_id NUMBER;
2289 l_claim_amount NUMBER;
2290 l_claim_line_amount NUMBER;
2291 l_cl_acctd_amount NUMBER;
2292 l_ln_acctd_amount NUMBER;
2293 l_line_util_amount NUMBER;
2294 l_claim_currency_code VARCHAR2(30);
2295 l_claim_settled_date DATE;
2296 l_claim_exc_rate NUMBER;
2297 l_claim_exc_type VARCHAR2(30);
2298 -- Fix for numeric or value error:
2299 --character to number conversion error --kpatro
2300 l_plan_currency_code VARCHAR2(30);
2301 l_fu_exc_type VARCHAR2(30);
2302 l_fu_exc_rate NUMBER;
2303 l_fu_exc_date DATE;
2304 l_lu_acctd_amount NUMBER;
2305 l_util_acctd_amount NUMBER;
2306 l_fxgl_amount NUMBER;
2307 l_functional_currency VARCHAR2(30);
2308
2309 CURSOR csr_function_currency IS
2310 SELECT gs.currency_code
2311 FROM gl_sets_of_books gs
2312 , ozf_sys_parameters org
2313 WHERE org.set_of_books_id = gs.set_of_books_id
2314 AND org.org_id = p_claim_org_id;
2315
2316 CURSOR c_claim_line_util_dtls IS
2317 SELECT lu.claim_line_util_id
2318 , lu.amount
2319 , lu.currency_code
2320 , lu.exchange_rate_type
2321 , lu.exchange_rate
2322 , fu.plan_currency_code
2323 , fu.exchange_rate_type
2324 , fu.exchange_rate
2325 , fu.exchange_rate_date
2326 FROM ozf_claim_lines_all ln
2327 , ozf_claim_lines_util_all lu
2328 , ozf_funds_utilized_all_b fu
2329 WHERE ln.claim_id = p_claim_id
2330 AND ln.claim_line_id = lu.claim_line_id
2331 AND lu.utilization_id = fu.utilization_id;
2332
2333 BEGIN
2334
2335 IF OZF_DEBUG_HIGH_ON THEN
2336 OZF_Utility_PVT.debug_message(l_full_name||' : start');
2337 OZF_Utility_PVT.debug_message('trunc(p_settled_date)' || trunc(p_settled_date));
2338 OZF_Utility_PVT.debug_message('trunc(p_claim_exchange_date)' || trunc(p_claim_exchange_date));
2339 OZF_Utility_PVT.debug_message('p_claim_rate' || p_claim_rate);
2340 END IF;
2341
2342 x_return_status := FND_API.g_ret_sts_success;
2343
2344 l_claim_settled_date := p_settled_date;
2345
2346 IF (trunc(p_settled_date) = trunc(p_claim_exchange_date)) THEN
2347 RETURN;
2348 END IF;
2349
2350
2351 OPEN csr_function_currency;
2352 FETCH csr_function_currency INTO l_functional_currency;
2353 CLOSE csr_function_currency;
2354
2355 -- Fix the issue numeric or value error: character to number conversion error --kpatro
2356 OPEN c_claim_line_util_dtls;
2357 LOOP
2358 FETCH c_claim_line_util_dtls INTO l_line_util_id
2359 , l_line_util_amount
2360 , l_claim_currency_code
2361 , l_claim_exc_type
2362 , l_claim_exc_rate
2363 , l_plan_currency_code
2364 , l_fu_exc_type
2365 , l_fu_exc_rate
2366 , l_fu_exc_date;
2367 EXIT WHEN c_claim_line_util_dtls%NOTFOUND;
2368
2369 -- ER#9382547 ChRM-SLA Uptake
2370 -- populate the claim line util exchange date when the claim currency
2371 -- and fucntional currency is same.
2372 IF (p_claim_rate = 1) THEN
2373
2374 p_claim_exchange_date := l_claim_settled_date;
2375 -- Update the claim line util table with the settled date.
2376 UPDATE ozf_claim_lines_util_all
2377 SET exchange_rate_date = l_claim_settled_date
2378 WHERE claim_line_util_id = l_line_util_id;
2379
2380 END IF;
2381
2382
2383
2384
2385 IF OZF_DEBUG_HIGH_ON THEN
2386 OZF_Utility_PVT.debug_message('p_claim_id = '||p_claim_id);
2387 OZF_Utility_PVT.debug_message('l_line_util_id = '||l_line_util_id);
2388 OZF_Utility_PVT.debug_message('l_line_util_amount = '||l_line_util_amount);
2389 OZF_Utility_PVT.debug_message('l_claim_currency_code = '||l_claim_currency_code);
2390 OZF_Utility_PVT.debug_message('l_claim_exc_rate = '||l_claim_exc_rate);
2391 OZF_Utility_PVT.debug_message('l_claim_exc_type = '||l_claim_exc_type);
2392 OZF_Utility_PVT.debug_message('l_plan_currency_code = '||l_plan_currency_code);
2393 OZF_Utility_PVT.debug_message('l_fu_exc_type = '||l_fu_exc_type);
2394 OZF_Utility_PVT.debug_message('l_fu_exc_rate = '||l_fu_exc_rate);
2395 OZF_Utility_PVT.debug_message('l_fu_exc_date = '||l_fu_exc_date);
2396 END IF;
2397
2398 IF l_claim_currency_code = l_plan_currency_code AND
2399 l_claim_currency_code <> l_functional_currency THEN
2400 IF l_claim_exc_rate <> l_fu_exc_rate AND l_fu_exc_rate IS NOT NULL THEN
2401
2402 OZF_UTILITY_PVT.Convert_Currency(
2403 p_from_currency => l_claim_currency_code
2404 ,p_to_currency => l_functional_currency
2405 ,p_conv_type => l_claim_exc_type
2406 ,p_conv_date => l_claim_settled_date
2407 ,p_from_amount => l_line_util_amount
2408 ,x_return_status => l_return_status
2409 ,x_to_amount => l_lu_acctd_amount
2410 ,x_rate => l_claim_exc_rate
2411 );
2412
2413 IF l_return_status = FND_API.g_ret_sts_error THEN
2414 RAISE FND_API.g_exc_error;
2415 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2416 RAISE FND_API.g_exc_unexpected_error;
2417 END IF;
2418
2419 OZF_UTILITY_PVT.Convert_Currency(
2420 p_from_currency => l_plan_currency_code
2421 ,p_to_currency => l_functional_currency
2422 ,p_conv_type => l_fu_exc_type
2423 ,p_conv_date => l_fu_exc_date
2424 ,p_from_amount => l_line_util_amount
2425 ,x_return_status => l_return_status
2426 ,x_to_amount => l_util_acctd_amount
2427 ,x_rate => l_fu_exc_rate
2428 );
2429
2430 IF l_return_status = FND_API.g_ret_sts_error THEN
2431 RAISE FND_API.g_exc_error;
2432 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2433 RAISE FND_API.g_exc_unexpected_error;
2434 END IF;
2435
2436 l_fxgl_amount := l_lu_acctd_amount - l_util_acctd_amount;
2437
2438 IF OZF_DEBUG_HIGH_ON THEN
2439 OZF_Utility_PVT.debug_message('l_line_util_id = '||l_line_util_id);
2440 OZF_Utility_PVT.debug_message('l_lu_acctd_amount = '||l_lu_acctd_amount);
2441 OZF_Utility_PVT.debug_message('l_util_acctd_amount = '||l_util_acctd_amount);
2442 OZF_Utility_PVT.debug_message('l_fxgl_amount = '||l_fxgl_amount);
2443 OZF_Utility_PVT.debug_message('l_claim_settled_date = '||l_claim_settled_date);
2444 OZF_Utility_PVT.debug_message('l_claim_exc_rate = '||l_claim_exc_rate);
2445 END IF;
2446
2447 UPDATE ozf_claim_lines_util_all
2448 SET acctd_amount = l_lu_acctd_amount
2449 , exchange_rate_date = l_claim_settled_date
2450 , exchange_rate = l_claim_exc_rate
2451 -- , utilized_acctd_amount = l_util_acctd_amount
2452 , fxgl_acctd_amount = l_fxgl_amount
2453 WHERE claim_line_util_id = l_line_util_id;
2454 END IF;
2455 END IF;
2456 END LOOP;
2457
2458 CLOSE c_claim_line_util_dtls;
2459
2460 p_claim_exchange_date := l_claim_settled_date;
2461 p_claim_rate := l_claim_exc_rate;
2462
2463
2464 IF OZF_DEBUG_HIGH_ON THEN
2465 OZF_Utility_PVT.debug_message(l_full_name||' : end');
2466 END IF;
2467
2468 EXCEPTION
2469 WHEN FND_API.G_EXC_ERROR THEN
2470 x_return_status := FND_API.G_RET_STS_ERROR;
2471
2472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474
2475 WHEN OTHERS THEN
2476 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2477 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2478 END IF;
2479 x_return_status := FND_API.g_ret_sts_unexp_error;
2480 END Recalculate_FXGL;
2481
2482
2483 ---------------------------------------------------------------------
2484 -- PROCEDURE
2485 -- Complete_Settlement
2486 --
2487 -- NOTES
2488 --
2489 -- HISTORY
2490 -- 03/22/2001 mchang Create.
2491 ---------------------------------------------------------------------
2492 PROCEDURE Complete_Settlement(
2493 p_api_version IN NUMBER
2494 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2495 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2496 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2497
2498 ,x_return_status OUT NOCOPY VARCHAR2
2499 ,x_msg_data OUT NOCOPY VARCHAR2
2500 ,x_msg_count OUT NOCOPY NUMBER
2501
2502 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
2503 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
2504 )
2505 IS
2506 l_api_version CONSTANT NUMBER := 1.0;
2507 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Settlement';
2508 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2509 l_return_status VARCHAR2(1);
2510
2511 l_curr_status VARCHAR2(30);
2512 l_prev_status VARCHAR2(30);
2513
2514 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
2515 l_complete_claim_rec OZF_CLAIM_PVT.claim_rec_type;
2516 l_approval_require VARCHAR2(1);
2517
2518 CURSOR csr_claim_prev_status(cv_claim_id IN NUMBER) IS
2519 SELECT status_code
2520 FROM ozf_claims
2521 WHERE claim_id = cv_claim_id;
2522
2523 -- Added For Rule Based Settlement ER
2524 CURSOR csr_rule_based_approval(p_claim_id IN NUMBER) IS
2525 SELECT NVL(approval_matched_credit, 'F'),NVL(approval_new_credit,'F')
2526 FROM ozf_sys_parameters os, ozf_claims_all oc
2527 WHERE oc.org_id = os.org_id
2528 and oc.claim_id = p_claim_id;
2529
2530 l_app_match_cr VARCHAR2(1);
2531 l_app_new_cr VARCHAR2(1);
2532 -- End For Rule Based Settlement ER
2533
2534 BEGIN
2535 --------------------- initialize -----------------------
2536 SAVEPOINT Complete_Settlement;
2537
2538 IF OZF_DEBUG_HIGH_ON THEN
2539 OZF_Utility_PVT.debug_message(l_full_name||' : start');
2540 END IF;
2541
2542 IF FND_API.to_boolean(p_init_msg_list) THEN
2543 FND_MSG_PUB.initialize;
2544 END IF;
2545
2546 IF NOT FND_API.compatible_api_call(
2547 l_api_version,
2548 p_api_version,
2549 l_api_name,
2550 g_pkg_name
2551 ) THEN
2552 RAISE FND_API.g_exc_unexpected_error;
2553 END IF;
2554
2555 -- Initialize API return status to sucess
2556 x_return_status := FND_API.g_ret_sts_success;
2557
2558 OPEN csr_claim_prev_status(p_claim_rec.claim_id);
2559 FETCH csr_claim_prev_status INTO l_prev_status;
2560 CLOSE csr_claim_prev_status;
2561
2562 -- Added For Rule Based Settlement ER
2563 OPEN csr_rule_based_approval(p_claim_rec.claim_id);
2564 FETCH csr_rule_based_approval INTO l_app_match_cr,l_app_new_cr;
2565 CLOSE csr_rule_based_approval;
2566
2567 IF OZF_DEBUG_HIGH_ON THEN
2568 OZF_Utility_PVT.debug_message('l_app_match_cr :'||l_app_match_cr);
2569 OZF_Utility_PVT.debug_message('l_app_new_cr :'|| l_app_new_cr);
2570 OZF_Utility_PVT.debug_message('p_claim_rec.settled_from :'|| p_claim_rec.settled_from);
2571 OZF_Utility_PVT.debug_message('p_claim_rec.payment_reference_number :'|| p_claim_rec.payment_reference_number);
2572 OZF_Utility_PVT.debug_message('p_claim_rec.payment_reference_id:'|| p_claim_rec.payment_reference_id);
2573 END IF;
2574
2575 -- End of Rule Based Settlement
2576 l_curr_status := p_claim_rec.status_code;
2577
2578 l_claim_rec := p_claim_rec;
2579
2580 --------------------- Start -----------------------
2581 /*-------------------------+
2582 | 1. Complete Claim
2583 +-------------------------*/
2584 --nepanda : fix for bug # 9539273 - issue #3
2585 IF p_claim_rec.status_code IN ('NEW', 'OPEN') AND
2586 p_claim_rec.payment_method IS NOT NULL AND
2587 p_claim_rec.payment_method <> FND_API.G_MISS_CHAR THEN
2588 OZF_CLAIM_SETTLEMENT_VAL_PVT.Complete_Claim(
2589 p_api_version => l_api_version
2590 ,p_init_msg_list => FND_API.g_false
2591 ,p_validation_level => FND_API.g_valid_level_full
2592 ,x_return_status => l_return_status
2593 ,x_msg_data => x_msg_data
2594 ,x_msg_count => x_msg_count
2595 ,p_x_claim_rec => l_claim_rec
2596 );
2597 IF l_return_status = FND_API.g_ret_sts_error THEN
2598 RAISE FND_API.g_exc_error;
2599 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2600 RAISE FND_API.g_exc_unexpected_error;
2601 END IF;
2602 END IF;
2603
2604 x_claim_rec := l_claim_rec;
2605
2606 /*-------------------------+
2607 | 2. Complete Settlement
2608 +-------------------------*/
2609 IF l_prev_status <> l_curr_status THEN
2610 -------- CANCELLED --------
2611 -- NEW ---> CANCELLED
2612 -- OPEN ---> CANCELLED
2613 -- COMPLETE ---> CANCELLED
2614 -- 11.5.10 subsequent receipt application ehancement
2615 -- PENDING_APPROVAL ---> CANCELLED
2616 -- APPROVED ---> CANCELLED
2617 -- REJECTED ---> CANCELLED
2618 IF l_curr_status = 'CANCELLED' THEN
2619 IF l_prev_status IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED', 'PENDING_APPROVAL', 'APPROVED') THEN
2620 Cancel_Claim_for_Completion (
2621 x_return_status => l_return_status
2622 ,x_msg_count => x_msg_count
2623 ,x_msg_data => x_msg_data
2624 ,p_claim_id => l_claim_rec.claim_id
2625 );
2626 IF l_return_status = FND_API.g_ret_sts_error THEN
2627 RAISE FND_API.g_exc_error;
2628 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2629 RAISE FND_API.g_exc_unexpected_error;
2630 END IF;
2631 ELSE
2632 Dispose_Invalid_Status_Order(
2633 p_prev_status => l_prev_status
2634 ,p_curr_status => l_curr_status
2635 );
2636 RAISE FND_API.g_exc_error;
2637 END IF;
2638
2639 -------- REJECTED --------
2640 -- NEW ---> REJECTED
2641 -- OPEN ---> REJECTED
2642 -- COMPLETE ---> REJECTED
2643 -- PENDING_APPROVAL ---> REJECTED
2644 ELSIF l_curr_status = 'REJECTED' THEN
2645 IF l_prev_status IN ('NEW', 'OPEN', 'COMPLETE', 'PENDING_APPROVAL') THEN
2646 Reject_Claim_for_Completion (
2647 x_return_status => l_return_status
2648 ,x_msg_count => x_msg_count
2649 ,x_msg_data => x_msg_data
2650 ,p_claim_rec => l_claim_rec
2651 ,x_claim_rec => x_claim_rec
2652 );
2653 IF l_return_status = FND_API.g_ret_sts_error THEN
2654 RAISE FND_API.g_exc_error;
2655 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2656 RAISE FND_API.g_exc_unexpected_error;
2657 END IF;
2658 ELSE
2659 Dispose_Invalid_Status_Order(
2660 p_prev_status => l_prev_status
2661 ,p_curr_status => l_curr_status
2662 );
2663 RAISE FND_API.g_exc_error;
2664 END IF;
2665
2666 -------- DUPLICATE --------
2667 -- NEW ---> DUPLICATE
2668 -- OPEN ---> DUPLICATE
2669 ELSIF l_curr_status = 'DUPLICATE' THEN
2670 IF l_prev_status IN ('OPEN', 'COMPLETE') THEN
2671 Duplicate_Claim_for_Completion (
2672 x_return_status => l_return_status
2673 ,x_msg_count => x_msg_count
2674 ,x_msg_data => x_msg_data
2675 ,p_claim_id => l_claim_rec.claim_id
2676 );
2677 IF l_return_status = FND_API.g_ret_sts_error THEN
2678 RAISE FND_API.g_exc_error;
2679 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2680 RAISE FND_API.g_exc_unexpected_error;
2681 END IF;
2682 ELSE
2683 Dispose_Invalid_Status_Order(
2684 p_prev_status => l_prev_status
2685 ,p_curr_status => l_curr_status
2686 );
2687 RAISE FND_API.g_exc_error;
2688 END IF;
2689 -------- COMPLETE --------
2690 -- OPEN ---> COMPLETE
2691 ELSIF l_curr_status = 'COMPLETE' THEN
2692 IF l_prev_status = 'OPEN' THEN
2693 Complete_Claim_for_Completion (
2694 x_return_status => l_return_status
2695 ,x_msg_count => x_msg_count
2696 ,x_msg_data => x_msg_data
2697 ,p_claim_rec => l_claim_rec
2698 ,x_claim_rec => x_claim_rec
2699 );
2700 IF l_return_status = FND_API.g_ret_sts_error THEN
2701 RAISE FND_API.g_exc_error;
2702 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2703 RAISE FND_API.g_exc_unexpected_error;
2704 END IF;
2705 ELSIF l_prev_status <> 'PENDING_APPROVAL' THEN
2706 Dispose_Invalid_Status_Order(
2707 p_prev_status => l_prev_status
2708 ,p_curr_status => l_curr_status
2709 );
2710 RAISE FND_API.g_exc_error;
2711 END IF;
2712
2713 -------- CLOSED --------
2714 ELSIF l_curr_status = 'CLOSED' THEN
2715 -- Added For Rule Based Settlement ER
2716
2717 IF NVL(p_claim_rec.settled_from,'X') <> 'RULEBASED' THEN
2718 Claim_Approval_Required(
2719 p_claim_id => p_claim_rec.claim_id
2720 ,x_return_status => l_return_status
2721 ,x_msg_data => x_msg_data
2722 ,x_msg_count => x_msg_count
2723 ,x_approval_require => l_approval_require
2724 );
2725 IF l_return_status = FND_API.g_ret_sts_error THEN
2726 RAISE FND_API.g_exc_error;
2727 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2728 RAISE FND_API.g_exc_unexpected_error;
2729 END IF;
2730 ELSE
2731 -- For Credit Match
2732 IF (p_claim_rec.payment_reference_number IS NOT NULL
2733 AND p_claim_rec.payment_reference_id IS NOT NULL
2734 AND l_app_match_cr = 'T') THEN
2735 l_approval_require := 'Y';
2736 -- For Accrual match
2737 ELSIF(p_claim_rec.payment_reference_number IS NULL
2738 AND p_claim_rec.payment_reference_id IS NULL
2739 AND l_app_new_cr = 'T') THEN
2740 l_approval_require := 'Y';
2741 END IF;
2742
2743 IF OZF_DEBUG_HIGH_ON THEN
2744 OZF_Utility_PVT.debug_message('l_approval_require:' || l_approval_require);
2745 END IF;
2746 IF l_approval_require = 'Y' THEN
2747 Approve_Claim_for_Completion (
2748 x_return_status => l_return_status
2749 ,x_msg_count => x_msg_count
2750 ,x_msg_data => x_msg_data
2751 ,p_claim_rec => l_claim_rec
2752 ,x_claim_rec => x_claim_rec
2753 );
2754 IF l_return_status = FND_API.g_ret_sts_error THEN
2755 RAISE FND_API.g_exc_error;
2756 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2757 RAISE FND_API.g_exc_unexpected_error;
2758 END IF;
2759 END IF;
2760
2761 END IF;
2762
2763 /*-------------------------------*
2764 | OPEN --> CLOSED :Completion
2765 *-------------------------------*/
2766 IF l_prev_status = 'OPEN' THEN
2767 Complete_Claim_for_Completion (
2768 x_return_status => l_return_status
2769 ,x_msg_count => x_msg_count
2770 ,x_msg_data => x_msg_data
2771 ,p_claim_rec => l_claim_rec
2772 ,x_claim_rec => l_complete_claim_rec
2773 );
2774 IF l_return_status = FND_API.g_ret_sts_error THEN
2775 RAISE FND_API.g_exc_error;
2776 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2777 RAISE FND_API.g_exc_unexpected_error;
2778 END IF;
2779
2780 l_claim_rec := l_complete_claim_rec;
2781
2782
2783 IF l_approval_require = 'Y' THEN
2784 Approve_Claim_for_Completion (
2785 x_return_status => l_return_status
2786 ,x_msg_count => x_msg_count
2787 ,x_msg_data => x_msg_data
2788 ,p_claim_rec => l_claim_rec
2789 ,x_claim_rec => x_claim_rec
2790 );
2791 IF l_return_status = FND_API.g_ret_sts_error THEN
2792 RAISE FND_API.g_exc_error;
2793 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2794 RAISE FND_API.g_exc_unexpected_error;
2795 END IF;
2796 ELSE -- skip approval workflow
2797 Create_Payment_for_Completion (
2798 x_return_status => l_return_status
2799 ,x_msg_count => x_msg_count
2800 ,x_msg_data => x_msg_data
2801 ,p_approval_require => 'N'
2802 ,p_claim_rec => l_claim_rec
2803 ,p_prev_status => l_prev_status
2804 ,p_curr_status => l_curr_status
2805 ,x_claim_rec => x_claim_rec
2806 );
2807 IF l_return_status = FND_API.g_ret_sts_error THEN
2808 RAISE FND_API.g_exc_error;
2809 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2810 RAISE FND_API.g_exc_unexpected_error;
2811 END IF;
2812 END IF;
2813
2814 /*-----------------------------------*
2815 | COMPLETE --> CLOSED :Completion
2816 *-----------------------------------*/
2817 ELSIF l_prev_status = 'COMPLETE' THEN
2818 IF l_approval_require = 'Y' THEN
2819 Approve_Claim_for_Completion (
2820 x_return_status => l_return_status
2821 ,x_msg_count => x_msg_count
2822 ,x_msg_data => x_msg_data
2823 ,p_claim_rec => l_claim_rec
2824 ,x_claim_rec => x_claim_rec
2825 );
2826 IF l_return_status = FND_API.g_ret_sts_error THEN
2827 RAISE FND_API.g_exc_error;
2828 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2829 RAISE FND_API.g_exc_unexpected_error;
2830 END IF;
2831 ELSE -- skip approval workflow
2832 Create_Payment_for_Completion (
2833 x_return_status => l_return_status
2834 ,x_msg_count => x_msg_count
2835 ,x_msg_data => x_msg_data
2836 ,p_approval_require => 'N'
2837 ,p_claim_rec => l_claim_rec
2838 ,p_prev_status => l_prev_status
2839 ,p_curr_status => l_curr_status
2840 ,x_claim_rec => x_claim_rec
2841 );
2842 IF l_return_status = FND_API.g_ret_sts_error THEN
2843 RAISE FND_API.g_exc_error;
2844 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2845 RAISE FND_API.g_exc_unexpected_error;
2846 END IF;
2847 END IF;
2848
2849 /*----------------------------------------------------*
2850 | APPROVED / PENDING_APPROVAL --> CLOSED :Completion
2851 *----------------------------------------------------*/
2852 ELSIF l_prev_status = 'APPROVED' OR
2853 l_prev_status = 'PENDING_APPROVAL' THEN
2854 Create_Payment_for_Completion (
2855 x_return_status => l_return_status
2856 ,x_msg_count => x_msg_count
2857 ,x_msg_data => x_msg_data
2858 ,p_approval_require => 'Y'
2859 ,p_claim_rec => l_claim_rec
2860 ,p_prev_status => l_prev_status
2861 ,p_curr_status => l_curr_status
2862 ,x_claim_rec => x_claim_rec
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 /*----------------------------------------*
2871 | PENDING_CLOSE --> CLOSED :Completion
2872 *----------------------------------------*/
2873 ELSIF l_prev_status = 'PENDING_CLOSE' THEN
2874 Update_Claim_Remaining_Amount (
2875 x_return_status => l_return_status
2876 ,x_msg_count => x_msg_count
2877 ,x_msg_data => x_msg_data
2878 ,p_claim_rec => l_claim_rec
2879 ,x_claim_rec => x_claim_rec
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 ELSE
2888 IF l_prev_status <> 'PENDING_CLOSE' THEN
2889 Dispose_Invalid_Status_Order(
2890 p_prev_status => l_prev_status
2891 ,p_curr_status => l_curr_status
2892 );
2893 RAISE FND_API.g_exc_error;
2894 END IF;
2895 END IF;
2896
2897 -------- PENDING_APPROVAL (for Mass Settlement) --------
2898 ELSIF l_curr_status = 'PENDING_APPROVAL' THEN
2899 IF l_prev_status = 'COMPLETE' THEN
2900 Approve_Claim_for_Completion (
2901 x_return_status => l_return_status
2902 ,x_msg_count => x_msg_count
2903 ,x_msg_data => x_msg_data
2904 ,p_claim_rec => l_claim_rec
2905 ,x_claim_rec => x_claim_rec
2906 );
2907 IF l_return_status = FND_API.g_ret_sts_error THEN
2908 RAISE FND_API.g_exc_error;
2909 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2910 RAISE FND_API.g_exc_unexpected_error;
2911 END IF;
2912 END IF;
2913
2914 /*
2915 The following status flow will not invoke any settlement process.
2916 but we need to check if the status flow is right.
2917 */
2918 -------- APPROVED --------
2919 ELSIF l_curr_status = 'APPROVED' THEN
2920 IF l_prev_status IN ( 'NEW'
2921 , 'OPEN'
2922 , 'COMPLETE'
2923 , 'CANCELLED'
2924 , 'REJECTED'
2925 , 'DUPLICATE'
2926 , 'APPROVED'
2927 , 'ARCHIVED') THEN
2928 -- commented 'CLOSED' from above slkrishn
2929 Dispose_Invalid_Status_Order(
2930 p_prev_status => l_prev_status
2931 ,p_curr_status => l_curr_status
2932 );
2933 RAISE FND_API.g_exc_error;
2934 END IF;
2935 -------- ARCHIVED --------
2936 ELSIF l_curr_status = 'ARCHIVED' THEN
2937 IF l_prev_status IN ( 'NEW'
2938 , 'OPEN'
2939 , 'COMPLETE'
2940 , 'DUPLICATE'
2941 , 'APPROVED'
2942 , 'PENDING_APPROVAL'
2943 , 'PENDING_CLOSE') THEN
2944 Dispose_Invalid_Status_Order(
2945 p_prev_status => l_prev_status
2946 ,p_curr_status => l_curr_status
2947 );
2948 RAISE FND_API.g_exc_error;
2949 END IF;
2950 -------- OPEN --------
2951 ELSIF l_curr_status = 'OPEN' THEN
2952 IF l_prev_status IN ( --'COMPLETE'
2953 --'APPROVED'
2954 --, 'PENDING_APPROVAL'
2955 --, 'PENDING_CLOSE'
2956 'ARCHIVED') THEN
2957 Dispose_Invalid_Status_Order(
2958 p_prev_status => l_prev_status
2959 ,p_curr_status => l_curr_status
2960 );
2961 RAISE FND_API.g_exc_error;
2962 /*---------------------------*
2963 | PENDING_APPROVAL --> OPEN
2964 | PENDING_CLOSE --> OPEN
2965 *---------------------------*/
2966 ELSIF l_prev_status IN ('PENDING_APPROVAL', 'PENDING_CLOSE') THEN
2967 Reopen_Claim_for_Completion(
2968 x_return_status => l_return_status
2969 ,x_msg_count => x_msg_count
2970 ,x_msg_data => x_msg_data
2971 ,p_claim_rec => l_claim_rec
2972 ,x_claim_rec => x_claim_rec
2973 );
2974 IF l_return_status = FND_API.g_ret_sts_error THEN
2975 RAISE FND_API.g_exc_error;
2976 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2977 RAISE FND_API.g_exc_unexpected_error;
2978 END IF;
2979 END IF;
2980
2981 -------- PENDING_CLOSED --------
2982 ELSIF l_curr_status = 'PENDING_CLOSED' THEN
2983 IF l_prev_status IN ( 'NEW'
2984 --, 'OPEN'
2985 , 'COMPLETE'
2986 , 'CLOSED'
2987 , 'CANCELLED'
2988 , 'REJECTED'
2989 , 'DUPLICATE'
2990 , 'PENDING_CLOSE'
2991 , 'ARCHIVED') THEN
2992 Dispose_Invalid_Status_Order(
2993 p_prev_status => l_prev_status
2994 ,p_curr_status => l_curr_status
2995 );
2996 RAISE FND_API.g_exc_error;
2997 END IF;
2998 END IF;
2999 --Printing the values for Claim Multi Currency
3000
3001 IF OZF_DEBUG_HIGH_ON THEN
3002 OZF_Utility_PVT.debug_message('x_claim_rec.settled_date' || x_claim_rec.settled_date);
3003 OZF_Utility_PVT.debug_message('x_claim_rec.claim_id' || x_claim_rec.claim_id);
3004 OZF_Utility_PVT.debug_message('x_claim_rec.org_id' || x_claim_rec.org_id);
3005 OZF_Utility_PVT.debug_message('x_claim_rec.claim_number' || x_claim_rec.claim_number);
3006 OZF_Utility_PVT.debug_message('x_claim_rec.exchange_rate_date' || x_claim_rec.exchange_rate_date);
3007 OZF_Utility_PVT.debug_message('x_claim_rec.exchange_rate_date' || x_claim_rec.exchange_rate);
3008 END IF;
3009 -- Added For Multi currency ER - kpatro
3010 IF(x_claim_rec.settled_date IS NOT NULL) THEN
3011
3012 Recalculate_FXGL( p_claim_id => x_claim_rec.claim_id
3013 , p_claim_org_id => x_claim_rec.org_id
3014 , p_settled_date => x_claim_rec.settled_date
3015 , p_claim_exchange_date => x_claim_rec.exchange_rate_date
3016 , p_claim_rate => x_claim_rec.exchange_rate
3017 , x_return_status => l_return_status
3018 );
3019 IF OZF_DEBUG_HIGH_ON THEN
3020 OZF_Utility_PVT.debug_message('l_return_status' ||l_return_status);
3021 END IF;
3022
3023 IF l_return_status = FND_API.g_ret_sts_error THEN
3024 RAISE FND_API.g_exc_error;
3025 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3026 RAISE FND_API.g_exc_unexpected_error;
3027 END IF;
3028
3029 END IF;
3030 END IF; -- end of complete settlement
3031
3032 ------------------------- finish -------------------------------
3033 /*
3034 -- Check for commit
3035 IF FND_API.to_boolean(p_commit) THEN
3036 COMMIT;
3037 END IF;
3038 */
3039
3040 FND_MSG_PUB.count_and_get(
3041 p_encoded => FND_API.g_false,
3042 p_count => x_msg_count,
3043 p_data => x_msg_data
3044 );
3045
3046 IF OZF_DEBUG_HIGH_ON THEN
3047 OZF_Utility_PVT.debug_message(l_full_name ||': end');
3048 END IF;
3049
3050 EXCEPTION
3051 WHEN FND_API.g_exc_error THEN
3052 ROLLBACK TO Complete_Settlement;
3053 x_return_status := FND_API.g_ret_sts_error;
3054 FND_MSG_PUB.count_and_get (
3055 p_encoded => FND_API.g_false,
3056 p_count => x_msg_count,
3057 p_data => x_msg_data
3058 );
3059 WHEN FND_API.g_exc_unexpected_error THEN
3060 ROLLBACK TO Complete_Settlement;
3061 x_return_status := FND_API.g_ret_sts_unexp_error ;
3062 FND_MSG_PUB.count_and_get (
3063 p_encoded => FND_API.g_false,
3064 p_count => x_msg_count,
3065 p_data => x_msg_data
3066 );
3067 WHEN OTHERS THEN
3068 ROLLBACK TO Complete_Settlement;
3069 x_return_status := FND_API.g_ret_sts_unexp_error ;
3070 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3071 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3072 END IF;
3073 FND_MSG_PUB.count_and_get (
3074 p_encoded => FND_API.g_false,
3075 p_count => x_msg_count,
3076 p_data => x_msg_data
3077 );
3078
3079 END Complete_Settlement;
3080
3081
3082 ---------------------------------------------------------------------
3083 -- PROCEDURE
3084 -- Settle_Claim
3085 --
3086 -- NOTES
3087 --
3088 -- HISTORY
3089 -- 10-AUG-2001 mchang Create.
3090 ---------------------------------------------------------------------
3091 PROCEDURE Settle_Claim(
3092 p_api_version IN NUMBER
3093 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3094 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3095 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3096
3097 ,x_return_status OUT NOCOPY VARCHAR2
3098 ,x_msg_data OUT NOCOPY VARCHAR2
3099 ,x_msg_count OUT NOCOPY NUMBER
3100
3101 ,p_claim_id IN NUMBER
3102 ,p_curr_status IN VARCHAR2
3103 ,p_prev_status IN VARCHAR2
3104 )
3105 IS
3106 l_api_version CONSTANT NUMBER := 1.0;
3107 l_api_name CONSTANT VARCHAR2(30) := 'Settle_Claim';
3108 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3109 l_return_status VARCHAR2(1);
3110
3111 -- Added the settled_from for Rule Based Settlement ER
3112 CURSOR csr_claim_approval_attr(cv_claim_id IN NUMBER) IS
3113 SELECT custom_setup_id
3114 , payment_method
3115 , write_off_flag
3116 , under_write_off_threshold
3117 , status_code
3118 , settled_from
3119 , payment_reference_number
3120 , payment_reference_id
3121 FROM ozf_claims
3122 WHERE claim_id = cv_claim_id;
3123
3124 l_claim_approval_attr csr_claim_approval_attr%ROWTYPE;
3125 l_approval_require VARCHAR2(1);
3126
3127 -- Added For Rule Based Settlement ER
3128 CURSOR csr_rule_based_approval(p_claim_id IN NUMBER) IS
3129 SELECT NVL(approval_matched_credit, 'F'),NVL(approval_new_credit,'F')
3130 FROM ozf_sys_parameters os, ozf_claims_all oc
3131 WHERE oc.org_id = os.org_id
3132 and oc.claim_id = p_claim_id;
3133
3134 l_app_match_cr VARCHAR2(1);
3135 l_app_new_cr VARCHAR2(1);
3136
3137 --End of Rule Based Settlement
3138
3139 BEGIN
3140 --------------------- initialize -----------------------
3141 SAVEPOINT Settle_Claim;
3142
3143 IF OZF_DEBUG_HIGH_ON THEN
3144 OZF_Utility_PVT.debug_message(l_full_name||' : start');
3145 END IF;
3146
3147 IF FND_API.to_boolean(p_init_msg_list) THEN
3148 FND_MSG_PUB.initialize;
3149 END IF;
3150
3151 IF NOT FND_API.compatible_api_call(
3152 l_api_version,
3153 p_api_version,
3154 l_api_name,
3155 g_pkg_name
3156 ) THEN
3157 RAISE FND_API.g_exc_unexpected_error;
3158 END IF;
3159
3160 -- Initialize API return status to sucess
3161 x_return_status := FND_API.g_ret_sts_success;
3162
3163 --------------------- Start -----------------------
3164 IF p_curr_status = 'CANCELLED' THEN
3165 Cancel_Claim_for_Settlement (
3166 x_return_status => l_return_status
3167 ,x_msg_count => x_msg_count
3168 ,x_msg_data => x_msg_data
3169 ,p_claim_id => p_claim_id
3170 ,p_prev_status => p_prev_status
3171 ,p_curr_status => p_curr_status
3172 );
3173 IF l_return_status = FND_API.g_ret_sts_error THEN
3174 RAISE FND_API.g_exc_error;
3175 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3176 RAISE FND_API.g_exc_unexpected_error;
3177 END IF;
3178
3179 ELSIF p_curr_status = 'REJECTED' THEN
3180 Reject_Claim_for_Settlement (
3181 x_return_status => l_return_status
3182 ,x_msg_count => x_msg_count
3183 ,x_msg_data => x_msg_data
3184 ,p_claim_id => p_claim_id
3185 ,p_prev_status => p_prev_status
3186 ,p_curr_status => p_curr_status
3187 );
3188 IF l_return_status = FND_API.g_ret_sts_error THEN
3189 RAISE FND_API.g_exc_error;
3190 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3191 RAISE FND_API.g_exc_unexpected_error;
3192 END IF;
3193
3194 ELSIF p_curr_status = 'DUPLICATE' THEN
3195 /* empty implementation */
3196 l_return_status := FND_API.g_ret_sts_success;
3197
3198 ELSIF p_curr_status = 'COMPLETE' THEN
3199 Complete_Claim_for_Settlement (
3200 x_return_status => l_return_status
3201 ,x_msg_count => x_msg_count
3202 ,x_msg_data => x_msg_data
3203 ,p_claim_id => p_claim_id
3204 );
3205 IF l_return_status = FND_API.g_ret_sts_error THEN
3206 RAISE FND_API.g_exc_error;
3207 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3208 RAISE FND_API.g_exc_unexpected_error;
3209 END IF;
3210
3211 ELSIF p_curr_status = 'CLOSED' THEN
3212 -- Added For Rule Based Settlement ER
3213 OPEN csr_claim_approval_attr(p_claim_id);
3214 FETCH csr_claim_approval_attr INTO l_claim_approval_attr.custom_setup_id
3215 , l_claim_approval_attr.payment_method
3216 , l_claim_approval_attr.write_off_flag
3217 , l_claim_approval_attr.under_write_off_threshold
3218 , l_claim_approval_attr.status_code
3219 , l_claim_approval_attr.settled_from
3220 , l_claim_approval_attr.payment_reference_number
3221 , l_claim_approval_attr.payment_reference_id;
3222
3223 CLOSE csr_claim_approval_attr;
3224
3225
3226 -- Added For Rule Based Settlement ER
3227 OPEN csr_rule_based_approval(p_claim_id);
3228 FETCH csr_rule_based_approval INTO l_app_match_cr,l_app_new_cr;
3229 CLOSE csr_rule_based_approval;
3230
3231 IF OZF_DEBUG_HIGH_ON THEN
3232 OZF_Utility_PVT.debug_message('l_app_match_cr :'||l_app_match_cr);
3233 OZF_Utility_PVT.debug_message('l_app_new_cr :'|| l_app_new_cr);
3234 OZF_Utility_PVT.debug_message('l_claim_approval_attr.payment_reference_number :'|| l_claim_approval_attr.payment_reference_number);
3235 OZF_Utility_PVT.debug_message('l_claim_approval_attr.payment_reference_id :'|| l_claim_approval_attr.payment_reference_id);
3236 END IF;
3237
3238 IF NVL(l_claim_approval_attr.settled_from,'X') <> 'RULEBASED' THEN
3239 Claim_Approval_Required(
3240 p_claim_id => p_claim_id
3241 ,x_return_status => l_return_status
3242 ,x_msg_data => x_msg_data
3243 ,x_msg_count => x_msg_count
3244 ,x_approval_require => l_approval_require
3245 );
3246 IF l_return_status = FND_API.g_ret_sts_error THEN
3247 RAISE FND_API.g_exc_error;
3248 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3249 RAISE FND_API.g_exc_unexpected_error;
3250 END IF;
3251 ELSE
3252 -- For Credit Match
3253 IF (l_claim_approval_attr.payment_reference_number IS NOT NULL
3254 AND l_claim_approval_attr.payment_reference_id IS NOT NULL
3255 AND l_app_match_cr = 'T') THEN
3256 l_approval_require := 'Y';
3257 -- For Accrual match
3258 ELSIF(l_claim_approval_attr.payment_reference_number IS NULL
3259 AND l_claim_approval_attr.payment_reference_id IS NULL
3260 AND l_app_new_cr = 'T') THEN
3261 l_approval_require := 'Y';
3262 END IF;
3263 END IF;
3264 -- End of Rule Based Settlement
3265
3266 /*-------------------------------*
3267 | OPEN --> CLOSED :Settlement
3268 *-------------------------------*/
3269 IF p_prev_status = 'OPEN' THEN
3270 Complete_Claim_for_Settlement (
3271 x_return_status => l_return_status
3272 ,x_msg_count => x_msg_count
3273 ,x_msg_data => x_msg_data
3274 ,p_claim_id => p_claim_id
3275 );
3276 IF l_return_status = FND_API.g_ret_sts_error THEN
3277 RAISE FND_API.g_exc_error;
3278 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3279 RAISE FND_API.g_exc_unexpected_error;
3280 END IF;
3281
3282 IF l_approval_require = 'Y' THEN
3283 -- call marketing general approval workflow
3284 Approve_Claim_for_Settlement (
3285 x_return_status => l_return_status
3286 ,x_msg_count => x_msg_count
3287 ,x_msg_data => x_msg_data
3288 ,p_claim_id => p_claim_id
3289 ,p_prev_status => p_prev_status
3290 ,p_curr_status => p_curr_status
3291 );
3292 IF l_return_status = FND_API.g_ret_sts_error THEN
3293 RAISE FND_API.g_exc_error;
3294 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3295 RAISE FND_API.g_exc_unexpected_error;
3296 END IF;
3297 ELSE -- skip approval workflow and create payment directly
3298 -- payment process won't start if claim will go for referral approval.
3299 IF l_claim_approval_attr.status_code <> 'PENDING_APPROVAL' THEN
3300 Create_Payment_for_Settlement (
3301 x_return_status => l_return_status
3302 ,x_msg_count => x_msg_count
3303 ,x_msg_data => x_msg_data
3304 ,p_claim_id => p_claim_id
3305 ,p_prev_status => p_prev_status
3306 ,p_curr_status => p_curr_status
3307 );
3308 IF l_return_status = FND_API.g_ret_sts_error THEN
3309 RAISE FND_API.g_exc_error;
3310 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3311 RAISE FND_API.g_exc_unexpected_error;
3312 END IF;
3313 END IF;
3314 END IF;
3315
3316 /*---------------------------------*
3317 | COMPLETE --> CLOSED :Settlement
3318 *---------------------------------*/
3319 ELSIF p_prev_status = 'COMPLETE' THEN
3320 IF l_approval_require = 'Y' THEN
3321 -- call marketing general approval workflow
3322 Approve_Claim_for_Settlement (
3323 x_return_status => l_return_status
3324 ,x_msg_count => x_msg_count
3325 ,x_msg_data => x_msg_data
3326 ,p_claim_id => p_claim_id
3327 ,p_prev_status => p_prev_status
3328 ,p_curr_status => p_curr_status
3329 );
3330 IF l_return_status = FND_API.g_ret_sts_error THEN
3331 RAISE FND_API.g_exc_error;
3332 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3333 RAISE FND_API.g_exc_unexpected_error;
3334 END IF;
3335 ELSE -- skip approval workflow and create payment directly
3336 -- payment process won't start if claim will go for referral approval.
3337 IF l_claim_approval_attr.status_code <> 'PENDING_APPROVAL' THEN
3338 Create_Payment_for_Settlement (
3339 x_return_status => l_return_status
3340 ,x_msg_count => x_msg_count
3341 ,x_msg_data => x_msg_data
3342 ,p_claim_id => p_claim_id
3343 ,p_prev_status => p_prev_status
3344 ,p_curr_status => p_curr_status
3345 );
3346 IF l_return_status = FND_API.g_ret_sts_error THEN
3347 RAISE FND_API.g_exc_error;
3348 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3349 RAISE FND_API.g_exc_unexpected_error;
3350 END IF;
3351 END IF;
3352 END IF;
3353
3354 /*---------------------------------------------------*
3355 | APPROVED / PENDING_APPROVAL --> CLOSED :Settlement
3356 *---------------------------------------------------*/
3357 ELSIF p_prev_status = 'APPROVED' OR
3358 p_prev_status = 'PENDING_APPROVAL' THEN
3359 Create_Payment_for_Settlement (
3360 x_return_status => l_return_status
3361 ,x_msg_count => x_msg_count
3362 ,x_msg_data => x_msg_data
3363 ,p_claim_id => p_claim_id
3364 ,p_prev_status => p_prev_status
3365 ,p_curr_status => p_curr_status
3366 );
3367 IF l_return_status = FND_API.g_ret_sts_error THEN
3368 RAISE FND_API.g_exc_error;
3369 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3370 RAISE FND_API.g_exc_unexpected_error;
3371 END IF;
3372 END IF;
3373 ELSIF p_curr_status = 'OPEN' THEN
3374 /* mchang fix for 1159
3375 IF p_prev_status = 'COMPLETE' AND
3376 payment_method= 'RMA' THEN
3377 -- Delete RMA Order
3378 IF l_return_status = FND_API.g_ret_sts_error THEN
3379 RAISE FND_API.g_exc_error;
3380 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3381 RAISE FND_API.g_exc_unexpected_error;
3382 END IF;
3383 */
3384 NULL;
3385 END IF;
3386
3387 ------------------------- finish -------------------------------
3388 /*
3389 -- Check for commit
3390 IF FND_API.to_boolean(p_commit) THEN
3391 COMMIT;
3392 END IF;
3393 */
3394
3395 FND_MSG_PUB.count_and_get(
3396 p_encoded => FND_API.g_false,
3397 p_count => x_msg_count,
3398 p_data => x_msg_data
3399 );
3400
3401 IF OZF_DEBUG_HIGH_ON THEN
3402 OZF_Utility_PVT.debug_message(l_full_name ||': end');
3403 END IF;
3404
3405 EXCEPTION
3406 WHEN FND_API.g_exc_error THEN
3407 ROLLBACK TO Settle_Claim;
3408 x_return_status := FND_API.g_ret_sts_error;
3409 FND_MSG_PUB.count_and_get (
3410 p_encoded => FND_API.g_false,
3411 p_count => x_msg_count,
3412 p_data => x_msg_data
3413 );
3414 WHEN FND_API.g_exc_unexpected_error THEN
3415 ROLLBACK TO Settle_Claim;
3416 x_return_status := FND_API.g_ret_sts_unexp_error ;
3417 FND_MSG_PUB.count_and_get (
3418 p_encoded => FND_API.g_false,
3419 p_count => x_msg_count,
3420 p_data => x_msg_data
3421 );
3422 WHEN OTHERS THEN
3423 ROLLBACK TO Settle_Claim;
3424 x_return_status := FND_API.g_ret_sts_unexp_error ;
3425 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3426 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3427 END IF;
3428 FND_MSG_PUB.count_and_get (
3429 p_encoded => FND_API.g_false,
3430 p_count => x_msg_count,
3431 p_data => x_msg_data
3432 );
3433
3434 END Settle_Claim;
3435
3436
3437 ---------------------------------------------------------------------
3438 -- PROCEDURE
3439 -- Raise_Business_Event
3440 --
3441 -- NOTES
3442 --
3443 -- HISTORY
3444 -- 10-OCT-2003 mchang Create.
3445 ---------------------------------------------------------------------
3446 PROCEDURE Raise_Business_Event(
3447 p_api_version IN NUMBER
3448 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3449 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3450 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3451
3452 ,x_return_status OUT NOCOPY VARCHAR2
3453 ,x_msg_data OUT NOCOPY VARCHAR2
3454 ,x_msg_count OUT NOCOPY NUMBER
3455
3456 ,p_claim_id IN NUMBER
3457 ,p_old_status IN VARCHAR2
3458 ,p_new_status IN VARCHAR2
3459 ,p_event_name IN VARCHAR2
3460 )
3461 IS
3462 l_api_version CONSTANT NUMBER := 1.0;
3463 l_api_name CONSTANT VARCHAR2(30) := 'Raise_Business_Event';
3464 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3465 l_return_status VARCHAR2(1);
3466 ---
3467
3468 l_parameter_list WF_PARAMETER_LIST_T;
3469 l_new_item_key VARCHAR2(30);
3470 l_event_name VARCHAR2(60);
3471
3472 CURSOR csr_claim_org(p_claim_id IN NUMBER) IS
3473 SELECT org_id
3474 FROM ozf_claims_all
3475 WHERE claim_id = p_claim_id;
3476 l_org_id NUMBER;
3477
3478 BEGIN
3479
3480 --------------------- initialize -----------------------
3481 SAVEPOINT Raise_Business_Event;
3482
3483 IF OZF_DEBUG_HIGH_ON THEN
3484 OZF_Utility_PVT.debug_message(l_full_name||' : start');
3485 OZF_Utility_PVT.debug_message(l_full_name||' : event = '||p_event_name);
3486 END IF;
3487
3488 IF FND_API.to_boolean(p_init_msg_list) THEN
3489 FND_MSG_PUB.initialize;
3490 END IF;
3491
3492 IF NOT FND_API.compatible_api_call(
3493 l_api_version,
3494 p_api_version,
3495 l_api_name,
3496 g_pkg_name
3497 ) THEN
3498 RAISE FND_API.g_exc_unexpected_error;
3499 END IF;
3500
3501 -- Initialize API return status to sucess
3502 x_return_status := FND_API.g_ret_sts_success;
3503
3504 --------------------- Start -----------------------
3505 l_new_item_key := p_claim_id||'_'||TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
3506
3507 l_parameter_list := WF_PARAMETER_LIST_T();
3508
3509 WF_EVENT.AddParameterToList( p_name => 'CLAIM_ID'
3510 , p_value => p_claim_id
3511 , p_parameterlist => l_parameter_list
3512 );
3513
3514 WF_EVENT.AddParameterToList( p_name => 'STATUS_CODE'
3515 , p_value => p_new_status
3516 , p_parameterlist => l_parameter_list
3517 );
3518
3519 OPEN csr_claim_org(p_claim_id);
3520 FETCH csr_claim_org INTO l_org_id;
3521 CLOSE csr_claim_org;
3522
3523 WF_EVENT.AddParameterToList( p_name => 'ORG_ID'
3524 , p_value => l_org_id
3525 , p_parameterlist => l_parameter_list
3526 );
3527
3528
3529 WF_EVENT.Raise(
3530 p_event_name => p_event_name
3531 ,p_event_key => l_new_item_key
3532 ,p_parameters => l_parameter_list
3533 );
3534
3535 ------------------------- finish -------------------------------
3536 -- Check for commit
3537 IF FND_API.to_boolean(p_commit) THEN
3538 COMMIT;
3539 END IF;
3540
3541 FND_MSG_PUB.count_and_get(
3542 p_encoded => FND_API.g_false,
3543 p_count => x_msg_count,
3544 p_data => x_msg_data
3545 );
3546
3547 IF OZF_DEBUG_HIGH_ON THEN
3548 OZF_Utility_PVT.debug_message(l_full_name ||': end');
3549 END IF;
3550
3551 EXCEPTION
3552 WHEN FND_API.g_exc_error THEN
3553 ROLLBACK TO Raise_Business_Event;
3554 x_return_status := FND_API.g_ret_sts_error;
3555 FND_MSG_PUB.count_and_get (
3556 p_encoded => FND_API.g_false,
3557 p_count => x_msg_count,
3558 p_data => x_msg_data
3559 );
3560 WHEN FND_API.g_exc_unexpected_error THEN
3561 ROLLBACK TO Raise_Business_Event;
3562 x_return_status := FND_API.g_ret_sts_unexp_error ;
3563 FND_MSG_PUB.count_and_get (
3564 p_encoded => FND_API.g_false,
3565 p_count => x_msg_count,
3566 p_data => x_msg_data
3567 );
3568 WHEN OTHERS THEN
3569 ROLLBACK TO Raise_Business_Event;
3570 x_return_status := FND_API.g_ret_sts_unexp_error ;
3571 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3572 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3573 END IF;
3574 FND_MSG_PUB.count_and_get (
3575 p_encoded => FND_API.g_false,
3576 p_count => x_msg_count,
3577 p_data => x_msg_data
3578 );
3579
3580 END Raise_Business_Event;
3581
3582 END OZF_CLAIM_SETTLEMENT_PVT;