Dynamics GP - Manufacturing Views - BOM and Exploded BOM

/****** Object:  View [dbo].[BI_Mfg_BOM]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM]
AS
SELECT        TOP (100) PERCENT dbo.IV00101.ITEMNMBR AS BomNumber, dbo.IV00101.ITEMDESC AS BOMName, IV00101_1.ITEMNMBR AS CmpNumber, IV00101_1.ITEMDESC AS CmpName,
                         dbo.BM010115.QUANTITY_I AS CmpQty, dbo.BM010115.UOFM AS CmpUofM, dbo.BM010415.LOCNCODE, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + dbo.IV00101.ITEMDESC AS BomLookup,
                         dbo.IV00101.ITMCLSCD, dbo.IV40400.ITMCLSDC, dbo.IVR10015.ITEMSTATUS_I, dbo.IVR10015.MAKEBUYCODE_I, dbo.IVR10015.ITEMFULFILLMETHOD,
                         CASE makebuycode_i WHEN 1 THEN 'Make' WHEN 2 THEN 'Buy' WHEN 3 THEN 'Make/Buy' END AS MakeBuy,
                         CASE itemfulfillmethod WHEN 1 THEN 'Make to Stock' WHEN 2 THEN 'Make to Order-Manual' WHEN 3 THEN 'Make to Order-Silent' END AS FulfillMethod, dbo.MN010000.NOTETEXT_I AS BomNote,
                         IV00101_1.CURRCOST AS CmpCurrCost, dbo.BM010415.BOMTYPE_I, CASE itemstatus_i WHEN 1 THEN 'Active' ELSE 'Inactive' END AS ItemEngStatus,
                         CASE WHEN iv00101.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMStatus, dbo.BM010415.EFFECTIVEDATE_I AS BOMInDate, CASE WHEN BM010415.EFFECTIVEDATE_I < getdate()
                         THEN 'Active' WHEN BM010415.EFFECTIVEDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS BOMDateStatus, CASE WHEN iv00101_1.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMCmpStatus,
                         dbo.BM010115.EFFECTIVEINDATE_I AS CmpInDate, dbo.BM010115.EFFECTIVEOUTDATE_I AS CmpOutDate, CASE WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I >= getdate()
                         THEN 'Active' WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS CmpStatus, dbo.BM010115.ALTERNATE_I AS IsAlternate,
                         dbo.BM010115.ALTERNATEPARTFOR_I AS AlternateFor
FROM            dbo.MN010000 RIGHT OUTER JOIN
                         dbo.IVR10015 ON dbo.MN010000.NOTEINDX = dbo.IVR10015.MFGNOTEINDEX_I RIGHT OUTER JOIN
                         dbo.IV00101 LEFT OUTER JOIN
                         dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD RIGHT OUTER JOIN
                         dbo.BM010415 ON dbo.IV00101.ITEMNMBR = dbo.BM010415.ITEMNMBR LEFT OUTER JOIN
                         dbo.IV00101 AS IV00101_1 RIGHT OUTER JOIN
                         dbo.BM010115 ON IV00101_1.ITEMNMBR = dbo.BM010115.CPN_I ON dbo.BM010415.BOMCAT_I = dbo.BM010115.BOMCAT_I AND dbo.BM010415.ITEMNMBR = dbo.BM010115.PPN_I ON
                         dbo.IVR10015.ITEMNMBR = dbo.BM010415.ITEMNMBR
GO
/****** Object:  View [dbo].[BI_Mfg_ActiveBomCmps]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_ActiveBomCmps]
AS
SELECT        BomNumber AS BN, CmpNumber AS CN, ItemEngStatus, BOMStatus, BOMDateStatus, BOMCmpStatus, CmpStatus
FROM            dbo.BI_Mfg_BOM
WHERE        (ItemEngStatus = 'active') AND (BOMStatus = 'active') AND (BOMDateStatus = 'active') AND (BOMCmpStatus = 'active') AND (CmpStatus = 'active')
GO
/****** Object:  View [dbo].[BI_Mfg_BOM2]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM2]
AS
SELECT        dbo.BI_Mfg_BOM.BomNumber, dbo.BI_Mfg_BOM.BOMName, dbo.BI_Mfg_BOM.CmpNumber, dbo.BI_Mfg_BOM.CmpName, dbo.BI_Mfg_BOM.CmpQty, dbo.BI_Mfg_BOM.CmpUofM, dbo.BI_Mfg_BOM.LOCNCODE,
                         dbo.BI_Mfg_BOM.BomLookup, dbo.BI_Mfg_BOM.ITMCLSCD, dbo.BI_Mfg_BOM.ITMCLSDC, dbo.BI_Mfg_BOM.ITEMSTATUS_I, dbo.BI_Mfg_BOM.MAKEBUYCODE_I, dbo.BI_Mfg_BOM.ITEMFULFILLMETHOD,
                         dbo.BI_Mfg_BOM.MakeBuy, dbo.BI_Mfg_BOM.FulfillMethod, dbo.BI_Mfg_BOM.BomNote, dbo.BI_Mfg_BOM.CmpCurrCost, dbo.BI_Mfg_BOM.BOMTYPE_I, dbo.BI_Mfg_BOM.ItemEngStatus,
                         dbo.BI_Mfg_BOM.BOMStatus, dbo.BI_Mfg_BOM.BOMInDate, dbo.BI_Mfg_BOM.BOMDateStatus, dbo.BI_Mfg_BOM.BOMCmpStatus, dbo.BI_Mfg_BOM.CmpInDate, dbo.BI_Mfg_BOM.CmpOutDate,
                         dbo.BI_Mfg_BOM.CmpStatus, dbo.BI_Mfg_BOM.IsAlternate, dbo.BI_Mfg_BOM.AlternateFor, CASE WHEN CN IS NULL THEN 'Inactive' ELSE 'Active' END AS IsActive
FROM            dbo.BI_Mfg_BOM LEFT OUTER JOIN
                         dbo.BI_Mfg_ActiveBomCmps ON dbo.BI_Mfg_BOM.BomNumber = dbo.BI_Mfg_ActiveBomCmps.BN AND dbo.BI_Mfg_BOM.CmpNumber = dbo.BI_Mfg_ActiveBomCmps.CN
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_L1]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_L1]
AS
SELECT        L0.BomNumber AS ParentBomNo, L1.BomNumber AS CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.cmpnumber ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                         THEN l0.cmpqty ELSE L1.CmpQty * L0.CmpQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                         THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
FROM            dbo.BI_Mfg_BOM2 AS L0 LEFT OUTER JOIN
                         dbo.BI_Mfg_BOM2 AS L1 ON L0.CmpNumber = L1.BomNumber
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_L2]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_L2]
AS
SELECT        L0.ParentBomNo, L0.CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.fnum ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                         THEN l0.fqty ELSE L1.CmpQty * L0.fQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                         THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
FROM            dbo.BI_Mfg_BOM_L1 AS L0 LEFT OUTER JOIN
                         dbo.BI_Mfg_BOM2 AS L1 ON L0.FNum = L1.BomNumber
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_L3]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_L3]
AS
SELECT        L0.ParentBomNo, L0.CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.fnum ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                         THEN l0.fqty ELSE L1.CmpQty * L0.fQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                         THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
FROM            dbo.BI_Mfg_BOM_L2 AS L0 LEFT OUTER JOIN
                         dbo.BI_Mfg_BOM2 AS L1 ON L0.FNum = L1.BomNumber
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_Exploded]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_Exploded]
AS
SELECT        dbo.BI_Mfg_BOM_L3.ParentBomNo, dbo.IV00101.ITEMDESC AS ParentBOMDesc, dbo.BI_Mfg_BOM_L3.FNum AS CmpNum, IV00101_1.ITEMDESC AS CmpDesc, dbo.BI_Mfg_BOM_L3.FQty AS CmpTotalQty,
                         dbo.BI_Mfg_BOM_L3.IsActive, dbo.BI_Mfg_BOM_L3.IsAlternate, dbo.BI_Mfg_BOM_L3.AlternateFor, IV00101_1.CURRCOST * dbo.BI_Mfg_BOM_L3.FQty AS CmpExtCost, IV00101_1.CURRCOST AS CmpUnitCost,
                         RTRIM(dbo.BI_Mfg_BOM_L3.ParentBomNo) + ' | ' + RTRIM(dbo.IV00101.ITEMDESC) AS ParentLabel, RTRIM(dbo.BI_Mfg_BOM_L3.FNum) + ' | ' + RTRIM(IV00101_1.ITEMDESC) AS CmpLabel,
                         CASE WHEN rt010001.itemnmbr IS NULL THEN 'No' ELSE 'Yes' END AS HasRouting, dbo.RT010001.ROUTINGNAME_I AS Routing
FROM            dbo.RT010001 RIGHT OUTER JOIN
                         dbo.IV00101 ON dbo.RT010001.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
                         dbo.BI_Mfg_BOM_L3 LEFT OUTER JOIN
                         dbo.IV00101 AS IV00101_1 ON dbo.BI_Mfg_BOM_L3.FNum = IV00101_1.ITEMNMBR ON dbo.IV00101.ITEMNMBR = dbo.BI_Mfg_BOM_L3.ParentBomNo