AIM Integration: Import Transactions Into Connect® - Setup and Technical Info

Information related to the setup and import of transactional data into the InsCipher Connect® Portal.

Table of Contents

1. Overview
2. Getting Setup
3. Editing Integration Settings After Installation
4. Standard Field Mapping Detail
5. Using a Custom SQL View
6. What About Documents?
7. Technical Installation Details
8. Version History

What is InsCipher Connect®?

Using InsCipher’s Connect® Integration Tool For

For those who have made the excellent decision to utilize the InsCipher Connect® filing management system, we have made it seamless to connect systems. To streamline the process of getting your surplus lines' policy data into Connect®, we have developed a Vertafore AIM® Integration Tool that will do the heavy lifting for you automatically. InsCipher is an official orange partner with Vertafore. Before you get started, please check with your IT/security team to ensure that there aren’t any firewalls or restrictions in place that would prevent the integration from being set up and for the data transfer to take place. Please contact support@inscipher.com if there are specific questions related to the setup that are not answered in the guide below:

How To Get Your Policy Data Into InsCipher

The Integration Tool will automatically extract relevant surplus lines policy data out of your local AIM® database and import that data into Connect® daily. Typically, this occurs around 2 am MST. Policies included in the daily import will be those that were marked as having the Invoice Exported, marked as being Collected By the Agency, and marked as Taxed. Individual installment transactions are excluded from the import. From a dev perspective, invoice transactions that have the StatusID = “E”, CollectedBy = “A”, and the Taxed = "Y" on the invoice header. In addition, we are bringing in transactions with the MemoInvoiceFlag = "Y" and including all Installment Invoices that have an InvTypeID = "Y". The reason why we are not including all invoices with other statuses is that there may be changes to the invoice prior to it being exported to accounting, and we believe that by only pulling policies in an exported invoice status, Connect® can provide a cleaner and more up-to-date list of transactions.

To Get Setup

To begin using the integration, you will first need to log into your Connect® account. Click on "Setup" in the left navigation bar >  “Integrations” > then click on the “ADD NEW” button to start the Integration Wizard. This wizard guides you through a four-step process of installation.

Step 1) Select Your Agency Management System

• Select “Vertafore AIM” > Click “Continue”
• Select "Create Payment Batch" if you want to utilize the feature described in this article.
• Under the "Choose Physical State Field" setting, indicate which value you want to use in AIM for the Physical / Tax State in InsCipher Connect®.
  • Use "Quote State" to pull from the Quote.State field
  • Use "Quote Tax State" to pull from the Quote.TaxState field.

Step 2) Integration Setup & Connection

In this step, you will be downloading and installing the InsCipher Integration Tool. You will also receive your InsCipher Integration Token in this step, which you then will use during the installation process.

During installation, you will be asked to enter your SQL server login credentials and your Integration Token, after which you will click “Connect” to link up with InsCipher. It can take a few minutes to process. If you are having issues connecting, ensure that your SQL login credentials are correct and that you can read the “InvoiceHeader” and "InvoiceDetails" tables in the database on that server using the same credentials. If you still are having trouble, click on the Send diagnostics button in the top right corner of the IC Tool.

Step 3) Fields Mapping

Once the installation is complete and the Integration Tool is synced with InsCipher Connect®, you will then be asked to map certain fields before the integration will be fully setup. These fields will automatically pull into the Connect® portal based on the queried data in your AIM database. You will need to map your agencies/divisions, broker/carrier fees, and your coverage codes in order to proceed to step 4.

Map Your Agencies

It is important that we map the agencies or divisions in AIM to the agencies created in InsCipher Connect®. You can map multiple divisions in AIM® to a single agency in Connect® or it can be a one-to-one relationship. Regardless, you must map your agencies before your integration will be fully set up. If you need to create additional agencies, refer to the “Getting Started” page in the help section under “Creating Agency Admin User.”

Map Your Reportable Broker And Carrier Fees

InsCipher has made it possible for you to determine which fees in AIM® get imported into InsCipher Connect® as either "Broker Fees" or "Carrier Fees". 

Map Your Coverage Codes

