SQL Acc Mamee Import: Difference between revisions

From eStream Software
 
(96 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Introduction==
==Introduction==
Is External Program which to import Mamee Text or CSV file to  
Is External Shareware Program which to import Mamee Text or CSV file to  
* Sales Invoice
* Sales Invoice
* Sales Credit Note
* Sales Credit Note
* Sales Debit Note (Non Product Related Only)


==Limitation==
==Limitation==
Line 8: Line 9:
* Only support Mamee Product Related file (i.e. file with end ''HDR'' or ''DTL'' eg ''INVHDR'' or ''INVDTL'')
* Only support Mamee Product Related file (i.e. file with end ''HDR'' or ''DTL'' eg ''INVHDR'' or ''INVDTL'')


==F & N Specification==
==Mamee Specification==
===Format 1===
* 03 Dec 2015
* 03 Dec 2015
* http://www.estream.com.my/downloadfile/Fairy/Mamee-Acct_Integration_Reference.zip
* http://www.estream.com.my/downloadfile/Fairy/Mamee-ExampleFile.zip
 
===Format 2===
* 22 Nov 2017
* http://www.estream.com.my/downloadfile/Fairy/Mamee-ExampleFile2.zip


==Import Program==
==Import Program==
* Version (1.0.0.0) - Coming Soon...
* Version (2.4.0.5) - 08 Dec 2017
* Evaluation Limit : 100 Records per file
* http://www.estream.com.my/downloadfile/Fairy/SQLAccMameeImp-setup.exe
* http://www.estream.com.my/downloadfile/Fairy/SQLAccMameeImp-setup.exe
* MD5 : BA72428486784AFC3655601294BE83FD


===History New/Updates/Changes===
===History New/Updates/Changes===
--Build 0--
--Build 5--
* Upgrade to Version 2.4.
* Upgrade to New Compiler.
* Add Format 2 import.
 
--Build 4--
* Upgrade to Version 1.3.
* Add Maintain Non-Product GL Function.
* Enable Import Non-Product Sales CN & DN Import
 
--Build 3--
* Upgrade to Version 1.2.
* Error in matching Agent
* Amount not excluding Disc Amount after Import.
* Add Link to SQL Acc. File | Run.
* Rearrange Detail column
* Fix Detail not Sort by Seq
* Set Disc to 2 Decimal Point.
 
--Build 2--
* Error on Maintain Agent if Record More then 1
 
--Build 1--
* Upgrade to Version 1.1
* Add Maintain Agent Function.
* Enable Use Census Number field as SQL Acc Customer Code.
 
<div style="float: right;">  [[#top|[top]]]</div>
 
==Todo==
*


==Field Mapping - Header==
==Field Mapping - Header==
{| class="wikitable"
===Product Related - Format 1===
! scope="col" style="width: 300px;" | For Sales Invoice
{| class="wikitable" style="margin: 1em auto 1em auto;"
! scope="col" style="width: 330px;" | For Sales Credit Note
! scope="col" style="width: 400px;" | For Sales Invoice
! scope="col" style="width: 420px;" | For Sales Credit Note
|- style="vertical-align: top;"
|- style="vertical-align: top;"
|  
|  
:{| class="wikitable"
:{| class="wikitable"
|-
|-
! Mamee Field !! SQL Accounting Field
! Mamee Field !! Field Position !! SQL Accounting Field
|-
|-
| Salesman Code || Agent
| Salesman Code || style="text-align: center;" | 1 || Agent
|-
|-
| Customer Code || Code
| Customer Code or <br />Census Number || style="text-align: center;" | 2 or <br />18 || Code
|-
|-
| Invoice Number || DocNo
| Invoice Number || style="text-align: center;" | 4  || DocNo
|-
|-
| Invoice Date || DocDate
| Invoice Date || style="text-align: center;" | 6 || DocDate
|}
|}
  ||  
  ||  
:{| class="wikitable"
:{| class="wikitable"
|- style="vertical-align: top;"
|- style="vertical-align: top;"
! Mamee Field !! SQL Accounting Field
! Mamee Field !! Field Position !! SQL Accounting Field
|-
|-
| Transaction Number || DocNo
| Transaction Number || style="text-align: center;" | 0 || DocNo
|-
|-
| Transaction Date || DocDate
| Transaction Date || style="text-align: center;" | 1 || DocDate
|-
|-
| Customer Code || Code
| Customer Code or <br />Census Number || style="text-align: center;" | 2 or <br />18|| Code
|-
|-
| Salesman Code || Agent
| Salesman Code || style="text-align: center;" | 7 || Agent
|-
|-
| Invoice Number || Example
| Invoice Number || style="text-align: center;" | 15 || DocNoEx
|}
|}
|}
|}
===Product Related - Format 2===
{| class="wikitable" style="margin: 1em auto 1em auto;"
! scope="col" style="width: 400px;" | For Sales Invoice
! scope="col" style="width: 420px;" | For Sales Credit Note
|- style="vertical-align: top;"
|
:{| class="wikitable"
|-
! Mamee Field !! Field Position !! SQL Accounting Field
|-
| Salesman Code || style="text-align: center;" | 3 || Agent
|-
| Customer Code or <br />Census Number || style="text-align: center;" | 1 or <br />31 || Code
|-
| Invoice Number || style="text-align: center;" | 5  || DocNo
|-
| Invoice Date || style="text-align: center;" | 9 || DocDate
|}
||
:{| class="wikitable"
|- style="vertical-align: top;"
! Mamee Field !! Field Position !! SQL Accounting Field
|-
| Transaction Number || style="text-align: center;" | 4 || DocNo
|-
| Transaction Date || style="text-align: center;" | 6 || DocDate
|-
| Customer Code or <br />Census Number || style="text-align: center;" | 0 or <br />18|| Code
|-
| Salesman Code || style="text-align: center;" | 2 || Agent
|-
| Invoice Number || style="text-align: center;" | 15 || DocNoEx
|}
|}
<div style="float: right;">  [[#top|[top]]]</div>


==Field Mapping - Detail==
==Field Mapping - Detail==
{| class="wikitable"
===Product Related - Format 1===
! scope="col" style="width: 300px;" | For Sales Invoice
{| class="wikitable"  style="margin: 1em auto 1em auto;"
! scope="col" style="width: 330px;" | For Sales Credit Note
! scope="col" style="width: 400px;" | For Sales Invoice
! scope="col" style="width: 420px;" | For Sales Credit Note
|- style="vertical-align: top;"
|
:{| class="wikitable"
|-
! Mamee Field !! Field Position !! SQL Accounting Field
|-
| Product Code ||style="text-align: center;" | 2 || ItemCode
|-
| Product Index || style="text-align: center;" | 3 || Seq
|-
| UOM Code || style="text-align: center;" | 5 || UOM
|-
| Product Quantity || style="text-align: center;" | 6  || Qty
|-
| UOM List Price || style="text-align: center;" | 9 || UnitPrice
|-
| Net Amount || style="text-align: center;" | 14 || Amount
|-
| Promo Discount || style="text-align: center;" | 12 || Disc
|-
| Tax Amount || style="text-align: center;" | 13 || TaxAmt
|}
||
:{| class="wikitable"
|-
! Mamee Field !! Field Position !! SQL Accounting Field
|-
| Product Code || style="text-align: center;" | 8 || ItemCode
|-
| Product Index || style="text-align: center;" | 6 || Seq
|-
| UOM Code || style="text-align: center;" | 14 || UOM
|-
| Product Quantity || style="text-align: center;" | 16 || Qty
|-
| UOM List Price || style="text-align: center;" | 18 || UnitPrice
|-
| Net Amount || style="text-align: center;" | 22 || Amount
|-
| Promotion Discount || style="text-align: center;" | 21 || Disc
|-
| Tax Amount || style="text-align: center;" | 23 || TaxAmt
|-
| Reason Description || style="text-align: center;" | 26 || Description2
|}
|}
 
===Product Related - Format 2===
{| class="wikitable"  style="margin: 1em auto 1em auto;"
! scope="col" style="width: 400px;" | For Sales Invoice
! scope="col" style="width: 420px;" | For Sales Credit Note
|- style="vertical-align: top;"
|- style="vertical-align: top;"
|  
|  
:{| class="wikitable"
:{| class="wikitable"
|-
|-
! Mamee Field !! SQL Accounting Field
! Mamee Field !! Field Position !! SQL Accounting Field
|-
|-
| Product Code || ItemCode
| Product Code ||style="text-align: center;" | 4 || ItemCode
|-
|-
| Product Index || Seq
| Product Index || style="text-align: center;" | 6 || Seq
|-
|-
| UOM Code || UOM
| UOM Code || style="text-align: center;" | 9 || UOM
|-
|-
| Product Quantity || Qty
| Product Quantity || style="text-align: center;" | 8  || Qty
|-
|-
| UOM List Price || UnitPrice
| UOM List Price || style="text-align: center;" | 12 || UnitPrice
|-
|-
| Gross Amount || Amount
| Net Amount || style="text-align: center;" | 14 || Amount
|-
|-
| Promo Discount || Disc
| Promo Discount || style="text-align: center;" | 18 || Disc
|-
|-
| Tax Amount || TaxAmt
| Tax Amount || style="text-align: center;" | 15 || TaxAmt
|}
|}
  ||  
  ||  
:{| class="wikitable"
:{| class="wikitable"
|-
|-
! Mamee Field !! SQL Accounting Field
! Mamee Field !! Field Position !! SQL Accounting Field
|-
|-
| Product Code || ItemCode
| Product Code || style="text-align: center;" | 4 || ItemCode
|-
|-
| Product Index || Seq
| Product Index || style="text-align: center;" | 6 || Seq
|-
|-
| UOM Code || UOM
| UOM Code || style="text-align: center;" | 9 || UOM
|-
|-
| Product Quantity || Qty
| Product Quantity || style="text-align: center;" | 8 || Qty
|-
|-
| UOM List Price || UnitPrice
| UOM List Price || style="text-align: center;" | 12 || UnitPrice
|-
|-
| Gross Amount || Amount
| Net Amount || style="text-align: center;" | 17 || Amount
|-
|-
| Promotion Discount || Disc
| Promotion Discount || style="text-align: center;" | 15 || Disc
|-
|-
| Tax Amount || TaxAmt
| Tax Amount || style="text-align: center;" | 18 || TaxAmt
|-  
|-  
| Reason Description || Description2
| Reason Description || style="text-align: center;" | 22 || Description2
|}
|}
 
<div style="float: right;">  [[#top|[top]]]</div>
 
==Field Mapping - Header==
===Non-Product Related===
{| class="wikitable"  style="margin: 1em auto 1em auto;"
! scope="col" style="width: 420px;" | For Sales Debit Note
! scope="col" style="width: 420px;" | For Sales Credit Note
|- style="vertical-align: top;"
|
:{| class="wikitable"
|-
! Mamee Field !! Field Position !! SQL Accounting Field
|-
| Transaction Number ||style="text-align: center;" | 0 || DocNo
|-
| Customer Code or <br /> Census Number || style="text-align: center;" | 3 or 16 || Code
|-
| Transaction Date || style="text-align: center;" | 2  || DocDate
|-
| Salesman Code || style="text-align: center;" | 9 || Agent
|-
| Header Remark || style="text-align: center;" | 12 || Header Description
|-
| Remark || style="text-align: center;" | 7 || Item Description
|-
| Reason Code  <br />(Description) || style="text-align: center;" | 11 || Description2
|-
| Tax Invoice Number || style="text-align: center;" | 6 || DocNoEx
|-
| Tax Invoice Date || style="text-align: center;" | 13 || DocRef1
|-
| Net Amount || style="text-align: center;" | 5 || UnitPrice
|-
| Tax Amount || style="text-align: center;" | 8 || TaxAmt
|}
||
:{| class="wikitable"
|-
! Mamee Field !! Field Position !! SQL Accounting Field
|-
| Salesman Code || style="text-align: center;" | 1 || Agent
|-
| Customer Code or <br /> Census Number || style="text-align: center;" | 3 or 16 || Code
|-
| Transaction Number || style="text-align: center;" | 5 || DocNo
|-
| Transaction Date || style="text-align: center;" | 6 || DocDate
|-
| Header Remark || style="text-align: center;" | 8 || Header Description
|-
| Remark || style="text-align: center;" | 11 || Item Description
|-
| Tax Invoice Number || style="text-align: center;" | 9 || DocNoEx
|-
| Tax Invoice Date || style="text-align: center;" | 10 || DocRef1
|-
| Reason Code  <br />(Description) || style="text-align: center;" | 7 || Description2
|-
| Net Amount || style="text-align: center;" | 12 || UnitPrice
|-
| Tax Amount || style="text-align: center;" | 14 || TaxAmt
|}
|}
|}
|}
==Setting==
===In SQL Accounting===
Make sure this option is Tick under '''Tools | Options | Customer'''
* One Cent Different Rounding (Local Currency Fields) for all AR/SL Documents (Recommended)
===In Mamee Import===
Menu : '''Tools | Options...'''
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Options !! Description
|-
| MameeCustomerCodeAsCode ||
* 0 : Mamee Census Number check against SQLAcc Customer Code Field
* 1 : Mamee Customer Code same as SQLAcc Customer Code Field (Recommended)
|-
| MameeSalesmanCodeAsAgent ||
* 0 : Mamee Salesman Code will use Mamee Import Agent List
* 1 : Mamee Salesman Code same as SQLAcc Agent Code (Recommended)
|-
| FormatType ||
* 1 : Format 1
* 2 : Format 2
|}
Menu : '''Tools | Maintain Agent...'''
[[File:Mamee-04.jpg|center]]
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Options !! Description
|-
| Saleman Code || Mamee Salesman Code
|-
| Agent || SQL Accounting Agent Code
|}
:01. Click the + button
:02. Enter the ''Mamee Salesman Code''
:03. Select the ''SQL Accounting Agent Code''
:04. Click Tick to Save
Menu : '''Tools | Maintain Non-Product GL...'''
[[File:Mamee-05.jpg|center]]
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Options !! Description
|-
| Mamee Reason Code || Mamee Reason Code
|-
| Reason Desc. || Mamee Reason Description
|-
| GL Acc. Debit Note || SQL Accounting GL Account Code for Debit Note
|-
| GL Acc. Credit Note || SQL Accounting GL Account Code for Credit Note
|}
:01. Select the record
:02. Click Edit (the up arrow)
:03. Select the ''GL Code for Debit Note''
:04. Select the ''GL Code for Credit Note''
:05. Click Tick to Save


==Steps==
==Steps==
===Product Related===
Below steps is example to import Sales Invoice<br />
Below steps is example to import Sales Invoice<br />
1. Click '''Invoice...'''<br />
:01. Click '''Product | Invoice...'''<br />
* More Coming Soon...
[[File:Mamee-01.jpg|center| 700px]]
:02. Click '''Get File''' button & system will prompt 2 dialog like below<br />
[[File:Mamee-02.jpg|center|700px]]
:03. Select the Mamee Invoice Text/CSV Header File<br />
:04. Click '''Open'''
[[File:Mamee-03.jpg|center|700px]]
:05. Select the Mamee Invoice Text/CSV Detail File<br />
:06. Click '''Open'''<br />
:07. Click '''Verify''' button to check the data with SQL Accounting Data<br />
:08. Click '''Post To A/c''' button if confirm all is ok to post to SQL Accounting<br />
 
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Tips-01.jpg|60px]]||
: Record(s) will only '''Post''' if the '''Check Box''' is Tick & '''Status''' is Ok
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
 
===Non-Product Related===
Below steps is example to import Sales Credit Note<br />
:01. Click '''Non-Product | Credit Note...'''<br />
[[File:Mamee-06.jpg|center| 750px]]
:02. Click '''Get File''' button & system will prompt dialog like below<br />
[[File:Mamee-07.jpg|center| 700px]]
:03. Select the Mamee Non Product Credit Note Text/CSV File<br />
:04. Click '''Open'''
:05. Click '''Verify''' button to check the data with SQL Accounting Data<br />
:06. Click '''Post To A/c''' button if confirm all is ok to post to SQL Accounting<br />


==FAQ==
==FAQ==

Latest revision as of 07:33, 8 December 2017

Introduction

Is External Shareware Program which to import Mamee Text or CSV file to

  • Sales Invoice
  • Sales Credit Note
  • Sales Debit Note (Non Product Related Only)

Limitation

  • Fixed Tax Code SR (i.e. all transaction is SR)
  • Only support Mamee Product Related file (i.e. file with end HDR or DTL eg INVHDR or INVDTL)

Mamee Specification

Format 1

Format 2

Import Program

History New/Updates/Changes

--Build 5--

  • Upgrade to Version 2.4.
  • Upgrade to New Compiler.
  • Add Format 2 import.

--Build 4--

  • Upgrade to Version 1.3.
  • Add Maintain Non-Product GL Function.
  • Enable Import Non-Product Sales CN & DN Import

--Build 3--

  • Upgrade to Version 1.2.
  • Error in matching Agent
  • Amount not excluding Disc Amount after Import.
  • Add Link to SQL Acc. File | Run.
  • Rearrange Detail column
  • Fix Detail not Sort by Seq
  • Set Disc to 2 Decimal Point.

--Build 2--

  • Error on Maintain Agent if Record More then 1

--Build 1--

  • Upgrade to Version 1.1
  • Add Maintain Agent Function.
  • Enable Use Census Number field as SQL Acc Customer Code.

Todo

Field Mapping - Header

Product Related - Format 1

For Sales Invoice For Sales Credit Note
Mamee Field Field Position SQL Accounting Field
Salesman Code 1 Agent
Customer Code or
Census Number		 2 or
18		 Code
Invoice Number 4 DocNo
Invoice Date 6 DocDate
Mamee Field Field Position SQL Accounting Field
Transaction Number 0 DocNo
Transaction Date 1 DocDate
Customer Code or
Census Number		 2 or
18		 Code
Salesman Code 7 Agent
Invoice Number 15 DocNoEx

Product Related - Format 2

For Sales Invoice For Sales Credit Note
Mamee Field Field Position SQL Accounting Field
Salesman Code 3 Agent
Customer Code or
Census Number		 1 or
31		 Code
Invoice Number 5 DocNo
Invoice Date 9 DocDate
Mamee Field Field Position SQL Accounting Field
Transaction Number 4 DocNo
Transaction Date 6 DocDate
Customer Code or
Census Number		 0 or
18		 Code
Salesman Code 2 Agent
Invoice Number 15 DocNoEx

Field Mapping - Detail

Product Related - Format 1

For Sales Invoice For Sales Credit Note
Mamee Field Field Position SQL Accounting Field
Product Code 2 ItemCode
Product Index 3 Seq
UOM Code 5 UOM
Product Quantity 6 Qty
UOM List Price 9 UnitPrice
Net Amount 14 Amount
Promo Discount 12 Disc
Tax Amount 13 TaxAmt
Mamee Field Field Position SQL Accounting Field
Product Code 8 ItemCode
Product Index 6 Seq
UOM Code 14 UOM
Product Quantity 16 Qty
UOM List Price 18 UnitPrice
Net Amount 22 Amount
Promotion Discount 21 Disc
Tax Amount 23 TaxAmt
Reason Description 26 Description2

Product Related - Format 2

For Sales Invoice For Sales Credit Note
Mamee Field Field Position SQL Accounting Field
Product Code 4 ItemCode
Product Index 6 Seq
UOM Code 9 UOM
Product Quantity 8 Qty
UOM List Price 12 UnitPrice
Net Amount 14 Amount
Promo Discount 18 Disc
Tax Amount 15 TaxAmt
Mamee Field Field Position SQL Accounting Field
Product Code 4 ItemCode
Product Index 6 Seq
UOM Code 9 UOM
Product Quantity 8 Qty
UOM List Price 12 UnitPrice
Net Amount 17 Amount
Promotion Discount 15 Disc
Tax Amount 18 TaxAmt
Reason Description 22 Description2

Field Mapping - Header

Non-Product Related

For Sales Debit Note For Sales Credit Note
Mamee Field Field Position SQL Accounting Field
Transaction Number 0 DocNo
Customer Code or
Census Number		 3 or 16 Code
Transaction Date 2 DocDate
Salesman Code 9 Agent
Header Remark 12 Header Description
Remark 7 Item Description
Reason Code
(Description)		 11 Description2
Tax Invoice Number 6 DocNoEx
Tax Invoice Date 13 DocRef1
Net Amount 5 UnitPrice
Tax Amount 8 TaxAmt
Mamee Field Field Position SQL Accounting Field
Salesman Code 1 Agent
Customer Code or
Census Number		 3 or 16 Code
Transaction Number 5 DocNo
Transaction Date 6 DocDate
Header Remark 8 Header Description
Remark 11 Item Description
Tax Invoice Number 9 DocNoEx
Tax Invoice Date 10 DocRef1
Reason Code
(Description)		 7 Description2
Net Amount 12 UnitPrice
Tax Amount 14 TaxAmt

Setting

In SQL Accounting

Make sure this option is Tick under Tools | Options | Customer

  • One Cent Different Rounding (Local Currency Fields) for all AR/SL Documents (Recommended)

In Mamee Import

Menu : Tools | Options...

Options Description
MameeCustomerCodeAsCode
  • 0 : Mamee Census Number check against SQLAcc Customer Code Field
  • 1 : Mamee Customer Code same as SQLAcc Customer Code Field (Recommended)
MameeSalesmanCodeAsAgent
  • 0 : Mamee Salesman Code will use Mamee Import Agent List
  • 1 : Mamee Salesman Code same as SQLAcc Agent Code (Recommended)
FormatType
  • 1 : Format 1
  • 2 : Format 2

Menu : Tools | Maintain Agent...

Mamee-04.jpg
Options Description
Saleman Code Mamee Salesman Code
Agent SQL Accounting Agent Code
01. Click the + button
02. Enter the Mamee Salesman Code
03. Select the SQL Accounting Agent Code
04. Click Tick to Save

Menu : Tools | Maintain Non-Product GL...

Mamee-05.jpg
Options Description
Mamee Reason Code Mamee Reason Code
Reason Desc. Mamee Reason Description
GL Acc. Debit Note SQL Accounting GL Account Code for Debit Note
GL Acc. Credit Note SQL Accounting GL Account Code for Credit Note
01. Select the record
02. Click Edit (the up arrow)
03. Select the GL Code for Debit Note
04. Select the GL Code for Credit Note
05. Click Tick to Save

Steps

Product Related

Below steps is example to import Sales Invoice

01. Click Product | Invoice...
Mamee-01.jpg
02. Click Get File button & system will prompt 2 dialog like below
Mamee-02.jpg
03. Select the Mamee Invoice Text/CSV Header File
04. Click Open
Mamee-03.jpg
05. Select the Mamee Invoice Text/CSV Detail File
06. Click Open
07. Click Verify button to check the data with SQL Accounting Data
08. Click Post To A/c button if confirm all is ok to post to SQL Accounting
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg
Record(s) will only Post if the Check Box is Tick & Status is Ok
----------------------------------------------------------------------------------------------------------------------------------------------------

Non-Product Related

Below steps is example to import Sales Credit Note

01. Click Non-Product | Credit Note...
Mamee-06.jpg
02. Click Get File button & system will prompt dialog like below
Mamee-07.jpg
03. Select the Mamee Non Product Credit Note Text/CSV File
04. Click Open
05. Click Verify button to check the data with SQL Accounting Data
06. 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_Mamee_Import&oldid=8026"
Last modified
This page was last edited on 8 December 2017, at 07:33.