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

Anuncios