
Set IMPLICIT_TRANSACTIONS off
GO

-- OBD 09/11/2018
Print 'Correction des F_SAL avec enregistrement inexistant dans F_RENSSAL'
--Select SA_No_Salarie From F_SAL Where F_SAL.SA_No_Salarie not in (Select F_RensSal.SA_No_Salarie From F_RensSal)
Delete From F_SAL Where F_SAL.SA_No_Salarie not in (Select F_RensSal.SA_No_Salarie From F_RensSal)

Print 'Correction des F_RENSSAL avec enregistrement inexistant dans F_SAL'
--Select SA_No_Salarie From F_RensSal Where F_RensSal.SA_No_Salarie not in (Select F_SAL.SA_No_Salarie From F_SAL)
Delete From F_RensSal Where F_RensSal.SA_No_Salarie not in (Select F_SAL.SA_No_Salarie From F_SAL)

Print 'Suppression des fiches de déclarations CRC'
-- Suppressions déplacées en tête de fichier
Delete From F_CRC
Delete From F_CRC0
Delete From F_CRC2
Delete From F_CRCI
--Fin OBD 09/11/2018

--BEAL PMEPAIE-2418 Début
Print 'Correction des F_HAFF avec Organisation à blanc'
Update F_HAFF Set OR_Departement = NULL Where OR_Departement = ''
Update F_HAFF Set OR_Service = NULL Where OR_Service = ''
Update F_HAFF Set OR_Unite = NULL Where OR_Unite = ''
Update F_HAFF Set OR_Categorie = NULL Where OR_Categorie = ''

Print 'Correction des F_HST_Affectation avec Organisation à blanc'
Update F_HST_Affectation Set AO_Affectation_Departement = NULL Where AO_Affectation_Departement = ''
Update F_HST_Affectation Set AO_Affectation_Service = NULL Where AO_Affectation_Service = ''
Update F_HST_Affectation Set AO_Affectation_Unite = NULL Where AO_Affectation_Unite = ''
Update F_HST_Affectation Set AO_Affectation_Categorie = NULL Where AO_Affectation_Categorie = ''

Print 'Correction des F_SAL avec Organisation à blanc'
Update F_SAL Set OR_Departement = NULL Where OR_Departement = ''
Update F_SAL Set OR_Service = NULL Where OR_Service = ''
Update F_SAL Set OR_Unite = NULL Where OR_Unite = ''
Update F_SAL Set OR_Categorie = NULL Where OR_Categorie = ''

Print 'Correction des F_RENSSAL avec Organisation à blanc'
Update F_RENSSAL Set FP_Affectation_Departement = NULL Where FP_Affectation_Departement = ''
Update F_RENSSAL Set FP_Affectation_Service = NULL Where FP_Affectation_Service = ''
Update F_RENSSAL Set FP_Affectation_Unite = NULL Where FP_Affectation_Unite = ''
Update F_RENSSAL Set FP_Affectation_Categorie = NULL Where FP_Affectation_Categorie = ''
--BEAL PMEPAIE-2418 FIN

Print 'Correction de F_RENSSAL par rapport / F_CPX '
--SELECT FP_SALAIRE_COMPTEAUXILIAIRE FROM F_RENSSAL WHERE   FP_SALAIRE_COMPTEAUXILIAIRE IS NOT NULL AND	FP_SALAIRE_COMPTEAUXILIAIRE NOT IN (SELECT CX_COMPTE FROM F_CPX)
UPDATE F_RENSSAL
SET
	FP_SALAIRE_COMPTEAUXILIAIRE = NULL 
WHERE 
	FP_SALAIRE_COMPTEAUXILIAIRE IS NOT NULL AND	
	FP_SALAIRE_COMPTEAUXILIAIRE NOT IN (SELECT CX_COMPTE FROM F_CPX)

Print 'Insertion dans F_ORG des codes organisation de type CATEGORIE par rapport / F_RENSSAL '
--SELECT FP_AFFECTATION_CATEGORIE FROM F_RENSSAL WHERE FP_AFFECTATION_CATEGORIE IS NOT NULL AND FP_AFFECTATION_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =3)
INSERT INTO F_ORG (OR_TYPE, OR_CODE, OR_INTITULE, ZR_HC_FLAGINFOSGENERAL, IdOdbc)
SELECT DISTINCT
	3,
	FP_AFFECTATION_CATEGORIE,
	'Fiche de conversion',
	0,
        ((Select Max(IdOdbc) From F_ORG Where OR_Type=3)+1)
FROM
	F_RENSSAL 
WHERE
	FP_AFFECTATION_CATEGORIE IS NOT NULL AND  
	FP_AFFECTATION_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =3)


Print 'Insertion dans F_ORG des codes organisation de type UNITE par rapport / F_RENSSAL  '
--SELECT FP_AFFECTATION_UNITE FROM F_RENSSAL WHERE FP_AFFECTATION_UNITE IS NOT NULL AND  FP_AFFECTATION_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =2)
INSERT INTO F_ORG (OR_TYPE, OR_CODE, OR_INTITULE, ZR_HC_FLAGINFOSGENERAL, IdOdbc) 
SELECT DISTINCT
	2,
	FP_AFFECTATION_UNITE,
	'Fiche de conversion',
	0,
        ((Select Max(IdOdbc) From F_ORG Where OR_Type=2)+1)	 
FROM 
	F_RENSSAL 
WHERE
	FP_AFFECTATION_UNITE IS NOT NULL AND
	FP_AFFECTATION_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =2)

Print 'Insertion dans F_ORG des codes organisation de type SERVICE par rapport / F_RENSSAL '
--SELECT FP_AFFECTATION_SERVICE FROM F_RENSSAL WHERE FP_AFFECTATION_SERVICE IS NOT NULL AND FP_AFFECTATION_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =1)
INSERT INTO F_ORG (OR_TYPE, OR_CODE, OR_INTITULE, ZR_HC_FLAGINFOSGENERAL, IdODbc) 
SELECT DISTINCT
	1,
	FP_AFFECTATION_SERVICE,
	'Fiche de conversion',
	0,
        ((Select Max(IdOdbc) From F_ORG Where OR_Type=1)+1)	 
FROM 
	F_RENSSAL 
WHERE
	FP_AFFECTATION_SERVICE IS NOT NULL AND
	FP_AFFECTATION_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =1)

Print 'Insertion dans F_ORG des codes organisation de type DEPARTEMENT par rapport / F_RENSSAL '
--SELECT FP_AFFECTATION_DEPARTEMENT FROM F_RENSSAL WHERE FP_AFFECTATION_DEPARTEMENT IS NOT NULL AND FP_AFFECTATION_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 0)
INSERT INTO F_ORG (OR_TYPE, OR_CODE, OR_INTITULE, ZR_HC_FLAGINFOSGENERAL, IdOdbc) 
SELECT DISTINCT
	0,
	FP_AFFECTATION_DEPARTEMENT,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_ORG Where OR_Type=0)+1)	 
FROM 
	F_RENSSAL 
WHERE  
	FP_AFFECTATION_DEPARTEMENT IS NOT NULL AND
	FP_AFFECTATION_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =0)


Print 'Correction de F_RENSSAL par rapport / F_GHRTAB / PAM '
--SELECT FP_GHR_CODEPAM FROM F_RENSSAL WHERE FP_GHR_CODEPAM IS NOT NULL AND FP_GHR_CODEPAM NOT IN (SELECT GT_CODE FROM F_GHRTAB WHERE GT_TYPEAFFECTATION =6)
UPDATE F_RENSSAL
SET 
	FP_GHR_CODEPAM = NULL 
WHERE 
	FP_GHR_CODEPAM IS NOT NULL AND
	FP_GHR_CODEPAM NOT IN (SELECT GT_CODE FROM F_GHRTAB WHERE GT_TYPEAFFECTATION =6)

Print 'Correction de F_TAB /FONCTIONENTREPRISE par rapport / F_TAB / CODECERFA '
--SELECT TA_VARIABLECODETABLELIEE FROM F_TAB WHERE TA_VARIABLECODETABLELIEE IS NOT NULL AND TA_TYPETABLE = 13 AND TA_VARIABLECODETABLELIEE NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =12)
UPDATE F_TAB
SET 
	TA_VARIABLECODETABLELIEE = NULL 
WHERE 
  TA_TYPETABLE = 13 AND 
  TA_VARIABLECODETABLELIEE IS NOT NULL AND 
  TA_VARIABLECODETABLELIEE NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =12)

Print 'Correction de F_RENSSAL par rapport / F_TAB / FonctionEntreprise '
--SELECT FP_CODEFCTENTREPRISE FROM F_RENSSAL WHERE FP_CODEFCTENTREPRISE IS NOT NULL AND FP_CODEFCTENTREPRISE NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =13)
UPDATE F_RENSSAL
SET 
	FP_CODEFCTENTREPRISE = NULL 
WHERE 
	FP_CODEFCTENTREPRISE IS NOT NULL AND 	
	FP_CODEFCTENTREPRISE NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =13)


Print 'Correction de F_RENSSAL par rapport / F_TAB / EMPLOI '
--SELECT FP_CODEEMPLOI FROM F_RENSSAL WHERE FP_CODEEMPLOI IS NOT NULL AND FP_CODEEMPLOI NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =14)
UPDATE F_RENSSAL
SET 
	FP_CODEEMPLOI = NULL 
WHERE 
	FP_CODEEMPLOI IS NOT NULL AND 	
	FP_CODEEMPLOI NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =14)


Print 'Correction de F_RUB par rapport / F_TAB / DUCS '
--SELECT RU_CODEDUCS FROM F_RUB WHERE RU_CODEDUCS IS NOT NULL AND RU_CODEDUCS NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =6)
/*INSERT INTO F_TAB (TA_TYPETABLE, TA_CODE, TA_Intitule, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT 
	6,
	RU_CODEDUCS,
        'Fiche de conversion',
	0,
        ((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=6)+1)	 
FROM 
	F_RUB 
WHERE  
	RU_CODEDUCS IS NOT NULL AND
	RU_CODEDUCS NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =6)*/

UPDATE F_RUB
SET 
	RU_CODEDUCS = NULL 
WHERE  
	RU_CODEDUCS IS NOT NULL AND
	RU_CODEDUCS NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =6)


Print 'Correction de F_RUB par rapport / F_KES '
--SELECT RU_CAISSE FROM F_RUB WHERE RU_CAISSE IS NOT NULL AND RU_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_RUB
SET 
	RU_CAISSE = NULL 
WHERE 
	RU_CAISSE IS NOT NULL AND 	
	RU_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)


Print 'Suppression des F_HCOT inexistants par rapport / F_RUB'
--SELECT HC_RUBRIQUE FROM F_HCOT WHERE HC_RUBRIQUE IS NOT NULL AND HC_RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
DELETE FROM F_HCOT
WHERE 
	HC_RUBRIQUE IS NOT NULL AND 	
	HC_RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)


Print 'Suppression des F_HBNS inexistants par rapport / F_RUB'
--SELECT HB_RUBRIQUE FROM F_HBNS WHERE HB_RUBRIQUE IS NOT NULL AND HB_RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
DELETE FROM F_HBNS
WHERE 
	HB_RUBRIQUE IS NOT NULL AND 	
	HB_RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)


Print 'Insertion dans F_CPA des comptes analytiques par rapport / F_PAI '
--SELECT PP_ANALYTIQUEATTENTE FROM F_PAI WHERE PP_ANALYTIQUEATTENTE IS NOT NULL AND PP_ANALYTIQUEATTENTE NOT IN (SELECT CA_COMPTE FROM F_CPA)
INSERT INTO F_CPA (CA_COMPTE,CA_INTITULE,CA_PLAN,ZR_CA_CumulsFaux,IdOdbc) 
SELECT DISTINCT
	PP_ANALYTIQUEATTENTE,
	'Fiche de conversion', 
	0,
	0,
	((Select Max(IdOdbc) From F_CPA)+1)	 
FROM 
	F_PAI
WHERE  
	PP_ANALYTIQUEATTENTE IS NOT NULL AND
	PP_ANALYTIQUEATTENTE NOT IN (SELECT CA_COMPTE FROM F_CPA)


Print 'Insertion dans F_CPG des comptes généraux par rapport / F_PAI '
--SELECT PP_GENERALATTENTE FROM F_PAI WHERE PP_GENERALATTENTE IS NOT NULL AND PP_GENERALATTENTE NOT IN (SELECT CG_COMPTE FROM F_CPG)
INSERT INTO F_CPG (CG_TypeCOMPTE,CG_COMPTE,CG_INTITULE,ZR_CG_CumulsFaux, IdOdbc) 
SELECT DISTINCT
	0,
	PP_GENERALATTENTE,
	'Fiche de conversion', 
	0,
	((Select Max(IdOdbc) From F_CPG)+1)	 
FROM 
	F_PAI
WHERE  
	PP_GENERALATTENTE IS NOT NULL AND
	PP_GENERALATTENTE NOT IN (SELECT CG_COMPTE FROM F_CPG)


Print 'Correction de F_PAI par rapport / F_RUB '
--SELECT PP_CALCULENVERS_MODE01RUBRIQUE FROM F_PAI WHERE PP_CALCULENVERS_MODE01RUBRIQUE IS NOT NULL AND PP_CALCULENVERS_MODE01RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_PAI
SET
	PP_CALCULENVERS_MODE01RUBRIQUE = NULL
WHERE 
	PP_CALCULENVERS_MODE01RUBRIQUE IS NOT NULL AND 	
	PP_CALCULENVERS_MODE01RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
--SELECT PP_CALCULENVERS_MODE02RUBRIQUE FROM F_PAI WHERE PP_CALCULENVERS_MODE02RUBRIQUE IS NOT NULL AND PP_CALCULENVERS_MODE02RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_PAI
SET 
	PP_CALCULENVERS_MODE02RUBRIQUE = NULL
WHERE 
	PP_CALCULENVERS_MODE02RUBRIQUE IS NOT NULL AND 	
	PP_CALCULENVERS_MODE02RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
--SELECT PP_CALCULENVERS_MODE03RUBRIQUE FROM F_PAI WHERE PP_CALCULENVERS_MODE03RUBRIQUE IS NOT NULL AND PP_CALCULENVERS_MODE03RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_PAI
SET 
	PP_CALCULENVERS_MODE03RUBRIQUE = NULL
WHERE 
	PP_CALCULENVERS_MODE03RUBRIQUE IS NOT NULL AND 	
	PP_CALCULENVERS_MODE03RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)

--SELECT PP_CALCULENVERS_MODE04RUBRIQUE FROM F_PAI WHERE PP_CALCULENVERS_MODE04RUBRIQUE IS NOT NULL AND PP_CALCULENVERS_MODE04RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_PAI
SET 
	PP_CALCULENVERS_MODE04RUBRIQUE = NULL
WHERE 
	PP_CALCULENVERS_MODE04RUBRIQUE IS NOT NULL AND
	PP_CALCULENVERS_MODE04RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)

--SELECT PP_CALCULENVERS_MODE05RUBRIQUE FROM F_PAI WHERE PP_CALCULENVERS_MODE05RUBRIQUE IS NOT NULL AND PP_CALCULENVERS_MODE05RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_PAI
SET
	PP_CALCULENVERS_MODE05RUBRIQUE = NULL
WHERE
	PP_CALCULENVERS_MODE05RUBRIQUE IS NOT NULL AND
	PP_CALCULENVERS_MODE05RUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)


--SELECT PP_RubriqueCmdTitreResto FROM F_PAI WHERE PP_RubriqueCmdTitreResto IS NOT NULL AND PP_RubriqueCmdTitreResto NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_PAI
SET
	PP_RubriqueCmdTitreResto = NULL
WHERE
	((PP_RubriqueCmdTitreResto =0)  OR (PP_RubriqueCmdTitreResto IS NOT NULL)) AND
	PP_RubriqueCmdTitreResto NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)



Print 'Correction dans F_HAFF par rapport / F_ORG / CATEGORIE '
--SELECT OR_CATEGORIE FROM F_HAFF WHERE OR_CATEGORIE IS NOT NULL AND OR_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =3)
UPDATE F_HAFF
SET 
	OR_CATEGORIE = NULL
WHERE 
	OR_CATEGORIE IS NOT NULL AND 	
	OR_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =3)

Print 'Correction dans F_HAFF par rapport / F_ORG / UNITE '
--SELECT OR_UNITE FROM F_HAFF WHERE OR_UNITE IS NOT NULL AND OR_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =2)
UPDATE F_HAFF
SET 
	OR_UNITE = NULL
WHERE 
	OR_UNITE IS NOT NULL AND 	
	OR_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =2)

Print 'Correction dans F_HAFF par rapport / F_ORG / SERVICE '
--SELECT OR_SERVICE FROM F_HAFF WHERE OR_SERVICE IS NOT NULL AND OR_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =1)
UPDATE F_HAFF
SET 
	OR_SERVICE = NULL
WHERE 
	OR_SERVICE IS NOT NULL AND 	
	OR_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =1)

Print 'Correction dans F_HAFF par rapport / F_ORG / DEPARTEMENT '
--SELECT OR_DEPARTEMENT FROM F_HAFF WHERE OR_DEPARTEMENT IS NOT NULL AND OR_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 0)
UPDATE F_HAFF
SET 
	OR_DEPARTEMENT = NULL
WHERE 
	OR_DEPARTEMENT IS NOT NULL AND 	
	OR_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =0)

Print 'Correction dans F_HAFF par rapport / F_ETA  dans ce cas on affecte le code ETABLISSEMENT de F_PSC '
--SELECT ET_Etablissement FROM F_HAFF WHERE ET_Etablissement IS NOT NULL AND ET_Etablissement NOT IN (SELECT ET_CODE FROM F_ETA)
UPDATE F_HAFF 
SET 
	ET_ETABLISSEMENT = p.ET_ETABLISSEMENT
FROM
	F_PSC p
WHERE 
	F_HAFF.ET_ETABLISSEMENT IS NOT NULL AND 	
	F_HAFF.ET_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Insertion dans F_COL des conventions collectives par rapport / F_ETA '
--SELECT CO_CONVENTION FROM F_ETA WHERE CO_CONVENTION IS NOT NULL AND CO_CONVENTION NOT IN (SELECT CO_CODE FROM F_COL)
INSERT INTO F_COL (CO_CODE,CO_INTITULE,ZR_HC_FLAGINFOSGENERAL, IdOdbc) 
SELECT DISTINCT
	CO_CONVENTION,
	'Fiche de conversion', 
	0,
	((Select Max(IdOdbc) From F_COL)+1)
FROM 
	F_ETA
WHERE  
	CO_CONVENTION IS NOT NULL AND
	CO_CONVENTION NOT IN (SELECT CO_CODE FROM F_COL)

Print 'Correction dans F_ETA par rapport / F_KES '
--SELECT KE_CAISSE FROM F_ETA WHERE KE_CAISSE IS NOT NULL AND KE_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_ETA
SET 
	KE_CAISSE = NULL
WHERE 
	KE_CAISSE IS NOT NULL AND 	
	KE_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)

--SSO 11/10/04
Print 'Correction de F_KES par rapport / F_KES '
--SELECT KE_DUCS_P_CaisseRegroupement FROM F_KES WHERE KE_DUCS_P_CaisseRegroupement IS NOT NULL AND KE_DUCS_P_CaisseRegroupement NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_KES
SET 
	KE_DUCS_P_CaisseRegroupement = NULL 
WHERE 
	KE_DUCS_P_CaisseRegroupement IS NOT NULL AND 	
	KE_DUCS_P_CaisseRegroupement NOT IN (SELECT KE_CODE FROM F_KES)
--Fin SSO 11/10/04

Print 'Correction dans F_PARFOR_GHR_PRC par rapport / F_GHR '
--SELECT GR_HREPOSACQUIS FROM F_PARFOR_GHR_PRC WHERE GR_HREPOSACQUIS IS NOT NULL AND GR_HREPOSACQUIS NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PRC
SET 
	GR_HREPOSACQUIS = NULL
WHERE 
	GR_HREPOSACQUIS IS NOT NULL AND 	
	GR_HREPOSACQUIS NOT IN (SELECT NE_CODE FROM F_GHR)

--SELECT GR_JREPOSACQUIS FROM F_PARFOR_GHR_PRC WHERE GR_JREPOSACQUIS IS NOT NULL AND GR_JREPOSACQUIS NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PRC
SET 
	GR_JREPOSACQUIS = NULL
WHERE 
	GR_JREPOSACQUIS IS NOT NULL AND 	
	GR_JREPOSACQUIS NOT IN (SELECT NE_CODE FROM F_GHR)


--SELECT GR_NATUREEVENEMENT FROM F_PARFOR_GHR_PRC WHERE GR_NATUREEVENEMENT IS NOT NULL AND GR_NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PRC
SET 
	GR_NATUREEVENEMENT = NULL
WHERE 
	GR_NATUREEVENEMENT IS NOT NULL AND 	
	GR_NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)



Print 'Correction dans F_GHR par rapport / F_GHR '
--SELECT NE_CODESUIVANTE FROM F_GHR WHERE NE_CODESUIVANTE IS NOT NULL AND NE_CODESUIVANTE NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_GHR
SET 
	NE_CODESUIVANTE = NULL
WHERE 
	NE_CODESUIVANTE IS NOT NULL AND 	
	NE_CODESUIVANTE NOT IN (SELECT NE_CODE FROM F_GHR)

Print 'Correction dans F_GHR par rapport / F_GHRTAB / REGROUPEMENT '
--SELECT GT_CODEREGROUPEMENT FROM F_GHR WHERE GT_CODEREGROUPEMENT IS NOT NULL AND GT_CODEREGROUPEMENT NOT IN (SELECT GT_CODE from F_GHRTAB WHERE GT_TYPEAFFECTATION = 5)
UPDATE F_GHR
SET 
	GT_CODEREGROUPEMENT = NULL
WHERE 
	GT_CODEREGROUPEMENT IS NOT NULL AND 	
	GT_CODEREGROUPEMENT NOT IN (SELECT GT_CODE from F_GHRTAB WHERE GT_TYPEAFFECTATION = 5)

Print 'Correction dans F_GHR par rapport / F_RUB '
--SELECT NE_EDITIONAPRESRUB FROM F_GHR WHERE NE_EDITIONAPRESRUB IS NOT NULL AND NE_EDITIONAPRESRUB NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_GHR
SET 
	NE_EDITIONAPRESRUB = NULL
WHERE 
	NE_EDITIONAPRESRUB IS NOT NULL AND 	
	NE_EDITIONAPRESRUB NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)


