Delimiter //
/**
 * Find the id of the given sequence
 * muste be executed AFTER LOADER_MEMBERSACCRED_NORMALIZE_MAP_ID_ENT_REF
 */

Drop Procedure If Exists LOADER_MEMBERSACCRED_NORMALIZE_MAP_ID_ENT_REF_DATAS//
Create Procedure LOADER_MEMBERSACCRED_NORMALIZE_MAP_ID_ENT_REF_DATAS(
    In batchnum Int
)
Begin
    

    Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);
    -- en raison du multi-cible il ne faut pas faire de jointure 
    -- direct entre LOADER_MEMBERSACCRED_REFDATA et LOADER_STATUS
    -- il est nécssire de faire une joiture vers ID_LOADER_MEMBERSACCRED

    Update
        LOADER_STATUS sts
        Inner Join LOADER_MEMBERSACCRED src
            On src.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
        Inner Join LOADER_MEMBERSACCRED_REFDATA ARD
            On ARD.LOADER_MEMBERSACCRED_ID_LOADER_MEMBERSACCRED = src.ID_LOADER_MEMBERSACCRED
        Inner Join ENTITIES_REF_DATAS ERD
            On ARD.ENTITIES_REF_ID_ENTITIES_REF = ERD.ENTITIES_REF_ID_ENTITIES_REF
            And ARD.ENTITIES_REF_DATA_CODE = ERD.ENTITIES_REF_DATAS_CODE
    Set
        ARD.ID_ENTITIES_REF_DATAS = ERD.ID_ENTITIES_REF_DATAS
    Where
        sts.LOADER_STATUS_BATCH_NUM = batchnum
    ;

    --
    -- table temporaire pour les références en création
    -- permet d'obtenir un auto increment
    DROP TEMPORARY TABLE IF EXISTS temp_ENTREFDATA;
    CREATE TEMPORARY TABLE temp_ENTREFDATA (
        ID_ENTITIES_REF_DATAS Int Unsigned Not Null Auto_increment
        , ENTITIES_REF_ID_ENTITIES_REF Int Unsigned Not Null 
        , ENTITIES_REF_DATA_CODE Varchar(20)
--         , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
        , Primary Key (ID_ENTITIES_REF_DATAS)
        , index (ENTITIES_REF_ID_ENTITIES_REF, ENTITIES_REF_DATA_CODE)
    );

    -- insert des données
    Insert Into temp_ENTREFDATA (ENTITIES_REF_ID_ENTITIES_REF, ENTITIES_REF_DATA_CODE)
    Select Distinct
        ARD.ENTITIES_REF_ID_ENTITIES_REF
        , ARD.ENTITIES_REF_DATA_CODE
    From    
        LOADER_STATUS sts
        Inner Join LOADER_MEMBERSACCRED src
            On src.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
        Inner Join LOADER_MEMBERSACCRED_REFDATA ARD
            On ARD.LOADER_MEMBERSACCRED_ID_LOADER_MEMBERSACCRED = src.ID_LOADER_MEMBERSACCRED
    Where 
        sts.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(ARD.ENTITIES_REF_DATA_CODE, '') != ''
        And ARD.ID_ENTITIES_REF_DATAS Is Null
        And sts.LOADER_STATUS_ERRCOUNT = 0
    ;

    -- mise à jour du champ NEW_ID_ENTITIES_REF_DATAS 

    Update
        LOADER_STATUS sts
        Inner Join LOADER_MEMBERSACCRED src
            On src.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
        Inner Join LOADER_MEMBERSACCRED_REFDATA ARD
            On ARD.LOADER_MEMBERSACCRED_ID_LOADER_MEMBERSACCRED = src.ID_LOADER_MEMBERSACCRED
        Inner Join temp_ENTREFDATA TMPERD
            On ARD.ENTITIES_REF_ID_ENTITIES_REF = TMPERD.ENTITIES_REF_ID_ENTITIES_REF
            And ARD.ENTITIES_REF_DATA_CODE = TMPERD.ENTITIES_REF_DATA_CODE

    Set
        ARD.NEW_ID_ENTITIES_REF_DATAS = TMPERD.ID_ENTITIES_REF_DATAS
    Where 
        sts.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(ARD.ENTITIES_REF_DATA_CODE, '') != ''
        And ARD.ID_ENTITIES_REF_DATAS Is Null
        And sts.LOADER_STATUS_ERRCOUNT = 0
    ;

    -- maj des erreur

    Update
        LOADER_STATUS sts
        Inner Join LOADER_MEMBERSACCRED src
            On src.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS
        Inner Join LOADER_MEMBERSACCRED_REFDATA ARD
            On ARD.LOADER_MEMBERSACCRED_ID_LOADER_MEMBERSACCRED = src.ID_LOADER_MEMBERSACCRED
    Set
        sts.LOADER_STATUS_ERRCOUNT = sts.LOADER_STATUS_ERRCOUNT + 1
        , sts.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(sts.LOADER_STATUS_ERRMSGS, Concat('Référence inexistante :', ARD.ENTITIES_REF_DATA_CODE ) )
    Where 
        sts.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(ARD.ENTITIES_REF_DATA_CODE, '') != ''
        And Coalesce(ARD.ID_ENTITIES_REF_DATAS,  ARD.NEW_ID_ENTITIES_REF_DATAS) Is Null
    ;

End//
Delimiter ;