 
GO

/****** Object:  StoredProcedure [dbo].[PurchaseTransGetandGets]    Script Date: 12/09/2020 19:40:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--PurchaseTransGetandGets 2,0,1,0    
    
--PurchaseTransGetandGets 546,1,6,1    
    
--PurchaseTransGetandGets 1938,1,1,0    
      
ALTER Procedure [dbo].[PurchaseTransGetandGets]        
(        
@ItemId numeric(18,0),      
@DepId numeric(18,0),    
@UserId numeric(18,0),    
@Type  int        
)        
As begin     

declare @Flag as int=0
select @Flag=COUNT(*) from Mst_Salesman where  userid=@UserId

DECLARE @RFLG NUMERIC(18,0)=0;
IF EXISTS(SELECT * FROM Mst_Users WHERE UserMenu LIKE '%'+'M204'+'%' AND UserId=@UserId)
SELECT @RFLG=1;

DECLARE @TBLDUMMYSUP AS TABLE(CustId NUMERIC(18,0),CustName varchar(300),CustAccount numeric(18,0))
INSERT INTO @TBLDUMMYSUP 
SELECT CustId,CASE WHEN @RFLG=0 THEN CustContactName3 ELSE CustName END,CustAccount FROM Mst_CustomerMain WHERE DelFlag=1

   
--For Avoid Jobs in Searching        
if(@ItemId!=0)        
begin      
if(@Type=0)    
begin    
 if exists(select UserMenu from Mst_Users where UserId=@UserId and UserMenu like '%,M205,%')--ALL DEPT TRANSACTION VIEW PERMISSION    
 BEGIN    
  SELECT distinct top 50 SlNo,SlNo as InvoNo,InvoDate,convert(datetime,InvoDate,103) AS InvoiceDate,CustName,CustAccount,Inv_PurchaseMain.Remarks,I.ItemCode,I.[Description],SUM(S.Quantity) AS 'Quantity',    
  FCRate,CurrencyName,inv.OtherCost,S.LocationId,LocationName,S.DeptId,DepartmentName,PO_No,isnull(S.Rate,0)Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no   
  into #A FROM Inv_PurchaseSub as S        
  left join Inv_PurchaseMain on S.InvMainSlno=Inv_PurchaseMain.SlNo and S.DeptId=Inv_PurchaseMain.DepartmentId     
  left join @TBLDUMMYSUP Tblsup on Inv_PurchaseMain.SupplierId=Tblsup.CustId        
  left join Mst_Currency on Inv_PurchaseMain.CurrencyId=Mst_Currency.Id        
  left join Mst_Item as I on S.ItemId=I.ItemId     
  left join Mst_Location on S.LocationId=Mst_Location.LocationId    
  left join Mst_Department  on S.DeptId=Mst_Department.DepartmentId    
  inner join Inv_InventoryTransaction AS INV on S.InvMainSlno=INV.BillNo and S.DeptId=INV.DeptId and S.ItemId=INV.ItemId AND TransType='PI' and s.BatchSlno=inv.BatchSlNo    
  left outer join Inv_InventoryTransaction_Prev AS INVPrev on  S.DeptId=INVPrev.DeptId and S.ItemId=INVPrev.ItemId AND INVPrev.TransType='PI' and s.BatchSlno=INVPrev.BatchSlNo    
  where S.ItemId=@ItemId and Inv_PurchaseMain.DelFlag=1 and S.DelFlag=1     
  GROUP BY FCRate,SlNo,InvoNo,InvoDate,CustName,CurrencyName,inv.OtherCost,CustAccount,Inv_PurchaseMain.Remarks,PO_No,S.LocationId,LocationName,S.DeptId,DepartmentName,I.ItemCode,I.[Description],S.Rate,AvgCost,Inv_PurchaseMain.PurchaseType ,mrv_no   
  ORDER BY InvoiceDate DESC    
    
  update #A set PO_No='PPNO#:'+convert(varchar(20),Performa_No)+',PONO#:'+(select top 1 LPO_No from Inv_PerformaMain where PPNo=Performa_No) from Inv_MRVSub where InvMRVSlno=mrv_no and ItemId=@ItemId  
  if(@Flag<>0)
  begin
  update #A set AvgCost=0,FcRate=0
  end
  
  select  SlNo,InvoNo,InvoDate,CustName,Quantity,Rate,FCRate,CurrencyName,ItemCode,Description,OtherCost,CustAccount,Remarks,LocationName,DeptId,DepartmentName,PO_No,AvgCost,PurchaseType from #A  
 END    
 else    
  BEGIN    
  SELECT distinct top 50 SlNo, SlNo as InvoNo,InvoDate,convert(datetime,InvoDate,103) AS InvoiceDate,CustName,CustAccount,Inv_PurchaseMain.Remarks,I.ItemCode,I.[Description],SUM(S.Quantity) AS 'Quantity',    
  FCRate,CurrencyName,OtherCost,S.LocationId,LocationName,S.DeptId,DepartmentName,PO_No,isnull(S.Rate,0)Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no  
  into #B FROM Inv_PurchaseSub as S        
  left join Inv_PurchaseMain on S.InvMainSlno=Inv_PurchaseMain.SlNo and S.DeptId=Inv_PurchaseMain.DepartmentId     
  left join @TBLDUMMYSUP Tblsup on Inv_PurchaseMain.SupplierId=Tblsup.CustId        
  left join Mst_Currency on Inv_PurchaseMain.CurrencyId=Mst_Currency.Id        
  left join Mst_Item as I on S.ItemId=I.ItemId     
  left join Mst_Location on S.LocationId=Mst_Location.LocationId    
  left join Mst_Department  on S.DeptId=Mst_Department.DepartmentId    
  inner join Inv_InventoryTransaction AS INV on S.InvMainSlno=INV.BillNo and S.DeptId=INV.DeptId and S.ItemId=INV.ItemId AND TransType='PI' and s.BatchSlno=inv.BatchSlNo    
  where S.ItemId=@ItemId and S.DeptId in (select DeptId from Mst_UserDeptDivision where UserId=@UserId) and Inv_PurchaseMain.DelFlag=1 and S.DelFlag=1    
  GROUP BY FCRate,SlNo,InvoNo,InvoDate,CustName,CurrencyName,OtherCost,CustAccount,Inv_PurchaseMain.Remarks,PO_No,S.LocationId,LocationName,S.DeptId,DepartmentName,I.ItemCode,I.[Description],S.Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no  
  ORDER BY InvoiceDate DESC    
    
  update #B set PO_No='PPNO#:'+convert(varchar(20),Performa_No)+',PONO#:'+(select LPO_No from Inv_PerformaMain where PPNo=Performa_No) from Inv_MRVSub where InvMRVSlno=mrv_no and ItemId=@ItemId  
 if(@Flag<>0)
  begin
  update #B set AvgCost=0,FcRate=0
  end
  select  SlNo,InvoNo,InvoDate,CustName,Quantity,Rate,FCRate,CurrencyName,ItemCode,Description,OtherCost,CustAccount,Remarks,LocationName,DeptId,DepartmentName,PO_No,AvgCost,PurchaseType from #B  
  END    
         
 end     
else    
begin    
 if exists(select UserMenu from Mst_Users where UserId=@UserId and UserMenu like '%,M205,%')--ALL DEPT TRANSACTION VIEW PERMISSION    
  BEGIN    
  SELECT distinct SlNo,InvoNo,InvoDate,convert(datetime,InvoDate,103) AS InvoiceDate,CustName,CustAccount,Inv_PurchaseMain.Remarks,I.ItemCode,I.[Description],SUM(S.Quantity) AS 'Quantity',    
  FCRate,CurrencyName,OtherCost,S.LocationId,LocationName,S.DeptId,DepartmentName,PO_No,isnull(S.Rate,0)Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no  
   into #C FROM Inv_PurchaseSub as S     
  left join Inv_PurchaseMain on S.InvMainSlno=Inv_PurchaseMain.SlNo and S.DeptId=Inv_PurchaseMain.DepartmentId     
  left join @TBLDUMMYSUP Tblsup on Inv_PurchaseMain.SupplierId=Tblsup.CustId        
  left join Mst_Currency on Inv_PurchaseMain.CurrencyId=Mst_Currency.Id        
  left join Mst_Item as I on S.ItemId=I.ItemId     
  left join Mst_Location on S.LocationId=Mst_Location.LocationId    
  left join Mst_Department  on S.DeptId=Mst_Department.DepartmentId     
  inner join Inv_InventoryTransaction AS INV on S.InvMainSlno=INV.BillNo and S.DeptId=INV.DeptId and S.ItemId=INV.ItemId AND TransType='PI' and s.BatchSlno=inv.BatchSlNo        
  where S.ItemId=@ItemId and Inv_PurchaseMain.DelFlag=1 and S.DelFlag=1    
  GROUP BY FCRate,SlNo,InvoNo,InvoDate,CustName,CurrencyName,I.ItemCode,OtherCost,CustAccount,Inv_PurchaseMain.Remarks,PO_No,S.LocationId,LocationName,S.DeptId,DepartmentName,I.[Description],S.Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no  
      
 -- union all --Data From Previous Transaction Table    
  insert into #C    
  select BillNo 'SlNo',BillNo as InvoNo,InvDate as InvoDate,convert(datetime,InvDate,103) AS InvoiceDate,CustName,CustAccount,    
  '' Remarks,invPrev.ItemCode,invPrev.ItemDescription as Description,SUM(Quantity) AS 'Quantity',FCCost 'FCRate',CurrencyName,    
  invPrev.OtherCost,invPrev.LocationId,LocationName,invPrev.DeptId,DepartmentName,0 'PO_No',isnull(Cost,0) 'Rate',AvgCost 'AvgCost','local' PurchaseType,0  
  from Inv_InventoryTransaction_Prev invPrev    
  left join @TBLDUMMYSUP Tblsup on invPrev.CustomerId=Tblsup.CustId        
  left join Mst_Currency on invPrev.CurrencyId=Mst_Currency.Id        
  left join Mst_Location on invPrev.LocationId=Mst_Location.LocationId    
  left join Mst_Department  on invPrev.DeptId=Mst_Department.DepartmentId    
  left join Mst_Item as I on invPrev.ItemId=I.ItemId     
  where invPrev.ItemId=@ItemId AND TransType='PI'    
  GROUP BY FCPrice,InvoiceNo,InvDate,CustName,CurrencyName,invPrev.OtherCost,CustAccount,Remarks,invPrev.LocationId,LocationName,invPrev.DeptId,DepartmentName,invPrev.ItemCode,    
  invPrev.ItemDescription,Cost,FCCost,AvgCost,BillNo   
      
      
  ORDER BY InvoiceDate DESC   
    
  update #C set PO_No='PPNO#:'+convert(varchar(20),Performa_No)+',PONO#:'+(select LPO_No from Inv_PerformaMain where PPNo=Performa_No) from Inv_MRVSub where InvMRVSlno=mrv_no and ItemId=@ItemId  
  if(@Flag<>0)
  begin
  update #C set AvgCost=0,FcRate=0
  end
  select  SlNo,InvoNo,InvoDate,CustName,Quantity,Rate,FCRate,CurrencyName,ItemCode,Description,OtherCost,CustAccount,Remarks,LocationName,DeptId,DepartmentName,PO_No,AvgCost,PurchaseType from #C ORDER BY InvoiceDate DESC   
  END    
 ELSE    
  BEGIN    
  SELECT distinct SlNo,InvoNo,InvoDate,convert(datetime,InvoDate,103) AS InvoiceDate,CustName,CustAccount,Inv_PurchaseMain.Remarks,I.ItemCode,I.[Description],SUM(S.Quantity) AS 'Quantity',    
  FCRate,CurrencyName,OtherCost,S.LocationId,LocationName,S.DeptId,DepartmentName,PO_No,isnull(S.Rate,0)Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no  
   into #D FROM Inv_PurchaseSub as S        
  left join Inv_PurchaseMain on S.InvMainSlno=Inv_PurchaseMain.SlNo and S.DeptId=Inv_PurchaseMain.DepartmentId     
  left join @TBLDUMMYSUP Tblsup on Inv_PurchaseMain.SupplierId=Tblsup.CustId        
  left join Mst_Currency on Inv_PurchaseMain.CurrencyId=Mst_Currency.Id        
  left join Mst_Item as I on S.ItemId=I.ItemId     
  left join Mst_Location on S.LocationId=Mst_Location.LocationId    
  left join Mst_Department  on S.DeptId=Mst_Department.DepartmentId     
  inner join Inv_InventoryTransaction AS INV on S.InvMainSlno=INV.BillNo and S.DeptId=INV.DeptId and S.ItemId=INV.ItemId AND TransType='PI' and s.BatchSlno=inv.BatchSlNo        
  where S.ItemId=@ItemId and S.DeptId in (select DeptId from Mst_UserDeptDivision where UserId=@UserId) and Inv_PurchaseMain.DelFlag=1 and S.DelFlag=1    
  GROUP BY FCRate,SlNo,InvoNo,InvoDate,CustName,CurrencyName,I.ItemCode,OtherCost,CustAccount,Inv_PurchaseMain.Remarks,PO_No,S.LocationId,LocationName,S.DeptId,DepartmentName,I.[Description],S.Rate,AvgCost,Inv_PurchaseMain.PurchaseType  ,mrv_no  
      
  --union all  --Data From Previous Transaction Table   InvoiceNo   
  insert into #D    
  select BillNo 'SlNo',BillNo as InvoNo,InvDate as InvoDate,convert(datetime,InvDate,103) AS InvoiceDate,CustName,CustAccount,    
  '' Remarks,invPrev.ItemCode,invPrev.ItemDescription as Description,SUM(Quantity) AS 'Quantity',FCCost 'FCRate',CurrencyName,    
  invPrev.OtherCost,invPrev.LocationId,LocationName,invPrev.DeptId,DepartmentName,0 'PO_No',isnull(Cost,0) 'Rate',AvgCost 'AvgCost','local' PurchaseType,0  
  from Inv_InventoryTransaction_Prev invPrev    
  left join @TBLDUMMYSUP Tblsup on invPrev.CustomerId=Tblsup.CustId        
  left join Mst_Currency on invPrev.CurrencyId=Mst_Currency.Id        
  left join Mst_Location on invPrev.LocationId=Mst_Location.LocationId    
  left join Mst_Department  on invPrev.DeptId=Mst_Department.DepartmentId    
  left join Mst_Item as I on invPrev.ItemId=I.ItemId     
  where invPrev.ItemId=@ItemId AND TransType='PI' and invPrev.DeptId in (select DeptId from Mst_UserDeptDivision where UserId=@UserId)    
  GROUP BY FCPrice,InvoiceNo,InvDate,CustName,CurrencyName,invPrev.OtherCost,CustAccount,Remarks,invPrev.LocationId,LocationName,invPrev.DeptId,DepartmentName,invPrev.ItemCode,    
  invPrev.ItemDescription,Cost,FCCost,AvgCost,BillNo   
      
  ORDER BY InvoiceDate DESC    
    
  update #D set PO_No='PPNO#:'+convert(varchar(20),Performa_No)+',PONO#:'+(select LPO_No from Inv_PerformaMain where PPNo=Performa_No) from Inv_MRVSub where InvMRVSlno=mrv_no and ItemId=@ItemId    
  if(@Flag<>0)
  begin
  update #D set AvgCost=0,FcRate=0
  end
  
  select  SlNo,InvoNo,InvoDate,CustName,Quantity,Rate,FCRate,CurrencyName,ItemCode,Description,OtherCost,CustAccount,Remarks,LocationName,DeptId,DepartmentName,PO_No,AvgCost,PurchaseType from #D ORDER BY InvoiceDate DESC    
  END    
 end     
end        
end 
GO


