This view was used to help identify costing differences between purchase receipts, purchase invoices and sales invoice lines
This view was used as part of this solution
https://community.dynamics.com/gp/b/dynamicsgpessentials/archive/2014/04/27/cost-layers-analysis-sql-script
-------------------------------------------------------------------
/****** Object: View [dbo].[BI_INV_HITBTrx] Script Date: 06/09/2018 8:05:50 PM ******/
CREATE VIEW [dbo].[BI_INV_HITBTrx]
AS
SELECT dbo.SEE30303.ITEMNMBR, dbo.SEE30303.SEQNUMBR, dbo.SEE30303.DOCDATE, dbo.SEE30303.GLPOSTDT, dbo.SEE30303.DOCNUMBR, dbo.SEE30303.DOCTYPE, dbo.SEE30303.LOCNCODE,
dbo.SEE30303.RCPTNMBR, dbo.SEE30303.RCTSEQNM, dbo.SEE30303.PCHSRCTY, dbo.SEE30303.QTYTYPE, dbo.SEE30303.UOFM, dbo.SEE30303.TRXQTYInBase, dbo.SEE30303.TRXQTY,
dbo.SEE30303.VARIANCEQTY, dbo.SEE30303.UNITCOST, dbo.SEE30303.EXTDCOST, dbo.SEE30303.DECPLQTY, dbo.SEE30303.DECPLCUR, dbo.SEE30303.IsOverrideReceipt, dbo.SEE30303.IsOverrideRelieved,
dbo.SEE30303.OverrideRelievedDate, dbo.SEE30303.RCPTNMBR1, dbo.SEE30303.HSTMODUL, dbo.SEE30303.ORTRXSRC, dbo.SEE30303.LNSEQNBR, dbo.SEE30303.LNITMSEQ, dbo.SEE30303.CMPNTSEQ,
dbo.SEE30303.SRCRFRNCNMBR, dbo.SEE30303.VENDORID, dbo.SEE30303.PONUMBER, dbo.SEE30303.TRXREFERENCE, dbo.SEE30303.VCTNMTHD, dbo.SEE30303.IVIVINDX, dbo.SEE30303.IVIVOFIX,
dbo.SEE30303.JRNENTRY, dbo.SEE30303.TRXSORCE, dbo.SEE30303.DEBITAMT, dbo.SEE30303.CRDTAMNT, dbo.SEE30303.DATE1, dbo.SEE30303.TIME1, dbo.SEE30303.DEX_ROW_ID,
dbo.GL00105.ACTNUMST AS IVOffsetAcc, dbo.GL00100.ACTDESCR AS IVOffsetAccDesc, GL00100_1.ACTDESCR AS IVAcc, GL00105_1.ACTNUMST AS IVAccDesc
FROM dbo.SEE30303 INNER JOIN
dbo.GL00100 ON dbo.SEE30303.IVIVOFIX = dbo.GL00100.ACTINDX INNER JOIN
dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
dbo.GL00100 AS GL00100_1 ON dbo.SEE30303.IVIVINDX = GL00100_1.ACTINDX INNER JOIN
dbo.GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX
GO
-----------------------------------------------------------
/****** Object: View [dbo].[BI_POP_RctvsInvUnitCost] Script Date: 06/09/2018 8:06:09 PM ******/
CREATE VIEW [dbo].[BI_POP_RctvsInvUnitCost]
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.UNITCOST AS RctUnitCost, INVOICE.UNITCOST AS InvUnitCost, RCT.EXTDCOST AS RctExtCost, INVOICE.EXTDCOST AS InvExtCost,
RCT.LOCNCODE AS RctLocation, InvLayers.RctSold, InvLayers.QtyRcd, InvLayers.QtySold, RCT.NONINVEN, INVOICE.TRXSORCE AS InvTrxSource, RCT.TRXSORCE AS RctTrxSource
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
--------------------------------------------------------------
/****** Object: View [dbo].[BI_POP_RctvsSOP] Script Date: 06/09/2018 8:06:44 PM ******/
CREATE VIEW [dbo].[BI_POP_RctvsSOP]
AS
SELECT CASE A.RCPTSOLD WHEN 1 THEN 'Closed' WHEN 0 THEN 'Open' ELSE 'NA' END AS [Cost Layer Status], A.RCPTNMBR AS [In Receipt Number],
CASE A.PCHSRCTY WHEN 1 THEN 'Adjustment' WHEN 2 THEN 'Variance' WHEN 3 THEN 'Transfer' WHEN 4 THEN 'Override' WHEN 5 THEN 'Receipt' WHEN 6 THEN 'Return' WHEN 7 THEN 'Assembly' WHEN 8 THEN
'In-Transit' ELSE 'NA' END AS [In Transaction Type], A.DATERECD AS [In Date Received], A.ITEMNMBR AS [In Item Number], A.TRXLOCTN AS [In Transaction Location], A.QTYRECVD AS [In Quantity Received],
A.QTYSOLD AS [In Quantity Sold], A.UNITCOST AS [In Unit Cost], A.RCTSEQNM AS [In Receipt Sequence Number], ISNULL(B.ORIGINDOCID, ' ') AS [Out Document Number], ISNULL(B.DOCDATE, ' ')
AS [Out Document Date], ISNULL(B.ITEMNMBR, ' ') AS [Out Item Number], ISNULL(B.TRXLOCTN, ' ') AS [Out Transaction Location], ISNULL(B.QTYSOLD, 0) AS [Out Quantity Sold], ISNULL(B.UNITCOST, 0)
AS [Out Unit Cost], ISNULL(B.SRCRCTSEQNM, ' ') AS [Out Source Receipt Sequence Number], ISNULL(C.SOPTYPE, ' ') AS [SLS SOP Type], ISNULL(C.SOPNUMBE, ' ') AS [SLS SOP Number], ISNULL(C.UNITPRCE,
0) AS [SLS SOP Unit Price], C.LNITMSEQ, dbo.SOP30200.DOCDATE AS SlsDocDate, dbo.SOP30200.GLPOSTDT AS SlsPostDate
FROM dbo.SOP30200 INNER JOIN
(SELECT CASE SOPTYPE WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 5 THEN 'Back Order' WHEN 6 THEN 'Fulfillment Order' ELSE 'NA' END AS SOPTYPE,
SOPNUMBE, ITEMNMBR, LOCNCODE, UNITCOST, UNITPRCE, LNITMSEQ, SOPTYPE AS sopcode
FROM dbo.SOP30300) AS C ON dbo.SOP30200.SOPNUMBE = C.SOPNUMBE AND dbo.SOP30200.SOPTYPE = C.sopcode RIGHT OUTER JOIN
dbo.IV10200 AS A LEFT OUTER JOIN
dbo.IV10201 AS B ON A.ITEMNMBR = B.ITEMNMBR AND A.TRXLOCTN = B.TRXLOCTN AND A.RCTSEQNM = B.SRCRCTSEQNM ON C.SOPNUMBE = B.ORIGINDOCID AND C.ITEMNMBR = B.ITEMNMBR AND
C.LOCNCODE = B.TRXLOCTN
GO
---------------------------------------------------
--This is the final view with all info combined
/****** Object: View [dbo].[BI_POP_RctvsInvvsSOP] Script Date: 06/09/2018 8:05:29 PM ******/
CREATE VIEW [dbo].[BI_POP_RctvsInvvsSOP]
AS
SELECT dbo.BI_POP_RctvsSOP.[Cost Layer Status], dbo.BI_POP_RctvsSOP.[In Transaction Type], dbo.BI_POP_RctvsSOP.[In Date Received], dbo.BI_POP_RctvsSOP.[In Item Number],
dbo.BI_POP_RctvsSOP.[In Transaction Location], dbo.BI_POP_RctvsSOP.[In Quantity Received], dbo.BI_POP_RctvsSOP.[In Quantity Sold], dbo.BI_POP_RctvsSOP.[In Receipt Sequence Number],
dbo.BI_POP_RctvsSOP.[Out Document Number], dbo.BI_POP_RctvsSOP.[Out Document Date], dbo.BI_POP_RctvsSOP.[Out Item Number], dbo.BI_POP_RctvsSOP.[Out Transaction Location],
dbo.BI_POP_RctvsSOP.[Out Source Receipt Sequence Number], dbo.BI_POP_RctvsSOP.[SLS SOP Type], dbo.BI_POP_RctvsSOP.[SLS SOP Unit Price], dbo.BI_POP_RctvsSOP.LNITMSEQ,
dbo.BI_INV_HITBTrx.IVOffsetAcc, dbo.BI_INV_HITBTrx.IVOffsetAccDesc, dbo.BI_INV_HITBTrx.IVAcc, dbo.BI_INV_HITBTrx.IVAccDesc, dbo.BI_POP_RctvsSOP.[In Receipt Number],
dbo.BI_POP_RctvsSOP.[In Unit Cost], dbo.BI_POP_RctvsInvUnitCost.InvNum, dbo.BI_POP_RctvsInvUnitCost.InvUnitCost, dbo.BI_POP_RctvsSOP.[SLS SOP Number], dbo.BI_POP_RctvsSOP.[Out Quantity Sold],
dbo.BI_POP_RctvsSOP.[Out Unit Cost], dbo.BI_POP_RctvsSOP.SlsDocDate, dbo.BI_POP_RctvsSOP.SlsPostDate
FROM dbo.BI_POP_RctvsSOP LEFT OUTER JOIN
dbo.BI_POP_RctvsInvUnitCost ON dbo.BI_POP_RctvsSOP.[In Receipt Number] = dbo.BI_POP_RctvsInvUnitCost.RctNum AND
dbo.BI_POP_RctvsSOP.[In Item Number] = dbo.BI_POP_RctvsInvUnitCost.RctItem LEFT OUTER JOIN
dbo.BI_INV_HITBTrx ON dbo.BI_POP_RctvsSOP.[In Item Number] = dbo.BI_INV_HITBTrx.ITEMNMBR AND dbo.BI_POP_RctvsSOP.LNITMSEQ = dbo.BI_INV_HITBTrx.LNITMSEQ AND
dbo.BI_POP_RctvsSOP.[SLS SOP Number] = dbo.BI_INV_HITBTrx.DOCNUMBR
GO