Lines of businesses do and can vary from system to system. Where possible, InsCipher has compiled a list of state-specific approved coverage codes and has mapped generic lines of businesses to these codes to simplify this process. This generic line of business list and how these are mapped to state-specific codes (if these exist) can be found here. Should you require additional generic code options or have questions about how this generic mapping works, please reach out to your implementation specialist or reach out to support@inscipher.com.

Filing Type Mapping

Filing or Transaction types do vary from AIM and InsCipher. In order to ensure these are mapped correctly for your workflows, InsCipher gives you the option to add your own custom mapping.

Click the "Add New" green button in this section to add an AIM transaction code related to the filing type, and then map this to a filing type in the InsCipher system. As this can vary for positive and negative amounts, you have the option to set this to be different so that it matches your workflow.

Step 4) Summary

Now the setup is complete. If there is an issue, the system will inform you. From this screen, you may go back into the previous sections as well. You also can set up a manual trigger to import transactions from your AIM® database to pull up to two weeks of historical data.

What If I Need To Change My Integration Settings?

InsCipher gives you the flexibility of making changes to your configuration after setup is complete. 

Regenerating Your InsCipher Token

At times you may be asked to regenerate a new InsCipher Token. Typically, this won't be needed unless there is a major version update. In this case, InsCipher would let you know this is needed. To regenerate your InsCipher Token, in the InsCipher Connect® portal, go to Setup > Integrations > Select “Details” under the AIM integration > Go to Step 2 (click on step name “Integration Setup & Connect” > Click on the Regenerate Token button.

Copy and paste the token into the InsCipher Integration Tool by selecting the application icon in the Windows server tray menu > clicking “settings” > updating your token > and clicking the “Connect” button.

How Do I Edit My SQL Login Credentials?

You do this by selecting the application icon in the Windows server tray menu > clicking “settings” > updating your SQL login credentials > and clicking the “Connect” button.

Deleting Integration Connection in Connect®?

If you want to remove/delete the integration connection in Connect®, go to Setup > Integrations > and click the “Delete” button next to the AIM® Integration. If you delete and you wish to reconnect, you will have to restart the integration setup process from Step 1.

What If There Are Updates to the Application?

The latest versions (Version 1.6.0 and above) offer automatic updates. Currently, InsCipher will communicate with you if there are any updates regardless so you are aware of any changes. You will be notified of the integration tool and also if updates are available. 

How Will I Know if there is an Issue?

There are a few ways in which you will know if there is an issue with the Integration tool:

Review the Filing Import Log

All transactions that are imported via the Integration Tool will show in the Filings Import Log page (found by clicking on the “Filings Import Log” link in the left navigation bar).

This log lists the number of transactions that were imported on a specified day, and which transactions were not imported. If there was an error, the batch ID number will appear in blue as a link. Clicking this link will download an error log so that you can review the reason why the transaction was not imported. In most cases, the error will be due to duplicate transactions being filtered out. Transactions are not imported twice if they have the same AIM invoice ID number. You also have the ability to rerun the import. 

You can also utilize the "Not Imported Filings" tab, which will summarize all not imported filings. You can filter these out by import date. 

Email and Dashboard Notifications

If there is an issue with the field mapping or a connection issue, the Filing Agency Admin user of your organization will get an email notification informing him/her of the issue. You will also be notified on the dashboard in Connect®.

If it is a connection issue, first try resetting your token then go into the InsCipher Integration application that was installed on your server (found by clicking on the InsCipher icon in the menu tray) and on “settings”. Paste the updated token on the setup page and click the “Connect” button. If the issue persists, please contact support@inscipher.com.

If it is an out-of-sync field, go to the Integrations page in Connect® and on the “Details” link next to the setup integration (the status should show that mapping is needed).

Clicking on the “Details” button will take you to the Fields Mapping page where you may map any fields that may be out-of-sync. These will be highlighted. You can quickly filter these fields using the “Mapped?” filter.

What Specific Fields will Import and Not Import?

We have done our best to map existing fields from AIM® into InsCipher to minimize the amount of dual entry that takes place. Where possible, we will work with Vertafore AIM® users to map additional fields. The Connect® system will flag any transactions that may have missing data that is required when filing with a state, so this information may be added later by a Filing Admin user:

