/****** 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
Monday, July 31, 2017
Friday, July 28, 2017
Dynamics GP - SOP Open and Hist Sales View 2017
/****** Object: View [dbo].[BI_SOP_Hist] Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Hist]
AS
SELECT TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
dbo.SOP30200.CUSTNMBR AS [Customer Number],
CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice,
CASE WHEN sop30200.soptype = 4 THEN sop30300.MRKDNAMT * - 1 ELSE sop30300.MRKDNAMT END AS MarkdownAmt,
CASE WHEN sop30200.soptype = 4 THEN sop30300.TRDISAMT * - 1 ELSE sop30300.TRDISAMT END AS DiscountAmt, CASE WHEN sop30200.soptype = 4 THEN (sop30300.UNITPRCE * SOP30300.qtytoinv)
* - 1 ELSE (sop30300.UNITPRCE * SOP30300.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop30200.soptype = 4 THEN sop30300.taxamnt * - 1 ELSE sop30300.taxamnt END AS TaxAmt
FROM dbo.RM00101 INNER JOIN
dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_Open] Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Open]
AS
SELECT TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
= 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice,
CASE WHEN sop10100.soptype = 4 THEN sop10200.MRKDNAMT * - 1 ELSE sop10200.MRKDNAMT END AS MarkdownAmt,
CASE WHEN sop10100.soptype = 4 THEN sop10200.TRDISAMT * - 1 ELSE sop10200.TRDISAMT END AS DiscountAmt, CASE WHEN sop10100.soptype = 4 THEN (sop10200.UNITPRCE * SOP10200.qtytoinv)
* - 1 ELSE (sop10200.UNITPRCE * SOP10200.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop10100.soptype = 4 THEN sop10200.taxamnt * - 1 ELSE sop10200.taxamnt END AS TaxAmt
FROM dbo.RM00101 INNER JOIN
dbo.SOP10100 INNER JOIN
dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_ALLSOP] Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_ALLSOP]
AS
SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code], [Customer Name], [Customer Class],
[Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, [Item Number], [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB,
UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth, DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR,
MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, QtytoBackOrder, LNITMSEQ, ReqShipDate, QtyToInvoice, MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM (SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM dbo.BI_SOP_Open
UNION
SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM dbo.BI_SOP_Hist) AS ALLSOP
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Hist]
AS
SELECT TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
dbo.SOP30200.CUSTNMBR AS [Customer Number],
CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice,
CASE WHEN sop30200.soptype = 4 THEN sop30300.MRKDNAMT * - 1 ELSE sop30300.MRKDNAMT END AS MarkdownAmt,
CASE WHEN sop30200.soptype = 4 THEN sop30300.TRDISAMT * - 1 ELSE sop30300.TRDISAMT END AS DiscountAmt, CASE WHEN sop30200.soptype = 4 THEN (sop30300.UNITPRCE * SOP30300.qtytoinv)
* - 1 ELSE (sop30300.UNITPRCE * SOP30300.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop30200.soptype = 4 THEN sop30300.taxamnt * - 1 ELSE sop30300.taxamnt END AS TaxAmt
FROM dbo.RM00101 INNER JOIN
dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_Open] Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Open]
AS
SELECT TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
= 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice,
CASE WHEN sop10100.soptype = 4 THEN sop10200.MRKDNAMT * - 1 ELSE sop10200.MRKDNAMT END AS MarkdownAmt,
CASE WHEN sop10100.soptype = 4 THEN sop10200.TRDISAMT * - 1 ELSE sop10200.TRDISAMT END AS DiscountAmt, CASE WHEN sop10100.soptype = 4 THEN (sop10200.UNITPRCE * SOP10200.qtytoinv)
* - 1 ELSE (sop10200.UNITPRCE * SOP10200.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop10100.soptype = 4 THEN sop10200.taxamnt * - 1 ELSE sop10200.taxamnt END AS TaxAmt
FROM dbo.RM00101 INNER JOIN
dbo.SOP10100 INNER JOIN
dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_ALLSOP] Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_ALLSOP]
AS
SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code], [Customer Name], [Customer Class],
[Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, [Item Number], [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB,
UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth, DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR,
MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, QtytoBackOrder, LNITMSEQ, ReqShipDate, QtyToInvoice, MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM (SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM dbo.BI_SOP_Open
UNION
SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM dbo.BI_SOP_Hist) AS ALLSOP
GO
Dynamics GP - Manufacturing - Order-MOPSOP Link-MO-Received-Invoice View
/****** Object: View [dbo].[BI_Mfg_MOSOLink] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_MOSOLink]
AS
SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QtyProcess AS MOQty, QtyMatch AS QtyAllocatedToSO, InHouseDueDt, PromiseDate
FROM (SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, 0 AS QtyProcess, 0 AS QtyMatch, SOITEMDUEDATE_I AS InHouseDueDt,
SOITEMPROMISEDATE_I AS PromiseDate
FROM dbo.IS030001
UNION
SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QTYProcess, QTYMATCH, SOITEMDUEDATE_I AS InHouseDueDt,
SOITEMPROMISEDATE_I AS PromiseDate
FROM dbo.IS010001) AS MOSO
WHERE (MANUFACTUREORDER_I > '')
GO
/****** Object: View [dbo].[BI_SOP_Hist] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Hist]
AS
SELECT TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
dbo.SOP30200.CUSTNMBR AS [Customer Number],
CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice, dbo.SOP30200.NOTEINDX,
dbo.SOP30200.PCKSLPNO AS PackNo, dbo.SOP30200.PICTICNU AS PickNo
FROM dbo.RM00101 INNER JOIN
dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_Open] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Open]
AS
SELECT TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
= 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice, dbo.SOP10100.NOTEINDX,
dbo.SOP10100.PCKSLPNO AS PackNo, dbo.SOP10100.PICTICNU AS PickNo
FROM dbo.RM00101 INNER JOIN
dbo.SOP10100 INNER JOIN
dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_ALLSOP] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_ALLSOP]
AS
SELECT ALLSOP.DOCID, ALLSOP.ORIGNUMB, ALLSOP.[SOP Number], ALLSOP.[Document Date], ALLSOP.[GL Post Date], ALLSOP.Sales, ALLSOP.Costs, ALLSOP.Qty, ALLSOP.[Customer Number], ALLSOP.[SOP Type],
ALLSOP.[Item Class Description], ALLSOP.[Item Class Code], ALLSOP.[Customer Name], ALLSOP.[Customer Class], ALLSOP.[Base Unit Of Measure], ALLSOP.[Units Sold], ALLSOP.STATE, ALLSOP.PRSTADCD,
ALLSOP.[Salesperson ID], ALLSOP.USCATVLS_1, ALLSOP.USCATVLS_2, ALLSOP.USCATVLS_3, ALLSOP.[Item Number], ALLSOP.[Item Description], ALLSOP.ShipToName, ALLSOP.Category, ALLSOP.UNITCOST,
ALLSOP.CREATDDT, ALLSOP.AB, ALLSOP.UNITPRCE, ALLSOP.[Sales Person Department], ALLSOP.[Sales Person First Name], ALLSOP.[Sales Person Last Name], ALLSOP.DocYear, ALLSOP.DocMonth,
ALLSOP.DocYrMonth, ALLSOP.CusIdName, ALLSOP.ItemIdName, ALLSOP.UOFM, ALLSOP.Div, ALLSOP.Salsterr, ALLSOP.SRNm, ALLSOP.CusClassDiv, ALLSOP.CLASDSCR, ALLSOP.MODIFDT, ALLSOP.ItemAge,
ALLSOP.COUNTRY, ALLSOP.BQty, ALLSOP.NatCusNm, ALLSOP.NatCus, ALLSOP.LocExpCus, ALLSOP.SOPTYPE, ALLSOP.QtytoBackOrder, ALLSOP.LNITMSEQ, ALLSOP.ReqShipDate, ALLSOP.QtyToInvoice,
RTRIM(CAST(dbo.SY03900.TXTFIELD AS varchar)) AS SOPNote, dbo.SOP10106.USRDAT01, dbo.SOP10106.USRDAT02, dbo.SOP10106.USRTAB01, dbo.SOP10106.USRTAB09, dbo.SOP10106.USRTAB03,
dbo.SOP10106.USERDEF1, dbo.SOP10106.USERDEF2, dbo.SOP10106.USRDEF03, dbo.SOP10106.USRDEF04, dbo.SOP10106.USRDEF05, dbo.SOP10106.COMMENT_1, dbo.SOP10106.COMMENT_2,
dbo.SOP10106.COMMENT_3, dbo.SOP10106.COMMENT_4, dbo.SOP10106.CMMTTEXT, ALLSOP.PackNo AS PackingSlip, ALLSOP.PickNo AS PickTicket
FROM (SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
NOTEINDX, PackNo, PickNo
FROM dbo.BI_SOP_Open
UNION
SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
NOTEINDX, PackNo, PickNo
FROM dbo.BI_SOP_Hist) AS ALLSOP LEFT OUTER JOIN
dbo.SOP10106 ON ALLSOP.SOPTYPE = dbo.SOP10106.SOPTYPE AND ALLSOP.[SOP Number] = dbo.SOP10106.SOPNUMBE LEFT OUTER JOIN
dbo.SY03900 ON ALLSOP.NOTEINDX = dbo.SY03900.NOTEINDX
GO
/****** Object: View [dbo].[BI_Mfg_SO] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_SO]
AS
SELECT dbo.BI_SOP_ALLSOP.SRNm, dbo.BI_SOP_ALLSOP.DOCID AS SODocID, dbo.BI_SOP_ALLSOP.ORIGNUMB AS SOOrigNum, dbo.BI_SOP_ALLSOP.[SOP Number] AS SODocNum,
dbo.BI_SOP_ALLSOP.[Document Date] AS SODate, dbo.BI_SOP_ALLSOP.Qty AS SOQty, dbo.BI_SOP_ALLSOP.[Item Number], dbo.BI_SOP_ALLSOP.[Item Description],
dbo.BI_SOP_ALLSOP.SOPTYPE AS SODocType, dbo.BI_SOP_ALLSOP.Sales AS SOSls, dbo.BI_SOP_ALLSOP.Costs AS SOCost, dbo.BI_SOP_ALLSOP.QtytoBackOrder, dbo.BI_SOP_ALLSOP.LNITMSEQ,
dbo.BI_SOP_ALLSOP.ReqShipDate AS SOReqShipDate, dbo.BI_SOP_ALLSOP.[Customer Number] AS SOCusNum, dbo.BI_SOP_ALLSOP.[Customer Name] AS SOCusName, dbo.BI_SOP_ALLSOP.QtyToInvoice,
dbo.BI_SOP_ALLSOP.SOPNote, dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, dbo.BI_SOP_ALLSOP.USERDEF1 AS SOUD1, dbo.BI_SOP_ALLSOP.USERDEF2 AS SOUD2,
dbo.BI_SOP_ALLSOP.USRDEF03 AS SOUD3, dbo.BI_SOP_ALLSOP.CMMTTEXT AS SOCMT
FROM dbo.BI_SOP_ALLSOP INNER JOIN
dbo.BI_Mfg_MOSOLink ON dbo.BI_SOP_ALLSOP.[SOP Number] = dbo.BI_Mfg_MOSOLink.SOPNUMBE
WHERE (dbo.BI_SOP_ALLSOP.SOPTYPE = 2)
GO
/****** Object: View [dbo].[BI_Mfg_SI] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_SI]
AS
SELECT DOCID AS SIDocID, ORIGNUMB AS SIOrigNum, [SOP Number] AS SIDocNum, SOPNote AS SINote, [Document Date] AS SIDocDate, Qty AS SIQty, [Item Number], [Item Description], SOPTYPE AS SIDocType,
Sales AS SISls, Costs AS SICost, LNITMSEQ, USERDEF1 AS SIUD1, USERDEF2 AS SIUD2, USRDEF03 AS SIUD3, CMMTTEXT AS SICMT, PackingSlip, PickTicket, PackingSlip AS DeliveryNote
FROM dbo.BI_SOP_ALLSOP
WHERE (SOPTYPE = 3) AND (Qty <> 0)
GO
/****** Object: View [dbo].[BI_Mfg_MO] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_MO]
AS
SELECT dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.DSCRIPTN, dbo.WO010032.MANUFACTUREORDERST_I AS MOStatus, dbo.WO010032.ITEMNMBR, dbo.WO010032.ARCHIVED_MO_I,
dbo.WO010032.BOMCAT_I, dbo.WO010032.BOMNAME_I, dbo.WO010032.ROUTINGNAME_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.STARTQTY_I, dbo.WO010032.STRTDATE, dbo.WO010032.STARTTIME_I,
dbo.WO010032.ENDDATE, dbo.WO010032.DRAWFROMSITE_I, dbo.WO010032.CHANGEDATE_I, dbo.WO010032.USERID, dbo.WO010032.SCHEDULEMETHOD_I, dbo.WO010032.PROJEMPLOYEEHRSSUM_I,
dbo.WO010032.PROJMACHINEHRSSUM_I, dbo.WO010032.MATPROJCOSTI_1, dbo.WO010032.MATPROJCOSTI_2, dbo.WO010032.MATFIXOHDPROJCOSTI_1, dbo.WO010032.MATFIXOHDPROJCOSTI_2,
dbo.WO010032.MATVAROHDPROJCOST_1, dbo.WO010032.MATVAROHDPROJCOST_2, dbo.WO010032.LABPROJCOSTI_1, dbo.WO010032.LABPROJCOSTI_2, dbo.WO010032.LABFIXOHDPROJCOSTI_1,
dbo.WO010032.LABFIXOHDPROJCOSTI_2, dbo.WO010032.LABVAROHDPROJCOSTI_1, dbo.WO010032.LABVAROHDPROJCOSTI_2, dbo.WO010032.MACHPROJCOSTI_1, dbo.WO010032.MACHPROJCOSTI_2,
dbo.WO010032.MACHFIXOHDPROJCOSTI_1, dbo.WO010032.MACHFIXOHDPROJCOSTI_2, dbo.WO010032.MACHVAROHDPROJCOSTI_1, dbo.WO010032.MACHVAROHDPROJCOSTI_2,
dbo.WO010032.POSTTOSITE_I, dbo.WO010032.LOTNUMBR, dbo.WO010032.SCHEDULINGPREFEREN_I, dbo.WO010032.PLANNAME_I, dbo.WO010032.ACTUALDEMAND_I,
dbo.WO010032.MANUFACTUREORDPRI_I, dbo.WO010032.Partial_Purge_Date, dbo.WO010032.NOTEINDX, dbo.WO010032.OUTSOURCED_I, dbo.WO010032.COMPCALCOPTION,
dbo.WO010032.COMPLETECLOSEDATE, dbo.WO010032.PSTGDATE, dbo.WO010032.PLNNDSPPLID, dbo.WO010032.PICKNUMBER, dbo.WO010032.QUICK_MO_I, dbo.WO010032.ROUTING_REVISION_LEVEL,
dbo.WO010032.BOM_REVISION_LEVEL, dbo.WO010032.DEX_ROW_ID, ISNULL(MORctQty.MOQtyRecvd, 0) AS MOQtyRecvd,
CASE WHEN MANUFACTUREORDERST_I = 1 THEN 'Quote,' WHEN MANUFACTUREORDERST_I = 2 THEN 'Open' WHEN MANUFACTUREORDERST_I = 3 THEN 'Released' WHEN MANUFACTUREORDERST_I = 4 THEN
'Hold' WHEN MANUFACTUREORDERST_I = 5 THEN 'Cancelled' WHEN MANUFACTUREORDERST_I = 6 THEN 'Complete' WHEN MANUFACTUREORDERST_I = 7 THEN 'Partial Receipt' WHEN MANUFACTUREORDERST_I
= 8 THEN 'Closed' ELSE 'Now Being Entered by ' + wo010032.userid END AS MOStatusDesc, dbo.MN010000.NOTETEXT_I AS MONote
FROM dbo.WO010032 LEFT OUTER JOIN
dbo.MN010000 ON dbo.WO010032.NOTEINDX = dbo.MN010000.NOTEINDX LEFT OUTER JOIN
(SELECT MANUFACTUREORDER_I, SUM(QTYRECVD) AS MOQtyRecvd
FROM dbo.MOP1100
GROUP BY MANUFACTUREORDER_I
HAVING (MANUFACTUREORDER_I > '')) AS MORctQty ON dbo.WO010032.MANUFACTUREORDER_I = MORctQty.MANUFACTUREORDER_I
WHERE (dbo.WO010032.MANUFACTUREORDER_I > '')
GO
/****** Object: View [dbo].[BI_Mfg_AllDocs] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_AllDocs]
AS
SELECT TOP (100) PERCENT dbo.BI_Mfg_SO.SODocID, dbo.BI_Mfg_SO.SODocType, dbo.BI_Mfg_SI.SIDocID, dbo.BI_Mfg_SI.SIDocType, dbo.BI_Mfg_SO.SRNm, dbo.BI_Mfg_SO.SODocNum,
dbo.BI_Mfg_SO.SOPNote AS SONote, dbo.BI_Mfg_SO.SOReqShipDate, dbo.BI_Mfg_SO.SOCusNum, dbo.BI_Mfg_SO.SOCusName, dbo.BI_Mfg_SO.SOQty, dbo.BI_Mfg_SO.QtytoBackOrder,
dbo.BI_Mfg_SO.QtyToInvoice, ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') AS MODocNo, dbo.BI_Mfg_SI.SIDocNum, dbo.BI_Mfg_SI.SINote, dbo.BI_Mfg_SI.SIQty,
dbo.BI_Mfg_SO.LNITMSEQ AS SOLine, dbo.BI_Mfg_MOSOLink.QtyAllocatedToSO, dbo.BI_Mfg_MOSOLink.MOQty, dbo.BI_Mfg_MO.MOStatus, dbo.BI_Mfg_MO.MOQtyRecvd, dbo.BI_Mfg_MO.MOStatusDesc,
dbo.BI_Mfg_MO.MONote, dbo.BI_Mfg_MO.ITEMNMBR AS MOItem, dbo.BI_ItemMaster.ITEMDESC AS MOItemDesc, dbo.BI_Mfg_MOSOLink.InHouseDueDt, dbo.BI_Mfg_MOSOLink.PromiseDate,
dbo.BI_Mfg_MO.ENDDATE AS MOEndDate, dbo.BI_Mfg_MO.STRTDATE AS MOStartDate, dbo.BI_Mfg_MOSOLink.MOQty AS MOQtyRem, dbo.BI_Mfg_SI.SIDocDate, CASE WHEN siud1 IS NULL
THEN soud1 ELSE siud1 END AS MainSalesOrd, CASE WHEN siud2 IS NULL THEN soud2 ELSE siud2 END AS OrderedBy, CASE WHEN siud3 IS NULL THEN soud3 ELSE siud3 END AS TypeID,
CASE WHEN sicmt IS NULL THEN socmt ELSE sicmt END AS Comment, dbo.BI_Mfg_SI.PackingSlip, dbo.BI_Mfg_SI.PickTicket, dbo.BI_Mfg_SI.DeliveryNote, dbo.BI_Mfg_SO.SODate,
CASE WHEN SIDOCNUM IS NOT NULL THEN '7 In Dispatch' WHEN DeliveryNote IS NOT NULL THEN '8 Dispatched' ELSE CAST(mostatus AS varchar) + ' MO ' + mostatusdesc END AS ProcessStatus
FROM dbo.BI_Mfg_SI RIGHT OUTER JOIN
dbo.BI_ItemMaster INNER JOIN
dbo.BI_Mfg_MO ON dbo.BI_ItemMaster.ITEMNMBR = dbo.BI_Mfg_MO.ITEMNMBR RIGHT OUTER JOIN
dbo.BI_Mfg_SO LEFT OUTER JOIN
dbo.BI_Mfg_MOSOLink ON dbo.BI_Mfg_SO.MANUFACTUREORDER_I = dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I AND dbo.BI_Mfg_SO.SODocNum = dbo.BI_Mfg_MOSOLink.SOPNUMBE AND
dbo.BI_Mfg_SO.LNITMSEQ = dbo.BI_Mfg_MOSOLink.LNITMSEQ AND dbo.BI_Mfg_SO.[Item Number] = dbo.BI_Mfg_MOSOLink.ITEMNMBR ON
dbo.BI_Mfg_MO.ITEMNMBR = dbo.BI_Mfg_MOSOLink.ITEMNMBR AND dbo.BI_Mfg_MO.MANUFACTUREORDER_I = dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I ON
dbo.BI_Mfg_SI.SIOrigNum = dbo.BI_Mfg_SO.SODocNum AND dbo.BI_Mfg_SI.[Item Number] = dbo.BI_Mfg_SO.[Item Number] AND dbo.BI_Mfg_SI.LNITMSEQ = dbo.BI_Mfg_SO.LNITMSEQ
WHERE (ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') > '')
GO
/****** Object: View [dbo].[BI_Mfg_Picklist] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_Picklist]
AS
SELECT dbo.PK010033.ITEMNMBR AS RMNumber, dbo.BI_ItemMaster.ITEMDESC AS RMDescription, dbo.PK010033.SUGGESTEDQTY_I AS RMQty, dbo.BI_ItemMaster.BASEUOFM AS UofM, dbo.PK010033.ALTERNATE_I,
dbo.PK010033.QTY_BACKFLUSHED_I AS RMQtyAllocatedtoMO, dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl, dbo.BI_ItemQty_ByLoc.MacoyaQtyAvlbl, dbo.BI_ItemQty_ByLoc.WIPQtyAvlbl,
dbo.BI_ItemQty_ByLoc.TotalOnOrd, dbo.BI_ItemMaster.ITMCLSCD AS ItemClass, dbo.BI_ItemMaster.USCATVLS_6 AS Origin, dbo.BI_Mfg_AllDocs.SODocID, dbo.BI_Mfg_AllDocs.SODocType,
dbo.BI_Mfg_AllDocs.SIDocID, dbo.BI_Mfg_AllDocs.SIDocType, dbo.BI_Mfg_AllDocs.SRNm, dbo.BI_Mfg_AllDocs.SODocNum, dbo.BI_Mfg_AllDocs.SONote, dbo.BI_Mfg_AllDocs.SOReqShipDate,
dbo.BI_Mfg_AllDocs.SOCusNum, dbo.BI_Mfg_AllDocs.SOCusName, dbo.BI_Mfg_AllDocs.SOQty, dbo.BI_Mfg_AllDocs.QtytoBackOrder, dbo.BI_Mfg_AllDocs.QtyToInvoice, RTRIM(dbo.BI_Mfg_AllDocs.MODocNo)
AS MODocNo, dbo.BI_Mfg_AllDocs.SIDocNum, dbo.BI_Mfg_AllDocs.SINote, dbo.BI_Mfg_AllDocs.SIQty, dbo.BI_Mfg_AllDocs.SOLine, dbo.BI_Mfg_AllDocs.QtyAllocatedToSO, dbo.BI_Mfg_AllDocs.MOQty,
dbo.BI_Mfg_AllDocs.MOStatus, dbo.BI_Mfg_AllDocs.MOQtyRecvd, dbo.BI_Mfg_AllDocs.MOStatusDesc, dbo.BI_Mfg_AllDocs.MONote, dbo.BI_Mfg_AllDocs.MOItem, dbo.BI_Mfg_AllDocs.MOItemDesc,
CASE WHEN dbo.PK010033.SUGGESTEDQTY_I - dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl - dbo.BI_ItemQty_ByLoc.TotalOnOrd > 0 THEN dbo.PK010033.SUGGESTEDQTY_I - dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl - dbo.BI_ItemQty_ByLoc.TotalOnOrd
ELSE 0 END AS SuggestedPurchaseQty, dbo.BI_Mfg_AllDocs.InHouseDueDt, dbo.BI_Mfg_AllDocs.PromiseDate, dbo.BI_Mfg_AllDocs.MOStartDate, dbo.BI_Mfg_AllDocs.MOEndDate,
dbo.BI_Mfg_AllDocs.MOQtyRem, CASE WHEN dbo.PK010033.SUGGESTEDQTY_I > dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl THEN 'Insufficient Raw Materials' ELSE '' END AS RMCheck,
RTRIM(dbo.PK010033.ITEMNMBR) + ' | ' + RTRIM(dbo.BI_ItemMaster.ITEMDESC) AS RMItemLbl, dbo.BI_Mfg_AllDocs.MODocNo AS RlMODocNo
FROM dbo.PK010033 LEFT OUTER JOIN
dbo.BI_Mfg_AllDocs ON dbo.PK010033.MANUFACTUREORDER_I = dbo.BI_Mfg_AllDocs.MODocNo LEFT OUTER JOIN
dbo.BI_ItemQty_ByLoc ON dbo.PK010033.ITEMNMBR = dbo.BI_ItemQty_ByLoc.ITEMNMBR LEFT OUTER JOIN
dbo.BI_ItemMaster ON dbo.PK010033.ITEMNMBR = dbo.BI_ItemMaster.ITEMNMBR
GO
/****** Object: View [dbo].[BI_Mfg_WorkOrder] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_WorkOrder]
AS
SELECT Origin, MOStatus, MOStatusDesc, MONote, RMNumber, RMDescription, RMQty, UofM, SODocNum, SONote, SOCusNum, SOCusName, SRNm, MODocNo, MOQtyRem, RMCheck, PromiseDate, MOStartDate,
MOEndDate, MOItem, MOItemDesc
FROM dbo.BI_Mfg_Picklist
WHERE (MOStatus IN (3, 7))
GO
/****** Object: View [dbo].[BI_Mfg_MORcts] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_MORcts]
AS
SELECT dbo.MOP1100.MANUFACTUREORDER_I AS MONum, dbo.WO010032.DSCRIPTN, dbo.MOP1100.MOPRCTNM, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.MOP1100.DATERECD,
dbo.MOP1100.QTYRECVD, dbo.MOP1100.TO_SITE_I, dbo.MOP1100.USERID, dbo.MOP1100.RECEIPTCOSTSARRAY_10 AS BackflushCost, dbo.MOP1100.POSTED
FROM dbo.IV00101 RIGHT OUTER JOIN
dbo.WO010032 ON dbo.IV00101.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
dbo.MOP1100 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.MOP1100.MANUFACTUREORDER_I
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_MOSOLink]
AS
SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QtyProcess AS MOQty, QtyMatch AS QtyAllocatedToSO, InHouseDueDt, PromiseDate
FROM (SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, 0 AS QtyProcess, 0 AS QtyMatch, SOITEMDUEDATE_I AS InHouseDueDt,
SOITEMPROMISEDATE_I AS PromiseDate
FROM dbo.IS030001
UNION
SELECT SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QTYProcess, QTYMATCH, SOITEMDUEDATE_I AS InHouseDueDt,
SOITEMPROMISEDATE_I AS PromiseDate
FROM dbo.IS010001) AS MOSO
WHERE (MANUFACTUREORDER_I > '')
GO
/****** Object: View [dbo].[BI_SOP_Hist] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Hist]
AS
SELECT TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
dbo.SOP30200.CUSTNMBR AS [Customer Number],
CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice, dbo.SOP30200.NOTEINDX,
dbo.SOP30200.PCKSLPNO AS PackNo, dbo.SOP30200.PICTICNU AS PickNo
FROM dbo.RM00101 INNER JOIN
dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_Open] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Open]
AS
SELECT TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
= 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
+ '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
'(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice, dbo.SOP10100.NOTEINDX,
dbo.SOP10100.PCKSLPNO AS PackNo, dbo.SOP10100.PICTICNU AS PickNo
FROM dbo.RM00101 INNER JOIN
dbo.SOP10100 INNER JOIN
dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC
GO
/****** Object: View [dbo].[BI_SOP_ALLSOP] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_ALLSOP]
AS
SELECT ALLSOP.DOCID, ALLSOP.ORIGNUMB, ALLSOP.[SOP Number], ALLSOP.[Document Date], ALLSOP.[GL Post Date], ALLSOP.Sales, ALLSOP.Costs, ALLSOP.Qty, ALLSOP.[Customer Number], ALLSOP.[SOP Type],
ALLSOP.[Item Class Description], ALLSOP.[Item Class Code], ALLSOP.[Customer Name], ALLSOP.[Customer Class], ALLSOP.[Base Unit Of Measure], ALLSOP.[Units Sold], ALLSOP.STATE, ALLSOP.PRSTADCD,
ALLSOP.[Salesperson ID], ALLSOP.USCATVLS_1, ALLSOP.USCATVLS_2, ALLSOP.USCATVLS_3, ALLSOP.[Item Number], ALLSOP.[Item Description], ALLSOP.ShipToName, ALLSOP.Category, ALLSOP.UNITCOST,
ALLSOP.CREATDDT, ALLSOP.AB, ALLSOP.UNITPRCE, ALLSOP.[Sales Person Department], ALLSOP.[Sales Person First Name], ALLSOP.[Sales Person Last Name], ALLSOP.DocYear, ALLSOP.DocMonth,
ALLSOP.DocYrMonth, ALLSOP.CusIdName, ALLSOP.ItemIdName, ALLSOP.UOFM, ALLSOP.Div, ALLSOP.Salsterr, ALLSOP.SRNm, ALLSOP.CusClassDiv, ALLSOP.CLASDSCR, ALLSOP.MODIFDT, ALLSOP.ItemAge,
ALLSOP.COUNTRY, ALLSOP.BQty, ALLSOP.NatCusNm, ALLSOP.NatCus, ALLSOP.LocExpCus, ALLSOP.SOPTYPE, ALLSOP.QtytoBackOrder, ALLSOP.LNITMSEQ, ALLSOP.ReqShipDate, ALLSOP.QtyToInvoice,
RTRIM(CAST(dbo.SY03900.TXTFIELD AS varchar)) AS SOPNote, dbo.SOP10106.USRDAT01, dbo.SOP10106.USRDAT02, dbo.SOP10106.USRTAB01, dbo.SOP10106.USRTAB09, dbo.SOP10106.USRTAB03,
dbo.SOP10106.USERDEF1, dbo.SOP10106.USERDEF2, dbo.SOP10106.USRDEF03, dbo.SOP10106.USRDEF04, dbo.SOP10106.USRDEF05, dbo.SOP10106.COMMENT_1, dbo.SOP10106.COMMENT_2,
dbo.SOP10106.COMMENT_3, dbo.SOP10106.COMMENT_4, dbo.SOP10106.CMMTTEXT, ALLSOP.PackNo AS PackingSlip, ALLSOP.PickNo AS PickTicket
FROM (SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
NOTEINDX, PackNo, PickNo
FROM dbo.BI_SOP_Open
UNION
SELECT DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
[Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
[Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
NOTEINDX, PackNo, PickNo
FROM dbo.BI_SOP_Hist) AS ALLSOP LEFT OUTER JOIN
dbo.SOP10106 ON ALLSOP.SOPTYPE = dbo.SOP10106.SOPTYPE AND ALLSOP.[SOP Number] = dbo.SOP10106.SOPNUMBE LEFT OUTER JOIN
dbo.SY03900 ON ALLSOP.NOTEINDX = dbo.SY03900.NOTEINDX
GO
/****** Object: View [dbo].[BI_Mfg_SO] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_SO]
AS
SELECT dbo.BI_SOP_ALLSOP.SRNm, dbo.BI_SOP_ALLSOP.DOCID AS SODocID, dbo.BI_SOP_ALLSOP.ORIGNUMB AS SOOrigNum, dbo.BI_SOP_ALLSOP.[SOP Number] AS SODocNum,
dbo.BI_SOP_ALLSOP.[Document Date] AS SODate, dbo.BI_SOP_ALLSOP.Qty AS SOQty, dbo.BI_SOP_ALLSOP.[Item Number], dbo.BI_SOP_ALLSOP.[Item Description],
dbo.BI_SOP_ALLSOP.SOPTYPE AS SODocType, dbo.BI_SOP_ALLSOP.Sales AS SOSls, dbo.BI_SOP_ALLSOP.Costs AS SOCost, dbo.BI_SOP_ALLSOP.QtytoBackOrder, dbo.BI_SOP_ALLSOP.LNITMSEQ,
dbo.BI_SOP_ALLSOP.ReqShipDate AS SOReqShipDate, dbo.BI_SOP_ALLSOP.[Customer Number] AS SOCusNum, dbo.BI_SOP_ALLSOP.[Customer Name] AS SOCusName, dbo.BI_SOP_ALLSOP.QtyToInvoice,
dbo.BI_SOP_ALLSOP.SOPNote, dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, dbo.BI_SOP_ALLSOP.USERDEF1 AS SOUD1, dbo.BI_SOP_ALLSOP.USERDEF2 AS SOUD2,
dbo.BI_SOP_ALLSOP.USRDEF03 AS SOUD3, dbo.BI_SOP_ALLSOP.CMMTTEXT AS SOCMT
FROM dbo.BI_SOP_ALLSOP INNER JOIN
dbo.BI_Mfg_MOSOLink ON dbo.BI_SOP_ALLSOP.[SOP Number] = dbo.BI_Mfg_MOSOLink.SOPNUMBE
WHERE (dbo.BI_SOP_ALLSOP.SOPTYPE = 2)
GO
/****** Object: View [dbo].[BI_Mfg_SI] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_SI]
AS
SELECT DOCID AS SIDocID, ORIGNUMB AS SIOrigNum, [SOP Number] AS SIDocNum, SOPNote AS SINote, [Document Date] AS SIDocDate, Qty AS SIQty, [Item Number], [Item Description], SOPTYPE AS SIDocType,
Sales AS SISls, Costs AS SICost, LNITMSEQ, USERDEF1 AS SIUD1, USERDEF2 AS SIUD2, USRDEF03 AS SIUD3, CMMTTEXT AS SICMT, PackingSlip, PickTicket, PackingSlip AS DeliveryNote
FROM dbo.BI_SOP_ALLSOP
WHERE (SOPTYPE = 3) AND (Qty <> 0)
GO
/****** Object: View [dbo].[BI_Mfg_MO] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_MO]
AS
SELECT dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.DSCRIPTN, dbo.WO010032.MANUFACTUREORDERST_I AS MOStatus, dbo.WO010032.ITEMNMBR, dbo.WO010032.ARCHIVED_MO_I,
dbo.WO010032.BOMCAT_I, dbo.WO010032.BOMNAME_I, dbo.WO010032.ROUTINGNAME_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.STARTQTY_I, dbo.WO010032.STRTDATE, dbo.WO010032.STARTTIME_I,
dbo.WO010032.ENDDATE, dbo.WO010032.DRAWFROMSITE_I, dbo.WO010032.CHANGEDATE_I, dbo.WO010032.USERID, dbo.WO010032.SCHEDULEMETHOD_I, dbo.WO010032.PROJEMPLOYEEHRSSUM_I,
dbo.WO010032.PROJMACHINEHRSSUM_I, dbo.WO010032.MATPROJCOSTI_1, dbo.WO010032.MATPROJCOSTI_2, dbo.WO010032.MATFIXOHDPROJCOSTI_1, dbo.WO010032.MATFIXOHDPROJCOSTI_2,
dbo.WO010032.MATVAROHDPROJCOST_1, dbo.WO010032.MATVAROHDPROJCOST_2, dbo.WO010032.LABPROJCOSTI_1, dbo.WO010032.LABPROJCOSTI_2, dbo.WO010032.LABFIXOHDPROJCOSTI_1,
dbo.WO010032.LABFIXOHDPROJCOSTI_2, dbo.WO010032.LABVAROHDPROJCOSTI_1, dbo.WO010032.LABVAROHDPROJCOSTI_2, dbo.WO010032.MACHPROJCOSTI_1, dbo.WO010032.MACHPROJCOSTI_2,
dbo.WO010032.MACHFIXOHDPROJCOSTI_1, dbo.WO010032.MACHFIXOHDPROJCOSTI_2, dbo.WO010032.MACHVAROHDPROJCOSTI_1, dbo.WO010032.MACHVAROHDPROJCOSTI_2,
dbo.WO010032.POSTTOSITE_I, dbo.WO010032.LOTNUMBR, dbo.WO010032.SCHEDULINGPREFEREN_I, dbo.WO010032.PLANNAME_I, dbo.WO010032.ACTUALDEMAND_I,
dbo.WO010032.MANUFACTUREORDPRI_I, dbo.WO010032.Partial_Purge_Date, dbo.WO010032.NOTEINDX, dbo.WO010032.OUTSOURCED_I, dbo.WO010032.COMPCALCOPTION,
dbo.WO010032.COMPLETECLOSEDATE, dbo.WO010032.PSTGDATE, dbo.WO010032.PLNNDSPPLID, dbo.WO010032.PICKNUMBER, dbo.WO010032.QUICK_MO_I, dbo.WO010032.ROUTING_REVISION_LEVEL,
dbo.WO010032.BOM_REVISION_LEVEL, dbo.WO010032.DEX_ROW_ID, ISNULL(MORctQty.MOQtyRecvd, 0) AS MOQtyRecvd,
CASE WHEN MANUFACTUREORDERST_I = 1 THEN 'Quote,' WHEN MANUFACTUREORDERST_I = 2 THEN 'Open' WHEN MANUFACTUREORDERST_I = 3 THEN 'Released' WHEN MANUFACTUREORDERST_I = 4 THEN
'Hold' WHEN MANUFACTUREORDERST_I = 5 THEN 'Cancelled' WHEN MANUFACTUREORDERST_I = 6 THEN 'Complete' WHEN MANUFACTUREORDERST_I = 7 THEN 'Partial Receipt' WHEN MANUFACTUREORDERST_I
= 8 THEN 'Closed' ELSE 'Now Being Entered by ' + wo010032.userid END AS MOStatusDesc, dbo.MN010000.NOTETEXT_I AS MONote
FROM dbo.WO010032 LEFT OUTER JOIN
dbo.MN010000 ON dbo.WO010032.NOTEINDX = dbo.MN010000.NOTEINDX LEFT OUTER JOIN
(SELECT MANUFACTUREORDER_I, SUM(QTYRECVD) AS MOQtyRecvd
FROM dbo.MOP1100
GROUP BY MANUFACTUREORDER_I
HAVING (MANUFACTUREORDER_I > '')) AS MORctQty ON dbo.WO010032.MANUFACTUREORDER_I = MORctQty.MANUFACTUREORDER_I
WHERE (dbo.WO010032.MANUFACTUREORDER_I > '')
GO
/****** Object: View [dbo].[BI_Mfg_AllDocs] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_AllDocs]
AS
SELECT TOP (100) PERCENT dbo.BI_Mfg_SO.SODocID, dbo.BI_Mfg_SO.SODocType, dbo.BI_Mfg_SI.SIDocID, dbo.BI_Mfg_SI.SIDocType, dbo.BI_Mfg_SO.SRNm, dbo.BI_Mfg_SO.SODocNum,
dbo.BI_Mfg_SO.SOPNote AS SONote, dbo.BI_Mfg_SO.SOReqShipDate, dbo.BI_Mfg_SO.SOCusNum, dbo.BI_Mfg_SO.SOCusName, dbo.BI_Mfg_SO.SOQty, dbo.BI_Mfg_SO.QtytoBackOrder,
dbo.BI_Mfg_SO.QtyToInvoice, ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') AS MODocNo, dbo.BI_Mfg_SI.SIDocNum, dbo.BI_Mfg_SI.SINote, dbo.BI_Mfg_SI.SIQty,
dbo.BI_Mfg_SO.LNITMSEQ AS SOLine, dbo.BI_Mfg_MOSOLink.QtyAllocatedToSO, dbo.BI_Mfg_MOSOLink.MOQty, dbo.BI_Mfg_MO.MOStatus, dbo.BI_Mfg_MO.MOQtyRecvd, dbo.BI_Mfg_MO.MOStatusDesc,
dbo.BI_Mfg_MO.MONote, dbo.BI_Mfg_MO.ITEMNMBR AS MOItem, dbo.BI_ItemMaster.ITEMDESC AS MOItemDesc, dbo.BI_Mfg_MOSOLink.InHouseDueDt, dbo.BI_Mfg_MOSOLink.PromiseDate,
dbo.BI_Mfg_MO.ENDDATE AS MOEndDate, dbo.BI_Mfg_MO.STRTDATE AS MOStartDate, dbo.BI_Mfg_MOSOLink.MOQty AS MOQtyRem, dbo.BI_Mfg_SI.SIDocDate, CASE WHEN siud1 IS NULL
THEN soud1 ELSE siud1 END AS MainSalesOrd, CASE WHEN siud2 IS NULL THEN soud2 ELSE siud2 END AS OrderedBy, CASE WHEN siud3 IS NULL THEN soud3 ELSE siud3 END AS TypeID,
CASE WHEN sicmt IS NULL THEN socmt ELSE sicmt END AS Comment, dbo.BI_Mfg_SI.PackingSlip, dbo.BI_Mfg_SI.PickTicket, dbo.BI_Mfg_SI.DeliveryNote, dbo.BI_Mfg_SO.SODate,
CASE WHEN SIDOCNUM IS NOT NULL THEN '7 In Dispatch' WHEN DeliveryNote IS NOT NULL THEN '8 Dispatched' ELSE CAST(mostatus AS varchar) + ' MO ' + mostatusdesc END AS ProcessStatus
FROM dbo.BI_Mfg_SI RIGHT OUTER JOIN
dbo.BI_ItemMaster INNER JOIN
dbo.BI_Mfg_MO ON dbo.BI_ItemMaster.ITEMNMBR = dbo.BI_Mfg_MO.ITEMNMBR RIGHT OUTER JOIN
dbo.BI_Mfg_SO LEFT OUTER JOIN
dbo.BI_Mfg_MOSOLink ON dbo.BI_Mfg_SO.MANUFACTUREORDER_I = dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I AND dbo.BI_Mfg_SO.SODocNum = dbo.BI_Mfg_MOSOLink.SOPNUMBE AND
dbo.BI_Mfg_SO.LNITMSEQ = dbo.BI_Mfg_MOSOLink.LNITMSEQ AND dbo.BI_Mfg_SO.[Item Number] = dbo.BI_Mfg_MOSOLink.ITEMNMBR ON
dbo.BI_Mfg_MO.ITEMNMBR = dbo.BI_Mfg_MOSOLink.ITEMNMBR AND dbo.BI_Mfg_MO.MANUFACTUREORDER_I = dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I ON
dbo.BI_Mfg_SI.SIOrigNum = dbo.BI_Mfg_SO.SODocNum AND dbo.BI_Mfg_SI.[Item Number] = dbo.BI_Mfg_SO.[Item Number] AND dbo.BI_Mfg_SI.LNITMSEQ = dbo.BI_Mfg_SO.LNITMSEQ
WHERE (ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') > '')
GO
/****** Object: View [dbo].[BI_Mfg_Picklist] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_Picklist]
AS
SELECT dbo.PK010033.ITEMNMBR AS RMNumber, dbo.BI_ItemMaster.ITEMDESC AS RMDescription, dbo.PK010033.SUGGESTEDQTY_I AS RMQty, dbo.BI_ItemMaster.BASEUOFM AS UofM, dbo.PK010033.ALTERNATE_I,
dbo.PK010033.QTY_BACKFLUSHED_I AS RMQtyAllocatedtoMO, dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl, dbo.BI_ItemQty_ByLoc.MacoyaQtyAvlbl, dbo.BI_ItemQty_ByLoc.WIPQtyAvlbl,
dbo.BI_ItemQty_ByLoc.TotalOnOrd, dbo.BI_ItemMaster.ITMCLSCD AS ItemClass, dbo.BI_ItemMaster.USCATVLS_6 AS Origin, dbo.BI_Mfg_AllDocs.SODocID, dbo.BI_Mfg_AllDocs.SODocType,
dbo.BI_Mfg_AllDocs.SIDocID, dbo.BI_Mfg_AllDocs.SIDocType, dbo.BI_Mfg_AllDocs.SRNm, dbo.BI_Mfg_AllDocs.SODocNum, dbo.BI_Mfg_AllDocs.SONote, dbo.BI_Mfg_AllDocs.SOReqShipDate,
dbo.BI_Mfg_AllDocs.SOCusNum, dbo.BI_Mfg_AllDocs.SOCusName, dbo.BI_Mfg_AllDocs.SOQty, dbo.BI_Mfg_AllDocs.QtytoBackOrder, dbo.BI_Mfg_AllDocs.QtyToInvoice, RTRIM(dbo.BI_Mfg_AllDocs.MODocNo)
AS MODocNo, dbo.BI_Mfg_AllDocs.SIDocNum, dbo.BI_Mfg_AllDocs.SINote, dbo.BI_Mfg_AllDocs.SIQty, dbo.BI_Mfg_AllDocs.SOLine, dbo.BI_Mfg_AllDocs.QtyAllocatedToSO, dbo.BI_Mfg_AllDocs.MOQty,
dbo.BI_Mfg_AllDocs.MOStatus, dbo.BI_Mfg_AllDocs.MOQtyRecvd, dbo.BI_Mfg_AllDocs.MOStatusDesc, dbo.BI_Mfg_AllDocs.MONote, dbo.BI_Mfg_AllDocs.MOItem, dbo.BI_Mfg_AllDocs.MOItemDesc,
CASE WHEN dbo.PK010033.SUGGESTEDQTY_I - dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl - dbo.BI_ItemQty_ByLoc.TotalOnOrd > 0 THEN dbo.PK010033.SUGGESTEDQTY_I - dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl - dbo.BI_ItemQty_ByLoc.TotalOnOrd
ELSE 0 END AS SuggestedPurchaseQty, dbo.BI_Mfg_AllDocs.InHouseDueDt, dbo.BI_Mfg_AllDocs.PromiseDate, dbo.BI_Mfg_AllDocs.MOStartDate, dbo.BI_Mfg_AllDocs.MOEndDate,
dbo.BI_Mfg_AllDocs.MOQtyRem, CASE WHEN dbo.PK010033.SUGGESTEDQTY_I > dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl THEN 'Insufficient Raw Materials' ELSE '' END AS RMCheck,
RTRIM(dbo.PK010033.ITEMNMBR) + ' | ' + RTRIM(dbo.BI_ItemMaster.ITEMDESC) AS RMItemLbl, dbo.BI_Mfg_AllDocs.MODocNo AS RlMODocNo
FROM dbo.PK010033 LEFT OUTER JOIN
dbo.BI_Mfg_AllDocs ON dbo.PK010033.MANUFACTUREORDER_I = dbo.BI_Mfg_AllDocs.MODocNo LEFT OUTER JOIN
dbo.BI_ItemQty_ByLoc ON dbo.PK010033.ITEMNMBR = dbo.BI_ItemQty_ByLoc.ITEMNMBR LEFT OUTER JOIN
dbo.BI_ItemMaster ON dbo.PK010033.ITEMNMBR = dbo.BI_ItemMaster.ITEMNMBR
GO
/****** Object: View [dbo].[BI_Mfg_WorkOrder] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_WorkOrder]
AS
SELECT Origin, MOStatus, MOStatusDesc, MONote, RMNumber, RMDescription, RMQty, UofM, SODocNum, SONote, SOCusNum, SOCusName, SRNm, MODocNo, MOQtyRem, RMCheck, PromiseDate, MOStartDate,
MOEndDate, MOItem, MOItemDesc
FROM dbo.BI_Mfg_Picklist
WHERE (MOStatus IN (3, 7))
GO
/****** Object: View [dbo].[BI_Mfg_MORcts] Script Date: 9/21/2017 3:42:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_MORcts]
AS
SELECT dbo.MOP1100.MANUFACTUREORDER_I AS MONum, dbo.WO010032.DSCRIPTN, dbo.MOP1100.MOPRCTNM, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.MOP1100.DATERECD,
dbo.MOP1100.QTYRECVD, dbo.MOP1100.TO_SITE_I, dbo.MOP1100.USERID, dbo.MOP1100.RECEIPTCOSTSARRAY_10 AS BackflushCost, dbo.MOP1100.POSTED
FROM dbo.IV00101 RIGHT OUTER JOIN
dbo.WO010032 ON dbo.IV00101.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
dbo.MOP1100 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.MOP1100.MANUFACTUREORDER_I
GO
Dynamics GP - Print PO Word Template directly to PDF and save with dynamic filename including PO Number, Vendor Name
- On each user's machine, install a pdf printer
- Set pdf printer as default printer
- Set the GP>user preferences to print to printer
- (alternatively, you can setup named printers, and force the PO to the pdf printer)
- Modify the "Purchase Order Print Options" screen to add a field that populates with the required path and filename using vba
- User will select the filename and copy
- You could also have vba copy the path and filename to the clipboard automatically
- User prints to pdf
- When the pdf printer prompts for the filename and location, user will paste filepath and name and save pdf
Thursday, July 27, 2017
SQL 2016 - SSRS - Email subscriptions are not enabled
Run the SSRS Configuration Manager
Set options under Email settings and subscription settings
Set options under Email settings and subscription settings
Wednesday, July 26, 2017
Dynamics GP - PO Receipt Posting hangs, freezes at 100%
Issue:
We were experiencing this problem with a specific number of items. Whenever a purchase receipt was posted for any one of these specific items, the posting would hang at 100%. Upon investigation, it looked as though all posting actually completed in the tables, but the original work document was failing to delete from the work tables.
No error message was being returned to screen
We enabled the DEXSQL Log, and saw this error coming up
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'MOP1041'.*/
Also, when we switched the posting to transaction level posting, and attempted to post, we got the error message
A Get Change operation failed on POP Line Hist
Solution:
Create the MOP1041 object
/****** Object: Table [dbo].[MOP1041] Script Date: 7/26/2017 2:25:58 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[MOP1041](
[MANUFACTUREORDER_I] [char](31) NOT NULL,
[MOPDOCNUM] [char](31) NOT NULL,
[CALLEDBY] [smallint] NOT NULL,
[TO_SITE_I] [char](11) NOT NULL,
[BIN] [char](15) NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[IVDOCNBR] [char](17) NOT NULL,
[QUANTITY] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKMOP1041] PRIMARY KEY CLUSTERED
(
[MANUFACTUREORDER_I] ASC,
[MOPDOCNUM] ASC,
[CALLEDBY] ASC,
[SEQNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
We were experiencing this problem with a specific number of items. Whenever a purchase receipt was posted for any one of these specific items, the posting would hang at 100%. Upon investigation, it looked as though all posting actually completed in the tables, but the original work document was failing to delete from the work tables.
No error message was being returned to screen
We enabled the DEXSQL Log, and saw this error coming up
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'MOP1041'.*/
Also, when we switched the posting to transaction level posting, and attempted to post, we got the error message
A Get Change operation failed on POP Line Hist
Solution:
Create the MOP1041 object
/****** Object: Table [dbo].[MOP1041] Script Date: 7/26/2017 2:25:58 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[MOP1041](
[MANUFACTUREORDER_I] [char](31) NOT NULL,
[MOPDOCNUM] [char](31) NOT NULL,
[CALLEDBY] [smallint] NOT NULL,
[TO_SITE_I] [char](11) NOT NULL,
[BIN] [char](15) NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[IVDOCNBR] [char](17) NOT NULL,
[QUANTITY] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKMOP1041] PRIMARY KEY CLUSTERED
(
[MANUFACTUREORDER_I] ASC,
[MOPDOCNUM] ASC,
[CALLEDBY] ASC,
[SEQNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Thursday, July 20, 2017
Dynamics NAV - ALL GL Transactions for a period
SELECT [CRONUS$G_L Entry].[Entry No_], [CRONUS$G_L Entry].[G_L Account No_], [CRONUS$G_L Account].Name,
[CRONUS$G_L Entry].[Posting Date], [CRONUS$G_L Entry].[Document Type], [CRONUS$G_L Entry].[Document No_],
[CRONUS$G_L Entry].Description, [CRONUS$G_L Entry].[Bal_ Account No_], [CRONUS$G_L Entry].Amount,
[CRONUS$G_L Entry].[Global Dimension 1 Code], [CRONUS$G_L Entry].[Global Dimension 2 Code], [CRONUS$G_L Entry].[User ID],
[CRONUS$G_L Entry].[Transaction No_], [CRONUS$G_L Entry].[Debit Amount], [CRONUS$G_L Entry].[Credit Amount],
[CRONUS$G_L Entry].[Document Date], YEAR([CRONUS$G_L Entry].[Posting Date]) AS PostYr, MONTH([CRONUS$G_L Entry].[Posting Date])
AS PostMth, [CRONUS$G_L Entry].[External Document No_], [CRONUS$G_L Entry].[Source Code]
FROM [CRONUS$G_L Entry] LEFT OUTER JOIN
[CRONUS$G_L Account] ON [CRONUS$G_L Entry].[G_L Account No_] = [CRONUS$G_L Account].No_
WHERE ([CRONUS$G_L Entry].[Posting Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102) AND
[CRONUS$G_L Entry].[Posting Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
[CRONUS$G_L Entry].[Posting Date], [CRONUS$G_L Entry].[Document Type], [CRONUS$G_L Entry].[Document No_],
[CRONUS$G_L Entry].Description, [CRONUS$G_L Entry].[Bal_ Account No_], [CRONUS$G_L Entry].Amount,
[CRONUS$G_L Entry].[Global Dimension 1 Code], [CRONUS$G_L Entry].[Global Dimension 2 Code], [CRONUS$G_L Entry].[User ID],
[CRONUS$G_L Entry].[Transaction No_], [CRONUS$G_L Entry].[Debit Amount], [CRONUS$G_L Entry].[Credit Amount],
[CRONUS$G_L Entry].[Document Date], YEAR([CRONUS$G_L Entry].[Posting Date]) AS PostYr, MONTH([CRONUS$G_L Entry].[Posting Date])
AS PostMth, [CRONUS$G_L Entry].[External Document No_], [CRONUS$G_L Entry].[Source Code]
FROM [CRONUS$G_L Entry] LEFT OUTER JOIN
[CRONUS$G_L Account] ON [CRONUS$G_L Entry].[G_L Account No_] = [CRONUS$G_L Account].No_
WHERE ([CRONUS$G_L Entry].[Posting Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102) AND
[CRONUS$G_L Entry].[Posting Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
Subscribe to:
Posts (Atom)