SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_rep_cie_bdx_header]
	@branch_id			int,
	@user_code			ais_code_medium,
	@bordereau_id		int
as 

	set nocount on;


	select	-- informations concernant l'utilisateur
			usr.usr_name, usr.usr_mail, 
			-- informations concernant l'entité juridique
			branch_internal_reference = branch.act_internal_reference,
			branch_external_reference = branch.act_external_reference,
			branch_display_name = branch.br_display_name,
			branch_name = branch.adr_complete_name,
			branch_addr1 = branch.adr_address1,
			branch_addr2 = branch.adr_address2,
			branch_addr3 = branch.adr_address3,
			branch_postal_code = branch.adr_postal_code,
			branch_city = branch.adr_city,
			branch_mail = branch.adr_mail,
			branch_home_phone = branch.adr_home_phone,
			-- informations concernant la compagnie
			cie_internal_reference = cie.act_internal_reference,
			cie_external_reference = cie.act_external_reference,
			cie_display_name = cie.cie_display_name,
			cie_name = cie.adr_complete_name,
			cie_addr1 = cie.adr_address1,
			cie_addr2 = cie.adr_address2,
			cie_addr3 = cie.adr_address3,
			cie_postal_code = cie.adr_postal_code,
			cie_city = cie.adr_city,
			cie_mail = cie.adr_mail,
			cie_home_phone = cie.adr_home_phone,
			-- informations concernant le code devise 
			bdr_devise_code = cie.a_currency_code,
			bdr_reference = cie.bdr_reference

	from	(	select *
				from	usr_users
				where	usr_user_code = @user_code ) as usr

			inner join	(	select	*
							from	vw_act_branches 
							where	br_actor_id = @branch_id ) as branch	on 1 = 1

			inner join	(		select	c.*, a.a_currency_code, b.bdr_reference
								from	acc_bordereaux b
										inner join acc_accounts a on a.a_account_id = b.bdr_bordereau_id
										inner join vw_act_cies c on c.act_actor_id = a.a_actor_id
								where	b.bdr_bordereau_id = @bordereau_id ) as cie on 1 = 1
	





GO
