Charging against a value

  • Thread starter Thread starter grep
  • Start date Start date
G

grep

I allow clients to purchase support time in discounted, pre-paid
bundles. They can buy them all at once, or on an accrual basis, over a
year. When services are rendered, I want to be able to charge the time
against their pre-paid hours, if available, first.

It is possible to go over the pre-paid hours, in which case charges
would be applied. This is especially so if a client purchases them on an
accrual basis - they may have purchased a contract good for 24 hours of
service, but they only accrue 2 hours per month. So, if the contract
begins Jan. 1, and they use nothing until Feb., but then they use 6
hours of time, they have used up their accrued 4 hours, plus they will
be charged for the additional 2 they had not yet accrued.

I need a way to handle this. I need to know, at a given time, how many
hours the client has in the bank. I need to decrement trouble-ticket
time against that time, with any overlap charged seperately. At the end
of the contract period, I need to zero-out the remaining hours, and
close the contract. A new contract will have to be purchased, starting
the process again.

It seems to me that I need a combination of table-design, query and
forms coding help to do this. I'm here for the table part, but if you
have any suggestions for the rest of it, I'll be glad to listen.

grep
 
grep said:
I allow clients to purchase support time in discounted
Click to expand...

grep
Click to expand...

The following is a *very* rough *guess* based on the supplied
narrative and a couple of hours of thought. It is not a fully
analyzed logical or physical data model.


CREATE TABLE Clients
(ClientID INTEGER
,CONSTRAINT pk_Clients
PRIMARY KEY (ClientID)
)

CREATE TABLE BillingRates
(BillingRateID INTEGER
,StartDate DATETIME
,EndDate DATETIME
,HourlyRate CURRENCY
,CONSTRAINT pk_BillingRates
PRIMARY KEY (BillingRateID)
)

Note: The dates allow billing rates to change across time, but still
allow you to know how much a client was charged in the past.

CREATE TABLE BillingPlans
(BillingPlanID INTEGER
,BillingRateID INTEGER
,Discount DECIMAL(2,2)
,AccrualTermination DATETIME
,SupportHours INTEGER
,PlanCreated DATETIME
,CONSTRAINT pk_BillingPlans
PRIMARY KEY (BillingPlanID)
,CONSTRAINT fk_BillingPlans_BillingRates_BillingRateID
FOREIGN KEY (BillingRateID)
REFERENCES BillingRates (BillingRateID)
)

Note: The BR1.HourlyRate * BP1.Discount * BP1.SupportHours equals
the price of the BillingPlan.

Note: AccuralTermination will be NULL on purchases of lump sums of
hours. On monthly accrual plans, it will be some date that is
compared as less than or equal to the current date in the regular
job cycle that updates SupportTimeAvailable. This will allow the
same client to purchase and gain hours from multiple plans
simultaneously.


CREATE TABLE ClientBillingPlans
(ClientBillingPlanID INTEGER
,ClientID INTEGER
,BillingPlanID INTEGER
,PurchaseDate DATETIME
,CONSTRAINT pk_ClientBillingPlans
PRIMARY KEY (ClientBillingPlanID)
,CONSTRAINT fk_ClientBillingPlans_Clients_ClientID
FOREIGN KEY (ClientID)
REFERENCES Clients (ClientID)
,CONSTRAINT fk_ClientBillingPlans_BillingPlans_BillingPlanID
FOREIGN KEY (BillingPlanID)
REFERENCES BillingPlans (BillingPlanID)
)

CREATE TABLE SupportTimeAvailable
(SupportTimeAvailableID INTEGER
,ClientBillingPlanID INTEGER
,HoursAvailable INTEGER
,CONSTRAINT pk_SupportTimeAvailable
PRIMARY KEY (SupportTimeAvailableID)
,CONSTRAINT
fk_SupportTimeAvailable_ClientBillingPlans_ClientBillingPlanID
FOREIGN KEY (ClientBillingPlanID)
REFERENCES ClientBillingPlans (ClientBillingPlanID)
)

CREATE TABLE SupportTimeProvided
(SupportTimeProvidedID INTEGER
,ClientID INTEGER
,StartTime DATETIME
,EndTime DATETIME
,SupportDescription TEXT(255)
,CONSTRAINT pk_SupportTimeProvided
PRIMARY KEY (SupportTimeProvidedID)
,CONSTRAINT fk_SupportTimeProvided_Clients_ClientID
FOREIGN KEY (ClientID)
REFERENCES Clients (ClientID)
)

Note: SupportDecription would probably best be replaced by a foreign
key that went off to a parent table that described the support and
provided the identity of your product supplied to the client, and
other information, etc.

Note: Whenever a billing cycle is run, the calculation of hours
provided in SupportTimeProvided is compared to HoursAvailable. When
the calculation is < 0, the ABS() of that value is the total number
of hours you charge at the full current hourly rate. Feed those
values into the invoices you send to your clients.


Sincerely,

Chris O.
 
Back
Top