The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER ,
x_org_id IN NUMBER,
-- anilk, bug#2784198
x_logical_delete_dt in DATE
) AS
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_PR_RU_OU_ALL
WHERE rowid = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
new_references.logical_delete_dt := x_logical_delete_dt;
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');
Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
SELECT rowid
FROM IGS_PR_RU_OU_ALL
WHERE progression_rule_cat = x_progression_rule_cat
AND pra_sequence_number = x_pra_sequence_number
AND sequence_number = x_sequence_number;
SELECT rowid
FROM IGS_PR_RU_OU_ALL
WHERE restricted_attendance_type = x_attendance_type ;
SELECT rowid
FROM IGS_PR_RU_OU_ALL
WHERE progression_outcome_type = x_progression_outcome_type ;
SELECT rowid
FROM IGS_PR_RU_OU_ALL
WHERE progression_rule_cat = x_progression_rule_cat
AND pra_sequence_number = x_sequence_number ;
SELECT rowid
FROM IGS_PR_RU_OU_ALL
WHERE prg_rule_repeat_fail_type = x_s_prg_rule_repeat_fail_type ;
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER,
x_org_id IN NUMBER,
-- anilk, bug#2784198
x_logical_delete_dt in DATE
) AS
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_progression_rule_cat,
x_pra_sequence_number,
x_sequence_number,
x_number_of_failures,
x_progression_outcome_type,
x_apply_automatically_ind,
x_prg_rule_repeat_fail_type,
x_override_show_cause_ind,
x_override_appeal_ind,
x_duration,
x_duration_type,
x_rank,
x_encmb_course_group_cd,
x_restricted_enrolment_cp,
x_restricted_attendance_type,
x_comments,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login ,
x_org_id,
-- anilk, bug#2784198
x_logical_delete_dt
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
Check_Parent_Existance;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
Check_Parent_Existance;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
Check_Child_Existance;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
IF Get_PK_For_Validation (
new_references.progression_rule_cat,
new_references.pra_sequence_number,
new_references.sequence_number) THEN
Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
CHECK_UNIQUENESS;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
Check_Child_Existance;
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;
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_PROGRESSION_RULE_CAT in VARCHAR2,
X_PRA_SEQUENCE_NUMBER in NUMBER,
X_SEQUENCE_NUMBER in NUMBER,
X_NUMBER_OF_FAILURES in NUMBER,
X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
X_APPLY_AUTOMATICALLY_IND in VARCHAR2,
X_PRG_RULE_REPEAT_FAIL_TYPE in VARCHAR2,
X_OVERRIDE_SHOW_CAUSE_IND in VARCHAR2,
X_OVERRIDE_APPEAL_IND in VARCHAR2,
X_DURATION in NUMBER,
X_DURATION_TYPE in VARCHAR2,
X_RANK in NUMBER,
X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
X_RESTRICTED_ENROLMENT_CP in NUMBER,
X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
X_COMMENTS in VARCHAR2,
X_MODE in VARCHAR2,
X_ORG_ID IN NUMBER,
-- anilk, bug#2784198
X_LOGICAL_DELETE_DT in DATE
) AS
cursor C is select ROWID from IGS_PR_RU_OU_ALL
where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
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 => 'INSERT',
x_rowid => x_rowid ,
x_progression_rule_cat => x_progression_rule_cat ,
x_pra_sequence_number => x_pra_sequence_number ,
x_sequence_number => x_sequence_number ,
x_number_of_failures => x_number_of_failures ,
x_progression_outcome_type => x_progression_outcome_type ,
x_apply_automatically_ind => nvl( x_apply_automatically_ind, 'N') ,
x_prg_rule_repeat_fail_type => x_prg_rule_repeat_fail_type ,
x_override_show_cause_ind => x_override_show_cause_ind ,
x_override_appeal_ind => x_override_appeal_ind ,
x_duration => x_duration ,
x_duration_type => x_duration_type ,
x_rank => x_rank ,
x_encmb_course_group_cd => x_encmb_course_group_cd ,
x_restricted_enrolment_cp => x_restricted_enrolment_cp ,
x_restricted_attendance_type => x_restricted_attendance_type ,
x_comments => x_comments ,
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_org_id => igs_ge_gen_003.get_org_id,
-- anilk, bug#2784198
x_logical_delete_dt => x_logical_delete_dt
) ;
insert into IGS_PR_RU_OU_ALL (
PROGRESSION_RULE_CAT,
PRA_SEQUENCE_NUMBER,
SEQUENCE_NUMBER,
NUMBER_OF_FAILURES,
PROGRESSION_OUTCOME_TYPE,
APPLY_AUTOMATICALLY_IND,
PRG_RULE_REPEAT_FAIL_TYPE,
OVERRIDE_SHOW_CAUSE_IND,
OVERRIDE_APPEAL_IND,
DURATION,
DURATION_TYPE,
RANK,
ENCMB_COURSE_GROUP_CD,
RESTRICTED_ENROLMENT_CP,
RESTRICTED_ATTENDANCE_TYPE,
COMMENTS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID,
-- anilk, bug#2784198
LOGICAL_DELETE_DT
) values (
NEW_REFERENCES.PROGRESSION_RULE_CAT,
NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
NEW_REFERENCES.SEQUENCE_NUMBER,
NEW_REFERENCES.NUMBER_OF_FAILURES,
NEW_REFERENCES.PROGRESSION_OUTCOME_TYPE,
NEW_REFERENCES.APPLY_AUTOMATICALLY_IND,
NEW_REFERENCES.PRG_RULE_REPEAT_FAIL_TYPE,
NEW_REFERENCES.OVERRIDE_SHOW_CAUSE_IND,
NEW_REFERENCES.OVERRIDE_APPEAL_IND,
NEW_REFERENCES.DURATION,
NEW_REFERENCES.DURATION_TYPE,
NEW_REFERENCES.RANK,
NEW_REFERENCES.ENCMB_COURSE_GROUP_CD,
NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
NEW_REFERENCES.COMMENTS,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
NEW_REFERENCES.ORG_ID,
-- anilk, bug#2784198
NEW_REFERENCES.LOGICAL_DELETE_DT
);
p_action => 'INSERT' ,
x_rowid => X_ROWID );
end INSERT_ROW;
X_LOGICAL_DELETE_DT in DATE
) AS
cursor c1 is select
NUMBER_OF_FAILURES,
PROGRESSION_OUTCOME_TYPE,
APPLY_AUTOMATICALLY_IND,
PRG_RULE_REPEAT_FAIL_TYPE,
OVERRIDE_SHOW_CAUSE_IND,
OVERRIDE_APPEAL_IND,
DURATION,
DURATION_TYPE,
RANK,
ENCMB_COURSE_GROUP_CD,
RESTRICTED_ENROLMENT_CP,
RESTRICTED_ATTENDANCE_TYPE,
COMMENTS,
-- anilk, bug#2784198
LOGICAL_DELETE_DT
from IGS_PR_RU_OU_ALL
where ROWID = X_ROWID for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
OR ((tlinfo.LOGICAL_DELETE_DT is null)
AND (X_LOGICAL_DELETE_DT is null)))
) then
null;
procedure UPDATE_ROW (
X_ROWID in VARCHAR2,
X_PROGRESSION_RULE_CAT in VARCHAR2,
X_PRA_SEQUENCE_NUMBER in NUMBER,
X_SEQUENCE_NUMBER in NUMBER,
X_NUMBER_OF_FAILURES in NUMBER,
X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
X_APPLY_AUTOMATICALLY_IND in VARCHAR2,
X_PRG_RULE_REPEAT_FAIL_TYPE in VARCHAR2,
X_OVERRIDE_SHOW_CAUSE_IND in VARCHAR2,
X_OVERRIDE_APPEAL_IND in VARCHAR2,
X_DURATION in NUMBER,
X_DURATION_TYPE in VARCHAR2,
X_RANK in NUMBER,
X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
X_RESTRICTED_ENROLMENT_CP in NUMBER,
X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
X_COMMENTS in VARCHAR2,
X_MODE in VARCHAR2 ,
-- anilk, bug#2784198
X_LOGICAL_DELETE_DT in DATE
) AS
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
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_progression_rule_cat => x_progression_rule_cat ,
x_pra_sequence_number => x_pra_sequence_number ,
x_sequence_number => x_sequence_number ,
x_number_of_failures => x_number_of_failures ,
x_progression_outcome_type => x_progression_outcome_type ,
x_apply_automatically_ind => x_apply_automatically_ind ,
x_prg_rule_repeat_fail_type => x_prg_rule_repeat_fail_type ,
x_override_show_cause_ind => x_override_show_cause_ind ,
x_override_appeal_ind => x_override_appeal_ind ,
x_duration => x_duration ,
x_duration_type => x_duration_type ,
x_rank => x_rank ,
x_encmb_course_group_cd => x_encmb_course_group_cd ,
x_restricted_enrolment_cp => x_restricted_enrolment_cp ,
x_restricted_attendance_type => x_restricted_attendance_type ,
x_comments => x_comments ,
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,
-- anilk, bug#2784198
x_logical_delete_dt => x_logical_delete_dt
) ;
update IGS_PR_RU_OU_ALL set
NUMBER_OF_FAILURES = NEW_REFERENCES.NUMBER_OF_FAILURES,
PROGRESSION_OUTCOME_TYPE = NEW_REFERENCES.PROGRESSION_OUTCOME_TYPE,
APPLY_AUTOMATICALLY_IND = NEW_REFERENCES.APPLY_AUTOMATICALLY_IND,
PRG_RULE_REPEAT_FAIL_TYPE = NEW_REFERENCES.PRG_RULE_REPEAT_FAIL_TYPE,
OVERRIDE_SHOW_CAUSE_IND = NEW_REFERENCES.OVERRIDE_SHOW_CAUSE_IND,
OVERRIDE_APPEAL_IND = NEW_REFERENCES.OVERRIDE_APPEAL_IND,
DURATION = NEW_REFERENCES.DURATION,
DURATION_TYPE = NEW_REFERENCES.DURATION_TYPE,
RANK = NEW_REFERENCES.RANK,
ENCMB_COURSE_GROUP_CD = NEW_REFERENCES.ENCMB_COURSE_GROUP_CD,
RESTRICTED_ENROLMENT_CP = NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
RESTRICTED_ATTENDANCE_TYPE = NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
COMMENTS = NEW_REFERENCES.COMMENTS,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
-- anilk, bug#2784198
LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT
where ROWID = X_ROWID
;
p_action => 'UPDATE' ,
x_rowid => X_ROWID );
end UPDATE_ROW;
X_LOGICAL_DELETE_DT in DATE
) AS
cursor c1 is select rowid from IGS_PR_RU_OU_ALL
where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
;
INSERT_ROW (
X_ROWID,
X_PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER,
X_SEQUENCE_NUMBER,
X_NUMBER_OF_FAILURES,
X_PROGRESSION_OUTCOME_TYPE,
X_APPLY_AUTOMATICALLY_IND,
X_PRG_RULE_REPEAT_FAIL_TYPE,
X_OVERRIDE_SHOW_CAUSE_IND,
X_OVERRIDE_APPEAL_IND,
X_DURATION,
X_DURATION_TYPE,
X_RANK,
X_ENCMB_COURSE_GROUP_CD,
X_RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE,
X_COMMENTS,
X_MODE,
x_org_id,
-- anilk, bug#2784198
X_LOGICAL_DELETE_DT
);
UPDATE_ROW (
X_ROWID ,
X_PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER,
X_SEQUENCE_NUMBER,
X_NUMBER_OF_FAILURES,
X_PROGRESSION_OUTCOME_TYPE,
X_APPLY_AUTOMATICALLY_IND,
X_PRG_RULE_REPEAT_FAIL_TYPE,
X_OVERRIDE_SHOW_CAUSE_IND,
X_OVERRIDE_APPEAL_IND,
X_DURATION,
X_DURATION_TYPE,
X_RANK,
X_ENCMB_COURSE_GROUP_CD,
X_RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE,
X_COMMENTS,
X_MODE,
-- anilk, bug#2784198
X_LOGICAL_DELETE_DT
);
SELECT rowid
FROM IGS_PR_RU_OU_ALL
WHERE progression_rule_cat = x_progression_rule_cat
AND pra_sequence_number = x_pra_sequence_number
AND prg_rule_repeat_fail_type = X_prg_rule_repeat_fail_type
AND number_of_failures = X_number_of_failures
AND rank = X_rank
AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
FOR UPDATE NOWAIT;