Default General Ledger Template

This document describes the default general ledger template, as well as the general method for how we create exports for General Ledger systems.

General Methodology

Any export from CloudBilling is based on one or more invoices. For the purpose of exports, we generally tend to look at groups of invoices that cover a certain period, e.g., all customer invoices over the month of May. This is also the context we will be assuming throughout this document. 

An invoice in CloudBilling is a collection of pricing rule results, relevant to that invoice, along with some general information regarding the invoice (period etc) and the customer (code, name, metadata, etc.). The export is a representation of the information present on these invoices. For exports we will consider plain text formats: txt(csv), xml, excel (through use of xml).

As mentioned before an export is a representation of information present on the invoice. In practice, this usually means that for every invoice there are some subtotals, a total, and a VAT amount that we want to present on the export to the general ledger system. The invoice however can contain a very large amount of pricing rule results (depending on your setup). We need a generic mechanism to determine which rule results are relevant for the export and a way to associate some information with them (general ledger code, debit/credit, etc.). For this purpose we use a mechanism called “Billing Output Tags” (BOTs in short). BOTs are a property of pricing rules. That is, for each pricing rule we can define a set of BOTs and every result that is generated by that pricing rule, will have these BOTs as a property. This way, we can add specific tags to the rules that generate the subtotals, total, and VAT that we’re interested in. Then we can use the BOTs to identify these results in the export and process them accordingly.

Example

Consider a simple example where we have 3 different product groups in the billing system: Usage, Subscriptions, and Once-offs. There are a lot of different products that each fall into one of these categories, we have pricing rules set up for it all. All we are interested in for the purpose of the export to the GL system though, are the subtotals for Usage, Subscriptions, Once-offs, the total for the invoice and the amount of VAT. Let’s say we’re using the following general ledger codes:

8201 : Usage
8202 : Subscriptions
8203 : Once-Offs
1300 : Debtors
1601 : VAT (21%)

So, for each customer invoice, we want to generate credit entries for the relevant subtotals and the VAT and a debit entry on the debtors account for the total amount. We would have pricing rules as follows:

Note that the fourth rule (SUM on all products) does not have any BOTs. This is due to the fact that this rule generates the total excluding VAT. We don’t require this total anywhere explicitly, so we don’t export it. On the VAT rule (the last one), we’ve listed 2 sets of BOTs. This rule generates 2 results. Say the total excluding VAT (the result of the 4th rule) is 100, then the results of the VAT rule are: 21, and 121. We generate a result for the VAT (the amount added) and for the outcome (the new total). These results correspond to the VAT and the total including VAT respectively. Those are two amounts that we want to use on the general ledger export, so we add tags to them.

Now, on the export we can take the set of results marked with “GL” and for each of them process the GL_Entry tag to generate general ledger entries. The GL_Entry tag has to follow a strict format: “GL_Entry:<code>;<Debit/Credit>;<Description>”. The description field is optional, it can be left empty, e.g., “GL_Entry:8201;Credit;”.

Default Template

CloudBillings default template, which can be used as a basis for exporting to general ledger systems we don’t (yet) support out of the box, is a csv file with the following fields:

Ledger;code;date;debit;credit;description;customer;invoiceNumber;bookingNumber

​The fields are semi-colon separated, we’ll briefly discuss them all here:

Ledger: This defines the ledger (commonly a code or name) that we export to. This is currently hardcoded in the template, since it’s almost always static information.

Code: The general ledger code to journal against. E.g., 1300 for debtors in the example above. This value is obtained from the GL_Entry BOT.

Date: The invoice date, a property of the invoice.

Debit: The debit amount, this value is the “Value” property of the pricing rule result, if the GL_Entry is a Debit entry, it’s 0 if this is a Credit entry.

Credit: Analogous to Debit, “Value” if the result is Credit, 0 otherwise.

Description: The description field from the GL_Entry BOT.

Customer: The “CustomerCode” property of the invoice.

InvoiceNumber: The “InvoiceNumber” property of the invoice. If the underlying invoice is not yet approved (and therefore does not have an invoiceNumber), DRAFT is displayed instead.

BookingNumber: This is a simple counter that is incremented for every invoice. Some GL systems require a unique number to indicate separate bookings.

For every invoice on which the export is based, lines will be generated for every pricing rule result on the invoice with a valid GL_Entry BOT and a GL BOT. That is, each result has to have both these tags in order to be considered for the export.

Rounding

