Dynamics GP - SQL VIEW - Received Costs vs Invoiced Costs - see where costing went wrong

This view should be used to help identify any discrepancies in item costing if the automatic revaluation was not enabled

• Objects used
• POP30300 - RctHeader Hist (Receipts and invoices)
• POP30310 - RctLineHist (Receipts and invoices)
• POP10600 - Rct to Invoice Match
• IV10200 - Inv Cost layers

CREATE VIEW [dbo].[BI_POP_RctvsInvCost]

AS

SELECT        RCTHDR.POPRCTNM AS RctNum, RCT.RCPTLNNM AS RctLine, RCTHDR.GLPOSTDT AS RctPostDate, INVHDR.POPRCTNM AS InvNum, INVOICE.RCPTLNNM AS InvLine, INVHDR.GLPOSTDT AS InvPostDate, 

                         RCT.ITEMNMBR AS RctItem, RCT.ITEMDESC AS RctItemDesc, RCT.EXTDCOST AS RctExtCost, INVOICE.EXTDCOST AS InvExtCost, InvLayers.RctSold, InvLayers.QtyRcd, InvLayers.QtySold

FROM            dbo.POP30310 AS RCT LEFT OUTER JOIN

                             (SELECT        RCPTNMBR, ITEMNMBR, SUM(RCPTSOLD) AS RctSold, SUM(QTYRECVD) AS QtyRcd, SUM(QTYSOLD) AS QtySold

                               FROM            dbo.IV10200 AS IV10200_1

                               GROUP BY ITEMNMBR, RCPTNMBR) AS InvLayers ON RCT.POPRCTNM = InvLayers.RCPTNMBR AND RCT.ITEMNMBR = InvLayers.ITEMNMBR LEFT OUTER JOIN

                         dbo.POP30300 AS RCTHDR ON RCT.POPRCTNM = RCTHDR.POPRCTNM RIGHT OUTER JOIN

                         dbo.POP30300 AS INVHDR RIGHT OUTER JOIN

                         dbo.POP30310 AS INVOICE ON INVHDR.POPRCTNM = INVOICE.POPRCTNM RIGHT OUTER JOIN

                         dbo.POP10600 ON INVOICE.POPRCTNM = dbo.POP10600.POPIVCNO AND INVOICE.RCPTLNNM = dbo.POP10600.IVCLINNO ON RCT.POPRCTNM = dbo.POP10600.POPRCTNM AND 

                         RCT.RCPTLNNM = dbo.POP10600.RCPTLNNM

GO