[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row(
x_error OUT NOCOPY VARCHAR2
, x_rowid IN OUT NOCOPY VARCHAR2
, x_shift_construct_id IN OUT NOCOPY NUMBER
, x_shift_id IN NUMBER
, x_unit_of_time_value IN VARCHAR2
, x_begin_time IN DATE
, x_end_time IN DATE
, x_start_date_active IN DATE
, x_end_date_active IN DATE
, x_availability_type_code IN VARCHAR2
, x_attribute1 IN VARCHAR2
, x_attribute2 IN VARCHAR2
, x_attribute3 IN VARCHAR2
, x_attribute4 IN VARCHAR2
, x_attribute5 IN VARCHAR2
, x_attribute6 IN VARCHAR2
, x_attribute7 IN VARCHAR2
, x_attribute8 IN VARCHAR2
, x_attribute9 IN VARCHAR2
, x_attribute10 IN VARCHAR2
, x_attribute11 IN VARCHAR2
, x_attribute12 IN VARCHAR2
, x_attribute13 IN VARCHAR2
, x_attribute14 IN VARCHAR2
, x_attribute15 IN VARCHAR2
, x_attribute_category IN VARCHAR2
, x_creation_date IN DATE
, x_created_by IN NUMBER
, x_last_update_date IN DATE
, x_last_updated_by IN NUMBER
, x_last_update_login IN NUMBER
) IS
/* -- Commented By Sarvi B'cos was not used else where and also was raising an exception.
cursor C is select ROWID from JTF_CAL_SHIFT_CONSTRUCTS
where SHIFT_CONSTRUCT_ID = X_SHIFT_CONSTRUCT_ID;
SELECT COUNT(*)
INTO p_rec
FROM jtf_cal_shifts_b
WHERE shift_id = x_shift_id;
SELECT jtf_cal_shift_constructs_s.NEXTVAL
INTO v_shift_construct_id
FROM DUAL;
SELECT COUNT(*)
INTO chk_shift
FROM jtf_cal_shift_constructs
WHERE shift_construct_id = v_shift_construct_id;
/* Add User Hook Check for INSERT by Jane Wang on 01/25/02 */
/* Comment the User Hook Check out by Jane Wang on 03/12/02 */
/*
IF jtf_usr_hks.ok_to_execute(
'JTF_CAL_SHIFT_CONSTRUCTS_PKG',
'INSERT_ROW',
'B',
'C')
THEN
JTF_CAL_SHIFT_CUHK.insert_shift_constructs_pre
(X_ERROR => X_ERROR,
X_ROWID => X_ROWID,
X_SHIFT_CONSTRUCT_ID => X_SHIFT_CONSTRUCT_ID ,
X_SHIFT_ID => X_SHIFT_ID,
X_UNIT_OF_TIME_VALUE => X_UNIT_OF_TIME_VALUE ,
X_BEGIN_TIME => X_BEGIN_TIME ,
X_END_TIME => X_END_TIME,
X_START_DATE_ACTIVE => X_START_DATE_ACTIVE ,
X_END_DATE_ACTIVE => X_END_DATE_ACTIVE ,
X_AVAILABILITY_TYPE_CODE => X_AVAILABILITY_TYPE_CODE ,
X_ATTRIBUTE1 => X_ATTRIBUTE1,
X_ATTRIBUTE2 => X_ATTRIBUTE2,
X_ATTRIBUTE3 => X_ATTRIBUTE3 ,
X_ATTRIBUTE4 => X_ATTRIBUTE4 ,
X_ATTRIBUTE5 => X_ATTRIBUTE5,
X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
X_ATTRIBUTE7 => X_ATTRIBUTE7 ,
X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
X_ATTRIBUTE10 => X_ATTRIBUTE10 ,
X_ATTRIBUTE11 => X_ATTRIBUTE11 ,
X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
X_ATTRIBUTE13 => X_ATTRIBUTE13 ,
X_ATTRIBUTE14 => X_ATTRIBUTE14 ,
X_ATTRIBUTE15 => X_ATTRIBUTE15 ,
X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY ,
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY ,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY ,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
);
END IF; -- End of User Hook Check for INSERT
INSERT INTO jtf_cal_shift_constructs
(
shift_construct_id
, shift_id
, unit_of_time_value
, begin_time
, end_time
, start_date_active
, end_date_active
, availability_type_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, object_version_number
)
VALUES (
v_shift_construct_id
, x_shift_id
, x_unit_of_time_value
, x_begin_time
, x_end_time
, x_start_date_active
, x_end_date_active
, x_availability_type_code
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, x_object_version_number
);
END insert_row;
SELECT shift_id
, unit_of_time_value
, begin_time
, end_time
, start_date_active
, end_date_active
, availability_type_code
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, shift_construct_id
FROM jtf_cal_shift_constructs
WHERE shift_construct_id = x_shift_construct_id
FOR UPDATE OF shift_construct_id NOWAIT;
PROCEDURE update_row(
x_error OUT NOCOPY VARCHAR2
, x_shift_construct_id IN NUMBER
, x_shift_id IN NUMBER
, x_unit_of_time_value IN VARCHAR2
, x_begin_time IN DATE
, x_end_time IN DATE
, x_start_date_active IN DATE
, x_end_date_active IN DATE
, x_availability_type_code IN VARCHAR2
, x_attribute1 IN VARCHAR2
, x_attribute2 IN VARCHAR2
, x_attribute3 IN VARCHAR2
, x_attribute4 IN VARCHAR2
, x_attribute5 IN VARCHAR2
, x_attribute6 IN VARCHAR2
, x_attribute7 IN VARCHAR2
, x_attribute8 IN VARCHAR2
, x_attribute9 IN VARCHAR2
, x_attribute10 IN VARCHAR2
, x_attribute11 IN VARCHAR2
, x_attribute12 IN VARCHAR2
, x_attribute13 IN VARCHAR2
, x_attribute14 IN VARCHAR2
, x_attribute15 IN VARCHAR2
, x_attribute_category IN VARCHAR2
, x_last_update_date IN DATE
, x_last_updated_by IN NUMBER
, x_last_update_login IN NUMBER
) IS
v_error CHAR := 'N';
/* Add User Hook Check for UPDATE by Jane Wang on 01/25/02 */
/* Comment the User Hook Check by Jane Wang on 03/12/02 */
/*
IF jtf_usr_hks.ok_to_execute(
'JTF_CAL_SHIFT_CONSTRUCTS_PKG',
'UPDATE_ROW',
'B',
'C')
THEN
JTF_CAL_SHIFT_CUHK.update_shift_pre
(X_ERROR => X_ERROR,
X_SHIFT_ID => X_SHIFT_ID,
X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
X_ATTRIBUTE1 => X_ATTRIBUTE1,
X_ATTRIBUTE2 => X_ATTRIBUTE2,
X_ATTRIBUTE3 => X_ATTRIBUTE3,
X_ATTRIBUTE4 => X_ATTRIBUTE4,
X_ATTRIBUTE5 => X_ATTRIBUTE5,
X_ATTRIBUTE6 => X_ATTRIBUTE6,
X_ATTRIBUTE7 => X_ATTRIBUTE7,
X_ATTRIBUTE8 => X_ATTRIBUTE8,
X_ATTRIBUTE9 => X_ATTRIBUTE9,
X_ATTRIBUTE10 => X_ATTRIBUTE10,
X_ATTRIBUTE11 => X_ATTRIBUTE11,
X_ATTRIBUTE12 => X_ATTRIBUTE12,
X_ATTRIBUTE13 => X_ATTRIBUTE13,
X_ATTRIBUTE14 => X_ATTRIBUTE14,
X_ATTRIBUTE15 => X_ATTRIBUTE15,
X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
);
END IF; -- End of User Hook Check for UPDATE
UPDATE jtf_cal_shift_constructs
SET
-- SHIFT_ID = X_SHIFT_ID,
unit_of_time_value = x_unit_of_time_value
, begin_time = x_begin_time
, end_time = x_end_time
, start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, availability_type_code = x_availability_type_code
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, shift_construct_id = x_shift_construct_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, last_update_login = fnd_global.login_id
, object_version_number = NVL(object_version_number, 0) + 1
WHERE shift_construct_id = x_shift_construct_id;
END update_row;
PROCEDURE delete_row(x_error OUT NOCOPY VARCHAR2, x_shift_construct_id IN NUMBER) IS
CURSOR c_sh_exist_in_task_assmt(p_shift_cons_id NUMBER) IS
SELECT 1
FROM jtf_task_assignments
WHERE shift_construct_id = p_shift_cons_id AND ROWNUM = 1;
DELETE FROM jtf_cal_shift_constructs
WHERE shift_construct_id = x_shift_construct_id;
END delete_row;
SELECT shift_id
, unit_of_time_value
, begin_time
, end_time
, start_date_active
, end_date_active
FROM jtf_cal_shift_constructs
WHERE shift_id = x_shift_id;
SELECT shift_id
, unit_of_time_value
, begin_time
, end_time
, shift_construct_id
FROM jtf_cal_shift_constructs
WHERE shift_id = x_shift_id;
SELECT shift_construct_id
FROM jtf_cal_shift_constructs
WHERE shift_id = l_shift_id AND begin_time <= l_day;
SELECT begin_time
, end_time
, shift_construct_id
FROM jtf_cal_shift_constructs
WHERE shift_id = l_shift_id AND TRUNC(end_time) > TO_DATE('07/01/1995', 'DD/MM/YYYY');
select shift_id, unit_of_time_value, begin_time, end_time, start_date_active, end_date_active
from jtf_cal_shift_constructs
where shift_id = X_SHIFT_ID;