VIEW: APPS.PV_PARTNER_PRIMARY_USERS_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )