Delimiter //
/**
check if ENTITIES_REF_SEQUENCE value
**/
Drop Procedure if exists LOADER_REFDATA_NORMALIZE_ENTITIES_REF_SEQUENCE_RANGE //
Create Procedure LOADER_REFDATA_NORMALIZE_ENTITIES_REF_SEQUENCE_RANGE (
    In batchnum int
)
Begin
    Update
        LOADER_STATUS S
        Inner Join
            LOADER_REFDATA ARD
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = S.ID_LOADER_STATUS
		Inner Join ENTITIES_REF_TYPES ERT
			On ARD.LOADER_REFDATA_TYPE = ERT.ENTITIES_REF_TYPES_CODE
        
    Set
        S.LOADER_STATUS_ERRCOUNT = S.LOADER_STATUS_ERRCOUNT + 1
        , S.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(S.LOADER_STATUS_ERRMSGS, 'incorrect sequence value' ) 
    Where
        S.LOADER_STATUS_BATCH_NUM = batchnum
        And ARD.ENTITIES_REF_SEQUENCE Not Between 1 and ERT.ENTITIES_REF_TYPES_MAX_SEQ
    ;

End //
Delimiter ;