Delimiter $$
/**
 * Author:  armen
 * Created: 1 juil. 2020
 */
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_ACCOUNTS_REFS_LABEL $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_ACCOUNTS_REFS_LABEL ()
Begin
        Truncate Table TMP_V_PUBLIC_ACCOUNTS_REFS_LABEL_VIEW;
        Insert Into TMP_V_PUBLIC_ACCOUNTS_REFS_LABEL_VIEW(
            ACCOUNTS_ID_ACCOUNTS, ENTITIES_ID_ENTITIES
            , ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_ID_ENTITIES_REF
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS
            , ENTITIES_CODE, ACCOUNTS_REF
            , ENTITIES_REF_DATAS_CODE, ENTITIES_REF_DATAS_LNG_LABEL
            , ENTITIES_REF_TYPES_CODE, ENTITIES_REF_SEQUENCE
            , FULL_ACCOUNTS_REFS_SEQUENCE 
        )
        Select
            acc.ID_ACCOUNTS, acc.ENTITIES_ID_ENTITIES
            , vbard.ENT_REF_TYPE_ID_ENT_REF_TYPES, vbard.ENTITIES_REF_ID_ENTITIES_REF
            , vbard.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS
            , ent.ENTITIES_CODE, acc.ACCOUNTS_REF
            , vbard.ENTITIES_REF_DATAS_CODE, erdl.ENTITIES_REF_DATAS_LNG_LABEL
            , vbard.ENTITIES_REF_TYPES_CODE, vbard.ENTITIES_REF_SEQUENCE
            , Case vbard.ENTITIES_REF_TYPES_CODE
                    When 'ACCOUNTS' Then 100
                    When 'ACCRED_ACCOUNTS' Then 200
        --             When 'ENTRIES' Then 300
        --             When 'ACCRED_ENTRIES' Then 400
                    Else 100

                End 
                + vbard.ENTITIES_REF_SEQUENCE FULL_ACCOUNTS_REFS_SEQUENCE

        From
            ENTITIES ent
            Inner Join ACCOUNTS acc
                On ent.ID_ENTITIES = acc.ENTITIES_ID_ENTITIES
        --     Inner Join V_CONTACTS_ALL vca
        --         On acc.CONTACTS_ID_CONTACTS = vca.ID_CONTACTS
            Left Join ENTITIES_AGENCIES eag
                On acc.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES = eag.ID_ENTITIES_AGENCIES
            Inner Join V_PUBLIC_ACCOUNTS_REFS_DATA vbard
                On acc.ID_ACCOUNTS = vbard.ACCOUNTS_ID_ACCOUNTS
            Inner Join ENTITIES_REF_DATAS_LNG erdl
                On vbard.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS = erdl.ENTT_RF_DATAS_ID_ENT_RF_DATAS
                And erdl.DF_LANGUAGES_ID_DF_LANG = 1

    ;
End $$
Delimiter ;
