Dynamics GP - Extended Pricing Views

/****** Object:  View [dbo].[BI_BookSheets]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_BookSheets]
AS
SELECT     dbo.RM00500.PRCSHID, dbo.RM00500.PRODTCOD, dbo.RM00500.LINKCODE, dbo.RM00500.SEQNUMBR, dbo.RM00500.PSSEQNUM, dbo.RM00500.DEX_ROW_ID,
                      dbo.SOP10109.DESCEXPR AS PrcBookDesc, CASE WHEN sop10109.isbase = 1 THEN 'Basebook' ELSE '' END AS BaseBkLbl, dbo.SOP10109.ISBASE
FROM         dbo.RM00500 LEFT OUTER JOIN
                      dbo.SOP10109 ON dbo.RM00500.LINKCODE = dbo.SOP10109.PRCBKID
WHERE     (dbo.RM00500.PRODTCOD = 'P')

GO
/****** Object:  View [dbo].[BI_Items]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Items]
AS
SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.ITMCLSCD, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3,
                      dbo.IV00101.USCATVLS_4, dbo.IV00101.USCATVLS_5, dbo.IV00101.USCATVLS_6, dbo.IV00101.ITEMSHWT / 100 AS Wgt, dbo.IV40201.BASEUOFM,
                      dbo.IV00101.DECPLCUR, dbo.IV00101.DECPLQTY, dbo.IVR10015.ITEMSTATUS_I, dbo.IV00101.ITMTSHID AS ItmTaxSched, dbo.IV00101.ITEMTYPE,
                      CASE WHEN itemstatus_i = 1 THEN 'Item Engineering Active' ELSE 'Item Engrineering INACTIVE' END AS IEStatusLbl,
                      CASE WHEN itemtype = 1 THEN 'Sales Inventory' WHEN itemtype = 2 THEN 'Discontinued' WHEN itemtype = 3 THEN 'Kit' WHEN itemtype = 4 THEN 'Misc Charges' WHEN
                       itemtype = 5 THEN 'Services' WHEN itemtype = 6 THEN 'Flat Fee' END AS ItemTypeLbl
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      dbo.IVR10015 ON dbo.IV00101.ITEMNMBR = dbo.IVR10015.ITEMNMBR

GO
/****** Object:  View [dbo].[BI_ExtPrcGrpDetails]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcGrpDetails]
AS
SELECT     dbo.IV10402.PRCSHID AS PriceSheet, dbo.IV10402.EPITMTYP AS PriceType, dbo.BI_Items.ITEMDESC, dbo.IV10402.UOFM, dbo.IV10402.QTYFROM,
                      dbo.IV10402.QTYTO, dbo.IV10402.PSITMVAL AS Price, dbo.IV10402.EQUOMQTY, dbo.IV10402.QTYBSUOM, dbo.IV10402.SEQNUMBR, dbo.IV10402.DEX_ROW_ID,
                      dbo.IV10401.PRODTCOD, dbo.BI_Items.ITMCLSCD, dbo.BI_Items.USCATVLS_1, dbo.BI_Items.USCATVLS_2, dbo.BI_Items.USCATVLS_3, dbo.BI_Items.USCATVLS_4,
                      dbo.BI_Items.USCATVLS_5, dbo.BI_Items.USCATVLS_6, dbo.BI_Items.Wgt, dbo.BI_Items.BASEUOFM, dbo.BI_Items.DECPLCUR, dbo.BI_Items.DECPLQTY,
                      dbo.BI_Items.ITEMSTATUS_I AS IEItemStatus, dbo.BI_Items.ItmTaxSched, dbo.IV10401.ACTIVE AS PriceIsActive, dbo.IV10401.PROMOTYP, dbo.IV10401.PROMOLVL,
                      dbo.SOP10110.ACTIVE AS PriceShtIsActive, dbo.SOP10110.STRTDATE AS PriceShtStart, dbo.SOP10110.ENDDATE AS PriceShtEnd,
                      dbo.SOP10110.CURNCYID AS PriceShtCurr, dbo.SOP10110.PROMO AS PriceShtShtPromo, dbo.SOP10110.DESCEXPR AS PriceShtDesc,
                      dbo.SOP10110.NTPRONLY AS IsNetPrice, dbo.BI_BookSheets.LINKCODE AS PriceBook, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)
                      AS PrcShtLbl, dbo.BI_Items.ITEMTYPE, dbo.BI_Items.IEStatusLbl, dbo.BI_Items.ItemTypeLbl,
                      CASE WHEN sop10110.active = 1 THEN 'Price Sheet is Active' ELSE 'Price Sheet INACTIVE' END AS PrcShtActiveLbl,
                      CASE WHEN IV10401.active = 1 THEN 'Price Line is Active' ELSE 'Price Line INACTIVE' END AS PrcLnActiveLbl,
                      CASE WHEN Promo = 1 THEN 'Promotional Sheet' ELSE '' END AS PrcShtPromoLbl, dbo.BI_BookSheets.PrcBookDesc, dbo.BI_BookSheets.BaseBkLbl,
                      dbo.BI_BookSheets.ISBASE, dbo.SOP10110.PRCSHID, dbo.BI_Items.ITEMNMBR, dbo.IV10400.PRCGRPID
FROM         dbo.IV10402 INNER JOIN
                      dbo.IV10401 ON dbo.IV10402.PRCSHID = dbo.IV10401.PRCSHID AND dbo.IV10402.ITEMNMBR = dbo.IV10401.ITEMNMBR INNER JOIN
                      dbo.SOP10110 ON dbo.IV10401.PRCSHID = dbo.SOP10110.PRCSHID INNER JOIN
                      dbo.IV10400 ON dbo.IV10401.ITEMNMBR = dbo.IV10400.PRCGRPID INNER JOIN
                      dbo.BI_Items ON dbo.IV10400.ITEMNMBR = dbo.BI_Items.ITEMNMBR LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.SOP10110.PRCSHID = dbo.BI_BookSheets.PRCSHID
WHERE     (dbo.BI_Items.ITEMTYPE <> 2)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails]
AS
SELECT     dbo.IV10402.PRCSHID AS PriceSheet, dbo.IV10402.EPITMTYP AS PriceType, dbo.BI_Items.ITEMDESC, dbo.IV10402.UOFM, dbo.IV10402.QTYFROM,
                      dbo.IV10402.QTYTO, dbo.IV10402.PSITMVAL AS Price, dbo.IV10402.EQUOMQTY, dbo.IV10402.QTYBSUOM, dbo.IV10402.SEQNUMBR, dbo.IV10402.DEX_ROW_ID,
                      dbo.IV10401.PRODTCOD, dbo.BI_Items.ITMCLSCD, dbo.BI_Items.USCATVLS_1, dbo.BI_Items.USCATVLS_2, dbo.BI_Items.USCATVLS_3, dbo.BI_Items.USCATVLS_4,
                      dbo.BI_Items.USCATVLS_5, dbo.BI_Items.USCATVLS_6, dbo.BI_Items.Wgt, dbo.BI_Items.BASEUOFM, dbo.BI_Items.DECPLCUR, dbo.BI_Items.DECPLQTY,
                      dbo.BI_Items.ITEMSTATUS_I AS IEItemStatus, dbo.BI_Items.ItmTaxSched, dbo.IV10401.ACTIVE AS PriceIsActive, dbo.IV10401.PROMOTYP, dbo.IV10401.PROMOLVL,
                      dbo.SOP10110.ACTIVE AS PriceShtIsActive, dbo.SOP10110.STRTDATE AS PriceShtStart, dbo.SOP10110.ENDDATE AS PriceShtEnd,
                      dbo.SOP10110.CURNCYID AS PriceShtCurr, dbo.SOP10110.PROMO AS PriceShtShtPromo, dbo.SOP10110.DESCEXPR AS PriceShtDesc,
                      dbo.SOP10110.NTPRONLY AS IsNetPrice, dbo.BI_BookSheets.LINKCODE AS PriceBook, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)
                      AS PrcShtLbl, dbo.BI_Items.ITEMTYPE, dbo.BI_Items.IEStatusLbl, dbo.BI_Items.ItemTypeLbl,
                      CASE WHEN sop10110.active = 1 THEN 'Price Sheet is Active' ELSE 'Price Sheet INACTIVE' END AS PrcShtActiveLbl,
                      CASE WHEN IV10401.active = 1 THEN 'Price Line is Active' ELSE 'Price Line INACTIVE' END AS PrcLnActiveLbl,
                      CASE WHEN Promo = 1 THEN 'Promotional Sheet' ELSE '' END AS PrcShtPromoLbl, dbo.BI_BookSheets.PrcBookDesc, dbo.BI_BookSheets.BaseBkLbl,
                      dbo.BI_BookSheets.ISBASE, dbo.SOP10110.PRCSHID, dbo.BI_Items.ITEMNMBR, '' AS PrcGrpID
FROM         dbo.IV10402 INNER JOIN
                      dbo.IV10401 ON dbo.IV10402.PRCSHID = dbo.IV10401.PRCSHID AND dbo.IV10402.ITEMNMBR = dbo.IV10401.ITEMNMBR INNER JOIN
                      dbo.BI_Items ON dbo.IV10402.ITEMNMBR = dbo.BI_Items.ITEMNMBR INNER JOIN
                      dbo.SOP10110 ON dbo.IV10401.PRCSHID = dbo.SOP10110.PRCSHID LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.SOP10110.PRCSHID = dbo.BI_BookSheets.PRCSHID
WHERE     (dbo.BI_Items.ITEMTYPE <> 2)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails_All]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails_All]
AS
SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD, USCATVLS_1,
                      USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus, ItmTaxSched, PriceIsActive,
                      PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc, IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE,
                      IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl, ISBASE, PRCSHID, ITEMNMBR, PrcGrpID
FROM         (SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD,
                                              USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus,
                                              ItmTaxSched, PriceIsActive, PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc,
                                              IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE, IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl,
                                              ISBASE, PRCSHID, ITEMNMBR, PrcGrpID
                       FROM          dbo.BI_ExtPrcDetails
                       UNION
                       SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD,
                                             USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus,
                                             ItmTaxSched, PriceIsActive, PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc,
                                             IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE, IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl,
                                             ISBASE, PRCSHID, ITEMNMBR, PRCGRPID
                       FROM         dbo.BI_ExtPrcGrpDetails) AS AllPrc

GO
/****** Object:  View [dbo].[BI_SheetCus]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SheetCus]
AS
SELECT     PRCSHID, PRODTCOD, LINKCODE, SEQNUMBR, PSSEQNUM, DEX_ROW_ID
FROM         dbo.RM00500
WHERE     (PRODTCOD = 'C')

GO
/****** Object:  View [dbo].[BI_PriceAssign]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PriceAssign]
AS
SELECT     dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.BI_SheetCus.PRCSHID AS PrcSht, dbo.BI_BookSheets.PRCSHID AS PrcBook,
                      dbo.SOP10110.STRTDATE AS PrcShtStart, dbo.SOP10110.ENDDATE AS PrcShtEnd, dbo.BI_SheetCus.DEX_ROW_ID, dbo.SOP10110.DESCEXPR AS PrcShtDesc,
                      RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR) AS PrcShtLBl
FROM         dbo.SOP10110 RIGHT OUTER JOIN
                      dbo.BI_SheetCus ON dbo.SOP10110.PRCSHID = dbo.BI_SheetCus.PRCSHID LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.BI_SheetCus.PRCSHID = dbo.BI_BookSheets.PRCSHID LEFT OUTER JOIN
                      dbo.RM00101 ON dbo.BI_SheetCus.LINKCODE = dbo.RM00101.CUSTNMBR
GROUP BY dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.BI_SheetCus.PRCSHID, dbo.BI_BookSheets.PRCSHID, dbo.SOP10110.STRTDATE,
                      dbo.SOP10110.ENDDATE, dbo.BI_SheetCus.DEX_ROW_ID, dbo.SOP10110.DESCEXPR, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails_Bycus]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails_Bycus]
AS
SELECT     dbo.BI_PriceAssign.CUSTNMBR, dbo.BI_PriceAssign.CUSTNAME, dbo.BI_ExtPrcDetails_All.PriceSheet, dbo.BI_ExtPrcDetails_All.PriceType,
                      dbo.BI_ExtPrcDetails_All.ITEMNMBR, dbo.BI_ExtPrcDetails_All.ITEMDESC, dbo.BI_ExtPrcDetails_All.UOFM, dbo.BI_ExtPrcDetails_All.QTYFROM,
                      dbo.BI_ExtPrcDetails_All.QTYTO, dbo.BI_ExtPrcDetails_All.Price, dbo.BI_ExtPrcDetails_All.EQUOMQTY, dbo.BI_ExtPrcDetails_All.QTYBSUOM,
                      dbo.BI_ExtPrcDetails_All.SEQNUMBR, dbo.BI_ExtPrcDetails_All.DEX_ROW_ID, dbo.BI_ExtPrcDetails_All.PRODTCOD, dbo.BI_ExtPrcDetails_All.ITMCLSCD,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_1, dbo.BI_ExtPrcDetails_All.USCATVLS_2, dbo.BI_ExtPrcDetails_All.USCATVLS_3, dbo.BI_ExtPrcDetails_All.USCATVLS_4,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_5, dbo.BI_ExtPrcDetails_All.USCATVLS_6, dbo.BI_ExtPrcDetails_All.Wgt, dbo.BI_ExtPrcDetails_All.BASEUOFM,
                      dbo.BI_ExtPrcDetails_All.DECPLCUR, dbo.BI_ExtPrcDetails_All.DECPLQTY, dbo.BI_ExtPrcDetails_All.IEItemStatus, dbo.BI_ExtPrcDetails_All.ItmTaxSched,
                      dbo.BI_ExtPrcDetails_All.PriceIsActive, dbo.BI_ExtPrcDetails_All.PROMOTYP, dbo.BI_ExtPrcDetails_All.PROMOLVL, dbo.BI_ExtPrcDetails_All.PriceShtIsActive,
                      dbo.BI_ExtPrcDetails_All.PriceShtStart, dbo.BI_ExtPrcDetails_All.PriceShtEnd, dbo.BI_ExtPrcDetails_All.PriceShtCurr, dbo.BI_ExtPrcDetails_All.PriceShtShtPromo,
                      dbo.BI_ExtPrcDetails_All.PriceShtDesc, dbo.BI_ExtPrcDetails_All.IsNetPrice, dbo.BI_ExtPrcDetails_All.PriceBook, dbo.BI_ExtPrcDetails_All.PrcShtLbl,
                      dbo.BI_ExtPrcDetails_All.ITEMTYPE, dbo.BI_ExtPrcDetails_All.IEStatusLbl, dbo.BI_ExtPrcDetails_All.ItemTypeLbl, dbo.BI_ExtPrcDetails_All.PrcShtActiveLbl,
                      dbo.BI_ExtPrcDetails_All.PrcLnActiveLbl, dbo.BI_ExtPrcDetails_All.PrcShtPromoLbl, dbo.BI_ExtPrcDetails_All.PrcBookDesc, dbo.BI_ExtPrcDetails_All.BaseBkLbl,
                      dbo.BI_ExtPrcDetails_All.ISBASE, dbo.BI_ExtPrcDetails_All.PRCSHID, dbo.BI_ExtPrcDetails_All.PrcGrpID
FROM         dbo.BI_ExtPrcDetails_All INNER JOIN
                      dbo.BI_PriceAssign ON dbo.BI_ExtPrcDetails_All.PriceSheet = dbo.BI_PriceAssign.PrcSht
GROUP BY dbo.BI_PriceAssign.CUSTNMBR, dbo.BI_PriceAssign.CUSTNAME, dbo.BI_ExtPrcDetails_All.PriceSheet, dbo.BI_ExtPrcDetails_All.PriceType,
                      dbo.BI_ExtPrcDetails_All.ITEMNMBR, dbo.BI_ExtPrcDetails_All.ITEMDESC, dbo.BI_ExtPrcDetails_All.UOFM, dbo.BI_ExtPrcDetails_All.QTYFROM,
                      dbo.BI_ExtPrcDetails_All.QTYTO, dbo.BI_ExtPrcDetails_All.Price, dbo.BI_ExtPrcDetails_All.EQUOMQTY, dbo.BI_ExtPrcDetails_All.QTYBSUOM,
                      dbo.BI_ExtPrcDetails_All.SEQNUMBR, dbo.BI_ExtPrcDetails_All.DEX_ROW_ID, dbo.BI_ExtPrcDetails_All.PRODTCOD, dbo.BI_ExtPrcDetails_All.ITMCLSCD,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_1, dbo.BI_ExtPrcDetails_All.USCATVLS_2, dbo.BI_ExtPrcDetails_All.USCATVLS_3, dbo.BI_ExtPrcDetails_All.USCATVLS_4,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_5, dbo.BI_ExtPrcDetails_All.USCATVLS_6, dbo.BI_ExtPrcDetails_All.Wgt, dbo.BI_ExtPrcDetails_All.BASEUOFM,
                      dbo.BI_ExtPrcDetails_All.DECPLCUR, dbo.BI_ExtPrcDetails_All.DECPLQTY, dbo.BI_ExtPrcDetails_All.IEItemStatus, dbo.BI_ExtPrcDetails_All.ItmTaxSched,
                      dbo.BI_ExtPrcDetails_All.PriceIsActive, dbo.BI_ExtPrcDetails_All.PROMOTYP, dbo.BI_ExtPrcDetails_All.PROMOLVL, dbo.BI_ExtPrcDetails_All.PriceShtIsActive,
                      dbo.BI_ExtPrcDetails_All.PriceShtStart, dbo.BI_ExtPrcDetails_All.PriceShtEnd, dbo.BI_ExtPrcDetails_All.PriceShtCurr, dbo.BI_ExtPrcDetails_All.PriceShtShtPromo,
                      dbo.BI_ExtPrcDetails_All.PriceShtDesc, dbo.BI_ExtPrcDetails_All.IsNetPrice, dbo.BI_ExtPrcDetails_All.PriceBook, dbo.BI_ExtPrcDetails_All.PrcShtLbl,
                      dbo.BI_ExtPrcDetails_All.ITEMTYPE, dbo.BI_ExtPrcDetails_All.IEStatusLbl, dbo.BI_ExtPrcDetails_All.ItemTypeLbl, dbo.BI_ExtPrcDetails_All.PrcShtActiveLbl,
                      dbo.BI_ExtPrcDetails_All.PrcLnActiveLbl, dbo.BI_ExtPrcDetails_All.PrcShtPromoLbl, dbo.BI_ExtPrcDetails_All.PrcBookDesc, dbo.BI_ExtPrcDetails_All.BaseBkLbl,
                      dbo.BI_ExtPrcDetails_All.ISBASE, dbo.BI_ExtPrcDetails_All.PRCSHID, dbo.BI_ExtPrcDetails_All.PrcGrpID

GO

/****** Object:  View [dbo].[BI_ExtPrc_BasePrices]    Script Date: 03/29/2017 10:20:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ExtPrc_BasePrices]
AS
SELECT     ITEMNMBR, MAX(Price) AS Price, QTYFROM, ISBASE, PriceShtIsActive, PriceShtCurr, MAX(PriceShtStart) AS PriceShtStart, MAX(PriceShtEnd) AS PriceShtEnd,
                      UOFM
FROM         dbo.BI_ExtPrcDetails_All
GROUP BY ITEMNMBR, QTYFROM, ISBASE, PriceShtIsActive, PriceShtCurr, UOFM
HAVING      (QTYFROM <= 1) AND (ISBASE = 1) AND (PriceShtIsActive = 1) AND (PriceShtCurr = 'TTD') AND (MAX(PriceShtStart) <= GETDATE()) AND (MAX(PriceShtEnd)
                      >= GETDATE())

GO

/****** Object:  View [dbo].[BI_ItemMaster]    Script Date: 12/02/2019 09:26:55 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ItemMaster]
AS
SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.UOMSCHDL, dbo.IV40201.BASEUOFM, dbo.IV40201.UMDPQTYS, dbo.IV00101.ITMCLSCD,
                      dbo.IV00101.CURRCOST, dbo.IV00101.ITMTRKOP, dbo.IV00101.LOTTYPE, dbo.IV00101.CREATDDT, dbo.IV00101.MODIFDT, DATEDIFF(d, dbo.IV00101.CREATDDT,
                      GETDATE()) AS AgeDays, dbo.IV40400.ITMCLSDC, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.USCATVLS_4,
                      dbo.IV00101.USCATVLS_5, dbo.IV00101.USCATVLS_6, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + RTRIM(dbo.IV00101.ITEMDESC) AS ItemLbl,
                      LEFT(dbo.IV00101.ITEMNMBR, 1) AS A, CASE WHEN LEFT(dbo.IV00101.ITEMNMBR, 1) = 'a' THEN 'Matouks' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1)
                      = 'b' THEN 'MP' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1) IN ('c', 'r') THEN 'Mabel' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1)
                      = 'e' THEN 'Eve' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1) = 'i' THEN 'National' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1)
                      = 'h' THEN 'Home' ELSE '(NONE)' END AS Brand
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD

GO

/****** Object:  View [dbo].[BI_ItemMasterFG]    Script Date: 12/02/2019 09:26:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ItemMasterFG]
AS
SELECT     ITEMNMBR, ITEMDESC, UOMSCHDL, BASEUOFM, UMDPQTYS, ITMCLSCD, CURRCOST, ITMTRKOP, LOTTYPE, CREATDDT, MODIFDT, AgeDays, ITMCLSDC,
                      USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, ItemLbl, ITEMNMBR AS [Item Number], ItemLbl AS ItemIDName, A, Brand
FROM         dbo.BI_ItemMaster
WHERE     (ITMCLSCD LIKE '%FG%') OR
                      (ITMCLSCD IN ('ADVERTISIN', 'CREDITMEMO'))

GO