USP_DATAFORMTEMPLATE_VIEW_APPLIATIONUSERDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_APPLIATIONUSERDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ASSIGNEDROLES xml = null output,
    @INVITATIONSTATUSCODE tinyint = null output,
    @INACTIVE bit = null output,
    @INVITATIONDATE date = null output,
    @INACTIVEDATE date = null output,
    @INACTIVEDETAILS nvarchar(300) = null output,
    @EMAILADDRESS nvarchar(100) = null output,
    @ISSYSADMIN bit = null output,
    @ISPROXYUSER bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1,
        @ASSIGNEDROLES = (
            select
                SYSTEMROLE.NAME
            from dbo.SYSTEMROLEAPPUSER
            inner join dbo.SYSTEMROLE
                on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID
            where SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
            order by SYSTEMROLE.NAME
            for xml raw('ITEM'),type,elements,root('ASSIGNEDROLES'),BINARY BASE64
        ),
        @INVITATIONSTATUSCODE = isnull(APPUSERTHIRDPARTYAUTH.INVITATIONSTATUSCODE, 0),
        @INACTIVE = case when isnull(APPUSERTHIRDPARTYAUTH.INACTIVE, 0) = 1 or APPUSER.ISACTIVE = 0 then 1
                         else 0 end,
        @INVITATIONDATE = APPUSERTHIRDPARTYAUTH.INVITATIONDATE,
        @INACTIVEDATE = APPUSERTHIRDPARTYAUTH.INACTIVEDATE,
        @INACTIVEDETAILS = isnull(APPUSERTHIRDPARTYAUTH.INACTIVEDETAILS, ''),
        @EMAILADDRESS = APPUSER.EMAILADDRESS,
        @ISSYSADMIN = APPUSER.ISSYSADMIN,
        @ISPROXYUSER = isnull(APPUSER.ISPROXYUSER, 0)
    from dbo.APPUSER
    left outer join dbo.APPUSERTHIRDPARTYAUTH
        on APPUSER.ID = APPUSERTHIRDPARTYAUTH.ID
    where APPUSER.ID = @ID;

    return 0;