(93 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'') | ||
== | ==Mamee Specification== | ||
===Format 1=== | |||
* 03 Dec 2015 | * 03 Dec 2015 | ||
* http://www.estream.com.my/downloadfile/Fairy/Mamee- | * 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 ( | * 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 | --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: | {| class="wikitable" style="margin: 1em auto 1em auto;" | ||
! scope="col" style="width: | ! 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 !! Field | ! Mamee Field !! Field Position !! SQL Accounting Field | ||
|- | |- | ||
| Salesman Code || 1 || Agent | | Salesman Code || style="text-align: center;" | 1 || Agent | ||
|- | |- | ||
| Customer Code || 2 || Code | | Customer Code or <br />Census Number || style="text-align: center;" | 2 or <br />18 || Code | ||
|- | |- | ||
| Invoice Number || 4 || DocNo | | Invoice Number || style="text-align: center;" | 4 || DocNo | ||
|- | |- | ||
| Invoice Date || 6 || 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 || DocNoEx | | 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: | {| class="wikitable" style="margin: 1em auto 1em auto;" | ||
! scope="col" style="width: | ! 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 | ||
|- | |- | ||
| | | 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 | ||
|- | |- | ||
| | | 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 /> | ||
:01. Click '''Product | Invoice...'''<br /> | |||
[[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
- Version (2.4.0.5) - 08 Dec 2017
- Evaluation Limit : 100 Records per file
- http://www.estream.com.my/downloadfile/Fairy/SQLAccMameeImp-setup.exe
- MD5 : BA72428486784AFC3655601294BE83FD
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 | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
Product Related - Format 2
For Sales Invoice | For Sales Credit Note | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
Field Mapping - Detail
Product Related - Format 1
For Sales Invoice | For Sales Credit Note | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
Product Related - Format 2
For Sales Invoice | For Sales Credit Note | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
Field Mapping - Header
Non-Product Related
For Sales Debit Note | For Sales Credit Note | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
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 |
|
MameeSalesmanCodeAsAgent |
|
FormatType |
|
Menu : Tools | Maintain Agent...
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...
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...
- 02. Click Get File button & system will prompt 2 dialog like below
- 03. Select the Mamee Invoice Text/CSV Header File
- 04. Click Open
- 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
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Non-Product Related
Below steps is example to import Sales Credit Note
- 01. Click Non-Product | Credit Note...
- 02. Click Get File button & system will prompt dialog like below
- 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
- Others Customisation