Inventory Journal Import

I wrote a simple class to handle csv imports into Movement Journals.

1. Create a Movement Journal and go into the Lines screen.
2. Select Functions, Import Lines

3. Select a csv file with the columns: ItemId, Warehouse, Location, Batch, Qty, Cost.  (you can mod the code to expand this)

4. The lines are added to the journal.

You can download the xpo below, and here are some of the key bits of code.

Create a class (mine is called inventJournalImport) extending runbase.  Create a menu item (action) for the class and drop it on the InventJournalMovement form.

The main method of the class is as follows:

client static void main(Args args)
{
    inventJournalImport             inventJournalImport;
    Object                          formRunObject;
    JournalForm                     journalForm;
    InventJournalId                 inventJournalId;
    FormDataSource                  journalTrans_ds;
    ;

    inventJournalImport = new inventJournalImport();
    inventJournalImport.getLast();

    if (!args || !args.caller() || args.caller().name() != formStr(InventJournalMovement))
    {
        throw error(strfmt("This function must be called from the %1 form.", formStr(InventJournalMovement)));
    }

    if (formHasMethod(args.caller(), identifierstr(journalForm)))
    {
        formRunObject = args.caller();

        journalForm = formRunObject.journalForm();
        inventJournalImport.parmJournalForm(journalForm);  //needed to index numOfLines on inventJournalTable

        inventJournalId = journalForm.journalTableData().journalTable().JournalId;
        inventJournalImport.parmInventJournalId(inventJournalId);  //for defaulting on the imported lines

        journalTrans_ds = journalForm.journalTransData().journalTrans().dataSource();  //for .executeQuery, below
    }

    if (inventJournalImport.prompt())
    {
        inventJournalImport.ImportRecords();

        //refresh the grid
        journalTrans_ds.executeQuery();
    }
}

The ImportRecords method is below.  There is a dialog in the class that fills filename and transactionDate.

void ImportRecords()
{
    AsciiIo             asciiIo;
    container           con;
    FileIoPermission    fioPermission;

    InventJournalTrans  inventJournalTrans;
    InventDim           inventDim;

    wmsLocationId       wmsLocationId;
    inventLocationId    inventLocationId;
    inventBatchId       inventBatchId;

    boolean             useDefaultInventDim, useDefaultCost;
    int                 imported;
    ;

    if (WINAPI::fileExists(fileName))
    {
        //show wait cursor
        startLengthyOperation();

        // The AsciiIO.new method runs under code access permission.
        fioPermission = new FileIoPermission(fileName,"R");

        if (fioPermission == null)
        {
            info(strfmt("Not able to read file: %1",fileName));
            return;
        }

        // Code access permission scope starts here.
        fioPermission.assert();

        ttsbegin;

        asciiIo = new AsciiIo(fileName,"R");
        asciiIo.inFieldDelimiter(",");
        if (asciiIo != null)
        {
            con = asciiIo.read();

            while (asciiIo.status() == IO_Status::Ok)
            {
                try
                {
                    //1-itemId, 2-warehouse, 3-location, 4-batch, 5-qty, 6-cost
                    inventJournalTrans.clear();
                    inventJournalTrans.TransDate = transactionDate;
                    inventJournalTrans.JournalId = journalId;
                    inventJournalTrans.initFromInventJournalTable(inventJournalTrans.inventJournalTable());

                    inventJournalTrans.ItemId = strLTrim(strRTrim(conpeek(con,1)));

                    inventLocationId = strLTrim(strRTrim(conpeek(con,2)));
                    wmsLocationId = strLTrim(strRTrim(conpeek(con,3)));
                    inventBatchId = strLTrim(strRTrim(conpeek(con,4)));
                    if (inventLocationId || wmsLocationId || inventBatchId)
                    {
                        useDefaultInventDim = false;

                        inventDim.clear();
                        inventDim.InventLocationId = inventLocationId;
                        inventDim.wMSLocationId = wmsLocationId;
                        inventDim.inventBatchId = inventBatchId;
                        inventDim = InventDim::findOrCreate(inventDim);

                        inventJournalTrans.InventDimId = inventDim.inventDimId;
                    }
                    else
                    {
                        useDefaultInventDim = true;
                    }

                    inventJournalTrans.Qty = str2num(strLTrim(strRTrim(conpeek(con,5))));
                    inventJournalTrans.Qty= decround(inventJournalTrans.Qty,InventTable::inventDecimals(inventJournalTrans.ItemId));

                    if (inventJournalTrans.Qty > 0)
                    {
                        //import the cost if this is an addition to inventory
                        useDefaultCost = false;
                        inventJournalTrans.CostPrice          = str2num(strLTrim(strRTrim(conpeek(con,6))));
                        inventJournalTrans.PriceUnit          = 1;
                        inventJournalTrans.CostMarkup         = 0;
                        inventJournalTrans.CostAmount         = inventJournalTrans.calcCostAmount();
                    }
                    else
                    {
                        useDefaultCost = true;
                    }

                    inventJournalTrans.initFromInventTable(inventJournalTrans.inventTable(),false, useDefaultInventDim, useDefaultCost);

                    inventJournalTrans.insertFromCode();
                    imported++;

                    //updates the number of lines count on the journal table
                    journalForm.journalTableData().addTotal(inventJournalTrans);
                }
                catch (Exception::Error)
                {
                    exceptionTextFallThrough();
                }

                con = asciiIo.read();
            }
        }

        // revertAssert is not really necessary here because the method is ending.
        CodeAccessPermission::revertAssert();

        ttscommit;
        info (strfmt("%1 records imported", imported));

        //remove wait cursor
        endLengthyOperation();
    }
    else
    {
        error(strfmt("Import File not found: %1",fileName));
    }
}

