[Home] [Help]
901: BEGIN
902:
903: select min(billing_date)-1
904: into l_next_bill_date
905: from ar_cons_inv
906: where customer_id = p_customer_id
907: and site_use_id = p_site_use_id
908: and currency_code = p_currency_code
909: and org_id = p_org_id
1126: select sum(bal_amount)
1127: into l_cust_balance
1128: from (
1129: select sum(ending_balance) bal_amount
1130: from ar_cons_inv cons_inv
1131: where cons_inv.customer_id = p_customer_id
1132: and cons_inv.site_use_id = p_site_use_id
1133: and cons_inv.currency_code = p_currency_code
1134: and cons_inv.org_id = p_org_id
1133: and cons_inv.currency_code = p_currency_code
1134: and cons_inv.org_id = p_org_id
1135: and cons_inv.status in('FINAL', 'ACCEPTED','IMPORTED')
1136: and cons_inv.billing_date = (select max(ci2.billing_date)
1137: from ar_cons_inv ci2
1138: where ci2.customer_id = p_customer_id
1139: and ci2.site_use_id = p_site_use_id
1140: and ci2.currency_code = p_currency_code
1141: and ci2.org_id = p_org_id
1162: 'CM','Y',
1163: 'N'),
1164: 'N') = 'Y'
1165: and (ps.trx_date > (select max(ci2.billing_date)
1166: from ar_cons_inv ci2
1167: where ci2.customer_id = p_customer_id
1168: and ci2.site_use_id = p_site_use_id
1169: and ci2.currency_code = p_currency_code
1170: and ci2.org_id = p_org_id
1174: /* No BFB exists for this customer.. for run date to run date option we
1175: have to calculate the ADB even for the period before the first BFB is
1176: created. i.e. There should not be any gaps in the ADB calculation */
1177: (not exists (select cons_inv_id
1178: from ar_cons_inv ci2
1179: where ci2.customer_id = p_customer_id
1180: and ci2.site_use_id = p_site_use_id
1181: and ci2.currency_code = p_currency_code
1182: and ci2.org_id = p_org_id
1236: select sum(a.balance)
1237: into l_cust_eligible_bal
1238: from (
1239: select sum(ci.ending_balance) balance
1240: from ar_cons_inv ci
1241: where ci.customer_id = p_customer_id
1242: and ci.site_use_id = p_site_use_id
1243: and ci.currency_code = p_currency_code
1244: and ci.org_id = p_org_id
1242: and ci.site_use_id = p_site_use_id
1243: and ci.currency_code = p_currency_code
1244: and ci.org_id = p_org_id
1245: and ci.billing_date = (select max(ci2.billing_date)
1246: from ar_cons_inv ci2
1247: where ci2.customer_id = p_customer_id
1248: and ci2.site_use_id = p_site_use_id
1249: and ci2.currency_code = p_currency_code
1250: and ci2.org_id = p_org_id
1252: and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
1253: and ci.status in ('FINAL', 'ACCEPTED','IMPORTED')
1254: union all
1255: select sum(ps.amount_due_original)
1256: from ar_cons_inv ci,
1257: ar_payment_schedules ps
1258: where ci.customer_id = p_customer_id
1259: and ci.site_use_id = p_site_use_id
1260: and ci.currency_code = p_currency_code
1259: and ci.site_use_id = p_site_use_id
1260: and ci.currency_code = p_currency_code
1261: and ci.org_id = p_org_id
1262: and ci.billing_date = (select max(ci2.billing_date)
1263: from ar_cons_inv ci2
1264: where ci2.customer_id = p_customer_id
1265: and ci2.site_use_id = p_site_use_id
1266: and ci2.currency_code = p_currency_code
1267: and ci2.org_id = p_org_id
4355: cust_sites.payment_grace_days,
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
4365: and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
4366: decode(l_total_workers,l_worker_number,0,l_worker_number)
4367: and cons_inv.org_id = cust_sites.org_id
4368: and cons_inv.billing_date = (select max(ci2.billing_date)
4369: from ar_cons_inv ci2
4370: where ci2.customer_id = cust_sites.customer_id
4371: and ci2.site_use_id = cust_sites.customer_site_use_id
4372: and ci2.currency_code = cust_sites.currency_code
4373: and ci2.org_id = cust_sites.org_id
4515: 'N'),
4516: 'N') = 'Y'
4517: and ps.trx_date <= l_fin_charge_date
4518: and ( ps.trx_date > (select max(ci2.billing_date)
4519: from ar_cons_inv ci2
4520: where ci2.customer_id = cust_sites.customer_id
4521: and ci2.site_use_id = cust_sites.customer_site_use_id
4522: and ci2.currency_code = cust_sites.currency_code
4523: and ci2.org_id = cust_sites.org_id
4524: and ci2.billing_date < l_fin_charge_date
4525: and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
4526: OR (
4527: not exists (select ci2.billing_date
4528: from ar_cons_inv ci2
4529: where ci2.customer_id = cust_sites.customer_id
4530: and ci2.site_use_id = cust_sites.customer_site_use_id
4531: and ci2.currency_code = cust_sites.currency_code
4532: and ci2.org_id = cust_sites.org_id
4735: cust_sites.payment_grace_days,
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
4745: and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
4746: decode(l_total_workers,l_worker_number,0,l_worker_number)
4747: and cons_inv.org_id = cust_sites.org_id
4748: and cons_inv.billing_date = (select max(ci2.billing_date)
4749: from ar_cons_inv ci2
4750: where ci2.customer_id = cust_sites.customer_id
4751: and ci2.site_use_id = cust_sites.customer_site_use_id
4752: and ci2.currency_code = cust_sites.currency_code
4753: and ci2.org_id = cust_sites.org_id
4827: 'N'),
4828: 'N') = 'Y'
4829: and ps.trx_date <= l_fin_charge_date
4830: and ps.trx_date > (select max(ci2.due_date)
4831: from ar_cons_inv ci2
4832: where ci2.customer_id = cust_sites.customer_id
4833: and ci2.site_use_id = cust_sites.customer_site_use_id
4834: and ci2.currency_code = cust_sites.currency_code
4835: and ci2.org_id = cust_sites.org_id