[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
)IS SELECT source_id EmployeeNumber
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = b_ResourceID
;
select pers.PARTY_name , hc.email_ADDRESS
from hz_parties pers,
hz_relationships hr,
hz_contact_points hc
where hc.owner_table_id = b_resource_id
and hr.party_id = hc.owner_table_id
and hr.subject_id = pers.party_id
and hr.subject_type = 'PERSON'
and hc.primary_flag = 'Y'
and hc.contact_point_type = 'EMAIL'
AND hc.status ='A'
and hc.owner_table_name = 'HZ_PARTIES';
SELECT name
INTO l_type_name
FROM jtf_task_types_tl
WHERE task_type_id = p_task_type_id
AND language = USERENV('LANG');
SELECT name
INTO l_priority_name
FROM jtf_task_priorities_tl
WHERE task_priority_id = p_task_priority_id
AND language = USERENV('LANG');
SELECT lk.meaning
INTO l_duration
FROM fnd_lookups lk
WHERE lk.lookup_type = 'JTF_CALND_DURATION'
AND lk.lookup_code = p_minutes;
)IS SELECT htv.name
FROM hz_timezones_vl htv
WHERE htv.timezone_id = b_timezone_id;
IS SELECT DISTINCT to_number(fgs.grantee_key) ResourceID
FROM fnd_grants fgs
, fnd_menus fmu
, fnd_objects fos
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 fmu.menu_name = 'JTF_CAL_ADMIN_ACCESS'
AND fgs.instance_pk1_value = to_char(b_group_id)
AND fgs.instance_pk2_value = ('RS_GROUP')
AND fgs.start_date < SYSDATE
AND ( fgs.end_date >= SYSDATE
OR fgs.end_date IS NULL
)
;
SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_item_key
FROM DUAL;
SELECT task_id, owner_id, task_name, description, timezone_id, task_type_id,
task_priority_id, calendar_start_date startDate,
(calendar_end_date - calendar_start_date)*24*60 duration
FROM jtf_tasks_vl
WHERE task_id IN
(SELECT task_id
FROM jtf_task_all_assignments
WHERE task_id IN (SELECT task_id
FROM jtf_task_all_assignments
WHERE task_assignment_id = p_task_assignment_id)
AND assignee_role = 'OWNER')
;
SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_item_key
FROM DUAL;
SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_ItemKey
FROM DUAL;
)IS SELECT jrb.group_id
FROM jtf_rs_groups_b jrb
, jtf_rs_groups_tl jrt
WHERE jrb.group_id = jrt.group_id
AND ( (jrb.end_date_active > SYSDATE)
OR (jrb.end_date_active IS NULL)
)
AND jrt.group_name = b_GroupName
-- AND jrt.group_desc = b_GroupDescription
AND jrt.language = userenv('LANG');
)IS SELECT group_usage_id
FROM jtf_rs_group_usages jru
WHERE jru.group_id = b_GroupID
AND jru.usage = b_Usage;
)IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
, jta.task_assignment_id TASK_ASSIGNMENT_ID
, jta.resource_id INVITEE
, jta.resource_type_code INVITEE_CODE
, jtl.task_name TASK_NAME
, jtl.description TASK_DESCRIPTION
, jtb.calendar_start_date START_DATE
, jtb.task_type_id TYPE_ID
, jtb.task_priority_id PRIORITY_ID
, (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
, jtb.timezone_id TIMEZONE_ID
FROM jtf_tasks_b jtb
, jtf_tasks_tl jtl
, jtf_task_all_assignments jta
WHERE jtb.task_id = jtl.task_id
AND jtl.language = userenv('LANG')
AND jta.task_id = jtb.task_id
AND jta.assignee_role = 'ASSIGNEE' -- don't sent one to the owner??
AND jta.show_on_calendar = 'Y' -- so Vanessa doesn't get it..
AND jtb.task_id = b_TaskID;
)IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
, jta.task_assignment_id TASK_ASSIGNMENT_ID
, jta.resource_id INVITEE
, jta.resource_type_code INVITEE_CODE
, jtl.task_name TASK_NAME
, jtl.description TASK_DESCRIPTION
, jtb.calendar_start_date START_DATE
, jtb.task_type_id TYPE_ID
, jtb.task_priority_id PRIORITY_ID
, (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
, jtb.timezone_id TIMEZONE_ID
FROM jtf_tasks_b jtb
, jtf_tasks_tl jtl
, jtf_task_all_assignments jta
WHERE jtb.task_id = jtl.task_id
AND jtl.language = userenv('LANG')
AND jta.task_id = jtb.task_id
AND jta.assignee_role = 'ASSIGNEE' -- don't sent one to the owner??
AND jta.show_on_calendar = 'Y' -- so Vanessa doesn't get it..
AND jtb.task_id = b_TaskID
AND jta.resource_id = b_INVITEE
AND jta.resource_type_code = b_INVITEE_TYPE ;
PROCEDURE UpdateInvitation
/*******************************************************************************
** Start of comments
** Procedure : UpdateInvitation
** Description : Given the task ID of the appointment (p_TaskID) and the
** Resource ID of the invitee (p_INVITEE) this procedure will
** respond to the notifications from the attendees of the appointment.
** Parameters :
** name direction type required?
** ---- --------- ---- ---------
** itemtype IN VARCHAR2 required
** itemkey IN VARCHAR2 required
** actid IN NUMBER required
** funcmode IN VARCHAR2 required
** resultout OUT VARCHAR2 required
** End of comments
*******************************************************************************/
( itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS
l_object_version_number NUMBER :=1 ;
JTF_TASK_ASSIGNMENTS_PVT.update_task_assignment
(
p_api_version => 1.0,
p_object_version_number => l_object_version_number,
p_init_msg_list => 'T', --?
p_task_assignment_id => l_task_assignment_id,
p_resource_type_code => l_resource_type,
p_resource_id => l_InviteeResourceID,
p_schedule_flag => fnd_api.g_miss_char, --Y Or N??
p_actual_start_date => null, --?
p_actual_end_date => null, --?
p_assignment_status_id => l_assignment_status_id,
p_show_on_calendar => 'Y',
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 UpdateInvitation;
)IS SELECT source_id EmployeeNumber
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = b_ResourceID;
SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_ItemKey
FROM DUAL;
** Save the workflow itemtype and item key so we can update the reminder WF
** if the start date or remind me settings change
***************************************************************************/
UPDATE jtf_task_all_assignments
SET reminder_wf_item_type = l_itemtype
, reminder_wf_item_key = l_ItemKey
WHERE task_id = p_TaskID;
)IS SELECT jta.task_assignment_id TaskAssignmentID
, to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
, jta.resource_id INVITEE
, jta.resource_type_code INVITEE_CODE
, jtl.task_name TASK_NAME
, jtl.description TASK_DESCRIPTION
, jtb.calendar_start_date START_DATE
, jtb.calendar_end_date END_DATE
, NVL( jtb.timezone_id
,(NVL( FND_PROFILE.Value('SERVER_TIMEZONE_ID')
, 4)
)
) SourceTimezoneID
FROM jtf_tasks_b jtb
, jtf_tasks_tl jtl
, jtf_task_all_assignments jta
WHERE jtb.task_id = jtl.task_id
AND jtl.language = userenv('LANG')
AND jta.task_id = jtb.task_id
AND jta.show_on_calendar = 'Y'
AND jta.assignment_status_id <> 4
AND jtb.task_id = b_TaskID;
PROCEDURE UpdateReminders
/*******************************************************************************
** Start of comments
** Procedure : UpdateReminders
** Description : Given the task ID and a new reminder date this procedure will
** update all the reminders for the appointment, should only be
** called if the reminder me or start date has changed
** Parameters :
** name direction type required?
** ---- --------- ---- ---------
** p_api_version IN NUMBER required
** p_init_msg_list IN VARCHAR2 optional
** p_commit IN VARCHAR2 optional
** x_return_status OUT VARCHAR2 optional
** x_msg_count OUT NUMBER required
** x_msg_data OUT VARCHAR2 required
** p_TaskID IN NUMBER required
** p_RemindDate IN DATE required
** Notes :
** 1) If an invitee does not exist in the WF directory a notification will
** be send to the invitor saying that the invitation was not send.
** 2) Currently invitations are only send to employees
** 3) The WFs won't be started until a commmit is done.
**
** End of comments
*******************************************************************************/
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_TaskID IN NUMBER -- Task ID of the appointment
, p_RemindDate IN DATE -- NEW Date/Time the reminder needs to be send
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UpdateReminderWF';
)IS SELECT jta.task_assignment_id TaskassignmentID
, jta.reminder_wf_item_type ReminderWFItemType
, jta.reminder_wf_item_key ReminderWFItemKey
, jta.resource_id INVITEE
, jtb.owner_id INVITOR
, jtl.task_name TASK_NAME
, jtl.description TASK_DESCRIPTION
, jtb.calendar_start_date START_DATE
, jtb.calendar_end_date END_DATE
FROM jtf_tasks_b jtb
, jtf_tasks_tl jtl
, jtf_task_all_assignments jta
WHERE jtb.task_id = jtl.task_id
AND jtl.language = userenv('LANG')
AND jta.task_id = jtb.task_id
AND jta.show_on_calendar = 'Y'
AND jta.assignee_role = 'OWNER'
AND jtb.task_id = b_TaskID;
** Save the workflow itemtype and item key so we can update the reminder WF
** if the start date or remind me settings change
***************************************************************************/
UPDATE jtf_task_all_assignments
SET reminder_wf_item_type = r_Task.ReminderWFItemType
, reminder_wf_item_key = r_Task.ReminderWFItemKey
WHERE task_id = p_TaskID;
END UpdateReminders;
PROCEDURE UpdateAttendee
/*******************************************************************************
** Start of comments
** Procedure : UpdateReminders
** Description : Given the task ID and a new reminder date this procedure will
** update all the reminders for the appointment, should only be
** called if the reminder me or start date has changed
** Parameters :
** name direction type required?
** ---- --------- ---- ---------
** p_api_version IN NUMBER required
** p_init_msg_list IN VARCHAR2 optional
** p_commit IN VARCHAR2 optional
** x_return_status OUT VARCHAR2 optional
** x_msg_count OUT NUMBER required
** x_msg_data OUT VARCHAR2 required
** p_TaskID IN NUMBER required
** p_RemindDate IN DATE required
** Notes :
** 1) If an invitee does not exist in the WF directory a notification will
** be send to the invitor saying that the invitation was not send.
** 2) Currently invitations are only send to employees
** 3) The WFs won't be started until a commmit is done.
**
** End of comments
*******************************************************************************/
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_INVITEE IN NUMBER -- Resource ID of Invitee
, p_INVITEE_TYPE IN VARCHAR2 --Resource Type of the INVITEE
, p_TaskID IN NUMBER -- Task ID of the appointment
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UpdateAttendeeWF';
SELECT tsk_vl.task_id, owner_id,owner_type_code
FROM jtf_tasks_vl tsk_vl,
jtf_task_all_assignments tsk_asg
WHERE tsk_vl.task_id = p_task_id
and tsk_vl.task_id = tsk_asg.task_id
AND assignee_role = 'OWNER' ;
)IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
, jta.task_assignment_id TASK_ASSIGNMENT_ID
, jta.resource_id INVITEE
, jta.resource_type_code INVITEE_CODE
, jtl.task_name TASK_NAME
, jtl.description TASK_DESCRIPTION
, jtb.calendar_start_date START_DATE
, jtb.task_type_id TYPE_ID
, jtb.task_priority_id PRIORITY_ID
, (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
, jtb.timezone_id TIMEZONE_ID
FROM jtf_tasks_b jtb
, jtf_tasks_tl jtl
, jtf_task_all_assignments jta
WHERE jtb.task_id = jtl.task_id
AND jtl.language = userenv('LANG')
AND jta.task_id = jtb.task_id
AND jta.show_on_calendar = 'Y'
AND jta.assignee_role = 'ASSIGNEE'
AND jtb.task_id = b_TaskID
AND jta.resource_id = b_INVITEE
AND jta.resource_type_code = b_INVITEE_TYPE ;
, process => 'UPDATE_INVITATION'
);
END UpdateAttendee;
) IS SELECT meaning
FROM WF_LOOKUPS
WHERE lookup_type = 'JTF_DAY_NUMBERS'
AND lookup_code = b_DayNumber;
) IS SELECT meaning
FROM WF_LOOKUPS
WHERE lookup_type = 'JTF_DAY_NAMES'
AND lookup_code = b_DayCode;
PROCEDURE DeleteAttendee
/*******************************************************************************
** Start of comments
** Procedure : DeleteAttendee
** Description : Given the task ID and the Invitee id this procedure will send reminders to
** attendees if the appointment is deleted
**
** Parameters :
** name direction type required
** ---- --------- ---- ---------
** p_api_version IN NUMBER required
** p_init_msg_list IN VARCHAR2 optional
** p_commit IN VARCHAR2 optional
** x_return_status OUT VARCHAR2 optional
** x_msg_count OUT NUMBER required
** x_msg_data OUT VARCHAR2 required
** p_TaskID IN NUMBER required
** p_RemindDate IN DATE required
** p_INVITEE IN NUMBER required
** p_INVITEE_TYPE IN VARCHAR2 required
** Notes :
** 1) If an invitee does not exist in the WF directory a notification will
** be send to the invitor saying that the invitation was not send.
** 2) Currently invitations are only send to employees
** 3) The WFs won't be started until a commmit is done.
**
** End of comments
*******************************************************************************/
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_INVITEE IN NUMBER -- Resource ID of Invitee
, p_INVITEE_TYPE IN VARCHAR2 --Resource Type of the INVITEE
, p_TaskID IN NUMBER -- Task ID of the appointment
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UpdateAttendeeWF';
SELECT tsk_vl.task_id, owner_id,owner_type_code
FROM jtf_tasks_vl tsk_vl,
jtf_task_all_assignments tsk_asg
WHERE tsk_vl.task_id = p_task_id
and tsk_vl.task_id = tsk_asg.task_id
AND assignee_role = 'OWNER' ;
)IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
, jta.task_assignment_id TASK_ASSIGNMENT_ID
, jta.resource_id INVITEE
, jta.resource_type_code INVITEE_CODE
, jtl.task_name TASK_NAME
, jtl.description TASK_DESCRIPTION
, jtb.calendar_start_date START_DATE
, jtb.task_type_id TYPE_ID
, jtb.task_priority_id PRIORITY_ID
, (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
, jtb.timezone_id TIMEZONE_ID
FROM jtf_tasks_b jtb
, jtf_tasks_tl jtl
, jtf_task_all_assignments jta
WHERE jtb.task_id = jtl.task_id
AND jtl.language = userenv('LANG')
AND jta.task_id = jtb.task_id
AND jta.show_on_calendar = 'Y'
AND jta.assignee_role = 'ASSIGNEE'
AND jtb.task_id = b_TaskID
AND jta.resource_id = b_INVITEE
AND jta.resource_type_code = b_INVITEE_TYPE ;
, process => 'DELETE_INVITATION'
);
END DeleteAttendee;
)IS SELECT jtr.occurs_which
, jtr.day_of_week
, jtr.date_of_month
, jtr.occurs_month
, jtr.occurs_uom
, jtr.occurs_every
, jtr.occurs_number
, jtr.start_date_active
, jtr.end_date_active
, jtr.sunday
, jtr.monday
, jtr.tuesday
, jtr.wednesday
, jtr.thursday
, jtr.friday
, jtr.saturday
, jtb.timezone_id
, (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 duration
FROM jtf_task_recur_rules jtr
, jtf_tasks_b jtb
WHERE jtb.recurrence_rule_id = jtr.recurrence_rule_id
AND jtb.task_id = b_task_id;