View HLPDTEST.STATEGPS.LAST_YEAR_RES_UTILIZATION_V 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
WORKING_DAYS number 22  √  null
EMPNAME varchar2 152  √  null
FACT_DATE varchar2 6  √  null
DEPT_NAME varchar2 255  √  null
BRANCH_ID varchar2 20  √  null
ITEM_NAME varchar2 255  √  null
FACT_YEAR number 22  √  null
ACTIVITY_SUMMARY number 22  √  null

Analyzed at Mon May 23 01:05 IST 2022

View Definition:
SELECT TRINITI_CALENDAR.WORKING_DAYS, EMP_INFO.EMPNAME, EMP_INFO.FACT_DATE, EMP_INFO.DEPT_NAME, EMP_INFO.BRANCH_ID, EMP_INFO.ITEM_NAME, EMP_INFO.FACT_YEAR, SUM(ROUND((EMP_INFO.DAYS_WORKED * 100 / TRINITI_CALENDAR.WORKING_DAYS), 2)) ACTIVITY_SUMMARY FROM (SELECT EP.NAME PROJECT_NAME, EMP.FIRSTNAME || DECODE(EMP.MIDDLENAME, NULL, '', ' ' || EMP.MIDDLENAME) || ' ' || EMP.LASTNAME EMPNAME, (TO_CHAR(EE.EXPENSE_DATE, 'MM') || '-' || TO_CHAR(EE.EXPENSE_DATE, 'MON')) FACT_DATE, EI.NAME ITEM_NAME, ROUND(SUM(EE.QUANTITY) / 8, 2) DAYS_WORKED, EMP.BRANCH_ID BRANCH_ID, T_DEPARTMENTS.NAME DEPT_NAME, T_GPS_CALENDER_DATES.FACT_MONTH FACT_MONTH, TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH, 'MM-MON') MONTH1, EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR) FACT_YEAR FROM EMSPROD.EMS_EXPENSES EE, EMSPROD.EMS_PROJECT EP, EIS.EIS_EMPLOYEE EMP, EMSPROD.EMS_ITEM EI, EIS.T_DEPARTMENTS T_DEPARTMENTS, STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES WHERE EE.CURRENCY_TYPE_ID = 501 AND EE.PROJECT_ID = EP.PROJECT_ID AND TO_CHAR(EE.PARTY_ID) = EMP.EMPLOYEE_ID AND EE.ITEM_ID = EI.ITEM_ID AND EE.EXPENSE_DATE >= TRUNC(TRUNC(SYSDATE, 'Year') - 1, 'Year') AND EE.QUANTITY > 0 AND EMP.STATUS = 'EMPLOYEE' AND EMP.DEPT_ID = T_DEPARTMENTS.ID AND T_GPS_CALENDER_DATES.FACT_DATE = TRUNC(EE.EXPENSE_DATE) GROUP BY EP.NAME, EMP.FIRSTNAME || DECODE(EMP.MIDDLENAME, NULL, '', ' ' || EMP.MIDDLENAME) || ' ' || EMP.LASTNAME, (TO_CHAR(EE.EXPENSE_DATE, 'MM') || '-' || TO_CHAR(EE.EXPENSE_DATE, 'MON')), EI.NAME, EMP.BRANCH_ID, T_DEPARTMENTS.NAME, T_GPS_CALENDER_DATES.FACT_MONTH, TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH, 'MM-MON'), EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR) UNION ALL SELECT A.APPLICATION_NAME, EMP.FIRSTNAME || DECODE(EMP.MIDDLENAME, NULL, '', ' ' || EMP.MIDDLENAME) || ' ' || EMP.LASTNAME, (TO_CHAR(PA.CREATED_DATE, 'MM') || '-' || TO_CHAR(PA.CREATED_DATE, 'MON')), 'Billable Time', ROUND(SUM(PA.BILLABLE_HOURS + PA.EXTRA_BILLABLE_HOURS) / 8, 2) DAYS_WORKED, EMP.BRANCH_ID, T_DEPARTMENTS.NAME, T_GPS_CALENDER_DATES.FACT_MONTH, TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH, 'MM-MON'), EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR) FROM STATEGPS.T_PROJECT_ACTIVITY PA, STATEGPS.T_PROJECT_TASK PT, STATEGPS.T_INSTANCES I, STATEGPS.T_GPS_APPLICATIONS A, EIS.EIS_EMPLOYEE EMP, EIS.EIS_LOGIN EL, EIS.T_DEPARTMENTS T_DEPARTMENTS, STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES WHERE PA.CREATED_DATE >= TRUNC(TRUNC(SYSDATE, 'Year') - 1, 'Year') AND (PA.BILLABLE_HOURS + PA.EXTRA_BILLABLE_HOURS) > 0 AND PA.PROJECT_TASK_ID = PT.PROJECT_TASK_ID AND PT.INSTANCE_ID = I.INSTANCE_ID AND A.APPLICATION_ID = I.APPLICATION_ID AND PA.USER_NAME = EL.USERNAME AND EL.EMPLOYEE_ID = EMP.EMPLOYEE_ID AND EMP.STATUS = 'EMPLOYEE' AND EMP.DEPT_ID = T_DEPARTMENTS.ID AND T_GPS_CALENDER_DATES.FACT_DATE = TRUNC(PA.CREATED_DATE) AND UPPER(A.VISIBLE_FLAG) = 'TRUE' GROUP BY A.APPLICATION_NAME, EMP.FIRSTNAME || DECODE(EMP.MIDDLENAME, NULL, '', ' ' || EMP.MIDDLENAME) || ' ' || EMP.LASTNAME, (TO_CHAR(PA.CREATED_DATE, 'MM') || '-' || TO_CHAR(PA.CREATED_DATE, 'MON')), EMP.BRANCH_ID, T_DEPARTMENTS.NAME, T_GPS_CALENDER_DATES.FACT_MONTH, TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH, 'MM-MON'), EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR) UNION ALL SELECT A.APPLICATION_NAME, EMP.FIRSTNAME || DECODE(EMP.MIDDLENAME, NULL, '', ' ' || EMP.MIDDLENAME) || ' ' || EMP.LASTNAME, (TO_CHAR(PA.CREATED_DATE, 'MM') || '-' || TO_CHAR(PA.CREATED_DATE, 'MON')), 'Non Billable Time', ROUND(SUM(PA.NON_BILLABLE_HOURS) / 8, 2) DAYS_WORKED, EMP.BRANCH_ID, T_DEPARTMENTS.NAME, T_GPS_CALENDER_DATES.FACT_MONTH, TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH, 'MM-MON'), EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR) FROM STATEGPS.T_PROJECT_ACTIVITY PA, STATEGPS.T_PROJECT_TASK PT, STATEGPS.T_INSTANCES I, STATEGPS.T_GPS_APPLICATIONS A, EIS.EIS_EMPLOYEE EMP, EIS.EIS_LOGIN EL, EIS.T_DEPARTMENTS T_DEPARTMENTS, STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES WHERE PA.CREATED_DATE >= TRUNC(TRUNC(SYSDATE, 'Year') - 1, 'Year') AND PA.NON_BILLABLE_HOURS > 0 AND PA.PROJECT_TASK_ID = PT.PROJECT_TASK_ID AND PT.INSTANCE_ID = I.INSTANCE_ID AND I.APPLICATION_ID = A.APPLICATION_ID AND PA.USER_NAME = EL.USERNAME AND EL.EMPLOYEE_ID = EMP.EMPLOYEE_ID AND EMP.STATUS = 'EMPLOYEE' AND EMP.DEPT_ID = T_DEPARTMENTS.ID AND T_GPS_CALENDER_DATES.FACT_DATE = TRUNC(PA.CREATED_DATE) AND UPPER(A.VISIBLE_FLAG) = 'TRUE' GROUP BY A.APPLICATION_NAME, EMP.FIRSTNAME || DECODE(EMP.MIDDLENAME, NULL, '', ' ' || EMP.MIDDLENAME) || ' ' || EMP.LASTNAME, (TO_CHAR(PA.CREATED_DATE, 'MM') || '-' || TO_CHAR(PA.CREATED_DATE, 'MON')), EMP.BRANCH_ID, T_DEPARTMENTS.NAME, T_GPS_CALENDER_DATES.FACT_MONTH, TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH, 'MM-MON'), EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR)) EMP_INFO, (SELECT COUNT(FACT_DATE) WORKING_DAYS, T_GPS_CALENDER_DATES.FACT_MONTH CAL_MONTH, EIS_BRANCH_DETAILS.BRANCH_ID BRANCHID FROM STATEGPS.T_GPS_CALENDER_DATES, EIS.EIS_BRANCH_DETAILS EIS_BRANCH_DETAILS WHERE DAY_TYPE IS NULL AND EXTRACT(YEAR FROM T_GPS_CALENDER_DATES.FACT_YEAR) >= EXTRACT(YEAR FROM SYSDATE) - 1 AND TRUNC(FACT_DATE) NOT IN (SELECT T_PDA_HOLIDAYS.HOLIDAY_DATE FROM PDA.T_PDA_HOLIDAYS WHERE T_PDA_HOLIDAYS.COUNTRY = EIS_BRANCH_DETAILS.BRANCH_NAME) GROUP BY T_GPS_CALENDER_DATES.FACT_MONTH, EIS_BRANCH_DETAILS.BRANCH_ID) TRINITI_CALENDAR WHERE TRINITI_CALENDAR.CAL_MONTH = EMP_INFO.FACT_MONTH AND TRINITI_CALENDAR.BRANCHID = EMP_INFO.BRANCH_ID GROUP BY TRINITI_CALENDAR.WORKING_DAYS, EMP_INFO.EMPNAME, EMP_INFO.FACT_DATE, EMP_INFO.DEPT_NAME, EMP_INFO.BRANCH_ID, EMP_INFO.ITEM_NAME, EMP_INFO.FACT_YEAR
 
Possibly Referenced Tables/Views: