[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TBH Impact changes done in create_loan_records() and update_loan_rec()
------------------------------------------------------------------------------------------------------------
bvisvana 10-Apr-2006 Build FA161. Bug 5006583
TBH Impact change done in insert_lor_dl_records().
--------------------------------------------------------------------------------------------------------------
museshad 20-Feb-2005 Bug 5031795 - SQL Repository issue.
In create_loan_records(), modified cursor cur_count_fed_code
for better performance.
---------------------------------------------------------------------------------------------------------------
bvisvana 20-Sep-2005 Bug # 4127532 - For ALT Loan, if borrower and Student are equal then
p_default_status is set to s_default_status during the loan creation
---------------------------------------------------------------------------------------------------------------
bvisvana 12-Sep-2005 SBCC Bug # 4575843
Before creating loan record, check whether the award accepted amount is in whole numbers.
--------------------------------------------------------------------------------------------
bvisvana 25-Aug-2005 Bug # 4568942. Grade Level not matching with enrollment data.
(Wrong argument type) g_fund_id is replaced with (correct argument type)
g_adplans_id while calling the Class standing wrapper igf_aw_packng_subfns.get_class_stnd
--------------------------------------------------------------------------------------------
bvisvana 18-Aug-2005 Bug # 4464629 - Removed the global variable gv_dl_version.
This is not used anywhere and this is causing unexpected behaviour.
---------------------------------------------------------------------------------------------------------------
mnade 6/3/2005 FA 157 - Auto Borrower population for PLUS loans, Cosigner Data for ALT Loans.
Student default borrower for ALT Loans.
pssahni 30-Dec-2004 Bug 4087865 Application form code must be populated
--------------------------------------------------------------------------
mnade 29-Dec-2004 Bug 4085937
Call get entity ids Only for Full Participant
sjadhav 25-Oct-2004 Bug 3416863 FA 149 Build Changes
------------------------------------------------------------------------
brajendr 12-Oct-2004 Bug 3732665 ISIR Enhacements
Modified the Payment ISIR reference
ugummall 23-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
In constructing loan_number for Direct Loan, School ID is derived from the
student's associated Org. For this purpose, base_id(as extra parameter) is
passed to this function.
Similarly, OPE ID and School Non Ed Brc ID are also derived for Common Line Loan.
--veramach 16-OCT-2003 FA124 Build remove ISIR requirement for awarding(bug # 3108506)
-- Added code for checking loan limits in insert_loan_records
-- bkkumar 06-oct-2003 Bug 3104228 Impact of adding the relationship_cd
in igf_sl_lor_all table and obsoleting
BORW_LENDER_ID,
DUNS_BORW_LENDER_ID,
GUARANTOR_ID,
DUNS_GUARNT_ID,
LENDER_ID, DUNS_LENDER_ID
LEND_NON_ED_BRC_ID, RECIPIENT_ID
RECIPIENT_TYPE,DUNS_RECIP_ID
RECIP_NON_ED_BRC_ID columns
Also the relationship code is now picked up from the
pick_Setup routine.
-- bkkumar 29-sep-2003 Bug 3104228 . FA 122 Loans Enhancements
In "insert_lor_cl_records" procedure
Derivation of the fields
REQ_SERIAL_LOAN_CODE,
PNOTE_DELIVERY_CODE,
BORW_INTEREST_IND,
BORW_LENDER_ID,
DUNS_BORW_LENDER_ID, -- FIELD OBSOLETED
GUARANTOR_ID,
DUNS_GUARNT_ID, -- FIELD OBSOLETED
PRC_TYPE_CODE,
LENDER_ID,
DUNS_LENDER_ID, -- FIELD OBSOLETED
LEND_NON_ED_BRC_ID,
RECIPIENT_ID,
RECIPIENT_TYPE,
DUNS_RECIP_ID, -- FIELD OBSOLETED
RECIP_NON_ED_BRC_ID,
is now done from the FFELP loan Setup.
Added the common framework logging messages.
-- rasahoo 02-Sep-2003 Replaced igf_ap_fa_base_h.class_standing%TYPE with
-- igs_pr_css_class_std_v.class_standing%TYPE and
-- igf_ap_fa_base_h.enrl_program_type%TYPE with igs_ps_ver_all.course_type%TYPE.
-- gmuralid 3-Juyl-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
-- Added legacy record flag as parameter to
-- igf_sl_loans_pkg
------------------------------------------------------------------------
-- sjadhav 28-Mar-2003 Bug 2863960
-- Corrected message token for
-- IGF_AP_NO_GRADE_LEVEL to PERSON_NUMBER
------------------------------------------------------------------------
-- masehgal 10-Oct-2002 # 2591960 Integration Enhancements
-- Validation on a FRESHMAN ( never attended
-- college or did )
-- Sepearated dl/cl code derivation from
-- return entities
-- Created separate procedure to obtain
-- those
------------------------------------------------------------------------
-- sjadhav Bug 2415013 Default Enrollment Code to Full Time
------------------------------------------------------------------------
-- sjadhav 26-Feb-2002 Bug 2240762
-- Removed references to cur_tp_dates
-- Added two functions to get Loan Start Date
-- and Loan End Date
-- Added a check to see if the SSN is already
-- used in creation of Loan Number
------------------------------------------------------------------------
-- sjadhav 24-jul-2001 Bug ID : 1818617
-- added parameter p_get_recent_info
------------------------------------------------------------------------
-- adhawan 15-feb-2002 Bug Id : 2216956 added columns
-- elec_mpn_ind,
-- borr_sign_in
-- stud_sign_ind
-- borr_credit_auth_code
----------------------------------------------------------------------- */
--
-- FA 134
-- FA 134
award_rec igf_aw_award_v%ROWTYPE;
SELECT
COUNT(v.row_id) parent_count,
MIN(v.object_ID) student_id,
MIN(v.SUBJECT_ID) parent_id
FROM
igs_pe_relationships_v v,
igf_aw_award_all awd,
igf_sl_loans_all loans,
igf_ap_fa_base_rec base
WHERE
base.person_id = v.object_id
AND base.base_id = awd.base_id
AND awd.award_id = loans.award_id
AND loans.loan_id = cp_loan_id
AND
RELATIONSHIP_CODE = 'PARENT_OF'
AND TRUNC(SYSDATE) BETWEEN v.start_date and NVL(v.end_date, SYSDATE);
SELECT
alt.rowid row_id,
alt.*
FROM
igf_sl_alt_borw_all alt
WHERE
alt.loan_id = cp_loan_id;
SELECT
slor.ci_cal_type, slor.ci_sequence_number
FROM igf_sl_lor_v slor
WHERE slor.loan_id = cp_loan_id;
SELECT
pct.restatus_code
FROM igs_pe_eit_restatus_v pct
WHERE pct.person_id = cp_person_id
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
SELECT fa_citi_stat_code FROM igf_sl_pe_citi_map
WHERE pe_citi_stat_code = p_citizenship_status
AND ci_sequence_number =p_sequence_number
AND ci_cal_type = p_cal_type;
SELECT relationship_code FROM
igs_pe_relationships_v v
WHERE v.object_id = cp_student_id AND -- child
v.subject_id = cp_parent_id AND -- parent
TRUNC(SYSDATE) BETWEEN v.start_date and NVL(v.end_date, SYSDATE);
SELECT fcat.fund_code||'-'||fmast.description description
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE fmast.fund_id = p_fund_id
AND fcat.fund_code = fmast.fund_code;
SELECT base_id
FROM igf_ap_fa_base_rec_all
WHERE base_id = p_base_id AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_seq_number;
SELECT group_cd
FROM igs_pe_persid_group_all
WHERE group_id = p_per_grp_id;
SELECT rep.rep_entity_id_txt
FROM igf_gr_attend_pell gap, igf_gr_report_pell rep
WHERE gap.ci_cal_type = p_cal_type
AND gap.ci_sequence_number = p_seq_num
AND gap.atd_entity_id_txt = p_atd_entity_id
AND gap.rcampus_id = rep.rcampus_id;
SELECT ld_cal_type,ld_sequence_number
FROM igf_aw_awd_disb awd
WHERE awd.award_id = p_award_id
AND awd.trans_type <> 'C'
GROUP BY awd.ld_cal_type,awd.ld_sequence_number;
SELECT base_id
FROM igf_aw_award_all
WHERE award_id = cp_award_id;
SELECT ld_cal_type,ld_sequence_number
FROM igf_aw_awd_disb awd
WHERE awd.award_id = p_award_id
AND awd.trans_type <> 'C'
GROUP BY awd.ld_cal_type,awd.ld_sequence_number;
SELECT base_id
FROM igf_aw_award_all
WHERE award_id = cp_award_id;
SELECT rowid
FROM igf_sl_loans_all
WHERE loan_number = p_loan_number;
SELECT igf_sl_cl_loan_seq_s.NEXTVAL into lv_incr_seq FROM DUAL;
SELECT person_id, ci_cal_type, ci_sequence_number
FROM igf_ap_fa_base_rec
WHERE base_id = cp_base_id ;
SELECT CSSV.igs_pr_cs_schdl_id ,
CSSV.igs_pr_class_std_id ,
CSSV.igs_pr_css_class_std_id
FROM igs_pr_css_class_std_v CSSV
WHERE CSSV.igs_pr_cs_schdl_id = (SELECT igs_pr_cs_schdl_id
FROM igs_pr_cs_schdl CS
WHERE CS.course_type = cp_program_type )
AND CSSV.class_standing = cp_class_standing ;
SELECT CSM.dl_std_code,
CSM.cl_std_code
FROM igf_ap_class_std_map CSM ,
igf_ap_pr_prg_type PPT
WHERE PPT.ppt_id = CSM.ppt_id
AND PPT.igs_pr_cs_schdl_id = cp_cs_schdl_id
AND PPT.cal_type = cp_ci_cal_type
AND PPT.sequence_number = cp_ci_sequence_number
AND CSM.igs_pr_css_class_std_id = cp_css_class_std_id ;
SELECT COUNT(institution_code)
FROM igs_ad_acad_history_v acad, igs_or_org_inst_type org
WHERE acad.institution_type = org.institution_type
AND person_id = cp_person_id
AND org.system_inst_type = 'POST-SECONDARY' ;
SELECT ci_cal_type, ci_sequence_number
FROM igf_ap_fa_base_rec
WHERE base_id = cp_baseid;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_sequence_number ;
SELECT isirm.nslds_match_flag, NVL(fabase.nslds_data_override_flg,'N'),isirm.transaction_num,
NVL(fabase.adnl_unsub_loan_elig_flag,'N') adnl_unsub_loan_elig_flag -- FA134
FROM igf_ap_isir_matched_all isirm,
igf_ap_fa_base_rec_all fabase
WHERE fabase.base_id = p_base_id
AND fabase.base_id = isirm.base_id
AND isirm.payment_isir = 'Y'
AND isirm.system_record_type = 'ORIGINAL';
/* Procedure to insert Direct Loan Originations into IGF_SL_LOR */
PROCEDURE insert_lor_dl_records(
p_cal_type IN igs_ca_inst.cal_type%TYPE,
p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
p_loan_id IN igf_sl_loans.loan_id%TYPE
)AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/11/13
Purpose : To Insert Records into IGF_SL_LOR for DL Loans
Known Limitations,Enhancements or Remarks
Change History :
Who When What
bvisvana 10-Apr-2006 Build FA161.
TBH impact change in igf_sl_lor_pkg.insert_row().
pkpatel 12-05-2001 Given default value for the parameter
pnote_status
***************************************************************/
CURSOR getloannumber (cp_loan_id NUMBER) is
SELECT LOAN_NUMBER
FROM IGF_SL_LOANS_ALL lar
WHERE lar.LOAN_ID = cp_loan_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_dl_records.debug',' Acad Begin Date :' || lv_acad_begin_date);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_dl_records.debug',' Acad End Date :' || lv_acad_end_date);
igf_sl_lor_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_origination_id => ln_origination_id,
x_loan_id => p_loan_id,
x_sch_cert_date => TRUNC(award_rec.creation_date),
x_orig_status_flag => NULL,
x_orig_batch_id => NULL,
x_orig_batch_date => NULL,
x_chg_batch_id => NULL,
x_orig_ack_date => NULL,
x_credit_override => NULL,
x_credit_decision_date => NULL,
x_req_serial_loan_code => NULL,
x_act_serial_loan_code => NULL,
x_pnote_delivery_code => NULL,
x_pnote_status => 'N',
x_pnote_status_date => NULL,
x_pnote_id => NULL,
x_pnote_print_ind => dl_setup_rec.pnote_print_ind,
x_pnote_accept_amt => NULL,
x_pnote_accept_date => NULL,
x_unsub_elig_for_heal => 'N',
x_disclosure_print_ind => dl_setup_rec.disclosure_print_ind,
x_orig_fee_perct => lv_orig_fee_perct,
x_borw_confirm_ind => NULL,
x_borw_interest_ind => NULL,
x_borw_outstd_loan_code => NULL,
x_unsub_elig_for_depnt => gv_unsub_elig_for_depnt,
x_guarantee_amt => NULL,
x_guarantee_date => NULL,
x_guarnt_amt_redn_code => NULL,
x_guarnt_status_code => NULL,
x_guarnt_status_date => NULL,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => NULL,
x_lend_status_date => NULL,
x_guarnt_adj_ind => NULL,
x_grade_level_code => g_grade_level_dl,
x_enrollment_code => NULL,
x_anticip_compl_date => NULL,
x_borw_lender_id => NULL,
x_duns_borw_lender_id => NULL,
x_guarantor_id => NULL,
x_duns_guarnt_id => NULL,
x_prc_type_code => NULL,
x_cl_seq_number => NULL,
x_last_resort_lender => NULL,
x_lender_id => NULL,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => NULL,
x_recipient_id => NULL,
x_recipient_type => NULL,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_rec_type_ind => NULL,
x_cl_loan_type => NULL,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => NULL,
x_alt_appl_ver_code => NULL,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => NULL,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_appl_send_error_codes => NULL,
x_tot_outstd_stafford => NULL,
x_tot_outstd_plus => NULL,
x_alt_borw_tot_debt => NULL,
x_act_interest_rate => NULL,
x_service_type_code => NULL,
x_rev_notice_of_guarnt => NULL,
x_sch_refund_amt => NULL,
x_sch_refund_date => NULL,
x_uniq_layout_vend_code => NULL,
x_uniq_layout_ident_code => NULL,
x_p_person_id => l_parent_person_id, -- FA 157 -- derived single parent ID.
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => NULL,
x_p_default_status => 'N',
x_p_signature_code => NULL,
x_p_signature_date => NULL,
x_s_ssn_chg_date => NULL,
x_s_dob_chg_date => NULL,
x_s_permt_addr_chg_date => NULL,
x_s_local_addr_chg_date => NULL,
x_s_default_status => g_s_default_status,
x_s_signature_code => NULL,
x_pnote_batch_id => NULL,
x_pnote_ack_date => NULL,
x_pnote_mpn_ind => NULL,
x_elec_mpn_ind => NULL,
x_borr_sign_ind => NULL,
x_stud_sign_ind => NULL,
x_borr_credit_auth_code => NULL,
x_relationship_cd => NULL, -- FA 122 Added this column
x_interest_rebate_percent_num => NVL(dl_setup_rec.int_rebate,0),
x_cps_trans_num => gn_transaction_num,
x_atd_entity_id_txt => gv_atd_entity_id_txt,
x_rep_entity_id_txt => gv_rep_entity_id_txt,
x_crdt_decision_status => NULL,
x_note_message => NULL,
x_book_loan_amt => NULL,
x_book_loan_amt_date => NULL,
x_pymt_servicer_amt => NULL,
x_pymt_servicer_date => NULL,
x_requested_loan_amt => g_accepted_amt,
x_eft_authorization_code => NULL,
x_external_loan_id_txt => NULL,
x_deferment_request_code => NULL,
x_actual_record_type_code => NULL,
x_reinstatement_amt => NULL,
x_school_use_txt => NULL,
x_lender_use_txt => NULL,
x_guarantor_use_txt => NULL,
x_fls_approved_amt => NULL,
x_flu_approved_amt => NULL,
x_flp_approved_amt => NULL,
x_alt_approved_amt => NULL,
x_loan_app_form_code => NULL,
x_override_grade_level_code => NULL,
x_b_alien_reg_num_txt => NULL,
x_esign_src_typ_cd => NULL,
x_acad_begin_date => lv_acad_begin_date,
x_acad_end_date => lv_acad_end_date);
fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.INSERT_LOR_DL_RECORDS');
END insert_lor_dl_records;
/* Procedure to insert into IGF_SL_LOR for CL Loans*/
PROCEDURE insert_lor_cl_records(
p_cal_type IN igs_ca_inst.cal_type%TYPE,
p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
p_loan_id IN igf_sl_loans.loan_id%TYPE,
p_comp_date IN igf_sl_lor.anticip_compl_date%TYPE,
p_grd_cl IN igf_sl_lor.grade_level_code%TYPE
)AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/11/13
Purpose : To Insert Records into IGF_SL_LOR for CL Loans
Known Limitations,Enhancements or Remarks
Change History :
Who When What
azmohamm 03-AUG-2006 For GPLUSFL, the new loan type would be 'GB'
and the Federal application form Code would be 'G'.
The borrower id is same as the student id.
So while inserting into igf_sl_lor_all table person_id has to be the student's party id.
rajagupt 10-Apr-2006 Bug# 5006583, FA161 CL4.
cps_trans_num is inserted. deferment_request_code
borw_interest_ind, borw_outstd_loan_code, p_default_status, s_default_status, stud_sign_ind
are inserted with null and loan_app_form_code = 'Q' for FLP loans
bvisvana 06-Jul-2005 Bug # 4473160. LOAN_NUMBER token set in the message
pssahni 30-Dec-2004 Application form code must be populated
bkkumar 02-04-04 FACR116 - Added the parameter to the pick_setup routine to check
in teh case of 'ALT' Loans. Added validation to check if the alt_rel_code
IS null or not.
venagara 18-May-2001 Bug# 1769051
For FLS,FLU, default cl_loan_type.
bkkumar 29-sep-2003 FA 122 Loans Enhancements
Derivation of the fields REQ_SERIAL_LOAN_CODE
PNOTE_DELIVERY_CODE
BORW_INTEREST_IND
BORW_LENDER_ID
DUNS_BORW_LENDER_ID -- OBSOLETED THIS FIELD
GUARANTOR_ID
DUNS_GUARNT_ID -- OBSOLETED THIS FIELD
PRC_TYPE_CODE
LENDER_ID
DUNS_LENDER_ID -- OBSOLETED THIS FIELD
LEND_NON_ED_BRC_ID
RECIPIENT_ID
RECIPIENT_TYPE
DUNS_RECIP_ID -- OBSOLETED THIS FIELD
RECIP_NON_ED_BRC_ID is now done from
the FFELP loan Setup.
bkkumar 06-oct-2003 Bug 3104228 Impact of adding the relationship_cd
in igf_sl_lor_all table and obsoleting
BORW_LENDER_ID,
DUNS_BORW_LENDER_ID,
GUARANTOR_ID,
DUNS_GUARNT_ID,
LENDER_ID, DUNS_LENDER_ID
LEND_NON_ED_BRC_ID, RECIPIENT_ID
RECIPIENT_TYPE,DUNS_RECIP_ID
RECIP_NON_ED_BRC_ID columns
Also the relationship code is now picked up from the
pick_Setup routine.
***************************************************************/
ln_origination_id igf_sl_lor.origination_id%TYPE;
SELECT awd.base_id, awd.award_id
FROM igf_sl_loans_all loans,
igf_aw_award_all awd
WHERE loans.loan_id = cp_loan_id
AND loans.award_id = awd.award_id;
SELECT *
FROM igf_sl_cl_setup_all
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number
AND relationship_cd = cp_rel_code
AND NVL(party_id,-1000) = NVL(cp_party_id,-1000);
SELECT COUNT(*)
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id;
SELECT * FROM igf_sl_loans
WHERE loan_id = cp_loanid;
SELECT fa_citi_stat_code FROM igf_sl_pe_citi_map
WHERE pe_citi_stat_code = p_citizenship_status
AND ci_sequence_number =p_sequence_number
AND ci_cal_type = p_cal_type;
SELECT p_person_id FROM igf_sl_lor_all
WHERE loan_id = cp_loanid;
SELECT
pct.restatus_code
FROM igs_pe_eit_restatus_v pct
WHERE pct.person_id = cp_person_id
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','The fed_fund_code being used is :' || award_rec.fed_fund_code );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','The values passed to pick_setup base_id :' || l_get_base_id.base_id );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','The values returned from pick setup RelCode :' || l_rel_code || ' The Party ID :' || l_party_id);
igf_sl_loans_pkg.update_row (
x_mode => 'R',
x_rowid => l_tbh_loans_rec.row_id,
x_loan_id => l_tbh_loans_rec.loan_id,
x_award_id => l_tbh_loans_rec.award_id,
x_seq_num => l_tbh_loans_rec.seq_num,
x_loan_number => l_tbh_loans_rec.loan_number,
x_loan_per_begin_date => l_tbh_loans_rec.loan_per_begin_date,
x_loan_per_end_date => l_tbh_loans_rec.loan_per_end_date,
x_loan_status => l_loan_status,
x_loan_status_date => l_tbh_loans_rec.loan_status_date,
x_loan_chg_status => l_tbh_loans_rec.loan_chg_status,
x_loan_chg_status_date => l_tbh_loans_rec.loan_chg_status_date,
x_active => l_tbh_loans_rec.active,
x_active_date => l_tbh_loans_rec.active_date ,
x_borw_detrm_code => l_tbh_loans_rec.borw_detrm_code,
x_legacy_record_flag => l_tbh_loans_rec.legacy_record_flag,
x_external_loan_id_txt => l_tbh_loans_rec.external_loan_id_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','Borrower citizenship status is ' || citizenship_dtl_rec.restatus_code );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','Borrower Alein reg number is ' || l_cur_fa_rec.fa_citi_stat_code);
igf_sl_lor_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_origination_id => ln_origination_id,
x_loan_id => p_loan_id,
x_sch_cert_date => TRUNC(award_rec.creation_date),
x_orig_status_flag => NULL,
x_orig_batch_id => NULL,
x_orig_batch_date => NULL,
x_chg_batch_id => NULL,
x_orig_ack_date => NULL,
x_credit_override => NULL,
x_credit_decision_date => NULL,
x_req_serial_loan_code => c_clsetup_rec.req_serial_loan_code,
x_act_serial_loan_code => NULL,
x_pnote_delivery_code => c_clsetup_rec.pnote_delivery_code,
x_pnote_status => NULL,
x_pnote_status_date => NULL,
x_pnote_id => NULL,
x_pnote_print_ind => NULL,
x_pnote_accept_amt => NULL,
x_pnote_accept_date => NULL,
x_unsub_elig_for_heal => NULL,
x_disclosure_print_ind => NULL,
x_orig_fee_perct => NULL,
x_borw_confirm_ind => NULL,
x_borw_interest_ind => l_borw_interest_ind,
x_borw_outstd_loan_code => l_borw_outstd_loan_code,
x_unsub_elig_for_depnt => gv_unsub_elig_for_depnt,
x_guarantee_amt => NULL,
x_guarantee_date => NULL,
x_guarnt_amt_redn_code => NULL,
x_guarnt_status_code => NULL,
x_guarnt_status_date => NULL,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => NULL,
x_lend_status_date => NULL,
x_guarnt_adj_ind => NULL,
x_grade_level_code => p_grd_cl,
x_enrollment_code => g_enrollment_code,
x_anticip_compl_date => lv_anticipated_compl_date,
x_borw_lender_id => NULL,
x_duns_borw_lender_id => NULL,
x_guarantor_id => NULL,
x_duns_guarnt_id => NULL,
x_prc_type_code => c_clsetup_rec.prc_type_code,
x_cl_seq_number => NULL,
x_last_resort_lender => NULL,
x_lender_id => NULL,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => NULL,
x_recipient_id => NULL,
x_recipient_type => NULL,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_rec_type_ind => 'A',
x_cl_loan_type => lv_cl_loan_type,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => award_rec.alt_loan_code, -- FACR116
x_alt_appl_ver_code => NULL,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => NULL,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_appl_send_error_codes => NULL,
x_tot_outstd_stafford => NULL,
x_tot_outstd_plus => NULL,
x_alt_borw_tot_debt => NULL,
x_act_interest_rate => NULL,
x_service_type_code => NULL,
x_rev_notice_of_guarnt => NULL,
x_sch_refund_amt => NULL,
x_sch_refund_date => NULL,
x_uniq_layout_vend_code => NULL,
x_uniq_layout_ident_code => NULL,
x_p_person_id => l_parent_person_id, -- FA 157 -- derived single parent ID.
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => NULL,
x_p_default_status => g_p_default_status, -- Bug 4127532 -derived borr default status from student default status
x_p_signature_code => NULL,
x_p_signature_date => NULL,
x_s_ssn_chg_date => NULL,
x_s_dob_chg_date => NULL,
x_s_permt_addr_chg_date => NULL,
x_s_local_addr_chg_date => NULL,
x_s_default_status => g_s_default_status,
x_s_signature_code => NULL,
x_pnote_batch_id => NULL,
x_pnote_ack_date => NULL,
x_pnote_mpn_ind => NULL,
x_elec_mpn_ind => NULL,
x_borr_sign_ind => NULL,
x_stud_sign_ind => NULL,
x_borr_credit_auth_code => NULL,
x_relationship_cd => l_rel_code, -- FA 122 Added this column,
x_interest_rebate_percent_num => NULL,
x_cps_trans_num => gn_transaction_num, -- FA161
x_atd_entity_id_txt => NULL,
x_rep_entity_id_txt => NULL,
x_crdt_decision_status => NULL,
x_note_message => NULL,
x_book_loan_amt => NULL,
x_book_loan_amt_date => NULL,
x_pymt_servicer_amt => NULL,
x_pymt_servicer_date => NULL,
x_requested_loan_amt => g_accepted_amt,
x_eft_authorization_code => c_clsetup_rec.eft_authorization,
x_external_loan_id_txt => NULL,
x_deferment_request_code => NULL,
x_actual_record_type_code => NULL,
x_reinstatement_amt => NULL,
x_school_use_txt => NULL,
x_lender_use_txt => NULL,
x_guarantor_use_txt => NULL,
x_fls_approved_amt => NULL,
x_flu_approved_amt => NULL,
x_flp_approved_amt => NULL,
x_alt_approved_amt => NULL,
x_loan_app_form_code => l_appl_form_code,
x_override_grade_level_code => NULL,
x_b_alien_reg_num_txt => l_b_alien_reg_num_txt,
x_esign_src_typ_cd => NULL,
x_acad_begin_date => NULL,
x_acad_end_date => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','before racalc fees');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','after racalc fees');
fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.INSERT_LOR_CL_RECORDS');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.exception',SQLERRM);
fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.INSERT_LOR_CL_RECORDS');
END insert_lor_cl_records;
PROCEDURE update_loan_rec(p_award_id number,
p_fund_id number,
p_base_id number,
p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
IS
CURSOR cur_loan IS
SELECT loan.*,
awd.adplans_id,
fa.person_id,
fcat.fed_fund_code,
awd.base_id,
awd.award_status,
awd.accepted_amt,
fmast.ci_cal_type,
fmast.ci_sequence_number
FROM igf_sl_loans_all loan,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat,
igf_ap_fa_base_rec_all fa
WHERE loan.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND fmast.fund_code = fcat.fund_code
AND fmast.ci_cal_type = p_ci_cal_type
AND fmast.ci_sequence_number = p_ci_sequence_number
AND awd.base_id = NVL (p_base_id, awd.base_id)
AND awd.fund_id = NVL (p_fund_id, awd.fund_id)
AND awd.award_id = NVL (p_award_id, awd.award_id)
AND fa.base_id = NVL (p_base_id, awd.base_id)
AND fa.ci_cal_type = fmast.ci_cal_type
AND fa.ci_sequence_number = fmast.ci_sequence_number;
SELECT * FROM igf_sl_lor
WHERE loan_id = cp_loanid;
SELECT * FROM igf_sl_loans
WHERE loan_id = cp_loanid;
lb_update Boolean;
SELECT fa_citi_stat_code FROM igf_sl_pe_citi_map
WHERE pe_citi_stat_code = p_citizenship_status
AND ci_sequence_number =p_sequence_number
AND ci_cal_type = p_cal_type;
SELECT p_person_id FROM igf_sl_lor_all
WHERE loan_id = cp_loanid;
SELECT
pct.restatus_code
FROM igs_pe_eit_restatus_v pct
WHERE pct.person_id = cp_person_id
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
SELECT
'X'
FROM
igs_pe_relationships_v v,
igf_aw_award_all awd,
igf_sl_loans_all loans,
igf_ap_fa_base_rec base
WHERE
base.person_id = v.object_ID
AND base.base_id = awd.base_id
AND awd.award_id = loans.award_id
AND loans.loan_id = cp_loan_id
AND
RELATIONSHIP_CODE = 'PARENT_OF'
AND trunc(SYSDATE) BETWEEN v.start_date and NVL(v.end_date, SYSDATE);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'after call to get_fa_base_details');
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_s_default_status= ' || g_s_default_status);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_grade_level_dl= ' || g_grade_level_dl);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_grade_level_cl= ' || g_grade_level_cl);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_enrollment_code= ' || g_enrollment_code);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_anticip_compl_date= ' || g_anticip_compl_date);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'gn_transaction_num= ' || gn_transaction_num);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'gv_unsub_elig_for_depnt= ' || gv_unsub_elig_for_depnt);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'fed fund code is ' ||cur_loan_rec.fed_fund_code);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'inside FFELP');
lb_update := FALSE;
lb_update := FALSE;
lb_update := FALSE;
lb_update := TRUE;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Borrower Id for the fed_fund_code is ' || l_cur_borrower_rec.p_person_id );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Borrower citizenship status is ' || citizenship_dtl_rec.restatus_code );
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Borrower Alein reg number is ' || l_cur_fa_rec.fa_citi_stat_code);
igf_sl_loans_pkg.update_row (
x_mode => 'R',
x_rowid => l_tbh_loans_rec.row_id,
x_loan_id => l_tbh_loans_rec.loan_id,
x_award_id => l_tbh_loans_rec.award_id,
x_seq_num => l_tbh_loans_rec.seq_num,
x_loan_number => l_tbh_loans_rec.loan_number,
x_loan_per_begin_date => l_tbh_loans_rec.loan_per_begin_date,
x_loan_per_end_date => l_tbh_loans_rec.loan_per_end_date,
x_loan_status => l_tbh_loans_rec.loan_status,
x_loan_status_date => l_tbh_loans_rec.loan_status_date,
x_loan_chg_status => l_tbh_loans_rec.loan_chg_status,
x_loan_chg_status_date => l_tbh_loans_rec.loan_chg_status_date,
x_active => l_tbh_loans_rec.active,
x_active_date => l_tbh_loans_rec.active_date ,
x_borw_detrm_code => l_tbh_loans_rec.borw_detrm_code,
x_legacy_record_flag => l_tbh_loans_rec.legacy_record_flag,
x_external_loan_id_txt => l_tbh_loans_rec.external_loan_id_txt,
x_called_from => 'UPDATE_MODE' --Bug# 5006587
);
fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'inside FFELP');
lb_update := FALSE;
lb_update := TRUE;
igf_sl_loans_pkg.update_row (
x_mode => 'R',
x_rowid => l_tbh_loans_rec.row_id,
x_loan_id => l_tbh_loans_rec.loan_id,
x_award_id => l_tbh_loans_rec.award_id,
x_seq_num => l_tbh_loans_rec.seq_num,
x_loan_number => l_tbh_loans_rec.loan_number,
x_loan_per_begin_date => l_tbh_loans_rec.loan_per_begin_date, -- this is getting updated
x_loan_per_end_date => l_tbh_loans_rec.loan_per_end_date, -- this is getting updated
x_loan_status => l_tbh_loans_rec.loan_status,
x_loan_status_date => l_tbh_loans_rec.loan_status_date,
x_loan_chg_status => l_tbh_loans_rec.loan_chg_status, -- this is getting updated
x_loan_chg_status_date => l_tbh_loans_rec.loan_chg_status_date,
x_active => l_tbh_loans_rec.active,
x_active_date => l_tbh_loans_rec.active_date ,
x_borw_detrm_code => l_tbh_loans_rec.borw_detrm_code,
x_legacy_record_flag => l_tbh_loans_rec.legacy_record_flag,
x_external_loan_id_txt => l_tbh_loans_rec.external_loan_id_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_return_status :' || gv_return_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_message :' || gv_message);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_atd_entity_id_txt :' || gv_atd_entity_id_txt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_rep_entity_id_txt :' || gv_rep_entity_id_txt);
END IF; -- to update or not
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_borrower_parent_id l_parent_person_id :' || l_parent_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_borrower_parent_id l_student_person_id :' || l_student_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Parent id :' || l_parent_person_id);
IF lb_update THEN
igf_sl_lor_pkg.update_row(
x_mode => 'R',
x_rowid => l_tbh_rec.row_id,
x_origination_id => l_tbh_rec.origination_id,
x_loan_id => l_tbh_rec.loan_id,
x_sch_cert_date => l_tbh_rec.sch_cert_date,
x_orig_status_flag => l_tbh_rec.orig_status_flag,
x_orig_batch_id => l_tbh_rec.orig_batch_id,
x_orig_batch_date => l_tbh_rec.orig_batch_date,
x_chg_batch_id => l_tbh_rec.chg_batch_id,
x_orig_ack_date => l_tbh_rec.orig_ack_date,
x_credit_override => l_tbh_rec.credit_override,
x_credit_decision_date => l_tbh_rec.credit_decision_date,
x_req_serial_loan_code => l_tbh_rec.req_serial_loan_code,
x_act_serial_loan_code => l_tbh_rec.act_serial_loan_code,
x_pnote_delivery_code => l_tbh_rec.pnote_delivery_code,
x_pnote_status => l_tbh_rec.pnote_status,
x_pnote_status_date => l_tbh_rec.pnote_status_date,
x_pnote_id => l_tbh_rec.pnote_id,
x_pnote_print_ind => l_tbh_rec.pnote_print_ind,
x_pnote_accept_amt => l_tbh_rec.pnote_accept_amt,
x_pnote_accept_date => l_tbh_rec.pnote_accept_date,
x_pnote_batch_id => l_tbh_rec.pnote_batch_id,
x_pnote_ack_date => l_tbh_rec.pnote_ack_date,
x_pnote_mpn_ind => l_tbh_rec.pnote_mpn_ind,
x_unsub_elig_for_heal => l_tbh_rec.unsub_elig_for_heal,
x_disclosure_print_ind => l_tbh_rec.disclosure_print_ind,
x_orig_fee_perct => l_tbh_rec.orig_fee_perct,
x_borw_confirm_ind => l_tbh_rec.borw_confirm_ind,
x_borw_interest_ind => l_tbh_rec.borw_interest_ind,
x_borw_outstd_loan_code => l_tbh_rec.borw_outstd_loan_code,
x_unsub_elig_for_depnt => l_tbh_rec.unsub_elig_for_depnt,
x_guarantee_amt => l_tbh_rec.guarantee_amt,
x_guarantee_date => l_tbh_rec.guarantee_date,
x_guarnt_amt_redn_code => l_tbh_rec.guarnt_amt_redn_code,
x_guarnt_status_code => l_tbh_rec.guarnt_status_code,
x_guarnt_status_date => l_tbh_rec.guarnt_status_date,
x_lend_apprv_denied_code => l_tbh_rec.lend_apprv_denied_code,
x_lend_apprv_denied_date => l_tbh_rec.lend_apprv_denied_date,
x_lend_status_code => l_tbh_rec.lend_status_code,
x_lend_status_date => l_tbh_rec.lend_status_date,
x_guarnt_adj_ind => l_tbh_rec.guarnt_adj_ind,
x_grade_level_code => l_tbh_rec.grade_level_code,
x_enrollment_code => l_tbh_rec.enrollment_code,
x_anticip_compl_date => l_tbh_rec.anticip_compl_date,
x_borw_lender_id => l_tbh_rec.borw_lender_id,
x_duns_borw_lender_id => l_tbh_rec.duns_borw_lender_id,
x_guarantor_id => l_tbh_rec.guarantor_id,
x_duns_guarnt_id => l_tbh_rec.duns_guarnt_id,
x_prc_type_code => l_tbh_rec.prc_type_code,
x_cl_seq_number => l_tbh_rec.cl_seq_number,
x_last_resort_lender => l_tbh_rec.last_resort_lender,
x_lender_id => l_tbh_rec.lender_id,
x_duns_lender_id => l_tbh_rec.duns_lender_id,
x_lend_non_ed_brc_id => l_tbh_rec.lend_non_ed_brc_id,
x_recipient_id => l_tbh_rec.recipient_id,
x_recipient_type => l_tbh_rec.recipient_type,
x_duns_recip_id => l_tbh_rec.duns_recip_id,
x_recip_non_ed_brc_id => l_tbh_rec.recip_non_ed_brc_id,
x_rec_type_ind => l_tbh_rec.rec_type_ind,
x_cl_loan_type => l_tbh_rec.cl_loan_type,
x_cl_rec_status => l_tbh_rec.cl_rec_status,
x_cl_rec_status_last_update => l_tbh_rec.cl_rec_status_last_update,
x_alt_prog_type_code => l_tbh_rec.alt_prog_type_code,
x_alt_appl_ver_code => l_tbh_rec.alt_appl_ver_code,
x_mpn_confirm_code => l_tbh_rec.mpn_confirm_code,
x_resp_to_orig_code => l_tbh_rec.resp_to_orig_code,
x_appl_loan_phase_code => l_tbh_rec.appl_loan_phase_code,
x_appl_loan_phase_code_chg => l_tbh_rec.appl_loan_phase_code_chg,
x_appl_send_error_codes => l_tbh_rec.appl_send_error_codes,
x_tot_outstd_stafford => l_tbh_rec.tot_outstd_stafford,
x_tot_outstd_plus => l_tbh_rec.tot_outstd_plus,
x_alt_borw_tot_debt => l_tbh_rec.alt_borw_tot_debt,
x_act_interest_rate => l_tbh_rec.act_interest_rate,
x_service_type_code => l_tbh_rec.service_type_code,
x_rev_notice_of_guarnt => l_tbh_rec.rev_notice_of_guarnt,
x_sch_refund_amt => l_tbh_rec.sch_refund_amt,
x_sch_refund_date => l_tbh_rec.sch_refund_date,
x_uniq_layout_vend_code => l_tbh_rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => l_tbh_rec.uniq_layout_ident_code,
x_p_person_id => l_tbh_rec.p_person_id,
x_p_ssn_chg_date => l_tbh_rec.p_ssn_chg_date,
x_p_dob_chg_date => l_tbh_rec.p_dob_chg_date,
x_p_permt_addr_chg_date => l_tbh_rec.p_permt_addr_chg_date,
x_p_default_status => l_tbh_rec.p_default_status,
x_p_signature_code => l_tbh_rec.p_signature_code,
x_p_signature_date => l_tbh_rec.p_signature_date,
x_s_ssn_chg_date => l_tbh_rec.s_ssn_chg_date,
x_s_dob_chg_date => l_tbh_rec.s_dob_chg_date ,
x_s_permt_addr_chg_date => l_tbh_rec.s_permt_addr_chg_date,
x_s_local_addr_chg_date => l_tbh_rec.s_local_addr_chg_date,
x_s_default_status => l_tbh_rec.s_default_status,
x_s_signature_code => l_tbh_rec.s_signature_code,
x_elec_mpn_ind => l_tbh_rec.elec_mpn_ind,
x_borr_sign_ind => l_tbh_rec.borr_sign_ind,
x_stud_sign_ind => l_tbh_rec.stud_sign_ind,
x_borr_credit_auth_code => l_tbh_rec.borr_credit_auth_code,
x_relationship_cd => l_tbh_rec.relationship_cd,
x_interest_rebate_percent_num => l_tbh_rec.interest_rebate_percent_num,
x_cps_trans_num => l_tbh_rec.cps_trans_num,
x_atd_entity_id_txt => l_tbh_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => l_tbh_rec.rep_entity_id_txt,
x_crdt_decision_status => l_tbh_rec.crdt_decision_status,
x_note_message => l_tbh_rec.note_message,
x_book_loan_amt => l_tbh_rec.book_loan_amt ,
x_book_loan_amt_date => l_tbh_rec.book_loan_amt_date,
x_pymt_servicer_amt => l_tbh_rec.pymt_servicer_amt,
x_pymt_servicer_date => l_tbh_rec.pymt_servicer_date,
x_requested_loan_amt => l_tbh_rec.requested_loan_amt,
x_eft_authorization_code => l_tbh_rec.eft_authorization_code,
x_external_loan_id_txt => l_tbh_rec.external_loan_id_txt,
x_deferment_request_code => l_tbh_rec.deferment_request_code ,
x_actual_record_type_code => l_tbh_rec.actual_record_type_code,
x_reinstatement_amt => l_tbh_rec.reinstatement_amt,
x_school_use_txt => l_tbh_rec.school_use_txt,
x_lender_use_txt => l_tbh_rec.lender_use_txt,
x_guarantor_use_txt => l_tbh_rec.guarantor_use_txt,
x_fls_approved_amt => l_tbh_rec.fls_approved_amt,
x_flu_approved_amt => l_tbh_rec.flu_approved_amt,
x_flp_approved_amt => l_tbh_rec.flp_approved_amt,
x_alt_approved_amt => l_tbh_rec.alt_approved_amt,
x_loan_app_form_code => l_tbh_rec.loan_app_form_code,
x_override_grade_level_code => l_tbh_rec.override_grade_level_code,
x_called_from => 'UPDATE_MODE', --Bug# 5006587
x_b_alien_reg_num_txt => l_tbh_rec.b_alien_reg_num_txt,
x_esign_src_typ_cd => l_tbh_rec.esign_src_typ_cd,
x_acad_begin_date => l_tbh_rec.acad_begin_date,
x_acad_end_date => l_tbh_rec.acad_end_date);
fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.UPDATE_LOAN_REC');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_lar_creation.update_loan_rec.exception','Exception:'||SQLERRM);
END update_loan_rec;
SELECT awdv.*
FROM igf_aw_award_v awdv
WHERE awdv.sys_fund_type ='LOAN'
AND awdv.ci_cal_type = p_ci_cal_type
AND awdv.ci_sequence_number = p_ci_sequence_number
AND awdv.award_id = NVL(p_award_id, awdv.award_id)
AND awdv.fund_id = NVL(p_fund_id, awdv.fund_id)
AND awdv.base_id = NVL(p_base_id, awdv.base_id) -- fa134
AND awdv.award_status = 'ACCEPTED'
AND awdv.fed_fund_code IN ('DLP','DLS','DLU','FLP','FLS','FLU','ALT','GPLUSFL')
AND awdv.award_id NOT IN ( SELECT loans.award_id
FROM igf_sl_loans_all loans )
ORDER BY awdv.award_id;
SELECT COUNT(*) + 1
FROM
igf_sl_loans_all loan,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat,
igf_ap_fa_base_rec_all fabase
WHERE
loan.award_id = awd.award_id AND
awd.fund_id = fmast.fund_id AND
awd.base_id = fabase.base_id AND
fcat.fund_code = fmast.fund_code AND
fabase.ci_cal_type = award_rec.ci_cal_type AND
fabase.ci_sequence_number = award_rec.ci_sequence_number AND
fabase.person_id = award_rec.student_id AND
fcat.fed_fund_code = award_rec.fed_fund_code;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from check_loan_limits l_aid :' || l_aid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values passed to get_fabase_details Base_id :' || award_rec.base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Default Status :' || g_s_default_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Grade_level_cl :' || g_grade_level_cl);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Grade_level_dl :' || g_grade_level_dl);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Enrollment Code :' || g_enrollment_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details anticip_compl_date :' || g_anticip_compl_date);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details gn_transaction_num :' || gn_transaction_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_return_status :' || gv_return_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_message :' || gv_message);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_atd_entity_id_txt :' || gv_atd_entity_id_txt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_rep_entity_id_txt :' || gv_rep_entity_id_txt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' g_accepted_amt is not a whole number');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' gn_award_id ' || gn_award_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' g_accepted_amt : ' || g_accepted_amt);
igf_sl_loans_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_loan_id => ln_loan_id,
x_award_id => award_rec.award_id,
x_seq_num => ln_seq_number,
x_loan_number => lv_loan_number,
x_loan_per_begin_date => ld_loan_prd_start_dt,
x_loan_per_end_date => ld_loan_prd_end_dt,
x_loan_status => 'G',
x_loan_status_date => TRUNC(SYSDATE),
x_loan_chg_status => NULL,
x_loan_chg_status_date => NULL,
x_active => 'Y',
x_active_date => TRUNC(SYSDATE),
x_borw_detrm_code => NULL,
x_legacy_record_flag => NULL,
x_external_loan_id_txt => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' after insert of loan record ');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' calling insert_lor_dl_records');
insert_lor_dl_records(p_ci_cal_type, p_ci_sequence_number, ln_loan_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' calling insert_lor_cl_records ');
insert_lor_cl_records(p_ci_cal_type, p_ci_sequence_number, ln_loan_id, g_anticip_compl_date, g_grade_level_cl);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' End of create_loan_records ');
SELECT person_number
FROM igs_pe_person_base_v
WHERE
person_id = p_person_id;
SELECT
base_id
FROM
igf_ap_fa_base_rec_all
WHERE
person_id = p_person_id AND
ci_cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number;
PROCEDURE insert_loan_records(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_award_year IN VARCHAR2,
p_run_mode IN VARCHAR2,
p_fund_id IN NUMBER,
p_dummy_1 IN NUMBER,
p_base_id IN NUMBER,
p_dummy_2 IN NUMBER,
p_award_id IN NUMBER,
p_dummy_3 IN NUMBER,
p_dyn_pid_grp IN NUMBER
)
AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/11/13
Purpose : To arrive at the default values for the various columns in
IGF_SL_LOANS
Known Limitations,Enhancements or Remarks
Change History :
Bug 2367953 Modify Parameters in Loans Process
Who When What
ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
bkkumar 04-04-04 FACR116 - Added a savepoint so that if the insert_lor procedures raises an
exception then we have to rollback and display the appropriate message
bkkumar 14-Jan-04 Bug# 3360702
Passed the l_aid paramter as 0 to the check_loan_limits and also displayed the
error message correctly.
appropriate message.
veramach 16-OCT-2003 Bug # 3108506 Added code to check loan limits before inserting loan records
rasahoo 27-Aug-2003 Removed the call IGF_AP_OSS_PROCESS.GET_OSS_DETAILS,
Changed the signature of procedure INSERT_LOAN_RECORDS,
Removed the parameter P_GET_RECENT_INFO,
as part of obsoletion of FA base record history
mesriniv 20-may-2002 1.Added check for Grade Level Code in case of Direct Loans
2.Added NVL for sch non ed brc id when arriving at loan number for CL Loans.
3.Added call to remove any special characters from SSN while concatenating SSN
in Loan Number
4.Added new message for Grade Level Check for DL
Bug 23241893 LAR Process does not populate loan amount correctly
Who When What
mesriniv 19-apr-2002 Added OFFERED Status in NOT IN condition of cursor cur_awards_rec.
Bug No: 2324159
Desc : LOAN APPLICATION PROCESS - LOG FILE DOES NOT DISPLAY ANY INFO
Who When What
mesriniv 18-apr-2002 1.Added cursor cur_check_fund_award
2.Initialized a variable l_found_loans to check and dsiplay
info on loan records created or not.
3.Added code to print list of person numbers having got the loan and details
in Output File
Bug No : 1978873
Who When What
agairola 15-Mar-2002 Modified the call for the IGF_SL_LOANS_PKG.Insert_Row
for Borrower Determination Code as part of Refunds DLD
Enhancement No: 2144600
ENH Bug No : 1806850 Bug Desc: Awards Build for Nov 2001 Rel
Who When What
mesriniv 6-Jul-2001 W.r.to Awards Build Filtered
Awards with award status
as Declined,Cancelled or Simulated
from creating Loans
***************************************************************/
p_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
SELECT *
FROM igf_sl_dl_setup_all
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_sequence_number;
SELECT *
FROM igf_sl_cl_setup_all
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_sequence_number;
SELECT ca.alternate_code,start_dt,end_dt
FROM igs_ca_inst ca
WHERE ca.cal_type =p_ci_cal_type
AND ca.sequence_number = p_ci_sequence_number;
SELECT awd.award_id,fmast.fund_code
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_ap_fa_base_rec_all base,
igf_aw_fund_cat_all fcat
WHERE awd.fund_id = fmast.fund_id
AND awd.base_id = base.base_id
AND awd.award_status = 'ACCEPTED'
AND fmast.fund_code = fcat.fund_code
AND fcat.fed_fund_code IN ('DLP','DLS','DLU','FLP','FLS','FLU','ALT','GPLUSFL')
AND awd.award_id = NVL(p_award_id,awd.award_id)
AND awd.base_id = NVL(p_base_id,awd.base_id)
AND awd.fund_id = NVL(p_fund_id,awd.fund_id)
AND fmast.ci_cal_type = p_cal_type
AND fmast.ci_sequence_number = p_seq_number;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_GE_PARAMETERS'
AND lookup_code IN ('AWARD_ID',
'AWARD_YEAR',
'FUND_CODE',
'GET_LATEST_OSS',
'PARAMETER_PASS',
'PERSON_ID_GROUP',
'PERSON_NUMBER',
'PROCESSING',
'RUN_MODE')
ORDER BY lookup_code;
SELECT group_id
FROM igs_pe_persid_group_all
WHERE
group_id = p_dyn_pid_grp AND
closed_ind = 'N';
SELECT TO_CHAR(SYSDATE,'DDDYY') INTO lv_incr_date FROM DUAL;
OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_dyn_pid_grp;
OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
END IF ; -- RUN MODE = INSERT
update_loan_rec(p_award_id, p_fund_id, p_base_id,p_ci_cal_type,p_ci_sequence_number);
update_loan_rec(p_award_id, p_fund_id, p_base_id,p_ci_cal_type,p_ci_sequence_number);
OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_dyn_pid_grp;
OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
update_loan_rec(p_award_id, p_fund_id, ln_base_id,p_ci_cal_type, p_ci_sequence_number);
END IF ; -- RUN MODE = UPDATE
END insert_loan_records;