# 一次更新出库批次（多条入库，一条出库）

----查找重复流水
SELECT * FROM
(select a.docno,b.pluid,sum(B.QTY) AS QTY,COUNT(1) AS DCNT
from doc01bf a,doc01dt b
where a.docno=b.docno and a.ctyp=3 and a.valid=1
group by a.docno,b.pluid)  A Left oUTER JOIN
(SELECT DOCNO, PLUID,SUM(QTY) AS PQTY ,COUNT(1) AS PCNT,MIN(CONVERT(INTEGER,ISFINISH)) AS ISH ,MAX(FQTY) AS FQTY
FROM FIFOIEBK WHERE DOCTYP='0103' GROUP BY DOCNO,PLUID) B
ON(A.DOCNO=B.DOCNO AND A.PLUID=B.PLUID)
WHERE A.QTY<>ISNULL(B.PQTY,0)

---查找rid，并确认是否完结
SELECT rid,qty,eqty,fqty,isfinish,fid FROM FIFOIEBK WHERE DOCNO='210809151260919' and pluid=7789

----查看是否有【要处理的rid】出库批次
select * from fifooebk where rid in(178909)

-----********没有《查看是否有【要处理的rid】出库批次》执行以下语句
update fifoiebk set isfinish=1,eqty=0,qty=0,fqty=0,csamt=0,nocsamt=0,taxamt=0
where rid in(182765,182766)
-----********

-----******************************有《查看是否有【要处理的rid】出库批次》执行以下语句
---查找0厂商的rid
select * from fifoiebk where comid=0

---更新销售表位0厂商
update sales set comid=0
where docno in(select docno from fifooebk where rid in (181757,181758) ) and pluid=1686
update fifooebk set rid=108147,comid=0 where rid in(181757,181758)
----更新入批次为0
update fifoiebk set isfinish=1,eqty=0,qty=0,fqty=0,csamt=0,nocsamt=0,taxamt=0
where rid in(181757,181758)
--****************************有《查看是否有【要处理的rid】出库批次》执行以下语句*******

/*

----更新出库批次，
UPDATE A SET FQTY= TABLE1.FQTY,ISFINISH=CASE WHEN TABLE1.FQTY=0 THEN 1 ELSE 0 END
FROM FIFOIEBK A,(select q.rid,PLUID,q.fqty
from (select k.rid,PLUID,case when k.fqty1 < k.fqty then k.fqty1 else k.fqty end as fqty
from (select e.rid,PLUID,e.fqty,case when e.t                              from (select rid as rid , PLUID,QTY,fqty as fqty,
fqty + isnull((select sum(fqty) from fifoiebk  where rid < a.rid AND PLUID=A.PLUID ),0) as t
from (SELECT b.rid,T1.PLUID,T1.QTY,B.FQTY
FROM

-----------------要核销入库的商品和数量
(SELECT A.PLUID ,SUM(A.QTY)/2 AS QTY,MAX(PLUNO) AS PLUNO,MAX(PLUNAME) AS PLUNAME
FROM (
select a.docno,B.pluid,FIFOuid,SUM(B.QTY) AS QTY from doc01bf a,doc01FIFO b,DOC01DT C
where a.docno=b.docno AND A.DOCNO=C.DOCNO AND C.PLUID=B.PLUID AND C.SID=B.SID and a.ctyp=3 and a.valid=1
group by a.docno,B.pluid,FIFOuid
having count(*)>1 AND SUM(B.QTY)<>SUM(C.QTY)) A,BASPLUMAIN B
WHERE A.PLUID=B.PLUID
GROUP BY A.PLUID) T1 LEFT JOIN FIFOIEBK B ON T1.PLUID=B.PLUID AND B.ISFINISH=0
) a

-----------------要核销入库的商品和数量
)e
)k
) q
) TABLE1
WHERE A.RID=TABLE1.RID AND A.PLUID=TABLE1.PLUID AND A.ISFINISH=0

*/

• 博文量
1
• 访问量
841