[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
and ((start_date_active <=p_day and end_date_active IS NULL) /* bug# 2408759 */
or (p_day between start_date_active and end_date_active))
and (
(
trunc(begin_time) <= trunc(p_uot_value)
and
trunc(end_time) >= trunc(p_uot_value)
)
or
(
trunc(begin_time) <= to_date('1995/01/07','YYYY/MM/DD') +
to_number(substr(to_char(trunc(p_uot_value), 'YYYY/MM/DD'),9,2))
and
trunc(end_time) >= to_date('1995/01/07','YYYY/MM/DD') +
to_number(substr(to_char(trunc(p_uot_value), 'YYYY/MM/DD'),9,2))
)
)
order by begin_time;
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,
nvl(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) ,p_start) start_assign,
nvl(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),p_end) end_assign
from jtf_cal_exception_assign a
,jtf_cal_exceptions_b e
where a.calendar_id = p_calendar_id
and a.exception_id = e.exception_id
and 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) <= p_end
and 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) >= p_start
and nvl(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),p_end) <= p_end --starts before end of range
and nvl(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),p_start) >= p_start -- end after start of range
UNION ALL
-- For bug 4547539, added db index skip hint to force db to use second indexed
-- column schedule_end_date for index search
-- Removed /*+ index_ss(T JTF_TASKS_B_N12) */ Hint to address performance issue Bug # 5167257 By MPADHIAR
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,
p_start start_assign,
p_end end_assign
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 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)
<= (trunc(p_end)+86399/84400)
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)
>= trunc(p_start)
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
order by 1,2; -- bug # 2520762
select a.calendar_id, a.start_date_time, a.end_date_time,b.start_date_active, b.end_date_active
from jtf_cal_resource_assign a,
jtf_calendars_b b
where a.resource_id = p_res_id
and a.resource_type_code = p_res_type
and a.calendar_id = b.calendar_id
and a.primary_calendar_flag = 'Y'
and trunc(a.start_date_time) <= p_end
and nvl(trunc(a.end_date_time),p_start) >= p_start
and trunc(b.start_date_active) <= p_end
and nvl(trunc(b.end_date_active),p_start) >= p_start
order by b.start_date_active;
l_shift_tbl.delete;
l_tbl.delete;
l_tbl.delete(l_idx);
l_tbl.delete(l_idx);
x_shift.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
and ((start_date_active <=p_day and end_date_active IS NULL) /* bug# 2408759 */
or (p_day between start_date_active and end_date_active))
and (
(
trunc(begin_time) <= trunc(p_uot_value)
and
trunc(end_time) >= trunc(p_uot_value)
)
or
(
trunc(begin_time) <= to_date('1995/01/07','YYYY/MM/DD') +
to_number(substr(to_char(trunc(p_uot_value), 'YYYY/MM/DD'),9,2))
and
trunc(end_time) >= to_date('1995/01/07','YYYY/MM/DD') +
to_number(substr(to_char(trunc(p_uot_value), 'YYYY/MM/DD'),9,2))
)
) ;
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
and ((start_date_active <=p_day and end_date_active IS NULL) /* bug# 2408759 */
or (p_day between start_date_active and end_date_active))
-- and unit_of_time_value = p_uot_value;
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 shift_begin_time,
end_time shift_end_time,
availability_type_code
from jtf_cal_shift_constructs
where shift_id = p_shift_id
and ((start_date_active <=p_day and end_date_active IS NULL) /* bug# 2408759 */
or (p_day between start_date_active and end_date_active))
and
(
(
trunc(begin_time) <= trunc(p_uot_value)
and
trunc(end_time) >= trunc(p_uot_value)
)
or
(
trunc(begin_time) <= to_date('1995/01/07','YYYY/MM/DD') +
to_number(substr(to_char(trunc(p_uot_value), 'YYYY/MM/DD'),9,2))
and
trunc(end_time) >= to_date('1995/01/07','YYYY/MM/DD') +
to_number(substr(to_char(trunc(p_uot_value), 'YYYY/MM/DD'),9,2))
)
)
order by begin_time;
select e.start_date_time excp_start_time,
e.end_date_time excp_end_time
from jtf_cal_exceptions_vl e, jtf_cal_exception_assign a
where a.calendar_id = p_calendar_id
and a.exception_id = e.exception_id
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 /*+ 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,
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_time,
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_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;
l_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 1 INTO nDummy FROM jtf_task_resources_vl
WHERE resource_id = p_resource_id AND resource_type = p_resource_type
AND ((start_date_active IS NULL AND end_date_active IS NULL)
OR (start_date_active <= SYSDATE AND end_date_active IS NULL)
OR (SYSDATE BETWEEN start_date_active AND end_date_active));
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_vl 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'));