[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_DEFAULTS_3
Source
1 PACKAGE BODY ARP_TRX_DEFAULTS_3 AS
2 /* $Header: ARTUDF3B.pls 120.27.12010000.6 2009/01/02 07:54:06 spdixit ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5
6 NULL_VAR address_rec_type; /* Added for Oracle8 - bug460986 */
7 pg_text_dummy varchar2(10);
8 pg_flag_dummy varchar2(10);
9 pg_number_dummy number;
10 pg_date_dummy date;
11
12 pg_base_curr_code gl_sets_of_books.currency_code%type;
13 pg_base_precision fnd_currencies.precision%type;
14 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
15 pg_set_of_books_id ar_system_parameters.set_of_books_id%type;
16
17 pg_remit_to_address_rec address_rec_type := NULL;
18 pg_payment_type_code ar_receipt_methods.payment_type_code%type; --ajay bug 1081390
19
20 /*===========================================================================+
21 | PROCEDURE |
22 | get_default_remit_to |
23 | |
24 | DESCRIPTION |
25 | Returns the default remit to address |
26 | |
27 | SCOPE - PUBLIC |
28 | |
29 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
30 | arp_util.debug |
31 | |
32 | ARGUMENTS : IN: |
33 | None |
34 | OUT: |
35 | p_remit_to_address_id |
36 | p_remit_to_address_rec |
37 | |
38 | NOTES |
39 | The procedure produces a NO_DATA_FOUND error if no default remit to |
40 | address has been set up. |
41 | |
42 | MODIFICATION HISTORY |
43 | 05-SEP-95 Charlie Tomberg Created |
44 | |
45 +===========================================================================*/
46
47
48 PROCEDURE get_default_remit_to(
49 p_remit_to_address_id OUT NOCOPY
50 NUMBER,
51 p_remit_to_address_rec OUT NOCOPY address_rec_type
52 ) IS
53
54
55 BEGIN
56
57 arp_util.debug('arp_trx_defaults_3.get_default_remit_to()+');
58
59
60 /*----------------------------------------------------------------+
61 | If the default remit to address has not been determined, |
62 | get the default remit to address and cache it using package |
63 | level variables. |
64 | If the default remit to address has been determined, |
65 | just use the cached values. |
66 +----------------------------------------------------------------*/
67
68 IF pg_remit_to_address_rec.cust_acct_site_id IS NULL
69 THEN
70
71 arp_util.debug('selecting the default remit to address.');
72
73 SELECT acct_site.cust_acct_site_id,
74 loc.address1, loc.address2, loc.address3,
75 loc.address4, loc.city, loc.state,
76 loc.province, loc.postal_code,
77 loc.country
78 INTO pg_remit_to_address_rec
79 FROM ra_remit_tos rt,
80 hz_cust_acct_sites acct_site,
81 hz_party_sites party_site,
82 hz_locations loc
83 WHERE rt.state = 'DEFAULT'
84 AND rt.country = 'DEFAULT'
85 AND rt.address_id = acct_site.cust_acct_site_id
86 and acct_site.party_site_id = party_site.party_site_id
87 AND loc.location_id = party_site.location_id
88 AND rt.status = 'A'
89 AND NVL( acct_site.status, 'A' ) = 'A';
90
91 ELSE
92 arp_util.debug('getting the default remit to address from the cache');
93
94 END IF;
95
96 p_remit_to_address_id := pg_remit_to_address_rec.cust_acct_site_id;
97 p_remit_to_address_rec := pg_remit_to_address_rec;
98
99 arp_util.debug(' ');
100 arp_util.debug('The default remit to address is:');
101 arp_util.debug('ID: = ' || pg_remit_to_address_rec.cust_acct_site_id);
102 arp_util.debug('address1: = ' || pg_remit_to_address_rec.address1);
103 arp_util.debug('address2: = ' || pg_remit_to_address_rec.address2);
104 arp_util.debug('address3: = ' || pg_remit_to_address_rec.address3);
105 arp_util.debug('address4: = ' || pg_remit_to_address_rec.address4);
106 arp_util.debug('city : = ' || pg_remit_to_address_rec.city);
107 arp_util.debug('state : = ' || pg_remit_to_address_rec.state);
108 arp_util.debug('provence: = ' || pg_remit_to_address_rec.province);
109 arp_util.debug('postal code: = ' || pg_remit_to_address_rec.postal_code);
110 arp_util.debug('country: = ' || pg_remit_to_address_rec.country);
111 arp_util.debug(' ');
112
113 arp_util.debug('arp_trx_defaults_3.get_default_remit_to()-');
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 arp_util.debug('EXCEPTION: arp_trx_defaults_3.get_default_remit_to()');
118 RAISE;
119
120 END;
121
122 /*===========================================================================+
123 | PROCEDURE |
124 | get_remit_to_address |
125 | |
126 | DESCRIPTION |
127 | Returns the default remit to address |
128 | |
129 | SCOPE - PUBLIC |
130 | |
131 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
132 | arp_util.debug |
133 | |
134 | ARGUMENTS : IN: |
135 | p_match_state |
136 | p_match_country |
137 | p_match_postal_code |
138 | p_match_address_id |
139 | p_match_site_use_id |
140 | OUT: |
141 | p_remit_to_address_id |
142 | p_remit_to_address_rec |
143 | |
144 | NOTES |
145 | One of the three sets of parameters must be passed in: |
146 | - p_match_state, p_match_country or p_match_postal_code |
147 | - p_match_address_id |
148 | - p_match_site use_id |
149 | |
150 | The procedure produces a NO_DATA_FOUND error if no remit to address |
151 | can be found. |
152 | |
153 | MODIFICATION HISTORY |
154 | 06-SEP-95 Charlie Tomberg Created |
155 | |
156 +===========================================================================*/
157
158
159 PROCEDURE get_remit_to_address(
160 p_match_state IN
161 hz_locations.state%type,
162 p_match_country IN
163 hz_locations.country%type,
164 p_match_postal_code IN
165 hz_locations.postal_code%type,
166 p_match_address_id IN
167 NUMBER,
168 p_match_site_use_id IN
169 NUMBER,
170 p_remit_to_address_id OUT NOCOPY
171 NUMBER,
172 p_remit_to_address_rec OUT NOCOPY
173 address_rec_type
174 ) IS
175
176
177 l_match_state hz_locations.state%type;
178 l_match_country hz_locations.country%type;
179 l_match_postal_code hz_locations.postal_code%type;
180 l_remit_to_address_rec address_rec_type;
181
182 /* BugFix:2107873 Modified the Following SELECT statement so that
183 the Remit_to Country will be picked Up from fnd_territories_vl instead
184 of from hz_locations */
185 CURSOR remit_to IS
186 SELECT acct_site.cust_acct_site_id,
187 loc.address1, loc.address2,
188 loc.address3, loc.address4,
189 loc.city, loc.state,
190 loc.province, loc.postal_code,
191 territory.territory_short_name --loc.country
192 FROM hz_cust_acct_sites acct_site,
193 hz_party_sites party_site,
194 hz_locations loc,
195 fnd_territories_vl territory,
196 ra_remit_tos rt
197 WHERE NVL( acct_site.status, 'A' ) = 'A'
198 AND acct_site.cust_acct_site_id = rt.address_id
199 AND acct_site.party_site_id = party_site.party_site_id
200 AND loc.location_id = party_site.location_id
201 AND rt.status = 'A'
202 AND rt.country = l_match_country
203 AND loc.country = territory.territory_code
204 AND (
205 l_match_state = NVL( rt.state, l_match_state )
206 OR
207 (
208 l_match_state IS NULL AND
209 rt.state IS NULL
210 )
211 OR (
212 l_match_state IS NULL AND
213 l_match_postal_code <= NVL( rt.postal_code_high,
214 l_match_postal_code ) AND
215 l_match_postal_code >= NVL( rt.postal_code_low,
216 l_match_postal_code ) AND
217 (
218 postal_code_low IS NOT NULL
219 OR postal_code_high IS NOT NULL
220 )
221 )
222 )
223 AND (
224 (
225 l_match_postal_code <= NVL( rt.postal_code_high,
226 l_match_postal_code ) AND
227 l_match_postal_code >= NVL( rt.postal_code_low,
228 l_match_postal_code )
229 )
230 OR (
231 l_match_postal_code IS NULL AND
232 rt.postal_code_low IS NULL AND
233 rt.postal_code_high IS NULL
234 )
235 )
236 ORDER BY rt.state,
237 rt.postal_code_low,
238 rt.postal_code_high;
239
240
241 BEGIN
242
243 arp_util.debug('arp_trx_defaults_3.get_remit_to_address()+');
244
245 /*----------------------------------+
246 | Initialize the OUT NOCOPY parameters |
247 +----------------------------------*/
248
249 p_remit_to_address_id := null;
250 p_remit_to_address_rec := NULL_VAR; /* modified for Oracle8 -bug460986 */
251
252 /*--------------------------------------------+
253 | Validate parameters to make sure that a |
254 | valid match criteria has been specified. |
255 +--------------------------------------------*/
256
257 IF (
258 p_match_state IS NULL AND
259 p_match_country IS NULL AND
260 p_match_postal_code IS NULL AND
261 p_match_address_id IS NULL AND
262 p_match_site_use_id IS NULL
263 )
264 THEN
265 fnd_message.set_name('AR', 'AR_INV_ARGS');
266 fnd_message.set_token('USER_EXIT',
267 'get_remit_to_address()');
268 app_exception.raise_exception;
269 END IF;
270
271 /*-------------------------------------------------------------------------+
272 | IF the state, country or postal code to match to have been specified, |
273 | THEN use those values. |
274 | ELSE get the match values by selecting based on the address or |
275 | the site use depending on which ID has been specified. |
276 +-------------------------------------------------------------------------*/
277
278 IF (
279 p_match_state ||
280 p_match_country ||
281 p_match_postal_code IS NULL
282 )
283 THEN
284
285 /*------------------------------------------+
286 | Get the address information to match |
287 | if the address_id was specified |
288 +------------------------------------------*/
289
290 IF ( p_match_address_id IS NOT NULL )
291 THEN
292
293 arp_util.debug('getting address Info. based on address_id');
294
295 SELECT loc.state,
296 loc.country,
297 loc.postal_code
298 INTO l_match_state,
299 l_match_country,
300 l_match_postal_code
301 FROM hz_cust_acct_sites acct_site,
302 hz_party_sites party_site,
303 hz_locations loc
304 WHERE acct_site.cust_acct_site_id = p_match_address_id
305 AND acct_site.party_site_id = party_site.party_site_id
306 AND loc.location_id = party_site.location_id;
307
308 END IF;
309
310 /*-------------------------------------------+
311 | Get the address information to match if |
312 | the site_use_id was specified and the |
313 | address_id was not specified |
314 +-------------------------------------------*/
315
316 IF ( p_match_site_use_id IS NOT NULL AND
317 p_match_address_id IS NULL )
318 THEN
319
320 arp_util.debug('getting address Info. based on site_use_id');
321
322 SELECT loc.state,
323 loc.country,
324 loc.postal_code
325 INTO l_match_state,
326 l_match_country,
327 l_match_postal_code
328 FROM hz_cust_acct_sites acct_site,
329 hz_party_sites party_site,
330 hz_locations loc,
331 hz_cust_site_uses su
332 WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
333 AND su.site_use_id = p_match_site_use_id
334 AND acct_site.party_site_id = party_site.party_site_id
335 AND loc.location_id = party_site.location_id;
336
337 END IF;
338
339 ELSE -- match columns were specified case
340 arp_util.debug('getting address Info. based on match values');
341
342 l_match_state := p_match_state;
343 l_match_country := p_match_country;
344 l_match_postal_code := p_match_postal_code;
345
346 END IF;
347
348 /*---------------------------------------------------------------+
349 | Select the remit to information based on the match criteria |
350 +---------------------------------------------------------------*/
351
352 arp_util.debug('selecting remit to information');
353
354 OPEN remit_to;
355
356 FETCH remit_to
357 INTO l_remit_to_address_rec;
358
359
360 /*-------------------------------------------------------------+
361 | IF no remit to address was selected above, |
362 | THEN use the default remit to address. |
363 | |
364 | IF no default remit to address exists, |
365 | THEN the procedure will raise a NO_DATA_FOUND exception. |
366 +-------------------------------------------------------------*/
367
368 IF ( remit_to%NOTFOUND )
369 THEN
370
371 get_default_remit_to(
372 p_remit_to_address_id,
373 l_remit_to_address_rec
374 );
375
376 p_remit_to_address_rec := l_remit_to_address_rec;
377
378 ELSE
379 p_remit_to_address_id := l_remit_to_address_rec.cust_acct_site_id;
380 p_remit_to_address_rec := l_remit_to_address_rec;
381 END IF;
382
383
384 CLOSE remit_to;
385
386 arp_util.debug(' ');
387 arp_util.debug('The remit to address is:');
388 arp_util.debug('ID: = ' || l_remit_to_address_rec.cust_acct_site_id);
389 arp_util.debug('address1: = ' || l_remit_to_address_rec.address1);
390 arp_util.debug('address2: = ' || l_remit_to_address_rec.address2);
391 arp_util.debug('address3: = ' || l_remit_to_address_rec.address3);
392 arp_util.debug('address4: = ' || l_remit_to_address_rec.address4);
393 arp_util.debug('city : = ' || l_remit_to_address_rec.city);
394 arp_util.debug('state : = ' || l_remit_to_address_rec.state);
395 arp_util.debug('provence: = ' || l_remit_to_address_rec.province);
396 arp_util.debug('postal code: = ' || l_remit_to_address_rec.postal_code);
397 arp_util.debug('country: = ' || l_remit_to_address_rec.country);
398 arp_util.debug(' ');
399
400 arp_util.debug('arp_trx_defaults_3.get_remit_to_address()-');
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 arp_util.debug('EXCEPTION: arp_trx_defaults_3.get_remit_to_address()');
405
406 arp_util.debug('---------- ' ||
407 'Parameters for arp_trx_defaults_3.get_remit_to_address() ' ||
408 '---------- ');
409
410 arp_util.debug('p_match_state : ' || p_match_state );
411 arp_util.debug('p_match_country : ' || p_match_country );
412 arp_util.debug('p_match_postal_code : ' || p_match_postal_code );
413 arp_util.debug('p_match_address_id : ' || p_match_address_id );
414 arp_util.debug('p_match_site_use_id : ' || p_match_site_use_id );
415
416
417 /*-----------------------------------------------------------+
418 | Close the cursor, but don't raise an error if the close |
419 | fails. The original error will be raised in this case. |
420 +------------------------------------------------------------*/
421
422 BEGIN
423 CLOSE remit_to;
424 EXCEPTION
425 WHEN OTHERS THEN null;
426 END;
427
428 RAISE;
429
430 END;
431
432 /*===========================================================================+
433 | FUNCTION |
434 | check_payment_method |
435 | |
436 | DESCRIPTION |
437 | Determines if a potential default payment method is valid. |
438 | |
439 | SCOPE - PUBLIC |
440 | |
441 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
442 | arp_util.debug |
443 | |
444 | ARGUMENTS : IN: |
445 | p_trx_date |
446 | p_customer_id |
447 | p_site_use_id |
448 | p_currency_code |
449 | OUT: |
450 | p_payment_method_name |
451 | p_receipt_method_id |
452 | p_creation_method_code |
453 | |
454 | RETURNS : TRUE if valid, FALSE otherwise |
455 | |
456 | NOTES |
457 | |
458 | MODIFICATION HISTORY |
459 | 21-NOV-95 Charlie Tomberg Created |
460 | 26-DEC-97 Debbie Jancis Fixed Bug 602458. Altered defaulting |
461 | to check if payment method is |
462 | automatic then a check is performed to |
463 | make sure that there is a bank acct for|
464 | entered currency. If not, nothing is |
465 | defaulted. |
466 +===========================================================================*/
467
468
469 FUNCTION check_payment_method(
470 p_trx_date IN
471 ra_customer_trx.trx_date%type,
472 p_customer_id IN
473 ra_customer_trx.customer_trx_id%type,
474 p_site_use_id IN
475 hz_cust_site_uses.site_use_id%type,
476 p_currency_code IN
477 fnd_currencies.currency_code%type,
478 p_payment_method_name OUT NOCOPY
479 ar_receipt_methods.name%type,
480 p_receipt_method_id OUT NOCOPY
481 ar_receipt_methods.receipt_method_id%type,
482 p_creation_method_code OUT NOCOPY
483 ar_receipt_classes.creation_method_code%type
484 ) RETURN BOOLEAN IS
485
486 CURSOR payment_method_cur IS
487 SELECT arm.name payment_method_name,
488 arm.receipt_method_id,
489 arm.payment_channel_code, --ajay bug 1081390
490 arc.creation_method_code
491 FROM ar_receipt_methods arm,
492 ra_cust_receipt_methods rcrm,
493 ar_receipt_method_accounts arma,
494 ce_bank_acct_uses_all aba,
495 ce_bank_accounts cba,
496 ar_receipt_classes arc,
497 ce_bank_branches_v bp /*Bug3348454*/
498 WHERE arm.receipt_method_id = rcrm.receipt_method_id
499 AND arm.receipt_method_id = arma.receipt_method_id
500 AND arm.receipt_class_id = arc.receipt_class_id
501 AND rcrm.customer_id = p_customer_id
502 AND arma.org_id = aba.org_id
503 AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
504 AND aba.bank_account_id = cba.bank_account_id
505 AND bp.branch_party_id = cba.bank_branch_id /*Bug3348454*/
506 AND p_trx_date <= NVL(bp.end_date,p_trx_date) /*Bug3348454*/
507 AND
508 (
509 /* Bug-3770337-PM - Remove NVl condition */
510 rcrm.site_use_id = p_site_use_id
511 OR
512 (
513 p_site_use_id IS NULL
514 AND rcrm.site_use_id IS NULL
515 )
516 )
517 AND rcrm.primary_flag = 'Y'
518 AND (
519 cba.currency_code =
520 p_currency_code OR
521 cba.receipt_multi_currency_flag = 'Y'
522 )
523
524 -- added following condition for Bug 602458:
525 --Removing the join condition based on currency_code as part of bug fix 5346710
526
527 /*AND ( arc.creation_method_code = 'MANUAL' or
528 ( arc.creation_method_code = 'AUTOMATIC' and
529 p_currency_code in (
530 select currency_code from
531 IBY_FNDCPT_PAYER_ASSGN_INSTR_V
532 where party_id = get_party_id(p_customer_id)
533 ))*/
534 -- AND aba.set_of_books_id = pg_set_of_books_id
535
536 /*Bug3348454*/
537 /*AND TRUNC(nvl(aba.end_date,
538 p_trx_date)) >=
539 TRUNC(p_trx_date)*/
540
541 AND TRUNC(nvl(cba.end_date,p_trx_date+1)) > TRUNC(p_trx_date)
542
543 AND p_trx_date between
544 TRUNC(nvl(
545 arm.start_date,
546 p_trx_date))
547 and TRUNC(nvl(
548 arm.end_date,
549 p_trx_date))
550 AND p_trx_date between
551 TRUNC(nvl(
552 rcrm.start_date,
553 p_trx_date))
554 and TRUNC(nvl(
555 rcrm.end_date,
556 p_trx_date))
557 AND p_trx_date between
558 TRUNC(arma.start_date)
559 and TRUNC(nvl(
560 arma.end_date,
561 p_trx_date))
562 /* 19-APR-2000 J Rautiainen BR Implementation. Added union to default BR
563 * payment method. */
564 UNION
565 SELECT arm.name payment_method_name,
566 arm.receipt_method_id,
567 arm.payment_channel_code,
568 arc.creation_method_code
569 FROM ar_receipt_methods arm,
570 ra_cust_receipt_methods rcrm,
571 ar_receipt_classes arc,
572 ar_system_parameters sys
573 WHERE arm.receipt_method_id = rcrm.receipt_method_id
574 AND arm.receipt_class_id = arc.receipt_class_id
575 AND arc.creation_method_code = 'BR'
576 AND NVL(sys.bills_receivable_enabled_flag,'N') = 'Y'
577 AND rcrm.customer_id = p_customer_id
578 AND
579 (
580 /* Bug-3770337-PM - Remove NVl condition */
581 rcrm.site_use_id = p_site_use_id
582 OR
583 (
584 p_site_use_id IS NULL
585 AND rcrm.site_use_id IS NULL
586 )
587 )
588 AND rcrm.primary_flag = 'Y'
589 AND p_trx_date between
590 TRUNC(nvl(
591 arm.start_date,
592 p_trx_date))
593 and TRUNC(nvl(
594 arm.end_date,
595 p_trx_date))
596 AND p_trx_date between
597 TRUNC(nvl(
598 rcrm.start_date,
599 p_trx_date))
600 and TRUNC(nvl(
601 rcrm.end_date,
602 p_trx_date));
603
604 l_payment_method_name ar_receipt_methods.name%type;
605 l_receipt_method_id ar_receipt_methods.receipt_method_id%type;
606 l_creation_method_code ar_receipt_classes.creation_method_code%type;
607 payment_method_rec payment_method_cur%ROWTYPE;
608
609 BEGIN
610
611 IF PG_DEBUG in ('Y', 'C') THEN
612 arp_util.debug('arp_trx_defaults_3.check_payment_method()+');
613 END IF;
614
615 /* 19-APR-2000 J Rautiainen BR Implementation.
616 * Moved select statement to cursor. Also removed NO_DATA_FOUND
617 * Exception handler and added the logic in the IF statement below */
618
619 OPEN payment_method_cur;
620 FETCH payment_method_cur INTO payment_method_rec;
621
622 IF payment_method_cur%NOTFOUND THEN
623 IF PG_DEBUG in ('Y', 'C') THEN
624 arp_util.debug('check_payment_method: ' || 'return value: FALSE');
625 END IF;
626 CLOSE payment_method_cur;
627 RETURN(FALSE);
628 END IF;
629
630 CLOSE payment_method_cur;
631
632 l_payment_method_name := payment_method_rec.payment_method_name;
633 l_receipt_method_id := payment_method_rec.receipt_method_id;
634 l_creation_method_code := payment_method_rec.creation_method_code;
635 p_payment_method_name := payment_method_rec.payment_method_name;
636 p_receipt_method_id := payment_method_rec.receipt_method_id;
637 p_creation_method_code := payment_method_rec.creation_method_code;
638 pg_payment_type_code := payment_method_rec.payment_channel_code; --ajay bug 1081390
639
640 IF PG_DEBUG in ('Y', 'C') THEN
641 arp_util.debug('check_payment_method: ' || 'p_payment_method_name = ' || l_payment_method_name );
642 arp_util.debug('check_payment_method: ' || 'p_receipt_method_id = ' ||
643 TO_CHAR(l_receipt_method_id ) );
644 arp_util.debug('check_payment_method: ' || 'p_creation_method_code = ' || l_creation_method_code );
645 arp_util.debug('check_payment_method: ' || 'return value = TRUE');
646 arp_util.debug('arp_trx_defaults_3.check_payment_method()-');
647 END IF;
648
649
650 return(TRUE);
651
652 EXCEPTION
653
654 WHEN OTHERS THEN
655 IF PG_DEBUG in ('Y', 'C') THEN
656 arp_util.debug('check_payment_method: ' ||
657 'EXCEPTION: arp_trx_defaults_3.check_payment_method()');
658 arp_util.debug('------- parameters for check_payment_method ----');
659 arp_util.debug('check_payment_method: ' || 'p_trx_date = ' || TO_CHAR(p_trx_date) );
660 arp_util.debug('check_payment_method: ' || 'p_customer_id = ' || TO_CHAR(p_customer_id) );
661 arp_util.debug('check_payment_method: ' || 'p_site_use_id = ' || TO_CHAR(p_site_use_id) );
662 arp_util.debug('check_payment_method: ' || 'p_currency_code = ' || p_currency_code );
663 END IF;
664
665 RAISE;
666
667 END check_payment_method;
668
669
670 /*===========================================================================+
671 | FUNCTION |
672 | check_bank default |
673 | |
674 | DESCRIPTION |
675 | Determines if a potential default bank is valid. |
676 | |
677 | SCOPE - PRIVATE |
678 | |
679 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
680 | arp_util.debug |
681 | |
682 | ARGUMENTS : IN: |
683 | p_trx_date |
684 | p_customer_id |
685 | p_site_use_id |
686 | p_currency_code |
687 | OUT: |
688 | p_customer_bank_account_id |
689 | p_bank_account_num |
690 | p_bank_name |
691 | p_bank_branch_name |
692 | p_bank_branch_id |
693 | |
694 | RETURNS : TRUE if valid, FALSE otherwise |
695 | |
696 | NOTES |
697 | |
698 | MODIFICATION HISTORY |
699 | 21-NOV-95 Charlie Tomberg Created |
700 | 16-AUG-99 Debbie Jancis Acct masking project. Modified select |
701 | to get masked bank acct num if profile |
702 | is set. |
703 +===========================================================================*/
704
705 FUNCTION check_bank_default(
706 p_trx_date IN
707 ra_customer_trx.trx_date%type,
708 p_customer_id IN
709 ra_customer_trx.customer_trx_id%type,
710 p_site_use_id IN
711 hz_cust_site_uses.site_use_id%type,
712 p_currency_code IN
713 fnd_currencies.currency_code%type,
714 p_customer_bank_account_id OUT NOCOPY
715 ce_bank_accounts.bank_account_id%type,
716 p_bank_account_num OUT NOCOPY
717 ce_bank_accounts.bank_account_num%type,
718 p_bank_name OUT NOCOPY
719 ce_bank_branches_v.bank_name%type,
720 p_bank_branch_name OUT NOCOPY
721 ce_bank_branches_v.bank_branch_name%type,
722 p_bank_branch_id OUT NOCOPY
723 ce_bank_branches_v.branch_party_id%TYPE
724 ) RETURN BOOLEAN IS
725
726
727 l_customer_bank_account_id
728 ce_bank_accounts.bank_account_id%type;
729 l_bank_account_num ce_bank_accounts.bank_account_num%type;
730 l_bank_name ce_bank_branches_v.bank_name%type;
731 l_bank_branch_name ce_bank_branches_v.bank_branch_name%type;
732 l_bank_branch_id ce_bank_branches_v.branch_party_id%TYPE;
733
734
735 BEGIN
736
737 IF PG_DEBUG in ('Y', 'C') THEN
738 arp_util.debug('arp_trx_defaults_3.check_bank_default()+');
739 END IF;
740 /* PAYMENT UPTAKE removed the code to default the bank_account */
741
742 return(TRUE);
743
744
745 END check_bank_default;
746
747
748 /*===========================================================================+
749 | PROCEDURE |
750 | get_payment_method_default |
751 | |
752 | DESCRIPTION |
753 | Gets the default payment method. |
754 | |
755 | SCOPE - PUBLIC |
756 | |
757 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
758 | arp_util.debug |
759 | |
760 | ARGUMENTS : IN: |
761 | p_trx_date |
762 | p_currency_code |
763 | p_paying_customer_id |
764 | p_paying_site_use_id |
765 | p_bill_to_customer_id |
766 | p_bill_to_site_use_id |
767 | OUT: |
768 | p_payment_method_name |
769 | p_receipt_method_id |
770 | p_creation_method_code |
771 | |
772 | RETURNS : NONE |
773 | |
774 | NOTES |
775 | |
776 | MODIFICATION HISTORY |
777 | 21-NOV-95 Charlie Tomberg Created |
778 | 06-OCT-04 Surendra Rajan Modified for bug-3770337 |
779 | |
780 +===========================================================================*/
781
782 PROCEDURE get_payment_method_default(
783 p_trx_date IN
784 ra_customer_trx.trx_date%type,
785 p_currency_code IN
786 fnd_currencies.currency_code%type,
787 p_paying_customer_id IN
788 hz_cust_accounts.cust_account_id%type,
789 p_paying_site_use_id IN
790 hz_cust_site_uses.site_use_id%type,
791 p_bill_to_customer_id IN
792 hz_cust_accounts.cust_account_id%type,
793 p_bill_to_site_use_id IN
794 hz_cust_site_uses.site_use_id%type,
795 p_payment_method_name OUT NOCOPY
796 ar_receipt_methods.name%type,
797 p_receipt_method_id OUT NOCOPY
798 ar_receipt_methods.receipt_method_id%type,
799 p_creation_method_code OUT NOCOPY
800 ar_receipt_classes.creation_method_code%type,
801 p_trx_manual_flag IN VARCHAR2 DEFAULT 'N'
802 ) IS
803
804
805 BEGIN
806
807 IF PG_DEBUG in ('Y', 'C') THEN
808 arp_util.debug('arp_trx_defaults_3.get_payment_method_default()+');
809 END IF;
810
811
812 /*-----------------------------------------------------------------+
813 | Check the parameters to make sure that defaulting is possible |
814 +-----------------------------------------------------------------*/
815
816 IF (
817 p_trx_date IS NULL
818 OR p_currency_code IS NULL
819 OR p_paying_customer_id ||
820 p_paying_site_use_id ||
821 p_bill_to_customer_id ||
822 p_bill_to_site_use_id IS NULL
823 )
824 THEN
825 IF PG_DEBUG in ('Y', 'C') THEN
826 arp_util.debug('Invalid parameters for get_payment_method_default()');
827 END IF;
828 RETURN;
829 END IF;
830
831 /*----------------------------------------------------------------+
832 | User creates a transaction in transaction form then the |
833 | payment method will default from the primary payment method |
834 | of the paying site use otherwise (Ref. Bug-3770337) |
835 | Default in the payment method using the following hierarchy: |
836 | 1) Primary payment method of the paying site use |
837 | 2) Primary payment method of the paying customer |
838 | 3) Primary payment method of the bill to site use |
839 | 4) Primary payment method of the bill to customer |
840 +----------------------------------------------------------------*/
841 IF ( p_paying_site_use_id IS NOT NULL )
842 THEN
843 IF (check_payment_method(
844 p_trx_date,
845 p_paying_customer_id,
846 p_paying_site_use_id,
847 p_currency_code,
848 p_payment_method_name,
849 p_receipt_method_id,
850 p_creation_method_code
851 ) = TRUE )
852 THEN
853 IF PG_DEBUG in ('Y', 'C') THEN
854 arp_util.debug('get_payment_method_default: ' ||
855 'Defaulting payment method from: Paying Customer Site');
856 END IF;
857 RETURN;
858 END IF;
859 END IF;
860
861
862 IF (check_payment_method(
863 p_trx_date,
864 p_paying_customer_id,
865 NULL,
866 p_currency_code,
867 p_payment_method_name,
868 p_receipt_method_id,
869 p_creation_method_code
870 ) = TRUE )
871 THEN
872
873 IF PG_DEBUG in ('Y', 'C') THEN
874 arp_util.debug('get_payment_method_default: ' || 'Defaulting payment method from: Paying Customer');
875 END IF;
876 RETURN;
877 END IF;
878
879 /* Bug-3770337-PM - Added below IF statement */
880 /*Bug 5208067 Moved the below IF statement from previous IF (to check payment method at
881 billto customer header level) to this IF */
882 IF p_trx_manual_flag = 'N' THEN
883
884 IF ( NVL(p_bill_to_customer_id,-1) <> NVL(p_paying_customer_id,-1) AND
885 NVL(p_bill_to_site_use_id,-1) <> NVL(p_paying_site_use_id,-1) AND
886 p_bill_to_site_use_id IS NOT NULL
887 )
888 THEN
889 IF (check_payment_method(
890 p_trx_date,
891 p_bill_to_customer_id,
892 p_bill_to_site_use_id,
893 p_currency_code,
894 p_payment_method_name,
895 p_receipt_method_id,
896 p_creation_method_code
897 ) = TRUE )
898 THEN
899 IF PG_DEBUG in ('Y', 'C') THEN
900 arp_util.debug('get_payment_method_default: ' ||
901 'Defaulting payment method from: Bill To Customer Site');
902 END IF;
903
904 RETURN;
905 END IF;
906 END IF;
907
908 IF ( NVL(p_bill_to_customer_id,-1) <> NVL(p_paying_customer_id,-1) )
909 THEN
910 IF (check_payment_method(
911 p_trx_date,
912 p_bill_to_customer_id,
913 NULL,
914 p_currency_code,
915 p_payment_method_name,
916 p_receipt_method_id,
917 p_creation_method_code
918 ) = TRUE )
919 THEN
920 IF PG_DEBUG in ('Y', 'C') THEN
921 arp_util.debug('get_payment_method_default: ' || 'Defaulting payment method from: Bill To Customer');
922 END IF;
923 RETURN;
924 END IF;
925 END IF;
926 END IF;
927
928 IF PG_DEBUG in ('Y', 'C') THEN
929 arp_util.debug('arp_trx_defaults_3.get_payment_method_default()-');
930 END IF;
931
932 EXCEPTION
933 WHEN NO_DATA_FOUND THEN NULL;
934 WHEN OTHERS THEN
935 IF PG_DEBUG in ('Y', 'C') THEN
936 arp_util.debug('get_payment_method_default: ' ||
937 'EXCEPTION: arp_trx_defaults_3.get_payment_method_default()');
938 arp_util.debug('get_payment_method_default: ' ||
939 '------- parameters for get_payment_method_default ----');
940 arp_util.debug('get_payment_method_default: ' || 'p_trx_date = ' || TO_CHAR(p_trx_date) );
941 arp_util.debug('get_payment_method_default: ' || 'p_currency_code = ' || p_currency_code );
942 arp_util.debug('get_payment_method_default: ' || 'p_paying_customer_id = ' || p_paying_customer_id );
943 arp_util.debug('get_payment_method_default: ' || 'p_paying_site_use_id = ' || p_paying_site_use_id );
944 arp_util.debug('get_payment_method_default: ' || 'p_bill_to_customer_id = ' || p_bill_to_customer_id );
945 arp_util.debug('get_payment_method_default: ' || 'p_bill_to_site_use_id = ' || p_bill_to_site_use_id );
946 END IF;
947
948 RAISE;
949
950 END get_payment_method_default;
951
952
953 /*===========================================================================+
954 | PROCEDURE |
955 | get_bank_defaults |
956 | |
957 | DESCRIPTION |
958 | Gets the default payment method. |
959 | |
960 | SCOPE - PUBLIC |
961 | |
962 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
963 | arp_util.debug |
964 | |
965 | ARGUMENTS : IN: |
966 | p_trx_date |
967 | p_currency_code |
968 | p_paying_customer_id |
969 | p_paying_site_use_id |
970 | p_bill_to_customer_id |
971 | p_bill_to_site_use_id |
972 | p_payment_type_code |
973 | OUT: |
974 | p_customer_bank_account_id |
975 | p_bank_account_num |
976 | p_bank_name |
977 | p_bank_branch_name |
978 | p_bank_branch_id |
979 | |
980 | RETURNS : NONE |
981 | |
982 | NOTES |
983 | |
984 | MODIFICATION HISTORY |
985 | 21-NOV-95 Charlie Tomberg Created |
986 | 06-OCT-04 Surendra Rajan Modified for bug-3770337 |
987 | |
988 +===========================================================================*/
989
990 PROCEDURE get_bank_defaults(
991 p_trx_date IN
992 ra_customer_trx.trx_date%type,
993 p_currency_code IN
994 fnd_currencies.currency_code%type,
995 p_paying_customer_id IN
996 hz_cust_accounts.cust_account_id%type,
997 p_paying_site_use_id IN
998 hz_cust_site_uses.site_use_id%type,
999 p_bill_to_customer_id IN
1000 hz_cust_accounts.cust_account_id%type,
1001 p_bill_to_site_use_id IN
1002 hz_cust_site_uses.site_use_id%type,
1003 p_payment_type_code IN
1004 ar_receipt_methods.payment_type_code%type,
1005 p_customer_bank_account_id OUT NOCOPY
1006 ce_bank_accounts.bank_account_id%type,
1007 p_bank_account_num OUT NOCOPY
1008 ce_bank_accounts.bank_account_num%type,
1009 p_bank_name OUT NOCOPY
1010 ce_bank_branches_v.bank_name%type,
1011 p_bank_branch_name OUT NOCOPY
1012 ce_bank_branches_v.bank_branch_name%type,
1013 p_bank_branch_id OUT NOCOPY
1014 ce_bank_branches_v.branch_party_id%TYPE,
1015 p_trx_manual_flag IN VARCHAR2 DEFAULT 'N'
1016 ) IS
1017
1018
1019 BEGIN
1020
1021 IF PG_DEBUG in ('Y', 'C') THEN
1022 arp_util.debug('arp_trx_defaults_3.get_bank_defaults()+');
1023 END IF;
1024
1025
1026 /*-----------------------------------------------------------------+
1027 | Check the parameters to make sure that defaulting is possible |
1028 +-----------------------------------------------------------------*/
1029
1030 IF (
1031 p_trx_date IS NULL
1032 OR p_currency_code IS NULL
1033 OR p_paying_customer_id ||
1034 p_paying_site_use_id ||
1035 p_bill_to_customer_id ||
1036 p_bill_to_site_use_id IS NULL
1037 )
1038 THEN
1039 IF PG_DEBUG in ('Y', 'C') THEN
1040 arp_util.debug('Invalid parameters for get_bank_defaults()');
1041 END IF;
1042 RETURN;
1043 END IF;
1044
1045 /* check first to see if the payment type is a credit card. If it
1046 is then we want to default bank_name and branch_name fields
1047 from ce_bank_branches_v.. */
1048
1049 IF ( NVL(p_payment_type_code,'-1') = 'CREDIT_CARD' ) THEN
1050 IF PG_DEBUG in ('Y', 'C') THEN
1051 arp_util.debug('get_bank_defaults: ' || 'Defaulting parameters for Credit Card Type ');
1052 END IF;
1053
1054 SELECT abb.bank_name,
1055 abb.bank_branch_name,
1056 abb.branch_party_id
1057 INTO
1058 p_bank_name,
1059 p_bank_branch_name,
1060 p_bank_branch_id
1061 FROM
1062 ce_bank_branches_v abb
1063 WHERE
1064 abb.branch_party_id = arp_global.CC_BANK_BRANCH_ID;
1065
1066 RETURN;
1067 END IF;
1068
1069 /*------------------------------------------------------------------+
1070 | User creates a transaction in transaction form then the bank |
1071 | information will default from the primary bank of the paying |
1072 | site use otherwise (Ref. Bug-3770337) |
1073 | Default in the bank information using the following hierarchy: |
1074 | 1) Primary bank of the paying site use |
1075 | 2) Primary bank of the paying customer |
1076 | 3) Primary bank of the bill to site use |
1077 | 4) Primary bank of the bill to customer |
1078 +------------------------------------------------------------------*/
1079
1080 IF ( p_paying_site_use_id IS NOT NULL )
1081 THEN
1082 IF (check_bank_default(
1083 p_trx_date,
1084 p_paying_customer_id,
1085 p_paying_site_use_id,
1086 p_currency_code,
1087 p_customer_bank_account_id,
1088 p_bank_account_num,
1089 p_bank_name,
1090 p_bank_branch_name,
1091 p_bank_branch_id
1092 ) = TRUE )
1093 THEN
1094 IF PG_DEBUG in ('Y', 'C') THEN
1095 arp_util.debug('get_bank_defaults: ' ||
1096 'Defaulting bank from: Paying Customer Site');
1097 END IF;
1098 RETURN;
1099 END IF;
1100 END IF;
1101
1102 IF (check_bank_default(
1103 p_trx_date,
1104 p_paying_customer_id,
1105 NULL,
1106 p_currency_code,
1107 p_customer_bank_account_id,
1108 p_bank_account_num,
1109 p_bank_name,
1110 p_bank_branch_name,
1111 p_bank_branch_id
1112 ) = TRUE )
1113 THEN
1114
1115 IF PG_DEBUG in ('Y', 'C') THEN
1116 arp_util.debug('get_bank_defaults: ' || 'Defaulting bank from: Paying Customer');
1117 END IF;
1118 RETURN;
1119 END IF;
1120
1121 /* Bug-3770337-PM - Added below IF statement */
1122 /*Moved this if here - Bug 5444390*/
1123
1124 IF p_trx_manual_flag = 'N' THEN
1125 IF ( NVL(p_bill_to_customer_id,-1) <> NVL(p_paying_customer_id,-1) AND
1126 NVL(p_bill_to_site_use_id,-1) <> NVL(p_paying_site_use_id,-1) AND
1127 p_bill_to_site_use_id IS NOT NULL
1128 )
1129 THEN
1130 IF (check_bank_default(
1131 p_trx_date,
1132 p_bill_to_customer_id,
1133 p_bill_to_site_use_id,
1134 p_currency_code,
1135 p_customer_bank_account_id,
1136 p_bank_account_num,
1137 p_bank_name,
1138 p_bank_branch_name,
1139 p_bank_branch_id
1140 ) = TRUE )
1141 THEN
1142 IF PG_DEBUG in ('Y', 'C') THEN
1143 arp_util.debug('get_bank_defaults: ' ||
1144 'Defaulting bank from: Bill To Customer Site');
1145 END IF;
1146
1147 RETURN;
1148 END IF;
1149 END IF;
1150
1151 IF ( NVL(p_bill_to_customer_id,-1) <> NVL(p_paying_customer_id,-1) )
1152 THEN
1153 IF (check_bank_default(
1154 p_trx_date,
1155 p_bill_to_customer_id,
1156 NULL,
1157 p_currency_code,
1158 p_customer_bank_account_id,
1159 p_bank_account_num,
1160 p_bank_name,
1161 p_bank_branch_name,
1162 p_bank_branch_id
1163 ) = TRUE )
1164 THEN
1165 IF PG_DEBUG in ('Y', 'C') THEN
1166 arp_util.debug('get_bank_defaults: ' || 'Defaulting bank from: Bill To Customer');
1167 END IF;
1168 RETURN;
1169 END IF;
1170 END IF;
1171 END IF;
1172
1173 IF PG_DEBUG in ('Y', 'C') THEN
1174 arp_util.debug('arp_trx_defaults_3.get_bank_defaults()-');
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN NO_DATA_FOUND THEN NULL;
1179 WHEN OTHERS THEN
1180 IF PG_DEBUG in ('Y', 'C') THEN
1181 arp_util.debug('get_bank_defaults: ' ||
1182 'EXCEPTION: arp_trx_defaults_3.get_bank_defaults()');
1183 arp_util.debug('get_bank_defaults: ' ||
1184 '------- parameters for get_bank_defaults ----');
1185 arp_util.debug('get_bank_defaults: ' || 'p_trx_date = ' || TO_CHAR(p_trx_date) );
1186 arp_util.debug('get_bank_defaults: ' || 'p_currency_code = ' || p_currency_code );
1187 arp_util.debug('get_bank_defaults: ' || 'p_paying_customer_id = ' || p_paying_customer_id );
1188 arp_util.debug('get_bank_defaults: ' || 'p_paying_site_use_id = ' || p_paying_site_use_id );
1189 arp_util.debug('get_bank_defaults: ' || 'p_bill_to_customer_id = ' || p_bill_to_customer_id );
1190 arp_util.debug('get_bank_defaults: ' || 'p_bill_to_site_use_id = ' || p_bill_to_site_use_id );
1191 END IF;
1192
1193 RAISE;
1194
1195 END get_bank_defaults;
1196
1197
1198 /*===========================================================================+
1199 | PROCEDURE |
1200 | get_bank_defaults |
1201 | |
1202 | DESCRIPTION |
1203 | Gets the default payment method. |
1204 | |
1205 | SCOPE - PUBLIC |
1206 | |
1207 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1208 | arp_util.debug |
1209 | |
1210 | ARGUMENTS : IN: |
1211 | p_trx_date |
1212 | p_currency_code |
1213 | p_paying_customer_id |
1214 | p_paying_site_use_id |
1215 | p_bill_to_customer_id |
1216 | p_bill_to_site_use_id |
1217 | OUT: |
1218 | p_customer_bank_account_id |
1219 | p_bank_account_num |
1220 | p_bank_name |
1221 | p_bank_branch_name |
1222 | p_bank_branch_id |
1223 | |
1224 | RETURNS : NONE |
1225 | |
1226 | NOTES |
1227 | |
1228 | MODIFICATION HISTORY |
1229 | 21-NOV-95 Charlie Tomberg Created |
1230 | 23-JUN-99 Ajay Pandit Modified the procedure for fixing |
1231 | bug no 913071 so that bank info. is |
1232 | not defaulted( rather assigned NULL |
1233 | values) when the payment method is |
1234 | MANUAL |
1235 +===========================================================================*/
1236
1237 PROCEDURE get_pay_method_and_bank_deflts(
1238 p_trx_date IN
1239 ra_customer_trx.trx_date%type,
1240 p_currency_code IN
1241 fnd_currencies.currency_code%type,
1242 p_paying_customer_id IN
1243 hz_cust_accounts.cust_account_id%type,
1244 p_paying_site_use_id IN
1245 hz_cust_site_uses.site_use_id%type,
1246 p_bill_to_customer_id IN
1247 hz_cust_accounts.cust_account_id%type,
1248 p_bill_to_site_use_id IN
1249 hz_cust_site_uses.site_use_id%type,
1250 p_payment_type_code IN
1251 ar_receipt_methods.payment_type_code%type,
1252 p_payment_method_name OUT NOCOPY
1253 ar_receipt_methods.name%type,
1254 p_receipt_method_id OUT NOCOPY
1255 ar_receipt_methods.receipt_method_id%type,
1256 p_creation_method_code OUT NOCOPY
1257 ar_receipt_classes.creation_method_code%type,
1258 p_customer_bank_account_id OUT NOCOPY
1259 ce_bank_accounts.bank_account_id%type,
1260 p_bank_account_num OUT NOCOPY
1261 ce_bank_accounts.bank_account_num%type,
1262 p_bank_name OUT NOCOPY
1263 ce_bank_branches_v.bank_name%type,
1264 p_bank_branch_name OUT NOCOPY
1265 ce_bank_branches_v.bank_branch_name%type,
1266 p_bank_branch_id OUT NOCOPY
1267 ce_bank_branches_v.branch_party_id%TYPE,
1268 p_trx_manual_flag IN VARCHAR2 DEFAULT 'N'
1269 ) IS
1270
1271
1272 BEGIN
1273
1274 IF PG_DEBUG in ('Y', 'C') THEN
1275 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'arp_trx_defaults_3.get_pay_method_and_bank_deflt()+');
1276 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'calling get_payment_method_default()');
1277 END IF;
1278 get_payment_method_default(
1279 p_trx_date,
1280 p_currency_code,
1281 p_paying_customer_id,
1282 p_paying_site_use_id,
1283 p_bill_to_customer_id,
1284 p_bill_to_site_use_id,
1285 p_payment_method_name,
1286 p_receipt_method_id,
1287 p_creation_method_code,
1288 p_trx_manual_flag /* Bug-3770337-PM */
1289 );
1290 /*Fix for Bug 913072 */
1291 IF (p_creation_method_code = 'MANUAL' or p_creation_method_code IS NULL) THEN /*Bug 3312212*/
1292 p_customer_bank_account_id := NULL;
1293 p_bank_account_num := NULL;
1294 p_bank_name := NULL;
1295 p_bank_branch_name := NULL;
1296 p_bank_branch_id := NULL;
1297 ELSE
1298
1299 IF PG_DEBUG in ('Y', 'C') THEN
1300 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'calling get_bank_defaults()');
1301 END IF;
1302 get_bank_defaults(
1303 p_trx_date,
1304 p_currency_code,
1305 p_paying_customer_id,
1306 p_paying_site_use_id,
1307 p_bill_to_customer_id,
1308 p_bill_to_site_use_id,
1309 pg_payment_type_code, --ajay bug 1081390
1310 p_customer_bank_account_id,
1311 p_bank_account_num,
1312 p_bank_name,
1313 p_bank_branch_name,
1314 p_bank_branch_id,
1315 p_trx_manual_flag /* Bug-3770337-PM */
1316 );
1317 END IF;
1318 IF PG_DEBUG in ('Y', 'C') THEN
1319 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'arp_trx_defaults_3.get_pay_method_and_bank_deflt()-');
1320 END IF;
1321
1322 EXCEPTION
1323 WHEN NO_DATA_FOUND THEN NULL;
1324 WHEN OTHERS THEN
1325 IF PG_DEBUG in ('Y', 'C') THEN
1326 arp_util.debug('get_pay_method_and_bank_deflts: ' ||
1327 'EXCEPTION: arp_trx_defaults_3.get_pay_method_and_bank_deflt()');
1328 arp_util.debug('get_pay_method_and_bank_deflts: ' ||
1329 '------- parameters for get_pay_method_and_bank_deflt ----');
1330 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'p_trx_date = ' || TO_CHAR(p_trx_date) );
1331 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'p_currency_code = ' || p_currency_code );
1332 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'p_paying_customer_id = ' || p_paying_customer_id );
1333 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'p_paying_site_use_id = ' || p_paying_site_use_id );
1334 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'p_bill_to_customer_id = ' || p_bill_to_customer_id );
1335 arp_util.debug('get_pay_method_and_bank_deflts: ' || 'p_bill_to_site_use_id = ' || p_bill_to_site_use_id );
1336 END IF;
1337
1338 RAISE;
1339
1340 END get_pay_method_and_bank_deflts;
1341
1342
1343 /*===========================================================================+
1344 | PROCEDURE |
1345 | get_remit_to_default |
1346 | |
1347 | DESCRIPTION |
1348 | Gets the default remit to address. |
1349 | |
1350 | SCOPE - PUBLIC |
1351 | |
1352 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1353 | arp_util.debug |
1354 | |
1355 | ARGUMENTS : IN: |
1356 | p_state |
1357 | p_postal_code |
1358 | p_country |
1359 | OUT: |
1360 | p_address_id |
1361 | p_address1 |
1362 | p_address2 |
1363 | p_address3 |
1364 | p_concatenated_address |
1365 | |
1366 | RETURNS : NONE |
1367 | |
1368 | NOTES |
1369 | |
1370 | MODIFICATION HISTORY |
1371 | 14-NOV-95 Charlie Tomberg Created |
1372 | |
1373 +===========================================================================*/
1374
1375 PROCEDURE get_remit_to_default(
1376 p_state IN hz_locations.state%type,
1377 p_postal_code IN hz_locations.postal_code%type,
1378 p_country IN hz_locations.country%type,
1379 p_address_id OUT NOCOPY NUMBER,
1380 p_address1 OUT NOCOPY hz_locations.address1%type,
1381 p_address2 OUT NOCOPY hz_locations.address2%type,
1382 p_address3 OUT NOCOPY varchar2,
1383 p_concatenated_address OUT NOCOPY varchar2
1384 ) IS
1385
1386 l_remit_to_address_rec address_rec_type;
1387
1388 BEGIN
1389
1390 IF PG_DEBUG in ('Y', 'C') THEN
1391 arp_util.debug('arp_trx_defaults_3.get_remit_to_default()+');
1392 END IF;
1393
1394
1395 BEGIN
1396 arp_trx_defaults_3.get_remit_to_address(
1397 p_state,
1398 p_country,
1399 p_postal_code,
1400 NULL,
1401 NULL,
1402 p_address_id,
1403 l_remit_to_address_rec
1404 );
1405
1406 p_address1 := l_remit_to_address_rec.address1;
1407
1408 p_address2 := l_remit_to_address_rec.address2;
1409
1410 p_address3 := l_remit_to_address_rec.city || ',' || ' ' ||
1411 NVL(l_remit_to_address_rec.state,
1412 l_remit_to_address_rec.province)
1413 ||' '|| l_remit_to_address_rec.postal_code||
1414 ' ' || l_remit_to_address_rec.country;
1415
1416 SELECT SUBSTRB( l_remit_to_address_rec.address1,
1417 1, 25) ||
1418 DECODE( l_remit_to_address_rec.address2,
1419 NULL, NULL,
1420 ', ') ||
1421 NVL(
1422 SUBSTRB( l_remit_to_address_rec.address2,
1423 1, 25),
1424 SUBSTRB( l_remit_to_address_rec.address1,
1425 26, 25)
1426 ) || ','||' '||
1427 l_remit_to_address_rec.city ||
1428 ','||' '||
1429 NVL( l_remit_to_address_rec.state,
1430 l_remit_to_address_rec.province) ||
1431 ' '|| l_remit_to_address_rec.postal_code ||
1432 ' '|| l_remit_to_address_rec.country
1433 INTO p_concatenated_address
1434 FROM dual;
1435
1436
1437 EXCEPTION
1438 WHEN NO_DATA_FOUND THEN
1439 p_address_id := NULL;
1440 p_address1 := NULL;
1441 p_address2 := NULL;
1442 p_address3 := NULL;
1443 p_concatenated_address := NULL;
1444
1445 WHEN OTHERS THEN RAISE;
1446 END;
1447
1448 IF PG_DEBUG in ('Y', 'C') THEN
1449 arp_util.debug('arp_trx_defaults_3.get_remit_to_default()-');
1450 END IF;
1451
1452 EXCEPTION
1453 WHEN NO_DATA_FOUND THEN NULL;
1454 WHEN OTHERS THEN
1455 IF PG_DEBUG in ('Y', 'C') THEN
1456 arp_util.debug('EXCEPTION: arp_trx_defaults_3.get_remit_to_default()');
1457 arp_util.debug('------- parameters for get_remit_to_default ----');
1458 arp_util.debug('get_remit_to_default: ' || 'p_state = ' || p_state);
1459 arp_util.debug('get_remit_to_default: ' || 'p_postal_code = ' || p_postal_code);
1460 arp_util.debug('get_remit_to_default: ' || 'p_countrye = ' || p_country);
1461 END IF;
1462
1463 RAISE;
1464
1465 END get_remit_to_default;
1466
1467 /*===========================================================================+
1468 | PROCEDURE |
1469 | get_term_default |
1470 | |
1471 | DESCRIPTION |
1472 | Determines and returns the term default and realted other items. |
1473 | The term defaults to the first valid term found in the following list: |
1474 | - The customer's bill to site use record |
1475 | - The customer's site level profile |
1476 | - The customer's customer level profile |
1477 | - The transaction type |
1478 | |
1479 | SCOPE - PRIVATE |
1480 | |
1481 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1482 | arp_util.debug |
1483 | |
1484 | ARGUMENTS : IN: |
1485 | p_term_id |
1486 | p_type_term_id |
1487 | p_type_term_name |
1488 | p_customer_id |
1489 | p_site_use_id |
1490 | p_trx_date |
1491 | p_class |
1492 | OUT: |
1493 | p_default_term_id |
1494 | p_default_term_name |
1495 | p_number_of_due_dates |
1496 | p_term_due_date |
1497 | |
1498 | RETURNS : NONE |
1499 | |
1500 | NOTES |
1501 | |
1502 | MODIFICATION HISTORY |
1503 | 04-NOV-95 Charlie Tomberg Created |
1504 | |
1505 +===========================================================================*/
1506
1507 PROCEDURE get_term_default(
1508 p_term_id IN ra_terms.term_id%type,
1509 p_type_term_id IN ra_terms.term_id%type,
1510 p_type_term_name IN ra_terms.name%type,
1511 p_customer_id IN hz_cust_accounts.cust_account_id%type,
1512 p_site_use_id IN hz_cust_site_uses.site_use_id%type,
1513 p_trx_date IN ra_customer_trx.trx_date%type,
1514 p_class IN ra_cust_trx_types.type%type,
1515 p_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%type,
1516 p_default_term_id OUT NOCOPY ra_terms.term_id%type,
1517 p_default_term_name OUT NOCOPY ra_terms.name%type,
1518 p_number_of_due_dates OUT NOCOPY number,
1519 p_term_due_date OUT NOCOPY
1520 ra_customer_trx.term_due_date%type
1521 ) IS
1522
1523 l_number_of_due_dates number;
1524 l_term_due_date ra_customer_trx.term_due_date%type;
1525 l_cust_term_id ra_terms.term_id%type;
1526 l_cust_term_name ra_terms.name%type;
1527 l_org_id ra_customer_trx.org_id%type;
1528 l_billing_cycle_id ra_terms.billing_cycle_id%type; -- Bug 7582592
1529
1530 BEGIN
1531
1532 IF PG_DEBUG in ('Y', 'C') THEN
1533 arp_util.debug('arp_trx_defaults_3.get_term_default()+');
1534 END IF;
1535
1536 /*------------------------------------------------------+
1537 | If the term has already been specified, do nothing |
1538 +------------------------------------------------------*/
1539
1540 IF ( p_term_id IS NULL )
1541 THEN
1542
1543 /*---------------------------------------------------+
1544 | First try to default the term from the customer |
1545 +---------------------------------------------------*/
1546
1547 IF (
1548 p_customer_id IS NOT NULL
1549 AND p_site_use_id IS NOT NULL
1550 )
1551 THEN
1552
1553 IF ar_bfb_utils_pvt.get_bill_level(p_customer_id) in ('A','S') then
1554
1555 -- R12:BFB
1556
1557 select org_id
1558 into l_org_id
1559 from ra_cust_trx_types
1560 where cust_trx_type_id = p_cust_trx_type_id;
1561
1562 l_cust_term_name := NULL;
1563 l_number_of_due_dates := NULL;
1564 l_term_due_date := to_date(NULL);
1565 l_cust_term_id := ar_bfb_utils_pvt.get_default_term(
1566 p_cust_trx_type_id,
1567 p_trx_date,
1568 l_org_id,
1569 p_site_use_id,
1570 p_customer_id);
1571
1572 if l_cust_term_id < 0 then
1573 -- Error in retrieving BFB term
1574 if l_cust_term_id = -91 then
1575 fnd_message.set_name('AR','AR_BFB_TERM_BILL_LEVEL_NULL');
1576 elsif l_cust_term_id = -92 then
1577 fnd_message.set_name('AR','AR_BFB_TERM_BILL_LEVEL_WRONG');
1578 elsif l_cust_term_id = -93 then
1579 fnd_message.set_name('AR','AR_BFB_TERM_MISSING_AT_ACCT');
1580 elsif l_cust_term_id = -94 then
1581 fnd_message.set_name('AR','AR_BFB_TERM_NO_DEFAULT');
1582 elsif l_cust_term_id = -95 then
1583 fnd_message.set_name('AR','AR_BFB_TERM_NO_BFB_DEFAULT');
1584 end if;
1585 --app_exception.raise_exception;
1586 else
1587 begin
1588 select name, billing_cycle_id
1589 into l_cust_term_name, l_billing_cycle_id
1590 from ra_terms
1591 where term_id = l_cust_term_id;
1592 exception
1593 when no_data_found then
1594 l_cust_term_name := null;
1595 end;
1596
1597 /* Bug 7582592 */
1598 IF l_billing_cycle_id is null THEN
1599 l_term_due_date := arpt_sql_func_util.get_First_Due_Date(l_cust_term_id,
1600 p_trx_date);
1601 END IF;
1602
1603 end if;
1604
1605 ELSE
1606 -- customer is not BFB-enabled
1607 BEGIN
1608 SELECT tl.term_id,
1609 NVL(
1610 t_su.name,
1611 NVL(
1612 t_cp1.name,
1613 t_cp2.name
1614 )
1615 ),
1616 arpt_sql_func_util.get_First_Due_Date( tl.term_id,
1617 p_trx_date),
1618 count(*)
1619 INTO l_cust_term_id,
1620 l_cust_term_name,
1621 l_term_due_date,
1622 l_number_of_due_dates
1623 FROM ra_terms t_su,
1624 ra_terms t_cp1,
1625 ra_terms t_cp2,
1626 ra_terms_lines tl,
1627 hz_customer_profiles cp1,
1628 hz_customer_profiles cp2,
1629 hz_cust_site_uses su
1630 WHERE p_customer_id = cp1.cust_account_id(+)
1631 AND su.site_use_id = p_site_use_id
1632 AND cp2.cust_account_id = p_customer_id
1633 AND su.site_use_id = cp1.site_use_id(+)
1634 AND cp2.site_use_id IS NULL
1635 AND su.payment_term_id = t_su.term_id(+)
1636 AND cp1.standard_terms = t_cp1.term_id(+)
1637 AND cp2.standard_terms = t_cp2.term_id(+)
1638 AND NVL(
1639 t_su.term_id,
1640 NVL(
1641 t_cp1.term_id,
1642 t_cp2.term_id
1643 )
1644 ) = tl.term_id
1645 AND p_trx_date BETWEEN t_su.start_date_active(+)
1646 AND NVL(t_su.end_date_active(+),
1647 p_trx_date)
1648 AND p_trx_date BETWEEN t_cp1.start_date_active(+)
1649 AND NVL(t_cp1.end_date_active(+),
1650 p_trx_date)
1651 AND p_trx_date BETWEEN t_cp2.start_date_active(+)
1652 AND NVL(t_cp2.end_date_active(+),
1653 p_trx_date)
1654 GROUP BY tl.term_id,
1655 t_su.name,
1656 t_cp1.name,
1657 t_cp2.name
1658 -- Guarantees cannot have split term terms
1659 HAVING 1 = DECODE(p_class,
1660 'GUAR', COUNT(*),
1661 1 );
1662
1663 EXCEPTION
1664 WHEN NO_DATA_FOUND THEN NULL;
1665 WHEN OTHERS THEN RAISE;
1666 END;
1667 END IF;
1668
1669 END IF;
1670
1671 /*----------------------------------------------------------------+
1672 | If a default has been found copy the values to the out NOCOPY params |
1673 | Otherwise, try to default from the transaction type. |
1674 +----------------------------------------------------------------*/
1675
1676 IF (l_cust_term_id IS NOT NULL)
1677 THEN
1678 p_default_term_id := l_cust_term_id;
1679 p_default_term_name := l_cust_term_name;
1680 p_number_of_due_dates := l_number_of_due_dates;
1681 p_term_due_date := l_term_due_date;
1682
1683 ELSIF (p_type_term_id IS NOT NULL)
1684 THEN
1685
1686 SELECT COUNT(*),
1687 arpt_sql_func_util.get_First_Due_Date(
1688 p_type_term_id,
1689 p_trx_date)
1690 INTO l_number_of_due_dates,
1691 l_term_due_date
1692 FROM ra_terms_lines
1693 WHERE term_id = p_type_term_id;
1694
1695 -- Guarantees cannot have split term terms
1696
1697 IF (
1698 p_class = 'GUAR'
1699 AND l_number_of_due_dates > 1
1700 )
1701 THEN
1702 p_default_term_id := NULL;
1703 p_number_of_due_dates := NULL;
1704 p_number_of_due_dates := NULL;
1705 p_term_due_date := NULL;
1706 ELSE
1707 p_default_term_id := p_type_term_id;
1708 p_default_term_name := p_type_term_name;
1709 p_number_of_due_dates := l_number_of_due_dates;
1710 p_term_due_date := l_term_due_date;
1711 END IF;
1712 /* Portion added for Bug 665567 : Please note that
1713 this portion of code is written to be executed if
1714 get_term_default is called from client side directly*/
1715 ELSIF (p_type_term_id IS NULL)
1716 THEN
1717 SELECT
1718 rat.name,
1719 rat.term_id
1720 INTO l_cust_term_name,
1721 l_cust_term_id
1722 FROM ra_terms rat,
1723 ra_cust_trx_types ctt
1724 WHERE ctt.cust_trx_type_id=p_cust_trx_type_id
1725 AND ctt.default_term=rat.term_id(+);
1726
1727 IF (l_cust_term_id is NOT NULL)
1728 THEN
1729 SELECT COUNT(*),
1730 arpt_sql_func_util.get_First_Due_Date(
1731 l_cust_term_id,
1732 p_trx_date)
1733 INTO l_number_of_due_dates,
1734 l_term_due_date
1735 FROM ra_terms_lines
1736 WHERE term_id = l_cust_term_id;
1737
1738 p_default_term_id := l_cust_term_id;
1739 p_default_term_name := l_cust_term_name;
1740 p_number_of_due_dates := l_number_of_due_dates;
1741 p_term_due_date := l_term_due_date;
1742 END IF;
1743
1744
1745 END IF;
1746
1747 END IF;
1748
1749 IF PG_DEBUG in ('Y', 'C') THEN
1750 arp_util.debug('arp_trx_defaults_3.get_term_default()-');
1751 END IF;
1752
1753 EXCEPTION
1754 WHEN NO_DATA_FOUND THEN NULL;
1755 WHEN OTHERS THEN
1756 IF PG_DEBUG in ('Y', 'C') THEN
1757 arp_util.debug('EXCEPTION: arp_trx_defaults_3.get_term_default()');
1758 END IF;
1759 RAISE;
1760
1761 END get_term_default;
1762
1763
1764 /*===========================================================================+
1765 | PROCEDURE |
1766 | Get_Additional_Customer_Info |
1767 | |
1768 | DESCRIPTION |
1769 | Gets additional information about a customer and site. |
1770 | |
1771 | SCOPE - PUBLIC |
1772 | |
1773 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1774 | arp_util.debug |
1775 | |
1776 | ARGUMENTS : IN: |
1777 | p_customer_id |
1778 | p_site_use_id |
1779 | p_invoice_currency_code |
1780 | p_previous_customer_trx_id |
1781 | p_ct_prev_initial_cust_trx_id |
1782 | p_trx_date |
1783 | p_code_combination_id_gain |
1784 | OUT: |
1785 | p_override_terms |
1786 | p_commitments_exist_flag |
1787 | IN/ OUT: |
1788 | None |
1789 | |
1790 | RETURNS : NONE |
1791 | |
1792 | NOTES |
1793 | |
1794 | MODIFICATION HISTORY |
1795 | 19-JUN-96 Charlie Tomberg Created |
1796 | 14-NOV-97 KTANG removed call to get_commitments_exist_flag|
1797 | |
1798 +===========================================================================*/
1799
1800 PROCEDURE Get_Additional_Customer_Info(
1801 p_customer_id IN number,
1802 p_site_use_id IN number,
1803 p_invoice_currency_code IN varchar2,
1804 p_previous_customer_trx_id IN number,
1805 p_ct_prev_initial_cust_trx_id IN number,
1806 p_trx_date IN date,
1807 p_code_combination_id_gain IN number,
1808 p_override_terms OUT NOCOPY varchar2,
1809 p_commitments_exist_flag OUT NOCOPY varchar2,
1810 p_agreements_exist_flag OUT NOCOPY varchar2)
1811 IS
1812
1813 l_override_terms hz_customer_profiles.override_terms%type;
1814 l_commitments_exist_flag varchar2(1);
1815 l_agreements_exist_flag varchar2(1);
1816
1817 BEGIN
1818
1819 arp_util.debug('arp_trx_defaults_3.Get_Additional_Customer_Info()+');
1820
1821
1822 l_override_terms :=
1823 arpt_sql_func_util.get_override_terms(p_customer_id,
1824 p_site_use_id);
1825
1826 /* Bug 551173: Commented out NOCOPY for performance reasons
1827
1828 l_commitments_exist_flag :=
1829 arpt_sql_func_util.get_commitments_exist_flag(
1830 p_customer_id,
1831 p_invoice_currency_code,
1832 p_previous_customer_trx_id,
1833 p_trx_date,
1834 p_ct_prev_initial_cust_trx_id,
1835 p_code_combination_id_gain,
1836 pg_base_curr_code);
1837 */
1838
1839 l_commitments_exist_flag := 'Y';
1840
1841
1842 l_agreements_exist_flag :=
1843 arpt_sql_func_util.get_agreements_exist_flag(
1844 p_customer_id,
1845 p_trx_date);
1846
1847 p_override_terms := l_override_terms;
1848 p_commitments_exist_flag := l_commitments_exist_flag;
1849 p_agreements_exist_flag := l_agreements_exist_flag;
1850
1851 arp_util.debug('p_customer_id = ' ||
1852 TO_CHAR(p_customer_id));
1853 arp_util.debug('p_site_use_id = ' ||
1854 TO_CHAR(p_site_use_id));
1855 arp_util.debug('p_invoice_currency_code = ' ||
1856 p_invoice_currency_code);
1857 arp_util.debug('p_previous_customer_trx_id = ' ||
1858 TO_CHAR(p_previous_customer_trx_id));
1859 arp_util.debug('p_ct_prev_initial_cust_trx_id = ' ||
1860 TO_CHAR(p_ct_prev_initial_cust_trx_id));
1861 arp_util.debug('p_trx_date = ' ||
1862 TO_CHAR(p_trx_date));
1863 arp_util.debug('p_code_combination_id_gain = ' ||
1864 TO_CHAR(p_code_combination_id_gain));
1865
1866 arp_util.debug('p_override_terms = ' || l_override_terms);
1867 arp_util.debug('p_commitments_exist_flag = ' ||
1868 l_commitments_exist_flag);
1869 arp_util.debug('p_agreements_exist_flag = ' ||
1870 l_agreements_exist_flag);
1871
1872 arp_util.debug('arp_trx_defaults_3.Get_Additional_Customer_Info()-');
1873
1874 EXCEPTION
1875 WHEN OTHERS THEN
1876 arp_util.debug('EXCEPTION : '||
1877 'arp_trx_defaults_3.Get_Additional_Customer_Info()-');
1878
1879 RAISE;
1880 END;
1881
1882
1883 /*===========================================================================+
1884 | PROCEDURE |
1885 | Get_Payment_Channel_name |
1886 | |
1887 | DESCRIPTION |
1888 | Get payment channel name based on payment channel code. |
1889 | |
1890 | SCOPE - PUBLIC |
1891 | |
1892 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1893 | arp_util.debug |
1894 | |
1895 | ARGUMENTS : IN: |
1896 | p_payment_channel_code |
1897 | OUT: |
1898 | NONE |
1899 | |
1900 | RETURNS : payment_channel_name if exists |
1901 | |
1902 | NOTES |
1903 | |
1904 | MODIFICATION HISTORY |
1905 | 08-Aug-05 Surendra Rajan Created |
1906 | |
1907 +===========================================================================*/
1908
1909 FUNCTION get_payment_channel_name(
1910 p_payment_channel_code IN
1911 ar_receipt_methods.payment_channel_code%type
1912 ) RETURN VARCHAR2 IS
1913
1914 l_payment_channel_name iby_fndcpt_all_pmt_channels_v.payment_channel_name%type; --corrected the field name Bug5367658
1915 BEGIN
1916 IF PG_DEBUG in ('Y', 'C') THEN
1917 arp_standard.debug('arp_trx_defaults_3.get_payment_channel_name()+');
1918 END IF;
1919
1920 Select payment_channel_name
1921 into l_payment_channel_name
1922 from iby_fndcpt_all_pmt_channels_v pmt_cv
1923 where pmt_cv.instrument_type not in ('MANUAL', 'PINLESSDEBITCARD')
1924 and pmt_cv.payment_channel_code = p_payment_channel_code;
1925
1926 RETURN l_payment_channel_name ;
1927 EXCEPTION
1928 WHEN NO_DATA_FOUND
1929 THEN
1930 IF PG_DEBUG in ('Y', 'C') THEN
1931 arp_util.debug('get_payment_channel_name: ' || ' NOT FOUND ');
1932 END IF;
1933 RETURN(NULL);
1934
1935 WHEN OTHERS THEN
1936 IF PG_DEBUG in ('Y', 'C') THEN
1937 arp_util.debug('get_payment_channel_name: ' ||
1938 'EXCEPTION: arp_trx_defaults_3.get_payment_channel_name()');
1939 arp_util.debug('get_payment_channel_name: ' || '------- parameters for get_payment_channel_name----');
1940 arp_util.debug('get_payment_channel_name: ' || 'p_payment_channel_code = ' || p_payment_channel_code);
1941 END IF;
1942
1943 RAISE;
1944
1945 END get_payment_channel_name;
1946 /*===========================================================================+
1947 | PROCEDURE |
1948 | Get_Party_id |
1949 | |
1950 | DESCRIPTION |
1951 | Get party id based on the customer account id. |
1952 | |
1953 | SCOPE - PUBLIC |
1954 | |
1955 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1956 | arp_util.debug |
1957 | |
1958 | ARGUMENTS : IN: |
1959 | p_cust_account_id |
1960 | OUT: |
1961 | NONE |
1962 | |
1963 | RETURNS : party_id if exists |
1964 | |
1965 | NOTES |
1966 | |
1967 | MODIFICATION HISTORY |
1968 | 08-Aug-05 Surendra Rajan Created |
1969 | |
1970 +===========================================================================*/
1971
1972 FUNCTION get_party_id (
1973 p_cust_account_id IN
1974 hz_cust_accounts.cust_account_id%type
1975 ) RETURN NUMBER IS
1976 l_party_id number;
1977 BEGIN
1978 IF PG_DEBUG in ('Y', 'C') THEN
1979 arp_standard.debug('arp_trx_defaults_3.get_party_id()+');
1980 END IF;
1981
1982 Select party_id
1983 into l_party_id
1984 from hz_cust_accounts
1985 where cust_account_id = p_cust_account_id ;
1986 RETURN l_party_id;
1987 EXCEPTION
1988 WHEN NO_DATA_FOUND
1989 THEN
1990 IF PG_DEBUG in ('Y', 'C') THEN
1991 arp_util.debug('get_party_id: ' || ' NOT FOUND ');
1992 END IF;
1993 RETURN(NULL);
1994
1995 WHEN OTHERS THEN
1996 IF PG_DEBUG in ('Y', 'C') THEN
1997 arp_util.debug('get_party_id : ' ||
1998 'EXCEPTION: arp_trx_defaults_3.get_party_id()');
1999 arp_util.debug('get_party_id: ' || '------- parameters for get_party_id ----');
2000 arp_util.debug('get_party_id: ' || 'p_cust_account_id = ' || p_cust_account_id);
2001 END IF;
2002
2003 RAISE;
2004
2005 END get_party_id;
2006 /*===========================================================================+
2007 | PROCEDURE |
2008 | Get_payment_instrument |
2009 | |
2010 | DESCRIPTION |
2011 | Gets instrument information from oracle payments based on the payment |
2012 | channel code and the payment trxn extension id. |
2013 | SCOPE - PUBLIC |
2014 | |
2015 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2016 | arp_util.debug |
2017 | |
2018 | ARGUMENTS : IN: |
2019 | p_payment_trxn_extension_id |
2020 | OUT: |
2021 | NONE |
2022 | |
2023 | RETURNS : Instrument number if exists |
2024 | |
2025 | NOTES |
2026 | |
2027 | MODIFICATION HISTORY |
2028 | 08-Aug-05 Surendra Rajan Created |
2029 | 04-AUG-06 Gyanajyothi Modified to include Bills receivable |
2030 | Bug 5435941 |
2031 +===========================================================================*/
2032
2033 FUNCTION get_payment_instrument(
2034 p_payment_trxn_extension_id IN
2035 ra_customer_trx.payment_trxn_extension_id%type,
2036 p_payment_channel_code IN
2037 ar_receipt_methods.payment_channel_code%type
2038 ) RETURN VARCHAR2 IS
2039 l_instrument iby_trxn_extensions_v.card_number%type;
2040 l_instrument_type iby_trxn_extensions_v.instrument_type%type;
2041 BEGIN
2042 IF PG_DEBUG in ('Y', 'C') THEN
2043 arp_standard.debug('arp_trx_defaults_3.get_payment_instrument()+');
2044 END IF;
2045
2046 IF p_payment_channel_code = 'CREDIT_CARD'
2047 Then
2048 l_instrument_type := 'CREDITCARD';
2049 ELSIF p_payment_channel_code in ('BANK_ACCT_XFER','BILLS_RECEIVABLE')
2050 Then
2051 l_instrument_type := 'BANKACCOUNT';
2052 END IF;
2053
2054 IF PG_DEBUG in ('Y', 'C') THEN
2055 arp_util.debug('p_payment_trxn_extension_id: ' || p_payment_trxn_extension_id);
2056 arp_util.debug('l_instrument_type: ' || l_instrument_type);
2057 END IF;
2058
2059 SELECT decode (nvl(u.instrument_type, p.instrument_type)
2060 , 'BANKACCOUNT', b.masked_bank_account_num
2061 , 'CREDITCARD', c.masked_cc_number
2062 , NULL) instrument
2063 into l_instrument
2064 FROM
2065 iby_creditcard c,
2066 iby_ext_bank_accounts b,
2067 iby_fndcpt_pmt_chnnls_b p,
2068 iby_fndcpt_pmt_chnnls_tl pt,
2069 iby_fndcpt_tx_extensions x,
2070 iby_pmt_instr_uses_all u,
2071 fnd_application a
2072 WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
2073 AND (DECODE(u.instrument_type, 'CREDITCARD',u.instrument_id, NULL) = c.instrid(+))
2074 AND (DECODE(u.instrument_type, 'BANKACCOUNT',u.instrument_id, NULL) = b.ext_bank_account_id(+))
2075 AND (x.payment_channel_code = p.payment_channel_code)
2076 AND (x.origin_application_id = a.application_id)
2077 AND (P.payment_channel_code = pt.payment_channel_code)
2078 AND (PT.LANGUAGE = USERENV('LANG'))
2079 AND trxn_extension_id = p_payment_trxn_extension_id
2080 AND nvl(u.instrument_type, p.instrument_type) = l_instrument_type;
2081
2082 IF PG_DEBUG in ('Y', 'C') THEN
2083 arp_util.debug('l_instrument: ' || l_instrument);
2084 END IF;
2085
2086 RETURN l_instrument;
2087 EXCEPTION
2088 WHEN NO_DATA_FOUND
2089 THEN
2090 IF PG_DEBUG in ('Y', 'C') THEN
2091 arp_util.debug('get_payment_instrument: ' || ' NOT FOUND ');
2092 END IF;
2093 RETURN(NULL);
2094
2095 WHEN TOO_MANY_ROWS
2096 THEN
2097 IF PG_DEBUG in ('Y', 'C') THEN
2098 arp_util.debug('get_payment_instrument: ' || 'TWO MANY ROWS FOUND ');
2099 END IF;
2100 RAISE;
2101
2102 WHEN OTHERS THEN
2103 IF PG_DEBUG in ('Y', 'C') THEN
2104 arp_util.debug('get_payment_instrument: ' ||
2105 'EXCEPTION: arp_trx_defaults_3.get_payment_instrument() '|| sqlerrm);
2106 arp_util.debug('get_payment_instrument: ' || '------- parameters ----');
2107 arp_util.debug('get_payment_instrument: ' || 'p_payment_channel_code= ' ||
2108 p_payment_channel_code);
2109 arp_util.debug('get_payment_instrument: ' || 'p_payment_trxn_extension_id= ' ||
2110 p_payment_trxn_extension_id);
2111 END IF;
2112
2113 RAISE;
2114 END get_payment_instrument;
2115
2116 /*===========================================================================+
2117 | PROCEDURE |
2118 | Get_BR_Bank_Defaults |
2119 | |
2120 | DESCRIPTION |
2121 | Gets BR Bank information from oracle payments based on the payment |
2122 | channel code and the payment trxn extension id. |
2123 | SCOPE - PUBLIC |
2124 | |
2125 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2126 | arp_util.debug |
2127 | |
2128 | ARGUMENTS : IN: |
2129 | p_payment_trxn_extension_id |
2130 | OUT: p_bank_name,p_branch_name,p_instr_assign_id |
2131 | p_instr_number |
2132 | |
2133 | RETURNS : Instrument details if exists |
2134 | |
2135 | NOTES |
2136 | |
2137 | MODIFICATION HISTORY |
2138 | 13-Feb-06 Gyanajyothi G Created : Bug 4778839 |
2139 | |
2140 +===========================================================================*/
2141
2142
2143 PROCEDURE get_br_bank_defaults( p_payment_trxn_extension_id IN
2144 ra_customer_trx.payment_trxn_extension_id%type,
2145 p_payment_channel_code IN
2146 ar_receipt_methods.payment_channel_code%type,
2147 p_bank_name OUT NOCOPY
2148 iby_trxn_extensions_v.bank_name%type,
2149 p_branch_name OUT NOCOPY
2150 iby_trxn_extensions_v.bank_branch_name%type,
2151 p_instr_assign_id OUT NOCOPY
2152 iby_trxn_extensions_v.instr_assignment_id%type,
2153 p_instr_number OUT NOCOPY
2154 iby_trxn_extensions_v.account_number%type)
2155 IS
2156
2157 BEGIN
2158 select bank_name,bank_branch_name,instr_assignment_id,account_number
2159 into p_bank_name,p_branch_name,p_instr_assign_id,p_instr_number
2160 from iby_trxn_extensions_v
2161 where trxn_extension_id = p_payment_trxn_extension_id
2162 and payment_channel_code = p_payment_channel_code;
2163
2164 exception
2165 when no_data_found then
2166 arp_util.debug('ar_br_bank_defaults - No data found');
2167 when others then
2168 arp_util.debug('ar_br_bank_defaults - Others');
2169 End;
2170
2171
2172 --Bug 5507178 To Default the Instrument Details for a transaction
2173
2174 PROCEDURE get_instr_defaults(p_org_id IN ra_customer_trx.org_id%type,
2175 p_paying_customer_id IN ra_customer_trx.paying_customer_id%type,
2176 p_paying_site_use_id IN iby_fndcpt_payer_assgn_instr_v.acct_site_use_id%type,
2177 p_instrument_type IN iby_fndcpt_payer_assgn_instr_v.instrument_type%type,
2178 p_currency_code IN iby_fndcpt_payer_assgn_instr_v.currency_code%type ,
2179 p_instrument_assignment_id OUT NOCOPY iby_trxn_extensions_v.instr_assignment_id%type
2180
2181 )
2182 IS
2183 l_instr_assignments IBY_FNDCPT_SETUP_PUB.pmtinstrassignment_tbl_type;
2184 l_payer IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
2185 l_payer_equivalency VARCHAR2(500);
2186 l_conditions IBY_FNDCPT_COMMON_PUB.trxncontext_rec_type;
2187 l_result_limit IBY_FNDCPT_COMMON_PUB.resultlimit_rec_type;
2188
2189
2190 l_response IBY_FNDCPT_COMMON_PUB.result_rec_type;
2191 l_return_status VARCHAR2(4000);
2192 l_msg_count NUMBER;
2193 l_msg_data VARCHAR2(4000);
2194
2195 -- This values based on global variables from FND_API G_TRUE and G_FALSE;
2196 l_true VARCHAR2(1) := 'T';
2197 l_false VARCHAR2(1) := 'F';
2198
2199
2200 BEGIN
2201 l_payer.payment_function := 'CUSTOMER_PAYMENT';
2202 l_payer.party_id := arp_trx_defaults_3.get_party_Id(p_paying_customer_id);
2203 l_payer.org_type := 'OPERATING_UNIT';
2204 l_payer.org_id := p_org_id;
2205 l_payer.cust_account_id := p_paying_customer_id;
2206 l_payer.account_site_id := p_paying_site_use_id;
2207
2208 l_payer_equivalency := 'UPWARD';--Verify this
2209
2210 l_conditions.application_id := 222;
2211 -- l_conditions.transaction_type := p_instrument_type ; --:IBY_TRXN_PARAMS.transaction_type;
2212 l_conditions.Payment_InstrType := p_instrument_type ;
2213 l_conditions.org_type := 'OPERATING_UNIT';
2214 l_conditions.org_id := p_org_id;
2215 l_conditions.currency_code := p_currency_code;
2216 l_conditions.payment_amount := null ; --:IBY_TRXN_PARAMS.payment_amount;
2217
2218 -- return only the default payment instrument based on the priority
2219 l_result_limit.default_flag := 'Y';
2220
2221
2222 -- Call funds capture PL/SQL API to query applicable payment instrument assignments
2223 IBY_FNDCPT_SETUP_PUB.get_trxn_appl_instr_assign(
2224 p_api_version => 1.0,
2225 p_init_msg_list => l_false,
2226 x_return_status => l_return_status,
2227 x_msg_count => l_msg_count,
2228 x_msg_data => l_msg_data,
2229 p_payer => l_payer,
2230 p_payer_equivalency => l_payer_equivalency,
2231 p_conditions => l_conditions,
2232 p_result_limit => l_result_limit,
2233 x_assignments => l_instr_assignments,
2234 x_response => l_response);
2235
2236
2237 IF (l_return_status <> 'S') THEN
2238 arp_util.debug('Unable to default the Instrument Details');
2239
2240 ELSE
2241 /*Bug6135223*/
2242 If l_instr_assignments.count = 0 Then
2243 p_instrument_assignment_id := Null;
2244 Else
2245 p_instrument_assignment_id := l_instr_assignments(l_instr_assignments.FIRST).assignment_id;
2246 End If;
2247 END IF;
2248
2249 END;
2250
2251 /*---------------------------------------------+
2252 | Package initialization section. |
2253 +---------------------------------------------*/
2254 PROCEDURE init IS
2255 BEGIN
2256
2257 pg_text_dummy := arp_ct_pkg.get_text_dummy;
2258 pg_flag_dummy := arp_ct_pkg.get_flag_dummy;
2259 pg_number_dummy := arp_ct_pkg.get_number_dummy;
2260 pg_date_dummy := arp_ct_pkg.get_date_dummy;
2261
2262 pg_base_curr_code := arp_global.functional_currency;
2263 pg_base_precision := arp_global.base_precision;
2264 pg_base_min_acc_unit := arp_global.base_min_acc_unit;
2265 pg_set_of_books_id :=
2266 arp_trx_global.system_info.system_parameters.set_of_books_id;
2267 END init;
2268
2269 BEGIN
2270 init;
2271 END ARP_TRX_DEFAULTS_3;