1 PACKAGE BODY OZF_AR_VALIDATION_PVT AS
2 /* $Header: ozfvarvb.pls 120.3.12020000.2 2012/07/30 11:08:59 amitamku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_AR_VALIDATION_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvarvb.pls';
6
7 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
9
10
11
12 /*=======================================================================*
13 | Function
14 | Check_to_Process_SETL_WF
15 |
16 | Return
17 | FND_API.g_true / FND_API.g_false
18 |
19 | NOTES
20 | When settling by invoice creditmemo, settlement should be done by
21 | receivable role in the following cases:
22 | 1. Different credit types are mixed.
23 | 2. Credit is not to source invoice.
24 |
25 |
26 | HISTORY
27 | 16-May-2005 Sahana Created for Bug4308173
28 |
29 *=======================================================================*/
30 FUNCTION Check_to_Process_SETL_WF(
31 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
32 ,x_return_status OUT NOCOPY VARCHAR2
33 ) RETURN BOOLEAN IS
34
35 CURSOR csr_claim_line_invoice(cv_claim_id IN NUMBER) IS
36 SELECT source_object_id
37 , source_object_line_id
38 , credit_to
39 , SUM(quantity) qty
40 , AVG(rate) rate
41 , SUM(NVL(claim_currency_amount,0)) amount
42 FROM ozf_claim_lines
43 WHERE claim_id = cv_claim_id
44 GROUP BY source_object_id, source_object_line_id, credit_to;
45 l_trx_lines csr_claim_line_invoice%ROWTYPE;
46
47 --//Bugfix :10241971
48 /*
49 CURSOR csr_invoice_apply_count(cv_receipt_id IN NUMBER, cv_invoice_id IN NUMBER) IS
50 SELECT COUNT(rec.cash_receipt_id)
51 FROM ar_receivable_applications rec
52 WHERE rec.applied_customer_trx_id = cv_invoice_id
53 AND rec.cash_receipt_id = cv_receipt_id
54 AND rec.display = 'Y';
55 l_apply_receipt_count NUMBER;
56 */
57
58 l_api_name CONSTANT VARCHAR2(30) := 'Check_to_Process_SETL_WF()';
59 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
60
61 l_process_setl_wf BOOLEAN := FALSE;
62 l_line_level_crediting BOOLEAN := FALSE;
63 l_type_crediting BOOLEAN := FALSE;
64 l_header_crediting BOOLEAN := FALSE;
65
66 l_prev_source_object_id NUMBER ;
67
68 BEGIN
69 x_return_status := FND_API.g_ret_sts_success;
70
71
72 IF OZF_DEBUG_LOW_ON THEN
73 OZF_Utility_PVT.debug_message(l_full_name || ': Start');
74 END IF;
75
76 OPEN csr_claim_line_invoice(p_claim_rec.claim_id);
77 LOOP
78 FETCH csr_claim_line_invoice INTO l_trx_lines;
79 EXIT WHEN csr_claim_line_invoice%NOTFOUND;
80
81 IF l_trx_lines.source_object_id <> l_prev_source_object_id THEN
82 l_process_setl_wf := FALSE;
83 l_line_level_crediting := FALSE;
84 l_type_crediting := FALSE;
85 l_header_crediting := FALSE;
86
87 IF OZF_DEBUG_LOW_ON THEN
88 OZF_Utility_PVT.debug_message('Credit to more then one invoice');
89 END IF;
90 EXIT ;
91 END IF;
92 l_prev_source_object_id := l_trx_lines.source_object_id ;
93
94 --//Bugfix :10241971
95 /*
96 IF p_claim_rec.claim_class = 'DEDUCTION' AND p_claim_rec.source_object_id IS NOT NULL THEN
97 IF l_trx_lines.source_object_id <> p_claim_rec.source_object_id THEN
98 l_process_setl_wf := TRUE;
99 IF OZF_DEBUG_LOW_ON THEN
100 OZF_Utility_PVT.debug_message('Credit to Invoice other then Source Invoice');
101 END IF;
102 EXIT ;
103 END IF;
104 ELSIF p_claim_rec.claim_class = 'DEDUCTION' AND p_claim_rec.source_object_id IS NULL THEN
105 OPEN csr_invoice_apply_count(p_claim_rec.receipt_id, l_trx_lines.source_object_id);
106 FETCH csr_invoice_apply_count INTO l_apply_receipt_count;
107 CLOSE csr_invoice_apply_count;
108
109 IF l_apply_receipt_count = 0 THEN
110 l_process_setl_wf := TRUE;
111 IF OZF_DEBUG_LOW_ON THEN
112 OZF_Utility_PVT.debug_message('Credit to Invoice not on the Source Receipt');
113 END IF;
114 EXIT;
115 END IF;
116 END IF;
117 */
118 IF l_trx_lines.credit_to IS NOT NULL THEN
119 l_type_crediting := TRUE;
120 OZF_Utility_PVT.debug_message(l_full_name || ': End1');
121
122 END IF;
123
124 IF l_trx_lines.credit_to IS NULL AND l_trx_lines.source_object_line_id IS NULL THEN
125 l_header_crediting := TRUE;
126 OZF_Utility_PVT.debug_message(l_full_name || ': End2');
127
128 END IF;
129
130 IF l_trx_lines.source_object_line_id IS NOT NULL THEN
131 l_line_level_crediting := TRUE;
132 OZF_Utility_PVT.debug_message(l_full_name || ': End3');
133
134 END IF;
135
136
137 -- Check evaluates if there is more then one line.
138 IF l_line_level_crediting AND l_header_crediting THEN
139 l_process_setl_wf := TRUE;
140 EXIT;
141 ELSIF l_line_level_crediting AND l_type_crediting THEN
142 l_process_setl_wf := TRUE;
143 EXIT;
144 ELSIF l_header_crediting AND l_type_crediting THEN
145 l_process_setl_wf := TRUE;
146 EXIT;
147 END IF;
148
149 END LOOP;
150 CLOSE csr_claim_line_invoice;
151
152 IF OZF_DEBUG_LOW_ON THEN
153 OZF_Utility_PVT.debug_message(l_full_name || ': End');
154 END IF;
155
156 RETURN l_process_setl_wf;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 x_return_status := FND_API.g_ret_sts_unexp_error ;
161 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
162 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
163 END IF;
164 END Check_to_Process_SETL_WF;
165
166
167 FUNCTION Check_Credit_To_Balance(
168 p_cash_receipt_id IN NUMBER
169 , p_customer_trx_id IN NUMBER
170 , p_line_type IN VARCHAR2
171 , p_claim_line_amount IN NUMBER
172 ) RETURN BOOLEAN
173 IS
174
175 CURSOR csr_transaction_balance(cv_customer_trx_id IN NUMBER) IS
176 SELECT payment_schedule_id
177 , trx_number
178 , amount_line_items_remaining
179 , tax_remaining
180 , freight_remaining
181 FROM ar_payment_schedules
182 WHERE customer_trx_id = cv_customer_trx_id;
183
184
185 l_payment_schedule_id NUMBER;
186 l_trx_number VARCHAR2(30);
187 l_line_remaining NUMBER;
188 l_tax_remaining NUMBER;
189 l_freight_remaining NUMBER;
190
191 BEGIN
192 OPEN csr_transaction_balance(p_customer_trx_id);
193 FETCH csr_transaction_balance INTO l_payment_schedule_id
194 , l_trx_number
195 , l_line_remaining
196 , l_tax_remaining
197 , l_freight_remaining;
198 CLOSE csr_transaction_balance;
199
200
201 IF p_line_type = 'LINE' AND
202 p_claim_line_amount > l_line_remaining THEN
203 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
204 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CR_TO_LINE_AMT_ERR');
205 FND_MESSAGE.set_token('TRX_NUMBER', l_trx_number);
206 FND_MSG_PUB.add;
207 END IF;
208 RETURN FALSE;
209 ELSIF p_line_type = 'TAX' AND
210 p_claim_line_amount > l_tax_remaining THEN
211 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
212 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CR_TO_TAX_AMT_ERR');
213 FND_MESSAGE.set_token('TRX_NUMBER', l_trx_number);
214 FND_MSG_PUB.add;
215 END IF;
216 RETURN FALSE;
217 ELSIF p_line_type = 'FREIGHT' AND
218 p_claim_line_amount > l_freight_remaining THEN
219 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
220 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CR_TO_FREIGHT_AMT_ERR');
221 FND_MESSAGE.set_token('TRX_NUMBER', l_trx_number);
222 FND_MSG_PUB.add;
223 END IF;
224 RETURN FALSE;
225 END IF;
226
227 RETURN TRUE;
228 END Check_Credit_To_Balance;
229
230
231 /*=======================================================================*
232 | PROCEDURE
233 | Validate_CreditTo_Information
234 |
235 | NOTES
236 | Credit Memo-Invoice settlement validation
237 | 1. When credit to specific types, check balance
238 | 2. When credit to line, check line balance
239 | 3. Check overall invoice balance
240 |
241 |
242 | HISTORY
243 | 03-May-2005 Sahana Created for Bug4308173
244 *=======================================================================*/
245
246 PROCEDURE Validate_CreditTo_Information(
247 p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
248 ,p_invoice_id IN NUMBER DEFAULT NULL
249 ,x_return_status OUT NOCOPY VARCHAR2
250 )
251 IS
252 l_api_version CONSTANT NUMBER := 1.0;
253 l_api_name CONSTANT VARCHAR2(30) := 'Validate_CreditTo_Information';
254 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
255
256
257 --// Bug 10241971
258 CURSOR csr_get_inv_info(cv_claim_id IN NUMBER, cv_invoice_id IN NUMBER) IS
259 SELECT source_object_id, SUM(NVL(claim_currency_amount,0))
260 FROM ozf_claim_lines
261 WHERE claim_id = cv_claim_id
262 AND ( cv_invoice_id IS NULL OR source_object_id = cv_invoice_id )
263 GROUP BY source_object_id;
264
265 /*
266 CURSOR csr_get_inv_info(cv_claim_id IN NUMBER, cv_invoice_id IN NUMBER) IS
267 SELECT source_object_id, SUM(NVL(claim_currency_amount,0))
268 FROM ozf_claim_lines
269 WHERE claim_id = cv_claim_id
270 AND ( cv_invoice_id IS NULL OR source_object_id = cv_invoice_id )
271 GROUP BY source_object_id;
272 */
273
274 CURSOR csr_get_creditto_info(cv_claim_id IN NUMBER, cv_invoice_id IN NUMBER) IS
275 SELECT source_object_id
276 , credit_to
277 , SUM(NVL(claim_currency_amount,0))
278 FROM ozf_claim_lines
279 WHERE claim_id = cv_claim_id
280 AND source_object_class IN ('INVOICE', 'DM', 'CB')
281 AND ( cv_invoice_id IS NULL OR source_object_id = cv_invoice_id )
282 GROUP BY source_object_id, credit_to;
283
284 CURSOR csr_get_lineid_info(cv_claim_id IN NUMBER, cv_invoice_id IN NUMBER) IS
285 SELECT source_object_id
286 , source_object_line_id
287 , SUM(NVL(claim_currency_amount,0))
288 FROM ozf_claim_lines
289 WHERE claim_id = cv_claim_id
290 AND ( cv_invoice_id IS NULL OR source_object_id = cv_invoice_id )
291 GROUP BY source_object_id,source_object_line_id;
292
293 CURSOR csr_trx_line_amount(cv_invoice_line_id IN NUMBER) IS
294 SELECT extended_amount
295 FROM ra_customer_trx_lines
296 WHERE customer_trx_line_id = cv_invoice_line_id;
297
298 CURSOR csr_trx_details(cv_customer_trx_id IN NUMBER) IS
299 SELECT trx_number from ar_payment_schedules_all
300 WHERE customer_trx_id = cv_customer_trx_id;
301
302 l_credit_to_type VARCHAR2(15);
303 l_object_class VARCHAR2(15);
304 l_sum_line_amt NUMBER;
305 l_invoice_id NUMBER;
306 l_trx_line_amt NUMBER;
307 l_invoice_line_id NUMBER;
308 l_error BOOLEAN;
309 l_process_setl_wf BOOLEAN;
310 l_trx_number AR_PAYMENT_SCHEDULES_ALL.TRX_NUMBER%TYPE;
311
312 BEGIN
313 IF OZF_DEBUG_HIGH_ON THEN
314 OZF_Utility_PVT.debug_message(l_full_name||' : start');
315 END IF;
316
317 -- Initialize API return status to sucess
318 x_return_status := FND_API.g_ret_sts_success;
319
320
321 -- Proceed with validation only if settlement is not by receivable role
322
323 -- This check is skipped for non invoice deductions since
324 -- the check is performed before this procedure is called.
325 IF p_claim_rec.claim_class = 'CLAIM' OR
326 ( p_claim_rec.claim_class = 'DEDUCTION' AND p_claim_rec.source_object_id IS NOT NULL ) THEN
327 l_process_setl_wf := Check_to_Process_SETL_WF(
328 p_claim_rec => p_claim_rec
329 ,x_return_status => x_return_status
330 );
331 IF x_return_status = FND_API.g_ret_sts_error THEN
332 RAISE FND_API.g_exc_error;
333 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
334 RAISE FND_API.g_exc_unexpected_error;
335 END IF;
336 IF l_process_setl_wf THEN
337 RETURN;
338 END IF;
339 END IF;
340
341
342 OPEN csr_get_inv_info(p_claim_rec.claim_id, p_invoice_id);
343 LOOP
344 FETCH csr_get_inv_info INTO l_invoice_id, l_sum_line_amt;
345 EXIT WHEN csr_get_inv_info%NOTFOUND;
346
347 OZF_CLAIM_SETTLEMENT_PVT.Check_Transaction_Balance(
348 p_customer_trx_id => l_invoice_id
349 ,p_claim_amount => l_sum_line_amt
350 ,p_claim_number => p_claim_rec.claim_number
351 ,x_return_status => x_return_status );
352 IF x_return_status <> FND_API.g_ret_sts_success THEN
353 RETURN;
354 END IF;
355 END LOOP;
356 CLOSE csr_get_inv_info;
357
358
359 OPEN csr_get_creditto_info(p_claim_rec.claim_id, p_invoice_id);
360 LOOP
361 FETCH csr_get_creditto_info INTO l_invoice_id, l_credit_to_type,l_sum_line_amt;
362 EXIT WHEN csr_get_creditto_info%NOTFOUND;
363
364 IF l_credit_to_type IS NOT NULL THEN
365 l_error := Check_Credit_To_Balance(
366 p_cash_receipt_id => p_claim_rec.receipt_id
367 , p_customer_trx_id => l_invoice_id
368 , p_line_type => l_credit_to_type
369 , p_claim_line_amount => l_sum_line_amt);
370 IF NOT l_error THEN
371 x_return_status := FND_API.g_ret_sts_error;
372 RETURN;
373 END IF;
374 END IF;
375 END LOOP;
376 CLOSE csr_get_creditto_info;
377
378 OPEN csr_get_lineid_info(p_claim_rec.claim_id, p_invoice_id);
379 LOOP
380 FETCH csr_get_lineid_info INTO l_invoice_id, l_invoice_line_id,l_sum_line_amt;
381 EXIT WHEN csr_get_lineid_info%NOTFOUND;
382
383 OPEN csr_trx_line_amount(l_invoice_line_id);
384 FETCH csr_trx_line_amount INTO l_trx_line_amt;
385 CLOSE csr_trx_line_amount;
386 IF ABS(l_trx_line_amt) < l_sum_line_amt THEN
387 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
388 OPEN csr_trx_details(l_invoice_id);
389 FETCH csr_trx_details INTO l_trx_number;
390 CLOSE csr_trx_details;
391 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_TRX_BAL_ERR');
392 FND_MESSAGE.set_token('TRX_NUMBER',l_trx_number);
393 FND_MSG_PUB.add;
394 END IF;
395 x_return_status := FND_API.g_ret_sts_error;
396 RETURN;
397 END IF;
398 END LOOP;
399 CLOSE csr_get_lineid_info;
400
401 IF OZF_DEBUG_HIGH_ON THEN
402 OZF_Utility_PVT.debug_message(l_full_name||' : end');
403 END IF;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
408 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
409 END IF;
410 x_return_status := FND_API.g_ret_sts_unexp_error;
411 END Validate_CreditTo_Information;
412
413
414
415
416 /*=======================================================================*
417 | Procedure
418 | Complete_AR_Validation
419 |
420 | Return
421 |
422 | NOTES
423 |
424 | HISTORY
425 | 21-JUL-2002 mchang Create.
426 *=======================================================================*/
427 PROCEDURE Complete_AR_Validation(
428 p_api_version IN NUMBER
429 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
430 ,p_commit IN VARCHAR2 := FND_API.g_false
431 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
432
433 ,x_return_status OUT NOCOPY VARCHAR2
434 ,x_msg_data OUT NOCOPY VARCHAR2
435 ,x_msg_count OUT NOCOPY NUMBER
436
437 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
438 )
439 IS
440 l_api_version CONSTANT NUMBER := 1.0;
441 l_api_name CONSTANT VARCHAR2(30) := 'Complete_AR_Validation';
442 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
443
444 CURSOR csr_sum_line_amt(cv_claim_id IN NUMBER) IS
445 SELECT NVL(SUM(claim_currency_amount), 0)
446 FROM ozf_claim_lines
447 WHERE claim_id = cv_claim_id;
448
449 CURSOR csr_sysparam_defaults(cv_set_of_books_id IN NUMBER) IS
450 SELECT batch_source_id
451 , post_to_gl
452 , gl_rec_clearing_account
453 , cm_trx_type_id
454 , billback_trx_type_id
455 , cb_trx_type_id
456 , adj_rec_trx_id
457 , wo_rec_trx_id
458 , neg_wo_rec_trx_id
459 FROM ozf_sys_parameters
460 WHERE set_of_books_id = cv_set_of_books_id;
461
462 CURSOR csr_sysparam_trx(cv_set_of_books_id IN NUMBER) IS
463 SELECT billback_trx_type_id, cm_trx_type_id
464 FROM ozf_sys_parameters
465 WHERE set_of_books_id = cv_set_of_books_id;
466
467 CURSOR csr_chk_trx_type(cv_trx_type_id IN NUMBER) IS
468 SELECT type, creation_sign
469 FROM ra_cust_trx_types
470 WHERE cust_trx_type_id = cv_trx_type_id;
471
472 CURSOR csr_reason(cv_reason_code_id IN NUMBER) IS
473 SELECT reason_code
474 , adjustment_reason_code
475 , name
476 FROM ozf_reason_codes_vl
477 WHERE reason_code_id = cv_reason_code_id;
478
479 CURSOR csr_trx_type(cv_claim_type_id IN NUMBER) IS
480 SELECT cm_trx_type_id
481 , dm_trx_type_id
482 , cb_trx_type_id
483 , wo_rec_trx_id
484 , neg_wo_rec_trx_id
485 , adj_rec_trx_id
486 FROM ozf_claim_types_vl
487 WHERE claim_type_id = cv_claim_type_id;
488
489 CURSOR csr_trx_balance(cv_customer_trx_id IN NUMBER) IS
490 SELECT SUM(amount_due_remaining),
491 invoice_currency_code
492 FROM ar_payment_schedules
493 WHERE customer_trx_id = cv_customer_trx_id
494 GROUP BY invoice_currency_code;
495
496 CURSOR csr_chk_line_product(cv_claim_id IN NUMBER) IS
497 SELECT item_id
498 , quantity_uom
499 --, org_id
500 , FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID')
501 FROM ozf_claim_lines
502 WHERE item_type = 'PRODUCT'
503 AND claim_id = cv_claim_id;
504
505 CURSOR csr_validate_primary_uom( cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
506 SELECT primary_uom_code
507 FROM mtl_system_items
508 WHERE inventory_item_id = cv_item_id
509 AND organization_id = cv_org_id;
510
511 CURSOR csr_validate_uom_class(cv_uom_code IN VARCHAR2) IS
512 SELECT uom_class
513 FROM mtl_units_of_measure
514 WHERE uom_code = cv_uom_code;
515
516 CURSOR csr_ar_system_options IS
517 SELECT salesrep_required_flag
518 FROM ar_system_parameters;
519
520 CURSOR csr_get_inv_info(cv_claim_id IN NUMBER) IS
521 SELECT source_object_id, source_object_class, SUM(NVL(claim_currency_amount,0))
522 FROM ozf_claim_lines
523 WHERE claim_id = cv_claim_id
524 GROUP BY source_object_id, source_object_class;
525
526 l_object_class VARCHAR2(15);
527 l_invoice_id NUMBER;
528
529 l_sum_line_amt NUMBER;
530 l_sum_util_amt NUMBER;
531 l_line_acctd_amt NUMBER;
532 l_line_util_err VARCHAR2(1) := FND_API.g_false;
533 l_return_status VARCHAR2(1);
534 l_complete_flag VARCHAR2(1);
535 l_complete_yet VARCHAR2(1) := FND_API.g_true;
536 l_line_currency VARCHAR2(3);
537 l_line_amount NUMBER;
538 l_line_claim_curr_amt NUMBER;
539 l_claim_line_id NUMBER;
540 l_line_amt_err_flag VARCHAR2(1) := FND_API.g_true;
541 l_asso_earning_exist VARCHAR2(1) := FND_API.g_false;
542 l_asso_earning VARCHAR2(1);
543 l_vendor_in_sys NUMBER := NULL;
544 l_rec_clr_in_sys NUMBER := NULL;
545 l_trx_type_id NUMBER := NULL;
546 l_trx_type VARCHAR2(20);
547 l_creation_sign VARCHAR2(30);
548 l_gl_count NUMBER := 0;
549 l_gl_date_type VARCHAR2(30);
550 l_gl_acc_checking VARCHAR2(1);
551 l_batch_source_id NUMBER;
552 l_dummy NUMBER;
553 l_credit_memo_reason VARCHAR2(30);
554 l_adjust_reason VARCHAR2(30);
555 --l_claim_reason_name VARCHAR2(60);
556 l_claim_reason_name OZF_REASON_CODES_ALL_TL.name%TYPE; --AMITAMKU fixed bug 14378888
557 l_reason_type VARCHAR2(30);
558 l_cm_trx_type_id NUMBER;
559 l_dm_trx_type_id NUMBER;
560 l_cb_trx_type_id NUMBER;
561 l_wo_rec_trx_id NUMBER;
562 l_neg_wo_rec_trx_id NUMBER;
563 l_adj_rec_trx_id NUMBER;
564 l_sp_cm_trx_type_id NUMBER;
565 l_sp_dm_trx_type_id NUMBER;
566 l_sp_cb_trx_type_id NUMBER;
567 l_sp_adj_rec_trx_id NUMBER;
568 l_sp_wo_rec_trx_id NUMBER;
569 l_sp_neg_wo_rec_trx_id NUMBER;
570 l_error BOOLEAN := FALSE;
571 l_trx_balance NUMBER;
572 l_process_setl_wf VARCHAR2(1);
573 l_count NUMBER;
574 l_line_item_id NUMBER;
575 l_line_uom_code VARCHAR2(30);
576 l_line_quantity NUMBER;
577 l_line_rate NUMBER;
578 l_line_org_id NUMBER;
579 l_primary_uom_code VARCHAR2(3);
580 l_primary_uom_class VARCHAR2(10);
581 l_line_uom_class VARCHAR2(10);
582 l_prod_uom_code VARCHAR2(30);
583 l_prod_inv_flag VARCHAR2(1);
584 l_asso_offr_perf_id NUMBER;
585 l_offr_perf_flag_err BOOLEAN := FALSE;
586 l_promo_distinct_err BOOLEAN := FALSE;
587 l_earnings_asso_flag VARCHAR2(1);
588 l_payment_schedule_id NUMBER;
589 l_salesrep_req_flag VARCHAR2(1);
590 l_trx_currency VARCHAR2(15);
591
592 BEGIN
593 IF OZF_DEBUG_HIGH_ON THEN
594 OZF_Utility_PVT.debug_message(l_full_name||' : start');
595 END IF;
596
597 -- Initialize API return status to sucess
598 x_return_status := FND_API.g_ret_sts_success;
599
600 l_return_status := FND_API.g_ret_sts_success;
601
602 OPEN csr_sum_line_amt(p_claim_rec.claim_id);
603 FETCH csr_sum_line_amt INTO l_sum_line_amt;
604 CLOSE csr_sum_line_amt;
605
606 -- check if GL Accounting Flg is turning on in System Parameer.
607 OPEN csr_sysparam_defaults(p_claim_rec.set_of_books_id);
608 FETCH csr_sysparam_defaults INTO l_batch_source_id
609 , l_gl_acc_checking
610 , l_rec_clr_in_sys
611 , l_sp_cm_trx_type_id
612 , l_sp_dm_trx_type_id
613 , l_sp_cb_trx_type_id
614 , l_sp_adj_rec_trx_id
615 , l_sp_wo_rec_trx_id
616 , l_sp_neg_wo_rec_trx_id;
617 CLOSE csr_sysparam_defaults;
618
619
620 /*-----------------------------------------------------*
621 | CREDIT_MEMO / DEBIT_MEMO: Receivables Batch Source is required for Interface
622 *-----------------------------------------------------*/
623 IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO') AND
624 l_batch_source_id IS NULL THEN
625 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
626 FND_MESSAGE.set_name('OZF','OZF_BATCH_SRC_REQ_FOR_INTF');
627 FND_MSG_PUB.add;
628 END IF;
629 RAISE FND_API.G_EXC_ERROR;
630 END IF;
631
632
633 ------------------------------------------------------
634 -- Sales Credit
635 -- Bug 2851466 fixing: Sales Rep is required in Claims
636 -- if "Requires Salesperson" in AR system options.
637 ------------------------------------------------------
638 IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO')
639 OR p_claim_rec.payment_method = 'RMA' THEN
640 OPEN csr_ar_system_options;
641 FETCH csr_ar_system_options INTO l_salesrep_req_flag;
642 CLOSE csr_ar_system_options;
643
644 IF l_salesrep_req_flag = 'Y' AND
645 p_claim_rec.sales_rep_id IS NULL THEN
646 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
647 FND_MESSAGE.set_name('OZF', 'OZF_SETL_MISS_SALESREP');
648 FND_MSG_PUB.add;
649 END IF;
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652 END IF;
653
654 /*------------------------------------------------------
655 | Claim Type / Creation Sign / Reason Code
656 *-----------------------------------------------------*/
657 OPEN csr_trx_type(p_claim_rec.claim_type_id);
658 FETCH csr_trx_type INTO l_cm_trx_type_id
659 , l_dm_trx_type_id
660 , l_cb_trx_type_id
661 , l_wo_rec_trx_id
662 , l_neg_wo_rec_trx_id
663 , l_adj_rec_trx_id;
664 CLOSE csr_trx_type;
665
666 l_cm_trx_type_id := NVL(l_cm_trx_type_id ,l_sp_cm_trx_type_id);
667 l_dm_trx_type_id := NVL(l_dm_trx_type_id ,l_sp_dm_trx_type_id);
668 l_cb_trx_type_id := NVL(l_cb_trx_type_id ,l_sp_cb_trx_type_id);
669 l_wo_rec_trx_id := NVL(NVL(p_claim_rec.wo_rec_trx_id,l_wo_rec_trx_id),l_sp_wo_rec_trx_id);
670 l_neg_wo_rec_trx_id := NVL(NVL(p_claim_rec.wo_rec_trx_id,l_neg_wo_rec_trx_id) ,l_sp_neg_wo_rec_trx_id);
671 l_adj_rec_trx_id := NVL(NVL(p_claim_rec.wo_rec_trx_id,l_adj_rec_trx_id),l_sp_adj_rec_trx_id);
672
673
674 OPEN csr_reason(p_claim_rec.reason_code_id);
675 FETCH csr_reason INTO l_credit_memo_reason
676 , l_adjust_reason
677 , l_claim_reason_name;
678 CLOSE csr_reason;
679
680 -- creation sign should match the claim settlement amount sign
681 IF p_claim_rec.payment_method IN ( 'CREDIT_MEMO'
682 , 'DEBIT_MEMO'
683 , 'CHARGEBACK'
684 ) THEN
685 IF p_claim_rec.payment_method = 'CREDIT_MEMO' AND
686 l_cm_trx_type_id IS NOT NULL THEN
687 OPEN csr_chk_trx_type(l_cm_trx_type_id);
688 FETCH csr_chk_trx_type INTO l_trx_type, l_creation_sign;
689 CLOSE csr_chk_trx_type;
690 ELSIF p_claim_rec.payment_method = 'DEBIT_MEMO' AND
691 l_dm_trx_type_id IS NOT NULL THEN
692 OPEN csr_chk_trx_type(l_dm_trx_type_id);
693 FETCH csr_chk_trx_type INTO l_trx_type, l_creation_sign;
694 CLOSE csr_chk_trx_type;
695 ELSIF p_claim_rec.payment_method = 'CHARGEBACK' AND
696 l_cb_trx_type_id IS NOT NULL THEN
697 OPEN csr_chk_trx_type(l_cb_trx_type_id);
698 FETCH csr_chk_trx_type INTO l_trx_type, l_creation_sign;
699 CLOSE csr_chk_trx_type;
700 END IF;
701
702 IF p_claim_rec.amount > 0 AND
703 l_creation_sign = 'P' THEN
704 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
705 FND_MESSAGE.set_name('OZF', 'OZF_SETL_TRX_TYPE_CS_WRONG');
706 FND_MSG_PUB.add;
707 END IF;
708 l_error := TRUE;
709 ELSIF p_claim_rec.amount < 0 AND
710 l_creation_sign = 'N' THEN
711 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
712 FND_MESSAGE.set_name('OZF', 'OZF_SETL_TRX_TYPE_CS_WRONG');
713 FND_MSG_PUB.add;
714 END IF;
715 l_error := TRUE;
716 END IF;
717 END IF;
718
719 -- Receivable Transaction Type should match to payment method.
720 -- Credit Memo Reason is required for CREDIT_MEMO and RMA settlement
721 -- Adjustment Reason can't be ENDORSEMENT or EXCHANGE for CHARGEBACK settlement
722 ------------ REG_CREDIT_MEMO ----------------
723 IF p_claim_rec.payment_method = 'REG_CREDIT_MEMO' THEN
724 IF l_credit_memo_reason IS NULL THEN
725 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
726 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CM_REASON_ERR');
727 FND_MSG_PUB.add;
728 END IF;
729 l_error := TRUE;
730 END IF;
731
732 ------------ CREDIT_MEMO ----------------
733 ELSIF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
734 IF l_cm_trx_type_id IS NULL THEN
735 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
736 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CM_TRX_ID_REQ');
737 FND_MSG_PUB.add;
738 END IF;
739 l_error := TRUE;
740 END IF;
741 IF p_claim_rec.claim_class = 'DEDUCTION' AND
742 p_claim_rec.source_object_id IS NOT NULL AND
743 l_sum_line_amt < p_claim_rec.amount_remaining THEN
744 -- adj_rec_trx_id is required later in case of tax_impact transaction
745 IF l_adj_rec_trx_id IS NULL THEN
746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
747 FND_MESSAGE.set_name('OZF', 'OZF_SETL_ADJ_TRX_ID_REQ');
748 FND_MSG_PUB.add;
749 END IF;
750 l_error := TRUE;
751 END IF;
752 END IF;
753
754 ------------ DEBIT_MEMO ----------------
755 ELSIF p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
756 IF l_dm_trx_type_id IS NULL THEN
757 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
758 FND_MESSAGE.set_name('OZF', 'OZF_SETL_DM_TRX_ID_REQ');
759 FND_MSG_PUB.add;
760 END IF;
761 l_error := TRUE;
762 END IF;
763
764 ------------ CHARGEBACK ----------------
765 ELSIF p_claim_rec.payment_method = 'CHARGEBACK' THEN
766 IF l_adjust_reason IN ('ENDORSEMENT', 'EXCHANGE') THEN
767 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
768 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CB_REACODE_ERR');
769 FND_MSG_PUB.add;
770 END IF;
771 l_error := TRUE;
772 END IF;
773 IF l_cb_trx_type_id IS NULL THEN
774 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
775 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CB_TRX_ID_REQ');
776 FND_MSG_PUB.add;
777 END IF;
778 l_error := TRUE;
779 END IF;
780
781 ------------ WRITE_OFF ----------------
782 ELSIF p_claim_rec.payment_method = 'WRITE_OFF' THEN
783 IF p_claim_rec.claim_class = 'DEDUCTION' THEN
784 IF p_claim_rec.source_object_id IS NOT NULL AND
785 l_adj_rec_trx_id IS NULL THEN
786 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
787 FND_MESSAGE.set_name('OZF', 'OZF_SETL_ADJ_TRX_ID_REQ');
788 FND_MSG_PUB.add;
789 END IF;
790 l_error := TRUE;
791 -- 11.5.10 Negative Receipt Write Off
792 ELSIF p_claim_rec.source_object_id IS NULL AND
793 ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed() AND
794 l_neg_wo_rec_trx_id IS NULL THEN
795 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
796 FND_MESSAGE.set_name('OZF', 'OZF_SETL_NEG_WO_TRX_ID_REQ');
797 FND_MSG_PUB.add;
798 END IF;
799 l_error := TRUE;
800 END IF;
801 ELSIF p_claim_rec.claim_class = 'OVERPAYMENT' AND
802 l_wo_rec_trx_id IS NULL THEN
803 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
804 FND_MESSAGE.set_name('OZF', 'OZF_SETL_WO_TRX_ID_REQ');
805 FND_MSG_PUB.add;
806 END IF;
807 l_error := TRUE;
808 END IF;
809 ------------ RMA ----------------
810 ELSIF p_claim_rec.payment_method = 'RMA' THEN
811 IF l_credit_memo_reason IS NULL THEN
812 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
813 FND_MESSAGE.set_name('OZF', 'OZF_SETL_RMA_REASON_REQ');
814 FND_MESSAGE.set_token('REASON', l_claim_reason_name);
815 FND_MSG_PUB.add;
816 END IF;
817 l_error := TRUE;
818 END IF;
819 END IF;
820
821 IF l_error THEN
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824
825 /*-----------------------------------------------------
826 | Related Customer and Site checking
827 *-----------------------------------------------------*/
828 -- related customer and site check
829 IF p_claim_rec.pay_related_account_flag = FND_API.g_true THEN
830 -- related_cust_acct_id should exist if pay_related_customer_flag is 'T'
831 IF p_claim_rec.related_cust_account_id IS NULL THEN
832 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
833 FND_MESSAGE.set_name('OZF', 'OZF_SETL_RELCUST_REQ');
834 FND_MSG_PUB.add;
835 END IF;
836 RAISE FND_API.G_EXC_ERROR;
837 END IF;
838 -- related_site_use_id should exist if pay_related_customer_flag is 'T'
839 IF p_claim_rec.related_site_use_id IS NULL THEN
840 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
841 FND_MESSAGE.set_name('OZF', 'OZF_SETL_RELCUST_SITE_REQ');
842 FND_MSG_PUB.add;
843 END IF;
844 RAISE FND_API.G_EXC_ERROR;
845 END IF;
846 ELSE
847 /*-----------------------------------------------------
848 | Bill To Site is required
849 *-----------------------------------------------------*/
850 IF p_claim_rec.cust_billto_acct_site_id IS NULL THEN
851 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
852 FND_MESSAGE.set_name('OZF', 'OZF_SETL_BILLTO_SITE_REQ');
853 FND_MSG_PUB.add;
854 END IF;
855 RAISE FND_API.G_EXC_ERROR;
856 END IF;
857 END IF;
858 /*-----------------------------------------------------
859 | Ship To Site is required for RMA settlement
860 *-----------------------------------------------------*/
861 IF p_claim_rec.payment_method = 'RMA' AND
862 p_claim_rec.cust_shipto_acct_site_id IS NULL THEN
863 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
864 FND_MESSAGE.set_name('OZF', 'OZF_SETL_SHIPTO_SITE_REQ');
865 FND_MSG_PUB.add;
866 END IF;
867 RAISE FND_API.G_EXC_ERROR;
868 END IF;
869
870
871 /*-----------------------------------------------------
872 | Receivable Clearing Account
873 *-----------------------------------------------------*/
874 -- receivable clearning account must exist in system parameter
875 IF l_gl_acc_checking = FND_API.g_true AND
876 l_rec_clr_in_sys IS NULL AND
877 p_claim_rec.payment_method <> 'RMA' THEN
878 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
879 FND_MESSAGE.set_name('OZF', 'OZF_SETL_RECCLRACC_REQ');
880 FND_MSG_PUB.add;
881 END IF;
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884
885 /*-----------------------------------------------------
886 | Prev Open Credit Memo/Debit Memo: open balance amount checking
887 *-----------------------------------------------------*/
888 IF p_claim_rec.payment_method IN ('PREV_OPEN_CREDIT', 'PREV_OPEN_DEBIT') THEN
889 IF p_claim_rec.payment_reference_id IS NULL THEN
890 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
891 FND_MESSAGE.set_name('OZF','OZF_PAY_REFERENCE_REQD');
892 FND_MSG_PUB.add;
893 END IF;
894 RAISE FND_API.G_EXC_ERROR;
895 ELSE
896 OPEN csr_trx_balance(p_claim_rec.payment_reference_id);
897 FETCH csr_trx_balance INTO l_trx_balance, l_trx_currency;
898 CLOSE csr_trx_balance;
899
900 IF p_claim_rec.currency_code = l_trx_currency AND
901 ABS(p_claim_rec.amount_remaining) > ABS(l_trx_balance) THEN
902 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
903 FND_MESSAGE.set_name('OZF', 'OZF_SETL_CM_DM_OP_BAL_ERR');
904 FND_MSG_PUB.add;
905 END IF;
906 RAISE FND_API.G_EXC_ERROR;
907 END IF;
908 END IF;
909 END IF;
910
911
912 /*-----------------------------------------------------*
913 | Credit Memo-Invoice settlement validation
914 | Modified for 4308173
915 *-----------------------------------------------------*/
916 IF p_claim_rec.payment_method = 'REG_CREDIT_MEMO' THEN
917
918 -- Invoice information needs to exist on claim line.
919 OPEN csr_get_inv_info(p_claim_rec.claim_id);
920 LOOP
921 FETCH csr_get_inv_info INTO l_invoice_id, l_object_class,l_sum_line_amt;
922 EXIT WHEN csr_get_inv_info%NOTFOUND;
923 IF l_invoice_id IS NULL THEN
924 FND_MESSAGE.set_name('OZF', 'OZF_SETL_INV_CR_TRX_MISS');
925 FND_MSG_PUB.add;
926 RAISE FND_API.G_EXC_ERROR;
927 ELSIF l_object_class IS NULL OR l_object_class NOT IN ('INVOICE','DM', 'CB') THEN
928 FND_MESSAGE.set_name('OZF', 'OZF_SETL_INVALID_OBJ_CLASS');
929 FND_MSG_PUB.add;
930 RAISE FND_API.G_EXC_ERROR;
931 END IF;
932 END LOOP;
933 CLOSE csr_get_inv_info;
934
935 -- Validation for non invoice deductions is done during payment.
936 IF p_claim_rec.claim_class = 'CLAIM' OR
937 ( p_claim_rec.claim_class = 'DEDUCTION' AND p_claim_rec.source_object_id IS NOT NULL ) THEN
938 Validate_CreditTo_Information(
939 p_claim_rec => p_claim_rec
940 ,x_return_status => l_return_status
941 );
942 IF l_return_status = FND_API.g_ret_sts_error THEN
943 RAISE FND_API.g_exc_error;
944 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
945 RAISE FND_API.g_exc_unexpected_error;
946 END IF;
947 END IF;
948
949 END IF; -- REG_CREDIT_MEMO
950
951
952 /*-----------------------------------------------------
953 | Product Information Validation
954 | Check only for CREDIT_MEMO / DEBIT_MEMO / RMA settlement.
955 *-----------------------------------------------------*/
956 IF p_claim_rec.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO', 'RMA') THEN
957 OPEN csr_chk_line_product(p_claim_rec.claim_id);
958 LOOP
959 FETCH csr_chk_line_product INTO l_line_item_id
960 , l_line_uom_code
961 , l_line_org_id;
962 EXIT WHEN csr_chk_line_product%NOTFOUND;
963
964 IF l_line_item_id IS NOT NULL THEN
965 IF l_line_uom_code IS NOT NULL THEN
966 -- Primary UOM validation
967 OPEN csr_validate_primary_uom(l_line_item_id, l_line_org_id);
968 FETCH csr_validate_primary_uom INTO l_primary_uom_code;
969 CLOSE csr_validate_primary_uom;
970
971 -- UOM class sharing validation
972 IF l_primary_uom_code <> l_line_uom_code THEN
973 OPEN csr_validate_uom_class(l_primary_uom_code);
974 FETCH csr_validate_uom_class INTO l_primary_uom_class;
975 CLOSE csr_validate_uom_class;
976 OPEN csr_validate_uom_class(l_line_uom_code);
977 FETCH csr_validate_uom_class INTO l_line_uom_class;
978 CLOSE csr_validate_uom_class;
979 IF l_primary_uom_class <> l_line_uom_class THEN
980 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
981 FND_MESSAGE.set_name('OZF', 'OZF_SETL_PROD_UOM_INVALID');
982 FND_MSG_PUB.add;
983 END IF;
984 l_error := TRUE;
985 END IF;
986 END IF;
987 ELSE
988 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
989 FND_MESSAGE.set_name('OZF', 'OZF_SETL_PROD_UOM_MISSING');
990 FND_MSG_PUB.add;
991 END IF;
992 l_error := TRUE;
993 END IF;
994 END IF;
995 END LOOP;
996 CLOSE csr_chk_line_product;
997 END IF;
998
999 IF l_error THEN
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002
1003
1004 IF OZF_DEBUG_HIGH_ON THEN
1005 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1006 END IF;
1007
1008 EXCEPTION
1009 WHEN FND_API.G_EXC_ERROR THEN
1010 x_return_status := FND_API.G_RET_STS_ERROR;
1011 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1013 WHEN OTHERS THEN
1014 x_return_status := FND_API.g_ret_sts_unexp_error ;
1015 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1016 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1017 END IF;
1018 END Complete_AR_Validation;
1019
1020
1021 END OZF_AR_VALIDATION_PVT;
[Home] [Help]