SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[SP_stockupdateItemTemp]           
@todate datetime,
@BranchName varchar(500)
AS        
BEGIN       
DECLARE @stockstartdate AS datetime
set @stockstartdate=(select isnull(stockdate,'01/01/1900')'date' from stockstartdate)  

DELETE FROM ItemTempDetails
    WHERE branchname = @BranchName;

   
   INSERT INTO ItemTempDetails (itemcode, stock, branchname)
    SELECT 
        a.itemcode,
        SUM(a.qty) AS stock,
        a.branchname
	
FROM 

(

select sum(qty)as qty,itemcode,branchname from (
select 0 as qty,ITEMCODE,@branchname as branchname from item  Group by ItemCode
union all
select isnull(sum(isnull(opstock,0)),0) as qty,ITEMCODE,branchname from OPStockEntryDetails where BranchName=@BranchName Group by ItemCode,branchname

UNION all
  select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE,branchname from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Purchase'  and isnull(deleted,'N')='N' and BranchName=@BranchName  Group by ItemCode,branchname
  UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE,branchname from InvoiceReturnItem where InvoiceReturnItem.entrydate>= @stockstartdate and  entryDate <= @todate and SR='Returns'  and isnull(deleted,'N')='N' and BranchName=@BranchName  Group by ItemCode,branchname
    
	  UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE,branchname from GoodsReceiptItem where GoodsReceiptItem.entrydate>= @stockstartdate and  entryDate <= @todate  and isnull(deleted,'N')='N' and BranchName=@BranchName   Group by ItemCode,branchname

    UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtypunit,1)),0) as qty,ITEMCODE,branchname from MaterialTransApproveDetails where MaterialTransApproveDetails.entrydate>= @stockstartdate and  entryDate <= @todate  and tostore=@BranchName   Group by ItemCode,branchname


	UNION all
	select isnull(sum(isnull(qty,0)*isnull(qtypunit,1)),0) as qty,ITEMCODE,MaterialTransDetails.branchname from MaterialTransDetails   INNER JOIN MaterialTrans ON MaterialTransDetails.DOCUMENTNO=MaterialTrans.VOUCHERNO  WHERE ItemCode NOT IN ('SER','DLC')   and MaterialTransDetails.entrydate>=@stockstartdate and MaterialTransDetails.entrydate<=@todate and MaterialTransDetails.BranchName=@BranchName    Group by ItemCode,MaterialTransDetails.branchname
   UNION all
   select isnull(sum(qty),0) as qty,ITEMCODE,branchname from FinalProduct where FinalProduct.entrydate>= @stockstartdate and  entryDate <= @todate and BranchName=@BranchName  Group by ItemCode,branchname

   UNION all
   select -1*isnull(sum(isnull(qty,0)*isnull(qtypunit,1)),0) as qty,ITEMCODE,branchname from MaterialTransApproveDetails where MaterialTransApproveDetails.entrydate>= @stockstartdate and  entryDate <= @todate  and fromstore=@BranchName   Group by ItemCode,branchname


   UNION all
   select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE,branchname from PurchaseReturnItem where PurchaseReturnItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Returns'  and isnull(deleted,'N')='N' and BranchName=@BranchName Group by ItemCode ,branchname
  UNION all
    select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE,branchname from InvoiceItem where InvoiceItem.entrydate>= @stockstartdate and  entryDate <= @todate and SR='Sales'  and isnull(deleted,'N')='N' and BranchName=@BranchName  Group by ItemCode,branchname
  UNION all
  select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE,branchname from DeliveryNoteItem where DeliveryNoteItem.entrydate>= @stockstartdate and  entryDate <= @todate and  Status=0  and isnull(deleted,'N')='N' and BranchName=@BranchName Group by ItemCode ,branchname
  UNION all
  select -1* isnull(sum(qty),0) as qty,ITEMCODE,branchname from FinalProductItem where FinalProductItem.entrydate>= @stockstartdate and  entryDate <= @todate and BranchName=@BranchName Group by ItemCode  ,branchname
 UNION all
  select -1* isnull(sum(isnull(qty,0)*isnull(qtypunit,1)),0) as qty,ITEMCODE,branchname from damageItem where damageItem.Date>= @stockstartdate and  Date <= @todate and BranchName=@BranchName  Group by ItemCode,branchname
  UNION all
  select -1* isnull(sum(qty),0)as qty,ITEMCODE,MaterialTransOutDetails.branchname from MaterialTransOutDetails  INNER JOIN MaterialTransOut ON MaterialTransOutDetails.INVNO=MaterialTransOut.INVNO 
  WHERE ItemCode NOT IN ('SER','DLC') and MaterialTransOutDetails.entrydate>= @stockstartdate  and MaterialTransOutDetails.entrydate<= @todate  AND MaterialTransOutDetails.fromstore IN (SELECT NAME FROM BRANCHMASTER ) and MaterialTransOutDetails.BranchName=@BranchName
    Group by ItemCode,MaterialTransOutDetails.branchname
   UNION all
   select  isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Qty,ITEMCODE,branchname from QtyAdjustmentDetails Where entryDate <= @todate and entryDate>=@stockstartdate and BranchName=@BranchName  Group by ItemCode,branchname) t 

    

 group by  t.itemcode,t.branchname )a


  group by a.itemcode ,a.branchname


END