Delimiter //
/**
 * move accounts from one entity to another
 *
 * @author Armen
 */
Drop Procedure if Exists BUSINESS_ENTITIES_MOVE_ACCOUNTS//

Create Procedure BUSINESS_ENTITIES_MOVE_ACCOUNTS(
    In srcEntityId Int Unsigned
    , In tarEntityId Int Unsigned
    , In inTransaction smallint
)
Begin
    bema_Main: begin
        Declare bema_transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
            Begin
                if bema_transactionStarted  <> 0 then
                    rollback;
                    set bema_transactionStarted  = 0;
                End if;
                resignal;
            end ;
        if Coalesce(inTransaction, 0) = 0 Then
            Start Transaction;
            Set bema_transactionStarted  = 1;
        else
            Set bema_transactionStarted  = 0;
        end if;

        Set @bema_srcEntityId = srcEntityId;
        Set @bema_tarEntityId = tarEntityId;

        bema_accounts: Begin
            -- explain
            Update ACCOUNTS tar
                Inner JOIN DF_ACC_CATEG dac_src
                    On tar.DF_ACC_CATEG_ID_DF_ACC_CATEG = dac_src.ID_DF_ACCOUNTS_CATEGORIES
                Left Join ENTITIES_AGENCIES entag_src
                    On tar.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES = entag_src.ID_ENTITIES_AGENCIES
            Set tar.ENTITIES_ID_ENTITIES = @bema_tarEntityId
                , tar.DF_ACC_CATEG_ID_DF_ACC_CATEG = (
                    Select dac_tar.ID_DF_ACCOUNTS_CATEGORIES
                    From DF_ACC_CATEG dac_tar
                    Where dac_tar.ENTITIES_ID_ENTITIES = @bema_tarEntityId
                        And dac_tar.DF_ACC_CATEG_CODE = dac_src.DF_ACC_CATEG_CODE
                    limit 1
                )
                , tar.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES = (
                    Select entag_tar.ID_ENTITIES_AGENCIES
                    From ENTITIES_AGENCIES entag_tar
                    Where entag_tar.ENTITIES_ID_ENTITIES = @bema_tarEntityId
                        And entag_tar.ENTITIES_AGENCIES_CODE = entag_src.ENTITIES_AGENCIES_CODE
                    limit 1
                )
            Where tar.ENTITIES_ID_ENTITIES = @bema_srcEntityId
            ;

        end ;

        if(bema_transactionStarted <> 0 ) Then
            -- pour dev / debug
#             rollback ;
            commit;
            Set bema_transactionStarted = 0;
        End if;
        
        
    end ;
End //
Delimiter ;