set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[STPCreateInvoiceDetail] @GoodSysCode int , @Comment nvarchar(1000) , @Quantity real , @Fee money , @Unit nvarchar(1000) , @UnitRate real , @UserPrice money , @SerialNo nvarchar(1000) , @Size int , @Color int , @WareHouse int, @FK_InvoiceSysCode int , @DiscountPercent real, @term nvarchar(1000), @rowNo int , @IgnoreAvailability bit=1 AS --Check good declare @goodUnit1 nvarchar(1000),@GoodUnitRate real, @goodUnit2 nvarchar(1000),@hasSerial bit; SELECT @GoodUnitRate= UnitRate, @goodUnit2=SecUnit, @goodUnit1=FstUnit,@hasSerial=@serialNo FROM StockTbl WHERE (GoodSyscode = @goodsyscode) if @goodunitrate is null begin raiserror (50601,16,1) return end --not found if not (@goodUnit1=@Unit and @UnitRate=1 ) and not (@goodUnit2=@Unit and @UnitRate=@GoodUnitRate ) begin raiserror (50604,16,1) return end --not valid row if not @Color is null if not exists(SELECT GoodSysCode FROM GoodColorTbl WHERE (GoodSysCode = @goodsyscode) AND (SysCode = @color)) begin raiserror (50604,16,1) return end --color not found if not @Size is null if not exists(SELECT GoodSysCode FROM GoodSizeTbl WHERE (GoodSysCode = @goodsyscode) AND (SysCode = @Size)) begin raiserror (50604,16,1) return end --size not found --------------check serial no if @hasSerial=1 and (@SerialNo is null or @SerialNo='') begin raiserror (50602,16,1) return end ------------------------- ------------------- check mojoodi if @IgnoreAvailability=0 begin declare @SqlStr nvarchar(4000),@GoodRemain real set @sqlstr=N'SELECT @remain=Remain FROM VWGoodDetailRemain WHERE (' if @color is null set @sqlstr=@sqlstr + N'color is null ' else set @sqlstr=@sqlstr + N'color =' + cast(@color as nvarchar) set @sqlstr=@sqlstr + N' and ' if @Size is null set @sqlstr=@sqlstr + N'Size is null ' else set @sqlstr=@sqlstr + N'Size =' + cast(@Size as nvarchar) set @sqlstr=@sqlstr + N' and ' if @SerialNo is null set @sqlstr=@sqlstr + N'SerialNo is null ' else set @sqlstr=@sqlstr + N'SerialNo =' + cast(@SerialNo as nvarchar) set @sqlstr=@sqlstr + N' and ' if @WareHouse is null set @sqlstr=@sqlstr + N'WareHouse is null ' else set @sqlstr=@sqlstr + N'WareHouse =' + cast(@WareHouse as nvarchar) set @sqlstr=@sqlstr + N' and GoodSysCode =' + cast(@GoodsysCode as nvarchar) + ')' exec sp_executesql @sqlstr,N'@remain int output',@remain=@GoodRemain output; if @GoodRemain is null set @GoodRemain=0 if @GoodRemain<=@Quantity*@UnitRate begin raiserror (50603,16,1) return end --Quantity exceeded end -------------------- INSERT INTO invoiceDetailTbl (GoodSysCode, Comment, Quantity, Fee, Unit, UnitRate, UserPrice, SerialNo, Size, Color, WareHouse, FK_InvoiceSysCode, DiscountPercent, Term, RowNo) VALUES (@GoodSysCode,@Comment,@Quantity,@Fee,@Unit,@UnitRate,@UserPrice,@SerialNo,@Size,@Color,@WareHouse,@FK_InvoiceSysCode,@DiscountPercent,@term,@rowNo)return @@identity