SELECT
PTP.PARTY_TAX_PROFILE_ID ,
PTP.PARTY_TYPE_CODE ,
PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
WHERE EXISTS (SELECT NULL FROM HZ_PARTIES HZP
WHERE PTP.PARTY_TYPE_CODE = 'FIRST_PARTY'
AND PTP.PARTY_ID = HZP.PARTY_ID)
OR (PTP.PARTY_TYPE_CODE = 'OU'
AND
EXISTS (SELECT NULL FROM MO_GLOB_ORG_ACCESS_TMP OA
WHERE OA.ORGANIZATION_ID = PTP.PARTY_ID
)
)
OR EXISTS (SELECT NULL FROM FND_LOOKUPS LU
WHERE PTP.PARTY_TYPE_CODE = 'GCO'
AND PTP.PARTY_TYPE_CODE = LU.LOOKUP_CODE
AND LU.LOOKUP_TYPE = 'ZX_PTP_PARTY_TYPE'
AND FND_PROFILE.VALUE ( 'ZX_GCO_WRITE_ACCESS' ) = 'Y')