DECLARE                                                    
@StartDate VARCHAR(30)='2021-03-30',                                                  
@EndDate  VARCHAR(30)='2021-03-30',                                                
@SearchString VARCHAR(MAX)='509',                                              
@UserCode VARCHAR(MAX)=''                                                                                                                            
--AS                                                                                                                                
DECLARE @RStartDAte DATETIME,@REndDAte DATETIME                                                                                                                             
IF LEN(@SearchString) = 0                                                                                                                                        
 SET @SearchString = '1=1'                                                                                                                       
IF LEN(@StartDate) = 0                                              
BEGIN                                                                                                                                        
SET @RStartDAte = (select GETDATE())                                              
SET @REndDAte = (select GETDATE())                                              
END                                              
ELSE                                              
BEGIN                                              
SET @RStartDAte =Convert(date,@StartDate)                                              
SET @REndDAte =Convert(date,@EndDate)                                              
END

SELECT Distinct VL.RouteCode,VL.TrxType,VL.TrxCode, Vl.UserCode ActualTrxType, Vl.UserCode TrxStatus
Into #Temp
 FROM tblVanStockLog(NOLOCK) VL                                              
 INNER JOIN tblItem I on I.Code=VL.ItemCode and I.SalesOrgCode=VL.SalesOrgCode --and I.Code='24782'                                              
 WHERE  DATEDIFF(DD,VL.ModifiedOn,/*DATEADD(day,-6,GETDATE())*/@RStartDAte)>0  and                                               
 --(ISNULL(@SearchString,'') = '1=1' OR 
 VL.RouteCode =@SearchString

 
 Update T Set T.ActualTrxType='TrxHeader',T.TrxStatus=H.TRXStatus from #Temp T Inner Join tblTrxHeader H On T.trxCode=H.TrxCode 
 Update T Set T.ActualTrxType='MovementHeader',T.TrxStatus=H.MovementStatus from #Temp T Inner Join tblMovementHeader H On T.trxCode=H.MovementCode


Delete from #Temp Where ActualTrxType=TrxStatus 
SELECT VL.ItemCode,U.RouteCode,VL.SalesOrgCode,CONVERT(DATE,@RStartDAte) as TransactionDate,                                              
 SUM(VL.QuantityEach) as Openingstockqty                                              
 FROM tblVanStockLog(NOLOCK) VL                                              
 INNER JOIN tblItem I on I.Code=VL.ItemCode and I.SalesOrgCode=VL.SalesOrgCode and I.Code in('38876','63113')                                           
 INNER JOIN tblUser U ON U.Code=VL.userCode                                              
 WHERE  DATEDIFF(DD,VL.ModifiedOn,/*DATEADD(day,-6,GETDATE())*/@RStartDAte)>=0  and                                               
 (ISNULL(@SearchString,'') = '1=1' OR U.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) 
 and VL.TrxCode Not in(Select TrxCode from #Temp)                                         
 GROUP BY VL.ItemCode,U.RouteCode,VL.SalesOrgCode
 Drop table #Temp

 --Select * from tblVanstockLog where isnull(RouteCode,'')='' order by Modifiedon desc
 /*
 Select distinct TH.trxCode,TH.TrxType,Th.CollectionType ,TD.* from tblTrxheader TH INNER JOIN tbltrxDetail TD ON TD.TrxCode=TH.TrxCode
 Where TH.RouteCode='509' and TD.itemCode='38876'
 and Datediff(dd,Th.TrxDate,'2021-03-30')=0 and TH.trxStatus=200
 --group bY TH.trxCode,TH.TrxType,TD.ItemType
 */