if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_3600_get_spec_price]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_3600_get_spec_price]
GO


create  function f_3600_get_spec_price
(	
	@branch_no varchar(8),-- 调出机构编码 	
	@item_no varchar(20), -- 商品货号
	@getdate datetime	  -- 传值应传当前时间，此种处理主要是因为2000数据库的函数中无法使用getdate()函数
	--成功则返回商品单价，否则返回零。 
)
returns numeric(16, 4)
begin
	declare @item_clsno varchar(20), @item_brand varchar(20), @spec_price decimal(16,4)

	select @item_clsno = item_clsno, @item_brand = item_brand from t_bd_item_info where item_no = @item_no 

	if not exists(select 1 from t_rm_spec_price 
			where item_no = @item_no 
			and branch_no = @branch_no
			and convert(char(10), @getdate, 120) between start_date and end_date)
	return @spec_price


--	print @item_clsno
	set @spec_price = 
	(
		select top 1 spe_price
		from
		(	select item_no, spe_price 
			from t_rm_spec_price 
			where item_no = @item_no 
			and branch_no = @branch_no
			and convert(char(10), @getdate, 120) between start_date and end_date
			and left(other3, '2') = 'PS'
			union
			select item_no, spe_price 
			from t_rm_spec_price 
			where item_no = @item_no 
			and branch_no = @branch_no
			and convert(char(10), @getdate, 120) between start_date and end_date
			and convert(char(8), @getdate, 108) between begin_time and end_time
			and left(other3, '2') = 'PE'
			union 
			select @item_no, spe_price = a.discount * b.sale_price
			from t_rm_spec_price a, t_pc_branch_price b
			where a.branch_no = b.branch_no 
			and a.branch_no = @branch_no
			and b.item_no = @item_no
			and left(@item_clsno, len(a.item_no)) = a.item_no 
			and start_date <= convert(char(10), @getdate, 120) and end_date >= convert(char(10), @getdate, 120)
			and a.special_type in ('6')
			union 
			select @item_no, spe_price = a.discount * b.sale_price
			from t_rm_spec_price a, t_pc_branch_price b
			where a.branch_no = b.branch_no 
			and a.branch_no = @branch_no
			and b.item_no = @item_no
			and left(@item_brand, len(a.item_no)) = a.item_no 
			and start_date <= convert(char(10), @getdate, 120) and end_date >= convert(char(10), @getdate, 120)
			and a.special_type in ('5')
			 
		) a
		order by a.spe_price
	)
	return @spec_price
end
GO

