You create purchase order from your sales order. You do it every time if you are in certain in industry. But why doesn’t accounting systems let you automate it?

We saw this behavior in many accounting application from – QuickBooks to Intacct and Microsoft Dynamics GP (aka GreatPlains).

We ran into this requirement with quite a few times and ended up using our DBSync application to build out these automated processes.

Here are some of the use cases
1. CRM application creates an opportunity
2. Opportunity flows into accounting as a Sales Order
3. Invoices can be created from these Sales Orders
4. Purchase Orders can be created using the Sales Order

To create Purchase Orders, you will need to make sure that all items have associated item vendors in it and in certain accounting applications, it should also need to be “Purchased” and not in stock.

In most of accounting system, you will have to read Sales Orders (SO) and translate to Purchase Orders (PO). Fortunately Microsoft Dynamics GP (GreatPlains) makes it easy for it when using with eConnect API. Microsoft Dynamics provides a SOP Transaction for creating PO from Sales Order (nice !!!). There seems to be a misconception in GP community that taSopToPopLink only links the two documents which is not true. It actually does create the PO for you.

The following eConnect XML request creates PO from a Sales Order (SOPNUMBE)

<eConnect xmlns:dt=”urn:schemas-microsoft-com:datatypes”>

Once you run it, you get back true or false (with error). Error is quite self descriptive for you to trouble shoot it.

You can find the records in POP10100 (header) and POP10110 (line item).

Hope to post the video of it working in due course. Hope this article helps.

Enjoy integrating…

We ran into a problem with upgrading to Microsoft Dynamics GP 2010 and did not find much documentation to solve it.

We were consistently getting error at

The following SQL statement produced an error:
CREATE VIEW GL10000CurrencyTranslationView AS select [GL10000Final].[OPENYEAR], [GL10000Final].[ACTINDX], [GL10000Final].[CRDTAMNT], [GL10000Final].[DEBITAMT], [GL10000Final].[ORCRDAMT], [GL10000Final].[ORDBTAMT], [GL10000Final].[TRXDATE], [GL10000Final].[DSCRIPTN], ...


After looking under the hood we ran the following 2 scripts to get over it -
Script 1

GO

SET ANSI_NULLS OFF
GO

SET ANSI_PADDING OFF
GO


Script 2

GO

SET ANSI_NULLS ON
GO

