Delimiter //
/**
 * Author:  armen
 * Created: 3 dec. 2019
 * recherche sur lesquels le profile des correspondant doit être appliqué
 * la règle doit avoir été déterminé au préalable : champ ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS > 0
 *
 */
Drop Procedure if Exists BUSINESS_ATTACHED_MEMBERS_INSERT_ACC_NOT_PROVIDED//
Create Procedure BUSINESS_ATTACHED_MEMBERS_INSERT_ACC_NOT_PROVIDED(
    in accAcredRefTpe Int unsigned
)
Begin
    SET @baminc_OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @baminc_OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

    baminc_main: Begin

        if accAcredRefTpe is null Then
            Set @baminc_accAcredRefTpe = (
                        Select
                            ID_ENTITIES_REF_TYPES
                        From ENTITIES_REF_TYPES
                        Where
                            ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                        Limit 1
                    )
                ;
        Else 
            Set @baminc_accAcredRefTpe = accAcredRefTpe;
        End if;

        baminc_prepare: Begin

            -- optimisation
            -- les valeurs null sont remplacé par des 0 pour que les indexes puissent être utilisés dans les requêtes
            Update TMP_ACCOUNTS_has_MEMBERS tar
            Set
#                 tar.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS = Coalesce(tar.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS, 0)
#                 , tar.ACCOUNTS_ID_ACCOUNTS = Coalesce(tar.ACCOUNTS_ID_ACCOUNTS, 0)
                tar.ENT_REF_DATA_ID_ENT_REF_DATAS_1 = Coalesce(tar.ENT_REF_DATA_ID_ENT_REF_DATAS_1, 0)
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_2 = Coalesce(tar.ENT_REF_DATA_ID_ENT_REF_DATAS_2, 0)
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_3 = Coalesce(tar.ENT_REF_DATA_ID_ENT_REF_DATAS_3, 0)
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_4 = Coalesce(tar.ENT_REF_DATA_ID_ENT_REF_DATAS_4, 0)
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_5 = Coalesce(tar.ENT_REF_DATA_ID_ENT_REF_DATAS_5, 0)
#                 , tar.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = Coalesce(
#                             tar.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
#                             , (
#                                 Select entaccmbr.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
#                                 From ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES entaccmbr
#
#                                 Where
#                                     tar.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS = entaccmbr.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES
# --                                 Order By
#                                 Limit 1
#                             )
#                             , 0
#                         )
            Where
                tar.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS Between 1 And 1000000
                And tar.ACCOUNTS_ID_ACCOUNTS = 0
            ;
#                 tar.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS Between 1 And 1000000
#                 And tar.ACCOUNTS_ID_ACCOUNTS Between 1 And 4000000

            -- select * from TMP_ACCOUNTS_has_MEMBERS limit 100

            -- table contenant les comptes et ref ana d'accred de comptes des entités en cours de traitement
            Drop Temporary Table If Exists TMP_ACCOUNTS_has_MEMBERS_ACC_DT;
            Create Temporary Table If Not Exists TMP_ACCOUNTS_has_MEMBERS_ACC_DT (
                        ACCOUNTS_ID_ACCOUNTS Int Unsigned
                        , ENTITIES_ID_ENTITIES Int Unsigned
                        , ENT_REF_DATA_ID_ENT_REF_DATAS_1 Int Unsigned
                        , ENT_REF_DATA_ID_ENT_REF_DATAS_2 Int Unsigned
                        , ENT_REF_DATA_ID_ENT_REF_DATAS_3 Int Unsigned
                        , ENT_REF_DATA_ID_ENT_REF_DATAS_4 Int Unsigned
                        , ENT_REF_DATA_ID_ENT_REF_DATAS_5 Int Unsigned

                        , Index(ACCOUNTS_ID_ACCOUNTS)
                        , Index (ENTITIES_ID_ENTITIES)
                        , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_1)
                        , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_2)
                        , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_3)
                        , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_4)
                        , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_5)
--                         , Index (
--                                 ENT_REF_DATA_ID_ENT_REF_DATAS_1
--                                 , ENT_REF_DATA_ID_ENT_REF_DATAS_2
--                                 , ENT_REF_DATA_ID_ENT_REF_DATAS_3
--                                 , ENT_REF_DATA_ID_ENT_REF_DATAS_4
--                                 , ENT_REF_DATA_ID_ENT_REF_DATAS_5
--                             )
                    )
                ;
            Insert Into TMP_ACCOUNTS_has_MEMBERS_ACC_DT (
                    ACCOUNTS_ID_ACCOUNTS
                    , ENTITIES_ID_ENTITIES
                )
    --         explain
            Select
                acc.ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS
                , acc.ENTITIES_ID_ENTITIES
            From ACCOUNTS acc

            Where 1 = 1 
                And acc.ENTITIES_ID_ENTITIES  In (
#                 And Exists (
                    Select tahm.ENTITIES_ID_ENTITIES
                    From TMP_ACCOUNTS_has_MEMBERS tahm
                    Where acc.ENTITIES_ID_ENTITIES = tahm.ENTITIES_ID_ENTITIES
                        And tahm.ACCOUNTS_ID_ACCOUNTS = 0 
                        And tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS between 1 and 100000
#                     Limit 1
                )
            ;

            -- obtention des id des ref d'accred des comptes
            -- utilisation de 0 à la place des null : (optimisation indexes)
            Update TMP_ACCOUNTS_has_MEMBERS_ACC_DT tar
            Set 
                tar.ENT_REF_DATA_ID_ENT_REF_DATAS_1 = Coalesce(
                        (
                            Select
                                serd1.ID_ENTITIES_REF_DATAS
                            From
                                ACC_REF_DATAS ard1
                                Inner Join ENTITIES_REF_DATAS serd1
                                    On ard1.ENT_REF_DATAS_ID_ENT_REF_DATAS = serd1.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF ser1
                                    On serd1.ENTITIES_REF_ID_ENTITIES_REF = ser1.ID_ENTITIES_REF
                            Where
                                tar.ACCOUNTS_ID_ACCOUNTS = ard1.ACCOUNTS_ID_ACCOUNTS
                                And ser1.ENT_REF_TYPE_ID_ENT_REF_TYPES = @baminc_accAcredRefTpe
                                And ser1.ENTITIES_REF_SEQUENCE = 1
                            Limit 1
                        )
                        , 0
                    )
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_2 = Coalesce(
                        (
                            Select
                                serd2.ID_ENTITIES_REF_DATAS
                            From
                                ACC_REF_DATAS ard2
                                Inner Join ENTITIES_REF_DATAS serd2
                                    On ard2.ENT_REF_DATAS_ID_ENT_REF_DATAS = serd2.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF ser2
                                    On serd2.ENTITIES_REF_ID_ENTITIES_REF = ser2.ID_ENTITIES_REF
                            Where
                                tar.ACCOUNTS_ID_ACCOUNTS = ard2.ACCOUNTS_ID_ACCOUNTS
                                And ser2.ENT_REF_TYPE_ID_ENT_REF_TYPES = @baminc_accAcredRefTpe
                                And ser2.ENTITIES_REF_SEQUENCE = 2
                            Limit 2
                        )
                        , 0
                    )
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_3 = Coalesce(
                        (
                            Select
                                serd3.ID_ENTITIES_REF_DATAS
                            From
                                ACC_REF_DATAS ard3
                                Inner Join ENTITIES_REF_DATAS serd3
                                    On ard3.ENT_REF_DATAS_ID_ENT_REF_DATAS = serd3.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF ser3
                                    On serd3.ENTITIES_REF_ID_ENTITIES_REF = ser3.ID_ENTITIES_REF
                            Where
                                tar.ACCOUNTS_ID_ACCOUNTS = ard3.ACCOUNTS_ID_ACCOUNTS
                                And ser3.ENT_REF_TYPE_ID_ENT_REF_TYPES = @baminc_accAcredRefTpe
                                And ser3.ENTITIES_REF_SEQUENCE = 3
                            Limit 3
                        )
                        , 0
                    )
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_4 = Coalesce(
                        (
                            Select
                                serd4.ID_ENTITIES_REF_DATAS
                            From
                                ACC_REF_DATAS ard4
                                Inner Join ENTITIES_REF_DATAS serd4
                                    On ard4.ENT_REF_DATAS_ID_ENT_REF_DATAS = serd4.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF ser4
                                    On serd4.ENTITIES_REF_ID_ENTITIES_REF = ser4.ID_ENTITIES_REF
                            Where
                                tar.ACCOUNTS_ID_ACCOUNTS = ard4.ACCOUNTS_ID_ACCOUNTS
                                And ser4.ENT_REF_TYPE_ID_ENT_REF_TYPES = @baminc_accAcredRefTpe
                                And ser4.ENTITIES_REF_SEQUENCE = 4
                            Limit 4
                        )
                        , 0
                    )
                , tar.ENT_REF_DATA_ID_ENT_REF_DATAS_5 = Coalesce(
                        (
                            Select
                                serd5.ID_ENTITIES_REF_DATAS
                            From
                                ACC_REF_DATAS ard5
                                Inner Join ENTITIES_REF_DATAS serd5
                                    On ard5.ENT_REF_DATAS_ID_ENT_REF_DATAS = serd5.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF ser5
                                    On serd5.ENTITIES_REF_ID_ENTITIES_REF = ser5.ID_ENTITIES_REF
                            Where
                                tar.ACCOUNTS_ID_ACCOUNTS = ard5.ACCOUNTS_ID_ACCOUNTS
                                And ser5.ENT_REF_TYPE_ID_ENT_REF_TYPES = @baminc_accAcredRefTpe
                                And ser5.ENTITIES_REF_SEQUENCE = 5
                            Limit 5
                        )
                        , 0
                    )
                ;
                -- is tous les champs de la table TMP_ACCOUNTS_has_MEMBERS_ACC_DT sont remplis
        end;
    
        baminc_mber_lst: begin
            -- table contenant la liste des membres à actualiser
            drop Temporary Table If exists TMP_ACCOUNTS_has_MEMBERS_MBR_LST;
            Create Temporary Table If Not exists TMP_ACCOUNTS_has_MEMBERS_MBR_LST (
                        ID_TMP_ACCOUNTS_has_MEMBERS_MBR_LST Int Unsigned Not Null AUTO_INCREMENT
                        , MEMBERS_ID_MEMBERS Int Unsigned
                        , ENTITIES_ID_ENTITIES Int Unsigned
                        , Primary Key (ID_TMP_ACCOUNTS_has_MEMBERS_MBR_LST)
                        , Index(MEMBERS_ID_MEMBERS)
                        , Index(ENTITIES_ID_ENTITIES)
                    )
                ;
            Insert Into TMP_ACCOUNTS_has_MEMBERS_MBR_LST (
                  MEMBERS_ID_MEMBERS, ENTITIES_ID_ENTITIES
              )
            Select Distinct MEMBERS_ID_MEMBERS, ENTITIES_ID_ENTITIES
            From TMP_ACCOUNTS_has_MEMBERS tahm
            Where tahm.ACCOUNTS_ID_ACCOUNTS = 0
                And tahm.MEMBERS_ID_MEMBERS between 1 and 100000
                And tahm.ENTITIES_ID_ENTITIES between 1 and 100000
                And tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS between 1 and 100000
            ;

        end ;
        baminc_mber: begin
            Set @baminc_nbMbre =Coalesce(
                        (
                            Select  ID_TMP_ACCOUNTS_has_MEMBERS_MBR_LST
                            From TMP_ACCOUNTS_has_MEMBERS_MBR_LST
                            Order By ID_TMP_ACCOUNTS_has_MEMBERS_MBR_LST Desc
                            Limit 1
                        )
                        , 0
                    )
                ;
            Set @baminc_size = Coalesce
                    (
                        (
                            Select MISCELLANEOUS_PARAMS_INT_VAL1
                            from MISCELLANEOUS_PARAMS
                            where MISCELLANEOUS_PARAMS_KEY = 'ATTACHED_MEMBERS'
                            Limit 1
                        )
                        , 1
                    )
                ;
            Set @baminc_ix = 1;
            -- Select @baminc_size, @baminc_nbMbre, @baminc_ix, @baminc_endIx
