VIEW: APPS.PA_PROJ_REQUEST_DIRECTORY_V

Source

View Text - Preformatted

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, ASA.OWNER_FLAG 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, ASA.OWNER_FLAG 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

View Text - HTML Formatted

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
, ASA.OWNER_FLAG
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
, ASA.OWNER_FLAG
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