SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[INTEG_INTERCO]
	@SOCANA CHAR(3),
	@DATEDEBUT CHAR(10)
 
AS
 
 
--BEGIN TRY--BEGIN TRAN
 
		ALTERINDEX[IX_TB_ECRITURES]ON[dbo].[TB_ECRITURES] DISABLE
		ALTERINDEX[IX_TB_COMPTES]ON[dbo].[TB_COMPTES] DISABLE
 
 
 
 
 
		-- *** Initialisations ****************************************
		DECLARE 
			@ANNEEDEBUT CHAR(4),
			@sSQL NVARCHAR(4000),
			@TB_INTEG_BRO NVARCHAR(20),
			@TB_INTEG_HIS NVARCHAR(20),
			@TB_INTEG_PLC NVARCHAR(20),
			@TB_INTEG_CLI NVARCHAR(20),
			@TB_INTEG_FOU NVARCHAR(20),
			@sWHR VARCHAR(300),
			@sINSERTSQL NVARCHAR(4000),
			@INTEG_ECR_tmp NVARCHAR(4000),
			@INTEG_ECR_BRO_tmp NVARCHAR(4000),
			@INTEG_ECR_HIS_tmp NVARCHAR(4000)
			
				
 
			SET @ANNEEDEBUT = LEFT(@DATEDEBUT,4)SET @TB_INTEG_BRO = 'BROUIL_CPTA_GEN'SET @TB_INTEG_HIS = 'HISTO_CPTA_GENE'SET @TB_INTEG_PLC = 'PLAN_COMPT_GENE'SET @TB_INTEG_CLI = 'CLIENTS'SET @TB_INTEG_FOU = 'FOURNISSEURS'
 
 
 
		-- 1.	ECRITURES-- 1.a	Effacer les donnéesSET @sSQL = 'DELETE TB_ECRITURES 
					 WHERE YEAR(ECR_DATE) >= ' + @ANNEEDEBUT +
					'   AND ECR_CODE_ANALYTIQUE = ''' + @SOCANA + ''' '
 
		EXECUTE(@sSQL)
		PRINT @sSQL
 
		PRINT 'DEL TB_ECRITURES'
		
 
 
 
			-- Condition de filtrage sur la date des écritures:SET @sWHR = ' WHERE YEAR(DAT_ECR) >= ' + @ANNEEDEBUT 
 
			DECLARE 
				@sSQL_INTEG_ECR VARCHAR(300)
 
			
			-- Définitions SQL pour les écrituresSET @sSQL_INTEG_ECR = 'SELECT NUM_LOT, COD_JRN, 
				DAT_ECR, 		
				CONCAT(NUM_PIE_DEB, NUM_PIE_FIN) as PIECE,
				CLF,
				CPT,
				SEN_ECR,
				MNT_DBT_FRS,
				LIB_ECR
 
				FROM '
 
 
			DECLARE 
				@sSQL_INTEG_BRO NVARCHAR(4000),
				@sSQL_INTEG_HIS NVARCHAR(4000)
 
			SET @sSQL_INTEG_BRO = @sSQL_INTEG_ECR + @TB_INTEG_BRO + @SWHR
			SET @sSQL_INTEG_HIS = @sSQL_INTEG_ECR + @TB_INTEG_HIS + @SWHR
			
								
			SET @sINSERTSQL = 'INSERT INTO TB_ECRITURES
				(
					ECR_CODE_ANALYTIQUE,
					ECR_HISTO_BROUILLARD,
					ECR_NUM_LOT,
					ECR_JOURNAL,
					ECR_DATE,
					ECR_PIECE,
					ECR_COLLECTIF,
					ECR_COMPTE,
					ECR_DEBIT,
					ECR_CREDIT,
					ECR_LIBELLE,
					ECR_LETTRAGE,
					ECR_MONTANTCOUVERTURE,
					ECR_DATELETTRAGE,
					ECR_LNK_CPT
				)
			'
 
			DECLARE 
			@SQL_ECR_BRO_tmp NVARCHAR(4000),
			@SQL_ECR_HIS_tmp NVARCHAR(4000),
			@SQL_ECR_tmp NVARCHAR(4000)SET @INTEG_ECR_tmp = ',NUM_LOT ,COD_JRN, CAST(DAT_ECR AS SMALLDATETIME),
					PIECE, CLF, CPT + '''',
					CASE SEN_ECR 
						WHEN ''C'' THEN ''0'' 
						ELSE MNT_DBT_FRS 
						END AS ECR_DEBIT,
					CASE SEN_ECR 
						WHEN ''D'' THEN 
							''0'' 
						ELSE 
							MNT_DBT_FRS 
						END AS ECR_CREDIT,
					SUBSTRING(REPLACE(LIB_ECR, CHAR(0), ''''), 1, 30) AS ECR_LIBELLE,
					''N/A'' AS ECR_LETTRAGE,
					''0'' as ECR_MONTANTCOUVERTURE,
					''4501-01-01'' as ECR_DATELETTRAGE,
					CASE SUBSTRING(CLF,1,2) 
						WHEN ''40'' THEN ''FOU'' 
						WHEN ''41'' THEN ''CLI'' 
						ELSE ''PLC'' 
						END AS ECR_LNK_CPT
			
 
				FROM OPENQUERY'
 
			SET @INTEG_ECR_BRO_tmp = 'SELECT ' +
					'''' + @SOCANA + ''', ''BRO'' '
					+ @INTEG_ECR_tmp
			
			SET @INTEG_ECR_HIS_tmp = 'SELECT ' +
					'''' + @SOCANA + ''', ''HIS'' '
					+ @INTEG_ECR_tmp
 
 
		-- 1.b	Intégration des Ecritures-- INSERTION ECRITURES BROUILLARDSET @sSQL = @sINSERTSQL +
			@INTEG_ECR_BRO_tmp + 
			'(INTEGRALE_' + @SOCANA + ', 
			''' + @sSQL_INTEG_BRO + ''')'
 
		PRINT 'INS ECR_BRO'
		EXECUTE(@sSQL)
		PRINT @sSQL
 
 
		-- INSERTION ECRITURES HISTOSET @sSQL = @sINSERTSQL +
			@INTEG_ECR_HIS_tmp + 
			'(INTEGRALE_' + @SOCANA + ', 
			''' + @sSQL_INTEG_HIS + ''')'
 
		PRINT 'INS ECR HIS: '
		EXECUTE(@sSQL)
		PRINT @sSQL
 
 
 
		-- 2.	COMPTES-- 2.a	Effacer les données
 
		SET @sSQL = 'DELETE TB_COMPTES WHERE CPT_CODE_ANALYTIQUE ='+@SOCANA
		EXECUTE(@sSQL)
		PRINT @sSQL
 
		PRINT 'DEL TB_COMPTES'
 
	-------------------------------------------------------------------------------
 
 
 
 
 
 
		DECLARE
			@sSQL_tmp NVARCHAR(4000),
			@sSQL_PLC NVARCHAR(4000),
			@INTEG_PLC NVARCHAR(4000)
			
 
 
		SET @sINSERTSQL = 'INSERT INTO TB_COMPTES(
			CPT_CODE_ANALYTIQUE,
			CPT_COMPTE,
			CPT_LIBELLE,
			CPT_ANALYTIQUE,
			CPT_LNK_ECR
			)'
 
 
		SET @sSQL_PLC = @sINSERTSQL +
				'	SELECT' +
				'''' + @SOCANA + ''',  
				SUBSTRING(CPT_GNR,1,13),
				SUBSTRING(ITT_CPT,1,30) ,
				SUBSTRING(COD_LBR_1,1,13),
				''PLC''
			
			FROM OPENQUERY'
 
		SET @INTEG_PLC = 'SELECT 	
					CPT_GNR,
					ITT_CPT,
					COD_LBR_1
				FROM PLAN_COMPT_GENE'
 
		SET @sSQL = @sSQL_PLC +
			'(INTEGRALE_' + @SOCANA + ', 
			''' + @INTEG_PLC + ''')'
 
		EXECUTE(@sSQL)
		PRINT @sSQL
 
		PRINT '# IMPORT PLAN COMPTABLE OK'
 
 
 
 
 
		SET @sSQL_PLC = @sINSERTSQL +
				'	SELECT' +
				'''' + @SOCANA + ''',  
				SUBSTRING(COD_CLT,1,13),
				SUBSTRING(NOM,1,30) ,
				SUBSTRING(COD_LBR_1,1,13),
				''CLI''
			
			FROM OPENQUERY'
 
		SET @INTEG_PLC = 'SELECT 	
					COD_CLT,
					NOM,
					COD_LBR_1
				FROM CLIENTS'
 
		SET @sSQL = @sSQL_PLC +
			'(INTEGRALE_' + @SOCANA + ', 
			''' + @INTEG_PLC + ''')'
 
		EXECUTE(@sSQL)
		PRINT @sSQL
 
		PRINT '# IMPORT CLI OK'
 
 
 
		SET @sSQL_PLC = @sINSERTSQL +
				'	SELECT' +
				'''' + @SOCANA + ''',  
				SUBSTRING(COD_FOU,1,13),
				SUBSTRING(NOM,1,30) ,
				SUBSTRING(COD_LBR_1,1,13),
				''FOU''
			
			FROM OPENQUERY'
 
		SET @INTEG_PLC = 'SELECT 	
					COD_FOU,
					NOM,
					COD_LBR_1
				FROM FOURNISSEURS'
 
		SET @sSQL = @sSQL_PLC +
			'(INTEGRALE_' + @SOCANA + ', 
			''' + @INTEG_PLC + ''')'
 
		EXECUTE(@sSQL)
		PRINT @sSQL
 
 
		PRINT '# IMPORT FOU OK'
 
 
 
		-- REQUETE DE BASE TRANSFORMEE EN TABLE : GAIN DE TEMPSDROPTABLE TMP__BASE_INTRA_GROUPES
		
 
		CREATETABLE TMP__BASE_INTRA_GROUPES
			(
			SIT_ANALYTIQUE char(3), 
			FIL_Code_filiale nchar(13),  
			FIL_Libelle char(30),  
			CFP_Type_de_compte char(3), 
			CPT_Collectif char(4),  
			CPT_Compte char(13),  
			CPT_LIBELLE char(30),  
			ECR_DATE datetime,  
			ECR_PIECE char(13),  
			ECR_DEBIT money,  
			ECR_CREDIT money, 
			ECR_LIBELLE char(30),  
			CTRL_ECR_DATE_YEAR int,  
			CTRL_ECR_DATE_MONTH int,  
			CPT_CONTREPARTIE_GROUPE char(3),  
			CPT_CONTREPARTIE char(13), 
			ECR_JOURNAL char(10),  
			ECR_NUM_LOT char(10))INSERTINTO TMP__BASE_INTRA_GROUPES(SIT_ANALYTIQUE, FIL_Code_filiale, FIL_Libelle, CFP_Type_de_compte, CPT_Collectif, CPT_Compte, 
		CPT_LIBELLE, ECR_DATE, ECR_PIECE, ECR_DEBIT, ECR_CREDIT, ECR_LIBELLE, CTRL_ECR_DATE_YEAR, CTRL_ECR_DATE_MONTH, 
		CPT_CONTREPARTIE_GROUPE, CPT_CONTREPARTIE, ECR_JOURNAL, ECR_NUM_LOT)
 
		SELECT  SIT_ANALYTIQUE, FIL_Code_filiale, FIL_Libelle, CFP_Type_de_compte, CPT_Collectif, CPT_Compte, 
		CPT_LIBELLE, ECR_DATE, ECR_PIECE, ECR_DEBIT, ECR_CREDIT, ECR_LIBELLE, CTRL_ECR_DATE_YEAR, CTRL_ECR_DATE_MONTH, 
		CPT_CONTREPARTIE_GROUPE, CPT_CONTREPARTIE, ECR_JOURNAL, ECR_NUM_LOT FROM VUE___BASE_INTRA_GROUPES
 
 
 
 
 
 
 
		--COMMIT TRAN
 
		--ALTER INDEX [IX_TB_ECRITURES] ON [dbo].[TB_ECRITURES] REORGANIZE WITH ( LOB_COMPACTION = ON )--ALTER INDEX [IX_TB_COMPTES] ON [dbo].[TB_COMPTES] REORGANIZE WITH ( LOB_COMPACTION = ON )
		
 
		CHECKPOINT
		BACKUP TRANSACTION CHIMCOMPTA WITH NO_LOG
		DBCC SHRINKFILE(N'CHIMCOMPTA_log',0)
		
	
 
 
--END TRY--BEGIN CATCHSELECT
        ERROR_NUMBER()AS ErrorNumber,
        ERROR_SEVERITY()AS ErrorSeverity,
        ERROR_STATE()AS ErrorState,
        ERROR_PROCEDURE()AS ErrorProcedure,
        ERROR_LINE()AS ErrorLine,
        ERROR_MESSAGE()AS ErrorMessage;
 
		--ROLLBACK TRAN--END CATCH