SQL Accounting Linking

Introduction

We had 4 Methods to Integrate/Link to SQL Accounting

SDK Live (Recommended - For two way communication)
SQL Acc XLS n MDB Import - XLS, XLSX, Json & CSV/TXT File (Format 2 - Bar(|),Comma(,),Semi Comma(;),Tilde(~))
SQL XML Import - XML File
SQL Text Import - CSV/TXT File (Format 1 - Semi Comma (;) Only)

SQL Accounting GST/SST & Firebird Program

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

[top]

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 Menu: View

Untick(UnSelect) the option

Command Navigator
Tabbed Docking
Allow Multiple Windows
Enable Windows Task Bar

02. Click Menu: Tools | Options... | Customer

Tick(Select) the option

Use Cash Sales No. for Payment Received

Untick(UnSelect) the option

Perform Tax / Local Amount Rounding
5 Cents Rounding (Sales Invoice)
5 Cents Rounding (Cash Sales)

03. Click Menu: Tools | Options... | Miscellaneous

Untick(UnSelect) the option

Prompt Negative Stock Quantity Dialog Box
Prompt Duplicate Cheque Number

04. Click Menu: Tools | Maintain User... | New

Create New User (eg POS)

05. Click Menu: Tools | Maintain User... | Access Right

Tick (Select) the option

Override Customer Credit Control under the Group : Customer
Override Sales Min/Max Price under the Group : Sales
Override Acceptable Transaction Date under the Group : Tools
Save on Negative Quantity under the Group : Stock

Untick (UnSelect) the option

Under the Group : Sales

Prompt Replace Unit Price Dialog
Show Payment / Change Dialog in Cash Sales

06. Must be had/valid in SQL Accounting

Field	In SQL Accounting
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)

07. 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

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

[top]

Table/Fields Detail

Updated 5 Jun 2024
DetailSpec

Normal Used Table/Biz Object Name

---Sales Side---

Biz Object	Description
AR_Customer	Maintain Customer
SL_IV	Sales Invoice
SL_CS	Cash Sales
SL_CN	Sales Credit Note
SL_DN	Sales Debit Note
AR_PM	Customer Payment
AR_IV	Customer Invoice
AR_DN	Customer Debit Note
AR_CN	Customer Credit Note

---Purchase Side---

Biz Object	Description
AP_Supplier	Maintain Supplier
PH_PI	Purchase Invoice
PH_CP	Cash Purchase
PH_SC	Purchase Return
PH_SD	Purchase Debit Note
AP_SP	Supplier Payment
AP_PI	Supplier Invoice
AP_SD	Supplier Debit Note
AP_SC	Supplier Credit 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_DocNo, P_PaymentMethod field is empty & P_Amount, P_PaidAmount field is 0)
 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/GST Code
Deposit for Non Refundable can use Customer Payment (AR_PM) & set NONREFUNDABLE field to 1

- Default is SR

- Will auto reverse once it being Knock-Off

Doc Disc should proportion by sub total amt for mix tax code (See Cash Sales POS-DocDisc)

Example

- Doc Discount = 10% of Document Discount

- Sub Total for SR = 150.24 => Disc (150.24 * 10%) = 15.02

- Sub Total for ZR = 988.88 => Disc (988.88 * 10%) = 98.89

0r

- Doc Discount = 113.91

- Sub Total for SR = 150.24 => Disc ((150.24/1139.12) * 113.91) = 15.02

- Sub Total for ZR = 988.88 => Disc ((988.88/1139.12) * 113.91) = 98.89

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

