用友U8 查询固定资产总帐时,显示原值和累计折旧等年初数和本年发生数据与其他帐表数不对用友U8 查询固定资产总帐时,显示原值和累计折旧等年初数和本年发生数据与其他帐表数不对
问题原因:经过测试发现与数据库的fa_Total表排序有关 解决方法:安装SQL2000 SP3 或者修改数据库中的存储过程fa_Q_Zz为:CREATE PROC fa_Q_ZZ(@sTypeNum varchar(50), @sDeptNum varchar(50)) AS --------------------------- Create #fa_Q_ZZ_Pre1 ---------------SELECT Top 1 '0' AS sSort, 0 AS sPeriod, 0 AS dblAddValueOfSum, 0 AS dblDecValueOfSum, Sum(fa_Total.dblMonthValue) AS dblBalance, 0 AS dblTransOutDeprT, 0 AS dblTransInDeprT, Sum(fa_Total.dblMonthDeprTotal) AS dblDeprT,(Sum(fa_Total.dblMonthValue)-Sum(fa_Total.dblMonthDeprTotal)) AS dblNetValue INTO #fa_Q_ZZ_Pre1_temp FROM fa_Total WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%')) GROUP BY fa_Total.iPeriod order by fa_Total.iPeriodSELECT *INTO #fa_Q_ZZ_Pre1FROM #fa_Q_ZZ_Pre1_temp UNION SELECT '1' AS sSort, fa_Total.iPeriod AS sPeriod, Sum(fa_Total.dblAddValue) AS dblAddValueOfSum, Sum(fa_Total.dblDecValue) AS dblDecValueOfSum, Sum(fa_Total.dblValue) AS dblBalance,Sum(fa_Total.dblTransOutDeprTotal) AS dblTransOutDeprT, Sum([dblTransInDeprTotal]+[dblDepr]) AS dblTransInDeprT ,Sum(fa_Total.dblDeprTotal) AS dblDeprT, (Sum(fa_Total.dblValue)- Sum(fa_Total.dblDeprTotal)) AS dblNetValue FROM fa_Total WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%') AND ((fa_Total.iPeriod)<=[iPeriod])) GROUP BY fa_Total.iPeriod UNION SELECT '2' AS sSort, 13 AS sPeriod, Sum(fa_Total.dblAddValue) AS dblAddValueOfSum, Sum(fa_Total.dblDecValue) AS dblDecValueOfSum, Sum(fa_Total.dblValue) AS dblBalance, Sum(fa_Total.dblTransOutDeprTotal) AS dblTransOutDeprT,Sum([dblTransInDeprTotal]+[dblDepr]) AS dblTransInDeprT, Sum(fa_Total.dblDeprTotal) AS dblDeprT, (Sum(fa_Total.dblValue)-Sum(fa_Total.dblDeprTotal)) AS dblNetValue FROM fa_Total WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%')) --------------------------- Create #fa_Q_ZZ_Pre2 ---------------SELECT '2' AS sSort, 13 AS sPeriod, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblValue] ELSE 0 END)) AS dblAddValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblValue] END)) AS dblDecValue, 0 AS dblBalanceValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblTransOutDeprTCard] END)) AS dblTransOutDeprT, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblTransInDeprTCard] ELSE 0 END)) AS dblTransInDeprT, 0 AS dblDeprT, 0 AS dblBalance INTO #fa_Q_ZZ_Pre2 FROM fa_Cards WHERE (((fa_Cards.sTypeNum) Like @sTypeNum + '%') AND ((fa_Cards.sDeptNum) Like @sDeptNum + '%') AND (([fa_Cards].[sTransTypeNum]) Like @sTypeNum + '%') AND (([fa_Cards].[sTransDepteNum]) Like @sDeptNum + '%') AND ((fa_Cards.iOptType) Between 6 And 9)) UNION SELECT '1' AS sSort, [iTransPeriod] AS sPeriod, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblValue] ELSE 0 END)) AS dblAddValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblValue] END)) AS dblDecValue, 0 AS dblBalanceValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblTransOutDeprTCard] END)) AS dblTransOutDeprT, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblTransInDeprTCard] ELSE 0 END)) AS dblTransInDeprT, 0 AS dblDeprT, 0 AS dblBalance FROM fa_Cards WHERE (((fa_Cards.sTypeNum) Like @sTypeNum + '%') AND ((fa_Cards.sDeptNum) Like @sDeptNum + '%') AND (([fa_Cards].[sTransTypeNum]) Like @sTypeNum + '%') AND (([fa_Cards].[sTransDepteNum]) Like @sDeptNum + '%') AND ((fa_Cards.iOptType) Between 6 And 9)) GROUP BY iTransPeriod --------------------------------- END -------------------------SELECT (CASE #fa_Q_ZZ_Pre1.sPeriod WHEN 0 THEN '上期结转' WHEN 13 THEN '合计' ELSE CONVERT(varchar(2),#fa_Q_ZZ_Pre1.sPeriod) END) AS sPeriod, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE #fa_Q_ZZ_Pre1.[dblAddValueOfSum]-ISNULL([dblAddValue],0) END) AS dblDebitValue, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE #fa_Q_ZZ_Pre1.[dblDecValueOfSum]-ISNULL([dblDecValue],0) END) AS dblCreditValue, #fa_Q_ZZ_Pre1.dblBalance, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE [#fa_Q_ZZ_Pre1].[dblTransOutDeprT]-ISNULL([#fa_Q_ZZ_Pre2].[dblTransOutDeprT],0) END) AS dblDebitDeprT, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE [#fa_Q_ZZ_Pre1].[dblTransInDeprT]-ISNULL([#fa_Q_ZZ_Pre2].[dblTransInDeprT],0) END) AS dblCreditDeprT, #fa_Q_ZZ_Pre1.dblDeprT, #fa_Q_ZZ_Pre1.dblNetValue ,[#fa_Q_ZZ_Pre1].[sPeriod] AS OrderPeriod FROM #fa_Q_ZZ_Pre1 LEFT JOIN #fa_Q_ZZ_Pre2 ON #fa_Q_ZZ_Pre1.sPeriod = #fa_Q_ZZ_Pre2.sPeriod ORDER BY #fa_Q_ZZ_Pre1.sSort, [#fa_Q_ZZ_Pre1].[sPeriod]GO
解决方案:
问题原因:经过测试发现与数据库的fa_Total表排序有关 解决方法:安装SQL2000 SP3 或者修改数据库中的存储过程fa_Q_Zz为:CREATE PROC fa_Q_ZZ(@sTypeNum varchar(50), @sDeptNum varchar(50)) AS --------------------------- Create #fa_Q_ZZ_Pre1 ---------------SELECT Top 1 '0' AS sSort, 0 AS sPeriod, 0 AS dblAddValueOfSum, 0 AS dblDecValueOfSum, Sum(fa_Total.dblMonthValue) AS dblBalance, 0 AS dblTransOutDeprT, 0 AS dblTransInDeprT, Sum(fa_Total.dblMonthDeprTotal) AS dblDeprT,(Sum(fa_Total.dblMonthValue)-Sum(fa_Total.dblMonthDeprTotal)) AS dblNetValue INTO #fa_Q_ZZ_Pre1_temp FROM fa_Total WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%')) GROUP BY fa_Total.iPeriod order by fa_Total.iPeriodSELECT *INTO #fa_Q_ZZ_Pre1FROM #fa_Q_ZZ_Pre1_temp UNION SELECT '1' AS sSort, fa_Total.iPeriod AS sPeriod, Sum(fa_Total.dblAddValue) AS dblAddValueOfSum, Sum(fa_Total.dblDecValue) AS dblDecValueOfSum, Sum(fa_Total.dblValue) AS dblBalance,Sum(fa_Total.dblTransOutDeprTotal) AS dblTransOutDeprT, Sum([dblTransInDeprTotal]+[dblDepr]) AS dblTransInDeprT ,Sum(fa_Total.dblDeprTotal) AS dblDeprT, (Sum(fa_Total.dblValue)- Sum(fa_Total.dblDeprTotal)) AS dblNetValue FROM fa_Total WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%') AND ((fa_Total.iPeriod)<=[iPeriod])) GROUP BY fa_Total.iPeriod UNION SELECT '2' AS sSort, 13 AS sPeriod, Sum(fa_Total.dblAddValue) AS dblAddValueOfSum, Sum(fa_Total.dblDecValue) AS dblDecValueOfSum, Sum(fa_Total.dblValue) AS dblBalance, Sum(fa_Total.dblTransOutDeprTotal) AS dblTransOutDeprT,Sum([dblTransInDeprTotal]+[dblDepr]) AS dblTransInDeprT, Sum(fa_Total.dblDeprTotal) AS dblDeprT, (Sum(fa_Total.dblValue)-Sum(fa_Total.dblDeprTotal)) AS dblNetValue FROM fa_Total WHERE (((fa_Total.sDeptNum) Like @sDeptNum + '%') AND ((fa_Total.sTypeNum) Like @sTypeNum + '%')) --------------------------- Create #fa_Q_ZZ_Pre2 ---------------SELECT '2' AS sSort, 13 AS sPeriod, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblValue] ELSE 0 END)) AS dblAddValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblValue] END)) AS dblDecValue, 0 AS dblBalanceValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblTransOutDeprTCard] END)) AS dblTransOutDeprT, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblTransInDeprTCard] ELSE 0 END)) AS dblTransInDeprT, 0 AS dblDeprT, 0 AS dblBalance INTO #fa_Q_ZZ_Pre2 FROM fa_Cards WHERE (((fa_Cards.sTypeNum) Like @sTypeNum + '%') AND ((fa_Cards.sDeptNum) Like @sDeptNum + '%') AND (([fa_Cards].[sTransTypeNum]) Like @sTypeNum + '%') AND (([fa_Cards].[sTransDepteNum]) Like @sDeptNum + '%') AND ((fa_Cards.iOptType) Between 6 And 9)) UNION SELECT '1' AS sSort, [iTransPeriod] AS sPeriod, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblValue] ELSE 0 END)) AS dblAddValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblValue] END)) AS dblDecValue, 0 AS dblBalanceValue, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN 0 ELSE [dblTransOutDeprTCard] END)) AS dblTransOutDeprT, Sum((CASE WHEN [iOptType]=7 Or [iOptType]=9 THEN [dblTransInDeprTCard] ELSE 0 END)) AS dblTransInDeprT, 0 AS dblDeprT, 0 AS dblBalance FROM fa_Cards WHERE (((fa_Cards.sTypeNum) Like @sTypeNum + '%') AND ((fa_Cards.sDeptNum) Like @sDeptNum + '%') AND (([fa_Cards].[sTransTypeNum]) Like @sTypeNum + '%') AND (([fa_Cards].[sTransDepteNum]) Like @sDeptNum + '%') AND ((fa_Cards.iOptType) Between 6 And 9)) GROUP BY iTransPeriod --------------------------------- END -------------------------SELECT (CASE #fa_Q_ZZ_Pre1.sPeriod WHEN 0 THEN '上期结转' WHEN 13 THEN '合计' ELSE CONVERT(varchar(2),#fa_Q_ZZ_Pre1.sPeriod) END) AS sPeriod, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE #fa_Q_ZZ_Pre1.[dblAddValueOfSum]-ISNULL([dblAddValue],0) END) AS dblDebitValue, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE #fa_Q_ZZ_Pre1.[dblDecValueOfSum]-ISNULL([dblDecValue],0) END) AS dblCreditValue, #fa_Q_ZZ_Pre1.dblBalance, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE [#fa_Q_ZZ_Pre1].[dblTransOutDeprT]-ISNULL([#fa_Q_ZZ_Pre2].[dblTransOutDeprT],0) END) AS dblDebitDeprT, (CASE WHEN [#fa_Q_ZZ_Pre1].[sSort]<>'1' And [#fa_Q_ZZ_Pre1].[sSort]<>'2' THEN 0 ELSE [#fa_Q_ZZ_Pre1].[dblTransInDeprT]-ISNULL([#fa_Q_ZZ_Pre2].[dblTransInDeprT],0) END) AS dblCreditDeprT, #fa_Q_ZZ_Pre1.dblDeprT, #fa_Q_ZZ_Pre1.dblNetValue ,[#fa_Q_ZZ_Pre1].[sPeriod] AS OrderPeriod FROM #fa_Q_ZZ_Pre1 LEFT JOIN #fa_Q_ZZ_Pre2 ON #fa_Q_ZZ_Pre1.sPeriod = #fa_Q_ZZ_Pre2.sPeriod ORDER BY #fa_Q_ZZ_Pre1.sSort, [#fa_Q_ZZ_Pre1].[sPeriod]GO
www.kuaiji66.com 天龙瑞德