[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT resource_id
FROM jtf_task_all_assignments jta
WHERE jta.resource_type_code = 'PN_LOCATION'
AND jta.task_id = p_task_id;
SELECT location
FROM cac_view_collab_details_vl cdv
WHERE cdv.task_id = p_task_id;
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 to_char(jrt.resource_id) = fgs.instance_pk1_value -- Commented by SBARAT on 23/02/2006 for bug# 5045559
AND jrt.resource_id = to_number(fgs.instance_pk1_value) -- Added by SBARAT on 23/02/2006 for bug# 5045559
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');
SELECT task_view.TASK_ID TaskId,
booking.ASSIGNEE_ROLE ,
booking_status.NAME AS BOOKING_STATUS_NAME,
task_type.NAME AS TYPE_NAME,
task_priority.NAME AS PRIORITY_NAME,
task_view.TASK_NAME ItemName ,
task_view.calendar_start_date AS StartDate,
task_view.calendar_end_date AS EndDate,
task_view.SOURCE_OBJECT_TYPE_CODE SourceObjectTypeCode,
task_view.SOURCE_OBJECT_ID SourceId,
booking.assignment_status_id AssignmentStatus,
freebusy.meaning FREE_BUSY_STATUS,
booking.assignee_role AssigneeRole,
task_view.alarm_on RemindIndicator,
DECODE(task_view.recurrence_rule_id
,NULL,0
,1
) RepeatIndicator,
task_view.TIMEZONE_ID TimezoneID,
task_view.OWNER_ID,
task_view.OWNER_TYPE_CODE,
owner.SOURCE_NAME AS OWNER,
task_view.PRIVATE_FLAG PrivateFlag ,
task_view.DESCRIPTION,
jtf_object.name AS SOURCE_NAME,
jtf_task_utl.get_owner(task_view.SOURCE_OBJECT_TYPE_CODE, task_view.SOURCE_OBJECT_ID) AS SOURCE_INSTANCE
FROM
jtf_tasks_vl task_view,
jtf_task_statuses_vl booking_status,
jtf_task_types_tl task_type,
jtf_task_priorities_tl task_priority,
jtf_rs_resource_extns owner,
jtf_task_all_assignments booking,
fnd_lookups freebusy,
jtf_objects_tl jtf_object
WHERE
booking.resource_id = b_ResourceId --10125
AND booking.resource_type_code = b_ResourceType --'RS_EMPLOYEE'
AND booking.task_id = task_view.task_id
--AND task_view.entity = 'BOOKING' -- all bookings
AND (NVL(task_view.deleted_flag,'N') = 'N') -- not deleted
AND (task_view.calendar_start_date <= b_EndDate --sysdate + 5*360 --start date
OR task_view.calendar_start_date is null)
AND (task_view.calendar_end_date >= b_StartDate--sysdate - 5*360 --end date
OR task_view.calendar_end_date is null)
AND task_view.task_type_id = task_type.task_type_id -- type
AND task_type.language = userenv('LANG')
AND booking.assignment_status_id = booking_status.task_status_id -- booking status
AND NVL(booking_status.cancelled_flag, 'N') <>'Y' -- not cancelled
AND NVL(booking_status.rejected_flag, 'N') <> 'Y' --not rejected
AND task_view.task_priority_id = task_priority.task_priority_id --priority
AND task_priority.language = userenv('LANG')
AND task_view.owner_id = owner.resource_id --owner
AND task_view.owner_type_code = 'RS_' || owner.category
AND freebusy.lookup_type = 'CAC_VIEW_FREE_BUSY'
AND booking.free_busy_type = freebusy.lookup_code
AND task_view.source_object_type_code = jtf_object.object_code
AND task_view.source_object_type_code = 'EXTERNAL APPOINTMENT'
AND jtf_object.language = userenv('LANG') ;
)IS SELECT 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
, jta.assignment_status_id AssignmentStatus
, jtb.alarm_on RemindIndicator
, DECODE(jtb.recurrence_rule_id
,NULL,0
,1
) RepeatIndicator
, jta.assignee_role AssigneeRole
, jta.free_busy_type free_busy_type
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 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 (p_ShowDeclined = 'Y' AND (jta.assignment_status_id IN (3,4,18))
OR (p_ShowDeclined = 'N' AND jta.assignment_status_id IN (3,18)))
AND (p_ShowOpenInvite = 'Y' AND (jta.assignment_status_id IN (3,4,18))
OR (p_ShowOpenInvite = 'N' AND jta.assignment_status_id IN (3,4)))
AND NVL(jtb.deleted_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 = 'APPOINTMENT'
AND jtb.source_object_type_code <> 'EXTERNAL APPOINTMENT'
;
)IS SELECT 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(jtb.recurrence_rule_id
,NULL,0
,1
) RepeatIndicator
, jta.assignee_role AssigneeRole
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 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 NVL(jtb.deleted_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.source_object_type_code <> 'APPOINTMENT'
;*/
SELECT /*+ first_rows */ task_view.TASK_ID,
assignment.TASK_ASSIGNMENT_ID,
assignment.ASSIGNEE_ROLE,
task_status.NAME AS STATUS_NAME,
assign_status.NAME AS ASSIGN_STATUS_NAME,
task_type.NAME AS TYPE_NAME,
task_priority.NAME AS PRIORITY_NAME,
task_view.TASK_NAME,
--task_view.CALENDAR_START_DATE,
--task_view.CALENDAR_END_DATE,
assignment.BOOKING_START_DATE CALENDAR_START_DATE,
assignment.BOOKING_END_DATE CALENDAR_END_DATE,
task_view.SOURCE_OBJECT_TYPE_CODE,
task_view.SOURCE_OBJECT_ID,
task_view.CUSTOMER_ID,
task_view.TASK_CONFIRMATION_STATUS,
DECODE(task_view.recurrence_rule_id
,NULL,0
,1
) RepeatIndicator,
task_view.TIMEZONE_ID,
task_view.OWNER_ID,
task_view.OWNER_TYPE_CODE,
owner.SOURCE_NAME AS OWNER,
party.PARTY_NAME AS CUSTOMER_NAME,
location.CITY,
task_view.PRIVATE_FLAG,
task_view.DESCRIPTION,
jtf_object.name AS SOURCE_NAME,
jtf_task_utl.get_owner(task_view.SOURCE_OBJECT_TYPE_CODE, task_view.SOURCE_OBJECT_ID) AS SOURCE_INSTANCE,
assignment.free_busy_type free_busy_type
FROM
jtf_tasks_vl task_view,
jtf_task_statuses_tl task_status,
jtf_task_statuses_vl assign_status,
jtf_task_types_tl task_type,
jtf_task_priorities_tl task_priority,
hz_parties party,
hz_party_sites site,
hz_locations location,
jtf_rs_resource_extns owner,
jtf_task_all_assignments assignment,
jtf_objects_tl jtf_object
WHERE
assignment.resource_id = b_ResourceId --13475
AND assignment.resource_type_code = b_ResourceType --'RS_EMPLOYEE'
AND assignment.task_id = task_view.task_id
AND task_view.entity = 'TASK'
AND task_view.task_type_id <> 22 -- not escalations
AND (NVL(task_view.deleted_flag,'N') = 'N') -- not deleted
AND (NVL(assignment.show_on_calendar,'Y') = 'Y') -- for backward compatibility
AND assignment.booking_start_date <= b_EndDate --sysdate + 5*360 --start date
AND assignment.booking_end_date >= b_StartDate --sysdate - 5*360 --end date
AND task_view.task_type_id = task_type.task_type_id -- type
AND task_type.language = userenv('LANG')
AND task_view.task_status_id = task_status.task_status_id -- task status
AND task_status.language = userenv('LANG')
AND assignment.assignment_status_id = assign_status.task_status_id -- assignment status
AND NVL(assign_status.cancelled_flag, 'N') <>'Y' -- not cancelled
AND task_view.task_priority_id = task_priority.task_priority_id --priority
AND task_priority.language = userenv('LANG')
AND task_view.customer_id = party.party_id(+) --customer
AND task_view.address_id = site.party_site_id(+) --task location
AND site.location_id = location.location_id(+)
AND task_view.source_object_type_code = jtf_object.object_code --source
AND jtf_object.language = userenv('LANG')
AND task_view.owner_id = owner.resource_id --owner
AND task_view.owner_type_code = 'RS_' || owner.category
AND assignment.ASSIGNEE_ROLE= decode (1, (select count(task_assignment_id)
from jtf_task_all_assignments
where task_id=task_view.task_id
and resource_id = b_ResourceId
and resource_type_code=b_ResourceType),
assignment.ASSIGNEE_ROLE,'ASSIGNEE')
AND assignment.free_busy_type <> 'FREE' ;
)IS SELECT DISTINCT jtb.source_id ItemSourceID
, jtb.cal_item_id CalItemId
, jtb.source_code SourceCode
, jtb.source_id SourceID
, jtb.start_date StartDate
, jtb.end_date EndDate
, jtb.timezone_id TimezoneID
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.cal_item_id CalItemId
, jtb.source_code SourceCode
, jtb.source_id SourceID
, jtb.start_date StartDate
, jtb.end_date EndDate
, jtb.timezone_id TimezoneID
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
)
;
SELECT a.assignment_id
FROM per_assignments_f a
, jtf_rs_resource_extns r
WHERE r.resource_id = p_emp_resource_id
AND a.person_id = r.source_id
AND a.primary_flag = 'Y'
AND trunc(SYSDATE) BETWEEN NVL(a.effective_start_date, trunc(SYSDATE))
AND NVL(a.effective_end_date, trunc(SYSDATE+1));
SELECT r.resource_id
FROM per_assignments_f a
, jtf_rs_resource_extns r
WHERE a.assignment_id = p_per_assignment_id
AND r.source_id = a.person_id
AND r.category = 'EMPLOYEE';
SELECT fmu.menu_name
FROM fnd_grants fgs
, fnd_menus fmu
, fnd_objects fos
WHERE fgs.object_id = fos.object_id
AND fgs.menu_id = fmu.menu_id
AND fos.obj_name = 'CAC_CAL_RESOURCES'
AND fgs.grantee_key = FND_GLOBAL.USER_NAME
AND fgs.grantee_type = 'USER'
AND fgs.start_date < SYSDATE
AND ( fgs.end_date >= SYSDATE
OR fgs.end_date IS NULL
)
and fgs.instance_type = 'INSTANCE'
AND fgs.instance_pk1_value = TO_CHAR(b_resource_id)
AND fgs.instance_pk2_value = b_resource_type;
)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';
Select per.party_name into person_party_name
From hz_parties per, hz_relationships hr
Where hr.subject_table_name = 'HZ_PARTIES'
and hr.object_table_name = 'HZ_PARTIES'
and hr.directional_flag = 'F'
and hr.subject_id = per.party_id
and per.party_type = 'PERSON'
and hr.party_id = p_party_id;