K3应收应付结账检查处理代码

最新推荐文章于 2023-05-07 15:04:36 发布

转载最新推荐文章于 2023-05-07 15:04:36 发布 · 2.5k 阅读

文章标签：

#join #2010

金蝶K3二次开发专栏收录该内容

22 篇文章

订阅专栏

本文提供了一系列SQL查询与更新语句，用于检查与修正财务系统中单据科目与凭证科目不一致的问题，并展示了如何查询期间不一致的单据及进行期间调整。

查科目IT内码

select * from t_account
where fname like '%客户%'

单据科目与凭证科目不一致

SELECT *
FROM t_RP_ARPBill
WHERE (FNumber LIKE 'QTYs%')
修改FAcountID

收款单列表

select * from t_RP_NewReceiveBill
where fnumber like '%237'

与凭证不一致的查询代码

select t1.FDate, t1.FNumber as FBillNO,
case when t1.FType = 1 then '其他应收单' when t1.FType = 2 then '其他应付单'
when t1.FType = 3 then '销售发票' when t1.FType = 4 then '采购发票'
when (t1.FType = 5 and t1.FPre =0) then '收款单' when (t1.FType = 6 and t1.FPre =0) then '付款单'
when (t1.FType = 5 and t1.FPre =1) then '预收单' when (t1.FType = 6 and t1.FPre =1) then '预付单'
when (t1.FType = 5 and t1.FPre =-1) then '应收退款单' when (t1.FType = 6 and t1.FPre =-1) then '应付退款单'
when t1.FType = 8 then '坏账' when t1.FType = 9 then '转账单据'
else '单据' end as FType,
t1.fid,t2.Fvoucherid, '单据期间与对应凭证不一致' as FErrInfo
from t_rp_contact t1,t_voucher t2
Where t1.FVoucherID = t2.FVoucherID And (t1.FPeriod <> t2.FPeriod or t1.FYear <> t2.FYear )
and t1.FIsinit = 0 and t1.FYear = 2010 and t1.Fperiod = 9 and t1.FRp = 0

修改未审核单据的期间。

update v1
set fperiod=10
from t_RP_Contact v1
inner join t_Currency t10 on (v1.FCurrencyID = t10.FcurrencyID)
where ( v1.FStatus & 1 )=0
and ( v1.FStatus & 16 )=0
and v1.FRP=0
and v1.FYear=2010 and FPeriod=9