Josh
CREATE PROCEDURE [Credit card invoice control] AS
/* The BillingCycles.EBillDays field tells Emerald how far in advance
to create invoices for people that pay by credit card or other
electronic payment methods. A value of 1 (day) tells Emerald to
create invoices for customers whos PaidThru date is <= tomorrow's
date.
This script dynamically changes this value (days) so that the
invoice is created (and charged) the miminum amount of days BEFORE
the payment is due, taking into account weekends and holidays (so
long as the holiday dates are listed below). This minimizes
customer complaints that they're being charged too far in advance.
This cannot be avoided when no one is around to batch credit card
transactions on weekends, etc.
*/
DECLARE @Today datetime,
@EBillDays int,
@Counter int
SELECT @Today = convert(varchar(10), getdate(), 1),
@EBillDays = 1,
@Counter = 0
WHILE (@Counter < @EBillDays)
BEGIN
-- Weekends (assuming no one is there to batch credit cards)
IF datename(weekday, dateadd(day, @EBillDays, @Today))
IN ('Saturday', 'Sunday')
BEGIN
SELECT @EBillDays = @EBillDays + 1
END
-- Holidays (assuming no one will be able to batch credit cards)
ELSE IF dateadd(day, @EBillDays, @Today)
IN (
'1/1/01', -- New Year
'5/28/01', -- Memorial Day
'7/4/01', -- Independence Day
'9/4/00', '9/3/01', -- Labor day
'11/23/00', '11/22/01', -- Thanksgiving
'12/24/00', '12/25/00', '12/24/01', '12/25/01' -- Christmas
)
BEGIN
SELECT @EBillDays = @EBillDays + 1
END
SELECT @Counter = @Counter + 1
END
UPDATE BillingCycles
SET EBillDays = @EBillDays
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart