Microsoft Great Plains Integrations ? Retail Management Sample

Microsoft Business Solutions is emerging as very attractive vendor for mid-size 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 in-house 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,

COST AMNT,

SLSAMNT,

MISCAMNT,

FRTAMNT,

TAXAMNT,

DOCAMNT,

CASHAMNT,

CBKIDCSH,

CASHDATE,

DCNUMCSH,

CHEKAMNT,

CBKIDCHK,

CBKIDCRD,

CHEKNMBR,

CHEKDATE,

DCNUMCHK,

CRCRDAMT,

DCNUMCRD,

CRCRDNAM,

RCTN CCRD,

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 1-866-528-0577! help@albaspectrum.com

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. akarasev@albaspectrum.com

---

Document Templates Give You The Perfect Framework For Your Documents

When it comes to running an office, the SOHO entrepreneur... Read More

Accessing XML Using Java Technologies

The most important benefit of XML is its simplicity. Though... Read More

Microsoft Small Business Manager eCommerce ? Overview

Microsoft Business Solutions Small Business Manager is scaled down Great... Read More

Lotus Notes Domino and Microsoft CRM Integration

Well, even if the combination might look very unusual, we... Read More

An Easy Way to Deal with Email Viruses and Worms

If you feel intimidated when someone tries to teach you... Read More

Accounts Payable: A Powerful Document Management and Workflow Solution

Accounts payable is just one area of office management where... Read More

Free Microsoft Word Online Training Tutorial Resources

Microsoft Word is one of the most popular office applications... Read More

Adware and Spyware

What is 'adware'?Adware is basically software or scripts that are... Read More

Microsoft Great Plains Implementation in Russia ? Overview for Consultant

Microsoft Business Solutions Great Plains is very popular ERP platform... Read More

Microsoft C# vs. VB.Net

Hi, Guys,I believe a lot of programmers are trying to... Read More

How Do I Get Rid Of That Darn Spyware?

Ad-Aware and Spybot are probably the two most well known... Read More

RFID: Strengthen the Position for SAP; United States

SAP Inc., a global leader in client/server enterprise application software... Read More

Microsoft Great Plains, Navision, Axapta ? Selection Considerations

During the years of our consulting practice, which comes back... Read More

Groupware Finds Home in American Business: Collaboration Tool Analysis

What is Groupware?Have you ever had to manage document collaboration... Read More

How to Choose the Right Accounting Software for Your Business

With any good luck and a good amount of hard... Read More

HSphere Control Panel Tips and Tricks - Power At Your Fingertips: Part 1

The first topic we are going to discuss... Read More

Importance of Coding Standards

Programming Help for BeginnersWe write programs to instruct computers. When... Read More

Microsoft Navision and Crystal Reports - An Overview

Microsoft Business Solutions ? Navision is an integrated solution for... Read More

What is Groupware?

Vince Lombardi once said that, "The achievements of an organization... Read More

Microsoft Great Plains international implementation ? USA / Mexico ? overview for consultant

Microsoft Business Solutions Great Plains was purchased from Great Plains... Read More

Separate Anti-Keylogging Protection: Who Needs it Most?

If there still are few unprotected computers left, I haven't... Read More

Microsoft Great Plains Reporting ? Overview for Developer

Looks like Microsoft Great Plains becomes more and more... Read More

ERP Consulting: Microsoft Great Plains Partner Future Directions

In the Clinton era the status quo was simple: you... Read More

Great Plains Dexterity Development: FAQ

Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More

Great Plains Customization Upgrade? Overview For CIO/IT Director

Around the same time Microsoft made its move with .Net... Read More

Professional XP Style Icons in Software and Website Development

Using professional icons in your application or website can bring... Read More

Microsoft Great Plains GL: General Ledger ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics/eEnterprise... Read More

New SQL Delta Version 3.1

COMMAND LINE FUNCTIONA powerful command line script processor has been... Read More

Getting Patched with Windows Service Pack

Are you one of those people that keeps track of... Read More

Internet Security Threats: Who Can Read Your Email?

Before being able to choose a secure Internet communication system,... Read More

Microsoft Great Plains Implementation for Large Corporation ? Overview for VP IT

Microsoft Business Solutions Great Plains was historically designed to serve... Read More

Microsoft CRM Lotus Notes Domino Connector FAQ

Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being... Read More