Integration between Microsoft CRM and SAP Business One

 

Alba Spectrum: São Paulo - SP - Brazil +55-11-3444-4949, USA: 1-866-528-0577, Worldwide: 1-630-961-5918,  help@albaspectrum.com http://www.albaspectrum.com http://www.enterlogix.com.br

 

This document contains documentation on the integration: architecture, settings, troubleshooting and redeployment highlights

 

  1. MS CRM Resides on CRMSERVER
  2. SAP Business One can have multiple installations and companies – integration can switch companies and send timlog to the appropriate company
  3. CRMSERVER has CRMCUSTOMIZATION database, which hosts all integration SQL objects:

3.1.    Tables:

·        ALBATIMELOG is where sp_Billing stored procedure sends infor from tasks-closed activities-active contract lines

·        COMPANIES stores info about where each company resides – SQL Server and DB name

·        CustomerPostingCompany stores customer number and company name.  If no record found for the customer, company is assumed ALBAUS – hardcoded

·        BilledCases – sp_Billing stored procedure sends cases to ALBATIMELOG and sends their Incident (Case) records to BilledCases and sets Integrated field as 0 (not-integrated).  Integration takes non-integrated cases from ALBANEWTIMELOG view and if Sales order (with always single line of the case resolution) is successfully created it calls sp_UpdateImportedCase stored procedure to mark case as integrated (Integrated=1 in BilledCases table)

 

3.2.    Views:

·        Timelog – this view has core select statement to show all closed cases from MS CRM.  This view is used by sp_Billing to bring time log into ALBATIMELOG table

·        ALBANEWTIMELOG shows only non-integrated cases from ALBATIMELOG table (checking if case is already integrated in BilledCases table)

3.3.    Stored Procedures

·        sp_Billing – moves closed cases, which are not yet present in BilledCases table – then the second step of this procedure is to send Incident info into BilledCases table, marking them as non-integrated (Integrated=0)

·        sp_UpdateImportedCase – updates case as integrated (Integrated=1) in BilledCases – it gets parameter IncidentId to identify the case for update

 

  1. Integration.

4.1.    Overview.  It is SAP SDK C# application, it gets parameters from Settings.txt file, where first line is SQL Connection string, second is select statement to identify unique companies (company databases) and the third line is select statement to select all the cases for the specific company – please pay attention to = sign at the end of the third line

4.2.    Prerequisites.  Customer Number should be synchronized in MS CRM and Customer record in SAP B1.  Item numbers in SAP B1 should be synchronized with Subject Descriptions in MS CRM.  Login credentials should be the same in each SAP B1 company – please verify in the first line of Settings.txt file

4.3.    Logic.  Integration has first (external) loop which comes through separate companies.  Second (internal) loop picks open cases for the specific company and insert them in the form of single Sales Order line – it creates one Sales Order per case.  Item number is set to Subject Description in MS CRM – ItemNumber field in ALBATIMELOG table.  Item Description comes from MS CRM case resolution statement.  If Sales Order is successfully added – integration marks the case as integrated (BilledCases table)

4.4.    Troubleshooting.  If the case is not integrated – you can identify this in BilledCases table after integration – look at the cases with Integrated=0 prior and after integration – if new ones found – you should investigate.  More likely the reasons would be – you didn’t not synchronize Item Number in SAP B1 with Descriptions in MS CRM Subjects.  Also check if Customer Number in SAP B1 is the same as Account Number in MS CRM for the customer (also – you should insert new customers in both systems manually – MS CRM and SAP B1 – in CRM customer may show up as the result of Lead promotion)

 

Customer Code in SAP B1

 

 

Should be the same as MS CRM Account Number

 

Microsoft CRM Subject Description

Should be equal to SAP B1 Item Number

 

You should include new company into COMPANIES table:

 

Here SQL Server in most cases is the same as computer name (if you use default instance of SQL server to host SAP B1)

 

5.  Redeployment.  You should backup and restore CRMCUSTOMIZATION database (do not create it from scratch – MS CRM changes default collation – MSCRMDATABASE objects have altered collation).  In order to deploy integration on different SQL Server – change settings in Settings.txt file: crm server.  In CRMCUSTOMIZATION table you should alter the following objects: timelog view – to modify your CRM database name.  You should copy C:\_IntegrationSAP\Integration folder from CRMSERVER to new server.  We recommend you to keep the same path.

 

We recommend you to deploy integration as MS SQL Server Job – first step is sp_Billing stored procedure and the second step is C:\_IntegrationSAP\Integration\bin\Debug\Integration.exe

 

6.  Modifications.  If you need to customize application logic – analyze SQL objects first.  If you need to alter number of columns or change status fields values – you need to modify Integration source code in MS Visual Studio C# console application project

 

Alba Spectrum: São Paulo - SP - Brazil +55-11-3444-4949, USA: 1-866-528-0577, Worldwide: 1-630-961-5918,  help@albaspectrum.com http://www.albaspectrum.com http://www.enterlogix.com.br