
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ================================================================================================================================================================================
-- Author:		Laba Catalin
-- Create date: 20/12/2011
-- Description:	Returns the products variables taxes at a specific date. To obtain all fields you must join with table acc_taxes on acctax_tax_code and acctax_date_setup
-- ================================================================================================================================================================================
CREATE function [dbo].[f_pr_products_variables_taxes]
(
	@product_code ais_code_short,
	@date_setup date,
	@variable_code ais_code_short = '%' 
) 
returns @taxes table 
						(
							variable_id int,
							tax_id int,
							product_code ais_code_short,
							variable_code ais_code_short,
							variable_date_setup date,
							tax_code ais_code_short,
							tax_date_setup date
						)
as 
begin

	insert into @taxes(
			variable_id,	tax_id,			product_code,			variable_code,			variable_date_setup,		tax_code,			tax_date_setup)
	select	variable_id,	acctax_tax_id,	prdv_product_code,		prdv_variable_code,		prdv_variable_date_setup,	acctax_tax_code,	acctax_tax_date_setup
	from pr_products_variables
		inner join (
					select	variable_id = prdvt_variable_id, 
							tax_code = prdvt_tax_code,
							tax_date_setup = max(acctax_tax_date_setup)
					from dbo.f_pr_products_variables('%', @product_code, @date_setup) fv
						inner join pr_products_variables_taxes on	prdvt_variable_id = fv.variable_id
						inner join acc_taxes on prdvt_tax_code = acctax_tax_code
					where	(  @variable_code = '%' or variable_code = @variable_code )
							and acctax_tax_date_setup <= @date_setup -- we search the max date setup for the tax before the passed parameter
					group by prdvt_variable_id, prdvt_tax_code ) as t on prdv_variable_id = variable_id
		
		inner join acc_taxes on		acctax_tax_code = tax_code 
								and	acctax_tax_date_setup = tax_date_setup
	
	return;
	
end




GO
