SQL Acc Unilever Import: Difference between revisions

From eStream Software
 
(38 intermediate revisions by the same user not shown)
Line 3: Line 3:
* Sales Invoice
* Sales Invoice
* Sales Credit Note
* Sales Credit Note
==Limitation==
* <del>Problem if Unilever use both Cases and Pieces for 1 row (will bypass Pieces)</del> - Not valid for Build 7 & above


==Unilever Specification==
==Unilever Specification==
* 07 Feb 2018
* http://www.estream.com.my/downloadfile/Fairy/UnileverImp-Structure-20180207.zip
* 25 Sep 2017
* 25 Sep 2017
* http://www.estream.com.my/downloadfile/Fairy/...
* http://www.estream.com.my/downloadfile/Fairy/UnileverImp-Structure-20170925.zip


<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>
==Import Program==
==Import Program==
* Version (1.0.0.0) - 00  2017
* Version (1.2.1.9) - 13 Oct 2019
* Evaluation Limit : 100 Records per file
* Evaluation Limit : 100 Records per file
* http://www.estream.com.my/downloadfile/Fairy/-setup.exe
* [https://download.sql.com.my/customer/Fairy/SQLAccUnileverImp-setup.exe SQLAccUnileverImp-setup.exe]
* MD5 : Coming Soon...
* MD5 : AED487D03930BE65B48770AB1DA40636


===History New/Updates/Changes===
===History New/Updates/Changes===
--Build 9--
* Add Copy Cell Value Function.
* Add Auto Add Rounding for DocAmt <> Total Detail Amount.
--Build 8--
* Fixed Find Company Code not Correct if almost same Retail Code.
* Fixed Setting loss after uninstall.
--Build 7--
* Upgrade to Version 1.2.
* Enable Support if Unilever using Both Cases and Pieces for 1 row by Convert to Smallest UOM for Cases.
--Build 6--
* Add ZRL & SR TaxCode Option
--Build 5--
* Fix Header DocNo not match with Detail DocNo
--Build 4--
* Verify DocNo not correct for Old Format import
* Amount should deduct Discount before append
--Build 3--
* Upgrade to Version 1.1.
* Add back Old Format Import with auto add 00 in-front of DocNo before Post to Account.
--Build 2--
* Fixed UnitPrice 0 when After Post to Account.
--Build 1--
* Update New Format.
--Build 0--
--Build 0--


Line 25: Line 63:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>
==Field Mapping - Header==
==Field Mapping - Header==
* 25 Sep 2017
{| class="wikitable" style="margin: 1em auto 1em auto;"
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
|-
! Unilever Field !! Field Position !! Field Size !! SQL Accounting Field
! Unilever Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| INVH_Type || style="text-align: center;" | 1 || style="text-align: center;" | 2 ||
* 01 - Sales Invoice
* 02 - Sales Credit Note
|-
|-
| INVH_No || style="text-align: center;" | 3 || style="text-align: center;" | 10 || DocNo
| INVH_No || style="text-align: center;" | 3 || style="text-align: center;" | 10 || DocNo
|-
|-
| INVH_Retailer || style="text-align: center;" | 17 || style="text-align: center;" | 8 || Code
| INVH_Retailer || style="text-align: center;" | 17 || style="text-align: center;" | 8 || Code or Maintain Customer Remark
|-
|-
| INVH_Date || style="text-align: center;" | 26 || style="text-align: center;" | 19 || DocDate
| INVH_Date || style="text-align: center;" | 26 || style="text-align: center;" | 19 || DocDate
|-
| INVH_Total || style="text-align: center;" | 141 || style="text-align: center;" | 21 || Amount Before Discount
|-
| INVH_Discount || style="text-align: center;" | 162 || style="text-align: center;" | 21 || Discount Amount
|}
* 07 Feb 2018
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Unilever Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| INVH_Type || style="text-align: center;" | 1 || style="text-align: center;" | 2 ||
* 01 - Sales Invoice
* 02 - Sales Credit Note
|-
| INVH_No || style="text-align: center;" | 3 || style="text-align: center;" | 12 || DocNo
|-
| INVH_Retailer || style="text-align: center;" | 19 || style="text-align: center;" | 8 || Code or Maintain Customer Remark
|-
| INVH_Date || style="text-align: center;" | 28 || style="text-align: center;" | 19 || DocDate
|-
| INVH_Total || style="text-align: center;" | 143 || style="text-align: center;" | 21 || Amount Before Discount
|-
| INVH_Discount || style="text-align: center;" | 164 || style="text-align: center;" | 21 || Discount Amount
|}
|}


Line 39: Line 108:


