CREATE FUNCTION [dbo].[MISPackagesSum] ()  

-- SG 20200505: Do not use provisionrecords
-- SG 20190710: DateStamp not used
-- SG 20190611: Added DateStamp (added to table MISTABLEPackSum)
-- OF 20120706: Ext: New mis attempt rev. 1
-- OF 20120411: Bug: CrossPwln disabled. Not used anymore. Might have returned the same ProductWareLinkNumber multiple times witch is not allowed.
-- OF 20120130: Bug: Misdata was still effected by misdata = 0.
-- OF 20120125: Parameters not needed.
-- OF 20120123: Ext: Only return pwln for range of days.
-- OF 20120123: New - Summarizes packages, from mistable, by their ProductWareLinkNumbers

RETURNS @Result TABLE (
  ProductWareLinkNumber int NOT NULL,
  PackageSum Money NULL,
  PackageVatOfSum Money NULL)
AS 
BEGIN 

Declare @SystemDateStr char(8)

SELECT @SystemDateStr = SYSTEMDATE FROM System WITH (nolock) WHERE NUMBER = 1

INSERT INTO @Result
select 
	ProductWareLinkNumber,
	isnull(SUM(BookedPackage + ActualPackage), 0) as PackageSum, 
	isnull(SUM(BookedVatOfPackage + ActualVatOfPackage), 0) as PackageVatOfSum
from MISTABLE WITH (NOLOCK)
where ProductWareLinkNumber <> -1
and WareType not in (11,12,13,14,15,16,17,18) --Provision
Group by ProductWareLinkNumber --, CASE WHEN Datestamp > @SystemDateStr THEN Datestamp ELSE '19000101' END 
having isnull(SUM(BookedPackage), 0) <> 0 or isnull(SUM(BookedVatOfPackage), 0) <> 0 or isnull(SUM(ActualPackage), 0) <> 0 or isnull(SUM(ActualVatOfPackage), 0) <> 0
order by ProductWareLinkNumber

RETURN
END
