Delimiter //
/**
 * rempli la table LOADER_ENTRIES_FORCED_MATCH_STATE
 * avec les écritures ayant un statut de lettrage forcé
 * - nouveau statue de lettrage forcé sur l'écriture (écriture nouvelle ou sans statut forcé)
 * - modification du statue de lettre forcé sur l'écriture (écriture éxistant avec un autre statut forcé
 * Cette PS doit être lancée après LOADER_ENTRIES_NORMALIZE_MAP_MATCHING_CODE_STATE_SET
 *  => se base sur le flag MATCHINGCODE_STATE_FORCED
 */
Drop Procedure if Exists LOADER_ENTRIES_NORMALIZE_MAP_FORCED_MATCH_STAT//
 
Create Procedure LOADER_ENTRIES_NORMALIZE_MAP_FORCED_MATCH_STAT (
    In batchnum int
)
Begin
    lenmfms_main: BEGIN

        lenmfms_init: BEGIN
            -- pour debug
#             Set @lenmfms_batchnum = 1;
            Set @lenmfms_batchnum = batchnum;
            Delete LOADER_ENTRIES_FORCED_MATCH_STATE from LOADER_ENTRIES_FORCED_MATCH_STATE;
        end ;
        lenmfms_newEntries: BEGIN

            Insert Into LOADER_ENTRIES_FORCED_MATCH_STATE (
                    LOADER_STATUS_ID_LOADER_STATUS
                    , LOADER_ENTRIES_ID_LOADER_ENTRIES
                    , DF_MATCH_STAT_ID_DF_MATCH_STAT
                )
            SELECT
                ls.ID_LOADER_STATUS
                , le.ID_LOADER_ENTRIES
                , le.DF_MATCH_STAT_ID_DF_MATCH_STAT
            From LOADER_STATUS ls
                Inner Join LOADER_ENTRIES le
                    On ls.ID_LOADER_STATUS = le.LOADER_STATUS_ID_LOADER_STATUS
            Where
                ls.LOADER_STATUS_BATCH_NUM = @lenmfms_batchnum
                And ls.LOADER_STATUS_ERRCOUNT = 0
                And le.MATCHINGCODE_STATE_FORCED = 1
                And le.ID_ENTRIES_NEW Is Not Null
            ;
        end ;

        lenmfms_entriesUpdated: BEGIN
            Insert Into LOADER_ENTRIES_FORCED_MATCH_STATE (
                    LOADER_STATUS_ID_LOADER_STATUS
                    , LOADER_ENTRIES_ID_LOADER_ENTRIES
                    , DF_MATCH_STAT_ID_DF_MATCH_STAT
                )
            SELECT
                ls.ID_LOADER_STATUS
                , le.ID_LOADER_ENTRIES
                , le.DF_MATCH_STAT_ID_DF_MATCH_STAT
#                 , ecd.*

            From LOADER_STATUS ls
                Inner Join LOADER_ENTRIES le
                    On ls.ID_LOADER_STATUS = le.LOADER_STATUS_ID_LOADER_STATUS
                Left Join ENTRIES_COMPUTED_DATA ecd
                    On le .ID_ENTRIES = ecd.ENTRIES_ID_ENTRIES
                    And ecd.ENTRIES_COMPUTED_DATA_KEY = 'FORCEDMATCHSTS'
            Where
                ls.LOADER_STATUS_BATCH_NUM = @lenmfms_batchnum
                And ls.LOADER_STATUS_ERRCOUNT = 0
                And le.MATCHINGCODE_STATE_FORCED = 1
                And le.ID_ENTRIES Is Not Null -- non implicite
                and (
                    -- ecritures existantes nouvellement forcé
                    ecd.ENTRIES_ID_ENTRIES Is Null
                    -- ecritures déjà forcé, avec un nouveau status
                    Or le.DF_MATCH_STAT_ID_DF_MATCH_STAT <> ecd.ENTRIES_COMPUTED_DATA_EXTID
                )
            ;
        end ;

    end ;


End //
Delimiter ;