proc_vendor_mstr_upd.sql

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

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

Create Procedure Proc_Vendor_Mstr_Upd
@action_cd 	char(1) = null,
@Vendor_ID  	char(8) = null,
@Status     	char(1) = null,
@title	    	varchar(30)= null,
@Vendor_class   char(1) = null,
@Vendor_nm	varchar(255) = null,
@Vendor_nm1	varchar(255) = null,
@search_terms1	varchar(30) = null,
@search_terms2  varchar(30) = null,
@street		varchar(120) = null,
@house_number   varchar(30) = null,
@postal_code	varchar(7)= null,
@city		varchar(30)= null,
@country	varchar(30) = null,
@language	varchar(30) = null,
@TEL		varchar(30) = null,
@FAX		varchar(30) = null,
@cont1		varchar(30) = null,
@address	varchar(255) = null,
@address1	varchar(255) = null,
@address2	varchar(255) = null,
@deft_Address	varchar(255) = null,
@Deft_Currency  char(3) = null,
@Mult_Currency  bit =0,
@Currency1	char(3) = null,
@Currency2      char(3) = null,
@inv_verify_flag bit = 0,
@MOQ		numeric(18,2) =null,
@Deliv_Plant	char(4) = null,
@Pur_org	char(4) = null,
@Plant		char(4) = null, 
@Pur_grp	char(4) = null,
@payterms	varchar(125) = null,
@Account_no	varchar(8) = null,
@discount	numeric(18,3) = null,
@handling_chrg	numeric(18,3) = null,
@website	varchar(80)=null,
@email		varchar(80) = null,
@Remark		varchar(255) = null,
@controller	varchar(15) = null,
@created_by     varchar(15) = null,
@updated_by	varchar(15) = null,
@returnid	int =null output

as
declare
	@maxitem int

begin
	if(@action_cd  ='N')  
	begin
	begin transaction 
		select @Returnid = -1	--unkown error

		select @maxitem = isnull(max(item),0)+1 from po_vendor_mstr
		exec  Proc_get_vendor_id 'N',@vendor_id output

	insert into po_vendor_mstr
(
item,
Vendor_ID,
Status,
title,
Vendor_class,
Vendor_nm,
Vendor_nm1,
search_terms1,
search_terms2,
street,
house_number,
postal_code,
city,
country,
language,
TEL,
FAX,
cont1,
address,
address1,
address2,
deft_Address,
Deft_Currency,
Mult_Currency,
Currency1,
Currency2,
inv_verify_flag,
MOQ,
Deliv_Plant,
Pur_org,
Plant,
Pur_grp,
payterms,
Account_no,
discount,
handling_chrg,
website,
email,
Remark,
controller,
creation_dt,
created_by,
update_dt,
updated_by
)
values
(
@maxitem,
@vendor_id,
@Status,
@title,
@Vendor_class,
@Vendor_nm,
@Vendor_nm1,
@search_terms1,
@search_terms2,
@street,
@house_number,
@postal_code,
@city,
@country,
@language,
@TEL,
@FAX,
@cont1,
@address,
@address1,
@address2,
@deft_Address,
@Deft_Currency,
@Mult_Currency,
@Currency1,
@Currency2,
@inv_verify_flag,
@MOQ,
@Deliv_Plant,
@Pur_org,
@Plant,
@Pur_grp,
@payterms,
@Account_no,
@discount,
@handling_chrg,
@website,
@email,
@Remark,
@controller,
getdate(),
@created_by,
getdate(),
@updated_by
)
 
select @ReturnID = @@error
if(@@error =0) 
	commit transaction
else
	rollback transaction
select @ReturnID

end
	else if(@action_cd ='M') 
	begin
	   if not exists(select vendor_id from po_vendor_mstr
		where vendor_id = @vendor_id)
	select @ReturnID = -2 ----not vendor found
	else
	begin 
	update po_vendor_mstr
	set 
		Status=@Status,
		title=@title,
		Vendor_class=@Vendor_class,
		Vendor_nm=@Vendor_nm,
		Vendor_nm1=@Vendor_nm1,
		search_terms1=@search_terms1,
		search_terms2=@search_terms2,
		street=@street,
		house_number=@house_number,
		postal_code=@postal_code,
		city=@city,
		country=@country,
		language=@language,
		TEL=@TEL,
		FAX=@FAX,
		cont1=@cont1,		
		address=@address,
		address1=@address1,
		address2=@address2,
		deft_Address=@deft_Address,
		Deft_Currency=@Deft_Currency,
		Mult_Currency=@Mult_Currency,
		Currency1=@Currency1,
		Currency2=@Currency2,
		inv_verify_flag=@inv_verify_flag,
		MOQ=@MOQ,
		Deliv_Plant=@Deliv_Plant,
		Pur_org=@Pur_org,
		Plant=@Plant,
		Pur_grp=@Pur_grp,
		payterms=@payterms,		
		Account_no=@Account_no,
		discount=@discount,
		handling_chrg=@handling_chrg,
		website=@website,
		email=@email,
		Remark=@Remark,
		controller = @controller,
		update_dt=getdate(),
		updated_by=@updated_by
				where vendor_id= @vendor_id

		select @ReturnID = @@Error
	end
		select @ReturnID
end
  else if(@action_cd = 'D') 
	begin
		if (exists(select vendor_id from po_vendor_mstr where vendor_id =@vendor_id) 
		and exists(select vendor_id from po_information_Record where vendor_id =@vendor_id))
		or
		exists(select id from ord_mst where id = @vendor_id)
		select @ReturnID = -3 -- cann't delete vendor ID
		else
		begin
			delete from po_vendor_mstr 
			where vendor_id = @vendor_id
			select @ReturnID = @@error
		end
		select @ReturnID 
	end
end