That's the bulk of the code.  You can download the xpo below to see everything.

SharedProject_InventoryJournalImport.xpo (google docs - shortened via goo.gl - 12kb)

Inventory Turns Report

AX doesn't really have a good inventory turns report.  Here's a simple job that calculates inventory turns by quantity and by value.  You can adapt the code to run in a report.

static void InventoryTurns(Args _args)
{
    itemId          itemId;
    inventTrans     inventTrans;
    inventSum       inventSum;
    qty             thisQty, qtyOnHand, totalDailyQtyOnHand, avgDailyQtyOnHand, usageQty;
    Amount          thisValue, valueOnHand, totalDailyValueOnHand, avgDailyValueOnHand, usageValue;
    Real            turnsByQty, turnsByValue;
    date            lastDate, fromDate, toDate;
    ;

    //set the itemID and date range
    itemId = '00001';
    fromDate = mkDate(1,1,2010);
    toDate = mkDate(31,12,2010);

    //****average daily on-hand
    lastDate = systemDateGet(); //initialize with today.

    //inventSum contains today's values for qty and value
    while select inventSum where inventSum.ItemId == itemId && inventSum.Closed == NoYes::No
    {
        qtyOnHand += inventSum.PhysicalInvent;
        valueOnHand += (inventSum.PhysicalValue + inventSum.PostedValue);
    }

    //starting with today, we work backward to the from date and manually calculate what the on-hand qty/value was each day
    while (lastDate >= fromDate)
    {
        if (lastDate <= toDate) //if we're within the date range, then sum the on-hand qty/value for determination of the average
        {
            //add one days worth of the current OnHand figures
            totalDailyQtyOnHand += qtyOnHand;
            totalDailyValueOnHand += valueOnHand;

            //update average calc
            avgDailyQtyOnHand = totalDailyQtyOnHand/((toDate + 1) - lastDate);
            avgDailyValueOnHand = totalDailyValueOnHand/((toDate + 1) - lastDate);
        }

        lastDate --;

        thisQty = 0;
        thisValue = 0;
        while select Qty, CostAmountPosted, CostAmountPhysical from inventTrans
            where inventTrans.ItemId == itemId
            && inventTrans.DatePhysical == lastDate
        {
            thisQty += inventTrans.Qty;
            //use the posted (financial) value if available
            if (inventTrans.CostAmountPosted != 0)
            {
                thisValue += inventTrans.CostAmountPosted;
            }
            else
            {
                thisValue += inventTrans.CostAmountPhysical;
            }
        }
        //calc the new onHand values
        qtyOnHand += -1 * thisQty;
        valueOnHand += -1 * thisValue;
    }

    //****annual usage
    while select inventTrans
        order by DatePhysical desc
        where inventTrans.ItemId == itemId
        && inventTrans.datePhysical >= fromDate  //this should work in place of limits on statusIssue < 3 and statusReceipt < 3
        && inventTrans.datePhysical <= toDate
        && (inventTrans.TransType == InventTransType::Sales
            || inventTrans.TransType == InventTransType::ProdLine
            || inventTrans.TransType == InventTransType::Project
            || inventTrans.TransType == InventTransType::InventTransaction)

    {
        usageQty += -1*inventTrans.Qty;
        usageValue += -1*inventTrans.CostAmountPhysical;
    }

    //****calc turns = usageQty divided by average on-hand qty
    if (avgDailyQtyOnHand == 0)  //divide by zero protection
    {
        turnsByQty = 0;
    }
    else
    {
        turnsByQty = usageQty / avgDailyQtyOnHand;
    }
    if (avgDailyValueOnHand == 0)
    {
        turnsByValue = 0;
    }
    else
    {
        turnsByValue = usageValue / avgDailyValueOnHand;
    }
    
    info(strfmt("Item Id: %1, Turns by Qty: %2, Turns by Value: %3",itemId,turnsByQty,turnsByValue));
}

Large Inventory Close Workaround

The Situation:
We needed to inventory-close 18 months worth of transactions.  This happened because a legacy system was the system of record for this period of time and no one was worrying about details like inventory close.  When AX became the system of record, focus turned to reconciling accounts and the inventory close.

We chose not to:
We chose not to do the inventory close one month at a time because that would require opening old accounting periods.

Problem:
When we did the inventory close for 18 months (moderate number of transactions), the process would run for 24 hours or so and then we would get an "Out of memory" error.

Workaround Attempts:

• The machines were reasonably powerful...but, just in case, we restarted the AOS and client to make sure that they were in the best condition possible.  No luck.
• We used "Calculation Help" to try to spread out the load of the closing process among several work stations.  This shortened the 24 hours, but still ended in "Out of memory".
• We set the Maximum Throughputs and Minimum... parameters to favor speed over accuracy.  For us, that was an option because this wasn't the system of record - we just need to get to a clean starting point.

More Info:

• When the inventory close failed, the status of the Invent Closing record was "Ledger Posting".
• All of the inventory settlement records had been created (and didn't roll back) when the inventory close failed.
• In looking at the code, it became clear the the "Calculation Help" only applied to creating/processing the inventory settlement records.  When it got to posting to the ledger, the original client was the only one doing the work.
• In looking at the code, it seemed that Posting with a specification of Total, Item Group, or Item didn't change the transaction size.  We had a specification of 'Total'.  (that's the only one we tried)

Our Plan:

1. Create InventSettlement records.  We used the records created by one of our failed attempts, but you could also comment out the call to this.updateLedgerPosting(); towards the bottom of classes\InventCostHelp.run().
2. Create several InventClosing records with status of "Ledger Posting".  We created 3 total.  You can make these by commenting out some code in classes\InventCostHelp.run() after the inventClosing record is inserted but before any work is done.
3. Update InventSettlement.Voucher (in SQL Mgmt Studio) to match InventClosing.Voucher so that the InventSettlement records are assigned to the InventClosing records that you created in manageable chunks.  Since we were posting with a specification of 'Total' we just split the records roughly in thirds.  If you're posting by Item Group, you might want to take care to put all of the same item group in the same voucher.
4. Post each InventClosing record.  Normally, when an inventory close record has failed, all you can do is cancel it.  We temporarily commented code so that when we clicked Close on a failed record, it would 'pick up where it left off' and run updateLedgerPosting().  I've included some snippets below on this.

Result:
Success!

Code related to step #4:

classes\InventCostClosing

static void main(Args args)
{
    InventClosing       inventClosing;
    FormDataSource      fd;

    InventCostClosing closing = InventCostClosing::newParameters();

    //NDP 9/24/10 - BEGIN temp change
    if (args && args.caller() && args.dataset() == tablenum(InventClosing))
    {
        inventClosing = args.record();
    }
    InventCostHelp::newInventClosing(inventClosing,true).run();
    /*
    if (! closing.prompt())
        return;

    try
    {
        closing.run();
    }
    catch
    {
        exceptionTextFallThrough();
    }
    */
    //NDP 9/24/10 - END

    if (args && args.caller() && args.dataset() == tablenum(InventClosing))
    {
...

classes\InventCostHelp

public void run()
{
...

    setprefix(InventCostClosing::prefixText(inventClosing.AdjustmentType));

    try
    {
        this.progressInit(InventCostClosing::prefixText(inventClosing.AdjustmentType),inventClosing.CostNumOf,#AviUpdate);
//NDP 9/24/10 - comment out starting here
/*
        if (! this.validate())
            throw error("@SYS18447");

...


        if (doMasterClient && !trueClient)
            masterClient = true;
*/
//NDP 9/24/10 - end comment out here
        if (! doStop && masterClient)
        {
            this.updateLedgerPosting();
            if (inventClosing.RunRecalculation && systemdateget() > inventClosing.TransDate)
            {
                this.runRecalculation();
            }
        }

Hope that helps,
Nate