[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x'
from jtf_tasks_b
where source_object_type_code = 'APPOINTMENT'
and rownum = 1;
PROCEDURE update_invalid_repeating_appts
IS
-----------------------------------------------------------
-- For selecting duplicate repeating appointments
-- among unchanged records
-----------------------------------------------------------
CURSOR c_duplicates (b_recurrence_rule_id NUMBER
,b_recur_creation_date DATE) IS
SELECT t.task_id
FROM jtf_tasks_b t
, (SELECT recurrence_rule_id
, calendar_start_date
, count(task_id) cnt
, min(task_id) min_task_id
FROM jtf_tasks_b
WHERE recurrence_rule_id = b_recurrence_rule_id
AND (last_update_date <= b_recur_creation_date OR
last_update_date = creation_date)
AND source_object_type_code = 'APPOINTMENT'
HAVING count(task_id) > 1
GROUP BY recurrence_rule_id, calendar_start_date
) dup
WHERE t.recurrence_rule_id = dup.recurrence_rule_id
AND t.calendar_start_date = dup.calendar_start_date
AND t.task_id <> dup.min_task_id;
SELECT task_id
, calendar_start_date
, created_by
FROM jtf_tasks_b
WHERE recurrence_rule_id = b_recurrence_rule_id
AND (last_update_date <= b_recur_creation_date OR
last_update_date = creation_date)
AND source_object_type_code = 'APPOINTMENT'
ORDER BY calendar_start_date, task_id;
SELECT recurrence_rule_id
, occurs_which
, day_of_week
, date_of_month
, occurs_number
, occurs_month
, occurs_uom
, occurs_every
, start_date_active
, end_date_active
, sunday
, monday
, tuesday
, wednesday
, thursday
, friday
, saturday
, created_by
, creation_date
FROM jtf_task_recur_rules r
WHERE EXISTS (SELECT 1
FROM jtf_tasks_b jtb
WHERE jtb.recurrence_rule_id = r.recurrence_rule_id
AND jtb.source_object_type_code = 'APPOINTMENT'
AND (jtb.object_changed_date = to_date('01/02/1970','MM/DD/YYYY') OR
jtb.object_changed_date IS NULL)
);
UPDATE jtf_tasks_b
SET recurrence_rule_id = NULL
, last_updated_by = fnd_global.user_id
WHERE task_id = rec_duplicates.task_id;
l_output_dates_tbl.DELETE(i);
UPDATE jtf_tasks_b
SET recurrence_rule_id = NULL
, last_updated_by = fnd_global.user_id
WHERE task_id = rec_repeat_appt.task_id;
SELECT jta_task_exclusions_s.NEXTVAL
INTO l_task_exclusion_id
FROM DUAL;
jta_task_exclusions_pkg.insert_row (
p_task_exclusion_id => l_task_exclusion_id,
p_task_id => 0-i,
p_recurrence_rule_id => rec_recur.recurrence_rule_id,
p_exclusion_date => l_exclusion_date,
p_created_by => rec_recur.created_by
);
raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (update_invalid_repeating_appts) : '||SQLERRM(SQLCODE));
END update_invalid_repeating_appts;
SELECT /*+ parallel(b) */ b.rowid row_id
FROM jtf_tasks_b b
WHERE b.object_changed_date IS NULL;
UPDATE jtf_tasks_b
SET object_changed_date = TO_DATE('01/02/1970', 'MM/DD/YYYY')
, last_updated_by = fnd_global.user_id
WHERE rowid = rec_tasks.row_id;
CURSOR c_updated_appts IS
SELECT /*+ parallel(t) */ t.task_id
, t.recurrence_rule_id
, t.calendar_start_date
, t.created_by
, t.deleted_flag
, recur.creation_date recur_creation_date
FROM jtf_tasks_b t
, jtf_task_recur_rules recur
WHERE ((t.creation_date <> t.last_update_date AND
t.last_update_date > recur.creation_date
) OR
deleted_flag = 'Y')
AND t.recurrence_rule_id = recur.recurrence_rule_id
AND t.source_object_type_code = 'APPOINTMENT'
AND (t.object_changed_date = to_date('01/02/1970','MM/DD/YYYY') OR
t.object_changed_date IS NULL)
ORDER BY t.calendar_start_date, t.task_id;
SELECT task_id
FROM jtf_tasks_b
WHERE recurrence_rule_id = b_recurrence_rule_id
AND calendar_start_date = b_calendar_start_date
AND task_id <> b_task_id;
SELECT recurrence_rule_id
, occurs_which
, day_of_week
, date_of_month
, occurs_number
, occurs_month
, occurs_uom
, occurs_every
, start_date_active
, end_date_active
, sunday
, monday
, tuesday
, wednesday
, thursday
, friday
, saturday
, created_by
FROM jtf_task_recur_rules
WHERE recurrence_rule_id = b_recurrence_rule_id;
l_updated BOOLEAN;
FOR rec_updated_appts IN c_updated_appts
LOOP
l_updated := FALSE;
OPEN c_duplicate_dates(rec_updated_appts.recurrence_rule_id
--,rec_updated_appts.recur_creation_date
,rec_updated_appts.calendar_start_date
,rec_updated_appts.task_id);
OPEN c_recur (rec_updated_appts.recurrence_rule_id);
l_recurrence_rule_id := rec_updated_appts.recurrence_rule_id;
IF TRUNC(rec_updated_appts.calendar_start_date) = TRUNC(l_output_dates_tbl(i))
THEN
l_start_date_or_extra_modified := FALSE;
SELECT jta_task_exclusions_s.NEXTVAL
INTO l_task_exclusion_id
FROM DUAL;
jta_task_exclusions_pkg.insert_row (
p_task_exclusion_id => l_task_exclusion_id,
p_task_id => rec_updated_appts.task_id,
p_recurrence_rule_id => rec_updated_appts.recurrence_rule_id,
p_exclusion_date => rec_updated_appts.calendar_start_date,
p_created_by => rec_updated_appts.created_by
);
l_updated := TRUE;
IF NVL(rec_updated_appts.deleted_flag,'N') = 'N'
THEN
UPDATE jtf_tasks_b
SET recurrence_rule_id = NULL
, last_updated_by = fnd_global.user_id
WHERE task_id = rec_updated_appts.task_id;
l_updated := TRUE;
IF l_updated
THEN
l_num := l_num + 1;
SELECT *
FROM jtf_task_recur_rules r
WHERE (end_date_active IS NULL OR occurs_number IS NULL)
AND EXISTS (SELECT 1
FROM jtf_tasks_b t
WHERE t.source_object_type_code = 'APPOINTMENT'
AND t.recurrence_rule_id = r.recurrence_rule_id);
UPDATE jtf_task_recur_rules
SET occurs_number = l_occurs_number
, end_date_active = l_max_date
, last_updated_by = fnd_global.user_id
WHERE recurrence_rule_id = rec_recur.recurrence_rule_id;
SELECT DISTINCT
t.task_id
, a.task_assignment_id
FROM jtf_task_all_assignments a
, jtf_tasks_b t
, (SELECT /*+ parallel(jtb) */ jtb.recurrence_rule_id
, jtaa.resource_id
, SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) num_of_accept
, SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) num_of_reject
, SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) num_of_invitee
FROM jtf_task_all_assignments jtaa
, jtf_tasks_b jtb
WHERE jtb.recurrence_rule_id IS NOT NULL
AND jtb.source_object_type_code = 'APPOINTMENT'
AND jtaa.task_id = jtb.task_id
AND jtaa.assignee_role = 'ASSIGNEE'
AND jtaa.assignment_status_id IN (18, 3, 4)
HAVING NOT
((SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) > 0 AND SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) = 0) OR
(SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) > 0 AND SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) = 0) OR
(SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) > 0))
GROUP BY jtb.recurrence_rule_id, jtaa.resource_id
ORDER BY jtb.recurrence_rule_id, jtaa.resource_id
) m
WHERE t.recurrence_rule_id = m.recurrence_rule_id
AND a.task_id = t.task_id
AND a.resource_id = m.resource_id;
UPDATE jtf_task_all_assignments
SET assignment_status_id = 18
, last_updated_by = fnd_global.user_id
WHERE task_assignment_id = rec_appt_with_mixed_status.task_assignment_id;
SELECT *
FROM jtf_task_recur_rules r
WHERE EXISTS (SELECT 1
FROM jtf_tasks_b t
WHERE t.source_object_type_code = 'APPOINTMENT'
AND t.recurrence_rule_id = r.recurrence_rule_id);
SELECT a.resource_id
, a.resource_type_code
, count(a.task_assignment_id)
FROM jtf_task_all_assignments a
, jtf_tasks_b t
WHERE t.recurrence_rule_id = b_recurrence_rule_id
AND t.source_object_type_code = 'APPOINTMENT'
AND (t.object_changed_date = to_date('01/02/1970','MM/DD/YYYY') OR
t.object_changed_date IS NULL)
AND a.task_id = t.task_id
AND a.assignee_role = 'ASSIGNEE'
HAVING count(a.task_assignment_id) < b_valid_count
GROUP BY a.resource_id, a.resource_type_code
ORDER BY a.resource_id, a.resource_type_code;
UPDATE jtf_tasks_b
SET recurrence_rule_id = NULL
, last_updated_by = fnd_global.user_id
WHERE recurrence_rule_id = rec_recur.recurrence_rule_id
AND NVL(deleted_flag,'N') <> 'Y';