Using A Customer SQL View (Optional)

For some clients, using our standard SQL query and transaction code mapping may not be adequate for your needs and may require some customization. Should you require additional customization, take advantage of our custom view feature.

The purpose of this feature is to allow AIM users to create custom views within AIM that later can be retrieved by the InsCipher AIM integration tool.

The advantages of such implementation are:

• AIM users can control what data feeds into InsCipher

• AIM users can identify edge-cases and modify custom view SQL queries accordingly

• InsCipher AIM tool maintenance becomes simpler as no frequent re-installment or tool updates are needed

The Workflow

AIM user creates a custom view that includes filings/transactions and information about those filings/transactions that is needed to be fed to InsCipher. This view would be saved on the same AIM database connected with the InsCipher Integration tool. With this custom view, Transaction Code mapping settings will be ignored in the Connect mapping settings. Meaning, that mapping related to what fees get imported into InsCipher Connect as either Carrier or Broker fees will need to be programmed. In addition, amounts related to SL Taxes, Stamping Fees, or other state-specific fees will also need to be specified on the view.

Step 1) 

As a Connect® user, go to the AIM tool integration wizard > Step 1 (Agency Management System) and check the "Use custom view" setting and then enter a custom view name. 

Step 2)

Ask your AIM Database admin to create and save a custom SQL view on the same AIM database that is connected to the InsCipher Integration Tool. The name of the view would need to be identical to that which is referenced in Step 1.  

Custom SQL View (Starting Point)

Here’s the out-of-the-box custom SQL view that your company can use to get you started, which you can modify as needed:

