SQL Accounting Linking

From eStream Software

Introduction

We had 3 Methods to Integrate/Link to SQL Accounting

SQL Accounting GST Program

Database

Sample DB

Empty DB

  1. At the Login Screen Click 2 dot Button
  2. Click Create New Database
  3. Follow the wizard
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg

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

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

Table/Fields Detail

Normal Used Table Name

  • SL_IV - Sales Invoice
  • SL_CS - Cash Sales
  • SL_CN/AR_CN - Credit Note
  • SL_DN - Debit Note
  • AR_PM - Payment

Posting Information

  • Detail Data should Group by Stock Group/Category & TaxType (ZRL or SR)
  • 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

Extra Notes

  • Both Full Tax & Simplified IV can use same Debtor Code as Not meantion required Customer GST ID
  • Gov. 5 cents Rounding - no Tax
  • Deposit for Non Refundable can use Customer Payment (AR_PM)
  • 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


----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • ALWAYS do backup the database 1st before import
----------------------------------------------------------------------------------------------------------------------------------------------------