| 解决方案： |
在查询分析器中运行下面的SQL语句可修改辅助总账的部门年初数：
DECLARE @SysID as varchar(2),@Period as tinyint,@ccode varchar(16)
-- 应付账款： @SysID='AP' 应收账款： @SysID='AR'
SET @SysID='AP'
-- 删除辅助总账中对应记录
SET @SysID=UPPER(@SysID)
IF @SysID='AP'
DELETE FROM GL_accass WHERE csup_id IS NULL and ccode in
(SELECT ccode FROM code WHERE cother=@SysID and bDept=1 and bend=1)
ELSE
IF @SysID='AR'
DELETE FROM GL_accass WHERE ccus_id IS NULL and ccode in
(SELECT ccode FROM code WHERE cother=@SysID and bDept=1 and bend=1)
DECLARE asscode CURSOR
FOR
SELECT ccode FROM code WHERE cother=@SysID and bDept=1 and bend=1 ORDER BY ccode
OPEN asscode
FETCH NEXT FROM asscode INTO @ccode
WHILE @@FETCH_STATUS=0
BEGIN
SET @Period=1
WHILE @Period<=12
BEGIN
INSERT INTO GL_accass(ccode, cdept_id, iperiod, cbegind_c, cBegind_c_engl,
mb, md, mc, cendd_c, cendd_c_engl, me, mb_f, md_f, mc_f, me_f, nb_s, nd_s,
nc_s, ne_s) SELECT ccode, cdeptcode, @Period,
CASE d_c WHEN 'Dr' THEN '借' WHEN '-' THEN '平' ELSE '贷' END, d_c, mb, 0,
0, CASE d_c WHEN 'Dr' THEN '借' WHEN '-' THEN '平' ELSE '贷' END, d_c, mb,
0, 0, 0, 0, 0, 0, 0, 0 FROM (
SELECT ccode, cDwCode, cDeptCode,
(CASE SIGN(iDAmount-iCAmount) WHEN 1 THEN 'Dr' WHEN 0 THEN '-' ELSE 'Cr' END) as d_c,
ABS(iDAmount-iCAmount) AS mb FROM (
SELECT ccode, cDwCode, cDeptCode, SUM(iDAmount) AS iDAmount,
SUM(iCAmount) AS iCAmount
FROM Ap_Detail WHERE cflag=@SysID and iperiod=0 and ccode=@ccode
GROUP BY ccode,cDwCode,cDeptCode
) AS A
) AS B
SET @Period=@Period+1
END
FETCH NEXT FROM asscode INTO @ccode
END
CLOSE asscode
DEALLOCATE asscode
|