SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_dyn_update_nm]
AS
BEGIN
	SET NOCOUNT ON


	/*
	DGE 16/03/2017
	0000692: Modification de la fiche client


	Certaines donnés des tables annexes doivent être utilisées dans les tables fixes.
	Exemple:
	- act_clients.cli_prof_code  => table nm_professions  => table annexe nm_professions
	- act_clients.cli_cli_activity_code => table nm_activities => table annexe GENE_CODE_APE

	Cette procédure met à jour (adjonction & update) les tables fixes avec les valeurs de md_link_values
	*/


	-------------------------------------------------------------------------------------------
	-- nm_professions

	-- update
	update	p
	set		p.nmprof_l1 = t.lval_l1,
			p.nmprof_l2 = t.lval_l2,
			p.nmprof_l3 = t.lval_l3
	from	nm_professions p
			inner join	(
						select	*
						from	md_link_values
						where	lval_link_table_name = 'nm_professions'
						) as t on p.nmprof_prof_code = t.lval_value
	where	p.nmprof_l1 != t.lval_l1
	or		p.nmprof_l2 != t.lval_l2
	or		p.nmprof_l3 != t.lval_l3


	-- insert
	insert into nm_professions(nmprof_prof_code, nmprof_l1, nmprof_l2,nmprof_l3)
	select	v.lval_value, v.lval_l1, v.lval_l2, v.lval_l3
	from	md_link_values v
	where	lval_link_table_name = 'nm_professions'
	and		not exists	(
						select	1
						from	nm_professions p
						where	p.nmprof_prof_code = v.lval_value)
			



	-------------------------------------------------------------------------------------------
	-- nm_activities
	-- update
	update	a
	set		a.ape_l1 = t.lval_l1,
			a.ape_l2 = t.lval_l2,
			a.ape_l3 = t.lval_l3
	from	nm_activities a
			inner join	(
						select	*
						from	md_link_values
						where	lval_link_table_name = 'GENE_CODE_APE'
						) as t on a.ape_activity_code = t.lval_value
	where	a.ape_l1 != t.lval_l1
	or		a.ape_l2 != t.lval_l2
	or		a.ape_l3 != t.lval_l3


	-- insert
	insert into nm_activities(ape_activity_code, ape_l1, ape_l2,ape_l3)
	select	v.lval_value, v.lval_l1, v.lval_l2, v.lval_l3
	from	md_link_values v
	where	lval_link_table_name = 'GENE_CODE_APE'
	and		not exists	(
						select	1
						from	nm_activities a
						where	a.ape_activity_code = v.lval_value)


END


GO
