+ <!-- Gets the all role functions and requires one parameters -->
+ <sql-query name="uploadAllRoleFunctions">
+ <return alias="allRoleFunctions"
+ class="org.openecomp.portalapp.portal.transport.BulkUploadRoleFunction" />
+ <![CDATA[
+ select fr.function_cd, fn.function_name from fn_role_function fr left outer join fn_function fn on fr.function_cd = fn.function_cd where role_id =:roleId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRoletoUpdateAAF">
+ <return alias="getRoletoUpdateAAF" class="org.openecomp.portalapp.portal.domain.EPRole" />
+ <![CDATA[
+ SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getBulkUserRoles">
+ <return alias="bulkUserRoles" class="org.openecomp.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 fu.org_user_id != 'su1234'
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getCentralizedApps">
+ <return alias="centralizedApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
+ <![CDATA[
+ select * from fn_app where auth_central = 'Y' and open = 'N';
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getUserRoles">
+ <return alias="getUserRolesList" class="org.openecomp.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
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getUserRolesForLeftMenu">
+ <return alias="getUserRolesListForLeftMenu" class="org.openecomp.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 or role.role_id = 999)
+ ;
+ ]]>
+ </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%'
+ ;
+ ]]>
+ </sql-query>
+
+ <sql-query name="getRequestIdsForApp">
+ <return alias="getRequestIdsForApp" class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
+ <![CDATA[
+ select * from ep_user_roles_request where app_id =:app_id
+ ;
+ ]]>
+ </sql-query>
+