PJT_RES_MONTHLY_ALLOC_HRS_V

Details

• Schema: FUSION

• Object owner: PJT

• Object type: VIEW

Columns

Name
RESOURCE_ID PROJECT_ID PROJECT_ROLE_ID RES_START_DATE CY_PERIOD_END_DATE ALLOCATED_HOURS

Query

SQL_Statement

SELECT a.resource_id , a.project_id , a.project_role_id , a.res_start_date , last_day(dlyhrs.start_date) cy_period_end_date , ROUND(SUM(DECODE(SIGN(dlyhrs.start_date-a.res_start_date), -1, 0, DECODE(SIGN(a.res_end_date-dlyhrs.start_date), -1, 0, dlyhrs.hours*a.allocation/100))), 2) allocated_hours FROM ( SELECT DISTINCT prjres.resource_id , res.person_id , res.email , NVL(prj.project_calendar_id, -99) AS schedule_id , prj.project_id , prjres.project_role_id , TRUNC(NVL(prjres.start_date_active, prj.start_date)) res_start_date , TRUNC(NVL(prjres.end_date_active, NVL(prj.completion_date, sysdate+366))) res_end_date , prjres.allocation FROM fusion.pjt_project_resource prjres, fusion.pjf_projects_all_vl prj, fusion.pjt_prj_enterprise_resource_vl res WHERE 1 =1 AND prjres.project_id = prj.project_id AND prjres.resource_id = res.resource_id AND res.resource_class = 'PEOPLE' AND NVL(prj.template_flag,'N') <> 'Y' AND prj.project_category IN ('EXECUTION','FIN_EXEC') AND prj.project_status_code <> 'CLOSED' )a, fusion.PJT_ZMM_SCHEDULE_DTLS_V dlyhrs WHERE a.schedule_id = dlyhrs.schedule_id GROUP BY a.resource_id , a.project_id , a.project_role_id , a.res_start_date , last_day(dlyhrs.start_date)