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