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