Inventory Posting Profile Upload in ax 2012

//Inventory Posting Profile ax 2012

1) Inventory Sales Posting Profile data
//Format 1.ItemgroupId,2.SOIssue,3.SOConsumption,4.SORevenue
static void ImportSalesPosting(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int                     row = 2;
    InventPosting           inventPosting;
    ItemGroupId             itemGroupId;
    MainAccountNum          salesOrderIssue, salesOrderConsumption, salesOrderRevenue;
    ;
    application =   SysExcelApplication::construct();
    workbooks   =   application.workbooks();
    try
    {
        workbooks.open(@"filename");
    }
    catch(Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    try
    {
        ttsBegin;
        do
        {
            itemGroupId = cells.item(row, 1).value().bStr();
            salesOrderIssue = cells.item(row, 3).value().bStr();
            salesOrderConsumption = cells.item(row, 4).value().bStr();
            salesOrderRevenue = cells.item(row, 5).value().bStr();

            if (salesOrderIssue)
            {
                try
                {
                    // sales issue
                    inventPosting.InventAccountType = InventAccountType::SalesIssue;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(salesOrderIssue).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, salesOrderIssue));
                }
            }
            if (salesOrderConsumption)
            {
                try
                {
                    // sales order consumption
                    inventPosting.InventAccountType = InventAccountType::SalesConsump;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(salesOrderConsumption).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, salesOrderConsumption));
                }
            }
            if (salesOrderRevenue)
            {
                try
                {
                    // sales order revenue
                    inventPosting.InventAccountType = InventAccountType::SalesRevenue;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(salesOrderRevenue).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, salesOrderRevenue));
                }
            }
            row ++;
            type = cells.item(row, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
        ttsCommit;
    }
    catch
    {
        application.quit();
        throw error(strFmt("%1 Line Error", row));
    }
    application.quit();
    info("Import completed");
}
2) Inventory Purchase Posting  Profile Data
//Format 1.ItemgroupId,2.prodReciptAcc,3.PurchExpUnInvoicedAcc,4.PurchInvReceiptAcc,5.PurchExpProdAcc,6.purchAccrualAcc
static void ImportPurchasePosting(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int                     row = 2;
    InventPosting           inventPosting;
    ItemGroupId             itemGroupId;
    MainAccountNum          productReceiptAccount, purchaseExpenditureUnInvoicedAccount, purchaseInventoryReceiptAccount,    purchaseExpenditureProductAccount,    purchaseAccrualAccount;
    ;
    application =   SysExcelApplication::construct();
    workbooks   =   application.workbooks();
    try
    {
        workbooks.open(@"filename");
    }
    catch(Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    try
    {
        ttsBegin;
        do
        {
            itemGroupId = cells.item(row, 1).value().bStr();
            productReceiptAccount = cells.item(row, 3).value().bStr();
            purchaseExpenditureUnInvoicedAccount = cells.item(row, 4).value().bStr();
            purchaseInventoryReceiptAccount = cells.item(row, 5).value().bStr();
            purchaseExpenditureProductAccount = cells.item(row, 6).value().bStr();
            purchaseAccrualAccount = cells.item(row, 7).value().bStr();
            if (productReceiptAccount)
            {
                try
                {
                    // product receipt
                    inventPosting.InventAccountType = InventAccountType::PurchPackingSlip;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(productReceiptAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, productReceiptAccount));
                }
            }
            if (purchaseExpenditureUnInvoicedAccount)
            {
                try
                {
                    // purchase expenditure unInvoiced
                    inventPosting.InventAccountType = InventAccountType::PurchPackingSlipOffsetAccount;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(purchaseExpenditureUnInvoicedAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, purchaseExpenditureUnInvoicedAccount));
                }
            }
            if (purchaseInventoryReceiptAccount)
            {
                try
                {
                    // purchase inventory receipt
                    inventPosting.InventAccountType = InventAccountType::PurchReceipt;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(purchaseInventoryReceiptAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, purchaseInventoryReceiptAccount));
                }
            }
            if (purchaseExpenditureProductAccount)
            {
                try
                {
                    // purchase expenditure product
                    inventPosting.InventAccountType = InventAccountType::PurchConsump;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(purchaseExpenditureProductAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, purchaseExpenditureProductAccount));
                }
            }
            if (purchaseExpenditureProductAccount)
            {
                try
                {
                    // purchase accrual
                    inventPosting.InventAccountType = InventAccountType::PurchPackingSlipPurchaseOffsetAccount;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(purchaseExpenditureProductAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, purchaseExpenditureProductAccount));
                }
            }
            row ++;
            type = cells.item(row, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
        ttsCommit;
    }
    catch
    {
        application.quit();
        throw error(strFmt("%1 Line Error", row));
    }
    application.quit();
    info("Import completed");
}
3) //Inventory Invent posting ProfileData
static void ImportInventoryPosting(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int                     row = 2;
    InventPosting           inventPosting;
    ItemGroupId             itemGroupId;
    MainAccountNum          inventoryIssueAccount, inventoryLossAccount, inventoryReceiptAccount, inventoryProfitAccount;
    ;
    application =   SysExcelApplication::construct();
    workbooks   =   application.workbooks();
    try
    {
        workbooks.open(@"filename");
    }
    catch(Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    try
    {
        ttsBegin;
        do
        {
            itemGroupId = cells.item(row, 1).value().bStr();
            inventoryIssueAccount = cells.item(row, 3).value().bStr();
            inventoryIssueAccount = strReplace(inventoryIssueAccount, "~", "");
            inventoryLossAccount = cells.item(row, 4).value().bStr();
            inventoryLossAccount = strReplace(inventoryLossAccount, "~", "");
            inventoryReceiptAccount = cells.item(row, 5).value().bStr();
            inventoryReceiptAccount = strReplace(inventoryReceiptAccount, "~", "");
            inventoryProfitAccount = cells.item(row, 6).value().bStr();
            inventoryProfitAccount = strReplace(inventoryProfitAccount, "~", "");
            if (inventoryIssueAccount)
            {
                try
                {
                    // inventory issue
                    inventPosting.InventAccountType = InventAccountType::InventIssue;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(inventoryIssueAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, inventoryIssueAccount));
                }
            }
            if (inventoryLossAccount)
            {
                try
                {
                    // inventory loss
                    inventPosting.InventAccountType = InventAccountType::InventLoss;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(inventoryLossAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, inventoryLossAccount));
                }
            }
            if (inventoryReceiptAccount)
            {
                try
                {
                    // inventory receipt
                    inventPosting.InventAccountType = InventAccountType::InventReceipt;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(inventoryReceiptAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, inventoryReceiptAccount));
                }
            }
            if (inventoryProfitAccount)
            {
                try
                {
                    // inventory profit
                    inventPosting.InventAccountType = InventAccountType::InventProfit;
                    inventPosting.ItemCode = InventPostingItemCode::GroupId;
                    inventPosting.ItemRelation = itemGroupId;
                    inventPosting.CostCode = TableGroupAll::All;
                    inventPosting.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(inventoryProfitAccount).RecId);
                    inventPosting.insert();
                }
                catch
                {
                    info(strFmt("Error: Item group: %1, Account: %2", itemGroupId, inventoryProfitAccount));
                }
            }
            row ++;
            type = cells.item(row, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
        ttsCommit;
    }
    catch
    {
        application.quit();
        throw error(strFmt("%1 Line Error", row));
    }
    application.quit();
    info("Import completed");
}

Vendor/Customer-Opening,Closing,Transaction AmountCur,AmountMST in Summary Balance

//VendorCustomer-Opening,Closing,AmountCur,AmountMST in Summary Balance

 vendTable.openBalanceCur(fromDate,toDate,assessmentDate,_CurrencyCode); //for Acc cur Mst
 CustTable.openBalanceCur(fromDate,toDate,assessmentDate,_CurrencyCode);
static void VendCustBalanceCheck(Args _args)
{
    VendTable     vendTableSummary; 
    TmpCustVendTrans    tmpCustVendTrans;
    ;
   while select vendTableSummary
    {
        tmpCustVendTrans.setTmpData(TmpCustVendTrans::vendTransBalanceCurrency(vendTableSummary.AccountNum,True));
           
        while select tmpCustVendTrans
        {
            info(strFmt("%1,%2,%3,%4,%5",vendTableSummary.AccountNum,tmpCustVendTrans.CurrencyCode,
                                         vendTableSummary.openBalanceCur(dateNull(),today()-1,today(),tmpCustVendTrans.CurrencyCode),
                                         tmpCustVendTrans.AmountCur,
                                         (vendTableSummary.openBalanceCur(dateNull(),today()-1,today(),tmpCustVendTrans.CurrencyCode)) -
                                                tmpCustVendTrans.AmountCur));
        }
    }
}

Clear marking in ax 2012 Invent Trans

//Clear marking in ax 2012 Invent Trans

public void clearMarking()
{
//this == inventTrans
    this.MarkingRefInventTransOrigin    = 0;
}

Set Default value in ax Contract class

//Set Default value in ax Contract class
ContractClass implements SysOperationInitializable
Method : initialize

AX Security Development Tool Reference

http://kaya-consulting.com/tips-on-ax-2012-security-development-tool-part-1/
http://kaya-consulting.com/tips-on-ax-2012-security-development-tool-part-2/
http://kaya-consulting.com/tips-on-ax-2012-security-development-tool-part-3/
http://kaya-consulting.com/tips-on-ax-2012-security-development-tool-part-4/
http://kaya-consulting.com/tips-on-ax-2012-security-development-tool-part-5/
http://kaya-consulting.com/tips-on-ax-2012-security-development-tool-part-6/
http://kaya-consulting.com/security-development-tool-part-7/

Get Aging bucket in ax 2012

//Get Aging bucket in ax 2012

static void Fcc_CheckBucketSplitUp(Args _args)
{
    CustVendReportName       _agingPeriodDefinition = "3";
    TransDate                _zeroDate = today();
    QueryRun                    queryRun;
    QueryBuildDataSource        qbds;
    TmpStatPer                  tmpStat;
    TransDate                   datePointer;
    StatRepIntervalLine         statRepIntervalLine;
    StatRepInterval             statRepInterval;
    RecordInsertList            recordInsertList;
    int                         bucket;
    ForwardBackwardPrinting     direction;
    Fcc_inventoryAgingPeriodScaleTmp    agingPeriodTmp;
    int i;
    Query                       query = new Query();
    TmpStatPer                  tmpStatPer = TmpStatPer::createDefName(_agingPeriodDefinition,_zeroDate); // Need to modify if aging bucket table is customize means..Table TmpStatPer Method CreateDefName for Newly created table

    void addLine(TransDate _transDate, str _description,  ItemAgingIndicator _indicator, int _bucketNumber)
    {
        agingPeriodTmp.TransDate            = _transDate;
        agingPeriodTmp.BucketDescription    = _description;
        agingPeriodTmp.AgingIndicator       = _indicator;
        agingPeriodTmp.BucketNumber         = _bucketNumber;
        recordInsertList.add(agingPeriodTmp);
    }
    delete_from agingPeriodTmp;
    direction = (select PrintDirection from statRepInterval where statRepInterval.Name == _agingPeriodDefinition).PrintDirection;
    qbds = query.addDataSource(tableNum(TmpStatPer));
    recordInsertList = new RecordInsertList(tableNum(Fcc_InventoryAgingPeriodScaleTmp), true, true, true, false, true, agingPeriodTmp);
    switch (direction)
    {
        case ForwardBackwardPrinting::Forward:
            qbds.addSortField(fieldNum(TmpStatPer, StartDate), SortOrder::Ascending);
            bucket = 1;
            break;
        case ForwardBackwardPrinting::Backward:
            qbds.addSortField(fieldNum(TmpStatPer, StartDate), SortOrder::Descending);
            bucket = int642int((select count(RecId) from statRepIntervalLine where statRepIntervalLine.Name == _agingPeriodDefinition).RecId);
            break;
    }
    queryRun = new QueryRun(query);
    queryRun.setCursor(tmpStatPer);
    while (queryRun.next())
    {
        tmpStat = queryRun.getNo(1) as TmpStatPer;
        if (tmpStat.StartDate == dateNull())
        {
            addLine(tmpStat.StartDate, tmpStat.Description, tmpStat.InvoiceQty, bucket);
            addLine(tmpStat.EndDate, tmpStat.Description, tmpStat.InvoiceQty, bucket);
        }
        if (tmpStat.StartDate != dateNull() && tmpStat.EndDate != dateMax())
        {
            for (datePointer = tmpStat.StartDate; datePointer <= tmpStat.EndDate; datePointer++)
            {
                // means that if we order buckets in aging period definition ASC from the past to future - each bucket will have a corresponding int, to be used afterwards as bucket number.
                addLine(datePointer, tmpStat.Description, tmpStat.InvoiceQty, bucket);
            }
        }
        if (tmpStat.EndDate == dateMax())
        {
            addLine(tmpStat.StartDate, tmpStat.Description, tmpStat.InvoiceQty, bucket);
            addLine(tmpStat.EndDate, tmpStat.Description, tmpStat.InvoiceQty, bucket);
        }
        if (direction == ForwardBackwardPrinting::Forward)
        {
            bucket++;
        }
        else
        {
            bucket--;
        }
    }
    recordInsertList.insertDatabase();
    //Deletion and updation of aging bucket
    ttsBegin;
    for(i=1;i<=6;i++)
    {
        agingPeriodTmp.clear();
        select forUpdate agingPeriodTmp order by RecId Asc
            where agingPeriodTmp.BucketNumber == i;
        if(agingPeriodTmp)
        {
            agingPeriodTmp.Updated = NoYes::Yes;
            agingPeriodTmp.Update();
        }
        agingPeriodTmp.clear();
        select forUpdate agingPeriodTmp order by RecId Desc
            where agingPeriodTmp.BucketNumber == i;
        if(agingPeriodTmp)
        {
            agingPeriodTmp.Updated = NoYes::Yes;
            agingPeriodTmp.Update();
        }
    }
    delete_from agingPeriodTmp where agingPeriodTmp.Updated == NoYes::No;
    ttsCommit;
}

