proc_get_po_number_rev1.0.sql

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

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


create     procedure Proc_Get_PO_Number
@flag        char(1) = null,    --------temporary or post to table Y/N
@identify    varchar(4)  = null, 
@who_used    varchar(15) = null,
@Ord_num_out varchar(12) = null output
as
declare 
@Order_num varchar(12),
@ID  char(4),
@Year char(2),
@maxitem int,
@temp_num varchar(4)
begin

	
	
	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 @id = isnull(IDEN,'PO') from order_number
	select @temp_num = convert(char(4),@maxitem)
 	
	if(@identify = 'POML') 
 	select @Order_num =rtrim(ltrim(@ID))+@year+'-'+ REPLICATE('0',4-len(@temp_num))+@temp_num
	else
	 	select @Order_num =rtrim(ltrim(@ID))+@year+'-'+ REPLICATE('0',5-len(@temp_num))+@temp_num
	select @Ord_num_out = @Order_num

    	if(upper(@flag) = 'Y') 
	begin
		
			update po_order_number
				set Number = Number + 1,
		 		  last_number = @ord_num_out 
				 where IDEN=@identify 
			
		 	
			insert into po_number_list
			(order_number,who_used,used_dt)
			values(@ord_num_out,@who_used,getdate())
	
	
	end
	select @Ord_num_out
end

go