EAS800_fi_arap_update_balancedata----------------------------------------------------应收余额数据升级-----------------------------------------------------------------删除原来余额类型为70、80的余额记录DeletefromT_AR_ArBalancewhereFBalTypeIn(70,80);--更新应收余额中类型为60的余额为坏账余额ifexists(select1fromksql_usertableswhereksql_tabname='T_AP_UPDATEDATA_BAL001')droptableT_AP_UPDATEDATA_BAL001;--性能考虑，将需要更新的数据放到临时表T_AP_UPDATEDATA_BAL001SELECTdistinctA.FIDFID,B.FBeginBalanceFor-A.FBeginBalanceForFBeginBalanceFor,B.FDebitFor-A.FDebitForFDebitFor,B.FCreditFor-A.FCreditForFCreditFor,B.FYearDebitFor-A.FYearDebitForFYearDebitFor,B.FYearCreditFor-A.FYearCreditForFYearCreditFor,B.FEndBalanceFor-A.FEndBalanceForFEndBalanceFor,B.FBeginBalanceLocal-A.FBeginBalanceLocalFBeginBalanceLocal,B.FDebitLocal-A.FDebitLocalFDebitLocal,B.FCreditLocal-A.FCreditLocalFCreditLocal,B.FYearDebitLocal-A.FYearDebitLocalFYearDebitLocal,B.FYearCreditLocal-A.FYearCreditLocalFYearCreditLocal,B.FEndBalanceLocal-A.FEndBalanceLocalFEndBalanceLocalINTOT_AP_UPDATEDATA_BAL001FROMT_AR_ArBalanceBINNERJOINT_AR_ArBalanceAONB.FCompanyID=A.FCompanyIDANDB.FPeriodID=A.FPeriodIDANDB.FCurrencyID=A.FCurrencyIDANDB.FAsstActTypeID=A.FAsstActTypeIDANDB.FAsstActID=A.FAsstActIDANDB.FAdminOrgUnitID=A.FAdminOrgUnitIDANDB.FPersonID=A.FPersonIDWHEREA.FBalType=60ANDB.FBalType=50;--性能考虑，临时表T_AP_UPDATEDATA_BAL001创建唯一索引createuniqueindexcix_ap_bal001_idonT_AP_UPDATEDATA_BAL001(fid);--性能考虑，将临时表T_AP_UPDATEDATA_BAL001数据更新到余额表UPDATET_AR_ArBalanceSET(FBeginBalanceFor,FDebitFor,FCreditFor,FYearDebitFor,FYearCreditFor,FEndBalanceFor,FBeginBalanceLocal,FDebitLocal,FCreditLocal,FYearDebitLocal,FYearCreditLocal,FEndBalanceLocal)=(SELECTA.FBeginBalanceFor,A.FDebitFor,A.FCreditFor,A.FYearDebitFor,A.FYearCreditFor,A.FEndBalanceFor,A.FBeginBalanceLocal,A.FDebitLocal,A.FCreditLocal,A.FYearDebitLocal,A.FYearCreditLocal,A.FEndBalanceLocalFROMT_AP_UPDATEDATA_BAL001asAWHEREA.fid=T_AR_ArBalance.fid);--根据往来户类型和往来户ID更新业务分析码updateT_AR_ArBalanceasAset(FBizAnalysisCodeID)=(selectB.FBizAnalysisCodeIDfromT_BD_CustomerBwhereB.FID=A.FAsstActIDandA.FAsstActTypeID='YW3xsAEJEADgAAUWwKgTB0c4VZA=');updateT_AR_ArBalanceasAset(FBizAnalysisCodeID)=(selectB.FBizAnalysisCodeIDfromT_BD_SupplierBwhereB.FID=A.FAsstActIDandA.FAsstActTypeID='YW3xsAEJEADgAAVEwKgTB0c4VZA=');--更新应收余额：类型50为101updateT_AR_ArBalancesetFBalType=101whereFBalType=50;--更新应收余额：类型60为104updateT_AR_ArBalancesetFBalType=104whereFBalType=60;--更新应收余额：类型90为102updateT_AR_ArBalancesetFBalType=102whereFBalType=90;--更新应收余额：类型100为103updateT_AR_ArBalancesetFBalType=103whereFBalType=100;----------------------------------------------------应付余额数据升级-----------------------------------------------------------------删除原来余额类型为70的余额记录DeletefromT_AP_ApBalancewhereFBalType=70;--根据往来户类型和往来户ID更新业务分析码updateT_AP_ApBalanceasAset(FBizAnalysisCodeID)=(selectB.FBizAnalysisCodeIDfromT_BD_CustomerBwhereB.FID=A.FAsstActIDandA.FAsstActTypeID='YW3xsAEJEADgAAUWwKgTB0c4VZA=');updateT_AP_ApBalanceasAset(FBizAnalysisCodeID)=(selectB.FBizAnalysisCodeIDfromT_BD_SupplierBwhereB.FID=A.FAsstActIDandA.FAsstActTypeID='YW3xsAEJEADgAAVEwKgTB0c4VZA=');--更新应付余额：类型50为101updateT_AP_ApBalancesetFBalType=101whereFBalType=50;--更新应付余额：类型90为102updateT_AP_ApBalancesetFBalType=102whereFBalType=90;--更新应付余额：类型100为103updateT_AP_ApBalancesetFBalType=103whereFBalType=100;
