Microsoft Dynamics GP

3rd Party Integration

This provides information relating to the swiftpos Microsoft Dynamics GP General Ledger 3rd Party Integration. To access select the Microsoft Dynamics GP menu option from the Data Portal menu.

---

Subjects      ▼

• About
• How To?
• Screens

 

---

About      ▲ ▼

• What Is It?
• What is Required?
• What To Consider?
• When Is It Used?

 

What Is It?      ▲ ▼

The swiftpos MS Dynamic GP export is a General Ledger interface that allows Venues to export Sales and Stock Movement data to their Microsoft Dynamics GP General Ledger. The MS Dynamic GP Export will export the following:

 

What Is Required?      ▲ ▼

• To make use of this feature, sites will need to be registered for the General Ledger MS Dynamics GP interface. This may incur an additional charge. To confirm charges, email registrations@swiftpos.com.au.

 

What To Consider?      ▲ ▼

• Assigning/Mapping Tabs
• Chart Of Accounts Format
• Export File Formats
• GL Account Code Exported
• GL Account Code Format
• MS Dynamics GP Accounts
• Stock Transfers
• Track Individual Location Costs

 

Assigning/Mapping Tabs     ▲ ▼

• All assigning/mapping tabs MUST HAVE Accounts assigned. Unassigned Accounts may cause errors.

 

Chart Of Accounts Format     ▲ ▼

Field Name	Description	Data Type	Max Length
Account	The MS Dynamic GP Account	Text	10
Description	The description of the Account	Text	50
Type	The type of Account. For example, Assets, Expenses, Sales, COGS, etc	Text	20

 

Example

1001,Sales Bar Bulk Beer,Sales
1002,Sales Bar Bottled Beer,Sales
1003,Sales Bar Spirits,Sales
1004,Sales Bar Wine,Sales
3001,Purchases Bar Bulk Beer,COGS
3002,Purchases Bottled Beer ,COGS
3003,Purchases Bar Spirits,COGS
3004,Purchases Bar Wine,COGS
5000,Cash On Hand ,Assets

 

Export File Formats     ▲ ▼

• Sales/Inventory Transaction Data

Field Name	Description	Data Type
Batch Number	SPGPYYYYMMDD	Text
Account	AAAABBBBBBCCC Where AAAA is the MS Dynamic GP Account, BBBBBB is the Location/Site and CCC is the Department	Text
Line Description	AAAAAAAAAAAAAAAAAAA; BBBBBBBBBBBBBBBBB Where AAAA is the MS Dynamic GP Account Description by default. However for Sales Accounts AAAA is the Product Group. BBBB is the Location Name.	Text
Debit Amount	The amount debited to the Account	Decimal
Credit Amount	The amount credited to the Account	Decimal
Date	DD/MM/YYYY - The Trading Date	Date
InterCompanyDB	The Location Group's selected Entity	Text
InterCompanyTransaction	True/False	Text

 

GL Account Code Exported     ▲ ▼

• GL Account Code Exported - The GL Account Code exported is sourced as follows:
  • GL Account - These Accounts can be either added or imported into the Accounts tab. 4 characters in length. (Represented as AAAA in the example below). Once added/imported, these Accounts can then be used to ensure all assignments/mappings are completed in the Clearing Account, Sales Account, Inventory Accounts and Tax Accounts tabs to the right of the Accounts tab.
  • Location/Site - This is configured HERE. 6 characters in length.  (Represented as BBBBBB in the example below).
  • Department - This is configured HERE.  3 characters in length. (Represented as CCC in the example below).
    
    

    Department Override - To override the default Location specific Department, a Department override can be configured against Master Groups and will be used instead of the Location specific Department when exporting.

    • Create a special Master Group (for example, Game Day) and specify an override Dept code (3 characters and represented as CCC in the example below) for it. The code specified here will be used to override the default Location's Department Code when generating the GL Account for export. Override Dept Codes ONLY have to be configured for those Master Groups that require it. All other Master Groups can be left blank. In which case they will default to the Location specific Department. 
    • Create a special Product Group (for example, Game Day), to which specific Products can be assigned.
    • Assign the specially created Product Group to the specially created Master Group.
    • Assign (using the Report Group field) the required Products to the specially created Product Group.
    • Example
      
      • MS Dynamics GP Accounts - These are configured in the Accounts tab and can consist of one of the following:
        
        1. GL Account - These are the Accounts that are added by importing the Chart of Accounts (COA). They are 4 characters in length and are then used as the first 4 characters of the GL Account generated when exported.

 

