AX2012报表开发-超额库存报表

一、需求分析

1.路径

2.界面

3.样张

4.取数逻辑

（1）当前时间倒算前6个月无库存交易记录的Item。
（2）当前时间前6个月且发货状态为已扣除和已售出的Item。

二、报表开发

1.创建临时表

2.创建数据合约类

（1）定义变量

（2）创建日期参数

（3）创建前6月期间参数

3.创建DP类

（1）定义变量

（2）数据处理

[
    SysEntryPointAttribute(false)
]
public void processReport()
{
    // 定义合约类
    HCN_StockReportsContract                      contract;


    // 定义变量
    //DatePhysical               datePhysical;
    ItemId                         _aireraftOne;
    ItemId                         _aireraftTwo;
    ItemId                         _aireraftTree;
    TransDate                   _asOfDate;
    ItemGroupId                 _itemGroupId;
    ItemId                      _itemId;
    Description                 _description;
    Price                       _avgPrice;
    Qty                         _actualStock_Qty;
    Amount                      _actualStock_Amt;
    Qty                         _lastsixmonthqty;
    Amount                      _lastsixmonthamount;
    Qty                         _OverStock6MthMore_Qty;
    Amount                      _OverStock6MthMore_Amt;
    EcoResStorageDimensionGroupName          _inventModelGroupId;


    Query                      query;
    QueryRun                   queryRun;
    QueryBuildRange            dateRange;
    QueryBuildDataSource       qbds;


    InventTable               inventTable;
    InventTrans               inventTrans;
    InventModelGroupItem      modelGroupItem;
    InventTransOrigin         inventTransOrigin;
    InventItemGroupItem       itemGroupItem;
    InventSum                 inventSum;
    EcoResStorageDimensionGroupItem               ecoResStorageDimensionGroupItem;
    EcoResStorageDimensionGroup                   ecoResStorageDimensionGroup;


    contract  = this.parmDataContract() as HCN_StockReportsContract;
    asOfDate    = contract.parmDate();
    sixMthBack = contract.getDateOfPastMonths(asOfDate,6);


     while select inventTable

//#3 End
    {
        _asOfDate = asOfDate;
        //tmp.InventModelGroupId = inventTableLocal.DimGroupId;//inventTableLocal.ModelGroupId;
        _itemGroupId = inventTable.ItemGroupId();
        _itemId = inventTable.ItemId;
        _description  = inventTable.NameAlias;

        select ecoResStorageDimensionGroupItem
        join  ecoResStorageDimensionGroup
        where ecoResStorageDimensionGroupItem.StorageDimensionGroup == EcoResStorageDimensionGroup.RecId
        && ecoResStorageDimensionGroupItem.ItemId == inventTable.ItemId;

        _inventModelGroupId  = ecoResStorageDimensionGroup.Name;

        // get qty and Physical Cost Amount by AsOfDate
        select sum(qty), sum(costAmountPhysical) from inventTrans
            where inventTrans.ItemId == InventTable.ItemId
            &&    inventTrans.DatePhysical <= asOfDate
            &&    inventTrans.DatePhysical != dateNull();

        _actualStock_Qty = abs(decRound(inventTrans.Qty,2));

        if (_actualStock_Qty == 0)
            continue;

        select firstonly inventSum
            where inventSum.ItemId == inventTable.ItemId &&
            inventSum.PhysicalInvent > 0 ;

        _avgPrice = decRound(inventSum.averageCostPrice(),4);

        _actualStock_Amt = abs(decRound(_actualStock_Qty * _avgPrice,2));


        inventTrans.clear();


        select sum(qty), sum(costAmountPhysical) from inventTrans
        join inventTransOrigin
            where inventTrans.ItemId == InventTable.ItemId && inventTransOrigin.RecId == inventTrans.InventTransOrigin
            &&    inventTransOrigin.ReferenceCategory == InventTransType::Sales
            &&    inventTrans.DatePhysical >= sixMthBack
            &&    inventTrans.DatePhysical <= asOfDate
            &&    (inventTrans.StatusIssue == StatusIssue::Deducted ||
                   inventTrans.StatusIssue == StatusIssue::Sold);


        _salesqty = inventTrans.Qty;
        _salesamount = inventTrans.CostAmountPhysical;

        select sum(qty), sum(costAmountPhysical) from inventTrans
            join inventTransOrigin
            where inventTrans.ItemId == InventTable.ItemId && inventTransOrigin.RecId == inventTrans.InventTransOrigin
            && inventTransOrigin.ReferenceCategory == InventTransType::ProdLine
            &&    inventTrans.DatePhysical >= sixMthBack
            &&    inventTrans.DatePhysical <= asOfDate
            &&    (inventTrans.StatusIssue == StatusIssue::Deducted ||
                   inventTrans.StatusIssue == StatusIssue::Sold);


        _productionqty = inventTrans.Qty;
        _productionamount = inventTrans.CostAmountPhysical;

        select sum(qty), sum(costAmountPhysical) from inventTrans
            join inventTransOrigin
            where inventTrans.ItemId == InventTable.ItemId && inventTransOrigin.RecId == inventTrans.InventTransOrigin
            &&    inventTransOrigin.ReferenceCategory == InventTransType::InventTransaction
            &&    inventTrans.DatePhysical >= sixMthBack
            &&    inventTrans.DatePhysical <= asOfDate
            &&    (inventTrans.StatusIssue == StatusIssue::Deducted ||
                   inventTrans.StatusIssue == StatusIssue::Sold);

            _othersqty          = inventTrans.Qty;
            _othersamount       = inventTrans.CostAmountPhysical;
            _totalqty           = _salesqty + _productionqty + _othersqty;
            _totalcostamount    = _salesamount + _productionamount + _othersamount;


        _lastsixmonthqty = abs(decRound(_totalqty,2));
        _lastsixmonthamount = abs(decRound(_lastsixmonthqty * _avgPrice,2));
        _OverStock6MthMore_Qty = _actualStock_Qty - _lastsixmonthqty;
        _OverStock6MthMore_Amt = _actualStock_Amt - _lastsixmonthamount;
        if (_OverStock6MthMore_Qty <= 0
        || _totalqty == 0)
            continue;

        _con = HCN_ZM_SearchTopItem::search3topitems(inventTable.ItemId);
        [_aireraftOne,_aireraftTwo,_aireraftTree] = conPeek(_con,1);

        this.insertTempTable(_aireraftOne,_aireraftTwo,_aireraftTree, _asOfDate,_itemGroupId,
                             _itemId,_description,_avgPrice, _actualStock_Qty,_actualStock_Amt,_lastsixmonthqty,
                             _lastsixmonthamount,_OverStock6MthMore_Qty,_OverStock6MthMore_Amt,_inventModelGroupId);

    }

   }

