+ <sql-query name="getBulkUserRoles">
+ <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
+ <![CDATA[
+ select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
+ left outer join fn_role fr on fr.role_id = fur.role_id
+ left outer join fn_app fa on fa.app_id = fur.app_id
+ left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getBulkUsersForSingleRole">
+ <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
+ <![CDATA[
+ select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
+ left outer join fn_role fr on fr.role_id = fur.role_id
+ left outer join fn_app fa on fa.app_id = fur.app_id
+ left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getCentralizedApps">
+ <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
+ <![CDATA[
+ select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getUserRoles">
+ <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
+ <![CDATA[
+
+ SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID WHERE user.org_user_id =:org_user_id and userrole.app_id = 1
+ ;
+ ]]>
+ </sql-query>
+ <sql-query name="getRoleFunctionsOfUser">
+ <![CDATA[
+ select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
+ where fu.role_id = ep.role_id
+ and fu.app_id = ep.app_id
+ and fu.user_id =:userId and ep.app_id = 1
+ and ea.function_cd = ep.function_cd
+ union
+ select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
+ where role_id = 999
+ and app_r_f.function_cd = a_f.function_cd
+ and exists
+ (
+ select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
+ and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
+ );
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
+ <![CDATA[
+ select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
+ where fu.role_id = ep.role_id
+ and fu.app_id = ep.app_id
+ and fu.user_id =:userId
+ and ea.function_cd = ep.function_cd
+ and exists
+ (
+ select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
+ and ur.app_id = fa.app_id and fa.enabled = 'Y'
+ );
+ ]]>
+ </sql-query>
+
+ <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
+ <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
+ <![CDATA[
+ SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
+ inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
+ where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
+ UNION
+ SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
+ INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
+ INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
+ INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
+ INNER JOIN ep_app_role_function appfunction ON appfunction.app_id = app.app_id and appfunction.role_id=role.ROLE_ID and (appfunction.function_cd like '%APPROVER%')
+ WHERE user.user_id = :userId ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getApplicationsofTheUserContainsApprover">
+ <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
+ <![CDATA[
+ SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
+ INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
+ INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
+ INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
+ INNER JOIN ep_app_role_function appfunction ON appfunction.app_id = app.app_id and appfunction.role_id=role.ROLE_ID and (appfunction.function_cd like '%APPROVER%')
+ WHERE user.user_id = :userId ;
+ ]]>
+ </sql-query>
+
+
+
+ <sql-query name="getUserRolesForLeftMenu">
+ <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
+
+ <![CDATA[
+ SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole
+ INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
+ INNER JOIN fn_app app ON app.app_id= userrole.app_id
+ INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
+ WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and (app.enabled='Y' or app.app_id=1)
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRolesForLeftMenu">
+ <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
+
+ <![CDATA[
+ SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole
+ INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
+ INNER JOIN fn_app app ON app.app_id= userrole.app_id
+ INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
+ WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and ((app.enabled='Y' and auth_central='Y' ) or app.app_id=1)
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Gets all functions for an application along with global functions and requires single parameter -->
+ <sql-query name="getMenuFunctions">
+ <![CDATA[
+ select f.function_cd from ep_app_function f
+ where f.app_id =:appId
+ UNION
+ select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
+ and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRequestIdsForApp">
+ <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
+ <![CDATA[
+ select * from ep_user_roles_request where app_id =:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="ApplicationUserRoles">
+ <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
+ <![CDATA[
+ select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title, fu.login_id,
+ fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
+ from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
+ where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.role_id = fr.role_id and fr.app_id =:appId and fr.active_yn='Y' and epr.function_cd= epf.function_cd and epf.app_id=epr.app_id and fur.role_id=epr.role_id
+ union
+ select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title,
+ fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
+ from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
+ where a.role_id in (select b.role_id from ep_app_role_function b where b.role_app_id = 1 and b.app_id =:appId) and a.user_id =fu.user_id and a.role_id = fr.role_id and fr.active_yn='Y' and fu.active_yn='Y'
+ and earf.role_id = a.role_id and earf.function_cd = eaf.function_cd and earf.app_id = eaf.app_id and earf.role_app_id = 1 and fr.active_yn='Y' and fu.active_yn='Y';
+ ]]>
+ </sql-query>
+
+ <sql-query name="getCurrentAppRoleFunctions">
+ <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
+ <![CDATA[
+ select distinct fr.role_id, fr.role_name from fn_role fr, ep_app_function ef, ep_app_role_function epr where fr.role_id = epr.role_id and epr.function_cd = ef.function_cd
+ and ef.function_cd =:functionCd and epr.app_id =:appId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="deleteUserWidgetPlacement">
+ <![CDATA[
+ DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getCentralizedAppsOfUser">
+ <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
+ <![CDATA[
+
+ select distinct fa.app_id, fa.app_name
+ from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
+ Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
+ and fu.org_user_id = :userId and (fur.role_id = 999 or fur.role_id = 1) and fr.active_yn='Y' and ((fa.enabled = 'Y' and fa.auth_central='Y') or fa.app_id =1)
+ ;
+
+ ]]>
+ </sql-query>
+
+ <query name="getEPUserByOrgUserId">
+ <![CDATA[
+ FROM EPUser WHERE orgUserId = :org_user_id
+ ]]>
+ </query>
+
+ <query name="getEPUserByLoginId">
+ <![CDATA[
+ FROM EPUser WHERE loginId = :login_id
+ ]]>
+ </query>
+
+ <query name="getEPUserByLoginIdLoginPwd">
+ <![CDATA[
+ FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
+ ]]>
+ </query>
+
+ <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
+ <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
+ <![CDATA[
+ select distinct d.role_id, d.role_name , c.function_cd , e.function_name , d.active_yn , d.priority , c.app_id, c.role_app_id
+ from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
+ where b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and e.function_cd = c.function_cd and c.app_id= :appId and e.app_id=c.app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getGlobalRolesOfPortal">
+ <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
+ <![CDATA[
+ select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
+ <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
+ <![CDATA[
+ select * from ep_app_role_function where app_id =:appId and role_id =:roleId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getAppFunctionOnCodeAndAppId">
+ <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
+ <![CDATA[
+ select * from ep_app_function where app_id =:appId and function_cd =:functionCd
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getGlobalRoleForRequestedApp">
+ <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
+ <![CDATA[
+ select distinct d.role_id, d.role_name , c.function_cd , e.function_name , d.active_yn , d.priority , c.app_id, c.role_app_id
+ from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
+ where b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and e.function_cd = c.function_cd and c.app_id=:requestedAppId and c.role_id =:roleId and e.app_id = c.app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
+ <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
+ <![CDATA[
+ select distinct fr.role_id, fr.role_name, fr.active_yn, fr.priority, epr.function_cd, ep.function_name, ep.app_id, epr.role_app_id
+ from fn_role fr, ep_app_function ep, ep_app_role_function epr
+ where fr.role_id = epr.role_id and ep.function_cd = epr.function_cd and ep.app_id = epr.app_id and epr.app_id = :appId and epr.role_app_id = 1
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="updateMenuFunctionalAndRoles">
+ <![CDATA[
+ UPDATE fn_menu_functional m, fn_menu_functional_roles mr SET m.url='' WHERE m.menu_id=mr.menu_id AND mr.app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove any favorites associated with a menu item that is associated with this app -->
+ <sql-query name="removeAppFromMenuFavorites">
+ <![CDATA[
+ DELETE FROM fn_menu_favorites using fn_menu_favorites inner join fn_menu_functional_roles where fn_menu_functional_roles.app_id=:app_id AND fn_menu_functional_roles.menu_id=fn_menu_favorites.menu_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
+ <sql-query name="removeAppFromMenuFunctionalRoles">
+ <![CDATA[
+ DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
+ <sql-query name="removeAppFromEpAppRoleFunction">
+ <![CDATA[
+ DELETE FROM ep_app_role_function WHERE app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
+ <sql-query name="removeAppFromEpAppFunction">
+ <![CDATA[
+ DELETE FROM ep_app_function WHERE app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
+ <sql-query name="removeAppFromFnUserRole">
+ <![CDATA[
+ DELETE FROM fn_user_role WHERE app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove any widgets that is associated with this app-->
+ <sql-query name="removeAppFromEpWidgetCatalogRole">
+ <![CDATA[
+ DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove any notifications that is associated with this app-->
+ <sql-query name="removeAppFromEpRoleNotification">
+ <![CDATA[
+ DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
+ where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove all records from fn_role that is associated with this app-->
+ <sql-query name="removeAppFromFnRole">
+ <![CDATA[
+ DELETE FROM fn_role where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove app contact us entries that is associated with this app-->
+ <sql-query name="removeAppFromAppContactUs">
+ <![CDATA[
+ DELETE FROM fn_app_contact_us where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove app personalization entries that is associated with this app-->
+ <sql-query name="removeAppFromEpPersUserAppSel">
+ <![CDATA[
+ DELETE FROM fn_pers_user_app_sel where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove app personalization sorting entries that is associated with this app-->
+ <sql-query name="removeAppFromEpPersUserAppManSort">
+ <![CDATA[
+ DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove rows from user role request table entries that is associated with this app-->
+ <sql-query name="removeAppFromEpUserRolesRequest">
+ <![CDATA[
+ DELETE FROM ep_user_roles_request where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove rows from web analytics table entries that is associated with this app-->
+ <sql-query name="removeAppFromEpWebAnalytics">
+ <![CDATA[
+ DELETE FROM ep_web_analytics_source where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+ <!-- Remove row from fn app table -->
+ <sql-query name="removeAppFromFnApp">
+ <![CDATA[
+ DELETE FROM fn_app where app_id=:app_id
+ ;
+ ]]>
+ </sql-query>
+
+
+ <query name="getBasicauthAccount">
+ select id,password from BasicAuthCredentials
+ </query>
+
+ <query name="getMicroserviceInfo">
+ select id,password from MicroserviceData
+ </query>
+
+ <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
+ <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
+ <![CDATA[
+ select * from fn_user_role where user_id= :userId
+ and role_id= :roleId
+ and app_id= :appId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="userAppGlobalRoles">
+ <return alias="userAppGlobalRoles"
+ class="org.onap.portalapp.portal.domain.EPRole" />
+ <![CDATA[
+ select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
+ from fn_user_role a, fn_role fr, fn_user fu
+ where a.role_id in (select b.role_id from ep_app_role_function b where b.role_app_id = 1 and b.app_id =:appId) and a.user_id =fu.user_id and a.role_id = fr.role_id and fr.active_yn='Y' and fu.active_yn='Y' and fu.user_id =:userId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getAllCentralizedAppsRoles">
+ <return alias="allCentralizedAppsRoles"
+ class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
+ <![CDATA[
+ select distinct fa.app_id, fa.auth_namespace, fr.role_name, fr.role_id from fn_app fa, fn_role fr where fa.auth_central = 'Y' and fa.auth_namespace is not null
+ and (fa.app_id = fr.app_id or COALESCE(fr.app_id,1) = fa.app_id) and fr.active_yn = 'Y' order by fa.app_id ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getUserCentralizedAppRoles">
+ <return alias="userCentralizedAppRoles"
+ class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
+ <![CDATA[
+ select distinct fur.app_id, fa.auth_namespace, fr.role_name, fur.role_id from fn_user_role fur, fn_app fa, fn_role fr, fn_user fu
+ where fa.app_id = fur.app_id
+ and fr.role_id = fur.role_id
+ and fu.user_id = fur.user_id
+ and fu.ORG_USER_ID = :orgUserId
+ and fa.auth_namespace is not null
+ and fr.active_yn = 'Y'
+ order by role_id;
+ ]]>
+ </sql-query>
+ <sql-query name="getAprroverRoleFunctionsOfUser">
+ <![CDATA[
+ select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
+ where fu.role_id = ep.role_id
+ and fu.app_id = ep.app_id
+ and fu.user_id =:userId
+ and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
+ and exists
+ (
+ select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
+ and ur.app_id = fa.app_id and fa.enabled = 'Y'
+ );
+ ]]>
+</sql-query>
+<sql-query name="getUserApproverRoles">
+ <![CDATA[
+ select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
+ where fu.role_id = ep.role_id
+ and fu.app_id = ep.app_id
+ and fu.user_id = :userId
+ and fu.role_id = fr.role_id and fr.active_yn='Y'
+ and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
+ and exists
+ (
+ select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
+ and ur.app_id = fa.app_id and fa.enabled = 'Y'
+ );
+ ]]>
+</sql-query>
+<sql-query name="getAdminAppsForTheUser">
+ <![CDATA[
+ select fa.app_id from fn_user_role ur,fn_app fa where ur.user_id =:userId and ur.app_id=fa.app_id and ur.role_id= 999 and (fa.enabled = 'Y' || fa.app_id=1)
+
+
+ ]]>
+</sql-query>
+
+
+<sql-query name="getUserRoleOnUserIdAndAppId">
+ <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
+ <![CDATA[
+
+ select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id FROM fn_role fr, fn_user_role fur WHERE fr.role_id = fur.role_id AND fur.user_id= :userId AND fur.app_id = :appId AND fr.active_yn='Y'
+
+ ]]>
+ </sql-query>
+
+ <sql-query name="getAllAdminAppsofTheUser">
+ <![CDATA[
+ select fa.app_id from fn_user_role ur,fn_app fa where ur.user_id =:userId and ur.app_id=fa.app_id and ur.role_id= 999
+
+ ]]>
+ </sql-query>
+ <sql-query name="getAllAppsFunctionsOfUser">
+ <![CDATA[
+ select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_app fa , fn_role fr
+ where fu.role_id = ep.role_id
+ and fu.app_id = ep.app_id
+ and fu.user_id =:userId
+ and ea.function_cd = ep.function_cd
+ and ((fu.app_id = fa.app_id and fa.enabled = 'Y' ) or (fa.app_id = 1))
+ and fr.role_id = fu.role_id and fr.active_yn='Y'
+ union
+ select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
+ where role_id = 999
+ and app_r_f.function_cd = a_f.function_cd
+ and exists
+ (
+ select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
+ and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
+ );
+ ]]>
+ </sql-query>
+ <sql-query name="updateFnUser">
+ <![CDATA[
+ UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
+ ]]>
+ </sql-query>