SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO











create PROCEDURE [dbo].[p_ext_policy_replace]
	@source_policy_situation_id INT,
	@destination_product_code ais_code_short,
	@replace_date DATE,
	@cancel_source_policy BIT = 1,
	@cancel_endorsmentpattern_code ais_code_short = 'RRP', 
	@cancel_db_invoices_effect_after_replace_date BIT = 1,
	@reimbourse_en_invoices_effect_after_replace_date BIT = 1,
	@prorate_invoices_in_effect BIT = 1,
	@lot_id INT = 0
AS
	SET NOCOUNT ON;
	SET XACT_ABORT ON; -- a voir 

	DECLARE @xact_state SMALLINT
	DECLARE @ErrorNumber INT
	DECLARE @ErrorSeverity INT
	DECLARE @ErrorState INT
	DECLARE @ErrorLine INT
	DECLARE @ErrorProcedure NVARCHAR(128)
	DECLARE @ErrorMessage NVARCHAR(4000)

	DECLARE @error_code ais_code_long
	DECLARE @create_lot BIT
	DECLARE @source_policy_id INT  
	DECLARE @source_product_code ais_code_short

	DECLARE @cancel_policy_situation_id INT
	DECLARE @new_policy_situation_id INT
	DECLARE @new_policy_id INT
	--DECLARE @cancel_risk_situation_id INT
	--DECLARE @new_risk_situation_id INT 
	DECLARE @source_invoice_id INT 
	--DECLARE @cancel_invoice_id INT 
	--DECLARE @new_invoice_id INT 
	--DECLARE @reimbourse_invoice_id INT 

	DECLARE @policy_situation_next_id INT
	DECLARE @policy_next_id INT
	DECLARE @risk_situation_next_id INT 
	DECLARE @risk_next_id INT
	DECLARE @account_next_id INT 
	DECLARE @utcdate DATETIME
	DECLARE @branch_id INT
	DECLARE @department_code ais_code_medium 
	DECLARE @source_mvt_date DATETIME 

	DECLARE @source_family_code ais_code_short
	DECLARE @destination_family_code ais_code_short

	DECLARE @min_ident INT 
	DECLARE @max_ident INT
	DECLARE @valeur_ident INT 
	DECLARE @source_risk_id INT 
	DECLARE @source_risk_situation_id INT 
	DECLARE @invoice_operation_code ais_code_short 
	DECLARE @invoice_start_date DATETIME
	DECLARE @invoice_end_date DATETIME
	DECLARE @prorate_coeff DECIMAL(20,15)
	DECLARE @user_code ais_code_medium 
	DECLARE @source_pols_number ais_code_long
	DECLARE @risque_mvt_code ais_code_short
	DECLARE @lot_id_already_replaced INT 
	DECLARE @mvt_type_code ais_code_short


	SET @utcdate = GETUTCDATE();

	/*
	*	Rechercher informations contrat
	*/
	SELECT	@source_policy_id = pp.pol_policy_id,
			@source_product_code = pp.pol_product_code,
			@source_mvt_date = pps.pols_mvt_date,
			@source_family_code = pp1.prd_family_code,
			@user_code = pps.pols_user_code,
			@source_pols_number = pps.pols_number,
			@mvt_type_code = pm.pmvt_type_code
	FROM pol_policies_situations pps
		INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id
		INNER JOIN pr_products pp1 ON pp1.prd_product_code = pp.pol_product_code
		INNER JOIN pol_mvts pm ON pps.pols_mvt_code = pm.pmvt_mvt_code
	WHERE pps.pols_policy_situation_id = @source_policy_situation_id

	SELECT	@lot_id_already_replaced = prps.prls_lot_id
	FROM policies_replaced_policies_situations prps
	WHERE		prps.prls_source_policy_situation_id = @source_policy_situation_id
			AND prps.prls_transaction_succeded = 1

	/*
	*	Création lot de contrats s'il n'est pas déjà fait
	*/
	SET @create_lot = CASE when ISNULL(@lot_id,0) < 1 THEN 1 ELSE 0 END

	IF @create_lot = 1
	BEGIN
		EXEC p_ext_policy_replace_create_lot @source_product_code = @source_product_code
											,@new_product_code = @destination_product_code
											,@lot_id = @lot_id OUT
											,@error_code = @error_code OUT
	END

	INSERT INTO policies_replaced_policies_situations(	prls_lot_id,	prls_source_policy_situation_id,	prls_start_date)
	VALUES(												@lot_id,		@source_policy_situation_id,		GETDATE())

	SET @error_code =	CASE
							WHEN @source_policy_id IS NULL THEN 'POLICY_NOT_FOUND'
							WHEN ISNULL(@lot_id_already_replaced,0) > 0 THEN 'POLICY_ALRD_REPLA'
							WHEN @mvt_type_code IN ('QUOT', 'INC', 'TER') THEN 'POLICY_STAT_INCORR'
							ELSE NULL
						END

	IF ISNULL(@error_code, '') = '' 
	BEGIN

		SELECT	@branch_id = pp.pol_branch_id,
				@department_code = pp.pol_department_code
		FROM pol_policies pp
		WHERE pp.pol_policy_id = @source_policy_id

		SELECT @destination_family_code = pp.prd_family_code
		FROM pr_products pp
		WHERE pp.prd_product_code = @destination_product_code

		/*
		* Création tables temporaires pour les logs, on travaille avec des variables de type table pour ne pas être influencés par la transaction
		*/
		/*declare @policies_replaced_policies_situations TABLE	(
																	[prls_lot_id] [int] NOT NULL,
																	[prls_source_policy_situation_id] [int] NOT NULL,
																	[prls_cancel_policy_situation_id] [int] NULL,
																	[prls_new_policy_situation_id] [int] NULL,
																	[prls_start_date] [datetime] NOT NULL,
																	[prls_end_date] [datetime] NULL,
																	[prls_transaction_succeded] [bit] NULL,
																	[prls_crystal_error_code] [dbo].[ais_code_long] NULL,
																	[prls_error_description] [nvarchar](2000) NULL,
																	PRIMARY KEY([prls_lot_id],[prls_source_policy_situation_id])
																)*/

		DECLARE @policies_replaced_risks_situations TABLE	(
																[prlrs_lot_id] [int] NOT NULL,
																[prlrs_source_risk_situation_id] [int] NOT NULL,
																[prlrs_cancel_risk_situation_id] [int] NULL,
																[prlrs_new_risk_situation_id] [int] NULL,
																[prlrs_transaction_succeded] [bit] NULL,
																[prlrs_crystal_error_code] [dbo].[ais_code_long] NULL,
																[prlrs_error_description] [nvarchar](2000) NULL,
																PRIMARY KEY([prlrs_lot_id], [prlrs_source_risk_situation_id])
															)
 

		DECLARE @policies_replaced_invoices TABLE(
													[prli_lot_id] [int] NOT NULL,
													[prli_source_invoice_id] [int] NOT NULL,
													[prli_cancel_invoice_id] [int] NULL,
													[prli_new_invoice_id] [int] NULL,
													[prli_reimboursed_invoice_id] [int] NULL,
													[prli_transaction_succeded] [bit] NOT NULL,
													[prli_crystal_error_code] [dbo].[ais_code_long] NULL,
													[prli_error_description] [nvarchar](2000) NULL,
													PRIMARY KEY ([prli_lot_id], [prli_source_invoice_id])
												)

		
 
	


		/*
		*	Rechercher les identifians des tables impactées
		*/

		--rechercher IDENTITY + 1 de la table des contrats
		EXEC p_table_next_identity_value @table_name = 'pol_policies'
										,@next_identity_value = @policy_next_id OUT
										,@error_code = @error_code OUT
	
		--rechercher IDENTITY + 1 de la table des situations contrat
		EXEC p_table_next_identity_value @table_name = 'pol_policies_situations'
										,@next_identity_value = @policy_situation_next_id OUT 
										,@error_code = @error_code OUT 

		--rechercher IDENTITY + 1 de la table des situations risks
		EXEC p_table_next_identity_value @table_name = 'ris_risks_situations'
										,@next_identity_value = @risk_situation_next_id OUT 
										,@error_code = @error_code OUT 


		--rechercher IDENTITY + 1 de la table des risks
		EXEC p_table_next_identity_value @table_name = 'ris_risks'
										,@next_identity_value = @risk_next_id OUT 
										,@error_code = @error_code OUT 

	

		--rechercher IDENTITY + 1 de la table des quittances
		EXEC p_table_next_identity_value @table_name = 'acc_accounts'
										,@next_identity_value = @account_next_id OUT 
										,@error_code = @error_code OUT 


		/*
		*	Rechercher les quittances impactées
		*/

		-- Pour les quittances ayant une date d’effet > au 01/06/2018 et n’étant pas encaissé, les passer directement en annulé (soldé)
		SELECT	ident = IDENTITY(INT, 1,1), 
				ai.*
		INTO #quittances_a_annuler
		FROM acc_invoices ai
			INNER JOIN pol_policies_situations pps ON ai.inv_policy_situation_id = pps.pols_policy_situation_id
		WHERE		ai.inv_operation_code__last in ('DB', 'AWR', 'CR')
				AND pps.pols_policy_id = @source_policy_id
				AND ai.inv_start_date > @replace_date

		DECLARE @debug_quittances_a_annuler XML = (SELECT * FROM #quittances_a_annuler FOR XML AUTO ,ROOT('r'))

		-- Pour les quittances ayant une date d’effet > au 01/06/2018 et étant encaissé, les reproduire a l’identique mais a l’inverse (remboursement de la totalité) Leur donner un statut DB
		SELECT	ident = IDENTITY(INT, 1,1), 
				ai.* 
		INTO #quittances_a_rembourser
		FROM acc_invoices ai
			INNER JOIN pol_policies_situations pps ON ai.inv_policy_situation_id = pps.pols_policy_situation_id
		WHERE		ai.inv_operation_code__last in ('EC')
				AND pps.pols_policy_id = @source_policy_id
				AND ai.inv_start_date > @replace_date

		DECLARE @debug_quittances_a_rembourser XML = (SELECT * FROM #quittances_a_rembourser FOR XML AUTO ,ROOT('r'))

		-- Pour toutes les autres quittances avec une date d’effet < au 01/06/2018 et une date de fin d’effet > 01/06/2018, cacluler le prorata pour la période du 01/06/2018 inclus au la date de fin de la quittance, 
		-- sur la totalité des variables en remboursement. 
		-- Sans tenir compte du faite qu’elles soit proratisable ou remboursable
		SELECT	ident = IDENTITY(INT, 1,1), 
				ai.* 
		INTO #quittances_a_proratiser
		FROM acc_invoices ai
			INNER JOIN pol_policies_situations pps ON ai.inv_policy_situation_id = pps.pols_policy_situation_id
		WHERE		ai.inv_operation_code__last IN ('DB', 'AWR', 'CR')
				AND pps.pols_policy_id = @source_policy_id
				AND @replace_date BETWEEN ai.inv_start_date AND ai.inv_end_date

		DECLARE @debug_quittances_a_proratiser XML = (SELECT * FROM #quittances_a_proratiser FOR XML AUTO ,ROOT('r'))


		/*
		*	Rechercher les transcodages des identifiants variables et taxes sur le nouveau produit
		*/

		--recherche le transcodage des taxes
		DECLARE @transcodage_taxes_ids_destination type_source_destination
		DECLARE @transcodage_taxes_ids_source type_source_destination

		INSERT INTO @transcodage_taxes_ids_destination(	source_id,		destination_id, common_code)
		SELECT											fatbd.tax_id,	NULL,			fatbd.tax_code
		FROM dbo.f_acc_taxes_by_date(@source_mvt_date) fatbd 

		--pour le produit de destination on cherche les id des taxes à la date de remplacement
		UPDATE tti
			SET destination_id = fatbd.tax_id
		FROM @transcodage_taxes_ids_destination tti
			INNER JOIN dbo.f_acc_taxes_by_date(@replace_date) fatbd ON fatbd.tax_code = tti.common_code 

		-- pour les variables source on ne change pas les identifiants des taxes
		INSERT INTO @transcodage_taxes_ids_source(	source_id,	destination_id)
		SELECT										source_id,	source_id
		FROM @transcodage_taxes_ids_destination

		DECLARE @debug_transcodage_taxes_ids_source XML = (SELECT * FROM @transcodage_taxes_ids_source FOR XML AUTO ,ROOT('r'))

		--rechercher le transcodage des variables
		DECLARE @transcodage_variables_ids_destination type_source_destination
		DECLARE @transcodage_variables_ids_source type_source_destination

		INSERT INTO @transcodage_variables_ids_destination(	source_id,			destination_id, common_code)
		SELECT												fppv.variable_id,	NULL,			fppv.variable_code
		FROM dbo.f_pr_products_variables(@source_family_code, @source_product_code, @source_mvt_date) fppv 

	
		--on cherche les identifiants sur le produit de destination
		UPDATE tvi	
			SET destination_id = fppv.variable_id
		FROM @transcodage_variables_ids_destination tvi
			INNER JOIN dbo.f_pr_products_variables(@destination_family_code, @destination_product_code, @replace_date) fppv ON fppv.variable_code = tvi.common_code

		DECLARE @debug_transcodage_variables_ids_destination XML = (SELECT * FROM @transcodage_variables_ids_destination FOR XML AUTO ,ROOT('r'))

		-- ces variables sont utilisées pour la situation de retrait + résiliation, ils ne changent pas d'identifiant
		INSERT INTO @transcodage_variables_ids_source(	source_id, destination_id)
		SELECT											source_id, source_id
		FROM @transcodage_variables_ids_destination

		DECLARE @debug_transcodage_variables_ids_source XML = (SELECT * FROM @transcodage_variables_ids_source FOR XML AUTO ,ROOT('r'))

		/*
		*	Rechercher les situations risques à retirer sur la situation de résiliation
		*/

		-- risques à retirer 
		DECLARE @risques_a_retirer TABLE(ident INT IDENTITY(1,1), risk_id INT, risk_situation_id INT, new_risk_situation_id int)
	 
		-- D'abord les risques encore en cours 
		INSERT INTO @risques_a_retirer(	risk_id,			risk_situation_id)
		SELECT							rr.ris_risk_id,		rr.ris_last_risk_situation_id
		FROM ris_risks rr
			INNER JOIN ris_risks_situations rrs ON		rrs.riss_risk_id = rr.ris_risk_id
													AND rrs.riss_risk_situation_id = rr.ris_last_risk_situation_id
			INNER JOIN nm_risks_movements nrm ON nrm.rmvt_movement_code = rrs.riss_movement_code
		WHERE		rr.ris_policy_id = @source_policy_id
				AND nrm.rmvt_type_retire = 0 

		DECLARE @debug_risques_a_retirer_for_en_cours XML = (SELECT * FROM @risques_a_retirer FOR XML AUTO ,ROOT('r'))

		--puis les risques qui sont sur les quittances de remboursement mais pas dans ceux à retirer, on rajoute une nouvelle situation RET
		INSERT INTO @risques_a_retirer(	risk_id,			risk_situation_id)
		SELECT							rr.ris_risk_id,		rr.ris_last_risk_situation_id
		FROM ris_risks rr
			INNER JOIN	(
							SELECT DISTINCT risk_id
							FROM	(
										SELECT DISTINCT risk_id = rrs.riss_risk_id
										FROM acc_invoices_risks_variables airv
											INNER JOIN #quittances_a_rembourser qar ON qar.inv_invoice_id = airv.invrisv_invoice_id
											INNER JOIN ris_risks_situations rrs ON rrs.riss_risk_situation_id = airv.invrisv_risk_situation_id
										WHERE rrs.riss_policy_id = @source_policy_id
							
										UNION 
								
										SELECT DISTINCT risk_id = rrs.riss_risk_id
										FROM acc_invoices_risks_variables airv
											INNER JOIN #quittances_a_proratiser qap ON qap.inv_invoice_id = airv.invrisv_invoice_id
											INNER JOIN ris_risks_situations rrs ON rrs.riss_risk_situation_id = airv.invrisv_risk_situation_id
										WHERE rrs.riss_policy_id = @source_policy_id
									) AS t 
						) AS t ON	t.risk_id = rr.ris_risk_id
		WHERE NOT EXISTS(	SELECT 1 
							FROM @risques_a_retirer rar 
							WHERE rar.risk_id = rr.ris_risk_id)

		DECLARE @debug_risques_a_retirer_all XML = (SELECT * FROM @risques_a_retirer FOR XML AUTO ,ROOT('r'))
		
		/*
		*	Rechercher les risques et situations à ajouter sur la le nouveau contrat
		*/
		-- risques à ajouter 
		DECLARE @risques_a_ajouter TABLE(ident INT IDENTITY(1,1), risk_id INT, risk_situation_id INT, new_risk_situation_id INT, mvt_code ais_code_short)
	 
		-- D'abord les risques en cours 
		INSERT INTO @risques_a_ajouter(	risk_id,			risk_situation_id,				mvt_code)
		SELECT							rr.ris_risk_id,		rr.ris_last_risk_situation_id,	'ADD'
		FROM ris_risks rr
			INNER JOIN ris_risks_situations rrs ON		rrs.riss_risk_id = rr.ris_risk_id
													AND rrs.riss_risk_situation_id = rr.ris_last_risk_situation_id
			INNER JOIN nm_risks_movements nrm ON nrm.rmvt_movement_code = rrs.riss_movement_code
		WHERE		rr.ris_policy_id = @source_policy_id
				AND nrm.rmvt_type_retire = 0

		DECLARE @debug_risques_a_ajouter XML = (SELECT * FROM @risques_a_ajouter FOR XML AUTO ,ROOT('r'))
		
		DECLARE @transcodage_situations_risques_retire type_source_destination
		DECLARE @transcodage_situations_risques_new type_source_destination


		BEGIN TRY
			BEGIN TRANSACTION RESILTRAN;

			/*
			*	Créer la situation contrat résiliation
			*/
			SET @error_code = 'POLICY_CANCEL_SIT';

				SET @cancel_policy_situation_id = @policy_situation_next_id

				--PRINT 'Debut ajout situation résil contrat'

				EXEC p_ext_policy_replace_duplicate_contrat @source_policy_situation_id = @source_policy_situation_id
														   ,@destination_policy_situation_id = @cancel_policy_situation_id
														   ,@destination_policy_id = @source_policy_id --le même contrat
														   ,@destination_product_code = @source_product_code
														   ,@mvt_date = @replace_date
														   ,@utc_date = @utcdate
														   ,@user_code = @user_code
														   ,@operation_code = 'AMENDMENT_REPLACE'
														   ,@endorsmentpattern_code = @cancel_endorsmentpattern_code
														   ,@transcodage_variables_ids = @transcodage_variables_ids_source
														   ,@transcodage_taxes_ids = @transcodage_taxes_ids_source

				--PRINT 'Fin ajout situation résil contrat'

		

			SET @error_code = null

			SET @policy_situation_next_id = @policy_situation_next_id + 1

			/*
			*	Ajouter la situation de retrait sur tous les risques impactés
			*/
			SET @min_ident = NULL

			SELECT	@min_ident = MIN(rar.ident), 
					@max_ident = MAX(rar.ident)
			FROM @risques_a_retirer rar

			IF @min_ident IS NOT NULL 
			BEGIN
				SET @valeur_ident = @min_ident

				WHILE @valeur_ident <= @max_ident
				BEGIN
				
					SET @error_code = 'RISK_CANCEL_SIT';

						SELECT	@source_risk_id = rar.risk_id,
								@source_risk_situation_id = rar.risk_situation_id
						FROM @risques_a_retirer rar
						WHERE rar.ident = @valeur_ident

						INSERT INTO @policies_replaced_risks_situations(prlrs_lot_id,	prlrs_source_risk_situation_id,	prlrs_cancel_risk_situation_id,	prlrs_transaction_succeded)
						VALUES(											@lot_id,		@source_risk_situation_id,		@risk_situation_next_id,		1)

						EXEC p_ext_policy_replace_duplicate_risque		@source_risk_situation_id = @source_risk_situation_id
																		,@destination_risk_situation_id = @risk_situation_next_id
																		,@destination_risk_id = @source_risk_id -- le risque reste le même
																		,@destination_policy_situation_id = @cancel_policy_situation_id
																		,@destination_policy_id = @source_policy_id --le même contrat
																		,@mvt_date = @replace_date
																		,@mvt_code = 'RET'
																		,@utc_date = @utcdate
																		,@user_code = @user_code
																		,@transcodage_variables_ids = @transcodage_variables_ids_source
																		,@transcodage_taxes_ids = @transcodage_taxes_ids_source

				
						UPDATE rar
							SET new_risk_situation_id = @risk_situation_next_id
						FROM @risques_a_retirer rar
						WHERE rar.ident = @valeur_ident

						--IF @valeur_ident % 10 = 0
						--BEGIN
							--PRINT CONVERT(VARCHAR, @valeur_ident) + ' risques rétirés';
						--END

					SET @error_code = null

					SET @risk_situation_next_id = @risk_situation_next_id + 1

					SET @valeur_ident = @valeur_ident + 1

				END

				--PRINT CONVERT(VARCHAR, @valeur_ident - 1) + ' risques rétirés';

			END

			INSERT INTO @transcodage_situations_risques_retire(	source_id,					destination_id)
			SELECT	DISTINCT									rar.risk_situation_id,		rar.new_risk_situation_id
			FROM @risques_a_retirer rar

			DECLARE @debug_transcodage_situations_risques_retire XML = (SELECT * FROM @transcodage_situations_risques_retire FOR XML AUTO ,ROOT('r'))
				

			/*
			*	Créer le nouveau contrat
			*/
			SET @error_code = 'POLICY_NEW_POLICY';

				SET @new_policy_situation_id = @policy_situation_next_id
				SET @new_policy_id = @policy_next_id

				EXEC p_ext_policy_replace_duplicate_contrat @source_policy_situation_id = @source_policy_situation_id
														   ,@destination_policy_situation_id = @new_policy_situation_id
														   ,@destination_policy_id = @new_policy_id
														   ,@destination_product_code = @destination_product_code
														   ,@mvt_date = @replace_date
														   ,@utc_date = @utcdate
														   ,@user_code = @user_code
														   ,@operation_code = 'SUBSCRIPTION'
														   ,@endorsmentpattern_code = null
														   ,@transcodage_variables_ids = @transcodage_variables_ids_destination
														   ,@transcodage_taxes_ids = @transcodage_taxes_ids_destination

		
			SET @error_code = null;
		
			SET @policy_situation_next_id = @policy_situation_next_id + 1

			SET @policy_next_id = @policy_next_id + 1


			/*
			*	Ajouter les risques sur le nouveau contrat
			*/
			SET @min_ident = NULL

			SELECT	@min_ident = MIN(raa.ident), 
					@max_ident = MAX(raa.ident)
			FROM @risques_a_ajouter raa

			IF @min_ident IS NOT NULL 
			BEGIN
				SET @valeur_ident = @min_ident

				WHILE @valeur_ident <= @max_ident
				BEGIN
				
					SET @error_code = 'RISK_NEW_RISK';

						SELECT	@source_risk_id = raa.risk_id,
								@source_risk_situation_id = raa.risk_situation_id,
								@risque_mvt_code = raa.mvt_code
						FROM @risques_a_ajouter raa
						WHERE raa.ident = @valeur_ident

						IF NOT EXISTS(SELECT 1 FROM @policies_replaced_risks_situations WHERE prlrs_lot_id = @lot_id AND prlrs_source_risk_situation_id = @source_risk_situation_id)
						BEGIN
							INSERT INTO @policies_replaced_risks_situations(prlrs_lot_id,	prlrs_source_risk_situation_id,	prlrs_new_risk_situation_id,	prlrs_transaction_succeded)
							VALUES(											@lot_id,		@source_risk_situation_id,		@risk_situation_next_id,		1)
						END
						ELSE
						BEGIN
							UPDATE @policies_replaced_risks_situations 
							SET		prlrs_new_risk_situation_id = @risk_situation_next_id,
									prlrs_transaction_succeded = 1
							WHERE prlrs_lot_id = @lot_id AND prlrs_source_risk_situation_id = @source_risk_situation_id
						END

						EXEC p_ext_policy_replace_duplicate_risque		@source_risk_situation_id = @source_risk_situation_id
																		,@destination_risk_situation_id = @risk_situation_next_id
																		,@destination_risk_id = @risk_next_id
																		,@destination_policy_situation_id = @new_policy_situation_id
																		,@destination_policy_id = @new_policy_id 
																		,@mvt_date = @replace_date
																		,@mvt_code = @risque_mvt_code
																		,@utc_date = @utcdate
																		,@user_code = @user_code
																		,@transcodage_variables_ids = @transcodage_variables_ids_destination
																		,@transcodage_taxes_ids = @transcodage_taxes_ids_destination

				
						SET @error_code = null
				
						UPDATE raa
							SET new_risk_situation_id = @risk_situation_next_id
						FROM @risques_a_ajouter raa
						WHERE raa.ident = @valeur_ident

				
        			SET @error_code = null;

					
					SET @risk_situation_next_id = @risk_situation_next_id + 1

					SET @risk_next_id = @risk_next_id + 1

					SET @valeur_ident = @valeur_ident + 1

				END

			END

			INSERT INTO @transcodage_situations_risques_new(	source_id,				destination_id)
			SELECT	DISTINCT									raa.risk_situation_id,	raa.new_risk_situation_id
			FROM @risques_a_ajouter raa
				
			DECLARE @debug_transcodage_situations_risques_new XML = (SELECT * FROM @transcodage_situations_risques_new FOR XML AUTO ,ROOT('r'))


			/*
			* Annuler les quittances DB qui ont une date d'effet superièure à la date de remplacement
			*/

			SET @error_code = 'ALL_INVOICES_CANC';
		
				INSERT INTO acc_invoices_operations (	invop_invoice_id,	invop_operation_utc_date,	invop_operation_code,	invop_user_code,	invop_obs,	invop_branch_id,	invop_department_code)
				SELECT									qaa.inv_invoice_id, @utcdate,					'AN',					@user_code,			NULL,		@branch_id,			@department_code	
				FROM #quittances_a_annuler qaa

				UPDATE ai
					SET inv_operation_code__last = 'AN',
						inv_an_date = @replace_date	
				FROM acc_invoices ai
					INNER JOIN #quittances_a_annuler tmp ON		tmp.inv_invoice_id = ai.inv_invoice_id

				INSERT INTO @policies_replaced_invoices (	prli_lot_id,	prli_source_invoice_id,		prli_cancel_invoice_id, prli_transaction_succeded)
				SELECT										@lot_id,		qaa.inv_invoice_id,			qaa.inv_invoice_id,		1
				FROM #quittances_a_annuler qaa
	
		
			SET @error_code = null;
		
			/*
			* Ristourner les quittances EN qui ont une date d'effet superièure à la date de remplacement
			*/

			SET @min_ident = NULL

			SELECT	@min_ident = MIN(qar.ident), 
					@max_ident = MAX(qar.ident)
			FROM #quittances_a_rembourser qar

			IF @min_ident IS NOT NULL 
			BEGIN
				SET @valeur_ident = @min_ident

				WHILE @valeur_ident <= @max_ident
				BEGIN
				
					SET @error_code = 'INVOICE_TOTAL_RMB';

						SELECT	@source_invoice_id = qar.inv_invoice_id,
								@invoice_end_date = qar.inv_end_date 
						FROM #quittances_a_rembourser qar
						WHERE qar.ident = @valeur_ident

						SET @invoice_operation_code = 'RMB'

						INSERT INTO @policies_replaced_invoices (	
									prli_lot_id,	prli_source_invoice_id,		prli_reimboursed_invoice_id,	prli_transaction_succeded)
						VALUES (	@lot_id,		@source_invoice_id,			@account_next_id,				1						);

						EXEC p_ext_policy_replace_duplicate_invoice @source_account_id = @source_invoice_id
																   ,@destination_account_id = @account_next_id
																   ,@destination_policy_situation_id = @cancel_policy_situation_id
																   ,@start_date = @replace_date
																   ,@end_date = @invoice_end_date
																   ,@utc_date = @utcdate
																   ,@operation_code = @invoice_operation_code
																   ,@user_code = @user_code
																   ,@prorate_coeff = -1
																   ,@transcodage_situations_risques = @transcodage_situations_risques_retire
																   ,@transcodage_variables_ids = @transcodage_variables_ids_source
																   ,@transcodage_taxes_ids = @transcodage_taxes_ids_source

				
        			SET @error_code = null;
					
					SET @account_next_id = @account_next_id + 1

					SET @valeur_ident = @valeur_ident + 1

				END

			END

			/*
			* Prorater les quittances DB,EN qui ont une date d'effet inferièure à la date de remplacement et une date de fin d'effet superièure à la date de remplacement
			*/
		
			------
			SET @min_ident = NULL

			SELECT	@min_ident = MIN(qap.ident), 
					@max_ident = MAX(qap.ident)
			FROM #quittances_a_proratiser qap

			IF @min_ident IS NOT NULL 
			BEGIN
				SET @valeur_ident = @min_ident

				WHILE @valeur_ident <= @max_ident
				BEGIN
				
					SET @error_code = 'INVOICE_PRORATE_RMB'

						SELECT	@source_invoice_id = qap.inv_invoice_id,
								@invoice_start_date = qap.inv_start_date,
								@invoice_end_date = qap.inv_end_date 
						FROM #quittances_a_proratiser qap
						WHERE qap.ident = @valeur_ident

						--d'abord la quittance de remboursement sur la situation de résiliation
						SET @invoice_operation_code = 'RMB'

						IF NOT EXISTS(SELECT 1 FROM @policies_replaced_invoices WHERE prli_lot_id = @lot_id AND prli_source_invoice_id = @source_invoice_id)
						BEGIN
							INSERT INTO @policies_replaced_invoices (	
										prli_lot_id,	prli_source_invoice_id,		prli_reimboursed_invoice_id,	prli_transaction_succeded)
							VALUES (	@lot_id,		@source_invoice_id,			@account_next_id,				1						);
						END
						ELSE
						BEGIN
							UPDATE @policies_replaced_invoices	
								SET prli_reimboursed_invoice_id = @account_next_id
							WHERE		prli_lot_id = @lot_id 
									AND prli_source_invoice_id = @source_invoice_id
						END

						SET @prorate_coeff = -1 * (ABS(DATEDIFF(dd, @replace_date, @invoice_end_date)) * 1.0) / ABS(DATEDIFF(dd, @invoice_start_date, @invoice_end_date))

						EXEC p_ext_policy_replace_duplicate_invoice @source_account_id = @source_invoice_id
																   ,@destination_account_id = @account_next_id
																   ,@destination_policy_situation_id = @cancel_policy_situation_id
																   ,@start_date = @replace_date
																   ,@end_date = @invoice_end_date
																   ,@utc_date = @utcdate
																   ,@operation_code = @invoice_operation_code
																   ,@user_code = @user_code
																   ,@prorate_coeff =  @prorate_coeff
																   ,@transcodage_situations_risques = @transcodage_situations_risques_retire
																   ,@transcodage_variables_ids = @transcodage_variables_ids_source
																   ,@transcodage_taxes_ids = @transcodage_taxes_ids_source


					--puis la quittance DB sur le nouveau contrat
					SET @error_code = 'INVOICE_PRORATE_DB'
				
						SET @invoice_operation_code = 'DB'
						SET @account_next_id = @account_next_id + 1


						IF NOT EXISTS(SELECT 1 FROM @policies_replaced_invoices WHERE prli_lot_id = @lot_id AND prli_source_invoice_id = @source_invoice_id)
						BEGIN
							INSERT INTO @policies_replaced_invoices (	
										prli_lot_id,	prli_source_invoice_id,		prli_new_invoice_id,	prli_transaction_succeded)
							VALUES (	@lot_id,		@source_invoice_id,			@account_next_id,		1						);
						END
						ELSE
						BEGIN
							UPDATE @policies_replaced_invoices	
								SET prli_new_invoice_id = @account_next_id
							WHERE		prli_lot_id = @lot_id 
									AND prli_source_invoice_id = @source_invoice_id
						END

						SET @prorate_coeff = ABS(@prorate_coeff)

						EXEC p_ext_policy_replace_duplicate_invoice @source_account_id = @source_invoice_id
																   ,@destination_account_id = @account_next_id
																   ,@destination_policy_situation_id = @new_policy_situation_id
																   ,@start_date = @replace_date
																   ,@end_date = @invoice_end_date
																   ,@utc_date = @utcdate
																   ,@operation_code = @invoice_operation_code
																   ,@user_code = @user_code
																   ,@prorate_coeff = @prorate_coeff
																   ,@transcodage_situations_risques = @transcodage_situations_risques_new
																   ,@transcodage_variables_ids = @transcodage_variables_ids_destination
																   ,@transcodage_taxes_ids = @transcodage_taxes_ids_destination


					SET @error_code = null

					SET @account_next_id = @account_next_id + 1

					SET @valeur_ident = @valeur_ident + 1

				END

			END


			------
	
			SET @xact_state = XACT_STATE();

			COMMIT TRANSACTION RESILTRAN;

			SELECT	@ErrorNumber = 0,
					@ErrorMessage = NULL,
					@error_code = NULL

		END TRY
		BEGIN CATCH
			IF @@trancount > 0
			BEGIN

				SELECT	@ErrorNumber = ERROR_NUMBER(),
						@ErrorSeverity = ERROR_SEVERITY(), 
						@ErrorState = ERROR_STATE(),
						@ErrorLine = ERROR_LINE(),
						@ErrorProcedure = ERROR_PROCEDURE(),
						@ErrorMessage = ERROR_MESSAGE();

				ROLLBACK TRANSACTION RESILTRAN;

				SET @ErrorMessage =		'Number: ' + CONVERT(VARCHAR, ISNULL(@ErrorNumber,0)) 
									+	', Severity: ' + CONVERT(VARCHAR, ISNULL(@ErrorSeverity,0))
									+	', State: ' + CONVERT(VARCHAR, ISNULL(@ErrorState,0)) + ', Line: ' + CONVERT(VARCHAR, ISNULL(@ErrorLine,0))
									+	', Procedure: ' + ISNULL(@ErrorProcedure,'')
									+	', Message: ' + ISNULL(@ErrorMessage,'')

				IF ISNULL(@error_code,'') = ''
				BEGIN
					SET @error_code = 'UNKNOWN'
				END

				IF LEFT(ISNULL(@error_code,''),5) = 'RISK_'
				BEGIN
					UPDATE @policies_replaced_risks_situations
							SET		prlrs_transaction_succeded = 0,
									prlrs_crystal_error_code = @error_code,
									prlrs_error_description = @ErrorMessage
							WHERE		prlrs_lot_id = @lot_id
									AND prlrs_source_risk_situation_id = @source_risk_situation_id;
				END
			

				IF LEFT(ISNULL(@error_code,''),8) = 'INVOICE_'
				BEGIN
					UPDATE @policies_replaced_invoices 
					SET		prli_transaction_succeded = 0,
							prli_crystal_error_code = @error_code,
							prli_error_description = @ErrorMessage
					WHERE		prli_lot_id = @lot_id
							AND prli_source_invoice_id = @source_invoice_id;
				END

				UPDATE	policies_replaced_policies_situations 
				SET	prls_crystal_error_code = @error_code,
					prls_error_description = @ErrorMessage,
					prls_cancel_policy_situation_id = NULL,
					prls_new_policy_situation_id = NULL
				WHERE		prls_lot_id = @lot_id
						AND prls_source_policy_situation_id = @source_policy_situation_id;

				--pour ne pas avoir de problème de FK
				UPDATE @policies_replaced_risks_situations
				SET prlrs_cancel_risk_situation_id = NULL,
					prlrs_new_risk_situation_id = NULL

				UPDATE @policies_replaced_invoices 
				SET	prli_cancel_invoice_id = NULL,
					prli_new_invoice_id = NULL,
					prli_reimboursed_invoice_id = NULL 

			END
			--THROW
		END CATCH

	
		INSERT INTO policies_replaced_invoices (
				prli_lot_id, prli_source_invoice_id, prli_cancel_invoice_id, prli_new_invoice_id, prli_reimboursed_invoice_id, prli_transaction_succeded, prli_crystal_error_code, prli_error_description)
		SELECT	prli_lot_id, prli_source_invoice_id, prli_cancel_invoice_id, prli_new_invoice_id, prli_reimboursed_invoice_id, prli_transaction_succeded, prli_crystal_error_code, prli_error_description
		FROM @policies_replaced_invoices

		INSERT INTO policies_replaced_risks_situations (
				prlrs_lot_id, prlrs_source_risk_situation_id, prlrs_cancel_risk_situation_id, prlrs_new_risk_situation_id, prlrs_transaction_succeded, prlrs_crystal_error_code, prlrs_error_description)
		SELECT	prlrs_lot_id, prlrs_source_risk_situation_id, prlrs_cancel_risk_situation_id, prlrs_new_risk_situation_id, prlrs_transaction_succeded, prlrs_crystal_error_code, prlrs_error_description
		FROM @policies_replaced_risks_situations

	END


	UPDATE	policies_replaced_policies_situations 
	SET		
		prls_cancel_policy_situation_id = CASE WHEN ISNULL(@error_code,'') = '' THEN @cancel_policy_situation_id ELSE NULL END 
		,prls_new_policy_situation_id = CASE WHEN ISNULL(@error_code,'') = '' THEN @new_policy_situation_id ELSE NULL END
		,prls_end_date = GETDATE()
		,prls_transaction_succeded = CASE WHEN ISNULL(@error_code,'') = '' THEN 1 ELSE 0 END
		,prls_crystal_error_code = @error_code
		,prls_error_description = @ErrorMessage
	WHERE		prls_lot_id = @lot_id
			AND prls_source_policy_situation_id = @source_policy_situation_id;

	/*
	*	Mettre à jour le lot
	*/
	IF @create_lot = 1
	BEGIN
		UPDATE policies_replaced_lots
			SET prl_end_date = GETDATE()
		WHERE prl_lot_id = @lot_id
	END

	IF ISNULL(@error_code, '') = ''
	BEGIN
		PRINT 'CONTRAT ' + @source_pols_number + ' - OPERATION REUSSIE!!!'
	END
	ELSE
	BEGIN
		PRINT 'CONTRAT ' + @source_pols_number + ' - OPERATION ECHOUE, ERREUR: ' + ISNULL(@ErrorMessage,'')
	END
GO
