[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
) AS
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_CA_INST_REL
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;
PROCEDURE BeforeRowInsertUpdateDelete(
p_inserting IN BOOLEAN DEFAULT FALSE,
p_updating IN BOOLEAN DEFAULT FALSE,
p_deleting IN BOOLEAN DEFAULT FALSE
) AS
/******************************************************************
Created By : schodava
Date Created By : 22-Jan-2002
Purpose : Enh # 2187247
Validates a one-to-one relation only between
a Fee and Load calendar instance.
Prevents delete of a FCI-LCI relation
if used in FAM module, FTCI or FCCI
Known limitations,
enhancements,
remarks :
Change History
Who When What
smvk 05-Feb-2002 Added call to IGS_FI_CREDITS_PKG.GET_FK_IGS_CA_INST_2
This is as per new Application Hierarchicy Compliance DLD
Enhancement Bug No. 2191470
******************************************************************/
cst_load CONSTANT VARCHAR2(10):= 'LOAD';
SELECT s_cal_cat
FROM igs_ca_type
WHERE cal_type = cp_cal_type;
SELECT 'x'
FROM igs_ca_inst_rel cir,
igs_ca_type ct1,
igs_ca_type ct2
WHERE cir.sub_cal_type = ct1.cal_type
AND ct1.s_cal_cat = cst_load
AND cir.sup_cal_type = ct2.cal_type
AND ct2.s_cal_cat = cst_fee
AND ((cir.sup_cal_type = cp_sup_cal_type
AND cir.sup_ci_sequence_number = cp_sup_ci_sequence_number)
OR (cir.sub_cal_type = cp_sub_cal_type
AND cir.sub_ci_sequence_number = cp_sub_ci_sequence_number))
AND cir.rowid <> NVL(l_rowid,'0');
IF p_inserting or p_updating THEN
-- Allows only one to one relation between a Fee Cal Instance
-- and a Load Cal Instance
FOR l_c_cat IN c_cat(new_references.sup_cal_type) LOOP
l_c_sup_cat := l_c_cat.s_cal_cat;
END BeforeRowInsertUpdateDelete;
PROCEDURE AfterStmtInsert2(
p_inserting IN BOOLEAN DEFAULT FALSE,
p_updating IN BOOLEAN DEFAULT FALSE,
p_deleting IN BOOLEAN DEFAULT FALSE
) AS
v_message_name varchar2(30);
IF p_inserting THEN
-- Validate superior/sub-ordinate calendar instance relationship
IF IGS_CA_VAL_CIR.calp_val_cir_ci (new_references.sub_cal_type,
new_references.sub_ci_sequence_number,
new_references.sup_cal_type,
new_references.sup_ci_sequence_number,
v_message_name) = FALSE
THEN
Fnd_Message.Set_Name('IGS',v_message_name);
END AfterStmtInsert2;
SELECT 'X'
FROM igs_ca_inst_rel a ,
igs_ca_type b ,
igs_ca_type c ,
igs_ca_inst d ,
igs_ca_inst e ,
igs_ca_stat f,
igs_ca_stat g
WHERE a.rowid = p_rowid
AND a.sub_cal_type = b.cal_type
AND b.s_cal_cat = 'ADMISSION'
AND a.sup_cal_type = c.cal_type
AND c.s_cal_cat = 'ACADEMIC'
AND a.sub_cal_type = d.cal_type
AND a.sub_ci_sequence_number = d.sequence_number
AND f.s_cal_status = 'ACTIVE'
AND d.cal_status = f.cal_status
AND a.sup_cal_type = e.cal_type
AND a.sup_ci_sequence_number = e.sequence_number
AND g.s_cal_status = 'ACTIVE'
AND e.cal_status = g.cal_status;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
SELECT rowid
FROM IGS_CA_INST_REL
WHERE sub_cal_type = x_sub_cal_type
AND sub_ci_sequence_number = x_sub_ci_sequence_number
AND sup_cal_type = x_sup_cal_type
AND sup_ci_sequence_number = x_sup_ci_sequence_number;
SELECT rowid
FROM IGS_CA_INST_REL
WHERE (sub_cal_type = x_cal_type
AND sub_ci_sequence_number = x_sequence_number)
OR (sup_cal_type = x_cal_type
AND sup_ci_sequence_number = x_sequence_number);
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL
) AS
/******************************************************************
Change History
Who When What
schodava 4-2-2002 Enh # 2187247
Added call to BeforeRowInsertUpdateDelete
kpadiyar 06-JAN-2002 Stop delete if SUP-CAL = Academic and SUB-CAL = Admission
and both calendars have active status.
******************************************************************/
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_sub_cal_type,
x_sub_ci_sequence_number,
x_sup_cal_type,
x_sup_ci_sequence_number,
x_load_research_percentage,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_PK_For_Validation (
new_references.sub_cal_type,
new_references.sub_ci_sequence_number,
new_references.sup_cal_type,
new_references.sup_ci_sequence_number ) THEN
Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
BeforeRowInsertUpdateDelete(p_inserting => TRUE);
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
BeforeRowInsertUpdateDelete(p_updating => TRUE);
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
BeforeRowInsertUpdateDelete(p_deleting => TRUE);
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_PK_For_Validation (
new_references.sub_cal_type,
new_references.sub_ci_sequence_number,
new_references.sup_cal_type,
new_references.sup_ci_sequence_number ) 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
-- Stop delete if SUP-CAL = Academic and SUB-CAL = Admission and both calendars have active status.
IF Check_acad_adm_cal_rel (p_rowid => x_rowid) THEN
Fnd_Message.Set_Name ('IGS', 'IGS_CA_REL_DEL_NOT');
BeforeRowInsertUpdateDelete(p_deleting => TRUE);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
AfterStmtInsert2 ( p_inserting => TRUE );
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_SUB_CAL_TYPE in VARCHAR2,
X_SUB_CI_SEQUENCE_NUMBER in NUMBER,
X_SUP_CAL_TYPE in VARCHAR2,
X_SUP_CI_SEQUENCE_NUMBER in NUMBER,
X_LOAD_RESEARCH_PERCENTAGE in NUMBER,
X_MODE in VARCHAR2 default 'R'
) AS
cursor C is select ROWID from IGS_CA_INST_REL
where SUB_CAL_TYPE = X_SUB_CAL_TYPE
and SUB_CI_SEQUENCE_NUMBER = X_SUB_CI_SEQUENCE_NUMBER
and SUP_CAL_TYPE = X_SUP_CAL_TYPE
and SUP_CI_SEQUENCE_NUMBER = X_SUP_CI_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_sub_cal_type =>X_SUB_CAL_TYPE,
x_sub_ci_sequence_number =>X_SUB_CI_SEQUENCE_NUMBER,
x_sup_cal_type =>X_SUP_CAL_TYPE,
x_sup_ci_sequence_number =>X_SUP_CI_SEQUENCE_NUMBER,
x_load_research_percentage =>X_LOAD_RESEARCH_PERCENTAGE,
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
);
insert into IGS_CA_INST_REL (
SUB_CAL_TYPE,
SUB_CI_SEQUENCE_NUMBER,
SUP_CAL_TYPE,
SUP_CI_SEQUENCE_NUMBER,
LOAD_RESEARCH_PERCENTAGE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
NEW_REFERENCES.SUB_CAL_TYPE,
NEW_REFERENCES.SUB_CI_SEQUENCE_NUMBER,
NEW_REFERENCES.SUP_CAL_TYPE,
NEW_REFERENCES.SUP_CI_SEQUENCE_NUMBER,
NEW_REFERENCES.LOAD_RESEARCH_PERCENTAGE,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
p_action =>'INSERT',
x_rowid =>X_ROWID
);
end INSERT_ROW;
cursor c1 is select
LOAD_RESEARCH_PERCENTAGE
from IGS_CA_INST_REL
where ROWID=X_ROWID
for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_ROWID in VARCHAR2,
X_SUB_CAL_TYPE in VARCHAR2,
X_SUB_CI_SEQUENCE_NUMBER in NUMBER,
X_SUP_CAL_TYPE in VARCHAR2,
X_SUP_CI_SEQUENCE_NUMBER in NUMBER,
X_LOAD_RESEARCH_PERCENTAGE in NUMBER,
X_MODE in VARCHAR2 default 'R'
) 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_sub_cal_type =>X_SUB_CAL_TYPE,
x_sub_ci_sequence_number =>X_SUB_CI_SEQUENCE_NUMBER,
x_sup_cal_type =>X_SUP_CAL_TYPE,
x_sup_ci_sequence_number =>X_SUP_CI_SEQUENCE_NUMBER,
x_load_research_percentage =>X_LOAD_RESEARCH_PERCENTAGE,
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
);
update IGS_CA_INST_REL set
LOAD_RESEARCH_PERCENTAGE = NEW_REFERENCES.LOAD_RESEARCH_PERCENTAGE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where ROWID=X_ROWID
;
p_action =>'UPDATE',
x_rowid =>X_ROWID
);
end UPDATE_ROW;
cursor c1 is select rowid from IGS_CA_INST_REL
where SUB_CAL_TYPE = X_SUB_CAL_TYPE
and SUB_CI_SEQUENCE_NUMBER = X_SUB_CI_SEQUENCE_NUMBER
and SUP_CAL_TYPE = X_SUP_CAL_TYPE
and SUP_CI_SEQUENCE_NUMBER = X_SUP_CI_SEQUENCE_NUMBER
;
INSERT_ROW (
X_ROWID,
X_SUB_CAL_TYPE,
X_SUB_CI_SEQUENCE_NUMBER,
X_SUP_CAL_TYPE,
X_SUP_CI_SEQUENCE_NUMBER,
X_LOAD_RESEARCH_PERCENTAGE,
X_MODE);
UPDATE_ROW (
X_ROWID,
X_SUB_CAL_TYPE,
X_SUB_CI_SEQUENCE_NUMBER,
X_SUP_CAL_TYPE,
X_SUP_CI_SEQUENCE_NUMBER,
X_LOAD_RESEARCH_PERCENTAGE,
X_MODE);
procedure DELETE_ROW (
X_ROWID in VARCHAR2
) AS
begin
Before_DML (
p_action =>'DELETE',
x_rowid =>X_ROWID
);
delete from IGS_CA_INST_REL
where ROWID=X_ROWID;
p_action =>'DELETE',
x_rowid =>X_ROWID
);
end DELETE_ROW;