View HLPDTEST.STATEGPS.V_PROJECT_RESOURCE_TIMESHEET 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
EMPLOYEE_ID varchar2 200  √  null
EMPLOYEE varchar2 152  √  null
TRADING_PARTY_NAME varchar2 255  √  null
PROCESS_NAME varchar2 255  √  null
TYPE varchar2 255  √  null
QUANTITY number 22  √  null
TRADING_PARTY_ID number 22  √  null
T_TRADING_PARTY.TRADING_PARTY_ID Implied Constraint R
FACT_MONTH varchar2 8  √  null
AS_ON_DATE date 7  √  null

Analyzed at Mon May 23 01:05 IST 2022

View Definition:
SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , EMS_ITEM.NAME, SUM(EMS_EXPENSES.QUANTITY) , T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM EMSPROD.EMS_EXPENSES EMS_EXPENSES , EMSPROD.EMS_PROJECT EMS_PROJECT , EMSPROD.EMS_ITEM EMS_ITEM , EIS.EIS_EMPLOYEE EIS_EMPLOYEE , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES, TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS WHERE EMS_EXPENSES.PROJECT_ID = EMS_PROJECT.PROJECT_ID AND EIS_EMPLOYEE.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') AND EMS_EXPENSES.ITEM_ID IN (1241,1336) AND EMS_EXPENSES.CURRENCY_TYPE_ID = 501 AND EMS_ITEM.ITEM_ID = EMS_EXPENSES.ITEM_ID AND TRUNC(EMS_EXPENSES.EXPENSE_DATE) = TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) AND TO_CHAR(EMS_EXPENSES.PARTY_ID) = EIS_EMPLOYEE.EMPLOYEE_ID AND T_SOW_PROCESS.PROCESS_NAME = EMS_PROJECT.NAME AND LOWER(EMS_EXPENSES.STATUS) <> 'rejected' GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID,EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' ' ||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME), EMS_ITEM.NAME UNION SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , 'Non Billable Time' , SUM(T_PROJECT_ACTIVITY.NON_BILLABLE_HOURS) , T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM STATEGPS.T_PROJECT_ACTIVITY T_PROJECT_ACTIVITY , STATEGPS.T_PROJECT_TASK T_PROJECT_TASK , STATEGPS.T_INSTANCES T_INSTANCES , STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS , EIS.EIS_EMPLOYEE EIS_EMPLOYEE , EIS.EIS_LOGIN EIS_LOGIN , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES , TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS WHERE T_PROJECT_TASK.PROJECT_TASK_ID = T_PROJECT_ACTIVITY.PROJECT_TASK_ID AND EIS_EMPLOYEE.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') AND T_PROJECT_TASK.INSTANCE_ID = T_INSTANCES.INSTANCE_ID AND T_INSTANCES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND T_PROJECT_ACTIVITY.USER_NAME IS NOT NULL AND T_PROJECT_ACTIVITY.CREATED_DATE = T_GPS_CALENDER_DATES.FACT_DATE AND T_PROJECT_ACTIVITY.NON_BILLABLE_HOURS IS NOT NULL AND EIS_LOGIN.USERNAME = T_PROJECT_ACTIVITY.USER_NAME AND EIS_LOGIN.EMPLOYEE_ID = EIS_EMPLOYEE.EMPLOYEE_ID AND T_SOW_PROCESS.PROCESS_NAME = T_GPS_APPLICATIONS.APPLICATION_NAME GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME) ||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME) UNION SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , 'Billable Time' , SUM(T_PROJECT_ACTIVITY.BILLABLE_HOURS) , T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM STATEGPS.T_PROJECT_ACTIVITY T_PROJECT_ACTIVITY , STATEGPS.T_PROJECT_TASK T_PROJECT_TASK , STATEGPS.T_INSTANCES T_INSTANCES , STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS , EIS.EIS_EMPLOYEE EIS_EMPLOYEE , EIS.EIS_LOGIN EIS_LOGIN , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES, TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS WHERE T_PROJECT_TASK.PROJECT_TASK_ID = T_PROJECT_ACTIVITY.PROJECT_TASK_ID AND EIS_EMPLOYEE.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') AND T_PROJECT_TASK.INSTANCE_ID = T_INSTANCES.INSTANCE_ID AND T_INSTANCES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND T_PROJECT_ACTIVITY.USER_NAME IS NOT NULL AND T_PROJECT_ACTIVITY.CREATED_DATE = T_GPS_CALENDER_DATES.FACT_DATE AND T_PROJECT_ACTIVITY.BILLABLE_HOURS IS NOT NULL AND EIS_LOGIN.USERNAME = T_PROJECT_ACTIVITY.USER_NAME AND EIS_LOGIN.EMPLOYEE_ID = EIS_EMPLOYEE.EMPLOYEE_ID AND T_SOW_PROCESS.PROCESS_NAME = T_GPS_APPLICATIONS.APPLICATION_NAME GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME) ||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME) UNION SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , 'Billable Time' , SUM(T_PROJECT_ACTIVITY.EXTRA_BILLABLE_HOURS) , T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM STATEGPS.T_PROJECT_ACTIVITY T_PROJECT_ACTIVITY , STATEGPS.T_PROJECT_TASK T_PROJECT_TASK , STATEGPS.T_INSTANCES T_INSTANCES , STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS , EIS.EIS_EMPLOYEE EIS_EMPLOYEE , EIS.EIS_LOGIN EIS_LOGIN , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES, TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS WHERE T_PROJECT_TASK.PROJECT_TASK_ID = T_PROJECT_ACTIVITY.PROJECT_TASK_ID AND EIS_EMPLOYEE.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') AND T_PROJECT_TASK.INSTANCE_ID = T_INSTANCES.INSTANCE_ID AND T_INSTANCES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND T_PROJECT_ACTIVITY.USER_NAME IS NOT NULL AND T_PROJECT_ACTIVITY.CREATED_DATE = T_GPS_CALENDER_DATES.FACT_DATE AND T_PROJECT_ACTIVITY.EXTRA_BILLABLE_HOURS IS NOT NULL AND EIS_LOGIN.USERNAME = T_PROJECT_ACTIVITY.USER_NAME AND EIS_LOGIN.EMPLOYEE_ID = EIS_EMPLOYEE.EMPLOYEE_ID AND T_SOW_PROCESS.PROCESS_NAME = T_GPS_APPLICATIONS.APPLICATION_NAME GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME) ||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME) UNION SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , 'Holiday Hours', COUNT(EIS_EMPLOYEE.EMPLOYEE_ID)*8 , T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM EIS.EIS_EMPLOYEE EIS_EMPLOYEE , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES, PDA.T_PDA_HOLIDAYS T_PDA_HOLIDAYS, EIS.EIS_BRANCH_DETAILS EIS_BRANCH_DETAILS, TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS WHERE T_PDA_HOLIDAYS.COUNTRY = EIS_BRANCH_DETAILS.COUNTRY AND EIS_BRANCH_DETAILS.BRANCH_ID = EIS_EMPLOYEE.BRANCH_ID AND EIS_EMPLOYEE.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND TRUNC(T_PDA_HOLIDAYS.HOLIDAY_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') AND TRUNC(T_PDA_HOLIDAYS.HOLIDAY_DATE) = TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) AND T_GPS_CALENDER_DATES.DAY_TYPE IS NULL GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID,EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME) ||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME) UNION SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , 'Vacation Hours', COUNT(EIS_EMPLOYEE.EMPLOYEE_ID)*8, T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM EIS.EIS_EMPLOYEE EIS_EMPLOYEE , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES, TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS, EIS.EIS_EMPLOYEE_LEAVES_DETAILS EIS_EMPLOYEE_LEAVES_DETAILS, EIS.T_LEAVE_TYPE T_LEAVE_TYPE WHERE EIS_EMPLOYEE.EMPLOYEE_ID = EIS_EMPLOYEE_LEAVES_DETAILS.EMPLOYEE_ID AND EIS_EMPLOYEE_LEAVES_DETAILS.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND EIS_EMPLOYEE_LEAVES_DETAILS.LEAVE_STATUS IN ('Approved','Pending','Cancel Requested','Admin Approved') AND EIS_EMPLOYEE_LEAVES_DETAILS.LEAVE_TYPE = T_LEAVE_TYPE.DISPLAY_NAME AND EIS_EMPLOYEE_LEAVES_DETAILS.LEAVE_NUMBER_OF_DAYS IS NOT NULL AND T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(EIS_EMPLOYEE_LEAVES_DETAILS.LEAVE_START_DATE) AND TRUNC(EIS_EMPLOYEE_LEAVES_DETAILS.LEAVE_END_DATE) AND NOT EXISTS( SELECT TRUNC(T_PDA_HOLIDAYS.HOLIDAY_DATE) FROM PDA.T_PDA_HOLIDAYS T_PDA_HOLIDAYS,EIS.EIS_BRANCH_DETAILS EIS_BRANCH_DETAILS WHERE TRUNC(T_PDA_HOLIDAYS.HOLIDAY_DATE) = TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) AND T_PDA_HOLIDAYS.COUNTRY = EIS_BRANCH_DETAILS.COUNTRY AND EIS_BRANCH_DETAILS.BRANCH_ID = EIS_EMPLOYEE.BRANCH_ID) AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') AND T_GPS_CALENDER_DATES.DAY_TYPE IS NULL GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID, EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME) ||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME) UNION SELECT EIS_EMPLOYEE.EMPLOYEE_ID , INITCAP(EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME)||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)) , T_SOW_PROCESS.TRADING_PARTY_NAME , T_SOW_PROCESS.PROCESS_NAME , 'Month Hours', COUNT(EIS_EMPLOYEE.EMPLOYEE_ID)*8, T_SOW_PROCESS.TRADING_PARTY_ID , TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'), TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) FROM EIS.EIS_EMPLOYEE EIS_EMPLOYEE , TADMIN.T_SOW_PROCESS T_SOW_PROCESS , STATEGPS.T_GPS_CALENDER_DATES T_GPS_CALENDER_DATES, TADMIN.T_SOW_RESOURCE_DETAILS T_SOW_RESOURCE_DETAILS WHERE T_SOW_RESOURCE_DETAILS.PROCESS_ID = T_SOW_PROCESS.PROCESS_ID AND EIS_EMPLOYEE.EMPLOYEE_ID = TO_CHAR(T_SOW_RESOURCE_DETAILS.EMPLOYEE_ID) AND T_GPS_CALENDER_DATES.DAY_TYPE IS NULL AND TRUNC(T_GPS_CALENDER_DATES.FACT_DATE) BETWEEN TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_START_DATE) AND TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE) AND TRUNC(T_GPS_CALENDER_DATES.FACT_MONTH) <= TRUNC(T_SOW_RESOURCE_DETAILS.RESOURCE_END_DATE,'MONTH') GROUP BY TO_CHAR(T_GPS_CALENDER_DATES.FACT_MONTH,'Mon YYYY'),TRUNC(T_GPS_CALENDER_DATES.FACT_DATE), T_SOW_PROCESS.TRADING_PARTY_ID, T_SOW_PROCESS.TRADING_PARTY_NAME, T_SOW_PROCESS.PROCESS_NAME, EIS_EMPLOYEE.EMPLOYEE_ID,EIS_EMPLOYEE.FIRSTNAME||DECODE(EIS_EMPLOYEE.MIDDLENAME,NULL,'',' '||EIS_EMPLOYEE.MIDDLENAME) ||DECODE(EIS_EMPLOYEE.LASTNAME,NULL,'',' '||EIS_EMPLOYEE.LASTNAME)
 
Possibly Referenced Tables/Views:


Close relationships: