begin transaction
-- OBD 09/11/2018
Print 'Correction de F_MontantDef par rapport / F_Montant'
-- Select MDF_IdMontant from F_MONTANTDEF where MDF_IdMontant not in (select IdMontant From F_Montant)
-- Select MDF_CodeMontant From F_MontantDef Where MDF_CodeMontant not in (Select MT_Code From F_Montant)
Delete From F_MONTANTDEF Where MDF_IdMontant not in (Select F_Montant.IdMontant From F_Montant) or 
			MDF_CodeMontant is NULL Or MDF_CodeMontant = '' or 
			MDF_CodeMontant not in (Select MT_Code From F_Montant)			

Print 'Correction de F_CST_Cumul par rapport / F_CST'
-- Select CC_CodeConstante from F_CST_Cumul where CC_CodeConstante not in (select CT_Code From F_CST)
Delete From F_CST_Cumul Where CC_CodeConstante not in (select CT_Code From F_CST) Or CC_CodeConstanteCumulIndi not in (select CT_Code From F_CST)

Print 'Correction de F_CST_Histo par rapport / F_CST'
-- Select CT_Code from F_CST_Histo where CT_Code not in (select CT_Code From F_CST)
Delete From F_CST_Histo Where F_CST_Histo.CT_Code not in (select F_CST.CT_Code From F_CST)

Print 'Correction de F_CST_Rubrique par rapport / F_CST'
-- Select CRB_CodeConstante from F_CST_Rubrique where CRB_CodeConstante not in (select CT_Code From F_CST)
Delete From F_CST_Rubrique Where CRB_CodeConstante not in (select F_CST.CT_Code From F_CST)

Print 'Correction de F_CSTBmod par rapport / F_CST'
-- Select CBM_CodeConstante from F_CSTBmod where CBM_CodeConstante not in (select CT_Code From F_CST)
Delete From F_CSTBmod Where CBM_CodeConstante not in (select F_CST.CT_Code From F_CST)

Print 'Suppression de F_CSTLigne par rapport / F_CST'
-- Select LC_CodeConstante from F_CSTLigne where LC_CodeConstante not in (select CT_Code From F_CST)
Delete from F_CSTLigne where LC_CodeConstante not in (select CT_Code From F_CST)
-- Fin OBD 09/11/2018
Commit Transaction

Begin Transaction
-- OBD 27/11/2018
Print 'Suppression de F_HST_PREVOYANCE avec code contrat null ou vide'
-- Select ACP_Prevoyance from F_HST_PREVOYANCE where ACP_Prevoyance is NULL Or ACP_Prevoyance = 0
Delete from F_HST_PREVOYANCE where ACP_Prevoyance is NULL Or ACP_Prevoyance = '' or  SA_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)

Print 'Suppression de F_PrevoyanceSalarieSalarie avec code contrat null ou vide'
-- Select CP_Code from F_PrevoyanceSalarie where CP_Code is NULL Or CP_Code = ''
Delete from F_PrevoyanceSalarie where CP_Code is NULL Or CP_Code = ''

Print 'Suppression de F_Prevoyance_Etab avec code contrat null ou vide'
-- Select CP_Code from F_Prevoyance_Etab where CP_Code is NULL Or CP_Code = ''
Delete from F_Prevoyance_Etab where CP_Code is NULL Or CP_Code = ''

Print 'Suppression de F_Prevoyance_Etab pointant sur établissement inexistant'
-- Select ET_CodeEtab From F_Prevoyance_Etab where ET_CodeEtab is NULL Or ET_CodeEtab Not in (Select ET_Code From F_ETA)
Delete from F_Prevoyance_Etab where ET_CodeEtab is NULL Or ET_CodeEtab Not in (Select ET_Code From F_ETA)

Print 'Suppression de F_DIP par rapport à F_SAL'
-- Select F_DIP.Sa_Matricule from F_DIP where F_DIP.Sa_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)
Delete from F_DIP where F_DIP.SA_Matricule is NULL Or F_DIP.SA_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)

Print 'Suppression de F_PER par rapport à F_SAL'
-- Select F_PER.Sa_Matricule from F_PER where F_PER.Sa_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)
Delete from F_PER where F_PER.SA_Matricule is NULL Or F_PER.SA_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL) or F_PER.SA_MATRICULE is null

Print 'Suppression de F_Langue_Salarie par rapport à F_SAL'
-- Select F_Langue_Salarie.SA_Matricule from F_Langue_Salarie where F_Langue_Salarie.SA_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)
Delete from F_Langue_Salarie where F_Langue_Salarie.Sa_Matricule is NULL Or F_Langue_Salarie.Sa_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)
-- Fin OBD 27/11/2018
Commit Transaction

Begin Transaction
-- OBD 30/11/2018
Print 'Suppression de F_MontantDef par rapport / F_RUB'
-- 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))

Print 'Suppression de F_BMOD avec code à 0'
-- Select * From F_BMOD Where BM_Code = 0
Delete From F_BMOD Where BM_Code = 0

