use clinical_area
go

ALTER PROCEDURE [dbo].[spGeneric_SaveAccessRights]
	@UserID as varchar(15),
	@RevenueID as varchar(5),
	@RevenueName as varchar(100),
	@RevTransSetting as varchar(1),
	@Status as varchar(1)

AS


if exists(Select * from Clinical_Area..tbGenericAccess where UserID =  @UserID and revenueID = @RevenueID)
	begin
	
		Update Clinical_Area..tbGenericAccess
			set Status = @Status,
				RevTransSetting = @RevTransSetting
		where UserID =  @UserID and
			RevenueID = @RevenueID
	end
else
	begin
		Insert into Clinical_Area..tbGenericAccess  (UserID, RevenueID, RevenueName, RevTransSetting, Status)
			values(@UserID, @RevenueID, @RevenueName, @RevTransSetting, @Status)
	end


go



ALTER Procedure [dbo].[spGeneric_GetRevenueAccess]
--declare
@userID as varchar(10)

--set @userid = '28'
as

		
	select '', revenueid as MenuID, RevenueName as Menu,
	case when RevTransSetting = 'D' then 'Default'
		 when RevTransSetting = 'I' then 'Immediate'
		 when RevTransSetting = 'Y' then 'Can Access'
		 when RevTransSetting = 'N' then 'No Access' end as Setting,
	case when RevTransSetting = 'N' then '0' else '1' End as Status
		from Clinical_Area..tbGenericAccess
	where userid = @userid
	
union all

	select '', revenueid as MenuID, ItemName as Menu,  'No Access' as Setting,
	  '0' as Status
		from build_file..tbcorevenuecode
	where GenericActive = 'Y' --and rwith = 'Y' and locationid = ''
	and revenueid not in(Select revenueid from tbGenericAccess where userid = @userid)


go