[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select shift_id,(shift_end_date - shift_start_date) shift_duration
from jtf_cal_shift_assign
where calendar_id = p_calendar_id
order by shift_sequence_number;
select shift_construct_id,
begin_time start_constr,
end_time end_constr,
availability_type_code
from jtf_cal_shift_constructs
where shift_id = p_shift_id
--
--Modified condition to take care first Day of week for Bug 1342982
--
and to_char(begin_time, 'd') = to_char(p_day, 'd')
-- and unit_of_time_value = p_uot_value
--end
-- validate shift construct
-- added by jawang on 06/07/2002 to fix bug 2393255
and ( ( p_day between start_date_active
and end_date_active)
or (start_date_active <=p_day
and end_date_active IS NULL));
select Decode(p_tz_enabled,'Y',
Decode(p_res_Timezone_id,NULL, e.start_date_time,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
p_server_tz_id,
e.start_date_time),
e.start_date_time)
),
e.start_date_time) start_except,
Decode(p_tz_enabled,'Y',
Decode(p_res_Timezone_id,NULL, e.end_date_time,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
p_server_tz_id,
e.end_date_time),
e.end_date_time)
),
e.end_date_time) end_except
from jtf_cal_exception_assign a
,jtf_cal_exceptions_vl e
where a.calendar_id = p_calendar_id
and a.exception_id = e.exception_id
-- validate exception assignment
and ( p_day >= trunc(Decode(p_tz_enabled,'Y',
Decode(p_res_Timezone_id,NULL, a.start_date_active,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
p_server_tz_id,
a.start_date_active),
a.start_date_active)
),
a.start_date_active))
or a.start_date_active is null)
and ( p_day <= trunc(Decode(p_tz_enabled,'Y',
Decode(p_res_Timezone_id,NULL, a.end_date_active,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
p_server_tz_id,
a.end_date_active),
a.end_date_active)
),
a.end_date_active))
or a.end_date_active is null)
-- validate exception
and p_day between trunc(Decode(p_tz_enabled,'Y',
Decode(p_res_Timezone_id,NULL, e.start_date_time,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
p_server_tz_id,
e.start_date_time),
e.start_date_time)
),
e.start_date_time))
and trunc(Decode(p_tz_enabled,'Y',
Decode(p_res_Timezone_id,NULL, e.end_date_time,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
p_server_tz_id,
e.end_date_time),
e.end_date_time)
),
e.end_date_time))
UNION ALL
-- we are picking up from scheduled date form tasks.
select Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_start_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_start_date),
t.scheduled_start_date)
),
t.scheduled_start_date) start_except,
Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_end_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_end_date),
t.scheduled_end_date)
),
t.scheduled_end_date) end_except
from jtf_tasks_b t,
jtf_task_assignments a,
jtf_task_statuses_b s --changed to table from jtf_task_statuses_vl bug #2473783
where a.resource_id = p_res_id
and a.resource_type_code = p_res_type
and Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_end_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_end_date),
t.scheduled_end_date)
),
t.scheduled_end_date) >= p_day -- Changed to "schedule_end_date" for bug 2817811 by A.Raina.
and Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_start_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_start_date),
t.scheduled_start_date)
),
t.scheduled_start_date) < p_day+1 --- removed trunc bug #2473783
and s.task_status_id = a.assignment_status_id
and t.task_id = a.task_id
and nvl(s.cancelled_flag,'N') <> 'Y'
and nvl(s.completed_flag,'N') <> 'Y'
and t.scheduled_start_date <> t.scheduled_end_date ; -- bug # 2520762
select a.calendar_id,b.start_date_active
into l_calendar_id,l_calendar_start_date
from jtf_cal_resource_assign a,
jtf_calendars_b b
where a.resource_id = p_resource_id
and a.resource_type_code = p_resource_type
and a.calendar_id = b.calendar_id
and a.primary_calendar_flag = 'Y'
and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
select sum(shift_end_date - shift_start_date)
into l_shifts_total_duration
from jtf_cal_shift_assign
where calendar_id = l_calendar_id;
l_tbl.delete;
l_tbl.delete;
select shift_id,(shift_end_date - shift_start_date) shift_duration
from jtf_cal_shift_assign
where calendar_id = p_calendar_id
order by shift_sequence_number;
select shift_construct_id,
begin_time start_constr,
end_time end_constr,
availability_type_code
from jtf_cal_shift_constructs
where shift_id = p_shift_id
--
--Modified condition to take care first Day of week for Bug 1342982
--
and to_char(begin_time, 'd') = to_char(p_day, 'd')
-- validate shift construct
-- added by jawang on 06/07/2002 to fix bug 2393255
and ( ( p_day between start_date_active
and end_date_active)
or (start_date_active <=p_day
and end_date_active IS NULL));
select e.start_date_time start_except
, e.end_date_time end_except
from jtf_cal_exception_assign a
, jtf_cal_exceptions_vl e
where a.calendar_id = p_calendar_id
and a.exception_id = e.exception_id
-- validate exception assignment
and ( p_day >= trunc(a.start_date_active)
or a.start_date_active is null)
and ( p_day <= trunc(a.end_date_active)
or a.end_date_active is null)
-- validate exception
and p_day between trunc(e.start_date_time)
and trunc(e.end_date_time);
select resource_name
into l_resource_name
--from jtf_rs_all_resources_vl
--Modified by jawang to fix the bug 2416932
from jtf_task_resources_vl
where resource_id = p_resource_id
and resource_type = p_resource_type;
select a.calendar_id,b.calendar_name,b.start_date_active,a.start_date_time
into l_calendar_id,l_calendar_name,l_calendar_start_date,l_start_date_time
from jtf_cal_resource_assign a,
jtf_calendars_vl b
where a.resource_id = p_resource_id
and a.resource_type_code = p_resource_type
and a.calendar_id = b.calendar_id
and a.primary_calendar_flag = 'Y'
-- Commented for bug 3891896 by ABRAINA
-- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
l_tbl.delete;
select sum(shift_end_date - shift_start_date)
into l_shifts_total_duration
from jtf_cal_shift_assign
where calendar_id = l_calendar_id;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete; -- if we find the exception and shift times are same then delete the row from table of records.
l_tbl.delete; -- to delete the record from TOR if no shift found
select shift_id,(shift_end_date - shift_start_date) shift_duration
from jtf_cal_shift_assign
where calendar_id = p_calendar_id
order by shift_sequence_number;
select shift_construct_id,
begin_time start_constr,
end_time end_constr,
availability_type_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from jtf_cal_shift_constructs
where shift_id = p_shift_id
--
--Modified condition to take care first Day of week for Bug 1342982
--
and to_char(begin_time, 'd') = to_char(p_day, 'd')
-- added by jawang on 06/07/2002 to fix bug 2393255
and ( ( p_day between start_date_active
and end_date_active)
or (start_date_active <=p_day
and end_date_active IS NULL));
select e.start_date_time start_except
, e.end_date_time end_except
from jtf_cal_exception_assign a
, jtf_cal_exceptions_vl e
where a.calendar_id = p_calendar_id
and a.exception_id = e.exception_id
-- validate exception assignment
and ( p_day >= trunc(a.start_date_active)
or a.start_date_active is null)
and ( p_day <= trunc(a.end_date_active)
or a.end_date_active is null)
-- validate exception
and p_day between trunc(e.start_date_time)
and trunc(e.end_date_time);
select start_date_time
from jtf_cal_resource_assign
where resource_id = p_resource_id
and resource_type_code = p_resource_type
and primary_calendar_flag = 'Y'
and (( p_shift_date >= trunc(start_date_time) and end_date_time IS NULL )
OR (p_shift_date between trunc(start_date_time)
and trunc(end_date_time)));
select resource_name
into l_resource_name
--from jtf_rs_all_resources_vl
--Modified by jawang to fix the bug 2416932
from jtf_task_resources_vl
where resource_id = p_resource_id
and resource_type = p_resource_type;
select a.calendar_id,b.calendar_name,b.start_date_active,a.start_date_time
into l_calendar_id,l_calendar_name,l_calendar_start_date,l_start_date_time
from jtf_cal_resource_assign a,
jtf_calendars_vl b
where a.resource_id = p_resource_id
and a.resource_type_code = p_resource_type
and a.calendar_id = b.calendar_id
and a.primary_calendar_flag = 'Y'
-- Commented for bug 3891896 by ABRAINA
-- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
select sum(shift_end_date - shift_start_date)
into l_shifts_total_duration
from jtf_cal_shift_assign
where calendar_id = l_calendar_id;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete;
l_tbl.delete; -- if we find the exception and shift times are same then delete the row from table of records.
l_tbl.delete; -- to delete the record from TOR if no shift found
select shift_id,(shift_end_date - shift_start_date) shift_duration
from jtf_cal_shift_assign
where calendar_id = p_calendar_id
order by shift_sequence_number;
select shift_construct_id,
begin_time shift_begin_time,
end_time shift_end_time,
availability_type_code
from jtf_cal_shift_constructs
where shift_id = p_shift_id
--
--Modified condition to take care first Day of week for Bug 1342982
--
and to_char(begin_time, 'd') = to_char(p_day, 'd')
-- added by jawang on 06/07/2002 to fix by 2393255
and ( ( p_day between start_date_active
and end_date_active)
or (start_date_active <=p_day
and end_date_active IS NULL));
select to_char(a.start_date_time,'HH24.MI') excp_start_time,
to_char(a.end_date_time,'HH24.MI') excp_end_time
from jtf_cal_exceptions_vl a, jtf_cal_exception_assign b
where trunc(a.start_date_time) = p_req_date
and a.exception_id = b.exception_id
and b.calendar_id = p_calendar_id;
select /*+ index_ss(T JTF_TASKS_B_N12) */
trunc(Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_start_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_start_date),
t.scheduled_start_date)
),
t.scheduled_start_date)
) task_start_date,
trunc(Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_end_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_end_date),
t.scheduled_end_date)
),
t.scheduled_end_date)
) task_end_date,
to_char(Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_start_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_start_date),
t.scheduled_start_date)
),
t.scheduled_start_date), 'HH24.MI'
) task_start_time,
to_char(Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_end_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_end_date),
t.scheduled_end_date)
),
t.scheduled_end_date), 'HH24.MI'
) task_end_time
from jtf_tasks_b t,
jtf_task_assignments a,
jtf_task_statuses_b s
where a.resource_id = p_res_id
and a.resource_type_code = p_res_type
and p_req_date between
trunc(Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_start_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_start_date),
t.scheduled_start_date)
),
t.scheduled_start_date)
)
and
Decode(p_tz_enabled,'Y',
Decode(t.timezone_id,NULL, t.scheduled_end_date,
Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
p_server_tz_id,
t.scheduled_end_date),
t.scheduled_end_date)
),
t.scheduled_end_date)
and s.task_status_id = a.assignment_status_id
AND t.task_id = a.task_id
and nvl(s.cancelled_flag,'N') <> 'Y'
and nvl(s.completed_flag,'N') <> 'Y'
and t.scheduled_start_date <> t.scheduled_end_date ; -- bug # 2520762
select resource_name
into l_resource_name
--from jtf_rs_all_resources_vl
--Modified by jawang to fix the bug 2416932
from jtf_task_resources_vl
where resource_id = p_resource_id
and resource_type = p_resource_type;
x_shift.delete;
select a.calendar_id,b.calendar_name,b.start_date_active
into l_calendar_id,l_calendar_name,l_calendar_start_date
from jtf_cal_resource_assign a,
jtf_calendars_vl b
where a.resource_id = p_resource_id
and a.resource_type_code = p_resource_type
and a.calendar_id = b.calendar_id
and a.primary_calendar_flag = 'Y'
-- Commented for bug 3891896 by ABRAINA
-- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
select sum(shift_end_date - shift_start_date)
into l_shifts_total_duration
from jtf_cal_shift_assign
where calendar_id = l_calendar_id;
select mod((l_shift_date - l_calendar_start_date),l_shifts_total_duration)
into l_left_days
from dual;
select to_char(l_shift_date, 'd')
into l_unit_of_time_value
from dual;
Select TIME_ZONE
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = p_resource_id
AND 'RS_'||category = p_resource_type
And trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
and trunc(nvl(END_DATE_ACTIVE,sysdate));
SELECT TIME_ZONE
FROM JTF_RS_GROUPS_B
WHERE group_id = p_resource_id
AND trunc(SYSDATE) BETWEEN trunc(nvl(START_DATE_ACTIVE,SYSDATE))
AND trunc(nvl(END_DATE_ACTIVE,SYSDATE));
select 1
into v_valid_cal
from jtf_calendars_b a
where calendar_id = P_Calendar_Id
-- Commented for bug 3891896 by ABRAINA
-- and P_shift_date between trunc(a.start_date_active) and nvl(trunc(a.end_date_active),to_date(get_g_miss_date,'DD/MM/RRRR'));