[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
It will also insert the document in the igf_sl_cod_doc_dtls table.
set_nls_fmt()
This process is called from the IGF_SL_INBOUND.xgm for setting the NLS Format
get_datetime()
This process is called from the IGF_SL_INBOUND.xgm for converting the datetime
fields into Gateway compatible.
get_date()
This process is called from the IGF_SL_INBOUND.xgm for converting the date field
into XML Gateway compatible.
launch_request()
This process is called from the workflow and is teh last step of the workflow. It will
launch the sub-process which will upload launch a concurrent request which will process
the response records uploaded by XML Gateway in the previous step.
main_response()
This process is the sub-process (also a concurrent program) which will take the records in the
response tables and updates teh system tables accordingly. It takes the document ID as a
parameter. It will internally call process_pell_records and process_dl_records which process
the Pell and DL Records respectively.
-----------------------------------------------------------*/
CURSOR chk_doc ( cp_doc_id VARCHAR2) IS
SELECT ROWID row_id, a.*
FROM IGF_SL_COD_DOC_DTLS a
WHERE document_id_txt = cp_doc_id;
PROCEDURE update_xml_document(l_chk_doc chk_doc%ROWTYPE, p_doc_status VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
igf_sl_cod_doc_dtls_pkg.update_row (
x_rowid => l_chk_doc.row_id,
x_document_id_txt => l_chk_doc.document_id_txt,
x_outbound_doc => l_chk_doc.outbound_doc ,
x_inbound_doc => l_chk_doc.inbound_doc ,
x_send_date => l_chk_doc.send_date ,
x_ack_date => l_chk_doc.ack_date ,
x_doc_status => p_doc_status ,
x_doc_type => l_chk_doc.doc_type ,
x_full_resp_code => l_chk_doc.full_resp_code ,
x_mode => 'R'
);
END update_xml_document;
PROCEDURE delete_temp_table_data
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE IGF_SL_COD_TEMP;
END delete_temp_table_data;
SELECT *
FROM IGF_SL_COD_TEMP;
DELETE IGF_SL_CR_RESP_DTLS WHERE DOCUMENT_ID_TXT = l_temp.REC_ID;
DELETE IGF_SL_RS_RESP_DTLS WHERE REP_SCHL_RESP_ID = l_temp.REC_ID;
DELETE IGF_SL_AS_RESP_DTLS WHERE ATD_SCHL_RESP_ID = l_temp.REC_ID;
DELETE IGF_SL_ST_RESP_DTLS WHERE STDNT_RESP_ID = l_temp.REC_ID;
DELETE IGF_SL_DL_RESP_DTLS WHERE DL_LOAN_RESP_ID = l_temp.REC_ID;
DELETE IGF_SL_DLDB_RSP_DTL WHERE DISB_RESP_ID = l_temp.REC_ID;
DELETE IGF_GR_RESP_DTLS WHERE PELL_RESP_ID = l_temp.REC_ID;
DELETE IGF_GR_DB_RESP_DTLS WHERE DISB_RESP_ID = l_temp.REC_ID;
DELETE IGF_SL_DI_RESP_DTLS WHERE DL_INFO_ID = l_temp.REC_ID;
DELETE IGF_SL_COD_TEMP;
SELECT REP.ORG_ALTERNATE_ID ENTITY_ID
FROM IGS_OR_ORG_ALT_IDS REP,
IGS_OR_ORG_ALT_IDTYP_V REPID
WHERE REP.ORG_ALTERNATE_ID_TYPE = REPID.ORG_ALTERNATE_ID_TYPE
AND REPID.SYSTEM_ID_TYPE = 'ENTITY_ID'
AND SYSDATE BETWEEN REP.START_DATE AND NVL(REP.END_DATE, SYSDATE)
AND REP.ORG_ALTERNATE_ID = cp_entity_id;
SELECT ROWID ROW_ID, docdtls.*
FROM IGF_SL_COD_DOC_DTLS docdtls
WHERE DOCUMENT_ID_TXT = cp_doc_id;
SELECT IGF_SL_LOAD_XML_S.NEXTVAL
FROM DUAL;
SELECT INBOUND_DOC INTO xmldoc FROM igf_sl_cod_doc_dtls WHERE DOCUMENT_ID_TXT = l_doc_id;
SELECT INBOUND_DOC INTO newxmldoc FROM igf_sl_cod_doc_dtls WHERE DOCUMENT_ID_TXT = l_doc_id FOR UPDATE;
SELECT *
FROM IGF_SL_REJ_EDIT_V
WHERE EDIT_ID = cp_id
AND LEVEL_CODE = cp_level;
SELECT *
FROM IGF_SL_DL_RESP_DTLS
WHERE STDNT_RESP_ID = cp_st_id
AND NVL(STATUS_CODE, '*') <> 'P';
SELECT *
FROM IGF_SL_DLDB_RSP_DTL
WHERE DL_LOAN_RESP_ID = cp_dl_id
AND NVL(STATUS_CODE, '*') <> 'P';
SELECT *
FROM IGF_SL_LOR_LOC_ALL
WHERE LOAN_NUMBER = cp_dl_num
AND FIN_AWARD_YEAR = cp_fin_awd_yr;
SELECT *
FROM IGF_AW_DB_CHG_DTLS
WHERE AWARD_ID = cp_awd_id
AND DISB_NUM = cp_disb_num
AND DISB_SEQ_NUM = cp_disb_seq_num;
SELECT igf_sl_lor.*
FROM IGF_SL_LOR
WHERE loan_id = cp_loan_id
FOR UPDATE NOWAIT;
SELECT igf_sl_lor_loc.*
FROM IGF_SL_LOR_LOC
WHERE loan_id = cp_loan_id
FOR UPDATE NOWAIT;
SELECT igf_sl_loans.*
FROM IGF_SL_LOANS
WHERE loan_id = cp_loan_id
FOR UPDATE NOWAIT;
SELECT *
FROM IGF_AW_DB_CHG_DTLS_V
WHERE AWARD_ID = cp_awd_id
AND DISB_NUM = cp_disb_num
AND DISB_SEQ_NUM = cp_disb_seq_num;
SELECT *
FROM IGF_AW_DB_COD_DTLS_V
WHERE AWARD_ID = cp_awd_id
AND DISB_NUM = cp_disb_num
AND DISB_SEQ_NUM = cp_disb_seq_num;
update_flag BOOLEAN;
UPDATE IGF_SL_DL_RESP_DTLS
SET STATUS_CODE = 'N'
WHERE DL_LOAN_RESP_ID = l_dl_resp.DL_LOAN_RESP_ID;
igf_sl_lor_pkg.update_row (
X_Mode => 'R',
x_rowid => l_tbh_cur.row_id,
x_origination_id => l_tbh_cur.origination_id,
x_loan_id => l_tbh_cur.loan_id,
x_sch_cert_date => l_tbh_cur.sch_cert_date,
x_orig_status_flag => l_tbh_cur.orig_status_flag,
x_orig_batch_id => l_tbh_cur.orig_batch_id,
x_orig_batch_date => l_tbh_cur.orig_batch_date,
x_chg_batch_id => l_tbh_cur.chg_batch_id,
x_orig_ack_date => l_tbh_cur.orig_ack_date,
x_credit_override => l_tbh_cur.credit_override,
x_credit_decision_date => l_tbh_cur.credit_decision_date,
x_req_serial_loan_code => l_tbh_cur.req_serial_loan_code,
x_act_serial_loan_code => l_tbh_cur.act_serial_loan_code,
x_pnote_delivery_code => l_tbh_cur.pnote_delivery_code,
x_pnote_status => l_tbh_cur.pnote_status,
x_pnote_status_date => l_tbh_cur.pnote_status_date,
x_pnote_id => l_tbh_cur.pnote_id,
x_pnote_print_ind => l_tbh_cur.pnote_print_ind,
x_pnote_accept_amt => l_tbh_cur.pnote_accept_amt,
x_pnote_accept_date => l_tbh_cur.pnote_accept_date,
x_unsub_elig_for_heal => l_tbh_cur.unsub_elig_for_heal,
x_disclosure_print_ind => l_tbh_cur.disclosure_print_ind,
x_orig_fee_perct => l_tbh_cur.orig_fee_perct,
x_borw_confirm_ind => l_tbh_cur.borw_confirm_ind,
x_borw_interest_ind => l_tbh_cur.borw_interest_ind,
x_borw_outstd_loan_code => l_tbh_cur.borw_outstd_loan_code,
x_unsub_elig_for_depnt => l_tbh_cur.unsub_elig_for_depnt,
x_guarantee_amt => l_tbh_cur.guarantee_amt,
x_guarantee_date => l_tbh_cur.guarantee_date,
x_guarnt_amt_redn_code => l_tbh_cur.guarnt_amt_redn_code,
x_guarnt_status_code => l_tbh_cur.guarnt_status_code,
x_guarnt_status_date => l_tbh_cur.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => l_tbh_cur.lend_status_code,
x_lend_status_date => l_tbh_cur.lend_status_date,
x_guarnt_adj_ind => l_tbh_cur.guarnt_adj_ind,
x_grade_level_code => l_tbh_cur.grade_level_code,
x_enrollment_code => l_tbh_cur.enrollment_code,
x_anticip_compl_date => l_tbh_cur.anticip_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 => l_tbh_cur.prc_type_code,
x_cl_seq_number => l_tbh_cur.cl_seq_number,
x_last_resort_lender => l_tbh_cur.last_resort_lender,
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 => l_tbh_cur.rec_type_ind,
x_cl_loan_type => l_tbh_cur.cl_loan_type,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => l_tbh_cur.alt_prog_type_code,
x_alt_appl_ver_code => l_tbh_cur.alt_appl_ver_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => l_tbh_cur.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_appl_send_error_codes => NULL,
x_tot_outstd_stafford => l_tbh_cur.tot_outstd_stafford,
x_tot_outstd_plus => l_tbh_cur.tot_outstd_plus,
x_alt_borw_tot_debt => l_tbh_cur.alt_borw_tot_debt,
x_act_interest_rate => l_tbh_cur.act_interest_rate,
x_service_type_code => l_tbh_cur.service_type_code,
x_rev_notice_of_guarnt => l_tbh_cur.rev_notice_of_guarnt,
x_sch_refund_amt => l_tbh_cur.sch_refund_amt,
x_sch_refund_date => l_tbh_cur.sch_refund_date,
x_uniq_layout_vend_code => l_tbh_cur.uniq_layout_vend_code,
x_uniq_layout_ident_code => l_tbh_cur.uniq_layout_ident_code,
x_p_person_id => l_tbh_cur.p_person_id,
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => NULL,
x_p_default_status => l_tbh_cur.p_default_status,
x_p_signature_code => l_tbh_cur.p_signature_code,
x_p_signature_date => l_tbh_cur.p_signature_date,
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 => l_tbh_cur.s_default_status,
x_s_signature_code => l_tbh_cur.s_signature_code,
x_pnote_batch_id => l_tbh_cur.pnote_batch_id,
x_pnote_ack_date => l_tbh_cur.pnote_ack_date,
x_pnote_mpn_ind => l_tbh_cur.pnote_mpn_ind ,
x_elec_mpn_ind => l_tbh_cur.elec_mpn_ind ,
x_borr_sign_ind => l_tbh_cur.borr_sign_ind ,
x_stud_sign_ind => l_tbh_cur.stud_sign_ind ,
x_borr_credit_auth_code => l_tbh_cur.borr_credit_auth_code ,
x_relationship_cd => l_tbh_cur.relationship_cd,
x_interest_rebate_percent_num => l_tbh_cur.interest_rebate_percent_num,
x_cps_trans_num => l_tbh_cur.cps_trans_num ,
x_atd_entity_id_txt => l_tbh_cur.atd_entity_id_txt,
x_rep_entity_id_txt => l_tbh_cur.rep_entity_id_txt,
x_crdt_decision_status => l_tbh_cur.crdt_decision_status,
x_note_message => l_tbh_cur.note_message ,
x_book_loan_amt => l_tbh_cur.book_loan_amt ,
x_book_loan_amt_date => l_tbh_cur.book_loan_amt_date,
x_actual_record_type_code => l_tbh_cur.actual_record_type_code,
x_alt_approved_amt => l_tbh_cur.alt_approved_amt,
x_deferment_request_code => l_tbh_cur.deferment_request_code,
x_eft_authorization_code => l_tbh_cur.eft_authorization_code,
x_external_loan_id_txt => l_tbh_cur.external_loan_id_txt,
x_flp_approved_amt => l_tbh_cur.flp_approved_amt,
x_fls_approved_amt => l_tbh_cur.fls_approved_amt,
x_flu_approved_amt => l_tbh_cur.flu_approved_amt,
x_guarantor_use_txt => l_tbh_cur.guarantor_use_txt,
x_lender_use_txt => l_tbh_cur.lender_use_txt,
x_loan_app_form_code => l_tbh_cur.loan_app_form_code,
x_override_grade_level_code => l_tbh_cur.override_grade_level_code,
x_pymt_servicer_amt => l_tbh_cur.pymt_servicer_amt,
x_pymt_servicer_date => l_tbh_cur.pymt_servicer_date,
x_reinstatement_amt => l_tbh_cur.reinstatement_amt,
x_requested_loan_amt => l_tbh_cur.requested_loan_amt,
x_school_use_txt => l_tbh_cur.school_use_txt,
x_b_alien_reg_num_txt => l_tbh_cur.b_alien_reg_num_txt,
x_esign_src_typ_cd => l_tbh_cur.esign_src_typ_cd,
x_acad_begin_date => l_tbh_cur.acad_begin_date,
x_acad_end_date => l_tbh_cur.acad_end_date);
igf_sl_lor_loc_pkg.update_row (
x_mode => 'R',
x_rowid => l_tbh_cur1.row_id,
x_loan_id => l_tbh_cur1.loan_id,
x_origination_id => l_tbh_cur1.origination_id,
x_loan_number => l_tbh_cur1.loan_number,
x_loan_type => l_tbh_cur1.loan_type,
x_loan_amt_offered => l_tbh_cur1.loan_amt_offered,
x_loan_amt_accepted => l_tbh_cur1.loan_amt_accepted,
x_loan_per_begin_date => l_tbh_cur1.loan_per_begin_date,
x_loan_per_end_date => l_tbh_cur1.loan_per_end_date,
x_acad_yr_begin_date => l_tbh_cur1.acad_yr_begin_date,
x_acad_yr_end_date => l_tbh_cur1.acad_yr_end_date,
x_loan_status => l_tbh_cur1.loan_status,
x_loan_status_date => l_tbh_cur1.loan_status_date,
x_loan_chg_status => l_tbh_cur1.loan_chg_status,
x_loan_chg_status_date => l_tbh_cur1.loan_chg_status_date,
x_req_serial_loan_code => l_tbh_cur1.req_serial_loan_code,
x_act_serial_loan_code => l_tbh_cur1.act_serial_loan_code,
x_active => l_tbh_cur1.active,
x_active_date => l_tbh_cur1.active_date,
x_sch_cert_date => l_tbh_cur1.sch_cert_date,
x_orig_status_flag => l_tbh_cur1.orig_status_flag,
x_orig_batch_id => l_tbh_cur1.orig_batch_id,
x_orig_batch_date => l_tbh_cur1.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => l_tbh_cur1.orig_ack_date,
x_credit_override => l_tbh_cur1.credit_override,
x_credit_decision_date => l_tbh_cur1.credit_decision_date,
x_pnote_delivery_code => l_tbh_cur1.pnote_delivery_code,
x_pnote_status => l_tbh_cur1.pnote_status,
x_pnote_status_date => l_tbh_cur1.pnote_status_date,
x_pnote_id => l_tbh_cur1.pnote_id,
x_pnote_print_ind => l_tbh_cur1.pnote_print_ind,
x_pnote_accept_amt => l_tbh_cur1.pnote_accept_amt,
x_pnote_accept_date => l_tbh_cur1.pnote_accept_date,
x_p_signature_code => l_tbh_cur1.p_signature_code,
x_p_signature_date => l_tbh_cur1.p_signature_date,
x_s_signature_code => l_tbh_cur1.s_signature_code,
x_unsub_elig_for_heal => l_tbh_cur1.unsub_elig_for_heal,
x_disclosure_print_ind => l_tbh_cur1.disclosure_print_ind,
x_orig_fee_perct => l_tbh_cur1.orig_fee_perct,
x_borw_confirm_ind => l_tbh_cur1.borw_confirm_ind,
x_borw_interest_ind => l_tbh_cur1.borw_interest_ind,
x_unsub_elig_for_depnt => l_tbh_cur1.unsub_elig_for_depnt,
x_guarantee_amt => l_tbh_cur1.guarantee_amt,
x_guarantee_date => l_tbh_cur1.guarantee_date,
x_guarnt_adj_ind => l_tbh_cur1.guarnt_adj_ind,
x_guarnt_amt_redn_code => l_tbh_cur1.guarnt_amt_redn_code,
x_guarnt_status_code => l_tbh_cur1.guarnt_status_code,
x_guarnt_status_date => l_tbh_cur1.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => l_tbh_cur1.lend_status_code,
x_lend_status_date => l_tbh_cur1.lend_status_date,
x_grade_level_code => l_tbh_cur1.grade_level_code,
x_enrollment_code => l_tbh_cur1.enrollment_code,
x_anticip_compl_date => l_tbh_cur1.anticip_compl_date,
x_borw_lender_id => l_tbh_cur1.borw_lender_id,
x_duns_borw_lender_id => NULL,
x_guarantor_id => l_tbh_cur1.guarantor_id,
x_duns_guarnt_id => NULL,
x_prc_type_code => l_tbh_cur1.prc_type_code,
x_rec_type_ind => l_tbh_cur1.rec_type_ind,
x_cl_loan_type => l_tbh_cur1.cl_loan_type,
x_cl_seq_number => l_tbh_cur1.cl_seq_number,
x_last_resort_lender => l_tbh_cur1.last_resort_lender,
x_lender_id => l_tbh_cur1.lender_id,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => l_tbh_cur1.lend_non_ed_brc_id,
x_recipient_id => l_tbh_cur1.recipient_id,
x_recipient_type => l_tbh_cur1.recipient_type,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => l_tbh_cur1.recip_non_ed_brc_id,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => l_tbh_cur1.alt_prog_type_code,
x_alt_appl_ver_code => l_tbh_cur1.alt_appl_ver_code,
x_borw_outstd_loan_code => l_tbh_cur1.borw_outstd_loan_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => l_tbh_cur1.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_tot_outstd_stafford => l_tbh_cur1.tot_outstd_stafford,
x_tot_outstd_plus => l_tbh_cur1.tot_outstd_plus,
x_alt_borw_tot_debt => l_tbh_cur1.alt_borw_tot_debt,
x_act_interest_rate => l_tbh_cur1.act_interest_rate,
x_service_type_code => l_tbh_cur1.service_type_code,
x_rev_notice_of_guarnt => l_tbh_cur1.rev_notice_of_guarnt,
x_sch_refund_amt => l_tbh_cur1.sch_refund_amt,
x_sch_refund_date => l_tbh_cur1.sch_refund_date,
x_uniq_layout_vend_code => l_tbh_cur1.uniq_layout_vend_code,
x_uniq_layout_ident_code => l_tbh_cur1.uniq_layout_ident_code,
x_p_person_id => l_tbh_cur1.p_person_id,
x_p_ssn => l_tbh_cur1.p_ssn,
x_p_ssn_chg_date => NULL,
x_p_last_name => l_tbh_cur1.p_last_name,
x_p_first_name => l_tbh_cur1.p_first_name,
x_p_middle_name => l_tbh_cur1.p_middle_name,
x_p_permt_addr1 => l_tbh_cur1.p_permt_addr1,
x_p_permt_addr2 => l_tbh_cur1.p_permt_addr2,
x_p_permt_city => l_tbh_cur1.p_permt_city,
x_p_permt_state => l_tbh_cur1.p_permt_state,
x_p_permt_zip => l_tbh_cur1.p_permt_zip,
x_p_permt_addr_chg_date => l_tbh_cur1.p_permt_addr_chg_date,
x_p_permt_phone => l_tbh_cur1.p_permt_phone,
x_p_email_addr => l_tbh_cur1.p_email_addr,
x_p_date_of_birth => l_tbh_cur1.p_date_of_birth,
x_p_dob_chg_date => NULL,
x_p_license_num => l_tbh_cur1.p_license_num,
x_p_license_state => l_tbh_cur1.p_license_state,
x_p_citizenship_status => l_tbh_cur1.p_citizenship_status,
x_p_alien_reg_num => l_tbh_cur1.p_alien_reg_num,
x_p_default_status => l_tbh_cur1.p_default_status,
x_p_foreign_postal_code => l_tbh_cur1.p_foreign_postal_code,
x_p_state_of_legal_res => l_tbh_cur1.p_state_of_legal_res,
x_p_legal_res_date => l_tbh_cur1.p_legal_res_date,
x_s_ssn => l_tbh_cur1.s_ssn,
x_s_ssn_chg_date => NULL,
x_s_last_name => l_tbh_cur1.s_last_name,
x_s_first_name => l_tbh_cur1.s_first_name,
x_s_middle_name => l_tbh_cur1.s_middle_name,
x_s_permt_addr1 => l_tbh_cur1.s_permt_addr1,
x_s_permt_addr2 => l_tbh_cur1.s_permt_addr2,
x_s_permt_city => l_tbh_cur1.s_permt_city,
x_s_permt_state => l_tbh_cur1.s_permt_state,
x_s_permt_zip => l_tbh_cur1.s_permt_zip,
x_s_permt_addr_chg_date => l_tbh_cur1.s_permt_addr_chg_date,
x_s_permt_phone => l_tbh_cur1.s_permt_phone,
x_s_local_addr1 => l_tbh_cur1.s_local_addr1,
x_s_local_addr2 => l_tbh_cur1.s_local_addr2,
x_s_local_city => l_tbh_cur1.s_local_city,
x_s_local_state => l_tbh_cur1.s_local_state,
x_s_local_zip => l_tbh_cur1.s_local_zip,
x_s_local_addr_chg_date => NULL,
x_s_email_addr => l_tbh_cur1.s_email_addr,
x_s_date_of_birth => l_tbh_cur1.s_date_of_birth,
x_s_dob_chg_date => NULL,
x_s_license_num => l_tbh_cur1.s_license_num,
x_s_license_state => l_tbh_cur1.s_license_state,
x_s_depncy_status => l_tbh_cur1.s_depncy_status,
x_s_default_status => l_tbh_cur1.s_default_status,
x_s_citizenship_status => l_tbh_cur1.s_citizenship_status,
x_s_alien_reg_num => l_tbh_cur1.s_alien_reg_num,
x_s_foreign_postal_code => l_tbh_cur1.s_foreign_postal_code,
x_pnote_batch_id => l_tbh_cur1.pnote_batch_id,
x_pnote_ack_date => l_tbh_cur1.pnote_ack_date,
x_pnote_mpn_ind => l_tbh_cur1.pnote_mpn_ind,
x_award_id => l_tbh_cur1.award_id ,
x_base_id => l_tbh_cur1.base_id ,
x_document_id_txt => l_tbh_cur1.document_id_txt ,
x_loan_key_num => l_tbh_cur1.loan_key_num ,
x_INTEREST_REBATE_PERCENT_NUM => l_tbh_cur1.INTEREST_REBATE_PERCENT_NUM,
x_fin_award_year => l_tbh_cur1.fin_award_year ,
x_cps_trans_num => l_tbh_cur1.cps_trans_num ,
x_ATD_ENTITY_ID_TXT => l_tbh_cur1.ATD_ENTITY_ID_TXT,
x_REP_ENTITY_ID_TXT => l_tbh_cur1.REP_ENTITY_ID_TXT,
x_SOURCE_ENTITY_ID_TXT => l_tbh_cur1.SOURCE_ENTITY_ID_TXT,
x_pymt_servicer_amt => l_tbh_cur1.pymt_servicer_amt ,
x_pymt_servicer_date => l_tbh_cur1.pymt_servicer_date ,
x_book_loan_amt => l_tbh_cur1.book_loan_amt ,
x_book_loan_amt_date => l_tbh_cur1.book_loan_amt_date ,
x_s_chg_birth_date => l_tbh_cur1.s_chg_birth_date ,
x_s_chg_ssn => l_tbh_cur1.s_chg_ssn ,
x_s_chg_last_name => l_tbh_cur1.s_chg_last_name ,
x_b_chg_birth_date => l_tbh_cur1.b_chg_birth_date ,
x_b_chg_ssn => l_tbh_cur1.b_chg_ssn ,
x_b_chg_last_name => l_tbh_cur1.b_chg_last_name ,
x_note_message => l_tbh_cur1.note_message ,
x_full_resp_code => l_tbh_cur1.full_resp_code ,
x_s_permt_county => l_tbh_cur1.s_permt_county ,
x_b_permt_county => l_tbh_cur1.b_permt_county ,
x_s_permt_country => l_tbh_cur1.s_permt_country ,
x_b_permt_country => l_tbh_cur1.b_permt_country ,
x_crdt_decision_status => l_tbh_cur1.crdt_decision_status,
x_actual_record_type_code => l_tbh_cur1.actual_record_type_code,
x_alt_approved_amt => l_tbh_cur1.alt_approved_amt,
x_alt_borrower_ind_flag => l_tbh_cur1.alt_borrower_ind_flag,
x_borower_credit_authoriz_flag => l_tbh_cur1.borower_credit_authoriz_flag,
x_borower_electronic_sign_flag => l_tbh_cur1.borower_electronic_sign_flag,
x_cost_of_attendance_amt => l_tbh_cur1.cost_of_attendance_amt,
x_deferment_request_code => l_tbh_cur1.deferment_request_code,
x_eft_authorization_code => l_tbh_cur1.eft_authorization_code,
x_established_fin_aid_amount => l_tbh_cur1.established_fin_aid_amount,
x_expect_family_contribute_amt => l_tbh_cur1.expect_family_contribute_amt,
x_external_loan_id_txt => l_tbh_cur1.external_loan_id_txt,
x_flp_approved_amt => l_tbh_cur1.flp_approved_amt,
x_fls_approved_amt => l_tbh_cur1.fls_approved_amt,
x_flu_approved_amt => l_tbh_cur1.flu_approved_amt,
x_guarantor_use_txt => l_tbh_cur1.guarantor_use_txt,
x_lender_use_txt => l_tbh_cur1.lender_use_txt,
x_loan_app_form_code => l_tbh_cur1.loan_app_form_code,
x_mpn_type_flag => l_tbh_cur1.mpn_type_flag,
x_reinstatement_amt => l_tbh_cur1.reinstatement_amt,
x_requested_loan_amt => l_tbh_cur1.requested_loan_amt,
x_school_id_txt => l_tbh_cur1.school_id_txt,
x_school_use_txt => l_tbh_cur1.school_use_txt,
x_student_electronic_sign_flag => l_tbh_cur1.student_electronic_sign_flag,
x_esign_src_typ_cd => l_tbh_cur1.esign_src_typ_cd
);
igf_sl_loans_pkg.update_row (
x_mode => 'R',
x_rowid => l_tbh_cur2.row_id,
x_loan_id => l_tbh_cur2.loan_id,
x_award_id => l_tbh_cur2.award_id,
x_seq_num => l_tbh_cur2.seq_num,
x_loan_number => l_tbh_cur2.loan_number,
x_loan_per_begin_date => l_tbh_cur2.loan_per_begin_date,
x_loan_per_end_date => l_tbh_cur2.loan_per_end_date,
x_loan_status => l_tbh_cur2.loan_status,
x_loan_status_date => l_tbh_cur2.loan_status_date,
x_loan_chg_status => l_tbh_cur2.loan_chg_status,
x_loan_chg_status_date => l_tbh_cur2.loan_chg_status_date,
x_active => l_tbh_cur2.active,
x_active_date => l_tbh_cur2.active_date,
x_borw_detrm_code => l_tbh_cur2.borw_detrm_code,
x_legacy_record_flag => NULL,
x_external_loan_id_txt => l_tbh_cur2.external_loan_id_txt);
UPDATE IGF_SL_DL_RESP_DTLS
SET STATUS_CODE = 'P'
WHERE DL_LOAN_RESP_ID = l_dl_resp.DL_LOAN_RESP_ID;
update_flag := FALSE;
UPDATE IGF_SL_DLDB_RSP_DTL
SET status_code = 'N'
WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
update_flag := FALSE;
UPDATE IGF_SL_DLDB_RSP_DTL
SET status_code = 'P'
WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
update_flag := TRUE;
UPDATE IGF_SL_DLDB_RSP_DTL
SET STATUS_CODE = 'N'
WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
update_flag := FALSE;
igf_gr_gen.insert_sys_holds(l_dl_rec.award_id,l_dl_db_rec.disb_num,'DL');
UPDATE IGF_SL_DLDB_RSP_DTL
SET STATUS_CODE = 'P'
WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
update_flag := TRUE;
UPDATE IGF_SL_DLDB_RSP_DTL
SET STATUS_CODE = 'P'
WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
update_flag := FALSE;
IF (update_flag) THEN
igf_aw_db_chg_dtls_pkg.update_row(
x_rowid => l_tbh_disb.row_id ,
x_award_id => l_tbh_disb.award_id,
x_disb_num => l_tbh_disb.disb_num ,
x_disb_seq_num => l_tbh_disb.disb_seq_num,
x_DISB_ACCEPTED_AMT => l_tbh_disb.DISB_ACCEPTED_AMT,
x_orig_fee_amt => l_tbh_disb.orig_fee_amt ,
x_disb_net_amt => l_tbh_disb.disb_net_amt ,
x_disb_date => l_tbh_disb.disb_date ,
x_disb_activity => l_tbh_disb.disb_activity ,
x_disb_status => l_tbh_disb.disb_status ,
x_disb_status_date => l_tbh_disb.disb_status_date,
x_disb_rel_flag => l_tbh_disb.disb_rel_flag ,
x_first_disb_flag => l_tbh_disb.first_disb_flag ,
x_INTEREST_REBATE_AMT => l_tbh_disb.INTEREST_REBATE_AMT,
x_disb_conf_flag => l_tbh_disb.disb_conf_flag ,
x_pymnt_prd_start_date => l_tbh_disb.pymnt_prd_start_date ,
x_note_message => l_tbh_disb.note_message ,
x_batch_id_txt => l_tbh_disb.batch_id_txt ,
x_ack_date => l_tbh_disb.ack_date ,
x_booking_id_txt => l_tbh_disb.booking_id_txt ,
x_booking_date => l_tbh_disb.booking_date ,
x_mode => 'R'
);
igf_aw_db_cod_dtls_pkg.update_row(
x_rowid => l_tbh_disb1.row_id ,
x_award_id => l_tbh_disb1.award_id ,
x_document_id_txt => l_tbh_disb1.document_id_txt ,
x_disb_num => l_tbh_disb1.disb_num ,
x_disb_seq_num => l_tbh_disb1.disb_seq_num ,
x_DISB_ACCEPTED_AMT => l_tbh_disb1.DISB_ACCEPTED_AMT,
x_orig_fee_amt => l_tbh_disb1.orig_fee_amt ,
x_disb_net_amt => l_tbh_disb1.disb_net_amt ,
x_disb_date => l_tbh_disb1.disb_date ,
x_disb_rel_flag => l_tbh_disb1.disb_rel_flag ,
x_first_disb_flag => l_tbh_disb1.first_disb_flag ,
x_INTEREST_REBATE_AMT => l_tbh_disb1.INTEREST_REBATE_AMT,
x_disb_conf_flag => l_tbh_disb1.disb_conf_flag ,
x_pymnt_per_start_date => l_tbh_disb1.pymnt_per_start_date,
x_note_message => l_tbh_disb1.note_message ,
x_rep_entity_id_txt => l_tbh_disb1.rep_entity_id_txt,
x_atd_entity_id_txt => l_tbh_disb1.atd_entity_id_txt,
x_mode => 'R'
);
SELECT *
FROM IGF_GR_RESP_DTLS
WHERE STDNT_RESP_ID = cp_st_id
AND NVL(STATUS_CODE, '*') <> 'P';
SELECT *
FROM IGF_GR_DB_RESP_DTLS
WHERE PELL_RESP_ID = cp_pell_id
AND NVL(STATUS_CODE, '*') <> 'P';
SELECT *
FROM IGF_GR_COD_DTLS
WHERE S_SSN = cp_ssn
AND S_DATE_OF_BIRTH = cp_dob
AND S_LAST_NAME = cp_lname
AND FIN_AWARD_YEAR = cp_fin_awd_yr;
SELECT *
FROM IGF_AW_DB_CHG_DTLS
WHERE AWARD_ID = cp_awd_id
AND DISB_NUM = cp_disb_num
AND DISB_SEQ_NUM = cp_disb_seq_num;
SELECT *
FROM IGF_GR_RFMS
WHERE ORIGINATION_ID = orig_id
FOR UPDATE;
SELECT *
FROM IGF_AW_DB_CHG_DTLS_V
WHERE AWARD_ID = cp_awd_id
AND DISB_NUM = cp_disb_num
AND DISB_SEQ_NUM = cp_disb_seq_num;
SELECT *
FROM IGF_AW_DB_COD_DTLS_V
WHERE AWARD_ID = cp_awd_id
AND DISB_NUM = cp_disb_num
AND DISB_SEQ_NUM = cp_disb_seq_num;
update_flag BOOLEAN;
UPDATE IGF_GR_RESP_DTLS
SET STATUS_CODE = 'N'
WHERE PELL_RESP_ID = l_pell_resp.PELL_RESP_ID;
/*SELECT THE COLUMNS YTD_DISB_AMT, TOT_ELIG_USED, SCHD_PELL_AMT, NEG_PEND_AMT, FSA_CODE_1,FSA_CODE_2,FSA_CODE_3,PELL_STATUS
CPS_VERIF_FLAG, HIGH_CPS_TRANS_NUM FROM IGF_GR_RESP_DTLS
Also update the fields YTD_DISB_AMT, TOT_ELIG_USED, PENDING_AMOUNT,ORIG_aCTION_CODE in the table IGF_GR_RFMS_ALL
*/--dont
l_tbh_pell.ORIG_ACTION_CODE := l_pell_resp.RESP_CODE;
igf_gr_rfms_pkg.update_row(
x_rowid => l_tbh_pell.row_id,
x_origination_id => l_tbh_pell.origination_id,
x_ci_cal_type => l_tbh_pell.ci_cal_type ,
x_ci_sequence_number => l_tbh_pell.ci_sequence_number ,
x_base_id => l_tbh_pell.base_id ,
x_award_id => l_tbh_pell.award_id ,
x_rfmb_id => l_tbh_pell.rfmb_id ,
x_sys_orig_ssn => l_tbh_pell.sys_orig_ssn ,
x_sys_orig_name_cd => l_tbh_pell.sys_orig_name_cd ,
x_transaction_num => l_tbh_pell.transaction_num ,
x_efc => l_tbh_pell.efc ,
x_ver_status_code => l_tbh_pell.ver_status_code ,
x_secondary_efc => l_tbh_pell.secondary_efc ,
x_secondary_efc_cd => l_tbh_pell.secondary_efc_cd ,
x_pell_amount => l_tbh_pell.pell_amount ,
x_pell_profile => l_tbh_pell.pell_profile ,
x_enrollment_status => l_tbh_pell.enrollment_status ,
x_enrollment_dt => l_tbh_pell.enrollment_dt ,
x_coa_amount => l_tbh_pell.coa_amount ,
x_academic_calendar => l_tbh_pell.academic_calendar ,
x_payment_method => l_tbh_pell.payment_method ,
x_total_pymt_prds => l_tbh_pell.total_pymt_prds ,
x_incrcd_fed_pell_rcp_cd => l_tbh_pell.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => l_tbh_pell.attending_campus_id ,
x_est_disb_dt1 => l_tbh_pell.est_disb_dt1 ,
x_orig_action_code => l_tbh_pell.orig_action_code ,
x_orig_status_dt => l_tbh_pell.orig_status_dt ,
x_orig_ed_use_flags => l_tbh_pell.orig_ed_use_flags ,
x_ft_pell_amount => l_tbh_pell.ft_pell_amount ,
x_prev_accpt_efc => l_tbh_pell.prev_accpt_efc ,
x_prev_accpt_tran_no => l_tbh_pell.prev_accpt_tran_no ,
x_prev_accpt_sec_efc_cd => l_tbh_pell.prev_accpt_sec_efc_cd ,
x_prev_accpt_coa => l_tbh_pell.prev_accpt_coa ,
x_orig_reject_code => l_tbh_pell.orig_reject_code ,
x_wk_inst_time_calc_pymt => l_tbh_pell.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => l_tbh_pell.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => l_tbh_pell.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => l_tbh_pell.cr_clk_hrs_acad_yr ,
x_inst_cross_ref_cd => l_tbh_pell.inst_cross_ref_cd ,
x_low_tution_fee => l_tbh_pell.low_tution_fee ,
x_rec_source => l_tbh_pell.rec_source ,
x_pending_amount => l_tbh_pell.pending_amount ,
x_mode => 'R',
x_birth_dt => l_tbh_pell.birth_dt ,
x_last_name => l_tbh_pell.last_name ,
x_first_name => l_tbh_pell.first_name ,
x_middle_name => l_tbh_pell.middle_name ,
x_current_ssn => l_tbh_pell.current_ssn ,
x_legacy_record_flag => NULL,
x_reporting_pell_cd => NULL,
x_rep_entity_id_txt => l_tbh_pell.rep_entity_id_txt ,
x_atd_entity_id_txt => l_tbh_pell.atd_entity_id_txt ,
x_note_message => l_tbh_pell.note_message ,
x_full_resp_code => l_tbh_pell.full_resp_code ,
x_document_id_txt => l_tbh_pell.document_id_txt
);
UPDATE IGF_GR_RESP_DTLS
SET STATUS_CODE = 'P'
WHERE PELL_RESP_ID = l_pell_resp.PELL_RESP_ID;
update_flag := FALSE;
UPDATE IGF_GR_DB_RESP_DTLS
SET status_code = 'N'
WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
update_flag := FALSE;
UPDATE IGF_GR_DB_RESP_DTLS
SET status_code = 'P'
WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
update_flag := TRUE;
UPDATE IGF_GR_DB_RESP_DTLS
SET STATUS_CODE = 'N'
WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
update_flag := FALSE;
if these are different then print into the log file and insert system hold on the disbursement as per
existing logic. Call the wrapper igf_gr_gen.insert_sys_holds(rec_award.award_id,rec_disb_orig.disb_ref_num,'PELL');
Update the disb_status = resp_code in table IGF_AW_DB_CHG_DTLS
Also print in the log file, Payment Period Start Date if present and update it in the table
IGF_AW_DISB_COD_DTLS and IGF_AW_DB_CHG_DTLS.
Update the status_code = 'P' in IGF_GR_DB_RESP_DTLS*/ --dont
IF l_pell_db_rec.DISB_ACCEPTED_AMT <> l_pell_db_resp.disb_amt THEN
fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DB_AMT')||':' ||l_pell_db_rec.DISB_ACCEPTED_AMT);
igf_gr_gen.insert_sys_holds(l_pell_rec.award_id,l_pell_db_rec.disb_num,'PELL');
UPDATE IGF_GR_DB_RESP_DTLS
SET STATUS_CODE = 'P'
WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
update_flag := TRUE;
Update the status_code = 'P' in IGF_GR_DB_RESP_DTLS. */ --dont
fnd_message.set_name('IGF','IGF_SL_COD_SCHL_ADJ');
UPDATE IGF_GR_DB_RESP_DTLS
SET STATUS_CODE = 'P'
WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
update_flag := FALSE;
IF (update_flag) THEN
igf_aw_db_chg_dtls_pkg.update_row(
x_rowid => l_tbh_disb.row_id,
x_award_id => l_tbh_disb.award_id,
x_disb_num => l_tbh_disb.disb_num,
x_disb_seq_num => l_tbh_disb.disb_seq_num,
x_disb_accepted_amt => l_tbh_disb.disb_accepted_amt,
x_orig_fee_amt => l_tbh_disb.orig_fee_amt,
x_disb_net_amt => l_tbh_disb.disb_net_amt,
x_disb_date => l_tbh_disb.disb_date,
x_disb_activity => l_tbh_disb.disb_activity,
x_disb_status => l_tbh_disb.disb_status,
x_disb_status_date => l_tbh_disb.disb_status_date,
x_disb_rel_flag => l_tbh_disb.disb_rel_flag,
x_first_disb_flag => l_tbh_disb.first_disb_flag,
x_interest_rebate_amt => l_tbh_disb.interest_rebate_amt,
x_disb_conf_flag => l_tbh_disb.disb_conf_flag,
x_pymnt_prd_start_date => l_tbh_disb.pymnt_prd_start_date,
x_note_message => l_tbh_disb.note_message,
x_batch_id_txt => l_tbh_disb.batch_id_txt,
x_ack_date => l_tbh_disb.ack_date,
x_booking_id_txt => l_tbh_disb.booking_id_txt,
x_booking_date => l_tbh_disb.booking_date,
x_mode => 'R'
);
igf_aw_db_cod_dtls_pkg.update_row(
x_rowid => l_tbh_disb1.row_id ,
x_award_id => l_tbh_disb1.award_id ,
x_document_id_txt => l_tbh_disb1.document_id_txt ,
x_disb_num => l_tbh_disb1.disb_num ,
x_disb_seq_num => l_tbh_disb1.disb_seq_num ,
x_disb_accepted_amt => l_tbh_disb1.disb_accepted_amt ,
x_orig_fee_amt => l_tbh_disb1.orig_fee_amt ,
x_disb_net_amt => l_tbh_disb1.disb_net_amt ,
x_disb_date => l_tbh_disb1.disb_date ,
x_disb_rel_flag => l_tbh_disb1.disb_rel_flag ,
x_first_disb_flag => l_tbh_disb1.first_disb_flag ,
x_interest_rebate_amt => l_tbh_disb1.interest_rebate_amt ,
x_disb_conf_flag => l_tbh_disb1.disb_conf_flag ,
x_pymnt_per_start_date => l_tbh_disb1.pymnt_per_start_date,
x_note_message => l_tbh_disb1.note_message ,
x_rep_entity_id_txt => l_tbh_disb1.rep_entity_id_txt ,
x_atd_entity_id_txt => l_tbh_disb1.atd_entity_id_txt ,
x_mode => 'R'
);
SELECT DOC_CREATED_DATE,
SOURCE_ENTITY_ID_TXT,
SOFT_PROVIDER,
SOFT_VERSION,
FULL_RESP_CODE,
RECEIPT_DATE,
DEST_ENTITY_ID_TXT,
DOC_TYPE_CODE,
DOC_STATUS_CODE,
PROCESS_DATE
FROM IGF_SL_CR_RESP_DTLS
WHERE DOCUMENT_ID_TXT = cp_doc_id;
SELECT *
FROM IGF_SL_RS_RESP_DTLS
WHERE document_id_txt = cp_doc_id;
SELECT *
FROM IGF_SL_RESP_F_SMRY
WHERE REP_SCHL_RESP_ID = cp_rep_id
ORDER BY FIN_AWARD_YEAR,
FIN_AWARD_TYPE;
SELECT *
FROM IGF_SL_AS_RESP_DTLS
WHERE REP_SCHL_RESP_ID = cp_rep_id;
SELECT *
FROM IGF_SL_ST_RESP_DTLS
WHERE ATD_SCHL_RESP_ID = cp_atd_id;
SELECT *
FROM IGF_SL_COD_TEMP;
SELECT codpell.ROWID row_id, codpell.*
FROM IGF_GR_COD_DTLS codpell
WHERE codpell.s_ssn = cp_ssn
AND codpell.s_last_name = cp_lname
AND codpell.s_date_of_birth = cp_dob;
SELECT coddl.*
FROM IGF_SL_LOR_LOC coddl
WHERE coddl.s_ssn = cp_ssn
AND coddl.s_last_name = cp_lname
AND coddl.s_date_of_birth = cp_dob;
UPDATE IGF_SL_CR_RESP_DTLS
SET DOC_TYPE_CODE = l_temp.DOC_TYPE_CODE,
DOC_STATUS_CODE = l_temp.DOC_STATUS_CODE,
PROCESS_DATE = l_temp.PROCESS_DATE
WHERE DOCUMENT_ID_TXT = l_temp.REC_ID;
UPDATE IGF_SL_RS_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE
WHERE REP_SCHL_RESP_ID = l_temp.REC_ID;
UPDATE IGF_SL_AS_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE
WHERE ATD_SCHL_RESP_ID = l_temp.REC_ID;
UPDATE IGF_SL_ST_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE
WHERE STDNT_RESP_ID = l_temp.REC_ID;
UPDATE IGF_SL_DL_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE,
ELEC_MPN_FLAG = l_temp.ELEC_MPN_FLAG,
PNOTE_MPN_ID = l_temp.PNOTE_MPN_ID,
MPN_STATUS_CODE = l_temp.MPN_STATUS_CODE,
MPN_LINK_FLAG = l_temp.MPN_LINK_FLAG,
PYMT_SERVICER_AMT = l_temp.PYMT_SERVICER_AMT,
PYMT_SERVICER_DATE = l_temp.PYMT_SERVICER_DATE,
BOOK_LOAN_AMT = l_temp.BOOK_LOAN_AMT,
BOOK_LOAN_AMT_DATE = l_temp.BOOK_LOAN_AMT_DATE,
ENDORSER_AMT = l_temp.ENDORSER_AMT,
CRDT_DECISION_STATUS = l_temp.CRDT_DECISION_STATUS,
CRDT_DECISION_DATE = l_temp.CRDT_DECISION_DATE,
CRDT_DECISION_OVRD_CODE = l_temp.CRDT_DECISION_OVRD_CODE
WHERE DL_LOAN_RESP_ID = l_temp.REC_ID;
UPDATE IGF_SL_DL_RESP_DTLS
SET B_RESP_CODE = l_temp.RESP_CODE
WHERE DL_LOAN_RESP_ID = l_temp.REC_ID;
UPDATE IGF_SL_DLDB_RSP_DTL
SET RESP_CODE = l_temp.RESP_CODE,
PREV_SEQ_NUM = l_temp.PREV_SEQ_NUM
WHERE DISB_RESP_ID = l_temp.REC_ID;
UPDATE IGF_GR_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE,
YTD_DISB_AMT = l_temp.YTD_DISB_AMT,
TOT_ELIG_USED = l_temp.TOT_ELIG_USED,
SCHD_PELL_AMT = l_temp.SCHD_PELL_AMT,
NEG_PEND_AMT = l_temp.NEG_PEND_AMT,
FSA_CODE_1 = l_temp.FSA_CODE_1,
FSA_CODE_2 = l_temp.FSA_CODE_2,
FSA_CODE_3 = l_temp.FSA_CODE_3,
CPS_VERIF_FLAG = l_temp.CPS_VERIF_FLAG,
HIGH_CPS_TRANS_NUM = l_temp.HIGH_CPS_TRANS_NUM
WHERE PELL_RESP_ID = l_temp.REC_ID;
UPDATE IGF_GR_DB_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE,
PREV_SEQ_NUM = l_temp.PREV_SEQ_NUM
WHERE DISB_RESP_ID = l_temp.REC_ID;
UPDATE IGF_SL_DI_RESP_DTLS
SET RESP_CODE = l_temp.RESP_CODE
WHERE DL_INFO_ID = l_temp.REC_ID;
update_xml_document(l_chk_doc, 'E');
update_xml_document(l_chk_doc, 'F');
delete_temp_table_data();
igf_sl_cod_doc_dtls_pkg.update_row (
x_rowid => l_chk_doc.row_id,
x_document_id_txt => l_chk_doc.document_id_txt,
x_outbound_doc => l_chk_doc.outbound_doc ,
x_inbound_doc => l_chk_doc.inbound_doc ,
x_send_date => l_chk_doc.send_date ,
x_ack_date => l_chk_doc.ack_date ,
x_doc_status => 'J' ,
x_doc_type => l_chk_doc.doc_type ,
x_full_resp_code => l_chk_doc.full_resp_code ,
x_mode => 'R'
);
igf_gr_cod_dtls_pkg.update_row(
x_rowid => rec.row_id,
x_origination_id => rec.origination_id,
x_award_id => rec.award_id,
x_document_id_txt => rec.document_id_txt,
x_base_id => rec.base_id,
x_fin_award_year => rec.fin_award_year,
x_cps_trans_num => rec.cps_trans_num,
x_award_amt => rec.award_amt,
x_coa_amt => rec.coa_amt,
x_low_tution_fee => rec.low_tution_fee,
x_incarc_flag => rec.incarc_flag,
x_ver_status_code => rec.ver_status_code,
x_enrollment_date => rec.enrollment_date,
x_sec_efc_code => rec.sec_efc_code,
x_ytd_disb_amt => rec.ytd_disb_amt,
x_tot_elig_used => rec.tot_elig_used,
x_schd_pell_amt => rec.schd_pell_amt,
x_neg_pend_amt => rec.neg_pend_amt,
x_cps_verif_flag => rec.cps_verif_flag,
x_high_cps_trans_num => rec.high_cps_trans_num,
x_note_message => rec.note_message,
x_full_resp_code => rec.full_resp_code,
x_atd_entity_id_txt => rec.atd_entity_id_txt,
x_rep_entity_id_txt => rec.rep_entity_id_txt,
x_source_entity_id_txt => rec.source_entity_id_txt,
x_pell_status => rec.pell_status,
x_pell_status_date => rec.pell_status_date,
x_s_ssn => changed_p_ssn,
x_driver_lic_state => rec.driver_lic_state,
x_driver_lic_number => rec.driver_lic_number,
x_s_date_of_birth => changed_p_dob,
x_first_name => UPPER(rec.first_name),
x_middle_name => UPPER(rec.middle_name),
x_s_last_name => changed_p_lname,
x_s_chg_date_of_birth => NULL,
x_s_chg_ssn => NULL,
x_s_chg_last_name => NULL,
x_permt_addr_foreign_flag => NULL,
x_addr_type_code => NULL,
x_permt_addr_line_1 => UPPER(rec.permt_addr_line_1),
x_permt_addr_line_2 => UPPER(rec.permt_addr_line_2),
x_permt_addr_line_3 => UPPER(rec.permt_addr_line_3),
x_permt_addr_city => UPPER(rec.permt_addr_city),
x_permt_addr_state_code => UPPER(rec.permt_addr_state_code),
x_permt_addr_post_code => UPPER(rec.permt_addr_post_code),
x_permt_addr_county => UPPER(rec.permt_addr_county),
x_permt_addr_country => UPPER(rec.permt_addr_country),
x_phone_number_1 => rec.phone_number_1,
x_phone_number_2 => NULL,
x_phone_number_3 => NULL,
x_email_address => UPPER(rec.email_address),
x_citzn_status_code => rec.citzn_status_code,
x_mode => 'R'
);
igf_sl_lor_loc_pkg.update_row (
x_mode => 'R',
x_rowid => rec.row_id,
x_loan_id => rec.loan_id,
x_origination_id => rec.origination_id,
x_loan_number => rec.loan_number,
x_loan_type => rec.loan_type,
x_loan_amt_offered => rec.loan_amt_offered,
x_loan_amt_accepted => rec.loan_amt_accepted,
x_loan_per_begin_date => rec.loan_per_begin_date,
x_loan_per_end_date => rec.loan_per_end_date,
x_acad_yr_begin_date => rec.acad_yr_begin_date,
x_acad_yr_end_date => rec.acad_yr_end_date,
x_loan_status => rec.loan_status,
x_loan_status_date => rec.loan_status_date,
x_loan_chg_status => rec.loan_chg_status,
x_loan_chg_status_date => rec.loan_chg_status_date,
x_req_serial_loan_code => rec.req_serial_loan_code,
x_act_serial_loan_code => rec.act_serial_loan_code,
x_active => rec.active,
x_active_date => rec.active_date,
x_sch_cert_date => rec.sch_cert_date,
x_orig_status_flag => rec.orig_status_flag,
x_orig_batch_id => rec.orig_batch_id,
x_orig_batch_date => rec.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => rec.orig_ack_date,
x_credit_override => rec.credit_override,
x_credit_decision_date => rec.credit_decision_date,
x_pnote_delivery_code => rec.pnote_delivery_code,
x_pnote_status => rec.pnote_status,
x_pnote_status_date => rec.pnote_status_date,
x_pnote_id => rec.pnote_id,
x_pnote_print_ind => rec.pnote_print_ind,
x_pnote_accept_amt => rec.pnote_accept_amt,
x_pnote_accept_date => rec.pnote_accept_date,
x_p_signature_code => rec.p_signature_code,
x_p_signature_date => rec.p_signature_date,
x_s_signature_code => rec.s_signature_code,
x_unsub_elig_for_heal => rec.unsub_elig_for_heal,
x_disclosure_print_ind => rec.disclosure_print_ind,
x_orig_fee_perct => rec.orig_fee_perct,
x_borw_confirm_ind => rec.borw_confirm_ind,
x_borw_interest_ind => rec.borw_interest_ind,
x_unsub_elig_for_depnt => rec.unsub_elig_for_depnt,
x_guarantee_amt => rec.guarantee_amt,
x_guarantee_date => rec.guarantee_date,
x_guarnt_adj_ind => rec.guarnt_adj_ind,
x_guarnt_amt_redn_code => rec.guarnt_amt_redn_code,
x_guarnt_status_code => rec.guarnt_status_code,
x_guarnt_status_date => rec.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => rec.lend_status_code,
x_lend_status_date => rec.lend_status_date,
x_grade_level_code => rec.grade_level_code,
x_enrollment_code => rec.enrollment_code,
x_anticip_compl_date => rec.anticip_compl_date,
x_borw_lender_id => rec.borw_lender_id,
x_duns_borw_lender_id => NULL,
x_guarantor_id => rec.guarantor_id,
x_duns_guarnt_id => NULL,
x_prc_type_code => rec.prc_type_code,
x_rec_type_ind => rec.rec_type_ind,
x_cl_loan_type => rec.cl_loan_type,
x_cl_seq_number => rec.cl_seq_number,
x_last_resort_lender => rec.last_resort_lender,
x_lender_id => rec.lender_id,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => rec.lend_non_ed_brc_id,
x_recipient_id => rec.recipient_id,
x_recipient_type => rec.recipient_type,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => rec.recip_non_ed_brc_id,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => rec.alt_prog_type_code,
x_alt_appl_ver_code => rec.alt_appl_ver_code,
x_borw_outstd_loan_code => rec.borw_outstd_loan_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => rec.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_tot_outstd_stafford => rec.tot_outstd_stafford,
x_tot_outstd_plus => rec.tot_outstd_plus,
x_alt_borw_tot_debt => rec.alt_borw_tot_debt,
x_act_interest_rate => rec.act_interest_rate,
x_service_type_code => rec.service_type_code,
x_rev_notice_of_guarnt => rec.rev_notice_of_guarnt,
x_sch_refund_amt => rec.sch_refund_amt,
x_sch_refund_date => rec.sch_refund_date,
x_uniq_layout_vend_code => rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => rec.uniq_layout_ident_code,
x_p_person_id => rec.p_person_id,
x_p_ssn => rec.p_ssn,
x_p_ssn_chg_date => NULL,
x_p_last_name => rec.p_last_name,
x_p_first_name => rec.p_first_name,
x_p_middle_name => rec.p_middle_name,
x_p_permt_addr1 => rec.p_permt_addr1,
x_p_permt_addr2 => rec.p_permt_addr2,
x_p_permt_city => rec.p_permt_city,
x_p_permt_state => rec.p_permt_state,
x_p_permt_zip => rec.p_permt_zip,
x_p_permt_addr_chg_date => rec.p_permt_addr_chg_date,
x_p_permt_phone => rec.p_permt_phone,
x_p_email_addr => rec.p_email_addr,
x_p_date_of_birth => rec.p_date_of_birth,
x_p_dob_chg_date => NULL,
x_p_license_num => rec.p_license_num,
x_p_license_state => rec.p_license_state,
x_p_citizenship_status => rec.p_citizenship_status,
x_p_alien_reg_num => rec.p_alien_reg_num,
x_p_default_status => rec.p_default_status,
x_p_foreign_postal_code => rec.p_foreign_postal_code,
x_p_state_of_legal_res => rec.p_state_of_legal_res,
x_p_legal_res_date => rec.p_legal_res_date,
x_s_ssn => changed_p_ssn,
x_s_ssn_chg_date => NULL,
x_s_last_name => changed_p_lname,
x_s_first_name => rec.s_first_name,
x_s_middle_name => rec.s_middle_name,
x_s_permt_addr1 => rec.s_permt_addr1,
x_s_permt_addr2 => rec.s_permt_addr2,
x_s_permt_city => rec.s_permt_city,
x_s_permt_state => rec.s_permt_state,
x_s_permt_zip => rec.s_permt_zip,
x_s_permt_addr_chg_date => rec.s_permt_addr_chg_date,
x_s_permt_phone => rec.s_permt_phone,
x_s_local_addr1 => rec.s_local_addr1,
x_s_local_addr2 => rec.s_local_addr2,
x_s_local_city => rec.s_local_city,
x_s_local_state => rec.s_local_state,
x_s_local_zip => rec.s_local_zip,
x_s_local_addr_chg_date => NULL,
x_s_email_addr => rec.s_email_addr,
x_s_date_of_birth => changed_p_dob,
x_s_dob_chg_date => NULL,
x_s_license_num => rec.s_license_num,
x_s_license_state => rec.s_license_state,
x_s_depncy_status => rec.s_depncy_status,
x_s_default_status => rec.s_default_status,
x_s_citizenship_status => rec.s_citizenship_status,
x_s_alien_reg_num => rec.s_alien_reg_num,
x_s_foreign_postal_code => rec.s_foreign_postal_code,
x_pnote_batch_id => rec.pnote_batch_id,
x_pnote_ack_date => rec.pnote_ack_date,
x_pnote_mpn_ind => rec.pnote_mpn_ind,
x_award_id => rec.award_id ,
x_base_id => rec.base_id ,
x_document_id_txt => rec.document_id_txt ,
x_loan_key_num => rec.loan_key_num ,
x_INTEREST_REBATE_PERCENT_NUM => rec.INTEREST_REBATE_PERCENT_NUM,
x_fin_award_year => rec.fin_award_year ,
x_cps_trans_num => rec.cps_trans_num ,
x_ATD_ENTITY_ID_TXT => rec.ATD_ENTITY_ID_TXT,
x_REP_ENTITY_ID_TXT => rec.REP_ENTITY_ID_TXT,
x_SOURCE_ENTITY_ID_TXT => rec.SOURCE_ENTITY_ID_TXT,
x_pymt_servicer_amt => rec.pymt_servicer_amt ,
x_pymt_servicer_date => rec.pymt_servicer_date ,
x_book_loan_amt => rec.book_loan_amt ,
x_book_loan_amt_date => rec.book_loan_amt_date ,
x_s_chg_birth_date => NULL,
x_s_chg_ssn => NULL,
x_s_chg_last_name => NULL,
x_b_chg_birth_date => rec.b_chg_birth_date ,
x_b_chg_ssn => rec.b_chg_ssn ,
x_b_chg_last_name => rec.b_chg_last_name ,
x_note_message => rec.note_message ,
x_full_resp_code => rec.full_resp_code ,
x_s_permt_county => rec.s_permt_county ,
x_b_permt_county => rec.b_permt_county ,
x_s_permt_country => rec.s_permt_country ,
x_b_permt_country => rec.b_permt_country ,
x_crdt_decision_status => rec.crdt_decision_status,
x_actual_record_type_code => rec.actual_record_type_code,
x_alt_approved_amt => rec.alt_approved_amt,
x_alt_borrower_ind_flag => rec.alt_borrower_ind_flag,
x_borower_credit_authoriz_flag => rec.borower_credit_authoriz_flag,
x_borower_electronic_sign_flag => rec.borower_electronic_sign_flag,
x_cost_of_attendance_amt => rec.cost_of_attendance_amt,
x_deferment_request_code => rec.deferment_request_code,
x_eft_authorization_code => rec.eft_authorization_code,
x_established_fin_aid_amount => rec.established_fin_aid_amount,
x_expect_family_contribute_amt => rec.expect_family_contribute_amt,
x_external_loan_id_txt => rec.external_loan_id_txt,
x_flp_approved_amt => rec.flp_approved_amt,
x_fls_approved_amt => rec.fls_approved_amt,
x_flu_approved_amt => rec.flu_approved_amt,
x_guarantor_use_txt => rec.guarantor_use_txt,
x_lender_use_txt => rec.lender_use_txt,
x_loan_app_form_code => rec.loan_app_form_code,
x_mpn_type_flag => rec.mpn_type_flag,
x_reinstatement_amt => rec.reinstatement_amt,
x_requested_loan_amt => rec.requested_loan_amt,
x_school_id_txt => rec.school_id_txt,
x_school_use_txt => rec.school_use_txt,
x_student_electronic_sign_flag => rec.student_electronic_sign_flag,
x_esign_src_typ_cd => rec.esign_src_typ_cd
);
FUNCTION get_last_updated_by
RETURN NUMBER AS
BEGIN
RETURN -1;
END get_last_updated_by ;
FUNCTION get_last_update_date
RETURN DATE AS
BEGIN
RETURN TO_DATE('01062004', 'DDMMYYYY');
END get_last_update_date ;
FUNCTION get_last_update_login
RETURN NUMBER AS
BEGIN
RETURN -1;
END get_last_update_login ;
PROCEDURE update_rs_respcode(p_rec_id IN VARCHAR2, p_resp_code IN VARCHAR2)
AS
CURSOR cur_cod_temp(cp_rec_id IN VARCHAR2) IS
SELECT rec_id, level_code, resp_code
FROM IGF_SL_COD_TEMP
WHERE REC_ID = cp_rec_id
AND LEVEL_CODE = 'RS';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'p_rec_id = ' || p_rec_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'p_resp_code = ' || p_resp_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'Record NOT found with p_rec_id ' || p_rec_id || ' and with level_code RS');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'Record has been found with p_rec_id ' || p_rec_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'REC_ID = ' || rec_cod_temp.REC_ID);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'LEVEL_CODE = ' || rec_cod_temp.LEVEL_CODE);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'RESP_CODE = ' || rec_cod_temp.RESP_CODE);
UPDATE IGF_SL_COD_TEMP
SET RESP_CODE = p_resp_code
WHERE REC_ID = p_rec_id
AND LEVEL_CODE = 'RS';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, Record NOT found with p_rec_id ' || p_rec_id || ' and with level_code RS');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, Record has been found with p_rec_id ' || p_rec_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, REC_ID = ' || rec_cod_temp.REC_ID);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, LEVEL_CODE = ' || rec_cod_temp.LEVEL_CODE);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, RESP_CODE = ' || rec_cod_temp.RESP_CODE);
fnd_message.set_token('NAME','igf_sl_upload_xml.update_rs_respcode');
fnd_message.set_token('NAME','igf_sl_upload_xml.update_rs_respcode');
END update_rs_respcode;
PROCEDURE update_rcptdate_respcode(p_doc_id IN VARCHAR2, p_receipt_date IN VARCHAR2)
AS
ld_receipt_date DATE;
UPDATE IGF_SL_CR_RESP_DTLS
SET RECEIPT_DATE = ld_receipt_date
WHERE DOCUMENT_ID_TXT = p_doc_id;