SQL Acc F&N Import: Difference between revisions

From eStream Software
(86 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Introduction==
==Introduction==
Is External Shareware Program which to import F&N Text file to  
Is External Shareware Program which to import F&N Text file to  
* Sales Invoice
* Sales Credit Note
Export E-Invoice to F&N - Available in Build 18 & Above
* Sales Invoice
* Sales Invoice
* Sales Credit Note
* Sales Credit Note
Line 8: Line 12:


==F & N Specification==
==F & N Specification==
* 17 Jun 2015
* 17 Jun 2015 - ''ESD soft-drink''
* http://www.estream.com.my/downloadfile/Fairy/FN-Acct_Integration_Reference.zip
* https://download.sql.com.my/customer/Fairy/FN-Acct_Integration_Reference-20150617.zip
 
* 15 Aug 2016 - ''ESD Online''
* https://download.sql.com.my/customer/Fairy/FN-Acct_Integration_Reference-20160815.zip
 
<div style="float: right;">  [[#top|[top]]]</div>


==Import Program==
==Import Program==
* Version (2.1.0.9) - 26 Jun 2015
* Version (3.5.0.20) - 10 Oct 2024
* http://www.estream.com.my/downloadfile/Fairy/SQLAccFNImp-setup.exe
* Evaluation Limit : 50 Records per file
* MD5 : B33D2565876D2E61DA90D682DA50B1E2
* https://download.sql.com.my/customer/Fairy/SQLAccFNImp-setup.exe
* MD5 : 0C5BC371CCCD2A67E3AE92F9B7067FCA


===History New/Updates/Changes===
===History New/Updates/Changes===
--Build 20--
* Fixed Export E-IV for CN apply error.
* Fixed Export E-IV dateTimeValidated formatting not correct.
--Build 19--
* Enable Import E-Invoice Info from FnN.
* Fixed Export E-IV header delimiter not correct.
--Build 18--
* Upgrade to Version 3.6.
* Add FnN to Header Field CC as indicator Import from FnN.
* Remove L tax code from import.
* Set Default Open FnN ESD Online Files format.
* Add E-Invoicing Export function.
--Build 17--
* Fixed Use UOMList not working.
--Build 16--
* Add Import Remark to DeliveryTerm Field for CSV format.
--Build 15--
* Add Auto Set to Outlet_ID2 if Debtor_Code2 is empty for CSV format.
* Add Get User Guide Option.
--Build 14--
* Upgrade to Version 3.5.
* Enable support Running In Windows Limited User.
* Fixed Verify Detail Error not untick the Master when itemcode not found.
* Fixed Verify Not Check Item Code UOM.
* Fixed Scrollbar not visible.
--Build 13--
* Upgrade to Version 3.4.
* Add Project & Location Lookup for Posting.
--Build 12--
* Add Outlet_ID2 to shown in Grid.
--Build 11--
* Upgrade to D25 Compiler.
* Upgrade to Version 3.3.
* Remove Import Debtor_Name2.
--Build 10--
* Upgrade to XE10 Compiler.
* Upgrade to Version 3.2
* Add Link to SQL Acc. File | Run.
* Add OutletID=Maintain Customer Code
* Add ESD Online - CSV Format
--Build 9--
--Build 9--
* Fix unable to import docamt is 0 even is no DS Code.
* Fix unable to import docamt is 0 even is no DS Code.
Line 27: Line 88:
--Build 7--
--Build 7--
* Fix CN Get File Error.
* Fix CN Get File Error.
<div style="float: right;">  [[#top|[top]]]</div>


==Todo==
==Todo==
* OutletID=Maintain Customer Code
*


==Field Mapping - Header==
==Field Mapping - Header==
* As at 17 Jun 2015 - ''ESD soft-drink'' - Fixed Length Format
{| class="wikitable" style="margin: 1em auto 1em auto;"
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
|-
! FnN Field !! Field Position !! SQL Accounting Field
! FnN Field !! Field Position !! Field Size !! SQL Accounting Field
|-
|-
| Invoice_Date || style="text-align: center;" | 1 || DocDate
| Invoice_Date || style="text-align: center;" | 1 || style="text-align: center;" | 10 || DocDate
|-
|-
| Outlet_ID || style="text-align: center;" | 71|| Maintain Customer Remark
| Invoice_ID || style="text-align: center;" | 11 || style="text-align: center;" | 10 || DocNo
|-
|-
| Invoice_ID || style="text-align: center;" | 11  || DocNo
| Gross Amount || style="text-align: center;" | 41 || style="text-align: center;" | 10 || DocAmt
|-
|-
| Gross Amount || style="text-align: center;" | 41 || DocAmt
| Outlet_ID || style="text-align: center;" | 71|| style="text-align: center;" | 10 || Maintain Customer Remark or Code
|}
 
* As at 15 Aug 2016 - ''ESD Online'' - CSV Format
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! FnN Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| Invoice_Date || style="text-align: center;" | 0 || style="text-align: center;" | 10 || DocDate
|-
| Invoice_ID || style="text-align: center;" | 1 || style="text-align: center;" | 15 || DocNo
|-
| Total_Gross_Amount || style="text-align: center;" | 4 || style="text-align: center;" | 10 || DocAmt
|-
| Debtor_Code2 || style="text-align: center;" | 9 || style="text-align: center;" | 10 || Customer Code
|-
| <del>Debtor_Name2</del> || style="text-align: center;" | <del>10</del> || style="text-align: center;" | <del>150</del> ||
* <del>Customer Name</del>
* Not valid for Build 11 & above
|-
| Outlet_ID2 || style="text-align: center;" | 11|| style="text-align: center;" | 10 || Display Only
|-
| Remark || style="text-align: center;" | 12|| style="text-align: center;" | 200 ||
* Delivery Term
* Available in Build 16 & above
|}
|}


==Field Mapping - Detail==
==Field Mapping - Detail==
* As at 17 Jun 2015 - ''ESD soft-drink'' - Fixed Length Format
{| class="wikitable" style="margin: 1em auto 1em auto;"
! scope="col" style="width: 450px;" | For Sales Invoice
! scope="col" style="width: 450px;" | For Sales Credit Note
|- style="vertical-align: top;"
|
:{| class="wikitable"
|-
! FnN Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| Item_ID || style="text-align: center;" | 14 || style="text-align: center;" | 10 || Itemcode or <br> Maintain Item Code Note
|-
| Unit or <br> SubUnits || style="text-align: center;" | 74 or <br>84 || style="text-align: center;" | 10 || Qty
|-
| Unit_Price || style="text-align: center;" | 94 || style="text-align: center;" | 10 || UnitPrice
|-
| Discount || style="text-align: center;" | 104 || style="text-align: center;" | 10 || Disc
|-
| Tax_Code || style="text-align: center;" | 139 || style="text-align: center;" | 5 || Tax
|-
| SubTotal_Tax_Amount || style="text-align: center;" | 149 || style="text-align: center;" | 10 || TaxAmt
|-
| SubTotal_Net_Amount || style="text-align: center;" | 189 || style="text-align: center;" | 10 || Amount
|}
||
:{| class="wikitable"
|-
! FnN Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| Item_ID || style="text-align: center;" | 14 || style="text-align: center;" | 10 || Itemcode or <br> Maintain Item Code Note
|-
| Unit or <br> SubUnits || style="text-align: center;" | 74 or <br>84 || style="text-align: center;" | 10 || Qty
|-
| Unit_Price || style="text-align: center;" | 94 || style="text-align: center;" | 10 || UnitPrice
|-
| Discount || style="text-align: center;" | 104 || style="text-align: center;" | 10 || Disc
|-
| Tax_Code || style="text-align: center;" | 139 || style="text-align: center;" | 5 || Tax
|-
| SubTotal_Tax_Amount || style="text-align: center;" | 149 || style="text-align: center;" | 10 || TaxAmt
|-
| SubTotal_Net_Amount || style="text-align: center;" | 259 || style="text-align: center;" | 10 || Amount
|-
| Reference<br>Reference_date<br>Reference_reason || style="text-align: center;" | 179<br>189<br>199 || style="text-align: center;" | 10<br>10<br>50 || Description2
|}
|}
* As at 15 Aug 2016 - ''ESD Online'' - CSV Format
{| class="wikitable" style="margin: 1em auto 1em auto;"
! scope="col" style="width: 450px;" | For Sales Invoice
! scope="col" style="width: 450px;" | For Sales Credit Note
|- style="vertical-align: top;"
|
:{| class="wikitable"
|-
! FnN Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| Item_ID || style="text-align: center;" | 2 || style="text-align: center;" | 10 || Itemcode or <br> Maintain Item Code Note
|-
| Unit or <br> SubUnits || style="text-align: center;" | 4 or <br>5 || style="text-align: center;" | 10 || Qty
|-
| Unit_Price || style="text-align: center;" | 6 || style="text-align: center;" | 10 || UnitPrice
|-
| Discount || style="text-align: center;" | 7 || style="text-align: center;" | 10 || Disc
|-
| Tax_Code || style="text-align: center;" | 11 || style="text-align: center;" | 5 || Tax
|-
| SubTotal_Tax_Amount || style="text-align: center;" | 13 || style="text-align: center;" | 10 || TaxAmt
|-
| SubTotal_Net_Amount || style="text-align: center;" | 17 || style="text-align: center;" | 10 || Amount
|}
||
:{| class="wikitable"
|-
! FnN Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| Item_ID || style="text-align: center;" | 2 || style="text-align: center;" | 10 || Itemcode or <br> Maintain Item Code Note
|-
| Unit or <br> SubUnits || style="text-align: center;" | 4 or <br>5 || style="text-align: center;" | 10 || Qty
|-
| Unit_Price || style="text-align: center;" | 6 || style="text-align: center;" | 10 || UnitPrice
|-
| Discount || style="text-align: center;" | 7 || style="text-align: center;" | 10 || Disc
|-
| Tax_Code || style="text-align: center;" | 11 || style="text-align: center;" | 5 || Tax
|-
| SubTotal_Tax_Amount || style="text-align: center;" | 13 || style="text-align: center;" | 10 || TaxAmt
|-
| SubTotal_Net_Amount || style="text-align: center;" | 19 || style="text-align: center;" | 10 || Amount
|-
| Reference<br>Reference_date<br>Reference_reason || style="text-align: center;" | 16<br>20<br>17 || style="text-align: center;" | 15<br>50<br>10 || Description2
|}
|}


<div style="float: right;">  [[#top|[top]]]</div>


==Setting==
==Setting==
===In SQL Accounting===
===In SQL Accounting===
Make sure this option is Tick under '''Tools | Options | Customer'''
01. May refer to Point 2 at [[SQL_Accounting_Linking#Things_To_Consider_Before_Import.2FPost|Things To Consider Before Import/Post]]<br />
* One Cent Different Rounding (Local Currency Fields) for all AR/SL Documents (Recommended)
02. Make sure this option is Tick under '''Tools | Options | Customer'''
* Perform Tax/Local Amount Rounding


===In F&N Import===
===In F&N Import===
Line 59: Line 242:
! Options !! Description
! Options !! Description
|-
|-
| UOMList || List of UOM available in F&N
| UOMList || List of UOM available in F&N (Normally is CTN & PCS)
|-
|-
| DocNoAsDocNo ||  
| DocNoAsDocNo ||  
Line 72: Line 255:
* 0 : F&N ItemCode will check against UOMList  
* 0 : F&N ItemCode will check against UOMList  
* 1 : F&N Itemcode UOM same as SQLAcc Itemcode UOM (Recommended)
* 1 : F&N Itemcode UOM same as SQLAcc Itemcode UOM (Recommended)
|-
| OutletIDAsCompCode ||
* For ESD soft-drink - Fixed Length Format Only
* 0 : F&N Outlet ID will check against SQL Accounting | Maintain Customer | Remark Field
* 1 : F&N Outlet ID same as SQLAcc Customer Code (Recommended)
|}
|}


* F&N '''Outlet ID''' should be enter in SQL Accounting | Maintain Customer | Remark Field
* <s>F&N '''Outlet ID''' should be enter in SQL Accounting | Maintain Customer | Remark Field</s> - Not valid for Build 10 & above
 
<div style="float: right;">  [[#top|[top]]]</div>


==Steps==
==Steps==
===Import Invoice/Credit Note===
Below steps is example to import Sales Invoice<br />
Below steps is example to import Sales Invoice<br />
1. Click '''Sales | Invoice...'''<br />
01. Click '''Sales | Invoice...'''<br />
2. Click '''Get File''' button & system will prompt 2 dialog like below<br />
02. Click '''Get File''' button & system will prompt 2 dialog like below<br />
[[File:FnN-01.jpg|center]]
[[File:FnN-01.jpg|center]]
3. Select the F&N Invoice Text Header File
03. Click '''Files of Type''' & select the FnN File Format type<br />
04. Select the F&N Invoice Text Header File
[[File:FnN-02.jpg|center]]
[[File:FnN-02.jpg|center]]
4. Select the F&N Invoice Text Detail File
05. Select the F&N Invoice Text Detail File
[[File:FnN-03.jpg | 700px|center]]
[[File:FnN-03.jpg | 700px|center]]
5. Click '''Verify''' button to check the data with SQL Accounting Data<br />
06. Click '''Verify''' button to check the data with SQL Accounting Data<br />
6. Click '''Post To A/c''' button if confirm all is ok to post to SQL Accounting<br />
07. Select '''Project''' & '''Location''' (if Required)<br />
08. Click '''Post To A/c''' button if confirm all is ok to post to SQL Accounting<br />


:::----------------------------------------------------------------------------------------------------------------------------------------------------
:::----------------------------------------------------------------------------------------------------------------------------------------------------
Line 96: Line 289:
|}
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
:::----------------------------------------------------------------------------------------------------------------------------------------------------
<div style="float: right;">  [[#top|[top]]]</div>
===Export E Invoice/Credit Note===
[[File:FnN-04.jpg|700px|center]]
:01. Click '''Export E-Invoice Status...'''
:02. Select Range Date (E-Invoice Submission date)
:03. Select DocType (eg Invoice)
:04. Click '''Apply'''
:05. Click '''Export'''
<div style="float: right;">  [[#top|[top]]]</div>


==FAQ==
==FAQ==

Revision as of 04:55, 10 October 2024

Introduction

Is External Shareware Program which to import F&N Text file to

  • Sales Invoice
  • Sales Credit Note

Export E-Invoice to F&N - Available in Build 18 & Above

  • Sales Invoice
  • Sales Credit Note

Limitation

  • TaxCode with DS will not import.

F & N Specification

Import Program

History New/Updates/Changes

--Build 20--

  • Fixed Export E-IV for CN apply error.
  • Fixed Export E-IV dateTimeValidated formatting not correct.

--Build 19--

  • Enable Import E-Invoice Info from FnN.
  • Fixed Export E-IV header delimiter not correct.

--Build 18--

  • Upgrade to Version 3.6.
  • Add FnN to Header Field CC as indicator Import from FnN.
  • Remove L tax code from import.
  • Set Default Open FnN ESD Online Files format.
  • Add E-Invoicing Export function.

--Build 17--

  • Fixed Use UOMList not working.

--Build 16--

  • Add Import Remark to DeliveryTerm Field for CSV format.

--Build 15--

  • Add Auto Set to Outlet_ID2 if Debtor_Code2 is empty for CSV format.
  • Add Get User Guide Option.

--Build 14--

  • Upgrade to Version 3.5.
  • Enable support Running In Windows Limited User.
  • Fixed Verify Detail Error not untick the Master when itemcode not found.
  • Fixed Verify Not Check Item Code UOM.
  • Fixed Scrollbar not visible.

--Build 13--

  • Upgrade to Version 3.4.
  • Add Project & Location Lookup for Posting.

--Build 12--

  • Add Outlet_ID2 to shown in Grid.

--Build 11--

  • Upgrade to D25 Compiler.
  • Upgrade to Version 3.3.
  • Remove Import Debtor_Name2.

--Build 10--

  • Upgrade to XE10 Compiler.
  • Upgrade to Version 3.2
  • Add Link to SQL Acc. File | Run.
  • Add OutletID=Maintain Customer Code
  • Add ESD Online - CSV Format

--Build 9--

  • Fix unable to import docamt is 0 even is no DS Code.
  • Fix Status no dropdown list.
  • Fix Detail not Verify.

--Build 8--

  • Fix Get File Error when record is without tax code.

--Build 7--

  • Fix CN Get File Error.

Todo

Field Mapping - Header

  • As at 17 Jun 2015 - ESD soft-drink - Fixed Length Format
FnN Field Field Position Field Size SQL Accounting Field
Invoice_Date 1 10 DocDate
Invoice_ID 11 10 DocNo
Gross Amount 41 10 DocAmt
Outlet_ID 71 10 Maintain Customer Remark or Code
  • As at 15 Aug 2016 - ESD Online - CSV Format
FnN Field Field Position Field Size SQL Accounting Field
Invoice_Date 0 10 DocDate
Invoice_ID 1 15 DocNo
Total_Gross_Amount 4 10 DocAmt
Debtor_Code2 9 10 Customer Code
Debtor_Name2 10 150
  • Customer Name
  • Not valid for Build 11 & above
Outlet_ID2 11 10 Display Only
Remark 12 200
  • Delivery Term
  • Available in Build 16 & above

Field Mapping - Detail

  • As at 17 Jun 2015 - ESD soft-drink - Fixed Length Format
For Sales Invoice For Sales Credit Note
FnN Field Field Position Field Size SQL Accounting Field
Item_ID 14 10 Itemcode or
Maintain Item Code Note
Unit or
SubUnits		 74 or
84		 10 Qty
Unit_Price 94 10 UnitPrice
Discount 104 10 Disc
Tax_Code 139 5 Tax
SubTotal_Tax_Amount 149 10 TaxAmt
SubTotal_Net_Amount 189 10 Amount
FnN Field Field Position Field Size SQL Accounting Field
Item_ID 14 10 Itemcode or
Maintain Item Code Note
Unit or
SubUnits		 74 or
84		 10 Qty
Unit_Price 94 10 UnitPrice
Discount 104 10 Disc
Tax_Code 139 5 Tax
SubTotal_Tax_Amount 149 10 TaxAmt
SubTotal_Net_Amount 259 10 Amount
Reference
Reference_date
Reference_reason		 179
189
199		 10
10
50		 Description2
  • As at 15 Aug 2016 - ESD Online - CSV Format
For Sales Invoice For Sales Credit Note
FnN Field Field Position Field Size SQL Accounting Field
Item_ID 2 10 Itemcode or
Maintain Item Code Note
Unit or
SubUnits		 4 or
5		 10 Qty
Unit_Price 6 10 UnitPrice
Discount 7 10 Disc
Tax_Code 11 5 Tax
SubTotal_Tax_Amount 13 10 TaxAmt
SubTotal_Net_Amount 17 10 Amount
FnN Field Field Position Field Size SQL Accounting Field
Item_ID 2 10 Itemcode or
Maintain Item Code Note
Unit or
SubUnits		 4 or
5		 10 Qty
Unit_Price 6 10 UnitPrice
Discount 7 10 Disc
Tax_Code 11 5 Tax
SubTotal_Tax_Amount 13 10 TaxAmt
SubTotal_Net_Amount 19 10 Amount
Reference
Reference_date
Reference_reason		 16
20
17		 15
50
10		 Description2

Setting

In SQL Accounting

01. May refer to Point 2 at Things To Consider Before Import/Post
02. Make sure this option is Tick under Tools | Options | Customer

  • Perform Tax/Local Amount Rounding

In F&N Import

This can be see under Tools | Options

Options Description
UOMList List of UOM available in F&N (Normally is CTN & PCS)
DocNoAsDocNo
  • 0 : F&N DocNo will post to SQLAcc DocNoEx Field
  • 1 : F&N DocNo will post to SQLAcc DocNo Field (Recommended)
FNCodeAsItemCode
  • 0 : F&N ItemCode will check against SQL Accounting | Maintain Item | Note Field
  • 1 : F&N Itemcode same as SQL Accounting Itemcode (Recommended)
FNUOMAsUOM
  • 0 : F&N ItemCode will check against UOMList
  • 1 : F&N Itemcode UOM same as SQLAcc Itemcode UOM (Recommended)
OutletIDAsCompCode
  • For ESD soft-drink - Fixed Length Format Only
  • 0 : F&N Outlet ID will check against SQL Accounting | Maintain Customer | Remark Field
  • 1 : F&N Outlet ID same as SQLAcc Customer Code (Recommended)
  • F&N Outlet ID should be enter in SQL Accounting | Maintain Customer | Remark Field - Not valid for Build 10 & above

Steps

Import Invoice/Credit Note

Below steps is example to import Sales Invoice
01. Click Sales | Invoice...
02. Click Get File button & system will prompt 2 dialog like below

FnN-01.jpg

03. Click Files of Type & select the FnN File Format type
04. Select the F&N Invoice Text Header File

FnN-02.jpg

05. Select the F&N Invoice Text Detail File

FnN-03.jpg

06. Click Verify button to check the data with SQL Accounting Data
07. Select Project & Location (if Required)
08. Click Post To A/c button if confirm all is ok to post to SQL Accounting

----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg

Remember to check the data with Has Deemed Supplies column which is Tick
as system will by pass posting

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

Export E Invoice/Credit Note

FnN-04.jpg
01. Click Export E-Invoice Status...
02. Select Range Date (E-Invoice Submission date)
03. Select DocType (eg Invoice)
04. Click Apply
05. Click Export

FAQ

May refer to FAQ

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_Acc_F%26N_Import&oldid=13081"
Last modified
This page was last edited on 10 October 2024, at 04:55.