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


create function fn_get_schema_price(
	@branch_no		varchar(6), -- 调出
    @d_branch_no	varchar(6), -- 调入 
    @sheet_branch	varchar(6),
	@item_no		varchar(20)
)
returns numeric(16, 4)
begin
	declare 
        @price_value_branch		varchar(6),
        @price_option_branch	varchar(6),
		@price			numeric(16, 4),
		@price_option	char(1),
        @f_trade_type		char(1),
        @t_trade_type		char(1),
        @Flag				char(1),
        @price_op_temp      char(1),
		@price_ratio		numeric(16, 4)--加价率在成本上

		/*--------------------
		trade_type：0.总部  3.自营店  4.加盟店  5.仓库
		----------------------*/
		
        select @price_value_branch = left(@branch_no,3)
        select @price_option_branch = left(@d_branch_no,3)
		select @Flag ='1'	

	    select @price_option = price_option,@price_ratio=price_ratio from t_bd_branch_info where branch_no = @price_option_branch
        if @price_option = '1'	--进价
        begin
          select @price = price from t_pc_branch_price where item_no = @item_no and branch_no = @price_value_branch
          if @price is null 
              select @price = price from t_bd_item_info  where item_no = @item_no
        end
        else if @price_option ='2'	--加价率
			begin
			 if @price_ratio=0 
				select @price_ratio=1

          	 select top 1 @price = avg_cost * @price_ratio from t_im_branch_stock where item_no = @item_no and left(branch_no,3) = @price_value_branch
			end
		else if @price_option = '3'	--配送价
        begin
		 select @price = scheme_price from t_pc_branch_price where item_no = @item_no and branch_no = @price_option_branch
			
           if @price is null and @Flag ='1'
             select @price = scheme_price from t_bd_item_info  where item_no = @item_no
        end
		else if @price_option = '4'	--零售价
        begin
           select @price = sale_price from t_pc_branch_price where item_no = @item_no and branch_no = @price_value_branch
          if @price is null and @Flag ='1'
             select @price = sale_price from t_bd_item_info  where item_no = @item_no
        end 
		else if @price_option ='5'	--成本价
		begin
           select top 1 @price = avg_cost from t_im_branch_stock where item_no = @item_no and left(branch_no,3) = @price_value_branch
		end       
        else
           select @price = 0
	if @price is null 
		set @price = 0

	return @price
end
go