[Home] [Help]
407: from ar_interest_lines int_lines,
408: ar_interest_headers int_headers,
409: ar_cash_receipts cr,
410: ar_payment_schedules ps,
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,
1363: /*Late charge changes for amount per tier Enhacement 6469663*/
1364: CURSOR wrong_setup(fin_charge_date in date ) is
1365: 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,
1366: 'INTEREST' type
1367: 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
1368: WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
1369: AND lc_site.interest_schedule_id = c_schdl.schedule_id
1370: AND c_schdl.schedule_id = h_schdl.schedule_id
1371: AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1374: AND cust_acct.party_id = party.party_id
1375: UNION
1376: 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,
1377: 'PENALTY' type
1378: 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
1379: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
1380: AND lc_site.penalty_schedule_id = c_schdl.schedule_id
1381: AND c_schdl.schedule_id = h_schdl.schedule_id
1382: AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1478: l_org_where := NULL;
1479: END IF;
1480:
1481: l_ins_statement :=
1482: 'insert into ar_lc_cust_sites_t
1483: (lc_cust_sites_id,
1484: customer_id,
1485: customer_site_use_id,
1486: currency_code,
1743: debug(rpad(setup.name,50) || ' ' || rpad(setup.currency_code ,8) || ' ' || rpad(setup.customer_site_use_id,12) || ' ' || setup.type);
1744: END LOOP;
1745:
1746: /*Late Charge Changes for charge per tier Enhacement 6469663*/
1747: UPDATE ar_lc_cust_sites_t set penalty_type = NULL
1748: WHERE lc_cust_sites_id IN
1749: ( select lc_cust_sites_id
1750: 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
1751: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
1746: /*Late Charge Changes for charge per tier Enhacement 6469663*/
1747: UPDATE ar_lc_cust_sites_t set penalty_type = NULL
1748: WHERE lc_cust_sites_id IN
1749: ( select lc_cust_sites_id
1750: 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
1751: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
1752: AND lc_site.penalty_schedule_id = c_schdl.schedule_id
1753: AND c_schdl.schedule_id = h_schdl.schedule_id
1754: AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1756: AND lc_site.customer_id = cust_acct.cust_account_id
1757: AND cust_acct.party_id = party.party_id
1758: );
1759:
1760: delete from ar_lc_cust_sites_t
1761: where lc_cust_sites_id IN
1762: (
1763: select lc_cust_sites_id
1764: 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
1760: delete from ar_lc_cust_sites_t
1761: where lc_cust_sites_id IN
1762: (
1763: select lc_cust_sites_id
1764: 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
1765: WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
1766: AND lc_site.interest_schedule_id = c_schdl.schedule_id
1767: AND c_schdl.schedule_id = h_schdl.schedule_id
1768: AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1833: cr.org_id,
1834: -1* ps.amount_due_remaining balance_due
1835: from ar_cash_receipts cr,
1836: ar_payment_schedules ps,
1837: ar_lc_cust_sites_t cust_site,
1838: ar_late_charge_cust_balance_gt bal
1839: where ps.actual_date_closed > l_fin_charge_date
1840: and ps.class ='PMT'
1841: and ps.trx_date <= l_fin_charge_date
1882: 'Y') balance_due
1883: from ar_payment_schedules ps,
1884: ra_customer_trx trx,
1885: ra_cust_trx_types types,
1886: ar_lc_cust_sites_t cust_site,
1887: ar_late_charge_cust_balance_gt bal
1888: where ps.customer_id = cust_site.customer_id
1889: and ps.customer_site_use_id = cust_site.customer_site_use_id
1890: and ps.invoice_currency_code = cust_site.currency_code
2354: ps.cash_receipt_id,
2355: 'OVERDUE' charge_type
2356: from ar_payment_schedules ps,
2357: ar_adjustments adj,
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,
2480: 'OVERDUE' charge_type
2481: from ar_payment_schedules ps,
2482: ar_receivable_applications app,
2483: ar_payment_schedules ps_cm_cr,
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,
2606: ps.org_id,
2607: ps.cash_receipt_id,
2608: 'OVERDUE' charge_type
2609: from ar_payment_schedules ps,
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,
3061: ps.cash_receipt_id,
3062: 'OVERDUE' charge_type
3063: from ar_payment_schedules ps,
3064: ar_adjustments adj,
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,
3202: 'OVERDUE' charge_type
3203: from ar_payment_schedules ps,
3204: ar_receivable_applications app,
3205: ar_payment_schedules ps_cm_cr,
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,
3336: ps.cash_receipt_id,
3337: 'OVERDUE' charge_type
3338: from ar_payment_schedules ps,
3339: ar_receivable_applications app,
3340: ar_lc_cust_sites_t cust_site,
3341: ar_late_charge_cust_balance_gt bal
3342: where ps.customer_id = cust_site.customer_id
3343: and cust_site.customer_site_use_id = ps.customer_site_use_id
3344: and ps.invoice_currency_code = cust_site.currency_code
3460: ps.org_id,
3461: ps.cash_receipt_id,
3462: 'OVERDUE' charge_type
3463: from ar_payment_schedules ps,
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,
3923: 'LATE' charge_type,
3924: cust_site.late_charge_calculation_trx eff_charge_type,
3925: cr.cash_receipt_id
3926: from ar_payment_schedules ps,
3927: ar_lc_cust_sites_t cust_site,
3928: ar_late_charge_cust_balance_gt bal,
3929: ar_receivable_applications app,
3930: ar_cash_receipts cr,
3931: ra_cust_trx_types types,
3933: ar_transaction_history th,
3934: (select ps.payment_schedule_id ,sum(adj.amount) fin_charge_charged
3935: from ar_payment_schedules ps,
3936: ar_adjustments adj,
3937: ar_lc_cust_sites_t cust_site
3938: where ps.customer_id = cust_site.customer_id
3939: and decode(cust_site.customer_site_use_id,'','X', ps.customer_site_use_id)
3940: = decode(cust_site.customer_site_use_id,'','X', cust_site.customer_site_use_id)
3941: and ps.invoice_currency_code = cust_site.currency_code
4356: cust_sites.min_fc_balance_overdue_type,
4357: cust_sites.min_fc_balance_amount,
4358: cust_sites.min_fc_balance_percent
4359: from ar_cons_inv cons_inv,
4360: ar_lc_cust_sites_t cust_sites,
4361: ar_system_parameters sysparam
4362: where cons_inv.customer_id = cust_sites.customer_id
4363: and cons_inv.site_use_id = cust_sites.customer_site_use_id
4364: and cons_inv.currency_code = cust_sites.currency_code
4436: cust_site.payment_grace_days,
4437: cust_site.min_fc_balance_overdue_type,
4438: cust_site.min_fc_balance_amount,
4439: cust_site.min_fc_balance_percent
4440: from ar_lc_cust_sites_t cust_site,
4441: ar_system_parameters sysparam
4442: where sysparam.org_id = cust_site.org_id
4443: and sysparam.late_charge_billing_calc_mode = 'RUN_TO_RUN_DATE'
4444: and cust_site.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
4488: cust_sites.min_fc_balance_overdue_type,
4489: cust_sites.min_fc_balance_amount,
4490: cust_sites.min_fc_balance_percent
4491: from ar_payment_schedules ps,
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,
4736: cust_sites.min_fc_balance_overdue_type,
4737: cust_sites.min_fc_balance_amount,
4738: cust_sites.min_fc_balance_percent
4739: from ar_cons_inv cons_inv,
4740: ar_lc_cust_sites_t cust_sites,
4741: ar_system_parameters sysparam
4742: where cons_inv.customer_id = cust_sites.customer_id
4743: and cons_inv.site_use_id = cust_sites.customer_site_use_id
4744: and cons_inv.currency_code = cust_sites.currency_code
4800: cust_sites.min_fc_balance_overdue_type,
4801: cust_sites.min_fc_balance_amount,
4802: cust_sites.min_fc_balance_percent
4803: from ar_payment_schedules ps,
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,
4922: sum((case when (decode(ps.class,'CM',ps.due_date,ps.due_date + NVL(cust_site.payment_grace_days,0))) < p_as_of_date then 1 else 0 end)
4923: * ps.amount_due_remaining) overdue_bal,
4924: ps.org_id
4925: FROM ar_payment_schedules_all ps,
4926: ar_lc_cust_sites_t cust_site,
4927: ra_customer_trx ct,
4928: ra_cust_trx_types typ
4929: WHERE ps.customer_id = cust_site.customer_id
4930: AND ps.customer_trx_id = ct.customer_trx_id --Bug12884574
4962: ps.org_id
4963: FROM ar_payment_schedules ps,
4964: ar_receivable_applications ra,
4965: ar_payment_schedules ps_cm_cr,
4966: ar_lc_cust_sites_t cust_site,
4967: ra_customer_trx ct,
4968: ra_cust_trx_types typ
4969: WHERE ps.customer_id = cust_site.customer_id
4970: AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
5008: ps.org_id
5009: FROM ar_payment_schedules ps,
5010: ar_receivable_applications ra,
5011: ar_payment_schedules ps_cm_cr,
5012: ar_lc_cust_sites_t cust_site,
5013: ra_customer_trx ct,
5014: ra_cust_trx_types typ
5015: WHERE ps.customer_id = cust_site.customer_id
5016: AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
5051: + NVL(ra.unearned_discount_taken,0))) overdue_bal,
5052: ps.org_id
5053: FROM ar_payment_schedules ps,
5054: ar_receivable_applications ra,
5055: ar_lc_cust_sites_t cust_site,
5056: ra_customer_trx ct,
5057: ra_cust_trx_types typ
5058: WHERE ps.customer_id = cust_site.customer_id
5059: AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
5093: *(adj.amount)) overdue_bal,
5094: ps.org_id
5095: FROM ar_adjustments adj ,
5096: ar_payment_schedules ps,
5097: ar_lc_cust_sites_t cust_site,
5098: ra_customer_trx ct,
5099: ra_cust_trx_types typ
5100: WHERE ps.customer_id = cust_site.customer_id
5101: AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
5132: sum(ps.amount_due_remaining) open_bal,
5133: sum(ps.amount_due_remaining) overdue_bal,
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)
5166: FROM ar_payment_schedules ps,
5167: ar_distributions ard,
5168: ar_transaction_history ath,
5169: ra_customer_trx_lines lines,
5170: ar_lc_cust_sites_t cust_site,
5171: ra_customer_trx ct,
5172: ra_cust_trx_types typ
5173: WHERE ps.customer_id = cust_site.customer_id
5174: AND ps.customer_trx_id = ct.customer_trx_id --Bug12884574
5408: trx.org_id,
5409: decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.interest_days,1) interest_days,
5410: decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.charge_type, NULL) charge_type,
5411: cust_site.penalty_type
5412: from ar_lc_cust_sites_t cust_site,
5413: ar_late_charge_trx_t trx,
5414: ar_charge_schedule_hdrs sched_hdrs,
5415: ar_charge_schedule_lines sched_lines,
5416: ar_aging_bucket_lines bucket_lines
5832: trx.currency_code,
5833: trx.late_charge_type,
5834: trx.org_id,
5835: trx.display_flag) hdr,
5836: ar_lc_cust_sites_t cust_site,
5837: ar_interest_batches bat,
5838: ar_system_parameters sysparam
5839: where hdr.customer_id = cust_site.customer_id
5840: and hdr.customer_site_use_id = cust_site.customer_site_use_id
6490: penalty_rate,
6491: penalty_fixed_amount,
6492: penalty_schedule_id,
6493: last_accrue_charge_date
6494: FROM ar_lc_cust_sites_t
6495: ORDER BY org_id,
6496: customer_id,
6497: customer_site_use_id,
6498: currency_code;
7305: l_err_flag := TRUE;
7306: END;
7307:
7308: /* delete the data in the interim tables before commit */
7309: delete from ar_lc_cust_sites_t;
7310:
7311: delete from ar_late_charge_trx_t;
7312:
7313: COMMIT;
7330: debug_payment_schedules;
7331: END IF;
7332: /* delete the data in the interim tables before commit */
7333:
7334: delete from ar_lc_cust_sites_t;
7335:
7336: delete from ar_late_charge_trx_t;
7337:
7338: END IF;
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;
7351:
7352: delete from ar_late_charge_trx_t;
7353:
7354: END create_late_charge_document;