PJT_PROJECT_FTE_HOURS_V

Details

• Schema: FUSION

• Object owner: PJT

• Object type: VIEW

Columns

Name
PROJECT_ID NODE_ID current_year_hours current_quarter_hours next_quarter_hours

Query

SQL_Statement

SELECT cyhours.project_id, cyhours.node_id , cyhours.current_year_hours, cqhours.current_quarter_hours, nqhours.next_quarter_hours FROM (SELECT a.NODE_ID, a.PROJECT_ID, SUM(b1.ALLOCATED_HOURS) current_year_hours FROM PJT_REP_EPS_PRJ_HDR a, PJT_REP_EPS_PRJ_DTLS b1 WHERE a.RECORD_ID = b1.RECORD_ID AND a.RECORD_TYPE = 'PRJ_RESOURCES' AND b1.YEAR = TO_CHAR(sysdate, 'YYYY') GROUP BY a.PROJECT_ID, a.NODE_ID) cyhours, (SELECT a.NODE_ID, a.PROJECT_ID, SUM(b1.ALLOCATED_HOURS) current_quarter_hours FROM PJT_REP_EPS_PRJ_HDR a, PJT_REP_EPS_PRJ_DTLS b1 WHERE a.RECORD_ID = b1.RECORD_ID AND a.RECORD_TYPE = 'PRJ_RESOURCES' AND b1.YEAR = TO_CHAR(sysdate, 'YYYY') AND b1.QUARTER = TO_CHAR(TO_DATE(sysdate, 'dd.mm.yyyy'), 'Q') GROUP BY a.PROJECT_ID, a.NODE_ID) cqhours, (SELECT a.NODE_ID, a.PROJECT_ID, SUM(b1.ALLOCATED_HOURS) next_quarter_hours FROM PJT_REP_EPS_PRJ_HDR a, PJT_REP_EPS_PRJ_DTLS b1 WHERE a.RECORD_ID = b1.RECORD_ID AND a.RECORD_TYPE = 'PRJ_RESOURCES' AND ((b1.YEAR = TO_CHAR(sysdate, 'YYYY') AND b1.QUARTER = TO_CHAR(TO_DATE(sysdate, 'dd.mm.yyyy'), 'Q') + 1 AND TO_CHAR(TO_DATE(sysdate, 'dd.mm.yyyy'), 'Q') < 4) OR (b1.YEAR = TO_CHAR(sysdate, 'YYYY') + 1 AND b1.QUARTER = 1 AND TO_CHAR(TO_DATE(sysdate, 'dd.mm.yyyy'), 'Q') = 4)) GROUP BY a.PROJECT_ID, a.NODE_ID) nqhours WHERE cyhours.PROJECT_ID = cqhours.PROJECT_ID AND cyhours.NODE_ID = cqhours.NODE_ID AND cqhours.PROJECT_ID = nqhours.PROJECT_ID AND cqhours.NODE_ID = cqhours.NODE_ID