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™?

Connect™ by InsCipher is an intuitive management software combining the best of technology and compliance to ensure your surplus lines filings are completed faster, on time, and with more accuracy.

Traditional methods of surplus lines tax filing are inefficient and prone to error. Connect™, powered by InsCipher was specifically created for companies with large volumes of surplus lines policies spread across multiple states. InsCipher's Connect™ system is designed with a full suite of automation tools to help companies file faster, on time, and with fewer errors. Backed by a team of compliance experts and software developers, InsCipher saves agencies thousands of dollars annually by minimizing overhead expenses, reducing penalties and fees, and avoiding state compliance issues. Vertafore and InsCipher have partnered to bring the power of Connect™ to  AIM™ users, offering yet another enhancement to the growing list of AIM™ Partner Integrations.

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.

IMPORTANT: Be aware that the InsCipher Integration Tool is always working in the background, regardless if you reboot your server. All batches imported will be included in the “Filings Import Log” page in InsCipher Connect™ for your review.

TIP: It is best practice with plans that are billed in installments to charge the entire SL taxes and fees upfront so that these can be reported and paid to the state. Should a policy eventually cancel, you would want to create an offsetting invoice so that you can report the unrealized premium to the state and return the returned SL Taxes and Fees back to the insured.

NOTE: Duplicate transactions with the same Invoice ID number will not be imported. These will show up in the Filings Import log, but as “Not Imported” 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.

IMPORTANT: You will have 10 minutes to use this token before you will need to regenerate!

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.

 

NOTE: Once the connection is complete, you will get an "installation successful” message. The application will continue to run in the background and will automatically startup with each reboot. To cancel, disconnect, or adjust settings to your connection, click on the application icon in Windows' tray menu.

 

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". 

TIP: Make sure that if you do not report the fee or amount to the state, that you select "Do Not Import Fee As Broker or Carrier Fee".

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.

TIP: Use the "Mapped?" filter to view only those LOBs that need to be mapped.

IMPORTANT: Select "All States" when configuring this feature. InsCipher is working on adding functionality to map to state-specific codes, but this functionality is not yet released fully.

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.

Note: To prevent timeout errors, batches are grouped into 100 transactions. If you are importing more than 100 transactions at a time, these will come in as multiple batches on the "Filings Import Log".

IMPORTANT: Please plan to regularly review the "Filings Import Log" to ensure all transactions are imported each day and address any import errors. Import Errors are described on the "Not Imported Filings" tab or by clicking on a particular batch "Error Log". Duplicate records are filtered out by the Invoice ID number.

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. 

Note: We are working on a feature that will be released shortly which will allow you to download this list to a .csv file so that you can review import errors for multiple batches at once.

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:

InsCipher Field Name

InsCipher Field Desc.

Mapped AIM Field

Additional Notes

id A unique ID used for tracking imported transactions InvoiceHeader.InvoiceID  
policy_number Policy number Non-unique field InvoiceHeader.PolicyID  
policy_effective_date Policy effective date Policy.Effective  
policy_expiration_date Policy expiration date Policy.Expiration  
transaction_effective_date Transaction effective date Policy.Effective  
expiring_policy_number Expiring Policy Number Quote.OldPolicyID  
invoice_date Invoice date or state-specific renamed date InvoiceHeader.InvoiceDate  
invoice_number The "Invoice ID" field on the Tracking tab in the filing details page of a Filing Agent User InvoiceHeader.InvoiceID  
transaction_type Use Filing Type Mapping Feature InvoiceHeader.InvoiceTypeID Custom - Setup in Integration settings
account_written_as Is the business written directly to the Insured or through a retail agent? B Hard coded value for now. Looking for ways to bring in "DC" policies, which would be those written directly to the customer.
rpg Is the transaction imported as part of a Risk Purchasing Group? 0 (No) Currently, this is hard code to not bring in RPG data
purchasing_group_name Required if RPG (rpg field) is set to Yes (or 1)   Currrently, this is hard coded Not mapped
risk_description Description of Risk Uses Quote.RiskInformation if not NULL otherwise, pulls from Quote.Description For states where this information needs to come from a specific list (NY, OH, and PA), these states will require a user to add the Description of Risk into the InsCipher Portal after the transaction is imported into Connect™. It is strongly recommended that you populate the risk information on the quote table in AIM for California, as this value can be used to generate electronic SL1 forms.
ecp Exempt Commercial Purchaser? Insured.IsECP If unknown, this will default to "No".
exempt Is the coverage considered tax-exempt by the state? Quote.FlagTaxExempt Hard coded value
multi_state Does the insured’s liability reside in multiple states? Quote.Multi-State Hard coded value
 policy_limit This is the aggregate policy limit/liability amount associated with the policy taaTaxInfo.Limit1 Only pulls if Policy Limit 1 is turned on for a state and then a user adds this information to a transaction.
export_list Is the line of business on the state’s Export List? 0 (No) Hard coded value
transaction_line_of_business Line of business or coverage associated with the policy Quote.CoverageID Defining the mapping for the coverage code that gets imported can be accomplished during the setup process > step 2: field mapping
OR for multiple lines of businesses:      
    transaction_line_of_business_list Line of business list InvoiceDetail.CoverageID  Taken only Details.LineTypeID = P
    transaction_line_of_business_coverage Lines of business breakdown by Premium InvoiceDetail.Amount Taken only Details.LineTypeID = P
non_admitted_insurer_code This is the NAIC code associated with the carrier For Lloyd's policies, use “AA-1122000” Company.NAIC Note: If the NAIC values in AIM are not populated in your company records, insurance companies will not import in Connect™. Take time during the setup to ensure that your company tables are populated.
OR for multiple insurance companies use:      
 non_admitted_insurer_code_list Multi-insurance company list   Not mapped
non_admitted_insurer_code_coverage Multi-insurance company breakdown percentages   Not mapped
 syndicate_list Syndicate list breakdown Pulled using a combination of the taaPremiumAllocation, taaCompanyContact, taaaContractNumber, and SyndicateCompany tables. Pulls from an aggregate weighted average of the contract # / property % breakdown for all policies that have a contract # that has Syndicate Company (Type S) connected to it.
 syndicate_list_coverage Syndicate list breakdown percentages Pulled using a combination of the taaPremiumAllocation, taaCompanyContact, taaaContractNumber, and SyndicateCompany tables. Pulls from an aggregate weighted average of the contract # / property % breakdown for all policies that have a contract # that has Syndicate Company (Type S) connected to it.
premium Policy premium (does not include any fees) InvoiceHeader.Premium  
agency_fee Policy / Broker / Agency Fee Fees are mapped using the integration mapping feature.  Transaction Codes within AIM that are charged and collected by the Agency or Brokerage should be mapped to "Broker Fee" in the mapping tool.
inspection_fee Inspection / Audit / Underwriting Fee Fees are mapped using the integration mapping feature.  Transaction Codes within AIM that are charged and collected by the Carrier or Insurance Company should be mapped to "Carrier" in the mapping tool.
sl_tax Surplus Lines Tax (name may vary slightly by state) InvoiceDetail.Amount where the InvoiceDetail.TransCd = SLT InvoiceDetail.TransCd = SLT
stamping_fee Stamping Fee (name may vary such as FSLO fee in Florida or Fire Marshal Tax in Oregon) InvoiceDetail.Amount where the InvoiceDetail.TransCd = SOF  
sl_service_charge Currently active only in a handful of states like Oregon and Mississippi InvoiceDetail.Amount where the InvoiceDetail.TransCd = SLSC or TXA  
municipal_fee Currently active only in Kentucky InvoiceDetail.Amount where the InvoiceDetail.TransCd = MUNI or MUNIT  
fm_tax Fire Marshal Tax based on Line of Business InvoiceDetail.Amount where the InvoiceDetail.TransCd = FMT  
empa_tax Active in the state of Florida and is based on Line of Business InvoiceDetail.Amount where the InvoiceDetail.TransCd = EMPA  
total total amount = premium + all taxes + all policy fees InvoiceHeader.InvoiceTotal  
 commission_received Is commission received? Currently only applies to NH 1 (Yes) Hard coded value. If you don't collect or receive commission in NH, talk with your implementation consultant about how to best address updating these records.
