/**
******************************************************
* set the area mask  of the ols_area_def table
*    the area mask defiend whcih area bit is valid
*
* @param @obj_uuid if null all entries will be updated
*                if set then onyl the row is set where the
*                object_def_uuid  matches
*
*/

CREATE procedure ols_sp_applyAreaMask
(    @obj_uuid binary(16)
)
as
begin
    declare @area_mask integer;
    declare @aid       integer;
    declare @abit       integer;

    set nocount on;
    set @area_mask = 0;

    DECLARE amcur1 CURSOR
        FOR select area_id  from ols_area_def;

    OPEN amcur1
    FETCH NEXT FROM amcur1 INTO @aid    -- get first

    WHILE @@FETCH_STATUS = 0
    BEGIN
        if ( @aid > 31 )
        begin
            raiserror ('Error 9019: Area_id in ols_area_def greater than 31.', 16, 1);
        end;
        if ( @aid = 31 )
            set @abit    = 0x80000000;
        else
            select @abit = (power(2,@aid));
        set @area_mask = @area_mask | @abit;

        FETCH NEXT FROM amcur1 INTO @aid;     -- get next

    END

    CLOSE amcur1
    DEALLOCATE amcur1

    if (@obj_uuid is null)
    begin
        -- update all rows of the table
        update ols_area_ace set area_mask = @area_mask;
    end
    else
    begin
        -- update a single object only
        update ols_area_ace set area_mask = @area_mask
            where object_def_uuid = @obj_uuid;
    end;
end;
GO
GRANT EXECUTE ON  [dbo].[ols_sp_applyAreaMask] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ols_sp_applyAreaMask] TO [ca_itrm_group_ams]
GO