Monday, January 16, 2017

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

No comments:

Post a Comment