proc_order_header_upd_rev1.0.sql

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

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

Create procedure Proc_Order_Header_Upd
@identify    varchar(4)  = null, 
@Order_Number_out varchar(30)=null output,
@Order_type CHAR(4) = NULL,
@vendor_id	char(8)=null,
@vendor_nm varchar(255) = null,
@customer_id CHAR(5) = NULL,
@cust_nm   varchar(80) = null,
@prno	varchar(30)=null,
@discount numeric(4,2)=null,
@currency  CHAR(3) = NULL,
@payterms  varchar(120) = null,
@deli_loc varchar(80)=null,
@remark	  varchar(255)=null,
@post_date datetime=null,
@Creator   char(10)=null,
@last_modify char(10)= null,
@linkman    varchar(30) = null,
@telephone    varchar(15) = null,
@fax	     varchar(15) = null
as
declare 
@Order_num varchar(12),
@Year char(2),
@maxitem int,
@temp_num varchar(4)
begin

  begin transaction 

	select @maxitem =  isnull(Number,1) from PO_Order_Number where IDEN= @identify 

 	select @Year = isnull(right(convert(char(4),datepart(YY,getdate())),2),'04')
	--select @identify = isnull(IDEN,'PO') from order_number
	select @temp_num = convert(char(4),@maxitem)
 	
	if(@identify = 'POML') 
 	select @Order_num =rtrim(ltrim(@identify))+@year+'-'+ REPLICATE('0',4-len(@temp_num))+@temp_num
	else
	 	select @Order_num =rtrim(ltrim(@identify))+@year+'-'+ REPLICATE('0',5-len(@temp_num))+@temp_num
	select @Order_Number_out = @Order_num

 
		update po_order_number
				set Number = Number + 1,
		 		  last_number = @Order_Number_out 
				 where IDEN=@identify 
			
		 	
			insert into po_number_list
			(order_number,who_used,used_dt,reply,reply_dt)
			values(@Order_Number_out,@creator,getdate(),0,getdate())

		insert into ord_mst(
			pono,
			Order_type,
			id,
			vendor_nm,
			customer_id,
			cust_nm,
			prno,
			discount,
			currency,
			payterms,
			deli_loc,
			post_date,
			last_up,
			remark,
			Creator,
			Last_modify,
			version,
			rev_date,
			status,
			revsion_by,
			linkman,
			telephone,
			fax)
		values(
	upper(@Order_Number_out),
	@order_type,
	@vendor_id,
	@vendor_nm,
	@customer_id,

	@cust_nm,
	upper(@prno),
	@discount,
	@currency,
	@payterms,
	@deli_loc,
	getdate(),
	getdate(),
	@remark,
	@Creator,
	@Last_modify,
	1.0,
	getdate(),
	'O',
	@creator,
	@linkman,
	@telephone,
	@fax)
   
	if (@@error=0) 
	commit transaction
	else
	 Rollback Transaction
	select @Order_number_out
end

GO