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