DECLARE @DOS VARCHAR(8)	= '1'

/*************************************/
/* CP - LETTRAGE ENTRE EUX NON SOLDE */
/*************************************/
	-- vérifier que les indicateurs de lettrage sont cohérent
		select 'CP - INDICATEUR LETTRAGE INCOHERENT' = 'CP - INDICATEUR LETTRAGE INCOHERENT'
			, CP.DOS
			, CP.ECRNO
			, CP.ECRLG
			, CP.CPT
			, CP.CE4
			, CP.LETT
			, CP.LETTDT
			, CP.DEV
		from CP
		where CP.DOS = @DOS
			and 
				(
						CP.CE4 = 2 and (left(CP.LETT,1) in (' ','*') or CP.LETTDT is null)		-- lettré sans numéro ou date
					or 
						CP.CE4 <> 2 and (left(CP.LETT,1) not IN (' ','*'))	-- non lettré avec numéro ou partiel
				)

/*************************************************************/
/* C8 - INDICATEUR DE PRESENCE DE VENTILATIONS MAL RENSEIGNE */
/*************************************************************/


	-- recherche des ventilations mal cochées
			select 'C8 - VENTILATION MAL COCHE' = 'C8 - VENTILATION MAL COCHE 1'
				, C8.DOS
				, C8.ECRNO
				, C8.ECRLG
				, C8.CPT
				, C8.ECRDT
				, C8.LETTDT
				, C8.VTLCOD_0005
				, VTLCOD_0005_CORRECT = '1'
				, NB_CP = 0
			from C8
				join C3
					on C8.DOS = C3.DOS
						and C8.CPT = C3.CPT
			where (select COUNT(*) 
					from CP
					where C8.DOS = CP.DOS
						and C8.ECRNO = CP.ECRNO
						and C8.ECRLG = CP.ECRLG) = 0
				and C8.VTLCOD_0005 <> 1
				and C3.CPTNAT in (3,4,5)			-- nature client, fournisseur, personnel
				and C8.DOS = @DOS

		union

			select 'C8 - VENTILATION MAL COCHE' = 'C8 - VENTILATION MAL COCHE 2 ou 3'
				, C8.DOS
				, C8.ECRNO
				, C8.ECRLG
				, C8.CPT
				, C8.ECRDT
				, C8.LETTDT
				, C8.VTLCOD_0005
				, VTLCOD_0005_CORRECT = '2 ou 3'
				, NB_CP = COUNT(*)
			from C8
				join CP
					on C8.DOS = CP.DOS
						and C8.ECRNO = CP.ECRNO
						and C8.ECRLG = CP.ECRLG
				join C3
					on C8.DOS = C3.DOS
						and C8.CPT = C3.CPT
			where C8.VTLCOD_0005 < 2
				and C3.CPTNAT in (3,4,5)			-- nature client, fournisseur, personnel
				and C8.DOS = @DOS
			group by C8.DOS
				, C8.ECRNO
				, C8.ECRLG
				, C8.CPT
				, C8.ECRDT
				, C8.LETTDT
				, C8.VTLCOD_0005
			having COUNT(*) > 0

		order by C8.ECRDT desc, C8.DOS, C8.ECRNO, C8.ECRLG, C8.CPT, C8.LETTDT

	-- vérifier que les CP sont cohérent par rapport aux C8
		select 'C8 CP - INCOHERENT' = 'C8 CP - INCOHERENT'
			, C8.DOS
			, C8.CPT
			, C8.ECRNO
			, C8.ECRLG
			, SOLDE_C8 = (case when C8.SENS = 1 then C8.MT else -C8.MT end)
			, SOLDE_CP = SUM(case when CP.SENS = 1 then CP.MT else -CP.MT end)
			, C8.LETT
			, C8.LETTDT
			, count(distinct C8.DEV)
		from C8
			left outer join CP
				on C8.DOS = CP.DOS
					and C8.ECRNO = CP.ECRNO
					and C8.ECRLG = CP.ECRLG
			join C3
				on C8.DOS = C3.DOS
					and C8.CPT = C3.CPT
		where C8.DOS = @DOS
			and C3.CPTNAT in (3,4,5)			-- nature client, fournisseur, personnel
		group by C8.DOS, C8.CPT, C8.ECRNO, C8.ECRLG, C8.SENS, C8.MT, C8.LETT, C8.LETTDT
		having (case when C8.SENS = 1 then C8.MT else -C8.MT end) <> SUM(case when CP.SENS = 1 then CP.MT else -CP.MT end)
		order by C8.DOS, C8.CPT, C8.ECRNO, C8.ECRLG

	-- vérifier que les lettrages des CP sont bien soldés
		select 'CP - LETTRAGE NON SOLDES' = 'CP - LETTRAGE NON SOLDES'
			, CP.DOS
			, CP.CPT
			, CP.LETT
			, CP.LETTDT
			, NB_PARTIEL = sum(case when left(C8.LETT,1) = '*' then 1 else 0 end)
			, SOLDE = SUM(case when CP.SENS = 1 then CP.MT else -CP.MT end)
			, PREMIER_C8 = MIN(CP.ECRNO)
			, DERNIER_C8 = MAX(CP.ECRNO)
		from CP
			join C8
				on CP.DOS = C8.DOS
				and CP.ECRNO = C8.ECRNO
				and CP.ECRLG = C8.ECRLG
			join C3
				on CP.DOS = C3.DOS
					and CP.CPT = C3.CPT
		where left(CP.LETT,1) not in (' ','*')	-- lettrage renseigné et pas de lettrage partiel
			and CP.DOS = @DOS
			and C3.CPTNAT in (3,4,5)			-- nature client, fournisseur, personnel
			and CP.LETTDT is not null
		group by CP.DOS, CP.CPT, CP.LETT, CP.LETTDT
		having SUM(case when CP.SENS = 1 then CP.MT else -CP.MT end) <> 0
		order by year(CP.LETTDT) desc, CP.CPT, CP.LETT, CP.LETTDT
	

	------------------------------------------------------


	-- vérifier que les lettrages des C8 sont bien soldés
		select 'C8 - LETTRAGE NON SOLDE' = 'C8 - LETTRAGE NON SOLDE'
			, C8.DOS
			, C8.CPT
			, C8.LETT
			, C8.LETTDT
			, SOLDE_C8 = SUM(case when C8.SENS = 1 then MT else -MT end)
		from C8 --with (index(INDEX_B))
		where C8.CE4 = 2
			--and left(C8.LETT,1) not in (' ','*')	-- lettrage renseigné et pas de lettrage partiel
			and C8.DOS = @DOS
			--and C8.LETTDT is not null
			--and C8.CPT in (select C3.CPT from C3 where C3.DOS = 1 and C3.CPTNAT in (3,4,5))
		group by C8.DOS, C8.CPT, C8.LETT, C8.LETTDT
		having SUM(case when C8.SENS = 1 then C8.MT else -C8.MT end) <> 0
		order by C8.DOS, C8.CPT, C8.LETT, C8.LETTDT


	------------------------------------------------------


	-- vérifier que les lettrages des C8 sont bien soldés
		select 'CPT - LETTRAGE NON SOLDE' = 'CPT - LETTRAGE NON SOLDE'
			, C8.DOS
			, C8.CPT
			, SOLDE_C8 = SUM(case when C8.SENS = 1 then MT else -MT end)
		from C8 --with (index(INDEX_B))
		where C8.CE4 = 2
			--and left(C8.LETT,1) not in (' ','*')	-- lettrage renseigné et pas de lettrage partiel
			and C8.DOS = @DOS
			--and C8.LETTDT is not null
			--and C8.CPT in (select C3.CPT from C3 where C3.DOS = 1 and C3.CPTNAT in (3,4,5))
		group by C8.DOS, C8.CPT
		having SUM(case when C8.SENS = 1 then C8.MT else -C8.MT end) <> 0
		order by C8.DOS, C8.CPT

	---------------------------------------------------------
	-- chercher les dates d'échéances manquantes dans les CP
		select 'CP - ECHEANCE VIDE' = 'CP - ECHEANCE VIDE'
			, CP.DOS
			, CP.ECRNO
			, CP.ECRLG
		from CP
			join C8
				on CP.DOS = C8.DOS
					and CP.ECRNO = C8.ECRNO
					and CP.ECRLG = C8.ECRLG
		where CP.ECHDT is null
			and CP.DOS = @DOS

	------------------------------------------------------
	-- vérifier que les indicateurs de lettrage sont cohérent
		select 'C8 - INDICATEUR LETTRAGE INCOHERENT' = 'C8 - INDICATEUR LETTRAGE INCOHERENT'
			, C8.DOS
			, C8.ECRNO
			, C8.ECRLG
			, C8.CPT
			, C8.CE4
			, C8.LETT
			, C8.LETTDT
			, C8.ECRDT
			, C8.DEV
		from C8
		where C8.DOS = @DOS
			and 
				(
						C8.CE4 = 2 and (left(C8.LETT,1) = ' ' or C8.LETTDT is null)		-- lettré sans numéro ou date
					or  
						C8.CE4 <> 2 and (left(C8.LETT,1) not IN (' ','*'))	-- non lettré avec numéro ou partiel
				)