Cowmaster's Corner: May 2020

A number of Warehouse Shipment lines could not post because the picking was done for more Quantity or Alt. Qty than existed.
To correct this, the following steps were taken

Attempt to ship and invoice the Warehouse Shipment

Get error message that the system cannot find sufficient stock in the lot

Reverse the pick for that line

Search for Registered Warehouse Pick List
Find your pick>Click on the number to view the line details
Select the line you want to reverse
Click Line>Undo Selected Lines

The system will automatically enter negative lines on the pick to reverse that specific line

Check the actual stock available

Items>Quantity on Hand and Qty on Hand Alt columns
Adjust stock to the correct quantities if necessary (Research the correct reason before adjusting quantities)

Go to Warehouse Physical Inventory Journal
Process>Calculate Inventory
Enter Whse. Document No.
Enter any filters required>Click ok
Edit the Qty. (Phys Inventory) and Qty. (Alt.)(Phys Inventory) fields to the amount of stock you want to have on hand
Process>Register
Go to Item Journals>Action>Function>Calculate Warehouse Adjustment

This will summarize all warehouse journals, and apply costing

Click Action>Posting>Post & Print

This will adjust the stock to the desired quantity
You can confirm by checking the Items>Quantity on Hand and Qty on Hand Alt columns

Create a New Pick

Search for Warehouse Shipments>Click No. to view line details
Click Process>Create Pick

A pick will be generated for any outstanding quantities on the shipment

Search for Warehouse Picks> Click No. to view line details

Take Line>Enter Bin, Qty to Handle, Qty to handle Alt, Lot, Container (if applicable)
Place Line>Enter Lot
Click Process>Register Pick

Post and Print the Warehouse Shipment

Search for Warehouse Shipments>Click No. to view line details
Click Posting>Post and Print

This will consume the stock and generate the invoice

*Related Errors

If the Sales Order, Lot Qty, or Bin qty are in different UofM's, you will run into problems when trying to pick on ADC. Use the Unit Conversion feature on the Bin Status screen to convert units prior to picking.

/****** Object: StoredProcedure [dbo].[BI-BuildItemQty] Script Date: 08-May-20 4:49:08 PM ******/
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

Cowmaster's Corner

Friday, May 29, 2020

SQL 2017 and SSRS

Wednesday, May 27, 2020

Dynamics NAV - Magento - "Could not Save Customer" "Member could not be created"

Friday, May 22, 2020

Dynamics NAV/BC - Columbus Foods - How to fix Incorrect Quantity on Warehouse Shipment

Friday, May 15, 2020

Dynamics NAV - how to schedule a report to run every month

Wednesday, May 13, 2020

Power BI - the profile for the user is a temporary profile

LS Central - How to setup LS Insight

Dynamics NAV/BC - Columbus Foods - Unit Costs

Friday, May 8, 2020

NAV/BC BI Views - Generic

Tuesday, May 5, 2020

Dynamics NAV/BC - Columbus Foods - Batch Reporting Rounding errors