1. Tutorial: Use a sample EHF XML document to produce ACF code

Tutorial: Use a sample EHF XML document to produce ACF code
Summary

For those unfamiliar with EHF (Electronic Trade Format) invoices, it's a comprehensive and intricate format. Crafting these invoices requires meticulous attention to detail, as even minor errors can lead to rejection. To simplify this process, I utilized a newly developed XML/JSON tool for creating ACF code that generates the required XML format. I divided the invoice into manageable sections, creating individual ACF functions for each. These sections include the root, document reference with attachments, supplier, customer, delivery, payment means, tax, legal monetary, and invoice lines. Each function was generated using the tool and customized with specific parameters to replace the sample text from the source XML. This approach, augmented by a helper function for formatting numbers to two decimal places (a requirement for PEPOL validation), significantly streamlined the process, taking just over an hour to assemble the complete document. This method exemplifies the efficiency and time-saving potential of the tool.

1.1. Tool Application Overview ↑

I developed a straightforward FileMaker application featuring a layout with three distinct tabs: "Conversion", "Testing", and "Console". The "Conversion" tab presents two large text fields: the first for inputting XML/JSON source data and the second for displaying the generated ACF code. Additional fields are provided to name the conversion and to specify a function name for the resulting code. A portal on the left side facilitates easy navigation between different parts of the application. A conveniently placed button between the text fields triggers the conversion function.

The "Testing" tab has a similar layout, but with the generated code field repositioned to the left. To the right, there's a sizable field for the output of the tested code. A button is available to initiate code testing, which compiles and executes the code, outputting results in the adjacent field.

The "Console" tab is dedicated to displaying output from the code compilation process. Any compilation errors are prominently displayed in this tab.

This tool simplifies the process of deconstructing complex XML structures into individual records within a table. Users can efficiently generate code for each segment and then integrate these segments into the target ACF document, ultimately assembling a comprehensive solution for processing the full XML structure under examination.

1.2. The produced functions. ↑

1.2.1. A helper function to format numbers with two decimal places. ↑

Without this function, I got validation errors in PEPOL because the default number format has too many decimals. It was required with only 2 decimals.

function EHF_numbers ( float number )
    return format ( "%.2f", number ); 
end

1.2.2. The Invoice Lines section ↑

We call this function for each invoice line. The parameters can for example be pulled from the database using an SQL query,

