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

Drop Procedure if exists LOADER_CONTACTS_BUSINESS_INSERT//

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

        Insert Into CONTACTS_BUSINESS (
            CONTACTS_ID_CONTACTS, 
            CONTACTS_BUSINESS_SIRET, 
            CONTACTS_BUSINESS_LAST_UPDATE, CONTACTS_BUSINESS_CREATED_DATE, 
            CONTACTS_BUSINESS_COMPANY_NAME,
            CONTACTS_BUSINESS_BRAND_NAME, 
            DF_FORMS_ID_DF_FORMS,
            CONTACT_INDIV_ID_CONTACT_INDIV
        )  
        Select  
            src.ID_CONTACTS_NEW + lastId, 
            src.CONTACTS_BUSINESS_SIRET, 
            src.CONTACTS_LAST_UPDATE, src.CONTACTS_LAST_UPDATE, 
            src.CONTACTS_BUSINESS_COMPANY_NAME, 
            src.CONTACTS_BUSINESS_BRAND_NAME, 
            src.DF_FORMS_ID_DF_FORMS, 
            TBL_1.ID_CONTACTS_INDIVIDUAL
        From  
            LOADER_CONTACTS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
            Inner Join CONTACTS_INDIVIDUAL TBL_1
                On src.ID_CONTACTS_NEW + lastId = TBL_1.CONTACTS_ID_CONTACTS
        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
--             And src.CONTACTS_BUSINESS_COMPANY_NAME is Not null
            And Coalesce(src.CONTACTS_BUSINESS_COMPANY_NAME, src.CONTACTS_BUSINESS_SIRET) is Not null
            And Coalesce(src.ID_CONTACTS_NEW, 0) <> 0 
        ;

		Drop Temporary table if exists TMP_NEW_CONTACTS_BUSINESS;
		Create Temporary table TMP_NEW_CONTACTS_BUSINESS (
			CONTACTS_ID_CONTACTS Int unsigned not null
			, Index (CONTACTS_ID_CONTACTS)
		);
		Insert Into TMP_NEW_CONTACTS_BUSINESS(CONTACTS_ID_CONTACTS)
		Select 
			src.ID_CONTACTS
		From  
            LOADER_CONTACTS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
            Inner Join CONTACTS_INDIVIDUAL TBL_1
                On src.ID_CONTACTS = TBL_1.CONTACTS_ID_CONTACTS
-- 			Left Join CONTACTS_BUSINESS tar
-- 				On src.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
        Where 
            lst.LOADER_STATUS_BATCH_NUM =  batchnum
            And lst.LOADER_STATUS_ERRCOUNT = 0  
--             tar.CONTACTS_ID_CONTACTS Is Null
--             And src.CONTACTS_BUSINESS_COMPANY_NAME is Not null
            And Coalesce(src.CONTACTS_BUSINESS_COMPANY_NAME, src.CONTACTS_BUSINESS_SIRET) is Not null
            And Not Exists (
                Select tar.CONTACTS_ID_CONTACTS
                From CONTACTS_BUSINESS tar
                Where src.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                Limit 1
            )
		;

		Insert Into CONTACTS_BUSINESS (
            CONTACTS_ID_CONTACTS, 
            CONTACTS_BUSINESS_SIRET, 
            CONTACTS_BUSINESS_LAST_UPDATE, CONTACTS_BUSINESS_CREATED_DATE, 
            CONTACTS_BUSINESS_COMPANY_NAME,
            CONTACTS_BUSINESS_BRAND_NAME, 
            DF_FORMS_ID_DF_FORMS,
            CONTACT_INDIV_ID_CONTACT_INDIV
        )  
        Select  
            src.ID_CONTACTS, 
            src.CONTACTS_BUSINESS_SIRET, 
            src.CONTACTS_LAST_UPDATE, src.CONTACTS_LAST_UPDATE, 
            src.CONTACTS_BUSINESS_COMPANY_NAME, 
            src.CONTACTS_BUSINESS_BRAND_NAME, 
            src.DF_FORMS_ID_DF_FORMS, 
            TBL_1.ID_CONTACTS_INDIVIDUAL
        From  
            LOADER_CONTACTS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
            Inner Join CONTACTS_INDIVIDUAL TBL_1
                On src.ID_CONTACTS = TBL_1.CONTACTS_ID_CONTACTS
			Inner Join TMP_NEW_CONTACTS_BUSINESS newbctc
				On src.ID_CONTACTS = newbctc.CONTACTS_ID_CONTACTS
        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
        ;

		Drop Temporary table if exists TMP_NEW_CONTACTS_BUSINESS;


End //

Delimiter ;