[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 |
|-----------------------------------------------------------------------|
|tsailaja 03-Aug-2006 Bug #5337555 |
| Included 'GPLUSFL' fund code. |
| so that any change to GPLUSFL awards gets logged |
| bvisvana 24-May-2005 FA 157 - Bug # 4382371 |
| Award History changes. Added procedures |
| set_award_source_change, |
| update_award_history,check_award_history |
| isChangeIn_AwardAttribute |
|-----------------------------------------------------------------------|
| smadathi 13-Oct-2004 Bug 3416936 ModIFied as per the TD |
| Added AfterRowInsertUpdateDelete1 and after_dml |
| procedures |
|-----------------------------------------------------------------------|
| bannamal 28-Sep-2004 Bug 3416863 - FA149 COD XML |
| Added check child existance for igf_gr_cod_dtls |
|-----------------------------------------------------------------------|
| sjadhav 1-Dec-2003 Bug 3252832 - FA 131 Build |
| Added two new columns for this build |
|-----------------------------------------------------------------------|
| veramach 1-NOV-2003 #3160568 Added adplans_id in the tbh calls |
|-----------------------------------------------------------------------|
| brajendr 21-Jul-2003 Bug 2991359 |
| Added check child existance for igf_gr_rfms |
|-----------------------------------------------------------------------|
| sjadhav 03-Jul-2003 Bug 3029739 |
| ModIFied igf_aw_gen.update_fmast call for |
| INSERT routine |
|-----------------------------------------------------------------------|
| bkkumar 04-jun-2003 Bug 2858504 Added award_ number _txt and |
| legacy_ record_flagin the tbh calls |
|-----------------------------------------------------------------------|
| adhawan 25-oct-2002 Bug 2613546. Added alt_pell_schedule in the |
| table handler calls gscc warnings fixed |
*=======================================================================*/
l_rowid VARCHAR2(25);
x_manual_update IN VARCHAR2,
x_rules_override IN VARCHAR2,
x_award_date IN DATE,
x_award_status IN VARCHAR2,
x_attribute_category IN VARCHAR2,
x_attribute1 IN VARCHAR2,
x_attribute2 IN VARCHAR2,
x_attribute3 IN VARCHAR2,
x_attribute4 IN VARCHAR2,
x_attribute5 IN VARCHAR2,
x_attribute6 IN VARCHAR2,
x_attribute7 IN VARCHAR2,
x_attribute8 IN VARCHAR2,
x_attribute9 IN VARCHAR2,
x_attribute10 IN VARCHAR2,
x_attribute11 IN VARCHAR2,
x_attribute12 IN VARCHAR2,
x_attribute13 IN VARCHAR2,
x_attribute14 IN VARCHAR2,
x_attribute15 IN VARCHAR2,
x_attribute16 IN VARCHAR2,
x_attribute17 IN VARCHAR2,
x_attribute18 IN VARCHAR2,
x_attribute19 IN VARCHAR2,
x_attribute20 IN VARCHAR2,
x_rvsn_id IN NUMBER,
x_alt_pell_schedule IN VARCHAR2,
x_award_number_txt IN VARCHAR2,
x_legacy_record_flag IN VARCHAR2,
x_adplans_id IN NUMBER,
x_lock_award_flag IN VARCHAR2,
x_app_trans_num_txt IN VARCHAR2,
x_awd_proc_status_code IN VARCHAR2,
x_notification_status_code IN VARCHAR2,
x_notification_status_date IN DATE,
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,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| Purpose : Initialises the Old and New references for the columns of the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| veramach 1-NOV-2003 #3160568 Added adplans_id to the procedure signature
|| (reverse chronological order - newest change first)
*/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGF_AW_AWARD_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.manual_update := x_manual_update;
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 AfterRowInsertUpdateDelete1(
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
/*-----------------------------------------------------------------
|| Created By : Sanil Madathil
|| Created On : 13-Oct-2004
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| tsailaja 03/08/2006 Bug #5337555 FA 163 Include 'GPLUSFL' fund code.
--------------------------------------------------------------------*/
CURSOR c_sl_clchsn_dtls (
cp_n_award_id igf_aw_award_all.award_id%TYPE
) IS
SELECT chdt.ROWID row_id,chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE chdt.award_id = cp_n_award_id
AND chdt.status_code IN ('R','N','D')
AND chdt.response_status_code IS NULL
AND chdt.cl_version_code = 'RELEASE-4';
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'inside AfterRowInsertUpdateDelete1 ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'fund code = '||l_v_fed_fund_code );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'g_v_called_from = '||g_v_called_from );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'old_references.award_status = '||old_references.award_status );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'new_references.award_status = '||new_references.award_status );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'old_references.accepted_amt = '||old_references.accepted_amt );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'new_references.accepted_amt = '||new_references.accepted_amt );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.clchgsnd_id : '||rec_c_sl_clchsn_dtls.clchgsnd_id );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.loan_number_txt : '||rec_c_sl_clchsn_dtls.loan_number_txt );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.change_field_code : '||rec_c_sl_clchsn_dtls.change_field_code );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.change_record_type_txt : '||rec_c_sl_clchsn_dtls.change_record_type_txt );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.change_code_txt : '||rec_c_sl_clchsn_dtls.change_code_txt );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.status_code : '||rec_c_sl_clchsn_dtls.status_code );
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => rec_c_sl_clchsn_dtls.row_id);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Change Send Record deleted successfully ');
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Loan Cancellation. ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_cl_create_chg.create_awd_chg_rec. ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Call to igf_sl_cl_create_chg.create_awd_chg_rec returned error '|| l_v_message_name);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Loan reinstatement/loan increase. ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_cl_create_chg.create_awd_chg_rec. ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Call to igf_sl_cl_create_chg.create_awd_chg_rec returned error '|| l_v_message_name);
END AfterRowInsertUpdateDelete1;
PROCEDURE AfterRowInsertUpdateDelete2(
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
/*-----------------------------------------------------------------
|| Created By : veramach
|| Created On : 16-Nov-2004
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
--------------------------------------------------------------------*/
lv_rowid ROWID;
igf_aw_awd_hist_pkg.insert_row(
x_rowid => lv_rowid,
x_awdh_id => l_awdh_id,
x_award_id => new_references.award_id,
x_tran_date => SYSDATE,
x_operation_txt => 'UPDATE',
x_offered_amt_num => old_references.offered_amt,
x_off_adj_num => (new_references.offered_amt - old_references.offered_amt),
x_accepted_amt_num => old_references.accepted_amt,
x_acc_adj_num => (new_references.accepted_amt - old_references.accepted_amt),
x_paid_amt_num => old_references.paid_amt,
x_paid_adj_num => (new_references.paid_amt - old_references.paid_amt),
x_mode => 'R'
);
END AfterRowInsertUpdateDelete2;
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 igf_aw_award_all
WHERE award_id = x_award_id
FOR UPDATE NOWAIT;
SELECT rowid
FROM igf_aw_award_all
WHERE ((rvsn_id = x_rvsn_id));
SELECT rowid
FROM igf_aw_award_all
WHERE ((fund_id = x_fund_id));
SELECT rowid
FROM igf_aw_award_all
WHERE ((base_id = x_base_id));
SELECT rowid
FROM igf_aw_award_all
WHERE ((adplans_id = x_adplans_id));
x_manual_update IN VARCHAR2 ,
x_rules_override IN VARCHAR2 ,
x_award_date IN DATE ,
x_award_status IN VARCHAR2 ,
x_attribute_category IN VARCHAR2 ,
x_attribute1 IN VARCHAR2 ,
x_attribute2 IN VARCHAR2 ,
x_attribute3 IN VARCHAR2 ,
x_attribute4 IN VARCHAR2 ,
x_attribute5 IN VARCHAR2 ,
x_attribute6 IN VARCHAR2 ,
x_attribute7 IN VARCHAR2 ,
x_attribute8 IN VARCHAR2 ,
x_attribute9 IN VARCHAR2 ,
x_attribute10 IN VARCHAR2 ,
x_attribute11 IN VARCHAR2 ,
x_attribute12 IN VARCHAR2 ,
x_attribute13 IN VARCHAR2 ,
x_attribute14 IN VARCHAR2 ,
x_attribute15 IN VARCHAR2 ,
x_attribute16 IN VARCHAR2 ,
x_attribute17 IN VARCHAR2 ,
x_attribute18 IN VARCHAR2 ,
x_attribute19 IN VARCHAR2 ,
x_attribute20 IN VARCHAR2 ,
x_rvsn_id IN NUMBER ,
x_alt_pell_schedule IN VARCHAR2 ,
x_award_number_txt IN VARCHAR2 ,
x_legacy_record_flag IN VARCHAR2 ,
x_adplans_id IN NUMBER ,
x_lock_award_flag IN VARCHAR2 ,
x_app_trans_num_txt IN VARCHAR2 ,
x_awd_proc_status_code IN VARCHAR2 ,
x_notification_status_code IN VARCHAR2 ,
x_notification_status_date IN DATE ,
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 ,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| 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
|| veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
|| (reverse chronological order - newest change first)
*/
BEGIN
set_column_values (
p_action,
x_rowid,
x_award_id,
x_fund_id,
x_base_id,
x_offered_amt,
x_accepted_amt,
x_paid_amt,
x_packaging_type,
x_batch_id,
x_manual_update,
x_rules_override,
x_award_date,
x_award_status,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_rvsn_id,
x_alt_pell_schedule,
x_award_number_txt,
x_legacy_record_flag,
x_adplans_id,
x_lock_award_flag,
x_app_trans_num_txt,
x_awd_proc_status_code,
x_notification_status_code,
x_notification_status_date,
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.award_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_parent_existance;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
check_child_existance;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation (
new_references.award_id
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_DELETE') THEN
check_child_existance;
Purpose : Updates the award history for an given combination of award id,transaction id and attribute type
Known limitations, enhancements or remarks :
Change History :
Who When What
-------------------------------------
-------------------------------------
(reverse chronological order - newest change first)
*/
PROCEDURE update_award_history AS
CURSOR c_lookup_attribute is
SELECT lookup_code from igf_lookups_view
WHERE lookup_type = 'IGF_AW_AWARD_ATTRIBUTES';
/* If award attributes Change, then insert / update */
IF (l_awd_attr_changed) THEN
igf_aw_award_level_hist_pkg.add_row
(
x_rowid => l_row_id,
x_award_id => old_references.AWARD_ID,
x_award_hist_tran_id => g_award_hist_tran_id,
x_award_attrib_code => l_award_atrr_code,
x_award_change_source_code => g_award_change_source,
x_old_offered_amt => old_references.OFFERED_AMT,
x_new_offered_amt => new_references.OFFERED_AMT,
x_old_accepted_amt => old_references.ACCEPTED_AMT,
x_new_accepted_amt => new_references.ACCEPTED_AMT,
x_old_paid_amt => old_references.PAID_AMT,
x_new_paid_amt => new_references.PAID_AMT,
x_old_lock_award_flag => old_references.LOCK_AWARD_FLAG,
x_new_lock_award_flag => new_references.LOCK_AWARD_FLAG,
x_old_award_status_code => old_references.AWARD_STATUS,
x_new_award_status_code => new_references.AWARD_STATUS,
x_old_adplans_id => old_references.ADPLANS_ID,
x_new_adplans_id => new_references.ADPLANS_ID,
x_mode => 'R'
);
FND_MESSAGE.SET_TOKEN('NAME','igf_aw_award_pkg.update_award_history' || SQLERRM);
END update_award_history;
|| Purpose : Invoke the proceduers related to after update
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
--------------------------------------------------------------------*/
BEGIN
l_rowid := NULL;
IF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.after_dml ', 'before call to AfterRowInsertUpdateDelete1 ' );
AfterRowInsertUpdateDelete1
(
p_inserting => FALSE,
p_updating => TRUE ,
p_deleting => FALSE
);
update_award_history;
AfterRowInsertUpdateDelete2(
p_inserting => FALSE,
p_updating => TRUE ,
p_deleting => FALSE
);
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_award_id IN OUT NOCOPY NUMBER,
x_fund_id IN NUMBER,
x_base_id IN NUMBER,
x_offered_amt IN NUMBER,
x_accepted_amt IN NUMBER,
x_paid_amt IN NUMBER,
x_packaging_type IN VARCHAR2,
x_batch_id IN VARCHAR2,
x_manual_update IN VARCHAR2,
x_rules_override IN VARCHAR2,
x_award_date IN DATE,
x_award_status IN VARCHAR2,
x_attribute_category IN VARCHAR2,
x_attribute1 IN VARCHAR2,
x_attribute2 IN VARCHAR2,
x_attribute3 IN VARCHAR2,
x_attribute4 IN VARCHAR2,
x_attribute5 IN VARCHAR2,
x_attribute6 IN VARCHAR2,
x_attribute7 IN VARCHAR2,
x_attribute8 IN VARCHAR2,
x_attribute9 IN VARCHAR2,
x_attribute10 IN VARCHAR2,
x_attribute11 IN VARCHAR2,
x_attribute12 IN VARCHAR2,
x_attribute13 IN VARCHAR2,
x_attribute14 IN VARCHAR2,
x_attribute15 IN VARCHAR2,
x_attribute16 IN VARCHAR2,
x_attribute17 IN VARCHAR2,
x_attribute18 IN VARCHAR2,
x_attribute19 IN VARCHAR2,
x_attribute20 IN VARCHAR2,
x_rvsn_id IN NUMBER,
x_alt_pell_schedule IN VARCHAR2,
x_mode IN VARCHAR2,
x_award_number_txt IN VARCHAR2,
x_legacy_record_flag IN VARCHAR2,
x_adplans_id IN NUMBER,
x_lock_award_flag IN VARCHAR2,
x_app_trans_num_txt IN VARCHAR2,
x_awd_proc_status_code IN VARCHAR2,
x_notification_status_code IN VARCHAR2,
x_notification_status_date IN DATE,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| Purpose : Handles the INSERT DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
|| (reverse chronological order - newest change first)
*/
CURSOR c IS
SELECT rowid
FROM igf_aw_award_all
WHERE award_id = x_award_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 igf_aw_award_s.nextval INTO x_award_id FROM dual;
p_action => 'INSERT',
x_rowid => x_rowid,
x_award_id => x_award_id,
x_fund_id => x_fund_id,
x_base_id => x_base_id,
x_offered_amt => x_offered_amt,
x_accepted_amt => x_accepted_amt,
x_paid_amt => x_paid_amt,
x_packaging_type => x_packaging_type,
x_batch_id => x_batch_id,
x_manual_update => x_manual_update,
x_rules_override => x_rules_override,
x_award_date => x_award_date,
x_award_status => x_award_status,
x_attribute_category => x_attribute_category,
x_attribute1 => x_attribute1,
x_attribute2 => x_attribute2,
x_attribute3 => x_attribute3,
x_attribute4 => x_attribute4,
x_attribute5 => x_attribute5,
x_attribute6 => x_attribute6,
x_attribute7 => x_attribute7,
x_attribute8 => x_attribute8,
x_attribute9 => x_attribute9,
x_attribute10 => x_attribute10,
x_attribute11 => x_attribute11,
x_attribute12 => x_attribute12,
x_attribute13 => x_attribute13,
x_attribute14 => x_attribute14,
x_attribute15 => x_attribute15,
x_attribute16 => x_attribute16,
x_attribute17 => x_attribute17,
x_attribute18 => x_attribute18,
x_attribute19 => x_attribute19,
x_attribute20 => x_attribute20,
x_rvsn_id => x_rvsn_id,
x_alt_pell_schedule => x_alt_pell_schedule,
x_award_number_txt => x_award_number_txt,
x_legacy_record_flag => x_legacy_record_flag,
x_adplans_id => x_adplans_id,
x_lock_award_flag => x_lock_award_flag,
x_app_trans_num_txt => x_app_trans_num_txt,
x_awd_proc_status_code => x_awd_proc_status_code,
x_notification_status_code => x_notification_status_code,
x_notification_status_date => x_notification_status_date,
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_aw_award_all (
award_id,
fund_id,
base_id,
offered_amt,
accepted_amt,
paid_amt,
packaging_type,
batch_id,
manual_update,
rules_override,
award_date,
award_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
rvsn_id,
alt_pell_schedule,
award_number_txt,
legacy_record_flag,
adplans_id,
lock_award_flag,
app_trans_num_txt,
awd_proc_status_code,
notification_status_code,
notification_status_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
org_id,
publish_in_ss_flag
) VALUES (
new_references.award_id,
new_references.fund_id,
new_references.base_id,
new_references.offered_amt,
new_references.accepted_amt,
new_references.paid_amt,
new_references.packaging_type,
new_references.batch_id,
new_references.manual_update,
new_references.rules_override,
new_references.award_date,
new_references.award_status,
new_references.attribute_category,
new_references.attribute1,
new_references.attribute2,
new_references.attribute3,
new_references.attribute4,
new_references.attribute5,
new_references.attribute6,
new_references.attribute7,
new_references.attribute8,
new_references.attribute9,
new_references.attribute10,
new_references.attribute11,
new_references.attribute12,
new_references.attribute13,
new_references.attribute14,
new_references.attribute15,
new_references.attribute16,
new_references.attribute17,
new_references.attribute18,
new_references.attribute19,
new_references.attribute20,
new_references.rvsn_id,
new_references.alt_pell_schedule,
new_references.award_number_txt,
new_references.legacy_record_flag,
new_references.adplans_id,
new_references.lock_award_flag,
new_references.app_trans_num_txt,
new_references.awd_proc_status_code,
new_references.notification_status_code,
new_references.notification_status_date,
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 ,
l_org_id,
new_references.publish_in_ss_flag
);
/* Update Fund Master */
--
-- Bug 3029739
-- As the variable old_referecnes is a package
-- variable it is retaining the old value
-- For insert routine, we need not have this value so it
-- is being replaced with new_ref
-- This is done so that the following routine would
-- correctly update fund manager totals
--
igf_aw_gen.update_fmast( new_references,
new_references,
'INSERT'
) ;
FND_MESSAGE.SET_TOKEN('NAME','igf_aw_award_pkg.insert_row' || SQLERRM);
END insert_row;
x_manual_update IN VARCHAR2,
x_rules_override IN VARCHAR2,
x_award_date IN DATE,
x_award_status IN VARCHAR2,
x_attribute_category IN VARCHAR2,
x_attribute1 IN VARCHAR2,
x_attribute2 IN VARCHAR2,
x_attribute3 IN VARCHAR2,
x_attribute4 IN VARCHAR2,
x_attribute5 IN VARCHAR2,
x_attribute6 IN VARCHAR2,
x_attribute7 IN VARCHAR2,
x_attribute8 IN VARCHAR2,
x_attribute9 IN VARCHAR2,
x_attribute10 IN VARCHAR2,
x_attribute11 IN VARCHAR2,
x_attribute12 IN VARCHAR2,
x_attribute13 IN VARCHAR2,
x_attribute14 IN VARCHAR2,
x_attribute15 IN VARCHAR2,
x_attribute16 IN VARCHAR2,
x_attribute17 IN VARCHAR2,
x_attribute18 IN VARCHAR2,
x_attribute19 IN VARCHAR2,
x_attribute20 IN VARCHAR2,
x_rvsn_id IN NUMBER,
x_alt_pell_schedule IN VARCHAR2,
x_award_number_txt IN VARCHAR2,
x_legacy_record_flag IN VARCHAR2,
x_adplans_id IN NUMBER,
x_lock_award_flag IN VARCHAR2,
x_app_trans_num_txt IN VARCHAR2,
x_awd_proc_status_code IN VARCHAR2,
x_notification_status_code IN VARCHAR2,
x_notification_status_date IN DATE,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| Purpose : Handles the LOCK mechanism for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
|| (reverse chronological order - newest change first)
*/
CURSOR c1 IS
SELECT
fund_id,
base_id,
offered_amt,
accepted_amt,
paid_amt,
packaging_type,
batch_id,
manual_update,
rules_override,
award_date,
award_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
rvsn_id,
alt_pell_schedule,
award_number_txt,
legacy_record_flag,
adplans_id,
lock_award_flag,
app_trans_num_txt,
awd_proc_status_code,
notification_status_code,
notification_status_date,
publish_in_ss_flag
FROM igf_aw_award_all
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
AND ((tlinfo.manual_update = x_manual_update) OR ((tlinfo.manual_update IS NULL) AND (X_manual_update IS NULL)))
AND ((tlinfo.rules_override = x_rules_override) OR ((tlinfo.rules_override IS NULL) AND (X_rules_override IS NULL)))
AND ((tlinfo.award_date = x_award_date) OR ((tlinfo.award_date IS NULL) AND (X_award_date IS NULL)))
AND ((tlinfo.award_status = x_award_status) OR ((tlinfo.award_status IS NULL) AND (X_award_status IS NULL)))
AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
AND ((tlinfo.rvsn_id = x_rvsn_id) OR ((tlinfo.rvsn_id IS NULL) AND (x_rvsn_id IS NULL)))
AND ((tlinfo.alt_pell_schedule = x_alt_pell_schedule) OR ((tlinfo.alt_pell_schedule IS NULL) AND (x_alt_pell_schedule IS NULL)))
AND ((tlinfo.award_number_txt = x_award_number_txt) OR ((tlinfo.award_number_txt IS NULL) AND (x_award_number_txt IS NULL)))
AND ((tlinfo.legacy_record_flag = x_legacy_record_flag) OR ((tlinfo.legacy_record_flag IS NULL) AND (x_legacy_record_flag IS NULL)))
AND ((tlinfo.adplans_id = x_adplans_id) OR ((tlinfo.adplans_id IS NULL) AND (x_adplans_id IS NULL)))
AND ((tlinfo.lock_award_flag = x_lock_award_flag) OR ((tlinfo.lock_award_flag IS NULL ) AND (x_lock_award_flag IS NULL)))
AND ((tlinfo.app_trans_num_txt = x_app_trans_num_txt) OR ((tlinfo.app_trans_num_txt IS NULL ) AND (x_app_trans_num_txt IS NULL)))
AND ((tlinfo.awd_proc_status_code = x_awd_proc_status_code) OR ((tlinfo.awd_proc_status_code IS NULL ) AND (x_awd_proc_status_code IS NULL)))
AND ((tlinfo.notification_status_code= x_notification_status_code) OR ((tlinfo.notification_status_code IS NULL ) AND (x_notification_status_code IS NULL)))
AND ((tlinfo.notification_status_date= x_notification_status_date) OR ((tlinfo.notification_status_date IS NULL ) AND (x_notification_status_date IS NULL)))
AND ((tlinfo.publish_in_ss_flag = x_publish_in_ss_flag) OR ((tlinfo.publish_in_ss_flag IS NULL ) AND (x_publish_in_ss_flag IS NULL)))
) THEN
NULL;
SELECT lookup_code from igf_lookups_view
WHERE lookup_type = 'IGF_AW_AWARD_ATTRIBUTES';
SELECT IGF_AW_AWARD_LEVEL_HIST_S.NEXTVAL INTO g_award_hist_tran_id from dual;
PROCEDURE update_row (
x_rowid IN VARCHAR2,
x_award_id IN NUMBER,
x_fund_id IN NUMBER,
x_base_id IN NUMBER,
x_offered_amt IN NUMBER,
x_accepted_amt IN NUMBER,
x_paid_amt IN NUMBER,
x_packaging_type IN VARCHAR2,
x_batch_id IN VARCHAR2,
x_manual_update IN VARCHAR2,
x_rules_override IN VARCHAR2,
x_award_date IN DATE,
x_award_status IN VARCHAR2,
x_attribute_category IN VARCHAR2,
x_attribute1 IN VARCHAR2,
x_attribute2 IN VARCHAR2,
x_attribute3 IN VARCHAR2,
x_attribute4 IN VARCHAR2,
x_attribute5 IN VARCHAR2,
x_attribute6 IN VARCHAR2,
x_attribute7 IN VARCHAR2,
x_attribute8 IN VARCHAR2,
x_attribute9 IN VARCHAR2,
x_attribute10 IN VARCHAR2,
x_attribute11 IN VARCHAR2,
x_attribute12 IN VARCHAR2,
x_attribute13 IN VARCHAR2,
x_attribute14 IN VARCHAR2,
x_attribute15 IN VARCHAR2,
x_attribute16 IN VARCHAR2,
x_attribute17 IN VARCHAR2,
x_attribute18 IN VARCHAR2,
x_attribute19 IN VARCHAR2,
x_attribute20 IN VARCHAR2,
x_rvsn_id IN NUMBER,
x_alt_pell_schedule IN VARCHAR2,
x_mode IN VARCHAR2,
x_award_number_txt IN VARCHAR2,
x_legacy_record_flag IN VARCHAR2,
x_adplans_id IN NUMBER,
x_lock_award_flag IN VARCHAR2,
x_app_trans_num_txt IN VARCHAR2,
x_awd_proc_status_code IN VARCHAR2,
x_notification_status_code IN VARCHAR2,
x_notification_status_date IN DATE,
x_called_from IN VARCHAR2,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| Purpose : Handles the UPDATE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
|| (reverse chronological order - newest change first)
*/
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_award_id => x_award_id,
x_fund_id => x_fund_id,
x_base_id => x_base_id,
x_offered_amt => x_offered_amt,
x_accepted_amt => x_accepted_amt,
x_paid_amt => x_paid_amt,
x_packaging_type => x_packaging_type,
x_batch_id => x_batch_id,
x_manual_update => x_manual_update,
x_rules_override => x_rules_override,
x_award_date => x_award_date,
x_award_status => x_award_status,
x_attribute_category => x_attribute_category,
x_attribute1 => x_attribute1,
x_attribute2 => x_attribute2,
x_attribute3 => x_attribute3,
x_attribute4 => x_attribute4,
x_attribute5 => x_attribute5,
x_attribute6 => x_attribute6,
x_attribute7 => x_attribute7,
x_attribute8 => x_attribute8,
x_attribute9 => x_attribute9,
x_attribute10 => x_attribute10,
x_attribute11 => x_attribute11,
x_attribute12 => x_attribute12,
x_attribute13 => x_attribute13,
x_attribute14 => x_attribute14,
x_attribute15 => x_attribute15,
x_attribute16 => x_attribute16,
x_attribute17 => x_attribute17,
x_attribute18 => x_attribute18,
x_attribute19 => x_attribute19,
x_attribute20 => x_attribute20,
x_rvsn_id => x_rvsn_id,
x_alt_pell_schedule => x_alt_pell_schedule,
x_award_number_txt => x_award_number_txt,
x_legacy_record_flag => x_legacy_record_flag,
x_adplans_id => x_adplans_id,
x_lock_award_flag => x_lock_award_flag,
x_app_trans_num_txt => x_app_trans_num_txt,
x_awd_proc_status_code => x_awd_proc_status_code,
x_notification_status_code => x_notification_status_code,
x_notification_status_date => x_notification_status_date,
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
);
x_program_update_date := old_references.program_update_date;
x_program_update_date := SYSDATE;
UPDATE igf_aw_award_all
SET
fund_id = new_references.fund_id,
base_id = new_references.base_id,
offered_amt = new_references.offered_amt,
accepted_amt = new_references.accepted_amt,
paid_amt = new_references.paid_amt,
packaging_type = new_references.packaging_type,
batch_id = new_references.batch_id,
manual_update = new_references.manual_update,
rules_override = new_references.rules_override,
award_date = new_references.award_date,
award_status = new_references.award_status,
attribute_category = new_references.attribute_category,
attribute1 = new_references.attribute1,
attribute2 = new_references.attribute2,
attribute3 = new_references.attribute3,
attribute4 = new_references.attribute4,
attribute5 = new_references.attribute5,
attribute6 = new_references.attribute6,
attribute7 = new_references.attribute7,
attribute8 = new_references.attribute8,
attribute9 = new_references.attribute9,
attribute10 = new_references.attribute10,
attribute11 = new_references.attribute11,
attribute12 = new_references.attribute12,
attribute13 = new_references.attribute13,
attribute14 = new_references.attribute14,
attribute15 = new_references.attribute15,
attribute16 = new_references.attribute16,
attribute17 = new_references.attribute17,
attribute18 = new_references.attribute18,
attribute19 = new_references.attribute19,
attribute20 = new_references.attribute20,
rvsn_id = new_references.rvsn_id,
alt_pell_schedule = new_references.alt_pell_schedule,
award_number_txt = new_references.award_number_txt,
legacy_record_flag = new_references.legacy_record_flag,
adplans_id = new_references.adplans_id,
lock_award_flag = new_references.lock_award_flag,
app_trans_num_txt = new_references.app_trans_num_txt,
awd_proc_status_code = new_references.awd_proc_status_code,
notification_status_code = new_references.notification_status_code,
notification_status_date = new_references.notification_status_date,
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,
publish_in_ss_flag = new_references.publish_in_ss_flag
WHERE rowid = x_rowid;
igf_aw_gen.update_fmast( old_references,
new_references,
'UPDATE'
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.update_row ', 'g_v_called_from '||g_v_called_from );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.update_row ', 'before invoking after_dml ' );
p_action =>'UPDATE',
x_rowid => x_rowid
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.update_row ', 'after invoking after_dml ' );
END update_row;
x_manual_update IN VARCHAR2,
x_rules_override IN VARCHAR2,
x_award_date IN DATE,
x_award_status IN VARCHAR2,
x_attribute_category IN VARCHAR2,
x_attribute1 IN VARCHAR2,
x_attribute2 IN VARCHAR2,
x_attribute3 IN VARCHAR2,
x_attribute4 IN VARCHAR2,
x_attribute5 IN VARCHAR2,
x_attribute6 IN VARCHAR2,
x_attribute7 IN VARCHAR2,
x_attribute8 IN VARCHAR2,
x_attribute9 IN VARCHAR2,
x_attribute10 IN VARCHAR2,
x_attribute11 IN VARCHAR2,
x_attribute12 IN VARCHAR2,
x_attribute13 IN VARCHAR2,
x_attribute14 IN VARCHAR2,
x_attribute15 IN VARCHAR2,
x_attribute16 IN VARCHAR2,
x_attribute17 IN VARCHAR2,
x_attribute18 IN VARCHAR2,
x_attribute19 IN VARCHAR2,
x_attribute20 IN VARCHAR2,
x_rvsn_id IN NUMBER,
x_alt_pell_schedule IN VARCHAR2,
x_mode IN VARCHAR2,
x_award_number_txt IN VARCHAR2,
x_legacy_record_flag IN VARCHAR2,
x_adplans_id IN NUMBER,
x_lock_award_flag IN VARCHAR2,
x_app_trans_num_txt IN VARCHAR2,
x_awd_proc_status_code IN VARCHAR2,
x_notification_status_code IN VARCHAR2,
x_notification_status_date IN DATE,
x_publish_in_ss_flag IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| 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
|| veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure call
|| (reverse chronological order - newest change first)
*/
CURSOR c1 IS
SELECT rowid
FROM igf_aw_award_all
WHERE award_id = x_award_id;
insert_row (
x_rowid,
x_award_id,
x_fund_id,
x_base_id,
x_offered_amt,
x_accepted_amt,
x_paid_amt,
x_packaging_type,
x_batch_id,
x_manual_update,
x_rules_override,
x_award_date,
x_award_status,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_rvsn_id,
x_alt_pell_schedule,
x_mode,
x_award_number_txt,
x_legacy_record_flag,
x_adplans_id,
x_lock_award_flag,
x_app_trans_num_txt,
x_awd_proc_status_code,
x_notification_status_code,
x_notification_status_date,
x_publish_in_ss_flag
);
update_row (
x_rowid,
x_award_id,
x_fund_id,
x_base_id,
x_offered_amt,
x_accepted_amt,
x_paid_amt,
x_packaging_type,
x_batch_id,
x_manual_update,
x_rules_override,
x_award_date,
x_award_status,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_rvsn_id,
x_alt_pell_schedule,
x_mode,
x_award_number_txt,
x_legacy_record_flag,
x_adplans_id,
x_lock_award_flag,
x_app_trans_num_txt,
x_awd_proc_status_code,
x_notification_status_code,
x_notification_status_date,
x_publish_in_ss_flag
);
PROCEDURE delete_row (
x_rowid IN VARCHAR2
) AS
/*
|| Created By : avenkatr
|| Created On : 06-DEC-2000
|| 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_aw_award_all
WHERE rowid = x_rowid;
igf_aw_gen.update_fmast( old_references,
new_references,
'DELETE'
) ;
END delete_row;