#
#             Declare baminc_id_members Int Unsigned;
#             Declare baminc_mber_cursor Cursor For
#
#                 Select distinct tahm.MEMBERS_ID_MEMBERS
#                 From TMP_ACCOUNTS_has_MEMBERS tahm
#                 Where tahm.ACCOUNTS_ID_ACCOUNTS = 0
#                     And tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS between 1 and 100000
#                     And tahm.MEMBERS_ID_MEMBERS between 1 and 100000
#                 -- debug
# --                     And tahm.MEMBERS_ID_MEMBERS In (678, 679, 600,  126, 102, 664, 301, 385, 107, 319, 125) -- les + importat snp
# --                 Limit 100
#                 ;
#
#             Open baminc_mber_cursor;
            baminc_mber_read: begin
#                 Declare Exit Handler for NOT FOUND
#                 Begin
#                     Close baminc_mber_cursor;
#                 End ;
                baminc_mber_loop: loop
#                     Fetch baminc_mber_cursor Into baminc_id_members;
#                     Set @baminc_id_members = baminc_id_members;
--                     Set @baminc_id_members = 1281 -- 88; -- debug
--                     Set @baminc_id_members = 37 -- 679; -- debug

                    if @baminc_ix > @baminc_nbMbre Then
                        leave baminc_mber_loop;
                    end if ;

                    Set @baminc_endIx = @baminc_ix + @baminc_size;

                    baminc_mber_trt: begin
                        set @baminc_idmbrprfl = null;
                        -- table avec la liste des règles applicables pour les membres du lots de traitement
                        Drop Temporary Table if exists TMP_ACCOUNTS_has_MEMBERS_MBR;
                        Create Temporary Table TMP_ACCOUNTS_has_MEMBERS_MBR (
                                    ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS Int Unsigned
                                    , DF_ACCESS_PROFILES_MNGT_LVL Smallint
                                    , ENTITIES_ID_ENTITIES Int Unsigned
                                    , MEMBERS_ID_MEMBERS Int Unsigned
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_1 Int Unsigned
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_2 Int Unsigned
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_3 Int Unsigned
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_4 Int Unsigned
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_5 Int Unsigned
--                                     , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES Int Unsigned
                                    , TMP_ACCOUNTS_has_MEMBERS_REF Varchar(40)

                                    , Index (ENTITIES_ID_ENTITIES)
                                    , Index (MEMBERS_ID_MEMBERS)
                                    , Index (DF_ACCESS_PROFILES_MNGT_LVL)
                                    , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_1)
                                    , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_2)
                                    , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_3)
                                    , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_4)
                                    , Index (ENT_REF_DATA_ID_ENT_REF_DATAS_5)
