DECLARE @ModifiedDate INT, @ModifiedTime INT, @TrxType VARCHAR(10),@WHCode varchar(100),
 @UserCode VARCHAR(50), @OrgCode VARCHAR(50),@MovementStatus int=2                                                                      
SELECT @ModifiedDate = ModifiedDate, @ModifiedTime = ModifiedTime FROM vw_GetModifiedDateTime   


Select TOp 1@WHCode=R.WHCode FROm tblMovementHeader MH INNER JOIN tblRoute R ON R.Code=MH.RouteCode
INNER JOIN LoadApproval LA ON LA.movementCode=MH.MovementCode

Select Distinct Code RouteCode,@WHCode WarehouseCode Into #tempAllRoute From tblRoute Where WHCode=@WHCode 


Select  * from #tempAllRoute
Print 'TotalDistinct Item in loadRequest'
Select Distinct ItemCode,@WHCode WHCode INTO #TenpAllItems From LoadApproval

Select Distinct TAI.WHCode,TAI.ItemCode,sum(Final.TotalQty) ReservedQty_PCS,ST.QtyBU StockQty,0 AvailableQty Into #FinalResult From #TenpAllItems TAI
LEFT JOIn tblStock St ON ST.WHCode=TAI.WHCode and TAI.ItemCode=ST.itemCode and isnull(St.StockType,'')='Good'
LEFT JOIN (
Select Distinct R.WHCode,MD.MovementCode, MD.ItemCode,MD.UOM, Isnull(MD.ShippedQuantity,0)*Isnull(IU.Conversion,1) TotalQty
FROM tblMovementHeader MH INNER JOIn tblMovementdetail MD On MD.MovementCode=MH.MovementCode
INNER JOIn #TenpAllItems TI ON TI.ItemCode=MD.ItemCode /*and TI.ItemCode='180396'*/
INNER JOIN tblItemUOM IU ON IU.ItemCode=MD.itemCode and MD.UOM=IU.UOM and IU.SalesOrgCode=MD.OrgCode
INNER JOIn tblRoute R ON R.Code=MH.RouteCode
INNER JOIn #tempAllRoute TR On TR.RouteCode=R.Code and R.WHCode=TR.WarehouseCode
LEFT JOIn tblMovementHeader_CarryForward MC ON MC.UnloadMovementCode=MH.MovementCode
Where MC.LoadMovementCode is null and MH.MovementType=2
UNION 
Select Distinct R.WHCode,MD.MovementCode,MD.ItemCode,MD.UOM, isnull(MD.InProcessQuantity,0)*Isnull(IU.Conversion,1) TotalQty
FROM tblMovementHeader MH INNER JOIn tblMovementdetail MD On MD.MovementCode=MH.MovementCode
INNER JOIn #TenpAllItems TI ON TI.ItemCode=MD.ItemCode /*and TI.ItemCode='180396'*/
INNER JOIN tblItemUOM IU ON IU.ItemCode=MD.itemCode and MD.UOM=IU.UOM and IU.SalesOrgCode=MD.OrgCode
INNER JOIn tblRoute R ON R.Code=MH.RouteCode
INNER JOIn #tempAllRoute TR On TR.RouteCode=R.Code and R.WHCode=TR.WarehouseCode
INNER JOIn tblMovementHeader_CarryForward MC ON MC.LoadMovementCode=MH.MovementCode
Where MH.MovementType=1 and MH.MovementStatus=101
) Final ON TAI.ItemCode=Final.ItemCode and Final.WHCode=TAI.WHCode
 Group BY  TAI.WHCode,TAI.ItemCode,ST.QtyBU

Update #FinalResult Set AvailableQty=isnull(StockQty,0)-Isnull(ReservedQty_PCS,0)

Select * from #FinalResult
Declare @MovementCode varchar(100),@LoadRequestNo varchar(100),@CreatedBy varchar(100)
Select Top 1@MovementCode=MovementCode,@LoadRequestNo=LoadRequestNo,@CreatedBy=UserCode From LoadApproval 


Declare @TOtalCount int,@Count int=1
Select @TOtalCount=Max(Row_Num) From LoadApproval
While (@Count<=@TOtalCount)
BEGIn
Declare @ItemCode varchar(100),@UOM varchar(100),@PrevReturnPC float, @FinalApproveQty_PCS int,@LineNo int, 
@FinalApproveQty int,@FinalAvailableQty int,@StockAvailableQty int,@Conversion float

