Inventory Value Query
The main view upon which the inventory value reports is based, is InventValueReportView which has semi-calculated inventory values which can be used to calculate the inventory value for items as of a certain date with simple query filtering and aggregation functions.
The simple query below results in the inventory value (quantity and amount) of 2 items as of a date:
DECLARE @ASOFDATE DATE
DECLARE @DATENULL DATE
DECLARE @DATEMAX DATE
DECLARE @YEARSTART DATE
DECLARE @DATAAREAID NVARCHAR(4)
DECLARE @PARTITION BIGINT
SET @ASOFDATE = '2017-07-31'
SET @DATENULL = '1900-01-01'
SET @DATEMAX = '2154-12-31'
SET @DATAAREAID = 'YIAC'
SET @PARTITION = 5637144576
SELECT
IVR.PARTITION
, IVR.DATAAREAID
, IVR.ITEMID
, SUM(IVR.QTY) QTY
, SUM(IVR.AMOUNT) AMOUNT
FROM
INVENTVALUEREPORTVIEW IVR
WHERE
1 = 1
AND IVR.PARTITION = @PARTITION
AND IVR.DATAAREAID = @DATAAREAID
AND IVR.TRANSDATE < @DATEMAX
AND IVR.TRANSDATE < @ASOFDATE
AND IVR.ITEMID IN ('PHABN0095N', 'JJCRD4244030WN')
GROUP BY
IVR.PARTITION
, IVR.DATAAREAID
, IVR.ITEMID
ORDER BY
IVR.ITEMID
You can see from the previous query that by simply putting a date filter (in the where clause) and aggregating by ItemId we get the result of the inventory value for the items in the where clause (or any items you choose for that matter).
The use of the PARTITION and DATAAREAID fields are for the purpose of enhancing the performance since they are used in the table index.
While this is enough to get the inventory value, it is almost always required to get more information along with the item (item name, dimensions, group ... etc.). In this aim, we utilize joins between the related tables to the item table in order to get the required data.
A very interesting requirement here is to emulate the function of dimension display in Dynamics AX can be implemented by using a technique that combines switching (CASE), aggregation and nesting to achieve the result.
The idea is to join the query records with their corresponding inventory dimensions InventDim. The CASE statement will be used to choose whether to project the inventory dimension field value or a blank instead (emulating dimension display is discussed in another post).
The final result is a query that implements the inventory value calculation algorithm, dimension display emulation, and joins with product information tables to get a complete report on inventory value, as in the below example:
DECLARE @ASOFDATE DATE = '2017-08-29'
DECLARE @DATENULL DATE = '1900-01-01'
DECLARE @DATEMAX DATE = '2154-12-31'
DECLARE @DATAAREAID NVARCHAR(4) = 'YIAC'
DECLARE @PARTITION BIGINT = 5637144576
DECLARE @BYIT BIT = 1
DECLARE @BYWH BIT = 0
DECLARE @BYLC BIT = 0
DECLARE @BYBH BIT = 0
DECLARE @BYSN BIT = 0
DECLARE @SHOWZERO BIT = 1
DECLARE @DIMATTRSEG BIGINT = 5637145326
DECLARE @DIMATTRDPT BIGINT = 5637145327
DECLARE @DIMATTRDIV BIGINT = 5637145328
DECLARE @DIMATTRPCP BIGINT = 5637145329
SELECT
IVR.PARTITION
, IVR.DATAAREAID
, IVR.ITEMID
, TXT.NAME
, PCP.DISPLAYVALUE PRINCIPAL
, IVR.WAREHOUSE
, IVR.LOCATION
, IVR.BATCH
, IVR.SERIAL
, IVR.QTY
, IVR.AMOUNT
/*
Nesting the query so that grouping occurs only in the inner query
and joins are done on the outer query
*/
FROM (
SELECT
IVR.PARTITION
, IVR.DATAAREAID
/*
Dimension display emulation with tracking/storage dimension fields
*/
, CASE
WHEN @BYIT = 1 THEN IVR.ITEMID
ELSE ''
END ITEMID
, CASE
WHEN @BYWH = 1 AND SDG.WAREHOUSE = 1 THEN DIM.INVENTLOCATIONID
ELSE ''
END WAREHOUSE
, CASE
WHEN @BYLC = 1 AND SDG.LOCATION = 1 THEN DIM.WMSLOCATIONID
ELSE ''
END LOCATION
, CASE
WHEN @BYBH = 1 AND TDG.BATCH = 1 THEN DIM.INVENTBATCHID
ELSE ''
END BATCH
, CASE
WHEN @BYSN = 1 AND TDG.SERIAL = 1 THEN DIM.INVENTSERIALID
ELSE ''
END SERIAL
/*
Quantity and amount aggregated as per the dimension display grouping fields
*/
, SUM(IVR.QTY) QTY
, SUM(IVR.AMOUNT) AMOUNT
FROM
INVENTVALUEREPORTVIEW IVR
INNER JOIN INVENTDIM DIM
ON DIM.PARTITION = IVR.PARTITION
AND DIM.DATAAREAID = IVR.DATAAREAID
AND DIM.INVENTDIMID = IVR.INVENTDIMID
INNER JOIN ECORESTRACKINGDIMENSIONGROUPITEM TDR
ON TDR.PARTITION = IVR.PARTITION
AND TDR.ITEMDATAAREAID = IVR.DATAAREAID
AND TDR.ITEMID = IVR.ITEMID
INNER JOIN ECORESSTORAGEDIMENSIONGROUPITEM SDR
ON SDR.PARTITION = IVR.PARTITION
AND SDR.ITEMDATAAREAID = IVR.DATAAREAID
AND SDR.ITEMID = IVR.ITEMID
/*
Storage dimension group finanacial iventory field setup
*/
INNER JOIN (
SELECT
PVT.STORAGEDIMENSIONGROUP
, SUM(ISNULL(PVT.[16], 0)) [SITE]
, SUM(ISNULL(PVT.[6], 0)) WAREHOUSE
, SUM(ISNULL(PVT.[3], 0)) LOCATION
FROM
ECORESSTORAGEDIMENSIONGROUPFLDSETUP SDG
PIVOT (SUM(SDG.ISFINANCIALINVENTORYENABLED) FOR SDG.DIMENSIONFIELDID IN ([3], [6], [16], [20001], [20002])) PVT
WHERE
PVT.ISACTIVE = 1
GROUP BY
PVT.STORAGEDIMENSIONGROUP
) SDG
ON SDG.STORAGEDIMENSIONGROUP = SDR.STORAGEDIMENSIONGROUP
/*
Tracking dimension group finanacial iventory field setup
*/
INNER JOIN (
SELECT PVT.TRACKINGDIMENSIONGROUP
, SUM(ISNULL(PVT.[2], 0)) BATCH
, SUM(ISNULL(PVT.[5], 0)) SERIAL
FROM ECORESTRACKINGDIMENSIONGROUPFLDSETUP TDG
PIVOT (SUM(TDG.ISFINANCIALINVENTORYENABLED) FOR TDG.DIMENSIONFIELDID IN ([2], [5])) PVT
WHERE PVT.ISACTIVE = 1
GROUP BY PVT.TRACKINGDIMENSIONGROUP
) TDG
ON TDG.TRACKINGDIMENSIONGROUP = TDR.TRACKINGDIMENSIONGROUP
WHERE
1 = 1
/*
1 = 1 is used just as a place holder so that other conditions in the
where clause can be commented out without breaking the query
*/
AND IVR.PARTITION = @PARTITION
AND IVR.DATAAREAID = @DATAAREAID
AND IVR.TRANSDATE < @DATEMAX
AND IVR.TRANSDATE < @ASOFDATE
GROUP BY
IVR.PARTITION
, IVR.DATAAREAID
/*
Grouping by dimension display settings combined with tracking/storage dimension group setup
*/
, CASE
WHEN @BYIT = 1 THEN IVR.ITEMID
ELSE ''
END
, CASE
WHEN @BYWH = 1 AND SDG.WAREHOUSE = 1 THEN DIM.INVENTLOCATIONID
ELSE ''
END
, CASE
WHEN @BYLC = 1 AND SDG.LOCATION = 1 THEN DIM.WMSLOCATIONID
ELSE ''
END
, CASE
WHEN @BYBH = 1 AND TDG.BATCH = 1 THEN DIM.INVENTBATCHID
ELSE ''
END
, CASE
WHEN @BYSN = 1 AND TDG.SERIAL = 1 THEN DIM.INVENTSERIALID
ELSE ''
END
) IVR
/*
Join with InventTable, EcoResProduct and EcoResProductTransaction to get the item name
*/
INNER JOIN INVENTTABLE ITM
ON ITM.PARTITION = IVR.PARTITION
AND ITM.DATAAREAID = IVR.DATAAREAID
AND ITM.ITEMID = IVR.ITEMID
INNER JOIN ECORESPRODUCT PRD
ON PRD.PARTITION = ITM.PARTITION
AND PRD.DISPLAYPRODUCTNUMBER = ITM.ITEMID
INNER JOIN ECORESPRODUCTTRANSLATION TXT
ON TXT.PARTITION = PRD.PARTITION
AND TXT.PRODUCT = PRD.RECID
AND TXT.LANGUAGEID = 'EN-US'
/*
Join with DimensionAttributeValueSetItemView to get the related financial dimension code (principal)
Other financial dimensions can be joined in the same manner by just
chaning the dimension attribute parameter (e.g. @DIMATTRDIV)
*/
LEFT OUTER JOIN DIMENSIONATTRIBUTEVALUESETITEMVIEW PCP
ON PCP.DIMENSIONATTRIBUTEVALUESET = ITM.DEFAULTDIMENSION
AND PCP.DIMENSIONATTRIBUTE = @DIMATTRPCP
WHERE
1 = 1
ORDER BY
IVR.ITEMID
No comments:
Post a Comment