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));

}

 

 

About the author

Fatima Zafar is an Associate Software Engineer in the Dynamics Team at Systems Limited. She is extremely passionate about her work and loves to explore development platforms and tools. As a technology oriented person, she has under her belt a wide range of experiences including technical writing, tutorial development and blogging for technology and has dabbled (significantly well) with X++, .NET and MySQL. Currently working as a Microsoft Dynamics AX developer, she aims to explore as many areas of Dynamics as she can. Other than AX, her passions include graphic designing and she would like to work more on that front as well.

COMMENTS (4)
Reply

I’ll be that vendor

Reply

Very informative

Can you please share the file format?

Reply

Wow Awesome! Is it possible for you to post more frequently, it would be a huge gift for the readers! :D

Hi Kirsten,

Thank you for contacting us.
We will get back to you on email with more details!

Regards

Leave a Comment

Name (required)
Email (required)
Comment (required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>