[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
, p_Start_Date_Time IN DATE -- start date and time of period of interest
, p_End_Date_Time IN DATE -- end date and time of period of interest
, p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
, p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
, p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
-- FREE: BUSY TENTATIVE means FREE
-- BUSY: BUSY TENTATIVE means BUSY
-- NULL: leave the interpretation to caller
, x_Schedule OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
-- return schedule
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
, p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
, p_Start_Date IN DATE -- start date of period of interest
, p_End_Date IN DATE -- end date of period of interest
, p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
, p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
, p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
-- FREE: BUSY TENTATIVE means FREE
-- BUSY: BUSY TENTATIVE means BUSY
-- NULL: leave the interpretation to caller
, x_Schedule_Summary OUT NOCOPY CAC_AVLBLTY_SUMMARY_VARRAY
-- return schedule summary
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
, p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
, p_Start_Date_Time IN DATE -- start date and time of period of interest
, p_End_Date_Time IN DATE -- end date and time of period of interest
, p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
, p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
-- FREE: BUSY TENTATIVE means FREE
-- BUSY: BUSY TENTATIVE means BUSY
-- NULL: leave the interpretation to caller
, p_task_assignment_id IN NUMBER DEFAULT NULL -- specifies the task assignment id to be ignored while checking availability
-- Added by lokumar for bug#6345516
, x_Available OUT NOCOPY VARCHAR2 -- 'T' or 'F'
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack
SELECT GREATEST( CAC_AVLBLTY_PVT.ADJUST_FOR_TIMEZONE( jtb.timezone_id
, b_ToTimeZone
, jtb.calendar_start_date
)
, b_StartDate
) StartDateTime
, LEAST( CAC_AVLBLTY_PVT.ADJUST_FOR_TIMEZONE( jtb.timezone_id
, b_ToTimeZone
, jtb.calendar_end_date
)
, b_EndDate
) EndDateTime
, DECODE( jta.free_busy_type, 'FREE','FREE'
, 'BUSY','BUSY'
, 'TENTATIVE',NVL(b_BusyTentative,'TENTATIVE')
) FBType
, jtb.task_type_id CategoryID
, jtb.entity CategoryType
, jta.task_assignment_id
FROM jtf_task_all_assignments jta
, jtf_tasks_b jtb
, ( SELECT /*+ INDEX(jts JTF_TASK_STATUSES_B_U1) */ jts.task_status_id
FROM jtf_task_statuses_b jts
WHERE jts.assignment_status_flag = 'Y'
AND NVL(jts.closed_flag,'N') = 'N'
AND NVL(jts.completed_flag,'N') = 'N'
AND NVL(jts.rejected_flag,'N') = 'N'
AND NVL(jts.on_hold_flag,'N') = 'N'
AND NVL(jts.cancelled_flag,'N') = 'N'
) jto
WHERE jta.resource_type_code = b_ObjectType
AND jta.resource_id = b_ObjectID
AND jta.assignment_status_id = jto.task_status_id
AND jta.task_id = jtb.task_id
AND jtb.open_flag = 'Y'
AND jtb.calendar_end_date >= b_StartDate
AND jtb.calendar_start_date <= b_EndDate
AND jtb.entity IN ('BOOKING','TASK','APPOINTMENT');