set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO







create PROCEDURE [dbo].[STPGridList_InvoiceDetailChange]
AS
SELECT     Users_1.UName AS OnChangeUName, ChangeinvoiceDetailTbl.OnChangeSysDate, ChangeinvoiceDetailTbl.OnChangeUserId, 
                      ChangeinvoiceDetailTbl.RowNo, ChangeinvoiceDetailTbl.Term, ChangeinvoiceDetailTbl.GoodSysCode, StockTbl.Title AS GoodTitle, StockTbl.Code, 
                      StockTbl.BarCode, GoodColorTbl.ColorName, WareHouseTbl.WareHouseName, GoodSizeTbl.SizeName, ChangeinvoiceDetailTbl.SysCode, 
                      ChangeinvoiceDetailTbl.DiscountPercent, ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.SerialNo, 
                      ChangeinvoiceDetailTbl.UnitRate, ChangeinvoiceDetailTbl.Unit, ChangeinvoiceDetailTbl.Fee, ChangeinvoiceDetailTbl.Quantity, 
                      ChangeinvoiceDetailTbl.Comment,cast((case when EXISTS (SELECT GoodSysCode FROM invoiceDetailTbl WHERE (GoodSysCode = ChangeinvoiceDetailTbl.GoodSysCode) AND (FK_InvoiceSysCode = ChangeinvoiceDetailTbl.FK_InvoiceSysCode) ) then 0 else 1 end ) as bit) as IsDelete
FROM         GoodColorTbl RIGHT OUTER JOIN
                      ChangeinvoiceDetailTbl LEFT OUTER JOIN
                      Users AS Users_1 ON ChangeinvoiceDetailTbl.OnChangeUserId = Users_1.UID LEFT OUTER JOIN
                      GoodSizeTbl ON ChangeinvoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN
                      WareHouseTbl ON ChangeinvoiceDetailTbl.WareHouse = WareHouseTbl.SysCode ON 
                      GoodColorTbl.SysCode = ChangeinvoiceDetailTbl.Color LEFT OUTER JOIN
                      StockTbl ON ChangeinvoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode
-- where ChangeinvoiceDetailTbl.goodsyscode not in (SELECT GoodSysCode FROM invoiceDetailTbl WHERE (GoodSysCode = ChangeinvoiceDetailTbl.GoodSysCode) AND (FK_InvoiceSysCode = ChangeinvoiceDetailTbl.FK_InvoiceSysCode))
ORDER BY ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.RowNo