View: PA_PROJ_REQUEST_DIRECTORY_V

Product:	PA - Projects
Description:	Added union clause to the view for ASN owner mapping enhancement.
Implementation/DBA Data:	APPS.PA_PROJ_REQUEST_DIRECTORY_V

View Text

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

Columns