Declare @Rundate date = (Select a.RunDate from dbo.vw_cor_solutionrundate a where a.Alias = 'MPR');

--Account Table mis update
Update mpr_balancesheet
set TeamCode = a.TeamCode,
AccountOfficerCode = a.AccountOfficerCode
from dbo.cor_cust_account a where a.AccountNo = mpr_balancesheet.AccountNo;


--Fix Team code of Account Officers
Update mpr_balancesheet
set TeamCode = a.ParentCode
from mpr_team a where a.Year = Year(@Rundate) and
 a.Code = mpr_balancesheet.AccountOfficerCode
 and a.CompanyCode = mpr_balancesheet.companycode;



--Product mis update
Update mpr_balancesheet
set TeamCode = a.TeamCode,
AccountOfficerCode = a.accountofficercode
from dbo.vw_mpr_productmis a where a.ProductCode = mpr_balancesheet.ProductCode
and a.CaptionName = mpr_balancesheet.CaptionName;

--Branch Update
Update mpr_balancesheet
set TeamCode = a.MisCode,
AccountOfficerCode = 'n/a'
from dbo.mpr_branchdefaultmis a where a.BranchCode = mpr_balancesheet.BranchCode
and mpr_balancesheet.VolumeGL in (select b.ProductCode from mpr_product b)
and a.Deleted = 0 and a.Active = 1;

--Fintrak Account MIS
Update mpr_balancesheet
set TeamCode = a.TeamCode,
AccountOfficerCode = a.AccountOfficerCode
from dbo.mpr_accountmis a where
a.accountno = mpr_balancesheet.AccountNo and a.Deleted = 0 and a.Active = 1;

declare
rundate date;
begin
Select a.RunDate into rundate from vw_cor_solutionrundate a where a.Alias = 'MPR';

--Account Table mis update
update mpr_balancesheet a
set (TeamCode,AccountOfficerCode) =
(select b.TeamCode,b.AccountOfficerCode
 from cor_cust_account b 
 where b.AccountNo = a.AccountNo)
where exists
(select null
 from cor_cust_account b 
 where b.AccountNo = a.AccountNo);



--Fix Team code of Account Officers
Update mpr_balancesheet a
set a.TeamCode = 
(select b.ParentCode
 from mpr_team b
 where b.Year = to_number(to_char(Rundate,'YYYY')) 
 and B.Code = A.AccountOfficerCode
 and B.CompanyCode = A.companycode)
WHERE EXISTS
(select NULL
 from mpr_team b
 where b.Year = to_number(to_char(Rundate,'YYYY')) 
 and B.Code = A.AccountOfficerCode
 and B.CompanyCode = A.companycode);

end;
/