|
SELECT REL.SUBJECT_ID AS OBJECT_ID,SH.ACCESS_GROUP_ID,'PartnerContact' AS OBJECT_TYPE ,SH.ACCESS_LEVEL_CODE,SH.OBJECT_NUMBER,SH.ACCESS_GROUP_NUMBER,'CUSTOM' AS ACCESS_GROUP_TYPE_CODE FROM HZ_RELATIONSHIPS REL,ZCA_OBJECT_SHARE SH
WHERE REL.STATUS = 'A'
AND SYSDATE BETWEEN REL.START_DATE AND REL.END_DATE
AND SH.OBJECT_ID = REL.OBJECT_ID
AND SH.OBJECT_TYPE = 'Partner'
AND REL.SUBJECT_TYPE = 'PERSON'
AND SH.ACCESS_LEVEL_CODE IN ('READ','UPDATE','DELETE','FULL')
AND EXISTS (SELECT 1 FROM HZ_PARTY_USG_ASSIGNMENTS PUA WHERE PUA.OWNER_TABLE_ID = REL.RELATIONSHIP_ID
AND PUA.OWNER_TABLE_NAME = 'HZ_RELATIONSHIPS'
AND PUA.PARTY_USAGE_CODE = 'PARTNER_CONTACT'
AND PUA.PARTY_ID = REL.SUBJECT_ID
AND PUA.STATUS_FLAG = 'A'
AND SYSDATE BETWEEN PUA.EFFECTIVE_START_DATE AND PUA.EFFECTIVE_END_DATE)
|