proc_job_mstr_upd.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.Proc_Job_Mstr_Upd') is not null
drop procedure Proc_Job_Mstr_Upd
go

create procedure Proc_Job_Mstr_Upd
@action_cd      char(1) = null,
@JOB_ID 	varchar(15) = null output,
@JOB_Type       CHAR(3) = null,
@JOB_Title	varchar(80) = null,
@Priority	char(1) = null,
@TO_Rcv		varchar(15) = null,
@From_By	varchar(15) = null,
@CC_To		varchar(15) = null,
@Ask_Context    varchar(800) = null,
@Reply_Context	varchar(800) = null,
@Reply_by	varchar(15) = null,
@Created_by     varchar(15) = null,
@Updated_by	varchar(15) = null
as
declare
@Year char(2),
@mm   char(2),
@dd   char(2),
@maxitem int,
@temp_num varchar(3)
begin
	if(@action_cd ='N')
	begin
	 begin transaction 
	
		select @maxitem =  isnull(Number,1) from PO_Job_Number where flag ='JOB'

	 	select @Year = isnull(right(convert(char(4),datepart(YY,getdate())),2),'05')
		select @mm = isnull(right(convert(char(2),datepart(mm,getdate())),2),'01')
		select @dd = isnull(right(convert(char(2),datepart(dd,getdate())),2),'01')
		--select @identify = isnull(IDEN,'PO') from order_number
		select @temp_num = convert(char(4),@maxitem)
 	
		select @JOB_ID ='JOB'+rtrim(@year)+rtrim(@mm)+rtrim(@dd)+'-'+ REPLICATE('0',3-len(@temp_num))+@temp_num
		--lect @JOB_ID
		
		insert into GL_JOB_Mstr
		(JOB_ID,
Version,
JOB_Type,
JOB_Title,
Priority,
TO_Rcv,
From_By,
CC_To,
Status,
Ask_Context,
Reply_Context,
Reply_by,
Reply_dt,
Creation_dt,
Created_by,
update_dt,
Updated_by
)values
(
@JOB_ID,
1.0,
@JOB_Type,
@JOB_Title,
@Priority,
@TO_Rcv,
@From_By,
@CC_To,
'N',
@Ask_Context,
'Not Reply',
'Null',
getdate(),
getdate(),
@Created_by,
getdate(),
@Updated_by
)
update PO_Job_Number
	set number = number+1,
            last_number = @job_id
	where flag ='JOB'

select @JOB_ID

 if(@@error=0) 
commit tran
else
rollback tran

end
	else if(@action_cd ='M')  -- modify
	begin
		update GL_JOB_Mstr
	set Version=version + 1,
	JOB_Type=@JOB_Type,
	JOB_Title=@JOB_Title,
	Priority=@Priority,
	TO_Rcv=@TO_Rcv,
	From_By=@From_By,
	CC_To=@CC_To,
	Ask_Context=@Ask_Context,
	update_dt=getdate(),
	Updated_by=@Updated_by	
	where JOB_ID=@JOB_ID and status ='N'

	select @JOB_ID
end
else if(@action_cd ='R')  --Reply
	begin
		update GL_JOB_Mstr
		set Reply_Context= @Reply_Context,
		Reply_by = @Reply_by,
		status = 'Y',
		reply_dt=getdate(),
		updated_by = @Updated_by,
		update_dt = getdate()
		where JOB_ID=@JOB_ID and status ='N'

	select @JOB_ID
end
end
go