[Home] [Help]
PACKAGE BODY: APPS.OZF_AR_PAYMENT_PVT
Source
1 PACKAGE BODY OZF_AR_PAYMENT_PVT AS
2 /* $Header: ozfvarpb.pls 120.31.12020000.4 2013/02/26 14:03:06 arpchakr ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_AR_PAYMENT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(15) := 'ozfvcarpb.pls';
6
7 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8
9 /*=======================================================================*
10 | PROCEDURE
11 | Query_Claim
12 |
13 | NOTES
14 |
15 | HISTORY
16 | 15-MAR-2002 mchang Create.
17 *=======================================================================*/
18 PROCEDURE Query_Claim(
19 p_claim_id IN NUMBER
20 ,x_claim_rec OUT NOCOPY OZF_Claim_PVT.claim_rec_type
21 ,x_return_status OUT NOCOPY VARCHAR2
22 )
23 IS
24 BEGIN
25 SELECT
26 CLAIM_ID
27 ,OBJECT_VERSION_NUMBER
28 ,LAST_UPDATE_DATE
29 ,LAST_UPDATED_BY
30 ,CREATION_DATE
31 ,CREATED_BY
32 ,LAST_UPDATE_LOGIN
33 ,REQUEST_ID
34 ,PROGRAM_APPLICATION_ID
35 ,PROGRAM_UPDATE_DATE
36 ,PROGRAM_ID
37 ,CREATED_FROM
38 ,BATCH_ID
39 ,CLAIM_NUMBER
40 ,CLAIM_TYPE_ID
41 ,CLAIM_CLASS
42 ,CLAIM_DATE
43 ,DUE_DATE
44 ,OWNER_ID
45 ,HISTORY_EVENT
46 ,HISTORY_EVENT_DATE
47 ,HISTORY_EVENT_DESCRIPTION
48 ,SPLIT_FROM_CLAIM_ID
49 ,DUPLICATE_CLAIM_ID
50 ,SPLIT_DATE
51 ,ROOT_CLAIM_ID
52 ,AMOUNT
53 ,AMOUNT_ADJUSTED
54 ,AMOUNT_REMAINING
55 ,AMOUNT_SETTLED
56 ,ACCTD_AMOUNT
57 ,ACCTD_AMOUNT_REMAINING
58 ,TAX_AMOUNT
59 ,TAX_CODE
60 ,TAX_CALCULATION_FLAG
61 ,CURRENCY_CODE
62 ,EXCHANGE_RATE_TYPE
63 ,EXCHANGE_RATE_DATE
64 ,EXCHANGE_RATE
65 ,SET_OF_BOOKS_ID
66 ,ORIGINAL_CLAIM_DATE
67 ,SOURCE_OBJECT_ID
68 ,SOURCE_OBJECT_CLASS
69 ,SOURCE_OBJECT_TYPE_ID
70 ,SOURCE_OBJECT_NUMBER
71 ,CUST_ACCOUNT_ID
72 ,CUST_BILLTO_ACCT_SITE_ID
73 ,CUST_SHIPTO_ACCT_SITE_ID
74 ,LOCATION_ID
75 ,PAY_RELATED_ACCOUNT_FLAG
76 ,RELATED_CUST_ACCOUNT_ID
77 ,RELATED_SITE_USE_ID
78 ,RELATIONSHIP_TYPE
79 ,VENDOR_ID
80 ,VENDOR_SITE_ID
81 ,REASON_TYPE
82 ,REASON_CODE_ID
83 ,TASK_TEMPLATE_GROUP_ID
84 ,STATUS_CODE
85 ,USER_STATUS_ID
86 ,SALES_REP_ID
87 ,COLLECTOR_ID
88 ,CONTACT_ID
89 ,BROKER_ID
90 ,TERRITORY_ID
91 ,CUSTOMER_REF_DATE
92 ,CUSTOMER_REF_NUMBER
93 ,ASSIGNED_TO
94 ,RECEIPT_ID
95 ,RECEIPT_NUMBER
96 ,DOC_SEQUENCE_ID
97 ,DOC_SEQUENCE_VALUE
98 ,GL_DATE
99 ,PAYMENT_METHOD
100 ,VOUCHER_ID
101 ,VOUCHER_NUMBER
102 ,PAYMENT_REFERENCE_ID
103 ,PAYMENT_REFERENCE_NUMBER
104 ,PAYMENT_REFERENCE_DATE
105 ,PAYMENT_STATUS
106 ,APPROVED_FLAG
107 ,APPROVED_DATE
108 ,APPROVED_BY
109 ,SETTLED_DATE
110 ,SETTLED_BY
111 ,EFFECTIVE_DATE
112 ,CUSTOM_SETUP_ID
113 ,TASK_ID
114 ,COUNTRY_ID
115 ,COMMENTS
116 ,ATTRIBUTE_CATEGORY
117 ,ATTRIBUTE1
118 ,ATTRIBUTE2
119 ,ATTRIBUTE3
120 ,ATTRIBUTE4
121 ,ATTRIBUTE5
122 ,ATTRIBUTE6
123 ,ATTRIBUTE7
124 ,ATTRIBUTE8
125 ,ATTRIBUTE9
126 ,ATTRIBUTE10
127 ,ATTRIBUTE11
128 ,ATTRIBUTE12
129 ,ATTRIBUTE13
130 ,ATTRIBUTE14
131 ,ATTRIBUTE15
132 ,DEDUCTION_ATTRIBUTE_CATEGORY
133 ,DEDUCTION_ATTRIBUTE1
134 ,DEDUCTION_ATTRIBUTE2
135 ,DEDUCTION_ATTRIBUTE3
136 ,DEDUCTION_ATTRIBUTE4
137 ,DEDUCTION_ATTRIBUTE5
138 ,DEDUCTION_ATTRIBUTE6
139 ,DEDUCTION_ATTRIBUTE7
140 ,DEDUCTION_ATTRIBUTE8
141 ,DEDUCTION_ATTRIBUTE9
142 ,DEDUCTION_ATTRIBUTE10
143 ,DEDUCTION_ATTRIBUTE11
144 ,DEDUCTION_ATTRIBUTE12
145 ,DEDUCTION_ATTRIBUTE13
146 ,DEDUCTION_ATTRIBUTE14
147 ,DEDUCTION_ATTRIBUTE15
148 ,ORG_ID
149 ,wo_rec_trx_id
150 ,legal_entity_id
151 INTO
152 x_claim_rec.claim_id
153 ,x_claim_rec.object_version_number
154 ,x_claim_rec.last_update_date
155 ,x_claim_rec.last_updated_by
156 ,x_claim_rec.creation_date
157 ,x_claim_rec.created_by
158 ,x_claim_rec.last_update_login
159 ,x_claim_rec.request_id
160 ,x_claim_rec.program_application_id
161 ,x_claim_rec.program_update_date
162 ,x_claim_rec.program_id
163 ,x_claim_rec.created_from
164 ,x_claim_rec.batch_id
165 ,x_claim_rec.claim_number
166 ,x_claim_rec.claim_type_id
167 ,x_claim_rec.claim_class
168 ,x_claim_rec.claim_date
169 ,x_claim_rec.due_date
170 ,x_claim_rec.owner_id
171 ,x_claim_rec.history_event
172 ,x_claim_rec.history_event_date
173 ,x_claim_rec.history_event_description
174 ,x_claim_rec.split_from_claim_id
175 ,x_claim_rec.duplicate_claim_id
176 ,x_claim_rec.split_date
177 ,x_claim_rec.root_claim_id
178 ,x_claim_rec.amount
179 ,x_claim_rec.amount_adjusted
180 ,x_claim_rec.amount_remaining
181 ,x_claim_rec.amount_settled
182 ,x_claim_rec.acctd_amount
183 ,x_claim_rec.acctd_amount_remaining
184 ,x_claim_rec.tax_amount
185 ,x_claim_rec.tax_code
186 ,x_claim_rec.tax_calculation_flag
187 ,x_claim_rec.currency_code
188 ,x_claim_rec.exchange_rate_type
189 ,x_claim_rec.exchange_rate_date
190 ,x_claim_rec.exchange_rate
191 ,x_claim_rec.set_of_books_id
192 ,x_claim_rec.original_claim_date
193 ,x_claim_rec.source_object_id
194 ,x_claim_rec.source_object_class
195 ,x_claim_rec.source_object_type_id
196 ,x_claim_rec.source_object_number
197 ,x_claim_rec.cust_account_id
198 ,x_claim_rec.cust_billto_acct_site_id
199 ,x_claim_rec.cust_shipto_acct_site_id
200 ,x_claim_rec.location_id
201 ,x_claim_rec.pay_related_account_flag
202 ,x_claim_rec.related_cust_account_id
203 ,x_claim_rec.related_site_use_id
204 ,x_claim_rec.relationship_type
205 ,x_claim_rec.vendor_id
206 ,x_claim_rec.vendor_site_id
207 ,x_claim_rec.reason_type
208 ,x_claim_rec.reason_code_id
209 ,x_claim_rec.task_template_group_id
210 ,x_claim_rec.status_code
211 ,x_claim_rec.user_status_id
212 ,x_claim_rec.sales_rep_id
213 ,x_claim_rec.collector_id
214 ,x_claim_rec.contact_id
215 ,x_claim_rec.broker_id
216 ,x_claim_rec.territory_id
217 ,x_claim_rec.customer_ref_date
218 ,x_claim_rec.customer_ref_number
219 ,x_claim_rec.assigned_to
220 ,x_claim_rec.receipt_id
221 ,x_claim_rec.receipt_number
222 ,x_claim_rec.doc_sequence_id
223 ,x_claim_rec.doc_sequence_value
224 ,x_claim_rec.gl_date
225 ,x_claim_rec.payment_method
226 ,x_claim_rec.voucher_id
227 ,x_claim_rec.voucher_number
228 ,x_claim_rec.payment_reference_id
229 ,x_claim_rec.payment_reference_number
230 ,x_claim_rec.payment_reference_date
231 ,x_claim_rec.payment_status
232 ,x_claim_rec.approved_flag
233 ,x_claim_rec.approved_date
234 ,x_claim_rec.approved_by
235 ,x_claim_rec.settled_date
236 ,x_claim_rec.settled_by
237 ,x_claim_rec.effective_date
238 ,x_claim_rec.custom_setup_id
239 ,x_claim_rec.task_id
240 ,x_claim_rec.country_id
241 ,x_claim_rec.comments
242 ,x_claim_rec.attribute_category
243 ,x_claim_rec.attribute1
244 ,x_claim_rec.attribute2
245 ,x_claim_rec.attribute3
246 ,x_claim_rec.attribute4
247 ,x_claim_rec.attribute5
248 ,x_claim_rec.attribute6
249 ,x_claim_rec.attribute7
250 ,x_claim_rec.attribute8
251 ,x_claim_rec.attribute9
252 ,x_claim_rec.attribute10
253 ,x_claim_rec.attribute11
254 ,x_claim_rec.attribute12
255 ,x_claim_rec.attribute13
256 ,x_claim_rec.attribute14
257 ,x_claim_rec.attribute15
258 ,x_claim_rec.deduction_attribute_category
259 ,x_claim_rec.deduction_attribute1
260 ,x_claim_rec.deduction_attribute2
261 ,x_claim_rec.deduction_attribute3
262 ,x_claim_rec.deduction_attribute4
263 ,x_claim_rec.deduction_attribute5
264 ,x_claim_rec.deduction_attribute6
265 ,x_claim_rec.deduction_attribute7
266 ,x_claim_rec.deduction_attribute8
267 ,x_claim_rec.deduction_attribute9
268 ,x_claim_rec.deduction_attribute10
269 ,x_claim_rec.deduction_attribute11
270 ,x_claim_rec.deduction_attribute12
271 ,x_claim_rec.deduction_attribute13
272 ,x_claim_rec.deduction_attribute14
273 ,x_claim_rec.deduction_attribute15
274 ,x_claim_rec.org_id
275 ,x_claim_rec.wo_rec_trx_id
276 ,x_claim_rec.legal_entity_id
277 FROM ozf_claims_all
278 WHERE claim_id = p_claim_id ;
279 x_return_status := FND_API.g_ret_sts_success;
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
283 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_QUERY_ERROR');
284 FND_MSG_PUB.add;
285 END IF;
286 x_return_status := FND_API.g_ret_sts_unexp_error;
287 END Query_Claim;
288
289
290 /*=======================================================================*
291 | PROCEDURE
292 | Close_Claim
293 |
294 | NOTES
295 |
296 | HISTORY
297 | 15-MAY-2002 mchang Create.
298 *=======================================================================*/
299 PROCEDURE Close_Claim(
300 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
301
302 ,x_return_status OUT NOCOPY VARCHAR2
303 ,x_msg_data OUT NOCOPY VARCHAR2
304 ,x_msg_count OUT NOCOPY NUMBER
305 )
306 IS
307 l_api_version CONSTANT NUMBER := 1.0;
308 l_api_name CONSTANT VARCHAR2(30) := 'Close_Claim';
309 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
310 l_return_status VARCHAR2(1);
311
312 BEGIN
313 -------------------- initialize -----------------------
314 IF g_debug THEN
315 OZF_Utility_PVT.debug_message(l_full_name||': start');
316 END IF;
317
318 x_return_status := FND_API.g_ret_sts_success;
319
320 ------------------------ start -------------------------
321 OZF_SETTLEMENT_DOC_PVT.Update_Claim_From_Settlement(
322 p_api_version_number => l_api_version,
323 p_init_msg_list => FND_API.g_false,
324 p_commit => FND_API.g_false,
325 p_validation_level => FND_API.g_valid_level_full,
326 x_return_status => l_return_status,
327 x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data,
329 p_claim_id => p_claim_rec.claim_id,
330 p_object_version_number => p_claim_rec.object_version_number,
331 p_status_code => 'CLOSED',
332 p_payment_status => 'PAID'
333 );
334 IF l_return_status = FND_API.g_ret_sts_error THEN
335 RAISE FND_API.g_exc_error;
336 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
337 RAISE FND_API.g_exc_unexpected_error;
338 END IF;
339
340 IF g_debug THEN
341 OZF_Utility_PVT.debug_message(l_full_name||': end');
342 END IF;
343 EXCEPTION
344 WHEN FND_API.g_exc_error THEN
345 x_return_status := FND_API.g_ret_sts_error;
346
347 WHEN FND_API.g_exc_unexpected_error THEN
348 x_return_status := FND_API.g_ret_sts_unexp_error ;
349
350 WHEN OTHERS THEN
351 x_return_status := FND_API.g_ret_sts_unexp_error ;
352 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
353 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
354 END IF;
355
356 END Close_Claim;
357
358
359 /*=======================================================================*
360 | PROCEDURE
361 | Unapply_Claim_Investigation
362 |
363 | NOTES
364 |
365 | HISTORY
366 | 15-MAR-2002 mchang Create.
367 *=======================================================================*/
368 PROCEDURE Unapply_Claim_Investigation(
369 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
370 ,p_reapply_amount IN NUMBER
371
372 ,x_return_status OUT NOCOPY VARCHAR2
373 ,x_msg_data OUT NOCOPY VARCHAR2
374 ,x_msg_count OUT NOCOPY NUMBER
375 )
376 IS
377 l_api_version CONSTANT NUMBER := 1.0;
378 l_api_name CONSTANT VARCHAR2(30) := 'Unapply_Claim_Investigation';
379 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
380 l_return_status VARCHAR2(1);
381
382 BEGIN
383 -------------------- initialize -----------------------
384 IF g_debug THEN
385 OZF_Utility_PVT.debug_message(l_full_name||': start');
386 END IF;
387
388 x_return_status := FND_API.g_ret_sts_success;
389
390 ------------------------ start -------------------------
391 IF g_debug THEN
392 OZF_Utility_PVT.debug_message('Reapply amount='||p_reapply_amount);
393 END IF;
394 -- For partical settlement
395 ARP_DEDUCTION_COVER.split_claim_reapplication(
396 p_claim_id => p_claim_rec.root_claim_id,
397 p_customer_trx_id => p_claim_rec.source_object_id,
398 p_amount => p_reapply_amount,
399 p_init_msg_list => FND_API.g_false,
400 p_cash_receipt_id => p_claim_rec.receipt_id,
401 p_ussgl_transaction_code => NULL,
402 x_return_status => l_return_status,
403 x_msg_count => x_msg_count,
404 x_msg_data => x_msg_data
405 );
406 IF l_return_status = FND_API.g_ret_sts_error THEN
407 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
408 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_SPLIT_REAPP_ERR');
409 FND_MSG_PUB.add;
410 END IF;
411 RAISE FND_API.g_exc_error;
412 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
413 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
414 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_SPLIT_REAPP_UERR');
415 FND_MSG_PUB.add;
416 END IF;
417 RAISE FND_API.g_exc_unexpected_error;
418 END IF;
419
420 IF g_debug THEN
421 OZF_Utility_PVT.debug_message(l_full_name||': end');
422 END IF;
423 EXCEPTION
424 WHEN FND_API.g_exc_error THEN
425 x_return_status := FND_API.g_ret_sts_error;
426
427 WHEN FND_API.g_exc_unexpected_error THEN
428 x_return_status := FND_API.g_ret_sts_unexp_error ;
429
430 WHEN OTHERS THEN
431 x_return_status := FND_API.g_ret_sts_unexp_error ;
432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
433 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
434 END IF;
435
436 END Unapply_Claim_Investigation;
437
438
439 /*=======================================================================*
440 | PROCEDURE
441 | Apply_On_Account_Credit
442 |
443 | NOTES
444 |
445 | HISTORY
446 | 15-MAR-2002 mchang Create.
447 *=======================================================================*/
448 PROCEDURE Apply_On_Account_Credit(
449 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
450 ,p_credit_amount IN NUMBER DEFAULT NULL
451
452 ,x_return_status OUT NOCOPY VARCHAR2
453 ,x_msg_data OUT NOCOPY VARCHAR2
454 ,x_msg_count OUT NOCOPY NUMBER
455 )
456 IS
457 l_api_version CONSTANT NUMBER := 1.0;
458 l_api_name CONSTANT VARCHAR2(30) := 'Apply_On_Account_Credit';
459 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
460 l_return_status VARCHAR2(1);
461
462 l_application_ref_num VARCHAR2(30); --Bug:2781186
463 l_secondary_appl_ref_id NUMBER; --Bug:2781186
464 l_customer_reference VARCHAR2(30); --Bug:2781186
465
466 l_amount_applied NUMBER;
467
468 -- bug#9279072
469 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
470
471
472 --Start:Bug:2781186
473 --Cursor to get customer reason,customer ref, reason code id
474 CURSOR csr_get_more_root_clm_dtls (cv_claim_id IN NUMBER) IS
475 SELECT claim_number, claim_id, customer_ref_number
476 FROM ozf_claims_all
477 WHERE claim_id = cv_claim_id;
478
479
480 --Cursor to get customer reason,customer ref, reason code id in case of child cliam
481 CURSOR csr_get_more_chld_clm_dtls (cv_claim_id IN NUMBER) IS
482 SELECT p.claim_number, p.claim_id, c.customer_ref_number
483 FROM ozf_claims c , ozf_claims p
484 WHERE c.claim_id = cv_claim_id
485 AND c.root_claim_id = p.claim_id;
486 --End:Bug:2781186
487
488
489 -- bug#9279072(+)
490 CURSOR csr_rec_flex_flds (p_claim_id IN NUMBER) IS
491 SELECT deduction_attribute1
492 , deduction_attribute2
493 , deduction_attribute3
494 , deduction_attribute4
495 , deduction_attribute5
496 , deduction_attribute6
497 , deduction_attribute7
498 , deduction_attribute8
499 , deduction_attribute9
500 , deduction_attribute10
501 , deduction_attribute11
502 , deduction_attribute12
503 , deduction_attribute13
504 , deduction_attribute14
505 , deduction_attribute15
506 FROM ozf_claims_all
507 WHERE claim_id = p_claim_id;
508 -- bug#9279072(-)
509
510 BEGIN
511 -------------------- initialize -----------------------
512 IF g_debug THEN
513 OZF_Utility_PVT.debug_message(l_full_name||': start');
514 END IF;
515
516 x_return_status := FND_API.g_ret_sts_success;
517
518 --Bug:2781186 Get more claim details
519 IF p_claim_rec.claim_id = p_claim_rec.root_claim_id THEN
520 OPEN csr_get_more_root_clm_dtls(p_claim_rec.claim_id);
521 FETCH csr_get_more_root_clm_dtls INTO l_application_ref_num,l_secondary_appl_ref_id,l_customer_reference;
522 CLOSE csr_get_more_root_clm_dtls;
523 ELSE
524 OPEN csr_get_more_chld_clm_dtls(p_claim_rec.claim_id);
525 FETCH csr_get_more_chld_clm_dtls INTO l_application_ref_num,l_secondary_appl_ref_id,l_customer_reference;
526 CLOSE csr_get_more_chld_clm_dtls;
527 END IF;
528
529 -- bug#9279072(+)
530 OPEN csr_rec_flex_flds(p_claim_rec.claim_id);
531 FETCH csr_rec_flex_flds INTO l_attribute_rec.attribute1
532 , l_attribute_rec.attribute2
533 , l_attribute_rec.attribute3
534 , l_attribute_rec.attribute4
535 , l_attribute_rec.attribute5
536 , l_attribute_rec.attribute6
537 , l_attribute_rec.attribute7
538 , l_attribute_rec.attribute8
539 , l_attribute_rec.attribute9
540 , l_attribute_rec.attribute10
541 , l_attribute_rec.attribute11
542 , l_attribute_rec.attribute12
543 , l_attribute_rec.attribute13
544 , l_attribute_rec.attribute14
545 , l_attribute_rec.attribute15;
546 CLOSE csr_rec_flex_flds;
547
548 IF g_debug THEN
549 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute1 = '||l_attribute_rec.attribute1);
550 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute2 = '||l_attribute_rec.attribute2);
551 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute3 = '||l_attribute_rec.attribute3);
552 END IF;
553 -- bug#9279072(-)
554
555 l_amount_applied := NVL(p_credit_amount, p_claim_rec.amount_settled) * -1;
556
557 ------------------------ start -------------------------
558 AR_RECEIPT_API_COVER.Apply_on_account(
559 -- Standard API parameters
560 p_api_version => l_api_version,
561 p_init_msg_list => FND_API.g_false,
562 p_commit => FND_API.g_false,
563 p_validation_level => FND_API.g_valid_level_full,
564 x_return_status => l_return_status,
565 x_msg_count => x_msg_count,
566 x_msg_data => x_msg_data,
567 -- Receipt application parameters.
568 p_cash_receipt_id => p_claim_rec.receipt_id,
569 p_receipt_number => NULL, --p_claim_rec.receipt_number,
570 p_amount_applied => l_amount_applied,
571 --p_apply_date => SYSDATE, --AR should default
572 --p_apply_gl_date => p_claim_rec.gl_date, --11.5.10 Enhancements. AR should default
573 --p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
574 --p_attribute_rec IN attribute_rec_type DEFAULT attribute_rec_const,
575 -- Global Flexfield parameters
576 --p_global_attribute_rec IN global_attribute_rec_type DEFAULT global_attribute_rec_const,
577 -- bug#9279072 (+)
578 p_attribute_rec => l_attribute_rec,
579 -- bug#9279072 (-)
580 p_comments => SUBSTRB(p_claim_rec.comments, 1, 240),
581 p_application_ref_num => l_application_ref_num,
582 p_secondary_application_ref_id => l_secondary_appl_ref_id,
583 p_customer_reference => l_customer_reference --11.5.10 enhancements. TM should pass.
584 );
585 IF l_return_status = FND_API.g_ret_sts_error THEN
586 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
587 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_REC_APPACC_ERR');
588 FND_MSG_PUB.add;
589 END IF;
590 RAISE FND_API.g_exc_error;
591 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
592 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
593 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_REC_APPACC_UERR');
594 FND_MSG_PUB.add;
595 END IF;
596 RAISE FND_API.g_exc_unexpected_error;
597 END IF;
598
599 IF g_debug THEN
600 OZF_Utility_PVT.debug_message(l_full_name||': end');
601 END IF;
602 EXCEPTION
603 WHEN FND_API.g_exc_error THEN
604 x_return_status := FND_API.g_ret_sts_error;
605
606 WHEN FND_API.g_exc_unexpected_error THEN
607 x_return_status := FND_API.g_ret_sts_unexp_error ;
608
609 WHEN OTHERS THEN
610 x_return_status := FND_API.g_ret_sts_unexp_error ;
611 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
612 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
613 END IF;
614
615 END Apply_On_Account_Credit;
616
617
618 /*=======================================================================*
619 | PROCEDURE
620 | Unapply_from_Receipt
621 |
622 | NOTES
623 |
624 | HISTORY
625 | 15-MAR-2002 mchang Create.
626 *=======================================================================*/
627 PROCEDURE Unapply_from_Receipt(
628 p_cash_receipt_id IN NUMBER
629 ,p_customer_trx_id IN NUMBER
630
631 ,x_return_status OUT NOCOPY VARCHAR2
632 ,x_msg_data OUT NOCOPY VARCHAR2
633 ,x_msg_count OUT NOCOPY NUMBER
634 )
635 IS
636 l_api_version CONSTANT NUMBER := 1.0;
637 l_api_name CONSTANT VARCHAR2(30) := 'Unapply_from_Receipt';
638 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
639 l_return_status VARCHAR2(1);
640
641 l_payment_schedule_id NUMBER;
642
643 CURSOR csr_payment_schedule(cv_customer_trx_id IN NUMBER) IS
644 SELECT payment_schedule_id
645 FROM ar_payment_schedules
646 WHERE customer_trx_id = cv_customer_trx_id;
647
648 BEGIN
649 -------------------- initialize -----------------------
650 IF g_debug THEN
651 OZF_Utility_PVT.debug_message(l_full_name||': start');
652 END IF;
653
654 x_return_status := FND_API.g_ret_sts_success;
655
656 ------------------------ start -------------------------
657 OPEN csr_payment_schedule(p_customer_trx_id);
658 FETCH csr_payment_schedule INTO l_payment_schedule_id;
659 CLOSE csr_payment_schedule;
660
661 AR_RECEIPT_API_COVER.Unapply(
662 -- Standard API parameters
663 p_api_version => l_api_version,
664 p_init_msg_list => FND_API.g_false,
665 p_commit => FND_API.g_false,
666 p_validation_level => FND_API.g_valid_level_full,
667 x_return_status => l_return_status,
668 x_msg_count => x_msg_count,
669 x_msg_data => x_msg_data,
670 -- Receipt Info. parameters
671 p_receipt_number => NULL,
672 p_cash_receipt_id => p_cash_receipt_id,
673 p_trx_number => NULL,
674 p_customer_trx_id => p_customer_trx_id,
675 p_installment => NULL,
676 p_applied_payment_schedule_id => l_payment_schedule_id,
677 p_receivable_application_id => NULL,
678 p_reversal_gl_date => NULL,
679 p_called_from => NULL,
680 p_cancel_claim_flag => 'N'
681 );
682 IF l_return_status = FND_API.g_ret_sts_error THEN
683 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
684 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_UNAPP_ERR');
685 FND_MSG_PUB.add;
686 END IF;
687 RAISE FND_API.g_exc_error;
688 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
689 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
690 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_UNAPP_UERR');
691 FND_MSG_PUB.add;
692 END IF;
693 RAISE FND_API.g_exc_unexpected_error;
694 END IF;
695
696 IF g_debug THEN
697 OZF_Utility_PVT.debug_message(l_full_name||': end');
698 END IF;
699 EXCEPTION
700 WHEN FND_API.g_exc_error THEN
701 x_return_status := FND_API.g_ret_sts_error;
702
703 WHEN FND_API.g_exc_unexpected_error THEN
704 x_return_status := FND_API.g_ret_sts_unexp_error ;
705
706 WHEN OTHERS THEN
707 x_return_status := FND_API.g_ret_sts_unexp_error ;
708 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
709 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
710 END IF;
711
712 END Unapply_from_Receipt;
713
714
715 /*=======================================================================*
716 | PROCEDURE
717 | Apply_on_Receipt
718 |
719 | NOTES
720 |
721 | HISTORY
722 | 15-MAR-2002 mchang Create.
723 *=======================================================================*/
724 PROCEDURE Apply_on_Receipt(
725 p_cash_receipt_id IN NUMBER
726 ,p_receipt_number IN VARCHAR2
727 ,p_customer_trx_id IN NUMBER
728 ,p_trx_number IN VARCHAR2
729 ,p_new_applied_amount IN NUMBER
730 ,p_new_applied_from_amount IN NUMBER --4684931
731 ,p_comments IN VARCHAR2
732 ,p_payment_set_id IN NUMBER
733 ,p_application_ref_type IN VARCHAR2
734 ,p_application_ref_id IN NUMBER
735 ,p_application_ref_num IN VARCHAR2
736 ,p_secondary_application_ref_id IN NUMBER
737 ,p_application_ref_reason IN VARCHAR2
738 ,p_customer_reference IN VARCHAR2
739 ,p_apply_date IN DATE -- Fix for Bug 3091401. TM passes old apply date
740 ,p_claim_id IN NUMBER -- Added For Rule Based Settlement ER
741 ,x_return_status OUT NOCOPY VARCHAR2
742 ,x_msg_data OUT NOCOPY VARCHAR2
743 ,x_msg_count OUT NOCOPY NUMBER
744 )
745 IS
746 l_api_version CONSTANT NUMBER := 1.0;
747 l_api_name CONSTANT VARCHAR2(30) := 'Apply_on_Receipt';
748 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
749 l_return_status VARCHAR2(1);
750
751 l_payment_schedule_id NUMBER;
752 l_balance_amount NUMBER;
753
754 -- Added For Rule Based Settlement ER
755 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
756 l_claim_trx_id NUMBER;
757 l_payment_method VARCHAR2(60);
758
759 CURSOR csr_payment_schedule(cv_customer_trx_id IN NUMBER) IS
760 SELECT payment_schedule_id
761 FROM ar_payment_schedules
762 WHERE customer_trx_id = cv_customer_trx_id;
763
764 CURSOR csr_trx_balance(cv_customer_trx_id IN NUMBER) IS
765 SELECT ABS(amount_due_remaining)
766 FROM ar_payment_schedules
767 WHERE customer_trx_id = cv_customer_trx_id;
768
769 -- Added For Rule Based Settlement ER
770 CURSOR csr_claim_trx(cv_claim_id IN NUMBER) IS
771 SELECT source_object_id, payment_method -- For bug#9279072
772 FROM ozf_claims_all
773 WHERE claim_id = cv_claim_id;
774
775 CURSOR csr_rec_flex_flds (p_cash_receipt_id IN NUMBER, p_customer_trx_id IN NUMBER) IS
776 SELECT attribute_category
777 , attribute1
778 , attribute2
779 , attribute3
780 , attribute4
781 , attribute5
782 , attribute6
783 , attribute7
784 , attribute8
785 , attribute9
786 , attribute10
787 , attribute11
788 , attribute12
789 , attribute13
790 , attribute14
791 , attribute15
792 FROM ar_receivable_applications_all
793 WHERE cash_receipt_id = p_cash_receipt_id
794 AND applied_customer_trx_id = p_customer_trx_id
795 AND status = 'APP'
796 AND display = 'Y';
797
798 -- bug#9279072(+)
799 CURSOR csr_rec_dffs (p_claim_id IN NUMBER) IS
800 SELECT deduction_attribute1
801 , deduction_attribute2
802 , deduction_attribute3
803 , deduction_attribute4
804 , deduction_attribute5
805 , deduction_attribute6
806 , deduction_attribute7
807 , deduction_attribute8
808 , deduction_attribute9
809 , deduction_attribute10
810 , deduction_attribute11
811 , deduction_attribute12
812 , deduction_attribute13
813 , deduction_attribute14
814 , deduction_attribute15
815 FROM ozf_claims_all
816 WHERE claim_id = p_claim_id;
817 -- bug#9279072(-)
818
819
820 -- End For Rule Based Settlement ER
821
822
823 BEGIN
824 -------------------- initialize -----------------------
825 IF g_debug THEN
826 OZF_Utility_PVT.debug_message(l_full_name||': start');
827 END IF;
828
829 x_return_status := FND_API.g_ret_sts_success;
830
831 ------------------------ start -------------------------
832 -- CM/DM open balance checking
833 OPEN csr_trx_balance(p_customer_trx_id);
834 FETCH csr_trx_balance INTO l_balance_amount;
835 CLOSE csr_trx_balance;
836
837 IF ABS(p_new_applied_amount) > l_balance_amount THEN
838 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
839 FND_MESSAGE.set_name('OZF', 'OZF_SETL_TRX_BAL_ERR');
840 FND_MESSAGE.set_token('APPLY_AMT', p_new_applied_amount);
841 FND_MSG_PUB.add;
842 END IF;
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845
846 OPEN csr_payment_schedule(p_customer_trx_id);
847 FETCH csr_payment_schedule INTO l_payment_schedule_id;
848 CLOSE csr_payment_schedule;
849
850 IF g_debug THEN
851 OZF_Utility_PVT.debug_message('cash_receipt_id='||p_cash_receipt_id);
852 OZF_Utility_PVT.debug_message('receipt_number='||p_receipt_number);
853 OZF_Utility_PVT.debug_message('customer_trx_id='||p_customer_trx_id);
854 OZF_Utility_PVT.debug_message('trx_number='||p_trx_number);
855 OZF_Utility_PVT.debug_message('applied_payment_schedule_id='||l_payment_schedule_id);
856 OZF_Utility_PVT.debug_message('new_applied_amount='||p_new_applied_amount);
857 OZF_Utility_PVT.debug_message('new_applied_from_amount='||p_new_applied_from_amount); --4684931
858 OZF_Utility_PVT.debug_message('Claim_ID='||p_claim_id); --4684931
859 END IF;
860
861 OPEN csr_claim_trx(p_claim_id);
862 FETCH csr_claim_trx INTO l_claim_trx_id, l_payment_method;
863 CLOSE csr_claim_trx;
864
865 IF g_debug THEN
866 OZF_Utility_PVT.debug_message('Claim Transaction Number ='||l_claim_trx_id);
867 OZF_Utility_PVT.debug_message('Claim Payment Method ='||l_payment_method);
868 END IF;
869
870
871 IF l_payment_method IS NOT NULL
872 AND l_payment_method IN ('PREV_OPEN_DEBIT', 'DEBIT_MEMO') THEN
873
874 -- bug#9279072(+)
875 OPEN csr_rec_dffs(p_claim_id);
876 FETCH csr_rec_dffs INTO l_attribute_rec.attribute1
877 , l_attribute_rec.attribute2
878 , l_attribute_rec.attribute3
879 , l_attribute_rec.attribute4
880 , l_attribute_rec.attribute5
881 , l_attribute_rec.attribute6
882 , l_attribute_rec.attribute7
883 , l_attribute_rec.attribute8
884 , l_attribute_rec.attribute9
885 , l_attribute_rec.attribute10
886 , l_attribute_rec.attribute11
887 , l_attribute_rec.attribute12
888 , l_attribute_rec.attribute13
889 , l_attribute_rec.attribute14
890 , l_attribute_rec.attribute15;
891 CLOSE csr_rec_dffs;
892
893 ELSE
894 -- Added For Rule Based Settlement ER
895 OPEN csr_rec_flex_flds(p_cash_receipt_id,l_claim_trx_id);
896 FETCH csr_rec_flex_flds INTO l_attribute_rec.attribute_category
897 , l_attribute_rec.attribute1
898 , l_attribute_rec.attribute2
899 , l_attribute_rec.attribute3
900 , l_attribute_rec.attribute4
901 , l_attribute_rec.attribute5
902 , l_attribute_rec.attribute6
903 , l_attribute_rec.attribute7
904 , l_attribute_rec.attribute8
905 , l_attribute_rec.attribute9
906 , l_attribute_rec.attribute10
907 , l_attribute_rec.attribute11
908 , l_attribute_rec.attribute12
909 , l_attribute_rec.attribute13
910 , l_attribute_rec.attribute14
911 , l_attribute_rec.attribute15;
912 CLOSE csr_rec_flex_flds;
913 END IF;
914
915 IF g_debug THEN
916 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute1 = '||l_attribute_rec.attribute1);
917 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute2 = '||l_attribute_rec.attribute2);
918 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute3 = '||l_attribute_rec.attribute3);
919 END IF;
920 -- bug#9279072(-)
921
922 IF g_debug THEN
923 OZF_Utility_PVT.debug_message('p_comments ='||p_comments);
924 OZF_Utility_PVT.debug_message('p_payment_set_id='||p_payment_set_id);
925 OZF_Utility_PVT.debug_message('p_application_ref_type='||p_application_ref_type);
926 OZF_Utility_PVT.debug_message('p_application_ref_id='||p_application_ref_id);
927 OZF_Utility_PVT.debug_message('p_application_ref_num='||p_application_ref_num);
928 OZF_Utility_PVT.debug_message('p_secondary_application_ref_id='||p_secondary_application_ref_id);
929 OZF_Utility_PVT.debug_message('p_application_ref_reason='||p_application_ref_reason); --4684931
930 OZF_Utility_PVT.debug_message('p_customer_reference='||p_customer_reference); --4684931
931 OZF_Utility_PVT.debug_message('l_attribute_rec.attribute3 = '||l_attribute_rec.attribute3); --4684931
932 END IF;
933
934
935
936
937 AR_RECEIPT_API_COVER.Apply(
938 -- Standard API parameters.
939 p_api_version => l_api_version,
940 p_init_msg_list => FND_API.g_false,
941 p_commit => FND_API.g_false,
942 p_validation_level => FND_API.g_valid_level_full,
943 x_return_status => l_return_status,
944 x_msg_count => x_msg_count,
945 x_msg_data => x_msg_data,
946 -- Receipt application parameters.
947 p_cash_receipt_id => p_cash_receipt_id,
948 p_receipt_number => p_receipt_number, --NULL,
949 p_customer_trx_id => p_customer_trx_id,
950 p_trx_number => p_trx_number, --NULL,
951 p_installment => NULL,
952 p_applied_payment_schedule_id => NULL, --l_payment_schedule_id,
953 p_amount_applied => p_new_applied_amount,
954 p_amount_applied_from => p_new_applied_from_amount,--4684931
955 p_apply_date => p_apply_date, -- Fix for Bug 3091401. TM passes old apply date
956 -- this is the allocated receipt amount
957 /*
958 p_amount_applied_from IN ar_receivable_applications.amount_applied_from%TYPE DEFAULT NULL,
959 p_trans_to_receipt_rate IN ar_receivable_applications.trans_to_receipt_rate%TYPE DEFAULT NULL,
960 p_discount IN ar_receivable_applications.earned_discount_taken%TYPE DEFAULT NULL,
961 p_apply_date IN ar_receivable_applications.apply_date%TYPE DEFAULT NULL,
962 p_apply_gl_date IN ar_receivable_applications.gl_date%TYPE DEFAULT NULL,
963 p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
964 p_customer_trx_line_id IN ar_receivable_applications.applied_customer_trx_line_id%TYPE DEFAULT NULL,
965 p_line_number IN ra_customer_trx_lines.line_number%TYPE DEFAULT NULL,
966 p_show_closed_invoices IN VARCHAR2 DEFAULT 'FALSE',
967 p_called_from IN VARCHAR2 DEFAULT NULL,
968 p_move_deferred_tax IN VARCHAR2 DEFAULT 'Y',
969 p_link_to_trx_hist_id IN ar_receivable_applications.link_to_trx_hist_id%TYPE DEFAULT NULL,
970 p_attribute_rec IN attribute_rec_type DEFAULT attribute_rec_const,
971 */
972 -- ******* Global Flexfield parameters *******
973 --p_global_attribute_rec => p_global_attribute_rec,
974 -- Added For Rule Based Settlement ER
975 p_attribute_rec => l_attribute_rec,
976 p_comments => p_comments,
977 p_payment_set_id => p_payment_set_id,
978 p_application_ref_type => p_application_ref_type,
979 p_application_ref_id => p_application_ref_id,
980 p_application_ref_num => p_application_ref_num,
981 p_secondary_application_ref_id => p_secondary_application_ref_id,
982 p_application_ref_reason => p_application_ref_reason,
983 p_customer_reference => p_customer_reference,
984 p_called_from => 'OZFAPI' --Fix for bug 13560662
985 );
986 IF l_return_status = FND_API.g_ret_sts_error THEN
987 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
988 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_APP_ERR');
989 FND_MSG_PUB.add;
990 END IF;
991 RAISE FND_API.g_exc_error;
992 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
993 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
994 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_APP_UERR');
995 FND_MSG_PUB.add;
996 END IF;
997 RAISE FND_API.g_exc_unexpected_error;
998 END IF;
999
1000 IF g_debug THEN
1001 OZF_Utility_PVT.debug_message(l_full_name||': end');
1002 END IF;
1003 EXCEPTION
1004 WHEN FND_API.g_exc_error THEN
1005 x_return_status := FND_API.g_ret_sts_error;
1006
1007 WHEN FND_API.g_exc_unexpected_error THEN
1008 x_return_status := FND_API.g_ret_sts_unexp_error ;
1009
1010 WHEN OTHERS THEN
1011 x_return_status := FND_API.g_ret_sts_unexp_error ;
1012 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1013 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1014 END IF;
1015
1016 END Apply_on_Receipt;
1017
1018
1019 /*=======================================================================*
1020 | PROCEDURE
1021 | Update_Dispute_Amount
1022 |
1023 | NOTES
1024 |
1025 | HISTORY
1026 | 15-MAR-2002 mchang Create.
1027 *=======================================================================*/
1028 PROCEDURE Update_dispute_amount(
1029 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1030 ,p_dispute_amount IN NUMBER
1031 ,x_return_status OUT NOCOPY VARCHAR2
1032 ,x_msg_data OUT NOCOPY VARCHAR2
1033 ,x_msg_count OUT NOCOPY NUMBER
1034 )
1035 IS
1036 l_api_version CONSTANT NUMBER := 1.0;
1037 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dispute_Amount';
1038 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1039 l_return_status VARCHAR2(1);
1040
1041 l_root_claim_number VARCHAR2(30);
1042
1043 CURSOR csr_root_claim_number(cv_root_claim_id IN NUMBER) IS
1044 SELECT claim_number
1045 FROM ozf_claims
1046 WHERE claim_id = cv_root_claim_id;
1047
1048 BEGIN
1049 -------------------- initialize -----------------------
1050 IF g_debug THEN
1051 OZF_Utility_PVT.debug_message(l_full_name||': start');
1052 END IF;
1053
1054 x_return_status := FND_API.g_ret_sts_success;
1055
1056 ------------------------ start -------------------------
1057 IF p_claim_rec.claim_id = p_claim_rec.root_claim_id THEN
1058 l_root_claim_number := p_claim_rec.claim_number;
1059 ELSE
1060 OPEN csr_root_claim_number(p_claim_rec.root_claim_id);
1061 FETCH csr_root_claim_number INTO l_root_claim_number;
1062 CLOSE csr_root_claim_number;
1063 END IF;
1064
1065 ARP_DEDUCTION_COVER.update_amount_in_dispute(
1066 p_customer_trx_id => p_claim_rec.source_object_id,
1067 p_claim_number => l_root_claim_number,
1068 p_amount => p_dispute_amount,
1069 p_init_msg_list => FND_API.g_false,
1070 x_return_status => l_return_status,
1071 x_msg_count => x_msg_count,
1072 x_msg_data => x_msg_data
1073 );
1074 IF l_return_status = FND_API.g_ret_sts_error THEN
1075 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1076 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_UPD_DISPUTE_ERR');
1077 FND_MSG_PUB.add;
1078 END IF;
1079 RAISE FND_API.g_exc_error;
1080 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1081 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1082 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_UPD_DISPUTE_UERR');
1083 FND_MSG_PUB.add;
1084 END IF;
1085 RAISE FND_API.g_exc_unexpected_error;
1086 END IF;
1087
1088 IF g_debug THEN
1089 OZF_Utility_PVT.debug_message(l_full_name||': end');
1090 END IF;
1091 EXCEPTION
1092 WHEN FND_API.g_exc_error THEN
1093 x_return_status := FND_API.g_ret_sts_error;
1094
1095 WHEN FND_API.g_exc_unexpected_error THEN
1096 x_return_status := FND_API.g_ret_sts_unexp_error ;
1097
1098 WHEN OTHERS THEN
1099 x_return_status := FND_API.g_ret_sts_unexp_error ;
1100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1101 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1102 END IF;
1103
1104 END Update_Dispute_Amount;
1105
1106
1107 /*=======================================================================*
1108 | PROCEDURE
1109 | Create_AR_Credit_Memo
1110 |
1111 | NOTES
1112 |
1113 | HISTORY
1114 | 15-MAR-2002 mchang Create.
1115 *=======================================================================*/
1116 PROCEDURE Create_AR_Credit_Memo(
1117 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1118 ,p_customer_trx_id IN NUMBER
1119 ,p_deduction_type IN VARCHAR2
1120 ,p_line_remaining IN NUMBER
1121 ,p_tax_remaining IN NUMBER
1122 ,p_freight_remaining IN NUMBER
1123 ,p_line_credit IN NUMBER
1124 ,p_tax_credit IN NUMBER
1125 ,p_freight_credit IN NUMBER
1126 ,p_total_credit IN NUMBER
1127 ,p_cm_line_tbl IN AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE
1128 ,p_upd_dispute_flag IN VARCHAR2
1129 ,x_cm_customer_trx_id OUT NOCOPY NUMBER
1130 ,x_cm_amount OUT NOCOPY NUMBER
1131 ,x_return_status OUT NOCOPY VARCHAR2
1132 ,x_msg_data OUT NOCOPY VARCHAR2
1133 ,x_msg_count OUT NOCOPY NUMBER
1134 )
1135 IS
1136 l_api_version CONSTANT NUMBER := 1.0;
1137 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Credit_Memo';
1138 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1139 l_return_status VARCHAR2(1);
1140
1141 l_batch_source_name VARCHAR2(50);
1142 l_reason_code VARCHAR2(30);
1143 l_request_id NUMBER;
1144 l_cm_line_tbl AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE;
1145 l_line_credit_flag VARCHAR2(1) := 'N';
1146 l_line_amount NUMBER;
1147 l_tax_amount NUMBER;
1148 l_freight_amount NUMBER;
1149
1150 l_inv_line_amount NUMBER;
1151 l_inv_tax_amount NUMBER;
1152 l_inv_freight_amount NUMBER;
1153
1154 l_total_credit NUMBER;
1155 l_root_claim_number VARCHAR2(30);
1156 l_credit_installments VARCHAR2(30);
1157 l_credit_rules VARCHAR2(30);
1158
1159 l_x_status_meaning VARCHAR2(60);
1160 l_x_reason_meaning VARCHAR2(60);
1161 l_x_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
1162 --l_x_cm_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE:
1163 l_x_line_amount RA_CM_REQUESTS.line_amount%TYPE;
1164 l_x_tax_amount RA_CM_REQUESTS.tax_amount%TYPE;
1165 l_x_freight_amount RA_CM_REQUESTS.freight_amount%TYPE;
1166 l_x_line_credits_flag VARCHAR2(1);
1167 l_x_created_by WF_USERS.display_name%TYPE;
1168 l_x_creation_date DATE;
1169 l_x_approval_date DATE;
1170 l_x_comments RA_CM_REQUESTS.comments%TYPE;
1171 l_x_cm_line_tbl AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE;
1172 l_x_cm_activity_tbl AR_CREDIT_MEMO_API_PUB.x_cm_activity_tbl%TYPE;
1173 l_x_cm_notes_tbl AR_CREDIT_MEMO_API_PUB.x_cm_notes_tbl%TYPE;
1174 l_attribute_rec arw_cmreq_cover.pq_attribute_rec_type;
1175
1176 CURSOR csr_batch_source(cv_set_of_books_id IN NUMBER) IS
1177 SELECT name
1178 FROM ra_batch_sources bs
1179 , ozf_sys_parameters sys
1180 WHERE sys.batch_source_id = bs.batch_source_id
1181 AND sys.set_of_books_id = cv_set_of_books_id;
1182
1183 CURSOR csr_reason_code(cv_reason_code_id IN NUMBER) IS
1184 SELECT reason_code
1185 FROM ozf_reason_codes_all_b
1186 WHERE reason_code_id = cv_reason_code_id;
1187
1188 CURSOR csr_root_claim_number(cv_root_claim_id IN NUMBER) IS
1189 SELECT claim_number
1190 FROM ozf_claims
1191 WHERE claim_id = cv_root_claim_id;
1192
1193 CURSOR csr_credit_amount(cv_customer_trx_id IN NUMBER) IS
1194 SELECT SUM(NVL(amount_line_items_remaining, 0))
1195 , SUM(NVL(tax_remaining, 0))
1196 , SUM(NVL(freight_remaining, 0))
1197 FROM ar_payment_schedules
1198 WHERE customer_trx_id = cv_customer_trx_id;
1199
1200 --Bugfix 8452740: Added flexfields to pass information from TM to AR
1201 CURSOR csr_claim_flex_flds (cv_cust_trx_id NUMBER) IS
1202 SELECT attribute_category
1203 , attribute1
1204 , attribute2
1205 , attribute3
1206 , attribute4
1207 , attribute5
1208 , attribute6
1209 , attribute7
1210 , attribute8
1211 , attribute9
1212 , attribute10
1213 , attribute11
1214 , attribute12
1215 , attribute13
1216 , attribute14
1217 , attribute15
1218 FROM ra_customer_trx
1219 WHERE customer_trx_id = cv_cust_trx_id;
1220
1221 -- Fix for Bug#11741663
1222 CURSOR csr_get_interface_attr_dtls (cv_claim_id IN NUMBER) IS
1223 SELECT oc.customer_reason, oc.customer_ref_number,
1224 oc.reason_code_id, osp.post_to_gl,
1225 (SELECT SUBSTRB(name,1,30) FROM ozf_reason_codes_vl WHERE reason_code_id = oc.reason_code_id) as name
1226 FROM ozf_sys_parameters_all osp,
1227 ozf_claims_all oc
1228 WHERE
1229 NVL(osp.org_id, -99) = NVL(oc.org_id, -99)
1230 AND oc.claim_id = cv_claim_id;
1231 l_post_to_gl VARCHAR2(1);
1232 l_reason_name VARCHAR2(30);
1233 l_reason_code_id NUMBER;
1234 l_customer_ref_number VARCHAR2(100);
1235 l_customer_reason VARCHAR2(30);
1236 l_interface_attribute_rec arw_cmreq_cover.pq_interface_rec_type;
1237
1238 BEGIN
1239 -------------------- initialize -----------------------
1240 IF g_debug THEN
1241 OZF_Utility_PVT.debug_message(l_full_name||': start');
1242 END IF;
1243
1244 x_return_status := FND_API.g_ret_sts_success;
1245 IF g_debug THEN
1246 OZF_Utility_PVT.debug_message(l_full_name||': p_total_credit = '|| p_total_credit);
1247 OZF_Utility_PVT.debug_message(l_full_name||': p_line_credit = '|| p_line_credit);
1248 OZF_Utility_PVT.debug_message(l_full_name||': p_tax_credit = '|| p_tax_credit);
1249 OZF_Utility_PVT.debug_message(l_full_name||': p_freight_credit = '|| p_freight_credit);
1250 OZF_Utility_PVT.debug_message(l_full_name||': p_cm_line_tbl count = '|| p_cm_line_tbl.COUNT);
1251 END IF;
1252 ------------------------ start -------------------------
1253 /* Logic Changed for Bug3963604 */
1254 IF p_total_credit = 0 AND p_line_credit = 0 AND
1255 p_tax_credit = 0 AND p_freight_credit = 0 AND p_cm_line_tbl IS NOT NULL THEN
1256 -- Line Level Credit Memo
1257 l_cm_line_tbl := p_cm_line_tbl;
1258 l_line_amount := NULL;
1259 l_tax_amount := NULL;
1260 l_freight_amount := NULL;
1261 l_line_credit_flag := 'Y';
1262 ELSE
1263 l_cm_line_tbl(1).customer_trx_line_id := NULL;
1264 l_cm_line_tbl(1).extended_amount := NULL;
1265 l_cm_line_tbl(1).quantity_credited :=NULL;
1266 l_cm_line_tbl(1).price := NULL;
1267 l_line_credit_flag := 'N';
1268
1269 IF p_total_credit = 0 AND
1270 (p_line_credit <> 0 OR p_tax_credit <> 0 OR p_freight_credit <> 0) THEN
1271 -- Header Level Credit Memo with Credit to Line/Tax/Freight
1272 l_line_amount := p_line_credit * -1;
1273 l_tax_amount := p_tax_credit * -1;
1274 l_freight_amount := p_freight_credit * -1;
1275 ELSE
1276 -- Header Level Credit Memo. Modified for Bug4308173
1277 OPEN csr_credit_amount(p_customer_trx_id);
1278 FETCH csr_credit_amount INTO l_inv_line_amount , l_inv_tax_amount , l_inv_freight_amount;
1279 CLOSE csr_credit_amount;
1280
1281 l_total_credit := p_total_credit;
1282 l_line_amount := LEAST(l_total_credit,l_inv_line_amount);
1283 l_total_credit := l_total_credit - l_line_amount;
1284
1285 IF l_total_credit > 0 THEN
1286 l_tax_amount := LEAST(l_total_credit,l_inv_tax_amount);
1287 l_total_credit := l_total_credit - l_tax_amount;
1288 END IF;
1289
1290 IF l_total_credit > 0 THEN
1291 l_freight_amount := LEAST(l_total_credit,l_inv_freight_amount);
1292 END IF;
1293
1294 l_line_amount := l_line_amount * -1;
1295 l_tax_amount := l_tax_amount * -1;
1296 l_freight_amount := l_freight_amount * -1;
1297 END IF;
1298 END IF;
1299
1300
1301 IF g_debug THEN
1302 OZF_Utility_PVT.debug_message(l_full_name||': request credit memo amount = '||p_claim_rec.amount_settled);
1303 OZF_Utility_PVT.debug_message(l_full_name||': request credit to line amount = '||l_line_amount);
1304 OZF_Utility_PVT.debug_message(l_full_name||': request credit to tax amount = '||l_tax_amount);
1305 OZF_Utility_PVT.debug_message(l_full_name||': request credit to freight amount = '||l_freight_amount);
1306 OZF_Utility_PVT.debug_message(l_full_name||': Line Level Credit = '||l_line_credit_flag);
1307 END IF;
1308
1309 OPEN csr_batch_source(p_claim_rec.set_of_books_id);
1310 FETCH csr_batch_source INTO l_batch_source_name;
1311 CLOSE csr_batch_source;
1312
1313 OPEN csr_reason_code(p_claim_rec.reason_code_id);
1314 FETCH csr_reason_code INTO l_reason_code;
1315 CLOSE csr_reason_code;
1316
1317
1318 l_credit_installments := NVL(FND_PROFILE.value('OZF_CLAIM_CREDIT_METHOD_INSTALLMENT'), 'PRORATE');
1319 l_credit_rules := NVL(FND_PROFILE.value('OZF_CLAIM_CREDIT_METHOD_RULE'), 'PRORATE');
1320
1321
1322
1323 --Bugfix 8452740: Added flexfields to pass information from TM to AR
1324 OPEN csr_claim_flex_flds(p_customer_trx_id);
1325 FETCH csr_claim_flex_flds INTO l_attribute_rec.attribute_category
1326 , l_attribute_rec.attribute1
1327 , l_attribute_rec.attribute2
1328 , l_attribute_rec.attribute3
1329 , l_attribute_rec.attribute4
1330 , l_attribute_rec.attribute5
1331 , l_attribute_rec.attribute6
1332 , l_attribute_rec.attribute7
1333 , l_attribute_rec.attribute8
1334 , l_attribute_rec.attribute9
1335 , l_attribute_rec.attribute10
1336 , l_attribute_rec.attribute11
1337 , l_attribute_rec.attribute12
1338 , l_attribute_rec.attribute13
1339 , l_attribute_rec.attribute14
1340 , l_attribute_rec.attribute15;
1341 CLOSE csr_claim_flex_flds;
1342
1343 -- Header field population for Bug#11741663
1344 l_interface_attribute_rec.interface_header_context := 'CLAIM';
1345 l_interface_attribute_rec.interface_header_attribute1 := p_claim_rec.claim_number;
1346
1347
1348
1349 OPEN csr_get_interface_attr_dtls(p_claim_rec.claim_id);
1350 FETCH csr_get_interface_attr_dtls INTO
1351 l_customer_reason, --customer reason
1352 l_customer_ref_number, --customer reference
1353 l_reason_code_id, --reason code id
1354 l_post_to_gl, --Post to GL
1355 l_reason_name; --reason code name
1356 CLOSE csr_get_interface_attr_dtls;
1357
1358
1359 l_interface_attribute_rec.interface_header_attribute5 := l_customer_ref_number;
1360 l_interface_attribute_rec.interface_header_attribute6 := l_customer_reason;
1361 l_interface_attribute_rec.interface_header_attribute7 := l_reason_name;
1362
1363 -- Header field population for Bug#11741663
1364
1365 /*------------------------------------------------------------*
1366 | 1. Create a credit memo in AR
1367 *------------------------------------------------------------*/
1368 AR_CREDIT_MEMO_API_PUB.create_request (
1369 -- standard API parameters
1370 p_api_version => l_api_version,
1371 p_init_msg_list => FND_API.G_FALSE,
1372 p_commit => FND_API.G_FALSE,
1373 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1374 x_return_status => l_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data,
1377 -- credit memo request parameters
1378 p_customer_trx_id => p_customer_trx_id,
1379 p_line_credit_flag => l_line_credit_flag,
1380 p_line_amount => l_line_amount,
1381 p_tax_amount => l_tax_amount,
1382 p_freight_amount => l_freight_amount,
1383 p_cm_reason_code => l_reason_code,
1384 --Fix for Bug#8531411
1385 --p_comments => SUBSTRB(p_claim_rec.comments, 1, 240),
1386 p_comments => SUBSTRB(p_claim_rec.comments, 1, 1760),
1387 p_orig_trx_number => NULL,--p_claim_rec.source_object_number,
1388 p_tax_ex_cert_num => NULL,
1389 p_request_url => NULL,
1390 p_transaction_url => NULL,
1391 p_trans_act_url => NULL,
1392 p_cm_line_tbl => l_cm_line_tbl,
1393 p_skip_workflow_flag => 'Y',
1394 p_credit_method_installments => l_credit_installments,
1395 p_credit_method_rules => l_credit_rules,
1396 p_batch_source_name => l_batch_source_name,
1397 p_attribute_rec => l_attribute_rec,
1398 p_interface_attribute_rec => l_interface_attribute_rec, -- Bug#11741663 -> header attributes
1399 x_request_id => l_request_id
1400 );
1401 IF l_return_status = FND_API.g_ret_sts_error THEN
1402 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1403 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CM_REQ_ERR');
1404 FND_MSG_PUB.add;
1405 END IF;
1406 RAISE FND_API.g_exc_error;
1407 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1409 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CM_REQ_UERR');
1410 FND_MSG_PUB.add;
1411 END IF;
1412 RAISE FND_API.g_exc_unexpected_error;
1413 END IF;
1414
1415 /*------------------------------------------------------------*
1416 | 2. Get creidt memo request information
1417 *------------------------------------------------------------*/
1418 AR_CREDIT_MEMO_API_PUB.get_request_status(
1419 -- standard API parameters
1420 p_api_version => l_api_version,
1421 p_init_msg_list => FND_API.G_false,
1422 x_msg_count => x_msg_count,
1423 x_msg_data => x_msg_data,
1424 x_return_status => l_return_status,
1425 -- credit memo request parameters
1426 p_request_id => l_request_id,
1427 x_status_meaning => l_x_status_meaning,
1428 x_reason_meaning => l_x_reason_meaning,
1429 x_customer_trx_id => l_x_customer_trx_id,
1430 x_cm_customer_trx_id => x_cm_customer_trx_id,
1431 x_line_amount => l_x_line_amount,
1432 x_tax_amount => l_x_tax_amount,
1433 x_freight_amount => l_x_freight_amount,
1434 x_line_credits_flag => l_x_line_credits_flag,
1435 x_created_by => l_x_created_by,
1436 x_creation_date => l_x_creation_date,
1437 x_approval_date => l_x_approval_date,
1438 x_comments => l_x_comments,
1439 x_cm_line_tbl => l_x_cm_line_tbl,
1440 x_cm_activity_tbl => l_x_cm_activity_tbl,
1441 x_cm_notes_tbl => l_x_cm_notes_tbl
1442 );
1443 IF l_return_status = FND_API.g_ret_sts_error THEN
1444 RAISE FND_API.g_exc_error;
1445 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1446 RAISE FND_API.g_exc_unexpected_error;
1447 END IF;
1448
1449 IF x_cm_customer_trx_id IS NULL THEN
1450 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1451 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CRE_ARCM_ERR');
1452 FND_MSG_PUB.add;
1453 END IF;
1454 RAISE FND_API.g_exc_error;
1455 -- [04/29/2002]: due to the rollback problem on ar credit memo api, instead of
1456 -- raising error to rollback, calling settlement workflow to proceed.
1457 BEGIN
1458 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
1459 p_claim_id => p_claim_rec.claim_id
1460 ,p_prev_status => 'OPEN' -- hard code
1461 ,p_curr_status => 'PENDING_CLOSE'
1462 ,p_next_status => 'CLOSED'
1463 );
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1467 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1468 FND_MSG_PUB.Add;
1469 RAISE FND_API.g_exc_unexpected_error;
1470 END;
1471 END IF;
1472
1473 x_cm_amount := l_x_line_amount + l_x_tax_amount + l_x_freight_amount;
1474
1475 IF g_debug THEN
1476 OZF_Utility_PVT.debug_message(l_full_name||': credit memo id => '||x_cm_customer_trx_id);
1477 OZF_Utility_PVT.debug_message(l_full_name||': credit memo amount => '||x_cm_amount);
1478 END IF;
1479
1480 --IF p_deduction_type = 'SOURCE_DED' THEN
1481 IF p_upd_dispute_flag = FND_API.g_true THEN
1482 /*------------------------------------------------------------*
1483 | 3. For Invoice Deduction only -> Taking invoice out of dispute
1484 *------------------------------------------------------------*/
1485 Update_dispute_amount(
1486 p_claim_rec => p_claim_rec
1487 ,p_dispute_amount => x_cm_amount
1488 ,x_return_status => l_return_status
1489 ,x_msg_data => x_msg_data
1490 ,x_msg_count => x_msg_count
1491 );
1492 IF l_return_status = FND_API.g_ret_sts_error THEN
1493 RAISE FND_API.g_exc_error;
1494 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1495 RAISE FND_API.g_exc_unexpected_error;
1496 END IF;
1497 END IF; -- end if p_deduction_type = 'SOURCE_DED'
1498
1499 IF g_debug THEN
1500 OZF_Utility_PVT.debug_message(l_full_name||': end');
1501 END IF;
1502 EXCEPTION
1503 WHEN FND_API.g_exc_error THEN
1504 x_return_status := FND_API.g_ret_sts_error;
1505
1506 WHEN FND_API.g_exc_unexpected_error THEN
1507 x_return_status := FND_API.g_ret_sts_unexp_error ;
1508
1509 WHEN OTHERS THEN
1510 x_return_status := FND_API.g_ret_sts_unexp_error ;
1511 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1512 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1513 END IF;
1514
1515 END Create_AR_Credit_Memo;
1516
1517
1518 /*=======================================================================*
1519 | PROCEDURE
1520 | Create_AR_Chargeback
1521 |
1522 | NOTES
1523 |
1524 | HISTORY
1525 | 15-MAR-2002 mchang Create.
1526 *=======================================================================*/
1527 PROCEDURE Create_AR_Chargeback(
1528 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1529 ,p_chargeback_amount IN NUMBER
1530 ,p_gl_date IN DATE
1531
1532 ,x_cb_customer_trx_id OUT NOCOPY NUMBER
1533 ,x_return_status OUT NOCOPY VARCHAR2
1534 ,x_msg_data OUT NOCOPY VARCHAR2
1535 ,x_msg_count OUT NOCOPY NUMBER
1536 )
1537 IS
1538 l_api_version CONSTANT NUMBER := 1.0;
1539 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Chargeback';
1540 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1541 l_return_status VARCHAR2(1);
1542
1543 l_chargeback_rec ARP_CHARGEBACK_COVER.Chargeback_Rec_Type;
1544 l_x_doc_seq_id NUMBER;
1545 l_x_doc_seq_value NUMBER;
1546 l_x_trx_number VARCHAR2(20);
1547 l_reason_type VARCHAR2(30);
1548 l_reason_code VARCHAR2(30);
1549 l_cb_trx_type_id NUMBER;
1550 l_check_inv_bal BOOLEAN;
1551 l_cb_ref_field VARCHAR2(80);
1552 l_reason_code_id NUMBER; --Bug:2781186
1553 l_gl_date_open_count NUMBER := 1;
1554
1555 -- Cust_trx_type_id for Chargeback
1556 CURSOR csr_cust_trx_type(cv_claim_type_id IN NUMBER) IS
1557 SELECT cb_trx_type_id
1558 FROM ozf_claim_types_vl
1559 WHERE claim_type_id = cv_claim_type_id;
1560
1561 CURSOR csr_sysparam_trx(cv_set_of_books_id IN NUMBER) IS
1562 --SELECT billback_trx_type_id --Modified by Padma as per 11.5.10 enhancements for system parameters.
1563 SELECT CB_TRX_TYPE_ID
1564 FROM ozf_sys_parameters
1565 WHERE set_of_books_id = cv_set_of_books_id;
1566
1567 -- R12 Enhancements
1568 CURSOR csr_reason_code(cv_reason_code_id IN NUMBER) IS
1569 SELECT invoicing_reason_code
1570 FROM ozf_reason_codes_all_b
1571 WHERE reason_code_id = cv_reason_code_id;
1572
1573 -- Cursor to get reference field value
1574 CURSOR csr_cb_ref_field IS
1575 SELECT default_reference
1576 FROM ra_batch_sources
1577 WHERE batch_source_id = 12;
1578
1579 --Start:Bug:2781186
1580 -- Cursor to get customer reason,customer ref, reason code id
1581 CURSOR csr_get_interface_attr_dtls (cv_claim_id IN NUMBER) IS
1582 SELECT customer_reason, customer_ref_number, reason_code_id
1583 --, customer_ref_date --11.5.10 enhancements - TM should pass
1584 -- Uncomment and Corresponding change for this has to be done while assigning to code after
1585 -- AR enhancement for this is done.
1586 FROM ozf_claims_all
1587 WHERE claim_id = cv_claim_id;
1588
1589 -- Cursor to get claim reason name
1590 CURSOR csr_get_reason_name (cv_reason_code_id IN NUMBER) IS
1591 SELECT SUBSTRB(name,1,30) name
1592 FROM ozf_reason_codes_vl
1593 WHERE reason_code_id = cv_reason_code_id;
1594 --End:Bug:2781186
1595
1596
1597 CURSOR csr_gl_date_open( p_set_of_books_id IN NUMBER
1598 , p_gl_date IN DATE
1599 ) IS
1600 SELECT DECODE(MAX(gl.period_name), '', 0, 1)
1601 FROM gl_period_statuses gl
1602 WHERE gl.application_id = 222
1603 AND gl.set_of_books_id = p_set_of_books_id
1604 AND gl.adjustment_period_flag = 'N'
1605 AND p_gl_date BETWEEN gl.start_date AND gl.end_date
1606 AND gl.closing_status IN ('O', 'F');
1607
1608
1609 BEGIN
1610 -------------------- initialize -----------------------
1611 IF g_debug THEN
1612 OZF_Utility_PVT.debug_message(l_full_name||': start');
1613 END IF;
1614
1615 x_return_status := FND_API.g_ret_sts_success;
1616
1617 ------------------------ start -------------------------
1618 OPEN csr_cust_trx_type(p_claim_rec.claim_type_id);
1619 FETCH csr_cust_trx_type INTO l_cb_trx_type_id;
1620 CLOSE csr_cust_trx_type;
1621
1622 IF l_cb_trx_type_id IS NULL THEN
1623 OPEN csr_sysparam_trx(p_claim_rec.set_of_books_id);
1624 FETCH csr_sysparam_trx INTO l_cb_trx_type_id;
1625 CLOSE csr_sysparam_trx;
1626 END IF;
1627
1628 IF l_cb_trx_type_id IS NULL THEN
1629 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1630 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CB_TRX_ID_REQ');
1631 FND_MSG_PUB.add;
1632 END IF;
1633 ELSE
1634 l_chargeback_rec.cust_trx_type_id := l_cb_trx_type_id;
1635 END IF;
1636
1637 OPEN csr_reason_code(p_claim_rec.reason_code_id);
1638 FETCH csr_reason_code INTO l_chargeback_rec.reason_code;
1639 CLOSE csr_reason_code;
1640
1641 l_chargeback_rec.amount := p_chargeback_amount;
1642 l_chargeback_rec.cash_receipt_id := p_claim_rec.receipt_id;
1643 l_chargeback_rec.secondary_application_ref_id := p_claim_rec.root_claim_id;
1644 l_chargeback_rec.new_second_application_ref_id := p_claim_rec.root_claim_id;
1645
1646 --11.5.10 Enhancements. TM passes only if AR period is Open (I.e. not when it's
1647 --Closed or Close Pending, in which cases AR will default)
1648 IF p_gl_date IS NULL OR
1649 p_gl_date = FND_API.g_miss_date THEN
1650 IF OZF_CLAIM_SETTLEMENT_VAL_PVT.gl_date_in_open(222, p_claim_rec.claim_id) THEN
1651 l_chargeback_rec.gl_date := p_claim_rec.gl_date;
1652 END IF;
1653 ELSE
1654 OPEN csr_gl_date_open(p_claim_rec.set_of_books_id, p_gl_date);
1655 FETCH csr_gl_date_open INTO l_gl_date_open_count;
1656 CLOSE csr_gl_date_open;
1657
1658 IF l_gl_date_open_count <> 0 THEN
1659 l_chargeback_rec.gl_date := p_gl_date;
1660 END IF;
1661 END IF;
1662
1663 --11.5.10 Enhancements. AR should default, TM Enh 2655917
1664 --l_chargeback_rec.due_date := p_claim_rec.due_date;
1665
1666 l_chargeback_rec.application_ref_type := 'CLAIM';
1667 -- [ BEGIN BUG246517 fixing 17-JUL-2002 ]: pass in bill to site id to chargeback rec.
1668 l_chargeback_rec.bill_to_site_use_id := p_claim_rec.cust_billto_acct_site_id;
1669 -- [ END BUG246517 fixing ]
1670
1671 -- [BEGIN of BUG2569355 fixing]: pass claim number to chargeback reference field
1672 OPEN csr_cb_ref_field;
1673 FETCH csr_cb_ref_field INTO l_cb_ref_field;
1674 CLOSE csr_cb_ref_field;
1675
1676 IF 1 = TO_NUMBER(l_cb_ref_field) THEN
1677 l_chargeback_rec.interface_header_attribute1 := p_claim_rec.claim_number;
1678 ELSIF 2 = TO_NUMBER(l_cb_ref_field) THEN
1679 l_chargeback_rec.interface_header_attribute2 := p_claim_rec.claim_number;
1680 ELSIF 3 = TO_NUMBER(l_cb_ref_field) THEN
1681 l_chargeback_rec.interface_header_attribute3 := p_claim_rec.claim_number;
1682 ELSIF 4 = TO_NUMBER(l_cb_ref_field) THEN
1683 l_chargeback_rec.interface_header_attribute4 := p_claim_rec.claim_number;
1684 ELSIF 5 = TO_NUMBER(l_cb_ref_field) THEN
1685 l_chargeback_rec.interface_header_attribute5 := p_claim_rec.claim_number;
1686 ELSIF 6 = TO_NUMBER(l_cb_ref_field) THEN
1687 l_chargeback_rec.interface_header_attribute6 := p_claim_rec.claim_number;
1688 ELSIF 7 = TO_NUMBER(l_cb_ref_field) THEN
1689 l_chargeback_rec.interface_header_attribute7 := p_claim_rec.claim_number;
1690 ELSIF 8 = TO_NUMBER(l_cb_ref_field) THEN
1691 l_chargeback_rec.interface_header_attribute8 := p_claim_rec.claim_number;
1692 ELSIF 9 = TO_NUMBER(l_cb_ref_field) THEN
1693 l_chargeback_rec.interface_header_attribute9 := p_claim_rec.claim_number;
1694 ELSIF 10 = TO_NUMBER(l_cb_ref_field) THEN
1695 l_chargeback_rec.interface_header_attribute10 := p_claim_rec.claim_number;
1696 ELSIF 11 = TO_NUMBER(l_cb_ref_field) THEN
1697 l_chargeback_rec.interface_header_attribute11 := p_claim_rec.claim_number;
1698 ELSIF 12 = TO_NUMBER(l_cb_ref_field) THEN
1699 l_chargeback_rec.interface_header_attribute12 := p_claim_rec.claim_number;
1700 ELSIF 13 = TO_NUMBER(l_cb_ref_field) THEN
1701 l_chargeback_rec.interface_header_attribute13 := p_claim_rec.claim_number;
1702 ELSIF 14 = TO_NUMBER(l_cb_ref_field) THEN
1703 l_chargeback_rec.interface_header_attribute14 := p_claim_rec.claim_number;
1704 ELSIF 15 = TO_NUMBER(l_cb_ref_field) THEN
1705 l_chargeback_rec.interface_header_attribute15 := p_claim_rec.claim_number;
1706 END IF;
1707 -- [END of BUG2569355 fixing]
1708
1709 l_chargeback_rec.interface_header_context := 'CLAIM';
1710 l_chargeback_rec.interface_header_attribute1 := p_claim_rec.claim_number;
1711
1712 OPEN csr_get_interface_attr_dtls(p_claim_rec.claim_id);
1713 FETCH csr_get_interface_attr_dtls INTO l_chargeback_rec.interface_header_attribute6, --customer reason
1714 l_chargeback_rec.interface_header_attribute5, --customer reference
1715 l_reason_code_id; --reason code id
1716 CLOSE csr_get_interface_attr_dtls;
1717
1718 OPEN csr_get_reason_name(l_reason_code_id);
1719 FETCH csr_get_reason_name INTO l_chargeback_rec.interface_header_attribute7; --reason name
1720 CLOSE csr_get_reason_name;
1721
1722 --Pass customer reference to separate field customer_reference in chargeback_rec.
1723 l_chargeback_rec.CUSTOMER_REFERENCE := l_chargeback_rec.interface_header_attribute5;
1724
1725 --Pass deduction attributes.
1726 l_chargeback_rec.attribute_category := p_claim_rec.deduction_attribute_category;
1727 l_chargeback_rec.attribute1 := p_claim_rec.deduction_attribute1;
1728 l_chargeback_rec.attribute2 := p_claim_rec.deduction_attribute2;
1729 l_chargeback_rec.attribute3 := p_claim_rec.deduction_attribute3;
1730 l_chargeback_rec.attribute4 := p_claim_rec.deduction_attribute4;
1731 l_chargeback_rec.attribute5 := p_claim_rec.deduction_attribute5;
1732 l_chargeback_rec.attribute6 := p_claim_rec.deduction_attribute6;
1733 l_chargeback_rec.attribute7 := p_claim_rec.deduction_attribute7;
1734 l_chargeback_rec.attribute8 := p_claim_rec.deduction_attribute8;
1735 l_chargeback_rec.attribute9 := p_claim_rec.deduction_attribute9;
1736 l_chargeback_rec.attribute10 := p_claim_rec.deduction_attribute10;
1737 l_chargeback_rec.attribute11 := p_claim_rec.deduction_attribute11;
1738 l_chargeback_rec.attribute12 := p_claim_rec.deduction_attribute12;
1739 l_chargeback_rec.attribute13 := p_claim_rec.deduction_attribute13;
1740 l_chargeback_rec.attribute14 := p_claim_rec.deduction_attribute14;
1741 l_chargeback_rec.attribute15 := p_claim_rec.deduction_attribute15;
1742
1743 --Pass Comments
1744 -- Fix for Bug#8531411
1745 --l_chargeback_rec.comments := SUBSTRB(p_claim_rec.comments,1,240);
1746 l_chargeback_rec.comments := SUBSTRB(p_claim_rec.comments,1,1760);
1747
1748 -- Pass LE
1749 l_chargeback_rec.legal_entity_id := p_claim_rec.legal_entity_id;
1750
1751 IF g_debug THEN
1752 OZF_Utility_PVT.debug_message(l_full_name||': chargeback amount='||l_chargeback_rec.amount);
1753 END IF;
1754
1755 ARP_CHARGEBACK_COVER.create_chargeback (
1756 p_chargeback_rec => l_chargeback_rec,
1757 p_init_msg_list => FND_API.g_false,
1758 x_doc_sequence_id => l_x_doc_seq_id,
1759 x_doc_sequence_value => l_x_doc_seq_value,
1760 x_trx_number => l_x_trx_number,
1761 x_customer_trx_id => x_cb_customer_trx_id,
1762 x_return_status => l_return_status,
1763 x_msg_count => x_msg_count,
1764 x_msg_data => x_msg_data
1765 );
1766 IF l_return_status = FND_API.g_ret_sts_error THEN
1767 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1768 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CRE_CB_ERR');
1769 FND_MSG_PUB.add;
1770 END IF;
1771 RAISE FND_API.g_exc_error;
1772 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1773 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1774 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CRE_CB_UERR');
1775 FND_MSG_PUB.add;
1776 END IF;
1777 RAISE FND_API.g_exc_unexpected_error;
1778 END IF;
1779
1780 IF g_debug THEN
1781 OZF_Utility_PVT.debug_message(l_full_name||': end');
1782 END IF;
1783 EXCEPTION
1784 WHEN FND_API.g_exc_error THEN
1785 x_return_status := FND_API.g_ret_sts_error;
1786
1787 WHEN FND_API.g_exc_unexpected_error THEN
1788 x_return_status := FND_API.g_ret_sts_unexp_error ;
1789
1790 WHEN OTHERS THEN
1791 x_return_status := FND_API.g_ret_sts_unexp_error ;
1792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1793 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1794 END IF;
1795
1796 END Create_AR_Chargeback;
1797
1798
1799 /*=======================================================================*
1800 | PROCEDURE
1801 | Create_AR_Write_Off
1802 |
1803 | NOTES
1804 |
1805 | HISTORY
1806 | 15-MAR-2002 mchang Create.
1807 | 17-Oct-2008 ateotia bug # 7484916 fixed.
1808 | FP:11510-R12 7371116 - OZF_AR_PAYMENT_PUT.CREATE_AR_WRITE_OFF ERRORS OUT
1809 *=======================================================================*/
1810 PROCEDURE Create_AR_Write_Off(
1811 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1812 ,p_deduction_type IN VARCHAR2
1813 ,p_write_off_amount IN NUMBER
1814 ,p_gl_date IN DATE
1815 ,p_wo_rec_trx_id IN NUMBER
1816 ,p_settlement_doc_id IN NUMBER
1817 ,x_wo_adjust_id OUT NOCOPY NUMBER
1818 ,x_return_status OUT NOCOPY VARCHAR2
1819 ,x_msg_data OUT NOCOPY VARCHAR2
1820 ,x_msg_count OUT NOCOPY NUMBER
1821 )
1822 IS
1823 l_api_version CONSTANT NUMBER := 1.0;
1824 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Write_Off';
1825 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1826 l_return_status VARCHAR2(1);
1827
1828 l_receivables_trx_id NUMBER := NULL;
1829 l_adj_rec AR_ADJUSTMENTS%ROWTYPE;
1830 l_x_new_adjust_number VARCHAR2(20);
1831 l_payment_schedule_id NUMBER;
1832 l_asso_rec_app_id NUMBER;
1833 l_reason_code VARCHAR2(30);
1834 l_root_claim_number VARCHAR2(30);
1835
1836 l_application_ref_num VARCHAR2(30);
1837 l_secondary_appl_ref_id NUMBER;
1838 l_customer_reference VARCHAR2(30);
1839
1840 l_adj_rec_trx_id NUMBER;
1841 l_wo_rec_trx_id NUMBER;
1842 l_neg_wo_rec_trx_id NUMBER;
1843 l_sp_adj_rec_trx_id NUMBER;
1844 l_sp_wo_rec_trx_id NUMBER;
1845 l_sp_neg_wo_rec_trx_id NUMBER;
1846
1847 l_amt_line_items_rem NUMBER;
1848 l_tax_remaining NUMBER;
1849 l_freight_remaining NUMBER;
1850 l_amount_due_remaining NUMBER;
1851 l_rem_amount NUMBER;
1852 l_idx NUMBER := 1;
1853 t_adj_rec AR_ADJUSTMENTS%ROWTYPE;
1854 l_x_wo_adjust_id NUMBER;
1855
1856 TYPE writeoff_dtls_type IS RECORD (
1857 type VARCHAR2(15),
1858 writeoff_amount NUMBER
1859 );
1860
1861 TYPE writeoff_dtls_tab IS TABLE OF writeoff_dtls_type
1862 INDEX BY BINARY_INTEGER;
1863
1864 l_writeoff_dtls writeoff_dtls_tab;
1865
1866 -- associated receivable application is
1867 CURSOR csr_ar_rec_application( cv_cash_receipt_id IN NUMBER
1868 , cv_customer_trx_id IN NUMBER
1869 , cv_claim_id IN NUMBER
1870 ) IS
1871 SELECT receivable_application_id
1872 , applied_payment_schedule_id
1873 FROM ar_receivable_applications
1874 WHERE cash_receipt_id = cv_cash_receipt_id
1875 AND applied_customer_trx_id = cv_customer_trx_id
1876 AND application_ref_type = 'CLAIM'
1877 ANd secondary_application_ref_id = cv_claim_id
1878 AND display = 'Y';
1879
1880 CURSOR csr_reason_code(cv_reason_code_id IN NUMBER) IS
1881 SELECT adjustment_reason_code
1882 FROM ozf_reason_codes_vl
1883 WHERE reason_code_id = cv_reason_code_id;
1884
1885 CURSOR csr_root_claim_number(cv_root_claim_id IN NUMBER) IS
1886 SELECT claim_number
1887 FROM ozf_claims
1888 WHERE claim_id = cv_root_claim_id;
1889
1890 CURSOR csr_claim_type_rec_trx(cv_claim_type_id IN NUMBER) IS
1891 SELECT adj_rec_trx_id
1892 , wo_rec_trx_id
1893 , neg_wo_rec_trx_id
1894 FROM ozf_claim_types_vl
1895 WHERE claim_type_id = cv_claim_type_id;
1896
1897 CURSOR csr_sys_param_rec_trx IS
1898 SELECT adj_rec_trx_id
1899 , wo_rec_trx_id
1900 , neg_wo_rec_trx_id
1901 FROM ozf_sys_parameters;
1902
1903 --Start:Bug:2781186
1904 -- Cursor to get customer reason,customer ref, reason code id
1905 CURSOR csr_get_more_root_clm_dtls (cv_claim_id IN NUMBER) IS
1906 SELECT claim_number, claim_id, customer_ref_number
1907 FROM ozf_claims_all
1908 WHERE claim_id = cv_claim_id;
1909
1910 --Cursor to get customer reason,customer ref, reason code id in case of child cliam
1911 CURSOR csr_get_more_chld_clm_dtls (cv_claim_id IN NUMBER) IS
1912 SELECT p.claim_number, p.claim_id, c.customer_ref_number
1913 FROM ozf_claims c , ozf_claims p
1914 WHERE c.claim_id = cv_claim_id
1915 AND c.root_claim_id = p.claim_id;
1916 --End:Bug:2781186
1917
1918 --Cursor to get amount details
1919 CURSOR csr_get_amount_dtls ( cv_cash_receipt_id IN NUMBER
1920 , cv_customer_trx_id IN NUMBER
1921 , cv_root_claim_id IN NUMBER ) IS
1922 SELECT pay.amount_due_remaining,
1923 pay.amount_line_items_remaining,
1924 pay.tax_remaining,
1925 pay.freight_remaining
1926 FROM ar_receivable_applications rec
1927 , ar_payment_schedules pay
1928 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
1929 AND rec.cash_receipt_id = cv_cash_receipt_id
1930 AND pay.customer_trx_id = cv_customer_trx_id
1931 AND rec.application_ref_type = 'CLAIM'
1932 AND rec.display = 'Y'
1933 AND rec.secondary_application_ref_id = cv_root_claim_id;
1934
1935 BEGIN
1936 -------------------- initialize -----------------------
1937 IF g_debug THEN
1938 OZF_Utility_PVT.debug_message(l_full_name||': start');
1939 END IF;
1940 x_return_status := FND_API.g_ret_sts_success;
1941
1942 --//Bug 5345095
1943 IF p_wo_rec_trx_id IS NULL THEN
1944 l_receivables_trx_id := p_claim_rec.wo_rec_trx_id;
1945 ELSE
1946 l_receivables_trx_id := p_wo_rec_trx_id;
1947 END IF;
1948
1949 IF l_receivables_trx_id IS NULL THEN
1950 OPEN csr_claim_type_rec_trx(p_claim_rec.claim_type_id);
1951 FETCH csr_claim_type_rec_trx INTO l_adj_rec_trx_id
1952 , l_wo_rec_trx_id
1953 , l_neg_wo_rec_trx_id;
1954 CLOSE csr_claim_type_rec_trx;
1955
1956 OPEN csr_sys_param_rec_trx;
1957 FETCH csr_sys_param_rec_trx INTO l_sp_adj_rec_trx_id
1958 , l_sp_wo_rec_trx_id
1959 , l_sp_neg_wo_rec_trx_id;
1960 CLOSE csr_sys_param_rec_trx;
1961
1962 l_adj_rec_trx_id := NVL(l_adj_rec_trx_id , l_sp_adj_rec_trx_id);
1963 l_wo_rec_trx_id := NVL(l_wo_rec_trx_id , l_sp_wo_rec_trx_id);
1964 l_neg_wo_rec_trx_id := NVL(l_neg_wo_rec_trx_id, l_sp_neg_wo_rec_trx_id);
1965 END IF;
1966
1967 IF g_debug THEN
1968 OZF_Utility_PVT.debug_message('p_deduction_type:' || p_deduction_type);
1969 END IF;
1970
1971 ------------------------ start -------------------------
1972 IF p_deduction_type IN ('SOURCE_DED','SOURCE_OPM') THEN
1973 /*------------------------------------------------------------*
1974 | Invoice Deduction -> 1. Create a negative adjustment.
1975 | 2. Take invoice out of dispute
1976 *------------------------------------------------------------*/
1977 -- 1. Create a negative adjustment
1978 OPEN csr_ar_rec_application( p_claim_rec.receipt_id
1979 , p_claim_rec.source_object_id
1980 , p_claim_rec.root_claim_id
1981 );
1982 FETCH csr_ar_rec_application INTO l_asso_rec_app_id
1983 , l_payment_schedule_id;
1984 CLOSE csr_ar_rec_application;
1985
1986 OPEN csr_reason_code(p_claim_rec.reason_code_id);
1987 FETCH csr_reason_code INTO l_reason_code;
1988 CLOSE csr_reason_code;
1989
1990 IF g_debug THEN
1991 OZF_Utility_PVT.debug_message('p_claim_rec.receipt_id:' || p_claim_rec.receipt_id);
1992 OZF_Utility_PVT.debug_message('p_claim_rec.source_object_id:' || p_claim_rec.source_object_id);
1993 OZF_Utility_PVT.debug_message('p_claim_rec.root_claim_id:' || p_claim_rec.root_claim_id);
1994 END IF;
1995
1996 OPEN csr_get_amount_dtls( p_claim_rec.receipt_id
1997 , p_claim_rec.source_object_id
1998 , p_claim_rec.root_claim_id
1999 );
2000 FETCH csr_get_amount_dtls INTO l_amount_due_remaining, l_amt_line_items_rem,
2001 l_tax_remaining, l_freight_remaining;
2002 CLOSE csr_get_amount_dtls;
2003
2004
2005 IF l_receivables_trx_id IS NULL THEN
2006 l_receivables_trx_id := l_adj_rec_trx_id;
2007 END IF;
2008
2009 l_adj_rec.payment_schedule_id := l_payment_schedule_id;
2010 l_adj_rec.amount := p_write_off_amount * -1;
2011 -- l_adj_customer_trx_line_id := -- for type other then 'INVOICE' only.
2012 l_adj_rec.receivables_trx_id := l_receivables_trx_id;
2013 l_adj_rec.apply_date := SYSDATE;
2014 IF p_gl_date IS NULL OR
2015 p_gl_date = FND_API.g_miss_date THEN
2016 l_adj_rec.gl_date := p_claim_rec.gl_date;
2017 ELSE
2018 l_adj_rec.gl_date := p_gl_date;
2019 END IF;
2020 l_adj_rec.reason_code := l_reason_code;
2021 --Fix for Bug#8531411
2022 --l_adj_rec.comments := SUBSTRB(p_claim_rec.comments, 1, 240);
2023 l_adj_rec.comments := SUBSTRB(p_claim_rec.comments, 1, 2000);
2024 l_adj_rec.associated_application_id := l_asso_rec_app_id;
2025 l_adj_rec.associated_cash_receipt_id := p_claim_rec.receipt_id;
2026 l_adj_rec.created_from := 'CLAIMS';
2027
2028
2029 IF l_amount_due_remaining = p_write_off_amount THEN
2030 l_writeoff_dtls(1).type := 'INVOICE';
2031 l_writeoff_dtls(1).writeoff_amount := p_write_off_amount;
2032 ELSE
2033
2034 l_rem_amount := p_write_off_amount;
2035 IF ((l_amt_line_items_rem > 0 AND p_deduction_type = 'SOURCE_DED')
2036 OR (l_amt_line_items_rem < 0 AND p_deduction_type = 'SOURCE_OPM')) THEN
2037 IF ((l_amt_line_items_rem >= l_rem_amount AND p_deduction_type = 'SOURCE_DED')
2038 OR (l_amt_line_items_rem <= l_rem_amount AND p_deduction_type = 'SOURCE_OPM')) THEN
2039 l_writeoff_dtls(l_idx).type := 'LINE';
2040 l_writeoff_dtls(l_idx).writeoff_amount := l_rem_amount;
2041 l_rem_amount := 0;
2042 ELSE
2043 l_writeoff_dtls(l_idx).type := 'LINE';
2044 l_writeoff_dtls(l_idx).writeoff_amount := l_amt_line_items_rem;
2045 l_rem_amount := l_rem_amount - l_amt_line_items_rem;
2046 END IF;
2047 l_idx := l_idx + 1;
2048 END IF;
2049
2050 IF ((l_rem_amount > 0 AND l_tax_remaining > 0 AND p_deduction_type = 'SOURCE_DED')
2051 OR (l_rem_amount < 0 AND l_tax_remaining < 0 AND p_deduction_type = 'SOURCE_OPM')) THEN
2052
2053 IF ((l_tax_remaining >= l_rem_amount AND p_deduction_type = 'SOURCE_DED')
2054 OR (l_tax_remaining <= l_rem_amount AND p_deduction_type = 'SOURCE_OPM')) THEN
2055 l_writeoff_dtls(l_idx).type := 'TAX';
2056 l_writeoff_dtls(l_idx).writeoff_amount := l_rem_amount;
2057 l_rem_amount := 0;
2058 ELSE
2059 l_writeoff_dtls(l_idx).type := 'TAX';
2060 l_writeoff_dtls(l_idx).writeoff_amount := l_tax_remaining;
2061 l_rem_amount := p_write_off_amount - l_tax_remaining;
2062 END IF;
2063 l_idx := l_idx + 1;
2064 END IF;
2065
2066 IF ((l_rem_amount > 0 AND l_freight_remaining > 0 AND p_deduction_type = 'SOURCE_DED')
2067 OR (l_rem_amount < 0 AND l_freight_remaining < 0 AND p_deduction_type = 'SOURCE_OPM')) THEN
2068 IF ((l_freight_remaining >= l_rem_amount AND p_deduction_type = 'SOURCE_DED')
2069 OR (l_freight_remaining <= l_rem_amount AND p_deduction_type = 'SOURCE_OPM')) THEN
2070 l_writeoff_dtls(l_idx).type := 'FREIGHT';
2071 l_writeoff_dtls(l_idx).writeoff_amount := l_rem_amount;
2072 l_rem_amount := 0;
2073 ELSE
2074 l_writeoff_dtls(l_idx).type := 'FREIGHT';
2075 l_writeoff_dtls(l_idx).writeoff_amount := l_freight_remaining;
2076 l_rem_amount := l_rem_amount - l_freight_remaining;
2077 END IF;
2078 END IF;
2079 END IF;
2080
2081 -- bug # 7484916 fixed by ateotia (+)
2082 --FOR l_idx IN l_writeoff_dtls.FIRST..l_writeoff_dtls.LAST LOOP
2083
2084 IF g_debug THEN
2085 OZF_Utility_PVT.debug_message('l_writeoff_dtls.COUNT:' || l_writeoff_dtls.COUNT);
2086 END IF;
2087
2088 IF (l_writeoff_dtls.COUNT > 0) THEN
2089 FOR rowCount IN l_writeoff_dtls.FIRST..l_writeoff_dtls.LAST LOOP
2090 t_adj_rec := l_adj_rec;
2091 t_adj_rec.type := l_writeoff_dtls(rowCount).type;
2092 t_adj_rec.amount := l_writeoff_dtls(rowCount).writeoff_amount * -1;
2093 -- bug # 7484916 fixed by ateotia (-)
2094 IF g_debug THEN
2095 OZF_Utility_PVT.debug_message('Before calling AR Adjustmnet API');
2096 OZF_Utility_PVT.debug_message('t_adj_rec.amount:' || t_adj_rec.amount);
2097 END IF;
2098
2099 AR_ADJUST_PUB.Create_Adjustment (
2100 p_api_name => 'AR_ADJUST_PUB.Create_Adjustment',
2101 p_api_version => l_api_version,
2102 p_init_msg_list => FND_API.g_false,
2103 p_commit_flag => FND_API.g_false,
2104 p_validation_level => FND_API.g_valid_level_full,
2105 p_msg_count => x_msg_count,
2106 p_msg_data => x_msg_data,
2107 p_return_status => l_return_status,
2108 p_adj_rec => t_adj_rec,
2109 p_chk_approval_limits => FND_API.g_false,
2110 p_check_amount => FND_API.g_true,
2111 p_move_deferred_tax => 'Y', --??
2112 p_new_adjust_number => l_x_new_adjust_number,
2113 p_new_adjust_id => l_x_wo_adjust_id,
2114 p_called_from => 'CLAIMS',
2115 p_old_adjust_id => NULL
2116 );
2117
2118 IF g_debug THEN
2119 OZF_Utility_PVT.debug_message('After calling AR Adjustmnet API l_return_status :' || l_return_status);
2120 OZF_Utility_PVT.debug_message('After calling AR Adjustmnet API l_x_new_adjust_number :' || l_x_new_adjust_number);
2121 OZF_Utility_PVT.debug_message('After calling AR Adjustmnet API l_x_wo_adjust_id :' || l_x_wo_adjust_id);
2122 END IF;
2123
2124 IF l_return_status = FND_API.g_ret_sts_error THEN
2125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2126 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_ADJ_ERR');
2127 FND_MSG_PUB.add;
2128 END IF;
2129 RAISE FND_API.g_exc_error;
2130 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2131 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2132 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_ADJ_UERR');
2133 FND_MSG_PUB.add;
2134 END IF;
2135 RAISE FND_API.g_exc_unexpected_error;
2136 END IF;
2137
2138 /*------------------------------------------------------------*
2139 | Update Deduction payment detail
2140 *------------------------------------------------------------*/
2141
2142 IF g_debug THEN
2143 OZF_Utility_PVT.debug_message('l_x_wo_adjust_id :' || l_x_wo_adjust_id);
2144 OZF_Utility_PVT.debug_message('p_claim_rec.payment_method :' || p_claim_rec.payment_method);
2145 OZF_Utility_PVT.debug_message('p_settlement_doc_id :' || p_settlement_doc_id);
2146 END IF;
2147
2148
2149 IF l_x_wo_adjust_id IS NOT NULL THEN
2150 -- Update Deduction payment detail
2151 -- Modified for Bug 13948201
2152 IF p_claim_rec.payment_method = 'MASS_SETTLEMENT'
2153 THEN
2154 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
2155 p_api_version => l_api_version
2156 ,p_init_msg_list => FND_API.g_false
2157 ,p_commit => FND_API.g_false
2158 ,p_validation_level => FND_API.g_valid_level_full
2159 ,x_return_status => l_return_status
2160 ,x_msg_data => x_msg_data
2161 ,x_msg_count => x_msg_count
2162 ,p_claim_id => p_claim_rec.claim_id
2163 ,p_payment_method => 'WRITE_OFF'
2164 ,p_deduction_type => p_deduction_type
2165 ,p_cash_receipt_id => p_claim_rec.receipt_id
2166 ,p_customer_trx_id => p_claim_rec.source_object_id
2167 ,p_adjust_id => l_x_wo_adjust_id
2168 ,p_settlement_doc_id => p_settlement_doc_id
2169 ,p_settlement_mode => 'MASS_SETTLEMENT'
2170 );
2171 ELSE
2172 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
2173 p_api_version => l_api_version
2174 ,p_init_msg_list => FND_API.g_false
2175 ,p_commit => FND_API.g_false
2176 ,p_validation_level => FND_API.g_valid_level_full
2177 ,x_return_status => l_return_status
2178 ,x_msg_data => x_msg_data
2179 ,x_msg_count => x_msg_count
2180 ,p_claim_id => p_claim_rec.claim_id
2181 ,p_payment_method => 'WRITE_OFF'
2182 ,p_deduction_type => p_deduction_type
2183 ,p_cash_receipt_id => p_claim_rec.receipt_id
2184 ,p_customer_trx_id => p_claim_rec.source_object_id
2185 ,p_adjust_id => l_x_wo_adjust_id
2186 );
2187 END IF;
2188
2189 IF l_return_status = FND_API.g_ret_sts_error THEN
2190 RAISE FND_API.g_exc_error;
2191 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2192 RAISE FND_API.g_exc_unexpected_error;
2193 END IF;
2194 END IF;
2195 END LOOP;
2196 END IF; -- bug # 7484916 fixed by ateotia
2197
2198 -- 2. Taking invoice out of dispute
2199 IF p_claim_rec.claim_id <> p_claim_rec.root_claim_id THEN
2200 OPEN csr_root_claim_number(p_claim_rec.root_claim_id);
2201 FETCH csr_root_claim_number INTO l_root_claim_number;
2202 CLOSE csr_root_claim_number;
2203 ELSE
2204 l_root_claim_number := p_claim_rec.claim_number;
2205 END IF;
2206
2207 ARP_DEDUCTION_COVER.update_amount_in_dispute(
2208 p_customer_trx_id => p_claim_rec.source_object_id,
2209 p_claim_number => l_root_claim_number,
2210 p_amount => p_write_off_amount * -1,
2211 p_init_msg_list => FND_API.g_false,
2212 x_return_status => l_return_status,
2213 x_msg_count => x_msg_count,
2214 x_msg_data => x_msg_data
2215 );
2216 IF l_return_status = FND_API.g_ret_sts_error THEN
2217 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2218 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_UPD_DISPUTE_ERR');
2219 FND_MSG_PUB.add;
2220 END IF;
2221 RAISE FND_API.g_exc_error;
2222 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2224 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_UPD_DISPUTE_UERR');
2225 FND_MSG_PUB.add;
2226 END IF;
2227 RAISE FND_API.g_exc_unexpected_error;
2228 END IF;
2229
2230 ELSIF p_deduction_type in ('RECEIPT_OPM','RECEIPT_DED') THEN
2231 /*------------------------------------------------------------*
2232 | Claim Investigation -> 1. Cover API :: Create Receipt Write_Off
2233 *------------------------------------------------------------*/
2234 IF p_claim_rec.claim_id = p_claim_rec.root_claim_id THEN
2235 OPEN csr_get_more_root_clm_dtls(p_claim_rec.claim_id);
2236 FETCH csr_get_more_root_clm_dtls INTO l_application_ref_num
2237 , l_secondary_appl_ref_id
2238 , l_customer_reference;
2239 CLOSE csr_get_more_root_clm_dtls;
2240 ELSE
2241 OPEN csr_get_more_chld_clm_dtls(p_claim_rec.claim_id);
2242 FETCH csr_get_more_chld_clm_dtls INTO l_application_ref_num
2243 , l_secondary_appl_ref_id
2244 , l_customer_reference;
2245 CLOSE csr_get_more_chld_clm_dtls;
2246 END IF;
2247
2248 IF p_wo_rec_trx_id IS NULL OR
2249 p_wo_rec_trx_id = FND_API.g_miss_num THEN
2250
2251 IF l_receivables_trx_id IS NULL THEN
2252 IF p_deduction_type = 'RECEIPT_OPM' THEN
2253 l_receivables_trx_id := l_wo_rec_trx_id;
2254 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
2255 l_receivables_trx_id := l_neg_wo_rec_trx_id;
2256 END IF;
2257 END IF;
2258 ELSE
2259 l_receivables_trx_id := p_wo_rec_trx_id;
2260 END IF;
2261
2262 ARP_DEDUCTION_COVER.create_receipt_writeoff(
2263 p_claim_id => p_claim_rec.root_claim_id,
2264 p_amount => (p_write_off_amount * -1),
2265 p_new_claim_id => p_claim_rec.root_claim_id,
2266 p_init_msg_list => FND_API.g_false,
2267 p_cash_receipt_id => p_claim_rec.receipt_id,
2268 p_receivables_trx_id => l_receivables_trx_id,
2269 p_ussgl_transaction_code => NULL,
2270 p_application_ref_num => l_application_ref_num,
2271 p_secondary_application_ref_id => l_secondary_appl_ref_id,
2272 p_customer_reference => l_customer_reference,
2273 x_return_status => l_return_status,
2274 x_msg_count => x_msg_count,
2275 x_msg_data => x_msg_data
2276 );
2277 IF l_return_status = FND_API.g_ret_sts_error THEN
2278 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2279 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_REC_WO_ERR');
2280 FND_MSG_PUB.add;
2281 END IF;
2282 RAISE FND_API.g_exc_error;
2283 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2284 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2285 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_REC_WO_UERR');
2286 FND_MSG_PUB.add;
2287 END IF;
2288 RAISE FND_API.g_exc_unexpected_error;
2289 END IF;
2290
2291 -- There is no write_off number populating in case of receipt write_off.
2292 l_x_wo_adjust_id := -3;
2293 -- Modified for Bug 13948201
2294 IF p_claim_rec.payment_method = 'MASS_SETTLEMENT' THEN
2295 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
2296 p_api_version => l_api_version
2297 ,p_init_msg_list => FND_API.g_false
2298 ,p_commit => FND_API.g_false
2299 ,p_validation_level => FND_API.g_valid_level_full
2300 ,x_return_status => l_return_status
2301 ,x_msg_data => x_msg_data
2302 ,x_msg_count => x_msg_count
2303 ,p_claim_id => p_claim_rec.claim_id
2304 ,p_payment_method => 'WRITE_OFF'
2305 ,p_deduction_type => p_deduction_type
2306 ,p_cash_receipt_id => p_claim_rec.receipt_id
2307 ,p_customer_trx_id => p_claim_rec.source_object_id
2308 ,p_adjust_id => l_x_wo_adjust_id
2309 ,p_settlement_doc_id => p_settlement_doc_id
2310 ,p_settlement_mode => 'MASS_SETTLEMENT'
2311 );
2312 ELSE
2313 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
2314 p_api_version => l_api_version
2315 ,p_init_msg_list => FND_API.g_false
2316 ,p_commit => FND_API.g_false
2317 ,p_validation_level => FND_API.g_valid_level_full
2318 ,x_return_status => l_return_status
2319 ,x_msg_data => x_msg_data
2320 ,x_msg_count => x_msg_count
2321 ,p_claim_id => p_claim_rec.claim_id
2322 ,p_payment_method => 'WRITE_OFF'
2323 ,p_deduction_type => p_deduction_type
2324 ,p_cash_receipt_id => p_claim_rec.receipt_id
2325 ,p_customer_trx_id => p_claim_rec.source_object_id
2326 ,p_adjust_id => l_x_wo_adjust_id
2327 );
2328 END IF;
2329
2330 IF l_return_status = FND_API.g_ret_sts_error THEN
2331 RAISE FND_API.g_exc_error;
2332 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2333 RAISE FND_API.g_exc_unexpected_error;
2334 END IF;
2335 END IF;
2336
2337 --//Bug 5345095 BK Modif
2338 IF l_receivables_trx_id IS NOT NULL THEN
2339 BEGIN
2340 UPDATE ozf_claims_all
2341 SET wo_rec_trx_id = l_receivables_trx_id
2342 WHERE claim_id = p_claim_rec.claim_id
2343 AND wo_rec_trx_id IS NULL;
2344
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2348 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_GRP_UPD_DEDU_ERR');
2349 FND_MSG_PUB.add;
2350 END IF;
2351
2352 IF g_debug THEN
2353 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2354 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2355 FND_MSG_PUB.Add;
2356 END IF;
2357 RAISE FND_API.g_exc_unexpected_error;
2358 END;
2359 END IF;
2360
2361 AMS_Utility_PVT.debug_message(l_full_name||': end');
2362
2363 OZF_Utility_PVT.debug_message(l_full_name||': end');
2364
2365 EXCEPTION
2366 WHEN FND_API.g_exc_error THEN
2367 x_return_status := FND_API.g_ret_sts_error;
2368
2369 WHEN FND_API.g_exc_unexpected_error THEN
2370 x_return_status := FND_API.g_ret_sts_unexp_error ;
2371
2372 WHEN OTHERS THEN
2373 x_return_status := FND_API.g_ret_sts_unexp_error ;
2374 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2375 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2376 END IF;
2377
2378 END Create_AR_Write_Off;
2379
2380
2381
2382 /*=======================================================================*
2383 | PROCEDURE
2384 | Process_Settlement_WF
2385 |
2386 | NOTES
2387 |
2388 | HISTORY
2389 | 15-MAR-2002 mchang Create.
2390 *=======================================================================*/
2391 PROCEDURE Process_Settlement_WF(
2392 p_claim_id IN NUMBER
2393
2394 ,x_return_status OUT NOCOPY VARCHAR2
2395 ,x_msg_data OUT NOCOPY VARCHAR2
2396 ,x_msg_count OUT NOCOPY NUMBER
2397 )
2398 IS
2399 l_api_version CONSTANT NUMBER := 1.0;
2400 l_api_name CONSTANT VARCHAR2(30) := 'Process_Settlement_WF';
2401 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2402 l_return_status VARCHAR2(1);
2403
2404 BEGIN
2405 -------------------- initialize -----------------------
2406 IF g_debug THEN
2407 OZF_Utility_PVT.debug_message(l_full_name||': start');
2408 END IF;
2409
2410 x_return_status := FND_API.g_ret_sts_success;
2411
2412 ------------------------ start -------------------------
2413 BEGIN
2414 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
2415 p_claim_id => p_claim_id
2416 ,p_prev_status => 'APPROVED'
2417 ,p_curr_status => 'PENDING_CLOSE'
2418 ,p_next_status => 'CLOSED'
2419 ,p_promotional_claim => 'N'
2420 ,p_process => 'OZF_CLAIM_SETTLEMENT'
2421 );
2422 EXCEPTION
2423 WHEN OTHERS THEN
2424 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2425 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2426 FND_MSG_PUB.Add;
2427 RAISE FND_API.g_exc_unexpected_error;
2428 END;
2429
2430 IF g_debug THEN
2431 OZF_Utility_PVT.debug_message(l_full_name||': end');
2432 END IF;
2433 EXCEPTION
2434 WHEN FND_API.g_exc_unexpected_error THEN
2435 x_return_status := FND_API.g_ret_sts_unexp_error ;
2436
2437 WHEN OTHERS THEN
2438 x_return_status := FND_API.g_ret_sts_unexp_error ;
2439 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2440 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2441 END IF;
2442
2443 END Process_Settlement_WF;
2444
2445 /*=======================================================================*
2446 | Function
2447 | Get_Inv_Credit_Details
2448 |
2449 | Return
2450 | FND_API.g_true / FND_API.g_false
2451 |
2452 | HISTORY
2453 | 14-Jun-2005 Sahana Created for R12
2454 *=======================================================================*/
2455 PROCEDURE Get_Inv_Credit_Details(
2456 p_claim_id IN NUMBER
2457 ,p_invoice_id IN NUMBER
2458 ,x_return_status OUT NOCOPY VARCHAR2
2459 ,x_line_credit OUT NOCOPY NUMBER
2460 ,x_tax_credit OUT NOCOPY NUMBER
2461 ,x_freight_credit OUT NOCOPY NUMBER
2462 ,x_total_credit OUT NOCOPY NUMBER
2463 ,x_cm_line_tbl OUT NOCOPY AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE
2464 ) IS
2465
2466
2467 --//Bug 12340283
2468 CURSOR csr_claim_line_invoice(cv_claim_id IN NUMBER, cv_inv_id IN NUMBER) IS
2469 SELECT source_object_id
2470 , source_object_line_id
2471 , credit_to
2472 , SUM(quantity) qty
2473 , AVG(rate) rate
2474 , SUM(NVL(claim_currency_amount,0)) amount
2475 FROM ozf_claim_lines
2476 WHERE claim_id = cv_claim_id
2477 AND source_object_id = cv_inv_id
2478 GROUP BY source_object_id,source_object_line_id,credit_to;
2479
2480 /*
2481 CURSOR csr_claim_line_invoice(cv_claim_id IN NUMBER, cv_inv_id IN NUMBER) IS
2482 SELECT ar.applied_customer_trx_id,
2483 -- cli.source_object_id,
2484 cli.source_object_line_id,
2485 cli.credit_to,
2486 SUM(cli.quantity) qty,
2487 AVG(cli.rate) rate,
2488 SUM(NVL(cli.claim_currency_amount,0)) amount
2489 FROM ar_receivable_applications_all ar,
2490 ozf_claims_all cla,
2491 ozf_claim_lines_all cli
2492 WHERE cla.claim_id = cli.claim_id
2493 AND ar.cash_receipt_id = cla.receipt_id
2494 AND ar.status ='APP'
2495 AND ar.display ='Y'
2496 AND cla.claim_id = cv_claim_id
2497 AND ar.applied_customer_trx_id = cv_inv_id
2498 GROUP BY ar.applied_customer_trx_id,cli.source_object_line_id,cli.credit_to;
2499 */
2500
2501 l_trx_lines csr_claim_line_invoice%ROWTYPE;
2502
2503 l_api_name CONSTANT VARCHAR2(30) := 'Get_Inv_Credit_Details()';
2504 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2505
2506 l_line_credit NUMBER := 0;
2507 l_tax_credit NUMBER := 0;
2508 l_freight_credit NUMBER := 0;
2509 l_total_credit NUMBER := 0;
2510
2511 l_counter NUMBER := 1;
2512
2513 BEGIN
2514 x_return_status := FND_API.g_ret_sts_success;
2515
2516
2517 IF g_debug THEN
2518 OZF_Utility_PVT.debug_message( l_full_name || ' : Start');
2519 END IF;
2520
2521 OPEN csr_claim_line_invoice(p_claim_id, p_invoice_id);
2522 LOOP
2523 FETCH csr_claim_line_invoice INTO l_trx_lines;
2524 EXIT WHEN csr_claim_line_invoice%NOTFOUND;
2525
2526 IF l_trx_lines.source_object_line_id IS NOT NULL THEN
2527 x_cm_line_tbl(l_counter).customer_trx_line_id := l_trx_lines.source_object_line_id;
2528 x_cm_line_tbl(l_counter).quantity_credited := l_trx_lines.qty * -1;
2529 x_cm_line_tbl(l_counter).price := l_trx_lines.rate;
2530 x_cm_line_tbl(l_counter).extended_amount := l_trx_lines.amount * -1;
2531
2532 IF g_debug THEN
2533 OZF_Utility_PVT.debug_message('x_cm_line_tbl('||l_counter||').customer_trx_line_id='||x_cm_line_tbl(l_counter).customer_trx_line_id);
2534 OZF_Utility_PVT.debug_message('x_cm_line_tbl('||l_counter||').extended_amount='||x_cm_line_tbl(l_counter).extended_amount);
2535 END IF;
2536 l_counter := l_counter +1;
2537 END IF;
2538
2539 IF l_trx_lines.credit_to IS NOT NULL THEN
2540 IF l_trx_lines.credit_to = 'LINE' THEN
2541 l_line_credit := l_line_credit + l_trx_lines.amount ;
2542 ELSIF l_trx_lines.credit_to = 'TAX' THEN
2543 l_tax_credit := l_tax_credit + l_trx_lines.amount ;
2544 ELSIF l_trx_lines.credit_to = 'FREIGHT' THEN
2545 l_freight_credit := l_freight_credit + l_trx_lines.amount ;
2546 END IF;
2547 END IF;
2548
2549 IF l_trx_lines.credit_to IS NULL AND l_trx_lines.source_object_line_id IS NULL THEN
2550 l_total_credit := l_total_credit + l_trx_lines.amount ;
2551 END IF;
2552
2553 END LOOP;
2554 IF g_debug THEN
2555 OZF_Utility_PVT.debug_message('l_line_credit = '||l_line_credit);
2556 OZF_Utility_PVT.debug_message('l_tax_credit = '||l_tax_credit);
2557 OZF_Utility_PVT.debug_message('l_freight_credit = '||l_freight_credit);
2558 OZF_Utility_PVT.debug_message('l_total_credit = '||l_total_credit);
2559 END IF;
2560 CLOSE csr_claim_line_invoice;
2561
2562
2563 x_line_credit := l_line_credit;
2564 x_tax_credit := l_tax_credit;
2565 x_freight_credit := l_freight_credit;
2566 x_total_credit := l_total_credit;
2567
2568 IF g_debug THEN
2569 OZF_Utility_PVT.debug_message( l_full_name || ' : End');
2570 END IF;
2571
2572
2573 EXCEPTION
2574 WHEN OTHERS THEN
2575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2576 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2577 END IF;
2578 x_return_status := FND_API.g_ret_sts_unexp_error;
2579 END Get_Inv_Credit_Details;
2580
2581 /*=======================================================================*
2582 | PROCEDURE
2583 | Pay_by_Single_Invoice_Credit
2584 |
2585 | NOTES
2586 |
2587 | HISTORY
2588 | 15-JUN-2005 Sahana Created for R12.
2589 | 19-APR-2011 BKUNJAN Added parameter p_pay_invoice_id for bugfix : 12340283
2590 *=======================================================================*/
2591 PROCEDURE Pay_by_Single_Invoice_Credit(
2592 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
2593 ,p_sttlmnt_amt IN NUMBER
2594 ,p_invoice_id IN NUMBER
2595 ,p_pay_invoice_id IN NUMBER
2596 ,p_deduction_type IN VARCHAR2
2597 ,p_line_credit IN NUMBER
2598 ,p_tax_credit IN NUMBER
2599 ,p_freight_credit IN NUMBER
2600 ,p_total_credit IN NUMBER
2601 ,p_cm_line_tbl IN AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE
2602
2603 ,x_return_status OUT NOCOPY VARCHAR2
2604 ,x_msg_data OUT NOCOPY VARCHAR2
2605 ,x_msg_count OUT NOCOPY NUMBER
2606 )
2607 IS
2608 l_api_version CONSTANT NUMBER := 1.0;
2609 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Single_Invoice_Credit';
2610 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2611 l_return_status VARCHAR2(1);
2612
2613 l_cm_customer_trx_id NUMBER := NULL;
2614 l_cm_amount NUMBER := 0;
2615 l_new_applied_amount NUMBER;
2616 l_old_applied_amount NUMBER;
2617 l_claim_line_count NUMBER;
2618 l_apply_receipt_id NUMBER;
2619 l_old_applied_claim_amount NUMBER;
2620 l_reapply_claim_amount NUMBER;
2621 l_line_remaining NUMBER;
2622 l_tax_remaining NUMBER;
2623 l_freight_remaining NUMBER;
2624
2625
2626
2627 CURSOR csr_old_applied_amount( cv_cash_receipt_id IN NUMBER
2628 , cv_customer_trx_id IN NUMBER
2629 ) IS
2630 SELECT rec.amount_applied
2631 , pay.amount_due_remaining
2632 , NVL(pay.amount_line_items_remaining, 0) amount_line_items_remaining
2633 , NVL(pay.tax_remaining, 0) tax_remaining
2634 , NVL(pay.freight_remaining, 0) freight_remaining
2635 , rec.comments
2636 , rec.payment_set_id
2637 , rec.application_ref_type
2638 , rec.application_ref_id
2639 , rec.application_ref_num
2640 , rec.secondary_application_ref_id
2641 , rec.application_ref_reason
2642 , rec.customer_reference
2643 FROM ar_receivable_applications rec
2644 , ar_payment_schedules pay
2645 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
2646 AND rec.cash_receipt_id = cv_cash_receipt_id
2647 AND pay.customer_trx_id = cv_customer_trx_id
2648 AND rec.display = 'Y';
2649
2650 --//Bugfix 12340283 - Cursor added to get the receipt details.
2651 --// This cursor will be reusing ROWTYPE l_old_applied_invoice record to store the details
2652
2653 CURSOR csr_receipt_details( cv_cash_receipt_id IN NUMBER ) IS
2654 SELECT 0 amount_applied
2655 , 0 amount_due_remaining
2656 , 0 amount_line_items_remaining
2657 , 0 tax_remaining
2658 , 0 freight_remaining
2659 , rec.comments
2660 , rec.payment_set_id
2661 , rec.application_ref_type
2662 , rec.application_ref_id
2663 , rec.application_ref_num
2664 , rec.secondary_application_ref_id
2665 , rec.application_ref_reason
2666 , rec.customer_reference
2667 FROM ar_receivable_applications_all rec
2668 WHERE rec.cash_receipt_id = cv_cash_receipt_id
2669 AND rec.display = 'Y';
2670
2671 l_old_applied_invoice csr_old_applied_amount%ROWTYPE;
2672
2673 CURSOR csr_count_claim_line(cv_claim_id IN NUMBER) IS
2674 SELECT COUNT(claim_line_id)
2675 FROM ozf_claim_lines
2676 WHERE claim_id = cv_claim_id;
2677
2678 CURSOR csr_invoice_apply_receipt(cv_invoice_id IN NUMBER) IS
2679 SELECT rec.cash_receipt_id
2680 FROM ar_receivable_applications_all rec
2681 , ar_payment_schedules pay
2682 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
2683 AND pay.customer_trx_id = cv_invoice_id;
2684
2685 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
2686 , cv_root_claim_id IN NUMBER) IS
2687 SELECT rec.amount_applied
2688 FROM ar_receivable_applications rec
2689 WHERE rec.applied_payment_schedule_id = -4
2690 AND rec.cash_receipt_id = cv_cash_receipt_id
2691 AND rec.application_ref_type = 'CLAIM'
2692 AND rec.display = 'Y'
2693 AND rec.secondary_application_ref_id = cv_root_claim_id;
2694
2695 CURSOR csr_customer_trx_lines(cv_invoice_line_id IN NUMBER) IS
2696 SELECT customer_trx_id
2697 FROM ra_customer_trx_lines
2698 WHERE customer_trx_line_id = cv_invoice_line_id;
2699
2700 BEGIN
2701 -------------------- initialize -----------------------
2702 IF g_debug THEN
2703 OZF_Utility_PVT.debug_message(l_full_name||': start');
2704 END IF;
2705
2706 x_return_status := FND_API.g_ret_sts_success;
2707
2708 IF p_deduction_type = 'SOURCE_DED' THEN
2709 /*------------------------------------------------------------*
2710 | Remove invoice from dispute.
2711 | Invoice Deduction - Credit to Tax/Line/Freight
2712 | -> 1. Unapply invoice from receipt.
2713 | -> 2. Create credit memo for the invoice.
2714 | -> 3. Apply invoice back on receipt.
2715 | -> 4. Update dispute amount.
2716 | Invoice Deduction - Credit to Invoice
2717 | -> create credit memo for the invoice
2718 *------------------------------------------------------------*/
2719
2720 -- Update dispute amount.
2721 IF g_debug THEN
2722 OZF_Utility_PVT.debug_message('Source Deduction -> Update dispute amount');
2723 END IF;
2724 Update_dispute_amount(
2725 p_claim_rec => p_claim_rec
2726 ,p_dispute_amount => l_cm_amount
2727 ,x_return_status => l_return_status
2728 ,x_msg_data => x_msg_data
2729 ,x_msg_count => x_msg_count
2730 );
2731 IF l_return_status = FND_API.g_ret_sts_error THEN
2732 RAISE FND_API.g_exc_error;
2733 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2734 RAISE FND_API.g_exc_unexpected_error;
2735 END IF;
2736
2737
2738 -- -------------------------------------
2739 -- Invoice Deduction - Credit to Invoice
2740 -- -------------------------------------
2741 IF p_line_credit = 0 AND
2742 p_tax_credit = 0 AND
2743 p_freight_credit = 0 AND
2744 p_total_credit <> 0 THEN
2745 IF g_debug THEN
2746 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to --] -> Create Credit Memo');
2747 END IF;
2748 Create_AR_Credit_Memo(
2749 p_claim_rec => p_claim_rec
2750 ,p_customer_trx_id => p_claim_rec.source_object_id
2751 ,p_deduction_type => p_deduction_type
2752 ,p_line_remaining => 0
2753 ,p_tax_remaining => 0
2754 ,p_freight_remaining => 0
2755 ,p_line_credit => p_line_credit
2756 ,p_tax_credit => p_tax_credit
2757 ,p_freight_credit => p_freight_credit
2758 ,p_total_credit => p_total_credit
2759 ,p_cm_line_tbl => p_cm_line_tbl
2760 ,p_upd_dispute_flag => FND_API.g_true
2761 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2762 ,x_cm_amount => l_cm_amount
2763 ,x_return_status => l_return_status
2764 ,x_msg_data => x_msg_data
2765 ,x_msg_count => x_msg_count
2766 );
2767 IF l_return_status = FND_API.g_ret_sts_error THEN
2768 RAISE FND_API.g_exc_error;
2769 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2770 RAISE FND_API.g_exc_unexpected_error;
2771 END IF;
2772
2773 -- ----------------------------------------------
2774 -- Invoice Deduction - Credit to Tax/Line/Freight
2775 -- ----------------------------------------------
2776 ELSE
2777 OPEN csr_old_applied_amount( p_claim_rec.receipt_id
2778 , p_claim_rec.source_object_id
2779 );
2780 FETCH csr_old_applied_amount INTO l_old_applied_invoice;
2781 CLOSE csr_old_applied_amount;
2782
2783
2784 -- 1. Unapply invoice from receipt.
2785 IF g_debug THEN
2786 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to Line/Tax/Freight] -> 1. Unapply invoice from receipt.');
2787 END IF;
2788 -- Bug4118351: Do not unapply if original amount applied is zero.
2789 IF l_old_applied_invoice.amount_applied <> 0 THEN
2790 Unapply_from_Receipt(
2791 p_cash_receipt_id => p_claim_rec.receipt_id
2792 ,p_customer_trx_id => p_claim_rec.source_object_id
2793 ,x_return_status => l_return_status
2794 ,x_msg_data => x_msg_data
2795 ,x_msg_count => x_msg_count
2796 );
2797 IF l_return_status = FND_API.g_ret_sts_error THEN
2798 RAISE FND_API.g_exc_error;
2799 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2800 RAISE FND_API.g_exc_unexpected_error;
2801 END IF;
2802 END IF;
2803
2804 -- 2. Create credit memo for the invoice
2805 IF g_debug THEN
2806 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to Line/Tax/Freight] -> 2. Create credit memo for the invoice');
2807 END IF;
2808 Create_AR_Credit_Memo(
2809 p_claim_rec => p_claim_rec
2810 ,p_customer_trx_id => p_claim_rec.source_object_id
2811 ,p_deduction_type => p_deduction_type
2812 ,p_line_remaining => 0
2813 ,p_tax_remaining => 0
2814 ,p_freight_remaining => 0
2815 ,p_line_credit => p_line_credit
2816 ,p_tax_credit => p_tax_credit
2817 ,p_freight_credit => p_freight_credit
2818 ,p_total_credit => p_total_credit
2819 ,p_cm_line_tbl => p_cm_line_tbl
2820 ,p_upd_dispute_flag => FND_API.g_false
2821 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2822 ,x_cm_amount => l_cm_amount
2823 ,x_return_status => l_return_status
2824 ,x_msg_data => x_msg_data
2825 ,x_msg_count => x_msg_count
2826 );
2827 IF l_return_status = FND_API.g_ret_sts_error THEN
2828 RAISE FND_API.g_exc_error;
2829 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2830 RAISE FND_API.g_exc_unexpected_error;
2831 END IF;
2832
2833 -- 3. Apply invoice back on receipt.
2834 IF g_debug THEN
2835 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to Line/Tax/Freight] -> 3. Apply invoice back on receipt');
2836 END IF;
2837 --IF (l_old_applied_invoice.amount_due_remaining + l_cm_amount) = 0 THEN
2838 IF l_old_applied_invoice.amount_due_remaining = p_claim_rec.amount_settled THEN
2839 l_old_applied_invoice.application_ref_type := NULL;
2840 l_old_applied_invoice.application_ref_id := NULL;
2841 l_old_applied_invoice.application_ref_num := NULL;
2842 l_old_applied_invoice.secondary_application_ref_id := NULL;
2843 l_old_applied_invoice.application_ref_reason := NULL;
2844 END IF;
2845
2846 -- Bug4118351: Reapply invoice only if original applied amount was not 0.
2847 IF l_old_applied_invoice.amount_applied <> 0 THEN
2848 Apply_on_Receipt(
2849 p_cash_receipt_id => p_claim_rec.receipt_id
2850 ,p_customer_trx_id => p_claim_rec.source_object_id
2851 ,p_new_applied_amount => l_old_applied_invoice.amount_applied
2852 ,p_comments => l_old_applied_invoice.comments
2853 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
2854 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
2855 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
2856 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
2857 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
2858 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
2859 ,p_customer_reference => l_old_applied_invoice.customer_reference
2860 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
2861 ,x_return_status => l_return_status
2862 ,x_msg_data => x_msg_data
2863 ,x_msg_count => x_msg_count
2864 );
2865 IF l_return_status = FND_API.g_ret_sts_error THEN
2866 RAISE FND_API.g_exc_error;
2867 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2868 RAISE FND_API.g_exc_unexpected_error;
2869 END IF;
2870 END IF;
2871
2872 END IF;
2873
2874 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
2875 /*------------------------------------------------------------*
2876 | Receipt Deduction
2877 | 1. Unapply associated invoice from receipt.
2878 | 2. Unapply_Claim_Investigation.
2879 | 3. Validate Invoice
2880 | -> Credit to Invoice
2881 | 4. Apply same associated invoice even if balance amount is zero
2882 | 5. Create credit memo for the invoice.
2883 |
2884 | -> Credit to Tax/Line/Freight or specific line
2885 | 4. Reapply at this point if balance is zero
2886 | 5. Create credit memo for the invoice.
2887 | 6. Reapply same associated invoice with reduced amount if balance not zero
2888 | Modified for 4308173
2889 *------------------------------------------------------------*/
2890
2891 IF g_debug THEN
2892 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Unapply associated invoice from receipt');
2893 END IF;
2894
2895 IF p_invoice_id <> 0 THEN --..Added for Bugfix : 12340283, Skip Unapply_from_Receipt if there is no associated receipt with Invoice.
2896
2897 OPEN csr_old_applied_amount( p_claim_rec.receipt_id, p_invoice_id);
2898 FETCH csr_old_applied_amount INTO l_old_applied_invoice;
2899 CLOSE csr_old_applied_amount;
2900
2901 Unapply_from_Receipt(
2902 p_cash_receipt_id => p_claim_rec.receipt_id
2903 ,p_customer_trx_id => p_invoice_id
2904 ,x_return_status => l_return_status
2905 ,x_msg_data => x_msg_data
2906 ,x_msg_count => x_msg_count
2907 );
2908 IF l_return_status = FND_API.g_ret_sts_error THEN
2909 RAISE FND_API.g_exc_error;
2910 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2911 RAISE FND_API.g_exc_unexpected_error;
2912 END IF;
2913
2914 ELSE
2915
2916 OPEN csr_receipt_details( p_claim_rec.receipt_id);
2917 FETCH csr_receipt_details INTO l_old_applied_invoice;
2918 CLOSE csr_receipt_details;
2919
2920 IF g_debug THEN
2921 OZF_Utility_PVT.debug_message('l_old_applied_invoice.application_ref_type :'||l_old_applied_invoice.application_ref_type);
2922 OZF_Utility_PVT.debug_message('l_old_applied_invoice.application_ref_id :'||l_old_applied_invoice.application_ref_id);
2923 OZF_Utility_PVT.debug_message('l_old_applied_invoice.application_ref_num :'||l_old_applied_invoice.application_ref_num);
2924 OZF_Utility_PVT.debug_message('l_old_applied_invoice.secondary_application_ref_id :'||l_old_applied_invoice.secondary_application_ref_id);
2925 OZF_Utility_PVT.debug_message('l_old_applied_invoice.application_ref_reason :'||l_old_applied_invoice.application_ref_reason);
2926 OZF_Utility_PVT.debug_message('l_old_applied_invoice.customer_reference :'||l_old_applied_invoice.customer_reference);
2927 END IF;
2928
2929 END IF; -- p_invoice_id <> 0 THEN
2930
2931 IF g_debug THEN
2932 OZF_Utility_PVT.debug_message('Receipt Deduction -> 2. Unapply_Claim_Investigation.');
2933 END IF;
2934 OPEN csr_old_claim_investigation(p_claim_rec.receipt_id, p_claim_rec.root_claim_id);
2935 FETCH csr_old_claim_investigation INTO l_old_applied_claim_amount;
2936 CLOSE csr_old_claim_investigation;
2937
2938 l_reapply_claim_amount := l_old_applied_claim_amount + p_sttlmnt_amt;
2939
2940 Unapply_Claim_Investigation(
2941 p_claim_rec => p_claim_rec
2942 ,p_reapply_amount => l_reapply_claim_amount --0
2943 ,x_return_status => l_return_status
2944 ,x_msg_data => x_msg_data
2945 ,x_msg_count => x_msg_count
2946 );
2947 IF l_return_status = FND_API.g_ret_sts_error THEN
2948 RAISE FND_API.g_exc_error;
2949 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2950 RAISE FND_API.g_exc_unexpected_error;
2951 END IF;
2952
2953
2954 IF g_debug THEN
2955 OZF_Utility_PVT.debug_message('Receipt Deduction -> 3. Validate Invoice.');
2956 END IF;
2957 OZF_AR_VALIDATION_PVT.Validate_CreditTo_Information(
2958 p_claim_rec => p_claim_rec
2959 ,p_invoice_id => p_pay_invoice_id
2960 ,x_return_status => l_return_status
2961 );
2962 IF l_return_status = FND_API.g_ret_sts_error THEN
2963 RAISE FND_API.g_exc_error;
2964 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2965 RAISE FND_API.g_exc_unexpected_error;
2966 END IF;
2967
2968 -- ----------------------------------------------
2969 -- Claim Investigation (Deduction) - Invoice
2970 -- ----------------------------------------------
2971
2972 IF p_line_credit = 0 AND
2973 p_tax_credit = 0 AND
2974 p_freight_credit = 0 AND
2975 p_total_credit <> 0 THEN
2976
2977 IF g_debug THEN
2978 OZF_Utility_PVT.debug_message('Receipt Deduction -> 4. Apply same associated invoice with reduced amount');
2979 END IF;
2980
2981 IF l_old_applied_invoice.amount_applied <> 0 THEN
2982 l_new_applied_amount := l_old_applied_invoice.amount_applied - p_sttlmnt_amt;
2983 ELSE
2984 l_new_applied_amount := p_sttlmnt_amt;
2985 END IF;
2986
2987 IF p_invoice_id <> 0 THEN --// Added for bugfix 12398368 - No need to apply on receipt if the Unapply_from_Receipt is not invoked.
2988
2989 Apply_on_Receipt(
2990 p_cash_receipt_id => p_claim_rec.receipt_id
2991 ,p_customer_trx_id => p_pay_invoice_id
2992 ,p_new_applied_amount => l_new_applied_amount
2993 ,p_comments => l_old_applied_invoice.comments
2994 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
2995 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
2996 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
2997 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
2998 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
2999 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
3000 ,p_customer_reference => l_old_applied_invoice.customer_reference
3001 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
3002 ,x_return_status => l_return_status
3003 ,x_msg_data => x_msg_data
3004 ,x_msg_count => x_msg_count
3005 );
3006 IF l_return_status = FND_API.g_ret_sts_error THEN
3007 RAISE FND_API.g_exc_error;
3008 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3009 RAISE FND_API.g_exc_unexpected_error;
3010 END IF;
3011 END IF;
3012
3013 -- 4. Create credit memo for the invoice.
3014 IF g_debug THEN
3015 OZF_Utility_PVT.debug_message('Receipt Deduction -> 5. Create credit memo for the invoice.');
3016 END IF;
3017 Create_AR_Credit_Memo(
3018 p_claim_rec => p_claim_rec
3019 ,p_customer_trx_id => p_pay_invoice_id
3020 ,p_deduction_type => p_deduction_type
3021 ,p_line_remaining => l_old_applied_invoice.amount_line_items_remaining
3022 ,p_tax_remaining => l_old_applied_invoice.tax_remaining
3023 ,p_freight_remaining => l_old_applied_invoice.freight_remaining
3024 ,p_line_credit => p_line_credit
3025 ,p_tax_credit => p_tax_credit
3026 ,p_freight_credit => p_freight_credit
3027 ,p_total_credit => p_total_credit
3028 ,p_cm_line_tbl => p_cm_line_tbl
3029 ,p_upd_dispute_flag => FND_API.g_false
3030 ,x_cm_customer_trx_id => l_cm_customer_trx_id
3031 ,x_cm_amount => l_cm_amount
3032 ,x_return_status => l_return_status
3033 ,x_msg_data => x_msg_data
3034 ,x_msg_count => x_msg_count
3035 );
3036 IF l_return_status = FND_API.g_ret_sts_error THEN
3037 RAISE FND_API.g_exc_error;
3038 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3039 RAISE FND_API.g_exc_unexpected_error;
3040 END IF;
3041
3042 ELSE
3043 -- ----------------------------------------------
3044 -- Claim Investigation (Deduction) - Credit to Tax/Line/Freight
3045 -- ----------------------------------------------
3046 -- 3. Apply same associated invoice if balance amount is zero.
3047 IF g_debug THEN
3048 OZF_Utility_PVT.debug_message('Receipt Deduction -> 3. Apply same associated invoice if balance amount is zero.');
3049 END IF;
3050 IF l_old_applied_invoice.amount_applied <> 0 THEN
3051 l_new_applied_amount := l_old_applied_invoice.amount_applied - p_claim_rec.amount_settled;
3052 ELSE
3053 l_new_applied_amount := p_claim_rec.amount_settled;
3054 END IF;
3055
3056 IF l_new_applied_amount = 0 THEN
3057 IF p_invoice_id <> 0 THEN --// Added for bugfix 12398368 - No need to apply on receipt if the Unapply_from_Receipt is not invoked.
3058 Apply_on_Receipt(
3059 p_cash_receipt_id => p_claim_rec.receipt_id
3060 ,p_customer_trx_id => p_pay_invoice_id
3061 ,p_new_applied_amount => l_new_applied_amount
3062 ,p_comments => l_old_applied_invoice.comments
3063 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
3064 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
3065 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
3066 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
3067 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
3068 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
3069 ,p_customer_reference => l_old_applied_invoice.customer_reference
3070 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
3071 ,x_return_status => l_return_status
3072 ,x_msg_data => x_msg_data
3073 ,x_msg_count => x_msg_count
3074 );
3075 IF l_return_status = FND_API.g_ret_sts_error THEN
3076 RAISE FND_API.g_exc_error;
3077 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3078 RAISE FND_API.g_exc_unexpected_error;
3079 END IF;
3080 END IF;
3081 END IF;
3082
3083 -- 4. Create credit memo for the invoice.
3084 IF g_debug THEN
3085 OZF_Utility_PVT.debug_message('Receipt Deduction -> 4. Create credit memo for the invoice.');
3086 END IF;
3087 Create_AR_Credit_Memo(
3088 p_claim_rec => p_claim_rec
3089 ,p_customer_trx_id => p_pay_invoice_id
3090 ,p_deduction_type => p_deduction_type
3091 ,p_line_remaining => l_old_applied_invoice.amount_line_items_remaining
3092 ,p_tax_remaining => l_old_applied_invoice.tax_remaining
3093 ,p_freight_remaining => l_old_applied_invoice.freight_remaining
3094 ,p_line_credit => p_line_credit
3095 ,p_tax_credit => p_tax_credit
3096 ,p_freight_credit => p_freight_credit
3097 ,p_total_credit => p_total_credit
3098 ,p_cm_line_tbl => p_cm_line_tbl
3099 ,p_upd_dispute_flag => FND_API.g_false
3100 ,x_cm_customer_trx_id => l_cm_customer_trx_id
3101 ,x_cm_amount => l_cm_amount
3102 ,x_return_status => l_return_status
3103 ,x_msg_data => x_msg_data
3104 ,x_msg_count => x_msg_count
3105 );
3106 IF l_return_status = FND_API.g_ret_sts_error THEN
3107 RAISE FND_API.g_exc_error;
3108 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3109 RAISE FND_API.g_exc_unexpected_error;
3110 END IF;
3111
3112
3113 IF g_debug THEN
3114 OZF_Utility_PVT.debug_message('Receipt Deduction -> 5. Apply same associated invoice with reduced amount');
3115 END IF;
3116
3117 IF l_old_applied_invoice.amount_applied <> 0 THEN
3118 l_new_applied_amount := l_old_applied_invoice.amount_applied - p_sttlmnt_amt;
3119 ELSE
3120 l_new_applied_amount := p_sttlmnt_amt;
3121 END IF;
3122
3123 IF l_new_applied_amount = 0 THEN
3124 IF p_invoice_id <> 0 THEN --// Added for bugfix 12398368 - No need to apply on receipt if the Unapply_from_Receipt is not invoked.
3125 Apply_on_Receipt(
3126 p_cash_receipt_id => p_claim_rec.receipt_id
3127 ,p_customer_trx_id => p_pay_invoice_id
3128 ,p_new_applied_amount => l_new_applied_amount
3129 ,p_comments => l_old_applied_invoice.comments
3130 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
3131 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
3132 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
3133 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
3134 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
3135 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
3136 ,p_customer_reference => l_old_applied_invoice.customer_reference
3137 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
3138 ,x_return_status => l_return_status
3139 ,x_msg_data => x_msg_data
3140 ,x_msg_count => x_msg_count
3141 );
3142 IF l_return_status = FND_API.g_ret_sts_error THEN
3143 RAISE FND_API.g_exc_error;
3144 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3145 RAISE FND_API.g_exc_unexpected_error;
3146 END IF;
3147 END IF;
3148 END IF;
3149 END IF;
3150
3151 ELSIF p_deduction_type = 'CLAIM' THEN
3152 /*------------------------------------------------------------*
3153 | Claim - Credit to Tax/Line/Freight
3154 | -> Create credit memo for the invoice.
3155 | Claim - Credit to --
3156 | -> create credit memo for the invoice
3157 *------------------------------------------------------------*/
3158
3159 IF p_line_credit = 0 AND
3160 p_tax_credit = 0 AND
3161 p_freight_credit = 0 AND
3162 p_total_credit <> 0 THEN
3163 IF g_debug THEN
3164 OZF_Utility_PVT.debug_message('Claim [Invoice Credit to --] -> 1. Create Credit Memo');
3165 END IF;
3166 -- 1. Create credit memo for the invoice
3167 Create_AR_Credit_Memo(
3168 p_claim_rec => p_claim_rec
3169 ,p_customer_trx_id => p_invoice_id
3170 ,p_deduction_type => p_deduction_type
3171 ,p_line_remaining => 0
3172 ,p_tax_remaining => 0
3173 ,p_freight_remaining => 0
3174 ,p_line_credit => p_line_credit
3175 ,p_tax_credit => p_tax_credit
3176 ,p_freight_credit => p_freight_credit
3177 ,p_total_credit => p_total_credit
3178 ,p_cm_line_tbl => p_cm_line_tbl
3179 ,p_upd_dispute_flag => FND_API.g_false
3180 ,x_cm_customer_trx_id => l_cm_customer_trx_id
3181 ,x_cm_amount => l_cm_amount
3182 ,x_return_status => l_return_status
3183 ,x_msg_data => x_msg_data
3184 ,x_msg_count => x_msg_count
3185 );
3186 IF l_return_status = FND_API.g_ret_sts_error THEN
3187 RAISE FND_API.g_exc_error;
3188 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3189 RAISE FND_API.g_exc_unexpected_error;
3190 END IF;
3191 ELSE
3192 -- 1. Create credit memo for the invoice
3193 IF g_debug THEN
3194 OZF_Utility_PVT.debug_message('Claim [Invoice Credit to Line/Tax/Freight] -> 1. Create credit memo for the invoice');
3195 END IF;
3196 Create_AR_Credit_Memo(
3197 p_claim_rec => p_claim_rec
3198 ,p_customer_trx_id => p_invoice_id
3199 ,p_deduction_type => p_deduction_type
3200 ,p_line_remaining => 0
3201 ,p_tax_remaining => 0
3202 ,p_freight_remaining => 0
3203 ,p_line_credit => p_line_credit
3204 ,p_tax_credit => p_tax_credit
3205 ,p_freight_credit => p_freight_credit
3206 ,p_total_credit => p_total_credit
3207 ,p_cm_line_tbl => p_cm_line_tbl
3208 ,p_upd_dispute_flag => FND_API.g_false
3209 ,x_cm_customer_trx_id => l_cm_customer_trx_id
3210 ,x_cm_amount => l_cm_amount
3211 ,x_return_status => l_return_status
3212 ,x_msg_data => x_msg_data
3213 ,x_msg_count => x_msg_count
3214 );
3215 IF l_return_status = FND_API.g_ret_sts_error THEN
3216 RAISE FND_API.g_exc_error;
3217 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3218 RAISE FND_API.g_exc_unexpected_error;
3219 END IF;
3220 END IF;
3221 END IF;
3222
3223 /*------------------------------------------------------------*
3224 | Update Deduction payment detail
3225 *------------------------------------------------------------*/
3226 IF l_cm_customer_trx_id IS NOT NULL THEN
3227 -- Update Deduction payment detail
3228 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
3229 p_api_version => l_api_version
3230 ,p_init_msg_list => FND_API.g_false
3231 ,p_commit => FND_API.g_false
3232 ,p_validation_level => FND_API.g_valid_level_full
3233 ,x_return_status => l_return_status
3234 ,x_msg_data => x_msg_data
3235 ,x_msg_count => x_msg_count
3236 ,p_claim_id => p_claim_rec.claim_id
3237 ,p_payment_method => p_claim_rec.payment_method
3238 ,p_deduction_type => p_deduction_type
3239 ,p_cash_receipt_id => NULL
3240 ,p_customer_trx_id => l_cm_customer_trx_id
3241 ,p_adjust_id => NULL
3242 );
3243 IF l_return_status = FND_API.g_ret_sts_error THEN
3244 RAISE FND_API.g_exc_error;
3245 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3246 RAISE FND_API.g_exc_unexpected_error;
3247 END IF;
3248 END IF;
3249
3250 IF g_debug THEN
3251 OZF_Utility_PVT.debug_message(l_full_name||': end');
3252 END IF;
3253 EXCEPTION
3254 WHEN FND_API.g_exc_error THEN
3255 x_return_status := FND_API.g_ret_sts_error;
3256
3257 WHEN FND_API.g_exc_unexpected_error THEN
3258 x_return_status := FND_API.g_ret_sts_unexp_error ;
3259
3260 WHEN OTHERS THEN
3261 x_return_status := FND_API.g_ret_sts_unexp_error ;
3262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3263 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3264 END IF;
3265
3266 END Pay_by_Single_Invoice_Credit;
3267
3268
3269 /*=======================================================================*
3270 | PROCEDURE
3271 | Pay_by_Invoice_Credit
3272 |
3273 | NOTES
3274 |
3275 | HISTORY
3276 | 15-MAR-2002 mchang Create.
3277 *=======================================================================*/
3278 PROCEDURE Pay_by_Invoice_Credit(
3279 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3280 ,p_deduction_type IN VARCHAR2
3281
3282 ,x_return_status OUT NOCOPY VARCHAR2
3283 ,x_msg_data OUT NOCOPY VARCHAR2
3284 ,x_msg_count OUT NOCOPY NUMBER
3285 )
3286 IS
3287 l_api_version CONSTANT NUMBER := 1.0;
3288 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Invoice_Credit';
3289 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3290 l_return_status VARCHAR2(1);
3291
3292 l_cm_customer_trx_id NUMBER := NULL;
3293 l_cm_amount NUMBER := 0;
3294 l_new_applied_amount NUMBER;
3295 l_old_applied_amount NUMBER;
3296 l_claim_line_count NUMBER;
3297 l_apply_receipt_id NUMBER;
3298 l_line_activity_type VARCHAR2(30);
3299 l_line_invoice_id NUMBER;
3300 l_line_invoice_line_id NUMBER;
3301 l_old_applied_claim_amount NUMBER;
3302 l_reapply_claim_amount NUMBER;
3303 l_line_remaining NUMBER;
3304 l_tax_remaining NUMBER;
3305 l_freight_remaining NUMBER;
3306 l_line_credit NUMBER;
3307 l_tax_credit NUMBER;
3308 l_freight_credit NUMBER;
3309 l_total_credit NUMBER;
3310 l_cm_line_tbl AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE;
3311
3312 l_process_setl_wf BOOLEAN;
3313 l_process_line_cr BOOLEAN;
3314
3315
3316 CURSOR csr_old_applied_amount( cv_cash_receipt_id IN NUMBER
3317 , cv_customer_trx_id IN NUMBER
3318 ) IS
3319 SELECT rec.amount_applied
3320 , pay.amount_due_remaining
3321 , NVL(pay.amount_line_items_remaining, 0) amount_line_items_remaining
3322 , NVL(pay.tax_remaining, 0) tax_remaining
3323 , NVL(pay.freight_remaining, 0) freight_remaining
3324 , rec.comments
3325 , rec.payment_set_id
3326 , rec.application_ref_type
3327 , rec.application_ref_id
3328 , rec.application_ref_num
3329 , rec.secondary_application_ref_id
3330 , rec.application_ref_reason
3331 , rec.customer_reference
3332 FROM ar_receivable_applications rec
3333 , ar_payment_schedules pay
3334 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
3335 AND rec.cash_receipt_id = cv_cash_receipt_id
3336 AND pay.customer_trx_id = cv_customer_trx_id
3337 AND rec.display = 'Y';
3338
3339 l_old_applied_invoice csr_old_applied_amount%ROWTYPE;
3340
3341 CURSOR csr_count_claim_line(cv_claim_id IN NUMBER) IS
3342 SELECT COUNT(claim_line_id)
3343 FROM ozf_claim_lines
3344 WHERE claim_id = cv_claim_id;
3345
3346 CURSOR csr_claim_line_invoice(cv_claim_id IN NUMBER) IS
3347 SELECT source_object_class
3348 , source_object_id
3349 , source_object_line_id
3350 FROM ozf_claim_lines
3351 WHERE claim_id = cv_claim_id;
3352
3353
3354 CURSOR csr_invoice_apply_receipt(cv_invoice_id IN NUMBER) IS
3355 SELECT rec.cash_receipt_id
3356 FROM ar_receivable_applications_all rec
3357 , ar_payment_schedules pay
3358 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
3359 AND pay.customer_trx_id = cv_invoice_id;
3360
3361 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
3362 , cv_root_claim_id IN NUMBER) IS
3363 SELECT rec.amount_applied
3364 FROM ar_receivable_applications rec
3365 WHERE rec.applied_payment_schedule_id = -4
3366 AND rec.cash_receipt_id = cv_cash_receipt_id
3367 AND rec.application_ref_type = 'CLAIM'
3368 AND rec.display = 'Y'
3369 AND rec.secondary_application_ref_id = cv_root_claim_id;
3370
3371 CURSOR csr_customer_trx_lines(cv_invoice_line_id IN NUMBER) IS
3372 SELECT customer_trx_id
3373 FROM ra_customer_trx_lines
3374 WHERE customer_trx_line_id = cv_invoice_line_id;
3375
3376 --//Bug 12340283
3377 CURSOR csr_claim_lines(cv_claim_id IN NUMBER) IS
3378 SELECT source_object_id, sum(claim_currency_amount) amt
3379 FROM ozf_claim_lines_all
3380 WHERE claim_id = cv_claim_id
3381 GROUP BY source_object_id;
3382
3383 /*CURSOR csr_claim_lines_ar(cv_claim_id IN NUMBER) IS
3384 SELECT ar.applied_customer_trx_id,
3385 cli.source_object_id,
3386 SUM(cli.claim_currency_amount) amt
3387 FROM ar_receivable_applications_all ar,
3388 ozf_claims_all cla,
3389 ozf_claim_lines_all cli
3390 WHERE cla.claim_id = cli.claim_id
3391 AND ar.cash_receipt_id = cla.receipt_id
3392 AND ar.status ='APP'
3393 AND ar.display ='Y'
3394 AND cla.claim_id = cv_claim_id
3395 GROUP BY ar.applied_customer_trx_id,cli.source_object_id;
3396 */
3397 -- Fix for Bug 13640947
3398 CURSOR csr_claim_lines_ar(cv_claim_id IN NUMBER) IS
3399 SELECT
3400 cli.source_object_id,
3401 SUM(cli.claim_currency_amount) amt
3402 FROM
3403 ozf_claims_all cla,
3404 ozf_claim_lines_all cli
3405 WHERE cla.claim_id = cli.claim_id
3406 AND cla.claim_id = cv_claim_id
3407 GROUP BY cli.source_object_id;
3408
3409 CURSOR get_applied_invoice_cnt(cv_claim_id IN NUMBER) IS
3410 SELECT count(*)
3411 FROM ar_receivable_applications_all ar,
3412 ozf_claims_all cla,
3413 ozf_claim_lines_all cli
3414 WHERE cla.claim_id = cli.claim_id
3415 AND ar.cash_receipt_id = cla.receipt_id
3416 AND ar.status ='APP'
3417 AND ar.display ='Y'
3418 AND cla.claim_id = cv_claim_id;
3419
3420 l_lines_rec_ar csr_claim_lines_ar%ROWTYPE;
3421 l_lines_rec csr_claim_lines%ROWTYPE;
3422 l_inv_count NUMBER := 0;
3423
3424 BEGIN
3425 -------------------- initialize -----------------------
3426 IF g_debug THEN
3427 OZF_Utility_PVT.debug_message(l_full_name||': start');
3428 END IF;
3429
3430 SAVEPOINT Pay_by_Invoice_Credit;
3431
3432 x_return_status := FND_API.g_ret_sts_success;
3433
3434 ------------------------ start -------------------------
3435 /*------------------------------------------------------------*
3436 | Check Claim Line invoice to see if need to process settement workflow
3437 *------------------------------------------------------------*/
3438
3439 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED' , 'CLAIM') THEN
3440
3441 l_process_setl_wf := OZF_AR_VALIDATION_PVT.Check_to_Process_SETL_WF(
3442 p_claim_rec => p_claim_rec
3443 ,x_return_status => l_return_status
3444 );
3445 IF l_return_status = FND_API.g_ret_sts_error THEN
3446 RAISE FND_API.g_exc_error;
3447 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3448 RAISE FND_API.g_exc_unexpected_error;
3449 END IF;
3450
3451
3452 IF g_debug THEN
3453 IF l_process_setl_wf THEN
3454 OZF_Utility_PVT.debug_message('Process Settlement Workflow? -> Yes' );
3455 ELSE
3456 OZF_Utility_PVT.debug_message('Process Settlement Workflow? -> No' );
3457 END IF;
3458 END IF;
3459
3460 IF l_process_setl_wf THEN
3461 /*------------------------------------------------------------*
3462 | Process Settlement Workflow
3463 *------------------------------------------------------------*/
3464
3465 Process_Settlement_WF(
3466 p_claim_id => p_claim_rec.claim_id
3467 ,x_return_status => l_return_status
3468 ,x_msg_data => x_msg_data
3469 ,x_msg_count => x_msg_count
3470 );
3471 IF l_return_status = FND_API.g_ret_sts_error THEN
3472 RAISE FND_API.g_exc_error;
3473 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3474 RAISE FND_API.g_exc_unexpected_error;
3475 END IF;
3476 l_cm_customer_trx_id := NULL;
3477 ELSE
3478
3479
3480 /*------------------------------------------------------------*
3481 | Update Claim Status to CLOSED.
3482 *------------------------------------------------------------*/
3483 Close_Claim(
3484 p_claim_rec => p_claim_rec
3485 ,x_return_status => l_return_status
3486 ,x_msg_data => x_msg_data
3487 ,x_msg_count => x_msg_count
3488 );
3489 IF l_return_status = FND_API.g_ret_sts_error THEN
3490 RAISE FND_API.g_exc_error;
3491 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3492 RAISE FND_API.g_exc_unexpected_error;
3493 END IF;
3494
3495
3496 -- For each invoice to be credited, get credit details and create creditmemo.
3497 --//Bugfix 12340283 - Program flow Modified.
3498
3499 IF p_deduction_type ='RECEIPT_DED' THEN
3500 --Below cursor will check if there is any Invoice associated with the Receipt. If not found, skip the
3501 --flow of Unappling associated invoice from receipt.
3502
3503 OPEN get_applied_invoice_cnt(p_claim_rec.claim_id);
3504 FETCH get_applied_invoice_cnt INTO l_inv_count;
3505 CLOSE get_applied_invoice_cnt;
3506
3507 IF l_inv_count > 0 THEN --//Invoice exists with receipt
3508
3509 OPEN csr_claim_lines_ar(p_claim_rec.claim_id);
3510 LOOP
3511 FETCH csr_claim_lines_ar INTO l_lines_rec_ar;
3512 EXIT WHEN csr_claim_lines_ar%NOTFOUND;
3513
3514 Get_Inv_Credit_Details(
3515 p_claim_id => p_claim_rec.claim_id
3516 ,p_invoice_id => l_lines_rec_ar.source_object_id
3517 ,x_return_status => l_return_status
3518 ,x_line_credit => l_line_credit
3519 ,x_tax_credit => l_tax_credit
3520 ,x_freight_credit => l_freight_credit
3521 ,x_total_credit => l_total_credit
3522 ,x_cm_line_tbl => l_cm_line_tbl);
3523 IF l_return_status = FND_API.g_ret_sts_error THEN
3524 RAISE FND_API.g_exc_error;
3525 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3526 RAISE FND_API.g_exc_unexpected_error;
3527 END IF;
3528 -- Fix for Bug 13640947
3529 Pay_by_Single_Invoice_Credit(
3530 p_claim_rec => p_claim_rec
3531 ,p_sttlmnt_amt => l_lines_rec.amt
3532 ,p_invoice_id => 0--l_lines_rec_ar.applied_customer_trx_id
3533 ,p_pay_invoice_id => l_lines_rec_ar.source_object_id
3534 ,p_deduction_type => p_deduction_type
3535 ,p_line_credit => l_line_credit
3536 ,p_tax_Credit => l_tax_credit
3537 ,p_freight_credit => l_freight_credit
3538 ,p_total_credit => l_total_credit
3539 ,p_cm_line_tbl => l_cm_line_tbl
3540 ,x_return_status => l_return_status
3541 ,x_msg_data => x_msg_data
3542 ,x_msg_count => x_msg_count
3543 );
3544 IF l_return_status = FND_API.g_ret_sts_error THEN
3545 RAISE FND_API.g_exc_error;
3546 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3547 RAISE FND_API.g_exc_unexpected_error;
3548 END IF;
3549 END LOOP;
3550 CLOSE csr_claim_lines_ar;
3551
3552 ELSE -- l_inv_count = 0 THEN (No Invoice exists with Receipt)
3553
3554 OPEN csr_claim_lines(p_claim_rec.claim_id);
3555 LOOP
3556 FETCH csr_claim_lines INTO l_lines_rec;
3557 EXIT WHEN csr_claim_lines%NOTFOUND;
3558
3559 Get_Inv_Credit_Details(
3560 p_claim_id => p_claim_rec.claim_id
3561 ,p_invoice_id => l_lines_rec.source_object_id
3562 ,x_return_status => l_return_status
3563 ,x_line_credit => l_line_credit
3564 ,x_tax_credit => l_tax_credit
3565 ,x_freight_credit => l_freight_credit
3566 ,x_total_credit => l_total_credit
3567 ,x_cm_line_tbl => l_cm_line_tbl);
3568
3569 IF l_return_status = FND_API.g_ret_sts_error THEN
3570 RAISE FND_API.g_exc_error;
3571 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3572 RAISE FND_API.g_exc_unexpected_error;
3573 END IF;
3574
3575 Pay_by_Single_Invoice_Credit(
3576 p_claim_rec => p_claim_rec
3577 ,p_sttlmnt_amt => l_lines_rec.amt
3578 ,p_invoice_id => 0 --//Set to Zero, As there is no Associated invoice.
3579 ,p_pay_invoice_id => l_lines_rec.source_object_id
3580 ,p_deduction_type => p_deduction_type
3581 ,p_line_credit => l_line_credit
3582 ,p_tax_Credit => l_tax_credit
3583 ,p_freight_credit => l_freight_credit
3584 ,p_total_credit => l_total_credit
3585 ,p_cm_line_tbl => l_cm_line_tbl
3586 ,x_return_status => l_return_status
3587 ,x_msg_data => x_msg_data
3588 ,x_msg_count => x_msg_count
3589 );
3590 IF l_return_status = FND_API.g_ret_sts_error THEN
3591 RAISE FND_API.g_exc_error;
3592 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3593 RAISE FND_API.g_exc_unexpected_error;
3594 END IF;
3595 END LOOP;
3596 CLOSE csr_claim_lines;
3597 END IF;
3598
3599
3600 ELSIF p_deduction_type IN ('SOURCE_DED','CLAIM') THEN
3601
3602 OPEN csr_claim_lines(p_claim_rec.claim_id);
3603 LOOP
3604 FETCH csr_claim_lines INTO l_lines_rec;
3605 EXIT WHEN csr_claim_lines%NOTFOUND;
3606
3607 Get_Inv_Credit_Details(
3608 p_claim_id => p_claim_rec.claim_id
3609 ,p_invoice_id => l_lines_rec.source_object_id
3610 ,x_return_status => l_return_status
3611 ,x_line_credit => l_line_credit
3612 ,x_tax_credit => l_tax_credit
3613 ,x_freight_credit => l_freight_credit
3614 ,x_total_credit => l_total_credit
3615 ,x_cm_line_tbl => l_cm_line_tbl);
3616 IF l_return_status = FND_API.g_ret_sts_error THEN
3617 RAISE FND_API.g_exc_error;
3618 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3619 RAISE FND_API.g_exc_unexpected_error;
3620 END IF;
3621
3622 Pay_by_Single_Invoice_Credit(
3623 p_claim_rec => p_claim_rec
3624 ,p_sttlmnt_amt => l_lines_rec.amt
3625 ,p_invoice_id => l_lines_rec.source_object_id
3626 ,p_pay_invoice_id => l_lines_rec.source_object_id
3627 ,p_deduction_type => p_deduction_type
3628 ,p_line_credit => l_line_credit
3629 ,p_tax_Credit => l_tax_credit
3630 ,p_freight_credit => l_freight_credit
3631 ,p_total_credit => l_total_credit
3632 ,p_cm_line_tbl => l_cm_line_tbl
3633 ,x_return_status => l_return_status
3634 ,x_msg_data => x_msg_data
3635 ,x_msg_count => x_msg_count
3636 );
3637 IF l_return_status = FND_API.g_ret_sts_error THEN
3638 RAISE FND_API.g_exc_error;
3639 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3640 RAISE FND_API.g_exc_unexpected_error;
3641 END IF;
3642 END LOOP;
3643 CLOSE csr_claim_lines;
3644
3645 END IF;
3646 END IF;
3647
3648 ELSE --p_deduction_type NOT IN ('SOURCE_DED', 'RECEIPT_DED', 'CLAIM')
3649 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3650 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
3651 FND_MESSAGE.set_token('CLAIM_NUMBER', p_claim_rec.claim_number);
3652 FND_MSG_PUB.add;
3653 END IF;
3654 RAISE FND_API.g_exc_error;
3655 END IF;
3656 EXCEPTION
3657 WHEN FND_API.g_exc_error THEN
3658 x_return_status := FND_API.g_ret_sts_error;
3659 ROLLBACK TO Pay_by_Invoice_Credit;
3660
3661 WHEN FND_API.g_exc_unexpected_error THEN
3662 x_return_status := FND_API.g_ret_sts_unexp_error ;
3663 ROLLBACK TO Pay_by_Invoice_Credit;
3664
3665 WHEN OTHERS THEN
3666 x_return_status := FND_API.g_ret_sts_unexp_error ;
3667 ROLLBACK TO Pay_by_Invoice_Credit;
3668 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3669 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3670 END IF;
3671
3672 END Pay_By_Invoice_credit;
3673
3674
3675
3676 /*=======================================================================*
3677 | PROCEDURE
3678 | Pay_by_Credit_Memo
3679 |
3680 | NOTES
3681 |
3682 | HISTORY
3683 | 15-MAR-2002 mchang Create.
3684 *=======================================================================*/
3685 PROCEDURE Pay_by_Credit_Memo(
3686 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3687 ,p_deduction_type IN VARCHAR2
3688 ,p_payment_reference_id IN NUMBER
3689 ,p_credit_memo_amount IN NUMBER
3690
3691 ,x_return_status OUT NOCOPY VARCHAR2
3692 ,x_msg_data OUT NOCOPY VARCHAR2
3693 ,x_msg_count OUT NOCOPY NUMBER
3694 )
3695 IS
3696 l_api_version CONSTANT NUMBER := 1.0;
3697 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Credit_Memo';
3698 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3699 l_return_status VARCHAR2(1);
3700
3701 l_invoice_applied_count NUMBER;
3702 -- l_old_applied_amount NUMBER;
3703 l_new_applied_amount NUMBER;
3704 l_cm_customer_trx_id NUMBER;
3705 l_cm_amount NUMBER;
3706 l_online_upd_ded_status BOOLEAN := FALSE;
3707 l_orig_dispute_amount NUMBER;
3708 l_p_new_applied_amount NUMBER; --4684931
3709 l_p_new_applied_from_amount NUMBER;
3710 l_receipt_currency VARCHAR2(15);
3711 l_trx_currency VARCHAR2(15);
3712 l_cm_applied_on_rec_amt NUMBER;
3713 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
3714 l_cm_applied_on_rec_amt_from NUMBER;
3715
3716 -- Fix for Bug 7494234
3717 CURSOR csr_old_applied_invoice( cv_cash_receipt_id IN NUMBER
3718 , cv_customer_trx_id IN NUMBER
3719 , cv_root_claim_id IN NUMBER ) IS
3720 SELECT rec.application_ref_type
3721 , rec.application_ref_id
3722 , rec.application_ref_num
3723 , rec.secondary_application_ref_id
3724 , sum(rec.amount_applied) amount_applied
3725 , sum(rec.amount_applied_from) amount_applied_from --4684931
3726 FROM ar_receivable_applications rec
3727 , ar_payment_schedules pay
3728 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
3729 AND rec.cash_receipt_id = cv_cash_receipt_id
3730 AND pay.customer_trx_id = cv_customer_trx_id
3731 AND rec.application_ref_type = 'CLAIM'
3732 AND rec.display = 'Y'
3733 AND rec.secondary_application_ref_id = cv_root_claim_id
3734 group by rec.application_ref_type, rec.application_ref_id, rec.application_ref_num,
3735 rec.secondary_application_ref_id;
3736 /*SELECT rec.comments
3737 , rec.payment_set_id
3738 , rec.application_ref_type
3739 , rec.application_ref_id
3740 , rec.application_ref_num
3741 , rec.secondary_application_ref_id
3742 , rec.application_ref_reason
3743 , rec.customer_reference
3744 , rec.amount_applied
3745 , rec.amount_applied_from --4684931
3746 , pay.amount_due_remaining
3747 FROM ar_receivable_applications rec
3748 , ar_payment_schedules pay
3749 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
3750 AND rec.cash_receipt_id = cv_cash_receipt_id
3751 AND pay.customer_trx_id = cv_customer_trx_id
3752 AND rec.application_ref_type = 'CLAIM'
3753 AND rec.display = 'Y'
3754 AND rec.secondary_application_ref_id = cv_root_claim_id;
3755 */
3756
3757 l_old_applied_invoice csr_old_applied_invoice%ROWTYPE;
3758
3759 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
3760 SELECT amount_applied
3761 FROM ar_receivable_applications
3762 WHERE application_ref_type = 'CLAIM'
3763 AND applied_payment_schedule_id = -4
3764 AND display = 'Y'
3765 AND secondary_application_ref_id = cv_root_claim_id;
3766
3767 CURSOR csr_cm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
3768 SELECT amount_applied,
3769 amount_applied_from, --4684931
3770 apply_date -- Fix for Bug 3091401. TM passes old apply date
3771 FROM ar_receivable_applications
3772 WHERE cash_receipt_id = cv_cash_receipt_id
3773 AND applied_customer_trx_id = cv_customer_trx_id
3774 AND display = 'Y'
3775 AND status = 'APP';
3776
3777 --4684931
3778 CURSOR csr_trx_currency(cv_customer_trx_id IN NUMBER) IS
3779 SELECT invoice_currency_code
3780 FROM ra_customer_trx
3781 WHERE customer_trx_id = cv_customer_trx_id;
3782
3783 CURSOR csr_rec_currency(cv_cash_receipt_id IN NUMBER) IS
3784 SELECT currency_code
3785 FROM ar_cash_receipts
3786 WHERE cash_receipt_id = cv_cash_receipt_id;
3787
3788 l_settlement_amount NUMBER := NULL;
3789
3790 BEGIN
3791 -------------------- initialize -----------------------
3792 IF g_debug THEN
3793 OZF_Utility_PVT.debug_message(l_full_name||': start');
3794 END IF;
3795 x_return_status := FND_API.g_ret_sts_success;
3796
3797 ------------------------ start -------------------------
3798 IF p_payment_reference_id IS NULL OR
3799 p_payment_reference_id = FND_API.g_miss_num THEN
3800 /*------------------------------------------------------------*
3801 | No payment reference specified (No open credit memo specified) -> AutoInvoice
3802 *------------------------------------------------------------*/
3803 IF g_debug THEN
3804 OZF_Utility_PVT.debug_message('No payment reference specified (No open credit memo specified) -> AutoInvoice.');
3805 END IF;
3806 -- 1. AutoInvoice
3807 OZF_AR_INTERFACE_PVT.Interface_Claim(
3808 p_api_version => l_api_version
3809 ,p_init_msg_list => FND_API.g_false
3810 ,p_commit => FND_API.g_false
3811 ,p_validation_level => FND_API.g_valid_level_full
3812 ,x_return_status => l_return_status
3813 ,x_msg_data => x_msg_data
3814 ,x_msg_count => x_msg_count
3815 ,p_claim_id => p_claim_rec.claim_id
3816 );
3817 IF l_return_status = FND_API.g_ret_sts_error THEN
3818 RAISE FND_API.g_exc_error;
3819 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3820 RAISE FND_API.g_exc_unexpected_error;
3821 END IF;
3822 OZF_Utility_PVT.debug_message('Return Status after OZF_AR_INTERFACE_PVT.Interface_Claim:' || l_return_status);
3823 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
3824 l_online_upd_ded_status := FALSE;
3825
3826 ELSE -- payment_reference_id is not null and deduction_type IN ('SOURCE_DED', 'RECEIPT_DED')
3827 /*------------------------------------------------------------*
3828 | Update Claim Status to CLOSED.
3829 *------------------------------------------------------------*/
3830 Close_Claim(
3831 p_claim_rec => p_claim_rec
3832 ,x_return_status => l_return_status
3833 ,x_msg_data => x_msg_data
3834 ,x_msg_count => x_msg_count
3835 );
3836 IF l_return_status = FND_API.g_ret_sts_error THEN
3837 RAISE FND_API.g_exc_error;
3838 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3839 RAISE FND_API.g_exc_unexpected_error;
3840 END IF;
3841
3842 IF p_claim_rec.receipt_id IS NOT NULL THEN
3843 --//Buffix : 9873938
3844 --//OPEN csr_cm_exist_on_rec(p_claim_rec.receipt_id, p_claim_rec.payment_reference_id);
3845 OPEN csr_cm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
3846 FETCH csr_cm_exist_on_rec INTO l_cm_applied_on_rec_amt, l_cm_applied_on_rec_amt_from, l_apply_date; -- Fix for Bug 3091401. TM passes old apply date
3847 CLOSE csr_cm_exist_on_rec;
3848 END IF;
3849
3850 IF p_deduction_type = 'CLAIM' THEN
3851 l_online_upd_ded_status := TRUE;
3852
3853 ELSIF p_deduction_type = 'SOURCE_DED' THEN
3854 /*------------------------------------------------------------*
3855 | <<OLD>>
3856 | Invoice Deduction -> 1. Update amount in dispute
3857 | 2. Unapply invoice from receipt.
3858 | 3. Apply credit memo with amount_settled on receipt.
3859 | 4. Apply original invoice with old balance + amount_settled.
3860 | <<NEW: AR One off patch 2367036>>
3861 | Invoice Deduction -> 1. Update amount in dispute
3862 | 2. Apply credit memo with amount_settled on receipt.
3863 | 3. Reapply invoice related deduction.
3864 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
3865 | Invoice Deduction -> 1. Update amount in dispute
3866 | 1.5. Unapply existing credit memo
3867 | 2. ReApply credit memo with increase amount on receipt.
3868 | 3. Reapply invoice related deduction.
3869 *------------------------------------------------------------*/
3870
3871 OPEN csr_old_applied_invoice( p_claim_rec.receipt_id
3872 , p_claim_rec.source_object_id
3873 , p_claim_rec.root_claim_id
3874 );
3875 FETCH csr_old_applied_invoice INTO l_old_applied_invoice;
3876 CLOSE csr_old_applied_invoice;
3877
3878 --4684931
3879 OPEN csr_rec_currency(p_claim_rec.receipt_id);
3880 FETCH csr_rec_currency INTO l_receipt_currency;
3881 CLOSE csr_rec_currency;
3882
3883 OPEN csr_trx_currency(p_payment_reference_id);
3884 FETCH csr_trx_currency INTO l_trx_currency;
3885 CLOSE csr_trx_currency;
3886
3887 IF g_debug THEN
3888 OZF_Utility_PVT.debug_message('Invoice Deduction -> 1. Update amount in dispute');
3889 END IF;
3890 -- 1. Update amount in dispute
3891 Update_dispute_amount(
3892 p_claim_rec => p_claim_rec
3893 ,p_dispute_amount => (p_credit_memo_amount * -1)
3894 ,x_return_status => l_return_status
3895 ,x_msg_data => x_msg_data
3896 ,x_msg_count => x_msg_count
3897 );
3898 IF l_return_status = FND_API.g_ret_sts_error THEN
3899 RAISE FND_API.g_exc_error;
3900 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3901 RAISE FND_API.g_exc_unexpected_error;
3902 END IF;
3903
3904 IF l_cm_applied_on_rec_amt IS NULL THEN
3905 IF g_debug THEN
3906 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Apply creit memo on receipt');
3907 END IF;
3908 --4684931
3909 IF l_trx_currency = p_claim_rec.currency_code THEN
3910 l_p_new_applied_amount := p_credit_memo_amount * -1;
3911 l_p_new_applied_from_amount := NULL;
3912 /*
3913 ELSE
3914 ??
3915 */
3916 END IF;
3917
3918 -- 2. Apply creit memo on receipt
3919 Apply_on_Receipt(
3920 p_cash_receipt_id => p_claim_rec.receipt_id
3921 --,p_receipt_number => p_claim_rec.receipt_number
3922 ,p_customer_trx_id => p_payment_reference_id
3923 ,p_new_applied_amount => l_p_new_applied_amount --4684931
3924 ,p_new_applied_from_amount => l_p_new_applied_from_amount
3925 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
3926 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
3927 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
3928 ,x_return_status => l_return_status
3929 ,x_msg_data => x_msg_data
3930 ,x_msg_count => x_msg_count
3931 );
3932 IF l_return_status = FND_API.g_ret_sts_error THEN
3933 RAISE FND_API.g_exc_error;
3934 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3935 RAISE FND_API.g_exc_unexpected_error;
3936 END IF;
3937 ELSE
3938 IF g_debug THEN
3939 OZF_Utility_PVT.debug_message('Invoice Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
3940 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Reapply credit memo with new amount on receipt');
3941 END IF;
3942
3943 l_settlement_amount := (p_credit_memo_amount * -1) ; -- Bug4308188
3944
3945 --4684931
3946 IF l_trx_currency = p_claim_rec.currency_code THEN
3947 l_p_new_applied_amount := l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1);
3948 l_p_new_applied_from_amount := NULL;
3949 END IF;
3950
3951 -- 2. Reapply credit memo on receipt
3952 arp_deduction_cover2.reapply_credit_memo(
3953 p_customer_trx_id => p_payment_reference_id ,
3954 p_cash_receipt_id => p_claim_rec.receipt_id,
3955 p_amount_applied => l_p_new_applied_amount, --4684931
3956 p_init_msg_list => FND_API.g_false,
3957 x_return_status => l_return_status,
3958 x_msg_count => x_msg_count,
3959 x_msg_data => x_msg_data);
3960 IF l_return_status = FND_API.g_ret_sts_error THEN
3961 RAISE FND_API.g_exc_error;
3962 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3963 RAISE FND_API.g_exc_unexpected_error;
3964 END IF;
3965
3966 END IF;
3967
3968 IF g_debug THEN
3969 OZF_Utility_PVT.debug_message('Invoice Deduction -> 3. Unapply claim investigation');
3970 OZF_Utility_PVT.debug_message('original invoice deduction amount = '||l_old_applied_invoice.amount_applied);
3971 OZF_Utility_PVT.debug_message('reapply invoice deduction amount = '||(l_old_applied_invoice.amount_applied + p_credit_memo_amount));
3972 END IF;
3973 -- 3. Reapply claim investigation
3974 Unapply_Claim_Investigation(
3975 p_claim_rec => p_claim_rec
3976 ,p_reapply_amount => l_old_applied_invoice.amount_applied + p_credit_memo_amount
3977 ,x_return_status => l_return_status
3978 ,x_msg_data => x_msg_data
3979 ,x_msg_count => x_msg_count
3980 );
3981 IF l_return_status = FND_API.g_ret_sts_error THEN
3982 RAISE FND_API.g_exc_error;
3983 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3984 RAISE FND_API.g_exc_unexpected_error;
3985 END IF;
3986
3987 l_online_upd_ded_status := TRUE;
3988
3989 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
3990 --4684931
3991 l_receipt_currency := p_claim_rec.currency_code;
3992
3993 OPEN csr_trx_currency(p_payment_reference_id);
3994 FETCH csr_trx_currency INTO l_trx_currency;
3995 CLOSE csr_trx_currency;
3996
3997 IF l_cm_applied_on_rec_amt IS NULL THEN
3998 /*------------------------------------------------------------*
3999 | Receipt Deduction -> 1. Apply credit memo on receipt.
4000 | 2. Unapply claim investigation
4001 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
4002 | Receipt Deduction -> 0.5. Unapply credit memo on receipt
4003 | 1. Apply credit memo with increased amount on receipt
4004 | 2. Unapply claim investigation
4005 *------------------------------------------------------------*/
4006 IF g_debug THEN
4007 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Apply creit memo on receipt');
4008 END IF;
4009
4010 --4684931
4011 IF l_receipt_currency = l_trx_currency THEN
4012 l_p_new_applied_amount := p_credit_memo_amount * -1;
4013 l_p_new_applied_from_amount := NULL;
4014 ELSE
4015 l_p_new_applied_amount := NULL;
4016 l_p_new_applied_from_amount := p_credit_memo_amount * -1;
4017 END IF;
4018
4019 -- 1. Apply creit memo on receipt
4020 Apply_on_Receipt(
4021 p_cash_receipt_id => p_claim_rec.receipt_id
4022 ,p_customer_trx_id => p_payment_reference_id
4023 ,p_new_applied_amount => l_p_new_applied_amount --4684931
4024 ,p_new_applied_from_amount => l_p_new_applied_from_amount
4025 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
4026 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
4027 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
4028 ,x_return_status => l_return_status
4029 ,x_msg_data => x_msg_data
4030 ,x_msg_count => x_msg_count
4031 );
4032 IF l_return_status = FND_API.g_ret_sts_error THEN
4033 RAISE FND_API.g_exc_error;
4034 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4035 RAISE FND_API.g_exc_unexpected_error;
4036 END IF;
4037 IF g_debug THEN
4038 OZF_Utility_PVT.debug_message('Receipt Deduction -> 2. Unapply claim investigation');
4039 END IF;
4040 ELSE
4041 /*------------------------------------------------------------*
4042 | Receipt Deduction
4043 *------------------------------------------------------------*/
4044 IF g_debug THEN
4045 OZF_Utility_PVT.debug_message('Receipt Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
4046 END IF;
4047
4048 IF g_debug THEN
4049 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Reapply creit memo with increased amount on receipt');
4050 END IF;
4051
4052 l_settlement_amount := (p_credit_memo_amount * -1); -- Bug4308188
4053 --4684931
4054 IF l_receipt_currency = l_trx_currency THEN
4055 l_p_new_applied_amount := l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1);
4056 l_p_new_applied_from_amount := NULL;
4057 ELSE
4058 l_p_new_applied_amount := NULL;
4059 l_p_new_applied_from_amount := l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1);
4060 END IF;
4061
4062 arp_deduction_cover2.reapply_credit_memo(
4063 p_customer_trx_id => p_payment_reference_id ,
4064 p_cash_receipt_id => p_claim_rec.receipt_id,
4065 p_amount_applied => l_p_new_applied_amount,
4066 p_init_msg_list => FND_API.g_false,
4067 x_return_status => l_return_status,
4068 x_msg_count => x_msg_count,
4069 x_msg_data => x_msg_data);
4070 IF l_return_status = FND_API.g_ret_sts_error THEN
4071 RAISE FND_API.g_exc_error;
4072 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4073 RAISE FND_API.g_exc_unexpected_error;
4074 END IF;
4075 END IF;
4076
4077 -- 2. Unapply claim investigation
4078 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
4079 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
4080 CLOSE csr_claim_investigation_amount;
4081
4082 IF g_debug THEN
4083 OZF_Utility_PVT.debug_message('original claim investigation amount = '||l_orig_dispute_amount);
4084 OZF_Utility_PVT.debug_message('reapply claim investigation amount = '||(l_orig_dispute_amount + p_credit_memo_amount));
4085 END IF;
4086 Unapply_Claim_Investigation(
4087 p_claim_rec => p_claim_rec
4088 ,p_reapply_amount => l_orig_dispute_amount + p_credit_memo_amount --(l_orig_reapply_amount - p_credit_memo_amount) * -1
4089 ,x_return_status => l_return_status
4090 ,x_msg_data => x_msg_data
4091 ,x_msg_count => x_msg_count
4092 );
4093 IF l_return_status = FND_API.g_ret_sts_error THEN
4094 RAISE FND_API.g_exc_error;
4095 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4096 RAISE FND_API.g_exc_unexpected_error;
4097 END IF;
4098 l_online_upd_ded_status := TRUE;
4099 END IF; -- end if p_deduction_type
4100 END IF; -- end if payment_reference_id is null
4101
4102 /*------------------------------------------------------------*
4103 | Update Deduction payment detail
4104 *------------------------------------------------------------*/
4105 IF l_online_upd_ded_status THEN
4106 -- Update Deduction payment detail
4107 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
4108 p_api_version => l_api_version
4109 ,p_init_msg_list => FND_API.g_false
4110 ,p_commit => FND_API.g_false
4111 ,p_validation_level => FND_API.g_valid_level_full
4112 ,x_return_status => l_return_status
4113 ,x_msg_data => x_msg_data
4114 ,x_msg_count => x_msg_count
4115 ,p_claim_id => p_claim_rec.claim_id
4116 ,p_payment_method => p_claim_rec.payment_method
4117 ,p_deduction_type => p_deduction_type
4118 ,p_cash_receipt_id => p_claim_rec.receipt_id
4119 ,p_customer_trx_id => p_payment_reference_id
4120 ,p_adjust_id => NULL
4121 ,p_settlement_amount => l_settlement_amount -- Bug4308188
4122 );
4123 IF l_return_status = FND_API.g_ret_sts_error THEN
4124 RAISE FND_API.g_exc_error;
4125 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4126 RAISE FND_API.g_exc_unexpected_error;
4127 END IF;
4128 END IF;
4129
4130 IF g_debug THEN
4131 OZF_Utility_PVT.debug_message(l_full_name||': end');
4132 END IF;
4133 EXCEPTION
4134 WHEN FND_API.g_exc_error THEN
4135 x_return_status := FND_API.g_ret_sts_error;
4136
4137 WHEN FND_API.g_exc_unexpected_error THEN
4138 x_return_status := FND_API.g_ret_sts_unexp_error ;
4139
4140 WHEN OTHERS THEN
4141 x_return_status := FND_API.g_ret_sts_unexp_error ;
4142 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4143 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4144 END IF;
4145
4146 END Pay_by_Credit_Memo;
4147
4148
4149 /*=======================================================================*
4150 | PROCEDURE
4151 | Pay_by_On_Account_Credit
4152 |
4153 | NOTES
4154 |
4155 | HISTORY
4156 | 15-MAR-2002 mchang Create.
4157 *=======================================================================*/
4158 PROCEDURE Pay_by_On_Account_Credit(
4159 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
4160 ,p_deduction_type IN VARCHAR2
4161
4162 ,x_return_status OUT NOCOPY VARCHAR2
4163 ,x_msg_data OUT NOCOPY VARCHAR2
4164 ,x_msg_count OUT NOCOPY NUMBER
4165 )
4166 IS
4167 l_api_version CONSTANT NUMBER := 1.0;
4168 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_On_Account_Credit';
4169 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4170 l_return_status VARCHAR2(1);
4171
4172 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
4173 , cv_root_claim_id IN NUMBER) IS
4174 SELECT rec.amount_applied
4175 FROM ar_receivable_applications rec
4176 WHERE rec.applied_payment_schedule_id = -4
4177 AND rec.cash_receipt_id = cv_cash_receipt_id
4178 AND rec.application_ref_type = 'CLAIM'
4179 AND rec.display = 'Y'
4180 AND rec.secondary_application_ref_id = cv_root_claim_id;
4181
4182 l_old_applied_claim_amount NUMBER;
4183 l_reapply_claim_amount NUMBER;
4184
4185 BEGIN
4186 -------------------- initialize -----------------------
4187 IF g_debug THEN
4188 OZF_Utility_PVT.debug_message(l_full_name||': start');
4189 END IF;
4190
4191 x_return_status := FND_API.g_ret_sts_success;
4192
4193 ------------------------ start -------------------------
4194 /*------------------------------------------------------------*
4195 | Update Claim Status to CLOSED.
4196 *------------------------------------------------------------*/
4197 Close_Claim(
4198 p_claim_rec => p_claim_rec
4199 ,x_return_status => l_return_status
4200 ,x_msg_data => x_msg_data
4201 ,x_msg_count => x_msg_count
4202 );
4203 IF l_return_status = FND_API.g_ret_sts_error THEN
4204 RAISE FND_API.g_exc_error;
4205 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4206 RAISE FND_API.g_exc_unexpected_error;
4207 END IF;
4208
4209 IF p_deduction_type = 'RECEIPT_OPM' THEN
4210 /*------------------------------------------------------------*
4211 | Overpayment -> 1. Unapply claim investigation
4212 | 2. Apply On Account Credit
4213 *------------------------------------------------------------*/
4214 IF g_debug THEN
4215 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation.');
4216 END IF;
4217 -- 1. Unapply claim investigation
4218 OPEN csr_old_claim_investigation(p_claim_rec.receipt_id, p_claim_rec.root_claim_id);
4219 FETCH csr_old_claim_investigation INTO l_old_applied_claim_amount;
4220 CLOSE csr_old_claim_investigation;
4221
4222 l_reapply_claim_amount := l_old_applied_claim_amount - (p_claim_rec.amount_settled * -1);
4223
4224 Unapply_Claim_Investigation(
4225 p_claim_rec => p_claim_rec
4226 ,p_reapply_amount => l_reapply_claim_amount --0
4227 ,x_return_status => l_return_status
4228 ,x_msg_data => x_msg_data
4229 ,x_msg_count => x_msg_count
4230 );
4231 IF l_return_status = FND_API.g_ret_sts_error THEN
4232 RAISE FND_API.g_exc_error;
4233 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4234 RAISE FND_API.g_exc_unexpected_error;
4235 END IF;
4236
4237 IF g_debug THEN
4238 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply On Account Credit.');
4239 END IF;
4240 --2. Apply On Account Credit
4241 Apply_On_Account_Credit(
4242 p_claim_rec => p_claim_rec
4243 ,x_return_status => l_return_status
4244 ,x_msg_data => x_msg_data
4245 ,x_msg_count => x_msg_count
4246 );
4247 IF l_return_status = FND_API.g_ret_sts_error THEN
4248 RAISE FND_API.g_exc_error;
4249 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4250 RAISE FND_API.g_exc_unexpected_error;
4251 END IF;
4252
4253 /*------------------------------------------------------------*
4254 | Update Deduction payment detail
4255 *------------------------------------------------------------*/
4256 -- Update Deduction payment detail
4257 IF g_debug THEN
4258 OZF_Utility_PVT.debug_message('cash_receipt_id = '||p_claim_rec.receipt_id);
4259 END IF;
4260 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
4261 p_api_version => l_api_version
4262 ,p_init_msg_list => FND_API.g_false
4263 ,p_commit => FND_API.g_false
4264 ,p_validation_level => FND_API.g_valid_level_full
4265 ,x_return_status => l_return_status
4266 ,x_msg_data => x_msg_data
4267 ,x_msg_count => x_msg_count
4268 ,p_claim_id => p_claim_rec.claim_id
4269 ,p_payment_method => p_claim_rec.payment_method
4270 ,p_deduction_type => p_deduction_type
4271 ,p_cash_receipt_id => p_claim_rec.receipt_id
4272 ,p_customer_trx_id => NULL --p_claim_rec.payment_reference_id
4273 ,p_adjust_id => NULL
4274 );
4275 IF l_return_status = FND_API.g_ret_sts_error THEN
4276 RAISE FND_API.g_exc_error;
4277 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4278 RAISE FND_API.g_exc_unexpected_error;
4279 END IF;
4280 ELSE
4281 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4282 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
4283 FND_MSG_PUB.add;
4284 END IF;
4285 RAISE FND_API.g_exc_error;
4286 END IF;
4287
4288 IF g_debug THEN
4289 OZF_Utility_PVT.debug_message(l_full_name||': end');
4290 END IF;
4291 EXCEPTION
4292 WHEN FND_API.g_exc_error THEN
4293 x_return_status := FND_API.g_ret_sts_error;
4294
4295 WHEN FND_API.g_exc_unexpected_error THEN
4296 x_return_status := FND_API.g_ret_sts_unexp_error ;
4297
4298 WHEN OTHERS THEN
4299 x_return_status := FND_API.g_ret_sts_unexp_error ;
4300 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4301 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4302 END IF;
4303
4304 END Pay_by_On_Account_Credit;
4305
4306
4307 /*=======================================================================*
4308 | PROCEDURE
4309 | Pay_by_Chargeback
4310 |
4311 | NOTES
4312 |
4313 | HISTORY
4314 | 15-MAR-2002 mchang Create.
4315 *=======================================================================*/
4316 PROCEDURE Pay_by_Chargeback(
4317 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
4318 ,p_deduction_type IN VARCHAR2
4319
4320 ,x_return_status OUT NOCOPY VARCHAR2
4321 ,x_msg_data OUT NOCOPY VARCHAR2
4322 ,x_msg_count OUT NOCOPY NUMBER
4323 )
4324 IS
4325 l_api_version CONSTANT NUMBER := 1.0;
4326 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Chargeback';
4327 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4328 l_return_status VARCHAR2(1);
4329
4330 l_cb_customer_trx_id NUMBER;
4331 l_chargeback_amount NUMBER;
4332
4333 BEGIN
4334 -------------------- initialize -----------------------
4335 IF g_debug THEN
4336 OZF_Utility_PVT.debug_message(l_full_name||': start');
4337 END IF;
4338
4339 x_return_status := FND_API.g_ret_sts_success;
4340
4341 ------------------------ start -------------------------
4342 /*------------------------------------------------------------*
4343 | Update Claim Status to CLOSED.
4344 *------------------------------------------------------------*/
4345 Close_Claim(
4346 p_claim_rec => p_claim_rec
4347 ,x_return_status => l_return_status
4348 ,x_msg_data => x_msg_data
4349 ,x_msg_count => x_msg_count
4350 );
4351 IF l_return_status = FND_API.g_ret_sts_error THEN
4352 RAISE FND_API.g_exc_error;
4353 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4354 RAISE FND_API.g_exc_unexpected_error;
4355 END IF;
4356
4357 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED') THEN
4358 IF p_deduction_type = 'SOURCE_DED'THEN
4359 l_chargeback_amount := p_claim_rec.amount_settled;
4360 ELSIF p_deduction_type = 'RECEIPT_DED'THEN
4361 l_chargeback_amount := p_claim_rec.amount_settled * -1;
4362 END IF;
4363
4364 Create_AR_Chargeback(
4365 p_claim_rec => p_claim_rec
4366 ,p_chargeback_amount => l_chargeback_amount
4367 ,x_cb_customer_trx_id => l_cb_customer_trx_id
4368 ,x_return_status => l_return_status
4369 ,x_msg_data => x_msg_data
4370 ,x_msg_count => x_msg_count
4371 );
4372 IF l_return_status = FND_API.g_ret_sts_error THEN
4373 RAISE FND_API.g_exc_error;
4374 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4375 RAISE FND_API.g_exc_unexpected_error;
4376 END IF;
4377
4378 /*------------------------------------------------------------*
4379 | Update Deduction payment detail
4380 *------------------------------------------------------------*/
4381 IF l_cb_customer_trx_id IS NOT NULL THEN
4382 -- Update Deduction payment detail
4383 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
4384 p_api_version => l_api_version
4385 ,p_init_msg_list => FND_API.g_false
4386 ,p_commit => FND_API.g_false
4387 ,p_validation_level => FND_API.g_valid_level_full
4388 ,x_return_status => l_return_status
4389 ,x_msg_data => x_msg_data
4390 ,x_msg_count => x_msg_count
4391 ,p_claim_id => p_claim_rec.claim_id
4392 ,p_payment_method => p_claim_rec.payment_method
4393 ,p_deduction_type => p_deduction_type
4394 ,p_cash_receipt_id => p_claim_rec.receipt_id
4395 ,p_customer_trx_id => l_cb_customer_trx_id
4396 ,p_adjust_id => NULL
4397 );
4398 IF l_return_status = FND_API.g_ret_sts_error THEN
4399 RAISE FND_API.g_exc_error;
4400 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4401 RAISE FND_API.g_exc_unexpected_error;
4402 END IF;
4403 END IF;
4404
4405 ELSE
4406 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4407 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
4408 FND_MSG_PUB.add;
4409 END IF;
4410 END IF;
4411
4412
4413 IF g_debug THEN
4414 OZF_Utility_PVT.debug_message(l_full_name||': end');
4415 END IF;
4416 EXCEPTION
4417 WHEN FND_API.g_exc_error THEN
4418 x_return_status := FND_API.g_ret_sts_error;
4419
4420 WHEN FND_API.g_exc_unexpected_error THEN
4421 x_return_status := FND_API.g_ret_sts_unexp_error ;
4422
4423 WHEN OTHERS THEN
4424 x_return_status := FND_API.g_ret_sts_unexp_error ;
4425 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4426 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4427 END IF;
4428
4429 END Pay_by_Chargeback;
4430
4431
4432 /*=======================================================================*
4433 | PROCEDURE
4434 | Pay_by_Write_Off
4435 |
4436 | NOTES
4437 |
4438 | HISTORY
4439 | 15-MAR-2002 mchang Create.
4440 *=======================================================================*/
4441 PROCEDURE Pay_by_Write_Off(
4442 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
4443 ,p_deduction_type IN VARCHAR2
4444
4445 ,x_return_status OUT NOCOPY VARCHAR2
4446 ,x_msg_data OUT NOCOPY VARCHAR2
4447 ,x_msg_count OUT NOCOPY NUMBER
4448 )
4449 IS
4450 l_api_version CONSTANT NUMBER := 1.0;
4451 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Write_Off';
4452 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4453 l_return_status VARCHAR2(1);
4454
4455 l_wo_adjust_id NUMBER;
4456
4457 BEGIN
4458 -------------------- initialize -----------------------
4459 IF g_debug THEN
4460 OZF_Utility_PVT.debug_message(l_full_name||': start');
4461 END IF;
4462
4463 x_return_status := FND_API.g_ret_sts_success;
4464
4465 ------------------------ start -------------------------
4466 IF p_deduction_type = 'RECEIPT_DED' AND
4467 NOT ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed THEN
4468 /*------------------------------------------------------------
4469 | Receipt Deduction -> Invoke Settlement Workflow
4470 *-----------------------------------------------------------*/
4471 Process_Settlement_WF(
4472 p_claim_id => p_claim_rec.claim_id
4473 ,x_return_status => l_return_status
4474 ,x_msg_data => x_msg_data
4475 ,x_msg_count => x_msg_count
4476 );
4477 IF l_return_status = FND_API.g_ret_sts_error THEN
4478 RAISE FND_API.g_exc_error;
4479 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4480 RAISE FND_API.g_exc_unexpected_error;
4481 END IF;
4482
4483 ELSIF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED', 'RECEIPT_OPM','SOURCE_OPM') THEN
4484 /*------------------------------------------------------------*
4485 | Update Claim Status to CLOSED.
4486 *------------------------------------------------------------*/
4487 Close_Claim(
4488 p_claim_rec => p_claim_rec
4489 ,x_return_status => l_return_status
4490 ,x_msg_data => x_msg_data
4491 ,x_msg_count => x_msg_count
4492 );
4493 IF l_return_status = FND_API.g_ret_sts_error THEN
4494 RAISE FND_API.g_exc_error;
4495 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4496 RAISE FND_API.g_exc_unexpected_error;
4497 END IF;
4498
4499 Create_AR_Write_Off(
4500 p_claim_rec => p_claim_rec
4501 ,p_deduction_type => p_deduction_type
4502 ,p_write_off_amount => p_claim_rec.amount_settled
4503 ,x_wo_adjust_id => l_wo_adjust_id
4504 ,x_return_status => l_return_status
4505 ,x_msg_data => x_msg_data
4506 ,x_msg_count => x_msg_count
4507 );
4508 IF l_return_status = FND_API.g_ret_sts_error THEN
4509 RAISE FND_API.g_exc_error;
4510 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4511 RAISE FND_API.g_exc_unexpected_error;
4512 END IF;
4513 END IF;
4514
4515 IF g_debug THEN
4516 OZF_Utility_PVT.debug_message(l_full_name||': end');
4517 END IF;
4518 EXCEPTION
4519 WHEN FND_API.g_exc_error THEN
4520 x_return_status := FND_API.g_ret_sts_error;
4521
4522 WHEN FND_API.g_exc_unexpected_error THEN
4523 x_return_status := FND_API.g_ret_sts_unexp_error ;
4524
4525 WHEN OTHERS THEN
4526 x_return_status := FND_API.g_ret_sts_unexp_error ;
4527 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4528 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4529 END IF;
4530
4531 END Pay_by_Write_Off;
4532
4533
4534 /*=======================================================================*
4535 | PROCEDURE
4536 | Pay_by_Debit_Memo
4537 |
4538 | NOTES
4539 |
4540 | HISTORY
4541 | 15-MAR-2002 mchang Create.
4542 *=======================================================================*/
4543 PROCEDURE Pay_by_Debit_Memo(
4544 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
4545 ,p_deduction_type IN VARCHAR2
4546 ,p_payment_reference_id IN NUMBER
4547 ,p_debit_memo_amount IN NUMBER
4548
4549 ,x_return_status OUT NOCOPY VARCHAR2
4550 ,x_msg_data OUT NOCOPY VARCHAR2
4551 ,x_msg_count OUT NOCOPY NUMBER
4552 )
4553 IS
4554 l_api_version CONSTANT NUMBER := 1.0;
4555 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Debit_Memo';
4556 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4557 l_return_status VARCHAR2(1);
4558
4559 l_dm_trx_type_id NUMBER;
4560 l_online_upd_ded_status BOOLEAN := FALSE;
4561 l_orig_dispute_amount NUMBER;
4562 l_payment_trx_number VARCHAR2(30);
4563 l_p_new_applied_amount NUMBER;
4564 l_p_new_applied_from_amount NUMBER; --bug 4684931
4565 l_receipt_currency VARCHAR2(15);
4566 l_trx_currency VARCHAR2(15);
4567 l_dm_applied_on_rec_amt NUMBER ;
4568 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
4569 l_dm_applied_on_rec_amt_from NUMBER;
4570
4571 CURSOR csr_dm_trx_type_id(cv_claim_type_id IN NUMBER) IS
4572 SELECT dm_trx_type_id
4573 FROM ozf_claim_types_all_b
4574 WHERE claim_type_id = cv_claim_type_id;
4575
4576 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
4577 SELECT amount_applied
4578 FROM ar_receivable_applications
4579 WHERE application_ref_type = 'CLAIM'
4580 AND applied_payment_schedule_id = -4
4581 AND display = 'Y'
4582 AND secondary_application_ref_id = cv_root_claim_id;
4583
4584 CURSOR csr_payment_trx_number(cv_customer_trx_id IN NUMBER) IS
4585 SELECT trx_number
4586 FROM ra_customer_trx
4587 WHERE customer_trx_id = cv_customer_trx_id;
4588
4589 CURSOR csr_dm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
4590 SELECT amount_applied,
4591 amount_applied_from,--bug 4684931
4592 apply_date -- Fix for Bug 3091401. TM passes old apply date
4593 FROM ar_receivable_applications
4594 WHERE cash_receipt_id = cv_cash_receipt_id
4595 AND applied_customer_trx_id = cv_customer_trx_id
4596 AND display = 'Y'
4597 AND status = 'APP';
4598
4599 l_settlement_amount NUMBER := NULL ;
4600
4601 -- Start Fix for Bug4324426
4602 CURSOR csr_ded_details(p_customer_trx_id IN NUMBER) IS
4603 SELECT claim_id, amount_due_remaining FROM ozf_claims_all, ar_payment_schedules_all
4604 WHERE source_object_id = customer_trx_id
4605 AND source_object_class = class
4606 AND claim_class = 'DEDUCTION'
4607 AND cust_account_id = customer_id
4608 AND customer_trx_id = p_customer_trx_id;
4609
4610 --bug 4684931
4611 CURSOR csr_trx_currency(cv_customer_trx_id IN NUMBER) IS
4612 SELECT invoice_currency_code
4613 FROM ra_customer_trx
4614 WHERE customer_trx_id = cv_customer_trx_id;
4615
4616 CURSOR csr_rec_currency(cv_cash_receipt_id IN NUMBER) IS
4617 SELECT currency_code
4618 FROM ar_cash_receipts
4619 WHERE cash_receipt_id = cv_cash_receipt_id;
4620
4621 l_deduction_id NUMBER;
4622 l_amt_due_remaining NUMBER;
4623 l_object_ver_number NUMBER;
4624
4625 l_deduction_rec OZF_CLAIM_GRP.DEDUCTION_REC_TYPE;
4626 l_stlmnt_amount NUMBER;
4627 -- End Fix for Bug4324426
4628
4629 -- Added for ER#14109799
4630
4631 CURSOR csr_old_applied_invoice( cv_cash_receipt_id IN NUMBER
4632 , cv_customer_trx_id IN NUMBER
4633 , cv_root_claim_id IN NUMBER ) IS
4634 SELECT rec.application_ref_type
4635 , rec.application_ref_id
4636 , rec.application_ref_num
4637 , rec.secondary_application_ref_id
4638 , sum(rec.amount_applied) amount_applied
4639 , sum(rec.amount_applied_from) amount_applied_from --4684931
4640 FROM ar_receivable_applications rec
4641 , ar_payment_schedules pay
4642 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
4643 AND rec.cash_receipt_id = cv_cash_receipt_id
4644 AND pay.customer_trx_id = cv_customer_trx_id
4645 AND rec.application_ref_type = 'CLAIM'
4646 AND rec.display = 'Y'
4647 AND rec.secondary_application_ref_id = cv_root_claim_id
4648 group by rec.application_ref_type, rec.application_ref_id, rec.application_ref_num,
4649 rec.secondary_application_ref_id;
4650
4651 l_old_applied_invoice csr_old_applied_invoice%ROWTYPE;
4652
4653 CURSOR csr_claim_src(cv_claim_id IN NUMBER) IS
4654 SELECT source_object_class
4655 FROM ozf_claims_all
4656 WHERE claim_id = cv_claim_id;
4657
4658 CURSOR csr_receipt_amt(cv_claim_id IN NUMBER) IS
4659 SELECT amount_applied
4660 FROM ar_receivable_applications_all
4661 WHERE secondary_application_ref_id = cv_claim_id;
4662
4663 CURSOR csr_trx_amt(cv_customer_trx_id IN NUMBER) IS
4664 SELECT amount_applied
4665 FROM ar_payment_schedules_all
4666 WHERE customer_trx_id = cv_customer_trx_id;
4667
4668 l_source_obt_class VARCHAR2(10);
4669 l_receipt_amt NUMBER;
4670 l_trx_amt NUMBER;
4671
4672 l_out_rec_application_id NUMBER;
4673 l_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
4674 l_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
4675 l_cm_app_rec AR_CM_API_PUB.cm_app_rec_type;
4676
4677
4678
4679
4680 BEGIN
4681 -------------------- initialize -----------------------
4682 IF g_debug THEN
4683 OZF_Utility_PVT.debug_message(l_full_name||': start');
4684 END IF;
4685
4686 x_return_status := FND_API.g_ret_sts_success;
4687
4688 ------------------------ start -------------------------
4689 -- Added for ER#14109799
4690 IF p_deduction_type IN ('RECEIPT_OPM', 'CHARGE','SOURCE_OPM') THEN
4691 IF p_payment_reference_id IS NULL THEN
4692 /*------------------------------------------------------------*
4693 | OVERPAYMENT -> No open debit memo specified --> AutoInvoice
4694 *------------------------------------------------------------*/
4695 -- 1. AutoInvoice
4696 OZF_AR_INTERFACE_PVT.Interface_Claim(
4697 p_api_version => l_api_version
4698 ,p_init_msg_list => FND_API.g_false
4699 ,p_commit => FND_API.g_false
4700 ,p_validation_level => FND_API.g_valid_level_full
4701 ,x_return_status => l_return_status
4702 ,x_msg_data => x_msg_data
4703 ,x_msg_count => x_msg_count
4704 ,p_claim_id => p_claim_rec.claim_id
4705 );
4706 IF l_return_status = FND_API.g_ret_sts_error THEN
4707 RAISE FND_API.g_exc_error;
4708 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4709 RAISE FND_API.g_exc_unexpected_error;
4710 END IF;
4711
4712 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
4713 l_online_upd_ded_status := FALSE;
4714 ELSE
4715 /*------------------------------------------------------------*
4716 | Update Claim Status to CLOSED.
4717 *------------------------------------------------------------*/
4718 Close_Claim(
4719 p_claim_rec => p_claim_rec
4720 ,x_return_status => l_return_status
4721 ,x_msg_data => x_msg_data
4722 ,x_msg_count => x_msg_count
4723 );
4724 IF l_return_status = FND_API.g_ret_sts_error THEN
4725 RAISE FND_API.g_exc_error;
4726 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4727 RAISE FND_API.g_exc_unexpected_error;
4728 END IF;
4729
4730
4731 --bug 4684931
4732 l_receipt_currency := p_claim_rec.currency_code;
4733 OPEN csr_trx_currency(p_payment_reference_id);
4734 FETCH csr_trx_currency INTO l_trx_currency;
4735 CLOSE csr_trx_currency;
4736
4737 IF p_deduction_type = 'CHARGE' THEN
4738 l_online_upd_ded_status := TRUE;
4739
4740 ELSIF p_deduction_type = 'RECEIPT_OPM' THEN
4741 /*------------------------------------------------------------*
4742 | Overpayment -> 1. Apply debit memo on receipt.
4743 | 2. Unapply claim investigation
4744 | <<Pay by Previous Open Debit Memo which already exists on the receipt>>:
4745 | Overpayment -> 1. Unapply claim investigation
4746 | 1.5. Unapply debit memo on receipt
4747 | 2. Apply debit memo on receipt.
4748 *------------------------------------------------------------*/
4749 OPEN csr_dm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
4750 FETCH csr_dm_exist_on_rec INTO l_dm_applied_on_rec_amt, l_dm_applied_on_rec_amt_from, l_apply_date; -- Fix for Bug 3091401. TM passes old apply date
4751 CLOSE csr_dm_exist_on_rec;
4752
4753 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation');
4754 -- 1. Unapply claim investigation
4755 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
4756 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
4757 CLOSE csr_claim_investigation_amount;
4758
4759 OZF_Utility_PVT.debug_message('original overpayment amount = '||l_orig_dispute_amount);
4760 OZF_Utility_PVT.debug_message('reapply overpayment amount = '||(l_orig_dispute_amount + p_debit_memo_amount));
4761
4762 Unapply_Claim_Investigation(
4763 p_claim_rec => p_claim_rec
4764 ,p_reapply_amount => (l_orig_dispute_amount + p_debit_memo_amount)
4765 ,x_return_status => l_return_status
4766 ,x_msg_data => x_msg_data
4767 ,x_msg_count => x_msg_count
4768 );
4769 IF l_return_status = FND_API.g_ret_sts_error THEN
4770 RAISE FND_API.g_exc_error;
4771 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4772 RAISE FND_API.g_exc_unexpected_error;
4773 END IF;
4774
4775 l_stlmnt_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4776 IF l_dm_applied_on_rec_amt IS NULL THEN
4777 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo on receipt');
4778 --bug4684931
4779 IF l_receipt_currency = l_trx_currency THEN
4780 l_p_new_applied_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4781 l_p_new_applied_from_amount := NULL;
4782 ELSE
4783 l_p_new_applied_amount := NULL;
4784 l_p_new_applied_from_amount := NVL(l_dm_applied_on_rec_amt_from,0) + (p_debit_memo_amount * -1);
4785 END IF;
4786
4787 -- 2. Apply debit memo on receipt
4788 Apply_on_Receipt(
4789 p_cash_receipt_id => p_claim_rec.receipt_id
4790 ,p_customer_trx_id => p_payment_reference_id
4791 ,p_new_applied_amount => l_p_new_applied_amount --bug4684931
4792 ,p_new_applied_from_amount => l_p_new_applied_from_amount --bug4684931
4793 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
4794 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
4795 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
4796 ,x_return_status => l_return_status
4797 ,x_msg_data => x_msg_data
4798 ,x_msg_count => x_msg_count
4799 );
4800
4801 IF l_return_status = FND_API.g_ret_sts_error THEN
4802 RAISE FND_API.g_exc_error;
4803 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4804 RAISE FND_API.g_exc_unexpected_error;
4805 END IF;
4806 ELSE
4807 OZF_Utility_PVT.debug_message('Overpayment: Pay by Previous Open Debit Memo which already exists on the receipt');
4808 OZF_Utility_PVT.debug_message('Overpayment -> 1.5. Unapply debit memo on receipt');
4809 -- 1.5. Unapply creit memo on receipt
4810 Unapply_from_Receipt(
4811 p_cash_receipt_id => p_claim_rec.receipt_id
4812 ,p_customer_trx_id => p_payment_reference_id
4813 ,x_return_status => l_return_status
4814 ,x_msg_data => x_msg_data
4815 ,x_msg_count => x_msg_count
4816 );
4817 IF l_return_status = FND_API.g_ret_sts_error THEN
4818 RAISE FND_API.g_exc_error;
4819 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4820 RAISE FND_API.g_exc_unexpected_error;
4821 END IF;
4822
4823 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo with increased amount on receipt');
4824 l_settlement_amount := (p_debit_memo_amount * -1); -- Bug4308188
4825
4826 --bug 4684931
4827 IF l_receipt_currency = l_trx_currency THEN
4828 l_p_new_applied_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4829 l_p_new_applied_from_amount := NULL;
4830 ELSE
4831 l_p_new_applied_amount := NULL;
4832 l_p_new_applied_from_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4833 END IF;
4834
4835 -- 2. Apply creit memo on receipt
4836 Apply_on_Receipt(
4837 p_cash_receipt_id => p_claim_rec.receipt_id
4838 ,p_customer_trx_id => p_payment_reference_id
4839 ,p_new_applied_amount => l_p_new_applied_amount
4840 ,p_new_applied_from_amount => l_p_new_applied_from_amount
4841 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
4842 ,p_apply_date => l_apply_date -- Fix for Bug 3091401. TM passes old apply date
4843 ,p_claim_id => p_claim_rec.claim_id -- Added For Rule Based Settlement ER
4844 ,x_return_status => l_return_status
4845 ,x_msg_data => x_msg_data
4846 ,x_msg_count => x_msg_count
4847 );
4848 IF l_return_status = FND_API.g_ret_sts_error THEN
4849 RAISE FND_API.g_exc_error;
4850 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4851 RAISE FND_API.g_exc_unexpected_error;
4852 END IF;
4853 END IF;
4854
4855
4856 l_online_upd_ded_status := TRUE;
4857
4858 -- Added for ER#14109799
4859 ELSIF p_deduction_type = 'SOURCE_OPM' THEN
4860
4861 OPEN csr_old_applied_invoice( p_claim_rec.receipt_id
4862 , p_claim_rec.source_object_id
4863 , p_claim_rec.root_claim_id
4864 );
4865 FETCH csr_old_applied_invoice INTO l_old_applied_invoice;
4866 CLOSE csr_old_applied_invoice;
4867
4868
4869 OPEN csr_dm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
4870 FETCH csr_dm_exist_on_rec INTO l_dm_applied_on_rec_amt, l_dm_applied_on_rec_amt_from, l_apply_date;
4871 CLOSE csr_dm_exist_on_rec;
4872
4873 OPEN csr_rec_currency(p_claim_rec.receipt_id);
4874 FETCH csr_rec_currency INTO l_receipt_currency;
4875 CLOSE csr_rec_currency;
4876
4877 OPEN csr_trx_currency(p_payment_reference_id);
4878 FETCH csr_trx_currency INTO l_trx_currency;
4879 CLOSE csr_trx_currency;
4880
4881 OPEN csr_claim_src (p_claim_rec.claim_id);
4882 FETCH csr_claim_src INTO l_source_obt_class;
4883 CLOSE csr_claim_src;
4884
4885
4886 OPEN csr_receipt_amt (p_claim_rec.claim_id);
4887 FETCH csr_receipt_amt INTO l_receipt_amt;
4888 CLOSE csr_receipt_amt;
4889
4890
4891 IF (l_source_obt_class IS NOT NULL AND l_source_obt_class = 'CM' AND l_receipt_amt = 0) THEN
4892
4893 IF g_debug THEN
4894 fnd_file.put_line(fnd_file.log, 'Apply DM to the Rceipt Line and Rceipt Balance Adjustment is not required');
4895 END IF;
4896
4897
4898 l_cm_app_rec.cm_customer_trx_id := p_claim_rec.source_object_id;-- CM Trx number
4899 l_cm_app_rec.inv_customer_trx_id := p_payment_reference_id;-- DM trx number
4900 l_cm_app_rec.apply_date := sysdate;
4901 l_cm_app_rec.gl_date := p_claim_rec.gl_date;
4902 l_cm_app_rec.amount_applied := p_debit_memo_amount * -1;
4903
4904 -- API to apply the DM to the CM
4905 AR_CM_API_PUB.apply_on_account(
4906 p_api_version => l_api_version,
4907 p_init_msg_list => FND_API.g_false,
4908 p_commit => FND_API.G_FALSE,
4909 p_cm_app_rec => l_cm_app_rec,
4910 x_return_status => l_return_status,
4911 x_msg_count => x_msg_count,
4912 x_msg_data => x_msg_data,
4913 x_out_rec_application_id => l_out_rec_application_id,
4914 x_acctd_amount_applied_from => l_acctd_amount_applied_from,
4915 x_acctd_amount_applied_to => l_acctd_amount_applied_to,
4916 p_org_id => p_claim_rec.org_id);
4917
4918 IF g_debug THEN
4919 fnd_file.put_line(fnd_file.log, 'l_return_status from AR_CM_API_PUB.apply_on_account '||l_return_status);
4920 END IF;
4921
4922 IF l_return_status = FND_API.g_ret_sts_error THEN
4923 RAISE FND_API.g_exc_error;
4924 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4925 RAISE FND_API.g_exc_unexpected_error;
4926 END IF;
4927
4928
4929 ELSE
4930 -- Get the Transaction Amount
4931 OPEN csr_trx_amt (p_claim_rec.source_object_id);
4932 FETCH csr_trx_amt INTO l_trx_amt;
4933 CLOSE csr_trx_amt;
4934
4935 IF g_debug THEN
4936 fnd_file.put_line(fnd_file.log, 'l_trx_amt : '||l_trx_amt);
4937 END IF;
4938
4939 IF l_dm_applied_on_rec_amt IS NULL THEN
4940
4941 IF g_debug THEN
4942 fnd_file.put_line(fnd_file.log, 'Invoice Overpayment -> 1. Unapply Invoice from Receipt');
4943 END IF;
4944
4945 Unapply_from_Receipt(
4946 p_cash_receipt_id => p_claim_rec.receipt_id
4947 ,p_customer_trx_id => p_claim_rec.source_object_id
4948 ,x_return_status => l_return_status
4949 ,x_msg_data => x_msg_data
4950 ,x_msg_count => x_msg_count
4951 );
4952 IF l_return_status = FND_API.g_ret_sts_error THEN
4953 RAISE FND_API.g_exc_error;
4954 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4955 RAISE FND_API.g_exc_unexpected_error;
4956 END IF;
4957
4958 IF g_debug THEN
4959 fnd_file.put_line(fnd_file.log, 'Invoice Overpayment -> 2. Apply original amount - debit memo amount to receipt');
4960 END IF;
4961
4962
4963 IF l_receipt_currency = l_trx_currency THEN
4964 l_p_new_applied_amount := l_trx_amt - (p_debit_memo_amount * -1);
4965 l_p_new_applied_from_amount := NULL;
4966 ELSE
4967 l_p_new_applied_amount := NULL;
4968 l_p_new_applied_from_amount := l_trx_amt - (p_debit_memo_amount * -1);
4969 END IF;
4970
4971
4972 Apply_on_Receipt(
4973 p_cash_receipt_id => p_claim_rec.receipt_id
4974 ,p_customer_trx_id => p_claim_rec.source_object_id
4975 ,p_new_applied_amount => l_p_new_applied_amount
4976 ,p_new_applied_from_amount => l_p_new_applied_from_amount
4977 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
4978 ,p_customer_reference => p_claim_rec.customer_ref_number
4979 ,p_claim_id => p_claim_rec.claim_id
4980 ,x_return_status => l_return_status
4981 ,x_msg_data => x_msg_data
4982 ,x_msg_count => x_msg_count
4983 );
4984
4985
4986 IF l_return_status = FND_API.g_ret_sts_error THEN
4987 RAISE FND_API.g_exc_error;
4988 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4989 RAISE FND_API.g_exc_unexpected_error;
4990 END IF;
4991
4992
4993 IF g_debug THEN
4994 fnd_file.put_line(fnd_file.log, 'Invoice Overpayment -> 3. Apply debit memo amount to receipt');
4995 END IF;
4996
4997
4998 IF l_receipt_currency = l_trx_currency THEN
4999 l_p_new_applied_amount := p_debit_memo_amount * -1;
5000 l_p_new_applied_from_amount := NULL;
5001 ELSE
5002 l_p_new_applied_amount := NULL;
5003 l_p_new_applied_from_amount := p_debit_memo_amount * -1;
5004 END IF;
5005
5006
5007
5008 Apply_on_Receipt(
5009 p_cash_receipt_id => p_claim_rec.receipt_id
5010 ,p_customer_trx_id => p_payment_reference_id
5011 ,p_new_applied_amount => l_p_new_applied_amount
5012 ,p_new_applied_from_amount => l_p_new_applied_from_amount
5013 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
5014 ,p_customer_reference => p_claim_rec.customer_ref_number
5015 ,p_claim_id => p_claim_rec.claim_id
5016 ,x_return_status => l_return_status
5017 ,x_msg_data => x_msg_data
5018 ,x_msg_count => x_msg_count
5019 );
5020
5021 IF l_return_status = FND_API.g_ret_sts_error THEN
5022 RAISE FND_API.g_exc_error;
5023 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5024 RAISE FND_API.g_exc_unexpected_error;
5025 END IF;
5026
5027
5028 ELSE
5029
5030 IF g_debug THEN
5031 fnd_file.put_line(fnd_file.log, 'Overpayment: -> 1.1 Pay by Previous Open Debit Memo which already exists on the receipt');
5032 fnd_file.put_line(fnd_file.log, 'Overpayment: -> 1.1 Unapply the Invoice from Receipt');
5033 END IF;
5034
5035 Unapply_from_Receipt(
5036 p_cash_receipt_id => p_claim_rec.receipt_id
5037 ,p_customer_trx_id => p_payment_reference_id
5038 ,x_return_status => l_return_status
5039 ,x_msg_data => x_msg_data
5040 ,x_msg_count => x_msg_count
5041 );
5042
5043 IF l_return_status = FND_API.g_ret_sts_error THEN
5044 RAISE FND_API.g_exc_error;
5045 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5046 RAISE FND_API.g_exc_unexpected_error;
5047 END IF;
5048
5049 IF g_debug THEN
5050 fnd_file.put_line(fnd_file.log, 'Overpayment: -> 1.1 Apply the invoice amount - (existing debit memo amount + new debit memo amount) on receipt');
5051 END IF;
5052
5053 IF l_receipt_currency = l_trx_currency THEN
5054 l_p_new_applied_amount := l_trx_amt - (l_dm_applied_on_rec_amt + (p_debit_memo_amount * -1));
5055 l_p_new_applied_from_amount := NULL;
5056 ELSE
5057 l_p_new_applied_amount := NULL;
5058 l_p_new_applied_from_amount := l_trx_amt - (l_dm_applied_on_rec_amt + (p_debit_memo_amount * -1));
5059 END IF;
5060
5061 Apply_on_Receipt(
5062 p_cash_receipt_id => p_claim_rec.receipt_id
5063 --,p_receipt_number => p_claim_rec.receipt_number
5064 ,p_customer_trx_id => p_claim_rec.source_object_id
5065 ,p_new_applied_amount => l_p_new_applied_amount
5066 ,p_new_applied_from_amount => l_p_new_applied_from_amount
5067 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
5068 ,p_customer_reference => p_claim_rec.customer_ref_number
5069 ,p_claim_id => p_claim_rec.claim_id
5070 ,x_return_status => l_return_status
5071 ,x_msg_data => x_msg_data
5072 ,x_msg_count => x_msg_count
5073 );
5074
5075 IF l_return_status = FND_API.g_ret_sts_error THEN
5076 RAISE FND_API.g_exc_error;
5077 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5078 RAISE FND_API.g_exc_unexpected_error;
5079 END IF;
5080
5081 IF g_debug THEN
5082 fnd_file.put_line(fnd_file.log, 'Invoice Overpayment -> 1.3. Apply (existing debit memo amount + new debit memo amount) on receipt');
5083 END IF;
5084
5085 IF l_receipt_currency = l_trx_currency THEN
5086 l_p_new_applied_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
5087 l_p_new_applied_from_amount := NULL;
5088 ELSE
5089 l_p_new_applied_amount := NULL;
5090 l_p_new_applied_from_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
5091 END IF;
5092
5093 Apply_on_Receipt(
5094 p_cash_receipt_id => p_claim_rec.receipt_id
5095 ,p_customer_trx_id => p_payment_reference_id
5096 ,p_new_applied_amount => l_p_new_applied_amount
5097 ,p_new_applied_from_amount => l_p_new_applied_from_amount
5098 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
5099 ,p_customer_reference => p_claim_rec.customer_ref_number
5100 ,p_claim_id => p_claim_rec.claim_id
5101 ,x_return_status => l_return_status
5102 ,x_msg_data => x_msg_data
5103 ,x_msg_count => x_msg_count
5104 );
5105
5106 IF l_return_status = FND_API.g_ret_sts_error THEN
5107 RAISE FND_API.g_exc_error;
5108 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5109 RAISE FND_API.g_exc_unexpected_error;
5110 END IF;
5111
5112 END IF; -- For IF l_dm_applied_on_rec_amt IS NULL THEN
5113
5114 END IF; -- For Source Object Class Check
5115
5116 l_online_upd_ded_status := TRUE;
5117
5118 END IF; -- end of dedecution type check
5119 END IF;
5120 ELSE
5121 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5122 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
5123 FND_MSG_PUB.add;
5124 END IF;
5125 RAISE FND_API.g_exc_error;
5126 END IF;
5127
5128
5129 /*------------------------------------------------------------*
5130 | Update Deduction payment detail
5131 *------------------------------------------------------------*/
5132 IF l_online_upd_ded_status THEN
5133 -- Update Deduction payment detail
5134 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
5135 p_api_version => l_api_version
5136 ,p_init_msg_list => FND_API.g_false
5137 ,p_commit => FND_API.g_false
5138 ,p_validation_level => FND_API.g_valid_level_full
5139 ,x_return_status => l_return_status
5140 ,x_msg_data => x_msg_data
5141 ,x_msg_count => x_msg_count
5142 ,p_claim_id => p_claim_rec.claim_id
5143 ,p_payment_method => p_claim_rec.payment_method
5144 ,p_deduction_type => p_deduction_type
5145 ,p_cash_receipt_id => p_claim_rec.receipt_id
5146 ,p_customer_trx_id => p_payment_reference_id
5147 ,p_adjust_id => NULL
5148 ,p_settlement_amount => l_settlement_amount -- Bug4308188
5149 );
5150 IF l_return_status = FND_API.g_ret_sts_error THEN
5151 RAISE FND_API.g_exc_error;
5152 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5153 RAISE FND_API.g_exc_unexpected_error;
5154 END IF;
5155 END IF;
5156
5157 IF g_debug THEN
5158 OZF_Utility_PVT.debug_message(l_full_name||': end');
5159 END IF;
5160 EXCEPTION
5161 WHEN FND_API.g_exc_error THEN
5162 x_return_status := FND_API.g_ret_sts_error;
5163
5164 WHEN FND_API.g_exc_unexpected_error THEN
5165 x_return_status := FND_API.g_ret_sts_unexp_error ;
5166
5167 WHEN OTHERS THEN
5168 x_return_status := FND_API.g_ret_sts_unexp_error ;
5169 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5170 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
5171 END IF;
5172
5173 END Pay_by_Debit_Memo;
5174
5175
5176 /*=======================================================================*
5177 | PROCEDURE
5178 | Pay_by_Contra_Charge
5179 |
5180 | NOTES
5181 |
5182 | HISTORY
5183 | 15-MAR-2002 mchang Create.
5184 *=======================================================================*/
5185 PROCEDURE Pay_by_Contra_Charge(
5186 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
5187 ,p_deduction_type IN VARCHAR2
5188
5189 ,x_return_status OUT NOCOPY VARCHAR2
5190 ,x_msg_data OUT NOCOPY VARCHAR2
5191 ,x_msg_count OUT NOCOPY NUMBER
5192 )
5193 IS
5194 l_api_version CONSTANT NUMBER := 1.0;
5195 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Contra_Charge';
5196 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5197 l_return_status VARCHAR2(1);
5198
5199
5200 BEGIN
5201 -------------------- initialize -----------------------
5202 IF g_debug THEN
5203 OZF_Utility_PVT.debug_message(l_full_name||': start');
5204 END IF;
5205
5206 x_return_status := FND_API.g_ret_sts_success;
5207
5208 ------------------------ start -------------------------
5209 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED') THEN
5210 -- invoke claim settlement workflow
5211 BEGIN
5212 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
5213 p_claim_id => p_claim_rec.claim_id
5214 ,p_prev_status => 'APPROVED'
5215 ,p_curr_status => 'PENDING_CLOSE'
5216 ,p_next_status => 'CLOSED'
5217 );
5218 EXCEPTION
5219 WHEN OTHERS THEN
5220 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
5221 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
5222 FND_MSG_PUB.Add;
5223 RAISE FND_API.g_exc_unexpected_error;
5224 END;
5225 ELSE
5226 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5227 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
5228 FND_MSG_PUB.add;
5229 END IF;
5230 END IF;
5231
5232 IF g_debug THEN
5233 OZF_Utility_PVT.debug_message(l_full_name||': end');
5234 END IF;
5235 EXCEPTION
5236 WHEN FND_API.g_exc_error THEN
5237 x_return_status := FND_API.g_ret_sts_error;
5238
5239 WHEN FND_API.g_exc_unexpected_error THEN
5240 x_return_status := FND_API.g_ret_sts_unexp_error ;
5241
5242 WHEN OTHERS THEN
5243 x_return_status := FND_API.g_ret_sts_unexp_error ;
5244 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5245 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
5246 END IF;
5247
5248 END Pay_by_Contra_Charge;
5249
5250
5251 /*=======================================================================*
5252 | PROCEDURE
5253 | Create_AR_Payment
5254 |
5255 | NOTES
5256 |
5257 | HISTORY
5258 | 15-MAR-2002 mchang Create.
5259 *=======================================================================*/
5260 PROCEDURE Create_AR_Payment(
5261 p_api_version IN NUMBER
5262 ,p_init_msg_list IN VARCHAR2
5263 ,p_commit IN VARCHAR2
5264 ,p_validation_level IN NUMBER
5265
5266 ,x_return_status OUT NOCOPY VARCHAR2
5267 ,x_msg_data OUT NOCOPY VARCHAR2
5268 ,x_msg_count OUT NOCOPY NUMBER
5269
5270 ,p_claim_id IN NUMBER
5271 )
5272 IS
5273 l_api_version CONSTANT NUMBER := 1.0;
5274 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Payment';
5275 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5276 l_return_status VARCHAR2(1);
5277
5278 l_claim_rec OZF_Claim_PVT.claim_rec_type;
5279 l_deduction_type VARCHAR2(30) := NULL;
5280
5281 BEGIN
5282 -------------------- initialize -----------------------
5283 SAVEPOINT Create_AR_Payment;
5284
5285 IF g_debug THEN
5286 OZF_Utility_PVT.debug_message(l_full_name||': start');
5287 END IF;
5288
5289 IF FND_API.to_boolean(p_init_msg_list) THEN
5290 FND_MSG_PUB.initialize;
5291 END IF;
5292
5293 IF NOT FND_API.compatible_api_call(
5294 l_api_version,
5295 p_api_version,
5296 l_api_name,
5297 g_pkg_name
5298 ) THEN
5299 RAISE FND_API.g_exc_unexpected_error;
5300 END IF;
5301
5302 x_return_status := FND_API.g_ret_sts_success;
5303
5304 ------------------------ start -------------------------
5305 Query_Claim(
5306 p_claim_id => p_claim_id
5307 ,x_claim_rec => l_claim_rec
5308 ,x_return_status => l_return_status
5309 );
5310 IF l_return_status = FND_API.g_ret_sts_error THEN
5311 RAISE FND_API.g_exc_error;
5312 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5313 RAISE FND_API.g_exc_unexpected_error;
5314 END IF;
5315
5316 IF g_debug THEN
5317 OZF_Utility_PVT.debug_message('Create Payment for ==> '||l_claim_rec.claim_number);
5318 END IF;
5319
5320 IF l_claim_rec.claim_class = 'DEDUCTION' THEN
5321 IF l_claim_rec.source_object_class IS NOT NULL AND
5322 l_claim_rec.source_object_id IS NOT NULL AND
5323 l_claim_rec.source_object_number IS NOT NULL THEN
5324 l_deduction_type := 'SOURCE_DED';
5325 ELSE
5326 l_deduction_type := 'RECEIPT_DED';
5327 END IF;
5328 ELSIF l_claim_rec.claim_class = 'OVERPAYMENT' THEN
5329 IF l_claim_rec.source_object_class IS NOT NULL AND
5330 l_claim_rec.source_object_id IS NOT NULL AND
5331 l_claim_rec.source_object_number IS NOT NULL THEN
5332 l_deduction_type := 'SOURCE_OPM';
5333 ELSE
5334 l_deduction_type := 'RECEIPT_OPM';
5335 END IF;
5336 ELSIF l_claim_rec.claim_class = 'CLAIM' THEN
5337 l_deduction_type := 'CLAIM';
5338 ELSIF l_claim_rec.claim_class = 'CHARGE' THEN
5339 l_deduction_type := 'CHARGE';
5340 ELSE
5341 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5342 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_DED_TYPE_ERR');
5343 FND_MSG_PUB.add;
5344 END IF;
5345 END IF;
5346
5347 --R12.1 Enhancement start :Close the claim contains the payment method as ACCOUNTING_ONLY
5348 -- Introduced the class CHARGE for RMA internal Ship and Debit claim
5349 IF l_deduction_type IN ('CLAIM','CHARGE') THEN
5350 IF l_claim_rec.payment_method = 'ACCOUNTING_ONLY' THEN
5351
5352 Close_Claim(
5353 p_claim_rec => l_claim_rec
5354 ,x_return_status => l_return_status
5355 ,x_msg_data => x_msg_data
5356 ,x_msg_count => x_msg_count
5357 );
5358
5359 IF g_debug THEN
5360 OZF_Utility_PVT.debug_message('After calling the close_claim ==> '||l_return_status);
5361 OZF_Utility_PVT.debug_message('After calling the close_claim ==> '||l_claim_rec.claim_id);
5362 END IF;
5363 IF l_return_status = FND_API.g_ret_sts_error THEN
5364 RAISE FND_API.g_exc_error;
5365 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5366 RAISE FND_API.g_exc_unexpected_error;
5367 END IF;
5368
5369 IF g_debug THEN
5370 OZF_Utility_PVT.debug_message('Before calling the update_payment_detail ==> '||l_claim_rec.claim_id);
5371 END IF;
5372 -- To create the settlement doc.
5373 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
5374 p_api_version => l_api_version
5375 ,p_init_msg_list => FND_API.g_false
5376 ,p_commit => FND_API.g_false
5377 ,p_validation_level => FND_API.g_valid_level_full
5378 ,x_return_status => l_return_status
5379 ,x_msg_data => x_msg_data
5380 ,x_msg_count => x_msg_count
5381 ,p_claim_id => l_claim_rec.claim_id
5382 ,p_payment_method => 'ACCOUNTING_ONLY'
5383 ,p_deduction_type => 'CLAIM'
5384 ,p_cash_receipt_id => NULL
5385 ,p_customer_trx_id => NULL --l_claim_rec.source_object_id
5386 ,p_adjust_id => NULL
5387 );
5388 IF g_debug THEN
5389 OZF_Utility_PVT.debug_message('After calling update_payment_detail ==> '||l_return_status);
5390 OZF_Utility_PVT.debug_message('After calling update_payment_detail ==> '||l_claim_rec.claim_id);
5391 END IF;
5392 IF l_return_status = FND_API.g_ret_sts_error THEN
5393 RAISE FND_API.g_exc_error;
5394 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5395 RAISE FND_API.g_exc_unexpected_error;
5396 END IF;
5397 END IF;
5398 END IF;
5399 --R12.1 Enhancement end
5400
5401 /*IF l_deduction_type = 'SOURCE_OPM' THEN
5402 Process_Settlement_WF(
5403 p_claim_id => p_claim_id
5404 ,x_return_status => l_return_status
5405 ,x_msg_data => x_msg_data
5406 ,x_msg_count => x_msg_count
5407 );
5408 IF l_return_status = FND_API.g_ret_sts_error THEN
5409 RAISE FND_API.g_exc_error;
5410 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5411 RAISE FND_API.g_exc_unexpected_error;
5412 END IF;
5413 */
5414 --R12.1 Enhancement : Check for ACCOUNTING_ONLY
5415 --ELS
5416 IF l_claim_rec.payment_method <> 'ACCOUNTING_ONLY' THEN
5417 IF l_claim_rec.payment_method = 'REG_CREDIT_MEMO' THEN
5418 Pay_by_Invoice_Credit(
5419 p_claim_rec => l_claim_rec
5420 ,p_deduction_type => l_deduction_type
5421 ,x_return_status => l_return_status
5422 ,x_msg_data => x_msg_data
5423 ,x_msg_count => x_msg_count
5424 );
5425 IF l_return_status = FND_API.g_ret_sts_error THEN
5426 RAISE FND_API.g_exc_error;
5427 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5428 RAISE FND_API.g_exc_unexpected_error;
5429 END IF;
5430
5431 ELSIF l_claim_rec.payment_method in ( 'CREDIT_MEMO', 'PREV_OPEN_CREDIT') THEN
5432 Pay_by_Credit_Memo(
5433 p_claim_rec => l_claim_rec
5434 ,p_deduction_type => l_deduction_type
5435 ,p_payment_reference_id => l_claim_rec.payment_reference_id
5436 ,p_credit_memo_amount => l_claim_rec.amount_settled
5437 ,x_return_status => l_return_status
5438 ,x_msg_data => x_msg_data
5439 ,x_msg_count => x_msg_count
5440 );
5441
5442 IF l_return_status = FND_API.g_ret_sts_error THEN
5443 RAISE FND_API.g_exc_error;
5444 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5445 RAISE FND_API.g_exc_unexpected_error;
5446 END IF;
5447
5448 ELSIF l_claim_rec.payment_method = 'ON_ACCT_CREDIT' THEN
5449 Pay_by_On_Account_Credit(
5450 p_claim_rec => l_claim_rec
5451 ,p_deduction_type => l_deduction_type
5452 ,x_return_status => l_return_status
5453 ,x_msg_data => x_msg_data
5454 ,x_msg_count => x_msg_count
5455 );
5456 IF l_return_status = FND_API.g_ret_sts_error THEN
5457 RAISE FND_API.g_exc_error;
5458 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5459 RAISE FND_API.g_exc_unexpected_error;
5460 END IF;
5461
5462 ELSIF l_claim_rec.payment_method = 'CHARGEBACK' THEN
5463 Pay_by_Chargeback(
5464 p_claim_rec => l_claim_rec
5465 ,p_deduction_type => l_deduction_type
5466 ,x_return_status => l_return_status
5467 ,x_msg_data => x_msg_data
5468 ,x_msg_count => x_msg_count
5469 );
5470 IF l_return_status = FND_API.g_ret_sts_error THEN
5471 RAISE FND_API.g_exc_error;
5472 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5473 RAISE FND_API.g_exc_unexpected_error;
5474 END IF;
5475
5476 ELSIF (l_claim_rec.payment_method = 'WRITE_OFF')
5477 THEN
5478 Pay_by_Write_Off(
5479 p_claim_rec => l_claim_rec
5480 ,p_deduction_type => l_deduction_type
5481 ,x_return_status => l_return_status
5482 ,x_msg_data => x_msg_data
5483 ,x_msg_count => x_msg_count
5484 );
5485 IF l_return_status = FND_API.g_ret_sts_error THEN
5486 RAISE FND_API.g_exc_error;
5487 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5488 RAISE FND_API.g_exc_unexpected_error;
5489 END IF;
5490
5491 ELSIF l_claim_rec.payment_method IN ( 'DEBIT_MEMO', 'PREV_OPEN_DEBIT') THEN
5492 Pay_by_Debit_Memo(
5493 p_claim_rec => l_claim_rec
5494 ,p_deduction_type => l_deduction_type
5495 ,p_payment_reference_id => l_claim_rec.payment_reference_id
5496 ,p_debit_memo_amount => l_claim_rec.amount_settled
5497 ,x_return_status => l_return_status
5498 ,x_msg_data => x_msg_data
5499 ,x_msg_count => x_msg_count
5500 );
5501 IF l_return_status = FND_API.g_ret_sts_error THEN
5502 RAISE FND_API.g_exc_error;
5503 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5504 RAISE FND_API.g_exc_unexpected_error;
5505 END IF;
5506
5507 ELSIF l_claim_rec.payment_method = 'CONTRA_CHARGE' THEN
5508 Pay_by_Contra_Charge(
5509 p_claim_rec => l_claim_rec
5510 ,p_deduction_type => l_deduction_type
5511 ,x_return_status => l_return_status
5512 ,x_msg_data => x_msg_data
5513 ,x_msg_count => x_msg_count
5514 );
5515 IF l_return_status = FND_API.g_ret_sts_error THEN
5516 RAISE FND_API.g_exc_error;
5517 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5518 RAISE FND_API.g_exc_unexpected_error;
5519 END IF;
5520
5521 ELSE
5522 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5523 FND_MESSAGE.set_name('OZF', 'OZF_DED_PAYMETHOD_ERR');
5524 FND_MESSAGE.set_token('PAYMENT_METHOD', l_claim_rec.payment_method);
5525 FND_MSG_PUB.add;
5526 END IF;
5527 RAISE FND_API.g_exc_error;
5528 END IF;
5529
5530 END IF;
5531
5532 ------------------------ finish ------------------------
5533 FND_MSG_PUB.count_and_get(
5534 p_encoded => FND_API.g_false,
5535 p_count => x_msg_count,
5536 p_data => x_msg_data
5537 );
5538
5539 IF g_debug THEN
5540 OZF_Utility_PVT.debug_message(l_full_name ||': end');
5541 END IF;
5542
5543 EXCEPTION
5544 WHEN FND_API.g_exc_error THEN
5545 ROLLBACK TO Create_AR_Payment;
5546 x_return_status := FND_API.g_ret_sts_error;
5547 FND_MSG_PUB.count_and_get (
5548 p_encoded => FND_API.g_false
5549 ,p_count => x_msg_count
5550 ,p_data => x_msg_data
5551 );
5552
5553 WHEN FND_API.g_exc_unexpected_error THEN
5554 ROLLBACK TO Create_AR_Payment;
5555 x_return_status := FND_API.g_ret_sts_unexp_error ;
5556 FND_MSG_PUB.count_and_get (
5557 p_encoded => FND_API.g_false
5558 ,p_count => x_msg_count
5559 ,p_data => x_msg_data
5560 );
5561
5562 WHEN OTHERS THEN
5563 ROLLBACK TO Create_AR_Payment;
5564 x_return_status := FND_API.g_ret_sts_unexp_error ;
5565 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5566 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
5567 END IF;
5568 FND_MSG_PUB.count_and_get(
5569 p_encoded => FND_API.g_false
5570 ,p_count => x_msg_count
5571 ,p_data => x_msg_data
5572 );
5573
5574 END Create_AR_Payment;
5575
5576 /*======================================================================*
5577 | PROCEDURE
5578 | Pay_by_RMA_Inv_CM
5579 |
5580 | NOTES
5581 |
5582 | HISTORY
5583 | 22-NOV-2004 Sahana Created for Bug3951827
5584 *=======================================================================*/
5585 PROCEDURE Pay_by_RMA_Inv_CM(
5586 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
5587 ,p_credit_memo_amount IN NUMBER
5588
5589 ,x_return_status OUT NOCOPY VARCHAR2
5590 ,x_msg_data OUT NOCOPY VARCHAR2
5591 ,x_msg_count OUT NOCOPY NUMBER
5592 )
5593 IS
5594 l_api_version CONSTANT NUMBER := 1.0;
5595 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_RMA_Inv_CM';
5596 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5597 l_return_status VARCHAR2(1);
5598
5599 BEGIN
5600 -------------------- initialize -----------------------
5601 IF g_debug THEN
5602 OZF_Utility_PVT.debug_message(l_full_name||': start');
5603 END IF;
5604 x_return_status := FND_API.g_ret_sts_success;
5605
5606 /*------------------------------------------------------------*
5607 | Update Claim Status to CLOSED.
5608 *------------------------------------------------------------*/
5609
5610 Close_Claim(
5611 p_claim_rec => p_claim_rec
5612 ,x_return_status => l_return_status
5613 ,x_msg_data => x_msg_data
5614 ,x_msg_count => x_msg_count
5615 );
5616 IF l_return_status = FND_API.g_ret_sts_error THEN
5617 RAISE FND_API.g_exc_error;
5618 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5619 RAISE FND_API.g_exc_unexpected_error;
5620 END IF;
5621
5622
5623 -- Update amount in dispute
5624 Update_dispute_amount(
5625 p_claim_rec => p_claim_rec
5626 ,p_dispute_amount => (p_credit_memo_amount * -1)
5627 ,x_return_status => l_return_status
5628 ,x_msg_data => x_msg_data
5629 ,x_msg_count => x_msg_count
5630 );
5631 IF l_return_status = FND_API.g_ret_sts_error THEN
5632 RAISE FND_API.g_exc_error;
5633 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5634 RAISE FND_API.g_exc_unexpected_error;
5635 END IF;
5636
5637 IF g_debug THEN
5638 OZF_Utility_PVT.debug_message(l_full_name||': end');
5639 END IF;
5640
5641 EXCEPTION
5642 WHEN FND_API.g_exc_error THEN
5643 x_return_status := FND_API.g_ret_sts_error;
5644
5645 WHEN FND_API.g_exc_unexpected_error THEN
5646 x_return_status := FND_API.g_ret_sts_unexp_error ;
5647
5648 WHEN OTHERS THEN
5649 x_return_status := FND_API.g_ret_sts_unexp_error ;
5650 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5651 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
5652 END IF;
5653
5654 END Pay_by_RMA_Inv_CM;
5655
5656 END OZF_AR_PAYMENT_PVT;