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

Drop Procedure if exists LOADER_CONTACTS_PHONES_INSERT//

Create Procedure LOADER_CONTACTS_PHONES_INSERT (
    In batchnum Int, 
    In lastId Int
)
Begin

        set @lcph_batchnum = batchnum;
        set @lcph_lastId = lastId;

        -- insetion des tél nouveaux contacts
    
        Insert Into CONTACTS_PHONES (
            CONTACTS_ID_CONTACTS, CONTACTS_PHONES_LAST_UPDATE, CONTACTS_PHONES_TRASHED, CONTACTS_PHONE_NUMBER, 
            CONTACTS_PHONE_NUMERIC_ONLY, CONTACTS_PHONE_PREFERENCE, DF_CHECK_STAT_ID_DF_CHECK_STAT, DF_PHONE_TYP_ID_DF_PHONE_TYP
            , CONTACTS_PHONES_ORIGIN
        )  
        Select  
            TBL_1.ID_CONTACTS_NEW + @lcph_lastId , src.CONTACTS_PHONES_LAST_UPDATE , src.CONTACTS_PHONES_TRASHED , src.CONTACTS_PHONE_NUMBER , 
            src.CONTACTS_PHONE_NUMERIC_ONLY , Coalesce(src.CONTACTS_PHONE_PREFERENCE, 0) , src.DF_CHECK_STAT_ID_DF_CHECK_STAT , src.DF_PHONE_TYP_ID_DF_PHONE_TYP
            , src.CONTACTS_PHONES_ORIGIN
        From  
            LOADER_CONTACTS_PHONES 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 =  @lcph_batchnum
            And Coalesce(TBL_1.ID_CONTACTS_NEW, 0) <> 0
        ;

        -- insertion des nouveaux téls des contacts existants
        Insert Into CONTACTS_PHONES (
            CONTACTS_ID_CONTACTS, CONTACTS_PHONES_LAST_UPDATE, CONTACTS_PHONES_TRASHED, CONTACTS_PHONE_NUMBER, 
            CONTACTS_PHONE_NUMERIC_ONLY, CONTACTS_PHONE_PREFERENCE, DF_CHECK_STAT_ID_DF_CHECK_STAT, DF_PHONE_TYP_ID_DF_PHONE_TYP
            ,CONTACTS_PHONES_ORIGIN
        )  
        Select  
            TBL_1.ID_CONTACTS , src.CONTACTS_PHONES_LAST_UPDATE , src.CONTACTS_PHONES_TRASHED , src.CONTACTS_PHONE_NUMBER , 
            src.CONTACTS_PHONE_NUMERIC_ONLY , src.CONTACTS_PHONE_PREFERENCE , src.DF_CHECK_STAT_ID_DF_CHECK_STAT , src.DF_PHONE_TYP_ID_DF_PHONE_TYP  
            , src.CONTACTS_PHONES_ORIGIN
        From  
            LOADER_STATUS lst  
            Inner Join LOADER_CONTACTS TBL_1 
                On lst.ID_LOADER_STATUS =  TBL_1.LOADER_STATUS_ID_LOADER_STATUS 
            Inner Join LOADER_CONTACTS_PHONES src  
            -- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
--                 On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
                On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
            Left Join CONTACTS_PHONES tar   
                On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                And src.CONTACTS_PHONE_NUMERIC_ONLY  = tar.CONTACTS_PHONE_NUMERIC_ONLY  
--                 And src.DF_PHONE_TYP_ID_DF_PHONE_TYP  = tar.DF_PHONE_TYP_ID_DF_PHONE_TYP  
        Where 1 = 1  
            And tar.ID_CONTACTS_PHONES Is Null
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  @lcph_batchnum
            And Coalesce(TBL_1.ID_CONTACTS, 0) <> 0
        ;

End //

Delimiter ;