不计入往来的收款单修改核销状态为完全SQL语句参考原创
39人赞赏了该文章
269次浏览
编辑于2024年06月03日 14:16:41
业务场景:
标准产品中收款用途不计入往来的收款单不参与往来核销,所以核销状态默认为空。
如有业务需求需要使其为完全核销的状态可通过数据库更新。
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
--更新明细字段:核销状态为完全,已核销金额和退款关联金额为应收金额 MERGE INTO T_AR_RECEIVEBILLENTRY T1 USING ( SELECT A.FID,A.FBILLNO 单据编号,A.FDATE 业务日期,B.FENTRYID,B.FSEQ 明细序号,B.FPURPOSEID 付款用途,C.FFINMANEGEMENT 是否计入往来,B.FWRITTENOFFSTATUS 明细核销状态,B.FRECTOTALAMOUNTFOR 应收金额,B.FRECTOTALAMOUNT 应收金额本位币,B.FWRITTENOFFAMOUNTFOR 已核销金额,B.FWRITTENOFFAMOUNT 已核销金额本位币,B.FREFUNDAMOUNT 退款关联金额 FROM T_AR_RECEIVEBILL A LEFT JOIN T_AR_RECEIVEBILLENTRY B ON A.FID=B.FID LEFT JOIN T_CN_RECPAYPURPOSE C ON B.FID=C.FID WHERE A.FDOCUMENTSTATUS='C' AND C.FFINMANEGEMENT=0 --AND A.FBILLNO='单据编号' --可根据单据编号更新 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FWRITTENOFFSTATUS='C',T1.FWRITTENOFFAMOUNTFOR=T1.FRECTOTALAMOUNTFOR,T1.FWRITTENOFFAMOUNT=T1.FRECTOTALAMOUNT,T1.FREFUNDAMOUNT=T1.FRECTOTALAMOUNTFOR; --更新单据头核销状态 MERGE INTO T_AR_RECEIVEBILL T1 USING ( SELECT A.FID,A.FBILLNO,A.FDATE,A.FWRITTENOFFSTATUS,CASE WHEN NOT EXISTS(SELECT 1 FROM T_AR_RECEIVEBILLENTRY WHERE FID=A.FID AND FWRITTENOFFSTATUS IN (' ','A','B')) THEN 'C' WHEN NOT EXISTS(SELECT 1 FROM T_AR_RECEIVEBILLENTRY WHERE FID=A.FID AND FWRITTENOFFSTATUS IN ('C','B')) THEN 'A' ELSE 'B' END NEW FROM T_AR_RECEIVEBILL A WHERE FRECEIVEAMOUNTFOR<>0 AND A.FWRITTENOFFSTATUS<>CASE WHEN NOT EXISTS(SELECT 1 FROM T_AR_RECEIVEBILLENTRY WHERE FID=A.FID AND FWRITTENOFFSTATUS IN (' ','A','B')) THEN 'C' WHEN NOT EXISTS(SELECT 1 FROM T_AR_RECEIVEBILLENTRY WHERE FID=A.FID AND FWRITTENOFFSTATUS IN ('C','B')) THEN 'A' ELSE 'B' END AND A.FDOCUMENTSTATUS<>'Z' --AND A.FBILLNO='单据编号' --可根据单据编号更新 ) T2 ON (T1.FID=T2.FID) WHEN MATCHED THEN UPDATE SET T1.FWRITTENOFFSTATUS=T2.NEW;
赞 39
39人点赞
还没有人点赞,快来当第一个点赞的人吧!
打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!
推荐阅读