Our accountants are going crazy because of the lack of information they are
receiving from me/Emerald regarding monthly income and "sales".
Our accountants need a listing of all invoices (including voided) created
per month (they call these "sales"), the amount due for each invoice, and
the total dollars invoiced for that month (preferably separating voided and
non-voided invoices). They also need a listing of all payments received
every month and the invoices they were associated with, along with the
total amount of money received and credited back. The names of the billing
contacts should be associated with each of these listings.
The scripts that I'm mentioning below have been attatched. If someone
knows a way to incorporate this info into some form of report, it'd be
perfect. Right now, I'm having to go into the script from time to time and
modify dates to reflect which month I'm inquiring about. If anyone can see
any problems with the scripts and knows of a solution, please let me know.
Last night, I made up a SQL script to produce a listing of all voided
invoices because apparently, they're not tracked in the "invreg.rpt" report
and our accountants need that info for whatever reason. At the end, a
total is displayed for the total dollar amount for the voided invoices.
Does the total dollar amount in invreg.rpt include voided invoices as well
as non-voided??
A second set of scripts that I made lastnight are for displaying payments
that have been received (or at least marked paid) along with the InvoiceID
number that the payment was for, the first and last name, date, and the
amount payed per payment. The problem with this script is that it displays
duplicate invoices/payments for any MBR that contains more than one
subaccount (if an MBR/payment has 3 subaccounts/services, 3 identical lines
will display on the output of this script). A second script totals the
payments.
Here's where there are a couple problems:
If a customer makes a payment today for a monthly payperiod, for an invoice
created yesterday, then a week from now that customer discontinues service,
this user should get 75% of his payment back for unused service. It's
obviously no big deal for the accountants to write the check and mail it,
but now there's a discrepency in the database for how much money has been
received, UNLESS a credit can be entered in for that MBR. How can this be
handled??
A few weeks ago, a person paying by credit card canceled service the day
after I charged his card for the next month's service. I credited back his
CC the $19.95 and marked his MBR inactive in Emerald, but the database
shows that a payment was received for 19.95, when in fact it was credited
back in "reality". So, the total dollar amount for received payments is
incorrect because there's no way to "void" a payment.
Another payment problem is when someone pays more (or less, I suppose) than
what their present invoice calls for. If the invoice says $19.95 due and
they send a check for $20.00, there's no way to account for the extra
nickel in the payment when it's entered into Emerald. Once again, when a
total for the month is obtained (received payments), the figure will be
inconsistent with the actual physical money received/deposited by the
accountants.
Are there any cures for these ailments?
By the way, what do the numbers represent in the "Deferred Revenue" report?
The 2 sets of scripts that I mentioned above:
voided-invoices.sql
-------------------
select i.InvoiceID, ma.FirstName, ma.LastName, InvoiceCreateDate=i.date,
i.Type, i.Amount
from masteraccounts ma, invoices i
where i.date > "Jan 1 1997" and i.date < "Feb 1 1997"
and i.type = "Void"
and ma.customerid = i.customerid
order by invoiceid
select TotalDollarsInvoiced=sum(Amount)
from Invoices
where date > "Jan 1 1997" and date < "Feb 1 1997"
and Type = "Void"
payments-received.sql
---------------------
select ii.InvoiceID, ma.FirstName, ma.LastName, PaymentDate=p.date,
p.Amount
from MasterAccounts ma, Payments p, invoiceitems ii, invoices i
where p.date > "Jan 1 1997" and p.date < "Feb 1 1997"
and i.invoiceid = ii.invoiceid
and ma.customerid = p.customerid
and p.paymentid = ii.paymentid
order by p.date
select TotalDollarsReceived=sum(Amount)
from Payments
where date > "Jan 1 1997" and date < "Feb 1 1997"
Thanks for any help!
Josh Hillman
hillman@talstar.com