[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rnirwani 13-Sep-2004 changed cursor cur_inst to not consider logically deleted records Bug# 3885804
********************************************************************************************** */
g_ind_yes CONSTANT VARCHAR2(1) := 'Y';
SELECT refund_cr_gl_ccid
FROM igs_fi_control;
SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
SELECT amount_high,
amount_low
FROM igs_fi_refund_setup
WHERE TRUNC(sysdate) BETWEEN TRUNC(START_DATE) AND TRUNC(NVL(END_DATE,sysdate));
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refunds
is now rounded off to currency precision
pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
Added check for manage_accounts - call to chk_manage_account()
smadathi 21-Feb-2002 Enh. Bug 2747329. Added validation for refund_cr_gl_ccid
shtatiko 28-JAN-2003 Bug# 2734316, changed message name to IGS_FI_PARAMETER_NULL from IGS_FI_INVALID_PARM
smadathi 15-Jan-2003 Bug 2722096. Incorporated logging of error message raised by the mass_apply
process call.
vvutukur 19-Nov-2002 Enh#2584986.Code added to validate the mandatory newly added IN parameter p_d_gl_date.
Passed this p_d_gl_date parameter to the calls to i)igs_fi_prc_appl.mass_apply,
ii)igs_fi_refunds_pkg.insert_row iii)igs_fi_gen_007.create_application.
shtatiko 24-Sep-2002 Bug No: 2564643 Removed references to Subaccount_id.
agairola 13-Jun-2002 Bug No: 2408221 For the Application Hierarchy, added the check for effective
start and end date
agairola 11-Jun-2002 Bug No: 2408221 added the validation for Application Hierarchy
********************************************************************************************** */
-- Cursor for validating the credits
CURSOR cur_val_crd(cp_credit_id IN NUMBER,
cp_status IN VARCHAR2) IS
SELECT crd.party_id,
crd.credit_type_id,
crd.effective_date
FROM igs_fi_credits crd,
igs_fi_cr_types crt
WHERE crd.credit_id = cp_credit_id
AND crd.credit_type_id = crt.credit_type_id
AND crd.status = cp_status
AND NVL(crt.refund_allowed,g_ind_no) = g_ind_yes;
SELECT CRD.PARTY_ID,
CRD.UNAPPLIED_AMOUNT,
CRD.FEE_CAL_TYPE,
CRD.FEE_CI_SEQUENCE_NUMBER
FROM igs_fi_credits crd
WHERE crd.credit_id = p_credit_id
AND crd.status = cp_status
FOR UPDATE NOWAIT;
SELECT 'x'
FROM igs_fi_a_hierarchies
WHERE credit_type_id = cp_credit_type_id
AND cp_effective_date BETWEEN effective_start_date AND NVL(effective_end_date,cp_effective_date);
igs_fi_refunds_pkg.insert_row(X_ROWID => l_rowid,
X_REFUND_ID => l_refund_id,
X_VOUCHER_DATE => TRUNC(sysdate),
X_PERSON_ID => l_crd_rec.party_id,
X_PAY_PERSON_ID => l_payee_id,
X_DR_GL_CCID => l_dr_gl_ccid,
X_CR_GL_CCID => l_cr_gl_ccid,
X_DR_ACCOUNT_CD => l_dr_account_cd,
X_CR_ACCOUNT_CD => l_cr_account_cd,
X_REFUND_AMOUNT => igs_fi_gen_gl.get_formatted_amount(l_crd_rec.unapplied_amount),
X_FEE_TYPE => l_fee_type,
X_FEE_CAL_TYPE => l_fee_cal_type,
X_FEE_CI_SEQUENCE_NUMBER => l_fee_ci_sequence_number,
X_SOURCE_REFUND_ID => g_null,
X_INVOICE_ID => l_invoice_id,
X_TRANSFER_STATUS => g_todo,
X_REVERSAL_IND => g_ind_no,
X_REASON => l_reason,
X_ATTRIBUTE_CATEGORY => g_null,
X_ATTRIBUTE1 => g_null,
X_ATTRIBUTE2 => g_null,
X_ATTRIBUTE3 => g_null,
X_ATTRIBUTE4 => g_null,
X_ATTRIBUTE5 => g_null,
X_ATTRIBUTE6 => g_null,
X_ATTRIBUTE7 => g_null,
X_ATTRIBUTE8 => g_null,
X_ATTRIBUTE9 => g_null,
X_ATTRIBUTE10 => g_null,
X_ATTRIBUTE11 => g_null,
X_ATTRIBUTE12 => g_null,
X_ATTRIBUTE13 => g_null,
X_ATTRIBUTE14 => g_null,
X_ATTRIBUTE15 => g_null,
X_ATTRIBUTE16 => g_null,
X_ATTRIBUTE17 => g_null,
X_ATTRIBUTE18 => g_null,
X_ATTRIBUTE19 => g_null,
X_ATTRIBUTE20 => g_null,
X_GL_DATE => p_d_gl_date,
X_REVERSAL_GL_DATE => NULL);
sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refunds
is now rounded off to currency precision
pmarada 19-jul-04 Bug 3775620, Logging credit number in log file, if there is no fee period for the refund fee type.
smadathi 25-AUG-2003 Enh. Bug 3045007. Added business logic to check if the Person for which the Refunds is being processed
is on an Active Payment Plan
pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
Added check for manage_accounts - call to chk_manage_account()
smadathi 21-Feb-2002 Bug 2747329. Cursor select cur_pers was modified to replace table igs_pe_persid_group reference to
igs_pe_all_persid_group_v. Added validation for refund_cr_gl_ccid.
agairola 23-Dec-02 Bug 2718029: Modified the code in Process_batch to remove the app_exception
.raise_exception for parameter validation and replaced it by setting retcode=2
and RETURN
vvutukur 19-Nov-2002 Enh#2584986.Added mandatory IN parameter p_d_gl_date.Code added to validate GL Date.
Modified the calls to igs_fi_refunds_pkg.insert_row and igs_fi_gen_007.create_application to include new parameter p_d_gl_date also. Modified IGS_GE_UNHANDLED_EXCEP message to
IGS_GE_UNHANDLED_EXCEPTION.
vvutukur 28-Jun-2002 modified IGS_FI_TOL_LIM_DEFIED message token from REF_AMNT to
REF_AMOUNT.bug#2427999.
sarakshi 14-Jun-02 bug#2400617,the check of refunds not be processed if student is studying
at another instution or the student is inactive should be there irrespective
of the financial aid student or not.
vchappid 13-Jun-02 Bug#2411529, Incorrectly used message name has been modified
agairola 15-May-02 For the bug 2373855, following modifications were done
1. Removed the CLOSE cur_spnsr statement from inside IF Cur_spnsr%FOUND
as it was already happening outside the %FOUND statements
2. Added a new variable l_hld_exsts as the process was displaying that
Holds exist even though there were no holds because the variable
l_process_party was set to FALSE. Hence added the additional check.
********************************************************************************************** */
-- Cursor for validating the person id
CURSOR cur_val_party(cp_party_id IN igs_fi_parties_v.person_id%TYPE) IS
SELECT person_number
FROM igs_fi_parties_v pe
WHERE pe.person_id = cp_party_id;
SELECT group_cd
FROM igs_pe_all_persid_group_v
WHERE group_id=cp_person_id_grp
AND closed_ind <> g_ind_yes;
SELECT party_id person_id
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT DISTINCT crd.party_id person_id
FROM igs_fi_credits crd,
igs_fi_cr_types crt
WHERE crd.credit_type_id = crt.credit_type_id
AND crd.status = g_cleared
AND NVL(crt.refund_allowed,g_ind_no) = g_ind_yes
AND crd.unapplied_amount > 0;
SELECT 'x'
FROM igf_aw_fund_mast fund,
igf_aw_fund_cat fcat
WHERE fund.fund_code = fcat.fund_code
AND fcat.sys_fund_type = g_sponsor
AND fund.party_id = cp_person_id;
SELECT crd.*, crt.credit_class
FROM igs_fi_credits crd,
igs_fi_cr_types crt,
igs_fi_cr_activities cra
WHERE crd.credit_type_id = crt.credit_type_id
AND crd.status = cp_status
AND crd.party_id = cp_person_id
AND NVL(crt.refund_allowed,g_ind_no) = g_ind_yes
AND crt.credit_class <> 'SPNSP'
AND crd.unapplied_amount > 0
AND cra.credit_id = crd.credit_id;
SELECT 'x'
FROM igs_en_stdnt_ps_att spa,
igs_en_intm_types intm,
igs_en_stdnt_ps_intm spi
WHERE spa.person_id = cp_person_id
AND spa.person_id = spi.person_id
AND spa.course_cd = spi.course_cd
AND spa.course_attempt_status = cp_crs_stat
AND spi.intermission_type = intm.intermission_type
AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND intm.study_antr_inst_ind = g_ind_yes;
SELECT 'x'
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id
AND course_attempt_status IN ('INACTIVE','ENROLLED');
SELECT cal_type||' '||to_char(start_dt)||' '||to_char(end_dt) fee_prd
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_num;
SELECT invoice_number
FROM igs_fi_inv_int
WHERE invoice_id = cp_invoice_id;
refund_calc.delete;
refund_fee_prd.delete;
refund_calc.DELETE;
refund_fee_prd.DELETE;
refund_fee_prd.DELETE;
igs_fi_refunds_pkg.insert_row(X_ROWID => l_rowid,
X_REFUND_ID => l_refund_id,
X_VOUCHER_DATE => TRUNC(sysdate),
X_PERSON_ID => l_n_party_id,
X_PAY_PERSON_ID => l_n_party_id,
X_DR_GL_CCID => l_dr_gl_ccid,
X_CR_GL_CCID => l_cr_gl_ccid,
X_DR_ACCOUNT_CD => l_dr_account_cd,
X_CR_ACCOUNT_CD => l_cr_account_cd,
X_REFUND_AMOUNT => l_rfnd_amnt,
X_FEE_TYPE => l_fee_type,
X_FEE_CAL_TYPE => l_fee_cal_type,
X_FEE_CI_SEQUENCE_NUMBER => l_fee_ci_sequence_number,
X_SOURCE_REFUND_ID => g_null,
X_INVOICE_ID => l_invoice_id,
X_TRANSFER_STATUS => g_todo,
X_REVERSAL_IND => g_ind_no,
X_REASON => l_reason,
X_ATTRIBUTE_CATEGORY => g_null,
X_ATTRIBUTE1 => g_null,
X_ATTRIBUTE2 => g_null,
X_ATTRIBUTE3 => g_null,
X_ATTRIBUTE4 => g_null,
X_ATTRIBUTE5 => g_null,
X_ATTRIBUTE6 => g_null,
X_ATTRIBUTE7 => g_null,
X_ATTRIBUTE8 => g_null,
X_ATTRIBUTE9 => g_null,
X_ATTRIBUTE10 => g_null,
X_ATTRIBUTE11 => g_null,
X_ATTRIBUTE12 => g_null,
X_ATTRIBUTE13 => g_null,
X_ATTRIBUTE14 => g_null,
X_ATTRIBUTE15 => g_null,
X_ATTRIBUTE16 => g_null,
X_ATTRIBUTE17 => g_null,
X_ATTRIBUTE18 => g_null,
X_ATTRIBUTE19 => g_null,
X_ATTRIBUTE20 => g_null,
X_GL_DATE => l_d_gl_date,
X_REVERSAL_GL_DATE => NULL);