[Home] [Help]
507: BEGIN
508:
509: select min(billing_date)-1
510: into l_next_bill_date
511: from ar_cons_inv
512: where customer_id = p_customer_id
513: and site_use_id = p_site_use_id
514: and currency_code = p_currency_code
515: and org_id = p_org_id
734: select sum(bal_amount)
735: into l_cust_balance
736: from (
737: select sum(ending_balance) bal_amount
738: from ar_cons_inv cons_inv
739: where cons_inv.customer_id = p_customer_id
740: and cons_inv.site_use_id = p_site_use_id
741: and cons_inv.currency_code = p_currency_code
742: and cons_inv.org_id = p_org_id
741: and cons_inv.currency_code = p_currency_code
742: and cons_inv.org_id = p_org_id
743: and cons_inv.status in('FINAL', 'ACCEPTED','IMPORTED')
744: and cons_inv.billing_date = (select max(ci2.billing_date)
745: from ar_cons_inv ci2
746: where ci2.customer_id = p_customer_id
747: and ci2.site_use_id = p_site_use_id
748: and ci2.currency_code = p_currency_code
749: and ci2.org_id = p_org_id
770: 'CM','Y',
771: 'N'),
772: 'N') = 'Y'
773: and (ps.trx_date > (select max(ci2.billing_date)
774: from ar_cons_inv ci2
775: where ci2.customer_id = p_customer_id
776: and ci2.site_use_id = p_site_use_id
777: and ci2.currency_code = p_currency_code
778: and ci2.org_id = p_org_id
782: /* No BFB exists for this customer.. for run date to run date option we
783: have to calculate the ADB even for the period before the first BFB is
784: created. i.e. There should not be any gaps in the ADB calculation */
785: (not exists (select cons_inv_id
786: from ar_cons_inv ci2
787: where ci2.customer_id = p_customer_id
788: and ci2.site_use_id = p_site_use_id
789: and ci2.currency_code = p_currency_code
790: and ci2.org_id = p_org_id
845: select sum(a.balance)
846: into l_cust_eligible_bal
847: from (
848: select sum(ci.ending_balance) balance
849: from ar_cons_inv ci
850: where ci.customer_id = p_customer_id
851: and ci.site_use_id = p_site_use_id
852: and ci.currency_code = p_currency_code
853: and ci.org_id = p_org_id
851: and ci.site_use_id = p_site_use_id
852: and ci.currency_code = p_currency_code
853: and ci.org_id = p_org_id
854: and ci.billing_date = (select max(ci2.billing_date)
855: from ar_cons_inv ci2
856: where ci2.customer_id = p_customer_id
857: and ci2.site_use_id = p_site_use_id
858: and ci2.currency_code = p_currency_code
859: and ci2.org_id = p_org_id
861: and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
862: and ci.status in ('FINAL', 'ACCEPTED','IMPORTED')
863: union all
864: select sum(ps.amount_due_original)
865: from ar_cons_inv ci,
866: ar_payment_schedules ps
867: where ci.customer_id = p_customer_id
868: and ci.site_use_id = p_site_use_id
869: and ci.currency_code = p_currency_code
868: and ci.site_use_id = p_site_use_id
869: and ci.currency_code = p_currency_code
870: and ci.org_id = p_org_id
871: and ci.billing_date = (select max(ci2.billing_date)
872: from ar_cons_inv ci2
873: where ci2.customer_id = p_customer_id
874: and ci2.site_use_id = p_site_use_id
875: and ci2.currency_code = p_currency_code
876: and ci2.org_id = p_org_id
3940: cust_sites.payment_grace_days,
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
3950: and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
3951: decode(l_total_workers,l_worker_number,0,l_worker_number)
3952: and cons_inv.org_id = cust_sites.org_id
3953: and cons_inv.billing_date = (select max(ci2.billing_date)
3954: from ar_cons_inv ci2
3955: where ci2.customer_id = cust_sites.customer_id
3956: and ci2.site_use_id = cust_sites.customer_site_use_id
3957: and ci2.currency_code = cust_sites.currency_code
3958: and ci2.org_id = cust_sites.org_id
4100: 'N'),
4101: 'N') = 'Y'
4102: and ps.trx_date <= l_fin_charge_date
4103: and ( ps.trx_date > (select max(ci2.billing_date)
4104: from ar_cons_inv ci2
4105: where ci2.customer_id = cust_sites.customer_id
4106: and ci2.site_use_id = cust_sites.customer_site_use_id
4107: and ci2.currency_code = cust_sites.currency_code
4108: and ci2.org_id = cust_sites.org_id
4109: and ci2.billing_date < l_fin_charge_date
4110: and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
4111: OR (
4112: not exists (select ci2.billing_date
4113: from ar_cons_inv ci2
4114: where ci2.customer_id = cust_sites.customer_id
4115: and ci2.site_use_id = cust_sites.customer_site_use_id
4116: and ci2.currency_code = cust_sites.currency_code
4117: and ci2.org_id = cust_sites.org_id
4320: cust_sites.payment_grace_days,
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
4330: and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
4331: decode(l_total_workers,l_worker_number,0,l_worker_number)
4332: and cons_inv.org_id = cust_sites.org_id
4333: and cons_inv.billing_date = (select max(ci2.billing_date)
4334: from ar_cons_inv ci2
4335: where ci2.customer_id = cust_sites.customer_id
4336: and ci2.site_use_id = cust_sites.customer_site_use_id
4337: and ci2.currency_code = cust_sites.currency_code
4338: and ci2.org_id = cust_sites.org_id
4412: 'N'),
4413: 'N') = 'Y'
4414: and ps.trx_date <= l_fin_charge_date
4415: and ps.trx_date > (select max(ci2.due_date)
4416: from ar_cons_inv ci2
4417: where ci2.customer_id = cust_sites.customer_id
4418: and ci2.site_use_id = cust_sites.customer_site_use_id
4419: and ci2.currency_code = cust_sites.currency_code
4420: and ci2.org_id = cust_sites.org_id