==Field Mapping - Detail==
==Field Mapping - Detail==
* 25 Sep 2017
{| class="wikitable" style="margin: 1em auto 1em auto;"
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
|-
Line 45: Line 115:
| INVR_No || style="text-align: center;" | 3 || style="text-align: center;" | 10 || DocNo
| INVR_No || style="text-align: center;" | 3 || style="text-align: center;" | 10 || DocNo
|-
|-
| INVR_Product || style="text-align: center;" | 13 || style="text-align: center;" | 8 || Item Code
| INVR_Product || style="text-align: center;" | 13 || style="text-align: center;" | 8 || Item Code or
Maintain Item Code Note
|-
|-
| INVR_Cases || style="text-align: center;" | 26 || style="text-align: center;" | 22 || Qty (UOM=CA)
| INVR_Cases || style="text-align: center;" | 26 || style="text-align: center;" | 22 || Qty (UOM=CA)
|-
|-
| INVR_Pieces || style="text-align: center;" | 26 || style="text-align: center;" | 22 || Qty (UOM=PCS)
| INVR_Pieces || style="text-align: center;" | 48 || style="text-align: center;" | 22 || Qty (UOM=PCS)
|-
|-
| INVR_Amount || style="text-align: center;" | 70 || style="text-align: center;" | 21 || Amount
| INVR_Amount || style="text-align: center;" | 70 || style="text-align: center;" | 21 || Amount
|-
|-
| INVR_CasePrice || style="text-align: center;" | 112 || style="text-align: center;" | 21 || UnitPrice
| INVR_Discount || style="text-align: center;" | 91 || style="text-align: center;" | 21 || Discount
|-
| INVR_TAXRate || style="text-align: center;" | 133 || style="text-align: center;" | 22 ||
* 0 Tax Code = ZRL
* 6 Tax Code = SR
|-
|-
| INVR_TAXTotal || style="text-align: center;" | 155 || style="text-align: center;" | 21 || GST Amount
| INVR_TAXTotal || style="text-align: center;" | 155 || style="text-align: center;" | 21 || GST Amount
|}
|}


* 07 Feb 2018
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Unilever Field !! Field Position !! Field Size !! SQL Accounting Field
|-
| INVR_No || style="text-align: center;" | 3 || style="text-align: center;" | 12 || DocNo
|-
| INVR_Product || style="text-align: center;" | 15 || style="text-align: center;" | 8 || Item Code or
Maintain Item Code Note
|-
| INVR_Cases || style="text-align: center;" | 28 || style="text-align: center;" | 22 || Qty (UOM=CA)
|-
| INVR_Pieces || style="text-align: center;" | 50 || style="text-align: center;" | 22 || Qty (UOM=PCS)
|-
| INVR_Amount || style="text-align: center;" | 72 || style="text-align: center;" | 21 || Amount
|-
| INVR_Discount || style="text-align: center;" | 93 || style="text-align: center;" | 21 || Discount
|-
| INVR_TAXRate || style="text-align: center;" | 135 || style="text-align: center;" | 22 ||
* 0 Tax Code = ZRL
* 6 Tax Code = SR
|-
| INVR_TAXTotal || style="text-align: center;" | 157 || style="text-align: center;" | 21 || GST Amount
|}
* Unit Price will not import & will auto Assign by SQL Accounting due to Unilever file don't had Pieces Price & UOM Rate


<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>
Line 66: Line 169:
02. Make sure this option is Tick under '''Tools | Options | Customer'''
02. Make sure this option is Tick under '''Tools | Options | Customer'''
* Perform Tax/Local Amount Rounding
* Perform Tax/Local Amount Rounding
03. ''Menu: Stock | Maintain Stock Item...''
* Item Code to be create
:* '''Rounding'''


===In Unilever Import===
===In Unilever Import===
This can be see under '''Tools | Options'''
This can be see under '''Tools | Options'''
 
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Options !! Description
|-
| UOM_Cases ||
* UOM for Cases in SQL Accounting
* Default is '''CA'''
|-
| UOM_Pieces ||
* UOM for Pieces in SQL Accounting
* Default is '''PCS'''
|-
| RetailAsCompCode ||
* 0 : Unilever Retail Code will check against SQL Accounting | Maintain Customer | Remark Field
* 1 : Unilever Retail Code  same as SQL Accounting Customer Code (Recommended)
|-
| UnileverCodeAsItemCode ||
* 0 : Unilever Item Code will check against SQL Accounting | Maintain Item | Note Field
* 1 : Unilever Item Code  same as SQL Accounting Item Code (Recommended)
|-
| TaxCodeZRL || Tax code for 0%
|-
| TaxCodeSR || Tax Code other then 0%
|}
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


Line 76: Line 205:
01. Click '''Sales | Invoice...'''<br />
01. Click '''Sales | Invoice...'''<br />
02. 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 />
03.
[[File:Unilever-01.jpg|center]]
03. Select the Unilever Invoice Text Header File
[[File:Unilever-02.jpg|center]]
04. Select the Unilever Invoice Text Detail File
[[File:Unilever-03.jpg|center|800 px]]
05. Click '''Verify''' button to check the data with SQL Accounting Data<br />
06. Select '''Project''' & '''Location''' (if Required)<br />
07. Click '''Post To A/c''' button if confirm all is ok to post to SQL Accounting<br />


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

