[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_GRP
Source
1 PACKAGE BODY OZF_CLAIM_GRP AS
2 /* $Header: ozfgclab.pls 120.20 2011/12/02 15:42:31 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) := 'update_parent_amounts';
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
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
375 --
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;
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;
497 l_qualifier OZF_ASSIGNMENT_QUALIFIER_PUB.qualifier_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;
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;
595 l_qual_deduction_rec.deduction_attribute6 := l_deduction_rec.deduction_attribute6;
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
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;
695 END IF;
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;
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
781 -------------------------------------------------------
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
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
903 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CRE_DEDU_ERR');
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 '||
1037 'From '||ref_split_claim_csr.AMOUNT||
1038 ' To '||l_new_deduction_amount || '.';
1039
1040 IF ref_split_claim_csr.status_code = 'OPEN' THEN
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 --Bigfix : 9715132
1121 END IF; -- status <> CLOSED
1122 END LOOP;
1123
1124 --//Bugfix : 7526516
1125 IF l_root_amount > 0 THEN
1126 -- This means receipt was unapplied and applied with lesser amount.
1127 -- Eg $10 was applied to $100 receipt. This was then changed to $5.
1128 -- Add the excess to the last claim in the table
1129 IF l_is_overpayment THEN
1130 l_root_amount := l_root_amount * -1;
1131 END IF;
1132 l_applicable_claims_tbl(l_record_count).amount := l_applicable_claims_tbl(l_record_count).amount + l_root_amount ;
1133 l_applicable_claims_tbl(l_record_count).amount_remaining := l_applicable_claims_tbl(l_record_count).amount_remaining + l_root_amount ;
1134
1135 -- [Begin of Debug Message]
1136 Write_Log(l_full_name, '---------------------');
1137 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').claim_id = '||l_applicable_claims_tbl(l_record_count).claim_id);
1138 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount = '||l_applicable_claims_tbl(l_record_count).amount);
1139 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_adjusted = '||l_applicable_claims_tbl(l_record_count).amount_adjusted);
1140 Write_Log(l_full_name, 'x_applicable_claims_tbl('||l_record_count||').amount_remaining = '||l_applicable_claims_tbl(l_record_count).amount_remaining);
1141
1142 END IF;
1143
1144
1145
1146 --Assign to OUT parameter
1147 x_applicable_claims_tbl := l_applicable_claims_tbl;
1148 x_notes_tbl := l_notes_tbl;
1149
1150 Write_Log(l_full_name, 'end');
1151
1152 EXCEPTION
1153 WHEN FND_API.G_EXC_ERROR THEN
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155 WHEN OTHERS THEN
1156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1157 FND_MESSAGE.set_name('OZF', 'OZF_API_DEBUG_MESSAGE');
1158 FND_MESSAGE.set_token('TEXT', l_full_name||'An error happened in '||l_full_name);
1159 FND_MSG_PUB.add;
1160
1161 END Check_Update_Allowed;
1162
1163
1164 -- -------------------------------------------------------------------------------------------
1165 -- PROCEDURE: Perform_Subsequent_Apply
1166 --
1167 -- PURPOSE :
1168 -- This procedure perform Subsequent Application.
1169 -- It calls the Update_claim proceudre in the private package.
1170 --
1171 -- PARAMETERS:
1172 -- IN:
1173 -- p_api_version_number IN NUMBER Required
1174 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1175 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1176 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1177 -- P_deduction IN DEDUCTION_REC_TYPE Required
1178 --
1179 -- OUT:
1180 -- x_return_status OUT VARCHAR2
1181 -- x_msg_count OUT NUMBER
1182 -- x_msg_data OUT VARCHAR2
1183 -- x_object_version_number OUT NUMBER
1184 --
1185 -- Note:
1186 --
1187 ----------------------------------------------------------------------------------------------
1188 PROCEDURE Perform_Subsequent_Apply(
1189 p_api_version IN NUMBER,
1190 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1191 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1192
1193 x_return_status OUT NOCOPY VARCHAR2,
1194 x_msg_count OUT NOCOPY NUMBER,
1195 x_msg_data OUT NOCOPY VARCHAR2,
1196
1197 p_deduction IN DEDUCTION_REC_TYPE
1198 )
1199 IS
1200 l_api_version CONSTANT NUMBER := 1.0;
1201 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Subsequent_Apply';
1202 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1203 l_return_status VARCHAR2(1);
1204
1205 l_object_version_number NUMBER;
1206 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
1207 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
1208 l_applicable_claims_tbl DEDUCTION_TBL_TYPE;
1209 l_notes_tbl CLAIM_NOTES_TBL_TYPE;
1210 l_claim_obj_ver_num NUMBER;
1211 l_claim_count NUMBER := 0;
1212 l_claim_amount NUMBER := 0;
1213 l_receipt_number VARCHAR2(30);
1214 l_split_flag VARCHAR2(3);
1215 l_notes VARCHAR2(2000);
1216 l_status_code VARCHAR2(30);
1217 l_new_status_code VARCHAR2(30);
1218 l_update_allowed_flag VARCHAR2(1);
1219 l_x_note_id NUMBER;
1220 l_claim_class VARCHAR2(30);
1221 l_amount_remaining NUMBER:= 0;
1222 l_new_amount_adjusted NUMBER:= 0;
1223 l_total_amount_applied NUMBER:= 0;
1224 l_source_object_id NUMBER;
1225
1226 -- get Count for given claim_id
1227 CURSOR get_claim_count_csr (p_claim_id in number) IS
1228 SELECT count(claim_id)
1229 FROM ozf_claims
1230 WHERE root_claim_id = p_claim_id;
1231
1232 -- get existing claim details.
1233 CURSOR get_claim_detail_csr (p_claim_id in number) IS
1234 SELECT status_code
1235 , amount
1236 , receipt_number
1237 , claim_class
1238 , source_object_id
1239 FROM ozf_claims
1240 WHERE claim_id = p_claim_id;
1241
1242 -- get existing claim details.
1243 CURSOR split_claim_csr(p_claim_id in number) IS
1244 SELECT root_claim_id
1245 , claim_id
1246 , object_version_number
1247 , claim_number
1248 , amount,amount_adjusted
1249 , amount_remaining
1250 , amount_settled
1251 , receipt_number
1252 , status_code
1253 FROM ozf_claims
1254 WHERE root_claim_id = p_claim_id
1255 AND status_code <> 'CLOSED'
1256 ORDER BY claim_id;
1257
1258 -- get all the parent claims
1259 CURSOR parent_claim_csr(p_claim_id in number) IS
1260 SELECT root_claim_id
1261 , claim_id
1262 , object_version_number
1263 , claim_number
1264 , amount
1265 , amount_adjusted
1266 , amount_remaining
1267 , amount_settled
1268 , status_code
1269 , receipt_number
1270 , claim_class
1271 , split_from_claim_id
1272 FROM ozf_claims
1273 WHERE split_from_claim_id IS NULL
1274 AND root_claim_id = p_claim_id
1275 ORDER BY claim_id desc;
1276
1277 CURSOR child_claim_csr(p_claim_id in number) IS
1278 SELECT SUM(amount)
1279 FROM ozf_claims
1280 WHERE split_from_claim_id = p_claim_id;
1281
1282 l_change_in_amount NUMBER;
1283 l_tot_child_amt NUMBER;
1284
1285 BEGIN
1286 --------------------- initialize -----------------------
1287 Write_Log(l_full_name, 'start');
1288
1289 x_return_status := FND_API.G_RET_STS_SUCCESS;
1290
1291 -- Check if the claim has any splits.
1292 OPEN get_claim_count_csr(l_deduction_rec.claim_id);
1293 FETCH get_claim_count_csr INTO l_claim_count;
1294 CLOSE get_claim_count_csr;
1295
1296 IF l_claim_count = 1 THEN
1297 l_split_flag := 'NO';
1298 ELSIF l_claim_count > 1 THEN
1299 l_split_flag := 'YES';
1300 ELSE
1301 l_split_flag := NULL;
1302 END IF;
1303
1304
1305
1306 --Deal with amount sign in case of OVERPAYMENTS
1307 OPEN get_claim_detail_csr(l_deduction_rec.claim_id);
1308 FETCH get_claim_detail_csr INTO l_status_code
1309 , l_claim_amount
1310 , l_receipt_number
1311 , l_claim_class
1312 , l_source_object_id;
1313 CLOSE get_claim_detail_csr;
1314
1315 IF l_claim_class = 'OVERPAYMENT' THEN
1316 IF l_deduction_rec.amount > 0 THEN
1317 l_deduction_rec.amount := l_deduction_rec.amount * -1;
1318 l_deduction_rec.amount_applied := l_deduction_rec.amount_applied * -1;
1319 END IF;
1320 ELSIF l_claim_class = 'DEDUCTION' AND
1321 (l_source_object_id IS NULL OR l_source_object_id = FND_API.g_miss_num) THEN
1322 IF l_deduction_rec.amount < 0 THEN
1323 l_deduction_rec.amount := l_deduction_rec.amount * -1;
1324 END IF;
1325 END IF;
1326
1327
1328 Write_Log(l_full_name, 'l_deduction_rec.amount = '||l_deduction_rec.amount);
1329 Write_Log(l_full_name, 'l_deduction_rec.amount_applied = '||l_deduction_rec.amount_applied);
1330 Write_Log(l_full_name, 'Split ? '||l_split_flag);
1331
1332
1333 -- -----------------
1334 -- No Split Scenario
1335 -- -----------------
1336 IF l_split_flag = 'NO' THEN
1337
1338 IF l_status_code <> 'CLOSED' THEN
1339 IF l_status_code = 'PENDING_CLOSE' THEN
1340 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1341 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
1342 FND_MSG_PUB.add;
1343 END IF;
1344 RAISE FND_API.G_EXC_ERROR;
1345 END IF;
1346
1347 -- ---------------------
1348 -- Fully Apply
1349 -- ---------------------
1350 IF l_deduction_rec.amount = 0 THEN
1351 --Build Notes. not required
1352 -- l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1353 -- Build Notes(29)
1354 --l_notes := l_notes||'New Balance Amount from AR is Zero, Current Status of cliam was changed From '||
1355 -- l_status_code||' To CANCELLED';
1356
1357 --bugfix 4869928
1358 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_AR_BAL_ZERO');
1359 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE',l_status_code,'CANCELLED' );
1360
1361 l_new_status_code := 'CANCELLED';
1362
1363 Write_Log(l_full_name, 'Full Apply - Cancelling Claim');
1364
1365 -- ---------------------
1366 -- Partial Apply
1367 -- ---------------------
1368 ELSE
1369 -- Build Notes(30)
1370 IF l_status_code = 'OPEN' THEN
1371 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1372 --l_notes := l_notes||' Claim Remaining Balance is changed '||
1373 -- 'From '||l_claim_amount||
1374 -- ' To '||l_deduction_rec.AMOUNT||
1375 -- ' due to Application of amount '||l_deduction_rec.AMOUNT_APPLIED||
1376 -- ' and Status is OPEN, remains the Same';
1377
1378 --bugfix 4869928
1379 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_APPLY_CHANGE',
1380 TO_CHAR(l_claim_amount),
1381 TO_CHAR(l_deduction_rec.AMOUNT),
1382 TO_CHAR(l_deduction_rec.AMOUNT_APPLIED),
1383 TO_CHAR(l_deduction_rec.amount_applied),
1384 TO_CHAR(l_deduction_rec.applied_receipt_number)
1385 );
1386 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_STATUS_SAME', 'OPEN');
1387
1388
1389
1390
1391 ELSE
1392 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1393 -- Build Notes(31)
1394 --l_notes := l_notes||' Claim Remaining Balance is changed '||
1395 -- 'From '||l_claim_amount||
1396 -- ' To '||l_deduction_rec.AMOUNT||
1397 -- ' due to Application of amount '||l_deduction_rec.AMOUNT_APPLIED||
1398 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')'||
1399 -- ' and Status is Changed '||
1400 -- 'From '||l_status_code||' To OPEN';
1401 --bugfix 4869928
1402 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_APPLY_CHANGE',
1403 TO_CHAR(l_claim_amount),
1404 TO_CHAR(l_deduction_rec.AMOUNT),
1405 TO_CHAR(l_deduction_rec.AMOUNT_APPLIED),
1406 TO_CHAR(l_deduction_rec.amount_applied),
1407 TO_CHAR(l_deduction_rec.applied_receipt_number)
1408 );
1409 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE', l_status_code, 'OPEN');
1410
1411
1412 END IF;
1413 l_new_status_code := 'OPEN';
1414 Write_Log(l_full_name, 'Partial Apply - Adjusting Amount');
1415
1416 END IF;
1417
1418 --Build Claim Rec.
1419 l_pvt_claim_rec.claim_id := l_deduction_rec.claim_id;
1420 l_pvt_claim_rec.object_version_number := l_deduction_rec.object_version_number;
1421 l_pvt_claim_rec.amount := l_deduction_rec.amount;
1422 l_pvt_claim_rec.currency_code := l_deduction_rec.currency_code;
1423 l_pvt_claim_rec.exchange_rate_type := l_deduction_rec.exchange_rate_type;
1424 l_pvt_claim_rec.exchange_rate_date := l_deduction_rec.exchange_rate_date;
1425 l_pvt_claim_rec.exchange_rate := l_deduction_rec.exchange_rate;
1426 l_pvt_claim_rec.status_code := l_new_status_code;
1427 --//Bug fix 10194954
1428 l_pvt_claim_rec.reason_code_id := l_deduction_rec.reason_code_id;
1429
1430 IF l_deduction_rec.source_object_id IS NOT NULL AND
1431 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
1432 --Transaction 8/22/2005Releated deduction
1433 l_pvt_claim_rec.receipt_id := l_deduction_rec.receipt_id;
1434 l_pvt_claim_rec.receipt_number := l_deduction_rec.receipt_number;
1435
1436 -- Build Notes (32)
1437 --l_notes := l_notes||' and Receipt Reference is Changed '||
1438 -- 'From '||l_receipt_number||' To '||l_deduction_rec.RECEIPT_NUMBER||'.]';
1439
1440 -- bugfix 4869928
1441 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(l_receipt_number), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
1442
1443 ELSE
1444 -- Claim Investigation
1445 -- Build Notes(33)
1446 --l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
1447
1448 -- bugfix 4869928
1449 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
1450
1451 END IF;
1452
1453 --Assign Applied Details
1454 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
1455 l_pvt_claim_rec.amount_applied := l_deduction_rec.amount_applied;
1456 l_pvt_claim_rec.applied_receipt_id := NVL(l_deduction_rec.applied_receipt_id,l_deduction_rec.receipt_id);
1457 l_pvt_claim_rec.applied_receipt_number := NVL(l_deduction_rec.applied_receipt_number,l_deduction_rec.receipt_number);
1458
1459 -- [Begin of Debug Message]
1460 Write_Log(l_full_name, 'applicable claim.amount = '||l_pvt_claim_rec.amount);
1461 Write_Log(l_full_name, 'applicable claim.amount_applied = '||l_pvt_claim_rec.amount_applied);
1462 Write_Log(l_full_name, 'applicable claim.status_code = '||l_pvt_claim_rec.status_code);
1463 Write_Log(l_full_name, 'applicable claim.receipt_id = '||l_pvt_claim_rec.receipt_id);
1464 Write_Log(l_full_name, 'applicable claim.receipt_number = '||l_pvt_claim_rec.receipt_number);
1465 Write_Log(l_full_name, 'applicable claim.applied_receipt_id = '||l_pvt_claim_rec.applied_receipt_id);
1466 Write_Log(l_full_name, 'applicable claim.applied_receipt_number = '||l_pvt_claim_rec.applied_receipt_number);
1467 -- [End of Debug Message]
1468
1469 -- Call Update_Claim to reflect the changes.
1470 OZF_claim_PVT.Update_claim(
1471 p_api_version => 1.0,
1472 p_init_msg_list => FND_API.G_FALSE,
1473 p_commit => FND_API.G_FALSE,
1474 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1475 x_return_status => l_return_status,
1476 x_msg_count => x_msg_count,
1477 x_msg_data => x_msg_data,
1478 p_claim => l_pvt_claim_rec,
1479 p_event => g_subsequent_apply_event, --g_update_event
1480 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
1481 x_object_version_number => l_object_version_number
1482 );
1483 IF l_return_status = FND_API.G_RET_STS_ERROR then
1484 RAISE FND_API.G_EXC_ERROR;
1485 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1487 END IF;
1488
1489 ELSE
1490 --Build Notes. (34)
1491 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
1492 --l_notes := l_notes||'Status of the claim is CLOSED, NO Subsequent Receipt application'||
1493 -- ' for receipt number '||l_deduction_rec.RECEIPT_NUMBER||
1494 -- ' will be performed on this Claim.]';
1495
1496 -- bugfix 4869928
1497 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_CLAIM_CLOSED');
1498 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_NO_SUBS_APPLY', TO_CHAR(l_deduction_rec.RECEIPT_NUMBER) );
1499
1500
1501 END IF;
1502
1503 --Call Create Notes API.
1504 Write_log(l_full_name,l_notes);
1505 JTF_NOTES_PUB.create_note(
1506 p_api_version => 1.0
1507 ,x_return_status => l_return_status
1508 ,x_msg_count => x_msg_count
1509 ,x_msg_data => x_msg_data
1510 ,p_source_object_id => l_deduction_rec.claim_id
1511 ,p_source_object_code => 'AMS_CLAM'
1512 ,p_notes => l_notes
1513 ,p_note_status => NULL
1514 ,p_entered_by => FND_GLOBAL.user_id
1515 ,p_entered_date => SYSDATE
1516 ,p_last_updated_by => FND_GLOBAL.user_id
1517 ,x_jtf_note_id => l_x_note_id
1518 ,p_note_type => 'AMS_JUSTIFICATION'
1519 ,p_last_update_date => SYSDATE
1520 ,p_creation_date => SYSDATE
1521 );
1522
1523
1524 -- --------------
1525 -- Split Scenario
1526 -- --------------
1527 ELSIF l_split_flag = 'YES' THEN
1528 --//Added for Bugfix: 13069412
1529 IF l_status_code <> 'CLOSED' THEN
1530
1531 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.claim_id) LOOP
1532 IF ref_split_claim_csr.status_code = 'PENDING_CLOSE' THEN
1533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1534 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
1535 FND_MSG_PUB.add;
1536 END IF;
1537 RAISE FND_API.G_EXC_ERROR;
1538 END IF;
1539 END LOOP;
1540
1541 -- ---------------------
1542 -- Fully Apply
1543 -- ---------------------
1544 IF l_deduction_rec.amount = 0 THEN
1545 -- Update Status to CANCELLED for all Claims including root claim and set amount to zero.
1546 -- Select all claims for the root_claim except claim with CLOSED status.
1547 l_new_status_code := 'CANCELLED';
1548
1549 Write_Log(l_full_name, 'Full Apply - Cancelling All Claims');
1550 -- Process Cancellation of claims and update remaining balance to Zero
1551 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.CLAIM_ID) LOOP
1552 l_amount_remaining := (ref_split_claim_csr.AMOUNT_REMAINING + ref_split_claim_csr.AMOUNT_SETTLED);
1553 -- Build Notes (35)
1554 --l_notes := '[Claim:'||ref_split_claim_csr.CLAIM_NUMBER||' Remark:';
1555 --l_notes := l_notes||'Claim Remaining Balance is changed '||
1556 -- 'From '||l_amount_remaining||' To '||l_deduction_rec.AMOUNT||
1557 -- ' due to Application of amount '||l_amount_remaining||
1558 -- ' (Out of AR applied amount '||l_deduction_rec.amount_applied||
1559 -- ' Receipt Number:'||l_deduction_rec.applied_receipt_number||')'||
1560 -- ' and Status is Changed '||'From '||ref_split_claim_csr.STATUS_CODE||
1561 -- ' To CANCELLED';
1562
1563 --bugfix 4869928
1564 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_APPLY_CHANGE'
1565 , TO_CHAR(l_amount_remaining),
1566 TO_CHAR(l_deduction_rec.AMOUNT),
1567 TO_CHAR(l_amount_remaining),
1568 TO_CHAR(l_deduction_rec.amount_applied),
1569 TO_CHAR(l_deduction_rec.applied_receipt_number)
1570 );
1571 l_notes := l_notes || Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE', ref_split_claim_csr.STATUS_CODE, 'CANCELLED');
1572
1573
1574 --build claim rec.
1575 l_pvt_claim_rec.claim_id := ref_split_claim_csr.claim_id;
1576 l_pvt_claim_rec.object_version_number := ref_split_claim_csr.object_version_number;
1577 l_pvt_claim_rec.status_code := l_new_status_code;
1578 l_pvt_claim_rec.amount := 0;
1579 l_pvt_claim_rec.amount_adjusted := 0;
1580 l_pvt_claim_rec.amount_remaining := 0;
1581 l_pvt_claim_rec.amount_settled := 0;
1582 --l_pvt_claim_rec.amount := l_deduction_rec.amount;
1583 l_pvt_claim_rec.currency_code := l_deduction_rec.currency_code;
1584 l_pvt_claim_rec.exchange_rate_type := l_deduction_rec.exchange_rate_type;
1585 l_pvt_claim_rec.exchange_rate_date := l_deduction_rec.exchange_rate_date;
1586 l_pvt_claim_rec.exchange_rate := l_deduction_rec.exchange_rate;
1587 --//Bug fix 10194954
1588 l_pvt_claim_rec.reason_code_id := l_deduction_rec.reason_code_id;
1589
1590 IF l_deduction_rec.source_object_id IS NOT NULL AND
1591 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
1592 --Transaction Releated deduction
1593 l_pvt_claim_rec.receipt_id := l_deduction_rec.receipt_id;
1594 l_pvt_claim_rec.receipt_number := l_deduction_rec.receipt_number;
1595
1596 -- Build Notes (36)
1597 --l_notes := l_notes||' and Receipt Reference is Changed '||
1598 -- 'From '||l_receipt_number||' To '||l_deduction_rec.RECEIPT_NUMBER||'.]';
1599
1600 -- bugfix 4869928
1601 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(l_receipt_number), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
1602
1603 ELSE
1604 -- Claim Investigation
1605 -- Build Notes(37)
1606 --l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
1607
1608 -- bugfix 4869928
1609 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
1610
1611 END IF;
1612
1613 --Assign Applied Details
1614 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
1615 l_pvt_claim_rec.amount_applied := l_amount_remaining;
1616 l_pvt_claim_rec.applied_receipt_id := NVL(l_deduction_rec.applied_receipt_id,l_deduction_rec.receipt_id);
1617 l_pvt_claim_rec.applied_receipt_number := NVL(l_deduction_rec.applied_receipt_number,l_deduction_rec.receipt_number);
1618
1619 -- [Begin of Debug Message]
1620 Write_Log(l_full_name, 'applicable claim.claim_id = '||l_pvt_claim_rec.claim_id);
1621 Write_Log(l_full_name, 'applicable claim.amount = '||l_pvt_claim_rec.amount);
1622 Write_Log(l_full_name, 'applicable claim.amount_adjusted = '||l_pvt_claim_rec.amount_adjusted);
1623 Write_Log(l_full_name, 'applicable claim.amount_remaining = '||l_pvt_claim_rec.amount_remaining);
1624 Write_Log(l_full_name, 'applicable claim.amount_settled = '||l_pvt_claim_rec.amount_settled);
1625 Write_Log(l_full_name, 'applicable claim.amount_applied = '||l_pvt_claim_rec.amount_applied);
1626 Write_Log(l_full_name, 'applicable claim.status_code = '||l_pvt_claim_rec.status_code);
1627 Write_Log(l_full_name, 'applicable claim.receipt_id = '||l_pvt_claim_rec.receipt_id);
1628 Write_Log(l_full_name, 'applicable claim.receipt_number = '||l_pvt_claim_rec.receipt_number);
1629 Write_Log(l_full_name, 'applicable claim.applied_receipt_id = '||l_pvt_claim_rec.applied_receipt_id);
1630 Write_Log(l_full_name, 'applicable claim.applied_receipt_number= '||l_pvt_claim_rec.applied_receipt_number);
1631 -- [End of Debug Message]
1632
1633 --Call Update_Claim to reflect the changes.
1634 OZF_claim_PVT.Update_claim(
1635 p_api_version => 1.0,
1636 p_init_msg_list => FND_API.G_FALSE,
1637 p_commit => FND_API.G_FALSE,
1638 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1639 x_return_status => l_return_status,
1640 x_msg_count => x_msg_count,
1641 x_msg_data => x_msg_data,
1642 p_claim => l_pvt_claim_Rec,
1643 p_event => G_SUBSEQUENT_APPLY_EVENT, --G_UPDATE_EVENT
1644 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
1645 x_object_version_number => l_object_version_number
1646 );
1647 IF l_return_status = FND_API.G_RET_STS_ERROR then
1648 RAISE FND_API.G_EXC_ERROR;
1649 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1651 END IF;
1652
1653
1654 --Call Create Notes API.
1655 Write_log(l_full_name,l_notes);
1656 JTF_NOTES_PUB.create_note(
1657 p_api_version => 1.0
1658 ,x_return_status => l_return_status
1659 ,x_msg_count => x_msg_count
1660 ,x_msg_data => x_msg_data
1661 ,p_source_object_id => l_deduction_rec.claim_id
1662 ,p_source_object_code => 'AMS_CLAM'
1663 ,p_notes => l_notes
1664 ,p_note_status => NULL
1665 ,p_entered_by => FND_GLOBAL.user_id
1666 ,p_entered_date => SYSDATE
1667 ,p_last_updated_by => FND_GLOBAL.user_id
1668 ,x_jtf_note_id => l_x_note_id
1669 ,p_note_type => 'AMS_JUSTIFICATION'
1670 ,p_last_update_date => SYSDATE
1671 ,p_creation_date => SYSDATE
1672 );
1673
1674 l_notes := null;
1675
1676 END LOOP;
1677
1678 ELSIF l_deduction_rec.AMOUNT <> 0 THEN
1679
1680 Write_Log(l_full_name, 'Partial Apply - Adjusting Amounts');
1681 Check_Update_Allowed(
1682 p_deduction_rec => l_deduction_rec
1683 ,x_applicable_claims_tbl => l_Applicable_Claims_Tbl
1684 ,x_notes_tbl => l_notes_tbl
1685 ,x_update_allowed_flag => l_update_allowed_flag
1686 ,x_return_status => l_return_status
1687 );
1688 IF l_return_status = FND_API.G_RET_STS_ERROR then
1689 RAISE FND_API.G_EXC_ERROR;
1690 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1692 END IF;
1693
1694 write_log(l_full_name, l_applicable_claims_tbl.count);
1695 FOR i in 1..l_applicable_claims_tbl.count LOOP
1696 write_log(l_full_name, 'i'||i);
1697 --build claim rec.
1698 l_pvt_claim_rec.claim_id := l_applicable_claims_tbl(i).claim_id;
1699 l_pvt_claim_rec.object_version_number := l_applicable_claims_tbl(i).object_version_number;
1700 l_pvt_claim_rec.status_code := l_applicable_claims_tbl(i).status_code;
1701 l_pvt_claim_rec.amount := l_applicable_claims_tbl(i).amount;
1702 l_pvt_claim_rec.amount_adjusted := l_applicable_claims_tbl(i).amount_adjusted;
1703 l_pvt_claim_rec.amount_remaining := l_applicable_claims_tbl(i).amount_remaining;
1704 l_pvt_claim_rec.amount_settled := l_applicable_claims_tbl(i).amount_settled;
1705 l_pvt_claim_rec.amount_applied := l_applicable_claims_tbl(i).amount_applied;
1706 l_pvt_claim_rec.currency_code := l_applicable_claims_tbl(i).currency_code;
1707 l_pvt_claim_rec.exchange_rate_type := l_applicable_claims_tbl(i).exchange_rate_type;
1708 l_pvt_claim_rec.exchange_rate_date := l_applicable_claims_tbl(i).exchange_rate_date;
1709 l_pvt_claim_rec.exchange_rate := l_applicable_claims_tbl(i).exchange_rate;
1710 l_pvt_claim_rec.receipt_id := l_applicable_claims_tbl(i).receipt_id;
1711 l_pvt_claim_rec.receipt_number := l_applicable_claims_tbl(i).receipt_number;
1712 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
1713 l_pvt_claim_rec.applied_receipt_id := l_deduction_rec.applied_receipt_id;
1714 l_pvt_claim_rec.applied_receipt_number := l_deduction_rec.applied_receipt_number;
1715 --//Bug fix 10194954
1716 l_pvt_claim_rec.reason_code_id := l_deduction_rec.reason_code_id;
1717
1718
1719 -- [Begin of Debug Message]
1720 Write_Log(l_full_name, 'claims('||i||').CLAIM_ID ='||l_pvt_claim_Rec.CLAIM_ID);
1721 Write_Log(l_full_name, 'claims('||i||').STATUS_CODE ='||l_pvt_claim_Rec.STATUS_CODE);
1722 Write_Log(l_full_name, 'claims('||i||').AMOUNT ='||l_pvt_claim_Rec.AMOUNT);
1723 Write_Log(l_full_name, 'claims('||i||').AMOUNT_ADJUSTED ='||l_pvt_claim_Rec.AMOUNT_ADJUSTED);
1724 Write_Log(l_full_name, 'claims('||i||').AMOUNT_REMAINING ='||l_pvt_claim_Rec.AMOUNT_REMAINING);
1725 Write_Log(l_full_name, 'claims('||i||').AMOUNT_SETTLED ='||l_pvt_claim_Rec.AMOUNT_SETTLED);
1726 Write_Log(l_full_name, 'claims('||i||').AMOUNT_APPLIED ='||l_pvt_claim_Rec.AMOUNT_APPLIED);
1727 Write_Log(l_full_name, 'claims('||i||').RECEIPT_ID ='||l_pvt_claim_Rec.RECEIPT_ID);
1728 Write_Log(l_full_name, 'claims('||i||').RECEIPT_NUMBER ='||l_pvt_claim_Rec.RECEIPT_NUMBER);
1729 -- [End of Debug Message]
1730
1731 --Call Update_Claim to reflect the changes.
1732 OZF_claim_PVT.Update_claim(
1733 p_api_version => 1.0,
1734 p_init_msg_list => FND_API.G_FALSE,
1735 p_commit => FND_API.G_FALSE,
1736 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1737 x_return_status => l_return_status,
1738 x_msg_count => x_msg_count,
1739 x_msg_data => x_msg_data,
1740 p_claim => l_pvt_claim_Rec,
1741 p_event => G_SUBSEQUENT_APPLY_EVENT, --G_UPDATE_EVENT
1742 p_mode => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
1743 x_object_version_number => l_object_version_number
1744 );
1745 IF l_return_status = FND_API.G_RET_STS_ERROR then
1746 RAISE FND_API.G_EXC_ERROR;
1747 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1749 END IF;
1750
1751 IF i <= l_notes_tbl.COUNT THEN
1752 --Call Create Notes API.
1753 JTF_NOTES_PUB.create_note(
1754 p_api_version => 1.0
1755 ,x_return_status => l_return_status
1756 ,x_msg_count => x_msg_count
1757 ,x_msg_data => x_msg_data
1758 ,p_source_object_id => l_pvt_claim_rec.claim_id
1759 ,p_source_object_code => 'AMS_CLAM'
1760 ,p_notes => l_notes_tbl(i).claim_notes
1761 ,p_note_status => NULL
1762 ,p_entered_by => FND_GLOBAL.user_id
1763 ,p_entered_date => SYSDATE
1764 ,p_last_updated_by => FND_GLOBAL.user_id
1765 ,x_jtf_note_id => l_x_note_id
1766 ,p_note_type => 'AMS_JUSTIFICATION'
1767 ,p_last_update_date => SYSDATE
1768 ,p_creation_date => SYSDATE
1769 );
1770 END IF;
1771 END LOOP;
1772 END IF; --IF l_deduction_rec.AMOUNT <> 0 THEN
1773
1774 -- Now sync adjustment amounts
1775 update_parent_amounts(
1776 x_return_status => l_return_status
1777 ,x_msg_count => x_msg_count
1778 ,x_msg_data => x_msg_data
1779 ,p_deduction_rec => l_deduction_rec);
1780 IF l_return_status = FND_API.G_RET_STS_ERROR then
1781 RAISE FND_API.G_EXC_ERROR;
1782 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1784 END IF;
1785
1786 ELSE
1787 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_CLAIM_CLOSED');
1788 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_NO_SUBS_APPLY', TO_CHAR(l_deduction_rec.RECEIPT_NUMBER) );
1789 END IF;
1790
1791 END IF; --IF l_split_flag = 'YES' THEN
1792
1793
1794 FND_MSG_PUB.count_and_get(
1795 p_encoded => FND_API.g_false,
1796 p_count => x_msg_count,
1797 p_data => x_msg_data
1798 );
1799
1800 Write_Log(l_full_name, 'end');
1801
1802
1803 EXCEPTION
1804 WHEN FND_API.G_EXC_ERROR THEN
1805 x_return_status := FND_API.G_RET_STS_ERROR;
1806 -- Standard call to get message count and if count=1, get the message
1807 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1808 ,p_count => x_msg_count
1809 ,p_data => x_msg_data
1810 );
1811
1812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1814 -- Standard call to get message count and if count=1, get the message
1815 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1816 ,p_count => x_msg_count
1817 ,p_data => x_msg_data
1818 );
1819
1820 WHEN OTHERS THEN
1821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1822 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1823 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
1824 FND_MSG_PUB.add;
1825 END IF;
1826 -- Standard call to get message count and if count=1, get the message
1827 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1828 ,p_count => x_msg_count
1829 ,p_data => x_msg_data
1830 );
1831
1832 End Perform_Subsequent_Apply;
1833
1834
1835 ----------------------------------------------------------------------------------------------
1836 -- PROCEDURE: Perform_Subsequent_Unpply
1837 --
1838 -- PURPOSE :
1839 -- This procedure perform Subsequent Un-Application.
1840 -- It calls the Update_claim proceudre in the private package.
1841 --
1842 -- PARAMETERS:
1843 -- IN:
1844 -- p_api_version_number IN NUMBER Required
1845 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1846 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1847 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1848 -- P_deduction IN DEDUCTION_REC_TYPE Required
1849 --
1850 -- OUT:
1851 -- x_return_status OUT VARCHAR2
1852 -- x_msg_count OUT NUMBER
1853 -- x_msg_data OUT VARCHAR2
1854 -- x_object_version_number OUT NUMBER
1855 --
1856 -- Note:
1857 --
1858 ----------------------------------------------------------------------------------------------
1859 PROCEDURE Perform_Subsequent_Unapply(
1860 p_api_version IN NUMBER,
1861 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1862 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1863
1864 x_return_status OUT NOCOPY VARCHAR2,
1865 x_msg_count OUT NOCOPY NUMBER,
1866 x_msg_data OUT NOCOPY VARCHAR2,
1867
1868 p_deduction IN DEDUCTION_REC_TYPE
1869 )
1870 IS
1871 l_api_version CONSTANT NUMBER := 1.0;
1872 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Subsequent_Unpply';
1873 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1874 l_return_status VARCHAR2(1);
1875
1876 l_object_version_number NUMBER;
1877 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
1878 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
1879 l_Applicable_Claims_Tbl DEDUCTION_REC_TYPE;
1880 l_child_claim_tbl OZF_SPLIT_CLAIM_PVT.Child_Claim_tbl_type;
1881 l_claim_obj_ver_num NUMBER;
1882 l_claim_count NUMBER := 0;
1883 l_split_flag VARCHAR2(3);
1884 l_notes VARCHAR2(2000);
1885 l_status_code VARCHAR2(30);
1886 l_new_status_code VARCHAR2(30);
1887 l_update_allowed_flag VARCHAR2(1);
1888 l_count_claim_on_receipt NUMBER:=0;
1889 l_new_amount NUMBER:=0;
1890 l_new_amount_adjusted NUMBER:=0;
1891 l_claim_class VARCHAR2(30);
1892 l_deduction_type VARCHAR2(30);
1893 l_reason_code_id NUMBER;
1894 l_receipt_number VARCHAR2(30);
1895 l_claim_amount NUMBER := 0;
1896 l_split_claim_id NUMBER;
1897 l_split_claim_number VARCHAR2(30);
1898 l_x_note_id NUMBER;
1899 l_amount_remaining NUMBER:=0;
1900 l_claim_type_id NUMBER:=FND_API.g_miss_num;
1901 l_source_object_id NUMBER;
1902 l_invoice_amount_due NUMBER:=0; --Added on 21-Apr-2003 (aadhawad)
1903
1904 -- get Count for given claim_id
1905 CURSOR get_claim_count_csr (p_claim_id in number) IS
1906 SELECT count(*)
1907 FROM ozf_claims_all
1908 WHERE root_claim_id = p_claim_id;
1909
1910 -- get existing claim details.
1911 CURSOR get_claim_detail_csr (p_claim_id in number) IS
1912 SELECT status_code,amount,receipt_number,claim_class,
1913 amount_remaining+amount_settled amount_remaining,
1914 source_object_id
1915 FROM ozf_claims_all
1916 WHERE claim_id = p_claim_id;
1917
1918 -- Added for Bug4872736
1919 CURSOR get_split_claim_detail_csr (p_claim_id in number) IS
1920 SELECT claim_class,
1921 sum(amount_remaining+amount_settled) amount_remaining,
1922 source_object_id
1923 FROM ozf_claims_all
1924 WHERE root_claim_id = p_claim_id
1925 GROUP BY claim_class, source_object_id;
1926
1927 -- get split claim details.
1928 CURSOR split_claim_csr(p_claim_id in number) IS
1929 SELECT root_claim_id,
1930 claim_id,
1931 object_version_number,
1932 claim_number,
1933 receipt_number,
1934 status_code,
1935 amount,
1936 amount_adjusted,
1937 amount_remaining,
1938 amount_settled
1939 FROM ozf_claims_all
1940 WHERE root_claim_id = p_claim_id
1941 ORDER BY claim_id;
1942
1943 -- get Claim_Class for claim_id
1944 CURSOR get_claim_class_csr(p_claim_id IN NUMBER) IS
1945 SELECT claim_class,reason_code_id,object_version_number,claim_type_id
1946 FROM ozf_claims_all
1947 WHERE claim_id = p_claim_id;
1948
1949 -- get Newly created split claim_id and claim_number
1950 CURSOR get_split_claim_ids_csr(p_claim_id IN NUMBER) IS
1951 SELECT claim_id,claim_number
1952 FROM ozf_claims_all
1953 WHERE root_claim_id = p_claim_id
1954 AND claim_id = (Select max(claim_id)
1955 from ozf_claims_all
1956 where root_claim_id = p_claim_id)
1957 AND trunc(creation_date) = trunc(sysdate);
1958
1959 -- get Amount Due on Invoice
1960 CURSOR get_invoice_amount_due_csr (p_claim_id in NUMBER) IS
1961 SELECT amount_due_original
1962 FROM ar_payment_schedules a, ozf_claims_all b
1963 WHERE customer_trx_id = b.source_object_id
1964 AND b.claim_id = p_claim_id;
1965
1966 -- [BEGIN OF BUG 3775972 FIXING]
1967 l_cancel_all_claim VARCHAR2(1) := 'N';
1968 -- [END OF BUG 3775972 FIXING]
1969
1970 -- Added for Bug5102282
1971 -- Get the payment_schedule_id
1972 CURSOR csr_applied_ps_id( cv_customer_trx_id IN NUMBER, cv_receipt_id IN NUMBER, cv_claim_id IN NUMBER) IS
1973 SELECT distinct applied_payment_schedule_id
1974 FROM ar_receivable_applications_all
1975 WHERE cash_receipt_id = cv_receipt_id
1976 AND applied_customer_trx_id = cv_customer_trx_id
1977 AND application_ref_type = 'CLAIM'
1978 AND secondary_application_ref_id = cv_claim_id
1979 AND status = 'APP';
1980 l_applied_ps_id NUMBER;
1981
1982 -- Added for Bug5102282
1983 -- Get the count of applications
1984 CURSOR csr_cnt_apply(cv_ps_id IN NUMBER, cv_receipt_id IN NUMBER, cv_claim_id IN NUMBER) IS
1985 SELECT COUNT(*)
1986 FROM ar_receivable_applications_all
1987 WHERE applied_payment_schedule_id = cv_ps_id
1988 AND cash_receipt_id <> cv_receipt_id
1989 AND application_ref_type = 'CLAIM'
1990 AND secondary_application_ref_id = cv_claim_id
1991 AND status = 'APP'
1992 AND display = 'Y';
1993 l_cnt_apply_recs NUMBER := 0;
1994 l_new_deduction_amount Number := 0;
1995
1996 BEGIN
1997 --------------------- initialize -----------------------
1998 Write_Log(l_full_name, 'start');
1999
2000 x_return_status := FND_API.G_RET_STS_SUCCESS;
2001
2002
2003 -- Check if the claim has any splits.
2004 OPEN get_claim_count_csr(l_deduction_rec.claim_id);
2005 FETCH get_claim_count_csr INTO l_claim_count;
2006 CLOSE get_claim_count_csr;
2007
2008 -- Get Details for root claimc
2009 OPEN get_claim_detail_csr(l_deduction_rec.claim_id);
2010 FETCH get_claim_detail_csr INTO l_status_code,l_claim_amount,l_receipt_number,
2011 l_claim_class,l_amount_remaining,l_source_object_id;
2012 CLOSE get_claim_detail_csr;
2013
2014 IF l_claim_count = 1 THEN
2015 l_split_flag := 'NO';
2016 ELSIF l_claim_count > 1 THEN
2017 l_split_flag := 'YES';
2018 ELSE
2019 l_split_flag := NULL;
2020 END IF;
2021
2022 Write_Log(l_full_name, 'l_deduction_rec.amount = '||l_deduction_rec.amount);
2023 Write_Log(l_full_name, 'l_deduction_rec.amount_applied = '||l_deduction_rec.amount_applied);
2024
2025 Write_Log(l_full_name, 'Split ? '||l_split_flag);
2026
2027 -- Handling for Invoice Deductions
2028 IF p_deduction.source_object_id IS NOT NULL THEN
2029
2030 -- Obtain the payment schedule id
2031 OPEN csr_applied_ps_id(p_deduction.source_object_id, p_deduction.receipt_id, p_deduction.claim_id);
2032 FETCH csr_applied_ps_id INTO l_applied_ps_id;
2033 CLOSE csr_applied_ps_id;
2034
2035 -- Check if there are any other applications
2036 OPEN csr_cnt_apply(l_applied_ps_id, p_deduction.receipt_id, p_deduction.claim_id);
2037 FETCH csr_cnt_apply INTO l_cnt_apply_recs;
2038 CLOSE csr_cnt_apply;
2039
2040 END IF;
2041
2042 -- -----------------
2043 -- No Split Scenario
2044 -- -----------------
2045 IF l_split_flag = 'NO' THEN
2046 --Deal with amount sign
2047 IF l_claim_class = 'OVERPAYMENT' THEN
2048 IF l_deduction_rec.AMOUNT > 0 THEN
2049 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2050 l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2051 END IF;
2052 END IF;
2053
2054 --Fixed:Date:12-Mar-2003. Partial application for claim investigation.
2055 IF ( l_claim_class = 'DEDUCTION' and
2056 (l_source_object_id is NULL or l_source_object_id = FND_API.g_miss_num)) THEN
2057 IF l_deduction_rec.AMOUNT < 0 THEN
2058 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2059 --l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2060 END IF;
2061 END IF;
2062
2063 IF l_status_code = 'PENDING_CLOSE' THEN
2064 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2065 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
2066 FND_MSG_PUB.add;
2067 END IF;
2068 RAISE FND_API.G_EXC_ERROR;
2069 END IF;
2070
2071 --- BUG 4157743 FIXING
2072 IF l_status_code <> 'CLOSED' THEN
2073
2074 -- Bug4300996/Bug4777500/Bug5102282:Cancel claim
2075 -- For invoice deductions, cancel if there are not other applications
2076 -- For non invoice deductions and overpayments, cancel if amount = 0
2077 IF l_deduction_rec.AMOUNT = 0 OR l_cnt_apply_recs = 0 THEN
2078
2079 Write_Log(l_full_name, 'Cancel ? Yes' );
2080
2081 l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2082 l_notes := l_notes||'For this Claim all receipts are fully Unapplied from AR '||
2083 ' and Status is changed From '||l_status_code||' To CANCELLED';
2084
2085 l_new_status_code := 'CANCELLED';
2086 l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
2087
2088 ELSE
2089 -- Build Notes(39)
2090 -- l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2091 -- l_notes := l_notes||' This Claim Balance is changed '||
2092 -- 'From '||l_amount_remaining||
2093 -- ' To '||l_deduction_rec.AMOUNT||
2094 -- ' due to Unapplication of amount '||l_deduction_rec.AMOUNT_APPLIED||
2095 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')';
2096
2097 -- bugfix 4869928
2098 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) );
2099
2100 IF l_status_code = 'OPEN' THEN
2101 -- Build Notes(40)
2102 -- l_notes := l_notes||' and Status is '||l_status_code||', remains the Same';
2103
2104 -- bugfix 4869928
2105 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_SAME', l_status_code);
2106 l_new_status_code := l_status_code;
2107
2108
2109 ELSE
2110 -- Build Notes(41)
2111 --l_notes := l_notes|| ' and Status is changed From '||l_status_code||' To OPEN';
2112
2113 -- bugfix 4869928
2114 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE', l_status_code, 'OPEN');
2115
2116
2117 l_new_status_code := 'OPEN';
2118 END IF;
2119
2120 --Check for Transaction Related Claim.
2121 IF l_deduction_rec.SOURCE_OBJECT_ID is NOT NULL AND
2122 l_deduction_rec.SOURCE_OBJECT_ID <> FND_API.G_MISS_NUM THEN
2123 --Transaction Related Claim.
2124 l_pvt_claim_Rec.RECEIPT_ID := l_deduction_rec.RECEIPT_ID;
2125 l_pvt_claim_Rec.RECEIPT_NUMBER := nvl(l_deduction_rec.RECEIPT_NUMBER,l_receipt_number);
2126
2127 --Build Event Description. Receipt is not changed because incoming recept number is NULL.
2128 -- Build Notes(42)
2129 --l_notes := l_notes||' and Receipt Reference is Changed '||
2130 -- 'From '||l_receipt_number||
2131 -- ' To '||l_deduction_rec.RECEIPT_NUMBER||'.]';
2132
2133 -- bugfix 4869928
2134 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_CHANGE', TO_CHAR(l_receipt_number), TO_CHAR(l_deduction_rec.RECEIPT_NUMBER));
2135
2136 ELSE
2137 --Build Event Description
2138 -- Build Notes(43)
2139 --l_notes := l_notes||' and Receipt Reference '||l_receipt_number||' is not Changed.]';
2140
2141 -- bugfix 4869928
2142 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
2143
2144 END IF;
2145 l_pvt_claim_Rec.AMOUNT := l_deduction_rec.AMOUNT;
2146
2147 END IF;
2148
2149 --Build Claim_Rec.
2150 l_pvt_claim_Rec.CLAIM_ID := l_deduction_rec.CLAIM_ID;
2151 l_pvt_claim_Rec.OBJECT_VERSION_NUMBER := l_deduction_rec.OBJECT_VERSION_NUMBER;
2152 l_pvt_claim_Rec.CURRENCY_CODE := l_deduction_rec.CURRENCY_CODE;
2153 l_pvt_claim_Rec.EXCHANGE_RATE_TYPE := l_deduction_rec.EXCHANGE_RATE_TYPE;
2154 l_pvt_claim_Rec.EXCHANGE_RATE_DATE := l_deduction_rec.EXCHANGE_RATE_DATE;
2155 l_pvt_claim_Rec.EXCHANGE_RATE := l_deduction_rec.EXCHANGE_RATE;
2156 l_pvt_claim_Rec.STATUS_CODE := l_new_status_code;
2157
2158 --In case of Unapply need to update these fileds which will appear on History.
2159 l_pvt_claim_Rec.HISTORY_EVENT_DATE := l_deduction_rec.APPLIED_DATE;
2160 l_pvt_claim_Rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED; --Unapplied Amount
2161 l_pvt_claim_Rec.APPLIED_RECEIPT_ID := l_deduction_rec.APPLIED_RECEIPT_ID;
2162 l_pvt_claim_Rec.APPLIED_RECEIPT_NUMBER := l_deduction_rec.APPLIED_RECEIPT_NUMBER;
2163
2164 --Call Update_Claim to reflect the changes.
2165 OZF_claim_PVT.Update_claim(
2166 P_Api_Version => 1.0,
2167 P_Init_Msg_List => FND_API.G_FALSE,
2168 P_Commit => FND_API.G_FALSE,
2169 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2170 X_Return_Status => x_return_status,
2171 X_Msg_Count => x_msg_count,
2172 X_Msg_Data => x_msg_data,
2173 P_claim => l_pvt_claim_Rec,
2174 p_event => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
2175 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
2176 X_Object_Version_Number => l_object_version_number );
2177
2178 -- Check return status from the above procedure call
2179 IF x_return_status = FND_API.G_RET_STS_ERROR then
2180 raise FND_API.G_EXC_ERROR;
2181 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2182 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2183 END IF;
2184 ELSE
2185 --Build Notes.(44)
2186 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2187 --l_notes := l_notes||'The Status of the claim is CLOSED, NO Subsequent Receipt Unapplication'||
2188 -- ' for receipt number '||l_deduction_rec.APPLIED_RECEIPT_NUMBER||
2189 -- ' will be performed on this Claim.]';
2190
2191 -- bugfix 4869928
2192 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_CLAIM_CLOSED');
2193 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_NO_SUBS_APPLY', TO_CHAR(l_deduction_rec.RECEIPT_NUMBER) );
2194
2195 END IF;
2196
2197 --Call Create Notes API.
2198 Write_log(l_full_name,l_notes);
2199 JTF_NOTES_PUB.create_note(
2200 p_api_version=> 1.0
2201 ,x_return_status=> x_return_status
2202 ,x_msg_count=> x_msg_count
2203 ,x_msg_data=> x_msg_data
2204 ,p_source_object_id=> l_deduction_rec.CLAIM_ID -- claim_id
2205 ,p_source_object_code=> 'AMS_CLAM'
2206 ,p_notes=> l_notes
2207 ,p_note_status=> NULL
2208 ,p_entered_by=> FND_GLOBAL.user_id
2209 ,p_entered_date=> SYSDATE
2210 ,p_last_updated_by=> FND_GLOBAL.user_id
2211 ,x_jtf_note_id=> l_x_note_id
2212 ,p_note_type=> 'AMS_JUSTIFICATION' --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification
2213 ,p_last_update_date=> SYSDATE
2214 ,p_creation_date=> SYSDATE
2215 );
2216
2217 --Initialize l_notes
2218 l_notes := null;
2219 END IF; --IF l_split_flag = 'NO' THEN
2220
2221 -- --------------
2222 -- Split Scenario
2223 -- --------------
2224 IF l_split_flag = 'YES' THEN
2225 --//Added for bugfix : 13069412
2226 IF l_status_code <> 'CLOSED' THEN
2227
2228 IF l_claim_class = 'OVERPAYMENT' 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 -- Bug4300996/Bug4777500/Bug5102282:Cancel claim
2236 -- For invoice deductions, cancel if there are not other applications
2237 -- For non invoice deductions and overpayments, cancel if amount = 0
2238 IF l_cnt_apply_recs = 0 OR l_deduction_rec.amount = 0 THEN
2239 l_cancel_all_claim := 'Y';
2240 END IF;
2241
2242 Write_Log(l_full_name, 'Cancel All ? ' || l_cancel_all_claim );
2243
2244 --Fixed:Date:12-Mar-2003. Partial application for claim investigation.
2245 IF ( l_claim_class = 'DEDUCTION' and
2246 (l_source_object_id is NULL or l_source_object_id = FND_API.g_miss_num)) THEN
2247 IF l_deduction_rec.AMOUNT < 0 THEN
2248 l_deduction_rec.AMOUNT := l_deduction_rec.AMOUNT * -1;
2249 --l_deduction_rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED * -1;
2250 END IF;
2251 END IF;
2252
2253
2254 IF l_deduction_rec.receipt_id is NOT NULL then
2255 l_update_allowed_flag := 'Y';
2256 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.CLAIM_ID) LOOP
2257 IF ref_split_claim_csr.STATUS_CODE = 'PENDING_CLOSE' THEN
2258 l_update_allowed_flag := 'N';
2259 END IF;
2260 END LOOP;
2261 ELSE
2262 l_update_allowed_flag := 'Y';
2263 END IF;
2264
2265 Write_Log(l_full_name, 'Update Allowed ? ' || l_update_allowed_flag );
2266
2267 IF l_update_allowed_flag = 'N' THEN
2268
2269 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2270 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');
2271 FND_MSG_PUB.add;
2272 END IF;
2273 RAISE FND_API.G_EXC_ERROR;
2274 END IF;
2275
2276 FOR ref_split_claim_csr in split_claim_csr(l_deduction_rec.CLAIM_ID) LOOP
2277 -- If cancel all claim flag is Y, then cancel all non closed claims
2278 IF l_cancel_all_claim = 'Y' THEN
2279 --//Bugfix : 7526516
2280 IF ref_split_claim_csr.status_code NOT IN ('CLOSED', 'CANCELLED','PENDING_APPROVAL') THEN
2281 --Build Notes (46)
2282 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2283 --l_notes := l_notes||' The Status of the Claim is changed From Open to Cancelled';
2284
2285 -- bugfix 4869928
2286 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE','OPEN', 'CANCELLED');
2287
2288 -- Build Notes (47)
2289 --l_notes := l_notes||' due to Unapplication of Amount '||l_deduction_rec.AMOUNT_APPLIED||
2290 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')';
2291 -- bugfix 4869928
2292 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) );
2293
2294 --Build Claim Rec.
2295 l_pvt_claim_rec.claim_id := ref_split_claim_csr.claim_id;
2296 l_pvt_claim_rec.object_version_number := ref_split_claim_csr.object_version_number;
2297 l_pvt_claim_rec.status_code := 'CANCELLED';
2298 l_pvt_claim_rec.history_event_date := l_deduction_rec.applied_date;
2299 l_pvt_claim_rec.amount_applied := l_deduction_rec.amount_applied; --unapplied amount
2300 l_pvt_claim_rec.applied_receipt_id := l_deduction_rec.applied_receipt_id;
2301 l_pvt_claim_rec.applied_receipt_number := l_deduction_rec.applied_receipt_number;
2302
2303 --Call Update_Claim to reflect the changes.
2304 OZF_claim_PVT.Update_claim(
2305 p_api_version => 1.0,
2306 p_init_msg_list => FND_API.G_FALSE,
2307 p_commit => FND_API.G_FALSE,
2308 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2309 x_return_status => x_return_status,
2310 x_msg_count => x_msg_count,
2311 x_msg_data => x_msg_data,
2312 p_claim => l_pvt_claim_Rec,
2313 p_event => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
2314 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
2315 x_object_version_number => l_object_version_number
2316 );
2317 -- Check return status from the above procedure call
2318 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2319 RAISE FND_API.G_EXC_ERROR;
2320 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2322 END IF;
2323
2324 --Call Create Notes API.
2325 Write_log(l_full_name,l_notes);
2326 JTF_NOTES_PUB.create_note(
2327 p_api_version => 1.0
2328 ,x_return_status => x_return_status
2329 ,x_msg_count => x_msg_count
2330 ,x_msg_data => x_msg_data
2331 ,p_source_object_id => ref_split_claim_csr.claim_id --claim_id
2332 ,p_source_object_code => 'AMS_CLAM'
2333 ,p_notes => l_notes
2334 ,p_note_status => NULL
2335 ,p_entered_by => FND_GLOBAL.user_id
2336 ,p_entered_date => SYSDATE
2337 ,p_last_updated_by => FND_GLOBAL.user_id
2338 ,x_jtf_note_id => l_x_note_id
2339 ,p_note_type => 'AMS_JUSTIFICATION' --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification
2340 ,p_last_update_date => SYSDATE
2341 ,p_creation_date => SYSDATE
2342 );
2343 END IF;
2344 ELSE -- l_cancel_all_claim = 'N'
2345
2346 -- Update the first open/complete/pending approval claim.
2347
2348 l_new_deduction_amount := ref_split_claim_csr.AMOUNT + l_deduction_rec.AMOUNT_APPLIED;
2349 --//Bugfix : 7526516
2350 IF ref_split_claim_csr.status_code NOT IN ('CLOSED', 'CANCELLED','PENDING_APPROVAL') THEN
2351 --Build Notes
2352 --l_notes := '[Claim:'||l_deduction_rec.CLAIM_NUMBER||' Remark:';
2353
2354 IF ref_split_claim_csr.STATUS_CODE <> 'OPEN' THEN
2355 l_new_status_code := 'OPEN';
2356 --Build Notes(52)
2357 --l_notes := l_notes||'The Status of the Claim is changed From '||ref_split_claim_csr.STATUS_CODE||
2358 -- ' To '||l_new_status_code;
2359
2360 --bugfix 4869928
2361 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_CHANGE',ref_split_claim_csr.STATUS_CODE,l_status_code);
2362
2363 ELSE
2364 l_new_status_code := ref_split_claim_csr.STATUS_CODE;
2365 --Build Notes (53)
2366 --l_notes := l_notes||'The Status of the Claim ('||l_new_status_code||
2367 -- ') is NOT changed ';
2368
2369 --bugfix 4869928
2370 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_STATUS_SAME',l_new_status_code);
2371
2372 END IF;
2373
2374
2375 --Build Notes (54)
2376 --l_notes := l_notes||' and Amount is changed '||
2377 -- 'From '||ref_split_claim_csr.AMOUNT||' To '||l_deduction_rec.AMOUNT||
2378 -- ' due to Unapplication of Amount '||l_deduction_rec.AMOUNT_APPLIED||
2379 -- ' (Receipt Number:'||l_deduction_rec.applied_receipt_number||')';
2380
2381 --bugfix 4869928
2382 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_UNAPPLY_CHANGE'
2383 , TO_CHAR(ref_split_claim_csr.AMOUNT)
2384 , TO_CHAR(l_deduction_rec.AMOUNT)
2385 , TO_CHAR(l_deduction_rec.AMOUNT_APPLIED)
2386 , TO_CHAR(l_deduction_rec.applied_receipt_number)
2387 );
2388
2389 --Build Claim Rec.
2390 l_pvt_claim_Rec.CLAIM_ID := ref_split_claim_csr.CLAIM_ID;
2391 l_pvt_claim_Rec.OBJECT_VERSION_NUMBER := ref_split_claim_csr.OBJECT_VERSION_NUMBER;
2392 l_pvt_claim_Rec.STATUS_CODE := l_new_status_code;
2393 l_pvt_claim_Rec.AMOUNT := l_new_deduction_amount;
2394 l_pvt_claim_Rec.CURRENCY_CODE := l_deduction_rec.CURRENCY_CODE;
2395 l_pvt_claim_Rec.EXCHANGE_RATE_TYPE := l_deduction_rec.EXCHANGE_RATE_TYPE;
2396 l_pvt_claim_Rec.EXCHANGE_RATE_DATE := l_deduction_rec.EXCHANGE_RATE_DATE;
2397 l_pvt_claim_Rec.EXCHANGE_RATE := l_deduction_rec.EXCHANGE_RATE;
2398
2399
2400 IF l_deduction_rec.source_object_id IS NOT NULL AND
2401 l_deduction_rec.source_object_id <> FND_API.G_MISS_NUM THEN
2402 -- Build Notes :for transaction releated deduction
2403
2404 IF l_deduction_rec.receipt_number <> ref_split_claim_csr.receipt_number THEN
2405 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));
2406 ELSE
2407 l_notes := l_notes||' Receipt Reference '||ref_split_claim_csr.RECEIPT_NUMBER||' is not Changed.';
2408 END IF;
2409 l_pvt_claim_Rec.receipt_id := l_deduction_rec.receipt_id;
2410 l_pvt_claim_Rec.receipt_number := l_deduction_rec.receipt_number;
2411 ELSE
2412 -- Build Notes :for claim investigation
2413 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_RCPT_SAME', TO_CHAR(l_receipt_number) );
2414 END IF;
2415
2416 --Assign Applied Details to l_pvt_claim_rec
2417 l_pvt_claim_Rec.HISTORY_EVENT_DATE := l_deduction_rec.APPLIED_DATE;
2418 l_pvt_claim_Rec.AMOUNT_APPLIED := l_deduction_rec.AMOUNT_APPLIED; --Unapplied Amount
2419 l_pvt_claim_Rec.APPLIED_RECEIPT_ID := l_deduction_rec.APPLIED_RECEIPT_ID;
2420 l_pvt_claim_Rec.APPLIED_RECEIPT_NUMBER := l_deduction_rec.APPLIED_RECEIPT_NUMBER;
2421
2422 --Call Update_Claim to reflect the changes.
2423 OZF_claim_PVT.Update_claim(
2424 P_Api_Version => 1.0,
2425 P_Init_Msg_List => FND_API.G_FALSE,
2426 P_Commit => FND_API.G_FALSE,
2427 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2428 X_Return_Status => x_return_status,
2429 X_Msg_Count => x_msg_count,
2430 X_Msg_Data => x_msg_data,
2431 P_claim => l_pvt_claim_Rec,
2432 p_event => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
2433 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
2434 X_Object_Version_Number => l_object_version_number );
2435
2436 -- Check return status from the above procedure call
2437 IF x_return_status = FND_API.G_RET_STS_ERROR then
2438 raise FND_API.G_EXC_ERROR;
2439 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2440 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2441 END IF;
2442
2443 --Call Create Notes API.
2444 Write_log(l_full_name,l_notes);
2445 JTF_NOTES_PUB.create_note(
2446 p_api_version=> 1.0
2447 ,x_return_status=> x_return_status
2448 ,x_msg_count=> x_msg_count
2449 ,x_msg_data=> x_msg_data
2450 ,p_source_object_id=> l_deduction_rec.CLAIM_ID --claim_id
2451 ,p_source_object_code=> 'AMS_CLAM'
2452 ,p_notes=> l_notes
2453 ,p_note_status=> NULL
2454 ,p_entered_by=> FND_GLOBAL.user_id
2455 ,p_entered_date=> SYSDATE
2456 ,p_last_updated_by=> FND_GLOBAL.user_id
2457 ,x_jtf_note_id=> l_x_note_id
2458 ,p_note_type=> 'AMS_JUSTIFICATION' --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification
2459 ,p_last_update_date=> SYSDATE
2460 ,p_creation_date=> SYSDATE
2461 );
2462
2463 -- Now sync adjustment amounts
2464 update_parent_amounts(
2465 x_return_status => l_return_status
2466 ,x_msg_count => x_msg_count
2467 ,x_msg_data => x_msg_data
2468 ,p_deduction_rec => l_deduction_rec);
2469 IF l_return_status = FND_API.G_RET_STS_ERROR then
2470 RAISE FND_API.G_EXC_ERROR;
2471 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473 END IF;
2474
2475 EXIT; -- Work is done !
2476 END IF; -- Is claim open/complete/pending_approval?
2477 END IF; -- if l_cancel_claim = 'Y' */
2478 END LOOP;
2479
2480 ELSE
2481 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_CLAIM_CLOSED');
2482 l_notes := l_notes|| Build_Note('OZF_CLAM_NOTES_NO_SUBS_APPLY', TO_CHAR(l_deduction_rec.RECEIPT_NUMBER) );
2483
2484 END IF;-- Status Flag
2485 END IF; --IF l_split_flag = 'YES' THEN
2486
2487 -- Standard call to get message count and if count is 1, get message info.
2488 FND_MSG_PUB.Count_And_Get
2489 (p_count => x_msg_count,
2490 p_data => x_msg_data
2491 );
2492
2493 Write_Log(l_full_name, 'end');
2494
2495 EXCEPTION
2496 WHEN FND_API.G_EXC_ERROR THEN
2497 x_return_status := FND_API.G_RET_STS_ERROR;
2498 -- Standard call to get message count and if count=1, get the message
2499 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2500 ,p_count => x_msg_count
2501 ,p_data => x_msg_data
2502 );
2503
2504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2506 -- Standard call to get message count and if count=1, get the message
2507 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2508 ,p_count => x_msg_count
2509 ,p_data => x_msg_data
2510 );
2511 WHEN OTHERS THEN
2512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2513 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2514 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
2515 FND_MSG_PUB.add;
2516 END IF;
2517 -- Standard call to get message count and if count=1, get the message
2518 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2519 ,p_count => x_msg_count
2520 ,p_data => x_msg_data
2521 );
2522
2523 End Perform_Subsequent_Unapply;
2524
2525
2526 ---------------------------------------------------------------------
2527 -- PROCEDURE: Update_Deduction
2528 --
2529 -- PURPOSE: This procedure update a Deduction. It calls the Update_claim function
2530 -- in the private package.
2531 --
2532 -- PARAMETERS:
2533 -- IN
2534 -- p_api_version_number IN NUMBER Required
2535 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2536 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2537 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2538 -- P_deduction IN DEDUCTION_REC_TYPE Required
2539 --
2540 -- OUT:
2541 -- x_return_status OUT VARCHAR2
2542 -- x_msg_count OUT NUMBER
2543 -- x_msg_data OUT VARCHAR2
2544 -- x_object_version_number OUT NUMBER
2545 --
2546 -- NOTE:
2547 --
2548 ---------------------------------------------------------------------
2549 PROCEDURE Update_Deduction(
2550 p_api_version_number IN NUMBER,
2551 p_init_msg_list IN VARCHAR2,
2552 p_validation_level IN NUMBER,
2553 p_commit IN VARCHAR2,
2554
2555 x_return_status OUT NOCOPY VARCHAR2,
2556 x_msg_count OUT NOCOPY NUMBER,
2557 x_msg_data OUT NOCOPY VARCHAR2,
2558
2559 p_deduction IN DEDUCTION_REC_TYPE,
2560 x_object_version_number OUT NOCOPY NUMBER
2561 )
2562 IS
2563 x_claim_reason_code_id NUMBER;
2564 x_claim_reason_name VARCHAR2(80);
2565 x_claim_id NUMBER;
2566 x_claim_number VARCHAR2(30);
2567
2568 BEGIN
2569 -- Initialize API return status to SUCCESS
2570 x_return_status := FND_API.G_RET_STS_SUCCESS;
2571
2572 OZF_Claim_GRP.Update_Deduction(
2573 p_api_version_number,
2574 p_init_msg_list,
2575 p_validation_level,
2576 p_commit,
2577 x_return_status,
2578 x_msg_count,
2579 x_msg_data,
2580 p_deduction,
2581 x_object_version_number,
2582 x_claim_reason_code_id,
2583 x_claim_reason_name,
2584 x_claim_id,
2585 x_claim_number
2586 );
2587
2588 EXCEPTION
2589 WHEN OTHERS THEN
2590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2591 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2592 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
2593 FND_MSG_PUB.add;
2594 END IF;
2595 FND_MSG_PUB.Count_And_Get (
2596 p_encoded => FND_API.G_FALSE,
2597 p_count => x_msg_count,
2598 p_data => x_msg_data
2599 );
2600 End Update_Deduction;
2601
2602
2603 ---------------------------------------------------------------------
2604 -- PROCEDURE: Update_Deduction
2605 --
2606 -- PURPOSE: This procedure update a Deduction. It calls the Update_claim function
2607 -- in the private package.
2608 --
2609 -- PARAMETERS:
2610 -- IN:
2611 -- p_api_version_number IN NUMBER Required
2612 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2613 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2614 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2615 -- P_deduction IN DEDUCTION_REC_TYPE Required
2616 --
2617 -- OUT:
2618 -- x_return_status OUT NOCOPY VARCHAR2
2619 -- x_msg_count OUT NOCOPY NUMBER
2620 -- x_msg_data OUT NOCOPY VARCHAR2
2621 -- x_object_version_number OUT NOCOPY NUMBER
2622 -- X_CLAIM_REASON_CODE_ID OUT NOCOPY NUMBER
2623 -- X_CLAIM_REASON_NAME OUT NOCOPY VARCHAR2
2624 -- X_CLAIM_ID, OUT NOCOPY NUMBER
2625 -- X_CLAIM_NUMBER OUT NOCOPY VARCHAR2
2626 --
2627 -- Note:
2628 --
2629 ---------------------------------------------------------------------
2630 PROCEDURE Update_Deduction(
2631 p_api_version_number IN NUMBER,
2632 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2633 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2634 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2635
2636 x_return_status OUT NOCOPY VARCHAR2,
2637 x_msg_count OUT NOCOPY NUMBER,
2638 x_msg_data OUT NOCOPY VARCHAR2,
2639
2640 p_deduction IN DEDUCTION_REC_TYPE,
2641 x_object_version_number OUT NOCOPY NUMBER,
2642 x_claim_reason_code_id OUT NOCOPY NUMBER,
2643 x_claim_reason_name OUT NOCOPY VARCHAR2,
2644 x_claim_id OUT NOCOPY NUMBER,
2645 x_claim_number OUT NOCOPY VARCHAR2
2646 )
2647 IS
2648 l_api_version CONSTANT NUMBER := 1.0;
2649 l_api_name CONSTANT VARCHAR2(30) := 'Update_Deduction';
2650 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME||'.'||l_api_name;
2651 l_return_status VARCHAR2(1);
2652 --
2653 l_deduction_rec DEDUCTION_REC_TYPE := p_deduction;
2654
2655 CURSOR csr_claim_identifier(cv_source_object_id IN NUMBER) IS
2656 -- [BEGIN OF BUG 4130258 FIXING]
2657 -- SELECT claim_id
2658 SELECT root_claim_id
2659 FROM ozf_claims
2660 WHERE source_object_id = cv_source_object_id;
2661 -- ORDER BY claim_id DESC;
2662 -- [END OF BUG 4130258 FIXING]
2663
2664 CURSOR csr_claim_object_version(cv_claim_id IN NUMBER) IS
2665 SELECT claim_number
2666 , object_version_number
2667 FROM ozf_claims
2668 WHERE claim_id = cv_claim_id;
2669
2670 CURSOR csr_ar_receipt(cv_receipt_id IN NUMBER) IS
2671 SELECT receipt_number
2672 FROM ar_cash_receipts
2673 WHERE cash_receipt_id = cv_receipt_id;
2674
2675 CURSOR csr_return_claim_info(cv_claim_id IN NUMBER) IS
2676 SELECT c.claim_id
2677 , c.claim_number
2678 , c.object_version_number
2679 , c.reason_code_id
2680 , r.name
2681 FROM ozf_claims c
2682 , ozf_reason_codes_vl r
2683 WHERE c.claim_id = cv_claim_id
2684 AND c.reason_code_id = r.reason_code_id;
2685
2686 -- Fix for 5182492
2687 CURSOR csr_previous_claim_info(cv_claim_id IN NUMBER) IS
2688 SELECT amount ,
2689 due_date ,
2690 claim_date ,
2691 claim_type_id ,
2692 reason_code_id ,
2693 currency_code ,
2694 cust_account_id
2695 FROM ozf_claims
2696 WHERE claim_id = cv_claim_id;
2697
2698 l_object_version_number NUMBER;
2699 l_pvt_claim_rec OZF_CLAIM_PVT.claim_Rec_Type;
2700 l_user_status_id NUMBER :=FND_API.G_MISS_NUM;
2701 l_custom_setup_id NUMBER;
2702 l_claim_obj_ver_num NUMBER;
2703
2704 -- Fix for 5182492
2705 l_claim_old_amount NUMBER;
2706 l_claim_old_duedate DATE;
2707 l_claim_old_typeId NUMBER;
2708 l_claim_old_reasonId NUMBER;
2709 l_claim_old_date DATE;
2710 l_claim_old_currencyCode VARCHAR2(15);
2711 l_claim_old_accId NUMBER;
2712 l_perform_subs_apply BOOLEAN;
2713
2714 -- get object_version_number, claim_reason_code_id
2715 CURSOR claim_number_csr(p_id in number) IS
2716 SELECT object_version_number,
2717 reason_code_id,
2718 claim_id,
2719 claim_number
2720 FROM ozf_claims_all
2721 WHERE claim_id = p_id;
2722
2723 -- get claim_reason_name
2724 CURSOR claim_name_csr(p_reason_code_id in number) IS
2725 SELECT name
2726 FROM ozf_reason_codes_all_tl
2727 WHERE reason_code_id = p_reason_code_id;
2728
2729 -- get object_version_number
2730 CURSOR claim_object_version_csr(p_id in number) IS
2731 SELECT object_version_number
2732 FROM ozf_claims_all
2733 WHERE claim_id = p_id;
2734
2735 -- get minimum required data
2736 CURSOR claim_min_req_data_csr(p_id in number) IS
2737 SELECT claim_number
2738 FROM ozf_claims_all
2739 WHERE claim_id = p_id;
2740
2741 -- get claim_id, claim_number incase it is not passed by AR.
2742 CURSOR get_claim_identifier_csr(p_source_object_id in number) IS
2743 SELECT root_claim_id,claim_number
2744 FROM ozf_claims_all
2745 WHERE source_object_id = p_source_object_id
2746 AND root_claim_id = claim_id;
2747 --AND status_code <> 'CLOSED';
2748
2749 --//Bugfix : 8262818
2750 CURSOR cur_active_receipt (p_claim_id IN NUMBER
2751 ,p_cash_receipt_id IN NUMBER ) IS
2752 SELECT ra.cash_receipt_id,
2753 cr.receipt_number
2754 FROM ar_receivable_applications_all ra,
2755 ar_cash_receipts_all cr
2756 WHERE ra.cash_receipt_id = cr.cash_receipt_id
2757 AND ra.cash_receipt_id <> p_cash_receipt_id
2758 AND ra.application_ref_type = 'CLAIM'
2759 AND ra.secondary_application_ref_id = p_claim_id
2760 AND ra.status = 'APP'
2761 AND ra.display = 'Y';
2762
2763 l_active_rec_id NUMBER :=NULL;
2764 l_active_rec_number VARCHAR2(30) := NULL;
2765
2766 BEGIN
2767 -- Standard Start of API savepoint
2768 SAVEPOINT UPDATE_CLAIM_GRP;
2769
2770 Write_Log(l_full_name, 'start');
2771
2772 -- Standard call to check for call compatibility.
2773 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2774 p_api_version_number,
2775 l_api_name,
2776 G_PKG_NAME ) THEN
2777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2778 END IF;
2779
2780 -- Initialize message list if p_init_msg_list is set to TRUE.
2781 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2782 FND_MSG_PUB.initialize;
2783 END IF;
2784
2785 -- Initialize API return status to SUCCESS
2786 x_return_status := FND_API.G_RET_STS_SUCCESS;
2787
2788 -----------------------------------------
2789 -- 1. Minimum required fields checking --
2790 -----------------------------------------
2791 -- "claim_id" or "source_object_id" is required field for updat_deduction
2792 IF l_deduction_rec.claim_id IS NULL AND
2793 l_deduction_rec.source_object_id IS NULL THEN
2794 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2795 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUFFICIENT_VAL');
2796 FND_MSG_PUB.add;
2797 END IF;
2798 RAISE FND_API.G_EXC_ERROR;
2799 END IF;
2800
2801 IF l_deduction_rec.applied_action_type IS NULL OR
2802 l_deduction_rec.applied_action_type NOT IN ('A','U') THEN
2803 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2804 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INVALID_ACTION');
2805 FND_MSG_PUB.add;
2806 END IF;
2807 RAISE FND_API.G_EXC_ERROR;
2808 ELSE
2809 IF l_deduction_rec.applied_action_type = 'A' THEN
2810 IF l_deduction_rec.amount IS NULL OR
2811 l_deduction_rec.receipt_id IS NULL THEN
2812 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2813 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_INSUFFICIENT_VAL_A');
2814 FND_MSG_PUB.add;
2815 END IF;
2816 RAISE FND_API.G_EXC_ERROR;
2817 END IF;
2818 ELSIF l_deduction_rec.applied_action_type = 'U' THEN
2819 IF l_deduction_rec.amount IS NULL OR
2820 l_deduction_rec.amount_applied IS NULL OR
2821 l_deduction_rec.applied_receipt_id IS NULL THEN
2822 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2823 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_INSUFFICIENT_VAL_U');
2824 FND_MSG_PUB.add;
2825 END IF;
2826 RAISE FND_API.G_EXC_ERROR;
2827 END IF;
2828 END IF;
2829 END IF;
2830
2831 ------------------------------------------
2832 -- 2. Default and derive column valude --
2833 ------------------------------------------
2834 --derive claim_id, claim_number from source_object_id
2835 IF l_deduction_rec.claim_id IS NULL THEN
2836 OPEN csr_claim_identifier(l_deduction_rec.source_object_id);
2837 FETCH csr_claim_identifier INTO l_deduction_rec.claim_id;
2838 CLOSE csr_claim_identifier;
2839 END IF;
2840
2841 -- Get latest object_version_number
2842 OPEN csr_claim_object_version(l_deduction_rec.claim_id);
2843 FETCH csr_claim_object_version INTO l_deduction_rec.claim_number
2844 , l_deduction_rec.object_version_number;
2845 CLOSE csr_claim_object_version;
2846
2847 --//Bugfix : 8262818
2848 --//Get the active receipt details from AR
2849 OPEN cur_active_receipt(l_deduction_rec.claim_id,l_deduction_rec.receipt_id);
2850 FETCH cur_active_receipt INTO l_active_rec_id,l_active_rec_number;
2851 CLOSE cur_active_receipt;
2852
2853 l_deduction_rec.receipt_id := NVL(l_active_rec_id,l_deduction_rec.receipt_id);
2854 l_deduction_rec.receipt_number := NVL(l_active_rec_number,l_deduction_rec.receipt_number);
2855
2856 -- derive receipt_number from receipt_id
2857 IF l_deduction_rec.receipt_id IS NOT NULL AND
2858 l_deduction_rec.receipt_number IS NULL THEN
2859 OPEN csr_ar_receipt(l_deduction_rec.receipt_id);
2860 FETCH csr_ar_receipt INTO l_deduction_rec.receipt_number;
2861 CLOSE csr_ar_receipt;
2862 END IF;
2863
2864 -- switch amount sign for claim investigation
2865 IF l_deduction_rec.source_object_id IS NULL THEN
2866 l_deduction_rec.amount := l_deduction_rec.amount * -1;
2867 END IF;
2868
2869 -- [Begin of Debug Message]
2870 Write_Log(l_full_name, 'claim_number = '||l_deduction_rec.claim_number);
2871 Write_Log(l_full_name, 'amount = '||l_deduction_rec.amount);
2872 Write_Log(l_full_name, 'action type = '||l_deduction_rec.applied_action_type);
2873 Write_Log(l_full_name, 'apply receipt id = '||l_deduction_rec.applied_receipt_id);
2874 Write_Log(l_full_name, 'reference receipt = '||l_deduction_rec.receipt_number);
2875 -- [Begin of Debug Message]
2876
2877
2878 ----------------------------------------------------------
2879 -- 3. Required Fields checking for Apply or Unapply Action
2880 ----------------------------------------------------------
2881 IF (l_deduction_rec.claim_id IS NULL OR
2882 l_deduction_rec.claim_id = FND_API.G_MISS_NUM
2883 ) OR
2884 (l_deduction_rec.amount IS NULL OR
2885 l_deduction_rec.amount = FND_API.G_MISS_NUM
2886 ) THEN
2887 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2888 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUF_VAL_UPD');
2889 FND_MSG_PUB.add;
2890 END IF;
2891 RAISE FND_API.G_EXC_ERROR;
2892 ELSE
2893 IF l_deduction_rec.applied_action_type = 'A' THEN
2894 IF (l_deduction_rec.amount_applied IS NULL OR
2895 l_deduction_rec.amount_applied = FND_API.G_MISS_NUM
2896 ) OR
2897 (l_deduction_rec.applied_receipt_id IS NULL OR
2898 l_deduction_rec.applied_receipt_id = FND_API.G_MISS_NUM
2899 ) THEN
2900 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2901 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUF_VAL_UPD');
2902 FND_MSG_PUB.add;
2903 END IF;
2904 RAISE FND_API.G_EXC_ERROR;
2905 END IF;
2906 ELSIF l_deduction_rec.applied_action_type = 'U' THEN
2907 IF (l_deduction_rec.amount_applied IS NULL OR
2908 l_deduction_rec.amount_applied = FND_API.G_MISS_NUM
2909 ) OR
2910 (l_deduction_rec.applied_receipt_id IS NULL OR
2911 l_deduction_rec.applied_receipt_id = FND_API.G_MISS_NUM
2912 ) THEN
2913 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2914 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INSUF_VAL_UPD');
2915 FND_MSG_PUB.add;
2916 END IF;
2917 RAISE FND_API.G_EXC_ERROR;
2918 END IF;
2919 ELSE
2920 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2921 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INVALID_ACTION_UPD');
2922 FND_MSG_PUB.add;
2923 END IF;
2924 RAISE FND_API.G_EXC_ERROR;
2925 END IF;
2926 END IF;
2927
2928 IF l_deduction_rec.applied_action_type = 'A' THEN
2929 ----------------
2930 -- 4.1. Apply --
2931 ----------------
2932 -- Fix for 5182492
2933 -- If invoice deduction, then perform subsequent apply always
2934 -- If claim investigation, then check if sign of amount has changed.
2935 -- If amount sign has changed, then a new claim has to be created.
2936
2937 l_perform_subs_apply := TRUE;
2938 IF l_deduction_rec.source_object_id IS NULL THEN
2939 OPEN csr_previous_claim_info(l_deduction_rec.claim_id);
2940 FETCH csr_previous_claim_info INTO l_claim_old_amount,
2941 l_claim_old_duedate,
2942 l_claim_old_date,
2943 l_claim_old_typeId,
2944 l_claim_old_reasonId,
2945 l_claim_old_currencyCode,
2946 l_claim_old_accId;
2947 CLOSE csr_previous_claim_info;
2948
2949 IF SIGN(l_deduction_rec.amount) <> SIGN(l_claim_old_amount * -1) THEN
2950
2951 Write_Log(l_full_name, 'Creating a new Claim');
2952 l_perform_subs_apply := FALSE;
2953 l_deduction_rec.claim_id := FND_API.G_MISS_NUM;
2954 l_deduction_rec.claim_number := FND_API.G_MISS_CHAR;
2955 l_deduction_rec.claim_date := l_claim_old_date;
2956 l_deduction_rec.due_date := l_claim_old_duedate;
2957 l_deduction_rec.claim_type_id := l_claim_old_typeId;
2958 l_deduction_rec.reason_code_id := l_claim_old_reasonId;
2959 l_deduction_rec.currency_code := l_claim_old_currencyCode;
2960 l_deduction_rec.cust_account_id := l_claim_old_accId;
2961
2962 Create_Deduction(
2963 p_api_version_number => 1.0,
2964 p_init_msg_list => FND_API.g_false,
2965 p_validation_level => FND_API.g_valid_level_full,
2966 p_commit => FND_API.g_false,
2967 x_return_status => x_return_status,
2968 x_msg_count => x_msg_count,
2969 x_msg_data => x_msg_data,
2970 p_deduction => l_deduction_rec,
2971 x_claim_id => x_claim_id,
2972 x_claim_number => x_claim_number,
2973 x_claim_reason_code_id => x_claim_reason_code_id,
2974 x_claim_reason_name => x_claim_reason_name
2975 );
2976 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2977 RAISE FND_API.G_EXC_ERROR;
2978 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2980 END IF;
2981 END IF; -- amount sign has changed
2982 END IF ; -- is inv deduction
2983
2984 IF l_perform_subs_apply THEN
2985 Perform_Subsequent_Apply(
2986 p_api_version => l_api_version
2987 ,p_init_msg_list => FND_API.g_false
2988 ,p_validation_level => FND_API.g_valid_level_full
2989 ,x_return_status => l_return_status
2990 ,x_msg_count => x_msg_count
2991 ,x_msg_data => x_msg_data
2992 ,p_deduction => l_deduction_rec
2993 );
2994 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2995 RAISE FND_API.G_EXC_ERROR;
2996 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2998 END IF;
2999 END IF;
3000
3001 ELSIF l_deduction_rec.applied_action_type = 'U' THEN
3002 -----------------
3003 -- 4.2. Unpply --
3004 -----------------
3005 Perform_Subsequent_Unapply(
3006 p_api_version => l_api_version
3007 ,p_init_msg_list => FND_API.g_false
3008 ,p_validation_level => FND_API.g_valid_level_full
3009 ,x_return_status => l_return_status
3010 ,x_msg_count => x_msg_count
3011 ,x_msg_data => x_msg_data
3012 ,p_deduction => l_deduction_rec
3013 );
3014 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3015 RAISE FND_API.G_EXC_ERROR;
3016 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3018 END IF;
3019 END IF;
3020
3021 ---------------------------------------
3022 -- 5. Assign value to OUT parameters --
3023 ---------------------------------------
3024 OPEN csr_return_claim_info(l_deduction_rec.claim_id);
3025 FETCH csr_return_claim_info INTO x_claim_id
3026 , x_claim_number
3027 , x_object_version_number
3028 , x_claim_reason_code_id
3029 , x_claim_reason_name;
3030 CLOSE csr_return_claim_info;
3031
3032 Write_Log(l_full_name, 'claim_id = '||x_claim_id);
3033 Write_Log(l_full_name, 'claim_number = '||x_claim_number);
3034 Write_Log(l_full_name, 'reason_code_id = '||x_claim_reason_code_id);
3035 Write_Log(l_full_name, 'claim_reason_name = '||x_claim_reason_name);
3036
3037
3038 Write_Log(l_full_name, 'end');
3039
3040 -- Standard check for p_commit
3041 IF FND_API.to_Boolean(p_commit) THEN
3042 COMMIT WORK;
3043 END IF;
3044
3045 -- Standard call to get message count and if count is 1, get message info.
3046 FND_MSG_PUB.Count_And_Get(
3047 p_count => x_msg_count,
3048 p_data => x_msg_data
3049 );
3050
3051 EXCEPTION
3052 WHEN FND_API.G_EXC_ERROR THEN
3053 ROLLBACK TO UPDATE_CLAIM_GRP;
3054 x_return_status := FND_API.G_RET_STS_ERROR;
3055 FND_MSG_PUB.Count_And_Get (
3056 p_encoded => FND_API.G_FALSE,
3057 p_count => x_msg_count,
3058 p_data => x_msg_data
3059 );
3060
3061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3062 ROLLBACK TO UPDATE_CLAIM_GRP;
3063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3064 FND_MSG_PUB.Count_And_Get (
3065 p_encoded => FND_API.G_FALSE,
3066 p_count => x_msg_count,
3067 p_data => x_msg_data
3068 );
3069
3070 WHEN OTHERS THEN
3071 ROLLBACK TO UPDATE_claim_GRP;
3072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3073 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3074 FND_MESSAGE.set_name('AMS', 'AMS_CLAIM_UPD_DEDU_ERR');
3075 FND_MSG_PUB.add;
3076 END IF;
3077 FND_MSG_PUB.Count_And_Get (
3078 p_encoded => FND_API.G_FALSE,
3079 p_count => x_msg_count,
3080 p_data => x_msg_data
3081 );
3082
3083 END Update_Deduction;
3084
3085
3086 ---------------------------------------------------------------------
3087 -- PROCEDURE: Check_Cancell_Deduction
3088 --
3089 -- PURPOSE: This function checks whether a claims can be cancelled or not.
3090 --
3091 -- PARAMETERS:
3092 -- p_claim_id IN NUMBER Required
3093 --
3094 -- Note:
3095 ---------------------------------------------------------------------
3096 FUNCTION Check_Cancell_Deduction(
3097 p_claim_id IN NUMBER
3098 ) RETURN BOOLEAN
3099 IS
3100 l_return BOOLEAN := FALSE;
3101 l_status_code VARCHAR2(30);
3102 l_amount_remaining NUMBER;
3103 l_open_flag VARCHAR2(1) := 'T';
3104 l_claim_mode VARCHAR2(1) := 'A';
3105 l_root_claim_id NUMBER;
3106 l_sql_stmt VARCHAR2(1000);
3107 idx NUMBER;
3108 TYPE ClaimCurTyp IS REF CURSOR;
3109 split_claims_csr ClaimCurTyp;
3110
3111 BEGIN
3112
3113 l_sql_stmt := 'SELECT root_claim_id, status_code '||
3114 'FROM ozf_claims '||
3115 'WHERE claim_id = :1 ';
3116
3117 EXECUTE IMMEDIATE l_sql_stmt
3118 INTO l_root_claim_id
3119 , l_status_code
3120 USING p_claim_id;
3121
3122 -- for manual solution (when splits have individual applications)
3123 IF l_root_claim_id <> p_claim_id THEN
3124 l_claim_mode := 'M';
3125 IF l_status_code <> 'OPEN' THEN
3126 l_open_flag := 'F';
3127 END IF;
3128 END IF;
3129
3130 -- for automated solution
3131 IF l_claim_mode = 'A' THEN
3132
3133 l_sql_stmt := 'SELECT status_code, amount_remaining '||
3134 'FROM ozf_claims '||
3135 'WHERE root_claim_id = :1 ';
3136
3137 OPEN split_claims_csr FOR l_sql_stmt USING p_claim_id;
3138 LOOP
3139 FETCH split_claims_csr INTO l_status_code
3140 , l_amount_remaining;
3141 EXIT WHEN split_claims_csr%NOTFOUND OR split_claims_csr%NOTFOUND IS NULL;
3142
3143 IF l_status_code NOT IN ('OPEN', 'COMPETE', 'REJECTED', 'CLOSED') THEN
3144 IF l_status_code = 'CANCELLED' THEN
3145 IF l_amount_remaining <> 0 THEN
3146 l_open_flag := 'F';
3147 END IF;
3148 ELSE
3149 l_open_flag := 'F';
3150 END IF;
3151 END IF;
3152 idx := idx + 1;
3153 END LOOP;
3154 CLOSE split_claims_csr;
3155 END IF;
3156
3157 IF l_open_flag = 'T' THEN
3158 l_return := TRUE;
3159 ELSIF l_open_flag = 'F' THEN
3160 l_return := FALSE;
3161 END IF;
3162
3163 RETURN l_return;
3164
3165 END Check_Cancell_Deduction ;
3166
3167
3168 ---------------------------------------------------------------------
3169 -- PROCEDURE: Check_Cancell_Deduction
3170 --
3171 -- PURPOSE: This function checks whether a claims can be cancelled or not.
3172 --
3173 -- PARAMETERS:
3174 -- p_customer_trx_id IN NUMBER
3175 -- p_receipt_id IN NUMBER Required
3176 --
3177 -- Note: This function checks whether a claim exists in TM in OPEN status.
3178 ---------------------------------------------------------------------
3179 FUNCTION Check_Open_Claims(
3180 P_Customer_Trx_Id NUMBER,
3181 P_Receipt_Id NUMBER
3182 ) RETURN BOOLEAN
3183 IS
3184 l_open_claims_count NUMBER := 0;
3185 l_sql_stmt VARCHAR2(1000);
3186
3187 BEGIN
3188 IF p_customer_trx_id IS NOT NULL THEN
3189 l_sql_stmt := 'SELECT COUNT(claim_id) '||
3190 'FROM ozf_claims '||
3191 'WHERE source_object_id = :1 '||
3192 'AND status_code <> ''CLOSED'' ';
3193
3194 EXECUTE IMMEDIATE l_sql_stmt
3195 INTO l_open_claims_count
3196 USING p_customer_trx_id;
3197
3198 ELSIF p_customer_trx_id IS NULL AND p_receipt_id IS NOT NULL THEN
3199 l_sql_stmt := 'SELECT COUNT(claim_id) '||
3200 'FROM ozf_claims '||
3201 'WHERE receipt_id = :1 '||
3202 'AND status_code <> ''CLOSED'' ';
3203
3204 EXECUTE IMMEDIATE l_sql_stmt
3205 INTO l_open_claims_count
3206 USING p_receipt_id;
3207
3208 END IF;
3209
3210 IF l_open_claims_count >= 1 THEN
3211 RETURN TRUE;
3212 ELSE
3213 RETURN FALSE;
3214 END IF;
3215
3216 END Check_Open_Claims;
3217
3218
3219 ---------------------------------------------------------------------
3220 -- PROCEDURE: Get_Claim_Additional_Info
3221 --
3222 -- PURPOSE: The procedure will be used by AR to get additional of info.
3223 -- for invoice related deduction.
3224 --
3225 -- PARAMETERS:
3226 -- IN:
3227 -- p_customer_trx_id IN NUMBER
3228 --
3229 -- OUT:
3230 -- x_application_ref_num OUT VARCHAR2
3231 -- x_secondary_appl_ref_id OUT NUMBER
3232 -- x_customer_reference OUT VARCHAR2
3233 -- x_customer_reason OUT VARCHAR2
3234 --
3235 -- Note:
3236 ---------------------------------------------------------------------
3237 PROCEDURE Get_Claim_Additional_Info(
3238 p_customer_trx_id IN NUMBER,
3239 x_application_ref_num OUT NOCOPY VARCHAR2,
3240 x_secondary_appl_ref_id OUT NOCOPY NUMBER,
3241 x_customer_reference OUT NOCOPY VARCHAR2
3242 )
3243 IS
3244
3245 CURSOR csr_get_claim_dtls (cv_customer_trx_id IN NUMBER) IS
3246 SELECT claim_id, claim_number, customer_ref_number FROM ozf_claims
3247 WHERE source_object_id = cv_customer_trx_id
3248 AND claim_id = root_claim_id
3249 AND root_claim_id IS NOT NULL
3250 AND cust_account_id = (SELECT bill_to_customer_id from ra_customer_trx_all
3251 WHERE customer_trx_id = cv_customer_trx_id);
3252
3253 BEGIN
3254 /*
3255 l_sql_stmt := 'SELECT claim_id, claim_number, customer_ref_number '||
3256 'FROM ozf_claims '||
3257 'WHERE source_object_id = :1 '||
3258 'AND claim_id = root_claim_id ';
3259
3260 EXECUTE IMMEDIATE l_sql_stmt
3261 INTO x_application_ref_num
3262 , x_secondary_appl_ref_id
3263 , x_customer_reference
3264 USING p_customer_trx_id;
3265 */
3266 OPEN csr_get_claim_dtls(p_customer_trx_id);
3267 FETCH csr_get_claim_dtls INTO x_application_ref_num
3268 , x_secondary_appl_ref_id
3269 , x_customer_reference;
3270 IF csr_get_claim_dtls%NOTFOUND THEN
3271 x_application_ref_num := NULL;
3272 x_secondary_appl_ref_id := NULL;
3273 x_customer_reference := NULL;
3274 END IF;
3275 CLOSE csr_get_claim_dtls;
3276
3277 EXCEPTION
3278 WHEN NO_DATA_FOUND THEN
3279 IF csr_get_claim_dtls%ISOPEN THEN
3280 CLOSE csr_get_claim_dtls;
3281 END IF;
3282 x_application_ref_num := NULL;
3283 x_secondary_appl_ref_id := NULL;
3284 x_customer_reference := NULL;
3285
3286 WHEN OTHERS THEN
3287 IF csr_get_claim_dtls%ISOPEN THEN
3288 CLOSE csr_get_claim_dtls;
3289 END IF;
3290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3291 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3292 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
3293 FND_MSG_PUB.Add;
3294 END IF;
3295 x_application_ref_num := NULL;
3296 x_secondary_appl_ref_id := NULL;
3297 x_customer_reference := NULL;
3298
3299 END Get_Claim_Additional_Info;
3300
3301 END OZF_CLAIM_GRP;