Line 254: | Line 254: | ||
! Field !! Value | ! Field !! Value | ||
|- | |- | ||
| Tax || '' | | Tax || ''''''' | ||
|- | |- | ||
| TaxInclusive || 0 | | TaxInclusive || 0 |
Revision as of 04:14, 29 November 2016
Introduction
We had 4 Methods to Integrate/Link to SQL Accounting
- SDK Live (Recommended)
- SQL XML Import
- SQL Text Import
- SQL Acc XLS n MDB Import
SQL Accounting GST Program
May get it from http://www.estream.com.my/webstore/products
Database
Sample DB
- http://www.estream.com.my/document/SQLLink/GST-SampleDB-20141203.zip
- http://www.estream.com.my/document/SQLLink/Video-RestoreDB.zip
Empty DB
- At the Login Screen Click 2 dot Button
- Click Create New Database
- Follow the wizard
|
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
- Tick(Select) the option
- Use Cash Sales No. for Payment Received
- Untick(UnSelect) 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. Click Menu: Tools | Maintain User... & Untick the Access Right for
- Prompt Replace Unit Price Dialog under the Group : Sales
03. Must be had/valid in SQL Accounting
Field | Location/Form |
---|---|
Customer Code | Customer => Maintain Customer |
Supplier Code | Supplier => Maintain Supplier |
Terms Code | Tools => Maintain Terms |
Project Code | Tools => Maintain Project |
Sales/Purchase Account Code | GL => Maintain Account |
Sales/Purchase Return Account Code | GL => Maintain Account |
Cash/Bank Account Code | Tools => Maintain Payment Method (Created from Maintain Account) |
Agent Code | Tools => Maintain Agent |
Area Code | Tools => Maintain Area |
Currency Code | Tools => Maintain Currency |
Tax Code | Tools => Maintain Tax (if had GST) |
04. Below is Optional (i.e. if had post/import item code to SQL Accounting)
Field | Location/Form |
---|---|
Item Code | Stock => Maintain Stock Item |
Location Code | Stock => Maintain Location |
Batch Code | Stock => Maintain Batch |
|
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
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)
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
- - TX-ES (Replace TX-N43)
- - TX-RE
- 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
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 |
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
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.
Can I do like this, DR Bank/Cash In hand & CR Sales Account?
- No. The correct Double Entry is as following
- - Invoice
- DR Debtor
- CR Sales Account
- - Payment Received
- DR Bank/Cash In Hand
- CR Debtor
- - Invoice
Can I use just ADMIN ID to Import/Posting?
- No. ADMIN had full Access Right & if had problem user might had problem trace back who doing the importing...
- It adviceable to create Another ID with less Access Right
Can SQL Accounting & External Program(eg POS) Import/Posting doing Stock Control in same time?
- Yes, but we Recommended External Program(eg POS) to do all the Stock Control if the External Program(eg POS)
- able to do all the report Required by the User.
- Reason :
- 01. Faster Import time
- 02. Timing issue as in SQL Acc the Qty is ALWAYS outdated cause real Stock In/Out is from the External Program(eg POS)
- 03. Data redundancy as 2 same data for different system
- 04. Avoid wasting time on Sync correctness stock data between 2 system
Is there any setting for transaction without GST?
- No there is no setting. All you had to do is to set the following field to
Field | Value |
---|---|
Tax | '' |
TaxInclusive | 0 |
TaxAmt | 0 |
- Need assistance? May email to support@sql.com.my