[Home] [Help]
| Product: | PA - Projects |
|---|---|
| Description: | This view lists the action log in the chronological format. In the view, the requests and the responses will have separate entries. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT COMMENTS.CI_COMMENT_ID
, COMMENTS.CI_ID
, A.CI_ACTION_ID
, TO_CHAR(COMMENTS.LAST_UPDATE_DATE) UPDATE_DATE
, TO_CHAR(COMMENTS.LAST_UPDATE_DATE
, 'HH12:MIAM')UPDATE_TIME
, COMMENTOR.PARTY_NAME COMMENT_CREATED_BY_NAME
, COMMENTOR.PARTY_ID
, REQUESTOR.PARTY_NAME REQUESTOR_NAME
, A.ASSIGNED_TO
, ASSIGNEE.PARTY_NAME ASSIGNEE_NAME
, COMMENTS.COMMENT_TEXT
, COMMENTS.TYPE_CODE MESSAGE_TYPE_CODE
, A.TYPE_CODE ACTION_TYPE_CODE
, PL.MEANING ACTION_TYPE
, A.STATUS_CODE
, PPS.PROJECT_STATUS_NAME STATUS_CODE_MEANING
, FL.MEANING SIGN_OFF_REQUIRED_FLAG_MEANING
, A.SIGN_OFF_REQUIRED_FLAG
, FL2.MEANING SIGN_OFF_FLAG_MEANING
, A.SIGN_OFF_FLAG
, TO_CHAR(A.DATE_REQUIRED)
, TO_CHAR(A.DATE_CLOSED)
, A.CI_ACTION_NUMBER
, A2.CI_ACTION_ID REASSIGN_CI_ACTION_ID
, A2.CI_ACTION_NUMBER REASSIGN_CI_ACTION_NUMBER
, A2.ASSIGNED_TO REASSIGNED_TO
, REASSIGNEE.PARTY_NAME REASSIGNEE_NAME
, COMMENTS.LAST_UPDATE_DATE
, PCI.PROJECT_ID
, COMMENTS.CREATED_BY
FROM PA_CI_ACTIONS A
, HZ_PARTIES REQUESTOR
, HZ_PARTIES ASSIGNEE
, HZ_PARTIES REASSIGNEE
, HZ_PARTIES COMMENTOR
, PA_CI_COMMENTS COMMENTS
, PA_CI_ACTIONS A2
, PA_LOOKUPS PL
, FND_LOOKUPS FL
, FND_LOOKUPS FL2
, PA_PROJECT_STATUSES PPS
, PA_CONTROL_ITEMS PCI
WHERE REQUESTOR.PARTY_ID(+) = PA_CI_ACTIONS_UTIL.GET_PARTY_ID(A.CREATED_BY)
AND REQUESTOR.PARTY_TYPE(+) = 'PERSON'
AND ASSIGNEE.PARTY_ID(+) = A.ASSIGNED_TO
AND ASSIGNEE.PARTY_TYPE(+) = 'PERSON'
AND COMMENTOR.PARTY_ID(+) = PA_CI_ACTIONS_UTIL.GET_PARTY_ID(COMMENTS.CREATED_BY)
AND COMMENTOR.PARTY_TYPE(+) = 'PERSON'
AND A.CI_ACTION_ID(+) = COMMENTS.CI_ACTION_ID
AND COMMENTS.CI_ID = PCI.CI_ID
AND A2.SOURCE_CI_ACTION_ID(+) = A.CI_ACTION_ID
AND A2.ASSIGNED_TO = REASSIGNEE.PARTY_ID(+)
AND REASSIGNEE.PARTY_TYPE(+) ='PERSON'
AND PL.LOOKUP_TYPE(+) ='PA_CI_ACTION_TYPES'
AND PL.LOOKUP_CODE(+) = A.TYPE_CODE
AND FL.LOOKUP_TYPE(+) = 'YES_NO'
AND FL.LOOKUP_CODE(+) = A.SIGN_OFF_REQUIRED_FLAG
AND FL2.LOOKUP_TYPE(+) = 'YES_NO'
AND FL2.LOOKUP_CODE(+) = A.SIGN_OFF_FLAG
AND PPS.STATUS_TYPE(+) = 'CI_ACTION'
AND PPS.PROJECT_SYSTEM_STATUS_CODE(+) = A.STATUS_CODE