Friday, September 4, 2015

Digging up the Dirt. Inside the InventTrans (Part 2).

In part 1 of this series on the InventTrans, I explained how a purchase order line mutates in the InventTrans table during the various stages of its life.

We are going to stick with the purchase order line for a while longer. All inventory transactions type are the same in general, so anything that applies to the PurchaseLine can be applied to the SalesLine (for example) with some minor changes and common sense.

Now first, let’s take a look at what ties the InventTrans and the PurchLine together. The answer is simple…nothing.

inventtrans

With the introduction of AX6 (2012) and the mysterious data model that came with it, the PurchLine still has a field named InventTransId, but the InventTrans table no longer has a InventTransId. Makes perfect sense, doesn’t it?

There are two tables that bind the transaction table to the order table. These are the InventTransOrigin and the InventTransOriginPurchLine. Both provide a cross-reference between the InventTransId (sometimes referred to as LOT-ID) from the PurchLine, and the InventTransOrigin (which is actually the RecId of the InventTransOrigin table) from the InventTrans.

Are you still with me?

PurchLine is governed by the InventTransId, while InventTrans is governed by the InventTransOrigin. InventTransOrigin and InventTransOriginPurchLine live in both realms and provide the link between the other two tables. Both InventTransOrigin and InventTransOriginPurchLine are required for successful processing of the various steps in the purchase order line life.

Why a cross-reference table?

Well children, a long long time ago, when computer disks were expensive and measured by the megabyte, it was believed that data in a database should be normalized (only stored in one place) to save as much precious disk space as possible. Apparently MS brought out the dinosaurs when it was time to assign the engineering teams for AX6. From what I understand many of these engineering choices have been reversed in R3 after MS killed the dinosaurs (Boom, boom, shakalaka-laka-boom).

Why two cross-reference tables?

Apparently the dinosaurs in Redmond also had some special brothers and sisters who were not only allowed to color the Microsoft logo, but also had a vote in the design process. There are others who say that it was done for performance reasons, but they obviously never worked with AX6.

Splitting it up.

What happens when you receive a partial order line?

Simple, AX splits up your InventTrans record in the part that you register and the part that remains to be received.

So now we have two records in the inventtrans, both with the same InventTransOriginId, one with PurchStatus ‘registered’ and one with PurchStatus ‘Ordered’. In the other three tables from the picture above we still only have one record (each).

This splitting of the InventTrans record can happen as many times as is required. The sum of all the quantities in the inventTrans with the same InventTransOriginId should always be equal to the total number of transactions on the purchase order line.

When the quantity on the purchase order line is changed, the quantity on the ‘Ordered’ line in InventTrans changes too. When the quantity of an inventTrans record reaches zero (cancel deliver remainder on the PurchLine), then the record is deleted along with its kin in the the InventTransOrigin and the InventTransOriginPurchLine.

Therefore not every PurchLine record has a corresponding record in the InventTrans. Come to think of it, you will (obviously) not find any inventory transactions for purchase lines belonging to an order of type journal either. ‘t is good to know.

Tuesday, August 11, 2015

Digging up the Dirt. Inside the InventTrans (Part 1).

I thought about naming this post ‘InventTrans for Dummies’, but we have to consider the source. We all know that the beating heart of the AX in- and outbound process is the inventtrans table. In essence this table is a ledger of any and all inventory movements and as such it is the ultimate source for answers to inevitable questions about changes to inventory cost, inventory value and inventory levels.

The table itself is no great mystery, but considering how crucial it is for the working of your organization and AX in general, it is a shame that there is no decent documentation for it to be found.

The InventTrans has undergone some significant changes from AX 2012 up. I won’t go into that, but be aware that this does not apply to older versions of AX.

The InventTrans is an inventory ledger, so incoming lines (e.g. purchase order lines) have a positive quantity, and outgoing lines (e.g. sales order lines) have a negative quantity.

Incoming lines have a receipt status (field StatusReceipt), while outgoing lines have an issue status (Field StatusIssue).

Receipt statuses:

  • Purchased (posted financially, i.e. ‘bought and paid for’)
  • Received (posted physically, i.e. ‘we got the goods’)
  • Registered (registered receipt, i.e. ‘signed for the parcel’)
  • Arrived
  • Ordered (purchase order line created)

Issue statuses:

  • Sold (posted invoice)
  • Deducted (posted packing slip, i.e. ‘signed, sealed, delivered’)
  • Picked (registered pick list, i.e. ‘ready to ship’)
  • ReservPhysical (earmarked items in warehouse)
  • ReservOrdered (marked items expected to arrive in the foreseeable future)
  • OnOrder (sales order line created)

Some basic rules for messing with data in the InventTrans:

  1. Don’t!
  2. Never-ever-ever-ever-EVER delete a line in InventTrans (unless you are absolutely sure)
  3. Always recalculate InventSum after you make changes to inventTrans
  4. Try to recreate the data from normal AX functionality. For example, cancel the deliver remainder on a sales order, then change it back to the original value. This will ‘reset’ your inventtrans for that line.

Purchase order

When we confirm a purchase order, we create a new record in InventTrans. Initially this record contains:

  • Date expected (expected date of inventory change for planning purposes)
  • Date status (not what you, or rather I, would think)
  • InventDimId (Order receipt location)
  • InventTransOrigin (RecId from table InventTransOrigin)
  • ItemId
  • Qty
  • StatusReceipt-Ordered (PO = receipt in inventory, current status is ordered)

This all makes perfect sense, doesn’t it? It is the short version of our purchase order line. From it we can see that on date x, we will receive quantity y of item z on location …what comes after z?

This record will accompany your purchase order line for the rest of its natural life and beyond. Yes, there is a one on one relationship…unless:

  1. You cancel the purchase order line.
    Note that there is no receipt status ‘canceled’ or anything similar. That’s because inventory doesn’t care why or how. If the goods don’t show, we don’t want to know. Canceling the (qty on the) purchase order line) will delete the inventtrans record.
  2. You split the purchase order line.
    ”But, but, I didn’t split the line. Why would I split the line?”
    You did when your supplier decided to send you only half the quantity you ordered. You did when you decided to work with an arrival schedule. You did when you rejected part of your delivery. Whenever the quantity of your order line is split two ways, there is an additional record created in InventTrans.

The InventTransOrigin refers (through a cross-reference on the InventTransOrigin table) to the purchase line InventTransId. Therefore all inventTrans records with the same InventTransOrigin tell the whole story of (for example) a purchase order line. The sum of the quantities for these records should match the quantity on the purchase order line.

When we (using WMS) post the arrival journal of this purchase order line, the following fields are added to the record:

  • Date inventory (receipt date)
  • MarkingRefInventTransOrigin (what other inventTrans record put dibs on this line?)

Further more the date status changes and the receipt status is bumped up to ‘registered’.

Did we skip the ‘Arrived’ stage? Yes we did. Unless you are running a major WMS controlled logistics operation, you probably won’t bother with this. If you are, you might not want to bother with AX (although I have heard good things about R3 on this matter).

When we post the purchase order receipt, the following fields are added to the inventtrans record:

  • CostAmountPhysical
  • DatePhysical
  • PackingSlipId
  • VoucherPhysical

It is clear that this is the posting of the physical receipt. In addition the date status field changes and the receipt status kicks it up a nudge to ‘received’.

Finally, we can post the invoice for the purchase order line. This adds the final fields to the inventtrans record:

  • CostAmountPost
  • CostAmountStd
  • CurrencyCode (Am I the only one who finds it odd that costs require a currency, but value does not?)
  • DateFinancial
  • InvoiceId
  • Voucher

The status issue goes to ‘Purchased’ and yes, the DateStatus is set to the same value as DateFinancial.

So en résumé:

Field

Create orderline

Post confirmation

Post receipts list

Post arrival journal

Post product receipt

Post invoice

StatusReceipt Ordered Ordered Ordered Registered Received Purchased
ItemId ZF123 ZF123 ZF123 ZF123 ZF123 ZF123
Qty 3 3 3 3 3 3
InventDimId

DIM00000001

DIM00000001

DIM00000001

DIM00000001

DIM00000001

DIM00000001

InventTransOrigin 16984987792 16984987792 16984987792 16984987792 16984987792 16984987792
Date expected 1-4-2015 1-4-2015 1-4-2015 1-4-2015 1-4-2015 1-4-2015
Date status 1-4-2015 1-4-2015 1-4-2015 7-4-2015 7-4-2015 7-4-2015
Date inventory 8-4-2015 8-4-2015
Date Physical 8-4-2015 8-4-2015
Date Financial 10-4-2015
CostAmountPhysical 274,62 274,62
CostAmountPost 205,96
CostAmountStd 205,96
PackingSlipId PS001 PS001
InvoiceId PI001
Voucher

GRB-15595

VoucherPhysical

ENG-0018171

ENG-0018171

CurrencyCode EUR

(to be continued)

Thursday, March 19, 2015

Reading URL in x++ code through a proxy

The case was simple enough: For all items, attach any and all drawings that are available in the online repository to the item's attached documents.

I could google an example of using the .NET webrequest to retrieve the content (and validity) of a URL. All went well and smooth until I ran the code from a different PC, that was connected to the web through a proxy.

Setting the proxy on the webrequest turned out to be a bit of undocumented trial and error. All I could find on the net were C# and VB examples that didn't seem to be close tot the correct calls in x++.
So without further ado, here is some basic code to test the validity of a URL and more importantly... set your proxy in AX.
static void TestURL(Args _args)
{    // REF https://msdn.microsoft.com/en-us/library/456dfw4f(v=vs.110).aspx

    System.Net.WebRequest      webReq;
    System.Net.WebResponse     webRes;

    ProxyIPAddress             address = "10.170.16.160";
    ProxyPortNum               port = 8080;
    System.Net.WebProxy        proxy = new System.Net.WebProxy(address, port);



    URL                        url = "https://msdn.microsoft.com";
    //URL url = "https://msdn.microsoft.baloney";

    url = System.Web.HttpUtility::UrlPathEncode(url);
    webReq = System.Net.WebRequest::Create(url);
    webReq.set_Proxy(proxy);

    try
    {
        webRes = webReq.GetResponse();
        info("URL exists");
        webRes.Close();
    }
    catch
    {
        info("URL does not exist.");
    }
}



And that's how you set the proxy from x++.
Consider that you might need to set the proxy regardless of your Windows/browser settings. That's just the way things are.

So if you ever need to set your webrequest, then this is a proxy mately how to do it.











Thursday, March 5, 2015

Excel column name from number

In the run-up to a comprehensive post about using the Excel COM object in X++ (because there's just no substitute for the real deal), here's a little problem I ran into along the way: Getting the Excel column name right.

There are a lot of algorithms out there, but I like none of them. This one is OLD SCHOOL!!

str getColStr(int _num)
{   int b = 26;
    int d = (a div b) div b;
    int f = _num mod b;
    int g = (a div b) mod b;
    str res;

    if (_num > 0 && _num < 16385)
    {
        res = strReplace(strFmt("%1%2%3", num2char(d+64), num2char(g+64), num2char(f+64)), "@", "")
        return res;
    }
}

In short: because of the 16385 column limitation of Excel, there is really no need to make it more complicated than it is. Three letters is the max, or column 'XFD' if you must know.
The first (rightmost) column letter is always the modulo of the number and 26 (letters in alphabet, so that one is easy.
The second (if any) column letter is the modulo of the remainder and 26.
The third column finally is the number of times the remainder can be divided by 26.

Example: what is column 1189 in Excel?

first column letter: 1189 MOD 26 = 19 > "S"
Remainder (how many times does 26 go into 1189 or 1189 DIV 26) : 45
Second column letter: remainder MOD 26 = 19 > "S"
Remainder (how many times does 26 go into 45 or 45 DIV 26) : 1 > "A"
Third column letter is the same as the remainder because we know there cannot be a greater power on this earth or the next. Besides, if you try to create a spreadsheet with over 16,000 columns you really should seek professional help in a different area of expertise.
So 1189 returns "ASS" and lo and behold...that is correct.
Why so serious, batman?

Speaking of which, why is it that all the examples I came across on the various blogs are so much more complex? It took me more time to google bad examples than to write this little ditty.
Is it just because only dinosaurs like myself remember a time before object orientation?

Column me old fashioned, but I like my code compressed.

Tuesday, February 17, 2015

Datasource link types.


LinkType

It seems easy enough, but when it comes to link types in AX, the noise is overwhelming and makes it hard to understand the simple facts. This may be due in part to the lets say unorthodox naming and gaming of this datasource property.
So let me try to clear things up once and for all.
Link types in AX can be divided in two groups. Active, Delayed and Passive are one to many relations. Records in the child data source are retrieved only for the current (selected) record in the parent data source. This makes these link types unsuitable for use in grids, but only for use in header/line type forms such as Sales orders.

Active

The child data source is updated immediately when a new record in the parent source is selected. Not suitable for high-scroll-level forms, such as anything with a grid.

Delayed

There is a literal delay in synchronizing the child data source with the parents. This allows for fast scrolling through the parent data source and ony retrieve child data for the relevant record.

Passive

Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.
For less application specific joins of datasources, there are four more flavors. Innerjoin,, OuterJoin, ExistJoin and NotExistJoin

InnerJoin

Classic inner join of two collections (parent and child data source).

INNER JOIN
This one is so trivial that even the makers of AX couldn't get it wrong. This is the first and last join name that we can agree on.


 

OuterJoin

All records from the parent data source that do NOT have a corresponding record in the child data source.

LEFT JOIN
In the more civilized regions of the SQL sphere these are called left joins, but that was of no concern to the boys at Damgaard. Microsoft has not bothered to change it so far or just never noticed it hard enough.
 


ExistJoin

Selects ony the records from the parent data source that have a reference to a child.

LEFT INNER JOIN ?
I’m not sure if such a thing as a left inner join exists or should exist. Maybe that’s how the DAX developers came up with the name for this conjunction.
The main benefit is speed. Pure and simple beautiful speed. So if you require a subset of your table and you have another table that you can use as a cake knife, then this is the way to go.

NotExistJoin

Selects only the records from the parent data source that do not have a reference to a child. This one was a mystery to me, until I bothered to build the example below. I found that every single description I ever googled about this link type was wrong. This is simply a…..

LEFT OUTER JOIN
..would be the proper name for this link type in my humble opinion.
Wouldn't it be nice if we could all just get along?




 

Don’t believe me?
Take a look at the following example.

TABLE A
TABLE B
CustomerID
Name
10
Red
20
Blue
30
Green
40
Pink
50
Black

OrderID
CustomerID
Item
101
10
Pepsi
102
10
Coke
103
10
Sprite
104
20
Dr. Pepper
105
30
7-Up
106
40
Mountain D
107
40
Fanta
108
60
Jolt
109
70
Fresca


Two simple tables that I will use as the datasources for an equally simple form using all five fields from the two tables. Linked on customer ID:


INNER JOIN
No surprises here. Only the records that exist in both tables are shown. Customer ID 50, 60 and 70 are left out.



OUTER JOIN
The same result as the inner join, plus Customer ID 50 who has never ordered pop in his life. This is by all means and conventions a left join, Microsoft.



EXIST JOIN
Here we see the customers from the inner join, but none of the precious sodas. It's an inner join light.



NOT EXIST JOIN
And finally, there is our lost customer. The one that got away. The one with its own secret soda fountain. Ever felt the need to track those customers who just fill up your CustTable without ever placing an order? AX makes it easy for you. I call this a left outer join and I dare anyone in Redmond to prove me wrong.




And there you have it. These are the flavors AX has to offer you. If you need more, then you are doing it wrong.

Enjoin!