A common error in general ledger exports is rounding. CloudBilling will calculate all numbers on a large amount of decimals to keep values correct. However, general ledger systems can (and should) generally only represent two decimals. This invariably introduces rounding errors. There are a few different scenarios that can be problematic:

1. Prices are quoted to customers including VAT in 2 decimals (common for B2C). In the GL, these values need to be represented excluding VAT on their PNL accounts. The excluding VAT amounts will have more than 2 decimals and will therefore need to be rounded. The problem that occurs is that the sum of the subtotals no longer matches the total excluding VAT and therefore we don’t have balance.
2. Prices are quoted to customers excluding VAT in 2 decimals (common for B2B). The potential problem here is that the VAT amount will have more decimals and will therefore need to be rounded, as will the total including VAT. This problem is addressed inside of CloudBilling though, by adding the option to round intermediate results to a specified number of decimals, exactly for this purpose.

The former case will need to be covered in the general ledger export, since that’s where the problem is introduced (it is where the rounding is done). Different GL systems deal with this differently. Some systems have a feature that automatically balances the books, some systems require you to do it yourself before importing. The standard template offers a mechanism to automate the balancing of the books by creating a debit or credit entry for a ‘rounding differences’ account. This account needs to be created (many GL systems offer one by default) and can be specified in the export template. If the rounding differences code is not specified in the template, the mechanism will be turned off and balancing will be left as a responsibility of the user or the GL system. Do note that this function just balances the bookings (per invoice). It can’t detect whether a difference is actually a rounding difference or some error. Consider for example the situation where an error is made in creating the GL_Entry for “Usage” in the example above. This would cause usage to not be processed by the export and will cause a large entry on the rounding differences account in this case. Therefore it’s always advised to check the bookings on the difference account, they should be on the scale of cents.

Template

The actual template:

