Macro and SQL

If you've developed anything in the supply chain area, you've most probably come across Inventdimjoin. Invendimjoin is a macro and it's mostly used to narrow the search results for inventory transactions (InventTrans) or INV Entory postings (inventtranspost), but can is used on any table has a inventdimid field.

The macro accepts four to five parameters

· InventDimId

· InventDim

· Inventdimcriteria

· Inventdimparm

· Index Hint (optional)

You must use it as a join with a select-statement (hence the name) and it returns no contents from InventDim. It is a exist join, but doesn ' t return to any useful fields, so it probably should was an exist-join. Anyway, let's take the parameters one by one:

InventDimId

The unique key to the inventory dimension table. This is where you supply the InventDimId field from the table and is joining.

InventDim

Any InventDim table buffer. This is the table buffer used in the joined select. The contents of the buffer has no influence on the select result set and resulting records would only have the TableId Fiel D filled (but that's a constant anyway and already filled by just defining the buffer, so really you get nothing).

Inventdimcriteria

This is also a InventDim buffer and the contents of this one matters. Using this buffer, you define which dimensions is looking for exactly (warehouse ' Main ', location ' IN-1 ', batch ' 2411 "etc".

Inventdimparm

Inventdimparm is a temporary table. A record basically consists of nothing but a number of the flags to indicate which inventory dimensions is important and whic H ones can be disregarded. There is a *flag field for every inventory dimension field. Inventdimparm have a variety of methods to initialize these flags. An example would is initphysicalinvent (). It clears all flags (=NO) and sets only those the flags to yes whose corresponding inventory dimension are a physical dimension (For the dimension group ID you pass along as a parameter).

Consequently provide Inventdimjoin with a inventdimparm buffer. For all flags with value ' No ' the contents of the corresponding Inventdimparm field does not matter.

Index hint

This is a optional parameter to help you optimize performance.

Here's an example:

static void Inventdimjointest (Args _args)

{

SalesLine SalesLine;

InventDim InventDim;

InventDim Inventdimcriteria;

Inventdimparm Inventdimparm;

;

Inventdimcriteria.inventlocationid = ' Main ';

Inventdimcriteria.wmslocationid = ' in-1′;

Inventdimcriteria.configid = ' Red ';

Inventdimparm.clear ();

Inventdimparm.inventlocationidflag = Noyes::yes;

Inventdimparm.wmslocationidflag = Noyes::no;

Inventdimparm.configidflag = Noyes::yes;

While select SalesLine

#InventDimJoin (Salesline.inventdimid, InventDim, Inventdimcriteria, Inventdimparm, Dimidx)

{

Print strfmt ("%1%2%3%4%5″,salesline.inventdimid, Salesline.salesid, Salesline.itemid,

Inventdim.inventlocationid, Salesline.inventdim (). Inventlocationid);

}

Pause

}

The job finds all saleslines for the ' Main ' warehouse and Configuration ' Red '. The location doesn ' t matter, since it's inventdimparm flag is turned off (additional dimensions like batch, serial Etc.wou LDN ' t a difference either, clear () takes care of that).

Note that%4 prints the Inventdim.inventlocationid. I just put, there to make the point, that it'll always be blank.

DIMIDX is the optional parameter. It ' s an index defined on the InventDim table. You'll notice in the output of the result is sorted by InventDimId.

Macro and SQL