SELECT 'Leave' PROJECT, null ACTIVITY, null IA_ISSUE_NUMBER, null IA_ISSUE_TITLE, NULL MODULE, el.username RESOURCE_NAME, TRUNC(FACT_DATE) ENTERED_DATE, FACT_DATE ENTERED_DATE_TIME, NULL BILLABLE_HOURS, 8 NON_BILLABLE_HOURS, NULL EXTRA_BILLABLE_HOURS, null STATE_ID, null STATE_NAME, ee.firstname || ' ' || ee.middlename || ' ' || ee.lastname EMPLOYEE_NAME, ee.branch_id BRANCH_ID, 'LEAVE' TYPE FROM EIS.eis_employee_leaves_details eld, t_gps_calender_dates cd, EIS.EIS_EMPLOYEE ee, EMSPROD.EIS_LOGIN el WHERE eld.employee_id = ee.employee_id AND TO_CHAR(el.login_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') UNION select activity.PROJECT, activity.ACTIVITY ACTIVITY, activity.IA_ISSUE_NUMBER, activity.IA_ISSUE_TITLE, activity.MODULE MODULE, activity.RESOURCE_NAME, activity.ENTERED_DATE, activity.ENTERED_DATE_TIME ENTERED_DATE_TIME, activity.BILLABLE_HOURS BILLABLE_HOURS, activity.NON_BILLABLE_HOURS NON_BILLABLE_HOURS, activity.EXTRA_BILLABLE_HOURS EXTRA_BILLABLE_HOURS, activity.STATE_ID, activity.STATE_NAME, ee.firstname || ' ' || ee.middlename || ' ' || ee.lastname EMPLOYEE_NAME, ee.branch_id BRANCH_ID, 'PROJECTS' TYPE from (SELECT T_GPS_APPLICATIONS.APPLICATION_NAME PROJECT, T_PROJECT_ACTIVITY.ACTIVITY ACTIVITY, V_INSTANCE_PROPS.IA_ISSUE_NUMBER, V_INSTANCE_PROPS.IA_ISSUE_TITLE, ISSUES.APPLICATION_MODULE MODULE, DECODE(T_PROJECT_ACTIVITY.USER_NAME, NULL, T_PROJECT_TASK.USER_NAME, T_PROJECT_ACTIVITY.USER_NAME) RESOURCE_NAME, DECODE(T_PROJECT_ACTIVITY.USER_NAME, NULL, T_PROJECT_TASK.USER_NAME, T_PROJECT_ACTIVITY.USER_NAME)|| '@triniti.com' RESOURCE_MAIL_ID, TRUNC(T_PROJECT_ACTIVITY.CREATED_DATE) ENTERED_DATE, T_PROJECT_ACTIVITY.CREATED_DATE ENTERED_DATE_TIME, T_PROJECT_ACTIVITY.BILLABLE_HOURS BILLABLE_HOURS, T_PROJECT_ACTIVITY.NON_BILLABLE_HOURS NON_BILLABLE_HOURS, T_PROJECT_ACTIVITY.EXTRA_BILLABLE_HOURS EXTRA_BILLABLE_HOURS, T_GPS_WORKFLOW_STATES.STATE_ID, T_GPS_WORKFLOW_STATES.STATE_NAME, T_PROJECT_ACTIVITY.USER_NAME T_PROJECT_ACTIVITY_USER_NAME, T_PROJECT_TASK.USER_NAME T_PROJECT_TASK_USER_NAME 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, STATEGPS.V_INSTANCE_PROPS V_INSTANCE_PROPS, TRI2GPS_DEV.ISSUES ISSUES, STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES 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_INSTANCES.INSTANCE_ID = V_INSTANCE_PROPS.INSTANCE_ID AND T_GPS_APPLICATIONS.APPLICATION_ID = T_INSTANCES.APPLICATION_ID AND V_INSTANCE_PROPS.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID and T_GPS_APPLICATIONS.Visible_Flag = 'true') activity, EIS.EIS_EMPLOYEE ee where ee.corporate_email_id (+) = activity.RESOURCE_MAIL_ID and ee.status = 'EMPLOYEE' UNION SELECT x.project, x.notes ACTIVITY, null IA_ISSUE_NUMBER, null IA_ISSUE_TITLE, x.name MODULE, x.resource_name, x.entered_date, x.ENTERED_DATE_TIME ENTERED_DATE_TIME, billable_hours, non_billable_hours, NULL, null STATE_ID, null STATE_NAME, x.EMPLOYEE_NAME, X.BRANCH_ID, 'EMS' TYPE FROM (SELECT ep.name PROJECT, NULL MODULE, ee.notes, ep.name, el.username RESOURCE_NAME, TRUNC(ee.expense_date) ENTERED_DATE, ee.expense_date ENTERED_DATE_TIME, 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, NULL extra_billable_hours, ee.firstname || ' ' || ee.middlename || ' ' || ee.lastname EMPLOYEE_NAME, ee.branch_id BRANCH_ID FROM EMSPROD.ems_expenses ee, EMSPROD.ems_project ep, EMSPROD.eis_login el, EIS.eis_employee ee WHERE ee.currency_type_id = 501 AND ee.project_id = ep.project_id AND ee.party_id = el.login_id AND TO_CHAR(el.login_id) = ee.employee_id) x ORDER BY 4 DESC