/* End of lines added to convert to online lock */

/*
*****************************************************
procedure for calcuating the area permission

    @param    @object_uuid uuid of the objects to be updated
    @param    @_object_type    object type
    
    @return     NULL in case it is not a member of a group
            otherwise area permission based on the memberships
*/

CREATE function [dbo].ols_fn_getGroupAreaPerm(
            @object_uuid    binary(16),       /* old.object_def_uuid */
            @_object_type     integer           /* old.object_type */

)
returns integer            -- area permission
as
begin
    
    declare @gcnt integer;

    
    set @gcnt = 0;
    select @gcnt = ( select count(*) cnt from ca_group_member m, ca_group_def g
                            where m.member_uuid = @object_uuid
                            and m.group_uuid = g.group_uuid
                            and g.security_group = 1);
    if( @gcnt > 0 )
    begin
        -- the object is a member of a group, we need to calculate the area_ace
        declare @object_ace integer;
        declare @parent_ace integer;
            
        set @parent_ace = 0;    -- set default value
        set @object_ace = 0;    -- set default value

        declare parent_ace_list cursor local for
        select area_ace
        from ols_area_ace
        where object_def_uuid in
        (select g.group_uuid from ca_group_member m , ca_group_def g
        where m.member_uuid = @object_uuid
                and m.group_uuid = g.group_uuid
                and g.security_group = 1)

        
        open parent_ace_list
        fetch from parent_ace_list into @parent_ace --get first ace
        while @@fetch_status = 0
        begin
            
            select @object_ace = (@object_ace | @parent_ace)
            
            fetch from parent_ace_list into @parent_ace  --get next ace
        end
        close parent_ace_list
        deallocate parent_ace_list
        return @object_ace;        
        
    end;
    
    -- the object is NOT member of a group
    return NULL;
    
end
GO
GRANT EXECUTE ON  [dbo].[ols_fn_getGroupAreaPerm] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ols_fn_getGroupAreaPerm] TO [ca_itrm_group_ams]
GO