The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_logical_delete_dt IN DATE ,
x_comments IN VARCHAR2 ,
x_org_id IN NUMBER ,
x_creation_date IN DATE ,
x_created_by IN NUMBER ,
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER
) AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
|| As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
----------------------------------------------------------------------------*/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_FI_FEE_AS_ALL
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');
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;
PROCEDURE BeforeRowInsertUpdateDelete1(
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
masehgal , IDC 10-JAN-2002 Enh # 2170429
Obsoletion of SPONSOR_CD
SCHODAVA 28-NOV-2001 Enh # 2122257
(SFCR015 : Change In Fee Category)
Changed the call to
IGS_FI_VAL_FAS.finp_val_fas_ass_ind function.
Added the params fee_cal_type
and fee_ci_sequence_number
(reverse chronological order - newest change first)
***************************************************************/
v_message_name varchar2(30);
SELECT 'x'
FROM igs_fi_fee_type fft
WHERE fft.s_fee_type = 'EXTERNAL'
AND fft.fee_type = 'x_fee_type';
/* IF p_inserting THEN
-- Validate current date not greater than Retrospective assessment period.
IF IGS_FI_VAL_FAS.finp_val_fas_retro (
new_references.fee_type,
new_references.fee_cal_type,
new_references.fee_ci_sequence_number,
new_references.fee_cat,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS',v_message_name);
IF p_inserting OR
(p_updating AND (new_references.course_cd <> old_references.course_cd OR
new_references.fee_cat <> old_references.fee_cat)) THEN
-- Validate that IGS_PS_COURSE code can be specified.
IF IGS_FI_VAL_FAS.finp_val_fas_create (
new_references.fee_type,
new_references.fee_cat,
new_references.course_cd,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS',v_message_name);
IF p_inserting OR
(new_references.effective_dt <> old_references.effective_dt) THEN
IF IGS_FI_VAL_FAS.finp_val_fas_eff_dt (
new_references.fee_type,
new_references.fee_cal_type,
new_references.fee_ci_sequence_number,
new_references.fee_cat,
new_references.effective_dt,
new_references.s_transaction_type,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS',v_message_name);
IF p_inserting OR p_updating THEN
-- Validate that course code is fee assessable for manual entries.
IF IGS_FI_VAL_FAS.finp_val_fas_com (
new_references.s_transaction_type,
new_references.comments,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS',v_message_name);
new_references.logical_delete_dt,
old_references.logical_delete_dt,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS',v_message_name);
END BeforeRowInsertUpdateDelete1;
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_FI_FEE_AS_ALL
WHERE person_id = x_person_id
AND transaction_id = x_transaction_id
FOR UPDATE NOWAIT;
SELECT rowid
FROM IGS_FI_FEE_AS_ALL
WHERE transaction_id = new_references.transaction_id
AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
FOR UPDATE NOWAIT;
SELECT rowid
FROM IGS_FI_FEE_AS_ALL
WHERE course_cd = x_course_cd ;
SELECT rowid
FROM IGS_FI_FEE_AS_ALL
WHERE person_id = x_person_id ;
SELECT rowid
FROM IGS_FI_FEE_AS_ALL
WHERE s_transaction_type = x_s_transaction_type ;
x_logical_delete_dt IN DATE,
x_comments IN VARCHAR2,
x_org_id IN NUMBER,
x_creation_date IN DATE,
x_created_by IN NUMBER,
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER
) AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| smvk 02-Sep-2002 Modified the call to beforerowinsertupdatedelete1 procedure as per apps standards.
|| As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
|| vvutukur 30-Aug-2002 Bug#2531390.Removed calls to check_uk_child_existance local procedure
|| as this procedure is removed as part of this bugfix.Also removed default
|| null from before_dml procedure parameters to avoid gscc warnings.
----------------------------------------------------------------------------*/
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_person_id,
x_transaction_id,
x_fee_type,
x_fee_cal_type,
x_fee_ci_sequence_number,
x_fee_cat,
x_s_transaction_type,
x_transaction_dt,
x_transaction_amount,
x_currency_cd,
x_exchange_rate,
x_chg_elements,
x_effective_dt,
x_course_cd,
x_notification_dt,
x_logical_delete_dt,
x_comments,
x_org_id,
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.
BeforeRowInsertUpdateDelete1 (p_inserting => TRUE , p_updating =>FALSE , p_deleting =>FALSE);
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
BeforeRowInsertUpdateDelete1 (p_inserting => FALSE, p_updating => TRUE, p_deleting =>FALSE);
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
Check_Child_Existance;
BeforeRowInsertUpdateDelete1 (p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
ELSIF (p_action = 'VALIDATE_INSERT') THEN
IF Get_PK_For_Validation (
new_references.person_id ,
new_references.transaction_id
) THEN
Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
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_PERSON_ID in NUMBER,
X_TRANSACTION_ID in out NOCOPY NUMBER,
X_FEE_TYPE in VARCHAR2,
X_FEE_CAL_TYPE in VARCHAR2,
X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
X_FEE_CAT in VARCHAR2,
X_S_TRANSACTION_TYPE in VARCHAR2,
X_TRANSACTION_DT in DATE,
X_TRANSACTION_AMOUNT in NUMBER,
X_CURRENCY_CD in VARCHAR2,
X_EXCHANGE_RATE in NUMBER,
X_CHG_ELEMENTS in NUMBER,
X_EFFECTIVE_DT in DATE,
X_COURSE_CD in VARCHAR2,
X_NOTIFICATION_DT in DATE,
X_LOGICAL_DELETE_DT in DATE,
X_COMMENTS in VARCHAR2,
x_org_id IN NUMBER,
X_MODE in VARCHAR2
) AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| vvutukur 30-Aug-2002 Bug#2531390.Removed default value of x_mode parameter to avoid gscc
|| warnings.
----------------------------------------------------------------------------*/
cursor C is select ROWID from IGS_FI_FEE_AS_ALL
where PERSON_ID = X_PERSON_ID
and TRANSACTION_ID = X_TRANSACTION_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;
SELECT igs_fi_fee_as_trn_id_s.nextval
INTO x_transaction_id
FROM DUAL;
p_action => 'INSERT',
x_rowid => x_rowid,
x_person_id => x_person_id,
x_transaction_id => x_transaction_id,
x_fee_type => x_fee_type,
x_fee_cal_type => x_fee_cal_type,
x_fee_ci_sequence_number => x_fee_ci_sequence_number,
x_fee_cat => x_fee_cat,
x_s_transaction_type => x_s_transaction_type,
x_transaction_dt => x_transaction_dt,
x_transaction_amount => x_transaction_amount,
x_currency_cd => x_currency_cd,
x_exchange_rate => x_exchange_rate,
x_chg_elements => x_chg_elements,
x_effective_dt => x_effective_dt,
x_course_cd => x_course_cd,
x_notification_dt => x_notification_dt,
x_logical_delete_dt => x_logical_delete_dt,
x_comments => x_comments,
x_org_id => igs_ge_gen_003.get_org_id,
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_FI_FEE_AS_ALL (
PERSON_ID,
TRANSACTION_ID,
FEE_TYPE,
FEE_CAL_TYPE,
FEE_CI_SEQUENCE_NUMBER,
FEE_CAT,
S_TRANSACTION_TYPE,
TRANSACTION_DT,
TRANSACTION_AMOUNT,
CURRENCY_CD,
EXCHANGE_RATE,
CHG_ELEMENTS,
EFFECTIVE_DT,
COURSE_CD,
NOTIFICATION_DT,
LOGICAL_DELETE_DT,
COMMENTS,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE
) values (
NEW_REFERENCES.PERSON_ID,
NEW_REFERENCES.TRANSACTION_ID,
NEW_REFERENCES.FEE_TYPE,
NEW_REFERENCES.FEE_CAL_TYPE,
NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
NEW_REFERENCES.FEE_CAT,
NEW_REFERENCES.S_TRANSACTION_TYPE,
NEW_REFERENCES.TRANSACTION_DT,
NEW_REFERENCES.TRANSACTION_AMOUNT,
NEW_REFERENCES.CURRENCY_CD,
NEW_REFERENCES.EXCHANGE_RATE,
NEW_REFERENCES.CHG_ELEMENTS,
NEW_REFERENCES.EFFECTIVE_DT,
NEW_REFERENCES.COURSE_CD,
NEW_REFERENCES.NOTIFICATION_DT,
NEW_REFERENCES.LOGICAL_DELETE_DT,
NEW_REFERENCES.COMMENTS,
NEW_REFERENCES.ORG_ID,
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
);
p_action => 'INSERT' ,
x_rowid => X_ROWID );
end INSERT_ROW;
X_LOGICAL_DELETE_DT in DATE,
X_COMMENTS in VARCHAR2
) AS
cursor c1 is select
FEE_TYPE,
FEE_CAL_TYPE,
FEE_CI_SEQUENCE_NUMBER,
FEE_CAT,
S_TRANSACTION_TYPE,
TRANSACTION_DT,
TRANSACTION_AMOUNT,
CURRENCY_CD,
EXCHANGE_RATE,
CHG_ELEMENTS,
EFFECTIVE_DT,
COURSE_CD,
NOTIFICATION_DT,
LOGICAL_DELETE_DT,
COMMENTS
from IGS_FI_FEE_AS_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)))
AND ((tlinfo.COMMENTS = X_COMMENTS)
OR ((tlinfo.COMMENTS is null)
AND (X_COMMENTS is null)))
) then
null;
procedure UPDATE_ROW (
X_ROWID in VARCHAR2,
X_PERSON_ID in NUMBER,
X_TRANSACTION_ID in NUMBER,
X_FEE_TYPE in VARCHAR2,
X_FEE_CAL_TYPE in VARCHAR2,
X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
X_FEE_CAT in VARCHAR2,
X_S_TRANSACTION_TYPE in VARCHAR2,
X_TRANSACTION_DT in DATE,
X_TRANSACTION_AMOUNT in NUMBER,
X_CURRENCY_CD in VARCHAR2,
X_EXCHANGE_RATE in NUMBER,
X_CHG_ELEMENTS in NUMBER,
X_EFFECTIVE_DT in DATE,
X_COURSE_CD in VARCHAR2,
X_NOTIFICATION_DT in DATE,
X_LOGICAL_DELETE_DT in DATE,
X_COMMENTS in VARCHAR2,
X_MODE in VARCHAR2
) AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| vvutukur 30-Aug-2002 Bug#2531390.Removed default value of x_mode parameter to avoid gscc
|| warnings.
----------------------------------------------------------------------------*/
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_person_id => x_person_id,
x_transaction_id => x_transaction_id,
x_fee_type => x_fee_type,
x_fee_cal_type => x_fee_cal_type,
x_fee_ci_sequence_number => x_fee_ci_sequence_number,
x_fee_cat => x_fee_cat,
x_s_transaction_type => x_s_transaction_type,
x_transaction_dt => x_transaction_dt,
x_transaction_amount => x_transaction_amount,
x_currency_cd => x_currency_cd,
x_exchange_rate => x_exchange_rate,
x_chg_elements => x_chg_elements,
x_effective_dt => x_effective_dt,
x_course_cd => x_course_cd,
x_notification_dt => x_notification_dt,
x_logical_delete_dt => x_logical_delete_dt,
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_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
X_PROGRAM_UPDATE_DATE := SYSDATE;
update IGS_FI_FEE_AS_ALL set
FEE_TYPE = NEW_REFERENCES.FEE_TYPE,
FEE_CAL_TYPE = NEW_REFERENCES.FEE_CAL_TYPE,
FEE_CI_SEQUENCE_NUMBER = NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
FEE_CAT = NEW_REFERENCES.FEE_CAT,
S_TRANSACTION_TYPE = NEW_REFERENCES.S_TRANSACTION_TYPE,
TRANSACTION_DT = NEW_REFERENCES.TRANSACTION_DT,
TRANSACTION_AMOUNT = NEW_REFERENCES.TRANSACTION_AMOUNT,
CURRENCY_CD = NEW_REFERENCES.CURRENCY_CD,
EXCHANGE_RATE = NEW_REFERENCES.EXCHANGE_RATE,
CHG_ELEMENTS = NEW_REFERENCES.CHG_ELEMENTS,
EFFECTIVE_DT = NEW_REFERENCES.EFFECTIVE_DT,
COURSE_CD = NEW_REFERENCES.COURSE_CD,
NOTIFICATION_DT = NEW_REFERENCES.NOTIFICATION_DT,
LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
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,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
where ROWID = X_ROWID;
p_action => 'UPDATE' ,
x_rowid => X_ROWID
);
end UPDATE_ROW;
X_LOGICAL_DELETE_DT in DATE,
X_COMMENTS in VARCHAR2,
X_MODE in VARCHAR2,
X_ORG_ID in NUMBER
) AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| vvutukur 30-Aug-2002 Bug#2531390.Removed default value of x_mode parameter to avoid gscc
|| warnings.
----------------------------------------------------------------------------*/
cursor c1 is select rowid from IGS_FI_FEE_AS_ALL
where PERSON_ID = X_PERSON_ID
and TRANSACTION_ID = X_TRANSACTION_ID
;
INSERT_ROW (
X_ROWID,
X_PERSON_ID,
X_TRANSACTION_ID,
X_FEE_TYPE,
X_FEE_CAL_TYPE,
X_FEE_CI_SEQUENCE_NUMBER,
X_FEE_CAT,
X_S_TRANSACTION_TYPE,
X_TRANSACTION_DT,
X_TRANSACTION_AMOUNT,
X_CURRENCY_CD,
X_EXCHANGE_RATE,
X_CHG_ELEMENTS,
X_EFFECTIVE_DT,
X_COURSE_CD,
X_NOTIFICATION_DT,
X_LOGICAL_DELETE_DT,
X_COMMENTS,
X_MODE,
X_ORG_ID);
UPDATE_ROW (
X_ROWID,
X_PERSON_ID,
X_TRANSACTION_ID,
X_FEE_TYPE,
X_FEE_CAL_TYPE,
X_FEE_CI_SEQUENCE_NUMBER,
X_FEE_CAT,
X_S_TRANSACTION_TYPE,
X_TRANSACTION_DT,
X_TRANSACTION_AMOUNT,
X_CURRENCY_CD,
X_EXCHANGE_RATE,
X_CHG_ELEMENTS,
X_EFFECTIVE_DT,
X_COURSE_CD,
X_NOTIFICATION_DT,
X_LOGICAL_DELETE_DT,
X_COMMENTS,
X_MODE);
procedure DELETE_ROW (
X_ROWID in VARCHAR2
) AS
begin
Before_DML (
p_action => 'DELETE',
x_rowid => X_ROWID
);
delete from IGS_FI_FEE_AS_ALL
where ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
end DELETE_ROW;