[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gmt_deviation_hours
INTO l_server_offset
FROM hz_timezones
WHERE timezone_id = l_server_tz_id;
Select calendar_start_date, timezone_id From jtf_tasks_b Where task_id=p_task_id;
SELECT DECODE (p_occurs_which, 1, 'FIRST', 2, 'SECOND', 3, 'THIRD', 4, 'FOUR', 99, 'LAST')
INTO l_occurs_which
FROM dual;
SELECT DECODE (
p_day_of_week,
1, 'SUNDAY',
2, 'MONDAY',
3, 'TUESDAY',
4, 'WEDNESDAY',
5, 'THURSDAY',
6, 'FRIDAY',
7, 'SATURDAY',
0, 'DAY',
8, 'WEEKDAY',
9, 'WEEKEND'
)
INTO l_day_of_week
FROM dual;
SELECT object_version_number
FROM jtf_tasks_b
WHERE task_id = b_task_id;
PROCEDURE set_last_update_date(p_recurrence_rule_id IN NUMBER)
IS
-- Fix bug 2376554
--CURSOR c_last_update_date IS
--select max(last_update_date)
-- from jtf_tasks_b t
--where t.recurrence_rule_id = p_recurrence_rule_id
-- and deleted_flag <> 'Y';
UPDATE jtf_task_recur_rules
SET last_update_date = l_date
, creation_date = l_date
WHERE recurrence_rule_id = p_recurrence_rule_id;
select min(planned_start_date)
from jtf_tasks_b t
where t.recurrence_rule_id = p_recurrence_rule_id
and deleted_flag <> 'Y';
select max(planned_end_date)
from jtf_tasks_b t
where t.recurrence_rule_id = p_recurrence_rule_id
and deleted_flag <> 'Y';
l_date_selected VARCHAR(1);
SELECT 1
FROM jtf_task_recur_rules
WHERE ROWID = l_rowid;
SELECT planned_start_date
, planned_end_date
, scheduled_start_date
, scheduled_end_date
, actual_start_date
, actual_end_date
, task_status_id
, creation_date
FROM jtf_tasks_b
WHERE task_id = p_task_id;
select planned_effort,
planned_effort_uom
from jtf_tasks_b
where task_id=p_task_id;
select task_assignment_id,
actual_start_date,
actual_end_date,
actual_travel_duration,
actual_travel_duration_uom,
actual_effort,
actual_effort_uom
from jtf_task_all_assignments
where task_id = p_task_id;
SELECT recurrence_rule_id
INTO l_recur_id
FROM jtf_task_templates_b
WHERE task_template_id = l_task_id;
SELECT recurrence_rule_id
INTO l_recur_id
FROM jtf_tasks_b
WHERE task_id = l_task_id;
SELECT date_selected
INTO l_date_selected
FROM jtf_tasks_b
WHERE task_id = p_task_id;
l_date_selected := null;
SELECT jtf_task_recur_rules_s.nextval
INTO l_recur_id
FROM dual;
jtf_task_recur_rules_pkg.insert_row (
x_rowid => l_rowid,
x_recurrence_rule_id => l_recur_id,
x_occurs_which => p_occurs_which,
x_day_of_week => p_day_of_week,
x_date_of_month => p_date_of_month,
x_occurs_month => p_occurs_month,
x_occurs_uom => p_occurs_uom,
x_occurs_every => p_occurs_every,
x_occurs_number => p_occurs_number,
x_start_date_active => p_start_date_active,
x_end_date_active => p_end_date_active,
x_attribute1 => p_attribute1 ,
x_attribute2 => p_attribute2 ,
x_attribute3 => p_attribute3 ,
x_attribute4 => p_attribute4 ,
x_attribute5 => p_attribute5 ,
x_attribute6 => p_attribute6 ,
x_attribute7 => p_attribute7 ,
x_attribute8 => p_attribute8 ,
x_attribute9 => p_attribute9 ,
x_attribute10 => p_attribute10 ,
x_attribute11 => p_attribute11 ,
x_attribute12 => p_attribute12 ,
x_attribute13 => p_attribute13 ,
x_attribute14 => p_attribute14 ,
x_attribute15 => p_attribute15,
x_attribute_category => p_attribute_category ,
x_creation_date => SYSDATE,
x_created_by => jtf_task_utl.created_by,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_task_utl.updated_by,
x_last_update_login => fnd_global.login_id,
x_sunday => p_sunday,
x_monday => p_monday,
x_tuesday => p_tuesday,
x_wednesday => p_wednesday,
x_thursday => p_thursday,
x_friday => p_friday,
x_saturday => p_saturday,
x_date_selected => l_date_selected
);
fnd_message.set_name ('JTF', 'ERROR_INSERTING_RECURRENCE');
UPDATE jtf_task_templates_b
SET recurrence_rule_id = l_recur_id
WHERE task_template_id = l_task_id;
UPDATE jtf_tasks_b
SET recurrence_rule_id = l_recur_id
WHERE task_id = l_task_id;
IF l_date_selected = 'P' OR l_date_selected IS NULL
THEN
IF ( l_task_details.planned_end_date IS NULL AND
l_task_details.planned_start_date IS NOT NULL
)
THEN
l_planned_start_date :=
get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.planned_start_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
, l_task_details.planned_start_date
);
ELSIF l_date_selected = 'S'
THEN
IF ( l_task_details.scheduled_end_date IS NULL AND
l_task_details.scheduled_start_date IS NOT NULL
)
THEN
l_scheduled_start_date :=
get_dst_corrected_date(TO_DATE (TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.scheduled_start_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
,l_task_details.scheduled_start_date
);
ELSIF l_date_selected = 'A'
THEN
IF ( l_task_details.actual_end_date IS NULL AND
l_task_details.actual_start_date IS NOT NULL
)
THEN
l_actual_start_date :=
get_dst_corrected_date(TO_DATE (to_char(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.actual_start_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
,l_task_details.actual_start_date
);
IF l_date_selected = 'P' OR l_date_selected IS NULL
THEN
--change or add dates for scheduled date fields with the same pattern
IF ( l_task_details.scheduled_end_date IS NULL
AND l_task_details.scheduled_start_date IS NOT NULL)
THEN
l_scheduled_start_date :=
l_task_details.scheduled_start_date +(l_planned_start_date - l_task_details.planned_start_date);
ELSIF l_date_selected = 'S'
THEN
--change or add dates for planned date fields with the same pattern
IF ( l_task_details.planned_end_date IS NULL
AND l_task_details.planned_start_date IS NOT NULL)
THEN
l_planned_start_date :=
l_task_details.planned_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
ELSIF l_date_selected = 'A'
THEN
--change or add dates for planned date fields with the same pattern
IF ( l_task_details.planned_end_date IS NULL
AND l_task_details.planned_start_date IS NOT NULL)
THEN
l_planned_start_date :=
l_task_details.planned_start_date + (l_actual_start_date - l_task_details.actual_start_date);
ELSIF l_date_selected = 'D'
THEN
l_planned_start_date := NULL;
IF l_date_selected IS NULL -- Added by lokumar for bug#6067036
THEN
l_calendar_start_date := NULL;
ELSIF l_date_selected = 'P' --OR l_date_selected IS NULL -- Commented out by lokumar for bug#6067036
THEN
if ((p_end_date_active is null or trunc(l_planned_start_date) <= trunc(p_end_date_active))
--and trunc(p_start_date_active) <> trunc(l_planned_start_date) then
and trunc(l_task_details.planned_start_date) <> trunc(l_planned_start_date))
OR l_date_selected IS NULL -- Fix bug 2376554
then
l_calendar_start_date := l_planned_start_date; -- Fix bug 2376554
ELSIF l_date_selected = 'S'
THEN
if (p_end_date_active is null or trunc(l_scheduled_start_date) <= trunc(p_end_date_active))
--and trunc(p_start_date_active) <> trunc(l_scheduled_start_date) then
and trunc(l_task_details.scheduled_start_date) <> trunc(l_scheduled_start_date) then
l_calendar_start_date := l_scheduled_start_date; -- Fix bug 2376554
ELSIF l_date_selected = 'A'
THEN
if (p_end_date_active is null or trunc(l_actual_start_date) <= trunc(p_end_date_active))
--and trunc(p_start_date_active) <> trunc(l_actual_start_date) then
and trunc(l_task_details.actual_start_date) <> trunc(l_actual_start_date) then
l_calendar_start_date := l_actual_start_date; -- Fix bug 2376554
ELSIF l_date_selected = 'D'
THEN
jtf_task_utl_ext.set_start_n_due_date (
p_task_status_id => l_task_details.task_status_id
, p_planned_start_date => l_planned_start_date
, p_planned_end_date => l_planned_end_date
, p_scheduled_start_date => l_scheduled_start_date
, p_scheduled_end_date => l_scheduled_end_date
, p_actual_start_date => l_actual_start_date
, p_actual_end_date => l_actual_end_date
, p_creation_date => l_task_details.creation_date
, x_calendar_start_date => l_calendar_start_date
, x_calendar_end_date => l_calendar_end_date
, x_return_status => x_return_status);
UPDATE jtf_tasks_b
SET recurrence_rule_id = l_recur_id,
planned_start_date = l_planned_start_date ,
planned_end_date = l_planned_end_date,
scheduled_start_date = l_scheduled_start_date ,
scheduled_end_date = l_scheduled_end_date,
actual_start_date = l_actual_start_date ,
actual_end_date = l_actual_end_date,
calendar_start_date = l_calendar_start_date ,
calendar_end_date = l_calendar_end_date,
creation_date = l_current,
last_update_date = l_current,
date_selected = l_date_selected -- Added by lokumar for bug#6067036
WHERE task_id = l_recur_task_id ;
UPDATE jtf_tasks_b
SET recurrence_rule_id = l_recur_id,
planned_start_date = l_planned_start_date ,
planned_end_date = l_planned_end_date,
scheduled_start_date = l_scheduled_start_date ,
scheduled_end_date = l_scheduled_end_date,
actual_start_date = l_actual_start_date ,
actual_end_date = l_actual_end_date,
calendar_start_date = l_calendar_start_date ,
calendar_end_date = l_calendar_end_date,
creation_date = l_current,
last_update_date = l_current,
date_selected = l_date_selected -- Added by lokumar for bug#6067036
WHERE task_id = l_task_id ;
UPDATE jtf_task_all_assignments
SET booking_start_date = l_booking_start_date,
booking_end_date = l_booking_end_date
WHERE task_assignment_id = i.task_assignment_id;
UPDATE jtf_task_recur_rules
SET start_date_active = TRUNC(NVL(l_repeat_start_date, l_output_dates_tbl(1))) -- Fix bug 2376554, bug 2385202
WHERE recurrence_rule_id = l_recur_id;
UPDATE jtf_task_recur_rules
SET end_date_active = trunc(l_output_dates_tbl(l_last)) --trunc(NVL(l_repeat_end_date,l_output_dates_tbl(l_last))) -- Fix bug 2376554, bug 2385202
WHERE recurrence_rule_id = l_recur_id;
set_last_update_date(l_recur_id);
SELECT 1
FROM jtf_task_recur_rules
WHERE recurrence_rule_id = p_recurrence_rule_id
AND TRUNC(start_date_active) = TRUNC(p_target_repeat_start_date);
PROCEDURE update_task_recurrence (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_task_id IN NUMBER,
p_recurrence_rule_id IN NUMBER,
p_occurs_which IN INTEGER DEFAULT NULL,
p_day_of_week IN INTEGER DEFAULT NULL,
p_date_of_month IN INTEGER DEFAULT NULL,
p_occurs_month IN INTEGER DEFAULT NULL,
p_occurs_uom IN VARCHAR2 DEFAULT NULL,
p_occurs_every IN INTEGER DEFAULT NULL,
p_occurs_number IN INTEGER DEFAULT NULL,
p_start_date_active IN DATE DEFAULT NULL,
p_end_date_active IN DATE DEFAULT NULL,
p_template_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_attribute1 IN VARCHAR2 DEFAULT NULL ,
p_attribute2 IN VARCHAR2 DEFAULT NULL ,
p_attribute3 IN VARCHAR2 DEFAULT NULL ,
p_attribute4 IN VARCHAR2 DEFAULT NULL ,
p_attribute5 IN VARCHAR2 DEFAULT NULL ,
p_attribute6 IN VARCHAR2 DEFAULT NULL ,
p_attribute7 IN VARCHAR2 DEFAULT NULL ,
p_attribute8 IN VARCHAR2 DEFAULT NULL ,
p_attribute9 IN VARCHAR2 DEFAULT NULL ,
p_attribute10 IN VARCHAR2 DEFAULT NULL ,
p_attribute11 IN VARCHAR2 DEFAULT NULL ,
p_attribute12 IN VARCHAR2 DEFAULT NULL ,
p_attribute13 IN VARCHAR2 DEFAULT NULL ,
p_attribute14 IN VARCHAR2 DEFAULT NULL ,
p_attribute15 IN VARCHAR2 DEFAULT NULL ,
p_attribute_category IN VARCHAR2 DEFAULT NULL ,
p_sunday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_monday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_tuesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_wednesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_thursday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_friday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
p_saturday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
x_new_recurrence_rule_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR c_task (b_task_id NUMBER) IS
SELECT MAX(t.calendar_start_date) calendar_start_date
, COUNT(t.task_id) occurs_number
FROM jtf_tasks_b t
, jtf_tasks_b curr_task
WHERE curr_task.task_id = b_task_id
AND t.recurrence_rule_id = curr_task.recurrence_rule_id
AND t.calendar_start_date < curr_task.calendar_start_date;
SELECT TRUNC(calendar_start_date) calendar_start_date
FROM jtf_tasks_b
WHERE task_id = b_task_id;
l_delete_future_recurrences VARCHAR2(1);
SAVEPOINT update_task_recurrence_pvt;
UPDATE jtf_task_all_assignments
SET assignment_status_id = 18
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE task_id = l_new_task_id
AND assignee_role = 'ASSIGNEE';
l_delete_future_recurrences := 'A'; -- Delete all the occurrences
l_delete_future_recurrences := fnd_api.g_true; -- Delete the future occrrences
jtf_tasks_pvt.delete_task (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_object_version_number => l_object_version_number,
p_task_id => p_task_id,
p_delete_future_recurrences => l_delete_future_recurrences,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE jtf_task_recur_rules
SET end_date_active = TRUNC(rec_task.calendar_start_date)
, occurs_number = rec_task.occurs_number
WHERE recurrence_rule_id = p_recurrence_rule_id;
UPDATE jta_sync_task_mapping
SET task_id = l_new_task_id
WHERE task_id = p_task_id;
ROLLBACK TO update_task_recurrence_pvt;
ROLLBACK TO update_task_recurrence_pvt;
END update_task_recurrence;