set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FunGoodLastFeeByDate] 
	(
	@Date char(8)='99/99/99'
	)
RETURNS TABLE
AS
	RETURN 
SELECT     derivedtbl_1.GoodSysCode, VWInvoiceDetailComplete.Fee AS LastFee, CASE WHEN NOT FK_AccountSysCode IS NULL 
                      THEN Title ELSE customername END AS LastSeller
FROM         VWInvoiceDetailComplete INNER JOIN
                          (SELECT     GoodSysCode, MAX(SysCode) AS LastSysCode
                             FROM         VWInvoiceDetailComplete AS VWInvoiceDetailComplete_1
                             WHERE     (Type = 2) AND (InvoiceDate <= @Date) AND (NOT (FK_DocsysCode IS NULL)) OR
                                                   (Type = 2) AND (InvoiceDate <= @Date) AND (Amani = 1)
                             GROUP BY GoodSysCode) AS derivedtbl_1 ON VWInvoiceDetailComplete.SysCode = derivedtbl_1.LastSysCode LEFT OUTER JOIN
                      CodeMNGDWNTbl ON VWInvoiceDetailComplete.FK_AccountSysCode = CodeMNGDWNTbl.AccountSysCode

GO

ALTER VIEW [dbo].[VWGoodDetailRemain]
AS
SELECT     dbo.VWInvoiceDetailComplete.GoodSysCode, SUM(dbo.VWInvoiceDetailComplete.NetQuantity) AS Remain, dbo.VWInvoiceDetailComplete.Color, 
                      dbo.VWInvoiceDetailComplete.Size, dbo.VWInvoiceDetailComplete.SerialNo, dbo.VWInvoiceDetailComplete.WareHouse, dbo.StockTbl.Type
FROM         dbo.VWInvoiceDetailComplete INNER JOIN
                      dbo.StockTbl ON dbo.VWInvoiceDetailComplete.GoodSysCode = dbo.StockTbl.GoodSyscode
WHERE     (NOT (dbo.VWInvoiceDetailComplete.FK_DocsysCode IS NULL)) OR
                      (dbo.VWInvoiceDetailComplete.Amani = 1)
GROUP BY dbo.VWInvoiceDetailComplete.GoodSysCode, dbo.VWInvoiceDetailComplete.Color, dbo.VWInvoiceDetailComplete.Size, dbo.VWInvoiceDetailComplete.SerialNo, 
                      dbo.VWInvoiceDetailComplete.WareHouse, dbo.StockTbl.Type
HAVING      (dbo.StockTbl.Type <> 4)
GO
ALTER FUNCTION [dbo].[FunGoodRemainBeforeDate] 
	(
	@Date char(8)='99/99/99'
	)
RETURNS TABLE
AS
	RETURN SELECT     VWInvoiceDetailComplete.GoodSysCode, SUM(VWInvoiceDetailComplete.NetQuantity) AS Remain
FROM         VWInvoiceDetailComplete INNER JOIN
                      StockTbl ON VWInvoiceDetailComplete.GoodSysCode = StockTbl.GoodSyscode
WHERE     (NOT (VWInvoiceDetailComplete.FK_DocsysCode IS NULL)) AND (VWInvoiceDetailComplete.InvoiceDate < @Date) OR
                      (VWInvoiceDetailComplete.InvoiceDate < @Date) AND (VWInvoiceDetailComplete.Amani = 1)
GROUP BY VWInvoiceDetailComplete.GoodSysCode, StockTbl.Type
HAVING      (StockTbl.Type <> 4)
GO
ALTER PROCEDURE [dbo].[STPListStock-simpleRemain] 
AS
SELECT     VWCompleteGood.Title, VWCompleteGood.Code, VWCompleteGood.BarCode, VWCompleteGood.Type, VWCompleteGood.FstUnit, VWCompleteGood.SecUnit, 
                      VWCompleteGood.UnitRate, VWCompleteGood.OrderPoint, VWCompleteGood.SalePrice1, VWCompleteGood.SalePrice2, VWCompleteGood.SalePrice3, 
                      VWCompleteGood.SalePrice4, VWCompleteGood.SalePrice5, VWCompleteGood.VisitorPer, VWCompleteGood.Comment, VWCompleteGood.DiscontPer, 
                      VWCompleteGood.UserPrice, VWCompleteGood.GroupName1, VWCompleteGood.GroupName2, VWCompleteGood.Term, VWCompleteGood.Weight, 
                      VWCompleteGood.SerialNo, VWGoodAvrageFee.AvgFee, VWGoodSimpleLastSummary.LastDate, VWCompleteGood.TypeId, VWCompleteGood.GroupId1, 
                      VWCompleteGood.GroupId2, VWCompleteGood.Weight * FunGoodRemainBeforeDate_1.Remain AS TotalWeight, FunGoodLastFeeByDate_1.LastFee, 
                      FunGoodRemainBeforeDate_1.Remain, FunGoodRemainBeforeDate_1.Remain * FunGoodLastFeeByDate_1.LastFee AS TotalAmount, 
                      FunGoodLastFeeByDate_1.LastSeller, VWCompleteGood.GoodSyscode
FROM         VWCompleteGood LEFT OUTER JOIN
                      dbo.FunGoodLastFeeByDate(DEFAULT) AS FunGoodLastFeeByDate_1 ON 
                      VWCompleteGood.GoodSyscode = FunGoodLastFeeByDate_1.GoodSysCode LEFT OUTER JOIN
                      dbo.FunGoodRemainBeforeDate(DEFAULT) AS FunGoodRemainBeforeDate_1 ON 
                      VWCompleteGood.GoodSyscode = FunGoodRemainBeforeDate_1.GoodSysCode LEFT OUTER JOIN
                      VWGoodSimpleLastSummary ON VWCompleteGood.GoodSyscode = VWGoodSimpleLastSummary.GoodSysCode LEFT OUTER JOIN
                      VWGoodAvrageFee ON VWCompleteGood.GoodSyscode = VWGoodAvrageFee.GoodSysCode