Select @LineNo=Max(CAST([LineNo] as int))+1 from tblMovementDetail where Movementcode= @MovementCode
--Select Isnull(IU.Conversion,1) ,FA.AvailableQty,LA.ItemCode,
--CASE WHEN FA.AvailableQty-(LA.ShippedQuantityCS* Isnull(IU.Conversion,1))<0
-- THEN FA.AvailableQty*1.0/Isnull(IU.Conversion,1) ELSE LA.ShippedQuantityCS END
--from LoadApproval LA INNER JOIn tblitemUOm IU ON IU.ItemCode=LA.ItemCode
--INNER JOIn #FinalResult FA On FA.ItemCode=LA.ItemCode
--where LA.Row_Num=@Count

Select @Conversion=Isnull(IU.Conversion,1) ,@UOM=LA.UOM ,@PrevReturnPC=LA.PrevReturnPC, @StockAvailableQty=FA.AvailableQty,@ItemCode=LA.ItemCode,
@FinalApproveQty=CASE WHEN FA.AvailableQty-(LA.ShippedQuantityCS* Isnull(IU.Conversion,1))<0
 THEN FA.AvailableQty*1.0/Isnull(IU.Conversion,1) ELSE LA.ShippedQuantityCS END
from LoadApproval LA INNER JOIn tblitemUOm IU ON IU.ItemCode=LA.ItemCode and LA.UOM=IU.UOM
INNER JOIn #FinalResult FA On FA.ItemCode=LA.ItemCode
where LA.Row_Num=@Count

SET @FinalApproveQty_PCS=@FinalApproveQty*@Conversion
Set @FinalAvailableQty=@StockAvailableQty-@FinalApproveQty_PCS

update #FinalResult Set AvailableQty=isnull(@FinalAvailableQty,0) where ItemCode=@ItemCode
Update LoadApproval set ShippedQuantity=isnull(@FinalApproveQty,0) Where Row_Num=@Count

IF EXISTS(SELECT 1 FROM tblMovementDetail Where Movementcode=@MovementCode And ItemCode=@ItemCode and (UOM=@UOM OR  (UOM='KG' and @UOM='CASE')))                  
BEGIN

 UPDATE tblMovementDetail  SET                         
 /*OriginalQuantityLevel1=QuantityLevel1,                  
  OriginalQuantityLevel2=QuantityLevel2 , */                        
     LogisticsQunatityLevel1=@FinalApproveQty ,                                    
  LogisticsQunatityLevel2=QuantityLevel2, LogisticsQunatityLevel3= (isNULL(@Conversion,1)*@FinalApproveQty) ,                              
   SupervisorQunatityLevel1= @FinalApproveQty ,                                    
  SupervisorQunatityLevel3= @FinalApproveQty ,                                    
     MovementStatus = @MovementStatus,                                         
   QuantityLevel1=@FinalApproveQty,                                    
  QuantityLevel3=(isNULL(@Conversion,1)*@FinalApproveQty),                                    
  QuantityBU=(isNULL(@Conversion,1)*@FinalApproveQty),                                    
  InProcessQuantity = ((isNULL(@Conversion,1)*@FinalApproveQty) +Isnull( @PrevReturnPC,0)),                                     
  ApprovedQuantity = @FinalApproveQty,                  
  FinalQuantityToPushToERP= @FinalApproveQty,         
  PrevReturn=isnull(@PrevReturnPC,0),                                                 
        ModifiedDate = @ModifiedDate,                                                                                                      
        ModifiedTime = @ModifiedTime,                                                                                            
        ModifiedOn = GETDATE(),                                          
        BatchNumber = ''                                                                 
     WHERE MovementCode = @MovementCode AND ItemCode = @ItemCode AND  (UOM=@UOM  OR (UOM='KG' and @UOM='CASE')) 

END
ELSE                  
                  
