View HLPDTEST.STATEGPS.V_RESOURCE_WORK_SUMMARY Generated by
SchemaSpy
Legend: SourceForge.net
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
USER_NAME varchar2 2000  √  null
H_DATE date 7  √  null
HOURS_TYPE varchar2 15  √  null
HOURS number 22  √  null

Analyzed at Mon May 23 01:05 IST 2022

View Definition:
SELECT T_PROJECT_TASK.USER_NAME , TRUNC(T_PROJECT_TASK_HISTORY.UPDATE_DATE) H_DATE, 'Estimated Hours' HOURS_TYPE, sum(((T_PROJECT_TASK_HISTORY.duration-T_PROJECT_TASK_HISTORY.daysremaining) - nvl((select(pth1.duration-pth1.daysremaining) from T_PROJECT_TASK_HISTORY pth1 where T_PROJECT_TASK_HISTORY.project_task_id = pth1.project_task_id and pth1.duration > pth1.daysremaining and pth1.daysremaining is not null and pth1.update_date < trunc(T_PROJECT_TASK_HISTORY.update_date) and rownum = 1 ),0)))*8 HOURS FROM STATEGPS.T_PROJECT_TASK T_PROJECT_TASK , STATEGPS.T_PROJECT_TASK_HISTORY T_PROJECT_TASK_HISTORY WHERE T_PROJECT_TASK_HISTORY.PROJECT_TASK_ID = T_PROJECT_TASK.PROJECT_TASK_ID AND T_PROJECT_TASK_HISTORY.DURATION > T_PROJECT_TASK_HISTORY.DAYSREMAINING AND T_PROJECT_TASK_HISTORY.DAYSREMAINING IS NOT NULL AND T_PROJECT_TASK.DURATION > 0 --AND T_PROJECT_TASK.USER_NAME = 'lchinta' --and TRUNC(T_PROJECT_TASK_HISTORY.UPDATE_DATE) = to_date('05/03/2010','MM/DD/YYYY') AND T_PROJECT_TASK.USER_NAME IS NOT NULL GROUP BY T_PROJECT_TASK.USER_NAME , TRUNC(T_PROJECT_TASK_HISTORY.UPDATE_DATE) union select pt.user_name,trunc(pa.created_date) H_DATE,'Actual Hours' HOURS_TYPE,sum(nvl(pa.billable_hours,0) + nvl(pa.non_billable_hours,0) + nvl(pa.extra_billable_hours,0)) HOURS from t_project_activity pa, t_project_task pt WHERE pa.project_task_id = pt.project_task_id --and pt.user_name = 'lchinta' --and trunc(pa.created_date) = to_date('05/03/2010','MM/DD/YYYY') group by pt.user_name,trunc(pa.created_date)
 
Possibly Referenced Tables/Views: