erconsult: Electronic Reporting (ER) Cookbook

[Blog bot]

Источник: http://erconsult.eu/blog/electronic-...g-er-cookbook/
==============

The Electronic Reporting module in Dynamics 365 for Operations, previously known as General Electronic Reporting (GER) was one of the coolest application inventions in Dynamics AX7. Programmed apparently in my alma mater – the Dynamics development office in Moscow – this “ER” is a surprisingly powerful tool with an own in-built programming language for transformations. A consultant may configure any vendor payment format or any moderately complex GL export (such as the German GDPdU or French FEC) within days with literally no customizations, and test it in-place.


Over the last year, I implemented the British BACS18, the Swiss DTA, the Spanish SEPA sub-format and the North American ACH outbound payment format. While the AX wiki is a good start, the best way to learn the tool is practice. Below are some transformation code snippets that may help you along the steep learning curve.

Line counters

In the payment files, there are often line numbers. The ER element “Counter” is intended to be used, but it is incremented on every occurrence in the format. I.e. the counter has a global context, and the same count may not be used more than once in a row.
A total of records in a footer section is a special case: you may enumerate the lines with a Counter, then use a function of the Group by type over the data source to get a total count.
With 2 concurrent counters you have no other choice but to use the [real numeric] use the journal line number, and convert it into an integer:
NUMBERFORMAT(VALUE(model.Payments.PaymentIdentifications.SourceBusinessEntityDescription), "00000000000")

Simple transformation formula examples

Convert an amount into an amount with fixed 2 decimal places after the comma
NUMBERFORMAT(model.Payments.InstructedAmount, "#.00")
Remove the decimal separator, produce an amount in pence / cents
REPLACE(NUMBERFORMAT(parameter,"0.00"), ".", "", false)
Remove line breaks from the postal address without a regular expression, and truncate to 70 characters
LEFT(REPLACE(model.Payments.Debtor.PostalAddress.AddressLine, "\n", " ", true), 70)
Remove all non-numeric characters with a regular expression
RIGHT(REPLACE(parameter, "[^0-9]", "", true),6)
REPLACE(model.Payments.DebtorAccount.Identification.Number, "[^0-9]", "", true)

Calculate a weighted check sum

The below expression adds the 9th checksum digit to an US ABA routing number, should the user be entering only the significant 8 digits. The RIGHT(NUMBERFORMAT(10 – VALUE(), “#”, 1) is a crude implementation of modulo 10.
CASE(
LEN(parameter), 9, parameter,
8, parameter&RIGHT(NUMBERFORMAT(10-VALUE(RIGHT(NUMBERFORMAT(
((VALUE(MID(parameter, 1, 1))+VALUE(MID(parameter, 4, 1)))+VALUE(MID(parameter, 7, 1)))*3+
((((VALUE(MID(parameter, 2, 1))+VALUE(MID(parameter, 5, 1)))+VALUE(MID(parameter, 8, 1)))*7+
VALUE(MID(parameter, 3, 1)))+VALUE(MID(parameter, 6, 1))), "#"),
1)),
"#"), 1),
RIGHT(parameter, 9))

Day number in a year (1-366)

The British BACS18 format contained a date format that really made me suffer: for the 1st of February 2016 it expected “ 16032” where 32 was the day count from the 1st of January. The below code took me a while; it uses the Gregorian calendar formula and predates the fact that if you start counting months from March, the length of the months oscillates 31-30-31…
CONCATENATE(" ", DATEFORMAT(parameter, "yy"),
NUMBERFORMAT(
ROUNDDOWN(275 * NUMBERVALUE(DATEFORMAT(parameter, "MM"),".","") / 9, 0) -
(ROUNDDOWN( (NUMBERVALUE(DATEFORMAT(parameter, "MM"),".","") + 9) / 12, 0) *
(1 + ROUNDDOWN( ((NUMBERVALUE(DATEFORMAT(parameter, "yyyy"),".","") –
4 * ROUNDDOWN (NUMBERVALUE(DATEFORMAT(parameter,"yyyy"),".","") / 4, 0) + 2) / 3), 0) ) )+
NUMBERVALUE(DATEFORMAT(parameter, "dd"),".","") - 30,
"000"))

Data model

To master the file format, you must first learn the data model, the ER’s configurable abstraction layer over the Dynamics database. Outbound vendor payments use the Payment model.

table {border-collapse: collapse;} th, td {border: 1px solid black; padding: 10px; text-align: left;}It provides generic payment attributes,

AttributeModel nodePayment date (user parm or journal date)model.Payments.RequestedExecutionDateAmountmodel.Payments.InstructedAmountISO payment currency (journal line currency)model.Payments.CurrencyJournal line number (see Line counters)model.Payments.PaymentIdentifications.SourceBusinessEntityDescriptionPayment / Notemodel.Payments.PaymentsNotesVoucher+RecId (a unique line ID)model.Payments.PaymentIdentifications.EndToEndIdentificationDate of todaymodel.CreationDateTime

our bank details,

AttributeModel nodeOur name (company name)model.Payments.Debtor.NameOur full address (company address)model.Payments.Debtor.PostalAddress.AddressLineName of our bankmodel.Payments.DebtorAgent.NameOur bank account numbermodel.Payments.DebtorAccount.Identification.NumberOur IBANmodel.Payments.DebtorAccount.Identification.IBANRouting number of our bankmodel.Payments.DebtorAgent.RoutingNumberBIC of our bankmodel.Payments.CreditorAgent.BICFI

and their (beneficiary) bank details:

AttributeModel nodeTheir name (vendor’s name)model.Payments.Creditor.NameTheir full address (vendor’s payment address)model.Payments.Creditor.PostalAddress.AddressLineName of their bankmodel.Payments.CreditorAgent.NameTheir bank account numbermodel.Payments.CreditorAccount.Identification.NumberTheir IBANmodel.Payments.CreditorAccount.Identification.IBANRouting number of their bankmodel.Payments.CreditorAgent.RoutingNumberBIC of their bankmodel.Payments.CreditorAgent.BICFIThe post Electronic Reporting (ER) Cookbook appeared first on ER-Consult.



Источник: http://erconsult.eu/blog/electronic-...g-er-cookbook/