[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
while calling insert_row of IGS_PE_PERS_ENCUMB_PKG
pmarada 26-jul-2004 Bug 3792800, Added code to bypass the holds apply validation in finp_apply_holds procedure
pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
Added procedure finp_auto_release_holds(), modified validate_holds() and all its call-outs
Modified validate_param - removed calls to igs_pe_gen_001.get_hold_auth.
pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes in person id group views
Replaced all occurrences of igs_pe_persid_group_v and igs_pe_prsid_grp_mem_v
with igs_pe_persid_group and igs_pe_prsid_grp_mem respectively
pathipat 05-May-2003 Enh 2831569 - Commercial Receivables Build
Modified finp_apply_holds() and finp_release_holds_main() - Added check for manage_accounts
vvutukur 05-Mar-2003 Bug#2824994.Modified procedure finp_apply_holds,function validate_holds(used in releasing holds),holds_balance.
pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
Modifications according to FI206_TD_SWS_Additional_Security_for_Holds_s1a.doc
Modified cursor c_person - selected from igs_pe_person_base_v instead of igs_pe_person
Changed declaration of local variable person_name appropriately.
ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
SYKRISHn 03-JAN-2002 Bug 2684895 --Procedure finp_apply_holds and finp_release_holds_main
Logging Person Group Cd instead of person group id.
SYKRISHN 31DEC2002 Bug 2676524 - Procedure finp_apply_holds
Derived the person number for the parameter p_auth_person_id
to display in the concurrent log file instead of the P_auth_person_id which
was logged earlier.
smadathi 20-dec-2002 Enh. Bug 2566615. Removed the references of obsoleted table IGS_FI_HOLD_PLN_LNS and
incorporated changes suggested as per FICR102 TD. Removed lookup_desc function
agairola 03-Dec-2002 Bug No: 2584741 As part of the Deposits Build, modified the cursor c_credit_amount to exclude
credits of Credit Class Enrolment Deposit and Other Deposit
pathipat 04-OCT-2002 Enh Bug:2562745 -- Reassess Balances build
1. Added check in finp_apply_holds() and in finp_release_holds_main() to check if
holds conversion process is running before continuing with further processing
2. Added check in validate_param() to check if active balance rule has been defined for the
balance type of HOLDS.
3. Also added in the same function, check if the process start date is not later than the
last_conversion_date of the balance rule when the hold plan name is at 'Account' level.
4. In validate_holds(), removed insertion of balance_amount into the igs_fi_person_holds
table as the column is being obsoleted. Added cursor c_bal_amount to obtain holds balance
from igs_fi_balances (in place of balance amount from igs_fi_person_holds)
5. Added parameter balance_rule_id in calls to check_exclusion_rules()
6. Removed igs_ge_date.igsdate(p_process_start_date) and replaced with just
p_process_Start_date. similarly for process_end_date also.
pkpatel 30-SEP-2002 Bug No: 2600842
Added the parameter auth_resp_id in the call to the procedures of TBH igs_pe_pers_encumb_pkg
vchappid 07-Jun-2002 Bug 2392486#, Calculation of the holds balance incase the Holds plan is at subaccount
is corrected , Holds Balance should be added only when the balance record is found in
the balances table
SYkrishn 30/APR/2002 in function validate_param
Changes in curor c_fee_type to compare ci ststu with system fee structure ststua
Bug 2348883
SYkrishn 03-APR-2002 Changes according to Build 2293676 - Planned Credits Functionality introduced.
vvutukur 28-02-2002 Modified the cursor c_person by selecting from igs_pe_person
instead of igs_fi_parties_v.for bug:2238362(reverting back the earlier fix).
vvutukur 27-02-2002 Modified cursor c_person by selecting from igs_fi_parties_v
instead of igs_pe_person for bug:2238362.
***************************************************************/
--Skip exception used to skip a record FROM the cursor based on the condition
skip EXCEPTION;
SELECT person_number,
full_name
FROM igs_pe_person_base_v
WHERE person_id = l_person_id;
SELECT hold_plan_level,
hold_type,
threshold_amount,
threshold_percent,
fee_type,
offset_days ,
payment_plan_threshold_amt ,
payment_plan_threshold_pcent
FROM igs_fi_hold_plan
WHERE hold_plan_name = p_hold_plan_name;
SELECT holds_balance
FROM igs_fi_balances
WHERE party_id = p_person_id
AND holds_balance IS NOT NULL
AND TRUNC(balance_date) <= TRUNC(p_process_start_date)
ORDER BY balance_date desc;
SELECT crd.credit_id,
crd.amount,
crd.effective_date
FROM igs_fi_credits crd,
igs_fi_cr_types crt
WHERE TRUNC(crd.effective_date) between (TRUNC(p_process_start_date) + 1) AND TRUNC(l_payment_due_date)
AND crd.status = 'CLEARED'
AND crd.party_id = p_person_id
AND crd.credit_type_id = crt.credit_type_id
AND crt.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT')
ORDER BY crd.credit_id ;
SELECT invoice_id,
invoice_amount amount,
invoice_amount_due ,
invoice_creation_date
FROM igs_fi_inv_int inv
WHERE fee_type = l_fee_type
AND fee_cal_type = p_fee_cal_type
AND fee_ci_sequence_number = p_fee_ci_sequence_number
AND TRUNC(invoice_creation_date) <= TRUNC(p_process_start_date)
AND person_id = p_person_id
AND NOT EXISTS (SELECT 'X'
FROM igs_fi_inv_wav_det
WHERE invoice_id = inv.invoice_id
AND balance_type = 'HOLDS'
AND (
(
TRUNC(end_dt) IS NOT NULL AND
TRUNC(p_process_start_date) BETWEEN TRUNC(start_dt) AND TRUNC(end_dt)
)
OR
(TRUNC(p_process_start_date) >= TRUNC(start_dt) AND TRUNC(end_dt) is null)
)
);
SELECT SUM(installment_amt) installment_amt,
SUM(due_amt) due_amt,
MAX(due_date) due_date
FROM igs_fi_pp_instlmnts
WHERE student_plan_id = cp_n_student_plan_id
AND due_date <= cp_d_d_pay_det_date;
SELECT NVL(SUM(chg.invoice_amount), 0)
FROM igs_fi_inv_int_all chg,
igs_fi_inv_wav_det wav
WHERE person_id = cp_n_person_id
AND chg.invoice_id = wav.invoice_id
AND wav.balance_type = 'HOLDS'
AND cp_d_process_start_date BETWEEN TRUNC(start_dt) AND NVL(TRUNC(end_dt), cp_d_process_start_date);
sapanigr 04-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_person_holds
are now rounded off to currency precision
svuppala 17-AUG-2005 Bug 4557933 - Unable to remove holds that were put on with concurrent manager
Passing global values to AUTHORISING_PERSON_ID and AUTH_RESP_ID from SWS Holds API
while calling insert_row of IGS_PE_PERS_ENCUMB_PKG
smadathi 28-Aug-2003 Enh Bug 3045007. Added 2 new IN parameter - p_n_student_plan_id and
p_d_last_inst_due_date
pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
Added param x_release_credit_id to TBH calls of igs_fi_person_holds
pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
Removed parameter p_auth_person_id. Passed Null to authorising_person_id
in the call to igs_pe_pers_encumb_pkg.insert_row
ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
pathipat 04-OCT-2002 Enh Bug: 2562745 -- obsoleted column balance_amount from igs_fi_person_holds
Removed column balance_amount from call to igs_fi_person_holds_pkg.insert_row()
pkpatel 04-OCT-2002 Bug No: 2600842
Added the parameter auth_resp_id in the call to TBH igs_pe_pers_encumb_pkg
***************************************************************/
--to check if reocrd exist in IGS_PE_PERS_ENCUMB for the passed start date
CURSOR c_hold_exist
IS
SELECT *
FROM igs_pe_pers_encumb
WHERE person_id = p_person_id
AND encumbrance_type = p_hold_type
AND TRUNC(start_dt) = TRUNC(p_hold_start_date);
igs_pe_pers_encumb_pkg.insert_row (
X_Mode => 'R',
X_RowId => l_rowid,
X_Person_Id => p_person_id,
X_Encumbrance_Type => p_hold_type,
X_CAL_TYPE => null,
X_SEQUENCE_NUMBER => null,
X_Start_Dt => p_hold_start_date,
X_Expiry_Dt => null,
X_Authorising_Person_Id => g_n_person_id,
X_Comments => null,
X_Spo_Course_Cd => null,
X_Spo_Sequence_Number => null,
x_auth_resp_id => g_n_resp_id,
x_external_reference => null); -- should always be null when passed from internal system
igs_fi_person_holds_pkg.insert_row(
x_Mode => 'R',
x_RowId => l_rowid,
x_person_id => p_person_id,
x_hold_plan_name => p_hold_plan_name ,
x_hold_type => p_hold_type ,
x_hold_start_dt => p_hold_start_date,
x_process_start_dt => p_process_start_dt,
x_process_end_dt => p_process_end_dt,
x_offset_days => p_offset_days,
x_past_due_amount => igs_fi_gen_gl.get_formatted_amount(P_holds_final_balance),
x_fee_cal_type => NULL,
x_fee_ci_sequence_number => NULL,
x_fee_type_invoice_amount => NULL,
x_release_credit_id => NULL,
x_student_plan_id => p_n_student_plan_id,
x_last_instlmnt_due_date => p_d_last_inst_due_date
);
igs_fi_person_holds_pkg.insert_row(
x_Mode => 'R',
x_Rowid => l_rowid,
x_person_id => p_person_id,
x_hold_plan_name => p_hold_plan_name ,
x_hold_type => p_hold_type ,
x_hold_start_dt => p_hold_start_date,
x_process_start_dt => p_process_start_dt,
x_process_end_dt => p_process_start_dt,
x_offset_days => NULL,
x_past_due_amount => igs_fi_gen_gl.get_formatted_amount(P_holds_final_balance),
x_fee_cal_type => P_fee_cal_type ,
x_fee_ci_sequence_number => P_fee_ci_sequence_number ,
x_fee_type_invoice_amount => igs_fi_gen_gl.get_formatted_amount(p_holds_charges),
x_release_credit_id => NULL,
x_student_plan_id => p_n_student_plan_id,
x_last_instlmnt_due_date => p_d_last_inst_due_date
);
SELECT group_id
FROM igs_pe_persid_group
WHERE group_id = p_person_id_group
AND TRUNC(create_dt) <= TRUNC(SYSDATE)
AND closed_ind = 'N';
SELECT hold_plan_name,
hold_plan_level,
offset_days
FROM igs_fi_hold_plan
WHERE hold_plan_name = p_hold_plan_name
AND closed_ind = 'N';
SELECT fcc.fee_cal_type
FROM igs_fi_f_typ_ca_inst fcc,
igs_fi_fee_str_stat fss
WHERE fcc.fee_type_ci_status = fss.fee_structure_status
AND fss.s_fee_structure_status = 'ACTIVE'
AND fcc.fee_cal_type = p_fee_cal_type
AND fcc.fee_ci_sequence_number = p_fee_ci_sequence_number ;
SELECT person_id
FROM igs_pe_prsid_grp_mem
WHERE (TRUNC(end_date) IS NULL OR TRUNC(end_date) >= TRUNC(SYSDATE))
AND group_id = p_person_id_group;
SELECT DISTINCT person_id
FROM igs_fi_inv_int;
SELECT group_id
FROM igs_pe_persid_group
WHERE group_id = p_person_id_group
AND TRUNC(create_dt) <= TRUNC(SYSDATE)
AND closed_ind = 'N';
SELECT hold_plan_name
FROM igs_fi_hold_plan
WHERE hold_plan_name = p_hold_plan_name;
Replaced call to igs_pe_pers_encumb_pkg.update_row with call to igs_pe_gen_001.release_hold
ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
smadathi 20-dec-2002 Enh. Bug 2566615. Removed the references of obsoleted table IGS_FI_HOLD_PLN_LNS and
incorporated changes suggested as per FICR102 TD
agairola 03-Dec-2002 Bug No: 2584741 As part of the Deposits Build, modified the cursor c_credit_amount to exclude
credits of Credit Class Enrolment Deposit and Other Deposit
pathipat 04-OCT-2002 Enh Bug: 2562745 -- Removed selection of balance_amount from cursor c_hold_type
Added cursor c_bal_amount to obtain holds balance amount from igs_fi_balances
Changed type of p_release and l_release_ind to PLS_INTEGER instead of NUMBER
pkpatel 04-OCT-2002 Bug No: 2600842
Added the parameter auth_resp_id in the call to TBH igs_pe_pers_encumb_pkg
sarakshi 23-sep-2002 Enh#2564643,removed the reference of subaccount from this function as mentioned in the TD
sykrishn 07-APR-2002 Introduced planned credits functionality as per SFCR018 DLD
2293676 - Planned Credits are also considered for release of holds along with
the actual credits . (suba account level holds plan)
***************************************************************/
CURSOR c_hold_type IS
SELECT a.person_id person_id,
a.encumbrance_type encumbrance_type,
a.start_dt start_dt,
c.rowid row_id,
a.comments comments,
a.expiry_dt expiry_dt,
a.authorising_person_id authorising_person_id,
a.spo_course_cd spo_course_cd,
a.spo_sequence_number spo_sequence_number,
a.cal_type cal_type,
a.sequence_number sequence_number ,
c.hold_plan_name hold_plan_name,
c.process_start_dt process_start_dt ,
c.fee_type_invoice_amount fee_type_invoice_amount,
c.fee_ci_sequence_number fee_ci_sequence_number,
c.fee_cal_type fee_cal_type,
c.hold_type hold_type,
a.auth_resp_id auth_resp_id,
a.external_reference external_reference,
c.hold_start_dt hold_start_dt,
c.process_end_dt process_end_dt,
c.offset_days offset_days,
c.past_due_amount past_due_amount,
hplan.hold_plan_level hold_plan_level,
hplan.threshold_amount threshold_amount,
hplan.threshold_percent threshold_percent,
hplan.payment_plan_threshold_amt payment_plan_threshold_amt ,
hplan.payment_plan_threshold_pcent payment_plan_threshold_pcent,
c.student_plan_id student_plan_id,
c.last_instlmnt_due_date last_instlmnt_due_date
FROM igs_pe_pers_encumb a,
igs_fi_person_holds c,
igs_fi_hold_plan hplan
WHERE (a.person_id = p_person_id OR p_person_id IS NULL)
AND a.start_dt <= TRUNC(SYSDATE)
AND (a.expiry_dt IS NULL OR TRUNC(SYSDATE) < a.expiry_dt )
AND c.hold_plan_name = hplan.hold_plan_name
AND c.person_id = a.person_id
AND c.hold_start_dt = a.start_dt
AND c.hold_type = a.encumbrance_type
AND (c.hold_plan_name = p_hold_plan_name OR p_hold_plan_name is null)
AND (hplan.hold_plan_level = p_hold_plan_level OR p_hold_plan_level IS NULL);
SELECT crd.credit_id,
crd.amount,
crd.effective_date
FROM igs_fi_credits crd,
igs_fi_cr_types crt
WHERE TRUNC(crd.effective_date) between
(TRUNC(l_process_start_date) + 1 )and TRUNC(SYSDATE)
AND crd.status = 'CLEARED'
AND crd.party_id = p_person_id
AND crd.credit_type_id = crt.credit_type_id
AND crt.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT')
ORDER BY crd.credit_id;
SELECT fee_type
FROM igs_fi_hold_plan fhpl
WHERE fhpl.hold_plan_name = cp_c_hold_plan_name;
SELECT invoice_id,
invoice_amount_due,
invoice_creation_date,
fee_type
FROM igs_fi_inv_int inv
WHERE fee_type = cp_c_fee_type
AND fee_cal_type = l_fee_cal_type
AND fee_ci_sequence_number = l_fee_ci_sequence_number
AND TRUNC(invoice_creation_date) <= TRUNC(l_process_start_dt)
AND person_id = p_person_id
AND NVL(invoice_amount_due,0) > 0
AND NOT EXISTS ( SELECT 'X'
FROM igs_fi_inv_wav_det
WHERE invoice_id = inv.invoice_id
AND balance_type = 'HOLDS'
AND ( (TRUNC(end_dt) IS NOT NULL AND TRUNC(SYSDATE) BETWEEN TRUNC(start_dt) AND TRUNC(end_dt))
OR
(TRUNC(SYSDATE) >= TRUNC(start_dt) AND TRUNC(end_dt) IS NULL)
)
);
SELECT holds_balance
FROM igs_fi_balances
WHERE party_id = cp_person_id
AND holds_balance IS NOT NULL
AND TRUNC(balance_date) <= TRUNC(cp_process_start_dt)
ORDER BY balance_date DESC;
SELECT SUM(installment_amt) installment_amt,
SUM(due_amt) due_amt
FROM igs_fi_pp_instlmnts
WHERE student_plan_id = cp_n_student_plan_id
AND due_date <= cp_d_inst_due_date;
SELECT NVL(SUM(chg.invoice_amount), 0)
FROM igs_fi_inv_int_all chg,
igs_fi_inv_wav_det wav
WHERE person_id = cp_n_person_id
AND chg.invoice_id = wav.invoice_id
AND wav.balance_type = 'HOLDS'
AND cp_d_process_start_date BETWEEN TRUNC(start_dt) AND NVL(TRUNC(end_dt), cp_d_process_start_date);
igs_fi_person_holds_pkg.update_row ( x_rowid => l_hold_type_rec.row_id,
x_person_id => l_hold_type_rec.person_id,
x_hold_plan_name => l_hold_type_rec.hold_plan_name,
x_hold_type => l_hold_type_rec.hold_type,
x_hold_start_dt => l_hold_type_rec.hold_start_dt,
x_process_start_dt => l_hold_type_rec.process_start_dt,
x_process_end_dt => l_hold_type_rec.process_end_dt,
x_offset_days => l_hold_type_rec.offset_days,
x_past_due_amount => l_hold_type_rec.past_due_amount,
x_fee_cal_type => l_hold_type_rec.fee_cal_type,
x_fee_ci_sequence_number => l_hold_type_rec.fee_ci_sequence_number,
x_fee_type_invoice_amount => l_hold_type_rec.fee_type_invoice_amount,
x_mode => 'R',
x_release_credit_id => p_release_credit_id ,
x_student_plan_id => l_hold_type_rec.student_plan_id,
x_last_instlmnt_due_date => l_hold_type_rec.last_instlmnt_due_date
);
SELECT person_id
FROM igs_pe_prsid_grp_mem
WHERE (TRUNC(end_date) IS NULL OR TRUNC(end_date) >= TRUNC(SYSDATE))
AND group_id = p_person_id_group;
SELECT person_id, person_number
FROM igs_pe_person_base_v per
WHERE EXISTS ( SELECT '1'
FROM igs_fi_person_holds hold
WHERE hold.person_id = per.person_id);
SELECT 'X'
FROM igs_pe_pers_encumb pe_encmb,
igs_fi_person_holds fi_holds,
igs_fi_hold_plan hplan
WHERE pe_encmb.person_id = p_person_id
AND pe_encmb.start_dt <= TRUNC(SYSDATE)
AND (pe_encmb.expiry_dt IS NULL OR TRUNC(SYSDATE) < pe_encmb.expiry_dt)
AND fi_holds.person_id = pe_encmb.person_id
AND fi_holds.hold_start_dt = pe_encmb.start_dt
AND fi_holds.hold_type = pe_encmb.encumbrance_type
AND fi_holds.hold_plan_name = hplan.hold_plan_name
AND hplan.hold_plan_level = p_hold_plan_level
AND ROWNUM < 2;
SELECT credit_number
FROM igs_fi_credits_all
WHERE credit_id = cp_release_credit_id;