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_grading_schema_dt_alias IN VARCHAR2 DEFAULT NULL,
x_begin_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
x_clean_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
x_planning_open_dt_alias IN VARCHAR2 DEFAULT NULL,
x_schedule_open_dt_alias IN VARCHAR2 ,
x_audit_status_dt_alias IN VARCHAR2 DEFAULT NULL
) AS
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_EN_CAL_CONF
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');
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');
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');
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_EN_CAL_CONF
WHERE s_control_num = x_s_control_num
FOR UPDATE NOWAIT;
SELECT rowid
FROM IGS_EN_CAL_CONF
WHERE commence_cutoff_dt_alias = x_dt_alias
OR commencement_dt_alias = x_dt_alias
OR effect_enr_strt_dt_alias = x_dt_alias
OR enr_cleanup_dt_alias = x_dt_alias
OR enr_form_due_dt_alias = x_dt_alias
OR enr_pckg_prod_dt_alias = x_dt_alias
OR enrolled_rule_cutoff_dt_alias = x_dt_alias
OR invalid_rule_cutoff_dt_alias = x_dt_alias
OR lapse_dt_alias = x_dt_alias
OR load_effect_dt_alias = x_dt_alias
OR record_cutoff_dt_alias = x_dt_alias
OR record_open_dt_alias = x_dt_alias
OR sub_unit_dt_alias = x_dt_alias
OR variation_cutoff_dt_alias = x_dt_alias
OR begin_trans_dt_alias = x_dt_alias
OR clean_trans_dt_alias = x_dt_alias
OR planning_open_dt_alias = x_dt_alias
OR schedule_open_dt_alias = x_dt_alias
OR audit_status_dt_alias = x_dt_alias;
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_grading_schema_dt_alias IN VARCHAR2 DEFAULT NULL,
x_begin_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
x_clean_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
x_planning_open_dt_alias IN VARCHAR2 DEFAULT NULL,
x_schedule_open_dt_alias IN VARCHAR2 DEFAULT NULL ,
x_audit_status_dt_alias IN VARCHAR2 DEFAULT NULL
) AS
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_s_control_num,
x_commence_cutoff_dt_alias,
x_commencement_dt_alias,
x_effect_enr_strt_dt_alias,
x_record_open_dt_alias,
x_record_cutoff_dt_alias,
x_sub_unit_dt_alias,
x_variation_cutoff_dt_alias,
x_enr_form_due_dt_alias,
x_enr_pckg_prod_dt_alias,
x_load_effect_dt_alias,
x_enr_rule_cutoff_dt_alias,
x_invalid_rule_cutoff_dt_alias,
x_enr_cleanup_dt_alias,
x_lapse_dt_alias,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_grading_schema_dt_alias,
x_begin_trans_dt_alias,
x_clean_trans_dt_alias,
x_planning_open_dt_alias ,
x_schedule_open_dt_alias ,
x_audit_status_dt_alias
);
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
if Get_PK_For_Validation (
new_references.s_control_num
) 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;
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_S_CONTROL_NUM in out NOCOPY NUMBER,
X_COMMENCE_CUTOFF_DT_ALIAS in VARCHAR2,
X_COMMENCEMENT_DT_ALIAS in VARCHAR2,
X_EFFECT_ENR_STRT_DT_ALIAS in VARCHAR2,
X_RECORD_OPEN_DT_ALIAS in VARCHAR2,
X_RECORD_CUTOFF_DT_ALIAS in VARCHAR2,
X_SUB_UNIT_DT_ALIAS in VARCHAR2,
X_VARIATION_CUTOFF_DT_ALIAS in VARCHAR2,
X_ENR_FORM_DUE_DT_ALIAS in VARCHAR2,
X_ENR_PCKG_PROD_DT_ALIAS in VARCHAR2,
X_LOAD_EFFECT_DT_ALIAS in VARCHAR2,
X_ENR_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
X_INVALID_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
X_LAPSE_DT_ALIAS in VARCHAR2,
X_ENR_CLEANUP_DT_ALIAS in VARCHAR2,
X_MODE in VARCHAR2 default 'R',
x_grading_schema_dt_alias IN VARCHAR2,
x_begin_trans_dt_alias IN VARCHAR2,
x_clean_trans_dt_alias IN VARCHAR2,
x_planning_open_dt_alias IN VARCHAR2 ,
x_schedule_open_dt_alias IN VARCHAR2 ,
x_audit_status_dt_alias IN VARCHAR2
) AS
cursor C is select ROWID from IGS_EN_CAL_CONF
where S_CONTROL_NUM = NEW_REFERENCES.S_CONTROL_NUM;
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_s_control_num => NVL(x_s_control_num,1) ,
x_commence_cutoff_dt_alias => x_commence_cutoff_dt_alias ,
x_commencement_dt_alias => x_commencement_dt_alias ,
x_effect_enr_strt_dt_alias => x_effect_enr_strt_dt_alias ,
x_record_open_dt_alias => x_record_open_dt_alias ,
x_record_cutoff_dt_alias => x_record_cutoff_dt_alias ,
x_sub_unit_dt_alias => x_sub_unit_dt_alias ,
x_variation_cutoff_dt_alias => x_variation_cutoff_dt_alias ,
x_enr_form_due_dt_alias => x_enr_form_due_dt_alias ,
x_enr_pckg_prod_dt_alias => x_enr_pckg_prod_dt_alias ,
x_load_effect_dt_alias => x_load_effect_dt_alias ,
x_enr_rule_cutoff_dt_alias => x_enr_rule_cutoff_dt_alias ,
x_invalid_rule_cutoff_dt_alias => x_invalid_rule_cutoff_dt_alias ,
x_enr_cleanup_dt_alias => x_enr_cleanup_dt_alias ,
x_lapse_dt_alias => x_lapse_dt_alias ,
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_grading_schema_dt_alias => x_grading_schema_dt_alias,
x_begin_trans_dt_alias => x_begin_trans_dt_alias,
x_clean_trans_dt_alias => x_clean_trans_dt_alias,
x_planning_open_dt_alias =>x_planning_open_dt_alias,
x_schedule_open_dt_alias => x_schedule_open_dt_alias,
x_audit_status_dt_alias => x_audit_status_dt_alias
);
insert into IGS_EN_CAL_CONF (
S_CONTROL_NUM,
COMMENCE_CUTOFF_DT_ALIAS,
COMMENCEMENT_DT_ALIAS,
EFFECT_ENR_STRT_DT_ALIAS,
RECORD_OPEN_DT_ALIAS,
RECORD_CUTOFF_DT_ALIAS,
SUB_UNIT_DT_ALIAS,
VARIATION_CUTOFF_DT_ALIAS,
ENR_FORM_DUE_DT_ALIAS,
ENR_PCKG_PROD_DT_ALIAS,
LOAD_EFFECT_DT_ALIAS,
ENROLLED_RULE_CUTOFF_DT_ALIAS,
INVALID_RULE_CUTOFF_DT_ALIAS,
LAPSE_DT_ALIAS,
ENR_CLEANUP_DT_ALIAS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
grading_schema_dt_alias,
begin_trans_dt_alias,
clean_trans_dt_alias,
planning_open_dt_alias,
schedule_open_dt_alias,
audit_status_dt_alias
) values (
NEW_REFERENCES.S_CONTROL_NUM,
NEW_REFERENCES.COMMENCE_CUTOFF_DT_ALIAS,
NEW_REFERENCES.COMMENCEMENT_DT_ALIAS,
NEW_REFERENCES.EFFECT_ENR_STRT_DT_ALIAS,
NEW_REFERENCES.RECORD_OPEN_DT_ALIAS,
NEW_REFERENCES.RECORD_CUTOFF_DT_ALIAS,
NEW_REFERENCES.SUB_UNIT_DT_ALIAS,
NEW_REFERENCES.VARIATION_CUTOFF_DT_ALIAS,
NEW_REFERENCES.ENR_FORM_DUE_DT_ALIAS,
NEW_REFERENCES.ENR_PCKG_PROD_DT_ALIAS,
NEW_REFERENCES.LOAD_EFFECT_DT_ALIAS,
NEW_REFERENCES.ENROLLED_RULE_CUTOFF_DT_ALIAS,
NEW_REFERENCES.INVALID_RULE_CUTOFF_DT_ALIAS,
NEW_REFERENCES.LAPSE_DT_ALIAS,
NEW_REFERENCES.ENR_CLEANUP_DT_ALIAS,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
x_grading_schema_dt_alias,
x_begin_trans_dt_alias,
x_clean_trans_dt_alias,
x_planning_open_dt_alias,
x_schedule_open_dt_alias,
x_audit_status_dt_alias
);
p_action => 'INSERT',
x_rowid => X_ROWID
);
end INSERT_ROW;
cursor c1 is select
COMMENCE_CUTOFF_DT_ALIAS,
COMMENCEMENT_DT_ALIAS,
EFFECT_ENR_STRT_DT_ALIAS,
RECORD_OPEN_DT_ALIAS,
RECORD_CUTOFF_DT_ALIAS,
SUB_UNIT_DT_ALIAS,
VARIATION_CUTOFF_DT_ALIAS,
ENR_FORM_DUE_DT_ALIAS,
ENR_PCKG_PROD_DT_ALIAS,
LOAD_EFFECT_DT_ALIAS,
ENROLLED_RULE_CUTOFF_DT_ALIAS,
INVALID_RULE_CUTOFF_DT_ALIAS,
LAPSE_DT_ALIAS,
ENR_CLEANUP_DT_ALIAS,
grading_schema_dt_alias ,
begin_trans_dt_alias,
clean_trans_dt_alias,
planning_open_dt_alias,
schedule_open_dt_alias,
audit_status_dt_alias
from IGS_EN_CAL_CONF
where ROWID = X_ROWID
for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_ROWID IN VARCHAR2,
X_S_CONTROL_NUM in NUMBER,
X_COMMENCE_CUTOFF_DT_ALIAS in VARCHAR2,
X_COMMENCEMENT_DT_ALIAS in VARCHAR2,
X_EFFECT_ENR_STRT_DT_ALIAS in VARCHAR2,
X_RECORD_OPEN_DT_ALIAS in VARCHAR2,
X_RECORD_CUTOFF_DT_ALIAS in VARCHAR2,
X_SUB_UNIT_DT_ALIAS in VARCHAR2,
X_VARIATION_CUTOFF_DT_ALIAS in VARCHAR2,
X_ENR_FORM_DUE_DT_ALIAS in VARCHAR2,
X_ENR_PCKG_PROD_DT_ALIAS in VARCHAR2,
X_LOAD_EFFECT_DT_ALIAS in VARCHAR2,
X_ENR_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
X_INVALID_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
X_LAPSE_DT_ALIAS in VARCHAR2,
X_ENR_CLEANUP_DT_ALIAS in VARCHAR2,
X_MODE in VARCHAR2 default 'R',
x_grading_schema_dt_alias IN VARCHAR2 ,
x_begin_trans_dt_alias IN VARCHAR2,
x_clean_trans_dt_alias IN VARCHAR2,
x_planning_open_dt_alias IN VARCHAR2 ,
x_schedule_open_dt_alias IN VARCHAR2 ,
x_audit_status_dt_alias IN VARCHAR2
) 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_s_control_num => x_s_control_num ,
x_commence_cutoff_dt_alias => x_commence_cutoff_dt_alias ,
x_commencement_dt_alias => x_commencement_dt_alias ,
x_effect_enr_strt_dt_alias => x_effect_enr_strt_dt_alias ,
x_record_open_dt_alias => x_record_open_dt_alias ,
x_record_cutoff_dt_alias => x_record_cutoff_dt_alias ,
x_sub_unit_dt_alias => x_sub_unit_dt_alias ,
x_variation_cutoff_dt_alias => x_variation_cutoff_dt_alias ,
x_enr_form_due_dt_alias => x_enr_form_due_dt_alias ,
x_enr_pckg_prod_dt_alias => x_enr_pckg_prod_dt_alias ,
x_load_effect_dt_alias => x_load_effect_dt_alias ,
x_enr_rule_cutoff_dt_alias => x_enr_rule_cutoff_dt_alias ,
x_invalid_rule_cutoff_dt_alias => x_invalid_rule_cutoff_dt_alias ,
x_enr_cleanup_dt_alias => x_enr_cleanup_dt_alias ,
x_lapse_dt_alias => x_lapse_dt_alias ,
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_grading_schema_dt_alias => x_grading_schema_dt_alias,
x_begin_trans_dt_alias => x_begin_trans_dt_alias,
x_clean_trans_dt_alias => x_clean_trans_dt_alias,
x_planning_open_dt_alias =>x_planning_open_dt_alias,
x_schedule_open_dt_alias => x_schedule_open_dt_alias,
x_audit_status_dt_alias => x_audit_status_dt_alias
);
update IGS_EN_CAL_CONF set
COMMENCE_CUTOFF_DT_ALIAS = NEW_REFERENCES.COMMENCE_CUTOFF_DT_ALIAS,
COMMENCEMENT_DT_ALIAS = NEW_REFERENCES.COMMENCEMENT_DT_ALIAS,
EFFECT_ENR_STRT_DT_ALIAS = NEW_REFERENCES.EFFECT_ENR_STRT_DT_ALIAS,
RECORD_OPEN_DT_ALIAS = NEW_REFERENCES.RECORD_OPEN_DT_ALIAS,
RECORD_CUTOFF_DT_ALIAS = NEW_REFERENCES.RECORD_CUTOFF_DT_ALIAS,
SUB_UNIT_DT_ALIAS = NEW_REFERENCES.SUB_UNIT_DT_ALIAS,
VARIATION_CUTOFF_DT_ALIAS = NEW_REFERENCES.VARIATION_CUTOFF_DT_ALIAS,
ENR_FORM_DUE_DT_ALIAS = NEW_REFERENCES.ENR_FORM_DUE_DT_ALIAS,
ENR_PCKG_PROD_DT_ALIAS = NEW_REFERENCES.ENR_PCKG_PROD_DT_ALIAS,
LOAD_EFFECT_DT_ALIAS = NEW_REFERENCES.LOAD_EFFECT_DT_ALIAS,
ENROLLED_RULE_CUTOFF_DT_ALIAS = NEW_REFERENCES.ENROLLED_RULE_CUTOFF_DT_ALIAS,
INVALID_RULE_CUTOFF_DT_ALIAS = NEW_REFERENCES.INVALID_RULE_CUTOFF_DT_ALIAS,
LAPSE_DT_ALIAS = NEW_REFERENCES.LAPSE_DT_ALIAS,
ENR_CLEANUP_DT_ALIAS = NEW_REFERENCES.ENR_CLEANUP_DT_ALIAS,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
grading_schema_dt_alias = x_grading_schema_dt_alias ,
begin_trans_dt_alias = x_begin_trans_dt_alias,
clean_trans_dt_alias = x_clean_trans_dt_alias,
planning_open_dt_alias=x_planning_open_dt_alias,
schedule_open_dt_alias=x_schedule_open_dt_alias,
audit_status_dt_alias=x_audit_status_dt_alias
where ROWID = X_ROWID
;
p_action => 'UPDATE',
x_rowid => X_ROWID
);
end UPDATE_ROW;
cursor c1 is select rowid from IGS_EN_CAL_CONF
where S_CONTROL_NUM = nvl(X_S_CONTROL_NUM,1)
;
INSERT_ROW (
X_ROWID,
X_S_CONTROL_NUM,
X_COMMENCE_CUTOFF_DT_ALIAS,
X_COMMENCEMENT_DT_ALIAS,
X_EFFECT_ENR_STRT_DT_ALIAS,
X_RECORD_OPEN_DT_ALIAS,
X_RECORD_CUTOFF_DT_ALIAS,
X_SUB_UNIT_DT_ALIAS,
X_VARIATION_CUTOFF_DT_ALIAS,
X_ENR_FORM_DUE_DT_ALIAS,
X_ENR_PCKG_PROD_DT_ALIAS,
X_LOAD_EFFECT_DT_ALIAS,
X_ENR_RULE_CUTOFF_DT_ALIAS,
X_INVALID_RULE_CUTOFF_DT_ALIAS,
X_LAPSE_DT_ALIAS,
X_ENR_CLEANUP_DT_ALIAS,
X_MODE,
x_grading_schema_dt_alias,
x_begin_trans_dt_alias,
x_clean_trans_dt_alias,
x_planning_open_dt_alias ,
x_schedule_open_dt_alias ,
x_audit_status_dt_alias
);
UPDATE_ROW (
X_ROWID,
X_S_CONTROL_NUM,
X_COMMENCE_CUTOFF_DT_ALIAS,
X_COMMENCEMENT_DT_ALIAS,
X_EFFECT_ENR_STRT_DT_ALIAS,
X_RECORD_OPEN_DT_ALIAS,
X_RECORD_CUTOFF_DT_ALIAS,
X_SUB_UNIT_DT_ALIAS,
X_VARIATION_CUTOFF_DT_ALIAS,
X_ENR_FORM_DUE_DT_ALIAS,
X_ENR_PCKG_PROD_DT_ALIAS,
X_LOAD_EFFECT_DT_ALIAS,
X_ENR_RULE_CUTOFF_DT_ALIAS,
X_INVALID_RULE_CUTOFF_DT_ALIAS,
X_LAPSE_DT_ALIAS,
X_ENR_CLEANUP_DT_ALIAS,
X_MODE,
x_grading_schema_dt_alias,
x_begin_trans_dt_alias,
x_clean_trans_dt_alias,
x_planning_open_dt_alias ,
x_schedule_open_dt_alias ,
x_audit_status_dt_alias
);
procedure DELETE_ROW (
X_ROWID IN VARCHAR2
) AS
begin
Before_DML(
p_action => 'DELETE',
x_rowid => X_ROWID
);
delete from IGS_EN_CAL_CONF
where ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
end DELETE_ROW;