How to use the XLS to AL3 Integration Utility

1. From the 360 Toolbox menu, select Launch Programs > Custom XLS to AL3 utility.
2. At the Input File Name box, click Browse and select your .xls file. The Excel file must be closed before you can convert it with the utility.
3. The Output File Name populates where the AL3 file will be saved. Record this path and file name.
4. Select the appropriate company from the AMS360 Parent Company list.
5. Select a Statement Date.
6. Select Default Template from the Select Template drop-down list.
7. Click Convert.

When the message "XLS to AL3 Export completed successfully appears," click OK and then close the Utility dialog box.

1. When you have the AL3 file you can process it using the download functions.
2. From the 360 Toolbox menu, select Integration > Download > Move Download Files.
3. Browse to the location and select the file provided in the Output File Name box in Step One above.
4. Click Open. The file is processed.

You will want to check the Customer/Policy Assign area in AMS360 after downloading each file to see if any accounting transactions must be manually matched to a customer and/or policy in AMS360.

1. From the 360 Toolbox menu, click Integration >Download >Assign Customers/Policies.
2. Select Accounting Transactions.
3. Assign any unmatched transactions to the appropriate customer and policy.
4. Once all items are assigned, click Save and Close.
5. In the Company center of AMS360, view the company for the statement and either create the statement or view the register to locate the In-Progress Direct Bill Entry statement, depending on which option you have sent for the company.

You now need to create and/or post the Direct Bill Statement. This process occurs in the Company Center for the company named in the statement.

1. In AMS360, click the Company tab.
2. Search for and open the company named in the statement.
3. Depending on the option you have set for the company do one of the following:

If you have set Direct Bill Entry for this Company:

1. In the Sidebar menu under Views, select Register and locate the In-Progress Direct Bill Entry statement and click the link to open it.
2. Review the statement and when finished, change the Status to Ready to Pay.
3. Click Post (green pushpin) on the toolbar.

- OR -

If you have set Direct Bill Statement for this Company:

1. In the Sidebar menu under Actions, select Direct Bill Statement.
2. Make the selections based on the statement you've imported using the XLS to AL3 Utility.
3. Click Create Statement.
4. The Statement opens where you can manually or auto-match transactions.
5. When you have finished matching transactions, change the status of the statement to Ready to Pay.
6. Click Post (green pushpin) on the toolbar.

If you receive an error during the XLS to AL3 conversion, use the tips that follow to validate the formatting in your Excel file is correct.

• Columns must be in the correct order and all fields filled in. There must be one header row, but no footer row.
• Line of Business code must match an active LOB Code in the management system.
• Transaction Type must be a legitimate AL3 code, e.g. NBS, REW or PCH.
• Commission percentages are always formatted as a decimal, i.e. 3.75% must be shown as .0375.
• Negative amounts are always displayed with a minus sign, not parentheses, i.e. -125.30 not (125.30).
• Transaction Effective Dates must fall within the policy term dates.
• The columns should have the following formatting set:

Use Find and Replace (ctrl+f) in Excel to locate and remove any commas or special characters from the data.

Sample:

We have seen cases where the session log shows an error or unable to load statement record for item number XXX, due to missing policy effective date. In this situation try saving the excel file as a CSV and closing it. Open the file again and save as XLS and close it. Then try the integration again. Also make sure the file is not open in Excel when trying to upload it.

Before using the utility, please verify the following company setups:

• The carrier that sent the XLS must be set up for download in AMS360 as an Insurance company. AMS360 can't download commissions to a broker.
• The company must have an NAIC code. It can be fictitious. It must be unique and no more than six characters and must be entered at the parent level (under the Company Name - not under the writing companies).
• The company must also have a download address. It can be fictitious and must be 12 characters. For example: IBMBLUECROSS.

More information can be found in the Help topic - Custom XLS to AL3 Utility.

• PAB - Premium audit
• PCH - Policy change
• POL - Policy (unspecified)
• REI - Reinstatement
• REW - Rewrite
• RWX - Non-renewal notified PolHolder
• XLC - Cancellation confirmation
• XLN - Cancellation request
• ARR - Anniversary re-rate
• NBQ - New business quote
• NRA - Non-renewal notified Agency
• PCQ - Policy change quote
• PNQ - Policy inquiry
• RIX - Reissue
• RNR - Reversal of non-renewal
• RRQ - Renewal requote
• RWQ - Renewal quote
• RWR - Renewal request
• SYN - Policy Synchronization
• SYR - Policy Synchronization Request
• BNB - Binder New Business
• BND - Binder Billable
• BPC - Binder Endorsement
• BRW - Binder Renewal
• NBS - New business
• RWL - Renew policy
• STL - Installment