--SELECT RU_RUBRIQUEANALYTIQUE FROM F_GHR WHERE RU_RUBRIQUEANALYTIQUE IS NOT NULL AND RU_RUBRIQUEANALYTIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
UPDATE F_GHR
SET 
	RU_RUBRIQUEANALYTIQUE = NULL
WHERE 
	RU_RUBRIQUEANALYTIQUE IS NOT NULL AND 	
	RU_RUBRIQUEANALYTIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)

Print 'Correction de F_GHR par rapport / F_TAB / MOTIF ARRET '
--SELECT TA_MOTIFARRET FROM F_GHR WHERE TA_MOTIFARRET IS NOT NULL AND TA_MOTIFARRET NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =0)
UPDATE F_GHR
SET 
	TA_MOTIFARRET = NULL 
WHERE 
	TA_MOTIFARRET IS NOT NULL AND 	
	TA_MOTIFARRET NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =0)


Print 'Correction dans F_HST_AFFECTATION par rapport / F_ORG / CATEGORIE '
--SELECT AO_AFFECTATION_CATEGORIE FROM F_HST_AFFECTATION WHERE AO_AFFECTATION_CATEGORIE IS NOT NULL AND AO_AFFECTATION_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =3)
UPDATE F_HST_AFFECTATION
SET 
	AO_AFFECTATION_CATEGORIE = NULL
WHERE 
	AO_AFFECTATION_CATEGORIE IS NOT NULL AND 	
	AO_AFFECTATION_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =3)

Print 'Correction dans F_HST_AFFECTATION par rapport / F_ORG / UNITE '
--SELECT AO_AFFECTATION_UNITE FROM F_HST_AFFECTATION WHERE AO_AFFECTATION_UNITE IS NOT NULL AND AO_AFFECTATION_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =2)
UPDATE F_HST_AFFECTATION
SET 
	AO_AFFECTATION_UNITE = NULL
WHERE 
	AO_AFFECTATION_UNITE IS NOT NULL AND 	
	AO_AFFECTATION_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =2)

Print 'Correction dans F_HST_AFFECTATION par rapport / F_ORG / SERVICE '
--SELECT AO_AFFECTATION_SERVICE FROM F_HST_AFFECTATION WHERE AO_AFFECTATION_SERVICE IS NOT NULL AND AO_AFFECTATION_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =1)
UPDATE F_HST_AFFECTATION
SET 
	AO_AFFECTATION_SERVICE = NULL
WHERE 
	AO_AFFECTATION_SERVICE IS NOT NULL AND 	
	AO_AFFECTATION_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =1)

Print 'Correction dans F_HST_AFFECTATION par rapport / F_ORG / DEPARTEMENT '
--SELECT AO_AFFECTATION_DEPARTEMENT FROM F_HST_AFFECTATION WHERE AO_AFFECTATION_DEPARTEMENT IS NOT NULL AND AO_AFFECTATION_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 0)
UPDATE F_HST_AFFECTATION
SET 
	AO_AFFECTATION_DEPARTEMENT = NULL
WHERE 
	AO_AFFECTATION_DEPARTEMENT IS NOT NULL AND 	
	AO_AFFECTATION_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE =0)

Print 'Suppression des F_HST_ETABLISSEMENT ayant de codes inexistants par rapport / F_ETA'
-- SELECT AE_Etab_Code FROM F_HST_ETABLISSEMENT WHERE AE_Etab_Code IS NOT NULL AND  AE_Etab_Code NOT IN (SELECT ET_CODE FROM F_ETA )
DELETE FROM F_HST_ETABLISSEMENT
WHERE 
	AE_ETAB_CODE IS NOT NULL AND  
	AE_ETAB_CODE NOT IN (SELECT ET_CODE FROM F_ETA )

Print 'Suppression des doublons dans F_CST'
--Insertion des Code et NoOrdre presentant de doublons dans une table temporaire
SELECT CT_CODE, CT_NOORDRE
INTO #cstDoublon
FROM 
	F_CST
GROUP BY CT_CODE, CT_NOORDRE
HAVING
	COUNT(*)>1
--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #tempcst
FROM f_CST c, #cstDoublon d
WHERE 
	c.CT_CODE = d.CT_CODE AND
	c.CT_NOORDRE = d.CT_NOORDRE
	
--Suppression des doublons dans la table initiale
DELETE F_CST FROM  #cstDoublon d
WHERE 
	F_CST.CT_CODE = d.CT_CODE AND
	F_CST.CT_NOORDRE = d.CT_NOORDRE
 
--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempcst ADD Compteur INT identity (1,1)
GO
--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_CST
SELECT
   CT_Code,CT_TypeCst,CT_CodeOperateur, CT_Operande1, CT_Operande2, CT_Operande3, CT_Constante1,CT_Constante2,
   CT_Constante3,CT_Constante4,ZR_CT_FlagInfosGenerales, CT_Intitule, CT_CodeMemo, CT_NoOrdre, CT_DateEffet,
   CT_CodeProvenance, CT_DateModification, CT_Arrondi, CT_MethodeArrondi, CT_DateDeMiseAJour, CT_CodeDeMiseAJour,
   CT_MotifDeMiseAJour, CT_DateDeDebutDApplication, CT_DateDeFinDApplication,
   CT_DescriptifDeMiseAJour, CT_SpecificitesPopulation, CT_SpecificitesCommentaire, CT_Visible, IdOdbc
FROM #tempcst t
WHERE Compteur = (Select min(t1.Compteur) from #tempcst t1 WHERE  t1.CT_CODE = t.CT_CODE  AND 
								  t1.CT_NOORDRE = t.CT_NOORDRE		
	     	  Group by t1.CT_CODE, t1.CT_NOORDRE  )


DROP TABLE #tempcst
DROP TABLE #cstDoublon


Print 'Suppression des doublons dans F_CPG'
--Insertion des Codecompte presentant de doublons dans une table temporaire
SELECT CG_COMPTE
INTO #cpgdoublon
FROM
	F_CPG
GROUP BY CG_COMPTE
HAVING
	COUNT(*)>1	

--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #tempcpg
FROM f_CPG c, #cpgDoublon d
WHERE 
	c.CG_COMPTE = d.CG_COMPTE 
	
--Suppression des doublons dans la table initiale
DELETE F_CPG FROM  #tempcpg d
WHERE 
	f_CPG.CG_COMPTE = d.CG_COMPTE 

--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempcpg ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_CPG
SELECT 
	CG_TypeCompte, CG_Compte, CG_Intitule,CG_IntituleAbrege, CG_Sens, ZR_CG_Acces, ZR_CG_Caracteristique, ZR_CG_Cpt_Correspond1, ZR_CG_Cpt_Correspond2,
	ZR_CG_DateOuverture,ZR_CG_DateMAJ,ZR_CG_DateFermeture,ZR_CG_Code_Quantite, ZR_CG_Solde, ZR_CG_Totalisation, ZR_CG_Saut_Page, ZR_CG_DerNumLettrage,
	ZR_CG_CumulsFaux, IdOdbc 
FROM #tempcpg t 
WHERE Compteur = (Select min(t1.Compteur) from #tempcpg t1 WHERE  t1.CG_COMPTE = t.CG_COMPTE Group by t1.CG_COMPTE  )

DROP TABLE #tempcpg
DROP TABLE #cpgDoublon

Print 'Suppression des doublons dans F_CPA'
--Insertion des Codecompte presentant de doublons dans une table temporaire
SELECT CA_Compte,CA_Plan
INTO #cpaDoublon
FROM 
	F_CPA
GROUP BY CA_Compte,CA_Plan
HAVING
	COUNT(*)>1	

--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #tempcpa
FROM f_CPA c, #cpaDoublon d
WHERE 
	c.CA_COMPTE = d.CA_COMPTE AND
	c.CA_PLAN = d.CA_PLAN
	
--Suppression des doublons dans la table initiale
DELETE F_CPA FROM  #cpaDoublon d
WHERE F_CPA.CA_COMPTE = d.CA_COMPTE AND
      F_CPA.CA_PLAN =  d.CA_PLAN

--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempcpa ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_CPA
SELECT 
	ZR_CA_Zonereservee,CA_Intitule,CA_IntituleAbrege, ZR_CA_Acces, ZR_CA_DateOpen,
        ZR_CA_DateMaj, ZR_CA_DateClose, ZR_CA_CompteTrie, ZR_CA_CumulsFaux, CA_Plan, CA_Compte, IdOdbc  
FROM #tempcpa t 
WHERE Compteur = (Select min(t1.Compteur) from #tempcpa t1 WHERE  t1.CA_COMPTE = t.CA_COMPTE AND
      								  t1.CA_PLAN =  t.CA_PLAN 
		  Group by t1.CA_COMPTE,t1.CA_PLAN  )

DROP TABLE #tempcpa
DROP TABLE #cpaDoublon

--SASOR : le 20/01/2005
Print 'Suppression des doublons dans F_VAN'
--Insertion des lignes presentant de doublons dans une table temporaire
SELECT VA_Ventilation,VA_CptGeneraux, RU_Rubrique,AXE_Code,VA_NoOrdre,VA_CptAnalytique
INTO #vandoublon
FROM  F_VAN
GROUP BY
 VA_Ventilation,VA_CptGeneraux, RU_Rubrique,AXE_Code,VA_NoOrdre,VA_CptAnalytique
HAVING COUNT(*) > 1

--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT v.*
INTO #tempvan
FROM f_VAN v, #vandoublon d
WHERE 
	ISNULL(v.VA_Ventilation,'') = ISNULL(d.VA_Ventilation,'') AND
	ISNULL(v.VA_CptGeneraux,'') = ISNULL(d.VA_CptGeneraux,'') AND
	ISNULL(v.RU_Rubrique,'')    = ISNULL(d.RU_Rubrique,'') AND	  
	ISNULL(v.AXE_Code,'')      = ISNULL(d.AXE_Code,'') AND
	ISNULL(v.VA_NoOrdre,'')     = ISNULL(d.VA_NoOrdre,'') AND
	ISNULL(v.VA_CptAnalytique,'') = ISNULL(d.VA_CptAnalytique,'')
	
--Suppression des doublons dans la table initiale
DELETE F_VAN FROM  #tempvan d
WHERE 
	ISNULL(F_VAN.VA_Ventilation,'') = ISNULL(d.VA_Ventilation,'') AND
	ISNULL(F_VAN.VA_CptGeneraux,'') = ISNULL(d.VA_CptGeneraux,'') AND
	ISNULL(F_VAN.RU_Rubrique,'')    = ISNULL(d.RU_Rubrique,'') AND	  
	ISNULL(F_VAN.AXE_Code,'')       = ISNULL(d.AXE_Code,'') AND
	ISNULL(F_VAN.VA_NoOrdre,'')     = ISNULL(d.VA_NoOrdre,'') AND
	ISNULL(F_VAN.VA_CptAnalytique,'') = ISNULL(d.VA_CptAnalytique,'')
	

--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempvan ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_VAN
SELECT 
	VA_Ventilation, VA_CptGeneraux, zone_reservee, VA_Pourcentage, RU_Rubrique, VA_Valeur,
        VA_NoOrdre, ZR_VA_FlagInfosGenerales, VA_NumeroCloture, VA_TypeVentilation, AXE_Code, VA_CptAnalytique, IdOdbc
FROM #tempvan t 
WHERE Compteur = (Select min(t1.Compteur) from #tempvan t1 WHERE 
						ISNULL(t1.VA_Ventilation,'') = ISNULL(t.VA_Ventilation,'') AND 
						ISNULL(t1.VA_CptGeneraux,'') = ISNULL(t.VA_CptGeneraux,'') AND
						ISNULL(t1.RU_Rubrique,'')    = ISNULL(t.RU_Rubrique,'') AND	 
						ISNULL(t1.AXE_Code,'')       = ISNULL(t.AXE_Code,'') AND
						ISNULL(t1.VA_NoOrdre,'')     = ISNULL(t.VA_NoOrdre,'') AND
						ISNULL(t1.VA_CptAnalytique,'') = ISNULL(t.VA_CptAnalytique,'')
	 				 Group by t1.VA_Ventilation, t1.VA_CptGeneraux, t1.RU_Rubrique,
				t1.AXE_Code,t1.VA_NoOrdre,t1.VA_CptAnalytique )

DROP TABLE #tempvan
DROP TABLE #vanDoublon
--FIN SASOR : le 20/01/2005
Print 'Insertion dans F_TAB des codes Statut professionnel par rapport / F_RENSSAL'
--SELECT FP_InfosDADSU_StatutProf FROM F_RENSSAL WHERE FP_InfosDADSU_StatutProf IS NOT NULL AND FP_InfosDADSU_StatutProf NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 17)
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc)
SELECT DISTINCT
	17,
	FP_InfosDADSU_StatutProf,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=17)+1)	 
FROM 
	F_RENSSAL
WHERE  
	FP_InfosDADSU_StatutProf IS NOT NULL AND
	FP_InfosDADSU_StatutProf NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 17)

Print 'Insertion dans F_TAB des codes Statut professionnel par rapport / F_HST_DADSU'
--SELECT ADU_STATUTPROFESSIONNEL FROM F_HST_DADSU WHERE ADU_STATUTPROFESSIONNEL IS NOT NULL AND ADU_STATUTPROFESSIONNEL NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 17)
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	17,
	ADU_STATUTPROFESSIONNEL,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=17)+1)	  
FROM 
	F_HST_DADSU 
WHERE  
	ADU_STATUTPROFESSIONNEL IS NOT NULL AND
	ADU_STATUTPROFESSIONNEL NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 17)

Print 'Suppression dans F_PARTICIPANT, des participants ayant des codes formation inexistants par rapport / F_FORMATION'
--SELECT FO_FORMATION FROM F_PARTICIPANT WHERE FO_FORMATION IS NOT NULL AND FO_FORMATION NOT IN (SELECT FO_CODE FROM F_FORMATION)  
DELETE FROM F_PARTICIPANT
WHERE 
	FO_FORMATION IS NOT NULL AND	
	FO_FORMATION NOT IN (SELECT FO_CODE FROM F_FORMATION)  


Print 'Suppression dans F_EFFECTIFPARCATEGORIE, des EFFECTIFS/CATEGORIE ayant des codes formation inexistants par rapport / F_FORMATION'
--SELECT FO_FORMATION FROM F_EFFECTIFPARCATEGORIE WHERE FO_FORMATION IS NOT NULL AND FO_FORMATION NOT IN (SELECT FO_CODE FROM F_FORMATION)  
DELETE FROM F_EFFECTIFPARCATEGORIE
WHERE 
	FO_FORMATION IS NOT NULL AND	
	FO_FORMATION NOT IN (SELECT FO_CODE FROM F_FORMATION)  


Print 'Suppression dans F_EFFECTIFPARCATEGORIE, des EFFECTIFS/CATEGORIE ayant des codes categorie inexistants par rapport / F_ORG / CATEGORIE '
--SELECT OR_Categorie FROM F_EFFECTIFPARCATEGORIE WHERE OR_Categorie IS NOT NULL AND OR_Categorie NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 3 )  
DELETE FROM F_EFFECTIFPARCATEGORIE
WHERE 
	OR_Categorie IS NOT NULL AND 
	OR_Categorie NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 3 )  


Print 'Correction de F_FORMATION par rapport / F_PARFOR_PG / F_PARFOR_PER '
/*
--SELECT FO_PERIODEDECALCUL FROM F_FORMATION WHERE FO_PERIODEDECALCUL IS NOT NULL 
UPDATE F_FORMATION
SET 
	FO_PERIODEDECALCUL = FT_INDICEFICHE 
FROM F_PARFOR_PER 
WHERE 
	YEAR(FO_DATEDEBUT) IN (YEAR(FT_DateDebutPeriode),YEAR(FT_DateFinPeriode)) AND
	FO_PERIODEDECALCUL IS NOT NULL
*/
DECLARE @IREF tinyint
select @IREF=PF_PeriodeRef FROM F_PARFOR_PG
Print 'Affectation de la Periode de reference : ' + STR(@IREF)

UPDATE F_FORMATION
SET 
  FO_01PERIODEDECALCUL=@IREF
WHERE 
	FO_01PERIODEDECALCUL IS NOT NULL AND 	
	FO_01PERIODEDECALCUL <> 0 AND 	
	FO_01PERIODEDECALCUL not in (select FT_IndiceFiche From F_PARFOR_PER where FO_01PERIODEDECALCUL=FT_IndiceFiche)

UPDATE F_FORMATION
SET 
  FO_02PERIODEDECALCUL=@IREF
WHERE 
	FO_02PERIODEDECALCUL IS NOT NULL AND 	
	FO_02PERIODEDECALCUL <> 0 AND 	
	FO_02PERIODEDECALCUL not in (select FT_IndiceFiche From F_PARFOR_PER where FO_02PERIODEDECALCUL=FT_IndiceFiche)

UPDATE F_FORMATION
SET 
  FO_03PERIODEDECALCUL=@IREF
WHERE 
	FO_03PERIODEDECALCUL IS NOT NULL AND 	
	FO_03PERIODEDECALCUL <> 0 AND 	
	FO_03PERIODEDECALCUL not in (select FT_IndiceFiche From F_PARFOR_PER where FO_03PERIODEDECALCUL=FT_IndiceFiche)

UPDATE F_FORMATION
SET 
  FO_04PERIODEDECALCUL=@IREF
WHERE 
	FO_04PERIODEDECALCUL IS NOT NULL AND 	
	FO_04PERIODEDECALCUL <> 0 AND 	
	FO_04PERIODEDECALCUL not in (select FT_IndiceFiche From F_PARFOR_PER where FO_04PERIODEDECALCUL=FT_IndiceFiche)

UPDATE F_FORMATION
SET 
  FO_05PERIODEDECALCUL=@IREF
WHERE 
	FO_05PERIODEDECALCUL IS NOT NULL AND 	
	FO_05PERIODEDECALCUL <> 0 AND 	
	FO_05PERIODEDECALCUL not in (select FT_IndiceFiche From F_PARFOR_PER where FO_05PERIODEDECALCUL=FT_IndiceFiche)


Print 'Insertion dans F_TAB des codes NATIONALITE par rapport / F_RENSSAL'
--SELECT FP_NATION FROM F_RENSSAL WHERE FP_NATION IS NOT NULL AND FP_NATION NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 4)
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	4,
	FP_NATION,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=4)+1)	 
FROM 
	F_RENSSAL 
WHERE  
	FP_NATION IS NOT NULL AND
	FP_NATION NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 4)

Print 'Insertion dans F_TAB des codes NATIONALITE par rapport / F_HST_NATIONALITE'
--SELECT AN_NATION FROM F_HST_NATIONALITE WHERE AN_NATION IS NOT NULL AND AN_NATION NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 4)
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	4,
	AN_NATION,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=4)+1)	 
FROM 
	F_HST_NATIONALITE 
WHERE  
	AN_NATION IS NOT NULL AND
	AN_NATION NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 4)


Print 'Suppression des F_MOD inexistants par rapport / F_ECR'
--SELECT MO_NOLIGNE, MO_NOLIEN FROM F_MOD WHERE MO_NOLIGNE IS NOT NULL AND MO_NOLIEN  IS NOT NULL AND (CONVERT(VARCHAR(5),MO_NOLIGNE)+ CONVERT(VARCHAR(5),MO_NOLIEN)) NOT IN (SELECT CONVERT(VARCHAR(5), ZR_EG_NOLIGNE) + CONVERT(VARCHAR(5),ZR_EG_NOLIEN)FROM F_ECR) 	
DELETE FROM F_MOD
WHERE 
	MO_NOLIGNE IS NOT NULL AND 
	MO_NOLIEN  IS NOT NULL AND 
	(CONVERT(VARCHAR(5),MO_NOLIGNE)+ CONVERT(VARCHAR(5),MO_NOLIEN)) 
		NOT IN (SELECT CONVERT(VARCHAR(5), ZR_EG_NOLIGNE) + CONVERT(VARCHAR(5),ZR_EG_NOLIEN)FROM F_ECR) 	

Print 'Suppression des F_TAB / DUCS ayant des codes caisse non affectes'
--SELECT TA_CAISSE ,* FROM F_TAB WHERE  TA_TYPETABLE = 6 AND TA_CAISSE IS NULL
DELETE FROM F_TAB 
WHERE 
	TA_TYPETABLE = 6 AND 
	TA_CAISSE IS NULL


Print 'Suppression des salaries dans F_RENSSAL ayant des matricules ou numeros non affectes'
--SELECT * FROM F_RENSSAL WHERE SA_MATRICULE IS NULL OR SA_NO_SALARIE IS NULL
DELETE FROM F_RENSSAL 
WHERE 
	SA_MATRICULE IS NULL OR 
	SA_NO_SALARIE IS NULL

Print 'Suppression des salaries dans F_SAL ayant des matricules ou numeros non affectes'
--SELECT * FROM F_SAL WHERE SA_MATRICULE IS NULL OR SA_NO_SALARIE IS NULL
DELETE FROM F_SAL 
WHERE 
	SA_MATRICULE IS NULL OR 
	SA_NO_SALARIE IS NULL

Print 'Suppression des F_BNSMOD ayant des bulletins modeles inexistants par rapport / F_BMOD'
--SELECT * FROM F_BNSMOD WHERE RM_NUMMOD IS NOT NULL AND RM_NUMMOD NOT IN(SELECT BM_COMPTEURNUMERO FROM F_BMOD)

DELETE FROM  F_BNSMOD
WHERE  
	RM_NUMMOD IS NOT NULL AND 	
	RM_NUMMOD NOT IN (SELECT BM_COMPTEURNUMERO FROM F_BMOD)

Print 'Suppression des F_COTMOD ayant des bulletins modeles inexistants par rapport / F_BMOD'
--SELECT * FROM F_COTMOD WHERE CM_NUMMOD IS NOT NULL AND CM_NUMMOD NOT IN (SELECT BM_COMPTEURNUMERO FROM F_BMOD)
DELETE FROM F_COTMOD 
WHERE 
	CM_NUMMOD IS NOT NULL AND 
	CM_NUMMOD NOT IN (SELECT BM_COMPTEURNUMERO FROM F_BMOD)

Print 'Suppression des F_BNSMOD ayant des rubriques inexistantes par rapport / F_RUB'
--SELECT RM_Numero FROM F_BNSMOD WHERE RM_Numero IS NOT NULL AND RM_Numero NOT IN(SELECT RU_RUBRIQUE FROM F_RUB)

DELETE FROM F_BNSMOD
WHERE  
	RM_Numero IS NOT NULL AND 	
	RM_Numero NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)

	Print 'Suppression des F_COTMOD inexistants par rapport / F_RUB'
--SELECT CM_Numero FROM F_COTMOD WHERE CM_Numero IS NOT NULL AND CM_Numero NOT IN (SELECT RU_Rubrique FROM F_RUB)
DELETE FROM F_COTMOD
WHERE 
	CM_Numero IS NOT NULL AND 
	CM_Numero NOT IN (SELECT RU_Rubrique FROM F_RUB)


Print 'Suppression des F_BILCUM ayant des codes departement inexistants par rapport / F_ORG / DEPARTEMENT '
--SELECT OR_DEPARTEMENT FROM F_BILCUM WHERE OR_DEPARTEMENT IS NOT NULL AND OR_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 0 )  
DELETE FROM F_BILCUM
WHERE 
	OR_DEPARTEMENT IS NOT NULL AND 
	OR_DEPARTEMENT NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 0 )  


Print 'Suppression des F_BILCUM ayant des codes service inexistants par rapport / F_ORG / SERVICE '
--SELECT OR_SERVICE FROM F_BILCUM WHERE OR_SERVICE IS NOT NULL AND OR_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 1 )  
DELETE FROM F_BILCUM
WHERE 
	OR_SERVICE IS NOT NULL AND 
	OR_SERVICE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 1 )  


Print 'Suppression des F_BILCUM ayant des codes unite inexistants par rapport / F_ORG / UNITE '
--SELECT OR_UNITE FROM F_BILCUM WHERE OR_UNITE IS NOT NULL AND OR_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 2 )  
DELETE FROM F_BILCUM
WHERE 
	OR_UNITE IS NOT NULL AND 
	OR_UNITE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 2 )  



Print 'Suppression des F_BILCUM ayant des codes categorie inexistants par rapport / F_ORG / CATEGORIE '
--SELECT OR_CATEGORIE FROM F_BILCUM WHERE OR_CATEGORIE IS NOT NULL AND OR_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 3 )  
DELETE FROM F_BILCUM
WHERE 
	OR_CATEGORIE IS NOT NULL AND 
	OR_CATEGORIE NOT IN (SELECT OR_CODE FROM F_ORG WHERE OR_TYPE = 3 )  

Print 'Insertion dans F_TAB des codes motif d''absence par rapport / F_HST_ARRET'
--SELECT AP_Arret_01Type FROM  F_HST_ARRET WHERE AP_Arret_01Type IS NOT NULL AND AP_Arret_01Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES,IdOdbc) 
SELECT DISTINCT
	0,
	AP_Arret_01Type,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=0)+1)	 
FROM 
	F_HST_ARRET
WHERE  
	AP_Arret_01Type IS NOT NULL AND 
	AP_Arret_01Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 

--SELECT AP_Arret_02Type FROM  F_HST_ARRET WHERE AP_Arret_02Type IS NOT NULL AND AP_Arret_02Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	0,
	AP_Arret_02Type,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=0)+1)	 
FROM 
	F_HST_ARRET
WHERE  
	AP_Arret_02Type IS NOT NULL AND 
	AP_Arret_02Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 

--SELECT AP_Arret_03Type FROM  F_HST_ARRET WHERE AP_Arret_03Type IS NOT NULL AND AP_Arret_03Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	0,
	AP_Arret_03Type,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=0)+1)	 
FROM 
	F_HST_ARRET
WHERE  
	AP_Arret_03Type IS NOT NULL AND 
	AP_Arret_03Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 

Print 'Insertion dans F_TAB des codes motif d''absence par rapport / F_RENSSAL'
--SELECT FP_Arret01Type FROM  F_RENSSAL WHERE FP_Arret01Type IS NOT NULL AND FP_Arret01Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	0,
	FP_Arret01Type,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=0)+1)	 	 
FROM 
	F_RENSSAL
WHERE  
	FP_Arret01Type IS NOT NULL AND 
	FP_Arret01Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 


--SELECT FP_Arret02Type FROM  F_RENSSAL WHERE FP_Arret02Type IS NOT NULL AND FP_Arret02Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	0,
	FP_Arret02Type,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=0)+1)	 	 
FROM 
	F_RENSSAL
WHERE  
	FP_Arret02Type IS NOT NULL AND 
	FP_Arret02Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 


--SELECT FP_Arret03Type FROM  F_RENSSAL WHERE FP_Arret03Type IS NOT NULL AND FP_Arret03Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	0,
	FP_Arret03Type,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=0)+1)	 	 
FROM 
	F_RENSSAL
WHERE  
	FP_Arret03Type IS NOT NULL AND 
	FP_Arret03Type NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 0) 


Print 'Suppression des F_DAS inexistants par rapport / F_RENSSAL'
--SELECT  * FROM F_DAS WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_DAS
WHERE 
	SA_MATRICULE IS NOT NULL AND 	
        SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Insertion dans F_TAB des codes type d''entree par rapport / F_RENSSAL'
--SELECT FP_Etab_TypeDEntree FROM F_RENSSAL WHERE FP_Etab_TypeDEntree IS NOT NULL AND FP_Etab_TypeDEntree NOT IN (SELECT TA_Code FROM F_TAB WHERE TA_TYPETABLE = 3)
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	3,
	FP_Etab_TypeDEntree,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=3)+1)	 	 
FROM 
	F_RENSSAL
WHERE  
	FP_Etab_TypeDEntree IS NOT NULL AND 
	FP_Etab_TypeDEntree NOT IN (SELECT TA_Code FROM F_TAB WHERE TA_TYPETABLE = 3)

Print 'Insertion dans F_TAB des codes type d''entree par rapport / F_HST_ETABLISSEMENT'
--SELECT AE_Etab_TypeDEntree FROM F_HST_ETABLISSEMENT WHERE AE_Etab_TypeDEntree IS NOT NULL AND AE_Etab_TypeDEntree NOT IN (SELECT TA_Code FROM F_TAB WHERE TA_TYPETABLE = 3)
INSERT INTO F_TAB (TA_TYPETABLE,TA_CODE,TA_INTITULE, ZR_TA_FLAGINFOSGENERALES, IdOdbc) 
SELECT DISTINCT
	3,
	AE_Etab_TypeDEntree,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_TAB Where TA_TypeTable=3)+1)	 
FROM 
	F_HST_ETABLISSEMENT
WHERE  
	AE_Etab_TypeDEntree IS NOT NULL AND 
	AE_Etab_TypeDEntree NOT IN (SELECT TA_Code FROM F_TAB WHERE TA_TYPETABLE = 3)

Print 'Insertion dans F_PREVOYANCE des codes prevoyance inexistants par rapport / F_RENSSAL'
--SELECT FP_Prevoyance_Code FROM F_RENSSAL WHERE FP_Prevoyance_Code IS NOT NULL AND FP_Prevoyance_Code NOT IN (SELECT CP_Code FROM F_PREVOYANCE)
INSERT INTO F_PREVOYANCE (CP_CODE, CP_INTITULE,ZR_CP_InfosGenerales,IdOdbc)
SELECT DISTINCT
	FP_Prevoyance_Code,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_Prevoyance)+1)
FROM F_RENSSAL 
WHERE 
	FP_Prevoyance_Code IS NOT NULL AND 
	FP_Prevoyance_Code NOT IN (SELECT CP_Code FROM F_PREVOYANCE)
UpDate F_Prevoyance Set IdOdbc = 1 Where IdOdbc is NULL 
Print 'Insertion dans F_PREVOYANCE des codes prevoyance inexistants par rapport / F_HST_PREVOYANCE'
--SELECT ACP_Prevoyance FROM F_HST_PREVOYANCE WHERE ACP_Prevoyance IS NOT NULL AND ACP_Prevoyance NOT IN (SELECT CP_Code FROM F_PREVOYANCE)
INSERT INTO F_PREVOYANCE (CP_CODE, CP_INTITULE,ZR_CP_InfosGenerales,IdOdbc)
SELECT DISTINCT
	ACP_Prevoyance,
	'Fiche de conversion',
	0,
	((Select Max(IdOdbc) From F_Prevoyance)+1)
FROM F_HST_PREVOYANCE 
WHERE 
	ACP_Prevoyance IS NOT NULL AND 
	ACP_Prevoyance NOT IN (SELECT CP_Code FROM F_PREVOYANCE)
UpDate F_Prevoyance Set IdOdbc = 1 Where IdOdbc is NULL

-- SSO 25/11/2003 ajout test sur F_RENSSAL
Print 'Suppression des F_BNSSAL inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT * FROM F_BNSSAL WHERE Sa_No_Salarie IS NOT NULL AND  Sa_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_BNSSAL 
WHERE 
	Sa_No_Salarie IS NOT NULL AND 
	Sa_No_Salarie NOT IN  (SELECT  SA_No_Salarie FROM F_SAL)

--SELECT * FROM F_BNSSAL WHERE Sa_No_Salarie IS NOT NULL AND  Sa_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_BNSSAL 
WHERE 
	Sa_No_Salarie IS NOT NULL AND 
	Sa_No_Salarie NOT IN  (SELECT  SA_No_Salarie FROM F_RENSSAL)


Print 'Suppression des F_BNSSAL inexistants par rapport / F_RUB'
--SELECT * FROM F_BNSSAL WHERE RB_Numero IS NOT NULL AND RB_Numero NOT IN (SELECT RU_Rubrique FROM F_RUB)
DELETE FROM F_BNSSAL 
WHERE 
	RB_Numero IS NOT NULL AND 
	RB_Numero NOT IN (SELECT RU_Rubrique FROM F_RUB)


-- SSO 25/11/2003 ajout test sur F_RENSSAL
Print 'Suppression des F_SALIV_A inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_No_Salarie FROM F_SALIV_A WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_SALIV_A
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)

--SELECT SA_No_Salarie FROM F_SALIV_A WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_SALIV_A
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)


-- SSO 25/11/2003 ajout test sur F_RENSSAL
Print 'Suppression des F_SALIV_B inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_No_Salarie FROM F_SALIV_B WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_SALIV_B
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
--SELECT SA_No_Salarie FROM F_SALIV_B WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_SALIV_B
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)

Print 'Suppression des F_SALIV_B inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_No_Salarie FROM F_SALIV_B WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_SALIV_B
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
--SELECT SA_No_Salarie FROM F_SALIV_B WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_SALIV_B
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)


-- SSO 25/11/2003
Print 'Suppression des F_SALIV_D inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_No_Salarie FROM F_SALIV_D WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_SALIV_D
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
--SELECT SA_No_Salarie FROM F_SALIV_D WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_SALIV_D
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)

Print 'Suppression des F_SALIV_E inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_No_Salarie FROM F_SALIV_E WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_SALIV_E
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
--SELECT SA_No_Salarie FROM F_SALIV_E WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_SALIV_E
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)

Print 'Suppression des F_SALIV_F inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_No_Salarie FROM F_SALIV_F WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
DELETE FROM F_SALIV_F
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)
--SELECT SA_No_Salarie FROM F_SALIV_F WHERE SA_No_Salarie IS NOT NULL AND SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)
DELETE FROM F_SALIV_F
WHERE 
	SA_No_Salarie IS NOT NULL AND 
	SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)

Print 'Suppression des F_RIB inexistants par rapport / F_ETA'
--SELECT ET_CODE FROM F_RIB WHERE ET_CODE IS NOT NULL AND ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE FROM F_RIB
WHERE 
	ET_CODE IS NOT NULL AND 
	ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Suppression des F_SEL inexistants par rapport / F_ECR'
--SELECT SE_NOLIGNE, SE_NOLIEN FROM F_SEL WHERE SE_NOLIGNE IS NOT NULL AND SE_NOLIEN IS NOT NULL AND SE_NOLIGNE <> 0 AND SE_NOLIEN  <> 0 AND (CONVERT(VARCHAR(5),SE_NOLIGNE)+ CONVERT(VARCHAR(5),SE_NOLIEN)) NOT IN (SELECT CONVERT(VARCHAR(5), ZR_EG_NOLIGNE) + CONVERT(VARCHAR(5),ZR_EG_NOLIEN)FROM F_ECR) 	
DELETE FROM F_SEL
WHERE 
	SE_NOLIGNE IS NOT NULL AND 
	SE_NOLIEN  IS NOT NULL AND 
	SE_NOLIGNE <> 0 AND 
	SE_NOLIEN  <> 0 AND 
	(CONVERT(VARCHAR(5),SE_NOLIGNE)+ CONVERT(VARCHAR(5),SE_NOLIEN)) 
		NOT IN (SELECT CONVERT(VARCHAR(5), ZR_EG_NOLIGNE) + CONVERT(VARCHAR(5),ZR_EG_NOLIEN)FROM F_ECR) 	

-- OLBD 25/07/2003

Print 'Suppression des F_COTSAL inexistants par rapport / F_RUB'
--SELECT CB_Numero FROM F_COTSAL WHERE CB_Numero NOT IN (SELECT RU_Rubrique FROM F_RUB)
DELETE FROM F_COTSAL
WHERE 
	CB_Numero NOT IN (SELECT RU_Rubrique FROM F_RUB)

-- SSO 06/10/2003
Print 'Suppression des F_HCOM inexistants par rapport / F_RUB'
--SELECT HM_CODERUBRIQUE FROM F_HCOM WHERE HM_CODERUBRIQUE IS NOT NULL AND HM_CODERUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
DELETE FROM F_HCOM
WHERE 
	HM_CODERUBRIQUE IS NOT NULL AND 	
	HM_CODERUBRIQUE NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)

--SSO 25/11/2003 > Modif 26/03/2013 >suppression fiches dont Matricule is NULL
Print 'Suppression des F_HST_ACCIDENTTRAVAIL inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_ACCIDENTTRAVAIL WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_ACCIDENTTRAVAIL WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_ACCIDENTTRAVAIL WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_ACCIDENTTRAVAIL WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_ADRESSE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_ADRESSE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_ADRESSE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_ADRESSE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_ADRESSE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)


Print 'Suppression des F_HST_AFFECTATION inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_AFFECTATION WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_AFFECTATION 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
--SELECT SA_MATRICULE FROM F_HST_AFFECTATION WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_AFFECTATION 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)


Print 'Suppression des F_HST_ARRET inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_ARRET WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_ARRET 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_ARRET WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_ARRET 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_BTP inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_BTP WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_BTP 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_BTP WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_BTP 
WHERE    
	SA_MATRICULE IS NOT NULL AND     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)


Print 'Suppression des F_HST_CHANGEMENT inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_CHANGEMENT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_CHANGEMENT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_CHANGEMENT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_CHANGEMENT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_COMMENT inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_COMMENT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_COMMENT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_COMMENT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_COMMENT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_CONGE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_CONGE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_CONGE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_CONGE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_CONGE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_CONTRAT inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_CONTRAT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_CONTRAT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_CONTRAT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_CONTRAT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)


Print 'Suppression des F_HST_DADSU inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_DADSU WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_DADSU 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_DADSU WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_DADSU 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)


Print 'Suppression des F_HST_ETABLISSEMENT inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_ETABLISSEMENT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_ETABLISSEMENT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_ETABLISSEMENT WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_ETABLISSEMENT 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_FAMILLE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_FAMILLE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_FAMILLE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_FAMILLE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_FAMILLE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_INFOSSOCIETE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_INFOSSOCIETE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_INFOSSOCIETE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_INFOSSOCIETE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_INFOSSOCIETE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_MEDECINE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_MEDECINE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_MEDECINE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_MEDECINE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_MEDECINE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_NATIONALITE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_NATIONALITE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_NATIONALITE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_NATIONALITE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_NATIONALITE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_POSTE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_POSTE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_POSTE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_POSTE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_POSTE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_PREVOYANCE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_PREVOYANCE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_PREVOYANCE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_PREVOYANCE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_PREVOYANCE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_SALAIRE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_SALAIRE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_SALAIRE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_SALAIRE WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_SALAIRE 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Suppression des F_HST_SECU inexistants par rapport / F_RENSSAL'
--SELECT SA_MATRICULE FROM F_HST_SECU WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_HST_SECU 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_HST_SECU WHERE SA_MATRICULE IS NULL OR SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_HST_SECU 
WHERE    
	SA_MATRICULE IS NULL OR     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Correction dans F_RENSSAL des etablissements agglomerants par rapport / F_ETA '
--SELECT FP_FISCALES_ETABAGGLOMERANT FROM F_RENSSAL WHERE FP_FISCALES_ETABAGGLOMERANT IS NOT NULL AND FP_FISCALES_ETABAGGLOMERANT NOT IN (SELECT ET_CODE FROM F_ETA)
UPDATE F_RENSSAL
SET 
	FP_FISCALES_ETABAGGLOMERANT = NULL
WHERE 
	FP_FISCALES_ETABAGGLOMERANT IS NOT NULL AND 	
	FP_FISCALES_ETABAGGLOMERANT NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Suppression des F_DUCS1EFF inexistants par rapport / F_DUCS1'
--SELECT D1E_DUCSPrincipale FROM F_DUCS1EFF WHERE D1E_DUCSPrincipale IS NOT NULL AND D1E_DUCSPrincipale NOT IN (SELECT IdDUCS1 FROM F_DUCS1)
DELETE FROM F_DUCS1EFF
WHERE 
  D1E_DUCSPrincipale IS NOT NULL AND 
  D1E_DUCSPrincipale NOT IN (SELECT IdDUCS1 FROM F_DUCS1)

--SASOR : le 9/12/2004
Print 'Suppression des doublons dans F_SEL'
--Insertion des Code et NoOrdre presentant de doublons dans une table temporaire
SELECT SE_TypeSelect, SE_NoLien, SE_NoLigne, SE_NbCriteres, SE_Utilisateur
INTO #SELDoublon
FROM 
	F_SEL
GROUP BY SE_TypeSelect, SE_NoLien, SE_NoLigne, SE_NbCriteres, SE_Utilisateur
HAVING
	COUNT(*)>1
--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #tempSEL
FROM f_SEL c, #SELDoublon d
WHERE 
	ISNULL(c.SE_TypeSelect,'')  = ISNULL(d.SE_TypeSelect,'') AND
	ISNULL(c.SE_NoLien,'')      = ISNULL(d.SE_NoLien,'')  AND
	ISNULL(c.SE_NoLigne,'')     = ISNULL(d.SE_NoLigne,'') AND 
	ISNULL(c.SE_NbCriteres,'')  = ISNULL(d.SE_NbCriteres,'') AND 
	ISNULL(c.SE_Utilisateur,'') = ISNULL(d.SE_Utilisateur,'')	

--Suppression des doublons dans la table initiale
DELETE F_SEL FROM  #SELDoublon d
WHERE 
	ISNULL(F_SEL.SE_TypeSelect,'')   = ISNULL(d.SE_TypeSelect,'') AND
	ISNULL(F_SEL.SE_NoLien,'')       = ISNULL(d.SE_NoLien,'')  AND
	ISNULL(F_SEL.SE_NoLigne,'')      = ISNULL(d.SE_NoLigne,'') AND 
	ISNULL(F_SEL.SE_NbCriteres,'')   = ISNULL(d.SE_NbCriteres,'') AND 
	ISNULL(	F_SEL.SE_Utilisateur,'') = ISNULL(d.SE_Utilisateur,'')	
 
--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempSEL ADD Compteur INT identity (1,1) 
GO
--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_SEL
SELECT 
  SE_TypeSelect, SE_NoLien, SE_NoLigne, SE_NbCriteres, SE_Ventilation, SE_Ventil01No, SE_Ventil01BorneInf,
  SE_Ventil01BorneSup, SE_Ventil01Rupture, SE_Ventil01Total, SE_Ventil02No, SE_Ventil02BorneInf, SE_Ventil02BorneSup,
  SE_Ventil02Rupture, SE_Ventil02Total, SE_Ventil03No, SE_Ventil03BorneInf, SE_Ventil03BorneSup, SE_Ventil03Rupture,
  SE_Ventil03Total, SE_Ventil04No, SE_Ventil04BorneInf, SE_Ventil04BorneSup, SE_Ventil04Rupture, SE_Ventil04Total,
  SE_Ventil05No, SE_Ventil05BorneInf, SE_Ventil05BorneSup, SE_Ventil05Rupture, SE_Ventil05Total, SE_Ventil06No, 
  SE_Ventil06BorneInf, SE_Ventil06BorneSup, SE_Ventil06Rupture, SE_Ventil06Total, SE_Ventil07No, SE_Ventil07BorneInf, 
  SE_Ventil07BorneSup, SE_Ventil07Rupture, SE_Ventil07Total, SE_Ventil08No, SE_Ventil08BorneInf, SE_Ventil08BorneSup, 
  SE_Ventil08Rupture, SE_Ventil08Total, SE_Ventil09No, SE_Ventil09BorneInf, SE_Ventil09BorneSup, SE_Ventil09Rupture,
  SE_Ventil09Total, SE_Ventil10No, SE_Ventil10BorneInf, SE_Ventil10BorneSup, SE_Ventil10Rupture, SE_Ventil10Total,
  SE_Utilisateur, SE_Axes, IdOdbc 
FROM #tempSEL t 
WHERE Compteur = (Select min(t1.Compteur) from #tempSEL t1 WHERE 
	ISNULL(t1.SE_TypeSelect,'')  = ISNULL(t.SE_TypeSelect,'') AND
	ISNULL(t1.SE_NoLien,'')      = ISNULL(t.SE_NoLien,'')  AND
	ISNULL(t1.SE_NoLigne,'')     = ISNULL(t.SE_NoLigne,'') AND 
	ISNULL(t1.SE_NbCriteres,'')  = ISNULL(t.SE_NbCriteres,'') AND 
	ISNULL(t1.SE_Utilisateur,'') = ISNULL(t.SE_Utilisateur,'')	
Group by 
	t1.SE_TypeSelect,t1.SE_NoLien,t1.SE_NoLigne,t1.SE_NbCriteres,t1.SE_Utilisateur)	


DROP TABLE #tempSEL
DROP TABLE #SELDoublon

-- SASOR le 23/12/04 
Print 'Suppression des doublons dans F_RUB'
--Insertion des Code et NoOrdre presentant de doublons dans une table temporaire
SELECT RU_RUBRIQUE, RU_TYPE
INTO #rubDoublon
FROm F_RUB
GROUP BY RU_RUBRIQUE, RU_TYPE
HAVING COUNT(*)>1

--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT r.*
INTO #temprub
FROM F_RUB r, #rubDoublon d
WHERE
	r.RU_RUBRIQUE = d.RU_RUBRIQUE AND
	r.RU_TYPE = d.RU_TYPE

