Delimiter //
/**
 * re-création des donées de la table ENTITIES_REF
 * nécéssite que DF_INDEXES soit rempli
 * @ticket #4321
 * @author Armen
 */ 
Drop Procedure If Exists BUSINESS_COPY_REF_DATA //
Create Procedure BUSINESS_COPY_REF_DATA(
    In targetEntitieId int    
    , In useTransaction TinyInt
)
Begin
    Main: Begin
        Declare transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
        Begin
                if transactionStarted  <> 0 then
                        rollback;
                        set transactionStarted  = 0;
                End if;
                resignal;
        end ;
        Set useTransaction = Coalesce(useTransaction, 0) ;

        if useTransaction <> 0 Then
            start transaction;
            set transactionStarted  = 1;
        End If;

        clearTarget: Begin
            -- delete ENTITIES_REF_DATAS_LNG
            Delete ENTITIES_REF_DATAS_LNG 
            From ENTITIES_REF_DATAS_LNG 
                Inner Join ENTITIES_REF_DATAS 
                    On ENTITIES_REF_DATAS_LNG.ENTT_RF_DATAS_ID_ENT_RF_DATAS = ENTITIES_REF_DATAS.ID_ENTITIES_REF_DATAS
                Inner Join ENTITIES_REF
                    On ENTITIES_REF_DATAS.ENTITIES_REF_ID_ENTITIES_REF = ENTITIES_REF.ID_ENTITIES_REF
            Where 
                ENTITIES_REF.ENTITIES_ID_ENTITIES = targetEntitieId
            ;
            -- delete ENTITIES_REF_DATAS
            Delete ENTITIES_REF_DATAS 
            From ENTITIES_REF_DATAS 
                Inner Join ENTITIES_REF
                    On ENTITIES_REF_DATAS.ENTITIES_REF_ID_ENTITIES_REF = ENTITIES_REF.ID_ENTITIES_REF
            Where 
                ENTITIES_REF.ENTITIES_ID_ENTITIES = targetEntitieId
            ;

            -- del ENTITIES_REF_LNG
            Delete ENTITIES_REF_LNG
            From ENTITIES_REF_LNG 
                Inner Join ENTITIES_REF
                    On ENTITIES_REF_LNG.ENTITIES_REF_ID_ENTITIES_REF = ENTITIES_REF.ID_ENTITIES_REF
            Where 
                ENTITIES_REF.ENTITIES_ID_ENTITIES = targetEntitieId
            ;


            -- delete ENTITIES_REF
            Delete ENTITIES_REF 
            From ENTITIES_REF 
            Where 
                ENTITIES_REF.ENTITIES_ID_ENTITIES = targetEntitieId
            ;


        End;
        
        -- copying data
        copyBlock: Begin 
            Insert Into ENTITIES_REF (
				ENTITIES_ID_ENTITIES, ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE
            )
			Select 
				targetEntitieId ENTITIES_ID_ENTITIES
				, ERT.ID_ENTITIES_REF_TYPES ENT_REF_TYPE_ID_ENT_REF_TYPES
				, DFI.DF_INDEXES_EXTID
			From
				ENTITIES_REF_TYPES ERT
				Inner Join DF_INDEXES DFI
					On ERT.ENTITIES_REF_TYPES_MAX_SEQ>= DFI.DF_INDEXES_EXTID
					And DFI.DF_INDEXES_LNGID = 1
			Order By 1,2,3
            ;
        End;
        -- validating all
        if transactionStarted <> 0 Then
            commit ;
        End If;
    End ;


End //

Delimiter ;