Delimiter //
/**
	obtient l'id du status de reclamation à partir du code status
	si code status non fournis ou non trouvé utilisation du status "favoris"
	Si code status non trouvé => alerte
*/
Drop Procedure If Exists LOADER_RECLAIMS_NORMALIZE_MAP_STATUS_CODE//
Create Procedure LOADER_RECLAIMS_NORMALIZE_MAP_STATUS_CODE(
    In batchnum Int
)
Begin
	Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);
	-- obtention du status de reclamation favoris
	Set @idStatusFav = (
			Select Max(ID_DF_RECLAIMS_STATUS) 
			From DF_RECLAIMS_STATUS 
			WHERE 
				ENTITIES_ID_ENTITIES = @idEntities 
				And DF_RECLAIMS_STATUS_DEFAULT = 1
		) ;
	Set @isStatusFavBloked = (
			Select DF_RECLAIMS_STATUS_CLOSED
			From DF_RECLAIMS_STATUS 
			WHERE 
				ID_DF_RECLAIMS_STATUS = @idStatusFav
		) ;

    Update
        LOADER_STATUS STS
        Inner Join LOADER_RECLAIMS LRD
            On LRD.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
		Inner Join DF_RECLAIMS_STATUS DFRS
			On LRD.DF_RECLAIMS_STATUS_CODE = DFRS.DF_RECLAIMS_STATUS_CODE
			And DFRS.ENTITIES_ID_ENTITIES =  @idEntities
    Set
        LRD.DF_RCLM_STT_ID_DF_RCLM_STT = DFRS.ID_DF_RECLAIMS_STATUS
		, LRD.RECLAIMS_DETAILS_CLOSED = DFRS.DF_RECLAIMS_STATUS_CLOSED
    Where
        STS.LOADER_STATUS_BATCH_NUM = batchnum
		And STS.LOADER_STATUS_ERRCOUNT = 0 
    ;

    -- maj des alertes

	call LOADER_BUILD_RECLAIMS_CHECK_BASE_QUERY (
				'DF_RECLAIMS_STATUS_CODE', batchnum, 'Undefined status code replaced by default value', '', 1, @query
			)
		;
	set @query = Concat(@query
					, ' And Coalesce(DF_RECLAIMS_STATUS_CODE, '''') <> '''' '
					, ' And DF_RCLM_STT_ID_DF_RCLM_STT Is Null'
				)
			;

	PREPARE stmt FROM @query;
	execute stmt			
		;
	Deallocate prepare stmt;

	-- code statuis par defaut
	Update
        LOADER_STATUS STS
        Inner Join LOADER_RECLAIMS LRD
            On LRD.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
    Set
        LRD.DF_RCLM_STT_ID_DF_RCLM_STT = @idStatusFav
		, LRD.RECLAIMS_DETAILS_CLOSED = @isStatusFavBloked 
    Where
        STS.LOADER_STATUS_BATCH_NUM = batchnum
		And STS.LOADER_STATUS_ERRCOUNT = 0
    ;

End//
Delimiter ;