if total Items remaning are more than or equal to selected quantity in inventory out then insert otherwise show alert message item out of stock
-------store procedure for add inventory in:---------------
ALTER Procedure [dbo].[SpAddInventory_In]
@ID int,
@Item_ID int,
@Quentity int,
@Rate float,
@TotalAmount float,
@Date_of_Order datetime,
@Date_of_Receiving datetime,
@Vendor_ID int,
@Store_ID int,
@Remarks nvarchar(Max),
@CreatedBy int,
@Moved_to_store varchar(50)
AS
BEGIN
Declare @Total_items_remaining int
Declare @item_storeID int
IF((Select COUNT(*) from Inventory_Incoming where ID=@ID)>0)
BEGIN
UPDATE Inventory_Incoming SET
Item_ID=@Item_ID,
Quentity =@Quentity,
Rate =@Rate,
TotalAmount =@TotalAmount,
Date_of_Order =@Date_of_Order,
Date_of_Receiving =@Date_of_Receiving,
Vendor_ID =@Vendor_ID,
Remarks =@Remarks,
CreatedBy =@CreatedBy,
CreatedDate=GETDATE(),
Store_ID =@Store_ID,
Moved_to_store =@Moved_to_store
where ID =@ID
IF(@Moved_to_store='Yes')
Begin
SET @item_storeID = ISNULL((Select ID from Items_Store where Store_ID = @Store_ID AND Item_ID = @Item_ID),0)
SET @Total_items_remaining =ISNULL((select SUM(Total_items_remaining) from Items_Store where Store_ID = @Store_ID AND Item_ID = @Item_ID),0)
IF(@Total_items_remaining>0)
SET @Total_items_remaining = @Total_items_remaining + (@Quentity)
ELSE
SET @Total_items_remaining = @Quentity
IF(@item_storeID>0)
BEGIN
update Items_Store SET Total_items_remaining = @Total_items_remaining
where ID = @item_storeID
END
ELSE
BEGIN
Insert into Items_Store (Store_ID,Item_ID,Total_items_remaining)
Values (@Store_ID,@Item_ID,@Total_items_remaining)
END
END
END
ELSE
BEGIN
INSERT INTO Inventory_Incoming (Item_ID,Quentity,Rate,TotalAmount,Date_of_Order
,Date_of_Receiving,Vendor_ID,Remarks,CreatedBy,CreatedDate,Store_ID,Moved_to_store)
VAlues (@Item_ID,@Quentity,@Rate,@TotalAmount,@Date_of_Order
,@Date_of_Receiving,@Vendor_ID,@Remarks,@CreatedBy,GETDATE(),@Store_ID,@Moved_to_store)
IF(@Moved_to_store='Yes')
Begin
Declare @Incoming_ID int
SET @Incoming_ID = (Select @@IDENTITY)
SET @item_storeID = ISNULL((Select ID from Items_Store where Store_ID = @Store_ID AND Item_ID = @Item_ID),0)
SET @Total_items_remaining =ISNULL((select SUM(Total_items_remaining) from Items_Store where Store_ID = @Store_ID AND Item_ID = @Item_ID),0)
IF(@Total_items_remaining>0)
SET @Total_items_remaining = @Total_items_remaining + (@Quentity)
ELSE
SET @Total_items_remaining = @Quentity
IF(@item_storeID>0)
BEGIN
update Items_Store SET Total_items_remaining = @Total_items_remaining
where ID = @item_storeID
END
ELSE
BEGIN
Insert into Items_Store (Store_ID,Item_ID,Total_items_remaining)
Values (@Store_ID,@Item_ID,@Total_items_remaining)
END
END
END
SELECT ID,(Select Item_name from Product where ID = Item_ID) as item_name,
Quentity, Rate, TotalAmount, CONVERT(varchar, Date_of_Order, 103) AS Date_of_Order, CONVERT(varchar, Date_of_Receiving, 103)
AS Date_of_Receiving,
(Select Vendor_name from Vendors where ID = Vendor_ID) as VendorName,
Remarks, CreatedBy, Bill_num, CreatedDate,(Select Store_location from Stores where ID = Store_ID) as StoreLocation,
Moved_to_store
FROM Inventory_Incoming
END
-------store procedure for add inventory out:---------------
ALTER Procedure [dbo].[SpAddInventory_Out]
@ID int,
@CampusID int,
@Item_ID int,
@Store_ID int,
@Quentity int,
@TotalAmount float,
@Rate float,
@Date_of_Order datetime,
@Date_of_Delivery datetime,
@DeliverBy int,
@ReceivedBy int,
@Remarks nvarchar(Max),
@CreatedBy int
AS
BEGIN
Declare @Total_items_remaining int
IF((Select COUNT(*) from Inventory_Out where ID=@ID)>0)
BEGIN
UPDATE Inventory_Out SET
CampusID=@CampusID,
Item_ID=@Item_ID,
Store_ID=@Store_ID,
Quentity =@Quentity,
TotalAmount =@TotalAmount,
Rate =@Rate,
Date_of_Order =@Date_of_Order,
Date_of_Delivery =@Date_of_Delivery,
DeliverBy =@DeliverBy,
ReceivedBy =@ReceivedBy,
CreatedDate=GETDATE(),
Remarks =@Remarks,
CreatedBy =@CreatedBy
FROM Inventory_Out
where ID =@ID
--Update Inventory here
UPDATE dbo.Items_Store
SET @Total_items_remaining = @Total_items_remaining + @Quentity
WHERE Item_ID = @Item_ID And Store_ID=@Store_ID
END
Else
Begin
INSERT INTO Inventory_Out
(
CampusID,
Item_ID,
Store_ID,
Quentity,
TotalAmount,
Rate,
Date_of_Order,
Date_of_Delivery,
DeliverBy,
ReceivedBy,
CreatedDate,
Remarks,
CreatedBy
)
Values
(
@CampusID,
@Item_ID,
@Store_ID,
@Quentity,
@TotalAmount,
@Rate,
@Date_of_Order,
@Date_of_Delivery,
@DeliverBy,
@ReceivedBy,
GETDATE(),
@Remarks,
@CreatedBy
)
--Update Inventory here
UPDATE dbo.Items_Store
SET @Total_items_remaining = @Total_items_remaining - @Quentity
WHERE Item_ID = @Item_ID And Store_ID=@Store_ID
END
SELECT ID,(Select Item_name from Product where ID = Item_ID) as Item_name,
Quentity, Rate,ReceivedBy,DeliverBy TotalAmount, CONVERT(varchar,
Date_of_Order, 103) AS Date_of_Order, CONVERT(varchar, Date_of_Delivery, 103) AS Date_of_Receiving,
(Select CampusName from Campus where ID = CampusID) as CampusName, Remarks,
CreatedBy,CreatedDate,(Select Store_location from Stores where ID = Store_ID) as StoreLocation
FROM Inventory_Out
END
