SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BI-BuildItemQty]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[BI-BuildItemQty] AS'
END
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[BI-BuildItemQty]
@FromDate as datetime = NULL,
@ToDate as datetime = NULL
AS
BEGIN
IF @FromDate is null BEGIN SET @FromDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) END
IF @ToDate is null BEGIN SET @ToDate = DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BIT-ItemQty]') AND type in (N'U'))
BEGIN
Drop Table [BIT-ItemQty]
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BIT-ItemQty0]') AND type in (N'U'))
BEGIN
Drop Table [BIT-ItemQty0]
END
--Calc Opening Figures
select * into #BITEMPOpeningItemQty from(
SELECT [Item No_], SUM(Quantity) AS OpeningQty
FROM [BI-ItemQty]
WHERE ([Posting Date] < @FromDate)
GROUP BY [Item No_]
) as TempQty
--Calc Period Figures
select * into #BITEMPItemQty from(
SELECT [Item No_], [Location Code], SUM(Quantity) AS Quantity, SUM(Receipts) AS Receipts, SUM(Sales) AS Sales, SUM(Rtns) AS Rtns, SUM(Adjustments) AS Adjustments, SUM([Assembly])
AS [Assembly]
FROM [BI-ItemQty]
WHERE ([Posting Date] < @ToDate)
GROUP BY [Item No_], [Location Code]
) as TempQty
--Calc Location Column Stock
Declare @Locs varchar(max)
Declare @Locs2 varchar(max)
Declare @Pivot varchar(max)
select @Locs = AllComm from (
SELECT distinct
(STUFF((SELECT distinct CAST(', [' + [Location Code] + ']' AS VARCHAR(MAX))
FROM #BITEMPItemQty
WHERE [Location Code] > ''
FOR XML PATH ('')), 1, 2, '')) AS AllComm
FROM #BITEMPItemQty) as A
select @Locs2 = AllComm from (
SELECT distinct
(STUFF((SELECT distinct CAST(', ISNULL([' + [Location Code] + '],0) ['+[Location Code] + ']' AS VARCHAR(MAX))
FROM #BITEMPItemQty
WHERE [Location Code] > ''
FOR XML PATH ('')), 1, 2, '')) AS AllComm
FROM #BITEMPItemQty) as B
Set @Pivot =
N'
select * into [BIT-ItemQty0] from (
select [Item No_]
,[Receipts]
,[Sales]
,[Rtns]
,[Adjustments]
,[Assembly],'+@Locs2+' from(SELECT *
FROM #BITEMPItemQty) ps
PIVOT
( SUM (Quantity)
FOR [Location Code] in ('+@Locs+')
) AS pvt) as pvt2'
-- print @Pivot
EXECUTE (@Pivot)
--Rejoin to data
select * into [BIT-ItemQty] from (
SELECT [#BITEMPOpeningItemQty].OpeningQty,[BI-ItemQtyLoc].*
FROM [#BITEMPOpeningItemQty] INNER JOIN
[BI-ItemQtyLoc] ON [#BITEMPOpeningItemQty].[Item No_] = [BI-ItemQtyLoc].[Item No_]
) as Joined
Drop Table #BITEMPOpeningItemQty
Drop Table #BITEMPItemQty
select * from [BI-ItemStockAndSale2]
END
GO
/****** Object: View [dbo].[BI-ItemStockAndSale] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemStockAndSale]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemStockAndSale]
AS
SELECT dbo.CRONUSLS13X$Item.Description, dbo.CRONUSLS13X$Item.[Division Code], dbo.CRONUSLS13X$Item.[Item Category Code], dbo.CRONUSLS13X$Item.[Product Group Code], dbo.[BIT-ItemQty].OpeningQty,
dbo.[BIT-ItemQty].[Item No_], dbo.[BIT-ItemQty].Receipts, dbo.[BIT-ItemQty].Sales, dbo.[BIT-ItemQty].Rtns, dbo.[BIT-ItemQty].Adjustments, dbo.[BIT-ItemQty].[Assembly], dbo.[BIT-ItemQty].BLUE, dbo.[BIT-ItemQty].GREEN,
dbo.[BIT-ItemQty].[OUT. LOG.], dbo.[BIT-ItemQty].[OWN LOG.], dbo.[BIT-ItemQty].RED, dbo.[BIT-ItemQty].S0001, dbo.[BIT-ItemQty].S0002, dbo.[BIT-ItemQty].S0003, dbo.[BIT-ItemQty].S0004, dbo.[BIT-ItemQty].S0007,
dbo.[BIT-ItemQty].S0007SPO, dbo.[BIT-ItemQty].S0009, dbo.[BIT-ItemQty].S0009A, dbo.[BIT-ItemQty].S0013, dbo.[BIT-ItemQty].S0015, dbo.[BIT-ItemQty].W0001, dbo.[BIT-ItemQty].W0002, dbo.[BIT-ItemQty].WHITE,
dbo.[BIT-ItemQty].YELLOW, dbo.[BIT-ItemQty].Sales + dbo.[BIT-ItemQty].Rtns + dbo.[BIT-ItemQty].Adjustments AS [Total Usage],
dbo.[BIT-ItemQty].Receipts + dbo.[BIT-ItemQty].Sales + dbo.[BIT-ItemQty].Rtns + dbo.[BIT-ItemQty].Adjustments + dbo.[BIT-ItemQty].[Assembly] AS [Net Change],
dbo.[BIT-ItemQty].Receipts + dbo.[BIT-ItemQty].Sales + dbo.[BIT-ItemQty].Rtns + dbo.[BIT-ItemQty].Adjustments + dbo.[BIT-ItemQty].[Assembly] + dbo.[BIT-ItemQty].OpeningQty AS [Total Balance]
FROM dbo.[BIT-ItemQty] INNER JOIN
dbo.CRONUSLS13X$Item ON dbo.[BIT-ItemQty].[Item No_] = dbo.CRONUSLS13X$Item.No_
'
GO
/****** Object: View [dbo].[BI-ItemStockAndSale2] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemStockAndSale2]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemStockAndSale2]
AS
SELECT Description, [Division Code], [Item Category Code], [Product Group Code], OpeningQty, [Item No_], Receipts, Sales, Rtns, Adjustments, [Assembly], BLUE, GREEN, [OUT. LOG.], [OWN LOG.], RED, S0001, S0002, S0003, S0004,
S0007, S0007SPO, S0009, S0009A, S0013, S0015, W0001, W0002, WHITE, YELLOW, [Total Usage], [Net Change], [Total Balance], OpeningQty + [Net Change] - [Total Balance] AS Variance, Sales * - 1 AS OppSales,
[Total Balance] * - 1 AS OppStock
FROM dbo.[BI-ItemStockAndSale]
'
GO
/****** Object: View [dbo].[BI-CusData0] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-CusData0]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-CusData0]
AS
SELECT CUS.No_, CUS.Name, CUS.[Customer Posting Group], CLE.[Posting Date], CLE.[Salesperson Code], SUM(CLE.[Sales (LCY)]) AS Sales, SUM(CLE.[Profit (LCY)]) AS Profit, CLE.[Open], SUM(DCLE.[Amount (LCY)]) AS OustandingAmt,
CLE.[Document Type], CLE.[Document No_], CUS.[Responsibility Center], DATEDIFF(day, CLE.[Posting Date], GETDATE()) AS DaysOld, YEAR(CLE.[Posting Date]) AS Yr
FROM dbo.[CRONUSLS13X$Cust_ Ledger Entry] AS CLE INNER JOIN
dbo.CRONUSLS13X$Customer AS CUS ON CLE.[Customer No_] = CUS.No_ INNER JOIN
dbo.[CRONUSLS13X$Detailed Cust_ Ledg_ Entry] AS DCLE ON CLE.[Entry No_] = DCLE.[Cust_ Ledger Entry No_]
GROUP BY CUS.No_, CUS.Name, CUS.[Customer Posting Group], CLE.[Posting Date], CLE.[Salesperson Code], CLE.[Open], CLE.[Document Type], CLE.[Document No_], CUS.[Responsibility Center], DATEDIFF(day, CLE.[Posting Date],
GETDATE()), YEAR(CLE.[Posting Date])
'
GO
/****** Object: View [dbo].[BI-CusData] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-CusData]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-CusData]
AS
SELECT No_, Name, [Customer Posting Group], [Posting Date], [Salesperson Code], Sales, Profit, [Open], OustandingAmt, [Document Type], [Document No_], [Responsibility Center], DaysOld,
CASE WHEN DaysOld < 31 THEN '' 0-30'' WHEN DaysOld BETWEEN 31 AND 90 THEN '' 31-90'' WHEN DaysOld BETWEEN 91 AND 2400 THEN '' 91-2400'' WHEN DaysOld BETWEEN 2400 AND
3600 THEN ''2400-3600'' WHEN DaysOld > 3600 THEN ''Over 3600'' END AS Aging, Yr, CASE WHEN DaysOld < 31 THEN ''1'' WHEN DaysOld BETWEEN 31 AND 90 THEN ''2'' WHEN DaysOld BETWEEN 91 AND
2400 THEN ''3'' WHEN DaysOld BETWEEN 2400 AND 3600 THEN ''4'' WHEN DaysOld > 3600 THEN ''5'' END AS AgingSort
FROM dbo.[BI-CusData0]
'
GO
/****** Object: View [dbo].[BI-VendData0] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-VendData0]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-VendData0]
AS
SELECT VEND.No_, VEND.Name, VEND.[Vendor Posting Group], VLE.[Posting Date], VLE.[User ID], DVLE.[Document Type], DVLE.[Document No_], SUM(DVLE.Amount) AS Amount, SUM(DVLE.[Amount (LCY)]) AS [Amount (LCY)],
DVLE.[Currency Code], DATEDIFF(day, VLE.[Posting Date], GETDATE()) AS DaysOld, YEAR(VLE.[Posting Date]) AS Yr
FROM dbo.[CRONUSLS13X$Vendor Ledger Entry] AS VLE INNER JOIN
dbo.CRONUSLS13X$Vendor AS VEND ON VLE.[Vendor No_] = VEND.No_ INNER JOIN
dbo.[CRONUSLS13X$Detailed Vendor Ledg_ Entry] AS DVLE ON VLE.[Entry No_] = DVLE.[Vendor Ledger Entry No_]
GROUP BY VEND.No_, VEND.Name, VEND.[Vendor Posting Group], VLE.[Posting Date], VLE.[User ID], DVLE.[Document Type], DVLE.[Document No_], DVLE.[Currency Code], DATEDIFF(day, VLE.[Posting Date], GETDATE()),
YEAR(VLE.[Posting Date])
'
GO
/****** Object: View [dbo].[BI-VendData] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-VendData]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-VendData]
AS
SELECT No_, Name, [Vendor Posting Group], [Posting Date], [User ID], [Document Type], [Document No_], Amount, [Amount (LCY)], [Currency Code], DaysOld, CASE WHEN DaysOld < 31 THEN '' 0-30'' WHEN DaysOld BETWEEN 31 AND
90 THEN '' 31-90'' WHEN DaysOld BETWEEN 91 AND 2400 THEN '' 91-2400'' WHEN DaysOld BETWEEN 2400 AND 3600 THEN ''2400-3600'' WHEN DaysOld > 3600 THEN ''Over 3600'' END AS Aging, Yr,
CASE WHEN DaysOld < 31 THEN ''1'' WHEN DaysOld BETWEEN 31 AND 90 THEN ''2'' WHEN DaysOld BETWEEN 91 AND 2400 THEN ''3'' WHEN DaysOld BETWEEN 2400 AND
3600 THEN ''4'' WHEN DaysOld > 3600 THEN ''5'' END AS AgingSort
FROM dbo.[BI-VendData0]
'
GO
/****** Object: View [dbo].[BI-YearFilt] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-YearFilt]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-YearFilt]
AS
SELECT Yr
FROM (SELECT YEAR([Posting Date]) AS Yr
FROM dbo.[BI-CusData]
UNION
SELECT YEAR([Posting Date]) AS Yr
FROM dbo.[BI-VendData]) AS derivedtbl_1
'
GO
/****** Object: View [dbo].[BI-ItemQty0] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemQty0]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemQty0]
AS
SELECT [Item No_], [Posting Date], [Location Code], SUM(Quantity) AS Quantity, [Document Type],
CASE WHEN [Document Type] = 0 THEN '''' WHEN [Document Type] = 1 THEN ''Sales Shipment'' WHEN [Document Type] = 2 THEN ''Sales Invoice'' WHEN [Document Type] = 3 THEN ''Sales Return Receipt'' WHEN [Document Type] =
4 THEN ''Sales Credit Memo'' WHEN [Document Type] = 5 THEN ''Purchase Receipt'' WHEN [Document Type] = 6 THEN ''Purchase Invoice'' WHEN [Document Type] = 7 THEN ''Purchase Return Shipment'' WHEN [Document Type] = 8
THEN ''Purchase Credit Memo'' WHEN [Document Type] = 9 THEN ''Transfer Shipment'' WHEN [Document Type] = 10 THEN ''Transfer Receipt'' WHEN [Document Type] = 11 THEN ''Service Shipment'' WHEN [Document Type] = 12 THEN
''Service Invoice'' WHEN [Document Type] = 13 THEN ''Service Credit Memo'' WHEN [Document Type] = 14 THEN ''Posted Assembly'' END AS DocTypeDesc, [Entry Type],
CASE WHEN [Entry Type] = 0 THEN ''Purchase'' WHEN [Entry Type] = 1 THEN ''Sale'' WHEN [Entry Type] IN (2, 3) THEN ''Adjustment'' ELSE ''Other'' END AS EntryTypeDesc
FROM dbo.[CRONUSLS13X$Item Ledger Entry] AS [32ItemLedgerEntry]
GROUP BY [Item No_], [Posting Date], [Location Code], [Document Type], [Entry Type]
'
GO
/****** Object: View [dbo].[BI-ItemQty] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemQty]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemQty]
AS
SELECT [Item No_], [Posting Date], [Location Code], Quantity, [Document Type], DocTypeDesc, [Entry Type], EntryTypeDesc, CASE WHEN EntryTypeDesc = ''Purchase'' THEN Quantity ELSE 0 END AS Receipts,
CASE WHEN EntryTypeDesc = ''Sale'' AND Quantity < 0 THEN Quantity ELSE 0 END AS Sales, CASE WHEN EntryTypeDesc = ''Sale'' AND Quantity > 0 THEN Quantity ELSE 0 END AS Rtns,
CASE WHEN EntryTypeDesc = ''Adjustment'' THEN Quantity ELSE 0 END AS Adjustments, CASE WHEN EntryTypeDesc = ''Other'' THEN Quantity ELSE 0 END AS [Assembly]
FROM dbo.[BI-ItemQty0]
'
GO
/****** Object: View [dbo].[BI-ItemStockAndSaleMonthly] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemStockAndSaleMonthly]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemStockAndSaleMonthly]
AS
SELECT [Item No_], SUM(Receipts) AS Stock, SUM(Sales + Rtns) AS Sales, [Location Code], YEAR([Posting Date]) AS Yr, MONTH([Posting Date]) AS Mth
FROM dbo.[BI-ItemQty]
GROUP BY [Item No_], [Location Code], YEAR([Posting Date]), MONTH([Posting Date])
'
GO
/****** Object: View [dbo].[BI-ItemValue] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemValue]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemValue]
AS
SELECT [Item No_], [Posting Date], SUM([Cost Amount (Actual)]) AS [Cost Amount (Actual)], YEAR([Posting Date]) AS Yr, SUM([Valued Quantity]) AS [Valued Quantity], [Location Code]
FROM dbo.[CRONUSLS13X$Value Entry]
GROUP BY [Item No_], [Posting Date], YEAR([Posting Date]), [Location Code]
'
GO
/****** Object: View [dbo].[BI-ItemValueCurrent] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemValueCurrent]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemValueCurrent]
AS
SELECT [Item No_], SUM([Cost Amount (Actual)]) AS [Cost Amount (Actual)], SUM([Valued Quantity]) AS [Valued Quantity], [Location Code]
FROM dbo.[BI-ItemValue]
GROUP BY [Item No_], [Location Code]
'
GO
/****** Object: View [dbo].[BI-ItemStockAndSaleAvgAnnualSales] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemStockAndSaleAvgAnnualSales]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemStockAndSaleAvgAnnualSales]
AS
SELECT [Item No_], AVG(Sales) AS AvgSales, Yr AS PYr, AVG(Stock) AS AvgStock, Yr + 1 AS Yr
FROM dbo.[BI-ItemStockAndSaleMonthly]
GROUP BY [Item No_], Yr + 1, Yr
'
GO
/****** Object: View [dbo].[BI-ItemStockAndSaleCurrent] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemStockAndSaleCurrent]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemStockAndSaleCurrent]
AS
SELECT [Item No_], SUM(Receipts) AS Stock, SUM(CASE WHEN year([Posting Date]) = Year(getdate()) THEN Sales + Rtns ELSE 0 END) AS Sales, YEAR(GETDATE()) AS Yr
FROM dbo.[BI-ItemQty]
GROUP BY [Item No_]
'
GO
/****** Object: View [dbo].[BI-ItemStockAndSaleStats] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemStockAndSaleStats]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemStockAndSaleStats]
AS
SELECT dbo.CRONUSLS13X$Item.No_, dbo.CRONUSLS13X$Item.Description, dbo.[BI-ItemStockAndSaleCurrent].Stock, dbo.[BI-ItemStockAndSaleCurrent].Sales, dbo.[BI-ItemStockAndSaleCurrent].Yr,
CASE WHEN AvgSales <> 0 THEN dbo.[BI-ItemStockAndSaleCurrent].Stock / dbo.[BI-ItemStockAndSaleAvgAnnualSales].AvgSales ELSE 0 END AS MonthsOnHand, dbo.[BI-ItemStockAndSaleAvgAnnualSales].AvgSales
FROM dbo.[BI-ItemStockAndSaleAvgAnnualSales] INNER JOIN
dbo.[BI-ItemStockAndSaleCurrent] ON dbo.[BI-ItemStockAndSaleAvgAnnualSales].[Item No_] = dbo.[BI-ItemStockAndSaleCurrent].[Item No_] AND
dbo.[BI-ItemStockAndSaleAvgAnnualSales].Yr = dbo.[BI-ItemStockAndSaleCurrent].Yr RIGHT OUTER JOIN
dbo.CRONUSLS13X$Item ON dbo.[BI-ItemStockAndSaleCurrent].[Item No_] = dbo.CRONUSLS13X$Item.No_
'
GO
/****** Object: View [dbo].[BI-Budget] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-Budget]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-Budget]
AS
SELECT YEAR(Date) AS Yr, [Budget Name], SUM([Sales Amount]) AS BudgetAmt, [Item No_]
FROM dbo.[CRONUSLS13X$Item Budget Entry]
GROUP BY YEAR(Date), [Budget Name], [Item No_]
'
GO
/****** Object: View [dbo].[BI-ItemQtyLoc] Script Date: 08-May-20 4:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI-ItemQtyLoc]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BI-ItemQtyLoc]
AS
SELECT [Item No_], SUM(Receipts) AS Receipts, SUM(Sales) AS Sales, SUM(Rtns) AS Rtns, SUM(Adjustments) AS Adjustments, SUM([Assembly]) AS [Assembly], SUM(BLUE) AS BLUE, SUM(GREEN) AS GREEN, SUM([OUT. LOG.])
AS [OUT. LOG.], SUM([OWN LOG.]) AS [OWN LOG.], SUM(RED) AS RED, SUM(S0001) AS S0001, SUM(S0002) AS S0002, SUM(S0003) AS S0003, SUM(S0004) AS S0004, SUM(S0007) AS S0007, SUM(S0007SPO) AS S0007SPO,
SUM(S0009) AS S0009, SUM(S0009A) AS S0009A, SUM(S0013) AS S0013, SUM(S0015) AS S0015, SUM(W0001) AS W0001, SUM(W0002) AS W0002, SUM(WHITE) AS WHITE, SUM(YELLOW) AS YELLOW
FROM dbo.[BIT-ItemQty0]
GROUP BY [Item No_]
'
GO
No comments:
Post a Comment