We are currently living in a “data revolution” where just
about any desired data point can be quantified. For Accounting and ERP
management, the question becomes: how useful is such an opportunity if data is
inaccessible and/or spread across multiple platforms? In this blog, I will use
a storyboard approach to summarize my experiences and demonstrate the
challenges I faced integrating SAGE 100 and 300 systems.
Integration is Important
Integrated accounting and financial systems enable
businesses to rely on real-time information about business transactions. For
example, the operator of a small hotel business can remotely track the number
of vacant rooms, occupied rooms, and new reservations; simultaneously track
gift shop sales and latest inventory records; access real-time reports of the
day’s operations and analyze the impact of transactions on the general ledger.
One-Stop Shop
An integrated accounting system serves as a “one-stop-shop”
for all accounting information, including financial, managerial and cash-flow
accounting. As such, when using an integrated accounting system, maintaining
separate accounting procedures for preparing financial, management and
cash-flow reports is not necessary.
Real-Time Information
Stand-alone accounting is never in “real-time”, as it
requires someone to manually take information from the ordering system and
re-enter it in the accounting system. This means that financial information
required for strong decision making is not up to date. Data re-entry further
invites human error, leading to a greater chance of making decisions based on
faulty information.
Integrated accounting provides a complete real-time picture
of the business at any time, as information automatically flows into the
accounting system as orders are processed. A web-based business management
system with integrated accounting allows for complete and accurate financials
that can easily be transported.
Integration Process
DBSync seamlessly integrates various applications with Sage
100 and 300 ERP to “push and pull” data including product details, inventory
levels, payment and shipping, customer information, sales orders, and status
updates. The information exchanged is securely and automatically synchronized
between Sage 300 ERP and any other target system.
SAGE 100 INTEGRATION:
It is important to note that both DBSync Cloud agent or
OnPrem service can be used to integrate with SAGE. A user is able to install
any ODBC driver on his/her local server (preferably a 32-bit driver). An
attempt should be made to supply values for the driver using the available
fields as shown in the image above. If it is discovered that a “Test
Connection” is unsuccessful then it may be necessary to create a manual
connection string that is specific to the newly installed ODBC. Once the ODBC
connected to DBSync staging server is established, we make use of SAGE Visual
Integrator to make a connection with our staging server through the ODBC
bridge.
A typical Visual Integrator (VI) import job making a
connection to our ODBC to import Sales Orders into SAGE is illustrated below:
Figure 1.
- Once
you are at the SAGE homepage, navigate to Visual Integrator and expand
Import Job Maintenance or Export Job Maintenance (depending on if you are
bringing data into SAGE or exporting data from SAGE).
- The
No.2 in the above screenshot illustrates a typical SAGE sales order
mapping through a VI job with DBSync staging ODBC server to bring data
sales orders into the SAGE system.
SAGE 300 INTEGRATION:
There are two ways to integrate with SAGE 300 using DBSync.
One option is to go via the SQL Server connector route, which uses the DBSync
database adapter. The other way is through DBSync Swagger adapter. The Swagger
adapter is the preferred route as SAGE 300 provides integrator through Swagger
UI. The Sage 300 Web API makes it easy to develop services that integrate with
Sage 300 data and business logic. The Web API can be used to create integrated
services for Sage 300 using many frameworks and tools—including just a web
browser. Swagger UI generates visual and dynamic documentation of the Sage 300
Web API endpoints and their models. It can be used to support verbs for
endpoint paths. The defaulted company for these operations is SAMLTD. To access
the Swagger JSON document and Swagger UI page, navigate to <<protocol>>://<<servername/Sage300WebApi>>,
where the protocol is HTTP or HTTPS and server name is the name of your Sage
300 server (for example, https://localhost/Sage300WebApi). Swagger is part of
the Open API Initiative (OAI). For more information on open API/Swagger and
Swagger UI, please see:
where the protocol is HTTP or HTTPS and server name is
the name of your Sage 300 server (for example,
https://localhost/Sage300WebApi). Swagger is part of the Open API Initiative
(OAI). For more information on open API/Swagger and Swagger UI, please see:
Relevant Use-Cases
Over a period of time, many use-cases and data-flows have
leveraged DBsync iPaaS. The most prominent and relevant SAGE integrations
asked for and in play, are:
Order to Cash:
The most typical use-case or automation that is seen with
any CRM & Accounting integration is Order-To-Cash or Quote-To-Cash
data-flow. Here, CRM systems like Salesforce or Microsoft Dynamics 365/CRM
typically become the system of record or a master database for all sales and
marketing processes that drives the integration for all customer and order
information. Once the orders are placed in the CRM and an Opportunity is
“Closed Won”, the sale is accounted for by integrating the Opportunity/Order
and all related information (Customer, Customer’s BillTo and ShipTo, Product
into SAGE receivables). The integration also includes the creation of Customer,
Product attributes since these records do not pre-exist. This prevents double
data entry of Customer and Product attributes. Lastly, the ‘transaction insert’
operation is executed, constituting of Quotes, Sales Order, Invoices and Sales
Receipts. The relevant transaction data flow can be activated within DBSync
depending on the requirements or business use case.
A typical CRM Object to SAGE Entity data flow mapping is
illustrated below:
|
CRM
|
Sync Direction
|
SAGE 100 / 300
|
|
Accounts
|
🡪
|
Customer
|
|
Opportunity / Order
|
🡪
|
Quotes /SO / Invoices / Receipts
|
|
Product
|
🡪
|
Product
|
|
Payments
|
🡨
|
AR Payments
|
|
A/R Reporting
|
🡨
|
A/R
|
Inventory Management & Movement
Inventory Management, Movement and Tracking is one of the
most complex and difficult automation in the integration business. It can be
the Achilles heel for lots of business who have inventories in multiple
locations. As SOs and POs are generated, it becomes paramount to track
inventory items’ movements and stock-on-hand updates. Typically in these
scenarios, the SAGE or Accounting presides over the other system and more often
than not becomes the master database. The incremental updates that are taking
place in SAGE need to be reflected in your CRM so the end-user/rep knows stock
availability before an order is placed. This particular use-case is very
relevant today, as reps/service technicians are in the field with hand-held
devices and require continuous real-time integration. Automation has increased
stock visibility across the board to our customers, and in some cases has not
only has reduced operational hazards but has also reduced billing cycles from
several weeks to a matter of days.
A typical mapping between CRM and SAGE tracking inventory
can be seen below:
|
CRM
|
Sync Direction
|
SAGE 100 / 300
|
|
Warehouse / Locations
|
🡨
|
Locations
|
|
PriceBook
|
🡨
|
PriceBooks
|
|
Product
|
🡨
|
Products
|
|
Inventory
|
🡨
|
Inventory
|
Other notable automation/integrations with SAGE are AP
Automation, Project Management, and Payroll Integrations. Please continue to
stay connected for more insightful and detailed discussions on some of the more
popular integrations that can be done with SAGE 100 or SAGE 300 systems.
No comments:
Post a Comment