Tag Archives: Data Import Export Framework

Pending Vendor Invoice Data Import Utility

Recently, I had to develop a data import utility as a part of one of the projects I was working on. One of the subparts of the data import utility is the Pending Vendor Invoices utility. The architecture of the data import utility is as follows:

  • Staging: Data is first import into staging table, where It can be viewed, validated or cleared by the user
  • Validate: Data can be validated in order to ensure the correctness of data
  • Import: Data in the staging tables are processed in the AX tables

I am sharing the Pending Vendor Invoice Import utility here.

Pending Vendor Invoice Import Utility UI:

Pending Vendor Invoice Import Utility UI

Pending Vendor Invoice Import Utility Staging Class:

Pending Vendor Invoice Import Utility Staging ClassPending Vendor Invoice Import Utility Validation Class:

Pending Vendor Invoice Import Utility Validation Class

Pending Vendor Invoice Import Utility Import Class:

Pending Vendor Invoice Import Utility Import Class

Code to Import:

private void ProcessData_Order()

{

SL_StagingPurchInvoice  stagingInvoice,stagingInvoiceCheck;

InventDimId inventDimId;

int         Purchcounter,counter,header;

int64       purchLineRowCount,QuantityLeft;

str         parmId,invoiceAccount,orderAccount,deliveryName;

TradeLineRefId  tableRefId;

 

try

{

header=0;

Purchcounter=0;

ttsBegin;

 

while select sum(Quantity),maxOf(FromDate),maxof(unitcost) from stagingInvoice

group by stagingInvoice.InvoiceNumber,stagingInvoice.PurchaseOrderNumber,stagingInvoice.Site,stagingInvoice.Warehouse,stagingInvoice.productnumber,stagingInvoice.Color,stagingInvoice.Size

where stagingInvoice.RecordStatus == SL_Status::Created

{

QuantityLeft = stagingInvoice.Quantity;

purchTable = PurchTable::find(stagingInvoice.PurchaseOrderNumber);

 

if (header == 0)

{

select recid from vendInvoiceInfoTable where vendInvoiceInfoTable.PurchId == purchTable.PurchId && vendInvoiceInfoTable.Num == stagingInvoice.InvoiceNumber;

if (!vendInvoiceInfoTable.recid)

{

vendInvoiceInfoTable.clear();

vendInvoiceInfoTable.initFromPurchTable(purchTable);

vendInvoiceInfoTable.DocumentOrigin         = DocumentOrigin::Manual;

vendInvoiceInfoTable.CurrencyCode           = purchTable.CurrencyCode;

vendInvoiceInfoTable.DeliveryName           = purchTable.DeliveryName;

vendInvoiceInfoTable.Num                    = stagingInvoice.InvoiceNumber ;

vendInvoiceInfoTable.PurchName              = purchTable.PurchName;

vendInvoiceInfoTable.VendInvoiceSaveStatus  = VendInvoiceSaveStatus::Pending;

vendInvoiceInfoTable.TransDate              = stagingInvoice.FromDate;

vendInvoiceInfoTable.FixedDueDate           = stagingInvoice.FromDate;

vendInvoiceInfoTable.DocumentDate           = stagingInvoice.FromDate;

vendInvoiceInfoTable.Hold                   = NoYes::Yes;

vendInvoiceInfoTable.insert();

header++;

}

}

inventDimId = this.getInventDim(stagingInvoice.Color, stagingInvoice.Size,stagingInvoice.Warehouse,stagingInvoice.Site);

select count(RecId) from purchline where purchLine.PurchId == purchTable.PurchId && purchLine.InventDimId == inventDimId && purchLine.ItemId == stagingInvoice.productnumber;

purchLineRowCount = purchline.RecId;

 

counter=0;

 

while select purchline where purchLine.PurchId == purchTable.PurchId && purchLine.InventDimId == inventDimId && purchLine.ItemId == stagingInvoice.productnumber

{

vendInvoiceInfoLine = VendInvoiceInfoLine::findByPurchLineRefRecId(purchline.RecId);

counter++;

if (!vendInvoiceInfoLine.RecId)

{

if (purchline.QtyOrdered != purchline.calcPendingQtyPurch())

{

vendInvoiceInfoLine.clear();

vendInvoiceInfoLine.initFromPurchLine(purchLine);

vendInvoiceInfoLine.DeliveryName    = vendInvoiceInfoTable.deliveryName;

vendInvoiceInfoLine.ParmId          = vendInvoiceInfoTable.parmId;

vendInvoiceInfoLine.TableRefId      = vendInvoiceInfoTable.tableRefId;

vendInvoiceInfoLine.CurrencyCode    = purchLine.CurrencyCode;

vendInvoiceInfoLine.DeliveryName    = purchLine.DeliveryName;

vendInvoiceInfoLine.LineNum         = purchLine.LineNumber;

vendInvoiceInfoLine.InvoiceAccount  = vendInvoiceInfoTable.invoiceAccount;

vendInvoiceInfoLine.InventDimId     = inventDimId;

vendInvoiceInfoLine.OrderAccount    = vendInvoiceInfoTable.orderAccount;

vendInvoiceInfoLine.ItemId          = purchline.ItemId;

vendInvoiceInfoLine.InventTransId   = purchline.InventTransId;

vendInvoiceInfoLine.DocumentOrigin  = DocumentOrigin::Manual;

vendInvoiceInfoLine.PurchPrice      = stagingInvoice.UnitCost;

vendInvoiceInfoLine.PurchLineRecId  = purchLine.RecId;

vendInvoiceInfoLine.OrigPurchId     = purchTable.PurchId;

 if (purchline.calcPendingQtyPurch() == 0)

{

if (QuantityLeft == purchline.QtyOrdered)

{

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

}

else if (QuantityLeft < purchline.QtyOrdered)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft;

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

 

}

else if (QuantityLeft > purchline.QtyOrdered)

{

if (counter == purchLineRowCount)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft + purchline.calcPendingQtyPurch();

 

}

else

{

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

}

}

}

else

{

if (QuantityLeft == purchline.QtyOrdered)

{

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

}

else if (QuantityLeft < purchline.QtyOrdered)

{

if (purchline.calcPendingQtyPurch() == 0)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft;

QuantityLeft = 0;

}

else if (purchline.calcPendingQtyPurch() >0)

{

vendInvoiceInfoLine.ReceiveNow  = purchline.QtyOrdered – purchline.calcPendingQtyPurch();

QuantityLeft =0;

}

}

else if (QuantityLeft > purchline.QtyOrdered)

{

if (counter == purchLineRowCount)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft+purchline.calcPendingQtyPurch();

 

}

else

{

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered;

}

}

if (counter != purchLineRowCount && vendInvoiceInfoLine.ReceiveNow > purchline.QtyOrdered)

{

vendInvoiceInfoLine.ReceiveNow = purchline.QtyOrdered;

                               }

}

vendInvoiceInfoLine.LineAmount = stagingInvoice.UnitCost*vendInvoiceInfoLine.ReceiveNow;

vendInvoiceInfoLine.insert();

Purchcounter++;

if (QuantityLeft<=0) break;

}

}

}

}

