// Inventory Movement Report Detail/Summary Qty and Value in ax 2012
//Sample Codeand view only
// Need to modify Query and code as per your requirment
static void InventoryStockDetails(Args _args)
{
InventDim inventDim;
Query query;
int j;
ReportTmpTable reportStagTable,reportStagIns;
InventValueReportView inventvalueReportView;
InventSum inventSum;
InventTransOrigin transOrigin;
Qty GetQtyByOpenDate(ItemId _item,InventDimId _inventDimId)
{
Qty total;
InventValueReportView trans;
;
select sum(Qty) from trans
where trans.TransDate < mkDate(1,1,2015)
&& trans.TransDate != dateNull()
&& trans.ItemId == _item
&& trans.inventDimId == _inventDimId;
total = trans.Qty;
return total;
}
Amount GetCostAmountbyOpenDate(ItemId _item,InventDimId _inventDimId)
{
Amount total;
InventValueReportView trans;
;
select sum(Amount) from trans
where trans.TransDate < mkDate(1,1,2015)
&& trans.TransDate != dateNull()
&& trans.ItemId == _item
&& trans.inventDimId == _inventDimId;
total = trans.Amount;
return total;
}
;
ttsbegin;
//Clear Report tmp Table
delete_from reportStagTable;
//End Code
contract = this.parmDataContract();
while select
inventSum by InventDimid,ItemId
where inventSum.itemId == _itemId
join inventDim
where inventDim.inventDimid == inventSum.inventDimId
{
reportStagTable.clear();
inventDim.clear();
inventDim = InventDim::find(this.CreateInventDim(inventDim.InventSiteId,
inventDim.InventLocationId,
inventDim.configId,
inventDim.InventSizeId,
inventDim.InventStyleId).inventDimId);
reportStagTable.InventDimId = inventDim.inventDimId;
reportStagTable.OnHandQty = GetQtyByOpenDate(reportStagTable.ItemId,inventDim.inventDimId);
reportStagTable.BeginQty = reportStagTable.OnHandQty;
reportStagTable.BeginCostAmount = GetCostAmountByOpenDate(reportStagTable.ItemId,inventDim.inventDimId);
reportStagTable.Parent = NoYes::Yes;
reportStagTable.Sort = 1;
reportStagTable.OnHandQtyOfEnd =0;
reportStagTable.PurchQty = 0;
reportStagTable.ReceivedQty = 0;
reportStagTable.PurchReturn = 0;
reportStagTable.SalesReturn = 0;
reportStagTable.SoldQty = 0;
reportStagTable.ProdInQty =0;
reportStagTable.ProdOutQty =0;
reportStagTable.AdjInQty = 0;
reportStagTable.AdjOutQty =0 ;
reportStagTable.BOMInqty =0;
reportStagTable.BOMOutQty = 0;
reportStagTable.CountInQty =0;
reportStagTable.CountOutQty =0;
reportStagTable.TransferInQty = 0;
reportStagTable.TransferOutQty =0;
reportStagTable.IssuedQty =0;
reportStagTable.RecPurchAmount = 0;
reportStagTable.IsuPurchReturnAmount = 0;
reportStagTable.RecSalesReturnAmount = 0;
reportStagTable.IsuSalesAmount = 0;
reportStagTable.RecProdAmount =0;
reportStagTable.IsuProdAmount =0;
reportStagTable.RecAdjAmount = 0;
reportStagTable.IsuAdjAmount=0 ;
reportStagTable.RecBOMAmount =0;
reportStagTable.IsuBOMAmount = 0;
reportStagTable.RecCountAmount =0;
reportStagTable.IsuCountAmount =0;
reportStagTable.RecTransferInAmount = 0;
reportStagTable.IsuTransferOutAmount =0;
reportStagTable.PurchasedQtyTotal = reportStagTable.PurchQty + reportStagTable.SalesReturn + reportStagTable.ProdInQty
+ reportStagTable.BOMInqty + reportStagTable.CountInQty + reportStagTable.AdjInQty + reportStagTable.TransferInQty;
reportStagTable.SoldQtyTotal = reportStagTable.SoldQty + reportStagTable.PurchReturn + reportStagTable.ProdOutQty
+ reportStagTable.BOMOutQty + reportStagTable.CountOutQty + reportStagTable.AdjOutQty + reportStagTable.TransferOutQty;
reportStagTable.TotalcloseQty = reportStagTable.OnHandQty + reportStagTable.PurchasedQtyTotal + reportStagTable.SoldQtyTotal;
reportStagTable.ReceivedCostAmount = reportStagTable.RecPurchAmount + reportStagTable.RecSalesReturnAmount
+ reportStagTable.RecAdjAmount + reportStagTable.RecProdAmount + reportStagTable.RecCountAmount
+ reportStagTable.RecBOMAmount + reportStagTable.RecTransferInAmount;
reportStagTable.IssuedCostAmount = reportStagTable.IsuSalesAmount + reportStagTable.IsuPurchReturnAmount
+ reportStagTable.IsuAdjAmount + reportStagTable.IsuProdAmount + reportStagTable.IsuCountAmount
+ reportStagTable.IsuBOMAmount + reportStagTable.IsuTransferOutAmount;
reportStagTable.TotalCostAmount = reportStagTable.BeginCostAmount + reportStagTable.ReceivedCostAmount + reportStagTable.IssuedCostAmount;
reportStagTable.Insert();
}
//Inserting Detail level transation
while select reportStagTable
where reportStagTable.Parent == NoYes::Yes
{
j = 2;
inventvalueReportView.clear();
transOrigin.clear();
while select inventvalueReportView order by TransDate Asc
where inventvalueReportView.TransDate >= mkDate(01,01,2015)
&& inventvalueReportView.TransDate <= mkDate(1,1,2016)
&& inventvalueReportView.ItemId == reportStagTable.ItemId
&& inventvalueReportView.inventDimId == reportStagTable.InventDimId
{
reportStagIns.clear();
reportStagIns.ItemId = reportStagTable.ItemId;
reportStagIns.UnitId = InventTableModule::find(reportStagTable.ItemId,ModuleInventPurchSales::Invent).UnitId;
reportStagIns.ItemGroupId = reportStagTable.ItemGroupId;
reportStagIns.ItemName = reportStagTable.ItemName;
reportStagIns.InventDimId = reportStagTable.InventDimId;
reportStagIns.configId = reportStagTable.configId;
reportStagIns.InventSizeId = reportStagTable.InventSizeId;
reportStagIns.InventStyleId = reportStagTable.InventStyleId;
reportStagIns.InventSiteId = reportStagTable.InventSiteId;
reportStagIns.InventLocationId = reportStagTable.InventLocationId;
reportStagIns.Parent = NoYes::No;
reportStagIns.Sort = j;
reportStagIns.OnHandQty = 0;
reportStagIns.BeginQty = 0;
switch(inventvalueReportView.ReferenceCategory)
{
case InventTransType::Purch :
if(inventvalueReportView.Qty > 0)
reportStagIns.PurchQty = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.PurchReturn = inventvalueReportView.Qty;
break;
case InventTransType::Sales :
if(inventvalueReportView.Qty > 0)
reportStagIns.SalesReturn = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.SoldQty = inventvalueReportView.Qty;
break;
case InventTransType::ProdLine || InventTransType::Production:
if(inventvalueReportView.Qty > 0)
reportStagIns.ProdInQty = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.ProdOutQty = inventvalueReportView.Qty;
break;
case InventTransType::InventLossProfit:
if(inventvalueReportView.Qty > 0)
reportStagIns.AdjInQty = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.AdjOutQty = inventvalueReportView.Qty;
break;
case InventTransType::BOMLine || InventTransType::BOMMain:
if(inventvalueReportView.Qty > 0)
reportStagIns.BOMInqty = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.BOMOutQty = inventvalueReportView.Qty;
break;
case InventTransType::InventTransfer :
if(inventvalueReportView.Qty > 0)
reportStagIns.TransferInQty = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.TransferOutQty = inventvalueReportView.Qty;
break;
case InventTransType::InventTransaction :
if(inventvalueReportView.Qty > 0)
reportStagIns.CountInQty = inventvalueReportView.Qty;
else if(inventvalueReportView.Qty < 0)
reportStagIns.CountOutQty = inventvalueReportView.Qty;
break;
}
//For Cost Value
switch(inventvalueReportView.ReferenceCategory)
{
case InventTransType::Purch :
if(inventvalueReportView.AMount > 0)
reportStagIns.RecPurchAmount = inventvalueReportView.AMount;
else if(inventvalueReportView.AMount < 0)
reportStagIns.IsuPurchReturnAmount = inventvalueReportView.AMount;
break;
case InventTransType::Sales :
if(inventvalueReportView.Amount > 0)
reportStagIns.RecSalesReturnAmount = inventvalueReportView.Amount;
else if(inventvalueReportView.Amount < 0)
reportStagIns.IsuSalesAmount = inventvalueReportView.Amount;
break;
case InventTransType::ProdLine || InventTransType::Production:
if(inventvalueReportView.Amount > 0)
reportStagIns.RecProdAmount = inventvalueReportView.Amount;
else if(inventvalueReportView.Qty < 0)
reportStagIns.IsuProdAmount = inventvalueReportView.Amount;
break;
case InventTransType::InventLossProfit:
if(inventvalueReportView.Amount > 0)
reportStagIns.RecAdjAmount = inventvalueReportView.Amount;
else if(inventvalueReportView.Amount < 0)
reportStagIns.IsuAdjAmount = inventvalueReportView.Amount;
break;
case InventTransType::BOMLine || InventTransType::BOMMain:
if(inventvalueReportView.Amount > 0)
reportStagIns.RecBOMAmount = inventvalueReportView.Amount;
else if(inventvalueReportView.Amount < 0)
reportStagIns.IsuBOMAmount = inventvalueReportView.Amount;
break;
case InventTransType::InventTransfer :
if(inventvalueReportView.Amount > 0)
reportStagIns.RecTransferInAmount = inventvalueReportView.Amount;
else if(inventvalueReportView.Amount < 0)
reportStagIns.IsuTransferOutAmount = inventvalueReportView.Amount;
break;
case InventTransType::InventTransaction :
if(inventvalueReportView.Amount > 0)
reportStagIns.RecCountAmount = inventvalueReportView.Amount;
else if(inventvalueReportView.Amount < 0)
reportStagIns.IsuCountAmount = inventvalueReportView.Amount;
break;
}
//For total Received , Issued Total Qty, COst Amount
reportStagIns.PurchasedQtyTotal = reportStagIns.PurchQty + reportStagIns.SalesReturn + reportStagIns.ProdInQty
+ reportStagIns.BOMInqty + reportStagIns.CountInQty + reportStagIns.AdjInQty + reportStagIns.TransferInQty;
reportStagIns.SoldQtyTotal = reportStagIns.SoldQty + reportStagIns.PurchReturn + reportStagIns.ProdOutQty
+ reportStagIns.BOMOutQty + reportStagIns.CountOutQty + reportStagIns.AdjOutQty + reportStagIns.TransferOutQty;
reportStagIns.TotalcloseQty = reportStagIns.OnHandQty + reportStagIns.PurchasedQtyTotal + reportStagIns.SoldQtyTotal;
reportStagIns.ReceivedCostAmount = reportStagIns.RecPurchAmount + reportStagIns.RecSalesReturnAmount
+ reportStagIns.RecAdjAmount + reportStagIns.RecProdAmount + reportStagIns.RecCountAmount
+ reportStagIns.RecBOMAmount + reportStagIns.RecTransferInAmount;
reportStagIns.IssuedCostAmount = reportStagIns.IsuSalesAmount + reportStagIns.IsuPurchReturnAmount
+ reportStagIns.IsuAdjAmount + reportStagIns.IsuProdAmount + reportStagIns.IsuCountAmount
+ reportStagIns.IsuBOMAmount + reportStagIns.IsuTransferOutAmount;
reportStagIns.TotalCostAmount = reportStagIns.BeginCostAmount + reportStagIns.ReceivedCostAmount + reportStagIns.IssuedCostAmount;
reportStagIns.insert();
j++;
}
}
ttsCommit;
}
}