Friday, June 8, 2018

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

No comments:

Post a Comment