View: PV_PARTNER_PRIMARY_USERS_V

Product:	PV - Partner Management
Description:
Implementation/DBA Data:	APPS.PV_PARTNER_PRIMARY_USERS_V

View Text

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 )

Columns