Integrating Microsoft Great Plains Accounting/ERP:

Integrating Microsoft Great Plains Accounting/ERP: RMS, CRM, eCommerce, Lotus Domino – overview for developer

by: Andrew Karasev

Microsoft Business Solutions Great Plains has substantial market share among horizontal and vertical clientele in the USA, Canada (including French version for Quebec/Montreal), UK, Australia, New Zealand, Spanish speaking Latin and Central America, South Africa and Middle East. Nowadays ERP can not stay as it is offtheshelf product – it requires integration with Legacy or newly implemented systems, such as CRM, Retail Management applications, custom inhouse made business systems (transportation/cargo tracking, etc). The tendency is that if company uses Microsoftdriven computer park (Windows domain, SQL Servers, MS Exchange) the rest of the ERP/CRM applications are Windowsoriented. However you can have successful bridge between nonMicrosoft ERP and Great Plains: Oracle, IBM Lotus Notes/Domino, DB2 or others), usually it involves Java/CORBA/EJB/JSP type of expertise.

Microsoft RMSGreat Plains integration. There are few options – first one is to use standard integration, coming with MS RMS. It has Dexterity interface in Great Plains, does integration to single company at the time. Doesn’t integration on SOP/AR level, however. Second option is to use integration, maintained by Alba Spectrum Technologies – it integrates unlimited number of stores, SOP/AR/POP, posts through Bank Reconciliation module and so allows to track Check Books/Credit Card balances. This integration also has Dexterity interface for StoresGP Companies mapping and could work as nightly or real time SQL Server routine

Microsoft CRMGreat Plains integration. This integration is maintained by Microsoft Business Solutions and allows you to synchronize SOP and Customers in Great Plains with Microsoft CRM Quotes and Orders. Microsoft CRM has open Microsoft CRM SDK and so you can customize and integrate Microsoft CRM at will, having C# or VB.Net inhouse expertise. The above mentioned integration could be altered and tuned by programming MS BizTalk server (it can use either BizTalk runtime or regular license)

Microsoft Great Plains as Back office for eCommerce website. Here we need to mention interesting thing, The product, created and dedicated to eCommerce developers – Great Plains eConnect is often considered as a bit expensive and licensingrestricting solution. If your company is ISV and implements eCommerce product, integrating to Great Plains – you would rather prefer set of open and simple stored procedures, moving data to and retrieving from Microsoft Great Plains Sales Order Processing (SOP) module

Microsoft Great Plains integration with Oracle. Well – you can do it either from Oracle or from MS SQL Server side – in both cases you have linking tools and heterogeneous queries mechanisms

Microsoft Great Plains integration with Lotus Notes/Domino. – If you want Great Plains documents lookup from Lotus – you typically use ODBC connection to MS SQL Server. You need to know Great Plains tables structure (Tools>Resource Description>Tables in Great Plains)

Good luck in upgrading and if you have issues or concerns – we are here to help! If you want us to do the job give us a call 18665280577, 16309615918! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM customization company, serving clients in Chicago, California, Texas, Florida, New York, Georgia, Arizona, Minnesota, UK, Australia and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is CMA, Great Plains Certified Master, Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer. You can contact Andrew: [email protected].

[email protected]

This article was posted on April 17

by Andrew Karasev

Microsoft Great Plains Integration Manager – worki

Microsoft Great Plains Integration Manager – working with text file

by: Andrew Karasev

Great Plains Integration Manager advanced programming, working with text files, aggregation, unionizing, VB scripting, translation

Microsoft Business Solutions main middle market ERP application Microsoft Great Plains has multiple integration options: MS SQL Scripting (stored procedures and views), ADO.Net programming, Microsoft SQL Server DTS packages. You certainly can deploy such SDK tools as eConnect. However here we would like to show you how to program the simplest user friendly tool: Microsoft Great Plains Integration Manager.

Multiple times in our consulting practice we saw the need to integrate General Ledger transactions from one text file and here we give you this and even more complex case, when credit and debit amounts are present on the same line with their own account numbers. Let’s assume that we have tab delimited text file, GLSOURCE.txt. Here is how the line looks:

ก11242004ก (date) ก11020016000ก (debit account) 212446.68 (debit amount) ก15260005400ก (credit account) 212446.68 (credit amount)

Pretty challenging, isn’t it?

Let’s begin

First of all and this is probably easy – you need ODBC DSN, use Microsoft Text Driver, change default directory, select the file and switch to tab delimited type.

Next, open Microsoft Great Plains Integration Manager and create two new queries – one will be for the Header and the second for distribution lines in GL transaction. In both cases use Advanced ODBC type of text queries.

GL Header should aggregate by date, so in its SQL window enter this:

select F1 from GLSOURCE.txt group by F1

Now the lines – here we need unionizing and checking if amount is not zero:

select F1, F2, F3, 0 from GLSOURCE.txt

where F3กก

union

select F1 F4, 0,F5 from GLSOURCE.txt

where F5กก

The one above is the most difficult part of this article. Please understand it – we are splitting line into two by union, first we create debit part and then we attach credit part, plus we are weeding out 0 amounts.

