[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_GRP
Source
1 PACKAGE BODY OZF_CLAIM_GRP AS
2 /* $Header: ozfgclab.pls 120.14.12010000.2 2008/11/18 08:01:05 bkunjan ship $ */
3 -- Start of Comments
4 -- Package name : OZF_CLAIM_GRP
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(20) := 'OZF_CLAIM_GRP';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfgclab.pls';
12
13 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
14 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
15
16 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
17
18 G_DEDUCTION_CLASS CONSTANT VARCHAR2(20) := 'DEDUCTION';
19 G_OVERPAYMENT_CLASS CONSTANT VARCHAR2(20) := 'OVERPAYMENT';
20 G_DEDUC_OBJ_TYPE CONSTANT VARCHAR2(6) := 'DEDU';
21 G_CLAIM_OBJECT_TYPE CONSTANT VARCHAR2(30) := 'CLAM';
22 G_CLAIM_STATUS CONSTANT VARCHAR2(30) := 'OZF_CLAIM_STATUS';
23
24 G_OPEN_STATUS CONSTANT VARCHAR2(30) := 'OPEN';
25 G_CANCELLED_STATUS CONSTANT VARCHAR2(30) := 'CANCELLED';
26 G_UPDATE_EVENT CONSTANT VARCHAR2(30) := 'UPDATE';
27 G_SUBSEQUENT_APPLY_EVENT CONSTANT VARCHAR2(30) := 'SUBSEQUENT_APPLY';
28 G_SUBSEQUENT_UNAPPLY_EVENT CONSTANT VARCHAR2(30) := 'SUBSEQUENT_UNAPPLY';
29 G_INVOICE CONSTANT VARCHAR2(30) := 'INVOICE';
30
31
32
33
34 -- *******************************************************
35 -- Start of Comments
36 -- *******************************************************
37 -- API Name: Build_Note
38 -- Type : Public
39 -- Pre-Req :
40 -- Parameters:
41 -- IN
42 -- p_message_name IN VARCHAR2 Required
43 -- p_token_1 IN VARCHAR2 Optional Default:= null
44 -- p_token_2 IN VARCHAR2 Optional Default:= null
45 -- p_token_3 IN VARCHAR2 Optional Default:= null
46 -- p_token_4 IN VARCHAR2 Optional Default:= null
47 -- p_token_5 IN VARCHAR2 Optional Default:= null
48 -- p_token_6 IN VARCHAR2 Optional Default:= null
49
50 -- Version : Current version 1.0
51 --
52 -- Note: This function builds a message given the name of the message and tokens
53 -- bugfix 4869928
54 -- End of Comments
55 -- *******************************************************
56
57 FUNCTION Build_Note (
58 p_message_name IN VARCHAR2
59 , p_token_1 IN VARCHAR2:= NULL
60 , p_token_2 IN VARCHAR2:= NULL
61 , p_token_3 IN VARCHAR2:= NULL
62 , p_token_4 IN VARCHAR2:= NULL
63 , p_token_5 IN VARCHAR2:= NULL
64 , p_token_6 IN VARCHAR2:= NULL
65 )
66 RETURN VARCHAR2
67 IS
68 BEGIN
69
70 IF p_message_name = 'OZF_CLAM_NOTES_STATUS_CHANGE' THEN
71 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_STATUS_CHANGE');
72 fnd_message.set_token('STATUS_CODE', p_token_1);
73 fnd_message.set_token('NEW_STATUS_CODE', p_token_2);
74 END IF;
75
76 IF p_message_name = 'OZF_CLAM_NOTES_STATUS_SAME' THEN
77 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_STATUS_SAME');
78 fnd_message.set_token('STATUS_CODE', p_token_1);
79 END IF;
80
81 IF p_message_name = 'OZF_CLAM_NOTES_RCPT_CHANGE' THEN
82 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_RCPT_CHANGE');
83 fnd_message.set_token('RECEIPT_NUMBER', p_token_1);
84 fnd_message.set_token('NEW_RECEIPT_NUMBER', p_token_2);
85 END IF;
86
87 IF p_message_name = 'OZF_CLAM_NOTES_RCPT_SAME' THEN
88 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_RCPT_SAME');
89 fnd_message.set_token('RECEIPT_NUMBER', p_token_1);
90 END IF;
91
92 IF p_message_name = 'OZF_CLAM_NOTES_APPLY_CHANGE' THEN
93 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_APPLY_CHANGE');
94 fnd_message.set_token('OLD_AMOUNT', p_token_1);
95 fnd_message.set_token('NEW_AMOUNT', p_token_2);
96 fnd_message.set_token('AMOUNT_APPLIED_L', p_token_3);
97 fnd_message.set_token('AMOUNT_APPLIED', p_token_4);
98 fnd_message.set_token('RECEIPT_NUMBER', p_token_5);
99 END IF;
100
101 IF p_message_name = 'OZF_CLAM_NOTES_UNAPPLY_CHANGE' THEN
102 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_UNAPPLY_CHANGE');
103 fnd_message.set_token('OLD_AMOUNT', p_token_1);
104 fnd_message.set_token('NEW_AMOUNT', p_token_2);
105 fnd_message.set_token('AMOUNT_APPLIED', p_token_3);
106 fnd_message.set_token('RECEIPT_NUMBER', p_token_4);
107 END IF;
108
109 IF p_message_name = 'OZF_CLAM_NOTES_AR_BAL_ZERO' THEN
110 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_AR_BAL_ZERO');
111 END IF;
112
113 IF p_message_name = 'OZF_CLAM_NOTES_RCPTS_UNAPPLIED' THEN
114 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_RCPTS_UNAPPLIED');
115 END IF;
116
117 IF p_message_name = 'OZF_CLAM_NOTES_NO_SUBS_APPLY' THEN
118 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_NO_SUBS_APPLY');
119 fnd_message.set_token('RECEIPT_NUMBER', p_token_1);
120 END IF;
121
122 IF p_message_name = 'OZF_CLAM_NOTES_NO_SUBS_UNAPPLY' THEN
123 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_NO_SUBS_UNAPPLY');
124 fnd_message.set_token('RECEIPT_NUMBER', p_token_1);
125 END IF;
126
127 IF p_message_name = 'OZF_CLAM_NOTES_CLAIM_PENDING' THEN
128 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_CLAIM_PENDING');
129 END IF;
130
131 IF p_message_name = 'OZF_CLAM_NOTES_CLAIM_CLOSED' THEN
132 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_CLAIM_CLOSED');
133 END IF;
134
135 IF p_message_name = 'OZF_CLAIM_NOTES_AMOUNT_UNAPPLY' THEN
136 fnd_message.set_name('OZF', 'OZF_CLAIM_NOTES_AMOUNT_UNAPPLY');
137 fnd_message.set_token('RECEIPT_NUMBER', p_token_1);
138 END IF;
139
140
141 IF p_message_name = 'OZF_CLAIM_NOTES_AMOUNT_ADJUST' THEN
142 fnd_message.set_name('OZF', 'OZF_CLAIM_NOTES_AMOUNT_ADJUST');
143 fnd_message.set_token('CLAIM_NUMBER', p_token_1);
144 fnd_message.set_token('STATUS_CODE', p_token_2);
145 fnd_message.set_token('NEW_CLAIM_NUMBER', p_token_3);
146 fnd_message.set_token('AMOUNT_APPLIED', p_token_4);
147 fnd_message.set_token('AMOUNT', p_token_5);
148 fnd_message.set_token('NEW_AMOUNT', p_token_6);
149 END IF;
150
151 IF p_message_name = 'OZF_CLAIM_NOTES_AMOUNT_SPLIT' THEN
152 fnd_message.set_name('OZF', 'OZF_CLAIM_NOTES_AMOUNT_SPLIT');
153 fnd_message.set_token('OLD_AMOUNT', p_token_1);
154 fnd_message.set_token('NEW_AMOUNT', p_token_2);
155 fnd_message.set_token('SPLIT_AMOUNT', p_token_3);
156
157 END IF;
158
159 IF p_message_name = 'OZF_CLAM_NOTES_AFFECTED' THEN
160 fnd_message.set_name('OZF', 'OZF_CLAM_NOTES_AFFECTED');
161 fnd_message.set_token('CLAIM_NUMBER', p_token_1);
162 fnd_message.set_token('AMOUNT_APPLIED', p_token_2);
163 END IF;
164
165 RETURN fnd_message.get;
166
167 END Build_Note;
168
169 ---------------------------------------------------------------------
170 -- PROCEDURE: Write_Log
171 --
172 -- PURPOSE: Populate log message
173 --
174 -- PARAMETERS:
175 -- IN:
176 -- p_module_name IN VARCHAR2 Required
177 -- p_log_message IN VARCHAR2 Required
178 --
179 -- NOTES:
180 --
181 ---------------------------------------------------------------------
182
183 PROCEDURE Write_Log (
184 p_module_name IN VARCHAR2,
185 p_log_message IN VARCHAR2
186 )
187 IS
188 --PRAGMA AUTONOMOUS_TRANSACTION;
189 BEGIN
190 IF OZF_DEBUG_HIGH_ON OR
191 OZF_CLAIM_GRP.G_DEBUG_MODE THEN
192 OZF_Utility_PVT.debug_message(p_module_name||':'||p_log_message);
193 END IF;
194
195 OZF_Utility_PVT.debug_message(
196 p_log_level => G_LOG_LEVEL,
197 p_module_name => p_module_name,
198 p_text => p_log_message
199 );
200
201 --INSERT INTO LOG_TEST VALUES(p_module_name, p_log_message);
202 --COMMIT;
203
204 --EXCEPTION
205 -- currently no exception handled
206
207 END Write_Log;
208
209 ---------------------------------------------------------------------
210 -- PROCEDURE: update_parent_amounts
211 --
212 -- PURPOSE: Updates the adjusted amounts on the parent
213 --
214 -- NOTES:
215 -- Sahana Created for 4969147
216 ---------------------------------------------------------------------
217 PROCEDURE update_parent_amounts(
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 p_deduction_rec IN DEDUCTION_REC_TYPE
222 ) IS
223 l_api_version CONSTANT NUMBER := 1.0;
224 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Subsequent_Apply';
225 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
226 l_return_status VARCHAR2(1);
227
228 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction_rec;
229 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
230 l_notes VARCHAR2(2000);
231 l_object_version_number NUMBER;
232 l_x_note_id NUMBER;
233
234 -- get all the parent claims
235 CURSOR parent_claim_csr(p_claim_id in number) IS
236 SELECT root_claim_id
237 , claim_id
238 , object_version_number
239 , claim_number
240 , amount
241 , amount_adjusted
242 , amount_remaining
243 , amount_settled
244 , status_code
245 , receipt_number
246 , claim_class
247 , split_from_claim_id
248 FROM ozf_claims
249 WHERE split_from_claim_id IS NULL
250 AND root_claim_id = p_claim_id
251 ORDER BY claim_id desc;
252
253 CURSOR child_claim_csr(p_claim_id in number) IS
254 SELECT SUM(amount)
255 FROM ozf_claims
256 WHERE split_from_claim_id = p_claim_id;
257
258 l_change_in_amount NUMBER;
259 l_tot_child_amt NUMBER;
260 BEGIN
261 Write_Log(l_full_name, 'start');
262
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264 -- Now sync the amount_adjusted for each parent
265 FOR ref_parent_claim IN parent_claim_csr(l_deduction_rec.claim_id)
266 LOOP
267 OPEN child_claim_csr(ref_parent_claim.claim_id);
268 FETCH child_claim_csr INTO l_tot_child_amt;
269 CLOSE child_claim_csr;
270
271 l_change_in_amount := ref_parent_claim.amount_adjusted - NVL(l_tot_child_amt,0);
272
273 IF l_change_in_amount <> 0 THEN
274 l_pvt_claim_rec.claim_id := ref_parent_claim.claim_id;
275 l_pvt_claim_rec.object_version_number := ref_parent_claim.object_version_number;
276 l_pvt_claim_rec.status_code := ref_parent_claim.status_code;
277 l_pvt_claim_rec.amount := ref_parent_claim.amount - l_change_in_amount ;
278 l_pvt_claim_rec.amount_adjusted := l_tot_child_amt;
279 l_pvt_claim_rec.amount_applied := l_deduction_rec.amount_applied;
280 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
281 l_pvt_claim_rec.applied_receipt_id := l_deduction_rec.applied_receipt_id;
282 l_pvt_claim_rec.applied_receipt_number := l_deduction_rec.applied_receipt_number;
283
284 l_notes := 'Claim Amount is changed '||
285 ' From '|| ref_parent_claim.amount ||' To '||l_pvt_claim_rec.amount||
286 ' due to Application of amount '||l_deduction_rec.amount_applied||
287 ' from Receipt Number:'||l_deduction_rec.applied_receipt_number||'.';
288
289 --Call Update_Claim to reflect the changes.
290 OZF_claim_PVT.Update_claim(
291 p_api_version => 1.0,
292 p_init_msg_list => FND_API.G_FALSE,
293 p_commit => FND_API.G_FALSE,
294 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
295 x_return_status => l_return_status,
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data,
298 p_claim => l_pvt_claim_Rec,
299 p_event => G_SUBSEQUENT_APPLY_EVENT, --G_UPDATE_EVENT
300 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
301 x_object_version_number => l_object_version_number
302 );
303 IF l_return_status = FND_API.G_RET_STS_ERROR then
304 RAISE FND_API.G_EXC_ERROR;
305 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307 END IF;
308
309 Write_log(l_full_name,l_notes);
310 JTF_NOTES_PUB.create_note(
311 p_api_version => 1.0
312 ,x_return_status => l_return_status
313 ,x_msg_count => x_msg_count
314 ,x_msg_data => x_msg_data
315 ,p_source_object_id => l_pvt_claim_rec.claim_id
316 ,p_source_object_code => 'AMS_CLAM'
317 ,p_notes => l_notes
318 ,p_note_status => NULL
319 ,p_entered_by => FND_GLOBAL.user_id
320 ,p_entered_date => SYSDATE
321 ,p_last_updated_by => FND_GLOBAL.user_id
322 ,x_jtf_note_id => l_x_note_id
323 ,p_note_type => 'AMS_JUSTIFICATION'
324 ,p_last_update_date => SYSDATE
325 ,p_creation_date => SYSDATE
326 );
327
328 END IF;
329 END LOOP; -- Now sync the amount_adjusted for each parent
330 Write_Log(l_full_name, 'end');
331 EXCEPTION
332 WHEN FND_API.G_EXC_ERROR THEN
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 -- Standard call to get message count and if count=1, get the message
335 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
336 ,p_count => x_msg_count
337 ,p_data => x_msg_data
338 );
339
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 -- Standard call to get message count and if count=1, get the message
343 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
344 ,p_count => x_msg_count
345 ,p_data => x_msg_data
346 );
347
348 WHEN OTHERS THEN
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
351 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
352 FND_MSG_PUB.add;
353 END IF;
354 -- Standard call to get message count and if count=1, get the message
355 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
356 ,p_count => x_msg_count
357 ,p_data => x_msg_data
358 );
359 END update_parent_amounts;
360
361 ---------------------------------------------------------------------
362 -- PROCEDURE: Create_Deduction
363 --
364 -- PURPOSE: This procedure checks information passed from AR to Claim module and then
365 -- calls Creat_claim function in the private package to create a claim record.
366 -- It returns a claim_id, cliam_number, claim reason code id and name as the result.
367
368 -- PARAMETERS:
369 -- IN:
370 -- p_api_version_number IN NUMBER Required
371 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
375 --
372 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
373 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
374 -- P_deduction_Rec IN DEDUCTION_REC_TYPE Required
376 -- OUT:
377 -- x_return_status OUT VARCHAR2
378 -- x_msg_count OUT NUMBER
379 -- x_msg_data OUT VARCHAR2
380 -- x_claim_id OUT NUMBER
381 -- x_claim_number OUT VARCHAR2
382 --
383 -- NOTES:
384 --
385 ---------------------------------------------------------------------
386 PROCEDURE Create_Deduction(
387 p_api_version_number IN NUMBER,
388 p_init_msg_list IN VARCHAR2,
389 p_validation_level IN NUMBER,
390 p_commit IN VARCHAR2,
391
392 x_return_status OUT NOCOPY VARCHAR2,
393 x_msg_count OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2,
395
396 P_deduction IN DEDUCTION_REC_TYPE,
397 x_claim_id OUT NOCOPY NUMBER,
398 x_claim_number OUT NOCOPY VARCHAR2
399 )
400 IS
401 x_claim_reason_code_id NUMBER;
402 x_claim_reason_name VARCHAR2(80);
403
404 BEGIN
405 -- Initialize API return status to SUCCESS
406 x_return_status := FND_API.G_RET_STS_SUCCESS;
407
408 OZF_CLAIM_GRP.Create_Deduction(
409 p_api_version_number,
410 p_init_msg_list,
411 p_validation_level,
412 p_commit,
413 x_return_status,
414 x_msg_count,
415 x_msg_data,
416 p_deduction,
417 x_claim_id,
418 x_claim_number,
419 x_claim_reason_code_id,
420 x_claim_reason_name
421 );
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
426 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
427 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CRE_DEDU_ERR');
428 FND_MSG_PUB.add;
429 END IF;
430 -- Standard call to get message count and if count=1, get the message
431 FND_MSG_PUB.Count_And_Get (
432 p_encoded => FND_API.G_FALSE,
433 p_count => x_msg_count,
434 p_data => x_msg_data
435 );
436 End Create_Deduction;
437
438
439 ---------------------------------------------------------------------
440 -- PROCEDURE: Create_Deduction
441 --
442 -- PURPOSE: This is modification to existing Create_Deduction procedure with two new
443 -- addtional parameters to return claim_reason_code_id and claim_reason_name
444 -- to the calling procedure.
445 -- This procedure checks information passed from AR to Claim module and then
446 -- calls Creat_claim function in the private package to create a claim record.
447 -- It returns a claim_id and cliam_number and cliam_reason_code and
448 -- claim_reason_nameas the result.
449 --
450 -- PARAMETERS:
451 -- IN
452 -- p_api_version_number IN NUMBER Required
453 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
454 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
455 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
456 -- P_deduction_Rec IN DEDUCTION_REC_TYPE Required
457 --
458 -- OUT:
459 -- x_return_status OUT NOCOPY VARCHAR2
460 -- x_msg_count OUT NOCOPY NUMBER
461 -- x_msg_data OUT NOCOPY VARCHAR2
462 -- x_claim_id OUT NOCOPY NUMBER,
463 -- x_claim_number OUT NOCOPY VARCHAR2
464 -- x_claim_reason_code_id OUT NOCOPY NUMBER
465 -- x_claim_reason_name OUT NOCOPY VARCHAR2
466 --
467 -- NOTES:
468 --
469 ---------------------------------------------------------------------
470 PROCEDURE Create_Deduction(
471 p_api_version_number IN NUMBER,
472 p_init_msg_list IN VARCHAR2,
473 p_validation_level IN NUMBER,
474 p_commit IN VARCHAR2,
475
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2,
479
480 p_deduction IN DEDUCTION_REC_TYPE,
481 x_claim_id OUT NOCOPY NUMBER,
482 x_claim_number OUT NOCOPY VARCHAR2,
483 x_claim_reason_code_id OUT NOCOPY NUMBER,
484 x_claim_reason_name OUT NOCOPY VARCHAR2
485 )
486 IS
487 l_api_version CONSTANT NUMBER := 1.0;
488 l_api_name CONSTANT VARCHAR2(30) := 'Create_Deduction';
489 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
490 l_return_status VARCHAR2(1);
491 l_dummy_number NUMBER;
492 --
493 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
497 l_qualifier OZF_ASSIGNMENT_QUALIFIER_PUB.qualifier_rec_type;
494 l_pvt_claim_rec OZF_CLAIM_PVT.claim_rec_type;
495 l_cre_claim_rec OZF_CLAIM_PVT.claim_rec_type;
496 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
498 l_qual_deduction_rec OZF_ASSIGNMENT_QUALIFIER_PUB.deduction_rec_type;
499 l_sql_stmt VARCHAR2(1000);
500
501 CURSOR csr_get_receipt_num(cv_cash_receipt_id IN NUMBER) IS
502 SELECT receipt_number
503 FROM ar_cash_receipts
504 WHERE cash_receipt_id = cv_cash_receipt_id;
505
506 CURSOR csr_get_trx_num(cv_customer_trx_id IN NUMBER) IS
507 SELECT trx_number
508 FROM ra_customer_trx
509 WHERE customer_trx_id = cv_customer_trx_id;
510
511 CURSOR csr_get_trx_class(cv_cust_trx_type_id IN NUMBER) IS
512 SELECT type
513 FROM ra_cust_trx_types
514 WHERE cust_trx_type_id = cv_cust_trx_type_id;
515
516 CURSOR csr_get_claim_info(cv_claim_id IN NUMBER) IS
517 SELECT c.claim_number
518 , c.reason_code_id
519 , r.name
520 FROM ozf_claims c
521 , ozf_reason_codes_vl r
522 WHERE c.claim_id = cv_claim_id
523 AND c.reason_code_id = r.reason_code_id;
524
525
526 BEGIN
527 --------------------- initialize -----------------------
528 -- Standard Start of API savepoint
529 SAVEPOINT CREATE_CLAIM_GRP;
530
531 Write_Log(l_full_name, 'start');
532
533 -- Standard call to check for call compatibility.
534 IF NOT FND_API.Compatible_API_Call ( l_api_version,
535 p_api_version_number,
536 l_api_name,
537 G_PKG_NAME)
538 THEN
539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
540 END IF;
541
542 -- Initialize message list if p_init_msg_list is set to TRUE.
543 IF FND_API.to_Boolean( p_init_msg_list ) THEN
544 FND_MSG_PUB.initialize;
545 END IF;
546
547 -- Initialize API return status to SUCCESS
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549
550 ------------------------- start -------------------------------
551
552 -----------------------------
553 -- 1. Assignment Qualifier --
554 -----------------------------
555 -- First, construct the deduction rec to get assignment qualifiers
556 l_qual_deduction_rec.claim_id := l_deduction_rec.claim_id;
557 l_qual_deduction_rec.claim_number := l_deduction_rec.claim_number;
558 l_qual_deduction_rec.claim_type_id := l_deduction_rec.claim_type_id;
559 l_qual_deduction_rec.claim_date := l_deduction_rec.claim_date;
560 l_qual_deduction_rec.due_date := l_deduction_rec.due_date;
561 l_qual_deduction_rec.owner_id := l_deduction_rec.owner_id;
562 l_qual_deduction_rec.amount := l_deduction_rec.amount;
563 l_qual_deduction_rec.currency_code := l_deduction_rec.currency_code;
564 l_qual_deduction_rec.exchange_rate_type := l_deduction_rec.exchange_rate_type;
565 l_qual_deduction_rec.exchange_rate_date := l_deduction_rec.exchange_rate_date;
566 l_qual_deduction_rec.exchange_rate := l_deduction_rec.exchange_rate;
567 l_qual_deduction_rec.set_of_books_id := l_deduction_rec.set_of_books_id;
568 l_qual_deduction_rec.source_object_id := l_deduction_rec.source_object_id;
569 l_qual_deduction_rec.source_object_class := l_deduction_rec.source_object_class;
570 l_qual_deduction_rec.source_object_type_id := l_deduction_rec.source_object_type_id;
571 l_qual_deduction_rec.source_object_number := l_deduction_rec.source_object_number;
572 l_qual_deduction_rec.cust_account_id := l_deduction_rec.cust_account_id;
573 l_qual_deduction_rec.cust_billto_acct_site_id := l_deduction_rec.cust_billto_acct_site_id;
574 l_qual_deduction_rec.cust_shipto_acct_site_id := l_deduction_rec.cust_shipto_acct_site_id;
575 l_qual_deduction_rec.location_id := l_deduction_rec.location_id;
576 l_qual_deduction_rec.reason_code_id := l_deduction_rec.reason_code_id;
577 l_qual_deduction_rec.status_code := l_deduction_rec.status_code;
578 l_qual_deduction_rec.user_status_id := l_deduction_rec.user_status_id;
579 l_qual_deduction_rec.sales_rep_id := l_deduction_rec.sales_rep_id;
580 l_qual_deduction_rec.collector_id := l_deduction_rec.collector_id;
581 l_qual_deduction_rec.contact_id := l_deduction_rec.contact_id;
582 l_qual_deduction_rec.broker_id := l_deduction_rec.broker_id;
583 l_qual_deduction_rec.customer_ref_date := l_deduction_rec.customer_ref_date;
584 l_qual_deduction_rec.customer_ref_number := l_deduction_rec.customer_ref_number;
585 l_qual_deduction_rec.receipt_id := l_deduction_rec.receipt_id;
586 l_qual_deduction_rec.receipt_number := l_deduction_rec.receipt_number;
587 l_qual_deduction_rec.gl_date := l_deduction_rec.gl_date;
588 l_qual_deduction_rec.comments := l_deduction_rec.comments;
589 l_qual_deduction_rec.deduction_attribute_category := l_deduction_rec.deduction_attribute_category;
590 l_qual_deduction_rec.deduction_attribute1 := l_deduction_rec.deduction_attribute1;
591 l_qual_deduction_rec.deduction_attribute2 := l_deduction_rec.deduction_attribute2;
595 l_qual_deduction_rec.deduction_attribute6 := l_deduction_rec.deduction_attribute6;
592 l_qual_deduction_rec.deduction_attribute3 := l_deduction_rec.deduction_attribute3;
593 l_qual_deduction_rec.deduction_attribute4 := l_deduction_rec.deduction_attribute4;
594 l_qual_deduction_rec.deduction_attribute5 := l_deduction_rec.deduction_attribute5;
596 l_qual_deduction_rec.deduction_attribute7 := l_deduction_rec.deduction_attribute7;
597 l_qual_deduction_rec.deduction_attribute8 := l_deduction_rec.deduction_attribute8;
598 l_qual_deduction_rec.deduction_attribute9 := l_deduction_rec.deduction_attribute9;
599 l_qual_deduction_rec.deduction_attribute10 := l_deduction_rec.deduction_attribute10;
600 l_qual_deduction_rec.deduction_attribute11 := l_deduction_rec.deduction_attribute11;
601 l_qual_deduction_rec.deduction_attribute12 := l_deduction_rec.deduction_attribute12;
602 l_qual_deduction_rec.deduction_attribute13 := l_deduction_rec.deduction_attribute13;
603 l_qual_deduction_rec.deduction_attribute14 := l_deduction_rec.deduction_attribute14;
604 l_qual_deduction_rec.deduction_attribute15 := l_deduction_rec.deduction_attribute15;
605 l_qual_deduction_rec.org_id := l_deduction_rec.org_id;
606
607 -- Get default value of assignment manager for a deduction record
608 OZF_ASSIGNMENT_QUALIFIER_PUB.Get_Deduction_Value(
609 p_api_version_number => l_api_version,
610 p_init_msg_list => FND_API.G_FALSE,
611 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
612 p_commit => FND_API.G_FALSE,
613 x_return_status => l_return_status,
614 x_msg_count => x_msg_count,
615 x_msg_data => x_msg_data,
616 p_deduction => l_qual_deduction_rec,
617 x_qualifier => l_qualifier
618 );
619 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
620 RAISE FND_API.G_EXC_ERROR;
621 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 -- assigned the changed values.
626 l_deduction_rec.claim_date := l_qualifier.claim_date;
627 l_deduction_rec.due_date := l_qualifier.due_date;
628 l_deduction_rec.owner_id := l_qualifier.owner_id;
629 l_deduction_rec.cust_billto_acct_site_id := l_qualifier.cust_billto_acct_site_id;
630 l_deduction_rec.cust_shipto_acct_site_id := l_qualifier.cust_shipto_acct_site_id;
631 l_deduction_rec.sales_rep_id := l_qualifier.sales_rep_id;
632 l_deduction_rec.contact_id := l_qualifier.contact_id;
633 l_deduction_rec.broker_id := l_qualifier.broker_id;
634 l_deduction_rec.gl_date := l_qualifier.gl_date;
635 l_deduction_rec.comments := l_qualifier.comments;
636 l_deduction_rec.claim_type_id := l_qualifier.claim_type_id;
637 l_deduction_rec.reason_code_id := l_qualifier.reason_code_id;
638 l_deduction_rec.customer_ref_date := l_qualifier.customer_ref_date;
639 l_deduction_rec.customer_ref_number := l_qualifier.customer_ref_number;
640
641
642 -----------------------------------------
643 -- 2. Minimum required fields checking --
644 -----------------------------------------
645 -- First, check whether all the required fields are filled for deductions.
646 -- These fields are
647 -- cust_account_id
648 -- receipt_id
649 -- currency_code
650 -- amount
651 -- source_object_type_id (required for transaction-related deduction)
652 IF l_deduction_rec.cust_account_id IS NULL OR
653 l_deduction_rec.receipt_id IS NULL OR
654 l_deduction_rec.currency_code IS NULL OR
655 l_deduction_rec.amount IS NULL THEN
656 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
657 FND_MESSAGE.Set_Name('OZF','OZF_REQUIRED_FIELDS_MISSING');
658 FND_MSG_PUB.Add;
659 END IF;
660 RAISE FND_API.g_exc_error;
661 ELSIF l_deduction_rec.source_object_id IS NOT NULL AND
662 l_deduction_rec.source_object_type_id IS NULL THEN
663 -- source_object_type_id is required for transaction-related deduction.
664 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
665 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SRC_INFO_MISSING');
666 FND_MSG_PUB.add;
667 END IF;
668 RAISE FND_API.g_exc_error;
669 END IF;
670
671 ------------------------------------------
672 -- 3. Default and derive column valude --
673 ------------------------------------------
674 -- Claim Investigation
675 IF l_deduction_rec.source_object_id IS NULL THEN
676 -- If it's a claim investigation, change the sign of the amount
677 l_deduction_rec.amount := p_deduction.amount * -1;
678
679 -- derive receipt number from receipt id.
680 IF l_deduction_rec.receipt_number IS NULL THEN
681 OPEN csr_get_receipt_num(l_deduction_rec.receipt_id);
682 FETCH csr_get_receipt_num INTO l_deduction_rec.receipt_number;
683 CLOSE csr_get_receipt_num;
684 END IF;
685
686 ELSE
687 -- Invoice Releated Deduction
688 l_deduction_rec.amount := p_deduction.amount;
689
690 -- derive source_object_number from source_object_id.
691 IF l_deduction_rec.source_object_number IS NULL THEN
695 END IF;
692 OPEN csr_get_trx_num(l_deduction_rec.source_object_id);
693 FETCH csr_get_trx_num INTO l_deduction_rec.source_object_number;
694 CLOSE csr_get_trx_num;
696
697 -- BUG 3680658 Fixing
698 --IF l_deduction_rec.source_object_class IS NULL THEN
699 OPEN csr_get_trx_class(l_deduction_rec.source_object_type_id);
700 FETCH csr_get_trx_class INTO l_deduction_rec.source_object_class;
701 CLOSE csr_get_trx_class;
702 --END IF;
703
704 IF l_deduction_rec.source_object_class = 'INV' THEN
705 l_deduction_rec.source_object_class := 'INVOICE';
706 END IF;
707 END IF;
708
709 -- set claim class
710 IF (l_deduction_rec.amount < 0) THEN
711 l_pvt_claim_rec.claim_class := G_OVERPAYMENT_CLASS;
712 ELSE
713 l_pvt_claim_rec.claim_class := G_DEDUCTION_CLASS;
714 END IF;
715
716 -- set user_status_id as 'OPEN'
717 l_deduction_rec.user_status_id := OZF_UTILITY_PVT.get_default_user_status(
718 'OZF_CLAIM_STATUS'
719 ,'OPEN'
720 );
721
722 ----------------------------------------------
723 -- 4. populate claim record for private api --
724 ----------------------------------------------
725 l_pvt_claim_rec.claim_id := l_deduction_rec.claim_id;
726 l_pvt_claim_rec.claim_date := l_deduction_rec.claim_date;
727 l_pvt_claim_rec.due_date := l_deduction_rec.due_date;
728 l_pvt_claim_rec.gl_date := l_deduction_rec.gl_date;
729 l_pvt_claim_rec.owner_id := l_deduction_rec.owner_id;
730 l_pvt_claim_rec.amount := l_deduction_rec.amount;
731 l_pvt_claim_rec.currency_code := l_deduction_rec.currency_code;
732 l_pvt_claim_rec.exchange_rate_type := l_deduction_rec.exchange_rate_type;
733 l_pvt_claim_rec.exchange_rate_date := l_deduction_rec.exchange_rate_date;
734 l_pvt_claim_rec.exchange_rate := l_deduction_rec.exchange_rate;
735 l_pvt_claim_rec.set_of_books_id := l_deduction_rec.set_of_books_id;
736 l_pvt_claim_rec.receipt_id := l_deduction_rec.receipt_id;
737 l_pvt_claim_rec.receipt_number := l_deduction_rec.receipt_number;
738 l_pvt_claim_rec.source_object_id := l_deduction_rec.source_object_id;
739 l_pvt_claim_rec.source_object_class := l_deduction_rec.source_object_class;
740 l_pvt_claim_rec.source_object_type_id := l_deduction_rec.source_object_type_id;
741 l_pvt_claim_rec.source_object_number := l_deduction_rec.source_object_number;
742 l_pvt_claim_rec.cust_account_id := l_deduction_rec.cust_account_id;
743 l_pvt_claim_rec.ship_to_cust_account_id := l_deduction_rec.ship_to_cust_account_id;
744 l_pvt_claim_rec.cust_billto_acct_site_id:=l_deduction_rec.cust_billto_acct_site_id;
745 l_pvt_claim_rec.cust_shipto_acct_site_id:=l_deduction_rec.cust_shipto_acct_site_id;
746 l_pvt_claim_rec.location_id := l_deduction_rec.location_id;
747 l_pvt_claim_rec.claim_type_id := l_deduction_rec.claim_type_id;
748 l_pvt_claim_rec.reason_code_id := l_deduction_rec.reason_code_id;
749 l_pvt_claim_rec.status_code := l_deduction_rec.status_code;
750 l_pvt_claim_rec.user_status_id := l_deduction_rec.user_status_id;
751 l_pvt_claim_rec.sales_rep_id := l_deduction_rec.sales_rep_id;
752 l_pvt_claim_rec.collector_id := l_deduction_rec.collector_id;
753 l_pvt_claim_rec.contact_id := l_deduction_rec.contact_id;
754 l_pvt_claim_rec.broker_id := l_deduction_rec.broker_id;
755 l_pvt_claim_rec.customer_ref_date := l_deduction_rec.customer_ref_date;
756 l_pvt_claim_rec.customer_ref_number := l_deduction_rec.customer_ref_number;
757 l_pvt_claim_rec.comments := l_deduction_rec.comments;
758 l_pvt_claim_rec.deduction_attribute_category := l_deduction_rec.deduction_attribute_category;
759 l_pvt_claim_rec.deduction_attribute1 := l_deduction_rec.deduction_attribute1;
760 l_pvt_claim_rec.deduction_attribute2 := l_deduction_rec.deduction_attribute2;
761 l_pvt_claim_rec.deduction_attribute3 := l_deduction_rec.deduction_attribute3;
762 l_pvt_claim_rec.deduction_attribute4 := l_deduction_rec.deduction_attribute4;
763 l_pvt_claim_rec.deduction_attribute5 := l_deduction_rec.deduction_attribute5;
764 l_pvt_claim_rec.deduction_attribute6 := l_deduction_rec.deduction_attribute6;
765 l_pvt_claim_rec.deduction_attribute7 := l_deduction_rec.deduction_attribute7;
766 l_pvt_claim_rec.deduction_attribute8 := l_deduction_rec.deduction_attribute8;
767 l_pvt_claim_rec.deduction_attribute9 := l_deduction_rec.deduction_attribute9;
768 l_pvt_claim_rec.deduction_attribute10 := l_deduction_rec.deduction_attribute10;
769 l_pvt_claim_rec.deduction_attribute11 := l_deduction_rec.deduction_attribute11;
770 l_pvt_claim_rec.deduction_attribute12 := l_deduction_rec.deduction_attribute12;
771 l_pvt_claim_rec.deduction_attribute13 := l_deduction_rec.deduction_attribute13;
772 l_pvt_claim_rec.deduction_attribute14 := l_deduction_rec.deduction_attribute14;
773 l_pvt_claim_rec.deduction_attribute15 := l_deduction_rec.deduction_attribute15;
774 l_pvt_claim_rec.org_id := l_deduction_rec.org_id;
775 l_pvt_claim_rec.customer_reason := l_deduction_rec.customer_reason;
776 l_pvt_claim_rec.amount_applied := l_deduction_rec.amount_applied;
777 l_pvt_claim_rec.applied_receipt_id := l_deduction_rec.applied_receipt_id;
781 -------------------------------------------------------
778 l_pvt_claim_rec.applied_receipt_number := l_deduction_rec.applied_receipt_number;
779 l_pvt_claim_rec.legal_entity_id := l_deduction_rec.legal_entity_id;
780
782 -- 5. Check claim common element thought private api --
783 -------------------------------------------------------
784 OZF_CLAIM_PVT.Check_Claim_Common_Element (
785 p_api_version => l_api_version,
786 p_init_msg_list => FND_API.g_false,
787 p_validation_level => FND_API.g_valid_level_full,
788 x_return_status => l_return_status,
789 x_msg_count => x_msg_count,
790 x_msg_data => x_msg_data,
791 p_claim => l_pvt_claim_rec,
792 x_claim => l_cre_claim_rec
793 );
794 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
795 RAISE FND_API.G_EXC_ERROR;
796 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
798 END IF;
799
800 -------------------------------------------------------
801 -- 6. Create claim
802 -------------------------------------------------------
803 OZF_CLAIM_PVT.Create_Claim(
804 p_api_version => l_api_version,
805 p_init_msg_list => FND_API.g_false,
806 p_commit => FND_API.g_false,
807 p_validation_level => FND_API.g_valid_level_full,
808 x_return_status => l_return_status,
809 x_msg_count => x_msg_count,
810 x_msg_data => x_msg_data,
811 p_claim => l_cre_claim_rec,
812 x_claim_id => x_claim_id
813 );
814 -- Check return status from the above procedure call
815 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
816 RAISE FND_API.G_EXC_ERROR;
817 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
819 END IF;
820
821 -------------------------------------------------------
822 -- 7. Assign value to OUT parameters
823 -------------------------------------------------------
824 OPEN csr_get_claim_info(x_claim_id);
825 FETCH csr_get_claim_info INTO x_claim_number
826 , x_claim_reason_code_id
827 , x_claim_reason_name;
828 CLOSE csr_get_claim_info;
829
830 -------------------------------------------------------
831 -- 8. Populate claim line record
832 -------------------------------------------------------
833 -- Calling Private package: Create_claim_Line
834 IF l_deduction_rec.source_object_id IS NULL THEN
835 l_claim_line_rec.claim_id := x_claim_id;
836 l_claim_line_rec.comments := l_deduction_rec.receipt_number;
837 -- l_claim_line_rec.item_description := l_deduction_rec.receipt_number;
838 l_claim_line_rec.claim_currency_amount := l_deduction_rec.amount;
839 ELSE
840 l_claim_line_rec.claim_id := x_claim_id;
841 l_claim_line_rec.source_object_id := l_deduction_rec.source_object_id;
842 l_claim_line_rec.source_object_class := l_deduction_rec.source_object_class;
843 l_claim_line_rec.source_object_type_id := l_deduction_rec.source_object_type_id;
844 l_claim_line_rec.claim_currency_amount := l_deduction_rec.amount;
845 END IF;
846
847 -------------------------------------------------------
848 -- 9. Create claim line
849 -------------------------------------------------------
850 OZF_CLAIM_Line_PVT.Create_Claim_Line(
851 p_api_version => l_api_version,
852 p_init_msg_list => FND_API.g_false,
853 p_commit => FND_API.g_false,
854 p_validation_level => FND_API.g_valid_level_full,
855 x_return_status => l_return_status,
856 x_msg_count => x_msg_count,
857 x_msg_data => x_msg_data,
858 p_claim_line_rec => l_claim_line_rec,
859 x_claim_line_id => l_dummy_number
860 );
861 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
862 RAISE FND_API.G_EXC_ERROR;
863 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 END IF;
866
867 ------------------------- finish -------------------------------
868 Write_Log(l_full_name, 'end');
869
870
871 -- Standard check for p_commit
872 IF FND_API.to_Boolean(p_commit) THEN
873 COMMIT WORK;
874 END IF;
875
876 -- Debug Message
877 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
878 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
879 FND_MESSAGE.Set_Token('TEXT',l_full_name||'End');
880 FND_MSG_PUB.Add;
881 END IF;
882
883 -- Standard call to get message count and if count is 1, get message info.
884 FND_MSG_PUB.Count_And_Get(
885 p_count => x_msg_count,
886 p_data => x_msg_data
887 );
888
889 EXCEPTION
890 WHEN FND_API.G_EXC_ERROR THEN
891 ROLLBACK TO CREATE_CLAIM_GRP;
892 x_return_status := FND_API.G_RET_STS_ERROR;
893 FND_MSG_PUB.Count_And_Get (
894 p_encoded => FND_API.G_FALSE,
895 p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898
899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
903 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CRE_DEDU_ERR');
900 ROLLBACK TO CREATE_CLAIM_GRP;
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
904 FND_MSG_PUB.add;
905 END IF;
906 FND_MSG_PUB.Count_And_Get (
907 p_encoded => FND_API.G_FALSE,
908 p_count => x_msg_count,
909 p_data => x_msg_data
910 );
911
912 WHEN OTHERS THEN
913 ROLLBACK TO CREATE_CLAIM_GRP;
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
916 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CRE_DEDU_ERR');
917 FND_MSG_PUB.add;
918 END IF;
919 FND_MSG_PUB.Count_And_Get (
920 p_encoded => FND_API.G_FALSE,
921 p_count => x_msg_count,
922 p_data => x_msg_data
923 );
924
925 End Create_Deduction;
926
927
928 -- -------------------------------------------------------------------------------------------
929 -- PROCEDURE
930 -- Check_Update_Allowed
931 --
932 -- PURPOSE
933 -- This procedure Checks whether update is allowed on give claim id.
934 --
935 -- PARAMETERS
936 -- p_deduction_rec : Deduction Record
937 -- x_Applicable_Claims_Tbl : Default OUT record.
938 -- x_Notes_Tbl : Default OUT record.
939 --
940 -- NOTES
941 ----------------------------------------------------------------------------------------------
942 PROCEDURE Check_Update_Allowed(
943 p_deduction_rec IN DEDUCTION_REC_TYPE,
944 x_applicable_claims_tbl OUT NOCOPY DEDUCTION_TBL_TYPE,
945 x_notes_tbl OUT NOCOPY CLAIM_NOTES_TBL_TYPE,
946 x_update_allowed_flag OUT NOCOPY VARCHAR2,
947 x_return_status OUT NOCOPY VARCHAR2
948 )
949 IS
950 l_api_version CONSTANT NUMBER := 1.0;
951 l_api_name CONSTANT VARCHAR2(30) := 'Check_Update_Allowed';
952 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
953 l_return_status VARCHAR2(1);
954
955 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction_rec;
956 l_applicable_claims_tbl DEDUCTION_TBL_TYPE;
957 l_notes_tbl CLAIM_NOTES_TBL_TYPE;
958 l_notes VARCHAR2(2000);
959 l_record_count NUMBER:=0;
960 l_ar_receipt_amount NUMBER;
961 l_new_deduction_amount NUMBER;
962 l_deduction_amount NUMBER;
963 l_new_status_code VARCHAR2(30);
964 l_amount_applied NUMBER := 0;
965
966 l_root_amount NUMBER;
967
968 -- get existing claim details.
969 CURSOR split_claim_csr(p_claim_id in number) IS
970 SELECT root_claim_id
971 , claim_id
972 , object_version_number
973 , claim_number
974 , amount
975 , amount_adjusted
976 , amount_remaining
977 , amount_settled
978 , status_code
979 , receipt_number
980 , claim_class
981 , appr_wf_item_key
982 , split_from_claim_id
983 FROM ozf_claims
984 WHERE root_claim_id = p_claim_id
985 ORDER BY claim_id desc;
986
987 l_is_overpayment BOOLEAN := FALSE;
988
989 BEGIN
990 --------------------- initialize -----------------------
991 Write_Log(l_full_name, 'start');
992
993 x_return_status := FND_API.G_RET_STS_SUCCESS;
994
995
996
997 l_ar_receipt_amount := l_deduction_rec.amount_applied;
998 l_root_amount := ABS(l_deduction_rec.amount);
999
1000 x_update_allowed_flag := 'Y';
1001 l_record_count := 0;
1002 -- Loop through split claims - adjusting the amounts and setting status
1003 FOR ref_split_claim_csr IN split_claim_csr (l_deduction_rec.claim_id) LOOP
1004 l_notes := NULL;
1005
1006 IF ref_split_claim_csr.status_code <> 'CLOSED' AND ref_split_claim_csr.amount_remaining <> 0 THEN
1007
1008 l_record_count := l_record_count + 1;
1009
1010 l_notes := 'Claim is affected due to application of Receipt:'||l_deduction_rec.applied_receipt_number||
1011 ' with Applied Amount:'||l_deduction_rec.amount_applied || '.';
1012 l_new_deduction_amount := 0;
1013 l_deduction_amount := ABS(ref_split_claim_csr.amount_remaining);
1014
1015 IF ref_split_claim_csr.claim_class = 'OVERPAYMENT' THEN
1016 l_is_overpayment := TRUE;
1017 END IF;
1018
1019 IF l_root_amount > 0 THEN
1020 l_new_status_code := 'OPEN';
1021 IF l_root_amount < l_deduction_amount THEN
1022 l_new_deduction_amount := l_root_amount;
1023 l_root_amount := 0;
1024
1025 ELSE --- l_root_amount >= l_deduction_amount
1026 l_new_deduction_amount := l_deduction_amount;
1027 l_root_amount := l_root_amount - l_deduction_amount;
1028 END IF;
1029
1030 IF l_is_overpayment THEN
1031 l_deduction_amount := l_deduction_amount * -1;
1032 l_new_deduction_amount := l_new_deduction_amount * -1;
1033 l_ar_receipt_amount := l_ar_receipt_amount * -1;
1034 END IF;
1035
1036 l_notes := l_notes||' Amount is Changed '||
1040 IF ref_split_claim_csr.status_code = 'OPEN' THEN
1037 'From '||ref_split_claim_csr.AMOUNT||
1038 ' To '||l_new_deduction_amount || '.';
1039
1041 l_notes := l_notes||' Status is not Changed. ';
1042 ELSE
1043 l_notes := l_notes|| ' Status is Changed '||
1044 'From '|| ref_split_claim_csr.status_code ||' To OPEN. ';
1045 END IF;
1046
1047 ELSE --- l_root_amount <= 0
1048
1049 --- Cancel Claims. Amounts are unchanged.
1050 l_deduction_amount := 0;
1051 l_new_deduction_amount := l_deduction_amount;
1052 l_new_status_code := 'CANCELLED';
1053
1054 IF l_is_overpayment THEN
1055 l_ar_receipt_amount := l_ar_receipt_amount * -1;
1056 END IF;
1057
1058 --Build Notes
1059 l_notes := l_notes||' Amount is Changed '||
1060 'From '||ref_split_claim_csr.AMOUNT||
1061 ' To '||l_new_deduction_amount || '.';
1062
1063 IF ref_split_claim_csr.status_code = 'CANCELLED' THEN
1064 l_notes := l_notes||' Status is not Changed. ';
1065 ELSE
1066 l_notes := l_notes|| ' Status is Changed '||
1067 'From '|| ref_split_claim_csr.status_code ||' To CANCELLED. ';
1068 END IF;
1069
1070 END IF; --- l_root_amount > 0
1071
1072
1073 IF l_deduction_rec.source_object_id IS NOT NULL AND
1074 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
1075 -- Build Notes :for transaction releated deduction
1076
1077 IF l_deduction_rec.receipt_number <> ref_split_claim_csr.receipt_number THEN
1078 l_notes := l_notes||' Receipt Reference is Changed '||
1079 'From '||ref_split_claim_csr.receipt_number||' To '||l_deduction_rec.receipt_number||'.';
1080 ELSE
1081 l_notes := l_notes||' Receipt Reference '||ref_split_claim_csr.RECEIPT_NUMBER||' is not Changed.';
1082 END IF;
1083 l_applicable_claims_tbl(l_record_count).receipt_id := l_deduction_rec.receipt_id;
1084 l_applicable_claims_tbl(l_record_count).receipt_number := l_deduction_rec.receipt_number;
1085 ELSE
1086 -- Build Notes :for claim investigation
1087 l_notes := l_notes||' Receipt Reference '||ref_split_claim_csr.RECEIPT_NUMBER||' is not Changed.';
1088 END IF;
1089
1090 -- Populate table of deduction record as OUT parameter.
1091 -- Note: Used OWNER ID to store root cliam id
1092 l_applicable_claims_tbl(l_record_count).owner_id := ref_split_claim_csr.split_from_claim_id;
1093 l_applicable_claims_tbl(l_record_count).claim_id := ref_split_claim_csr.claim_id;
1094 l_applicable_claims_tbl(l_record_count).object_version_number := ref_split_claim_csr.object_version_number;
1095 l_applicable_claims_tbl(l_record_count).status_code := l_new_status_code;
1096 l_applicable_claims_tbl(l_record_count).currency_code := l_deduction_rec.currency_code;
1097 l_applicable_claims_tbl(l_record_count).exchange_rate_type := l_deduction_rec.exchange_rate_type;
1098 l_applicable_claims_tbl(l_record_count).exchange_rate_date := l_deduction_rec.exchange_rate_date;
1099 l_applicable_claims_tbl(l_record_count).exchange_rate := l_deduction_rec.exchange_rate;
1100 l_applicable_claims_tbl(l_record_count).amount := l_new_deduction_amount + ref_split_claim_csr.amount_adjusted;
1101 l_applicable_claims_tbl(l_record_count).amount_adjusted := ref_split_claim_csr.amount_adjusted;
1102 l_applicable_claims_tbl(l_record_count).amount_settled := 0;
1103 l_applicable_claims_tbl(l_record_count).amount_remaining := l_new_deduction_amount;
1104 l_applicable_claims_tbl(l_record_count).amount_applied := l_ar_receipt_amount;
1105
1106 -- [Begin of Debug Message]
1107 Write_Log(l_full_name, '---------------------');
1108 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').split_from_claim_id = '||l_applicable_claims_tbl(l_record_count).owner_id);
1109 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').claim_id = '||l_applicable_claims_tbl(l_record_count).claim_id);
1110 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount = '||l_applicable_claims_tbl(l_record_count).amount);
1111 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_adjusted = '||l_applicable_claims_tbl(l_record_count).amount_adjusted);
1112 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_remaining = '||l_applicable_claims_tbl(l_record_count).amount_remaining);
1113 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_settled = '||l_applicable_claims_tbl(l_record_count).amount_settled);
1114 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').status_code = '||l_applicable_claims_tbl(l_record_count).status_code);
1115
1116 --Populate table of Notes record as OUT parameter.
1117 l_notes_tbl(l_record_count).claim_notes := l_notes;
1118 Write_Log(l_full_name, 'l_notes_tbl('||l_record_count||').claim_notes = ' ||l_notes_tbl(l_record_count).claim_notes);
1119
1120 --//Bugfix : 7526516
1121 IF l_root_amount > 0 THEN
1122 -- This means receipt was unapplied and applied with lesser amount.
1123 -- Eg $10 was applied to $100 receipt. This was then changed to $5.
1124 -- Add the excess to the last claim in the table
1128 l_applicable_claims_tbl(l_record_count).amount := l_applicable_claims_tbl(l_record_count).amount + l_root_amount ;
1125 IF l_is_overpayment THEN
1126 l_root_amount := l_root_amount * -1;
1127 END IF;
1129 l_applicable_claims_tbl(l_record_count).amount_remaining := l_applicable_claims_tbl(l_record_count).amount_remaining + l_root_amount ;
1130
1131 -- [Begin of Debug Message]
1132 Write_Log(l_full_name, '---------------------');
1133 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').claim_id = '||l_applicable_claims_tbl(l_record_count).claim_id);
1134 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount = '||l_applicable_claims_tbl(l_record_count).amount);
1135 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_adjusted = '||l_applicable_claims_tbl(l_record_count).amount_adjusted);
1136 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_remaining = '||l_applicable_claims_tbl(l_record_count).amount_remaining);
1137
1138 END IF;
1139
1140 END IF; -- status <> CLOSED
1141 END LOOP;
1142
1143 --Assign to OUT parameter
1144 x_applicable_claims_tbl := l_applicable_claims_tbl;
1145 x_notes_tbl := l_notes_tbl;
1146
1147 Write_Log(l_full_name, 'end');
1148
1149 EXCEPTION
1150 WHEN FND_API.G_EXC_ERROR THEN
1151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1152 WHEN OTHERS THEN
1153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1154 FND_MESSAGE.set_name('OZF', 'OZF_API_DEBUG_MESSAGE');
1155 FND_MESSAGE.set_token('TEXT', l_full_name||'An error happened in '||l_full_name);
1156 FND_MSG_PUB.add;
1157
1158 END Check_Update_Allowed;
1159
1160
1161 -- -------------------------------------------------------------------------------------------
1162 -- PROCEDURE: Perform_Subsequent_Apply
1163 --
1164 -- PURPOSE :
1165 -- This procedure perform Subsequent Application.
1166 -- It calls the Update_claim proceudre in the private package.
1167 --
1168 -- PARAMETERS:
1169 -- IN:
1170 -- p_api_version_number IN NUMBER Required
1171 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1172 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1173 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1174 -- P_deduction IN DEDUCTION_REC_TYPE Required
1175 --
1176 -- OUT:
1177 -- x_return_status OUT VARCHAR2
1178 -- x_msg_count OUT NUMBER
1179 -- x_msg_data OUT VARCHAR2
1180 -- x_object_version_number OUT NUMBER
1181 --
1182 -- Note:
1183 --
1184 ----------------------------------------------------------------------------------------------
1185 PROCEDURE Perform_Subsequent_Apply(
1186 p_api_version IN NUMBER,
1187 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1188 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1189
1190 x_return_status OUT NOCOPY VARCHAR2,
1191 x_msg_count OUT NOCOPY NUMBER,
1192 x_msg_data OUT NOCOPY VARCHAR2,
1193
1194 p_deduction IN DEDUCTION_REC_TYPE
1195 )
1196 IS
1197 l_api_version CONSTANT NUMBER := 1.0;
1198 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Subsequent_Apply';
1199 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1200 l_return_status VARCHAR2(1);
1201
1202 l_object_version_number NUMBER;
1203 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
1204 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
1205 l_applicable_claims_tbl DEDUCTION_TBL_TYPE;
1206 l_notes_tbl CLAIM_NOTES_TBL_TYPE;
1207 l_claim_obj_ver_num NUMBER;
1208 l_claim_count NUMBER := 0;
1209 l_claim_amount NUMBER := 0;
1210 l_receipt_number VARCHAR2(30);
1211 l_split_flag VARCHAR2(3);
1212 l_notes VARCHAR2(2000);
1213 l_status_code VARCHAR2(30);
1214 l_new_status_code VARCHAR2(30);
1215 l_update_allowed_flag VARCHAR2(1);
1216 l_x_note_id NUMBER;
1217 l_claim_class VARCHAR2(30);
1218 l_amount_remaining NUMBER:= 0;
1219 l_new_amount_adjusted NUMBER:= 0;
1220 l_total_amount_applied NUMBER:= 0;
1221 l_source_object_id NUMBER;
1222
1223 -- get Count for given claim_id
1224 CURSOR get_claim_count_csr (p_claim_id in number) IS
1225 SELECT count(claim_id)
1226 FROM ozf_claims
1227 WHERE root_claim_id = p_claim_id;
1228
1229 -- get existing claim details.
1230 CURSOR get_claim_detail_csr (p_claim_id in number) IS
1231 SELECT status_code
1232 , amount
1233 , receipt_number
1234 , claim_class
1235 , source_object_id
1236 FROM ozf_claims
1237 WHERE claim_id = p_claim_id;
1238
1239 -- get existing claim details.
1240 CURSOR split_claim_csr(p_claim_id in number) IS
1241 SELECT root_claim_id
1242 , claim_id
1243 , object_version_number
1244 , claim_number
1245 , amount,amount_adjusted
1246 , amount_remaining
1247 , amount_settled
1251 WHERE root_claim_id = p_claim_id
1248 , receipt_number
1249 , status_code
1250 FROM ozf_claims
1252 AND status_code <> 'CLOSED'
1253 ORDER BY claim_id;
1254
1255 -- get all the parent claims
1256 CURSOR parent_claim_csr(p_claim_id in number) IS
1257 SELECT root_claim_id
1258 , claim_id
1259 , object_version_number
1260 , claim_number
1261 , amount
1262 , amount_adjusted
1263 , amount_remaining
1264 , amount_settled
1265 , status_code
1266 , receipt_number
1267 , claim_class
1268 , split_from_claim_id
1269 FROM ozf_claims
1270 WHERE split_from_claim_id IS NULL
1271 AND root_claim_id = p_claim_id
1272 ORDER BY claim_id desc;
1273
1274 CURSOR child_claim_csr(p_claim_id in number) IS
1275 SELECT SUM(amount)
1276 FROM ozf_claims
1277 WHERE split_from_claim_id = p_claim_id;
1278
1279 l_change_in_amount NUMBER;
1280 l_tot_child_amt NUMBER;
1281
1282 BEGIN
1283 --------------------- initialize -----------------------
1284 Write_Log(l_full_name, 'start');
1285
1286 x_return_status := FND_API.G_RET_STS_SUCCESS;
1287
1288 -- Check if the claim has any splits.
1289 OPEN get_claim_count_csr(l_deduction_rec.claim_id);
1290 FETCH get_claim_count_csr INTO l_claim_count;
1291 CLOSE get_claim_count_csr;
1292
1293 IF l_claim_count = 1 THEN
1294 l_split_flag := 'NO';
1295 ELSIF l_claim_count > 1 THEN
1296 l_split_flag := 'YES';
1297 ELSE
1298 l_split_flag := NULL;
1299 END IF;
1300
1301
1302
1303 --Deal with amount sign in case of OVERPAYMENTS
1304 OPEN get_claim_detail_csr(l_deduction_rec.claim_id);
1305 FETCH get_claim_detail_csr INTO l_status_code
1306 , l_claim_amount
1307 , l_receipt_number
1308 , l_claim_class
1309 , l_source_object_id;
1310 CLOSE get_claim_detail_csr;
1311
1312 IF l_claim_class = 'OVERPAYMENT' THEN
1313 IF l_deduction_rec.amount > 0 THEN
1314 l_deduction_rec.amount := l_deduction_rec.amount * -1;
1315 l_deduction_rec.amount_applied := l_deduction_rec.amount_applied * -1;
1316 END IF;
1317 ELSIF l_claim_class = 'DEDUCTION' AND
1318 (l_source_object_id IS NULL OR l_source_object_id = FND_API.g_miss_num) THEN
1319 IF l_deduction_rec.amount < 0 THEN
1320 l_deduction_rec.amount := l_deduction_rec.amount * -1;
1321 END IF;
1322 END IF;
1323
1324
1325 Write_Log(l_full_name, 'l_deduction_rec.amount = '||l_deduction_rec.amount);
1326 Write_Log(l_full_name, 'l_deduction_rec.amount_applied = '||l_deduction_rec.amount_applied);
1327 Write_Log(l_full_name, 'Split ? '||l_split_flag);
1328
1329
1330 -- -----------------
1331 -- No Split Scenario
1332 -- -----------------
1333 IF l_split_flag = 'NO' THEN
1334
1335 IF l_status_code <> 'CLOSED' THEN
1336 IF l_status_code = 'PENDING_CLOSE' THEN
1337 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1338 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
1339 FND_MSG_PUB.add;
1340 END IF;
1341 RAISE FND_API.G_EXC_ERROR;
1342 END IF;
1343
1344 -- ---------------------
1345 -- Fully Apply
1346 -- ---------------------
1347 IF l_deduction_rec.amount = 0 THEN
1348 --Build Notes. not required
1349 -- l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1350 -- Build Notes(29)
1351 --l_notes := l_notes||'New Balance Amount from AR is Zero, Current Status of cliam was changed From '||
1352 -- l_status_code||' To CANCELLED';
1353
1354 --bugfix 4869928
1355 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_AR_BAL_ZERO');
1356 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE',l_status_code,'CANCELLED' );
1357
1358 l_new_status_code := 'CANCELLED';
1359
1360 Write_Log(l_full_name, 'Full Apply - Cancelling Claim');
1361
1362 -- ---------------------
1363 -- Partial Apply
1364 -- ---------------------
1365 ELSE
1366 -- Build Notes(30)
1367 IF l_status_code = 'OPEN' THEN
1368 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1369 --l_notes := l_notes||' Claim Remaining Balance is changed '||
1370 -- 'From '||l_claim_amount||
1371 -- ' To '||l_deduction_rec.AMOUNT||
1372 -- ' due to Application of amount '||l_deduction_rec.AMOUNT_APPLIED||
1373 -- ' and Status is OPEN, remains the Same';
1374
1375 --bugfix 4869928
1376 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_APPLY_CHANGE',
1377 TO_CHAR(l_claim_amount),
1378 TO_CHAR(l_deduction_rec.AMOUNT),
1379 TO_CHAR(l_deduction_rec.AMOUNT_APPLIED),
1380 TO_CHAR(l_deduction_rec.amount_applied),
1381 TO_CHAR(l_deduction_rec.applied_receipt_number)
1382 );
1383 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_STATUS_SAME', 'OPEN');
1384
1385
1386
1390 -- Build Notes(31)
1387
1388 ELSE
1389 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1391 --l_notes := l_notes||' Claim Remaining Balance is changed '||
1392 -- 'From '||l_claim_amount||
1393 -- ' To '||l_deduction_rec.AMOUNT||
1394 -- ' due to Application of amount '||l_deduction_rec.AMOUNT_APPLIED||
1395 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')'||
1396 -- ' and Status is Changed '||
1397 -- 'From '||l_status_code||' To OPEN';
1398 --bugfix 4869928
1399 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_APPLY_CHANGE',
1400 TO_CHAR(l_claim_amount),
1401 TO_CHAR(l_deduction_rec.AMOUNT),
1402 TO_CHAR(l_deduction_rec.AMOUNT_APPLIED),
1403 TO_CHAR(l_deduction_rec.amount_applied),
1404 TO_CHAR(l_deduction_rec.applied_receipt_number)
1405 );
1406 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE', l_status_code, 'OPEN');
1407
1408
1409 END IF;
1410 l_new_status_code := 'OPEN';
1411 Write_Log(l_full_name, 'Partial Apply - Adjusting Amount');
1412
1413 END IF;
1414
1415 --Build Claim Rec.
1416 l_pvt_claim_rec.claim_id := l_deduction_rec.claim_id;
1417 l_pvt_claim_rec.object_version_number := l_deduction_rec.object_version_number;
1418 l_pvt_claim_rec.amount := l_deduction_rec.amount;
1419 l_pvt_claim_rec.currency_code := l_deduction_rec.currency_code;
1420 l_pvt_claim_rec.exchange_rate_type := l_deduction_rec.exchange_rate_type;
1421 l_pvt_claim_rec.exchange_rate_date := l_deduction_rec.exchange_rate_date;
1422 l_pvt_claim_rec.exchange_rate := l_deduction_rec.exchange_rate;
1423 l_pvt_claim_rec.status_code := l_new_status_code;
1424
1425 IF l_deduction_rec.source_object_id IS NOT NULL AND
1426 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
1427 --Transaction 8/22/2005Releated deduction
1428 l_pvt_claim_rec.receipt_id := l_deduction_rec.receipt_id;
1429 l_pvt_claim_rec.receipt_number := l_deduction_rec.receipt_number;
1430
1431 -- Build Notes (32)
1432 --l_notes := l_notes||' and Receipt Reference is Changed '||
1433 -- 'From '||l_receipt_number||' To '||l_deduction_rec.RECEIPT_NUMBER||'.]';
1434
1435 -- bugfix 4869928
1436 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(l_receipt_number), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
1437
1438 ELSE
1439 -- Claim Investigation
1440 -- Build Notes(33)
1441 --l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
1442
1443 -- bugfix 4869928
1444 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
1445
1446 END IF;
1447
1448 --Assign Applied Details
1449 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
1450 l_pvt_claim_rec.amount_applied := l_deduction_rec.amount_applied;
1451 l_pvt_claim_rec.applied_receipt_id := NVL(l_deduction_rec.applied_receipt_id,l_deduction_rec.receipt_id);
1452 l_pvt_claim_rec.applied_receipt_number := NVL(l_deduction_rec.applied_receipt_number,l_deduction_rec.receipt_number);
1453
1454 -- [Begin of Debug Message]
1455 Write_Log(l_full_name, 'applicable claim.amount = '||l_pvt_claim_rec.amount);
1456 Write_Log(l_full_name, 'applicable claim.amount_applied = '||l_pvt_claim_rec.amount_applied);
1457 Write_Log(l_full_name, 'applicable claim.status_code = '||l_pvt_claim_rec.status_code);
1458 Write_Log(l_full_name, 'applicable claim.receipt_id = '||l_pvt_claim_rec.receipt_id);
1459 Write_Log(l_full_name, 'applicable claim.receipt_number = '||l_pvt_claim_rec.receipt_number);
1460 Write_Log(l_full_name, 'applicable claim.applied_receipt_id = '||l_pvt_claim_rec.applied_receipt_id);
1461 Write_Log(l_full_name, 'applicable claim.applied_receipt_number = '||l_pvt_claim_rec.applied_receipt_number);
1462 -- [End of Debug Message]
1463
1464 -- Call Update_Claim to reflect the changes.
1465 OZF_claim_PVT.Update_claim(
1466 p_api_version => 1.0,
1467 p_init_msg_list => FND_API.G_FALSE,
1468 p_commit => FND_API.G_FALSE,
1469 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1470 x_return_status => l_return_status,
1471 x_msg_count => x_msg_count,
1472 x_msg_data => x_msg_data,
1473 p_claim => l_pvt_claim_rec,
1474 p_event => g_subsequent_apply_event, --g_update_event
1475 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
1476 x_object_version_number => l_object_version_number
1477 );
1478 IF l_return_status = FND_API.G_RET_STS_ERROR then
1479 RAISE FND_API.G_EXC_ERROR;
1480 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 END IF;
1483
1484 ELSE
1485 --Build Notes. (34)
1486 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1490
1487 --l_notes := l_notes||'Status of the claim is CLOSED, NO Subsequent Receipt application'||
1488 -- ' for receipt number '||l_deduction_rec.RECEIPT_NUMBER||
1489 -- ' will be performed on this Claim.]';
1491 -- bugfix 4869928
1492 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_CLAIM_CLOSED');
1493 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_NO_SUBS_APPLY', TO_CHAR(l_deduction_rec.RECEIPT_NUMBER) );
1494
1495
1496 END IF;
1497
1498 --Call Create Notes API.
1499 Write_log(l_full_name,l_notes);
1500 JTF_NOTES_PUB.create_note(
1501 p_api_version => 1.0
1502 ,x_return_status => l_return_status
1503 ,x_msg_count => x_msg_count
1504 ,x_msg_data => x_msg_data
1505 ,p_source_object_id => l_deduction_rec.claim_id
1506 ,p_source_object_code => 'AMS_CLAM'
1507 ,p_notes => l_notes
1508 ,p_note_status => NULL
1509 ,p_entered_by => FND_GLOBAL.user_id
1510 ,p_entered_date => SYSDATE
1511 ,p_last_updated_by => FND_GLOBAL.user_id
1512 ,x_jtf_note_id => l_x_note_id
1513 ,p_note_type => 'AMS_JUSTIFICATION'
1514 ,p_last_update_date => SYSDATE
1515 ,p_creation_date => SYSDATE
1516 );
1517
1518
1519 -- --------------
1520 -- Split Scenario
1521 -- --------------
1522 ELSIF l_split_flag = 'YES' THEN
1523
1524 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.claim_id) LOOP
1525 IF ref_split_claim_csr.status_code = 'PENDING_CLOSE' THEN
1526 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1527 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
1528 FND_MSG_PUB.add;
1529 END IF;
1530 RAISE FND_API.G_EXC_ERROR;
1531 END IF;
1532 END LOOP;
1533
1534 -- ---------------------
1535 -- Fully Apply
1536 -- ---------------------
1537 IF l_deduction_rec.amount = 0 THEN
1538 -- Update Status to CANCELLED for all Claims including root claim and set amount to zero.
1539 -- Select all claims for the root_claim except claim with CLOSED status.
1540 l_new_status_code := 'CANCELLED';
1541
1542 Write_Log(l_full_name, 'Full Apply - Cancelling All Claims');
1543 -- Process Cancellation of claims and update remaining balance to Zero
1544 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.CLAIM_ID) LOOP
1545 l_amount_remaining := (ref_split_claim_csr.AMOUNT_REMAINING + ref_split_claim_csr.AMOUNT_SETTLED);
1546 -- Build Notes (35)
1547 --l_notes := '[Claim:'||ref_split_claim_csr.CLAIM_NUMBER||' Remark:';
1548 --l_notes := l_notes||'Claim Remaining Balance is changed '||
1549 -- 'From '||l_amount_remaining||' To '||l_deduction_rec.AMOUNT||
1550 -- ' due to Application of amount '||l_amount_remaining||
1551 -- ' (Out of AR applied amount '||l_deduction_rec.amount_applied||
1552 -- ' Receipt Number:'||l_deduction_rec.applied_receipt_number||')'||
1553 -- ' and Status is Changed '||'From '||ref_split_claim_csr.STATUS_CODE||
1554 -- ' To CANCELLED';
1555
1556 --bugfix 4869928
1557 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_APPLY_CHANGE'
1558 , TO_CHAR(l_amount_remaining),
1559 TO_CHAR(l_deduction_rec.AMOUNT),
1560 TO_CHAR(l_amount_remaining),
1561 TO_CHAR(l_deduction_rec.amount_applied),
1562 TO_CHAR(l_deduction_rec.applied_receipt_number)
1563 );
1564 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE', ref_split_claim_csr.STATUS_CODE, 'CANCELLED');
1565
1566
1567 --build claim rec.
1568 l_pvt_claim_rec.claim_id := ref_split_claim_csr.claim_id;
1569 l_pvt_claim_rec.object_version_number := ref_split_claim_csr.object_version_number;
1570 l_pvt_claim_rec.status_code := l_new_status_code;
1571 l_pvt_claim_rec.amount := 0;
1572 l_pvt_claim_rec.amount_adjusted := 0;
1573 l_pvt_claim_rec.amount_remaining := 0;
1574 l_pvt_claim_rec.amount_settled := 0;
1575 --l_pvt_claim_rec.amount := l_deduction_rec.amount;
1576 l_pvt_claim_rec.currency_code := l_deduction_rec.currency_code;
1577 l_pvt_claim_rec.exchange_rate_type := l_deduction_rec.exchange_rate_type;
1578 l_pvt_claim_rec.exchange_rate_date := l_deduction_rec.exchange_rate_date;
1579 l_pvt_claim_rec.exchange_rate := l_deduction_rec.exchange_rate;
1580
1581 IF l_deduction_rec.source_object_id IS NOT NULL AND
1582 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
1583 --Transaction Releated deduction
1584 l_pvt_claim_rec.receipt_id := l_deduction_rec.receipt_id;
1585 l_pvt_claim_rec.receipt_number := l_deduction_rec.receipt_number;
1586
1587 -- Build Notes (36)
1588 --l_notes := l_notes||' and Receipt Reference is Changed '||
1589 -- 'From '||l_receipt_number||' To '||l_deduction_rec.RECEIPT_NUMBER||'.]';
1590
1591 -- bugfix 4869928
1592 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(l_receipt_number), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
1593
1594 ELSE
1595 -- Claim Investigation
1596 -- Build Notes(37)
1597 --l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
1598
1599 -- bugfix 4869928
1600 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
1601
1602 END IF;
1603
1604 --Assign Applied Details
1605 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
1606 l_pvt_claim_rec.amount_applied := l_amount_remaining;
1607 l_pvt_claim_rec.applied_receipt_id := NVL(l_deduction_rec.applied_receipt_id,l_deduction_rec.receipt_id);
1608 l_pvt_claim_rec.applied_receipt_number := NVL(l_deduction_rec.applied_receipt_number,l_deduction_rec.receipt_number);
1609
1610 -- [Begin of Debug Message]
1611 Write_Log(l_full_name, 'applicable claim.claim_id = '||l_pvt_claim_rec.claim_id);
1612 Write_Log(l_full_name, 'applicable claim.amount = '||l_pvt_claim_rec.amount);
1613 Write_Log(l_full_name, 'applicable claim.amount_adjusted = '||l_pvt_claim_rec.amount_adjusted);
1614 Write_Log(l_full_name, 'applicable claim.amount_remaining = '||l_pvt_claim_rec.amount_remaining);
1615 Write_Log(l_full_name, 'applicable claim.amount_settled = '||l_pvt_claim_rec.amount_settled);
1616 Write_Log(l_full_name, 'applicable claim.amount_applied = '||l_pvt_claim_rec.amount_applied);
1617 Write_Log(l_full_name, 'applicable claim.status_code = '||l_pvt_claim_rec.status_code);
1618 Write_Log(l_full_name, 'applicable claim.receipt_id = '||l_pvt_claim_rec.receipt_id);
1619 Write_Log(l_full_name, 'applicable claim.receipt_number = '||l_pvt_claim_rec.receipt_number);
1620 Write_Log(l_full_name, 'applicable claim.applied_receipt_id = '||l_pvt_claim_rec.applied_receipt_id);
1621 Write_Log(l_full_name, 'applicable claim.applied_receipt_number= '||l_pvt_claim_rec.applied_receipt_number);
1622 -- [End of Debug Message]
1623
1624 --Call Update_Claim to reflect the changes.
1625 OZF_claim_PVT.Update_claim(
1626 p_api_version => 1.0,
1627 p_init_msg_list => FND_API.G_FALSE,
1628 p_commit => FND_API.G_FALSE,
1629 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1630 x_return_status => l_return_status,
1631 x_msg_count => x_msg_count,
1632 x_msg_data => x_msg_data,
1633 p_claim => l_pvt_claim_Rec,
1634 p_event => G_SUBSEQUENT_APPLY_EVENT, --G_UPDATE_EVENT
1635 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
1636 x_object_version_number => l_object_version_number
1637 );
1638 IF l_return_status = FND_API.G_RET_STS_ERROR then
1639 RAISE FND_API.G_EXC_ERROR;
1640 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1642 END IF;
1643
1644
1645 --Call Create Notes API.
1646 Write_log(l_full_name,l_notes);
1647 JTF_NOTES_PUB.create_note(
1648 p_api_version => 1.0
1649 ,x_return_status => l_return_status
1650 ,x_msg_count => x_msg_count
1651 ,x_msg_data => x_msg_data
1652 ,p_source_object_id => l_deduction_rec.claim_id
1653 ,p_source_object_code => 'AMS_CLAM'
1654 ,p_notes => l_notes
1655 ,p_note_status => NULL
1656 ,p_entered_by => FND_GLOBAL.user_id
1657 ,p_entered_date => SYSDATE
1658 ,p_last_updated_by => FND_GLOBAL.user_id
1659 ,x_jtf_note_id => l_x_note_id
1660 ,p_note_type => 'AMS_JUSTIFICATION'
1661 ,p_last_update_date => SYSDATE
1662 ,p_creation_date => SYSDATE
1663 );
1664
1665 l_notes := null;
1666
1667 END LOOP;
1668
1669 ELSIF l_deduction_rec.AMOUNT <> 0 THEN
1670
1671 Write_Log(l_full_name, 'Partial Apply - Adjusting Amounts');
1672 Check_Update_Allowed(
1673 p_deduction_rec => l_deduction_rec
1677 ,x_return_status => l_return_status
1674 ,x_applicable_claims_tbl => l_Applicable_Claims_Tbl
1675 ,x_notes_tbl => l_notes_tbl
1676 ,x_update_allowed_flag => l_update_allowed_flag
1678 );
1679 IF l_return_status = FND_API.G_RET_STS_ERROR then
1680 RAISE FND_API.G_EXC_ERROR;
1681 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 END IF;
1684
1685 write_log(l_full_name, l_applicable_claims_tbl.count);
1686 FOR i in 1..l_applicable_claims_tbl.count LOOP
1687 write_log(l_full_name, 'i'||i);
1688 --build claim rec.
1689 l_pvt_claim_rec.claim_id := l_applicable_claims_tbl(i).claim_id;
1690 l_pvt_claim_rec.object_version_number := l_applicable_claims_tbl(i).object_version_number;
1691 l_pvt_claim_rec.status_code := l_applicable_claims_tbl(i).status_code;
1692 l_pvt_claim_rec.amount := l_applicable_claims_tbl(i).amount;
1693 l_pvt_claim_rec.amount_adjusted := l_applicable_claims_tbl(i).amount_adjusted;
1694 l_pvt_claim_rec.amount_remaining := l_applicable_claims_tbl(i).amount_remaining;
1695 l_pvt_claim_rec.amount_settled := l_applicable_claims_tbl(i).amount_settled;
1696 l_pvt_claim_rec.amount_applied := l_applicable_claims_tbl(i).amount_applied;
1697 l_pvt_claim_rec.currency_code := l_applicable_claims_tbl(i).currency_code;
1698 l_pvt_claim_rec.exchange_rate_type := l_applicable_claims_tbl(i).exchange_rate_type;
1699 l_pvt_claim_rec.exchange_rate_date := l_applicable_claims_tbl(i).exchange_rate_date;
1700 l_pvt_claim_rec.exchange_rate := l_applicable_claims_tbl(i).exchange_rate;
1701 l_pvt_claim_rec.receipt_id := l_applicable_claims_tbl(i).receipt_id;
1702 l_pvt_claim_rec.receipt_number := l_applicable_claims_tbl(i).receipt_number;
1703 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
1704 l_pvt_claim_rec.applied_receipt_id := l_deduction_rec.applied_receipt_id;
1705 l_pvt_claim_rec.applied_receipt_number := l_deduction_rec.applied_receipt_number;
1706
1707
1708 -- [Begin of Debug Message]
1709 Write_Log(l_full_name, 'claims('||i||').CLAIM_ID ='||l_pvt_claim_Rec.CLAIM_ID);
1710 Write_Log(l_full_name, 'claims('||i||').STATUS_CODE ='||l_pvt_claim_Rec.STATUS_CODE);
1711 Write_Log(l_full_name, 'claims('||i||').AMOUNT ='||l_pvt_claim_Rec.AMOUNT);
1712 Write_Log(l_full_name, 'claims('||i||').AMOUNT_ADJUSTED ='||l_pvt_claim_Rec.AMOUNT_ADJUSTED);
1713 Write_Log(l_full_name, 'claims('||i||').AMOUNT_REMAINING ='||l_pvt_claim_Rec.AMOUNT_REMAINING);
1714 Write_Log(l_full_name, 'claims('||i||').AMOUNT_SETTLED ='||l_pvt_claim_Rec.AMOUNT_SETTLED);
1715 Write_Log(l_full_name, 'claims('||i||').AMOUNT_APPLIED ='||l_pvt_claim_Rec.AMOUNT_APPLIED);
1716 Write_Log(l_full_name, 'claims('||i||').RECEIPT_ID ='||l_pvt_claim_Rec.RECEIPT_ID);
1717 Write_Log(l_full_name, 'claims('||i||').RECEIPT_NUMBER ='||l_pvt_claim_Rec.RECEIPT_NUMBER);
1718 -- [End of Debug Message]
1719
1720 --Call Update_Claim to reflect the changes.
1721 OZF_claim_PVT.Update_claim(
1722 p_api_version => 1.0,
1723 p_init_msg_list => FND_API.G_FALSE,
1724 p_commit => FND_API.G_FALSE,
1725 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1726 x_return_status => l_return_status,
1727 x_msg_count => x_msg_count,
1728 x_msg_data => x_msg_data,
1729 p_claim => l_pvt_claim_Rec,
1730 p_event => G_SUBSEQUENT_APPLY_EVENT, --G_UPDATE_EVENT
1731 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
1732 x_object_version_number => l_object_version_number
1733 );
1734 IF l_return_status = FND_API.G_RET_STS_ERROR then
1735 RAISE FND_API.G_EXC_ERROR;
1736 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1738 END IF;
1739
1740 IF i <= l_notes_tbl.COUNT THEN
1741 --Call Create Notes API.
1742 JTF_NOTES_PUB.create_note(
1743 p_api_version => 1.0
1744 ,x_return_status => l_return_status
1745 ,x_msg_count => x_msg_count
1746 ,x_msg_data => x_msg_data
1747 ,p_source_object_id => l_pvt_claim_rec.claim_id
1748 ,p_source_object_code => 'AMS_CLAM'
1749 ,p_notes => l_notes_tbl(i).claim_notes
1750 ,p_note_status => NULL
1751 ,p_entered_by => FND_GLOBAL.user_id
1752 ,p_entered_date => SYSDATE
1753 ,p_last_updated_by => FND_GLOBAL.user_id
1754 ,x_jtf_note_id => l_x_note_id
1755 ,p_note_type => 'AMS_JUSTIFICATION'
1756 ,p_last_update_date => SYSDATE
1760 END LOOP;
1757 ,p_creation_date => SYSDATE
1758 );
1759 END IF;
1761 END IF; --IF l_deduction_rec.AMOUNT <> 0 THEN
1762
1763 -- Now sync adjustment amounts
1764 update_parent_amounts(
1765 x_return_status => l_return_status
1766 ,x_msg_count => x_msg_count
1767 ,x_msg_data => x_msg_data
1768 ,p_deduction_rec => l_deduction_rec);
1769 IF l_return_status = FND_API.G_RET_STS_ERROR then
1770 RAISE FND_API.G_EXC_ERROR;
1771 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1773 END IF;
1774
1775 END IF; --IF l_split_flag = 'YES' THEN
1776
1777
1778 FND_MSG_PUB.count_and_get(
1779 p_encoded => FND_API.g_false,
1780 p_count => x_msg_count,
1781 p_data => x_msg_data
1782 );
1783
1784 Write_Log(l_full_name, 'end');
1785
1786
1787 EXCEPTION
1788 WHEN FND_API.G_EXC_ERROR THEN
1789 x_return_status := FND_API.G_RET_STS_ERROR;
1790 -- Standard call to get message count and if count=1, get the message
1791 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1792 ,p_count => x_msg_count
1793 ,p_data => x_msg_data
1794 );
1795
1796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798 -- Standard call to get message count and if count=1, get the message
1799 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1800 ,p_count => x_msg_count
1801 ,p_data => x_msg_data
1802 );
1803
1804 WHEN OTHERS THEN
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1807 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
1808 FND_MSG_PUB.add;
1809 END IF;
1810 -- Standard call to get message count and if count=1, get the message
1811 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1812 ,p_count => x_msg_count
1813 ,p_data => x_msg_data
1814 );
1815
1816 End Perform_Subsequent_Apply;
1817
1818
1819 ----------------------------------------------------------------------------------------------
1820 -- PROCEDURE: Perform_Subsequent_Unpply
1821 --
1822 -- PURPOSE :
1823 -- This procedure perform Subsequent Un-Application.
1824 -- It calls the Update_claim proceudre in the private package.
1825 --
1826 -- PARAMETERS:
1827 -- IN:
1828 -- p_api_version_number IN NUMBER Required
1829 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1830 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1831 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1832 -- P_deduction IN DEDUCTION_REC_TYPE Required
1833 --
1834 -- OUT:
1835 -- x_return_status OUT VARCHAR2
1836 -- x_msg_count OUT NUMBER
1837 -- x_msg_data OUT VARCHAR2
1838 -- x_object_version_number OUT NUMBER
1839 --
1840 -- Note:
1841 --
1842 ----------------------------------------------------------------------------------------------
1843 PROCEDURE Perform_Subsequent_Unapply(
1844 p_api_version IN NUMBER,
1845 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1846 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1847
1848 x_return_status OUT NOCOPY VARCHAR2,
1849 x_msg_count OUT NOCOPY NUMBER,
1850 x_msg_data OUT NOCOPY VARCHAR2,
1851
1852 p_deduction IN DEDUCTION_REC_TYPE
1853 )
1854 IS
1855 l_api_version CONSTANT NUMBER := 1.0;
1856 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Subsequent_Unpply';
1857 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1858 l_return_status VARCHAR2(1);
1859
1860 l_object_version_number NUMBER;
1861 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
1862 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
1863 l_Applicable_Claims_Tbl DEDUCTION_REC_TYPE;
1864 l_child_claim_tbl OZF_SPLIT_CLAIM_PVT.Child_Claim_tbl_type;
1865 l_claim_obj_ver_num NUMBER;
1866 l_claim_count NUMBER := 0;
1867 l_split_flag VARCHAR2(3);
1868 l_notes VARCHAR2(2000);
1869 l_status_code VARCHAR2(30);
1870 l_new_status_code VARCHAR2(30);
1871 l_update_allowed_flag VARCHAR2(1);
1872 l_count_claim_on_receipt NUMBER:=0;
1873 l_new_amount NUMBER:=0;
1874 l_new_amount_adjusted NUMBER:=0;
1875 l_claim_class VARCHAR2(30);
1876 l_deduction_type VARCHAR2(30);
1877 l_reason_code_id NUMBER;
1878 l_receipt_number VARCHAR2(30);
1879 l_claim_amount NUMBER := 0;
1880 l_split_claim_id NUMBER;
1881 l_split_claim_number VARCHAR2(30);
1882 l_x_note_id NUMBER;
1883 l_amount_remaining NUMBER:=0;
1887
1884 l_claim_type_id NUMBER:=FND_API.g_miss_num;
1885 l_source_object_id NUMBER;
1886 l_invoice_amount_due NUMBER:=0; --Added on 21-Apr-2003 (aadhawad)
1888 -- get Count for given claim_id
1889 CURSOR get_claim_count_csr (p_claim_id in number) IS
1890 SELECT count(*)
1891 FROM ozf_claims_all
1892 WHERE root_claim_id = p_claim_id;
1893
1894 -- get existing claim details.
1895 CURSOR get_claim_detail_csr (p_claim_id in number) IS
1896 SELECT status_code,amount,receipt_number,claim_class,
1897 amount_remaining+amount_settled amount_remaining,
1898 source_object_id
1899 FROM ozf_claims_all
1900 WHERE claim_id = p_claim_id;
1901
1902 -- Added for Bug4872736
1903 CURSOR get_split_claim_detail_csr (p_claim_id in number) IS
1904 SELECT claim_class,
1905 sum(amount_remaining+amount_settled) amount_remaining,
1906 source_object_id
1907 FROM ozf_claims_all
1908 WHERE root_claim_id = p_claim_id
1909 GROUP BY claim_class, source_object_id;
1910
1911 -- get split claim details.
1912 CURSOR split_claim_csr(p_claim_id in number) IS
1913 SELECT root_claim_id,
1914 claim_id,
1915 object_version_number,
1916 claim_number,
1917 receipt_number,
1918 status_code,
1919 amount,
1920 amount_adjusted,
1921 amount_remaining,
1922 amount_settled
1923 FROM ozf_claims_all
1924 WHERE root_claim_id = p_claim_id
1925 ORDER BY claim_id;
1926
1927 -- get Claim_Class for claim_id
1928 CURSOR get_claim_class_csr(p_claim_id IN NUMBER) IS
1929 SELECT claim_class,reason_code_id,object_version_number,claim_type_id
1930 FROM ozf_claims_all
1931 WHERE claim_id = p_claim_id;
1932
1933 -- get Newly created split claim_id and claim_number
1934 CURSOR get_split_claim_ids_csr(p_claim_id IN NUMBER) IS
1935 SELECT claim_id,claim_number
1936 FROM ozf_claims_all
1937 WHERE root_claim_id = p_claim_id
1938 AND claim_id = (Select max(claim_id)
1939 from ozf_claims_all
1940 where root_claim_id = p_claim_id)
1941 AND trunc(creation_date) = trunc(sysdate);
1942
1943 -- get Amount Due on Invoice
1944 CURSOR get_invoice_amount_due_csr (p_claim_id in NUMBER) IS
1945 SELECT amount_due_original
1946 FROM ar_payment_schedules a, ozf_claims_all b
1947 WHERE customer_trx_id = b.source_object_id
1948 AND b.claim_id = p_claim_id;
1949
1950 -- [BEGIN OF BUG 3775972 FIXING]
1951 l_cancel_all_claim VARCHAR2(1) := 'N';
1952 -- [END OF BUG 3775972 FIXING]
1953
1954 -- Added for Bug5102282
1955 -- Get the payment_schedule_id
1956 CURSOR csr_applied_ps_id( cv_customer_trx_id IN NUMBER, cv_receipt_id IN NUMBER, cv_claim_id IN NUMBER) IS
1957 SELECT distinct applied_payment_schedule_id
1958 FROM ar_receivable_applications_all
1959 WHERE cash_receipt_id = cv_receipt_id
1960 AND applied_customer_trx_id = cv_customer_trx_id
1961 AND application_ref_type = 'CLAIM'
1962 AND secondary_application_ref_id = cv_claim_id
1963 AND status = 'APP';
1964 l_applied_ps_id NUMBER;
1965
1966 -- Added for Bug5102282
1967 -- Get the count of applications
1968 CURSOR csr_cnt_apply(cv_ps_id IN NUMBER, cv_receipt_id IN NUMBER, cv_claim_id IN NUMBER) IS
1969 SELECT COUNT(*)
1970 FROM ar_receivable_applications_all
1971 WHERE applied_payment_schedule_id = cv_ps_id
1972 AND cash_receipt_id <> cv_receipt_id
1973 AND application_ref_type = 'CLAIM'
1974 AND secondary_application_ref_id = cv_claim_id
1975 AND status = 'APP'
1976 AND display = 'Y';
1977 l_cnt_apply_recs NUMBER := 0;
1978 l_new_deduction_amount Number := 0;
1979
1980 BEGIN
1981 --------------------- initialize -----------------------
1982 Write_Log(l_full_name, 'start');
1983
1984 x_return_status := FND_API.G_RET_STS_SUCCESS;
1985
1986
1987 -- Check if the claim has any splits.
1988 OPEN get_claim_count_csr(l_deduction_rec.claim_id);
1989 FETCH get_claim_count_csr INTO l_claim_count;
1990 CLOSE get_claim_count_csr;
1991
1992 -- Get Details for root claimc
1993 OPEN get_claim_detail_csr(l_deduction_rec.claim_id);
1994 FETCH get_claim_detail_csr INTO l_status_code,l_claim_amount,l_receipt_number,
1995 l_claim_class,l_amount_remaining,l_source_object_id;
1996 CLOSE get_claim_detail_csr;
1997
1998 IF l_claim_count = 1 THEN
1999 l_split_flag := 'NO';
2000 ELSIF l_claim_count > 1 THEN
2001 l_split_flag := 'YES';
2002 ELSE
2003 l_split_flag := NULL;
2004 END IF;
2005
2006 Write_Log(l_full_name, 'l_deduction_rec.amount = '||l_deduction_rec.amount);
2007 Write_Log(l_full_name, 'l_deduction_rec.amount_applied = '||l_deduction_rec.amount_applied);
2008
2009 Write_Log(l_full_name, 'Split ? '||l_split_flag);
2010
2011 -- Handling for Invoice Deductions
2012 IF p_deduction.source_object_id IS NOT NULL THEN
2013
2014 -- Obtain the payment schedule id
2015 OPEN csr_applied_ps_id(p_deduction.source_object_id, p_deduction.receipt_id, p_deduction.claim_id);
2016 FETCH csr_applied_ps_id INTO l_applied_ps_id;
2017 CLOSE csr_applied_ps_id;
2018
2019 -- Check if there are any other applications
2020 OPEN csr_cnt_apply(l_applied_ps_id, p_deduction.receipt_id, p_deduction.claim_id);
2021 FETCH csr_cnt_apply INTO l_cnt_apply_recs;
2022 CLOSE csr_cnt_apply;
2023
2024 END IF;
2025
2029 IF l_split_flag = 'NO' THEN
2026 -- -----------------
2027 -- No Split Scenario
2028 -- -----------------
2030 --Deal with amount sign
2031 IF l_claim_class = 'OVERPAYMENT' THEN
2032 IF l_deduction_rec.AMOUNT > 0 THEN
2033 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2034 l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2035 END IF;
2036 END IF;
2037
2038 --Fixed:Date:12-Mar-2003. Partial application for claim investigation.
2039 IF ( l_claim_class = 'DEDUCTION' and
2040 (l_source_object_id is NULL or l_source_object_id = FND_API.g_miss_num)) THEN
2041 IF l_deduction_rec.AMOUNT < 0 THEN
2042 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2043 --l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2044 END IF;
2045 END IF;
2046
2047 IF l_status_code = 'PENDING_CLOSE' THEN
2048 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2049 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
2050 FND_MSG_PUB.add;
2051 END IF;
2052 RAISE FND_API.G_EXC_ERROR;
2053 END IF;
2054
2055 --- BUG 4157743 FIXING
2056 IF l_status_code <> 'CLOSED' THEN
2057
2058 -- Bug4300996/Bug4777500/Bug5102282:Cancel claim
2059 -- For invoice deductions, cancel if there are not other applications
2060 -- For non invoice deductions and overpayments, cancel if amount = 0
2061 IF l_deduction_rec.AMOUNT = 0 OR l_cnt_apply_recs = 0 THEN
2062
2063 Write_Log(l_full_name, 'Cancel ? Yes' );
2064
2065 l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2066 l_notes := l_notes||'For this Claim all receipts are fully Unapplied from AR '||
2067 ' and Status is changed From '||l_status_code||' To CANCELLED';
2068
2069 l_new_status_code := 'CANCELLED';
2070 l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
2071
2072 ELSE
2073 -- Build Notes(39)
2074 -- l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2075 -- l_notes := l_notes||' This Claim Balance is changed '||
2076 -- 'From '||l_amount_remaining||
2077 -- ' To '||l_deduction_rec.AMOUNT||
2078 -- ' due to Unapplication of amount '||l_deduction_rec.AMOUNT_APPLIED||
2079 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')';
2080
2081 -- bugfix 4869928
2082 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_UNAPPLY_CHANGE', TO_CHAR(l_amount_remaining), TO_CHAR(l_deduction_rec.AMOUNT),TO_CHAR(l_deduction_rec.AMOUNT_APPLIED),TO_CHAR(l_deduction_rec.applied_receipt_number) );
2083
2084 IF l_status_code = 'OPEN' THEN
2085 -- Build Notes(40)
2086 -- l_notes := l_notes||' and Status is '||l_status_code||', remains the Same';
2087
2088 -- bugfix 4869928
2089 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_SAME', l_status_code);
2090 l_new_status_code := l_status_code;
2091
2092
2093 ELSE
2094 -- Build Notes(41)
2095 --l_notes := l_notes|| ' and Status is changed From '||l_status_code||' To OPEN';
2096
2097 -- bugfix 4869928
2098 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE', l_status_code, 'OPEN');
2099
2100
2101 l_new_status_code := 'OPEN';
2102 END IF;
2103
2104 --Check for Transaction Related Claim.
2105 IF l_deduction_rec.SOURCE_OBJECT_ID is NOT NULL AND
2106 l_deduction_rec.SOURCE_OBJECT_ID <> FND_API.G_MISS_NUM THEN
2107 --Transaction Related Claim.
2108 l_pvt_claim_Rec.RECEIPT_ID := l_deduction_rec.RECEIPT_ID;
2109 l_pvt_claim_Rec.RECEIPT_NUMBER := nvl(l_deduction_rec.RECEIPT_NUMBER,l_receipt_number);
2110
2111 --Build Event Description. Receipt is not changed because incoming recept number is NULL.
2112 -- Build Notes(42)
2113 --l_notes := l_notes||' and Receipt Reference is Changed '||
2114 -- 'From '||l_receipt_number||
2115 -- ' To '||l_deduction_rec.RECEIPT_NUMBER||'.]';
2116
2117 -- bugfix 4869928
2118 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(l_receipt_number), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
2119
2120 ELSE
2121 --Build Event Description
2122 -- Build Notes(43)
2123 --l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
2124
2125 -- bugfix 4869928
2126 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
2127
2128 END IF;
2129 l_pvt_claim_Rec.AMOUNT := l_deduction_rec.AMOUNT;
2130
2131 END IF;
2132
2133 --Build Claim_Rec.
2134 l_pvt_claim_Rec.CLAIM_ID := l_deduction_rec.CLAIM_ID;
2135 l_pvt_claim_Rec.OBJECT_VERSION_NUMBER := l_deduction_rec.OBJECT_VERSION_NUMBER;
2136 l_pvt_claim_Rec.CURRENCY_CODE := l_deduction_rec.CURRENCY_CODE;
2137 l_pvt_claim_Rec.EXCHANGE_RATE_TYPE := l_deduction_rec.EXCHANGE_RATE_TYPE;
2138 l_pvt_claim_Rec.EXCHANGE_RATE_DATE := l_deduction_rec.EXCHANGE_RATE_DATE;
2142 --In case of Unapply need to update these fileds which will appear on History.
2139 l_pvt_claim_Rec.EXCHANGE_RATE := l_deduction_rec.EXCHANGE_RATE;
2140 l_pvt_claim_Rec.STATUS_CODE := l_new_status_code;
2141
2143 l_pvt_claim_Rec.HISTORY_EVENT_DATE := l_deduction_rec.APPLIED_DATE;
2144 l_pvt_claim_Rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED; --Unapplied Amount
2145 l_pvt_claim_Rec.APPLIED_RECEIPT_ID := l_deduction_rec.APPLIED_RECEIPT_ID;
2146 l_pvt_claim_Rec.APPLIED_RECEIPT_NUMBER := l_deduction_rec.APPLIED_RECEIPT_NUMBER;
2147
2148 --Call Update_Claim to reflect the changes.
2149 OZF_claim_PVT.Update_claim(
2150 P_Api_Version => 1.0,
2151 P_Init_Msg_List => FND_API.G_FALSE,
2152 P_Commit => FND_API.G_FALSE,
2153 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2154 X_Return_Status => x_return_status,
2155 X_Msg_Count => x_msg_count,
2156 X_Msg_Data => x_msg_data,
2157 P_claim => l_pvt_claim_Rec,
2158 p_event => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
2159 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
2160 X_Object_Version_Number => l_object_version_number );
2161
2162 -- Check return status from the above procedure call
2163 IF x_return_status = FND_API.G_RET_STS_ERROR then
2164 raise FND_API.G_EXC_ERROR;
2165 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2166 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2167 END IF;
2168 ELSE
2169 --Build Notes.(44)
2170 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2171 --l_notes := l_notes||'The Status of the claim is CLOSED, NO Subsequent Receipt Unapplication'||
2172 -- ' for receipt number '||l_deduction_rec.APPLIED_RECEIPT_NUMBER||
2173 -- ' will be performed on this Claim.]';
2174
2175 -- bugfix 4869928
2176 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_CLAIM_CLOSED');
2177 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_NO_SUBS_APPLY', TO_CHAR(l_deduction_rec.RECEIPT_NUMBER) );
2178
2179 END IF;
2180
2181 --Call Create Notes API.
2182 Write_log(l_full_name,l_notes);
2183 JTF_NOTES_PUB.create_note(
2184 p_api_version=> 1.0
2185 ,x_return_status=> x_return_status
2186 ,x_msg_count=> x_msg_count
2187 ,x_msg_data=> x_msg_data
2188 ,p_source_object_id=> l_deduction_rec.CLAIM_ID -- claim_id
2189 ,p_source_object_code=> 'AMS_CLAM'
2190 ,p_notes=> l_notes
2191 ,p_note_status=> NULL
2192 ,p_entered_by=> FND_GLOBAL.user_id
2193 ,p_entered_date=> SYSDATE
2194 ,p_last_updated_by=> FND_GLOBAL.user_id
2195 ,x_jtf_note_id=> l_x_note_id
2196 ,p_note_type=> 'AMS_JUSTIFICATION' --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification
2197 ,p_last_update_date=> SYSDATE
2198 ,p_creation_date=> SYSDATE
2199 );
2200
2201 --Initialize l_notes
2202 l_notes := null;
2203 END IF; --IF l_split_flag = 'NO' THEN
2204
2205 -- --------------
2206 -- Split Scenario
2207 -- --------------
2208 IF l_split_flag = 'YES' THEN
2209
2210 IF l_claim_class = 'OVERPAYMENT' THEN
2211 IF l_deduction_rec.AMOUNT > 0 THEN
2212 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2213 l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2214 END IF;
2215 END IF;
2216
2217 -- Bug4300996/Bug4777500/Bug5102282:Cancel claim
2218 -- For invoice deductions, cancel if there are not other applications
2219 -- For non invoice deductions and overpayments, cancel if amount = 0
2220 IF l_cnt_apply_recs = 0 OR l_deduction_rec.amount = 0 THEN
2221 l_cancel_all_claim := 'Y';
2222 END IF;
2223
2224 Write_Log(l_full_name, 'Cancel All ? ' || l_cancel_all_claim );
2225
2226 --Fixed:Date:12-Mar-2003. Partial application for claim investigation.
2227 IF ( l_claim_class = 'DEDUCTION' and
2228 (l_source_object_id is NULL or l_source_object_id = FND_API.g_miss_num)) THEN
2229 IF l_deduction_rec.AMOUNT < 0 THEN
2230 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2231 --l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2232 END IF;
2233 END IF;
2234
2235
2236 IF l_deduction_rec.receipt_id is NOT NULL then
2237 l_update_allowed_flag := 'Y';
2238 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.CLAIM_ID) LOOP
2239 IF ref_split_claim_csr.STATUS_CODE = 'PENDING_CLOSE' THEN
2240 l_update_allowed_flag := 'N';
2241 END IF;
2242 END LOOP;
2243 ELSE
2244 l_update_allowed_flag := 'Y';
2245 END IF;
2246
2247 Write_Log(l_full_name, 'Update Allowed ? ' || l_update_allowed_flag );
2248
2249 IF l_update_allowed_flag = 'N' THEN
2250
2251 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2252 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
2253 FND_MSG_PUB.add;
2254 END IF;
2255 RAISE FND_API.G_EXC_ERROR;
2256 END IF;
2257
2261 --//Bugfix : 7526516
2258 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.CLAIM_ID) LOOP
2259 -- If cancel all claim flag is Y, then cancel all non closed claims
2260 IF l_cancel_all_claim = 'Y' THEN
2262 IF ref_split_claim_csr.status_code NOT IN ('CLOSED', 'CANCELLED','PENDING_APPROVAL') THEN
2263 --Build Notes (46)
2264 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2265 --l_notes := l_notes||' The Status of the Claim is changed From Open to Cancelled';
2266
2267 -- bugfix 4869928
2268 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE','OPEN', 'CANCELLED');
2269
2270 -- Build Notes (47)
2271 --l_notes := l_notes||' due to Unapplication of Amount '||l_deduction_rec.AMOUNT_APPLIED||
2272 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')';
2273 -- bugfix 4869928
2274 l_notes := l_notes|| Build_Note('OZF_CLAIM_NOTES_AMOUNT_UNAPPLY', TO_CHAR(l_deduction_rec.AMOUNT_APPLIED), TO_CHAR(l_deduction_rec.applied_receipt_number) );
2275
2276 --Build Claim Rec.
2277 l_pvt_claim_rec.claim_id := ref_split_claim_csr.claim_id;
2278 l_pvt_claim_rec.object_version_number := ref_split_claim_csr.object_version_number;
2279 l_pvt_claim_rec.status_code := 'CANCELLED';
2280 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
2281 l_pvt_claim_rec.amount_applied := l_deduction_rec.amount_applied; --unapplied amount
2282 l_pvt_claim_rec.applied_receipt_id := l_deduction_rec.applied_receipt_id;
2283 l_pvt_claim_rec.applied_receipt_number := l_deduction_rec.applied_receipt_number;
2284
2285 --Call Update_Claim to reflect the changes.
2286 OZF_claim_PVT.Update_claim(
2287 p_api_version => 1.0,
2288 p_init_msg_list => FND_API.G_FALSE,
2289 p_commit => FND_API.G_FALSE,
2290 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2291 x_return_status => x_return_status,
2292 x_msg_count => x_msg_count,
2293 x_msg_data => x_msg_data,
2294 p_claim => l_pvt_claim_Rec,
2295 p_event => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
2296 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
2297 x_object_version_number => l_object_version_number
2298 );
2299 -- Check return status from the above procedure call
2300 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2301 RAISE FND_API.G_EXC_ERROR;
2302 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2304 END IF;
2305
2306 --Call Create Notes API.
2307 Write_log(l_full_name,l_notes);
2308 JTF_NOTES_PUB.create_note(
2309 p_api_version => 1.0
2310 ,x_return_status => x_return_status
2311 ,x_msg_count => x_msg_count
2312 ,x_msg_data => x_msg_data
2313 ,p_source_object_id => ref_split_claim_csr.claim_id --claim_id
2314 ,p_source_object_code => 'AMS_CLAM'
2315 ,p_notes => l_notes
2316 ,p_note_status => NULL
2317 ,p_entered_by => FND_GLOBAL.user_id
2318 ,p_entered_date => SYSDATE
2319 ,p_last_updated_by => FND_GLOBAL.user_id
2320 ,x_jtf_note_id => l_x_note_id
2321 ,p_note_type => 'AMS_JUSTIFICATION' --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification
2322 ,p_last_update_date => SYSDATE
2323 ,p_creation_date => SYSDATE
2324 );
2325 END IF;
2326 ELSE -- l_cancel_all_claim = 'N'
2327
2328 -- Update the first open/complete/pending approval claim.
2329
2330 l_new_deduction_amount := ref_split_claim_csr.AMOUNT + l_deduction_rec.AMOUNT_APPLIED;
2331 --//Bugfix : 7526516
2332 IF ref_split_claim_csr.status_code NOT IN ('CLOSED', 'CANCELLED','PENDING_APPROVAL') THEN
2333 --Build Notes
2334 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2335
2336 IF ref_split_claim_csr.STATUS_CODE <> 'OPEN' THEN
2337 l_new_status_code := 'OPEN';
2338 --Build Notes(52)
2339 --l_notes := l_notes||'The Status of the Claim is changed From '||ref_split_claim_csr.STATUS_CODE||
2340 -- ' To '||l_new_status_code;
2341
2342 --bugfix 4869928
2343 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE',ref_split_claim_csr.STATUS_CODE,l_status_code);
2344
2345 ELSE
2346 l_new_status_code := ref_split_claim_csr.STATUS_CODE;
2347 --Build Notes (53)
2348 --l_notes := l_notes||'The Status of the Claim ('||l_new_status_code||
2349 -- ') is NOT changed ';
2350
2351 --bugfix 4869928
2352 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_SAME',l_new_status_code);
2353
2354 END IF;
2355
2356
2360 -- ' due to Unapplication of Amount '||l_deduction_rec.AMOUNT_APPLIED||
2357 --Build Notes (54)
2358 --l_notes := l_notes||' and Amount is changed '||
2359 -- 'From '||ref_split_claim_csr.AMOUNT||' To '||l_deduction_rec.AMOUNT||
2361 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')';
2362
2363 --bugfix 4869928
2364 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_UNAPPLY_CHANGE'
2365 , TO_CHAR(ref_split_claim_csr.AMOUNT)
2366 , TO_CHAR(l_deduction_rec.AMOUNT)
2367 , TO_CHAR(l_deduction_rec.AMOUNT_APPLIED)
2368 , TO_CHAR(l_deduction_rec.applied_receipt_number)
2369 );
2370
2371 --Build Claim Rec.
2372 l_pvt_claim_Rec.CLAIM_ID := ref_split_claim_csr.CLAIM_ID;
2373 l_pvt_claim_Rec.OBJECT_VERSION_NUMBER := ref_split_claim_csr.OBJECT_VERSION_NUMBER;
2374 l_pvt_claim_Rec.STATUS_CODE := l_new_status_code;
2375 l_pvt_claim_Rec.AMOUNT := l_new_deduction_amount;
2376 l_pvt_claim_Rec.CURRENCY_CODE := l_deduction_rec.CURRENCY_CODE;
2377 l_pvt_claim_Rec.EXCHANGE_RATE_TYPE := l_deduction_rec.EXCHANGE_RATE_TYPE;
2378 l_pvt_claim_Rec.EXCHANGE_RATE_DATE := l_deduction_rec.EXCHANGE_RATE_DATE;
2379 l_pvt_claim_Rec.EXCHANGE_RATE := l_deduction_rec.EXCHANGE_RATE;
2380
2381
2382 IF l_deduction_rec.source_object_id IS NOT NULL AND
2383 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
2384 -- Build Notes :for transaction releated deduction
2385
2386 IF l_deduction_rec.receipt_number <> ref_split_claim_csr.receipt_number THEN
2387 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(ref_split_claim_csr.RECEIPT_NUMBER), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
2388 ELSE
2389 l_notes := l_notes||' Receipt Reference '||ref_split_claim_csr.RECEIPT_NUMBER||' is not Changed.';
2390 END IF;
2391 l_pvt_claim_Rec.receipt_id := l_deduction_rec.receipt_id;
2392 l_pvt_claim_Rec.receipt_number := l_deduction_rec.receipt_number;
2393 ELSE
2394 -- Build Notes :for claim investigation
2395 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
2396 END IF;
2397
2398 --Assign Applied Details to l_pvt_claim_rec
2399 l_pvt_claim_Rec.HISTORY_EVENT_DATE := l_deduction_rec.APPLIED_DATE;
2400 l_pvt_claim_Rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED; --Unapplied Amount
2401 l_pvt_claim_Rec.APPLIED_RECEIPT_ID := l_deduction_rec.APPLIED_RECEIPT_ID;
2402 l_pvt_claim_Rec.APPLIED_RECEIPT_NUMBER := l_deduction_rec.APPLIED_RECEIPT_NUMBER;
2403
2404 --Call Update_Claim to reflect the changes.
2405 OZF_claim_PVT.Update_claim(
2406 P_Api_Version => 1.0,
2407 P_Init_Msg_List => FND_API.G_FALSE,
2408 P_Commit => FND_API.G_FALSE,
2409 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2410 X_Return_Status => x_return_status,
2411 X_Msg_Count => x_msg_count,
2412 X_Msg_Data => x_msg_data,
2413 P_claim => l_pvt_claim_Rec,
2414 p_event => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
2415 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
2416 X_Object_Version_Number => l_object_version_number );
2417
2418 -- Check return status from the above procedure call
2419 IF x_return_status = FND_API.G_RET_STS_ERROR then
2420 raise FND_API.G_EXC_ERROR;
2421 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2422 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2423 END IF;
2424
2425 --Call Create Notes API.
2426 Write_log(l_full_name,l_notes);
2427 JTF_NOTES_PUB.create_note(
2428 p_api_version=> 1.0
2429 ,x_return_status=> x_return_status
2430 ,x_msg_count=> x_msg_count
2431 ,x_msg_data=> x_msg_data
2432 ,p_source_object_id=> l_deduction_rec.CLAIM_ID --claim_id
2433 ,p_source_object_code=> 'AMS_CLAM'
2434 ,p_notes=> l_notes
2435 ,p_note_status=> NULL
2436 ,p_entered_by=> FND_GLOBAL.user_id
2437 ,p_entered_date=> SYSDATE
2438 ,p_last_updated_by=> FND_GLOBAL.user_id
2439 ,x_jtf_note_id=> l_x_note_id
2440 ,p_note_type=> 'AMS_JUSTIFICATION' --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification
2441 ,p_last_update_date=> SYSDATE
2442 ,p_creation_date=> SYSDATE
2443 );
2444
2445 -- Now sync adjustment amounts
2446 update_parent_amounts(
2447 x_return_status => l_return_status
2448 ,x_msg_count => x_msg_count
2449 ,x_msg_data => x_msg_data
2450 ,p_deduction_rec => l_deduction_rec);
2451 IF l_return_status = FND_API.G_RET_STS_ERROR then
2452 RAISE FND_API.G_EXC_ERROR;
2456
2453 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2455 END IF;
2457 EXIT; -- Work is done !
2458 END IF; -- Is claim open/complete/pending_approval?
2459 END IF; -- if l_cancel_claim = 'Y' */
2460 END LOOP;
2461 END IF; --IF l_split_flag = 'YES' THEN
2462
2463 -- Standard call to get message count and if count is 1, get message info.
2464 FND_MSG_PUB.Count_And_Get
2465 (p_count => x_msg_count,
2466 p_data => x_msg_data
2467 );
2468
2469 Write_Log(l_full_name, 'end');
2470
2471 EXCEPTION
2472 WHEN FND_API.G_EXC_ERROR THEN
2473 x_return_status := FND_API.G_RET_STS_ERROR;
2474 -- Standard call to get message count and if count=1, get the message
2475 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2476 ,p_count => x_msg_count
2477 ,p_data => x_msg_data
2478 );
2479
2480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2482 -- Standard call to get message count and if count=1, get the message
2483 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2484 ,p_count => x_msg_count
2485 ,p_data => x_msg_data
2486 );
2487 WHEN OTHERS THEN
2488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2489 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2490 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
2491 FND_MSG_PUB.add;
2492 END IF;
2493 -- Standard call to get message count and if count=1, get the message
2494 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2495 ,p_count => x_msg_count
2496 ,p_data => x_msg_data
2497 );
2498
2499 End Perform_Subsequent_Unapply;
2500
2501
2502 ---------------------------------------------------------------------
2503 -- PROCEDURE: Update_Deduction
2504 --
2505 -- PURPOSE: This procedure update a Deduction. It calls the Update_claim function
2506 -- in the private package.
2507 --
2508 -- PARAMETERS:
2509 -- IN
2510 -- p_api_version_number IN NUMBER Required
2511 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2512 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2513 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2514 -- P_deduction IN DEDUCTION_REC_TYPE Required
2515 --
2516 -- OUT:
2517 -- x_return_status OUT VARCHAR2
2518 -- x_msg_count OUT NUMBER
2519 -- x_msg_data OUT VARCHAR2
2520 -- x_object_version_number OUT NUMBER
2521 --
2522 -- NOTE:
2523 --
2524 ---------------------------------------------------------------------
2525 PROCEDURE Update_Deduction(
2526 p_api_version_number IN NUMBER,
2527 p_init_msg_list IN VARCHAR2,
2528 p_validation_level IN NUMBER,
2529 p_commit IN VARCHAR2,
2530
2531 x_return_status OUT NOCOPY VARCHAR2,
2532 x_msg_count OUT NOCOPY NUMBER,
2533 x_msg_data OUT NOCOPY VARCHAR2,
2534
2535 p_deduction IN DEDUCTION_REC_TYPE,
2536 x_object_version_number OUT NOCOPY NUMBER
2537 )
2538 IS
2539 x_claim_reason_code_id NUMBER;
2540 x_claim_reason_name VARCHAR2(80);
2541 x_claim_id NUMBER;
2542 x_claim_number VARCHAR2(30);
2543
2544 BEGIN
2545 -- Initialize API return status to SUCCESS
2546 x_return_status := FND_API.G_RET_STS_SUCCESS;
2547
2548 OZF_Claim_GRP.Update_Deduction(
2549 p_api_version_number,
2550 p_init_msg_list,
2551 p_validation_level,
2552 p_commit,
2553 x_return_status,
2554 x_msg_count,
2555 x_msg_data,
2556 p_deduction,
2557 x_object_version_number,
2558 x_claim_reason_code_id,
2559 x_claim_reason_name,
2560 x_claim_id,
2561 x_claim_number
2562 );
2563
2564 EXCEPTION
2565 WHEN OTHERS THEN
2566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2567 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2568 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
2569 FND_MSG_PUB.add;
2570 END IF;
2571 FND_MSG_PUB.Count_And_Get (
2572 p_encoded => FND_API.G_FALSE,
2573 p_count => x_msg_count,
2574 p_data => x_msg_data
2575 );
2576 End Update_Deduction;
2577
2578
2579 ---------------------------------------------------------------------
2580 -- PROCEDURE: Update_Deduction
2581 --
2582 -- PURPOSE: This procedure update a Deduction. It calls the Update_claim function
2583 -- in the private package.
2584 --
2585 -- PARAMETERS:
2586 -- IN:
2587 -- p_api_version_number IN NUMBER Required
2591 -- P_deduction IN DEDUCTION_REC_TYPE Required
2588 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2589 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2590 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2592 --
2593 -- OUT:
2594 -- x_return_status OUT NOCOPY VARCHAR2
2595 -- x_msg_count OUT NOCOPY NUMBER
2596 -- x_msg_data OUT NOCOPY VARCHAR2
2597 -- x_object_version_number OUT NOCOPY NUMBER
2598 -- X_CLAIM_REASON_CODE_ID OUT NOCOPY NUMBER
2599 -- X_CLAIM_REASON_NAME OUT NOCOPY VARCHAR2
2600 -- X_CLAIM_ID, OUT NOCOPY NUMBER
2601 -- X_CLAIM_NUMBER OUT NOCOPY VARCHAR2
2602 --
2603 -- Note:
2604 --
2605 ---------------------------------------------------------------------
2606 PROCEDURE Update_Deduction(
2607 p_api_version_number IN NUMBER,
2608 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2609 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2610 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2611
2612 x_return_status OUT NOCOPY VARCHAR2,
2613 x_msg_count OUT NOCOPY NUMBER,
2614 x_msg_data OUT NOCOPY VARCHAR2,
2615
2616 p_deduction IN DEDUCTION_REC_TYPE,
2617 x_object_version_number OUT NOCOPY NUMBER,
2618 x_claim_reason_code_id OUT NOCOPY NUMBER,
2619 x_claim_reason_name OUT NOCOPY VARCHAR2,
2620 x_claim_id OUT NOCOPY NUMBER,
2621 x_claim_number OUT NOCOPY VARCHAR2
2622 )
2623 IS
2624 l_api_version CONSTANT NUMBER := 1.0;
2625 l_api_name CONSTANT VARCHAR2(30) := 'Update_Deduction';
2626 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME||'.'||l_api_name;
2627 l_return_status VARCHAR2(1);
2628 --
2629 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
2630
2631 CURSOR csr_claim_identifier(cv_source_object_id IN NUMBER) IS
2632 -- [BEGIN OF BUG 4130258 FIXING]
2633 -- SELECT claim_id
2634 SELECT root_claim_id
2635 FROM ozf_claims
2636 WHERE source_object_id = cv_source_object_id;
2637 -- ORDER BY claim_id DESC;
2638 -- [END OF BUG 4130258 FIXING]
2639
2640 CURSOR csr_claim_object_version(cv_claim_id IN NUMBER) IS
2641 SELECT claim_number
2642 , object_version_number
2643 FROM ozf_claims
2644 WHERE claim_id = cv_claim_id;
2645
2646 CURSOR csr_ar_receipt(cv_receipt_id IN NUMBER) IS
2647 SELECT receipt_number
2648 FROM ar_cash_receipts
2649 WHERE cash_receipt_id = cv_receipt_id;
2650
2651 CURSOR csr_return_claim_info(cv_claim_id IN NUMBER) IS
2652 SELECT c.claim_id
2653 , c.claim_number
2654 , c.object_version_number
2655 , c.reason_code_id
2656 , r.name
2657 FROM ozf_claims c
2658 , ozf_reason_codes_vl r
2659 WHERE c.claim_id = cv_claim_id
2660 AND c.reason_code_id = r.reason_code_id;
2661
2662 -- Fix for 5182492
2663 CURSOR csr_previous_claim_info(cv_claim_id IN NUMBER) IS
2664 SELECT amount ,
2665 due_date ,
2666 claim_date ,
2667 claim_type_id ,
2668 reason_code_id ,
2669 currency_code ,
2670 cust_account_id
2671 FROM ozf_claims
2672 WHERE claim_id = cv_claim_id;
2673
2674 l_object_version_number NUMBER;
2675 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
2676 l_user_status_id NUMBER :=FND_API.G_MISS_NUM;
2677 l_custom_setup_id NUMBER;
2678 l_claim_obj_ver_num NUMBER;
2679
2680 -- Fix for 5182492
2681 l_claim_old_amount NUMBER;
2682 l_claim_old_duedate DATE;
2683 l_claim_old_typeId NUMBER;
2684 l_claim_old_reasonId NUMBER;
2685 l_claim_old_date DATE;
2686 l_claim_old_currencyCode VARCHAR2(15);
2687 l_claim_old_accId NUMBER;
2688 l_perform_subs_apply BOOLEAN;
2689
2690 -- get object_version_number, claim_reason_code_id
2691 CURSOR claim_number_csr(p_id in number) IS
2692 SELECT object_version_number,
2693 reason_code_id,
2694 claim_id,
2695 claim_number
2696 FROM ozf_claims_all
2697 WHERE claim_id = p_id;
2698
2699 -- get claim_reason_name
2700 CURSOR claim_name_csr(p_reason_code_id in number) IS
2701 SELECT name
2702 FROM ozf_reason_codes_all_tl
2703 WHERE reason_code_id = p_reason_code_id;
2704
2705 -- get object_version_number
2706 CURSOR claim_object_version_csr(p_id in number) IS
2707 SELECT object_version_number
2708 FROM ozf_claims_all
2709 WHERE claim_id = p_id;
2710
2711 -- get minimum required data
2712 CURSOR claim_min_req_data_csr(p_id in number) IS
2713 SELECT claim_number
2714 FROM ozf_claims_all
2715 WHERE claim_id = p_id;
2716
2717 -- get claim_id, claim_number incase it is not passed by AR.
2718 CURSOR get_claim_identifier_csr(p_source_object_id in number) IS
2719 SELECT root_claim_id,claim_number
2720 FROM ozf_claims_all
2721 WHERE source_object_id = p_source_object_id
2722 AND root_claim_id = claim_id;
2723 --AND status_code <> 'CLOSED';
2724
2725
2726 BEGIN
2730 Write_Log(l_full_name, 'start');
2727 -- Standard Start of API savepoint
2728 SAVEPOINT UPDATE_CLAIM_GRP;
2729
2731
2732 -- Standard call to check for call compatibility.
2733 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2734 p_api_version_number,
2735 l_api_name,
2736 G_PKG_NAME ) THEN
2737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2738 END IF;
2739
2740 -- Initialize message list if p_init_msg_list is set to TRUE.
2741 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2742 FND_MSG_PUB.initialize;
2743 END IF;
2744
2745 -- Initialize API return status to SUCCESS
2746 x_return_status := FND_API.G_RET_STS_SUCCESS;
2747
2748 -----------------------------------------
2749 -- 1. Minimum required fields checking --
2750 -----------------------------------------
2751 -- "claim_id" or "source_object_id" is required field for updat_deduction
2752 IF l_deduction_rec.claim_id IS NULL AND
2753 l_deduction_rec.source_object_id IS NULL THEN
2754 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2755 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUFFICIENT_VAL');
2756 FND_MSG_PUB.add;
2757 END IF;
2758 RAISE FND_API.G_EXC_ERROR;
2759 END IF;
2760
2761 IF l_deduction_rec.applied_action_type IS NULL OR
2762 l_deduction_rec.applied_action_type NOT IN ('A','U') THEN
2763 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2764 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INVALID_ACTION');
2765 FND_MSG_PUB.add;
2766 END IF;
2767 RAISE FND_API.G_EXC_ERROR;
2768 ELSE
2769 IF l_deduction_rec.applied_action_type = 'A' THEN
2770 IF l_deduction_rec.amount IS NULL OR
2771 l_deduction_rec.receipt_id IS NULL THEN
2772 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2773 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_INSUFFICIENT_VAL_A');
2774 FND_MSG_PUB.add;
2775 END IF;
2776 RAISE FND_API.G_EXC_ERROR;
2777 END IF;
2778 ELSIF l_deduction_rec.applied_action_type = 'U' THEN
2779 IF l_deduction_rec.amount IS NULL OR
2780 l_deduction_rec.amount_applied IS NULL OR
2781 l_deduction_rec.applied_receipt_id IS NULL THEN
2782 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2783 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_INSUFFICIENT_VAL_U');
2784 FND_MSG_PUB.add;
2785 END IF;
2786 RAISE FND_API.G_EXC_ERROR;
2787 END IF;
2788 END IF;
2789 END IF;
2790
2791 ------------------------------------------
2792 -- 2. Default and derive column valude --
2793 ------------------------------------------
2794 --derive claim_id, claim_number from source_object_id
2795 IF l_deduction_rec.claim_id IS NULL THEN
2796 OPEN csr_claim_identifier(l_deduction_rec.source_object_id);
2797 FETCH csr_claim_identifier INTO l_deduction_rec.claim_id;
2798 CLOSE csr_claim_identifier;
2799 END IF;
2800
2801 -- Get latest object_version_number
2802 OPEN csr_claim_object_version(l_deduction_rec.claim_id);
2803 FETCH csr_claim_object_version INTO l_deduction_rec.claim_number
2804 , l_deduction_rec.object_version_number;
2805 CLOSE csr_claim_object_version;
2806
2807 -- derive receipt_number from receipt_id
2808 IF l_deduction_rec.receipt_id IS NOT NULL AND
2809 l_deduction_rec.receipt_number IS NULL THEN
2810 OPEN csr_ar_receipt(l_deduction_rec.receipt_id);
2811 FETCH csr_ar_receipt INTO l_deduction_rec.receipt_number;
2812 CLOSE csr_ar_receipt;
2813 END IF;
2814
2815 -- switch amount sign for claim investigation
2816 IF l_deduction_rec.source_object_id IS NULL THEN
2817 l_deduction_rec.amount := l_deduction_rec.amount * -1;
2818 END IF;
2819
2820 -- [Begin of Debug Message]
2821 Write_Log(l_full_name, 'claim_number = '||l_deduction_rec.claim_number);
2822 Write_Log(l_full_name, 'amount = '||l_deduction_rec.amount);
2823 Write_Log(l_full_name, 'action type = '||l_deduction_rec.applied_action_type);
2824 Write_Log(l_full_name, 'apply receipt id = '||l_deduction_rec.applied_receipt_id);
2825 Write_Log(l_full_name, 'reference receipt = '||l_deduction_rec.receipt_number);
2826 -- [Begin of Debug Message]
2827
2828
2829 ----------------------------------------------------------
2830 -- 3. Required Fields checking for Apply or Unapply Action
2831 ----------------------------------------------------------
2832 IF (l_deduction_rec.claim_id IS NULL OR
2833 l_deduction_rec.claim_id = FND_API.G_MISS_NUM
2834 ) OR
2835 (l_deduction_rec.amount IS NULL OR
2836 l_deduction_rec.amount = FND_API.G_MISS_NUM
2837 ) THEN
2838 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2839 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUF_VAL_UPD');
2840 FND_MSG_PUB.add;
2841 END IF;
2842 RAISE FND_API.G_EXC_ERROR;
2843 ELSE
2844 IF l_deduction_rec.applied_action_type = 'A' THEN
2845 IF (l_deduction_rec.amount_applied IS NULL OR
2846 l_deduction_rec.amount_applied = FND_API.G_MISS_NUM
2850 ) THEN
2847 ) OR
2848 (l_deduction_rec.applied_receipt_id IS NULL OR
2849 l_deduction_rec.applied_receipt_id = FND_API.G_MISS_NUM
2851 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2852 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUF_VAL_UPD');
2853 FND_MSG_PUB.add;
2854 END IF;
2855 RAISE FND_API.G_EXC_ERROR;
2856 END IF;
2857 ELSIF l_deduction_rec.applied_action_type = 'U' THEN
2858 IF (l_deduction_rec.amount_applied IS NULL OR
2859 l_deduction_rec.amount_applied = FND_API.G_MISS_NUM
2860 ) OR
2861 (l_deduction_rec.applied_receipt_id IS NULL OR
2862 l_deduction_rec.applied_receipt_id = FND_API.G_MISS_NUM
2863 ) THEN
2864 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2865 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUF_VAL_UPD');
2866 FND_MSG_PUB.add;
2867 END IF;
2868 RAISE FND_API.G_EXC_ERROR;
2869 END IF;
2870 ELSE
2871 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2872 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INVALID_ACTION_UPD');
2873 FND_MSG_PUB.add;
2874 END IF;
2875 RAISE FND_API.G_EXC_ERROR;
2876 END IF;
2877 END IF;
2878
2879 IF l_deduction_rec.applied_action_type = 'A' THEN
2880 ----------------
2881 -- 4.1. Apply --
2882 ----------------
2883 -- Fix for 5182492
2884 -- If invoice deduction, then perform subsequent apply always
2885 -- If claim investigation, then check if sign of amount has changed.
2886 -- If amount sign has changed, then a new claim has to be created.
2887
2888 l_perform_subs_apply := TRUE;
2889 IF l_deduction_rec.source_object_id IS NULL THEN
2890 OPEN csr_previous_claim_info(l_deduction_rec.claim_id);
2891 FETCH csr_previous_claim_info INTO l_claim_old_amount,
2892 l_claim_old_duedate,
2893 l_claim_old_date,
2894 l_claim_old_typeId,
2895 l_claim_old_reasonId,
2896 l_claim_old_currencyCode,
2897 l_claim_old_accId;
2898 CLOSE csr_previous_claim_info;
2899
2900 IF SIGN(l_deduction_rec.amount) <> SIGN(l_claim_old_amount * -1) THEN
2901
2902 Write_Log(l_full_name, 'Creating a new Claim');
2903 l_perform_subs_apply := FALSE;
2904 l_deduction_rec.claim_id := FND_API.G_MISS_NUM;
2905 l_deduction_rec.claim_number := FND_API.G_MISS_CHAR;
2906 l_deduction_rec.claim_date := l_claim_old_date;
2907 l_deduction_rec.due_date := l_claim_old_duedate;
2908 l_deduction_rec.claim_type_id := l_claim_old_typeId;
2909 l_deduction_rec.reason_code_id := l_claim_old_reasonId;
2910 l_deduction_rec.currency_code := l_claim_old_currencyCode;
2911 l_deduction_rec.cust_account_id := l_claim_old_accId;
2912
2913 Create_Deduction(
2914 p_api_version_number => 1.0,
2915 p_init_msg_list => FND_API.g_false,
2916 p_validation_level => FND_API.g_valid_level_full,
2917 p_commit => FND_API.g_false,
2918 x_return_status => x_return_status,
2919 x_msg_count => x_msg_count,
2920 x_msg_data => x_msg_data,
2921 p_deduction => l_deduction_rec,
2922 x_claim_id => x_claim_id,
2923 x_claim_number => x_claim_number,
2924 x_claim_reason_code_id => x_claim_reason_code_id,
2925 x_claim_reason_name => x_claim_reason_name
2926 );
2927 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2928 RAISE FND_API.G_EXC_ERROR;
2929 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2931 END IF;
2932 END IF; -- amount sign has changed
2933 END IF ; -- is inv deduction
2934
2935 IF l_perform_subs_apply THEN
2936 Perform_Subsequent_Apply(
2937 p_api_version => l_api_version
2938 ,p_init_msg_list => FND_API.g_false
2939 ,p_validation_level => FND_API.g_valid_level_full
2940 ,x_return_status => l_return_status
2941 ,x_msg_count => x_msg_count
2942 ,x_msg_data => x_msg_data
2943 ,p_deduction => l_deduction_rec
2944 );
2945 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2946 RAISE FND_API.G_EXC_ERROR;
2947 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2949 END IF;
2950 END IF;
2951
2952 ELSIF l_deduction_rec.applied_action_type = 'U' THEN
2953 -----------------
2954 -- 4.2. Unpply --
2955 -----------------
2956 Perform_Subsequent_Unapply(
2957 p_api_version => l_api_version
2958 ,p_init_msg_list => FND_API.g_false
2959 ,p_validation_level => FND_API.g_valid_level_full
2960 ,x_return_status => l_return_status
2961 ,x_msg_count => x_msg_count
2962 ,x_msg_data => x_msg_data
2963 ,p_deduction => l_deduction_rec
2964 );
2965 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2966 RAISE FND_API.G_EXC_ERROR;
2967 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2969 END IF;
2970 END IF;
2971
2972 ---------------------------------------
2973 -- 5. Assign value to OUT parameters --
2974 ---------------------------------------
2975 OPEN csr_return_claim_info(l_deduction_rec.claim_id);
2976 FETCH csr_return_claim_info INTO x_claim_id
2977 , x_claim_number
2978 , x_object_version_number
2979 , x_claim_reason_code_id
2980 , x_claim_reason_name;
2981 CLOSE csr_return_claim_info;
2982
2983 Write_Log(l_full_name, 'claim_id = '||x_claim_id);
2984 Write_Log(l_full_name, 'claim_number = '||x_claim_number);
2985 Write_Log(l_full_name, 'reason_code_id = '||x_claim_reason_code_id);
2986 Write_Log(l_full_name, 'claim_reason_name = '||x_claim_reason_name);
2987
2988
2989 Write_Log(l_full_name, 'end');
2990
2991 -- Standard check for p_commit
2992 IF FND_API.to_Boolean(p_commit) THEN
2993 COMMIT WORK;
2994 END IF;
2995
2996 -- Standard call to get message count and if count is 1, get message info.
2997 FND_MSG_PUB.Count_And_Get(
2998 p_count => x_msg_count,
2999 p_data => x_msg_data
3000 );
3001
3002 EXCEPTION
3003 WHEN FND_API.G_EXC_ERROR THEN
3004 ROLLBACK TO UPDATE_CLAIM_GRP;
3005 x_return_status := FND_API.G_RET_STS_ERROR;
3006 FND_MSG_PUB.Count_And_Get (
3007 p_encoded => FND_API.G_FALSE,
3008 p_count => x_msg_count,
3009 p_data => x_msg_data
3010 );
3011
3012 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3013 ROLLBACK TO UPDATE_CLAIM_GRP;
3014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3015 FND_MSG_PUB.Count_And_Get (
3016 p_encoded => FND_API.G_FALSE,
3017 p_count => x_msg_count,
3018 p_data => x_msg_data
3019 );
3020
3021 WHEN OTHERS THEN
3022 ROLLBACK TO UPDATE_claim_GRP;
3023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3024 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3025 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
3026 FND_MSG_PUB.add;
3027 END IF;
3028 FND_MSG_PUB.Count_And_Get (
3029 p_encoded => FND_API.G_FALSE,
3030 p_count => x_msg_count,
3031 p_data => x_msg_data
3032 );
3033
3034 END Update_Deduction;
3035
3036
3037 ---------------------------------------------------------------------
3038 -- PROCEDURE: Check_Cancell_Deduction
3039 --
3040 -- PURPOSE: This function checks whether a claims can be cancelled or not.
3041 --
3042 -- PARAMETERS:
3043 -- p_claim_id IN NUMBER Required
3044 --
3045 -- Note:
3049 ) RETURN BOOLEAN
3046 ---------------------------------------------------------------------
3047 FUNCTION Check_Cancell_Deduction(
3048 p_claim_id IN NUMBER
3050 IS
3051 l_return BOOLEAN := FALSE;
3052 l_status_code VARCHAR2(30);
3053 l_amount_remaining NUMBER;
3054 l_open_flag VARCHAR2(1) := 'T';
3055 l_claim_mode VARCHAR2(1) := 'A';
3056 l_root_claim_id NUMBER;
3057 l_sql_stmt VARCHAR2(1000);
3058 idx NUMBER;
3059 TYPE ClaimCurTyp IS REF CURSOR;
3060 split_claims_csr ClaimCurTyp;
3061
3062 BEGIN
3063
3064 l_sql_stmt := 'SELECT root_claim_id, status_code '||
3065 'FROM ozf_claims '||
3066 'WHERE claim_id = :1 ';
3067
3068 EXECUTE IMMEDIATE l_sql_stmt
3069 INTO l_root_claim_id
3070 , l_status_code
3071 USING p_claim_id;
3072
3073 -- for manual solution (when splits have individual applications)
3074 IF l_root_claim_id <> p_claim_id THEN
3075 l_claim_mode := 'M';
3076 IF l_status_code <> 'OPEN' THEN
3077 l_open_flag := 'F';
3078 END IF;
3079 END IF;
3080
3081 -- for automated solution
3082 IF l_claim_mode = 'A' THEN
3083
3084 l_sql_stmt := 'SELECT status_code, amount_remaining '||
3085 'FROM ozf_claims '||
3086 'WHERE root_claim_id = :1 ';
3087
3088 OPEN split_claims_csr FOR l_sql_stmt USING p_claim_id;
3089 LOOP
3090 FETCH split_claims_csr INTO l_status_code
3091 , l_amount_remaining;
3092 EXIT WHEN split_claims_csr%NOTFOUND OR split_claims_csr%NOTFOUND IS NULL;
3093
3094 IF l_status_code NOT IN ('OPEN', 'COMPETE', 'REJECTED', 'CLOSED') THEN
3095 IF l_status_code = 'CANCELLED' THEN
3096 IF l_amount_remaining <> 0 THEN
3097 l_open_flag := 'F';
3098 END IF;
3099 ELSE
3100 l_open_flag := 'F';
3101 END IF;
3102 END IF;
3103 idx := idx + 1;
3104 END LOOP;
3105 CLOSE split_claims_csr;
3106 END IF;
3107
3108 IF l_open_flag = 'T' THEN
3109 l_return := TRUE;
3110 ELSIF l_open_flag = 'F' THEN
3111 l_return := FALSE;
3112 END IF;
3113
3114 RETURN l_return;
3115
3116 END Check_Cancell_Deduction ;
3117
3118
3119 ---------------------------------------------------------------------
3120 -- PROCEDURE: Check_Cancell_Deduction
3121 --
3122 -- PURPOSE: This function checks whether a claims can be cancelled or not.
3123 --
3124 -- PARAMETERS:
3125 -- p_customer_trx_id IN NUMBER
3126 -- p_receipt_id IN NUMBER Required
3127 --
3128 -- Note: This function checks whether a claim exists in TM in OPEN status.
3129 ---------------------------------------------------------------------
3130 FUNCTION Check_Open_Claims(
3131 P_Customer_Trx_Id NUMBER,
3132 P_Receipt_Id NUMBER
3133 ) RETURN BOOLEAN
3134 IS
3135 l_open_claims_count NUMBER := 0;
3136 l_sql_stmt VARCHAR2(1000);
3137
3138 BEGIN
3139 IF p_customer_trx_id IS NOT NULL THEN
3140 l_sql_stmt := 'SELECT COUNT(claim_id) '||
3141 'FROM ozf_claims '||
3142 'WHERE source_object_id = :1 '||
3143 'AND status_code <> ''CLOSED'' ';
3144
3145 EXECUTE IMMEDIATE l_sql_stmt
3146 INTO l_open_claims_count
3147 USING p_customer_trx_id;
3148
3149 ELSIF p_customer_trx_id IS NULL AND p_receipt_id IS NOT NULL THEN
3150 l_sql_stmt := 'SELECT COUNT(claim_id) '||
3151 'FROM ozf_claims '||
3152 'WHERE receipt_id = :1 '||
3153 'AND status_code <> ''CLOSED'' ';
3154
3155 EXECUTE IMMEDIATE l_sql_stmt
3156 INTO l_open_claims_count
3157 USING p_receipt_id;
3158
3159 END IF;
3160
3161 IF l_open_claims_count >= 1 THEN
3162 RETURN TRUE;
3163 ELSE
3164 RETURN FALSE;
3165 END IF;
3166
3167 END Check_Open_Claims;
3168
3169
3170 ---------------------------------------------------------------------
3171 -- PROCEDURE: Get_Claim_Additional_Info
3172 --
3173 -- PURPOSE: The procedure will be used by AR to get additional of info.
3174 -- for invoice related deduction.
3175 --
3176 -- PARAMETERS:
3177 -- IN:
3178 -- p_customer_trx_id IN NUMBER
3179 --
3180 -- OUT:
3181 -- x_application_ref_num OUT VARCHAR2
3182 -- x_secondary_appl_ref_id OUT NUMBER
3183 -- x_customer_reference OUT VARCHAR2
3184 -- x_customer_reason OUT VARCHAR2
3185 --
3186 -- Note:
3187 ---------------------------------------------------------------------
3188 PROCEDURE Get_Claim_Additional_Info(
3189 p_customer_trx_id IN NUMBER,
3190 x_application_ref_num OUT NOCOPY VARCHAR2,
3191 x_secondary_appl_ref_id OUT NOCOPY NUMBER,
3192 x_customer_reference OUT NOCOPY VARCHAR2
3193 )
3194 IS
3195
3196 CURSOR csr_get_claim_dtls (cv_customer_trx_id IN NUMBER) IS
3197 SELECT claim_id, claim_number, customer_ref_number FROM ozf_claims
3198 WHERE source_object_id = cv_customer_trx_id
3199 AND claim_id = root_claim_id
3200 AND root_claim_id IS NOT NULL
3201 AND cust_account_id = (SELECT bill_to_customer_id from ra_customer_trx_all
3202 WHERE customer_trx_id = cv_customer_trx_id);
3203
3204 BEGIN
3205 /*
3206 l_sql_stmt := 'SELECT claim_id, claim_number, customer_ref_number '||
3207 'FROM ozf_claims '||
3208 'WHERE source_object_id = :1 '||
3209 'AND claim_id = root_claim_id ';
3210
3211 EXECUTE IMMEDIATE l_sql_stmt
3212 INTO x_application_ref_num
3213 , x_secondary_appl_ref_id
3214 , x_customer_reference
3215 USING p_customer_trx_id;
3216 */
3217 OPEN csr_get_claim_dtls(p_customer_trx_id);
3218 FETCH csr_get_claim_dtls INTO x_application_ref_num
3219 , x_secondary_appl_ref_id
3220 , x_customer_reference;
3221 IF csr_get_claim_dtls%NOTFOUND THEN
3222 x_application_ref_num := NULL;
3223 x_secondary_appl_ref_id := NULL;
3224 x_customer_reference := NULL;
3225 END IF;
3226 CLOSE csr_get_claim_dtls;
3227
3228 EXCEPTION
3229 WHEN NO_DATA_FOUND THEN
3230 IF csr_get_claim_dtls%ISOPEN THEN
3231 CLOSE csr_get_claim_dtls;
3232 END IF;
3233 x_application_ref_num := NULL;
3234 x_secondary_appl_ref_id := NULL;
3235 x_customer_reference := NULL;
3236
3237 WHEN OTHERS THEN
3238 IF csr_get_claim_dtls%ISOPEN THEN
3239 CLOSE csr_get_claim_dtls;
3240 END IF;
3241 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3242 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3243 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3244 FND_MSG_PUB.Add;
3245 END IF;
3246 x_application_ref_num := NULL;
3247 x_secondary_appl_ref_id := NULL;
3248 x_customer_reference := NULL;
3249
3250 END Get_Claim_Additional_Info;
3251
3252 END OZF_CLAIM_GRP;