MS Dynamics GP Account	Location/Site	Department	GL Account Exported	Comments
AAAA	BBBBBB	CCC	AAAABBBBBBCCC	Where AAAA is the MS Dynamics GP Account from the COA Import. BBBBBB is the Location/Site value configured against each Location. CCC is the Department value configured against each Location.
3161	003BGL	RES	3161003BGLRES
31612	003BGL	RES	31612003BGLRE	The GL Account will be generated for export but will most probably cause an error when importing into MS Dynamics GP.
3161	003BG	RES		The GL Account will not be generated for export in these two cases, as they do NOT comply with the GL Account Code Format.
3161	003BGL	RE

 

• GL Account Code Exported continued ...
  • Department continued ...
    • Example continued ...
      • MS Dynamics GP Accounts continued ...
        • Alternative Account - These Accounts are added (via the Accounts Tab) to ensure an alternate GL Account is generated when exporting. These Accounts can be one of the following lengths:

 

Length	Format	Location/Site	Department	GL Account Exported	Comments
4	XXXX	BBBBBB	CCC	XXXXXBBBBBBCCC	Where XXXX, XXXX-YYYYYY and/or XXXX-YYYYYY-CCC  is the MS Dynamics GP Account added as an Alternative Account. The hyphen characters must be included. These will be removed when generating the GL Account for export. BBBBBB is the Location/Site value configured against each Location. CCC is the Department value configured against each Location.
11	XXXX-YYYYYY	003BGL	RES	XXXXYYYYYYRES
15	XXXX-YYYYYY-ZZZ	003BGL	RES	XXXXYYYYYYZZZ

 

• GL Account Code Exported continued ...
  • Example

 

Length	Alternate Account	Location/Site	Department	GL Account Exported	Comments
4	8540	003BGL	RES	8540003BGLRES	Once an Alternative Account has been added (via the Accounts Tab), they need to be assigned/mapped to the appropriate Accounts, that is via the Clearing Accounts, Sales Account, Inventory Accounts and Tax Accounts Tabs.   Also, A Product requiring Alternative Accounts to that of the Accounts currently assigned/mapped to the Product's Location/Master Group combinations (in the Tabs in the Settings screen), a separate Master Group will need to be created and the Product assigned to it. Also, once the Master Group has been created ensure the Alternative Account has been created and the Location/Master Group combinations (in the Tabs in the Settings screen) are correctly assigned/mapped.
11	8540-045ENT	003BGL	RES	8540045ENTRES
15	8540-045ENT-ZZZ	003BGL	GDY	8540045ENTGDY

 

GL Account Code Format     ▲ ▼

The GL Account Code is based on the 3 sources and a GL Account Code Format value of XXXXXXXXXXXXX, will be exported as a 13-character field.

• If the GL Account Code Format value entered is more than 13 characters long, the GL Account Code will NOT be generated.
• If it is LESS than 13 characters, then only the number of characters specified in the GL Account Code Format will be generated. The remainder will be truncated.
•  Any hyphen characters included in GL Account Code Format value entered will result in the GL Account Code NOT being generated.

 

MS Dynamics GP Accounts     ▲ ▼

These Accounts are created/imported to assign/map to swiftpos Location/Media, Location/Master Group combinations and swiftpos Locations in the tabs to the right.

 

 

As a guide and at a minimum the Accounts needed to be available/configured are as follows:

• Asset Type Accounts
  • Undeposited Funds - An account to which Cash payments/refunds from sales made can be posted.
  • Clearing Account - An account to be used as a default account for the assigning/mapping of swiftpos Location/Media combinations.
  • Accounts Receivable/Trade Debtors - An Account to which Customer/Member Account Charges/Payments can be posted.
  • Inventory - Accounts to which Stock Sales generated transaction data can be posted.
