Billing run troubleshooting
The following list of possible billing problems and solutions can help you in troubleshooting.
Problem
Some old, expired customers were picked up for billing.
Cause
The customer has a Paid Thru date that has been expired for over a year and has never been re-categorized as suspended or as a non-customer.
Solution
Update the customer's status (manually or in mass) to S for suspended or I for inactive, or manually update the customer type from a customer to a non-customer.
Problem
The number of renewal notices printed is greater than the number of customers billed.
Cause
Some customers were manually billed. Generating or printing individual renewal notices does not set the reminder count flag indicating that the renewal notice has been printed. When Printing Options are run separately from the Billing Options, iMIS prints a notice for customers with outstanding balances in which the reminder count is still set to zero.
Solution
The best way to avoid this situation is to select the Billing Options and Printing Options at the same time. To print the initial notices separately, use the Ad hoc Search option to ensure that only the desired notices are printed (for example, those for a specific Bill Date or Run Date).
If you have already encountered the problem and need to reprint only the desired notices, you may be able to work around this situation by printing the initial notices as Reminder Notices. To do this, select First Reminders, for example, and then enter the original Bill Date or Run Date in the both the Begin and End dates of the reminder date range.
Warning!
If you have a special notice-specific title or Billing and Reminder Notice text that prints differently for Reminder Notice, this may not be a viable option.
Problem
Too few customers were billed. For example, the pre-billing report shows 1,000 customers have Paid Thru dates expiring within the month. When billing was generated, only 500 notices were generated.
Cause
In order to bill customers, both the Paid Thru date and the Renewed Thru date must be earlier than the billing Effective Date. If too few customers are billed, the problem typically is caused by a Renewed Thru date that is greater than the Effective Date. The Renewed Thru date is advanced to the projected term Paid Thru or expiration date at billing time, preventing the customer from being rebilled for the same customer term in subsequent billing runs. An incorrect Renewed Thru date may result from two different causes:
- A billing run was started but canceled without restoring a backup, or
- The Paid Thru date was manually overridden to an earlier date without a similar modification being made to the Renewed Thru date.
Solution
The Renewed Thru date can be viewed from the individual customer's Financial tab. If only a few customers were missed due to a problem with the Renewed Thru date, the date can be corrected or blanked out through an Edit change and the missed customers can be billed individually (see Running individual dues billing) by clicking Create Invoice on the Billing tab. If large numbers of customers were missed, you should make mass corrections through a correction script.
Note: You may want to add the Name.PAID_THRU field to the list of Change Logging fields in order to track manual changes made to the Paid Thru date.
Problem
Too many customers were billed. For example, the pre-billing report shows 500 customers have Paid Thru dates expiring within the month. When billing was generated, 1,000 customers were billed.
Cause
This problem usually occurs only on the first dues billing run after conversion to iMIS because billing data, particularly the Renewed Thru date, has not been properly set up during pre-conversion.
This problem may also occur in late billing runs because the Name_Fin records (where the Renewed Thru date is maintained) were not populated in post-conversion. If the Name_Fin record does not exist for the customer at billing time, the billing procedure will not be able to update the Renewed Thru date. This prevents the customer from being rebilled for the same billing term in a subsequent billing run.
Solution
Restore from backup and properly populate the missing Renewed Thru dates.
If the Name_Fin records are missing from a missed post-conversion step, execute the procedure to generate the missing records. Select Utilities > Reports and Queries > Set up tables report type, and execute “Add missing Name_Fin records.”
Problem
Invalid column name error message. When you attempt to bill, you get an SQL error message, such as:
DuesBill1/103 SQL_CHANNEL1 207 Invalid column name “Assets.”
Cause
The column name “Assets” does not exist in the Name_All view because the user-defined table that contains it is not included in the Name_All view.
Solution
Add the table to the Name_All view.
Problem
Invalid column prefix error message. When you attempt to bill, you get an SQL error message, such as:
DuesBill1/103 SQL_CHANNEL1 107 The column prefix 'Name' does not match with a table name or alias name used in the query.
Cause
You have not used the required Name_All prefix in your special pricing formula. Even when you are referencing a field within the Name table, you must set up and cross-reference the Name_All view for all special pricing methods except flat rates.
Solution
Change the prefix in all special pricing field references to Name_All. Be sure to generate the Name_All view.
Problem
Line items are being billed for strange, negative amounts, such as -999.00 or -900.00.
Cause
There is a problem with the application of a special pricing formula during the billing process. This could be caused by:
- The Name_All view does not contain a record for the customers or subscribers who were billed the negative amounts, or
- There is an SQL syntax error in your special pricing calculation.
Solution
Verify the completeness of the Name_All view. Run the following script for one customer who was billed a negative amount:
select ID from Name_All where ID = '101'
Note: Substitute the correct ID in place of 101.
If blank results are returned, the Name_All view is not complete. Check the test script again after populating missing records. Note that the Name_Fin table must also be properly populated in order for the Name_All view to be complete.
If you suspect there may be missing Name_Fin records due to a missed post-conversion step, select Utilities > Reports and Queries. Select the Table Maintenance report type and execute “Add missing Name_Fin records.”
If the Name_All view appears to be complete, check your special pricing formulas for accuracy and correct as needed.
Problem
Customers not being billed for a Dues billing cycle when the Add Member Type Items option is enabled.
Solution
Isolate the problem to a specific customer type/category that is not being billed. Find an individual who should have been billed that has this customer type/category. Verify that the products are set up in the Set up customer types window that the customer type, and that customer and nonmember pricing is correct. Verify that the Name.PAID_THRU date and the Name_Fin.RENEWED_THRU dates are less than the Effective Date. Check the Bill Date in the Billing tab to determine whether the customer was billed, but was billed an incorrect amount. If the customer was billed but billed incorrectly, then verify whether that a special pricing rule was applied.
Run the following SQL query to see if this customer is included in the Name_ALL view: Select ID from Name_ALL where ID = '####'
If this does not return a record, then this customer does not have a record inserted into either the Name_Fin table or in a user-defined table marked “Use for Dues Pricing.”
Problem
Customers not being billed for a non-dues billing cycles when products are manually entered on the Billing tab.
Solution
The subscription record must have been previously created in the Billing tab. Verify the Bill Date on this window to determine if the customer was billed but billed an incorrect amount. If the customer was billed incorrectly, check to see if a special pricing rule was applied. If the customer was not billed, then verify that the Subscriptions.PAID_THRU and Subscriptions.BILL_THRU dates are less than or equal to the Effective Date entered.
Problem
Delays are experienced when processing payments for products on one invoice.
Solution
To speed up processing, add an additional index to the Subscriptions table consisting of INVOICE_REFERENCE_NUM and INVOICE_LINE_NUM.
Problem
You need to add subscription dates to your Renewal Notices.
Solution
Add Subscriptions.BILL_BEGIN in the Parameters window of the report specification. The date will be printed in mm/dd/yyyy format.
Problem
You have a custom field that contains a year and would like to use it for special pricing.
Solution
You can use the SQL Anywhere DATEPART function in special pricing by adding a Name_Custom.MEMBER_YEAR to the Name_All view. The following calculation would then be used for special pricing:
If Name_All.MEMBER_YEAR >= datepart(year,Today(*)) Then 100 Else 200 Endif
The datepart function takes the year from the current date, Today(*). The “if” statement compares the current year to the Name_All.MEMBER_YEAR, resulting in customers being charged 100 if the Member_Year is greater than the current year, or 200 if the Member_Year is less than the current year.
Problem
You are able to bill individually, but not mass billing or only bill amounts entered as the standard customer or non-customer amounts because special pricing is not being recognized.
Solution
There is a temporary table that is built when running a mass billing which creates a special pricing table for all dues products whether they are included in the billing cycle or not. This temporary table creates the price if special pricing is used, by converting the BASE 1 filed in the Product_Price table to a money field. To verify that there is only numeric data in this field, query the Product_Price table to see if you received any error messages by using the following SQL query:
Select PRODUCT_CODE, BASE_1, PRICE=convert(money,pp.BASE_1) from Product_Price
Problem
If you create a query in MS Query using more than one table, and then try to merge the date in MS Word, the following error message occurs: Word was unable to open the data source.
Solution
An MS Query limitation restricts queries to one table. Perform your queries using only one table or use MS Access to perform the query. For more information, see MS Support Article 193715.