解决方案: | 应收付系统结转年度出现未知错误0解决方案(只限于SQL版本) 1、维护工具cleasql.exe使用 此工具必须对已建立新年度数据库的数据操作才有用(例如2002年转2003年) 在数据库服务器上执行该程序,然后点刷新,会显示所有本机的用友数据库。 选择需要检查的帐套的2003年度数据库,点检验,可以检查应收付数据中基础档案不存在于档案表的情况。(包括科目,存货档案,客户,供应商,部门和人员档案)。 点检查AP_DETAIL,会出现Ap_detail表不能为空的字段为空的记录数据。可能为空的字段包括cinvcode(存货编码),cDWcode(客户或供应商编码),cDeptcode(部门),cPerson(人员)。 此工具只是作为辅助检查工具,具体更正修复方法还要靠代理或用户自行解决,或参考下面的方法。 2、存货编码(civcode)为空。此种情况最为常见,约占0错误的80%以上。 检查语句: select * from ap_detail where cinvcode is null and ccovouchtype in (‘26‘,‘27‘,‘01‘,‘02‘,‘28‘,‘29‘) 更新语句(应收系统对应的销售发票): UPDATE Ap_Detail set cInvCode=b.cInvCode,iBVID=b.AutoID From SaleBillVouch a INNER JOIN SaleBillVouchs b on a.SBVID=b.SBVID Where Ap_Detail.cCoVouchType=a.cVouchType and Ap_Detail.cCoVouchID=a.cSBVCode and (Ap_Detail.cInvCode=‘‘ or Ap_Detail.cInvCode Is Null or iBVID=0 or iBVID is null) and iFlag<3 更新语句(应付系统对应的采购发票) UPDATE Ap_Detail set cInvCode=b.cInvCode,iBVID=b.ID From purBillVouch a INNER JOIN purBillVouchs b on a.PBVID=b.PBVID Where Ap_Detail.cCoVouchType=a.cPBVBillType and Ap_Detail.cCoVouchID=a.cPBVCode and (Ap_Detail.cInvCode=‘‘ or Ap_Detail.cInvCode Is Null or iBVID=0 or iBVID is null) and iFlag<3 3、其他档案字段为空或档案丢失 cDWcode不能为空。 cDeotcode和cPerson可以为空,但必须保证档案中存在这些编码。 检查语句如下 cDWcode为空的语句 select * from ap_detail where cDWcode is null or cDWcode=‘‘ cDWcode中档案不存在的语句 select * from ap_detail where cDWcode not in (select cCusCode from Customer) and cflag=‘AR‘ select * from ap_detail where cDWcode not in (select cVenCode from Vendor) and cflag=‘AP‘ cDeptcode中档案不存在的语句 select * from ap_detail where cdeptcode not in (select cdeptcode from Department) cperson中档案不存在的语句 select * from ap_detail where cperson not in (select cpersoncode from person) 4、cvouchtype和ccovouchtype中R0或P0写成RO或PO的情况 检查语句 select cvouchtype from ap_detail group by cvouchtype select ccovouchtype from ap_detail group by ccovouchtype 如果执行显示出现2个类似‘R0‘或‘P0‘,或者有不存在于ap_vouchtype中的单据类型,就说明存在这种情况。 更正语句 update ap_detail set cvouchtype=‘R0‘ where cvouchtype like ‘R%‘ update ap_detail set cvouchtype=‘P0‘ where cvouchtype like ‘P%‘ update ap_detail set ccovouchtype=‘R0‘ where ccovouchtype like ‘R%‘ update ap_detail set ccovouchtype=‘P0‘ where ccovouchtype like ‘P%‘ 5、ap_detail表与salebillvouch表中同一张发票的客户编码不同导致。 查找语句: select s.cvouchtype as 发票类型 ,a.cvouchid as 发票号 ,ISNULL(s.cCusCode, 0) as 发票表中客户编号,a.cdwcode as ap_detai表中的客户编号 from (salebillvouchs s1 join salebillvouch s on s1.sbvid=s.sbvid)join ap_detail a on a.ibvid=s1.autoid where s.cvouchtype=a.cvouchtype and s.csbvcode=a.cvouchid and ISNULL(s.cCusCode, 0)a.cdwcode 修正语句: update ap_detail set ap_detail.cdwcode=s.ccuscode from (salebillvouchs s1 join salebillvouch s on s1.sbvid=s.sbvid)join ap_detail a on a.ibvid=s1.autoid where s.cvouchtype=a.cvouchtype and s.csbvcode=a.cvouchid and s.ccuscodea.cdwcode |
---|