General Ledger

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     

 

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?      

 

What To Consider?      

 

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.

 

Notes ...

 

swiftpos does not require the whole of the MS Dynamics GP Account list to be setup, as only a small subset of these is actually used by the MS Dynamics GP Export feature.

 

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.

 

Notes ...

 

For sites with multiple Locations, they may choose to create Location specific Accounts. Obviously this list will vary from Venue to Venue and will also be dependent on the Venue's Chart of Accounts (Account List). it is recommended to liaise with Accountants to ensure the Accounts required are configured correctly.

 

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.

 

Notes ...

 

With regards to Account related transactions, the MS Dynamics GP export does not export individual Account (Debtor) specific data but rather exports data that can be imported into MS Dynamics GP in a single Debtors Account (and NOT into individual Debtor Accounts).

 

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

 


How To       

 

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.

 

microsoft-dynamics-gp-12Example 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).

 

Notes ...

 

Once all the above steps have been completed, it is highly recommended to initially only manually export data, and NOT as part of the End of Day process Once all problems have been identified and resolved, then it can be included as part of the End of Day process.

 

  • 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

 

IMPORTANT

 

  It is highly recommended that ALL grid entries in ALL assignment/mapping tabs are assigned/mapped to a MS Dynamics GP Account. Use the Exclude from Export Account (listed as the first entry in the Account drop down lists in the assigning/mapping tabs) to ensure specific Location/Media, Location/Master Group and Location entries are excluded from being exported.

Caution must be taken when using the Exclude from Export Account, as it can potentially result in unbalanced Journals being generated and these will NOT successfully import into MS Dynamics GP. 

 

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

      microsoft-dynamics-gp-11

    • If SUCCESSFUL, the grid in the Accounts tab will be populated with the imported MS Dynamics GP Accounts. 

 


Screens     

 

Export Screen     

 

microsoft-dynamics-gp-2

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

 

microsoft-dynamics-gp-9

 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.

 

Notes ...

 

The Open Folder icon will only be displayed if the Use LG Export Paths option is NOT selected.

 

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

 

Notes ...

 

Once exported, the data exported will be marked as exported. This will ensure that the same data is not exported multiple times.

 

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

 

microsoft-dynamics-gp-10

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

 

Notes ...

 

When resetting and exporting Journals again, be sure that they DO NOT result in duplicate Journals when imported into MS Dynamics GP.

 

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

 

Notes ...

 

For those Venues that have multiple Location Groups (Venues) and do Inventory/Stock transfers between them Location Group (Venues)For those Venues that 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     

 

microsoft-dynamics-gp-3

 

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

 

Tabs     

 

Accounts Tab      

 

microsoft-dynamics-gp-4-1

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.

 

Notes ...

 

swiftpos does not require the whole of the MS Dynamics GP Account list to be setup, as only a small subset of these is actually used by the MS Dynamics GP Export feature.

 

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

 

Notes ...

 

Account Codes added must comply with the GL Account Code Format as outlined HERE. Duplicate Accounts are NOT accepted.

 

  • 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      

 

microsoft-dynamics-gp-5Example of the Accounts required for an initial Export.

 

Sales Accounts Tab      

 

microsoft-dynamics-gp-6

 

Inventory Accounts Tab      

 

microsoft-dynamics-gp-7

 

Tax Accounts Tab      

 

microsoft-dynamics-gp-7

 

End of article