[Home] [Help]
1: PACKAGE BODY AR_CALC_LATE_CHARGE AS
2: /* $Header: ARCALATB.pls 120.40.12020000.7 2012/12/14 11:07:20 vpotti ship $ */
3:
4: l_debug_flag varchar2(1);
5: pg_last_updated_by number;
136: l_interest number;
137: BEGIN
138:
139: IF l_debug_flag = 'Y' THEN
140: debug('ar_calc_late_charge.Calculate_Interest()+ ');
141: debug('Input Parameters.... ');
142: debug('p_payment_schedule_id : '||p_payment_schedule_id);
143: debug('p_amount : '|| p_amount);
144: debug('p_formula : '||p_formula);
173: debug('l_interest before currency rounding : '||l_interest);
174: END IF;
175:
176: IF l_interest <> 0 THEN
177: l_interest := ar_calc_late_charge.currency_round(l_interest,p_currency);
178: END IF;
179:
180: IF l_debug_flag = 'Y' THEN
181: debug('l_interest after currency rounding : '||l_interest);
178: END IF;
179:
180: IF l_debug_flag = 'Y' THEN
181: debug('l_interest after currency rounding : '||l_interest);
182: debug('ar_calc_late_charge.Calculate_Interest()- ');
183: END IF;
184:
185: return l_interest;
186:
301: 'FIXED_AMOUNT',0,
302: 'CHARGE_PER_TIER',0 ,
303: decode(sched_hdrs.schedule_header_type,
304: 'AMOUNT',0,
305: ar_calc_late_charge.calculate_interest(
306: decode(b.charge_on_finance_charge_flag,
307: 'F',0,b.overdue_amt),
308: b.charge_on_finance_charge_flag,
309: least(decode(b.multiple_interest_rates_flag,
342: from (
343: select
344: ps.customer_id,
345: decode(ps.class,
346: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
347: ps.customer_site_use_id,
348: ps.org_id),
349: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
350: ps.customer_site_use_id,
345: decode(ps.class,
346: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
347: ps.customer_site_use_id,
348: ps.org_id),
349: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
350: ps.customer_site_use_id,
351: ps.org_id),
352: ps.customer_site_use_id) customer_site_use_id,
353: ps.invoice_currency_code,
411: ar_lc_cust_sites_t cust_site,
412: ar_late_charge_cust_balance_gt bal
413: where ps.customer_id = cust_site.customer_id
414: and cust_site.customer_site_use_id = decode(ps.class,
415: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
416: ps.customer_site_use_id,
417: ps.org_id),
418: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
419: ps.customer_site_use_id,
414: and cust_site.customer_site_use_id = decode(ps.class,
415: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
416: ps.customer_site_use_id,
417: ps.org_id),
418: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
419: ps.customer_site_use_id,
420: ps.org_id),
421: ps.customer_site_use_id)
422: and ps.invoice_currency_code = cust_site.currency_code
451: and cr.reversal_date < l_fin_charge_date
452: and cr.reversal_date > ps.last_charge_date
453: group by ps.customer_id,
454: decode(ps.class,
455: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
456: ps.customer_site_use_id,
457: ps.org_id),
458: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
459: ps.customer_site_use_id,
454: decode(ps.class,
455: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
456: ps.customer_site_use_id,
457: ps.org_id),
458: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
459: ps.customer_site_use_id,
460: ps.org_id),
461: ps.customer_site_use_id),
462: ps.invoice_currency_code,
919: into l_next_date
920: from ar_payment_schedules ps
921: where customer_id = p_customer_id
922: and decode(ps.class,
923: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
924: ps.customer_site_use_id,
925: ps.org_id),
926: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
927: ps.customer_site_use_id,
922: and decode(ps.class,
923: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
924: ps.customer_site_use_id,
925: ps.org_id),
926: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
927: ps.customer_site_use_id,
928: ps.org_id),
929: ps.customer_site_use_id) = p_site_use_id
930: and ps.invoice_currency_code = p_currency_code
1145: select sum(amount_due_original)
1146: from ar_payment_schedules ps
1147: where ps.customer_id = p_customer_id
1148: and decode(ps.class,
1149: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1150: ps.customer_site_use_id,
1151: ps.org_id),
1152: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1153: ps.customer_site_use_id,
1148: and decode(ps.class,
1149: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1150: ps.customer_site_use_id,
1151: ps.org_id),
1152: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1153: ps.customer_site_use_id,
1154: ps.org_id),
1155: ps.customer_site_use_id) = p_site_use_id
1156: and ps.invoice_currency_code = p_currency_code
1217: l_cust_threshold NUMBER;
1218:
1219: BEGIN
1220: IF l_debug_flag = 'Y' THEN
1221: debug('ar_calc_late_charge.check_adb_eligibility()+');
1222: debug('Customer_id : '||p_customer_id);
1223: debug('Site Use ID : '||p_site_use_id);
1224: debug('Currency_code : '||p_currency_code);
1225: debug('Org ID : '||p_org_id);
1269: and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
1270: and ci.status in ('FINAL', 'ACCEPTED','IMPORTED')
1271: and ps.customer_id = ci.customer_id
1272: and decode(ps.class,
1273: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1274: ps.customer_site_use_id,
1275: ps.org_id),
1276: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1277: ps.customer_site_use_id,
1272: and decode(ps.class,
1273: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1274: ps.customer_site_use_id,
1275: ps.org_id),
1276: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1277: ps.customer_site_use_id,
1278: ps.org_id),
1279: ps.customer_site_use_id) = ci.site_use_id
1280: and ps.invoice_currency_code = ci.currency_code
1305: END IF;
1306: return 'N';
1307: END IF;
1308: IF l_debug_flag = 'Y' THEN
1309: debug('ar_calc_late_charge.check_adb_eligibility()-');
1310: END IF;
1311: EXCEPTION WHEN OTHERS THEN
1312: --IF l_debug_flag = 'Y' THEN
1313: debug('EXCEPTION : ar_calc_late_charge.check_adb_eligibility()');
1309: debug('ar_calc_late_charge.check_adb_eligibility()-');
1310: END IF;
1311: EXCEPTION WHEN OTHERS THEN
1312: --IF l_debug_flag = 'Y' THEN
1313: debug('EXCEPTION : ar_calc_late_charge.check_adb_eligibility()');
1314: --END IF;
1315: return 'N';
1316:
1317: END check_adb_eligibility;
1332: into l_first_activity_date
1333: from ar_payment_schedules ps
1334: where ps.customer_id = p_customer_id
1335: and decode(ps.class,
1336: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1337: ps.customer_site_use_id,
1338: ps.org_id),
1339: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1340: ps.customer_site_use_id,
1335: and decode(ps.class,
1336: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1337: ps.customer_site_use_id,
1338: ps.org_id),
1339: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1340: ps.customer_site_use_id,
1341: ps.org_id),
1342: ps.customer_site_use_id) = p_site_use_id
1343: and ps.invoice_currency_code = p_currency_code
1408: l_ignore INTEGER;
1409: l_first_rec BOOLEAN;
1410: BEGIN
1411: IF l_debug_flag = 'Y' THEN
1412: debug( 'ar_calc_late_charge.get_cust_late_charge_policy()+' );
1413: END IF;
1414:
1415: l_org_id := p_org_id;
1416: l_customer_name_from := p_customer_name_from;
1569: from
1570: (select distinct
1571: ps.customer_id,
1572: decode(ps.class,
1573: ''PMT'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1574: ps.customer_site_use_id,
1575: ps.org_id),
1576: ''BR'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1577: ps.customer_site_use_id,
1572: decode(ps.class,
1573: ''PMT'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1574: ps.customer_site_use_id,
1575: ps.org_id),
1576: ''BR'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1577: ps.customer_site_use_id,
1578: ps.org_id),
1579: ps.customer_site_use_id) customer_site_use_id,
1580: ps.invoice_currency_code currency_code,
1643: AND ps.customer_trx_id = trx.customer_trx_id(+)
1644: AND nvl(ps.last_charge_date,ps.due_date) < :l_fin_charge_date
1645: AND nvl(trx.finance_charges,decode(ps.class,''DEP'',''N'',''Y'')) = ''Y''
1646: AND profiles.cust_account_id = cust_acct.cust_account_id
1647: AND ((ar_calc_late_charge.get_profile_class_site_use_id
1648: (decode(:l_use_late_charge_site ,
1649: ''Y'',ar_calc_late_charge.get_late_charge_site(ps.customer_id,
1650: ps.org_id),
1651: ''N'',ps.customer_site_use_id)
1645: AND nvl(trx.finance_charges,decode(ps.class,''DEP'',''N'',''Y'')) = ''Y''
1646: AND profiles.cust_account_id = cust_acct.cust_account_id
1647: AND ((ar_calc_late_charge.get_profile_class_site_use_id
1648: (decode(:l_use_late_charge_site ,
1649: ''Y'',ar_calc_late_charge.get_late_charge_site(ps.customer_id,
1650: ps.org_id),
1651: ''N'',ps.customer_site_use_id)
1652: ,ps.org_id) IS NULL
1653: and profiles.site_use_id is null)
1650: ps.org_id),
1651: ''N'',ps.customer_site_use_id)
1652: ,ps.org_id) IS NULL
1653: and profiles.site_use_id is null)
1654: OR profiles.site_use_id = ar_calc_late_charge.get_profile_class_site_use_id
1655: (decode(:l_use_late_charge_site,
1656: ''Y'',ar_calc_late_charge.get_late_charge_site(ps.customer_id,
1657: ps.org_id),
1658: ''N'',ps.customer_site_use_id)
1652: ,ps.org_id) IS NULL
1653: and profiles.site_use_id is null)
1654: OR profiles.site_use_id = ar_calc_late_charge.get_profile_class_site_use_id
1655: (decode(:l_use_late_charge_site,
1656: ''Y'',ar_calc_late_charge.get_late_charge_site(ps.customer_id,
1657: ps.org_id),
1658: ''N'',ps.customer_site_use_id)
1659: ,ps.org_id))
1660: AND profiles.interest_charges = ''Y''
1660: AND profiles.interest_charges = ''Y''
1661: AND profiles.cust_account_profile_id = prof_amts.cust_account_profile_id
1662: AND prof_amts.currency_code = ps.invoice_currency_code
1663: AND site_use.site_use_id (+) = decode(ps.class,
1664: ''PMT'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1665: ps.customer_site_use_id,
1666: ps.org_id),
1667: ''BR'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1668: ps.customer_site_use_id,
1663: AND site_use.site_use_id (+) = decode(ps.class,
1664: ''PMT'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1665: ps.customer_site_use_id,
1666: ps.org_id),
1667: ''BR'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1668: ps.customer_site_use_id,
1669: ps.org_id),
1670: ps.customer_site_use_id)
1671: AND mod(nvl(ps.customer_site_use_id,0),:l_total_workers) =
1771: AND cust_acct.party_id = party.party_id
1772: );
1773:
1774: IF l_debug_flag = 'Y' THEN
1775: debug( 'ar_calc_late_charge.get_cust_late_charge_policy()-' );
1776: END IF;
1777:
1778: EXCEPTION
1779: WHEN OTHERS THEN
1778: EXCEPTION
1779: WHEN OTHERS THEN
1780: --IF l_debug_flag = 'Y' THEN
1781: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
1782: debug('EXCEPTION: ar_calc_late_charge.get_cust_late_charge_policy' );
1783: --END IF;
1784: RAISE;
1785: END get_cust_late_charge_policy;
1786:
1791: l_worker_number number;
1792: l_total_workers number;
1793: BEGIN
1794: IF l_debug_flag = 'Y' THEN
1795: debug( 'ar_calc_late_charge.insert_credit_amount()+' );
1796: END IF;
1797:
1798: l_fin_charge_date := p_fin_charge_date;
1799: l_worker_number := p_worker_number;
1824: /* The receipt balance is taken as -1*amount_due_remaining from ar_payment
1825: schedules as receipt date is considered for calculating the balances of
1826: the transaction and not the application date */
1827: from (select cr.pay_from_customer customer_id,
1828: ar_calc_late_charge.get_bill_to_site_use_id(cr.pay_from_customer,
1829: cr.customer_site_use_id,
1830: cr.org_id) customer_site_use_id,
1831: cr.currency_code,
1832: cr.legal_entity_id,
1840: and ps.class ='PMT'
1841: and ps.trx_date <= l_fin_charge_date
1842: and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
1843: and ps.customer_id = cust_site.customer_id
1844: and ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
1845: ps.customer_site_use_id,
1846: ps.org_id) = cust_site.customer_site_use_id
1847: and ps.invoice_currency_code = cust_site.currency_code
1848: and ps.org_id = cust_site.org_id
1875: trx.legal_entity_id,
1876: ps.org_id,
1877: /* Always get the true balance of the CM as of the finance charge date. For that,
1878: p_charge_on_finance_charge_flag is passed as Y */
1879: -1*ar_calc_late_charge.get_balance_as_of(ps.payment_schedule_id,
1880: l_fin_charge_date,
1881: 'CM',
1882: 'Y') balance_due
1883: from ar_payment_schedules ps,
1924: legal_entity_id,
1925: org_id);
1926:
1927: IF l_debug_flag = 'Y' THEN
1928: debug( 'ar_calc_late_charge.insert_credit_amount()-' );
1929: END IF;
1930: EXCEPTION
1931: WHEN OTHERS THEN
1932: --IF l_debug_flag = 'Y' THEN
1930: EXCEPTION
1931: WHEN OTHERS THEN
1932: --IF l_debug_flag = 'Y' THEN
1933: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
1934: debug('EXCEPTION: ar_calc_late_charge.insert_credit_amount' );
1935: --END IF;
1936: RAISE;
1937:
1938: END insert_credit_amount;
1991: l_worker_number number;
1992: l_total_workers number;
1993: BEGIN
1994: IF l_debug_flag = 'Y' THEN
1995: debug( 'ar_calc_late_charge.insert_int_overdue_adj_dm()+' );
1996: END IF;
1997:
1998: l_fin_charge_date := p_fin_charge_date;
1999: l_worker_number := p_worker_number;
2117: 0,0,
2118: decode(c.interest_type,'FIXED_AMOUNT',decode(c.class,'INV',decode(sign(c.original_balance),-1,0,c.interest_fixed_amount),c.interest_fixed_amount), /*Bug 8559863*/
2119: 'CHARGE_PER_TIER', sched_lines.amount, /*Late Charge Charge per tier Enhacement 6469663*/
2120: decode(sched_hdrs.schedule_header_type,'AMOUNT', sched_lines.amount,
2121: ar_calc_late_charge.calculate_interest(
2122: decode(dense_rank()
2123: over(partition by c.customer_id,
2124: c.customer_site_use_id,
2125: c.invoice_currency_code,
2307: from (
2308: select
2309: ps.customer_id,
2310: decode(ps.class,
2311: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2312: ps.customer_site_use_id,
2313: ps.org_id),
2314: ps.customer_site_use_id) customer_site_use_id ,
2315: ps.invoice_currency_code,
2358: ar_lc_cust_sites_t cust_site,
2359: ar_late_charge_cust_balance_gt bal
2360: where ps.customer_id = cust_site.customer_id
2361: and cust_site.customer_site_use_id = decode(ps.class,
2362: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2363: ps.customer_site_use_id,
2364: ps.org_id),
2365: ps.customer_site_use_id)
2366: and ps.invoice_currency_code = cust_site.currency_code
2394: and adj.status = 'A'
2395: group by
2396: ps.customer_id,
2397: decode(ps.class,
2398: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2399: ps.customer_site_use_id,
2400: ps.org_id),
2401: ps.customer_site_use_id),
2402: ps.invoice_currency_code,
2435: cust_site.last_accrue_charge_date
2436: union all
2437: select ps.customer_id,
2438: decode(ps.class,
2439: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2440: ps.customer_site_use_id,
2441: ps.org_id),
2442: ps.customer_site_use_id) customer_site_use_id,
2443: ps.invoice_currency_code,
2484: ar_lc_cust_sites_t cust_site,
2485: ar_late_charge_cust_balance_gt bal
2486: where ps.customer_id = cust_site.customer_id
2487: and cust_site.customer_site_use_id = decode(ps.class,
2488: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2489: ps.customer_site_use_id,
2490: ps.org_id),
2491: ps.customer_site_use_id)
2492: and ps.invoice_currency_code = cust_site.currency_code
2523: and ps_cm_cr.payment_schedule_id = app.payment_schedule_id
2524: and ps_cm_cr.trx_date > l_fin_charge_date
2525: group by ps.customer_id,
2526: decode(ps.class,
2527: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2528: ps.customer_site_use_id,
2529: ps.org_id),
2530: ps.customer_site_use_id),
2531: ps.invoice_currency_code,
2564: cust_site.last_accrue_charge_date
2565: UNION ALL
2566: select ps.customer_id,
2567: decode(ps.class,
2568: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2569: ps.customer_site_use_id,
2570: ps.org_id),
2571: ps.customer_site_use_id) customer_site_use_id,
2572: ps.invoice_currency_code,
2610: ar_lc_cust_sites_t cust_site,
2611: ar_late_charge_cust_balance_gt bal
2612: where ps.customer_id = cust_site.customer_id
2613: and cust_site.customer_site_use_id = decode(ps.class,
2614: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2615: ps.customer_site_use_id,
2616: ps.org_id),
2617: ps.customer_site_use_id)
2618: and ps.invoice_currency_code = cust_site.currency_code
2751: and bat.transferred_status <> 'S'));
2752:
2753:
2754: IF l_debug_flag = 'Y' THEN
2755: debug( 'ar_calc_late_charge.insert_int_overdue_adj_dm()-' );
2756: END IF;
2757: --
2758: EXCEPTION
2759: WHEN OTHERS THEN
2758: EXCEPTION
2759: WHEN OTHERS THEN
2760: --IF l_debug_flag = 'Y' THEN
2761: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
2762: debug('EXCEPTION: ar_calc_late_charge.insert_int_overdue_adj_dm' );
2763: --END IF;
2764: RAISE;
2765: END insert_int_overdue_adj_dm;
2766: /*=========================================================================================+
2806: l_total_workers number;
2807:
2808: BEGIN
2809: IF l_debug_flag = 'Y' THEN
2810: debug( 'ar_calc_late_charge.insert_int_overdue_inv()+' );
2811: END IF;
2812:
2813: l_fin_charge_date := p_fin_charge_date;
2814: l_worker_number := p_worker_number;
2915: 'AMOUNT',decode(b.class,
2916: 'PMT',-1* sched_lines.amount,
2917: 'CM', -1* sched_lines.amount,
2918: sched_lines.amount),
2919: ar_calc_late_charge.calculate_interest(
2920: b.overdue_amt,
2921: b.charge_on_finance_charge_flag,
2922: least(decode(b.multiple_interest_rates_flag,
2923: 'Y',decode(sched_hdrs.schedule_header_type,
3011: from (
3012: select
3013: ps.customer_id,
3014: decode(ps.class,
3015: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3016: ps.customer_site_use_id,
3017: ps.org_id),
3018: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3019: ps.customer_site_use_id,
3014: decode(ps.class,
3015: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3016: ps.customer_site_use_id,
3017: ps.org_id),
3018: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3019: ps.customer_site_use_id,
3020: ps.org_id),
3021: ps.customer_site_use_id) customer_site_use_id,
3022: ps.invoice_currency_code,
3065: ar_lc_cust_sites_t cust_site,
3066: ar_late_charge_cust_balance_gt bal
3067: where ps.customer_id = cust_site.customer_id
3068: and cust_site.customer_site_use_id = decode(ps.class,
3069: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3070: ps.customer_site_use_id,
3071: ps.org_id),
3072: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3073: ps.customer_site_use_id,
3068: and cust_site.customer_site_use_id = decode(ps.class,
3069: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3070: ps.customer_site_use_id,
3071: ps.org_id),
3072: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3073: ps.customer_site_use_id,
3074: ps.org_id),
3075: ps.customer_site_use_id)
3076: and ps.invoice_currency_code = cust_site.currency_code
3109: and adj.status = 'A'
3110: group by
3111: ps.customer_id,
3112: decode(ps.class,
3113: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3114: ps.customer_site_use_id,
3115: ps.org_id),
3116: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3117: ps.customer_site_use_id,
3112: decode(ps.class,
3113: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3114: ps.customer_site_use_id,
3115: ps.org_id),
3116: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3117: ps.customer_site_use_id,
3118: ps.org_id),
3119: ps.customer_site_use_id),
3120: ps.invoice_currency_code,
3153: cust_site.last_accrue_charge_date
3154: UNION ALL
3155: select ps.customer_id,
3156: decode(ps.class,
3157: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3158: ps.customer_site_use_id,
3159: ps.org_id),
3160: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3161: ps.customer_site_use_id,
3156: decode(ps.class,
3157: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3158: ps.customer_site_use_id,
3159: ps.org_id),
3160: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3161: ps.customer_site_use_id,
3162: ps.org_id),
3163: ps.customer_site_use_id) customer_site_use_id,
3164: ps.invoice_currency_code,
3206: ar_lc_cust_sites_t cust_site,
3207: ar_late_charge_cust_balance_gt bal
3208: where ps.customer_id = cust_site.customer_id
3209: and cust_site.customer_site_use_id = decode(ps.class,
3210: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3211: ps.customer_site_use_id,
3212: ps.org_id),
3213: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3214: ps.customer_site_use_id,
3209: and cust_site.customer_site_use_id = decode(ps.class,
3210: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3211: ps.customer_site_use_id,
3212: ps.org_id),
3213: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3214: ps.customer_site_use_id,
3215: ps.org_id),
3216: ps.customer_site_use_id)
3217: and ps.invoice_currency_code = cust_site.currency_code
3253: and ps_cm_cr.payment_schedule_id = app.payment_schedule_id
3254: and ps_cm_cr.trx_date > l_fin_charge_date
3255: group by ps.customer_id,
3256: decode(ps.class,
3257: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3258: ps.customer_site_use_id,
3259: ps.org_id),
3260: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3261: ps.customer_site_use_id,
3256: decode(ps.class,
3257: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3258: ps.customer_site_use_id,
3259: ps.org_id),
3260: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3261: ps.customer_site_use_id,
3262: ps.org_id),
3263: ps.customer_site_use_id),
3264: ps.invoice_currency_code,
3415: cust_site.last_accrue_charge_date
3416: UNION ALL
3417: select ps.customer_id,
3418: decode(ps.class,
3419: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3420: ps.customer_site_use_id,
3421: ps.org_id),
3422: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3423: ps.customer_site_use_id,
3418: decode(ps.class,
3419: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3420: ps.customer_site_use_id,
3421: ps.org_id),
3422: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3423: ps.customer_site_use_id,
3424: ps.org_id),
3425: ps.customer_site_use_id) customer_site_use_id,
3426: ps.invoice_currency_code,
3464: ar_lc_cust_sites_t cust_site,
3465: ar_late_charge_cust_balance_gt bal
3466: where ps.customer_id = cust_site.customer_id
3467: and cust_site.customer_site_use_id = decode(ps.class,
3468: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3469: ps.customer_site_use_id,
3470: ps.org_id),
3471: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3472: ps.customer_site_use_id,
3467: and cust_site.customer_site_use_id = decode(ps.class,
3468: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3469: ps.customer_site_use_id,
3470: ps.org_id),
3471: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3472: ps.customer_site_use_id,
3473: ps.org_id),
3474: ps.customer_site_use_id)
3475: and ps.invoice_currency_code = cust_site.currency_code
3624:
3625:
3626:
3627: IF l_debug_flag = 'Y' THEN
3628: debug( 'ar_calc_late_charge.insert_int_overdue_inv()-' );
3629: END IF;
3630: --
3631: EXCEPTION
3632: WHEN OTHERS THEN
3631: EXCEPTION
3632: WHEN OTHERS THEN
3633: --IF l_debug_flag = 'Y' THEN
3634: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
3635: debug('EXCEPTION: ar_calc_late_charge.insert_int_overdue_inv' );
3636: --END IF;
3637: RAISE;
3638:
3639: END insert_int_overdue_inv;
3690: l_total_workers number;
3691:
3692: BEGIN
3693: IF l_debug_flag = 'Y' THEN
3694: debug( 'ar_calc_late_charge.insert_int_late_pay()+' );
3695: END IF;
3696:
3697: l_fin_charge_date := p_fin_charge_date;
3698: l_worker_number := p_worker_number;
3819: decode(a.interest_type,
3820: 'FIXED_AMOUNT',a.interest_fixed_amount,
3821: decode(sched_hdrs.schedule_header_type,
3822: 'AMOUNT',sched_lines.amount,
3823: ar_calc_late_charge.calculate_interest(
3824: decode(sign(a.late_pay_amount - a.original_balance),
3825: -1,a.late_pay_amount,
3826: 0,a.late_pay_amount,
3827: a.original_balance),
3859: from
3860: (
3861: select ps.customer_id,
3862: decode(ps.class,
3863: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3864: ps.customer_site_use_id,
3865: ps.org_id),
3866: ps.customer_site_use_id) customer_site_use_id ,
3867: ps.invoice_currency_code,
3881: cust_site.min_interest_charge,
3882: cust_site.max_interest_charge,
3883: sum(app.amount_applied + nvl(app.earned_discount_taken,0) +
3884: nvl(app.unearned_discount_taken,0)) late_pay_amount,
3885: ar_calc_late_charge.get_balance_as_of(ps.payment_schedule_id,
3886: cr.receipt_date-1,
3887: ps.class,
3888: cust_site.charge_on_finance_charge_flag) original_balance,
3889: ps.due_date,
3952: and adj.type ='CHARGES'
3953: group by ps.payment_schedule_id) adj
3954: where ps.customer_id = cust_site.customer_id
3955: and cust_site.customer_site_use_id = decode(ps.class,
3956: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3957: ps.customer_site_use_id,
3958: ps.org_id),
3959: ps.customer_site_use_id)
3960: and ps.invoice_currency_code = cust_site.currency_code
4002: and nvl(th.current_record_flag,'Y') = 'Y'
4003: and nvl(th.status,'*') not in ('PROTESTED','MATURED_PEND_RISK_ELIMINATION','CLOSED', 'CANCELLED')
4004: group by ps.customer_id,
4005: decode(ps.class,
4006: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4007: ps.customer_site_use_id,
4008: ps.org_id),
4009: ps.customer_site_use_id),
4010: ps.invoice_currency_code,
4022: cust_site.last_accrue_charge_date)),
4023: cust_site.exchange_rate_type,
4024: cust_site.min_interest_charge,
4025: cust_site.max_interest_charge,
4026: ar_calc_late_charge.get_balance_as_of(ps.payment_schedule_id,
4027: cr.receipt_date-1,
4028: ps.class,
4029: cust_site.charge_on_finance_charge_flag),
4030: ps.due_date,
4111: and bat.batch_status ='F'
4112: and bat.transferred_status <> 'S'));
4113:
4114: IF l_debug_flag = 'Y' THEN
4115: debug( 'ar_calc_late_charge.insert_int_late_pay()-' );
4116: END IF;
4117:
4118: --
4119: EXCEPTION
4119: EXCEPTION
4120: WHEN OTHERS THEN
4121: --IF l_debug_flag = 'Y' THEN
4122: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
4123: debug('EXCEPTION: ar_calc_late_charge.insert_int_late_pay' );
4124: --END IF;
4125: RAISE;
4126:
4127: END insert_int_late_pay;
4184:
4185: BEGIN
4186:
4187: IF l_debug_flag = 'Y' THEN
4188: debug( 'ar_calc_late_charge. insert_int_avg_daily_bal()+' );
4189: END IF;
4190:
4191: l_fin_charge_date := p_fin_charge_date;
4192: l_worker_number := p_worker_number;
4294: a.last_accrue_charge_date,
4295: a.exchange_rate_type,
4296: a.min_interest_charge,
4297: a.max_interest_charge,
4298: ar_calc_late_charge.currency_round(sum(a.balance * (a.date_to - a.date_from+1))
4299: / sum(a.date_to - a.date_from+1),
4300: a.currency_code) overdue_late_pay_amount,
4301: decode(a.interest_type,
4302: 'FIXED_RATE',a.interest_rate, NULL) interest_rate,
4303: sum(a.date_to - a.date_from+1) interest_days,
4304: decode(a.interest_type,
4305: 'FIXED_AMOUNT', a.interest_fixed_amount,
4306: 'FIXED_RATE',
4307: ar_calc_late_charge.currency_round(nvl(
4308: ((sum(a.balance * (a.date_to - a.date_from+1))/
4309: sum(a.date_to - a.date_from+1))/100
4310: *a.interest_rate
4311: ),
4325: cons_inv.currency_code,
4326: cons_inv.org_id,
4327: cons_inv.billing_date date_from,
4328: decode(sign(l_fin_charge_date -
4329: ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
4330: cons_inv.site_use_id,
4331: cons_inv.currency_code,
4332: cons_inv.org_id,
4333: sysparam.post_billing_item_inclusion,
4333: sysparam.post_billing_item_inclusion,
4334: cons_inv.billing_date,
4335: l_fin_charge_date)),
4336: -1,l_fin_charge_date,
4337: ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
4338: cons_inv.site_use_id,
4339: cons_inv.currency_code,
4340: cons_inv.org_id,
4341: sysparam.post_billing_item_inclusion,
4381: cust_site.customer_site_use_id,
4382: cust_site.currency_code,
4383: cust_site.org_id,
4384: decode(cust_site.last_accrue_charge_date,
4385: NULL, ar_calc_late_charge.get_first_activity_date(cust_site.customer_id,
4386: cust_site.customer_site_use_id,
4387: cust_site.currency_code,
4388: cust_site.org_id),
4389: cust_site.last_accrue_charge_date+1) date_from,
4387: cust_site.currency_code,
4388: cust_site.org_id),
4389: cust_site.last_accrue_charge_date+1) date_from,
4390: decode(sign(l_fin_charge_date -
4391: ar_calc_late_charge.get_next_activity_date(cust_site.customer_id,
4392: cust_site.customer_site_use_id,
4393: cust_site.currency_code,
4394: cust_site.org_id,
4395: sysparam.post_billing_item_inclusion,
4400: cust_site.org_id),
4401: cust_site.last_accrue_charge_date+1),
4402: l_fin_charge_date)),
4403: -1, l_fin_charge_date,
4404: ar_calc_late_charge.get_next_activity_date(cust_site.customer_id,
4405: cust_site.customer_site_use_id,
4406: cust_site.currency_code,
4407: cust_site.org_id,
4408: sysparam.post_billing_item_inclusion,
4411: cust_site.customer_site_use_id, cust_site.currency_code,
4412: cust_site.org_id),
4413: cust_site.last_accrue_charge_date+1),
4414: l_fin_charge_date)) date_to,
4415: ar_calc_late_charge.get_cust_balance(cust_site.customer_id,
4416: cust_site.customer_site_use_id,
4417: cust_site.currency_code,
4418: cust_site.org_id,
4419: sysparam.post_billing_item_inclusion,
4452: cust_sites.currency_code,
4453: cust_sites.org_id,
4454: ps.trx_date,
4455: decode(sign(l_fin_charge_date -
4456: ar_calc_late_charge.get_next_activity_date(ps.customer_id,
4457: ps.customer_site_use_id,
4458: ps.invoice_currency_code,
4459: ps.org_id,
4460: sysparam.post_billing_item_inclusion,
4460: sysparam.post_billing_item_inclusion,
4461: ps.trx_date,
4462: l_fin_charge_date)),
4463: -1, l_fin_charge_date,
4464: ar_calc_late_charge.get_next_activity_date(ps.customer_id,
4465: ps.customer_site_use_id,
4466: ps.invoice_currency_code,
4467: ps.org_id,
4468: sysparam.post_billing_item_inclusion,
4467: ps.org_id,
4468: sysparam.post_billing_item_inclusion,
4469: ps.trx_date,
4470: l_fin_charge_date)) date_to,
4471: ar_calc_late_charge.get_cust_balance(ps.customer_id,
4472: ps.customer_site_use_id,
4473: ps.invoice_currency_code,
4474: ps.org_id,
4475: sysparam.post_billing_item_inclusion,
4492: ar_lc_cust_sites_t cust_sites,
4493: ar_system_parameters sysparam
4494: where ps.customer_id = cust_sites.customer_id
4495: and decode(ps.class,
4496: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4497: ps.customer_site_use_id,
4498: ps.org_id),
4499: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4500: ps.customer_site_use_id,
4495: and decode(ps.class,
4496: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4497: ps.customer_site_use_id,
4498: ps.org_id),
4499: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4500: ps.customer_site_use_id,
4501: ps.org_id),
4502: ps.customer_site_use_id) = cust_sites.customer_site_use_id
4503: and ps.invoice_currency_code = cust_sites.currency_code
4557: a.min_fc_balance_overdue_type,
4558: a.min_fc_balance_amount,
4559: a.min_fc_balance_percent)b
4560: /* Apply the customer level tolerance - check for eligibility */
4561: where ar_calc_late_charge.check_adb_eligibility(b.customer_id,
4562: b.site_use_id,
4563: b.currency_code,
4564: b.org_id,
4565: b.payment_grace_days,
4650: NULL, -- bucket days to
4651: decode(b.interest_type,
4652: 'FIXED_AMOUNT', b.interest_fixed_amount,
4653: 'FIXED_RATE',
4654: ar_calc_late_charge.currency_round(nvl(b.overdue_late_pay_amt_org /100* b.interest_rate,0),
4655: b.currency_code)) late_charge_amount,
4656: 'INV' ,
4657: b.late_charge_term_id,
4658: NULL, -- interest_period_days not applicable
4685: a.payment_grace_days,
4686: a.min_fc_balance_overdue_type,
4687: a.min_fc_balance_amount,
4688: a.min_fc_balance_percent,
4689: ar_calc_late_charge.currency_round(sum(a.balance * (a.date_to - a.date_from+1)) /
4690: sum(a.date_to - a.date_from+1),
4691: a.currency_code) overdue_late_pay_amount,
4692: sum(a.balance * (a.date_to - a.date_from+1)) /
4693: sum(a.date_to - a.date_from+1) overdue_late_pay_amt_org,
4700: cons_inv.currency_code,
4701: cons_inv.org_id,
4702: cons_inv.due_date+1 date_from,
4703: decode(sign(l_fin_charge_date-
4704: ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
4705: cons_inv.site_use_id,
4706: cons_inv.currency_code,
4707: cons_inv.org_id,
4708: sysparam.post_billing_item_inclusion,
4708: sysparam.post_billing_item_inclusion,
4709: cons_inv.due_date+1,
4710: l_fin_charge_date)),
4711: -1,l_fin_charge_date,
4712: ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
4713: cons_inv.site_use_id,
4714: cons_inv.currency_code,
4715: cons_inv.org_id,
4716: sysparam.post_billing_item_inclusion,
4715: cons_inv.org_id,
4716: sysparam.post_billing_item_inclusion,
4717: cons_inv.due_date+1,
4718: l_fin_charge_date)) date_to,
4719: ar_calc_late_charge.get_cust_balance(cons_inv.customer_id,
4720: cons_inv.site_use_id,
4721: cons_inv.currency_code,
4722: cons_inv.org_id,
4723: sysparam.post_billing_item_inclusion,
4764: cust_sites.currency_code,
4765: cust_sites.org_id,
4766: ps.trx_date,
4767: decode(sign(l_fin_charge_date -
4768: ar_calc_late_charge.get_next_activity_date(ps.customer_id,
4769: ps.customer_site_use_id,
4770: ps.invoice_currency_code,
4771: ps.org_id,
4772: sysparam.post_billing_item_inclusion,
4772: sysparam.post_billing_item_inclusion,
4773: ps.trx_date,
4774: l_fin_charge_date)),
4775: -1, l_fin_charge_date,
4776: ar_calc_late_charge.get_next_activity_date(ps.customer_id,
4777: ps.customer_site_use_id,
4778: ps.invoice_currency_code,
4779: ps.org_id,
4780: sysparam.post_billing_item_inclusion,
4779: ps.org_id,
4780: sysparam.post_billing_item_inclusion,
4781: ps.trx_date,
4782: l_fin_charge_date)) date_to,
4783: ar_calc_late_charge.get_cust_balance(ps.customer_id,
4784: ps.customer_site_use_id,
4785: ps.invoice_currency_code,
4786: ps.org_id,
4787: sysparam.post_billing_item_inclusion,
4804: ar_lc_cust_sites_t cust_sites,
4805: ar_system_parameters sysparam
4806: where ps.customer_id = cust_sites.customer_id
4807: and decode(ps.class,
4808: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4809: ps.customer_site_use_id,
4810: ps.org_id),
4811: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4812: ps.customer_site_use_id,
4807: and decode(ps.class,
4808: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4809: ps.customer_site_use_id,
4810: ps.org_id),
4811: 'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4812: ps.customer_site_use_id,
4813: ps.org_id),
4814: ps.customer_site_use_id) = cust_sites.customer_site_use_id
4815: and ps.invoice_currency_code = cust_sites.currency_code
4836: and ci2.billing_date < l_fin_charge_date
4837: and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED')))a
4838: where nvl(a.balance,0) <> 0
4839: /* Apply the customer level tolerance - check for eligibility */
4840: and ar_calc_late_charge.check_adb_eligibility(a.customer_id,
4841: a.site_use_id,
4842: a.currency_code,
4843: a.org_id,
4844: a.payment_grace_days,
4876: and bat.batch_status ='F'
4877: and bat.transferred_status <> 'S'));
4878:
4879: IF l_debug_flag = 'Y' THEN
4880: debug( 'ar_calc_late_charge. insert_int_avg_daily_bal()-' );
4881: END IF;
4882: --
4883: EXCEPTION
4884: WHEN OTHERS THEN
4883: EXCEPTION
4884: WHEN OTHERS THEN
4885: --IF l_debug_flag = 'Y' THEN
4886: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
4887: debug('EXCEPTION: ar_calc_late_charge. insert_int_avg_daily_bal' );
4888: --END IF;
4889: RAISE;
4890:
4891: END insert_int_avg_daily_bal;
4894: p_worker_number IN NUMBER,
4895: p_total_workers IN NUMBER) IS
4896: BEGIN
4897: IF l_debug_flag = 'Y' THEN
4898: debug( 'ar_calc_late_charge.insert_cust_balances()+');
4899: END IF;
4900: INSERT INTO ar_late_charge_cust_balance_gt
4901: (late_charge_cust_sites_id,
4902: customer_id,
4931: AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
4932: AND ct.org_id = typ.org_id --Bug12884574
4933: AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
4934: AND cust_site.customer_site_use_id = decode(ps.class,
4935: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4936: ps.customer_site_use_id,
4937: ps.org_id),
4938: ps.customer_site_use_id)
4939: AND ps.invoice_currency_code = cust_site.currency_code
4971: AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
4972: AND ct.org_id = typ.org_id --Bug12884574
4973: AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
4974: AND cust_site.customer_site_use_id = decode(ps.class,
4975: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4976: ps.customer_site_use_id,
4977: ps.org_id),
4978: ps.customer_site_use_id)
4979: AND ps.invoice_currency_code = cust_site.currency_code
5017: AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
5018: AND ct.org_id = typ.org_id --Bug12884574
5019: AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
5020: AND cust_site.customer_site_use_id = decode(ps.class,
5021: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
5022: ps.customer_site_use_id,
5023: ps.org_id),
5024: ps.customer_site_use_id)
5025: AND ps.invoice_currency_code = cust_site.currency_code
5060: AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
5061: AND ct.org_id = typ.org_id --Bug12884574
5062: AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
5063: AND cust_site.customer_site_use_id = decode(ps.class,
5064: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
5065: ps.customer_site_use_id,
5066: ps.org_id),
5067: ps.customer_site_use_id)
5068: AND ps.invoice_currency_code = cust_site.currency_code
5102: AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
5103: AND ct.org_id = typ.org_id --Bug12884574
5104: AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
5105: AND cust_site.customer_site_use_id = decode(ps.class,
5106: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
5107: ps.customer_site_use_id,
5108: ps.org_id),
5109: ps.customer_site_use_id)
5110: AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
5134: ps.org_id
5135: FROM ar_payment_schedules ps,
5136: ar_lc_cust_sites_t cust_site
5137: WHERE ps.customer_id = cust_site.customer_id
5138: AND cust_site.customer_site_use_id = ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
5139: ps.customer_site_use_id,
5140: ps.org_id)
5141: AND ps.invoice_currency_code = cust_site.currency_code
5142: AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
5175: AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
5176: AND ct.org_id = typ.org_id --Bug12884574
5177: AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
5178: AND cust_site.customer_site_use_id = decode(ps.class,
5179: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
5180: ps.customer_site_use_id,
5181: ps.org_id),
5182: ps.customer_site_use_id)
5183: AND ps.invoice_currency_code = cust_site.currency_code
5205: a.customer_site_use_id,
5206: a.currency_code,
5207: a.org_id);
5208: IF l_debug_flag = 'Y' THEN
5209: debug( 'ar_calc_late_charge.insert_cust_balances()-');
5210: END IF;
5211:
5212: EXCEPTION
5213: WHEN OTHERS THEN
5212: EXCEPTION
5213: WHEN OTHERS THEN
5214: --IF l_debug_flag = 'Y' THEN
5215: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
5216: debug('EXCEPTION: ar_calc_late_charge.insert_cust_balances' );
5217: --END IF;
5218: RAISE;
5219:
5220:
5257:
5258: BEGIN
5259:
5260: IF l_debug_flag = 'Y' THEN
5261: debug( 'ar_calc_late_charge.insert_penalty_lines()+' );
5262: END IF;
5263:
5264: insert into ar_late_charge_trx_t
5265: ( late_charge_trx_id,
5337: a.rate_start_date,
5338: a.rate_end_date,
5339: a.schedule_days_start,
5340: a.schedule_days_to,
5341: NVL(a.penalty_amount,ar_calc_late_charge.currency_round(nvl(a.penalty_rate,0) * a.interest/100,
5342: a.currency_code)),
5343: a.late_charge_type,
5344: a.late_charge_term_id,
5345: a.interest_period_days,
5526: trx.message_text_id,
5527: cust_site.penalty_type) a);
5528:
5529: IF l_debug_flag = 'Y' THEN
5530: debug( 'ar_calc_late_charge.insert_penalty_lines()-' );
5531: END IF;
5532: --
5533: EXCEPTION
5534: WHEN OTHERS THEN
5533: EXCEPTION
5534: WHEN OTHERS THEN
5535: --IF l_debug_flag = 'Y' THEN
5536: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
5537: debug('EXCEPTION: ar_calc_late_charge.insert_penalty_lines' );
5538: --END IF;
5539: RAISE;
5540: END insert_penalty_lines;
5541:
5544:
5545: BEGIN
5546:
5547: IF l_debug_flag = 'Y' THEN
5548: debug('ar_calc_late_charge.delete_draft_batches()+');
5549: debug('p_worker_number : '||p_worker_number);
5550: debug('p_total_workers : '||p_total_workers);
5551: END IF;
5552: /* delete lines first */
5578: /* Deleting the empty batches are done later in delete_empty_batches */
5579:
5580:
5581: IF l_debug_flag = 'Y' THEN
5582: debug('ar_calc_late_charge.delete_draft_batches()-');
5583: END IF;
5584:
5585: EXCEPTION WHEN OTHERS THEN
5586: --IF l_debug_flag = 'Y' THEN
5584:
5585: EXCEPTION WHEN OTHERS THEN
5586: --IF l_debug_flag = 'Y' THEN
5587: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
5588: debug('EXCEPTION : ar_calc_late_charge.delete_draft_batches()');
5589: --END IF;
5590: RAISE;
5591: END delete_draft_batches;
5592:
5608:
5609: BEGIN
5610:
5611: IF l_debug_flag = 'Y' THEN
5612: debug( 'ar_calc_late_charge.insert_int_batches()+' );
5613: END IF;
5614:
5615: l_operating_unit_id := p_operating_unit_id;
5616: l_batch_name := p_batch_name;
5663: from ar_system_parameters sysparam
5664: where nvl(l_operating_unit_id,sysparam.org_id) = sysparam.org_id);
5665:
5666: IF l_debug_flag = 'Y' THEN
5667: debug( 'ar_calc_late_charge.insert_int_batches()-' );
5668: END IF;
5669: --
5670: EXCEPTION
5671: WHEN OTHERS THEN
5670: EXCEPTION
5671: WHEN OTHERS THEN
5672: --IF l_debug_flag = 'Y' THEN
5673: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
5674: debug('EXCEPTION: ar_calc_late_charge.insert_int_batches' );
5675: --END IF;
5676: RAISE;
5677: END insert_int_batches;
5678:
5682: p_total_workers IN NUMBER) IS
5683: BEGIN
5684:
5685: IF l_debug_flag = 'Y' THEN
5686: debug( 'ar_calc_late_charge.insert_int_headers()+' );
5687: END IF;
5688:
5689: insert into ar_interest_headers
5690: (interest_header_id,
5847: and sysparam.org_id = cust_site.org_id
5848: and bat.request_id = l_request_id);
5849:
5850: IF l_debug_flag = 'Y' THEN
5851: debug( 'ar_calc_late_charge.insert_int_headers()-' );
5852: END IF;
5853: --
5854: EXCEPTION
5855: WHEN OTHERS THEN
5854: EXCEPTION
5855: WHEN OTHERS THEN
5856: --IF l_debug_flag = 'Y' THEN
5857: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
5858: debug('EXCEPTION: ar_calc_late_charge.insert_int_headers' );
5859: --END IF;
5860: RAISE;
5861: END insert_int_headers;
5862:
5909: PROCEDURE insert_int_lines(p_worker_number IN NUMBER,
5910: p_total_workers IN NUMBER) IS
5911: BEGIN
5912: IF l_debug_flag = 'Y' THEN
5913: debug( 'ar_calc_late_charge.insert_int_lines()+' );
5914: END IF;
5915:
5916: insert into ar_interest_lines
5917: (interest_line_id,
6069: trx.amount_due_remaining,
6070: trx.customer_trx_id original_trx_id,
6071: decode(hdr.late_charge_type,
6072: 'ADJ',decode(trx.charge_line_type,
6073: 'PENALTY',ar_calc_late_charge.get_penalty_rec_trx_id(trx.finance_charge_date,
6074: trx.org_id),
6075: 'INTEREST',ar_calc_late_charge.get_int_rec_trx_id(trx.customer_trx_id,
6076: trx.finance_charge_date,
6077: trx.org_id),
6071: decode(hdr.late_charge_type,
6072: 'ADJ',decode(trx.charge_line_type,
6073: 'PENALTY',ar_calc_late_charge.get_penalty_rec_trx_id(trx.finance_charge_date,
6074: trx.org_id),
6075: 'INTEREST',ar_calc_late_charge.get_int_rec_trx_id(trx.customer_trx_id,
6076: trx.finance_charge_date,
6077: trx.org_id),
6078: NULL),
6079: NULL) receivables_trx_id,
6088: trx.org_id,
6089: trx.cash_receipt_id
6090: from
6091: (select
6092: ar_calc_late_charge.get_next_hdr_id interest_header_id,
6093: a.customer_id,
6094: a.customer_site_use_id,
6095: a.currency_code,
6096: a.legal_entity_id,
6173: and trx1.display_flag = 'Y')))
6174: and hdr.payment_schedule_id = decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id)) a ) b);
6175:
6176: IF l_debug_flag = 'Y' THEN
6177: debug( 'ar_calc_late_charge.insert_int_lines()-' );
6178: END IF;
6179: --
6180: EXCEPTION
6181: WHEN OTHERS THEN
6180: EXCEPTION
6181: WHEN OTHERS THEN
6182: --IF l_debug_flag = 'Y' THEN
6183: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
6184: debug('EXCEPTION: ar_calc_late_charge.insert_int_lines');
6185: --END IF;
6186: RAISE;
6187: END insert_int_lines;
6188:
6192: PROCEDURE insert_int_lines_adb(p_worker_number IN NUMBER,
6193: p_total_workers IN NUMBER) IS
6194: BEGIN
6195: IF l_debug_flag = 'Y' THEN
6196: debug( 'ar_calc_late_charge.insert_int_lines_adb()+' );
6197: END IF;
6198:
6199: insert into ar_interest_lines
6200: (interest_line_id,
6323: trx.schedule_days_to,
6324: trx.org_id
6325: from
6326: (select
6327: ar_calc_late_charge.get_next_hdr_id interest_header_id,
6328: a.customer_id,
6329: a.customer_site_use_id,
6330: a.currency_code,
6331: a.legal_entity_id,
6374: and trx.charge_type = 'AVERAGE_DAILY_BALANCE'
6375: and hdr.payment_schedule_id = decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id)) a )b);
6376:
6377: IF l_debug_flag = 'Y' THEN
6378: debug( 'ar_calc_late_charge.insert_int_lines_adb()-' );
6379: END IF;
6380: --
6381: EXCEPTION
6382: WHEN OTHERS THEN
6381: EXCEPTION
6382: WHEN OTHERS THEN
6383: --IF l_debug_flag = 'Y' THEN
6384: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
6385: debug('EXCEPTION: ar_calc_late_charge.insert_int_lines_adb');
6386: --END IF;
6387: RAISE;
6388: END insert_int_lines_adb;
6389:
6389:
6390: PROCEDURE delete_empty_batches IS
6391: BEGIN
6392: IF l_debug_flag = 'Y' THEN
6393: debug('ar_calc_late_charge.delete_empty_batches()+');
6394: END IF;
6395:
6396: delete from ar_interest_batches bat
6397: where not exists (select interest_header_id
6400: and ( request_id = l_request_id
6401: OR batch_status = 'D') ;
6402:
6403: IF l_debug_flag = 'Y' THEN
6404: debug('ar_calc_late_charge.delete_empty_batches()-');
6405: END IF;
6406: EXCEPTION WHEN OTHERS THEN
6407: --IF l_debug_flag = 'Y' THEN
6408: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
6405: END IF;
6406: EXCEPTION WHEN OTHERS THEN
6407: --IF l_debug_flag = 'Y' THEN
6408: debug('EXCEPTION : '||SQLCODE||' : '||SQLERRM);
6409: debug('EXCEPTION: ar_calc_late_charge.delete_empty_batches()');
6410: --END IF;
6411: END delete_empty_batches;
6412:
6413: PROCEDURE lock_batches IS
6427: l_bulk_fetch_rows number := 1000;
6428: l_last_fetch_rows boolean := FALSE;
6429:
6430: BEGIN
6431: debug('ar_calc_late_charge.lock_batches ()+');
6432:
6433: OPEN c_lock;
6434:
6435: LOOP
6442: END IF;
6443: END LOOP;
6444: CLOSE c_lock;
6445:
6446: debug('ar_calc_late_charge.lock_batches ()-');
6447: EXCEPTION
6448: WHEN OTHERS THEN
6449: debug('EXCEPTION : ar_calc_late_charge.lock_batches ()');
6450: debug('EXCEPTION : '||SQLCODE ||' : '||SQLERRM);
6445:
6446: debug('ar_calc_late_charge.lock_batches ()-');
6447: EXCEPTION
6448: WHEN OTHERS THEN
6449: debug('EXCEPTION : ar_calc_late_charge.lock_batches ()');
6450: debug('EXCEPTION : '||SQLCODE ||' : '||SQLERRM);
6451: RAISE;
6452: END lock_batches;
6453:
7107:
7108: l_debug_flag := p_enable_debug;
7109:
7110: IF l_debug_flag = 'Y' THEN
7111: debug('ar_calc_late_charge.create_late_charge_document()+ ');
7112: debug('Global package variables');
7113: debug('pg_last_updated_by : '||pg_last_updated_by);
7114: debug('pg_last_update_login : '||pg_last_update_login);
7115: END IF;
7342:
7343: retcode := 0;
7344:
7345: IF l_debug_flag = 'Y' THEN
7346: debug('ar_calc_late_charge.create_late_charge_document()- ');
7347: END IF;
7348: EXCEPTION WHEN OTHERS THEN
7349: /* delete the data in the interim tables before commit */
7350: delete from ar_lc_cust_sites_t;
7662: pg_last_updated_by := arp_global.last_updated_by;
7663: pg_last_update_login := arp_global.last_update_login;
7664: -- l_debug_flag := 'Y'; /* Enable Debug now */
7665:
7666: END AR_CALC_LATE_CHARGE;