Print 'Suppression de F_GHRCal attribué à tort à un salarié'
-- Select * From F_GHRCal where CL_TypeCalendrier = 0 And SA_No_Salarie <> 0
Delete From F_GHRCal where CL_TypeCalendrier = 0 And SA_No_Salarie <> 0

Print 'Suppression de F_GHRCal sans salarié'
-- Select * From F_GHRCal where CL_TypeCalendrier = 1 And SA_No_Salarie = 0
Delete From F_GHRCal where CL_TypeCalendrier = 1 And SA_No_Salarie = 0

Print 'Suppression de F_ArretDeTravail par rapport / F_SAL'
-- Select * From F_ArretDeTravail Where SA_NumeroSal is NULL Or 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_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 'Correction de F_HST_DADSU par rapport / F_TAB'
--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 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

Print 'Suppression de F_GroupeBMOD par rapport / F_BMOD'
-- Select BM_BulletinModele From F_GroupeBMOD Where BM_BulletinModele not in (select BM_Code From F_BMOD)
Delete From F_GroupeBMOD Where (BM_BulletinModele is NULL Or BM_BulletinModele = 0 Or (BM_BulletinModele not in (select BM_Code From F_BMOD)))

Print 'Suppression de F_GroupeBMOD par rapport / F_Groupe'
-- Select GRP_Code From F_GroupeBMOD Where F_GroupeBMOD.GRP_Code not in (select F_Groupe.GRP_Code From F_Groupe)
Delete From F_GroupeBMOD Where F_GroupeBMOD.GRP_Code not in (select F_Groupe.GRP_Code From F_Groupe)

Print 'Correction de F_HST_DADSU par rapport / F_ETA'
--Select ADU_EtablissementAgglomerant From F_HST_DADSU Where ADU_EtablissementAgglomerant not in (Select ET_Code From F_ETA)
Update F_HST_DADSU Set ADU_EtablissementAgglomerant = '' Where ADU_EtablissementAgglomerant not in (Select ET_Code From F_ETA)

Print 'Correction de F_ETC par rapport / F_KES'
--Select EC_PDUCS_CaisseRegroupement From F_ETC Where EC_PDUCS_CaisseRegroupement not in (Select KE_Code From F_KES)
Update F_ETC Set EC_PDUCS_CaisseRegroupement = 0 Where EC_PDUCS_CaisseRegroupement not in (Select KE_Code From F_KES)

--Print 'Correction de F_ETC par rapport / F_KES'
--Select KE_CodeCaisse From F_ETC Where KE_CodeCaisse not in (Select KE_Code From F_KES)
-- ceci est faux - ne pas le remettre Delete From F_ETC Where KE_CodeCaisse = 0 Or KE_CodeCaisse not in (Select KE_Code From F_KES)

Print 'Correction de F_Prevoyance_Etab / F_KES'
--Select PE_Caisse From F_Prevoyance_Etab Where PE_Caisse not in (Select KE_Code From F_KES)
Update F_Prevoyance_Etab SET PE_Caisse = NULL WHERE PE_Caisse not in (SELECT KE_Code FROM F_KES)

Print 'Correction de F_HST_InfosSociete / F_TAB'
--Select * From F_HST_InfosSociete Where AI_Societe_MotifDepart not in (Select TA_Code From F_TAB Where TA_TypeTable = 1)
Update F_HST_InfosSociete SET AI_Societe_MotifDepart = '' WHERE AI_Societe_MotifDepart not in (SELECT TA_Code FROM F_TAB Where TA_TypeTable = 1)
--Fin OBD 30/11/2018
Commit transaction

begin transaction
-- OBD 30/01/19
Print 'Correction de F_SALARIE_TAUX_PAS par rapport / F_SAL'
-- Select * From F_SALARIE_TAUX_PAS Where (SA_Matricule is NULL Or SA_Matricule = '') Or (F_SALARIE_TAUX_PAS.SA_Matricule not in (Select F_SAL.SA_Matricule From T_SAL))
Delete from F_SALARIE_TAUX_PAS Where (SA_Matricule is NULL Or SA_Matricule = '') Or (F_SALARIE_TAUX_PAS.SA_Matricule not in (Select F_SAL.SA_Matricule From F_SAL))

Print 'Suppression de F_Contact par rapport à F_SAL'
-- Select F_Contact.SA_Matricule from F_Contact where TypeContact = 0 And F_Contact.SA_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)
Delete from F_Contact where CNT_TypeContact = 0 And F_Contact.Sa_Matricule is NULL Or F_Contact.Sa_Matricule Not In (Select F_SAL.SA_Matricule From F_SAL)
-- Fin OBD 30/01/19
Commit Transaction

begin transaction
-- OBD 21/02/19
Print 'Correction de F_HstPIALaSource par rapport / F_SAL'
-- Select * From F_HstPIALaSource Where (SA_Matricule is NULL Or SA_Matricule = '') Or (F_HstPIALaSource.SA_Matricule not in (Select F_SAL.SA_Matricule From T_SAL))
Delete from F_HstPIALaSource Where (SA_Matricule is NULL Or SA_Matricule = '') Or (F_HstPIALaSource.SA_Matricule not in (Select F_SAL.SA_Matricule From F_SAL))
-- Fin OBD 21/02/19
Commit Transaction

