Version 1

declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20);                                           
select @budgetIdmax=max(budget_id) from budget;   
set @curBudgetId=1;   
while @curBudgetId<=@budgetIdmax   
begin  

INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS) 
(SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS   
FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID   
        FROM EFFORT_ESTIMATION) ee INNER JOIN  
      BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID   
WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR  
      (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId)  ;

select top 1 @FYbudgetStatus=BUDGET_plan_STATUS  from budget where budget_id=@curBudgetId;
INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) 
((select distinct fk_fiscalyear_id from EFFORT_ESTIMATION where fk_budget_id=@curBudgetId ) ee 
cross join (SELECT DISTINCT BUDGET_ID,BUDGET_PLAN_STATUS FROM BUDGET WHERE RECORD_FY_ID>0 AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) b)

set @curBudgetId=@curBudgetId+1;            
end 

?

Version 2:

declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20);                                              
select @budgetIdmax=max(budget_id) from budget;      
set @curBudgetId=1;      
while @curBudgetId<=@budgetIdmax      
begin     
  
INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS)    
(SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS      
FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID      
        FROM EFFORT_ESTIMATION) ee INNER JOIN     
      BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID      
WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR     
      (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId)  ;   
  
select top 1 @FYbudgetStatus=BUDGET_plan_STATUS  from budget where budget_id=@curBudgetId;   
INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS)    

(select b.record_fy_id,b.budget_id,b.record_budget_status from 
(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee    
inner join 
(SELECT DISTINCT BUDGET_ID,record_fy_id,record_budget_status FROM BUDGET
 WHERE RECORD_FY_ID>0 AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) b on b.record_fy_id=ee.fk_fiscalYear_id and ee.fk_budget_id=b.budget_id)   
  
set @curBudgetId=@curBudgetId+1;               
end 

?

not necessary code:

UPDATE BUDGET
SET RECORD_FY_ID = 0
WHERE (IS_IN_PROCESS = 1) AND (BUDGET_PLAN_STATUS <> 'APPROVED') AND 
      (BUDGET_PLAN_STATUS <> 'ALIGNED')

?

Version 3:

declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20);                                              
select @budgetIdmax=max(budget_id) from budget;      
set @curBudgetId=96;      
   
  
INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS)    
(SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS      
FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID      
        FROM EFFORT_ESTIMATION) ee INNER JOIN     
      BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID      
WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR     
      (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')or budget.is_in_process=1)  and ee.FK_BUDGET_ID=@curBudgetId)  ;