Inventory Stock Details Qty and Value in ax 2012

// 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;
}
}