CREATE VIEW [dbo].[GL10000CurrencyTranslationView] AS select [GL10000Final].[OPENYEAR], [GL10000Final].[ACTINDX], [GL10000Final].[CRDTAMNT], [GL10000Final].[DEBITAMT], [GL10000Final].[ORCRDAMT], [GL10000Final].[ORDBTAMT], [GL10000Final].[TRXDATE], [GL10000Final].[DSCRIPTN], [GL10000Final].[REFRENCE], [GL10000Final].[CURNCYID], [GL10000Final].[Original_Exchange_Rate], [GL10000Final].[JRNENTRY], [GL10000Final].[TRXSORCE], [GL10000Final].[SOURCDOC], [GL10000Final].[ORDOCNUM], [GL10000Final].[ORTRXSRC], [GL10000Final].[ORMSTRID], [GL10000Final].[ORMSTRNM], [GL10000Final].[ORTRXTYP], [GL10000Final].[SERIES], [GL10000Final].[VOIDED], [GL10000Final].[Ledger_ID], [GL10000Final].[TranslationCurrencyID], [GL10000Final].[CurrencyTranslationType], [GL10000Final].[PERDENDT], [GL10000Final].[TranslationExchangeRate], case GL10000Final.CRDTAMNT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended([GL10000Final].[RTCLCMTD], 3, [GL10000Final].[TranslationExchangeRate], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[DECPLCUR], [GL10000Final].[CRDTAMNT]) end as TranslationCreditAmount, case GL10000Final.DEBITAMT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended([GL10000Final].[RTCLCMTD], 3, [GL10000Final].[TranslationExchangeRate], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[DECPLCUR], [GL10000Final].[DEBITAMT]) end as TranslationDebitAmount, [GL10000Final].[SequenceNumber], [GL10000Final].[PERIODID], [GL10000Final].[CURRNIDX], [GL10000Final].[DECPLCUR], [GL10000Final].[RATETPID], [GL10000Final].[EXGTBLID], [GL10000Final].[EXCHDATE], [GL10000Final].[TIME1], [GL10000Final].[RTCLCMTD], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[Adjustment_Transaction] from (select distinct [GL_TRX_WORK].[OPENYEAR], [GL_TRX_WORK].[ACTINDX], [GL_TRX_WORK].[CRDTAMNT], [GL_TRX_WORK].[DEBITAMT], [GL_TRX_WORK].[ORCRDAMT], [GL_TRX_WORK].[ORDBTAMT], [GL_TRX_WORK].[TRXDATE], [GL_TRX_WORK].[DSCRIPTN], [GL_TRX_WORK].[REFRENCE], [GL_TRX_WORK].[CURNCYID], [GL_TRX_WORK].[XCHGRATE] as Original_Exchange_Rate, [GL_TRX_WORK].[JRNENTRY], [GL_TRX_WORK].[TRXSORCE], [GL_TRX_WORK].[SOURCDOC], [GL_TRX_WORK].[ORDOCNUM], [GL_TRX_WORK].[ORTRXSRC], [GL_TRX_WORK].[ORMSTRID], [GL_TRX_WORK].[ORMSTRNM], [GL_TRX_WORK].[ORTRXTYP], [GL_TRX_WORK].[SERIES], [GL_TRX_WORK].[VOIDED], [GL_TRX_WORK].[Ledger_ID], [GL_TRX_WORK].[TranslationCurrencyID], [GL_TRX_WORK].[CurrencyTranslationType], [GL_TRX_WORK].[PERDENDT], F.XCHGRATE as TranslationExchangeRate, [GL_TRX_WORK].[SQNCLINE] as SequenceNumber, [GL_TRX_WORK].[PERIODID], E.[CURRNIDX], (E.[DECPLCUR]-1) as DECPLCUR, [GL_TRX_WORK].[RATETPID], [GL_TRX_WORK].[EXGTBLID], F.[EXCHDATE], F.[TIME1], D.[RTCLCMTD], dbo.mcFuncGetDenExchRate(GL_TRX_WORK.TranslationCurrencyID,D.RTCLCMTD) as DENXRATE, [GL_TRX_WORK].[MCTRXSTT], [GL_TRX_WORK].[Adjustment_Transaction] from DYNAMICS..MC40200 E, DYNAMICS..MC40300 D cross apply (select e.YEAR1 as OPENYEAR,a.JRNENTRY,a.SOURCDOC,a.REFRENCE,f.DSCRIPTN,a.TRXDATE, a.TRXSORCE,f.ACTINDX,a.SERIES,f.ORTRXTYP,f.ORMSTRID,f.ORMSTRNM, f.ORDOCNUM,a.ORTRXSRC,a.SQNCLINE,a.CURNCYID,b.CURNCYID as TranslationCurrencyID, a.CURRNIDX,a.RATETPID,b.ExchangeTableID as EXGTBLID,a.XCHGRATE, a.EXCHDATE,a.TIME1,a.RTCLCMTD,dbo.glFuncGetPeriodID(a.TRXDATE,a.OPENYEAR,2) as PERIODID,f.CRDTAMNT,f.DEBITAMT,f.ORCRDAMT,f.ORDBTAMT, e.PERDENDT, dbo.mcFuncGetMCTrxState(b.CURNCYID) as MCTRXSTT,b.CurrencyTranslationType, a.VOIDED,a.Ledger_ID, a.Adjustment_Transaction, case b.CurrencyTranslationType when 1 then e.PERDENDT when 3 then a.TRXDATE end as ExchangeRateDate from GL10000 a, GL10001 f, (select c.ACTINDX,b.CURNCYID, ExchangeTableID= case CurrencyTranslationType when 1 then b.AverageExchangeTableID when 3 then b.HistoricalExchgTableID end, c.CurrencyTranslationType from MC00200 c,MC40600 b where c.CURNCYID='' and c.CurrencyTranslationType<>2) b, (select distinct b.PERIODID, a.YEAR1,a.FSTFSCDY,a.LSTFSCDY, b.PERIODDT,b.PERDENDT from SY40101 a, SY40100 b where a.YEAR1=b.YEAR1 and b.SERIES=2) e where a.JRNENTRY=f.JRNENTRY and f.ACTINDX=b.ACTINDX and a.PERIODID=e.PERIODID and e.YEAR1=e.YEAR1 and a.TRXDATE >=e.FSTFSCDY and a.TRXDATE <=e.LSTFSCDY) GL_TRX_WORK cross apply dbo.mcFuncGetExchangeRateTable(GL_TRX_WORK.ExchangeRateDate, GL_TRX_WORK.EXGTBLID, D.TRXDTDEF, D.DATELMTS,D.PRVDSLMT,D.Base_Exchange_Rate_On,GL_TRX_WORK.MCTRXSTT) F where GL_TRX_WORK.EXGTBLID = D.EXGTBLID and D.CURNCYID=E.CURNCYID) GL10000Final
GO


