[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_update_next IN BOOLEAN
, p_shift_bands IN CAC_AVLBLTY_TIME_BAND_VARRAY
, x_avlblty_time IN OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
, x_index IN OUT NOCOPY NUMBER
) IS
l_start_dt DATE;
IF (p_update_next)
THEN
-- see if the previous one should be merged with the current one
IF (((NOT p_blank_record) AND (x_avlblty_time(x_index).PERIOD_CATEGORY_ID =
p_category_id)) OR (p_blank_record AND
x_avlblty_time(x_index).PERIOD_CATEGORY_ID IS NULL))
THEN
x_avlblty_time(x_index).END_DATE_TIME := p_end_dt;
, 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_Schdl_Cat 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
, p_return_type IN VARCHAR2 -- 'D': Detailed schedule
-- 'S': Schedule Summary
-- 'B': Both detailed and summary of schedule
, x_Schedule OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
, x_Schedule_Summary OUT NOCOPY CAC_AVLBLTY_SUMMARY_VARRAY
-- return schedule
)
IS
CURSOR C_SCHEDULE
(
b_schdl_cat VARCHAR2,
b_object_type VARCHAR2,
b_object_id NUMBER,
b_start_dt DATE,
b_end_dt DATE,
b_busy_tentative VARCHAR2
) IS
SELECT CSSB.SCHEDULE_ID,
CSSD.START_DATE_TIME,
CSSD.END_DATE_TIME,
CSPVL.PERIOD_ID,
CSPVL.HAS_DETAILS,
CSPVL.DURATION,
CSPVL.DURATION_UOM,
NVL(CSPVL.PERIOD_NAME,FL.MEANING) AS PERIOD_NAME,
CSPCVL.PERIOD_CATEGORY_ID,
CSPCVL.PERIOD_CATEGORY_NAME,
DECODE(CSPCVL.FREE_BUSY_TYPE,'FREE','FREE','BUSY','BUSY',NVL(b_busy_tentative,'TENTATIVE')) FREE_BUSY_TYPE,
CSPCVL.DISPLAY_COLOR
FROM CAC_SR_SCHDL_OBJECTS CSSO,
CAC_SR_SCHEDULES_B CSSB,
CAC_SR_SCHDL_DETAILS CSSD,
CAC_SR_PERIODS_VL CSPVL,
CAC_SR_PERIOD_CATS_VL CSPCVL,
FND_LOOKUPS FL
WHERE CSSO.OBJECT_TYPE = b_object_type
AND CSSO.OBJECT_ID = b_object_id
AND CSSO.START_DATE_ACTIVE <= b_end_dt
AND CSSO.END_DATE_ACTIVE >= b_start_dt
AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
AND CSSB.DELETED_DATE IS NULL
AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
FROM CAC_SR_PUBLISH_SCHEDULES
WHERE OBJECT_TYPE = b_object_type
AND OBJECT_ID = b_object_id
AND b_schdl_cat IS NULL
))
AND CSSD.SCHEDULE_OBJECT_ID = CSSO.SCHEDULE_OBJECT_ID
AND CSSD.START_DATE_TIME < b_end_dt
AND CSSD.END_DATE_TIME > b_start_dt
AND CSPVL.PERIOD_ID = CSSD.PERIOD_ID
AND CSPCVL.PERIOD_CATEGORY_ID = CSPVL.PERIOD_CATEGORY_ID
AND FL.LOOKUP_TYPE(+) = 'CAC_SR_WEEK_DAY'
AND FL.LOOKUP_CODE(+) = CSPVL.week_day_num
ORDER BY CSSD.START_DATE_TIME;
SELECT CSEVL.EXCEPTION_ID,
CSEVL.START_DATE_TIME,
DECODE(CSEVL.WHOLE_DAY_FLAG,'N',CSEVL.END_DATE_TIME,CSEVL.END_DATE_TIME+1) END_DATE_TIME, -- Add 24 hrs if it's whole day
CSEVL.EXCEPTION_NAME,
CSPVL.PERIOD_CATEGORY_ID,
CSPVL.PERIOD_CATEGORY_NAME,
DECODE(CSPVL.FREE_BUSY_TYPE,'FREE','FREE','BUSY','BUSY',NVL(b_busy_tentative,'TENTATIVE')) FREE_BUSY_TYPE,
CSPVL.DISPLAY_COLOR,
CSEVL.HR_CAL_EVENT_TYPE,
CSEVL.HR_CAL_EVENT_ID,
DECODE(CSSE.SCHEDULE_OBJECT_ID,NULL,
DECODE(CSEVL.HR_CAL_EVENT_TYPE,NULL,
DECODE(CSEVL.HR_CAL_EVENT_ID,NULL,3,2),1),
DECODE(CSEVL.HR_CAL_EVENT_TYPE,NULL,
DECODE(CSEVL.HR_CAL_EVENT_ID,NULL,6,5),4)) LEVEL_IND
FROM CAC_SR_SCHDL_OBJECTS CSSO,
CAC_SR_SCHEDULES_B CSSB,
CAC_SR_SCHDL_EXCEPTIONS CSSE,
CAC_SR_EXCEPTIONS_VL CSEVL,
CAC_SR_PERIOD_CATS_VL CSPVL
WHERE CSSO.OBJECT_TYPE = b_object_type
AND CSSO.OBJECT_ID = b_object_id
AND CSSO.START_DATE_ACTIVE <= b_end_dt
AND CSSO.END_DATE_ACTIVE >= b_start_dt
AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
AND CSSB.DELETED_DATE IS NULL
AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
FROM CAC_SR_PUBLISH_SCHEDULES
WHERE OBJECT_TYPE = b_object_type
AND OBJECT_ID = b_object_id
AND b_schdl_cat IS NULL
))
AND ( ((CSSE.SCHEDULE_ID = CSSB.SCHEDULE_ID) AND (CSSE.SCHEDULE_OBJECT_ID IS NULL OR CSSE.SCHEDULE_OBJECT_ID = CSSO.SCHEDULE_OBJECT_ID))
OR CSSE.SCHEDULE_OBJECT_ID = CSSO.SCHEDULE_OBJECT_ID)
AND CSEVL.EXCEPTION_ID = CSSE.EXCEPTION_ID
AND CSPVL.PERIOD_CATEGORY_ID = CSEVL.PERIOD_CATEGORY_ID
ORDER BY LEVEL_IND,CSEVL.START_DATE_TIME;
l_hr_cal_events.DELETE;
, p_ObjectID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
, p_PeriodStartDateTime IN DATE -- start date and time of period of interest
, p_PeriodEndDateTime IN DATE -- end date and time of period of interest
, p_OpagueBkngCat IN JTF_NUMBER_TABLE -- Booking Categories (i.e. task types) that should be considered OPAGUE
, p_BookingStatus IN VARCHAR2 -- Are we looking for Firm or Soft bookings
, p_BusyTentative IN VARCHAR2 -- How to treat FREEBUSYTIME objects with FREEBUSYTYPE = BUSY TENTATIVE?
-- FREE: BUSY TENTATIVE means FREE
-- BUSY: BUSY TENTATIVE means BUSY
-- NULL: leave the interpretation to caller
, x_BookingData IN OUT CAC_SR_FREEBUSYTIME_VARRAY
-- returns the existings bookings for the Object Instance
, x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
-- 'E': API completed with recoverable errors; explanation on errorstack (Warnings)
'SELECT GREATEST( CAC_AVLBLTY_PVT.AdjustForTimezone( jtb.timezone_id ' ||
', :b_ToTimeZone ' ||
', jtb.calendar_start_date ' ||
') ' ||
', :b_PeriodStartDateTime ' ||
') StartDateTime ' ||
', LEAST( CAC_AVLBLTY_PVT.AdjustForTimezone( jtb.timezone_id ' ||
', :b_ToTimeZone ' ||
', jtb.calendar_end_date ' ||
') ' ||
', :b_PeriodEndDateTime ' ||
') EndDateTime ' ||
', DECODE( jta.free_busy_type, ''FREE'',''FREE'' ' ||
', ''BUSY'',''BUSY'' ' ||
', ''TENTATIVE'',NVL(:b_BusyTentative,''TENTATIVE'') ' ||
') FBType ' ||
', jtb.task_type_id CategoryID ' ||
', jtb.entity CategoryType '||
'FROM jtf_task_all_assignments jta ' ||
', jtf_tasks_b jtb ' ||
', ( SELECT 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'') '; -- Add appointment here once they go to servertimezone
PROCEDURE INSERT_SCHEDULE_DETAILS
/*******************************************************************************
**
** INSERT_SCHEDULE_DETAILS
**
** popluates the schedule details table from the pl/sql table
**
*******************************************************************************/
( p_Schedule_Id IN NUMBER -- id of the schedule
, p_Schedule_Object_Id IN NUMBER
, p_Schedule_Details IN SCHDL_DETAILS_TBL_TYPE
) IS
l_created_by NUMBER;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_date := SYSDATE;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
INSERT INTO CAC_SR_SCHDL_DETAILS
(
SCHEDULE_DETAIL_ID,
SCHEDULE_ID,
SCHEDULE_OBJECT_ID,
PERIOD_ID,
START_DATE_TIME,
END_DATE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
CAC_SR_SCHDL_DETAILS_S.NEXTVAL,
p_schedule_id,
p_schedule_object_id,
p_Schedule_Details(i).period_id,
p_Schedule_Details(i).start_date_time,
p_Schedule_Details(i).end_date_time,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
END INSERT_SCHEDULE_DETAILS;
SELECT DTLS.TEMPLATE_DETAIL_ID,
DTLS.TEMPLATE_DETAIL_SEQ,
DTLS.TEMPLATE_ID,
DTLS.CHILD_PERIOD_ID,
DTLS.CHILD_TEMPLATE_ID,
CSPB.DURATION,
CSPB.DURATION_UOM
FROM CAC_SR_PERIODS_B CSPB,
(SELECT CSTD.TEMPLATE_DETAIL_ID,
CSTD.TEMPLATE_DETAIL_SEQ,
CSTD.TEMPLATE_ID,
CSTD.CHILD_PERIOD_ID,
CSTD.CHILD_TEMPLATE_ID
FROM CAC_SR_TMPL_DETAILS CSTD
START WITH CSTD.TEMPLATE_ID = b_tmpl_id
CONNECT BY PRIOR CSTD.CHILD_TEMPLATE_ID = CSTD.TEMPLATE_ID
ORDER SIBLINGS BY TEMPLATE_DETAIL_SEQ) DTLS
WHERE CSPB.PERIOD_ID(+) = DTLS.CHILD_PERIOD_ID
ORDER BY TEMPLATE_DETAIL_SEQ; --ADDED FOR BUG#7491187
SELECT DTLS.TEMPLATE_DETAIL_ID,
DTLS.TEMPLATE_DETAIL_SEQ,
DTLS.TEMPLATE_ID,
DTLS.CHILD_PERIOD_ID,
DTLS.CHILD_TEMPLATE_ID,
CSPB.WEEK_DAY_NUM,
CSPB.START_TIME_MS,
CSPB.DURATION,
CSPB.DURATION_UOM,
CSTB.TEMPLATE_LENGTH_DAYS
FROM CAC_SR_TEMPLATES_B CSTB,
CAC_SR_PERIODS_B CSPB,
(SELECT CSTD.TEMPLATE_DETAIL_ID,
CSTD.TEMPLATE_DETAIL_SEQ,
CSTD.TEMPLATE_ID,
CSTD.CHILD_PERIOD_ID,
CSTD.CHILD_TEMPLATE_ID
FROM CAC_SR_TMPL_DETAILS CSTD
START WITH CSTD.TEMPLATE_ID = b_tmpl_id
CONNECT BY PRIOR CSTD.CHILD_TEMPLATE_ID = CSTD.TEMPLATE_ID
ORDER SIBLINGS BY TEMPLATE_DETAIL_SEQ) DTLS
WHERE CSTB.TEMPLATE_ID(+) = DTLS.CHILD_TEMPLATE_ID
AND CSPB.PERIOD_ID(+) = DTLS.CHILD_PERIOD_ID
ORDER BY TEMPLATE_DETAIL_SEQ; --ADDED FOR BUG#7491187
SELECT DTLS.TEMPLATE_DETAIL_ID,
DTLS.TEMPLATE_DETAIL_SEQ,
DTLS.TEMPLATE_ID,
DTLS.CHILD_PERIOD_ID,
DTLS.CHILD_TEMPLATE_ID,
DTLS.DAY_START,
DTLS.DAY_STOP,
CSPB.START_TIME_MS,
CSPB.END_TIME_MS,
CSPB.DURATION,
CSPB.DURATION_UOM
FROM CAC_SR_PERIODS_B CSPB,
(SELECT CSTD.TEMPLATE_DETAIL_ID,
CSTD.TEMPLATE_DETAIL_SEQ,
CSTD.TEMPLATE_ID,
CSTD.CHILD_PERIOD_ID,
CSTD.CHILD_TEMPLATE_ID,
CSTD.DAY_START,
CSTD.DAY_STOP
FROM CAC_SR_TMPL_DETAILS CSTD
START WITH CSTD.TEMPLATE_ID = b_tmpl_id
CONNECT BY PRIOR CSTD.CHILD_TEMPLATE_ID = CSTD.TEMPLATE_ID
ORDER SIBLINGS BY TEMPLATE_DETAIL_SEQ) DTLS
WHERE CSPB.PERIOD_ID(+) = DTLS.CHILD_PERIOD_ID
ORDER BY CHILD_PERIOD_ID NULLS FIRST , TEMPLATE_DETAIL_SEQ; --ADDED FOR BUG#7491187
SELECT SCHEDULE_OBJECT_ID
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, START_TEMPLATE_DETAIL_ID
FROM CAC_SR_SCHDL_OBJECTS
WHERE SCHEDULE_ID = b_schedule_id;
INSERT_SCHEDULE_DETAILS
(
p_Schedule_id,
NULL,
l_schdl_dtls_data
);
INSERT_SCHEDULE_DETAILS
(
p_Schedule_id,
ref_schdl_objects.schedule_object_id,
l_schdl_dtls_data
);
INSERT_SCHEDULE_DETAILS
(
p_Schedule_id,
ref_schdl_objects.schedule_object_id,
l_obj_schdl_dtls
);
SELECT CSTB.TEMPLATE_ID
, CSTB.TEMPLATE_TYPE
, CSTB.TEMPLATE_LENGTH_DAYS
, CSSB.START_DATE_ACTIVE
, CSSB.END_DATE_ACTIVE
FROM CAC_SR_SCHEDULES_B CSSB
, CAC_SR_TEMPLATES_B CSTB
WHERE CSSB.SCHEDULE_ID = b_schedule_id
AND CSTB.TEMPLATE_ID = CSSB.TEMPLATE_ID;
DELETE FROM CAC_SR_SCHDL_DETAILS
WHERE SCHEDULE_OBJECT_ID = p_schedule_object_id;
INSERT_SCHEDULE_DETAILS
(
p_Schedule_id,
p_Schedule_Object_Id,
l_schdl_dtls_data
);
SELECT OBJECT_TYPE
, OBJECT_ID
, START_DATE_ACTIVE
, END_DATE_ACTIVE
FROM CAC_SR_SCHDL_OBJECTS
WHERE SCHEDULE_ID = b_schedule_id;
PROCEDURE POST_UPDATE_SCHEDULE
/*******************************************************************************
**
** POST_UPDATE_SCHEDULE
**
** expands the schedule for the given:
** - Schedule
** - template
** - duration
** and submits business events
**
*******************************************************************************/
( p_Schedule_Id IN NUMBER -- id of the schedule
, p_Schedule_Category IN VARCHAR2
, p_Schdl_Tmpl_Id IN NUMBER
, p_Schdl_Tmpl_Length IN NUMBER
, p_Schdl_Tmpl_Type IN VARCHAR2
, p_Schdl_Start_Date IN DATE
, p_Schdl_End_Date IN DATE
)
IS
CURSOR c_get_schdl_objects
(
b_schedule_id NUMBER
) IS
SELECT CSSO.OBJECT_TYPE
, CSSO.OBJECT_ID
, CSSO.START_DATE_ACTIVE
, CSSO.END_DATE_ACTIVE
, CSSD.SCHEDULE_OBJECT_ID
FROM CAC_SR_SCHDL_OBJECTS CSSO,
(SELECT SCHEDULE_OBJECT_ID, MIN(START_DATE_TIME)
FROM CAC_SR_SCHDL_DETAILS
WHERE SCHEDULE_ID = b_schedule_id
) CSSD
WHERE CSSO.SCHEDULE_ID = b_schedule_id
AND CSSD.SCHEDULE_OBJECT_ID(+) = CSSO.SCHEDULE_OBJECT_ID;
DELETE FROM CAC_SR_SCHDL_DETAILS
WHERE SCHEDULE_ID = p_schedule_id;
CAC_AVLBLTY_EVENTS_PVT.RAISE_UPDATE_SCHEDULE
(
p_Schedule_Id,
p_Schedule_Category,
p_Schdl_Start_Date,
p_Schdl_End_Date
);
END POST_UPDATE_SCHEDULE;
PROCEDURE POST_DELETE_SCHEDULE
/*******************************************************************************
**
** POST_DELETE_SCHEDULE
**
** submits business events
**
*******************************************************************************/
( p_Schedule_Id IN NUMBER -- id of the schedule
, p_Schedule_Category IN VARCHAR2
, p_Schdl_Start_Date IN DATE
, p_Schdl_End_Date IN DATE
)
IS
BEGIN
CAC_AVLBLTY_EVENTS_PVT.RAISE_DELETE_SCHEDULE
(
p_Schedule_Id,
p_Schedule_Category,
p_Schdl_Start_Date,
p_Schdl_End_Date
);
END POST_DELETE_SCHEDULE;