proc_get_po_number.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

	
	if(upper(@identify)='POM') 
	begin
	select @id = 'POM'
	select @maxitem =  isnull(Number,1) from PO_Order_Number where IDEN='POM' 
	end
	else if (@identify ='POL')
	begin
		select @id = 'POL'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='POL' 
	end
	else if(@identify='POML')
        begin
		select @id = 'POML'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='POML' 
	end
	else
	begin
		select @id = 'PO'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='PO' 
	end
 	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
		
			if(upper(@identify) ='POM')
			begin	
				update po_order_number
				set Number = Number + 1,
		 		  last_number = @ord_num_out 
				 where IDEN='POM' 
			end
			else if(@identify = 'POL')
			begin
				update po_order_number
				set Number = Number + 1,
				last_number = @ord_num_out
				where IDEN='POL'
			end
			else if(@identify = 'POML')
			begin
				update po_order_number
					set Number = Number + 1,
					last_number = @ord_num_out
					where IDEN='POML'
			end
			else
			begin
				update po_order_number
					set Number = Number + 1,
					last_number = @ord_num_out
					where IDEN='PO'
			end
		 	
			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