SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[p_ext_policy_replace_duplicate_contrat]
	@source_policy_situation_id INT,
	@destination_policy_situation_id INT,
	@destination_policy_id INT,
	@destination_product_code ais_code_short,
	@mvt_date DATE,
	@utc_date DATETIME,
	@user_code ais_code_medium,
	@operation_code ais_code_long,
	@endorsmentpattern_code ais_code_short,
	@transcodage_variables_ids type_source_destination READONLY,
	@transcodage_taxes_ids type_source_destination READONLY
	 
AS
	SET NOCOUNT ON;

	DECLARE @source_policy_id INT
	DECLARE @destination_company_id INT 
	DECLARE @act_code ais_code_short
	DECLARE @mvt_code ais_code_short
	DECLARE @sequence_code ais_code_short
	DECLARE @pols_number_new ais_code_long
	DECLARE @client_id INT
	DECLARE @destination_branch_id INT
	DECLARE @department_code ais_code_medium

	SELECT	@destination_company_id = pp.prd_company_id,
			@mvt_code = CASE @operation_code
							WHEN 'SUBSCRIPTION' THEN pp.prd_mvt_code__subscription
							WHEN 'POLICY_REPLACE' THEN pp.prd_mvt_code__subscription
							WHEN 'AMENDMENT_RESIL' THEN 'CANC'
							WHEN 'AMENDMENT_REPLACE' THEN 'CANC'
							ELSE null
						END,
			@sequence_code = CASE @operation_code
									WHEN 'SUBSCRIPTION' THEN pp.prd_sequence_code__subscription
									WHEN 'POLICY_REPLACE' THEN pp.prd_sequence_code__subscription
									WHEN 'AMENDMENT_RESIL' THEN NULL
									WHEN 'AMENDMENT_REPLACE' THEN NULL
									ELSE null
							END
	FROM pr_products pp
	WHERE pp.prd_product_code = @destination_product_code

	SELECT @act_code = npo.nmpop_act_code
	FROM nm_pol_operations npo
	WHERE npo.nmpop_operation_code = @operation_code

	
	--recherche du contrat
	SELECT	@source_policy_id = pps.pols_policy_id,
			@client_id = pp.pol_client_id,
			@department_code = pp.pol_department_code,
			@destination_branch_id = pp.pol_branch_id
	FROM pol_policies_situations pps
		INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id
	WHERE pps.pols_policy_situation_id = @source_policy_situation_id

	--si le contrat de destination est différent de celui source il faut créer l'entête du risque
	IF @source_policy_id != @destination_policy_id
	BEGIN

		IF ISNULL(@sequence_code,'') <> '' 
		BEGIN
			EXEC p_sequence_next_number @sequence_code = @sequence_code
									   ,@actor_id = @destination_branch_id
									   ,@department_code = @department_code
									   ,@max_len_allowed = 20
									   ,@next_number = @pols_number_new out
		END 
		
		INSERT INTO pol_policies (
				pol_policy_id,			pol_client_id,			pol_start_date,			pol_last_policy_situation_id,	pol_situations_count,	pol_age,	pol_type_code__cts, 
				pol_branch_id,			pol_department_code,	pol_company_id,			pol_product_code,				pol_currency_code)
		SELECT	@destination_policy_id,	pp.pol_client_id,		@mvt_date,				NULL,							NULL,					1,			NULL,
				pp.pol_branch_id,		pp.pol_department_code,	@destination_company_id,@destination_product_code,		pp.pol_currency_code
		FROM pol_policies pp
		WHERE pp.pol_policy_id = @source_policy_id

		--copier les rib du contrat
		SELECT * 
		INTO #pol_policies_accounts
		FROM pol_policies_accounts ppa
		WHERE ppa.polacc_policy_id = @source_policy_id

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

		UPDATE #pol_policies_accounts SET polacc_policy_id = @destination_policy_id

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

		INSERT INTO pol_policies_accounts
		SELECT * 
		FROM #pol_policies_accounts

	END

	--sauvegarder la situation contrat dans une table temporaire
	SELECT *
	INTO #pol_policies_situations
	FROM vw___pol_policies_situations_without_rowver_and_computed vppswrac
	WHERE vppswrac.pols_policy_situation_id = @source_policy_situation_id

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

	--mettre à jour les éléments de la nouvelle situation contrat
	UPDATE #pol_policies_situations
	SET pols_policy_situation_id = @destination_policy_situation_id,
		pols_policy_id = @destination_policy_id,
		pols_number = ISNULL(@pols_number_new, pols_number),
		pols_sequence_code = ISNULL(@sequence_code, pols_sequence_code),
		pols_mvt_date = @mvt_date,
		pols_mvt_code = @mvt_code,
		pols_act_code = @act_code,
		pols_operation_utc_date = @utc_date,
		pols_operation_code = @operation_code,
		pols_user_code = @user_code,
		pols_endorsmentpattern_code = @endorsmentpattern_code--,
		--pols_period_number = ''

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


	--créer la situation contrat
	INSERT INTO vw___pol_policies_situations_without_rowver_and_computed
	SELECT * 
	FROM #pol_policies_situations

	--mettre à jour le contrat en rapport avec la dernière situation
	UPDATE pol_policies
		SET pol_last_policy_situation_id = @destination_policy_situation_id,
			pol_situations_count = ISNULL(pol_situations_count,0) +  1
	WHERE pol_policy_id = @destination_policy_id

	--copie des cpl contrat
	EXEC p_cpl_duplicate_row @table_name = 'pol_cpl'
							,@primary_key_field_name = 'pcpl_policy_situation_id'
							,@source_id = @source_policy_situation_id
							,@destination_id = @destination_policy_situation_id
	
	--copier les variables dans une table temporaire
	SELECT *
	INTO #pol_variables
	FROM pol_variables pv
	WHERE pv.polv_policy_situation_id = @source_policy_situation_id

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

	--mettre à jour la situation dans la table temporaire des variables
	UPDATE #pol_variables SET polv_policy_situation_id = @destination_policy_situation_id

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

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

	--insérer les variables contrat
	INSERT INTO pol_variables
	SELECT * 
	FROM #pol_variables

	--copier las taxes des variables dans une table temporaire
	SELECT *
	INTO #pol_variables_taxes
	FROM pol_variables_taxes pvt
	WHERE pvt.polvt_policy_situation_id = @source_policy_situation_id

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

	--mettre à jour la situation dans la table temporaire des taxes
	UPDATE #pol_variables_taxes SET polvt_policy_situation_id =  @destination_policy_situation_id

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

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

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

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

	--copier les apporteurs dans une table temporaire
	SELECT *
	INTO #pol_policies_situations_introducers
	FROM pol_policies_situations_introducers ppsi
	WHERE ppsi.polsi_policy_situation_id = @source_policy_situation_id

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

	--mettre à jour l'identiafiant de la situation
	UPDATE #pol_policies_situations_introducers SET polsi_policy_situation_id = @destination_policy_situation_id

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

	--insérer les données apporteurs
	INSERT INTO pol_policies_situations_introducers
	SELECT * 
	FROM #pol_policies_situations_introducers


GO
