Posts tagged with: Open Vendor Invoices

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

}