SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_grid_act_branches_pr_families_cies]
	@branch_id		as int,
	@family_code	as ais_code_short = '%',
	@treaty			as varchar(1) = '%',		-- 1 (seulement les traités)/ 0 (seulement les compagnies qui ne sont pas traités) / % (ttes les compagnies)
	@company_id		varchar(15) = '%'
AS

set nocount on;

declare @id int 

if isnumeric(@company_id) = 1 set @id = convert(int,@company_id)

select	*
from	act_branches_cies abc
		inner join vw_act_cies vac on vac.cie_actor_id = abc.brc_company_id
		LEFT JOIN pr_families_cies pfc ON pfc.prfc_company_id = abc.brc_company_id
where	abc.brc_branch_id = @branch_id
and		1 =	case
			when @treaty = '%' then 1
			when @treaty = '1' and cie_treaty != 0 then 1
			when @treaty = '0' and cie_treaty = 0 then 1
			else 0
			end
and		( @company_id = '%' OR abc.brc_company_id = @id)
AND		( @family_code = '%' OR pfc.prfc_family_code = @family_code) 



GO