Getting Aging date by days,Month,year,Qr in ax 2012

//Getting Aging date by days,Month,year,Qr in ax 2012

TransDate startDate(TransDate initDate = systemDateGet())
{
    Futures                 futures;
    recId                   calendarRecId = CompanyInfo::fiscalCalendarRecId();
    ;
    switch(this.IntervalType)
    {
        case PerDayWeekMthQtYr::Period:
            return FiscalCalendars::findPeriodStartDateByDateWithIndex(calendarRecId,initDate,this.Qty+1);
        case PerDayWeekMthQtYr::Day :
            futures = new Futures(initDate,
                                 (this.Qty+1),
                                 PeriodUnit::Day);
            return futures.next();
        case PerDayWeekMthQtYr::Week:
            futures = new Futures(dateStartWk(initDate),
                                 (this.Qty   * 7 +7),
                                 PeriodUnit::Day);
            return futures.next();
        case PerDayWeekMthQtYr::Month:
            futures = new Futures(dateStartMth(initDate),
                                 (this.Qty+1),
                                 PeriodUnit::Month);
            return futures.next();
        case PerDayWeekMthQtYr::Quarter:
            futures = new Futures(dateStartQtr(initDate),
                                 (this.Qty * 3 +3),
                                 PeriodUnit::Month);
            return futures.next();
        case PerDayWeekMthQtYr::Year:
            futures = new Futures(dateStartYr(initDate),
                                 (this.Qty+1),
                                 PeriodUnit::Year);
            return futures.next();
        case PerDayWeekMthQtYr::Unlimited:
            return dateNull();
    }
    return dateNull();
}

Hiding Parameter in UI builder ax 2012 from Controller class caller

//Hiding Parameter in UI builder ax 2012 based on Controller caller class


if (this.controller().parmArgs().menuitemName() == #yourMenuItemName)
     {
           grp.visible(false);
     }

Get Table Properties in ax 2012

//Get Table Properties in ax 2012

static void TableProperties(Args _args)
{
    DictTable       dictTable;
    DictField       dictField;
    int             i, cnt;
 
    dictTable = new DictTable(tableNum(CustTrans));
    cnt = dictTable.fieldCnt();
    for (i= 1; i<=cnt;i++)
    {
        dictField = new DictField(tableNum(CustTrans),dictTable.fieldCnt2Id(i));
        if (dictField.mandatory())
        {
            info (strFmt("Field %1 is mandatory.",dictField.label()));
        }
    }
   
}

Record Lock in SQL

//Record Lock in SQL
select cmd,* from sys.sysprocesses
where blocked > 0

exec sp_lock
exec sp_who
exec sp_who2

select
  object_name(resource_associated_entity_id) as 'TableName' ,*
from
  sys.dm_tran_locks
where resource_type = 'OBJECT'
  and resource_database_id = DB_ID()
SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,
    
     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,
    
     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

http://shahnawazdba06.blogspot.com/2016/05/find-table-which-has-row-lock.html