View: PA_CI_COMMENTS_V

Product:	PA - Projects
Description:	This view provides the attribute values from all comments from the table PA_CI_COMMENTS.
Implementation/DBA Data:	APPS.PA_CI_COMMENTS_V

View Text

SELECT COMMENTS.CI_COMMENT_ID
, COMMENTS.CI_ID
, A.CI_ACTION_ID
, COMMENTS.LAST_UPDATE_DATE UPDATE_DATE
, COMMENTOR.PARTY_NAME COMMENT_CREATED_BY_NAME
, COMMENTOR.PARTY_ID
, COMMENTOR.PARTY_NAME REQUESTOR_NAME
, COMMENTS.COMMENT_TEXT
, COMMENTS.TYPE_CODE MESSAGE_TYPE_CODE
, A.TYPE_CODE ACTION_TYPE_CODE
, PL.MEANING ACTION_TYPE
, A.STATUS_CODE
, A.CI_ACTION_NUMBER
, A2.CI_ACTION_ID REASSIGN_CI_ACTION_ID
, REASSIGNEE.PARTY_NAME REASSIGNEE_NAME
, COMMENTS.LAST_UPDATE_DATE
, PCI.PROJECT_ID
, COMMENTS.CREATED_BY
, A.SOURCE_CI_ACTION_ID
, ASSIGNEE.PARTY_NAME ASSIGNEE_NAME
, DECODE(COMMENTS.TYPE_CODE
, 'REQUESTOR'
, DECODE(A.STATUS_CODE
, 'CI_ACTION_OPEN'
, PA_CI_ACTIONS_UTIL.ACTION_WITH_REPLY(A.CI_ACTION_ID)
, 'Y')
, 'N') CHILD_EXISTS
FROM PA_CI_ACTIONS A
, HZ_PARTIES ASSIGNEE
, HZ_PARTIES REASSIGNEE
, HZ_PARTIES COMMENTOR
, PA_CI_COMMENTS COMMENTS
, PA_CI_ACTIONS A2
, PA_LOOKUPS PL
, PA_CONTROL_ITEMS PCI
WHERE COMMENTS.CI_ID = PCI.CI_ID
AND A.CI_ACTION_ID = COMMENTS.CI_ACTION_ID
AND A.CI_ID = COMMENTS.CI_ID
AND A2.SOURCE_CI_ACTION_ID(+) = COMMENTS.CI_ACTION_ID
AND A2.CI_ID(+) = COMMENTS.CI_ID
AND ASSIGNEE.PARTY_ID = A.ASSIGNED_TO
AND ASSIGNEE.PARTY_TYPE = 'PERSON'
AND COMMENTOR.PARTY_ID = PA_UTILS.GET_PARTY_ID(COMMENTS.CREATED_BY)
AND COMMENTOR.PARTY_TYPE = 'PERSON'
AND REASSIGNEE.PARTY_ID(+)= A2.ASSIGNED_TO
AND REASSIGNEE.PARTY_TYPE(+) = 'PERSON'
AND PL.LOOKUP_TYPE ='PA_CI_ACTION_TYPES'
AND PL.LOOKUP_CODE = A.TYPE_CODE UNION ALL SELECT COMMENTS.CI_COMMENT_ID
, COMMENTS.CI_ID
, COMMENTS.CI_ACTION_ID CI_ACTION_ID
, COMMENTS.LAST_UPDATE_DATE UPDATE_DATE
, COMMENTOR.PARTY_NAME COMMENT_CREATED_BY_NAME
, COMMENTOR.PARTY_ID
, COMMENTOR.PARTY_NAME REQUESTOR_NAME
, COMMENTS.COMMENT_TEXT
, COMMENTS.TYPE_CODE MESSAGE_TYPE_CODE
, NULL ACTION_TYPE_CODE
, NULL ACTION_TYPE
, NULL STATUS_CODE
, TO_NUMBER(NULL) CI_ACTION_NUMBER
, TO_NUMBER(NULL) REASSIGN_CI_ACTION_ID
, NULL REASSIGNEE_NAME
, COMMENTS.LAST_UPDATE_DATE
, PCI.PROJECT_ID
, COMMENTS.CREATED_BY
, TO_NUMBER(NULL) SOURCE_CI_ACTION_ID
, NULL ASSIGNEE_NAME
, 'N' CHILD_EXISTS
FROM PA_CI_COMMENTS COMMENTS
, HZ_PARTIES COMMENTOR
, PA_CONTROL_ITEMS PCI
WHERE COMMENTS.CI_ID = PCI.CI_ID
AND COMMENTS.CI_ACTION_ID IS NULL
AND (COMMENTS.TYPE_CODE = 'UNSOLICITED' OR COMMENTS.TYPE_CODE = 'RESOLUTION' OR COMMENTS.TYPE_CODE = 'CHANGE_STATUS' OR COMMENTS.TYPE_CODE = 'CHANGE_OWNER')
AND COMMENTOR.PARTY_ID = PA_UTILS.GET_PARTY_ID(COMMENTS.CREATED_BY)
AND COMMENTOR.PARTY_TYPE = 'PERSON'

Columns