while select forUpdate stagingInvoiceCheck where stagingInvoiceCheck.RecordStatus == SL_Status::Created

{

stagingInvoiceCheck.RecordStatus = SL_Status::Completed;

stagingInvoiceCheck.update();

}

ttsCommit;

}

catch(Exception::Error)

{

Purchcounter=0;

ttsBegin;

while select forUpdate stagingInvoiceCheck where stagingInvoiceCheck.RecordStatus == SL_Status::Created

{

stagingInvoiceCheck.RecordStatus = SL_Status::Completed;

stagingInvoiceCheck.update();

}

ttsCommit;

 

info(strFmt(‘%1′,Exception::Error));

ttsAbort;

}

info(strFmt(‘%1 Lines Processed Created’,Purchcounter));

}

 

 

Troubleshooting Data Import Export Framework Dynamics AX 2012 Error: 0 records have been inserted in Staging Tables

This is one of the errors you get when you are trying to import your Data in AX 2012 through the Data Import Export Framework.

Error in DMF

It is very frustrating to trace that one record, from a large set of data that is the cause of this error. This error usually arises when your primary key is duplicated in the import data file.

Always make sure to perform the following steps before importing your Data in AX 2012 through the Data Import Export Framework. Open your file in excel and select the primary key column. Go to Condition Formatting in the Home tab and select Highlight Cell Rules-> Duplicate Values. This function will highlight the duplicated primary key records. Fix the records before importing your data in AX in order to avoid such errors.