proc_im_log_upd.sql

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

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

Create procedure Proc_IM_Log_Upd
@action_cd char(1) = null,
@Item	int =null,
@Plant	char(4) = null,
@Version numeric(3,1)=null,
@Status	 char(1) = null,
@Priority char(1) =null,
@IM_DT	  datetime = null,
@Vendor_id	varchar(10) = null,
@ISSUE_TYPE	varchar(3) = null,
@DN_Number	varchar(30) = null,
@Order_Number	varchar(30) = null,
@hpn		varchar(18) = null,
@cpn		varchar(30) = null,
@Buyer		varchar(15) = null,
@Report_dt	datetime =null,
@Rcv_dt		datetime = null,
@Reply_by	varchar(15) = null,
@Remark		varchar(120)= null,
@Created_by	varchar(15) = null,
@Updated_by	varchar(15) = null,
@msg 		varchar(80) output
as
	
declare
	@maxitem int
begin
 if(@action_cd ='N') 
	begin
		select @maxitem = isnull(max(item),0)+1 from im_log_mstr  
	insert into im_log_mstr
(
Item,
Plant,
Version,
Status,
Priority,
IM_DT,
Vendor_id,
ISSUE_TYPE,
DN_Number,
Order_Number,
hpn,
cpn,
Buyer,
Report_dt,
Rcv_dt,
Read_flag,
Reply_flag,
Reply_dt,
Reply_by,
Remark,
Created_by,
Creation_dt,
Updated_by,
Update_dt
)
values
(
@maxitem,
@Plant,
@Version,
'A',
@Priority,
@IM_DT,
@Vendor_id,
@ISSUE_TYPE,
@DN_Number,
@Order_Number,
@hpn,
@cpn,
@Buyer,
@Report_dt,
@Rcv_dt,
'N',
'N',
getdate(),
@Reply_by,
@Remark,
@Created_by,
getdate(),
@Updated_by,
getdate()
)
 if(@@error=0) 
	Select @Msg ='Save Im Log Record Successfully!'
else
   select @Msg ='Save Im Log Record Faild!'
select @msg

end
else
   if(@action_cd ='M') 
begin
	
	update im_log_mstr
		set 
Plant=@Plant,
Version=@Version,
Status=@Status,
Priority=@Priority,
IM_DT=@IM_DT,
Vendor_id=@Vendor_id,
ISSUE_TYPE=@ISSUE_TYPE,
DN_Number=@DN_Number,
Order_Number=@Order_Number,
Buyer=@Buyer,
Report_dt=@Report_dt,
Rcv_dt=@Rcv_dt,
Reply_by=@Reply_by,
Remark=@Remark,
Created_by=@Created_by,
Updated_by=@Updated_by,
Update_dt=getdate(),
hpn=@hpn,
cpn=@cpn
where item =@item
	 if(@@error=0) 
	Select @Msg ='Save Im Log Record Successfully!'
else
   select @Msg ='Save Im Log Record Faild!'
select @msg


end
end