proc_sign_upd.sql

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

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

create procedure Proc_Sign_upd
@action_cd char(1)= NULL,
@order_number varchar(30) = null,
@Signatured_By varchar(15) = null,
@Req_Type      Char(2) = null,
@Requisited_By varchar(15) = null,
@Requisited_Dt datetime = null,
@Short_Text    varchar(800) = null,
@Priority       char(1) = null,
@Reply_notice   char(1) = null,
@job_id		varchar(15) = null

as
begin
	if (@action_cd = 'S') ---send requisite
	begin
		if not exists(select order_number from  
		PO_Signature_List where order_number = @order_number)
		begin
			insert into PO_Signature_List
			(
			Order_number,
Status,
Signatured,
Signatured_dt,
Signatured_By,
Req_Type,
Requisited_By,
Requisited_Dt,
Short_Text,
Priority,
Reply_notice,
Reply_Text,
Reply_dt,
Confirmed,
Confirmed_By,
Confirmed_dt,
Approved,
Approved_By,
Approved_Dt,
job_id
)
values
(			 
@Order_number,
'A',
'Y',
getdate(),
@Signatured_By,
@Req_Type,
@Requisited_By,
@Requisited_Dt,
@Short_Text,
@Priority,
@Reply_notice,
'',
getdate(),
'N',
'',
getdate(),
'N',
'',
getdate(),
@job_id
			
)
 end
	else
	 begin
		 update PO_Signature_List
			set Requisited_By = @Requisited_By,
			    Req_Type = @Req_Type,
			    Requisited_Dt = getdate(),
	    		    Short_Text = @short_text,
			    Priority = @priority,
		 	    Reply_notice = @Reply_notice,
			   job_id = @job_id
			where order_number = @order_number
			
	update ord_mst 
			set confirmed_sig ='',
			    confirmed_by = ''
			Where pono = @Order_number

		
end			
	
	end
		
end
go