[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE AfterRowInsertUpdateDelete1(
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
);
PROCEDURE BeforeRowInsertUpdateDelete1(
p_rowid IN VARCHAR2,
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
);
|| in procedure igf_aw_db_chg_dtls_pkg.insert_row
--------------------------------------------------------------*/
CURSOR award_year_cal_cur (cp_award_id IGF_AW_AWD_DISB_ALL.award_id%TYPE) IS
SELECT fmast.ci_cal_type,fmast.ci_sequence_number, awd.award_status
FROM IGF_AW_AWARD_ALL awd,
IGF_AW_FUND_MAST fmast
WHERE awd.award_id = cp_award_id
AND awd.fund_id = fmast.fund_id;
SELECT dbchgdtls.ROWID,dbchgdtls.*
FROM igf_aw_db_chg_dtls dbchgdtls
WHERE dbchgdtls.award_id = cp_award_id
AND dbchgdtls.disb_num = cp_disb_num
AND dbchgdtls.disb_seq_num = cp_disb_seq_num;
SELECT max(dbchgdtls.disb_seq_num)+1
FROM igf_aw_db_chg_dtls dbchgdtls
WHERE dbchgdtls.award_id = cp_award_id
AND dbchgdtls.disb_num = cp_disb_num;
SELECT sl.ROWID, sl.*
FROM IGF_SL_LOANS_ALL sl
WHERE sl.award_id = cp_award_id;
SELECT pell.*, pell.ROWID
FROM igf_gr_rfms_all pell
WHERE pell.award_id = cp_award_id;
SELECT min(disb_date)
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id;
SELECT chgdtls.*
FROM IGF_AW_DB_CHG_DTLS chgdtls
WHERE chgdtls.award_id = cp_award_id
AND chgdtls.disb_num = cp_disb_num
AND chgdtls.disb_status = 'A'
AND (chgdtls.disb_activity = 'P' OR chgdtls.disb_activity = cp_disb_activity)
ORDER BY chgdtls.disb_seq_num DESC;
CURSOR cur_latest_DateAmt_for_update ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE,
cp_disb_num igf_aw_db_chg_dtls.disb_num%TYPE,
cp_disb_activity igf_aw_db_chg_dtls.disb_activity%TYPE) IS
SELECT chgdtls.ROWID, chgdtls.*
FROM IGF_AW_DB_CHG_DTLS chgdtls
WHERE chgdtls.award_id = cp_award_id
AND chgdtls.disb_num = cp_disb_num
AND chgdtls.disb_status <> 'A'
AND chgdtls.disb_activity = cp_disb_activity
ORDER BY chgdtls.disb_seq_num DESC;
latest_update_rec cur_latest_DateAmt_for_update%ROWTYPE;
CURSOR cur_updated_award_amount (cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
SELECT SUM(awddisb.DISB_NET_AMT) total_amt
FROM IGF_AW_AWD_DISB_ALL awddisb
WHERE awddisb.award_id = cp_award_id;
updated_award_amount cur_updated_award_amount%ROWTYPE;
IF (p_action = 'UPDATE') THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.after_dml ', 'action = update ' );
AfterRowInsertUpdateDelete1
(
p_inserting => FALSE,
p_updating => TRUE ,
p_deleting => FALSE
);
ELSIF (p_action = 'INSERT') THEN
-- Call all the procedures related to After insert
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.after_dml ', 'action = insert ' );
AfterRowInsertUpdateDelete1
(
p_inserting => TRUE,
p_updating => FALSE ,
p_deleting => FALSE
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' Before update in RFMS ' );
OPEN cur_updated_award_amount(new_references.award_id);
FETCH cur_updated_award_amount INTO updated_award_amount;
CLOSE cur_updated_award_amount;
igf_gr_rfms_pkg.update_row(
x_rowid => pell_rec.rowid,
x_origination_id => pell_rec.origination_id,
x_ci_cal_type => pell_rec.ci_cal_type,
x_ci_sequence_number => pell_rec.ci_sequence_number,
x_base_id => pell_rec.base_id,
x_award_id => pell_rec.award_id,
x_rfmb_id => pell_rec.rfmb_id,
x_sys_orig_ssn => pell_rec.sys_orig_ssn,
x_sys_orig_name_cd => pell_rec.sys_orig_name_cd,
x_transaction_num => pell_rec.transaction_num,
x_efc => pell_rec.efc,
x_ver_status_code => pell_rec.ver_status_code,
x_secondary_efc => pell_rec.secondary_efc,
x_secondary_efc_cd => pell_rec.secondary_efc_cd,
x_pell_amount => updated_award_amount.total_amt,
x_pell_profile => pell_rec.pell_profile,
x_enrollment_status => pell_rec.enrollment_status,
x_enrollment_dt => pell_rec.enrollment_dt,
x_coa_amount => pell_rec.coa_amount,
x_academic_calendar => pell_rec.academic_calendar,
x_payment_method => pell_rec.payment_method,
x_total_pymt_prds => pell_rec.total_pymt_prds,
x_incrcd_fed_pell_rcp_cd => pell_rec.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => pell_rec.attending_campus_id,
x_est_disb_dt1 => pell_rec.est_disb_dt1,
x_orig_action_code => 'R', -- ready to send
x_orig_status_dt => TRUNC(SYSDATE),
x_orig_ed_use_flags => pell_rec.orig_ed_use_flags,
x_ft_pell_amount => pell_rec.ft_pell_amount,
x_prev_accpt_efc => pell_rec.prev_accpt_efc,
x_prev_accpt_tran_no => pell_rec.prev_accpt_tran_no,
x_prev_accpt_sec_efc_cd => pell_rec.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => pell_rec.prev_accpt_coa,
x_orig_reject_code => pell_rec.orig_reject_code,
x_wk_inst_time_calc_pymt => pell_rec.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => pell_rec.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => pell_rec.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => pell_rec.cr_clk_hrs_acad_yr,
x_inst_cross_ref_cd => pell_rec.inst_cross_ref_cd,
x_low_tution_fee => pell_rec.low_tution_fee,
x_rec_source => pell_rec.rec_source,
x_pending_amount => pell_rec.pending_amount,
x_mode => 'R',
x_birth_dt => pell_rec.birth_dt,
x_last_name => pell_rec.last_name,
x_first_name => pell_rec.first_name,
x_middle_name => pell_rec.middle_name,
x_current_ssn => pell_rec.current_ssn,
x_legacy_record_flag => pell_rec.legacy_record_flag,
x_reporting_pell_cd => pell_rec.reporting_pell_cd,
x_rep_entity_id_txt => pell_rec.rep_entity_id_txt,
x_atd_entity_id_txt => pell_rec.atd_entity_id_txt,
x_note_message => pell_rec.note_message,
x_full_resp_code => pell_rec.full_resp_code,
x_document_id_txt => pell_rec.document_id_txt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' updated rfms table ' );
IF p_action = 'INSERT' THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' before insert of change rec 1 ' );
igf_aw_db_chg_dtls_pkg.insert_row(
x_rowid => l_row_id,
x_award_id => new_references.award_id,
x_disb_num => new_references.disb_num,
x_disb_seq_num => 1,
x_disb_accepted_amt => NVL(new_references.disb_accepted_amt,0),
x_orig_fee_amt => new_references.fee_1,
x_disb_net_amt => new_references.disb_net_amt,
x_disb_date => new_references.disb_date,
x_disb_activity => 'P',
x_disb_status => 'G',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => NVL(new_references.hold_rel_ind,'FALSE'),
x_first_disb_flag => l_first_disb_flag,
x_interest_rebate_amt => new_references.int_rebate_amt,
x_disb_conf_flag => new_references.affirm_flag,
x_pymnt_prd_start_date => new_references.payment_prd_st_date,
x_note_message => NULL,
x_batch_id_txt => NULL,
x_ack_date => NULL,
x_booking_id_txt => NULL,
x_booking_date => NULL,
x_mode => 'R');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' after insert of change rec 1 ' );
ELSIF (p_action = 'UPDATE') THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','p_action= ' ||p_action);
- DRI is FALSE. Since DRI is FALSE, existing disb sequence needs to be updated, no question of insert.
- Disb amount/Disb date has got changed (or) DRI has changed from FALSE to TRUE.
l_change_value = 2
- DRI is TRUE. Insert/Update of disb sequence.
- Disb amount/Disb date has got changed.
If Disb amount has changed, chk if there exists a disb sequence (in desc order of disb seq)
of disb_activity type 'A' in not Accepted status. If it exists, update this disb seq,
else insert new disb sequence with the new disb amount.
Same logic holds good for Disb date change (disb_activity = 'Q')
Note: Code clean up is done only for Full-Participant. The new code doesn't look for Phase-in
participant assuming that Schools can't be in Phase-in Participant. For Phase-in participant
l_change_value remains 0 and no change is done to the disb change records.
*/
IF l_cod_year_flag THEN
IF (NVL(new_references.hold_rel_ind, 'FALSE') = 'TRUE' AND NVL(old_references.hold_rel_ind, 'FALSE') = 'FALSE') THEN
-- DRI changed from FALSE to TRUE. Update disb sequence
l_change_value := 1;
igf_aw_db_chg_dtls_pkg.update_row (
x_rowid => upd_db_chg_dtls_rec.ROWID,
x_award_id => upd_db_chg_dtls_rec.award_id,
x_disb_num => upd_db_chg_dtls_rec.disb_num,
x_disb_seq_num => upd_db_chg_dtls_rec.disb_seq_num,
x_disb_accepted_amt => new_references.disb_accepted_amt,
x_orig_fee_amt => new_references.fee_1,
x_disb_net_amt => new_references.disb_net_amt,
x_disb_date => new_references.disb_date,
x_disb_activity => upd_db_chg_dtls_rec.disb_activity,
x_disb_status => 'G',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
x_first_disb_flag => l_first_disb_flag,
x_interest_rebate_amt => new_references.int_rebate_amt,
x_disb_conf_flag => new_references.affirm_flag,
x_pymnt_prd_start_date => new_references.payment_prd_st_date,
x_note_message => upd_db_chg_dtls_rec.note_message,
x_batch_id_txt => upd_db_chg_dtls_rec.batch_id_txt,
x_ack_date => upd_db_chg_dtls_rec.ack_date,
x_booking_id_txt => upd_db_chg_dtls_rec.booking_id_txt,
x_booking_date => upd_db_chg_dtls_rec.booking_date,
x_mode => 'R'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' after update of change rec disb status chg true 1 ' );
OPEN cur_latest_DateAmt_for_update(new_references.award_id, new_references.disb_num, 'A');
FETCH cur_latest_DateAmt_for_update INTO latest_update_rec;
CLOSE cur_latest_DateAmt_for_update;
IF latest_update_rec.award_id IS NOT NULL THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,
'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',
'Updating disb sequence ' ||latest_update_rec.disb_seq_num|| ' with the new disb amount');
igf_aw_db_chg_dtls_pkg.update_row (
x_rowid => latest_update_rec.ROWID,
x_award_id => latest_update_rec.award_id,
x_disb_num => latest_update_rec.disb_num,
x_disb_seq_num => latest_update_rec.disb_seq_num,
x_disb_accepted_amt => new_references.disb_accepted_amt,
x_orig_fee_amt => new_references.fee_1,
x_disb_net_amt => new_references.disb_net_amt,
x_disb_date => latest_accepted_DateAmount_rec.disb_date,
x_disb_activity => 'A',
x_disb_status => 'G',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
x_first_disb_flag => l_first_disb_flag,
x_interest_rebate_amt => new_references.int_rebate_amt,
x_disb_conf_flag => new_references.affirm_flag,
x_pymnt_prd_start_date => new_references.payment_prd_st_date,
x_note_message => latest_update_rec.note_message,
x_batch_id_txt => latest_update_rec.batch_id_txt,
x_ack_date => latest_update_rec.ack_date,
x_booking_id_txt => latest_update_rec.booking_id_txt,
x_booking_date => latest_update_rec.booking_date,
x_mode => 'R'
);
'Inserting new disb sequence ' ||NVL(l_max_disb_seq_num,1)|| ' with the new disb amount');
igf_aw_db_chg_dtls_pkg.insert_row(
x_rowid => l_row_id,
x_award_id => new_references.award_id,
x_disb_num => new_references.disb_num,
x_disb_seq_num => NVL(l_max_disb_seq_num,1),
x_disb_accepted_amt => new_references.disb_accepted_amt,
x_orig_fee_amt => new_references.fee_1,
x_disb_net_amt => new_references.disb_net_amt,
x_disb_date => latest_accepted_DateAmount_rec.disb_date,
x_disb_activity => 'A',
x_disb_status => 'G',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
x_first_disb_flag => l_first_disb_flag,
x_interest_rebate_amt => new_references.int_rebate_amt,
x_disb_conf_flag => new_references.affirm_flag,
x_pymnt_prd_start_date => new_references.payment_prd_st_date,
x_note_message => NULL,
x_batch_id_txt => NULL,
x_ack_date => NULL,
x_booking_id_txt => NULL,
x_booking_date => NULL,
x_mode => 'R'
);
END IF; -- for either Update/Insert
OPEN cur_latest_DateAmt_for_update(new_references.award_id, new_references.disb_num, 'Q');
FETCH cur_latest_DateAmt_for_update INTO latest_update_rec;
CLOSE cur_latest_DateAmt_for_update;
IF latest_update_rec.award_id IS NOT NULL THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,
'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',
'Updating disb sequence ' ||latest_update_rec.disb_seq_num|| ' with the new disb date');
igf_aw_db_chg_dtls_pkg.update_row (
x_rowid => latest_update_rec.ROWID,
x_award_id => latest_update_rec.award_id,
x_disb_num => latest_update_rec.disb_num,
x_disb_seq_num => latest_update_rec.disb_seq_num,
x_disb_accepted_amt => latest_accepted_DateAmount_rec.disb_accepted_amt,
x_orig_fee_amt => latest_accepted_DateAmount_rec.orig_fee_amt,
x_disb_net_amt => latest_accepted_DateAmount_rec.disb_net_amt,
x_disb_date => new_references.disb_date,
x_disb_activity => 'Q',
x_disb_status => 'G',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
x_first_disb_flag => l_first_disb_flag,
x_interest_rebate_amt => latest_accepted_DateAmount_rec.interest_rebate_amt,
x_disb_conf_flag => latest_accepted_DateAmount_rec.disb_conf_flag,
x_pymnt_prd_start_date => latest_accepted_DateAmount_rec.pymnt_prd_start_date,
x_note_message => latest_update_rec.note_message,
x_batch_id_txt => latest_update_rec.batch_id_txt,
x_ack_date => latest_update_rec.ack_date,
x_booking_id_txt => latest_update_rec.booking_id_txt,
x_booking_date => latest_update_rec.booking_date,
x_mode => 'R'
);
'Inserting new disb sequence ' ||NVL(l_max_disb_seq_num,1)|| ' with the new disb date');
igf_aw_db_chg_dtls_pkg.insert_row(
x_rowid => l_row_id,
x_award_id => new_references.award_id,
x_disb_num => new_references.disb_num,
x_disb_seq_num => NVL(l_max_disb_seq_num,1),
x_disb_accepted_amt => latest_accepted_DateAmount_rec.disb_accepted_amt,
x_orig_fee_amt => latest_accepted_DateAmount_rec.orig_fee_amt,
x_disb_net_amt => latest_accepted_DateAmount_rec.disb_net_amt,
x_disb_date => new_references.disb_date,
x_disb_activity => 'Q',
x_disb_status => 'G',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
x_first_disb_flag => l_first_disb_flag,
x_interest_rebate_amt => latest_accepted_DateAmount_rec.interest_rebate_amt,
x_disb_conf_flag => latest_accepted_DateAmount_rec.disb_conf_flag,
x_pymnt_prd_start_date => latest_accepted_DateAmount_rec.pymnt_prd_start_date,
x_note_message => NULL,
x_batch_id_txt => NULL,
x_ack_date => NULL,
x_booking_id_txt => NULL,
x_booking_date => NULL,
x_mode => 'R'
);
END IF; -- for either Update/Insert
igf_sl_loans_pkg.update_row(
x_rowid => loans_rec.rowid,
x_loan_id => loans_rec.loan_id,
x_award_id => loans_rec.award_id,
x_seq_num => loans_rec.seq_num,
x_loan_number => loans_rec.loan_number,
x_loan_per_begin_date => loans_rec.loan_per_begin_date,
x_loan_per_end_date => loans_rec.loan_per_end_date,
x_loan_status => loans_rec.loan_status,
x_loan_status_date => loans_rec.loan_status_date,
x_loan_chg_status => loans_rec.loan_chg_status,
x_loan_chg_status_date => loans_rec.loan_chg_status_date,
x_active => loans_rec.active,
x_active_date => loans_rec.active_date,
x_borw_detrm_code => loans_rec.borw_detrm_code,
x_mode => 'R',
x_legacy_record_flag => loans_rec.legacy_record_flag,
x_external_loan_id_txt => loans_rec.external_loan_id_txt,
x_called_from => NULL
);
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL,
x_payment_prd_st_date IN DATE DEFAULT NULL,
x_change_type_code IN VARCHAR2 DEFAULT NULL,
x_fund_return_mthd_code IN VARCHAR2 DEFAULT NULL,
x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
) AS
/*
|| Created By : adhawan
|| Created On : 16-NOV-2000
|| Purpose : Initialises the Old and New references for the columns of the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
|| addded a new column
|| veramach 3-NOV-2003 FA 125 Multiple Distr Methods
|| Added attendance_type_code to the signature
|| (reverse chronological order - newest change first)
*/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGF_AW_AWD_DISB_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;
PROCEDURE BeforeRowInsertUpdateDelete1(
p_rowid IN VARCHAR2,
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
/*-----------------------------------------------------------------
|| Created By : Sanil Madathil
|| Created On : 24-Nov-2004
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
--------------------------------------------------------------------*/
CURSOR c_aw_awd_disb (cp_rowid ROWID) IS
SELECT award_id
,disb_num
FROM igf_aw_awd_disb
WHERE row_id = cp_rowid;
SELECT lar.loan_number
,lar.loan_status
,lor.prc_type_code
,lor.cl_rec_status
FROM igf_sl_lor_all lor
,igf_sl_loans_all lar
WHERE lor.loan_id = lar.loan_id
AND lar.award_id = cp_n_award_id;
SELECT chdt.ROWID row_id,chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE chdt.loan_number_txt = cp_v_loan_number
AND chdt.disbursement_number = cp_new_disb_num
AND chdt.status_code IN ('R','N','D')
AND chdt.change_field_code = 'DISB_NUM'
AND chdt.change_code_txt = 'D'
AND chdt.change_record_type_txt = '09';
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'Action = delete ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'inside BeforeRowInsertUpdateDelete1 ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_v_fed_fund_code : '||l_v_fed_fund_code );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'g_v_called_from : '||g_v_called_from );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_n_cl_version : '||l_n_cl_version );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_v_loan_status : '||l_v_loan_status );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_v_prc_type_code : '||l_v_prc_type_code );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_c_cl_rec_status : '||l_c_cl_rec_status );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_n_disb_num : '||l_n_disb_num );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', ' Change record to be deleted ');
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_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', ' Change record deleted successfully ');
END BeforeRowInsertUpdateDelete1;
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)
--------------------------------------------------------------------*/
CURSOR c_igf_sl_lorlar(cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
SELECT lar.loan_number
,lar.loan_status
,lor.prc_type_code
,lor.cl_rec_status
FROM igf_sl_lor_all lor
,igf_sl_loans_all lar
WHERE lor.loan_id = lar.loan_id
AND lar.award_id = cp_n_award_id;
SELECT chdt.ROWID row_id,chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE chdt.loan_number_txt = cp_v_loan_number
AND chdt.disbursement_number = cp_new_disb_num
AND chdt.status_code IN ('R','N','D')
AND chdt.change_field_code = 'DISB_NUM'
AND chdt.change_code_txt = 'D'
AND chdt.change_record_type_txt = '09';
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'inside AfterRowInsertUpdateDelete1 ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' fund code = '||l_v_fed_fund_code );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' called from = '||g_v_called_from );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_cl_create_chg.create_disb_chg_rec. ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'Call to igf_sl_cl_create_chg.create_disb_chg_rec returned error '|| l_v_message_name);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_n_cl_version : '||l_n_cl_version );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_v_loan_status : '||l_v_loan_status );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_v_prc_type_code : '||l_v_prc_type_code );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_c_cl_rec_status : '||l_c_cl_rec_status );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_clchsn_dtls_pkg.update_row to update @9 record of change_code_txt = D ');
igf_sl_clchsn_dtls_pkg.update_row (
x_rowid => rec_c_sl_clchsn_dtls.row_id,
x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => new_references.hold_rel_ind ,
x_old_date => rec_c_sl_clchsn_dtls.old_date ,
x_new_date => new_references.disb_date ,
x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
x_new_amt => new_references.disb_accepted_amt ,
x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_sl_clchsn_dtls.send_record_txt
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validating the Change record for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validation of the Change record failed for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send ');
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
igf_sl_clchsn_dtls_pkg.update_row (
x_rowid => rec_c_sl_clchsn_dtls.row_id,
x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => new_references.hold_rel_ind ,
x_old_date => rec_c_sl_clchsn_dtls.old_date ,
x_new_date => new_references.disb_date ,
x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
x_new_amt => new_references.disb_accepted_amt ,
x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_sl_clchsn_dtls.send_record_txt
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' updated the status to Not Ready to Send for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
ELSIF p_inserting THEN
l_n_award_id := new_references.award_id;
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_clchsn_dtls_pkg.insert_row to insert @9 record of change_code_txt = D ');
igf_sl_clchsn_dtls_pkg.insert_row (
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_NUM' ,
x_change_record_type_txt => '09' ,
x_change_code_txt => 'D' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => new_references.hold_rel_ind ,
x_new_value_txt => new_references.hold_rel_ind ,
x_old_date => new_references.disb_date ,
x_new_date => new_references.disb_date ,
x_old_amt => 0,
x_new_amt => new_references.disb_accepted_amt,
x_disbursement_number => new_references.disb_num ,
x_disbursement_date => new_references.disb_date ,
x_change_issue_code => 'PRE_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'Inserted @9 record of change_code_txt = D ');
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validating the Change record for Change send id: ' ||l_n_clchgsnd_id);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validation of the Change record failed for Change send id: ' ||l_n_clchgsnd_id);
END AfterRowInsertUpdateDelete1;
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 igf_aw_awd_disb_all
WHERE award_id = x_award_id
AND disb_num = x_disb_num
FOR UPDATE NOWAIT;
SELECT rowid
FROM igf_aw_awd_disb_all
WHERE ((award_id = x_award_id));
SELECT rowid
FROM igf_aw_awd_disb_all
WHERE ((rvsn_id = x_rvsn_id));
SELECT rowid
FROM igf_aw_awd_disb_all
WHERE ((tp_cal_type = x_cal_type) AND
(tp_sequence_number = x_sequence_number))
OR ((ld_cal_type = x_cal_type) AND
(ld_sequence_number = x_sequence_number));
SELECT rowid
FROM igf_aw_awd_disb_all
WHERE fee_class = x_fee_class;
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL,
x_payment_prd_st_date IN DATE DEFAULT NULL,
x_change_type_code IN VARCHAR2 DEFAULT NULL,
x_fund_return_mthd_code IN VARCHAR2 DEFAULT NULL,
x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
) AS
/*
|| Created By : adhawan
|| Created On : 16-NOV-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
|| bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
|| addded a new column
|| veramach 3-NOV-2003 FA 125 Multiple Distr Methods
|| Added attendance_type_code to the signature
|| (reverse chronological order - newest change first)
*/
BEGIN
set_column_values (
p_action,
x_rowid,
x_award_id,
x_disb_num,
x_tp_cal_type,
x_tp_sequence_number,
x_disb_gross_amt,
x_fee_1,
x_fee_2,
x_disb_net_amt,
x_disb_date,
x_trans_type,
x_elig_status,
x_elig_status_date,
x_affirm_flag,
x_hold_rel_ind,
x_manual_hold_ind,
x_disb_status,
x_disb_status_date,
x_late_disb_ind,
x_fund_dist_mthd,
x_prev_reported_ind,
x_fund_release_date,
x_fund_status,
x_fund_status_date,
x_fee_paid_1,
x_fee_paid_2,
x_cheque_number,
x_ld_cal_type,
x_ld_sequence_number,
x_disb_accepted_amt,
x_disb_paid_amt,
x_rvsn_id,
x_int_rebate_amt,
x_force_disb,
x_min_credit_pts,
x_disb_exp_dt,
x_verf_enfr_dt,
x_fee_class,
x_show_on_bill,
x_attendance_type_code,
x_base_attendance_type_code,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_payment_prd_st_date,
x_change_type_code,
x_fund_return_mthd_code,
x_direct_to_borr_flag
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation(
new_references.award_id,
new_references.disb_num
)
) 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_constraints;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation (
new_references.award_id,
new_references.disb_num
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
check_constraints;
ELSIF p_action IN ('DELETE','VALIDATE_DELETE') THEN
check_child_existance;
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_award_id IN NUMBER,
x_disb_num IN NUMBER,
x_tp_cal_type IN VARCHAR2,
x_tp_sequence_number IN NUMBER,
x_disb_gross_amt IN NUMBER,
x_fee_1 IN NUMBER,
x_fee_2 IN NUMBER,
x_disb_net_amt IN NUMBER,
x_disb_date IN DATE,
x_trans_type IN VARCHAR2,
x_elig_status IN VARCHAR2,
x_elig_status_date IN DATE,
x_affirm_flag IN VARCHAR2,
x_hold_rel_ind IN VARCHAR2,
x_manual_hold_ind IN VARCHAR2,
x_disb_status IN VARCHAR2,
x_disb_status_date IN DATE,
x_late_disb_ind IN VARCHAR2,
x_fund_dist_mthd IN VARCHAR2,
x_prev_reported_ind IN VARCHAR2,
x_fund_release_date IN DATE,
x_fund_status IN VARCHAR2,
x_fund_status_date IN DATE,
x_fee_paid_1 IN NUMBER,
x_fee_paid_2 IN NUMBER,
x_cheque_number IN VARCHAR2,
x_ld_cal_type IN VARCHAR2,
x_ld_sequence_number IN NUMBER,
x_disb_accepted_amt IN NUMBER,
x_disb_paid_amt IN NUMBER,
x_rvsn_id IN NUMBER,
x_int_rebate_amt IN NUMBER,
x_force_disb IN VARCHAR2,
x_min_credit_pts IN NUMBER,
x_disb_exp_dt IN DATE,
x_verf_enfr_dt IN DATE,
x_fee_class IN VARCHAR2,
x_show_on_bill IN VARCHAR2,
x_mode IN VARCHAR2,
x_attendance_type_code IN VARCHAR2,
x_base_attendance_type_code IN VARCHAR2,
x_payment_prd_st_date IN DATE,
x_change_type_code IN VARCHAR2,
x_fund_return_mthd_code IN VARCHAR2,
x_called_from IN VARCHAR2,
x_direct_to_borr_flag IN VARCHAR2
) AS
/*
|| Created By : adhawan
|| Created On : 16-NOV-2000
|| Purpose : Handles the INSERT DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
|| addded a new column
|| veramach 3-NOV-2003 FA 125 Multiple Distr Methods
|| Added attendance_type_code to the signature
|| (reverse chronological order - newest change first)
*/
CURSOR c IS
SELECT rowid
FROM igf_aw_awd_disb_all
WHERE award_id = x_award_id
AND disb_num = x_disb_num;
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;
p_action => 'INSERT',
x_rowid => x_rowid,
x_award_id => x_award_id,
x_disb_num => x_disb_num,
x_tp_cal_type => x_tp_cal_type,
x_tp_sequence_number => x_tp_sequence_number,
x_disb_gross_amt => x_disb_gross_amt,
x_fee_1 => x_fee_1,
x_fee_2 => x_fee_2,
x_disb_net_amt => x_disb_net_amt,
x_disb_date => x_disb_date,
x_trans_type => x_trans_type,
x_elig_status => x_elig_status,
x_elig_status_date => x_elig_status_date,
x_affirm_flag => x_affirm_flag,
x_hold_rel_ind => x_hold_rel_ind,
x_manual_hold_ind => x_manual_hold_ind,
x_disb_status => x_disb_status,
x_disb_status_date => x_disb_status_date,
x_late_disb_ind => x_late_disb_ind,
x_fund_dist_mthd => x_fund_dist_mthd,
x_prev_reported_ind => x_prev_reported_ind,
x_fund_release_date => x_fund_release_date,
x_fund_status => NVL(x_fund_status,'N'),
x_fund_status_date => x_fund_status_date,
x_fee_paid_1 => x_fee_paid_1,
x_fee_paid_2 => x_fee_paid_2,
x_cheque_number => x_cheque_number,
x_ld_cal_type => x_ld_cal_type,
x_ld_sequence_number => x_ld_sequence_number,
x_disb_accepted_amt => x_disb_accepted_amt,
x_disb_paid_amt => x_disb_paid_amt,
x_rvsn_id => x_rvsn_id,
x_int_rebate_amt => x_int_rebate_amt,
x_force_disb => x_force_disb,
x_min_credit_pts => x_min_credit_pts,
x_disb_exp_dt => x_disb_exp_dt,
x_verf_enfr_dt => x_verf_enfr_dt,
x_fee_class => x_fee_class,
x_show_on_bill => x_show_on_bill,
x_attendance_type_code => x_attendance_type_code,
x_base_attendance_type_code => x_base_attendance_type_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_payment_prd_st_date => x_payment_prd_st_date,
x_change_type_code => x_change_type_code,
x_fund_return_mthd_code => x_fund_return_mthd_code,
x_direct_to_borr_flag => x_direct_to_borr_flag
);
INSERT INTO igf_aw_awd_disb_all(
award_id,
disb_num,
tp_cal_type,
tp_sequence_number,
disb_gross_amt,
fee_1,
fee_2,
disb_net_amt,
disb_date,
trans_type,
elig_status,
elig_status_date,
affirm_flag,
hold_rel_ind,
manual_hold_ind,
disb_status,
disb_status_date,
late_disb_ind,
fund_dist_mthd,
prev_reported_ind,
fund_release_date,
fund_status,
fund_status_date,
fee_paid_1,
fee_paid_2,
cheque_number,
ld_cal_type,
ld_sequence_number,
disb_accepted_amt,
disb_paid_amt,
rvsn_id,
int_rebate_amt,
force_disb,
min_credit_pts,
disb_exp_dt,
verf_enfr_dt,
fee_class,
show_on_bill,
attendance_type_code,
base_attendance_type_code,
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,
payment_prd_st_date,
change_type_code,
fund_return_mthd_code,
direct_to_borr_flag
) VALUES (
new_references.award_id,
new_references.disb_num,
new_references.tp_cal_type,
new_references.tp_sequence_number,
new_references.disb_gross_amt,
new_references.fee_1,
new_references.fee_2,
new_references.disb_net_amt,
new_references.disb_date,
new_references.trans_type,
new_references.elig_status,
new_references.elig_status_date,
new_references.affirm_flag,
NVL(new_references.hold_rel_ind, 'FALSE'),
new_references.manual_hold_ind,
new_references.disb_status,
new_references.disb_status_date,
new_references.late_disb_ind,
new_references.fund_dist_mthd,
new_references.prev_reported_ind,
new_references.fund_release_date,
NVL(new_references.fund_status,'N'),
new_references.fund_status_date,
new_references.fee_paid_1,
new_references.fee_paid_2,
new_references.cheque_number,
new_references.ld_cal_type,
new_references.ld_sequence_number,
new_references.disb_accepted_amt,
new_references.disb_paid_amt,
new_references.rvsn_id,
new_references.int_rebate_amt,
new_references.force_disb,
new_references.min_credit_pts,
new_references.disb_exp_dt,
new_references.verf_enfr_dt,
new_references.fee_class,
new_references.show_on_bill,
new_references.attendance_type_code,
new_references.base_attendance_type_code,
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.payment_prd_st_date,
new_references.change_type_code,
new_references.fund_return_mthd_code,
new_references.direct_to_borr_flag
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', ' g_v_called_from '||g_v_called_from);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', 'before invoking igf_aw_gen.update_award ' );
igf_aw_gen.update_award (
new_references.award_id,
new_references.disb_num,
new_references.disb_net_amt,
new_references.disb_date,
'I',
g_v_called_from
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', 'after invoking igf_aw_gen.update_award ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', 'before invoking after_dml for p_action = INSERT ' );
after_dml(p_action => 'INSERT');
END insert_row;
SELECT
tp_cal_type,
tp_sequence_number,
disb_gross_amt,
fee_1,
fee_2,
disb_net_amt,
disb_date,
trans_type,
elig_status,
elig_status_date,
affirm_flag,
hold_rel_ind,
manual_hold_ind,
disb_status,
disb_status_date,
late_disb_ind,
fund_dist_mthd,
prev_reported_ind,
fund_release_date,
fund_status,
fund_status_date,
fee_paid_1,
fee_paid_2,
cheque_number,
ld_cal_type,
ld_sequence_number,
disb_accepted_amt,
disb_paid_amt,
rvsn_id,
int_rebate_amt,
force_disb,
min_credit_pts,
disb_exp_dt,
verf_enfr_dt,
fee_class,
show_on_bill,
attendance_type_code,
base_attendance_type_code,
payment_prd_st_date,
change_type_code,
fund_return_mthd_code,
direct_to_borr_flag
FROM igf_aw_awd_disb_all
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_rowid IN VARCHAR2,
x_award_id IN NUMBER,
x_disb_num IN NUMBER,
x_tp_cal_type IN VARCHAR2,
x_tp_sequence_number IN NUMBER,
x_disb_gross_amt IN NUMBER,
x_fee_1 IN NUMBER,
x_fee_2 IN NUMBER,
x_disb_net_amt IN NUMBER,
x_disb_date IN DATE,
x_trans_type IN VARCHAR2,
x_elig_status IN VARCHAR2,
x_elig_status_date IN DATE,
x_affirm_flag IN VARCHAR2,
x_hold_rel_ind IN VARCHAR2,
x_manual_hold_ind IN VARCHAR2,
x_disb_status IN VARCHAR2,
x_disb_status_date IN DATE,
x_late_disb_ind IN VARCHAR2,
x_fund_dist_mthd IN VARCHAR2,
x_prev_reported_ind IN VARCHAR2,
x_fund_release_date IN DATE,
x_fund_status IN VARCHAR2,
x_fund_status_date IN DATE,
x_fee_paid_1 IN NUMBER,
x_fee_paid_2 IN NUMBER,
x_cheque_number IN VARCHAR2,
x_ld_cal_type IN VARCHAR2,
x_ld_sequence_number IN NUMBER,
x_disb_accepted_amt IN NUMBER,
x_disb_paid_amt IN NUMBER,
x_rvsn_id IN NUMBER,
x_int_rebate_amt IN NUMBER,
x_force_disb IN VARCHAR2,
x_min_credit_pts IN NUMBER,
x_disb_exp_dt IN DATE,
x_verf_enfr_dt IN DATE,
x_fee_class IN VARCHAR2,
x_show_on_bill IN VARCHAR2,
x_mode IN VARCHAR2,
x_attendance_type_code IN VARCHAR2,
x_base_attendance_type_code IN VARCHAR2,
x_payment_prd_st_date IN DATE,
x_change_type_code IN VARCHAR2,
x_fund_return_mthd_code IN VARCHAR2,
x_called_from IN VARCHAR2,
x_direct_to_borr_flag IN VARCHAR2
) AS
/*
|| Created By : adhawan
|| Created On : 16-NOV-2000
|| Purpose : Handles the UPDATE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
|| addded a new column
|| veramach 3-NOV-2003 FA 125 Multiple Distr Methods
|| Added attendance_type_code to the 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_disb_num => x_disb_num,
x_tp_cal_type => x_tp_cal_type,
x_tp_sequence_number => x_tp_sequence_number,
x_disb_gross_amt => x_disb_gross_amt,
x_fee_1 => x_fee_1,
x_fee_2 => x_fee_2,
x_disb_net_amt => x_disb_net_amt,
x_disb_date => x_disb_date,
x_trans_type => x_trans_type,
x_elig_status => x_elig_status,
x_elig_status_date => x_elig_status_date,
x_affirm_flag => x_affirm_flag,
x_hold_rel_ind => x_hold_rel_ind,
x_manual_hold_ind => x_manual_hold_ind,
x_disb_status => x_disb_status,
x_disb_status_date => x_disb_status_date,
x_late_disb_ind => x_late_disb_ind,
x_fund_dist_mthd => x_fund_dist_mthd,
x_prev_reported_ind => x_prev_reported_ind,
x_fund_release_date => x_fund_release_date,
x_fund_status => x_fund_status,
x_fund_status_date => x_fund_status_date,
x_fee_paid_1 => x_fee_paid_1,
x_fee_paid_2 => x_fee_paid_2,
x_cheque_number => x_cheque_number,
x_ld_cal_type => x_ld_cal_type,
x_ld_sequence_number => x_ld_sequence_number,
x_disb_accepted_amt => x_disb_accepted_amt,
x_disb_paid_amt => x_disb_paid_amt,
x_rvsn_id => x_rvsn_id,
x_int_rebate_amt => x_int_rebate_amt,
x_force_disb => x_force_disb,
x_min_credit_pts => x_min_credit_pts,
x_disb_exp_dt => x_disb_exp_dt,
x_verf_enfr_dt => x_verf_enfr_dt,
x_fee_class => x_fee_class,
x_show_on_bill => x_show_on_bill,
x_attendance_type_code => x_attendance_type_code,
x_base_attendance_type_code => x_base_attendance_type_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_payment_prd_st_date => x_payment_prd_st_date,
x_change_type_code => x_change_type_code,
x_fund_return_mthd_code => x_fund_return_mthd_code,
x_direct_to_borr_flag => x_direct_to_borr_flag
);
x_program_update_date := old_references.program_update_date;
x_program_update_date := SYSDATE;
igf_aw_gen.update_disb( old_references,new_references );
UPDATE igf_aw_awd_disb_all
SET
tp_cal_type = new_references.tp_cal_type,
tp_sequence_number = new_references.tp_sequence_number,
disb_gross_amt = new_references.disb_gross_amt,
fee_1 = new_references.fee_1,
fee_2 = new_references.fee_2,
disb_net_amt = new_references.disb_net_amt,
disb_date = new_references.disb_date,
trans_type = new_references.trans_type,
elig_status = new_references.elig_status,
elig_status_date = new_references.elig_status_date,
affirm_flag = new_references.affirm_flag,
hold_rel_ind = NVL(new_references.hold_rel_ind, 'FALSE'),
manual_hold_ind = new_references.manual_hold_ind,
disb_status = new_references.disb_status,
disb_status_date = new_references.disb_status_date,
late_disb_ind = new_references.late_disb_ind,
fund_dist_mthd = new_references.fund_dist_mthd,
prev_reported_ind = new_references.prev_reported_ind,
fund_release_date = new_references.fund_release_date,
fund_status = new_references.fund_status,
fund_status_date = new_references.fund_status_date,
fee_paid_1 = new_references.fee_paid_1,
fee_paid_2 = new_references.fee_paid_2,
cheque_number = new_references.cheque_number,
ld_cal_type = new_references.ld_cal_type,
ld_sequence_number = new_references.ld_sequence_number,
disb_accepted_amt = new_references.disb_accepted_amt,
disb_paid_amt = new_references.disb_paid_amt,
rvsn_id = new_references.rvsn_id,
int_rebate_amt = new_references.int_rebate_amt,
force_disb = new_references.force_disb,
min_credit_pts = new_references.min_credit_pts,
disb_exp_dt = new_references.disb_exp_dt,
verf_enfr_dt = new_references.verf_enfr_dt,
fee_class = new_references.fee_class,
show_on_bill = new_references.show_on_bill,
attendance_type_code = new_references.attendance_type_code,
base_attendance_type_code = new_references.base_attendance_type_code,
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,
payment_prd_st_date = new_references.payment_prd_st_date,
change_type_code = new_references.change_type_code,
fund_return_mthd_code = new_references.fund_return_mthd_code,
direct_to_borr_flag = new_references.direct_to_borr_flag
WHERE rowid = x_rowid;
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', ' g_v_called_from '||g_v_called_from);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'before invoking after_dml ' );
after_dml(p_action => 'UPDATE');
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'after invoking after_dml ' );
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'before invoking igf_aw_gen.update_award ' );
igf_aw_gen.update_award (new_references.award_id,
new_references.disb_num,
new_references.disb_net_amt,
new_references.disb_date,
'U',
g_v_called_from
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'after invoking igf_aw_gen.update_award ' );
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)
|| bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
|| addded a new column
|| veramach 3-NOV-2003 FA 125 Multiple Distr Methods
|| Added attendance_type_code to the signature
*/
CURSOR c1 IS
SELECT rowid
FROM igf_aw_awd_disb_all
WHERE award_id = x_award_id
AND disb_num = x_disb_num;
insert_row (
x_rowid,
x_award_id,
x_disb_num,
x_tp_cal_type,
x_tp_sequence_number,
x_disb_gross_amt,
x_fee_1,
x_fee_2,
x_disb_net_amt,
x_disb_date,
x_trans_type,
x_elig_status,
x_elig_status_date,
x_affirm_flag,
x_hold_rel_ind,
x_manual_hold_ind,
x_disb_status,
x_disb_status_date,
x_late_disb_ind,
x_fund_dist_mthd,
x_prev_reported_ind,
x_fund_release_date,
x_fund_status,
x_fund_status_date,
x_fee_paid_1,
x_fee_paid_2,
x_cheque_number,
x_ld_cal_type,
x_ld_sequence_number,
x_disb_accepted_amt,
x_disb_paid_amt,
x_rvsn_id,
x_int_rebate_amt,
x_force_disb,
x_min_credit_pts,
x_disb_exp_dt,
x_verf_enfr_dt,
x_fee_class,
x_show_on_bill,
x_mode,
x_attendance_type_code,
x_base_attendance_type_code,
x_payment_prd_st_date,
x_change_type_code,
x_fund_return_mthd_code,
x_direct_to_borr_flag
);
update_row (
x_rowid,
x_award_id,
x_disb_num,
x_tp_cal_type,
x_tp_sequence_number,
x_disb_gross_amt,
x_fee_1,
x_fee_2,
x_disb_net_amt,
x_disb_date,
x_trans_type,
x_elig_status,
x_elig_status_date,
x_affirm_flag,
x_hold_rel_ind,
x_manual_hold_ind,
x_disb_status,
x_disb_status_date,
x_late_disb_ind,
x_fund_dist_mthd,
x_prev_reported_ind,
x_fund_release_date,
x_fund_status,
x_fund_status_date,
x_fee_paid_1,
x_fee_paid_2,
x_cheque_number,
x_ld_cal_type,
x_ld_sequence_number,
x_disb_accepted_amt,
x_disb_paid_amt,
x_rvsn_id,
x_int_rebate_amt,
x_force_disb,
x_min_credit_pts,
x_disb_exp_dt,
x_verf_enfr_dt,
x_fee_class,
x_show_on_bill,
x_mode,
x_attendance_type_code,
x_base_attendance_type_code,
x_payment_prd_st_date,
x_change_type_code,
x_fund_return_mthd_code,
x_direct_to_borr_flag
);
PROCEDURE delete_row (
x_rowid IN VARCHAR2,
x_called_from IN VARCHAR2
) AS
/*
|| Created By : adhawan
|| Created On : 16-NOV-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)
*/
--
-- Bug 2255279
-- sjadhav
-- Apr02,2002
-- Reflect The Amoutns into Awards Table once a disbursement
-- is deleted
--
CURSOR cur_get_award ( p_row_id ROWID)
IS
SELECT
award_id
FROM
igf_aw_awd_disb
WHERE
row_id = p_row_id;
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' g_v_called_from '||g_v_called_from);
p_action => 'DELETE',
x_rowid => x_rowid
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' before invoking BeforeRowInsertUpdateDelete1 ');
BeforeRowInsertUpdateDelete1(
p_rowid => x_rowid,
p_inserting => FALSE,
p_updating => FALSE ,
p_deleting => TRUE
);
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' after invoking BeforeRowInsertUpdateDelete1 ');
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' deleting the record from igf_aw_awd_disb_all table for rowid '||x_rowid);
DELETE FROM igf_aw_awd_disb_all
WHERE rowid = x_rowid;
fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' before invoking igf_aw_gen.update_award ');
igf_aw_gen.update_award (get_award_rec.award_id,
0,
0,
TRUNC(SYSDATE),
'D',
g_v_called_from
);
END delete_row;