--Suppression des doublons dans la table initiale
DELETE F_RUB FROM  #rubDoublon d
WHERE
	F_RUB.RU_RUBRIQUE = d.RU_RUBRIQUE AND
	F_RUB.RU_TYPE = d.RU_TYPE

--Numerotation des doublons pour pouvoir en recuperer un seul
ALTER TABLE #temprub ADD Compteur INT identity (1,1)
GO
--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_RUB
SELECT
   RU_Rubrique, RU_type,RU_Formule, RU_Elements_indice01, RU_Elements_Nombre,
   RU_Elements_indice02, RU_Elements_BaseDeCalcul, RU_Elements_indice03, RU_Elements_TauxSalarial,
   RU_Elements_indice04, RU_Elements_MontantSal,RU_Elements_indice05,RU_Elements_TauxPat,RU_Elements_indice06,
   RU_Elements_MontantPat, RU_plafond, RU_plancher, RU_BaseCotisation, RU_PlafondTB, RU_PlafondTC,
   RU_activite, RU_imprimable, RU_gain, RU_BaseCSG, RU_AAjouter, RU_ASoustraire, RU_DASRisque, RU_Cloture,
   RU_DASEnPlus, RU_BulletinsInterdits, RU_DASEnMoins, RU_PartSalariale, RU_PartPatronal, RU_DasEnPlus2,
   RU_das_avnat, RU_das_cas, RU_DasTaux, RU_DasEnMoins2, RU_Caisse, RU_Intitule,RU_CodeMemo, RU_CompteRemuneration,
   RU_CompteCharge, RU_CompteCaisse, ZR_RU_FlagInfosGenerales, RU_Divers_EnPlus, RU_Divers_EnMoins,
   RU_RubriqueARegul, RU_CumulRegul_Ajouter, RU_CumulRegul_Soustraire, RU_Crc_EnPlus, RU_Crc_EnMoins,
   RU_CumulAbsence, RU_RapportsTBTC, RU_CodeSAGE, RU_CodeDUCS, RU_GroupeInterneDUCS, RU_DateEffet,
   RU_CodeProvenance, RU_DateModification, RU_MontantDEquivalence, RU_ExclueGarantieNetAPayer, RU_Divers_EnPlus2,
   RU_Divers_EnMoins2, RU_AjouterAutres, RU_SoustraireAutres, RU_AATPeriodicite, RU_AjouterDADSU, RU_SoustraireDADSU,
   RU_AjouterDADSUGHR, RU_SoustraireDADSUGHR, RU_TypeBaseBruteSpecifique, RU_TypeSommesIsolees, RU_TypeExoneration,
   RU_TypeBaseBrutePlafonnee, RU_ContratPrevoyance,RU_NoDOrdre,RU_CodeCommunePourCodeDucs,RU_TauxNominalPourCodeDucs,
   RU_NatureDeBaseDeCotisation,RU_TypeDEpargneSalariale,RU_TypeAutresSommesExonerees,RU_TypeDePrimeVersee,
   RU_PeriodiciteDeLaPrime,RU_PrimeVerseeATermeEchu,RU_TypeDIndemnitesVerseesM,RU_TYPEDEREMUNERATIONHS,
   RU_TYPEDERAPPELDEPAIE, RU_TypeFinanceAvantagesPart, RU_DateDeMiseAJour, RU_CodeDeMiseAJour,
   RU_MotifDeMiseAJour, RU_DateDeDebutDApplication, RU_DateDeFinDApplication,
   RU_DescriptifDeMiseAJour, RU_SpecificitesPopulation, RU_SpecificitesCommentaire,
   RU_TypeExonerationRetraite, RU_TypeFraisProfessionnels, RU_EntiteDAffectation, RU_NonDeclareDSN, RU_DSNAccesSpecificitesMSA,
   RU_DSNMSAPaiementATermeEchu, RU_DSNMSAAccesCodeCommune, RU_DSNMSAPeriodiciteDePaiement, RU_DSNMSACodeCommune, IdOdbc
FROM #temprub t
WHERE Compteur = (Select min(t1.Compteur) from #temprub t1 WHERE  t1.RU_RUBRIQUE = t.RU_RUBRIQUE  AND
                                                                  t1.RU_TYPE = t.RU_TYPE
                  Group by t1.RU_RUBRIQUE, t1.RU_TYPE )


DROP TABLE #temprub
DROP TABLE #rubDoublon

-- SASOR le 14/12/04
Print 'Suppression dans F_ENF, des enfants associés à des salariés inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_ENF WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_ENF
WHERE
	SA_MATRICULE IS NOT NULL AND
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_ENF WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_ENF
WHERE
	SA_MATRICULE IS NOT NULL AND
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

-- SASOR le 15/12/04
Print 'Suppression dans F_DESCOMP, des compétences acquises associées à des salariés inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT CCC_CODEENTITE FROM F_DESCOMP WHERE CCC_TYPEFICHE = 0 AND CCC_TYPEENTITE = 2 AND CCC_CODEENTITE IS NOT NULL AND CCC_CODEENTITE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_DESCOMP
WHERE
	CCC_TYPEFICHE = 0 AND
	CCC_TYPEENTITE = 2 AND
	CCC_CODEENTITE IS NOT NULL AND
	CCC_CODEENTITE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT CCC_CODEENTITE FROM F_DESCOMP WHERE CCC_TYPEFICHE = 0 AND CCC_TYPEENTITE = 2 AND CCC_CODEENTITE IS NOT NULL AND CCC_CODEENTITE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_DESCOMP
WHERE
	CCC_TYPEFICHE = 0 AND
	CCC_TYPEENTITE = 2 AND
	CCC_CODEENTITE IS NOT NULL AND
	CCC_CODEENTITE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

-- SASOR le 15/12/04
Print 'Suppression dans F_DIP, des diplômes associés à des salariés inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_DIP WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_DIP
WHERE
	SA_MATRICULE IS NOT NULL AND
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)

--SELECT SA_MATRICULE FROM F_DIP WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_DIP
WHERE
	SA_MATRICULE IS NOT NULL AND
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

-- SASOR le 23/12/04
Print 'Suppression des F_DAS2 inexistants par rapport / F_ETA'
--SELECT ET_CODE FROM F_DAS2 WHERE ET_CODE Is NULL Or ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE FROM F_DAS2
WHERE 
    ET_CODE Is NULL Or 
    ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)

--SSO 11/01/2005
Print 'Suppression dans F_ETC, des établissements inexistants par rapport / F_ETA '
--SELECT ET_CODE FROM F_ETC WHERE ET_CODE IS NOT NULL AND ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE From F_ETC
WHERE
	ET_CODE IS NULL OR
	ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Suppression dans F_ETC, des caisses par établissement inexistantes par rapport / F_KES '
--SELECT EC_NoCaisse FROM F_ETC WHERE EC_NoCaisse IS NOT NULL AND EC_NoCaisse <> 0 AND EC_NoCaisse NOT IN (SELECT KE_CODE FROM F_KES)
DELETE From F_ETC
WHERE
	EC_NoCaisse IS NOT NULL AND
	EC_NoCaisse <> 0 AND
	EC_NoCaisse NOT IN (SELECT KE_CODE FROM F_KES)

Print 'Suppression dans F_DUCS1, des établissements inexistants par rapport / F_ETA '
--SELECT ET_ETABLISSEMENT FROM F_DUCS1 WHERE ET_ETABLISSEMENT IS NOT NULL AND ET_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE From F_DUCS1
WHERE
	ET_ETABLISSEMENT IS NULL OR
	ET_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Suppression dans F_DUCS1, des caisses inexistantes par rapport / F_KES '
--SELECT EC_Caisse FROM F_DUCS1 WHERE EC_Caisse IS NOT NULL AND EC_Caisse NOT IN (SELECT KE_CODE FROM F_KES)
DELETE F_DUCS1
WHERE 
	EC_Caisse IS NULL OR 	
	EC_Caisse NOT IN (SELECT KE_CODE FROM F_KES)

Print 'Suppression dans F_DUCS2, des établissements inexistants par rapport / F_ETA '
--SELECT ET_ETABLISSEMENT FROM F_DUCS2 WHERE ET_ETABLISSEMENT IS NOT NULL AND ET_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE From F_DUCS2
WHERE 
	ET_ETABLISSEMENT IS NULL OR 	
	ET_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Suppression dans F_DUCS2, des caisses inexistantes par rapport / F_KES '
--SELECT EC_Caisse FROM F_DUCS2 WHERE EC_Caisse IS NOT NULL AND EC_Caisse NOT IN (SELECT KE_CODE FROM F_KES)
DELETE F_DUCS2
WHERE 
	EC_Caisse IS NULL OR 	
	EC_Caisse NOT IN (SELECT KE_CODE FROM F_KES)

--Fin SSO 11/01/2005

--SSO 18/01/2005
Print 'Correction des F_RENSSAL par rapport / F_TAB / MOTIFDEPART '
--SELECT FP_SOCIETE_MOTIFDEPART FROM F_RENSSAL WHERE FP_SOCIETE_MOTIFDEPART IS NOT NULL AND FP_SOCIETE_MOTIFDEPART NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=1)
UPDATE F_RENSSAL 
SET FP_SOCIETE_MOTIFDEPART = NULL
WHERE    
	FP_SOCIETE_MOTIFDEPART IS NOT NULL AND     
	FP_SOCIETE_MOTIFDEPART NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=1)


Print 'Correction des F_HST_INFOSSOCIETE par rapport / F_TAB / MOTIFDEPART '
--SELECT AI_SOCIETE_MOTIFDEPART FROM F_HST_INFOSSOCIETE WHERE AI_SOCIETE_MOTIFDEPART IS NOT NULL AND AI_SOCIETE_MOTIFDEPART NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=1)
UPDATE F_HST_INFOSSOCIETE 
SET AI_SOCIETE_MOTIFDEPART = NULL
WHERE    
	AI_SOCIETE_MOTIFDEPART IS NOT NULL AND     
	AI_SOCIETE_MOTIFDEPART NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=1)
--Fin SSO 18/01/2005
--SSO 20/01/2005
Print 'Correction de F_SALIV_A par rapport / F_KES '
--SELECT FA_OrganismeRecouvrement FROM F_SALIV_A WHERE FA_OrganismeRecouvrement IS NOT NULL AND FA_OrganismeRecouvrement NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_SALIV_A
SET 
	FA_OrganismeRecouvrement = NULL 
WHERE 
	FA_OrganismeRecouvrement IS NOT NULL AND 	
	FA_OrganismeRecouvrement NOT IN (SELECT KE_CODE FROM F_KES)

--SELECT FA_ARRCO FROM F_SALIV_A WHERE FA_ARRCO IS NOT NULL AND FA_ARRCO NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_SALIV_A
SET 
	FA_ARRCO = NULL 
WHERE 
	FA_ARRCO IS NOT NULL AND 	
	FA_ARRCO NOT IN (SELECT KE_CODE FROM F_KES)

--SELECT FA_AGIRC FROM F_SALIV_A WHERE FA_AGIRC IS NOT NULL AND FA_AGIRC NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_SALIV_A
SET 
	FA_AGIRC = NULL 
WHERE 
	FA_AGIRC IS NOT NULL AND 	
	FA_AGIRC NOT IN (SELECT KE_CODE FROM F_KES)

--SELECT FA_AUTRERETRAITE FROM F_SALIV_A WHERE FA_AUTRERETRAITE IS NOT NULL AND FA_AUTRERETRAITE NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_SALIV_A
SET 
	FA_AUTRERETRAITE = NULL 
WHERE 
	FA_AUTRERETRAITE IS NOT NULL AND 	
	FA_AUTRERETRAITE NOT IN (SELECT KE_CODE FROM F_KES)


--SELECT FA_CodeCaisseSSSpeciale FROM F_SALIV_A WHERE FA_CodeCaisseSSSpeciale IS NOT NULL AND FA_CodeCaisseSSSpeciale NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_SALIV_A
SET 
	FA_CodeCaisseSSSpeciale = NULL 
WHERE 
	FA_CodeCaisseSSSpeciale IS NOT NULL AND 	
	FA_CodeCaisseSSSpeciale NOT IN (SELECT KE_CODE FROM F_KES)


--SELECT FA_CodeCaisseCongesPayes FROM F_SALIV_A WHERE FA_CodeCaisseCongesPayes IS NOT NULL AND FA_CodeCaisseCongesPayes NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_SALIV_A
SET 
	FA_CodeCaisseCongesPayes = NULL 
WHERE 
	FA_CodeCaisseCongesPayes IS NOT NULL AND 	
	FA_CodeCaisseCongesPayes NOT IN (SELECT KE_CODE FROM F_KES)

Print 'Suppression des F_DUCS2 inexistants par rapport / F_DUCS '
/*SELECT
 D2_CaissePourCodeDUCS, D2_CodeDUCS 
FROM F_DUCS2 
WHERE 	
	D2_CaissePourCodeDUCS IS NOT NULL AND 
	D2_CodeDUCS IS NOT NULL AND
	(CONVERT(VARCHAR(5),D2_CaissePourCodeDUCS)+D2_CodeDUCS) 
 		NOT IN (SELECT (CONVERT(VARCHAR(5),TA_CAISSE)+ TA_CODE) FROM F_TAB WHERE TA_TYPETABLE =6)*/

DELETE F_DUCS2
WHERE 	
	D2_CaissePourCodeDUCS IS NOT NULL AND 
	D2_CodeDUCS IS NOT NULL AND
	(CONVERT(VARCHAR(5),D2_CaissePourCodeDUCS)+D2_CodeDUCS) 
 		NOT IN (SELECT (CONVERT(VARCHAR(5),TA_CAISSE)+ TA_CODE) FROM F_TAB WHERE TA_TYPETABLE =6)
--Fin SSO 20/01/2005


-- SASOR le 11/03/2005 
Print 'Correction dans T_PARFOR_GHR_PGEN par rapport / F_GHR '
--SELECT PG_NatureHTrav FROM F_PARFOR_GHR_PGEN WHERE PG_NatureHTrav IS NOT NULL AND PG_NatureHTrav NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PGEN
SET 
	PG_NatureHTrav = NULL
WHERE 
	PG_NatureHTrav IS NOT NULL AND 	
	PG_NatureHTrav NOT IN (SELECT NE_CODE FROM F_GHR)

--SELECT PG_NaturePause FROM F_PARFOR_GHR_PGEN WHERE PG_NaturePause IS NOT NULL AND PG_NaturePause NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PGEN
SET 
	PG_NaturePause = NULL
WHERE 
	PG_NaturePause IS NOT NULL AND 	
	PG_NaturePause NOT IN (SELECT NE_CODE FROM F_GHR)

Print 'Correction de F_ALRT par rapport / F_TAB / ACTIONALERTE '
--SELECT AL_ACTION FROM F_ALRT WHERE AL_ACTION IS NOT NULL AND AL_ACTION NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =11)
UPDATE F_ALRT
SET 
	AL_ACTION = NULL 
WHERE 
	AL_ACTION IS NOT NULL AND 	
	AL_ACTION NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =11)

-- FIN SASOR le 11/03/2005 
-- SASOR le 21/03/2005 
Print 'Suppression des F_VALEURSGHRSAL inexistants par rapport / F_GHRSAL'
--SELECT EV_IdGHrSal FROM F_VALEURSGHRSAL WHERE EV_IdGHrSal IS NOT NULL AND EV_IdGHrSal NOT IN (SELECT IdGhrSal FROM F_GHRSAL)
DELETE FROM F_VALEURSGHRSAL
WHERE 
	EV_IdGHrSal IS NOT NULL AND 	
	EV_IdGHrSal NOT IN (SELECT IdGhrSal FROM F_GHRSAL)

Print 'Suppression des F_ORG /DEPARTEMENT ayant des codes non affectes'
--SELECT OR_CODE FROM F_ORG WHERE OR_CODE IS NULL AND OR_TYPE = 0
DELETE FROM F_ORG 
WHERE 
	OR_CODE IS NULL AND 
	OR_TYPE = 0

Print 'Suppression des F_ORG /SERVICE ayant des codes non affectes'
--SELECT OR_CODE FROM F_ORG WHERE OR_CODE IS NULL AND OR_TYPE = 1
DELETE FROM F_ORG 
WHERE 
	OR_CODE IS NULL AND 
	OR_TYPE = 1


Print 'Suppression des F_ORG /UNITE ayant des codes non affectes'
--SELECT OR_CODE FROM F_ORG WHERE OR_CODE IS NULL AND OR_TYPE = 2
DELETE FROM F_ORG 
WHERE 
	OR_CODE IS NULL AND 
	OR_TYPE = 2

Print 'Suppression des F_ORG /CATEGORIE ayant des codes non affectes'
--SELECT OR_CODE FROM F_ORG WHERE OR_CODE IS NULL AND OR_TYPE = 3
DELETE FROM F_ORG 
WHERE 
	OR_CODE IS NULL AND 
	OR_TYPE = 3

Print 'Suppression des competences erronees dans F_TABGCC'
--SELECT * FROM F_TABGCC WHERE 	TCC_CODE like '[_]%' AND TCC_TypeCompetence = 0
DELETE FROM F_TABGCC 
WHERE 
	TCC_CODE like '[_]%' AND
 	TCC_TypeCompetence = 0

-- FIN SASOR le 21/03/2005 
-- SASOR le 22/04/2005 
Print 'Suppression des F_DIP ayant des codes inexistants par rapport / F_TABGCC / GCCCOMPETENCE'
--SELECT TCC_CODECOMPETENCE FROM F_DIP WHERE TCC_CODECOMPETENCE IS NOT NULL AND TCC_CODECOMPETENCE NOT IN (SELECT TCC_CODE FROM F_TABGCC WHERE TCC_TYPE =0)
DELETE FROM F_DIP
WHERE 
	TCC_CODECOMPETENCE IS NOT NULL AND 
	TCC_CODECOMPETENCE NOT IN (SELECT TCC_CODE FROM F_TABGCC WHERE TCC_TYPE =0)

Print 'Suppression des F_DESCOMP / COMPACQUISE ayant des codes inexistants par rapport / F_TABGCC / GCCCOMPETENCE'
--SELECT CCC_CODE FROM F_DESCOMP WHERE (CCC_TYPEENTITE=2) AND (CCC_TYPEFICHE=0) AND CCC_CODE IS NOT NULL AND CCC_CODE NOT IN (SELECT TCC_CODE FROM F_TABGCC WHERE TCC_TYPE =0)
DELETE FROM F_DESCOMP
WHERE 
	(CCC_TYPEENTITE=2) AND (CCC_TYPEFICHE=0) AND 
	CCC_CODE IS NOT NULL AND 
	CCC_CODE NOT IN (SELECT TCC_CODE FROM F_TABGCC WHERE TCC_TYPE =0)
-- FIN SASOR le 22/04/2005 
-- SASOR le 26/04/2005 
Print 'Suppression des F_CUMPLANANALYTIQUESAL associés à des salariés inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_NO_SALARIE FROM F_CUMPLANANALYTIQUESAL WHERE SA_NO_SALARIE IS NOT NULL AND SA_NO_SALARIE NOT IN (SELECT SA_NO_SALARIE FROM F_SAL)
DELETE FROM F_CUMPLANANALYTIQUESAL
WHERE 
	SA_NO_SALARIE IS NOT NULL AND 
	SA_NO_SALARIE NOT IN (SELECT SA_NO_SALARIE FROM F_SAL)

--SELECT SA_NO_SALARIE FROM F_CUMPLANANALYTIQUESAL WHERE SA_NO_SALARIE IS NOT NULL AND SA_NO_SALARIE NOT IN (SELECT SA_NO_SALARIE FROM F_RENSSAL)
DELETE FROM F_CUMPLANANALYTIQUESAL
WHERE 
	SA_NO_SALARIE IS NOT NULL AND 
	SA_NO_SALARIE NOT IN (SELECT SA_NO_SALARIE FROM F_RENSSAL)

Print 'Suppression des F_HST_CHANGEMENT inexistants par rapport / F_TAB / CODEMOTIFDADSU '
--SELECT ACH_CodeMotifDADSU FROM F_HST_CHANGEMENT WHERE ACH_CodeMotifDADSU IS NOT NULL AND ACH_CodeMotifDADSU NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 26)
DELETE 
FROM F_HST_CHANGEMENT 
WHERE 
	ACH_CodeMotifDADSU IS NOT NULL AND 
	ACH_CodeMotifDADSU NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 26)

--Print 'Correction de F_CRC par rapport / F_TAB / TYPEDENTREE '
--SELECT RS_TYPEDENTREE FROM F_CRC WHERE RS_TYPEDENTREE IS NOT NULL AND RS_TYPEDENTREE NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 3)
--UPDATE F_CRC
--SET 
--	RS_TYPEDENTREE = NULL 
--WHERE 
--	RS_TYPEDENTREE IS NOT NULL AND 	
--	RS_TYPEDENTREE NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =3)
-- Traitement mis en commentaire le 09/11/2018, F_CRC n'est plus utilisé et toutes ses lignes sont supprimées plus bas (19/09/2014)

-- FIN SASOR le 26/04/2005 

-- SASOR le 10/05/2005 
Print 'Suppression des F_LANGUE_SALARIE associés à des salariés inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_LANGUE_SALARIE WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_LANGUE_SALARIE
WHERE 
	SA_MATRICULE IS NOT NULL AND 	
        SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)


--SELECT SA_MATRICULE FROM F_LANGUE_SALARIE WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_LANGUE_SALARIE
WHERE 
	SA_MATRICULE IS NOT NULL AND 	
        SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

Print 'Correction de F_GHRSAL par rapport / F_TAB / MOTIFDABSENCE '
--SELECT TA_MOTIF FROM F_GHRSAL WHERE TA_MOTIF IS NOT NULL AND TA_MOTIF NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =0)
UPDATE F_GHRSAL
SET 
	TA_MOTIF = NULL 
WHERE 
  TA_MOTIF IS NOT NULL AND 
  TA_MOTIF NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE =0)

Print 'Correction de F_GHR par rapport / F_PARFOR_GHR_TST '
--SELECT NE_TYPENOMBRE FROM F_GHR WHERE NE_TYPENOMBRE IS NOT NULL AND NE_TYPENOMBRE NOT IN (SELECT TT_INDICEFICHE FROM F_PARFOR_GHR_TST)
UPDATE F_GHR
SET 
	NE_TYPENOMBRE = NULL 
