应付余额报表 create table #APDirectTmp (ID bigint, APStatementDirect int) -- 付款单据类型 insert into #APDirectTmp select A.[ID], A.[APStatementDirect] from AP_PayDocType as A create table #AccrueSupplierTmp (AccrueSupplierSite_ID bigint, Org_IsAuditOrg bit, Org_IsOperatingOrg bit, AccrueSupplier_ID bigint, AccrueSupplierSite_Territory_CombineName nvarchar(255), AccrueSupplier_CombineName nvarchar(255), AccrueSupplier_Territory_CombineName nvarchar(255), AccrueSupplier_Category_CombineName nvarchar(255), AccrueSupplier_Category_ID bigint, AccrueSupplier_Territory_ID bigint, AccrueSupplierSite_Territory_ID bigint) insert into #AccrueSupplierTmp select A.[ID] as [AccrueSupplierSite_ID], --供应商位置ID A2.[IsAuditOrg] as [Org_IsAuditOrg], --核算组织 A2.[IsOperatingOrg] as [Org_IsOperatingOrg], -- 营运组织 A1.[ID] as [AccrueSupplier_ID], -- 供应商ID A6.[CombineName] as [AccrueSupplierSite_Territory_CombineName],--地区名称 A7.[CombineName] as [AccrueSupplier_CombineName], --供应商名称 A8.[CombineName] as [AccrueSupplier_Territory_CombineName], --地区名称 A9.[CombineName] as [AccrueSupplier_Category_CombineName], --供应商分类名称 A3.[ID] as [AccrueSupplier_Category_ID], --供应商ID A4.[ID] as [AccrueSupplier_Territory_ID], --地区ID A5.[ID] as [AccrueSupplierSite_Territory_ID] --地区ID from CBO_SupplierSite as A -- 供应商位置 left join [CBO_Supplier] as A1 on (A.[Supplier] = A1.[ID]) -- 供应商 left join [Base_Organization] as A2 on (A1.[Org] = A2.[ID]) -- 组织 left join [CBO_SupplierCategory] as A3 on (A1.[Category] = A3.[ID]) -- 供应商分类 left join [Base_Territory] as A4 on (A1.[Territory] = A4.[ID]) -- 地区 left join [Base_Territory] as A5 on (A.[Territory] = A5.[ID]) -- 地区 left join [Base_Territory_Trl] as A6 on (A6.SysMlFlag = 'zh-CN') and (A5.[ID] = A6.[ID]) --地区TRl left join [CBO_Supplier_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A1.[ID] = A7.[ID]) -- 供应商TRL left join [Base_Territory_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and (A4.[ID] = A8.[ID]) --地区TRl left join [CBO_SupplierCategory_Trl] as A9 on (A9.SysMlFlag = 'zh-CN') and (A3.[ID] = A9.[ID]) --供应商分类TRL where (1 = 1) create table #PaySupplierTmp (PaySupplierSite_ID bigint, Org_IsAuditOrg bit, Org_IsOperatingOrg bit, PaySupplier_Code nvarchar(255), PaySupplier_ID bigint, PaySupplier_Name nvarchar(255), PaySupplierSite_Territory_CombineName nvarchar(255), PaySupplier_CombineName nvarchar(255), PaySupplier_Territory_CombineName nvarchar(255), PaySupplier_Category_CombineName nvarchar(255), PaySupplier_Category_ID bigint, PaySupplier_Territory_ID bigint, PaySupplierSite_Territory_ID bigint) insert into #PaySupplierTmp select A.[ID] as [PaySupplierSite_ID], --供应商位ID A2.[IsAuditOrg] as [Org_IsAuditOrg], --核算组织 A2.[IsOperatingOrg] as [Org_IsOperatingOrg], -- 营运组织 A1.[Code] as [PaySupplier_Code], --供应商编码 A1.[ID] as [PaySupplier_ID], --供应商ID A6.[Name] as [PaySupplier_Name], -- 供应商名称 A7.[CombineName] as [PaySupplierSite_Territory_CombineName], --地区名称 A6.[CombineName] as [PaySupplier_CombineName], -- 供应商名称 A8.[CombineName] as [PaySupplier_Territory_CombineName],--地区名称 A9.[CombineName] as [PaySupplier_Category_CombineName], --供应商分类名称 A3.[ID] as [PaySupplier_Category_ID], -- 供应商分类ID A4.[ID] as [PaySupplier_Territory_ID], -- 地区ID A5.[ID] as [PaySupplierSite_Territory_ID] -- 地区ID from CBO_SupplierSite as A --供应商位置 left join [CBO_Supplier] as A1 on (A.[Supplier] = A1.[ID]) -- 供应商 left join [Base_Organization] as A2 on (A1.[Org] = A2.[ID]) -- 组织 left join [CBO_SupplierCategory] as A3 on (A1.[Category] = A3.[ID]) -- 供应商分类 left join [Base_Territory] as A4 on (A1.[Territory] = A4.[ID]) -- 地区 left join [Base_Territory] as A5 on (A.[Territory] = A5.[ID]) -- 地区 left join [CBO_Supplier_Trl] as A6 on (A6.SysMlFlag = 'zh-CN') and (A1.[ID] = A6.[ID]) left join [Base_Territory_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A5.[ID] = A7.[ID]) left join [Base_Territory_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and (A4.[ID] = A8.[ID]) left join [CBO_SupplierCategory_Trl] as A9 on (A9.SysMlFlag = 'zh-CN') and (A3.[ID] = A9.[ID]) where ((1 = 1) and (A1.[Code] = N'1.006')) select A.[ID], A.[Code], A1.[Name], A.[APStatementDirect], '1' as [BillCategory] into #APDirectTempTable from AP_APDocType as A left join [AP_APDocType_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID]) insert into #APDirectTempTable select A.[ID], -- 付款单据类型ID A.[Code], -- 付款单据类型编码 A1.[Name], -- 付款单据类型名称 A.[APStatementDirect], --应付对账单方向 '0' as [BillCategory] from AR_ARDocType as A -- 付款单据类型 left join [AR_ARDocType_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID])-- 付款单据类型TRL create table #TempMXHZ (AccrueSupplierSite_ID bigint, PaySupplierSite_ID bigint, Dept_TreeDisplayName nvarchar(2000), Item_ID bigint, BC_Code nvarchar(50), BC_Name nvarchar(50), AP_BusinessType nvarchar(50), AP_DocumentType bigint, DocType_Code nvarchar(50), DocType_Name nvarchar(50), AccrueSupplier_CombineName nvarchar(255), AccrueSupplierSite_Territory_CombineName nvarchar(255), AccrueSupplier_Category_CombineName nvarchar(255), AccrueSupplier_Territory_CombineName nvarchar(255), PaySupplier_CombineName nvarchar(255), PaySupplierSite_Territory_CombineName nvarchar(255), PaySupplier_Category_CombineName nvarchar(255), PaySupplier_Territory_CombineName nvarchar(255), PaySupplier_Code nvarchar(255), PaySupplier_Name nvarchar(255), DescFlexField_CombineName nvarchar(4000), QCAmount decimal(24,9), QCACMoney decimal(24,9), QCFCMoney decimal(24,9), ARAmount decimal(24,9), ARACMoney decimal(24,9), ARFCMoney decimal(24,9), RecoverAmount decimal(24,9), RecoverACMoney decimal(24,9), RecoverFCMoney decimal(24,9), QMAmount decimal(24,9), QMACMoney decimal(24,9), QMFCMoney decimal(24,9), BCMoneyRound int, FCMoneyRound int, UOMMoneyRound int, BC_Symbol nvarchar(50), FC_Symbol nvarchar(50), PUom_Code nvarchar(50), PUom_Name nvarchar(50)) insert into #TempMXHZ select A.[AccrueSuppSite_SupplierSite] as [AccrueSupplierSite_ID], --供应商位置ID A.[PaySuppSite_SupplierSite] as [PaySupplierSite_ID], -- 付款供应商位置ID A18.[TreeDisplayName] as [Dept_TreeDisplayName], -- 部门名称 A.[Item_ItemID] as [Item_ID], -- 料品ID A10.[Code] as [BC_Code], -- 币种编码 A19.[Name] as [BC_Name], --单据币种名称 dbo.F_GetEnumName('UFIDA.U9.Base.Doc.BusinessTypeEnum',A.[BusinessType],'zh-CN') as [AP_BusinessType], --业务类型 A.[DocumentType] as [AP_DocumentType], -- 应付账已记账明细单据类型 A11.Code as [DocType_Code], -- 付款单据类型编码 A11.Name as [DocType_Name], -- -- 付款单据类型名称 null as [AccrueSupplier_CombineName], --应计供应商组合名称？ null as [AccrueSupplierSite_Territory_CombineName], --应计供应商位置组合名称？ null as [AccrueSupplier_Category_CombineName], --应计供应商联系人组合名称？ null as [AccrueSupplier_Territory_CombineName], -- 应计供应商地区组合名称？ isnull(A13.PaySupplier_CombineName,'') as [PaySupplier_CombineName], -- 付款供应商名称 isnull(A13.PaySupplierSite_Territory_CombineName,'') as [PaySupplierSite_Territory_CombineName], --付款供应商位置名称？ isnull(A13.PaySupplier_Category_CombineName,'') as [PaySupplier_Category_CombineName], --付款供应商联系人组合名称？ isnull(A13.PaySupplier_Territory_CombineName,'') as [PaySupplier_Territory_CombineName], -- 付款供应商地区组合名称？ A13.PaySupplier_Code as [PaySupplier_Code], --付款供应商编码 A13.PaySupplier_Name as [PaySupplier_Name], --付款供应商名称 null as [DescFlexField_CombineName], (A.[CRPUAmount] - A.[DRPUAmount]) as [QCAmount], --计价单位数量 - 计价单位数量 (A.[CROCMoney_TotalMoney] - A.[DROCMoney_TotalMoney]) as [QCACMoney], -- 核币总金额差额 (A.[CRFCMoney_TotalMoney] - A.[DRFCMoney_TotalMoney]) as [QCFCMoney], -- 本币总金额差额 0 as [ARAmount], 0 as [ARACMoney], 0 as [ARFCmoney], 0 as [RecoverAmount], 0 as [RecoverACMoney], 0 as [RecoverFCmoney], 0 as [QMAmount], 0 as [QMACMoney], 0 as [QMFCmoney], A16.[MoneyRound_Precision] as [BCMoneyRound], -- 单据币种金额精度 A17.[MoneyRound_Precision] as [FCMoneyRound], -- 本币币种金额精度 A15.[Round_Precision]as [UOMMoneyRound], -- 单位精度 A16.[Symbol] as [BC_Symbol], -- 单据币种货币符号 A17.[Symbol] as [FC_Symbol], --本币币种货币符号 A15.[Code] as [PUom_Code], -- 单位编码 A20.[Name] as [PUom_Name] -- 单位名称 from AP_APPostedDetail as A --应付账已记账明细 left join [CBO_Operators] as A1 on (A.[Transactor] = A1.[ID]) --业务员 left join [Base_Organization] as A2 on (A.[Org] = A2.[ID]) --组织 left join [Base_Organization] as A3 on (A.[SrcBusinessOrg] = A3.[ID]) --来源组织 left join [Base_Organization] as A4 on (A.[BizOrg] = A4.[ID]) --业务组织 left join [Base_Organization] as A5 on (A.[SettleOrg] = A5.[ID]) -- 结算组织 left join [CBO_Department] as A6 on (A.[Dept] = A6.[ID]) --部门 left join [CBO_Project] as A7 on (A.[Project] = A7.[ID]) --项目 left join [CBO_Task] as A8 on (A.[Task] = A8.[ID]) -- 任务 left join [Base_Currency] as A9 on (A.[FC] = A9.[ID]) --本币币种 left join [Base_Currency] as A10 on (A.[BC] = A10.[ID]) --单据币种 left join #APDirectTempTable as A11 on (A.[DocumentType] = A11.ID) -- 付款单据类型临时表 left join #AccrueSupplierTmp as A12 on (A.[AccrueSuppSite_SupplierSite] = A12.AccrueSupplierSite_ID) --供应商信息临时表 inner join #PaySupplierTmp as A13 on (A.[PaySuppSite_SupplierSite] = A13.PaySupplierSite_ID) -- 付款供应商信息临时表 left join Base_SOBAccountingPeriod as A14 on (A.[ProcessBillPeriod] = A14.[ID]) --账簿会计期间 left join Base_UOM as A15 on (A.[PUom] = A15.[ID]) --单位 left join Base_Currency as A16 on (A.[BC] = A16.[ID]) -- 单据币种 left join Base_Currency as A17 on (A.[FC] = A17.[ID]) -- 本币币种 left join [CBO_Department_Trl] as A18 on (A18.SysMlFlag = 'zh-CN') and (A6.[ID] = A18.[ID]) --部门TRL left join [Base_Currency_Trl] as A19 on (A19.SysMlFlag = 'zh-CN') and (A10.[ID] = A19.[ID]) -- 币种TRl left join [Base_UOM_Trl] as A20 on (A20.SysMlFlag = 'zh-CN') and (A15.[ID] = A20.[ID]) --单位TRL where (((((((1 = 1) and (A2.[Code] = N'10')) and (A.[PaySupp_Code] = N'1.006')) and (A.[IsSureAccrue] = 1)) and A.[DocStatus] in (2)) and (A14.[DisplayName] >= '2023-12')) and (A14.[DisplayName] <= '2024-12')) create table #TempMXCur (AccrueSupplierSite_ID bigint, PaySupplierSite_ID bigint, Dept_TreeDisplayName nvarchar(2000), Item_ID bigint, BC_Code nvarchar(50), BC_Name nvarchar(100), AP_BusinessType nvarchar(50), AP_DocumentType bigint, DocType_Code nvarchar(50), DocType_Name nvarchar(100), AccrueSupplier_CombineName nvarchar(255), AccrueSupplierSite_Territory_CombineName nvarchar(255), AccrueSupplier_Category_CombineName nvarchar(255), AccrueSupplier_Territory_CombineName nvarchar(255), PaySupplier_CombineName nvarchar(255), PaySupplierSite_Territory_CombineName nvarchar(255), PaySupplier_Category_CombineName nvarchar(255), PaySupplier_Territory_CombineName nvarchar(255), PaySupplier_Code nvarchar(255), PaySupplier_Name nvarchar(255), DescFlexField_CombineName nvarchar(4000), APStatementDirect int, OppAPStatementDirect int, CurAmount decimal(24,9), CurACMoney decimal(24,9), CurFCMoney decimal(24,9), BCMoneyRound int, FCMoneyRound int, UOMMoneyRound int, BC_Symbol nvarchar(50), FC_Symbol nvarchar(50), PUom_Code nvarchar(50), PUom_Name nvarchar(100)) create table #selectPeriod (ID bigint) --账簿会计期间ID insert into #selectPeriod select A.[ID] from Base_SOBAccountingPeriod as A where (((1 = 1) and (A.[DisplayName] >= '2025-01')) and (A.[DisplayName] <= '2025-01')) insert into #TempMXCur select A.[AccrueSuppSite_SupplierSite] as [AccrueSupplierSite_ID], --供应商位置ID A.[PaySuppSite_SupplierSite] as [PaySupplierSite_ID], -- 付款供应商位置ID A20.[TreeDisplayName] as [Dept_TreeDisplayName], -- 部门名称 A.[Item_ItemID] as [Item_ID], -- 料品ID A10.[Code] as [BC_Code], -- 单据币种编码 A21.[Name] as [BC_Name], -- 单据币种名称 dbo.F_GetEnumName('UFIDA.U9.Base.Doc.BusinessTypeEnum',A.[BusinessType],'zh-CN') as [AP_BusinessType], -- 业务类型 A.[DocumentType] as [AP_DocumentType], -- 应付账已记账明细单据类型 A11.Code as [DocType_Code], -- 付款单据类型编码 A11.Name as [DocType_Name], -- 付款单据类型名称 null as [AccrueSupplier_CombineName], null as [AccrueSupplierSite_Territory_CombineName], null as [AccrueSupplier_Category_CombineName], null as [AccrueSupplier_Territory_CombineName], isnull(A13.PaySupplier_CombineName,'') as [PaySupplier_CombineName], -- 付款供应商名称 isnull(A13.PaySupplierSite_Territory_CombineName,'') as [PaySupplierSite_Territory_CombineName], -- 付款供应商位置名称 isnull(A13.PaySupplier_Category_CombineName,'') as [PaySupplier_Category_CombineName],-- 付款供应商联系人组合名称 isnull(A13.PaySupplier_Territory_CombineName,'') as [PaySupplier_Territory_CombineName], -- 付款供应商地区组合名称 A13.PaySupplier_Code as [PaySupplier_Code], A13.PaySupplier_Name as [PaySupplier_Name], -- 付款供应商编码 null as [DescFlexField_CombineName], A14.APStatementDirect as [APStatementDirect], -- 付款供应商名称 A15.APStatementDirect as [OppAPStatementDirect], (A.[CRPUAmount] - A.[DRPUAmount]) as [CurAmount],-- 计价单位数量差额（计入数量 - 计出数量）） (A.[CROCMoney_TotalMoney] - A.[DROCMoney_TotalMoney]) as [CurACMoney], -- 核币总金额差额 (A.[CRFCMoney_TotalMoney] - A.[DRFCMoney_TotalMoney]) as [CurFCMoney], -- 本币总金额差额 A17.[MoneyRound_Precision] as [BCMoneyRound],-- 单据币种金额精度 A18.[MoneyRound_Precision] as [FCMoneyRound], -- 本币币种金额精度 A16.[Round_Precision] as [UOMMoneyRound], -- 单位精度 A17.[Symbol] as [BC_Symbol], -- 单据币种货币符号 A18.[Symbol] as [FC_Symbol], -- 本币币种货币符号 A16.[Code] as [PUom_Code], -- 单位编码 A22.[Name] as [PUom_Name] -- 单位名称 from AP_APPostedDetail as A -- 应付账已记账明细 left join [CBO_Operators] as A1 on (A.[Transactor] = A1.[ID]) -- 业务员 left join [Base_Organization] as A2 on (A.[Org] = A2.[ID]) -- 组织 left join [Base_Organization] as A3 on (A.[SrcBusinessOrg] = A3.[ID]) -- 来源组织 left join [Base_Organization] as A4 on (A.[BizOrg] = A4.[ID]) -- 业务组织 left join [Base_Organization] as A5 on (A.[SettleOrg] = A5.[ID]) -- 结算组织 left join [CBO_Department] as A6 on (A.[Dept] = A6.[ID]) -- 部门 left join [CBO_Project] as A7 on (A.[Project] = A7.[ID]) -- 项目 left join [CBO_Task] as A8 on (A.[Task] = A8.[ID]) -- 任务 left join [Base_Currency] as A9 on (A.[FC] = A9.[ID]) -- 本币币种 left join [Base_Currency] as A10 on (A.[BC] = A10.[ID]) -- 单据币种 left join #APDirectTempTable as A11 on (A.[DocumentType] = A11.ID) -- 付款单据类型临时表 left join #AccrueSupplierTmp as A12 on (A.[AccrueSuppSite_SupplierSite] = A12.AccrueSupplierSite_ID) -- 供应商信息临时表 inner join #PaySupplierTmp as A13 on (A.[PaySuppSite_SupplierSite] = A13.PaySupplierSite_ID) -- 付款供应商信息临时表 left join #APDirectTmp as A14 on (A.[DocumentType] = A14.ID) -- 应付账结算方向临时表 left join #APDirectTmp as A15 on (A.[OppDocType] = A15.ID) -- 应付账结算方向临时表 left join Base_UOM as A16 on (A.[PUom] = A16.[ID]) -- 单位 left join Base_Currency as A17 on (A.[BC] = A17.[ID]) -- 单据币种（用于金额精度和符号） left join Base_Currency as A18 on (A.[FC] = A18.[ID]) -- 本币币种（用于金额精度和符号） inner join #selectPeriod as A19 on (A.[ProcessBillPeriod] = A19.ID) -- 账簿会计期间筛选 left join [CBO_Department_Trl] as A20 on (A20.SysMlFlag = 'zh-CN') and (A6.[ID] = A20.[ID]) left join [Base_Currency_Trl] as A21 on (A21.SysMlFlag = 'zh-CN') and (A10.[ID] = A21.[ID]) left join [Base_UOM_Trl] as A22 on (A22.SysMlFlag = 'zh-CN') and (A16.[ID] = A22.[ID]) where (((((1 = 1) and (A2.[Code] = N'10')) and (A.[PaySupp_Code] = N'1.006')) and (A.[IsSureAccrue] = 1)) and A.[DocStatus] in (2)) insert into #TempMXHZ select A.AccrueSupplierSite_ID, -- 供应商位置ID A.PaySupplierSite_ID, -- 付款供应商位置ID A.Dept_TreeDisplayName,-- 部门名称 A.Item_ID, -- 料品ID A.BC_Code, -- 单据币种编码 A.BC_Name, -- 单据币种名称 A.AP_BusinessType, -- 业务类型 A.AP_DocumentType, -- 应付账已记账明细单据类型 A.DocType_Code,-- 付款单据类型编码 A.DocType_Name,-- 付款单据类型名称 A.AccrueSupplier_CombineName, -- 应计供应商组合名称？ A.AccrueSupplierSite_Territory_CombineName, -- 应计供应商位置组合名称？ A.AccrueSupplier_Category_CombineName, -- 应计供应商联系人组合名称？ A.AccrueSupplier_Territory_CombineName, -- 应计供应商地区组合名称？ A.PaySupplier_CombineName, -- 付款供应商名称 A.PaySupplierSite_Territory_CombineName, -- 付款供应商位置名称 A.PaySupplier_Category_CombineName,-- 付款供应商联系人组合名称 A.PaySupplier_Territory_CombineName, -- 付款供应商地区组合名称 A.PaySupplier_Code, A.PaySupplier_Name, -- 付款供应商编码 A.DescFlexField_CombineName, -- 付款供应商名称 0 as [QCAmount], 0 as [QCACMoney], 0 as [QCFCMoney], case when ((A.APStatementDirect = 0) and ((A.OppAPStatementDirect = 0) or A.OppAPStatementDirect is null)) then A.CurAmount else 0 end as [ARAmount], -- 应收数量：当应付账结算方向为正向（0）且对方结算方向为正向或为空时，取当前数量；否则为0 case when ((A.APStatementDirect = 0) and ((A.OppAPStatementDirect = 0) or A.OppAPStatementDirect is null)) then A.CurACMoney else 0 end as [ARACMoney], -- 应收核算币金额：同上条件取当前核算币金额；否则为0 case when ((A.APStatementDirect = 0) and ((A.OppAPStatementDirect = 0) or A.OppAPStatementDirect is null)) then A.CurFCMoney else 0 end as [ARFCMoney], -- 应收本币金额：同上条件取当前本币金额；否则为0 case when (not(((A.APStatementDirect = 0) and ((A.OppAPStatementDirect = 0) or A.OppAPStatementDirect is null)))) then (-(A.CurAmount)) else 0 end as [RecoverAmount], -- 数量：当应付账结算方向不符合上述条件时，取当前数量的负值；否则为0 case when (not(((A.APStatementDirect = 0) and ((A.OppAPStatementDirect = 0) or A.OppAPStatementDirect is null)))) then (-(A.CurACMoney)) else 0 end as [RecoverACMoney], -- 核算币金额：条件不满足时，取当前核算币金额的负值；否则为0 case when (not(((A.APStatementDirect = 0) and ((A.OppAPStatementDirect = 0) or A.OppAPStatementDirect is null)))) then (-(A.CurFCMoney)) else 0 end as [RecoverFCMoney],-- 本币金额：条件不满足时，取当前本币金额的负值；否则为0 0 as [QMAmount], 0 as [QMACMoney], 0 as [QMFCmoney], A.BCMoneyRound,-- 单据币种金额精度 A.FCMoneyRound, -- 本币币种金额精度 A.UOMMoneyRound, -- 单位精度 A.BC_Symbol, -- 单据币种货币符号 A.FC_Symbol, -- 本币币种货币符号 A.PUom_Code, -- 单位编码 A.PUom_Name -- 单位名称 from #TempMXCur as A -- 临时表 #TempMXCur 存储了当前期间的应付/应收数据 where ((A.CurACMoney != 0) or (A.CurFCMoney != 0))-- 仅返回当前核算币金额或本币金额不为0的记录 drop table #PaySupplierTmp select A.PaySupplier_Code, A.BC_Code, A.PaySupplier_Name, A.BC_Name, sum(A.QCACMoney) as [QCACMoney], max(A.BCMoneyRound) as [BCMoneyRound], A.BC_Symbol, sum(A.ARACMoney) as [ARACMoney], sum(A.RecoverACMoney) as [RecoverACMoney], sum(((A.QCACMoney + A.ARACMoney) - A.RecoverACMoney)) as [QMACMoney], sum(((A.QCFCMoney + A.ARFCMoney) - A.RecoverFCMoney)) as [QMFCMoney] into #TempGroupHZ from #TempMXHZ as A group by A.PaySupplier_Code, A.BC_Code, A.PaySupplier_Name, A.BC_Name, A.BC_Symbol select * from #TempGroupHZ as A where (1 = 1) order by A.PaySupplier_Code asc, A.PaySupplier_Name asc, A.BC_Name asc