• Liability Type Accounts
  • Tax - Accounts to which GST Collected and Paid can be posted.
• Revenue Type Accounts
  • Sales - Accounts to which income/revenue from sales made can be credited.
• Expense Type Accounts
  • Direct Costs Type Accounts
    • Cost of Goods Sold (COGS) - Accounts to which the Cost of Goods from sales can be posted.
  • Expense Type Accounts
    • Stock Adjustments - Accounts to which Stocktake and Stock Adjustments (Receipts, Adjustments, Damaged Goods, Returns, Promotions and Wastage) generated transaction data can be posted.
    • Unders & Overs - An Account to which variances in POS Terminal Cash Offs can be posted.

 

 

Stock Transfers     ▲ ▼

• Stock transfers between Locations that exist in different Location Groups (Venues), is supported when exporting to a single MS Dynamics GP Company file. Obviously MS Dynamics GP Accounts will need to be configured for the Locations in question.

 

 

Track Individual Location Costs     ▲ ▼

• For Location Groups (Venues) that have the Track Individual Location Costs option selected, transfers between Locations will be exported, with the value of the transfer calculated based on the cost of the Products in the From Location (at the time of the Transfer).

 

When Is It Used?      ▲ ▼

Used when Venues want to export swiftpos Sales and Stock Movement transactional data to Microsoft Dynamics GP. The MS Dynamic GP Export will export the following:

• Sales - All Sales, Refunds, Account Charges/Payments, etc.

 

 

• Stock Movements - All Stock Adjustments, Damages, Receipts, Returns, Transfers, Wastage and Stocktake variances.

 

---

How To       ▲ ▼

• Configure For End Of Day Processing
• Export
• Export Multi Venue (LGs) Inventory/Stock Transfers
• GET STARTED
• Import Chart Of Accounts

 

How To Configure For End Of Day Processing       ▲ ▼

To ensure Sales/Stock Movement transaction data is exported daily, ensure the following has been done:

• Complete the steps outlined in What Is Required above.
• Ensure the MS Dynamics GP Export option is selected in the EOD Options screen for the Location Groups (Venues) from which transactions will be exported.
• Confirm the EOD Run Time is correctly configured.
• Confirm the EOD Service is running.
• The first time the End of Day runs, check the Event Logs > swiftpos Services > End of Day Services.

 

Example of the entries expected to be seen in the Event Log

 

How To Export       ▲ ▼

• Before exporting for the first time, or before testing the exporting of data:

  • Backup your MS Dynamics GP data.

• Before exporting in production for the first time:

  • It may be necessary to ensure that all transactions to date are marked as exported. Previous years transactions may not need to be exported and therefore will need to be marked as exported. This can be done by using the Mark ALL as Exported options in the Export screen. This will need to be done for Sales/Stock Movement transaction data.

• Once all transactions have been marked as exported, then reset transactions for the specific Trading Dates that need to be exported to MS Dynamics GP. This can be done using the Reset options in thein the Export screen. This will need to be done for both Sales/Stock Movement transaction data.

• Export the Sales and Stock Movements data for the dates reset. The export files generated will be CSV delimited (.CSV) files, and by default files will be written to the MS Dynamics GP Data Export Path specified against each Location Group (Venue).

 

 

• In MS Dynamics GP import the files generated by the Export. The files generated are named as follows:

  • For Sales/Stock Movement transaction data:
    GENERAL-YYYY_MM_DD_HH_MM_SS_SSS.csv

 

How To Export Multi Venue (LGs) Inventory/Stock Transfers       ▲ ▼

To handle the transfer of Inventory/Stock between Location Groups (Venues), do as follows:

