SQL Accounting Integration : Smart-Acc e-Support

Prerequisite

The integration are performed using SQL Accounting SDK Live (https://wiki.sql.com.my/wiki/SDK_Live)

As such, the SQL Accounting program must be installed on the machine that need to run the integration task.

The program can be downloaded from https://www.sql.com.my/download/demo/sqlacc-setup.exe

After download and install, you should have the icon on your desktop.

Introduction

This module enabled the data synchronization between DynaMod and SQL Accounting vice versa with preset of tasks such as download stock from SQL Accounting, upload Cash Sales to SQL Accounting.

The module can be access from [Common Module] - [System Utility] - [SQL Accounting]

There are 2 screen currently available for the module:

1. SQL Accounting Integration Project

2. SQL Accounting Integration Task

SQL Accounting Integration Tasks

There are total 16 tasks available currently.

1. SQLACC_DOWNLOAD_AGENT

Download from SQL Accounting Agent To DynaMod Salesman

DynaMod: AcSalesman

SQL Accounting: AGENT

AcSalesman.AcSalesmanID = Agent.Code

AcSalesman.AcSalesmanName = Agent.Description

AcSalesman.Active = Agent.IsActive

2. SQLACC_DOWNLOAD_AREA

Download Area from SQL Accounting to DynaMod

3. SQLACC_DOWNLOAD_TERM

Download Term from SQL Accounting

4. SQLACC_DOWNLOAD_CURRENCY

Download Currency from SQL Accounting

Following will not update if currency code already exists.

1. CurrencyRate

2. BankBuyRate

3. BankSellRate

Update only on following:

1. Currency Desription

2. Currency Symbol

5. SQLACC_DOWNLOAD_STOCK_GROUP

Download Stock Group from SQL Accounting

6. SQLACC_DOWNLOAD_TARIFF

Download Tariff from SQL Accounting

7. SQLACC_UPLOAD_AGENT

Upload DynaMod Salesman To SQL Accounting Agent

DynaMod: AcSalesman

SQL Accounting: AGENT

AcSalesman.AcSalesmanID = Agent.Code

AcSalesman.AcSalesmanName = Agent.Description

AcSalesman.Active = Agent.IsActive

8. SQLACC_UPLOAD_AREA

Upload DynaMod Area to SQL Accounting Area

9. SQLACC_UPLOAD_TERM

Upload Term to SQL Accounting's Term

10. SQLACC_UPLOAD_PROJECT

Upload DynaMod Location as SQL Accounting Project

Upload DynaMod Location to SQL Accounting's Project. Cash Sales in SQL Accounting can only be identified by Project for their financial report or Sales Analysis.

11. SQLACC_UPLOAD_CURRENCY

Upload Currency to SQL Accounting

Following will not update if currency code already exists.

1. BuyingRate

2. SellingRate

Update only on following:

1. Description

2. Symbol

12. SQLACC_UPLOAD_COMPANY_CATEGORY

Upload DynaMod Customer Group to SQL Accounting's Company Category (Customer Category)

13. SQLACC_DOWNLOAD_STOCK

Download Stock from SQL Accounting

Things would be download from SQL Accounting for this task.

1. Multiuom Stock: Default selling price (Company Level only). UOM Rate.

2. Stock Barcode: It tight to corresponding Stock's UOM.

These tasks would be perform first:

SQLACC_DOWNLOAD_STOCK_GROUP

SQLACC_DOWNLOAD_TARIFF

14. SQLACC_DOWNLOAD_STOCK_TRANSFER_AS_RECEIVE

Download Stock Transfer from SQL Accounting as Stock Receive in DynaMod

Condition to be able to download to DynaMod:

1. Location To must equivalent to Project in SQL Accounting Stock Transfer Items

2. 1 Document can only have 1 Location To. If more than 1 Location To encountered in item list of stock transfer, the document wouldn't be downloaded.

These tasks would be perform first:

SQLACC_DOWNLOAD_STOCK_GROUP

SQLACC_DOWNLOAD_TARIFF

SQLACC_DOWNLOAD_STOCK

15. SQLACC_UPLOAD_CUSTOMER

Upload Customer to SQL Accounting

Things to take note:

1. Debtor Account = [DynaMod] Customer Code

2. Debtor Control Account = [DynaMod] Trade Debtor Control Account

3. Agent = [DynaMod] Salesperson

4. Term = [DynaMod] Term

These tasks would be perform first:

SQLACC_UPLOAD_AGENT

SQLACC_UPLOAD_AREA

SQLACC_UPLOAD_COMPANY_CATEGORY

SQLACC_UPLOAD_TERM

SQLACC_UPLOAD_CURRENCY

16. SQLACC_UPLOAD_CASH_SALES

Upload Cash Sales to SQL Accounting

Things to Take Note:

1. Cash Sales will post to SQL Accounting 2 places. Cash Sales and AR Receipt

2. Cash Sales with negative bill amount will post SQL Accounting 2 places. Credit Note and AP Payment

3. Location = Project in SQL Accounting. SQL Accounting's location is use for stock control. For sales to be differentiated by Outlet, Projects shall be created accordingly (If having 10 Locations, then create 10 Projects as well)

4. 1 to 1 posting from DynaMod to SQL Accounting

5. Agent in cash sales (Salesman) must be upload first.

6. Customer in cash sales must be upload first.

7. Stock records with corresponding UOM shall be downloaded first.

IMPORTANT NOTES:

1. If tax code been used in transaction, such tax code must be maintained in SQL Accounting. Eg: SR

2. Round Cent would be itemized in SQL Accounting. Please create a corresponding Stock Code: ROUND CENT, UOM: UNIT

3. Service Charge would be itemzed in SQL Accounting. Please create a corresponding Stock Code: SERVICE CHARGE, UOM: UNIT

4. Other Balance (Eg: Sales - Sales Voucher Exceed Value (53000 in DynaMod) ) would be itemized in SQL Accounting. Please create a corrsponding Stock Code: OTHER BALANCE, UOM: UNIT

Scenario: Sales 95, but paid using Voucher 100. Double Entry would be:

Dr Voucher 100

Cr Sales 95

Cr Sales - Sales Voucher Exceed Value 5

5. Payment Collection would be posted to SQL Accounting as AR Receipt (1 Receipt with the corresponding knock-off amount).

Eg: If the transaction consists 2 payment method (VOUCHER, CASH), then 2 receipts would be created in SQL Accounting to indicated the collection.

Please make sure all possible collection account being created in SQL Accounting or vise versa, Eg: 30030, 33031, 33032 etc.

You may refer to [Company System Setting] - [POS Payment Account] - [GL Mapping for Payment Collection], and default Sales Cash Collection Account

These tasks would be perform first:

SQLACC_UPLOAD_AGENT

SQLACC_UPLOAD_AREA

SQLACC_UPLOAD_COMPANY_CATEGORY

SQLACC_UPLOAD_TERM

SQLACC_UPLOAD_CURRENCY

SQLACC_UPLOAD_CUSTOMER

SQLACC_UPLOAD_PROJECT

SQLACC_DOWNLOAD_STOCK_GROUP

SQLACC_DOWNLOAD_TARIFF

SQLACC_DOWNLOAD_STOCK

SQL Accounting Integration Project

This is the place to configure what and how's the integration tasks to be performed. Create a project, fill up the SQL Accounting Authentication Information, then populate the tasks and choose the desired one.

You can disable the task and disallowed it from auto run (Configured in Process Manager)

Question: Where do I get the SQL Accounting Authentication Information?

By default the created database would be located at "C:\eStream\SQLAccounting\DB" with those file extension *.FDB

DCF Full Name, a default configuration file for SQL Accounting. Usually located at "C:\eStream\SQLAccounting\Share" with the file extension .DCF

Database Name, the SQL Accounting database file name (Eg: ACC-0001.FDB)

User Name, default would be ADMIN

Password, default would be admin

Note: For the integration work well in the case if you're change of user name, please make sure the security access right in SQL Accounting to be fully granted.

Note: Test the connectivity to SQL Accounting after input of authentication info to ensure the integration work well later on.

You should be able to see the result as follow if able to connect successfully.

Hint: Sometimes for testing purpose, the function [Dynamic Query from SQL Accounting] allowed to write query (SQL statement) to retrieve result adhoc from SQL Accounting's database. You may also export the result into Excel for further reference or study purpose.

The above query get the stocks information from SQL Accounting database

Sometimes we need to know what are those available tables name in order for us to query from database. You may use query below:

select rdb$relation_name

from rdb$relations

where rdb$view_blr is null

and (rdb$system_flag is null or rdb$system_flag = 0)

Reference for query above: http://www.firebirdfaq.org/faq174/

You can see there are 186 tables in SQL Accounting database.

Full table list are downloadable from attached Excel file SQLAccounting_TableList.xlsx

Manually Perform Integrated Tasks using Task Manager Function

DynaMod allowed you to perform SQl Accounting Integration Task manually as well by using function in More - 1. Task Manager

The list of manual run-enable tasks as listed in new screen:

Choose the desired task, then press the button [Run Selected Task by Last Sync Date], a confirmation message will be prompted upfront to continue.

Once the process completed, it will marked as Manual run by current login user for status whether Failed or Success. The error log would be indicated and saved as well.

Automated integration tasks using DynaMod Process Manager

In DynaMod Process Manager 2019.1 Build 0009, those pre-defined tasks were added to the list for configuration

SQL Accounting Integration Task Configuration

Press the button [Setup Connection], below screen would shown: