[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update mode provided key information is not changed. |
| |
| HISTORY |
| psssahni 29-Oct-2004 Bug 3416863 |
| Added validation to run the process for |
| awards having Ready to Send status only in |
| case of COD-XML processing. |
| Added validation to check the combination of|
| attending and reporting pell id |
| svuppala 14-Oct-2004 Bug # 3416936 |
| Modified TBH call to addeded field |
| Eligible for Additional Unsubsidized Loans |
| |
| ugummall 20-Apr-2004 Bug 3558751. Added lookup enabled_flag |
| check for all the cursors using lookups. |
| veramach 10-Dec-2003 Removed cursor c_pell_setup and related |
| code |
| rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05 |
| added new parameter |
| award_fmly_contribution_type to |
| igf_ap_fa_base_rec_pkg.update_row |
| Changed the cursor c_isir_details |
| brajendr 04-Jul-2002 Bug # 2991359 Creation of file |
| bkkumar 13-Aug-2003 Bug# 3089841 Added one transaction_num |
| validation and changed the c_isir_details |
| cursor. |
| nsidana 10/31/2003 Multiple FA offices build : Added new fn |
| to derive the REP PELL ID. |
| gvarapra 14-sep-2004 FA138 - ISIR Enhancements |
| Changed arguments in call to |
| IGF_AP_FA_BASE_RECORD_PKG. |
*=======================================================================*/
-- Get the details of Pell Origination Interface records
CURSOR c_pell_orig_int(
cp_batch_num igf_aw_li_pell_ints.batch_num%TYPE,
cp_ci_alternate_code igf_aw_li_pell_ints.ci_alternate_code%TYPE,
cp_orig_status_code igf_aw_li_pell_ints.orig_status_code%TYPE
) IS
SELECT ROWID row_id,
batch_num batch_num,
TRIM(ci_alternate_code) ci_alternate_code,
TRIM(person_number) person_number,
TRIM(award_number_txt) award_number_txt,
TRIM(origination_id_txt) origination_id_txt,
TRIM(import_status_type) import_status_type,
TRIM(orig_send_batch_id_txt) orig_send_batch_id_txt,
TRIM(transaction_num_txt) transaction_num_txt,
efc_amt efc_amt,
TRIM(verification_status_code) verification_status_code,
secondary_efc_amt secondary_efc_amt,
TRIM(secondary_efc_code) secondary_efc_code,
pell_award_amt pell_award_amt,
TRIM(enrollment_status_flag) enrollment_status_flag,
enrollment_date enrollment_date,
pell_coa_amt pell_coa_amt,
TRIM(academic_calendar_cd) academic_calendar_cd,
TRIM(payment_method_code) payment_method_code,
TRIM(incrcd_fed_pell_rcp_code) incrcd_fed_pell_rcp_code,
TRIM(attending_campus_cd ) attending_campus_cd,
TRIM(orig_status_code) orig_status_code,
orig_status_date orig_status_date,
TRIM(orig_ed_use_flags_txt) orig_ed_use_flags_txt,
ft_sch_pell_amt ft_sch_pell_amt,
prev_accpt_efc_amt prev_accpt_efc_amt,
TRIM(prev_accpt_tran_num_txt) prev_accpt_tran_num_txt,
TRIM(prev_accpt_sec_efc_cd) prev_accpt_sec_efc_cd,
prev_accpt_coa_amt prev_accpt_coa_amt,
TRIM(orig_reject_codes_txt) orig_reject_codes_txt,
wk_inst_time_calc_pymt_num wk_inst_time_calc_pymt_num,
wk_int_time_prg_def_yr_num wk_int_time_prg_def_yr_num,
cr_clk_hrs_prds_sch_yr_num cr_clk_hrs_prds_sch_yr_num,
cr_clk_hrs_acad_yr_num cr_clk_hrs_acad_yr_num,
TRIM(inst_cross_ref_cd) inst_cross_ref_cd,
TRIM(low_tution_fee_cd) low_tution_fee_cd,
pending_amt pending_amt,
rfms_process_date rfms_process_date,
rfms_ack_date rfms_ack_date,
TRIM(import_record_type) import_record_type,
TRIM(ope_cd) ope_cd,
pell_alt_exp_amt pell_alt_exp_amt,
atd_entity_id_txt atd_entity_id_txt,
rep_entity_id_txt rep_entity_id_txt
FROM igf_aw_li_pell_ints pell
WHERE batch_num = cp_batch_num
AND TRIM(ci_alternate_code) = cp_ci_alternate_code
AND TRIM(import_status_type) IN ('U','R')
AND NVL (TRIM (orig_status_code), 'x') = NVL (cp_orig_status_code, NVL (TRIM
(orig_status_code), 'x'))
ORDER BY ci_alternate_code, person_number, award_number_txt;
SELECT ROWID row_id,
TRIM(ci_alternate_code) ci_alternate_code,
TRIM(person_number) person_number,
TRIM(award_number_txt) award_number_txt,
TRIM(origination_id_txt) origination_id_txt,
disbursement_num disbursement_num,
TRIM(disb_ack_act_flag) disb_ack_act_flag,
disb_status_date disb_status_date,
accpt_disb_date accpt_disb_date,
disb_accpt_amt disb_accpt_amt,
TRIM(disbursement_sign_flag) disbursement_sign_flag,
disb_ytd_amt disb_ytd_amt,
pymt_prd_start_date pymt_prd_start_date,
accpt_pymt_prd_start_date accpt_pymt_prd_start_date,
TRIM(edit_codes_txt) edit_codes_txt,
TRIM(disburse_batch_id_txt) disburse_batch_id_txt,
disburse_batch_process_date disburse_batch_process_date,
disburse_batch_ack_date disburse_batch_ack_date,
TRIM(ed_use_flags) ed_use_flags
FROM igf_aw_li_pdb_ints
WHERE TRIM(ci_alternate_code) = cp_ci_alternate_code
AND TRIM(person_number) = cp_person_number
AND TRIM(award_number_txt) = cp_award_number_txt
AND TRIM(origination_id_txt) = cp_origination_id_txt
ORDER BY ci_alternate_code, person_number, award_number_txt, origination_id_txt, disbursement_num;
SELECT i.original_ssn,
RPAD(i.orig_name_id,2,' ') orig_name_id,
i.date_of_birth,
i.last_name,
i.first_name,
i.middle_initial,
i.current_ssn,
DECODE(f.award_fmly_contribution_type,
'2',i.secondary_efc,
i.primary_efc) paid_efc
FROM igf_ap_isir_matched_all i,
igf_ap_fa_base_rec_all f
WHERE i.base_id = cp_base_id
AND f.base_id = i.base_id
AND i.system_record_type = 'ORIGINAL'
AND TO_NUMBER(i.transaction_num) = TO_NUMBER(cp_transaction_num);
g_delete_flag VARCHAR2(1);
SELECT lkups.meaning, lkups.lookup_code
FROM igf_lookups_view lkups
WHERE lkups.lookup_type = 'IGF_GE_PARAMETERS'
AND lkups.lookup_code IN ('AWARD_YEAR','BATCH_NUMBER','DELETE_FLAG','PARAMETER_PASS')
AND lkups.enabled_flag = 'Y' ;
l_delete_flag_pmpt igf_lookups_view.meaning%TYPE;
ELSIF (parameter_rec.lookup_code ='DELETE_FLAG') THEN
l_delete_flag_pmpt := TRIM(parameter_rec.meaning);
fnd_file.put_line(fnd_file.log, RPAD(l_delete_flag_pmpt,40) || ' : '|| igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO', p_del_ind));
FUNCTION update_fa_base_data(
p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
p_coa_pell igf_ap_fa_base_rec_all.coa_pell%TYPE,
p_pell_alt_expense igf_ap_fa_base_rec_all.pell_alt_expense%TYPE
) RETURN VARCHAR2 AS
/*
|| Created By : brajendr
|| Created On : 10-Jul-2003
|| Purpose : Updates the FA Base record with the Pel COA and Pell Alternate expenses
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
||
|| rasahoo 17-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
*/
-- Get base record deails
CURSOR c_get_base_rec_dtls(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
) IS
SELECT ROWID row_id, base.*
FROM igf_ap_fa_base_rec_all base
WHERE base.base_id = cp_base_id;
l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure update_fa_base_data :: ';
igf_ap_fa_base_rec_pkg.update_row(
x_Mode => 'R' ,
x_rowid => lc_get_base_rec_dtls.row_id ,
x_base_id => lc_get_base_rec_dtls.base_id ,
x_ci_cal_type => lc_get_base_rec_dtls.ci_cal_type ,
x_person_id => lc_get_base_rec_dtls.person_id ,
x_ci_sequence_number => lc_get_base_rec_dtls.ci_sequence_number ,
x_org_id => lc_get_base_rec_dtls.org_id ,
x_coa_pending => lc_get_base_rec_dtls.coa_pending ,
x_verification_process_run => lc_get_base_rec_dtls.verification_process_run ,
x_inst_verif_status_date => lc_get_base_rec_dtls.inst_verif_status_date ,
x_manual_verif_flag => lc_get_base_rec_dtls.manual_verif_flag ,
x_fed_verif_status => lc_get_base_rec_dtls.fed_verif_status ,
x_fed_verif_status_date => lc_get_base_rec_dtls.fed_verif_status_date ,
x_inst_verif_status => lc_get_base_rec_dtls.inst_verif_status ,
x_nslds_eligible => lc_get_base_rec_dtls.nslds_eligible ,
x_ede_correction_batch_id => lc_get_base_rec_dtls.ede_correction_batch_id ,
x_fa_process_status_date => lc_get_base_rec_dtls.fa_process_status_date ,
x_isir_corr_status => lc_get_base_rec_dtls.isir_corr_status ,
x_isir_corr_status_date => lc_get_base_rec_dtls.isir_corr_status_date ,
x_isir_status => lc_get_base_rec_dtls.isir_status ,
x_isir_status_date => lc_get_base_rec_dtls.isir_status_date ,
x_coa_code_f => lc_get_base_rec_dtls.coa_code_f ,
x_coa_code_i => lc_get_base_rec_dtls.coa_code_i ,
x_coa_f => lc_get_base_rec_dtls.coa_f ,
x_coa_i => lc_get_base_rec_dtls.coa_i ,
x_disbursement_hold => lc_get_base_rec_dtls.disbursement_hold ,
x_fa_process_status => lc_get_base_rec_dtls.fa_process_status ,
x_notification_status => lc_get_base_rec_dtls.notification_status ,
x_notification_status_date => lc_get_base_rec_dtls.notification_status_date ,
x_packaging_status => lc_get_base_rec_dtls.packaging_status,
x_packaging_status_date => lc_get_base_rec_dtls.packaging_status_date,
x_total_package_accepted => lc_get_base_rec_dtls.total_package_accepted ,
x_total_package_offered => lc_get_base_rec_dtls.total_package_offered ,
x_admstruct_id => lc_get_base_rec_dtls.admstruct_id ,
x_admsegment_1 => lc_get_base_rec_dtls.admsegment_1 ,
x_admsegment_2 => lc_get_base_rec_dtls.admsegment_2 ,
x_admsegment_3 => lc_get_base_rec_dtls.admsegment_3 ,
x_admsegment_4 => lc_get_base_rec_dtls.admsegment_4 ,
x_admsegment_5 => lc_get_base_rec_dtls.admsegment_5 ,
x_admsegment_6 => lc_get_base_rec_dtls.admsegment_6 ,
x_admsegment_7 => lc_get_base_rec_dtls.admsegment_7 ,
x_admsegment_8 => lc_get_base_rec_dtls.admsegment_8 ,
x_admsegment_9 => lc_get_base_rec_dtls.admsegment_9 ,
x_admsegment_10 => lc_get_base_rec_dtls.admsegment_10 ,
x_admsegment_11 => lc_get_base_rec_dtls.admsegment_11 ,
x_admsegment_12 => lc_get_base_rec_dtls.admsegment_12 ,
x_admsegment_13 => lc_get_base_rec_dtls.admsegment_13 ,
x_admsegment_14 => lc_get_base_rec_dtls.admsegment_14 ,
x_admsegment_15 => lc_get_base_rec_dtls.admsegment_15 ,
x_admsegment_16 => lc_get_base_rec_dtls.admsegment_16 ,
x_admsegment_17 => lc_get_base_rec_dtls.admsegment_17 ,
x_admsegment_18 => lc_get_base_rec_dtls.admsegment_18 ,
x_admsegment_19 => lc_get_base_rec_dtls.admsegment_19 ,
x_admsegment_20 => lc_get_base_rec_dtls.admsegment_20 ,
x_packstruct_id => lc_get_base_rec_dtls.packstruct_id ,
x_packsegment_1 => lc_get_base_rec_dtls.packsegment_1 ,
x_packsegment_2 => lc_get_base_rec_dtls.packsegment_2 ,
x_packsegment_3 => lc_get_base_rec_dtls.packsegment_3 ,
x_packsegment_4 => lc_get_base_rec_dtls.packsegment_4 ,
x_packsegment_5 => lc_get_base_rec_dtls.packsegment_5 ,
x_packsegment_6 => lc_get_base_rec_dtls.packsegment_6 ,
x_packsegment_7 => lc_get_base_rec_dtls.packsegment_7 ,
x_packsegment_8 => lc_get_base_rec_dtls.packsegment_8 ,
x_packsegment_9 => lc_get_base_rec_dtls.packsegment_9 ,
x_packsegment_10 => lc_get_base_rec_dtls.packsegment_10 ,
x_packsegment_11 => lc_get_base_rec_dtls.packsegment_11 ,
x_packsegment_12 => lc_get_base_rec_dtls.packsegment_12 ,
x_packsegment_13 => lc_get_base_rec_dtls.packsegment_13 ,
x_packsegment_14 => lc_get_base_rec_dtls.packsegment_14 ,
x_packsegment_15 => lc_get_base_rec_dtls.packsegment_15 ,
x_packsegment_16 => lc_get_base_rec_dtls.packsegment_16 ,
x_packsegment_17 => lc_get_base_rec_dtls.packsegment_17 ,
x_packsegment_18 => lc_get_base_rec_dtls.packsegment_18 ,
x_packsegment_19 => lc_get_base_rec_dtls.packsegment_19 ,
x_packsegment_20 => lc_get_base_rec_dtls.packsegment_20 ,
x_miscstruct_id => lc_get_base_rec_dtls.miscstruct_id ,
x_miscsegment_1 => lc_get_base_rec_dtls.miscsegment_1 ,
x_miscsegment_2 => lc_get_base_rec_dtls.miscsegment_2 ,
x_miscsegment_3 => lc_get_base_rec_dtls.miscsegment_3 ,
x_miscsegment_4 => lc_get_base_rec_dtls.miscsegment_4 ,
x_miscsegment_5 => lc_get_base_rec_dtls.miscsegment_5 ,
x_miscsegment_6 => lc_get_base_rec_dtls.miscsegment_6 ,
x_miscsegment_7 => lc_get_base_rec_dtls.miscsegment_7 ,
x_miscsegment_8 => lc_get_base_rec_dtls.miscsegment_8 ,
x_miscsegment_9 => lc_get_base_rec_dtls.miscsegment_9 ,
x_miscsegment_10 => lc_get_base_rec_dtls.miscsegment_10 ,
x_miscsegment_11 => lc_get_base_rec_dtls.miscsegment_11 ,
x_miscsegment_12 => lc_get_base_rec_dtls.miscsegment_12 ,
x_miscsegment_13 => lc_get_base_rec_dtls.miscsegment_13 ,
x_miscsegment_14 => lc_get_base_rec_dtls.miscsegment_14 ,
x_miscsegment_15 => lc_get_base_rec_dtls.miscsegment_15 ,
x_miscsegment_16 => lc_get_base_rec_dtls.miscsegment_16 ,
x_miscsegment_17 => lc_get_base_rec_dtls.miscsegment_17 ,
x_miscsegment_18 => lc_get_base_rec_dtls.miscsegment_18 ,
x_miscsegment_19 => lc_get_base_rec_dtls.miscsegment_19 ,
x_miscsegment_20 => lc_get_base_rec_dtls.miscsegment_20 ,
x_prof_judgement_flg => lc_get_base_rec_dtls.prof_judgement_flg ,
x_nslds_data_override_flg => lc_get_base_rec_dtls.nslds_data_override_flg ,
x_target_group => lc_get_base_rec_dtls.target_group ,
x_coa_fixed => lc_get_base_rec_dtls.coa_fixed ,
x_coa_pell => p_coa_pell ,
x_profile_status => lc_get_base_rec_dtls.profile_status ,
x_profile_status_date => lc_get_base_rec_dtls.profile_status_date ,
x_profile_fc => lc_get_base_rec_dtls.profile_fc ,
x_manual_disb_hold => lc_get_base_rec_dtls.manual_disb_hold,
x_pell_alt_expense => p_pell_alt_expense,
x_assoc_org_num => lc_get_base_rec_dtls.assoc_org_num,
x_award_fmly_contribution_type => lc_get_base_rec_dtls.award_fmly_contribution_type,
x_isir_locked_by => lc_get_base_rec_dtls.isir_locked_by,
x_adnl_unsub_loan_elig_flag => lc_get_base_rec_dtls.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => lc_get_base_rec_dtls.lock_awd_flag,
x_lock_coa_flag => lc_get_base_rec_dtls.lock_coa_flag
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.update_fa_base_data.debug', l_debug_str || ' Sucssfully updated ');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.update_fa_base_data.exception', l_debug_str || SQLERRM );
fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.UPDATE_FA_BASE_DATA');
END update_fa_base_data;
FUNCTION delete_existing_pell_rec(
p_origination_id igf_gr_rfms_all.origination_id%TYPE,
p_cal_type igf_gr_rfms_all.ci_cal_type%TYPE,
p_seq_number igf_gr_rfms_all.ci_sequence_number%TYPE
) RETURN VARCHAR2 AS
/*
|| Created By : brajendr
|| Created On : 10-Jul-2003
|| Purpose : Deletes the exitsing pell legacy records from the production tables
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- Check whether is there any pell origination record present for the context information
CURSOR c_chk_pell_orig(
cp_origination_id igf_gr_rfms_all.origination_id%TYPE,
cp_cal_type igf_gr_rfms_all.ci_cal_type%TYPE,
cp_seq_number igf_gr_rfms_all.ci_sequence_number%TYPE
) IS
SELECT ROWID row_id, rfms.rfmb_id
FROM igf_gr_rfms_all rfms
WHERE rfms.origination_id = cp_origination_id
AND rfms.ci_cal_type = cp_cal_type
AND rfms.ci_sequence_number = cp_seq_number
AND NVL(rfms.legacy_record_flag,'N') = 'Y';
SELECT ROWID row_id, pdb.rfmd_id, pdb.rfmb_id
FROM igf_gr_rfms_disb_all pdb
WHERE pdb.origination_id = cp_origination_id;
l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure delete_existing_pell_rec :: ';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.delete_existing_pell_rec.debug', l_debug_str);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.delete_existing_pell_rec.debug', ' Deleting Pell Disb : ' || lc_get_pell_disb.rfmd_id);
igf_gr_rfms_disb_pkg.delete_row(lc_get_pell_disb.row_id);
igf_gr_rfms_pkg.delete_row(lc_chk_pell_orig.row_id);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.delete_existing_pell_rec.exception', l_debug_str || SQLERRM );
fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.DELETE_EXISTING_PELL_REC');
END delete_existing_pell_rec;
SELECT pb.rfmb_id
FROM igf_gr_rfms_batch_all pb
WHERE pb.batch_id = cp_batch_id;
igf_gr_rfms_batch_pkg.insert_row(
x_rowid => l_row_id,
x_rfmb_id => l_rfmb_id,
x_batch_id => p_pell_disb_int.disburse_batch_id_txt,
x_data_rec_length => 100,
x_ope_id => p_ope_cd,
x_software_providor => NULL,
x_rfms_process_dt => p_pell_disb_int.disburse_batch_process_date,
x_rfms_ack_dt => p_pell_disb_int.disburse_batch_ack_date,
x_rfms_ack_batch_id => l_rfms_ack_batch_id,
x_reject_reason => NULL,
x_mode => 'R'
);
igf_gr_rfms_disb_pkg.insert_row(
x_mode => 'R',
x_rowid => l_row_id,
x_rfmd_id => l_rfmd_id,
x_origination_id => p_pell_disb_int.origination_id_txt,
x_disb_ref_num => p_pell_disb_int.disbursement_num,
x_disb_dt => p_disb_date,
x_disb_amt => NVL(p_pell_disb_int.disb_accpt_amt,p_disb_amt), -- If the disb ack status is R or N then, get the amt from awd disb
x_db_cr_flag => l_db_cr_flag,
x_disb_ack_act_status => p_pell_disb_int.disb_ack_act_flag,
x_disb_status_dt => p_pell_disb_int.disb_status_date,
x_accpt_disb_dt => p_pell_disb_int.accpt_disb_date,
x_disb_accpt_amt => p_pell_disb_int.disb_accpt_amt,
x_accpt_db_cr_flag => p_pell_disb_int.disbursement_sign_flag,
x_disb_ytd_amt => p_pell_disb_int.disb_ytd_amt,
x_pymt_prd_start_dt => p_pell_disb_int.pymt_prd_start_date,
x_accpt_pymt_prd_start_dt => p_pell_disb_int.accpt_pymt_prd_start_date,
x_edit_code => p_pell_disb_int.edit_codes_txt,
x_rfmb_id => p_rfmb_id,
x_ed_use_flags => p_pell_disb_int.ed_use_flags
);
UPDATE igf_aw_li_pdb_ints
SET last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE ci_alternate_code = p_pell_disb_int.ci_alternate_code
AND person_number = p_pell_disb_int.person_number
AND award_number_txt = p_pell_disb_int.award_number_txt
AND origination_id_txt = p_pell_disb_int.origination_id_txt
AND disbursement_num = p_pell_disb_int.disbursement_num;
SELECT disb.disb_date, disb.disb_gross_amt, disb.disb_net_amt
FROM igf_aw_awd_disb_all disb
WHERE disb.award_id = cp_award_id
AND disb.disb_num = cp_disb_num
AND disb.trans_type = 'A';
SELECT pb.rfmb_id
FROM igf_gr_rfms_batch_all pb
WHERE pb.batch_id = cp_batch_id;
igf_gr_rfms_batch_pkg.insert_row(
x_rowid => l_row_id,
x_rfmb_id => l_rfmb_id,
x_batch_id => p_pell_orig_int.orig_send_batch_id_txt,
x_data_rec_length => 300,
x_ope_id => p_pell_orig_int.ope_cd,
x_software_providor => NULL,
x_rfms_process_dt => p_pell_orig_int.rfms_process_date,
x_rfms_ack_dt => p_pell_orig_int.rfms_ack_date,
x_rfms_ack_batch_id => l_rfms_ack_batch_id,
x_reject_reason => NULL,
x_mode => 'R'
);
fnd_message.set_name('IGF','IGF_GR_LI_BINSERT_FAIL');
SELECT adisb1.disb_num, adisb2.disb_date
FROM igf_aw_awd_disb adisb1, igf_aw_awd_disb adisb2
WHERE adisb1.award_id = cp_award_id
AND adisb1.disb_num IN ( SELECT MAX(adisb11.disb_num)
FROM igf_aw_awd_disb adisb11
WHERE adisb11.award_id = adisb1.award_id
)
AND adisb1.award_id = adisb2.award_id
AND adisb2.disb_num IN ( SELECT MIN(adisb11.disb_num)
FROM igf_aw_awd_disb adisb11
WHERE adisb11.award_id = adisb2.award_id
);
igf_gr_rfms_pkg.insert_row(
x_rowid => l_row_id,
x_origination_id => p_pell_orig_int.origination_id_txt,
x_ci_cal_type => g_cal_type,
x_ci_sequence_number => g_seq_number,
x_base_id => g_base_id,
x_award_id => g_award_id,
x_rfmb_id => p_rfmb_id,
x_sys_orig_ssn => p_isir_details.original_ssn,
x_sys_orig_name_cd => p_isir_details.orig_name_id,
x_transaction_num => p_pell_orig_int.transaction_num_txt,
x_efc => p_pell_orig_int.efc_amt,
x_ver_status_code => p_pell_orig_int.verification_status_code,
x_secondary_efc => p_pell_orig_int.secondary_efc_amt,
x_secondary_efc_cd => p_pell_orig_int.secondary_efc_code,
x_pell_amount => p_pell_orig_int.pell_award_amt,
x_pell_profile => NULL,
x_enrollment_status => p_pell_orig_int.enrollment_status_flag,
x_enrollment_dt => p_pell_orig_int.enrollment_date,
x_coa_amount => p_pell_orig_int.pell_coa_amt,
x_academic_calendar => p_pell_orig_int.academic_calendar_cd,
x_payment_method => p_pell_orig_int.payment_method_code,
x_total_pymt_prds => lc_pymt_prds_n_date.disb_num,
x_incrcd_fed_pell_rcp_cd => p_pell_orig_int.incrcd_fed_pell_rcp_code,
x_attending_campus_id => g_attending_pell_cd,
x_est_disb_dt1 => lc_pymt_prds_n_date.disb_date,
x_orig_action_code => p_pell_orig_int.orig_status_code,
x_orig_status_dt => p_pell_orig_int.orig_status_date,
x_orig_ed_use_flags => p_pell_orig_int.orig_ed_use_flags_txt,
x_ft_pell_amount => p_pell_orig_int.ft_sch_pell_amt,
x_prev_accpt_efc => p_pell_orig_int.prev_accpt_efc_amt,
x_prev_accpt_tran_no => p_pell_orig_int.prev_accpt_tran_num_txt,
x_prev_accpt_sec_efc_cd => p_pell_orig_int.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => p_pell_orig_int.prev_accpt_coa_amt,
x_orig_reject_code => p_pell_orig_int.orig_reject_codes_txt,
x_wk_inst_time_calc_pymt => p_pell_orig_int.wk_inst_time_calc_pymt_num,
x_wk_int_time_prg_def_yr => p_pell_orig_int.wk_int_time_prg_def_yr_num,
x_cr_clk_hrs_prds_sch_yr => p_pell_orig_int.cr_clk_hrs_prds_sch_yr_num,
x_cr_clk_hrs_acad_yr => p_pell_orig_int.cr_clk_hrs_acad_yr_num,
x_inst_cross_ref_cd => p_pell_orig_int.inst_cross_ref_cd,
x_low_tution_fee => p_pell_orig_int.low_tution_fee_cd,
x_rec_source => 'B',
x_pending_amount => p_pell_orig_int.pending_amt,
x_birth_dt => p_isir_details.date_of_birth,
x_last_name => p_isir_details.last_name,
x_first_name => p_isir_details.first_name,
x_middle_name => p_isir_details.middle_initial,
x_current_ssn => p_isir_details.current_ssn,
x_legacy_record_flag => 'Y',
x_mode => 'R',
x_reporting_pell_cd => g_reporting_pell_id,
x_rep_entity_id_txt => p_pell_orig_int.rep_entity_id_txt,
x_atd_entity_id_txt => p_pell_orig_int.atd_entity_id_txt,
x_note_message => NULL,
x_full_resp_code => NULL,
x_document_id_txt => NULL
);
|| Now it will select "Base_attendance_type_code" instead of "attendance_type_code"
|| Removed cursor "c_get_enrl_status" as it is no longer used.
|| ugummall 05-DEC-2003 Bug 3252832. FA 131 - COD Updates
|| Changed the logic for validating the enrollment status w.r.t. FA 131.
|| Added the cursor cur_get_attendance_type_code for above validation.
|| Removed the validation for Scheduled Pell Award.
|| rasahoo 02-Sep-2003 changed the cursor C_GET_ENRL_STATUS.
|| Removed the join with IGF_AP_FA_BASE_H
|| and got the DERIVED_ATTEND_TYPE from IGF_AP_GEN_001
|| and changed the data type of l_enrl_status from igf_ap_fa_base_h.derived_attend_type%TYPE
|| as part of FA-114(Obsoletion of FA base record History)
||
*/
-- Get the Award details for the refference award number if present
CURSOR c_awd_details(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_award_number igf_aw_award_all.award_number_txt%TYPE
) IS
SELECT awd.award_id, awd.offered_amt, awd.accepted_amt, awd.fund_id
FROM igf_aw_award_all awd
WHERE awd.base_id = cp_base_id
AND awd.award_number_txt IS NOT NULL
AND awd.award_number_txt = cp_award_number
AND awd.award_status <> 'SIMULATED';
SELECT rfms.origination_id
FROM igf_gr_rfms_all rfms
WHERE rfms.award_id = cp_award_id;
SELECT ad.disb_num, ad.disb_date, ad.disb_accepted_amt
FROM igf_aw_awd_disb_all ad
WHERE ad.award_id = cp_award_id
AND NOT EXISTS ( SELECT pdisb.disb_ref_num
FROM igf_gr_rfms_all porig, igf_gr_rfms_disb_all pdisb
WHERE porig.origination_id = pdisb.origination_id
AND ad.award_id = porig.award_id
AND ad.disb_num = pdisb.disb_ref_num);
SELECT base_attendance_type_code
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id
GROUP BY base_attendance_type_code;
fnd_message.set_name('IGF','IGF_GR_LI_OINSERT_DUP_AWID');
fnd_message.set_name('IGF','IGF_GR_LI_OINSERT_FAIL');
fnd_message.set_name('IGF','IGF_GR_LI_DINSERT_FAIL');
igf_gr_rfms_disb_pkg.insert_row(
x_mode => 'R',
x_rowid => l_row_id,
x_rfmd_id => l_rfmd_id,
x_origination_id => p_pell_orig_int.origination_id_txt,
x_disb_ref_num => lc_get_remain_disb.disb_num,
x_disb_dt => lc_get_remain_disb.disb_date,
x_disb_amt => lc_get_remain_disb.disb_accepted_amt,
x_db_cr_flag => l_db_cr_flag,
x_disb_ack_act_status => 'R',
x_disb_status_dt => TRUNC(SYSDATE),
x_accpt_disb_dt => NULL,
x_disb_accpt_amt => NULL,
x_accpt_db_cr_flag => NULL,
x_disb_ytd_amt => NULL,
x_pymt_prd_start_dt => NULL,
x_accpt_pymt_prd_start_dt => NULL,
x_edit_code => NULL,
x_rfmb_id => NULL,
x_ed_use_flags => NULL
);
SELECT atd.atd_entity_id_txt, rep.rep_entity_id_txt
FROM igf_gr_report_pell rep, igf_gr_attend_pell atd
WHERE rep.rcampus_id = atd.rcampus_id
AND atd.atd_entity_id_txt = p_atd_entity_id_txt
AND rep.rep_entity_id_txt = p_rep_entity_id_txt;
p_delete_flag IN VARCHAR2
) AS
/*
|| Created By : brajendr
|| Created On : 18-Jun-2003
|| Purpose : Main process imports the Pell data from the Legacy Pell interface table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
SELECT ca.alternate_code
FROM igs_ca_inst_all ca
WHERE ca.cal_type = cp_cal_type
AND ca.sequence_number = cp_seq_number;
SELECT bam.alternate_code, bam.award_year_status_code, bam.sys_award_year
FROM igf_ap_batch_aw_map_v bam
WHERE bam.ci_cal_type = cp_ci_cal_type
AND bam.ci_sequence_number = cp_ci_sequence_number;
SELECT 'x'
FROM DUAL
WHERE EXISTS(
SELECT 'x'
FROM igf_gr_reg_amts reg, igf_ap_batch_aw_map_all batch
WHERE batch.ci_cal_type = cp_ci_cal_type
AND batch.ci_sequence_number = cp_ci_sequence_number
AND batch.sys_award_year = reg.sys_awd_yr
)
AND EXISTS(
SELECT 'X'
FROM igf_gr_alt_amts alt, igf_ap_batch_aw_map_all batch
WHERE batch.ci_cal_type = cp_ci_cal_type
AND batch.ci_sequence_number = cp_ci_sequence_number
AND alt.sys_awd_yr = batch.sys_award_year
)
AND EXISTS(
SELECT 'X'
FROM igf_gr_tuition_fee_codes tfee, igf_ap_batch_aw_map_all batch
WHERE batch.ci_cal_type = cp_ci_cal_type
AND batch.ci_sequence_number = cp_ci_sequence_number
AND batch.sys_award_year = tfee.sys_awd_yr
)
AND EXISTS(
SELECT 'X'
FROM igf_ap_attend_map_all atm
WHERE atm.cal_type IS NOT NULL
AND atm.sequence_number IS NOT NULL
AND atm.cal_type = cp_ci_cal_type
AND atm.sequence_number = cp_ci_sequence_number);
l_delete_status VARCHAR2(1);
g_delete_flag := p_delete_flag;
log_parameters( lc_get_alternate_code.alternate_code, p_batch_num, p_delete_flag );
UPDATE igf_aw_li_pell_ints
SET import_status_type = 'E',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE batch_num = lc_pell_orig_int.batch_num
AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
UPDATE igf_aw_li_pell_ints
SET import_status_type = 'E',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE batch_num = lc_pell_orig_int.batch_num
AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
l_delete_status := 'E';
l_delete_status := delete_existing_pell_rec(
lc_pell_orig_int.origination_id_txt,
g_cal_type,
g_seq_number
);
IF l_delete_status = 'E' THEN
ROLLBACK TO SP_MAIN_PELL;
fnd_message.set_name('IGF','IGF_GR_LI_UPDATE_FAIL');
UPDATE igf_aw_li_pell_ints
SET import_status_type = 'E',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE batch_num = lc_pell_orig_int.batch_num
AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
UPDATE igf_aw_li_pell_ints
SET import_status_type = 'E',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE batch_num = lc_pell_orig_int.batch_num
AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
l_fabase_ret_status := update_fa_base_data( g_base_id, lc_pell_orig_int.pell_coa_amt, lc_pell_orig_int.pell_alt_exp_amt);
IF l_pell_import_status = 'I' AND g_delete_flag = 'Y' THEN
IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
l_debug_str := l_debug_str || ' : Deleting Pell Int rec : ';
DELETE igf_aw_li_pell_ints
WHERE batch_num = lc_pell_orig_int.batch_num
AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
DELETE igf_aw_li_pdb_ints
WHERE ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
UPDATE igf_aw_li_pell_ints
SET import_status_type = l_pell_import_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE batch_num = lc_pell_orig_int.batch_num
AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
AND person_number = lc_pell_orig_int.person_number
AND award_number_txt = lc_pell_orig_int.award_number_txt
AND origination_id_txt = lc_pell_orig_int.origination_id_txt;