Function EHF_Make_InvocieLine(int lineNo, string ProdID, string item, float count, float price, float sum, string ref, float VATpercent, string VATcode, string currency)

    XML xmlvar;
    xmlVar["cac:InvoiceLine"] = 
            XML("cbc:ID",lineNo,"cbc:InvoicedQuantity",EHF_numbers(count),"cbc:LineExtensionAmount",EHF_numbers(sum)
            );
    xmlVar["cac:InvoiceLine.cac:OrderLineReference"] = 
            XML("cbc:LineID",ref);
    xmlVar["cac:InvoiceLine.cac:Item"] = 
            XML("cbc:Name",item);
    xmlVar["cac:InvoiceLine.cac:Item.cac:SellersItemIdentification"] = 
            XML("cbc:ID",ProdID);
    xmlVar["cac:InvoiceLine.cac:Item.cac:ClassifiedTaxCategory"] = 
            XML("cbc:ID",VATcode,"cbc:Percent",EHF_numbers(VATpercent));
    xmlVar["cac:InvoiceLine.cac:Item.cac:ClassifiedTaxCategory.cac:TaxScheme"] = 
            XML("cbc:ID","VAT");
    xmlVar["cac:InvoiceLine.cac:Price"] = 
            XML("cbc:PriceAmount",EHF_numbers(price));
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:InvoiceLine.cbc:InvoicedQuantity"], XML("unitCode","NAR","unitCodeListID","UNECERec20"); 
    XMLattributes(xmlVar["cac:InvoiceLine.cbc:LineExtensionAmount"], XML("currencyID",currency); 
    XMLattributes(xmlVar["cac:InvoiceLine.cac:Item.cac:ClassifiedTaxCategory.cbc:ID"], XML("schemeID","UNCL5305"); 
    XMLattributes(xmlVar["cac:InvoiceLine.cac:Price.cbc:PriceAmount"], XML("currencyID",currency); 
    

    return XmlVar;
End

1.2.3. The EHF_Make_Suplier function ↑

This is your client's company.

Function EHF_Make_Suplier(string OrgNr, string CompanyName, string Street, String City, string ZipCode, string Country, string VATcode, string Contact)

    XML xmlvar;
    xmlVar["cac:AccountingSupplierParty.cac:Party"] = 
            XML("cbc:EndpointID",OrgNr);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyName"] = 
            XML("cbc:Name",CompanyName);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PostalAddress"] = 
            XML("cbc:StreetName",Street,"cbc:CityName",City,"cbc:PostalZone",ZipCode
            );
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PostalAddress.cac:Country"] = 
            XML("cbc:IdentificationCode",Country);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyTaxScheme"] = 
            XML("cbc:CompanyID",VATcode);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyTaxScheme.cac:TaxScheme"] = 
            XML("cbc:ID","VAT");
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyLegalEntity"] = 
            XML("cbc:RegistrationName",CompanyName,"cbc:CompanyID",OrgNr);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyLegalEntity.cac:RegistrationAddress"] = 
            XML("cbc:CityName",City);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyLegalEntity.cac:RegistrationAddress.cac:Country"] = 
            XML("cbc:IdentificationCode",Country);
    xmlVar["cac:AccountingSupplierParty.cac:Party.cac:Contact"] = 
            XML("cbc:ID",Contact);
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:AccountingSupplierParty.cac:Party.cbc:EndpointID"], XML("schemeID","NO:ORGNR"); 
    XMLattributes(xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PostalAddress.cac:Country.cbc:IdentificationCode"], XML("listID","ISO3166-1:Alpha2"); 
    XMLattributes(xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyTaxScheme.cbc:CompanyID"], XML("schemeID","NO:VAT"); 
    XMLattributes(xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyLegalEntity.cbc:CompanyID"], XML("schemeID","NO:ORGNR","schemeName","Foretaksregisteret"); 
    XMLattributes(xmlVar["cac:AccountingSupplierParty.cac:Party.cac:PartyLegalEntity.cac:RegistrationAddress.cac:Country.cbc:IdentificationCode"], XML("listID","ISO3166-1:Alpha2"); 
    

    return XmlVar;
End

1.2.4. The EHF_Make_Customer function ↑

This is for the paying customer's details

Function EHF_Make_Customer(string OrgNr, string CustomerNo, string CompanyName, string Street, String City, string ZipCode, string Country, string VATcode, string Contact)

    XML xmlvar;
    xmlVar["cac:AccountingCustomerParty.cac:Party"] = 
            XML("cbc:EndpointID",OrgNr);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyIdentification"] = 
            XML("cbc:ID",CustomerNo);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyName"] = 
            XML("cbc:Name",CustomerNo);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PostalAddress"] = 
            XML("cbc:StreetName","Street","cbc:CityName",City,"cbc:PostalZone",ZipCode);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PostalAddress.cac:Country"] = 
            XML("cbc:IdentificationCode",Country);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyTaxScheme"] = 
            XML("cbc:CompanyID",VATcode);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyTaxScheme.cac:TaxScheme"] = 
            XML("cbc:ID","VAT");
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyLegalEntity"] = 
            XML("cbc:RegistrationName",CompanyName,"cbc:CompanyID","915903037");
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyLegalEntity.cac:RegistrationAddress"] = 
            XML("cbc:CityName",City);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyLegalEntity.cac:RegistrationAddress.cac:Country"] = 
            XML("cbc:IdentificationCode",Country);
    xmlVar["cac:AccountingCustomerParty.cac:Party.cac:Contact"] = 
            XML("cbc:ID",Contact);
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:AccountingCustomerParty.cac:Party.cbc:EndpointID"], XML("schemeID","NO:ORGNR"); 
    XMLattributes(xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyIdentification.cbc:ID"], XML("schemeID","ZZZ"); 
    XMLattributes(xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PostalAddress.cac:Country.cbc:IdentificationCode"], XML("listID","ISO3166-1:Alpha2"); 
    XMLattributes(xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyTaxScheme.cbc:CompanyID"], XML("schemeID","NO:VAT"); 
    XMLattributes(xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyLegalEntity.cbc:CompanyID"], XML("schemeID","NO:ORGNR"); 
    XMLattributes(xmlVar["cac:AccountingCustomerParty.cac:Party.cac:PartyLegalEntity.cac:RegistrationAddress.cac:Country.cbc:IdentificationCode"], XML("listID","ISO3166-1:Alpha2"); 
    
    return XmlVar;
End

1.2.5. The EHF_Make_Delivery function ↑

This is for the delivery address

Function EHF_Make_Delivery(string Street, string City, string ZipCode, string Country)

    XML xmlvar;
    xmlVar["cac:Delivery.cac:DeliveryLocation.cac:Address"] = 
            XML("cbc:StreetName",Street,"cbc:CityName",City,"cbc:PostalZone",ZipCode
            );
    xmlVar["cac:Delivery.cac:DeliveryLocation.cac:Address.cac:Country"] = 
            XML("cbc:IdentificationCode",Country);
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:Delivery.cac:DeliveryLocation.cac:Address.cac:Country.cbc:IdentificationCode"], XML("listID","ISO3166-1:Alpha2"); 
    

    return XmlVar;
End

1.2.6. The EHF_Make_PaymentMeans function ↑

Here we have the invoice number, the due date, and the bank account number...

Function EHF_Make_PaymentMeans(string InvoiceNo, date dueDate, string PaymentMeansCode, string ToAccount)

    XML xmlvar;
    xmlVar["cac:PaymentMeans"] = 
            XML("cbc:PaymentMeansCode",PaymentMeansCode,"cbc:PaymentDueDate",string(dueDate, "%Y-%m-%d"),"cbc:PaymentID",InvoiceNo);
    xmlVar["cac:PaymentMeans.cac:PayeeFinancialAccount"] = 
            XML("cbc:ID",ToAccount);
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:PaymentMeans.cbc:PaymentMeansCode"], XML("listID","UNCL4461"); 
    XMLattributes(xmlVar["cac:PaymentMeans.cac:PayeeFinancialAccount.cbc:ID"], XML("schemeID","BBAN"); 

    return XmlVar;
End

1.2.7. The EHF_Make_TaxTotal function ↑

This function makes the structure for Tax, and breakdown to all the Tax classes.

Function EHF_Make_TaxTotal(float SumTaxAm, array float TaxSubAm, array float Taxable, array string TaxCode, array float taxPercent)
    XML xmlvar;
    xmlVar["cac:TaxTotal"] = 
            XML("cbc:TaxAmount",EHF_numbers(SumTaxAm));

    XMLref cac_TaxSubtotalRef = FindElement(xmlVar["cac:TaxTotal.cac:TaxSubtotal"]);
    int x = sizeof (TaxSubAm ); 
    int i=1; 
    // for (i=1, x)
        xmlVar["cac:TaxTotal"] = XML("cac:TaxSubtotal", XML("cbc:TaxableAmount",EHF_numbers(Taxable[i]),"cbc:TaxAmount",EHF_numbers(TaxSubAm[i]),"cac:TaxCategory",
            XML("cbc:ID",TaxCode[i],"cbc:Percent",EHF_numbers(taxPercent[i]),"cac:TaxScheme",XML("cbc:ID","VAT"))));
        XMLattributes(xmlvar["cac:TaxTotal.cac:TaxSubtotal.cbc:TaxableAmount"], XML("currencyID","NOK"); 
        XMLattributes(xmlvar["cac:TaxTotal.cac:TaxSubtotal.cbc:TaxAmount"], XML("currencyID","NOK"); 
        XMLattributes(xmlvar["cac:TaxTotal.cac:TaxSubtotal.cac:TaxCategory.cbc:ID"], XML("schemeID","UNCL5305"); 
    // end for
    
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:TaxTotal.cbc:TaxAmount"], XML("currencyID","NOK"); 
    
    return XmlVar;
End

1.2.8. The EHFMakeLegalMonetaryTotal function ↑

This is for summing up the invoice structure.

Function EHF_Make_LegalMonetaryTotal(float LineExtAm, float TaxExclAm, float TaxInclAm, float RoundingAm, float PayAm, string currency)

    XML xmlvar;
    xmlVar["cac:LegalMonetaryTotal"] = 
            XML("cbc:LineExtensionAmount",EHF_numbers(LineExtAm),"cbc:TaxExclusiveAmount",EHF_numbers(TaxExclAm),"cbc:TaxInclusiveAmount",EHF_numbers(TaxInclAm)
            ,"cbc:PayableRoundingAmount",EHF_numbers(RoundingAm),"cbc:PayableAmount",EHF_numbers(PayAm));
    // Assign attributes to the XML...
    XMLattributes(xmlVar["cac:LegalMonetaryTotal.cbc:LineExtensionAmount"], XML("currencyID",currency); 
    XMLattributes(xmlVar["cac:LegalMonetaryTotal.cbc:TaxExclusiveAmount"], XML("currencyID",currency); 
    XMLattributes(xmlVar["cac:LegalMonetaryTotal.cbc:TaxInclusiveAmount"], XML("currencyID",currency); 
    XMLattributes(xmlVar["cac:LegalMonetaryTotal.cbc:PayableRoundingAmount"], XML("currencyID",currency); 
    XMLattributes(xmlVar["cac:LegalMonetaryTotal.cbc:PayableAmount"], XML("currencyID",currency); 
    
    return XmlVar;
End

1.2.9. The EHF_MakeAddDocRef ↑

This function is for adding PDF files to the invoice, in the printed format. The parameter is amongst others the file path for the file. The function reads the file and converts it to BASE64 format for the XML tag.

Function EHF_MakeAddDocRef(string id, string docType, string filePath)

    XML xmlvar; 
    string doc, base64text;
    int x; 
    if (file_exists (filePath)) then
        x = open ( filePath, "r"); 
        doc = read ( x );
        close (x);  
        base64text = BASE64_ENCODE ( doc ); 
    
        xmlVar["cac:AdditionalDocumentReference"] = 
                XML("cbc:ID",id,"cbc:DocumentType",docType);
        xmlVar["cac:AdditionalDocumentReference.cac:Attachment"] = 
                XML("cbc:EmbeddedDocumentBinaryObject",base64text);
        // Assign attributes to the XML...
        XMLattributes(xmlVar["cac:AdditionalDocumentReference.cac:Attachment.cbc:EmbeddedDocumentBinaryObject"], XML("mimeCode","application/pdf"); 
    else
        alert ( "Document does not exists"); 
    end if

    return XmlVar;
End

1.3. The Main function ↑

This is the main function last, which calls all the other functions in sequence. I have for simplicity only added hard-coded values here, but it is easy to implement some SQL queries to pull the data needed from the FileMaker database.

Function EHF_GenEHFXML(string InvoiceID)
    string Note = "Konsulentbistand vedr debugging av Diverse";
    date IssueDate = date ( Now() ) ; 
    string OrderRef = "987654";
    string currency = "NOK";

    XML xmlvar;

    xmlVar["Invoice"] = 
            XML("cbc:UBLVersionID","2.1","cbc:CustomizationID",
"urn:www.cenbii.eu:transaction:biitrns010:ver2.0:extended:urn:www.peppol.eu:bis:peppol5a:ver2.0:extended:urn:www.difi.no:ehf:faktura:ver2.0",
            "cbc:ProfileID","urn:www.cenbii.eu:profile:bii05:ver2.0",
            "cbc:ID",InvoiceID,"cbc:IssueDate",string(IssueDate,"%Y-%m-%d"),"cbc:InvoiceTypeCode","380",
            "cbc:Note",Note,"cbc:DocumentCurrencyCode", currency);
    xmlVar["Invoice.cac:OrderReference"] = 
            XML("cbc:ID",OrderRef);
    // Assign attributes to the XML...
    XMLattributes(xmlVar["Invoice"], XML("xmlns","urn:oasis:names:specification:ubl:schema:xsd:Invoice-2",
        "xmlns:cac","urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2",
        "xmlns:cbc","urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"
        ,"xmlns:ccts","urn:un:unece:uncefact:documentation:2","xmlns:qdt",
        "urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2","xmlns:udt",
        "urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2"); 
    XMLattributes(xmlVar["Invoice.cbc:InvoiceTypeCode"], XML("listID","UNCL1001"); 
    XMLattributes(xmlVar["Invoice.cbc:DocumentCurrencyCode"], XML("listID","ISO4217"); 
    
    
    xmlVar["Invoice"] = EHF_MakeAddDocRef ( InvoiceID, "Invoice "+InvoiceID , "~/Desktop/invoice_test.pdf"); 
    xmlVar["Invoice"] = EHF_Make_Suplier("961718192", "My Good Consultant Company", "Vestfaret 77", "FREDRIKSTAD", "1633", "NO", "961718192MVA", "John Doe Jr."); 
    xmlVar["Invoice"] = EHF_Make_Customer("998997993", "00223", "Fredrich Clausen Company ", "Vestavind 66", "OSLO", "0978", "NO", "998997993MVA", "Karl Croch");
    xmlVar["Invoice"] = EHF_Make_Delivery("Vestavind 66", "OSLO", "0978", "NO");
    date dueDate = IssueDate + 14;
    xmlVar["Invoice"] = EHF_Make_PaymentMeans (InvoiceID, string(dueDate, "%Y-&m-%d"), "31", "12345678911"); 
    
    array float TaxSubAm, Taxable, taxPercent;
    array string TaxCode;
    
    float LineExtAm = 1000.0;
    float TaxTotal = 250.0; 
    TaxSubAm[] = TaxTotal;
    Taxable [] = LineExtAm;
    taxPercent[] = 25.0;
    TaxCode [] = "S"; 
    
    xmlVar["Invoice"] = EHF_Make_TaxTotal(TaxTotal, TaxSubAm, Taxable, TaxCode, taxPercent); 
    xmlVar["Invoice"] = EHF_Make_LegalMonetaryTotal(LineExtAm, LineExtAm, LineExtAm+TaxTotal, 0, LineExtAm+TaxTotal, currency);
    
    // Invoice Lines
    //  EHF_Make_InvocieLine(int lineNo, string ProdID, string item, float count, float price, float sum, string ref, float VATpercent, string VATcode, string currency)

    xmlVar["Invoice"] = EHF_Make_InvocieLine(1, "K021", "Consultant Service", 0.5, 1000.0, 500.0, "1234", 25.0, "S", currency);
    xmlVar["Invoice"] = EHF_Make_InvocieLine(2, "K022", "Meeting", 1.0, 500.0, 500.0, "1235", 25.0, "S", currency);

    return string(XmlVar);
End

1.4. The product ↑

It is a long XML, but it validates at PEPPOL. I have added this sample as a download for those interested.

2. Summary ↑

Using so little time to craft all these details is a real time-saver. For most of us, it would be the difference between doing it or getting someone else to do it for many of us. One would use two weeks to craft all these details and get it correct, with a lot of study and trial and error along the way.

Download the demo application to test it for yourself, and have a lot of fun along the way.

Best Regards, Ole Kristian Ek Hornnes.