--                                     , Index (
--                                             ENT_REF_DATA_ID_ENT_REF_DATAS_1
--                                             , ENT_REF_DATA_ID_ENT_REF_DATAS_2
--                                             , ENT_REF_DATA_ID_ENT_REF_DATAS_3
--                                             , ENT_REF_DATA_ID_ENT_REF_DATAS_4
--                                             , ENT_REF_DATA_ID_ENT_REF_DATAS_5
--                                         )
                                )
                            ;

                        Insert Into TMP_ACCOUNTS_has_MEMBERS_MBR (
                                    ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS 
                                    , DF_ACCESS_PROFILES_MNGT_LVL 
                                    , TMP_ACCOUNTS_has_MEMBERS_REF
                                    , MEMBERS_ID_MEMBERS
                                    , ENTITIES_ID_ENTITIES
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_1
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_2 
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_3 
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_4 
                                    , ENT_REF_DATA_ID_ENT_REF_DATAS_5 
                            )
                        -- explain
                        Select
                                tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS 
                                , dfaccpf.DF_ACCESS_PROFILES_MNGT_LVL 
                                , tahm.TMP_ACCOUNTS_has_MEMBERS_REF
                                , tahm.MEMBERS_ID_MEMBERS
                                , tahm.ENTITIES_ID_ENTITIES
                                , tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_1
                                , tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_2 
                                , tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_3 
                                , tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_4 
                                , tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_5 
                        From TMP_ACCOUNTS_has_MEMBERS tahm
                            Inner Join ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES EAAMP
                                on tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS = EAAMP.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES
                            Inner Join DF_ACCESS_PROFILES dfaccpf
                                On EAAMP.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = dfaccpf.ID_DF_ACCESS_PROFILES
                            Inner Join TMP_ACCOUNTS_has_MEMBERS_MBR_LST tmblst
                                On tahm.MEMBERS_ID_MEMBERS = tmblst.MEMBERS_ID_MEMBERS
                        Where 1 = 1 
                            And tahm.ACCOUNTS_ID_ACCOUNTS = 0
                            -- ci-dessous pas nécessaire en raison de la jointure
