SELECT 'Holiday' project, NULL module, el.username resource_name, trunc(t_pda_holidays.holiday_date) entered_date, to_number(NULL) billable_hours, 8 non_billable_hours, to_number(NULL) extra_billable_hours, ee.firstname || DECODE(ee.middlename,NULL,' ',' ' || ee.middlename || ' ') || ee.lastname employee_name, ee.branch_id branch_id, NULL status, ee.employee_id, to_number(NULL) project_id, to_number(NULL) manager_id, to_number(ee.next_higher_authority) reporting_manager_id, --Added Project type for better differenciation of Projects 'Holiday' project_type, 0 billing_rate, 0 billing_amount FROM pda.t_pda_holidays t_pda_holidays, t_gps_calender_dates cd, eis.eis_employee ee, eis.eis_login el, eis.eis_branch_details bd WHERE trunc(t_pda_holidays.holiday_date) = trunc(cd.fact_date) AND el.employee_id = ee.employee_id AND ee.branch_id = bd.branch_id AND t_pda_holidays.country = bd.country AND ee.status IN ( 'EMPLOYEE', 'TRAINEE' ) UNION ALL SELECT 'Vacation' project, NULL module, el.username resource_name, trunc(fact_date) entered_date, NULL billable_hours, (case when ((trunc(fact_date) = trunc(eld.leave_start_date)) and (eld.startdate_ampm is not null)) then 4 when ((trunc(fact_date) = trunc(eld.leave_end_date)) and (eld.enddate_ampm is not null)) then 4 else 8 end) non_billable_hours, NULL extra_billable_hours, ee.firstname || DECODE(ee.middlename,NULL,' ',' ' || ee.middlename || ' ') || ee.lastname employee_name, ee.branch_id branch_id, upper(eld.leave_status) status, ee.employee_id, NULL project_id, NULL manager_id, to_number(ee.next_higher_authority) reporting_manager_id, 'Vacation' project_type, 0 billing_rate, 0 billing_amount FROM eis.eis_employee_leaves_details eld, t_gps_calender_dates cd, eis.eis_employee ee, eis.eis_login el WHERE eld.employee_id = ee.employee_id AND el.employee_id = ee.employee_id AND trunc(cd.fact_date) BETWEEN trunc(eld.leave_start_date) AND trunc(eld.leave_end_date) AND TO_CHAR(cd.fact_date,'D') NOT IN ( '1', '7' ) AND ( eld.leave_confirmation IS NULL OR eld.leave_confirmation = 'Definite' ) /* Added Leave_status IN('Approved','Pending') condition to Exclude considering rejected and cancelled leaves in t_time_tracking_v view */ AND eld.leave_status IN ( 'Approved', 'Pending' ) --Added below condition in order to exclude company holidays from vacations AND trunc(fact_date) NOT IN ( SELECT trunc(t_pda_holidays.holiday_date) entered_date FROM pda.t_pda_holidays t_pda_holidays, t_gps_calender_dates cd, eis.eis_employee ee, eis.eis_login el1, eis.eis_branch_details bd WHERE trunc(t_pda_holidays.holiday_date) = trunc(cd.fact_date) AND el1.employee_id = ee.employee_id AND ee.branch_id = bd.branch_id AND t_pda_holidays.country = bd.country AND ee.status IN ( 'EMPLOYEE', 'TRAINEE' ) AND el1.username = el.username AND t_pda_holidays.holiday_date = trunc(fact_date) ) UNION ALL SELECT t_gps_applications.application_name project, NULL module, DECODE(t_project_activity.user_name,NULL,t_project_task.user_name,t_project_activity.user_name) resource_name, trunc(t_project_activity.created_date) entered_date, SUM(t_project_activity.billable_hours) billable_hours, SUM(t_project_activity.non_billable_hours) non_billable_hours, SUM(t_project_activity.extra_billable_hours) extra_billable_hours, ee.firstname || DECODE(ee.middlename,NULL,' ',' ' || ee.middlename || ' ') || ee.lastname employee_name, ee.branch_id branch_id, t_project_activity.approve status, ee.employee_id, t_gps_applications.application_id project_id, to_number( ( SELECT el.employee_id FROM tas.t_users u, t_gps_app_properties ap, eis.eis_login el WHERE TO_CHAR(u.user_id) = ap.property_value AND ap.property_name = 'PROJECT_MANAGER' AND u.user_name = el.username AND ap.application_id = t_gps_applications.application_id ) ) manager_id, to_number(ee.next_higher_authority) reporting_manager_id, 'Tri2' project_type, 0 billing_rate, 0 billing_amount 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 ee, eis.eis_login el WHERE t_project_task.project_task_id = t_project_activity.project_task_id AND t_instances.instance_id = t_project_task.instance_id AND t_gps_applications.application_id = t_instances.application_id AND ee.employee_id = el.employee_id AND el.username = DECODE(t_project_activity.user_name,NULL,t_project_task.user_name,t_project_activity.user_name) -- Added visible_flag condition to exclude invalid projects AND t_gps_applications.visible_flag = 'true' --Added t_gps_applications.include_in_time_tracking = 'true' to ignore the project activity if is false. AND t_gps_applications.include_in_time_tracking = 'true' GROUP BY t_gps_applications.application_name, DECODE(t_project_activity.user_name,NULL,t_project_task.user_name,t_project_activity.user_name), trunc(t_project_activity.created_date), ee.firstname || DECODE(ee.middlename,NULL,' ',' ' || ee.middlename || ' ') || ee.lastname, ee.branch_id, t_project_activity.approve, ee.employee_id, t_gps_applications.application_id, to_number(ee.next_higher_authority) UNION ALL SELECT x.project, NULL module, x.resource_name, x.entered_date, SUM(billable_hours), SUM(non_billable_hours), NULL extra_billable_hours, x.employee_name, x.branch_id, upper(x.status), x.employee_id, x.project_id project_id, x.first_project_manager_id manager_id, to_number(x.next_higher_authority) reporting_manager_id, 'EMS' project_type, resource_rate billing_rate, sum(case when resource_billing_type = 'HOURLY' then (billable_hours) * resource_rate else (billable_hours / 8) * resource_rate end) as billing_amount FROM (SELECT ep.name project, el.username resource_name, trunc(ee.expense_date) entered_date, CASE ee.item_id WHEN 1241 THEN ee.quantity END AS billable_hours, CASE ee.item_id WHEN 1336 THEN ee.quantity END AS non_billable_hours, employee.firstname || DECODE(employee.middlename, NULL, ' ', ' ' || employee.middlename || ' ') || employee.lastname employee_name, employee.branch_id branch_id, ee.status status, employee.employee_id, ep.project_id, ep.first_project_manager_id, employee.next_higher_authority, sow_rd.resource_billing_type, sow_rd.resource_rate FROM eis.eis_employee employee join eis.eis_login el on el.employee_id = employee.employee_id join emsprod.ems_expenses ee on TO_CHAR(ee.party_id) = employee.employee_id join emsprod.ems_project ep on ee.project_id = ep.project_id left join tadmin.t_sow_process sow on ep.name = sow.process_name left join tadmin.t_sow_resource_details sow_rd on sow.process_id = sow_rd.process_id AND trunc(ee.expense_date) between trunc(sow_rd.resource_start_date) and trunc(sow_rd.resource_end_date) and sow_rd.employee_id = to_number(employee.employee_id) WHERE ee.currency_type_id = 501 AND ee.status <> 'Rejected' AND ep.include_in_time_tracking = 'true' ) x GROUP BY x.project, x.resource_name, x.entered_date, x.employee_name, x.branch_id, x.status, x.employee_id, x.project_id, x.first_project_manager_id, to_number(x.next_higher_authority), resource_rate ORDER BY 4 DESC