01.集成项目号信息进入金蝶K3

CREATE PROCEDURE [dbo].[sp_addProjectNumber]	
	@FNumber varchar(80),
	@FName varchar(255)
AS
BEGIN
	SET NOCOUNT ON;

	declare @Result int;
	set @Result = -1;

	declare @FUserName varchar(100);
	set @FUserName = 'Administrator';

	declare @FUserId int;
	select @FUserId = FUserID from t_Base_User where FName = @FUserName;

    -- Insert statements for procedure here
	if not exists(select FItemID from dbo.t_Item where FNumber =  @FNumber)

	begin

	begin transaction Tran_Project_Number

		declare @MaxObjID int;
		declare @tran_error int;
		set @tran_error = 0;

		begin try

			select @MaxObjID = FNext from t_identity where FName='t_Item'  


			INSERT INTO dbo.t_Item
			(
			FItemID,FItemClassID,FExternID,FNumber,FParentID,FLevel,FDetail,FName,FUnUsed,FBrNo,FFullNumber,FDiff,FDeleted,FShortNumber,FFullName,FGRCommonID,FSystemType,FUseSign,FChkUserID,FAccessory,FGrControl,FHavePicture
			)
			VALUES
				(

					@MaxObjID,'3003','-1',@FNumber,'0','1','1',@FName,'0','0',@FNumber,'0','0',@FNumber,@FName,'-1','1','0',@FUserId,0,'-1',0
			);

			INSERT INTO t_Item_3003(
				FitemID,
				FNumber,
				FName,
					F_105,
					F_106
			)
			VALUES
				(
						@MaxObjID,
					@FNumber,
					@FName,
							null,
							0
				);

			Insert Into t_BaseProperty(FTypeID, FItemID, FCreateDate, FCreateUser, FLastModDate, FLastModUser, FDeleteDate, FDeleteUser)
			Values(3, @MaxObjID, GETDATE(), @FUserName, Null, Null, Null, Null) 

		end try

		BEGIN CATCH
		set @tran_error=@tran_error+1; 
		END CATCH

		if ( @tran_error > 0 )
			begin
				--执行出错，回滚事务
				rollback tran;				
				print 'Failed to insert prject number!';
			end;
		else
			begin
				--没有异常，提交事务
				commit tran;
				set @Result = @MaxObjID;
				print 'Succeed to insert prject number!';
			end;

	end
	else 
	begin
		select @Result = FItemID from dbo.t_Item where FNumber =  @FNumber
	end 

	return @Result;
END
GO