Minimum and maximum from ReqItemTable

(1) Share

Report

Posted on by faiz7049

1,883

Hi Experts,

Customer requirement to get item minimum and maximum from ReqItemTable with Availablephysical and Ordered quantities

The AvilablePhysical and Ordered quantity not respecting inventDimId. It is giving me all AvailablePhysical and Ordered quantities for an itemid.

SELECT 
    R.ITEMID,
    INVENTDIM.CONFIGID,
    INVENTDIM.INVENTSTYLEID,
    INVENTDIM.INVENTSIZEID,
    INVENTDIM.INVENTCOLORID,
    INVENTDIM.INVENTSITEID,
	INVENTDIM.INVENTLOCATIONID,
    R.COVINVENTDIMID,
    R.MININVENTONHAND,
    R.MAXINVENTONHAND,
    SUM(ISUM.AvailPhysical) AS TotalAvailPhysical,
	SUM(ISUM.ORDERED) AS TotalAvailORDER
FROM 
    REQITEMTABLE R
JOIN 
    INVENTDIM ON R.COVINVENTDIMID = INVENTDIM.INVENTDIMID
JOIN 
    INVENTSUM ISUM ON R.ITEMID = ISUM.ITEMID 
WHERE 
    R.COVRULE = 2
   
GROUP BY
    R.ITEMID,
    INVENTDIM.CONFIGID,
    INVENTDIM.INVENTSTYLEID,
    INVENTDIM.INVENTSIZEID,
    INVENTDIM.INVENTCOLORID,
    INVENTDIM.INVENTSITEID,
	INVENTDIM.INVENTLOCATIONID,
    R.COVINVENTDIMID,
    R.MININVENTONHAND,
    R.MAXINVENTONHAND

Categories:

Dynamics 365 Supply Chain Management

All responses (3)

Answers (0)

faiz7049 1,883 on at

Like (1)

Report

Minimum and maximum from ReqItemTable

Thank you Jac rod.
lvdtang 1,917 on at

Like (0)

Report

Minimum and maximum from ReqItemTable

Good day,

Which dimensions are you missing?

Best regards,

Laurens van der Tang
jac_rod 1,299 on at

Like (1)

Report

Minimum and maximum from ReqItemTable

Check the settings on the dimension groups (especially the storage dimension group) and confirm that the "Coverage plan by dimension" checkbox is checked for the dimensions that you're interested in.