[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_ds_ind VARCHAR2 ,
p_delete_dsd_ind VARCHAR2 ,
p_delete_dda_ind VARCHAR2 ,
p_org_id NUMBER
) AS
BEGIN
-- As per SFCR005, this Concurrent Program is obsolete and if the user is trying to
-- run the program then an error message should be written to the Log file that
-- the Concurrent Program is obsolete and this should not be run
FND_MESSAGE.Set_Name('IGS',
'IGS_GE_OBSOLETE_JOB');
-- This routine will logically delete all IGS_FI_ELM_RANGE_RT records
-- associated with an IGS_FI_ELM_RANGE or IGS_FI_FEE_AS_RATE record which is
-- being logically deleted.
DECLARE
e_resource_busy_exception EXCEPTION;
SELECT
ROWID,
err_id,
FEE_TYPE,
FEE_CAL_TYPE,
FEE_CI_SEQUENCE_NUMBER,
S_RELATION_TYPE,
RANGE_NUMBER,
RATE_NUMBER,
CREATE_DT,
FEE_CAT,
LOGICAL_DELETE_DT
FROM IGS_FI_ELM_RANGE_RT err
WHERE err.FEE_TYPE = p_fee_type AND
err.fee_cal_type = p_fee_cal_type AND
err.fee_ci_sequence_number = p_fee_ci_sequence_number AND
err.s_relation_type = p_s_relation_type AND
NVL(err.FEE_CAT, 'NULL') = NVL(p_fee_cat, 'NULL') AND
err.range_number = NVL(p_range_number, err.range_number) AND
err.rate_number = NVL(p_rate_number, err.rate_number) AND
err.logical_delete_dt IS NULL
FOR UPDATE OF err.logical_delete_dt NOWAIT;
-- Perform a logical delete of the associated IGS_FI_ELM_RANGE_RT items.
-- Update the appropriate records with NOWAIT option.
FOR v_err_rec IN c_err LOOP
IGS_FI_ELM_RANGE_RT_PKG.UPDATE_ROW(
X_ROWID => v_err_rec.ROWID,
X_ERR_ID => v_err_rec.ERR_ID,
X_FEE_TYPE => v_err_rec.FEE_TYPE,
X_FEE_CAL_TYPE => v_err_rec.FEE_CAL_TYPE,
X_FEE_CI_SEQUENCE_NUMBER => v_err_rec.FEE_CI_SEQUENCE_NUMBER,
X_S_RELATION_TYPE => v_err_rec.S_RELATION_TYPE,
X_RANGE_NUMBER => v_err_rec.RANGE_NUMBER,
X_RATE_NUMBER => v_err_rec.RATE_NUMBER,
X_CREATE_DT => v_err_rec.CREATE_DT,
X_FEE_CAT => v_err_rec.FEE_CAT,
X_LOGICAL_DELETE_DT => SYSDATE,
X_MODE => 'R');
-- This routine is used to insert a contract fee assessment rate record.
DECLARE
X_ROWID VARCHAR2(25);
--Insert the contract fee assessment rate
IGS_FI_FEE_AS_RT_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_person_id => p_person_id,
X_course_cd => p_course_cd,
X_FEE_TYPE => p_fee_type,
X_start_dt => p_start_dt,
X_end_dt => p_end_dt,
X_location_cd => p_location_cd,
X_ATTENDANCE_TYPE => p_attendance_type,
X_ATTENDANCE_MODE => p_attendance_mode,
X_chg_rate => p_chg_rate,
X_lower_nrml_rate_ovrd_ind => p_lower_nrml_rate_ovrd_ind,
X_MODE => 'R');
SELECT
ROWID,
SUB_ERR_ID ,
SUB_ER_ID ,
FAR_ID ,
CREATE_DATE ,
LOGICAL_DELETE_DATE
FROM IGS_FI_SUB_ER_RT fsert
WHERE fsert.sub_er_id = p_sub_er_id AND
fsert.logical_delete_date IS NULL
FOR UPDATE OF fsert.logical_delete_date NOWAIT;
-- Perform a logical delete of the associated IGS_FI_SUB_ER_RT items.
-- Update the appropriate records with NOWAIT option.
FOR v_fsert_rec IN c_fsert LOOP
igs_fi_sub_er_rt_pkg.update_row (
x_mode => 'R',
x_rowid => v_fsert_rec.ROWID,
x_sub_err_id => v_fsert_rec.SUB_ERR_ID,
x_sub_er_id => v_fsert_rec.SUB_ER_ID,
x_far_id => v_fsert_rec.FAR_ID,
x_create_date => v_fsert_rec.CREATE_DATE,
x_logical_delete_date => SYSDATE
);
SELECT
ROWID,
SUB_ER_ID ,
ER_ID ,
sub_range_num,
sub_lower_range,
sub_upper_range,
sub_chg_method_code,
LOGICAL_DELETE_DATE
FROM IGS_FI_SUB_ELM_RNG fser
WHERE fser.er_id = p_er_id AND
fser.logical_delete_date IS NULL
FOR UPDATE OF fser.logical_delete_date NOWAIT;
-- Perform a logical delete of the associated IGS_FI_SUB_ELM_RNG items.
-- Update the appropriate records with NOWAIT option.
FOR v_fser_rec IN c_fser LOOP
igs_fi_sub_elm_rng_pkg.update_row (
x_mode => 'R',
x_rowid => v_fser_rec.ROWID,
x_sub_er_id => v_fser_rec.SUB_ER_ID,
x_er_id => v_fser_rec.ER_ID,
x_sub_range_num => v_fser_rec.SUB_RANGE_NUM,
x_sub_lower_range => v_fser_rec.SUB_LOWER_RANGE,
x_sub_upper_range => v_fser_rec.SUB_UPPER_RANGE,
x_sub_chg_method_code => v_fser_rec.SUB_CHG_METHOD_CODE,
x_logical_delete_date => SYSDATE
);
SELECT
ROWID,
SUB_ER_ID ,
FAR_ID ,
CREATE_DATE
FROM IGS_FI_SUB_ER_RT fsert
WHERE fsert.sub_err_id = p_sub_err_id AND
fsert.logical_delete_date IS NULL
FOR UPDATE OF fsert.logical_delete_date NOWAIT;
-- Perform a logical delete of the associated IGS_FI_SUB_ER_RT items.
-- Update the appropriate records with NOWAIT option.
OPEN c_fsert;
igs_fi_sub_er_rt_pkg.update_row (
x_mode => 'R',
x_rowid => l_rowid,
x_sub_err_id => p_sub_err_id,
x_sub_er_id => l_sub_er_id,
x_far_id => l_far_id,
x_create_date => l_create_date,
x_logical_delete_date => SYSDATE
);