[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
igs_ss_enr_details.drop_selected_units as a part of technical impact.Bug#2686793
| shtatiko 03-sep-2002 Enh# 2562745 Modified validate_input_parameters|
| modified drop_disc_sua_non_payment. |
| vvutukur 23-Sep-2002 Enh#2564643.Modified drop_disc_sua_non_payment.|
| Removed DEFAULT clause from package body. |
| vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name |
| has been modified |
SYKRISHN 30_APR-2002 Bug 2348883- Modified cursor cur_fee_ci
to compare fee type ci status with the system status
and not with the user entered status - Using Fee Structure status
In function validate_input_parameters
| vvutukur 23-apr-2002 modified the log file to show meanings & |
| descriptions for person group,balance type|
| and discontinuation reason code , test run|
| schodava 30-Jan-2002 Enh # 2187247 |
| Modified PROCEDURE |
| drop_disc_sua_non_payment |
| vvutukur 20-dec-2001 Created the file for Unit Drop for Non |
| Payment Build: Bug # 2153205 |
*=======================================================================*/
g_fee_type_ci_status CONSTANT VARCHAR2(10) := 'ACTIVE';
SELECT description
-- Bug#5018036: Replaced igs_en_dcnt_reasoncd_v by igs_en_dcnt_reasoncd_all
FROM igs_en_dcnt_reasoncd_all
WHERE discontinuation_reason_cd = cp_dcnt_reason_cd
AND closed_ind = g_no_ind
AND dcnt_unit_ind = g_yes_ind;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT group_cd
FROM igs_pe_persid_group
WHERE group_id = cp_person_id_grp
AND closed_ind = g_no_ind;
SELECT 'x'
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 = g_fee_type_ci_status
AND fcc.fee_cal_type=cp_fee_cal_type
AND fcc.fee_ci_sequence_number = cp_fee_ci_sequence_number;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_type = g_lookup_type
AND lookup_code = cp_balance_type
AND lookup_code NOT IN ('STANDARD', 'INSTALLMENT', 'OTHER')
AND sysdate BETWEEN NVL(start_date_active, sysdate) AND
NVL(end_date_active, sysdate)
AND enabled_flag='Y';
SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = g_lookup_type_tr
AND lookup_code = cp_test_run;
shtatiko 21-JUL-2003 Bug# 3037137, Replaced call to igs_ss_en_wrappers.drop_selected_units
with igs_en_gen_004.enrp_dropall_unit
pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes to person id group views
Modified cur_unit_outstdng_chrgs - replaced igs_pe_prsid_grp_mem_v
with igs_pe_prsid_grp_mem
pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
Added check for manage_accounts - call to chk_manage_account()
svenkata 27-Dec-2002 Added 4 new parameters with defauly value of 'N' to the call to the routine
igs_ss_enr_details.drop_selected_units as a part of technical impact.Bug#2686793
shtatiko 04-Oct-2002 Enh# 2562745, Added calls to FINP_GET_BALANCE_RULE.
Added a parameter in the invocation of check_exclusion_rules.
vvutukur 23-Sep-2002 Enh#2564643.Removed references to subaccount_id from cursor
cur_unit_outstdng_chrgs and its usage and also removed
DEFAULT clause from package body as a gscc fix.
schodava 30-Jan-2002 Enh # 2187247
SFCR021 - FCI-LCI Relation
agairola 02-Jan-2002 For modification of logic
(reverse chronological order - newest change first)
****************************************************/
-- Cursor for getting the details for the charges for the person which have the Amount Due as greater than zero
-- and which have been created due to the Fee Assessment Run and have the Unit Section details
-- and the records do not have waiver details
CURSOR cur_person_chrgs(cp_n_person_id IN hz_parties.party_id%type,
cp_v_balance_type IN igs_fi_balance_rules.balance_name%TYPE,
cp_v_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
cp_n_fee_ci_sequence_number IN igs_fi_inv_int_all.fee_ci_sequence_number%TYPE
) IS
SELECT hd.person_id person_id,
ln.uoo_id uoo_id,
hd.course_cd,
hd.invoice_creation_date invoice_creation_date,
hd.fee_type,
hd.fee_cal_type,
hd.fee_ci_sequence_number,
hd.invoice_id,
hd.invoice_amount_due
FROM igs_fi_inv_int_all hd,
igs_fi_invln_int_v ln
WHERE hd.invoice_id = ln.invoice_id
AND hd.transaction_type = g_transaction_type
AND hd.person_id = cp_n_person_id
AND hd.invoice_amount_due > 0
AND hd.fee_cal_type = cp_v_fee_cal_type
AND hd.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
AND ln.uoo_id IS NOT NULL
AND hd.course_cd IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM igs_fi_inv_wav_det wd
WHERE wd.invoice_id = hd.invoice_id
AND wd.balance_type = cp_v_balance_type
AND (wd.end_dt is not null AND sysdate
BETWEEN wd.start_dt and wd.end_dt))
ORDER BY person_id,course_cd, uoo_id,invoice_creation_date;
SELECT hd.person_id person_id,
ln.uoo_id uoo_id,
hd.course_cd,
hd.invoice_creation_date invoice_creation_date,
hd.fee_type,
hd.fee_cal_type,
hd.fee_ci_sequence_number,
hd.invoice_id,
hd.invoice_amount_due
FROM igs_fi_inv_int_all hd,
igs_fi_invln_int_v ln
WHERE hd.invoice_id = ln.invoice_id
AND hd.transaction_type = g_transaction_type
AND hd.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem
WHERE group_id = cp_n_person_id_grp
AND ((end_date IS NULL) OR (TRUNC(end_date) >= TRUNC(SYSDATE))))
AND hd.invoice_amount_due > 0
AND hd.fee_cal_type = cp_v_fee_cal_type
AND hd.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
AND ln.uoo_id IS NOT NULL
AND hd.course_cd IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM igs_fi_inv_wav_det wd
WHERE wd.invoice_id = hd.invoice_id
AND wd.balance_type = cp_v_balance_type
AND (wd.end_dt is not null AND sysdate
BETWEEN wd.start_dt and wd.end_dt))
ORDER BY person_id,course_cd, uoo_id,invoice_creation_date;
SELECT hd.person_id person_id,
ln.uoo_id uoo_id,
hd.course_cd,
hd.invoice_creation_date invoice_creation_date,
hd.fee_type,
hd.fee_cal_type,
hd.fee_ci_sequence_number,
hd.invoice_id,
hd.invoice_amount_due
FROM igs_fi_inv_int_all hd,
igs_fi_invln_int_v ln
WHERE hd.invoice_id = ln.invoice_id
AND hd.transaction_type = g_transaction_type
AND hd.invoice_amount_due > 0
AND hd.fee_cal_type = cp_v_fee_cal_type
AND hd.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
AND ln.uoo_id IS NOT NULL
AND hd.course_cd IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM igs_fi_inv_wav_det wd
WHERE wd.invoice_id = hd.invoice_id
AND wd.balance_type = cp_v_balance_type
AND (wd.end_dt is not null AND sysdate
BETWEEN wd.start_dt and wd.end_dt))
ORDER BY person_id,course_cd, uoo_id,invoice_creation_date;
SELECT person_number,
first_name || ' ' || last_name person_name
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT enrolled_dt,
unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
uoo_id,
unit_attempt_status
FROM igs_en_su_attempt
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT ps.course_cd, ps.version_number
FROM igs_en_stdnt_ps_att ps
WHERE ps.person_id = p_person_id
AND ps.course_cd = p_course_cd;
SELECT description
FROM igs_ca_inst_all ca
WHERE ca.cal_type = p_v_cal_type
AND ca.sequence_number = p_n_sequence_number;