Delimiter //
/**
 * Author:  armen
 * Created: 19 oct. 2022
 */

Drop Procedure If Exists BUSINESS_PROCESSES_UPDATE_MAX_LEVELS //
Create Procedure BUSINESS_PROCESSES_UPDATE_MAX_LEVELS(
    In idProcesses Int Unsigned
)
Begin
    bpuml_main: Begin

        
        set @bpuml_minId = Coalesce(idProcesses, 0);
        set @bpuml_maxId = Coalesce(idProcesses, 1e5);
        Update PROCESS_TREE_DESC ptd
            Inner Join PROCESS_LIST pl
                On ptd.ID_PROCESS_TREE_DESC = pl.PRCSSLST_ID_PROCESS_TREE_DESC
        Set ptd.PROCESS_TREE_MAX_DEPTH = (
                Select
                    max(pt.PRCSSTR_LEVEL)
                From PROCESS_TREE pt
                Where
                    pt.PRCSSTR_ID_PROCESS_TREE_DESC = ptd.ID_PROCESS_TREE_DESC
                Group by pt.PRCSSTR_ID_PROCESS_TREE_DESC 
                Limit 1
            )
        Where 
            pl.PRCSSLST_ID_PROCESSES between @bpuml_minId and @bpuml_maxId
        ;

       

    End ;


End //

Delimiter ;