[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dl_code,
pell_code,
ffel_code,
isir_code,
profile_code
FROM
igf_ap_batch_aw_map
WHERE
ci_cal_type = p_cal_type AND
ci_sequence_number = p_seq_num;
SELECT meaning
FROM
igf_lookups_view
WHERE
lookup_code = UPPER(TRIM(x_code)) AND
lookup_type = UPPER(TRIM(x_type)) ;
SELECT meaning
FROM
igs_lookups_view
WHERE
lookup_code = UPPER(TRIM(x_code)) AND
lookup_type = UPPER(TRIM(x_type)) ;
SELECT 'X'
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id AND
trans_type <> 'C';
PROCEDURE update_disb(p_disb_old_rec igf_aw_awd_disb_all%ROWTYPE,
p_disb_new_rec igf_aw_awd_disb_all%ROWTYPE)
IS
--------------------------------------------------------------------------------------
-- sjadhav 05-Nov-2004 FA 134 Build. Update loans table if disb data changes
--------------------------------------------------------------------------------------
-- ayedubat 11-OCT-2004 Changed the calling of procedure, igf_db_awd_disb_dtl_pkg.insert_row
-- to pass NULL values for the columns, DISB_STATUS, DISB_STATUS_DATE,
-- DISB_BATCH_ID, DISB_ACK_DATE, BOOKING_BATCH_ID and BOOKED_DATE for Bug, 3416863
-- sjadhav 18-Feb-2003 Bug 2758823
-- Modified update_disb routine to create adjustment
-- for disbursement date change for direct loan award
--------------------------------------------------------------------------------------
-- sjadhav, Feb 08th 2002
--
-- This procedure creates adjustments for actual disbursement
--
-- If the Direct Loan is Accepted and if any amounts change then
-- update Loan Change Status to 'Ready to Send'
--------------------------------------------------------------------------------------
--
-- Cursor to get loan details
--
CURSOR cur_loans(
p_award_id igf_db_awd_disb_dtl.award_id%TYPE
)
IS
SELECT loan.*
FROM igf_sl_loans loan
WHERE loan.award_id = p_award_id
FOR UPDATE OF loan_chg_status;
SELECT cat.fed_fund_code, awd.award_status award_status
FROM igf_aw_award awd, igf_aw_fund_mast fmast, igf_aw_fund_cat cat
WHERE awd.award_id = p_award_id
AND awd.fund_id = fmast.fund_id
AND fmast.fund_code = cat.fund_code;
SELECT MAX(disb_seq_num) max_num
FROM igf_db_awd_disb_dtl
WHERE award_id = p_award_id AND disb_num = p_disb_num;
SELECT
SUM(DECODE(DISB_ACTIVITY,'D',DISB_NET_AMT,'A',DISB_ADJ_AMT,'Q',0)) net_total
FROM igf_db_awd_disb_dtl_all
WHERE award_id =p_award_id AND disb_num = p_disb_num AND sf_status in('R','P','E');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'p_disb_old_rec.disb_net_amt:' || p_disb_old_rec.disb_net_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'p_disb_new_rec.disb_net_amt:' || p_disb_new_rec.disb_net_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'Inserting igf_db_awd_disb_dtl award_id '|| dbdtlrec.award_id);
igf_db_awd_disb_dtl_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => dbdtlrec.award_id,
x_disb_num => dbdtlrec.disb_num,
x_disb_seq_num => dbdtlrec.disb_seq_num,
x_disb_gross_amt => dbdtlrec.disb_gross_amt,
x_fee_1 => dbdtlrec.fee_1,
x_fee_2 => dbdtlrec.fee_2,
x_disb_net_amt => dbdtlrec.disb_net_amt,
x_disb_adj_amt => dbdtlrec.disb_adj_amt,
x_disb_date => dbdtlrec.disb_date,
x_fee_paid_1 => dbdtlrec.fee_paid_1,
x_fee_paid_2 => dbdtlrec.fee_paid_2,
x_disb_activity => dbdtlrec.disb_activity,
x_disb_batch_id => NULL,
x_disb_ack_date => NULL,
x_booking_batch_id => NULL,
x_booked_date => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_sf_status => dbdtlrec.sf_status,
x_sf_status_date => dbdtlrec.sf_status_date,
x_sf_invoice_num => dbdtlrec.sf_invoice_num,
x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
x_sf_credit_id => dbdtlrec.sf_credit_id,
x_error_desc => dbdtlrec.error_desc,
x_mode => 'R',
x_notification_date => NULL,
x_interest_rebate_amt => NULL,
x_ld_cal_type => dbdtlrec.ld_cal_type,
x_ld_sequence_number => dbdtlrec.ld_sequence_number
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'Inserting igf_db_awd_disb_dtl award_id '|| dbdtlrec.award_id);
igf_db_awd_disb_dtl_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => dbdtlrec.award_id,
x_disb_num => dbdtlrec.disb_num,
x_disb_seq_num => dbdtlrec.disb_seq_num,
x_disb_gross_amt => dbdtlrec.disb_gross_amt,
x_fee_1 => dbdtlrec.fee_1,
x_fee_2 => dbdtlrec.fee_2,
x_disb_net_amt => dbdtlrec.disb_net_amt,
x_disb_adj_amt => dbdtlrec.disb_adj_amt,
x_disb_date => dbdtlrec.disb_date,
x_fee_paid_1 => dbdtlrec.fee_paid_1,
x_fee_paid_2 => dbdtlrec.fee_paid_2,
x_disb_activity => dbdtlrec.disb_activity,
x_disb_batch_id => NULL,
x_disb_ack_date => NULL,
x_booking_batch_id => NULL,
x_booked_date => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_sf_status => dbdtlrec.sf_status,
x_sf_status_date => dbdtlrec.sf_status_date,
x_sf_invoice_num => dbdtlrec.sf_invoice_num,
x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
x_sf_credit_id => dbdtlrec.sf_credit_id,
x_error_desc => dbdtlrec.error_desc,
x_mode => 'R',
x_notification_date => NULL,
x_interest_rebate_amt => NULL,
x_ld_cal_type => dbdtlrec.ld_cal_type,
x_ld_sequence_number => dbdtlrec.ld_sequence_number
);
igf_db_awd_disb_dtl_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => dbdtlrec.award_id,
x_disb_num => dbdtlrec.disb_num,
x_disb_seq_num => dbdtlrec.disb_seq_num+1,
x_disb_gross_amt => dbdtlrec.disb_gross_amt,
x_fee_1 => dbdtlrec.fee_1,
x_fee_2 => dbdtlrec.fee_2,
x_disb_net_amt => dbdtlrec.disb_net_amt,
x_disb_adj_amt => dbdtlrec.disb_adj_amt,
x_disb_date => dbdtlrec.disb_date,
x_fee_paid_1 => dbdtlrec.fee_paid_1,
x_fee_paid_2 => dbdtlrec.fee_paid_2,
x_disb_activity => dbdtlrec.disb_activity,
x_disb_batch_id => NULL,
x_disb_ack_date => NULL,
x_booking_batch_id => NULL,
x_booked_date => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_sf_status => dbdtlrec.sf_status,
x_sf_status_date => dbdtlrec.sf_status_date,
x_sf_invoice_num => dbdtlrec.sf_invoice_num,
x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
x_sf_credit_id => dbdtlrec.sf_credit_id,
x_error_desc => dbdtlrec.error_desc,
x_mode => 'R',
x_notification_date => NULL,
x_interest_rebate_amt => NULL,
x_ld_cal_type => dbdtlrec.ld_cal_type,
x_ld_sequence_number => dbdtlrec.ld_sequence_number
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'Inserting to igf_db_awd_disb_dtl award_id:'|| dbdtlrec.award_id);
igf_db_awd_disb_dtl_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => dbdtlrec.award_id,
x_disb_num => dbdtlrec.disb_num,
x_disb_seq_num => dbdtlrec.disb_seq_num,
x_disb_gross_amt => dbdtlrec.disb_gross_amt,
x_fee_1 => dbdtlrec.fee_1,
x_fee_2 => dbdtlrec.fee_2,
x_disb_net_amt => dbdtlrec.disb_net_amt,
x_disb_adj_amt => dbdtlrec.disb_adj_amt,
x_disb_date => dbdtlrec.disb_date,
x_fee_paid_1 => dbdtlrec.fee_paid_1,
x_fee_paid_2 => dbdtlrec.fee_paid_2,
x_disb_activity => dbdtlrec.disb_activity,
x_disb_batch_id => NULL,
x_disb_ack_date => NULL,
x_booking_batch_id => NULL,
x_booked_date => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_sf_status => dbdtlrec.sf_status,
x_sf_status_date => dbdtlrec.sf_status_date,
x_sf_invoice_num => dbdtlrec.sf_invoice_num,
x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
x_sf_credit_id => dbdtlrec.sf_credit_id,
x_error_desc => dbdtlrec.error_desc,
x_mode => 'R',
x_notification_date => NULL,
x_interest_rebate_amt => NULL,
x_ld_cal_type => dbdtlrec.ld_cal_type,
x_ld_sequence_number => dbdtlrec.ld_sequence_number
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'Updating loan ' || loans_rec.loan_id || ' as ready to send');
igf_sl_loans_pkg.update_row(
x_rowid => loans_rec.row_id,
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 => 'G',
x_loan_chg_status_date => TRUNC(SYSDATE),
x_active => loans_rec.active,
x_active_date => loans_rec.active_date,
x_borw_detrm_code => loans_rec.borw_detrm_code,
x_external_loan_id_txt => loans_rec.external_loan_id_txt,
x_mode => 'R'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
'Updating loan ' || loans_rec.loan_id || ' as ready to send');
igf_sl_loans_pkg.update_row(
x_rowid => loans_rec.row_id,
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 => 'G',
x_loan_chg_status_date => TRUNC(SYSDATE),
x_active => loans_rec.active,
x_active_date => loans_rec.active_date,
x_borw_detrm_code => loans_rec.borw_detrm_code,
x_external_loan_id_txt => loans_rec.external_loan_id_txt,
x_mode => 'R'
);
fnd_message.set_token('NAME', 'IGF_AW_GEN.UPDATE_DISB' || ' ' || SQLERRM);
END update_disb;
PROCEDURE update_fabase_awds(
p_base_id in igf_ap_fa_base_rec_all.base_id%TYPE,
p_pack_status igf_ap_fa_base_rec.packaging_status%TYPE
) IS
/*
|| Created By : cdcruz
|| Created On : 29-JAN-2001
|| Purpose : Update FA Base record with the latest Packaging deails
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rasahoo 27-NOV-2003 FA 128 - ISIR update 2004-05
|| added new parameter award_fmly_contribution_type to
|| igf_ap_fa_base_rec_pkg.update_row
||
|| ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
|| added new parameter assoc_org_num to
|| igf_ap_fa_base_rec_pkg.update_row call.
||
|| brajendr 18-Dec-2002 Bug # 2691832
|| Modified the logic for updating the Packaging Status.
||
|| masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
|| removed packaging hold
||
|| masehgal 25-Sep-2002 FA 104 - To Do Enhancements
|| Added manual_disb_hold in FA Base update
||
|| avenkatr 27-JUN-2001 1. Added p_pack_status parameter.
*/
-- Sums up studentwise packaged totals
CURSOR c_pkg_tot ( x_base_id igf_aw_award_t.base_id%TYPE) IS
SELECT SUM(NVL(awd.offered_amt,0)) offered_amt,
SUM(NVL(awd.accepted_amt,0)) accepted_amt
FROM igf_aw_award awd
WHERE awd.base_id = x_base_id
AND award_status NOT IN( 'CANCELLED', 'DECLINED','SIMULATED' ) ;
SELECT fabase.*
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = x_base_id ;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fabase_awds.debug','p_base_id:'||p_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fabase_awds.debug','p_pack_status:'||p_pack_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fabase_awds.debug','Updating igf_ap_fa_base_rec');
igf_ap_fa_base_rec_pkg.update_row(
x_rowid => l_stud_det.row_id,
x_base_id => l_stud_det.base_id,
x_ci_cal_type => l_stud_det.ci_cal_type,
x_person_id => l_stud_det.person_id,
x_ci_sequence_number => l_stud_det.ci_sequence_number,
x_org_id => l_stud_det.org_id,
x_coa_pending => l_stud_det.coa_pending,
x_verification_process_run => l_stud_det.verification_process_run,
x_inst_verif_status_date => l_stud_det.inst_verif_status_date,
x_manual_verif_flag => l_stud_det.manual_verif_flag,
x_fed_verif_status => l_stud_det.fed_verif_status,
x_fed_verif_status_date => l_stud_det.fed_verif_status_date,
x_inst_verif_status => l_stud_det.inst_verif_status,
x_nslds_eligible => l_stud_det.nslds_eligible,
x_ede_correction_batch_id => l_stud_det.ede_correction_batch_id,
x_fa_process_status_date => l_stud_det.fa_process_status_date,
x_isir_corr_status => l_stud_det.isir_corr_status,
x_isir_corr_status_date => l_stud_det.isir_corr_status_date,
x_isir_status => l_stud_det.isir_status,
x_isir_status_date => l_stud_det.isir_status_date,
x_coa_code_f => l_stud_det.coa_code_f,
x_coa_code_i => l_stud_det.coa_code_i,
x_coa_f => l_stud_det.coa_f,
x_coa_i => l_stud_det.coa_i,
x_disbursement_hold => l_stud_det.disbursement_hold,
x_fa_process_status => l_stud_det.fa_process_status,
x_notification_status => l_stud_det.notification_status,
x_notification_status_date => l_stud_det.notification_status_date,
x_packaging_status => l_stud_det.packaging_status,
x_packaging_status_date => l_stud_det.packaging_status_date,
x_total_package_accepted => l_stud_det.total_package_accepted,
x_total_package_offered => l_stud_det.total_package_offered,
x_admstruct_id => l_stud_det.admstruct_id,
x_admsegment_1 => l_stud_det.admsegment_1,
x_admsegment_2 => l_stud_det.admsegment_2,
x_admsegment_3 => l_stud_det.admsegment_3,
x_admsegment_4 => l_stud_det.admsegment_4,
x_admsegment_5 => l_stud_det.admsegment_5,
x_admsegment_6 => l_stud_det.admsegment_6,
x_admsegment_7 => l_stud_det.admsegment_7,
x_admsegment_8 => l_stud_det.admsegment_8,
x_admsegment_9 => l_stud_det.admsegment_9,
x_admsegment_10 => l_stud_det.admsegment_10,
x_admsegment_11 => l_stud_det.admsegment_11,
x_admsegment_12 => l_stud_det.admsegment_12,
x_admsegment_13 => l_stud_det.admsegment_13,
x_admsegment_14 => l_stud_det.admsegment_14,
x_admsegment_15 => l_stud_det.admsegment_15,
x_admsegment_16 => l_stud_det.admsegment_16,
x_admsegment_17 => l_stud_det.admsegment_17,
x_admsegment_18 => l_stud_det.admsegment_18,
x_admsegment_19 => l_stud_det.admsegment_19,
x_admsegment_20 => l_stud_det.admsegment_20,
x_packstruct_id => l_stud_det.packstruct_id,
x_packsegment_1 => l_stud_det.packsegment_1,
x_packsegment_2 => l_stud_det.packsegment_2,
x_packsegment_3 => l_stud_det.packsegment_3,
x_packsegment_4 => l_stud_det.packsegment_4,
x_packsegment_5 => l_stud_det.packsegment_5,
x_packsegment_6 => l_stud_det.packsegment_6,
x_packsegment_7 => l_stud_det.packsegment_7,
x_packsegment_8 => l_stud_det.packsegment_8,
x_packsegment_9 => l_stud_det.packsegment_9,
x_packsegment_10 => l_stud_det.packsegment_10,
x_packsegment_11 => l_stud_det.packsegment_11,
x_packsegment_12 => l_stud_det.packsegment_12,
x_packsegment_13 => l_stud_det.packsegment_13,
x_packsegment_14 => l_stud_det.packsegment_14,
x_packsegment_15 => l_stud_det.packsegment_15,
x_packsegment_16 => l_stud_det.packsegment_16,
x_packsegment_17 => l_stud_det.packsegment_17,
x_packsegment_18 => l_stud_det.packsegment_18,
x_packsegment_19 => l_stud_det.packsegment_19,
x_packsegment_20 => l_stud_det.packsegment_20,
x_miscstruct_id => l_stud_det.miscstruct_id,
x_miscsegment_1 => l_stud_det.miscsegment_1,
x_miscsegment_2 => l_stud_det.miscsegment_2,
x_miscsegment_3 => l_stud_det.miscsegment_3,
x_miscsegment_4 => l_stud_det.miscsegment_4,
x_miscsegment_5 => l_stud_det.miscsegment_5,
x_miscsegment_6 => l_stud_det.miscsegment_6,
x_miscsegment_7 => l_stud_det.miscsegment_7,
x_miscsegment_8 => l_stud_det.miscsegment_8,
x_miscsegment_9 => l_stud_det.miscsegment_9,
x_miscsegment_10 => l_stud_det.miscsegment_10,
x_miscsegment_11 => l_stud_det.miscsegment_11,
x_miscsegment_12 => l_stud_det.miscsegment_12,
x_miscsegment_13 => l_stud_det.miscsegment_13,
x_miscsegment_14 => l_stud_det.miscsegment_14,
x_miscsegment_15 => l_stud_det.miscsegment_15,
x_miscsegment_16 => l_stud_det.miscsegment_16,
x_miscsegment_17 => l_stud_det.miscsegment_17,
x_miscsegment_18 => l_stud_det.miscsegment_18,
x_miscsegment_19 => l_stud_det.miscsegment_19,
x_miscsegment_20 => l_stud_det.miscsegment_20,
x_prof_judgement_flg => l_stud_det.prof_judgement_flg,
x_nslds_data_override_flg => l_stud_det.nslds_data_override_flg ,
x_target_group => l_stud_det.target_group,
x_coa_fixed => l_stud_det.coa_fixed,
x_coa_pell => l_stud_det.coa_pell,
x_profile_status => l_stud_det.profile_status,
x_profile_status_date => l_stud_det.profile_status_date,
x_profile_fc => l_stud_det.profile_fc,
x_tolerance_amount => l_stud_det.tolerance_amount,
x_manual_disb_hold => l_stud_det.manual_disb_hold,
x_mode => 'R',
x_pell_alt_expense => l_stud_det.pell_alt_expense,
x_assoc_org_num => l_stud_det.assoc_org_num,
x_award_fmly_contribution_type => l_stud_det.award_fmly_contribution_type,
x_isir_locked_by => l_stud_det.isir_locked_by,
x_adnl_unsub_loan_elig_flag => l_stud_det.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => l_stud_det.lock_awd_flag,
x_lock_coa_flag => l_stud_det.lock_coa_flag
);
fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_FA_BASE_AWD' ||' ' || SQLERRM);
END update_fabase_awds;
SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),NULL)
FROM dual;
PROCEDURE update_fmast( x_old_ref in igf_aw_award_all%ROWTYPE,
x_new_ref in igf_aw_award_all%ROWTYPE,
flag in Varchar )
IS
/*
|| Created By : pkpatel
|| Created On : 11-DEC-2001
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| museshad 24-Oct-2005 cancelled_amt was getting updated incorrectly.
|| Fixed this. Note that, when a Offered/Accepted award (with
|| award amount say X) is Cancelled, then the cancelled_amt
|| of the fund is 0 and not X. The cancelled_amt for a fund
|| always remains at 0 and it is not used anywhere.
||
|| museshad 14-Jul-2005 Build FA 140.
|| Modified TBH call due to the addition of new
|| columns to igf_aw_fund_mast_all table.
||
|| smvk 10_feb_2003 Bug # 2758812. Added send_without_doc column in the igf_aw_fund_mast_pkg.update_row call.
||
|| brajendr 19-Dec-2002 Bug # 2708599
|| Modifed the procedure update_fmast for deletion of Simulated awards,
|| earlier it was looking at new refferences, modified to old refferences
||
|| pkpatel 11-DEC-2001 Bug NO:2154941 Disbursement DLD
|| Removed the reference to dropped columns from, IGF_AW_FUND_MAST
||
|| cdcruz 06-MAY-2002 Bug NO: 2310222 Summary Tab updation
|| All summary column counts and totals modified
*/
CURSOR c_fmast( x_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
SELECT fmast.*
FROM igf_aw_fund_mast fmast
WHERE fund_id = x_fund_id ;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','flag:'||flag);
IF ( flag = 'DELETE' ) THEN
OPEN c_fmast( x_old_ref.fund_id );
IF ( flag = 'INSERT' ) THEN
IF ( x_new_ref.award_status = 'OFFERED' ) THEN
l_fmast.offered_amt := NVL(l_fmast.offered_amt,0) + NVL(x_new_ref.offered_amt,0) ;
ELSIF ( flag = 'UPDATE' ) THEN
-- First update the amounts which is independent of Status
l_fmast.accepted_amt := NVL(l_fmast.accepted_amt,0) - NVL(x_old_ref.accepted_amt,0) + NVL(x_new_ref.accepted_amt,0) ;
ELSIF ( flag = 'DELETE' ) THEN
l_fmast.offered_amt := NVL(l_fmast.offered_amt,0) - NVL(x_old_ref.offered_amt,0) ;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.offered_amt:'||l_fmast.offered_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.accepted_amt:'||l_fmast.accepted_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.total_offered:'||l_fmast.total_offered);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.cancelled_amt:'||l_fmast.cancelled_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.total_cancelled:'||l_fmast.total_cancelled);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.declined_amt:'||l_fmast.declined_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.total_declined:'||l_fmast.total_declined);
igf_aw_fund_mast_pkg.update_row (
x_rowid => l_fmast.row_id,
x_fund_id => l_fmast.fund_id,
x_fund_code => l_fmast.fund_code,
x_ci_cal_type => l_fmast.ci_cal_type,
x_ci_sequence_number => l_fmast.ci_sequence_number,
x_description => l_fmast.description,
x_discontinue_fund => l_fmast.discontinue_fund,
x_entitlement => l_fmast.entitlement,
x_auto_pkg => l_fmast.auto_pkg,
x_self_help => l_fmast.self_help,
x_allow_man_pkg => l_fmast.allow_man_pkg,
x_update_need => l_fmast.update_need,
x_disburse_fund => l_fmast.disburse_fund,
x_available_amt => l_fmast.available_amt,
x_offered_amt => l_fmast.offered_amt,
x_pending_amt => l_fmast.pending_amt,
x_accepted_amt => l_fmast.accepted_amt,
x_declined_amt => l_fmast.declined_amt,
x_cancelled_amt => l_fmast.cancelled_amt,
x_remaining_amt => l_fmast.remaining_amt,
x_enrollment_status => l_fmast.enrollment_status,
x_prn_award_letter => l_fmast.prn_award_letter,
x_over_award_amt => l_fmast.over_award_amt,
x_over_award_perct => l_fmast.over_award_perct,
x_min_award_amt => l_fmast.min_award_amt,
x_max_award_amt => l_fmast.max_award_amt,
x_max_yearly_amt => l_fmast.max_yearly_amt,
x_max_life_amt => l_fmast.max_life_amt,
x_max_life_term => l_fmast.max_life_term,
x_fm_fc_methd => l_fmast.fm_fc_methd,
x_roundoff_fact => l_fmast.roundoff_fact,
x_replace_fc => l_fmast.replace_fc,
x_allow_overaward => l_fmast.allow_overaward,
x_pckg_awd_stat => l_fmast.pckg_awd_stat,
x_org_record_req => l_fmast.org_record_req,
x_disb_record_req => l_fmast.disb_record_req,
x_prom_note_req => l_fmast.prom_note_req,
x_min_num_disb => l_fmast.min_num_disb,
x_max_num_disb => l_fmast.max_num_disb,
X_FEE_TYPE => l_fmast.FEE_TYPE ,
x_total_offered => l_fmast.total_offered,
x_total_accepted => l_fmast.total_accepted,
x_total_declined => l_fmast.total_declined,
x_total_revoked => l_fmast.total_revoked,
x_total_cancelled => l_fmast.total_cancelled,
x_total_disbursed => l_fmast.total_disbursed,
x_total_committed => l_fmast.total_committed,
x_committed_amt => l_fmast.committed_amt,
x_disbursed_amt => l_fmast.disbursed_amt,
x_awd_notice_txt => l_fmast.awd_notice_txt,
x_attribute_category => l_fmast.attribute_category,
x_attribute1 => l_fmast.attribute1,
x_attribute2 => l_fmast.attribute2,
x_attribute3 => l_fmast.attribute3,
x_attribute4 => l_fmast.attribute4,
x_attribute5 => l_fmast.attribute5,
x_attribute6 => l_fmast.attribute6,
x_attribute7 => l_fmast.attribute7,
x_attribute8 => l_fmast.attribute8,
x_attribute9 => l_fmast.attribute9,
x_attribute10 => l_fmast.attribute10,
x_attribute11 => l_fmast.attribute11,
x_attribute12 => l_fmast.attribute12,
x_attribute13 => l_fmast.attribute13,
x_attribute14 => l_fmast.attribute14,
x_attribute15 => l_fmast.attribute15,
x_attribute16 => l_fmast.attribute16,
x_attribute17 => l_fmast.attribute17,
x_attribute18 => l_fmast.attribute18,
x_attribute19 => l_fmast.attribute19,
x_attribute20 => l_fmast.attribute20,
x_disb_verf_da => l_fmast.disb_verf_da,
x_fund_exp_da => l_fmast.fund_exp_da,
x_nslds_disb_da => l_fmast.nslds_disb_da,
x_disb_exp_da => l_fmast.disb_exp_da,
x_fund_recv_reqd => l_fmast.fund_recv_reqd,
x_show_on_bill => l_fmast.show_on_bill,
x_bill_desc => l_fmast.bill_desc,
x_credit_type_id => l_fmast.credit_type_id,
x_spnsr_ref_num => l_fmast.spnsr_ref_num,
x_party_id => l_fmast.party_id,
x_spnsr_fee_type => l_fmast.spnsr_fee_type,
x_min_credit_points => l_fmast.min_credit_points,
x_group_id => l_fmast.group_id,
x_threshold_perct => l_fmast.threshold_perct,
x_threshold_value => l_fmast.threshold_value,
x_spnsr_attribute_category => l_fmast.spnsr_attribute_category,
x_spnsr_attribute1 => l_fmast.spnsr_attribute1,
x_spnsr_attribute2 => l_fmast.spnsr_attribute2,
x_spnsr_attribute3 => l_fmast.spnsr_attribute3,
x_spnsr_attribute4 => l_fmast.spnsr_attribute4,
x_spnsr_attribute5 => l_fmast.spnsr_attribute5,
x_spnsr_attribute6 => l_fmast.spnsr_attribute6,
x_spnsr_attribute7 => l_fmast.spnsr_attribute7,
x_spnsr_attribute8 => l_fmast.spnsr_attribute8,
x_spnsr_attribute9 => l_fmast.spnsr_attribute9,
x_spnsr_attribute10 => l_fmast.spnsr_attribute10,
x_spnsr_attribute11 => l_fmast.spnsr_attribute11,
x_spnsr_attribute12 => l_fmast.spnsr_attribute12,
x_spnsr_attribute13 => l_fmast.spnsr_attribute13,
x_spnsr_attribute14 => l_fmast.spnsr_attribute14,
x_spnsr_attribute15 => l_fmast.spnsr_attribute15,
x_spnsr_attribute16 => l_fmast.spnsr_attribute16,
x_spnsr_attribute17 => l_fmast.spnsr_attribute17,
x_spnsr_attribute18 => l_fmast.spnsr_attribute18,
x_spnsr_attribute19 => l_fmast.spnsr_attribute19,
x_spnsr_attribute20 => l_fmast.spnsr_attribute20,
x_ver_app_stat_override => l_fmast.ver_app_stat_override,
x_gift_aid => l_fmast.gift_aid,
x_send_without_doc => l_fmast.send_without_doc, -- Bug # 2758812. Added send_without_doc column.
x_re_pkg_verif_flag => l_fmast.re_pkg_verif_flag,
x_donot_repkg_if_code => l_fmast.donot_repkg_if_code,
x_lock_award_flag => l_fmast.lock_award_flag,
x_disb_rounding_code => l_fmast.disb_rounding_code,
x_view_only_flag => l_fmast.view_only_flag,
x_accept_less_amt_flag => l_fmast.accept_less_amt_flag,
x_allow_inc_post_accept_flag => l_fmast.allow_inc_post_accept_flag,
x_min_increase_amt => l_fmast.min_increase_amt,
x_allow_dec_post_accept_flag => l_fmast.allow_dec_post_accept_flag,
x_min_decrease_amt => l_fmast.min_decrease_amt,
x_allow_decln_post_accept_flag => l_fmast.allow_decln_post_accept_flag,
x_status_after_decline => l_fmast.status_after_decline,
x_fund_information_txt => l_fmast.fund_information_txt,
x_mode => 'R'
);
fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_FMAST' ||' ' || SQLERRM);
END update_fmast;
PROCEDURE update_award( p_award_id IN igf_aw_award_all.award_id%TYPE,
p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE,
p_disb_amt IN igf_aw_awd_disb_all.disb_net_amt%TYPE,
p_disb_dt IN igf_aw_awd_disb_all.disb_date%TYPE,
p_action IN VARCHAR2,
x_called_from IN VARCHAR2
)
IS
--------------------------------------------------------------------------------------
-- museshad 05-Apr-2006 Bug 5140851. Do not reset paid amount for FWS awards
-- bcoz paid amount for FWS is maintained only at award
-- level. Retain award paid amout for FWS awards.
--------------------------------------------------------------------------------------
-- museshad 08-Aug-2005 Bug 3954451. Cancel award(and make offered amount
-- and accepted amount as 0), if all disbursements
-- in the award are cancelled.
--------------------------------------------------------------------------------------
-- veramach 05-Jul-2004 bug 3682032 Modified logic so that if error 'fund locked'
-- error is thrown by the awards table handler, it is passed
-- correctly to the caller
--------------------------------------------------------------------------------------
-- sjadhav 10-Dec-2003 FA 131 Changes
-- De-link auto update of Pell Disbursement and Pell
-- origination amounts
--------------------------------------------------------------------------------------
-- veramach 1-NOV-2003 FA 125(#3160568) Added apdlans_id in the calls
-- to igf_aw_award_pkg.update_row
--------------------------------------------------------------------------------------
-- rasahoo 09-Sep-2003 Bug 33094878 Added the call to update_fabase_awds
-- as the FA base rec was not getting updated when any
-- DML Operations performed from Disbursement form.
--------------------------------------------------------------------------------------
-- sjadhav 05-Feb-2003 FA116 Build - Bug 2758812
-- Modified update_award to set pell origination
-- status to 'R'.
--------------------------------------------------------------------------------------
-- sjadhav Jan 25,2002 Bug ID : 2154941
-- This routine is called throgh table handler of
-- igf_aw_awd_Disb table. This routine updates
-- igf_aw_Award,igf_gr_rfms,igf_gr_rfms_disb
-- tables to reflect the changes made in
-- igf_aw_Awd_disb table
--------------------------------------------------------------------------------------
--
-- Cursor to Get The Award Record which will be updated
--
CURSOR cur_award (p_award_id igf_aw_award_all.award_id%TYPE)
IS
SELECT
awd.rowid row_id,awd.*
FROM
igf_aw_award_all awd
WHERE
award_id = p_award_id
FOR UPDATE OF offered_amt;
SELECT
SUM(NVL(disb_gross_amt,0)) offered_amt,
SUM(NVL(disb_accepted_amt,0)) accepted_amt,
SUM(NVL(disb_paid_amt,0)) paid_amt
FROM
igf_aw_awd_disb_all
WHERE
award_id = p_award_id;
SELECT
cat.fed_fund_code
FROM
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all cat
WHERE
fmast.fund_id = c_fund_id AND
fmast.fund_code = cat.fund_code;
SELECT 'X'
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id AND
trans_type <> 'C';
SELECT SUM(disb_accepted_amt) accepted_amt
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id
AND trans_type <> 'C';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_award_id: '||p_award_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_disb_num: '||p_disb_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_disb_amt: '||p_disb_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_disb_dt: '||p_disb_dt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_action: '||p_action);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','disb_rec.paid_amt: '||disb_rec.paid_amt);
If the user updates the disb_accepted_amt of an award while the award is still in
OFFERED state, silently update the status to ACCEPTED
*/
IF award_rec.award_status = 'OFFERED' THEN
l_get_accept_amt := NULL;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','setting the award status to accepted');
'igf.plsql.igf_aw_gen.update_award.debug',
'All the disbursements in award ' ||award_rec.award_id|| ' are cancelled. Cancelling the award.');
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_aw_gen.update_award.debug',
'Not updtaing award paid_amt with sum of disb paid_amt bcoz this is an FWS award. Retaining existing award paid amt. Award Id= '
||award_rec.award_id);
igf_aw_award_pkg.update_row ( x_mode => 'R',
x_rowid => award_rec.row_id,
x_award_id => award_rec.award_id,
x_fund_id => award_rec.fund_id,
x_base_id => award_rec.base_id,
x_offered_amt => l_awd_off_amt,
x_accepted_amt => l_awd_acc_amt,
x_paid_amt => disb_rec.paid_amt,
x_packaging_type => award_rec.packaging_type,
x_batch_id => award_rec.batch_id,
x_manual_update => award_rec.manual_update,
x_rules_override => award_rec.rules_override,
x_award_date => award_rec.award_date,
x_award_status => l_awd_status,
x_attribute_category => award_rec.attribute_category,
x_attribute1 => award_rec.attribute1,
x_attribute2 => award_rec.attribute2,
x_attribute3 => award_rec.attribute3,
x_attribute4 => award_rec.attribute4,
x_attribute5 => award_rec.attribute5,
x_attribute6 => award_rec.attribute6,
x_attribute7 => award_rec.attribute7,
x_attribute8 => award_rec.attribute8,
x_attribute9 => award_rec.attribute9,
x_attribute10 => award_rec.attribute10,
x_attribute11 => award_rec.attribute11,
x_attribute12 => award_rec.attribute12,
x_attribute13 => award_rec.attribute13,
x_attribute14 => award_rec.attribute14,
x_attribute15 => award_rec.attribute15,
x_attribute16 => award_rec.attribute16,
x_attribute17 => award_rec.attribute17,
x_attribute18 => award_rec.attribute18,
x_attribute19 => award_rec.attribute19,
x_attribute20 => award_rec.attribute20,
x_rvsn_id => award_rec.rvsn_id,
x_alt_pell_schedule => award_rec.alt_pell_schedule,
x_award_number_txt => award_rec.award_number_txt,
x_legacy_record_flag => NULL,
x_adplans_id => award_rec.adplans_id,
x_lock_award_flag => award_rec.lock_award_flag,
x_app_trans_num_txt => award_rec.app_trans_num_txt,
x_awd_proc_status_code => award_rec.awd_proc_status_code,
x_notification_status_code => award_rec.notification_status_code,
x_notification_status_date => award_rec.notification_status_date,
x_called_from => l_v_called_from,
x_publish_in_ss_flag => award_rec.publish_in_ss_flag
);
update_fabase_awds(award_rec.base_id, 'REVISED');
fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_AWARD' || ' ' || SQLERRM);
END update_award;
SELECT terms.adterms_id
FROM igf_aw_dp_terms terms
WHERE terms.adplans_id = p_adplans_id;
SELECT terms.adterms_id
FROM igf_aw_dp_terms terms
WHERE terms.adplans_id = p_adplans_id
AND NOT EXISTS (
SELECT 'x'
FROM igf_aw_dp_teach_prds tech
WHERE tech.adterms_id = terms.adterms_id
);
SELECT TO_NUMBER(LTRIM(RTRIM(str)))
FROM DUAL;
SELECT TO_CHAR(MIN(status_order)) min_award_status, MAX(notification_status_date) notification_status_date
FROM
(SELECT
awd.award_id,
notification_status_code,
notification_status_date,
DECODE(AWD.notification_status_code,
NULL, 99,
'R', 1,
'D', 2,
'S', 3,
'F', 3
) STATUS_ORDER
FROM
IGF_AW_AWARD_all AWD,
IGF_AW_FUND_MAST FMAST
WHERE
FMAST.CI_CAL_TYPE = cp_ci_cal_type AND
FMAST.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
AWD.FUND_ID = FMAST.FUND_ID AND
AWD.BASE_ID = cp_base_id AND
NOT EXISTS
(SELECT DISB.LD_CAL_TYPE, DISB.LD_SEQUENCE_NUMBER
FROM IGF_AW_AWD_DISB DISB
WHERE
DISB.AWARD_ID = AWD.AWARD_ID
MINUS
SELECT LD_CAL_TYPE, LD_SEQUENCE_NUMBER
FROM IGF_AW_AWD_PRD_TERM APT
WHERE APT.CI_CAL_TYPE = cp_ci_cal_type AND
APT.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
APT.award_prd_cd = NVL(cp_award_prd_code, award_prd_cd))) temp;
SELECT val_data FROM
(
SELECT 1 key_data, 'R' val_data from dual union all
SELECT 2 key_data, 'D' val_data from dual union all
SELECT 3 key_data, 'S' val_data from dual union all
SELECT 4 key_data, 'F' val_data from dual
) MAPPING
WHERE key_data = cp_mapped_value;
|| fall under the given awarding period. All awards will be updated to carry supplied
|| Notification Status and Notification Status Date.
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
PROCEDURE update_notification_status (
p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
p_notification_status_code IN igf_aw_award_all.notification_status_code%TYPE,
p_notification_status_date IN igf_aw_award_all.notification_status_date%TYPE,
p_called_from IN VARCHAR2
) AS
-- mnade 5/24/2005 using the base query from IGFAW016.pld for the same C_PROCESS_STATUS
CURSOR c_awards (cp_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
cp_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
cp_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
IS
SELECT
awd.award_id
FROM
IGF_AW_AWARD_all AWD,
IGF_AW_FUND_MAST FMAST
WHERE
FMAST.CI_CAL_TYPE = cp_ci_cal_type AND
FMAST.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
AWD.FUND_ID = FMAST.FUND_ID AND
AWD.BASE_ID = cp_base_id AND
NOT EXISTS
(SELECT DISB.LD_CAL_TYPE, DISB.LD_SEQUENCE_NUMBER
FROM IGF_AW_AWD_DISB DISB
WHERE
DISB.AWARD_ID = AWD.AWARD_ID
MINUS
SELECT LD_CAL_TYPE, LD_SEQUENCE_NUMBER
FROM IGF_AW_AWD_PRD_TERM APT
WHERE APT.CI_CAL_TYPE = cp_ci_cal_type AND
APT.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
APT.award_prd_cd = NVL(cp_award_prd_code, award_prd_cd));
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_cal_type - ' || p_cal_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_seq_num - ' || p_seq_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_awarding_period - ' || p_awarding_period);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_base_id - ' || p_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_code - ' || p_notification_status_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_date - ' || p_notification_status_date);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_awarding_period - ' || p_awarding_period);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_called_from - ' || p_called_from);
LOOP -- Award Noification Status Update
FETCH c_awards INTO award_rec;
update_awd_notification_status (
p_award_id => award_rec.award_id,
p_notification_status_code => p_notification_status_code,
p_notification_status_date => p_notification_status_date,
p_called_from => p_called_from
);
END LOOP; -- END Award Noification Status Update
fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_NOTIFICATION_STATUS ' || SQLERRM);
END update_notification_status;
|| Updates the Notification Status and Notification Status Date for given award.
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
PROCEDURE update_awd_notification_status (
p_award_id IN igf_aw_award_all.award_id%TYPE,
p_notification_status_code IN igf_aw_award_all.notification_status_code%TYPE,
p_notification_status_date IN igf_aw_award_all.notification_status_date%TYPE,
p_called_from IN VARCHAR2
) AS
-- mnade 5/24/2005 using the base query from IGFAW016.pld for the same C_PROCESS_STATUS
CURSOR c_award (cp_award_id igf_aw_award_all.award_id%TYPE)
IS
SELECT
awd.rowid row_id, awd.*
FROM
IGF_AW_AWARD_all awd
WHERE
awd.award_id = cp_award_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_award_id - ' || p_award_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_code - ' || p_notification_status_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_date - ' || p_notification_status_date);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_called_from - ' || p_called_from);
igf_aw_award_pkg.update_row ( x_mode => 'R',
x_rowid => award_rec.row_id,
x_award_id => award_rec.award_id,
x_fund_id => award_rec.fund_id,
x_base_id => award_rec.base_id,
x_offered_amt => award_rec.offered_amt,
x_accepted_amt => award_rec.accepted_amt,
x_paid_amt => award_rec.paid_amt,
x_packaging_type => award_rec.packaging_type,
x_batch_id => award_rec.batch_id,
x_manual_update => award_rec.manual_update,
x_rules_override => award_rec.rules_override,
x_award_date => award_rec.award_date,
x_award_status => award_rec.award_status,
x_attribute_category => award_rec.attribute_category,
x_attribute1 => award_rec.attribute1,
x_attribute2 => award_rec.attribute2,
x_attribute3 => award_rec.attribute3,
x_attribute4 => award_rec.attribute4,
x_attribute5 => award_rec.attribute5,
x_attribute6 => award_rec.attribute6,
x_attribute7 => award_rec.attribute7,
x_attribute8 => award_rec.attribute8,
x_attribute9 => award_rec.attribute9,
x_attribute10 => award_rec.attribute10,
x_attribute11 => award_rec.attribute11,
x_attribute12 => award_rec.attribute12,
x_attribute13 => award_rec.attribute13,
x_attribute14 => award_rec.attribute14,
x_attribute15 => award_rec.attribute15,
x_attribute16 => award_rec.attribute16,
x_attribute17 => award_rec.attribute17,
x_attribute18 => award_rec.attribute18,
x_attribute19 => award_rec.attribute19,
x_attribute20 => award_rec.attribute20,
x_rvsn_id => award_rec.rvsn_id,
x_alt_pell_schedule => award_rec.alt_pell_schedule,
x_award_number_txt => award_rec.award_number_txt,
x_legacy_record_flag => NULL,
x_adplans_id => award_rec.adplans_id,
x_lock_award_flag => award_rec.lock_award_flag,
x_app_trans_num_txt => award_rec.app_trans_num_txt,
x_awd_proc_status_code => award_rec.awd_proc_status_code,
x_notification_status_code => p_notification_status_code,
x_notification_status_date => p_notification_status_date,
x_called_from => p_called_from,
x_publish_in_ss_flag => award_rec.publish_in_ss_flag
);
fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_AWD_NOTIFICATION_STATUS ' || SQLERRM);
END update_awd_notification_status;
SELECT
user_concurrent_program_name
FROM
FND_CONCURRENT_PROGRAMS_TL
WHERE
APPLICATION_ID = 8406 AND
CONCURRENT_PROGRAM_ID = p_program_id AND
LANGUAGE = userenv('LANG');
SELECT
COUNT(awd.award_id) lock_count
FROM
igf_aw_award_all awd,
igf_aw_awd_disb_all disb,
igf_aw_awd_prd_term apt
WHERE
disb.award_id = awd.award_id
AND disb.ld_cal_type = apt.ld_cal_type
AND disb.ld_sequence_number = apt.ld_sequence_number
AND NVL(awd.lock_award_flag, 'N') = 'Y'
AND awd.fund_id = cp_fund_id
AND apt.ci_cal_type = cp_cal_type
AND apt.ci_sequence_number = cp_seq_num
AND apt.award_prd_cd = NVL(cp_awarding_period, award_prd_cd)
AND awd.base_id = cp_base_id;