[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_MSG_PUB.DELETE_MSG (l_msg_count);
FND_MSG_PUB.DELETE_MSG (l_msg_count);
FND_MSG_PUB.DELETE_MSG (l_msg_count);
bdeviset 13-SEP-2004 Bug no 3885804.Added parameter logical_delete_date in the call
igs_en_stdnt_ps_intm_pkg.get_pk_for_validation.
***********************************************************************************************/
l_valid_db_constr BOOLEAN := TRUE;
l_logical_delete_date igs_en_stdnt_ps_intm.logical_delete_date%TYPE;
l_logical_delete_date := to_date('31-12-4712','DD-MM-YYYY');
IF igs_en_stdnt_ps_intm_pkg.get_pk_for_validation ( p_person_id, p_intermiss_rec.program_cd, p_intermiss_rec.start_dt,l_logical_delete_date) THEN
FND_MESSAGE.SET_NAME('IGS','IGS_EN_STU_INTM_EXISTS');
l_insert_flag BOOLEAN := TRUE;
l_last_update_date igs_en_stdnt_ps_intm.last_update_date%TYPE;
l_last_updated_by igs_en_stdnt_ps_intm.last_updated_by%TYPE;
l_last_update_login igs_en_stdnt_ps_intm.last_update_login%TYPE;
l_logical_delete_date igs_en_stdnt_ps_intm.logical_delete_date%TYPE;
l_insert_flag := FALSE;
IF l_insert_flag = TRUE THEN
-- Derive the values for further processing.
-- Get the person id for the passed person number.
l_person_id := Igs_Ge_Gen_003.get_person_id (p_intermiss_rec.person_number);
l_insert_flag := FALSE;
IF l_insert_flag = TRUE THEN
-- Get the approver person id for the approver.
IF p_intermiss_rec.approver_person_number IS NOT NULL THEN
l_approver_person_id := Igs_Ge_Gen_003.get_person_id (p_intermiss_rec.approver_person_number);
l_insert_flag := FALSE;
IF l_insert_flag = TRUE THEN
l_ret_val := validate_db_cons ( l_person_id, p_intermiss_rec);
l_insert_flag := FALSE;
l_insert_flag := FALSE;
IF l_insert_flag = TRUE THEN
IF NOT validate_stu_intm (l_person_id, p_intermiss_rec, l_approver_person_id) THEN
l_insert_flag := FALSE;
IF l_insert_flag = TRUE THEN
IF NOT igs_en_gen_legacy.check_approv_reqd (p_intermiss_rec.intermission_type) THEN
l_approver_person_id := NULL;
IF l_insert_flag = TRUE THEN
IF igs_en_gen_legacy.check_study_antr_instu (p_intermiss_rec.intermission_type) THEN
l_institution_name := p_intermiss_rec.institution_name;
IF l_insert_flag = TRUE THEN
-- Populating who columns
l_creation_date := SYSDATE;
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
IF l_last_updated_by IS NULL THEN
l_last_updated_by := -1;
IF l_last_update_login IS NULL THEN
l_last_update_login := -1;
l_logical_delete_date := to_date('31-12-4712','DD-MM-YYYY');
INSERT INTO igs_en_stdnt_ps_intm (
person_id,
course_cd,
start_dt,
logical_delete_date,
end_dt,
voluntary_ind,
comments,
intermission_type,
approved,
institution_name,
max_credit_pts,
max_terms,
anticipated_credit_points,
approver_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
cond_return_flag
)VALUES(
l_person_id,
p_intermiss_rec.program_cd,
p_intermiss_rec.start_dt,
l_logical_delete_date,
p_intermiss_rec.end_dt,
NVL(p_intermiss_rec.voluntary_ind,'N'),
p_intermiss_rec.comments,
p_intermiss_rec.intermission_type,
NVL(p_intermiss_rec.approved,'N'),
l_institution_name,
l_max_credit_pts,
l_max_terms,
l_anticipated_credit_points,
l_approver_person_id,
p_intermiss_rec.attribute_category,
p_intermiss_rec.attribute1,
p_intermiss_rec.attribute2,
p_intermiss_rec.attribute3,
p_intermiss_rec.attribute4,
p_intermiss_rec.attribute5,
p_intermiss_rec.attribute6,
p_intermiss_rec.attribute7,
p_intermiss_rec.attribute8,
p_intermiss_rec.attribute9,
p_intermiss_rec.attribute10,
p_intermiss_rec.attribute11,
p_intermiss_rec.attribute12,
p_intermiss_rec.attribute13,
p_intermiss_rec.attribute14,
p_intermiss_rec.attribute15,
p_intermiss_rec.attribute16,
p_intermiss_rec.attribute17,
p_intermiss_rec.attribute18,
p_intermiss_rec.attribute19,
p_intermiss_rec.attribute20,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_cond_return_flag
);
-- If needed update it with INTERMIT status.
IF igs_en_gen_legacy.check_sca_status_upd ( l_person_id, p_intermiss_rec.program_cd, 'SPI', p_course_attempt_status ) THEN
UPDATE igs_en_stdnt_ps_att_all SET course_attempt_status = 'INTERMIT'
WHERE person_id = l_person_id
AND course_cd = p_intermiss_rec.program_cd;
IF (FND_API.TO_BOOLEAN(p_commit) AND l_insert_flag ) THEN
COMMIT WORK;