ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次更新出库批次(多条入库,一条出库)

一次更新出库批次(多条入库,一条出库)

原创 Linux操作系统 作者:djs00717 时间:2009-03-04 20:35:45 0 删除 编辑

----查找重复流水
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

 


*/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11996652/viewspace-561423/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-02-13

  • 博文量
    1
  • 访问量
    823