NAV\BC Views - Item Ledger Value entries

CREATE VIEW [dbo].[BI-ItemValueEntries]
AS
SELECT        [Entry No_] AS IVEEntryNo, [Item Ledger Entry No_], [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],
                         SUM([Sales Amount (Actual)]) AS [Sales Amount (Actual)]
FROM            dbo.[Fresh Supplier Bonaire$Value Entry] AS [5802ValueEntry]
GROUP BY [Item No_], [Posting Date], YEAR([Posting Date]), [Location Code], [Entry No_], [Item Ledger Entry No_]
GO

CREATE VIEW [dbo].[BI-ItemLedgerEntries]

AS

SELECT        [Entry No_] AS ILEEntryNo, [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, [Document No_], Description, [Document Date], 

                         [External Document No_]

FROM            dbo.[Fresh Supplier Bonaire$Item Ledger Entry] AS [32ItemLedgerEntry]

GROUP BY [Item No_], [Posting Date], [Location Code], [Document Type], [Entry Type], [Entry No_], [Document No_], Description, [Document Date], [External Document No_]

GO

USE [FSB]

GO

/****** Object:  View [dbo].[BI-ItemLedgerValueEntries]    Script Date: 7/8/2020 5:44:58 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[BI-ItemLedgerValueEntries]

AS

SELECT        dbo.[BI-ItemLedgerEntries].ILEEntryNo, SUM(dbo.[BI-ItemValueEntries].[Cost Amount (Actual)]) AS [Cost Amount (Actual)], SUM(dbo.[BI-ItemValueEntries].[Sales Amount (Actual)]) AS [Sales Amount (Actual)], 

                         dbo.[BI-ItemLedgerEntries].[Item No_], dbo.[BI-ItemLedgerEntries].[Posting Date], dbo.[BI-ItemLedgerEntries].[Location Code], dbo.[BI-ItemLedgerEntries].Quantity, dbo.[BI-ItemLedgerEntries].[Document Type], 

                         dbo.[BI-ItemLedgerEntries].DocTypeDesc, dbo.[BI-ItemLedgerEntries].[Entry Type], dbo.[BI-ItemLedgerEntries].EntryTypeDesc, dbo.[BI-ItemLedgerEntries].[Document No_], dbo.[BI-ItemLedgerEntries].Description, 

                         dbo.[BI-ItemLedgerEntries].[Document Date], dbo.[BI-ItemLedgerEntries].[External Document No_], SUM(CASE WHEN EntryTypeDesc = 'Sale' THEN [Sales Amount (Actual)] ELSE [Cost Amount (Actual)] END) AS Amount

FROM            dbo.[BI-ItemLedgerEntries] INNER JOIN

                         dbo.[BI-ItemValueEntries] ON dbo.[BI-ItemLedgerEntries].ILEEntryNo = dbo.[BI-ItemValueEntries].[Item Ledger Entry No_]

GROUP BY dbo.[BI-ItemLedgerEntries].ILEEntryNo, dbo.[BI-ItemLedgerEntries].[Item No_], dbo.[BI-ItemLedgerEntries].[Posting Date], dbo.[BI-ItemLedgerEntries].[Location Code], dbo.[BI-ItemLedgerEntries].Quantity, 

                         dbo.[BI-ItemLedgerEntries].[Document Type], dbo.[BI-ItemLedgerEntries].DocTypeDesc, dbo.[BI-ItemLedgerEntries].[Entry Type], dbo.[BI-ItemLedgerEntries].EntryTypeDesc, dbo.[BI-ItemLedgerEntries].[Document No_], 

                         dbo.[BI-ItemLedgerEntries].Description, dbo.[BI-ItemLedgerEntries].[Document Date], dbo.[BI-ItemLedgerEntries].[External Document No_]

GO