This got us past this hurdle.

Hope this helps you get started with Microsoft Dyanamics GP 2010.

As you grow into your business, you add applications. You start off with Quickbooks, then add eCommerce applications and more as you start to organize your business and life. Then you realize “Geez” I have all these data all across and I am sitting down copying data from one application to the other, reconciling payments and more. Every business we have spoken to have its own story…

While we are trying to address most of these issues, lately we have received a lot of interest in our Intacct Integration Connector. Intacct themselves provide Salesforce.com integration with a pre-built setup, works for most case and does not in others. But a bigger problem is integrating with existing in-house application like Shopping carts and others. If you look at the Intacct ecosystem, there are not many, actually none which are cost effective.

We had a similar experience with one of our prospects and we recorded our session to show you all how we can easily use DBSync to integrate with in-house databases. We have also used the same mechanism to integrate with other CRM’s like SugarCRM and vTiger. We hope you find this informative.

This is a two part YouTube video.

Part 1:

Part 2:

Please feel free to contact us if you have any questions. You can also register at www.mydbsync.com to get access to our Wiki for more examples.

Many of our customers are not use on how best to streamline business process between CRM like Salesforce.com or Microsoft CRM or eCommerce or shopping carts with accounting systems like Quickbooks, Intacct or Microsoft Dynamcs GP.

A “typical” business flow is like

Opportunity -> Estimate/Quotes  ->Sales Order -> Invoice ->Credit

Here is how they should think about it -

Do you carry inventory?

If Yes (manufacturing, retail etc)

Holding inventory has a all sort of new ounces to worry about like managing adequate Inventory levels, supply chain – remember – holding inventory is not a good idea as it locks up working capital. From a standpoint of online orders or CRM Opportunity integration, they should always use “Sales Order”. Here are some are benefits -

• Sales Order does not hit GL and usually is a “notification” for fulfillment to deliver
• As you ship or deliver goods, you can generate multiple Invoices against a Sales Order
• Sales Order will deduct from inventory but not necessarily commit shipped on Inventory. This gives you a better count of inventory on hand vs commited.

So the recommendation is

Opportunity -> Quotes -> Sales Order -> Invoice (on or more) -> Credits or RMA (Return Merchandise)

If No (Services companies, Professional Services, Consulting etc)

You deal with people most of the time and billing is usually a function of scope of work or time spent.  Normally you would have many contract terms on billing as mile stone based, retainers and others. So in your case you can have multiple setups of invoicing and would trigger at different times.

• If you receive retainers, you can apply towards an Asset account and as you invoice it, move it from the asset account to the income account
• You can use Estimate to hold the hours allocated
• Invoices (and PO or Bills on AP if you are sub contracting) can be generated from Time & Expense modules
• The same can be done if you invoice based on milestones. You can deduct hours or Unit of work against your Estimate towards invoicing

So the recommendation is

Opportunity -> Estimate (hold hours) -> Invoice (invoice on spent time on milestone and track against Estimate) -> Credits

These are some simple rule of thumb when designing your business process flow. Hope this helps our audience.

Most of us are aware Master Data Management is not just managing data, but it’s beyond just MDM, it actually requires use of technology, data integration tools & processes to create and maintain consistent and accurate list of master data in organizations. Master Data Management or MDM typically called as might become a nightmare for many organizations either large or small if it’s not planned and implemented at the right time. So how organizations need to plan for it?

3 Steps to Plan for Master Data Management

