[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 01-FEB-2005 rhshriva Changed update_existing_data |
| 03-FEB-2004 rhshriva Changed create_new_data and do_mapping |
| 26-SEP-2005 deeprao Changed delete_task_data |
| and delete_bookings, added delete_tasks |
*=======================================================================*/
g_fb_type_changed boolean := false;
SELECT 1
FROM jtf_task_recur_rules jtrr, jtf_tasks_b jtb
WHERE ((jtrr.occurs_uom ='YER' AND
jtrr.occurs_every > 1)
OR
(jtrr.occurs_uom ='MON'
AND (DECODE(sunday,'Y',1,0) + DECODE(monday,'Y',1,0) + DECODE(tuesday,'Y',1,0)
+ DECODE(wednesday,'Y',1,0) + DECODE(thursday,'Y',1,0) + DECODE(friday,'Y',1,0)
+ DECODE(saturday,'Y',1,0)) > 1)) AND
jtb.recurrence_rule_id=jtrr.recurrence_rule_id
AND jtb.task_id=b_task_id;
SELECT task_id FROM jta_task_exclusions WHERE
task_id=b_exclude_task_id;
l_update_exclusion BOOLEAN:=FALSE;
l_update_exclusion:=TRUE;
l_update_exclusion:=FALSE;
if ((p_exclusion_tbl(i).eventType <> g_delete) ) then
--creating exclusion
if (l_update_exclusion) then
delete from jta_task_exclusions
where task_id=l_exclude_task_id
and recurrence_rule_id=p_rec_rule_id;
end if;-- if (l_update_exclusion) then
delete_exclusion_task (
p_repeating_task_id => l_exclude_task_id,
x_task_rec => p_task_rec
);
SELECT COLLAB_ID, MEETING_MODE,MEETING_ID,MEETING_URL,JOIN_URL ,
PLAYBACK_URL ,DOWNLOAD_URL ,CHAT_URL ,IS_STANDALONE_LOCATION,DIAL_IN
FROM CAC_VIEW_COLLAB_DETAILS_VL
WHERE task_id=b_task_id;
p_updation_record jtf_task_repeat_appt_pvt.updated_field_rec;
jtf_task_repeat_appt_pvt.update_repeat_appointment(
p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_object_version_number =>l_ovn,
p_updated_field_rec =>p_updation_record ,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data
) ;
THEN-- Failed to update a task
cac_sync_common.put_message_to_excl_record (
p_exclusion_rec=>p_exclusion,
p_status => 2,
p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
);
cac_view_collab_details_pkg.update_row
(x_collab_id=> l_collab_details.collab_id ,
x_task_id=> p_updation_record.task_id,
x_meeting_mode=>l_collab_details.meeting_mode,
x_meeting_id=>l_collab_details.meeting_id,
x_meeting_url=>l_collab_details.meeting_url,
x_join_url=>l_collab_details.join_url,
x_playback_url=>l_collab_details.playback_url,
x_download_url=>l_collab_details.download_url,
x_chat_url=>l_collab_details.chat_url,
x_is_standalone_location=>l_collab_details.is_standalone_location,
x_location=>l_location,
x_dial_in=>p_exclusion.dial_in,
x_last_update_date=>SYSDATE,
x_last_updated_by=>jtf_task_utl.updated_by,
x_last_update_login=>jtf_task_utl.login_id);
SELECT task_id
FROM jta_sync_task_mapping
WHERE task_sync_id = p_sync_id;
SELECT COUNT (recurrence_rule_id)
INTO l_count
FROM jta_task_exclusions
WHERE recurrence_rule_id = p_recurrence_rule_id;
SELECT COUNT (recurrence_rule_id)
INTO l_count
FROM jtf_tasks_b
WHERE recurrence_rule_id = p_recurrence_rule_id;
SELECT task_id
FROM jtf_tasks_b
WHERE recurrence_rule_id = b_recurrence_rule_id
AND TRUNC (calendar_start_date) = TRUNC (b_exclusion_start_date);
l_date_selected VARCHAR2(1);
CURSOR c_dateselect
IS
SELECT jt.date_selected
FROM jtf_tasks_b jt
WHERE jt.task_id = p_task_id;
OPEN c_dateselect;
FETCH c_dateselect INTO l_date_selected;
IF c_dateselect%NOTFOUND
OR l_date_selected = 'P'
OR p_request_type = G_REQ_APPOINTMENT
THEN
l_date := p_planned_start_date;
ELSIF l_date_selected = 'S'
THEN
l_date := p_scheduled_start_date;
ELSIF l_date_selected = 'A'
THEN
l_date := p_actual_start_date;
CLOSE c_dateselect;
SELECT task_id
FROM jta_sync_task_mapping
WHERE task_sync_id = p_sync_id;
SELECT task_id
FROM jtf_task_all_assignments
WHERE task_assignment_id = p_task_assignment_id;
SELECT timezone_id
FROM jtf_tasks_b
WHERE task_id = p_task_id;
SELECT object_version_number
FROM jtf_tasks_b
WHERE task_id = b_task_id;
SELECT object_version_number
FROM jtf_task_all_assignments
WHERE task_assignment_id = b_task_assignment_id;
SELECT resource_id, 'RS_' || category
FROM jtf_rs_resource_extns
WHERE user_id = fnd_global.user_id;
SELECT jta_sync_task_mapping_s.nextval
INTO x_task_sync_id
FROM dual;
cac_sync_task_map_pkg.insert_row (
p_task_sync_id => x_task_sync_id,
p_task_id => p_task_id,
p_resource_id => cac_sync_task.g_login_resource_id,
p_principal_id => p_principal_id
);
cac_sync_task_map_pkg.update_row (
p_task_sync_id => x_task_sync_id,
p_task_id => p_task_id,
p_resource_id => cac_sync_task.g_login_resource_id,
p_principal_id => p_principal_id
);
/* ELSIF p_operation = G_DELETE
THEN
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => x_task_sync_id
);*/
p_deleted_flag IN VARCHAR2,
p_task_sync_id IN NUMBER,
p_source_object_type_code IN VARCHAR2,
p_calendar_start_date IN DATE,
p_calendar_end_date IN DATE,
p_assignment_status_id IN NUMBER,
x_operation OUT NOCOPY VARCHAR2
)
IS
l_deleted_flag VARCHAR2(1) := NVL (p_deleted_flag, 'N');
x_operation := G_DELETE;
IF l_deleted_flag = 'Y' OR
p_assignment_status_id = 4 -- Rejected
THEN
x_operation := G_DELETE;
ELSE -- l_deleted_flag = 'N'
x_operation := G_MODIFY;
IF l_deleted_flag = 'N' AND
nvl(p_assignment_status_id,-1) <> 4 -- Not Rejected
THEN
x_operation := G_NEW;
SELECT group_id resource_id
FROM jtf_rs_group_members
WHERE resource_id = b_resource_id
AND delete_flag <> 'Y';
SELECT team_id resource_id
FROM jtf_rs_team_members
WHERE team_resource_id = b_resource_id
AND delete_flag <> 'Y';
SELECT DISTINCT fgs.instance_pk1_value resource_id,
fgs.instance_pk2_value resource_type
FROM fnd_grants fgs,
fnd_menus fmu,
fnd_objects fos,
jtf_rs_group_usages jru,
jtf_rs_groups_tl jrt
WHERE fgs.object_id = fos.object_id -- grants joint to object
AND fgs.menu_id = fmu.menu_id -- grants joint to menus
AND fos.obj_name = 'JTF_TASK_RESOURCE'
AND fgs.grantee_key = b_resource_id
AND fgs.grantee_type = 'USER'
AND fgs.start_date < SYSDATE
AND ( fgs.end_date >= SYSDATE
OR fgs.end_date IS NULL)
AND fgs.instance_pk2_value = 'RS_GROUP'
AND jrt.group_id = TO_NUMBER (fgs.instance_pk1_value)
AND jrt.language = USERENV ('LANG')
AND jru.group_id = jrt.group_id
AND jru.usage = 'GROUP_CALENDAR';
p_operation IN VARCHAR2, --CREATE OR UPDATE
x_planned_start OUT NOCOPY DATE,
x_planned_end OUT NOCOPY DATE,
x_scheduled_start OUT NOCOPY DATE,
x_scheduled_end OUT NOCOPY DATE,
x_actual_start OUT NOCOPY DATE,
x_actual_end OUT NOCOPY DATE,
x_date_selected OUT NOCOPY VARCHAR2,
x_show_on_calendar OUT NOCOPY VARCHAR2
)
IS
l_task_id NUMBER;
SELECT MAX (tasks.calendar_start_date)
FROM ( select b.calendar_start_date from jtf_tasks_b b
where b.recurrence_rule_id=p_recurrence_rule_id
union
select b.calendar_start_date from jtf_tasks_b b,
jta_task_exclusions jte where
jte.recurrence_rule_id=p_recurrence_rule_id
and jte.task_id=b.task_id) tasks;
SELECT task_priority_id
FROM jtf_tasks_b
WHERE task_id = p_task_id;
p_exclusion_data (i).dateSelected:=l_exclusion.date_selected;
FUNCTION already_selected(p_task_id IN NUMBER
,p_sync_id IN NUMBER
,p_task_tbl IN cac_sync_task.task_tbl)
RETURN BOOLEAN
IS
l_selected BOOLEAN := FALSE;
l_selected := TRUE;
l_selected := TRUE;
RETURN l_selected;
END already_selected;
p_date_selected IN VARCHAR2,
p_timezone_id IN NUMBER,
p_syncanchor IN DATE,
p_planned_start_date IN DATE,
p_planned_end_date IN DATE,
p_scheduled_start_date IN DATE,
p_scheduled_end_date IN DATE,
p_actual_start_date IN DATE,
p_actual_end_date IN DATE,
p_calendar_start_date IN DATE,
p_calendar_end_date IN DATE,
p_alarm_on IN VARCHAR2,
p_alarm_start IN NUMBER,
p_recurrence_rule_id IN NUMBER,
p_occurs_uom IN VARCHAR2,
p_occurs_every IN NUMBER,
p_occurs_number IN NUMBER,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
p_sunday IN VARCHAR2,
p_monday IN VARCHAR2,
p_tuesday IN VARCHAR2,
p_wednesday IN VARCHAR2,
p_thursday IN VARCHAR2,
p_friday IN VARCHAR2,
p_saturday IN VARCHAR2,
p_date_of_month IN VARCHAR2,
p_occurs_which IN VARCHAR2,
--p_get_data IN BOOLEAN,
p_locations IN VARCHAR2,
p_free_busy_type IN VARCHAR2,
p_dial_in IN VARCHAR2,
x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
)
IS
l_category_name VARCHAR2(240); -- Fix bug 2540722
IF p_operation <> G_DELETE
THEN
make_prefix (
p_assignment_status_id => get_assignment_status_id (p_task_id, p_resource_id),
p_source_object_type_code => x_task_rec.objectcode,
p_resource_type => p_owner_type_code,
p_resource_id => cac_sync_task.g_login_resource_id,
p_group_id => p_resource_id,
x_subject => x_task_rec.subject
);
x_task_rec.dateselected := NVL(p_date_selected,'S'); -- fix bug 2389092
PROCEDURE check_delete_data (
p_task_id IN NUMBER,
p_resource_id IN NUMBER,
p_objectcode IN VARCHAR2,
x_status_id OUT NOCOPY NUMBER,
x_delete_flag OUT NOCOPY VARCHAR2
)
IS
l_assignee_role VARCHAR2(30);
x_delete_flag := 'U'; -- UPDATE
x_delete_flag := 'X'; -- DO NOTHING
x_delete_flag := 'D'; -- DELETE
x_delete_flag := 'D';
END check_delete_data;
SELECT task_assignment_id
FROM jtf_task_all_assignments
WHERE task_id = p_task_id
AND resource_id = p_resource_id
AND resource_type_code = p_resource_type;
SELECT assignment_status_id
FROM jtf_task_all_assignments
WHERE task_id = p_task_id
AND resource_id = p_resource_id;
SELECT task_name, owner_id, owner_type_code
FROM jtf_tasks_vl
WHERE task_id = b_task_id;
SELECT a.assignee_role, a.assignment_status_id, r.resource_id
FROM jtf_rs_resource_extns r, jtf_task_all_assignments a
WHERE a.task_id = b_task_id
AND a.resource_id = b_resource_id
AND r.user_id = a.created_by;
SELECT DISTINCT fmu.menu_name
FROM fnd_menus fmu, fnd_objects fos, fnd_grants fgs
WHERE fmu.menu_id = fgs.menu_id -- grants joint to menus
AND fos.obj_name = 'JTF_TASK_RESOURCE'
AND fos.object_id = fgs.object_id -- grants joint to object
AND fgs.grantee_key = b_resource_id
AND fgs.grantee_type = 'USER'
AND fgs.start_date < SYSDATE
AND ( fgs.end_date >= SYSDATE
OR fgs.end_date IS NULL)
AND fgs.instance_pk2_value = 'RS_GROUP'
AND fgs.instance_pk1_value = b_group_id;
SELECT source_object_type_code
FROM jtf_tasks_b
WHERE task_id = b_task_id;
SELECT asg.assignee_role
, rs.resource_type_code
, rs.group_calendar_flag
, asg.assignment_status_id
, tsk.source_object_type_code
FROM (SELECT p_resource_id resource_id
, 'RS_EMPLOYEE' resource_type_code
, 'N' group_calendar_flag
FROM dual
UNION ALL
SELECT tm.team_id resource_id
, 'RS_TEAM' resource_type_code
, 'N' group_calendar_flag
FROM jtf_rs_team_members tm
WHERE tm.team_resource_id = p_resource_id
UNION ALL
SELECT gm.group_id resource_id
, 'RS_GROUP' resource_type_code
, 'N' group_calendar_flag
FROM jtf_rs_group_members gm
WHERE gm.resource_id = p_resource_id
UNION ALL
SELECT g.group_id resource_id
, 'RS_GROUP' resource_type_code
, 'Y' group_calendar_flag
FROM fnd_grants fg
, jtf_rs_groups_b g
WHERE fg.grantee_key = to_char(p_resource_id)
AND fg.grantee_type = 'USER'
AND fg.instance_pk2_value = 'RS_GROUP'
AND fg.instance_pk1_value = to_char(g.group_id)
) rs
, jtf_task_all_assignments asg
, jtf_tasks_b tsk
WHERE asg.resource_type_code = rs.resource_type_code
AND asg.resource_id = rs.resource_id
AND asg.task_id = tsk.task_id
AND tsk.task_id = p_task_id
ORDER BY rs.group_calendar_flag desc
,decode(rs.resource_type_code,
'RS_EMPLOYEE', 1,
'RS_GROUP', 2,
'RS_TEAM', 3);
FUNCTION get_update_type (p_task_id IN NUMBER,
p_resource_id IN NUMBER,
p_subject IN VARCHAR2)
RETURN VARCHAR2
IS
l_synced_resource_type VARCHAR2(30);
l_update_type VARCHAR2(15) := G_UPDATE_ALL; -- Added for fix bug 2442686
l_update_type := g_do_nothing; -- Added for fix bug 2442686
l_update_type := g_update_status;
l_update_type := g_do_nothing;
RETURN l_update_type; -- Added for fix bug 2442686
END get_update_type;
SELECT
t.timezone_id,
tl.description,
tl.task_name,
t.planned_start_date,
t.planned_end_date,
t.scheduled_start_date,
t.scheduled_end_date,
t.actual_start_date,
t.actual_end_date,
t.calendar_end_date,
NVL (t.private_flag, 'N') private_flag,
rc.occurs_uom,
rc.occurs_every,
greatest(rc.start_date_active, t.planned_start_date) start_date_active,
rc.end_date_active,
rc.sunday,
rc.monday,
rc.tuesday,
rc.wednesday,
rc.thursday,
rc.friday,
rc.saturday,
rc.date_of_month,
rc.occurs_which,
rc.recurrence_rule_id,
CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
ta.free_busy_type free_busy_type,
t.alarm_start alarm_start,
t.alarm_on alarm_on
FROM jtf_task_recur_rules rc,
jtf_task_statuses_b ts,
jtf_task_priorities_b tb,
jtf_tasks_tl tl,
jtf_task_all_assignments ta,
jtf_tasks_b t
WHERE
ta.task_id = t.task_id
and ta.assignee_role= b_role
AND tl.task_id = t.task_id
AND ts.task_status_id = t.task_status_id
AND tl.language = USERENV ('LANG')
AND rc.recurrence_rule_id (+)= t.recurrence_rule_id
AND tb.task_priority_id (+) = t.task_priority_id
and t.task_id=b_task_id
and nvl(t.deleted_flag,'N')='N';
SELECT t.recurrence_rule_id
FROM jtf_tasks_b t
WHERE t.task_id = p_task_id;
IF NOT (l_invalid OR already_selected(p_task_id => rec_modify_nonrepeat.task_id, p_task_tbl => x_data))
THEN
IF p_get_data
THEN
add_task (
p_request_type => p_request_type,
p_resource_id => p_resource_id,
p_principal_id => p_principal_id,
p_resource_type => p_resource_type,
p_recordindex => i+1,
p_operation => g_modify,
p_task_sync_id => rec_modify_nonrepeat.task_sync_id,
p_task_id => rec_modify_nonrepeat.task_id,
p_task_name => rec_modify_nonrepeat.task_name,
p_owner_type_code => rec_modify_nonrepeat.owner_type_code,
p_description => rec_modify_nonrepeat.description,
p_task_status_id => rec_modify_nonrepeat.task_status_id,
p_task_priority_id => rec_modify_nonrepeat.importance_level ,
p_private_flag => rec_modify_nonrepeat.private_flag,
p_date_selected => rec_modify_nonrepeat.date_selected,
p_timezone_id => rec_modify_nonrepeat.timezone_id,
p_syncanchor => rec_modify_nonrepeat.new_timestamp,
p_planned_start_date => rec_modify_nonrepeat.planned_start_date,
p_planned_end_date => rec_modify_nonrepeat.planned_end_date,
p_scheduled_start_date => rec_modify_nonrepeat.scheduled_start_date,
p_scheduled_end_date => rec_modify_nonrepeat.scheduled_end_date,
p_actual_start_date => rec_modify_nonrepeat.actual_start_date,
p_actual_end_date => rec_modify_nonrepeat.actual_end_date,
p_calendar_start_date => rec_modify_nonrepeat.calendar_start_date,
p_calendar_end_date => rec_modify_nonrepeat.calendar_end_date,
p_alarm_on => rec_modify_nonrepeat.alarm_on,
p_alarm_start => rec_modify_nonrepeat.alarm_start,
p_recurrence_rule_id => rec_modify_nonrepeat.recurrence_rule_id,
p_occurs_uom => NULL,
p_occurs_every => NULL,
p_occurs_number => NULL,
p_start_date_active => NULL,
p_end_date_active => NULL,
p_sunday => NULL,
p_monday => NULL,
p_tuesday => NULL,
p_wednesday => NULL,
p_thursday => NULL,
p_friday => NULL,
p_saturday => NULL,
p_date_of_month => NULL,
p_occurs_which => NULL,
p_locations => rec_modify_nonrepeat.locations,
p_free_busy_type => rec_modify_nonrepeat.free_busy_type,
p_dial_in => get_dial_in_value(rec_modify_nonrepeat.task_id),
x_task_rec => x_task_rec
);
ELSE -- For get_count, store the task_id selected so as to avoid the duplicate
i := i + 1;
IF NOT (l_invalid OR already_selected(p_task_id => rec_new_nonrepeat.task_id, p_task_tbl => x_data))
THEN
IF p_get_data
THEN
add_task (
p_request_type => p_request_type,
p_resource_id => p_resource_id,
p_principal_id => p_principal_id,
p_resource_type => p_resource_type,
p_recordindex => i + 1,
p_operation => g_new,
p_task_sync_id => NULL,
p_task_id => rec_new_nonrepeat.task_id,
p_task_name => rec_new_nonrepeat.task_name,
p_owner_type_code => rec_new_nonrepeat.owner_type_code,
p_description => rec_new_nonrepeat.description,
p_task_status_id => rec_new_nonrepeat.task_status_id,
p_task_priority_id => rec_new_nonrepeat.importance_level ,
p_private_flag => rec_new_nonrepeat.private_flag,
p_date_selected => rec_new_nonrepeat.date_selected,
p_timezone_id => rec_new_nonrepeat.timezone_id,
p_syncanchor => rec_new_nonrepeat.new_timestamp,
p_planned_start_date => rec_new_nonrepeat.planned_start_date,
p_planned_end_date => rec_new_nonrepeat.planned_end_date,
p_scheduled_start_date => rec_new_nonrepeat.scheduled_start_date,
p_scheduled_end_date => rec_new_nonrepeat.scheduled_end_date,
p_actual_start_date => rec_new_nonrepeat.actual_start_date,
p_actual_end_date => rec_new_nonrepeat.actual_end_date,
p_calendar_start_date => rec_new_nonrepeat.calendar_start_date,
p_calendar_end_date => rec_new_nonrepeat.calendar_end_date,
p_alarm_on => rec_new_nonrepeat.alarm_on,
p_alarm_start => rec_new_nonrepeat.alarm_start,
p_recurrence_rule_id => rec_new_nonrepeat.recurrence_rule_id,
p_occurs_uom => NULL,
p_occurs_every => NULL,
p_occurs_number => NULL,
p_start_date_active => NULL,
p_end_date_active => NULL,
p_sunday => NULL,
p_monday => NULL,
p_tuesday => NULL,
p_wednesday => NULL,
p_thursday => NULL,
p_friday => NULL,
p_saturday => NULL,
p_date_of_month => NULL,
p_occurs_which => NULL,
--p_get_data => p_get_data,
p_locations => rec_new_nonrepeat.locations,
p_free_busy_type=>rec_new_nonrepeat.free_busy_type,
p_dial_in=>get_dial_in_value(rec_new_nonrepeat.task_id),
x_task_rec => x_task_rec
);
ELSE -- For get_count, store the task_id selected so as to avoid the duplicate
i := i + 1;
PROCEDURE get_all_deleted_tasks (
p_request_type IN VARCHAR2,
p_syncanchor IN DATE,
p_recordindex IN NUMBER,
p_resource_id IN NUMBER,
p_principal_id IN NUMBER,
p_resource_type IN VARCHAR2,
p_source_object_type IN VARCHAR2,
p_get_data IN BOOLEAN,
x_totaldeleted IN OUT NOCOPY NUMBER,
x_data IN OUT NOCOPY cac_sync_task.task_tbl
)
IS
i INTEGER := nvl(x_data.last,0);
FOR rec_delete IN cac_sync_task_cursors.c_delete_task (
p_syncanchor,
p_resource_id,
p_principal_id,
p_resource_type,
p_source_object_type
)
LOOP
IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
THEN
IF p_get_data
THEN
i := i + 1;
x_data(i).syncid := rec_delete.task_sync_id;
x_data(i).eventtype := g_delete;
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => rec_delete.task_sync_id
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_totaldeleted := x_totaldeleted + 1;
FOR rec_delete IN cac_sync_task_cursors.c_delete_assignee_reject (
p_syncanchor,
p_resource_id,
p_principal_id,
p_resource_type,
p_source_object_type
)
LOOP
IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
THEN
IF p_get_data
THEN
i := i + 1;
x_data(i).syncid := rec_delete.task_sync_id;
x_data(i).eventtype := g_delete;
/* Commented this for bug#5191856 bcos for deleted appointments, records are not deleted
from jtf_task_all_assignments table and the user has the option of Accepting the declined
Appointment. */
/*cac_sync_task_map_pkg.delete_row (
p_task_sync_id => rec_delete.task_sync_id
);*/
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_totaldeleted := x_totaldeleted + 1;
FOR rec_delete IN cac_sync_task_cursors.c_delete_assignment (
p_syncanchor,
p_resource_id,
p_resource_type,
p_principal_id,
p_source_object_type
)
LOOP
IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
THEN
IF p_get_data
THEN
i := i + 1;
x_data(i).eventtype := g_delete;
x_data(i).syncid := rec_delete.task_sync_id;
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => rec_delete.task_sync_id
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_totaldeleted := x_totaldeleted + 1;
FOR rec_delete IN cac_sync_task_cursors.c_delete_rejected_tasks (
p_syncanchor,
p_resource_id,
p_resource_type,
p_principal_id,
p_source_object_type
)
LOOP
IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
THEN
IF p_get_data
THEN
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_data (i).eventtype := g_delete;
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => rec_delete.task_sync_id
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_totaldeleted := x_totaldeleted + 1;
FOR rec_delete IN cac_sync_task_cursors.c_delete_unsubscribed(
p_resource_id,
p_resource_type,
p_principal_id,
p_source_object_type
)
LOOP
IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
THEN
IF p_get_data
THEN
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_data (i).eventtype := g_delete;
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => rec_delete.task_sync_id
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
x_data (i).syncid := rec_delete.task_sync_id;
x_totaldeleted := x_totaldeleted + 1;
END get_all_deleted_tasks;
x_data(i).eventtype := g_delete;
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => rec_modify_repeat.task_sync_id
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
IF NOT (l_invalid OR already_selected(p_task_id => rec_modify_repeat.task_id, p_task_tbl => x_data))
THEN
IF p_get_data
THEN
add_task (
p_request_type => p_request_type,
p_resource_id => p_resource_id,
p_principal_id => p_principal_id,
p_resource_type => p_resource_type,
p_recordindex => i + 1,
p_operation => g_modify,
p_task_sync_id => rec_modify_repeat.task_sync_id,
p_task_id => rec_modify_repeat.task_id,
p_task_name => rec_modify_repeat.task_name,
p_owner_type_code => rec_modify_repeat.owner_type_code,
p_description => rec_modify_repeat.description,
p_task_status_id => rec_modify_repeat.task_status_id,
p_task_priority_id => null ,
p_private_flag => rec_modify_repeat.private_flag,
p_date_selected => rec_modify_repeat.date_selected,
p_timezone_id => rec_modify_repeat.timezone_id,
p_syncanchor => rec_modify_repeat.new_timestamp,
p_planned_start_date => rec_modify_repeat.planned_start_date,
p_planned_end_date => rec_modify_repeat.planned_end_date,
p_scheduled_start_date => rec_modify_repeat.scheduled_start_date,
p_scheduled_end_date => rec_modify_repeat.scheduled_end_date,
p_actual_start_date => rec_modify_repeat.actual_start_date,
p_actual_end_date => rec_modify_repeat.actual_end_date,
p_calendar_start_date => rec_modify_repeat.calendar_start_date,
p_calendar_end_date => rec_modify_repeat.calendar_end_date,
p_alarm_on => rec_modify_repeat.alarm_on,
p_alarm_start => rec_modify_repeat.alarm_start,
p_recurrence_rule_id => rec_modify_repeat.recurrence_rule_id,
p_occurs_uom => rec_modify_repeat.occurs_uom,
p_occurs_every => rec_modify_repeat.occurs_every,
p_occurs_number => rec_modify_repeat.occurs_number,
p_start_date_active => rec_modify_repeat.start_date_active,
p_end_date_active => rec_modify_repeat.end_date_active,
p_sunday => rec_modify_repeat.sunday,
p_monday => rec_modify_repeat.monday,
p_tuesday => rec_modify_repeat.tuesday,
p_wednesday => rec_modify_repeat.wednesday,
p_thursday => rec_modify_repeat.thursday,
p_friday => rec_modify_repeat.friday,
p_saturday => rec_modify_repeat.saturday,
p_date_of_month => rec_modify_repeat.date_of_month,
p_occurs_which => rec_modify_repeat.occurs_which,
p_locations => rec_modify_repeat.locations,
p_free_busy_type=>rec_modify_repeat.free_busy_type,
p_dial_in =>get_dial_in_value(rec_modify_repeat.task_id),
x_task_rec => x_task_rec
--p_get_data => p_get_data
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
IF NOT (l_invalid OR already_selected(p_task_id => rec_new_repeat.task_id, p_task_tbl => x_data))
THEN
IF p_get_data
THEN
add_task (
p_request_type => p_request_type,
p_resource_id => p_resource_id,
p_principal_id => p_principal_id,
p_resource_type => p_resource_type,
p_recordindex => i + 1,
p_operation => g_new,
p_task_sync_id => null ,
p_task_id => rec_new_repeat.task_id,
p_task_name => rec_new_repeat.task_name,
p_owner_type_code => rec_new_repeat.owner_type_code,
p_description => rec_new_repeat.description,
p_task_status_id => rec_new_repeat.task_status_id,
p_task_priority_id => rec_new_repeat.importance_level,
p_private_flag => rec_new_repeat.private_flag,
p_date_selected => rec_new_repeat.date_selected,
p_timezone_id => rec_new_repeat.timezone_id,
p_syncanchor => rec_new_repeat.new_timestamp,
p_planned_start_date => rec_new_repeat.planned_start_date,
p_planned_end_date => rec_new_repeat.planned_end_date,
p_scheduled_start_date => rec_new_repeat.scheduled_start_date,
p_scheduled_end_date => rec_new_repeat.scheduled_end_date,
p_actual_start_date => rec_new_repeat.actual_start_date,
p_actual_end_date => rec_new_repeat.actual_end_date,
p_calendar_start_date => rec_new_repeat.calendar_start_date,
p_calendar_end_date => rec_new_repeat.calendar_end_date,
p_alarm_on => rec_new_repeat.alarm_on,
p_alarm_start => rec_new_repeat.alarm_start,
p_recurrence_rule_id => rec_new_repeat.recurrence_rule_id,
p_occurs_uom => rec_new_repeat.occurs_uom,
p_occurs_every => rec_new_repeat.occurs_every,
p_occurs_number => rec_new_repeat.occurs_number,
p_start_date_active => rec_new_repeat.start_date_active,
p_end_date_active => rec_new_repeat.end_date_active,
p_sunday => rec_new_repeat.sunday,
p_monday => rec_new_repeat.monday,
p_tuesday => rec_new_repeat.tuesday,
p_wednesday => rec_new_repeat.wednesday,
p_thursday => rec_new_repeat.thursday,
p_friday => rec_new_repeat.friday,
p_saturday => rec_new_repeat.saturday,
p_date_of_month => rec_new_repeat.date_of_month,
p_occurs_which => rec_new_repeat.occurs_which,
--p_get_data => p_get_data,
p_locations => rec_new_repeat.locations,
p_free_busy_type=>rec_new_repeat.free_busy_type,
p_dial_in => get_dial_in_value(rec_new_repeat.task_id),
x_task_rec => x_task_rec
);
ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
i := i + 1;
SELECT cac_view_collab_details_s.nextval INTO l_key FROM DUAL;
l_date_selected VARCHAR2(100);
l_update_type VARCHAR2(15);
select b.object_version_number,b.task_id from jtf_tasks_b b,jtf_tasks_tl t
where b.entity in ('BOOKING','APPOINTMENT')
and b.source_object_type_code='EXTERNAL APPOINTMENT'
and t.task_id=b.task_id
and t.language=userenv('LANG')
and nvl(b.deleted_flag,'N')='N'
and t.task_name=b_task_name
and b.calendar_start_date =b_cal_start_date
and b.calendar_end_date=b_cal_end_date
and b.owner_type_code=b_owner_type_code
and b.owner_id=b_owner_id;
select 1 from jta_sync_task_mapping where
principal_id=b_principal_id
and task_id=b_task_id
and resource_id=b_resource_id;
SELECT COLLAB_ID, MEETING_MODE,MEETING_ID,MEETING_URL,JOIN_URL ,
PLAYBACK_URL ,DOWNLOAD_URL ,CHAT_URL ,IS_STANDALONE_LOCATION,DIAL_IN
FROM CAC_VIEW_COLLAB_DETAILS_VL
WHERE task_id=b_task_id;
x_date_selected => l_date_selected,
x_show_on_calendar => l_show_on_calendar
);
cac_view_collab_details_pkg.insert_row (
x_rowid => l_rowid,
x_collab_id => get_collab_id,
x_task_id => l_task_id,
x_meeting_mode => 'LIVE',
x_meeting_id => null,
x_meeting_url => null,
x_join_url => null,
x_playback_url => null,
x_download_url => null,
x_chat_url => null,
x_is_standalone_location => 'Y',
x_location => l_location,-- previous it was p_task_rec.locations,
x_dial_in => p_task_rec.dial_in,
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 => jtf_task_utl.login_id
);
p_timezone_id => G_SERVER_TIMEZONE_ID,--changed from g_client_timezone_id, as everything should be inserted in server timezone
p_date_selected => NULL, -- Fix Bug 2467021: For creation, pass NULL
p_alarm_start => l_alarm_mins,
p_alarm_start_uom => 'MIN',
p_alarm_interval_uom => 'MIN',
p_alarm_on => p_task_rec.alarmflag,
p_private_flag => p_task_rec.privateFlag,
p_category_id => l_category_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_task_id => l_task_id
);
cac_view_appt_pvt.update_external_appointment (
p_object_version_number =>l_object_version_number,
p_task_id =>l_task_id,
p_task_name => l_subject,
p_task_type_id => get_default_task_type,
p_description => p_task_rec.description,
p_task_priority_id => p_task_rec.priorityid,
p_planned_start_date => l_planned_start,
p_planned_end_date => l_planned_end,
p_timezone_id => G_SERVER_TIMEZONE_ID, --changed from g_client_timezone_id as all the value must be stored at server timezone
p_private_flag => p_task_rec.privateFlag,
p_alarm_start => l_alarm_mins,
p_alarm_on => p_task_rec.alarmflag,
p_category_id => l_category_id,
p_free_busy_type => p_task_rec.free_busy_type,
p_change_mode => jtf_task_repeat_appt_pvt.g_all,
x_return_status => l_return_status
);
cac_view_collab_details_pkg.update_row
(x_collab_id=> l_collab_details.collab_id ,
x_task_id=> l_task_id,
x_meeting_mode=>l_collab_details.meeting_mode,
x_meeting_id=>l_collab_details.meeting_id,
x_meeting_url=>l_collab_details.meeting_url,
x_join_url=>l_collab_details.join_url,
x_playback_url=>l_collab_details.playback_url,
x_download_url=>l_collab_details.download_url,
x_chat_url=>l_collab_details.chat_url,
x_is_standalone_location=>l_collab_details.is_standalone_location,
x_location=>l_location,
x_dial_in=>p_task_rec.dial_in,
x_last_update_date=>SYSDATE,
x_last_updated_by=>jtf_task_utl.updated_by,
x_last_update_login=>jtf_task_utl.login_id);
--checking if the update status is false and if yes, write to message stack.
if (cac_sync_common.is_success (l_return_status)=false) then
cac_sync_common.put_messages_to_result (
p_task_rec,
p_status => 2,
p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
);
cac_view_collab_details_pkg.insert_row (
x_rowid => l_rowid,
x_collab_id => get_collab_id,
x_task_id => l_task_id,
x_meeting_mode => 'LIVE',
x_meeting_id => NULL,
x_meeting_url => NULL,
x_join_url => NULL,
x_playback_url => NULL,
x_download_url => NULL,
x_chat_url => NULL,
x_is_standalone_location => 'Y',
x_location => l_location,-- previous it was p_task_rec.locations,
x_dial_in => p_task_rec.dial_in,
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 => jtf_task_utl.login_id
);
/*insert_or_update_mapping (
p_task_sync_id => p_task_rec.syncid,
p_task_id => l_task_id,
p_resource_id => p_resource_id,
p_mapping_type => p_mapping_type
);
SELECT tl.task_name,t.task_id,
tl.description,
t.date_selected,
t.planned_start_date,
t.planned_end_date,
t.scheduled_start_date,
t.scheduled_end_date,
t.actual_start_date,
t.actual_end_date,
t.calendar_start_date,
t.calendar_end_date,
t.task_status_id,
tb.importance_level importance_level,
NVL (t.alarm_on, 'N') alarm_on,
t.alarm_start,
UPPER (t.alarm_start_uom) alarm_start_uom,
NVL (t.private_flag, 'N') private_flag,
t.timezone_id timezone_id,
t.owner_type_code,
t.source_object_type_code,
rc.recurrence_rule_id,
rc.occurs_uom,
rc.occurs_every,
rc.occurs_number,
greatest(rc.start_date_active, t.planned_start_date) start_date_active,
rc.end_date_active,
rc.sunday,
rc.monday,
rc.tuesday,
rc.wednesday,
rc.thursday,
rc.friday,
rc.saturday,
rc.date_of_month,
rc.occurs_which,
greatest(t.object_changed_date, ta.last_update_date) new_timestamp,
CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
ta.free_busy_type free_busy_type
FROM jtf_task_recur_rules rc,
jtf_task_statuses_b ts,
jtf_task_priorities_b tb,
jtf_tasks_tl tl,
jtf_task_all_assignments ta,
jtf_tasks_b t
WHERE
ta.task_id = t.task_id
and ta.assignee_role= b_role
AND tl.task_id = t.task_id
AND ts.task_status_id = t.task_status_id
AND tl.language = USERENV ('LANG')
AND rc.recurrence_rule_id (+)= t.recurrence_rule_id
AND tb.task_priority_id (+) = t.task_priority_id
and t.task_id=b_task_id
and nvl(t.deleted_flag,'N')='N';
PROCEDURE update_existing_data (
p_task_rec IN OUT NOCOPY cac_sync_task.task_rec,
p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl,
p_resource_id IN NUMBER,
p_resource_type IN VARCHAR2
)
IS
l_ovn NUMBER;
l_date_selected VARCHAR2(100);
l_update_type VARCHAR2(15);
l_update_all VARCHAR2(5);
SELECT task_id,
planned_start_date,
planned_end_date,
scheduled_start_date,
scheduled_end_date,
actual_start_date,
actual_end_date,
calendar_start_date,
timezone_id
FROM jtf_tasks_b
WHERE recurrence_rule_id = b_recurrence_rule_id;
select CAC.COLLAB_ID, CAC.MEETING_MODE,CAC.MEETING_ID,CAC.MEETING_URL,
CAC.JOIN_URL ,CAC.PLAYBACK_URL ,CAC.DOWNLOAD_URL ,CAC.CHAT_URL ,
CAC.IS_STANDALONE_LOCATION,CAC.DIAL_IN, jtb1.task_id
from cac_view_collab_details_vl cac, jtf_tasks_b jtb1,jtf_tasks_b jtb2
where cac.task_id=jtb1.task_id
and jtb1.recurrence_rule_id=jtb2.recurrence_rule_id
and jtb2.task_id=b_task_id;
select COLLAB_ID, MEETING_MODE,MEETING_ID,MEETING_URL,JOIN_URL ,
PLAYBACK_URL ,DOWNLOAD_URL ,CHAT_URL ,IS_STANDALONE_LOCATION,DIAL_IN
from CAC_VIEW_COLLAB_DETAILS_VL
where task_id=b_task_id;
p_operation => 'UPDATE',
x_planned_start => l_planned_start_date,
x_planned_end => l_planned_end_date,
x_scheduled_start => l_scheduled_start_date,
x_scheduled_end => l_scheduled_end_date,
x_actual_start => l_actual_start_date,
x_actual_end => l_actual_end_date,
x_date_selected => l_date_selected,
x_show_on_calendar => l_show_on_calendar
);
l_update_type := get_update_type (
p_task_id => l_task_id,
p_resource_id => p_resource_id,
p_subject => p_task_rec.subject
);
IF l_update_type = g_update_all
THEN
IF p_exclusion_tbl.COUNT > 0
THEN
process_exclusions (
p_exclusion_tbl => p_exclusion_tbl,
p_rec_rule_id => l_rec_rule_id,
p_repeating_task_id => l_task_id,
p_task_rec => p_task_rec
);
l_update_type = cac_sync_task_common.g_update_all
THEN -- Changed Repeating Rule
-- include open end dates also
l_G_TASK_TIMEZONE_ID:=get_task_timezone_id(l_task_id);
jtf_task_recurrences_pvt.update_task_recurrence (
p_api_version => 1.0,
p_task_id => l_task_id,
p_recurrence_rule_id => l_rec_rule_id,
p_occurs_which => l_occurs_which,
p_date_of_month => l_date_of_month,
p_occurs_month => l_occurs_month,
p_occurs_uom => p_task_rec.unit_of_measure,
p_occurs_every => p_task_rec.occurs_every,
p_occurs_number => l_occurs_number,
p_start_date_active => l_repeat_start_date,
p_end_date_active => l_repeat_end_date,
p_sunday => l_sunday,
p_monday => l_monday,
p_tuesday => l_tuesday,
p_wednesday => l_wednesday,
p_thursday => l_thursday,
p_friday => l_friday,
p_saturday => l_saturday,
x_new_recurrence_rule_id => l_new_recurrence_rule_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
THEN-- Failed to update a task
l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
p_user_message => 'JTA_SYNC_UPDATE_RECUR_FAIL'
);
cac_view_collab_details_pkg.update_row
(x_collab_id=> p_getTaskForRecurRule.collab_id ,
x_task_id=> p_getTaskForRecurRule.task_id,
x_meeting_mode=>p_getTaskForRecurRule.meeting_mode,
x_meeting_id=>p_getTaskForRecurRule.meeting_id,
x_meeting_url=>p_getTaskForRecurRule.meeting_url,
x_join_url=>p_getTaskForRecurRule.join_url,
x_playback_url=>p_getTaskForRecurRule.playback_url,
x_download_url=>p_getTaskForRecurRule.download_url,
x_chat_url=>p_getTaskForRecurRule.chat_url,
x_is_standalone_location=>p_getTaskForRecurRule.is_standalone_location,
x_location=>l_location,-- p_task_rec.locations,
x_dial_in=>p_task_rec.dial_in,
x_last_update_date=>sysdate,
x_last_updated_by=>jtf_task_utl.updated_by,
x_last_update_login=>jtf_task_utl.login_id);
END IF; -- l_update_type = g_update_all
IF l_update_type = g_update_all
THEN
-----------------------------------------------------------
-- Fix for the bug 2380399
-- : If the current sync has a change of any fields
-- along with the change of repeating rule,
-- The update_task_recurrence_rule API creates new repeating
-- tasks and updates the mapping record with the new first
-- task_id. Hence the new task_id must be picked from
-- mapping table again. And the new object_version_number
-- of the the new task_id must be selected for update of the
-- other fields
-----------------------------------------------------------
l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
delete_task_data ( p_task_rec =>p_task_rec, p_delete_map_flag =>true);
jta_cal_appointment_pvt.update_appointment (
p_object_version_number => l_ovn ,
p_task_id => l_task_id,
p_task_name => NVL (l_task_name, ' '),
p_description => p_task_rec.description,
p_task_priority_id => l_priorityId,
p_planned_start_date => l_planned_start_date,
p_planned_end_date => l_planned_end_date,
p_timezone_id => get_task_timezone_id (l_task_id),
p_private_flag => p_task_rec.privateflag,
p_alarm_start => l_alarm_mins,
p_alarm_on => p_task_rec.alarmflag,
--p_category_id => l_category_id,
p_free_busy_type => p_task_rec.free_busy_type,
p_change_mode => jtf_task_repeat_appt_pvt.g_all,
x_return_status => l_return_status
);
cac_view_collab_details_pkg.update_row
(x_collab_id=> l_collab_details.collab_id ,
x_task_id=> l_task_id,
x_meeting_mode=>l_collab_details.meeting_mode,
x_meeting_id=>l_collab_details.meeting_id,
x_meeting_url=>l_collab_details.meeting_url,
x_join_url=>l_collab_details.join_url,
x_playback_url=>l_collab_details.playback_url,
x_download_url=>l_collab_details.download_url,
x_chat_url=>l_collab_details.chat_url,
x_is_standalone_location=>l_collab_details.is_standalone_location,
x_location=>l_location,-- was p_task_rec.locations,
x_dial_in=>p_task_rec.dial_in,
x_last_update_date=>sysdate,
x_last_updated_by=>jtf_task_utl.updated_by,
x_last_update_login=>jtf_task_utl.login_id);
cac_view_collab_details_pkg.insert_row (
x_rowid => l_rowid,
x_collab_id => get_collab_id,--cac_view_collab_details_s.nextval,
x_task_id => l_task_id,
x_meeting_mode => 'LIVE',
x_meeting_id => null,
x_meeting_url => null,
x_join_url => null,
x_playback_url => null,
x_download_url => null,
x_chat_url => null,
x_is_standalone_location => 'Y',
x_location => l_location,--was p_task_rec.locations,
x_dial_in => p_task_rec.dial_in,
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 => jtf_task_utl.login_id);
cac_view_collab_details_pkg.update_row
(x_collab_id=> p_getTaskForRecurRule.collab_id ,
x_task_id=> p_getTaskForRecurRule.task_id,
x_meeting_mode=>p_getTaskForRecurRule.meeting_mode,
x_meeting_id=>p_getTaskForRecurRule.meeting_id,
x_meeting_url=>p_getTaskForRecurRule.meeting_url,
x_join_url=>p_getTaskForRecurRule.join_url,
x_playback_url=>p_getTaskForRecurRule.playback_url,
x_download_url=>p_getTaskForRecurRule.download_url,
x_chat_url=>p_getTaskForRecurRule.chat_url,
x_is_standalone_location=>p_getTaskForRecurRule.is_standalone_location,
x_location=>l_location,--was p_task_rec.locations,
x_dial_in=>p_task_rec.dial_in,
x_last_update_date=>sysdate,
x_last_updated_by=>jtf_task_utl.updated_by,
x_last_update_login=>jtf_task_utl.login_id);
jtf_tasks_pvt.update_task (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_task_id => l_task_id,
p_object_version_number => l_ovn,
p_task_name => NVL (l_task_name, ' '),
p_description => p_task_rec.description,
p_task_status_id => p_task_rec.statusid,
p_task_priority_id => p_task_rec.priorityid,
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_show_on_calendar => fnd_api.g_miss_char, -- Fix Bug 2467021: For update, pass g_miss_char
p_date_selected => fnd_api.g_miss_char, -- Fix Bug 2467021: For update, pass g_miss_char
p_alarm_start => l_alarm_mins,
p_alarm_start_uom => 'MIN',
p_timezone_id => get_task_timezone_id (l_task_id),
p_private_flag => p_task_rec.privateflag,
--p_category_id => l_category_id,
p_change_mode => 'A',
p_enable_workflow => 'N',
p_abort_workflow => 'N',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
delete_task_data ( p_task_rec =>p_task_rec, p_delete_map_flag =>true);
cac_view_appt_pvt.update_external_appointment (
p_object_version_number =>l_ovn,
p_task_id =>l_task_id,
p_task_name => NVL (l_task_name, ' '),
p_task_type_id => get_default_task_type,
p_description => p_task_rec.description,
p_task_priority_id => p_task_rec.priorityid,
p_planned_start_date => l_planned_start_date,
p_planned_end_date => l_planned_end_date,
p_timezone_id => G_SERVER_TIMEZONE_ID, --changed from g_client_timezone_id as all the value must be stored at server timezone
p_private_flag => p_task_rec.privateFlag,
p_alarm_start => l_alarm_mins,
p_alarm_on => p_task_rec.alarmflag,
--p_category_id => l_category_id,
p_free_busy_type => p_task_rec.free_busy_type,
p_change_mode => jtf_task_repeat_appt_pvt.g_all,
x_return_status => l_return_status
);
cac_view_collab_details_pkg.update_row
(x_collab_id=> l_collab_details.collab_id ,
x_task_id=> l_task_id,
x_meeting_mode=>l_collab_details.meeting_mode,
x_meeting_id=>l_collab_details.meeting_id,
x_meeting_url=>l_collab_details.meeting_url,
x_join_url=>l_collab_details.join_url,
x_playback_url=>l_collab_details.playback_url,
x_download_url=>l_collab_details.download_url,
x_chat_url=>l_collab_details.chat_url,
x_is_standalone_location=>l_collab_details.is_standalone_location,
x_location=>l_location,
x_dial_in=>p_task_rec.dial_in,
x_last_update_date=>SYSDATE,
x_last_updated_by=>jtf_task_utl.updated_by,
x_last_update_login=>jtf_task_utl.login_id);
THEN-- Failed to update a task
cac_sync_common.put_messages_to_result (
p_task_rec,
p_status => 2,
p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
); -- l_return_status
ELSIF ((l_update_type = g_update_status)) --and (compare_task_rec(p_task_rec)=true))
THEN
if not (compare_task_rec(p_task_rec)) then
cac_sync_common.put_messages_to_result (
p_task_rec,
p_status => 2,
p_user_message => 'CAC_SYNC_APPT_PERMISSION_DENY',
p_token_name=>'P_APPOINTMENT_SUBJECT',
p_token_value=>p_task_rec.subject
); -- l_return_status
jtf_task_assignments_pvt.update_task_assignment (
p_api_version => 1.0,
p_object_version_number => l_ovn,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_task_assignment_id => l_task_assignment_id,
p_assignment_status_id => 3, -- ACCEPT
p_free_busy_type => l_free_busy_type,
p_enable_workflow => fnd_profile.value('JTF_TASK_ENABLE_WORKFLOW'),
p_abort_workflow => fnd_profile.value('JTF_TASK_ABORT_PREV_WF'),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
p_user_message => 'JTA_SYNC_UPDATE_STS_FAIL'
);
ELSE--IF --(l_update_type=g_do_nothing) then
/*
if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.create_new_data', ' When l_update_type=g_do_nothing for task '|| p_task_rec.subject);
jtf_task_assignments_pvt.update_task_assignment (
p_api_version => 1.0,
p_object_version_number => l_ovn,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_task_assignment_id => l_task_assignment_id,
p_free_busy_type => p_task_rec.free_busy_type,
p_enable_workflow => fnd_profile.value('JTF_TASK_ENABLE_WORKFLOW'),
p_abort_workflow => fnd_profile.value('JTF_TASK_ABORT_PREV_WF'),
--p_update_all => l_update_all,
--p_enable_workflow => 'N',
--p_abort_workflow => 'N',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END IF; -- l_update_type
END update_existing_data;
PROCEDURE delete_exclusion_task (
p_repeating_task_id IN NUMBER,
x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
)
IS
l_ovn NUMBER;
jta_cal_appointment_pvt.delete_appointment (
p_object_version_number => l_ovn,
p_task_id => p_repeating_task_id,
p_delete_future_recurrences => fnd_api.g_false,
x_return_status => l_return_status
);
jtf_tasks_pvt.delete_task (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_task_id => p_repeating_task_id,
p_object_version_number => l_ovn,
x_return_status => l_return_status,
p_delete_future_recurrences => fnd_api.g_false ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
p_user_message => 'JTA_SYNC_DELETE_EXCLUSION_FAIL'
);
END delete_exclusion_task;
PROCEDURE delete_task_data (
p_task_rec IN OUT NOCOPY cac_sync_task.task_rec,
p_delete_map_flag IN BOOLEAN
)
IS
l_task_id NUMBER;
delete_tasks(p_task_id => l_task_id,
x_return_status => l_return_status);
IF p_delete_map_flag
THEN
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => p_task_rec.syncid
);
p_user_message => 'JTA_SYNC_DELETE_TASK_FAILED'
);
END delete_task_data;
l_deleted BOOLEAN := FALSE;
l_update_all varchar2(1) ;
l_update_all := 'Y' ;
l_update_all := null ;
jtf_task_assignments_pvt.update_task_assignment (
p_api_version => 1.0,
p_object_version_number => l_ovn,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_task_assignment_id => l_task_assignment_id,
p_assignment_status_id => 4, -- reject
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
--p_enable_workflow => 'N',
--p_abort_workflow => 'N'
);
cac_sync_task_map_pkg.delete_row(p_task_sync_id => p_task_rec.syncid);
p_user_message => 'JTA_SYNC_UPDATE_STS_FAIL'
);
SELECT jtrr.*
FROM jtf_task_recur_rules jtrr, jtf_tasks_b jtb
WHERE jtb.task_id = b_task_id
AND jtb.recurrence_rule_id IS NOT NULL
AND jtrr.recurrence_rule_id = jtb.recurrence_rule_id;
SELECT closed_flag, completed_flag, cancelled_flag, rejected_flag,
assigned_flag, working_flag, schedulable_flag, accepted_flag,
on_hold_flag, approved_flag
FROM jtf_task_statuses_b
WHERE task_status_id = p_task_status_id;
x_operation := cac_sync_task_common.g_delete;
x_operation := cac_sync_task_common.g_delete;
x_operation := cac_sync_task_common.g_delete;
SELECT 1 INTO l_num
FROM
fnd_user
,fnd_user_resp_groups
,fnd_responsibility
,jtf_state_rules_b
, jtf_state_responsibilities
WHERE fnd_user.user_id = fnd_global.user_id
AND fnd_user.user_id = fnd_user_resp_groups.user_id
AND fnd_user_resp_groups.responsibility_id = jtf_state_responsibilities.responsibility_id
AND jtf_state_responsibilities.rule_id = jtf_state_rules_b.rule_id;
SELECT 1
FROM jta_sync_task_mapping
WHERE task_sync_id = b_syncid;
select cactl.dial_in
from
cac_view_collab_details_tl cactl,
cac_view_collab_details cac
where cac.collab_id=cactl.collab_id
and cactl.LANGUAGE = userenv('LANG')
and cac.task_id=b_task_id;
procedure delete_bookings (
p_principal_id IN NUMBER
)
IS
CURSOR getUserId(b_principal_id IN NUMBER)
IS
SELECT user_id
FROM cac_sync_principals
WHERE principal_id = b_principal_id;
SELECT jtb.task_id,jtb.object_version_number,jstm.task_sync_id, jtb.source_object_type_code objectcode
FROM jta_sync_task_mapping jstm, jtf_tasks_b jtb
WHERE jstm.principal_id IN
(SELECT principal_id
FROM cac_sync_principals
WHERE device_id = (SELECT device_id FROM cac_sync_principals
WHERE principal_id = b_principal_id)
AND user_id = b_user_id)
AND jstm.task_id=jtb.task_id
AND jtb.entity IN ('BOOKING', 'APPOINTMENT')
AND jtb.source_object_type_code='EXTERNAL APPOINTMENT';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'no booking is found for the principal id ' || p_principal_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'booking is found for the principal id ' || p_principal_id);
delete_tasks(p_task_id => p_getBookings.task_id,
x_return_status => l_return_status);
cac_sync_task_map_pkg.delete_row (
p_task_sync_id => p_getBookings.task_sync_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'success from cac_bookings_pub.delete_booking');
else --failure from delete_booking API
if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'failure from cac_bookings_pub.delete_booking');
END delete_bookings;
select OCCURS_WHICH,DAY_OF_WEEK,DATE_OF_MONTH,
OCCURS_MONTH,OCCURS_UOM,OCCURS_EVERY,
OCCURS_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE,
SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,
SATURDAY
from jtf_task_recur_rules jtrr, jtf_tasks_b b
where b.recurrence_rule_id=jtrr.recurrence_rule_id
and b.task_id=b_task_id;
l_update_type VARCHAR2(15);
l_update_type := get_update_type (
p_task_id => l_task_id,
p_resource_id => l_resource_id,
p_subject => p_task_rec.subject
);
if (l_update_type = g_update_all) then
open get_recur_rule(l_task_id);
end if; -- if (l_update_type = g_update_all)
PROCEDURE delete_tasks (
p_task_id IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
cursor get_tasks_ids (b_recurrence_rule_id IN NUMBER)
is
select jte.task_id from
jta_task_exclusions jte
where jte.recurrence_rule_id=b_recurrence_rule_id;
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_task_id => p_task_id,
p_object_version_number => l_ovn,
p_delete_future_recurrences => 'A',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
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_task_id => l_tsk_ids.task_id,
p_object_version_number => l_ovn,
p_delete_future_recurrences => jtf_task_repeat_appt_pvt.G_ONE,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END delete_tasks;
If the appointment has been deleted/declined, it will return false and update will not be called.
Instead Sync will throw error saying that appointment has been deleted in server. */
PROCEDURE is_appointment_existing(p_task_sync_id IN NUMBER, x_result OUT NOCOPY VARCHAR2)
IS
CURSOR check_appt(b_resource_id NUMBER, b_resource_type_code VARCHAR2)
IS
SELECT a.assignment_status_id
from jtf_tasks_b b, jtf_task_all_assignments a, jta_sync_Task_mapping s
where b.task_id = a.task_id
and s.task_id = b.task_id
and s.task_sync_id = p_task_sync_id
and a.resource_id = b_resource_id
and a.resource_type_code = b_resource_type_code;