[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
PROCEDURE Insert_schedule( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_type IN VARCHAR2,
p_cal_type IN VARCHAR2,
p_sequence_number IN NUMBER,
p_cal_start_dt IN DATE,
p_cal_end_dt IN DATE,
p_uoo_id IN NUMBER,
p_unit_section_occurrence_id IN NUMBER,
p_start_time IN DATE,
p_end_time IN DATE,
p_building_id IN NUMBER,
p_room_id IN NUMBER,
p_schedule_status IN VARCHAR2,
p_error_text IN VARCHAR2,
p_org_id IN NUMBER,
p_uso_start_date IN DATE,
p_uso_end_date IN DATE,
p_sunday IN VARCHAR2,
p_monday IN VARCHAR2,
p_tuesday IN VARCHAR2,
p_wednesday IN VARCHAR2,
p_thursday IN VARCHAR2,
p_friday IN VARCHAR2,
p_saturday IN VARCHAR2
) AS
/***********************************************************************************************
Created By: schodava
Date Created By: 12-06-2001
Purpose: This procedure is used to insert records in the Scheduling interface tables.
Known limitations,enhancements,remarks:
Change History
Who When What
jbegum 22-APR-2003 Enh bug#2833850
Added following parameters
p_uso_start_date,p_uso_end_date,p_sunday,p_monday,p_tuesday,p_wednesday,p_thursday,p_friday,p_saturday
smvk 10-Feb-2003 Bug # 2803385. Modified the variable buiding_code and room_code as building_id and
room_id respectively.
********************************************************************************************** */
l_api_name CONSTANT VARCHAR2(30) := 'Insert_sch';
SELECT *
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_n_usec_id;
SELECT start_dt ,end_dt
FROM igs_ca_inst
WHERE cal_type = cp_c_cal_type AND
sequence_number = cp_n_sequence_number;
SELECT 'x'
FROM igs_ad_building_all
WHERE building_id = cp_n_building_id;
SELECT 'x'
FROM igs_ad_room_all
WHERE room_id = cp_n_room_id;
SELECT 'x'
FROM igs_ad_room_all
WHERE room_id = cp_n_room_id AND
building_id = cp_n_building_id;
SAVEPOINT Insert_schedule_pub;
p_transaction_type NOT IN ('REQUEST','UPDATE','CANCEL') THEN
FND_MESSAGE.Set_Name('IGS','IGS_FI_INVALID_TXN_TYPE');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_BD_OR_RM_NULL');
IF cur_usec_rec.schedule_status IN ('PROCESSING','USER_UPDATE','USER_CANCEL') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_CONFLICT_SCHD');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PE_EDT_LT_SDT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_STDT_GE_US_STDT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_STDT_GE_TP_STDT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ST_DT_UOO_END_DT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ST_DT_TP_END_DT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_END_DT_UOO_ST_DT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_END_DT_TP_ST_DT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ENDT_LE_US_ENDT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ENDT_LE_TP_ENDT');
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
OPEN c_bld(p_building_id);
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
OPEN c_room(p_room_id);
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
OPEN c_bld_room(p_room_id,p_building_id);
(p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_ATLEAST_ONE_DAY_CHECK');
INSERT INTO igs_ps_sch_hdr_int (
transaction_id ,
originator ,
request_date ,
org_id
)
VALUES (
IGS_PS_SCH_HDR_INT_S.NEXTVAL,
'EXTERNAL',
SYSDATE,
p_org_id ) RETURNING transaction_id INTO l_trans_id;
SELECT *
FROM igs_ca_inst_all
WHERE cal_type=cp_cal_type
AND sequence_number=cp_sequence_number;
SELECT unit_cd,version_number
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id=cp_uoo_id;
--Insert into pattern interface table
INSERT INTO IGS_PS_SCH_PAT_INT
(int_pat_id ,
transaction_id ,
calendar_type ,
sequence_number ,
teaching_cal_alternate_code ,
start_date ,
end_date ,
unit_cd ,
version_number ,
enrollment_expected ,
enrollment_maximum ,
override_enrollment_maximum ,
unit_status ,
abort_flag ,
import_done_flag ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login)
VALUES(
IGS_PS_SCH_PAT_INT_S.NEXTVAL,
l_trans_id,
p_cal_type,
p_sequence_number,
l_cur_cal.alternate_code,
l_cur_cal.start_dt,
l_cur_cal.end_dt,
l_cur_pat.unit_cd,
l_cur_pat.version_number,
NULL,
NULL,
NULL,
NULL,
'N',
'N',
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) RETURNING int_pat_id INTO l_int_pat_id;
INSERT INTO igs_ps_sch_usec_int_all (
int_usec_id ,
calendar_type ,
sequence_number ,
unit_cd ,
version_number ,
unit_title ,
owner_org_unit_cd ,
unit_class ,
unit_section_start_date ,
unit_section_end_date ,
unit_section_status ,
enrollment_maximum ,
enrollment_actual ,
enrollment_expected ,
override_enrollment_max ,
location_cd ,
cal_start_dt ,
cal_end_dt ,
uoo_id ,
transaction_id ,
org_id ,
ABORT_FLAG ,
IMPORT_DONE_FLAG ,
CALL_NUMBER ,
SUBTITLE ,
ORG_UNIT_DESCRIPTION ,
TEACHING_CAL_ALTERNATE_CODE ,
INT_PAT_ID ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_USEC_INT_S.NEXTVAL,
p_cal_type,
p_sequence_number,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NVL(p_cal_start_dt,rec_cal.start_dt),
NVL(p_cal_end_dt,rec_cal.end_dt),
p_uoo_id,
l_trans_id,
p_org_id,
'N',
'N',
NULL,
NULL,
NULL,
NULL,
l_int_pat_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) RETURNING int_usec_id INTO l_int_usec_id;
INSERT INTO igs_ps_sch_int_all (
CALENDAR_TYPE ,
SEQUENCE_NUMBER ,
TRANSACTION_TYPE ,
UNIT_SECTION_OCCURRENCE_ID ,
UNIT_CD ,
VERSION_NUMBER ,
UNIT_TITLE ,
OWNER_ORG_UNIT_CD ,
UNIT_CLASS ,
MONDAY ,
TUESDAY ,
WEDNESDAY ,
THURSDAY ,
FRIDAY ,
SATURDAY ,
SUNDAY ,
UNIT_SECTION_START_DATE ,
UNIT_SECTION_END_DATE ,
START_TIME ,
END_TIME ,
ENROLLMENT_MAXIMUM ,
ENROLLMENT_ACTUAL ,
INSTRUCTOR_ID ,
SURNAME ,
BUILDING_ID ,
ROOM_ID ,
LOCATION_CD ,
UNIT_SEC_CROSS_UNIT_SEC_ID ,
UOO_ID ,
SCHEDULE_STATUS ,
ERROR_TEXT ,
TRANSACTION_ID ,
INT_OCCURS_ID ,
INT_USEC_ID ,
GIVEN_NAMES ,
MIDDLE_NAME ,
ORG_ID ,
DEDICATED_BUILDING_ID ,
DEDICATED_ROOM_ID ,
PREFERRED_BUILDING_ID ,
PREFERRED_ROOM_ID ,
PREFERRED_REGION_CODE ,
TBA_STATUS ,
USO_START_DATE ,
USO_END_DATE ,
abort_flag ,
import_done_flag ,
occurrence_identifier ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
NULL,
NULL,
p_transaction_type,
p_unit_section_occurrence_id,
NULL,
NULL,
NULL,
NULL,
NULL,
p_monday,
p_tuesday,
p_wednesday,
p_thursday,
p_friday,
p_saturday,
p_sunday,
NULL,
NULL,
p_start_time,
p_end_time,
NULL,
NULL,
NULL,
NULL,
p_building_id,
p_room_id,
NULL,
NULL,
NULL,
p_schedule_status,
p_error_text,
NULL,
IGS_PS_SCH_INT_S.NEXTVAL,
l_int_usec_id,
NULL,
NULL,
p_org_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_uso_start_date,
p_uso_end_date,
'N',
'N',
cur_usec_rec.occurrence_identifier,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) ;
ROLLBACK TO Insert_schedule_pub;
ROLLBACK TO Insert_schedule_pub;
ROLLBACK TO Insert_schedule_pub;
END insert_schedule;
PROCEDURE update_schedule( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_int_occurs_id IN NUMBER,
p_start_time IN DATE ,
p_end_time IN DATE ,
p_building_id IN NUMBER ,
p_room_id IN NUMBER ,
p_schedule_status IN VARCHAR2,
p_error_text IN VARCHAR2,
p_org_id IN NUMBER,
p_uso_start_date IN DATE,
p_uso_end_date IN DATE,
p_sunday IN VARCHAR2,
p_monday IN VARCHAR2,
p_tuesday IN VARCHAR2,
p_wednesday IN VARCHAR2,
p_thursday IN VARCHAR2,
p_friday IN VARCHAR2,
p_saturday IN VARCHAR2
) AS
/***********************************************************************************************
Created By: schodava
Date Created By: 12-06-2001
Purpose: This procedure is used to update records in the Scheduling interface tables.
Known limitations,enhancements,remarks:
Change History
Who When What
jbegum 18-APR-2003 Enh bug#2833850
Added following parameters
p_uso_start_date,p_uso_end_date,p_sunday,p_monday,p_tuesday,p_wednesday,p_thursday,p_friday,p_saturday
smvk 10-Feb-2003 Bug # 2803385. Modified the variable buiding_code and room_code as building_id and
room_id respectively.
*************************************************************************************************/
l_api_name CONSTANT VARCHAR2(30) := 'Update_sch';
SAVEPOINT Update_Schedule_pub;
SELECT psi.rowid,
psi.*,
ps.unit_section_start_date us_start_date,
ps.unit_section_end_date us_end_date
FROM IGS_PS_SCH_INT_ALL psi,
IGS_PS_SCH_USEC_INT ps
WHERE psi.int_occurs_id = cp_int_occurs_id AND
psi.int_usec_id = ps.int_usec_id;
SELECT 'x'
FROM igs_ad_building_all
WHERE building_id = cp_n_building_id;
SELECT 'x'
FROM igs_ad_room_all
WHERE room_id = cp_n_room_id;
SELECT 'x'
FROM igs_ad_room_all
WHERE room_id = cp_n_room_id AND
building_id = cp_n_building_id;
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_BD_OR_RM_NULL');
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
cur_usec_rec.tba_status = 'Y' AND
cur_usec_rec.uso_start_date IS NULL AND cur_usec_rec.uso_end_date IS NULL THEN
FND_MESSAGE.Set_Name('IGS','IGS_PE_EDT_LT_SDT');
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
cur_usec_rec.tba_status = 'Y' AND
cur_usec_rec.uso_start_date IS NULL THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_STDT_GE_US_STDT');
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
cur_usec_rec.tba_status = 'Y' AND
cur_usec_rec.uso_start_date IS NULL THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ST_DT_UOO_END_DT');
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
cur_usec_rec.tba_status = 'Y' AND
cur_usec_rec.uso_end_date IS NULL THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_END_DT_UOO_ST_DT');
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
cur_usec_rec.tba_status = 'Y' AND
cur_usec_rec.uso_end_date IS NULL THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ENDT_LE_US_ENDT');
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
OPEN c_bld(p_building_id);
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
OPEN c_room(p_room_id);
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
OPEN c_bld_room(p_room_id,p_building_id);
(cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
FND_MESSAGE.Set_Name('IGS','IGS_PS_ATLEAST_ONE_DAY_CHECK');
UPDATE igs_ps_sch_int_all SET
TRANSACTION_TYPE = cur_usec_rec.transaction_type,
UNIT_SECTION_OCCURRENCE_ID = cur_usec_rec.unit_section_occurrence_id,
MONDAY = l_monday,
TUESDAY = l_tuesday,
WEDNESDAY = l_wednesday,
THURSDAY = l_thursday,
FRIDAY = l_friday,
SATURDAY = l_saturday,
SUNDAY = l_sunday,
START_TIME = l_start_time,
END_TIME = l_end_time,
INSTRUCTOR_ID = cur_usec_rec.instructor_id,
SURNAME = cur_usec_rec.surname,
BUILDING_ID = l_building_id,
ROOM_ID = l_room_id,
SCHEDULE_STATUS = p_schedule_status,
ERROR_TEXT = p_error_text,
INT_OCCURS_ID = l_int_occurs_id,
INT_USEC_ID = cur_usec_rec.int_usec_id,
GIVEN_NAMES = cur_usec_rec.given_names,
MIDDLE_NAME = cur_usec_rec.middle_name,
DEDICATED_BUILDING_id = cur_usec_rec.dedicated_building_id,
DEDICATED_ROOM_id = cur_usec_rec.dedicated_room_id,
PREFERRED_BUILDING_id = cur_usec_rec.preferred_building_id,
PREFERRED_ROOM_id = cur_usec_rec.preferred_room_id,
TBA_STATUS = cur_usec_rec.tba_status,
USO_START_DATE = l_uso_start_date,
USO_END_DATE = l_uso_end_date,
PREFERRED_REGION_CODE = cur_usec_rec.preferred_region_code,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE INT_OCCURS_ID= l_int_occurs_id;
ROLLBACK TO Update_Schedule_pub;
ROLLBACK TO Update_Schedule_pub;
ROLLBACK TO Update_Schedule_pub;
END update_schedule;