VIEW: APPS.PA_CI_COMMENTS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'