[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL,
x_evaluation_sequence IN NUMBER DEFAULT NULL,
x_rating_scale_id IN NUMBER DEFAULT NULL,
x_closed_ind IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_AD_APPL_EVAL
WHERE rowid = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Close cur_old_ref_values;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date;
new_references.last_updated_by := x_last_updated_by;
new_references.last_update_login := x_last_update_login;
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
SELECT rowid
FROM igs_ad_appl_eval
WHERE appl_eval_id = x_appl_eval_id AND
NVL(closed_ind,'N') = DECODE(closed_ind,NULL,'N',NVL(x_closed_ind,closed_ind))
FOR UPDATE NOWAIT;
SELECT rowid
FROM igs_ad_appl_eval
WHERE rating_values_id = x_rating_values_id ;
SELECT rowid
FROM igs_ad_appl_eval
WHERE rating_type_id = x_code_id ;
SELECT rowid
FROM igs_ad_appl_eval
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd
AND sequence_number = x_sequence_number ;
SELECT rowid
FROM igs_ad_appl_eval
WHERE evaluator_id = x_person_id ;
SELECT rowid
FROM igs_ad_appl_eval
WHERE rating_scale_id = x_rating_scale_id ;
Purpose : Insert and Update is not allowed if system outcome status is in
('VOIDED','WITHDRAWN','NO-QUOTA','OFFER','OFFER-FUTURE-TERM')
or the system outcome status is REJECTED and the
req_for_reconsideration_ind is set to 'N'
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_get_outcome_status ( p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
SELECT a.adm_outcome_status, b.req_for_reconsideration_ind
FROM igs_ad_ps_appl_inst_all a, igs_ad_ps_appl b
WHERE a.person_id = p_person_id
AND a.admission_appl_number = p_admission_appl_number
AND a.nominated_course_cd = p_nominated_course_cd
AND a.sequence_number = p_sequence_number
AND a.person_id = b.person_id
AND a.admission_appl_number = b.admission_appl_number
AND a.nominated_course_cd = b.nominated_course_cd;
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL,
x_evaluation_sequence IN NUMBER DEFAULT NULL,
x_rating_scale_id IN NUMBER DEFAULT NULL,
x_closed_ind IN VARCHAR2 DEFAULT NULL
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_appl_eval_id,
x_person_id,
x_admission_appl_number,
x_nominated_course_cd,
x_sequence_number,
x_evaluator_id,
x_assign_type,
x_assign_date,
x_evaluation_date,
x_rating_type_id,
x_rating_values_id,
x_rating_notes,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_evaluation_sequence,
x_rating_scale_id,
x_closed_ind
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
Null;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
Null;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
Null;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_PK_For_Validation (
new_references.appl_eval_id) THEN
Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
Null;
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
Null;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
Null;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to After Delete.
Null;
select evaluation_sequence
from igs_ad_appl_eval
where person_id = x_person_id
and admission_appl_number = x_admission_appl_number
and nominated_course_cd = x_nominated_course_cd
and sequence_number = x_sequence_number
and evaluation_sequence > x_eval_seq
order by evaluation_sequence;
Select
distinct EVALUATOR_ID
From IGS_AD_APPL_EVAL
Where EVALUATION_SEQUENCE = cp_eval_seq
and person_id = x_person_id
and admission_appl_number = x_admission_appl_number
and nominated_course_cd = x_nominated_course_cd
and sequence_number = x_sequence_number;
SELECT
a.appl_rev_profile_id,
a.appl_revprof_revgr_id,
r.SEQUENTIAL_CONCURRENT_IND
FROM igs_ad_appl_arp a, igs_ad_apl_rev_prf_all r
WHERE a.person_id = x_person_id
AND a.admission_appl_number = x_admission_appl_number
And a.nominated_course_cd = x_nominated_course_cd
And a.sequence_number = x_sequence_number
AND a.appl_rev_profile_id = r.appl_rev_profile_id;
SELECT 'X' --bug 3709285 arvsrini
FROM IGS_AD_APPL_EVAL
WHERE EVALUATION_SEQUENCE = x_eval_seq
AND rating_type_ID is NOT NULL
AND rating_scale_id IS NOT NULL
AND EVALUATION_DATE IS NULL
AND rating_values_id IS NULL
AND PERSON_ID = x_person_id
AND ADMISSION_APPL_NUMBER = x_admission_appl_number
AND NOMINATED_COURSE_CD = x_nominated_course_cd
AND SEQUENCE_NUMBER = x_sequence_number;
/* SELECT count(rowid)
FROM IGS_AD_APPL_EVAL
WHERE EVALUATION_SEQUENCE = x_eval_seq
AND rating_type_ID is NOT NULL
AND rating_scale_id IS NOT NULL
AND EVALUATION_DATE IS NOT NULL
AND rating_values_id IS NOT NULL;
/* Added this function call to take care of evaluation sequence gaps caused by delete on this table */
l_person_id := find_next_eval(
x_person_id,
x_admission_appl_number,
x_NOMINATED_COURSE_CD,
x_SEQUENCE_NUMBER,
x_eval_seq );
/* Select
distinct person_id
Into l_person_id
From IGS_AD_APPL_EVAL
Where EVALUATION_SEQUENCE = x_eval_seq + 1; */
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_appl_eval_id IN OUT NOCOPY NUMBER,
x_person_id IN NUMBER,
x_admission_appl_number IN NUMBER,
x_nominated_course_cd IN VARCHAR2,
x_sequence_number IN NUMBER,
x_evaluator_id IN NUMBER,
x_assign_type IN VARCHAR2,
x_assign_date IN DATE,
x_evaluation_date IN DATE,
x_rating_type_id IN NUMBER,
x_rating_values_id IN NUMBER,
x_rating_notes IN VARCHAR2,
x_mode IN VARCHAR2,
x_evaluation_sequence IN NUMBER DEFAULT NULL,
x_rating_scale_id IN NUMBER DEFAULT NULL,
x_closed_ind IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
ravishar 5/30/2005 Security related changes
(reverse chronological order - newest change first)
***************************************************************/
cursor C is select ROWID from IGS_AD_APPL_EVAL
where APPL_EVAL_ID= X_APPL_EVAL_ID
;
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_PROGRAM_UPDATE_DATE DATE;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
X_PROGRAM_UPDATE_DATE := NULL;
X_PROGRAM_UPDATE_DATE := SYSDATE;
p_action=>'INSERT',
x_rowid=>X_ROWID,
x_appl_eval_id=>X_APPL_EVAL_ID,
x_person_id=>X_PERSON_ID,
x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_evaluator_id=>X_EVALUATOR_ID,
x_assign_type=>X_ASSIGN_TYPE,
x_assign_date=>X_ASSIGN_DATE,
x_evaluation_date=>X_EVALUATION_DATE,
x_rating_type_id=>X_RATING_TYPE_ID,
x_rating_values_id=>X_RATING_VALUES_ID,
x_rating_notes=>X_RATING_NOTES,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_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,
x_evaluation_sequence=>X_EVALUATION_SEQUENCE,
x_rating_scale_id=>X_RATING_SCALE_ID,
x_closed_ind => X_CLOSED_IND);
INSERT INTO igs_ad_appl_eval (
appl_eval_id
,person_id
,admission_appl_number
,nominated_course_cd
,sequence_number
,evaluator_id
,assign_type
,assign_date
,evaluation_date
,rating_type_id
,rating_values_id
,rating_notes
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_id
,program_application_id
,program_update_date
,evaluation_sequence
,rating_scale_id
,closed_ind
) VALUES (
igs_ad_appl_eval_s.NEXTVAL
,new_references.person_id
,new_references.admission_appl_number
,new_references.nominated_course_cd
,new_references.sequence_number
,new_references.evaluator_id
,new_references.assign_type
,new_references.assign_date
,new_references.evaluation_date
,new_references.rating_type_id
,new_references.rating_values_id
,new_references.rating_notes
,x_last_update_date
,x_last_updated_by
,x_last_update_date
,x_last_updated_by
,x_last_update_login
,x_request_id
,x_program_id
,x_program_application_id
,x_program_update_date
,x_evaluation_sequence
,x_rating_scale_id
,x_closed_ind
)RETURNING appl_eval_id INTO x_appl_eval_id;
p_action => 'INSERT' ,
x_rowid => X_ROWID );
END insert_row;
SELECT
person_id,
admission_appl_number,
nominated_course_cd,
sequence_number,
evaluator_id,
assign_type,
assign_date,
evaluation_date,
rating_type_id,
rating_values_id,
rating_notes,
evaluation_sequence,
rating_scale_id,
closed_ind
FROM igs_ad_appl_eval
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_rowid IN VARCHAR2,
x_appl_eval_id IN NUMBER,
x_person_id IN NUMBER,
x_admission_appl_number IN NUMBER,
x_nominated_course_cd IN VARCHAR2,
x_sequence_number IN NUMBER,
x_evaluator_id IN NUMBER,
x_assign_type IN VARCHAR2,
x_assign_date IN DATE,
x_evaluation_date IN DATE,
x_rating_type_id IN NUMBER,
x_rating_values_id IN NUMBER,
x_rating_notes IN VARCHAR2,
x_mode IN VARCHAR2,
x_evaluation_sequence IN NUMBER DEFAULT NULL,
x_rating_scale_id IN NUMBER DEFAULT NULL,
x_closed_ind IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
ravishar 05/27/05 Security related changes
(reverse chronological order - newest change first)
***************************************************************/
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_PROGRAM_UPDATE_DATE DATE;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
p_action=>'UPDATE',
x_rowid=>X_ROWID,
x_appl_eval_id=>X_APPL_EVAL_ID,
x_person_id=>X_PERSON_ID,
x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_evaluator_id=>X_EVALUATOR_ID,
x_assign_type=>X_ASSIGN_TYPE,
x_assign_date=>X_ASSIGN_DATE,
x_evaluation_date=>X_EVALUATION_DATE,
x_rating_type_id=>X_RATING_TYPE_ID,
x_rating_values_id=>X_RATING_VALUES_ID,
x_rating_notes=>X_RATING_NOTES,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_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,
x_evaluation_sequence=>X_EVALUATION_SEQUENCE,
x_rating_scale_id=>X_RATING_SCALE_ID,
x_closed_ind => X_CLOSED_IND );
X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
X_PROGRAM_UPDATE_DATE := SYSDATE;
UPDATE igs_ad_appl_eval SET
person_id = new_references.person_id,
admission_appl_number = new_references.admission_appl_number,
nominated_course_cd = new_references.nominated_course_cd,
sequence_number = new_references.sequence_number,
evaluator_id = new_references.evaluator_id,
assign_type = new_references.assign_type,
assign_date = new_references.assign_date,
evaluation_date = new_references.evaluation_date,
rating_type_id = new_references.rating_type_id,
rating_values_id = new_references.rating_values_id,
rating_notes = new_references.rating_notes,
evaluation_sequence = new_references.evaluation_sequence,
rating_scale_id = new_references.rating_scale_id,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_id = x_program_id,
program_application_id = program_application_id,
program_update_date = x_program_update_date,
closed_ind = x_closed_ind
WHERE rowid = x_rowid;
p_action => 'UPDATE' ,
x_rowid => X_ROWID
);
END update_row;
SELECT rowid FROM igs_ad_appl_eval
WHERE appl_eval_id= x_appl_eval_id;
insert_row (
x_rowid,
x_appl_eval_id,
x_person_id,
x_admission_appl_number,
x_nominated_course_cd,
x_sequence_number,
x_evaluator_id,
x_assign_type,
x_assign_date,
x_evaluation_date,
x_rating_type_id,
x_rating_values_id,
x_rating_notes,
x_mode,
x_evaluation_sequence,
x_rating_scale_id,
x_closed_ind );
update_row (
x_rowid,
x_appl_eval_id,
x_person_id,
x_admission_appl_number,
x_nominated_course_cd,
x_sequence_number,
x_evaluator_id,
x_assign_type,
x_assign_date,
x_evaluation_date,
x_rating_type_id,
x_rating_values_id,
x_rating_notes,
x_mode,
x_evaluation_sequence,
x_rating_scale_id,
x_closed_ind );
select evaluation_sequence
from igs_ad_appl_eval
where person_id = x_person_id
and admission_appl_number = x_admission_appl_number
and nominated_course_cd = x_nominated_course_cd
and sequence_number = x_sequence_number
and evaluation_sequence < x_eval_seq
order by evaluation_sequence desc;
procedure Notification_On_Delete(
x_person_id IN NUMBER,
x_admission_appl_number IN NUMBER,
x_NOMINATED_COURSE_CD IN VARCHAR2,
x_SEQUENCE_NUMBER IN NUMBER,
x_eval_seq IN NUMBER
) AS
/* This procedure is used to sent notification to the next personid in the sequence provided all records in the current sequence is del
eted and the evaluation has been completed for the prevous sequence */
/* Cursor to find out if there are still some records for the evaluation sequence that is getting deleted */
cursor c_seq_exists IS
SELECT evaluation_sequence
FROM IGS_AD_APPL_EVAL
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd
AND sequence_number = x_sequence_number
and evaluation_sequence = x_eval_seq;
SELECT evaluation_sequence
FROM IGS_AD_APPL_EVAL
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd
AND sequence_number = x_sequence_number
and evaluation_sequence = cp_prev_seq_number
and evaluation_date IS NULL
AND rating_values_id IS NULL;
PROCEDURE delete_row (
x_rowid IN VARCHAR2,
x_mode IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
ravishar 5/30/2005 Security related changes
(reverse chronological order - newest change first)
***************************************************************/
cursor c_del_appl_eval IS
SELECT person_id,
admission_appl_number,
nominated_course_cd,
sequence_number,
evaluator_id,
evaluation_sequence
FROM igs_ad_appl_eval
WHERE rowid = x_rowid;
p_action => 'DELETE',
x_rowid => X_ROWID
);
DELETE FROM igs_ad_appl_eval
WHERE rowid = x_rowid;
p_action => 'DELETE',
x_rowid => X_ROWID
);
Notification_On_Delete(
l_del_appl_eval.person_id,
l_del_appl_eval.admission_appl_number,
l_del_appl_eval.NOMINATED_COURSE_CD,
l_del_appl_eval.SEQUENCE_NUMBER,
l_del_appl_eval.evaluation_sequence
);
END delete_row;