#                             And tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS between 1 and 100000
#                             And tahm.MEMBERS_ID_MEMBERS = @baminc_id_members
                            And tmblst.ID_TMP_ACCOUNTS_has_MEMBERS_MBR_LST Between @baminc_ix And  @baminc_endIx
                        ;
                        
                        -- table contenant les comptes sélectionné par les règles d'accréditation  des membres en cours de traitement
                        Drop Temporary Table if exists TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD;
                        Create Temporary Table TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD (
                                    ACCOUNTS_ID_ACCOUNTS Int Unsigned
                                    , MEMBERS_ID_MEMBERS Int Unsigned
                                    , ENTITIES_ID_ENTITIES Int Unsigned
                                    , ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS Int Unsigned
                                    , TMP_ACCOUNTS_has_MEMBERS_REF Varchar(40)
                                    , Index(ACCOUNTS_ID_ACCOUNTS)
                                    , Index(ENTITIES_ID_ENTITIES)
                                )
                            ;
                        
                    
                    -- Insertion dans la table TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD
                    -- des comptes correspondants aux règles pour le membre

                        Insert Into TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD (
                                    ACCOUNTS_ID_ACCOUNTS, MEMBERS_ID_MEMBERS
                                    , ENTITIES_ID_ENTITIES
                                    , ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS
                            )
                        --   explain
                        Select
                            taccrfdt.ACCOUNTS_ID_ACCOUNTS
