SQL Acc F&N Import

From eStream Software
Jump to: navigation, search

Introduction

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

  • Sales Invoice
  • Sales Credit Note

Limitation

  • TaxCode with DS will not import.

F & N Specification

[top]

Import Program

History New/Updates/Changes

--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.
[top]

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
[top]

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
[top]

Steps

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

----------------------------------------------------------------------------------------------------------------------------------------------------
[top]

FAQ

May refer to FAQ

See also