+
+ <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>