--CREATE PROCEDURE [dbo].[sp_StockMovementDetail] 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 CREATE TABLE #TempAllData(Conversion INT,ItemCode VARCHAR(50),ItemName VARCHAR(100),RouteCode VARCHAR(50),SalesOrgCode VARCHAR(50),TransactionDate DATETIME ,Loadedstockqty INT,Loadedstockqty_CS INT, Soldstockqty INT, Soldstockqty_CS INT,Closingstockqty INT,Closingstockqty_CS INT,GRVqty INT,GRVqty_CS INT , Openingstockqty INT,Openingstockqty_CS INT,RouteCode1 VARCHAR(50),InternalUOM VARCHAR(50),UnLoadedstockqty INT ,UnLoadedstockqty_CS INT ,DamageQty INT,DamageQty_CS INT,ExpireQty INT,ExpireQty_CS INT,GoodExcessAuditQty INT, GoodShortageAuditQty INt,BadExcessAuditQty int,BadShortageAuditQty INT,ExpiryExcessAuditQty INT, ExpiryShortageAuditQty INT,DamagedExcessAuditQty INT,DamagedShortageAuditQty INT) CREATE TABLE #TempDamageOrder(Conversion INT,ItemCode VARCHAR(50),ItemName VARCHAR(100),RouteCode VARCHAR(50),SalesOrgCode VARCHAR(50),TransactionDate DATETIME ,Loadedstockqty INT,Soldstockqty INT,Closingstockqty INT,GRVqty INT,Openingstockqty INT,RouteCode1 VARCHAR(50),InternalUOM VARCHAR(50),UnLoadedstockqty INT ,DamageQty INT,ExpireQty INT,GoodAuditQty INT,BadAuditQty int) Print 1 INSERT INTO #TempAllData(Conversion,ItemCode,ItemName,RouteCode,SalesOrgCode,TransactionDate,Loadedstockqty,Soldstockqty,Closingstockqty ,GRVqty,Openingstockqty,RouteCode1,InternalUOM,UnLoadedstockqty,DamageQty,ExpireQty,GoodExcessAuditQty, GoodShortageAuditQty,ExpiryExcessAuditQty,ExpiryShortageAuditQty,DamagedExcessAuditQty,DamagedShortageAuditQty) SELECT ISNULL(IU.Conversion,1) as Conversion,I.Code ItemCode,I.Description as ItemName,R.Code RouteCode,R.SalesOrgCode,CONVERT(DATE,GETDATE()) as TransactionDate ,CONVERT(INT,ISNULL(VL.Loadedstockqty,0)) as Loadedstockqty, CONVERT(INT,ISNULL(TH.Soldstockqty,0)) as Soldstockqty, CONVERT(INT, SUM(V.QuantityEach/*/CASE WHEN ISNULL(IU.Conversion,0)=0 THEN 1 ELSE ISNULL(IU.Conversion,0) END*/)) as Closingstockqty ,CONVERT(INT,ISNULL(GR.GrvQty,0)) as GRVqty, CONVERT(INT,ISNULL(OP.Openingstockqty,0)) as Openingstockqty, R.Code RouteCode,I.InternalUOM as InternalUOM ,CONVERT(INT,ISNULL(UVL.UnLoadedstockqty ,0)) as UnLoadedstockqty ,CONVERT(INT,ISNULL(DH.DamageQty,0)) as DamageQty ,CONVERT(INT,ISNULL(EH.ExpireQty,0)) as ExpireQty, GA.GoodExcessAuditQty ,GA.GoodShortageAuditQty,BA.ExpiryExcessAuditQty,BA.ExpiryShortageAuditQty, BA.DamagedExcessAuditQty,BA.DamagedShortageAuditQty FROM tblRoute R (NOLOCK) INNER JOIN tblItem I on I.SalesOrgCode=R.SalesOrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=I.Code and IU.UOM='CASE' LEFT JOIn tblVanStockLog V ON V.ItemCode=I.Code and V.RouteCode=R.Code LEFT JOIN ( SELECT ItemCode,RouteCode,TransactionDate,Soldstockqty FROM ( SELECT TD.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TD.QuantityLevel1*ISNULL(IU.Conversion,1)) Soldstockqty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrxDetail(NOLOCK) TD on TH.TrxCode=TD.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and /*IU.UOM=I.InternalUOM */IU.UOM=TD.Uom WHERE TH.TrxType in (1) and TH.TrxStatus=200 and TD.ItemType in ('O','F') AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TD.ItemCode,TH.RouteCode,TD.OrgCode,TH.RouteCode UNION SELECT TL.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TL.IssuedQuantity) Soldstockqty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrayStockLog TL on TL.TrxCode = TH.TrxCode and TL.RouteCode=TH.RouteCode WHERE TH.TrxType in (1,12) and TH.TrxStatus=200 AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TL.ItemCode,TH.RouteCode,TH.RouteCode ) as SL ) TH on TH.ItemCode=I.Code and TH.RouteCode=R.Code --and TH.SalesOrgCode=V.SalesOrgCode LEFT JOIN ( SELECT MD.ItemCode,MH.RouteCode,MH.OrgCode as SalesOrgCode,CONVERT(DATE,@RStartDAte) as TransactionDate ,SUM(ShippedQuantity/CASE WHEN ISNULL(IU.Conversion,0)=0 THEN 1 ELSE ISNULL(IU.Conversion,0) END) as Loadedstockqty FROM tblMovementHeader(NOLOCK) MH INNER JOIN tblMovementDetail(NOLOCK) MD on MD.MovementCode=MH.MovementCode INNER JOIN tblItem I on I.Code=MD.ItemCode and I.SalesOrgCode=MD.OrgCode LEFT JOIN tblItemUOM IU on IU.ItemCode=MD.ItemCode and IU.UOM=I.InternalUOM WHERE DATEDIFF(DD,MH.MovementDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR MH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) AND MH.MovementType=1 AND MH.MovementStatus=100 GROUP BY MD.ItemCode,MH.RouteCode,MH.OrgCode ) VL on VL.ItemCode=I.Code and VL.RouteCode=R.Code and VL.SalesOrgCode=R.SalesOrgCode LEFT JOIN ( SELECT MD.ItemCode,MH.RouteCode,MH.OrgCode as SalesOrgCode,CONVERT(DATE,@RStartDAte) as TransactionDate ,SUM(ShippedQuantity/CASE WHEN ISNULL(IU.Conversion,0)=0 THEN 1 ELSE ISNULL(IU.Conversion,0) END) as UnLoadedstockqty FROM tblMovementHeader(NOLOCK) MH INNER JOIN tblMovementDetail(NOLOCK) MD on MD.MovementCode=MH.MovementCode INNER JOIN tblItem I on I.Code=MD.ItemCode and I.SalesOrgCode=MD.OrgCode LEFT JOIN tblItemUOM IU on IU.ItemCode=MD.ItemCode and IU.UOM=I.InternalUOM WHERE DATEDIFF(DD,MH.MovementDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR MH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) AND MH.MovementType=2 AND MH.MovementStatus=100 GROUP BY MD.ItemCode,MH.RouteCode,MH.OrgCode ) UVL on UVL.ItemCode=I.Code and UVL.RouteCode=R.Code and UVL.SalesOrgCode=R.SalesOrgCode LEFT JOIN( SELECT ItemCode,RouteCode,TransactionDate,GrvQty FROM( SELECT TD.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TD.QuantityLevel1*ISNULL(IU.Conversion,1)) as GrvQty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrxDetail(NOLOCK) TD on TH.TrxCode=TD.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and /*IU.UOM=I.InternalUOM */IU.UOM=TD.Uom WHERE TH.TrxType in (4) and TH.TrxStatus=200 and (ISNULL(TD.Reason,'')='' OR ISNULL(TD.Reason,'') not in('Damage','Expiry')) AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TD.ItemCode,TH.RouteCode,TD.OrgCode,TH.RouteCode UNION SELECT ItemCode,RouteCode,TransactionDate,SUM(GrvQty) as GrvQty FROM( SELECT DISTINCT TL.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, TL.ReceivedQuantity GrvQty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrayStockLog TL on TL.RouteCode=TH.RouteCode AND DATEDIFF(DD,TL.CreatedOn,TH.TrxDate)=0 WHERE TH.TrxType in (1,12) and TH.TrxStatus=200 AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,',')))) as S GROUP BY ItemCode,RouteCode,TransactionDate ) as LS ) GR on GR.ItemCode=I.Code and GR.RouteCode=R.Code --and GR.SalesOrgCode=V.SalesOrgCode LEFT JOIN( SELECT VL.ItemCode,U.RouteCode,VL.SalesOrgCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(VL.QuantityEach) as Openingstockqty FROM tblVanStockLog(NOLOCK) VL INNER JOIN tblUser U ON U.Code=VL.userCode INNER JOIN tblItem I on I.Code=VL.ItemCode and I.SalesOrgCode=VL.SalesOrgCode --INNER JOIN tblItemUOM IU on IU.ItemCode=VL.ItemCode and IU.UOM=I.InternalUOM 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 ) OP on OP.ItemCode=I.Code and OP.RouteCode=R.Code and OP.SalesOrgCode=R.SalesOrgCode LEFT JOIN ( SELECT ItemCode,RouteCode,TransactionDate,DamageQty FROM ( SELECT TD.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TD.QuantityLevel1*ISNULL(IU.Conversion,1)) DamageQty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrxDetail(NOLOCK) TD on TH.TrxCode=TD.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and /*IU.UOM=I.InternalUOM */IU.UOM=TD.Uom WHERE TH.TrxType in(1,4) and TH.TrxStatus=200 and TD.Reason='Damage' AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TD.ItemCode,TH.RouteCode,TD.OrgCode,TH.RouteCode ) as SL ) DH on DH.ItemCode=I.Code and DH.RouteCode=R.Code --and TH.SalesOrgCode=V.SalesOrgCode LEFT JOIN ( SELECT ItemCode,RouteCode,TransactionDate,ExpireQty FROM ( SELECT TD.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TD.QuantityLevel1*ISNULL(IU.Conversion,1)) ExpireQty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrxDetail(NOLOCK) TD on TH.TrxCode=TD.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and /*IU.UOM=I.InternalUOM */IU.UOM=TD.Uom WHERE TH.TrxType in(1,4) and TH.TrxStatus=200 and TD.Reason='Expiry' AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TD.ItemCode,TH.RouteCode,TD.OrgCode,TH.RouteCode ) as SL ) EH on EH.ItemCode=I.Code and EH.RouteCode=R.Code LEFT JOIN ( SELECT Distinct ItemCode,RouteCode,TransactionDate,Sum(GoodExcessAuditQty) GoodExcessAuditQty, Sum(GoodShortageAuditQty) GoodShortageAuditQty FROM ( SELECT Distinct TD.ItemCode,U.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, CASE WHEN TD.Quantity*ISNULL(IU.Conversion,1)> TD.VanQty THEN TD.Quantity*ISNULL(IU.Conversion,1)-TD.VanQty ELSE 0 END GoodExcessAuditQty, CASE WHEN TD.Quantity*ISNULL(IU.Conversion,1)< TD.VanQty THEN ABS(TD.Quantity*ISNULL(IU.Conversion,1)-TD.VanQty) ELSE 0 END GoodShortageAuditQty FROM tblAudit(NOLOCK) TH INNER JOIN tblUser U ON U.Code=TH.usercode INNER JOIN tblAuditItemDetail(NOLOCK) TD on TH.AuditNumber=TD.AuditNumber INNER JOIN tblItem I on I.Code=TD.ItemCode --and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and /*IU.UOM=I.InternalUOM */IU.UOM=TD.Uom WHERE DATEDIFF(DD,AuditDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR U.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) ) Final GROUP BY ItemCode,RouteCode,TransactionDate ) GA on GA.ItemCode=I.Code and GA.RouteCode=R.Code --and TH.SalesOrgCode=V.SalesOrgCode LEFT JOIN ( SELECT ItemCode,RouteCode,TransactionDate,Sum(ExpiryExcessAuditQty) ExpiryExcessAuditQty, Sum(ExpiryShortageAuditQty) ExpiryShortageAuditQty,Sum(DamagedExcessAuditQty) DamagedExcessAuditQty, Sum(DamagedShortageAuditQty) DamagedShortageAuditQty FROM ( SELECT Distinct TD.ItemCode,U.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, CASE WHEN ISNULL(TH.Remarks,'')='Return' AND TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)> TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1) THEN TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)-(TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1) )ELSE 0 END ExpiryExcessAuditQty, CASE WHEN ISNULL(TH.Remarks,'')='Return' AND TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)< TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1) THEN ABS(TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)-(TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1))) ELSE 0 END ExpiryShortageAuditQty, CASE WHEN ISNULL(TH.Remarks,'')='Damage' AND TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)> TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1) THEN TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)-(TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1)) ELSE 0 END DamagedExcessAuditQty, CASE WHEN ISNULL(TH.Remarks,'')='Damage' AND TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)< TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1) THEN ABS(TD.Updated_NonSellableQuantity*ISNULL(IU.Conversion,1)-(TD.Actual_NonSellableQuantity*ISNULL(IU.Conversion,1))) ELSE 0 END DamagedShortageAuditQty FROM tblReturnAudit(NOLOCK) TH INNER JOIN tblUser U ON U.Code=TH.usercode INNER JOIN tblReturnAuditItemDetails(NOLOCK) TD on TH.AuditCode=TD.AuditCode INNER JOIN tblItem I on I.Code=TD.ItemCode --and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and /*IU.UOM=I.InternalUOM */IU.UOM=TD.Attribute1 WHERE DATEDIFF(DD,AuditDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR U.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) ) Final GROUP BY ItemCode,RouteCode,TransactionDate ) BA ON BA.ItemCode=I.Code and BA.RouteCode=R.Code --and TH.SalesOrgCode=V.SalesOrgCode WHERE /*DATEDIFF(DD,V.ModifiedOn,/*DATEADD(day,-6,GETDATE())*/@RStartDAte)>=0 AND */ (ISNULL(@SearchString,'') = '1=1' OR R.Code in (SELECT value FROM dbo.fn_split(@SearchString,','))) --AND V.ItemCode not in ('30-0101') --AND V.QuantityEach>0 GROUP BY I.Code,R.Code,R.SalesOrgCode,TH.Soldstockqty,VL.Loadedstockqty,GR.GrvQty, OP.Openingstockqty,I.Description,UVL.UnLoadedstockqty ,I.InternalUOM,DH.DamageQty,EH.ExpireQty,ISNULL(IU.Conversion,1),GA.GoodExcessAuditQty,GA.GoodShortageAuditQty , BA.ExpiryExcessAuditQty,BA.ExpiryShortageAuditQty, BA.DamagedExcessAuditQty,BA.DamagedShortageAuditQty ORDER BY I.Code ASC Print 'END' INSERT INTO #TempDamageOrder(Conversion,ItemCode,ItemName,RouteCode,SalesOrgCode,TransactionDate,Loadedstockqty,Soldstockqty,Closingstockqty ,GRVqty,Openingstockqty,RouteCode1,InternalUOM,UnLoadedstockqty,DamageQty,ExpireQty) SELECT DISTINCT ISNULL(IU.Conversion,1) as Conversion,TD.ItemCode,I.Description as ItemName,TH.RouteCode,TH.OrgCode as SalesOrgCode,@RStartDAte TransactionDate ,0 Loadedstockqty,0 Soldstockqty,0 Closingstockqty,0 GRVqty,0 Openingstockqty,TH.RouteCode,I.InternalUOM,0 UnLoadedstockqty ,CONVERT(INT,ISNULL(DH.DamageQty,0)) as DamageQty,CONVERT(INT,ISNULL(SH.ExpireQty,0)) as ExpireQty FROM tblTrxHeader TH INNER JOIN tblTrxDetail TD on TD.TrxCode=TH.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and IU.UOM='CASE' LEFT JOIN( SELECT ItemCode,RouteCode,TransactionDate,DamageQty FROM ( SELECT TD.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TD.QuantityLevel1) DamageQty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrxDetail(NOLOCK) TD on TH.TrxCode=TD.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and IU.UOM=I.InternalUOM WHERE TH.TrxType in (1,12) and TH.TrxStatus=200 and TD.Reason='Damage' AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TD.ItemCode,TH.RouteCode,TD.OrgCode,TH.RouteCode) as T ) DH on DH.ItemCode=TD.ItemCode and DH.RouteCode=TH.RouteCode LEFT JOIN( SELECT ItemCode,RouteCode,TransactionDate,ExpireQty FROM ( SELECT TD.ItemCode,TH.RouteCode,CONVERT(DATE,@RStartDAte) as TransactionDate, SUM(TD.QuantityLevel1) ExpireQty FROM tblTrxHeader(NOLOCK) TH INNER JOIN tblTrxDetail(NOLOCK) TD on TH.TrxCode=TD.TrxCode INNER JOIN tblItem I on I.Code=TD.ItemCode and I.SalesOrgCode=TD.OrgCode INNER JOIN tblItemUOM IU on IU.ItemCode=TD.ItemCode and IU.UOM=I.InternalUOM WHERE TH.TrxType in (1,12) and TH.TrxStatus=200 and TD.Reason='Expiry' AND DATEDIFF(DD,TrxDate,@RStartDAte)=0 and (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) GROUP BY TD.ItemCode,TH.RouteCode,TD.OrgCode,TH.RouteCode) as T ) SH on SH.ItemCode=TD.ItemCode and SH.RouteCode=TH.RouteCode WHERE TH.TrxType in (1,12) AND TH.TrxStatus=200 AND DATEDIFF(DD,TH.TrxDate,@RStartDAte)=0 AND (ISNULL(@SearchString,'') = '1=1' OR TH.RouteCode in (SELECT value FROM dbo.fn_split(@SearchString,','))) AND TD.ItemType in ('D') PRINT 'Start' Select * INTO #Final from ( SELECT Conversion,ItemCode,ItemName,RouteCode,SalesOrgCode,TransactionDate,Loadedstockqty,Soldstockqty, ((ISNULL(Loadedstockqty,0) + ISNULL(GRVqty,0)+ISNULL(Openingstockqty,0)+ ISNULL(GoodExcessAuditQty,0)) -(ISNULL(UnLoadedstockqty,0)+ISNULL(Soldstockqty,0)+ISNULL(GoodShortageAuditQty,0))) Closingstockqty ,GRVqty,Openingstockqty,RouteCode1,InternalUOM,UnLoadedstockqty, (ISNULL(DamageQty,0) + ISNULL(DamagedExcessAuditQty,0))-ISNULL(DamagedShortageAuditQty,0) DamageQty, (ExpireQty+ ISNULL(ExpiryExcessAuditQty,0))-ISNULL(ExpiryShortageAuditQty,0) ExpireQty FROM #TempAllData UNION SELECT DISTINCT Conversion,ItemCode,ItemName,RouteCode,SalesOrgCode,TransactionDate,Loadedstockqty,Soldstockqty, ((Loadedstockqty + GRVqty+Openingstockqty) -(UnLoadedstockqty+Soldstockqty)) Closingstockqty ,GRVqty,Openingstockqty,RouteCode1,InternalUOM,UnLoadedstockqty,DamageQty,ExpireQty FROM #TempDamageOrder WHERE ItemCode NOT IN (SELECT DISTINCT ItemCOde from #TempAllData) ) F SELECT S.Conversion,S.ItemCode,ItemName,RouteCode,RouteCode1,InternalUOM,S.SalesOrgCode,TransactionDate,CAST(SUM(Loadedstockqty) as int) Loadedstockqty , CAST(SUM(Loadedstockqty_CS) as int) Loadedstockqty_CS,CAST(SUM(Soldstockqty) as int) Soldstockqty, CAST(SUM(Soldstockqty_CS) as int) Soldstockqty_CS,CAST(SUM(Closingstockqty) as int) Closingstockqty, CAST(SUM(Closingstockqty_CS) as int) Closingstockqty_CS,CAST(SUM(GRVqty) as int) GRVqty,CAST(SUM(GRVqty_CS) as int) GRVqty_CS, CAST(SUM(Openingstockqty) as int) Openingstockqty , CAST(SUM(Openingstockqty_CS) as int) Openingstockqty_CS,CAST(SUM(UnLoadedstockqty) as int) UnLoadedstockqty, CAST(SUM(UnLoadedstockqty_CS) as int) UnLoadedstockqty_CS, CAST(SUM(DamageQty_CS) as int) DamageQty_CS,CAST(SUM(DamageQty) as int) DamageQty, CAST(SUM(ExpireQty) as int) ExpireQty,CAST(SUM(ExpireQty_CS) as int) ExpireQty_CS INTO #Result FROM ( SELECT 0 Loadedstockqty_CS,CASE WHEN Cast(S.Loadedstockqty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.Loadedstockqty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.Loadedstockqty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END Loadedstockqty, 0 Soldstockqty_CS,CASE WHEN Cast(S.Soldstockqty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.Soldstockqty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.Soldstockqty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END Soldstockqty , 0 Closingstockqty_CS,CASE WHEN Cast(S.Closingstockqty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.Closingstockqty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.Closingstockqty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END Closingstockqty, 0 GRVqty_CS,CASE WHEN Cast(S.GRVqty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.GRVqty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.GRVqty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END GRVqty , 0 Openingstockqty_CS,CASE WHEN Cast(S.Openingstockqty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.Openingstockqty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.Openingstockqty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END Openingstockqty, 0 UnLoadedstockqty_CS,CASE WHEN Cast(S.UnLoadedstockqty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.UnLoadedstockqty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.UnLoadedstockqty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END UnLoadedstockqty , 0 DamageQty_CS,CASE WHEN Cast(S.DamageQty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.DamageQty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.DamageQty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END DamageQty, 0 ExpireQty_CS,CASE WHEN Cast(S.ExpireQty as decimal(15,2))>= Case when ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end THEN (Cast(S.ExpireQty as decimal(15,2))%CASE WHEN ISNULL(Iu1.Conversion,1)=0 then 1 else ISNULL(Iu1.Conversion,1) end)/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE S.ExpireQty*1.0/ CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END END ExpireQty, S.Conversion,S.ItemCode,S.ItemName,RouteCode,S.SalesOrgCode,TransactionDate,RouteCode1,S.InternalUOM FROm #Final S INNER JOIn tblItemUom IU ON IU.ItemCode=S.ItemCode AND Iu.Uom='PCS' LEFT JOIN tblItemUom Iu1 ON Iu1.ItemCode=S.ItemCode AND IU1.UOM='CASE' UNION ALL Select CASE WHEN S.Loadedstockqty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.Loadedstockqty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END Loadedstockqty_CS, 0 Loadedstockqty, CASE WHEN S.Soldstockqty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.Soldstockqty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END Soldstockqty_CS, 0 Soldstockqty, CASE WHEN S.Closingstockqty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.Closingstockqty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END Closingstockqty_CS, 0 Closingstockqty, CASE WHEN S.GRVqty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.GRVqty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END GRVqty_CS, 0 GRVqty, CASE WHEN S.Openingstockqty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.Openingstockqty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END Openingstockqty_CS, 0 Openingstockqty, CASE WHEN S.UnLoadedstockqty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.UnLoadedstockqty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END UnLoadedstockqty_CS, 0 UnLoadedstockqty, CASE WHEN S.DamageQty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.DamageQty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END DamageQty_CS, 0 DamageQty, CASE WHEN S.ExpireQty>= CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END THEN Cast(S.ExpireQty as decimal(15,2))/CASE WHEN ISNULL(Iu.Conversion,1)=0 THEN 1 ELSE ISNULL(Iu.Conversion,1) END ELSE 0 END ExpireQty_CS, 0 ExpireQty, S.Conversion,S.ItemCode,ItemName,RouteCode,S.SalesOrgCode,TransactionDate,RouteCode1,S.InternalUOM from #Final S INNER JOIn tblItemUom IU ON IU.ItemCode=S.ItemCode AND Iu.Uom='CASE' ) S GROUP BY S.Conversion,S.ItemCode,ItemName,RouteCode,S.SalesOrgCode,TransactionDate,S.RouteCode1,S.InternalUOM Select * from #Result Where ItemCode in('38876','63113') and (Isnull(Openingstockqty,0)>0 or Isnull(Openingstockqty_CS,0)>0 or isnull(Loadedstockqty,0)>0 OR ISNULL(Openingstockqty_CS,0)>0 or Isnull(Soldstockqty,0)>0 or Isnull(Soldstockqty_CS,0)>0 or isnull(UnLoadedstockqty,0)>0 or Isnull(UnLoadedstockqty_CS,0)>0 OR isnull(ExpireQty,0)>0 Or ISNULL(ExpireQty_CS,0)>0 OR ISNULL(DamageQty,0)>0 OR ISNULL(DamageQty_CS,0)>0 OR ISNULL(Closingstockqty,0)>0 or ISNULL(Closingstockqty_CS,0)>0 OR ISNULL(GRVqty,0)>0 OR ISNUll(GRVqty_CS,0)>0 ) Order By ItemCode DROp TABLE #Final DROP TABLE #TempAllData DROP TABLE #TempDamageOrder DROP table #Result Drop table #Temp