[Home] [Help]
887:
888:
889: cursor check_curr_user_perms(l_role_id JTF_NUMBER_TABLE, l_user_name varchar2) IS
890: Select jtfperm.permission_name
891: FROM jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd, jtf_auth_role_perms jtfrp,
892: jtf_auth_permissions_b jtfperm
893: where jtfp1.principal_name = l_user_name
894: and jtfp1.is_user_flag=1
895: and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
1126:
1127:
1128: cursor get_users_w_curr_role(cv_role_id JTF_NUMBER_TABLE) IS
1129: select /*+ cardinality( t 10 ) */ jtfp2.principal_name, fndu.user_id
1130: from jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp2,jtf_auth_principals_b jtfp1,
1131: fnd_user fndu, jtf_rs_resource_extns jtfre, (Select column_value from table
1132: (CAST(cv_role_id AS JTF_NUMBER_TABLE))) t
1133: where jtfp1.jtf_auth_principal_id = t.column_value
1134: and jtfp1.is_user_flag = 0
1142:
1143: /** ??? Does this query need to handle inactive resources or partner contacts **/
1144: cursor get_usrs_perm_wo_curr_role(cv_role_id JTF_NUMBER_TABLE, cv_user_name JTF_VARCHAR2_TABLE_1000) IS
1145: select /*+ cardinality( t 10 ) */ jtfperm.permission_name, jtfp3.principal_name
1146: from jtf_auth_principals_b jtfp3, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm, jtf_auth_principal_maps jtfpm2, (Select column_value from table (CAST(cv_user_name AS JTF_VARCHAR2_TABLE_1000))) t
1147: where jtfp3.principal_name = t.column_value
1148: and jtfp3.is_user_flag = 1 and jtfp3.jtf_auth_principal_id=jtfpm2.jtf_auth_principal_id
1149: and jtfpm2.jtf_auth_parent_principal_id = jtfrp.JTF_AUTH_PRINCIPAL_ID
1150: and jtfpm2.jtf_auth_parent_principal_id NOT IN (Select * from table (CAST(cv_role_id AS JTF_NUMBER_TABLE)))
1406: where fndu.user_name = l_user_name;
1407:
1408: cursor get_user_permissions(l_user_name VARCHAR2) IS
1409: select jtfperm.permission_name
1410: from jtf_auth_principals_b jtfp1, jtf_auth_principal_maps jtfpm,
1411: jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1412: where jtfp1.principal_name = l_user_name
1413: and jtfp1.is_user_flag = 1
1414: and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_principal_id
1636:
1637:
1638: cursor get_user_permissions(cv_role_id NUMBER, cv_user_name JTF_VARCHAR2_TABLE_1000) IS
1639: select /*+ cardinality( t 10 ) */ jtfperm.permission_name, jtfp3.principal_name
1640: from jtf_auth_principals_b jtfp3, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm, jtf_auth_principal_maps jtfpm2, (Select column_value from table (CAST(cv_user_name AS JTF_VARCHAR2_TABLE_1000))) t
1641: where jtfp3.principal_name = t.column_value
1642: and jtfp3.is_user_flag = 1
1643: and jtfp3.jtf_auth_principal_id=jtfpm2.jtf_auth_principal_id
1644: and jtfpm2.jtf_auth_parent_principal_id = jtfrp.JTF_AUTH_PRINCIPAL_ID
1651:
1652:
1653: cursor get_users_w_curr_role(cv_role_id NUMBER) IS
1654: select jtfp2.principal_name, fndu.user_id
1655: from jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp2,jtf_auth_principals_b jtfp1,
1656: fnd_user fndu, jtf_rs_resource_extns jtfre
1657: where jtfp1.jtf_auth_principal_id = cv_role_id
1658: and jtfp1.is_user_flag = 0
1659: and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
1992: and (hzr.end_date is null or hzr.end_date > sysdate)
1993: and hzr.object_id = pvpp.partner_party_id
1994: and pvpp.status = 'A' and exists
1995: (
1996: select 1 from jtf_auth_principal_maps jtfpm,jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
1997: jtf_auth_principals_b jtfp2,jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1998: where jtfp1.principal_name = RES.user_name and jtfp1.is_user_flag=1
1999: and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
2000: and jtfp2.is_user_flag=0 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id