Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
    
*/

Drop Procedure if exists LOADER_CONTACTS_INDIVIDUAL_INSERT//

Create Procedure LOADER_CONTACTS_INDIVIDUAL_INSERT (
    In batchnum Int, 
    In lastId Int
)
Begin
        Insert Into CONTACTS_INDIVIDUAL (
            CONTACTS_ID_CONTACTS,
            DF_TITLES_ID_DF_TITLES, DF_FUNCTIONS_ID_DF_FUNCTIONS, 
            CONTACTS_INDIVIDUAL_FIRST_NAME, CONTACTS_INDIVIDUAL_LAST_NAME, 
            CONTACTS_INDIVIDUAL_BIRTH_NAME, 
--             CONTACTS_INDIVIDUAL_BIRTH_DAY, 
            CONTACTS_INDIVIDUAL_LAST_UPDAT
            
        )  
        Select  
            src.ID_CONTACTS_NEW + lastId , 
            src.DF_TITLES_ID_DF_TITLES, DF_FUNCTIONS_ID_DF_FUNCTIONS, 
            src.CONTACTS_INDIVIDUAL_FIRST_NAME, src.CONTACTS_INDIVIDUAL_LAST_NAME, 
            src.CONTACTS_INDIVIDUAL_BIRTH_NAME, 
            src.CONTACTS_LAST_UPDATE
        From  
            LOADER_CONTACTS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  

        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            And Coalesce(src.ID_CONTACTS_NEW, 0) <> 0 
        ;

	-- il se peut que des contacts soient exitants mais pas les contact_idividual de ces contacts
	-- creation de nouveauw CONTACTS_INDIVIDUAL pour des contacts existant sans CONTACTS_INDIVIDUAL

		Insert Into CONTACTS_INDIVIDUAL (
            CONTACTS_ID_CONTACTS,
            DF_TITLES_ID_DF_TITLES, DF_FUNCTIONS_ID_DF_FUNCTIONS, 
            CONTACTS_INDIVIDUAL_FIRST_NAME, CONTACTS_INDIVIDUAL_LAST_NAME, 
            CONTACTS_INDIVIDUAL_BIRTH_NAME, 
            CONTACTS_INDIVIDUAL_LAST_UPDAT
            
        )  
        Select  
            src.ID_CONTACTS, 
            src.DF_TITLES_ID_DF_TITLES, src.DF_FUNCTIONS_ID_DF_FUNCTIONS, 
            src.CONTACTS_INDIVIDUAL_FIRST_NAME, src.CONTACTS_INDIVIDUAL_LAST_NAME, 
            src.CONTACTS_INDIVIDUAL_BIRTH_NAME, 
            src.CONTACTS_LAST_UPDATE
        From  
            LOADER_CONTACTS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
-- 			Left Join CONTACTS_INDIVIDUAL ctcchk
-- 				On src.ID_CONTACTS = ctcchk.CONTACTS_ID_CONTACTS
        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
			And src.ID_CONTACTS Is Not Null
--             And ctcchk.CONTACTS_ID_CONTACTS Is Null
            And Not Exists (
                Select ctcchk.CONTACTS_ID_CONTACTS
                From CONTACTS_INDIVIDUAL ctcchk
                Where src.ID_CONTACTS = ctcchk.CONTACTS_ID_CONTACTS
                Limit 1
            )
        ;




End //

Delimiter ;