WHERE 
  NE_TYPENOMBRE IS NOT NULL AND 
  NE_TYPENOMBRE NOT IN (SELECT TT_INDICEFICHE FROM F_PARFOR_GHR_TST)

Print 'Correction de F_FORMATION par rapport / F_PARFOR_PER '
--SELECT FO_PERIODEDECALCUL FROM F_FORMATION WHERE FO_PERIODEDECALCUL IS NOT NULL AND FO_PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)
UPDATE F_FORMATION
SET 
	FO_01PERIODEDECALCUL = NULL 
WHERE 
  FO_01PERIODEDECALCUL IS NOT NULL AND 
  FO_01PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)

UPDATE F_FORMATION
SET 
	FO_02PERIODEDECALCUL = NULL 
WHERE 
  FO_02PERIODEDECALCUL IS NOT NULL AND 
  FO_02PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)

UPDATE F_FORMATION
SET 
	FO_03PERIODEDECALCUL = NULL 
WHERE 
  FO_03PERIODEDECALCUL IS NOT NULL AND 
  FO_03PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)

UPDATE F_FORMATION
SET 
	FO_04PERIODEDECALCUL = NULL 
WHERE 
  FO_04PERIODEDECALCUL IS NOT NULL AND 
  FO_04PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)

UPDATE F_FORMATION
SET 
	FO_05PERIODEDECALCUL = NULL 
WHERE 
  FO_05PERIODEDECALCUL IS NOT NULL AND 
  FO_05PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)

Print 'Correction de F_HST_BTP par rapport / F_ETA '
--SELECT ABT_ETABLISSEMENT FROM F_HST_BTP WHERE ABT_ETABLISSEMENT IS NOT NULL AND ABT_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)
UPDATE F_HST_BTP
SET 
	ABT_ETABLISSEMENT = NULL 
WHERE 
  ABT_ETABLISSEMENT IS NOT NULL AND 
  ABT_ETABLISSEMENT NOT IN (SELECT ET_CODE FROM F_ETA)

Print 'Suppression des F_HST_ETABLISSEMENT inexistants par rapport / F_ETA'
--SELECT AE_ETAB_CODE FROM F_HST_ETABLISSEMENT WHERE AE_ETAB_CODE IS NOT NULL AND AE_ETAB_CODE NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE FROM F_HST_ETABLISSEMENT
WHERE 
  AE_ETAB_CODE IS NOT NULL AND 
  AE_ETAB_CODE NOT IN (SELECT ET_CODE FROM F_ETA)

-- FIN SASOR le 10/05/2005 

-- OLBD le 18/05/2005 
Print 'Suppression des F_PER associés à des permis inexistants par rapport / F_TABGCC / GCCCOMPETENCE'
--Select * from F_PER Where TCC_CodeCompetence not in (Select TCC_Code from F_TABGCC WHERE TCC_TYPE=0)
DELETE FROM F_PER 
WHERE
	TCC_CodeCompetence IS NOT NULL AND
	TCC_CodeCompetence not in (Select TCC_Code from F_TABGCC WHERE TCC_TYPE=0)
-- FIN OLBD le 18/05/2005 

-- SASOR le 26/05/2005 
Print 'Correction de F_PARTICCONVENTIONS par rapport / F_PARFOR_PG / F_PARFOR_PER '
DECLARE @IREF tinyint
select @IREF=PF_PeriodeRef FROM F_PARFOR_PG
Print 'Affectation de la Periode de reference : ' + STR(@IREF)

UPDATE F_PARTICCONVENTIONS
SET 
  PTC_PERIODEDECALCUL=@IREF
WHERE 
	PTC_PERIODEDECALCUL IS NOT NULL AND 	
	PTC_PERIODEDECALCUL <> 0 AND 	
	PTC_PERIODEDECALCUL not in (select FT_IndiceFiche From F_PARFOR_PER where PTC_PERIODEDECALCUL=FT_IndiceFiche)


Print 'Correction de F_PARTICCONVENTIONS par rapport / F_PARFOR_PER '
--SELECT FO_PERIODEDECALCUL FROM F_FORMATION WHERE FO_PERIODEDECALCUL IS NOT NULL AND FO_PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)
UPDATE F_PARTICCONVENTIONS
SET 
	PTC_PERIODEDECALCUL = NULL 
WHERE 
  PTC_PERIODEDECALCUL IS NOT NULL AND 
  PTC_PERIODEDECALCUL NOT IN (SELECT FT_INDICEFICHE FROM F_PARFOR_PER)

-- FIN SASOR le 26/05/2005 

-- SASOR le 09/06/2005
Print 'Correction de F_PREVOYANCE par rapport / F_KES '
--SELECT CP_CAISSE FROM F_PREVOYANCE WHERE CP_CAISSE IS NOT NULL AND CP_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)
UPDATE F_PREVOYANCE
SET 
	CP_CAISSE = NULL
WHERE 
	CP_CAISSE IS NOT NULL AND 
	CP_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)  
-- FIN SASOR le 09/06/2005


-- SASOR le 19/07/2005
Print 'Suppression des F_COTSAL inexistants par rapport / F_SAL et F_RENSSAL'
--IF EXISTS (SELECT SA_No_Salarie FROM F_COTSAL WHERE (SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)))
DELETE FROM F_COTSAL
WHERE (SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR
       SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL))


Print 'Suppression des F_CUMSAL inexistants par rapport / F_SAL et F_RENSSAL'
--IF EXISTS (SELECT SA_No_Salarie FROM F_CUMSAL WHERE (SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)))
DELETE FROM F_CUMSAL
WHERE (SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR
       SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL))


Print 'Suppression des F_CUM2SAL inexistants par rapport / F_SAL et F_RENSSAL'
--IF EXISTS (SELECT SA_NumeroSal FROM F_CUM2SAL WHERE (SA_NumeroSal NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR SA_NumeroSal NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)))
DELETE FROM F_CUM2SAL
WHERE (SA_NumeroSal NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR
       SA_NumeroSal NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL))

Print 'Suppression des F_CUMCOMPTEURSAL inexistants par rapport / F_SAL et F_RENSSAL'
--IF EXISTS (SELECT SA_No_Salarie FROM F_CUMCOMPTEURSAL WHERE (SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL)))
DELETE FROM F_CUMCOMPTEURSAL
WHERE (SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_SAL)OR
       SA_No_Salarie NOT IN (SELECT SA_No_Salarie FROM F_RENSSAL))

-- FIN SASOR le 19/07/2005

-- SASOR le 03/01/2006
Print 'Suppression des F_PREVOYANCESALARIE associées à des prévoyances inexistantes par rapport / F_PREVOYANCE'
--SELECT CP_CODE FROM F_PREVOYANCESALARIE WHERE CP_CODE IS NOT NULL AND CP_CODE NOT IN (SELECT CP_CODE FROM F_PREVOYANCE)
DELETE FROM F_PREVOYANCESALARIE 
WHERE 
	CP_CODE IS NOT NULL AND
	CP_CODE NOT IN (SELECT CP_CODE FROM F_PREVOYANCE)

Print 'Suppression des F_PREVOYANCESALARIE inexistants par rapport / F_SAL et F_RENSSAL'
--SELECT SA_MATRICULE FROM F_PREVOYANCESALARIE WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_PREVOYANCESALARIE
WHERE    
 SA_MATRICULE IS NOT NULL AND     
 SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
 
--SELECT SA_MATRICULE FROM F_PREVOYANCESALARIE WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_PREVOYANCESALARIE
WHERE    
 SA_MATRICULE IS NOT NULL AND     
 SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)

-- FIN SASOR le 03/01/2006

-- SASOR le 14/02/2006
Print 'Suppression des F_CST_RUBRIQUE ayant des rubriques inexistantes par rapport / F_RUB'
--SELECT CRB_CodeRubrique FROM F_CST_RUBRIQUE WHERE CRB_CodeRubrique IS NOT NULL AND CRB_CodeRubrique NOT IN(SELECT RU_RUBRIQUE FROM F_RUB)

/*DELETE FROM  F_CST_RUBRIQUE
WHERE  
	CRB_CodeRubrique IS NOT NULL AND 	
	CRB_CodeRubrique NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)*/

-- OLBD le 22/10/2012
DELETE FROM  F_CST_RUBRIQUE
WHERE  
                CRB_CodeRubrique IS NOT NULL AND      
                CRB_CodeRubrique <> 0 AND
                CRB_CodeRubrique NOT IN (SELECT RU_RUBRIQUE FROM F_RUB)
-- FIN OLBD le 22/10/2012	
-- FIN SASOR le 14/02/2006

-- SASOR le 15/02/2006
Print 'Suppression des F_GHRSAL inexistants par rapport / F_GHR'
--SELECT NE_CodeNature FROM F_GHRSAL WHERE NE_CodeNature IS NOT NULL AND NE_CodeNature NOT IN (SELECT NE_Code FROM F_GHR)
DELETE FROM F_GHRSAL
WHERE 
  NE_CodeNature IS NOT NULL AND 
  NE_CodeNature NOT IN (SELECT NE_Code FROM F_GHR )

Print 'Suppression des F_VALEURSGHRSAL inexistants par rapport / F_GHR'
--SELECT VG_Nature FROM F_VALEURSGHRSAL WHERE VG_Nature IS NOT NULL AND VG_Nature NOT IN (SELECT NE_Code FROM F_GHR)
DELETE FROM F_VALEURSGHRSAL
WHERE 
	VG_Nature IS NOT NULL AND 	
	VG_Nature NOT IN (SELECT NE_Code FROM F_GHR)

-- FIN SASOR le 15/02/2006


-- OLBD le 10/05/2006 > Modif 26/03/2013 >suppression fiches dont Matricule is NULL
Print 'Suppression des F_PREVOYANCESALARIE dont le salarié est inexistant par rapport / F_SAL'
--Select * from F_PREVOYANCESALARIE Where SA_Matricule IS NULL OR SA_Matricule not in (Select SA_Matricule From F_SAL)
DELETE FROM F_PREVOYANCESALARIE 
WHERE
	SA_Matricule is NULL OR
	SA_Matricule not in (Select SA_Matricule From F_SAL)

Print 'Suppression des F_PREVOYANCESALARIE dont le code prévoyance est nul'
--Select * from F_PREVOYANCESALARIE Where CP_Code is NULL
DELETE FROM F_PREVOYANCESALARIE 
WHERE           
	CP_Code is NULL	
-- FIN OLBD le 10/05/2006


-- SASOR le 09/11/2006

Print 'Suppression des F_BMOD ayant un code à 0'
--SELECT * FROM F_BMOD WHERE BM_CODE = 0
DELETE FROM F_BMOD
WHERE 
	BM_CODE = 0
Print 'Suppression des F_GROUPEBMOD ayant un code à 0'
--SELECT * FROM F_GROUPEBMOD WHERE BM_BULLETINMODELE = 0
DELETE FROM F_GROUPEBMOD
WHERE 
	BM_BULLETINMODELE = 0

-- FIN SASOR le 09/11/2006

-- OLBD le 08/02/07
Print 'Suppression des F_AMORTIS inexistants par rapport à F_PRET'
--SELECT * FROM F_AMORTIS WHERE PR_NoPret NOT IN (SELECT PR_NoPret FROM F_PRET)
DELETE FROM F_AMORTIS
WHERE 
	PR_NoPret NOT IN (SELECT PR_NoPret FROM F_PRET)
-- FIN OLBD le 08/02/07
--SASOR le 25/05/2007
Print 'Correction de F_PARFOR_GHR_PHS par rapport / F_GHR '
--SELECT GH_SEUILS_01NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_01NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_01NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_01NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_01NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_01NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_02NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_02NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_02NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_02NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_02NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_02NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_03NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_03NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_03NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_03NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_03NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_03NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_04NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_04NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_04NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_04NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_04NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_04NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_05NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_05NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_05NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_05NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_05NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_05NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_06NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_06NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_06NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_06NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_06NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_06NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_07NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_07NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_07NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_07NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_07NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_07NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_08NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_08NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_08NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_08NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_08NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_08NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_09NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_09NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_09NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_09NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_09NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_09NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_10NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_10NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_10NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_10NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_10NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_10NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_11NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_11NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_11NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_11NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_11NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_11NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--SELECT GH_SEUILS_12NATUREEVENEMENT FROM F_PARFOR_GHR_PHS WHERE GH_SEUILS_12NATUREEVENEMENT IS NOT NULL AND GH_SEUILS_12NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
UPDATE F_PARFOR_GHR_PHS
SET GH_SEUILS_12NATUREEVENEMENT = NULL
WHERE 
  GH_SEUILS_12NATUREEVENEMENT IS NOT NULL AND 
  GH_SEUILS_12NATUREEVENEMENT NOT IN (SELECT NE_CODE FROM F_GHR)
--FIN SASOR le 25/05/2007

--SASOR le 31/05/2007
Print 'Correction de F_RENSSAL par rapport / F_TAB / CONDTRAVAILIRCANTEC  '
--SELECT FP_ZIRCANTEC_Per_01CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_01CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_01CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_01CTravail = NULL 
WHERE
	FP_ZIRCANTEC_Per_01CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_01CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_02CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_02CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_02CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_02CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_02CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_02CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_03CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_03CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_03CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_03CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_03CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_03CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_04CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_04CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_04CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_04CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_04CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_04CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_05CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_05CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_05CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_05CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_05CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_05CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_06CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_06CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_06CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_06CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_06CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_06CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_07CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_07CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_07CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_07CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_07CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_07CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_08CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_08CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_08CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_08CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_08CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_08CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_09CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_09CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_09CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_09CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_09CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_09CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--SELECT FP_ZIRCANTEC_Per_10CTravail FROM F_RENSSAL WHERE FP_ZIRCANTEC_Per_10CTravail IS NOT NULL AND FP_ZIRCANTEC_Per_05CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
UPDATE F_RENSSAL
SET 
	FP_ZIRCANTEC_Per_10CTravail = NULL 
WHERE 
	FP_ZIRCANTEC_Per_10CTravail IS NOT NULL 
	AND FP_ZIRCANTEC_Per_10CTravail NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=7)
--FIN SASOR le 31/05/2007

-- SASOR le 11/06/2007
  Print 'Correction de F_RUB par rapport / F_TAB / CODEDUCS  '
  -- SELECT RU_CodeDUCS FROM F_RUB WHERE RU_CodeDUCS IS NOT NULL AND RU_Caisse NOT IN ( SELECT TA_CAISSE FROM F_TAB WHERE TA_TYPETABLE=6 )
    UPDATE  F_RUB    
    SET
         RU_CodeDUCS = NULL         
    WHERE   
    RU_CodeDUCS IS NOT NULL AND
    RU_Caisse NOT IN ( SELECT TA_CAISSE FROM F_TAB WHERE TA_TYPETABLE=6 )
--FIN SASOR le 11/06/2007

-- SASOR le 12/06/2007
  Print 'Correction de F_SALIV_E par rapport / F_TAB / CODECERFA  '
  --SELECT EM_DUE_NATUREEMPLOIQUALIF FROM F_SALIV_E WHERE EM_DUE_NATUREEMPLOIQUALIF IS NOT NULL AND EM_DUE_NATUREEMPLOIQUALIF NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=12)
  UPDATE F_SALIV_E 
  SET
     EM_DUE_NATUREEMPLOIQUALIF = NULL
  WHERE 
     EM_DUE_NATUREEMPLOIQUALIF IS NOT NULL AND 
     EM_DUE_NATUREEMPLOIQUALIF NOT IN (SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE=12) 
--FIN SASOR le 12/06/2007

-- SASOR le 11/07/2007
  Print 'Suppression des F_GROUPEBMOD inexistants par rapport / F_GROUPE'
  --SELECT GRP_Code FROM F_GROUPEBMOD WHERE GRP_Code IS NOT NULL AND GRP_Code NOT IN (SELECT GRP_CODE FROM F_GROUPE )
  DELETE FROM F_GROUPEBMOD
  WHERE 
    GRP_Code IS NOT NULL AND 
    GRP_Code NOT IN (SELECT GRP_CODE FROM F_GROUPE )
--FIN SASOR le 11/07/2007

-- SASOR le 30/08/2007
Print 'Suppression des F_RATTACHEMENTCAISSE inexistants par rapport / F_KES'
--SELECT KE_CAISSE FROM F_RATTACHEMENTCAISSE WHERE KE_CAISSE IS NOT NULL AND KE_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)
DELETE FROM F_RATTACHEMENTCAISSE 
WHERE 
	KE_CAISSE IS NOT NULL AND 
	KE_CAISSE NOT IN (SELECT KE_CODE FROM F_KES)

Print 'Correction de F_RENSSAL par rapport / F_RATTACHEMENTCAISSE '
--SELECT FP_IdRattachementCaisse01 FROM F_RENSSAL WHERE FP_IdRattachementCaisse01 IS NOT NULL AND FP_IdRattachementCaisse01 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_RENSSAL
SET 
	FP_IdRattachementCaisse01 = NULL 
WHERE 
	FP_IdRattachementCaisse01 IS NOT NULL AND 	
	FP_IdRattachementCaisse01 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

--SELECT FP_IdRattachementCaisse02 FROM F_RENSSAL WHERE FP_IdRattachementCaisse02 IS NOT NULL AND FP_IdRattachementCaisse02 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_RENSSAL
SET 
	FP_IdRattachementCaisse02 = NULL 
WHERE 
	FP_IdRattachementCaisse02 IS NOT NULL AND 	
	FP_IdRattachementCaisse02 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
--SELECT FP_IdRattachementCaisse03 FROM F_RENSSAL WHERE FP_IdRattachementCaisse03 IS NOT NULL AND FP_IdRattachementCaisse03 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_RENSSAL
SET
	FP_IdRattachementCaisse03 = NULL 
WHERE 
	FP_IdRattachementCaisse03 IS NOT NULL AND 	
	FP_IdRattachementCaisse03 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

--SELECT FP_IdRattachementCaisse04 FROM F_RENSSAL WHERE FP_IdRattachementCaisse04 IS NOT NULL AND FP_IdRattachementCaisse04 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_RENSSAL
SET 
	FP_IdRattachementCaisse04 = NULL 
WHERE 
	FP_IdRattachementCaisse04 IS NOT NULL AND 	
	FP_IdRattachementCaisse04 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

--SELECT FP_IdRattachementCaisse05 FROM F_RENSSAL WHERE FP_IdRattachementCaisse05 IS NOT NULL AND FP_IdRattachementCaisse05 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_RENSSAL
SET 
	FP_IdRattachementCaisse05 = NULL 
WHERE 
	FP_IdRattachementCaisse05 IS NOT NULL AND 	
	FP_IdRattachementCaisse05 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)


Print 'Correction de F_HST_DADSU par rapport / F_RATTACHEMENTCAISSE '
--SELECT ADU_IdRattachementCaisse01 FROM F_HST_DADSU WHERE ADU_IdRattachementCaisse01 IS NOT NULL AND ADU_IdRattachementCaisse01 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_HST_DADSU
SET 
	ADU_IdRattachementCaisse01 = NULL 
WHERE 
	ADU_IdRattachementCaisse01 IS NOT NULL AND 	
	ADU_IdRattachementCaisse01 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

--SELECT ADU_IdRattachementCaisse02 FROM F_HST_DADSU WHERE ADU_IdRattachementCaisse02 IS NOT NULL AND ADU_IdRattachementCaisse02 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_HST_DADSU
SET 
	ADU_IdRattachementCaisse02 = NULL 
WHERE 
	ADU_IdRattachementCaisse02 IS NOT NULL AND 	
	ADU_IdRattachementCaisse02 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
--SELECT ADU_IdRattachementCaisse03 FROM F_HST_DADSU WHERE ADU_IdRattachementCaisse03 IS NOT NULL AND ADU_IdRattachementCaisse03 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_HST_DADSU
SET 
	ADU_IdRattachementCaisse03 = NULL 
WHERE 
	ADU_IdRattachementCaisse03 IS NOT NULL AND 	
	ADU_IdRattachementCaisse03 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

--SELECT ADU_IdRattachementCaisse04 FROM F_HST_DADSU WHERE ADU_IdRattachementCaisse04 IS NOT NULL AND ADU_IdRattachementCaisse04 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_HST_DADSU
SET
	ADU_IdRattachementCaisse04 = NULL 
WHERE 
	ADU_IdRattachementCaisse04 IS NOT NULL AND 	
	ADU_IdRattachementCaisse04 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

--SELECT ADU_IdRattachementCaisse05 FROM F_HST_DADSU WHERE ADU_IdRattachementCaisse05 IS NOT NULL AND ADU_IdRattachementCaisse05 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)
UPDATE F_HST_DADSU
SET 
	ADU_IdRattachementCaisse05 = NULL 
WHERE 
	ADU_IdRattachementCaisse05 IS NOT NULL AND 	
	ADU_IdRattachementCaisse05 NOT IN (SELECT IdRattachementCaisse FROM F_RATTACHEMENTCAISSE)

Print 'Correction de F_RENSSAL par rapport / F_ETA '
--SELECT FP_Fiscales_EtabAgglomerant FROM F_RENSSAL WHERE FP_Fiscales_EtabAgglomerant IS NOT NULL AND FP_Fiscales_EtabAgglomerant NOT IN (SELECT ET_Code FROM F_ETA)
UPDATE F_RENSSAL
SET 
	FP_Fiscales_EtabAgglomerant = NULL 
WHERE 
	FP_Fiscales_EtabAgglomerant IS NOT NULL AND 	
	FP_Fiscales_EtabAgglomerant NOT IN (SELECT ET_Code FROM F_ETA)

Print 'Correction de F_HST_DADSU par rapport / F_ETA '
--SELECT ADU_EtablissementAgglomerant FROM F_HST_DADSU WHERE ADU_EtablissementAgglomerant IS NOT NULL AND ADU_EtablissementAgglomerant NOT IN (SELECT ET_Code FROM F_ETA)
UPDATE F_HST_DADSU
SET 
	ADU_EtablissementAgglomerant = NULL 
WHERE 
	ADU_EtablissementAgglomerant IS NOT NULL AND 	
	ADU_EtablissementAgglomerant NOT IN (SELECT ET_Code FROM F_ETA)

Print 'Suppression des F_MUL inexistants par rapport / F_RENSSAL'
--SELECT SA_MATRICULE FROM F_MUL WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
DELETE FROM F_MUL 
WHERE    
	SA_MATRICULE IS NOT NULL AND     
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_SAL)
--SELECT SA_MATRICULE FROM F_MUL WHERE SA_MATRICULE IS NOT NULL AND SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
DELETE FROM F_MUL 
WHERE    
	SA_MATRICULE IS NOT NULL AND
	SA_MATRICULE NOT IN (SELECT SA_MATRICULE FROM F_RENSSAL)
