SELECT el.username username,cd.fact_date entered_date, NULL unique_id, ep.NAME PROJECT, ei.NAME item, ep.project_id project_id, ei.item_id item_id, NULL quantity, 'EMS' SOURCE,eup.party_id party_id,employee.corporate_email_id email_id, null notes FROM emsprod.ems_user_preference eup JOIN emsprod.ems_project ep ON ep.project_id = eup.project_id JOIN emsprod.ems_item ei ON eup.item_id = ei.item_id JOIN eis.eis_login el ON el.employee_id = to_char(eup.party_id) JOIN eis.eis_employee employee ON el.employee_id = employee.employee_id LEFT JOIN tadmin.t_sow_process process ON ep.NAME = process.process_name LEFT JOIN tadmin.t_sow_resource_details details ON details.employee_id = eup.party_id AND process.process_id = details.process_id, t_gps_calender_dates cd WHERE (employee.status = 'EMPLOYEE' or (employee.state is null and employee.status = 'RESIGNED')) AND ei.item_type_id = 501 AND ei.item_id IN (1241,1336) AND cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL) AND (next_day(ep.enddate,'MON') >= TRUNC(current_date) OR ep.enddate IS NULL) AND NOT EXISTS (SELECT * FROM emsprod.ems_expenses ee WHERE ee.party_id = eup.party_id AND ee.project_id = eup.project_id AND ee.item_id = eup.item_id AND TRUNC(ee.expense_date) = cd.fact_date ) AND (details.resource_end_date >= current_date OR details.resource_end_date IS NULL) UNION ALL /* * Entered EMS portion = This portion gets all the entered project items in EMS */ SELECT el.username username,TRUNC(ee.expense_date) entered_date,ee.expense_line_id unique_id, ep.NAME PROJECT,ei.NAME item,ep.project_id project_id, ei.item_id item_id,ee.quantity quantity, 'EMS' SOURCE,ee.party_id party_id,employee.corporate_email_id email_id, ee.notes FROM emsprod.ems_expenses ee, emsprod.ems_project ep, emsprod.ems_item ei, eis.eis_login el, eis.eis_employee employee,t_gps_calender_dates cd WHERE cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL) AND TRUNC(ee.expense_date) = cd.fact_date AND ep.project_id = ee.project_id AND ei.item_id = ee.item_id AND ei.item_type_id = 501 AND ei.item_id IN (1241,1336) AND el.employee_id = to_char(ee.party_id) AND el.employee_id = employee.employee_id AND (employee.status = 'EMPLOYEE' or (employee.state is null and employee.status = 'RESIGNED')) UNION ALL /* * Entered PROJECT ACTIVITY Billable Time = This portion gets all the billable time entered on the specified date */ SELECT pa.user_name username,TRUNC(pa.created_date) entered_date,pa.activity_id unique_id,A.application_name PROJECT,'Billable Time' item,NULL project_id, NULL item_id,pa.billable_hours quantity, 'PA' SOURCE, NULL party_id,employee.corporate_email_id email_id, null notes FROM t_project_activity pa, t_project_task pt, t_instances I, t_gps_applications A,eis.eis_employee employee, eis.eis_login el,t_gps_calender_dates cd WHERE pt.project_task_id = pa.project_task_id AND pt.instance_id = I.instance_id AND I.application_id = A.application_id AND pa.user_name IS NOT NULL AND TRUNC(pa.created_date) = cd.fact_date AND cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL) AND pa.billable_hours IS NOT NULL AND el.username = pa.user_name AND el.employee_id = employee.employee_id AND (employee.status = 'EMPLOYEE' or (employee.state is null and employee.status = 'RESIGNED')) UNION ALL /* * Entered PROJECT ACTIVITY Extra Billable Time = This portion gets all the extra billable time entered on the specified date */ SELECT pa.user_name username,TRUNC(pa.created_date) entered_date,pa.activity_id unique_id,A.application_name PROJECT,'Extra Billable Time' item,NULL project_id, NULL item_id,pa.extra_billable_hours quantity, 'PA' SOURCE, NULL party_id,employee.corporate_email_id email_id, null notes FROM t_project_activity pa, t_project_task pt, t_instances I, t_gps_applications A, eis.eis_employee employee, eis.eis_login el,t_gps_calender_dates cd WHERE pt.project_task_id = pa.project_task_id AND pt.instance_id = I.instance_id AND I.application_id = A.application_id AND pa.user_name IS NOT NULL AND TRUNC(pa.created_date) = cd.fact_date AND cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL) AND pa.extra_billable_hours IS NOT NULL AND el.username = pa.user_name AND el.employee_id = employee.employee_id AND (employee.status = 'EMPLOYEE' or (employee.state is null and employee.status = 'RESIGNED')) UNION ALL /* * Entered PROJECT ACTIVITY Non Billable Time = This portion gets all the non billable time entered on the specified date */ SELECT pa.user_name username,TRUNC(pa.created_date) entered_date,pa.activity_id unique_id,A.application_name PROJECT,'Non Billable Time' item,NULL project_id, NULL item_id,pa.non_billable_hours quantity, 'PA' SOURCE, NULL party_id,employee.corporate_email_id email_id, null notes FROM t_project_activity pa, t_project_task pt, t_instances I, t_gps_applications A,eis.eis_employee employee, eis.eis_login el,t_gps_calender_dates cd WHERE pt.project_task_id = pa.project_task_id AND pt.instance_id = I.instance_id AND I.application_id = A.application_id AND pa.user_name IS NOT NULL AND TRUNC(pa.created_date) = cd.fact_date AND cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL) AND pa.non_billable_hours IS NOT NULL AND el.username = pa.user_name AND el.employee_id = employee.employee_id AND (employee.status = 'EMPLOYEE' or (employee.state is null and employee.status = 'RESIGNED')) UNION ALL /* * Holiday portion = This portion gets all holiday hours on the specified date */ SELECT el.username username,TRUNC(t_pda_holidays.holiday_date) entered_date,NULL unique_id,'Holiday' PROJECT,'Non Billable Time' item,NULL project_id, NULL item_id,8 quantity, 'HOLIDAY' SOURCE, NULL party_id,ee.corporate_email_id email_id, null notes FROM pda.t_pda_holidays t_pda_holidays, eis.eis_employee ee, emsprod.eis_login el, eis.eis_branch_details bd,t_gps_calender_dates cd WHERE to_char(el.login_id) = ee.employee_id AND ee.branch_id = bd.branch_id AND t_pda_holidays.country = bd.country AND (ee.status = 'EMPLOYEE' or (ee.state is null and ee.status = 'RESIGNED')) AND TRUNC(t_pda_holidays.holiday_date) = cd.fact_date AND cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL) /* * Vacation portion = This portion gets all definate vacation hours on the specified date */ UNION ALL SELECT el.username username,TRUNC(fact_date) entered_date,NULL unique_id,'Vacation' PROJECT,'Non Billable Time' item,NULL project_id, NULL item_id, /*decode(mod(eld.leave_number_of_days*8,8),0,8,decode(trunc(eld.leave_start_date),trunc(cd.fact_date),mod(eld.leave_number_of_days*8,8),8)) quantity, */ ( case when cd.fact_date=eld.leave_start_date and startdate_ampm='AM' then 4 when cd.fact_date=eld.leave_start_date and startdate_ampm='PM' then 4 when cd.fact_date=eld.leave_end_date and enddate_ampm='AM' then 4 else 8 end ) as quantity, 'VACATION' SOURCE, NULL party_id,ee.corporate_email_id email_id, null notes 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') AND (eld.leave_confirmation IS NULL OR eld.leave_confirmation = 'Definite') AND eld.leave_status IN ('Approved','Pending') AND NOT EXISTS (SELECT * FROM pda.t_pda_holidays H, eis.eis_branch_details bd WHERE H.country = bd.country AND ee.branch_id = bd.branch_id AND TRUNC(cd.fact_date) = TRUNC(holiday_date)) AND cd.fact_month = TRUNC(current_date,'month') AND (cd.day_type != 'WE' OR cd.day_type IS NULL)