Next – we need to link two queries – simply link them by F1 field (date). Do it in query relationship. When you are done with linking, right click on the arrow, open properties and in Select Relationship Type window change to:

There can be 0 or more records in the child for each record in the master.

This is important – we’ll have more than one line in GL distribution.

The rest should be familiar for IM consultant.

Select destination as GL Journal, assign transaction date and batch ID as F1 from Header query, switch to entries and select them from debit and credit parts of the lines query.

As additional tools in filtering your text query you could use VBA scripting and even translation.

Translation is the way to go when you need to replace account numbers from the text file with different or translated accounts in Great Plains.

Happy integrating! if you want us to do the job give us a call 16309615918 or 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM customization company, serving Chicago, California, Colorado, Arizona, New York, Texas, Florida, Georgia, Canada, UK, Europe and Australia and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on December 15, 2004

by Andrew Karasev

eCommerce development for Microsoft Great Plains:

eCommerce development for Microsoft Great Plains: tools and highlights for programmer

by: Andrew Karasev

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics and eEnterprise was designed in earlier 1990th as ERP, which can be easily transferable to the winning Database and OS platform and it was originally available on Mac and PC – Mac OS and Microsoft Windows respectively. Graphical platforms battle is pretty much over and now with eCommerce demands, we should look at Great Plains Dynamics tables structure:

Naming Convention. Great Plains was designed to be ready to move to the winning database platform and probably this is why we see this a bit complicated naming convention in place: SOP10100, RM00101, IV00101 – these are samples: SOP header, Customer master, Inventory master. First – we see module prefix: SOP – Sales Order Processing, RM – Receivables management, IV – Inventory control, etc., then 0 stays for the master files, 1 – socalled work files (before transaction being posted), 2 – open files (after transaction is posted) and 3 – historical files (when you close the year in General Ledger – transactions are moved from open to history files). So as you see – logic is present and structured, but it is not friendly to the developer, who never seen and worked with Great Plains Dynamics.

Tables Groups. Great plains was designed to first serve midsize businesses and then with the availability of third party modules – the intention was to compete on corporate ERP market with Oracle, PeopleSoft, SAP and others big players. This is why we see the whole cluster of tables to store, say Sales Invoice: SOP Header, SOP Lines, SOP Distribution, RM Key file, etc.

From the Forms side (or screens) you can see more humanreadable names: SOP Entry, RM_Customer_Maintenance, POP_Entry or the like. But these legacy Great Plains Dexterity names do not help eCommerce developer – only probably as the reference on which table works with specific screen.

Let’s take a look at the tools available:

eCommerce – together with eOrder, and other eXXX products it should be considered as legacy and phase out product, based on Microsoft eCommerce server and ASP technology, today Microsoft has new paradigm .Net and ASPX World

eConnect – was specially created for eCommerce developers, who integrate Microsoft Great Plains with eCommerce web interface. This tool covers a lot of Great Plains objects creation and retrieving functionality, however it does have restrictions, because it was not intended as replacement to Great Plains Dexterity shell. For example – if you create Orders in SOP via Web interface/eConnect – it is difficult manipulate these orders (transfers to Invoices, backorders, reallocations, etc.). Another issue with eConnect – developers are kind of used to the fact that Microsoft provides free SDK to its products, Microsoft CRM for example has freely downloadable Microsoft CRM SDK. For eConnect you have to pay license and be on Microsoft Business Solutions annual support to get version upgrades. Also if you are ISV and develop your GP integration to your customers – you have licensing issue with Microsoft.

Custom SOP/AR stored procedures. Microsoft Business Solutions partners in their practice usually have several Great Plains integration projects implemented where integration is realized on the stored procedures level and transactions are created and manipulated in Great Plains SOP. So – you may end up seeking this type of help

Good luck with implementation, customization and integration and if you have issues or concerns – we are here to help! If you want us to do the job give us a call 8665280577 or 6309615918! [email protected]

About The Author

