应收应付按照科目对账-查询对账关系异常的凭证
zhenzhu_chen
2人赞赏了该文章
1,499次浏览
编辑于2016年11月24日 11:22:05
应收应付与总账对账，按照科目对账模式的取数来源是单据（应收单，收款单，应付单，付款单）和结算记录与总账模块凭证直接的对账关系（t_ar_araprelationentry）。以下列举出查询按照对账不平，对账关系丢失或者翻倍的凭证的查询语句。 查询本期借方对账关系丢失的凭证select b.fnumber from T_GL_VoucherEntry cinner join t_gl_voucher b on c.FBillID=b.fidinner join T_BD_Periodd on b.FPeriodID=d.fidinner join T_ORG_Company e on e.fid=b.FCompanyIDwhere 1=1and d.FNumber='201611'and e.fnumber='01'and c.FEntryDC=1 and c.fid not in (select fdestEntryID from T_AR_ArApRelationEntry ) 查询本期贷方对账关系丢失的凭证select b.fnumber from T_GL_VoucherEntry cinner join t_gl_voucher b on c.FBillID=b.fidinner join T_BD_Periodd on b.FPeriodID=d.fidinner join T_ORG_Company e on e.fid=b.FCompanyIDwhere 1=1and d.FNumber='201611'and e.fnumber='01'and c.FEntryDC=0and c.fid not in (selectfdestEntryID from T_AR_ArApRelationEntry ) 查询应收本期借方对账关系重复的凭证(应收单生成凭证) select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillID inner join (selecta.FSrcEntryID ,a.FDestEntryID from T_AR_ArApRelationEntry a inner join T_GL_VoucherEntry c on c.fid=a.FDestEntryID inner joint_ar_otherbillentry e on a.FSrcEntryID=e.fid group by a.FSrcEntryID,a.FDestEntryID having count(1) >1 ) as t3on t2.fid=t3.fdestentryid inner join T_ORG_Company t4 on t1.FCompanyID=t4.fidinner join T_BD_Period t5 ont1.FPeriodID = t5.fidwhere 1=1and t4.fnumber='01'and t5.fnumber='201611'and t2.FEntryDC=1 查询应收本期贷方对账关系重复的凭证 （收款单生成凭证）select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillID inner join (selecta.FSrcEntryID ,a.FDestEntryID from T_AR_ArApRelationEntry a inner join T_GL_VoucherEntry c on c.fid=a.FDestEntryID inner joinT_CAS_ReceivingBillEntry e on a.FSrcEntryID=e.fid group by a.FSrcEntryID,a.FDestEntryID having count(1) >1 ) as t3on t2.fid=t3.fdestentryid inner join T_ORG_Company t4 on t1.FCompanyID=t4.fidinner join T_BD_Period t5 ont1.FPeriodID = t5.fidwhere 1=1and t4.fnumber='01'and t5.fnumber='201611'and t2.FEntryDC=0 查询本期借方对账关系重复的凭证(付款单生成凭证)select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillID inner join (selecta.FSrcEntryID ,a.FDestEntryID from T_AR_ArApRelationEntry a inner join T_GL_VoucherEntry c on c.fid=a.FDestEntryID inner joinT_CAS_PaymentBillEntry e on a.FSrcEntryID=e.fid group by a.FSrcEntryID,a.FDestEntryID having count(1) >1 ) as t3on t2.fid=t3.fdestentryid inner join T_ORG_Company t4 on t1.FCompanyID=t4.fidinner join T_BD_Period t5 ont1.FPeriodID = t5.fidwhere 1=1and t4.fnumber='01'and t5.fnumber='201611'and t2.FEntryDC=1查询本期贷方对账关系重复的凭证(应付单生成凭证)selectt1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillID inner join (selecta.FSrcEntryID ,a.FDestEntryID from T_AR_ArApRelationEntry a inner join T_GL_VoucherEntry c on c.fid=a.FDestEntryID inner joinT_AP_OtherBillentry e on a.FSrcEntryID=e.fid group by a.FSrcEntryID,a.FDestEntryID having count(1) >1 ) as t3on t2.fid=t3.fdestentryid inner join T_ORG_Company t4 on t1.FCompanyID=t4.fidinner join T_BD_Period t5 ont1.FPeriodID = t5.fidwhere 1=1and t4.fnumber='01'and t5.fnumber='201611'andt2.FEntryDC=0查询本期应收结算记录借方对账关系翻倍select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillIDinner join t_org_company t3 on t1.fcompanyid=t3.fid inner join t_bd_period t4 on t1.FPeriodID=t4.fid where 1=1 and t3.fnumber='01'and t4.FNumber='201611'and t2.fid in (select a.fdestentryid from T_AR_ArApRelationEntry a innerjoin t_gl_voucherentry b on a.FDestEntryID=b.FIDinner join T_AR_VerificationBillentry c on a.FSrcEntryID=c.fidinner join t_ar_otherbill d on c.fbillid=d.fid where b.FEntryDC=1) 查询本期应收结算记录贷方对账关系翻倍select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillIDinner join t_org_company t3 on t1.fcompanyid=t3.fid inner join t_bd_period t4 on t1.FPeriodID=t4.fid where 1=1 and t3.fnumber='01'and t4.FNumber='201611'and t2.fid in (select a.fdestentryid from T_AR_ArApRelationEntry a innerjoin t_gl_voucherentry b on a.FDestEntryID=b.FIDinner join T_AR_VerificationBillentry c on a.FSrcEntryID=c.fidinner join T_CAS_ReceivingBilld on c.fbillid=d.fid where b.FEntryDC=1) 查询本期应付结算记录借方对账关系翻倍select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillIDinner join t_org_company t3 on t1.fcompanyid=t3.fid inner join t_bd_period t4 on t1.FPeriodID=t4.fid where 1=1 and t3.fnumber='01'and t4.FNumber='201611'and t2.fid in (select a.fdestentryid from T_AR_ArApRelationEntry a innerjoin t_gl_voucherentry b on a.FDestEntryID=b.FIDinner join T_Ap_VerificationBillentry c on a.FSrcEntryID=c.fidinner join T_CAS_PaymentBilld on c.fbillid=d.fid where b.FEntryDC=1) 查询本期应付结算记录贷方对账关系翻倍select t1.fnumber from t_gl_voucher t1 inner join T_GL_VoucherEntry t2 on t1.fid=t2.FBillIDinner join t_org_company t3 on t1.fcompanyid=t3.fid inner join t_bd_period t4 on t1.FPeriodID=t4.fid where 1=1 and t3.fnumber='01'and t4.FNumber='201611'and t2.fid in (select a.fdestentryid from T_AR_ArApRelationEntry a innerjoin t_gl_voucherentry b on a.FDestEntryID=b.FIDinner join T_Ap_VerificationBillentry c on a.FSrcEntryID=c.fidinner join t_ap_otherbill d on c.fbillid=d.fid where b.FEntryDC=1)
推荐阅读