[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igf_gr_rfms rfms
WHERE rfms.base_id = cp_base_id
AND rfms.orig_action_code in ('A','S')
AND rfms.transaction_num <> cp_transaction_num ;
SELECT
COUNT (disb_num)
FROM
igf_db_awd_disb_dtl
WHERE
p_award_id = award_id AND
p_disb_num = disb_num;
SELECT
alternate_code
FROM
igs_ca_inst
WHERE
cal_type = p_ci_cal_type AND
sequence_number = p_ci_sequence_number;
SELECT
description,
alternate_code
FROM
igs_ca_inst
WHERE
cal_type = p_ci_cal_type AND
sequence_number = p_ci_sequence_number;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id =
(
SELECT person_id
FROM
igf_ap_fa_base_rec
WHERE
base_id = p_base_id
);
SELECT person_number
FROM igf_ap_person_v
WHERE
person_id = p_person_id;
SELECT person_id
FROM igf_ap_fa_base_rec
WHERE
base_id = p_base_id;
SELECT person_number
FROM igs_pe_person_base_v
WHERE
person_id = p_person_id;
PROCEDURE insert_sys_holds ( p_award_id igf_aw_award_all.award_id%TYPE,
p_disb_num igf_aw_awd_disb_all.disb_num%TYPE,
p_hold igf_db_disb_holds_all.hold%TYPE)
IS
--------------------------------------------------------------------------------------------
--
-- sjadhav, 15Feb2002
--
-- This procedure puts hold on Planned Disbursements
-- This process can be modified to insert holds on Actual Disbursements as well
--
--------------------------------------------------------------------------------------------
CURSOR cur_get_adisb ( p_award_id igf_aw_award_all.award_id%TYPE,
p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
IS
SELECT
awd.award_id,awd.disb_num
FROM
igf_aw_awd_disb awd
WHERE
awd.award_id = p_award_id AND
awd.disb_num = NVL(p_disb_num,awd.disb_num) AND
awd.trans_type = 'P';
SELECT COUNT(hold_id) cnt
FROM
igf_db_disb_holds
WHERE
award_id = p_award_id AND
disb_num = p_disb_num AND
NVL(release_flag,'N') = 'N' AND
hold = p_hold;
igf_db_disb_holds_pkg.insert_row(x_rowid => lv_rowid,
x_hold_id => holds_rec.hold_id,
x_award_id => awd_rec.award_id,
x_disb_num => awd_rec.disb_num,
x_hold => p_hold,
x_hold_date => TRUNC(SYSDATE),
x_hold_type => 'SYSTEM',
x_release_date => NULL,
x_release_flag => 'N',
x_release_reason => NULL,
x_mode => 'R');
fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_SYS_HOLDS'|| ' ' || SQLERRM);
END insert_sys_holds;
SELECT ope_cd
FROM igf_gr_report_pell rpell
WHERE rpell.ci_cal_type = cp_ci_cal_type
AND rpell.ci_sequence_number = cp_ci_seq_num
AND rpell.reporting_pell_cd = cp_rep_pell_id;
igf_gr_rfms_batch_pkg.insert_row (
x_rowid => l_rowid,
x_rfmb_id => p_rfmb_id,
x_batch_id => p_batch_id,
x_data_rec_length => ln_data_rec_len,
x_ope_id => NULL,
x_software_providor => NULL,
x_rfms_process_dt => TRUNC(SYSDATE),
x_rfms_ack_dt => NULL,
x_rfms_ack_batch_id => NULL,
x_reject_reason => NULL,
x_mode => 'R');
RPAD(' ',6) || -- updated by RFMS
RPAD(' ',11) || -- updated by RFMS
RPAD(' ',1) || -- Accepted and corrected sign indicator
RPAD(' ',6) || -- Number of Duplicate Records, updated by RFMS
RPAD(' ',18); -- updated by RFMS
SELECT
record_data
FROM
igf_gr_load_file_t
WHERE gldr_id = 1
AND record_data LIKE 'GRANT HDR%'
AND file_type = p_file_type;
SELECT
gldr_id last_gldr_id,
record_data
FROM
igf_gr_load_file_t
WHERE
gldr_id = (SELECT MAX(gldr_id) FROM igf_gr_load_file_t) AND
record_data LIKE 'GRANT TLR%' AND
file_type = p_file_type;
SELECT *
FROM
igf_gr_rfms_batch
WHERE
batch_id = p_batch_id
FOR UPDATE OF rfms_ack_dt NOWAIT;
SELECT
*
FROM
igf_gr_rfms
WHERE
rfmb_id = p_rfmb_id AND
orig_action_code = 'S';
SELECT
*
FROM
igf_gr_rfms_disb
WHERE
rfmb_id = p_rfmb_id AND
disb_ack_act_status = 'S';
igf_gr_rfms_batch_pkg.update_row (
x_rowid => rfms_batch_rec.row_id,
x_rfmb_id => rfms_batch_rec.rfmb_id,
x_batch_id => rfms_batch_rec.batch_id,
x_data_rec_length => rfms_batch_rec.data_rec_length,
x_ope_id => rfms_batch_rec.ope_id,
x_software_providor => rfms_batch_rec.software_providor,
x_rfms_process_dt => fnd_date.string_to_date(l_rfms_process_dt,'YYYYMMDD'),
x_rfms_ack_dt => TRUNC(SYSDATE),
x_rfms_ack_batch_id => p_batch_id,
x_reject_reason => l_batch_rej_reason,
x_mode => 'R' );
igf_gr_rfms_pkg.update_row(
x_rowid => cur_get_rfms.row_id,
x_origination_id => cur_get_rfms.origination_id,
x_ci_cal_type => cur_get_rfms.ci_cal_type,
x_ci_sequence_number => cur_get_rfms.ci_sequence_number,
x_base_id => cur_get_rfms.base_id,
x_award_id => cur_get_rfms.award_id,
x_rfmb_id => NULL,
x_sys_orig_ssn => cur_get_rfms.sys_orig_ssn,
x_sys_orig_name_cd => cur_get_rfms.sys_orig_name_cd,
x_transaction_num => cur_get_rfms.transaction_num,
x_efc => igf_gr_gen.get_pell_efc(cur_get_rfms.base_id),
x_ver_status_code => cur_get_rfms.ver_status_code,
x_secondary_efc => cur_get_rfms.secondary_efc,
x_secondary_efc_cd => igf_gr_gen.get_pell_efc_code(cur_get_rfms.base_id),
x_pell_amount => cur_get_rfms.pell_amount,
x_pell_profile => cur_get_rfms.pell_profile,
x_enrollment_status => cur_get_rfms.enrollment_status,
x_enrollment_dt => cur_get_rfms.enrollment_dt,
x_coa_amount => cur_get_rfms.coa_amount,
x_academic_calendar => cur_get_rfms.academic_calendar,
x_payment_method => cur_get_rfms.payment_method,
x_total_pymt_prds => cur_get_rfms.total_pymt_prds,
x_incrcd_fed_pell_rcp_cd => cur_get_rfms.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => cur_get_rfms.attending_campus_id,
x_est_disb_dt1 => cur_get_rfms.est_disb_dt1,
x_orig_action_code => 'R',
x_orig_status_dt => cur_get_rfms.orig_status_dt,
x_orig_ed_use_flags => cur_get_rfms.orig_ed_use_flags,
x_ft_pell_amount => cur_get_rfms.ft_pell_amount,
x_prev_accpt_efc => cur_get_rfms.prev_accpt_efc,
x_prev_accpt_tran_no => cur_get_rfms.prev_accpt_tran_no,
x_prev_accpt_sec_efc_cd => cur_get_rfms.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => cur_get_rfms.prev_accpt_coa,
x_orig_reject_code => cur_get_rfms.orig_reject_code,
x_wk_inst_time_calc_pymt => cur_get_rfms.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => cur_get_rfms.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => cur_get_rfms.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => cur_get_rfms.cr_clk_hrs_acad_yr,
x_inst_cross_ref_cd => cur_get_rfms.inst_cross_ref_cd,
x_low_tution_fee => cur_get_rfms.low_tution_fee,
x_rec_source => cur_get_rfms.rec_source,
x_pending_amount => cur_get_rfms.pending_amount,
x_mode => 'R',
x_birth_dt => cur_get_rfms.birth_dt,
x_last_name => cur_get_rfms.last_name,
x_first_name => cur_get_rfms.first_name,
x_middle_name => cur_get_rfms.middle_name,
x_current_ssn => cur_get_rfms.current_ssn,
x_legacy_record_flag => NULL,
x_reporting_pell_cd => cur_get_rfms.rep_pell_id,
x_rep_entity_id_txt => cur_get_rfms.rep_entity_id_txt,
x_atd_entity_id_txt => cur_get_rfms.atd_entity_id_txt,
x_note_message => cur_get_rfms.note_message,
x_full_resp_code => cur_get_rfms.full_resp_code,
x_document_id_txt => cur_get_rfms.document_id_txt
);
igf_gr_rfms_disb_pkg.update_row (
x_rowid => cur_get_rfms_disb.row_id,
x_rfmd_id => cur_get_rfms_disb.rfmd_id ,
x_origination_id => cur_get_rfms_disb.origination_id,
x_disb_ref_num => cur_get_rfms_disb.disb_ref_num,
x_disb_dt => cur_get_rfms_disb.disb_dt,
x_disb_amt => cur_get_rfms_disb.disb_amt,
x_db_cr_flag => cur_get_rfms_disb.db_cr_flag,
x_disb_ack_act_status => 'R', -- record processed
x_disb_status_dt => cur_get_rfms_disb.disb_status_dt,
x_accpt_disb_dt => cur_get_rfms_disb.accpt_disb_dt ,
x_disb_accpt_amt => cur_get_rfms_disb.disb_accpt_amt,
x_accpt_db_cr_flag => cur_get_rfms_disb.accpt_db_cr_flag,
x_disb_ytd_amt => cur_get_rfms_disb.disb_ytd_amt,
x_pymt_prd_start_dt => cur_get_rfms_disb.pymt_prd_start_dt,
x_accpt_pymt_prd_start_dt => cur_get_rfms_disb.accpt_pymt_prd_start_dt,
x_edit_code => cur_get_rfms_disb.edit_code ,
x_rfmb_id => NULL,
x_mode => 'R',
x_ed_use_flags => cur_get_rfms_disb.ed_use_flags);
SELECT
ver_status_code
FROM
igf_gr_rfms
WHERE
origination_id = p_orig_id;
SELECT
MIN(disb_ref_num)
FROM
igf_gr_rfms_disb
WHERE
origination_id = p_orig_id;
SELECT
MIN(disb_num)
FROM
igf_aw_awd_disb
WHERE
award_id = p_award_id;
SELECT award_fmly_contribution_type
FROM igf_ap_fa_base_rec
WHERE base_id = p_base_id;
SELECT
origination_id,
secondary_efc_cd
FROM
igf_gr_rfms
WHERE
base_id = p_base_id;
SELECT
batch.rfms_ack_batch_id
FROM
igf_gr_rfms pell,
igf_gr_rfms_batch batch
WHERE
origination_id = p_orig_id AND
pell.rfmb_id = batch.rfmb_id AND
batch.rfms_ack_batch_id IS NOT NULL;
SELECT fund_id
FROM
igf_aw_award
WHERE
award_id = p_award_id;
PROCEDURE delete_coa( p_record IN VARCHAR2,
p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
p_item_code IN igf_aw_coa_grp_item_all.item_code%TYPE
)
IS
--
-- Bug 2613546
-- sjadhav,
-- routine to delete cost of attendance group/item childs
--
--
-- Cursor to fetch the COA Items
--
CURSOR cur_coa_items ( p_coa_code igf_aw_coa_group_all.coa_code%TYPE,
p_cal_type igf_aw_coa_group_all.ci_cal_type%TYPE,
p_sequence_number igf_aw_coa_group_all.ci_sequence_number%TYPE
)
IS
SELECT
*
FROM
igf_aw_coa_grp_item
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
SELECT
row_id
FROM
igf_aw_coa_ld
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
SELECT
row_id
FROM
igf_aw_cit_ld_overide
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number AND
item_code = p_item_code;
igf_aw_cit_ld_ovrd_pkg.delete_row(l_term.row_id);
igf_aw_coa_grp_item_pkg.delete_row(coa_items_rec.row_id);
igf_aw_coa_grp_item_pkg.update_row (
x_mode => 'R',
x_rowid => coa_items_rec.row_id,
x_coa_code => coa_items_rec.coa_code,
x_ci_cal_type => coa_items_rec.ci_cal_type,
x_ci_sequence_number => coa_items_rec.ci_sequence_number,
x_item_code => coa_items_rec.item_code,
x_default_value => coa_items_rec.default_value,
x_fixed_cost => coa_items_rec.fixed_cost,
x_pell_coa => NULL,
x_active => coa_items_rec.active,
x_pell_amount => coa_items_rec.pell_amount,
x_pell_alternate_amt => coa_items_rec.pell_alternate_amt,
x_item_dist => 'N',
x_lock_flag => coa_items_rec.lock_flag);
igf_aw_coa_ld_pkg.delete_row(l_term.row_id);
igf_aw_cit_ld_ovrd_pkg.delete_row(l_term.row_id);
fnd_message.set_token('NAME','IGF_GR_GEN.DELETE_COA'|| ' ' || SQLERRM);
END delete_coa;
PROCEDURE update_item_dist( p_coa_code IN igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
p_cal_type IN igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
p_sequence_number IN igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
p_item_code IN igf_aw_cit_ld_ovrd_all.item_code%TYPE,
p_upd_result OUT NOCOPY VARCHAR2)
IS
--
-- Bug 2613546
-- sjadhav,
-- routine to update cost of attendance item distribution
--
--
-- Cursor to fetch the COA Items
--
CURSOR cur_coa_items ( p_coa_code igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
p_cal_type igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
p_sequence_number igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
p_item_code igf_aw_cit_ld_ovrd_all.item_code%TYPE
)
IS
SELECT
*
FROM
igf_aw_coa_grp_item
WHERE
item_code = p_item_code AND
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
SELECT
ld_perct
FROM
igf_aw_coa_ld
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number
ORDER BY
ld_sequence_number;
SELECT
ld_perct
FROM
igf_aw_cit_ld_overide
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number AND
item_code = p_item_code
ORDER BY
ld_sequence_number;
SELECT
SUM(ld_perct)
FROM
igf_aw_cit_ld_overide
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number AND
item_code = p_item_code;
delete_coa( 'COA_ITEM' ,
coa_items_rec.coa_code,
coa_items_rec.ci_cal_type,
coa_items_rec.ci_sequence_number,
coa_items_rec.item_code
);
igf_aw_coa_grp_item_pkg.update_row (
x_mode => 'R',
x_rowid => coa_items_rec.row_id,
x_coa_code => coa_items_rec.coa_code,
x_ci_cal_type => coa_items_rec.ci_cal_type,
x_ci_sequence_number => coa_items_rec.ci_sequence_number,
x_item_code => coa_items_rec.item_code,
x_default_value => coa_items_rec.default_value,
x_fixed_cost => coa_items_rec.fixed_cost,
x_pell_coa => NULL,
x_active => coa_items_rec.active,
x_pell_amount => coa_items_rec.pell_amount,
x_pell_alternate_amt => coa_items_rec.pell_alternate_amt,
x_item_dist => lv_item_dist,
x_lock_flag => coa_items_rec.lock_flag);
fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_ITEM_DIST'|| ' ' || SQLERRM);
END update_item_dist;
SELECT
alternate_code,
cal_type,
sequence_number,
start_dt,
end_dt
FROM
igf_ap_award_year_v
ORDER BY
ABS(TRUNC(SYSDATE) - TRUNC(start_dt));
SELECT
ld_perct
FROM
igf_aw_cit_ld_overide
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
SELECT alt_pell_schedule
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE awd.base_id = p_base_id
AND awd.fund_id = fmast.fund_id
AND awd.award_status IN ('ACCEPTED','OFFERED')
AND fmast.fund_code = fcat.fund_code
AND fcat.fed_fund_code = 'PELL';
SELECT
pell_alt_expense
FROM
igf_ap_fa_base_rec
WHERE
base_id = p_base_id;
SELECT ltfees.lt_fees_code
FROM igf_gr_tuition_fee_codes ltfees,
igf_ap_batch_aw_map_all batch
WHERE p_exp BETWEEN ltfees.min_range_amt AND ltfees.max_range_amt
AND batch.ci_cal_type = p_cal_type
AND batch.ci_sequence_number = p_sequence_number
AND batch.sys_award_year = ltfees.sys_awd_yr;
PROCEDURE insert_coa_items( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
p_item_code IN igf_aw_coa_grp_item_all.item_code%TYPE,
p_count OUT NOCOPY NUMBER
)
IS
--
-- Cursor to get default load periods
--
CURSOR cur_default_ld(p_coa_code IN VARCHAR2,
p_cal_type IN VARCHAR2,
p_sequence_number IN NUMBER
)
IS
SELECT
ld_cal_type,
ld_sequence_number,
ld_perct,
ci_cal_type,
ci_sequence_number
FROM
igf_aw_coa_ld
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
SELECT
cldo_id
FROM
igf_aw_cit_ld_overide
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number AND
item_code = p_item_code AND
ld_cal_type = p_ld_cal_type AND
ld_sequence_number = p_ld_sequence_number;
igf_aw_cit_ld_ovrd_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_ld_rowid,
x_cldo_id => l_cldo_id,
x_coa_code => p_coa_code,
x_ci_cal_type => p_cal_type,
x_ci_sequence_number => p_sequence_number,
x_item_code => p_item_code,
x_ld_cal_type => default_ld_rec.ld_cal_type,
x_ld_sequence_number => default_ld_rec.ld_sequence_number,
x_ld_perct => default_ld_rec.ld_perct
);
fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_COA_ITEMS'|| ' ' || SQLERRM);
END insert_coa_items;
PROCEDURE insert_coa_terms( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE
)
IS
CURSOR cur_get_items (p_coa_code igf_aw_coa_group_all.coa_code%TYPE,
p_cal_type igf_aw_coa_group_all.ci_cal_type%TYPE,
p_sequence_number igf_aw_coa_group_all.ci_sequence_number%TYPE)
IS
SELECT
DISTINCT item_code
FROM
igf_aw_cit_ld_overide
WHERE
coa_code = p_coa_code AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
igf_aw_cit_ld_ovrd_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_ld_rowid,
x_cldo_id => l_cldo_id,
x_coa_code => p_coa_code,
x_ci_cal_type => p_cal_type,
x_ci_sequence_number => p_sequence_number,
x_item_code => get_items_rec.item_code,
x_ld_cal_type => p_ld_cal_type,
x_ld_sequence_number => p_ld_sequence_number,
x_ld_perct => 0
);
fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_COA_TERMS'|| ' ' || SQLERRM);
END insert_coa_terms;
PROCEDURE insert_stu_coa_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE,
p_result OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_get_terms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE)
IS
SELECT item_code
FROM igf_aw_coa_items
WHERE base_id = p_base_id;
igf_aw_coa_itm_terms_pkg.insert_row( x_rowid => lv_rowid,
x_base_id => p_base_id,
x_item_code => get_terms_rec.item_code,
x_amount => 0,
x_ld_cal_type => p_ld_cal_type,
x_ld_sequence_number => p_ld_sequence_number,
x_mode => 'R',
x_lock_flag => 'N'
);
fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_STU_COA_TERMS'|| ' ' || SQLERRM);
END insert_stu_coa_terms;
PROCEDURE insert_existing_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
p_result OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_get_existing_terms(c_base_id igf_aw_coa_itm_terms.base_id%TYPE)
IS
SELECT DISTINCT ld_cal_type,ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = c_base_id;
igf_aw_coa_itm_terms_pkg.insert_row( x_rowid => lv_rowid,
x_base_id => p_base_id,
x_item_code => p_item_code,
x_amount => 0,
x_ld_cal_type => get_existing_terms_rec.ld_cal_type,
x_ld_sequence_number => get_existing_terms_rec.ld_sequence_number,
x_mode => 'R',
x_lock_flag => 'N'
);
fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_EXISTING_TERMS'|| ' ' || SQLERRM);
END insert_existing_terms;
PROCEDURE delete_stu_coa_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE,
p_result OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_get_terms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
p_ld_cal_type igf_aw_coa_ld_all.ld_cal_type%TYPE,
p_ld_sequence_number igf_aw_coa_ld_all.ld_sequence_number%TYPE
)
IS
SELECT rowid
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
AND ld_cal_type = p_ld_cal_type
AND ld_sequence_number = p_ld_sequence_number;
igf_aw_coa_itm_terms_pkg.delete_row( x_rowid => get_terms_rec.rowid);
fnd_message.set_token('NAME','IGF_GR_GEN.DELETE_STU_COA_TERMS'|| ' ' || SQLERRM);
END delete_stu_coa_terms;
PROCEDURE delete_stu_coa_items( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
p_result OUT NOCOPY VARCHAR2,
p_item_code IN igf_aw_coa_items.item_code%TYPE
)
IS
CURSOR cur_get_coa_itms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
p_item_code igf_aw_coa_items.item_code%TYPE)
IS
SELECT item_code,rowid
FROM igf_aw_coa_items
WHERE base_id = p_base_id
AND item_code = NVL(p_item_code,item_code);
SELECT rowid
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
AND item_code = p_item_code;
igf_aw_coa_itm_terms_pkg.delete_row(x_rowid => get_coa_terms_rec.rowid);
fnd_message.set_token('NAME','IGF_GR_GEN.DELETE_STU_COA_ITEMS'|| ' ' || SQLERRM);
END delete_stu_coa_items;
PROCEDURE update_stu_coa_items( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
p_result OUT NOCOPY VARCHAR2)
IS
CURSOR cur_get_items (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
p_item_code igf_aw_coa_itm_terms.item_code%TYPE)
IS
SELECT items.rowid,items.*
FROM
igf_aw_coa_items items
WHERE base_id = p_base_id
AND item_code = NVL(p_item_code,item_code);
SELECT
SUM (amount) item_amount
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
AND item_code = p_item_code
GROUP BY item_code;
igf_aw_coa_items_pkg.update_row (x_rowid => get_items_rec.rowid,
x_base_id => p_base_id,
x_item_code => get_items_rec.item_code,
x_amount => get_terms_rec.item_amount,
x_pell_coa_amount => get_items_rec.pell_coa_amount,
x_alt_pell_amount => get_items_rec.alt_pell_amount,
x_fixed_cost => get_items_rec.fixed_cost,
x_mode => 'R',
x_lock_flag => get_items_rec.lock_flag
);
fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_STU_COA_ITEMS'|| ' ' || SQLERRM);
END update_stu_coa_items;
SELECT pell_att_code
FROM igf_ap_attend_map
WHERE attendance_type = p_att_code
AND cal_type = p_cal_type
AND sequence_number = p_sequence_number;
SELECT
base_id
FROM
igf_ap_fa_base_rec
WHERE
person_id = p_person_id AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
PROCEDURE update_current_ssn (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
p_cur_ssn IN igf_ap_isir_matched_all.current_ssn%TYPE,
p_message OUT NOCOPY fnd_new_messages.message_name%TYPE)
IS
--
-- sjadhav,2/4/03
-- FA116 Build - Bug 2758812 - 2/4/03
-- update_current_ssn
-- updates current ssn with the new value from active isir
--
CURSOR cur_get_pell (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
IS
SELECT pell.*
FROM
igf_gr_rfms pell
WHERE
base_id = p_base_id
FOR UPDATE OF current_ssn;
igf_gr_rfms_pkg.update_row(
x_rowid => get_pell_rec.row_id,
x_origination_id => get_pell_rec.origination_id,
x_ci_cal_type => get_pell_rec.ci_cal_type,
x_ci_sequence_number => get_pell_rec.ci_sequence_number,
x_base_id => get_pell_rec.base_id,
x_award_id => get_pell_rec.award_id,
x_rfmb_id => get_pell_rec.rfmb_id,
x_sys_orig_ssn => get_pell_rec.sys_orig_ssn,
x_sys_orig_name_cd => get_pell_rec.sys_orig_name_cd,
x_transaction_num => get_pell_rec.transaction_num,
x_efc => get_pell_rec.efc,
x_ver_status_code => get_pell_rec.ver_status_code,
x_secondary_efc => get_pell_rec.secondary_efc,
x_secondary_efc_cd => get_pell_rec.secondary_efc_cd,
x_pell_amount => get_pell_rec.pell_amount,
x_pell_profile => get_pell_rec.pell_profile,
x_enrollment_status => get_pell_rec.enrollment_status,
x_enrollment_dt => get_pell_rec.enrollment_dt,
x_coa_amount => get_pell_rec.coa_amount,
x_academic_calendar => get_pell_rec.academic_calendar,
x_payment_method => get_pell_rec.payment_method,
x_total_pymt_prds => get_pell_rec.total_pymt_prds,
x_incrcd_fed_pell_rcp_cd => get_pell_rec.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => get_pell_rec.attending_campus_id,
x_est_disb_dt1 => get_pell_rec.est_disb_dt1,
x_orig_action_code => get_pell_rec.orig_action_code,
x_orig_status_dt => get_pell_rec.orig_status_dt,
x_orig_ed_use_flags => get_pell_rec.orig_ed_use_flags,
x_ft_pell_amount => get_pell_rec.ft_pell_amount,
x_prev_accpt_efc => get_pell_rec.prev_accpt_efc,
x_prev_accpt_tran_no => get_pell_rec.prev_accpt_tran_no,
x_prev_accpt_sec_efc_cd => get_pell_rec.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => get_pell_rec.prev_accpt_coa,
x_orig_reject_code => get_pell_rec.orig_reject_code,
x_wk_inst_time_calc_pymt => get_pell_rec.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => get_pell_rec.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => get_pell_rec.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => get_pell_rec.cr_clk_hrs_acad_yr,
x_inst_cross_ref_cd => get_pell_rec.inst_cross_ref_cd,
x_low_tution_fee => get_pell_rec.low_tution_fee,
x_rec_source => get_pell_rec.rec_source,
x_pending_amount => get_pell_rec.pending_amount,
x_mode => 'R',
x_birth_dt => get_pell_rec.birth_dt,
x_last_name => get_pell_rec.last_name,
x_first_name => get_pell_rec.first_name,
x_middle_name => get_pell_rec.middle_name,
x_current_ssn => get_pell_rec.current_ssn,
x_legacy_record_flag => NULL,
x_reporting_pell_cd => get_pell_rec.rep_pell_id,
x_rep_entity_id_txt => get_pell_rec.rep_entity_id_txt,
x_atd_entity_id_txt => get_pell_rec.atd_entity_id_txt,
x_note_message => get_pell_rec.note_message,
x_full_resp_code => get_pell_rec.full_resp_code,
x_document_id_txt => get_pell_rec.document_id_txt
);
fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_CURRENT_SSN');
fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_CURRENT_SSN'|| ' ' || SQLERRM);
END update_current_ssn;
PROCEDURE update_pell_status (p_award_id IN igf_aw_award_all.award_id%TYPE,
p_fed_fund_code IN igf_aw_fund_cat_all.fed_fund_code%TYPE,
p_message OUT NOCOPY fnd_new_messages.message_name%TYPE,
p_status_desc OUT NOCOPY igf_lookups_view.meaning%TYPE)
IS
--
-- sjadhav,2/4/03
-- FA116 Build - Bug 2758812 - 2/4/03
-- update_pell_status
-- sets pell origination status desc
--
CURSOR cur_get_pell (p_award_id igf_gr_rfms_all.award_id%TYPE)
IS
SELECT pell.orig_action_code
FROM
igf_gr_rfms pell
WHERE
award_id = p_award_id;
fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_PELL_STATUS'|| ' ' || SQLERRM);
END update_pell_status;
SELECT
MIN(disb_num)
FROM
igf_aw_awd_disb
WHERE
award_id = p_award_id;
SELECT grp.reporting_pell_cd
FROM
igf_gr_report_pell grp
WHERE
grp.ci_cal_type = cp_cal_type AND
grp.ci_sequence_number = cp_seq_num AND
grp.ope_cd = cp_ope_cd;
SELECT grp.reporting_pell_cd
FROM
igf_gr_attend_pell gap,
igf_gr_report_pell grp
WHERE
gap.ci_cal_type = cp_cal_type AND
gap.ci_sequence_number = cp_seq_num AND
gap.attending_pell_cd = cp_att_pell AND
gap.rcampus_id = grp.rcampus_id;