Delimiter //
/**
 * If the code date is not provided, initialyze it with the current date
 * 
 * @param int batchnum
 * @author Benoit
 * @ticket #3948
 */ 
Drop Procedure if Exists LOADER_MATCHINGCODE_NORMALIZE_MAP_MATCHING_STATE//
 
Create Procedure LOADER_MATCHINGCODE_NORMALIZE_MAP_MATCHING_STATE (
    In batchnum int
)
Begin
    lmnmms_main: Begin

            -- supression du status de lettrage forcé
            -- pour les case suivants 
            -- - codes statue AUTO
            -- - status id = 0
            -- lors de la copie des données , si  le champ DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE n'est pas null
            -- et a la valeur "0", cela indique qu'il faut on supprimer le status de lettrage forcé
            -- la supression se fait de manière indirecte lors de la phase de "copie" (poste normalisation) : 
            -- - la PS LOADER_MATCHINGCODES_ENTRIES_DELETE supprime de facto l'information de statue de lettrage forcé des 
            --      écritures en cours de lettrage 
            -- - la PS LOADER_MATCHINGCODES_ENTRIES_INSERT l'ajoute le statue de lettarge forcé que pour des valeurs non null suivants
            --      - 1 (ouvert)
            --      - 2 (partiel)
            --      - 3 (clos)
            -- la valeur "0" est donc ingnoré et ne sera donc recréée après sa supression


            Set @lmnmms_psModeAutoCode = 'AUTO'; -- constante local 

            Set @lmnmms_openEntryCode = 'OPEN ENTRY';
            Set @lmnmms_partialEntryCode = 'PARTIAL MATCHING CODE';
            Set @lmnmms_closedEntryCode = 'CLOSED ENTRY';

            Set @lmnmms_psModeAuto = 0; -- constante local 

            Set @lmnmms_openEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = @lmnmms_openEntryCode Limit 1);
            Set @lmnmms_partialEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = @lmnmms_partialEntryCode Limit 1);
            Set @lmnmms_closedEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = @lmnmms_closedEntryCode Limit 1);




            Update
                LOADER_MATCHINGCODES LM
                Inner Join LOADER_STATUS S
                    On S.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
            Set
                LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE = @lmnmms_psModeAuto -- 0 = AUTO
            Where 
                S.LOADER_STATUS_BATCH_NUM = batchnum
                And (
                    MATCHINGCODE_STATE = 0
                    Or MATCHINGCODES_STATE_CODE = @lmnmms_psModeAutoCode
                )
            ;



            -- obtention de l'id de status par le CODE STATUE mappé
            Update
                LOADER_MATCHINGCODES LM
                Inner Join LOADER_STATUS S
                    On S.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
                Inner Join DF_MATCHINGCODE_STATE DMS
                    ON LM.MATCHINGCODES_STATE_CODE = DMS.DF_MATCHINGCODE_STATE_CODE
            Set
                LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE = DMS.ID_DF_MATCHINGCODE_STATE
            Where 
                S.LOADER_STATUS_BATCH_NUM = batchnum
                And MATCHINGCODE_STATE Is Null
                -- ci dessous pour permettre la gestion de la valeur "0" -> mode auto, supression du status forcé
                And LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Null
            ;


            -- Alerte si le code status n'est pas trouvé
            Set @lmnmms_query = null;
            call LOADER_BUILD_CHECK_BASE_QUERY('LOADER_MATCHINGCODES', 'MATCHINGCODES_STATE_CODE', 'LOADER_STATUS_BATCH_NUM', batchnum, 'WARNINGCOUNT', 'WARNINGMSG', 'Matching status CODE not found', null, null, @lmnmms_query);
            set @lmnmms_query = Concat(@lmnmms_query, ' And MATCHINGCODES_STATE_CODE Is Not Null And DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Null And MATCHINGCODE_STATE Is Null ');
            PREPARE stmt FROM @lmnmms_query;
            execute stmt			
                ;
            Deallocate prepare stmt;


            -- vérification de l'ID de statue mappé
            Update
                LOADER_MATCHINGCODES LM
                Inner Join LOADER_STATUS S
                    On S.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
                Inner Join DF_MATCHINGCODE_STATE DMS
                    ON LM.MATCHINGCODE_STATE = DMS.ID_DF_MATCHINGCODE_STATE
            Set
                LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE = DMS.ID_DF_MATCHINGCODE_STATE
            Where 
                S.LOADER_STATUS_BATCH_NUM = batchnum
                -- ci dessous pour permettre la gestion de la valeur "0" -> mode auto, supression du status forcé
                And LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Null
            ;

            -- Alerte si le l'id status n'est pas trouvé
            Set @lmnmms_query = null;

            call LOADER_BUILD_CHECK_BASE_QUERY('LOADER_MATCHINGCODES', 'MATCHINGCODE_STATE', 'LOADER_STATUS_BATCH_NUM', batchnum, 'WARNINGCOUNT', 'WARNINGMSG', 'Matching status ID not found', null, null, @lmnmms_query);
            set @lmnmms_query = Concat(@lmnmms_query, ' And DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Null And MATCHINGCODE_STATE Is Not Null ');
            PREPARE stmt FROM @lmnmms_query;
            execute stmt			
                ;
            Deallocate prepare stmt;

    End ;
End //
Delimiter ;