Test and debug the procedure

9/9/2009 3:04:09 PM :
    UDPDEPANELBYPS
    @xmlUnitSN = '<?xml version="1.0" encoding="ISO-8859-1"?>', -- (System.String)
    @xmlStation = '<?xml version="1.0" encoding="ISO-8859-1"?>', -- (System.String)
    @EmployeeID = 1000, -- (System.Int32)
    @xmlExtraData = null,
    RETURN VALUE = 280050070





 
 
 
 
 
 
/********************************************************************************************************* 
Author  : Chong Yee Kean 
Creation Date :7/07/2009 10:08 am 
Explanation : Temporarily Workaround fot 2.7603 of depanelization solution. 
Script. Version : FF2_7_6_3 
Parameter :  
ModifiedHistory : Initial 
**********************************************************************************************************/ 
 
Create PROCEDURE [dbo].[udpDepanelByPS]  
 @xmlUnitSN text = null, 
 @xmlProdOrder text = null, 
 @xmlPart text = null, 
 @xmlPackages text = null, 
 @xmlStation text = null, 
 @EmployeeID int = 0, 
 @xmlExtraData text = null, 
 @SNOutput varchar (800) = null OUTPUT 
as  
 
if (getDate()>'2009-11-30 00:00:00.000') 
Begin 
 return 8988312321 
End 
 
 
set @EmployeeID = 1000 -- (System.Int32) 
set @xmlExtraData = null 
 
 
Declare @rec as int 
Declare @xml as varchar(8000) 
Declare @UnitID int 
Declare @Number as varchar(200) 
Declare @NumberType as varchar(200) 
Declare @POID as int 
Declare @PartID as int 
Declare @LooperCount as int 
Declare @EnterTime as Datetime 
Declare @ExitTime as Datetime 
set @EnterTime=getDate() 
 
exec @rec=uspXMLUnit @xmlUnitSN,@UnitID output,@Number output,@NumberType output,@POID output, @PartID output,@LooperCount output 
 
 
declare @xmlPanel as varchar(800) 
declare @BoardQty as int 
 
set @xmlPanel = '<?xml version="1.0" encoding="ISO-8859-1"?>' 
set @xmlPanel =@xmlPanel + '' 
print @UnitID  
--Check Consumption 
if Exists( 
select 1 from  
( 
 select count(PanelSide) as Side,IsSwitchable,LoadingListID from  
 ( 
  select distinct PanelSide,IsSwitchable,LoadingListID from  
  ( 
   select PanelSide,LoadingListMachineID from ffUnitSnapShot where ID in 
   ( 
    select UnitSnapShotID from ffMaterialConsumption where UnitID= @UnitID 
   )  
  ) as t1 
  left join 
  ffLoadingListMachine 
  on 
  ffLoadingListMachine.ID=t1.LoadingListMachineID 
  left join 
  ffLoadingList  
  on  
  ffLoadingListMachine.LoadingListID=ffLoadingList.ID 
 ) as f1 
 group by IsSwitchable,LoadingListID 
)as f1 where Side-1<>IsSwitchable 
) 
begin  
 return 600100300 
end  
 
 
 
--Extra BoardQty from Loading List 
select @BoardQty=BoardQty from ffLoadingList(NoLock) where ID in 
( 
 SELECT LoadingListID FROM ffLoadingListMachine(NoLock) where ID in 
 ( 
  select LoadingListMachineID from ffUnitSnapShot(nolock) where ID in 
  ( 
   select UnitSnapShotID from ffMaterialConsumption(nolock) where UnitID=@UnitID  
 
  ) 
 ) 
) 
print  @BoardQty 
/*Change Board Qty in Cross Board Case*/ 
 
if exists(select 1 from udtEXT1CrossBoardUnit(nolock) where UnitID=@UnitID) 
Begin 
 Declare @l as varchar(100) 
 select  @l =CrossBoard from udtEXT1CrossBoardUnit(nolock) where UnitID=@UnitID 
 Declare @counter as int 
 Declare @i as int 
 set @i=1; 
 set @counter=0 
 if len(rtrim(ltrim(@l)))>=1 
 Begin 
  set @counter=1 
 End 
 while @i begin 
  if substring(@l,@i,1)=',' 
  begin 
   set @counter=1+@counter 
  end 
  set @i=@i+1 
 end  
 if @counter>0  
 begin  
  set @BoardQty=@BoardQty-@counter 
 end  
End 
 
 
 