Andrew is Great Plains specialist in Alba Spectrum Technologies ( http://www.albaspectrum.com ) – Microsoft Great Plains, Navision, Microsoft CRM Partner, serving clients in San Francisco, San Diego, Chicago, New York, Atlanta, Houston, Dallas, Miami, Orlando, New Orleans, Phoenix, Seattle, Minneapolis, Detroit, Los Angeles

[email protected]

This article was posted on April 17

by Andrew Karasev

Microsoft RMS – Great Plains Integration – overvie

Microsoft RMS – Great Plains Integration – overview for IT specialist

by: Andrew Karasev

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS) are getting new market share due to the Microsoft strong marketing campaign. Both products: Microsoft Great Plains and Microsoft RMS are coming from the same Microsoft subdivision Microsoft Business Solutions (MBS), however due to the fact that both products were bought by Microsoft from different vendors, the integration between the two is not an easy thing. At this time MBS has RMS integration on the General Ledger and Purchase Order level into Great Plains out of the box. This integration has some advancements in comparison to old product: QuickSell, but it is still GL and PO only. We do understand the need for midsize and large retail companies, structured as clubs and selling on account to their members to have more adequate integration when you can synchronize your Sales information and have robust Great Plains reporting.
There is the product on the market, which is integration on the Receivables Management level from RMS to Great Plains, written by Daniel Sionov and Andrew Karasev and maintained by the alliance between LightEdge Solutions (www.lightedge.com) and Alba Spectrum Technologies (www.albaspectrum.com). In Alba Spectrum Technologies we actually do coding and product tuning for specific client needs.
If you are developer you can end up with your own custom solution, we would like to give you directions.
1. Great Plains Integration Manager if the sales volume is very low, say 100 transactions per day then you can do data export from RMS and import it into Great Plains via Integration Manager. This is rather enduser tool it is very intuitive, it validates 100% of business logic, brings in/updates master records (accounts, employees, customers, vendors. etc.) brings in transactions into work tables. The limitation of Integration Manager it does use GP windows behind the scenes without showing them so it is relatively slow you can bring 100 records but when you are talking about thousands it is not a good option. By the way you can program Integration Manager with VBA.
2. eConnect – You can create VB.Net application which will be pulling info from RMS and then uses eConnect to move it to Great Plains. eConnect is kind of Software Development Kit with samples in VB.Net. Obviously the development environment should be Visual Studio.Net. eConnect will allow you to integrate master records such as new customers, vendors, employees, etc., plus you can bring transactions into so called Great Plains work tables (eConnect doesn’t allow you to bring open or historical records you need to post work records in Great Plains, the same limitation applies to Integration Manager above) eConnect is rather for ongoing integration. It was initially created for eCommerce application integration to Great Plains.
3. SQL Stored Procedures. The product weกve mentioned above is collection of stored procs. Obviously you have unlimited control and possibilities with SQL queries. You need to know Retail Management System Headquarters and Great Plains tables structure and data flow. Launch Great Plains and go to Tools>Resource Description>Tables. Find the table in the proper series. If you are looking for the customers – it should be RM00101 – customer master file. If you need historical Sales Order Processing documents – they are in SOP30200 – Sales History Header file, etc. Do not change existing tables do not create new fields, etc. Also you need to realize that each GP table has DEX_ROW_ID identity column. Sometimes it is good idea to use inbound/outbound XML in the parameters then you can deploy web service as a middle party between two systems. RMS tables structure is self explanatory.
4. Data Transformation Services (DTS) – Good tool for importing your third party data into staging tables in GP then you can pull them in using either stored procs of Integration Manager. You can also deploy this tool for EDI export/import.
5. Great Plains Dexterity Custom Screens. You can create the window, which will have integration settings in it RMS store ID matching GP Company database, etc. Sometimes users prefer to have seamlessly integrated into GP interface custom screens for parameters settings and initiating integration. Dexterity is a good option, however remember it is always better to create new custom screen versus customizing existing one due to the future upgrade issues. Also Dexterity is in phasing our by Microsoft Business Solutions.
6. Modifier/VBA custom buttons on the existing screens alternative to Dexterity is you are comfortable with VBA and ADO.
Happy integrating! if you want us to do the job or use our product give us a call 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM, Microsoft RMS customization company, based in Chicago, Arizona, California, Taxes, New York, Florida and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on July 29, 2004

by Andrew Karasev

Microsoft CRM for Large Corporation – Security

Microsoft CRM for Large Corporation – Security

by: Andrew Karasev

Microsoft Business Solutions CRM proved to be reliable solution in the whole spectrum of industries and market niches: transportation & logistics, education, recruiting & placement, supply chain management, pharmaceutical, oil & gas, aerospace & defense, manufacturing, wholesale & retail. When corporation is looking into CRM application to choose from – one of the fist questions is security. In this small article we’ll try to give you the highlights on the most typical questions we are receiving in the security area.

Active Directory. Microsoft CRM uses Active Directory users and its security is built upon Windows domain security. We heard complains about this tight integration. If you compare MS CRM with Siebel, for example – you will see, that Siebel security is built on the CRM level and Siebel can be installed on Windows and Unix/Linux platforms

Vertical Security, Roles. If you are thinking about vertical security, say assign more rights to manager, versus salesperson, this should be done on the role level. We first talk about vertical security, to give you conception of the matrixtype of security, realized in MS CRM

Horizontal Security, Teams. At this moment (MS CRM 1.2, MS CRM 3.0 is due in September 2005) team can include users only, not other teams, however, if you share the object with the team – you can share it with multiple teams. This is the place where collective security begins. Imagine, you have Worldwide product line – widgets and you have several regions: Europe, Pacific, USA, Australia & New Zealand, China. In gadgets team you have product manager, who should see all the sales worldwide, also you create regional teams for European, America, Australian etc markets and then make gadgets manager as team member of all these teams

Secured Reporting. When you, as European market manager run report on the gadgets sales – you see only records in your security realm. This statement is true even if you have customization, done in MS CRM SDK: C# or VB.Net.

Presentation Level. If you use MS CRM Web or Outlook clients – these are just two types of presentation level clients. Microsoft CRM schema sits in MS SQL server database, so if you deploy MS CRM SDK to modify/customize your CRM – both presentation clients will get the custom interface

Messaging. Microsoft CRM deploys MS Exchange. However you can improve MS CRM Exchange connector to catch emails by addresser email or domain, also you can deploy Lotus Notes Domino as email server – check with Alba Spectrum Technologies

