[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| that they were inserted by datamerge. |
| |
| The ADD_ROW routine will see whether a row exists by selecting |
| based on the primary key, and updates the row if it exists, |
| or inserts the row if it doesn't already exist. |
| |
| This module is called by AutoInstall (afplss.drv) on install and |
| upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
| |
| HISTORY |
| Who When What |
| bannamal 29-Sep-2004 3416863 cod xml changes for pell and |
| direct loan. added two new columns |
| cdcruz 06-Jun-2003 # 2858504 FA 118.1 Legacy Import |
| Added new column : award_year_status_code |
| masehgal 17-Oct-2002 # 2613546 FA 105_108 Multiple Award Years |
| Added unique check on system award year |
| Added new column : |
| sys_award_year |
| masehgal 14-Jun-2002 # 2413695 Changed message to |
| 'IGF','IGF_AP_BAM_CI_FK' |
| |
| brajendr 04-Jul-2002 Bug # 2436484 - FACR009 Calendar Relations |
| Following columns are obsoleted. Signature |
| of PKG is retained and all the references |
| are removed |
| ci_sequence_number_acad |
| ci_cal_type_acad |
| ci_cal_type_adm |
| ci_sequence_number_adm |
| |
*=======================================================================*/
l_rowid VARCHAR2(25);
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : prchandr
|| Created On : 28-MAR-2001
|| Purpose : Initialises the Old and New references for the columns of the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR cur_old_ref_values IS
SELECT *
FROM igf_ap_batch_aw_map_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');
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');
SELECT rowid
FROM igf_ap_batch_aw_map_all
WHERE bam_id = x_bam_id
FOR UPDATE NOWAIT;
SELECT rowid
FROM igf_ap_batch_aw_map_all
WHERE ci_cal_type = x_ci_cal_type
AND ci_sequence_number = x_ci_sequence_number
AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
SELECT rowid
FROM igf_ap_batch_aw_map_all
WHERE sys_award_year = x_sys_award_year
AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
SELECT rowid
FROM igf_ap_batch_aw_map_all
WHERE ((ci_cal_type = x_cal_type) AND
(ci_sequence_number = x_sequence_number))
OR ((ci_cal_type_acad = x_cal_type) AND
(ci_sequence_number_acad = x_sequence_number)) ;
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : prchandr
|| Created On : 28-MAR-2001
|| Purpose : Initialises the columns, Checks Constraints, Calls the
|| Trigger Handlers for the table, before any DML operation.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
set_column_values (
p_action,
x_rowid,
x_batch_year,
x_ci_sequence_number,
x_ci_cal_type,
x_ci_sequence_number_acad,
x_ci_cal_type_acad,
x_ci_cal_type_adm,
x_ci_sequence_number_adm,
x_bam_id,
x_css_academic_year,
x_efc_frml ,
x_num_days_divisor ,
x_roundoff_fact ,
x_efc_dob,
x_dl_code,
x_ffel_code,
x_pell_code,
x_isir_code,
x_profile_code,
x_tolerance_limit,
x_sys_award_year,
x_award_year_status_code,
x_pell_participant_code,
x_dl_participant_code,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_publish_in_ss_flag
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation( new_references.bam_id )) THEN
FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
check_uniqueness;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation ( new_references.bam_id ) ) THEN
FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
check_uniqueness;
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2 ,
x_batch_year IN NUMBER ,
x_ci_sequence_number IN NUMBER ,
x_ci_cal_type IN VARCHAR2 ,
x_ci_sequence_number_acad IN NUMBER ,
x_ci_cal_type_acad IN VARCHAR2 ,
x_ci_cal_type_adm IN VARCHAR2 ,
x_ci_sequence_number_adm IN NUMBER ,
x_bam_id IN OUT NOCOPY NUMBER ,
x_css_academic_year IN NUMBER ,
x_efc_frml IN VARCHAR2 ,
x_num_days_divisor IN NUMBER ,
x_roundoff_fact IN VARCHAR2 ,
x_efc_dob IN DATE ,
x_dl_code IN VARCHAR2 ,
x_ffel_code IN VARCHAR2 ,
x_pell_code IN VARCHAR2 ,
x_isir_code IN VARCHAR2 ,
x_profile_code IN VARCHAR2 ,
x_tolerance_limit IN NUMBER ,
x_sys_award_year IN VARCHAR2 ,
x_award_year_status_code IN VARCHAR2 ,
x_pell_participant_code IN VARCHAR2 ,
x_dl_participant_code IN VARCHAR2 ,
x_mode IN VARCHAR2,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : prchandr
|| Created On : 28-MAR-2001
|| Purpose : Handles the INSERT DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR c IS
SELECT rowid
FROM igf_ap_batch_aw_map_all
WHERE bam_id = x_bam_id;
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;
SELECT igf_ap_batch_aw_map_all_s.NEXTVAL INTO x_bam_id FROM dual;
p_action => 'INSERT',
x_rowid => x_rowid,
x_batch_year => x_batch_year,
x_ci_sequence_number => x_ci_sequence_number,
x_ci_cal_type => x_ci_cal_type,
x_ci_sequence_number_acad => x_ci_sequence_number_acad,
x_ci_cal_type_acad => x_ci_cal_type_acad,
x_ci_cal_type_adm => x_ci_cal_type_adm,
x_ci_sequence_number_adm => x_ci_sequence_number_adm,
x_bam_id => x_bam_id,
x_css_academic_year => x_css_academic_year,
x_efc_frml => x_efc_frml,
x_num_days_divisor => x_num_days_divisor,
x_roundoff_fact => x_roundoff_fact,
x_efc_dob => x_efc_dob,
x_dl_code => x_dl_code,
x_ffel_code => x_ffel_code,
x_pell_code => x_pell_code,
x_isir_code => x_isir_code,
x_profile_code => x_profile_code,
x_tolerance_limit => x_tolerance_limit ,
x_sys_award_year => x_sys_award_year ,
x_award_year_status_code => x_award_year_status_code,
x_pell_participant_code => x_pell_participant_code,
x_dl_participant_code => x_dl_participant_code,
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_publish_in_ss_flag => x_publish_in_ss_flag
);
INSERT INTO igf_ap_batch_aw_map_all (
batch_year,
ci_sequence_number,
ci_cal_type,
ci_sequence_number_acad,
ci_cal_type_acad,
ci_cal_type_adm,
ci_sequence_number_adm,
bam_id,
css_academic_year,
efc_frml ,
num_days_divisor,
roundoff_fact,
efc_dob,
dl_code,
ffel_code,
pell_code,
isir_code,
profile_code,
tolerance_limit ,
sys_award_year ,
award_year_status_code ,
pell_participant_code ,
dl_participant_code ,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id,
publish_in_ss_flag
) VALUES (
new_references.batch_year,
new_references.ci_sequence_number,
new_references.ci_cal_type,
NULL,
NULL,
NULL,
NULL,
new_references.bam_id,
new_references.css_academic_year,
new_references.efc_frml ,
new_references.num_days_divisor,
new_references.roundoff_fact,
new_references.efc_dob,
new_references.dl_code,
new_references.ffel_code,
new_references.pell_code,
new_references.isir_code,
new_references.profile_code,
new_references.tolerance_limit ,
new_references.sys_award_year ,
new_references.award_year_status_code ,
new_references.pell_participant_code ,
new_references.dl_participant_code ,
x_last_update_date,
x_last_updated_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
l_org_id,
new_references.publish_in_ss_flag
);
END insert_row;
SELECT batch_year,
ci_sequence_number,
ci_cal_type,
css_academic_year,
efc_frml ,
num_days_divisor ,
roundoff_fact ,
efc_dob,
dl_code,
ffel_code,
pell_code,
isir_code,
profile_code,
tolerance_limit,
sys_award_year,
award_year_status_code,
pell_participant_code,
dl_participant_code,
publish_in_ss_flag
FROM igf_ap_batch_aw_map_all
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_rowid IN VARCHAR2 ,
x_batch_year IN NUMBER ,
x_ci_sequence_number IN NUMBER ,
x_ci_cal_type IN VARCHAR2 ,
x_ci_sequence_number_acad IN NUMBER ,
x_ci_cal_type_acad IN VARCHAR2 ,
x_ci_cal_type_adm IN VARCHAR2 ,
x_ci_sequence_number_adm IN NUMBER ,
x_bam_id IN NUMBER ,
x_css_academic_year IN NUMBER ,
x_efc_frml IN VARCHAR2 ,
x_num_days_divisor IN NUMBER ,
x_roundoff_fact IN VARCHAR2 ,
x_efc_dob IN DATE ,
x_dl_code IN VARCHAR2 ,
x_ffel_code IN VARCHAR2 ,
x_pell_code IN VARCHAR2 ,
x_isir_code IN VARCHAR2 ,
x_profile_code IN VARCHAR2 ,
x_tolerance_limit IN NUMBER ,
x_sys_award_year IN VARCHAR2 ,
x_award_year_status_code IN VARCHAR2 ,
x_pell_participant_code IN VARCHAR2 ,
x_dl_participant_code IN VARCHAR2 ,
x_mode IN VARCHAR2,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : prchandr
|| Created On : 28-MAR-2001
|| Purpose : Handles the UPDATE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
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_batch_year => x_batch_year,
x_ci_sequence_number => x_ci_sequence_number,
x_ci_cal_type => x_ci_cal_type,
x_ci_sequence_number_acad => x_ci_sequence_number_acad,
x_ci_cal_type_acad => x_ci_cal_type_acad,
x_ci_cal_type_adm => x_ci_cal_type_adm,
x_ci_sequence_number_adm => x_ci_sequence_number_adm,
x_bam_id => x_bam_id,
x_css_academic_year => x_css_academic_year,
x_efc_frml => x_efc_frml,
x_num_days_divisor => x_num_days_divisor,
x_roundoff_fact => x_roundoff_fact,
x_efc_dob => x_efc_dob,
x_dl_code => x_dl_code,
x_ffel_code => x_ffel_code,
x_pell_code => x_pell_code,
x_isir_code => x_isir_code,
x_profile_code => x_profile_code,
x_tolerance_limit => x_tolerance_limit ,
x_sys_award_year => x_sys_award_year ,
x_award_year_status_code => x_award_year_status_code ,
x_pell_participant_code => x_pell_participant_code ,
x_dl_participant_code => x_dl_participant_code ,
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_publish_in_ss_flag => x_publish_in_ss_flag
);
UPDATE igf_ap_batch_aw_map_all
SET
batch_year = new_references.batch_year,
ci_sequence_number = new_references.ci_sequence_number,
ci_cal_type = new_references.ci_cal_type,
css_academic_year = new_references.css_academic_year,
efc_frml = new_references.efc_frml,
num_days_divisor = new_references.num_days_divisor,
roundoff_fact = new_references.roundoff_fact,
efc_dob = new_references.efc_dob,
dl_code = new_references.dl_code,
ffel_code = new_references.ffel_code,
pell_code = new_references.pell_code,
isir_code = new_references.isir_code,
profile_code = new_references.profile_code,
tolerance_limit = new_references.tolerance_limit ,
sys_award_year = new_references.sys_award_year ,
award_year_status_code = new_references.award_year_status_code ,
pell_participant_code = new_references.pell_participant_code,
dl_participant_code = new_references.dl_participant_code,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
publish_in_ss_flag = new_references.publish_in_ss_flag
WHERE rowid = x_rowid;
END update_row;
|| Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR c1 IS
SELECT rowid
FROM igf_ap_batch_aw_map_all
WHERE bam_id = x_bam_id;
insert_row (
x_rowid,
x_batch_year,
x_ci_sequence_number,
x_ci_cal_type,
x_ci_sequence_number_acad,
x_ci_cal_type_acad,
x_ci_cal_type_adm,
x_ci_sequence_number_adm,
x_bam_id,
x_efc_frml ,
x_num_days_divisor,
x_roundoff_fact ,
x_efc_dob,
x_dl_code,
x_ffel_code,
x_pell_code,
x_isir_code,
x_profile_code,
x_tolerance_limit ,
x_sys_award_year ,
x_award_year_status_code,
x_pell_participant_code,
x_dl_participant_code,
x_mode,
x_publish_in_ss_flag
);
update_row (
x_rowid,
x_batch_year,
x_ci_sequence_number,
x_ci_cal_type,
x_ci_sequence_number_acad,
x_ci_cal_type_acad,
x_ci_cal_type_adm,
x_ci_sequence_number_adm,
x_bam_id,
x_css_academic_year,
x_efc_frml ,
x_num_days_divisor ,
x_roundoff_fact ,
x_efc_dob,
x_dl_code,
x_ffel_code,
x_pell_code,
x_isir_code,
x_profile_code,
x_tolerance_limit ,
x_sys_award_year ,
x_award_year_status_code,
x_pell_participant_code,
x_dl_participant_code,
x_mode,
x_publish_in_ss_flag
);
PROCEDURE delete_row (
x_rowid IN VARCHAR2
) AS
/*
|| Created By : prchandr
|| Created On : 28-MAR-2001
|| Purpose : Handles the DELETE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
before_dml (
p_action => 'DELETE',
x_rowid => x_rowid
);
DELETE FROM igf_ap_batch_aw_map_all
WHERE rowid = x_rowid;
END delete_row;