Accounting Tools

There are two selections within Accounting Tools.  The first is Bulk Assigned Account Change, this tool allows you to change the charges related to a specific account and the devices associated.  The second tool is the Payment Import, this allows you to import payments received through pay.gov.

Bulk Assigned Account Change

Within this menu option, you can modify the charges related to the account for an assignment. This can be done on a single basis or you can perform a mass change. The change is meant to update or remove charge types, organization, and or account number based on a billing start date and end date. For example, if you have an organization switch accounts mid-cycle, you can change the account information for the first half of the billing cycle.

You can also use the bulk assigned account change to remove information from an assignment.

This is a three-step process; the first step is to verify that CES is setup properly. Your cairs.net administrator can access your CES threads through System Admin à Service Management à Service Manager à Reassign Accounts. They need to remove the checkmark from the Disabled field to enable the thread to run on the next cycle.

The second step is to create a reassign request. You will first enter in the general information for the request and then you will create a search parameter to narrow down the number accounts that you are changing.

Reassign Requests

Creating a Change Accounts Request

1. Click the Accounting
2. Within the Navigation pane, click Accounting Tool and then Bulk Accounts Change Requests.
3. Within the Actions pane, click the Change Accounts link
4. The Change Accounts window opens. There are two panes of information. The top pane allows you to search for the accounts that you want to change. The bottom pane is where you will type in the new information for the selected accounts.
5. You need to follow a few rules before you submit a request:

• • Use the Advanced Search options. If you click the Show All button, all the records within the upper pane will change to the new information.
  • The site name must be the same in both panes.
  • It is a good idea to make a backup of your database before you run the request.

Searching for Accounts

6. Before you start to create a search parameter, lets talk about the Search Properties ribbon group, these checkboxes will help to limit the number of accounts you search through. You have the following options:

• • Show All Accounts – this is the default, when you search all account types will appear
  • Hide Call Accounts – place a checkmark in this box to hide all accounts that are related to a Number, you will only see account relating to Equipment & Services
  • Hide Other Accounts – place a checkmark in this box to hide all accounts that are related to Equipment & Services, you will only see accounts relating to Number
  • Show Historical Accounts – place a checkmark in this box to show accounts that have a billing end date.

7. After you have made you selection, you can create a search parameter, within the header of the pane, click the double arrow next to the magnifying glass. This opens the advanced search options.
8. Next to each search field is a search criteria descriptor such as Contains, Equal to, Begins With, etc. This descriptor tells the system how to look for the information within the fields. You can select a different descriptor by clicking the field’s descriptor button.
9. Choose the new descriptor and type the search criteria into the field. Press Enter. Results matching your search criteria display.
10. Once your results appear in the lower pane, you can enter in the new information within the lower pane, complete the following information for the account change (required fields are marked with an asterisk (*)):

• • Charge Type* – click the field to access a dropdown arrow to select the new charge type, the following options are available:
  • All Charges
  • Recurring Charges
  • One-time Charges
  • Call Charges
  • Site* – this field needs to be the same as the field within the upper pane. Click the dropdown arrow to access a list, from the list select the site name.
  • Organization – if you are changing the organization information, click the dropdown arrow to access the list of the organizations, from the list select the new organization. The lowest level of the organization is listed first, after the two ~~ the complete path to the organization is displayed, each level is separated by a >.

! If the organization you selected is associated to an account, the account number auto-populates in the account number field (official accounts only.)

• • Account Number – if you are changing the account number for the assignment, click the dropdown arrow to access the list of account numbers, from the list select the new account number.
  • Billing Start Date* – this populates with the current date, to change the date click the dropdown arrow to access a calendar, from the calendar select the new date. To change the month and year click the right or left arrow, and once at the appropriate month and year, click the number to select the date. The date populates in the box.
  • Billing End Date – click the dropdown arrow to access a calendar and from the calendar, select the billing end date. To change the month and year, click the right or left arrow, and once at the appropriate month and year, click the number to select the date. The date populates in the box.

11. Once you have completed the information, within the Show ribbon group, click the Reassign
12. A message box displays with the total number of records that are going to be modified, within the box click Yes to continue with the request or No to cancel the request.
13. You will receive another message that the request has been sent to CES for processing.
14. Alternately, you can click on the Run Now button within the Process ribbon group, to run the CES process immediately.

Unassign Requests

Creating a Change Account Request

1. Click the Accounting
2. Within the Navigation pane, click Accounting Tool and then Bulk Accounts Change Requests.
3. Within the Actions pane, click the Change Accounts link
4. The Change Accounts window opens. There are two panes of information. The top pane allows you to search for the accounts that you want to change. The bottom pane is where you will type in the new information for the selected accounts.
5. You need to follow a few rules before you submit a request:

• • Use the Advanced Search options. If you click the Show All button, all the records within the upper pane will change to the new information.
  • The site name must be the same in both panes.
  • It is a good idea to make a backup of your database before you run the request.

Searching for Accounts

6. Before you start to create a search parameter, lets talk about the Search Properties ribbon group, these checkboxes will help to limit the number of accounts you search through. You have the following options:

