[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
akandreg 31-May-2006 Bug 5134636 - Modified finpl_lock_records and finpl_insert_record
abshriva 24-May-2006 Bug:5204728 - Added Order by clause in both cursor of finpl_find_far.Introduced p_trace_on in create_retention_charge
abshriva 17-May-2006 Bug 5113295 - Modifies finpl_chk_debt_ret_sched - Added call to igs_fi_gen_008.chk_unit_prg_transfer
abshriva 12-May-2006 Bug 5217319: Amount Precision change in create_retention_charge,finpl_ins_fee_ass
gurprsin 06-Dec-2005 Bug 4735807, Modified function 'finp_ins_enr_fee_ass', Modified the logic to return and log the message
if No Fee category is attached to the SPA.
abshriva 05-Dec-2005 Bug 4701695 Modified function 'finp_ins_enr_fee_ass'
pathipat 23-Nov-2005 Bug 4718712 - Modified finpl_ins_fee_as, finpl_chk_debt_ret_sched, finp_clc_ass_amnt
and finpl_prc_fee_cat_fee_liab. Added course_cd and career to tbl_wav_fcfl.
ayedubat 03-NOV-2005 Bug 4634950 - Changed the procedure, finp_ins_enr_fee_ass to set the Global
Parameter, g_v_wav_calc_flag to N after the call to create_waivers procedure
ayedubat 17-OCT-2005 Bug 4639869 - Incase the Fee Assessment is invoked from Tutiton Waivers logic,
Rollback should not be used as this would cause to rollback the Waivers records
prior to invocation of Fee Assessment
ayedubat 17-OCT-2005 Bug 4639869 - Changed the logic to Roll Back upto Save Point, fee_calc_sp only
if the global parameter g_v_wav_calc_flag is set to N. Also changed the logic to
assess the SPA records even if the profile, Auto Calculation of Waivers is set to N
pathipat 14-Oct-2005 Bug 4644004 - Retention amount is not calculated for increment charge method
Modified finpl_ins_fee_as
pathipat 14-Oct-2005 Bug 4634543 - Waiver amt not getting computed from fee assessment process
Modified finp_ins_enr_fee_ass
pathipat 10-Oct-2005 Bug 4375258 - Change party_number FK to TCA parties impact -
Added new local function finpl_get_org_unit_cd to fetch the org_unit_cd\
Replaced usage of igs_fi_gen_008.get_party_number with finpl_get_org_unit_cd for Org derivation
pathipat 04-Oct-2005 Bug 3781716 - Improper output message when fee category missing
Modified finp_ins_enr_fee_ass - added code to log message and error out if Fee Cat is
not specified for the SPA.
pathipat 21-Sep-2005 Bug 4383148 - Fees not assessed if attendance type cannot be derived
Modified finpl_get_derived_values
pathipat 21-Sep-2005 Enh 3513252 - Modified finp_clc_ass_amnt - Removed appending IGS_FI_PRE_SET_CHARGE
to the fee type description
uudayapr 16-Sep-2005 Bug 4609164 - Modified the code logic in finpl_ins_fee_ass procedure
pathipat 06-Sep-2005 Bug 4540295 - Fee assessment produce double fees after program version change
bannamal 26-Aug-2005 Enh#3392095 Tuition Waiver Build.
bannamal 08-Jul-2005 Enh#3392088 Campus Privilege Fee Build.
bannamal 03-JUN-2005 Bug#3442712 Unit Level Fee Assessment Build. Changes done as per TD.
bannamal 27-May-2005 Bug#4077763 Fee Calculation Performance Enhancement. Changes done as per TD.
bannamal 14-Apr-2005 Bug#4297359 ER Registration Fee issue
Modified finpl_clc_chg_mthd_elements. Added code to check whether the credit points for the unit
attempt is non zero in case the non zero billable cp flag is set to 'Y'.
Also modified finpl_chk_debt_ret_sched. modified the call to igs_fi_gen_008.get_complete_withdr_ret_amt
to add one more parameter.
bannamal 31-MAR-2005 Bug 4224364 STUDENT FINANCE UPTAKE OF PROGRAM TRANSFER FUTURE DATED FLAG VALUE CHANGES
Modified the cursors c_get_term_recs, c_get_scas_recs to ignore the program attempts
with Future-Dated Transfer flag set to 'C'
svuppala 21-MAR-2005 Bug 4240402 Timezone impact; Truncating the time part in calling place of the table handlers
rnirwani 13-Sep-2004 changed cursor c_latest_intermit_date to not consider logically deleted records and
also to avoid un-approved intermission records. Bug# 3885804
pathipat 07-Sep-2004 Enh 3880438 - Retention Enhancements
Modified finpl_chk_debt_ret_sched, finpl_ins_fee_ass, finp_ins_enr_fee_ass
Added proc create_retention_charge.
Removed duplicate function finpl_chk_debt_ret_sched
shtatiko 27-JUL-2004 Enh# 3787816, Removed function finpl_charge_is_declined. This call has been replaced with igs_fi_gen_008.chk_chg_adj
shtatiko 23-JUL-2004 Enh# 3741400, Added finpl_clc_sua_cp and modified c_sua_load, finpl_clc_chg_mthd_elements.
pathipat 01-Jul-2004 Bug 3734842 - Modified finpl_prc_fee_cat_fee_liab() to lock records before processing.
Modified finpl_ins_fee_ass() to check if header and line records were created correctly
Added functions finpl_lock_records(), finpl_insert_record() and finpl_check_header_lines()
shtatiko 24-DEC-2003 Enh# 3167098, Removed references to g_d_prg_chg_da_alias_val and g_b_prg_chg_da_use
as fee assessment calculations based on program change enforcement date alias is removed.
Impacted procedures are finp_ins_enr_fee_ass, finpl_prc_fee_cat_fee_liab, finpl_clc_chg_mthd_elements and finpl_get_derived_values
Removed references to igs_fi_f_cat_cal_rel.
uudayapr 17-dec-2003 Bug#3080983 ,Modified the cursor c_fadv to fetch data from the table IGS_FI_FEE_AS instead of the view
IGS_FI_FEE_ASS_DEBT_V. and also the declartions of IGS_FI_FEE_ASS_DEBT_V.assessment_amount%TYPE to NUMBER
shtatiko 08-DEC-2003 Bug# 3175779, Modified finp_clc_ass_amnt. Separated the processing of element ranges and element range rates.
shtatiko 13-NOV-2003 Bug# 3255069, p_charge_elements is made to 1 only when Charge Method is overridden.
And this is done only after processing all records in PL/SQL Table.
pathipat 05-Nov-2003 Enh 3117341 - Audit and Special Fees TD - Modifications according to TD, s1a
pathipat 29-Oct-2003 Bug 3166331 - Modified finp_clc_ass_amnt
Derived location_cd from SUA level if charge method <> Flatrate added cursor c_sua_location_cd for the same.
pathipat 13-Oct-2003 Bug 3166331 - Modified finp_clc_ass_amnt
Modified code to derive org_unit_cd from Unit Attempt/Unit Section level
if the charge method is not Flatrate. Also for Predictive Mode.
pathipat 01-Oct-2003 Bug 3164141 - Modified finpl_ins_fee_ass - Added check for Declined Charges
Modified code to log messages just before the insert into igs_fi_fee_as happens
pathipat 12-Sep-2003 Enh 3108052 - Unit Sets in Rate Table build
Modifications according to TD - s1a
pathipat 03-Sep-2003 Bug 3123669 - Modified finp_clc_ass_amnt - If charge method is overridden, then re-set charge method
to Flat Rate and Status = 'O'. Removed commented out code.
vchappid 22-Jul-2003 Bug#3048175, Element Ranges mapping bug. In function finp_clc_ass_amnt, parameter p_charge_elements
should be set only when the element rate range is found and not when the Element Range Applies.
vchappid 11-Jul-2003 Bug#2916881 procedure finpl_get_derived_values, flags v_on_att_mode,v_off_att_mode,
v_composite_att_mod are initialized to FALSE. When, attendance mode/type is not derived
then the assessment should be stopped.
vvutukur 26-May-2003 Enh#2831572.Financial Accounting Build.Modified procedure finpl_ins_fee_ass.
knaraset 02-May-03 Modified cursors c_suah_load_scahfv and c_sua_hist_load in function finpl_clc_chg_mthd_elements
and c_org_unit_cd and c_unit_class_att in function finpl_prc_sua_load to consider uoo_id
as part of MUS build bug 2829262.
vchappid 12-Feb-03 Bug#2788346, function finpl_clc_chg_mthd_elements invokes function finpl_prc_sua_load. This
invoking logic is changed for 'Flat Rate' charge method.
vchappid 27-Jan-03 Bug#2656411, in the function finpl_ins_match_chg_rate, modified the logic for identifying
matching fee assessment rate
pradhakr 15-Jan-03 Added one more paramter no_assessment_ind to
the call enrp_get_load_apply as an impact, following
the modification of the package Igs_En_Prc_Load.
Changes wrt ENCR026. Bug# 2743459
vchappid 09-Jan-03 Bug# 2660155, As a review comment, in the log messages Person id is replaced with the person number
vchappid 11-Nov-02 Bug# 2584986, GL- Interface Build, New Date parameter p_d_gl_date
is added to the finp_ins_enr_fee_ass procedure specification,
the same parameter is passed to the charges API
Reference to the igs_fi_curr is removed, Exchange_Rate is always passed as 1 only.
npalanis 23-OCT-02 Bug : 2608360
references to residency_class_id and residency_status_id is changed to residency_status_cd
and residency_class_cd due to transtion of residency_class and residency_status code class
to igs lookups
vchappid 21-Oct-02 Bug# 2580672, Modifications to the code as suggested by Enrolment Unit Attempt TD
vchappid 17-Oct-02 Bug# 2595962, Removed the procedure finpl_create_todo_rec,
changes as per the Predictive Fee Assessment TD
sarakshi 13-Sep-02 Enh#2564643,removed the reference of subaccount also default for gscc fix
vchappid 25-jul-02 Bug#2237227, added 'add_flag' with Default value 'N' into the Pl/SQL table t_fee_as_items
In the Function finpl_sum_fee_ass_item, if the record in the pl/sql table matches with values
that are passed to the function and the Fee Calculation Method is Primary Career then the Charge
Elements, EFTSU, Credit Points and the assessment amount are added to the existing PL/SQL table
otherwise the values are replaced with the values that are passed to the Function
vchappid 18-Jul-02 Bug# 2326166, values of Credit Points, Eftsu are also recorded when a charge is created incase when the
charge method is Per Unit, Per Credit Points and EFTSU incase of Institution/Non-Institution Fee Triggers
vchappid 15-Jul-02 Bug# 2433955, logging of message -IGS_FI_DER_RES_STAT is incorrectly done.
Same Message Name is logged using procedure r_s_log_entry and the same message name is used to set
the name of the message name when the details have to be shown to the user
Message Name IGS_FI_DER_RES_STAT is changed to IGS_FI_RES_STAT, the new message is already
registered in the system but is not being used in the process.
rnirwani 11-Jun-02 Bug# 2396536
Modified procedure finp_ins_enr_fee_ass so that after the check for key program in case it is found that the key program does
not exist then the message is logged. The log entry is saved and a return true is done. This will enable the process to continue
processing for other students - if applicable.
rnirwani 28-May-02 Bug# 2378804
Removed the generic invocation of get-lci-fci-rel and moved the same to procedures: get_derived_elemens
and clc_chg_method_elems where actually the load calendar values would be used. removed the usage of global variable since
it is not required any longer.
rnirwani 27-May-02 Bug# 2378804
Moved the code related to setting the global variable gv_Current_Data at the beginning of the procedure.
Altered the invocation of get-lci-fci-rel so that in case teh prior fee cal instance is passed then
the load calendar for prior fee calendar should be returned by the this function. This load calendar is
then used for all further calculations.
rnirwani 13-May-02 Bug# 2261649 - Removed the variable lv_usec_amount since it was not being used
and referrd to table IGS_PS_USEC_CHARGE which has been obsolete.
rnirwani 02-May-02 Bug# 2344901 - modification in procedure finp_clc_ass_amnt.
fee assessment does not happen in case a charge rate record is not located
for any one line item records (where multiple exists).
smadathi 02-May-2002 Bug 2261649. The function finp_get_additional_charge removed.
rnirwani 02-May-02 Bug# 2345191 modified procedure finp_clc_ass_amnt
the contract fee rate identification code was using the parameter passed attributes
for attendance type, mode and location.
It is supposed to use the derived values.
rnirwani 17-apr-02 bug# 2317155 modified finpl_ins_fee_ass .
passed org unit code to the charges API
vchappid 17-Jan-02 Enh Bug#2162747, Key Program Implementation, Fin Cal Inst parameters
removed, new parameter p_c_career is added
masehgal 17-Jan-2002 ENH # 2170429
Obsoletion of SPONSOR_CD
vchappid 29-Nov-01 Enh Bug#2122257, Changed some of the cursor definitions
obsolecence of function finpl_get_fee_cat is done
(reverse chronological order - newest change first)
***************************************************************/
--
X_ROWID VARCHAR2(25);
SELECT alternate_code
FROM igs_ca_inst_all
WHERE cal_type = cp_v_cal_type
AND sequence_number = cp_n_seq_num;
SELECT census_dt_alias
FROM igs_ge_s_gen_cal_con
WHERE s_control_num = cp_n_s_control_num;
SELECT MAX(alias_val)
FROM igs_ca_da_inst_v
WHERE dt_alias = cp_dt_alias
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_sequence_number;
SELECT asu.unit_set_cd,
asu.us_version_number
FROM igs_as_su_setatmpt asu,
igs_en_unit_set_all us,
igs_en_unit_set_cat usc
WHERE asu.person_id = cp_person_id
AND asu.course_cd = cp_course_cd
AND asu.student_confirmed_ind = cp_v_student_confirmed_ind
AND TRUNC(cp_effective_dt) BETWEEN TRUNC(asu.selection_dt) AND NVL(TRUNC(asu.rqrmnts_complete_dt), NVL(TRUNC(asu.end_dt), TRUNC(cp_effective_dt)))
AND asu.unit_set_cd = us.unit_set_cd
AND asu.us_version_number = us.version_number
AND us.unit_set_cat = usc.unit_set_cat
AND usc.s_unit_set_cat = cp_v_s_unit_set_cat;
SELECT uv.unit_type_id, uv.unit_level
FROM igs_ps_unit_ver_all uv,
igs_ps_unit_ofr_opt_all uoo
WHERE uv.unit_cd = uoo.unit_cd
AND uv.version_number = uoo.version_number
AND uoo.uoo_id = cp_n_uoo_id;
SELECT level_code
FROM igs_ps_unit_type_lvl
WHERE unit_type_id = cp_n_unit_type_id;
SELECT uc.unit_class, uc.unit_mode
FROM igs_as_unit_class_all uc,
igs_ps_unit_ofr_opt_all uoo
WHERE uc.unit_class = uoo.unit_class
AND uoo.uoo_id = p_n_uoo_id;
SELECT order_num, order_attr_value
FROM igs_fi_er_ord_dtls
WHERE elm_rng_order_name = p_v_elm_rng_order_name
AND order_attr_value = TO_CHAR(cp_n_unit_type_id);
SELECT *
FROM igs_en_su_attempt_all
WHERE person_id = cp_n_person_id
AND course_cd = cp_v_course_cd
AND uoo_id = cp_n_uoo_id
ORDER BY discontinued_dt;
SELECT non_std_usec_ind
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id;
SELECT spat.person_id,
spat.program_cd,
spat.program_version,
spat.fee_cat,
sca.commencement_dt,
sca.discontinued_dt,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number,
sca.cal_type,
spat.location_cd,
spat.attendance_mode,
spat.attendance_type,
ps.course_type
FROM igs_en_spa_terms spat,
igs_en_stdnt_ps_att_all sca,
igs_ps_ver_all ps,
igs_lookups_view lkps
WHERE spat.person_id = p_person_id
AND spat.person_id = sca.person_id
AND spat.program_cd = sca.course_cd
AND spat.program_version = sca.version_number
AND spat.term_cal_type = g_v_load_cal_type
AND spat.term_sequence_number = g_n_load_seq_num
AND spat.program_cd = ps.course_cd
AND spat.program_version = ps.version_number
AND lkps.lookup_type = cp_v_lookup_type
AND sca.course_attempt_status = lkps.lookup_code
AND lkps.fee_ass_ind = cp_v_fee_ass_ind;
OPEN c_att_md_ty FOR SELECT a.attendance_type,
b.govt_attendance_mode,
a.attendance_mode
FROM igs_en_spa_terms a,
igs_en_atd_mode_all b
WHERE a.person_id = p_person_id
AND a.term_cal_type = g_v_load_cal_type
AND a.term_sequence_number = g_n_load_seq_num
AND b.attendance_mode = a.attendance_mode
AND a.key_program_flag = 'Y';
OPEN c_att_md_ty FOR SELECT a.attendance_type,
b.govt_attendance_mode,
a.attendance_mode
FROM igs_en_spa_terms a,
igs_en_atd_mode_all b
WHERE a.person_id = p_person_id
AND a.program_cd = p_course_cd
AND a.term_cal_type = g_v_load_cal_type
AND a.term_sequence_number = g_n_load_seq_num
AND b.attendance_mode = a.attendance_mode;
OPEN c_scafv FOR SELECT program_cd course_cd,
NULL
FROM igs_en_spa_terms
WHERE person_id = p_person_id
AND term_cal_type = g_v_load_cal_type
AND term_sequence_number = g_n_load_seq_num;
SELECT description
FROM igs_fi_fee_type_all
WHERE fee_type = cp_fee_type;
SELECT err.rate_number,
far.order_of_precedence,
far.chg_rate,
far.govt_hecs_payment_option,
far.govt_hecs_cntrbtn_band,
far.location_cd,
far.attendance_type,
far.attendance_mode,
far.unit_class,
far.residency_status_id,
far.residency_status_cd,
far.course_cd,
far.version_number,
far.class_standing ,
far.org_party_id,
far.unit_set_cd,
far.us_version_number,
far.unit_type_id,
far.unit_mode,
far.unit_cd,
far.unit_version_number,
far.unit_level
FROM igs_fi_elm_range_rt err,
igs_fi_fee_as_rate far
WHERE far.fee_type = err.fee_type
AND far.fee_cal_type = err.fee_cal_type
AND far.fee_ci_sequence_number = err.fee_ci_sequence_number
AND far.rate_number = err.rate_number
AND far.s_relation_type = err.s_relation_type
AND (far.fee_cat = err.fee_cat OR (far.fee_cat IS NULL AND err.fee_cat IS NULL))
AND err.fee_type = cp_v_fee_type
AND err.fee_cal_type = cp_v_fee_cal_type
AND err.fee_ci_sequence_number = cp_n_ci_seq_number
AND (err.fee_cat IS NULL OR err.fee_cat = cp_v_fee_cat)
AND err.range_number = cp_n_range_number
AND err.s_relation_type = cp_v_relation_type -- just to be sure that Elm Ranges and Elm Range Rates are picked up from same level and so redundant.
AND err.logical_delete_dt IS NULL
AND far.logical_delete_dt IS NULL
ORDER BY far.order_of_precedence ASC;
SELECT cfar.lower_nrml_rate_ovrd_ind,
cfar.chg_rate
FROM igs_fi_fee_as_rt cfar,
igs_en_atd_mode_all am
WHERE cfar.person_id = p_person_id AND
cfar.course_cd = p_course_cd AND
cfar.FEE_TYPE = p_fee_type AND
NVL(cfar.location_cd, cp_location_cd) = cp_location_cd AND
NVL(cfar.attendance_type, cp_attendance_type) = cp_attendance_type AND
am.attendance_mode (+) = cfar.attendance_mode AND
(
(g_v_att_profile = gcst_nominated AND NVL(cfar.attendance_mode, cp_prog_att_mode) = cp_prog_att_mode)
OR
(g_v_att_profile = gcst_derived AND NVL(am.govt_attendance_Mode, cp_govt_att_mode) = cp_govt_att_mode)
) AND
TRUNC(p_effective_dt) >= TRUNC(cfar.start_dt) AND
(cfar.end_dt IS NULL OR
TRUNC(p_effective_dt) <= TRUNC(cfar.end_dt));
SELECT am.GOVT_ATTENDANCE_MODE
FROM igs_en_atd_mode_all am
WHERE am.ATTENDANCE_MODE = cp_attendance_mode;
SELECT responsible_org_unit_cd
FROM igs_ps_ver_all v
WHERE v.course_cd = cp_course_cd
AND v.version_number = cp_version_number;
SELECT owner_org_unit_cd, location_cd
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_uoo_id;
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id;
SELECT am.govt_attendance_mode
FROM igs_en_atd_mode_all am
WHERE am.attendance_mode = p_rate_attendance_mode;
SELECT s_fee_type
FROM igs_fi_fee_type_all
WHERE fee_type = cp_v_fee_type;
OPEN c_att_md_ty FOR SELECT a.attendance_type,
b.govt_attendance_mode,
a.attendance_mode
FROM igs_en_spa_terms a,
igs_en_atd_mode_all b
WHERE a.person_id = p_person_id
AND a.term_cal_type = g_v_load_cal_type
AND a.term_sequence_number = g_n_load_seq_num
AND b.attendance_mode = a.attendance_mode
AND a.key_program_flag = 'Y';
OPEN c_att_md_ty FOR SELECT a.attendance_type,
b.govt_attendance_mode,
a.attendance_mode
FROM igs_en_spa_terms a,
igs_en_atd_mode_all b
WHERE a.person_id = p_person_id
AND a.program_cd = p_course_cd
AND a.term_cal_type = g_v_load_cal_type
AND a.term_sequence_number = g_n_load_seq_num
AND b.attendance_mode = a.attendance_mode;
OPEN c_scafv FOR SELECT program_cd course_cd,
NULL
FROM igs_en_spa_terms
WHERE person_id = p_person_id
AND term_cal_type = g_v_load_cal_type
AND term_sequence_number = g_n_load_seq_num;
SELECT far.chg_rate,
far.s_relation_type,
far.location_cd,
far.attendance_type,
far.attendance_mode,
far.rate_number,
far.residency_status_cd,
far.org_party_id,
far.course_cd,
far.version_number,
far.class_standing,
far.unit_set_cd,
far.us_version_number,
far.unit_type_id,
far.unit_class,
far.unit_mode,
far.unit_cd,
far.unit_version_number,
far.unit_level
FROM igs_fi_fee_as_rate far,
igs_fi_f_cat_fee_lbl_all fcfl
WHERE far.s_relation_type = cp_v_s_relation_type
AND far.logical_delete_dt is NULL
AND fcfl.fee_cat = far.fee_cat
AND fcfl.fee_cal_type = far.fee_cal_type
AND fcfl.fee_ci_sequence_number = far.fee_ci_sequence_number
AND fcfl.fee_type = far.fee_type
AND far.fee_type = p_fee_type
AND far.fee_cal_type = p_fee_cal_type
AND far.fee_ci_sequence_number = p_fee_ci_sequence_number
AND far.fee_cat = p_fee_cat
ORDER BY far.order_of_precedence ASC;
SELECT far.chg_rate,
far.s_relation_type,
far.location_cd,
far.attendance_type,
far.attendance_mode,
far.rate_number,
far.residency_status_cd,
far.org_party_id,
far.course_cd,
far.version_number,
far.class_standing,
far.unit_set_cd,
far.us_version_number,
far.unit_type_id,
far.unit_class,
far.unit_mode,
far.unit_cd,
far.unit_version_number,
far.unit_level
FROM igs_fi_fee_as_rate far,
igs_fi_f_cat_fee_lbl_all fcfl
WHERE far.s_relation_type = cp_v_s_relation_type
AND far.logical_delete_dt is NULL
AND fcfl.fee_type = far.fee_type
AND fcfl.fee_cal_type = far.fee_cal_type
AND fcfl.fee_ci_sequence_number = far.fee_ci_sequence_number
AND fcfl.fee_cat = p_fee_cat
AND far.fee_type = p_fee_type
AND far.fee_cal_type = p_fee_cal_type
AND far.fee_ci_sequence_number = p_fee_ci_sequence_number
AND (far.fee_cat = p_fee_cat or far.fee_cat is NULL)
ORDER BY far.order_of_precedence ASC;
SELECT er_id,
s_relation_type,
range_number,
lower_range,
upper_range,
s_chg_method_type override_chg_method_type -- Override charge method at element range level
FROM igs_fi_elm_range
WHERE fee_type = cp_v_fee_type
AND fee_cal_Type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_ci_seq_number
AND (fee_cat IS NULL OR fee_cat = cp_v_fee_cat)
AND logical_delete_dt IS NULL
ORDER BY lower_range ASC;
SELECT far.*
FROM igs_fi_elm_range_rt err,
igs_fi_fee_as_rate far
WHERE far.fee_type = err.fee_type
AND far.fee_cal_type = err.fee_cal_type
AND far.fee_ci_sequence_number = err.fee_ci_sequence_number
AND far.rate_number = err.rate_number
AND far.s_relation_type = err.s_relation_type
AND (far.fee_cat = err.fee_cat OR (far.fee_cat IS NULL AND err.fee_cat IS NULL))
AND err.fee_type = cp_v_fee_type
AND err.fee_cal_type = cp_v_fee_cal_type
AND err.fee_ci_sequence_number = cp_n_ci_seq_number
AND (err.fee_cat IS NULL OR err.fee_cat = cp_v_fee_cat)
AND err.range_number = cp_n_range_number
AND err.s_relation_type = cp_v_relation_type -- just to be sure that Elm Ranges and Elm Range Rates are picked up from same level and so redundant.
AND err.logical_delete_dt IS NULL
AND far.logical_delete_dt IS NULL
ORDER BY far.order_of_precedence ASC;
SELECT *
FROM igs_fi_sub_elm_rng
WHERE er_id = cp_n_er_id
ORDER BY sub_lower_range;
SELECT far.*
FROM igs_fi_sub_er_rt ser,
igs_fi_fee_as_rate far
WHERE ser.sub_er_id = cp_n_sub_er_id
AND far.far_id = ser.far_id
AND ser.logical_delete_date IS NULL
AND far.logical_delete_dt IS NULL
ORDER BY far.order_of_precedence;
SELECT responsible_org_unit_cd
FROM igs_ps_ver_all v
WHERE v.course_cd = cp_course_cd
AND v.version_number = cp_version_number;
SELECT retention_level_code
FROM igs_fi_f_typ_ca_inst_all
WHERE fee_type = cp_v_fee_type
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_sequence_num;
tbl_fai_unit_dtls.DELETE;
tbl_fai_unit_dtls.DELETE;
SELECT fcci.fee_cat
FROM igs_fi_f_cat_ca_inst fcci,
igs_fi_fee_str_stat fss
WHERE fcci.fee_cat = p_fee_category
AND (
-- In Predictive, Select only when Effective Date (i.e., SYSDATE) is less than FCCI Start Date Alias Value.
( g_c_predictive_ind = 'Y' AND
(TRUNC(cp_effective_dt) < (SELECT TRUNC(daiv.alias_val)
FROM igs_ca_da_inst_v daiv
WHERE daiv.DT_ALIAS = fcci.start_dt_alias AND
daiv.sequence_number = fcci.start_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number)
)
)
OR
-- In Actual, Select only when FCCI is active as on Effective Date. (i.e., Eff Date <= FCCI Start Date Alias)
( g_c_predictive_ind = 'N' AND
(TRUNC(cp_effective_dt) >= (SELECT TRUNC(daiv.alias_val)
FROM igs_ca_da_inst_v daiv
WHERE daiv.DT_ALIAS = fcci.start_dt_alias AND
daiv.sequence_number = fcci.start_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number)
)
)
)
AND TRUNC(cp_effective_dt) <= ( SELECT TRUNC(daiv.alias_val)
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.DT_ALIAS = fcci.end_dt_alias
AND daiv.sequence_number = fcci.end_dai_sequence_number
AND daiv.CAL_TYPE =fcci.fee_cal_type
AND daiv.ci_sequence_number = fcci.fee_ci_sequence_number)
AND fcci.fee_cat_ci_status = fss.FEE_STRUCTURE_STATUS
AND fss.s_fee_structure_status = gcst_active;
SELECT fee_calc_mthd_code,
currency_cd
FROM igs_fi_control_all;
SELECT 'X'
FROM igs_ps_type_all
WHERE course_type = p_c_career;
SELECT alias_val
FROM igs_ca_da_inst_v
WHERE dt_alias = cp_dt_alias
AND cal_type = p_fee_cal_type
AND ci_sequence_number = p_fee_ci_sequence_num;
SELECT currency_cd
FROM igs_fi_fee_cat_all
WHERE fee_cat = cp_fee_cat;
SELECT sca.course_attempt_status
FROM igs_en_stdnt_ps_att_all sca
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd;
SELECT 'X'
FROM igs_en_stdnt_ps_att_all sca,
igs_en_su_attempt_all sua
WHERE sca.person_id = cp_n_person_id
AND sca.person_id = sua.person_id
AND sca.course_cd = sua.course_cd
AND NVL(sca.primary_program_type, cp_v_secondary) = cp_v_secondary
AND sua.unit_attempt_status = cp_v_enrolled;
SELECT program_cd, program_version
FROM igs_en_spa_terms
WHERE person_id = cp_n_person_id
AND term_cal_type = cp_v_load_cal_type
AND term_sequence_number = cp_n_load_ci_seq_num
AND key_program_flag = cp_v_key_prog_flag;
SELECT esptv.person_id,
esptv.program_cd,
esptv.program_version,
psv.course_type,
esptv.fee_cat,
esptv.location_cd,
esptv.attendance_mode,
esptv.attendance_type,
sca.course_attempt_status,
sca.cal_type,
sca.commencement_dt,
sca.discontinued_dt,
psv.short_title,
esptv.key_program_flag
FROM igs_en_spa_terms esptv,
igs_ps_ver_all psv,
igs_en_stdnt_ps_att_all sca,
igs_lookups_view scas
WHERE esptv.program_cd = psv.course_cd
AND esptv.program_version = psv.version_number
AND esptv.program_cd = sca.course_cd
AND esptv.program_version = sca.version_number
AND esptv.person_id = sca.person_id
AND esptv.person_id = cp_n_person_id
AND
(esptv.term_cal_type = cp_v_load_cal_type
AND
esptv.term_sequence_number = cp_n_load_ci_seq_num
)
AND (cp_v_program_cd IS NULL OR esptv.program_cd = cp_v_program_cd)
AND (cp_v_fee_cat IS NULL OR esptv.fee_cat = cp_v_fee_cat)
AND ( /* If Fee Calc Mthd is CAREER, Term records are created only for primary programs. So select all records from terms table */
(g_c_fee_calc_mthd IN (g_v_program,g_v_career))
OR
(esptv.key_program_flag = cp_v_key_program_flag AND g_c_fee_calc_mthd = g_v_primary_career)
)
AND (cp_v_course_type IS NULL OR psv.course_type = cp_v_course_type)
AND sca.course_attempt_status = scas.lookup_code
AND scas.lookup_type = cp_v_lookup_type
AND scas.fee_ass_ind = cp_v_fee_ass_ind
ORDER BY esptv.fee_cat, esptv.person_id, esptv.program_cd;
SELECT party_name
FROM hz_parties p
WHERE p.party_id = cp_n_person_id;
SELECT esptv.person_id,
esptv.program_cd,
esptv.program_version,
psv.course_type,
esptv.fee_cat,
esptv.location_cd,
esptv.attendance_mode,
esptv.attendance_type,
sca.course_attempt_status,
sca.cal_type,
sca.commencement_dt,
sca.discontinued_dt,
psv.short_title,
esptv.key_program_flag
FROM igs_en_spa_terms esptv,
igs_ps_ver_all psv,
igs_en_stdnt_ps_att_all sca,
igs_lookups_view scas
WHERE esptv.program_cd = psv.course_cd
AND esptv.program_version = psv.version_number
AND esptv.program_cd = sca.course_cd
AND esptv.program_version = sca.version_number
AND esptv.person_id = sca.person_id
AND esptv.person_id = cp_n_person_id
AND (esptv.term_cal_type = cp_v_load_cal_type
AND esptv.term_sequence_number = cp_n_load_ci_seq_num)
AND ((g_c_fee_calc_mthd in (g_v_program, g_v_career)) OR
(esptv.key_program_flag = cp_v_key_program_flag AND
g_c_fee_calc_mthd = g_v_primary_career))
AND sca.course_attempt_status = scas.lookup_code
AND scas.lookup_type = cp_v_lookup_type
AND scas.fee_ass_ind = cp_v_fee_ass_ind
AND esptv.fee_cat = cp_v_fee_cat;
SELECT fee_cat
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_n_person_id
AND course_cd = cp_v_course_cd;
SELECT description, s_fee_trigger_cat
FROM igs_fi_fee_type_all
WHERE fee_type = cp_fee_type;
part of Program Transfer.Added cur_disc_dt to select only dcnt_reason_cd.
pathipat 22-Nov-2005 Bug 4718712 - Added code to log Old and New Amts only if atleast one is non-zero
bannamal 08-Jul-2005 Enh#3392088 Campus Privilege Fee. Changes done as per TD.
bannamal 14-Apr-2005 Bug#4297359 ER Registration Fee issue
Modified the call to igs_fi_gen_008.get_complete_withdr_ret_amt to add
a new parameter p_v_nonzero_billable_cp_flag.
pathipat 07-Sep-2004 Enh 3880438 - Retention Enhancements
Completely revamped retention logic to include Teaching Period retention.
pathipat 05-Nov-2003 Enh 3117341 - Audit and Special Fees TD
Removed code for retention amount, added call to generic pkg to get amount
pathipat 12-Sep-2003 Enh 3108052 - Unit Sets in Rate table build
Modified TBH calls of igs_Fi_fee_As_items
sarakshi 13-Sep-2002 Enh#2564643,removed teh reference of subaccount from this procedure
vvutukur 11-02-2002 Removed the cursor l_c_invoice_id and the check if the retention amount is greater than 0,
within which source invoice id is derived. This is done for bug 2195715 as part of SFCR003,
as there is no negative adjustment charge crated by Retention.
*********************************************************************************************************/
-- Local variables
l_v_retention_level igs_fi_f_typ_ca_inst_all.retention_level_code%TYPE := NULL;
SELECT optional_payment_ind, description, s_fee_trigger_cat
FROM igs_fi_fee_type_all
WHERE fee_type = cp_v_fee_type;
SELECT nonzero_billable_cp_flag
FROM igs_fi_f_typ_ca_inst_all
WHERE fee_type = cp_v_fee_type
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_seq_number;
SELECT fai.*
FROM igs_fi_fai_dtls fai
WHERE fee_ass_item_id = cp_n_fee_ass_item_id;
SELECT discontinued_dt
FROM igs_en_su_attempt_all sua,
igs_lookups_view lkp
WHERE lkp.lookup_code = sua.unit_attempt_status
AND lkp.lookup_type = cp_v_lookup_type
AND (sua.no_assessment_ind = cp_v_fee_ass_ind OR cp_v_fee_ass_ind IS NULL)
AND sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND (igs_en_prc_load.enrp_get_load_apply(sua.cal_type,
sua.ci_sequence_number,
sua.discontinued_dt,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.no_assessment_ind,
g_v_load_cal_type,
g_n_load_seq_num,
sua.no_assessment_ind ) = cp_v_enrp_get_load_apply )
AND sua.uoo_id = cp_n_uoo_id
AND (igs_fi_gen_008.chk_unit_prg_transfer(sua.dcnt_reason_cd) = 'N')
ORDER BY sua.discontinued_dt;
SELECT sua.enrolled_dt
FROM igs_en_su_attempt_all sua
WHERE sua.uoo_id = cp_n_uoo_id
AND sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd;
SELECT sua.dcnt_reason_cd
FROM igs_en_su_attempt_all sua
WHERE sua.uoo_id = cp_n_uoo_id
AND sua.person_id = cp_n_person_id
AND sua.course_cd = cp_v_course_cd;
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id;
tbl_fee_as_items.DELETE;
tbl_fee_as_items_diff.DELETE;
tbl_unit_status.DELETE;
tbl_enr_disc_dt.DELETE;
tbl_fee_as_items_dummy.DELETE;
Used t_Fee_as_items.crs_version_number for inserting into igs_fi_fee_as_items
instead of the local variable l_crs_version_number.
bannamal 08-Jul-2005 Enh#3392088 Campus Privilege Fee.
Modified the tbh call to fee as items table to include two new columns.
bannamal 03-Jun-2005 Bug#3442712 Unit Level Fee Assessment Build. Modified the call to igs_fi_fee_as_items_pkg.insert_row
added new parameters unit_type_id, unit_class, unit_mode and unit_level.
pathipat 07-Sep-2004 Enh 3880438 - Retention Enhancements build
Removed condition checking if new amount < old amount before invoking finpl_chk_debt_ret_sched
shtatiko 27-JUL-2004 Enh# 3787816, Replaced the call to finpl_charge_is_declined with igs_fi_gen_008.chk_chg_adj.
pathipat 06-Jul-2004 Bug 3734842 - Added logic to check if records have been created correctly
in IGS_FI_FEE_AS and IGS_FI_FEE_AS_ITEMS tables - added call to finpl_check_header_lines
UUDAYAPR 17-DEC-2003 BUG#3080983 Modified V_assessment_amount,V_transaction_amount,p_fee_assessment
To Number From Igs_fi_fee_ass_debt_v.Assessment_amount%Type,
V_manual_entry_ind To Varchar2(1)
v_last_effective_assessment_dt = IGS_FI_FEE_ASS_DEBT_V.last_effective_assessment_dt%TYPE TO DATE;
Moved code for logging messages to before the actual insert happens
pathipat 12-Sep-2003 Enh 3108052 - Unit Sets in Rate Table build
Modified TBH call of igs_fi_fee_as_items
vvutukur 26-May-2003 Enh#2831572.Financial Accounting Build. Assigned proper value to p_v_residency_cd before calling charges api.
sarakshi 13-Sep-2002 Enh#2564643,removed teh reference of subaccount from this procedure
VVUTUKUR 11-02-2002 Removed cursor l_c_invoice_id and related logic reg. the derivation of the source transaction ID.
Since this part of covered in Charges API. bug 2195715 as part of SFCR003
rnirwani 17-apr-02 Before invocation to the charges api the orgunit code is also assigned to record group
relating to lines entry for bug# 2317155
************************************************************************************************/
-- Exception raised when insertion into IGS_FI_FEE_AS and IGS_FI_FEE_AS_ITEMS had some errors
e_unexpected_error EXCEPTION;
SELECT s_chg_method_type, elm_rng_order_name
FROM igs_fi_f_cat_fee_lbl_v
WHERE fee_cat = cp_fee_cat
AND fee_type = cp_fee_type
AND fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_sequence_number;
p_v_string => 'Try inserting Fee As Transaction.' );
/*** to set flag to true for insert into AS table only one record in AS table **/
IF NOT lv_as_record_ins THEN
lv_as_record_ins := TRUE;
/** summing up the differential amount for insert into AS Table**/
lv_sum_diff := NVL(lv_sum_diff,0) + NVL(lv_diff_amount,0);
END IF; /** added by syam on 21-NOV-2000 to insert proper sum into amount of as table **/
p_v_string => 'Trying to insert records..');
p_v_string => 'Inserting Fee As Record for Amount: ' || lv_sum_diff );
igs_fi_fee_as_pkg.insert_row(
x_rowid => lv_as_rowid,
x_person_id => p_person_id,
x_transaction_id => v_fa_sequence_number,
x_fee_type => p_fee_type,
x_fee_cal_type => p_fee_cal_type,
x_fee_ci_sequence_number => p_fee_ci_sequence_number,
x_fee_cat => v_fee_cat,
x_s_transaction_type => cst_assessment,
x_transaction_dt => TRUNC(SYSDATE),
x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(NVL(lv_sum_diff,0)),
x_currency_cd => p_currency,
x_exchange_rate => 1,
x_chg_elements => v_chg_elements,
x_effective_dt => TRUNC(p_effective_dt),
x_course_cd => v_course_cd,
x_notification_dt => NULL,
x_logical_delete_dt => NULL,
x_comments => NULL,
x_mode => 'R',
x_org_id => l_n_org_id );
/*** to insert all records in AS ITEMs table with difference in amount ***/
OPEN c_chg_method (t_fee_as_items(i).fee_cat,
t_fee_as_items(i).fee_type,
t_fee_as_items(i).fee_cal_type,
t_fee_as_items(i).fee_ci_sequence_number);
p_v_string => 'Inserting Charge for Amount: ' || lv_diff_amount );
p_v_string => 'Inserting Fee Assessment Items Record.');
igs_fi_fee_as_items_pkg.insert_row(
x_rowid => lv_as_items_rowid,
x_fee_ass_item_id => lv_fee_ass_item_id,
x_transaction_id => v_fa_sequence_number,
x_person_id => t_fee_as_items(i).person_id,
x_status => t_fee_as_items(i).status,
x_fee_type => t_fee_as_items(i).fee_type,
x_fee_cat => t_fee_as_items(i).fee_cat ,
x_fee_cal_type => t_fee_as_items(i).fee_cal_type ,
x_fee_ci_sequence_number => t_fee_as_items(i).fee_ci_sequence_number,
x_rul_sequence_number => t_fee_as_items(i).rul_sequence_number,
x_s_chg_method_type => t_fee_as_items(i).chg_method_type,
x_description => t_fee_as_items(i).description,
x_chg_elements => t_fee_as_items(i).chg_elements,
x_amount => igs_fi_gen_gl.get_formatted_amount(NVL(lv_diff_amount,0)),
x_fee_effective_dt => TRUNC(p_effective_dt),
x_course_cd => t_fee_as_items(i).course_cd,
x_crs_version_number => t_fee_as_items(i).crs_version_number,
x_course_attempt_status => l_crs_attempt_status,
x_attendance_mode => p_attendance_mode,
x_attendance_type => p_attendance_type,
x_unit_attempt_status => t_fee_as_items(i).unit_attempt_status,
x_location_cd => t_fee_as_items(i).location_cd,
x_eftsu => t_fee_as_items(i).eftsu,
x_credit_points => t_fee_as_items(i).credit_points,
x_logical_delete_date => null,
X_INVOICE_ID => l_invoice_id,
x_org_unit_cd => t_fee_as_items(i).org_unit_cd,
x_class_standing => t_fee_as_items(i).class_standing,
x_residency_status_cd => t_fee_as_items(i).residency_status_cd,
x_uoo_id => t_fee_as_items(i).uoo_id,
x_chg_rate => igs_fi_gen_gl.get_formatted_amount(t_fee_as_items(i).chg_rate),
x_unit_set_cd => t_fee_as_items(i).unit_set_cd,
x_us_version_number => t_fee_as_items(i).us_version_number,
x_unit_type_id => t_fee_as_items(i).unit_type_id,
x_unit_class => t_fee_as_items(i).unit_class,
x_unit_mode => t_fee_as_items(i).unit_mode,
x_unit_level => t_fee_as_items(i).unit_level,
x_scope_rul_sequence_num => p_n_scope_rul_seq_num,
x_elm_rng_order_name => l_v_elm_rng_ord_name,
x_max_chg_elements => p_n_max_chg_elements
);
p_v_string => 'Inserting record in IGS_FI_FAI_DTLS with UOO_ID: '||tbl_fai_unit_dtls(j).uoo_id||
' for Fee Assessment Item ID: '||lv_fee_ass_item_id);
igs_fi_fai_dtls_pkg.insert_row(
x_rowid => l_v_fai_rowid,
x_fee_as_item_dtl_id => l_v_fee_as_item_dtl_id,
x_fee_ass_item_id => lv_fee_ass_item_id,
x_fee_cat => tbl_fai_unit_dtls(j).fee_cat,
x_course_cd => tbl_fai_unit_dtls(j).course_cd,
x_crs_version_number => tbl_fai_unit_dtls(j).crs_version_number,
x_unit_attempt_status => tbl_fai_unit_dtls(j).unit_attempt_status,
x_org_unit_cd => tbl_fai_unit_dtls(j).org_unit_cd,
x_class_standing => tbl_fai_unit_dtls(j).class_standing,
x_location_cd => tbl_fai_unit_dtls(j).location_cd,
x_uoo_id => tbl_fai_unit_dtls(j).uoo_id,
x_unit_set_cd => tbl_fai_unit_dtls(j).unit_set_cd,
x_us_version_number => tbl_fai_unit_dtls(j).us_version_number,
x_chg_elements => tbl_fai_unit_dtls(j).chg_elements
);
SELECT unit_class, no_assessment_ind
FROM igs_en_su_attempt_all
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT org_unit_cd -- this column needs to be added as yet to the table as a part of Nov 2001 Build
FROM igs_en_su_attempt_all su
WHERE su.person_id = cp_person_id
AND su.course_cd = cp_course_cd
AND su.uoo_id = cp_uoo_id;
SELECT owner_org_unit_cd -- this column is added as a part of Unit Section Reference information Build
FROM igs_ps_unit_ofr_opt_all uoo
WHERE uoo.unit_cd = cp_unit_cd
AND uoo.version_number = cp_version_number
AND uoo.cal_type = cp_cal_type
AND uoo.ci_sequence_number = cp_ci_sequence_number
AND uoo.location_cd = cp_location_cd
AND uoo.unit_class = cp_unit_class;
SELECT responsible_org_unit_cd
FROM igs_ps_ver_all v
WHERE v.course_cd = cp_course_cd
AND v.version_number = cp_version_number;
p_description=>null,--fee type desc selected inside
p_chg_elements=>p_charge_elements,--which is 1
p_unit_attempt_status =>NULL,
p_location_cd=>p_location_cd,
p_eftsu =>null,
p_credit_points=>null,
p_amount=>0,
p_org_unit_cd => v_derived_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on=>p_trace_on,
p_message_name=>lv_sum_message,
p_uoo_id => NULL,
p_n_unit_type_id => NULL,
p_v_unit_level => NULL,
p_v_unit_class => NULL,
p_v_unit_mode => NULL,
p_v_unit_cd => NULL,
p_n_unit_version => NULL
) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_prc_sua_load',
p_v_string => 'Returning false as finpl_sum_fee_ass_item returned with message:'|| lv_sum_message );
p_description=>null,--fee type desc selected inside
p_chg_elements=>1, -- The Charge Method is Per Unit, Charge Elements will be passed as 1 for each assessable Unit
p_unit_attempt_status =>p_unit_attempt_status,
p_location_cd=>p_location_cd,
p_eftsu =>p_eftsu,
p_credit_points=>p_credit_points,
p_amount=>0,
p_org_unit_cd => v_derived_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on=>p_trace_on,
p_message_name=>lv_sum_message,
p_uoo_id => p_uoo_id,
p_n_unit_type_id => p_n_unit_type_id,
p_v_unit_level => p_v_unit_level,
p_v_unit_class => lv_unit_class_att,
p_v_unit_mode => p_v_unit_mode,
p_v_unit_cd => p_unit_cd,
p_n_unit_version => p_unit_version_number ) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_prc_sua_load',
p_v_string => 'Returning false as finpl_sum_fee_ass_item returned with message:'|| lv_sum_message );
p_description=>null,--fee type desc selected inside
p_chg_elements=>p_eftsu,
p_unit_attempt_status =>p_unit_attempt_status,
p_location_cd=>p_location_cd,
p_eftsu =>p_eftsu,
p_credit_points=>p_credit_points,
p_amount=>0,
p_org_unit_cd => v_derived_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on=>p_trace_on,
p_message_name=>lv_sum_message,
p_uoo_id => p_uoo_id,
p_n_unit_type_id => p_n_unit_type_id,
p_v_unit_level => p_v_unit_level,
p_v_unit_class => lv_unit_class_att,
p_v_unit_mode => p_v_unit_mode,
p_v_unit_cd => p_unit_cd,
p_n_unit_version => p_unit_version_number ) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_prc_sua_load',
p_v_string => 'Returning false as finpl_sum_fee_ass_item returned with message:'|| lv_sum_message );
p_description=>null,--fee type desc selected inside
p_chg_elements=>p_credit_points,
p_unit_attempt_status =>p_unit_attempt_status,
p_location_cd=>p_location_cd,
p_eftsu =>p_eftsu,
p_credit_points=>p_credit_points,
p_amount=>0,
p_org_unit_cd => v_derived_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on=>p_trace_on,
p_message_name=>lv_sum_message,
p_uoo_id => p_uoo_id,
p_n_unit_type_id => p_n_unit_type_id,
p_v_unit_level => p_v_unit_level,
p_v_unit_class => lv_unit_class_att,
p_v_unit_mode => p_v_unit_mode,
p_v_unit_cd => p_unit_cd,
p_n_unit_version => p_unit_version_number ) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_prc_sua_load',
p_v_string => 'Returning false as finpl_sum_fee_ass_item returned with message:'|| lv_sum_message );
p_n_selection_rule IN igs_ru_rule.sequence_number%TYPE
)
RETURN BOOLEAN AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
bannamal 08-Jul-2005 Enh#3392088 Campus Privilege Fee.
Changes done as per TD.
bannamal 14-Apr-2005 Bug#4297359 ER Registration Fee issue
Added code to check whether the credit points for the unit
attempt is non zero in case the non zero billable cp flag is
set to 'Y'.
pathipat 05-Nov-2003 Enh 3117341 - Audit and Special Fees TD
Modifications according to TD, s1a
vchappid 12-Feb-03 Bug#2788346, In this function, function finpl_pr8/16/2005
invoking logic is changed for 'Flat Rate' charge method.
***************************************************************/
lv_param_values VARCHAR2(1080);
SELECT sua.cal_type,
sua.ci_sequence_number,
sua.discontinued_dt,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.unit_cd,
sua.version_number,
sua.uoo_id,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.no_assessment_ind,
sua.location_cd,
sua.org_unit_cd,
uoo.owner_org_unit_cd
FROM igs_en_su_attempt_all sua,
igs_lookups_view lkp,
igs_ps_unit_ofr_opt_all uoo
WHERE lkp.lookup_code = sua.unit_attempt_status
AND lkp.lookup_type = cp_v_lookup_type
AND lkp.fee_ass_ind = cp_v_fee_ass_indicator
AND (sua.no_assessment_ind = cp_v_fee_ass_ind OR cp_v_fee_ass_ind IS NULL)
AND sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND uoo.uoo_id = sua.uoo_id;
SELECT sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.unit_attempt_status,
sua.ci_sequence_number,
sua.location_cd,
SYSDATE effective_start_dt,
SYSDATE effective_end_dt,
NVL(igs_en_gen_014.enrs_clc_sua_eftsu(
sua.person_id,
p_course_cd, -- Enh#2162747 changed sua.course_cd to p_course_cd
p_course_version_number, -- Enh#2162747
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
cp_load_cal_type,
cp_load_ci_sequence_number,
sua.override_enrolled_cp,
sua.override_eftsu,
'Y', -- truncate_ind
NULL,
sua.no_assessment_ind
),0) eftsu, -- sca_cp_total
finpl_clc_sua_cp( sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
cp_load_cal_type,
cp_load_ci_sequence_number,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.uoo_id,
sua.no_assessment_ind) credit_points,
sua.uoo_id uoo_id,
sua.no_assessment_ind,
sua.org_unit_cd,
uoo.owner_org_unit_cd
FROM igs_en_su_attempt_all sua,
igs_lookups_view suas,
igs_en_spa_terms terms,
igs_ps_unit_ofr_opt_all uoo
WHERE sua.person_id = p_person_id AND
sua.person_id = terms.person_id AND
sua.course_cd = terms.program_cd AND
uoo.uoo_id = sua.uoo_id AND
terms.term_cal_type = g_v_load_cal_type AND
terms.term_sequence_number = g_n_load_seq_num AND
(
( sua.course_cd = p_course_cd AND g_c_fee_calc_mthd IN (g_v_program, g_v_career))
OR
(g_c_fee_calc_mthd= g_v_primary_career)
) AND
suas.lookup_code = sua.unit_attempt_status AND
suas.lookup_type = cp_v_lookup_type AND
suas.fee_ass_ind = cp_v_fee_ass_indicator AND
(sua.no_assessment_ind = cp_v_fee_ass_ind OR cp_v_fee_ass_ind IS NULL) AND
igs_en_prc_load.enrp_get_load_apply(
sua.cal_type,
sua.ci_sequence_number,
sua.discontinued_dt,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.no_assessment_ind,
cp_load_cal_type,
cp_load_ci_sequence_number,
sua.no_assessment_ind
) = cp_v_enrp_get_load_apply;
SELECT responsible_org_unit_cd
FROM igs_ps_ver_all v
WHERE v.course_cd = cp_course_cd
AND v.version_number = cp_version_number;
SELECT spat.person_id,
spat.program_cd,
spat.program_version,
spat.fee_cat,
sca.commencement_dt,
sca.discontinued_dt,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number,
sca.cal_type,
spat.location_cd,
spat.attendance_mode,
spat.attendance_type,
ps.course_type
FROM igs_en_spa_terms spat,
igs_en_stdnt_ps_att_all sca,
igs_ps_ver_all ps,
igs_lookups_view lkps
WHERE spat.person_id = p_person_id
AND spat.person_id = sca.person_id
AND spat.program_cd = sca.course_cd
AND spat.program_version = sca.version_number
AND spat.term_cal_type = g_v_load_cal_type
AND spat.term_sequence_number = g_n_load_seq_num
AND spat.program_cd = ps.course_cd
AND spat.program_version = ps.version_number
AND lkps.lookup_type = cp_v_lookup_type
AND sca.course_attempt_status = lkps.lookup_code
AND lkps.fee_ass_ind = cp_v_fee_ass_ind;
SELECT nonzero_billable_cp_flag
FROM igs_fi_f_typ_ca_inst_all
WHERE fee_type = cp_v_fee_type
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_seq_number;
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id;
SELECT retention_level_code
FROM igs_fi_f_typ_ca_inst_all
WHERE fee_type = cp_v_fee_type
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_sequence_num;
SELECT asu.unit_set_cd,
asu.us_version_number
FROM igs_as_su_setatmpt asu,
igs_en_unit_set_all us,
igs_en_unit_set_cat usc
WHERE asu.person_id = cp_person_id
AND asu.course_cd = cp_course_cd
AND asu.student_confirmed_ind = cp_v_student_confirmed_ind
AND TRUNC(cp_effective_dt) BETWEEN TRUNC(asu.selection_dt) AND NVL(TRUNC(asu.rqrmnts_complete_dt), NVL(TRUNC(asu.end_dt), TRUNC(cp_effective_dt)))
AND asu.unit_set_cd = us.unit_set_cd
AND asu.us_version_number = us.version_number
AND us.unit_set_cat = usc.unit_set_cat
AND usc.s_unit_set_cat = cp_v_s_unit_set_cat;
p_description => null, --fee type desc selected inside
p_chg_elements => p_charge_elements, --which is 1
p_unit_attempt_status => null,
p_location_cd => l_v_location_cd,
p_eftsu => null,
p_credit_points => null,
p_amount => 0,
p_org_unit_cd => l_v_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on => p_trace_on,
p_message_name => lv_sum_message,
p_uoo_id => NULL,
p_n_unit_type_id => NULL,
p_v_unit_level => NULL,
p_v_unit_class => NULL,
p_v_unit_mode => NULL,
p_v_unit_cd => NULL,
p_n_unit_version => NULL
) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_clc_chg_mthd_elements',
p_v_string => 'Returning False as finpl_sum_fee_ass_item returned false with message: ' || lv_sum_message);
p_description => null,--fee type desc selected inside
p_chg_elements => v_eftsu,
p_unit_attempt_status => null,
p_location_cd => l_v_location_cd,
p_eftsu => v_eftsu,
p_credit_points => null,
p_amount => 0,
p_org_unit_cd => l_v_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on => p_trace_on,
p_message_name => lv_sum_message,
p_uoo_id => NULL,
p_n_unit_type_id => NULL,
p_v_unit_level => NULL,
p_v_unit_class => NULL,
p_v_unit_mode => NULL,
p_v_unit_cd => NULL,
p_n_unit_version => NULL
) = FALSE THEN
RETURN FALSE;
p_description => null,--fee type desc selected inside
p_chg_elements => v_credit_points,
p_unit_attempt_status => null,
p_location_cd => l_v_location_cd,
p_eftsu => null,
p_credit_points => v_credit_points,
p_amount => 0,
p_org_unit_cd => l_v_org_unit_cd, -- CCR for Enh# 1851586
p_trace_on => p_trace_on,
p_message_name => lv_sum_message,
p_uoo_id => NULL,
p_n_unit_type_id => NULL,
p_v_unit_level => NULL,
p_v_unit_class => NULL,
p_v_unit_mode => NULL,
p_v_unit_cd => NULL,
p_n_unit_version => NULL ) = FALSE THEN
RETURN FALSE;
tbl_fai_unit_dtls.DELETE;
IF (p_n_selection_rule IS NOT NULL) THEN
IF (igs_ru_gen_003.rulp_clc_student_scope(p_rule_number => p_n_selection_rule,
p_unit_loc_cd => v_sualv_scafv_rec.location_cd,
p_prg_type_level => finpl_get_uptl(l_n_prg_type_level),
p_org_code => v_sualv_scafv_rec.owner_org_unit_cd,
p_unit_mode => l_v_unit_mode,
p_unit_class => l_v_unit_class,
p_message => l_v_message_name ) = TRUE) THEN
l_b_rule := TRUE;
p_description=>null ,--fee type desc selected inside
p_chg_elements=>p_charge_elements,--which is 1
p_unit_attempt_status =>NULL,
p_location_cd=>NULL,
p_eftsu =>NULL,
p_credit_points=>NULL,
p_amount=>0,
p_org_unit_cd => NULL,
p_trace_on=>p_trace_on,
p_message_name=>lv_sum_message,
p_uoo_id => NULL,
p_n_unit_type_id => NULL,
p_v_unit_level => NULL,
p_v_unit_class => NULL,
p_v_unit_mode => NULL,
p_v_unit_cd => NULL,
p_n_unit_version => NULL ) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_clc_chg_mthd_elements',
p_v_string => 'Flat Rate Case: Returning False as finpl_sum_fee_ass_item returned message: ' || lv_sum_message);
p_description=>null ,--fee type desc selected inside
p_chg_elements=>p_charge_elements,
p_unit_attempt_status =>NULL,
p_location_cd=>NULL,
p_eftsu => l_eftsu,
p_credit_points=> l_crpoint,
p_amount=>0,
p_org_unit_cd => NULL, -- CCR for Enh# 1851586
p_trace_on=>p_trace_on,
p_message_name=>lv_sum_message,
p_uoo_id => NULL,
p_n_unit_type_id => NULL,
p_v_unit_level => NULL,
p_v_unit_class => NULL,
p_v_unit_mode => NULL,
p_v_unit_cd => NULL,
p_n_unit_version => NULL) = FALSE THEN
log_to_fnd( p_v_module => 'finpl_clc_chg_mthd_elements',
p_v_string => 'Other than Flat Rate Case: Returning false as finpl_sum_fee_ass_item returned message: '||lv_sum_message);
IF (p_n_selection_rule IS NOT NULL) THEN
IF (igs_ru_gen_003.rulp_clc_student_scope (p_rule_number => p_n_selection_rule,
p_unit_loc_cd => v_sua_load_rec.location_cd,
p_prg_type_level => finpl_get_uptl(l_n_prg_type_level),
p_org_code => v_sua_load_rec.owner_org_unit_cd,
p_unit_mode => l_v_unit_mode,
p_unit_class => l_v_unit_class,
p_message => l_v_message_name ) = TRUE) THEN
l_b_rule := TRUE;
SELECT fcci.FEE_CAT,
fcci.fee_cal_type,
fcci.fee_ci_sequence_number,
fcci.start_dt_alias,
fcci.start_dai_sequence_number,
fcci.end_dt_alias,
fcci.end_dai_sequence_number,
ci.start_dt,
ci.end_dt
FROM IGS_FI_F_CAT_CA_INST fcci,
IGS_FI_FEE_STR_STAT fss,
igs_ca_inst_all ci
WHERE fcci.FEE_CAT = p_fee_cat AND
(p_fee_cal_type IS NULL OR fcci.fee_cal_type = p_fee_cal_type) AND
(p_fee_ci_sequence_num IS NULL OR
fcci.fee_ci_sequence_number = p_fee_ci_sequence_num) AND
(
-- In Predictive, Select only when Effective Date (i.e., SYSDATE) is less than FCFL Start Date Alias Value.
( g_c_predictive_ind = 'Y' AND
(TRUNC(cp_effective_dt) < (SELECT TRUNC(daiv.alias_val)
FROM igs_ca_da_inst_v daiv
WHERE daiv.DT_ALIAS = fcci.start_dt_alias AND
daiv.sequence_number = fcci.start_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number AND
daiv.alias_val IS NOT NULL))
)
OR
-- In Actual, Select only when FCFL is active as on Effective Date. (i.e., Eff Date <= FCFL Start Date Alias)
( g_c_predictive_ind = 'N' AND
(TRUNC(cp_effective_dt) >= (SELECT TRUNC(daiv.alias_val)
FROM igs_ca_da_inst_v daiv
WHERE daiv.DT_ALIAS = fcci.start_dt_alias AND
daiv.sequence_number = fcci.start_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number AND
daiv.alias_val IS NOT NULL))
)
) AND
TRUNC(cp_effective_dt) <=
(SELECT TRUNC(daiv.alias_val)
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.DT_ALIAS = fcci.end_dt_alias AND
daiv.sequence_number = fcci.end_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number AND
daiv.alias_val IS NOT NULL) AND
(p_discontinued_dt IS NULL OR
(TRUNC(p_discontinued_dt) >=
(SELECT TRUNC(daiv.alias_val)
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.DT_ALIAS = fcci.start_dt_alias AND
daiv.sequence_number = fcci.start_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number AND
daiv.alias_val IS NOT NULL) AND
TRUNC(p_discontinued_dt) <=
(SELECT TRUNC(daiv.alias_val)
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.DT_ALIAS = fcci.end_dt_alias AND
daiv.sequence_number = fcci.end_dai_sequence_number AND
daiv.CAL_TYPE =fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number AND
daiv.alias_val IS NOT NULL))) AND
fcci.fee_cat_ci_status = fss.FEE_STRUCTURE_STATUS AND
fss.s_fee_structure_status = gcst_active AND
ci.CAL_TYPE = fcci.fee_cal_type AND
ci.sequence_number = fcci.fee_ci_sequence_number;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.DT_ALIAS = cp_dt_alias AND
daiv.sequence_number = cp_dai_sequence_number AND
daiv.CAL_TYPE =cp_fee_cal_type AND
daiv.ci_sequence_number = cp_fee_ci_sequence_number;
SELECT fcflv.fee_cal_type,
fcflv.fee_ci_sequence_number,
fcflv.FEE_TYPE,
fcflv.fee_liability_status,
fcflv.start_dt_alias,
fcflv.start_dai_sequence_number,
fcflv.end_dt_alias,
fcflv.end_dai_sequence_number,
fcflv.s_chg_method_type,
fcflv.rul_sequence_number,
fcflv.currency_cd,
fss.s_fee_structure_status,
ft.s_fee_trigger_cat,
ft.s_fee_type,
fcflv.fee_cat,
'CURRENT' source,
fcflv.elm_rng_order_name,
fcflv.scope_rul_sequence_num,
fcflv.max_chg_elements
FROM igs_fi_f_cat_fee_lbl_v fcflv,
igs_fi_fee_str_stat fss,
igs_fi_fee_type_all ft
WHERE fcflv.fee_cat = cp_fee_cat AND
fcflv.fee_cal_type = cp_fee_cal_type AND
fcflv.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
(p_fee_type IS NULL OR fcflv.fee_type = p_fee_type) AND
fcflv.fee_liability_status = fss.fee_structure_status AND
fcflv.fee_type = ft.fee_type AND
(p_waiver_call_ind = 'N' OR (p_waiver_call_ind = 'Y' AND fcflv.fee_type = p_target_fee_type)) AND
((p_v_wav_calc_flag = 'Y' AND fcflv.waiver_calc_flag = 'Y') OR (p_v_wav_calc_flag = 'N' AND fcflv.waiver_calc_flag = 'N'))
UNION ALL
-- This will select FCFLs from Fee As Items table that are not selected in above part of cursor.
SELECT DISTINCT ast.fee_cal_type,
ast.fee_ci_sequence_number,
ast.fee_type,
NULL,
NULL,
TO_NUMBER(NULL),
NULL,
TO_NUMBER(NULL),
NULL,
TO_NUMBER(NULL),
NULL,
'INACTIVE',
ft.s_fee_trigger_cat,
ft.s_fee_type,
ast.fee_cat,
'OLD' source,
ast.elm_rng_order_name,
ast.scope_rul_sequence_num,
ast.max_chg_elements
FROM igs_fi_fee_as_items ast,
igs_fi_fee_as_all fas,
igs_fi_fee_type_all ft,
igs_ps_ver_all ps
WHERE ast.person_id = p_person_id
AND (p_fee_type IS NULL OR (p_fee_type IS NOT NULL AND ast.fee_type = p_fee_type))-- will reverse existing charges only when user has not provided Fee Type as input
AND ps.course_cd (+) = ast.course_cd
AND ps.version_number (+) = ast.crs_version_number
AND ast.fee_cal_type = cp_fee_cal_type
AND ast.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND ast.fee_type = ft.fee_type
AND ast.person_id = fas.person_id
AND ast.transaction_id = fas.transaction_id
AND fas.s_transaction_type = cp_v_s_transaction_type
AND (
((ft.s_fee_trigger_cat = gcst_institutn
AND (ast.course_cd IS NULL
OR (g_c_fee_calc_mthd = g_v_program AND ast.course_cd IS NOT NULL)
))
OR
( ft.s_fee_trigger_cat <> gcst_institutn AND
(
(ast.course_cd = p_course_cd AND g_c_fee_calc_mthd = g_v_program) OR
(ps.course_type = p_c_career AND g_c_fee_calc_mthd = g_v_career)
)
))
OR
(g_c_fee_calc_mthd = g_v_primary_career)
) AND (ast.fee_type,NVL(ast.fee_cat,cp_fee_cat)) NOT IN (SELECT fee_type,fee_cat FROM igs_fi_f_cat_fee_lbl_all
WHERE fee_cat = cp_fee_cat
AND fee_cal_type = ast.fee_cal_type
AND fee_ci_sequence_number = ast.fee_ci_sequence_number
AND fee_type = ast.fee_type);
SELECT spt.program_cd,
spt.program_version,
spt.fee_cat,
spa.adm_admission_appl_number,
spa.adm_nominated_course_cd,
spa.adm_sequence_number,
spa.commencement_dt,
spa.discontinued_dt,
spa.cal_type,
spt.location_cd,
spt.attendance_mode,
spt.attendance_type
FROM igs_en_spa_terms spt,
igs_en_stdnt_ps_att_all spa,
igs_ps_ver_all ps,
igs_lookups_view lkps
WHERE spt.person_id = spa.person_id
AND spt.program_cd = spa.course_cd
AND spt.program_version = spa.version_number
AND spt.person_id = p_person_id
AND spt.term_cal_type = g_v_load_cal_type
AND spt.term_sequence_number = g_n_load_seq_num
AND spt.program_cd = ps.course_cd
AND spt.program_version = ps.version_number
AND spa.course_attempt_status = lkps.lookup_code
AND lkps.lookup_type = cp_v_lookup_type
AND lkps.fee_ass_ind = cp_v_fee_ass_ind
AND (
(spt.program_cd <> p_course_cd AND g_c_fee_calc_mthd = g_v_program) OR
(ps.course_type <> p_c_career AND g_c_fee_calc_mthd = g_v_career)
);
SELECT fee_cat, fee_type
FROM igs_fi_f_cat_fee_lbl_all
WHERE fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_seq_num
AND fee_type = cp_v_fee_type;
SELECT description,
s_fee_trigger_cat
FROM igs_fi_fee_type_all
WHERE fee_type = cp_fee_type;
SELECT s_chg_method_type,
scope_rul_sequence_num
FROM igs_fi_f_typ_ca_inst_all
WHERE fee_type = cp_fee_type
AND fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_sequence_number;
SELECT a.course_cd,
a.crs_version_number,
a.location_cd,
SUM(a.chg_elements) sum_elements,
SUM(a.amount) sum_amount,
SUM(a.credit_points) sum_credit_points,
SUM(a.eftsu) sum_eftsu,
a.org_unit_cd,
a.uoo_id,
a.fee_cat
FROM igs_fi_fee_as_items a,
igs_ps_ver_all ps
WHERE a.course_cd = ps.course_cd (+)
AND a.crs_version_number = ps.version_number (+)
AND a.person_id = cp_person_id
AND a.fee_type = cp_fee_type
AND a.fee_cal_type = cp_fee_cal_type
AND a.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND ((p_fee_cat IS NOT NULL AND a.fee_cat = p_fee_cat ) OR (a.fee_cat IS NULL OR p_fee_cat IS NULL))
AND ((cp_s_trigger_cat = gcst_institutn AND
(a.course_cd is NULL
OR (g_c_fee_calc_mthd = g_v_program AND a.course_cd IS NOT NULL)
))
OR (
(g_c_fee_calc_mthd = g_v_program AND a.course_cd = cp_course_cd)
OR
(g_c_fee_calc_mthd = g_v_career AND ps.course_type = cp_career)
OR
(g_c_fee_calc_mthd = g_v_primary_career)
)
)
AND a.logical_delete_date IS NULL
AND TRUNC(a.fee_effective_dt) <= TRUNC(p_effective_date)
AND NOT EXISTS ( SELECT 'x'
FROM igs_fi_fee_as_all b
WHERE b.person_id = cp_person_id
AND b.transaction_id = a.transaction_id
AND b.fee_type = cp_fee_type
AND b.fee_cal_type = cp_fee_cal_type
AND b.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND ((p_fee_cat IS NOT NULL AND b.fee_cat = p_fee_cat ) OR (b.fee_cat IS NULL OR p_fee_cat IS NULL))
AND ((cp_s_trigger_cat = gcst_institutn AND
(a.course_cd is NULL
OR (g_c_fee_calc_mthd = g_v_program AND a.course_cd IS NOT NULL)
))
OR (
(g_c_fee_calc_mthd = g_v_program AND a.course_cd = cp_course_cd)
OR
(g_c_fee_calc_mthd = g_v_career AND ps.course_type = cp_career)
OR
(g_c_fee_calc_mthd = g_v_primary_career)
)
)
AND b.s_transaction_type in (cp_v_s_transaction_type1, cp_v_s_transaction_type2)
)
GROUP BY a.course_cd,
a.crs_version_number,
a.fee_cat,
a.uoo_id,
a.location_cd,
a.org_unit_cd;
SELECT a.course_cd,
a.crs_version_number,
a.location_cd,
SUM(a.chg_elements) sum_elements,
SUM(a.amount) sum_amount,
SUM(a.credit_points) sum_credit_points,
SUM(a.eftsu) sum_eftsu,
a.org_unit_cd,
a.uoo_id,
a.fee_cat
FROM igs_fi_fee_as_items a
WHERE a.person_id = cp_person_id AND
a.fee_type = cp_fee_type AND
a.fee_cal_type = cp_fee_cal_type AND
a.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
((p_fee_cat IS NOT NULL AND a.fee_cat = p_fee_cat ) OR (a.fee_cat IS NULL OR p_fee_cat IS NULL)) AND
a.logical_delete_date IS NULL AND
TRUNC(a.fee_effective_dt) <= TRUNC(p_effective_date) AND
NOT EXISTS ( SELECT 'X'
FROM igs_fi_fee_as_all b
WHERE b.person_id = cp_person_id
AND b.transaction_id = a.transaction_id
AND b.fee_type = cp_fee_type
AND b.fee_cal_type = cp_fee_cal_type
AND b.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND ((p_fee_cat IS NOT NULL AND b.fee_cat = p_fee_cat ) OR (b.fee_cat IS NULL OR p_fee_cat IS NULL))
AND b.s_transaction_type in (cp_v_s_transaction_type1, cp_v_s_transaction_type2)
)
GROUP BY a.course_cd,
a.crs_version_number,
a.fee_cat,
a.uoo_id,
a.location_cd,
a.org_unit_cd;
SELECT a.fee_ass_item_id upd,
a.s_chg_method_type
FROM igs_fi_fee_as_items a,
igs_ps_ver_all ps
WHERE a.course_cd = ps.course_cd (+)
AND a.crs_version_number = ps.version_number (+)
AND a.person_id = cp_person_id
AND a.fee_type = cp_fee_type
AND (a.course_cd IS NULL
OR
(
(g_c_fee_calc_mthd = g_v_program AND a.course_cd = cp_course_cd)
OR
/* Select based on Career but not on the course code passed as Primary Program can be changed after the assessment. */
(g_c_fee_calc_mthd = g_v_career AND ps.course_type = cp_career)
OR
(g_c_fee_calc_mthd = g_v_primary_career)
)
)
AND a.fee_cal_type = cp_fee_cal_type
AND a.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND a.chg_elements <> 0
ORDER BY upd DESC;
tbl_fai_unit_dtls.DELETE;
SELECT spat.person_id,
spat.program_cd,
spat.program_version,
psv.course_type,
spat.fee_cat,
sca.cal_type,
spat.location_cd,
spat.attendance_mode,
spat.attendance_type,
spat.key_program_flag,
sca.course_attempt_status,
sca.commencement_dt,
sca.discontinued_dt
FROM igs_en_spa_terms spat,
igs_en_stdnt_ps_att_all sca,
igs_ps_ver_all psv
WHERE spat.program_cd = psv.course_cd
AND spat.program_version = psv.version_number
AND spat.person_id = cp_n_person_id
AND spat.program_cd = sca.course_cd
AND spat.program_version = sca.version_number
AND spat.person_id = sca.person_id
AND
(spat.term_cal_type = g_v_load_cal_type
AND
spat.term_sequence_number = g_n_load_seq_num
)
AND (cp_v_fee_category IS NULL OR spat.fee_cat = cp_v_fee_category)
AND (cp_v_course_cd IS NULL OR spat.program_cd = cp_v_course_cd)
AND (cp_v_career IS NULL OR cp_v_career = psv.course_type)
AND (
(g_c_fee_calc_mthd IN (g_v_program,g_v_career)) -- Get all Term Records
OR
(spat.key_program_flag = cp_v_key_program_flag AND g_c_fee_calc_mthd = g_v_primary_career) -- Get only record of Key Program
)
AND (
( g_c_fee_calc_mthd = g_v_program
AND (sca.course_attempt_status IN (cp_v_course_attempt_status1, cp_v_course_attempt_status2, cp_v_course_attempt_status3, cp_v_course_attempt_status4)
AND (NVL(sca.future_dated_trans_flag,'N') <> cp_v_future_dated_trans_flag)
)
)
OR
( g_c_fee_calc_mthd IN (g_v_career, g_v_primary_career)
AND sca.course_attempt_status in (cp_v_course_attempt_status1, cp_v_course_attempt_status2, cp_v_course_attempt_status3))
)
ORDER BY spat.fee_cat,
spat.person_id,
spat.program_cd;
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
psv.course_type,
sca.fee_cat,
sca.cal_type,
sca.location_cd,
sca.attendance_mode,
sca.attendance_type,
sca.key_program,
sca.course_attempt_status,
sca.commencement_dt,
sca.discontinued_dt,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number
FROM igs_en_stdnt_ps_att_all sca,
igs_ps_ver_all psv
WHERE sca.course_cd = psv.course_cd
AND sca. version_number = psv.version_number
AND sca.person_id = cp_n_person_id
AND (cp_v_fee_cat IS NULL OR sca.fee_cat = cp_v_fee_cat)
AND (cp_v_course_cd IS NULL OR sca.course_cd = cp_v_course_cd)
AND (cp_v_career IS NULL OR psv.course_type = cp_v_career)
AND (
(g_c_fee_calc_mthd =g_v_program)
OR
(g_c_fee_calc_mthd =g_v_career AND sca.primary_program_type = cp_v_primary_program_type )
OR
(g_c_fee_calc_mthd = g_v_primary_career AND sca.key_program = cp_v_key_program)
)
AND (
(g_c_fee_calc_mthd = g_v_program AND (sca.course_attempt_status IN (cp_v_course_attempt_status1, cp_v_course_attempt_status2, cp_v_course_attempt_status3, cp_v_course_attempt_status4)
AND (NVL(sca.future_dated_trans_flag,'N') <> cp_v_future_dated_trans_flag)
)
)
OR
(g_c_fee_calc_mthd IN (g_v_career, g_v_primary_career) AND sca.course_attempt_status in (cp_v_course_attempt_status1, cp_v_course_attempt_status2, cp_v_course_attempt_status3))
)
ORDER BY sca.fee_cat,
sca.person_id,
sca.course_cd;
SELECT currency_cd
FROM igs_fi_fee_cat_all
WHERE fee_cat = cp_v_fee_cat;
cp_logical_delete_date igs_en_stdnt_ps_intm.logical_delete_date%TYPE) IS
SELECT sci.end_dt
FROM igs_en_stdnt_ps_intm sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = cp_n_person_id
AND sci.course_cd = cp_v_course_cd
AND sci.logical_delete_date = cp_logical_delete_date
AND sci.approved = eit.appr_reqd_ind AND
eit.intermission_type = sci.intermission_type
ORDER BY end_dt DESC;
SELECT off.s_adm_offer_resp_status,
app.adm_cal_type,
app.adm_ci_sequence_number
FROM igs_ad_ps_appl_inst_all apl_in,
igs_ad_ofr_resp_stat off,
igs_ad_appl_all app
WHERE apl_in.adm_offer_resp_status = off.adm_offer_resp_status
AND apl_in.person_id = cp_n_person_id
AND apl_in.nominated_course_cd = cp_v_adm_nominated_course_cd
AND apl_in.sequence_number = cp_n_adm_sequence_number
AND apl_in.admission_appl_number = cp_n_adm_admission_appl_number
AND app.person_id = apl_in.person_id
AND app.admission_appl_number = apl_in.admission_appl_number;
SELECT rl.sub_cal_type,
rl.sub_ci_sequence_number
FROM igs_ca_inst_rel rl,
igs_ca_type ct1,
igs_ca_type ct2
WHERE rl.sup_cal_type = ct1.cal_type
AND ct1.s_cal_cat = cp_v_admission
AND rl.sub_cal_type = ct2.cal_type
AND ct2.s_cal_cat = cp_v_load
AND rl.sup_cal_type = cp_v_adm_cal_type
AND rl.sup_ci_sequence_number = cp_n_adm_ci_seq_num;
SELECT start_dt
FROM igs_ca_inst_all
WHERE cal_type = cp_v_cal_type
AND sequence_number = cp_n_ci_seq_num;
/* Select the fee calculation method and program change date alias,
error out NOCOPY when there is no fee calculation method setup */
OPEN c_fi_control;
tbl_wav_fcfl.DELETE;
FUNCTION finpl_insert_record(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
RETURN BOOLEAN
IS
PRAGMA AUTONOMOUS_TRANSACTION;
Purpose : Function inserts records into table IGS_FI_SPA_FEE_PRDS
if such a record doesnt exist already. Invoked from
finpl_lock_records().
Returns TRUE if insertion was successful, FALSE otherwise.
Know limitations, enhancements or remarks
Change History
Who When What
akandreg 24-Apr-2006 Bug 5134627 - Modified TBH callout to pass value for TRANSACTION_TYPE
***************************************************************/
l_rowid ROWID;
log_to_fnd( p_v_module => 'finpl_insert_record',
p_v_string => 'Proceeding to insert an Assessment record into IGS_FI_SPA_FEE_PRDS' );
igs_fi_spa_fee_prds_pkg.insert_row ( x_rowid => l_rowid,
x_person_id => p_n_person_id,
x_course_cd => p_v_course_cd,
x_fee_cal_type => p_v_fee_cal_type,
x_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
x_mode => 'R',
x_transaction_type => 'ASSESSMENT'
);
log_to_fnd( p_v_module => 'finpl_insert_record',
p_v_string => 'Record inserted into IGS_FI_SPA_FEE_PRDS, Commit successful, Return TRUE' );
log_to_fnd( p_v_module => 'finpl_insert_record',
p_v_string => 'Exception section of finpl_insert_record : '||SQLERRM );
END finpl_insert_record;
SELECT 'x'
FROM igs_fi_spa_fee_prds
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_sequence_number
AND transaction_type = cp_transaction_type
FOR UPDATE NOWAIT;
l_v_dummy VARCHAR2(2) := NULL; -- Dummy variable to hold the value selected in cur_fee_spa
p_v_string => 'Record not present in IGS_FI_SPA_FEE_PRDS, so call finpl_insert_record' );
IF finpl_insert_record(p_n_person_id,
p_v_course_cd,
p_v_fee_cal_type,
p_n_fee_ci_sequence_number) THEN
-- After insertion (if insertion was successful), lock the record
log_to_fnd( p_v_module => 'finpl_lock_records',
p_v_string => 'Insertion of record in IGS_FI_SPA_FEE_PRDS successful, lock and return TRUE');
p_v_string => 'Insertion of record into IGS_FI_SPA_FEE_PRDS not successful, return FALSE');
SELECT SUM(transaction_amount)
FROM igs_fi_fee_as_all
WHERE person_id = cp_n_person_id
AND transaction_id = cp_n_transaction_id;
SELECT amount
FROM igs_fi_fee_as_items
WHERE person_id = cp_n_person_id
AND transaction_id = cp_n_transaction_id;
to igs_fi_fee_as_items_pkg.insert_row added new parameters
unit_type_id, unit_class, unit_mode and unit_level.
***************************************************************/
-- Cursor to fetch the Fee Type Description
l_rowid ROWID := NULL;
p_v_string => 'Inserting AS record for Retention Amount.');
igs_fi_fee_as_pkg.insert_row (x_rowid => l_rowid,
x_person_id => p_n_person_id,
x_transaction_id => l_n_transaction_id,
x_fee_type => p_v_fee_type,
x_fee_cal_type => p_v_fee_cal_type,
x_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
x_fee_cat => l_v_fee_cat,
x_s_transaction_type => g_v_retention,
x_transaction_dt => TRUNC(SYSDATE),
x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(p_n_amount),
x_currency_cd => g_v_currency_cd,
x_exchange_rate => 1,
x_chg_elements => NULL,
x_effective_dt => TRUNC(SYSDATE),
x_course_cd => l_v_course_cd,
x_notification_dt => NULL,
x_logical_delete_dt => NULL,
x_comments => NULL,
x_mode => 'R',
x_org_id => g_n_org_id);
p_v_string => 'Inserting Fee Assessment Items Record for Rentention charge.');
igs_fi_fee_as_items_pkg.insert_row( x_rowid => l_rowid,
x_fee_ass_item_id => l_n_fee_ass_item_id,
x_transaction_id => l_n_transaction_id,
x_person_id => p_n_person_id,
x_status => 'E',
x_fee_type => p_v_fee_type,
x_fee_cat => l_v_fee_cat ,
x_fee_cal_type => p_v_fee_cal_type ,
x_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
x_rul_sequence_number => NULL,
x_s_chg_method_type => g_v_chgmthd_flatrate,
x_description => NULL,
x_chg_elements => NULL,
x_amount => igs_fi_gen_gl.get_formatted_amount(p_n_amount),
x_fee_effective_dt => TRUNC(SYSDATE),
x_course_cd => l_v_course_cd,
x_crs_version_number => NULL,
x_course_attempt_status => NULL,
x_attendance_mode => NULL,
x_attendance_type => NULL,
x_unit_attempt_status => NULL,
x_location_cd => NULL,
x_eftsu => NULL,
x_credit_points => NULL,
x_logical_delete_date => NULL,
x_invoice_id => l_n_invoice_id,
x_org_unit_cd => NULL,
x_class_standing => NULL,
x_residency_status_cd => NULL,
x_uoo_id => p_n_uoo_id,
x_chg_rate => NULL,
x_unit_set_cd => NULL,
x_us_version_number => NULL,
x_unit_type_id => NULL,
x_unit_class => NULL,
x_unit_mode => NULL,
x_unit_level => NULL
);
SELECT party_number
FROM igs_or_inst_org_base_v
WHERE party_id = cp_n_party_id;