问题现象: | T6升级到U8 10.0后,9月份对账单摘要出错,取的不是对应单据的备注字段。 客户名称:洛阳北台壁纸有限公司 需要远程的话联系QQ:1300383871. |
问题原因: | |
解决方案: | 您好, 此问题【经后台跟踪】现做如下分析与答复: 对账单摘要,不一定是对应单据的备注字段,其取值原理: 1、如果是期初,则直接为期初余额; 2、 其它情况: 如果:单据来源单据(ccovouchtype) = 单据处理方式(cprocstyle) or 是否正常标志(iFlag =1) or 单据处理方式(cprocstyle = 'xj'), 摘要(cdigest) 否则 如果 摘要(cdigest) is null then 单据类型名称(cTypeName) 否则 摘要 结束 结束 drop table tempdb..Ywzb11203664669 drop table #myRdrecord 可以通过如下语句来查询出其对应的摘要值: 先执行如下语句: Create Table #Ywzb11203664669 (iYear nvarchar(10),iMonth nvarchar(10),iDay nvarchar(10),cDwCode nvarchar(20),cDwName nvarchar(120),cDeptCode nvarchar(20),cDepName nvarchar(120),cPerson nvarchar(20), cPersonName nvarchar(120), cInvCode nvarchar(60), cInvName nvarchar(255),cInvStd nvarchar(255),cDwCCode nvarchar(20),cDWCName nvarchar(120),cDwDCode nvarchar(20),cDCName nvarchar(120),cHDwCode nvarchar(20),cHDwName nvarchar(120),cHDptCode nvarchar(20),cHDepName nvarchar(120),cHPsnCode nvarchar(20),cHPersonName nvarchar(120),cInvCCode nvarchar(20),cInvCName nvarchar(120),cCode nvarchar(60),cCode_Name nvarchar(120),cItem_Class nvarchar(2),cItem_Name nvarchar(20),cItemCode nvarchar(60),cItemName nvarchar(255),cPzNum nvarchar(15),cContractType nvarchar(12),cContractTypeName nvarchar(40),cContractID nvarchar(64),cContractName nvarchar(400),cCusCreditCompany nvarchar(20),cCusCreditName nvarchar(120),cOrderNo nvarchar(30),cDLCode nvarchar(30),cSaleOut nvarchar(30),dgst nvarchar(200),vtype nvarchar(50),vid nvarchar(30),cCancelNo nvarchar(40),cpzid nvarchar(30),exchname nvarchar(8),price float,Rate Float,jf_f money,jf_s float,jf money,df_f money,df_s float,df money,jf_f2 money,jf_s2 float,jf2 money,df_f2 money,df_s2 float,df2 money,ye_f money,ye_s float,ye money,ye_f2 money,ye_s2 float,ye2 money,csysid nvarchar(2),cDwAddress nvarchar(200),cDwPostCode nvarchar(6),cDwEmail nvarchar(100),cDwPerson nvarchar(50),cDwPhone nvarchar(100),cDwFax nvarchar(100),Auto_Id int,dRDate datetime,dExpireDate datetime,[cDefine1] nvarchar(20),[cDefine2] nvarchar(20),[cDefine5] int) go create table #myTLang (ctypecode nvarchar(10),ctypename nvarchar(60),ctypeclass nvarchar(1),cflag nvarchar(2),csign nvarchar(8)) Create Index idx_ctypecode on #myTLang(ctypecode) declare @LocaleID varchar(32) select @LocaleID=LocaleID from U8LangDefine with(nolock) where LangID=@@LANGID INSERT INTO #myTLang(ctypecode,ctypename,ctypeclass,cflag,csign) select ctypecode,ctypename,ctypeclass,cflag,csign from ap_vouchtype_base Where LocaleID = @LocaleID insert into #Ywzb11203664669(iMonth,iDay,cdwcode,cdwname,dgst,jf_f,jf_s,jf,df_f,df_s,df,csysid,cDwAddress,cDwPostCode,cDwEmail,cDwPerson,cDwPhone,cDwFax) select null as imonth,null as iDay,max(cdwcode),max(cdwname) as cdwname,N'期初余额' as dgst,sum(case when a.cexch_name=N'人民币' then 0 else idamount_f end) as jf_f,sum(idamount_s) as jf_s,sum(idamount) as jf,sum(case when a.cexch_name=N'人民币' then 0 else icamount_f end) as df_f,sum(icamount_s) as df_s,sum(icamount) as df,N'AR',max(cDwAddress),max(cDwPostCode),max(cDwEmail),max(cDwPerson),max(cDwPhone),max(cDwFax) From Ar_DetailCust_s a with (nolock) Left Join vw_CM_ContractBForAPAR cmb with (nolock) on a.cContractID=cmb.strContractID and (a.cprocstyle like '2%' or (a.cprocstyle = N'XJ' and a.ccovouchtype like '2%')) where ((isnull(dcreditstart,dregdate)<'2011-01-01')) And a.cFlag=N'AR' and a.iflag<=2 and (isnull(cmb.busestage,0)=0) Group by cdwcode Having sum(idamount - icamount) <> 0 Or sum(idamount_f - icamount_f) <> 0 go select * from #Ywzb11203664669 create table #myRdrecord (idlsid int,cSource nvarchar(2),ccode nvarchar(30)) go Create Index idlsid_idx on #myRdrecord(idlsid) go DECLARE @maxidlsid INT DECLARE @minidlsid INT DECLARE @maxddate datetime DECLARE @minddate datetime SELECT @maxidlsid = MAX(idlsid), @minidlsid = MIN(idlsid) From ar_detailcust_s a with(nolock) Left Join vw_CM_ContractBForAPAR cmb with (nolock) on a.cContractID=cmb.strContractID and (a.cprocstyle like '2%' or (a.cprocstyle = N'XJ' and a.ccovouchtype like '2%')) select @maxddate = MAX(ddate), @minddate = MIN(Ddate) from rdrecords32 rdrecords with(nolock) inner join rdrecord32 rdrecord with(nolock) on rdrecords.id=rdrecord.id where idlsid>= @minidlsid AND idlsid<= @maxidlsid insert into #myRdrecord select idlsid,max(case when csource like N'出口%' then N'EX' else N'2' end) as cSource ,max(ccode) as ccode from rdrecords32 rdrecords with(nolock) inner join rdrecord32 rdrecord with(nolock) on rdrecords.id=rdrecord.id where idlsid>= @minidlsid AND idlsid<= @maxidlsid AND ddate >= @minddate and ddate <= @maxddate group by idlsid 其后查看结果,第一个字段即是摘要(备注)取值: select case when ccovouchtype=cprocstyle Or a.iFlag=1 Or cProcStyle=N'XJ' then cdigest else (case when cdigest is null then ap_vouchtype_2.cTypeName else cdigest end) end as dgst, year(min(isnull(dCreditStart,dregdate))) as iyear,month(min(isnull(dCreditStart,dregdate))) as imonth, Day(min(isnull(dCreditStart,dregdate))) as iDay,max(cdwcode),max(cdwname) as cdwname,max(corderno),max(a.cdlcode),max(#myRdrecord.ccode), Max(cGlsign+N'-'+isnull(REPLICATE(N'0',4-len(iGLno_id)),N'')+convert(nvarchar,iGLno_id)) as cPzNum, max(case when ccovouchtype=cprocstyle Or a.iFlag=1 Or cProcStyle=N'XJ' then cdigest else (case when cdigest is null then ap_vouchtype_2.cTypeName else cdigest end) end) as dgst,max(case when ccovouchtype=cprocstyle Or a.iFlag=1 Or cProcStyle=N'XJ' then ap_vouchtype.cTypeName else ap_vouchtype_2.cTypeName end) as vtype,max(case when ccovouchtype=cprocstyle Or a.iFlag=1 Or cProcStyle=N'XJ' then cCoVouchId else cCancelNo end) as vid,max(cCancelNo) as ccancelno, max(cpzid) as cpzid, sum(case when a.cexch_name=N'人民币' then 0 else idamount_f end) as jf_f,sum(idamount_s) as jf_s,sum(idamount) as jf, sum(case when a.cexch_name=N'人民币' then 0 else icamount_f end) as df_f,sum(icamount_s) as df_s,sum(icamount) as df,max(a.cexch_name) as exchname, max(a.iprice) as price,max(a.iexchrate) As Rate,N'AR',Max(Auto_Id),min(isnull(dCreditStart,dregdate)), max(isnull(dgatheringdate,isnull(a.dtZbjEndDate,dVouchDate+isnull(PayCondition.iPayCreDays,0)))),max(cDwAddress),max(cDwPostCode),max(cDwEmail), max(cDwPerson),max(cDwPhone),max(cDwFax),max(a.cDefine1),max(a.cDefine2),max(a.cDefine5) From Ar_DetailCust_s a with (nolock) LEFT JOIN #myTLang Ap_VouchType with (nolock) ON a.cVouchType = Ap_VouchType.cTypeCode LEFT JOIN #myTLang Ap_VouchType_2 with (nolock) ON a.cProcstyle = Ap_VouchType_2.cTypeCode LEFT JOIN PayCondition with (nolock) ON a.cPayCode = PayCondition.cPayCode left join #myRdrecord on a.idlsid=#myRdrecord.idlsid and ((a.ccovouchtype like 'EX%' AND #myRdrecord.cSource = 'EX') OR (a.ccovouchtype like '2%' AND #myRdrecord.cSource = '2') ) Left Join vw_CM_ContractBForAPAR cmb with (nolock) on a.cContractID=cmb.strContractID and (a.cprocstyle like '2%' or (a.cprocstyle = N'XJ' and a.ccovouchtype like '2%')) where ((isnull(dCreditStart,dregdate)>='2011-01-01' and isnull(dCreditStart,dregdate)<='2011-01-31')) And a.cFlag=N'AR' and a.iflag<=2 and (isnull(cmb.busestage,0)=0) Group by cdwcode,ccancelno,a.cexch_name,dCreditStart,ccovouchtype,cprocstyle,iFlag,cProcStyle,ap_vouchtype_2.cTypeName,cdigest having sum(idamount - icamount) <> 0 Or sum(idamount_f - icamount_f) <> 0 【注意事项】修改前请务必做好数据备份; |
相关补丁: | |
版本: | U8 V10.0 |
模块: | 公司对账 |
产品: | 财务会计 |
问题名称: | T6升级到U8 10.0后,应收模块9月份对账单摘要出错 |
最后更新: | 2011-12-06 23:41:05 |