• Ensure that the setup outlined HERE has been completed.
• Ensure the checkbox opposite the appropriate Location Groups (Venues) has been selected. That is, all LGs that will be engaged in Inventory/Stock Transfers, regardless of whether they provide or receive inventory/Stock, or both.
• Run the export for the dates the Transfers took place. If all goes well then for a single transfer from one LG to another, at a minimum, 2 GL Journals should be generated that would look something similar to the following example:
  • 4 bottles of wine with a total cost of $20.00 was transferred from the B&G Bottle Shop location in LG 0 to the LC Conference Room Bar location in LG 1
    • GL Journal generated after exporting for the From LG 0:
      Batch Number,Account No,Line Description,DebitAmount,CreditAmount,Date,InterCompanyDB,InterCompanyTransaction
      SPGP20231024,6301001BGLBSH,Inventory - Beverage; B&G Bottle Shop,0,20,2023/10/24,PANTH,TRUE
      SPGP20231024,8652001BGLBSH,Inter Venue Transfers; B&G Bottle Shop,20,0,2023/10/24,PANTH,TRUE (Clearing Account Entry)
       
    • GL Journal generated after exporting for the To LG 1:
      Batch Number,Account No,Line Description,DebitAmount,CreditAmount,Date,InterCompanyDB,InterCompanyTransaction
      SPGP20231024,6301104LGCCON,Inventory - Beverage; LC Conference Room Bar,20,0,2023/10/24,PANTH,TRUE
      SPGP20231024,8652104LGCCON,Inter Venue Transfers; LC Conference Room Bar,0,20,2023/10/24,PANTH,TRUE (Clearing Account Entry)

 

How To GET STARTED       ▲ ▼

• In Back Office

• • In Global Preferences, In the Other tab, ensure the GL Account Code Format is set. Refer HERE when specifying the value of the GL Account Code format.
  • In Location Group Edit ensure the following:
    
    • Ensure the drop-down Entity field is appropriately selected.
    • Ensure that the MS Dynamics GP Data Export Path is configured for those Location Groups (Venues) from which transactions will be exported.
  • In Location Full Edit ensure the following is configured for all Locations within each Location Group (Venue) from which data is to be exported:
    
    • Ensure the Department field is configured appropriately. 3 characters in length. This field is mandatory.
    • Ensure the Location/Site field is configured appropriately. 6 characters in length. This field is mandatory
    • Ensure the Post Financial Data option is selected.
  • In the Data Portal, ensure that the MS Dynamics GP Accounts are configured in the Accounts tab.  Refer HERE for information on how the GL Account Code that is exported is sourced.
  • In Member Classifications, if required set a Discount Expense Media against Member Classifications. This is optional and once set the Discount Expenses incurred in a sale will be exported using the GL Clearing Account the Discount Expense Media is mapped to.
  • Venues that have multiple Location Groups (Venues) and transfer Inventory/Stock between Location Group (Venues), then it is advised that the following is configured:
    
    • Ensure the Transfer Mapping option is set to Inventory<->Clearing Account in Administration > Global Preferences > Other tab > GL Account section.
    • Ensure the Draw From, Transfer In and Transfer Out Media are mapped to an appropriate Clearing Account in the Clearing Accounts tab.
  • In the Accounts tab of the Settings screen: 
    • Create or import Accounts required to assign/map to swiftpos combinations found in the tabs to the right of the Accounts tab.  Creating Accounts can be done by selecting the Add Row option and importing Accounts can be done by selecting the Import Chart of Accounts option in the Accounts tab. Refer to the Accounts tab for an example of the Accounts required for an initial Export. Obviously, this example list of Accounts can be customised and added to, to suit the individual requirements of Venues.

• • In the Assignment/Mapping tabs found to the right of the Accounts tab, assign/map the Location/Media, Location/Master Group and Location entries in the grid of each tab, to the appropriate MS Dynamics GP Accounts (as created/imported above). This can be done by using the Quick setup options available in the header section of the tabs, or by selecting from the drop-down list of Accounts, listed on the right-hand side of the grid, in each of the tabs. Refer HERE for examples of the assignments/mappings required for one Location

 

 