BEGIn                  
Insert into tblMovementDetail                                           
([LineNo],MovementCode,ItemCode,OrgCode,ItemDescription,ItemAltDescription,MovementStatus,UOM,QuantityLevel1,QuantityLevel2,                    
QuantityLevel3,QuantityBU                                              
,QuantitySU,NonSellableQty,CurrencyCode,PriceLevel1,PriceLevel2,PriceLevel3,MovementReasonCode,ExpiryDate,Note,AffectedStock,Status,DistributionCode                                              
,CreatedOn,ModifiedDate,ModifiedTime,PushedOn,CancelledQuantity,InProcessQuantity,ShippedQuantity,ModifiedOn,BatchNumber,ReturnType,Remarks,UnloadQuantity                   
,IsUnloaded,ApprovedQuantity,UnitPerCase,RouteCode,CurrentStock,Priority,GroupItems,SubGroupDescription,OrderNumber,Customer_Code,OriginalQuantityLevel1                                              
,OriginalQuantityLevel2,OriginalQuantityLevel3,SalesmanQuantityLevel2,SalesmanQuantityLevel3,SKUCOntroller1QuantityLevel2,SKUCOntroller1QuantityLevel3                                            
,SupervisorQunatityLevel1,SupervisorQunatityLevel3,AMBMQuantityLevel2,AMBMQuantityLevel3,SKUCOntroller2QuantityLevel2,SKUCOntroller2QuantityLevel3                                              
,LogisticsQunatityLevel1,LogisticsQunatityLevel2,LogisticsQunatityLevel3                                        
,TrackQuantity,IsConsolidatedDone,SellingUOM,SellingUOMQty ,VanQuantity,MSLPriority,LoadSequenceNo,TotalAmbientCapacity,TotalChilledCapacity                      
,TargetCapacity,TargetAmbientCapacity,TargetChilledCapacity,StartAmbientCapacity,StartChilledCapacity,NeededAmbientCapcity,NeededChilledCapacity                      
,BalanceAmbientCapacity,BalanceChilledCapacity,FinalQuantityToPushToERP,PrevReturn)                     
                    
SELECT Top 1@LineNo,@MovementCode,@ItemCode,OrgCode,(Select top 1Description from tblItem Where Code=@ItemCode) ItemDescription,      
(Select Top 1AltDescription from tblItem Where Code=@ItemCode) ItemAltDescription,3,@UOM UOM,@FinalApproveQty QuantityLevel1,0 QuantityLevel2,                    
@FinalApproveQty*@Conversion QuantityLevel3,0 QuantityBU,0 QuantitySU,NonSellableQty,CurrencyCode,PriceLevel1,PriceLevel2,PriceLevel3,MovementReasonCode,ExpiryDate,Note,AffectedStock,Status,DistributionCode                                              
,Getdate() CreatedOn,VW.ModifiedDate,VW.ModifiedTime,PushedOn,CancelledQuantity,        
((isNULL(@Conversion,1)*@FinalApproveQty) + @PrevReturnPC) InProcessQuantity,0 ShippedQuantity,getdate() ModifiedOn,BatchNumber,ReturnType,Remarks,UnloadQuantity                                              
,IsUnloaded,@FinalApproveQty ApprovedQuantity,UnitPerCase,RouteCode,CurrentStock,Priority,GroupItems,SubGroupDescription,OrderNumber,Customer_Code,0 OriginalQuantityLevel1                                              
,OriginalQuantityLevel2,0 OriginalQuantityLevel3,SalesmanQuantityLevel2,SalesmanQuantityLevel3,                    
SKUCOntroller1QuantityLevel2,SKUCOntroller1QuantityLevel3 ,@FinalApproveQty SupervisorQuantityLevel1,@FinalApproveQty SupervisorQuantityLevel3,        
AMBMQuantityLevel2,AMBMQuantityLevel3, SKUCOntroller2QuantityLevel2,SKUCOntroller2QuantityLevel3,@FinalApproveQty LogisticsQunatityLevel1,        
0 LogisticsQunatityLevel2, @FinalApproveQty*@Conversion LogisticsQunatityLevel3,TrackQuantity,IsConsolidatedDone,SellingUOM,SellingUOMQty ,        
VanQuantity,MSLPriority,LoadSequenceNo,TotalAmbientCapacity,TotalChilledCapacity,TargetCapacity,TargetAmbientCapacity,TargetChilledCapacity,        
StartAmbientCapacity,StartChilledCapacity,NeededAmbientCapcity,NeededChilledCapacity,BalanceAmbientCapacity,BalanceChilledCapacity,        
@FinalApproveQty FinalQuantityToPushToERP,@PrevReturnPC PrevReturn  FROM tblMovementDetail MD                     
INNER JOIN vw_GetModifiedDateTime VW on 1=1 Where MovementCode=@MovementCode /*and ItemCode=@ItemCode*/                    
                   
END   

SET @Count=@Count+1
END

Select * from LoadApproval

drop table #tempAllRoute
Drop table #TenpAllItems
Drop table #FinalResult