Create new entity error - The natural key for the table CustPackingSlipTrans was not found

When create new entity for D365, the wizard will prompt developer to select the table name, the rest is just the matter of selecting fields and finish off the wizard. But sometimes we'll get error for table without a Replacement key.

Eg. CustPackingSlipTrans.

Unfortunately you can't change the "Replacement key" for standard table unless you "Customize" it.
Table extension doesn't allow the change of this property either.

If you use the wizard to create entity for CustPackingSlipTrans, you'll get the "The natural key for the table CustPackingSlipTrans was not found" error.

To create the entity for this table, there're two choices:

1. Manually create them (including the child datasources)
2. Temporarily "Customize" it, then remove the "Customize" element after the entity is created

Below are the steps for Option 2 (less effort and quicker than Option 1).

1. Create a temporary index at CustPackingSlipTrans

Allow Duplicates = No
Alternate Key = Yes

*NOTE: Do not include RecId in the temp index

2. Set the temporary index as Replacement key



3. Create the new entity (Right click on the project > Add > New item > Data Model > Data entity)

4. At the wizard, select "CustPackingSlipTrans" as Primary datasource

5. Click "Next", select the required fields and finish off the wizard

6. After the new entity is created, delete the "Customized" CustPackingSlipTrans

7. Update the EntityKey with other key field that's appropriate
(If Data Management property has been set to Yes, then update the staging table as well)

Data Management Entity export for all companies

In D365O, by default if you create an entity for a company specific table, the "Primary Company Context" property is set to DataAreaId. This affect what data is exported when the Entity is used for exporting data.

Eg.
All rows from table AAA_TestTable.

AAA_TestTable entity where the "Primary Company Context" property is set to "DataAreaId".
NOTE: The "CompanyId" field is mapped to "DataAreaId" of the source table.

When an Export job is setup using this Entity, it will export the rows base on the company context.

Eg.
Current company = USMF, then only records from USMF company is exported.

If the "Primary Company Context" is cleared out (empty), it will export all records regardless of the company.

Eg.
It exported all records regardless of the company context.

If the Entity is used through OData service call, then the above changes is not required to make the data export cross-company. It just need to add the "cross-company=true" parameter to the service URL. Eg. https://demoaos.cloudax.dynamics.com/data/AAA_TestTable?cross-company=true.

For more details, refers to https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/cross-company-behavior

DIXF - System.Security.Cryptography.CryptographicException: Key not valid for use in specified state

Had this error each time we replaced the business database in our Test environment.

It occur at the Data Import Export Framework > Setup > Source data format > Validate (button).

Took a quick look at the code which causes this error, it came from \Data Dictionary\Tables\DMFDataSourceProperties\Methods\validateConnectionString - Ln 49, on the statement "cryptoblob2str(WinAPIServer::cryptUnProtectData(_dmfDataSourceProperties.Password))".

It is trying to decrypt data that's encrypted from another environment, which will cause error in this case. The "Password" field in the table is storing the encrypted data. Our setup is to use "System DSN " from the "AOS", so there're two way for us to fix it:
1. Clear the "Password" field from back-end (Eg. DMFDataSourceProperties.Password = conNull(); DMFDataSourceProperties.update();)
2. Delete the existing record and create a new one with the exact same setup

It works in our case due to we just need this field to be empty as it won't be used.


Detail error message

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Security.Cryptography.CryptographicException: Key not valid for use in specified state.

   at System.Security.Cryptography.ProtectedData.Unprotect(Byte[] encryptedData, Byte[] optionalEntropy, DataProtectionScope scope)

   --- End of inner exception stack trace ---

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

   at Microsoft.Dynamics.AX.ManagedInterop.ClrBridgeCore.InvokeClrStaticMethod(Char* pszClassName, Char* pszMethodName, Char* assemblyName, Int32 argsLength, ObjectWrapper** arguments, Boolean* argsAreByRef, Boolean* isException)

Error - The field 'FieldName' on the extension 'ExtensionName.Extension' already exists. This extension cannot be applied to the base element.

In a recent D365O development, I came across this error when moving fields from a model to another model.

The field 'FieldName' on the extension 'ExtensionName.Extension' already exists. This extension cannot be applied to the base element.

The scenario is that while doing testing on development, some fields have been moved to the standard element (overlayered it by "Customize") [Eg. This is in ModelA]. When the test is done, these field are then moved back to the extension [Eg. This is in ModelB]. Unfortunately after moved it back to the extension, this error occur during the build of the solution.

This is what has been derived:

• When the fields are moved to the base element, a build is done and the assembly is updated
• After the testing is done, the change on the base element is deleted (deleting the whole overlayered element)
• Then used the "Get latest" to restore the extension (it is where the fields are originally created)
• Then build solution again and error occur
• It is suspected that after the change in base element is deleted, the reference to the assembly still there. Hence, when building the solution of the extension, it found that the same fields already exist in the base element.

Solution

1. In Visual Studio, go to Dynamics 365 > Build models
2. Select only the model where the base element sit (Eg. Application suite)
3. Build it
4. Then build the solution again

D365 - You are not authorized to login with your current credentials

If you're restoring Dynamics 365 for Operation database with a backup from another environment, one of the error you might get is "You are not authorized to login with your current credentials. You will be redirected to the login page in a few seconds.".

To fix that, you just need to rerun the "AdminUserProvisioning" tool again.
If you're still getting the error after running the provisioning tool, check the event log.

One of the possible cause would be this database from another environment hasn't been sync with your environment yet, hence, giving some error of this or that field is not found, etc.
Try synchronize the DB, then do an iisreset.

Ineffective execution plan due to outdated statistics

Recently encounter a performance issue where the query plan generated is ineffective due to outdated statistics.

1. There'a batch job periodically updating a table (recurrence job) based on the daily activities on another table
2. External call from web - calling a stored proc and executed a query (generated the execution plan and cached it)
3. One of the occurrence has made large number of record update but not large enough to hit the threshold to trigger statistic update
4. External call from web - calling the same stored proc
5. Due to the large update on #3 has cause the data changes, the query plan no longer effective
6. The query in the stored proc is running extremely long

TableA: ~16m records

TableB: ~1m records

TableC: ~100k records

TableD: ~10k records

TableE: ~5 records

The query in the stored proc is joining a few table (Eg. TableA - E), one of the table always has very few records (between 1 to 10), on normal circumstances, the query always generated a query plan which join TableE first to other table so that it reduce the number of records required on the next join to the rest of the table.

But somehow the first run of the stored proc has generated a query plan which use TableC as first join, joining it to TableA, then to TableB, TableD, and TableE. This appear to be correct on that run itself due to the parameter value passed in does run (slightly) better with this plan. But the next time when it run, unfortunately the previously cached plan is bad for that parameter, it has cause the query run much longer.

TableE is specifically designed to reduce the join operation, but unfortunately on the bad query plan, it put it to the last join operation, hence doing a lot of unnecessary join.

The solution we put in is to make the TableE join with TableA as subquery.

All other join has where condition which correlated to TableA, so this will make the subquery being executed first, this then achieve our initial purpose, to ensure the join order always has the TableE first, then the rest of the order does not matter, they can be join as SQL optimizer feel appropriate.

Performance improvement on sp_WHSOnHand / sp_WHSOnHandWithDelta

These two stored procedure are generated by AX during synchronization (there're others as well, but not the focus of this post).

- sp_WHSOnHand

- sp_WHSOnHandWithDelta

In our environment (with certain amount of data), it tooks around 250ms to execute, although this number looks small, but when you have 100 of them being executed, that's 25,000ms (25 seconds), it is a different story then. A wait of 25s isn't something nice for the user.

Analyzing the query plan shows that it starts from InventDim, getting a large number of records and then nested loop to WHSInventReserve and so on. Looking at the thickness of the arrow next to InventDim, that's an indicator that a large number of rows are returned from that operator. That means it is operating that large number of rows against the WHSInventReserve.

This is the major bottleneck of the query.

We've tried update statistics and reindex and clear the cache to make sure it generate a new plan, but it still generate the same execution plan.

Due to this stored procedure has an ItemId provided, it should limit the number of rows from the WHSInventReserve, using this limited number of rows and nested loop against the InventDim would be much quicker, but somehow the optimizer didn't think so.

Bad query plan

To resolve this problem, I'm splitting this query into two parts.

First, filter the WHSInventReserve with the ItemId provided and insert it into a table variable.

Then join this table variable to the InventDim. Although the sum of the estimated subtree cost of the original query and the new 2-parts query is similar, but it runs much quicker than the original version.

Better query plan

With this new change, the stored procedure completed in 7ms instead of 250ms.

Original query join

New query join

The stored procedure should be changed from within AX at the 'WHSOnHandSPHelper' class. When synchronization is run, it regenerate this stored procedure. It also regenerate when stock dimension setup is changed.

Below is a sample of complete stored procedure code generated by the 'WHSOnHandSPHelper' class (Do take note, the selection field changed based on the stock dimension setup).

=================================================

CREATE PROCEDURE [dbo].[sp_WHSOnHand]

    @PARTITION bigint,

    @LEVEL int,

    @DATAAREAID nvarchar (4),

    @ITEMID nvarchar (20),

    @INVENTSITEID nvarchar (10) = NULL,

    @INVENTSITEIDLEVEL int = 1024,

    @INVENTLOCATIONID nvarchar (10) = NULL,

    @INVENTLOCATIONIDLEVEL int = 1024,

    @WMSLOCATIONID nvarchar (10) = NULL,

    @WMSLOCATIONIDLEVEL int = 1024,

    @WMSPALLETID nvarchar (18) = NULL,

    @WMSPALLETIDLEVEL int = 1024,

    @INVENTSTATUSID nvarchar (10) = NULL,

    @INVENTSTATUSIDLEVEL int = 1024,

    @LICENSEPLATEID nvarchar (25) = NULL,

    @LICENSEPLATEIDLEVEL int = 1024,

    @INVENTBATCHID nvarchar (20) = NULL,

    @INVENTBATCHIDLEVEL int = 1024,

    @INVENTSERIALID nvarchar (20) = NULL,

    @INVENTSERIALIDLEVEL int = 1024,

    @INVENTPROFILEID_RU nvarchar (10) = NULL,

    @INVENTPROFILEID_RULEVEL int = 1024,

    @INVENTOWNERID_RU nvarchar (20) = NULL,

    @INVENTOWNERID_RULEVEL int = 1024,

    @INVENTGTDID_RU nvarchar (30) = NULL,

    @INVENTGTDID_RULEVEL int = 1024,

    @CONFIGID nvarchar (10) = NULL,

    @INVENTSIZEID nvarchar (10) = NULL,

    @INVENTCOLORID nvarchar (10) = NULL,

    @INVENTSTYLEID nvarchar (10) = NULL,

    -- Output parameters

    @AVAILPHYSICAL  numeric(32, 16) OUTPUT,

    @AVAILORDERED  numeric(32, 16) OUTPUT

AS

-- Without nocount on the procedure will return number of affected rows in the result set which is

-- harmful to performance and requires us to adjust field retrieval when calling the stored procedure

SET NOCOUNT ON;

-- We need to supress warnings when trying to aggregate NULL columns. This is a valid business scenario

-- and happens e.g. for a non-LP controlled location with batch below item.

SET ANSI_WARNINGS OFF;

-- The @RESULT table variable is used to store intermediate quantities.

DECLARE @RESULT TABLE(

    AVAILPHYSICALRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

    AVAILORDEREDRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

    AVAILPHYSICALFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

    AVAILORDEREDFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

    HIERARCHYLEVEL int NOT NULL,

    CONFIGID nvarchar (10) NULL,

    INVENTSIZEID nvarchar (10) NULL,

    INVENTCOLORID nvarchar (10) NULL,

    INVENTSTYLEID nvarchar (10) NULL,

    INVENTSITEID nvarchar (10) NULL,

    INVENTLOCATIONID nvarchar (10) NULL,

    WMSLOCATIONID nvarchar (10) NULL,

    WMSPALLETID nvarchar (18) NULL,

    INVENTSTATUSID nvarchar (10) NULL,

    LICENSEPLATEID nvarchar (25) NULL,

    INVENTBATCHID nvarchar (20) NULL,

    INVENTSERIALID nvarchar (20) NULL,

    INVENTPROFILEID_RU nvarchar (10) NULL,

    INVENTOWNERID_RU nvarchar (20) NULL,

    INVENTGTDID_RU nvarchar (30) NULL);

DECLARE @DIMLIST TABLE(

    INVENTDIMID nvarchar (20) NULL,

    AVAILPHYSICAL numeric(32, 16) NOT NULL,

    AVAILORDERED numeric(32, 16) NOT NULL);

-- The algorithm of the stored procedure is as follows:

-- 1. Initialize the @RESULT table with aggregated qtys in WHSInventReserve for the level requested by user

-- 2. Traverse the hierarchy up and for each level:

--     1) Aggregate qtys from previous level in @RESULT grouping by dimensions of current hierarchy level

--     2) Aggregate qtys from current level in WHSInventReserve grouping by dimensions of current hierarchy level

--     3) Select the minimum of the above qtys and insert into @RESULT

-- 3. When hierarchy level 0 is reached, return the sum of aggregated qtys in @RESULT

-- In the original query (the one after below query), it is joining InventDim and WHSInventReserve and filter records based on some criteria.

-- But in the execution plan, it doesn't seems to generate a good plan (even after UPDATE STATISTICS). In the original execution plan, it always

-- Get records from InventDim first, which returns lot of rows, then only join it to WHSInventReserve and continue filter from there.

-- If we makes it get records from WHSInventReserve first, filter it, then with lesser rows from here, join it to InventDim, it will be quicker.

-- So the optimization is to filter WHSInventResreve first, then use this result at the original query. In the test, this has reduce the execution

-- time from ~250ms to ~7ms

  INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)

  SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED

    FROM WHSINVENTRESERVE WHSINVENTRESERVE

   WHERE WHSINVENTRESERVE.ITEMID         = @ITEMID

     AND WHSINVENTRESERVE.PARTITION      = @PARTITION

     AND WHSINVENTRESERVE.DATAAREAID     = @DATAAREAID

     AND WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL

-- Initialize the @RESULT variable.

-- Loads aggregated data for the lowest level requested by user.

INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

SELECT SUM(DIMLIST.AVAILPHYSICAL),

    SUM(DIMLIST.AVAILORDERED),

    NULL,

    NULL,

    @LEVEL,

    INVENTDIM.CONFIGID,

    INVENTDIM.INVENTSIZEID,

    INVENTDIM.INVENTCOLORID,

    INVENTDIM.INVENTSTYLEID,

    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

    FROM INVENTDIM INVENTDIM

    JOIN @DIMLIST DIMLIST

      ON INVENTDIM.INVENTDIMID = DIMLIST.INVENTDIMID

    WHERE   INVENTDIM.DATAAREAID = @DATAAREAID

        AND INVENTDIM.PARTITION = @PARTITION

        AND (@CONFIGID IS NULL OR INVENTDIM.CONFIGID = @CONFIGID)

        AND (@INVENTSIZEID IS NULL OR INVENTDIM.INVENTSIZEID = @INVENTSIZEID)

        AND (@INVENTCOLORID IS NULL OR INVENTDIM.INVENTCOLORID = @INVENTCOLORID)

        AND (@INVENTSTYLEID IS NULL OR INVENTDIM.INVENTSTYLEID = @INVENTSTYLEID)

        AND (@LEVEL < @INVENTSITEIDLEVEL OR @INVENTSITEID IS NULL OR @INVENTSITEID = INVENTDIM.INVENTSITEID)

        AND (@LEVEL < @INVENTLOCATIONIDLEVEL OR @INVENTLOCATIONID IS NULL OR @INVENTLOCATIONID = INVENTDIM.INVENTLOCATIONID)

        AND (@LEVEL < @WMSLOCATIONIDLEVEL OR @WMSLOCATIONID IS NULL OR @WMSLOCATIONID = INVENTDIM.WMSLOCATIONID)

        AND (@LEVEL < @WMSPALLETIDLEVEL OR @WMSPALLETID IS NULL OR @WMSPALLETID = INVENTDIM.WMSPALLETID)

        AND (@LEVEL < @INVENTSTATUSIDLEVEL OR @INVENTSTATUSID IS NULL OR @INVENTSTATUSID = INVENTDIM.INVENTSTATUSID)

        AND (@LEVEL < @LICENSEPLATEIDLEVEL OR @LICENSEPLATEID IS NULL OR @LICENSEPLATEID = INVENTDIM.LICENSEPLATEID)

        AND (@LEVEL < @INVENTBATCHIDLEVEL OR @INVENTBATCHID IS NULL OR @INVENTBATCHID = INVENTDIM.INVENTBATCHID)

        AND (@LEVEL < @INVENTSERIALIDLEVEL OR @INVENTSERIALID IS NULL OR @INVENTSERIALID = INVENTDIM.INVENTSERIALID)

        AND (@LEVEL < @INVENTPROFILEID_RULEVEL OR @INVENTPROFILEID_RU IS NULL OR @INVENTPROFILEID_RU = INVENTDIM.INVENTPROFILEID_RU)

        AND (@LEVEL < @INVENTOWNERID_RULEVEL OR @INVENTOWNERID_RU IS NULL OR @INVENTOWNERID_RU = INVENTDIM.INVENTOWNERID_RU)

        AND (@LEVEL < @INVENTGTDID_RULEVEL OR @INVENTGTDID_RU IS NULL OR @INVENTGTDID_RU = INVENTDIM.INVENTGTDID_RU)

GROUP BY

    INVENTDIM.CONFIGID,

    INVENTDIM.INVENTSIZEID,

    INVENTDIM.INVENTCOLORID,

    INVENTDIM.INVENTSTYLEID,

    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END;

WHILE (@LEVEL > 0)

BEGIN

  DELETE FROM @DIMLIST

  INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)

  SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED

    FROM WHSINVENTRESERVE WHSINVENTRESERVE

   WHERE WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL + -1

     AND WHSINVENTRESERVE.ITEMID         = @ITEMID

     AND WHSINVENTRESERVE.PARTITION      = @PARTITION

     AND WHSINVENTRESERVE.DATAAREAID     = @DATAAREAID

    INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

SELECT SUM(CASE WHEN RESULT.AVAILPHYSICALFOUND < RESULT.AVAILPHYSICALRESULT THEN RESULT.AVAILPHYSICALFOUND ELSE RESULT.AVAILPHYSICALRESULT END),

    SUM(CASE WHEN RESULT.AVAILORDEREDFOUND < RESULT.AVAILORDEREDRESULT THEN RESULT.AVAILORDEREDFOUND ELSE RESULT.AVAILORDEREDRESULT END),

    SUM(FOUND.AVAILPHYSICAL),

    SUM(FOUND.AVAILORDERED),

    @LEVEL + -1,

    RESULT.CONFIGID AS CONFIGID,

    RESULT.INVENTSIZEID AS INVENTSIZEID,

    RESULT.INVENTCOLORID AS INVENTCOLORID,

    RESULT.INVENTSTYLEID AS INVENTSTYLEID,

    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN RESULT.INVENTSITEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN RESULT.INVENTLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN RESULT.WMSLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN RESULT.WMSPALLETID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN RESULT.INVENTSTATUSID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN RESULT.LICENSEPLATEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN RESULT.INVENTBATCHID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN RESULT.INVENTSERIALID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN RESULT.INVENTPROFILEID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN RESULT.INVENTOWNERID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN RESULT.INVENTGTDID_RU ELSE NULL END

FROM @RESULT RESULT

LEFT OUTER JOIN (

SELECT SUM(DIMLIST.AVAILPHYSICAL) AS AVAILPHYSICAL,

    SUM(DIMLIST.AVAILORDERED) AS AVAILORDERED,

    INVENTDIM.CONFIGID AS CONFIGID,

    INVENTDIM.INVENTSIZEID AS INVENTSIZEID,

    INVENTDIM.INVENTCOLORID AS INVENTCOLORID,

    INVENTDIM.INVENTSTYLEID AS INVENTSTYLEID,

    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END AS INVENTSITEID,

    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END AS INVENTLOCATIONID,

    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END AS WMSLOCATIONID,

    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END AS WMSPALLETID,

    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END AS INVENTSTATUSID,

    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END AS LICENSEPLATEID,

    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END AS INVENTBATCHID,

    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END AS INVENTSERIALID,

    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END AS INVENTPROFILEID_RU,

    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END AS INVENTOWNERID_RU,

    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END AS INVENTGTDID_RU

FROM INVENTDIM INVENTDIM

JOIN @DIMLIST DIMLIST

     ON INVENTDIM.PARTITION = @PARTITION

    AND INVENTDIM.DATAAREAID  = @DATAAREAID

    AND INVENTDIM.INVENTDIMID = DIMLIST.INVENTDIMID

GROUP BY

    INVENTDIM.CONFIGID,

    INVENTDIM.INVENTSIZEID,

    INVENTDIM.INVENTCOLORID,

    INVENTDIM.INVENTSTYLEID,

    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

    ) AS FOUND ON (RESULT.CONFIGID = FOUND.CONFIGID)

        AND (RESULT.INVENTSIZEID = FOUND.INVENTSIZEID)

        AND (RESULT.INVENTCOLORID = FOUND.INVENTCOLORID)

        AND (RESULT.INVENTSTYLEID = FOUND.INVENTSTYLEID)

        AND (@LEVEL + -1 < @INVENTSITEIDLEVEL OR RESULT.INVENTSITEID = FOUND.INVENTSITEID)

        AND (@LEVEL + -1 < @INVENTLOCATIONIDLEVEL OR RESULT.INVENTLOCATIONID = FOUND.INVENTLOCATIONID)

        AND (@LEVEL + -1 < @WMSLOCATIONIDLEVEL OR RESULT.WMSLOCATIONID = FOUND.WMSLOCATIONID)

        AND (@LEVEL + -1 < @WMSPALLETIDLEVEL OR RESULT.WMSPALLETID = FOUND.WMSPALLETID)

        AND (@LEVEL + -1 < @INVENTSTATUSIDLEVEL OR RESULT.INVENTSTATUSID = FOUND.INVENTSTATUSID)

        AND (@LEVEL + -1 < @LICENSEPLATEIDLEVEL OR RESULT.LICENSEPLATEID = FOUND.LICENSEPLATEID)

        AND (@LEVEL + -1 < @INVENTBATCHIDLEVEL OR RESULT.INVENTBATCHID = FOUND.INVENTBATCHID)

        AND (@LEVEL + -1 < @INVENTSERIALIDLEVEL OR RESULT.INVENTSERIALID = FOUND.INVENTSERIALID)

        AND (@LEVEL + -1 < @INVENTPROFILEID_RULEVEL OR RESULT.INVENTPROFILEID_RU = FOUND.INVENTPROFILEID_RU)

        AND (@LEVEL + -1 < @INVENTOWNERID_RULEVEL OR RESULT.INVENTOWNERID_RU = FOUND.INVENTOWNERID_RU)

        AND (@LEVEL + -1 < @INVENTGTDID_RULEVEL OR RESULT.INVENTGTDID_RU = FOUND.INVENTGTDID_RU)

WHERE RESULT.HIERARCHYLEVEL = @LEVEL

GROUP BY

    RESULT.CONFIGID,

    RESULT.INVENTSIZEID,

    RESULT.INVENTCOLORID,

    RESULT.INVENTSTYLEID,

    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN RESULT.INVENTSITEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN RESULT.INVENTLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN RESULT.WMSLOCATIONID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN RESULT.WMSPALLETID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN RESULT.INVENTSTATUSID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN RESULT.LICENSEPLATEID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN RESULT.INVENTBATCHID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN RESULT.INVENTSERIALID ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN RESULT.INVENTPROFILEID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN RESULT.INVENTOWNERID_RU ELSE NULL END,

    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN RESULT.INVENTGTDID_RU ELSE NULL END;

    SET @LEVEL = @LEVEL - 1

END; -- WHILE

SELECT @AVAILPHYSICAL = SUM(CASE WHEN AVAILPHYSICALFOUND < AVAILPHYSICALRESULT THEN AVAILPHYSICALFOUND ELSE AVAILPHYSICALRESULT END),

    @AVAILORDERED = SUM(CASE WHEN AVAILORDEREDFOUND < AVAILORDEREDRESULT THEN AVAILORDEREDFOUND ELSE AVAILORDEREDRESULT END)

FROM @RESULT

WHERE HIERARCHYLEVEL = 0;

GO

=================================================

Below is the XPO extract of the updated 'WHSOnHandSPHelper' class

=================================================

Exportfile for AOT version 1.0 or later

Formatversion: 1

***Element: CLS

; Microsoft Dynamics AX Class: WHSOnHandSPHelper unloaded

; --------------------------------------------------------------------------------

  CLSVERSION 1

  

  CLASS #WHSOnHandSPHelper

    PROPERTIES

      Name                #WHSOnHandSPHelper

      Origin              #{E0CDDBB5-8AE5-4BEF-9B5B-A41A300F36E5}

    ENDPROPERTIES

    

    METHODS

      SOURCE #buildCreateSPStmt

        #private str buildCreateSPStmt(boolean _includeDelta)

        #{

        #    str             ret = '';

        #    ListEnumerator  enabledDimsEnumerator;

        #    ListEnumerator  productDimensionsEnumerator;

        #    FieldId         dimFieldId;

        #    str             dimFieldName;

        #    boolean         isSerialEnabled;

        #

        #    if(useOptimizedVersion)

        #    {

        #        ret = this.TEC_buildCreateSPStmt(_includeDelta);

        #    }

        #    else

        #    {

        #        InventDimMetaDataCache::clearCache(); //in some cases the cache is not updated before sync making below SP

        #

        #        enabledDimsEnumerator = this.enabledDimensions().getEnumerator();

        #        productDimensionsEnumerator = this.productDimensions().getEnumerator();

        #        isSerialEnabled = this.getEnabledDimsParametersMap().exists(fieldNum(InventDim, InventSerialId));

        #

        #        /*

        #CREATE PROCEDURE [sp_WHSOnHand]

        #    @PARTITION bigint,

        #    @LEVEL int,

        #    @DATAAREAID nvarchar (4),

        #    @ITEMID nvarchar (20),

        #    @INVENTSITEID nvarchar (10) = NULL,

        #    @INVENTSITEIDLEVEL int = 1024,

        #    @INVENTLOCATIONID nvarchar (10) = NULL,

        #    @INVENTLOCATIONIDLEVEL int = 1024,

        #    @WMSLOCATIONID nvarchar (10) = NULL,

        #    @WMSLOCATIONIDLEVEL int = 1024,

        #    @WMSPALLETID nvarchar (18) = NULL,

        #    @WMSPALLETIDLEVEL int = 1024,

        #    @INVENTSTATUSID nvarchar (10) = NULL,

        #    @INVENTSTATUSIDLEVEL int = 1024,

        #    @LICENSEPLATEID nvarchar (25) = NULL,

        #    @LICENSEPLATEIDLEVEL int = 1024,

        #    @INVENTBATCHID nvarchar (20) = NULL,

        #    @INVENTBATCHIDLEVEL int = 1024,

        #    @INVENTSERIALID nvarchar (20) = NULL,

        #    @INVENTSERIALIDLEVEL int = 1024,

        #    @INVENTPROFILEID_RU nvarchar (10) = NULL,

        #    @INVENTPROFILEID_RULEVEL int = 1024,

        #    @INVENTOWNERID_RU nvarchar (20) = NULL,

        #    @INVENTOWNERID_RULEVEL int = 1024,

        #    @INVENTGTDID_RU nvarchar (30) = NULL,

        #    @INVENTGTDID_RULEVEL int = 1024,

        #    @CONFIGID nvarchar (10) = NULL,

        #    @INVENTSIZEID nvarchar (10) = NULL,

        #    @INVENTCOLORID nvarchar (10) = NULL,

        #    @INVENTSTYLEID nvarchar (10) = NULL,

        #    -- Output parameters

        #    @AVAILPHYSICAL  numeric(32, 16) OUTPUT,

        #    @AVAILORDERED  numeric(32, 16) OUTPUT

        #AS

        #

        #-- Without nocount on the procedure will return number of affected rows in the result set which is

        #-- harmful to performance and requires us to adjust field retrieval when calling the stored procedure

        #SET NOCOUNT ON;

        #

        #-- We need to supress warnings when trying to aggregate NULL columns. This is a valid business scenario

        #-- and happens e.g. for a non-LP controlled location with batch below item.

        #SET ANSI_WARNINGS OFF;

        #

        #-- The @RESULT table variable is used to store intermediate quantities.

        #DECLARE @RESULT TABLE(

        #    AVAILPHYSICALRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

        #    AVAILORDEREDRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

        #    AVAILPHYSICALFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

        #    AVAILORDEREDFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

        #    HIERARCHYLEVEL int NOT NULL,

        #    CONFIGID nvarchar (10) NULL,

        #    INVENTSIZEID nvarchar (10) NULL,

        #    INVENTCOLORID nvarchar (10) NULL,

        #    INVENTSTYLEID nvarchar (10) NULL,

        #    INVENTSITEID nvarchar (10) NULL,

        #    INVENTLOCATIONID nvarchar (10) NULL,

        #    WMSLOCATIONID nvarchar (10) NULL,

        #    WMSPALLETID nvarchar (18) NULL,

        #    INVENTSTATUSID nvarchar (10) NULL,

        #    LICENSEPLATEID nvarchar (25) NULL,

        #    INVENTBATCHID nvarchar (20) NULL,

        #    INVENTSERIALID nvarchar (20) NULL,

        #    INVENTPROFILEID_RU nvarchar (10) NULL,

        #    INVENTOWNERID_RU nvarchar (20) NULL,

        #    INVENTGTDID_RU nvarchar (30) NULL);

        #

        #-- The algorithm of the stored procedure is as follows:

        #-- 1. Initialize the @RESULT table with aggregated qtys in WHSInventReserve for the level requested by user

        #-- 2. Traverse the hierarchy up and for each level:

        #--     1) Aggregate qtys from previous level in @RESULT grouping by dimensions of current hierarchy level

        #--     2) Aggregate qtys from current level in WHSInventReserve grouping by dimensions of current hierarchy level

        #--     3) Select the minimum of the above qtys and insert into @RESULT

        #-- 3. When hierarchy level 0 is reached, return the sum of aggregated qtys in @RESULT

        #

        #-- Initialize the @RESULT variable.

        #-- Loads aggregated data for the lowest level requested by user.

        #INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

        #SELECT SUM(WHSINVENTRESERVE.AVAILPHYSICAL),

        #    SUM(WHSINVENTRESERVE.AVAILORDERED),

        #    NULL,

        #    NULL,

        #    @LEVEL,

        #    INVENTDIM.CONFIGID,

        #    INVENTDIM.INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

        #    FROM WHSINVENTRESERVE WHSINVENTRESERVE

        #    JOIN INVENTDIM ON INVENTDIM.INVENTDIMID = WHSINVENTRESERVE.INVENTDIMID AND INVENTDIM.DATAAREAID = WHSINVENTRESERVE.DATAAREAID AND INVENTDIM.PARTITION = WHSINVENTRESERVE.PARTITION

        #    WHERE WHSINVENTRESERVE.ITEMID           = @ITEMID

        #        AND WHSINVENTRESERVE.PARTITION      = @PARTITION

        #        AND WHSINVENTRESERVE.DATAAREAID     = @DATAAREAID

        #        AND WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL

        #        AND (@CONFIGID IS NULL OR INVENTDIM.CONFIGID = @CONFIGID)

        #        AND (@INVENTSIZEID IS NULL OR INVENTDIM.INVENTSIZEID = @INVENTSIZEID)

        #        AND (@INVENTCOLORID IS NULL OR INVENTDIM.INVENTCOLORID = @INVENTCOLORID)

        #        AND (@INVENTSTYLEID IS NULL OR INVENTDIM.INVENTSTYLEID = @INVENTSTYLEID)

        #        AND (@LEVEL < @INVENTSITEIDLEVEL OR @INVENTSITEID IS NULL OR @INVENTSITEID = INVENTDIM.INVENTSITEID)

        #        AND (@LEVEL < @INVENTLOCATIONIDLEVEL OR @INVENTLOCATIONID IS NULL OR @INVENTLOCATIONID = INVENTDIM.INVENTLOCATIONID)

        #        AND (@LEVEL < @WMSLOCATIONIDLEVEL OR @WMSLOCATIONID IS NULL OR @WMSLOCATIONID = INVENTDIM.WMSLOCATIONID)

        #        AND (@LEVEL < @WMSPALLETIDLEVEL OR @WMSPALLETID IS NULL OR @WMSPALLETID = INVENTDIM.WMSPALLETID)

        #        AND (@LEVEL < @INVENTSTATUSIDLEVEL OR @INVENTSTATUSID IS NULL OR @INVENTSTATUSID = INVENTDIM.INVENTSTATUSID)

        #        AND (@LEVEL < @LICENSEPLATEIDLEVEL OR @LICENSEPLATEID IS NULL OR @LICENSEPLATEID = INVENTDIM.LICENSEPLATEID)

        #        AND (@LEVEL < @INVENTBATCHIDLEVEL OR @INVENTBATCHID IS NULL OR @INVENTBATCHID = INVENTDIM.INVENTBATCHID)

        #        AND (@LEVEL < @INVENTSERIALIDLEVEL OR @INVENTSERIALID IS NULL OR @INVENTSERIALID = INVENTDIM.INVENTSERIALID)

        #        AND (@LEVEL < @INVENTPROFILEID_RULEVEL OR @INVENTPROFILEID_RU IS NULL OR @INVENTPROFILEID_RU = INVENTDIM.INVENTPROFILEID_RU)

        #        AND (@LEVEL < @INVENTOWNERID_RULEVEL OR @INVENTOWNERID_RU IS NULL OR @INVENTOWNERID_RU = INVENTDIM.INVENTOWNERID_RU)

        #        AND (@LEVEL < @INVENTGTDID_RULEVEL OR @INVENTGTDID_RU IS NULL OR @INVENTGTDID_RU = INVENTDIM.INVENTGTDID_RU)

        #GROUP BY

        #    INVENTDIM.CONFIGID,

        #    INVENTDIM.INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END;

        #

        #WHILE (@LEVEL > 0)

        #BEGIN

        #    INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

        #SELECT SUM(CASE WHEN RESULT.AVAILPHYSICALFOUND < RESULT.AVAILPHYSICALRESULT THEN RESULT.AVAILPHYSICALFOUND ELSE RESULT.AVAILPHYSICALRESULT END),

        #    SUM(CASE WHEN RESULT.AVAILORDEREDFOUND < RESULT.AVAILORDEREDRESULT THEN RESULT.AVAILORDEREDFOUND ELSE RESULT.AVAILORDEREDRESULT END),

        #    SUM(FOUND.AVAILPHYSICAL),

        #    SUM(FOUND.AVAILORDERED),

        #    @LEVEL + -1,

        #    RESULT.CONFIGID AS CONFIGID,

        #    RESULT.INVENTSIZEID AS INVENTSIZEID,

        #    RESULT.INVENTCOLORID AS INVENTCOLORID,

        #    RESULT.INVENTSTYLEID AS INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN RESULT.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN RESULT.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN RESULT.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN RESULT.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN RESULT.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN RESULT.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN RESULT.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN RESULT.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN RESULT.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN RESULT.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN RESULT.INVENTGTDID_RU ELSE NULL END

        #FROM @RESULT RESULT

        #LEFT OUTER JOIN (

        #SELECT SUM(WHSINVENTRESERVE.AVAILPHYSICAL) AS AVAILPHYSICAL,

        #    SUM(WHSINVENTRESERVE.AVAILORDERED) AS AVAILORDERED,

        #    INVENTDIM.CONFIGID AS CONFIGID,

        #    INVENTDIM.INVENTSIZEID AS INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID AS INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID AS INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END AS INVENTSITEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END AS INVENTLOCATIONID,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END AS WMSLOCATIONID,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END AS WMSPALLETID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END AS INVENTSTATUSID,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END AS LICENSEPLATEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END AS INVENTBATCHID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END AS INVENTSERIALID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END AS INVENTPROFILEID_RU,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END AS INVENTOWNERID_RU,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END AS INVENTGTDID_RU

        #FROM WHSINVENTRESERVE WHSINVENTRESERVE

        #JOIN INVENTDIM ON INVENTDIM.PARTITION = WHSINVENTRESERVE.PARTITION

        #    AND INVENTDIM.DATAAREAID  = WHSINVENTRESERVE.DATAAREAID

        #    AND INVENTDIM.INVENTDIMID = WHSINVENTRESERVE.INVENTDIMID

        #WHERE WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL + -1

        #    AND WHSINVENTRESERVE.ITEMID       = @ITEMID

        #    AND WHSINVENTRESERVE.PARTITION    = @PARTITION

        #    AND WHSINVENTRESERVE.DATAAREAID   = @DATAAREAID

        #GROUP BY

        #    INVENTDIM.CONFIGID,

        #    INVENTDIM.INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

        #    ) AS FOUND ON (RESULT.CONFIGID = FOUND.CONFIGID)

        #        AND (RESULT.INVENTSIZEID = FOUND.INVENTSIZEID)

        #        AND (RESULT.INVENTCOLORID = FOUND.INVENTCOLORID)

        #        AND (RESULT.INVENTSTYLEID = FOUND.INVENTSTYLEID)

        #        AND (@LEVEL + -1 < @INVENTSITEIDLEVEL OR RESULT.INVENTSITEID = FOUND.INVENTSITEID)

        #        AND (@LEVEL + -1 < @INVENTLOCATIONIDLEVEL OR RESULT.INVENTLOCATIONID = FOUND.INVENTLOCATIONID)

        #        AND (@LEVEL + -1 < @WMSLOCATIONIDLEVEL OR RESULT.WMSLOCATIONID = FOUND.WMSLOCATIONID)

        #        AND (@LEVEL + -1 < @WMSPALLETIDLEVEL OR RESULT.WMSPALLETID = FOUND.WMSPALLETID)

        #        AND (@LEVEL + -1 < @INVENTSTATUSIDLEVEL OR RESULT.INVENTSTATUSID = FOUND.INVENTSTATUSID)

        #        AND (@LEVEL + -1 < @LICENSEPLATEIDLEVEL OR RESULT.LICENSEPLATEID = FOUND.LICENSEPLATEID)

        #        AND (@LEVEL + -1 < @INVENTBATCHIDLEVEL OR RESULT.INVENTBATCHID = FOUND.INVENTBATCHID)

        #        AND (@LEVEL + -1 < @INVENTSERIALIDLEVEL OR RESULT.INVENTSERIALID = FOUND.INVENTSERIALID)

        #        AND (@LEVEL + -1 < @INVENTPROFILEID_RULEVEL OR RESULT.INVENTPROFILEID_RU = FOUND.INVENTPROFILEID_RU)

        #        AND (@LEVEL + -1 < @INVENTOWNERID_RULEVEL OR RESULT.INVENTOWNERID_RU = FOUND.INVENTOWNERID_RU)

        #        AND (@LEVEL + -1 < @INVENTGTDID_RULEVEL OR RESULT.INVENTGTDID_RU = FOUND.INVENTGTDID_RU)

        #WHERE RESULT.HIERARCHYLEVEL = @LEVEL

        #GROUP BY

        #    RESULT.CONFIGID,

        #    RESULT.INVENTSIZEID,

        #    RESULT.INVENTCOLORID,

        #    RESULT.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN RESULT.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN RESULT.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN RESULT.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN RESULT.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN RESULT.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN RESULT.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN RESULT.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN RESULT.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN RESULT.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN RESULT.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN RESULT.INVENTGTDID_RU ELSE NULL END;

        #

        #

        #    SET @LEVEL = @LEVEL - 1

        #END; -- WHILE

        #

        #SELECT @AVAILPHYSICAL = SUM(CASE WHEN AVAILPHYSICALFOUND < AVAILPHYSICALRESULT THEN AVAILPHYSICALFOUND ELSE AVAILPHYSICALRESULT END),

        #    @AVAILORDERED = SUM(CASE WHEN AVAILORDEREDFOUND < AVAILORDEREDRESULT THEN AVAILORDEREDFOUND ELSE AVAILORDEREDRESULT END)

        #FROM @RESULT

        #WHERE HIERARCHYLEVEL = 0;

        #        */

        #

        #        ret += strFmt('CREATE PROCEDURE [%1]\n', WhsOnHandSPHelper::storedProcedureName(_includeDelta));

        #        ret +=        '    @PARTITION bigint,\n';

        #        ret += strFmt('    %1 int,\n', this.levelExpression(0));

        #        ret += strFmt('    @DATAAREAID %1,\n', this.sqlTypeNameForField(tableNum(InventTable), fieldNum(InventTable, DataAreaId)));

        #        ret += strFmt('    @ITEMID %1,\n', this.sqlTypeNameForField(tableNum(InventTable), fieldNum(InventTable, ItemId)));

        #        if (_includeDelta)

        #        {

        #            ret += strFmt('    %1 %2,\n', #TTSIdParmName, this.sqlTypeNameForField(tableNum(WHSInventReserveDelta), fieldNum(WHSInventReserveDelta, TTSId)));

        #        }

        #

        #        // Generate parameters for supported dimensions

        #        enabledDimsEnumerator.reset();

        #        while (enabledDimsEnumerator.moveNext())

        #        {

        #            [dimFieldId, dimFieldName] = enabledDimsEnumerator.current();

        #            ret += strFmt('    %1 %2 = NULL,\n', this.dimensionParm(dimFieldName), this.sqlTypeNameForField(tableNum(InventDim), dimFieldId));

        #            ret += strFmt('    %1 int = %2,\n', this.dimensionLevelParm(dimFieldName), #INVALIDHIERARCHYLEVEL);

        #        }

        #

        #        // Generate parameters for product dimensions

        #        productDimensionsEnumerator.reset();

        #        while (productDimensionsEnumerator.moveNext())

        #        {

        #            [dimFieldId, dimFieldName] = productDimensionsEnumerator.current();

        #            ret += strFmt('    %1 %2 = NULL,\n', this.dimensionParm(dimFieldName), this.sqlTypeNameForField(tableNum(InventDim), dimFieldId));

        #        }

        #

        #        ret +=        '    -- Output parameters\n';

        #        ret += strFmt('    %1  numeric(32, 16) OUTPUT,\n', #AvailPhysicalParmName);

        #        ret += strFmt('    %1  numeric(32, 16) OUTPUT\n', #AvailOrderedParmName);

        #        ret +=        'AS\n\n';

        #

        #        ret +=        '-- Without nocount on the procedure will return number of affected rows in the result set which is\n';

        #        ret +=        '-- harmful to performance and requires us to adjust field retrieval when calling the stored procedure\n';

        #        ret +=        'SET NOCOUNT ON;\n\n';

        #

        #        ret +=        '-- We need to supress warnings when trying to aggregate NULL columns. This is a valid business scenario\n';

        #        ret +=        '-- and happens e.g. for a non-LP controlled location with batch below item.\n';

        #        ret +=        'SET ANSI_WARNINGS OFF;\n\n';

        #

        #        ret += this.declareResultTableVariable() + '\n';

        #

        #        ret +=        '-- The algorithm of the stored procedure is as follows:\n';

        #        ret +=        '-- 1. Initialize the @RESULT table with aggregated qtys in WHSInventReserve for the level requested by user\n';

        #        ret +=        '-- 2. Traverse the hierarchy up and for each level:\n';

        #        ret +=        '--     1) Aggregate qtys from previous level in @RESULT grouping by dimensions of current hierarchy level\n';

        #        ret +=        '--     2) Aggregate qtys from current level in WHSInventReserve grouping by dimensions of current hierarchy level\n';

        #        ret +=        '--     3) Select the minimum of the above qtys and insert into @RESULT\n';

        #        ret +=        '-- 3. When hierarchy level 0 is reached, return the sum of aggregated qtys in @RESULT\n\n';

        #

        #        ret +=        '-- Initialize the @RESULT variable.\n';

        #        ret +=        '-- Loads aggregated data for the lowest level requested by user.\n';

        #        ret +=        'INSERT INTO @RESULT (' + this.resultTableVariableFields() + ')\n';

        #

        #        ret += this.selectFromWHSInventReserveStmt(_includeDelta) + '\n';

        #

        #        ret += strFmt('WHILE (%1 > 0)\n', this.levelExpression(0));

        #        ret +=        'BEGIN\n';

        #        ret += strFmt('    INSERT INTO @RESULT (%1)\n', this.resultTableVariableFields());

        #

        #        ret += this.selectFromResultJoinWHSInventReserveStmt(_includeDelta) + '\n\n';

        #

        #        ret += strFmt('    SET %1 = %1 - 1\n', this.levelExpression(0));

        #        ret +=        'END; -- WHILE\n\n';

        #

        #        ret += strFmt('SELECT %1 = SUM(CASE WHEN %2 < %3 THEN %2 ELSE %3 END),\n',

        #                        #AvailPhysicalParmName,

        #                        #AVAILPHYSICALFOUNDFieldName,

        #                        #AVAILPHYSICALRESULTFieldName);

        #        ret += strFmt('    %1 = SUM(CASE WHEN %2 < %3 THEN %2 ELSE %3 END)\n',

        #                        #AvailOrderedParmName,

        #                        #AVAILORDEREDFOUNDFieldName,

        #                        #AVAILORDEREDRESULTFieldName);

        #        ret +=        'FROM @RESULT\n';

        #        ret += strFmt('WHERE %1 = 0;\n', #HIERARCHYLEVELFieldName);

        #    }

        #

        #    return ret;

        #}

      ENDSOURCE

      SOURCE #classDeclaration

        #///

        #///     The WHSOnHandSPHelper class contains helper methods to manipulate the WHS on-hand stored procedure.

        #///

        #public class WhsOnHandSPHelper

        #{

        #    #Define.CacheScope('WHSOnHandSPHelper')

        #    #Define.EnabledDimsParametersCacheKey('EnabledDimensionsCache')

        #    #Define.ProductDimsParametersCacheKey('ProductDimensionsCache')

        #

        #    #Define.AvailPhysicalParmName('@AVAILPHYSICAL')

        #    #Define.AvailPhysicalMinName('@AVAILPHYSICALMIN')

        #    #Define.AvailOrderedParmName('@AVAILORDERED')

        #    #Define.AvailOrderedMinName('@AVAILORDERMIN')

        #    #Define.TTSIdParmName('@TTSId')

        #

        #    #Define.AVAILPHYSICALRESULTFieldName('AVAILPHYSICALRESULT')

        #    #Define.AVAILORDEREDRESULTFieldName('AVAILORDEREDRESULT')

        #    #Define.AVAILPHYSICALFOUNDFieldName('AVAILPHYSICALFOUND')

        #    #Define.AVAILORDEREDFOUNDFieldName('AVAILORDEREDFOUND')

        #    #Define.HIERARCHYLEVELFieldName('HIERARCHYLEVEL')

        #

        #    #WHSOnHandSP

        #    #WHSReservationHierarchy

        #

        #    str resultTableVariableFields; // access using the resultTableVariableFields() method

        #

        #    boolean     useOptimizedVersion;

        #}

      ENDSOURCE

      SOURCE #new

        #protected void new()

        #{

        #    resultTableVariableFields = '';

        #

        #    useOptimizedVersion = true;

        #}

      ENDSOURCE

      SOURCE #selectFromWHSInventReserveStmt

        #private str selectFromWHSInventReserveStmt(boolean _includeDelta)

        #{

        #    str             ret = '';

        #    ListEnumerator  enabledDimsEnumerator = this.enabledDimensions().getEnumerator();

        #    ListEnumerator  productDimsEnumerator = this.productDimensions().getEnumerator();

        #    str             dimFieldName;

        #    str             levelExpr = this.levelExpression(-1);

        #    str             inventReserveAlias = this.whsInventReserveTableAlias(_includeDelta);

        #    boolean         hasDoneFirstIteration;

        #

        #    if(useOptimizedVersion)

        #    {

        #        ret = this.TEC_selectFromWHSInventReserveStmt(_includeDelta);

        #    }

        #    else

        #    {

        #        ret += strFmt('SELECT SUM(%1.AVAILPHYSICAL),\n', inventReserveAlias);

        #        ret += strFmt('    SUM(%1.AVAILORDERED),\n', inventReserveAlias);

        #        ret +=        '    NULL,\n';

        #        ret +=        '    NULL,\n';

        #        ret += strFmt('    %1,\n', this.levelExpression(0));

        #

        #        // Select product dimensions

        #        productDimsEnumerator.reset();

        #        while (productDimsEnumerator.moveNext())

        #        {

        #            dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #            ret += strFmt('    INVENTDIM.%1,\n', dimFieldName);

        #        }

        #

        #        // Select enabled dimensions

        #        enabledDimsEnumerator.reset();

        #        hasDoneFirstIteration = false;

        #        while (enabledDimsEnumerator.moveNext())

        #        {

        #            if (hasDoneFirstIteration)

        #            {

        #                ret += ',\n';

        #            }

        #

        #            dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #            ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END',

        #                            this.levelExpression(-1),

        #                            this.dimensionLevelParm(dimFieldName),

        #                            dimFieldName);

        #

        #            hasDoneFirstIteration = true;

        #        }

        #        ret += '\n';

        #

        #        ret += strFmt('    FROM %1 %2\n', this.whsInventReserveTableName(_includeDelta), inventReserveAlias);

        #        ret += strFmt('    JOIN INVENTDIM ON INVENTDIM.INVENTDIMID = %1.INVENTDIMID AND INVENTDIM.DATAAREAID = %1.DATAAREAID AND INVENTDIM.PARTITION = %1.PARTITION\n', inventReserveAlias);

        #        ret += strFmt('    WHERE %1.ITEMID           = @ITEMID\n', inventReserveAlias);

        #        ret += strFmt('        AND %1.PARTITION      = @PARTITION\n', inventReserveAlias);

        #        ret += strFmt('        AND %1.DATAAREAID     = @DATAAREAID\n', inventReserveAlias);

        #        ret += strFmt('        AND %1.HIERARCHYLEVEL = %2\n', inventReserveAlias, this.levelExpression(0));

        #

        #        // Add product dimensions range

        #        productDimsEnumerator.reset();

        #        while (productDimsEnumerator.moveNext())

        #        {

        #            dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #            ret += strFmt('        AND (%2 IS NULL OR INVENTDIM.%1 = %2)\n',

        #                            dimFieldName,

        #                            this.dimensionParm(dimFieldName));

        #        }

        #

        #        // Add enabled dimensions range

        #        enabledDimsEnumerator.reset();

        #        while (enabledDimsEnumerator.moveNext())

        #        {

        #            dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #            ret += strFmt('        AND (%1 < %2 OR %3 IS NULL OR %3 = INVENTDIM.%4)\n',

        #                            this.levelExpression(0),

        #                            this.dimensionLevelParm(dimFieldName),

        #                            this.dimensionParm(dimFieldName),

        #                            dimFieldName);

        #        }

        #

        #        ret +=        'GROUP BY\n';

        #

        #        // Add grouping by product dimensions

        #        productDimsEnumerator.reset();

        #        while (productDimsEnumerator.moveNext())

        #        {

        #            dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #            ret += strFmt('    INVENTDIM.%1,\n', dimFieldName);

        #        }

        #

        #        // Add grouping by enabled dimensions

        #        enabledDimsEnumerator.reset();

        #        hasDoneFirstIteration = false;

        #        while (enabledDimsEnumerator.moveNext())

        #        {

        #            if (hasDoneFirstIteration)

        #            {

        #                ret += ',\n';

        #            }

        #

        #            dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #            ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END',

        #                            this.levelExpression(-1),

        #                            this.dimensionLevelParm(dimFieldName),

        #                            dimFieldName);

        #

        #            hasDoneFirstIteration = true;

        #        }

        #

        #        ret += ';\n';

        #    }

        #

        #    return ret;

        #}

      ENDSOURCE

      SOURCE #selectInnerFromWHSInventReserveStmt

        #private str selectInnerFromWHSInventReserveStmt(boolean _includeDelta)

        #{

        #    str             ret = '';

        #    ListEnumerator  enabledDimsEnumerator = this.enabledDimensions().getEnumerator();

        #    ListEnumerator  productDimsEnumerator = this.productDimensions().getEnumerator();

        #    str             dimFieldName;

        #    str             levelExpr = this.levelExpression(-1);

        #    str             inventReserveAlias = this.whsInventReserveTableAlias(_includeDelta);

        #    boolean         hasDoneFirstIteration;

        #

        #    if(useOptimizedVersion)

        #    {

        #        ret = this.TEC_selectInnerFromWHSInventReserveStmt(_includeDelta);

        #    }

        #    else

        #    {

        #        ret += strFmt('SELECT SUM(%1.AVAILPHYSICAL) AS AVAILPHYSICAL,\n', inventReserveAlias);

        #        ret += strFmt('    SUM(%1.AVAILORDERED) AS AVAILORDERED,\n', inventReserveAlias);

        #

        #        productDimsEnumerator.reset();

        #        while (productDimsEnumerator.moveNext())

        #        {

        #            dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #            ret += strFmt('    INVENTDIM.%1 AS %1,\n', dimFieldName);

        #        }

        #

        #        enabledDimsEnumerator.reset();

        #        hasDoneFirstIteration = false;

        #        while (enabledDimsEnumerator.moveNext())

        #        {

        #            if (hasDoneFirstIteration)

        #            {

        #                ret += ',\n';

        #            }

        #

        #            dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #            ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END AS %3',

        #                            levelExpr,

        #                            this.dimensionLevelParm(dimFieldName),

        #                            dimFieldName);

        #

        #            hasDoneFirstIteration = true;

        #        }

        #        ret += '\n';

        #

        #        ret += strFmt('FROM %1 %2\n', this.whsInventReserveTableName(_includeDelta), inventReserveAlias);

        #        ret += strFmt('JOIN INVENTDIM ON INVENTDIM.PARTITION = %1.PARTITION\n', inventReserveAlias);

        #        ret += strFmt('    AND INVENTDIM.DATAAREAID  = %1.DATAAREAID\n', inventReserveAlias);

        #        ret += strFmt('    AND INVENTDIM.INVENTDIMID = %1.INVENTDIMID\n', inventReserveAlias);

        #        ret += strFmt('WHERE %1.HIERARCHYLEVEL = %2\n', inventReserveAlias, levelExpr);

        #        ret += strFmt('    AND %1.ITEMID       = @ITEMID\n', inventReserveAlias);

        #        ret += strFmt('    AND %1.PARTITION    = @PARTITION\n', inventReserveAlias);

        #        ret += strFmt('    AND %1.DATAAREAID   = @DATAAREAID\n', inventReserveAlias);

        #        ret +=          'GROUP BY\n';

        #

        #        // Add groupping by product dimensions

        #        productDimsEnumerator.reset();

        #        while (productDimsEnumerator.moveNext())

        #        {

        #            dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #            ret += strFmt('    INVENTDIM.%1,\n', dimFieldName);

        #        }

        #

        #        // Add groupping by enabled dimensions

        #        enabledDimsEnumerator.reset();

        #        hasDoneFirstIteration = false;

        #        while (enabledDimsEnumerator.moveNext())

        #        {

        #            if (hasDoneFirstIteration)

        #            {

        #                ret += ',\n';

        #            }

        #

        #            dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #            ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END',

        #                            levelExpr,

        #                            this.dimensionLevelParm(dimFieldName),

        #                            dimFieldName);

        #

        #            hasDoneFirstIteration = true;

        #        }

        #        ret += '\n';

        #    }

        #

        #    return ret;

        #}

      ENDSOURCE

      SOURCE #TEC_buildCreateSPStmt

        #private str TEC_buildCreateSPStmt(boolean _includeDelta)

        #{

        #    str             ret = '';

        #    ListEnumerator  enabledDimsEnumerator;

        #    ListEnumerator  productDimensionsEnumerator;

        #    FieldId         dimFieldId;

        #    str             dimFieldName;

        #    boolean         isSerialEnabled;

        #    str             inventReserveAlias = this.whsInventReserveTableAlias(_includeDelta);

        #

        #    InventDimMetaDataCache::clearCache(); //in some cases the cache is not updated before sync making below SP

        #

        #    enabledDimsEnumerator = this.enabledDimensions().getEnumerator();

        #    productDimensionsEnumerator = this.productDimensions().getEnumerator();

        #    isSerialEnabled = this.getEnabledDimsParametersMap().exists(fieldNum(InventDim, InventSerialId));

        #

        #    /*

        #CREATE PROCEDURE [dbo].[sp_WHSOnHand]

        #    @PARTITION bigint,

        #    @LEVEL int,

        #    @DATAAREAID nvarchar (4),

        #    @ITEMID nvarchar (20),

        #    @INVENTSITEID nvarchar (10) = NULL,

        #    @INVENTSITEIDLEVEL int = 1024,

        #    @INVENTLOCATIONID nvarchar (10) = NULL,

        #    @INVENTLOCATIONIDLEVEL int = 1024,

        #    @WMSLOCATIONID nvarchar (10) = NULL,

        #    @WMSLOCATIONIDLEVEL int = 1024,

        #    @WMSPALLETID nvarchar (18) = NULL,

        #    @WMSPALLETIDLEVEL int = 1024,

        #    @INVENTSTATUSID nvarchar (10) = NULL,

        #    @INVENTSTATUSIDLEVEL int = 1024,

        #    @LICENSEPLATEID nvarchar (25) = NULL,

        #    @LICENSEPLATEIDLEVEL int = 1024,

        #    @INVENTBATCHID nvarchar (20) = NULL,

        #    @INVENTBATCHIDLEVEL int = 1024,

        #    @INVENTSERIALID nvarchar (20) = NULL,

        #    @INVENTSERIALIDLEVEL int = 1024,

        #    @INVENTPROFILEID_RU nvarchar (10) = NULL,

        #    @INVENTPROFILEID_RULEVEL int = 1024,

        #    @INVENTOWNERID_RU nvarchar (20) = NULL,

        #    @INVENTOWNERID_RULEVEL int = 1024,

        #    @INVENTGTDID_RU nvarchar (30) = NULL,

        #    @INVENTGTDID_RULEVEL int = 1024,

        #    @CONFIGID nvarchar (10) = NULL,

        #    @INVENTSIZEID nvarchar (10) = NULL,

        #    @INVENTCOLORID nvarchar (10) = NULL,

        #    @INVENTSTYLEID nvarchar (10) = NULL,

        #    -- Output parameters

        #    @AVAILPHYSICAL  numeric(32, 16) OUTPUT,

        #    @AVAILORDERED  numeric(32, 16) OUTPUT

        #AS

        #

        #-- Without nocount on the procedure will return number of affected rows in the result set which is

        #-- harmful to performance and requires us to adjust field retrieval when calling the stored procedure

        #SET NOCOUNT ON;

        #

        #-- We need to supress warnings when trying to aggregate NULL columns. This is a valid business scenario

        #-- and happens e.g. for a non-LP controlled location with batch below item.

        #SET ANSI_WARNINGS OFF;

        #

        #-- The @RESULT table variable is used to store intermediate quantities.

        #DECLARE @RESULT TABLE(

        #    AVAILPHYSICALRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

        #    AVAILORDEREDRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

        #    AVAILPHYSICALFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

        #    AVAILORDEREDFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

        #    HIERARCHYLEVEL int NOT NULL,

        #    CONFIGID nvarchar (10) NULL,

        #    INVENTSIZEID nvarchar (10) NULL,

        #    INVENTCOLORID nvarchar (10) NULL,

        #    INVENTSTYLEID nvarchar (10) NULL,

        #    INVENTSITEID nvarchar (10) NULL,

        #    INVENTLOCATIONID nvarchar (10) NULL,

        #    WMSLOCATIONID nvarchar (10) NULL,

        #    WMSPALLETID nvarchar (18) NULL,

        #    INVENTSTATUSID nvarchar (10) NULL,

        #    LICENSEPLATEID nvarchar (25) NULL,

        #    INVENTBATCHID nvarchar (20) NULL,

        #    INVENTSERIALID nvarchar (20) NULL,

        #    INVENTPROFILEID_RU nvarchar (10) NULL,

        #    INVENTOWNERID_RU nvarchar (20) NULL,

        #    INVENTGTDID_RU nvarchar (30) NULL);

        #

        #DECLARE @DIMLIST TABLE(

        #    INVENTDIMID nvarchar (20) NULL,

        #    AVAILPHYSICAL numeric(32, 16) NOT NULL,

        #    AVAILORDERED numeric(32, 16) NOT NULL);

        #

        #-- The algorithm of the stored procedure is as follows:

        #-- 1. Initialize the @RESULT table with aggregated qtys in WHSInventReserve for the level requested by user

        #-- 2. Traverse the hierarchy up and for each level:

        #--     1) Aggregate qtys from previous level in @RESULT grouping by dimensions of current hierarchy level

        #--     2) Aggregate qtys from current level in WHSInventReserve grouping by dimensions of current hierarchy level

        #--     3) Select the minimum of the above qtys and insert into @RESULT

        #-- 3. When hierarchy level 0 is reached, return the sum of aggregated qtys in @RESULT

        #

        #-- In the original query (the one after below query), it is joining InventDim and WHSInventReserve and filter records based on some criteria.

        #-- But in the execution plan, it doesn't seems to generate a good plan (even after UPDATE STATISTICS). In the original execution plan, it always

        #-- Get records from InventDim first, which returns lot of rows, then only join it to WHSInventReserve and continue filter from there.

        #-- If we makes it get records from WHSInventReserve first, filter it, then with lesser rows from here, join it to InventDim, it will be quicker.

        #-- So the optimization is to filter WHSInventResreve first, then use this result at the original query. In the test, this has reduce the execution

        #-- time from ~250ms to ~7ms

        #  INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)

        #  SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED

        #    FROM WHSINVENTRESERVE WHSINVENTRESERVE

        #   WHERE WHSINVENTRESERVE.ITEMID         = @ITEMID

        #     AND WHSINVENTRESERVE.PARTITION      = @PARTITION

        #     AND WHSINVENTRESERVE.DATAAREAID     = @DATAAREAID

        #     AND WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL

        #

        #-- Initialize the @RESULT variable.

        #-- Loads aggregated data for the lowest level requested by user.

        #INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

        #SELECT SUM(DIMLIST.AVAILPHYSICAL),

        #    SUM(DIMLIST.AVAILORDERED),

        #    NULL,

        #    NULL,

        #    @LEVEL,

        #    INVENTDIM.CONFIGID,

        #    INVENTDIM.INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

        #    FROM INVENTDIM INVENTDIM

        #    JOIN @DIMLIST DIMLIST

        #      ON INVENTDIM.INVENTDIMID = DIMLIST.INVENTDIMID

        #    WHERE   INVENTDIM.DATAAREAID = @DATAAREAID

        #        AND INVENTDIM.PARTITION = @PARTITION

        #        AND (@CONFIGID IS NULL OR INVENTDIM.CONFIGID = @CONFIGID)

        #        AND (@INVENTSIZEID IS NULL OR INVENTDIM.INVENTSIZEID = @INVENTSIZEID)

        #        AND (@INVENTCOLORID IS NULL OR INVENTDIM.INVENTCOLORID = @INVENTCOLORID)

        #        AND (@INVENTSTYLEID IS NULL OR INVENTDIM.INVENTSTYLEID = @INVENTSTYLEID)

        #        AND (@LEVEL < @INVENTSITEIDLEVEL OR @INVENTSITEID IS NULL OR @INVENTSITEID = INVENTDIM.INVENTSITEID)

        #        AND (@LEVEL < @INVENTLOCATIONIDLEVEL OR @INVENTLOCATIONID IS NULL OR @INVENTLOCATIONID = INVENTDIM.INVENTLOCATIONID)

        #        AND (@LEVEL < @WMSLOCATIONIDLEVEL OR @WMSLOCATIONID IS NULL OR @WMSLOCATIONID = INVENTDIM.WMSLOCATIONID)

        #        AND (@LEVEL < @WMSPALLETIDLEVEL OR @WMSPALLETID IS NULL OR @WMSPALLETID = INVENTDIM.WMSPALLETID)

        #        AND (@LEVEL < @INVENTSTATUSIDLEVEL OR @INVENTSTATUSID IS NULL OR @INVENTSTATUSID = INVENTDIM.INVENTSTATUSID)

        #        AND (@LEVEL < @LICENSEPLATEIDLEVEL OR @LICENSEPLATEID IS NULL OR @LICENSEPLATEID = INVENTDIM.LICENSEPLATEID)

        #        AND (@LEVEL < @INVENTBATCHIDLEVEL OR @INVENTBATCHID IS NULL OR @INVENTBATCHID = INVENTDIM.INVENTBATCHID)

        #        AND (@LEVEL < @INVENTSERIALIDLEVEL OR @INVENTSERIALID IS NULL OR @INVENTSERIALID = INVENTDIM.INVENTSERIALID)

        #        AND (@LEVEL < @INVENTPROFILEID_RULEVEL OR @INVENTPROFILEID_RU IS NULL OR @INVENTPROFILEID_RU = INVENTDIM.INVENTPROFILEID_RU)

        #        AND (@LEVEL < @INVENTOWNERID_RULEVEL OR @INVENTOWNERID_RU IS NULL OR @INVENTOWNERID_RU = INVENTDIM.INVENTOWNERID_RU)

        #        AND (@LEVEL < @INVENTGTDID_RULEVEL OR @INVENTGTDID_RU IS NULL OR @INVENTGTDID_RU = INVENTDIM.INVENTGTDID_RU)

        #GROUP BY

        #    INVENTDIM.CONFIGID,

        #    INVENTDIM.INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END;

        #

        #WHILE (@LEVEL > 0)

        #BEGIN

        #

        #  DELETE FROM @DIMLIST

        #

        #  INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)

        #  SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED

        #    FROM WHSINVENTRESERVE WHSINVENTRESERVE

        #   WHERE WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL + -1

        #     AND WHSINVENTRESERVE.ITEMID         = @ITEMID

        #     AND WHSINVENTRESERVE.PARTITION      = @PARTITION

        #     AND WHSINVENTRESERVE.DATAAREAID     = @DATAAREAID

        #

        #INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

        #SELECT SUM(CASE WHEN RESULT.AVAILPHYSICALFOUND < RESULT.AVAILPHYSICALRESULT THEN RESULT.AVAILPHYSICALFOUND ELSE RESULT.AVAILPHYSICALRESULT END),

        #    SUM(CASE WHEN RESULT.AVAILORDEREDFOUND < RESULT.AVAILORDEREDRESULT THEN RESULT.AVAILORDEREDFOUND ELSE RESULT.AVAILORDEREDRESULT END),

        #    SUM(FOUND.AVAILPHYSICAL),

        #    SUM(FOUND.AVAILORDERED),

        #    @LEVEL + -1,

        #    RESULT.CONFIGID AS CONFIGID,

        #    RESULT.INVENTSIZEID AS INVENTSIZEID,

        #    RESULT.INVENTCOLORID AS INVENTCOLORID,

        #    RESULT.INVENTSTYLEID AS INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN RESULT.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN RESULT.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN RESULT.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN RESULT.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN RESULT.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN RESULT.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN RESULT.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN RESULT.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN RESULT.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN RESULT.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN RESULT.INVENTGTDID_RU ELSE NULL END

        #FROM @RESULT RESULT

        #LEFT OUTER JOIN (

        #SELECT SUM(DIMLIST.AVAILPHYSICAL) AS AVAILPHYSICAL,

        #    SUM(DIMLIST.AVAILORDERED) AS AVAILORDERED,

        #    INVENTDIM.CONFIGID AS CONFIGID,

        #    INVENTDIM.INVENTSIZEID AS INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID AS INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID AS INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END AS INVENTSITEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END AS INVENTLOCATIONID,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END AS WMSLOCATIONID,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END AS WMSPALLETID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END AS INVENTSTATUSID,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END AS LICENSEPLATEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END AS INVENTBATCHID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END AS INVENTSERIALID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END AS INVENTPROFILEID_RU,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END AS INVENTOWNERID_RU,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END AS INVENTGTDID_RU

        #FROM INVENTDIM INVENTDIM

        #JOIN @DIMLIST DIMLIST

        #     ON INVENTDIM.PARTITION = @PARTITION

        #    AND INVENTDIM.DATAAREAID  = @DATAAREAID

        #    AND INVENTDIM.INVENTDIMID = DIMLIST.INVENTDIMID

        #GROUP BY

        #    INVENTDIM.CONFIGID,

        #    INVENTDIM.INVENTSIZEID,

        #    INVENTDIM.INVENTCOLORID,

        #    INVENTDIM.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

        #    ) AS FOUND ON (RESULT.CONFIGID = FOUND.CONFIGID)

        #        AND (RESULT.INVENTSIZEID = FOUND.INVENTSIZEID)

        #        AND (RESULT.INVENTCOLORID = FOUND.INVENTCOLORID)

        #        AND (RESULT.INVENTSTYLEID = FOUND.INVENTSTYLEID)

        #        AND (@LEVEL + -1 < @INVENTSITEIDLEVEL OR RESULT.INVENTSITEID = FOUND.INVENTSITEID)

        #        AND (@LEVEL + -1 < @INVENTLOCATIONIDLEVEL OR RESULT.INVENTLOCATIONID = FOUND.INVENTLOCATIONID)

        #        AND (@LEVEL + -1 < @WMSLOCATIONIDLEVEL OR RESULT.WMSLOCATIONID = FOUND.WMSLOCATIONID)

        #        AND (@LEVEL + -1 < @WMSPALLETIDLEVEL OR RESULT.WMSPALLETID = FOUND.WMSPALLETID)

        #        AND (@LEVEL + -1 < @INVENTSTATUSIDLEVEL OR RESULT.INVENTSTATUSID = FOUND.INVENTSTATUSID)

        #        AND (@LEVEL + -1 < @LICENSEPLATEIDLEVEL OR RESULT.LICENSEPLATEID = FOUND.LICENSEPLATEID)

        #        AND (@LEVEL + -1 < @INVENTBATCHIDLEVEL OR RESULT.INVENTBATCHID = FOUND.INVENTBATCHID)

        #        AND (@LEVEL + -1 < @INVENTSERIALIDLEVEL OR RESULT.INVENTSERIALID = FOUND.INVENTSERIALID)

        #        AND (@LEVEL + -1 < @INVENTPROFILEID_RULEVEL OR RESULT.INVENTPROFILEID_RU = FOUND.INVENTPROFILEID_RU)

        #        AND (@LEVEL + -1 < @INVENTOWNERID_RULEVEL OR RESULT.INVENTOWNERID_RU = FOUND.INVENTOWNERID_RU)

        #        AND (@LEVEL + -1 < @INVENTGTDID_RULEVEL OR RESULT.INVENTGTDID_RU = FOUND.INVENTGTDID_RU)

        #WHERE RESULT.HIERARCHYLEVEL = @LEVEL

        #GROUP BY

        #    RESULT.CONFIGID,

        #    RESULT.INVENTSIZEID,

        #    RESULT.INVENTCOLORID,

        #    RESULT.INVENTSTYLEID,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN RESULT.INVENTSITEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN RESULT.INVENTLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN RESULT.WMSLOCATIONID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN RESULT.WMSPALLETID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN RESULT.INVENTSTATUSID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN RESULT.LICENSEPLATEID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN RESULT.INVENTBATCHID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN RESULT.INVENTSERIALID ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN RESULT.INVENTPROFILEID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN RESULT.INVENTOWNERID_RU ELSE NULL END,

        #    CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN RESULT.INVENTGTDID_RU ELSE NULL END;

        #

        #

        #    SET @LEVEL = @LEVEL - 1

        #END; -- WHILE

        #

        #SELECT @AVAILPHYSICAL = SUM(CASE WHEN AVAILPHYSICALFOUND < AVAILPHYSICALRESULT THEN AVAILPHYSICALFOUND ELSE AVAILPHYSICALRESULT END),

        #    @AVAILORDERED = SUM(CASE WHEN AVAILORDEREDFOUND < AVAILORDEREDRESULT THEN AVAILORDEREDFOUND ELSE AVAILORDEREDRESULT END)

        #FROM @RESULT

        #WHERE HIERARCHYLEVEL = 0;

        #    */

        #

        #    ret += strFmt('CREATE PROCEDURE [%1]\n', WhsOnHandSPHelper::storedProcedureName(_includeDelta));

        #    ret +=        '    @PARTITION bigint,\n';

        #    ret += strFmt('    %1 int,\n', this.levelExpression(0));

        #    ret += strFmt('    @DATAAREAID %1,\n', this.sqlTypeNameForField(tableNum(InventTable), fieldNum(InventTable, DataAreaId)));

        #    ret += strFmt('    @ITEMID %1,\n', this.sqlTypeNameForField(tableNum(InventTable), fieldNum(InventTable, ItemId)));

        #    if (_includeDelta)

        #    {

        #        ret += strFmt('    %1 %2,\n', #TTSIdParmName, this.sqlTypeNameForField(tableNum(WHSInventReserveDelta), fieldNum(WHSInventReserveDelta, TTSId)));

        #    }

        #

        #    // Generate parameters for supported dimensions

        #    enabledDimsEnumerator.reset();

        #    while (enabledDimsEnumerator.moveNext())

        #    {

        #        [dimFieldId, dimFieldName] = enabledDimsEnumerator.current();

        #        ret += strFmt('    %1 %2 = NULL,\n', this.dimensionParm(dimFieldName), this.sqlTypeNameForField(tableNum(InventDim), dimFieldId));

        #        ret += strFmt('    %1 int = %2,\n', this.dimensionLevelParm(dimFieldName), #INVALIDHIERARCHYLEVEL);

        #    }

        #

        #    // Generate parameters for product dimensions

        #    productDimensionsEnumerator.reset();

        #    while (productDimensionsEnumerator.moveNext())

        #    {

        #        [dimFieldId, dimFieldName] = productDimensionsEnumerator.current();

        #        ret += strFmt('    %1 %2 = NULL,\n', this.dimensionParm(dimFieldName), this.sqlTypeNameForField(tableNum(InventDim), dimFieldId));

        #    }

        #

        #    ret +=        '    -- Output parameters\n';

        #    ret += strFmt('    %1  numeric(32, 16) OUTPUT,\n', #AvailPhysicalParmName);

        #    ret += strFmt('    %1  numeric(32, 16) OUTPUT\n', #AvailOrderedParmName);

        #    ret +=        'AS\n\n';

        #

        #    ret +=        '-- Without nocount on the procedure will return number of affected rows in the result set which is\n';

        #    ret +=        '-- harmful to performance and requires us to adjust field retrieval when calling the stored procedure\n';

        #    ret +=        'SET NOCOUNT ON;\n\n';

        #

        #    ret +=        '-- We need to supress warnings when trying to aggregate NULL columns. This is a valid business scenario\n';

        #    ret +=        '-- and happens e.g. for a non-LP controlled location with batch below item.\n';

        #    ret +=        'SET ANSI_WARNINGS OFF;\n\n';

        #

        #    ret += this.declareResultTableVariable() + '\n';

        #

        #    ret +=        'DECLARE @DIMLIST TABLE(\n';

        #    ret +=        '    INVENTDIMID nvarchar (20) NULL,\n';

        #    ret +=        '    AVAILPHYSICAL numeric(32, 16) NOT NULL,\n';

        #    ret +=        '    AVAILORDERED numeric(32, 16) NOT NULL);\n\n';

        #

        #    ret +=        '-- The algorithm of the stored procedure is as follows:\n';

        #    ret +=        '-- 1. Initialize the @RESULT table with aggregated qtys in WHSInventReserve for the level requested by user\n';

        #    ret +=        '-- 2. Traverse the hierarchy up and for each level:\n';

        #    ret +=        '--     1) Aggregate qtys from previous level in @RESULT grouping by dimensions of current hierarchy level\n';

        #    ret +=        '--     2) Aggregate qtys from current level in WHSInventReserve grouping by dimensions of current hierarchy level\n';

        #    ret +=        '--     3) Select the minimum of the above qtys and insert into @RESULT\n';

        #    ret +=        '-- 3. When hierarchy level 0 is reached, return the sum of aggregated qtys in @RESULT\n\n';

        #

        #    ret +=        '-- In the original query (the one after below query), it is joining InventDim and WHSInventReserve and filter records based on some criteria.\n';

        #    ret +=        '-- But in the execution plan, it doesn\'t seems to generate a good plan (even after UPDATE STATISTICS). In the original execution plan, it always\n';

        #    ret +=        '-- Get records from InventDim first, which returns lot of rows, then only join it to WHSInventReserve and continue filter from there.\n';

        #    ret +=        '-- If we makes it get records from WHSInventReserve first, filter it, then with lesser rows from here, join it to InventDim, it will be quicker.\n';

        #    ret +=        '-- So the optimization is to filter WHSInventResreve first, then use this result at the original query. In the test, this has reduce the execution\n';

        #    ret +=        '-- time from ~250ms to ~7ms\n';

        #    ret +=        '  INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)\n';

        #    ret +=        '  SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED\n';

        #    ret += strFmt('    FROM %1 %2\n', this.whsInventReserveTableName(_includeDelta), inventReserveAlias);

        #    ret += strFmt('   WHERE %1.ITEMID         = @ITEMID\n', inventReserveAlias);

        #    ret += strFmt('     AND %1.PARTITION      = @PARTITION\n', inventReserveAlias);

        #    ret += strFmt('     AND %1.DATAAREAID     = @DATAAREAID\n', inventReserveAlias);

        #    ret += strFmt('     AND %1.HIERARCHYLEVEL = %2\n\n', inventReserveAlias, this.levelExpression(0));

        #

        #    ret +=        '-- Initialize the @RESULT variable.\n';

        #    ret +=        '-- Loads aggregated data for the lowest level requested by user.\n';

        #    ret +=        'INSERT INTO @RESULT (' + this.resultTableVariableFields() + ')\n';

        #

        #    ret += this.selectFromWHSInventReserveStmt(_includeDelta) + '\n';

        #

        #    ret += strFmt('WHILE (%1 > 0)\n', this.levelExpression(0));

        #    ret +=        'BEGIN\n';

        #

        #    ret += strFmt('  DELETE FROM @DIMLIST\n\n');

        #

        #    ret += strFmt('  INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)\n');

        #    ret += strFmt('  SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED\n');

        #    ret += strFmt('    FROM %1 %2\n', this.whsInventReserveTableName(_includeDelta), inventReserveAlias);

        #    ret += strFmt('   WHERE %1.HIERARCHYLEVEL = %2 + -1\n', inventReserveAlias, this.levelExpression(0));

        #    ret += strFmt('     AND %1.ITEMID         = @ITEMID\n', inventReserveAlias);

        #    ret += strFmt('     AND %1.PARTITION      = @PARTITION\n', inventReserveAlias);

        #    ret += strFmt('     AND %1.DATAAREAID     = @DATAAREAID\n\n', inventReserveAlias);

        #

        #    ret += strFmt('    INSERT INTO @RESULT (%1)\n', this.resultTableVariableFields());

        #

        #    ret += this.selectFromResultJoinWHSInventReserveStmt(_includeDelta) + '\n\n';

        #

        #    ret += strFmt('    SET %1 = %1 - 1\n', this.levelExpression(0));

        #    ret +=        'END; -- WHILE\n\n';

        #

        #    ret += strFmt('SELECT %1 = SUM(CASE WHEN %2 < %3 THEN %2 ELSE %3 END),\n',

        #                    #AvailPhysicalParmName,

        #                    #AVAILPHYSICALFOUNDFieldName,

        #                    #AVAILPHYSICALRESULTFieldName);

        #    ret += strFmt('    %1 = SUM(CASE WHEN %2 < %3 THEN %2 ELSE %3 END)\n',

        #                    #AvailOrderedParmName,

        #                    #AVAILORDEREDFOUNDFieldName,

        #                    #AVAILORDEREDRESULTFieldName);

        #    ret +=        'FROM @RESULT\n';

        #    ret += strFmt('WHERE %1 = 0;\n', #HIERARCHYLEVELFieldName);

        #

        #    return ret;

        #}

      ENDSOURCE

      SOURCE #TEC_selectFromWHSInventReserveStmt

        #private str TEC_selectFromWHSInventReserveStmt(boolean _includeDelta)

        #{

        #    str             ret = '';

        #    ListEnumerator  enabledDimsEnumerator = this.enabledDimensions().getEnumerator();

        #    ListEnumerator  productDimsEnumerator = this.productDimensions().getEnumerator();

        #    str             dimFieldName;

        #    str             levelExpr = this.levelExpression(-1);

        #    str             inventReserveAlias = this.whsInventReserveTableAlias(_includeDelta);

        #    str             dimList = 'DIMLIST';

        #    boolean         hasDoneFirstIteration;

        #

        #    ret += strFmt('SELECT SUM(%1.AVAILPHYSICAL),\n', dimList);

        #    ret += strFmt('    SUM(%1.AVAILORDERED),\n', dimList);

        #    ret +=        '    NULL,\n';

        #    ret +=        '    NULL,\n';

        #    ret += strFmt('    %1,\n', this.levelExpression(0));

        #

        #    // Select product dimensions

        #    productDimsEnumerator.reset();

        #    while (productDimsEnumerator.moveNext())

        #    {

        #        dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #        ret += strFmt('    INVENTDIM.%1,\n', dimFieldName);

        #    }

        #

        #    // Select enabled dimensions

        #    enabledDimsEnumerator.reset();

        #    hasDoneFirstIteration = false;

        #    while (enabledDimsEnumerator.moveNext())

        #    {

        #        if (hasDoneFirstIteration)

        #        {

        #            ret += ',\n';

        #        }

        #

        #        dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #        ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END',

        #                        this.levelExpression(-1),

        #                        this.dimensionLevelParm(dimFieldName),

        #                        dimFieldName);

        #

        #        hasDoneFirstIteration = true;

        #    }

        #    ret += '\n';

        #

        #    //ret += strFmt('    FROM %1 %2\n', this.whsInventReserveTableName(_includeDelta), inventReserveAlias);

        #    //ret += strFmt('    JOIN INVENTDIM ON INVENTDIM.INVENTDIMID = %1.INVENTDIMID AND INVENTDIM.DATAAREAID = %1.DATAAREAID AND INVENTDIM.PARTITION = %1.PARTITION\n', inventReserveAlias);

        #    //ret += strFmt('    WHERE %1.ITEMID           = @ITEMID\n', inventReserveAlias);

        #    //ret += strFmt('        AND %1.PARTITION      = @PARTITION\n', inventReserveAlias);

        #    //ret += strFmt('        AND %1.DATAAREAID     = @DATAAREAID\n', inventReserveAlias);

        #    //ret += strFmt('        AND %1.HIERARCHYLEVEL = %2\n', inventReserveAlias, this.levelExpression(0));

        #    ret += strFmt('    FROM INVENTDIM INVENTDIM\n');

        #    ret += strFmt('    JOIN @DIMLIST %1\n', dimList);

        #    ret += strFmt('      ON INVENTDIM.INVENTDIMID = %1.INVENTDIMID\n', dimList);

        #    ret += strFmt('    WHERE   INVENTDIM.DATAAREAID = @DATAAREAID\n');

        #    ret += strFmt('        AND INVENTDIM.PARTITION = @PARTITION\n');

        #

        #    // Add product dimensions range

        #    productDimsEnumerator.reset();

        #    while (productDimsEnumerator.moveNext())

        #    {

        #        dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #        ret += strFmt('        AND (%2 IS NULL OR INVENTDIM.%1 = %2)\n',

        #                        dimFieldName,

        #                        this.dimensionParm(dimFieldName));

        #    }

        #

        #    // Add enabled dimensions range

        #    enabledDimsEnumerator.reset();

        #    while (enabledDimsEnumerator.moveNext())

        #    {

        #        dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #        ret += strFmt('        AND (%1 < %2 OR %3 IS NULL OR %3 = INVENTDIM.%4)\n',

        #                        this.levelExpression(0),

        #                        this.dimensionLevelParm(dimFieldName),

        #                        this.dimensionParm(dimFieldName),

        #                        dimFieldName);

        #    }

        #

        #    ret +=        'GROUP BY\n';

        #

        #    // Add grouping by product dimensions

        #    productDimsEnumerator.reset();

        #    while (productDimsEnumerator.moveNext())

        #    {

        #        dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #        ret += strFmt('    INVENTDIM.%1,\n', dimFieldName);

        #    }

        #

        #    // Add grouping by enabled dimensions

        #    enabledDimsEnumerator.reset();

        #    hasDoneFirstIteration = false;

        #    while (enabledDimsEnumerator.moveNext())

        #    {

        #        if (hasDoneFirstIteration)

        #        {

        #            ret += ',\n';

        #        }

        #

        #        dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #        ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END',

        #                        this.levelExpression(-1),

        #                        this.dimensionLevelParm(dimFieldName),

        #                        dimFieldName);

        #

        #        hasDoneFirstIteration = true;

        #    }

        #

        #    ret += ';\n';

        #

        #    return ret;

        #}

      ENDSOURCE

      SOURCE #TEC_selectInnerFromWHSInventReserveStmt

        #private str TEC_selectInnerFromWHSInventReserveStmt(boolean _includeDelta)

        #{

        #    str             ret = '';

        #    ListEnumerator  enabledDimsEnumerator = this.enabledDimensions().getEnumerator();

        #    ListEnumerator  productDimsEnumerator = this.productDimensions().getEnumerator();

        #    str             dimFieldName;

        #    str             levelExpr = this.levelExpression(-1);

        #    str             inventReserveAlias = this.whsInventReserveTableAlias(_includeDelta);

        #    str             dimList = 'DIMLIST';

        #    boolean         hasDoneFirstIteration;

        #

        #    ret += strFmt('SELECT SUM(%1.AVAILPHYSICAL) AS AVAILPHYSICAL,\n', dimList);

        #    ret += strFmt('    SUM(%1.AVAILORDERED) AS AVAILORDERED,\n', dimList);

        #

        #    productDimsEnumerator.reset();

        #    while (productDimsEnumerator.moveNext())

        #    {

        #        dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #        ret += strFmt('    INVENTDIM.%1 AS %1,\n', dimFieldName);

        #    }

        #

        #    enabledDimsEnumerator.reset();

        #    hasDoneFirstIteration = false;

        #    while (enabledDimsEnumerator.moveNext())

        #    {

        #        if (hasDoneFirstIteration)

        #        {

        #            ret += ',\n';

        #        }

        #

        #        dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #        ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END AS %3',

        #                        levelExpr,

        #                        this.dimensionLevelParm(dimFieldName),

        #                        dimFieldName);

        #

        #        hasDoneFirstIteration = true;

        #    }

        #    ret += '\n';

        #

        #    //ret += strFmt('FROM %1 %2\n', this.whsInventReserveTableName(includeDelta), inventReserveAlias);

        #    //ret += strFmt('JOIN INVENTDIM ON INVENTDIM.PARTITION = %1.PARTITION\n', inventReserveAlias);

        #    //ret += strFmt('    AND INVENTDIM.DATAAREAID  = %1.DATAAREAID\n', inventReserveAlias);

        #    //ret += strFmt('    AND INVENTDIM.INVENTDIMID = %1.INVENTDIMID\n', inventReserveAlias);

        #    //ret += strFmt('WHERE %1.HIERARCHYLEVEL = %2\n', inventReserveAlias, levelExpr);

        #    //ret += strFmt('    AND %1.ITEMID       = @ITEMID\n', inventReserveAlias);

        #    //ret += strFmt('    AND %1.PARTITION    = @PARTITION\n', inventReserveAlias);

        #    //ret += strFmt('    AND %1.DATAAREAID   = @DATAAREAID\n', inventReserveAlias);

        #    ret += strFmt('FROM INVENTDIM INVENTDIM\n');

        #    ret += strFmt('JOIN @DIMLIST %1\n', dimList);

        #    ret += strFmt('     ON INVENTDIM.PARTITION = @PARTITION\n');

        #    ret += strFmt('    AND INVENTDIM.DATAAREAID  = @DATAAREAID\n');

        #    ret += strFmt('    AND INVENTDIM.INVENTDIMID = DIMLIST.INVENTDIMID\n');

        #    ret +=          'GROUP BY\n';

        #

        #    // Add groupping by product dimensions

        #    productDimsEnumerator.reset();

        #    while (productDimsEnumerator.moveNext())

        #    {

        #        dimFieldName = conPeek(productDimsEnumerator.current(), 2);

        #        ret += strFmt('    INVENTDIM.%1,\n', dimFieldName);

        #    }

        #

        #    // Add groupping by enabled dimensions

        #    enabledDimsEnumerator.reset();

        #    hasDoneFirstIteration = false;

        #    while (enabledDimsEnumerator.moveNext())

        #    {

        #        if (hasDoneFirstIteration)

        #        {

        #            ret += ',\n';

        #        }

        #

        #        dimFieldName = conPeek(enabledDimsEnumerator.current(), 2);

        #        ret += strFmt('    CASE WHEN %1 >= %2 THEN INVENTDIM.%3 ELSE NULL END',

        #                        levelExpr,

        #                        this.dimensionLevelParm(dimFieldName),

        #                        dimFieldName);

        #

        #        hasDoneFirstIteration = true;

        #    }

        #    ret += '\n';

        #

        #    return ret;

        #}

      ENDSOURCE

    ENDMETHODS

  ENDCLASS

***Element: END

=================================================