• • Show All Accounts – this is the default, when you search all account types will appear
  • Hide Call Accounts – place a checkmark in this box to hide all accounts that are related to a Number, you will only see account relating to Equipment & Services
  • Hide Other Accounts – place a checkmark in this box to hide all accounts that are related to Equipment & Services, you will only see accounts relating to Number
  • Show Historical Accounts – place a checkmark in this box to show accounts that have a billing end date.

7. After you have made you selection, you can create a search parameter, within the header of the pane, click the double arrow next to the magnifying glass. This opens the advanced search options.
8. Next to each search field is a search criteria descriptor such as Contains, Equal to, Begins With, etc. This descriptor tells the system how to look for the information within the fields. You can select a different descriptor by clicking the field’s descriptor button.
9. Choose the new descriptor and type the search criteria into the field. Press Enter. Results matching your search criteria display.
10. Once your results appear in the lower pane, you can enter in the new information within the top pane, complete the following information for the account change (required fields are marked with an asterisk (*)):

• • Charge Type* – click the field to access a dropdown arrow to select the new charge type, the following options are available:
  • All Charges
  • Recurring Charges
  • One-time Charges
  • Call Charges
  • Site* – this field needs to be the same as the field within the lower pane. Click the dropdown arrow to access a list, from the list select the site name.
  • Organization – if you are changing the organization information, click the dropdown arrow to access the list of the organizations, from the list select the new organization. The lowest level of the organization is listed first, after the two ~~ the complete path to the organization is displayed, each level is separated by a >.

! If the organization you selected, is associated to an account, the account number auto-populates in the account number field (official accounts only.)

• • Account Number – if you are changing the account number for the assignment, click the dropdown arrow to access the list of account numbers, from the list select the new account number.
  • Billing Start Date* – this populates with the current date, to change the date click the dropdown arrow to access a calendar, from the calendar select the new date. To change the month and year click the right or left arrow, and once at the appropriate month and year, click the number to select the date. The date populates in the box.
  • Billing End Date – click the dropdown arrow to access a calendar and from the calendar, select the billing end date. To change the month and year, click the right or left arrow, and once at the appropriate month and year, click the number to select the date. The date populates in the box.

11. Once you have completed the information, within the Show ribbon group, click the Unassign
12. A message box displays with the total number of records that are going to be modified, within the box click Yes to continue with the request or No to cancel the request.
13. You will receive another message that the request has been sent to CES for processing.
14. Alternately, you can click on the Run Now button within the Process ribbon group, to run the CES process immediately.

Viewing Requests

You can view the list of pending and completed requests by accessing the Bulk Accounts Change list. Click Accounting then Bulk Accounts Change, the list of request will not populate right away, you can either click Show All or type a search parameter into the field and click on the magnifying glass to populate the list.  The list displays the Reassign Parameters, Status, and New Charge Type among several other fields of information.  The information displayed is read only.

Payment Import

Pay.gov payments

You can use the Import External Data menu option in cairs.net to import various types of media. Specifically, you can use it to import the payments that you receive through pay.gov. The import allows you to create a sort of template that you can use to import the payments month after month. In addition, the import allows you to select only the information that you want to see within your cairs.net database.

You will need your database to meet a few requirements before you can complete an import. First, you need to define your payment types, methods, locations and check status. Second, you will to have at least one unofficial account created within the database.

It is also a good idea to place the file that you receive from pay.gov in that same location every month. This allows you to change some properties required for the import quickly.

How to Import your Pay.gov statements

1. Create a location on your local drive to place the files from pay.gov.
2. Copy the file to that location (for example: c:\Pay.gov Files).
3. Click the Accounting
4. In the Navigation pane, click Accounting Tools à Payment Import.
5. Within the Actions pane, click the Add New Import
6. The General – Import window opens, complete the following information (required fields are marked within an asterisk (*)):

• • Project Name * – type the name of your import project.
  • Connection String – this provides cairs.net with the location, type and properties of the file that you are importing into your database.

! There are different types of connection strings that you can enter here to get your data to import, here are a few commonly used connection strings, and within each connection string, you must replace the highlighted text with the location and/or name of your file:

CSV File: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Location of your file;Extended Proerties=”text;HDR=Yes;FMT=Delimited

Excel File: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Location of your file\File Name.xls;Extended Properties=Excel 8.0

Access: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Location of your file\File Name.mdb

MS SQL Server: Provider=sqloledb;Data Source=.\SQLEXPRESS;Initial Catalog=Location;Integrated Security=SSPI;

Here are the possible queries that can be used for a specific file type:

CSV File – Select * from [File Name.txt]

Excel File – select * from [Sheet1$] (if you have renamed sheet 1 within your file, replace the name)

Access – Select * from [Name]

MS SQL Server – Select * from [Name]

• • Query * – indicates the location of where cairs.net will look in the above connection to provide the information to import, type your query location.
  • Action * – click the field to access a drop-down list, from the list select Payments.
  • Description – type a description of the import, if necessary.

