StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhen goods receiving updating stock table
text
Body
copied!<p>I have three tables Good Receive header ,Good Receive Details table and stock Table</p> <pre><code> TB_GoodsReceive_HDR - Header Table ID SupplierID 1 1 TB_GoodsReceive_DTL - Details Table ID GR_HDR_ID ItemID WarehouseID ExpiryDate Qty 1 1 1 1 4/4/2012 20 2 1 2 1 4/4/2012 30 TB_Stock - Stock Table ID ItemID WarehouseID ExpiryDate StockType GR_HDR_ID Qty 1 1 1 4/4/2012 R 1 20 1 2 1 4/4/2012 R 1 30 </code></pre> <p>I have received Item 1 – 20 Qty and Item2 - 30 Qty. Same I have it in my stock table. There are lot of scenarios , But I need guidelines for this scenario</p> <p>Now when user updates the TB_GoodsReceive_DTL as below: Updating the item only(ItemID 1 to ItemID 3)</p> <pre><code> ID GR_HDR_ID ItemID WarehouseID ExpiryDate Qty 1 1 **3** 1 4/4/2012 20 2 1 2 1 4/4/2012 30 My stock table values should be as follows: ID ItemID WarehouseID ExpiryDate StockType GR_HDR_ID Qty 1 **3** 1 4/4/2012 R 1 20 1 2 1 4/4/2012 R 1 30 </code></pre> <p>But my below stored procedure inserts the new row rather updating the row in the stock master. Likewise user might update the warehouse or expiry date</p> <p>The below procedure does the following:</p> <p>Whenever the goods are received it will check the itemid, warehouse and expirydate in the stock table,if exists it will add the QTY else it will insert the new item in stock table.</p> <p>The Problem is whenever the Good Receive table is updated(means when user updates ItemId or Expiry Date or Warehouse) how to update the stock table. Please help me guys, its breaking my head...</p> <pre><code> Declare @ItemID int, @WareHouseID int, @Qty int, @StockType nvarchar(30), @ExpiryDate datetime, @IsExistsItem int, @IsExistsWH int, @IsExistsExpiryDate int, @IsGR_HDR_ID int, @GR_HDR_ID int set @ItemID=1 set @WareHouseID=2 set @Qty=20 set @StockType='R' set @GR_HDR_ID=2 set @ExpiryDate = '4/4/2012 12:00:00 AM' set @IsExistsItem = (select count(ItemID) from TB_Stock_Details where ItemID=@ItemID) set @IsExistsWH = (select count(WareHouseID) from TB_Stock_Details where WareHouseID=@WareHouseID) set @IsExistsExpiryDate = (select count(ExpiryDate) from TB_Stock_Details where ExpiryDate=@ExpiryDate) if(@StockType='R') begin IF (@IsExistsItem>0) and (@IsExistsWH>0) and (@IsExistsExpiryDate>0) BEGIN UPDATE TB_Stock_Details SET Qty =Qty + @Qty WHERE ItemID = @ItemID and GR_HDR_ID = @GR_HDR_ID END ELSE begin INSERT INTO TB_Stock_Details(ItemID,WareHouseID,Qty,StockType,ExpiryDate) VALUES(@ItemID,@WareHouseID,@Qty,@StockType,@ExpiryDate) end end </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload