SQL Accounting Linking

Introduction

We had 4 Methods to Integrate/Link to SQL Accounting

SQL Accounting GST Program

May get it from http://www.estream.com.my/webstore/products

Database

Sample DB

Empty DB

At the Login Screen Click 2 dot Button
Click Create New Database
Follow the wizard

----------------------------------------------------------------------------------------------------------------------------------------------------

Default UserName & Password is ADMIN

----------------------------------------------------------------------------------------------------------------------------------------------------

Things To Consider Before Import/Post

Before wanted to Import/Post to SQL Accounting Database, below information/setting must set in the SQL Accounting

01. Click Tools | Options... | Customer & Untick the option

One Cent Different Rounding (Local Currency Fields) for all AR/SL Documents (Recommended)
5 Cents Rounding (Sales Invoice)
5 Cents Rounding (Cash Sales)

02. Must be had/valid in SQL Accounting

Customer Code => Maintain Customer
Supplier Code => Maintain Supplier
Terms Code => Maintain Terms
Project Code => Maintain Project
Sales/Purchase Account Code => Maintain Account
Sales/Purchase Return Account Code => Maintain Account
Cash/Bank Account Code => Maintain Payment Method (Created from Maintain Account)
Agent Code => Maintain Agent
Area Code => Maintain Area
Currency Code => Maintain Currency

03. Below is Optional (i.e. if had post/import item code to SQL Accounting)

Item Code => Maintain Stock Item
Location Code => Maintain Location
Batch Code => Maintain Batch

----------------------------------------------------------------------------------------------------------------------------------------------------

ALWAYS do backup the database First before Import/Post to SQL Accounting

----------------------------------------------------------------------------------------------------------------------------------------------------

[top]

Table/Fields Detail

Updated 19 Aug 2015
http://www.estream.com.my/document/SQLLink/DetailSpec.xlsx

Normal Used Table Name

SL_IV - Sales Invoice
SL_CS - Cash Sales
SL_CN/AR_CN - Credit Note
SL_DN - Sales Debit Note
AR_PM - Customer Payment
AR_IV - Customer Invoice
AR_DN - Customer Debit Note

[top]

Posting Information

Detail Data should Group by Stock Group/Category & TaxType (ZRL or SR) & Itemcode (if wanted import itemcode)
Eg. Cafe can be group by

- Food - SR

- Food - ZRL

- Beverage

- Service Charges

- Rounding

Eg. Today got 10 transactions

- 8 is Simplified Invoice - Group as 1 Doc No - POS-00001

- 2 is Full Tax Invoice/Credit Sales Invoice - 1 by 1 in - POS-00002, POS-00003

All can post to SL_CS & AR_PM
Below is example Today Total Simplified Invoice Sales is RM1000

Method 1 (Recommended)

It will be had 4 transactions

 1. RM1000 - Post to SL_CS & P_PaymentMethod field is empty
 2. RM700 by Cash - Post to AR_PM
 3. RM200 by MasterCard - Post to AR_PM
 4. RM100 by CreditCard - Post to AR_PM

Pros : Easy to Edit or Delete the transactions
Cons : Many Posting document

Method 2

It will be had 3 transactions

 1. RM700 by Cash - Post to SL_CS P_PaymentMethod field
 2. RM200 by MasterCard - Post to AR_PM
 3. RM100 by CreditCard - Post to AR_PM

Pros : Less Posting document
Cons :

Not Easy to Edit or Delete the transactions
Cash Sales OR number unable to override (i.e. System Auto set/assign)

[top]

Extra Notes

Both Full Tax & Simplified IV can use same Debtor Code as Not mention required Customer GST ID
Government 5 cents Rounding Mechanism - no Tax
Deposit for Non Refundable can use Customer Payment (AR_PM) & set NONREFUNDABLE field to 1
Doc Disc should proportion by sub total amt for mix tax code (See Cash Sales POS4)

Example

- Doc Discount = 30

- Sub Total for SR = 849.53 => Disc ((849.53/1,182.51) * 30) = 21.55

- Sub Total for ZR = 332.98 => Disc ((332.98/1,182.51) * 30) = 8.45

Mixed Supplies Tax Code

- ES

- TXN43

- TXRE

Realise Bad Debts Use CN as Normal

- System will contra the provision bad debts done at GST-03 by 6 mth bad debts

- Make sure Knock the actual Bad debts Invoice

FAQ

Prompt Invalid class string, ProdID: "SQLAcc.BizApp" Error while try to link to SQL Accounting.

Login SQL Accounting
Click Tools | Options
Click Register & follow wizard
Exit SQL Accounting
Login SQL Accounting
Try run you application to link again

If still prompt after above steps

Uninstall SQL Accounting
Stop Anti Virus
Reinstall SQL Accounting
Run the above steps again

Prompt Access Violation when import.

This happen due to

The fieldname is not match with SQL Accounting (eg SQLAcc fieldname UDF_Width yr had call for UDF_Weight)
Old version of SQL Acc Import program, try update the SQL Acc Import program
The fieldname had the empty space at the beginning and/or end (norm happen if import from Excel) eg 'DocNo ' or ' DocNo' instead of 'DocNo'

Why after import the DO still had outstanding even the Invoice had imported?

Yes all import will loss the Transfer status (i.e. if import DO & IV will treat as different) except using SDK import with condition

04. Which field should I map/insert to for Credit Note & Debit Note for Invoice Number, Invoice Date & Reason?

In Generally(Recommended) you can use below field (All in Header Field)

SQL Accounting Field	Mapping Field	Description
DocNoEx	Invoice Number	Field Size : 20
DocRef1	Invoice Date	Field Size : 25
Description	Reason	Field Size : 200

If you wanted posted in Detail Field also can

SQL Accounting Field	Mapping Field	Description
Remark1	Invoice Number	Field Size : 200
Remark2	Invoice Date	Field Size : 200
Description2	Reason	Field Size : 200

05. Can I post/import to SQL Accounting in Monthly Basis?

No for GST Era. It advice able to do Daily Basis posting/import as user had more time to verify if posting/import had Error/Problem

Below is the proper steps to do posting/import

Backup
Post/Import to SQL Accounting
Compare Listing in SQL Accounting with the POS/External Program Listing

06. In my system had multiple level document discount, how to post to SQL Accounting?

You can insert 2 or more rows(depend how many level you had) of item as negative unitprice.

But as mention above Extra Notes proportion by sub total amt for mix tax code.

07. Can I do like this, DR Bank/Cash In hand & CR Sales Account?

No. The correct Double Entry is as follwoing

- Invoice

DR Debtor

CR Sales Account

- Payment Received

DR Bank/Cash In Hand

CR Debtor

Need assistance? May email to support@sql.com.my

[top]