Thursday, August 24, 2017

Dynamics GP - Current Cost is wrong. Compare Last Received Cost to Current Cost Values

/****** Object:  View [dbo].[BI_AUDIT_CurrentCost]    Script Date: 8/24/2017 10:39:14 AM ******/
//This will take the lowest cost of all receipts for an item on the last date received to get around the //issue of large rounded 1-item costs

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AUDIT_CurrentCost]
AS
SELECT        TOP (100) PERCENT dbo.IV10200.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV10200.RCTSEQNM AS LRSNM, dbo.IV10200.DATERECD, dbo.IV10200.QTYONHND, dbo.IV10200.UNITCOST,
                         dbo.IV10200.DEX_ROW_ID AS Lastentry, dbo.IV00101.CURRCOST, LRSNM.LsLowRcvCost, LRSNM.LsLowRcvCost - dbo.IV00101.CURRCOST AS CostDiff, Qty.AllQtyOnHnd
FROM            (SELECT        ITEMNMBR, QTYONHND AS AllQtyOnHnd
                          FROM            dbo.IV00102
                          WHERE        (LOCNCODE = '')) AS Qty RIGHT OUTER JOIN
                         dbo.IV00101 ON Qty.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
                         dbo.IV10200 INNER JOIN
                             (SELECT        IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM, MIN(IV10200_2.UNITCOST) AS LsLowRcvCost
                               FROM            dbo.IV10200 AS IV10200_2 INNER JOIN
                                                             (SELECT        ITEMNMBR, MAX(DATERECD) AS LDR
                                                               FROM            dbo.IV10200 AS IV10200_1
                                                               GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                               GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON dbo.IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND dbo.IV10200.RCTSEQNM = LRSNM.LRSNM ON
                         dbo.IV00101.ITEMNMBR = dbo.IV10200.ITEMNMBR
ORDER BY CostDiff
GO

No comments:

Post a Comment