[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_CREDIT_UTIL
Source
1 PACKAGE BODY ARP_PROCESS_CREDIT_UTIL AS
2 /* $Header: ARTECMUB.pls 120.12.12010000.1 2008/07/24 16:55:42 appldev ship $ */
3
4 pg_set_of_books_id ar_system_parameters.set_of_books_id%type;
5
6 /*===========================================================================+
7 | FUNCTION |
8 | get_commitment_adjustments |
9 | |
10 | DESCRIPTION |
11 | Procedure to get the commitment adjustment amount for a child invoice |
12 | |
13 | SCOPE - PRIVATE |
14 | |
15 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
16 | arp_util.debug |
17 | |
18 | ARGUMENTS : IN: |
19 | p_ct_id |
20 | p_commit_ct_id |
21 | |
22 | RETURNS : amount - NUMBER |
23 | |
24 | NOTES |
25 | |
26 | MODIFICATION HISTORY |
27 | 12-JUL-95 Subash Chadalavada Created |
28 | 18-JUN-01 Michael Raymond 1483656 - added new procedure
29 | called get_commitment_adjustments
30 | to retrv line, tax, and freight bal.
31 +===========================================================================*/
32 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
33
34 FUNCTION get_commitment_adjustments(
35 p_ct_id IN ra_customer_trx.customer_trx_id%type,
36 p_commit_ct_id IN ra_customer_trx.customer_trx_id%type) RETURN number
37 IS
38 l_amount number;
39 BEGIN
40 IF PG_DEBUG in ('Y', 'C') THEN
41 arp_util.debug('arp_process_credit_util.get_commitment_adjustments()+');
42 END IF;
43
44 SELECT sum(amount)
45 INTO l_amount
46 FROM ar_adjustments adj,
47 ra_cust_trx_types commit_ctt,
48 ra_customer_trx commit_trx
49 WHERE commit_ctt.cust_trx_type_id = commit_trx.cust_trx_type_id
50 AND commit_trx.customer_trx_id = p_commit_ct_id
51 AND commit_ctt.type = 'DEP'
52 AND adj.customer_trx_id = p_ct_id
53 AND adj.adjustment_type = 'C';
54
55 return(l_amount);
56
57 IF PG_DEBUG in ('Y', 'C') THEN
58 arp_util.debug('arp_process_credit_util.get_commitment_adjustments()-');
59 END IF;
60
61 EXCEPTION
62
63 WHEN OTHERS THEN
64 IF PG_DEBUG in ('Y', 'C') THEN
65 arp_util.debug('get_commitment_adjustments: ' || 'EXCEPTION : '||
66 'arp_process_credit_util.get_commitment_adjustments');
67 arp_util.debug('get_commitment_adjustments: ' || 'p_ct_id : '||p_ct_id);
68 arp_util.debug('get_commitment_adjustments: ' || 'p_commit_ct_id : '||p_commit_ct_id);
69 END IF;
70 RAISE;
71 END get_commitment_adjustments;
72
73 /*===========================================================================+
74 | PROCEDURE |
75 | get_commitment_adj_detail |
76 | |
77 | DESCRIPTION |
78 | Procedure to get the commitment adjustment amount (line, tax, and frt |
79 | for a child invoice |
80 | |
81 | SCOPE - PRIVATE |
82 | |
83 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
84 | arp_util.debug |
85 | |
86 | ARGUMENTS : IN: |
87 | p_ct_id |
88 | p_commit_ct_id |
89 | p_amount
90 | p_line_amount
91 | p_tax_amount
92 | p_freight_amount
93 |
94 | RETURNS : |
95 | |
96 | NOTES |
97 | |
98 | MODIFICATION HISTORY |
99 | 18-JUN-01 Michael Raymond 1483656 - added new procedure
100 | to retrieve commitment adjustments
101 | (line, tax, and freight)
102 +===========================================================================*/
103
104 PROCEDURE get_commitment_adj_detail(
105 p_ct_id IN ra_customer_trx.customer_trx_id%type,
106 p_commit_ct_id IN ra_customer_trx.customer_trx_id%type,
107 p_amount IN OUT NOCOPY number,
108 p_line_amount IN OUT NOCOPY number,
109 p_tax_amount IN OUT NOCOPY number,
110 p_freight_amount IN OUT NOCOPY number)
111 IS
112 BEGIN
113 IF PG_DEBUG in ('Y', 'C') THEN
114 arp_util.debug('arp_process_credit_util.get_commitment_adj_detail()+');
115 END IF;
116
117 SELECT sum(amount), sum(NVL(line_adjusted,0)),
118 sum(NVL(tax_adjusted,0)), sum(NVL(freight_adjusted,0))
119 INTO p_amount,
120 p_line_amount,
121 p_tax_amount,
122 p_freight_amount
123 FROM ar_adjustments adj,
124 ra_cust_trx_types commit_ctt,
125 ra_customer_trx commit_trx
126 WHERE commit_ctt.cust_trx_type_id = commit_trx.cust_trx_type_id
127 AND commit_trx.customer_trx_id = p_commit_ct_id
128 AND commit_ctt.type = 'DEP'
129 AND adj.customer_trx_id = p_ct_id
130 AND adj.adjustment_type = 'C';
131
132 IF PG_DEBUG in ('Y', 'C') THEN
133 arp_util.debug('arp_process_credit_util.get_commitment_adj_detail()-');
134 END IF;
135
136 EXCEPTION
137
138 WHEN OTHERS THEN
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_util.debug('get_commitment_adj_detail: ' || 'EXCEPTION : '||
141 'arp_process_credit_util.get_commitment_adj_detail()');
142 arp_util.debug('get_commitment_adj_detail: ' || 'p_ct_id : '||p_ct_id);
143 arp_util.debug('get_commitment_adj_detail: ' || 'p_commit_ct_id : '||p_commit_ct_id);
144 END IF;
145 RAISE;
146 END get_commitment_adj_detail;
147
148
149 /*===========================================================================+
150 | PROCEDURE |
151 | get_credited_trx_amounts |
152 | |
153 | DESCRIPTION |
154 | Procedure to get amounts for the credited transaction |
155 | |
156 | SCOPE - PRIVATE |
157 | |
158 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
159 | arp_util.debug |
160 | arp_trx_util.get_summary_trx_balances |
161 | |
162 | ARGUMENTS : IN: |
163 | p_ct_id |
164 | p_commit_ct_id |
165 | OUT: |
166 | p_orig_line_amount |
167 | p_orig_tax_amount |
168 | p_orig_frt_amount |
169 | p_bal_line_amount |
170 | p_bal_tax_amount |
171 | p_bal_frt_amount |
172 | p_num_line_lines |
173 | p_num_tax_lines |
174 | p_num_frt_lines |
175 | p_num_installments |
176 | IN/ OUT: |
177 | None |
178 | |
179 | RETURNS : NONE |
180 | |
181 | NOTES |
182 | |
183 | MODIFICATION HISTORY |
184 | 12-JUL-95 Subash Chadalavada Created |
185 | |
186 +===========================================================================*/
187
188 PROCEDURE get_credited_trx_amounts(
189 p_ct_id IN ra_customer_trx.customer_trx_id%type,
190 p_commit_ct_id IN ra_customer_trx.customer_trx_id%type,
191 p_orig_line_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
192 p_orig_tax_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
193 p_orig_frt_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
194 p_orig_tot_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
195 p_bal_line_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
196 p_bal_tax_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
197 p_bal_frt_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
198 p_bal_tot_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
199 p_num_line_lines OUT NOCOPY number,
200 p_num_tax_lines OUT NOCOPY number,
201 p_num_frt_lines OUT NOCOPY number,
202 p_num_installments OUT NOCOPY number)
203 IS
204 l_commit_adj_amount number;
205 l_commit_line_amount number;
206 l_commit_tax_amount number;
207 l_commit_frt_amount number;
208 l_orig_line_amount ra_customer_trx_lines.extended_amount%type;
209 l_orig_tax_amount ra_customer_trx_lines.extended_amount%type;
210 l_orig_frt_amount ra_customer_trx_lines.extended_amount%type;
211 l_orig_chrg_amount ra_customer_trx_lines.extended_amount%type;
212 l_orig_tot_amount ra_customer_trx_lines.extended_amount%type;
213 l_bal_line_amount ra_customer_trx_lines.extended_amount%type;
214 l_bal_tax_amount ra_customer_trx_lines.extended_amount%type;
215 l_bal_frt_amount ra_customer_trx_lines.extended_amount%type;
216 l_bal_chrg_amount ra_customer_trx_lines.extended_amount%type;
217 l_bal_tot_amount ra_customer_trx_lines.extended_amount%type;
218 l_num_line_lines number;
219 l_num_tax_lines number;
220 l_num_frt_lines number;
221 l_num_installments number;
222 BEGIN
223
224 IF PG_DEBUG in ('Y', 'C') THEN
225 arp_util.debug('arp_process_credit_util.get_credited_trx_amounts()+');
226 END IF;
227
228 /*--------------------------------------------------------------------+
229 | get count of installments and lines by type |
230 +--------------------------------------------------------------------*/
231
232 SELECT count(*)
233 INTO l_num_installments
234 FROM ar_payment_schedules ps
235 WHERE ps.customer_trx_id = p_ct_id;
236
237 SELECT count(decode(ctl.line_type,
238 'LINE', 1,
239 'CHARGES', 1,
240 'CB', 1,
241 null)),
242 count(decode(ctl.line_type,
243 'TAX', 1,
244 null)),
245 count(decode(ctl.line_type,
246 'FREIGHT', 1,
247 null))
248 INTO l_num_line_lines,
249 l_num_tax_lines,
250 l_num_frt_lines
251 FROM ra_customer_trx_lines ctl
252 WHERE ctl.customer_trx_id = p_ct_id;
253
254 /*--------------------------------------------------------------------+
255 | get transaction summary balances |
256 +--------------------------------------------------------------------*/
257 arp_trx_util.get_summary_trx_balances(
258 p_ct_id,
259 null,
260 l_orig_line_amount,
261 l_bal_line_amount,
262 l_orig_tax_amount,
263 l_bal_tax_amount,
264 l_orig_frt_amount,
265 l_bal_frt_amount,
266 l_orig_chrg_amount,
267 l_bal_chrg_amount,
268 l_orig_tot_amount,
269 l_bal_tot_amount);
270
271 --
272 -- get commitment adjustments if the credited transaction is a
273 -- child of a deposit
274 --
275 -- 1483656 - replacd call to original function (returned only
276 -- adj amount - with new call to procedure that returns
277 -- adj, line, tax, and frt.
278
279 IF (p_commit_ct_id IS NOT NULL)
280 THEN
281
282 get_commitment_adj_detail(p_ct_id, p_commit_ct_id,
283 l_commit_adj_amount,
284 l_commit_line_amount,
285 l_commit_tax_amount,
286 l_commit_frt_amount);
287 END IF;
288
289 p_orig_line_amount := nvl(l_orig_line_amount,0);
290 p_orig_tax_amount := l_orig_tax_amount;
291 p_orig_frt_amount := l_orig_frt_amount;
292 p_orig_tot_amount := l_orig_tot_amount;
293
294 p_bal_line_amount := nvl(l_bal_line_amount, 0) -
295 nvl(l_commit_line_amount, 0);
296 p_bal_tax_amount := nvl(l_bal_tax_amount, 0) -
297 nvl(l_commit_tax_amount, 0);
298 p_bal_frt_amount := nvl(l_bal_frt_amount, 0) -
299 nvl(l_commit_frt_amount, 0);
300 p_bal_tot_amount := nvl(l_bal_tot_amount, 0) -
301 nvl(l_commit_adj_amount, 0);
302
303 p_num_line_lines := l_num_line_lines;
304 p_num_tax_lines := l_num_tax_lines;
305 p_num_frt_lines := l_num_frt_lines;
306 p_num_installments := l_num_installments;
307
308 IF PG_DEBUG in ('Y', 'C') THEN
309 arp_util.debug('arp_process_credit_util.get_credited_trx_amounts()-');
310 END IF;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 IF PG_DEBUG in ('Y', 'C') THEN
315 arp_util.debug('get_credited_trx_amounts: ' || 'EXCEPTION : '||
316 'arp_process_credit_util.get_credited_trx_amounts');
317 arp_util.debug('get_credited_trx_amounts: ' || 'p_ct_id : '||p_ct_id);
318 arp_util.debug('get_credited_trx_amounts: ' || 'p_commit_ct_id : '||p_commit_ct_id);
319 END IF;
320
321 RAISE;
322 END get_credited_trx_amounts;
323
324 /*===========================================================================+
325 | PROCEDURE |
326 | get_credited_trx_details |
327 | |
328 | DESCRIPTION |
329 | Procedure to get details for the credited transaction |
330 | |
331 | SCOPE - PRIVATE |
332 | |
333 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
334 | arp_util.debug |
335 | arp_trx_util.get_summary_trx_balances |
336 | |
337 | ARGUMENTS : IN: |
338 | p_ct_id |
339 | p_commit_ct_id |
340 | OUT: |
341 | p_orig_line_amount |
342 | p_orig_tax_amount |
343 | p_orig_frt_amount |
344 | p_bal_line_amount |
345 | p_bal_tax_amount |
346 | p_bal_frt_amount |
347 | p_num_line_lines |
348 | p_num_tax_lines |
349 | p_num_frt_lines |
350 | p_num_installments |
351 | p_payment_exist_flag |
352 | IN/ OUT: |
353 | None |
354 | |
355 | RETURNS : NONE |
356 | |
357 | NOTES |
358 | |
359 | MODIFICATION HISTORY |
360 | 12-JUL-95 Subash Chadalavada Created |
361 | |
362 +===========================================================================*/
363 PROCEDURE get_credited_trx_details(
364 p_ct_id IN ra_customer_trx.customer_trx_id%type,
365 p_commit_ct_id IN ra_customer_trx.customer_trx_id%type,
366 p_orig_line_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
367 p_orig_tax_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
368 p_orig_frt_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
369 p_orig_tot_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
370 p_bal_line_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
371 p_bal_tax_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
372 p_bal_frt_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
373 p_bal_tot_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
374 p_num_line_lines OUT NOCOPY number,
375 p_num_tax_lines OUT NOCOPY number,
376 p_num_frt_lines OUT NOCOPY number,
377 p_num_installments OUT NOCOPY number,
378 p_payment_exist_flag OUT NOCOPY varchar2)
379 IS
380 BEGIN
381
382 IF PG_DEBUG in ('Y', 'C') THEN
383 arp_util.debug('arp_process_credit_util.get_credited_trx_details()+');
384 END IF;
385
386 get_credited_trx_amounts(
387 p_ct_id,
388 p_commit_ct_id,
389 p_orig_line_amount,
390 p_orig_tax_amount,
391 p_orig_frt_amount,
392 p_orig_tot_amount,
393 p_bal_line_amount,
394 p_bal_tax_amount,
395 p_bal_frt_amount,
396 p_bal_tot_amount,
397 p_num_line_lines,
398 p_num_tax_lines,
399 p_num_frt_lines,
400 p_num_installments);
401
402 --
403 -- get payment flag
404 --
405 select decode(nvl(sum(ps.amount_applied), 0),
406 0, 'N',
407 'Y')
408 into p_payment_exist_flag
409 from ar_payment_schedules ps
410 where customer_trx_id = p_ct_id;
411
412
413 IF PG_DEBUG in ('Y', 'C') THEN
414 arp_util.debug('arp_process_credit_util.get_credited_trx_details()-');
415 END IF;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 IF PG_DEBUG in ('Y', 'C') THEN
420 arp_util.debug('get_credited_trx_details: ' || 'EXCEPTION : '||
421 'arp_process_credit_util.get_credited_trx_details');
422 arp_util.debug('get_credited_trx_details: ' || 'p_ct_id : '||p_ct_id);
423 arp_util.debug('get_credited_trx_details: ' || 'p_commit_ct_id : '||p_commit_ct_id);
424 END IF;
425
426 RAISE;
427
428 END get_credited_trx_details;
429
430
431 /*===========================================================================+
432 | PROCEDURE |
433 | get_credited_memo_amounts |
434 | |
435 | DESCRIPTION |
436 | Procedure to get amounts for the credited memo |
437 | |
438 | SCOPE - PRIVATE |
439 | |
440 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
441 | arp_util.debug |
442 | arp_trx_util.get_summary_trx_balances |
443 | |
444 | ARGUMENTS : IN: |
445 | p_ct_id |
446 | OUT: |
447 | p_cm_line_amount |
448 | p_cm_tax_amount |
449 | p_cm_frt_amount |
450 | p_num_line_lines |
451 | p_num_tax_lines |
452 | p_num_frt_lines |
453 | IN/ OUT: |
454 | None |
455 | |
456 | RETURNS : NONE |
457 | |
458 | NOTES |
459 | |
460 | MODIFICATION HISTORY |
461 | 12-JUL-95 Subash Chadalavada Created |
462 | |
463 +===========================================================================*/
464
465 PROCEDURE get_credit_memo_amounts(
466 p_ct_id IN ra_customer_trx.customer_trx_id%type,
467 p_cm_line_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
468 p_cm_tax_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
469 p_cm_frt_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
470 p_num_line_lines OUT NOCOPY number,
471 p_num_tax_lines OUT NOCOPY number,
472 p_num_frt_lines OUT NOCOPY number)
473 IS
474 l_cm_line_amount ra_customer_trx_lines.extended_amount%type;
475 l_cm_tax_amount ra_customer_trx_lines.extended_amount%type;
476 l_cm_frt_amount ra_customer_trx_lines.extended_amount%type;
477 l_num_line_lines number;
478 l_num_tax_lines number;
479 l_num_frt_lines number;
480 BEGIN
481
482 IF PG_DEBUG in ('Y', 'C') THEN
483 arp_util.debug('arp_process_credit_util.get_credit_memo_amounts()+');
484 END IF;
485
486 SELECT sum(decode(ctl.line_type,
487 'LINE', ctl.extended_amount,
488 'CB', ctl.extended_amount,
489 'CHARGES', ctl.extended_amount,
490 null)),
491 sum(decode(ctl.line_type,
492 'TAX', ctl.extended_amount,
493 null)),
494 sum(decode(ctl.line_type,
495 'FREIGHT', ctl.extended_amount,
496 null)),
497 count(decode(ctl.line_type,
498 'LINE', 1,
499 'CB', 1,
500 'CHARGES', 1,
501 null)),
502 count(decode(ctl.line_type,
503 'TAX', 1,
504 null)),
505 count(decode(ctl.line_type,
506 'FREIGHT', 1,
507 null))
508 INTO l_cm_line_amount,
509 l_cm_tax_amount,
510 l_cm_frt_amount,
511 l_num_line_lines,
512 l_num_tax_lines,
513 l_num_frt_lines
514 FROM ra_customer_trx_lines ctl
515 WHERE ctl.customer_trx_id = p_ct_id;
516
517 p_cm_line_amount := l_cm_line_amount;
518 p_cm_tax_amount := l_cm_tax_amount;
519 p_cm_frt_amount := l_cm_frt_amount;
520 p_num_line_lines := l_num_line_lines;
521 p_num_tax_lines := l_num_tax_lines;
522 p_num_frt_lines := l_num_frt_lines;
523
524 IF PG_DEBUG in ('Y', 'C') THEN
525 arp_util.debug('arp_process_credit_util.get_credit_memo_amounts()-');
526 END IF;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 IF PG_DEBUG in ('Y', 'C') THEN
531 arp_util.debug('EXCEPTION : arp_process_credit_util.get_credit_memo_amounts');
532 arp_util.debug('get_credit_memo_amounts: ' || 'p_ct_id : '||p_ct_id);
533 END IF;
534
535 RAISE;
536
537 END get_credit_memo_amounts;
538
539 /*===========================================================================+
540 | PROCEDURE |
541 | get_parent_site_use |
542 | |
543 | DESCRIPTION |
544 | Procedure to get site use of the parent customer |
545 | |
546 | SCOPE - PRIVATE |
547 | |
548 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
549 | arp_util.debug |
550 | |
551 | ARGUMENTS : IN: |
552 | p_parent_customer_id |
553 | OUT: |
554 | p_parent_site_use_id |
555 | IN/ OUT: |
556 | None |
557 | |
558 | RETURNS : NONE |
559 | |
560 | NOTES |
561 | |
562 | MODIFICATION HISTORY |
563 | 12-JUL-95 Subash Chadalavada Created |
564 | |
565 +===========================================================================*/
566 PROCEDURE get_parent_site_use(
567 p_parent_customer_id IN hz_cust_accounts.cust_account_id%type,
568 p_parent_site_use_id OUT NOCOPY hz_cust_site_uses.site_use_id%type)
569 IS
570 l_parent_site_use_id hz_cust_site_uses.site_use_id%type;
571 BEGIN
572
573 SELECT
574 decode(count(*),
575 0, null,
576 1, substrb(min(decode(nvl(site_uses.primary_flag,'N'),
577 'Y','1',
578 'N','2')||to_char(site_uses.site_use_id)),
579 2),
580 decode(substrb(min(decode(nvl(site_uses.primary_flag,'N'),
581 'Y','1',
582 'N','2')||to_char(site_uses.site_use_id)),
583 1,1),
584 '1', substrb(min(decode(nvl(site_uses.primary_flag,'N'),
585 'Y','1',
586 'N','2')||to_char(site_uses.site_use_id)),
587 2),
588 null))
589 INTO l_parent_site_use_id
590 FROM hz_cust_site_uses site_uses,
591 hz_cust_acct_sites acct_site
592 WHERE site_uses.site_use_code = 'BILL_TO'
593 and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
594 and acct_site.cust_account_id = p_parent_customer_id;
595
596 p_parent_site_use_id := l_parent_site_use_id;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 IF PG_DEBUG in ('Y', 'C') THEN
601 arp_util.debug('EXCEPTION : get_parent_site_use');
602 arp_util.debug('get_parent_site_use: ' || 'p_parent_customer_id : '||p_parent_customer_id);
603 END IF;
604 RAISE;
605 END get_parent_site_use;
606
607
608 /*===========================================================================+
609 | PROCEDURE |
610 | get_parent_customer_site |
611 | |
612 | DESCRIPTION |
613 | Procedure to get the parent customer and parent site use |
614 | |
615 | SCOPE - PRIVATE |
616 | |
617 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
618 | arp_util.debug |
619 | |
620 | ARGUMENTS : IN: |
621 | p_bill_to_customer_id |
622 | OUT: |
623 | p_parent_customer_id |
624 | p_parent_site_use_id |
625 | IN/ OUT: |
626 | None |
627 | |
628 | RETURNS : NONE |
629 | |
630 | NOTES |
631 | |
632 | MODIFICATION HISTORY |
633 | 12-JUL-95 Subash Chadalavada Created |
634 | |
635 +===========================================================================*/
636
637 PROCEDURE get_parent_customer_site(
638 p_bill_to_customer_id IN hz_cust_accounts.cust_account_id%type,
639 p_parent_customer_id OUT NOCOPY hz_cust_accounts.cust_account_id%type,
640 p_parent_site_use_id OUT NOCOPY hz_cust_site_uses.site_use_id%type)
641 IS
642 l_parent_customer_id hz_cust_accounts.cust_account_id%type;
643 BEGIN
644
645 BEGIN
646
647 SELECT cr.cust_account_id
648 INTO l_parent_customer_id
649 FROM hz_cust_acct_relate cr
650 WHERE cr.related_cust_account_id = p_bill_to_customer_id
651 AND cr.status = 'A'
652 AND cr.bill_to_flag = 'Y'
653 AND nvl(cr.customer_reciprocal_flag,'N') = 'N';
654
655 p_parent_customer_id := l_parent_customer_id;
656
657 EXCEPTION
658
659 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
660 l_parent_customer_id := null;
661 WHEN OTHERS THEN
662 RAISE;
663 END;
664
665 IF (l_parent_customer_id IS NOT NULL)
666 THEN
667 get_parent_site_use(l_parent_customer_id,
668 p_parent_site_use_id);
669 END IF;
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 IF PG_DEBUG in ('Y', 'C') THEN
674 arp_util.debug('EXCEPTION : get_parent_customer_site');
675 arp_util.debug('get_parent_customer_site: ' || 'p_bill_to_customer_id : '||p_bill_to_customer_id);
676 END IF;
677 RAISE;
678 END get_parent_customer_site;
679
680 /*===========================================================================+
681 | FUNCTION |
682 | check_payment_method |
683 | |
684 | DESCRIPTION |
685 | Function to check if the payment method is valid |
686 | |
687 | SCOPE - PRIVATE |
688 | |
689 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
690 | arp_util.debug |
691 | |
692 | ARGUMENTS : IN: |
693 | p_trx_date |
694 | p_customer_id |
695 | p_site_use_id |
696 | p_parent_customer_id |
697 | p_parent_site_use_id |
698 | p_currency_code |
699 | p_crtrx_receipt_method_id |
700 | OUT: |
701 | p_payment_method_name |
702 | p_receipt_method_id |
703 | p_creation_method_code |
704 | IN/ OUT: |
705 | None |
706 | |
707 | RETURNS : BOOLEAN : TRUE if payment method is valid |
708 | FALSE if invalid |
709 | |
710 | NOTES |
711 | |
712 | MODIFICATION HISTORY |
713 | 12-JUL-95 Subash Chadalavada Created |
714 | |
715 +===========================================================================*/
716
717 FUNCTION check_payment_method(
718 p_trx_date IN
719 ra_customer_trx.trx_date%type,
720 p_customer_id IN
721 ra_customer_trx.customer_trx_id%type,
722 p_site_use_id IN
723 hz_cust_site_uses.site_use_id%type,
724 p_parent_customer_id IN
725 hz_cust_accounts.cust_account_id%type,
726 p_parent_site_use_id IN
727 hz_cust_site_uses.site_use_id%type,
728 p_currency_code IN
729 fnd_currencies.currency_code%type,
730 p_crtrx_receipt_method_id IN
731 ar_receipt_methods.receipt_method_id%type,
732 p_payment_method_name OUT NOCOPY
733 ar_receipt_methods.name%type,
734 p_receipt_method_id OUT NOCOPY
735 ar_receipt_methods.receipt_method_id%type,
736 p_creation_method_code OUT NOCOPY
737 ar_receipt_classes.creation_method_code%type
738 ) RETURN BOOLEAN IS
739
740 l_payment_method_name ar_receipt_methods.name%type;
741 l_receipt_method_id ar_receipt_methods.receipt_method_id%type;
742 l_creation_method_code ar_receipt_classes.creation_method_code%type;
743
744 BEGIN
745
746 SELECT arm.name,
747 arm.receipt_method_id,
748 arc.creation_method_code,
749 arm.name,
750 arm.receipt_method_id,
751 arc.creation_method_code
752 INTO l_payment_method_name,
753 l_receipt_method_id,
754 l_creation_method_code,
755 p_payment_method_name,
756 p_receipt_method_id,
757 p_creation_method_code
758 FROM ar_receipt_methods arm,
759 ra_cust_receipt_methods rcrm,
760 ar_receipt_method_accounts arma,
761 ce_bank_accounts cba,
762 ce_bank_acct_uses aba,
763 ar_receipt_classes arc,
764 ce_bank_branches_v bp /*Bug3348454*/
765 WHERE arm.receipt_method_id = rcrm.receipt_method_id
766 AND arm.receipt_method_id = arma.receipt_method_id
767 AND arm.receipt_class_id = arc.receipt_class_id
768 AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
769 AND aba.bank_account_id = cba.bank_account_id
770 /*Bug3348454*/
771 AND cba.bank_branch_id = bp.branch_party_id
772 AND p_trx_date <= NVL(bp.end_date,p_trx_date)
773 /*Bug3348454*/
774
775 -- AND aba.set_of_books_id = pg_set_of_books_id
776 AND arm.receipt_method_id = p_crtrx_receipt_method_id
777 AND
778 (
779 ( rcrm.customer_id = p_customer_id
780 AND
781 NVL(rcrm.site_use_id,
782 p_site_use_id) = p_site_use_id
783 )
784 OR
785 ( rcrm.customer_id = nvl(p_parent_customer_id,
786 -88888)
787 AND
788 nvl(rcrm.site_use_id,
789 nvl(p_parent_site_use_id,
790 -88888)) = nvl(p_parent_site_use_id,
791 -88888)
792 )
793 )
794 AND (
795 cba.currency_code =
796 p_currency_code OR
797 cba.receipt_multi_currency_flag = 'Y'
798 )
799 -- AND aba.set_of_books_id = pg_set_of_books_id
800 /*Bug3348454*/
801 /*AND TRUNC(nvl(aba.end_date,
802 p_trx_date)) >=
803 TRUNC(p_trx_date)*/
804
805 AND TRUNC(nvl(cba.end_date,p_trx_date+1)) > TRUNC(p_trx_date)
806
807 AND p_trx_date between
808 TRUNC(nvl(
809 arm.start_date,
810 p_trx_date))
811 and TRUNC(nvl(
812 arm.end_date,
813 p_trx_date))
814 AND p_trx_date between
815 TRUNC(nvl(
816 rcrm.start_date,
817 p_trx_date))
818 and TRUNC(nvl(
819 rcrm.end_date,
820 p_trx_date))
821 AND p_trx_date between
822 TRUNC(arma.start_date)
823 and TRUNC(nvl(
824 arma.end_date,
825 p_trx_date))
826 AND rownum = 1;
827
828 return(TRUE);
829
830 EXCEPTION
831
832 WHEN NO_DATA_FOUND THEN
833 IF PG_DEBUG in ('Y', 'C') THEN
834 arp_util.debug('check_payment_method: ' || 'return value: FALSE');
835 END IF;
836 RETURN(FALSE);
837
838 WHEN OTHERS THEN
839 RAISE;
840
841 END check_payment_method;
842
843 /*===========================================================================+
844 | FUNCTION |
845 | check_bank_account |
846 | |
847 | DESCRIPTION |
848 | Function to check if a bank account is valid |
849 | |
850 | SCOPE - PRIVATE |
851 | |
852 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
853 | arp_util.debug |
854 | |
855 | ARGUMENTS : IN: |
856 | p_trx_date |
857 | p_currency_code |
858 | p_bill_to_customer_id |
859 | p_bill_to_site_use_id |
860 | p_parent_customer_id |
861 | p_parent_site_use_id |
862 | p_crtrx_cust_bank_account_id |
863 | OUT: |
864 | p_cust_bank_account_id |
865 | p_paying_customer_id |
866 | IN/ OUT: |
867 | None |
868 | |
869 | RETURNS : BOOLEAN : TRUE if bank account is valid |
870 | FALSE if invalid |
871 | |
872 | NOTES |
873 | |
874 | MODIFICATION HISTORY |
875 | 12-JUL-95 Subash Chadalavada Created |
876 | |
877 +===========================================================================*/
878
879 FUNCTION check_bank_account(
880 p_trx_date IN
881 ra_customer_trx.trx_date%type,
882 p_currency_code IN
883 fnd_currencies.currency_code%type,
884 p_bill_to_customer_id IN
885 hz_cust_accounts.cust_account_id%type,
886 p_bill_to_site_use_id IN
887 hz_cust_site_uses.site_use_id%type,
888 p_parent_customer_id IN
889 hz_cust_accounts.cust_account_id%type,
890 p_parent_site_use_id IN
891 hz_cust_site_uses.site_use_id%type,
892 p_crtrx_cust_bank_account_id IN
893 ce_bank_accounts.bank_account_id%type,
894 p_cust_bank_account_id OUT NOCOPY
895 ce_bank_accounts.bank_account_id%type,
896 p_paying_customer_id OUT NOCOPY
897 hz_cust_accounts.cust_account_id%type)
898 RETURN BOOLEAN IS
899 l_cust_bank_account_id ce_bank_accounts.bank_account_id%type;
900 l_paying_customer_id hz_cust_accounts.cust_account_id%type;
901 l_account_valid boolean := FALSE;
902 BEGIN
903 /* BICHATTE removed the validation for bank account
904 PAYMENT UPTAKE */
905 RETURN(TRUE);
906 END;
907
908 /*===========================================================================+
909 | FUNCTION |
910 | check_cm_trxtype |
911 | |
912 | DESCRIPTION |
913 | Function to check whether the cm transaction types has the open |
914 | receivable flag and post to gl are same as their related invoice. |
915 | SCOPE - PRIVATE |
916 | |
917 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
918 | arp_util.debug |
919 | |
920 | ARGUMENTS : IN: |
921 | p_inv_trx_type_id |
922 | p_inv_open_rec_flag |
923 | p_cm_trx_type_id |
924 | IN/ OUT: |
925 | None |
926 | |
927 | RETURNS : BOOLEAN : if same flags then TRUE else FALSE |
928 | |
929 | NOTES |
930 | Bug-3205760 - 3547652 |
931 | MODIFICATION HISTORY |
932 | 17-MAY-2004 Surendra Rajan Created |
933 | |
934 +===========================================================================*/
935
936 FUNCTION check_cm_trxtype(
937 p_inv_trx_type_id IN
938 ra_cust_trx_types.cust_trx_type_id%type,
939 p_inv_open_rec_flag IN
940 ra_cust_trx_types.accounting_affect_flag%type,
941 p_cm_trx_type_id IN
942 ra_cust_trx_types.cust_trx_type_id%type
943 )
944 RETURN BOOLEAN IS
945 l_dummy char := 'N';
946 BEGIN
947 IF P_INV_TRX_TYPE_ID IS NOT NULL Then
948
949 Select 'Y' Into l_dummy
950 from ra_cust_trx_types cmctt
951 where cmctt.cust_trx_type_id = p_cm_trx_type_id
952 and
953 cmctt.accounting_affect_flag = nvl(p_inv_open_rec_flag,cmctt.accounting_affect_flag) and
954 cmctt.post_to_gl = (select post_to_gl from ra_cust_trx_types invctt
955 where invctt.cust_trx_type_id = p_inv_trx_type_id);
956 End If;
957 Return(TRUE);
958 EXCEPTION
959 WHEN NO_DATA_FOUND THEN
960 RETURN(FALSE);
961 WHEN OTHERS THEN
962 IF PG_DEBUG in ('Y', 'C') THEN
963 arp_util.debug('EXCEPTION : check_cm_trxtype');
964 END IF;
965 RAISE;
966 END;
967
968
969 /*===========================================================================+
970 | PROCEDURE |
971 | get_cm_defaults |
972 | |
973 | DESCRIPTION |
974 | Procedure to get the defaults for a CM |
975 | |
976 | SCOPE - PRIVATE |
977 | |
978 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
979 | arp_util.debug |
980 | |
981 | ARGUMENTS : IN: |
982 | OUT: |
983 | IN/ OUT: |
984 | None |
985 | |
986 | RETURNS : NONE |
987 | |
988 | NOTES |
989 | |
990 | MODIFICATION HISTORY |
991 | 12-JUL-95 Subash Chadalavada Created |
992 | |
993 +===========================================================================*/
994
995 PROCEDURE get_cm_header_defaults(
996 p_trx_date IN
997 ra_customer_trx.trx_date%type,
998 p_crtrx_ct_id IN
999 ra_customer_trx.customer_trx_id%type,
1000 p_ct_id IN
1001 ra_customer_trx.customer_trx_id%type,
1002 p_bs_id IN
1003 ra_batch_sources.batch_source_id%type,
1004 p_gl_date IN
1005 ra_cust_trx_line_gl_dist.gl_date%type,
1006 p_currency_code IN
1007 fnd_currencies.currency_code%type,
1008 p_cust_trx_type_id IN
1009 ra_cust_trx_types.cust_trx_type_id%type,
1010 p_ship_to_customer_id IN
1011 hz_cust_accounts.cust_account_id%type,
1012 p_ship_to_site_use_id IN
1013 hz_cust_site_uses.site_use_id%type,
1014 p_ship_to_contact_id IN
1015 hz_cust_account_roles.cust_account_role_id%type,
1016 p_bill_to_customer_id IN
1017 hz_cust_accounts.cust_account_id%type,
1018 p_bill_to_site_use_id IN
1019 hz_cust_site_uses.site_use_id%type,
1020 p_bill_to_contact_id IN
1021 hz_cust_account_roles.cust_account_role_id%type,
1022 p_primary_salesrep_id IN
1023 ra_salesreps.salesrep_id%type,
1024 p_receipt_method_id IN
1025 ar_receipt_methods.receipt_method_id%type,
1026 p_customer_bank_account_id IN
1027 ce_bank_accounts.bank_account_id%type,
1028 p_paying_customer_id IN
1029 hz_cust_accounts.cust_account_id%type,
1030 p_paying_site_use_id IN
1031 hz_cust_site_uses.site_use_id%type,
1032 p_ship_via IN
1033 ra_customer_trx.ship_via%type,
1034 p_fob_point IN
1035 ra_customer_trx.fob_point%type,
1036 p_invoicing_rule_id IN
1037 ra_customer_trx.invoicing_rule_id%type,
1038 p_rev_recog_run_flag IN
1039 varchar2,
1040 p_complete_flag IN
1041 ra_customer_trx.complete_flag%type,
1042 p_salesrep_required_flag IN
1043 ar_system_parameters.salesrep_required_flag%type,
1044 --
1045 p_crtrx_bs_id IN
1046 ra_batch_sources.batch_source_id%type,
1047 p_crtrx_cm_bs_id IN
1048 ra_batch_sources.batch_source_id%type,
1049 p_batch_bs_id IN
1050 ra_batch_sources.batch_source_id%type,
1051 p_profile_bs_id IN
1052 ra_batch_sources.batch_source_id%type,
1053 p_crtrx_type_id IN
1054 ra_cust_trx_types.cust_trx_type_id%type,
1055 p_crtrx_cm_type_id IN
1056 ra_cust_trx_types.cust_trx_type_id%type,
1057 p_crtrx_gl_date IN
1058 ra_cust_trx_line_gl_dist.gl_date%type,
1059 p_batch_gl_date IN
1060 ra_batches.gl_date%type,
1061 --
1062 p_crtrx_ship_to_customer_id IN
1063 hz_cust_accounts.cust_account_id%type,
1064 p_crtrx_ship_to_site_use_id IN
1065 hz_cust_site_uses.site_use_id%type,
1066 p_crtrx_ship_to_contact_id IN
1067 hz_cust_account_roles.cust_account_role_id%type,
1068 p_crtrx_bill_to_customer_id IN
1069 hz_cust_accounts.cust_account_id%type,
1070 p_crtrx_bill_to_site_use_id IN
1071 hz_cust_site_uses.site_use_id%type,
1072 p_crtrx_bill_to_contact_id IN
1073 hz_cust_account_roles.cust_account_role_id%type,
1074 p_crtrx_primary_salesrep_id IN
1075 ra_salesreps.salesrep_id%type,
1076 p_crtrx_open_rec_flag IN
1077 ra_cust_trx_types.accounting_affect_flag%type,
1078 --
1079 p_crtrx_receipt_method_id IN
1080 ar_receipt_methods.receipt_method_id%type,
1081 p_crtrx_cust_bank_account_id IN
1082 ce_bank_accounts.bank_account_id%type,
1083 p_crtrx_ship_via IN
1084 ra_customer_trx.ship_via%type,
1085 p_crtrx_ship_date_actual IN
1086 ra_customer_trx.ship_date_actual%type,
1087 p_crtrx_waybill_number IN
1088 ra_customer_trx.waybill_number%type,
1089 p_crtrx_fob_point IN
1090 ra_customer_trx.fob_point%type,
1091 --
1092 p_default_bs_id OUT NOCOPY
1093 ra_batch_sources.batch_source_id%type,
1094 p_default_bs_name OUT NOCOPY
1095 ra_batch_sources.name%type,
1096 p_auto_trx_numbering_flag OUT NOCOPY
1097 ra_batch_sources.auto_trx_numbering_flag%type,
1098 p_bs_type OUT NOCOPY
1099 ra_batch_sources.batch_source_type%type,
1100 p_copy_doc_number_flag OUT NOCOPY
1101 ra_batch_sources.copy_doc_number_flag%type,
1102 p_bs_default_cust_trx_type_id OUT NOCOPY
1103 ra_cust_trx_types.cust_trx_type_id%type,
1104 p_default_cust_trx_type_id OUT NOCOPY
1105 ra_cust_trx_types.cust_trx_type_id%type,
1106 p_default_type_name OUT NOCOPY
1107 ra_cust_trx_types.name%type,
1108 p_open_receivable_flag OUT NOCOPY
1109 ra_cust_trx_types.accounting_affect_flag%type,
1110 p_post_to_gl_flag OUT NOCOPY
1111 ra_cust_trx_types.post_to_gl%type,
1112 p_allow_freight_flag OUT NOCOPY
1113 ra_cust_trx_types.allow_freight_flag%type,
1114 p_creation_sign OUT NOCOPY
1115 ra_cust_trx_types.creation_sign%type,
1116 p_allow_overapplication_flag OUT NOCOPY
1117 ra_cust_trx_types.allow_overapplication_flag%type,
1118 p_natural_app_only_flag OUT NOCOPY
1119 ra_cust_trx_types.natural_application_only_flag%type,
1120 p_tax_calculation_flag OUT NOCOPY
1121 ra_cust_trx_types.tax_calculation_flag%type,
1122 p_default_printing_option OUT NOCOPY
1123 ra_customer_trx.printing_option%type,
1124 --
1125 p_default_gl_date OUT NOCOPY
1126 ra_cust_trx_line_gl_dist.gl_date%type,
1127 p_default_ship_to_customer_id OUT NOCOPY
1128 hz_cust_accounts.cust_account_id%type,
1129 p_default_ship_to_site_use_id OUT NOCOPY
1130 hz_cust_site_uses.site_use_id%type,
1131 p_default_ship_to_contact_id OUT NOCOPY
1132 hz_cust_account_roles.cust_account_role_id%type,
1133 p_default_bill_to_customer_id OUT NOCOPY
1134 hz_cust_accounts.cust_account_id%type,
1135 p_default_bill_to_site_use_id OUT NOCOPY
1136 hz_cust_site_uses.site_use_id%type,
1137 p_default_bill_to_contact_id OUT NOCOPY
1138 hz_cust_account_roles.cust_account_role_id%type,
1139 p_default_primary_salesrep_id OUT NOCOPY
1140 ra_salesreps.salesrep_id%type,
1141 p_default_receipt_method_id OUT NOCOPY
1142 ar_receipt_methods.receipt_method_id%type,
1143 p_default_cust_bank_account_id OUT NOCOPY
1144 ce_bank_accounts.bank_account_id%type,
1145 p_default_paying_customer_id OUT NOCOPY
1146 hz_cust_accounts.cust_account_id%type,
1147 p_default_paying_site_use_id OUT NOCOPY
1148 hz_cust_site_uses.site_use_id%type,
1149 p_default_ship_via OUT NOCOPY
1150 ra_customer_trx.ship_via%type,
1151 p_default_ship_date_actual OUT NOCOPY
1152 ra_customer_trx.ship_date_actual%type,
1153 p_default_waybill_number OUT NOCOPY
1154 ra_customer_trx.waybill_number%type,
1155 p_default_fob_point OUT NOCOPY
1156 ra_customer_trx.fob_point%type
1157 )
1158 IS
1159 l_bs_id ra_batch_sources.batch_source_id%type;
1160 l_default_bs_name ra_batch_sources.name%type;
1161 l_auto_trx_numbering_flag ra_batch_sources.auto_trx_numbering_flag%type;
1162 l_bs_type ra_batch_sources.batch_source_type%type;
1163 l_copy_doc_number_flag ra_batch_sources.copy_doc_number_flag%type;
1164 l_bs_default_cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%type;
1165 l_default_cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%type;
1166 l_default_status_code ar_lookups.lookup_code%type;
1167 l_default_status ar_lookups.meaning%type;
1168 l_default_printing_option_code ar_lookups.lookup_code%type;
1169 l_default_printing_option ar_lookups.meaning%type;
1170 l_default_term_id ra_terms.term_id%type;
1171 l_default_term_name ra_terms.name%type;
1172 l_bill_to_customer_id hz_cust_accounts.cust_account_id%type;
1173 l_bill_to_site_use_id hz_cust_site_uses.site_use_id%type;
1174 l_ship_to_site_use_id hz_cust_site_uses.site_use_id%type; --4766915
1175 l_ship_to_contact_id hz_cust_account_roles.cust_account_role_id%type;
1176 l_primary_salesrep_id ra_salesreps.salesrep_id%type;
1177 l_number_of_due_dates number;
1178 l_term_due_date date;
1179 l_default_class ra_cust_trx_types.type%type;
1180 l_post_to_gl_flag ra_cust_trx_types.post_to_gl%type;
1181 l_allow_not_open_flag varchar2(1);
1182 l_default_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
1183 l_defaulting_rule_used varchar2(128);
1184 l_error_message varchar2(128);
1185
1186 l_paying_customer_id hz_cust_accounts.cust_account_id%type;
1187 l_parent_customer_id hz_cust_accounts.cust_account_id%type;
1188 l_paying_site_use_id hz_cust_site_uses.site_use_id%type;
1189 l_parent_site_use_id hz_cust_site_uses.site_use_id%type;
1190
1191 l_receipt_method_id ar_receipt_methods.receipt_method_id%type;
1192 l_payment_method_name ar_receipt_methods.name%type;
1193 l_creation_method_code ar_receipt_classes.creation_method_code%type;
1194 l_cust_bank_account_id ce_bank_accounts.bank_account_id%type;
1195 l_bank_account_num ce_bank_accounts.bank_account_num%type;
1196 l_bank_name ce_bank_branches_v.bank_name%type;
1197 l_bank_branch_name ce_bank_branches_v.bank_branch_name%type;
1198 l_bank_branch_id ce_bank_branches_v.branch_party_id%TYPE;
1199
1200 -- 7/18/98: gjayanth: ar_receipt_methods.payment_type_code is an
1201 -- additional parameter to get_bank_defaults().
1202 --
1203 l_payment_type_code ar_receipt_methods.payment_type_code%type := NULL;
1204
1205 -- Added for bug # 2712726
1206 -- ORASHID
1207 --
1208 l_nocopy_cust_bank_account_id ce_bank_accounts.bank_account_id%type;
1209
1210 BEGIN
1211 IF PG_DEBUG in ('Y', 'C') THEN
1212 arp_util.debug('arp_process_credit_util.get_cm_header_defaults()+');
1213 END IF;
1214
1215 --
1216 -- first try out NOCOPY the credit memo batch source corresponding to
1217 -- the credited transaction's batch source
1218 --
1219 IF ( p_bs_id IS NOT NULL )
1220 THEN
1221 arp_trx_defaults_2.get_source_default(
1222 p_bs_id,
1223 'CM',
1224 p_trx_date,
1225 null,
1226 l_bs_id,
1227 p_default_bs_name,
1228 p_auto_trx_numbering_flag,
1229 p_bs_type,
1230 p_copy_doc_number_flag,
1231 l_bs_default_cust_trx_type_id
1232 );
1233 END IF;
1234
1235 --
1236 -- try credit memo batch source corresponding to the credited
1237 -- transaction's batch source
1238 --
1239 IF ( l_bs_id IS NULL
1240 AND
1241 p_crtrx_cm_bs_id IS NOT NULL )
1242 THEN
1243 arp_trx_defaults_2.get_source_default(
1244 p_crtrx_cm_bs_id,
1245 'CM',
1246 p_trx_date,
1247 null,
1248 l_bs_id,
1249 p_default_bs_name,
1250 p_auto_trx_numbering_flag,
1251 p_bs_type,
1252 p_copy_doc_number_flag,
1253 l_bs_default_cust_trx_type_id
1254 );
1255 END IF;
1256
1257 --
1258 -- try credited transaction's batch source
1259 --
1260 IF ( l_bs_id IS NULL
1261 AND
1262 p_crtrx_bs_id IS NOT NULL )
1263 THEN
1264 arp_trx_defaults_2.get_source_default(
1265 p_crtrx_bs_id,
1266 'CM',
1267 p_trx_date,
1268 null,
1269 l_bs_id,
1270 p_default_bs_name,
1271 p_auto_trx_numbering_flag,
1272 p_bs_type,
1273 p_copy_doc_number_flag,
1274 l_bs_default_cust_trx_type_id
1275 );
1276 END IF;
1277
1278 --
1279 -- try batch source for the batch
1280 --
1281 IF ( l_bs_id IS NULL
1282 AND
1283 p_batch_bs_id IS NOT NULL )
1284 THEN
1285 arp_trx_defaults_2.get_source_default(
1286 p_batch_bs_id,
1287 'CM',
1288 p_trx_date,
1289 null,
1290 l_bs_id,
1291 p_default_bs_name,
1292 p_auto_trx_numbering_flag,
1293 p_bs_type,
1294 p_copy_doc_number_flag,
1295 l_bs_default_cust_trx_type_id
1296 );
1297 END IF;
1298
1299 -- try profile
1300
1301 IF ( l_bs_id IS NULL
1302 AND
1303 p_profile_bs_id IS NOT NULL )
1304 THEN
1305 arp_trx_defaults_2.get_source_default(
1306 p_profile_bs_id,
1307 'CM',
1308 p_trx_date,
1309 null,
1310 l_bs_id,
1311 p_default_bs_name,
1312 p_auto_trx_numbering_flag,
1313 p_bs_type,
1314 p_copy_doc_number_flag,
1315 l_bs_default_cust_trx_type_id
1316 );
1317 END IF;
1318
1319 IF ( l_bs_id IS NULL )
1320 THEN return;
1321 ELSE
1322 p_default_bs_id := l_bs_id;
1323 p_bs_default_cust_trx_type_id := l_bs_default_cust_trx_type_id;
1324
1325 IF ( p_cust_trx_type_id IS NOT NULL )
1326 THEN
1327 arp_trx_defaults_2.get_type_defaults(
1328 p_cust_trx_type_id,
1329 p_trx_date,
1330 'CM',
1331 null,
1332 p_invoicing_rule_id,
1333 p_rev_recog_run_flag,
1334 p_complete_flag,
1335 p_crtrx_open_rec_flag,
1336 p_ct_id,
1337 l_default_cust_trx_type_id,
1338 p_default_type_name,
1339 l_default_class,
1340 p_open_receivable_flag,
1341 l_post_to_gl_flag,
1342 p_allow_freight_flag,
1343 p_creation_sign,
1344 p_allow_overapplication_flag,
1345 p_natural_app_only_flag,
1346 p_tax_calculation_flag,
1347 l_default_status_code,
1348 l_default_status,
1349 l_default_printing_option_code,
1350 l_default_printing_option,
1351 l_default_term_id,
1352 l_default_term_name,
1353 l_number_of_due_dates,
1354 l_term_due_date);
1355 END IF;
1356
1357 IF ( l_default_cust_trx_type_id IS NULL
1358 AND
1359 p_crtrx_cm_type_id IS NOT NULL)
1360 THEN
1361 /* Bug-3205760 */
1362 IF (check_cm_trxtype(
1363 p_crtrx_type_id, -- Invoice trx type
1364 p_crtrx_open_rec_flag, -- Invoice flag
1365 p_crtrx_cm_type_id -- cm trx type
1366 )
1367 ) Then
1368 arp_trx_defaults_2.get_type_defaults(
1369 p_crtrx_cm_type_id,
1370 p_trx_date,
1371 'CM',
1372 null,
1373 p_invoicing_rule_id,
1374 p_rev_recog_run_flag,
1375 p_complete_flag,
1376 p_crtrx_open_rec_flag,
1377 p_ct_id,
1378 l_default_cust_trx_type_id,
1379 p_default_type_name,
1380 l_default_class,
1381 p_open_receivable_flag,
1382 l_post_to_gl_flag,
1383 p_allow_freight_flag,
1384 p_creation_sign,
1385 p_allow_overapplication_flag,
1386 p_natural_app_only_flag,
1387 p_tax_calculation_flag,
1388 l_default_status_code,
1389 l_default_status,
1390 l_default_printing_option_code,
1391 l_default_printing_option,
1392 l_default_term_id,
1393 l_default_term_name,
1394 l_number_of_due_dates,
1395 l_term_due_date);
1396 END IF;
1397 END IF;
1398
1399 IF ( l_default_cust_trx_type_id IS NULL
1400 AND
1401 l_bs_default_cust_trx_type_id IS NOT NULL)
1402 THEN
1403 /* Bug-3205760 */
1404 IF (check_cm_trxtype(
1405 p_crtrx_type_id, -- Invoice trx type
1406 p_crtrx_open_rec_flag, -- Invoice flag
1407 l_bs_default_cust_trx_type_id -- cm trx type
1408 )
1409 ) Then
1410 arp_trx_defaults_2.get_type_defaults(
1411 l_bs_default_cust_trx_type_id,
1412 p_trx_date,
1413 'CM',
1414 null,
1415 p_invoicing_rule_id,
1416 p_rev_recog_run_flag,
1417 p_complete_flag,
1418 p_crtrx_open_rec_flag,
1419 p_ct_id,
1420 l_default_cust_trx_type_id,
1421 p_default_type_name,
1422 l_default_class,
1423 p_open_receivable_flag,
1424 l_post_to_gl_flag,
1425 p_allow_freight_flag,
1426 p_creation_sign,
1427 p_allow_overapplication_flag,
1428 p_natural_app_only_flag,
1429 p_tax_calculation_flag,
1430 l_default_status_code,
1431 l_default_status,
1432 l_default_printing_option_code,
1433 l_default_printing_option,
1434 l_default_term_id,
1435 l_default_term_name,
1436 l_number_of_due_dates,
1437 l_term_due_date);
1438 END IF;
1439 END IF;
1440
1441 IF (l_default_cust_trx_type_id IS NOT NULL)
1442 THEN
1443 p_default_cust_trx_type_id := l_default_cust_trx_type_id;
1444 p_post_to_gl_flag := l_post_to_gl_flag;
1445 p_default_printing_option := l_default_printing_option_code;
1446 END IF;
1447
1448 END IF;
1449
1450 IF (l_post_to_gl_flag = 'Y')
1451 THEN
1452 IF (p_invoicing_rule_id = -3)
1453 THEN l_allow_not_open_flag := 'Y';
1454 ELSE l_allow_not_open_flag := 'N';
1455 END IF;
1456
1457 /* Bug 1882597
1458 The fourth parameter to the function call is changed to NULL
1459 from p_trx_date */
1460 IF ( arp_util.validate_and_default_gl_date(
1461 p_gl_date,
1462 p_trx_date,
1463 p_crtrx_gl_date,
1464 NULL,
1465 NULL,
1466 p_trx_date,
1467 p_crtrx_gl_date,
1468 p_batch_gl_date,
1469 l_allow_not_open_flag,
1470 TO_CHAR(p_invoicing_rule_id),
1471 arp_trx_global.system_info.system_parameters.set_of_books_id,
1472 222,
1473 l_default_gl_date,
1474 l_defaulting_rule_used,
1475 l_error_message
1476 ) = FALSE )
1477 THEN
1478 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1479 fnd_message.set_token('GENERIC_TEXT',
1480 l_error_message);
1481 app_exception.raise_exception;
1482
1483 ELSE
1484
1485 IF PG_DEBUG in ('Y', 'C') THEN
1486 arp_util.debug('get_cm_header_defaults: ' || 'default GL Date: ' ||
1487 to_char(l_default_gl_date) ||
1488 ' Rule: ' || l_defaulting_rule_used);
1489 END IF;
1490
1491 IF (l_default_gl_date IS NOT NULL)
1492 THEN
1493 p_default_gl_date := l_default_gl_date;
1494 END IF;
1495 END IF;
1496
1497 END IF;
1498
1499 IF (p_crtrx_ship_to_customer_id IS NOT NULL
1500 AND
1501 p_ship_to_customer_id IS NULL )
1502 THEN
1503 p_default_ship_to_customer_id := p_crtrx_ship_to_customer_id;
1504 p_default_ship_to_site_use_id := p_crtrx_ship_to_site_use_id;
1505 p_default_ship_to_contact_id := p_crtrx_ship_to_contact_id;
1506 --4766915
1507 l_ship_to_site_use_id := p_crtrx_ship_to_site_use_id;
1508
1509 l_ship_to_contact_id := p_crtrx_ship_to_contact_id;
1510 ELSE
1511 l_ship_to_contact_id := p_ship_to_contact_id;
1512 --4766915
1513 l_ship_to_site_use_id := p_ship_to_site_use_id;
1514 END IF;
1515
1516
1517 IF (p_crtrx_bill_to_customer_id IS NOT NULL
1518 AND
1519 p_bill_to_customer_id IS NULL )
1520 THEN
1521 p_default_bill_to_customer_id := p_crtrx_bill_to_customer_id;
1522 l_bill_to_customer_id := p_crtrx_bill_to_customer_id;
1523 p_default_bill_to_site_use_id := p_crtrx_bill_to_site_use_id;
1524 l_bill_to_site_use_id := p_crtrx_bill_to_site_use_id;
1525
1526 BEGIN
1527 -- Bug 1883538: replaced references of current role state with
1528 -- status column
1529 SELECT distinct acct_role.cust_account_role_id
1530 INTO p_default_bill_to_contact_id
1531 FROM hz_cust_account_roles acct_role,
1532 hz_cust_site_uses site_uses
1533 WHERE site_uses.site_use_id = l_bill_to_site_use_id
1534 AND acct_role.cust_account_id = l_bill_to_customer_id
1535 AND nvl(acct_role.cust_acct_site_id,site_uses.cust_acct_site_id)
1536 = site_uses.cust_acct_site_id
1537 AND ( acct_role.cust_account_role_id = p_crtrx_bill_to_contact_id
1538 OR
1539 nvl(acct_role.status,'I') = 'A'
1540 )
1541 AND acct_role.cust_account_role_id =
1542 nvl(p_crtrx_bill_to_contact_id,
1543 l_ship_to_contact_id);
1544 EXCEPTION
1545 WHEN NO_DATA_FOUND THEN
1546 null;
1547 WHEN OTHERS THEN
1548 RAISE;
1549 END;
1550 ELSE
1551 l_bill_to_customer_id := p_bill_to_customer_id;
1552 l_bill_to_site_use_id := p_bill_to_site_use_id;
1553 END IF;
1554
1555
1556 --Bug 4766915
1557 -- default to the salesrep id of the credited transaction
1558 -- Otherwise pick the sales person from the bill-to on the invoice.
1559 -- If not available, we pick it from the ship-to on the invoice.
1560 -- If neither of these are avilable, we do not default any sales person
1561 -- and we go to No Sales Credit.
1562
1563
1564 IF (p_crtrx_primary_salesrep_id IS NOT NULL)
1565 THEN
1566
1567 IF ( p_primary_salesrep_id IS NULL)
1568 THEN
1569 --
1570 -- set to the primary salesrep id of the credited trx
1571 --
1572 p_default_primary_salesrep_id := p_crtrx_primary_salesrep_id;
1573 l_primary_salesrep_id := p_crtrx_primary_salesrep_id;
1574 ELSE
1575 l_primary_salesrep_id := p_primary_salesrep_id;
1576 END IF;
1577
1578 ELSIF (l_bill_to_site_use_id IS NOT NULL
1579 AND
1580 l_primary_salesrep_id IS NULL )
1581 THEN
1582 BEGIN
1583 /* modified for tca uptake */
1584 SELECT s.salesrep_id
1585 INTO l_primary_salesrep_id
1586 FROM ra_salesreps s,
1587 hz_cust_site_uses site_uses
1588 WHERE s.salesrep_id = site_uses.primary_salesrep_id
1589 AND site_uses.site_use_id = l_bill_to_site_use_id
1590 AND p_trx_date BETWEEN nvl(start_date_active, p_trx_date)
1591 AND nvl(end_date_active, p_trx_date);
1592
1593 p_default_primary_salesrep_id := l_primary_salesrep_id;
1594 EXCEPTION
1595 WHEN NO_DATA_FOUND THEN
1596 null;
1597 WHEN OTHERS THEN
1598 RAISE;
1599 END;
1600 END IF;
1601
1602 IF (l_ship_to_site_use_id IS NOT NULL
1603 AND
1604 l_primary_salesrep_id IS NULL )
1605 THEN
1606 BEGIN
1607 /* modified for tca uptake */
1608 SELECT s.salesrep_id
1609 INTO l_primary_salesrep_id
1610 FROM ra_salesreps s,
1611 hz_cust_site_uses site_uses
1612 WHERE s.salesrep_id = site_uses.primary_salesrep_id
1613 AND site_uses.site_use_id = l_ship_to_site_use_id
1614 AND p_trx_date BETWEEN nvl(start_date_active, p_trx_date)
1615 AND nvl(end_date_active, p_trx_date);
1616
1617 p_default_primary_salesrep_id := l_primary_salesrep_id;
1618 EXCEPTION
1619 WHEN NO_DATA_FOUND THEN
1620 null;
1621 WHEN OTHERS THEN
1622 RAISE;
1623 END;
1624 END IF;
1625
1626
1627 IF (p_salesrep_required_flag = 'Y'
1628 AND
1629 l_primary_salesrep_id IS NULL
1630 AND
1631 p_primary_salesrep_id IS NULL )
1632 THEN
1633 p_default_primary_salesrep_id := -3; -- No Sales Credit
1634 END IF;
1635
1636 get_parent_customer_site(l_bill_to_customer_id,
1637 l_parent_customer_id,
1638 l_parent_site_use_id);
1639
1640
1641
1642 IF PG_DEBUG in ('Y', 'C') THEN
1643 arp_util.debug('get_cm_header_defaults: ' || 'bill_to_customer_id : '||l_bill_to_customer_id);
1644 arp_util.debug('get_cm_header_defaults: ' || 'parent_customer_id : '||l_parent_customer_id);
1645 arp_util.debug('get_cm_header_defaults: ' || 'parent_site_use_id : '||l_parent_site_use_id);
1646 arp_util.debug('get_cm_header_defaults: ' || 'receipt_method_id : '||p_receipt_method_id);
1647 END IF;
1648
1649 --
1650 -- check if the receipt method is valid
1651 --
1652 IF (p_receipt_method_id IS NOT NULL)
1653 THEN
1654 IF (check_payment_method(
1655 p_trx_date,
1656 l_bill_to_customer_id,
1657 l_bill_to_site_use_id,
1658 l_parent_customer_id,
1659 l_parent_site_use_id,
1660 p_currency_code,
1661 p_receipt_method_id,
1662 l_payment_method_name,
1663 l_receipt_method_id,
1664 l_creation_method_code
1665 ))
1666 THEN
1667 l_receipt_method_id := p_receipt_method_id;
1668 IF PG_DEBUG in ('Y', 'C') THEN
1669 arp_util.debug('get_cm_header_defaults: ' || 'Receipt method is valid'||p_receipt_method_id);
1670 END IF;
1671 ELSE
1672 l_receipt_method_id := null;
1673 END IF;
1674 ELSE
1675 l_receipt_method_id := null;
1676 END IF;
1677
1678 --
1679 -- default the receipt method
1680 -- credited transaction
1681 -- Primary payment method of the parent site use
1682 -- Primary payment method of the parent customer
1683 -- Primary payment method of the bill to site use
1684 -- Primary payment method of the bill to customer
1685 --
1686
1687 IF (l_receipt_method_id IS NULL)
1688 THEN
1689 IF (check_payment_method(
1690 p_trx_date,
1691 l_bill_to_customer_id,
1692 l_bill_to_site_use_id,
1693 l_parent_customer_id,
1694 l_parent_site_use_id,
1695 p_currency_code,
1696 p_crtrx_receipt_method_id,
1697 l_payment_method_name,
1698 l_receipt_method_id,
1699 l_creation_method_code
1700 ))
1701 THEN
1702 IF PG_DEBUG in ('Y', 'C') THEN
1703 arp_util.debug('get_cm_header_defaults: ' || 'Credited Trx receipt method is valid : '||
1704 p_crtrx_receipt_method_id);
1705 END IF;
1706 ELSE
1707 arp_trx_defaults_3.get_payment_method_default(
1708 p_trx_date,
1709 p_currency_code,
1710 l_parent_customer_id,
1711 l_parent_site_use_id,
1712 l_bill_to_customer_id,
1713 l_bill_to_site_use_id,
1714 l_payment_method_name,
1715 l_receipt_method_id,
1716 l_creation_method_code);
1717 END IF;
1718 END IF;
1719
1720 IF PG_DEBUG in ('Y', 'C') THEN
1721 arp_util.debug('get_cm_header_defaults: ' || 'creation method is : '||l_creation_method_code);
1722 END IF;
1723 IF (l_creation_method_code = 'MANUAL' )
1724 THEN
1725 p_default_cust_bank_account_id := null;
1726 p_default_paying_customer_id := null;
1727 p_default_paying_site_use_id := null;
1728 ELSIF (l_creation_method_code = 'AUTOMATIC' )
1729 THEN
1730 IF (p_customer_bank_account_id IS NOT NULL)
1731 THEN
1732
1733 IF (check_bank_account(
1734 p_trx_date,
1735 p_currency_code,
1736 l_bill_to_customer_id,
1737 l_bill_to_site_use_id,
1738 l_parent_customer_id,
1739 l_parent_site_use_id,
1740 p_customer_bank_account_id,
1741 l_cust_bank_account_id,
1742 l_paying_customer_id))
1743 THEN
1744 IF PG_DEBUG in ('Y', 'C') THEN
1745 arp_util.debug('get_cm_header_defaults: ' || 'Bank account is valid : '||
1746 p_customer_bank_account_id);
1747 END IF;
1748 ELSE
1749 l_cust_bank_account_id := null;
1750 END IF;
1751 END IF;
1752
1753 IF (l_cust_bank_account_id IS NULL
1754 AND
1755 p_crtrx_cust_bank_account_id IS NOT NULL)
1756 THEN
1757 IF (check_bank_account(
1758 p_trx_date,
1759 p_currency_code,
1760 l_bill_to_customer_id,
1761 l_bill_to_site_use_id,
1762 l_parent_customer_id,
1763 l_parent_site_use_id,
1764 p_crtrx_cust_bank_account_id,
1765 l_cust_bank_account_id,
1766 l_paying_customer_id))
1767 THEN
1768 IF PG_DEBUG in ('Y', 'C') THEN
1769 arp_util.debug('get_cm_header_defaults: ' || 'Credited Trx bank account is valid : '||
1770 p_crtrx_cust_bank_account_id);
1771 END IF;
1772 ELSE
1773 l_cust_bank_account_id := null;
1774 END IF;
1775 END IF;
1776
1777 IF (l_cust_bank_account_id IS NULL)
1778 THEN
1779 arp_trx_defaults_3.get_bank_defaults(
1780 p_trx_date,
1781 p_currency_code,
1782 l_parent_customer_id,
1783 l_parent_site_use_id,
1784 l_bill_to_customer_id,
1785 l_bill_to_site_use_id,
1786 l_payment_type_code,
1787 l_cust_bank_account_id,
1788 l_bank_account_num,
1789 l_bank_name,
1790 l_bank_branch_name,
1791 l_bank_branch_id);
1792
1793 -- Modified for bug # 2712726
1794 -- ORASHID
1795 --
1796 l_nocopy_cust_bank_account_id := l_cust_bank_account_id;
1797
1798 IF (l_cust_bank_account_id IS NOT NULL
1799 AND
1800
1801 check_bank_account(
1802 p_trx_date,
1803 p_currency_code,
1804 l_bill_to_customer_id,
1805 l_bill_to_site_use_id,
1806 l_parent_customer_id,
1807 l_parent_site_use_id,
1808 l_nocopy_cust_bank_account_id,
1809 l_cust_bank_account_id,
1810 l_paying_customer_id))
1811 THEN
1812 null;
1813 END IF;
1814 END IF;
1815
1816 p_default_paying_customer_id := l_paying_customer_id;
1817
1818 IF (l_bill_to_customer_id = l_paying_customer_id)
1819 THEN
1820 p_default_paying_site_use_id := l_bill_to_site_use_id;
1821 ELSIF (l_parent_customer_id = l_paying_customer_id)
1822 THEN
1823 p_default_paying_site_use_id := l_parent_site_use_id;
1824 END IF;
1825
1826 END IF;
1827
1828
1829 p_default_receipt_method_id := l_receipt_method_id;
1830 p_default_cust_bank_account_id := l_cust_bank_account_id;
1831
1832 p_default_ship_via := p_crtrx_ship_via;
1833 p_default_ship_date_actual := p_crtrx_ship_date_actual;
1834 p_default_waybill_number := p_crtrx_waybill_number;
1835 p_default_fob_point := p_crtrx_fob_point;
1836
1837 IF PG_DEBUG in ('Y', 'C') THEN
1838 arp_util.debug('arp_process_credit_util.get_cm_header_defaults()-');
1839 END IF;
1840
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 IF PG_DEBUG in ('Y', 'C') THEN
1844 arp_util.debug('EXCEPTION : get_cm_header_defaults');
1845 END IF;
1846 RAISE;
1847
1848 END get_cm_header_defaults;
1849
1850 PROCEDURE init IS
1851 BEGIN
1852
1853 pg_set_of_books_id :=
1854 arp_trx_global.system_info.system_parameters.set_of_books_id;
1855 END init;
1856
1857 BEGIN
1858 init;
1859 END ARP_PROCESS_CREDIT_UTIL;
1860