For Purchase Invoice(PI) MUST 1 by 1 post in (i.e. can't Group multi PI in 1 PI)

FAQ

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

Login SQL Accounting
Click Tools | Options | General
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/SST 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/Debtor & CR Sales Account using Journal?

No. The correct Double Entry is as following

- Invoice/Cash Sales

DR Debtor

CR Sales Account

- Payment Received

DR Bank/Cash In Hand

CR Debtor

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

Can SQL Acc accept negative amount?

Yes if for Item Row Amount

No all document amount(DocAmt field) must not below 0

Is there any setting for transaction without Tax?

No there is no special setting. All you had to do is to set the following field to

Field	Value
Tax	''
TaxInclusive	0
TaxAmt	0

How to post If supplier given is Simplified Invoice but exceed RM500?

It advisable to ask the user to Request Full Tax Invoice from their supplier as user might pay double Tax due to add back for non claimable amount to the company profit by their Audit/Accountant.

Below is example(suggest) how we post to Simplified Invoice from supplier

You also can use the TX-NC tax code for amount exceed RM500

[top]

Prompt `Could not convert variant of type (Null) into type (Integer)` error while try to post (eg Invoice)

Make sure the database you login is had Started the GST.

[top]

How to post SR with 6% for issue document after 01 Jun 2018?

There are 2 solutions

Solution 1

Add TaxRate Field Script/Code
... lDtl.FindField('TAX').AsString := 'SR'; lDtl.FindField('TAXRATE').AsString := '6%'; ...

Solution 2

Create New Tax SR tax code with 6%(eg SR6) in SQL Accounting

[top]

Is there any changes in Linking for the SST?

GST to No SST

May follow Non GST

GST to SST

There is no much changes. Just treat is like normal GST only different is the Tax Code

Tax Code	Description	Rate	Required Tariff Code
ST5	Sales Tax	5%	Y
ST	Sales Tax	10%	Y
SV	Service Tax	6% (8% From 01 Mar 2024)	Y
STE	Sales Tax Exempted		N

New Field added

Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
TARIFF	String	20	C		Tariff or HS Code Mandatory - If posting to - GL Payment Voucher & GL Official Receipt - Customer Invoice, Debit Note & Credit Note - Supplier Invoice, Debit Note & Credit Note - All Sales & Purchase Module without Item Code No Required - If posting Sales & Purchase with Item Code

[top]

How to SQL Accounting handle if had Discount/Cash Voucher?

You can treat the Discount/Cash Voucher as Part of item row but is in Negative UnitPrice

Begin 11 Oct 2019 the Company ROC/BRN will increase to 12 digit characters. Any changes in Linking?

Yes if user is upgrade to Version 875.782 & above.

Table Affected

- SY_PROFILE => File | Company Profile

- AR_CUSTOMER => Customer | Maintain Customer

- AP_SUPPLIER => Supplier | Maintain Supplier

Field Changes

- Original Name REGISTERNO change to BRN

- New Field BRN2

- New Field Size for both Fields 30

SSM Announcement or PDF

Is there any changes in Linking for the E-Invoicing?

New Field Added
Available in Version 5.2024.983.848 & above

Maintain Customer & Maintain Supplier

Field Name	Field Type	Field Size	Remarks
TIN	String	14	Company Tax Register Number
IDTYPE	Integer		0 - Empty 1 - Reg No 2 - NRIC 3 - Passport 4 - ARMY
IDNO	String	20	This field will depend on the ID Type selected
TOURISMNO	String	17	This is only applicable to tourism tax registrant, which may consist of hotel operators and online travel operators
SIC	String	10	Malaysia Standard Industrial Classification (MSIC) Codes https://sdk.myinvois.hasil.gov.my/codes/#msic-codes Available in Version (TBA) Maintain Supplier Only
POSTCODE	String	10	The identifier for an addressable group of properties according to the relevant postal service
CITY	String	50	The common name of the city, town or village, where the Customer/Supplier address is located.
STATE	String	50	The state of a country. https://sdk.myinvois.hasil.gov.my/codes/state-codes/
COUNTRY	String	2	A code that identifies the country. May refer to Country List Here

Maintain Stock Item

Field Name	Field Type	Field Size	Remarks
IRBM_CLASSIFICATION	String	3	Category of products or services being billed as a result of a commercial transaction. More than 1 classification codes can be added for goods / services included in the e-Invoice. https://sdk.myinvois.hasil.gov.my/codes/classification-codes/

Available in Version (TBA)

Sales & Purchase Data Entry - Header

Field Name	Field Type	Field Size	Remarks
POSTCODE	String	10	The identifier for an addressable group of properties according to the relevant postal service
CITY	String	50	The common name of the city, town or village, where the Customer/Supplier address is located.
STATE	String	50	The state of a country. https://sdk.myinvois.hasil.gov.my/codes/state-codes/
COUNTRY	String	2	A code that identifies the country. May refer to Country List Here

Sales & Purchase Data Entry - Detail

Field Name	Field Type	Field Size	Remarks
Coming Soon...	String	0

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

Introduction

SQL Accounting GST/SST & Firebird Program

Database

Sample DB

Empty DB

Things To Consider Before Import/Post

Table/Fields Detail

Normal Used Table/Biz Object Name

Posting Information

Method 1 (Recommended)

Method 2

Extra Notes

FAQ

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

Prompt Access Violation when import.

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

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

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

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

Can I do like this, DR Bank/Cash In hand/Debtor & CR Sales Account using Journal?

Can I use just ADMIN ID to Import/Posting?

Can SQL Accounting & External Program(eg POS) Import/Posting doing Stock Control in same time?

Can SQL Acc accept negative amount?

Is there any setting for transaction without Tax?

How to post If supplier given is Simplified Invoice but exceed RM500?

Prompt Could not convert variant of type (Null) into type (Integer) error while try to post (eg Invoice)

How to post SR with 6% for issue document after 01 Jun 2018?

Is there any changes in Linking for the SST?

How to SQL Accounting handle if had Discount/Cash Voucher?

Begin 11 Oct 2019 the Company ROC/BRN will increase to 12 digit characters. Any changes in Linking?

Is there any changes in Linking for the E-Invoicing?

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

Prompt `Access Violation` when import.

Prompt `Could not convert variant of type (Null) into type (Integer)` error while try to post (eg Invoice)