[Home] [Help]
PACKAGE BODY: APPS.OZF_AR_PAYMENT_PVT
Source
1 PACKAGE BODY OZF_AR_PAYMENT_PVT AS
2 /* $Header: ozfvarpb.pls 120.15.12010000.5 2008/11/06 06:46:58 kpatro 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
469 --Start:Bug:2781186
470 --Cursor to get customer reason,customer ref, reason code id
471 CURSOR csr_get_more_root_clm_dtls (cv_claim_id IN NUMBER) IS
472 SELECT claim_number, claim_id, customer_ref_number
473 FROM ozf_claims_all
474 WHERE claim_id = cv_claim_id;
475
476 --Cursor to get customer reason,customer ref, reason code id in case of child cliam
477 CURSOR csr_get_more_chld_clm_dtls (cv_claim_id IN NUMBER) IS
478 SELECT p.claim_number, p.claim_id, c.customer_ref_number
479 FROM ozf_claims c , ozf_claims p
480 WHERE c.claim_id = cv_claim_id
481 AND c.root_claim_id = p.claim_id;
482 --End:Bug:2781186
483
484 BEGIN
485 -------------------- initialize -----------------------
486 IF g_debug THEN
487 OZF_Utility_PVT.debug_message(l_full_name||': start');
488 END IF;
489
490 x_return_status := FND_API.g_ret_sts_success;
491
492 --Bug:2781186 Get more claim details
493 IF p_claim_rec.claim_id = p_claim_rec.root_claim_id THEN
494 OPEN csr_get_more_root_clm_dtls(p_claim_rec.claim_id);
495 FETCH csr_get_more_root_clm_dtls INTO l_application_ref_num,l_secondary_appl_ref_id,l_customer_reference;
496 CLOSE csr_get_more_root_clm_dtls;
497 ELSE
498 OPEN csr_get_more_chld_clm_dtls(p_claim_rec.claim_id);
499 FETCH csr_get_more_chld_clm_dtls INTO l_application_ref_num,l_secondary_appl_ref_id,l_customer_reference;
500 CLOSE csr_get_more_chld_clm_dtls;
501 END IF;
502
503 l_amount_applied := NVL(p_credit_amount, p_claim_rec.amount_settled) * -1;
504
505 ------------------------ start -------------------------
506 AR_RECEIPT_API_COVER.Apply_on_account(
507 -- Standard API parameters
508 p_api_version => l_api_version,
509 p_init_msg_list => FND_API.g_false,
510 p_commit => FND_API.g_false,
511 p_validation_level => FND_API.g_valid_level_full,
512 x_return_status => l_return_status,
513 x_msg_count => x_msg_count,
514 x_msg_data => x_msg_data,
515 -- Receipt application parameters.
516 p_cash_receipt_id => p_claim_rec.receipt_id,
517 p_receipt_number => NULL, --p_claim_rec.receipt_number,
518 p_amount_applied => l_amount_applied,
519 --p_apply_date => SYSDATE, --AR should default
520 --p_apply_gl_date => p_claim_rec.gl_date, --11.5.10 Enhancements. AR should default
521 --p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
522 --p_attribute_rec IN attribute_rec_type DEFAULT attribute_rec_const,
523 -- Global Flexfield parameters
524 --p_global_attribute_rec IN global_attribute_rec_type DEFAULT global_attribute_rec_const,
525 p_comments => SUBSTRB(p_claim_rec.comments, 1, 240),
526 p_application_ref_num => l_application_ref_num,
527 p_secondary_application_ref_id => l_secondary_appl_ref_id,
528 p_customer_reference => l_customer_reference --11.5.10 enhancements. TM should pass.
529 );
530 IF l_return_status = FND_API.g_ret_sts_error THEN
531 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
532 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_REC_APPACC_ERR');
533 FND_MSG_PUB.add;
534 END IF;
535 RAISE FND_API.g_exc_error;
536 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
537 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
538 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_REC_APPACC_UERR');
539 FND_MSG_PUB.add;
540 END IF;
541 RAISE FND_API.g_exc_unexpected_error;
542 END IF;
543
544 IF g_debug THEN
545 OZF_Utility_PVT.debug_message(l_full_name||': end');
546 END IF;
547 EXCEPTION
548 WHEN FND_API.g_exc_error THEN
549 x_return_status := FND_API.g_ret_sts_error;
550
551 WHEN FND_API.g_exc_unexpected_error THEN
552 x_return_status := FND_API.g_ret_sts_unexp_error ;
553
554 WHEN OTHERS THEN
555 x_return_status := FND_API.g_ret_sts_unexp_error ;
556 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
557 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
558 END IF;
559
560 END Apply_On_Account_Credit;
561
562
563 /*=======================================================================*
564 | PROCEDURE
565 | Unapply_from_Receipt
566 |
567 | NOTES
568 |
569 | HISTORY
570 | 15-MAR-2002 mchang Create.
571 *=======================================================================*/
572 PROCEDURE Unapply_from_Receipt(
573 p_cash_receipt_id IN NUMBER
574 ,p_customer_trx_id IN NUMBER
575
576 ,x_return_status OUT NOCOPY VARCHAR2
577 ,x_msg_data OUT NOCOPY VARCHAR2
578 ,x_msg_count OUT NOCOPY NUMBER
579 )
580 IS
581 l_api_version CONSTANT NUMBER := 1.0;
582 l_api_name CONSTANT VARCHAR2(30) := 'Unapply_from_Receipt';
583 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
584 l_return_status VARCHAR2(1);
585
586 l_payment_schedule_id NUMBER;
587
588 CURSOR csr_payment_schedule(cv_customer_trx_id IN NUMBER) IS
589 SELECT payment_schedule_id
590 FROM ar_payment_schedules
591 WHERE customer_trx_id = cv_customer_trx_id;
592
593 BEGIN
594 -------------------- initialize -----------------------
595 IF g_debug THEN
596 OZF_Utility_PVT.debug_message(l_full_name||': start');
597 END IF;
598
599 x_return_status := FND_API.g_ret_sts_success;
600
601 ------------------------ start -------------------------
602 OPEN csr_payment_schedule(p_customer_trx_id);
603 FETCH csr_payment_schedule INTO l_payment_schedule_id;
604 CLOSE csr_payment_schedule;
605
606 AR_RECEIPT_API_COVER.Unapply(
607 -- Standard API parameters
608 p_api_version => l_api_version,
609 p_init_msg_list => FND_API.g_false,
610 p_commit => FND_API.g_false,
611 p_validation_level => FND_API.g_valid_level_full,
612 x_return_status => l_return_status,
613 x_msg_count => x_msg_count,
614 x_msg_data => x_msg_data,
615 -- Receipt Info. parameters
616 p_receipt_number => NULL,
617 p_cash_receipt_id => p_cash_receipt_id,
618 p_trx_number => NULL,
619 p_customer_trx_id => p_customer_trx_id,
620 p_installment => NULL,
621 p_applied_payment_schedule_id => l_payment_schedule_id,
622 p_receivable_application_id => NULL,
623 p_reversal_gl_date => NULL,
624 p_called_from => NULL,
625 p_cancel_claim_flag => 'N'
626 );
627 IF l_return_status = FND_API.g_ret_sts_error THEN
628 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
629 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_UNAPP_ERR');
630 FND_MSG_PUB.add;
631 END IF;
632 RAISE FND_API.g_exc_error;
633 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
634 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
635 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_UNAPP_UERR');
636 FND_MSG_PUB.add;
637 END IF;
638 RAISE FND_API.g_exc_unexpected_error;
639 END IF;
640
641 IF g_debug THEN
642 OZF_Utility_PVT.debug_message(l_full_name||': end');
643 END IF;
644 EXCEPTION
645 WHEN FND_API.g_exc_error THEN
646 x_return_status := FND_API.g_ret_sts_error;
647
648 WHEN FND_API.g_exc_unexpected_error THEN
649 x_return_status := FND_API.g_ret_sts_unexp_error ;
650
651 WHEN OTHERS THEN
652 x_return_status := FND_API.g_ret_sts_unexp_error ;
653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
654 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
655 END IF;
656
657 END Unapply_from_Receipt;
658
659
660 /*=======================================================================*
661 | PROCEDURE
662 | Apply_on_Receipt
663 |
664 | NOTES
665 |
666 | HISTORY
667 | 15-MAR-2002 mchang Create.
668 *=======================================================================*/
669 PROCEDURE Apply_on_Receipt(
670 p_cash_receipt_id IN NUMBER
671 ,p_receipt_number IN VARCHAR2
672 ,p_customer_trx_id IN NUMBER
673 ,p_trx_number IN VARCHAR2
674 ,p_new_applied_amount IN NUMBER
675 ,p_new_applied_from_amount IN NUMBER --4684931
676 ,p_comments IN VARCHAR2
677 ,p_payment_set_id IN NUMBER
678 ,p_application_ref_type IN VARCHAR2
679 ,p_application_ref_id IN NUMBER
680 ,p_application_ref_num IN VARCHAR2
681 ,p_secondary_application_ref_id IN NUMBER
682 ,p_application_ref_reason IN VARCHAR2
683 ,p_customer_reference IN VARCHAR2
684 ,p_apply_date IN DATE -- Fix for Bug 3091401. TM passes old apply date
685 ,x_return_status OUT NOCOPY VARCHAR2
686 ,x_msg_data OUT NOCOPY VARCHAR2
687 ,x_msg_count OUT NOCOPY NUMBER
688 )
689 IS
690 l_api_version CONSTANT NUMBER := 1.0;
691 l_api_name CONSTANT VARCHAR2(30) := 'Apply_on_Receipt';
692 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
693 l_return_status VARCHAR2(1);
694
695 l_payment_schedule_id NUMBER;
696 l_balance_amount NUMBER;
697
698 CURSOR csr_payment_schedule(cv_customer_trx_id IN NUMBER) IS
699 SELECT payment_schedule_id
700 FROM ar_payment_schedules
701 WHERE customer_trx_id = cv_customer_trx_id;
702
703 CURSOR csr_trx_balance(cv_customer_trx_id IN NUMBER) IS
704 SELECT ABS(amount_due_remaining)
705 FROM ar_payment_schedules
706 WHERE customer_trx_id = cv_customer_trx_id;
707
708 BEGIN
709 -------------------- initialize -----------------------
710 IF g_debug THEN
711 OZF_Utility_PVT.debug_message(l_full_name||': start');
712 END IF;
713
714 x_return_status := FND_API.g_ret_sts_success;
715
716 ------------------------ start -------------------------
717 -- CM/DM open balance checking
718 OPEN csr_trx_balance(p_customer_trx_id);
719 FETCH csr_trx_balance INTO l_balance_amount;
720 CLOSE csr_trx_balance;
721
722 IF ABS(p_new_applied_amount) > l_balance_amount THEN
723 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
724 FND_MESSAGE.set_name('OZF', 'OZF_SETL_TRX_BAL_ERR');
725 FND_MESSAGE.set_token('APPLY_AMT', p_new_applied_amount);
726 FND_MSG_PUB.add;
727 END IF;
728 RAISE FND_API.G_EXC_ERROR;
729 END IF;
730
731 OPEN csr_payment_schedule(p_customer_trx_id);
732 FETCH csr_payment_schedule INTO l_payment_schedule_id;
733 CLOSE csr_payment_schedule;
734
735 IF g_debug THEN
736 OZF_Utility_PVT.debug_message('cash_receipt_id='||p_cash_receipt_id);
737 OZF_Utility_PVT.debug_message('receipt_number='||p_receipt_number);
738 OZF_Utility_PVT.debug_message('customer_trx_id='||p_customer_trx_id);
739 OZF_Utility_PVT.debug_message('trx_number='||p_trx_number);
740 OZF_Utility_PVT.debug_message('applied_payment_schedule_id='||l_payment_schedule_id);
741 OZF_Utility_PVT.debug_message('new_applied_amount='||p_new_applied_amount);
742 OZF_Utility_PVT.debug_message('new_applied_from_amount='||p_new_applied_from_amount); --4684931
743 END IF;
744
745 AR_RECEIPT_API_COVER.Apply(
746 -- Standard API parameters.
747 p_api_version => l_api_version,
748 p_init_msg_list => FND_API.g_false,
749 p_commit => FND_API.g_false,
750 p_validation_level => FND_API.g_valid_level_full,
751 x_return_status => l_return_status,
752 x_msg_count => x_msg_count,
753 x_msg_data => x_msg_data,
754 -- Receipt application parameters.
755 p_cash_receipt_id => p_cash_receipt_id,
756 p_receipt_number => p_receipt_number, --NULL,
757 p_customer_trx_id => p_customer_trx_id,
758 p_trx_number => p_trx_number, --NULL,
759 p_installment => NULL,
760 p_applied_payment_schedule_id => NULL, --l_payment_schedule_id,
761 p_amount_applied => p_new_applied_amount,
762 p_amount_applied_from => p_new_applied_from_amount,--4684931
763 p_apply_date => p_apply_date, -- Fix for Bug 3091401. TM passes old apply date
764 -- this is the allocated receipt amount
765 /*
766 p_amount_applied_from IN ar_receivable_applications.amount_applied_from%TYPE DEFAULT NULL,
767 p_trans_to_receipt_rate IN ar_receivable_applications.trans_to_receipt_rate%TYPE DEFAULT NULL,
768 p_discount IN ar_receivable_applications.earned_discount_taken%TYPE DEFAULT NULL,
769 p_apply_date IN ar_receivable_applications.apply_date%TYPE DEFAULT NULL,
770 p_apply_gl_date IN ar_receivable_applications.gl_date%TYPE DEFAULT NULL,
771 p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
772 p_customer_trx_line_id IN ar_receivable_applications.applied_customer_trx_line_id%TYPE DEFAULT NULL,
773 p_line_number IN ra_customer_trx_lines.line_number%TYPE DEFAULT NULL,
774 p_show_closed_invoices IN VARCHAR2 DEFAULT 'FALSE',
775 p_called_from IN VARCHAR2 DEFAULT NULL,
776 p_move_deferred_tax IN VARCHAR2 DEFAULT 'Y',
777 p_link_to_trx_hist_id IN ar_receivable_applications.link_to_trx_hist_id%TYPE DEFAULT NULL,
778 p_attribute_rec IN attribute_rec_type DEFAULT attribute_rec_const,
779 */
780 -- ******* Global Flexfield parameters *******
781 --p_global_attribute_rec => p_global_attribute_rec,
782 p_comments => p_comments,
783 p_payment_set_id => p_payment_set_id,
784 p_application_ref_type => p_application_ref_type,
785 p_application_ref_id => p_application_ref_id,
786 p_application_ref_num => p_application_ref_num,
787 p_secondary_application_ref_id => p_secondary_application_ref_id,
788 p_application_ref_reason => p_application_ref_reason,
789 p_customer_reference => p_customer_reference,
790 p_called_from => NULL
791 );
792 IF l_return_status = FND_API.g_ret_sts_error THEN
793 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
794 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_APP_ERR');
795 FND_MSG_PUB.add;
796 END IF;
797 RAISE FND_API.g_exc_error;
798 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
799 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
800 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_REC_APP_UERR');
801 FND_MSG_PUB.add;
802 END IF;
803 RAISE FND_API.g_exc_unexpected_error;
804 END IF;
805
806 IF g_debug THEN
807 OZF_Utility_PVT.debug_message(l_full_name||': end');
808 END IF;
809 EXCEPTION
810 WHEN FND_API.g_exc_error THEN
811 x_return_status := FND_API.g_ret_sts_error;
812
813 WHEN FND_API.g_exc_unexpected_error THEN
814 x_return_status := FND_API.g_ret_sts_unexp_error ;
815
816 WHEN OTHERS THEN
817 x_return_status := FND_API.g_ret_sts_unexp_error ;
818 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
819 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
820 END IF;
821
822 END Apply_on_Receipt;
823
824
825 /*=======================================================================*
826 | PROCEDURE
827 | Update_Dispute_Amount
828 |
829 | NOTES
830 |
831 | HISTORY
832 | 15-MAR-2002 mchang Create.
833 *=======================================================================*/
834 PROCEDURE Update_dispute_amount(
835 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
836 ,p_dispute_amount IN NUMBER
837 ,x_return_status OUT NOCOPY VARCHAR2
838 ,x_msg_data OUT NOCOPY VARCHAR2
839 ,x_msg_count OUT NOCOPY NUMBER
840 )
841 IS
842 l_api_version CONSTANT NUMBER := 1.0;
843 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dispute_Amount';
844 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
845 l_return_status VARCHAR2(1);
846
847 l_root_claim_number VARCHAR2(30);
848
849 CURSOR csr_root_claim_number(cv_root_claim_id IN NUMBER) IS
850 SELECT claim_number
851 FROM ozf_claims
852 WHERE claim_id = cv_root_claim_id;
853
854 BEGIN
855 -------------------- initialize -----------------------
856 IF g_debug THEN
857 OZF_Utility_PVT.debug_message(l_full_name||': start');
858 END IF;
859
860 x_return_status := FND_API.g_ret_sts_success;
861
862 ------------------------ start -------------------------
863 IF p_claim_rec.claim_id = p_claim_rec.root_claim_id THEN
864 l_root_claim_number := p_claim_rec.claim_number;
865 ELSE
866 OPEN csr_root_claim_number(p_claim_rec.root_claim_id);
867 FETCH csr_root_claim_number INTO l_root_claim_number;
868 CLOSE csr_root_claim_number;
869 END IF;
870
871 ARP_DEDUCTION_COVER.update_amount_in_dispute(
872 p_customer_trx_id => p_claim_rec.source_object_id,
873 p_claim_number => l_root_claim_number,
874 p_amount => p_dispute_amount,
875 p_init_msg_list => FND_API.g_false,
876 x_return_status => l_return_status,
877 x_msg_count => x_msg_count,
878 x_msg_data => x_msg_data
879 );
880 IF l_return_status = FND_API.g_ret_sts_error THEN
881 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
882 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_UPD_DISPUTE_ERR');
883 FND_MSG_PUB.add;
884 END IF;
885 RAISE FND_API.g_exc_error;
886 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
887 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
888 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_UPD_DISPUTE_UERR');
889 FND_MSG_PUB.add;
890 END IF;
891 RAISE FND_API.g_exc_unexpected_error;
892 END IF;
893
894 IF g_debug THEN
895 OZF_Utility_PVT.debug_message(l_full_name||': end');
896 END IF;
897 EXCEPTION
898 WHEN FND_API.g_exc_error THEN
899 x_return_status := FND_API.g_ret_sts_error;
900
901 WHEN FND_API.g_exc_unexpected_error THEN
902 x_return_status := FND_API.g_ret_sts_unexp_error ;
903
904 WHEN OTHERS THEN
905 x_return_status := FND_API.g_ret_sts_unexp_error ;
906 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
907 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
908 END IF;
909
910 END Update_Dispute_Amount;
911
912
913 /*=======================================================================*
914 | PROCEDURE
915 | Create_AR_Credit_Memo
916 |
917 | NOTES
918 |
919 | HISTORY
920 | 15-MAR-2002 mchang Create.
921 *=======================================================================*/
922 PROCEDURE Create_AR_Credit_Memo(
923 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
924 ,p_customer_trx_id IN NUMBER
925 ,p_deduction_type IN VARCHAR2
926 ,p_line_remaining IN NUMBER
927 ,p_tax_remaining IN NUMBER
928 ,p_freight_remaining IN NUMBER
929 ,p_line_credit IN NUMBER
930 ,p_tax_credit IN NUMBER
931 ,p_freight_credit IN NUMBER
932 ,p_total_credit IN NUMBER
933 ,p_cm_line_tbl IN AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE
934 ,p_upd_dispute_flag IN VARCHAR2
935 ,x_cm_customer_trx_id OUT NOCOPY NUMBER
936 ,x_cm_amount OUT NOCOPY NUMBER
937 ,x_return_status OUT NOCOPY VARCHAR2
938 ,x_msg_data OUT NOCOPY VARCHAR2
939 ,x_msg_count OUT NOCOPY NUMBER
940 )
941 IS
942 l_api_version CONSTANT NUMBER := 1.0;
943 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Credit_Memo';
944 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
945 l_return_status VARCHAR2(1);
946
947 l_batch_source_name VARCHAR2(50);
948 l_reason_code VARCHAR2(30);
949 l_request_id NUMBER;
950 l_cm_line_tbl AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE;
951 l_line_credit_flag VARCHAR2(1) := 'N';
952 l_line_amount NUMBER;
953 l_tax_amount NUMBER;
954 l_freight_amount NUMBER;
955
956 l_inv_line_amount NUMBER;
957 l_inv_tax_amount NUMBER;
958 l_inv_freight_amount NUMBER;
959
960 l_total_credit NUMBER;
961 l_root_claim_number VARCHAR2(30);
962 l_credit_installments VARCHAR2(30);
963 l_credit_rules VARCHAR2(30);
964
965 l_x_status_meaning VARCHAR2(60);
966 l_x_reason_meaning VARCHAR2(60);
967 l_x_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
968 --l_x_cm_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE:
969 l_x_line_amount RA_CM_REQUESTS.line_amount%TYPE;
970 l_x_tax_amount RA_CM_REQUESTS.tax_amount%TYPE;
971 l_x_freight_amount RA_CM_REQUESTS.freight_amount%TYPE;
972 l_x_line_credits_flag VARCHAR2(1);
973 l_x_created_by WF_USERS.display_name%TYPE;
974 l_x_creation_date DATE;
975 l_x_approval_date DATE;
976 l_x_comments RA_CM_REQUESTS.comments%TYPE;
977 l_x_cm_line_tbl AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE;
978 l_x_cm_activity_tbl AR_CREDIT_MEMO_API_PUB.x_cm_activity_tbl%TYPE;
979 l_x_cm_notes_tbl AR_CREDIT_MEMO_API_PUB.x_cm_notes_tbl%TYPE;
980
981 CURSOR csr_batch_source(cv_set_of_books_id IN NUMBER) IS
982 SELECT name
983 FROM ra_batch_sources bs
984 , ozf_sys_parameters sys
985 WHERE sys.batch_source_id = bs.batch_source_id
986 AND sys.set_of_books_id = cv_set_of_books_id;
987
988 CURSOR csr_reason_code(cv_reason_code_id IN NUMBER) IS
989 SELECT reason_code
990 FROM ozf_reason_codes_all_b
991 WHERE reason_code_id = cv_reason_code_id;
992
993 CURSOR csr_root_claim_number(cv_root_claim_id IN NUMBER) IS
994 SELECT claim_number
995 FROM ozf_claims
996 WHERE claim_id = cv_root_claim_id;
997
998 CURSOR csr_credit_amount(cv_customer_trx_id IN NUMBER) IS
999 SELECT SUM(NVL(amount_line_items_remaining, 0))
1000 , SUM(NVL(tax_remaining, 0))
1001 , SUM(NVL(freight_remaining, 0))
1002 FROM ar_payment_schedules
1003 WHERE customer_trx_id = cv_customer_trx_id;
1004
1005 BEGIN
1006 -------------------- initialize -----------------------
1007 IF g_debug THEN
1008 OZF_Utility_PVT.debug_message(l_full_name||': start');
1009 END IF;
1010
1011 x_return_status := FND_API.g_ret_sts_success;
1012
1013 ------------------------ start -------------------------
1014 /* Logic Changed for Bug3963604 */
1015 IF p_total_credit = 0 AND p_line_credit = 0 AND
1016 p_tax_credit = 0 AND p_freight_credit = 0 AND p_cm_line_tbl IS NOT NULL THEN
1017 -- Line Level Credit Memo
1018 l_cm_line_tbl := p_cm_line_tbl;
1019 l_line_amount := NULL;
1020 l_tax_amount := NULL;
1021 l_freight_amount := NULL;
1022 l_line_credit_flag := 'Y';
1023 ELSE
1024 l_cm_line_tbl(1).customer_trx_line_id := NULL;
1025 l_cm_line_tbl(1).extended_amount := NULL;
1026 l_cm_line_tbl(1).quantity_credited :=NULL;
1027 l_cm_line_tbl(1).price := NULL;
1028 l_line_credit_flag := 'N';
1029
1030 IF p_total_credit = 0 AND
1031 (p_line_credit <> 0 OR p_tax_credit <> 0 OR p_freight_credit <> 0) THEN
1032 -- Header Level Credit Memo with Credit to Line/Tax/Freight
1033 l_line_amount := p_line_credit * -1;
1034 l_tax_amount := p_tax_credit * -1;
1035 l_freight_amount := p_freight_credit * -1;
1036 ELSE
1037 -- Header Level Credit Memo. Modified for Bug4308173
1038 OPEN csr_credit_amount(p_customer_trx_id);
1039 FETCH csr_credit_amount INTO l_inv_line_amount , l_inv_tax_amount , l_inv_freight_amount;
1040 CLOSE csr_credit_amount;
1041
1042 l_total_credit := p_total_credit;
1043 l_line_amount := LEAST(l_total_credit,l_inv_line_amount);
1044 l_total_credit := l_total_credit - l_line_amount;
1045
1046 IF l_total_credit > 0 THEN
1047 l_tax_amount := LEAST(l_total_credit,l_inv_tax_amount);
1048 l_total_credit := l_total_credit - l_tax_amount;
1049 END IF;
1050
1051 IF l_total_credit > 0 THEN
1052 l_freight_amount := LEAST(l_total_credit,l_inv_freight_amount);
1053 END IF;
1054
1055 l_line_amount := l_line_amount * -1;
1056 l_tax_amount := l_tax_amount * -1;
1057 l_freight_amount := l_freight_amount * -1;
1058 END IF;
1059 END IF;
1060
1061
1062 IF g_debug THEN
1063 OZF_Utility_PVT.debug_message(l_full_name||': request credit memo amount = '||p_claim_rec.amount_settled);
1064 OZF_Utility_PVT.debug_message(l_full_name||': request credit to line amount = '||l_line_amount);
1065 OZF_Utility_PVT.debug_message(l_full_name||': request credit to tax amount = '||l_tax_amount);
1066 OZF_Utility_PVT.debug_message(l_full_name||': request credit to freight amount = '||l_freight_amount);
1067 OZF_Utility_PVT.debug_message(l_full_name||': Line Level Credit = '||l_line_credit_flag);
1068 END IF;
1069
1070 OPEN csr_batch_source(p_claim_rec.set_of_books_id);
1071 FETCH csr_batch_source INTO l_batch_source_name;
1072 CLOSE csr_batch_source;
1073
1074 OPEN csr_reason_code(p_claim_rec.reason_code_id);
1075 FETCH csr_reason_code INTO l_reason_code;
1076 CLOSE csr_reason_code;
1077
1078
1079 l_credit_installments := NVL(FND_PROFILE.value('OZF_CLAIM_CREDIT_METHOD_INSTALLMENT'), 'PRORATE');
1080 l_credit_rules := NVL(FND_PROFILE.value('OZF_CLAIM_CREDIT_METHOD_RULE'), 'PRORATE');
1081
1082
1083 /*------------------------------------------------------------*
1084 | 1. Create a credit memo in AR
1085 *------------------------------------------------------------*/
1086 AR_CREDIT_MEMO_API_PUB.create_request (
1087 -- standard API parameters
1088 p_api_version => l_api_version,
1089 p_init_msg_list => FND_API.G_FALSE,
1090 p_commit => FND_API.G_FALSE,
1091 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1092 x_return_status => l_return_status,
1093 x_msg_count => x_msg_count,
1094 x_msg_data => x_msg_data,
1095 -- credit memo request parameters
1096 p_customer_trx_id => p_customer_trx_id,
1097 p_line_credit_flag => l_line_credit_flag,
1098 p_line_amount => l_line_amount,
1099 p_tax_amount => l_tax_amount,
1100 p_freight_amount => l_freight_amount,
1101 p_cm_reason_code => l_reason_code,
1102 p_comments => SUBSTRB(p_claim_rec.comments, 1, 240),
1103 p_orig_trx_number => NULL,--p_claim_rec.source_object_number,
1104 p_tax_ex_cert_num => NULL,
1105 p_request_url => NULL,
1106 p_transaction_url => NULL,
1107 p_trans_act_url => NULL,
1108 p_cm_line_tbl => l_cm_line_tbl,
1109 p_skip_workflow_flag => 'Y',
1110 p_credit_method_installments => l_credit_installments,
1111 p_credit_method_rules => l_credit_rules,
1112 p_batch_source_name => l_batch_source_name,
1113 x_request_id => l_request_id
1114 );
1115 IF l_return_status = FND_API.g_ret_sts_error THEN
1116 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1117 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CM_REQ_ERR');
1118 FND_MSG_PUB.add;
1119 END IF;
1120 RAISE FND_API.g_exc_error;
1121 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1122 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1123 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CM_REQ_UERR');
1124 FND_MSG_PUB.add;
1125 END IF;
1126 RAISE FND_API.g_exc_unexpected_error;
1127 END IF;
1128
1129 /*------------------------------------------------------------*
1130 | 2. Get creidt memo request information
1131 *------------------------------------------------------------*/
1132 AR_CREDIT_MEMO_API_PUB.get_request_status(
1133 -- standard API parameters
1134 p_api_version => l_api_version,
1135 p_init_msg_list => FND_API.G_false,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data,
1138 x_return_status => l_return_status,
1139 -- credit memo request parameters
1140 p_request_id => l_request_id,
1141 x_status_meaning => l_x_status_meaning,
1142 x_reason_meaning => l_x_reason_meaning,
1143 x_customer_trx_id => l_x_customer_trx_id,
1144 x_cm_customer_trx_id => x_cm_customer_trx_id,
1145 x_line_amount => l_x_line_amount,
1146 x_tax_amount => l_x_tax_amount,
1147 x_freight_amount => l_x_freight_amount,
1148 x_line_credits_flag => l_x_line_credits_flag,
1149 x_created_by => l_x_created_by,
1150 x_creation_date => l_x_creation_date,
1151 x_approval_date => l_x_approval_date,
1152 x_comments => l_x_comments,
1153 x_cm_line_tbl => l_x_cm_line_tbl,
1154 x_cm_activity_tbl => l_x_cm_activity_tbl,
1155 x_cm_notes_tbl => l_x_cm_notes_tbl
1156 );
1157 IF l_return_status = FND_API.g_ret_sts_error THEN
1158 RAISE FND_API.g_exc_error;
1159 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1160 RAISE FND_API.g_exc_unexpected_error;
1161 END IF;
1162
1163 IF x_cm_customer_trx_id IS NULL THEN
1164 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1165 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CRE_ARCM_ERR');
1166 FND_MSG_PUB.add;
1167 END IF;
1168 RAISE FND_API.g_exc_error;
1169 -- [04/29/2002]: due to the rollback problem on ar credit memo api, instead of
1170 -- raising error to rollback, calling settlement workflow to proceed.
1171 BEGIN
1172 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
1173 p_claim_id => p_claim_rec.claim_id
1174 ,p_prev_status => 'OPEN' -- hard code
1175 ,p_curr_status => 'PENDING_CLOSE'
1176 ,p_next_status => 'CLOSED'
1177 );
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1181 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1182 FND_MSG_PUB.Add;
1183 RAISE FND_API.g_exc_unexpected_error;
1184 END;
1185 END IF;
1186
1187 x_cm_amount := l_x_line_amount + l_x_tax_amount + l_x_freight_amount;
1188
1189 IF g_debug THEN
1190 OZF_Utility_PVT.debug_message(l_full_name||': credit memo id => '||x_cm_customer_trx_id);
1191 OZF_Utility_PVT.debug_message(l_full_name||': credit memo amount => '||x_cm_amount);
1192 END IF;
1193
1194 --IF p_deduction_type = 'SOURCE_DED' THEN
1195 IF p_upd_dispute_flag = FND_API.g_true THEN
1196 /*------------------------------------------------------------*
1197 | 3. For Invoice Deduction only -> Taking invoice out of dispute
1198 *------------------------------------------------------------*/
1199 Update_dispute_amount(
1200 p_claim_rec => p_claim_rec
1201 ,p_dispute_amount => x_cm_amount
1202 ,x_return_status => l_return_status
1203 ,x_msg_data => x_msg_data
1204 ,x_msg_count => x_msg_count
1205 );
1206 IF l_return_status = FND_API.g_ret_sts_error THEN
1207 RAISE FND_API.g_exc_error;
1208 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1209 RAISE FND_API.g_exc_unexpected_error;
1210 END IF;
1211 END IF; -- end if p_deduction_type = 'SOURCE_DED'
1212
1213 IF g_debug THEN
1214 OZF_Utility_PVT.debug_message(l_full_name||': end');
1215 END IF;
1216 EXCEPTION
1217 WHEN FND_API.g_exc_error THEN
1218 x_return_status := FND_API.g_ret_sts_error;
1219
1220 WHEN FND_API.g_exc_unexpected_error THEN
1221 x_return_status := FND_API.g_ret_sts_unexp_error ;
1222
1223 WHEN OTHERS THEN
1224 x_return_status := FND_API.g_ret_sts_unexp_error ;
1225 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1226 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1227 END IF;
1228
1229 END Create_AR_Credit_Memo;
1230
1231
1232 /*=======================================================================*
1233 | PROCEDURE
1234 | Create_AR_Chargeback
1235 |
1236 | NOTES
1237 |
1238 | HISTORY
1239 | 15-MAR-2002 mchang Create.
1240 *=======================================================================*/
1241 PROCEDURE Create_AR_Chargeback(
1242 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1243 ,p_chargeback_amount IN NUMBER
1244 ,p_gl_date IN DATE
1245
1246 ,x_cb_customer_trx_id OUT NOCOPY NUMBER
1247 ,x_return_status OUT NOCOPY VARCHAR2
1248 ,x_msg_data OUT NOCOPY VARCHAR2
1249 ,x_msg_count OUT NOCOPY NUMBER
1250 )
1251 IS
1252 l_api_version CONSTANT NUMBER := 1.0;
1253 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Chargeback';
1254 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1255 l_return_status VARCHAR2(1);
1256
1257 l_chargeback_rec ARP_CHARGEBACK_COVER.Chargeback_Rec_Type;
1258 l_x_doc_seq_id NUMBER;
1259 l_x_doc_seq_value NUMBER;
1260 l_x_trx_number VARCHAR2(20);
1261 l_reason_type VARCHAR2(30);
1262 l_reason_code VARCHAR2(30);
1263 l_cb_trx_type_id NUMBER;
1264 l_check_inv_bal BOOLEAN;
1265 l_cb_ref_field VARCHAR2(80);
1266 l_reason_code_id NUMBER; --Bug:2781186
1267 l_gl_date_open_count NUMBER := 1;
1268
1269 -- Cust_trx_type_id for Chargeback
1270 CURSOR csr_cust_trx_type(cv_claim_type_id IN NUMBER) IS
1271 SELECT cb_trx_type_id
1272 FROM ozf_claim_types_vl
1273 WHERE claim_type_id = cv_claim_type_id;
1274
1275 CURSOR csr_sysparam_trx(cv_set_of_books_id IN NUMBER) IS
1276 --SELECT billback_trx_type_id --Modified by Padma as per 11.5.10 enhancements for system parameters.
1277 SELECT CB_TRX_TYPE_ID
1278 FROM ozf_sys_parameters
1279 WHERE set_of_books_id = cv_set_of_books_id;
1280
1281 -- R12 Enhancements
1282 CURSOR csr_reason_code(cv_reason_code_id IN NUMBER) IS
1283 SELECT invoicing_reason_code
1284 FROM ozf_reason_codes_all_b
1285 WHERE reason_code_id = cv_reason_code_id;
1286
1287 -- Cursor to get reference field value
1288 CURSOR csr_cb_ref_field IS
1289 SELECT default_reference
1290 FROM ra_batch_sources
1291 WHERE batch_source_id = 12;
1292
1293 --Start:Bug:2781186
1294 -- Cursor to get customer reason,customer ref, reason code id
1295 CURSOR csr_get_interface_attr_dtls (cv_claim_id IN NUMBER) IS
1296 SELECT customer_reason, customer_ref_number, reason_code_id
1297 --, customer_ref_date --11.5.10 enhancements - TM should pass
1298 -- Uncomment and Corresponding change for this has to be done while assigning to code after
1299 -- AR enhancement for this is done.
1300 FROM ozf_claims_all
1301 WHERE claim_id = cv_claim_id;
1302
1303 -- Cursor to get claim reason name
1304 CURSOR csr_get_reason_name (cv_reason_code_id IN NUMBER) IS
1305 SELECT SUBSTRB(name,1,30) name
1306 FROM ozf_reason_codes_vl
1307 WHERE reason_code_id = cv_reason_code_id;
1308 --End:Bug:2781186
1309
1310
1311 CURSOR csr_gl_date_open( p_set_of_books_id IN NUMBER
1312 , p_gl_date IN DATE
1313 ) IS
1314 SELECT DECODE(MAX(gl.period_name), '', 0, 1)
1315 FROM gl_period_statuses gl
1316 WHERE gl.application_id = 222
1317 AND gl.set_of_books_id = p_set_of_books_id
1318 AND gl.adjustment_period_flag = 'N'
1319 AND p_gl_date BETWEEN gl.start_date AND gl.end_date
1320 AND gl.closing_status IN ('O', 'F');
1321
1322
1323 BEGIN
1324 -------------------- initialize -----------------------
1325 IF g_debug THEN
1326 OZF_Utility_PVT.debug_message(l_full_name||': start');
1327 END IF;
1328
1329 x_return_status := FND_API.g_ret_sts_success;
1330
1331 ------------------------ start -------------------------
1332 OPEN csr_cust_trx_type(p_claim_rec.claim_type_id);
1333 FETCH csr_cust_trx_type INTO l_cb_trx_type_id;
1334 CLOSE csr_cust_trx_type;
1335
1336 IF l_cb_trx_type_id IS NULL THEN
1337 OPEN csr_sysparam_trx(p_claim_rec.set_of_books_id);
1338 FETCH csr_sysparam_trx INTO l_cb_trx_type_id;
1339 CLOSE csr_sysparam_trx;
1340 END IF;
1341
1342 IF l_cb_trx_type_id IS NULL THEN
1343 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1344 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CB_TRX_ID_REQ');
1345 FND_MSG_PUB.add;
1346 END IF;
1347 ELSE
1348 l_chargeback_rec.cust_trx_type_id := l_cb_trx_type_id;
1349 END IF;
1350
1351 OPEN csr_reason_code(p_claim_rec.reason_code_id);
1352 FETCH csr_reason_code INTO l_chargeback_rec.reason_code;
1353 CLOSE csr_reason_code;
1354
1355 l_chargeback_rec.amount := p_chargeback_amount;
1356 l_chargeback_rec.cash_receipt_id := p_claim_rec.receipt_id;
1357 l_chargeback_rec.secondary_application_ref_id := p_claim_rec.root_claim_id;
1358 l_chargeback_rec.new_second_application_ref_id := p_claim_rec.root_claim_id;
1359
1360 --11.5.10 Enhancements. TM passes only if AR period is Open (I.e. not when it's
1361 --Closed or Close Pending, in which cases AR will default)
1362 IF p_gl_date IS NULL OR
1363 p_gl_date = FND_API.g_miss_date THEN
1364 IF OZF_CLAIM_SETTLEMENT_VAL_PVT.gl_date_in_open(222, p_claim_rec.claim_id) THEN
1365 l_chargeback_rec.gl_date := p_claim_rec.gl_date;
1366 END IF;
1367 ELSE
1368 OPEN csr_gl_date_open(p_claim_rec.set_of_books_id, p_gl_date);
1369 FETCH csr_gl_date_open INTO l_gl_date_open_count;
1370 CLOSE csr_gl_date_open;
1371
1372 IF l_gl_date_open_count <> 0 THEN
1373 l_chargeback_rec.gl_date := p_gl_date;
1374 END IF;
1375 END IF;
1376
1377 --11.5.10 Enhancements. AR should default, TM Enh 2655917
1378 --l_chargeback_rec.due_date := p_claim_rec.due_date;
1379
1380 l_chargeback_rec.application_ref_type := 'CLAIM';
1381 -- [ BEGIN BUG246517 fixing 17-JUL-2002 ]: pass in bill to site id to chargeback rec.
1382 l_chargeback_rec.bill_to_site_use_id := p_claim_rec.cust_billto_acct_site_id;
1383 -- [ END BUG246517 fixing ]
1384
1385 -- [BEGIN of BUG2569355 fixing]: pass claim number to chargeback reference field
1386 OPEN csr_cb_ref_field;
1387 FETCH csr_cb_ref_field INTO l_cb_ref_field;
1388 CLOSE csr_cb_ref_field;
1389
1390 IF 1 = TO_NUMBER(l_cb_ref_field) THEN
1391 l_chargeback_rec.interface_header_attribute1 := p_claim_rec.claim_number;
1392 ELSIF 2 = TO_NUMBER(l_cb_ref_field) THEN
1393 l_chargeback_rec.interface_header_attribute2 := p_claim_rec.claim_number;
1394 ELSIF 3 = TO_NUMBER(l_cb_ref_field) THEN
1395 l_chargeback_rec.interface_header_attribute3 := p_claim_rec.claim_number;
1396 ELSIF 4 = TO_NUMBER(l_cb_ref_field) THEN
1397 l_chargeback_rec.interface_header_attribute4 := p_claim_rec.claim_number;
1398 ELSIF 5 = TO_NUMBER(l_cb_ref_field) THEN
1399 l_chargeback_rec.interface_header_attribute5 := p_claim_rec.claim_number;
1400 ELSIF 6 = TO_NUMBER(l_cb_ref_field) THEN
1401 l_chargeback_rec.interface_header_attribute6 := p_claim_rec.claim_number;
1402 ELSIF 7 = TO_NUMBER(l_cb_ref_field) THEN
1403 l_chargeback_rec.interface_header_attribute7 := p_claim_rec.claim_number;
1404 ELSIF 8 = TO_NUMBER(l_cb_ref_field) THEN
1405 l_chargeback_rec.interface_header_attribute8 := p_claim_rec.claim_number;
1406 ELSIF 9 = TO_NUMBER(l_cb_ref_field) THEN
1407 l_chargeback_rec.interface_header_attribute9 := p_claim_rec.claim_number;
1408 ELSIF 10 = TO_NUMBER(l_cb_ref_field) THEN
1409 l_chargeback_rec.interface_header_attribute10 := p_claim_rec.claim_number;
1410 ELSIF 11 = TO_NUMBER(l_cb_ref_field) THEN
1411 l_chargeback_rec.interface_header_attribute11 := p_claim_rec.claim_number;
1412 ELSIF 12 = TO_NUMBER(l_cb_ref_field) THEN
1413 l_chargeback_rec.interface_header_attribute12 := p_claim_rec.claim_number;
1414 ELSIF 13 = TO_NUMBER(l_cb_ref_field) THEN
1415 l_chargeback_rec.interface_header_attribute13 := p_claim_rec.claim_number;
1416 ELSIF 14 = TO_NUMBER(l_cb_ref_field) THEN
1417 l_chargeback_rec.interface_header_attribute14 := p_claim_rec.claim_number;
1418 ELSIF 15 = TO_NUMBER(l_cb_ref_field) THEN
1419 l_chargeback_rec.interface_header_attribute15 := p_claim_rec.claim_number;
1420 END IF;
1421 -- [END of BUG2569355 fixing]
1422
1423 l_chargeback_rec.interface_header_context := 'CLAIM';
1424 l_chargeback_rec.interface_header_attribute1 := p_claim_rec.claim_number;
1425
1426 OPEN csr_get_interface_attr_dtls(p_claim_rec.claim_id);
1427 FETCH csr_get_interface_attr_dtls INTO l_chargeback_rec.interface_header_attribute6, --customer reason
1428 l_chargeback_rec.interface_header_attribute5, --customer reference
1429 l_reason_code_id; --reason code id
1430 CLOSE csr_get_interface_attr_dtls;
1431
1432 OPEN csr_get_reason_name(l_reason_code_id);
1433 FETCH csr_get_reason_name INTO l_chargeback_rec.interface_header_attribute7; --reason name
1434 CLOSE csr_get_reason_name;
1435
1436 --Pass customer reference to separate field customer_reference in chargeback_rec.
1437 l_chargeback_rec.CUSTOMER_REFERENCE := l_chargeback_rec.interface_header_attribute5;
1438
1439 --Pass deduction attributes.
1440 l_chargeback_rec.attribute_category := p_claim_rec.deduction_attribute_category;
1441 l_chargeback_rec.attribute1 := p_claim_rec.deduction_attribute1;
1442 l_chargeback_rec.attribute2 := p_claim_rec.deduction_attribute2;
1443 l_chargeback_rec.attribute3 := p_claim_rec.deduction_attribute3;
1444 l_chargeback_rec.attribute4 := p_claim_rec.deduction_attribute4;
1445 l_chargeback_rec.attribute5 := p_claim_rec.deduction_attribute5;
1446 l_chargeback_rec.attribute6 := p_claim_rec.deduction_attribute6;
1447 l_chargeback_rec.attribute7 := p_claim_rec.deduction_attribute7;
1448 l_chargeback_rec.attribute8 := p_claim_rec.deduction_attribute8;
1449 l_chargeback_rec.attribute9 := p_claim_rec.deduction_attribute9;
1450 l_chargeback_rec.attribute10 := p_claim_rec.deduction_attribute10;
1451 l_chargeback_rec.attribute11 := p_claim_rec.deduction_attribute11;
1452 l_chargeback_rec.attribute12 := p_claim_rec.deduction_attribute12;
1453 l_chargeback_rec.attribute13 := p_claim_rec.deduction_attribute13;
1454 l_chargeback_rec.attribute14 := p_claim_rec.deduction_attribute14;
1455 l_chargeback_rec.attribute15 := p_claim_rec.deduction_attribute15;
1456
1457 --Pass Comments
1458 l_chargeback_rec.comments := SUBSTRB(p_claim_rec.comments,1,240);
1459
1460 -- Pass LE
1461 l_chargeback_rec.legal_entity_id := p_claim_rec.legal_entity_id;
1462
1463 IF g_debug THEN
1464 OZF_Utility_PVT.debug_message(l_full_name||': chargeback amount='||l_chargeback_rec.amount);
1465 END IF;
1466
1467 ARP_CHARGEBACK_COVER.create_chargeback (
1468 p_chargeback_rec => l_chargeback_rec,
1469 p_init_msg_list => FND_API.g_false,
1470 x_doc_sequence_id => l_x_doc_seq_id,
1471 x_doc_sequence_value => l_x_doc_seq_value,
1472 x_trx_number => l_x_trx_number,
1473 x_customer_trx_id => x_cb_customer_trx_id,
1474 x_return_status => l_return_status,
1475 x_msg_count => x_msg_count,
1476 x_msg_data => x_msg_data
1477 );
1478 IF l_return_status = FND_API.g_ret_sts_error THEN
1479 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1480 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CRE_CB_ERR');
1481 FND_MSG_PUB.add;
1482 END IF;
1483 RAISE FND_API.g_exc_error;
1484 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1485 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1486 FND_MESSAGE.set_name('OZF', 'OZF_SETL_AR_CRE_CB_UERR');
1487 FND_MSG_PUB.add;
1488 END IF;
1489 RAISE FND_API.g_exc_unexpected_error;
1490 END IF;
1491
1492 IF g_debug THEN
1493 OZF_Utility_PVT.debug_message(l_full_name||': end');
1494 END IF;
1495 EXCEPTION
1496 WHEN FND_API.g_exc_error THEN
1497 x_return_status := FND_API.g_ret_sts_error;
1498
1499 WHEN FND_API.g_exc_unexpected_error THEN
1500 x_return_status := FND_API.g_ret_sts_unexp_error ;
1501
1502 WHEN OTHERS THEN
1503 x_return_status := FND_API.g_ret_sts_unexp_error ;
1504 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1505 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1506 END IF;
1507
1508 END Create_AR_Chargeback;
1509
1510
1511 /*=======================================================================*
1512 | PROCEDURE
1513 | Create_AR_Write_Off
1514 |
1515 | NOTES
1516 |
1517 | HISTORY
1518 | 15-MAR-2002 mchang Create.
1519 | 17-Oct-2008 ateotia bug # 7484916 fixed.
1520 | FP:11510-R12 7371116 - OZF_AR_PAYMENT_PUT.CREATE_AR_WRITE_OFF ERRORS OUT
1521 *=======================================================================*/
1522 PROCEDURE Create_AR_Write_Off(
1523 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
1524 ,p_deduction_type IN VARCHAR2
1525 ,p_write_off_amount IN NUMBER
1526 ,p_gl_date IN DATE
1527 ,p_wo_rec_trx_id IN NUMBER
1528
1529 ,x_wo_adjust_id OUT NOCOPY NUMBER
1530 ,x_return_status OUT NOCOPY VARCHAR2
1531 ,x_msg_data OUT NOCOPY VARCHAR2
1532 ,x_msg_count OUT NOCOPY NUMBER
1533 )
1534 IS
1535 l_api_version CONSTANT NUMBER := 1.0;
1536 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Write_Off';
1537 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1538 l_return_status VARCHAR2(1);
1539
1540 l_receivables_trx_id NUMBER := NULL;
1541 l_adj_rec AR_ADJUSTMENTS%ROWTYPE;
1542 l_x_new_adjust_number VARCHAR2(20);
1543 l_payment_schedule_id NUMBER;
1544 l_asso_rec_app_id NUMBER;
1545 l_reason_code VARCHAR2(30);
1546 l_root_claim_number VARCHAR2(30);
1547
1548 l_application_ref_num VARCHAR2(30);
1549 l_secondary_appl_ref_id NUMBER;
1550 l_customer_reference VARCHAR2(30);
1551
1552 l_adj_rec_trx_id NUMBER;
1553 l_wo_rec_trx_id NUMBER;
1554 l_neg_wo_rec_trx_id NUMBER;
1555 l_sp_adj_rec_trx_id NUMBER;
1556 l_sp_wo_rec_trx_id NUMBER;
1557 l_sp_neg_wo_rec_trx_id NUMBER;
1558
1559 l_amt_line_items_rem NUMBER;
1560 l_tax_remaining NUMBER;
1561 l_freight_remaining NUMBER;
1562 l_amount_due_remaining NUMBER;
1563 l_rem_amount NUMBER;
1564 l_idx NUMBER := 1;
1565 t_adj_rec AR_ADJUSTMENTS%ROWTYPE;
1566 l_x_wo_adjust_id NUMBER;
1567
1568 TYPE writeoff_dtls_type IS RECORD (
1569 type VARCHAR2(15),
1570 writeoff_amount NUMBER
1571 );
1572
1573 TYPE writeoff_dtls_tab IS TABLE OF writeoff_dtls_type
1574 INDEX BY BINARY_INTEGER;
1575
1576 l_writeoff_dtls writeoff_dtls_tab;
1577
1578 -- associated receivable application is
1579 CURSOR csr_ar_rec_application( cv_cash_receipt_id IN NUMBER
1580 , cv_customer_trx_id IN NUMBER
1581 , cv_claim_id IN NUMBER
1582 ) IS
1583 SELECT receivable_application_id
1584 , applied_payment_schedule_id
1585 FROM ar_receivable_applications
1586 WHERE cash_receipt_id = cv_cash_receipt_id
1587 AND applied_customer_trx_id = cv_customer_trx_id
1588 AND application_ref_type = 'CLAIM'
1589 ANd secondary_application_ref_id = cv_claim_id
1590 AND display = 'Y';
1591
1592 CURSOR csr_reason_code(cv_reason_code_id IN NUMBER) IS
1593 SELECT adjustment_reason_code
1594 FROM ozf_reason_codes_vl
1595 WHERE reason_code_id = cv_reason_code_id;
1596
1597 CURSOR csr_root_claim_number(cv_root_claim_id IN NUMBER) IS
1598 SELECT claim_number
1599 FROM ozf_claims
1600 WHERE claim_id = cv_root_claim_id;
1601
1602 CURSOR csr_claim_type_rec_trx(cv_claim_type_id IN NUMBER) IS
1603 SELECT adj_rec_trx_id
1604 , wo_rec_trx_id
1605 , neg_wo_rec_trx_id
1606 FROM ozf_claim_types_vl
1607 WHERE claim_type_id = cv_claim_type_id;
1608
1609 CURSOR csr_sys_param_rec_trx IS
1610 SELECT adj_rec_trx_id
1611 , wo_rec_trx_id
1612 , neg_wo_rec_trx_id
1613 FROM ozf_sys_parameters;
1614
1615 --Start:Bug:2781186
1616 -- Cursor to get customer reason,customer ref, reason code id
1617 CURSOR csr_get_more_root_clm_dtls (cv_claim_id IN NUMBER) IS
1618 SELECT claim_number, claim_id, customer_ref_number
1619 FROM ozf_claims_all
1620 WHERE claim_id = cv_claim_id;
1621
1622 --Cursor to get customer reason,customer ref, reason code id in case of child cliam
1623 CURSOR csr_get_more_chld_clm_dtls (cv_claim_id IN NUMBER) IS
1624 SELECT p.claim_number, p.claim_id, c.customer_ref_number
1625 FROM ozf_claims c , ozf_claims p
1626 WHERE c.claim_id = cv_claim_id
1627 AND c.root_claim_id = p.claim_id;
1628 --End:Bug:2781186
1629
1630 --Cursor to get amount details
1631 CURSOR csr_get_amount_dtls ( cv_cash_receipt_id IN NUMBER
1632 , cv_customer_trx_id IN NUMBER
1633 , cv_root_claim_id IN NUMBER ) IS
1634 SELECT pay.amount_due_remaining,
1635 pay.amount_line_items_remaining,
1636 pay.tax_remaining,
1637 pay.freight_remaining
1638 FROM ar_receivable_applications rec
1639 , ar_payment_schedules pay
1640 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
1641 AND rec.cash_receipt_id = cv_cash_receipt_id
1642 AND pay.customer_trx_id = cv_customer_trx_id
1643 AND rec.application_ref_type = 'CLAIM'
1644 AND rec.display = 'Y'
1645 AND rec.secondary_application_ref_id = cv_root_claim_id;
1646
1647 BEGIN
1648 -------------------- initialize -----------------------
1649 AMS_Utility_PVT.debug_message(l_full_name||': start');
1650
1651 x_return_status := FND_API.g_ret_sts_success;
1652
1653 --//Bug 5345095
1654 IF p_wo_rec_trx_id IS NULL THEN
1655 l_receivables_trx_id := p_claim_rec.wo_rec_trx_id;
1656 ELSE
1657 l_receivables_trx_id := p_wo_rec_trx_id;
1658 END IF;
1659
1660 IF l_receivables_trx_id IS NULL THEN
1661 OPEN csr_claim_type_rec_trx(p_claim_rec.claim_type_id);
1662 FETCH csr_claim_type_rec_trx INTO l_adj_rec_trx_id
1663 , l_wo_rec_trx_id
1664 , l_neg_wo_rec_trx_id;
1665 CLOSE csr_claim_type_rec_trx;
1666
1667 OPEN csr_sys_param_rec_trx;
1668 FETCH csr_sys_param_rec_trx INTO l_sp_adj_rec_trx_id
1669 , l_sp_wo_rec_trx_id
1670 , l_sp_neg_wo_rec_trx_id;
1671 CLOSE csr_sys_param_rec_trx;
1672
1673 l_adj_rec_trx_id := NVL(l_adj_rec_trx_id , l_sp_adj_rec_trx_id);
1674 l_wo_rec_trx_id := NVL(l_wo_rec_trx_id , l_sp_wo_rec_trx_id);
1675 l_neg_wo_rec_trx_id := NVL(l_neg_wo_rec_trx_id, l_sp_neg_wo_rec_trx_id);
1676 END IF;
1677
1678 ------------------------ start -------------------------
1679 IF p_deduction_type = 'SOURCE_DED' THEN
1680 /*------------------------------------------------------------*
1681 | Invoice Deduction -> 1. Create a negative adjustment.
1682 | 2. Take invoice out of dispute
1683 *------------------------------------------------------------*/
1684 -- 1. Create a negative adjustment
1685 OPEN csr_ar_rec_application( p_claim_rec.receipt_id
1686 , p_claim_rec.source_object_id
1687 , p_claim_rec.root_claim_id
1688 );
1689 FETCH csr_ar_rec_application INTO l_asso_rec_app_id
1690 , l_payment_schedule_id;
1691 CLOSE csr_ar_rec_application;
1692
1693 OPEN csr_reason_code(p_claim_rec.reason_code_id);
1694 FETCH csr_reason_code INTO l_reason_code;
1695 CLOSE csr_reason_code;
1696
1697 OPEN csr_get_amount_dtls( p_claim_rec.receipt_id
1698 , p_claim_rec.source_object_id
1699 , p_claim_rec.root_claim_id
1700 );
1701 FETCH csr_get_amount_dtls INTO l_amount_due_remaining, l_amt_line_items_rem,
1702 l_tax_remaining, l_freight_remaining;
1703 CLOSE csr_get_amount_dtls;
1704
1705 IF l_receivables_trx_id IS NULL THEN
1706 l_receivables_trx_id := l_adj_rec_trx_id;
1707 END IF;
1708
1709 l_adj_rec.payment_schedule_id := l_payment_schedule_id;
1710 l_adj_rec.amount := p_write_off_amount * -1;
1711 -- l_adj_customer_trx_line_id := -- for type other then 'INVOICE' only.
1712 l_adj_rec.receivables_trx_id := l_receivables_trx_id;
1713 l_adj_rec.apply_date := SYSDATE;
1714 IF p_gl_date IS NULL OR
1715 p_gl_date = FND_API.g_miss_date THEN
1716 l_adj_rec.gl_date := p_claim_rec.gl_date;
1717 ELSE
1718 l_adj_rec.gl_date := p_gl_date;
1719 END IF;
1720 l_adj_rec.reason_code := l_reason_code;
1721 l_adj_rec.comments := SUBSTRB(p_claim_rec.comments, 1, 240);
1722 l_adj_rec.associated_application_id := l_asso_rec_app_id;
1723 l_adj_rec.associated_cash_receipt_id := p_claim_rec.receipt_id;
1724 l_adj_rec.created_from := 'CLAIMS';
1725
1726 IF l_amount_due_remaining = p_write_off_amount THEN
1727 l_writeoff_dtls(1).type := 'INVOICE';
1728 l_writeoff_dtls(1).writeoff_amount := p_write_off_amount;
1729 ELSE
1730 l_rem_amount := p_write_off_amount;
1731 IF l_amt_line_items_rem > 0 THEN
1732 IF l_amt_line_items_rem >= l_rem_amount THEN
1733 l_writeoff_dtls(l_idx).type := 'LINE';
1734 l_writeoff_dtls(l_idx).writeoff_amount := l_rem_amount;
1735 l_rem_amount := 0;
1736 ELSE
1737 l_writeoff_dtls(l_idx).type := 'LINE';
1738 l_writeoff_dtls(l_idx).writeoff_amount := l_amt_line_items_rem;
1739 l_rem_amount := l_rem_amount - l_amt_line_items_rem;
1740 END IF;
1741 l_idx := l_idx + 1;
1742 END IF;
1743
1744 IF l_rem_amount > 0 AND l_tax_remaining > 0 THEN
1745 IF l_tax_remaining >= l_rem_amount THEN
1746 l_writeoff_dtls(l_idx).type := 'TAX';
1747 l_writeoff_dtls(l_idx).writeoff_amount := l_rem_amount;
1748 l_rem_amount := 0;
1749 ELSE
1750 l_writeoff_dtls(l_idx).type := 'TAX';
1751 l_writeoff_dtls(l_idx).writeoff_amount := l_tax_remaining;
1752 l_rem_amount := p_write_off_amount - l_tax_remaining;
1753 END IF;
1754 l_idx := l_idx + 1;
1755 END IF;
1756
1757 IF l_rem_amount > 0 AND l_freight_remaining > 0 THEN
1758 IF l_freight_remaining >= l_rem_amount THEN
1759 l_writeoff_dtls(l_idx).type := 'FREIGHT';
1760 l_writeoff_dtls(l_idx).writeoff_amount := l_rem_amount;
1761 l_rem_amount := 0;
1762 ELSE
1763 l_writeoff_dtls(l_idx).type := 'FREIGHT';
1764 l_writeoff_dtls(l_idx).writeoff_amount := l_freight_remaining;
1765 l_rem_amount := l_rem_amount - l_freight_remaining;
1766 END IF;
1767 END IF;
1768 END IF;
1769
1770 -- bug # 7484916 fixed by ateotia (+)
1771 --FOR l_idx IN l_writeoff_dtls.FIRST..l_writeoff_dtls.LAST LOOP
1772 IF (l_writeoff_dtls.COUNT > 0) THEN
1773 FOR rowCount IN l_writeoff_dtls.FIRST..l_writeoff_dtls.LAST LOOP
1774 t_adj_rec := l_adj_rec;
1775 --t_adj_rec.type := l_writeoff_dtls(l_idx).type;
1776 --t_adj_rec.amount := l_writeoff_dtls(l_idx).writeoff_amount * -1;
1777 t_adj_rec.type := l_writeoff_dtls(rowCount).type;
1778 t_adj_rec.amount := l_writeoff_dtls(rowCount).writeoff_amount * -1;
1779 -- bug # 7484916 fixed by ateotia (-)
1780
1781 AR_ADJUST_PUB.Create_Adjustment (
1782 p_api_name => 'AR_ADJUST_PUB.Create_Adjustment',
1783 p_api_version => l_api_version,
1784 p_init_msg_list => FND_API.g_false,
1785 p_commit_flag => FND_API.g_false,
1786 p_validation_level => FND_API.g_valid_level_full,
1787 p_msg_count => x_msg_count,
1788 p_msg_data => x_msg_data,
1789 p_return_status => l_return_status,
1790 p_adj_rec => t_adj_rec,
1791 p_chk_approval_limits => FND_API.g_false,
1792 p_check_amount => FND_API.g_true,
1793 p_move_deferred_tax => 'Y', --??
1794 p_new_adjust_number => l_x_new_adjust_number,
1795 p_new_adjust_id => l_x_wo_adjust_id,
1796 p_called_from => 'CLAIMS',
1797 p_old_adjust_id => NULL
1798 );
1799 IF l_return_status = FND_API.g_ret_sts_error THEN
1800 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1801 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_ADJ_ERR');
1802 FND_MSG_PUB.add;
1803 END IF;
1804 RAISE FND_API.g_exc_error;
1805 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1806 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1807 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_ADJ_UERR');
1808 FND_MSG_PUB.add;
1809 END IF;
1810 RAISE FND_API.g_exc_unexpected_error;
1811 END IF;
1812
1813 /*------------------------------------------------------------*
1814 | Update Deduction payment detail
1815 *------------------------------------------------------------*/
1816 IF l_x_wo_adjust_id IS NOT NULL THEN
1817 -- Update Deduction payment detail
1818 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1819 p_api_version => l_api_version
1820 ,p_init_msg_list => FND_API.g_false
1821 ,p_commit => FND_API.g_false
1822 ,p_validation_level => FND_API.g_valid_level_full
1823 ,x_return_status => l_return_status
1824 ,x_msg_data => x_msg_data
1825 ,x_msg_count => x_msg_count
1826 ,p_claim_id => p_claim_rec.claim_id
1827 ,p_payment_method => 'WRITE_OFF'
1828 ,p_deduction_type => p_deduction_type
1829 ,p_cash_receipt_id => p_claim_rec.receipt_id
1830 ,p_customer_trx_id => p_claim_rec.source_object_id
1831 ,p_adjust_id => l_x_wo_adjust_id
1832 );
1833
1834 IF l_return_status = FND_API.g_ret_sts_error THEN
1835 RAISE FND_API.g_exc_error;
1836 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1837 RAISE FND_API.g_exc_unexpected_error;
1838 END IF;
1839 END IF;
1840 END LOOP;
1841 END IF; -- bug # 7484916 fixed by ateotia
1842
1843 -- 2. Taking invoice out of dispute
1844 IF p_claim_rec.claim_id <> p_claim_rec.root_claim_id THEN
1845 OPEN csr_root_claim_number(p_claim_rec.root_claim_id);
1846 FETCH csr_root_claim_number INTO l_root_claim_number;
1847 CLOSE csr_root_claim_number;
1848 ELSE
1849 l_root_claim_number := p_claim_rec.claim_number;
1850 END IF;
1851
1852 ARP_DEDUCTION_COVER.update_amount_in_dispute(
1853 p_customer_trx_id => p_claim_rec.source_object_id,
1854 p_claim_number => l_root_claim_number,
1855 p_amount => p_write_off_amount * -1,
1856 p_init_msg_list => FND_API.g_false,
1857 x_return_status => l_return_status,
1858 x_msg_count => x_msg_count,
1859 x_msg_data => x_msg_data
1860 );
1861 IF l_return_status = FND_API.g_ret_sts_error THEN
1862 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1863 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_UPD_DISPUTE_ERR');
1864 FND_MSG_PUB.add;
1865 END IF;
1866 RAISE FND_API.g_exc_error;
1867 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1868 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1869 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_UPD_DISPUTE_UERR');
1870 FND_MSG_PUB.add;
1871 END IF;
1872 RAISE FND_API.g_exc_unexpected_error;
1873 END IF;
1874
1875 ELSIF p_deduction_type in ('RECEIPT_OPM','RECEIPT_DED') THEN
1876 /*------------------------------------------------------------*
1877 | Claim Investigation -> 1. Cover API :: Create Receipt Write_Off
1878 *------------------------------------------------------------*/
1879 IF p_claim_rec.claim_id = p_claim_rec.root_claim_id THEN
1880 OPEN csr_get_more_root_clm_dtls(p_claim_rec.claim_id);
1881 FETCH csr_get_more_root_clm_dtls INTO l_application_ref_num
1882 , l_secondary_appl_ref_id
1883 , l_customer_reference;
1884 CLOSE csr_get_more_root_clm_dtls;
1885 ELSE
1886 OPEN csr_get_more_chld_clm_dtls(p_claim_rec.claim_id);
1887 FETCH csr_get_more_chld_clm_dtls INTO l_application_ref_num
1888 , l_secondary_appl_ref_id
1889 , l_customer_reference;
1890 CLOSE csr_get_more_chld_clm_dtls;
1891 END IF;
1892
1893 IF p_wo_rec_trx_id IS NULL OR
1894 p_wo_rec_trx_id = FND_API.g_miss_num THEN
1895
1896 IF l_receivables_trx_id IS NULL THEN
1897 IF p_deduction_type = 'RECEIPT_OPM' THEN
1898 l_receivables_trx_id := l_wo_rec_trx_id;
1899 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
1900 l_receivables_trx_id := l_neg_wo_rec_trx_id;
1901 END IF;
1902 END IF;
1903 ELSE
1904 l_receivables_trx_id := p_wo_rec_trx_id;
1905 END IF;
1906
1907 ARP_DEDUCTION_COVER.create_receipt_writeoff(
1908 p_claim_id => p_claim_rec.root_claim_id,
1909 p_amount => (p_write_off_amount * -1),
1910 p_new_claim_id => p_claim_rec.root_claim_id,
1911 p_init_msg_list => FND_API.g_false,
1912 p_cash_receipt_id => p_claim_rec.receipt_id,
1913 p_receivables_trx_id => l_receivables_trx_id,
1914 p_ussgl_transaction_code => NULL,
1915 p_application_ref_num => l_application_ref_num,
1916 p_secondary_application_ref_id => l_secondary_appl_ref_id,
1917 p_customer_reference => l_customer_reference,
1918 x_return_status => l_return_status,
1919 x_msg_count => x_msg_count,
1920 x_msg_data => x_msg_data
1921 );
1922 IF l_return_status = FND_API.g_ret_sts_error THEN
1923 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1924 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_REC_WO_ERR');
1925 FND_MSG_PUB.add;
1926 END IF;
1927 RAISE FND_API.g_exc_error;
1928 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1929 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1930 FND_MESSAGE.set_name('AMS', 'AMS_SETL_AR_CRE_REC_WO_UERR');
1931 FND_MSG_PUB.add;
1932 END IF;
1933 RAISE FND_API.g_exc_unexpected_error;
1934 END IF;
1935
1936 -- There is no write_off number populating in case of receipt write_off.
1937 l_x_wo_adjust_id := -3;
1938 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
1939 p_api_version => l_api_version
1940 ,p_init_msg_list => FND_API.g_false
1941 ,p_commit => FND_API.g_false
1942 ,p_validation_level => FND_API.g_valid_level_full
1943 ,x_return_status => l_return_status
1944 ,x_msg_data => x_msg_data
1945 ,x_msg_count => x_msg_count
1946 ,p_claim_id => p_claim_rec.claim_id
1947 ,p_payment_method => 'WRITE_OFF'
1948 ,p_deduction_type => p_deduction_type
1949 ,p_cash_receipt_id => p_claim_rec.receipt_id
1950 ,p_customer_trx_id => p_claim_rec.source_object_id
1951 ,p_adjust_id => l_x_wo_adjust_id
1952 );
1953
1954 IF l_return_status = FND_API.g_ret_sts_error THEN
1955 RAISE FND_API.g_exc_error;
1956 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1957 RAISE FND_API.g_exc_unexpected_error;
1958 END IF;
1959 END IF;
1960
1961 --//Bug 5345095 BK Modif
1962 IF l_receivables_trx_id IS NOT NULL THEN
1963 BEGIN
1964 UPDATE ozf_claims_all
1965 SET wo_rec_trx_id = l_receivables_trx_id
1966 WHERE claim_id = p_claim_rec.claim_id
1967 AND wo_rec_trx_id IS NULL;
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1972 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_GRP_UPD_DEDU_ERR');
1973 FND_MSG_PUB.add;
1974 END IF;
1975
1976 IF g_debug THEN
1977 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1978 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1979 FND_MSG_PUB.Add;
1980 END IF;
1981 RAISE FND_API.g_exc_unexpected_error;
1982 END;
1983 END IF;
1984
1985 AMS_Utility_PVT.debug_message(l_full_name||': end');
1986
1987 EXCEPTION
1988 WHEN FND_API.g_exc_error THEN
1989 x_return_status := FND_API.g_ret_sts_error;
1990
1991 WHEN FND_API.g_exc_unexpected_error THEN
1992 x_return_status := FND_API.g_ret_sts_unexp_error ;
1993
1994 WHEN OTHERS THEN
1995 x_return_status := FND_API.g_ret_sts_unexp_error ;
1996 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1997 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1998 END IF;
1999
2000 END Create_AR_Write_Off;
2001
2002
2003
2004 /*=======================================================================*
2005 | PROCEDURE
2006 | Process_Settlement_WF
2007 |
2008 | NOTES
2009 |
2010 | HISTORY
2011 | 15-MAR-2002 mchang Create.
2012 *=======================================================================*/
2013 PROCEDURE Process_Settlement_WF(
2014 p_claim_id IN NUMBER
2015
2016 ,x_return_status OUT NOCOPY VARCHAR2
2017 ,x_msg_data OUT NOCOPY VARCHAR2
2018 ,x_msg_count OUT NOCOPY NUMBER
2019 )
2020 IS
2021 l_api_version CONSTANT NUMBER := 1.0;
2022 l_api_name CONSTANT VARCHAR2(30) := 'Process_Settlement_WF';
2023 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2024 l_return_status VARCHAR2(1);
2025
2026 BEGIN
2027 -------------------- initialize -----------------------
2028 IF g_debug THEN
2029 OZF_Utility_PVT.debug_message(l_full_name||': start');
2030 END IF;
2031
2032 x_return_status := FND_API.g_ret_sts_success;
2033
2034 ------------------------ start -------------------------
2035 BEGIN
2036 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
2037 p_claim_id => p_claim_id
2038 ,p_prev_status => 'APPROVED'
2039 ,p_curr_status => 'PENDING_CLOSE'
2040 ,p_next_status => 'CLOSED'
2041 ,p_promotional_claim => 'N'
2042 ,p_process => 'OZF_CLAIM_SETTLEMENT'
2043 );
2044 EXCEPTION
2045 WHEN OTHERS THEN
2046 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2047 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2048 FND_MSG_PUB.Add;
2049 RAISE FND_API.g_exc_unexpected_error;
2050 END;
2051
2052 IF g_debug THEN
2053 OZF_Utility_PVT.debug_message(l_full_name||': end');
2054 END IF;
2055 EXCEPTION
2056 WHEN FND_API.g_exc_unexpected_error THEN
2057 x_return_status := FND_API.g_ret_sts_unexp_error ;
2058
2059 WHEN OTHERS THEN
2060 x_return_status := FND_API.g_ret_sts_unexp_error ;
2061 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2062 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2063 END IF;
2064
2065 END Process_Settlement_WF;
2066
2067 /*=======================================================================*
2068 | Function
2069 | Get_Inv_Credit_Details
2070 |
2071 | Return
2072 | FND_API.g_true / FND_API.g_false
2073 |
2074 | HISTORY
2075 | 14-Jun-2005 Sahana Created for R12
2076 *=======================================================================*/
2077 PROCEDURE Get_Inv_Credit_Details(
2078 p_claim_id IN NUMBER
2079 ,p_invoice_id IN NUMBER
2080 ,x_return_status OUT NOCOPY VARCHAR2
2081 ,x_line_credit OUT NOCOPY NUMBER
2082 ,x_tax_credit OUT NOCOPY NUMBER
2083 ,x_freight_credit OUT NOCOPY NUMBER
2084 ,x_total_credit OUT NOCOPY NUMBER
2085 ,x_cm_line_tbl OUT NOCOPY AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE
2086 ) IS
2087
2088 CURSOR csr_claim_line_invoice(cv_claim_id IN NUMBER, cv_inv_id IN NUMBER) IS
2089 SELECT source_object_id
2090 , source_object_line_id
2091 , credit_to
2092 , SUM(quantity) qty
2093 , AVG(rate) rate
2094 , SUM(NVL(claim_currency_amount,0)) amount
2095 FROM ozf_claim_lines
2096 WHERE claim_id = cv_claim_id
2097 AND source_object_id = cv_inv_id
2098 GROUP BY source_object_id,source_object_line_id,credit_to;
2099 l_trx_lines csr_claim_line_invoice%ROWTYPE;
2100
2101 l_api_name CONSTANT VARCHAR2(30) := 'Get_Inv_Credit_Details()';
2102 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2103
2104 l_line_credit NUMBER := 0;
2105 l_tax_credit NUMBER := 0;
2106 l_freight_credit NUMBER := 0;
2107 l_total_credit NUMBER := 0;
2108
2109 l_counter NUMBER := 1;
2110
2111 BEGIN
2112 x_return_status := FND_API.g_ret_sts_success;
2113
2114
2115 IF g_debug THEN
2116 OZF_Utility_PVT.debug_message( l_full_name || ' : Start');
2117 END IF;
2118
2119 OPEN csr_claim_line_invoice(p_claim_id, p_invoice_id);
2120 LOOP
2121 FETCH csr_claim_line_invoice INTO l_trx_lines;
2122 EXIT WHEN csr_claim_line_invoice%NOTFOUND;
2123
2124 IF l_trx_lines.source_object_line_id IS NOT NULL THEN
2125 x_cm_line_tbl(l_counter).customer_trx_line_id := l_trx_lines.source_object_line_id;
2126 x_cm_line_tbl(l_counter).quantity_credited := l_trx_lines.qty * -1;
2127 x_cm_line_tbl(l_counter).price := l_trx_lines.rate;
2128 x_cm_line_tbl(l_counter).extended_amount := l_trx_lines.amount * -1;
2129
2130 IF g_debug THEN
2131 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);
2132 OZF_Utility_PVT.debug_message('x_cm_line_tbl('||l_counter||').extended_amount='||x_cm_line_tbl(l_counter).extended_amount);
2133 END IF;
2134 l_counter := l_counter +1;
2135 END IF;
2136
2137 IF l_trx_lines.credit_to IS NOT NULL THEN
2138 IF l_trx_lines.credit_to = 'LINE' THEN
2139 l_line_credit := l_line_credit + l_trx_lines.amount ;
2140 ELSIF l_trx_lines.credit_to = 'TAX' THEN
2141 l_tax_credit := l_tax_credit + l_trx_lines.amount ;
2142 ELSIF l_trx_lines.credit_to = 'FREIGHT' THEN
2143 l_freight_credit := l_freight_credit + l_trx_lines.amount ;
2144 END IF;
2145 END IF;
2146
2147 IF l_trx_lines.credit_to IS NULL AND l_trx_lines.source_object_line_id IS NULL THEN
2148 l_total_credit := l_total_credit + l_trx_lines.amount ;
2149 END IF;
2150
2151 END LOOP;
2152 IF g_debug THEN
2153 OZF_Utility_PVT.debug_message('l_line_credit = '||l_line_credit);
2154 OZF_Utility_PVT.debug_message('l_tax_credit = '||l_tax_credit);
2155 OZF_Utility_PVT.debug_message('l_freight_credit = '||l_freight_credit);
2156 OZF_Utility_PVT.debug_message('l_total_credit = '||l_total_credit);
2157 END IF;
2158 CLOSE csr_claim_line_invoice;
2159
2160
2161 x_line_credit := l_line_credit;
2162 x_tax_credit := l_tax_credit;
2163 x_freight_credit := l_freight_credit;
2164 x_total_credit := l_total_credit;
2165
2166 IF g_debug THEN
2167 OZF_Utility_PVT.debug_message( l_full_name || ' : End');
2168 END IF;
2169
2170
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2174 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2175 END IF;
2176 x_return_status := FND_API.g_ret_sts_unexp_error;
2177 END Get_Inv_Credit_Details;
2178
2179 /*=======================================================================*
2180 | PROCEDURE
2181 | Pay_by_Single_Invoice_Credit
2182 |
2183 | NOTES
2184 |
2185 | HISTORY
2186 | 15-JUN-2005 Sahana Created for R12.
2187 *=======================================================================*/
2188 PROCEDURE Pay_by_Single_Invoice_Credit(
2189 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
2190 ,p_sttlmnt_amt IN NUMBER
2191 ,p_invoice_id IN NUMBER
2192 ,p_deduction_type IN VARCHAR2
2193 ,p_line_credit IN NUMBER
2194 ,p_tax_credit IN NUMBER
2195 ,p_freight_credit IN NUMBER
2196 ,p_total_credit IN NUMBER
2197 ,p_cm_line_tbl IN AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE
2198
2199 ,x_return_status OUT NOCOPY VARCHAR2
2200 ,x_msg_data OUT NOCOPY VARCHAR2
2201 ,x_msg_count OUT NOCOPY NUMBER
2202 )
2203 IS
2204 l_api_version CONSTANT NUMBER := 1.0;
2205 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Single_Invoice_Credit';
2206 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2207 l_return_status VARCHAR2(1);
2208
2209 l_cm_customer_trx_id NUMBER := NULL;
2210 l_cm_amount NUMBER := 0;
2211 l_new_applied_amount NUMBER;
2212 l_old_applied_amount NUMBER;
2213 l_claim_line_count NUMBER;
2214 l_apply_receipt_id NUMBER;
2215 l_old_applied_claim_amount NUMBER;
2216 l_reapply_claim_amount NUMBER;
2217 l_line_remaining NUMBER;
2218 l_tax_remaining NUMBER;
2219 l_freight_remaining NUMBER;
2220
2221
2222
2223 CURSOR csr_old_applied_amount( cv_cash_receipt_id IN NUMBER
2224 , cv_customer_trx_id IN NUMBER
2225 ) IS
2226 SELECT rec.amount_applied
2227 , pay.amount_due_remaining
2228 , NVL(pay.amount_line_items_remaining, 0) amount_line_items_remaining
2229 , NVL(pay.tax_remaining, 0) tax_remaining
2230 , NVL(pay.freight_remaining, 0) freight_remaining
2231 , rec.comments
2232 , rec.payment_set_id
2233 , rec.application_ref_type
2234 , rec.application_ref_id
2235 , rec.application_ref_num
2236 , rec.secondary_application_ref_id
2237 , rec.application_ref_reason
2238 , rec.customer_reference
2239 FROM ar_receivable_applications rec
2240 , ar_payment_schedules pay
2241 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
2242 AND rec.cash_receipt_id = cv_cash_receipt_id
2243 AND pay.customer_trx_id = cv_customer_trx_id
2244 AND rec.display = 'Y';
2245
2246 l_old_applied_invoice csr_old_applied_amount%ROWTYPE;
2247
2248 CURSOR csr_count_claim_line(cv_claim_id IN NUMBER) IS
2249 SELECT COUNT(claim_line_id)
2250 FROM ozf_claim_lines
2251 WHERE claim_id = cv_claim_id;
2252
2253 CURSOR csr_invoice_apply_receipt(cv_invoice_id IN NUMBER) IS
2254 SELECT rec.cash_receipt_id
2255 FROM ar_receivable_applications_all rec
2256 , ar_payment_schedules pay
2257 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
2258 AND pay.customer_trx_id = cv_invoice_id;
2259
2260 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
2261 , cv_root_claim_id IN NUMBER) IS
2262 SELECT rec.amount_applied
2263 FROM ar_receivable_applications rec
2264 WHERE rec.applied_payment_schedule_id = -4
2265 AND rec.cash_receipt_id = cv_cash_receipt_id
2266 AND rec.application_ref_type = 'CLAIM'
2267 AND rec.display = 'Y'
2268 AND rec.secondary_application_ref_id = cv_root_claim_id;
2269
2270 CURSOR csr_customer_trx_lines(cv_invoice_line_id IN NUMBER) IS
2271 SELECT customer_trx_id
2272 FROM ra_customer_trx_lines
2273 WHERE customer_trx_line_id = cv_invoice_line_id;
2274
2275 BEGIN
2276 -------------------- initialize -----------------------
2277 IF g_debug THEN
2278 OZF_Utility_PVT.debug_message(l_full_name||': start');
2279 END IF;
2280
2281 x_return_status := FND_API.g_ret_sts_success;
2282
2283 IF p_deduction_type = 'SOURCE_DED' THEN
2284 /*------------------------------------------------------------*
2285 | Remove invoice from dispute.
2286 | Invoice Deduction - Credit to Tax/Line/Freight
2287 | -> 1. Unapply invoice from receipt.
2288 | -> 2. Create credit memo for the invoice.
2289 | -> 3. Apply invoice back on receipt.
2290 | -> 4. Update dispute amount.
2291 | Invoice Deduction - Credit to Invoice
2292 | -> create credit memo for the invoice
2293 *------------------------------------------------------------*/
2294
2295 -- Update dispute amount.
2296 IF g_debug THEN
2297 OZF_Utility_PVT.debug_message('Source Deduction -> Update dispute amount');
2298 END IF;
2299 Update_dispute_amount(
2300 p_claim_rec => p_claim_rec
2301 ,p_dispute_amount => l_cm_amount
2302 ,x_return_status => l_return_status
2303 ,x_msg_data => x_msg_data
2304 ,x_msg_count => x_msg_count
2305 );
2306 IF l_return_status = FND_API.g_ret_sts_error THEN
2307 RAISE FND_API.g_exc_error;
2308 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2309 RAISE FND_API.g_exc_unexpected_error;
2310 END IF;
2311
2312
2313 -- -------------------------------------
2314 -- Invoice Deduction - Credit to Invoice
2315 -- -------------------------------------
2316 IF p_line_credit = 0 AND
2317 p_tax_credit = 0 AND
2318 p_freight_credit = 0 AND
2319 p_total_credit <> 0 THEN
2320 IF g_debug THEN
2321 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to --] -> Create Credit Memo');
2322 END IF;
2323 Create_AR_Credit_Memo(
2324 p_claim_rec => p_claim_rec
2325 ,p_customer_trx_id => p_claim_rec.source_object_id
2326 ,p_deduction_type => p_deduction_type
2327 ,p_line_remaining => 0
2328 ,p_tax_remaining => 0
2329 ,p_freight_remaining => 0
2330 ,p_line_credit => p_line_credit
2331 ,p_tax_credit => p_tax_credit
2332 ,p_freight_credit => p_freight_credit
2333 ,p_total_credit => p_total_credit
2334 ,p_cm_line_tbl => p_cm_line_tbl
2335 ,p_upd_dispute_flag => FND_API.g_true
2336 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2337 ,x_cm_amount => l_cm_amount
2338 ,x_return_status => l_return_status
2339 ,x_msg_data => x_msg_data
2340 ,x_msg_count => x_msg_count
2341 );
2342 IF l_return_status = FND_API.g_ret_sts_error THEN
2343 RAISE FND_API.g_exc_error;
2344 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2345 RAISE FND_API.g_exc_unexpected_error;
2346 END IF;
2347
2348 -- ----------------------------------------------
2349 -- Invoice Deduction - Credit to Tax/Line/Freight
2350 -- ----------------------------------------------
2351 ELSE
2352 OPEN csr_old_applied_amount( p_claim_rec.receipt_id
2353 , p_claim_rec.source_object_id
2354 );
2355 FETCH csr_old_applied_amount INTO l_old_applied_invoice;
2356 CLOSE csr_old_applied_amount;
2357
2358
2359 -- 1. Unapply invoice from receipt.
2360 IF g_debug THEN
2361 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to Line/Tax/Freight] -> 1. Unapply invoice from receipt.');
2362 END IF;
2363 -- Bug4118351: Do not unapply if original amount applied is zero.
2364 IF l_old_applied_invoice.amount_applied <> 0 THEN
2365 Unapply_from_Receipt(
2366 p_cash_receipt_id => p_claim_rec.receipt_id
2367 ,p_customer_trx_id => p_claim_rec.source_object_id
2368 ,x_return_status => l_return_status
2369 ,x_msg_data => x_msg_data
2370 ,x_msg_count => x_msg_count
2371 );
2372 IF l_return_status = FND_API.g_ret_sts_error THEN
2373 RAISE FND_API.g_exc_error;
2374 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2375 RAISE FND_API.g_exc_unexpected_error;
2376 END IF;
2377 END IF;
2378
2379 -- 2. Create credit memo for the invoice
2380 IF g_debug THEN
2381 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to Line/Tax/Freight] -> 2. Create credit memo for the invoice');
2382 END IF;
2383 Create_AR_Credit_Memo(
2384 p_claim_rec => p_claim_rec
2385 ,p_customer_trx_id => p_claim_rec.source_object_id
2386 ,p_deduction_type => p_deduction_type
2387 ,p_line_remaining => 0
2388 ,p_tax_remaining => 0
2389 ,p_freight_remaining => 0
2390 ,p_line_credit => p_line_credit
2391 ,p_tax_credit => p_tax_credit
2392 ,p_freight_credit => p_freight_credit
2393 ,p_total_credit => p_total_credit
2394 ,p_cm_line_tbl => p_cm_line_tbl
2395 ,p_upd_dispute_flag => FND_API.g_false
2396 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2397 ,x_cm_amount => l_cm_amount
2398 ,x_return_status => l_return_status
2399 ,x_msg_data => x_msg_data
2400 ,x_msg_count => x_msg_count
2401 );
2402 IF l_return_status = FND_API.g_ret_sts_error THEN
2403 RAISE FND_API.g_exc_error;
2404 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2405 RAISE FND_API.g_exc_unexpected_error;
2406 END IF;
2407
2408 -- 3. Apply invoice back on receipt.
2409 IF g_debug THEN
2410 OZF_Utility_PVT.debug_message('Invoice Deduction[Credit to Line/Tax/Freight] -> 3. Apply invoice back on receipt');
2411 END IF;
2412 --IF (l_old_applied_invoice.amount_due_remaining + l_cm_amount) = 0 THEN
2413 IF l_old_applied_invoice.amount_due_remaining = p_claim_rec.amount_settled THEN
2414 l_old_applied_invoice.application_ref_type := NULL;
2415 l_old_applied_invoice.application_ref_id := NULL;
2416 l_old_applied_invoice.application_ref_num := NULL;
2417 l_old_applied_invoice.secondary_application_ref_id := NULL;
2418 l_old_applied_invoice.application_ref_reason := NULL;
2419 END IF;
2420
2421 -- Bug4118351: Reapply invoice only if original applied amount was not 0.
2422 IF l_old_applied_invoice.amount_applied <> 0 THEN
2423 Apply_on_Receipt(
2424 p_cash_receipt_id => p_claim_rec.receipt_id
2425 ,p_customer_trx_id => p_claim_rec.source_object_id
2426 ,p_new_applied_amount => l_old_applied_invoice.amount_applied
2427 ,p_comments => l_old_applied_invoice.comments
2428 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
2429 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
2430 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
2431 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
2432 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
2433 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
2434 ,p_customer_reference => l_old_applied_invoice.customer_reference
2435 ,x_return_status => l_return_status
2436 ,x_msg_data => x_msg_data
2437 ,x_msg_count => x_msg_count
2438 );
2439 IF l_return_status = FND_API.g_ret_sts_error THEN
2440 RAISE FND_API.g_exc_error;
2441 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2442 RAISE FND_API.g_exc_unexpected_error;
2443 END IF;
2444 END IF;
2445
2446 END IF;
2447
2448 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
2449 /*------------------------------------------------------------*
2450 | Receipt Deduction
2451 | 1. Unapply associated invoice from receipt.
2452 | 2. Unapply_Claim_Investigation.
2453 | 3. Validate Invoice
2454 | -> Credit to Invoice
2455 | 4. Apply same associated invoice even if balance amount is zero
2456 | 5. Create credit memo for the invoice.
2457 |
2458 | -> Credit to Tax/Line/Freight or specific line
2459 | 4. Reapply at this point if balance is zero
2460 | 5. Create credit memo for the invoice.
2461 | 6. Reapply same associated invoice with reduced amount if balance not zero
2462 | Modified for 4308173
2463 *------------------------------------------------------------*/
2464
2465 IF g_debug THEN
2466 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Unapply associated invoice from receipt');
2467 END IF;
2468
2469
2470 OPEN csr_old_applied_amount( p_claim_rec.receipt_id, p_invoice_id);
2471 FETCH csr_old_applied_amount INTO l_old_applied_invoice;
2472 CLOSE csr_old_applied_amount;
2473
2474 Unapply_from_Receipt(
2475 p_cash_receipt_id => p_claim_rec.receipt_id
2476 ,p_customer_trx_id => p_invoice_id
2477 ,x_return_status => l_return_status
2478 ,x_msg_data => x_msg_data
2479 ,x_msg_count => x_msg_count
2480 );
2481 IF l_return_status = FND_API.g_ret_sts_error THEN
2482 RAISE FND_API.g_exc_error;
2483 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2484 RAISE FND_API.g_exc_unexpected_error;
2485 END IF;
2486
2487
2488 IF g_debug THEN
2489 OZF_Utility_PVT.debug_message('Receipt Deduction -> 2. Unapply_Claim_Investigation.');
2490 END IF;
2491 OPEN csr_old_claim_investigation(p_claim_rec.receipt_id, p_claim_rec.root_claim_id);
2492 FETCH csr_old_claim_investigation INTO l_old_applied_claim_amount;
2493 CLOSE csr_old_claim_investigation;
2494
2495 l_reapply_claim_amount := l_old_applied_claim_amount + p_sttlmnt_amt;
2496
2497 Unapply_Claim_Investigation(
2498 p_claim_rec => p_claim_rec
2499 ,p_reapply_amount => l_reapply_claim_amount --0
2500 ,x_return_status => l_return_status
2501 ,x_msg_data => x_msg_data
2502 ,x_msg_count => x_msg_count
2503 );
2504 IF l_return_status = FND_API.g_ret_sts_error THEN
2505 RAISE FND_API.g_exc_error;
2506 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2507 RAISE FND_API.g_exc_unexpected_error;
2508 END IF;
2509
2510
2511 IF g_debug THEN
2512 OZF_Utility_PVT.debug_message('Receipt Deduction -> 3. Validate Invoice.');
2513 END IF;
2514 OZF_AR_VALIDATION_PVT.Validate_CreditTo_Information(
2515 p_claim_rec => p_claim_rec
2516 ,p_invoice_id => p_invoice_id
2517 ,x_return_status => l_return_status
2518 );
2519 IF l_return_status = FND_API.g_ret_sts_error THEN
2520 RAISE FND_API.g_exc_error;
2521 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2522 RAISE FND_API.g_exc_unexpected_error;
2523 END IF;
2524
2525 -- ----------------------------------------------
2526 -- Claim Investigation (Deduction) - Invoice
2527 -- ----------------------------------------------
2528 IF p_line_credit = 0 AND
2529 p_tax_credit = 0 AND
2530 p_freight_credit = 0 AND
2531 p_total_credit <> 0 THEN
2532
2533 IF g_debug THEN
2534 OZF_Utility_PVT.debug_message('Receipt Deduction -> 4. Apply same associated invoice with reduced amount');
2535 END IF;
2536 l_new_applied_amount := l_old_applied_invoice.amount_applied - p_sttlmnt_amt;
2537
2538
2539 Apply_on_Receipt(
2540 p_cash_receipt_id => p_claim_rec.receipt_id
2541 ,p_customer_trx_id => p_invoice_id
2542 ,p_new_applied_amount => l_new_applied_amount
2543 ,p_comments => l_old_applied_invoice.comments
2544 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
2545 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
2546 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
2547 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
2548 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
2549 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
2550 ,p_customer_reference => l_old_applied_invoice.customer_reference
2551 ,x_return_status => l_return_status
2552 ,x_msg_data => x_msg_data
2553 ,x_msg_count => x_msg_count
2554 );
2555 IF l_return_status = FND_API.g_ret_sts_error THEN
2556 RAISE FND_API.g_exc_error;
2557 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2558 RAISE FND_API.g_exc_unexpected_error;
2559 END IF;
2560
2561 -- 4. Create credit memo for the invoice.
2562 IF g_debug THEN
2563 OZF_Utility_PVT.debug_message('Receipt Deduction -> 5. Create credit memo for the invoice.');
2564 END IF;
2565 Create_AR_Credit_Memo(
2566 p_claim_rec => p_claim_rec
2567 ,p_customer_trx_id => p_invoice_id
2568 ,p_deduction_type => p_deduction_type
2569 ,p_line_remaining => l_old_applied_invoice.amount_line_items_remaining
2570 ,p_tax_remaining => l_old_applied_invoice.tax_remaining
2571 ,p_freight_remaining => l_old_applied_invoice.freight_remaining
2572 ,p_line_credit => p_line_credit
2573 ,p_tax_credit => p_tax_credit
2574 ,p_freight_credit => p_freight_credit
2575 ,p_total_credit => p_total_credit
2576 ,p_cm_line_tbl => p_cm_line_tbl
2577 ,p_upd_dispute_flag => FND_API.g_false
2578 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2579 ,x_cm_amount => l_cm_amount
2580 ,x_return_status => l_return_status
2581 ,x_msg_data => x_msg_data
2582 ,x_msg_count => x_msg_count
2583 );
2584 IF l_return_status = FND_API.g_ret_sts_error THEN
2585 RAISE FND_API.g_exc_error;
2586 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2587 RAISE FND_API.g_exc_unexpected_error;
2588 END IF;
2589
2590 ELSE
2591 -- ----------------------------------------------
2592 -- Claim Investigation (Deduction) - Credit to Tax/Line/Freight
2593 -- ----------------------------------------------
2594 -- 3. Apply same associated invoice if balance amount is zero.
2595 IF g_debug THEN
2596 OZF_Utility_PVT.debug_message('Receipt Deduction -> 3. Apply same associated invoice if balance amount is zero.');
2597 END IF;
2598 l_new_applied_amount := l_old_applied_invoice.amount_applied - p_claim_rec.amount_settled;
2599
2600 IF l_new_applied_amount = 0 THEN
2601 Apply_on_Receipt(
2602 p_cash_receipt_id => p_claim_rec.receipt_id
2603 ,p_customer_trx_id => p_invoice_id
2604 ,p_new_applied_amount => l_new_applied_amount
2605 ,p_comments => l_old_applied_invoice.comments
2606 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
2607 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
2608 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
2609 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
2610 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
2611 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
2612 ,p_customer_reference => l_old_applied_invoice.customer_reference
2613 ,x_return_status => l_return_status
2614 ,x_msg_data => x_msg_data
2615 ,x_msg_count => x_msg_count
2616 );
2617 IF l_return_status = FND_API.g_ret_sts_error THEN
2618 RAISE FND_API.g_exc_error;
2619 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2620 RAISE FND_API.g_exc_unexpected_error;
2621 END IF;
2622 END IF;
2623
2624 -- 4. Create credit memo for the invoice.
2625 IF g_debug THEN
2626 OZF_Utility_PVT.debug_message('Receipt Deduction -> 4. Create credit memo for the invoice.');
2627 END IF;
2628 Create_AR_Credit_Memo(
2629 p_claim_rec => p_claim_rec
2630 ,p_customer_trx_id => p_invoice_id
2631 ,p_deduction_type => p_deduction_type
2632 ,p_line_remaining => l_old_applied_invoice.amount_line_items_remaining
2633 ,p_tax_remaining => l_old_applied_invoice.tax_remaining
2634 ,p_freight_remaining => l_old_applied_invoice.freight_remaining
2635 ,p_line_credit => p_line_credit
2636 ,p_tax_credit => p_tax_credit
2637 ,p_freight_credit => p_freight_credit
2638 ,p_total_credit => p_total_credit
2639 ,p_cm_line_tbl => p_cm_line_tbl
2640 ,p_upd_dispute_flag => FND_API.g_false
2641 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2642 ,x_cm_amount => l_cm_amount
2643 ,x_return_status => l_return_status
2644 ,x_msg_data => x_msg_data
2645 ,x_msg_count => x_msg_count
2646 );
2647 IF l_return_status = FND_API.g_ret_sts_error THEN
2648 RAISE FND_API.g_exc_error;
2649 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2650 RAISE FND_API.g_exc_unexpected_error;
2651 END IF;
2652
2653
2654 IF g_debug THEN
2655 OZF_Utility_PVT.debug_message('Receipt Deduction -> 5. Apply same associated invoice with reduced amount');
2656 END IF;
2657 l_new_applied_amount := l_old_applied_invoice.amount_applied - p_sttlmnt_amt;
2658
2659 IF l_new_applied_amount <> 0 THEN
2660 Apply_on_Receipt(
2661 p_cash_receipt_id => p_claim_rec.receipt_id
2662 ,p_customer_trx_id => p_invoice_id
2663 ,p_new_applied_amount => l_new_applied_amount
2664 ,p_comments => l_old_applied_invoice.comments
2665 ,p_payment_set_id => l_old_applied_invoice.payment_set_id
2666 ,p_application_ref_type => l_old_applied_invoice.application_ref_type
2667 ,p_application_ref_id => l_old_applied_invoice.application_ref_id
2668 ,p_application_ref_num => l_old_applied_invoice.application_ref_num
2669 ,p_secondary_application_ref_id => l_old_applied_invoice.secondary_application_ref_id
2670 ,p_application_ref_reason => l_old_applied_invoice.application_ref_reason
2671 ,p_customer_reference => l_old_applied_invoice.customer_reference
2672 ,x_return_status => l_return_status
2673 ,x_msg_data => x_msg_data
2674 ,x_msg_count => x_msg_count
2675 );
2676 IF l_return_status = FND_API.g_ret_sts_error THEN
2677 RAISE FND_API.g_exc_error;
2678 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2679 RAISE FND_API.g_exc_unexpected_error;
2680 END IF;
2681 END IF;
2682 END IF;
2683
2684 ELSIF p_deduction_type = 'CLAIM' THEN
2685 /*------------------------------------------------------------*
2686 | Claim - Credit to Tax/Line/Freight
2687 | -> Create credit memo for the invoice.
2688 | Claim - Credit to --
2689 | -> create credit memo for the invoice
2690 *------------------------------------------------------------*/
2691
2692 IF p_line_credit = 0 AND
2693 p_tax_credit = 0 AND
2694 p_freight_credit = 0 AND
2695 p_total_credit <> 0 THEN
2696 IF g_debug THEN
2697 OZF_Utility_PVT.debug_message('Claim [Invoice Credit to --] -> 1. Create Credit Memo');
2698 END IF;
2699 -- 1. Create credit memo for the invoice
2700 Create_AR_Credit_Memo(
2701 p_claim_rec => p_claim_rec
2702 ,p_customer_trx_id => p_invoice_id
2703 ,p_deduction_type => p_deduction_type
2704 ,p_line_remaining => 0
2705 ,p_tax_remaining => 0
2706 ,p_freight_remaining => 0
2707 ,p_line_credit => p_line_credit
2708 ,p_tax_credit => p_tax_credit
2709 ,p_freight_credit => p_freight_credit
2710 ,p_total_credit => p_total_credit
2711 ,p_cm_line_tbl => p_cm_line_tbl
2712 ,p_upd_dispute_flag => FND_API.g_false
2713 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2714 ,x_cm_amount => l_cm_amount
2715 ,x_return_status => l_return_status
2716 ,x_msg_data => x_msg_data
2717 ,x_msg_count => x_msg_count
2718 );
2719 IF l_return_status = FND_API.g_ret_sts_error THEN
2720 RAISE FND_API.g_exc_error;
2721 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2722 RAISE FND_API.g_exc_unexpected_error;
2723 END IF;
2724 ELSE
2725 -- 1. Create credit memo for the invoice
2726 IF g_debug THEN
2727 OZF_Utility_PVT.debug_message('Claim [Invoice Credit to Line/Tax/Freight] -> 1. Create credit memo for the invoice');
2728 END IF;
2729 Create_AR_Credit_Memo(
2730 p_claim_rec => p_claim_rec
2731 ,p_customer_trx_id => p_invoice_id
2732 ,p_deduction_type => p_deduction_type
2733 ,p_line_remaining => 0
2734 ,p_tax_remaining => 0
2735 ,p_freight_remaining => 0
2736 ,p_line_credit => p_line_credit
2737 ,p_tax_credit => p_tax_credit
2738 ,p_freight_credit => p_freight_credit
2739 ,p_total_credit => p_total_credit
2740 ,p_cm_line_tbl => p_cm_line_tbl
2741 ,p_upd_dispute_flag => FND_API.g_false
2742 ,x_cm_customer_trx_id => l_cm_customer_trx_id
2743 ,x_cm_amount => l_cm_amount
2744 ,x_return_status => l_return_status
2745 ,x_msg_data => x_msg_data
2746 ,x_msg_count => x_msg_count
2747 );
2748 IF l_return_status = FND_API.g_ret_sts_error THEN
2749 RAISE FND_API.g_exc_error;
2750 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2751 RAISE FND_API.g_exc_unexpected_error;
2752 END IF;
2753 END IF;
2754 END IF;
2755
2756 /*------------------------------------------------------------*
2757 | Update Deduction payment detail
2758 *------------------------------------------------------------*/
2759 IF l_cm_customer_trx_id IS NOT NULL THEN
2760 -- Update Deduction payment detail
2761 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
2762 p_api_version => l_api_version
2763 ,p_init_msg_list => FND_API.g_false
2764 ,p_commit => FND_API.g_false
2765 ,p_validation_level => FND_API.g_valid_level_full
2766 ,x_return_status => l_return_status
2767 ,x_msg_data => x_msg_data
2768 ,x_msg_count => x_msg_count
2769 ,p_claim_id => p_claim_rec.claim_id
2770 ,p_payment_method => p_claim_rec.payment_method
2771 ,p_deduction_type => p_deduction_type
2772 ,p_cash_receipt_id => NULL
2773 ,p_customer_trx_id => l_cm_customer_trx_id
2774 ,p_adjust_id => NULL
2775 );
2776 IF l_return_status = FND_API.g_ret_sts_error THEN
2777 RAISE FND_API.g_exc_error;
2778 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2779 RAISE FND_API.g_exc_unexpected_error;
2780 END IF;
2781 END IF;
2782
2783 IF g_debug THEN
2784 OZF_Utility_PVT.debug_message(l_full_name||': end');
2785 END IF;
2786 EXCEPTION
2787 WHEN FND_API.g_exc_error THEN
2788 x_return_status := FND_API.g_ret_sts_error;
2789
2790 WHEN FND_API.g_exc_unexpected_error THEN
2791 x_return_status := FND_API.g_ret_sts_unexp_error ;
2792
2793 WHEN OTHERS THEN
2794 x_return_status := FND_API.g_ret_sts_unexp_error ;
2795 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2796 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2797 END IF;
2798
2799 END Pay_by_Single_Invoice_Credit;
2800
2801
2802
2803
2804 /*=======================================================================*
2805 | PROCEDURE
2806 | Pay_by_Invoice_Credit
2807 |
2808 | NOTES
2809 |
2810 | HISTORY
2811 | 15-MAR-2002 mchang Create.
2812 *=======================================================================*/
2813 PROCEDURE Pay_by_Invoice_Credit(
2814 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
2815 ,p_deduction_type IN VARCHAR2
2816
2817 ,x_return_status OUT NOCOPY VARCHAR2
2818 ,x_msg_data OUT NOCOPY VARCHAR2
2819 ,x_msg_count OUT NOCOPY NUMBER
2820 )
2821 IS
2822 l_api_version CONSTANT NUMBER := 1.0;
2823 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Invoice_Credit';
2824 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2825 l_return_status VARCHAR2(1);
2826
2827 l_cm_customer_trx_id NUMBER := NULL;
2828 l_cm_amount NUMBER := 0;
2829 l_new_applied_amount NUMBER;
2830 l_old_applied_amount NUMBER;
2831 l_claim_line_count NUMBER;
2832 l_apply_receipt_id NUMBER;
2833 l_line_activity_type VARCHAR2(30);
2834 l_line_invoice_id NUMBER;
2835 l_line_invoice_line_id NUMBER;
2836 l_old_applied_claim_amount NUMBER;
2837 l_reapply_claim_amount NUMBER;
2838 l_line_remaining NUMBER;
2839 l_tax_remaining NUMBER;
2840 l_freight_remaining NUMBER;
2841 l_line_credit NUMBER;
2842 l_tax_credit NUMBER;
2843 l_freight_credit NUMBER;
2844 l_total_credit NUMBER;
2845 l_cm_line_tbl AR_CREDIT_MEMO_API_PUB.cm_line_tbl_type_cover%TYPE;
2846
2847 l_process_setl_wf BOOLEAN;
2848 l_process_line_cr BOOLEAN;
2849
2850
2851 CURSOR csr_old_applied_amount( cv_cash_receipt_id IN NUMBER
2852 , cv_customer_trx_id IN NUMBER
2853 ) IS
2854 SELECT rec.amount_applied
2855 , pay.amount_due_remaining
2856 , NVL(pay.amount_line_items_remaining, 0) amount_line_items_remaining
2857 , NVL(pay.tax_remaining, 0) tax_remaining
2858 , NVL(pay.freight_remaining, 0) freight_remaining
2859 , rec.comments
2860 , rec.payment_set_id
2861 , rec.application_ref_type
2862 , rec.application_ref_id
2863 , rec.application_ref_num
2864 , rec.secondary_application_ref_id
2865 , rec.application_ref_reason
2866 , rec.customer_reference
2867 FROM ar_receivable_applications rec
2868 , ar_payment_schedules pay
2869 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
2870 AND rec.cash_receipt_id = cv_cash_receipt_id
2871 AND pay.customer_trx_id = cv_customer_trx_id
2872 AND rec.display = 'Y';
2873
2874 l_old_applied_invoice csr_old_applied_amount%ROWTYPE;
2875
2876 CURSOR csr_count_claim_line(cv_claim_id IN NUMBER) IS
2877 SELECT COUNT(claim_line_id)
2878 FROM ozf_claim_lines
2879 WHERE claim_id = cv_claim_id;
2880
2881 CURSOR csr_claim_line_invoice(cv_claim_id IN NUMBER) IS
2882 SELECT source_object_class
2883 , source_object_id
2884 , source_object_line_id
2885 FROM ozf_claim_lines
2886 WHERE claim_id = cv_claim_id;
2887
2888
2889 CURSOR csr_invoice_apply_receipt(cv_invoice_id IN NUMBER) IS
2890 SELECT rec.cash_receipt_id
2891 FROM ar_receivable_applications_all rec
2892 , ar_payment_schedules pay
2893 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
2894 AND pay.customer_trx_id = cv_invoice_id;
2895
2896 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
2897 , cv_root_claim_id IN NUMBER) IS
2898 SELECT rec.amount_applied
2899 FROM ar_receivable_applications rec
2900 WHERE rec.applied_payment_schedule_id = -4
2901 AND rec.cash_receipt_id = cv_cash_receipt_id
2902 AND rec.application_ref_type = 'CLAIM'
2903 AND rec.display = 'Y'
2904 AND rec.secondary_application_ref_id = cv_root_claim_id;
2905
2906 CURSOR csr_customer_trx_lines(cv_invoice_line_id IN NUMBER) IS
2907 SELECT customer_trx_id
2908 FROM ra_customer_trx_lines
2909 WHERE customer_trx_line_id = cv_invoice_line_id;
2910
2911 CURSOR csr_claim_lines(cv_claim_id IN NUMBER) IS
2912 SELECT source_object_id, sum(claim_currency_amount) amt
2913 FROM ozf_claim_lines_all
2914 WHERE claim_id = cv_claim_id
2915 GROUP BY source_object_id;
2916 l_lines_rec csr_claim_lines%ROWTYPE;
2917
2918
2919 BEGIN
2920 -------------------- initialize -----------------------
2921 IF g_debug THEN
2922 OZF_Utility_PVT.debug_message(l_full_name||': start');
2923 END IF;
2924
2925 SAVEPOINT Pay_by_Invoice_Credit;
2926
2927 x_return_status := FND_API.g_ret_sts_success;
2928
2929 ------------------------ start -------------------------
2930 /*------------------------------------------------------------*
2931 | Check Claim Line invoice to see if need to process settement workflow
2932 *------------------------------------------------------------*/
2933 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED' , 'CLAIM') THEN
2934 l_process_setl_wf := OZF_AR_VALIDATION_PVT.Check_to_Process_SETL_WF(
2935 p_claim_rec => p_claim_rec
2936 ,x_return_status => l_return_status
2937 );
2938 IF l_return_status = FND_API.g_ret_sts_error THEN
2939 RAISE FND_API.g_exc_error;
2940 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2941 RAISE FND_API.g_exc_unexpected_error;
2942 END IF;
2943
2944
2945 IF g_debug THEN
2946 IF l_process_setl_wf THEN
2947 OZF_Utility_PVT.debug_message('Process Settlement Workflow? -> Yes' );
2948 ELSE
2949 OZF_Utility_PVT.debug_message('Process Settlement Workflow? -> No' );
2950 END IF;
2951 END IF;
2952
2953 IF l_process_setl_wf THEN
2954 /*------------------------------------------------------------*
2955 | Process Settlement Workflow
2956 *------------------------------------------------------------*/
2957 Process_Settlement_WF(
2958 p_claim_id => p_claim_rec.claim_id
2959 ,x_return_status => l_return_status
2960 ,x_msg_data => x_msg_data
2961 ,x_msg_count => x_msg_count
2962 );
2963 IF l_return_status = FND_API.g_ret_sts_error THEN
2964 RAISE FND_API.g_exc_error;
2965 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2966 RAISE FND_API.g_exc_unexpected_error;
2967 END IF;
2968 l_cm_customer_trx_id := NULL;
2969 ELSE
2970
2971 /*------------------------------------------------------------*
2972 | Update Claim Status to CLOSED.
2973 *------------------------------------------------------------*/
2974 Close_Claim(
2975 p_claim_rec => p_claim_rec
2976 ,x_return_status => l_return_status
2977 ,x_msg_data => x_msg_data
2978 ,x_msg_count => x_msg_count
2979 );
2980 IF l_return_status = FND_API.g_ret_sts_error THEN
2981 RAISE FND_API.g_exc_error;
2982 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2983 RAISE FND_API.g_exc_unexpected_error;
2984 END IF;
2985
2986
2987 -- For each invoice to be credited, get credit details and create creditmemo.
2988 OPEN csr_claim_lines(p_claim_rec.claim_id);
2989 LOOP
2990 FETCH csr_claim_lines INTO l_lines_rec;
2991 EXIT WHEN csr_claim_lines%NOTFOUND;
2992
2993 Get_Inv_Credit_Details(
2994 p_claim_id => p_claim_rec.claim_id
2995 ,p_invoice_id => l_lines_rec.source_object_id
2996 ,x_return_status => l_return_status
2997 ,x_line_credit => l_line_credit
2998 ,x_tax_credit => l_tax_credit
2999 ,x_freight_credit => l_freight_credit
3000 ,x_total_credit => l_total_credit
3001 ,x_cm_line_tbl => l_cm_line_tbl);
3002 IF l_return_status = FND_API.g_ret_sts_error THEN
3003 RAISE FND_API.g_exc_error;
3004 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3005 RAISE FND_API.g_exc_unexpected_error;
3006 END IF;
3007
3008 Pay_by_Single_Invoice_Credit(
3009 p_claim_rec => p_claim_rec
3010 ,p_sttlmnt_amt => l_lines_rec.amt
3011 ,p_invoice_id => l_lines_rec.source_object_id
3012 ,p_deduction_type => p_deduction_type
3013 ,p_line_credit => l_line_credit
3014 ,p_tax_Credit => l_tax_credit
3015 ,p_freight_credit => l_freight_credit
3016 ,p_total_credit => l_total_credit
3017 ,p_cm_line_tbl => l_cm_line_tbl
3018 ,x_return_status => l_return_status
3019 ,x_msg_data => x_msg_data
3020 ,x_msg_count => x_msg_count
3021 );
3022 IF l_return_status = FND_API.g_ret_sts_error THEN
3023 RAISE FND_API.g_exc_error;
3024 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3025 RAISE FND_API.g_exc_unexpected_error;
3026 END IF;
3027 END LOOP;
3028 CLOSE csr_claim_lines;
3029 END IF;
3030 ELSE --p_deduction_type NOT IN ('SOURCE_DED', 'RECEIPT_DED', 'CLAIM')
3031 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3032 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
3033 FND_MESSAGE.set_token('CLAIM_NUMBER', p_claim_rec.claim_number);
3034 FND_MSG_PUB.add;
3035 END IF;
3036 RAISE FND_API.g_exc_error;
3037 END IF;
3038 EXCEPTION
3039 WHEN FND_API.g_exc_error THEN
3040 x_return_status := FND_API.g_ret_sts_error;
3041 ROLLBACK TO Pay_by_Invoice_Credit;
3042
3043 WHEN FND_API.g_exc_unexpected_error THEN
3044 x_return_status := FND_API.g_ret_sts_unexp_error ;
3045 ROLLBACK TO Pay_by_Invoice_Credit;
3046
3047 WHEN OTHERS THEN
3048 x_return_status := FND_API.g_ret_sts_unexp_error ;
3049 ROLLBACK TO Pay_by_Invoice_Credit;
3050 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3051 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3052 END IF;
3053
3054 END Pay_By_Invoice_credit;
3055
3056
3057
3058
3059 /*=======================================================================*
3060 | PROCEDURE
3061 | Pay_by_Credit_Memo
3062 |
3063 | NOTES
3064 |
3065 | HISTORY
3066 | 15-MAR-2002 mchang Create.
3067 *=======================================================================*/
3068 PROCEDURE Pay_by_Credit_Memo(
3069 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3070 ,p_deduction_type IN VARCHAR2
3071 ,p_payment_reference_id IN NUMBER
3072 ,p_credit_memo_amount IN NUMBER
3073
3074 ,x_return_status OUT NOCOPY VARCHAR2
3075 ,x_msg_data OUT NOCOPY VARCHAR2
3076 ,x_msg_count OUT NOCOPY NUMBER
3077 )
3078 IS
3079 l_api_version CONSTANT NUMBER := 1.0;
3080 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Credit_Memo';
3081 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3082 l_return_status VARCHAR2(1);
3083
3084 l_invoice_applied_count NUMBER;
3085 -- l_old_applied_amount NUMBER;
3086 l_new_applied_amount NUMBER;
3087 l_cm_customer_trx_id NUMBER;
3088 l_cm_amount NUMBER;
3089 l_online_upd_ded_status BOOLEAN := FALSE;
3090 l_orig_dispute_amount NUMBER;
3091 l_p_new_applied_amount NUMBER; --4684931
3092 l_p_new_applied_from_amount NUMBER;
3093 l_receipt_currency VARCHAR2(15);
3094 l_trx_currency VARCHAR2(15);
3095 l_cm_applied_on_rec_amt NUMBER;
3096 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
3097 l_cm_applied_on_rec_amt_from NUMBER;
3098
3099 -- Fix for Bug 7494234
3100 CURSOR csr_old_applied_invoice( cv_cash_receipt_id IN NUMBER
3101 , cv_customer_trx_id IN NUMBER
3102 , cv_root_claim_id IN NUMBER ) IS
3103 SELECT rec.application_ref_type
3104 , rec.application_ref_id
3105 , rec.application_ref_num
3106 , rec.secondary_application_ref_id
3107 , sum(rec.amount_applied) amount_applied
3108 , sum(rec.amount_applied_from) amount_applied_from --4684931
3109 FROM ar_receivable_applications rec
3110 , ar_payment_schedules pay
3111 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
3112 AND rec.cash_receipt_id = cv_cash_receipt_id
3113 AND pay.customer_trx_id = cv_customer_trx_id
3114 AND rec.application_ref_type = 'CLAIM'
3115 AND rec.display = 'Y'
3116 AND rec.secondary_application_ref_id = cv_root_claim_id
3117 group by rec.application_ref_type, rec.application_ref_id, rec.application_ref_num,
3118 rec.secondary_application_ref_id;
3119 /*SELECT rec.comments
3120 , rec.payment_set_id
3121 , rec.application_ref_type
3122 , rec.application_ref_id
3123 , rec.application_ref_num
3124 , rec.secondary_application_ref_id
3125 , rec.application_ref_reason
3126 , rec.customer_reference
3127 , rec.amount_applied
3128 , rec.amount_applied_from --4684931
3129 , pay.amount_due_remaining
3130 FROM ar_receivable_applications rec
3131 , ar_payment_schedules pay
3132 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
3133 AND rec.cash_receipt_id = cv_cash_receipt_id
3134 AND pay.customer_trx_id = cv_customer_trx_id
3135 AND rec.application_ref_type = 'CLAIM'
3136 AND rec.display = 'Y'
3137 AND rec.secondary_application_ref_id = cv_root_claim_id;
3138 */
3139
3140 l_old_applied_invoice csr_old_applied_invoice%ROWTYPE;
3141
3142 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
3143 SELECT amount_applied
3144 FROM ar_receivable_applications
3145 WHERE application_ref_type = 'CLAIM'
3146 AND applied_payment_schedule_id = -4
3147 AND display = 'Y'
3148 AND secondary_application_ref_id = cv_root_claim_id;
3149
3150 CURSOR csr_cm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
3151 SELECT amount_applied,
3152 amount_applied_from, --4684931
3153 apply_date -- Fix for Bug 3091401. TM passes old apply date
3154 FROM ar_receivable_applications
3155 WHERE cash_receipt_id = cv_cash_receipt_id
3156 AND applied_customer_trx_id = cv_customer_trx_id
3157 AND display = 'Y'
3158 AND status = 'APP';
3159
3160 --4684931
3161 CURSOR csr_trx_currency(cv_customer_trx_id IN NUMBER) IS
3162 SELECT invoice_currency_code
3163 FROM ra_customer_trx
3164 WHERE customer_trx_id = cv_customer_trx_id;
3165
3166 CURSOR csr_rec_currency(cv_cash_receipt_id IN NUMBER) IS
3167 SELECT currency_code
3168 FROM ar_cash_receipts
3169 WHERE cash_receipt_id = cv_cash_receipt_id;
3170
3171 l_settlement_amount NUMBER := NULL;
3172
3173 BEGIN
3174 -------------------- initialize -----------------------
3175 IF g_debug THEN
3176 OZF_Utility_PVT.debug_message(l_full_name||': start');
3177 END IF;
3178 x_return_status := FND_API.g_ret_sts_success;
3179
3180 ------------------------ start -------------------------
3181 IF p_payment_reference_id IS NULL OR
3182 p_payment_reference_id = FND_API.g_miss_num THEN
3183 /*------------------------------------------------------------*
3184 | No payment reference specified (No open credit memo specified) -> AutoInvoice
3185 *------------------------------------------------------------*/
3186 IF g_debug THEN
3187 OZF_Utility_PVT.debug_message('No payment reference specified (No open credit memo specified) -> AutoInvoice.');
3188 END IF;
3189 -- 1. AutoInvoice
3190 OZF_AR_INTERFACE_PVT.Interface_Claim(
3191 p_api_version => l_api_version
3192 ,p_init_msg_list => FND_API.g_false
3193 ,p_commit => FND_API.g_false
3194 ,p_validation_level => FND_API.g_valid_level_full
3195 ,x_return_status => l_return_status
3196 ,x_msg_data => x_msg_data
3197 ,x_msg_count => x_msg_count
3198 ,p_claim_id => p_claim_rec.claim_id
3199 );
3200 IF l_return_status = FND_API.g_ret_sts_error THEN
3201 RAISE FND_API.g_exc_error;
3202 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3203 RAISE FND_API.g_exc_unexpected_error;
3204 END IF;
3205
3206 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
3207 l_online_upd_ded_status := FALSE;
3208
3209 ELSE -- payment_reference_id is not null and deduction_type IN ('SOURCE_DED', 'RECEIPT_DED')
3210 /*------------------------------------------------------------*
3211 | Update Claim Status to CLOSED.
3212 *------------------------------------------------------------*/
3213 Close_Claim(
3214 p_claim_rec => p_claim_rec
3215 ,x_return_status => l_return_status
3216 ,x_msg_data => x_msg_data
3217 ,x_msg_count => x_msg_count
3218 );
3219 IF l_return_status = FND_API.g_ret_sts_error THEN
3220 RAISE FND_API.g_exc_error;
3221 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3222 RAISE FND_API.g_exc_unexpected_error;
3223 END IF;
3224
3225 IF p_claim_rec.receipt_id IS NOT NULL THEN
3226 OPEN csr_cm_exist_on_rec(p_claim_rec.receipt_id, p_claim_rec.payment_reference_id);
3227 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
3228 CLOSE csr_cm_exist_on_rec;
3229 END IF;
3230
3231 IF p_deduction_type = 'CLAIM' THEN
3232 l_online_upd_ded_status := TRUE;
3233
3234 ELSIF p_deduction_type = 'SOURCE_DED' THEN
3235 /*------------------------------------------------------------*
3236 | <<OLD>>
3237 | Invoice Deduction -> 1. Update amount in dispute
3238 | 2. Unapply invoice from receipt.
3239 | 3. Apply credit memo with amount_settled on receipt.
3240 | 4. Apply original invoice with old balance + amount_settled.
3241 | <<NEW: AR One off patch 2367036>>
3242 | Invoice Deduction -> 1. Update amount in dispute
3243 | 2. Apply credit memo with amount_settled on receipt.
3244 | 3. Reapply invoice related deduction.
3245 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
3246 | Invoice Deduction -> 1. Update amount in dispute
3247 | 1.5. Unapply existing credit memo
3248 | 2. ReApply credit memo with increase amount on receipt.
3249 | 3. Reapply invoice related deduction.
3250 *------------------------------------------------------------*/
3251
3252 OPEN csr_old_applied_invoice( p_claim_rec.receipt_id
3253 , p_claim_rec.source_object_id
3254 , p_claim_rec.root_claim_id
3255 );
3256 FETCH csr_old_applied_invoice INTO l_old_applied_invoice;
3257 CLOSE csr_old_applied_invoice;
3258
3259 --4684931
3260 OPEN csr_rec_currency(p_claim_rec.receipt_id);
3261 FETCH csr_rec_currency INTO l_receipt_currency;
3262 CLOSE csr_rec_currency;
3263
3264 OPEN csr_trx_currency(p_payment_reference_id);
3265 FETCH csr_trx_currency INTO l_trx_currency;
3266 CLOSE csr_trx_currency;
3267
3268 IF g_debug THEN
3269 OZF_Utility_PVT.debug_message('Invoice Deduction -> 1. Update amount in dispute');
3270 END IF;
3271 -- 1. Update amount in dispute
3272 Update_dispute_amount(
3273 p_claim_rec => p_claim_rec
3274 ,p_dispute_amount => (p_credit_memo_amount * -1)
3275 ,x_return_status => l_return_status
3276 ,x_msg_data => x_msg_data
3277 ,x_msg_count => x_msg_count
3278 );
3279 IF l_return_status = FND_API.g_ret_sts_error THEN
3280 RAISE FND_API.g_exc_error;
3281 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3282 RAISE FND_API.g_exc_unexpected_error;
3283 END IF;
3284
3285 IF l_cm_applied_on_rec_amt IS NULL THEN
3286 IF g_debug THEN
3287 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Apply creit memo on receipt');
3288 END IF;
3289 --4684931
3290 IF l_trx_currency = p_claim_rec.currency_code THEN
3291 l_p_new_applied_amount := p_credit_memo_amount * -1;
3292 l_p_new_applied_from_amount := NULL;
3293 /*
3294 ELSE
3295 ??
3296 */
3297 END IF;
3298
3299 -- 2. Apply creit memo on receipt
3300 Apply_on_Receipt(
3301 p_cash_receipt_id => p_claim_rec.receipt_id
3302 --,p_receipt_number => p_claim_rec.receipt_number
3303 ,p_customer_trx_id => p_payment_reference_id
3304 ,p_new_applied_amount => l_p_new_applied_amount --4684931
3305 ,p_new_applied_from_amount => l_p_new_applied_from_amount
3306 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
3307 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
3308 ,x_return_status => l_return_status
3309 ,x_msg_data => x_msg_data
3310 ,x_msg_count => x_msg_count
3311 );
3312 IF l_return_status = FND_API.g_ret_sts_error THEN
3313 RAISE FND_API.g_exc_error;
3314 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3315 RAISE FND_API.g_exc_unexpected_error;
3316 END IF;
3317 ELSE
3318 IF g_debug THEN
3319 OZF_Utility_PVT.debug_message('Invoice Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
3320 OZF_Utility_PVT.debug_message('Invoice Deduction -> 2. Reapply credit memo with new amount on receipt');
3321 END IF;
3322
3323 l_settlement_amount := (p_credit_memo_amount * -1) ; -- Bug4308188
3324
3325 --4684931
3326 IF l_trx_currency = p_claim_rec.currency_code THEN
3327 l_p_new_applied_amount := l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1);
3328 l_p_new_applied_from_amount := NULL;
3329 END IF;
3330
3331 -- 2. Reapply credit memo on receipt
3332 arp_deduction_cover2.reapply_credit_memo(
3333 p_customer_trx_id => p_payment_reference_id ,
3334 p_cash_receipt_id => p_claim_rec.receipt_id,
3335 p_amount_applied => l_p_new_applied_amount, --4684931
3336 p_init_msg_list => FND_API.g_false,
3337 x_return_status => l_return_status,
3338 x_msg_count => x_msg_count,
3339 x_msg_data => x_msg_data);
3340 IF l_return_status = FND_API.g_ret_sts_error THEN
3341 RAISE FND_API.g_exc_error;
3342 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3343 RAISE FND_API.g_exc_unexpected_error;
3344 END IF;
3345
3346 END IF;
3347
3348 IF g_debug THEN
3349 OZF_Utility_PVT.debug_message('Invoice Deduction -> 3. Unapply claim investigation');
3350 OZF_Utility_PVT.debug_message('original invoice deduction amount = '||l_old_applied_invoice.amount_applied);
3351 OZF_Utility_PVT.debug_message('reapply invoice deduction amount = '||(l_old_applied_invoice.amount_applied + p_credit_memo_amount));
3352 END IF;
3353 -- 3. Reapply claim investigation
3354 Unapply_Claim_Investigation(
3355 p_claim_rec => p_claim_rec
3356 ,p_reapply_amount => l_old_applied_invoice.amount_applied + p_credit_memo_amount
3357 ,x_return_status => l_return_status
3358 ,x_msg_data => x_msg_data
3359 ,x_msg_count => x_msg_count
3360 );
3361 IF l_return_status = FND_API.g_ret_sts_error THEN
3362 RAISE FND_API.g_exc_error;
3363 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3364 RAISE FND_API.g_exc_unexpected_error;
3365 END IF;
3366
3367 l_online_upd_ded_status := TRUE;
3368
3369 ELSIF p_deduction_type = 'RECEIPT_DED' THEN
3370 --4684931
3371 l_receipt_currency := p_claim_rec.currency_code;
3372
3373 OPEN csr_trx_currency(p_payment_reference_id);
3374 FETCH csr_trx_currency INTO l_trx_currency;
3375 CLOSE csr_trx_currency;
3376
3377 IF l_cm_applied_on_rec_amt IS NULL THEN
3378 /*------------------------------------------------------------*
3379 | Receipt Deduction -> 1. Apply credit memo on receipt.
3380 | 2. Unapply claim investigation
3381 | <<Pay by Previous Open Credit Memo which already exists on the receipt>>:
3382 | Receipt Deduction -> 0.5. Unapply credit memo on receipt
3383 | 1. Apply credit memo with increased amount on receipt
3384 | 2. Unapply claim investigation
3385 *------------------------------------------------------------*/
3386 IF g_debug THEN
3387 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Apply creit memo on receipt');
3388 END IF;
3389
3390 --4684931
3391 IF l_receipt_currency = l_trx_currency THEN
3392 l_p_new_applied_amount := p_credit_memo_amount * -1;
3393 l_p_new_applied_from_amount := NULL;
3394 ELSE
3395 l_p_new_applied_amount := NULL;
3396 l_p_new_applied_from_amount := p_credit_memo_amount * -1;
3397 END IF;
3398
3399 -- 1. Apply creit memo on receipt
3400 Apply_on_Receipt(
3401 p_cash_receipt_id => p_claim_rec.receipt_id
3402 ,p_customer_trx_id => p_payment_reference_id
3403 ,p_new_applied_amount => l_p_new_applied_amount --4684931
3404 ,p_new_applied_from_amount => l_p_new_applied_from_amount
3405 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
3406 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
3407 ,x_return_status => l_return_status
3408 ,x_msg_data => x_msg_data
3409 ,x_msg_count => x_msg_count
3410 );
3411 IF l_return_status = FND_API.g_ret_sts_error THEN
3412 RAISE FND_API.g_exc_error;
3413 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3414 RAISE FND_API.g_exc_unexpected_error;
3415 END IF;
3416 IF g_debug THEN
3417 OZF_Utility_PVT.debug_message('Receipt Deduction -> 2. Unapply claim investigation');
3418 END IF;
3419 ELSE
3420 /*------------------------------------------------------------*
3421 | Receipt Deduction
3422 *------------------------------------------------------------*/
3423 IF g_debug THEN
3424 OZF_Utility_PVT.debug_message('Receipt Deduction: Pay by Previous Open Credit Memo which already exists on the receipt');
3425 END IF;
3426
3427 IF g_debug THEN
3428 OZF_Utility_PVT.debug_message('Receipt Deduction -> 1. Reapply creit memo with increased amount on receipt');
3429 END IF;
3430
3431 l_settlement_amount := (p_credit_memo_amount * -1); -- Bug4308188
3432 --4684931
3433 IF l_receipt_currency = l_trx_currency THEN
3434 l_p_new_applied_amount := l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1);
3435 l_p_new_applied_from_amount := NULL;
3436 ELSE
3437 l_p_new_applied_amount := NULL;
3438 l_p_new_applied_from_amount := l_cm_applied_on_rec_amt + (p_credit_memo_amount * -1);
3439 END IF;
3440
3441 arp_deduction_cover2.reapply_credit_memo(
3442 p_customer_trx_id => p_payment_reference_id ,
3443 p_cash_receipt_id => p_claim_rec.receipt_id,
3444 p_amount_applied => l_p_new_applied_amount,
3445 p_init_msg_list => FND_API.g_false,
3446 x_return_status => l_return_status,
3447 x_msg_count => x_msg_count,
3448 x_msg_data => x_msg_data);
3449 IF l_return_status = FND_API.g_ret_sts_error THEN
3450 RAISE FND_API.g_exc_error;
3451 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3452 RAISE FND_API.g_exc_unexpected_error;
3453 END IF;
3454 END IF;
3455
3456 -- 2. Unapply claim investigation
3457 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
3458 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
3459 CLOSE csr_claim_investigation_amount;
3460
3461 IF g_debug THEN
3462 OZF_Utility_PVT.debug_message('original claim investigation amount = '||l_orig_dispute_amount);
3463 OZF_Utility_PVT.debug_message('reapply claim investigation amount = '||(l_orig_dispute_amount + p_credit_memo_amount));
3464 END IF;
3465 Unapply_Claim_Investigation(
3466 p_claim_rec => p_claim_rec
3467 ,p_reapply_amount => l_orig_dispute_amount + p_credit_memo_amount --(l_orig_reapply_amount - p_credit_memo_amount) * -1
3468 ,x_return_status => l_return_status
3469 ,x_msg_data => x_msg_data
3470 ,x_msg_count => x_msg_count
3471 );
3472 IF l_return_status = FND_API.g_ret_sts_error THEN
3473 RAISE FND_API.g_exc_error;
3474 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3475 RAISE FND_API.g_exc_unexpected_error;
3476 END IF;
3477 l_online_upd_ded_status := TRUE;
3478 END IF; -- end if p_deduction_type
3479 END IF; -- end if payment_reference_id is null
3480
3481 /*------------------------------------------------------------*
3482 | Update Deduction payment detail
3483 *------------------------------------------------------------*/
3484 IF l_online_upd_ded_status THEN
3485 -- Update Deduction payment detail
3486 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
3487 p_api_version => l_api_version
3488 ,p_init_msg_list => FND_API.g_false
3489 ,p_commit => FND_API.g_false
3490 ,p_validation_level => FND_API.g_valid_level_full
3491 ,x_return_status => l_return_status
3492 ,x_msg_data => x_msg_data
3493 ,x_msg_count => x_msg_count
3494 ,p_claim_id => p_claim_rec.claim_id
3495 ,p_payment_method => p_claim_rec.payment_method
3496 ,p_deduction_type => p_deduction_type
3497 ,p_cash_receipt_id => p_claim_rec.receipt_id
3498 ,p_customer_trx_id => p_payment_reference_id
3499 ,p_adjust_id => NULL
3500 ,p_settlement_amount => l_settlement_amount -- Bug4308188
3501 );
3502 IF l_return_status = FND_API.g_ret_sts_error THEN
3503 RAISE FND_API.g_exc_error;
3504 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3505 RAISE FND_API.g_exc_unexpected_error;
3506 END IF;
3507 END IF;
3508
3509 IF g_debug THEN
3510 OZF_Utility_PVT.debug_message(l_full_name||': end');
3511 END IF;
3512 EXCEPTION
3513 WHEN FND_API.g_exc_error THEN
3514 x_return_status := FND_API.g_ret_sts_error;
3515
3516 WHEN FND_API.g_exc_unexpected_error THEN
3517 x_return_status := FND_API.g_ret_sts_unexp_error ;
3518
3519 WHEN OTHERS THEN
3520 x_return_status := FND_API.g_ret_sts_unexp_error ;
3521 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3522 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3523 END IF;
3524
3525 END Pay_by_Credit_Memo;
3526
3527
3528 /*=======================================================================*
3529 | PROCEDURE
3530 | Pay_by_On_Account_Credit
3531 |
3532 | NOTES
3533 |
3534 | HISTORY
3535 | 15-MAR-2002 mchang Create.
3536 *=======================================================================*/
3537 PROCEDURE Pay_by_On_Account_Credit(
3538 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3539 ,p_deduction_type IN VARCHAR2
3540
3541 ,x_return_status OUT NOCOPY VARCHAR2
3542 ,x_msg_data OUT NOCOPY VARCHAR2
3543 ,x_msg_count OUT NOCOPY NUMBER
3544 )
3545 IS
3546 l_api_version CONSTANT NUMBER := 1.0;
3547 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_On_Account_Credit';
3548 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3549 l_return_status VARCHAR2(1);
3550
3551 CURSOR csr_old_claim_investigation( cv_cash_receipt_id IN NUMBER
3552 , cv_root_claim_id IN NUMBER) IS
3553 SELECT rec.amount_applied
3554 FROM ar_receivable_applications rec
3555 WHERE rec.applied_payment_schedule_id = -4
3556 AND rec.cash_receipt_id = cv_cash_receipt_id
3557 AND rec.application_ref_type = 'CLAIM'
3558 AND rec.display = 'Y'
3559 AND rec.secondary_application_ref_id = cv_root_claim_id;
3560
3561 l_old_applied_claim_amount NUMBER;
3562 l_reapply_claim_amount NUMBER;
3563
3564 BEGIN
3565 -------------------- initialize -----------------------
3566 IF g_debug THEN
3567 OZF_Utility_PVT.debug_message(l_full_name||': start');
3568 END IF;
3569
3570 x_return_status := FND_API.g_ret_sts_success;
3571
3572 ------------------------ start -------------------------
3573 /*------------------------------------------------------------*
3574 | Update Claim Status to CLOSED.
3575 *------------------------------------------------------------*/
3576 Close_Claim(
3577 p_claim_rec => p_claim_rec
3578 ,x_return_status => l_return_status
3579 ,x_msg_data => x_msg_data
3580 ,x_msg_count => x_msg_count
3581 );
3582 IF l_return_status = FND_API.g_ret_sts_error THEN
3583 RAISE FND_API.g_exc_error;
3584 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3585 RAISE FND_API.g_exc_unexpected_error;
3586 END IF;
3587
3588 IF p_deduction_type = 'RECEIPT_OPM' THEN
3589 /*------------------------------------------------------------*
3590 | Overpayment -> 1. Unapply claim investigation
3591 | 2. Apply On Account Credit
3592 *------------------------------------------------------------*/
3593 IF g_debug THEN
3594 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation.');
3595 END IF;
3596 -- 1. Unapply claim investigation
3597 OPEN csr_old_claim_investigation(p_claim_rec.receipt_id, p_claim_rec.root_claim_id);
3598 FETCH csr_old_claim_investigation INTO l_old_applied_claim_amount;
3599 CLOSE csr_old_claim_investigation;
3600
3601 l_reapply_claim_amount := l_old_applied_claim_amount - (p_claim_rec.amount_settled * -1);
3602
3603 Unapply_Claim_Investigation(
3604 p_claim_rec => p_claim_rec
3605 ,p_reapply_amount => l_reapply_claim_amount --0
3606 ,x_return_status => l_return_status
3607 ,x_msg_data => x_msg_data
3608 ,x_msg_count => x_msg_count
3609 );
3610 IF l_return_status = FND_API.g_ret_sts_error THEN
3611 RAISE FND_API.g_exc_error;
3612 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3613 RAISE FND_API.g_exc_unexpected_error;
3614 END IF;
3615
3616 IF g_debug THEN
3617 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply On Account Credit.');
3618 END IF;
3619 --2. Apply On Account Credit
3620 Apply_On_Account_Credit(
3621 p_claim_rec => p_claim_rec
3622 ,x_return_status => l_return_status
3623 ,x_msg_data => x_msg_data
3624 ,x_msg_count => x_msg_count
3625 );
3626 IF l_return_status = FND_API.g_ret_sts_error THEN
3627 RAISE FND_API.g_exc_error;
3628 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3629 RAISE FND_API.g_exc_unexpected_error;
3630 END IF;
3631
3632 /*------------------------------------------------------------*
3633 | Update Deduction payment detail
3634 *------------------------------------------------------------*/
3635 -- Update Deduction payment detail
3636 IF g_debug THEN
3637 OZF_Utility_PVT.debug_message('cash_receipt_id = '||p_claim_rec.receipt_id);
3638 END IF;
3639 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
3640 p_api_version => l_api_version
3641 ,p_init_msg_list => FND_API.g_false
3642 ,p_commit => FND_API.g_false
3643 ,p_validation_level => FND_API.g_valid_level_full
3644 ,x_return_status => l_return_status
3645 ,x_msg_data => x_msg_data
3646 ,x_msg_count => x_msg_count
3647 ,p_claim_id => p_claim_rec.claim_id
3648 ,p_payment_method => p_claim_rec.payment_method
3649 ,p_deduction_type => p_deduction_type
3650 ,p_cash_receipt_id => p_claim_rec.receipt_id
3651 ,p_customer_trx_id => NULL --p_claim_rec.payment_reference_id
3652 ,p_adjust_id => NULL
3653 );
3654 IF l_return_status = FND_API.g_ret_sts_error THEN
3655 RAISE FND_API.g_exc_error;
3656 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3657 RAISE FND_API.g_exc_unexpected_error;
3658 END IF;
3659 ELSE
3660 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3661 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
3662 FND_MSG_PUB.add;
3663 END IF;
3664 RAISE FND_API.g_exc_error;
3665 END IF;
3666
3667 IF g_debug THEN
3668 OZF_Utility_PVT.debug_message(l_full_name||': end');
3669 END IF;
3670 EXCEPTION
3671 WHEN FND_API.g_exc_error THEN
3672 x_return_status := FND_API.g_ret_sts_error;
3673
3674 WHEN FND_API.g_exc_unexpected_error THEN
3675 x_return_status := FND_API.g_ret_sts_unexp_error ;
3676
3677 WHEN OTHERS THEN
3678 x_return_status := FND_API.g_ret_sts_unexp_error ;
3679 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3680 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3681 END IF;
3682
3683 END Pay_by_On_Account_Credit;
3684
3685
3686 /*=======================================================================*
3687 | PROCEDURE
3688 | Pay_by_Chargeback
3689 |
3690 | NOTES
3691 |
3692 | HISTORY
3693 | 15-MAR-2002 mchang Create.
3694 *=======================================================================*/
3695 PROCEDURE Pay_by_Chargeback(
3696 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3697 ,p_deduction_type IN VARCHAR2
3698
3699 ,x_return_status OUT NOCOPY VARCHAR2
3700 ,x_msg_data OUT NOCOPY VARCHAR2
3701 ,x_msg_count OUT NOCOPY NUMBER
3702 )
3703 IS
3704 l_api_version CONSTANT NUMBER := 1.0;
3705 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Chargeback';
3706 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3707 l_return_status VARCHAR2(1);
3708
3709 l_cb_customer_trx_id NUMBER;
3710 l_chargeback_amount NUMBER;
3711
3712 BEGIN
3713 -------------------- initialize -----------------------
3714 IF g_debug THEN
3715 OZF_Utility_PVT.debug_message(l_full_name||': start');
3716 END IF;
3717
3718 x_return_status := FND_API.g_ret_sts_success;
3719
3720 ------------------------ start -------------------------
3721 /*------------------------------------------------------------*
3722 | Update Claim Status to CLOSED.
3723 *------------------------------------------------------------*/
3724 Close_Claim(
3725 p_claim_rec => p_claim_rec
3726 ,x_return_status => l_return_status
3727 ,x_msg_data => x_msg_data
3728 ,x_msg_count => x_msg_count
3729 );
3730 IF l_return_status = FND_API.g_ret_sts_error THEN
3731 RAISE FND_API.g_exc_error;
3732 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3733 RAISE FND_API.g_exc_unexpected_error;
3734 END IF;
3735
3736 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED') THEN
3737 IF p_deduction_type = 'SOURCE_DED'THEN
3738 l_chargeback_amount := p_claim_rec.amount_settled;
3739 ELSIF p_deduction_type = 'RECEIPT_DED'THEN
3740 l_chargeback_amount := p_claim_rec.amount_settled * -1;
3741 END IF;
3742
3743 Create_AR_Chargeback(
3744 p_claim_rec => p_claim_rec
3745 ,p_chargeback_amount => l_chargeback_amount
3746 ,x_cb_customer_trx_id => l_cb_customer_trx_id
3747 ,x_return_status => l_return_status
3748 ,x_msg_data => x_msg_data
3749 ,x_msg_count => x_msg_count
3750 );
3751 IF l_return_status = FND_API.g_ret_sts_error THEN
3752 RAISE FND_API.g_exc_error;
3753 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3754 RAISE FND_API.g_exc_unexpected_error;
3755 END IF;
3756
3757 /*------------------------------------------------------------*
3758 | Update Deduction payment detail
3759 *------------------------------------------------------------*/
3760 IF l_cb_customer_trx_id IS NOT NULL THEN
3761 -- Update Deduction payment detail
3762 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
3763 p_api_version => l_api_version
3764 ,p_init_msg_list => FND_API.g_false
3765 ,p_commit => FND_API.g_false
3766 ,p_validation_level => FND_API.g_valid_level_full
3767 ,x_return_status => l_return_status
3768 ,x_msg_data => x_msg_data
3769 ,x_msg_count => x_msg_count
3770 ,p_claim_id => p_claim_rec.claim_id
3771 ,p_payment_method => p_claim_rec.payment_method
3772 ,p_deduction_type => p_deduction_type
3773 ,p_cash_receipt_id => p_claim_rec.receipt_id
3774 ,p_customer_trx_id => l_cb_customer_trx_id
3775 ,p_adjust_id => NULL
3776 );
3777 IF l_return_status = FND_API.g_ret_sts_error THEN
3778 RAISE FND_API.g_exc_error;
3779 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3780 RAISE FND_API.g_exc_unexpected_error;
3781 END IF;
3782 END IF;
3783
3784 ELSE
3785 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3786 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
3787 FND_MSG_PUB.add;
3788 END IF;
3789 END IF;
3790
3791
3792 IF g_debug THEN
3793 OZF_Utility_PVT.debug_message(l_full_name||': end');
3794 END IF;
3795 EXCEPTION
3796 WHEN FND_API.g_exc_error THEN
3797 x_return_status := FND_API.g_ret_sts_error;
3798
3799 WHEN FND_API.g_exc_unexpected_error THEN
3800 x_return_status := FND_API.g_ret_sts_unexp_error ;
3801
3802 WHEN OTHERS THEN
3803 x_return_status := FND_API.g_ret_sts_unexp_error ;
3804 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3805 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3806 END IF;
3807
3808 END Pay_by_Chargeback;
3809
3810
3811 /*=======================================================================*
3812 | PROCEDURE
3813 | Pay_by_Write_Off
3814 |
3815 | NOTES
3816 |
3817 | HISTORY
3818 | 15-MAR-2002 mchang Create.
3819 *=======================================================================*/
3820 PROCEDURE Pay_by_Write_Off(
3821 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3822 ,p_deduction_type IN VARCHAR2
3823
3824 ,x_return_status OUT NOCOPY VARCHAR2
3825 ,x_msg_data OUT NOCOPY VARCHAR2
3826 ,x_msg_count OUT NOCOPY NUMBER
3827 )
3828 IS
3829 l_api_version CONSTANT NUMBER := 1.0;
3830 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Write_Off';
3831 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3832 l_return_status VARCHAR2(1);
3833
3834 l_wo_adjust_id NUMBER;
3835
3836 BEGIN
3837 -------------------- initialize -----------------------
3838 IF g_debug THEN
3839 OZF_Utility_PVT.debug_message(l_full_name||': start');
3840 END IF;
3841
3842 x_return_status := FND_API.g_ret_sts_success;
3843
3844 ------------------------ start -------------------------
3845 IF p_deduction_type = 'RECEIPT_DED' AND
3846 NOT ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed THEN
3847 /*------------------------------------------------------------
3848 | Receipt Deduction -> Invoke Settlement Workflow
3849 *-----------------------------------------------------------*/
3850 Process_Settlement_WF(
3851 p_claim_id => p_claim_rec.claim_id
3852 ,x_return_status => l_return_status
3853 ,x_msg_data => x_msg_data
3854 ,x_msg_count => x_msg_count
3855 );
3856 IF l_return_status = FND_API.g_ret_sts_error THEN
3857 RAISE FND_API.g_exc_error;
3858 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3859 RAISE FND_API.g_exc_unexpected_error;
3860 END IF;
3861
3862 ELSIF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED', 'RECEIPT_OPM') THEN
3863 /*------------------------------------------------------------*
3864 | Update Claim Status to CLOSED.
3865 *------------------------------------------------------------*/
3866 Close_Claim(
3867 p_claim_rec => p_claim_rec
3868 ,x_return_status => l_return_status
3869 ,x_msg_data => x_msg_data
3870 ,x_msg_count => x_msg_count
3871 );
3872 IF l_return_status = FND_API.g_ret_sts_error THEN
3873 RAISE FND_API.g_exc_error;
3874 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3875 RAISE FND_API.g_exc_unexpected_error;
3876 END IF;
3877
3878 Create_AR_Write_Off(
3879 p_claim_rec => p_claim_rec
3880 ,p_deduction_type => p_deduction_type
3881 ,p_write_off_amount => p_claim_rec.amount_settled
3882 ,x_wo_adjust_id => l_wo_adjust_id
3883 ,x_return_status => l_return_status
3884 ,x_msg_data => x_msg_data
3885 ,x_msg_count => x_msg_count
3886 );
3887 IF l_return_status = FND_API.g_ret_sts_error THEN
3888 RAISE FND_API.g_exc_error;
3889 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3890 RAISE FND_API.g_exc_unexpected_error;
3891 END IF;
3892 END IF;
3893
3894 IF g_debug THEN
3895 OZF_Utility_PVT.debug_message(l_full_name||': end');
3896 END IF;
3897 EXCEPTION
3898 WHEN FND_API.g_exc_error THEN
3899 x_return_status := FND_API.g_ret_sts_error;
3900
3901 WHEN FND_API.g_exc_unexpected_error THEN
3902 x_return_status := FND_API.g_ret_sts_unexp_error ;
3903
3904 WHEN OTHERS THEN
3905 x_return_status := FND_API.g_ret_sts_unexp_error ;
3906 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3907 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3908 END IF;
3909
3910 END Pay_by_Write_Off;
3911
3912
3913 /*=======================================================================*
3914 | PROCEDURE
3915 | Pay_by_Debit_Memo
3916 |
3917 | NOTES
3918 |
3919 | HISTORY
3920 | 15-MAR-2002 mchang Create.
3921 *=======================================================================*/
3922 PROCEDURE Pay_by_Debit_Memo(
3923 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
3924 ,p_deduction_type IN VARCHAR2
3925 ,p_payment_reference_id IN NUMBER
3926 ,p_debit_memo_amount IN NUMBER
3927
3928 ,x_return_status OUT NOCOPY VARCHAR2
3929 ,x_msg_data OUT NOCOPY VARCHAR2
3930 ,x_msg_count OUT NOCOPY NUMBER
3931 )
3932 IS
3933 l_api_version CONSTANT NUMBER := 1.0;
3934 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Debit_Memo';
3935 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3936 l_return_status VARCHAR2(1);
3937
3938 l_dm_trx_type_id NUMBER;
3939 l_online_upd_ded_status BOOLEAN := FALSE;
3940 l_orig_dispute_amount NUMBER;
3941 l_payment_trx_number VARCHAR2(30);
3942 l_p_new_applied_amount NUMBER;
3943 l_p_new_applied_from_amount NUMBER; --bug 4684931
3944 l_receipt_currency VARCHAR2(15);
3945 l_trx_currency VARCHAR2(15);
3946 l_dm_applied_on_rec_amt NUMBER ;
3947 l_apply_date ar_receivable_applications.apply_date%TYPE; -- Fix for Bug 3091401. TM passes old apply date
3948 l_dm_applied_on_rec_amt_from NUMBER;
3949
3950 CURSOR csr_dm_trx_type_id(cv_claim_type_id IN NUMBER) IS
3951 SELECT dm_trx_type_id
3952 FROM ozf_claim_types_all_b
3953 WHERE claim_type_id = cv_claim_type_id;
3954
3955 CURSOR csr_claim_investigation_amount(cv_root_claim_id IN NUMBER) IS
3956 SELECT amount_applied
3957 FROM ar_receivable_applications
3958 WHERE application_ref_type = 'CLAIM'
3959 AND applied_payment_schedule_id = -4
3960 AND display = 'Y'
3961 AND secondary_application_ref_id = cv_root_claim_id;
3962
3963 CURSOR csr_payment_trx_number(cv_customer_trx_id IN NUMBER) IS
3964 SELECT trx_number
3965 FROM ra_customer_trx
3966 WHERE customer_trx_id = cv_customer_trx_id;
3967
3968 CURSOR csr_dm_exist_on_rec(cv_cash_receipt_id IN NUMBER, cv_customer_trx_id IN NUMBER) IS
3969 SELECT amount_applied,
3970 amount_applied_from,--bug 4684931
3971 apply_date -- Fix for Bug 3091401. TM passes old apply date
3972 FROM ar_receivable_applications
3973 WHERE cash_receipt_id = cv_cash_receipt_id
3974 AND applied_customer_trx_id = cv_customer_trx_id
3975 AND display = 'Y'
3976 AND status = 'APP';
3977
3978 l_settlement_amount NUMBER := NULL ;
3979
3980 -- Start Fix for Bug4324426
3981 CURSOR csr_ded_details(p_customer_trx_id IN NUMBER) IS
3982 SELECT claim_id, amount_due_remaining FROM ozf_claims_all, ar_payment_schedules_all
3983 WHERE source_object_id = customer_trx_id
3984 AND source_object_class = class
3985 AND claim_class = 'DEDUCTION'
3986 AND cust_account_id = customer_id
3987 AND customer_trx_id = p_customer_trx_id;
3988
3989 --bug 4684931
3990 CURSOR csr_trx_currency(cv_customer_trx_id IN NUMBER) IS
3991 SELECT invoice_currency_code
3992 FROM ra_customer_trx
3993 WHERE customer_trx_id = cv_customer_trx_id;
3994
3995 l_deduction_id NUMBER;
3996 l_amt_due_remaining NUMBER;
3997 l_object_ver_number NUMBER;
3998
3999 l_deduction_rec OZF_CLAIM_GRP.DEDUCTION_REC_TYPE;
4000 l_stlmnt_amount NUMBER;
4001 -- End Fix for Bug4324426
4002
4003 BEGIN
4004 -------------------- initialize -----------------------
4005 IF g_debug THEN
4006 OZF_Utility_PVT.debug_message(l_full_name||': start');
4007 END IF;
4008
4009 x_return_status := FND_API.g_ret_sts_success;
4010
4011 ------------------------ start -------------------------
4012 IF p_deduction_type IN ('RECEIPT_OPM', 'CHARGE') THEN
4013 IF p_payment_reference_id IS NULL THEN
4014 /*------------------------------------------------------------*
4015 | OVERPAYMENT -> No open debit memo specified --> AutoInvoice
4016 *------------------------------------------------------------*/
4017 -- 1. AutoInvoice
4018 OZF_AR_INTERFACE_PVT.Interface_Claim(
4019 p_api_version => l_api_version
4020 ,p_init_msg_list => FND_API.g_false
4021 ,p_commit => FND_API.g_false
4022 ,p_validation_level => FND_API.g_valid_level_full
4023 ,x_return_status => l_return_status
4024 ,x_msg_data => x_msg_data
4025 ,x_msg_count => x_msg_count
4026 ,p_claim_id => p_claim_rec.claim_id
4027 );
4028 IF l_return_status = FND_API.g_ret_sts_error THEN
4029 RAISE FND_API.g_exc_error;
4030 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4031 RAISE FND_API.g_exc_unexpected_error;
4032 END IF;
4033
4034 --2. Update Deduction payment detail -- <Batch Process>: Fetcher program
4035 l_online_upd_ded_status := FALSE;
4036 ELSE
4037 /*------------------------------------------------------------*
4038 | Update Claim Status to CLOSED.
4039 *------------------------------------------------------------*/
4040 Close_Claim(
4041 p_claim_rec => p_claim_rec
4042 ,x_return_status => l_return_status
4043 ,x_msg_data => x_msg_data
4044 ,x_msg_count => x_msg_count
4045 );
4046 IF l_return_status = FND_API.g_ret_sts_error THEN
4047 RAISE FND_API.g_exc_error;
4048 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4049 RAISE FND_API.g_exc_unexpected_error;
4050 END IF;
4051
4052 --bug 4684931
4053 l_receipt_currency := p_claim_rec.currency_code;
4054 OPEN csr_trx_currency(p_payment_reference_id);
4055 FETCH csr_trx_currency INTO l_trx_currency;
4056 CLOSE csr_trx_currency;
4057
4058 IF p_deduction_type = 'CHARGE' THEN
4059 l_online_upd_ded_status := TRUE;
4060
4061 ELSIF p_deduction_type = 'RECEIPT_OPM' THEN
4062 /*------------------------------------------------------------*
4063 | Overpayment -> 1. Apply debit memo on receipt.
4064 | 2. Unapply claim investigation
4065 | <<Pay by Previous Open Debit Memo which already exists on the receipt>>:
4066 | Overpayment -> 1. Unapply claim investigation
4067 | 1.5. Unapply debit memo on receipt
4068 | 2. Apply debit memo on receipt.
4069 *------------------------------------------------------------*/
4070 OPEN csr_dm_exist_on_rec(p_claim_rec.receipt_id, p_payment_reference_id);
4071 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
4072 CLOSE csr_dm_exist_on_rec;
4073
4074 OZF_Utility_PVT.debug_message('Overpayment -> 1. Unapply claim investigation');
4075 -- 1. Unapply claim investigation
4076 OPEN csr_claim_investigation_amount(p_claim_rec.root_claim_id);
4077 FETCH csr_claim_investigation_amount INTO l_orig_dispute_amount;
4078 CLOSE csr_claim_investigation_amount;
4079
4080 OZF_Utility_PVT.debug_message('original overpayment amount = '||l_orig_dispute_amount);
4081 OZF_Utility_PVT.debug_message('reapply overpayment amount = '||(l_orig_dispute_amount + p_debit_memo_amount));
4082
4083 Unapply_Claim_Investigation(
4084 p_claim_rec => p_claim_rec
4085 ,p_reapply_amount => (l_orig_dispute_amount + p_debit_memo_amount)
4086 ,x_return_status => l_return_status
4087 ,x_msg_data => x_msg_data
4088 ,x_msg_count => x_msg_count
4089 );
4090 IF l_return_status = FND_API.g_ret_sts_error THEN
4091 RAISE FND_API.g_exc_error;
4092 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4093 RAISE FND_API.g_exc_unexpected_error;
4094 END IF;
4095
4096 l_stlmnt_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4097 IF l_dm_applied_on_rec_amt IS NULL THEN
4098 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo on receipt');
4099 --bug4684931
4100 IF l_receipt_currency = l_trx_currency THEN
4101 l_p_new_applied_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4102 l_p_new_applied_from_amount := NULL;
4103 ELSE
4104 l_p_new_applied_amount := NULL;
4105 l_p_new_applied_from_amount := NVL(l_dm_applied_on_rec_amt_from,0) + (p_debit_memo_amount * -1);
4106 END IF;
4107
4108 -- 2. Apply debit memo on receipt
4109 Apply_on_Receipt(
4110 p_cash_receipt_id => p_claim_rec.receipt_id
4111 ,p_customer_trx_id => p_payment_reference_id
4112 ,p_new_applied_amount => l_p_new_applied_amount --bug4684931
4113 ,p_new_applied_from_amount => l_p_new_applied_from_amount --bug4684931
4114 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
4115 ,p_customer_reference => p_claim_rec.customer_ref_number --11.5.10 enhancements. TM should pass.
4116 ,x_return_status => l_return_status
4117 ,x_msg_data => x_msg_data
4118 ,x_msg_count => x_msg_count
4119 );
4120
4121 IF l_return_status = FND_API.g_ret_sts_error THEN
4122 RAISE FND_API.g_exc_error;
4123 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4124 RAISE FND_API.g_exc_unexpected_error;
4125 END IF;
4126 ELSE
4127 OZF_Utility_PVT.debug_message('Overpayment: Pay by Previous Open Debit Memo which already exists on the receipt');
4128 OZF_Utility_PVT.debug_message('Overpayment -> 1.5. Unapply debit memo on receipt');
4129 -- 1.5. Unapply creit memo on receipt
4130 Unapply_from_Receipt(
4131 p_cash_receipt_id => p_claim_rec.receipt_id
4132 ,p_customer_trx_id => p_payment_reference_id
4133 ,x_return_status => l_return_status
4134 ,x_msg_data => x_msg_data
4135 ,x_msg_count => x_msg_count
4136 );
4137 IF l_return_status = FND_API.g_ret_sts_error THEN
4138 RAISE FND_API.g_exc_error;
4139 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4140 RAISE FND_API.g_exc_unexpected_error;
4141 END IF;
4142
4143 OZF_Utility_PVT.debug_message('Overpayment -> 2. Apply debit memo with increased amount on receipt');
4144 l_settlement_amount := (p_debit_memo_amount * -1); -- Bug4308188
4145
4146 --bug 4684931
4147 IF l_receipt_currency = l_trx_currency THEN
4148 l_p_new_applied_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4149 l_p_new_applied_from_amount := NULL;
4150 ELSE
4151 l_p_new_applied_amount := NULL;
4152 l_p_new_applied_from_amount := NVL(l_dm_applied_on_rec_amt,0) + (p_debit_memo_amount * -1);
4153 END IF;
4154
4155 -- 2. Apply creit memo on receipt
4156 Apply_on_Receipt(
4157 p_cash_receipt_id => p_claim_rec.receipt_id
4158 ,p_customer_trx_id => p_payment_reference_id
4159 ,p_new_applied_amount => l_p_new_applied_amount
4160 ,p_new_applied_from_amount => l_p_new_applied_from_amount
4161 ,p_comments => SUBSTRB(p_claim_rec.comments, 1, 240)
4162 ,p_apply_date => l_apply_date -- Fix for Bug 3091401. TM passes old apply date
4163 ,x_return_status => l_return_status
4164 ,x_msg_data => x_msg_data
4165 ,x_msg_count => x_msg_count
4166 );
4167 IF l_return_status = FND_API.g_ret_sts_error THEN
4168 RAISE FND_API.g_exc_error;
4169 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4170 RAISE FND_API.g_exc_unexpected_error;
4171 END IF;
4172 END IF;
4173
4174
4175 l_online_upd_ded_status := TRUE;
4176 END IF;
4177 END IF;
4178 ELSE
4179 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4180 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
4181 FND_MSG_PUB.add;
4182 END IF;
4183 RAISE FND_API.g_exc_error;
4184 END IF;
4185
4186
4187 /*------------------------------------------------------------*
4188 | Update Deduction payment detail
4189 *------------------------------------------------------------*/
4190 IF l_online_upd_ded_status THEN
4191 -- Update Deduction payment detail
4192 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
4193 p_api_version => l_api_version
4194 ,p_init_msg_list => FND_API.g_false
4195 ,p_commit => FND_API.g_false
4196 ,p_validation_level => FND_API.g_valid_level_full
4197 ,x_return_status => l_return_status
4198 ,x_msg_data => x_msg_data
4199 ,x_msg_count => x_msg_count
4200 ,p_claim_id => p_claim_rec.claim_id
4201 ,p_payment_method => p_claim_rec.payment_method
4202 ,p_deduction_type => p_deduction_type
4203 ,p_cash_receipt_id => p_claim_rec.receipt_id
4204 ,p_customer_trx_id => p_payment_reference_id
4205 ,p_adjust_id => NULL
4206 ,p_settlement_amount => l_settlement_amount -- Bug4308188
4207 );
4208 IF l_return_status = FND_API.g_ret_sts_error THEN
4209 RAISE FND_API.g_exc_error;
4210 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4211 RAISE FND_API.g_exc_unexpected_error;
4212 END IF;
4213 END IF;
4214
4215 IF g_debug THEN
4216 OZF_Utility_PVT.debug_message(l_full_name||': end');
4217 END IF;
4218 EXCEPTION
4219 WHEN FND_API.g_exc_error THEN
4220 x_return_status := FND_API.g_ret_sts_error;
4221
4222 WHEN FND_API.g_exc_unexpected_error THEN
4223 x_return_status := FND_API.g_ret_sts_unexp_error ;
4224
4225 WHEN OTHERS THEN
4226 x_return_status := FND_API.g_ret_sts_unexp_error ;
4227 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4228 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4229 END IF;
4230
4231 END Pay_by_Debit_Memo;
4232
4233
4234 /*=======================================================================*
4235 | PROCEDURE
4236 | Pay_by_Contra_Charge
4237 |
4238 | NOTES
4239 |
4240 | HISTORY
4241 | 15-MAR-2002 mchang Create.
4242 *=======================================================================*/
4243 PROCEDURE Pay_by_Contra_Charge(
4244 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
4245 ,p_deduction_type IN VARCHAR2
4246
4247 ,x_return_status OUT NOCOPY VARCHAR2
4248 ,x_msg_data OUT NOCOPY VARCHAR2
4249 ,x_msg_count OUT NOCOPY NUMBER
4250 )
4251 IS
4252 l_api_version CONSTANT NUMBER := 1.0;
4253 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_Contra_Charge';
4254 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4255 l_return_status VARCHAR2(1);
4256
4257
4258 BEGIN
4259 -------------------- initialize -----------------------
4260 IF g_debug THEN
4261 OZF_Utility_PVT.debug_message(l_full_name||': start');
4262 END IF;
4263
4264 x_return_status := FND_API.g_ret_sts_success;
4265
4266 ------------------------ start -------------------------
4267 IF p_deduction_type IN ('SOURCE_DED', 'RECEIPT_DED') THEN
4268 -- invoke claim settlement workflow
4269 BEGIN
4270 OZF_AR_SETTLEMENT_PVT.Start_Settlement(
4271 p_claim_id => p_claim_rec.claim_id
4272 ,p_prev_status => 'APPROVED'
4273 ,p_curr_status => 'PENDING_CLOSE'
4274 ,p_next_status => 'CLOSED'
4275 );
4276 EXCEPTION
4277 WHEN OTHERS THEN
4278 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
4279 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
4280 FND_MSG_PUB.Add;
4281 RAISE FND_API.g_exc_unexpected_error;
4282 END;
4283 ELSE
4284 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4285 FND_MESSAGE.set_name('OZF', 'OZF_AR_PAYMENT_NOTMATCH');
4286 FND_MSG_PUB.add;
4287 END IF;
4288 END IF;
4289
4290 IF g_debug THEN
4291 OZF_Utility_PVT.debug_message(l_full_name||': end');
4292 END IF;
4293 EXCEPTION
4294 WHEN FND_API.g_exc_error THEN
4295 x_return_status := FND_API.g_ret_sts_error;
4296
4297 WHEN FND_API.g_exc_unexpected_error THEN
4298 x_return_status := FND_API.g_ret_sts_unexp_error ;
4299
4300 WHEN OTHERS THEN
4301 x_return_status := FND_API.g_ret_sts_unexp_error ;
4302 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4303 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4304 END IF;
4305
4306 END Pay_by_Contra_Charge;
4307
4308
4309 /*=======================================================================*
4310 | PROCEDURE
4311 | Create_AR_Payment
4312 |
4313 | NOTES
4314 |
4315 | HISTORY
4316 | 15-MAR-2002 mchang Create.
4317 *=======================================================================*/
4318 PROCEDURE Create_AR_Payment(
4319 p_api_version IN NUMBER
4320 ,p_init_msg_list IN VARCHAR2
4321 ,p_commit IN VARCHAR2
4322 ,p_validation_level IN NUMBER
4323
4324 ,x_return_status OUT NOCOPY VARCHAR2
4325 ,x_msg_data OUT NOCOPY VARCHAR2
4326 ,x_msg_count OUT NOCOPY NUMBER
4327
4328 ,p_claim_id IN NUMBER
4329 )
4330 IS
4331 l_api_version CONSTANT NUMBER := 1.0;
4332 l_api_name CONSTANT VARCHAR2(30) := 'Create_AR_Payment';
4333 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4334 l_return_status VARCHAR2(1);
4335
4336 l_claim_rec OZF_Claim_PVT.claim_rec_type;
4337 l_deduction_type VARCHAR2(30) := NULL;
4338
4339 BEGIN
4340 -------------------- initialize -----------------------
4341 SAVEPOINT Create_AR_Payment;
4342
4343 IF g_debug THEN
4344 OZF_Utility_PVT.debug_message(l_full_name||': start');
4345 END IF;
4346
4347 IF FND_API.to_boolean(p_init_msg_list) THEN
4348 FND_MSG_PUB.initialize;
4349 END IF;
4350
4351 IF NOT FND_API.compatible_api_call(
4352 l_api_version,
4353 p_api_version,
4354 l_api_name,
4355 g_pkg_name
4356 ) THEN
4357 RAISE FND_API.g_exc_unexpected_error;
4358 END IF;
4359
4360 x_return_status := FND_API.g_ret_sts_success;
4361
4362 ------------------------ start -------------------------
4363 Query_Claim(
4364 p_claim_id => p_claim_id
4365 ,x_claim_rec => l_claim_rec
4366 ,x_return_status => l_return_status
4367 );
4368 IF l_return_status = FND_API.g_ret_sts_error THEN
4369 RAISE FND_API.g_exc_error;
4370 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4371 RAISE FND_API.g_exc_unexpected_error;
4372 END IF;
4373
4374 IF g_debug THEN
4375 OZF_Utility_PVT.debug_message('Create Payment for ==> '||l_claim_rec.claim_number);
4376 END IF;
4377
4378 IF l_claim_rec.claim_class = 'DEDUCTION' THEN
4379 IF l_claim_rec.source_object_class IS NOT NULL AND
4380 l_claim_rec.source_object_id IS NOT NULL AND
4381 l_claim_rec.source_object_number IS NOT NULL THEN
4382 l_deduction_type := 'SOURCE_DED';
4383 ELSE
4384 l_deduction_type := 'RECEIPT_DED';
4385 END IF;
4386 ELSIF l_claim_rec.claim_class = 'OVERPAYMENT' THEN
4387 IF l_claim_rec.source_object_class IS NOT NULL AND
4388 l_claim_rec.source_object_id IS NOT NULL AND
4389 l_claim_rec.source_object_number IS NOT NULL THEN
4390 l_deduction_type := 'SOURCE_OPM';
4391 ELSE
4392 l_deduction_type := 'RECEIPT_OPM';
4393 END IF;
4394 ELSIF l_claim_rec.claim_class = 'CLAIM' THEN
4395 l_deduction_type := 'CLAIM';
4396 ELSIF l_claim_rec.claim_class = 'CHARGE' THEN
4397 l_deduction_type := 'CHARGE';
4398 ELSE
4399 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4400 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_DED_TYPE_ERR');
4401 FND_MSG_PUB.add;
4402 END IF;
4403 END IF;
4404
4405 --R12.1 Enhancement start :Close the claim contains the payment method as ACCOUNTING_ONLY
4406 -- Introduced the class CHARGE for RMA internal Ship and Debit claim
4407 IF l_deduction_type IN ('CLAIM','CHARGE') THEN
4408 IF l_claim_rec.payment_method = 'ACCOUNTING_ONLY' THEN
4409
4410 Close_Claim(
4411 p_claim_rec => l_claim_rec
4412 ,x_return_status => l_return_status
4413 ,x_msg_data => x_msg_data
4414 ,x_msg_count => x_msg_count
4415 );
4416
4417 OZF_Utility_PVT.debug_message('After calling the close_claim ==> '||l_return_status);
4418 OZF_Utility_PVT.debug_message('After calling the close_claim ==> '||l_claim_rec.claim_id);
4419 IF l_return_status = FND_API.g_ret_sts_error THEN
4420 RAISE FND_API.g_exc_error;
4421 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4422 RAISE FND_API.g_exc_unexpected_error;
4423 END IF;
4424 OZF_Utility_PVT.debug_message('Before calling the update_payment_detail ==> '||l_claim_rec.claim_id);
4425 -- To create the settlement doc.
4426 OZF_SETTLEMENT_DOC_PVT.Update_Payment_Detail(
4427 p_api_version => l_api_version
4428 ,p_init_msg_list => FND_API.g_false
4429 ,p_commit => FND_API.g_false
4430 ,p_validation_level => FND_API.g_valid_level_full
4431 ,x_return_status => l_return_status
4432 ,x_msg_data => x_msg_data
4433 ,x_msg_count => x_msg_count
4434 ,p_claim_id => l_claim_rec.claim_id
4435 ,p_payment_method => 'ACCOUNTING_ONLY'
4436 ,p_deduction_type => 'CLAIM'
4437 ,p_cash_receipt_id => NULL
4438 ,p_customer_trx_id => NULL --l_claim_rec.source_object_id
4439 ,p_adjust_id => NULL
4440 );
4441 OZF_Utility_PVT.debug_message('After calling update_payment_detail ==> '||l_return_status);
4442 OZF_Utility_PVT.debug_message('After calling update_payment_detail ==> '||l_claim_rec.claim_id);
4443 IF l_return_status = FND_API.g_ret_sts_error THEN
4444 RAISE FND_API.g_exc_error;
4445 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4446 RAISE FND_API.g_exc_unexpected_error;
4447 END IF;
4448 END IF;
4449 END IF;
4450 --R12.1 Enhancement end
4451
4452 IF l_deduction_type = 'SOURCE_OPM' THEN
4453 Process_Settlement_WF(
4454 p_claim_id => p_claim_id
4455 ,x_return_status => l_return_status
4456 ,x_msg_data => x_msg_data
4457 ,x_msg_count => x_msg_count
4458 );
4459 IF l_return_status = FND_API.g_ret_sts_error THEN
4460 RAISE FND_API.g_exc_error;
4461 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4462 RAISE FND_API.g_exc_unexpected_error;
4463 END IF;
4464 --R12.1 Enhancement : Check for ACCOUNTING_ONLY
4465 ELSIF l_claim_rec.payment_method <> 'ACCOUNTING_ONLY' THEN
4466 IF l_claim_rec.payment_method = 'REG_CREDIT_MEMO' THEN
4467 Pay_by_Invoice_Credit(
4468 p_claim_rec => l_claim_rec
4469 ,p_deduction_type => l_deduction_type
4470 ,x_return_status => l_return_status
4471 ,x_msg_data => x_msg_data
4472 ,x_msg_count => x_msg_count
4473 );
4474 IF l_return_status = FND_API.g_ret_sts_error THEN
4475 RAISE FND_API.g_exc_error;
4476 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4477 RAISE FND_API.g_exc_unexpected_error;
4478 END IF;
4479
4480 ELSIF l_claim_rec.payment_method in ( 'CREDIT_MEMO', 'PREV_OPEN_CREDIT') THEN
4481 Pay_by_Credit_Memo(
4482 p_claim_rec => l_claim_rec
4483 ,p_deduction_type => l_deduction_type
4484 ,p_payment_reference_id => l_claim_rec.payment_reference_id
4485 ,p_credit_memo_amount => l_claim_rec.amount_settled
4486 ,x_return_status => l_return_status
4487 ,x_msg_data => x_msg_data
4488 ,x_msg_count => x_msg_count
4489 );
4490 IF l_return_status = FND_API.g_ret_sts_error THEN
4491 RAISE FND_API.g_exc_error;
4492 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4493 RAISE FND_API.g_exc_unexpected_error;
4494 END IF;
4495
4496 ELSIF l_claim_rec.payment_method = 'ON_ACCT_CREDIT' THEN
4497 Pay_by_On_Account_Credit(
4498 p_claim_rec => l_claim_rec
4499 ,p_deduction_type => l_deduction_type
4500 ,x_return_status => l_return_status
4501 ,x_msg_data => x_msg_data
4502 ,x_msg_count => x_msg_count
4503 );
4504 IF l_return_status = FND_API.g_ret_sts_error THEN
4505 RAISE FND_API.g_exc_error;
4506 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4507 RAISE FND_API.g_exc_unexpected_error;
4508 END IF;
4509
4510 ELSIF l_claim_rec.payment_method = 'CHARGEBACK' THEN
4511 Pay_by_Chargeback(
4512 p_claim_rec => l_claim_rec
4513 ,p_deduction_type => l_deduction_type
4514 ,x_return_status => l_return_status
4515 ,x_msg_data => x_msg_data
4516 ,x_msg_count => x_msg_count
4517 );
4518 IF l_return_status = FND_API.g_ret_sts_error THEN
4519 RAISE FND_API.g_exc_error;
4520 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4521 RAISE FND_API.g_exc_unexpected_error;
4522 END IF;
4523
4524 ELSIF (l_claim_rec.payment_method = 'WRITE_OFF')
4525 THEN
4526 Pay_by_Write_Off(
4527 p_claim_rec => l_claim_rec
4528 ,p_deduction_type => l_deduction_type
4529 ,x_return_status => l_return_status
4530 ,x_msg_data => x_msg_data
4531 ,x_msg_count => x_msg_count
4532 );
4533 IF l_return_status = FND_API.g_ret_sts_error THEN
4534 RAISE FND_API.g_exc_error;
4535 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4536 RAISE FND_API.g_exc_unexpected_error;
4537 END IF;
4538
4539 ELSIF l_claim_rec.payment_method IN ( 'DEBIT_MEMO', 'PREV_OPEN_DEBIT') THEN
4540 Pay_by_Debit_Memo(
4541 p_claim_rec => l_claim_rec
4542 ,p_deduction_type => l_deduction_type
4543 ,p_payment_reference_id => l_claim_rec.payment_reference_id
4544 ,p_debit_memo_amount => l_claim_rec.amount_settled
4545 ,x_return_status => l_return_status
4546 ,x_msg_data => x_msg_data
4547 ,x_msg_count => x_msg_count
4548 );
4549 IF l_return_status = FND_API.g_ret_sts_error THEN
4550 RAISE FND_API.g_exc_error;
4551 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4552 RAISE FND_API.g_exc_unexpected_error;
4553 END IF;
4554
4555 ELSIF l_claim_rec.payment_method = 'CONTRA_CHARGE' THEN
4556 Pay_by_Contra_Charge(
4557 p_claim_rec => l_claim_rec
4558 ,p_deduction_type => l_deduction_type
4559 ,x_return_status => l_return_status
4560 ,x_msg_data => x_msg_data
4561 ,x_msg_count => x_msg_count
4562 );
4563 IF l_return_status = FND_API.g_ret_sts_error THEN
4564 RAISE FND_API.g_exc_error;
4565 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4566 RAISE FND_API.g_exc_unexpected_error;
4567 END IF;
4568
4569 ELSE
4570 OZF_Utility_PVT.debug_message('KP1 Test: end');
4571 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4572 FND_MESSAGE.set_name('OZF', 'OZF_DED_PAYMETHOD_ERR');
4573 FND_MESSAGE.set_token('PAYMENT_METHOD', l_claim_rec.payment_method);
4574 FND_MSG_PUB.add;
4575 END IF;
4576 RAISE FND_API.g_exc_error;
4577 END IF;
4578
4579 END IF;
4580
4581 ------------------------ finish ------------------------
4582 FND_MSG_PUB.count_and_get(
4583 p_encoded => FND_API.g_false,
4584 p_count => x_msg_count,
4585 p_data => x_msg_data
4586 );
4587
4588 IF g_debug THEN
4589 OZF_Utility_PVT.debug_message(l_full_name ||': end');
4590 END IF;
4591
4592 EXCEPTION
4593 WHEN FND_API.g_exc_error THEN
4594 ROLLBACK TO Create_AR_Payment;
4595 x_return_status := FND_API.g_ret_sts_error;
4596 FND_MSG_PUB.count_and_get (
4597 p_encoded => FND_API.g_false
4598 ,p_count => x_msg_count
4599 ,p_data => x_msg_data
4600 );
4601
4602 WHEN FND_API.g_exc_unexpected_error THEN
4603 ROLLBACK TO Create_AR_Payment;
4604 x_return_status := FND_API.g_ret_sts_unexp_error ;
4605 FND_MSG_PUB.count_and_get (
4606 p_encoded => FND_API.g_false
4607 ,p_count => x_msg_count
4608 ,p_data => x_msg_data
4609 );
4610
4611 WHEN OTHERS THEN
4612 ROLLBACK TO Create_AR_Payment;
4613 x_return_status := FND_API.g_ret_sts_unexp_error ;
4614 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4615 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4616 END IF;
4617 FND_MSG_PUB.count_and_get(
4618 p_encoded => FND_API.g_false
4619 ,p_count => x_msg_count
4620 ,p_data => x_msg_data
4621 );
4622
4623 END Create_AR_Payment;
4624
4625 /*======================================================================*
4626 | PROCEDURE
4627 | Pay_by_RMA_Inv_CM
4628 |
4629 | NOTES
4630 |
4631 | HISTORY
4632 | 22-NOV-2004 Sahana Created for Bug3951827
4633 *=======================================================================*/
4634 PROCEDURE Pay_by_RMA_Inv_CM(
4635 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
4636 ,p_credit_memo_amount IN NUMBER
4637
4638 ,x_return_status OUT NOCOPY VARCHAR2
4639 ,x_msg_data OUT NOCOPY VARCHAR2
4640 ,x_msg_count OUT NOCOPY NUMBER
4641 )
4642 IS
4643 l_api_version CONSTANT NUMBER := 1.0;
4644 l_api_name CONSTANT VARCHAR2(30) := 'Pay_by_RMA_Inv_CM';
4645 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4646 l_return_status VARCHAR2(1);
4647
4648 BEGIN
4649 -------------------- initialize -----------------------
4650 IF g_debug THEN
4651 OZF_Utility_PVT.debug_message(l_full_name||': start');
4652 END IF;
4653 x_return_status := FND_API.g_ret_sts_success;
4654
4655 /*------------------------------------------------------------*
4656 | Update Claim Status to CLOSED.
4657 *------------------------------------------------------------*/
4658
4659 Close_Claim(
4660 p_claim_rec => p_claim_rec
4661 ,x_return_status => l_return_status
4662 ,x_msg_data => x_msg_data
4663 ,x_msg_count => x_msg_count
4664 );
4665 IF l_return_status = FND_API.g_ret_sts_error THEN
4666 RAISE FND_API.g_exc_error;
4667 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4668 RAISE FND_API.g_exc_unexpected_error;
4669 END IF;
4670
4671
4672 -- Update amount in dispute
4673 Update_dispute_amount(
4674 p_claim_rec => p_claim_rec
4675 ,p_dispute_amount => (p_credit_memo_amount * -1)
4676 ,x_return_status => l_return_status
4677 ,x_msg_data => x_msg_data
4678 ,x_msg_count => x_msg_count
4679 );
4680 IF l_return_status = FND_API.g_ret_sts_error THEN
4681 RAISE FND_API.g_exc_error;
4682 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4683 RAISE FND_API.g_exc_unexpected_error;
4684 END IF;
4685
4686 IF g_debug THEN
4687 OZF_Utility_PVT.debug_message(l_full_name||': end');
4688 END IF;
4689
4690 EXCEPTION
4691 WHEN FND_API.g_exc_error THEN
4692 x_return_status := FND_API.g_ret_sts_error;
4693
4694 WHEN FND_API.g_exc_unexpected_error THEN
4695 x_return_status := FND_API.g_ret_sts_unexp_error ;
4696
4697 WHEN OTHERS THEN
4698 x_return_status := FND_API.g_ret_sts_unexp_error ;
4699 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4700 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4701 END IF;
4702
4703 END Pay_by_RMA_Inv_CM;
4704
4705 END OZF_AR_PAYMENT_PVT;