Integration with MS Great Plains. Microsoft CRM integrates with GP customers, SOP Orders & Invoices. You can have custom integration, by calling Great Plains objects (customer annual sales info, employee’s payroll summary, etc.)

Good luck and you can always seek our help in customization, implementation, integration and support. Call us: 18665280577 or 16309615918, [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM customization company, serving Chicago, California, Arizona, Texas, Florida, Georgia, New York, Australia, UK, Canada, Continental Europe, Russia and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer

[email protected]

This article was posted on August 04

by Andrew Karasev

Microsoft CRM Customization: Integration with thir

Microsoft CRM Customization: Integration with third party SQL Application/Database

by: Andrew Karasev

Microsoft CRM – Client Relationship Management package from Microsoft Business Solutions was initially designed to be customizable with Microsoft Visual Studio.Net and one of its programming languages – C#.Net or VB.Net. You can use ADO.Net, Web Service, Transact SQL scripting and stored procedures, deploy such SQL Server tools as Linked Server to all ODBC/OLEDB compliant database, including ORACLE, Sybase, Ingress, DB2, Unidata, Pervasive SQL, Ctree and even Lotus Notes/Domino. In this small article we would like to give you the clue on programming the integration with SQL third party Database.

First – use Microsoft CRM SDK to initiate communication with Microsoft CRM, we have it in C#:

String[] arr1 = coll.AllKeys;

int loop1, loop2;

for (loop1 = 0; loop1 0?1:arr1.Length); loop1++)

