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

Drop Procedure if exists LOADER_CONTACTS_EMAILS_INSERT//

Create Procedure LOADER_CONTACTS_EMAILS_INSERT (
    In batchnum Int, 
    In lastId Int
)
Begin
        Set @lcei_batchnum = batchnum;
		
        -- insertion email des nouveaux contacts
    
        Insert Into CONTACTS_EMAILS (
            CONTACTS_EMAILS_EMAIL, CONTACTS_EMAILS_LAST_UPDATE, CONTACTS_EMAILS_PREFERENCE, CONTACTS_EMAILS_TRASHED, 
            CONTACTS_ID_CONTACTS, DF_CHECK_STAT_ID_DF_CHECK_STAT
            , CONTACTS_EMAILS_ORIGIN
        )  
        Select 
            src.CONTACTS_EMAILS_EMAIL , src.CONTACTS_EMAILS_LAST_UPDATE , src.CONTACTS_EMAILS_PREFERENCE , src.CONTACTS_EMAILS_TRASHED ,  
            TBL_1.ID_CONTACTS_NEW + lastId, 
            src.DF_CHECK_STAT_ID_DF_CHECK_STAT  
            , src.CONTACTS_EMAILS_ORIGIN
        From  
			-- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
            LOADER_CONTACTS_EMAILS src  
			Inner Join LOADER_CONTACTS TBL_1 
                On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
            Inner Join LOADER_STATUS lst  
                On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  @lcei_batchnum
            And Coalesce(TBL_1.ID_CONTACTS_NEW, 0) <> 0
        ;

        -- insertion des nouveaux emails des contacts existants
    
        Insert Into CONTACTS_EMAILS (
            CONTACTS_EMAILS_EMAIL, CONTACTS_EMAILS_LAST_UPDATE, CONTACTS_EMAILS_PREFERENCE, CONTACTS_EMAILS_TRASHED, 
            CONTACTS_ID_CONTACTS, DF_CHECK_STAT_ID_DF_CHECK_STAT
            , CONTACTS_EMAILS_ORIGIN
        )  
        Select 
            src.CONTACTS_EMAILS_EMAIL , src.CONTACTS_EMAILS_LAST_UPDATE , src.CONTACTS_EMAILS_PREFERENCE , src.CONTACTS_EMAILS_TRASHED ,  
            TBL_1.ID_CONTACTS, 
            src.DF_CHECK_STAT_ID_DF_CHECK_STAT  
            , src.CONTACTS_EMAILS_ORIGIN
        From  
			-- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
            LOADER_CONTACTS_EMAILS src  
			Inner Join LOADER_CONTACTS TBL_1 
                On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
            Inner Join LOADER_STATUS lst  
                On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
            Left Join CONTACTS_EMAILS tar
                On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                And src.CONTACTS_EMAILS_EMAIL  = tar.CONTACTS_EMAILS_EMAIL  
        Where 1 = 1  
            And tar.ID_CONTACTS_EMAILS Is Null
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  @lcei_batchnum
            And Coalesce(TBL_1.ID_CONTACTS, 0) <> 0
        ;
End //

Delimiter ;