#                              , @baminc_id_members
                            , tahml.MEMBERS_ID_MEMBERS
                            , taccrfdt.ENTITIES_ID_ENTITIES
                            , Coalesce(@baminc_idmbrprfl, 0)
                        From 
                            TMP_ACCOUNTS_has_MEMBERS_ACC_DT taccrfdt
                            Inner Join TMP_ACCOUNTS_has_MEMBERS_MBR_LST tahml
                                On taccrfdt.ENTITIES_ID_ENTITIES = tahml.ENTITIES_ID_ENTITIES
                        Where 1 = 1 
                            And taccrfdt.ACCOUNTS_ID_ACCOUNTS Between 1 and 40000000
                            And tahml.ID_TMP_ACCOUNTS_has_MEMBERS_MBR_LST between @baminc_ix And @baminc_endIx
                            And
                            (
                                (
                                    Select
                                        (@baminc_idmbrprfl:=(tahm.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS))
                                    From
                                        TMP_ACCOUNTS_has_MEMBERS_MBR tahm
                                    Where 1 = 1
                                        And taccrfdt.ENTITIES_ID_ENTITIES = tahm.ENTITIES_ID_ENTITIES
#                                         And tahml.ENTITIES_ID_ENTITIES = tahm.ENTITIES_ID_ENTITIES
                                        And tahml.MEMBERS_ID_MEMBERS = tahm.MEMBERS_ID_MEMBERS
                                        And (
--                                                 tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_1 = taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_1
                                                tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_1 In (taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_1, 0)
                                                Or taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_1 = 0
                                            )
                                        And (
--                                                 tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_2 = taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_2
                                                tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_2 In (taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_2, 0)
                                                Or taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_2 = 0
                                            )
                                        And (
--                                                 tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_3  = taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_3
                                                tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_3 In (taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_3, 0)
                                                Or taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_3 = 0
                                            )
                                        And (
--                                                 tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_4 = taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_4
                                                tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_4 In (taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_4, 0)
                                                Or taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_4 = 0
                                            )
                                        And (
--                                                 tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_5 = taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_5
                                                tahm.ENT_REF_DATA_ID_ENT_REF_DATAS_5 In (taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_5, 0)
                                                Or taccrfdt.ENT_REF_DATA_ID_ENT_REF_DATAS_5 = 0
                                            )
                                        Order By tahm.DF_ACCESS_PROFILES_MNGT_LVL
                                        Limit 1
                                ) 
                            ) IS Not Null
                        ;

                        -- select count(*) from TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD
                        -- select * from TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD limit 1000

                        -- suppression des données pour lesquels on n'a pas trouvé de règles
                        Delete
                            taccnprvd
                            -- select *
                        From TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD taccnprvd
                        Where
                              taccnprvd.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS = 0
                              Or taccnprvd.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS is null
                        ;
                        -- suppression des données existantes pour ne pas les insérer en double
                        Delete
                            taccnprvd
                        -- select *
                        From TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD taccnprvd
                            Inner Join ACCOUNTS_has_MEMBERS ahm
                                On taccnprvd.ACCOUNTS_ID_ACCOUNTS = ahm.ACCOUNTS_ID_ACCOUNTS
                                And taccnprvd.MEMBERS_ID_MEMBERS = ahm.MEMBERS_ID_MEMBERS
                                And taccnprvd.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS = ahm.ENTT_ACCNTS_ATTCHD_MBRS_PRFLES_ID_ENTT_ACCNTS_ATTCHD_MBRS_PRFLES
                        ;


                            Update
                                TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD taccnprvd
                            Set TMP_ACCOUNTS_has_MEMBERS_REF = (
                                        Select thambr.TMP_ACCOUNTS_has_MEMBERS_REF
                                        From TMP_ACCOUNTS_has_MEMBERS_MBR thambr
                                        Where taccnprvd.ENTITIES_ID_ENTITIES = thambr.ENTITIES_ID_ENTITIES
                                            And taccnprvd.MEMBERS_ID_MEMBERS = thambr.MEMBERS_ID_MEMBERS
                                        Limit 1
                                    )
                            Where taccnprvd.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS between 1 and 100000
                            ;

--                             explain
                            Insert Into ACCOUNTS_has_MEMBERS (
                                        ACCOUNTS_ID_ACCOUNTS, MEMBERS_ID_MEMBERS, ACCOUNTS_has_MEMBERS_STATUS
                                        , ENTT_ACCNTS_ATTCHD_MBRS_PRFLES_ID_ENTT_ACCNTS_ATTCHD_MBRS_PRFLES
                                        , ACCOUNTS_has_MEMBERS_REF
                                    )
                            Select 
                            -- Distinct 
                                src.ACCOUNTS_ID_ACCOUNTS, src.MEMBERS_ID_MEMBERS, 0 MEMBERS_STATUS
                                , src.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS
                                , src.TMP_ACCOUNTS_has_MEMBERS_REF
                            From TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD src
#                             Where
#                                 src.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS is not null
#                                 src.ENT_AC_ATTC_MBRS_PRFLS_ID_ENT_AC_ATTC_MBRS_PRFLS between 1 and 100000
                            ;

                            -- select * from TMP_NEW_ACC_HAS_MBR_ACC_NOT_PRVD
                            -- Select schema(), (Select count(*) from TMP_ACCOUNTS_has_MEMBERS_ACC_DT)
                            -- select * from TMP_ACCOUNTS_has_MEMBERS
                    end ;

                    Set @baminc_ix = @baminc_endIx + 1;
                    -- debug
#                     leave baminc_mber_loop;


                end loop baminc_mber_loop;
            end ;
        end ;
    End;

    SET FOREIGN_KEY_CHECKS=@baminc_OLD_UNIQUE_CHECKS;
    SET UNIQUE_CHECKS=@baminc_OLD_FOREIGN_KEY_CHECKS;

End //
Delimiter ;