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