Latest revision as of 09:17, 11 July 2024

Introduction

Is External Shareware Program which to import Unilever CSV file to

  • Sales Invoice
  • Sales Credit Note

Limitation

  • Problem if Unilever use both Cases and Pieces for 1 row (will bypass Pieces) - Not valid for Build 7 & above

Unilever Specification

Import Program

  • Version (1.2.1.9) - 13 Oct 2019
  • Evaluation Limit : 100 Records per file
  • SQLAccUnileverImp-setup.exe
  • MD5 : AED487D03930BE65B48770AB1DA40636

History New/Updates/Changes

--Build 9--

  • Add Copy Cell Value Function.
  • Add Auto Add Rounding for DocAmt <> Total Detail Amount.

--Build 8--

  • Fixed Find Company Code not Correct if almost same Retail Code.
  • Fixed Setting loss after uninstall.

--Build 7--

  • Upgrade to Version 1.2.
  • Enable Support if Unilever using Both Cases and Pieces for 1 row by Convert to Smallest UOM for Cases.

--Build 6--

  • Add ZRL & SR TaxCode Option

--Build 5--

  • Fix Header DocNo not match with Detail DocNo

--Build 4--

  • Verify DocNo not correct for Old Format import
  • Amount should deduct Discount before append

--Build 3--

  • Upgrade to Version 1.1.
  • Add back Old Format Import with auto add 00 in-front of DocNo before Post to Account.

--Build 2--

  • Fixed UnitPrice 0 when After Post to Account.

--Build 1--

  • Update New Format.

--Build 0--

Todo

Field Mapping - Header

  • 25 Sep 2017
Unilever Field Field Position Field Size SQL Accounting Field
INVH_Type 1 2
  • 01 - Sales Invoice
  • 02 - Sales Credit Note
INVH_No 3 10 DocNo
INVH_Retailer 17 8 Code or Maintain Customer Remark
INVH_Date 26 19 DocDate
INVH_Total 141 21 Amount Before Discount
INVH_Discount 162 21 Discount Amount


  • 07 Feb 2018
Unilever Field Field Position Field Size SQL Accounting Field
INVH_Type 1 2
  • 01 - Sales Invoice
  • 02 - Sales Credit Note
INVH_No 3 12 DocNo
INVH_Retailer 19 8 Code or Maintain Customer Remark
INVH_Date 28 19 DocDate
INVH_Total 143 21 Amount Before Discount
INVH_Discount 164 21 Discount Amount

Field Mapping - Detail

  • 25 Sep 2017
Unilever Field Field Position Field Size SQL Accounting Field
INVR_No 3 10 DocNo
INVR_Product 13 8 Item Code or

Maintain Item Code Note

INVR_Cases 26 22 Qty (UOM=CA)
INVR_Pieces 48 22 Qty (UOM=PCS)
INVR_Amount 70 21 Amount
INVR_Discount 91 21 Discount
INVR_TAXRate 133 22
  • 0 Tax Code = ZRL
  • 6 Tax Code = SR
INVR_TAXTotal 155 21 GST Amount


  • 07 Feb 2018
Unilever Field Field Position Field Size SQL Accounting Field
INVR_No 3 12 DocNo
INVR_Product 15 8 Item Code or

Maintain Item Code Note

INVR_Cases 28 22 Qty (UOM=CA)
INVR_Pieces 50 22 Qty (UOM=PCS)
INVR_Amount 72 21 Amount
INVR_Discount 93 21 Discount
INVR_TAXRate 135 22
  • 0 Tax Code = ZRL
  • 6 Tax Code = SR
INVR_TAXTotal 157 21 GST Amount


  • Unit Price will not import & will auto Assign by SQL Accounting due to Unilever file don't had Pieces Price & UOM Rate

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

03. Menu: Stock | Maintain Stock Item...

  • Item Code to be create
  • Rounding

In Unilever Import

This can be see under Tools | Options

Options Description
UOM_Cases
  • UOM for Cases in SQL Accounting
  • Default is CA
UOM_Pieces
  • UOM for Pieces in SQL Accounting
  • Default is PCS
RetailAsCompCode
  • 0 : Unilever Retail Code will check against SQL Accounting | Maintain Customer | Remark Field
  • 1 : Unilever Retail Code same as SQL Accounting Customer Code (Recommended)
UnileverCodeAsItemCode
  • 0 : Unilever Item Code will check against SQL Accounting | Maintain Item | Note Field
  • 1 : Unilever Item Code same as SQL Accounting Item Code (Recommended)
TaxCodeZRL Tax code for 0%
TaxCodeSR Tax Code other then 0%

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

Unilever-01.jpg

03. Select the Unilever Invoice Text Header File

Unilever-02.jpg

04. Select the Unilever Invoice Text Detail File

Unilever-03.jpg

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

FAQ

May refer to FAQ

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_Acc_Unilever_Import&oldid=12975"
Last modified
This page was last edited on 11 July 2024, at 09:17.