操作系统学习网

–声明变量–Declare BBB scroll Cursor for Select*from#AAA Order by nLevel–建立一个Cursor Declare@S1 varchar(20),@S2 varchar(2000),@S3 int,@S4 int,@S5 float(10),@S6 float(10),@S7 datetime,@S8 datetime,@S9 varchar(30),@S10 bit,@istate as int,@Dep varchar(20),@Day varchar(10),@cCheckPerson as varchar(20),@dCheckDate varchar(10)–OPEN BBB–打开Cursor BBB FETCH NEXT FROM BBB INTO@S1,@S2,@S3,@S4,@S5,@S6,@S7,@S8,@S9,@S10–到第一条WHILE@FETCH_STATUS=0–循环BEGIN if@S10=0 IF@S3=1–@S3=nBoots=0,9为已经判断产品BEGIN IF(SELECT Count(*)From ProductStructures Where cPSPCode=@S1)0–如果有子产品，取出到#AAA BEGIN if@nDay=0 begin Insert#AAA(cInvCode,nLevel,nBoots,nStep,iMQTY,iInvAdvance,dStartDate,dPlanFinishDate,cDepName,bPurchase)SELECT ProductStructures.cPSCode as cInvCode,@S2+cPSCode as nLevel,1 as nBoots,@S4+1 as nStep,ProductStructures.iPSQuantity*@S5 as iMQTY,Inventory.iInvAdvance,@S7 as dStartDate,@S8 as dPlanFinishDate,Department.cDepName,Inventory.bPurchase FROM(Inventory INNER JOIN ProductStructures ON Inventory.cInvCode=ProductStructures.cPSCode)LEFT JOIN Department ON ProductStructures.cDepCode=Department.cDepCode Where cPSPCode=@S1 end else begin Insert#AAA(cInvCode,nLevel,nBoots,nStep,iMQTY,iInvAdvance,dStartDate,dPlanFinishDate,cDepName,bPurchase)SELECT ProductStructures.cPSCode as cInvCode,@S2+cPSCode as nLevel,1 as nBoots,@S4+1 as nStep,ProductStructures.iPSQuantity*@S5 as iMQTY,Inventory.iInvAdvance,@S7 as dStartDate,dateadd(day,-isnull(iInvAdvance,0),@S8)as dPlanFinishDate,Department.cDepName,Inventory.bPurchase FROM(Inventory INNER JOIN ProductStructures ON Inventory.c InvCode=ProductStructures.cPSCode)LEFT JOIN Department ON ProductStructures.cDepCode=Department.cDepCode Where cPSPCode=@S1 end Update#AAA Set nBoots=9 Where nLevel=@S2–把此级的nBoots=9有下级不再检查CLOSE BBB OPEN BBB–关上重新打开FETCH NEXT FROM BBB INTO@S1,@S2,@S3,@S4,@S5,@S6,@S7,@S8,@S9,@S10–到下一条END ELSE BEGIN Update#AAA set nBoots=0 Where nLevel=@S2–没下级，把此级的nBoots=0不再检查FETCH NEXT FROM BBB INTO@S1,@S2,@S3,@S4,@S5,@S6,@S7,@S8,@S9,@S10–到下一条END END ELSE FETCH NEXT FROM BBB INTO@S1,@S2,@S3,@S4,@S5,@S6,@S7,@S8,@S9,@S10–检查过，到下一条else begin Update#AAA set nBoots=0 Where nLevel=@S2–没下级，把此级的nBoots=0不再检查FETCH NEXT FROM BBB INTO@S1,@S2,@S3,@S4,@S5,@S6,@S7,@S8,@S9,@S10–到下一条end END CLOSE BBB DEALLOCATE BBB–关上BBB SET NOCOUNT OFF If@nWhich=1 begin SELECT#AAA.*,In ventory.cInvName,Inventory.cInvStd,Inventory.cInvM_Unit as cUnit,Inventory.iInvSPrice as Price FROM#AAA,Inventory WHERE Inventory.cInvCode=#AAA.cInvCode order by nlevel end if@nWhich=2 begin BEGIN TRANSACTION if(select count(*)from PP_RMRPmain where cMPID=@MPID)0 begin set@istate=(select istate from PP_RMRPmain where cMPID=@MPID)set@Dep=(select cDepCode from PP_RMRPmain where cMPID=@MPID)set@day=(select convert(varchar,dCreatDate,120)from PP_RMRPmain where cMPID=@MPID)set@cCheckPerson=(select cCheckPerson from PP_RMRPmain where cMPID=@MPID)set@dCheckDate=(select convert(varchar,dCheckDate,120)from PP_RMRPmain where cMPID=@MPID)end else begin set@istate=1 set@Dep=@DepCode set@day=@date set@cCheckPerson=’’set@dCheckDate=”end delete from PP_RMRPdetails where cMPID=@MPID IF@ERROR 0begin Rollback transaction Return end delete from PP_RMRPmain where cMPID=@MPID IF@ERROR 0begin Rollback transaction Return end Insert into PP_RMRPmain(cMPID,dCreatDate,cDepCode,cCheckPerson,dCheckDate,iState)Values(@MPID,@Date,@DepCode,@cCheckPerson,@dCheckDate,@istate)IF@ERROR 0begin Rollback transaction Return end insert PP_RMRPdetails(cMPID,cInvCode,cProjectCode,cUnit,iMQTY,dRequireDate)SELECT@MPID as cMPID,#AAA.cInvCode,”as cProjectCode,Inventory.cInvM_Unit as cUnit,sum(iMQTY)as iMQTY,#AAA.dPlanfinishDate FROM#AAA,Inventory WHERE Inventory.cInvCode=#AAA.cInvCode and nBoots=0 group by#AAA.cInvCode,Inventory.cInvM_Unit,#AAA.dPlanfinishDate—修改相关计划状态—Update PP_MPSmain set iState=3 Where cMPID=@MPID IF@ERROR 0begin Rollback transaction Return end COMMIT TRANSACTION end if@nWhich=3 begin SELECT#AAA.cInvCode,Inventory.cInvName,Inventory.cInvStd,Inventory.cInvM_Unit as cUnit,sum(iMQTY)as iMQTY,#AAA.dPlanfinishDate FROM#AAA,Inventory WHERE Inventory.cInvCode=#AAA.cInvCode and nBoots=0 group by#AAA.cInvCode,Inventory.cInvName,Inventory.cInvStd,Inventory.cInvM_Unit,#AAA.dPlanfinishDate end DROP TABLE#AAA GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 2、文章标题：U8网络版产品《采购计划》中独立需求只能由一个人做，不可多个人同时做的问题。解决办法：将ufsystem中表ua_control中cAuth_Id=’PP 020300′and cRepellent=’PP 020300′的记录删掉即可。