1. Identify Master Data Requirements – First and foremost thing when you have decided to go for master data management is the need to evaluate your existing systems, processes, infrastructure, organization structure and more, then try to find out if your organization actually requires an MDM and start identifying the requirements by asking questions as what’s are the business values to be achieved with this information from MDM & more.

2. Business Process Requirements – Once you have identified your MDM requirement your next immediate things to do is to start asking questions related to business process like what needs to be managed, who would be managing the MDM, what are the processes to be considered, what are the integration tools required, who can access the information, what’s the format of display, who can alter or edit the information, how to maintain the master list should it be in single format or multiple formats/copies, what locations to be maintained and more.

3. Type of Analytical requirements – Next would be to find out what types of analytical requirements for your organizational MDM plan like for areas as business intelligence & management reporting for accurate information for various roles and responsibilities.

From these steps and information start building the MDM project plan & look out for vendors like IBM, Informatica, Oracle, SAP & others who can implement MDM for your organization. During the next few years Master data management might become mandatory due to increase in mergers & acquisitions worldwide. Developing a strong MDM for large or small business helps in organizations success.

Learn more about DBSync

All major auto manufacturing companies either large or mid size have implemented JIT (Just in Time) concept for their manufacturing units across the globe. JIT was pioneered, implemented and practiced by Toyota few decades back. JIT (Just in Time) concept is nothing but ensuring inventory to be available on time for its manufacturing or assembling units. Though these manufacturing organizations use JIT concept do they need sales & inventory forecasting software as they run on JIT (Just in Time).

Four Reasons why it’s crucial:

• Accurate inventory forecasting – For organizations running on JIT system it’s very important to have its inventory to be forecast accurately to keep its manufacturing or assembling units be running on schedule or the system would get collapsed due to wrong forecasting methods.

• Critical components on JIT – Not all components can be made to run on JIT system as few of the components play a critical role in final output of the product. So it’s better to have forecast these components on higher level for usage at critical juncture with a small percentage of inventory in the system

• Collaborating with Suppliers – Suppliers supplying components or materials to JIT (Just in Time) organizations need to collaborate with vendors for accurate forecasting of components or materials and need forecasting software which works with suppliers and vendors IT infrastructure.

• After Sales Service – Although JIT (Just in Time) can be implemented for manufacturing components what about for after sales service? For parts to be used in after sales service organizations cannot implement JIT (Just in Time) as it would cripple the entire system of service and organizations image. So in order to have better after sales service support organizations need forecasting software with sufficient inventory levels for day to day operations.

JIT (Just in Time) has been implemented successfully by few major manufacturing units across the globe, but not 100% successful. So manufacturing organizations need forecasting software for forecasting inventory required for its day to day operation, critical components & components for after sales service.

For more on inventory level forecasting visit www.mydbsync.com

Most of the large & medium size manufacturing companies & retail chain stores have inventory forecasting software’s for forecasting inventory & future sales number based on past data, industry trends & economic conditions All statistical based inventory and sales forecasting models or software’s aren’t 100% accurate but have you ever thought this inventory forecasting software’s fail completely.

 3 reasons for failure:

• Negative sales – In many products based organizations sales happens during last few days of the month or year for achieving numbers. Few of these sales are called negative sales as they would be reversed back at a later date. Does your sales and inventory forecasting software accept stock reversing if yes then it failed to forecast sales accurately? So the sale happened isn’t actual sale and all forecast done for future dates in invalid due to negative sales. Do you still rely on the data?

• Promotional offers – Does your sales and inventory forecasting software consider your organizations promotional offers while forecasting sales and inventory? If yes, what’s the base for such forecasting as each promotional offer is unique to attract more and more customers and varies depending on the sales trend, market scenario and geography?

• Newly Launched products. All products have specific product life cycle from launching stage, growth stage, maturity stage and finally phase out stage. But few products are launched which wouldn’t fall into any existing product category as they have been launched to create a new product category itself like world cheapest car. In such new launches does your forecasting software forecast sales & inventory. If yes what’s the basis for such forecasting or does it considers competitors most similar products. If yes it’s not forecasting for your product but for your competitor products. Do you really need such forecasting data?

Considering these facts under which sales and inventory forecasting software’s fail, let’s assume we would soon have sales and inventory forecasting software which forecasts up-to 99.99% accurately.  Till then we need to rely on our old forecasting software’s only.