--Extra Depanel Part ID (UC) 
Declare @DepanelPartID as int 
Declare @DepanelPartNumber as varchar(200) 
Declare @Revision as varchar(200) 
Declare @SNFormat as varchar(200) 
 
/*Get From Product Structure*/ 
select @DepanelPartID=ID,@DepanelPartNumber=PartNumber,@Revision=Revision from ffPart(nolock) where ID in  
( 
 select ParentPartID from ffProductStructure(Nolock) where PartID=@PartID  
) 
 
if isnull(@DepanelPartID,'')=''  
Begin 
 select @DepanelPartID=ID,@DepanelPartNumber=PartNumber,@Revision=Revision from ffPart(nolock) where ID=@PartID  
End 
 
 
select @SNFormat=Content from ffPartDetail(Nolock)  where PartDetailDefID in ( 
select ID from luPartDetailDef(Nolock) where Description='SNFormat') and PartID=@DepanelPartID 
 
 
 
Declare @Format as varchar(200) 
select @Format=Content from ffPartDetail(Nolock)  where PartDetailDefID in ( 
select ID from luPartDetailDef(Nolock) where Description='Format') and PartID=@DepanelPartID 
 
if (isnull(@Format,''))='Y'  
Begin  
 set @Format='' 
end 
else 
Begin 
 if(isnull(@Format,'')='') 
 Begin 
  set @Format='Format="' 
  declare @j int 
  set @j=1 
  while @j<=len(@Number) 
  Begin 
   set @Format=@Format +'?' 
   set @j=@j+1 
  end  
  set @j=0 
  while @j<=60 
  Begin 
   set @Format=@Format+'@' 
   set @j=@j+1 
  end  
  set @Format=@Format+'"'  
 End   
 else 
 Begin 
  set @Format='Format="' + @Format+'"'  
 end 
End 
 
 
set @xmlPanel =@xmlPanel + '' 
set @xmlPanel =@xmlPanel + '' 
 
 
set @ExitTime=getDate() 
Begin Tran 
 
/*Register Panel and Board If Board is not created*/ 
if not exists(select 1 from ffUnit(Nolock)  where PanelID=@UnitID) 
Begin 
exec @rec=OBJTSKREGISTERPANEL 
  @xmlPanel, 
  False, -- (System.Boolean) 
  False, -- (System.Boolean) 
  @xmlStation, 
  @EmployeeID, 
  0, 
  @EnterTime, 
  @ExitTime 
 
 if @rec<>0  
 begin 
  rollback 
  return @rec 
 end  
End 
 
 
 
 
Declare @StationID int 
exec @rec=uspXMLStation @xmlStation,@StationID output,null,null 
 
if @rec<>0  
begin 
 rollback 
 return @rec 
end  
 
exec @rec=UDPEXT1DEPANEL2PANELSN 
 @UnitID, -- (System.Int32) 
 '', -- (System.Int32) 
 @StationID, -- (System.Int32) 
 @EmployeeID, -- (System.Int32) 
 @EnterTime -- (System.String) 
 if @rec<>0  
 begin 
  rollback 
  return @rec 
 end  
Commit 
 return @rec 
 
/* 
 UDPDEPANELBYPS 
 @xmlUnitSN = '<?xml version="1.0" encoding="ISO-8859-1"?>', -- (System.String) 
 @xmlStation = '<?xml version="1.0" encoding="ISO-8859-1"?>', -- (System.String) 
 @EmployeeID = 1000, -- (System.Int32) 
 @xmlExtraData = null, 
 RETURN VALUE = 10000 
*/ 
/* 
 OBJTSKREGISTERPANEL 
 @xmlPanel = '<?xml version="1.0" encoding="ISO-8859-1"?>', -- (System.String) 
 @RetainWastedSN = False, -- (System.Boolean) 
 @AssignToPO = False, -- (System.Boolean) 
 @xmlStation = '<?xml version="1.0" encoding="ISO-8859-1"?>', -- (System.String) 
 @EmployeeID = 1000, -- (System.Int32) 
 @IsDePanelize = 1, -- (System.Int32) 
 @EnterTime = '2009-7-6 9:20:52.795', -- (System.String) 
 @ExitTime = '2009-7-6 9:20:52.795', -- (System.String) 
 RETURN VALUE = 280000030 
 
*/ 
 
 
 
 
 
 
 
 

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

转载于:http://blog.itpub.net/445851/viewspace-614287/