
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[f_variable_used_by_policy_or_risk] 
(
	@product_code as ais_code_short,
	@variable_code as ais_code_short,
	@variable_date_setup as datetime
)  
RETURNS BIT AS  
BEGIN 


	/*
	autoriser la modification des variables, même si des contrats les référencent
	*/
	if	exists (	select	1
					from	ais_options
					where	opt_allow_update_variables != 0 )
			return 0

	DECLARE	@variable_id INT
	
	SELECT  @variable_id = prdv_variable_id 
	FROM    pr_products_variables 
	where	prdv_product_code = @product_code 
	and		prdv_variable_code = @variable_code 
	and		prdv_variable_date_setup = @variable_date_setup 

	-- check to see if the variable is used in a policy situation
	IF EXISTS(
			SELECT	1
			FROM	pol_variables
			WHERE	polv_variable_id = @variable_id )
		RETURN 1
		
	-- check to see if the variable is used in a risk situation
	IF EXISTS(
			SELECT	1
			FROM	ris_variables
			WHERE	risv_variable_id = @variable_id )
			
		RETURN 1
		
	RETURN 0
		
	

END




GO
