/**
*****************************************************
ols_getEffectiveAreaAce

    get the effective area permission for a user regarding
    a certain secured object


    if area support is disabled on a gloabl level then
    the function returns  a value -1

    if the area suppot is enabled on global level but disabled
    on profile level (at least in one profile the arae support
    is diabeld where the user is a meber of ) the the value
    -1 is returned (Everyone profile is excluded - will
    not be checked)

    if area code is enabled in all profiles for a user then
    the effective area permission is returned.
    (the ORed area permissons  of the profiles are
    ANDed with the object permissons for the particular object)


    @param @obj_uuid uuid of the secured object

    @param    @usr_uuid uuid of the user
*****************************************************
*/
CREATE  procedure ols_sp_getEffectiveAreaAce
(    @obj_uuid  binary(16),
    @usr_uuid    binary(16)
)
as
begin
    declare @isEnabled        int;
    declare @eff_obj_area_ace    int;
    declare @eff_sp_area_ace    int;
    select @isEnabled = (select is_area_enabled from ols_v_user where user_uuid = @usr_uuid);
    if ( @isEnabled = 0 )
    begin
        -- area support is disabled, so give full access
        select -1 as eff_area_ace;
        return;
    end

    -- area support is enabled
    -- either at global level or for all security profiles where  the user is
    -- a member of ecluding the Everyone Profile
    -- now calculate the effectve area_ace

    select @eff_obj_area_ace = (select area_ace & area_mask  from ols_area_ace where object_def_uuid = @obj_uuid);
    if( @eff_obj_area_ace is null)
    begin
        -- error
        -- force to return an empty resultset
        select 0 as eff_area_ace;
        return;
    end;

    set @eff_sp_area_ace = (select [dbo].ols_fn_getAreaAceByUserUuid (@usr_uuid));
    if( @eff_sp_area_ace is null)
    begin
        -- error
        -- force to return an empty resultset
        select 0 as eff_area_ace;
        return;
    end;

    select (@eff_sp_area_ace & @eff_obj_area_ace) as eff_area_ace;
    return;

end
GO
GRANT EXECUTE ON  [dbo].[ols_sp_getEffectiveAreaAce] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ols_sp_getEffectiveAreaAce] TO [ca_itrm_group_ams]
GO