查询生产单PO的位置

--查询生产单PO的位置

Declare @tmpWrkCtrId VarChar(20)

Declare curWrkCtrID# Cursor For Select WRKCTRID From ACT_JOBTABLE a

       inner join ACT_JOBTRANS b on b.ACT_JobId = a.ACT_JobId

       Where PRODID = 'WR0073262' and WRKCTRID != 'WC-PL' and b.ItemId = 'PO'

       group by WRKCTRID

 

IF EXISTS(SELECT Name FROM Sys.Objects WHERE Object_Id = OBJECT_ID('TempWrkCtr#') AND Type=N'U')

DROP Table TempWrkCtr#

Create Table TempWrkCtr#

(

    WrkCtrId VarChar(20),      

    ProdID Varchar(20),

    Qty int

)

 

Open curWrkCtrID#

Fetch Next From curWrkCtrID# into @tmpWrkCtrId

while @@FETCH_STATUS = 0

Begin     

    insert into TempWrkCtr#

       select x.Wrkctrid as WrkCtrId ,x.ProdId as ProdID,(x.Qty-y.Qty) as Qty

       from

           (select sum(OutQty) as Qty,WrkCtrId,ProdId

              from ACT_JOBTRANS a1 inner join ACT_JOBTABLE b1 on a1.ACT_JobId = b1.ACT_JobId

              where b1.ProdId = 'WR0073262' and a1.ItemId = 'PO' and WrkCtrId = @tmpWrkCtrId

              group by WrkCtrId,ProdId) x,

           (select sum(outQty) as Qty ,FrWrkCtrId,ProdId

              from ACT_JOBTRANS a inner join ACT_JOBTABLE b on a.ACT_JobId = b.ACT_JobId

              where b.FrWrkCtrId = @tmpWrkCtrId and  b.ProdId = 'WR0073262' and a.ItemId = 'PO'

              group by FrWrkCtrId,ProdId) y

       Where x.Qty-y.Qty >0

 

    insert into TempWrkCtr#

       select Wrkctrid,ProdId,OutQty Qty

       from ACT_JOBTRANS b1 inner join ACT_JOBTABLE b2 on b1.ACT_JobId = b2.ACT_JobId

       where b2.ProdId = 'WR0073262' and b1.ItemId = 'PO' and WrkCtrId=@tmpWrkCtrId and WrkCtrId

           not in(select FrWrkCtrId

                  from ACT_JOBTABLE a1 inner join ACT_JOBTRANS b1 on a1.ACT_JobId = b1.ACT_JobId

                  where a1.ProdId = 'WR0073262' and b1.ItemId = 'PO')

 

    Fetch Next From curWrkCtrID# into @tmpWrkCtrId

End

close curWrkCtrID#

Deallocate curWrkCtrID#

 

    select Sum(Qty) as Qty,WrkCtrId,ProdId

    from TempWrkCtr#

    Group by WrkCtrId,ProdId

 

IF EXISTS(SELECT Name FROM Sys.Objects WHERE Object_Id = OBJECT_ID('TempWrkCtr#') AND Type=N'U')

DROP Table TempWrkCtr#