CREATE VIEW [dbo].[InsCipherCustomView] AS
WITH CTE AS
(SELECT
    ih.InvoiceKey_PK,
        ih.InvoiceID,
        ih.PolicyID,
        ih.QuoteID,
        ih.InvoiceTypeID,
        ih.Premium,
        ih.InvoiceTotal,
        ih.InvoiceDate,
        ih.ProductID,
        ih.DivisionID,
        ih.Entity,
        ih.PayableID,
        ih.PayToCode,
        ih.InsuredID,
        ih.CompanyID,
        ih.PostDate
    FROM InvoiceHeader ih WHERE ((ih.StatusID = 'E'
    AND ih.Taxed = 'Y'
    AND ih.InstallmentFlag = 'N')
    OR(ih.MemoInvoiceFlag = 'Y'
    AND ih.StatusID IN('R', 'P')))
    ) SELECT
    h.InvoiceKey_PK,
    h.InvoiceID,
    h.PolicyID,
    h.QuoteID,
    h.InvoiceTypeID,
    h.Premium,
    h.InvoiceTotal,
    h.InvoiceDate,
    h.ProductID,
    h.Entity AS Entity,
    h.PayableID AS PayableID,
    h.PayToCode AS PayToCode,
    h.PostDate,
    h.DivisionID AS DivisionID,
    p.Effective,
    p.Expiration,
    q.OldPolicyID,
    q.CoverageID,
    i.IsECP,
    q.RiskInformation AS RiskInformation,
    q.NamedInsured AS NamedInsured,
    q.MailAddress1 AS MailAddress1,
    q.MailAddress2 AS MailAddress2,
    q.MailCity AS MailCity,
    q.MailState AS MailState,
    q.MailZip AS MailZip,
    q.Address1 AS Address1,
    q.Address2 AS Address2,
    q.City AS City,
    q.Zip AS Zip,
    q.State AS State,
    q.SLA AS SLA,
    q.TaxState AS TaxState,
    q.FlagTaxExempt AS FlagTaxExempt,
    q.isMultiState AS isMultiState,
    ic.NAIC AS NAIC,
    ti.Limit1 AS Limit1,
    i.NamedInsured AS InsuredNamedInsured,
    i.MailAddress1 AS InsuredMailAddress1,
    i.MailAddress2 AS InsuredMailAddress2,
    i.MailCity AS InsuredMailCity,
    i.MailState AS InsuredMailState,
    i.MailZip AS InsuredMailZip,
    i.Address1 AS InsuredAddress1,
    i.Address2 AS InsuredAddress2,
    i.City AS InsuredCity,
    i.Zip AS InsuredZip,
    i.State AS InsuredState,
    q.Description AS RiskInformationFromDescription,
    d.AgencyFee,
    d.InspectionFee,
    d.StampingFee,
    d.SlTax,
    d.EmpaTax,
    d.FmTax,
    d.MunicipalFee,
    d.SlServiceCharge,
    NULL AS InsuredEntity,
    NULL AS InsuredPhone,
    NULL AS InsuredEmail,
    NULL AS InsuredCounty,
    NULL AS PropertyLimit,
    NULL AS LayeredRisk,
    NULL AS BrokerOfRecord,
    NULL AS RiskRetentionGroup,
    NULL AS ServiceOfProcessName,
    NULL AS ServiceOfProcessAddress,
    NULL AS ServiceOfProcessAddress2,
    NULL AS ServiceOfProcessCity,
    NULL AS ServiceOfProcessState,
    NULL AS ServiceOfProcessZip,
    NULL AS WindStormExclusion,
    0 AS WindStormDeductible,
    NULL AS DecliningCarriers,
    STUFF((SELECT ' | ' + details.CoverageID + ' ^ ' + CONVERT(varchar(255),details.Amount, 0)
              FROM InvoiceDetail AS details WHERE details.InvoiceKey_FK = h.InvoiceKey_PK AND details.LineTypeID = 'P'  FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,3,'') AS Coverage, pr.Name AS RetailProducerName, pr.Name AS RetailAgencyName, pr.Address1 AS RetailAddress, pr.Address2 AS RetailAddress2, pr.City AS RetailCity, pr.Zip AS RetailZip, pr.State AS RetailState, pr.License AS RetailProducerLicense, pr.License AS RetailAgencyLicense, pr.Phone AS RetailPhoneNumber, pr.EMail AS RetailEmailAddress
    
    FROM CTE AS h (NOLOCK)
    JOIN Insured AS i (NOLOCK) ON h.InsuredID = i.InsuredID
    JOIN Quote AS q (NOLOCK) ON h.QuoteID = q.QuoteID
    JOIN POLICY AS p (NOLOCK) ON h.QuoteID = p.QuoteID
    LEFT OUTER JOIN Company AS ic (NOLOCK) ON h.CompanyID = ic.CompanyID
    LEFT OUTER JOIN taaTaxInfo AS ti (NOLOCK) ON h.QuoteID = ti.QuoteID
    LEFT OUTER JOIN Producer AS pr (NOLOCK) ON q.ProducerID = pr.ProducerID AND q.TaxState = 'WA' AND GETDATE() BETWEEN q.Effective AND q.Expiration
    LEFT JOIN (
        SELECT
            id.InvoiceKey_FK,
            SUM(
                CASE WHEN id.TransCd IN('ADF', 'AGCYF', 'ABF', 'CFF', 'JBL', 'MGF', 'MVR', 'OMNI', 'PLF', 'FEE', 'RMF', 'SVF', 'SLF', 'TPF', 'PRODF') THEN
                    id.Amount
                ELSE
                    0
                END) AS AgencyFee,
            SUM(
                CASE WHEN id.TransCd IN('LLF', 'EC', 'INF', 'MAF', 'AUF', 'ATF', 'MBF', 'IN2', 'MMVR', 'CPF', 'MRM', 'WCA', 'WCS', 'UNF') THEN
                    id.Amount
                ELSE
                    0
                END) AS InspectionFee,
            SUM(
                CASE WHEN id.TransCd IN('SOF')
                    OR(hh.State = 'AK' AND id.TransCd = 'AKFF')
                    OR(hh.State = 'KY' AND id.TransCd = 'KFE')
                    OR(hh.State = 'MI' AND id.TransCd = 'REGF')
                    OR(hh.State = 'OR' AND id.TransCd = 'FM')
                    OR(hh.State = 'PR' AND id.TransCd = 'NIMAF')
                    OR(hh.State = 'SD' AND id.TransCd = 'NIMAF')
                    OR(hh.State = 'TN' AND id.TransCd = 'TNTRN')
                    OR(hh.State = 'UT' AND id.TransCd = 'NIMAF')
                    OR(hh.State = 'VI' AND id.TransCd = 'VIFF')
                    OR(hh.State = 'VA' AND id.TransCd = 'VAF')
                    OR(hh.State = 'WY' AND id.TransCd = 'NIMAF') THEN
                    id.Amount
                ELSE
                    0
                END) AS StampingFee,
            SUM(
                CASE WHEN id.TransCd IN('SLT')
                    OR(hh.State = 'MT' AND id.TransCd = 'FP')
                    OR(hh.State = 'NJ' AND id.TransCd = 'FFA') THEN
                    id.Amount
                ELSE
                    0
                END) AS SlTax,
            SUM(
                CASE WHEN id.TransCd IN('EMPA')
                    OR(hh.State = 'FL' AND id.TransCd = 'FEM') THEN
                    id.Amount
                ELSE
                    0
                END) AS EmpaTax,
            SUM(
                CASE WHEN id.TransCd IN('FMT')
                    OR(hh.State = 'AK' AND id.TransCd = 'WMT')
                    OR(hh.State = 'IL' AND id.TransCd = 'ILS')
                    OR(hh.State = 'MT' AND id.TransCd = 'FM')
                    OR(hh.State = 'SC' AND id.TransCd = 'SCMMA')
                    OR(hh.State = 'SD' AND id.TransCd = 'SDFIR') THEN
                    id.Amount
                ELSE
                    0
                END) AS FmTax,
            SUM(
                CASE WHEN id.TransCd IN('MUNI', 'MUNIT') THEN
                    id.Amount
                ELSE
                    0
                END) AS MunicipalFee,
            SUM(
                CASE WHEN id.TransCd IN('SLSC', 'TXA')
                    OR(hh.State = 'MS' AND id.TransCd = 'MWUA') THEN
                    id.Amount
                ELSE
                    0
                END) AS SlServiceCharge
        FROM
            InvoiceDetail AS id
    LEFT JOIN InvoiceHeader AS hh ON id.InvoiceKey_FK = hh.InvoiceKey_PK
GROUP BY
    id.InvoiceKey_FK) AS d ON h.InvoiceKey_PK = d.InvoiceKey_FK

What About Importing Documents?

Currently, InsCipher does not have an integration with ImageRight® or other document management systems. You can attach documents to a transaction in Connect® should you need the document to export filings to a state. We are working on bulk document import options currently that will help this process be more efficient. Stay tuned.

Technical Installation Details

The Integration Client (IC) must be installed on-premises so that it could access the MSSQL machine over the private network. Ideally, the IC could be installed on the machine hosting the MSSQL server itself but could connect to a copy of the live database if the field names are the same, and entry was granted through your firewall.

Once the IC gets installed, the user is then able to configure the hostname and port on which the MSSQL machine is set up. The firewall of the MSSQL machine and the server software must be set up to allow connecting over the network. Also, the user will need to configure the IC to use a user account for MSSQL, which is going to be used to query insurance policy transactions. This means that the user account must have at least read-only access to some of the tables from the database (or copy of the database) used by AIM software.

For the IC to integrate with InsCipher properly, we decided to keep a persistent connection to our Integration Management Servers (IMS). This is achieved by leveraging "socket" technology. For this to work, the IC must be able to connect to IMS over the internet (HTTP on port 80), and then maintain a connection using the in-built socket.io client (the same port 80 is used). This approach lets us have flexibility and control over when the import jobs should be initiated.

Using the MSSQL access credentials that you will be able to set up in the IC, it will regularly query the MSSQL database for new/updated insurance policy transactions. Once the IC is done querying (again, this is done multiple times, indefinitely, as long as the IC is active), it will need to send these results to one of our API endpoints (over HTTP, probably port 80).

Information extracted and imported into InsCipher is limited only to the fields listed in the mapping table above. Or in other words, those fields that are required for Surplus Lines filing and for the InsCipher Connect to function as designed. Not all of the information that can be imported into InsCipher through our API Endpoint will be imported due to some limitations in field mapping between AIM and InsCipher.

Should you have further technical questions, please reach out to support@inscipher.com, and we can put you in touch with a developer that can answer your questions.

VERSION HISTORY