1 PACKAGE BODY JL_ZZ_AR_LIBRARY_1_PKG AS
2 /* $Header: jlzzrl1b.pls 120.18 2006/11/21 19:06:06 appradha ship $ */
3
4 -- Get customer_trx_id from ar_payment_schedules
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AR_ENABLE_DEBUG_OUTPUT'), 'N');
6
7 PROCEDURE get_customer_trx_id (pay_sched_id IN NUMBER,
8 cust_trx_id IN OUT NOCOPY NUMBER,
9 trans_date IN OUT NOCOPY DATE,
10 row_number IN NUMBER,
11 Errcd IN OUT NOCOPY NUMBER) IS
12 BEGIN
13 Errcd := 0;
14 SELECT arps.customer_trx_id,ract.trx_date
15 INTO cust_trx_id,trans_date
16 FROM ar_payment_schedules arps, ra_customer_trx ract
17 WHERE arps.payment_schedule_id = pay_sched_id
18 AND ract.customer_trx_id = arps.customer_trx_id
19 AND rownum = row_number;
20
21 EXCEPTION
22 WHEN OTHERS THEN
23 Errcd := SQLCODE;
24 END get_customer_trx_id;
25
26 -- See if the amount is with in approval limits
27 PROCEDURE get_amt_within_approval_limits (userid IN NUMBER,
28 amt IN NUMBER,
29 approved_amt IN OUT NOCOPY VARCHAR2,
30 row_number IN NUMBER,
31 Errcd IN OUT NOCOPY NUMBER) IS
32 BEGIN
33 Errcd := 0;
34 SELECT 'Y' approved_yes_no
35 INTO approved_amt
36 FROM ar_approval_user_limits araul,
37 gl_sets_of_books glsb,
38 ar_system_parameters arsp
39 WHERE araul.user_id = userid
40 AND araul.document_type = 'ADJ'
41 AND glsb.set_of_books_id = arsp.set_of_books_id
42 AND araul.currency_code = glsb.currency_code
43 AND araul.amount_to >= NVL(amt,0)
44 AND araul.amount_from <= NVL(amt,0)
45 AND rownum = row_number;
46 EXCEPTION
47 WHEN OTHERS THEN
48 Errcd := SQLCODE;
49 END get_amt_within_approval_limits;
50
51
52 -- Select amounts from jl_br_ar_rec_met_accts_ext table
53 PROCEDURE get_bank_account_amounts (rcpt_mthd IN NUMBER,
54 bnk_acct IN NUMBER,
55 perc_tol IN OUT NOCOPY NUMBER,
56 amt_tol IN OUT NOCOPY NUMBER,
57 writeoff_rectrx IN OUT NOCOPY NUMBER,
58 writeoff_ccid IN OUT NOCOPY NUMBER,
59 rev_rectrx IN OUT NOCOPY NUMBER,
60 rev_ccid IN OUT NOCOPY NUMBER,
61 calc_intr_ccid IN OUT NOCOPY NUMBER,
62 calc_intr_rectx_id IN OUT NOCOPY NUMBER,
63 row_number IN NUMBER,
64 Errcd IN OUT NOCOPY NUMBER) IS
65 BEGIN
66 Errcd := 0;
67 SELECT writeoff_perc_tolerance,
68 writeoff_amount_tolerance,
69 interest_writeoff_rectrx_id,
70 interest_writeoff_ccid,
71 interest_revenue_rectrx_id,
72 interest_revenue_ccid,
73 calculated_interest_ccid,
74 calculated_interest_rectrx_id
75 INTO perc_tol, amt_tol, writeoff_rectrx,
76 writeoff_ccid, rev_rectrx, rev_ccid,
77 calc_intr_ccid, calc_intr_rectx_id
78 FROM jl_br_ar_rec_met_accts_ext
79 WHERE receipt_method_id = rcpt_mthd
80 AND bank_acct_use_id = bnk_acct
81 AND rownum = row_number;
82 EXCEPTION
83 WHEN OTHERS THEN
84 Errcd := SQLCODE;
85 END get_bank_account_amounts;
86
87
88 PROCEDURE get_sum_adjustment_amounts (pay_sched_id IN NUMBER,
89 amount_adjusted IN OUT NOCOPY NUMBER,
90 row_number IN NUMBER,
91 Errcd IN OUT NOCOPY NUMBER) IS
92 BEGIN
93 Errcd := 0;
94 SELECT Sum(Amount)
95 INTO amount_adjusted
96 FROM ar_adjustments
97 WHERE payment_schedule_id = pay_sched_id;
98 EXCEPTION
99 WHEN OTHERS THEN
100 Errcd := SQLCODE;
101 END get_sum_adjustment_amounts;
102
103
104 -- Get IDM profiles GA17/16/18 from ar_system_parameters
105 PROCEDURE get_idm_profiles_from_syspa (trx_type IN OUT NOCOPY VARCHAR2,
106 batch_source IN OUT NOCOPY VARCHAR2,
107 receipt_method IN OUT NOCOPY VARCHAR2,
108 row_number IN NUMBER,
109 Errcd IN OUT NOCOPY NUMBER) IS
110 BEGIN
111 Errcd := 0;
112 SELECT global_attribute17, global_attribute16, global_attribute18
113 INTO trx_type, batch_source, receipt_method
114 FROM ar_system_parameters
115 WHERE rownum = row_number;
116 EXCEPTION
117 WHEN OTHERS THEN
118 Errcd := SQLCODE;
119 END get_idm_profiles_from_syspa;
120
121
122 -- Get interest_payment_date from ar_payment_schedules
123 PROCEDURE get_interest_payment_date (pay_schd_id IN NUMBER,
124 interest_payment_date IN OUT NOCOPY VARCHAR2,
125 row_number IN NUMBER,
126 Errcd IN OUT NOCOPY NUMBER) IS
127 BEGIN
128 Errcd := 0;
129 SELECT MAX (global_attribute7)
130 INTO interest_payment_date
131 FROM ar_payment_schedules
132 WHERE payment_schedule_id = pay_schd_id;
133 EXCEPTION
134 WHEN OTHERS THEN
135 Errcd := SQLCODE;
136 END get_interest_payment_date;
137
138
139 -- Get Global Attributes 1..7 from ra_customer_trx
140 PROCEDURE get_customer_interest_dtls (cust_trx_id IN NUMBER,
141 interest_type IN OUT NOCOPY VARCHAR2,
142 interest_rate_amount IN OUT NOCOPY NUMBER,
143 interest_period IN OUT NOCOPY NUMBER,
144 interest_formula IN OUT NOCOPY VARCHAR2,
145 interest_grace_days IN OUT NOCOPY NUMBER,
146 penalty_type IN OUT NOCOPY VARCHAR2,
147 penalty_rate_amount IN OUT NOCOPY NUMBER,
148 row_number IN NUMBER,
149 Errcd IN OUT NOCOPY NUMBER) IS
150 BEGIN
151 Errcd := 0;
152 /* Bug 3465021 - Added fnd_number.canonical_to_number api to amount related GDFs */
153 SELECT SUBSTR (global_attribute1, 1, 15),
154 fnd_number.canonical_to_number(SUBSTR (global_attribute2, 1, 38)),
155 fnd_number.canonical_to_number(SUBSTR (global_attribute3, 1, 15)),
156 SUBSTR (global_attribute4, 1, 30),
157 fnd_number.canonical_to_number(SUBSTR (global_attribute5, 1, 4)),
158 SUBSTR (global_attribute6, 1, 15),
159 fnd_number.canonical_to_number(SUBSTR (global_attribute7, 1, 38))
160 INTO interest_type, interest_rate_amount, interest_period, interest_formula,
161 interest_grace_days, penalty_type, penalty_rate_amount
162 FROM ra_customer_trx
163 WHERE customer_trx_id = cust_trx_id
164 AND rownum = row_number;
165 EXCEPTION
166 WHEN OTHERS THEN
167 Errcd := SQLCODE;
168 END get_customer_interest_dtls;
169
170
171 PROCEDURE get_city_from_ra_addresses (pay_sched_id IN NUMBER,
172 city IN OUT NOCOPY VARCHAR2,
173 row_number IN NUMBER,
174 Errcd IN OUT NOCOPY NUMBER) IS
175 BEGIN
176 Errcd := 0;
177 SELECT loc.city
178 INTO city
179 FROM ar_payment_schedules arps,
180 hz_cust_acct_sites ad,
181 hz_cust_site_uses hzsu,
182 ra_customer_trx ract,
183 -- ra_site_uses rasu,
184 hz_locations loc,
185 hz_party_sites pty
186 WHERE arps.payment_schedule_id = pay_sched_id
187 AND ract.customer_trx_id = arps.customer_trx_id
188 AND hzsu.site_use_id = ract.bill_to_site_use_id
189 AND ad.cust_acct_site_id = hzsu.cust_acct_site_id
190 AND ad.party_site_id = pty.party_site_id
191 AND loc.location_id = pty.location_id
192 AND rownum = row_number;
193 EXCEPTION
194 WHEN OTHERS THEN
195 Errcd := SQLCODE;
196 END get_city_from_ra_addresses;
197
198
199 -- Get Record Count for the cash_receipt_id for LOOPing purposes
200 PROCEDURE get_total_receipts (cash_rcpt_id IN NUMBER,
201 tot_rec IN OUT NOCOPY NUMBER,
202 row_number IN NUMBER,
203 Errcd IN OUT NOCOPY NUMBER) IS
204 BEGIN
205 Errcd := 0;
206 SELECT COUNT (*)
207 INTO tot_rec
208 FROM ar_receivable_applications
209 WHERE cash_receipt_id = cash_rcpt_id;
210 EXCEPTION
211 WHEN OTHERS THEN
212 Errcd := SQLCODE;
213 END get_total_receipts;
214
215
216 -- Get Status
217 PROCEDURE get_status_amount_due (amt_due_remain_char IN VARCHAR2,
218 status IN OUT NOCOPY VARCHAR2,
219 row_number IN NUMBER,
220 Errcd IN OUT NOCOPY NUMBER) IS
221 BEGIN
222 Errcd := 0;
223 SELECT DECODE (amt_due_remain_char, '0', 'CL', 'OP')
224 INTO status
225 FROM dual
226 WHERE rownum = row_number;
227 EXCEPTION
228 WHEN OTHERS THEN
229 Errcd := SQLCODE;
230 END get_status_amount_due;
231
232
233 -- Get GL Date Closed
234 PROCEDURE get_gl_date_closed (amt_due_remain_char IN VARCHAR2,
235 gl_date IN VARCHAR2,
236 gl_date_closed IN OUT NOCOPY VARCHAR2,
237 row_number IN NUMBER,
238 Errcd IN OUT NOCOPY NUMBER) IS
239 BEGIN
240 Errcd := 0;
241 SELECT DECODE (amt_due_remain_char, '0', gl_date, NULL)
242 INTO gl_date_closed
243 FROM dual
244 WHERE rownum = row_number;
245 EXCEPTION
246 WHEN OTHERS THEN
247 Errcd := SQLCODE;
248 END get_gl_date_closed;
249
250
251 -- Get Actual date Closed
252 PROCEDURE get_actual_date_closed (amt_due_remain_char IN VARCHAR2,
253 gl_date IN VARCHAR2,
254 actual_date_closed IN OUT NOCOPY VARCHAR2,
255 row_number IN NUMBER,
256 Errcd IN OUT NOCOPY NUMBER) IS
257 BEGIN
258 Errcd := 0;
259 SELECT DECODE (amt_due_remain_char, '0', gl_date, NULL)
260 INTO actual_date_closed
261 FROM dual
262 WHERE rownum = row_number;
263 EXCEPTION
264 WHEN OTHERS THEN
265 Errcd := SQLCODE;
266 END get_actual_date_closed;
267
268
269 PROCEDURE get_count_of_receipt_methods (rcpt_class_id IN NUMBER,
270 total_rec IN OUT NOCOPY NUMBER,
271 row_number IN NUMBER,
272 Errcd IN OUT NOCOPY NUMBER) IS
273 BEGIN
274 Errcd := 0;
275 SELECT COUNT (ext.receipt_method_id)
276 INTO total_rec
277 FROM jl_br_ar_rec_met_accts_ext ext,
278 ar_receipt_methods arm
279 WHERE arm.receipt_method_id = ext.receipt_method_id
280 AND arm.receipt_class_id = rcpt_class_id;
281 EXCEPTION
282 WHEN OTHERS THEN
283 Errcd := SQLCODE;
284 END get_count_of_receipt_methods;
285
286
287 PROCEDURE get_collection_method (rcpt_class_id IN NUMBER,
288 collection_method IN OUT NOCOPY NUMBER,
289 row_number IN NUMBER,
290 Errcd IN OUT NOCOPY NUMBER) IS
291 BEGIN
292 Errcd := 0;
293 SELECT global_attribute1
294 INTO collection_method
295 FROM ar_receipt_classes
296 WHERE receipt_class_id = rcpt_class_id
297 AND rownum = row_number;
298 EXCEPTION
299 WHEN OTHERS THEN
300 Errcd := SQLCODE;
301 END get_collection_method;
302
303
304 PROCEDURE get_print_immediately_flag (print_immediately_flag IN OUT NOCOPY VARCHAR2,
305 row_number IN NUMBER,
306 Errcd IN OUT NOCOPY NUMBER) IS
307 BEGIN
308 Errcd := 0;
309 SELECT global_attribute12
310 INTO print_immediately_flag
311 FROM ar_system_parameters
312 WHERE rownum = row_number;
313 EXCEPTION
314 WHEN OTHERS THEN
315 Errcd := SQLCODE;
316 END get_print_immediately_flag;
317
318
319 PROCEDURE get_count_complete_flag (total_records IN OUT NOCOPY NUMBER,
320 row_number IN NUMBER,
321 Errcd IN OUT NOCOPY NUMBER) IS
322 BEGIN
323 Errcd := 0;
324 SELECT COUNT (rct.complete_flag)
325 INTO total_records
326 FROM ra_customer_trx rct, ra_cust_trx_types rctt
327 WHERE rct.complete_flag = 'Y'
328 AND NVL (rct.printing_count,0) = 0
329 AND NVL (rct.status_trx, 'VD') <> 'VD'
330 AND rctt.cust_trx_type_id = rct.cust_trx_type_id
331 AND rctt.type = 'INV';
332 EXCEPTION
333 WHEN OTHERS THEN
334 Errcd := SQLCODE;
335 END get_count_complete_flag;
336
337
338 PROCEDURE get_cust_trx_type_status (p_cust_trx_type_id IN NUMBER,
339 class IN OUT NOCOPY VARCHAR2,
340 dfstatus IN OUT NOCOPY VARCHAR2,
341 row_number IN NUMBER,
342 Errcd IN OUT NOCOPY NUMBER) IS
343 BEGIN
344 Errcd := 0;
345 SELECT type, default_status
346 INTO class, dfstatus
347 FROM ra_cust_trx_types_all
348 WHERE cust_trx_type_id = p_cust_trx_type_id
349 AND rownum = row_number;
350 EXCEPTION
351 WHEN OTHERS THEN
352 Errcd := SQLCODE;
353 END get_cust_trx_type_status;
354
355 -- Bug 3610797
356 PROCEDURE get_inv_item_details ( fcc_code_type IN VARCHAR2,
357 tran_nat_type IN VARCHAR2,
358 so_org_id IN VARCHAR2,
359 inv_item_id IN NUMBER,
360 fcc_code IN OUT NOCOPY VARCHAR2,
361 tran_nat IN OUT NOCOPY VARCHAR2,
362 item_org IN OUT NOCOPY VARCHAR2,
363 item_ft IN OUT NOCOPY VARCHAR2,
364 fed_trib IN OUT NOCOPY VARCHAR2,
365 sta_trib IN OUT NOCOPY VARCHAR2,
366 row_number IN NUMBER,
367 Errcd IN OUT NOCOPY NUMBER) IS
368 BEGIN
369 Errcd := 0;
370 SELECT fcc.meaning,
371 tn.meaning,
372 mtl.global_attribute3,
373 mtl.global_attribute4,
374 mtl.global_attribute5,
375 mtl.global_attribute6
376 INTO fcc_code,
377 tran_nat,
378 item_org,
379 item_ft,
380 fed_trib,
381 sta_trib
382 FROM mtl_system_items mtl,fnd_lookups fcc , fnd_lookups tn
383 WHERE fcc.lookup_code = SUBSTR(mtl.global_attribute1,1,25)
384 AND fcc.lookup_type = fcc_code_type
385 AND tn.lookup_code = substr(mtl.global_attribute2,1,25)
386 AND tn.lookup_type = tran_nat_type
387 AND mtl.organization_id = so_org_id
388 AND mtl.inventory_item_id = inv_item_id
389 AND rownum = row_number;
390 EXCEPTION
391 WHEN OTHERS THEN
392 Errcd := SQLCODE;
393 END get_inv_item_details;
394
395
396 PROCEDURE get_memo_line_details (p_memo_line_id IN NUMBER,
397 item_org IN OUT NOCOPY VARCHAR2,
398 item_ft IN OUT NOCOPY VARCHAR2,
399 fed_trib IN OUT NOCOPY VARCHAR2,
400 sta_trib IN OUT NOCOPY VARCHAR2,
401 row_number IN NUMBER,
402 Errcd IN OUT NOCOPY NUMBER) IS
403 BEGIN
404 Errcd := 0;
405 SELECT aml.global_attribute3,
406 aml.global_attribute4,
407 aml.global_attribute5,
408 aml.global_attribute6
409 INTO item_org,
410 item_ft,
411 fed_trib,
412 sta_trib
413 FROM ar_memo_lines aml
414 WHERE aml.memo_line_id = p_memo_line_id
415 AND rownum = row_number;
416 EXCEPTION
417 WHEN OTHERS THEN
418 Errcd := SQLCODE;
419 END get_memo_line_details;
420
421
422 PROCEDURE get_next_seq_number (seq_name IN VARCHAR2,
423 seq_no IN OUT NOCOPY NUMBER,
424 row_number IN NUMBER,
425 Errcd IN OUT NOCOPY NUMBER) IS
426 l_trx_num_cursor INTEGER;
427 l_count NUMBER;
428 BEGIN
429 Errcd := 0;
430 l_trx_num_cursor := dbms_sql.open_cursor;
431 dbms_sql.parse(l_trx_num_cursor,
432 'select '||
433 seq_name||'.nextval seq_number '||
434 'from dual ',
435 dbms_sql.NATIVE);
436
437 dbms_sql.define_column(l_trx_num_cursor, 1, seq_no);
438
439 l_count := dbms_sql.execute_and_fetch(l_trx_num_cursor,TRUE);
440
441 dbms_sql.column_value(l_trx_num_cursor, 1, seq_no);
442 dbms_sql.close_cursor(l_trx_num_cursor);
443
444 END get_next_seq_number;
445
446
447
448 PROCEDURE get_bearer_of_trade_note (pay_sched_id IN NUMBER,
449 bearer_tr_note IN OUT NOCOPY VARCHAR2,
450 row_number IN NUMBER,
451 Errcd IN OUT NOCOPY NUMBER) IS
452 BEGIN
453 Errcd := 0;
454 SELECT global_attribute2
455 INTO bearer_tr_note
456 FROM ar_payment_schedules
457 WHERE payment_schedule_id = pay_sched_id
458 AND rownum = row_number;
459 EXCEPTION
460 WHEN OTHERS THEN
461 Errcd := SQLCODE;
462 END get_bearer_of_trade_note;
463
464
465 PROCEDURE get_customer_profile_dtls (bill_to_cust_id IN NUMBER,
466 interest_type IN OUT NOCOPY VARCHAR2,
467 interest_rate_amt IN OUT NOCOPY VARCHAR2,
468 interest_period IN OUT NOCOPY VARCHAR2,
469 interest_formula IN OUT NOCOPY VARCHAR2,
470 interest_grace IN OUT NOCOPY VARCHAR2,
471 penalty_type IN OUT NOCOPY VARCHAR2,
472 penalty_rate_amt IN OUT NOCOPY VARCHAR2,
473 row_number IN NUMBER,
474 Errcd IN OUT NOCOPY NUMBER) IS
475 BEGIN
476 Errcd := 0;
477 SELECT acpc.global_attribute3,
478 acpc.global_attribute4,
479 acpc.global_attribute5,
480 acpc.global_attribute6,
481 acpc.global_attribute7,
482 acpc.global_attribute8,
483 acpc.global_attribute9
484 INTO interest_type,
485 interest_rate_amt,
486 interest_period,
487 interest_formula,
488 interest_grace,
489 penalty_type,
490 penalty_rate_amt
491 FROM hz_cust_profile_classes acpc,
492 hz_customer_profiles acp
493 WHERE acp.cust_account_id = bill_to_cust_id
494 AND acp.profile_class_id = acpc.profile_class_id
495 AND acp.site_use_id is null
496 AND rownum = row_number;
497 EXCEPTION
498 WHEN OTHERS THEN
499 Errcd := SQLCODE;
500 END get_customer_profile_dtls;
501
502
503 PROCEDURE get_batch_id (p_batch_source_id IN NUMBER,
504 batch_id IN OUT NOCOPY NUMBER,
505 row_number IN NUMBER,
506 Errcd IN OUT NOCOPY NUMBER) IS
507 BEGIN
508 Errcd := 0;
509 SELECT global_attribute1
510 INTO batch_id
511 FROM ra_batch_sources
512 WHERE batch_source_id = p_batch_source_id
513 AND rownum = row_number;
514 EXCEPTION
515 WHEN OTHERS THEN
516 Errcd := SQLCODE;
517 END get_batch_id;
518
519
520 PROCEDURE get_tax_base_rate_amt (cust_trx_id IN NUMBER,
521 base_amt IN OUT NOCOPY NUMBER,
522 base_rate IN OUT NOCOPY NUMBER,
523 row_number IN NUMBER,
524 Errcd IN OUT NOCOPY NUMBER) IS
525 BEGIN
526 Errcd := 0;
527 SELECT TO_NUMBER (global_attribute11),
528 TO_NUMBER (global_attribute12)
529 INTO base_amt,
530 base_rate
531 FROM ra_customer_trx_lines
532 WHERE customer_trx_line_id = cust_trx_id
533 AND rownum = row_number;
534 EXCEPTION
535 WHEN OTHERS THEN
536 Errcd := SQLCODE;
537 END get_tax_base_rate_amt;
538
539 PROCEDURE get_issue_date (cust_trx_id IN NUMBER,
540 iss_date IN OUT NOCOPY DATE,
541 row_number IN NUMBER,
542 Errcd IN OUT NOCOPY NUMBER) IS
543 BEGIN
544 Errcd := 0;
545 SELECT global_attribute8
546 INTO iss_date
547 FROM ra_customer_trx
548 WHERE customer_trx_id = cust_trx_id
549 AND rownum = row_number;
550 EXCEPTION
551 WHEN OTHERS THEN
552 Errcd := SQLCODE;
553 END get_issue_date;
554
555 PROCEDURE get_customer_trx_dtls (cust_trx_id IN NUMBER,
556 status IN OUT NOCOPY VARCHAR2,
557 typ_class IN OUT NOCOPY VARCHAR2,
558 row_number IN NUMBER,
559 Errcd IN OUT NOCOPY NUMBER) IS
560 BEGIN
561 Errcd := 0;
562 SELECT rct.status_trx,
563 rctt.type
564 INTO status,
565 typ_class
566 FROM ra_customer_trx_all rct,
567 ra_cust_trx_types_all rctt
568 WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id
569 AND rct.customer_trx_id = cust_trx_id
570 AND rownum = row_number;
571 EXCEPTION
572 WHEN OTHERS THEN
573 Errcd := SQLCODE;
574 END get_customer_trx_dtls;
575
576 PROCEDURE get_class (p_trx_number IN VARCHAR2,
577 p_class IN OUT NOCOPY VARCHAR2,
578 row_number IN NUMBER,
579 Errcd IN OUT NOCOPY NUMBER) IS
580 BEGIN
581 Errcd := 0;
582 SELECT class
583 INTO p_class
584 FROM ar_payment_schedules
585 WHERE trx_number = p_trx_number;
586 EXCEPTION
587 WHEN OTHERS THEN
588 Errcd := SQLCODE;
589 END get_class;
590
591 PROCEDURE get_prev_interest_values(p_applied_payment_schedule_id IN NUMBER,
592 p_cash_receipt_id IN NUMBER,
593 p_apply_date IN DATE,
594 p_main_amnt_rec OUT NOCOPY VARCHAR2,
595 p_base_int_calc OUT NOCOPY VARCHAR2,
596 p_calculated_interest OUT NOCOPY VARCHAR2,
597 p_received_interest OUT NOCOPY VARCHAR2,
598 p_int_diff_action OUT NOCOPY VARCHAR2,
599 p_int_writeoff_reason OUT NOCOPY VARCHAR2,
600 p_payment_date OUT NOCOPY VARCHAR2,
601 p_writeoff_date OUT NOCOPY VARCHAR2,
602 Errcd IN OUT NOCOPY NUMBER) IS
603
604 x_apply_date DATE;
605 flag BOOLEAN;
606
607 BEGIN
608
609 Errcd := 0;
610 flag := TRUE;
611
612 BEGIN
613
614 SELECT MAX(apply_date)
615 INTO x_apply_date
616 FROM ar_receivable_applications
617 WHERE applied_payment_schedule_id = p_applied_payment_schedule_id
618 AND application_type = 'CASH'
619 AND status = 'APP'
620 AND confirmed_flag = 'Y'
621 AND reversal_gl_date IS NULL
622 AND apply_date < p_apply_date
623 AND cash_receipt_id <> p_cash_receipt_id;
624
625 EXCEPTION
626 WHEN NO_DATA_FOUND THEN
627 flag := FALSE;
628 WHEN OTHERS THEN
629 IF PG_DEBUG in ('Y', 'C') THEN
630 arp_util.debug('get_prev_interest_values: ' || to_char(SQLCODE));
631 END IF;
632 Errcd := SQLCODE;
633
634 END;
635
636 IF Errcd = 0 THEN
637 IF flag THEN
638 BEGIN
639 SELECT global_attribute1,
640 global_attribute2,
641 global_attribute3,
642 global_attribute4,
643 global_attribute5,
644 global_attribute6,
645 global_attribute7,
646 global_attribute8
647 INTO p_main_amnt_rec,
648 p_base_int_calc,
649 p_calculated_interest,
650 p_received_interest,
651 p_int_diff_action,
652 p_int_writeoff_reason,
653 p_payment_date,
654 p_writeoff_date
655 FROM ar_receivable_applications
656 WHERE applied_payment_schedule_id = p_applied_payment_schedule_id
657 AND application_type = 'CASH'
658 AND status = 'APP'
659 AND confirmed_flag = 'Y'
660 AND reversal_gl_date IS NULL
661 AND apply_date = x_apply_date;
662 EXCEPTION
663 WHEN OTHERS THEN
664 IF PG_DEBUG in ('Y', 'C') THEN
665 arp_util.debug('get_prev_interest_values: ' || to_char(SQLCODE));
666 END IF;
667 Errcd := SQLCODE;
668 END;
669 ELSE
670 p_main_amnt_rec := '';
671 p_base_int_calc := '';
672 p_calculated_interest := '';
673 p_received_interest := '';
674 p_int_diff_action := '';
675 p_int_writeoff_reason := '';
676 p_payment_date := '';
677 p_writeoff_date := '';
678 END IF;
679 END IF;
680
681 END get_prev_interest_values;
682
683 PROCEDURE get_interest_reversal_flag(p_cash_receipt_id IN NUMBER,
684 p_interest_reversal OUT NOCOPY BOOLEAN,
685 Errcd IN OUT NOCOPY NUMBER) IS
686
687 Cursor pay_sched is
688 SELECT applied_payment_schedule_id,
689 nvl(global_attribute3,0) calculated_interest,
690 nvl(global_attribute4,0) received_interest,
691 apply_date
692 FROM ar_receivable_applications
693 where cash_receipt_id = p_cash_receipt_id;
694
695 ps_rec pay_sched%ROWTYPE;
696 revcode NUMBER;
697
698 BEGIN
699
700 Errcd := 0;
701 revcode := 0;
702
703 OPEN pay_sched;
704 LOOP
705 FETCH pay_sched INTO ps_rec;
706 EXIT WHEN pay_sched%NOTFOUND
707 OR pay_sched%NOTFOUND IS NULL;
708 IF ps_rec.calculated_interest = 0 THEN
709 p_interest_reversal := TRUE;
710 ELSE
711 BEGIN
712 SELECT 1
713 INTO revcode
714 FROM ar_receivable_applications
715 WHERE applied_payment_schedule_id = ps_rec.applied_payment_schedule_id
716 AND status = 'APP'
717 AND cash_receipt_id <> p_cash_receipt_id
718 AND apply_date between ps_rec.apply_date and sysdate
719 AND reversal_gl_date IS NULL;
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 revcode := 0;
723 p_interest_reversal := TRUE;
724 WHEN TOO_MANY_ROWS THEN
725 revcode := 0;
726 p_interest_reversal := FALSE;
727 WHEN OTHERS THEN
728 IF PG_DEBUG in ('Y', 'C') THEN
729 arp_util.debug('get_interest_reversal_flag: ' || to_char(SQLCODE));
730 END IF;
731 Errcd := SQLCODE;
732 revcode := 0;
733 END;
734
735 IF revcode = 1 THEN
736 p_interest_reversal := FALSE;
737 END IF;
738
739 IF NOT p_interest_reversal THEN
740 EXIT;
741 END IF;
742
743 BEGIN
744 SELECT 1
745 INTO revcode
746 FROM ar_adjustments
747 WHERE payment_schedule_id = ps_rec.applied_payment_schedule_id
748 AND associated_cash_receipt_id = p_cash_receipt_id
749 AND gl_posted_date IS NULL;
750 EXCEPTION
751 WHEN NO_DATA_FOUND THEN
752 revcode := 0;
753 p_interest_reversal := FALSE;
754 WHEN TOO_MANY_ROWS THEN
755 revcode := 0;
756 p_interest_reversal := TRUE;
757 WHEN OTHERS THEN
758 IF PG_DEBUG in ('Y', 'C') THEN
759 arp_util.debug('get_interest_reversal_flag: ' || to_char(SQLCODE));
760 END IF;
761 revcode := 0;
762 Errcd := -1;
763 END;
764
765 IF revcode = 1 THEN
766 p_interest_reversal := TRUE;
767 END IF;
768
769 IF NOT p_interest_reversal THEN
770 EXIT;
771 END IF;
772
773 END IF;
774 END LOOP;
775 CLOSE pay_sched;
776
777 END get_interest_reversal_flag;
778
779 PROCEDURE get_adjustment_record(p_adj_rec IN OUT NOCOPY ar_adjustments%ROWTYPE,
780 p_user_id IN NUMBER,
781 p_amount IN NUMBER,
782 p_receipt_date IN DATE,
783 p_cash_receipt_id IN NUMBER,
784 p_customer_trx_id IN NUMBER,
785 p_pay_sched_id IN NUMBER,
786 p_rectrx_id IN NUMBER,
787 p_status IN VARCHAR2,
788 Errcd IN OUT NOCOPY NUMBER) IS
789 BEGIN
790
791 Errcd := 0;
792
793 SELECT p_user_id,
794 sysdate,
795 p_user_id,
796 p_user_id,
797 sysdate,
798 p_amount,
799 sysdate,
800 p_receipt_date,
801 arsp.set_of_books_id,
802 'CHARGES',
803 decode( p_status,'Y','A','M'),
804 p_status ,
805 p_cash_receipt_id,
806 p_customer_trx_id,
807 p_pay_sched_id,
808 p_rectrx_id,
809 'ARXRWMAI',
810 decode( p_status ,'Y','Y','N'),
811 decode( p_status ,'Y',p_user_id,NULL),
812 -3,
813 p_amount
814 INTO p_adj_rec.LAST_UPDATED_BY,
815 p_adj_rec.LAST_UPDATE_DATE,
816 p_adj_rec.LAST_UPDATE_LOGIN,
817 p_adj_rec.CREATED_BY,
818 p_adj_rec.CREATION_DATE,
819 p_adj_rec.AMOUNT,
820 p_adj_rec.APPLY_DATE,
821 p_adj_rec.GL_DATE,
822 p_adj_rec.SET_OF_BOOKS_ID,
823 p_adj_rec.TYPE,
824 p_adj_rec.ADJUSTMENT_TYPE,
825 p_adj_rec.STATUS,
826 p_adj_rec.ASSOCIATED_CASH_RECEIPT_ID,
827 p_adj_rec.CUSTOMER_TRX_ID,
828 p_adj_rec.PAYMENT_SCHEDULE_ID,
829 p_adj_rec.RECEIVABLES_TRX_ID,
830 p_adj_rec.CREATED_FROM,
831 p_adj_rec.POSTABLE,
832 p_adj_rec.APPROVED_BY,
833 p_adj_rec.POSTING_CONTROL_ID,
834 p_adj_rec.ACCTD_AMOUNT
835 FROM ar_system_parameters arsp;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 Errcd := SQLCODE;
840
841 END get_adjustment_record;
842
843 PROCEDURE get_warehouse_info(p_customer_trx_id IN NUMBER,
844 p_warehouse_count OUT NOCOPY NUMBER) IS
845
846 BEGIN
847 SELECT count(*)
848 INTO p_warehouse_count
849 FROM ra_customer_trx_lines
850 WHERE customer_trx_id = p_customer_trx_id
851 AND line_type = 'LINE'
852 AND warehouse_id IS NULL
853 AND inventory_item_id IS NOT NULL;
854
855 END get_warehouse_info;
856
857 PROCEDURE get_warehouse_id(p_customer_trx_id IN NUMBER,
858 p_warehouse_id OUT NOCOPY NUMBER) IS
859
860 BEGIN
861 SELECT distinct warehouse_id
862 INTO p_warehouse_id
863 FROM ra_customer_trx_lines
864 WHERE customer_trx_id = p_customer_trx_id
865 AND line_type = 'LINE'
866 AND inventory_item_id IS NOT NULL
867 AND rownum = 1;
868
869 EXCEPTION WHEN OTHERS THEN
870 p_warehouse_id := NULL;
871
872 END get_warehouse_id;
873
874
875 PROCEDURE get_void_trx_type_id(p_country_code IN VARCHAR2,
876 p_void_trx_type_id OUT NOCOPY NUMBER,
877 Errcd IN OUT NOCOPY NUMBER) IS
878
879 l_category VARCHAR2(30);
880
881 BEGIN
882
883 IF p_country_code = 'CL' THEN
884 l_category := 'JL.CL.RAXSUCTT.CUST_TRX_TYPES';
885 ELSIF p_country_code = 'AR' THEN
886 l_category := 'JL.AR.RAXSUCTT.CUST_TRX_TYPES';
887 ELSIF p_country_code = 'CO' THEN
888 l_category := 'JL.CO.RAXSUCTT.CUST_TRX_TYPES';
889 END IF;
890
891 SELECT cust_trx_type_id
892 INTO p_void_trx_type_id
893 FROM ra_cust_trx_types ct
894 WHERE ct.global_attribute_category = l_category
895 AND ct.global_attribute6 = 'Y';
896
897 EXCEPTION
898 WHEN no_data_found THEN
899 p_void_trx_type_id := 0;
900 WHEN OTHERS THEN
901 Errcd := SQLCODE;
902
903 END get_void_trx_type_id;
904
905 PROCEDURE get_city_from_ra_addresses (pay_sched_id IN NUMBER,
906 city IN OUT NOCOPY VARCHAR2,
907 row_number IN NUMBER,
908 Errcd IN OUT NOCOPY NUMBER,
909 state IN OUT NOCOPY VARCHAR2) IS --Bug 2319552
910 BEGIN
911 Errcd := 0;
912 SELECT loc.city,
913 loc.state --Bug 2319552
914 INTO city,
915 state --Bug 2319552
916 FROM ar_payment_schedules arps,
917 hz_cust_acct_sites ad,
918 hz_cust_site_uses hzsu,
919 ra_customer_trx ract,
920 --ra_site_uses rasu,
921 hz_locations loc,
922 hz_party_sites pty
923 WHERE arps.payment_schedule_id = pay_sched_id
924 AND ract.customer_trx_id = arps.customer_trx_id
925 AND hzsu.site_use_id = ract.bill_to_site_use_id
926 AND ad.cust_acct_site_id = hzsu.cust_acct_site_id
927 AND ad.party_site_id = pty.party_site_id
928 AND loc.location_id = pty.location_id
929 AND rownum = row_number;
930 EXCEPTION
931 WHEN OTHERS THEN
932 Errcd := SQLCODE;
933 END get_city_from_ra_addresses;
934
935 PROCEDURE update_doc_status(p_cash_receipt_id IN NUMBER) IS
936 Cursor c1(p_rec_id NUMBER) is SELECT receivable_application_id, global_attribute12
937 FROM ar_receivable_applications_all
938 WHERE cash_receipt_id = p_rec_id;
939 BEGIN
940
941 For rec in c1(p_cash_receipt_id) loop
942
943 update jl_br_ar_collection_docs_all set document_status = 'SELECTED'
944 where document_id = to_number(rec.global_attribute12);
945
946 END LOOP;
947 EXCEPTION
948 WHEN Others then
949 null;
950 END update_doc_status;
951
952 PROCEDURE get_dbms_sql_native (x_dbms_sql_native OUT NOCOPY INTEGER) IS --Bugs 2952004 / 2939830
953 BEGIN
954 x_dbms_sql_native := DBMS_SQL.NATIVE;
955 END get_dbms_sql_native;
956
957 END JL_ZZ_AR_LIBRARY_1_PKG;
[Home] [Help]