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
No comments:
Post a Comment