General Ledger
      Back to home
      1. swiftpos Help Guides
      2. 3rd Party Integrations
      3. General Ledger

      GL Mapping

      3rd Party Integration

      Provides a Venue with the ability to export Supplier Invoice related data from swiftpos and import them into their 3rd Party Financial application. Supplier Invoice data refers to completed Supplier Invoices.

      Subjects      ▼

       

      About      ▲ ▼

       

      Available In?      ▲ ▼

      • Depending on the Export format, they were available as follows:

        • Generic - In swiftpos V10+
        • Great Plains (Integration Manager) - In swiftpos V10.35-2+
        • Adept - To be continued...
        • Sage 300 - In swiftpos V10.62+

       

      What Is It?      ▲ ▼

      It is used to export Supplier Invoices into a predefined format, offering Venues to then import these into their 3rd Party Financial software. It provides Venues with a choice of either using the Generic export format, or one of the other export format options available:

      • Great Plains (Integration Manager)
      • Adept
      • Sage 300

       

      What Is Not Supported?      ▲ ▼

      • Exporting of Supplier Invoices via the End Of Day process is NOT supported.

       

      What Is Required?      ▲ ▼

       

      What To Consider?      ▲ ▼

       

      Export File Specifications      ▲ ▼

       

      Generic Export Format      ▲ ▼

      • Name - Bills_YYYYMMDDYYMMSS (For example, Bills_20220824235959)
      • Extension - TXT
      • Type - CSV formatted file
      • Headings - Not applicable
      • Header Record
      • Detail Record

       

      Header Record      ▲ ▼

       

      Field Example Description
      Line Type H Header Record
      Account Type AP Accounts Payable
      Supplier ID Metcash Supplier ID
      Document Number 202208040955 Invoice Number
      Invoice Date 20220804 Invoice Date (YYYYMMDD)
      Invoice ID 94 Unique ID for Invoice
      Spare   For future use
      Spare   For future use
      Spare   For future use
      Spare   For future use
      Spare   For future use
      Tax Total 0.00 For future use
      Invoice Amount 3322.00 Invoice Amount (Inc Tax)

       

      Sample

      H,AP,Metcash,202208040955,20220804,94,,,,,,,0.00,3322.00

       

      Detail Record      ▲ ▼

       

      Field Example Description
      Line Type D Detail Record
      GL Code 1-4100 GL Code
      Spare   For future use
      Tax Amount 0.00 For future use
      Item Amount 2940.00 Item Amount (Exc Tax)

       

      Sample

      D,1-4100,,0.00,2940.00
      D,6-0100,,0.00,10.00
      D,6-0200,,0.00,2.50
      D,6-0300,,0.00,1.00
      D,2-4000,,0.00,368.50

       

      Great Plains (Integration Manager) Export Format      ▲ ▼

      • Name - Supplier invoices.csv
      • Extension - CSV
      • Type - CSV formatted file
      • Headings - First row
      • File Format

       

      The exported file comprises of the following attributes:

      • The first line in the file contains the headings.
      • A carriage return exists after the last line in the file, indicating the end of the file.
      • Values exported are summed by the the grouping of Product Groups (DistributonReference), GL Account Code (DistributionAccount) and then Tax indicator (LineTax).
      • Freight, Rounding, Surcharges and Discounts will be included in separate lines within the file, with the DistributonReference differentiating the type of line. These lines will always be Exc Tax.

       

      File Format      ▲ ▼

       

      Field Description Data Type Data Length Example
      DocumentType If the Invoice total (TotalIncAmount) is positive or zero, the value Invoice will be exported, otherwise the value Return will be exported. Char 10 Invoice OR Return
      VendorID The Supplier ID the Invoice was associated with. Char 10 COCA5
      DocumentNumber Invoice Number entered when receipting the Invoice. Char 15 228458684
      Docate Invoice Date entered when receipting the Invoice. Datetime DD/MM/YYYY 16/05/2022
      TotalIncAmount* Invoice Total including Tax. This is calculated by summing all the Inc Tax values of each Invoice item, rounded to 2 decimal places. +Decimal**   10866.59
      TotalExAmount* Invoice Total excluding Tax. This is calculated by summing all the Exc Tax values of each Invoice item, rounded to 2 decimal places.   +Decimal**   10655.80
      TotalGSTAmount* The Total GST. This is calculated by summing all the GST Tax values of each Invoice item, rounded to 2 decimal places.   +Decimal**   210.79
      DistributionReference The Description of the Product Group assigned to the Products at the time of the export. Char 30 RTDs
      IncAmount The total value (including tax) of the Invoice items that were assigned to the Product Group at the time of the export. Note : The sum total of the IncAmount values exported for the same Invoice should equal the TotalIncAmount for that Invoice. Decimal   733.04
      ExAmount The total value (excluding tax) of the Invoice items that were assigned to the Product Group at the time of the export. Note : The sum total of the ExAmount values exported for the same Invoice should equal the TotalExAmount for that Invoice. Decimal   666.40
      GSTAmount The total value of the tax for the Invoice items that were assigned to the Product Group at the time of the export. Note : The sum total of the GSTAmount values exported for the same Invoice should equal the TotalGSTAmount for that Invoice. Decimal   66.64
      DistributionAccount The GL Account Code as assigned/mapped based assignments/mappings configured in the tabs to the right of the Accounts Setup tab above. Char 20 1-95-9050
      LineTax Indicates whether a exported line item includes tax or not.If the Tax value of the exported line item equals zero, then the value of ‘N’ will be outputted.If the Tax value of the line item does not equal zero, then the value of ‘Y’ will be outputted. Char 1 NOR Y
      TaxableAmount This will be, either or both of the following values: The sum total of all ExAmount values for all exported line items where the LineTax = N for the same Invoice And/Or The sum total of all ExAmount values for all exported line items where the LineTax = Y for the same Invoice +Decimal*   2107.84

      * – Indicated that this value may be out by 1 cent, due to rounding, compared to the SwiftPOS equivalent value. However, it is important the export file balances. Hence, the value is ALWAYS calculated at the time of export, rather than sourcing the total from the Invoicein SwiftPOS. Also, the value exported is ALWAYS an absolute value. That is, regardless of the DocumentType it will be exported as a positive value.  ** – +Decimal denotes the value exported is always an absolute value (that is, a positive value) regardless of the DocumentType.

       

      Sample

      DocumentType,VendorID,DocumentNumber,DocDate,TotalIncAmount,TotalExAmount,TotalGSTAmount,DistributionReference,IncAmount,ExAmount,GSTAmount,DistributionAccount,LineTax,TaxableAmount
      Invoice,Metcash,202208040955,04/08/2022,3322.00,2953.50,368.50,Rounding,1.00,1.00,0.00,6-0300,N,3.50
      Invoice,Metcash,202208040955,04/08/2022,3322.00,2953.50,368.50,Surcharge,2.50,2.50,0.00,6-0200,N,3.50
      Invoice,Metcash,202208040955,04/08/2022,3322.00,2953.50,368.50,Freight,11.00,10.00,1.00,6-0100,Y,2950.00
      Invoice,Metcash,202208040955,04/08/2022,3322.00,2953.50,368.50,Spirits,3138.75,2790.00,348.75,1-4100,Y,2950.00
      Invoice,Metcash,202208040955,04/08/2022,3322.00,2953.50,368.50,Wine,168.75,150.00,18.75,1-4100,Y,2950.00

       

      Adept Export Format      ▲ ▼

       

      File Specification      ▲ ▼

      • Name - Bills_YYYYMMDDHHMMSS (For example, Bills_20220824235959)
      • Extension - CSV
      • Type - CSV formatted file
      • Headings - Not applicable

       

      Header Record      ▲ ▼

       

      Field Example Description
      Line Type D Detail Record
      Intercompany   Leave blank
      GL Expense Account 1-4100  
      Cost Centre 101 GL Division
      Location Description Wine Product Group
      Inc Amount 168.75  
      Tax Amount 18.75  

      Invoice Record      ▲ ▼

       

      Field Example Description
      Line Type I or C Invoice (I) or Credit Note (C)
       Both types may exist in the same file. Credit Notes will have negative values for amounts in the relevant amount fields. For reference I have also attached a file with credit notes CN20220701090532_FIX2.csv
      Suppler Code Metcash Maximum 10 characters
      Invoice No 202208040955  
      Invoice Date 04/08/2022 YYYYMMDD
      Invoice Due Date   Leave blank
      Second Reference   Optional. Could contain a Purchase Order Number.
      Total Invoice Amount (Inc GST) 3322.00 Sum of all Inc Amount values in all D type records.
      Total Tax Amount 368.50 Sum of all Tax Amount values in all D type records.
      Venue Code G1 Group Prefix

      Detail Record      ▲ ▼

       

      Field Example Description
      Line Type D Detail Record
      Intercompany   Leave blank
      GL Expense Account 1-4100  
      Cost Centre 101 GL Division
      Location Description Wine Product Group
      Inc Amount 168.75  
      Tax Amount 18.75  

       

      Sample

      D,,1-4100,101,Wine,168.75,18.75
      D,,1-4100,101,Spirits,3138.75,348.75
      D,,6-0100,101,Freight,11.00,1.00
      D,,6-0200,101,Surcharge,2.50,0.00
      D,,6-0300,101,Rounding,1.00,0.00
      C,Metcash,202208241340,20220824,,,-6834.38,-759.38,G1
      D,,1-4100,103,Spirits,-6631.88,-736.88
      D,,1-4100,103,Wine,-202.50,-22.50
      I,HLW,202208241343,20220824,,,1200.00,0.00,G1
      D,,1-4100,103,Spirits,1200.00,0.00

       

      Sage 300 Export Format      ▲ ▼

       

      File Specification      ▲ ▼

      • Name - Supplier Invoices YYYMMDDHHMMSS.csv (For example, Supplier Invoices 20230920085011.csv)
      • Extension - CSV
      • Type - CSV formatted file
      • Headings - First 5 rows

       

      The exported file comprises of the following attributes:

      • The first 5 lines in the file contains the column names for the 5 record types.

       

      Record Type 1      ▲ ▼

       

      Field Description Data Type Data Length Example
      RECTYPE Record Type 1 Char 1 1
      CNTBTCH        
      CNTITEM        
      IDVEND        
      IDINVC        
      IDTRX        
      ORDRNBR        
      PONBR        
      INVCDESC        
      DATEINVC        
      CODETAXGRP        
      CODETAX1        
      TAXCLASS1        
      AMTTAXDIST        
      AMTINVCTOT        
      AMTTAXTOT        
      AMTGROSTOT        

      Record Type 2      ▲ ▼

       

      Field Description Data Type Data Length Example
      RECTYPE Record Type 2 Char 1 2
      CNTBTCH        
      CNTITEM        
      CNTLINE        
      DESCOMP        
      TEXTDESC        
      AMTTOTTAX        
      SWMANLTX        
      BASETAX1        
      TAXCLASS1        
      SWTAXINCL1        
      AMTTAX1        
      IDGLACCT        
      AMTDIST        

       

      Record Type 3      ▲ ▼

       

      Field Description Data Type Data Length Example
      RECTYPE Record Type 3 Char 1 3
      CNTBTCH        
      CNTITEM        
      CNTPAYM        
      DATEDUE        
      AMTDUE        

      Record Type 4      ▲ ▼

       

      Field Description Data Type Data Length Example
      RECTYPE Record Type 4 Char 1 4
      CNTBTCH        
      CNTITEM        
      OPTFIELD        

       

       

      Record Type 5      ▲ ▼

       

      Field Description Data Type Data Length Example
      RECTYPE Record Type 5 Char 1 5
      CNTBTCH        
      CNTITEM        
      CNTLINE        
      OPTFIELD        

       

      Sample      ▲ ▼

      RECTYPE,CNTBTCH,CNTITEM,IDVEND,IDINVC,IDTRX,ORDRNBR,PONBR,INVCDESC,DATEINVC,CODETAXGRP,CODETAX1,TAXCLASS1,AMTTAXDIST,AMTINVCTOT,AMTTAXTOT,AMTGROSTOT
      RECTYPE,CNTBTCH,CNTITEM,CNTLINE,DESCOMP,TEXTDESC,AMTTOTTAX,SWMANLTX,BASETAX1,TAXCLASS1,SWTAXINCL1,AMTTAX1,IDGLACCT,AMTDIST
      RECTYPE,CNTBTCH,CNTITEM,CNTPAYM,DATEDUE,AMTDUE
      RECTYPE,CNTBTCH,CNTITEM,OPTFIELD
      RECTYPE,CNTBTCH,CNTITEM,CNTLINE,OPTFIELD
      1,1,1,Metcash,202110200803,12,,,,20211020,GST,GST,1,35.1200,351.2000,35.1200,386.3200
      2,1,1,20,,Spirits,35.12,0,351.20,1,1,35.12,1-4100,386.3200
      3,1,1,20,20211020,386.32
      1,1,2,Metcash,202110200807,12,,,,20211020,GST,GST,1,132.2500,1060.0000,132.2500,1192.2500
      2,1,2,20,,Spirits,131.25,0,1050.00,1,1,131.25,1-4100,1181.2500
      2,1,2,40,,Freight,1.00,0,10.00,1,1,1.00,6-0100,11.0000
      3,1,2,40,20211020,1192.25
      1,1,3,Metcash,202110200812,12,,,,20211020,GST,GST,1,140.6300,1127.0000,140.6300,1267.6300
      2,1,3,20,,Spirits,140.63,0,1125.00,1,1,140.63,1-4100,1265.6300
      2,1,3,40,,Rounding,0.00,0,2.00,1,1,0.00,6-0300,2.0000
      3,1,3,40,20211020,1267.63
      1,1,4,Metcash,202110200912,12,,,,20211020,GST,GST,1,44.9600,359.7000,44.9600,404.6600
      2,1,4,20,,Spirits,44.96,0,359.70,1,1,44.96,1-4100,404.6600
      3,1,4,20,20211020,404.66
      1,1,5,Metcash,202110200929,12,,,,20211020,GST,GST,1,44.9600,360.7000,44.9600,405.6600
      2,1,5,20,,Spirits,44.96,0,359.70,1,1,44.96,1-4100,404.6600
      2,1,5,40,,Rounding,0.00,0,1.00,1,1,0.00,6-0300,1.0000
      3,1,5,40,20211020,405.66
      1,1,6,Metcash,2021100937,12,,,,20211020,GST,GST,1,45.9600,369.7000,45.9600,415.6600
      2,1,6,20,,Spirits,44.96,0,359.70,1,1,44.96,1-4100,404.6600
      2,1,6,40,,Freight,1.00,0,10.00,1,1,1.00,6-0100,11.0000
      3,1,6,40,20211020,415.66
      1,1,7,Metcash,2021100942,12,,,,20211020,GST,GST,1,44.9600,364.7000,44.9600,409.6600
      2,1,7,20,,Spirits,44.96,0,359.70,1,1,44.96,1-4100,404.6600
      2,1,7,40,,Surcharge,0.00,0,5.00,1,1,0.00,6-0200,5.0000
      3,1,7,40,20211020,409.66
      1,1,8,Metcash,202110200954,12,,,,20211020,GST,GST,1,44.9600,362.7000,44.9600,407.6600
      2,1,8,20,,Spirits,44.96,0,359.70,1,1,44.96,1-4100,404.6600
      2,1,8,40,,Discount,0.00,0,3.00,1,1,0.00,6-0500,3.0000
      3,1,8,40,20211020,407.66

       

      When Is It Used?      ▲ ▼

      Any swiftpos Venue that wants the ability to export swiftpos Supplier Invoices to their 3rd Party Financial software. It may be useful to Venues that want to make use of the freely available export formats offered.

       

      How To?      ▲ ▼

       

      How To Get Started?      ▲ ▼

      Do as follows:

      • Configure the Accounts
      • Assign/Map the Accounts
      • Mark As Exported
      • Reset Trading Dates
      • Export Supplier Invoices
        Using one of the following Export Formats:
        • Generic
        • Great Plains (Integration Manager)
        • Adept
        • Sage 300

       

      Screens      ▲ ▼

       

      Purchases Export Screen      ▲ ▼

       

       

      To access select the Purchase Export menu option in the GL Mapping menu in Data Portal. Used to export completed Supplier Invoice data.


       

      Notes ...


      Invoices completed and having the same Posting Date as that of the selected Trading Date, will be selected for Export. Also, DO NOT enter Credit Notes into swiftpos as negative Supplier Invoices. Instead, it is recommended to create a Credit Note using the Credit Note Type option available when entering Supplier Invoices.

       

      • Export Folder - Displays/Set the folder to which exported files will be written. Select the icon on the right to select a folder.

       

      Notes ...


      Once exported, the data will be flagged as exported and the date will be removed from the Trading Date drop down list. This will ensure that the same data is NOT exported multiple times. It is important to note that even though the export for a specific Trading Date may NOT be completely successful, the Trading Date will still be removed from the drop-down list. It is therefore VERY IMPORTANT to always review the Event Logs to ensure that any ERRORS are investigated and resolved in an appropriate manner.

       

      • Invoice Date to Export - Select from the drop-down list of dates the date for which Supplier Invoices are to be exported. This option is only available when the Generic Export Format option is selected above.
      • Export - Select to commence the export of data. Once completed a prompt will be displayed confirming completion. The data will be exported and written to the Export Folder specified. Refer to Export File Specifications for more information.

       

      Notes ...


      Once exported, the data exported will be flagged as exported and the date will be removed from the drop-down list. This will ensure that the same data is not exported multiple times.

      • Reset - Select to ensure that Supplier Invoice related data for the selected Trading Date is flagged as NOT Exported, so that they can be exported again. Select to display the following prompt:

       

      • Continued ...
        • Trading Date - Select the Trading Date that needs to be reset. That is, flagged as NOT exported.

       

      Notes ...


      When resetting and exporting again, be sure that they DO NOT result in duplicates when imported into XERO.

      • Mark As Exported - Select to display the following prompt and select a range of Trading Dates to ensure all Supplier Invoice related data in the selected date range is flagged as exported.

       

      Notes ...


      This is usually recommended when initially configuring swiftpos to export using this interface for the first time and wanting to ensure that Supplier Invoice prior to a specific Trading Date are NOT exported. Also, it is useful when there is a requirement that all existing Supplier Invoice related transactions for a selected date range need to be flagged as exported.

      • Continued ...
        • From/To - Select the Trading Date date range for which data will be flagged as exported.

       

      Settings Screen      ▲ ▼

      Accessed by selecting the Settings menu option in the GL Mapping menu in Data Portal.

      Provides a Venue with the ability to configure the settings required to be able to make use of the exporting options available in the Purchase Export menu option.

      Tabs      ▲ ▼

       

      Account Setup Tab      ▲ ▼

       

       

      Grid Section

      Displays a list of Account Codes that are currently available to be selected when assigning/mapping Accounts in the tabs to the right of the Account Setup tab.

      • Account Code
      • Description
      • GL Account Group

       

      Footer Section 

      • Add Row - Select to add a row to the grid.
      • Delete Row - Select to delete the currently selected row in the grid.

       

      Account Assign / Mapping Tabs      ▲ ▼

      Using the Account assignment/mapping tabs to the right of the Account Setup tab, to assign/map swiftpos Location/Media, Location/Master Group and Location entries in the grid of each tab, to the appropriate Account Code (as configured in the Account Setup tab). 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.

       

      Notes ...


      It is recommended that all rows, in each of the tabs, be assigned/mapped to an Account.

       

      Media / Account Assignment Tab      ▲ ▼

       

      Example of Account assignments/mappings for SwiftPOS Location/Media combinations in ONE Location

      Used to assign/map Media and other Accounts to SwiftPOS Location/Media combinations.

       

      Filters Section

      • GL Account Code - Select from the drop down list the XERO Account that will be used to assign/map to the SwiftPOS Location/Media combination.

       

      Apply To Section

      Use to assign/map the selected XERO Account to the rows in the grid by using one of the following options:

      • Selected Rows - Select to assign/map to all rows that are currently selected in the grid.
      • Unassigned Rows - Select to assign/map to all rows that are currently DO NOT have an Account assigned/mapped in the grid.
      • Selected Location - Select to assign/map to all rows in the grid that are associated with a selected Location.
      • Copy Location - Select to copy the assignments/mappings of a selected Location to another.

      Grid Section

      Displays a list of Location/Media combinations that are required to be assigned/mapped to an Account Code.

       

      Group / Account Assignment Tab      ▲ ▼

       

      Used to assign/map Cost of Goods and Revenue Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to a Cost of Goods Account and a Revenue Account.

       

      Inventory / Purchase Assignment Tab      ▲ ▼

       

      Used to assign/map Inventory/Purchase Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      Freight Expense Assignment Tab      ▲ ▼

       

      Used to assign/map Freight Expense Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      Surcharge Expense Assignment Tab      ▲ ▼

       

      Used to assign/map Surcharge Expense Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      Discount Account Tab      ▲ ▼

       

       

      Used to assign/map Discount Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       


      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      Bonus Discount Assignment Tab      ▲ ▼

       

      Used to assign/map Bonus Discount Accounts to SwiftPOS Location/Master Group combinations.

       

        Filters Section 

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      Rounding Expense Assignment Tab      ▲ ▼

       

      Used to assign/map Rounding Expense Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      Purchases Tax Assignment Tab      ▲ ▼

       

      Used to assign/map Purchase Tax Accounts to SwiftPOS Location/Master Group combinations.

       

      Filters Section

      Refer HERE for more information.

       

      Apply To Section

      Refer HERE for more information.

       

      Grid Section

      Displays a list of Location/Master Group combinations that are required to be assigned/mapped to an Account Code.

       

      End of article.      ▲