[Home] [Help]
972: /*Late charge changes for amount per tier Enhacement 6469663*/
973: CURSOR wrong_setup(fin_charge_date in date ) is
974: select substrb(party.party_name,1,50) name,lc_site.lc_cust_sites_id,lc_site.customer_id,lc_site.currency_code,lc_site.customer_site_use_id,
975: 'INTEREST' type
976: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
977: WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
978: AND lc_site.interest_schedule_id = c_schdl.schedule_id
979: AND c_schdl.schedule_id = h_schdl.schedule_id
980: AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
983: AND cust_acct.party_id = party.party_id
984: UNION
985: select substrb(party.party_name,1,50) name,lc_site.lc_cust_sites_id,lc_site.customer_id,lc_site.currency_code,lc_site.customer_site_use_id,
986: 'PENALTY' type
987: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
988: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
989: AND lc_site.penalty_schedule_id = c_schdl.schedule_id
990: AND c_schdl.schedule_id = h_schdl.schedule_id
991: AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1087: l_org_where := NULL;
1088: END IF;
1089:
1090: l_ins_statement :=
1091: 'insert into ar_lc_cust_sites_t
1092: (lc_cust_sites_id,
1093: customer_id,
1094: customer_site_use_id,
1095: currency_code,
1352: debug(rpad(setup.name,50) || ' ' || rpad(setup.currency_code ,8) || ' ' || rpad(setup.customer_site_use_id,12) || ' ' || setup.type);
1353: END LOOP;
1354:
1355: /*Late Charge Changes for charge per tier Enhacement 6469663*/
1356: UPDATE ar_lc_cust_sites_t set penalty_type = NULL
1357: WHERE lc_cust_sites_id IN
1358: ( select lc_cust_sites_id
1359: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
1360: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
1355: /*Late Charge Changes for charge per tier Enhacement 6469663*/
1356: UPDATE ar_lc_cust_sites_t set penalty_type = NULL
1357: WHERE lc_cust_sites_id IN
1358: ( select lc_cust_sites_id
1359: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
1360: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
1361: AND lc_site.penalty_schedule_id = c_schdl.schedule_id
1362: AND c_schdl.schedule_id = h_schdl.schedule_id
1363: AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1365: AND lc_site.customer_id = cust_acct.cust_account_id
1366: AND cust_acct.party_id = party.party_id
1367: );
1368:
1369: delete from ar_lc_cust_sites_t
1370: where lc_cust_sites_id IN
1371: (
1372: select lc_cust_sites_id
1373: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
1369: delete from ar_lc_cust_sites_t
1370: where lc_cust_sites_id IN
1371: (
1372: select lc_cust_sites_id
1373: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
1374: WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
1375: AND lc_site.interest_schedule_id = c_schdl.schedule_id
1376: AND c_schdl.schedule_id = h_schdl.schedule_id
1377: AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1442: cr.org_id,
1443: -1* ps.amount_due_remaining balance_due
1444: from ar_cash_receipts cr,
1445: ar_payment_schedules ps,
1446: ar_lc_cust_sites_t cust_site,
1447: ar_late_charge_cust_balance_gt bal
1448: where ps.actual_date_closed > l_fin_charge_date
1449: and ps.class ='PMT'
1450: and ps.trx_date <= l_fin_charge_date
1491: 'Y') balance_due
1492: from ar_payment_schedules ps,
1493: ra_customer_trx trx,
1494: ra_cust_trx_types types,
1495: ar_lc_cust_sites_t cust_site,
1496: ar_late_charge_cust_balance_gt bal
1497: where ps.customer_id = cust_site.customer_id
1498: and ps.customer_site_use_id = cust_site.customer_site_use_id
1499: and ps.invoice_currency_code = cust_site.currency_code
1964: ps.cash_receipt_id,
1965: 'OVERDUE' charge_type
1966: from ar_payment_schedules ps,
1967: ar_adjustments adj,
1968: ar_lc_cust_sites_t cust_site,
1969: ar_late_charge_cust_balance_gt bal
1970: where ps.customer_id = cust_site.customer_id
1971: and cust_site.customer_site_use_id = decode(ps.class,
1972: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2087: 'OVERDUE' charge_type
2088: from ar_payment_schedules ps,
2089: ar_receivable_applications app,
2090: ar_payment_schedules ps_cm_cr,
2091: ar_lc_cust_sites_t cust_site,
2092: ar_late_charge_cust_balance_gt bal
2093: where ps.customer_id = cust_site.customer_id
2094: and cust_site.customer_site_use_id = decode(ps.class,
2095: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2210: ps.org_id,
2211: ps.cash_receipt_id,
2212: 'OVERDUE' charge_type
2213: from ar_payment_schedules ps,
2214: ar_lc_cust_sites_t cust_site,
2215: ar_late_charge_cust_balance_gt bal
2216: where ps.customer_id = cust_site.customer_id
2217: and cust_site.customer_site_use_id = decode(ps.class,
2218: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2662: ps.cash_receipt_id,
2663: 'OVERDUE' charge_type
2664: from ar_payment_schedules ps,
2665: ar_adjustments adj,
2666: ar_lc_cust_sites_t cust_site,
2667: ar_late_charge_cust_balance_gt bal
2668: where ps.customer_id = cust_site.customer_id
2669: and cust_site.customer_site_use_id = decode(ps.class,
2670: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2802: 'OVERDUE' charge_type
2803: from ar_payment_schedules ps,
2804: ar_receivable_applications app,
2805: ar_payment_schedules ps_cm_cr,
2806: ar_lc_cust_sites_t cust_site,
2807: ar_late_charge_cust_balance_gt bal
2808: where ps.customer_id = cust_site.customer_id
2809: and cust_site.customer_site_use_id = decode(ps.class,
2810: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
2935: ps.cash_receipt_id,
2936: 'OVERDUE' charge_type
2937: from ar_payment_schedules ps,
2938: ar_receivable_applications app,
2939: ar_lc_cust_sites_t cust_site,
2940: ar_late_charge_cust_balance_gt bal
2941: where ps.customer_id = cust_site.customer_id
2942: and cust_site.customer_site_use_id = ps.customer_site_use_id
2943: and ps.invoice_currency_code = cust_site.currency_code
3058: ps.org_id,
3059: ps.cash_receipt_id,
3060: 'OVERDUE' charge_type
3061: from ar_payment_schedules ps,
3062: ar_lc_cust_sites_t cust_site,
3063: ar_late_charge_cust_balance_gt bal
3064: where ps.customer_id = cust_site.customer_id
3065: and cust_site.customer_site_use_id = decode(ps.class,
3066: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
3512: 'MONTHLY',first_day(ps.due_date)) eff_due_date,
3513: 'LATE' charge_type,
3514: cust_site.late_charge_calculation_trx eff_charge_type
3515: from ar_payment_schedules ps,
3516: ar_lc_cust_sites_t cust_site,
3517: ar_late_charge_cust_balance_gt bal,
3518: ar_receivable_applications app,
3519: ar_cash_receipts cr,
3520: ra_cust_trx_types types,
3522: ar_transaction_history th,
3523: (select ps.payment_schedule_id ,sum(adj.amount) fin_charge_charged
3524: from ar_payment_schedules ps,
3525: ar_adjustments adj,
3526: ar_lc_cust_sites_t cust_site
3527: where ps.customer_id = cust_site.customer_id
3528: and decode(cust_site.customer_site_use_id,'','X', ps.customer_site_use_id)
3529: = decode(cust_site.customer_site_use_id,'','X', cust_site.customer_site_use_id)
3530: and ps.invoice_currency_code = cust_site.currency_code
3941: cust_sites.min_fc_balance_overdue_type,
3942: cust_sites.min_fc_balance_amount,
3943: cust_sites.min_fc_balance_percent
3944: from ar_cons_inv cons_inv,
3945: ar_lc_cust_sites_t cust_sites,
3946: ar_system_parameters sysparam
3947: where cons_inv.customer_id = cust_sites.customer_id
3948: and cons_inv.site_use_id = cust_sites.customer_site_use_id
3949: and cons_inv.currency_code = cust_sites.currency_code
4021: cust_site.payment_grace_days,
4022: cust_site.min_fc_balance_overdue_type,
4023: cust_site.min_fc_balance_amount,
4024: cust_site.min_fc_balance_percent
4025: from ar_lc_cust_sites_t cust_site,
4026: ar_system_parameters sysparam
4027: where sysparam.org_id = cust_site.org_id
4028: and sysparam.late_charge_billing_calc_mode = 'RUN_TO_RUN_DATE'
4029: and cust_site.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
4073: cust_sites.min_fc_balance_overdue_type,
4074: cust_sites.min_fc_balance_amount,
4075: cust_sites.min_fc_balance_percent
4076: from ar_payment_schedules ps,
4077: ar_lc_cust_sites_t cust_sites,
4078: ar_system_parameters sysparam
4079: where ps.customer_id = cust_sites.customer_id
4080: and decode(ps.class,
4081: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4321: cust_sites.min_fc_balance_overdue_type,
4322: cust_sites.min_fc_balance_amount,
4323: cust_sites.min_fc_balance_percent
4324: from ar_cons_inv cons_inv,
4325: ar_lc_cust_sites_t cust_sites,
4326: ar_system_parameters sysparam
4327: where cons_inv.customer_id = cust_sites.customer_id
4328: and cons_inv.site_use_id = cust_sites.customer_site_use_id
4329: and cons_inv.currency_code = cust_sites.currency_code
4385: cust_sites.min_fc_balance_overdue_type,
4386: cust_sites.min_fc_balance_amount,
4387: cust_sites.min_fc_balance_percent
4388: from ar_payment_schedules ps,
4389: ar_lc_cust_sites_t cust_sites,
4390: ar_system_parameters sysparam
4391: where ps.customer_id = cust_sites.customer_id
4392: and decode(ps.class,
4393: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4506: sum((case when ps.due_date < p_as_of_date then 1 else 0 end)
4507: * ps.amount_due_remaining) overdue_bal,
4508: ps.org_id
4509: FROM ar_payment_schedules ps,
4510: ar_lc_cust_sites_t cust_site
4511: WHERE ps.customer_id = cust_site.customer_id
4512: AND cust_site.customer_site_use_id = decode(ps.class,
4513: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4514: ps.customer_site_use_id,
4540: ps.org_id
4541: FROM ar_payment_schedules ps,
4542: ar_receivable_applications ra,
4543: ar_payment_schedules ps_cm_cr,
4544: ar_lc_cust_sites_t cust_site
4545: WHERE ps.customer_id = cust_site.customer_id
4546: AND cust_site.customer_site_use_id = decode(ps.class,
4547: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4548: ps.customer_site_use_id,
4577: + NVL(ra.unearned_discount_taken,0))) overdue_bal,
4578: ps.org_id
4579: FROM ar_payment_schedules ps,
4580: ar_receivable_applications ra,
4581: ar_lc_cust_sites_t cust_site
4582: WHERE ps.customer_id = cust_site.customer_id
4583: AND cust_site.customer_site_use_id = decode(ps.class,
4584: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4585: ps.customer_site_use_id,
4613: *(adj.amount)) overdue_bal,
4614: ps.org_id
4615: FROM ar_adjustments adj ,
4616: ar_payment_schedules ps,
4617: ar_lc_cust_sites_t cust_site
4618: WHERE ps.customer_id = cust_site.customer_id
4619: AND cust_site.customer_site_use_id = decode(ps.class,
4620: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4621: ps.customer_site_use_id,
4646: sum(ps.amount_due_remaining) open_bal,
4647: sum(ps.amount_due_remaining) overdue_bal,
4648: ps.org_id
4649: FROM ar_payment_schedules ps,
4650: ar_lc_cust_sites_t cust_site
4651: WHERE ps.customer_id = cust_site.customer_id
4652: AND cust_site.customer_site_use_id = ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4653: ps.customer_site_use_id,
4654: ps.org_id)
4680: FROM ar_payment_schedules ps,
4681: ar_distributions ard,
4682: ar_transaction_history ath,
4683: ra_customer_trx_lines lines,
4684: ar_lc_cust_sites_t cust_site
4685: WHERE ps.customer_id = cust_site.customer_id
4686: AND cust_site.customer_site_use_id = decode(ps.class,
4687: 'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
4688: ps.customer_site_use_id,
4915: trx.org_id,
4916: decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.interest_days,1) interest_days,
4917: decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.charge_type, NULL) charge_type,
4918: cust_site.penalty_type
4919: from ar_lc_cust_sites_t cust_site,
4920: ar_late_charge_trx_t trx,
4921: ar_charge_schedule_hdrs sched_hdrs,
4922: ar_charge_schedule_lines sched_lines,
4923: ar_aging_bucket_lines bucket_lines
5337: trx.currency_code,
5338: trx.late_charge_type,
5339: trx.org_id,
5340: trx.display_flag) hdr,
5341: ar_lc_cust_sites_t cust_site,
5342: ar_interest_batches bat,
5343: ar_system_parameters sysparam
5344: where hdr.customer_id = cust_site.customer_id
5345: and hdr.customer_site_use_id = cust_site.customer_site_use_id
5984: penalty_rate,
5985: penalty_fixed_amount,
5986: penalty_schedule_id,
5987: last_accrue_charge_date
5988: FROM ar_lc_cust_sites_t
5989: ORDER BY org_id,
5990: customer_id,
5991: customer_site_use_id,
5992: currency_code;
6787: p_worker_number => l_worker_number,
6788: p_total_workers => l_total_workers);
6789:
6790: /* delete the data in the interim tables before commit */
6791: delete from ar_lc_cust_sites_t;
6792:
6793: delete from ar_late_charge_trx_t;
6794:
6795: COMMIT;
6811: debug_credit_amts;
6812: debug_payment_schedules;
6813: END IF;
6814: /* delete the data in the interim tables before commit */
6815: delete from ar_lc_cust_sites_t;
6816:
6817: delete from ar_late_charge_trx_t;
6818: END IF;
6819:
6826: debug('ar_calc_late_charge.create_late_charge_document()- ');
6827: END IF;
6828: EXCEPTION WHEN OTHERS THEN
6829: /* delete the data in the interim tables before commit */
6830: delete from ar_lc_cust_sites_t;
6831:
6832: delete from ar_late_charge_trx_t;
6833:
6834: END create_late_charge_document;