For detailed sales &inventory forecasting software visit www.mydbsync.com 

Learn more about DBSync & Lokad

If you are starting to feel that your current accounting and business solution is slowly but surely becoming inadequate, not catering to your business needs entirely and also isn’t keeping up pace with your organization growth, then you have just outgrown the entry-level accounting software. In such a scenario if you’re seeking a solution that can overcome these limitations then, Microsoft’s ERP solution,  Dynamics GP (Great Plains) is an ideal upgrade that can cater to your business needs and perhaps more.

With the advent of cloud, there has been a fundamental shift in the way businesses are being operated these days. This change is more evident than ever before as more and more organizations require both on-premise and online solutions. In most cases these processes are interconnected, leading to the integration of financial systems, supply chain management, CRM, e-selling, and other key business processes. In view of this, GP provides great flexibility for small and mid-sized businesses alike to deal with the IT challenges they face in the new interconnected economy. Being a Microsoft Certified partner, we have managed to make better use of features incorporated in GP and has enabled us to provide successful integration with host of applications.

With DBSync, Microsoft Dynamics GP users can connect to databases, on-demand CRM tools and a variety of other applications to improve productivity and data visibility. Some of the features that customers can make use of are as follows,

• Synchronize Customer Master Databases with Microsoft Dynamics GP
• Convert Opportunities Won to Order generated in MS Dynamics GP
• Convert Opportunities Won to Invoice in MS Dynamics GP
• Integrate E-commerce website orders to MS Dynamics GP
• Integrated payments received from online payment systems for reconciliation.

DBSync for Salesforce.com & Microsoft Dynamics GP integrates Salesforce objects like Accounts, Contacts, Products and Opportunities to GP as Customers, Sales Order Processing (SOP), Purchasing Order Processing (POP ), Receivables (RM), Items, Invoicing Transactions, and many more, while preserving links between tables.

Quality of data plays a major critical role in industries like retail, telecommunications & financial services with large customer base.  Most organizations falling under these industries in a rush to capture market share and to add new customers give secondary thought to quality of data captured, leading to substandard customer data with inaccurate, duplicate & outdated customer details.

Inaccurate or poor customer data is due to non standardization of data capturing process or format, multiple data capturing points across the organization & data storing in incompatible systems and formats.

Organizational impacts due to poor data quality

• Business performance – Due to duplication of data it’s difficult for marketing to cross sell other products and services, profiling and segmentation of customers leading to decrease in business performance
• Customer retention – Due to poor data quality cost of customer retention is high as it requires multiple interactions with customers to understand their actual requirements which would have been solved with  accurate data and past transaction details
• Customer Mapping – Due to multiple entries of same customer with contradicting details it would lead to wrong mapping of customer s with wrong product or service leading to valuable customer loss and revenue to organization

So organizations with large customer data should ensure their data capturing methods are standardized across all data capturing points with latest data integration software for a satisfied customer and profitable organization.

Learn more about data integration by visiting www.mydbsync.com

Learn more about DBSync

Have you ever imagined few years back that our world would see faster changes in technology front with newer and better products and features? Yes, let’s accept the fact that we are in world with fast changes in technology right from accounting software to various payment modes by customers and organizations.

All nonprofit organization are run by donations made by its members and non members in various modes of payments like cheques, credit card, debit card, mobile banking, online banking, PayPal & other modes.  Currently QuickBooks online edition accepts payments only through credit card payment by its donors, members & non members.

Reasons why QuickBooks should incorporate these features in QuickBooks online edition

• Higher ROI (Return on Investment) – All nonprofit organization either small or large look for return on investment as they rely only on donations. If these latest payments modes are automated rather than manually entering them it would reduce a substantial amount of time and money for the organization
• Lower TCO (Total cost of ownership) – Most accounting software vendors like QuickBooks and others would always reply on lower TCO for their customer to be more competitive in the market. By these features automated payments modes the cost of ownership would reduce drastically for customers

We would soon see a world without any cheque payments either by organizations or customers. To be in tandem with the changes in payment modes by customers and organizations QuickBooks need to incorporate these features as quickly as possible in QuickBooks online edition to be ahead of its competition and being a next generation company for its customers.