[Home] [Help]
PACKAGE BODY: APPS.PSA_MFAR_VAL_PKG
Source
1 PACKAGE BODY PSA_MFAR_VAL_PKG AS
2 /* $Header: PSAMFVLB.pls 120.10 2006/09/13 14:09:08 agovil ship $ */
3
4 /* Procedure to Validate the Transaction Header */
5 --===========================FND_LOG.START=====================================
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAMFVLB.PSA_MFAR_VAL_PKG.';
13 --===========================FND_LOG.END=======================================
14
15 PROCEDURE AR_MFAR_VALIDATE_TRX_HEADER(
16 X_TRANSACTION_TYPE_ID Number,
17 X_TRANSACTION_CLASS varchar2,
18 X_TRX_COMMITMENT_NUMBER varchar2,
19 X_TRANSACTION_RULES_FLAG varchar2,
20 X_INVOICE_RULE_ID Number,
21 X_RECEIPT_METHOD_ID Number,
22 X_SET_OF_BOOKS_ID Number,
23 X_BASE_CURRENCY_CODE varchar2
24 ) IS
25
26
27 l_trx_type_validate varchar2(1);
28 l_base_currency varchar2(15);
29 l_accounting_method varchar2(30);
30 -- error handling variables
31 l_error_api_name varchar2(2000);
32 l_return_status varchar2(1);
33 l_msg_count number;
34 l_msg_data varchar2(2000);
35 l_msg_index_out number;
36 -- l_api_name varchar2(30) := 'PSP_PAYTRN';
37 l_subline_message varchar2(200);
38 l_payment_method varchar2(30);
39 l_trx_type_check varchar2(1) ;
40 -- ========================= FND LOG ===========================
41 l_full_path VARCHAR2(100) := g_path || 'AR_MFAR_VALIDATE_TRX_HEADER';
42 -- ========================= FND LOG ===========================
43 BEGIN
44 FND_MSG_PUB.Initialize;
45
46 if (arp_global.sysparam.accounting_method <> 'ACCRUAL' ) then
47 return;
48 end if;
49
50 SELECT 'X' into l_trx_type_check
51 from psa_trx_types_ALL a, ra_cust_trx_types_all b
52 where a.psa_trx_type_id = X_TRANSACTION_TYPE_ID
53 AND a.psa_trx_type_id = b.cust_trx_type_id ;
54
55 -- EXCEPTION
56 -- WHEN NO_DATA_FOUND THEN
57
58 -- FND_MESSAGE.set_name('AR','AR_NO_TRX_TYPE_IN_RA_INTERFACE_LINES');
59 -- APP_EXCEPTION.raise_exception;
60 -- RAISE;
61 -- END;
62
63 if l_trx_type_check is not null then
64
65 /* ------------------------------------------------------------------------------+
66 | If the transaction Class is of Gurantee,Credit Memo or Charge Back |
67 | then stop the Validation and Raise error Else continue further validation |
68 +--------------------------------------------------------------------------------*/
69
70 if
71 X_TRANSACTION_CLASS = 'GUAR' then
72 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CLASS_GUAR');
73 -- ========================= FND LOG ===========================
74 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
75 -- ========================= FND LOG ===========================
76 APP_EXCEPTION.RAISE_EXCEPTION;
77 fnd_msg_pub.add;
78 RAISE FND_API.G_EXC_ERROR;
79
80 elsif
81 X_TRANSACTION_CLASS = 'CM' then
82 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CLASS_CM');
83 -- ========================= FND LOG ===========================
84 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
85 -- ========================= FND LOG ===========================
86 APP_EXCEPTION.RAISE_EXCEPTION;
87 fnd_msg_pub.add;
88 RAISE FND_API.G_EXC_ERROR;
89
90 elsif
91 X_TRANSACTION_CLASS = 'CB' then
92 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CLASS_CB');
93 -- ========================= FND LOG ===========================
94 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
95 -- ========================= FND LOG ===========================
96 APP_EXCEPTION.RAISE_EXCEPTION;
97 fnd_msg_pub.add;
98 RAISE FND_API.G_EXC_ERROR;
99 end if;
100
101 /* ------------------------------------------------------------------------+
102 | If the Transaction Currency is not the same as the GL Functional |
103 | Currency exit validation with an error . Otherwise this transaction |
104 | will be qualified as MFAR transaction |
105 +-------------------------------------------------------------------------*/
106
107 select sob.currency_code
108 into l_base_currency
109 from GL_SETS_OF_BOOKS sob,AR_SYSTEM_PARAMETERS_ALL sp
110 where sob.set_of_books_id = X_SET_OF_BOOKS_ID
111 and sob.set_of_books_id = sp.set_of_books_id
112 and rownum < 2 ;
113
114 if (l_base_currency <> X_BASE_CURRENCY_CODE) then
115 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CURRENCY_CODE');
116 -- ========================= FND LOG ===========================
117 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
118 -- ========================= FND LOG ===========================
119 APP_EXCEPTION.RAISE_EXCEPTION;
120 fnd_msg_pub.add;
121 RAISE FND_API.G_EXC_ERROR;
122 end if;
123
124
125 /* ------------------------------------------------------------------------+
126 | If the Payment Method is of Automatic then exit validation |
127 | with an error . Otherwise this transaction will be qualified as MFAR |
128 | transaction |
129 +-------------------------------------------------------------------------*/
130 /*
131 if (X_RECEIPT_METHOD_ID is not null ) then
132 select a.creation_method_code into l_payment_method
133 from AR_RECEIPT_CLASSES a,AR_RECEIPT_METHODS b
134 where a.RECEIPT_CLASS_ID = b.RECEIPT_CLASS_ID
135 and b.receipt_method_id = X_RECEIPT_METHOD_ID;
136
137 if (l_payment_method = 'AUTOMATIC') then
138 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_PAYMENT_METHOD');
139 APP_EXCEPTION.RAISE_EXCEPTION;
140 fnd_msg_pub.add;
141 RAISE FND_API.G_EXC_ERROR;
142 end if;
143 end if;
144 */
145 /* ------------------------------------------------------------------------+
146 | If Invoice has Rules applied then exit the validation |
147 | Else continue further validation |
148 +-------------------------------------------------------------------------*/
149
150
151 if (X_INVOICE_RULE_ID is not null) then
152 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_TRX_RULES');
153 -- ========================= FND LOG ===========================
154 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
155 -- ========================= FND LOG ===========================
156 APP_EXCEPTION.RAISE_EXCEPTION;
157 fnd_msg_pub.add;
158 RAISE FND_API.G_EXC_ERROR;
159 end if;
160
161 /* ------------------------------------------------------------------------+
162 | If Invoice has commitments then exit the validation |
163 | Else continue further validation |
164 +-------------------------------------------------------------------------*/
165
166 if X_TRX_COMMITMENT_NUMBER is not null then
167 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_COMMITMENT_NO');
168 -- ========================= FND LOG ===========================
169 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
170 -- ========================= FND LOG ===========================
171 APP_EXCEPTION.RAISE_EXCEPTION;
172 fnd_msg_pub.add;
173 RAISE FND_API.G_EXC_ERROR;
174 end if;
175
176 return;
177 end if;
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND or TOO_MANY_ROWS then
181 return;
182 WHEN OTHERS then
183 -- ========================= FND LOG ===========================
184 psa_utils.debug_unexpected_msg(l_full_path);
185 -- ========================= FND LOG ===========================
186 RAISE;
187 end AR_MFAR_VALIDATE_TRX_HEADER;
188
189 /* Procedure to validate a Credit Memo */
190
191 PROCEDURE AR_MFAR_CM_VAL_CHECK(X_TRX_ID number,
192 X_SET_OF_BOOKS_ID Number,
193 X_BASE_CURRENCY_CODE varchar2
194 ) IS
195
196 l_trx_number_validate varchar2(1);
197 -- error handling variables
198 l_error_api_name varchar2(2000);
199 l_return_status varchar2(1);
200 l_msg_count number;
201 l_msg_data varchar2(2000);
202 l_msg_index_out number;
203 -- l_api_name varchar2(30) := 'PSP_PAYTRN';
204 l_subline_message varchar2(200);
205 l_profile_val varchar2(240);
206 l_base_currency varchar2(15);
207 l_accounting_method varchar2(30);
208 l_trx_type_check varchar2(1);
209 -- ========================= FND LOG ===========================
210 l_full_path VARCHAR2(100) := g_path || 'AR_MFAR_CM_VAL_CHECK';
211 -- ========================= FND LOG ===========================
212
213 BEGIN
214
215 FND_MSG_PUB.Initialize;
216
217 if (arp_global.sysparam.accounting_method <> 'ACCRUAL' )
218 then
219 return;
220 end if;
221
222 SELECT 'X' into l_trx_type_check
223 from psa_trx_types_ALL a, ra_customer_trx_all b
224 where a.psa_trx_type_id = b.cust_trx_type_id
225 and b.customer_trx_id = X_TRX_ID ;
226
227 if l_trx_type_check is not null then
228
229 -- open validate_trx_number_csr;
230 -- fetch validate_trx_number_csr into l_trx_number_validate ;
231 -- if validate_trx_number_csr%NOTFOUND then
232 -- FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_RECORD');
233 -- fnd_msg_pub.add;
234 -- close validate_trx_number_csr;
235 -- return;
236
237 select sob.currency_code
238 into l_base_currency
239 from GL_SETS_OF_BOOKS sob,AR_SYSTEM_PARAMETERS_ALL sp
240 where sob.set_of_books_id = X_SET_OF_BOOKS_ID
241 and sob.set_of_books_id = sp.set_of_books_id
242 and rownum < 2 ;
243
244 if (l_base_currency <> X_BASE_CURRENCY_CODE) then
245 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CURRENCY_CODE');
246 -- ========================= FND LOG ===========================
247 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
248 -- ========================= FND LOG ===========================
249 fnd_msg_pub.add;
250 RAISE FND_API.G_EXC_ERROR;
251 end if;
252
253 l_profile_val := fnd_profile.value('AR_USE_INV_ACCT_FOR_CM_FLAG');
254 -- dbms_output.put_line(l_value);
255
256 /* Bug Fix 1534215
257 -- modified by SIS
258 if (l_profile_val <> 'Y' ) then
259 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_PROFILE_VAL');
260 fnd_msg_pub.add;
261 RAISE FND_API.G_UNEXPECTED_EXC_ERROR
262 end if;
263 */
264
265 if (l_profile_val <> 'Y' ) then
266 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_PROFILE_VAL');
267 -- ========================= FND LOG ===========================
268 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
269 -- ========================= FND LOG ===========================
270 APP_EXCEPTION.RAISE_EXCEPTION;
271 fnd_msg_pub.add;
272 RAISE FND_API.G_EXC_ERROR;
273 end if;
274 -- else
275 return;
276 end if;
277
278 EXCEPTION
279 WHEN NO_DATA_FOUND or TOO_MANY_ROWS then
280 return;
281 WHEN OTHERS then
282 -- ========================= FND LOG ===========================
283 psa_utils.debug_unexpected_msg(l_full_path);
284 -- ========================= FND LOG ===========================
285 RAISE;
286 end AR_MFAR_CM_VAL_CHECK;
287
288 /* Procedure to validate a Transaction ,Receipt or an Adjustment */
289
290 FUNCTION AR_MFAR_VALIDATE_CHECK(
291 X_SOURCE_ID in Number,
292 X_SOURCE_TYPE in varchar2,
293 X_SET_OF_BOOKS_ID in Number)
294 RETURN varchar2 IS
295
296 l_trx_type varchar2(1);
297 l_accounting_method varchar2(30);
298 l_return_status varchar2(1);
299 l_validate_trx varchar2(1);
300 l_validate_adj varchar2(1);
301 l_app_cust_trx_id number;
302 -- ========================= FND LOG ===========================
303 l_full_path VARCHAR2(100) := g_path || 'AR_MFAR_VALIDATE_CHECK';
304 -- ========================= FND LOG ===========================
305 Begin
306
307 l_return_status := 'N' ;
308 FND_MSG_PUB.Initialize;
309
310 -- Commented for enabling cash basis accounting
311 --
312 -- if (arp_global.sysparam.accounting_method <> 'ACCRUAL' )
313 -- then
314 -- return(l_return_status);
315 -- end if;
316
317 if X_SOURCE_TYPE = 'TRX' then
318
319 SELECT 'X' into l_validate_trx
320 from psa_trx_types_ALL a, ra_customer_trx_all b
321 where a.psa_trx_type_id = b.cust_trx_type_id
322 and customer_trx_id = X_SOURCE_ID ;
323
324 if l_validate_trx is not null then
325 l_return_status := 'Y' ;
326 return(l_return_status);
327
328 else
329 return(l_return_status);
330 end if;
331
332 elsif X_SOURCE_TYPE = 'ADJ' then
333
334 SELECT 'X' into l_validate_adj
335 from psa_trx_types_ALL a, ra_customer_trx_all b,
336 ar_adjustments_all c
337 where b.customer_trx_id = c.customer_trx_id
338 and a.psa_trx_type_id = b.cust_trx_type_id
339 and c.adjustment_id = X_SOURCE_ID ;
340
341 if l_validate_adj is not null then
342 l_return_status := 'Y' ;
343 return(l_return_status);
344
345 else
346 return(l_return_status);
347 end if;
348
349 elsif X_SOURCE_TYPE = 'RCT' then
350 select applied_customer_trx_id
351 into l_app_cust_trx_id
352 from ar_receivable_applications_all
353 where receivable_application_id = X_SOURCE_ID;
354
355 return AR_MFAR_VALIDATE_CHECK( l_app_cust_trx_id,'TRX', X_SET_OF_BOOKS_ID );
356
357 end if;
358
359 EXCEPTION
360 WHEN NO_DATA_FOUND or TOO_MANY_ROWS then
361 l_return_status := 'N';
362 return(l_return_status);
363 WHEN OTHERS then
364 -- ========================= FND LOG ===========================
365 psa_utils.debug_unexpected_msg(l_full_path);
366 -- ========================= FND LOG ===========================
367 RAISE;
368
369 end AR_MFAR_VALIDATE_CHECK;
370
371 /* Bug 2435404 : Transaction number is being passed as a paremeter for this routine.
372 This will fail with 'too_many_rows_ exception is there is more than one transaction with same number.
373 PSA.pll will be modified to route ra_customer_trx_id instead of Transaction number */
374
375 /* Function to check for the validation of Receipt Header */
376 FUNCTION AR_MFAR_RECEIPT_CHECK(
377 X_RECEIPT_ID in Number,
378 X_RECEIPT_METHOD_ID in varchar2,
379 X_TRANSACTION_ID in number)
380 RETURN varchar2 IS
381
382 l_return_status varchar2(1);
383 l_trx_type varchar2(1);
384 l_confirm_flag varchar2(1);
385 l_remit_flag varchar2(1);
386 l_rct_check varchar2(1);
387 l_inv_currency_code varchar2(15);
388 l_rct_currency_code varchar2(15);
389 l_org_id number(15);
390 -- ========================= FND LOG ===========================
391 l_full_path VARCHAR2(100) := g_path || 'AR_MFAR_RECEIPT_CHECK';
392 -- ========================= FND LOG ===========================
393 begin
394
395 l_return_status := 'N';
396
397 FND_MSG_PUB.Initialize;
398 -- Get the org_id -- Bug 2374853
399
400 fnd_profile.get('ORG_ID',l_org_id);
401
402 SELECT 'X' into l_rct_check
403 from psa_trx_types_ALL a, ra_customer_trx_ALL b
404 -- ar_cash_receipts_ALL c,ar_receivable_applications_ALL d
405 -- where b.customer_trx_id = d.applied_customer_trx_id
406 -- and c.cash_receipt_id = d.cash_receipt_id
407 -- and
408 where a.psa_trx_type_id = b.cust_trx_type_id
409 and b.customer_trx_id = X_TRANSACTION_ID ;
410
411 if l_rct_check is not null then
412
413 -- Commented for enabling cash basis accounting
414 --
415 -- if (arp_global.sysparam.accounting_method <> 'ACCRUAL' )
416 -- then
417 -- FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_ACCOUNTING_METHOD');
418 -- fnd_msg_pub.add;
419 -- APP_EXCEPTION.RAISE_EXCEPTION;
420 -- RAISE FND_API.G_EXC_ERROR;
421 -- end if;
422
423 -- Modified this sql to include org_id
424 -- Bug 2374853
425
426 Select Invoice_currency_code into l_inv_currency_code
427 from ra_customer_trx_all
428 where customer_trx_id = X_TRANSACTION_ID and
429 org_id = l_org_id;
430
431
432 select currency_code into l_rct_currency_code
433 from ar_cash_receipts_all
434 where cash_receipt_id = X_RECEIPT_ID ;
435
436 if (l_inv_currency_code <> l_rct_currency_code ) then
437 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CURRENCY_CODE');
438 -- ========================= FND LOG ===========================
439 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
440 -- ========================= FND LOG ===========================
441 -- FND_MESSAGE.SHOW ;
442 APP_EXCEPTION.RAISE_EXCEPTION;
443 fnd_msg_pub.add;
444 RAISE FND_API.G_EXC_ERROR;
445 end if;
446
447 select a.REMIT_FLAG,a.confirm_flag into
448 l_remit_flag,l_confirm_flag
449 from AR_RECEIPT_CLASSES a,AR_RECEIPT_METHODS b
450 where a.RECEIPT_CLASS_ID = b.RECEIPT_CLASS_ID
451 and b.receipt_method_id = X_RECEIPT_METHOD_ID ;
452
453 if (l_remit_flag = 'Y') then
454 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_REMIT_CODE');
455 -- ========================= FND LOG ===========================
456 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
457 -- ========================= FND LOG ===========================
458 APP_EXCEPTION.RAISE_EXCEPTION;
459 fnd_msg_pub.add;
460 RAISE FND_API.G_EXC_ERROR;
461 elsif l_remit_flag = 'N' then
462 if l_confirm_flag = 'Y' then
463 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CONFIRM_CODE');
464 -- ========================= FND LOG ===========================
465 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
466 -- ========================= FND LOG ===========================
467 APP_EXCEPTION.RAISE_EXCEPTION;
468 fnd_msg_pub.add;
469 RAISE FND_API.G_EXC_ERROR;
470 elsif l_confirm_flag = 'N' then
471 l_return_status := 'Y' ;
472 return(l_return_status);
473 end if;
474
475 end if;
476 else
477 l_return_status := 'Y' ;
478 return(l_return_status);
479 end if;
480
481 EXCEPTION
482 WHEN NO_DATA_FOUND or TOO_MANY_ROWS then
483 l_return_status := 'N';
484 return(l_return_status);
485 WHEN OTHERS then
486 -- ========================= FND LOG ===========================
487 psa_utils.debug_unexpected_msg(l_full_path);
488 -- ========================= FND LOG ===========================
489 RAISE;
490
491 end AR_MFAR_RECEIPT_CHECK ;
492
493 /* Procedure to check for the validation of Quick Cash */
494 PROCEDURE ar_mfar_quickcash (x_receipt_method_id number) is
495 l_confirm_flag varchar2(1);
496 l_remit_flag varchar2(1);
497 -- ========================= FND LOG ===========================
498 l_full_path VARCHAR2(100) := g_path || 'ar_mfar_quickcash';
499 -- ========================= FND LOG ===========================
500 begin
501 FND_MSG_PUB.Initialize;
502 SELECT
503 a.REMIT_FLAG,
504 a.confirm_flag
505 INTO
506 l_remit_flag,
507 l_confirm_flag
508 FROM
509 AR_RECEIPT_CLASSES a,
510 AR_RECEIPT_METHODS b
511 WHERE
512 a.RECEIPT_CLASS_ID = b.RECEIPT_CLASS_ID AND
513 b.receipt_method_id = X_RECEIPT_METHOD_ID ;
514
515 if (l_remit_flag = 'Y') then
516 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_REMIT_CODE');
517 -- ========================= FND LOG ===========================
518 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
519 -- ========================= FND LOG ===========================
520 APP_EXCEPTION.RAISE_EXCEPTION;
521 fnd_msg_pub.add;
522 RAISE FND_API.G_EXC_ERROR;
523 elsif l_remit_flag = 'N' then
524 if l_confirm_flag = 'Y' then
525 FND_MESSAGE.SET_NAME('PSA','PSA_INVALID_CONFIRM_CODE');
526 -- ========================= FND LOG ===========================
527 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
528 -- ========================= FND LOG ===========================
529 APP_EXCEPTION.RAISE_EXCEPTION;
530 fnd_msg_pub.add;
531 RAISE FND_API.G_EXC_ERROR;
532 elsif l_confirm_flag = 'N' then
533 null; -- l_return_status := 'Y' ;
534 -- return(l_return_status);
535 end if;
536 end if;
537 EXCEPTION
538 when OTHERS then
539 -- ========================= FND LOG ===========================
540 psa_utils.debug_unexpected_msg(l_full_path);
541 -- ========================= FND LOG ===========================
542 RAISE;
543
544 END ar_mfar_quickcash;
545
546 /* Procedure to validate the Lockbox Functionality in Multifund */
547
548 FUNCTION AR_LOCKBOX_VALIDATION
549 RETURN varchar2 IS
550 l_return_status varchar2(1) ;
551 l_currency_check varchar2(1) ;
552 l_base_currency varchar2(15);
553 l_payment_method_check varchar2(1) ;
554 -- it_id item ;
555 l_set_of_books_id gl_sets_of_books.set_of_books_id%type;
556 -- ========================= FND LOG ===========================
557 l_full_path VARCHAR2(100) := g_path || 'AR_LOCKBOX_VALIDATION';
558 -- ========================= FND LOG ===========================
559
560 begin
561 l_return_status := 'N' ;
562 -- FND_PROFILE.GET('GL_SET_OF_BKS_ID', l_set_of_books_id);
563
564 -- Bug 1632998
565
566 l_set_of_books_id := PSA_MFAR_UTILS.get_ar_sob_id ;
567 -- it_id := FIND_ITEM('LBSUB.PB_SUBMIT') ;
568 begin
569 select sob.currency_code
570 into l_base_currency
571 from GL_SETS_OF_BOOKS sob,AR_SYSTEM_PARAMETERS_ALL sp
572 where sob.set_of_books_id = l_set_of_books_id
573 and sob.set_of_books_id = sp.set_of_books_id
574 and rownum < 2 ;
575
576 Select 'X'
577 into l_currency_check
578 from AR_INTERIM_CASH_RECEIPTS_ALL a
579 WHERE a.currency_code <> l_base_currency
580 and rownum < 2 ;
581
582 IF l_currency_check is not null THEN
583 l_return_status := 'Y' ;
584 return(l_return_status);
585 END IF;
586
587 EXCEPTION
588 WHEN NO_DATA_FOUND or TOO_MANY_ROWS then
589 -- return(l_return_status);
590 null;
591 WHEN OTHERS then
592 -- ========================= FND LOG ===========================
593 psa_utils.debug_unexpected_msg(l_full_path);
594 -- ========================= FND LOG ===========================
595 RAISE;
596 END ;
597
598 BEGIN
599 select 'X'
600 into l_payment_method_check
601 from AR_RECEIPT_CLASSES a,AR_RECEIPT_METHODS b,
602 AR_INTERIM_CASH_RECEIPTS_ALL c
603 where a.RECEIPT_CLASS_ID = b.RECEIPT_CLASS_ID
604 and b.receipt_method_id = c.RECEIPT_METHOD_ID
605 and a.remit_flag = 'Y'
606 and rownum < 2 ;
607
608 if l_payment_method_check is not null then
609 l_return_status := 'X' ;
610 return(l_return_status);
611 end if;
612 EXCEPTION
613 WHEN NO_DATA_FOUND or TOO_MANY_ROWS then
614 return(l_return_status);
615 WHEN OTHERS then
616 -- ========================= FND LOG ===========================
617 psa_utils.debug_unexpected_msg(l_full_path);
618 -- ========================= FND LOG ===========================
619 RAISE;
620 return(l_return_status);
621 END ;
622 end AR_LOCKBOX_VALIDATION ;
623
624 /* Procedure to check the Auto Invoice Validation */
625
626 PROCEDURE ar_mfar_autoinv_trx_header(l_request_id IN NUMBER) is
627
628 begin
629 /* ------------------------------------------------------------------------+
630 | If l_request_id is not null then check for the following |
631 | check for the transaction type of the Interface_line |
632 | If the transaction type is of non MFAR or null exit the program |
633 | If the transaction type is of MFAR then continue further validation |
634 +-------------------------------------------------------------------------*/
635
636
637 /* ------------------------------------------------------------------------+
638 | If the transaction type is of MFAR then continue further validation |
639 +-------------------------------------------------------------------------*/
640
641 /* ------------------------------------------------------------------------+
642 | If the accounting method is of CASH then exit validation |
643 | If the accounting method is not the CASH then continue |
644 +-------------------------------------------------------------------------*/
645
646 /* ------------------------------------------------------------------------------+
647 | If the transaction Class is of Gurantee,Credit Memo or Charge Back |
648 | then Insert the error into Errors Table. Else continue further validation |
649 +--------------------------------------------------------------------------------*/
650 if (arp_global.sysparam.accounting_method <> 'ACCRUAL' )
651 then
652 return;
653 end if;
654
655 -- Added Hint to improve performance Bug # 2503680
656
657 INSERT INTO RA_INTERFACE_ERRORS
658 (INTERFACE_LINE_ID,
659 MESSAGE_TEXT)
660 SELECT /*+ ORDERED */ nvl(L.INTERFACE_LINE_ID,0 ) ,
661 DECODE(B.TYPE ,'GUAR','Commitments Can not be Multi Fund Transactions Type',
662 'CB' ,'Multi Fund TRansactions Can not be Charged Back' ,
663 'CM' , 'On-Account Credit Can not use Multi Fund Transaction types')
664 FROM ra_interface_lines_gt L,
665 ra_cust_trx_types_all B,
666 psa_trx_types_all C
667 WHERE L.REQUEST_ID = l_request_id
668 AND L.cust_trx_type_id = B.cust_trx_type_id
669 AND B.TYPE in ('GUAR', 'CB', 'CM')
670 AND B.cust_trx_type_id = C.psa_trx_type_id;
671
672
673
674 /* ------------------------------------------------------------------------+
675 | If Invoice has Rules applied then exit the validation |
676 | Else continue further validation |
677 +-------------------------------------------------------------------------*/
678
679 INSERT INTO RA_INTERFACE_ERRORS
680 (INTERFACE_LINE_ID,
681 MESSAGE_TEXT)
682 SELECT L.INTERFACE_LINE_ID,
683 'Can not Assign Rules to Multi Fund Transactions'
684 FROM RA_INTERFACE_LINES_GT L
685 WHERE L.REQUEST_ID = l_request_id
686 AND ( L.INVOICING_RULE_ID IS NOT NULL
687 OR L.INVOICING_RULE_NAME IS NOT NULL )
688 AND exists
689 (SELECT 'X' FROM RA_CUST_TRX_TYPES_ALL B,
690 PSA_TRX_TYPES_ALL C
691 WHERE B.CUST_TRX_TYPE_ID = C.PSA_TRX_TYPE_ID
692 AND B.CUST_TRX_TYPE_ID = L.CUST_TRX_TYPE_ID ) ;
693
694 /* ------------------------------------------------------------------------+
695 | If the Transaction Currency is not the same as the GL Functional |
696 | Currency exit validation with an error . Otherwise this transaction |
697 | will be qualified as MFAR transaction |
698 +-------------------------------------------------------------------------*/
699
700 INSERT INTO RA_INTERFACE_ERRORS
701 (INTERFACE_LINE_ID,
702 MESSAGE_TEXT)
703 SELECT nvl(L.INTERFACE_LINE_ID,0) ,
704 'Transaction Currency Should be Equal to the GL Functional Currency'
705 FROM RA_INTERFACE_LINES_GT L
706 WHERE L.REQUEST_ID = l_request_id
707 AND exists
708 (SELECT 'X' FROM RA_CUST_TRX_TYPES_ALL B,
709 PSA_TRX_TYPES_ALL C
710 WHERE B.CUST_TRX_TYPE_ID = C.PSA_TRX_TYPE_ID
711 AND B.CUST_TRX_TYPE_ID = L.CUST_TRX_TYPE_ID )
712 AND not exists
713 (select 'X'
714 from GL_SETS_OF_BOOKS sob,AR_SYSTEM_PARAMETERS_ALL sp
715 where sob.set_of_books_id = L.SET_OF_BOOKS_ID
716 and sob.set_of_books_id = sp.set_of_books_id
717 and sob.currency_code = L.currency_code
718 and rownum < 2 );
719
720
721 /* ------------------------------------------------------------------------+
722 | If the Payment Method is of Automatic then exit validation |
723 | with an error . Otherwise this transaction will be qualified as MFAR |
724 | transaction |
725 +-------------------------------------------------------------------------*/
726 /*
727 INSERT INTO RA_INTERFACE_ERRORS
728 (INTERFACE_LINE_ID,
729 MESSAGE_TEXT )
730 SELECT nvl(L.INTERFACE_LINE_ID,0) ,
731 'Can not Mark Multi Fund Transactions for Automatic Receipts '
732 FROM RA_INTERFACE_LINES_GT L
733 WHERE L.REQUEST_ID = l_request_id
734 AND exists
735 (SELECT 'X' FROM RA_CUST_TRX_TYPES_ALL B,
736 PSA_TRX_TYPES_ALL C
737 WHERE B.CUST_TRX_TYPE_ID = C.PSA_TRX_TYPE_ID
738 AND B.CUST_TRX_TYPE_ID = L.CUST_TRX_TYPE_ID )
739 AND exists
740 (SELECT 'X'
741 FROM
742 AR_RECEIPT_CLASSES A,
743 AR_RECEIPT_METHODS B
744 WHERE
745 A.RECEIPT_CLASS_ID = B.RECEIPT_CLASS_ID
746 AND (B.RECEIPT_METHOD_ID = L.RECEIPT_METHOD_ID
747 OR B.NAME = L.RECEIPT_METHOD_NAME)
748 AND A.CREATION_METHOD_CODE = 'AUTOMATIC' ) ;
749 */
750 /* ------------------------------------------------------------------------+
751 | If the Invoice is having commitments then exit validation |
752 | with an error . Otherwise this transaction will be qualified as MFAR |
753 | transaction |
754 +-------------------------------------------------------------------------*/
755
756 INSERT INTO RA_INTERFACE_ERRORS
757 (INTERFACE_LINE_ID,
758 MESSAGE_TEXT)
759 SELECT nvl(L.INTERFACE_LINE_ID,0) ,
760 'Can not Assign Commitments to Multi Fund Transactions'
761 FROM RA_INTERFACE_LINES_GT L
762 WHERE L.REQUEST_ID = l_request_id
763 AND exists
764 (SELECT 'X' FROM RA_CUST_TRX_TYPES_ALL B,
765 PSA_TRX_TYPES_ALL C
766 WHERE B.CUST_TRX_TYPE_ID = C.PSA_TRX_TYPE_ID
767 AND B.TYPE = 'INV'
768 AND L.REFERENCE_LINE_ID is not null
769 AND B.CUST_TRX_TYPE_ID = L.CUST_TRX_TYPE_ID ) ;
770
771 /* ------------------------------------------------------------------------+
772 | If the Profile Option Use Invoice Accounting for Credit Memos is not |
773 | set to yes then exit validation with an error . |
774 | Otherwise this transaction will be qualified as MFAR |
775 | transaction |
776 +-------------------------------------------------------------------------*/
777
778 -- Bug 2571462 : Added Join between ra_cust_trx_types_all and psa_trx_types_all
779 -- This will supersede bug 2503680
780
781 INSERT INTO RA_INTERFACE_ERRORS
782 (INTERFACE_LINE_ID,
783 MESSAGE_TEXT)
784 SELECT nvl(L.INTERFACE_LINE_ID,0) ,
785 'Credit Memo Profile Option must use Invoice Accounting for Multi Fund Transactions'
786 FROM RA_INTERFACE_LINES_GT L
787 WHERE L.REQUEST_ID = l_request_id
788 AND exists
789 (SELECT 'X' FROM RA_CUST_TRX_TYPES_ALL B,
790 PSA_TRX_TYPES_ALL C
791 WHERE fnd_profile.value('AR_USE_INV_ACCT_FOR_CM_FLAG') <> 'Y'
792 AND B.TYPE = 'CM'
793 AND L.REFERENCE_LINE_ID is not null
794 AND B.CUST_TRX_TYPE_ID = L.CUST_TRX_TYPE_ID
795 AND b.cust_trx_type_id = c.psa_trx_type_id);
796
797 end ar_mfar_autoinv_trx_header ;
798
799
800 /*===========================================================================+
801 | FUNCTION |
802 | ar_mfar_flag |
803 | RETURN VARCHAR2 |
804 | |
805 | DESCRIPTION |
806 | This Fuction returs YES for MFAR and NO otherwise |
807 +===========================================================================*/
808
809 FUNCTION ar_mfar_flag
810 RETURN VARCHAR2 is
811 BEGIN
812 RETURN 'YES';
813 END ar_mfar_flag;
814
815
816 /*===============================================================
817 Function to validate a Miscellaneous Receipt.
818 A Miscellaneous Receipt is of Type Multi-fund if
819 --> The Receivable Activity is flagged as Multi-Fund type from Setup form
820
821 For a Multi-Fund type Misc Receipt, the Payment Method should
822 not require Confirmation / Remittance. If these validations are
823 not met, ERROR is raised during WHEN-VALIDATE-RECORD of Misc. receipts header.
824 ==================================================================*/
825
826 FUNCTION MISC_RCT_VAL
827 ( p_cash_receipt_id IN NUMBER,
828 p_receipt_method_id IN NUMBER,
829 p_receivables_trx_id IN NUMBER
830 ) RETURN VARCHAR2 is
831
832
833 CURSOR c_conf_remit IS
834 SELECT
835 confirm_flag,
836 remit_flag
837 FROM
838 ar_receipt_classes rc,
839 ar_receipt_methods rm
840 WHERE
841 rc.receipt_class_id = rm.receipt_class_id AND
842 rm.receipt_method_id = p_receipt_method_id;
843
844
845 CURSOR c_rec_activity IS
846 SELECT
847 'Y'
848 FROM
849 psa_receivables_trx_all psart,
850 ar_receivables_trx_all rt
851 WHERE
852 psart.psa_receivables_trx_id = p_receivables_trx_id
853 AND
854 psart.psa_receivables_trx_id = rt.receivables_trx_id;
855
856 l_conf_flag ar_receipt_classes.confirm_flag%TYPE;
857 l_remit_flag ar_receipt_classes.remit_flag%TYPE;
858 l_mf_type VARCHAR2(1) := NULL;
859 l_return_flag VARCHAR2(1);
860 mf_rct_invalid EXCEPTION;
861 -- ========================= FND LOG ===========================
862 l_full_path VARCHAR2(100) := g_path || 'MISC_RCT_VAL';
863 -- ========================= FND LOG ===========================
864
865 BEGIN
866
867 FND_MSG_PUB.Initialize;
868
869
870 IF NOT c_rec_activity%isopen THEN
871 OPEN c_rec_activity;
872 FETCH c_rec_activity INTO l_mf_type;
873 CLOSE c_rec_activity;
874 END IF;
875
876 IF l_mf_type IS NOT NULL THEN
877
878 l_return_flag := 'Y';
879
880 ELSE
881 l_return_flag := 'N';
882
883 END IF;
884
885 RETURN l_return_flag;
886
887 EXCEPTION
888
889 WHEN mf_rct_invalid THEN
890 RETURN l_return_flag;
891
892 FND_MESSAGE.SET_NAME('PSA','PSA_MF_MISC_INVALID');
893 -- ========================= FND LOG ===========================
894 psa_utils.debug_other_msg(g_excep_level,l_full_path,FALSE);
895 -- ========================= FND LOG ===========================
896 APP_EXCEPTION.RAISE_EXCEPTION;
897 fnd_msg_pub.add;
898 RAISE FND_API.G_EXC_ERROR;
899
900 WHEN OTHERS THEN
901 -- ========================= FND LOG ===========================
902 psa_utils.debug_other_string(g_excep_level,l_full_path, 'Other exception');
903 psa_utils.debug_other_string(g_excep_level,l_full_path, SQLCODE || SQLERRM);
904 psa_utils.debug_unexpected_msg(l_full_path);
905 -- ========================= FND LOG ===========================
906 RAISE;
907
908
909 END;
910
911
912 end PSA_MFAR_VAL_PKG;