{

String[] arr2 = coll.GetValues(arr1[loop1]);

for (loop2 = 0; loop2

Then you use ADO.Net for calling stored procedure with parameters to do the integration job:

try

{

string SQLStatement=กICS_UpdateAccountPrivate กก+

strAccountId +กก , กก + this.TextBoxWorkPerformed.Text +

กก , ก+doubleEncode(System.Double.Parse(this.TextBoxAnnualRevenue.Text))+ก , ก+

intEncode(System.Int32.Parse(this.TextBoxNumberOfEmployees.Text.Replace(ก,ก,กก)))+ก , ก+

doubleEncode(System.Double.Parse(this.TextBoxAverageGrowthRate.Text))+ก , ก+

กกก+this.DropDownListOwnership.SelectedItem.Text +กก , ก+

intEncode(System.Int32.Parse(this.RadioButtonList.SelectedItem.Value))+ก , ก+

intEncode(System.Int32.Parse(this.TextBoxCredit.Text.Replace(ก,ก,กก)))+ก , กก+

this.TextBoxComments.Text+กกก;

System.Data.SqlClient.SqlConnection tmpConnection =

new System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings[กConnectionStringICSก]

);

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM customization company, based in Chicago, California, Texas, Florida, New York, Georgia, Colorado, Oregon, Washington, Canada, UK, Australia and having locations in multiple states and internationally (www.albaspectrum.com), he is CMA, Great Plains Certified Master, Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer. You can contact Andrew: 18665280577 [email protected].

[email protected]

This article was posted on October 18, 2004

by Andrew Karasev

Implementing Microsoft CRM: setup and configuratio

Implementing Microsoft CRM: setup and configuration – notes for IT specialist

by: Andrew Karasev

Microsoft Business Solutions CRM is webbased CRM application, deploying all the spectrum of recent Microsoft technologies. We’ll try to analyze different needs and implementation scenarios attributed to specific industries and business types, based on our experience.

First we would like to emphasize the increasing role of inhouse IT department in the strategic decision making. Looking back to the history – it is probably fair to say that initial CRM applications market issues, such as low successful implementations percentage could be attributed to traditional Sales style of those years – selling to the top, when charismatic salesperson was trying to jump over the head of IT people and press business owner or top executive guy. Microsoft in our opinion never used this technique and it’s CRM implementation is not possible (except when you host it in hosting centers) without everyday IT department involvement. MS CRM works in concert with MS Exchange, Windows Active Directory, SQL Server, BizTalk (integration with Great Plains and in the close future Navision). Utilizing this broad spectrum of Microsoft servers and technologies leads to easy Microsoft CRM acceptance and adoption by IT department (usually the one, having several MS certified professionals and DB administrators). Microsoft oriented IT group easily understands Microsoft CRM setup, tuning, customization, integration and initial data import.

It is not a secret that rational way to implement new computer business system lays in the method of evolution, suggesting intelligent integration with existing legacy systems and stepbystep old pieces replacement. Microsoft CRM fits to this approach – it has open to anyone MS CRM SDK and experienced C# or VB.Net programmer should be capable to program the integration or interface to other system.

We are giving you typical setup and integration scenarios, attributed to specific industries below

Freight Forwarding / Transportation. Companies in this industry usually already have efficient cargo delivering tracking system. MS CRM links several systems into one and plays the role of the initial order entry and customer order status requests with the interface into above mentioned cargo tracking application. You should program such an interface with MS CRM SDK and ADO.Net programming

Distribution and Wholesale. Multiple companies have legacy ASP or JSP web sites with new or existing customers order entry logic. Typical need is transferring these orders to Microsoft CRM in the form of activity, placed in the order taker on duty queue. You can solve this with multiple techniques. Consider HTTP handler, called from ASP/JSP legacy application, reading MS CRM user credentials from INI file and thus penetrating into MS CRM, where you in turn use MS CRM SDK constructions.

Pension Funds and Financial institutions. Considering conservative decision making – there is often the requirement of clear and complete audit to verify past decision making. MS CRM plays this role by automatic logging all the documentation and especially all the incoming and outgoing emails to/from clients. You need MS CRM Exchange connector customization

Large Enterprises. You can frequently see different kinds of corporate policies, requiring using such applications as Oracle eBusiness Suite, or Lotus Domino document workflow and email server. In the first case you need heterogeneous database integration with MS CRM, he second case can be resolved with combining MS CRM SDK and Java Agents programming (from the side of Lotus Notes/Domino).

Happy implementing! We are always here: 18665280577, 16309615918, [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ( http://www.albaspectrum.com )– USA nationwide Microsoft CRM, Microsoft Great Plains customization company, serving clients in Illinois, California, Colorado, Texas, New York, Arizona, Georgia, Florida, Canada, UK, Australia and having locations in multiple states and internationally

[email protected]

This article was posted on March 26

by Andrew Karasev

Microsoft Great Plains Integrations – Retail Manag

Microsoft Great Plains Integrations – Retail Management sample

by: Andrew Karasev

Microsoft Business Solutions is emerging as very attractive vendor for midsize companies. The strength of its products is in their cross integration potential. This integration strategy will be the software development direction for this Microsoft subdivision. Meanwhile, as of right now – we have to recommend just to stake on it and deal with custom or inhouse developed integration.

If you are developer who needs some sample code to start with – we are placing Accounts Receivable level integration code – it pulls info from Microsoft RMS and places it into work tables in Great Plains.

Be aware, that Microsoft Great Plains version 7.5 has out of the box integration with Microsoft RMS 1.2, but it is on GL and POP level only. You can’t do check book reconciliation, for example. Currently Microsoft is in decision on who will be coding the integration for Great Plains 8.0 and what the functionality should be. More likely – it will be Nodus Technologies with the integration on SOP and GL level – it will deploy Great Plains Dexterity and MS SQL Server stored procs.

And here is the code, you have to deal with RM10301 and distribution RM, also we recommend insert new customers into RM00101 and RM00102:

Sales Transaction Record

insert into RM10301

(

DOCTYPE,

RMDTYPAL,

RMDNUMWK,

DOCNUMBR,

DOCDESCR,

DOCDATE,

BACHNUMB,

BCHSOURC,

CUSTNMBR,

CUSTNAME,

ADRSCODE,

COSTAMNT,

SLSAMNT,

MISCAMNT,

FRTAMNT,

TAXAMNT,

DOCAMNT,

CASHAMNT,

CBKIDCSH,

CASHDATE,

DCNUMCSH,

CHEKAMNT,

CBKIDCHK,

CBKIDCRD,

CHEKNMBR,

CHEKDATE,

DCNUMCHK,

CRCRDAMT,

DCNUMCRD,

CRCRDNAM,

RCTNCCRD,

CRCARDDT,

TRDISAMT,

ACCTAMNT,

DISCDATE,

DUEDATE,

LSTEDTDT,

LSTUSRED,

GLPOSTDT,

PSTGSTUS,

CURNCYID,

TXENGCLD

)

select

DOCTYPE = (case

when a.Total > 0 then 1

else 7

end),

RMDTYPAL = (case

when a.Total > 0 then 1

else 8

end),

RMDNUMWK = กSMSก + replicate(ก0ก,

13 datalength(convert(varchar(13), a.TransactionNumber))) +

convert(varchar(13), a.TransactionNumber) + convert(varchar(10), a.StoreID),

DOCNUMBR = กSMSก + replicate(ก0ก,

13 datalength(convert(varchar(13), a.TransactionNumber))) +

convert(varchar(13), a.TransactionNumber) + convert(varchar(10), a.StoreID),

DOCDESCR = left(a.ReferenceNumber, 29),

DOCDATE = convert(varchar(10), a.[Time], 101),

BACHNUMB = กSMSก

+ replicate(ก0ก,

2 datalength(convert(varchar(2), a.StoreID))) +

convert(varchar(2), a.StoreID)

+ replicate(ก0ก,

9 datalength(convert(varchar(11), a.BatchNumber))) +

convert(varchar(11), a.BatchNumber),

BCHSOURC = กRM_Salesก,

CUSTNMBR=กCASHก,

CUSTNAME = กCASHก,

ADRSCODE = กPRIMARYก,

COSTAMNT = (case

when isnull((select round(sum(round(Cost * Quantity,2)),2)

from LINKEDSERVER.HQ.dbo.TransactionEntry

where TransactionNumber = a.TransactionNumber and

StoreID = a.StoreID),0) > 0 and a.Total > 0 then

abs(isnull((select round(sum(round(Cost * Quantity,2)),2)

from LINKEDSERVER.HQ.dbo.TransactionEntry

where TransactionNumber = a.TransactionNumber and

StoreID = a.StoreID),0))

when isnull((select round(sum(round(Cost * Quantity,2)),2)

from LINKEDSERVER.HQ.dbo.TransactionEntry

where TransactionNumber = a.TransactionNumber and

StoreID = a.StoreID),0) 0 and

a.StoreID = ก1ก and

not exists (select 1 from RM10301 c

where c.RMDNUMWK = กSMSก + replicate(ก0ก,

13 datalength(convert(varchar(13), a.TransactionNumber))) +

convert(varchar(13), a.TransactionNumber) + convert(varchar(10), a.StoreID) and

c.RMDTYPAL = (case

when a.Total > 0 then 1

else 8

end)) and

not exists (select 1 from RM00401 c

where c.DOCNUMBR = กSMSก + replicate(ก0ก,

13 datalength(convert(varchar(13), a.TransactionNumber))) +

convert(varchar(13), a.TransactionNumber) + convert(varchar(10), a.StoreID) and

c.RMDTYPAL = (case

when a.Total > 0 then 1

else 8

end))

and a.[Time] > ก01/01/2004ก

Happy integrating! if you want us to do the job or use our product give us a call 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM, Microsoft RMS customization company, based in Chicago, Arizona, California, Colorado, Texas, Georgia, New York, Florida and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on October 11, 2004

by Andrew Karasev

Microsoft RMS Great Plains Integration – overvie

Microsoft RMS Great Plains Integration – overview for IT specialist

by: Andrew Karasev

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS) are getting new market share due to the Microsoft strong marketing campaign. Both products: Microsoft Great Plains and Microsoft RMS are coming from the same Microsoft subdivision Microsoft Business Solutions (MBS), however due to the fact that both products were bought by Microsoft from different vendors, the integration between the two is not an easy thing. At this time MBS has RMS integration on the General Ledger and Purchase Order level into Great Plains out of the box. This integration has some advancements in comparison to old product: QuickSell, but it is still GL and PO only. We do understand the need for midsize and large retail companies, structured as clubs and selling on account to their members to have more adequate integration when you can synchronize your Sales information and have robust Great Plains reporting.

There is the product on the market, which is integration on the Receivables Management level from RMS to Great Plains, written by Daniel Sionov and Andrew Karasev and maintained by the alliance between LightEdge Solutions (www.lightedge.com) and Alba Spectrum Technologies (www.albaspectrum.com). In Alba Spectrum Technologies we actually do coding and product tuning for specific client needs.

If you are developer you can end up with your own custom solution, we would like to give you directions.

1. Great Plains Integration Manager if the sales volume is very low, say 100 transactions per day then you can do data export from RMS and import it into Great Plains via Integration Manager. This is rather enduser tool it is very intuitive, it validates 100% of business logic, brings in/updates master records (accounts, employees, customers, vendors. etc.) brings in transactions into work tables. The limitation of Integration Manager it does use GP windows behind the scenes without showing them so it is relatively slow you can bring 100 records but when you are talking about thousands it is not a good option. By the way you can program Integration Manager with VBA.

2. eConnect – You can create VB.Net application which will be pulling info from RMS and then uses eConnect to move it to Great Plains. eConnect is kind of Software Development Kit with samples in VB.Net. Obviously the development environment should be Visual Studio.Net. eConnect will allow you to integrate master records such as new customers, vendors, employees, etc., plus you can bring transactions into so called Great Plains work tables (eConnect doesn’t allow you to bring open or historical records you need to post work records in Great Plains, the same limitation applies to Integration Manager above) eConnect is rather for ongoing integration. It was initially created for eCommerce application integration to Great Plains.

3. SQL Stored Procedures. The product weกve mentioned above is collection of stored procs. Obviously you have unlimited control and possibilities with SQL queries. You need to know Retail Management System Headquarters and Great Plains tables structure and data flow. Launch Great Plains and go to Tools>Resource Description>Tables. Find the table in the proper series. If you are looking for the customers – it should be RM00101 – customer master file. If you need historical Sales Order Processing documents – they are in SOP30200 – Sales History Header file, etc. Do not change existing tables do not create new fields, etc. Also you need to realize that each GP table has DEX_ROW_ID identity column. Sometimes it is good idea to use inbound/outbound XML in the parameters then you can deploy web service as a middle party between two systems. RMS tables structure is self explanatory.

4. Data Transformation Services (DTS) – Good tool for importing your third party data into staging tables in GP then you can pull them in using either stored procs of Integration Manager. You can also deploy this tool for EDI export/import.

5. Great Plains Dexterity Custom Screens. You can create the window, which will have integration settings in it RMS store ID matching GP Company database, etc. Sometimes users prefer to have seamlessly integrated into GP interface custom screens for parameters settings and initiating integration. Dexterity is a good option, however remember it is always better to create new custom screen versus customizing existing one due to the future upgrade issues. Also Dexterity is in phasing our by Microsoft Business Solutions.

6. Modifier/VBA custom buttons on the existing screens alternative to Dexterity is you are comfortable with VBA and ADO.

Happy integrating! if you want us to do the job or use our product give us a call 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM, Microsoft RMS customization company, based in Chicago, Arizona, California, Colorado, Texas, New York, Georgia, Florida and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on October 13, 2004

by Andrew Karasev

Microsoft Great Plains Microsoft RMS Integration

Microsoft Great Plains Microsoft RMS Integration – overview

by: Andrew Karasev

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS) are originally developed by different software vendors, who had no idea that in the remote future (now) these two applications will be owned by Microsoft and will need to be tightly integrated. Current integration between the two is not an easy thing. At this time MBS has RMS integration on the General Ledger and Purchase Order level into Great Plains out of the box. This integration has some advancements in comparison to old product: QuickSell, but it is still GL and PO only. We do understand the need for midsize and large retail companies, structured as clubs and selling on account to their members to have more adequate integration when you can synchronize your Sales information and have robust Great Plains reporting.

There is the product on the market, which is integration on the Receivables Management and Purchase Order Processing level from RMS to Great Plains, written by Daniel Sionov and Andrew Karasev and maintained by the alliance between LightEdge Solutions (www.lightedge.com) and Alba Spectrum Technologies (www.albaspectrum.com). In Alba Spectrum Technologies we actually do coding and product tuning for specific client needs. This product allows you to map multiple RMS stores to one or multiple Great Plains companies. We usually have to tune it for specific needs of the customer, but in general words it is based on SQL insert into statement and so can handle hundred thousands transactions per day maximum of what RMS can handle. Integration is usually setup on RMS Headquarters database. However we can set it for Store Operations database.

Overview of outofthebox Microsoft RMS integration. This integration is currently available for Great Plains version 7.5 and Microsoft RMS 1.2. MBS is in process of subcontracting Nodus Technologies to write new integration for version 8.0. The weak points of the outofthebox 7.5 integration are:

1. It is for integration into one Great Plains company only. If you have multiple stores as multiple companies in Great Plains then you have to remember which batch should be posted into which Great Plains company.

2. It is on GL and Purchasing level only. So, if you have to reconcile checkbooks / Bank Reconciliation module in Great Plains you can not do it with standard integration

If you are developer you can end up with your own custom solution, we would like to give you directions.

1. Great Plains Integration Manager if the sales volume is very low, say 100 transactions per day then you can do data export from RMS and import it into Great Plains via Integration Manager. This is rather enduser tool it is very intuitive, it validates 100% of business logic, brings in/updates master records (accounts, employees, customers, vendors. etc.) brings in transactions into work tables. The limitation of Integration Manager it does use GP windows behind the scenes without showing them so it is relatively slow you can bring 100 records but when you are talking about thousands it is not a good option. By the way you can program Integration Manager with VBA.

2. eConnect – You can create VB.Net application which will be pulling info from RMS and then uses eConnect to move it to Great Plains. eConnect is kind of Software Development Kit with samples in VB.Net. Obviously the development environment should be Visual Studio.Net. eConnect will allow you to integrate master records such as new customers, vendors, employees, etc., plus you can bring transactions into so called Great Plains work tables (eConnect doesn’t allow you to bring open or historical records you need to post work records in Great Plains, the same limitation applies to Integration Manager above) eConnect is rather for ongoing integration. It was initially created for eCommerce application integration to Great Plains.

3. SQL Stored Procedures. The product weกve mentioned above is collection of stored procs. Obviously you have unlimited control and possibilities with SQL queries. You need to know Retail Management System Headquarters and Great Plains tables structure and data flow. Launch Great Plains and go to Tools>Resource Description>Tables. Find the table in the proper series. If you are looking for the customers – it should be RM00101 – customer master file. If you need historical Sales Order Processing documents – they are in SOP30200 – Sales History Header file, etc. Do not change existing tables do not create new fields, etc. Also you need to realize that each GP table has DEX_ROW_ID identity column. Sometimes it is good idea to use inbound/outbound XML in the parameters then you can deploy web service as a middle party between two systems. RMS tables structure is self explanatory.

4. Data Transformation Services (DTS) – Good tool for importing your third party data into staging tables in GP then you can pull them in using either stored procs of Integration Manager. You can also deploy this tool for EDI export/import.

5. Great Plains Dexterity Custom Screens. You can create the window, which will have integration settings in it RMS store ID matching GP Company database, etc. Sometimes users prefer to have seamlessly integrated into GP interface custom screens for parameters settings and initiating integration. Dexterity is a good option, however remember it is always better to create new custom screen versus customizing existing one due to the future upgrade issues. Also Dexterity is in phasing our by Microsoft Business Solutions.

6. Modifier/VBA custom buttons on the existing screens alternative to Dexterity is you are comfortable with VBA and ADO.

Happy integrating! if you want us to do the job or use our product give us a call 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM, Microsoft RMS customization company, based in Chicago, Arizona, Colorado, California, Texas, New York, Georgia, Florida and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on September 24, 2004

by Andrew Karasev

Microsoft Great Plains 8.0 Microsoft RMS 1,2 Mul

Microsoft Great Plains 8.0 Microsoft RMS 1,2 MultiStore Integration – overview

by: Andrew Karasev

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS) are originally developed by different software vendors, who had no idea that in the remote future (now) these two applications will be owned by Microsoft and will need to be tightly integrated. Current integration between the two is not an easy thing. At this time MBS has RMS integration on the General Ledger and Purchase Order level into Great Plains out of the box. This integration has some advancements in comparison to old product: QuickSell, but it is still GL and PO only. We do understand the need for midsize and large retail companies, structured as clubs and selling on account to their members to have more adequate integration when you can synchronize your Sales information and have robust Great Plains reporting.

There is third party integration available on the market and supported by Alba Spectrum Technologies. Its features:

Accounts Receivables transactions move from RMS to Great Plains

Multiple Stores – Multiple Great Plains companies mapping

Sales Order Processing transaction integration (settings allow you to have either AR or SOP transactions created in GP)

Purchase Order Processing POs and Receipts integration (it also imports Open POs Partial Receipts)

Automatic new customer move from RMS to GP

Automatic new vendor import

From technical side – integration is a set of SQL stored procedures and Great Plains Dexterity custom setup form. You can also schedule integration as SQL job to run overnight, looping through all your RMS stores

Recommended settings – targeting RMS Headquarters database (after daily transactions migration from Stores to Headquarter), however you could use linked SQL server to your Store Operation databases and pull daily transactions directly from there

Happy integrating! if you want us to do the job or use our product give us a call 16309615918 or 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Great Plains, Microsoft CRM, Microsoft RMS customization company, serving clients in Chicago, Arizona, Colorado, California, Texas, New York, Georgia, Florida, Minnesota and having locations in multiple states and internationally ( http://www.albaspectrum.com ), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on February 18

by Andrew Karasev

Microsoft CRM Implementation & Remote Support – ov

Microsoft CRM Implementation & Remote Support – overview for V.P. IT

by: Andrew Karasev

Microsoft CRM is now on the scene and it is increasing its market share, due to the Microsoft Business Solutions muscles and marketing strategy. It is tightly integrated with other Microsoft Business Solutions products such as Microsoft Great Plains, Solomon, Navision. Being relatively inexpensive in comparison to competitors, like Siebel, Oracle Microsoft CRM opens you the door for worldwide operations automation.

We would like to give you pluses and minuses of Microsoft CRM.

1. Web and MS Outlook based client this means that you don’t need additional installations or training everyone is using Outlook if you are on Windows XP/2000/2003

2. SQL Server database just to mention

3. Integration with Microsoft Exchange 2003/2000 this means that replies to CRM originated emails will be captured and store in CRM as activities for the Contact, Account or Lead.

4. Remote Support I would say this would be in the nature of MS CRM you host itกs database in your headquarter and users will connect to it worldwide via Internet Explorer

5. Integration with other MBS products currently it has integration interface with Microsoft Great Plains

6. Microsoft technologies dependence yes, this is true and maybe considered as minus, it doesn’t work with Netscape or Mozilla browser.

7. New Product it is relatively new on the market and may not have industry specific features yet

Also if you are considering some customization scenarios, we would suggest you to review the options below:

1. Microsoft CRM SDK – this is software development kit with C# and partly VB.net code samples – it is supported by Microsoft Business Solutions technical support. It is based on web service calls, if you are C# .NET developer – you are excellently positioned to do this type of customizations. This is the preferred modification scenario and this should be easily upgradeable customization. VB.Net examples will be available soon.

2. Legacy SQL Data integration. This is also easy and safe. If you have SQL database, sitting on the same or linked SQL Server – you can create ASPX .Net application and simply integrate it into CRM. You can place it on the navigation bar or menu in isv.config – please refer to MS CRM SDK

3. Legacy ASP integration – this is somewhat more sophisticated. You have to deploy HTTP handler to be a middle party between CRM which is .Net based and ASP which is legacy IIS. The trick is – you have to have INI file with security settings to penetrate into MS CRM with proper credentials, calling web service.

4. Microsoft Exchange Programming. Microsoft CRM has Exchange connector – which moves CRM incoming email to MS if it has GUID in its subject. You can alter this logic (for instance move email to CRM if it doesn’t have GUID but it is from the sender who is contact or account in MS CRM). Refer to MS Exchange SDK onsyncsave event handling. Then simply apply some MS CRM SDK programming you need some COM+ objects creation and VB programming experience.

5. Direct SQL touch – in #4 above I described you the scenario with MS Exchange handlers – this would be ideal world if MS CRM SDK does the job. But – in real world this is not always true – you have to do direct flags correction in CRM database (like making Activity closed, moving email attachments/octet streams, etc). This is not supported by MBS technical support – but you can rescue to this technique if you have to get job done.

6. MS CRM Customization tool – this is rather enduser tool and we don’t describe it here – read the manual. We’ve described above the options to use when this tool doesn’t do the job

7. Crystal Reports feel free to create Crystal report tables and views structure is self explanatory. Try to avoid the temptation to create your own SQL view or stored procedure in MS CRM database, instead create custom database and place your view and stored proc in it.

Happy selection, implementation and modification! If you want us to do the job give us a call 18665280577! [email protected]

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA nationwide Microsoft CRM, Microsoft Great Plains customization company, based in Chicago, Boston, San Francisco, San Diego, Los Angeles, Houston, Dallas, Atlanta, and Miami and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, SQL, VB/C#.Net, Crystal Reports and Microsoft CRM SDK developer.

[email protected]

This article was posted on September 11, 2004

by Andrew Karasev