Delimiter //
/**
	creation des tables temporaire TMP_MBRE_FULL_ACRED_DATA
	contient les liste des règles pour le membre
    Doit être lancé après BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_DELECATED_MEMBERS qui remplit la table TMP_MBRE_FULL_ACRED_DATA.
    la table TMP_MBRE_FULL_ACRED_DATA  ne concerne que le membre en cour de traitements
    aussi le paramètre du membre en cours de traitement n'est pas nécéssaire
*/
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_PREP_DFPROFILES_TEMP_TABLES//
 
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_PREP_DFPROFILES_TEMP_TABLES(
        In enttRefTpeAccAcrd Int Unsigned
    )
Begin
    bcmcpdtt_main: BEGIN
        bcmcpdtt_init: BEGIN
            if enttRefTpeAccAcrd Is Null Then
                set @bcmcpdtt_enttRefTpeAccAcrd = (select ID_ENTITIES_REF_TYPES  from ENTITIES_REF_TYPES where ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS');
            Else
                set @bcmcpdtt_enttRefTpeAccAcrd = enttRefTpeAccAcrd;
            End if;


            Drop Temporary Table if exists TMP_MBRE_FULL_ACRED_DATA;
            Create Temporary Table TMP_MBRE_FULL_ACRED_DATA (
                    ENTITIES_ID_ENTITIES Int Unsigned Not Null
                    , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES Int Unsigned Not Null
                    , DF_ACCESS_PROFILES_MNGT_LVL SmallInt Not Null default 0
    --             , DELECATED_ID_MEMBERS Int Unsigned Not Null
    --             , MEMBERS_ID_MEMBERS Int Unsigned Not Null
                    , ACCREDT1 Int Unsigned Not Null Default 0
                    , ACCREDT2 Int Unsigned Not Null Default 0
                    , ACCREDT3 Int Unsigned Not Null Default 0
                    , ACCREDT4 Int Unsigned Not Null Default 0
                    , ACCREDT5 Int Unsigned Not Null Default 0
                    , index (ENTITIES_ID_ENTITIES asc, DF_ACCESS_PROFILES_MNGT_LVL asc)
                    , index (DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES)
                    , index (ACCREDT1)
                    , index (ACCREDT2)
                    , index (ACCREDT3)
                    , index (ACCREDT4)
                    , index (ACCREDT5)
                )
            ;

        end ;

        bcmcpdtt_filltbl: BEGIN
            Insert Into TMP_MBRE_FULL_ACRED_DATA (
                   ENTITIES_ID_ENTITIES
                 , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES, DF_ACCESS_PROFILES_MNGT_LVL
                -- , DELECATED_ID_MEMBERS, MEMBERS_ID_MEMBERS
                 , ACCREDT1
                 , ACCREDT2
                 , ACCREDT3
                 , ACCREDT4
                 , ACCREDT5
            )
-- explain
            Select
                ehm.ENTITIES_ID_ENTITIES
                 , dap.ID_DF_ACCESS_PROFILES, dap.DF_ACCESS_PROFILES_MNGT_LVL
                 , Coalesce(
                    (
                        Select
                            ettmbrd1.ENTT_RF_DATAS_ID_ENTT_RF_DATAS
                        From
                            ENTT_has_MBRS_has_ENTT_RFDATAS ettmbrd1
                                Inner Join ENTITIES_REF_DATAS erdt1
                                           On ettmbrd1.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erdt1.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er1
                                           On erdt1.ENTITIES_REF_ID_ENTITIES_REF = er1.ID_ENTITIES_REF
                        Where
                                er1.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcpdtt_enttRefTpeAccAcrd
                          And er1.ENTITIES_REF_SEQUENCE = 1
                          And ettmbrd1.ENTT_has_MBRS_ID_ENTT_has_MBRS = ehm.ID_ENTITIES_has_MEMBERS
                        Limit 1
                    ), 0
                ) ACCRED1
                 , Coalesce(
                    (
                        Select
                            ettmbrd2.ENTT_RF_DATAS_ID_ENTT_RF_DATAS
                        From
                            ENTT_has_MBRS_has_ENTT_RFDATAS ettmbrd2
                                Inner Join ENTITIES_REF_DATAS erdt2
                                           On ettmbrd2.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erdt2.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er2
                                           On erdt2.ENTITIES_REF_ID_ENTITIES_REF = er2.ID_ENTITIES_REF
                        Where
                                er2.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcpdtt_enttRefTpeAccAcrd
                          And er2.ENTITIES_REF_SEQUENCE = 2
                          And ettmbrd2.ENTT_has_MBRS_ID_ENTT_has_MBRS = ehm.ID_ENTITIES_has_MEMBERS
                        Limit 1
                    ), 0
                ) ACCRED2
                 , Coalesce(
                    (
                        Select
                            ettmbrd3.ENTT_RF_DATAS_ID_ENTT_RF_DATAS
                        From
                            ENTT_has_MBRS_has_ENTT_RFDATAS ettmbrd3
                                Inner Join ENTITIES_REF_DATAS erdt3
                                           On ettmbrd3.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erdt3.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er3
                                           On erdt3.ENTITIES_REF_ID_ENTITIES_REF = er3.ID_ENTITIES_REF
                        Where
                                er3.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcpdtt_enttRefTpeAccAcrd
                          And er3.ENTITIES_REF_SEQUENCE = 3
                          And ettmbrd3.ENTT_has_MBRS_ID_ENTT_has_MBRS = ehm.ID_ENTITIES_has_MEMBERS
                        Limit 1
                    ), 0
                ) ACCRED3
                 , Coalesce(
                    (
                        Select
                            ettmbrd4.ENTT_RF_DATAS_ID_ENTT_RF_DATAS
                        From
                            ENTT_has_MBRS_has_ENTT_RFDATAS ettmbrd4
                                Inner Join ENTITIES_REF_DATAS erdt4
                                           On ettmbrd4.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erdt4.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er4
                                           On erdt4.ENTITIES_REF_ID_ENTITIES_REF = er4.ID_ENTITIES_REF
                        Where
                                er4.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcpdtt_enttRefTpeAccAcrd
                          And er4.ENTITIES_REF_SEQUENCE = 4
                          And ettmbrd4.ENTT_has_MBRS_ID_ENTT_has_MBRS = ehm.ID_ENTITIES_has_MEMBERS
                        Limit 1
                    ), 0
                ) ACCRED4
                 , Coalesce(
                    (
                        Select
                            ettmbrd5.ENTT_RF_DATAS_ID_ENTT_RF_DATAS
                        From
                            ENTT_has_MBRS_has_ENTT_RFDATAS ettmbrd5
                                Inner Join ENTITIES_REF_DATAS erdt5
                                           On ettmbrd5.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erdt5.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er5
                                           On erdt5.ENTITIES_REF_ID_ENTITIES_REF = er5.ID_ENTITIES_REF
                        Where
                                er5.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcpdtt_enttRefTpeAccAcrd
                          And er5.ENTITIES_REF_SEQUENCE = 5
                          And ettmbrd5.ENTT_has_MBRS_ID_ENTT_has_MBRS = ehm.ID_ENTITIES_has_MEMBERS
                        Limit 1
                    ), 0
                ) ACCRED5
            From ENTITIES_has_MEMBERS ehm
                 Inner Join TMP_MBR_DELECATED_MBR tmdm
                            On ehm.ID_ENTITIES_has_MEMBERS = tmdm.ID_ENTITIES_has_MEMBERS
                 Inner join DF_ACCESS_PROFILES dap
                            On ehm.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = dap.ID_DF_ACCESS_PROFILES
#             Where 1 = 1
            ;
        end ;

    end ;
end //
Delimiter ;

