View HLPDTEST.STATEGPS.PROJECT_TASK_AUDIT_V | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Sat Jul 02 01:05 IST 2022 |
View Definition:
select distinct a.state_id, HISTORY_ID,USER_COMMENTS,UPDATE_DATE,DAYSREMAINING,DURATION,USER_NAME,
UPDATED_BY,PRIORITY,PROMISED_DATE,MANAGER_COMMENTS,APPLICATION_NAME,STATE_NAME,WORKFLOW_TYPE_NAME,PROJECT_TASK_ID,
ISSUE_TITLE,ISSUE_NUMBER,INSTANCE_ID,APPLICATION_MODULE,
getDocuments(HISTORY_ID) as DOCUMENT_NAME from
(
SELECT
t_project_task_history.HISTORY_ID "HISTORY_ID"
,t_project_task_history.COMMENTS "USER_COMMENTS"
,to_char(t_project_task_history.UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') "UPDATE_DATE"
,t_project_task_history.DAYSREMAINING "DAYSREMAINING"
,t_project_task_history.DURATION "DURATION"
,( SELECT(first_name || '' || middle_name || ' ' || last_name)
FROM stategps.t_users t
WHERE t.user_name = t_users.USER_NAME ) "USER_NAME"
,( SELECT(first_name || '' || middle_name || ' ' || last_name)
FROM stategps.t_users t
WHERE t.user_name = t_task_manager_comment.UPDATED_BY ) "UPDATED_BY"
,(SELECT T_PROJECT_CUSTOM_PRIORITY.priority
FROM stategps.T_PROJECT_CUSTOM_PRIORITY T_PROJECT_CUSTOM_PRIORITY
WHERE T_PROJECT_CUSTOM_PRIORITY.start_range = t_task_manager_comment.PRIORITY ) "PRIORITY"
,t_task_manager_comment.PROMISED_DATE "PROMISED_DATE"
,t_task_manager_comment.COMMENTS "MANAGER_COMMENTS"
,t_gps_applications.APPLICATION_NAME "APPLICATION_NAME"
,t_gps_workflow_states.STATE_NAME "STATE_NAME"
,t_gps_workflow_types.WORKFLOW_TYPE_NAME "WORKFLOW_TYPE_NAME"
,t_project_task.PROJECT_TASK_ID "PROJECT_TASK_ID"
,t_project_task.ISSUE_TITLE "ISSUE_TITLE"
,t_project_task.ISSUE_NUMBER "ISSUE_NUMBER"
,t_project_task.INSTANCE_ID "INSTANCE_ID"
,t_project_task.APPLICATION_MODULE "APPLICATION_MODULE"
,t_gps_workflow_states.state_id
,CASE
WHEN t_instance_child_docs.DOCUMENT_NAME IS NOT NULL
THEN t_instance_child_docs.document || '/' || t_instance_child_docs.DOCUMENT_NAME
ELSE t_instance_child_docs.DOCUMENT_NAME
END AS "DOCUMENT_NAME"
FROM
stategps.t_project_task_history t_project_task_history
,tas.t_users t_users
,stategps.t_task_manager_comment t_task_manager_comment
,stategps.t_gps_applications t_gps_applications
,stategps.t_gps_workflow_states t_gps_workflow_states
,stategps.t_gps_workflow_types t_gps_workflow_types
,(select t_project_task1.PROJECT_TASK_ID,
t_project_task1.state_id,
t_project_task1.instance_id,
issues.ISSUE_TITLE
,issues.ISSUE_NUMBER
,issues.APPLICATION_MODULE from tri2gps_dev.issues issues,stategps.t_project_task t_project_task1
where issues.INSTANCE_ID = t_project_task1.INSTANCE_ID) t_project_task LEFT OUTER JOIN
(select parent_docs.instance_id,parent_docs.document_id,parent_docs.document,child_docs.document_name,child_docs.state_id from stategps.t_Instance_Documents parent_docs,
stategps.t_instance_child_docs child_docs
where parent_docs.document_id=child_docs.document_id ) t_instance_child_docs
ON t_project_task.instance_id = t_instance_child_docs.Instance_Id
and t_project_task.state_id = t_instance_child_docs.state_id
WHERE
t_gps_workflow_types.APPLICATION_ID = t_gps_applications.APPLICATION_ID
AND t_project_task.STATE_ID = t_gps_workflow_states.STATE_ID
AND t_project_task_history.PROJECT_TASK_ID = t_project_task.PROJECT_TASK_ID
AND t_project_task_history.RESOURCE_ID = t_users.USER_ID
AND t_gps_workflow_states.WORKFLOW_TYPE_ID = t_gps_workflow_types.WORKFLOW_TYPE_ID
AND t_task_manager_comment.PROJECT_TASK_ID = t_project_task.PROJECT_TASK_ID
AND t_task_manager_comment.UPDATED_AT = t_project_task_history.UPDATE_DATE
ORDER BY
t_project_task_history.HISTORY_ID ASC ) a
Possibly Referenced Tables/Views:
![]() ![]() |