SQL Acc F&N Import

From eStream Software
Revision as of 02:03, 14 October 2022 by Twfaung (talk | contribs) (→‎Import Program)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

Import Program

History New/Updates/Changes

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

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

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

FAQ

May refer to FAQ

See also