Delimiter //
/**
 * Author:  armen
 * Created: 23 juin 2020
 */
Drop Procedure if exists LOADER_MEMBERS_METADA_UPDATE//

Create Procedure LOADER_MEMBERS_METADA_UPDATE (
    In batchnum int
)
Begin
    lmmu_main: Begin
        set @lmmu_batchnum =batchnum;
        set @lmmu_key = 'LOADERINFO_XML';

        lmmu_purge: Begin
            Delete tar
            From MEMBERS_METADATA tar
            where
                tar.MEMBERS_METADATA_KEY = @lmmu_key
                And Exists ( 
                    Select
                        src.ID_MEMBERS
                    From  LOADER_MEMBERS src  
                        Inner Join LOADER_STATUS lst  
                            On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
                    Where lst.LOADER_STATUS_ERRCOUNT = 0  
                        And lst.LOADER_STATUS_BATCH_NUM =  @lmmu_batchnum
                        And tar.MEMBERS_ID_MEMBERS = src.ID_MEMBERS
                    Limit 1
                )
            ;
        End;
        lmmu_insert: Begin
            Call LOADER_GET_ALL_INFO_FORMATED('xml', @lmmu_batchnum, @lmmu_infoxml);

            Insert Into MEMBERS_METADATA (
                MEMBERS_ID_MEMBERS, MEMBERS_METADATA_KEY, MEMBERS_METADATA_EXTID
                , MEMBERS_METADATA_DATE, MEMBERS_METADATA_TEXT
            )
            Select
                src.ID_MEMBERS,  @lmmu_key, null
                , curdate(), @lmmu_infoxml
            From
                LOADER_MEMBERS src  
                Inner Join LOADER_STATUS lst  
                    On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
            Where
                lst.LOADER_STATUS_ERRCOUNT = 0  
                And lst.LOADER_STATUS_BATCH_NUM =  @lmmu_batchnum
                And Coalesce(src.ID_MEMBERS, 0) <> 0
            ;
        End;
    End;
End //

Delimiter ;