SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[p_ext_policy_replace_duplicate_risque]
	@source_risk_situation_id INT,
	@destination_risk_situation_id INT,
	@destination_risk_id INT,
	@destination_policy_situation_id INT,
	@destination_policy_id INT,
	@mvt_date DATE,
	@mvt_code ais_code_short,
	@utc_date DATETIME,
	@user_code ais_code_medium,
	@transcodage_variables_ids type_source_destination READONLY,
	@transcodage_taxes_ids type_source_destination READONLY
	 
AS
	SET NOCOUNT ON;

	DECLARE @source_risk_id INT

	--recherche du risque
	SELECT @source_risk_id = rrs.riss_risk_id
	FROM ris_risks_situations rrs
	WHERE rrs.riss_risk_situation_id = @source_risk_situation_id

	--si le risque de destination est différent de celui source il faut créer l'entête du risque
	IF @source_risk_id != @destination_risk_id
	BEGIN
		INSERT INTO ris_risks (
				ris_risk_id,			ris_policy_id,			ris_entrance_date,	ris_last_risk_situation_id, ris_situations_count)
		SELECT	@destination_risk_id,	@destination_policy_id,	@mvt_date,			NULL,						NULL
		FROM ris_risks rr
		WHERE rr.ris_risk_id = @source_risk_id
	END

	--sauvegarder la situation risque dans une table temporaire
	SELECT *
	INTO #ris_risks_situations
	FROM vw___ris_risks_situations_without_rowver_and_computed rrs 
	WHERE rrs.riss_risk_situation_id = @source_risk_situation_id

	--DECLARE @debug_original_ris_risks_situations XML = (SELECT * FROM #ris_risks_situations FOR XML AUTO ,ROOT('r'))

	--mettre à jour les éléments de la nouvelle situation risque
	UPDATE #ris_risks_situations
	SET		riss_risk_situation_id = @destination_risk_situation_id,
			riss_risk_id = @destination_risk_id,
			riss_policy_id = @destination_policy_id,
			riss_policy_situation_id = @destination_policy_situation_id,
			riss_movement_code = @mvt_code,
			riss_entrance_date = CASE WHEN @mvt_code = 'ADD' THEN @mvt_date ELSE riss_entrance_date END,
			riss_modify_date = CASE WHEN @mvt_code = 'MOD' THEN @mvt_date ELSE null END,
			riss_retire_date = CASE WHEN @mvt_code = 'RET' THEN @mvt_date ELSE null END,
			riss_movement_date = @mvt_date,
			riss_operation_utc_date = @utc_date,
			riss_risk_situation_id_old = CASE WHEN @source_risk_id = @destination_risk_id THEN @source_risk_situation_id ELSE NULL END,
			riss_obsolete = 0,
			riss_perception_invoice_start_date = null, 
			riss_perception_invoice_end_date = null, 
			riss_reimboursement_invoice_start_date = null, 
			riss_reimboursement_invoice_end_date = NULL

	--DECLARE @debug_after_update_ris_risks_situations XML = (SELECT * FROM #ris_risks_situations FOR XML AUTO ,ROOT('r'))
	
	--créer la situation risque
	INSERT INTO vw___ris_risks_situations_without_rowver_and_computed
	SELECT * 
	FROM #ris_risks_situations

	--mettre à jour le risque en rapport avec la dernière situation
	UPDATE ris_risks
		SET ris_last_risk_situation_id = @destination_risk_situation_id,
			ris_situations_count = ISNULL(ris_situations_count,0) +  1
	WHERE ris_risk_id = @destination_risk_id
	
	--copie des cpl risque
	EXEC p_cpl_duplicate_row @table_name = 'ris_cpl'
							,@primary_key_field_name = 'rcpl_risk_situation_id'
							,@source_id = @source_risk_situation_id
							,@destination_id = @destination_risk_situation_id

	
	--copier les variables dans une table temporaire
	SELECT *
	INTO #ris_variables
	FROM ris_variables rv
	WHERE rv.risv_risk_situation_id = @source_risk_situation_id

	--DECLARE @debug_original_ris_variables XML = (SELECT * FROM #ris_variables FOR XML AUTO ,ROOT('r'))

	--mettre à jour la situation dans la table temporaire des variables
	UPDATE #ris_variables SET risv_risk_situation_id = @destination_risk_situation_id

	--mettre à jour la clé variable dans la table temporaire des variables
	UPDATE rv
		SET risv_variable_id = tvi.destination_id
	FROM #ris_variables rv
		INNER JOIN @transcodage_variables_ids tvi ON tvi.source_id = rv.risv_variable_id

	--DECLARE @debug_after_update_ris_variables XML = (SELECT * FROM #ris_variables FOR XML AUTO ,ROOT('r'))

	--insérer les variables risque
	INSERT INTO ris_variables
	SELECT * 
	FROM #ris_variables
	
	--copier las taxes des variables dans une table temporaire
	SELECT *
	INTO #ris_variables_taxes
	FROM ris_variables_taxes rvt
	WHERE rvt.risvt_risk_situation_id = @source_risk_situation_id

	--DECLARE @debug_original_ris_variables_taxes XML = (SELECT * FROM #ris_variables_taxes FOR XML AUTO ,ROOT('r'))

	--mettre à jour la situation dans la table temporaire des taxes
	UPDATE #ris_variables_taxes SET risvt_risk_situation_id =  @destination_risk_situation_id

	--mettre à jour les clés variables dans la table temporaire des taxes
	UPDATE rvt 
		SET risvt_variable_id = tvi.destination_id
	FROM #ris_variables_taxes rvt
		INNER JOIN @transcodage_variables_ids tvi ON tvi.source_id = rvt.risvt_variable_id 

	--mettre à jour les clés taxes dans la table temporaire des taxes
	UPDATE rvt 
		SET risvt_tax_id = tti.destination_id
	FROM #ris_variables_taxes rvt
		INNER JOIN @transcodage_taxes_ids tti ON tti.source_id = rvt.risvt_tax_id

	--DECLARE @debug_after_update_ris_variables_taxes XML = (SELECT * FROM #ris_variables_taxes FOR XML AUTO ,ROOT('r'))

	--insérer les taxes des variables risque
	INSERT INTO ris_variables_taxes
	SELECT * 
	FROM #ris_variables_taxes
GO
