Delimiter //
/**
* 
**/

Drop Procedure if exists LOADER_MEMBERSACCRED_NORMALIZE_MAP_ACCRED_RULES_EXISTS//

Create Procedure LOADER_MEMBERSACCRED_NORMALIZE_MAP_ACCRED_RULES_EXISTS (
    In batchnum int
)
Begin
    Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);
        Update 
            LOADER_STATUS sts
            Inner Join LOADER_MEMBERSACCRED src
                On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS
        Set
            src.ID_ENTITIES_has_MEMBERS = (
                    Select 
                        max(ehm.ID_ENTITIES_has_MEMBERS)
        --                 , erd1.ID_ENTITIES_REF_DATAS ID_ENTITIES_REF_DATAS_1, erd1.ENTITIES_REF_DATAS_CODE ENTITIES_REF_DATAS_CODE_1
        --                 , erd2.ID_ENTITIES_REF_DATAS ID_ENTITIES_REF_DATAS_2, erd2.ENTITIES_REF_DATAS_CODE ENTITIES_REF_DATAS_CODE_2
        --                 , erd3.ID_ENTITIES_REF_DATAS ID_ENTITIES_REF_DATAS_3, erd3.ENTITIES_REF_DATAS_CODE ENTITIES_REF_DATAS_CODE_3
        --                 , erd4.ID_ENTITIES_REF_DATAS ID_ENTITIES_REF_DATAS_4, erd4.ENTITIES_REF_DATAS_CODE ENTITIES_REF_DATAS_CODE_4
        --                 , erd5.ID_ENTITIES_REF_DATAS ID_ENTITIES_REF_DATAS_5, erd5.ENTITIES_REF_DATAS_CODE ENTITIES_REF_DATAS_CODE_5
                    From 
                        ENTITIES_has_MEMBERS ehm
                        Left Join (
                            ENTT_has_MBRS_has_ENTT_RFDATAS ehmhrd1
                                Inner Join ENTITIES_REF_DATAS erd1
                                    On ehmhrd1.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erd1.ID_ENTITIES_REF_DATAS
                                Inner Join (
                                    ENTITIES_REF erf1
                                    Inner Join ENTITIES_REF_TYPES ert1
                                        On erf1.ENT_REF_TYPE_ID_ENT_REF_TYPES = ert1.ID_ENTITIES_REF_TYPES
                                        And ert1.ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                                ) On erd1.ENTITIES_REF_ID_ENTITIES_REF = erf1.ID_ENTITIES_REF
                                    And erf1.ENTITIES_REF_SEQUENCE = 1
                        ) On ehm.ID_ENTITIES_has_MEMBERS = ehmhrd1.ENTT_has_MBRS_ID_ENTT_has_MBRS
                            And ehm.ENTITIES_ID_ENTITIES = erf1.ENTITIES_ID_ENTITIES
                        Left Join (
                            ENTT_has_MBRS_has_ENTT_RFDATAS ehmhrd2
                                Inner Join ENTITIES_REF_DATAS erd2
                                    On ehmhrd2.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erd2.ID_ENTITIES_REF_DATAS
                                Inner Join (
                                    ENTITIES_REF erf2
                                    Inner Join ENTITIES_REF_TYPES ert2
                                        On erf2.ENT_REF_TYPE_ID_ENT_REF_TYPES = ert2.ID_ENTITIES_REF_TYPES
                                        And ert2.ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                                ) On erd2.ENTITIES_REF_ID_ENTITIES_REF = erf2.ID_ENTITIES_REF
                                    And erf2.ENTITIES_REF_SEQUENCE = 2
                        ) On ehm.ID_ENTITIES_has_MEMBERS = ehmhrd2.ENTT_has_MBRS_ID_ENTT_has_MBRS
                            And ehm.ENTITIES_ID_ENTITIES = erf2.ENTITIES_ID_ENTITIES
                        Left Join (
                            ENTT_has_MBRS_has_ENTT_RFDATAS ehmhrd3
                                Inner Join ENTITIES_REF_DATAS erd3
                                    On ehmhrd3.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erd3.ID_ENTITIES_REF_DATAS
                                Inner Join (
                                    ENTITIES_REF erf3
                                    Inner Join ENTITIES_REF_TYPES ert3
                                        On erf3.ENT_REF_TYPE_ID_ENT_REF_TYPES = ert3.ID_ENTITIES_REF_TYPES
                                        And ert3.ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                                ) On erd3.ENTITIES_REF_ID_ENTITIES_REF = erf3.ID_ENTITIES_REF
                                    And erf3.ENTITIES_REF_SEQUENCE = 3
                        ) On ehm.ID_ENTITIES_has_MEMBERS = ehmhrd3.ENTT_has_MBRS_ID_ENTT_has_MBRS
                            And ehm.ENTITIES_ID_ENTITIES = erf3.ENTITIES_ID_ENTITIES
                        Left Join (
                            ENTT_has_MBRS_has_ENTT_RFDATAS ehmhrd4
                                Inner Join ENTITIES_REF_DATAS erd4
                                    On ehmhrd4.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erd4.ID_ENTITIES_REF_DATAS
                                Inner Join (
                                    ENTITIES_REF erf4
                                    Inner Join ENTITIES_REF_TYPES ert4
                                        On erf4.ENT_REF_TYPE_ID_ENT_REF_TYPES = ert4.ID_ENTITIES_REF_TYPES
                                        And ert4.ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                                ) On erd4.ENTITIES_REF_ID_ENTITIES_REF = erf4.ID_ENTITIES_REF
                                    And erf4.ENTITIES_REF_SEQUENCE = 4
                        ) On ehm.ID_ENTITIES_has_MEMBERS = ehmhrd4.ENTT_has_MBRS_ID_ENTT_has_MBRS
                            And ehm.ENTITIES_ID_ENTITIES = erf4.ENTITIES_ID_ENTITIES
                        Left Join (
                            ENTT_has_MBRS_has_ENTT_RFDATAS ehmhrd5
                                Inner Join ENTITIES_REF_DATAS erd5
                                    On ehmhrd5.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = erd5.ID_ENTITIES_REF_DATAS
                                Inner Join (
                                    ENTITIES_REF erf5
                                    Inner Join ENTITIES_REF_TYPES ert5
                                        On erf5.ENT_REF_TYPE_ID_ENT_REF_TYPES = ert5.ID_ENTITIES_REF_TYPES
                                        And ert5.ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                                ) On erd5.ENTITIES_REF_ID_ENTITIES_REF = erf5.ID_ENTITIES_REF
                                    And erf5.ENTITIES_REF_SEQUENCE = 5
                        ) On ehm.ID_ENTITIES_has_MEMBERS = ehmhrd5.ENTT_has_MBRS_ID_ENTT_has_MBRS
                            And ehm.ENTITIES_ID_ENTITIES = erf5.ENTITIES_ID_ENTITIES

                    where
                        ehm.ENTITIES_ID_ENTITIES = @idEntities
                        And src.ID_MEMBERS = ehm.MEMBERS_ID_MEMBERS
                        And src.ID_ENTITIES_REF_DATAS_1 = Coalesce(erd1.ID_ENTITIES_REF_DATAS, 0)
                        And src.ID_ENTITIES_REF_DATAS_1_NEW Is Null
                        And src.ID_ENTITIES_REF_DATAS_2 = Coalesce(erd2.ID_ENTITIES_REF_DATAS, 0)
                        And src.ID_ENTITIES_REF_DATAS_2_NEW Is Null
                        And src.ID_ENTITIES_REF_DATAS_3 = Coalesce(erd3.ID_ENTITIES_REF_DATAS, 0)
                        And src.ID_ENTITIES_REF_DATAS_2_NEW Is Null
                        And src.ID_ENTITIES_REF_DATAS_4 = Coalesce(erd4.ID_ENTITIES_REF_DATAS, 0)
                        And src.ID_ENTITIES_REF_DATAS_4_NEW Is Null
                        And src.ID_ENTITIES_REF_DATAS_5 = Coalesce(erd5.ID_ENTITIES_REF_DATAS, 0)
                        And src.ID_ENTITIES_REF_DATAS_5_NEW Is Null
--                         And src.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = ENTITIES_has_MEMBERS.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
            )
        Where sts.LOADER_STATUS_BATCH_NUM = batchnum
--             and LOADER_STATUS_ERRCOUNT = 1
        ;    
End //
Delimiter ;