/*
   جمعه, 12 خرداد 1402 10:40:07 ب.ظ
   User: 
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/


Use ATNDBTest
Go
/*=============================================================================================================*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.Asset ADD NetValue money NULL
GO
ALTER TABLE AST.Asset SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
/*====================================================================================================================================*/
If Exists(Select * From sys.objects Where name = 'CalculateAssetDepreciation') Drop Function AST.CalculateAssetDepreciation
Go
Create Function AST.CalculateAssetDepreciation(@AssetSerial As Bigint, @EffectiveDate Char(10), @AssetDocHeaderSerial As Bigint)  
Returns @ResultTable Table
(
	AssetSerial Bigint,
	BuyUnitPrice Money,
	FinalBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul Money,
	ToEffectiveDateBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul Money,
	FinalResidualValue Money,
	ToEffectiveDateResidualValue Money,
	FinalBookValue Money,
	ToEffectiveDateBookValue Money,
	FinalAccumulatedDepreciation Money,
	ToEffectiveDateAccumulatedDepreciation Money,
	AssetDepreciation Money
)
With Encryption
As
Begin
	
	If(@AssetDocHeaderSerial = -1)
		Return

	If (@EffectiveDate Is Null And IsNull(@AssetDocHeaderSerial,0) > 0)
	Begin
		Select @EffectiveDate = EffectiveDate From AST.AssetDocHeader Where Serial = @AssetDocHeaderSerial
	End
	If (@AssetDocHeaderSerial Is Null Or 
	    @AssetDocHeaderSerial = 0 Or 
		@EffectiveDate Is Null Or 
		Ltrim(Rtrim(@EffectiveDate)) = '/  /' Or 
		Ltrim(Rtrim(@EffectiveDate)) = '')
	Begin
		Select @AssetDocHeaderSerial = Cast('In CalculateAssetDepreciation function only AssetSerial can have null value' As int)
	End

	Insert Into @ResultTable
	Select 
		AssetSerial,
		BuyUnitPrice,
		
		FinalBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul = Case When FinalTakeOutDocSerial Is Not Null Then 0 Else FinalBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul End,
		ToEffectiveDateBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul,

		FinalResidualValue = Case When FinalTakeOutDocSerial Is Not Null Then 0 Else FinalResidualValue End,
		
		ToEffectiveDateResidualValue = Case When ToEffectiveDateTakeOutDocSerial Is Not Null Then 0 Else ToEffectiveDateResidualValue End,
		
		FinalBookValue = Case When FinalBookValue < FinalResidualValue Or FinalResidualDocSerial Is Not Null Then FinalResidualValue 
						      When FinalTakeOutDocSerial Is Not Null Then 0
						      Else FinalBookValue End,
		
		ToEffectiveDateBookValue = Case When ToEffectiveDateBookValue < ToEffectiveDateResidualValue Or ToEffectiveDateResidualDocSerial Is Not Null Then ToEffectiveDateResidualValue 
						                When ToEffectiveDateTakeOutDocSerial Is Not Null Then 0
						                Else ToEffectiveDateBookValue End,
		

		FinalAccumulatedDepreciation = Case When FinalBookValue < FinalResidualValue Or FinalResidualDocSerial Is Not Null Then FinalBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul - FinalResidualValue 
						                    When FinalTakeOutDocSerial Is Not Null Then 0
		                                    Else FinalAccumulatedDepreciation End,

		ToEffectiveDateAccumulatedDepreciation = Case When ToEffectiveDateBookValue < ToEffectiveDateResidualValue Or ToEffectiveDateResidualDocSerial Is Not Null Then ToEffectiveDateBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul - ToEffectiveDateResidualValue 
						                              When ToEffectiveDateTakeOutDocSerial Is Not Null Then 0
		                                              Else ToEffectiveDateAccumulatedDepreciation End,

		AssetDepreciation = Case When ToEffectiveDateTakeOutDocSerial Is Not Null Or ToEffectiveDateResidualDocSerial Is Not Null Then 0
								 When ToEffectiveDateTakeOutDocSerial Is Null And ToEffectiveDateResidualDocSerial Is Null And VerificationDate Is Null And ToEffectiveDateBookValue - AssetDepreciation < ToEffectiveDateResidualValue Then ToEffectiveDateBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul - ToEffectiveDateResidualValue - ToEffectiveDateAccumulatedDepreciation 
								 When ToEffectiveDateTakeOutDocSerial Is Null And ToEffectiveDateResidualDocSerial Is Null And VerificationDate Is Not Null And ToEffectiveDateBookValue < ToEffectiveDateResidualValue Then AssetDepreciation - (ToEffectiveDateResidualValue - ToEffectiveDateBookValue) 
							     Else AssetDepreciation End
	From
		(Select
			TblManualDepreciation.VerificationDate,
			AssetSerial = Asset.Serial,
			TblResidualDoc.FinalResidualDocSerial,
			TblResidualDoc.ToEffectiveDateResidualDocSerial,
			TblTakeOutDoc.FinalTakeOutDocSerial,
			TblTakeOutDoc.ToEffectiveDateTakeOutDocSerial,
			TblResidualValue.FinalResidualValue,
			TblResidualValue.ToEffectiveDateResidualValue,
			TblBuyUnitPrice.BuyUnitPrice,
			FinalBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul = IsNull(TblBuyOrRevaluationPrice.FinalBuyOrRevaluationPrice,0) + IsNull(TblExpansionOrOverhaul.FinalExpansionOrOverhaul,0),
			ToEffectiveDateBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul = IsNull(TblBuyOrRevaluationPrice.ToEffectiveDateBuyOrRevaluationPrice,0) + IsNull(TblExpansionOrOverhaul.ToEffectiveDateExpansionOrOverhaul,0),
			FinalBookValue = IsNull(TblBuyOrRevaluationPrice.FinalBuyOrRevaluationPrice,0) - IsNull(TblAccumulatedDepreciation.FinalAccumulatedDepreciation,0) + IsNull(TblExpansionOrOverhaul.FinalExpansionOrOverhaul,0),
			ToEffectiveDateBookValue = IsNull(TblBuyOrRevaluationPrice.ToEffectiveDateBuyOrRevaluationPrice,0) - IsNull(TblAccumulatedDepreciation.ToEffectiveDateAccumulatedDepreciation,0) + IsNull(TblExpansionOrOverhaul.ToEffectiveDateExpansionOrOverhaul,0),
			FinalAccumulatedDepreciation = IsNull(TblAccumulatedDepreciation.FinalAccumulatedDepreciation,0),
			ToEffectiveDateAccumulatedDepreciation = IsNull(TblAccumulatedDepreciation.ToEffectiveDateAccumulatedDepreciation,0),
			AssetDepreciation = Round((
				Case When Asset.DepreciationType = 1 /* روش مستقیم */ Then
						((IsNull(TblBuyOrRevaluationPrice.ToEffectiveDateBuyOrRevaluationPrice, 0) + IsNull(TblExpansionOrOverhaul.ToEffectiveDateExpansionOrOverhaul,0) - IsNull(TblResidualValue.ToEffectiveDateResidualValue,0)) / Asset.DepreciationValue)

					 When Asset.DepreciationType = 2 /* روش نزولـی */ Then 
						((IsNull(TblBuyOrRevaluationPrice.ToEffectiveDateBuyOrRevaluationPrice,0) + IsNull(TblExpansionOrOverhaul.ToEffectiveDateExpansionOrOverhaul,0) - IsNull(TblAccumulatedDepreciation.ToEffectiveDateAccumulatedDepreciation,0)) * Asset.DepreciationValue) / 100.0000000000000000

					 When Asset.DepreciationType = 3 /* بــدون روش */ Then
						IsNull(TblManualDepreciation.ManualDepreciation, 0)
				End
				*
				Case When Asset.DepreciationType = 1 Or Asset.DepreciationType = 2 /* روش مستقیم یا روش نزولـی */ Then
						(dbo.DiffDate(IsNull(TblLatestEffectiveDate.LatestEffectiveDate, Asset.OperationDate), IsNull(@EffectiveDate, dbo.Solar_Date_Converter(getdate()))) / 365.0000000000000000)
				Else 1 End),0)
			From 										   
				AST.Asset Left Join

				(Select 
					AssetDocDetail.AssetRef As AssetSerial,
					BuyUnitPrice = Sum(Case When AssetDocKindRef = 310 Then AssetDocDetail.BuyUnitPrice 
											When AssetDocKindRef = 315 Then AssetDocDetail.AccumulatedDepreciation + AssetDocDetail.BookValue --> بهاءتمام شده در سند استقرار برابراست با مجموع استهلاک انباشته و ارزش دفتری
											When AssetDocKindRef = 325 Then AssetDocDetail.BuyUnitPrice 
									   Else 0 End) 
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325) And
					AssetDocHeader.VerificationDate Is Not Null
				 Group By AssetDocDetail.AssetRef) As TblBuyUnitPrice On TblBuyUnitPrice.AssetSerial = Asset.Serial Left Join

				(Select 
					TblAst1.Serial As AssetSerial,
					FinalBuyOrRevaluationPrice = (Select 
											     	   FinalBuyOrRevaluationPrice = Case When AssetDocKindRef = 310 Then AssetDocDetail.BuyUnitPrice 
											     								    	 When AssetDocKindRef = 315 Then AssetDocDetail.AccumulatedDepreciation + AssetDocDetail.BookValue --> بهاءتمام شده در سند استقرار برابراست با مجموع استهلاک انباشته و ارزش دفتری
											     								    	 When AssetDocKindRef = 325 Then AssetDocDetail.BuyUnitPrice 
											     								    	 When AssetDocKindRef = 330 Then AssetDocDetail.BookValue 
											     								    	 When AssetDocKindRef = 355 Then (AssetDocDetail.OldBookValue - AssetDocDetail.BookValue) + (AssetDocDetail.OldAccumulatedDepreciation - AssetDocDetail.AccumulatedDepreciation)--> در سند تجزیه و خروج، بهاءتمام شده جدید برابر است با مانده ارزش دفتری بعلاوه مانده استهلاک انباشته
											     								    Else 0 End 
											      From 							    
													   AST.AssetDocHeader Inner Join
											     	   AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
											      Where
											     	   AssetDocDetail.AssetRef = TblAst1.Serial And
											     	   AssetDocHeader.Serial = (Select
											     	 								Max(AssetDocHeader.Serial)
											     							    From 
											          						  		AST.AssetDocHeader Inner Join
											          						  		AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
											     							    Where
											     							  		(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 355) And 
											          						  		AssetDocHeader.VerificationDate Is Not Null And 
											          						  		AssetDocDetail.AssetRef = TblAst1.Serial And
											     							  		(Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End) = 
																							   (Select 
											     							  	 					Max((Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End)) 
											     							  	 				From 
											         						  	 					AST.AssetDocHeader Inner Join
											         						  	 					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
											     							  	 				Where
											     							  	 					(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 355) And 
											         						  	 					AssetDocHeader.VerificationDate Is Not Null And 
											         						  	 					AssetDocDetail.AssetRef = TblAst1.Serial))),

					ToEffectiveDateBuyOrRevaluationPrice =  (Select 
											     				ToEffectiveDateBuyOrRevaluationPrice = Case When AssetDocKindRef = 310 Then AssetDocDetail.BuyUnitPrice 
														     									       	    When AssetDocKindRef = 315 Then AssetDocDetail.AccumulatedDepreciation + AssetDocDetail.BookValue --> بهاءتمام شده در سند استقرار برابراست با مجموع استهلاک انباشته و ارزش دفتری
														     									       	    When AssetDocKindRef = 325 Then AssetDocDetail.BuyUnitPrice 
														     									       	    When AssetDocKindRef = 330 Then AssetDocDetail.BookValue 
											     								    						When AssetDocKindRef = 355 Then (AssetDocDetail.OldBookValue - AssetDocDetail.BookValue) + (AssetDocDetail.OldAccumulatedDepreciation - AssetDocDetail.AccumulatedDepreciation)--> در سند تجزیه و خروج، بهاءتمام شده جدید برابر است با مانده ارزش دفتری بعلاوه مانده استهلاک انباشته
														     									       Else 0 End 
														      From 							    
																   AST.AssetDocHeader Inner Join
														     	   AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
														      Where
														     	   AssetDocDetail.AssetRef = TblAst1.Serial And
														     	   AssetDocHeader.Serial = (Select
														     	 								Max(AssetDocHeader.Serial)
														     							    From 
														          						  		AST.AssetDocHeader Inner Join
														          						  		AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
														     							    Where
														     							  		(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 355) And 
														          						  		AssetDocHeader.VerificationDate Is Not Null And 
														          						  		AssetDocDetail.AssetRef = TblAst1.Serial And
														     							  		(Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End) = 
																										   (Select 
														     							  	 					Max((Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End)) 
														     							  	 				From 
														         						  	 					AST.AssetDocHeader Inner Join
														         						  	 					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
														     							  	 				Where
														     							  	 					(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 355) And 
														     							  	 					((Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End) <= @EffectiveDate) And
														         						  	 					AssetDocHeader.VerificationDate Is Not Null And 
														         						  	 					AssetDocDetail.AssetRef = TblAst1.Serial)))
				 From 																	    
					AST.Asset As TblAst1									    
				 Where 
					@AssetSerial = 0 Or @AssetSerial Is Null Or TblAst1.Serial = @AssetSerial) As TblBuyOrRevaluationPrice On TblBuyOrRevaluationPrice.AssetSerial = Asset.Serial Left Join

				(Select 
					AssetDocDetail.AssetRef As AssetSerial,
					FinalExpansionOrOverhaul = Sum(AssetDocDetail.ExpansionOrOverhaul), 
					ToEffectiveDateExpansionOrOverhaul = Sum(Case When (ExpansionOrOverhaulDate < @EffectiveDate Or (ExpansionOrOverhaulDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then AssetDocDetail.ExpansionOrOverhaul Else 0 End) 
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					(AssetDocHeader.AssetDocKindRef = 350) And
					AssetDocHeader.VerificationDate Is Not Null And
					AssetDocDetail.ExpansionOrOverhaulDate >= IsNull((Select 
																			Max((Case When ADH22.AssetDocKindRef = 355 Then ADD22.TakeOutDate Else ADH22.EffectiveDate End)) 
																		 From 
																			AST.AssetDocHeader As ADH22 Inner Join
																			AST.AssetDocDetail As ADD22 On ADH22.Serial = ADD22.AssetDocHeaderRef
																		 Where
																			(ADH22.AssetDocKindRef = 330 Or ADH22.AssetDocKindRef = 355) And 
																			ADH22.VerificationDate Is Not Null And 
																			ADD22.AssetRef = AssetDocDetail.AssetRef),'')
				 Group By AssetDocDetail.AssetRef) As TblExpansionOrOverhaul On TblExpansionOrOverhaul.AssetSerial = Asset.Serial Left Join
				(Select 
					AssetDocDetail.AssetRef As AssetSerial,

					ToEffectiveDateAccumulatedDepreciation = Sum(Case When (AssetDocKindRef = 315) And (BuyDate < @EffectiveDate Or (BuyDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then (AssetDocDetail.AccumulatedDepreciation) 
																      When (AssetDocKindRef = 360) And (EffectiveDate < @EffectiveDate Or (EffectiveDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then (AssetDocDetail.Depreciation)
										                              When (AssetDocKindRef = 330) And (EffectiveDate < @EffectiveDate Or (EffectiveDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then -1 * AssetDocDetail.AccumulatedDepreciation 
										                              When (AssetDocKindRef = 355) And (TakeOutDate < @EffectiveDate Or (TakeOutDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then -1 * AssetDocDetail.AccumulatedDepreciation 
										                              When (AssetDocKindRef = 380) And (TakeOutDate < @EffectiveDate Or (TakeOutDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then -1 * AssetDocDetail.AccumulatedDepreciation 
													                  Else 0 End),

					FinalAccumulatedDepreciation = Sum(Case When (AssetDocKindRef = 315) Then (AssetDocDetail.AccumulatedDepreciation) 
															When (AssetDocKindRef = 360) Then (AssetDocDetail.Depreciation)
										                    When (AssetDocKindRef = 330) Then -1 * AssetDocDetail.AccumulatedDepreciation 
										                    When (AssetDocKindRef = 355) Then -1 * AssetDocDetail.AccumulatedDepreciation
										                    When (AssetDocKindRef = 380) Then -1 * AssetDocDetail.AccumulatedDepreciation 
													        Else 0 End)
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					(AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 355 Or AssetDocHeader.AssetDocKindRef = 360 Or AssetDocHeader.AssetDocKindRef = 380) And
					AssetDocHeader.VerificationDate Is Not Null				
				 Group By AssetDocDetail.AssetRef) As TblAccumulatedDepreciation On TblAccumulatedDepreciation.AssetSerial = Asset.Serial Left Join
				(Select
					AssetDocDetail.AssetRef As AssetSerial,
					LatestEffectiveDate = Max(EffectiveDate)
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					AssetDocHeader.AssetDocKindRef = 360 And 
					(EffectiveDate <= @EffectiveDate) And
					AssetDocHeader.VerificationDate Is Not Null And
					AssetDocHeader.Serial < @AssetDocHeaderSerial
				 Group By AssetDocDetail.AssetRef) As TblLatestEffectiveDate On TblLatestEffectiveDate.AssetSerial = Asset.Serial Left Join
				(Select
					AssetDocDetail.AssetRef As AssetSerial,
					FinalResidualDocSerial = Max(AssetDocHeader.Serial),
					ToEffectiveDateResidualDocSerial = Max(Case When (EffectiveDate < @EffectiveDate Or (EffectiveDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then AssetDocHeader.Serial Else Null End) 
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					AssetDocHeader.AssetDocKindRef = 370 And 
					AssetDocHeader.VerificationDate Is Not Null
				 Group By AssetDocDetail.AssetRef) As TblResidualDoc On TblResidualDoc.AssetSerial = Asset.Serial Left Join
				(Select
					AssetDocDetail.AssetRef As AssetSerial,
					FinalTakeOutDocSerial = Max(AssetDocHeader.Serial),
					ToEffectiveDateTakeOutDocSerial = Max(Case When (TakeOutDate < @EffectiveDate Or (TakeOutDate = @EffectiveDate And AssetDocHeader.Serial < @AssetDocHeaderSerial)) Then AssetDocHeader.Serial Else Null End) 
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					AssetDocHeader.AssetDocKindRef = 380 And 
					AssetDocHeader.VerificationDate Is Not Null
				 Group By AssetDocDetail.AssetRef) As TblTakeOutDoc On TblTakeOutDoc.AssetSerial = Asset.Serial Left Join
				(Select
					AssetDocHeader.VerificationDate,
					AssetDocDetail.AssetRef As AssetSerial,
					ManualDepreciation = Sum(AssetDocDetail.Depreciation)
				 From 
					AST.AssetDocHeader Inner Join
					AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
				 Where 
					(@AssetSerial = 0 Or @AssetSerial Is Null Or AssetDocDetail.AssetRef = @AssetSerial) And 
					AssetDocHeader.Serial = @AssetDocHeaderSerial And
					AssetDocKindRef = 360
				 Group By
					AssetDocHeader.VerificationDate, 
					AssetDocDetail.AssetRef) As TblManualDepreciation On TblManualDepreciation.AssetSerial = Asset.Serial Left Join
				(Select
					TblAst2.Serial As AssetSerial,
					FinalResidualValue = (Select 
										AssetDocDetail.ResidualValue
									 From 
										AST.AssetDocHeader Inner Join
										AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
									 Where
										AssetDocDetail.AssetRef = TblAst2.Serial And
										AssetDocHeader.Serial = (Select
										 							Max(AssetDocHeader.Serial)
																 From 
									     							AST.AssetDocHeader Inner Join
									     							AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
																 Where
																	(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 350 Or AssetDocHeader.AssetDocKindRef = 355) And 
									     							AssetDocHeader.VerificationDate Is Not Null And 
									     							AssetDocDetail.AssetRef = TblAst2.Serial And
																	(Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End) = 
																			   (Select 
																					Max((Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End)) 
																				From 
									    											AST.AssetDocHeader Inner Join
									    											AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
																				Where
																					(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 350 Or AssetDocHeader.AssetDocKindRef = 355) And 
									    											AssetDocHeader.VerificationDate Is Not Null And 
									    											AssetDocDetail.AssetRef = TblAst2.Serial))),
					ToEffectiveDateResidualValue = (Select 
										AssetDocDetail.ResidualValue
									 From 
										AST.AssetDocHeader Inner Join
										AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
									 Where
										AssetDocDetail.AssetRef = TblAst2.Serial And
										AssetDocHeader.Serial = (Select
										 							Max(AssetDocHeader.Serial)
																 From 
									     							AST.AssetDocHeader Inner Join
									     							AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
																 Where
																	(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 350 Or AssetDocHeader.AssetDocKindRef = 355) And 
									     							AssetDocHeader.VerificationDate Is Not Null And 
									     							AssetDocDetail.AssetRef = TblAst2.Serial And
																	(Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End) = 
																			   (Select 
																					Max((Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End)) 
																				From 
									    											AST.AssetDocHeader Inner Join
									    											AST.AssetDocDetail On AssetDocHeader.Serial = AssetDocDetail.AssetDocHeaderRef
																				Where
																					(AssetDocHeader.AssetDocKindRef = 310 Or AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Or AssetDocHeader.AssetDocKindRef = 330 Or AssetDocHeader.AssetDocKindRef = 350 Or AssetDocHeader.AssetDocKindRef = 355) And 
																					((Case When AssetDocHeader.AssetDocKindRef = 315 Or AssetDocHeader.AssetDocKindRef = 325 Then AssetDocDetail.BuyDate When AssetDocHeader.AssetDocKindRef = 350 Then AssetDocDetail.ExpansionOrOverhaulDate When AssetDocHeader.AssetDocKindRef = 380 Or AssetDocHeader.AssetDocKindRef = 355 Then AssetDocDetail.TakeOutDate Else AssetDocHeader.EffectiveDate End) <= @EffectiveDate) And
									    											AssetDocHeader.VerificationDate Is Not Null And 
									    											AssetDocDetail.AssetRef = TblAst2.Serial)))
				 From 
					AST.Asset As TblAst2
				 Where 
					@AssetSerial = 0 Or @AssetSerial Is Null Or TblAst2.Serial = @AssetSerial) As TblResidualValue On TblResidualValue.AssetSerial = Asset.Serial

			Where
				@AssetSerial = Asset.Serial Or
				((@AssetSerial = 0 Or @AssetSerial Is Null) And Asset.Serial In (Select AssetRef From AST.AssetDocDetail Where AssetDocHeaderRef = @AssetDocHeaderSerial))) As ABC
	Return
End
Go
/*====================================================================================================================================*/
If (Select Count(*) From AST.Asset) > 0
Begin
	Update 
		AST.Asset
	Set
		NetValue = Case When AssetDocHeaderAndDetailList.AssetDocKindSerial = 310 Or
								AssetDocHeaderAndDetailList.AssetDocKindSerial = 315 Or
								AssetDocHeaderAndDetailList.AssetDocKindSerial = 325 Or
								AssetDocHeaderAndDetailList.AssetDocKindSerial = 330 Or
								AssetDocHeaderAndDetailList.AssetDocKindSerial = 350 Or
								AssetDocHeaderAndDetailList.AssetDocKindSerial = 355 Or
								AssetDocHeaderAndDetailList.AssetDocKindSerial = 380 Then TblDepreciation.FinalBuyOrRevaluationPrice_Plus_ExpansionOrOverhaul 
					Else Asset.NetValue End
	From
		AST.Asset Inner Join 
		AST.AssetDocHeaderAndDetailList On AssetDocHeaderAndDetailList.AssetSerial = Asset.Serial Cross Apply 
		AST.CalculateAssetDepreciation(AssetDocHeaderAndDetailList.AssetSerial, AssetDocHeaderAndDetailList.EffectiveDate, AssetDocHeaderAndDetailList.AssetDocHeaderSerial) As TblDepreciation 
	Where
		AssetDocHeaderAndDetailList.AssetDocKindSerial In(310,315,325,330,350,355,380)
End