SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE view [dbo].[vw_tt_act_clients]
as

	select	cli_prof_code,
			cli_fam_code,
			cli_csp_code,
			cli_culture_code,
			cli_vip,
			cli_statut_code,
			cli_user_code, 
	
			cli__act_actor_id = aac.act_actor_id,
			cli__act_internal_reference = aac.act_internal_reference,
			cli__act_external_reference = aac.act_external_reference,
			cli__act_address_id = aac.act_address_id,
			
			cli__adr_name = adr_name,
			cli__adr_first_name = adr_first_name,
			cli__adr_complete_name = adr_complete_name,
			cli__adr_address1 = adr_address1,
			cli__adr_address2 = adr_address2,
			cli__adr_address3 = adr_address3,
			cli__adr_postal_code = adr_postal_code,
			cli__adr_city = adr_city,
			cli__adr_country_code = adr_country_code,
			cli__adr_mail = adr_mail,
			cli__adr_home_phone = adr_home_phone,
			cli__adr_business_phone = adr_business_phone,
			cli__adr_business_phone2 = adr_business_phone2,
			cli__adr_mobile_phone = adr_mobile_phone,
			cli__adr_fax = adr_fax,
			cli__adr_title_code = adr_title_code,
			
			cli__nmtit_l1 = nmtit_l1,
			cli__nmtit_l2 = nmtit_l2,
			cli__nmtit_l3 = nmtit_l3,

			cli__nmtit_short_code_l1 = nmtit_short_code_l1,
			cli__nmtit_short_code_l2 = nmtit_short_code_l2,
			cli__nmtit_short_code_l3 = nmtit_short_code_l3,

			
			cli__nmctr_l1 = nmctr_l1,
			cli__nmctr_l2 = nmctr_l2,
			cli__nmctr_l3 = nmctr_l3,
			
			cli__nmprof_l1 = nmprof_l1,
			cli__nmprof_l2 = nmprof_l2,
			cli__nmprof_l3 = nmprof_l3,
			
			cli__fcsp_l1 = fcsp_l1,
			cli__fcsp_l2 = fcsp_l2,
			cli__fcsp_l3 = fcsp_l3,
			
			cli__csp_l1 = csp_l1,
			cli__csp_l2 = csp_l2,
			cli__csp_l3 = csp_l3, 
			
			cli__clt_l1 = clt_l1,
			cli__clt_l2 = clt_l2,
			cli__clt_l3 = clt_l3,
			
			cli__nmscli_l1 = nmscli_l1,
			cli__nmscli_l2 = nmscli_l2,
			cli__nmscli_l3 = nmscli_l3

	from act_clients
		inner join act_actors aac on aac.act_actor_id = cli_actor_id
		inner join vw_tt_act_actors_addresse_corresp on adra_actor_id = aac.act_actor_id
		inner join adr_addresses on adr_address_id = adra_address_id  
		left join nm_titles on nmtit_title_code = adr_title_code
		left join nm_countries on nmctr_country_code = adr_country_code
		left join nm_professions on nmprof_prof_code = cli_prof_code
		left join act_fam_csp fc on fc.fcsp_fam_code = cli_fam_code 
		left join act_csp ac on ac.csp_fam_code = cli_fam_code and ac.csp_csp_code = cli_csp_code
		inner join md_cultures mc on mc.clt_culture_code = cli_culture_code
		inner join nm_status_clients nsc on nsc.nmscli_statut_code = cli_statut_code



		





GO