• Once all assignments/mappings have been completed, testing of the exporting of swiftpos Sales and Stock Movement transactional data to Microsoft Dynamics GP can commence.
• Ensure MS Dynamics GP is backed up before importing from swiftpos. In the event something goes wrong, then at least the MS Dynamics GP data can be restored from the backup.
• It is recommended that all types of Transactions are tested and imported into MS Dynamics GP. It may be necessary to enlist the assistance/expertise of an accountant to ensure that from the MS Dynamics GP end, all transactions are posted correctly.
• Once testing of all types of Transactions has completed, the Dynamics GP export can be optionally included as part of the End of Day process. Refer HERE for more information.
• Refer HERE for information on How To Export.

 

How To Import Chart of Accounts       ▲ ▼

• Select the Import Chart of Accounts option.

• In the Open screen select the CSV file to be imported.

• Select Open.

  • If an error is encountered during the Import, the following prompt will be displayed.
    
    
    
    
  • If SUCCESSFUL, the grid in the Accounts tab will be populated with the imported MS Dynamics GP Accounts. 

 

---

Screens      ▲ ▼

• Export
• Settings

 

Export Screen      ▲ ▼

 

To access select the Export menu option in the Microsoft Dynamics GP menu in Data Portal.

 

 Used to export Sales and Inventory related data from swiftpos for import a Venue's Microsoft Dynamics GP General Ledger.

 

Location Groups Section

• All - Select to ensure the Data in all Location Groups listed in the grid are flagged for Export. Unselect to ensure no Location Groups are selected.
• Export - Select individual Location Groups to ensure Data from those selected is Exported.
• ID/Name - Displays the Location Group ID and Name.
• LG Export Path - Displays the Export Path to which Export files will be written. These are configured in the MS Dynamics GP Data Export Path for each Location Group (Venue).
• Last Result - Displays the result of the last time Data was Exported.

 

Sales/Stock Movement Transaction Data Section

• Location Group - Select from the drop down list the All or one of the Location Groups from which data will be exported.
• Include All unexported up to Trading Date - Select to ensure that all Transactions, up to and including the selected Trading Date, that have not been exported are exported.
• Use LG Export Paths, otherwise use - Select to set the default Export Path. Leave Unselected to use the LG Export Paths as configured in the grid above. Select the Open Folder icon to open and view the content.

 

 

• Trading Date - Select the date for which and export file is to be created.

 

 

• Export - Select to invoke the export. Once completed a prompt will be displayed confirming completion. A file named similar to GENERAL-2018_06_19_13_14_18_023.csv will be generated and written to the Export Path specified above.
• Reset - Select a Trading Date to ensure that General Journal related transactions for that date are flagged as NOT Exported, so that they can be exported again. Select to display the following prompt:

 

• Location Group - Select from the drop down list the Location Group for which data will be reset.
• Trading Date - Select the Trading Date for which General Journal related transactions will be reset.  Resetting is limited to a single Trading Date at a time.

 

 

• Mark ALL as Exported - Select to ensure ALL General Journal related data in swiftpos is marked as exported. This is usually recommended when initially configuring swiftpos to export to MS Dynamics GP for the first time and there is a requirement that all previous General Journal related transactions are marked as Exported.

 

 

Settings Screen      ▲ ▼

 

 

To access select the Settings menu option from the Microsoft Dynamics GP menu in Data Portal.

 

Tabs      ▲ ▼

• Accounts
• Assignment/Mapping Tabs
  • Clearing Accounts
  • Sales Accounts
  • Inventory Accounts
  • Tax Accounts

 

Accounts Tab      ▲ ▼

 

Used to create/import a list of MS Dynamics GP Accounts that will be used by the Export feature to assign/map to swiftpos Location/Media, Location/Master Group combinations and swiftpos Locations in the tabs to the right.

 

 

• Add Row - Select to add a row to the grid

 

 

• Delete Row - Select to delete the currently selected row.
• Import Chart of Accounts - Select to Import the Chart of Accounts (COA) from a CSV file.

 

Clearing Accounts Tab      ▲ ▼

 

Example of the Accounts required for an initial Export.

 

Sales Accounts Tab      ▲ ▼

 

 

Inventory Accounts Tab      ▲ ▼

 

 

Tax Accounts Tab      ▲ ▼

 

 

End of article      ▲