SELECT JTFRE.USER_ID USER_ID , JTFRE.USER_NAME USER_NAME , PVPP.PARTNER_ID PARTNER_ID , JTFRE.RESOURCE_ID RESOURCE_ID FROM PV_PARTNER_PROFILES PVPP , HZ_RELATIONSHIPS HZR , JTF_RS_RESOURCE_EXTNS JTFRE , FND_USER FNDU WHERE PVPP.PARTNER_PARTY_ID = HZR.OBJECT_ID AND PVPP.STATUS = 'A' AND HZR.RELATIONSHIP_CODE = 'EMPLOYEE_OF' AND HZR.SUBJECT_TABLE_NAME ='HZ_PARTIES' AND HZR.OBJECT_TABLE_NAME ='HZ_PARTIES' AND HZR.DIRECTIONAL_FLAG = 'F' AND HZR.START_DATE <= SYSDATE AND (HZR.END_DATE IS NULL OR HZR.END_DATE > SYSDATE) AND HZR.STATUS = 'A' AND HZR.PARTY_ID = JTFRE.SOURCE_ID AND JTFRE.CATEGORY = 'PARTY' AND FNDU.USER_ID = JTFRE.USER_ID AND FNDU.START_DATE <= SYSDATE AND (FNDU.END_DATE IS NULL OR FNDU.END_DATE > SYSDATE) AND EXISTS (SELECT 1 FROM JTF_AUTH_PRINCIPAL_MAPS JTFPM , JTF_AUTH_PRINCIPALS_B JTFP1 , JTF_AUTH_DOMAINS_B JTFD , JTF_AUTH_PRINCIPALS_B JTFP2 , JTF_AUTH_ROLE_PERMS JTFRP , JTF_AUTH_PERMISSIONS_B JTFPERM WHERE JTFP1.IS_USER_FLAG=1 AND JTFP1.JTF_AUTH_PRINCIPAL_ID=JTFPM.JTF_AUTH_PRINCIPAL_ID AND JTFP2.JTF_AUTH_PRINCIPAL_ID=JTFPM.JTF_AUTH_PARENT_PRINCIPAL_ID AND JTFP2.IS_USER_FLAG=0 AND JTFP2.JTF_AUTH_PRINCIPAL_ID=JTFRP.JTF_AUTH_PRINCIPAL_ID AND JTFRP.POSITIVE_FLAG = 1 AND JTFRP.JTF_AUTH_PERMISSION_ID = JTFPERM.JTF_AUTH_PERMISSION_ID AND JTFPERM.PERMISSION_NAME IN ('PV_PARTNER_USER' , 'IBE_INT_PRIMARY_USER') AND JTFD.JTF_AUTH_DOMAIN_ID = JTFPM.JTF_AUTH_DOMAIN_ID AND JTFD.DOMAIN_NAME = 'CRM_DOMAIN' AND JTFP1.PRINCIPAL_NAME = JTFRE.USER_NAME GROUP BY JTFP1.PRINCIPAL_NAME HAVING COUNT (DISTINCT DECODE(JTFPERM.PERMISSION_NAME , 'IBE_INT_PRIMARY_USER' , NULL , JTFPERM.PERMISSION_NAME) ) = 1 AND COUNT(DISTINCT DECODE(JTFPERM.PERMISSION_NAME , 'IBE_INT_PRIMARY_USER' , JTFPERM.PERMISSION_NAME , NULL )) =1 )