[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IS SELECT DISTINCT fmu.menu_name Privilege
, fgs.instance_pk1_value ResourceID
, jrt.resource_name ResourceName
FROM fnd_grants fgs
, fnd_menus fmu
, fnd_objects fos
, jtf_rs_resource_extns_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_Grantee_Key
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_EMPLOYEE')
AND jrt.resource_id = to_number(fgs.instance_pk1_value) -- Modified by SBARAT on 30/05/2006 for bug# 5213367
AND jrt.LANGUAGE = USERENV('LANG');
IS SELECT DISTINCT DECODE(fmu.menu_name,'JTF_CAL_ADMIN_ACCESS','JTF_CAL_FULL_ACCESS',FMU.MENU_NAME) Privilege
, fgs.instance_pk1_value ResourceID
, jrt.group_name ResourceName
FROM fnd_grants fgs
, fnd_menus fmu
, fnd_objects fos
, 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 fmu.MENU_NAME in ('JTF_CAL_ADMIN_ACCESS','JTF_CAL_FULL_ACCESS')
AND fos.obj_name = 'JTF_TASK_RESOURCE'
AND fgs.grantee_key = b_GranteeKey --'1000001366'
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');
)IS SELECT /*+ INDEX(jtsb JTF_TASK_STATUSES_B_U1) */
jtb.task_id TaskId
, jtb.source_object_type_code SourceObjectTypeCode
, jtb.source_object_id SourceId
, jtb.customer_id CustomerId
, jtt.task_name ItemName
, jtb.calendar_start_date StartDate
, jtb.calendar_end_date EndDate
, jtb.timezone_id TimezoneID
, jtb.duration Duration -- always in minutes
, jtb.private_flag PrivateFlag -- needed to determin access level
, DECODE(jta.assignment_status_id ,18, DECODE(jtb.source_object_type_code ,'APPOINTMENT',1,0),0)
InviteIndicator
, DECODE(jtb.recurrence_rule_id
,NULL,0
,1
) RepeatIndicator
, DECODE(jtb.source_object_type_code ,'APPOINTMENT', x_preferences.ApptColor, x_preferences.TaskColor)
ItemColor
, DECODE(jtb.source_object_type_code ,'APPOINTMENT', x_preferences.ApptPrefix, x_preferences.TaskPrefix)
ItemPrefix
, Jtf_Cal_Utility_Pvt.GetItemURL -- can't join URL is dynamic..
( jtb.source_object_id
, jtb.source_object_type_code) URL
, jta.assignee_role AssigneeRole
, jtb.task_priority_id PriorityID -- Needed for todos
, jta.category_id CategoryID -- Needed for todos
, jtb.object_version_number AssignmentOVN -- Needed to update todos
, jta.object_version_number TaskOVN -- Needed to update todos
FROM jtf_task_all_assignments jta
, jtf_tasks_b jtb
, jtf_tasks_tl jtt
, jtf_task_statuses_b jtsb
WHERE jta.resource_id = b_ResourceID -- 101272224
AND jta.resource_type_code = b_ResourceType -- 'RS_EMPLOYEE'
AND jta.task_id = jtb.task_id -- join to tasks_b
AND jtb.task_status_id = jtsb.task_status_id -- join to to task_status_b
AND jtb.task_id = jtt.task_id -- join to tasks_tl
AND jtt.LANGUAGE = USERENV('LANG') -- join to tasks_tl
AND jta.show_on_calendar = 'Y'
AND jta.assignment_status_id <> 4 -- using status rejected for declined
AND NVL(jtsb.closed_flag,'N')<> 'Y'
AND ( jtb.calendar_start_date <= b_EndDate
OR jtb.calendar_start_date IS NULL
)
AND ( jtb.calendar_end_date >= b_StartDate
OR jtb.calendar_end_date IS NULL
)
AND jtb.entity <> 'BOOKING'
AND jtb.source_object_type_code <> 'EXTERNAL APPOINTMENT'
;
)IS SELECT DISTINCT jtb.source_id ItemSourceID
--, jtb.source_code ItemSourceCode
, jtf_cal_items_pvt.GetName(jtb.SOURCE_CODE, jtb.SOURCE_ID) ItemName
, jtb.source_code SourceCode
, jtb.source_id SourceID
, jtb.start_date StartDate
, jtb.end_date EndDate
, jtb.timezone_id TimezoneID
, jtb.url URL
, jtf_cal_items_pvt.GetUrlParams(jtb.SOURCE_CODE, jtb.SOURCE_ID) URLParams
FROM jtf_cal_items_b jtb
WHERE( jtb.start_date <= b_EndDate
)
AND ( jtb.end_date >= b_StartDate
)
AND jtb.resource_type = 'RS_GROUP'
AND jtb.resource_id IN --select groups that user is member of
(SELECT mem.group_id
FROM
jtf_rs_group_members mem,
jtf_rs_group_usages rgu
WHERE mem.resource_id = b_ResourceID
AND nvl(mem.delete_flag, 'N') <> 'Y'
AND rgu.group_id = mem.group_id
AND rgu.usage = 'CALENDAR_ITEMS')
UNION -- individual items
SELECT jtb.source_id ItemSourceID
--, jtb.source_code ItemSourceCode
, jtf_cal_items_pvt.GetName(jtb.SOURCE_CODE, jtb.SOURCE_ID) ItemName
, jtb.source_code SourceCode
, jtb.source_id SourceID
, jtb.start_date StartDate
, jtb.end_date EndDate
, jtb.timezone_id TimezoneID
, jtb.url URL
, jtf_cal_items_pvt.GetUrlParams(jtb.SOURCE_CODE, jtb.SOURCE_ID) URLParams
FROM jtf_cal_items_b jtb
WHERE jtb.resource_id = b_ResourceID
AND jtb.resource_type = 'RS_EMPLOYEE'
AND ( jtb.start_date <= b_EndDate
)
AND ( jtb.end_date >= b_StartDate
)
;
)IS SELECT DISTINCT TO_NUMBER(fgs.instance_pk1_value) GroupID
, Jtf_Cal_Utility_Pvt.GetGroupColor
( b_ResourceID
, 'RS_EMPLOYEE'
, TO_NUMBER(fgs.instance_pk1_value)
) Color
, Jtf_Cal_Utility_Pvt.GetGroupPrefix
( b_ResourceID
, 'RS_EMPLOYEE'
, TO_NUMBER(fgs.instance_pk1_value)
) Prefix
FROM fnd_grants fgs
, fnd_objects fos
, jtf_rs_group_usages rgu
WHERE fgs.object_id = fos.object_id -- grants joint to object
AND fos.obj_name = 'JTF_TASK_RESOURCE'
AND fgs.grantee_key = TO_CHAR(b_ResourceID)
AND fgs.start_date < SYSDATE
AND ( (fgs.end_date >= SYSDATE)
OR (fgs.end_date IS NULL)
)
AND fgs.instance_pk2_value = 'RS_GROUP'
AND rgu.GROUP_ID = TO_NUMBER(fgs.instance_pk1_value)
AND rgu.USAGE = 'GROUP_CALENDAR';