（3）插入临时表

private void insertTempTable(ItemId                         _aireraftOne,
    ItemId                         _aireraftTwo,
    ItemId                         _aireraftTree,
    TransDate                   _asOfDate,
    ItemGroupId                 _itemGroupId,
    ItemId                      _itemId,
    Description                 _description,
    Price                       _avgPrice,
    Qty                         _actualStock_Qty,
    Amount                      _actualStock_Amt,
    Qty                         _lastsixmonthqty,
    Amount                      _lastsixmonthamount,
    Qty                         _OverStock6MthMore_Qty,
    Amount                      _OverStock6MthMore_Amt,
    EcoResStorageDimensionGroupName          _inventModelGroupId
                             )
{

    stockReportsTmp.clear();
    stockReportsTmp.ItemId          = _itemId;
    stockReportsTmp.AvgPrice   = _avgPrice;
    stockReportsTmp.Qty             = _actualStock_Qty;
    stockReportsTmp.Amount       =  stockReportsTmp.AvgPrice * stockReportsTmp.Qty;
    stockReportsTmp.Description = _description;
    stockReportsTmp.ItemGroupId = _itemGroupId;
    stockReportsTmp.InventModelGroupId = _inventModelGroupId;
    stockReportsTmp.ActualStock_Qty = _actualStock_Qty;
    stockReportsTmp.ActualStock_Amt = _actualStock_Amt;
    stockReportsTmp.HCN_lastsixmonthqty = _lastsixmonthqty;
    stockReportsTmp.Last6MonthAvg_Qty = _lastsixmonthqty/6;
    stockReportsTmp.HCN_lastsixmonthamount = _lastsixmonthamount;
    stockReportsTmp.OverStock6MthMore_Qty = _OverStock6MthMore_Qty;
    stockReportsTmp.OverStock6MthMore_Amt = _OverStock6MthMore_Amt;
    stockReportsTmp.HCN_topitemid  = _aireraftOne;
    stockReportsTmp.HCN_topitemid1 = _aireraftTwo;
    stockReportsTmp.HCN_topitemid2 = _aireraftTree;
    stockReportsTmp.insert();
}

（4）返回临时表

4.创建Job测试

（略）

5.在VS中创建项目，数据源选择RDP，用Precision Design (精确设计)。