[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| igf_aw_awd_disb_pkg.update_row() call.
|| Note: Issues 2 and 3 are additional fixes not directly related to the bug.
|| museshad 14-Apr-2006 Bug 5042136.
|| Origination Fee, Guarantor Fee, Interest Rebate Amount should
|| become 0 when a disb gets cancelled. Fixed this in -
|| cancel_invalid_award() and post_award().
|| veramach 19/July/2005 Bug # 3392043 FA 140 Student SS Build
|| Added option to publish/hide packaged/repackaged awards to Student SS
|| Exclusive Inclusive funds checks now work for Repackaging too - Rejoice!!!!!
|| Yet another indicator added for temporary awards - AR - which indicates an award
|| cancelled due to exclusive inclusive checks
|| museshad 11-Jul-2005 Build FA 157.
|| 1) Disbursement Rounding
|| 2) Validate program eligibility
|| 3) Use anticipated data
|| 4) Cancel ineligible awards in Repackaging
|| veramach April 2005 bug # 4274177
|| Paid amount was getting reset to zero on repackaging. Fixed this - repackaging
|| now does not update paid amount
|| veramach Oct/Nov 2004 FA 152 - Automatic Repackaging
|| FA 137 - COA Enhancements
|| Modified logic to bring in packaging per awarding period rather than the whole award year
|| Modified logic to allow repackage existing awards
|| Added new functions and modified signatures of existing functions
|| ayedubat 12-OCT-2004 Changed the post_award procedure for FA 149 build bug # 3416863
|| Fixed the GSCC warning "File.Sql.35 261, 2047, 3229, 7461-7462 - Do not assign default
|| values in PL/SQL initialization or declaration" except for line number: 261
|| veramach July 2004 FA 151 HR integration (bug # 3709292)
|| Impact of obsoleting columns from igf_aw_awd_disb_pkg
-- sjadhav 04-Aug-2004 Corrected Group By
--
|| veramach 30-Jun-2004 bug 3709109 - Added call to function check_disb to enforce the rule that FWS funds can
|| have only one disbursement per term
|| veramach 11-Jun-2004 bug # 3684031 Added a check so that packaging does not put holds on any award-packaging, as per
|| current logic, does not overaward. Single-fund packaging does impose overaward holds if it overawards.
|| veramach 04-Mar-2004 bug # 3484438 - Changed cursor cur_new_awards to properly join on igf_aw_fund_mast_all
|| veramach 16-Feb-2004 bug # 3446214 - removed code from process_single_fund which uses award group.
|| cdcruz 04-Dec-2003 FA 131 COD Updates
|| Modified the pell wrapper used to calculate pell amount. one more return parameter added
|| Pell Schedule Code
|| Modified igf_sl_roundoff_digits_pkg.gross_fees_roundoff , as the package dropped a parameter
|| Modified igf_sl_roundoff_digits_pkg.cl_gross_fees_roundoff , as the pkg dropped 2 parameters
|| veramach 03-Dec-2003 FA 131 COD Updates
|| Modifies the pell wrapper used to calculate pell amount. The same wrapper returns the disbursements
|| get_disbursements uses this disbursements and not calculate disbursements
|| veramach 20-NOV-2003 Added check_plan,get_plan_desc procedures
|| veramach 10-NOV-2003 Added debug statements
|| ugumall 30 OCT 03 Bug 3102439. Removed code references of IGF_AP_FA_SETUP
|| commented code related to FISAP since the implementation
|| is not being supported. Done as part of FA126 Build
|| Commented the declarations of the following variables
|| l_method, l_pct, l_fseog_cnt, l_fseog_sum,
|| l_match_pct, cursor c_match_method
|| and cursor variable l_method_rec.
|| veramach 13-OCT-2003 FA 124 Build Remove ISIR Requirement for Awarding
|| 1.Added logic to insert 2 rows into igf_aw_award_t table which holds IM/FM needs in calc_need
|| 2.Added logic to error out if a student does not have active ISIR in multiple fund packaging in stud_run
|| 3.Added logic as specified in the logic flow specified in the TD in process_stud
|| 4.Added logic for validations on g_sf_max_award_amt,g_sf_min_award_amt,g_allow_to_exceed in run
|| 5.Removed p_grp_code parameter and added p_sf_min_amount,p_sf_max_amount,p_allow_to_exceed in pkg_single_fund
|| bkkumar 30-sep-2003 FA 122 Loans Enhancemnts
|| Added base_id to the get_loan_fee1 and
|| get_loan_fee2 and added l_auto_late_ind
|| for teh CL Loans
|| 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.
||
bkkumar 27-Aug-2003 Bug# 3071157 Added explicit date format mask to the
to_date() function.
sjadhav 06-Aug-2003 Bug 3062062
Modified post_award.
Added check to not create award if there
are no disbursements
|| sjadhav 24-Jun-2003 Bug 2983181. elig status populated with 'N'
|| bkkumar 04-jun-2003 Bug #2858504
|| Added legacy_record_flag,award_number_txt
|| in the table handler calls for
|| igf_aw_award_pkg.insert_row
|| Added legacy_record_flag
|| in the table handler calls for igf_ap_td_item_inst_pkg.insert_row
||
|| rasahoo 19-May-2003 Bug # 2860836
|| Added exception handling for resolving
|| locking problem created by fund manager
|| brajendr 07-Mar-2003 Bug # 2829487
|| Added the call to update the Process status of the student after adding the TO Do Items
||
|| brajendr 27-Feb-2003 Bug # 2662487
|| Modified the rounding off logic.
||
|| cdcruz 05-Feb-2003 Bug # 2758804
|| Modified the ISIR record being picked as part of FACR105
||
|| brajendr 08-Jan-2003 Bug # 2762648
|| Removed Function validate_student_efc call as this validation is necessary only for Packaging Process
||
|| brajendr 09-Jan-2003 Bug # 2740222
|| Added different messages for each validation and gave more clarity in the log file.
||
|| brajendr 09-Jan-2003 Bug # 2733847 Modified the code for calculating the Running Totals and Last Disbursement.
|| Bug # 2742000 Modified the logic for updating Notification Status.
|| Earlier notification status is done only for Auto Packaging
||
|| brajendr 08-Jan-2003 Bug # 2710314
|| Added a Function validate_student_efc
|| for checking the validity of EFC
||
|| brajendr 18-Dec-2002 Bug # 2711114
|| Added a new token for message IGF_AW_AWD_FUND_HOLD_FAIL
||
|| brajendr 18-Dec-2002 Bug # 2691832
|| Modified the logic for updating the Packaging Status.
||
|| brajendr 17-Dec-2002 Bug # 2686797
|| Modified the logic to round off the Amounts to 2 decimals before creating the disbursements.
|| Last disb amouts are calculated using the remaining amount at the award level.
||
|| brajendr 10-Dec-2002 Bug # 2701470
|| Modified the logic for not validating the packaging status for single fund process
||
|| brajendr 09-Dec-2002 Bug # 2676394
|| Removed the referrences of the EFC from igf_ap_efc_det table to igf_ap_isir_matched table.
|| Used igf_aw_packng_subfns.get_fed_efc to calculate the EFC
||
|| brajendr 07-NOV-2002 Bug # 2613536
|| Added the code to skip the fund if there are holds for the person
||
|| brajendr 24-Oct-2002 FA105 / FA108 Builds
|| Refer TDs for the changes
||
|| CDCRUZ 22-Oct-2002 FA105 / FA108 Build
|| removes a parameter from igf_ap_efc_calc.get_efc_no_of_months
||
|| brajendr 18-Oct-2002 Bug : 2591643
|| Modified the chk_todo_result for FA104 - To Do Enhancements
||
|| sjadhav Bug 2411031
|| Changed sequence of calling igf_sl_award.get_loan_amts and
|| igf_sl_roundoff_digits_pkg.gross_fees_roundoff. This is done
|| becuase first round off disbursement gross amount should be
|| calculated first and then net amount / fee amount etc
||
|| CDCRUZ 12-JUN-2002 Bug ID : 2412897
|| Prkins loan was still included for Stafford Loan Limits chk
||
||
|| CDCRUZ 07-JUN-2002 Bug ID : 2405510
|| Students with Packaging Hold should not be packaged
||
|| CDCRUZ 05-JUN-2002 Bug ID : 2400556
|| The group level maximum limits running totals were getting updated
|| only for Non Entitlement Funds .
|| Even though Entitlement bypasses this validation the running total
|| As a result of this entitlement has to be updated for future funds.
||
|| adhawan 02-may-2002 Bug ID : 2330105
|| Removed the logic , {If the Individual Packaging is set to "Y"
|| and manually packaged is set to "N" then Packaging should skip that fund}
||
|| sjadhav 12-sep-2001 Bug ID : 1978618
|| added exception param_err
|| removed hard coded messages
||
|| sjadhav 24-jul-2001 Bug ID : 1818617
|| added parameter p_get_recent_info
||
|| skoppula 26-apr-2002 Bug :2317853
|| Changed the cursor in process_stud that is raising invalid
|| NUMBER exception
||
|| pmarada 14-feb-2002 FACR008-correspondence Build,2213043
|| Added a p_upd_awd_notif_status parameter in run and post_award.
|| as part of FACR008-Correspondence build.
||
|| ssawhney 31-Oct-2001 Introduce changes in packaging due to FISAP.
|| Check the percentage of matching funds for an FSEOG fund
|| Modified process_stud () and update_fund ().
||
|| pmarada 23-Jul-2001 Bug ID : 1818617
|| OSS Interface usage was changde to pick the
|| attributes from FA-Base-History record
||
|| sjadhav May-21-2001 Bug ID : 1747948
|| 1. Added one more parameter Group_Code in the callable 'run'
|| 2. Added new cursor to get enrollment details from
|| OSS Interface table
|| 3. In Stud_Run, a student is skipped if it fails conditions
||
|| avenkatr 15-May-2001 Bug Id : 1755969 Maximum NUMBER of terms
|| 1. Added check to test if Aid exceeds Max Award
|| amount given in Fund manager in stud_run procedure.
||
|| avenkatr 01-May-2001 Bug Id : 1755969 Maximum NUMBER of terms
|| 1. Corrected the check for Max NUMBER of terms of a fund.
||
|| avenkatr 01-May-2001 Bug Id : 1754396 General Award Issues
|| 1. Added check to continue awarding for 'Replace FC'
|| funds even WHEN Need is over.
|| 2. Corrected the 'OverAward' check for funds.
|| 3. Updated the remaining amt of the fund if the awards are
|| packaged
||
|| avenkatr 25-APR-2001 Bug Id : 1750254 Self Help Limits.
|| 1. Corrected the variable used for checking self help in
|| procedure stud_run.
|| 2. Added clear_simulation in stud_run procedure
||
|| avenkatr 19-APR-2001 Bug Id : 1726280 Rounding off process for Direct Loans.
|| 1. Corrected the variable used for printing
|| the Disbursement Gross amount.
||
|| mesriniv 20-APR-2001 Bug Id : 1723272 Process Requests.
|| 1.In the Procedure post_award,Added a cursor c_person_number
|| to fetch the person number for the Base Id.
|| 2.Changed the Prompt and variable (l_base_id)
|| for Display of Person number.
||
|| avenkatr 19-APR-2001 Bug Id : 1726280 Rounding off process for Direct Loans.
|| 1. Corrected the variable used for printing the
|| Disbursement Gross amount.
|| 2. Corrected the NVL(offered_amt, accepted_amt) to
|| NVL( accepted_amt, offered_amt) in procedures
|| stud_run and post_award.
|| 3. Removed the NVL for accepted_amt in the c_awd_grp
|| cursor of stud_run procedure.
||
|| prchandr 06-APR-2001 Bug Id : 1726280 Rounding off process for Direct Loans.
|| In procedure "post_award", a call is made to
|| round off procedure incase of direct loans.
|| Rounding off process. A call is made to roundoff process package
||
|| The Packaging for a Run Code/Target Group/Individual Student is done in this pkg
||
|| Pre-requisites
|| The following tables have to be populated before calling this process
|| igf_fa_base_rec
|| igf_aw_fund_mast
|| igf_aw_ssn_tp
||
|| The Cost of Attendance Process must be run before Running Packaging
-------------------------------------------------------------------------------
Important : The following are the statuses present for the igf_aw_award_t.flag
-------------------------------------------------------------------------------
AA - Already Awarded fund to the student.(Will exist only if the student has awards)
AW - Selected for Award, before Fund checks.
CF - Initial loaded INTO the temporary table.
DB - Disbursements of the Fund.
FL - Final Indication : Fund Ready to award after fund validations.
LD - Load Calendar details of the Fund.
ND - Need Calculated for the Fund.(Will exist only if the student does not have existing awards
OV - Over Award Indicator of the Fund.
RF - Rejected fund while Exclusive and Inclusive checks.
ST - Loaded students as per decreaseing need
AL - Awards loaded for the student and locked.
AU - Awards which are candidates for repackaging(Awards which are unlocked)
AC - Awards cancelled due to some reason,during repackaging
AR - Awards cancelled due to Exclusive Inclusive Checks
-------------------------------------------------------------------------------
*/
g_sf_min_amount NUMBER;
update_need igf_aw_fund_mast_all.update_need%TYPE,
entitlement igf_aw_fund_mast_all.entitlement%TYPE,
fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE
);
SELECT COUNT(tp.adteach_id) tp,
COUNT(DISTINCT terms.adterms_id) terms
FROM igf_aw_dp_teach_prds tp,
igf_aw_dp_terms terms,
igf_aw_awd_dist_plans dp,
igf_aw_awd_prd_term aprd
WHERE terms.adterms_id = tp.adterms_id
AND terms.adplans_id = cp_adplans_id
AND terms.adplans_id = dp.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND aprd.ld_cal_type = terms.ld_cal_type
AND aprd.ld_sequence_number = terms.ld_sequence_number
AND aprd.award_prd_cd = cp_award_prd_code;
SELECT COUNT(tp.adteach_id) tp,
COUNT(DISTINCT terms.adterms_id) terms
FROM igf_aw_dp_teach_prds tp,
igf_aw_dp_terms terms,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id, ld_cal_type, ld_sequence_number) coaterms,
igf_aw_awd_prd_term aprd,
igf_aw_awd_dist_plans dp
WHERE terms.adterms_id = tp.adterms_id
AND terms.adplans_id = cp_adplans_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
AND dp.adplans_id = terms.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND aprd.ld_cal_type = terms.ld_cal_type
AND aprd.ld_sequence_number = terms.ld_sequence_number
AND aprd.award_prd_cd = cp_award_prd_code;
SELECT fcat.fed_fund_code
FROM igf_aw_fund_cat fcat,
igf_aw_fund_mast fmast
WHERE fcat.fund_code = fmast.fund_code
AND fmast.fund_id = p_fund_id;
SELECT fcat.sys_fund_type
FROM igf_aw_fund_cat fcat,
igf_aw_fund_mast fmast
WHERE fcat.fund_code = fmast.fund_code
AND fmast.fund_id = p_fund_id;
SELECT disb_rounding_code
FROM igf_aw_fund_mast
WHERE fund_id = cp_fund_id;
SELECT DECODE(cp_fund_source,
'STATE', UPPER(state_financial_aid),
'FEDERAL', UPPER(federal_financial_aid),
'INSTITUTIONAL', UPPER(institutional_financial_aid),
NULL) prog_eligibility
FROM IGS_PS_VER_V
WHERE course_cd = cp_stud_program_cd AND
version_number = cp_stud_program_ver;
SELECT 'x' FROM dual
WHERE EXISTS
( SELECT * FROM IGS_PS_VER_V
WHERE course_cd = cp_stud_program_cd AND
DECODE(cp_fund_source,
'STATE', UPPER(state_financial_aid),
'FEDERAL', UPPER(federal_financial_aid),
'INSTITUTIONAL', UPPER(institutional_financial_aid),
NULL) = 'Y'
);
SELECT awd_dist_plan_cd_desc,
dist_plan_method_code_desc
FROM igf_aw_awd_dist_plans_v
WHERE adplans_id = cp_adplans_id;
SELECT awd.ROWID row_id,
awd.*
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast
WHERE fmast.ci_cal_type = l_ci_cal_type
AND fmast.ci_sequence_number = l_ci_sequence_number
AND awd.fund_id = fmast.fund_id
AND awd.base_id = p_base_id
AND NOT EXISTS(
SELECT disb.ld_cal_type,
disb.ld_sequence_number
FROM igf_aw_awd_disb_all 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 = l_ci_cal_type
AND apt.ci_sequence_number = l_ci_sequence_number
AND apt.award_prd_cd = p_award_prd_cd);
SELECT fa.ci_cal_type,
fa.ci_sequence_number
FROM igf_ap_fa_base_rec_all fa
WHERE fa.base_id = p_base_id;
igf_aw_award_pkg.update_row(
x_rowid => l_awards.row_id,
x_award_id => l_awards.award_id,
x_fund_id => l_awards.fund_id,
x_base_id => l_awards.base_id,
x_offered_amt => l_awards.offered_amt,
x_accepted_amt => l_awards.accepted_amt,
x_paid_amt => l_awards.paid_amt,
x_packaging_type => l_awards.packaging_type,
x_batch_id => l_awards.batch_id,
x_manual_update => l_awards.manual_update,
x_rules_override => l_awards.rules_override,
x_award_date => l_awards.award_date,
x_award_status => l_awards.award_status,
x_attribute_category => l_awards.attribute_category,
x_attribute1 => l_awards.attribute1,
x_attribute2 => l_awards.attribute2,
x_attribute3 => l_awards.attribute3,
x_attribute4 => l_awards.attribute4,
x_attribute5 => l_awards.attribute5,
x_attribute6 => l_awards.attribute6,
x_attribute7 => l_awards.attribute7,
x_attribute8 => l_awards.attribute8,
x_attribute9 => l_awards.attribute9,
x_attribute10 => l_awards.attribute10,
x_attribute11 => l_awards.attribute11,
x_attribute12 => l_awards.attribute12,
x_attribute13 => l_awards.attribute13,
x_attribute14 => l_awards.attribute14,
x_attribute15 => l_awards.attribute15,
x_attribute16 => l_awards.attribute16,
x_attribute17 => l_awards.attribute17,
x_attribute18 => l_awards.attribute18,
x_attribute19 => l_awards.attribute19,
x_attribute20 => l_awards.attribute20,
x_rvsn_id => l_awards.rvsn_id,
x_alt_pell_schedule => l_awards.alt_pell_schedule,
x_mode => 'R',
x_award_number_txt => l_awards.award_number_txt,
x_legacy_record_flag => l_awards.legacy_record_flag,
x_adplans_id => l_awards.adplans_id,
x_lock_award_flag => l_awards.lock_award_flag,
x_app_trans_num_txt => l_awards.app_trans_num_txt,
x_awd_proc_status_code => 'AWARDED',
x_notification_status_code => l_awards.notification_status_code,
x_notification_status_date => l_awards.notification_status_date,
x_publish_in_ss_flag => l_awards.publish_in_ss_flag
);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => NULL,
x_base_id => p_base_id,
x_offered_amt => NULL,
x_accepted_amt => NULL,
x_paid_amt => NULL,
x_need_reduction_amt => NULL,
x_flag => 'ST',
x_temp_num_val1 => l_normal_efc, -- Students EFC
x_temp_num_val2 => p_coa - l_normal_efc, -- Students Need
x_temp_char_val1 => NULL,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
SELECT awd_dist_plan_cd,
awd_dist_plan_cd_desc,
dist_plan_method_code
FROM igf_aw_awd_dist_plans
WHERE adplans_id = cp_adplans_id;
SELECT fa_detail.base_id,
igf_aw_coa_gen.coa_amount(fa_detail.base_id,g_awd_prd) coa_f
FROM igf_ap_fa_base_rec fa_detail
WHERE fa_detail.ci_cal_type = x_ci_cal_type
AND fa_detail.ci_sequence_number = x_ci_sequence_number
AND fa_detail.target_group = x_group_code;
SELECT base_id
FROM igf_aw_award_t
WHERE flag = 'ST'
AND process_id = l_process_id
ORDER BY temp_num_val2 DESC;
SELECT adisb.rowid row_id
FROM igf_aw_awd_disb_all adisb
WHERE adisb.award_id = cp_award_id;
SELECT awd.rowid row_id,
awd.award_id
FROM igf_aw_award_all awd
WHERE awd.base_id = cp_base_id
AND awd.award_status = 'SIMULATED';
igf_aw_awd_disb_pkg.delete_row(l_sim_adisb.row_id);
igf_aw_award_pkg.delete_row(l_sim_awd.row_id);
PROCEDURE update_pell_orig_stat(
p_award_id igf_aw_award_all.award_id%TYPE,
p_amount igf_aw_award_all.offered_amt%TYPE
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 05-Nov-2004
--
--Purpose:
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
-- Get pell orig
CURSOR c_pell_orig(
cp_award_id igf_aw_award_all.award_id%TYPE,
cp_amount igf_aw_award_all.offered_amt%TYPE
) IS
SELECT rfms.rowid row_id,
rfms.*
FROM igf_gr_rfms_all rfms
WHERE award_id = cp_award_id
AND pell_amount <> cp_amount
AND orig_action_code <> 'S';
igf_gr_rfms_pkg.update_row(
x_rowid => l_pell_orig.row_id,
x_origination_id => l_pell_orig.origination_id,
x_ci_cal_type => l_pell_orig.ci_cal_type,
x_ci_sequence_number => l_pell_orig.ci_sequence_number,
x_base_id => l_pell_orig.base_id,
x_award_id => l_pell_orig.award_id,
x_rfmb_id => l_pell_orig.rfmb_id,
x_sys_orig_ssn => l_pell_orig.sys_orig_ssn,
x_sys_orig_name_cd => l_pell_orig.sys_orig_name_cd,
x_transaction_num => l_pell_orig.transaction_num,
x_efc => l_pell_orig.efc,
x_ver_status_code => l_pell_orig.ver_status_code,
x_secondary_efc => l_pell_orig.secondary_efc,
x_secondary_efc_cd => l_pell_orig.secondary_efc_cd,
x_pell_amount => p_amount,--update with new award amount
x_pell_profile => l_pell_orig.pell_profile,
x_enrollment_status => l_pell_orig.enrollment_status,
x_enrollment_dt => l_pell_orig.enrollment_dt,
x_coa_amount => l_pell_orig.coa_amount,
x_academic_calendar => l_pell_orig.academic_calendar,
x_payment_method => l_pell_orig.payment_method,
x_total_pymt_prds => l_pell_orig.total_pymt_prds,
x_incrcd_fed_pell_rcp_cd => l_pell_orig.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => l_pell_orig.attending_campus_id,
x_est_disb_dt1 => l_pell_orig.est_disb_dt1,
x_orig_action_code => l_orig_action_code,--update to 'Ready to Send' or 'Not Ready'
x_orig_status_dt => TRUNC(SYSDATE),--update origination status date
x_orig_ed_use_flags => l_pell_orig.orig_ed_use_flags,
x_ft_pell_amount => l_pell_orig.ft_pell_amount,
x_prev_accpt_efc => l_pell_orig.prev_accpt_efc,
x_prev_accpt_tran_no => l_pell_orig.prev_accpt_tran_no,
x_prev_accpt_sec_efc_cd => l_pell_orig.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => l_pell_orig.prev_accpt_coa,
x_orig_reject_code => l_pell_orig.orig_reject_code,
x_wk_inst_time_calc_pymt => l_pell_orig.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => l_pell_orig.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => l_pell_orig.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => l_pell_orig.cr_clk_hrs_acad_yr,
x_inst_cross_ref_cd => l_pell_orig.inst_cross_ref_cd,
x_low_tution_fee => l_pell_orig.low_tution_fee,
x_rec_source => l_pell_orig.rec_source,
x_pending_amount => l_pell_orig.pending_amount,
x_mode => 'R',
x_birth_dt => l_pell_orig.birth_dt,
x_last_name => l_pell_orig.last_name,
x_first_name => l_pell_orig.first_name,
x_middle_name => l_pell_orig.middle_name,
x_current_ssn => l_pell_orig.current_ssn,
x_legacy_record_flag => l_pell_orig.legacy_record_flag,
x_reporting_pell_cd => l_pell_orig.reporting_pell_cd,
x_rep_entity_id_txt => l_pell_orig.rep_entity_id_txt,
x_atd_entity_id_txt => l_pell_orig.atd_entity_id_txt,
x_note_message => l_pell_orig.note_message,
x_full_resp_code => l_pell_orig.full_resp_code,
x_document_id_txt => l_pell_orig.document_id_txt
);
END update_pell_orig_stat;
PROCEDURE update_loan_stat(
p_award_id igf_aw_award_all.award_id%TYPE,
p_amount igf_aw_award_all.offered_amt%TYPE
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 07-Dec-2004
--
--Purpose:
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
-- Get loan
CURSOR c_loan(
cp_award_id igf_aw_award_all.award_id%TYPE,
cp_amount igf_aw_award_all.offered_amt%TYPE
) IS
SELECT loan.ROWID row_id,
loan.*
FROM igf_sl_loans_all loan,
igf_aw_award_all awd
WHERE loan.award_id = cp_award_id
AND loan.award_id = awd.award_id
AND awd.offered_amt <> cp_amount
AND loan.loan_status <> 'S';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_loan_stat.debug '|| g_req_id,'starting update_loan_stat with award_id:'||p_award_id||
'amount:'||p_amount);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_loan_stat.debug '|| g_req_id,'l_loan.loan_status:'||l_loan.loan_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_loan_stat.debug '|| g_req_id,'l_loan.loan_status_date:'||l_loan.loan_status_date);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_loan_stat.debug '|| g_req_id,'l_loan.loan_chg_status:'||l_loan.loan_chg_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_loan_stat.debug '|| g_req_id,'l_loan.loan_chg_status_date:'||l_loan.loan_chg_status_date);
igf_sl_loans_pkg.update_row(
x_rowid => l_loan.row_id,
x_loan_id => l_loan.loan_id,
x_award_id => l_loan.award_id,
x_seq_num => l_loan.seq_num,
x_loan_number => l_loan.loan_number,
x_loan_per_begin_date => l_loan.loan_per_begin_date,
x_loan_per_end_date => l_loan.loan_per_end_date,
x_loan_status => l_loan.loan_status,
x_loan_status_date => l_loan.loan_status_date,
x_loan_chg_status => l_loan.loan_chg_status,
x_loan_chg_status_date => l_loan.loan_chg_status_date,
x_active => l_loan.active,
x_active_date => l_loan.active_date,
x_borw_detrm_code => l_loan.borw_detrm_code,
x_mode => 'R',
x_legacy_record_flag => l_loan.legacy_record_flag,
x_external_loan_id_txt => l_loan.external_loan_id_txt,
x_called_from => 'IGFAW03B'
);
END update_loan_stat;
SELECT awd.rowid row_id,
awd.*
FROM igf_aw_award_all awd
WHERE award_id = cp_award_id
AND award_status <> 'CANCELLED';
SELECT disb.rowid row_id,
disb.*
FROM igf_aw_awd_disb_all disb
WHERE award_id = cp_award_id;
SELECT fund_code
FROM igf_aw_fund_mast_all
WHERE fund_id = cp_fund_id;
SELECT SUM(disb.disb_amt)
FROM igf_gr_rfms_all rfms,
igf_gr_rfms_disb_all disb
WHERE rfms.award_id = cp_award_id
AND rfms.origination_id = disb.origination_id
AND disb.disb_ack_act_status IN ('S','A','D','C');
SELECT disb_num,
tp_cal_type,
tp_sequence_number,
ld_cal_type,
ld_sequence_number,
show_on_bill,
base_attendance_type_code
FROM igf_aw_awd_disb disb
WHERE disb.award_id = p_award_id
AND ROWNUM = 1
ORDER BY disb_num DESC;
igf_aw_awd_disb_pkg.update_row(
x_rowid => disb_rec.row_id,
x_award_id => disb_rec.award_id,
x_disb_num => disb_rec.disb_num,
x_tp_cal_type => disb_rec.tp_cal_type,
x_tp_sequence_number => disb_rec.tp_sequence_number,
x_disb_gross_amt => 0,
x_fee_1 => 0,
x_fee_2 => 0,
x_disb_net_amt => 0,
x_disb_date => disb_rec.disb_date,
x_trans_type => 'C',
x_elig_status => 'A',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => disb_rec.affirm_flag,
x_hold_rel_ind => disb_rec.hold_rel_ind,
x_manual_hold_ind => disb_rec.manual_hold_ind,
x_disb_status => disb_rec.disb_status,
x_disb_status_date => disb_rec.disb_status_date,
x_late_disb_ind => disb_rec.late_disb_ind,
x_fund_dist_mthd => disb_rec.fund_dist_mthd,
x_prev_reported_ind => disb_rec.prev_reported_ind,
x_fund_release_date => disb_rec.fund_release_date,
x_fund_status => disb_rec.fund_status,
x_fund_status_date => disb_rec.fund_status_date,
x_fee_paid_1 => disb_rec.fee_paid_1,
x_fee_paid_2 => disb_rec.fee_paid_2,
x_cheque_number => disb_rec.cheque_number,
x_ld_cal_type => disb_rec.ld_cal_type,
x_ld_sequence_number => disb_rec.ld_sequence_number,
x_disb_accepted_amt => 0,
x_disb_paid_amt => disb_rec.disb_paid_amt,
x_rvsn_id => disb_rec.rvsn_id,
x_int_rebate_amt => 0,
x_force_disb => disb_rec.force_disb,
x_min_credit_pts => disb_rec.min_credit_pts,
x_disb_exp_dt => disb_rec.disb_exp_dt,
x_verf_enfr_dt => disb_rec.verf_enfr_dt,
x_fee_class => disb_rec.fee_class,
x_show_on_bill => disb_rec.show_on_bill,
x_mode => 'R',
x_attendance_type_code => disb_rec.attendance_type_code,
x_base_attendance_type_code => disb_rec.base_attendance_type_code,
x_payment_prd_st_date => disb_rec.payment_prd_st_date,
x_change_type_code => disb_rec.change_type_code,
x_fund_return_mthd_code => disb_rec.fund_return_mthd_code,
x_direct_to_borr_flag => disb_rec.direct_to_borr_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.cancel_invalid_award.debug '|| g_req_id,'updated disbursement number '||disb_rec.disb_num);
igf_aw_award_pkg.update_row(
x_rowid => awd_rec.row_id,
x_award_id => awd_rec.award_id,
x_fund_id => awd_rec.fund_id,
x_base_id => awd_rec.base_id,
x_offered_amt => 0,
x_accepted_amt => 0,
x_paid_amt => awd_rec.paid_amt,
x_packaging_type => awd_rec.packaging_type,
x_batch_id => awd_rec.batch_id,
x_manual_update => awd_rec.manual_update,
x_rules_override => awd_rec.rules_override,
x_award_date => awd_rec.award_date,
x_award_status => 'CANCELLED',
x_attribute_category => awd_rec.attribute_category,
x_attribute1 => awd_rec.attribute1,
x_attribute2 => awd_rec.attribute2,
x_attribute3 => awd_rec.attribute3,
x_attribute4 => awd_rec.attribute4,
x_attribute5 => awd_rec.attribute5,
x_attribute6 => awd_rec.attribute6,
x_attribute7 => awd_rec.attribute7,
x_attribute8 => awd_rec.attribute8,
x_attribute9 => awd_rec.attribute9,
x_attribute10 => awd_rec.attribute10,
x_attribute11 => awd_rec.attribute11,
x_attribute12 => awd_rec.attribute12,
x_attribute13 => awd_rec.attribute13,
x_attribute14 => awd_rec.attribute14,
x_attribute15 => awd_rec.attribute15,
x_attribute16 => awd_rec.attribute16,
x_attribute17 => awd_rec.attribute17,
x_attribute18 => awd_rec.attribute18,
x_attribute19 => awd_rec.attribute19,
x_attribute20 => awd_rec.attribute20,
x_rvsn_id => awd_rec.rvsn_id,
x_alt_pell_schedule => awd_rec.alt_pell_schedule,
x_mode => 'R',
x_award_number_txt => awd_rec.award_number_txt,
x_legacy_record_flag => awd_rec.legacy_record_flag,
x_adplans_id => awd_rec.adplans_id,
x_lock_award_flag => awd_rec.lock_award_flag,
x_app_trans_num_txt => awd_rec.app_trans_num_txt,
x_awd_proc_status_code => 'AWARDED',
x_notification_status_code => awd_rec.notification_status_code,
x_notification_status_date => awd_rec.notification_status_date,
x_publish_in_ss_flag => awd_rec.publish_in_ss_flag
);
update_pell_orig_stat(awd_rec.award_id,0); --uodate the pell origination status with zero amount and 'Ready to Send' status
igf_aw_awd_disb_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => awd_rec.award_id,
x_disb_num => (l_last_disb.disb_num + 1),
x_tp_cal_type => l_last_disb.tp_cal_type,
x_tp_sequence_number => l_last_disb.tp_sequence_number,
x_disb_gross_amt => l_amount,
x_fee_1 => 0,
x_fee_2 => 0,
x_disb_net_amt => l_amount,
x_disb_date => SYSDATE,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => 'N',
x_hold_rel_ind => 'N',
x_manual_hold_ind => 'N',
x_disb_status => NULL,
x_disb_status_date => NULL,
x_late_disb_ind => 'N',
x_fund_dist_mthd => 'E',
x_prev_reported_ind => 'N',
x_fund_release_date => NULL,
x_fund_status => NULL,
x_fund_status_date => NULL,
x_fee_paid_1 => 0,
x_fee_paid_2 => 0,
x_cheque_number => NULL,
x_ld_cal_type => l_last_disb.ld_cal_type,
x_ld_sequence_number => l_last_disb.ld_sequence_number,
x_disb_accepted_amt => 0,
x_disb_paid_amt => 0,
x_rvsn_id => NULL,
x_int_rebate_amt => 0,
x_force_disb => NULL,
x_min_credit_pts => 0,
x_disb_exp_dt => NULL,
x_verf_enfr_dt => NULL,
x_fee_class => NULL,
x_show_on_bill => l_last_disb.show_on_bill,
x_attendance_type_code => NULL,
x_mode => 'R',
x_base_attendance_type_code => l_last_disb.base_attendance_type_code,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
SELECT awdt.award_id
FROM igf_aw_award_t awdt
WHERE process_id = cp_process_id
AND base_id = cp_base_id
AND flag IN ('AC','AR');
|| amounts. This PL/SQL table is then used to update the
|| IGF_AW_AWARD_T table.
||
|| Note, this procedure holds good only for non-PELL funds.
|| Disbursement rounding for Pell is handled in IGFGR11B.pls
||
|| Note, with the 'Equal' distribution method, the extra disbursement
|| amount is shared with the possible disbursements starting from
|| the last/first disbursement. But with 'Match COA'
|| and 'Manual' distribution the extra disbursement amount is not
|| shared with each disbursement but it is fully given either to
|| the first/last disbursement.
||
|| Description of the main variables used -
|| l_disb_amt Normal disbursement amount
||
|| l_disb_diff Holds the extra disbursement amount that needs to be shared
|| with the disbursements
||
|| l_extra_factor In 'Equal Distribution' this variables gives the share of the
|| extra amount for each disbursement. For ONES rounding it is 1
|| and for DECIMALS rounding it is 0.01.
||
|| l_disb_amt_extra In 'Equal Distribution', the extra disbursement amount is shared with the
|| possible disbursements. This variable holds (l_disb_amt + l_extra_factor)
||
|| l_trunc_factor The trunc factor to be used for rounding the decimal portion in the disbursement amount.
|| For ONES rounding this is 0 and for DECIMALS rounding this is 2
||
|| l_disb_no Holds the disbursement number that is currently being processed.
||
|| l_special_disb_no In 'Match COA' distribution the extra amount is fully given to either
|| first/last disbursement depending on the disbursement rounding value.
|| This variable is either 1/p_disb_count
||
|| l_disb_limt1, All these three variable form the loop attributes.
|| l_disb_limt2, If l_step = 1, the loop runs from l_disb_limt1 to l_disb_limt2.
|| l_step If l_step = -1, the loop runs from l_disb_limt2 to l_disb_limt1.
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who WHEN What
|| (reverse chronological order - newest change first)
*/
-- Returns all the disbursements in the temporary table
-- The ORDER BY clause ensures that the disbursements are returned
-- in the order of their creation
CURSOR cur_get_all_disb (
cp_fund_id igf_aw_award_t_all.fund_id%TYPE,
cp_base_id igf_aw_award_t_all.base_id%TYPE,
cp_process_id igf_aw_award_t_all.process_id%TYPE,
cp_adplans_id igf_aw_award_t_all.adplans_id%TYPE,
p_award_id igf_aw_award_t_all.award_id%TYPE
)
IS
SELECT awd_t.rowid, awd_t.*
FROM igf_aw_award_t_all awd_t
WHERE
base_id = p_base_id AND
fund_id = p_fund_id AND
process_id = p_process_id AND
NVL(adplans_id,-1) = NVL(p_adplans_id,-1) AND
NVL(award_id,-1) = NVL(p_award_id,-1) AND
flag = 'DB'
ORDER BY fnd_date.chardate_to_date(temp_char_val1) ASC;
igf_aw_award_t_pkg.update_row(
x_rowid => l_disb_rec.rowid,
x_process_id => l_disb_rec.process_id,
x_sl_number => l_disb_rec.sl_number,
x_fund_id => l_disb_rec.fund_id,
x_base_id => l_disb_rec.base_id,
x_offered_amt => NVL(l_disb_structure_rec(l_disb_no).disb_amt, 0),
x_accepted_amt => l_disb_rec.accepted_amt,
x_paid_amt => l_disb_rec.paid_amt,
x_need_reduction_amt => l_disb_rec.need_reduction_amt,
x_flag => l_disb_rec.flag,
x_temp_num_val1 => NVL(l_disb_structure_rec(l_disb_no).disb_amt, 0),
x_temp_num_val2 => l_disb_rec.temp_num_val2,
x_temp_char_val1 => l_disb_rec.temp_char_val1,
x_tp_cal_type => l_disb_rec.tp_cal_type,
x_tp_sequence_number => l_disb_rec.tp_sequence_number,
x_ld_cal_type => l_disb_rec.ld_cal_type,
x_ld_sequence_number => l_disb_rec.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_disb_rec.adplans_id,
x_app_trans_num_txt => l_disb_rec.app_trans_num_txt,
x_award_id => l_disb_rec.award_id,
x_lock_award_flag => l_disb_rec.lock_award_flag,
x_temp_val3_num => l_disb_rec.temp_val3_num,
x_temp_val4_num => l_disb_rec.temp_val4_num,
x_temp_char2_txt => l_disb_rec.temp_char2_txt,
x_temp_char3_txt => l_disb_rec.temp_char3_txt
);
|| 2. Modified the logic used to derive lb_disb_update
|| museshad 17-Apr-2006 Bug 5039489.
|| Negative entry was NOT getting posted to Student a/c when a
|| disb gets cancelled during repackaging process. This was
|| happening bcoz disb_net_amt was NOT getting set to 0 while
|| cancelling extra disb in the old award during repackaging.
|| Note, this issue is applicable to awards from all funds.
|| Also not directly relating to this bug, disb_paid_amt was
|| getting set to 0 in this case which should not happen actually.
|| Fixed these issues.
|| museshad 14-Apr-2006 Bug 5042136.
|| When a disb is cancelled- Origination Fee, Guarantor Fee,
|| Interest Rebate Amount should become 0. Fixed this.
|| Note, in repackaging when there are extra disb of the old
|| award they are cancelled.
|| museshad 02-Jun-2005 Build# FA157 - Bug# 4382371.
|| As per the new logic 'Award Notification Status' and 'Award Notification
|| Status Date' are got at the award level (from IGF_AW_AWARD_ALL table).
|| Previously they were got from IGF_AP_FA_BASE_REC_ALL table. Passed
|| NULL to these columns in the TBH call for
|| IGF_AP_FA_BASE_REC_ALL table.
|| ayedubat 12-OCT-04 Changed the TBH calls of igf_aw_awd_disb_pkg package to add a new column,
|| PAYMENT_PRD_ST_DATE as part of FA 149 build bug # 3416863
|| bkkumar 02-04-04 FACR116 - Added the new paramter p_alt_rel_code to the
|| get_loan_fee1 , get_loan_fee2 , get_cl_hold_rel_ind
|| ,get_cl_auto_late_ind
|| veramach 03-Dec-2003 FA 131 COD Updates
|| Adds cursor c_trans_num which is used to populate payment ISIR transaction number
|| veramach 20-NOV-2003 c_awd_tot cursor select adplans_id also
|| c_awd_disb,c_awd_disb_cnt choose adplans_id
|| Added cursor c_disb. This is used to apply NSLDS offset to first disb of the student
|| bkkumar 30-sep-2003 Added base_id to the get_loan_fee1 and
|| get_loan_fee2 and added l_auto_late_ind
|| for teh CL Loans
|| 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.
|| rasahoo 23-Apl-2003 Bug # 2860836
|| Added exception handling for resolving
|| locking problem created by fund manager
|| brajendr 07-Mar-2003 Bug # 2829487
|| Added the call to update the Process status of the student after adding the TO Do Items
||
|| masehgal 25-Feb-2003 Bug # 2662487 Introduced Logic to Round Off amounts for CL as well
|| Removed Hard Coded check for Fund Types
||
|| brajendr 09-Jan-2003 Bug # 2742000 Modified the logic for updating Notification Status.
|| Earlier notification status is done only for Auto Packaging
||
|| 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
||
|| pmarada 13-Feb-2002 Modified as part of FACR008-correspondence build, updateing fabase rec.
*/
-- Get all the awards which can be awarded to the students
CURSOR c_awd_tot( x_process_id igf_aw_award_t.process_id%TYPE,
x_base_id igf_aw_award_t.base_id%TYPE ) IS
SELECT awdt.fund_id,
awdt.adplans_id,
NVL(awdt.offered_amt,0) offered_amt,
NVL(awdt.accepted_amt,0) accepted_amt,
temp_char_val1 over_award,
temp_num_val1 common_perct,
award_id,
lock_award_flag
FROM igf_aw_award_t awdt
WHERE awdt.process_id = x_process_id
AND awdt.base_id = x_base_id
AND awdt.flag = 'FL';
SELECT fmast.pckg_awd_stat,
fmast.ci_cal_type,
fmast.ci_sequence_number,
fmast.nslds_disb_da,
fmast.disb_exp_da,
fmast.disb_verf_Da,
fmast.fee_type,
fmast.fund_code,
fmast.show_on_bill,
fmast.entitlement,
fcat.fund_source,
fcat.fed_fund_code,
fcat.sys_fund_type
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE fund_id = x_fund_id
AND fcat.fund_code = fmast.fund_code;
SELECT person_number, ssn, person_id
FROM igf_ap_fa_con_v faconv
WHERE faconv.base_id = l_base_id;
SELECT awdt.*
FROM igf_aw_award_t awdt
WHERE base_id = x_base_id
AND fund_id = x_fund_id
AND process_id = x_process_id
AND NVL(award_id,-1) = NVL(x_award_id,-1)
AND flag = 'DB'
AND NVL(adplans_id,-1) = NVL(x_adplans_id,-1)
ORDER BY fnd_date.chardate_to_date(awdt.temp_char_val1);
SELECT COUNT(*)
FROM igf_aw_award_t awdt
WHERE base_id = x_base_id
AND fund_id = x_fund_id
AND process_id = x_process_id
AND flag = 'DB'
AND NVL(adplans_id,-1) = NVL(x_adplans_id,-1)
AND NVL(award_id,-1) = NVL(x_award_id,-1);
SELECT 'x'
FROM igf_ap_nslds_data nslds,
igf_ap_fa_base_rec_all fabase
WHERE fabase.base_id = nslds.base_id AND
fabase.person_id = cp_person_id AND
nslds.nslds_loan_prog_code_1 IS NOT NULL;
SELECT fabase.*
FROM igf_ap_fa_base_rec fabase
WHERE base_id = cp_baseid
AND (notification_status NOT IN ('R','D') OR notification_status IS NULL);
SELECT rowid row_id,
disb.*
FROM igf_aw_awd_disb_all disb
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num;
lb_disb_update BOOLEAN := FALSE;
lv_update_notif_stat VARCHAR(2);
SELECT transaction_num
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND system_record_type = 'ORIGINAL'
AND payment_isir = 'Y';
SELECT awd.ROWID row_id,awd.*
FROM igf_aw_award_all awd
WHERE award_id = cp_award_id;
SELECT *
FROM igf_aw_awd_disb
WHERE award_id = cp_award_id
AND disb_num > cp_disb_num;
SELECT rfmd.ROWID row_id,
rfmd.*
FROM igf_gr_rfms_all rfms,
igf_gr_rfms_disb_all rfmd
WHERE rfms.origination_id = rfmd.origination_id
AND rfms.award_id = cp_award_id
AND rfmd.disb_ref_num = cp_disb_num;
SELECT rfms.origination_id
FROM igf_gr_rfms_all rfms
WHERE rfms.award_id = cp_award_id;
lv_update_notif_stat := 'F';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'inserting into igf_aw_award');
IF l_awd_tot.award_id IS NULL THEN --insert award only if the award is packaged.
igf_aw_award_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => l_award_id,
x_fund_id => l_awd_tot.fund_id,
x_base_id => l_base_id,
x_offered_amt => l_awd_tot.offered_amt * ln_com_perct / 100,
x_accepted_amt => l_awd_tot.accepted_amt * ln_com_perct / 100,
x_paid_amt => 0,
x_packaging_type => 'B',
x_batch_id => l_process_id,
x_manual_update => 'N',
x_rules_override => NULL,
x_award_date => TRUNC(SYSDATE),
x_award_status => l_award_status,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_rvsn_id => NULL,
x_mode => 'R',
x_alt_pell_schedule => l_alt_pell_schedule,
x_award_number_txt => NULL,
x_legacy_record_flag => NULL,
x_adplans_id => l_awd_tot.adplans_id,
x_lock_award_flag => NVL(l_awd_tot.lock_award_flag,'N'),
x_app_trans_num_txt => lv_app_trans_num_txt,
x_awd_proc_status_code => 'AWARDED',
x_notification_status_code => lv_awd_notif_status,
x_notification_status_date => TRUNC(sysdate),
x_publish_in_ss_flag => g_publish_in_ss_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'calling igf_ap_batch_ver_prc_pkg.update_process_status as l_post <> Y');
igf_ap_batch_ver_prc_pkg.update_process_status(l_base_id, NULL);
Since the fund is an entitlement, we should not insert overaward holds on the award.
we show a message to the user saying that this award will result in an overaward, but we are not
inserting overaward holds as the fund is an entitlement
*/
fnd_message.set_name('IGF','IGF_AW_ENTITLE_OVAWD');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'for last disb insert remaining amount!ln_db_act_gross_amt:'||ln_db_act_gross_amt);
'l_disb_date1 IS NOT NULL and l_disb_num = 1. So, setting lb_disb_update to TRUE to apply NSLDS offset');
lb_disb_update := TRUE;
'l_disb_date1 IS NULL and l_disb_num = 1. So, setting lb_disb_update to FALSE so that NSLDS offset is NOT applied');
lb_disb_update := FALSE;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'Before inserting into igf_aw_awd_disb table with l_attendance_type_code : '||l_attendance_type_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'inserting into igf_aw_awd_disb table with manual_hold_ind:'||l_hld_flg);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'Before inserting into igf_aw_awd_disb table with l_base_attendance_type_code : '||l_base_attendance_type_code);
igf_aw_awd_disb_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => l_award_id,
x_disb_num => l_disb_num,
x_tp_cal_type => l_awd_disb.tp_cal_type,
x_tp_sequence_number => l_awd_disb.tp_sequence_number,
x_disb_gross_amt => ln_db_act_gross_amt ,
x_fee_1 => NVL(l_fee_amt1, 0),
x_fee_2 => NVL(l_fee_amt2, 0),
x_disb_net_amt => NVL(l_awd_disb.temp_num_val1,0),
x_disb_date => l_disb_date,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => 'N',
x_hold_rel_ind => l_hold_rel_ind,
x_manual_hold_ind => l_hld_flg,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_late_disb_ind => l_auto_late_ind, -- FA 122 Added l_auto_late_ind
x_fund_dist_mthd => 'E',
x_prev_reported_ind => 'N',
x_fund_release_date => NULL,
x_fund_status => NULL,
x_fund_status_date => NULL,
x_fee_paid_1 => NVL(l_fee_paid1, 0),
x_fee_paid_2 => NVL(l_fee_paid2, 0),
x_cheque_number => NULL,
x_ld_cal_type => l_awd_disb.ld_cal_type,
x_ld_sequence_number => l_awd_disb.ld_sequence_number,
x_disb_accepted_amt => ln_db_act_accpt_amt,
x_disb_paid_amt => l_awd_disb.paid_amt,
x_rvsn_id => NULL,
x_int_rebate_amt => NVL(l_rebate, 0),
x_force_disb => NULL,
x_min_credit_pts => l_credits,
x_disb_exp_dt => l_exp_dt,
x_verf_enfr_dt => l_verf_dt,
x_fee_class => NULL,
x_show_on_bill => l_fmast.show_on_bill,
x_attendance_type_code => l_attendance_type_code,
x_mode => 'R',
x_base_attendance_type_code => l_base_attendance_type_code,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'after inserting into disb table....l_hld_flg: '||l_hld_flg);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'after inserting into disb table....l_attendance_type_code: '||l_attendance_type_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'inserting into igf_db_disb_holds table');
igf_db_disb_holds_pkg.insert_row(
x_rowid => lv_rowid,
x_hold_id => l_hold_id,
x_award_id => l_award_id,
x_disb_num => l_disb_num,
x_hold => 'OVERAWARD',
x_hold_date => TRUNC(SYSDATE),
x_hold_type => 'SYSTEM',
x_release_date => NULL,
x_release_flag => 'N',
x_release_reason => NULL,
x_mode => 'R'
);
IF lb_disb_update THEN
OPEN c_disb(l_award_id,1);
igf_aw_awd_disb_pkg.update_row(
x_mode => 'R',
x_rowid => l_disb.row_id,
x_award_id => l_disb.award_id,
x_disb_num => l_disb.disb_num,
x_tp_cal_type => l_disb.tp_cal_type,
x_tp_sequence_number => l_disb.tp_sequence_number,
x_disb_gross_amt => l_disb.disb_gross_amt,
x_fee_1 => l_disb.fee_1,
x_fee_2 => l_disb.fee_2,
x_disb_net_amt => l_disb.disb_net_amt,
x_disb_date => l_disb_date1,
x_trans_type => l_disb.trans_type,
x_elig_status => l_disb.elig_status,
x_elig_status_date => l_disb.elig_status_date,
x_affirm_flag => l_disb.affirm_flag,
x_hold_rel_ind => l_disb.hold_rel_ind,
x_manual_hold_ind => l_disb.manual_hold_ind,
x_disb_status => l_disb.disb_status,
x_disb_status_date => l_disb.disb_status_date,
x_late_disb_ind => l_disb.late_disb_ind,
x_fund_dist_mthd => l_disb.fund_dist_mthd,
x_prev_reported_ind => l_disb.prev_reported_ind ,
x_fund_release_date => l_disb.fund_release_date,
x_fund_status => l_disb.fund_status,
x_fund_status_date => l_disb.fund_status_date,
x_fee_paid_1 => l_disb.fee_paid_1,
x_fee_paid_2 => l_disb.fee_paid_2,
x_cheque_number => l_disb.cheque_number,
x_ld_cal_type => l_disb.ld_cal_type,
x_ld_sequence_number => l_disb.ld_sequence_number,
x_disb_accepted_amt => l_disb.disb_accepted_amt,
x_disb_paid_amt => l_disb.disb_paid_amt,
x_rvsn_id => l_disb.rvsn_id,
x_int_rebate_amt => l_disb.int_rebate_amt,
x_force_disb => l_disb.force_disb,
x_min_credit_pts => l_disb.min_credit_pts,
x_disb_exp_dt => l_disb.disb_exp_dt,
x_verf_enfr_dt => l_disb.verf_enfr_dt,
x_fee_class => l_disb.fee_class,
x_show_on_bill => l_disb.show_on_bill,
x_attendance_type_code => l_disb.attendance_type_code,
x_base_attendance_type_code => l_disb.base_attendance_type_code,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => l_disb.change_type_code,
x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb.direct_to_borr_flag
);
update_pell_orig_stat(l_award_det.award_id,l_awd_tot.offered_amt);
update_loan_stat(l_award_det.award_id,l_awd_tot.offered_amt);
igf_aw_award_pkg.update_row(
x_rowid => l_award_det.row_id,
x_award_id => l_award_det.award_id,
x_fund_id => l_award_det.fund_id,
x_base_id => l_award_det.base_id,
x_offered_amt => l_awd_tot.offered_amt,
x_accepted_amt => l_awd_tot.accepted_amt,
x_paid_amt => l_award_det.paid_amt,
x_packaging_type => l_award_det.packaging_type,
x_batch_id => l_award_det.batch_id,
x_manual_update => l_award_det.manual_update,
x_rules_override => l_award_det.rules_override,
x_award_date => l_award_det.award_date,
x_award_status => l_award_status,
x_attribute_category => l_award_det.attribute_category,
x_attribute1 => l_award_det.attribute1,
x_attribute2 => l_award_det.attribute2,
x_attribute3 => l_award_det.attribute3,
x_attribute4 => l_award_det.attribute4,
x_attribute5 => l_award_det.attribute5,
x_attribute6 => l_award_det.attribute6,
x_attribute7 => l_award_det.attribute7,
x_attribute8 => l_award_det.attribute8,
x_attribute9 => l_award_det.attribute9,
x_attribute10 => l_award_det.attribute10,
x_attribute11 => l_award_det.attribute11,
x_attribute12 => l_award_det.attribute12,
x_attribute13 => l_award_det.attribute13,
x_attribute14 => l_award_det.attribute14,
x_attribute15 => l_award_det.attribute15,
x_attribute16 => l_award_det.attribute16,
x_attribute17 => l_award_det.attribute17,
x_attribute18 => l_award_det.attribute18,
x_attribute19 => l_award_det.attribute19,
x_attribute20 => l_award_det.attribute20,
x_rvsn_id => l_award_det.rvsn_id,
x_mode => 'R',
x_alt_pell_schedule => l_award_det.alt_pell_schedule,
x_award_number_txt => l_award_det.award_number_txt,
x_legacy_record_flag => l_award_det.legacy_record_flag,
x_adplans_id => l_awd_tot.adplans_id,
x_lock_award_flag => NVL(l_awd_tot.lock_award_flag,'N'),
x_app_trans_num_txt => l_award_det.app_trans_num_txt,
x_awd_proc_status_code => 'AWARDED',
x_notification_status_code => lv_awd_notif_status,
x_notification_status_date => TRUNC(sysdate),
x_publish_in_ss_flag => g_publish_in_ss_flag
);
Since the fund is an entitlement, we should not insert overaward holds on the award.
we show a message to the user saying that this award will result in an overaward, but we are not
inserting overaward holds as the fund is an entitlement
*/
fnd_message.set_name('IGF','IGF_AW_ENTITLE_OVAWD');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'for last disb insert remaining amount!ln_db_act_gross_amt:'||ln_db_act_gross_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'Setting lb_disb_update to TRUE');
lb_disb_update := TRUE;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'Setting lb_disb_update to FALSE');
lb_disb_update := FALSE;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'Before inserting into igf_aw_awd_disb table with l_attendance_type_code : '||l_attendance_type_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'inserting into igf_aw_awd_disb table with manual_hold_ind:'||l_hld_flg);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'calling igf_aw_awd_disb_pkg.update_row');
igf_aw_awd_disb_pkg.update_row(
x_mode => 'R',
x_rowid => l_disb.row_id,
x_award_id => l_disb.award_id,
x_disb_num => l_disb.disb_num,
x_tp_cal_type => l_awd_disb.tp_cal_type,
x_tp_sequence_number => l_awd_disb.tp_sequence_number,
x_disb_gross_amt => ln_db_act_gross_amt ,
x_fee_1 => NVL(l_fee_amt1, 0),
x_fee_2 => NVL(l_fee_amt2, 0),
x_disb_net_amt => NVL(l_awd_disb.temp_num_val1,0),
x_disb_date => l_disb_date,
x_trans_type => l_disb.trans_type,
x_elig_status => l_disb.elig_status,
x_elig_status_date => l_disb.elig_status_date,
x_affirm_flag => l_disb.affirm_flag,
x_hold_rel_ind => l_hold_rel_ind,
x_manual_hold_ind => l_hld_flg,
x_disb_status => l_disb.disb_status,
x_disb_status_date => l_disb.disb_status_date,
x_late_disb_ind => l_auto_late_ind,
x_fund_dist_mthd => l_disb.fund_dist_mthd,
x_prev_reported_ind => l_disb.prev_reported_ind ,
x_fund_release_date => l_disb.fund_release_date,
x_fund_status => l_disb.fund_status,
x_fund_status_date => l_disb.fund_status_date,
x_fee_paid_1 => NVL(l_fee_paid1, 0),
x_fee_paid_2 => NVL(l_fee_paid2, 0),
x_cheque_number => l_disb.cheque_number,
x_ld_cal_type => l_awd_disb.ld_cal_type,
x_ld_sequence_number => l_awd_disb.ld_sequence_number,
x_disb_accepted_amt => ln_db_act_accpt_amt,
x_disb_paid_amt => l_disb.disb_paid_amt,
x_rvsn_id => l_disb.rvsn_id,
x_int_rebate_amt => NVL(l_rebate, 0),
x_force_disb => l_disb.force_disb,
x_min_credit_pts => l_credits,
x_disb_exp_dt => l_exp_dt,
x_verf_enfr_dt => l_verf_dt,
x_fee_class => l_disb.fee_class,
x_show_on_bill => l_fmast.show_on_bill,
x_attendance_type_code => l_disb.attendance_type_code,
x_base_attendance_type_code => l_base_attendance_type_code,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => l_disb.change_type_code,
x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb.direct_to_borr_flag
);
Update the Pell Disbursement Origination Record, if it exists
*/
OPEN c_pell_disb_orig(l_disb.award_id,l_disb.disb_num);
igf_gr_rfms_disb_pkg.update_row(
x_rowid => l_pell_disb_orig.row_id,
x_rfmd_id => l_pell_disb_orig.rfmd_id,
x_origination_id => l_pell_disb_orig.origination_id,
x_disb_ref_num => l_pell_disb_orig.disb_ref_num,
x_disb_dt => l_pell_disb_orig.disb_dt,
x_disb_amt => l_pell_disb_orig.disb_amt,
x_db_cr_flag => l_pell_disb_orig.db_cr_flag,
x_disb_ack_act_status => l_pell_disb_orig.disb_ack_act_status,
x_disb_status_dt => l_pell_disb_orig.disb_status_dt,
x_accpt_disb_dt => l_pell_disb_orig.accpt_disb_dt,
x_disb_accpt_amt => l_pell_disb_orig.disb_accpt_amt,
x_accpt_db_cr_flag => l_pell_disb_orig.accpt_db_cr_flag,
x_disb_ytd_amt => l_pell_disb_orig.disb_ytd_amt,
x_pymt_prd_start_dt => l_pell_disb_orig.pymt_prd_start_dt,
x_accpt_pymt_prd_start_dt => l_pell_disb_orig.accpt_pymt_prd_start_dt,
x_edit_code => l_pell_disb_orig.edit_code,
x_rfmb_id => l_pell_disb_orig.rfmb_id,
x_mode => 'R',
x_ed_use_flags => l_pell_disb_orig.ed_use_flags
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'calling igf_aw_awd_disb_pkg.insert_row');
igf_aw_awd_disb_pkg.insert_row(
x_rowid => lv_rowid,
x_award_id => l_award_det.award_id,
x_disb_num => l_disb_num,
x_tp_cal_type => l_awd_disb.tp_cal_type,
x_tp_sequence_number => l_awd_disb.tp_sequence_number,
x_disb_gross_amt => ln_db_act_gross_amt ,
x_fee_1 => NVL(l_fee_amt1, 0),
x_fee_2 => NVL(l_fee_amt2, 0),
x_disb_net_amt => NVL(l_awd_disb.temp_num_val1,0),
x_disb_date => l_disb_date,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => 'N',
x_hold_rel_ind => l_hold_rel_ind,
x_manual_hold_ind => l_hld_flg,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_late_disb_ind => l_auto_late_ind, -- FA 122 Added l_auto_late_ind
x_fund_dist_mthd => 'E',
x_prev_reported_ind => 'N',
x_fund_release_date => NULL,
x_fund_status => NULL,
x_fund_status_date => NULL,
x_fee_paid_1 => NVL(l_fee_paid1, 0),
x_fee_paid_2 => NVL(l_fee_paid2, 0),
x_cheque_number => NULL,
x_ld_cal_type => l_awd_disb.ld_cal_type,
x_ld_sequence_number => l_awd_disb.ld_sequence_number,
x_disb_accepted_amt => ln_db_act_accpt_amt,
x_disb_paid_amt => l_awd_disb.paid_amt,
x_rvsn_id => NULL,
x_int_rebate_amt => NVL(l_rebate, 0),
x_force_disb => NULL,
x_min_credit_pts => l_credits,
x_disb_exp_dt => l_exp_dt,
x_verf_enfr_dt => l_verf_dt,
x_fee_class => NULL,
x_show_on_bill => l_fmast.show_on_bill,
x_attendance_type_code => l_attendance_type_code,
x_mode => 'R',
x_base_attendance_type_code => l_base_attendance_type_code,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
so insert a disbursement origination record for this new disbursement
we do this only for phase-in participant years - for full participant years,
there will no IGF_GR_RFMS_DISB record. While sending the origination, the data
is directly picked from the IGF_AW_AWD_DISB_ALL table.
*/
lv_row_id := NULL;
igf_gr_rfms_disb_pkg.insert_row(
x_mode => 'R',
x_rowid => lv_row_id,
x_rfmd_id => lv_rfmd_id,
x_origination_id => l_orig_id,
x_disb_ref_num => l_disb_num,
x_disb_dt => l_disb_date,
x_disb_amt => ln_db_act_gross_amt,
x_db_cr_flag => 'P',
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
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'after inserting into disb table....l_hld_flg: '||l_hld_flg);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'after inserting into disb table....l_attendance_type_code: '||l_attendance_type_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'inserting into igf_db_disb_holds table');
igf_db_disb_holds_pkg.insert_row(
x_rowid => lv_rowid,
x_hold_id => l_hold_id,
x_award_id => l_award_det.award_id,
x_disb_num => l_disb_num,
x_hold => 'OVERAWARD',
x_hold_date => TRUNC(SYSDATE),
x_hold_type => 'SYSTEM',
x_release_date => NULL,
x_release_flag => 'N',
x_release_reason => NULL,
x_mode => 'R'
);
igf_aw_awd_disb_pkg.update_row(
x_rowid => disb_cancel_rec.row_id,
x_award_id => disb_cancel_rec.award_id,
x_disb_num => disb_cancel_rec.disb_num,
x_tp_cal_type => disb_cancel_rec.tp_cal_type,
x_tp_sequence_number => disb_cancel_rec.tp_sequence_number,
x_disb_gross_amt => 0,
x_fee_1 => 0,
x_fee_2 => 0,
x_disb_net_amt => 0,
x_disb_date => disb_cancel_rec.disb_date,
x_trans_type => 'C',
x_elig_status => disb_cancel_rec.elig_status,
x_elig_status_date => disb_cancel_rec.elig_status_date,
x_affirm_flag => disb_cancel_rec.affirm_flag,
x_hold_rel_ind => disb_cancel_rec.hold_rel_ind,
x_manual_hold_ind => disb_cancel_rec.manual_hold_ind,
x_disb_status => disb_cancel_rec.disb_status,
x_disb_status_date => disb_cancel_rec.disb_status_date,
x_late_disb_ind => disb_cancel_rec.late_disb_ind,
x_fund_dist_mthd => disb_cancel_rec.fund_dist_mthd,
x_prev_reported_ind => disb_cancel_rec.prev_reported_ind,
x_fund_release_date => disb_cancel_rec.fund_release_date,
x_fund_status => disb_cancel_rec.fund_status,
x_fund_status_date => disb_cancel_rec.fund_status_date,
x_fee_paid_1 => disb_cancel_rec.fee_paid_1,
x_fee_paid_2 => disb_cancel_rec.fee_paid_2,
x_cheque_number => disb_cancel_rec.cheque_number,
x_ld_cal_type => disb_cancel_rec.ld_cal_type,
x_ld_sequence_number => disb_cancel_rec.ld_sequence_number,
x_disb_accepted_amt => 0,
x_disb_paid_amt => disb_cancel_rec.disb_paid_amt,
x_rvsn_id => disb_cancel_rec.rvsn_id,
x_int_rebate_amt => 0,
x_force_disb => disb_cancel_rec.force_disb,
x_min_credit_pts => disb_cancel_rec.min_credit_pts,
x_disb_exp_dt => disb_cancel_rec.disb_exp_dt,
x_verf_enfr_dt => disb_cancel_rec.verf_enfr_dt,
x_fee_class => disb_cancel_rec.fee_class,
x_show_on_bill => disb_cancel_rec.show_on_bill,
x_mode => 'R',
x_attendance_type_code => disb_cancel_rec.attendance_type_code,
x_base_attendance_type_code => disb_cancel_rec.base_attendance_type_code,
x_payment_prd_st_date => disb_cancel_rec.payment_prd_st_date,
x_change_type_code => disb_cancel_rec.change_type_code,
x_fund_return_mthd_code => disb_cancel_rec.fund_return_mthd_code,
x_direct_to_borr_flag => disb_cancel_rec.direct_to_borr_flag
);
IF lb_disb_update THEN
OPEN c_disb(l_award_det.award_id,1);
igf_aw_awd_disb_pkg.update_row(
x_mode => 'R',
x_rowid => l_disb.row_id,
x_award_id => l_disb.award_id,
x_disb_num => l_disb.disb_num,
x_tp_cal_type => l_disb.tp_cal_type,
x_tp_sequence_number => l_disb.tp_sequence_number,
x_disb_gross_amt => l_disb.disb_gross_amt,
x_fee_1 => l_disb.fee_1,
x_fee_2 => l_disb.fee_2,
x_disb_net_amt => l_disb.disb_net_amt,
x_disb_date => l_disb_date1,
x_trans_type => l_disb.trans_type,
x_elig_status => l_disb.elig_status,
x_elig_status_date => l_disb.elig_status_date,
x_affirm_flag => l_disb.affirm_flag,
x_hold_rel_ind => l_disb.hold_rel_ind,
x_manual_hold_ind => l_disb.manual_hold_ind,
x_disb_status => l_disb.disb_status,
x_disb_status_date => l_disb.disb_status_date,
x_late_disb_ind => l_disb.late_disb_ind,
x_fund_dist_mthd => l_disb.fund_dist_mthd,
x_prev_reported_ind => l_disb.prev_reported_ind ,
x_fund_release_date => l_disb.fund_release_date,
x_fund_status => l_disb.fund_status,
x_fund_status_date => l_disb.fund_status_date,
x_fee_paid_1 => l_disb.fee_paid_1,
x_fee_paid_2 => l_disb.fee_paid_2,
x_cheque_number => l_disb.cheque_number,
x_ld_cal_type => l_disb.ld_cal_type,
x_ld_sequence_number => l_disb.ld_sequence_number,
x_disb_accepted_amt => l_disb.disb_accepted_amt,
x_disb_paid_amt => l_disb.disb_paid_amt,
x_rvsn_id => l_disb.rvsn_id,
x_int_rebate_amt => l_disb.int_rebate_amt,
x_force_disb => l_disb.force_disb,
x_min_credit_pts => l_disb.min_credit_pts,
x_disb_exp_dt => l_disb.disb_exp_dt,
x_verf_enfr_dt => l_disb.verf_enfr_dt,
x_fee_class => l_disb.fee_class,
x_show_on_bill => l_disb.show_on_bill,
x_attendance_type_code => l_disb.attendance_type_code,
x_base_attendance_type_code => l_disb.base_attendance_type_code,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => l_disb.change_type_code,
x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb.direct_to_borr_flag
);
igf_aw_gen.update_fabase_awds( l_base_id, 'SINGLE' );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'called igf_aw_gen.update_fabase_awds with SINGLE');
lv_update_notif_stat := 'T';
igf_aw_gen.update_fabase_awds( l_base_id, 'SIMULATED' );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'called igf_aw_gen.update_fabase_awds with SIMULATED');
lv_update_notif_stat := 'F';
igf_aw_gen.update_fabase_awds( l_base_id, 'AUTO_PACKAGED' );
lv_update_notif_stat := 'T';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'called igf_aw_gen.update_fabase_awds with AUTO_PACKAGED');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'after update_fabase_awds , lv_update_notif_stat:'||lv_update_notif_stat);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'after update_fabase_awds , l_upd_awd_notif_status:'||l_upd_awd_notif_status);
IF l_upd_awd_notif_status = 'Y' AND lv_update_notif_stat = 'T' THEN
OPEN c_fabase(l_base_id );
igf_ap_fa_base_rec_pkg.update_row(
x_rowid => l_fabase.row_id,
x_base_id => l_fabase.base_id,
x_ci_cal_type => l_fabase.ci_cal_type,
x_person_id => l_fabase.person_id,
x_ci_sequence_number => l_fabase.ci_sequence_number,
x_org_id => l_fabase.org_id,
x_coa_pending => l_fabase.coa_pending,
x_verification_process_run => l_fabase.verification_process_run,
x_inst_verif_status_date => l_fabase.inst_verif_status_date,
x_manual_verif_flag => l_fabase.manual_verif_flag,
x_fed_verif_status => l_fabase.fed_verif_status,
x_fed_verif_status_date => l_fabase.fed_verif_status_date,
x_inst_verif_status => l_fabase.inst_verif_status,
x_nslds_eligible => l_fabase.nslds_eligible,
x_ede_correction_batch_id => l_fabase.ede_correction_batch_id,
x_fa_process_status_date => l_fabase.fa_process_status_date,
x_isir_corr_status => l_fabase.isir_corr_status,
x_isir_corr_status_date => l_fabase.isir_corr_status_date,
x_isir_status => l_fabase.isir_status,
x_isir_status_date => l_fabase.isir_status_date,
x_coa_code_f => l_fabase.coa_code_f,
x_coa_code_i => l_fabase.coa_code_i,
x_coa_f => l_fabase.coa_f,
x_coa_i => l_fabase.coa_i,
x_disbursement_hold => l_fabase.disbursement_hold,
x_fa_process_status => l_fabase.fa_process_status,
x_packaging_status => l_fabase.packaging_status,
x_packaging_status_date => l_fabase.packaging_status_date,
x_total_package_accepted => l_fabase.total_package_accepted,
x_total_package_offered => l_fabase.total_package_offered,
x_admstruct_id => l_fabase.admstruct_id,
x_admsegment_1 => l_fabase.admsegment_1,
x_admsegment_2 => l_fabase.admsegment_2,
x_admsegment_3 => l_fabase.admsegment_3,
x_admsegment_4 => l_fabase.admsegment_4,
x_admsegment_5 => l_fabase.admsegment_5,
x_admsegment_6 => l_fabase.admsegment_6,
x_admsegment_7 => l_fabase.admsegment_7,
x_admsegment_8 => l_fabase.admsegment_8,
x_admsegment_9 => l_fabase.admsegment_9,
x_admsegment_10 => l_fabase.admsegment_10,
x_admsegment_11 => l_fabase.admsegment_11,
x_admsegment_12 => l_fabase.admsegment_12,
x_admsegment_13 => l_fabase.admsegment_13,
x_admsegment_14 => l_fabase.admsegment_14,
x_admsegment_15 => l_fabase.admsegment_15,
x_admsegment_16 => l_fabase.admsegment_16,
x_admsegment_17 => l_fabase.admsegment_17,
x_admsegment_18 => l_fabase.admsegment_18,
x_admsegment_19 => l_fabase.admsegment_19,
x_admsegment_20 => l_fabase.admsegment_20,
x_packstruct_id => l_fabase.packstruct_id,
x_packsegment_1 => l_fabase.packsegment_1,
x_packsegment_2 => l_fabase.packsegment_2,
x_packsegment_3 => l_fabase.packsegment_3,
x_packsegment_4 => l_fabase.packsegment_4,
x_packsegment_5 => l_fabase.packsegment_5,
x_packsegment_6 => l_fabase.packsegment_6,
x_packsegment_7 => l_fabase.packsegment_7,
x_packsegment_8 => l_fabase.packsegment_8,
x_packsegment_9 => l_fabase.packsegment_9,
x_packsegment_10 => l_fabase.packsegment_10,
x_packsegment_11 => l_fabase.packsegment_11,
x_packsegment_12 => l_fabase.packsegment_12,
x_packsegment_13 => l_fabase.packsegment_13,
x_packsegment_14 => l_fabase.packsegment_14,
x_packsegment_15 => l_fabase.packsegment_15,
x_packsegment_16 => l_fabase.packsegment_16,
x_packsegment_17 => l_fabase.packsegment_17,
x_packsegment_18 => l_fabase.packsegment_18,
x_packsegment_19 => l_fabase.packsegment_19,
x_packsegment_20 => l_fabase.packsegment_20,
x_miscstruct_id => l_fabase.miscstruct_id,
x_miscsegment_1 => l_fabase.miscsegment_1,
x_miscsegment_2 => l_fabase.miscsegment_2,
x_miscsegment_3 => l_fabase.miscsegment_3,
x_miscsegment_4 => l_fabase.miscsegment_4,
x_miscsegment_5 => l_fabase.miscsegment_5,
x_miscsegment_6 => l_fabase.miscsegment_6,
x_miscsegment_7 => l_fabase.miscsegment_7,
x_miscsegment_8 => l_fabase.miscsegment_8,
x_miscsegment_9 => l_fabase.miscsegment_9,
x_miscsegment_10 => l_fabase.miscsegment_10,
x_miscsegment_11 => l_fabase.miscsegment_11,
x_miscsegment_12 => l_fabase.miscsegment_12,
x_miscsegment_13 => l_fabase.miscsegment_13,
x_miscsegment_14 => l_fabase.miscsegment_14,
x_miscsegment_15 => l_fabase.miscsegment_15,
x_miscsegment_16 => l_fabase.miscsegment_16,
x_miscsegment_17 => l_fabase.miscsegment_17,
x_miscsegment_18 => l_fabase.miscsegment_18,
x_miscsegment_19 => l_fabase.miscsegment_19,
x_miscsegment_20 => l_fabase.miscsegment_20,
x_prof_judgement_flg => l_fabase.prof_judgement_flg,
x_nslds_data_override_flg => l_fabase.nslds_data_override_flg,
x_target_group => l_fabase.target_group,
x_coa_fixed => l_fabase.coa_fixed,
x_coa_pell => l_fabase.coa_pell,
x_mode => 'R',
x_profile_status => l_fabase.profile_status,
x_profile_status_date => l_fabase.profile_status_date,
x_profile_fc => l_fabase.profile_fc,
x_tolerance_amount => l_fabase.tolerance_amount,
x_manual_disb_hold => l_fabase.manual_disb_hold,
x_pell_alt_expense => l_fabase.pell_alt_expense,
x_assoc_org_num => l_fabase.assoc_org_num,
x_award_fmly_contribution_type => l_fabase.award_fmly_contribution_type,
x_isir_locked_by => l_fabase.isir_locked_by,
x_lock_awd_flag => l_fabase.lock_awd_flag,
x_lock_coa_flag => l_fabase.lock_coa_flag,
x_adnl_unsub_loan_elig_flag => l_fabase.adnl_unsub_loan_elig_flag,
x_notification_status => NULL,
x_notification_status_date => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.post_award.debug '|| g_req_id,'updated FA Base');
SELECT 'x'
FROM igf_aw_awd_disb_all
WHERE award_id = p_award_id
AND trans_type = 'A'
AND ROWNUM = 1;
SELECT 'x'
FROM igf_aw_award_all awd,
igf_aw_awd_disb_all disb,
igf_aw_awd_prd_term aprd
WHERE awd.fund_id = p_fund_id
AND awd.base_id = p_base_id
AND awd.award_status IN ('ACCEPTED','OFFERED','CANCELLED')
AND awd.award_id = disb.award_id
AND disb.ld_cal_type = aprd.ld_cal_type
AND disb.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = p_awd_prd_code
AND aprd.ci_cal_type = p_ci_cal_type
AND aprd.ci_sequence_number = p_ci_sequence_number
AND ROWNUM = 1;
SELECT 'x'
FROM igf_aw_award_all
WHERE fund_id = p_fund_id
AND base_id = p_base_id
AND award_status IN ('ACCEPTED','OFFERED','CANCELLED')
AND ROWNUM = 1;
SELECT 'x'
FROM igf_sl_loans
WHERE award_id = p_award_id
AND loan_status NOT IN ('G','N');--ready to send and not ready to send
SELECT 'x'
FROM igf_gr_rfms_all
WHERE award_id = p_award_id
AND orig_action_code NOT IN ('R','N','A','D','C');--ready to send and not ready to send
SELECT fed_verif_status
FROM igf_ap_fa_base_rec_all
WHERE base_id = p_base_id;
SELECT 'x'
FROM DUAL
WHERE EXISTS(
SELECT 'x'
FROM igf_aw_awd_disb_all
WHERE award_id = p_award_id
AND (ld_cal_type, ld_sequence_number) IN(
SELECT ld_cal_type,
ld_sequence_number
FROM igf_aw_awd_prd_term
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND award_prd_cd = p_awd_prd_code))
AND EXISTS(
SELECT 'x'
FROM igf_aw_awd_disb_all
WHERE award_id = p_award_id
AND (ld_cal_type, ld_sequence_number) NOT IN(
SELECT ld_cal_type,
ld_sequence_number
FROM igf_aw_awd_prd_term
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND award_prd_cd = p_awd_prd_code));
SELECT 'x'
FROM igf_aw_award_t
WHERE base_id = cp_base_id
AND process_id = l_process_id
AND flag = 'AU'
AND fund_id NOT IN (SELECT fund_id
FROM igf_aw_awd_frml_det
WHERE formula_code = cp_target_group
AND ci_cal_type = cp_ci_cal_type
AND ci_sequence_number = cp_ci_sequence_number);
SELECT awd.fund_id
FROM igf_aw_award_t awd
WHERE awd.temp_char_val1 IS NULL
AND awd.flag IN ('CF','AU')
AND awd.base_id = cp_base_id
AND awd.process_id = l_process_id
AND NOT EXISTS (SELECT 'x'
FROM igf_aw_fn_rpkg_prty
WHERE fund_id = awd.fund_id);
SELECT ROWNUM priority,
awd.*
FROM igf_aw_fn_rpkg_prty prty,
igf_aw_award_t awd
WHERE awd.base_id = cp_base_id
AND awd.process_id = l_process_id
AND awd.flag IN ('CF','AU')
AND awd.fund_id = prty.fund_id
AND prty.ci_cal_type = cp_ci_cal_type
AND prty.ci_sequence_number = cp_ci_sequence_number
ORDER BY prty.fund_order_num, awd.award_id;
SELECT fund_code
FROM igf_aw_fund_mast_all
WHERE fund_id = cp_fund_id;
igf_aw_award_t_pkg.update_row(
x_rowid => l_merge.row_id,
x_process_id => l_merge.process_id,
x_sl_number => l_merge.sl_number,
x_fund_id => l_merge.fund_id,
x_base_id => l_merge.base_id,
x_offered_amt => l_merge.offered_amt,
x_accepted_amt => l_merge.accepted_amt,
x_paid_amt => l_merge.paid_amt,
x_need_reduction_amt => l_merge.need_reduction_amt,
x_flag => l_merge.flag,
x_temp_num_val1 => l_merge.temp_num_val1,
x_temp_num_val2 => l_merge.temp_num_val2,
x_temp_char_val1 => TO_CHAR(l_merge.priority),
x_tp_cal_type => l_merge.tp_cal_type,
x_tp_sequence_number => l_merge.tp_sequence_number,
x_ld_cal_type => l_merge.ld_cal_type,
x_ld_sequence_number => l_merge.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_merge.adplans_id,
x_app_trans_num_txt => l_merge.app_trans_num_txt,
x_award_id => l_merge.award_id,
x_lock_award_flag => l_merge.lock_award_flag,
x_temp_val3_num => l_merge.temp_val3_num,
x_temp_val4_num => l_merge.temp_val4_num,
x_temp_char2_txt => l_merge.temp_char2_txt,
x_temp_char3_txt => l_merge.temp_char3_txt
);
SELECT awd.award_id,
awd.offered_amt,
awd.paid_amt,
awd.lock_award_flag,
awd.adplans_id,
fmast.auto_pkg,
NVL(p_max_award_amt, fmast.max_award_amt) max_award_amt,
NVL(p_min_award_amt, fmast.min_award_amt) min_award_amt,
fmast.allow_overaward,
fmast.over_award_amt,
fmast.over_award_perct,
fmast.available_amt,
fmast.remaining_amt
FROM igf_aw_award_all awd, igf_aw_fund_mast_all fmast
WHERE awd.base_id = cp_base_id
AND awd.fund_id = cp_fund_id
AND awd.award_status IN('OFFERED', 'ACCEPTED', 'CANCELLED')
AND fmast.fund_id = awd.fund_id
AND lv_fed_fund_code <> 'PELL'
AND awd.award_id IN(
SELECT DISTINCT awd.award_id
FROM igf_aw_award_all awd,
igf_aw_awd_disb_all disb,
igf_aw_awd_prd_term aprd
WHERE awd.fund_id = cp_fund_id
AND awd.base_id = cp_base_id
AND awd.award_id = disb.award_id
AND disb.ld_cal_type = aprd.ld_cal_type
AND disb.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = cp_award_prd_code
AND aprd.ci_cal_type = cp_ci_cal_type
AND aprd.ci_sequence_number = cp_ci_sequence_number)
UNION ALL
SELECT awd.award_id,
awd.offered_amt,
awd.paid_amt,
awd.lock_award_flag,
awd.adplans_id,
fmast.auto_pkg,
NVL(p_max_award_amt, fmast.max_award_amt) max_award_amt,
NVL(p_min_award_amt, fmast.min_award_amt) min_award_amt,
fmast.allow_overaward,
fmast.over_award_amt,
fmast.over_award_perct,
fmast.available_amt,
fmast.remaining_amt
FROM igf_aw_award_all awd, igf_aw_fund_mast_all fmast
WHERE awd.base_id = cp_base_id
AND awd.fund_id = cp_fund_id
AND awd.award_status IN('OFFERED', 'ACCEPTED', 'CANCELLED')
AND fmast.fund_id = awd.fund_id
AND lv_fed_fund_code = 'PELL';
SELECT loan_status,
loan_chg_status
FROM igf_sl_loans
WHERE award_id = cp_award_id;
SELECT orig_action_code
FROM igf_gr_rfms_all
WHERE award_id = cp_award_id;
SELECT disb.disb_ack_act_status
FROM igf_gr_rfms_disb_all disb,
igf_gr_rfms_all rfms
WHERE rfms.origination_id = disb.origination_id
AND rfms.award_id = cp_award_id
AND disb.disb_ack_act_status = 'S'
AND ROWNUM = 1;
SELECT 'x'
FROM igf_aw_award_t_all
WHERE
process_id = l_process_id AND
fund_id = cp_fund_id AND
base_id = cp_base_id AND
flag = 'AL';
SELECT awd_t.rowid, awd_t.*
FROM igf_aw_award_t_all awd_t
WHERE
awd_t.flag = 'AU' AND
process_id = l_process_id AND
fund_id = cp_fund_id AND
base_id = cp_base_id;
SELECT row_id
FROM igf_aw_award_t awdt
WHERE fund_id = x_fund_id
AND flag = 'OV'
AND process_id = l_process_id;
SELECT SUM(DECODE(awd.award_status,'CANCELLED',1,0)) cancelled_awd,
SUM(DECODE(awd.award_status,'OFFERED',1,'ACCEPTED',1,0)) off_acc_awd
FROM igf_aw_award_t_all awdt,
igf_aw_award_all awd
WHERE awdt.fund_id = cp_fund_id
AND awdt.base_id = cp_base_id
AND awdt.process_id = l_process_id
AND awdt.flag = 'AU'
AND awdt.award_id = awd.award_id;
SELECT awdt.ROWID row_id,
awdt.*
FROM igf_aw_award_t_all awdt,
igf_aw_award_all awd
WHERE awdt.award_id = awd.award_id
AND awdt.fund_id = cp_fund_id
AND awdt.base_id = cp_base_id
AND awdt.process_id = l_process_id
AND awd.award_status = 'CANCELLED';
SELECT awdt.ROWID row_id,
awdt.*
FROM igf_aw_award_t_all awdt
WHERE awdt.award_id <> cp_award_id
AND awdt.base_id = cp_base_id
AND awdt.fund_id = cp_fund_id
AND awdt.process_id = l_process_id;
SELECT awd.award_id
FROM igf_aw_award_t_all awdt,
igf_aw_award_all awd
WHERE awdt.award_id = awd.award_id
AND awdt.fund_id = cp_fund_id
AND awdt.base_id = cp_base_id
AND awdt.process_id = l_process_id
AND awd.award_status = 'CANCELLED'
AND awdt.flag = 'AU'
ORDER BY awd.award_id DESC;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.load_awards.debug '|| g_req_id,'inserting into igf_aw_award_t with flag:OV for fund:'||p_fund_id||' and base_id:'||p_base_id);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => p_fund_id,
x_base_id => p_base_id,
x_offered_amt => 0 ,
x_accepted_amt => 0 ,
x_paid_amt => 0 ,
x_need_reduction_amt => NULL,
x_flag => 'OV',
x_temp_num_val1 => l_awds.remaining_amt,
x_temp_num_val2 => l_overaward,
x_temp_char_val1 => NULL,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => p_fund_id,
x_base_id => p_base_id,
x_offered_amt => igf_aw_coa_gen.award_amount(p_base_id,g_awd_prd,l_awds.award_id),
x_accepted_amt => 0,
x_paid_amt => 0,
x_need_reduction_amt => NULL,
x_flag => 'AL',
x_temp_num_val1 => NULL,
x_temp_num_val2 => NULL,
x_temp_char_val1 => NULL,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => l_awds.adplans_id,
x_app_trans_num_txt => NULL,
x_award_id => l_awds.award_id,
x_lock_award_flag => p_lock_award_flag,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => p_fund_id,
x_base_id => p_base_id,
x_offered_amt => igf_aw_coa_gen.award_amount(p_base_id,g_awd_prd,l_awds.award_id),
x_accepted_amt => 0,
x_paid_amt => 0,
x_need_reduction_amt => NULL,
x_flag => 'AU',
x_temp_num_val1 => NULL,
x_temp_num_val2 => NULL,
x_temp_char_val1 => p_seq_no,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NVL(p_adplans_id,g_plan_id),
x_app_trans_num_txt => NULL,
x_award_id => l_awds.award_id,
x_lock_award_flag => p_lock_award_flag,
x_temp_val3_num => l_awds.max_award_amt,
x_temp_val4_num => l_awds.min_award_amt,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_exist_awd.rowid,
x_process_id => l_exist_awd.process_id,
x_sl_number => l_exist_awd.sl_number,
x_fund_id => l_exist_awd.fund_id,
x_base_id => l_exist_awd.base_id,
x_offered_amt => l_exist_awd.offered_amt,
x_accepted_amt => l_exist_awd.accepted_amt,
x_paid_amt => l_exist_awd.paid_amt,
x_need_reduction_amt => l_exist_awd.need_reduction_amt,
x_flag => 'AL',
x_temp_num_val1 => l_exist_awd.temp_num_val1,
x_temp_num_val2 => l_exist_awd.temp_num_val2,
x_temp_char_val1 => l_exist_awd.temp_char_val1,
x_tp_cal_type => l_exist_awd.tp_cal_type,
x_tp_sequence_number => l_exist_awd.tp_sequence_number,
x_ld_cal_type => l_exist_awd.ld_cal_type,
x_ld_sequence_number => l_exist_awd.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_exist_awd.adplans_id,
x_app_trans_num_txt => l_exist_awd.app_trans_num_txt,
x_award_id => l_exist_awd.award_id,
x_lock_award_flag => l_exist_awd.lock_award_flag,
x_temp_val3_num => l_exist_awd.temp_val3_num,
x_temp_val4_num => l_exist_awd.temp_val4_num,
x_temp_char2_txt => l_exist_awd.temp_char2_txt,
x_temp_char3_txt => l_exist_awd.temp_char3_txt
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_lock_pell_awd.row_id,
x_process_id => l_lock_pell_awd.process_id,
x_sl_number => l_lock_pell_awd.sl_number,
x_fund_id => l_lock_pell_awd.fund_id,
x_base_id => l_lock_pell_awd.base_id,
x_offered_amt => l_lock_pell_awd.offered_amt,
x_accepted_amt => l_lock_pell_awd.accepted_amt,
x_paid_amt => l_lock_pell_awd.paid_amt,
x_need_reduction_amt => l_lock_pell_awd.need_reduction_amt,
x_flag => 'AL',
x_temp_num_val1 => l_lock_pell_awd.temp_num_val1,
x_temp_num_val2 => l_lock_pell_awd.temp_num_val2,
x_temp_char_val1 => l_lock_pell_awd.temp_char_val1,
x_tp_cal_type => l_lock_pell_awd.tp_cal_type,
x_tp_sequence_number => l_lock_pell_awd.tp_sequence_number,
x_ld_cal_type => l_lock_pell_awd.ld_cal_type,
x_ld_sequence_number => l_lock_pell_awd.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_lock_pell_awd.adplans_id,
x_app_trans_num_txt => l_lock_pell_awd.app_trans_num_txt,
x_award_id => l_lock_pell_awd.award_id,
x_lock_award_flag => l_lock_pell_awd.lock_award_flag,
x_temp_val3_num => l_lock_pell_awd.temp_val3_num,
x_temp_val4_num => l_lock_pell_awd.temp_val4_num,
x_temp_char2_txt => l_lock_pell_awd.temp_char2_txt,
x_temp_char3_txt => l_lock_pell_awd.temp_char3_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.load_awards.debug '|| g_req_id,'update the latest PELL to re-packagable');
igf_aw_award_t_pkg.update_row(
x_rowid => l_lock_pell_awd.row_id,
x_process_id => l_lock_pell_awd.process_id,
x_sl_number => l_lock_pell_awd.sl_number,
x_fund_id => l_lock_pell_awd.fund_id,
x_base_id => l_lock_pell_awd.base_id,
x_offered_amt => l_lock_pell_awd.offered_amt,
x_accepted_amt => l_lock_pell_awd.accepted_amt,
x_paid_amt => l_lock_pell_awd.paid_amt,
x_need_reduction_amt => l_lock_pell_awd.need_reduction_amt,
x_flag => 'AL',
x_temp_num_val1 => l_lock_pell_awd.temp_num_val1,
x_temp_num_val2 => l_lock_pell_awd.temp_num_val2,
x_temp_char_val1 => l_lock_pell_awd.temp_char_val1,
x_tp_cal_type => l_lock_pell_awd.tp_cal_type,
x_tp_sequence_number => l_lock_pell_awd.tp_sequence_number,
x_ld_cal_type => l_lock_pell_awd.ld_cal_type,
x_ld_sequence_number => l_lock_pell_awd.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_lock_pell_awd.adplans_id,
x_app_trans_num_txt => l_lock_pell_awd.app_trans_num_txt,
x_award_id => l_lock_pell_awd.award_id,
x_lock_award_flag => l_lock_pell_awd.lock_award_flag,
x_temp_val3_num => l_lock_pell_awd.temp_val3_num,
x_temp_val4_num => l_lock_pell_awd.temp_val4_num,
x_temp_char2_txt => l_lock_pell_awd.temp_char2_txt,
x_temp_char3_txt => l_lock_pell_awd.temp_char3_txt
);
|| veramach 08-Dec-2003 FA 131 COD Updates
|| Added validations so that funds with no matching terms of the distribution plan and COA
|| for the student are not loaded
|| veramach 20-NOV-2003 FA 125 multiple distr methods
|| 1.changed cursor c_fund_ld to fetch adplans_id,
|| max_num_disb,min_num_disb
|| 2.Added validations to reject a fund if the distribution will result in
|| disbursements whose number is not within the max/min levels set in fund manager
*/
-- Retrieves all the funds that are part of the Formula Code in Sequence and loads the temporary table
-- If this procedure being called from Single Fund, then retrive the details of fund directly.
-- If not called from Single fund, then load all the funds which are linked to the given group code
CURSOR c_fund_ld(
x_group_code igf_aw_awd_frml_det_all.formula_code%TYPE,
x_ci_cal_type igf_aw_awd_frml_det_all.ci_cal_type%TYPE,
x_ci_sequence_number igf_aw_awd_frml_det_all.ci_sequence_number%TYPE
) IS
SELECT fmdet.fund_id fund_id,
fmast.fund_code,
fmdet.seq_no seq_no,
fmdet.max_award_amt max_award_amt,
fmdet.min_award_amt min_award_amt,
fmdet.replace_fc replace_fc,
fmast.allow_overaward allow_overaward,
fmast.over_award_amt over_award_amt,
fmast.over_award_perct over_award_perct,
fmast.available_amt available_amt,
fmast.remaining_amt remaining_amt,
fmast.max_num_disb max_num_disb,
fmast.min_num_disb min_num_disb,
fmast.donot_repkg_if_code donot_repkg_if_code,
fmdet.pe_group_id pe_group_id,
fmdet.adplans_id adplans_id,
fmast.re_pkg_verif_flag re_pkg_verif_flag,
NVL(fmdet.lock_award_flag,fmast.lock_award_flag) lock_award_flag
FROM igf_aw_awd_frml_det fmdet,
igf_aw_fund_mast_all fmast
WHERE fmdet.formula_code = x_group_code
AND fmdet.ci_cal_type = x_ci_cal_type
AND fmdet.ci_sequence_number = x_ci_sequence_number
AND fmdet.fund_id = fmast.fund_id
AND fmast.discontinue_fund <> 'Y'
AND g_sf_packaging = 'F'
UNION
SELECT fmast.fund_id fund_id,
fmast.fund_code,
1 seq_no,
fmast.max_award_amt max_award_amt,
fmast.min_award_amt min_award_amt,
fmast.replace_fc replace_fc,
fmast.allow_overaward allow_overaward,
fmast.over_award_amt over_award_amt,
fmast.over_award_perct over_award_perct,
fmast.available_amt available_amt,
fmast.remaining_amt remaining_amt,
fmast.max_num_disb max_num_disb,
fmast.min_num_disb min_num_disb,
fmast.donot_repkg_if_code donot_repkg_if_code,
0 pe_group_id,
g_plan_id adplans_id,
fmast.re_pkg_verif_flag re_pkg_verif_flag,
NVL(g_lock_award,fmast.lock_award_flag) lock_award_flag
FROM igf_aw_fund_mast_all fmast
WHERE fmast.discontinue_fund <> 'Y'
AND g_sf_packaging = 'T'
AND fund_id = g_sf_fund
ORDER BY 3;
SELECT row_id
FROM igf_aw_award_t awdt
WHERE fund_id = x_fund_id
AND flag = 'OV'
AND process_id = l_process_id;
SELECT fmdet.seq_no, fmdet.pe_group_id, grp.group_cd
FROM igf_aw_awd_frml_det fmdet,
igf_aw_fund_mast fmast,
igs_pe_all_persid_group_v grp
WHERE fmast.fund_id = cp_fund_id
AND fmdet.fund_id = fmast.fund_id
AND fmdet.seq_no = cp_seq_no
AND fmdet.pe_group_id IS NOT NULL
AND fmdet.pe_group_id = grp.group_id
AND fmdet.formula_code = cp_formula_code
AND fmdet.ci_cal_type = cp_ci_cal_type
AND fmdet.ci_sequence_number = cp_ci_sequence_number;
SELECT 'x'
FROM dual
WHERE EXISTS (
SELECT DISTINCT ld_cal_type,ld_sequence_number
FROM igf_aw_dp_terms
WHERE adplans_id = cp_adplans_id
MINUS
SELECT ld_cal_type,ld_sequence_number
FROM igf_aw_awd_prd_term
WHERE ci_cal_type = cp_ci_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND award_prd_cd = cp_award_prd_code
);
SELECT DISTINCT fmast.fund_id,
fmast.lock_award_flag,
fmast.re_pkg_verif_flag,
fmast.donot_repkg_if_code
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_awd_disb_all disb,
igf_aw_awd_prd_term aprd
WHERE fmast.fund_id = awd.fund_id
AND awd.base_id = cp_base_id
AND awd.award_id = disb.award_id
AND disb.ld_cal_type = aprd.ld_cal_type
AND disb.ld_sequence_number = aprd.ld_sequence_number
AND aprd.ci_cal_type = fmast.ci_cal_type
AND aprd.ci_sequence_number = fmast.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND awd.fund_id NOT IN (SELECT fmdet.fund_id
FROM igf_aw_awd_frml_det_all fmdet
WHERE fmdet.formula_code = cp_group_code
AND fmdet.ci_cal_type = cp_ci_cal_type
AND fmdet.ci_sequence_number = cp_ci_sequence_number);
SELECT fund_source
FROM IGF_AW_FUND_CAT_ALL
WHERE fund_code = cp_fund_code;
SELECT ant_data.program_cd prog,
ant_data.ld_cal_type load_cal_type,
ant_data.ld_sequence_number load_seq_num
FROM
igf_aw_awd_prd_term awd_per,
igs_ca_inst_all cal_inst,
igf_ap_fa_ant_data ant_data,
igs_ps_ver prog
WHERE
awd_per.ld_cal_type = cal_inst.cal_type AND
awd_per.ld_sequence_number = cal_inst.sequence_number AND
ant_data.ld_cal_type = awd_per.ld_cal_type AND
ant_data.ld_sequence_number = awd_per.ld_sequence_number AND
awd_per.ci_cal_type = cp_ci_cal_type AND
awd_per.ci_sequence_number = cp_sequence_number AND
awd_per.award_prd_cd = cp_awd_per AND
ant_data.base_id = cp_base_id AND
ant_data.program_cd = prog.course_cd AND
prog.course_status = 'ACTIVE' AND
ant_data.program_cd IS NOT NULL
ORDER BY get_term_start_date(cp_base_id, awd_per.ld_cal_type, awd_per.ld_sequence_number) ASC,
prog.version_number DESC;
SELECT awd_t.rowid, awd_t.*
FROM igf_aw_award_t_all awd_t
WHERE
base_id = cp_base_id AND
fund_id = cp_fund_id AND
process_id = cp_process_id AND
flag <> 'AL';
SELECT adm.course_cd key_prog, adm.crv_version_number key_prog_ver
FROM
igs_ad_ps_appl_inst_all adm,
igs_ad_ou_stat s_adm_st,
igf_ap_fa_base_rec_all fabase
WHERE
adm.person_id = fabase.person_id AND
fabase.base_id = cp_base_id AND
adm.adm_outcome_status = s_adm_st.adm_outcome_status AND
s_adm_st.s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
adm.course_cd IS NOT NULL AND
1 = (SELECT COUNT(person_id)
FROM igs_ad_ps_appl_inst_all adm1, igs_ad_ou_stat s_adm_st1
WHERE
adm1.person_id = adm.person_id AND
adm1.adm_outcome_status = s_adm_st1.adm_outcome_status AND
s_adm_st1.s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
adm1.course_cd IS NOT NULL);
OPEN c_rmv_funds_check FOR 'SELECT 1
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = :base_id
AND fabase.person_id in ( '||lv_sql_stmt||') ' USING l_base_id, lc_rmv_funds.pe_group_id;
OPEN c_rmv_funds_check FOR 'SELECT 1
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = :base_id
AND fabase.person_id in ( '||lv_sql_stmt||') ' USING l_base_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.load_funds.debug '|| g_req_id,'inserting into igf_aw_award_t with flag:CF for fund:'||l_fund_ld.fund_id||' and base_id:'||l_base_id);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_fund_ld.fund_id,
x_base_id => l_base_id,
x_offered_amt => 0,
x_accepted_amt => 0,
x_paid_amt => 0,
x_need_reduction_amt => NULL,
x_flag => 'CF',
x_temp_num_val1 => NULL,
x_temp_num_val2 => NULL,
x_temp_char_val1 => TO_CHAR(l_fund_ld.seq_no),
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NVL(l_fund_ld.adplans_id,g_plan_id),
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => l_fund_ld.lock_award_flag,
x_temp_val3_num => l_fund_ld.max_award_amt,
x_temp_val4_num => l_fund_ld.min_award_amt,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.load_funds.debug '|| g_req_id,'inserting into igf_aw_award_t with flag:OV for fund:'||l_fund_ld.fund_id||' and base_id:'||l_base_id);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_fund_ld.fund_id,
x_base_id => l_base_id,
x_offered_amt => 0 ,
x_accepted_amt => 0 ,
x_paid_amt => 0 ,
x_need_reduction_amt => NULL,
x_flag => 'OV',
x_temp_num_val1 => l_fund_ld.remaining_amt,
x_temp_num_val2 => l_overaward,
x_temp_char_val1 => NULL,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_cancel_awd_rec.rowid,
x_process_id => l_cancel_awd_rec.process_id,
x_sl_number => l_cancel_awd_rec.sl_number,
x_fund_id => l_cancel_awd_rec.fund_id,
x_base_id => l_cancel_awd_rec.base_id,
x_offered_amt => 0,
x_accepted_amt => l_cancel_awd_rec.accepted_amt,
x_paid_amt => l_cancel_awd_rec.paid_amt,
x_need_reduction_amt => l_cancel_awd_rec.need_reduction_amt,
x_flag => 'AC',
x_temp_num_val1 => 0,
x_temp_num_val2 => l_cancel_awd_rec.temp_num_val2,
x_temp_char_val1 => l_cancel_awd_rec.temp_char_val1,
x_tp_cal_type => l_cancel_awd_rec.tp_cal_type,
x_tp_sequence_number => l_cancel_awd_rec.tp_sequence_number,
x_ld_cal_type => l_cancel_awd_rec.ld_cal_type,
x_ld_sequence_number => l_cancel_awd_rec.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_cancel_awd_rec.adplans_id,
x_app_trans_num_txt => l_cancel_awd_rec.app_trans_num_txt,
x_award_id => l_cancel_awd_rec.award_id,
x_lock_award_flag => l_cancel_awd_rec.lock_award_flag,
x_temp_val3_num => l_cancel_awd_rec.temp_val3_num,
x_temp_val4_num => l_cancel_awd_rec.temp_val4_num,
x_temp_char2_txt => l_cancel_awd_rec.temp_char2_txt,
x_temp_char3_txt => l_cancel_awd_rec.temp_char3_txt
);
SELECT *
FROM igf_aw_award_t
WHERE process_id = x_process_is
AND base_id = x_base_id
AND flag IN ('RF','AR');
SELECT awdt.*
FROM igf_aw_award_t awdt
WHERE awdt.process_id = x_process_id
AND awdt.base_id = x_base_id
AND awdt.flag IN ('CF','AU')
ORDER BY TO_NUMBER(awdt.temp_char_val1); -- Sequence order of the fund in Temp table
SELECT fexc.fund_code
FROM igf_aw_fund_excl fexc
WHERE fexc.fund_id = x_fund_id;
SELECT finc.fund_code
FROM igf_aw_fund_incl finc
WHERE finc.fund_id = x_fund_id;
SELECT COUNT(awdt.fund_id) ftotal
FROM igf_aw_award_t awdt,
igf_aw_fund_mast_all fmast,
igf_aw_dp_terms adterms,
igf_aw_awd_prd_term aprd
WHERE fmast.fund_code = x_fund_code
AND awdt.base_id = x_base_id
AND awdt.process_id = x_process_id
AND awdt.fund_id = fmast.fund_id
AND awdt.flag = 'CF'
AND awdt.adplans_id = adterms.adplans_id
AND aprd.ld_cal_type = adterms.ld_cal_type
AND aprd.ld_sequence_number = adterms.ld_sequence_number
AND aprd.award_prd_cd = x_award_prd_code
AND aprd.ci_cal_type = fmast.ci_cal_type
AND aprd.ci_sequence_number = fmast.ci_sequence_number;
SELECT COUNT(fmast.fund_code) ftotal
FROM igf_aw_award_all awd,
igf_aw_awd_disb_all disb,
igf_aw_fund_mast_all fmast,
igf_aw_awd_prd_term aprd
WHERE fmast.fund_code = x_fund_code
AND awd.base_id = x_base_id
AND awd.fund_id = fmast.fund_id
AND awd.award_id = disb.award_id
AND disb.ld_cal_type = aprd.ld_cal_type
AND disb.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = x_award_prd_code
AND aprd.ci_cal_type = fmast.ci_cal_type
AND aprd.ci_sequence_number = fmast.ci_sequence_number
AND awd.award_status IN ('OFFERED','ACCEPTED')
AND disb.trans_type <> 'C';
SELECT fund_code
FROM igf_aw_fund_mast
WHERE fund_id = cp_fund_id;
igf_aw_award_t_pkg.update_row(
x_rowid => l_init_flag.row_id,
x_process_id => l_init_flag.process_id,
x_sl_number => l_init_flag.sl_number,
x_fund_id => l_init_flag.fund_id,
x_base_id => l_init_flag.base_id,
x_offered_amt => l_init_flag.offered_amt,
x_accepted_amt => l_init_flag.accepted_amt,
x_paid_amt => l_init_flag.paid_amt ,
x_need_reduction_amt => l_init_flag.need_reduction_amt,
x_flag => l_flag,
x_temp_num_val1 => l_init_flag.temp_num_val1,
x_temp_num_val2 => l_init_flag.temp_num_val2,
x_temp_char_val1 => l_init_flag.temp_char_val1,
x_tp_cal_type => l_init_flag.tp_cal_type,
x_tp_sequence_number => l_init_flag.tp_sequence_number,
x_ld_cal_type => l_init_flag.ld_cal_type,
x_ld_sequence_number => l_init_flag.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_init_flag.adplans_id,
x_app_trans_num_txt => l_init_flag.app_trans_num_txt,
x_award_id => l_init_flag.award_id,
x_lock_award_flag => l_init_flag.lock_award_flag,
x_temp_val3_num => l_init_flag.temp_val3_num,
x_temp_val4_num => l_init_flag.temp_val4_num,
x_temp_char2_txt => l_init_flag.temp_char2_txt,
x_temp_char3_txt => l_init_flag.temp_char3_txt
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_fund_chk.row_id,
x_process_id => l_fund_chk.process_id,
x_sl_number => l_fund_chk.sl_number,
x_fund_id => l_fund_chk.fund_id,
x_base_id => l_fund_chk.base_id,
x_offered_amt => l_fund_chk.offered_amt,
x_accepted_amt => l_fund_chk.accepted_amt,
x_paid_amt => l_fund_chk.paid_amt ,
x_need_reduction_amt => l_fund_chk.need_reduction_amt,
x_flag => l_flag,
x_temp_num_val1 => l_fund_chk.temp_num_val1,
x_temp_num_val2 => l_fund_chk.temp_num_val2,
x_temp_char_val1 => l_fund_chk.temp_char_val1,
x_tp_cal_type => l_fund_chk.tp_cal_type,
x_tp_sequence_number => l_fund_chk.tp_sequence_number,
x_ld_cal_type => l_fund_chk.ld_cal_type,
x_ld_sequence_number => l_fund_chk.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_fund_chk.adplans_id,
x_app_trans_num_txt => l_fund_chk.app_trans_num_txt,
x_award_id => l_fund_chk.award_id,
x_lock_award_flag => l_fund_chk.lock_award_flag,
x_temp_val3_num => l_fund_chk.temp_val3_num,
x_temp_val4_num => l_fund_chk.temp_val4_num,
x_temp_char2_txt => l_fund_chk.temp_char2_txt,
x_temp_char3_txt => l_fund_chk.temp_char3_txt
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_fund_chk.row_id,
x_process_id => l_fund_chk.process_id,
x_sl_number => l_fund_chk.sl_number,
x_fund_id => l_fund_chk.fund_id,
x_base_id => l_fund_chk.base_id,
x_offered_amt => l_fund_chk.offered_amt,
x_accepted_amt => l_fund_chk.accepted_amt,
x_paid_amt => l_fund_chk.paid_amt ,
x_need_reduction_amt => l_fund_chk.need_reduction_amt,
x_flag => l_flag,
x_temp_num_val1 => l_fund_chk.temp_num_val1,
x_temp_num_val2 => l_fund_chk.temp_num_val2,
x_temp_char_val1 => l_fund_chk.temp_char_val1,
x_tp_cal_type => l_fund_chk.tp_cal_type,
x_tp_sequence_number => l_fund_chk.tp_sequence_number,
x_ld_cal_type => l_fund_chk.ld_cal_type,
x_ld_sequence_number => l_fund_chk.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_fund_chk.adplans_id,
x_app_trans_num_txt => l_fund_chk.app_trans_num_txt,
x_award_id => l_fund_chk.award_id,
x_lock_award_flag => l_fund_chk.lock_award_flag,
x_temp_val3_num => l_fund_chk.temp_val3_num,
x_temp_val4_num => l_fund_chk.temp_val4_num,
x_temp_char2_txt => l_fund_chk.temp_char2_txt,
x_temp_char3_txt => l_fund_chk.temp_char3_txt
);
SELECT *
FROM igf_aw_award_t
WHERE process_id = l_process_id
AND base_id = p_base_id
AND flag = 'AA';
SELECT coa_duration_efc_amt
FROM igf_ap_css_profile_all
WHERE active_profile = 'Y'
AND base_id = cp_base_id;
IF p_awds.replace_fc = 'Y' AND p_awds.update_need = 'Y' THEN
l_rem_efc := NVL(l_efc,0) - NVL(p_awds.award,0);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'Replace_EFC and Update_Need set in Fund Mgr for fund ' ||p_awds.fed_fund_code|| '. Updated EFC and Need.');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'Replace_EFC set in Fund Mgr for fund ' ||p_awds.fed_fund_code|| '. Updated EFC.');
ELSIF p_awds.update_need = 'Y' THEN
l_need := NVL(l_need,0) - NVL(p_awds.award,0);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'Update_Need set in Fund Mgr for fund ' ||p_awds.fed_fund_code|| '. Updated Need.');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'p_awds.replace_fc:'||p_awds.replace_fc||' p_awds.update_need:'||p_awds.update_need||'adplans_id:'||l_efc_cur.adplans_id);
igf_aw_award_t_pkg.update_row(
x_rowid => l_efc_cur.row_id ,
x_process_id => l_efc_cur.process_id ,
x_sl_number => l_efc_cur.sl_number,
x_fund_id => l_efc_cur.fund_id,
x_base_id => l_efc_cur.base_id,
x_offered_amt => l_efc_cur.offered_amt,
x_accepted_amt => l_efc_cur.accepted_amt,
x_paid_amt => l_efc_cur.paid_amt,
x_need_reduction_amt => l_efc_cur.need_reduction_amt,
x_flag => l_efc_cur.flag,
x_temp_num_val1 => l_rem_efc,
x_temp_num_val2 => l_need,
x_temp_char_val1 => l_efc_cur.temp_char_val1,
x_tp_cal_type => l_efc_cur.tp_cal_type,
x_tp_sequence_number => l_efc_cur.tp_sequence_number,
x_ld_cal_type => l_efc_cur.ld_cal_type,
x_ld_sequence_number => l_efc_cur.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_efc_cur.adplans_id,
x_app_trans_num_txt => l_efc_cur.app_trans_num_txt,
x_award_id => l_efc_cur.award_id,
x_lock_award_flag => l_efc_cur.lock_award_flag,
x_temp_val3_num => l_need_VB_AC,
x_temp_val4_num => l_efc_cur.temp_val4_num,
x_temp_char2_txt => l_efc_cur.temp_char2_txt,
x_temp_char3_txt => l_efc_cur.temp_char3_txt
);
IF NVL(p_awds.replace_fc,'N') = 'Y' AND NVL(p_awds.update_need,'N') = 'Y' THEN
l_rem_efc_f := NVL(l_efc_f,0) - NVL(p_awds.award,0);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'Replace_EFC and Update_Need set in Fund Mgr for fund ' ||p_awds.fed_fund_code|| '. Updated EFC and Need.');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'Replace_EFC set in Fund Mgr for fund ' ||p_awds.fed_fund_code|| '. Updated EFC.');
ELSIF p_awds.update_need ='Y' THEN
l_need_f := NVL(l_need_f,0) - NVL(p_awds.award,0);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'Update_Need set in Fund Mgr for fund ' ||p_awds.fed_fund_code|| '. Updated Need.');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,' p_awds.replace_fc : '||p_awds.replace_fc||' p_awds.update_need: '||p_awds.update_need);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'inserting calculated values into igf_aw_award_t with x_temp_char_val1=FEDERAL');
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => NULL,
x_base_id => p_base_id,
x_offered_amt => NULL,
x_accepted_amt => NULL,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'AA', -- Allready awarded either manually or in prior runs.
x_temp_num_val1 => l_rem_efc_f, -- efc to be passed
x_temp_num_val2 => l_need_f, -- need to be passed
x_temp_char_val1 => 'FEDERAL',
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => l_need_VB_AC_f,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.calc_need.debug '|| g_req_id,'inserting calculating values into igf_aw_award_t with x_temp_char_val1=INSTITUTIONAL');
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => NULL,
x_base_id => p_base_id,
x_offered_amt => NULL,
x_accepted_amt => NULL,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'AA', -- Allready awarded either manually or in prior runs.
x_temp_num_val1 => l_rem_efc_i, -- efc to be passed
x_temp_num_val2 => l_need_i, -- need to be passed
x_temp_char_val1 => 'INSTITUTIONAL',
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => l_need_VB_AC_i,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE base_id = p_base_id
AND active_isir = 'Y';
SELECT cssp_id
FROM igf_ap_css_profile_all
WHERE base_id = p_base_id
AND active_profile = 'Y';
SELECT DECODE(
MAX(status_order),
0, NULL,
1, 'AWARDED',
2, 'READY',
3, 'REVIEW',
4, 'DO_NOT_REPKG'
) award_processing_status
FROM (SELECT awd.award_id,
awd_proc_status_code,
DECODE(
awd.awd_proc_status_code,
NULL, 0,
'AWARDED', 1,
'READY', 2,
'REVIEW', 3,
'DO_NOT_REPKG', 4
) status_order
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast
WHERE fmast.ci_cal_type = p_ci_cal_type
AND fmast.ci_sequence_number = p_ci_sequence_number
AND awd.fund_id = fmast.fund_id
AND awd.base_id = p_base_id
AND NOT EXISTS(
SELECT disb.ld_cal_type,
disb.ld_sequence_number
FROM igf_aw_awd_disb_all 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 = p_ci_cal_type
AND apt.ci_sequence_number = p_ci_sequence_number
AND apt.award_prd_cd = p_award_prd_code));
SELECT awd.award_id,
awd.offered_amt award,
fm.fund_id fund_id,
fm.replace_fc replace_fc,
fm.update_need update_need,
NVL(fm.entitlement,'N') entitlement,
fcat.fed_fund_code fed_fund_code,
fm.fm_fc_methd fm_fc_methd
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fm,
igf_aw_fund_cat_all fcat
WHERE base_id = cp_base_id
AND awd.fund_id = fm.fund_id
AND fm.fund_code = fcat.fund_code
AND awd.award_status IN ('ACCEPTED','OFFERED')
AND awd.award_id NOT IN ( SELECT award_id
FROM igf_aw_award_t
WHERE base_id = cp_base_id
AND process_id = l_process_id
AND flag = 'AU'); -- AU is it is a candidate for repackaging
SELECT pe.party_number person_number, fa.packaging_status
FROM igf_ap_fa_base_rec_all fa,
hz_parties pe
WHERE fa.base_id = x_base_id
AND pe.party_id = fa.person_id;
SELECT fabase.*
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = x_base_id;
SELECT tgrp.*
FROM igf_aw_target_grp_all tgrp
WHERE tgrp.cal_type = x_ci_cal_type
AND tgrp.sequence_number = x_ci_sequence_number
AND tgrp.group_cd = x_group_cd;
SELECT COUNT(*)
FROM igf_aw_award_t
WHERE process_id = l_process_id
AND base_id = x_base_id
AND flag IN ('CF','AU');
for calculation of need. Modified CURSOR c_awds accordingly to select awards which cannot be re-packaged.
*/
/* If Student is awarded already then his resources need to be considered
Earlier logic of ignoring earlier awards is changed. Logic to calculate
COA is also removed as the COA is now changed
*/
-- Get all the awards which are already awarded to the students and update the Temp table
-- with the fund details, EFC and Need with the status as 'AA' - Already Awarded
OPEN c_awds( l_fabase.base_id);
update_fund( l_fund_id, l_seq_no, l_process_id, l_base_id ,l_failed_award_id);
SELECT pell_participant_code
FROM igf_ap_batch_aw_map_all
WHERE ci_cal_type = cp_ci_cal_type
AND ci_sequence_number = cp_ci_sequence_number;
|| veramach 08-Dec-2003 FA 131 COD Updates
|| Added validations so that the pell wrapper is called with different parameters
|| when student has COA and student does not have COA
|| This is required for the pell wrapper to distribute the award
|| veramach 03-Dec-2003 FA 131 COD Updates
|| Modifies the pell wrapper used to calculate pell award amount
|| veramach 20-NOV-2003 FA 125 Build - cursor c_fund selects adplans_id also
|| cursor c_get_term_prsnt uses adplans_id instead of fund_id to find matching terms %
|| c_awd_grp cursor selects adplans_id
|| veramach 13-OCT-2003 FA 124 Build - Added logic as specified in the logic flow specified in the TD
|| rasahoo 01-09-2003 Removed Cursor C_ENROLL_STATUS as part of FA-114 (Obsoletion
|| of base record history)
|| brajendr 09-Dec-2002 Bug # 2676394
|| Modified the logic for calculating the EFC value. Code for retriving
|| the value from efc_det table is replaced with igf_aw_packng_subfns.get_fed_efc
||
|| ssawhney 31october introduce check for FSEOG matching fund percentage
|| (reverse chronological order - newest change first)
*/
-- Retrieves all the funds that are part of the Formula Code in Sequence
-- It has a link to igf_aw_award_t with a flag = 'CF' ie the valid funds
CURSOR c_fund(
x_group_code igf_aw_target_grp_all.group_cd%TYPE,
x_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
x_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
x_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
x_process_id NUMBER
) IS
SELECT TO_NUMBER(awt.temp_char_val1) seq_no,
awt.fund_id fund_id,
awt.temp_val3_num max_award_amt,
awt.temp_val4_num min_award_amt,
awt.adplans_id adplans_id,
fmast.replace_fc replace_fc,
fmast.fund_code,
awt.lock_award_flag,
fmast.fm_fc_methd,
fcat.fund_source fund_source,
awt.award_id,
awt.offered_amt
FROM igf_aw_award_t awt,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE awt.fund_id = fmast.fund_id
AND fmast.fund_code = fcat.fund_code
AND awt.base_id = x_base_id
AND awt.flag IN ('CF','AU')
AND process_id = l_process_id
AND g_sf_packaging = 'F'
UNION
SELECT 1 seq_no,
fmast.fund_id fund_id,
max_award_amt,
min_award_amt,
awt1.adplans_id,
replace_fc,
fmast.fund_code,
awt1.lock_award_flag,
fmast.fm_fc_methd,
fcat.fund_source fund_source,
awt1.award_id,
awt1.offered_amt
FROM igf_aw_fund_mast_all fmast ,
igf_aw_award_t awt1,
igf_aw_fund_cat_all fcat
WHERE g_sf_packaging = 'T'
AND fmast.fund_id = g_sf_fund
AND fmast.fund_id = awt1.fund_id
AND fmast.fund_code = fcat.fund_code
AND awt1.flag IN ('CF','AU')
AND awt1.process_id = x_process_id
AND awt1.base_id = x_base_id
ORDER BY 1;
SELECT fmast.*,
fcat.fund_type,
fcat.fund_source,
fcat.fed_fund_code,
fcat.sys_fund_type,
'Y' emulate_fed,
DECODE(fmast.fm_fc_methd,'INSTITUTIONAL','Y','N') inst_method
FROM igf_aw_fund_mast_all fmast ,
igf_aw_fund_cat fcat
WHERE fmast.fund_id = cp_fund_id
AND fcat.fund_code = fmast.fund_code;
SELECT NVL(SUM(offered_amt),0) offered
FROM igf_aw_award_t awdt
WHERE awdt.base_id = x_base_id
AND awdt.fund_id = x_fund_id
AND awdt.process_id = x_process_id
AND awdt.flag = 'AW';
SELECT awdt.*
FROM igf_aw_award_t awdt
WHERE awdt.fund_id = x_fund_id
AND flag = 'OV'
AND process_id = l_process_id;
SELECT pell_att_code
FROM igf_ap_attend_map
WHERE attendance_type = cp_derived;
SELECT SUM(disb.disb_gross_amt) yr_total
FROM igf_aw_awd_disb_all disb,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast
WHERE disb.award_id = awd.award_id
AND fmast.fund_id = awd.fund_id
AND fmast.fund_id = cp_fund_id
AND awd.base_id = cp_base_id
AND awd.award_status IN ('OFFERED','ACCEPTED')
AND disb.trans_type <> 'C';
SELECT pe.party_number person_number
FROM hz_parties pe,
igf_ap_fa_base_rec_all fabase
WHERE fabase.base_id = cp_base_id
AND pe.party_id = fabase.person_id;
SELECT
NVL(SUM(NVL(disb.disb_gross_amt,0)),0) lf_total,
COUNT(DISTINCT awd.award_id) lf_count
FROM
igf_aw_awd_disb_all disb,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_ap_fa_base_rec_all fabase
WHERE fmast.fund_code = cp_fund_code
AND disb.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND awd.base_id = fabase.base_id
AND fabase.person_id = cp_person_id
AND disb.trans_type <> 'C'
AND awd.award_status IN ('OFFERED', 'ACCEPTED');
SELECT NVL(SUM(NVL(awd.offered_amt,0)),0) lf_total,
COUNT(awd.award_id) lf_count
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_ap_fa_base_rec fabase,
igf_ap_batch_aw_map_all bam
WHERE fmast.fund_code = cp_fund_code
AND awd.fund_id = fmast.fund_id
AND awd.base_id = fabase.base_id
AND fabase.person_id = cp_person_id
AND fabase.ci_cal_type = bam.ci_cal_type
AND fabase.ci_sequence_number = bam.ci_sequence_number
AND awd.award_status IN ('OFFERED', 'ACCEPTED')
AND bam.award_year_status_code IN ('LA','LE');
SELECT awdt.row_id row_id
FROM igf_aw_award_t awdt
WHERE awdt.process_id = x_process_id
AND awdt.base_id = x_base_id
AND awdt.fund_id = x_fund_id
AND flag = 'AW';
SELECT COUNT(*) cnt, fm.fund_id
FROM igf_aw_fund_mast_all fm,
igf_aw_fund_cat_all ca
WHERE fm.fund_code = ca.fund_code
AND ca.fed_fund_code ='FSEOG'
AND fm.fund_id IN ( SELECT awdt.fund_id
FROM igf_aw_award_t awdt
WHERE awdt.process_id = cp_process_id
AND awdt.base_id = cp_base_id )
GROUP BY fm.fund_id;
SELECT fsm.fund_id
FROM igf_aw_fseog_match fsm
WHERE fsm.ci_cal_type = cp_ci_cal_type
AND fsm.ci_sequence_number = cp_ci_sequence_number
ORDER BY fsm.fund_id;
SELECT NVL(SUM(awdt.offered_amt),0) offered_amt
FROM igf_aw_award_t awdt
WHERE awdt.fund_id= cp_fund_id
AND awdt.process_id = cp_process_id
AND awdt.base_id = cp_base_id
AND awdt.flag = 'AW'
UNION ALL
SELECT NVL(SUM(awd.offered_amt),0) offered_amt
FROM igf_aw_award_all awd
WHERE awd.fund_id = cp_fund_id
AND awd.base_id=cp_base_id
AND award_status NOT IN ('DECLINED','CANCELLED','STOPPED');
SELECT *
FROM igf_aw_award_t
WHERE process_id = l_process_id
AND base_id = l_fabase.base_id
AND flag IN ('ND','AA');
SELECT COUNT(award_id) cnt
FROM igf_aw_award_all
WHERE base_id = cp_base_id
AND fund_id = cp_fund_id
AND award_status NOT IN ('SIMULATED','CANCELLED');
SELECT fcat.fund_code fund_code,
fed_fund_code,
NVL(awd.offered_amt,0) offered_amt ,
award_date
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fm,
igf_aw_fund_cat_all fcat
WHERE awd.award_status IN ('OFFERED','ACCEPTED')
AND awd.base_id = cp_base_id
AND awd.fund_id = fm.fund_id
AND fm.fund_code = fcat.fund_code
AND fcat.fed_fund_code IN ('FLS','FLU','DLS','DLU')
AND awd.award_id NOT IN (SELECT award_id
FROM igf_aw_award_t
WHERE award_id IS NOT NULL
AND base_id = cp_base_id
AND process_id = cp_process_id
AND flag IN ('AU','AW'))
/* Fetches all awards that are not getting repackaged */
UNION
SELECT fcat.fund_code fund_code,
fed_fund_code,
NVL(awdt.offered_amt,0),
SYSDATE award_date
FROM igf_aw_Award_t awdt,
igf_aw_fund_mast_all fmt,
igf_aw_fund_cat_all fcat
WHERE awdt.base_id = cp_base_id
AND awdt.process_id = cp_process_id
AND awdt.fund_id = fmt.fund_id
AND awdt.flag = 'AW'
AND fmt.fund_code = fcat.fund_code
AND fcat.fed_fund_code IN ('FLS','FLU','DLS','DLU');
SELECT terms.ld_perct_num,
terms.ld_cal_type,
terms.ld_sequence_number
FROM igf_aw_dp_terms terms
WHERE terms.adplans_id = cp_adplans_id
AND EXISTS (SELECT ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms coat
WHERE coat.base_id = cp_base_id
AND coat.ld_cal_type = terms.ld_cal_type
AND coat.ld_sequence_number = terms.ld_sequence_number
);
SELECT fmast.fm_fc_methd fm_fc_methd
FROM igf_aw_fund_mast_all fmast,
igf_aw_award_all awd
WHERE awd.fund_id = fmast.fund_id
AND awd.base_id=cp_base_id
AND fmast.fm_fc_methd = 'FEDERAL'
UNION
SELECT awd_t.temp_char_val1 fm_fc_methd
FROM igf_aw_award_t awd_t
WHERE awd_t.base_id = cp_base_id
AND awd_t.temp_char_val1 = 'FEDERAL'
and awd_t.flag = 'AA'
and awd_t.process_id = cp_process_id;
SELECT *
FROM igf_aw_award_t
WHERE award_id = cp_award_id
AND process_id = l_process_id
AND flag = 'AU';
SELECT disb.ld_cal_type,
disb.ld_sequence_number,
disb.base_attendance_type_code,
SUM(disb.disb_gross_amt) term_total,
awd.adplans_id
FROM igf_aw_awd_disb_all disb,
igf_aw_award_all awd
WHERE awd.award_id = disb.award_id
AND awd.award_id = p_award_id
GROUP BY disb.ld_cal_type,
disb.ld_sequence_number,
disb.base_attendance_type_code,
awd.adplans_id;
SELECT disb_date,
tp_cal_type,
tp_sequence_number
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number
AND ROWNUM = 1
ORDER BY disb_date;
for the student in the Temp table then calculate these and insert also into Temp table with flag as 'ND'
7. If the context fund is PELL, then get the PELL award amount form the PELL Regular or Alternate Matrix
And do all the entilement checks etc
a. If fund is Entitlement, then the student will get the awarded aid irrespective of target group level max limits.
and for non Entitlement funds group level limits should be enforced ( Bug 3400556 ). At the same time update the running totals
The following check are performed
i. Check for the Max Grant Amount limit
ii. Check for the Max Self Help Amount limit
iii. Check for the Max Gift Aid Amount limit
iv. Check for the Max Scholarship Amount limit
v. Check whether the award aid amount is crosing Max slab amount
b. If the fund package status is accepted, then update the accepted amount of the award
c. Set the Global award percentage to 100 as we are not considering the common terms for PELL.
8. If the fund is NOT PELL then
a. Check whether the student is still having the Need, if not having and fund is
not entilement or not replace FC then skip the fund and start awarding.
b. Get the total awarding amount for the context fund. ( Sum up from the temp table with the flag = 'AW' for the fund id + base id )
c. Get the Max amount limits defined at the fund level or target group level and award the student with that amount.
d. Decrease the Awarded Aid upto the extent of the common terms present at the COA Items and at the FUND.
Sum up all the percentages for the common terms and set to global variable.
e. If the Replace EFC is set for the fund and it is not already awarded then update students need. (Replace for the fund can
be considered only once for the fund. this check is not present for PELL as pell can be awarded only once for a student)
f. Check whether the award can be given with the overaward limit defined at the fund if the available amount defined at
the fund is vanished. ( This is making use of execeeding fund limits with the over award %)
g. Check whether the award is vaoilating the Max limit of fund, then award upto max limit.
h. If the fund is Entitlement then award aid upto the need of the student.
i. Check for students Maximum Yearly amount for context fund.
j. Check whether the student has already exceed the Max Life Terms (Maximum time student can get the fund), then log a message and skip the fund
k. Check for students Maximum Life amount for context fund.
l. Do all fund specific checks
i. Check for the Max Grant Amount limit
ii. Check for the Max Work Amount limit
iii. Check for the Max Self Help Amount limit
iv. Check for the Max Gift Aid Amount limit
v. Check for the Max Scholarship Amount limit
vi. Check whether the award aid amount is crosing Max slab amount
m. For LOANS, check for max loan amount
i. Update the Loans PL/SQL table with offered amounts and fund details
ii. Check for the loan limits set at Target group level.
n. check for the Minimum award Amount, If awarded aid is less then log a message and skip the fund.
o. If the awarded Aid is less than ZERO then log a message and skip the fund.
p. If the fund package status is accepted, then update the accepted amount of the award.
q. Update the Remaining Amount and Over Award Limt for the fund as these were changed after the current award.
9. Now the Actual Aid is calculated. Insert this award into the Temp table.
10. If Replace FC and Update Need are set. Accordingly update the running totals in the Temp tables.
This amounts will be used while awarding the same fund once again as specified in the Target Group.
*/
-- initialise package variables.
l_fund_fail := FALSE;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'inserting into igf_aw_award_t with flag:ND and adplans_id: '||l_fund.adplans_id);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_fund.fund_id,
x_base_id => l_fabase.base_id,
x_offered_amt => NULL,
x_accepted_amt => NULL,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'ND',
x_temp_num_val1 => NVL(l_efc_i,0),
x_temp_num_val2 => NVL(l_need_i,0),
x_temp_char_val1 => 'INSTITUTIONAL',--l_fmast.fm_fc_methd,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_fund.fund_id,
x_base_id => l_fabase.base_id,
x_offered_amt => NULL,
x_accepted_amt => NULL,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'ND',
x_temp_num_val1 => NVL(l_efc_f,0),
x_temp_num_val2 => NVL(l_need_f,0),
x_temp_char_val1 => 'FEDERAL',--l_fmast.fm_fc_methd,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => NULL,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
g_pell_tab.DELETE;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'^^^inserting into igf_aw_award_t with flag GR^^^');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'^^^done inserting into igf_aw_award_t^^^');
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_fund.fund_id,
x_base_id => l_fabase.base_id,
x_offered_amt => l_term_aid - l_term_amounts.term_total,
x_accepted_amt => NULL,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'GR',
x_temp_num_val1 => NULL,
x_temp_num_val2 => NULL,
x_temp_char_val1 => l_first_disb_dt.disb_date,
x_tp_cal_type => l_first_disb_dt.tp_cal_type,
x_tp_sequence_number => l_first_disb_dt.tp_sequence_number,
x_ld_cal_type => l_term_amounts.ld_cal_type,
x_ld_sequence_number => l_term_amounts.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_term_amounts.adplans_id,
x_app_trans_num_txt => NULL,
x_award_id => l_fund.award_id,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => l_term_amounts.base_attendance_type_code,
x_temp_char3_txt => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'updated need after funding aid is l_need:'||l_need);
l_std_loan_tab.DELETE;
igf_aw_award_t_pkg.update_row(
x_rowid => l_cur_ovr_awd.row_id,
x_process_id => l_cur_ovr_awd.process_id,
x_sl_number => l_cur_ovr_awd.sl_number,
x_fund_id => l_cur_ovr_awd.fund_id,
x_base_id => l_cur_ovr_awd.base_id,
x_offered_amt => l_cur_ovr_awd.offered_amt,
x_accepted_amt => l_cur_ovr_awd.accepted_amt,
x_paid_amt => l_cur_ovr_awd.paid_amt,
x_need_reduction_amt => l_cur_ovr_awd.need_reduction_amt,
x_flag => l_cur_ovr_awd.flag,
x_temp_num_val1 => l_remaining_amt,
x_temp_num_val2 => l_overaward,
x_temp_char_val1 => l_cur_ovr_awd.temp_char_val1,
x_tp_cal_type => l_cur_ovr_awd.tp_cal_type,
x_tp_sequence_number => l_cur_ovr_awd.tp_sequence_number,
x_ld_cal_type => l_cur_ovr_awd.ld_cal_type,
x_ld_sequence_number => l_cur_ovr_awd.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_cur_ovr_awd.adplans_id,
x_app_trans_num_txt => l_cur_ovr_awd.app_trans_num_txt,
x_award_id => l_cur_ovr_awd.award_id,
x_lock_award_flag => l_cur_ovr_awd.lock_award_flag,
x_temp_val3_num => l_cur_ovr_awd.temp_val3_num,
x_temp_val4_num => l_cur_ovr_awd.temp_val4_num,
x_temp_char2_txt => l_cur_ovr_awd.temp_char2_txt,
x_temp_char3_txt => l_cur_ovr_awd.temp_char3_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'after awarding,inserting to igf_aw_award_t with flag:AW and l_aid: '||l_aid||' ln_award_perct:'||ln_award_perct||
'lock_award_flag:'||l_fund.lock_award_flag);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'inserting a new award with flag=AW'||
'process_id:'||l_process_id||
'fund_id:'||l_fmast.fund_id||
'base_id:'||l_fabase.base_id);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_fmast.fund_id,
x_base_id => l_fabase.base_id,
x_offered_amt => l_aid * 100 / ln_award_perct,
x_accepted_amt => l_accepted_amt * 100 / ln_award_perct,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'AW',
x_temp_num_val1 => NULL,
x_temp_num_val2 => NULL,
x_temp_char_val1 => NULL,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => l_fund.adplans_id,
x_app_trans_num_txt => NULL,
x_award_id => NULL,
x_lock_award_flag => l_fund.lock_award_flag,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_temp_awd.row_id,
x_process_id => l_temp_awd.process_id,
x_sl_number => l_temp_awd.sl_number,
x_fund_id => l_fmast.fund_id,
x_base_id => l_fabase.base_id,
x_offered_amt => l_aid * 100 / ln_award_perct,
x_accepted_amt => l_accepted_amt * 100 / ln_award_perct,
x_paid_amt => l_temp_awd.paid_amt,
x_need_reduction_amt => l_temp_awd.need_reduction_amt,
x_flag => 'AW',
x_temp_num_val1 => l_temp_awd.temp_num_val1,
x_temp_num_val2 => l_temp_awd.temp_num_val2,
x_temp_char_val1 => l_temp_awd.temp_char_val1,
x_tp_cal_type => l_temp_awd.tp_cal_type,
x_tp_sequence_number => l_temp_awd.tp_sequence_number,
x_ld_cal_type => l_temp_awd.ld_cal_type,
x_ld_sequence_number => l_temp_awd.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_fund.adplans_id,
x_app_trans_num_txt => l_temp_awd.app_trans_num_txt,
x_award_id => l_temp_awd.award_id,
x_lock_award_flag => l_temp_awd.lock_award_flag,
x_temp_val3_num => l_temp_awd.temp_val3_num,
x_temp_val4_num => l_temp_awd.temp_val4_num,
x_temp_char2_txt => l_temp_awd.temp_char2_txt,
x_temp_char3_txt => l_temp_awd.temp_char3_txt
);
'l_fmast.update_need:'||l_fmast.update_need||
'l_aid:'||l_aid||
'l_rem_rep_efc:'||l_rem_rep_efc||
'l_need:'||l_need||
'l_old_need:'||l_old_need
);
IF l_fund.replace_fc = 'Y' AND l_fmast.update_need = 'Y' THEN
l_rem_rep_efc := NVL(l_rem_rep_efc,0) - NVL(l_aid,0);
'Update Need and Replace FC are set, l_rem_rep_efc: '||l_rem_rep_efc
||' l_need_f: '||l_need_f||' l_need_i: '||l_need_i
||' l_need_VB_AC_f: '||l_need_VB_AC_f||' l_need_VB_AC_i: '||l_need_VB_AC_i);
ELSIF l_fmast.update_need = 'Y' THEN
l_need_f := NVL(l_need,0) - NVL(l_aid,0);
'Only Update Need is set, l_rem_rep_efc: '||l_rem_rep_efc
||' l_need_f: '||l_need_f||' l_need_i: '||l_need_i
||' l_need_VB_AC_f: '||l_need_VB_AC_f|| 'l_need_VB_AC_i: '||l_need_VB_AC_i);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'Both Update Need and Replace FC are NOT set, l_rem_rep_efc: '||l_rem_rep_efc||' l_need_f: '||l_need_f||' l_need_i: '||l_need_i);
'l_fmast.update_need:'||l_fmast.update_need||
'l_aid:'||l_aid||
'l_rem_rep_efc:'||l_rem_rep_efc||
'l_need:'||l_need||
'l_old_need:'||l_old_need
);
IF l_fmast.update_need = 'Y' THEN
l_need_i := NVL(l_need,0) - NVL(l_aid,0);
'Only Update Need is set, l_rem_rep_efc: '||l_rem_rep_efc
||' l_need_f: '||l_need_f||' l_need_i: '||l_need_i
||' l_need_VB_AC_f: '||l_need_VB_AC_f||' l_need_VB_AC_i: '||l_need_VB_AC_i);
igf_aw_award_t_pkg.update_row(
x_rowid => l_rem_efc.row_id ,
x_process_id => l_rem_efc.process_id ,
x_sl_number => l_rem_efc.sl_number,
x_fund_id => l_rem_efc.fund_id,
x_base_id => l_rem_efc.base_id,
x_offered_amt => l_rem_efc.offered_amt,
x_accepted_amt => l_rem_efc.accepted_amt,
x_paid_amt => l_rem_efc.paid_Amt,
x_need_reduction_amt => l_rem_efc.need_reduction_amt,
x_flag => l_rem_efc.flag,
x_temp_num_val1 => l_rem_efc.temp_num_val1,
x_temp_num_val2 => NVL(l_need_i,0),
x_temp_char_val1 => l_rem_efc.temp_char_val1,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => l_rem_efc.adplans_id,
x_app_trans_num_txt => l_rem_efc.app_trans_num_txt,
x_award_id => l_rem_efc.award_id,
x_lock_award_flag => l_rem_efc.lock_award_flag,
x_temp_val3_num => NVL(l_need_VB_AC_i,0),
x_temp_val4_num => l_rem_efc.temp_val4_num,
x_temp_char2_txt => l_rem_efc.temp_char2_txt,
x_temp_char3_txt => l_rem_efc.temp_char3_txt
);
igf_aw_award_t_pkg.update_row(
x_rowid => l_rem_efc.row_id ,
x_process_id => l_rem_efc.process_id ,
x_sl_number => l_rem_efc.sl_number,
x_fund_id => l_rem_efc.fund_id,
x_base_id => l_rem_efc.base_id,
x_offered_amt => l_rem_efc.offered_amt,
x_accepted_amt => l_rem_efc.accepted_amt,
x_paid_amt => l_rem_efc.paid_Amt,
x_need_reduction_amt => l_rem_efc.need_reduction_amt,
x_flag => l_rem_efc.flag,
x_temp_num_val1 => NVL(l_rem_rep_efc,0),
x_temp_num_val2 => NVL(l_need_f,0),
x_temp_char_val1 => l_rem_efc.temp_char_val1,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => l_rem_efc.adplans_id,
x_app_trans_num_txt => l_rem_efc.app_trans_num_txt,
x_award_id => l_rem_efc.award_id,
x_lock_award_flag => l_rem_efc.lock_award_flag,
x_temp_val3_num => NVL(l_need_VB_AC_f,0),
x_temp_val4_num => l_rem_efc.temp_val4_num,
x_temp_char2_txt => l_rem_efc.temp_char2_txt,
x_temp_char3_txt => l_rem_efc.temp_char3_txt
);
5. Update the running totals in the PL/SQL table for the base_id and Fund id combination
6. Insert a consolidated Record for the fund with the falg as 'FL'
*/
IF ( NOT l_fund_fail ) THEN
-- Get Consolidated Totals for each fund and validate the consolidated checks
IF g_sf_packaging = 'F' THEN
OPEN c_awd_grp FOR
SELECT awdt.fund_id,
fmast.fund_code,
SUM(NVL(awdt.offered_amt,0)) offered_amt ,
SUM(awdt.accepted_amt) accepted_amt ,
COUNT(*) total,
frml.seq_no,
awdt.adplans_id,
awdt.award_id,
awdt.lock_award_flag
FROM igf_aw_award_t awdt ,
igf_aw_fund_mast fmast,
(SELECT MIN(awdt1.temp_char_val1) seq_no,
awdt1.fund_id fund_id
FROM igf_aw_award_t awdt1
WHERE awdt1.process_id = l_process_id
AND awdt1.base_id = l_fabase.base_id
AND awdt1.flag = 'AW'
GROUP BY awdt1.fund_id) frml
WHERE awdt.process_id = l_process_id
AND awdt.base_id = l_fabase.base_id
AND fmast.fund_id = awdt.fund_id
AND awdt.flag = 'AW'
AND frml.fund_id = fmast.fund_id
GROUP BY awdt.fund_id,
awdt.adplans_id,
fmast.fund_code,
awdt.base_id,
frml.seq_no,
awdt.award_id,
awdt.lock_award_flag
ORDER BY frml.seq_no;
SELECT awdt.fund_id,
fmast.fund_code,
SUM(NVL(awdt.offered_amt,0)) offered_amt ,
SUM(awdt.accepted_amt) accepted_amt ,
COUNT(*) total,
1,
awdt.adplans_id,
awdt.award_id,
awdt.lock_award_flag
FROM igf_aw_award_t awdt,
igf_aw_fund_mast fmast
WHERE awdt.process_id = l_process_id
AND awdt.base_id = l_fabase.base_id
AND awdt.fund_id = fmast.fund_id
AND awdt.flag = 'AW'
GROUP BY awdt.fund_id,
awdt.adplans_id,
fmast.fund_code,
awdt.award_id,
awdt.lock_award_flag;
igf_aw_award_t_pkg.delete_row(l_awd_grp_funds.row_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'inserting to igf_aw_award_t with flag:FL for base_id:'||l_fabase.base_id||' g_over_awd:'||g_over_awd);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.process_stud.debug '|| g_req_id,'Before inserting with FL flag in the temp, ln_com_perct : '||ln_com_perct
||' l_awd_grp.fund_id:'||l_awd_grp.fund_id
||' l_aid:'||l_aid
||' l_fabase.base_id:'||l_fabase.base_id
||' l_awd_grp.award_id:'||l_awd_grp.award_id
);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid ,
x_process_id => l_process_id ,
x_sl_number => l_sl_number,
x_fund_id => l_awd_grp.fund_id,
x_base_id => l_fabase.base_id,
x_offered_amt => l_aid ,
x_accepted_amt => l_accepted_amt ,
x_paid_amt => NULL ,
x_need_reduction_amt => NULL,
x_flag => 'FL',
x_temp_num_val1 => NVL(ln_com_perct,100),
x_temp_num_val2 => NULL,
x_temp_char_val1 => g_over_awd,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_ld_cal_type => NULL,
x_ld_sequence_number => NULL,
x_mode => 'R',
x_adplans_id => l_awd_grp.adplans_id,
x_app_trans_num_txt => NULL,
x_award_id => l_awd_grp.award_id,
x_lock_award_flag => l_awd_grp.lock_award_flag,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual;
PROCEDURE update_fund(
l_fund_id IN NUMBER,
l_seq_no IN NUMBER,
l_process_id IN NUMBER,
l_base_id IN NUMBER,
l_award_id IN NUMBER
) IS
/*
|| Created By : skoppula
|| Created On : 02-JAN-2002
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who WHEN What
|| (reverse chronological order - newest change first)
*/
CURSOR c_awd_det(
x_fund_id igf_aw_fund_mast.fund_id%TYPE,
x_process_id NUMBER,
x_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
x_seq_no NUMBER,
x_award_id igf_aw_award_all.award_id%TYPE
) IS
SELECT awdt.*
FROM igf_aw_award_t awdt
WHERE fund_id = x_fund_id
AND (temp_char_val1 = TO_CHAR( x_seq_no ) OR x_seq_no =-1)
AND process_id = x_process_id
AND flag IN ('CF','AU')
AND base_id = x_base_id
AND NVL(award_id,-1) = NVL(x_award_id,-1);
SELECT SUM(paid_amount) paid_amount
FROM igf_se_payment pay,
igf_se_auth auth
WHERE pay.auth_id = auth.auth_id
AND auth.award_id = cp_award_id
AND auth.flag = 'A';
SELECT 'x'
FROM igf_aw_award_t
WHERE process_id = cp_process_id
AND award_id = cp_award_id
AND flag = 'AU'
AND TO_NUMBER(NVL(temp_char_val1,-1)) < cp_seq_no;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'update_fund called');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'fund_id:'||l_fund_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'base_id:'||l_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'process_id:'||l_process_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'l_seq_no:'||l_seq_no);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'l_award_id:'||l_award_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'Calling delete_row for');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'fund_id:'||l_awd_det.fund_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'base_id:'||l_awd_det.base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'flag:'||l_awd_det.flag);
igf_aw_award_t_pkg.delete_row( x_rowid => l_awd_det.row_id );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'Calling update_row for');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'fund_id:'||l_awd_det.fund_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'base_id:'||l_awd_det.base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'flag:'||l_awd_det.flag);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'award_id:'||l_awd_det.award_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'cancelling the award');
igf_aw_award_t_pkg.update_row(
x_rowid => l_awd_det.row_id,
x_process_id => l_awd_det.process_id,
x_sl_number => l_awd_det.sl_number,
x_fund_id => l_awd_det.fund_id,
x_base_id => l_awd_det.base_id,
x_offered_amt => l_awd_det.offered_amt,
x_accepted_amt => l_awd_det.accepted_amt,
x_paid_amt => l_awd_det.paid_amt,
x_need_reduction_amt => l_awd_det.need_reduction_amt,
x_flag => 'AC',
x_temp_num_val1 => l_awd_det.temp_num_val1,
x_temp_num_val2 => l_awd_det.temp_num_val2,
x_temp_char_val1 => l_awd_det.temp_char_val1,
x_tp_cal_type => l_awd_det.tp_cal_type,
x_tp_sequence_number => l_awd_det.tp_sequence_number,
x_ld_cal_type => l_awd_det.ld_cal_type,
x_ld_sequence_number => l_awd_det.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_awd_det.adplans_id,
x_app_trans_num_txt => l_awd_det.app_trans_num_txt,
x_award_id => l_awd_det.award_id,
x_lock_award_flag => l_awd_det.lock_award_flag,
x_temp_val3_num => l_awd_det.temp_val3_num,
x_temp_val4_num => l_awd_det.temp_val4_num,
x_temp_char2_txt => l_awd_det.temp_char2_txt,
x_temp_char3_txt => l_awd_det.temp_char3_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'deleting the award');
igf_aw_award_t_pkg.delete_row( x_rowid => l_awd_det.row_id );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.update_fund.debug '|| g_req_id,'update_fund finished');
fnd_message.set_token('NAME','IGF_AW_PACKAGING.UPDATE_FUND '||SQLERRM);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_packaging.update_fund.exception '|| g_req_id,'sql error message: '||SQLERRM);
END update_fund;
|| veramach 08-Dec-2003 FA 131 COD Updates
|| Added 2 new cursors c_auto_disb_wcoa,c_auto_disb_equal_wcoa
|| These cursors do not use student's COA terms for distributing the award
|| veramach 03-Dec-2003 FA 131 COD Updates
|| Existing logic to find disbursement is now used only for non-PELL funds
|| For PELL funds, the disbursements returned from the PELL wrapper is used.
|| veramach 21-NOV-2003 FA 125 - changed c_tp_perct to choose distribution % using adplans_id
|| Added c_auto_disb_equal,cur_terms_count,c_auto_disb_coa_match,c_coa
|| Modified cursor c_get_ofst
|| Added logic to choose distribution % based on distribution method
*/
-- Get the Teching and load calendar details of the fund for creating the disbursements
CURSOR c_tp_perct(
cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
) IS
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
100/cp_num_teach_periods perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
100/cp_num_teach_periods perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
ORDER BY 1;
SELECT COUNT(*)
FROM igf_aw_dp_terms terms,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
igf_aw_awd_dist_plans adplans,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = cp_adplans_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
AND adplans.adplans_id = terms.adplans_id
AND adplans.cal_type = aprd.ci_cal_type
AND adplans.sequence_number = aprd.ci_sequence_number
AND aprd.ld_cal_type = terms.ld_cal_type
AND aprd.ld_sequence_number = terms.ld_sequence_number
AND aprd.award_prd_cd = g_awd_prd;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(coa_term_amount/cp_total_coa_amount) * teach_periods.tp_perct_num perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number,
amount coa_term_amount
FROM igf_aw_coa_term_tot_v
WHERE base_id = cp_base_id) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
ORDER BY 1;
SELECT SUM(amount) coa
FROM igf_aw_coa_itm_terms coa_terms,
igf_aw_dp_terms terms,
igf_aw_awd_dist_plans adplans,
igf_aw_awd_prd_term aprd
WHERE terms.ld_cal_type = coa_terms.ld_cal_type
AND terms.ld_sequence_number = coa_terms.ld_sequence_number
AND coa_terms.base_id = cp_base_id
AND terms.adplans_id = cp_adplans_id
AND terms.adplans_id = adplans.adplans_id
AND adplans.cal_type = aprd.ci_cal_type
AND adplans.sequence_number = aprd.ci_sequence_number
AND aprd.ld_cal_type = terms.ld_cal_type
AND aprd.ld_sequence_number = terms.ld_sequence_number
AND aprd.award_prd_cd = g_awd_prd;
SELECT disb.rowid row_id,
disb.*
FROM igf_aw_awd_disb_all disb
WHERE disb.award_id = cp_award_id
AND disb.trans_type <> 'C'
ORDER BY disb.disb_num;
SELECT adplans_id
FROM igf_aw_award_all
WHERE award_id = cp_award_id;
SELECT awdt.*
FROM igf_aw_award_t awdt
WHERE process_id = l_process_id
AND base_id = cp_base_id
AND award_id = cp_award_id
AND flag = 'GR';
SELECT awdt.temp_char2_txt base_attendance_type_code
FROM igf_aw_award_t awdt
WHERE process_id = l_process_id
AND base_id = cp_base_id
AND award_id = cp_award_id
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
l_disb_dt.delete;
IF get_fed_fund_code(l_fund_id) <> 'PELL' THEN -- Added in FA 131 COD Updates Build
-- Get the Fund details like Amount, Calendar dates etc for each fund and insert INTO the Temporary table
-- and at the same time fetch the actual dates for the date aliases mentioned at the fund.
-- These details are used while validating the awards of the sutdents
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.get_disbursements.debug '|| g_req_id,'calling check_plan');
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid,
x_process_id => l_process_id,
x_sl_number => l_sl_number,
x_fund_id => l_fund_id,
x_base_id => l_base_id,
x_offered_amt => l_disb_amt,
x_accepted_amt => l_disb_accpt_amt,
x_paid_amt => 0,
x_need_reduction_amt => ((l_disb_amt/l_offered_amt)*100),
x_flag => 'DB',
x_temp_num_val1 => l_disb_amt,
x_temp_num_val2 => l_disb_num,
x_temp_char_val1 => fnd_date.date_to_chardate(l_tp_perct.disb_dt),
x_tp_cal_type => l_tp_perct.tp_cal_type,
x_tp_sequence_number => l_tp_perct.tp_sequence_number,
x_ld_cal_type => l_tp_perct.ld_cal_type,
x_ld_sequence_number => l_tp_perct.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_adplans_id,
x_app_trans_num_txt => NULL,
x_award_id => l_award_id,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid,
x_process_id => l_process_id,
x_sl_number => l_sl_number,
x_fund_id => l_fund_id,
x_base_id => l_base_id,
x_offered_amt => g_pell_tab(i).offered_amt,
x_accepted_amt => g_pell_tab(i).accepted_amt,
x_paid_amt => 0,
x_need_reduction_amt => g_pell_tab(i).offered_amt * 100 / l_offered_amt,
x_flag => 'DB',
x_temp_num_val1 => g_pell_tab(i).offered_amt,
x_temp_num_val2 => i,
x_temp_char_val1 => fnd_date.date_to_chardate(g_pell_tab(i).disb_dt),
x_tp_cal_type => g_pell_tab(i).tp_cal_type,
x_tp_sequence_number => g_pell_tab(i).tp_sequence_number,
x_ld_cal_type => g_pell_tab(i).ld_cal_type,
x_ld_sequence_number => g_pell_tab(i).ld_sequence_number,
x_mode => 'R',
x_adplans_id => g_pell_tab(i).adplans_id,
x_app_trans_num_txt => g_pell_tab(i).app_trans_num_txt,
x_award_id => l_award_id,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NULL,
x_temp_char3_txt => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.get_disbursements.debug '|| g_req_id,'^^^inserting into igf_aw_award_t for PELL(old)^^^');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.get_disbursements.debug '|| g_req_id,'^^^done inserting into igf_aw_award_t^^^');
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid,
x_process_id => l_process_id,
x_sl_number => l_sl_number,
x_fund_id => l_fund_id,
x_base_id => l_base_id,
x_offered_amt => l_pelldisb.disb_gross_amt,
x_accepted_amt => l_pelldisb.disb_accepted_amt,
x_paid_amt => 0,
x_need_reduction_amt => 100,
x_flag => 'DB',
x_temp_num_val1 => l_pelldisb.disb_gross_amt,
x_temp_num_val2 => NULL,
x_temp_char_val1 => l_pelldisb.disb_date,
x_tp_cal_type => l_pelldisb.tp_cal_type,
x_tp_sequence_number => l_pelldisb.tp_sequence_number,
x_ld_cal_type => l_pelldisb.ld_cal_type,
x_ld_sequence_number => l_pelldisb.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_pell_dp,
x_app_trans_num_txt => NULL,
x_award_id => l_pelldisb.award_id,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => NVL(l_base_attendance.base_attendance_type_code,l_pelldisb.base_attendance_type_code),
x_temp_char3_txt => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.get_disbursements.debug '|| g_req_id,'^^^inserting into igf_aw_award_t for PELL(new)^^^');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packaging.get_disbursements.debug '|| g_req_id,'^^^done inserting into igf_aw_award_t^^^');
igf_aw_award_t_pkg.insert_row(
x_rowid => lv_rowid,
x_process_id => l_process_id,
x_sl_number => l_sl_number,
x_fund_id => l_fund_id,
x_base_id => l_base_id,
x_offered_amt => l_pell_new_disb.offered_amt,
x_accepted_amt => l_pell_new_disb.accepted_amt,
x_paid_amt => 0,
x_need_reduction_amt => 100,
x_flag => 'DB',
x_temp_num_val1 => l_pell_new_disb.offered_amt,
x_temp_num_val2 => l_pell_new_disb.temp_num_val2,
x_temp_char_val1 => l_pell_new_disb.temp_char_val1,
x_tp_cal_type => l_pell_new_disb.tp_cal_type,
x_tp_sequence_number => l_pell_new_disb.tp_sequence_number,
x_ld_cal_type => l_pell_new_disb.ld_cal_type,
x_ld_sequence_number => l_pell_new_disb.ld_sequence_number,
x_mode => 'R',
x_adplans_id => l_pell_new_disb.adplans_id,
x_app_trans_num_txt => NULL,
x_award_id => l_pell_new_disb.award_id,
x_lock_award_flag => NULL,
x_temp_val3_num => NULL,
x_temp_val4_num => NULL,
x_temp_char2_txt => l_pell_new_disb.temp_char2_txt,
x_temp_char3_txt => NULL
);
|| inactive_flag parameter was being passed as NULL in insert_row.changed that to 'N'.
|| (reverse chronological order - newest change first)
*/
-- Get all the To Do Items which needs to be assigned to the student
-- Fetch all To Do Items defined at the fund level and remove all To Do which are already assigned to the student
CURSOR c_fnd_todo(
cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
) IS
SELECT tdmst.todo_number,
tdmst.required_for_application,
tdmst.max_attempt,
tdmst.freq_attempt,
tdmst.system_todo_type_code,
td.row_id,
td.base_id,
td.item_sequence_number,
td.status,
td.status_date,
td.add_date,
td.corsp_date,
td.corsp_count,
td.inactive_flag,
td.freq_attempt td_freq_attempt,
td.max_attempt td_max_attempt,
td.required_for_application td_required_for_application,
td.legacy_record_flag,
td.clprl_id
FROM igf_aw_fund_td_map fndtd,
igf_ap_td_item_mst tdmst,
igf_ap_td_item_inst_v td
WHERE fndtd.fund_id = cp_fund_id
AND tdmst.todo_number = td.item_sequence_number(+)
AND tdmst.todo_number = fndtd.item_sequence_number
AND td.person_id(+) = cp_person_id;
SELECT person_id
FROM igf_ap_fa_base_rec_all
WHERE base_id = cp_base_id;
SELECT relationship_cd
FROM igf_sl_cl_pref_lenders
WHERE person_id = cp_person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND TRUNC(NVL(end_date,SYSDATE));
igf_ap_td_item_inst_pkg.insert_row(
x_rowid => lv_rowid,
x_base_id => p_base_id,
x_item_sequence_number => c_fnd_todo_rec.todo_number,
x_status => 'REQ',
x_status_date => TRUNC(SYSDATE),
x_add_date => TRUNC(SYSDATE),
x_corsp_date => NULL,
x_corsp_count => NULL,
x_inactive_flag => 'N',
x_required_for_application => c_fnd_todo_rec.required_for_application,
x_max_attempt => c_fnd_todo_rec.max_attempt,
x_freq_attempt => c_fnd_todo_rec.freq_attempt,
x_mode => 'R',
x_legacy_record_flag => NULL,
x_clprl_id => NULL
);
igf_ap_td_item_inst_pkg.update_row(
x_rowid => c_fnd_todo_rec.row_id,
x_base_id => c_fnd_todo_rec.base_id,
x_item_sequence_number => c_fnd_todo_rec.todo_number,
x_status => c_fnd_todo_rec.status,
x_status_date => c_fnd_todo_rec.status_date,
x_add_date => c_fnd_todo_rec.add_date,
x_corsp_date => c_fnd_todo_rec.corsp_date,
x_corsp_count => c_fnd_todo_rec.corsp_count,
x_inactive_flag => 'N',
x_required_for_application => c_fnd_todo_rec.td_required_for_application,
x_max_attempt => c_fnd_todo_rec.td_max_attempt,
x_freq_attempt => c_fnd_todo_rec.td_freq_attempt,
x_mode => 'R',
x_legacy_record_flag => c_fnd_todo_rec.legacy_record_flag,
x_clprl_id => c_fnd_todo_rec.clprl_id
);
SELECT fabase.*
FROM igf_ap_fa_base_rec fabase
WHERE fabase.ci_cal_type = p_ci_cal_type
AND fabase.ci_Sequence_number = p_ci_sequence_number
AND fabase.base_id = NVL(p_base_id, fabase.base_id);
SELECT base_id
FROM igf_aw_award_t
WHERE flag = 'ST'
AND process_id = l_process_id
ORDER BY temp_num_val2 DESC;
OPEN cur_sf_persid FOR 'SELECT fabase.base_id, igf_aw_coa_gen.coa_amount(fabase.base_id,:g_aprd) coa_f
FROM igf_ap_fa_base_rec fabase
WHERE fabase.ci_cal_type = :p_ci_cal_type
AND fabase.ci_sequence_number = :p_ci_sequence_number
AND fabase.person_id IN ( '||lv_sql_stmt||' ) ' USING g_awd_prd,g_ci_cal_type,g_ci_sequence,p_persid_grp;
OPEN cur_sf_persid FOR 'SELECT fabase.base_id, igf_aw_coa_gen.coa_amount(fabase.base_id,:g_aprd) coa_f
FROM igf_ap_fa_base_rec fabase
WHERE fabase.ci_cal_type = :p_ci_cal_type
AND fabase.ci_sequence_number = :p_ci_sequence_number
AND fabase.person_id IN ( '||lv_sql_stmt||' ) ' USING g_awd_prd,g_ci_cal_type,g_ci_sequence;
SELECT alternate_code
FROM igs_ca_inst_all
WHERE cal_type = TRIM(SUBSTR(cp_award_year,1,10))
AND sequence_number = TO_NUMBER(SUBSTR(p_award_year,11));
SELECT group_cd group_name
FROM igs_pe_persid_group_all
WHERE group_id = cp_persid_grp;
SELECT award_prd_desc
FROM igf_aw_award_prd
WHERE award_prd_cd = cp_awd_prd_code
AND ci_cal_type = TRIM(SUBSTR(cp_award_year,1,10))
AND ci_sequence_number = TO_NUMBER(SUBSTR(p_award_year,11));
SELECT fund_code
FROM igf_aw_fund_mast_all
WHERE fund_id = cp_fund_id;
SELECT party_number
FROM hz_parties parties,
igf_ap_fa_base_rec_all fabase
WHERE fabase.person_id = parties.party_id
AND fabase.base_id = cp_base_id;
SELECT awd_dist_plan_cd_desc
FROM igf_aw_awd_dist_plans
WHERE adplans_id = cp_adplans_id;
SELECT description
FROM igf_aw_target_grp_all
WHERE group_cd = cp_grp_code
AND cal_type = TRIM(SUBSTR(cp_award_year,1,10))
AND sequence_number = TO_NUMBER(SUBSTR(p_award_year,11));
SELECT row_id row_id
FROM igf_aw_award_t awdt
WHERE awdt.process_id = l_process_id;
SELECT COUNT(*) cnt,
SUM(awt.offered_amt * awt.temp_num_val1/100) offered_amt,
fund_code,
adplans_id
FROM igf_aw_award_t awt,
igf_aw_fund_mast_all fm
WHERE fm.fund_id=awt.fund_id
AND awt.flag = 'FL'
AND awt.process_id = l_process_id
GROUP BY fund_code,
adplans_id;
SELECT fm.auto_pkg,
fm.fund_code,
fcat.fed_fund_code,
fcat.fund_source,
fm.entitlement,
fm.min_award_amt,
fm.max_award_amt
FROM igf_aw_fund_mast fm,
igf_aw_fund_cat fcat
WHERE fm.fund_id = cp_fund_id
AND fm.fund_code = fcat.fund_code;
SELECT fabase.base_id,igf_aw_coa_gen.coa_amount(fabase.base_id,g_awd_prd) coa_f
FROM igf_ap_fa_base_rec_all fabase,
igf_ap_isir_matched_all im
WHERE fabase.ci_cal_type = cp_cal_type
AND fabase.ci_sequence_number = cp_sequence_number
AND fabase.target_group IS NOT NULL
AND im.system_record_type = 'ORIGINAL'
AND im.payment_isir = 'Y'
AND im.base_id = fabase.base_id;
SELECT base_id
FROM igf_aw_award_t
WHERE flag = 'ST'
AND process_id = l_process_id
ORDER BY temp_num_val2 DESC;
SELECT adplans_id
FROM igf_aw_target_grp
WHERE group_cd = cp_group_cd;
igf_aw_award_t_pkg.delete_row(l_temp_rec.row_id );