View HLPDTEST.STATEGPS.V_CURRENT_MONTH_TIMESHEET | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Tue May 24 01:05 IST 2022 |
View Definition:
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)
Possibly Referenced Tables/Views:
![]() ![]() |