--FIN SASOR le 30/08/2007

--OLBD le 12/10/2007
Print 'Suppression des F_DAS2 inexistants par rapport / F_ETA'
--SELECT ET_CODE FROM F_DAS2 WHERE ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)
DELETE FROM F_DAS2
WHERE ET_CODE NOT IN (SELECT ET_CODE FROM F_ETA)
--FIN OLBD le 12/10/2007

--DACO le 07/11/2007
Print 'Suppression des F_ACUMULERGHR inexistants par rapport / F_GHR'
--SELECT ACG_Nature FROM F_ACUMULERGHR
DELETE FROM F_ACUMULERGHR
WHERE ACG_Nature NOT IN (SELECT NE_Code FROM F_GHR)
--FIN DACO le 07/11/2007

--OLBD le 24/06/2008
Print 'Suppression des F_DESCOMP inexistants par rapport / F_SAL'
--Select * From F_Descomp Where ( CCC_TYPEENTITE=2) And (CCC_TYPEFICHE=0) And CCC_CodeEntite is not NULL and CCC_CodeEntite not in (Select Sa_Matricule from F_SAL)
Delete From F_DESCOMP 
Where (CCC_TYPEENTITE=2) And (CCC_TYPEFICHE=0) 
And   (CCC_CodeEntite IS NOT NULL And CCC_CodeEntite NOT IN (Select Sa_Matricule From F_SAL))

Print 'Correction de F_POSTE'
--Select PCC_POSTE From F_POSTE Where PCC_CodePere is not NULL and PCC_CodePere not in (Select PCC_Poste from F_Poste)
UPDATE F_POSTE
Set PCC_CodePere = NULL
Where PCC_CodePere is not NULL and PCC_CodePere not in (Select PCC_Poste from F_Poste)
--FIN OLBD le 24/06/2008

--OLBD le 29/08/2008
Print 'Correction de F_SOUHAIT par rapport / F_COMPETENCESDEMANDEES'
--Select SA_Matricule, SO_DateDemande, ST_Stage, SO_CompDemandeeLiee from f_Souhait Where SO_CompDemandeeLiee <> 0 And SO_CompDemandeeLiee not in (Select CD_CompteurCompDemandee from F_CompetencesDemandees)
Update F_SOUHAIT
Set SO_CompDemandeeLiee = 0
Where SO_CompDemandeeLiee <> 0 And SO_CompDemandeeLiee not in (Select CD_CompteurCompDemandee from F_CompetencesDemandees)
--FIN OLBD le 29/08/2008

--OLBD le 02/10/2008
Print 'Suppression des F_ETAHST de code NULL'
--SELECT ETH_CODE FROM F_ETAHST WHERE ETH_CODE is NULL
DELETE FROM F_ETAHST
WHERE ETH_CODE is NULL

Print 'Suppression des F_PRET de Matricule NULL'
--SELECT SA_Matricule FROM F_PRET WHERE SA_Matricule is NULL
DELETE FROM F_PRET
WHERE SA_Matricule is NULL

Print 'Suppression des F_AMORTIS inexistants par rapport / F_PRET'
--Select PR_NoPret From F_AMORTIS Where PR_NoPret Not In (Select PR_NoPret From F_PRET)
DELETE FROM F_AMORTIS
WHERE PR_NoPret Not In (Select PR_NoPret From F_PRET)
--FIN OLBD le 02/10/2008

--OLBD le 09/10/2008
/*
-- Suite mail OLBD : bloc mis en commentaire le 22/10/2012 
Print 'Suppression des F_CST_RUBrique inexistants par rapport / F_RUB'
--Select * From F_CST_RUBrique Where CRB_CodeRubrique Not In (Select RU_Rubrique From F_RUB)
Delete From F_CST_RUBrique
Where CRB_CodeRubrique Not In (Select RU_Rubrique From F_RUB)
*/

Print 'Suppression des F_PrevoyanceSalarie inexistants par rapport / F_Prevoyance'
--Select * From F_PrevoyanceSalarie Where CRB_CodeRubrique Not In (Select RU_Rubrique From F_Prevoyance)
Delete From F_PrevoyanceSalarie
Where CP_Code is NULL Or
      CP_Code Not In (Select CP_Code From F_Prevoyance)
--FIN OLBD le 09/10/2008

--OLBD le 13/10/2008
Print 'Suppression des F_PRET inexistants par rapport / F_TABGRH'
--Select * From F_PRET Where TG_TypeDePret not in (Select TG_Table from F_TABGRH Where TG_TypeTable = 0)
Delete From F_PRET
Where TG_TypeDePret not in (Select TG_Table from F_TABGRH Where TG_TypeTable = 0)
--FIN OLBD le 13/10/2008

--OLBD le 17/10/2008
Print 'Suppression des F_HST_Contrat de Matricule NULL'
--SELECT SA_Matricule FROM F_HST_Contrat WHERE SA_Matricule is NULL
DELETE FROM F_HST_Contrat
WHERE SA_Matricule is NULL

Print 'Suppression des F_BILCUM de Matricule NULL ou inexistant'
--SELECT SA_No_Salarie From F_BILCUM Where SA_No_Salarie = 0 Or SA_No_Salarie not in (Select SA_No_Salarie from F_SAL)
DELETE FROM F_BILCUM
Where SA_No_Salarie = 0 Or SA_No_Salarie not in (Select SA_No_Salarie from F_SAL)

Print 'Suppression des F_BILCUM pointant sur un établissement NULL ou inexistant'
--SELECT ET_Etablissement From F_BILCUM Where ET_Etablissement is  NULL Or ET_Etablissement not in (Select ET_Code from F_ETA)
DELETE FROM F_BILCUM
Where ET_Etablissement is  NULL Or ET_Etablissement not in (Select ET_Code from F_ETA)

Print 'Suppression des F_BILSAL de Matricule inexistant'
--SELECT SA_No_Salarie From F_BILSAL Where SA_No_Salarie not in (Select SA_No_Salarie from F_SAL)
DELETE FROM F_BILSAL
Where SA_No_Salarie not in (Select SA_No_Salarie from F_SAL)
--FIN OLBD le 17/10/2008

--jp le  4/11/2008
Print 'Suppression des FDU_ORGANISMES41 dont le code organisme est vide'
If OBJECT_ID('FDU_ORGANISMES41') is not null
Delete From FDU_ORGANISMES41
Where OR_CodeOrganisme is null
--FIN jp le  4/11/2008

-- OLBD le 12/12/2008
Print 'Correction de F_GHRTAB par rapport / F_Parfor_GHR_PHS'
-- Select * From F_GHRTAB Where GT_TypeAffectation = 6 And GT_ParametreHS Not In (Select GH_IndiceFiche From F_Parfor_GHR_PHS Where GH_TypeFiche = 5)

Update F_GHRTAB
Set GT_ParametreHS = 0 Where
GT_TypeAffectation = 6 And
GT_ParametreHS Not In (Select GH_IndiceFiche From F_Parfor_GHR_PHS Where GH_TypeFiche = 5)
-- Fin OLBD le 12/12/2008

--OLBD 18/10/2012
Print 'Correction de F_ACUMULERGHR par rapport / F_RUB'
-- Select ACG_Rubrique from F_ACUMULERGHR where ACG_Rubrique <> 0 And ACG_Rubrique not in (select RU_Rubrique from F_RUB)
Update F_ACUMULERGHR Set ACG_Rubrique = 0 Where ACG_Rubrique <> 0
And ACG_Rubrique not in (Select RU_Rubrique From F_RUB)

Print 'Correction de F_MontantDef par rapport / F_RUB'
-- 19/10/2012
-- Select MDF_CodeRubrique from F_MONTANTDEF where (MDF_TypeDeDonnee = 1) and (MDF_CodeRubrique = 0 or MDF_CodeRubrique not in (select RU_Rubrique From F_RUB))
Delete From F_MONTANTDEF Where (MDF_TypeDeDonnee = 1) and (MDF_CodeRubrique = 0 or MDF_CodeRubrique not in (Select RU_Rubrique From F_RUB))
-- Fin OLBD 18/10/2012

