Dynamics NAV Mexican Sales and CFDI Part 2

Now the real problem

Codeunit 10145 (in case that you use the Microsoft imposed PAC).

In function LOCAL CreateXMLDocument (changes in yellow).

InitXML(XMLDoc,XMLCurrNode);
WITH TempDocumentHeader DO BEGIN
   AddAttribute(XMLDoc,XMLCurrNode,’version’,CompanyInfo.”CFDI Version No“);
   AddAttribute(XMLDoc,XMLCurrNode,’folio’,”No.”);
   AddAttribute(XMLDoc,XMLCurrNode,’fecha’,DateTimeFirstReqSent);

  AddAttribute(XMLDoc,XMLCurrNode,’sello’,SignedString);
   AddAttribute(XMLDoc,XMLCurrNode,’certificado’,Certificate);
   AddAttribute(XMLDoc,XMLCurrNode,’formaDePago’,GetPmtTermCode(“Payment Terms Code”));
   AddAttribute(XMLDoc,XMLCurrNode,’noCertificado’,CertificateSerialNo);
   AddAttribute(XMLDoc,XMLCurrNode,’subTotal’,FormatAmount(SubTotal));
   IF “Currency Code” <> ” THEN BEGIN
     AddAttribute(XMLDoc,XMLCurrNode,’TipoCambio’,FormatAmount(1 / “Currency Factor”));
     AddAttribute(XMLDoc,XMLCurrNode,’Moneda’,”Currency Code”);
   END;
   AddAttribute(XMLDoc,XMLCurrNode,’total’,FormatAmount(“Amount Including VAT”));
   IF IsCredit THEN
     AddAttribute(XMLDoc,XMLCurrNode,’tipoDeComprobante’,’E’)
   ELSE
     AddAttribute(XMLDoc,XMLCurrNode,’tipoDeComprobante’,’I’);
   AddAttribute(XMLDoc,XMLCurrNode,’metodoDePago’,”Payment Method Code”);
   AddAttribute(XMLDoc,XMLCurrNode,’LugarExpedicion’,CompanyInfo.City);
   IF CompanyInfo.”Bank Account No.” <> ” THEN
     AddAttribute(XMLDoc,XMLCurrNode,’NumCtaPago’,GetBankAccountLastFourChars(CompanyInfo.”Bank Account No.”));

  // Emisor
   WriteCompanyInfo(XMLDoc,XMLCurrNode);

  // Receptor
   XMLCurrNode := XMLCurrNode.ParentNode;
   XMLCurrNode := XMLCurrNode.ParentNode;
   AddElementCFDI(XMLCurrNode,’Receptor’,”,DocNameSpace,XMLNewChild);
   XMLCurrNode := XMLNewChild;
   AddAttribute(XMLDoc,XMLCurrNode,’rfc’,Customer.”RFC No.”);
   AddAttribute(XMLDoc,XMLCurrNode,’nombre’,”Bill-to/Pay-To Name”);
   AddAttribute(XMLDoc,XMLCurrNode,’UsoCFDI’,TempDocumentHeader.”CFDI Use”);
   // Receptor->Domicilio
   AddElementCFDI(XMLCurrNode,’Domicilio’,”,DocNameSpace,XMLNewChild);
   XMLCurrNode := XMLNewChild;
   AddAttribute(XMLDoc,XMLCurrNode,’calle’,”Bill-to/Pay-To Address”);
   AddAttribute(XMLDoc,XMLCurrNode,’colonia’,”Bill-to/Pay-To County”);
   AddAttribute(XMLDoc,XMLCurrNode,’localidad’,”Bill-to/Pay-To City”);
   AddAttribute(XMLDoc,XMLCurrNode,’pais’,Customer.”Country/Region Code”);
   AddAttribute(XMLDoc,XMLCurrNode,’codigoPostal’,”Bill-to/Pay-To Post Code”);

  // Conceptos
   XMLCurrNode := XMLCurrNode.ParentNode;
   XMLCurrNode := XMLCurrNode.ParentNode;
   AddElementCFDI(XMLCurrNode,’Conceptos’,”,DocNameSpace,XMLNewChild);
   XMLCurrNode := XMLNewChild;

  // Conceptos->Concepto
   TempDocumentLine.RESET;
   TempDocumentLine.SETRANGE(“Document No.”,”No.”);
   TempDocumentLine.SETFILTER(Type,'<>%1′,TempDocumentLine.Type::” “);
   IF TempDocumentLine.FINDSET THEN
     REPEAT
       AddElementCFDI(XMLCurrNode,’Concepto’,”,DocNameSpace,XMLNewChild);
       XMLCurrNode := XMLNewChild;

//New SAT Requirement Start
       AddAttribute(XMLDoc,XMLCurrNode,’ClaveProdServ’,TempDocumentLine.”SAT Product/Service Code”);
//New SAT Requirement End
       AddAttribute(XMLDoc,XMLCurrNode,’cantidad’,FORMAT(TempDocumentLine.Quantity,0,9));

//New SAT Requirement
AddAttribute(XMLDoc,XMLCurrNode,’ClaveUnidad’,TempDocumentLine.”Unit of Measure Code”);
//New SAT Requirement
       AddAttribute(XMLDoc,XMLCurrNode,’unidad’,TempDocumentLine.”Unit of Measure Code”);
       AddAttribute(XMLDoc,XMLCurrNode,’noIdentificacion’,TempDocumentLine.”No.”);
       AddAttribute(XMLDoc,XMLCurrNode,’descripcion’,TempDocumentLine.Description);
       AddAttribute(XMLDoc,XMLCurrNode,’valorUnitario’,FormatAmount(TempDocumentLine.”Unit Price/Direct Unit Cost”));
       AddAttribute(
         XMLDoc,XMLCurrNode,’importe’,FormatAmount(TempDocumentLine.Quantity * TempDocumentLine.”Unit Price/Direct Unit Cost”));
      //New SAT Requirement Start
       AddElementCFDI(XMLCurrNode,’Impuestos’,”,DocNameSpace,XMLNewChild);
       XMLCurrNode := XMLNewChild;
         AddElementCFDI(XMLCurrNode,’Traslados’,”,DocNameSpace,XMLNewChild);
         XMLCurrNode := XMLNewChild;
           AddElementCFDI(XMLCurrNode,’Traslado’,”,DocNameSpace,XMLNewChild);
           XMLCurrNode := XMLNewChild;
           AddAttribute(XMLDoc,XMLCurrNode,’Importe’,FormatAmount((TempDocumentLine.Quantity * TempDocumentLine.”Unit Price/Direct Unit Cost”)*1.16));
           AddAttribute(XMLDoc,XMLCurrNode,’TasaOCuota’,FormatAmount(TempDocumentLine.”VAT %”));
           AddAttribute(XMLDoc,XMLCurrNode,’TipoFactor’,’Tasa’);
           AddAttribute(XMLDoc,XMLCurrNode,’Impuesto’,FormatAmount(TempDocumentLine.”VAT %”));

           AddAttribute(XMLDoc,XMLCurrNode,’Base’,FormatAmount(TempDocumentLine.Quantity * TempDocumentLine.”Unit Price/Direct Unit Cost”));
           XMLCurrNode := XMLCurrNode.ParentNode;
         XMLCurrNode := XMLCurrNode.ParentNode;
       XMLCurrNode := XMLCurrNode.ParentNode;

       //New SAT Requirement end
       XMLCurrNode := XMLCurrNode.ParentNode;
     UNTIL TempDocumentLine.NEXT = 0;

  // Impuestos
   XMLCurrNode := XMLCurrNode.ParentNode;
   AddElementCFDI(XMLCurrNode,’Impuestos’,”,DocNameSpace,XMLNewChild);
   XMLCurrNode := XMLNewChild;
   IF IsCredit THEN
     AddAttribute(XMLDoc,XMLCurrNode,’totalImpuestosRetenidos’,FormatAmount(RetainAmt))
   ELSE
     AddAttribute(XMLDoc,XMLCurrNode,’totalImpuestosTrasladados’,FormatAmount(RetainAmt));

  // Impuestos->Traslados/Retenciones
   IF IsCredit THEN
     AddElementCFDI(XMLCurrNode,’Retenciones’,”,DocNameSpace,XMLNewChild)
   ELSE
     AddElementCFDI(XMLCurrNode,’Traslados’,”,DocNameSpace,XMLNewChild);
   XMLCurrNode := XMLNewChild;

  IF TempDocumentLine.FINDSET THEN
     REPEAT
       IF IsCredit THEN
         AddElementCFDI(XMLCurrNode,’Retencion’,”,DocNameSpace,XMLNewChild)
       ELSE
         AddElementCFDI(XMLCurrNode,’Traslado’,”,DocNameSpace,XMLNewChild);
       XMLCurrNode := XMLNewChild;
       AddAttribute(XMLDoc,XMLCurrNode,’impuesto’,’IVA’);
       IF NOT IsCredit THEN
         AddAttribute(XMLDoc,XMLCurrNode,’tasa’,FormatAmount(TempDocumentLine.”VAT %”));
       AddAttribute(XMLDoc,XMLCurrNode,’importe’,
         FormatAmount(TempDocumentLine.”Amount Including VAT” – TempDocumentLine.Amount));
       XMLCurrNode := XMLCurrNode.ParentNode;
     UNTIL TempDocumentLine.NEXT = 0;
END;

In Local function CreateOriginalStr (changes in yellow).

WITH TempDocumentHeader DO BEGIN
   BlobManagement.Init;
   BlobManagement.Write(‘||’ + CompanyInfo.”CFDI Version No” + ‘|’);
   BlobManagement.Write(DateTimeFirstReqSent + ‘|’);
   IF IsCredit THEN
     BlobManagement.Write(FORMAT(‘E’) + ‘|’)
   ELSE
     BlobManagement.Write(FORMAT(‘I’) + ‘|’);
   BlobManagement.Write(GetPmtTermCode(“Payment Terms Code”) + ‘|’);

  IF NOT Export THEN BEGIN
     GetCompanyInfo;
     GetCustomer(“Bill-to/Pay-To No.”);
   END;

  BlobManagement.Write(FormatAmount(SubTotal) + ‘|’);
   IF “Currency Code” <> ” THEN BEGIN
     BlobManagement.Write(FormatAmount(1 / “Currency Factor”) + ‘|’);
     BlobManagement.Write(“Currency Code” + ‘|’);
   END;
   BlobManagement.Write(FormatAmount(“Amount Including VAT”) + ‘|’);
   BlobManagement.Write(“Payment Method Code” + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.City) + ‘|’);  // LugarExpedicion

  IF CompanyInfo.”Bank Account No.” <> ” THEN
     BlobManagement.Write(RemoveInvalidChars(GetBankAccountLastFourChars(CompanyInfo.”Bank Account No.”)) + ‘|’);    // NumCtaPago

  BlobManagement.Write(CompanyInfo.”RFC No.” + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.Name) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.Address) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.County) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.City) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.City) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.County) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.”Country/Region Code”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.”Post Code”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.Address) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.County) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.City) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.County) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.”Country/Region Code”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.”Post Code”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(CompanyInfo.”Tax Scheme”) + ‘|’);

  BlobManagement.Write(Customer.”RFC No.” + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(“Bill-to/Pay-To Name”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(TempDocumentHeader.”CFDI Use”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(“Bill-to/Pay-To Address”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(“Bill-to/Pay-To County”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(“Bill-to/Pay-To City”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(Customer.”Country/Region Code”) + ‘|’);
   BlobManagement.Write(RemoveInvalidChars(“Bill-to/Pay-To Post Code”) + ‘|’);

  TempDocumentLine.SETRANGE(“Document No.”,”No.”);
   TempDocumentLine.SETFILTER(Type,'<>%1′,TempDocumentLine.Type::” “);
   IF TempDocumentLine.FINDSET THEN
     REPEAT
       BlobManagement.Write(TempDocumentLine.”SAT Product/Service Code” + ‘|’);//New SAT Requirement
       BlobManagement.Write(FORMAT(TempDocumentLine.Quantity,0,9) + ‘|’);
       BlobManagement.Write(TempDocumentLine.”Unit of Measure Code” + ‘|’);
       BlobManagement.Write(TempDocumentLine.”No.” + ‘|’);
       BlobManagement.Write(RemoveInvalidChars(TempDocumentLine.Description) + ‘|’);
       BlobManagement.Write(FormatAmount(TempDocumentLine.”Unit Price/Direct Unit Cost”) + ‘|’);
       BlobManagement.Write(FormatAmount(TempDocumentLine.Quantity * TempDocumentLine.”Unit Price/Direct Unit Cost”) + ‘|’);
     UNTIL TempDocumentLine.NEXT = 0;

  IF TempDocumentLine.FINDSET THEN
     REPEAT
       BlobManagement.Write(‘IVA’ + ‘|’);
       IF NOT IsCredit THEN
         BlobManagement.Write(FormatAmount(TempDocumentLine.”VAT %”) + ‘|’);
       BlobManagement.Write(FormatAmount(TempDocumentLine.”Amount Including VAT” – TempDocumentLine.Amount) + ‘|’);
     UNTIL TempDocumentLine.NEXT = 0;

  BlobManagement.Write(FormatAmount(RetainAmt) + ‘||’);
   BlobManagement.Get(TempBlob);
END;

Anuncios

Dynamics NAV Mexican Sales and CFDI

Part One

Since Mexican authorities created the CFDI and electronic accounting regulation, Dynamics NAV lacks the needed changes to accomplish the Mexican rules or has a limited functionality.

What Microsoft needs to do?

Well let´s start with the CFDI

What is a CFDI? It’s a xml file created with every sales invoice and credit memo that is posted.

After this file is created, we need to send it to a authorized certification vendor (PAC). Right now, Dynamics NAV only works with one PAC of the more of 47 PAC´s in the country. That vendor has a very poor support and its very expensive. If Microsoft can remove the actual security connection process or add more connection methods modifying the related dll file, maybe we can use more vendors.

The Actual process in NAV has the next steps

1. You create a Sales Order/Invoice

2. Select your items, quantities, prices, discounts and post the document (or create shipment, this depends on your settings).

3. Then go to history, look for the posted document and click on the send button to send the xml to the PAC and wait for response.

4. If everything runs ok, you send the xml and pdf files to your customer but, if you have any problem, you need to change some status in the table to send the document again.

Let’s start with the problems and changes need it by order.

Dynamics NAV to PAC connection

The connection method is based in soap exchange controlled by certificates and that was good, the problem is Microsoft and the vendor stablish a certificate exchange rule that other vendors don’t use and maybe that was the vendor monopolist definition to avoid other vendors to compete.

What can Microsoft can do? Easy, remove the mandatory use of the certificate exchange in order to send the file to any PAC webservice using only username/password combination. Microsoft needs to modify its connection dll to check which method needs to use, Certificate or username in table 1000 PAC Web Services adding new fields

Field No.

Field Name

Caption

Data Type

Length

Field Class

Option String

57000

Connection Method

Metodo Conexion

Option

Normal

,Certificate,Username/Password

57001

Username

Usuario

Text

50

Normal

57002

Password

Contraseña

Text

50

Normal

001

And make changes in the codeunit 10145 InvokeMethod function which calls codeunit 10147 E-Invoice Object Factory which calls Microsoft.Dynamics.Nav.MX.dll based in the option field Connection Method on table 1000

002

CFDI xml file generation

In this moment, the Mexican partner needs to develop another solution like txt file creation, web service methods, systems that reads SQL tables and return the xml file, etc.

This cause a price increase in all implementation projects and can be fixed very easily by Microsoft and give to the partners more options.

Depending on our government mood, they release new requirements about electronic invoices, for the new CFDI definition (3.3), we need to change some concepts in the xml definition like the item ID; the Mexican government wants that xml file has a new product/service id instead the item number or account number, so it’s very easy to add a new field for this information but we need not only a text field, we need a code field (27100, Product/Service Code) in tables

ID

Name

15

G/L Account

27

Item

37

Sales Line

111

Sales Shipment Line

113

Sales Invoice Line

115

Sales Cr.Memo Line

156

Resource

5600

Fixed Asset

5800

Item Charge

5902

Service Line

5993

Service Invoice Line

5995

Service Cr.Memo Line

With a new table (27002, SAT Product/Service) to create a new catalog like bank or things like that, add the new field to the xml definition and cover this new requirement.

In the table 37 we need to add come code in the T37.”No.” field (in yellow)

CASE Type OF

Type::” “:

BEGIN

“Tax Area Code” := ”;

“Tax Liable” := FALSE;

StandardText.GET(“No.”);

Description := StandardText.Description;

“Allow Item Charge Assignment” := FALSE;

END;

Type::”G/L Account”:

BEGIN

GLAcc.GET(“No.”);

GLAcc.CheckGLAcc;

IF NOT “System-Created Entry” THEN

GLAcc.TESTFIELD(“Direct Posting”,TRUE);

Description := GLAcc.Name;

“Gen. Prod. Posting Group” := GLAcc.”Gen. Prod. Posting Group”;

“VAT Prod. Posting Group” := GLAcc.”VAT Prod. Posting Group”;

“Tax Group Code” := GLAcc.”Tax Group Code”;

“Allow Invoice Disc.” := FALSE;

“Allow Item Charge Assignment” := FALSE;

“SAT Product/Service Code” := GLAcc.“SAT Product/Service Code”;

InitDeferralCode;

END;

Type::Item:

BEGIN

GetItem;

Item.TESTFIELD(Blocked,FALSE);

Item.TESTFIELD(“Gen. Prod. Posting Group”);

IF Item.Type = Item.Type::Inventory THEN BEGIN

Item.TESTFIELD(“Inventory Posting Group”);

“Posting Group” := Item.”Inventory Posting Group”;

END;

Description := Item.Description;

“Description 2″ := Item.”Description 2”;

GetUnitCost;

“Allow Invoice Disc.” := Item.”Allow Invoice Disc.”;

“Units per Parcel” := Item.”Units per Parcel”;

“Gen. Prod. Posting Group” := Item.”Gen. Prod. Posting Group”;

“VAT Prod. Posting Group” := Item.”VAT Prod. Posting Group”;

“Tax Group Code” := Item.”Tax Group Code”;

“Package Tracking No.” := SalesHeader.”Package Tracking No.”;

“Item Category Code” := Item.”Item Category Code”;

“Product Group Code” := Item.”Product Group Code”;

Nonstock := Item.”Created From Nonstock Item”;

“Profit %” := Item.”Profit %”;

“Allow Item Charge Assignment” := TRUE;

PrepaymentMgt.SetSalesPrepaymentPct(Rec,SalesHeader.”Posting Date”);

IF SalesHeader.”Language Code” <> ” THEN

GetItemTranslation;

IF Item.Reserve = Item.Reserve::Optional THEN

Reserve := SalesHeader.Reserve

ELSE

Reserve := Item.Reserve;

“Unit of Measure Code” := Item.”Sales Unit of Measure”;

“SAT Product/Service Code” := Item.“SAT Product/Service Code”;

InitDeferralCode;

SetDefaultItemQuantity;

END;

Type::Resource:

BEGIN

Res.GET(“No.”);

Res.TESTFIELD(Blocked,FALSE);

Res.TESTFIELD(“Gen. Prod. Posting Group”);

Description := Res.Name;

“Description 2″ := Res.”Name 2”;

“Unit of Measure Code” := Res.”Base Unit of Measure”;

“Unit Cost (LCY)” := Res.”Unit Cost”;

“Gen. Prod. Posting Group” := Res.”Gen. Prod. Posting Group”;

“VAT Prod. Posting Group” := Res.”VAT Prod. Posting Group”;

“Tax Group Code” := Res.”Tax Group Code”;

“Allow Item Charge Assignment” := FALSE;

“SAT Product/Service Code” := Res.“SAT Product/Service Code”;

FindResUnitCost;

InitDeferralCode;

END;

Type::”Fixed Asset”:

BEGIN

FixedAsset.GET(“No.”);

FixedAsset.TESTFIELD(Inactive,FALSE);

FixedAsset.TESTFIELD(Blocked,FALSE);

GetFAPostingGroup;

Description := FixedAsset.Description;

“Description 2″ := FixedAsset.”Description 2”;

“Allow Invoice Disc.” := FALSE;

“Allow Item Charge Assignment” := FALSE;

“SAT Product/Service Code” := FixedAsset.“SAT Product/Service Code”;

END;

Type::”Charge (Item)”:

BEGIN

ItemCharge.GET(“No.”);

Description := ItemCharge.Description;

“Gen. Prod. Posting Group” := ItemCharge.”Gen. Prod. Posting Group”;

“VAT Prod. Posting Group” := ItemCharge.”VAT Prod. Posting Group”;

“Tax Group Code” := ItemCharge.”Tax Group Code”;

“Allow Invoice Disc.” := FALSE;

“Allow Item Charge Assignment” := FALSE;

“SAT Product/Service Code” := ItemCharge.“SAT Product/Service Code”;

END;

END;

Proposed SAT Product/Service Table Layout (table key field 1)

Field No

Description

Type

Long

Comments

1

Code

Code

10

Defined SAT code for the product/service

2

Description

Text

50

SAT Description

Example

c_ClaveProdServ

Descripción

01010101

No existe en el catálogo

10101500

Animales vivos de granja

10101501

Gatos vivos

10101502

Perros

10101504

Visón

10101505

Ratas

10101506

Caballos

10101507

Ovejas

10101508

Cabras

10101509

Asnos

In table 27 we need to add a new field called Item Product/Service Code with table relation property with the table/code field.

Same for the next tables

15

G/L Account

156

Resource

5600

Fixed Asset

5800

Item Charge

Table 79 Company Information

In table 79 we need to add a new 27000 field type text (5 chars) to define the xml version (3.3 instead 3.2) for future changes.

Table 309 Series

In table 309 we need to add a new field called “CFDI Type” text 5 chars to define the CFDI document type according the next table

c_UsoCFDI

Descripción

G01

Adquisición de mercancías (Finished Goods)

G02

Devoluciones, descuentos o bonificaciones (This Credit Memo)

G03

Gastos en general (expenses like electrical power, water, etc)

I01

Construcciones (constructions)

I02

Mobilario y equipo de oficina por inversiones

I03

Equipo de transporte (transport equipment)

I04

Equipo de computo y accesorios (computers & related)

I05

Dados, troqueles, moldes, matrices y herramental

I06

Comunicaciones telefónicas

I07

Comunicaciones satelitales

I08

Otra maquinaria y equipo

D01

Honorarios médicos, dentales y gastos hospitalarios.

D02

Gastos médicos por incapacidad o discapacidad

D03

Gastos funerales.

D04

Donativos.

D05

Intereses reales efectivamente pagados por créditos hipotecarios (casa habitación).

Then in the normal operations, we need to add relations between series for the “Posted Invoice Nos.” if the company needs to post different series because the company sells different goods.

END OF THE FIRST PART

OK, what I need to install Dynamics NAV–Part 2

Lets start with the second part.

In the latest post I´ve talked about the memory for the middle tier but we need to check some things about the service.

Each NAV server needs 500 MB of memory to run plus 5 MB per user (active session) even if the sessions are idle and for every page the user opens, you need to allocate more memory.

The server instance has some special parameters to run and give service to the clients.

Data Cache Size – This parameter determines how much memory is used caching data. For a single tenant deployment 9 could be ok.

clip_image001

In NAV 2016 and higher the SQL – NAV interface use ADO.NET instead of ODBC so the resource consumption is lower than prior versions with ODBC.

The cache is a “store in memory” function so when a user read a record, if a second user wants to read the same record, gets it from cache because the cache is shared between the users of the same instance.

NAV use the cache when the system use the following functions

GET

  • FIND
  • FINDFIRST
  • FINDLAST
  • FINDSET
  • COUNT
  • ISEMPTY
  • CALCFIELDS

And there are two types of cache

Global Cache – for all users connected to a Microsoft Dynamics NAV instance.

Private cache – per user, per company, in a transactional scope. Data in a private cache for a given table and company are flushed when a transaction ends.

The cache that is used is determined by the lock state of a table. If a table is not locked than the global cache is queried for data; otherwise, the private cache is queried.

For a call to any of the FIND functions, 1024 rows are cached. You can set the size of the cache by using the Data Cache Size setting in the Microsoft Dynamics NAV Server configuration file. The default size is 9, which approximates a cache size of 500 MB. If you increase this number by one, then the cache size doubles. So, you need to consider how many records can bring back a simple SETRANGE function.

So, from here you can do the math and check how much memory do your Dynamics NAV server needs for one instance and add more memory for development and test instances (if they run in the same server).

OK, what I need to install Dynamics NAV–Part 1

Before the Dynamics NAV installation, we need to check some things related to the infrastructure, users, locations, etc.

Active Directory

Dynamics NAV use AD, SQL and more things like IIS so we need to figure how we can deploy the system, servers and other software. Also, we need to check if the electrical power is consistent or you have many blackouts; same for the internet connection.

Let’s check some scenarios starting with the simplest deployment

Microsoft says that we need an Active Directory. The Active Directory is a very important server because manages all the security and users. For this server you can purchase a low level server with maybe 4-8 GB of RAM, one Xeon processor and a small hard disk (don’t forget the Windows Updates, they need storage space).

This server is queried every time a user tries to access a network share, computer, printer or when you run an application (this is the basic explanation). Nobody recommends to install another service or application in an AD Server so you can buy a cheap server.

Don forget a very basic rule, if you put all the eggs in the same basket, you can lose all your eggs.

So, what can we do? Use to AD Server distributing the FSMO roles in two servers at least. In a Windows environment are five roles

  • Schema Master
  • Domain Naming Master
  • RID Master
  • PDC Emulator
  • Infrastructure Master

You can get more info about these roles in https://support.microsoft.com/en-us/help/223346/fsmo-placement-and-optimization-on-active-directory-domain-controllers

SQL Server

Another server that we need is the Database Server, this server has the SQL Server installation and the database files. We need to check some things for this server:

  • How many Dynamics NAV users are we going to have?
  • How many transactions?

For this server we can start from a dual Xeon based server with 16 GB of RAM for less than 25 users to whatever you can pay.

We need to check the best performance setup for the SQL server, are we going to use SSD or not?

For a SQL server it’s very important to choose the fastest RAID level, the RAID 0 is the fastest because multiple drives are reading and writing data but we don’t have fault tolerance. The fault tolerance can be achieved with RAID 1 which is a “mirroring” RAID. For Dynamics NAV the best approach is to use RAID 10 which combine speed and mirroring and its ideal for highly used database. The problem with the RAID 10 is that you need four drives at least but the SATA and SAS drives prices are getting lower and SSD too.

In SQL Server you need to take into account how many processors and cores you have. Why? Because you can split your log file in partitions and is better to have a log partition in a dedicated disk, as an example, if you have 8 cores, you can split your log file in 8 disk drives (in RAID 10 will be 16) for a better performance.

Also you need to check the SQL Recovery Model, simple, full or bulk logged. https://msdn.microsoft.com/en-us/library/ms189275.aspx

Each model has advantages and disadvantages but the real decision maker point is: do you have personnel with the technical knowledge for use full or bulk logged model? If you don’t have an experienced and dedicated person to do this job, choose the simple model and make daily basis full backups at least.

If you choose the simple model, you can forget the log file partition because the model automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

When you are configuring the SQL server, you need to modify some settings in the server’s settings.

Max Server Memory

The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory, this can severely reduce SQL Server performance, with one of our customers, their infrastructure admins top the Max Server Memory to 28 when the server has a 64 total with more than 50 free, all user start to complain until the max memory was free again.

Auto – Create Statistics

This parameter needs to be enabled to allow SQL to tune itself for a better performance.

Auto – Update Statistics

This parameter needs to be enabled to allow SQL to tune itself for a better performance. Also, many C/AL functions relies in these statistics.

Auto-grow

Leave it enabled.

Max Degree of parallelism

NAV queries are OLTP type so it’s very important to set the MAXDOP in 1 for normal use, if you run maintenance jobs, you can change this parameter to 0, run maintenance and return to 1 when its finished.

As a better performance setting, the best setup can be OS and SQL Server in a disk, the database mdf file in another and the ldf in a third disk (all in RAID 10).

Dynamics NAV Server

Here we have the middle tier. Since Dynamics NAV 2013 to 2017, we have the three tier scenario, DB, NAV and client.

According to Microsoft, we need 2 GB of RAM for the server to give access to 5 users, a very small memory amount, the DB needs to be very small.

In a document posted by Microsoft “https://mbs.microsoft.com/Files/partner/NAV/SalesMarketing/MarketingCollateral/MicrosoftDynamicsNAV2013R2SingleTenantOnPremiseSizingGuidelines.pdf “, we have the next statement

clip_image002

So, the NAV Server needs less memory and we can purchase a little server. The document is a great analysis about the transactions and the process time and effort but they don’t check Web Client or Tablet/Phone app besides they don’t install IIS in the testing server.

In this moment we need to start to think about the client access methods, RoleTailored Client, Web client or Tablet/Phone client. Each one has its owns requirements in memory and can affect the server. In the littlest scenario, you install the IIS in the same NAV Server to publish NAV for all of clients and the Help Server too.

Digital Orphanage or why Microsoft is leaving us

Last weekend I went to see Toruk and in the event the announcer says “if you want to participate in our show, please download the app…” I took my phone without hope and when I search for the app, nothing comes from windows store as I expected.

On Jan 12, Microsoft announces StaffHub but only gave us app for IOS and Android

clip_image002

I have a Lumia 1320, good hardware, great screen, works very good for me. Even Microsoft says “we are going to support our devices to the win10 upgrade” my phone was putted aside and I don’t have any upgrade.

Later Microsoft stops their windows phone sales and remove the devices from stores (I went to San Antonio and cannot found any 950XL in Microsoft stores). Here in Mexico, the greatest carrier stops to selling windows phones.

Why Microsoft stops selling a good product? I can understand that most of the cell users want an iPhone or android based on cost or taste, but Windows has a good features for me, I don’t want to put dog faces in my pics, I use my phone for work, email, sometimes pics, here music, search for places to eat and read twitter. Why can´t Microsoft help us with this?

My twitter app in Windows phone is broken, Waze remove all support for windows devices, I hope that Uber works for at least one year more in Windows.

For my work, I use Dynamics NAV and the app works pretty good, I don’t have any problems installing the security certificate, very good speed displaying the pages. In IOS my coworkers have problems to get the sec certificates, in android, when you install a certificate, the phone puts the screen lock security even if you don’t want.

I don’t want to change to IPhone, it’s very expensive for a pretty phone (I really don’t like it, it feels strange in my hand, you know, the corners) without a real office and every time they change or upgrade the IOS, your phone runs slowly.

Also, I don’t like Android, everybody says “you can do everything in your phone, you can hack the cellphone, you can change whatever you want, etc.”, I just want a phone with a familiar GUI to talk, work, take pictures, hear my music and read without the fire problems.

Maybe is the new Microsoft´s strategy, launch to the market a new product, the people buy it, then Microsoft says “we are retiring from this market” and leave the users orphan (remember the Microsoft Band? I have one too).

Some sites show things about the surface phone, specs, prices, etc. but Microsoft don’t say anything, even a release date.

I do not know if I should continue to have faith in Microsoft.

Did I need to wait and purchase a new device which maybe is going to be set aside again in maybe one or two years?

Or believe in a so called upgradable device which lost its upgradability?

I hope that Microsoft or Terry Myerson says something about the windows phone to us, the people who bought their products.

Last weekend I went to see Toruk and in the event the announcer say “ if you want to participate in our show, please download the app…” i took my phone without hope and when i search for the app, nothing comes from windows store.

Dynamics NAV–Windows Server Compatibility

Hello all

I upgraded my laptop from Windows Server 2012 R2 to Windows Server 2016.

The upgrade process was a full hard disk wipeout and start from scratch.

image

for the SQL

image

Installed Dynamics NAV Versions

  • Microsoft Dynamics NAV 2017
  • Microsoft Dynamics NAV 2016
  • Microsoft Dynamics NAV 2015
  • Microsoft Dynamics NAV 2013 R2
  • Microsoft Dynamics NAV 2013
  • Microsoft Dynamics NAV 2009 R2

image

So, if you want to migrate your servers to the last OS you can do it without problems

Regards

Dynamics NAV e-Purchasing in Mexico

Here in Mexico, we have a government very interested in track all the operations between companies, that´s why we have electronic accounting and electronic invoicing.

With Dynamics NAV we have some problems, the “out-of-the-box” system lacks of some functionality to achieve the goal.

In this post, I’m gonna show the e-purchase functionality designed for the purchase area.

In Mexico we have the CFDI (Internet Digital Fiscal Bill), it’s a xml file with the Sales/purchase transaction information. We have several versions but in this year, the government add information related to the purchased items or services.

With this new data, we can add a special table called “SAT ProdServ Item Relation” and put in that table what we want to post in NAV when an xml file is processed.

clip_image002

What we need to do with this? We develop a new codeunit to process the xml files, we grab the files from a folder, read the file and fill a “cloned purchase order”

clip_image004

As you can see in the image, we make an Imported xml list with some functions:

Import Purch xml – This button grabs the xml files from a folder in your computer.

New Invoice from xml – Because we have all the info in the xml file, we can create a purchase invoice from the file.

Search & Match – Clicking in this button allows NAV to look for an existing Posted Purchase Invoice trying to match the xml to the posted invoices by RFC (Mexican Federal taxpayer registration), invoice amount (with a user-defined variation) and/or invoice date (also with a user-defined variation). If the system found a document, the posted invoice number is added to the list (green formatted line).

If you open the line, you can find the xml imported information that allow us to create a new vendor if we can’t find the RFC in the system.

clip_image006

With the new fields required by the last xml version (3.3) and the “SAT ProdServ Item Relation” we can fill the lines automatically.

clip_image008

Also, we added Job related information to add the purchase to a job and Dimension Control

clip_image010

AS you can see, with this development, we added support to Dynamics NAV to achieve a more automated process for purchase control without “touch” the NAV standard functionality or objects.

Extending possibilities

We added support for Standard Vendor Purchase Codes use with this objects; if you create a purchase standard code for the Vendor, we can use that (for G/L account) to make a Dimension distribution of the expense (as an example, cost center control).

Also we added a validation tool to check if the CFDI is valid using a Web Service of the government sending the required information (which we have in the import process of the xml file). If we have an approved answer for the service we can create the purchase, if we have a rejection from the service, the xml cannot create nor be assigned to a document in the system.

Dynamics NAV 2017 Events

Dynamics NAV 2017 extensions

Well, I’m gonna try to explain some things about extensions and events in Dynamics NAV

First of all, forget your previous assumptions on how do you need to develop anything in NAV, forget to change tables, pages, codeunits. The new development paradigm resides in “create everything new and attach those new things to events”.

Yes, you need to use (purchase) objects.

What we are going to achieve with this new approach? A more stable Dynamics NAV, less problems with developments, an easy upgrade path and maybe, just maybe, some repeatability in the code deployments (this always depends on customer´s ability to understand a standard process).

There is no better way to learn than with a real example.

This is the scenario, in Mexico (with all its stupid’s laws) you need to withhold taxes from certain types of vendors usually called professional services, lease or freights.

This withholding has different types and percentages, for example, if the vendor is a person who gives you a receipt of fees for his work you need to make the next operations

Amount (+) 1000
VAT 16% (+) 160
VAT Withholding 2/3 of VAT (-) 10.67 106.67
Income Tax Withholding (-) 10% 100
Total Amount 953.33

Besides the calculation, you need to store the withholdings in a specific G/L account

Remember you cannot modify the standard tables, pages or codeunits on NAV!!!

My approach is:

Create a table called Withholding Groups

001

Create a “alternate” G/L Account table and in the new table, add some fields to the G/L Account

002

It’s very important to keep G/L Account and the new G/L Account copy synchronized

To achieve this first create a new codeunit (I called eAccSubscribers).

Create a new function called OnGLAccInsert (I used this kind of names to allow a very easy understanding).

003

Once created, click in the Properties button in the menú

004

Local must be No

Event – select Subscriber

EventPublisherObject – I use table 15, btw you can type “t 15” and NAV complete the field when you change to the next field

Event Function – choose OnAfterInsertEvent from the list

005

As you see, you have triggers or events for some actions or processes in NAV

In the next window asking for confirmation click ok.

Now let’s write some code, the subscriber has 2 parameters added by NAV, a Rec and a Boolean, the rec is related to the G/L account table so I´ve added a new variable to my G/L Account table called eAccGL.

The code is

eAccGL.”No.” := Rec.”No.”;

eAccGL.INSERT(TRUE)

What are we do to this moment? When a new G/L Account is inserted in the NAV Table 15, the code takes the t15.”No.” value and inserts the same data in the new table.

Now, you need to create another subscriber to complete the G/L Account information so we create a new function called OnGLAccModify with the next properties

006

Nav adds these parameters

007

We can use a TRANSFERFIELDS but I don’t need all G/L Account fields, just some, so I need to write every field one by one

008

and so on.

You can create an On Delete function.

Now save the codeunit and create a new G/L Account, then run the copied table and you´ll see the added account.

Now, you need to setup the G/L accounts and the withholding groups before start the tricky part, insert new lines before post execution code.

In previous versions you can create a codeunit to check if the G/L account needs to be withholded, and create the new lines in purchase line table and finally, you have some choices, create a button in the menu to call the create lines function (Page modification), add this behavior in the Release button (Codeunit Modification) or modify codeunit 90 (again codeunit modification).

Now we can create the same codeunit to check G/L Account, create lines, etc), but in this version we create a subscriber called CheckIFWHGLAccOnPost, the eventpublisherobject is the codeunit 90 and the event is OnBeforePostPurchaseDoc

009

Add some code to make all calculations and line insertions like this

010

Finally, test the function

Create Purchase Invoice and select the account with withholding

011

Post and check the lines

012

 

About the cloud

Along time ago (nov 2011) I wrote a 5 parts blog post about the cloud, now reading that again, I can’t find something to change my point of view. Cloud is a very expensive way of work, maybe because the cloud is not my computer, is another guy computer.

A few months ago, one of the projects is going to go live and the customer´s technical leader and I made a technical design for the new deployment. We´re talking about 70 -75 concurrent users with at least 5 remote offices, 15 companies with purchase, warehouse, assembly, sales and financial transactions. We are going to deploy Dynamics NAV 2015 with SQL 2014.

Somebody talks something about the “cloud” and everything goes to a new level, a “cloud & azure” partner comes to the scene. It was very funny to see what followed.

That partner sends their “deployment” proposal:

  • AD Server on Azure A2 3.5 GB RAM
  • 1 NAV server on Azure A3 7 GB RAM
  • 1 SQL server on Azure A4 14 GB RAM

They talked about VPNs from physical sites to servers, 20×100,000 storage transactions and 500 GB for data transfer

We laugh a lot and we commented that if he could make a little more realistic scenario.

After that he send a new proposal:

  • AD Server on Azure A2 3.5 GB RAM
  • 1 NAV server on Azure A4 14 GB RAM
  • 1 SQL server on Azure D4 28 GB RAM

As you can see, the proposal only changes a little. Next he sends the third version:

  • AD Server on Azure A2 3.5 GB RAM
  • 1 NAV server on Azure A4 14 GB RAM
  • 1 SQL server on Azure D3 28 GB RAM

And they talked about a growth of 30 users in 2 years (we have 70 users license so we going to have 70 users in the go live, not in 2 years).

When we talked with the partner about our concerns, they say, “everything is going to run fast because we are going to us the NAV server as Remote Desktop Server to give the users the best experience”. More and more laughs. I asked them about the printing problem, the delay when you print, the problem with the UPD of windows server, they had an answer “don’t worry, we can use citrix”, the customer responds “and who is gonna pay for the citrix licenses?”. An awkward silence on the line.

Finally, the customer says thanks and throw the papers in the trash can.

What we finally install??

  • A Dell Server with 256 GB RAM
  • 6 Virtual servers with Hyper V
  • AD Server, electronic invoicing.
  • 1 SQL server with 64 GB RAM and 8 Cores
  • 2 NAV server with 64 GB RAM and 2 cores each (runs LAN instances with windows auth and certificate auth for external access).
  • The SQL has 4 partitions (OS, SQL, Datafile, logfile) all in SSD.
  • 1 backup server with SQL and NAV
  • And in an old physical server a secondary domain controller with a copy of the SQL for backup too.

The server cost was around the half of the azure investment for 2 years and the sizing was designed for 5 years.

There is no complains of slowness by the users.

We have access to NAV with certificates and now we can run in NAV Client, Web Client and Tablet Client.

There are no printing problems (we only use terminal server to manage the servers).

I scheduled a daily maintenance in the SQL server.

What we can learn from this?

  1. Look for a partner who has real experience deploying Dynamics NAV. Not all the azure and cloud partners has the experience or the knowledge to make this well.
  2. Remember and always remember, talking about NAV, SQL and memory, more and bigger it´s better.
  3. Like with the doctor, always look for a second opinion.
  4. Read the small letters (storage transactions ans data transfer).
  5. Learn about new Dynamics NAV technologies (Certificates, webclient, phone client).

Dynamics NAV requirements

Well, Microsoft always give the hardware requirements for the systems they sol, but everybody knows that those specs are to low, maybe they need to put in those documents “to crawl” instead “to run”.

In my experience, talking about 3 tier setup with up to 30 users, you will need for SQL server at least 64 GB RAM, I’m taking in to account snapshots, maintenance, reindexing, update statistics. the NAV server (running 2 instances, LAN/domain instance and remote with certificates instance) needs at least 32 GB of RAM and finally, the client needs at least 4 GB of RAM if you just use word, excel and outlook besides NAV client (Preferably 8).

For 2 tier setup (one server running SQL and NAV with the client running in the cliente computer) you need at least 64 GB of RAM for 30 users.

Remember , Dynamics NAV needs a lots of RAM, you can use 2.2 processors (preferably 2 procs per server) with 2 cores minimum.

The best HDD is SSD, if its to expensive, you can use SATA disks but always in RAID 10 (never use RAID 5, its very slow), you need 4 disks minimum.

Another important thing is the bandwidth, for 30 users maybe 10 Mbps for the NAV server and between NAV and SQL servers a 100 Mbps or 1 GB direct connection.

You can always enhance your NAV experience adding more memory and using less flowfields ;-).