mailing_insured_name Insured name as it appears in policy documents Quote.NamedInsured Mailing address will pull from the Insured Table in AIM if it doesn't exist on the Quote table.
mailing_address Mailing address Quote.MailAddress1 Mailing address will pull from the Insured Table in AIM if it doesn't exist on the Quote table.
mailing_address2 Mailing address line 2 (if applicable) Quote.MailAddress2 Mailing address will pull from the Insured Table in AIM if it doesn't exist on the Quote table.
mailing_city Mailing city Quote.MailCity Mailing address will pull from the Insured Table in AIM if it doesn't exist on the Quote table.
mailing_zip_code Mailing zip code Quote.MailZip Mailing address will pull from the Insured Table in AIM if it doesn't exist on the Quote table.
mailing_state_code Mailing state 2 letter state code Quote.MailState Mailing address will pull from the Insured Table in AIM if it doesn't exist on the Quote table.
physical_same_as_mailing Is the Physical the same as the mailing address? Yes if mailing_address = physical_address Predefined logic determine by if the addresses are the same or not
physical_address Physical address Quote.Address1  
physical_address2 Physical address line 2 Quote.Address2  
physical_city Physical city Quote.City  
physical_zip_code Physical zip code Quote.Zip  
physical_state_code Physical state 2 letter state code Quote.State or Quote.TaxState depending on your setup To change which state is used, go to Setup > Integrations > Details > Step 2 and toggle this setting.

retail_producer_name

Retail Producer Name

tnm.Name Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.

retail_agency_name

Retail Agency Name

p.Name Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.

retail_address

Retail Producer’s Address Line 1

p.Address1 Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.

retail_address2

Retail Producer’s Address Line 2

p.Address2 Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.

retail_city

Retail Producer City Location

p.City Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.

retail_state

Retail Producer State Location

p.State Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.

retail_zip

Retail Producer Zip Code p.Zip Only applies if account is written directly to the insured ("DC"). Otherwise, retail producer is defaulted to the broker for the state/agency in InsCipher.
agent_notes Populates the Agent Notes section on the Filing Details page of a Filing Agent/Filing Agency Admin user Null

Hard coded value as blank

transaction_code For NJ, a unique transaction code is needed in order to file with the state. Quote.SLA

For NJ only

customer_code Value to indicate unique customer code or ID found on the tracking tab. It is a reportable and filterable field. Quote.ID

The unique quote or submission ID from AIM

unique_id Unique ID on the tracking tab for certain states like MO, IL, NY, etc. that have their own unique transaction numbers. Quote.SLA 

For other states besides NJ

windstorm_exlcusion Flag on whether or not windstorm exclusion applies. Only for TX, and FL imports Not imported, unless using custom view

Not imported, unless using custom view

windstom_deductable Amount flagged as windstorm deductible. Only for FL imports. Not imported, unless using custom view

Not imported, unless using custom view

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

IMPORTANT CONSIDERATION: Only use this Custom View feature if necessary. Otherwise, our suggestion would be to utilize the standard SQL query and transaction code mapping features built within the AIM Integration setup wizard in Connect™ and NOT turn this feature on.

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.

TIP: InsCipher has default field names for tax titles, but these can vary slightly by state. Should these vary, these tax titles are described on this mapping sheet, for your reference.

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. We do have a bulk drag-and-drop feature to assist you in the import of relevant documents. Or you can attach documents to a transaction in Connect™ should you need the document to export filings to a state.

The bulk drag-and-drop feature can be accessed by clicking on the “Filings Import Log” link in the left navigation bar > finding the relevant batch > clicking on the box next to the batch ID number > and then clicking the button at the bottom to “Upload Documents.” This will take you to a window where you can bulk drag-and-drop relevant documents into InsCipher for one or more policy transactions in the batch.

TIP: Many of our clients that use AIM are choosing, for now, to only add documents to InsCipher for the "State Export states" that require it. "State Export states", are those states where InsCipher has created the ability to batch file in that state (either programmatically or through an import file). In order to improve the workflow and reduce duplicate entry, this would mean that we could make most of your filing documents as as "Optional" in the Connect™ portal, meaning that they wouldn't need to be stored in InsCipher. If you went with this workflow, then you would only need to import documents for the following states where documents are required for the batch filing process:
  • California
  • Mississippi (coming in Q2 2021)
  • New Jersey (coming in Q2 2021)
  • New York
  • Oregon
  • Pennsylvania
  • Washington
We are working on creating batch export files for NJ, and MS so the document requirements may need to be updated once we release this feature for these states.
In all the other states, you can singularly store the policy documentation in ImageRight.

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

DATE DESCRIPTION OF CHANGE
9/16/2022 Added a number of new fields to the custom view SQL sample
12/6/2021 Added section for Filing Type Mapping and fixed small grammatical errors
10/19/2021 Add Custom View Section and Table of Contents Plus Formatting Changes
8/3/2021 Added Retail Producer Information
7/28/2021 Added Version History