Delimiter //
/**
 * Set the matching code state value.
 * - default value or forced value
 * set the flag MATCHINGCODE_STATE_FORCED
 * @param int batchnum
 * @author Benoit
 * ticket #4002
 */ 
Drop Procedure if Exists LOADER_ENTRIES_NORMALIZE_MAP_MATCHING_CODE_STATE_SET//
 
Create Procedure LOADER_ENTRIES_NORMALIZE_MAP_MATCHING_CODE_STATE_SET (
    In batchnum int
)
Begin
    -- statue de lettrage automatique
    Set @lenmm_openStatId = (
                Select ID_DF_MATCHINGCODE_STATE
                From DF_MATCHINGCODE_STATE
                Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY'
                    And DF_MATCHINGCODE_STATE_OPEN = 1
                    And DF_MATCHINGCODE_STATE_PARTIAL = 0
                    And DF_MATCHINGCODE_STATE_CLOSED = 0
                Limit 1
            )
        ;
    Set @lenmm_batchnum = batchnum;

	-- ticket #6291 gestion des statues de lettrage forcé

    -- retro compatibilité
    Update
        LOADER_ENTRIES LE
        Inner Join LOADER_STATUS S
            On S.ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
    Set LE.ENTRIES_MATCHINGCODES_STATE = LE.ENTRIES_MATCHINGCODE_STATE
    Where
        LE.ENTRIES_MATCHINGCODES_STATE Is NULL
        And LE.ENTRIES_MATCHINGCODE_STATE Is Not Null
        And S.LOADER_STATUS_BATCH_NUM = @lenmm_batchnum
    ;


	-- statut de lettrage forcé par id statut
	-- règle prioritaire attribut la valeur par défaut d'ou l'exclusion volontaire du test "LE.ENTRIES_MATCHINGCODES_STATE Is Not Null"
    -- les valeurs null sont pris en compte dans les clauses case when
    Update
        LOADER_ENTRIES LE
        Inner Join LOADER_STATUS S
            On S.ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
		Left Join DF_MATCHINGCODE_STATE DMS
			ON LE.ENTRIES_MATCHINGCODES_STATE = DMS.ID_DF_MATCHINGCODE_STATE
    Set
        LE.DF_MATCH_STAT_ID_DF_MATCH_STAT = Coalesce(DMS.ID_DF_MATCHINGCODE_STATE, @lenmm_openStatId)
        -- ci dessous, on test explicitement IS NOT null pour prendre en compte
        -- - les status forcé d'ont l'id est trouvé
        -- - la valeurs par défaut, pour les champs null ou non trouvé
        , LE.MATCHINGCODE_STATE_FORCED = Case When DMS.ID_DF_MATCHINGCODE_STATE Is Not Null Then 1 Else 0 End
        , S.LOADER_STATUS_WARNCOUNT = (S.LOADER_STATUS_WARNCOUNT
                                        + Case
                                                -- ignoré si non forcé
                                                When LE.ENTRIES_MATCHINGCODES_STATE Is Null Then 0
                                                -- forcé mais incorrect
                                                When DMS.ID_DF_MATCHINGCODE_STATE Is Null Then 1
                                                Else 0
                                          End
                                    )
        , S.LOADER_STATUS_WARNMSGS = (
                CASE
                    -- ignoré si non forcé
                    When LE.ENTRIES_MATCHINGCODES_STATE Is Null Then LOADER_STATUS_WARNMSGS
                    -- forcé mais incorrect
                    When DMS.ID_DF_MATCHINGCODE_STATE Is Null Then
                         LOADER_CONCAT_ERR(S.LOADER_STATUS_WARNMSGS
                             -- ci dessous pas basoin de coalesace en raison du 1er when
                                        , Concat('ID de statut de lettrage inexistant :', LE.ENTRIES_MATCHINGCODES_STATE )
                                    )
                    ELSE S.LOADER_STATUS_WARNMSGS
                END
            )
    Where
        S.LOADER_STATUS_BATCH_NUM = @lenmm_batchnum
#         And LE.ENTRIES_MATCHINGCODES_STATE Is Not Null
    ;


	-- statut de lettrage forcé par code statut
	-- obtention de l'id de statut à l'aide du code statut mappé
	--

    Update
        LOADER_ENTRIES LE
        Inner Join LOADER_STATUS S
            On S.ID_LOADER_STATUS = LE.LOADER_STATUS_ID_LOADER_STATUS
		Left Join DF_MATCHINGCODE_STATE DMS
			ON LE.ENTRIES_MATCHINGCODES_STATE_CODE = DMS.DF_MATCHINGCODE_STATE_CODE
    Set
#         LE.DF_MATCH_STAT_ID_DF_MATCH_STAT = DMS.ID_DF_MATCHINGCODE_STATE
        LE.DF_MATCH_STAT_ID_DF_MATCH_STAT = Coalesce(DMS.ID_DF_MATCHINGCODE_STATE, LE.DF_MATCH_STAT_ID_DF_MATCH_STAT, @lenmm_openStatId)
        , LE.MATCHINGCODE_STATE_FORCED = Case When DMS.ID_DF_MATCHINGCODE_STATE Is Null Then 0 Else 1 End
        , S.LOADER_STATUS_WARNCOUNT = (S.LOADER_STATUS_WARNCOUNT
                + Case When DMS.ID_DF_MATCHINGCODE_STATE Is Null Then 1 Else 0 End
            )
        , S.LOADER_STATUS_WARNMSGS = (
                CASE WHEN DMS.ID_DF_MATCHINGCODE_STATE Is Null
                     THEN LOADER_CONCAT_ERR(S.LOADER_STATUS_WARNMSGS, Concat('Statut de lettrage inexistant :', LE.ENTRIES_MATCHINGCODES_STATE_CODE ) )
                     ELSE S.LOADER_STATUS_WARNMSGS
                END
            )
    Where
        S.LOADER_STATUS_BATCH_NUM = @lenmm_batchnum
        And Coalesce(LE.ENTRIES_MATCHINGCODES_STATE_CODE, '') <> ''
        -- peut avoir été maj par id, on test si uniquement code fourni
		And LE.ENTRIES_MATCHINGCODES_STATE Is Null
    ;

End //
Delimiter ;