7. Within the Actions ribbon group, click the Save button to save you information and allow you to continue working with the import.
8. Within the Show ribbon group, click the Source Data The screen changes to display the information that exists with the file to be imported. The information has not been imported yet.
9. Within the Show ribbon group, click the Association The screen changes to allow you to associate the fields that appear within the file to fields that appear within cairs.net.
10. There are two panes of information; the pane on the left (External source field association) display the fields that appear in cairs.net. Within each field there is a drop-down list that displays the fields that appear within your pay.gov file.
11. For each field, click the field to access a drop-down list. Select the appropriate field association. For example, in the Account Number field you would associate it to Account Number.
12. Complete the same process for all fields, you must complete an association for the fields marked with an asterisk (*).
13. From the Default Values pane you can enter in a default for any of the fields that you want imported. For example, if you want the received date to be the date that you import the information into cairs.net, click the Received Date field to access a drop-down calendar, from the calendar select the date.

! The Default Value pane servers two purposes. The first is to allow you to enter in a value for a field, so when the import occurs all records will have the same value in that field. The second is to act as a fail-safe, if there is no value in the field in the pay.gov file, the value within the default field will import. For example, if the received date is blank in the pay.gov file, the date you have entered into the default value will import with the record.

13. Once you have completed your field association, click the Import button within the Show ribbon group.
14. Depending on the amount of information you have within the file, the import may take a while to complete.
15. The screen changes to display the information that you will import into you database, after the field association. The information is still not imported into you database. This allows you to make any changes to the associations before you import it. (Note: There is no undo once you import!)
16. There are three panes of information; the top pane displays the information before it is imported. Pay close attention to the Status column, this column will let you know of any issues with any record. You can change any information within this pane by clicking on the field and selecting the new value.
17. Directly below the ribbon, there are several selections:

• • Imported Selected – once you have selected the records to import, click this button.
  • Select All – click this button to place a checkmark in the Select column for all the records within the pane, this indicates that you want to import the records into you database.
  • Select None – click this button to remove the checkmarks from the Select column.
  • Invert Selection – click this button to switch the checkmarks from being checked -> unchecked and unchecked -> checked.
  • Include rows with warning – click the box to insert a checkmark to include the rows with warnings in your imported file.
  • Batch Import Limit – the default value is 500, this means that cairs.net will import 500 records at a time and continue importing 500 records at a time until the entire file is imported.
  • Log Activity – click the box to insert a checkmark to log the import in System Admin -> Security -> Activity Log. We do not recommend you check this box.
  • Export SQL – you can test your import, this is not recommended unless you have a good knowledge of SQL Server.
  • Save to File – click the button to export the information within the top pane to a text file.

16. Once you have your rows selected, click the Imported Selected Again, depending on how many records you have within your file, this may take a while.
17. Once cairs.net has completed the import, the bottom two panes populate with your records. The bottom pane displays the records that were successfully imported into you database.
18. The middle pane displays the records that failed to import into you database. The Status column displays the reason the import was unsuccessful.
19. At the very bottom of the screen, the import results display. You will see the total records that were successfully imported, the total records that failed, and the time it took to import them.

Imported records that failed

1. While still in the import, make sure you have the Import button highlighted in the Show ribbon group.
2. The middle pane displays the records that failed on your import.
3. The status column displays the reason the import failed, you can make the necessary adjustments to the record by clicking on the appropriate field and selecting the correct information.
4. Directly above the header there are several buttons that allow you to perform actions on the records within the pane:

• • Apply correction to selected.
  • Select All – click this button to place a checkmark in the Select column for all the records within the pane, this indicates that you want to import the records into you database.
  • Select None – click this button to clear the checkmarks from the Select column.
  • Invert Selection – click this button to switch the checkmarks from being checked -> unchecked, and unchecked -> checked.
  • Save to File – click the button to export the information within the top pane to a text file.
  • Pop-up error – click the box to insert a checkmark to indicate that if a record fails you would like to see the pop-up error message. Once the pop-up opens, click the OK button to continue, you are asked if you want to receive any further pop-ups, click the appropriate answer.

3. Once you have your rows corrected and selected, click the Apply correction to selected Depending on how many records you have within your file, this may take a while.
4. Once cairs.net has completed the import, it moves the successful records to the bottom pane. The records that failed remain within the middle pane.
5. Complete the process until all records have successfully imported.

Viewing the import results within Accounts

1. Within the Navigation pane, click the Accounts
2. Within the Navigation Pane, click Accounts > Unofficial Accounts.
3. Before the Account list populates, you must enter in search criteria to populate the list. There are two ways to populate the list:

• • Within the search box, type the account number or portion of the number then click the magnifying glass.
  • Click the Show All

4. The Accounts list opens, displaying the account that matched your search criteria. Double-click the account. The Account window opens.
5. Within the Billing Tools ribbon group, click the upper portion of the Payments
6. Within the Payment History pane in the lower portion of the screen, you can view the payment applied and pending on the account.
7. Cairs.net applies all pending payments to the balance due when you generate the next bill. The status will then change to Applied.