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