SELECT PR.REQUEST_ID , RES.RESOURCE_ID , RES.SOURCE_NAME , MAP.DEST_VALUE_PK1 , MAP.DEST_VALUE , ASA.SALESFORCE_ROLE_CODE , ASL.MEANING , PR.SOURCE_ORG_ID , FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(PR.SOURCE_ORG_ID) , RES.SOURCE_JOB_TITLE , RES.SOURCE_PHONE , RES.SOURCE_EMAIL FROM PA_PROJECT_REQUESTS PR , AS_ACCESSES_ALL ASA , AS_LOOKUPS ASL , PA_OBJECT_RELATIONSHIPS OB , PA_VALUE_MAPS MAP , PA_VALUE_MAP_DEFS DEF , JTF_RS_RESOURCE_EXTNS RES WHERE OB.OBJECT_TYPE_FROM = 'AS_LEADS' AND OB.OBJECT_TYPE_TO = 'PA_PROJECT_REQUESTS' AND OB.RELATIONSHIP_TYPE = 'A' AND OB.RELATIONSHIP_SUBTYPE = 'PROJECT_REQUEST' AND OB.OBJECT_ID_FROM1 = ASA.LEAD_ID AND OB.OBJECT_ID_TO1 = PR.REQUEST_ID AND ASA.SALESFORCE_ID = RES.RESOURCE_ID AND ASA.SALESFORCE_ROLE_CODE = MAP.SOURCE_VALUE_PK1 AND MAP.VALUE_MAP_DEF_ID = DEF.VALUE_MAP_DEF_ID AND DEF.VALUE_MAP_DEF_TYPE = 'PERSON_ROLE_OPP_PROJ' AND PR.REQUEST_TYPE = DEF.DEF_SUBTYPE AND ASA.SALESFORCE_ROLE_CODE = ASL.LOOKUP_CODE AND ASL.LOOKUP_TYPE = 'ROLE_TYPE' AND ASA.SALESFORCE_ROLE_CODE IS NOT NULL /* ADDED FOR BUG 4029930.*/ UNION /* UNION ADDED FOR BUG 4029930. THIS IS NEEDED IN CASES WHERE ASN IS ENABLED AND SALESFORCE ROLE CODE IS NULL */ SELECT PR.REQUEST_ID , RES.RESOURCE_ID , RES.SOURCE_NAME , MAP.DEST_VALUE_PK1 , MAP.DEST_VALUE , ASA.SALESFORCE_ROLE_CODE , MAP.SOURCE_VALUE , PR.SOURCE_ORG_ID , FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(PR.SOURCE_ORG_ID) , RES.SOURCE_JOB_TITLE , RES.SOURCE_PHONE , RES.SOURCE_EMAIL FROM PA_PROJECT_REQUESTS PR , AS_ACCESSES_ALL ASA , PA_OBJECT_RELATIONSHIPS OB , PA_VALUE_MAPS MAP , PA_VALUE_MAP_DEFS DEF , JTF_RS_RESOURCE_EXTNS RES WHERE OB.OBJECT_TYPE_FROM = 'AS_LEADS' AND OB.OBJECT_TYPE_TO = 'PA_PROJECT_REQUESTS' AND OB.RELATIONSHIP_TYPE = 'A' AND OB.RELATIONSHIP_SUBTYPE = 'PROJECT_REQUEST' AND OB.OBJECT_ID_FROM1 = ASA.LEAD_ID AND OB.OBJECT_ID_TO1 = PR.REQUEST_ID AND ASA.SALESFORCE_ID = RES.RESOURCE_ID AND NVL(ASA.SALESFORCE_ROLE_CODE , 'OPPORTUNITY_OWNER') = MAP.SOURCE_VALUE_PK1 AND MAP.VALUE_MAP_DEF_ID = DEF.VALUE_MAP_DEF_ID AND DEF.VALUE_MAP_DEF_TYPE = 'PERSON_ROLE_OPP_PROJ' AND PR.REQUEST_TYPE = DEF.DEF_SUBTYPE AND ASA.SALESFORCE_ROLE_CODE IS NULL