[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lor.prc_type_code
,lor.cl_rec_status
FROM igf_sl_lor_all lor
WHERE loan_id = cp_n_loan_id;
SELECT resp.loan_per_begin_date
,resp.loan_per_end_date
FROM igf_sl_cl_resp_r1_all resp
WHERE resp.loan_number = cp_v_loan_number
AND resp.cl_rec_status IN ('B','G')
AND resp.prc_type_code IN ('GO','GP')
AND resp.cl_version_code = 'RELEASE-4'
ORDER BY clrp1_id DESC;
p_v_string => ' Verifying if existing change record is to be updated or inserted ' ||
' Loan Number : ' || l_v_loan_number ||
' cl version : ' || l_n_cl_version ||
' loan status : ' || l_v_loan_status ||
' Processing Type : ' || l_v_prc_type_code ||
' Loan Record Status : ' || l_c_cl_rec_status ||
' response loan per begin dt : ' || l_d_res_loan_per_begin_dt ||
' new loan per begin dt : ' || l_d_loan_per_begin_date ||
' change_field_code : ' || 'LOAN_PER_BEGIN_DT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_date = :cp_d_resp_begin_dt ' ||
'AND chdt.change_field_code = ''LOAN_PER_BEGIN_DT'' '||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid,
x_clchgsnd_id => l_n_clchgsnd_id,
x_award_id => l_n_award_id,
x_loan_number_txt => l_v_loan_number,
x_cl_version_code => l_n_cl_version,
x_change_field_code => 'LOAN_PER_BEGIN_DT',
x_change_record_type_txt => '07',
x_change_code_txt => 'A',
x_status_code => 'R',
x_status_date => TRUNC(SYSDATE),
x_response_status_code => NULL,
x_old_value_txt => NULL,
x_new_value_txt => NULL,
x_old_date => l_d_res_loan_per_begin_dt,
x_new_date => l_d_loan_per_begin_date,
x_old_amt => NULL,
x_new_amt => NULL,
x_disbursement_number => NULL,
x_disbursement_date => NULL,
x_change_issue_code => NULL,
x_disbursement_cancel_date => NULL,
x_disbursement_cancel_amt => NULL,
x_disbursement_revised_amt => NULL,
x_disbursement_revised_date => NULL,
x_disbursement_reissue_code => NULL,
x_disbursement_reinst_code => NULL,
x_disbursement_return_amt => NULL,
x_disbursement_return_date => NULL,
x_disbursement_return_code => NULL,
x_post_with_disb_return_amt => NULL,
x_post_with_disb_return_date => NULL,
x_post_with_disb_return_code => NULL,
x_prev_with_disb_return_amt => NULL,
x_prev_with_disb_return_date => NULL,
x_school_use_txt => NULL,
x_lender_use_txt => NULL,
x_guarantor_use_txt => NULL,
x_validation_edit_txt => NULL,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'LOAN_PER_BEGIN_DT' ||
' Change record type: ' ||'07 - Loan Period/Grade Level/Anticipated Completion Date Change' ||
' Change code : ' ||'A - Loan Period Change '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_loan_per_begin_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_loan_per_begin_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response loan per begin dt : ' ||l_d_res_loan_per_begin_dt ||
' new loan per begin dt : ' ||l_d_loan_per_begin_date ||
' change_field_code : ' ||'LOAN_PER_BEGIN_DT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_date = :cp_d_new_begin_dt ' ||
'AND chdt.change_field_code = ''LOAN_PER_BEGIN_DT'' '||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
p_v_string => ' Verifying if existing change record is to be updated or inserted ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response loan per end dt : ' ||l_d_res_loan_per_end_dt ||
' new loan per end dt : ' ||l_d_loan_per_end_date ||
' change_field_code : ' ||'LOAN_PER_END_DT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_date = :cp_d_resp_end_dt ' ||
'AND chdt.change_field_code = ''LOAN_PER_END_DT'' '||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => p_new_loan_rec.award_id ,
x_loan_number_txt => p_new_loan_rec.loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'LOAN_PER_END_DT' ,
x_change_record_type_txt => '07' ,
x_change_code_txt => 'A' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_res_loan_per_end_dt ,
x_new_date => l_d_loan_per_end_date ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'LOAN_PER_END_DT' ||
' Change record type : ' ||'07 - Loan Period/Grade Level/Anticipated Completion Date Change' ||
' Change code : ' ||'A - Loan Period Change '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_loan_per_end_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_loan_per_end_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response loan per end dt : ' ||l_d_res_loan_per_end_dt ||
' new loan per end dt : ' ||l_d_loan_per_end_date ||
' change_field_code : ' ||'LOAN_PER_END_DT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_date = :cp_d_new_end_dt ' ||
'AND chdt.change_field_code = ''LOAN_PER_END_DT'' '||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
SELECT lar.loan_number
,lar.loan_status
,lar.award_id
FROM igf_sl_loans_all lar
WHERE loan_id = cp_n_loan_id;
SELECT resp.anticip_compl_date
,resp.grade_level_code
FROM igf_sl_cl_resp_r1_all resp
WHERE resp.loan_number = cp_v_loan_number
AND resp.cl_rec_status IN ('B','G')
AND resp.prc_type_code IN ('GO','GP')
AND resp.cl_version_code = 'RELEASE-4'
ORDER BY clrp1_id DESC;
p_v_string => ' Verifying if existing change record is to be updated or inserted ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response anticip compl date : ' ||l_d_resp_anticip_compl_dt ||
' new anticip compl date : ' ||l_d_lor_anticip_compl_dt ||
' change_field_code : ' ||'ANTICIP_COML_DT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_date = :cp_d_resp_anticip_dt ' ||
'AND chdt.change_field_code = ''ANTICIP_COML_DT'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'ANTICIP_COML_DT' ,
x_change_record_type_txt => '07' ,
x_change_code_txt => 'C' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_anticip_compl_dt ,
x_new_date => l_d_lor_anticip_compl_dt ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_lor_anticip_compl_dt ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_lor_anticip_compl_dt ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response anticip compl date : ' ||l_d_resp_anticip_compl_dt ||
' new anticip compl date : ' ||l_d_lor_anticip_compl_dt ||
' change_field_code : ' ||'ANTICIP_COML_DT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_date = :cp_d_new_anticip_dt ' ||
'AND chdt.change_field_code = ''ANTICIP_COML_DT'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' || l_n_award_id ||
' loan number : ' || l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' || l_n_award_id ||
' loan number : ' || l_v_loan_number
);
p_v_string => ' Verifying if existing change record is to be updated or inserted ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response grade level cd : ' ||l_v_resp_grade_level_cd ||
' new grade level cd : ' ||l_v_ovr_grade_level_cd ||
' change_field_code : ' ||'GRADE_LEVEL_CODE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_value_txt = :cp_v_resp_grade_lvl_cd ' ||
'AND chdt.change_field_code = ''GRADE_LEVEL_CODE'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'GRADE_LEVEL_CODE' ,
x_change_record_type_txt => '07' ,
x_change_code_txt => 'B' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => l_v_resp_grade_level_cd ,
x_new_value_txt => l_v_ovr_grade_level_cd ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'GRADE_LEVEL_CODE' ||
' Change record type : ' ||'07 - Loan Period/Grade Level/Anticipated Completion Date Change'||
' Change code : ' ||'B - Grade Level Change '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => l_v_ovr_grade_level_cd ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => l_v_ovr_grade_level_cd ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response grade level cd : ' ||l_v_resp_grade_level_cd ||
' new grade level cd : ' ||l_v_ovr_grade_level_cd ||
' change_field_code : ' ||'GRADE_LEVEL_CODE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.old_value_txt = :cp_v_new_grade_lvl_cd ' ||
'AND chdt.change_field_code = ''GRADE_LEVEL_CODE'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.change_record_type_txt = ''07'' ';
p_v_string => ' Change record to be deleted' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
SELECT lar.loan_number
,lar.loan_status
,lor.prc_type_code
,lor.cl_rec_status
FROM igf_sl_lor_all lor
,igf_sl_loans_all lar
WHERE lor.loan_id = lar.loan_id
AND lar.award_id = cp_n_award_id;
SELECT adisb.disb_num
FROM igf_aw_awd_disb_all adisb
WHERE adisb.award_id = cp_n_award_id
AND NVL(adisb.fund_status,'N') = 'Y';
SELECT resp.guarantee_amt
FROM igf_sl_cl_resp_r1_all resp
WHERE resp.loan_number = cp_v_loan_number
AND resp.cl_rec_status IN ('B','G')
AND resp.prc_type_code IN ('GO','GP')
AND resp.cl_version_code = 'RELEASE-4'
ORDER BY clrp1_id DESC;
p_v_string => ' Verifying if change record is to be inserted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' Response Guaranteed amt : ' ||l_n_resp_guarantee_amt ||
' New Guaranteed amt : ' ||p_n_new_amount ||
' change_field_code : ' ||'AWARD_AMOUNT' ||
' chg_type : ' ||p_v_chg_type ||
' Change record type : ' ||'08 - Loan Cancellation' ||
' Change code : ' ||'A - Full Loan Cancellation '
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.new_amt = 0 ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''08'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'AWARD_AMOUNT' ,
x_change_record_type_txt => '08' ,
x_change_code_txt => 'A' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => l_n_resp_guarantee_amt ,
x_new_amt => 0 ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = 0 ' ||
'AND chdt.new_amt = :cp_n_new_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''08'' ';
p_v_string => ' fullY reinstated Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''24'' ';
p_v_string => ' Loan Increase Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
l_v_sqlstmt := 'SELECT chdt.ROWID, chdt.response_status_code ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.new_amt = 0 ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''08'' ';
p_v_string => ' Verifying if Loan Increase change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' Response Guaranteed amt: ' ||l_n_resp_guarantee_amt ||
' New Guaranteed amt : ' ||p_n_new_amount ||
' change_field_code : ' ||'AWARD_AMOUNT' ||
' chg_type : ' ||p_v_chg_type ||
' Change record type : ' ||'24 - Loan Increase' ||
' change_code : ' ||'A - Loan Increase'
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''24'' ';
p_v_string => ' Loan Increase Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
p_v_string => ' Verifying if Full Loan Reinstatement change record to be inserted or not'||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' Response Guaranteed amt: ' ||l_n_resp_guarantee_amt ||
' New Guaranteed amt : ' ||p_n_new_amount ||
' change_field_code : ' ||'AWARD_AMOUNT' ||
' chg_type : ' ||p_v_chg_type ||
' Change record type : ' ||'08 - Loan Cancellation' ||
' change_code : ' ||'B - Full Loan Reinstatement'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = 0 ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''08'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'AWARD_AMOUNT' ,
x_change_record_type_txt => '08' ,
x_change_code_txt => 'B' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => 0 ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'AWARD_AMOUNT' ||
' Change record type : ' ||'08 - Loan Reinstatement'||
' Change code : ' ||'B - Loan Reinstatement '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = 0 ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''08'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'AWARD_AMOUNT' ,
x_change_record_type_txt => '08' ,
x_change_code_txt => 'B' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => 0 ,
x_new_amt => l_n_resp_guarantee_amt,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'AWARD_AMOUNT' ||
' Change record type : ' ||'08 - Loan Reinstatement'||
' Change code : ' ||'B - Loan Reinstatement '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_resp_guarantee_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_resp_guarantee_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''24'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'AWARD_AMOUNT' ,
x_change_record_type_txt => '24' ,
x_change_code_txt => 'A' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => l_n_resp_guarantee_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'AWARD_AMOUNT' ||
' Change record type : ' ||'24 - Loan Increase'||
' Change code : ' ||'A - LOan Increase '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.new_amt = 0 ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''08'' ';
p_v_string => ' full loan cancellation Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' full loan cancellation Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
p_v_string => ' Verifying if Loan Increase change record is to be inserted or not'||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' Response Guaranteed amt: ' ||l_n_resp_guarantee_amt ||
' New Guaranteed amt : ' ||p_n_new_amount ||
' change_field_code : ' ||'AWARD_AMOUNT' ||
' chg_type : ' ||p_v_chg_type ||
' Change record type : ' ||'24 - Loan Increase' ||
' change_code : ' ||'A - Loan Increase'
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''24'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'AWARD_AMOUNT' ,
x_change_record_type_txt => '24' ,
x_change_code_txt => 'A' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => l_n_resp_guarantee_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => NULL ,
x_disbursement_date => NULL ,
x_change_issue_code => NULL ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => NULL ,
x_disbursement_revised_date => NULL ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => NULL ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'AWARD_AMOUNT' ||
' Change record type : ' ||'24 - Loan Increase'||
' Change code : ' ||'A - LOan Increase '
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => p_n_new_amount ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' Response Guaranteed amt: ' ||l_n_resp_guarantee_amt ||
' New Guaranteed amt : ' ||p_n_new_amount ||
' change_field_code : ' ||'AWARD_AMOUNT' ||
' chg_type : ' ||p_v_chg_type ||
' Change record type : ' ||'24 - Loan Increase' ||
' change_code : ' ||'A - Loan Increase'
);
l_v_sqlstmt := 'SELECT chdt.ROWID ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.loan_number_txt = :cp_v_loan_number ' ||
'AND chdt.old_amt = :cp_n_old_amt ' ||
'AND chdt.change_field_code = ''AWARD_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''24'' ';
p_v_string => ' @24 Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' @24 Change record deleted successfully' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number
);
SELECT lar.loan_number
,lar.loan_status
,lor.prc_type_code
,lor.cl_rec_status
FROM igf_sl_lor_all lor
,igf_sl_loans_all lar
WHERE lor.loan_id = lar.loan_id
AND lar.award_id = cp_n_award_id;
SELECT resp_r8.disb_date
,resp_r8.disb_gross_amt
,resp_r8.disb_hold_rel_ind
FROM igf_sl_cl_resp_r1_all resp_r1
,igf_sl_cl_resp_r8_all resp_r8
WHERE resp_r1.loan_number = cp_v_loan_number
AND resp_r1.cl_rec_status IN ('B','G')
AND resp_r1.prc_type_code IN ('GO','GP')
AND resp_r1.cl_version_code = 'RELEASE-4'
AND resp_r8.clrp1_id = resp_r1.clrp1_id
AND resp_r8.clrp8_id = cp_n_disb_num
ORDER BY resp_r1.clrp1_id DESC;
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb date : '||l_d_resp_disb_date ||
' new reference of disb date: '||l_d_new_disb_date ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '|| l_v_new_change_type_code ||
' change_field_code : '|| 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_resp_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_DATE' ,
x_change_record_type_txt => '09' ,
x_change_code_txt => 'B' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => TRUNC(l_d_resp_disb_date),
x_new_date => l_d_new_disb_date ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => TRUNC(l_d_resp_disb_date),
x_change_issue_code => 'PRE_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_DATE' ||
' Change record type: ' ||'09 - Disbursement Cancellation/Change' ||
' Change code : ' ||'B - Disbursement Date Change ' ||
' new disb date : ' || l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response disb date : ' ||l_d_resp_disb_date ||
' new reference of disb date: ' ||l_d_new_disb_date ||
' fund status : ' ||'Pre Disbursement change' ||
' change type code : ' || l_v_new_change_type_code ||
' change_field_code : ' || 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_new_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted Successfully' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response hold release ind: '||l_c_resp_hold_rel_ind ||
' new hold release ind : '||l_c_new_hold_rel_ind ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_HOLD_REL_IND'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_value_txt = :cp_c_resp_hold_rel_ind ' ||
'AND chdt.change_field_code = ''DISB_HOLD_REL_IND'' ' ||
'AND chdt.change_code_txt = ''E'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_HOLD_REL_IND' ,
x_change_record_type_txt => '09' ,
x_change_code_txt => 'E' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => l_c_resp_hold_rel_ind ,
x_new_value_txt => l_c_new_hold_rel_ind ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'PRE_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_HOLD_REL_IND' ||
' Change record type : ' ||'09 - Disbursement Cancellation/Change' ||
' Change code : ' ||'E - Disbursement Hold Release Change ' ||
' new hold release ind: ' || l_c_new_hold_rel_ind
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => l_c_new_hold_rel_ind ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => l_c_new_hold_rel_ind ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response hold release ind: '||l_c_resp_hold_rel_ind ||
' new hold release ind : '||l_c_new_hold_rel_ind ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '|| l_v_new_change_type_code ||
' change_field_code : '|| 'DISB_HOLD_REL_IND'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_value_txt = :cp_c_new_hold_rel_ind ' ||
'AND chdt.change_field_code = ''DISB_HOLD_REL_IND'' ' ||
'AND chdt.change_code_txt = ''E'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' new hold release ind: '||l_c_new_hold_rel_ind
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record to be deleted successfully'||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' new hold release ind: '||l_c_new_hold_rel_ind
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_resp_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''AI'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
p_v_string => ' Change record to be deleted' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number||
' Disb Number : ' ||l_n_disb_num ||
' change_code_txt : ' ||'AI'
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' loan number : ' ||l_v_loan_number||
' Disb Number : ' ||l_n_disb_num ||
' change_code_txt : ' ||'AI'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_resp_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.change_code_txt = :cp_change_code ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_AMOUNT' ,
x_change_record_type_txt => '09' ,
x_change_code_txt => l_v_change_code_txt ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => l_n_resp_disb_gross_amt ,
x_new_amt => l_n_new_disb_accepted_amt,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'PRE_DISB' ,
x_disbursement_cancel_date => l_d_disb_cancel_dt ,
x_disbursement_cancel_amt => l_n_disb_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_AMOUNT' ||
' Change record type : ' ||'09 - Disbursement Cancellation/Change' ||
' Change code : ' ||l_v_change_code_txt ||
' new disb Amount : ' ||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => l_d_disb_cancel_dt ,
x_disbursement_cancel_amt => l_n_disb_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => l_d_disb_cancel_dt ,
x_disbursement_cancel_amt => l_n_disb_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if existing change record is to be deleted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_new_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' Loan number : ' ||rec_c_igf_sl_clchsn_dtls.loan_number_txt ||
' Change send id: ' ||rec_c_igf_sl_clchsn_dtls.clchgsnd_id ||
' Change Code : ' ||rec_c_igf_sl_clchsn_dtls.change_code_txt
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted successfully ' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' Loan number : ' ||rec_c_igf_sl_clchsn_dtls.loan_number_txt ||
' Change send id: ' ||rec_c_igf_sl_clchsn_dtls.clchgsnd_id ||
' Change Code : ' ||rec_c_igf_sl_clchsn_dtls.change_code_txt
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_resp_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_AMOUNT' ,
x_change_record_type_txt => '09' ,
x_change_code_txt => 'C' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => l_n_resp_disb_gross_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'PRE_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'Y' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_AMOUNT' ||
' Change record type : ' ||'09 - Disbursement Cancellation/Change' ||
' Change code : ' ||'C - Disbursement Reinstatement ' ||
' new disb Amount : ' ||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_new_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.change_record_type_txt = ''09'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Amount: '||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record to be deleted successfully' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Amount: '||l_n_new_disb_accepted_amt
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb date : '||l_d_resp_disb_date ||
' new reference of disb date: '||l_d_new_disb_date ||
' fund status : '||'Pre Disbursement change' ||
' change type code : '|| l_v_new_change_type_code ||
' change_field_code : '|| 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_resp_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_DATE' ,
x_change_record_type_txt => '09' ,
x_change_code_txt => 'C' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_disb_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'PRE_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'Y' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_DATE' ||
' Change record type: ' ||'09 - Disbursement Cancellation/Change' ||
' Change code : ' ||'B - Disbursement Date Change ' ||
' new disb date : ' || l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response disb date : ' ||l_d_resp_disb_date ||
' new reference of disb date: ' ||l_d_new_disb_date ||
' fund status : ' ||'Pre Disbursement change' ||
' change type code : ' || l_v_new_change_type_code ||
' change_field_code : ' || 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_new_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''09'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted Successfully' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Post Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_resp_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_AMOUNT' ,
x_change_record_type_txt => '10' ,
x_change_code_txt => 'C' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => NULL ,
x_new_date => NULL ,
x_old_amt => l_n_resp_disb_gross_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'POST_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'Y' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_AMOUNT' ||
' Change record type: ' ||'10 - Disbursement Notification / Change' ||
' Change code : ' ||'C-Full or Partial Reinstatement' ||
' new disb Amount : ' ||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Post Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_new_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.change_record_type_txt = ''10'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' new disb Amount: ' ||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record to be deleted succesfully' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' new disb Amount: ' ||l_n_new_disb_accepted_amt
);
p_v_string => ' Verifying if existing change record is to be updated or inserted ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Post Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_resp_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_AMOUNT' ,
x_change_record_type_txt => '10' ,
x_change_code_txt => 'B' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_disb_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => l_n_resp_disb_gross_amt ,
x_new_amt => l_n_new_disb_accepted_amt,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'POST_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => 'Y' ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => l_v_fund_return_mthd_code,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Post Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_new_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.change_record_type_txt = ''10'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' new disb Amount: ' ||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record to be deleted succesfully' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' new disb Amount: ' ||l_n_new_disb_accepted_amt
);
p_v_string => ' Verifying if existing change record is to be updated or inserted ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Post Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_resp_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_AMOUNT' ,
x_change_record_type_txt => '10' ,
x_change_code_txt => 'A' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_disb_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => l_n_resp_disb_gross_amt ,
x_new_amt => l_n_new_disb_accepted_amt,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'POST_DISB' ,
x_disbursement_cancel_date => TRUNC(SYSDATE) ,
x_disbursement_cancel_amt => (l_n_resp_disb_gross_amt - l_n_new_disb_accepted_amt) ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => l_v_fund_return_mthd_code,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => TRUNC(SYSDATE) ,
x_disbursement_cancel_amt => (rec_c_igf_sl_clchsn_dtls.old_amt - l_n_new_disb_accepted_amt) ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => l_n_new_disb_accepted_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => TRUNC(SYSDATE) ,
x_disbursement_cancel_amt => (rec_c_igf_sl_clchsn_dtls.old_amt - l_n_new_disb_accepted_amt),
x_disbursement_revised_amt => l_n_new_disb_accepted_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb gross amount: '||l_n_resp_disb_gross_amt ||
' new disb accepted amount : '||l_n_new_disb_accepted_amt ||
' fund status : '||'Post Disbursement change' ||
' change type code : '||l_v_new_change_type_code ||
' change_field_code : '||'DISB_AMOUNT'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_amt = :cp_d_new_disb_amt ' ||
'AND chdt.change_field_code = ''DISB_AMOUNT'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.change_record_type_txt = ''10'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' new disb Amount: ' ||l_n_new_disb_accepted_amt
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record to be deleted succesfully' ||
' Award Id : ' ||l_n_award_id ||
' Disb Num : ' ||l_n_disb_num ||
' new disb Amount: ' ||l_n_new_disb_accepted_amt
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb date : '||l_d_resp_disb_date ||
' new reference of disb date: '||l_d_new_disb_date ||
' fund status : '||'Post Disbursement change' ||
' change type code : '|| l_v_new_change_type_code ||
' change_field_code : '|| 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_resp_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_DATE' ,
x_change_record_type_txt => '10' ,
x_change_code_txt => 'C' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_disb_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'POST_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'Y' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => NULL ,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_DATE' ||
' Change record type : ' ||'10 - Disbursement Notification / Change' ||
' Change code : ' ||'C - Full or Partial Reinstatement ' ||
' new disb date : ' || l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response disb date : ' ||l_d_resp_disb_date ||
' new reference of disb date: ' ||l_d_new_disb_date ||
' fund status : ' ||'Post Disbursement change' ||
' change type code : ' || l_v_new_change_type_code ||
' change_field_code : ' || 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_new_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''C'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted Successfully' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb date : '||l_d_resp_disb_date ||
' new reference of disb date: '||l_d_new_disb_date ||
' fund status : '||'Post Disbursement change' ||
' change type code : '|| l_v_new_change_type_code ||
' change_field_code : '|| 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_resp_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_DATE' ,
x_change_record_type_txt => '10' ,
x_change_code_txt => 'B' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_disb_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'POST_DISB' ,
x_disbursement_cancel_date => NULL ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => 'Y' ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => l_v_fund_return_mthd_code,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_DATE' ||
' Change record type : ' ||'10 - Disbursement Notification / Change' ||
' Change code : ' ||'B - Full or Partial Reissue ' ||
' new disb date : ' || l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response disb date : ' ||l_d_resp_disb_date ||
' new reference of disb date: ' ||l_d_new_disb_date ||
' fund status : ' ||'Post Disbursement change' ||
' change type code : ' || l_v_new_change_type_code ||
' change_field_code : ' || 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_new_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''B'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted Successfully' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
p_v_string => ' Verifying if existing change record is to be updated or inserted '||
' cl version : '||l_n_cl_version ||
' loan status : '||l_v_loan_status ||
' Processing Type : '||l_v_prc_type_code ||
' Loan Record Status : '||l_c_cl_rec_status ||
' response disb date : '||l_d_resp_disb_date ||
' new reference of disb date: '||l_d_new_disb_date ||
' fund status : '||'Post Disbursement change' ||
' change type code : '|| l_v_new_change_type_code ||
' change_field_code : '|| 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_resp_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
igf_sl_clchsn_dtls_pkg.insert_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => l_n_clchgsnd_id ,
x_award_id => l_n_award_id ,
x_loan_number_txt => l_v_loan_number ,
x_cl_version_code => l_n_cl_version ,
x_change_field_code => 'DISB_DATE' ,
x_change_record_type_txt => '10' ,
x_change_code_txt => 'A' ,
x_status_code => 'R' ,
x_status_date => TRUNC(SYSDATE) ,
x_response_status_code => NULL ,
x_old_value_txt => NULL ,
x_new_value_txt => NULL ,
x_old_date => l_d_resp_disb_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => NULL ,
x_new_amt => NULL ,
x_disbursement_number => l_n_disb_num ,
x_disbursement_date => l_d_resp_disb_date ,
x_change_issue_code => 'POST_DISB' ,
x_disbursement_cancel_date => l_d_new_disb_date ,
x_disbursement_cancel_amt => NULL ,
x_disbursement_revised_amt => l_n_new_disb_accepted_amt,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => NULL ,
x_disbursement_reinst_code => 'N' ,
x_disbursement_return_amt => NULL ,
x_disbursement_return_date => NULL ,
x_disbursement_return_code => l_v_fund_return_mthd_code,
x_post_with_disb_return_amt => NULL ,
x_post_with_disb_return_date => NULL ,
x_post_with_disb_return_code => NULL ,
x_prev_with_disb_return_amt => NULL ,
x_prev_with_disb_return_date => NULL ,
x_school_use_txt => NULL ,
x_lender_use_txt => NULL ,
x_guarantor_use_txt => NULL ,
x_validation_edit_txt => NULL ,
x_send_record_txt => NULL
);
p_v_string => ' Change record is updated ' ||
' Change_field_code : ' ||'DISB_DATE' ||
' Change record type : ' ||'10 - Disbursement Notification / Change' ||
' Change code : ' ||'A - Full or Partial Cancellation ' ||
' new disb date : ' || l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'R' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => TRUNC(SYSDATE) ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => NULL ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => l_v_rowid ,
x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
x_new_date => l_d_new_disb_date ,
x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => TRUNC(SYSDATE) ,
x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => l_d_new_disb_date ,
x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => fnd_message.get ,
x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' updated the status of change send record to Not Ready to Send'
);
p_v_string => ' Verifying if change record is to be deleted or not ' ||
' cl version : ' ||l_n_cl_version ||
' loan status : ' ||l_v_loan_status ||
' Processing Type : ' ||l_v_prc_type_code ||
' Loan Record Status : ' ||l_c_cl_rec_status ||
' response disb date : ' ||l_d_resp_disb_date ||
' new reference of disb date: ' ||l_d_new_disb_date ||
' fund status : ' ||'Post Disbursement change' ||
' change type code : ' || l_v_new_change_type_code ||
' change_field_code : ' || 'DISB_DATE'
);
l_v_sqlstmt := 'SELECT chdt.ROWID row_id ' ||
'FROM igf_sl_clchsn_dtls chdt ' ||
'WHERE chdt.award_id = :cp_n_award_id ' ||
'AND chdt.disbursement_number = :cp_n_dib_num ' ||
'AND chdt.old_date = :cp_d_new_disb_dt ' ||
'AND chdt.change_field_code = ''DISB_DATE'' ' ||
'AND chdt.change_code_txt = ''A'' ' ||
'AND chdt.status_code IN (''R'',''N'',''D'') ' ||
'AND chdt.change_record_type_txt = ''10'' ';
p_v_string => ' Change record to be deleted ' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => l_v_rowid);
p_v_string => ' Change record deleted Successfully' ||
' Award Id : '||l_n_award_id ||
' Disb Num : '||l_n_disb_num ||
' New disb Date: '||l_d_new_disb_date
);
SELECT chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE rowid = p_rowid;