-- jp le jeudi 28/05/2009
Print 'Correction de F_ParFor_GHr_PGri par rapport / F_GHr'
--select * from F_ParFor_GHr_PGri
--where (GG_Lig_TabNatures01  is not null) and (GG_Lig_TabNatures01  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures02  is not null) and (GG_Lig_TabNatures02  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures03  is not null) and (GG_Lig_TabNatures03  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures04  is not null) and (GG_Lig_TabNatures04  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures05  is not null) and (GG_Lig_TabNatures05  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures06  is not null) and (GG_Lig_TabNatures06  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures07  is not null) and (GG_Lig_TabNatures07  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures08  is not null) and (GG_Lig_TabNatures08  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures09  is not null) and (GG_Lig_TabNatures09  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures10  is not null) and (GG_Lig_TabNatures10  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures11  is not null) and (GG_Lig_TabNatures11  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures12  is not null) and (GG_Lig_TabNatures12  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures13  is not null) and (GG_Lig_TabNatures13  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures14  is not null) and (GG_Lig_TabNatures14  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures15  is not null) and (GG_Lig_TabNatures15  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures16  is not null) and (GG_Lig_TabNatures16  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures17  is not null) and (GG_Lig_TabNatures17  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures18  is not null) and (GG_Lig_TabNatures18  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures19  is not null) and (GG_Lig_TabNatures19  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures20  is not null) and (GG_Lig_TabNatures20  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures21  is not null) and (GG_Lig_TabNatures21  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures22  is not null) and (GG_Lig_TabNatures22  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures23  is not null) and (GG_Lig_TabNatures23  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures24  is not null) and (GG_Lig_TabNatures24  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures25  is not null) and (GG_Lig_TabNatures25  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures26  is not null) and (GG_Lig_TabNatures26  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures27  is not null) and (GG_Lig_TabNatures27  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures28  is not null) and (GG_Lig_TabNatures28  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures29  is not null) and (GG_Lig_TabNatures29  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures30  is not null) and (GG_Lig_TabNatures30  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures31  is not null) and (GG_Lig_TabNatures31  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures32  is not null) and (GG_Lig_TabNatures32  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures33  is not null) and (GG_Lig_TabNatures33  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures34  is not null) and (GG_Lig_TabNatures34  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures35  is not null) and (GG_Lig_TabNatures35  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures36  is not null) and (GG_Lig_TabNatures36  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures37  is not null) and (GG_Lig_TabNatures37  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures38  is not null) and (GG_Lig_TabNatures38  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures39  is not null) and (GG_Lig_TabNatures39  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures40  is not null) and (GG_Lig_TabNatures40  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures41  is not null) and (GG_Lig_TabNatures41  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures42  is not null) and (GG_Lig_TabNatures42  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures43  is not null) and (GG_Lig_TabNatures43  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures44  is not null) and (GG_Lig_TabNatures44  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures45  is not null) and (GG_Lig_TabNatures45  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures46  is not null) and (GG_Lig_TabNatures46  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures47  is not null) and (GG_Lig_TabNatures47  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures48  is not null) and (GG_Lig_TabNatures48  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures49  is not null) and (GG_Lig_TabNatures49  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures50  is not null) and (GG_Lig_TabNatures50  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures51  is not null) and (GG_Lig_TabNatures51  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures52  is not null) and (GG_Lig_TabNatures52  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures53  is not null) and (GG_Lig_TabNatures53  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures54  is not null) and (GG_Lig_TabNatures54  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures55  is not null) and (GG_Lig_TabNatures55  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures56  is not null) and (GG_Lig_TabNatures56  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures57  is not null) and (GG_Lig_TabNatures57  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures58  is not null) and (GG_Lig_TabNatures58  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures59  is not null) and (GG_Lig_TabNatures59  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures60  is not null) and (GG_Lig_TabNatures60  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures61  is not null) and (GG_Lig_TabNatures61  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures62  is not null) and (GG_Lig_TabNatures62  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures63  is not null) and (GG_Lig_TabNatures63  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures64  is not null) and (GG_Lig_TabNatures64  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures65  is not null) and (GG_Lig_TabNatures65  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures66  is not null) and (GG_Lig_TabNatures66  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures67  is not null) and (GG_Lig_TabNatures67  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures68  is not null) and (GG_Lig_TabNatures68  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures69  is not null) and (GG_Lig_TabNatures69  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures70  is not null) and (GG_Lig_TabNatures70  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures71  is not null) and (GG_Lig_TabNatures71  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures72  is not null) and (GG_Lig_TabNatures72  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures73  is not null) and (GG_Lig_TabNatures73  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures74  is not null) and (GG_Lig_TabNatures74  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures75  is not null) and (GG_Lig_TabNatures75  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures76  is not null) and (GG_Lig_TabNatures76  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures77  is not null) and (GG_Lig_TabNatures77  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures78  is not null) and (GG_Lig_TabNatures78  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures79  is not null) and (GG_Lig_TabNatures79  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures80  is not null) and (GG_Lig_TabNatures80  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures81  is not null) and (GG_Lig_TabNatures81  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures82  is not null) and (GG_Lig_TabNatures82  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures83  is not null) and (GG_Lig_TabNatures83  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures84  is not null) and (GG_Lig_TabNatures84  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures85  is not null) and (GG_Lig_TabNatures85  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures86  is not null) and (GG_Lig_TabNatures86  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures87  is not null) and (GG_Lig_TabNatures87  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures88  is not null) and (GG_Lig_TabNatures88  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures89  is not null) and (GG_Lig_TabNatures89  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures90  is not null) and (GG_Lig_TabNatures90  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures91  is not null) and (GG_Lig_TabNatures91  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures92  is not null) and (GG_Lig_TabNatures92  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures93  is not null) and (GG_Lig_TabNatures93  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures94  is not null) and (GG_Lig_TabNatures94  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures95  is not null) and (GG_Lig_TabNatures95  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures96  is not null) and (GG_Lig_TabNatures96  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures97  is not null) and (GG_Lig_TabNatures97  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures98  is not null) and (GG_Lig_TabNatures98  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures99  is not null) and (GG_Lig_TabNatures99  not in (select NE_CODE from F_GHR))
--   or (GG_Lig_TabNatures100 is not null) and (GG_Lig_TabNatures100 not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures01  = null where (GG_Lig_TabNatures01  is not null) and (GG_Lig_TabNatures01  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures02  = null where (GG_Lig_TabNatures02  is not null) and (GG_Lig_TabNatures02  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures03  = null where (GG_Lig_TabNatures03  is not null) and (GG_Lig_TabNatures03  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures04  = null where (GG_Lig_TabNatures04  is not null) and (GG_Lig_TabNatures04  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures05  = null where (GG_Lig_TabNatures05  is not null) and (GG_Lig_TabNatures05  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures06  = null where (GG_Lig_TabNatures06  is not null) and (GG_Lig_TabNatures06  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures07  = null where (GG_Lig_TabNatures07  is not null) and (GG_Lig_TabNatures07  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures08  = null where (GG_Lig_TabNatures08  is not null) and (GG_Lig_TabNatures08  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures09  = null where (GG_Lig_TabNatures09  is not null) and (GG_Lig_TabNatures09  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures10  = null where (GG_Lig_TabNatures10  is not null) and (GG_Lig_TabNatures10  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures11  = null where (GG_Lig_TabNatures11  is not null) and (GG_Lig_TabNatures11  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures12  = null where (GG_Lig_TabNatures12  is not null) and (GG_Lig_TabNatures12  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures13  = null where (GG_Lig_TabNatures13  is not null) and (GG_Lig_TabNatures13  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures14  = null where (GG_Lig_TabNatures14  is not null) and (GG_Lig_TabNatures14  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures15  = null where (GG_Lig_TabNatures15  is not null) and (GG_Lig_TabNatures15  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures16  = null where (GG_Lig_TabNatures16  is not null) and (GG_Lig_TabNatures16  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures17  = null where (GG_Lig_TabNatures17  is not null) and (GG_Lig_TabNatures17  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures18  = null where (GG_Lig_TabNatures18  is not null) and (GG_Lig_TabNatures18  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures19  = null where (GG_Lig_TabNatures19  is not null) and (GG_Lig_TabNatures19  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures20  = null where (GG_Lig_TabNatures20  is not null) and (GG_Lig_TabNatures20  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures21  = null where (GG_Lig_TabNatures21  is not null) and (GG_Lig_TabNatures21  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures22  = null where (GG_Lig_TabNatures22  is not null) and (GG_Lig_TabNatures22  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures23  = null where (GG_Lig_TabNatures23  is not null) and (GG_Lig_TabNatures23  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures24  = null where (GG_Lig_TabNatures24  is not null) and (GG_Lig_TabNatures24  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures25  = null where (GG_Lig_TabNatures25  is not null) and (GG_Lig_TabNatures25  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures26  = null where (GG_Lig_TabNatures26  is not null) and (GG_Lig_TabNatures26  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures27  = null where (GG_Lig_TabNatures27  is not null) and (GG_Lig_TabNatures27  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures28  = null where (GG_Lig_TabNatures28  is not null) and (GG_Lig_TabNatures28  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures29  = null where (GG_Lig_TabNatures29  is not null) and (GG_Lig_TabNatures29  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures30  = null where (GG_Lig_TabNatures30  is not null) and (GG_Lig_TabNatures30  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures31  = null where (GG_Lig_TabNatures31  is not null) and (GG_Lig_TabNatures31  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures32  = null where (GG_Lig_TabNatures32  is not null) and (GG_Lig_TabNatures32  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures33  = null where (GG_Lig_TabNatures33  is not null) and (GG_Lig_TabNatures33  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures34  = null where (GG_Lig_TabNatures34  is not null) and (GG_Lig_TabNatures34  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures35  = null where (GG_Lig_TabNatures35  is not null) and (GG_Lig_TabNatures35  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures36  = null where (GG_Lig_TabNatures36  is not null) and (GG_Lig_TabNatures36  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures37  = null where (GG_Lig_TabNatures37  is not null) and (GG_Lig_TabNatures37  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures38  = null where (GG_Lig_TabNatures38  is not null) and (GG_Lig_TabNatures38  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures39  = null where (GG_Lig_TabNatures39  is not null) and (GG_Lig_TabNatures39  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures40  = null where (GG_Lig_TabNatures40  is not null) and (GG_Lig_TabNatures40  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures41  = null where (GG_Lig_TabNatures41  is not null) and (GG_Lig_TabNatures41  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures42  = null where (GG_Lig_TabNatures42  is not null) and (GG_Lig_TabNatures42  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures43  = null where (GG_Lig_TabNatures43  is not null) and (GG_Lig_TabNatures43  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures44  = null where (GG_Lig_TabNatures44  is not null) and (GG_Lig_TabNatures44  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures45  = null where (GG_Lig_TabNatures45  is not null) and (GG_Lig_TabNatures45  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures46  = null where (GG_Lig_TabNatures46  is not null) and (GG_Lig_TabNatures46  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures47  = null where (GG_Lig_TabNatures47  is not null) and (GG_Lig_TabNatures47  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures48  = null where (GG_Lig_TabNatures48  is not null) and (GG_Lig_TabNatures48  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures49  = null where (GG_Lig_TabNatures49  is not null) and (GG_Lig_TabNatures49  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures50  = null where (GG_Lig_TabNatures50  is not null) and (GG_Lig_TabNatures50  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures51  = null where (GG_Lig_TabNatures51  is not null) and (GG_Lig_TabNatures51  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures52  = null where (GG_Lig_TabNatures52  is not null) and (GG_Lig_TabNatures52  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures53  = null where (GG_Lig_TabNatures53  is not null) and (GG_Lig_TabNatures53  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures54  = null where (GG_Lig_TabNatures54  is not null) and (GG_Lig_TabNatures54  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures55  = null where (GG_Lig_TabNatures55  is not null) and (GG_Lig_TabNatures55  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures56  = null where (GG_Lig_TabNatures56  is not null) and (GG_Lig_TabNatures56  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures57  = null where (GG_Lig_TabNatures57  is not null) and (GG_Lig_TabNatures57  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures58  = null where (GG_Lig_TabNatures58  is not null) and (GG_Lig_TabNatures58  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures59  = null where (GG_Lig_TabNatures59  is not null) and (GG_Lig_TabNatures59  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures60  = null where (GG_Lig_TabNatures60  is not null) and (GG_Lig_TabNatures60  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures61  = null where (GG_Lig_TabNatures61  is not null) and (GG_Lig_TabNatures61  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures62  = null where (GG_Lig_TabNatures62  is not null) and (GG_Lig_TabNatures62  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures63  = null where (GG_Lig_TabNatures63  is not null) and (GG_Lig_TabNatures63  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures64  = null where (GG_Lig_TabNatures64  is not null) and (GG_Lig_TabNatures64  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures65  = null where (GG_Lig_TabNatures65  is not null) and (GG_Lig_TabNatures65  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures66  = null where (GG_Lig_TabNatures66  is not null) and (GG_Lig_TabNatures66  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures67  = null where (GG_Lig_TabNatures67  is not null) and (GG_Lig_TabNatures67  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures68  = null where (GG_Lig_TabNatures68  is not null) and (GG_Lig_TabNatures68  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures69  = null where (GG_Lig_TabNatures69  is not null) and (GG_Lig_TabNatures69  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures70  = null where (GG_Lig_TabNatures70  is not null) and (GG_Lig_TabNatures70  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures71  = null where (GG_Lig_TabNatures71  is not null) and (GG_Lig_TabNatures71  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures72  = null where (GG_Lig_TabNatures72  is not null) and (GG_Lig_TabNatures72  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures73  = null where (GG_Lig_TabNatures73  is not null) and (GG_Lig_TabNatures73  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures74  = null where (GG_Lig_TabNatures74  is not null) and (GG_Lig_TabNatures74  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures75  = null where (GG_Lig_TabNatures75  is not null) and (GG_Lig_TabNatures75  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures76  = null where (GG_Lig_TabNatures76  is not null) and (GG_Lig_TabNatures76  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures77  = null where (GG_Lig_TabNatures77  is not null) and (GG_Lig_TabNatures77  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures78  = null where (GG_Lig_TabNatures78  is not null) and (GG_Lig_TabNatures78  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures79  = null where (GG_Lig_TabNatures79  is not null) and (GG_Lig_TabNatures79  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures80  = null where (GG_Lig_TabNatures80  is not null) and (GG_Lig_TabNatures80  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures81  = null where (GG_Lig_TabNatures81  is not null) and (GG_Lig_TabNatures81  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures82  = null where (GG_Lig_TabNatures82  is not null) and (GG_Lig_TabNatures82  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures83  = null where (GG_Lig_TabNatures83  is not null) and (GG_Lig_TabNatures83  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures84  = null where (GG_Lig_TabNatures84  is not null) and (GG_Lig_TabNatures84  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures85  = null where (GG_Lig_TabNatures85  is not null) and (GG_Lig_TabNatures85  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures86  = null where (GG_Lig_TabNatures86  is not null) and (GG_Lig_TabNatures86  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures87  = null where (GG_Lig_TabNatures87  is not null) and (GG_Lig_TabNatures87  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures88  = null where (GG_Lig_TabNatures88  is not null) and (GG_Lig_TabNatures88  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures89  = null where (GG_Lig_TabNatures89  is not null) and (GG_Lig_TabNatures89  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures90  = null where (GG_Lig_TabNatures90  is not null) and (GG_Lig_TabNatures90  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures91  = null where (GG_Lig_TabNatures91  is not null) and (GG_Lig_TabNatures91  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures92  = null where (GG_Lig_TabNatures92  is not null) and (GG_Lig_TabNatures92  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures93  = null where (GG_Lig_TabNatures93  is not null) and (GG_Lig_TabNatures93  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures94  = null where (GG_Lig_TabNatures94  is not null) and (GG_Lig_TabNatures94  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures95  = null where (GG_Lig_TabNatures95  is not null) and (GG_Lig_TabNatures95  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures96  = null where (GG_Lig_TabNatures96  is not null) and (GG_Lig_TabNatures96  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures97  = null where (GG_Lig_TabNatures97  is not null) and (GG_Lig_TabNatures97  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures98  = null where (GG_Lig_TabNatures98  is not null) and (GG_Lig_TabNatures98  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures99  = null where (GG_Lig_TabNatures99  is not null) and (GG_Lig_TabNatures99  not in (select NE_CODE from F_GHR))
Update F_ParFor_GHr_PGri set GG_Lig_TabNatures100 = null where (GG_Lig_TabNatures100 is not null) and (GG_Lig_TabNatures100 not in (select NE_CODE from F_GHR))
-- Fin jp le jevdi 28/ 5/2009

--OLBD le 14/12/2009
Print 'Suppression des F_TAB de code nul'
--SELECT * From F_TAB Where TA_Code is NULL
DELETE FROM F_TAB Where  TA_Code is NULL
--FIN OLBD le 14/12/2009

-- de OlBD courriel du mercredi 24 février 2010
-- OLBD le 24/02/2010
Print 'Correction de F_Question par rapport / F_Groupe'
-- Select QUEST_Groupe from F_QUESTION where QUEST_Groupe not in  (select GRP_Code from F_GROUPE)
Update F_QUESTION set QUEST_Groupe = null where QUEST_Groupe not in  (select GRP_Code from F_GROUPE)

Print 'Correction de F_HST_DADSU par rapport / F_TAB'
-- Select ADU_RegimedeBaseObligatoire from F_HST_DADSU where ADU_RegimedeBaseObligatoire not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_HST_DADSU set ADU_RegimedeBaseObligatoire = '200' where ADU_RegimedeBaseObligatoire not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_HST_DADSU par rapport / F_TAB'
-- Select ADU_CodeRisqueMaladie from F_HST_DADSU where ADU_CodeRisqueMaladie not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_HST_DADSU set ADU_CodeRisqueMaladie = '200' where ADU_CodeRisqueMaladie not in (Select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_HST_DADSU par rapport / F_TAB'
-- Select ADU_CodeRisqueAT from F_HST_DADSU where ADU_CodeRisqueAT not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_HST_DADSU set ADU_CodeRisqueAT = '200' where ADU_CodeRisqueAT not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_HST_DADSU par rapport / F_TAB'
-- Select ADU_CodeRisqueVPat from F_HST_DADSU where ADU_CodeRisqueVPat not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_HST_DADSU set ADU_CodeRisqueVPat = '200' where ADU_CodeRisqueVPat not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_HST_DADSU par rapport / F_TAB'
-- Select ADU_CodeRisqueVSal From F_HST_DADSU where ADU_CodeRisqueVSal not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_HST_DADSU set ADU_CodeRisqueVSal = '200' where ADU_CodeRisqueVSal not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_RensSal par rapport / F_TAB'
-- Select FP_InfosDADSU_RegimeBaseOblig from F_RensSal where FP_InfosDADSU_RegimeBaseOblig not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_RensSal set FP_InfosDADSU_RegimeBaseOblig = '200' where FP_InfosDADSU_RegimeBaseOblig not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_RensSal par rapport / F_TAB'
-- Select FP_AutresDADSU_CodeRisqueMalad from F_RensSal where FP_AutresDADSU_CodeRisqueMalad not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_RensSal set FP_AutresDADSU_CodeRisqueMalad = '200' where FP_AutresDADSU_CodeRisqueMalad not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_RensSal par rapport / F_TAB'
-- Select FP_AutresDADSU_CodeRisqueAT from F_RensSal where FP_AutresDADSU_CodeRisqueAT not in (Select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_RensSal set FP_AutresDADSU_CodeRisqueAT = '200' where FP_AutresDADSU_CodeRisqueAT not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_RensSal par rapport / F_TAB'
-- Select FP_AutresDADSU_CodeRisqueVPat from F_RensSal where FP_AutresDADSU_CodeRisqueVPat not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_RensSal set FP_AutresDADSU_CodeRisqueVPat = '200' where FP_AutresDADSU_CodeRisqueVPat not in (select TA_Code from F_TAB where TA_TypeTable = 16)

Print 'Correction de F_RensSal par rapport / F_TAB'
-- Select FP_AutresDADSU_CodeRisqueVSal from F_RensSal where FP_AutresDADSU_CodeRisqueVSal not in (select TA_Code from F_TAB where TA_TypeTable = 16)
Update F_RensSal set FP_AutresDADSU_CodeRisqueVSal = '200' where FP_AutresDADSU_CodeRisqueVSal not in (select TA_Code from F_TAB where TA_TypeTable = 16)
-- Fin OLBD le mercredi 24/02/2010

-- de OlBD courriel du mercredi 21 juillet 2010
Print 'Suppression des F_CumPartSal de SA_No_Salarie inconnu'
--Select * from F_CUMPARTSAL where SA_No_Salarie not in (select SA_CompteurNumero from F_SAL)
Delete from F_CUMPARTSAL where SA_No_Salarie not in (select SA_CompteurNumero from F_SAL)

-- de dokeo courriel du lvndi 23 août 2010
Print 'Correction de F_Rub par rapport / F_Prevoyance'
--Select * from F_RUB where RU_ContratPrevoyance is not null and RU_ContratPrevoyance not in (select CP_Code from F_PREVOYANCE)
Update F_RUB set RU_ContratPrevoyance = null where RU_ContratPrevoyance not in (select CP_Code from F_PREVOYANCE)

-- par jopai le mardi 17 juillet 2012
Print 'Correction de FDU_DNAINDEMNITESS41 : types indemnité versée nuls'
--Select * from FDU_DNAIndemnitesS41 where IN_TypeDIndemniteVersee is null
If OBJECT_ID('FDU_DNAIndemnitesS41') is not null
Update FDU_DNAIndemnitesS41 set IN_TypeDIndemniteVersee = '00' where IN_TypeDIndemniteVersee is null

--OLBD le 20/08/2012
Print 'Correction de F_ETAHST par rapport / F_ETA'
--Select * From F_ETAHST Where ETH_Code Not In (Select ET_Code From F_ETA)
Delete From F_ETAHST Where ETH_Code Not In (Select ET_Code From F_ETA)
--FIN OLBD le 20/08/2012

--OLBD 22/10/2012
Print 'Correction de F_ETC par rapport / F_KES'
-- Select EC_PDUCS_CaisseRegroupement From F_ETC Where EC_PDUCS_CaisseRegroupement <> 0 And EC_PDUCS_CaisseRegroupement not IN (Select KE_Code From F_KES)
Update F_ETC Set EC_PDUCS_CaisseRegroupement = 0 Where EC_PDUCS_CaisseRegroupement <> 0  
And EC_PDUCS_CaisseRegroupement not IN (Select KE_Code From F_KES)

Print 'Correction de F_VALEURSGHRSAL par rapport / F_SAL'
-- Select * From F_VALEURSGHRSAL Where SA_No_Salarie not in (Select SA_CompteurNumero From F_SAL)
Delete From F_VALEURSGHRSAL Where SA_No_Salarie not in (Select SA_CompteurNumero From F_SAL)
-- Fin OLBD 22/10/2012

--OLBD 15/09/2014
Print 'Correction de F_ArretDeTravail par rapport / F_SAL'
-- Select * From F_ArretDeTravail Where SA_NumeroSal not in (select SA_CompteurNumero From F_SAL)
Delete From F_ArretDeTravail Where SA_NumeroSal not in (select SA_CompteurNumero From F_SAL)

Print 'Correction de F_ArretRegul par rapport / F_ArretDeTravail'
-- Select * From F_ArretRegul Where ARG_IdArretDeTravail not in (select IdArretDeTravail From F_ArretDeTravail)
Delete From F_ArretRegul Where ARG_IdArretDeTravail not in (select IdArretDeTravail From F_ArretDeTravail)

Print 'Correction de F_ArretRembo par rapport / F_ArretDeTravail'
-- Select * From F_ArretRembo Where ARE_IdArretDeTravail not in (select IdArretDeTravail From F_ArretDeTravail)
Delete From F_ArretRembo Where ARE_IdArretDeTravail not in (select IdArretDeTravail From F_ArretDeTravail)

-- Fin OLBD 15/09/2014

--OLBD 17/09/2014
Print 'Correction de F_ETA par rapport / F_CONTACT'
-- Select ET_CodeContact From F_ETA Where ET_CodeContact IS Not NULL And ET_CodeContact Not in (Select CNT_CodeContact From F_Contact)
Update F_ETA Set ET_CodeContact = NULL Where ET_CodeContact IS Not NULL And ET_CodeContact Not in (Select CNT_CodeContact From F_Contact)

-- Fin OLBD 17/09/2014

--OLBD 19/09/2014
Print 'Correction de F_CSTBMOD par rapport / F_BMOD'
-- Select CBM_CodeBMod From F_CSTBMOD Where CBM_CodeBMod Not in (Select BM_Code From F_BMOD)
Delete From F_CSTBMOD Where CBM_CodeBMod Not in (Select BM_Code From F_BMOD)

Print 'Correction de F_CSTBMOD par rapport / F_CST'
-- Select CBM_Codeconstante From F_CSTBMOD Where CBM_Codeconstante Is NULL Or CBM_Codeconstante Not in (Select CT_Code From F_CST)
Delete From F_CSTBMOD Where CBM_Codeconstante Is NULL Or CBM_Codeconstante Not in (Select CT_Code From F_CST)

Print 'Correction de F_HST_Question par rapport / F_SAL'	
--Select F_HST_Question.SA_Matricule From F_HST_Question Where F_HST_Question.SA_Matricule is NULL Or F_HST_Question.SA_Matricule not in (Select F_SAL.SA_Matricule From F_SAL)
Delete From F_HST_Question 
Where F_HST_Question.SA_Matricule is NULL 
Or F_HST_Question.SA_Matricule not in (Select F_SAL.SA_Matricule From F_SAL)

Print 'Correction de F_HST_Question par rapport / F_Question'
--Select F_HST_Question.Quest_Code From F_HST_Question Where F_HST_Question.Quest_Code is NULL Or F_HST_Question.Quest_Code not in (Select F_Question.Quest_Code From F_Question)
Delete From F_HST_Question 
Where F_HST_Question.Quest_Code is NULL 
Or F_HST_Question.Quest_Code not in (Select F_Question.Quest_Code From F_Question)

Print 'Suppression des historiques adresse établissement F_ETAHST'
Delete From F_ETAHST

-- Fin OLBD 19/09/2014

--OLBD 17/10/2014

Print 'Suppression des comptes généraux de code non renseigné'
-- Select CG_Compte From F_CPG Where CG_Compte is NULL
Delete From F_CPG Where CG_Compte is NULL

Print 'Correction dans F_KES par rapport / F_CONTACT'
--Select KE_CodeContact From F_KES Where KE_CodeContact not In (Select CNT_CodeContact From F_Contact)
Update F_KES Set KE_CodeContact = NULL Where KE_CodeContact not In (Select CNT_CodeContact From F_Contact)

Print 'Correction dans F_SALIV_A par rapport / F_CONTACT'
-- Select FA_CodeContact From F_SALIV_A Where FA_CodeContact is not NULL And FA_CodeContact Not In (Select CNT_CodeContact From F_Contact)
Update F_SALIV_A
Set FA_CodeContact = NULL
Where FA_CodeContact is not NULL
And FA_CodeContact Not In (Select CNT_CodeContact From F_Contact)

Print 'Correction dans F_SALIV_B par rapport / F_CONTACT'
-- Select FM_AM_CodeContact From F_SALIV_B Where FM_AM_CodeContact is not NULL And FM_AM_CodeContact Not In (Select CNT_CodeContact From F_Contact)
Update F_SALIV_B
Set FM_AM_CodeContact = NULL
Where FM_AM_CodeContact is not NULL
And FM_AM_CodeContact Not In (Select CNT_CodeContact From F_Contact)

Print 'Correction dans F_SALIV_E par rapport / F_CONTACT'
-- Select EM_DUE_Contact From F_SALIV_E Where EM_DUE_Contact is not NULL And EM_DUE_Contact Not In (Select CNT_CodeContact From F_Contact)
Update F_SALIV_E
Set EM_DUE_Contact = NULL
Where EM_DUE_Contact is not NULL
And EM_DUE_Contact Not In (Select CNT_CodeContact From F_Contact)

Print 'Correction dans F_SALIV_F par rapport / F_CONTACT'
--- Select AAT_AAT_CodeContact From F_SALIV_F Where AAT_AAT_CodeContact is not NULL And AAT_AAT_CodeContact Not In (Select CNT_CodeContact From F_Contact)
Update F_SALIV_F
Set AAT_AAT_CodeContact = NULL
Where AAT_AAT_CodeContact is not NULL
And AAT_AAT_CodeContact Not In (Select CNT_CodeContact From F_Contact)

Print 'Suppression des conventions collectives de code non renseigné'
-- Select CO_Code From F_COL Where CO_Code is NULL
Delete From F_COL Where CO_Code is NULL

-- PMEPAIE-2142 - Correction sur mise à jour F_TAB pour préciser la granularité manquante sur "TA_TypeTable"/"TA_Caisse"
Print 'Suppression des codes DUCS en doublons dans F_TAB'
--Insertion des TA_Code présentant des doublons dans une table temporaire
SELECT  TA_Code, TA_TypeTable, TA_Caisse
INTO #TabDoublon
FROM F_TAB
Where TA_TypeTable = 6
GROUP BY  TA_Code, TA_TypeTable, TA_Caisse
HAVING COUNT(*) > 1	

--Sauvegarde des doublons dans table tempo de même structure que la table initiale
SELECT c.*
INTO #TempTab
FROM F_TAB c, #TabDoublon d
WHERE 
	c.TA_Code      = d.TA_Code AND
	c.TA_TypeTable = d.TA_TypeTable AND
	c.TA_Caisse    = d.TA_Caisse
	
--Suppression des doublons dans la table initiale
DELETE F_TAB FROM  #TempTab d
WHERE 
	f_TAB.TA_Code      = d.TA_Code AND
	f_TAB.TA_TypeTable = d.TA_TypeTable AND
	f_TAB.TA_Caisse    = d.TA_Caisse

--Numérotation des doublons pour pouvoir en récupérer un seul 
ALTER TABLE #TempTab ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_TAB
SELECT 
	TA_TypeTable, TA_Code, TA_Intitule, ZR_TA_FlagInfosGenerales, TA_MontantEDI, ZR_TA_Filler5, TA_TypeRegimeBase,
	TA_TypeMotifPeriodeDASU, TA_CompterDansDUE, TA_NatureDeContrat, TA_CodeCerfa2483, TA_CodeMSA, TA_CodeBilanSocial,
	TA_CodeDasCRC, TA_CodeASS, TA_CodeMouvement, TA_Caisse, TA_CodeEmploiInsee, TA_CodeISO2, TA_CodeDADSU, TA_CodeCaisseCP,
	TA_TypeOrganismeDestinataire, ZR_TA_Filler3, TA_VariableCoefficient, TA_VariableCodeTableLiee, TA_VariableZR_Filler4,
	TA_VariableCodeMotifDebutPer, TA_VariableCodeMotifFinPer, TA_VariableDepartement, TA_VariableVille, TA_VariableMemo,
	TA_CodeCommune, TA_TauxNominal, TA_CodeTableBis, TA_DeclareDSN, TA_DeclareDADSU, TA_visible, TA_CodeDSN3, TA_DispositifAP3,
	TA_DateModification, TA_Enumere, IdOdbc
FROM #TempTab t 
WHERE Compteur = (SELECT MIN(t1.Compteur) FROM #TempTab t1 WHERE
                  t1.TA_Code      = t.TA_Code AND 
                  t1.TA_TypeTable = t.TA_TypeTable AND 
				  t1.TA_Caisse    = t.TA_Caisse
				  GROUP BY t1.TA_Code, t1.TA_TypeTable, t1.TA_Caisse)
-- PMEPAIE-2142 - FIN

DROP TABLE #TempTab
DROP TABLE #TabDoublon
GO

-- PMEPAIE-2142 - Correction sur mise à jour F_ORG pour préciser la granularité manquante sur "Or_Type"
Print 'Suppression des Départements en doublons dans F_ORG'
--Insertion des OR_Code présentant des doublons dans une table temporaire
SELECT  Or_Code, Or_Type
INTO #OrgDoublon
FROM F_ORG
Where OR_Type = 0
GROUP BY  OR_Code, Or_Type
HAVING COUNT(*) > 1	

--Sauvegarde des doublons dans table tempo de même structure que la table initiale
SELECT c.*
INTO #TempOrg
FROM F_ORG c, #OrgDoublon d
WHERE 
	c.OR_Code = d.OR_Code 
	and c.Or_Type = d.Or_Type
	
--Suppression des doublons dans la table initiale
DELETE F_ORG FROM  #TempOrg d
WHERE 
	F_ORG.OR_Code = d.OR_Code 
    and F_ORG.Or_Type = d.Or_Type
	
--Numérotation des doublons pour pouvoir en récupérer un seul 
ALTER TABLE #TempOrg ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_ORG
SELECT 
	OR_Type, OR_Code, OR_Intitule, ZR_HC_FlagInfosgeneral, OR_StatutCategoriel, OR_StatutProf, OR_Nature, OR_DUCS,
	OR_FormationProfessionnelle, OR_BTP, OR_BTP_RCP, OR_CategorielConvention, OR_CodePointDistribution, OR_CodeNiveauDistribution,
	OR_UniteDeTempsDeTravail, OR_QuotiteDeTravail, IdOdbc
FROM #TempOrg t 
WHERE Compteur = (Select min(t1.Compteur) from #TempOrg t1 WHERE
                  t1.OR_Code = t.OR_Code and
                  t1.OR_Type = t.OR_Type Group by t1.OR_Code, t1.OR_Type)

DROP TABLE #TempOrg
DROP TABLE #OrgDoublon
GO

Print 'Suppression des Services en doublons dans F_ORG'
--Insertion des OR_Code présentant des doublons dans une table temporaire
SELECT  Or_Code, Or_Type
INTO #OrgDoublon
FROM F_ORG
Where OR_Type = 1
GROUP BY  OR_Code, Or_Type
HAVING COUNT(*)>1	

--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #TempOrg
FROM F_ORG c, #OrgDoublon d
WHERE 
	c.OR_Code = d.OR_Code 
	and c.Or_Type = d.Or_Type
	
--Suppression des doublons dans la table initiale
DELETE F_ORG FROM  #TempOrg d
WHERE 
	F_ORG.OR_Code = d.OR_Code 
	and F_ORG.Or_Type = d.Or_Type

--Numérotation des doublons pour pouvoir en récupérer un seul 
ALTER TABLE #TempOrg ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_ORG
SELECT 
	OR_Type, OR_Code, OR_Intitule, ZR_HC_FlagInfosgeneral, OR_StatutCategoriel, OR_StatutProf, OR_Nature, OR_DUCS,
	OR_FormationProfessionnelle, OR_BTP, OR_BTP_RCP, OR_CategorielConvention, OR_CodePointDistribution, OR_CodeNiveauDistribution,
	OR_UniteDeTempsDeTravail, OR_QuotiteDeTravail, IdOdbc
FROM #TempOrg t 
WHERE Compteur = (Select min(t1.Compteur) from #TempOrg t1 WHERE 
                  t1.OR_Code = t.OR_Code and
                  t1.OR_Type = t.OR_Type Group by t1.OR_Code, t1.OR_Type)

DROP TABLE #TempOrg
DROP TABLE #OrgDoublon
GO

Print 'Suppression des Unités en doublons dans F_ORG'
--Insertion des OR_Code présentant des doublons dans une table temporaire
SELECT  Or_Code, Or_Type
INTO #OrgDoublon
FROM F_ORG
Where OR_Type = 2
GROUP BY  OR_Code, Or_Type
HAVING COUNT(*)>1	

--Sauvegarde des doublons dans table tempo de même structure que la table initiale
SELECT c.*
INTO #TempOrg
FROM F_ORG c, #OrgDoublon d
WHERE 
	c.OR_Code = d.OR_Code 
	and c.Or_Type = d.Or_Type
	
--Suppression des doublons dans la table initiale
DELETE F_ORG FROM  #TempOrg d
WHERE 
	F_ORG.OR_Code = d.OR_Code 
	and F_ORG.Or_Type = d.Or_Type

--Numérotation des doublons pour pouvoir en récupérer un seul 
ALTER TABLE #TempOrg ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_ORG
SELECT 
	OR_Type, OR_Code, OR_Intitule, ZR_HC_FlagInfosgeneral, OR_StatutCategoriel, OR_StatutProf, OR_Nature, OR_DUCS,
	OR_FormationProfessionnelle, OR_BTP, OR_BTP_RCP, OR_CategorielConvention, OR_CodePointDistribution, OR_CodeNiveauDistribution,
	OR_UniteDeTempsDeTravail, OR_QuotiteDeTravail, IdOdbc
FROM #TempOrg t 
WHERE Compteur = (Select min(t1.Compteur) from #TempOrg t1 WHERE 
                  t1.OR_Code = t.OR_Code and
                  t1.OR_Type = t.OR_Type Group by t1.OR_Code, t1.OR_Type)

DROP TABLE #TempOrg
DROP TABLE #OrgDoublon
GO

Print 'Suppression des Catégories en doublons dans F_ORG'
--Insertion des OR_Code présentant des doublons dans une table temporaire
SELECT  Or_Code, Or_Type
INTO #OrgDoublon
FROM F_ORG
Where OR_Type = 3
GROUP BY  OR_Code, Or_Type
HAVING COUNT(*)>1	

--Sauvegarde des doublons dans table tempo de meme structure que la table initiale
SELECT c.*
INTO #TempOrg
FROM F_ORG c, #OrgDoublon d
WHERE 
	c.OR_Code = d.OR_Code 
	and c.Or_Type = d.Or_Type
	
--Suppression des doublons dans la table initiale
DELETE F_ORG FROM  #TempOrg d
WHERE 
	F_ORG.OR_Code = d.OR_Code 
	and F_ORG.Or_Type = d.Or_Type

--Numérotation des doublons pour pouvoir en récupérer un seul 
ALTER TABLE #TempOrg ADD Compteur INT identity (1,1) 
GO

--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_ORG
SELECT 
	OR_Type, OR_Code, OR_Intitule, ZR_HC_FlagInfosgeneral, OR_StatutCategoriel, OR_StatutProf, OR_Nature, OR_DUCS,
	OR_FormationProfessionnelle, OR_BTP, OR_BTP_RCP, OR_CategorielConvention, OR_CodePointDistribution, OR_CodeNiveauDistribution,
	OR_UniteDeTempsDeTravail, OR_QuotiteDeTravail, IdOdbc
FROM #TempOrg t 
WHERE Compteur = (Select min(t1.Compteur) from #TempOrg t1 WHERE
                  t1.OR_Code = t.OR_Code and
                  t1.OR_Type = t.OR_Type Group by t1.OR_Code, t1.OR_Type)

DROP TABLE #TempOrg
DROP TABLE #OrgDoublon
GO
-- PMEPAIE-2142 - FIN

Print 'Suppression des caisses de code non renseigné / F_KES'
-- Select KE_Code From F_KES Where KE_Code = 0
Delete From F_KES Where KE_Code = 0 or KE_Code = ''
-- Fin OLBD 17/10/2014

-- OLBD 03/11/2014
Print 'Suppression des historiques Périodes d''arrêt de date non renseignée / F_HST_Arret'
Delete From F_HST_Arret Where AP_DateDeDebut is NULL

Print 'Correction de F_HST_Contrat par rapport / F_TAB'
UPDATE F_HST_CONTRAT 
SET AC_Contrat_Nature = NULL WHERE AC_Contrat_Nature IS NOT NULL AND 
AC_Contrat_Nature NOT IN (SELECT DISTINCT TA_Code FROM F_TAB WHERE TA_TypeTable = 2)

-- PMEPAIE-2142 - Refactoring
Print 'Modification des F_DUCS2 de code commune incohérent/ F_TAB'
-- <OLD>
--Update F_DUCS2 Set D2_CodeCommune = 
--(Select Distinct TA_CodeCommune From F_TAB 
--Left outer join F_DUCS2 On F_DUCS2.D2_CodeDUCS = F_TAB.TA_Code Where
--F_TAB.TA_TypeTable = 6)
-- <REVISION>
UPDATE F_DUCS2 SET D2_CodeCommune = F_TAB.TA_CodeCommune 
FROM F_DUCS2 INNER JOIN F_TAB
ON (F_DUCS2.D2_CodeDUCS = F_TAB.TA_Code)
WHERE F_TAB.TA_TypeTable = 6

Print 'Modification des F_DUCS2 de Taux nominal incohérent/ F_TAB'
-- <OLD>
--Update F_DUCS2 Set D2_TauxNominal = 
--(Select Distinct  TA_TauxNominal From F_TAB 
--Left outer join F_DUCS2 On F_DUCS2.D2_CodeDUCS = F_TAB.TA_Code Where
--F_TAB.TA_TypeTable = 6)
-- <REVISION>
UPDATE F_DUCS2 SET D2_TauxNominal = F_TAB.TA_TauxNominal 
FROM F_DUCS2 INNER JOIN F_TAB
ON (F_DUCS2.D2_CodeDUCS = F_TAB.TA_Code)
WHERE F_TAB.TA_TypeTable = 6
-- PMEPAIE-2142 - Fin
-- Fin OLBD 03/11/2014

IF EXISTS (SELECT Sa_No_Salarie FROM F_RENSSAL GROUP BY Sa_No_Salarie HAVING COUNT(*)>1	)
Print 'Anomalie F_RENSSAL : Doublons. Pas de correction effectuee'

IF EXISTS (SELECT Sa_Matricule FROM F_SAL GROUP BY Sa_Matricule HAVING COUNT(*)>1)
Print 'Anomalie F_SAL : Doublons. Pas de correction effectuee'

-- SSO 25/11/03
IF EXISTS (SELECT Sa_No_Salarie FROM F_RENSSAL WHERE  SA_No_Salarie NOT IN (SELECT Sa_No_Salarie FROM F_SAL))
Print 'Anomalie F_RENSSAL : Salarie inexistant dans F_SAL. Pas de correction effectuee'

IF EXISTS (SELECT Sa_No_Salarie FROM F_SAL WHERE  SA_No_Salarie NOT IN (SELECT Sa_No_Salarie FROM F_RENSSAL))
Print 'Anomalie F_SAL : Salarie inexistant dans F_RENSSAL. Pas de correction effectuee'

-- PMEPAIE-2005 - Suppression entrée doublon "ASSIETTE_CICE" - table F_MONTANT
Print 'Suppression de l''entrée doublon "ASSIETTE_CICE" de la table F_MONTANT dans le cadre de la migration CBASE 2.13'
IF EXISTS (SELECT mt_code FROM F_MONTANT WHERE mt_code = 'ASSIETTE_CICE' GROUP BY mt_code HAVING COUNT(*) > 1)
BEGIN
  -- DELETE des items F_MONTANTDEF
  DELETE FROM F_MONTANTDEF WHERE MDF_IdMontant IN 
    (SELECT IDMONTANT FROM F_MONTANT WHERE mt_code = 'ASSIETTE_CICE' AND ((MT_CODECORRESPONDANT IS NULL) OR (MT_CODECORRESPONDANT <> 'S40.G30.40.006')));  
  -- DELETE des items F_MONTANT
  DELETE FROM F_MONTANT WHERE mt_code = 'ASSIETTE_CICE' AND ((MT_CODECORRESPONDANT IS NULL) OR (MT_CODECORRESPONDANT <> 'S40.G30.40.006'));
END;
-- PMEPAIE-2005 - Fin

-- PMEPAIE-2210 - Campagne de corrections - Patch 7.10.1
-- >> Suppression entrées F_VALEURSGHRSAL invalidées sur FK "EV_IdGhrSal" inexistante dans F_GHRSAL
Print 'Suppression entrees invalides dans la table F_VALEURSGHRSAL'
DELETE FROM F_VALEURSGHRSAL WHERE EV_IdGhrSal NOT IN (SELECT IdGhrSal FROM F_GHRSAL)

-- >> Suppression entrées F_CST_RUBRIQUE invalidées sur FK "CRB_CodeRubrique" inexistante dans F_RUB
--Print 'Suppression entrees invalides dans la table F_CST_RUBRIQUE'
-- FROM F_CST_RUBRIQUE WHERE CRB_CodeRubrique NOT IN (SELECT RU_Rubrique FROM F_RUB)
-- Mis en commentaire le 09/11/2018. Point traité plus haut et les enreg avec code rubrique à NULL ou 0 ne sont pas à supprimer

-- >> Invalidation entrées F_KES sur FK "KE_CodeContact" inexistante dans F_CONTACT
Print 'Dissocation valeur de colonne KE_CodeContact pour entrees invalides dans la table F_KES'
UPDATE F_KES SET KE_CodeContact = NULL WHERE (KE_CodeContact IS NOT NULL) AND KE_CodeContact NOT IN (SELECT CNT_CodeContact FROM F_CONTACT)

-- >> Suppression entrées F_HST_CHANGEMENT sur FK "ACH_CodeMotifDADSU" inexistante dans F_TAB
Print 'Suppression entrees invalides dans la table F_HST_CHANGEMENT'
DELETE FROM F_HST_CHANGEMENT WHERE ACH_CodeMotifDADSU NOT IN (SELECT TA_Code
  FROM F_TAB
  -- >> Remarquer le filtrage pour les références "Motifs DADSU" 
  WHERE ta_typetable = 26)

-- >> Suppression entrées F_HST_DADSU sur FK "ADU_StatutProfessionnel" inexistante dans F_TAB/ta_typetable = 17
Print 'Suppression entrees invalides dans la table F_HST_DADSU'
DELETE FROM F_HST_DADSU WHERE ADU_StatutProfessionnel NOT IN (SELECT TA_Code
  FROM F_TAB
  -- >> Remarquer le filtrage pour les références "Statuts Professionnels" 
  WHERE ta_typetable = 17)

-- >> Invalidation entrées F_ORG (i.e table T_CATEGORIE cible) sur FK "OR_StatutProf" inexistante dans F_TAB/ta_typetable = 17
Print 'Dissocation valeur de colonne OR_StatutProf pour entrees invalides dans la table F_ORG'
UPDATE F_ORG SET OR_StatutProf = NULL WHERE
  (OR_StatutProf IS NOT NULL) AND OR_StatutProf NOT IN (SELECT TA_Code
  FROM F_TAB
  -- >> Remarquer le filtrage pour les références "Statuts Professionnels" 
  WHERE ta_typetable = 17)

-- >> Invalidation entrées F_RUB sur FK "RU_TypeExoneration" inexistante dans F_TAB/ta_typetable = 23
Print 'Dissocation valeur de colonne RU_TypeExoneration pour entrees invalides dans la table F_RUB'
UPDATE F_RUB SET RU_TypeExoneration = NULL WHERE
  (RU_TypeExoneration IS NOT NULL) AND RU_TypeExoneration NOT IN (SELECT TA_Code
  FROM F_TAB
  -- >> Remarquer le filtrage pour les références "Types Exonérations" 
  WHERE ta_typetable = 23)
-- PMEPAIE-2210 - Fin

-- PMEPAIE-2242 - Campagne de corrections - Version Paie 7.20
-- >> (1) Invalidation entrées F_ETC sur future FK "EC_PDUCS_CaisseRegroupement"
Print 'Dissocation valeur de colonne EC_PDUCS_CaisseRegroupement non référencée dans la table F_KES'
UPDATE F_ETC SET EC_PDUCS_CaisseRegroupement = NULL WHERE
  (EC_PDUCS_CaisseRegroupement IS NOT NULL) AND EC_PDUCS_CaisseRegroupement NOT IN (SELECT KE_Code FROM F_KES)

-- >> (2) Invalidation entrées F_ETC sur future FK "EC_NoCaisse"
--Print 'Suppression des F_ETC pointant sur caisse inexistante par rapport / F_KES'
--DELETE FROM F_ETC WHERE (EC_NoCaisse IS NULL) OR EC_NoCaisse NOT IN (SELECT KE_Code FROM F_KES)

-- >> (3) Invalidation entrées F_DUCS2 sur future FK "EC_Caisse"
Print 'Suppression des F_DUCS2 pointant sur caisse inexistante par rapport / F_KES'
DELETE FROM F_DUCS2 WHERE (EC_Caisse IS NULL) OR EC_Caisse NOT IN (SELECT KE_Code FROM F_KES)

-- >> (4) Invalidation entrées F_PREVOYANCE sur future FK "CP_Caisse"
Print 'Dissocation valeur de colonne CP_Caisse non référencée dans la table F_PREVOYANCE'
UPDATE F_PREVOYANCE SET CP_Caisse = NULL WHERE
  (CP_Caisse IS NOT NULL) AND CP_Caisse NOT IN (SELECT KE_Code FROM F_KES)

-- >> (5) Invalidation entrées F_HST_PREVOYANCE sur future FK "ACP_Caisse"
Print 'Dissocation valeur de colonne ACP_Caisse non référencée dans la table F_HST_PREVOYANCE'
UPDATE F_HST_PREVOYANCE SET ACP_Caisse = NULL WHERE
  (ACP_Caisse IS NOT NULL) AND ACP_Caisse NOT IN (SELECT KE_Code FROM F_KES)

-- >> (6) Invalidation entrées F_PREVOYANCESALARIE sur future FK "PS_Caisse"
Print 'Dissocation valeur de colonne PS_Caisse non référencée dans la table F_PREVOYANCESALARIE'
UPDATE F_PREVOYANCESALARIE SET PS_Caisse = NULL WHERE
  (PS_Caisse IS NOT NULL) AND PS_Caisse NOT IN (SELECT KE_Code FROM F_KES)

-- >> (7) Suppression de doublons dans F_GROUPEBMOD
Print 'Suppression de doublons dans la table F_GROUPEBMOD'
SELECT
  BM_BulletinModele,
  GRP_Code
  INTO #GroupeModDoublon
  FROM F_GROUPEBMOD
GROUP BY
  BM_BulletinModele, GRP_Code HAVING COUNT(*) > 1

-- Sauvegarde des doublons dans table tempo de même structure que la table initiale
SELECT a.*
INTO #TempGroupeMod
FROM F_GROUPEBMOD a, #GroupeModDoublon b
WHERE a.BM_BulletinModele = b.BM_BulletinModele AND
      a.GRP_Code          = b.GRP_Code

-- Suppression des doublons dans la table initiale
DELETE F_GROUPEBMOD FROM #TempGroupeMod a 
WHERE F_GROUPEBMOD.BM_BulletinModele = a.BM_BulletinModele AND
      F_GROUPEBMOD.GRP_Code          = a.GRP_Code

-- Numérotation des doublons pour pouvoir en récupérer un seul 
ALTER TABLE #TempGroupeMod ADD Compteur INT identity (1,1) 
GO

-- Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_GROUPEBMOD
SELECT BM_BulletinModele, GRP_Code, ZR_GBM_FlagInfosGenerales, IdOdbc
FROM #TempGroupeMod t
WHERE Compteur = (SELECT MIN(t1.Compteur) FROM #TempGroupeMod t1 WHERE
                  t1.BM_BulletinModele = t.BM_BulletinModele AND t1.GRP_Code = t.GRP_Code
GROUP BY t1.BM_BulletinModele, t1.GRP_Code)

DROP TABLE #TempGroupeMod
DROP TABLE #GroupeModDoublon
GO

Print 'Suppression des F_PIECESJOINTES pointant sur salarié inexistant par rapport / F_SAL'
--SELECT PJ_Numero FROM F_PIECESJOINTES WHERE PJ_Type = 1 And (PJ_Numero = 0 OR PJ_Numero NOT IN (SELECT SA_CompteurNumero FROM F_SAL))
DELETE FROM F_PIECESJOINTES
WHERE (PJ_Type = 1) AND             
      ((PJ_Numero = 0) OR (PJ_Numero NOT IN (SELECT SA_CompteurNumero FROM F_SAL)))
	  
Print 'Suppression des F_ANA pointant sur écriture inexistante par rapport / F_ECR'
-- SELECT ZR_EA_NoLien, ZR_EA_NoLigne FROM F_ANA WHERE EA_TypeEcriture = 0 AND (ZR_EA_NoLien NOT IN (SELECT ZR_EG_NoLien FROM F_ECR) Or ZR_EA_NoLigne NOT IN (SELECT ZR_EG_NoLigne FROM F_ECR))
DELETE FROM F_ANA
WHERE (EA_TypeEcriture = 0) AND 
(ZR_EA_NoLien NOT IN (SELECT ZR_EG_NoLien FROM F_ECR)
OR
 ZR_EA_NoLigne NOT In (SELECT ZR_EG_NoLigne FROM F_ECR))
-- PMEPAIE-2242 - Fin

--BEAL PMEPAIE-2418 Début
Print 'Suppression des F_ANA pointant sur écriture inexistante par rapport / F_ECR'
-- Select ZR_EA_NoLien, ZR_EA_NoLigne From F_ANA Where EA_TypeEcriture = 0 And (ZR_EA_NoLien NOT IN (Select ZR_EG_NoLien From F_ECR) Or ZR_EA_NoLigne NOT IN (Select ZR_EG_NoLigne FROM F_ECR))
Delete From F_ANA
Where (EA_TypeEcriture = 0) AND
(ZR_EA_NoLien NOT IN (Select ZR_EG_NoLien From F_ECR)
OR
ZR_EA_NoLigne NOT In (Select ZR_EG_NoLigne From F_ECR))

Print 'Suppression des F_DAS pointant sur établissement inexistant par rapport / F_ETA'
-- Select ET_Etablissement From F_DAS Where ET_Etablissement is NULL Or ET_Etablissement not in (Select ET_Code From F_ETA)
Delete From F_DAS Where ET_Etablissement is NULL Or ET_Etablissement not in (Select ET_Code From F_ETA)

--BEAL PMEPAIE-2418 FIN

Delete From F_GHRCAL Where CL_TypeCalendrier <> 0 And F_GHRCAL.SA_No_Salarie Not in (Select F_SAL.SA_No_Salarie From F_SAL)

Print 'Suppression des doublons dans F_CUMSAL'
--Insertion des Code et NoOrdre presentant de doublons dans une table temporaire
SELECT SA_No_Salarie, VB_Numero
INTO #CumSalDoublon
FROM 
	F_CumSAl
GROUP BY SA_No_Salarie, VB_Numero
HAVING
	COUNT(*)>1
--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #tempCumSal
FROM f_CumSal c, #CumSalDoublon d
WHERE 
	c.SA_No_Salarie = d.SA_No_Salarie AND
	c.VB_Numero = d.VB_Numero
	
--Suppression des doublons dans la table initiale
DELETE F_CumSal FROM  #CumSalDoublon d
WHERE 
	F_CumSal.SA_No_Salarie = d.SA_No_Salarie AND
	F_CumSal.VB_Numero = d.VB_Numero
 
--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempCumSal ADD Compteur INT identity (1,1)
GO
--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_CumSal
SELECT
   SA_No_Salarie, VB_Numero, VB_ElementCum, VB_ValeurCum_Intermediaire, VB_ValeurCum_Mois, 
   VB_ValeurCum_Trimestre, VB_ValeurCum_Annee, VB_ValeurEvt, IdOdbc
FROM #tempCumSal t
WHERE Compteur = (Select min(t1.Compteur) from #tempCumSAl t1 WHERE  t1.SA_No_Salarie = t.SA_No_Salarie  AND 
								  t1.VB_Numero = t.VB_Numero		
	     	  Group by t1.SA_No_Salarie, t1.VB_Numero  )


DROP TABLE #tempCumSal
DROP TABLE #CumSalDoublon

-- OBD 07/05/15
Print 'Suppression des doublons dans F_CUMSAL'
--Insertion des Code et NoOrdre presentant de doublons dans une table temporaire
SELECT SA_No_Salarie, VB_Numero
INTO #CumSalDoublon
FROM 
	F_CumSAl
GROUP BY SA_No_Salarie, VB_Numero
HAVING
	COUNT(*)>1
--Sauvegarde des doublons dans table tempo de mˆme structure que la table initiale
SELECT c.*
INTO #tempCumSal
FROM f_CumSal c, #CumSalDoublon d
WHERE 
	c.SA_No_Salarie = d.SA_No_Salarie AND
	c.VB_Numero = d.VB_Numero
	
--Suppression des doublons dans la table initiale
DELETE F_CumSal FROM  #CumSalDoublon d
WHERE 
	F_CumSal.SA_No_Salarie = d.SA_No_Salarie AND
	F_CumSal.VB_Numero = d.VB_Numero
 
--Numerotation des doublons pour pouvoir en recuperer un seul 
ALTER TABLE #tempCumSal ADD Compteur INT identity (1,1)
GO
--Insertion d'une seule des lignes doublons en jouant sur le min du compteur
INSERT INTO F_CumSal
SELECT
   SA_No_Salarie, VB_Numero, VB_ElementCum, VB_ValeurCum_Intermediaire, VB_ValeurCum_Mois, 
   VB_ValeurCum_Trimestre, VB_ValeurCum_Annee, VB_ValeurEvt, IdOdbc
FROM #tempCumSal t
WHERE Compteur = (Select min(t1.Compteur) from #tempCumSAl t1 WHERE  t1.SA_No_Salarie = t.SA_No_Salarie  AND 
	  t1.VB_Numero = t.VB_Numero		
	  Group by t1.SA_No_Salarie, t1.VB_Numero  )
DROP TABLE #tempCumSal
DROP TABLE #CumSalDoublon

-- Suppression des entrées invalides de F_HST_DADSU : codes "Régimes obligatoires"
DELETE FROM F_HST_DADSU WHERE ADU_RegimedeBaseObligatoire NOT IN (SELECT TA_Code
  FROM F_TAB
  -- >> Remarquer le filtrage pour les références "Régimes obligatoires" 
  WHERE ta_typetable = 16)
  
-- Mise à 'Null" valeurs non référencées FP_InfosDADSU_RegimeBaseOblig/FP_InfosDADSU_StatutProf de F_RENSSAL
UPDATE F_RENSSAL
SET FP_InfosDADSU_StatutProf = NULL
WHERE FP_InfosDADSU_StatutProf NOT IN
(SELECT TA_CODE FROM F_TAB WHERE TA_TYPETABLE = 17);

-- Fin OBD 07/05/15

-- OBD 26/05/15
Print 'Correction des F_RensSal / F_ETA'
--SELECT FP_InfosBTP_Etablissement FROM F_RENSSAL WHERE FP_InfosBTP_Etablissement NOT IN (Select ET_Code From F_ETA)
Update F_RENSSAL Set FP_InfosBTP_Etablissement = FP_Etab_Code Where FP_InfosBTP_Etablissement not in (Select ET_Code From F_ETA)

-- Fin OBD 26/05/15