@using Kolonel.Domain.Model
@{
    var nlCult = System.Globalization.CultureInfo.CreateSpecificCulture("nl");

    var ledger = "VERK";
    var differenceCode = "4501";
    var differenceDesc = "Afrondingsverschil BTW";
    var booking= 0;
}Ledger;code;date;debit;credit;description;customer;invoiceNumber;bookingNumber
@foreach (var inv in Model.ExportInvoices){
    booking++;//
    if(inv.InvoiceNumber != null){//APPROVED!
        var invNum = inv.InvoiceNumber;
        var date = inv.BillingPeriodStart.ToString("dd-MM-yyyy", nlCult);
        var cust = inv.CustomerCode;
        var lines = inv.InvoiceItems.Where(iit => iit.InvoiceRuleResult.BillingOutputTags.Contains("GL"));
        foreach (var line in lines){
            var lineCodes = line.InvoiceRuleResult.BillingOutputTags.Where(b => b.StartsWith("GL_Entry:"));
            foreach(var lineCode in lineCodes){
                //split lineCode into parts: GL_Code:code;debit/credit;description
                var codeItem = lineCode == null?new[] {""}:lineCode.Substring(9).Split(new[] {";"}, StringSplitOptions.None);
                if(codeItem.Length < 3){
                    continue;//skip this (incorrect GL_Entry format)
                }
                var code = codeItem[0];
                var debitItem = codeItem[1].ToLower()=="debit";
                var debAmt = debitItem?line.InvoiceRuleResult.Value:0;
                var debit = debAmt.ToString("#0.00", nlCult);
                var creditItem = codeItem[1].ToLower()=="credit";
                var credAmt = creditItem?line.InvoiceRuleResult.Value:0;
                var credit = credAmt.ToString("#0.00", nlCult);
                var descItem = codeItem[2];
                var desc = (descItem == null?line.InvoiceRuleResult.ProductTagName:descItem);
@ledger<text>;</text>@code<text>;</text>@date<text>;</text>@credit<text>;</text>@debit<text>;</text>@desc<text>;</text>@cust<text>;</text>@invNum<text>;</text>@booking<text>
</text>}
        }
        if(differenceCode!=""){//Book rounding differences to a specific account
            var c = 0.0;
            var d = 0.0;
            foreach(var line in lines){
                var lineCodes = line.InvoiceRuleResult.BillingOutputTags.Where(b => b.StartsWith("GL_Entry:"));
                foreach(var lineCode in lineCodes){
                    var codeItem = lineCode == null?new[] {""}:lineCode.Substring(9).Split(new[] {";"}, StringSplitOptions.None);
                    if(codeItem.Length < 3){
                        continue;
                    }
                    if(codeItem[1].ToLower() == "debit"){
                        d += Math.Round(line.InvoiceRuleResult.Value, 2, MidpointRounding.AwayFromZero);
                    } else if(codeItem[1].ToLower()=="credit"){
                        c += Math.Round(line.InvoiceRuleResult.Value, 2, MidpointRounding.AwayFromZero);
                    }
                }
            }
            var bal = d-c;
            var zero = 0.ToString("#0.00", nlCult);
            if(bal > 0){//
@ledger<text>;</text>@differenceCode<text>;</text>@date<text>;</text>@bal.ToString("#0.00", nlCult)<text>;</text>@zero<text>;</text>@differenceDesc<text>;</text>@cust<text>;</text>@invNum<text>;</text>@booking<text>
</text>} else if(bal < 0){//
@ledger<text>;</text>@differenceCode<text>;</text>@date<text>;</text>@zero<text>;</text>@Math.Abs(bal).ToString("#0.00", nlCult)<text>;</text>@differenceDesc<text>;</text>@cust<text>;</text>@invNum<text>;</text>@booking<text>
</text>}
        }
    } else {//Not approved.
        var invNum = "DRAFT";
        var date = inv.BillingPeriodStart.ToString("dd-MM-yyyy", nlCult);
        var cust = inv.CustomerCode;
        var lines = inv.InvoiceItems.Where(iit => iit.InvoiceRuleResult.BillingOutputTags.Contains("GL"));
        foreach (var line in lines){
            var lineCodes = line.InvoiceRuleResult.BillingOutputTags.Where(b => b.StartsWith("GL_Entry:"));
            foreach(var lineCode in lineCodes){
                //split lineCode into parts: GL_Code:code;debit/credit;description
                var codeItem = lineCode == null?new[] {""}:lineCode.Substring(9).Split(new[] {";"}, StringSplitOptions.None);
                if(codeItem.Length < 3){
                    continue;
                }
                var code = codeItem[0];
                var debitItem = codeItem[1].ToLower()=="crebit";
                var debAmt = codeItem[1].ToLower()=="debit"?line.InvoiceRuleResult.Value:0;
                var debit = debAmt.ToString("#0.00", nlCult);
                var creditItem = codeItem[1].ToLower()=="credit";
                var credAmt = codeItem[1].ToLower()=="credit"?line.InvoiceRuleResult.Value:0;
                var credit = credAmt.ToString("#0.00", nlCult);
                var descItem = codeItem[2];
                var desc = (descItem == null?line.InvoiceRuleResult.ProductTagName:descItem);
@ledger<text>;</text>@code<text>;</text>@date<text>;</text>@credit<text>;</text>@debit<text>;</text>@desc<text>;</text>@cust<text>;</text>@invNum<text>;</text>@booking<text>
</text>}
        }
        if(differenceCode!=""){//Book rounding differences to a specific account
            var c = 0.0;
            var d = 0.0;
            foreach(var line in lines){
                var lineCodes = line.InvoiceRuleResult.BillingOutputTags.Where(b => b.StartsWith("GL_Entry:"));
                foreach(var lineCode in lineCodes){
                    var codeItem = lineCode == null?new[] {""}:lineCode.Substring(9).Split(new[] {";"}, StringSplitOptions.None);
                    if(codeItem.Length < 3){
                        continue;
                    }
                    if(codeItem[1].ToLower() == "debit"){
                        d += Math.Round(line.InvoiceRuleResult.Value,2, MidpointRounding.AwayFromZero);
                    } else if(codeItem[1].ToLower()=="credit"){
                        c += Math.Round(line.InvoiceRuleResult.Value,2, MidpointRounding.AwayFromZero);
                    }
                }
            }
            var bal = d-c;
            var zero = 0.ToString("#0.00", nlCult);
            if(bal > 0){//
@ledger<text>;</text>@differenceCode<text>;</text>@date<text>;</text>@bal.ToString("#0.00", nlCult)<text>;</text>@zero<text>;</text>@differenceDesc<text>;</text>@cust<text>;</text>@invNum<text>;</text>@booking<text>
</text>} else if(bal < 0){//
@ledger<text>;</text>@differenceCode<text>;</text>@date<text>;</text>@zero<text>;</text>@Math.Abs(bal).ToString("#0.00", nlCult)<text>;</text>@differenceDesc<text>;</text>@cust<text>;</text>@invNum<text>;</text>@booking<text>
</text>}
        }
    }
}