[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Modified igs_fi_control_pkg.update_row by adding two new columns
post_waiver_gl_flag, waiver_notify_finaid_flag
gurprsin 18-Jun-2005 Bug# 3392088 , Modified call to igs_fi_f_typ_ca_inst_pkg.update_row method to include
scope_rul_sequence_num and elm_rng_order_name
gurprsin 02-Jun-2005 Enh# 3442712 - Fee Based on Unit Level Attributes
Added 4 new columns unit_level,unit_type_id,unit_class,unit_mode in insert_ftci_accounts
Modifications to reflect the Addition of 4 new parameters to incorporate Unit Level Attributes.
updated call to insert_ftci_accts in updt_ftci_acct_info procedure to pass new parameters with NULL values
svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
Modifications to reflect the data model changes (NONZERO_BILLABLE_CP_FLAG) in
Fee Type Calendar Instances Table
rmaddipa 17-Sep-2004 Enh# 3880438 Modified the procedure update_ftci_rev_acc_cd
jbegum 14-June-2003 Bug# 2998266 Obsoleted the column NEXT_INVOICE_NUMBER.
shtatiko 12-JUN-2003 Enh# 2831582, Modified updt_ftci_acct_info.
-------------------------------------------------------------------*/
skip EXCEPTION;
SELECT ca.start_dt,
ca.end_dt,
ft.s_fee_type,
h.name,
ftci.rowid,
ftci.*
FROM igs_fi_fee_type ft,
igs_fi_f_typ_ca_inst ftci,
igs_ca_inst ca,
igs_fi_hier_accounts h
WHERE ft.fee_type = ftci.fee_type
AND ftci.fee_cal_type = ca.cal_type
AND ftci.fee_ci_sequence_number = ca.sequence_number
AND ftci.acct_hier_id = h.acct_hier_id
ORDER BY ft.fee_type,
ftci.fee_cal_type,
ca.start_dt;
PROCEDURE insert_ftci_accounts( p_v_fee_type IN VARCHAR2,
p_v_fee_cal_type IN VARCHAR2,
p_n_fee_ci_sequence_number IN NUMBER,
p_n_order_sequence IN NUMBER,
p_n_natural_account_segment IN VARCHAR2,
p_v_rev_account_cd IN VARCHAR2,
p_v_location_cd IN VARCHAR2,
p_v_attendance_type IN VARCHAR2,
p_v_attendance_mode IN VARCHAR2,
p_v_course_cd IN VARCHAR2,
p_n_crs_version_number IN NUMBER,
p_v_unit_cd IN VARCHAR2,
p_n_unit_version_number IN NUMBER,
p_v_org_unit_cd IN VARCHAR2,
p_v_residency_status_cd IN VARCHAR2,
p_n_uoo_id IN NUMBER,
p_v_unit_level IN VARCHAR2,
p_n_unit_type_id IN NUMBER,
p_v_unit_mode IN VARCHAR2,
p_v_unit_class IN VARCHAR2
) IS
/*------------------------------------------------------------------
Created by : vvutukur, Oracle IDC
Date created: 23-May-2003
Purpose: Inserts record into igs_fi_ftci_accts table using the specified parameters.
Known limitations/enhancements and/or remarks:
Change History:
Who When What
gurprsin 02-Jun-2005 Enh# 3442712, Added 4 new paramters for Unit Level Attributes
-------------------------------------------------------------------*/
l_rowid VARCHAR2(25);
igs_fi_ftci_accts_pkg.insert_row (
x_rowid => l_rowid,
x_acct_id => l_n_acct_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_order_sequence => p_n_order_sequence,
x_natural_account_segment => p_n_natural_account_segment,
x_rev_account_cd => p_v_rev_account_cd,
x_location_cd => p_v_location_cd,
x_attendance_type => p_v_attendance_type,
x_attendance_mode => p_v_attendance_mode,
x_course_cd => p_v_course_cd,
x_crs_version_number => p_n_crs_version_number,
x_unit_cd => p_v_unit_cd,
x_unit_version_number => p_n_unit_version_number,
x_org_unit_cd => p_v_org_unit_cd,
x_residency_status_cd => p_v_residency_status_cd,
x_uoo_id => p_n_uoo_id,
x_mode => 'R',
x_unit_level => p_v_unit_level,
x_unit_type_id => p_n_unit_type_id,
x_unit_mode => p_v_unit_mode,
x_unit_class => p_v_unit_class
);
END insert_ftci_accounts;
PROCEDURE update_ftci_rev_acc_cd( l_cur_ftci_details cur_ftci%ROWTYPE,
p_v_rev_account_cd igs_fi_f_typ_ca_inst.rev_account_cd%TYPE) IS
/*------------------------------------------------------------------
Created by : vvutukur, Oracle IDC
Date created: 05-Jun-2003
Purpose: Updates the FTCI record with the specified revenue account code parameter value.
Known limitations/enhancements and/or remarks:
Change History:
Who When What
gurprsin 18-Jun-2005 Bug# 3392088 , Modified call to igs_fi_f_typ_ca_inst_pkg.update_row method to include
scope_rul_sequence_num and elm_rng_order_name
svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
TBH impact of NONZERO_BILLABLE_CP_FLAG field in Fee Type Calendar Instances Table
rmaddipa 17-Sep-2004 Added two parameters retention_level_code,complete_ret_flag.
-------------------------------------------------------------------*/
BEGIN
igs_fi_f_typ_ca_inst_pkg.update_row(
x_rowid => l_cur_ftci_details.rowid,
x_fee_type => l_cur_ftci_details.fee_type,
x_fee_cal_type => l_cur_ftci_details.fee_cal_type,
x_fee_ci_sequence_number => l_cur_ftci_details.fee_ci_sequence_number,
x_fee_type_ci_status => l_cur_ftci_details.fee_type_ci_status,
x_start_dt_alias => l_cur_ftci_details.start_dt_alias,
x_start_dai_sequence_number => l_cur_ftci_details.start_dai_sequence_number,
x_end_dt_alias => l_cur_ftci_details.end_dt_alias,
x_end_dai_sequence_number => l_cur_ftci_details.end_dai_sequence_number,
x_retro_dt_alias => l_cur_ftci_details.retro_dt_alias,
x_retro_dai_sequence_number => l_cur_ftci_details.retro_dai_sequence_number,
x_s_chg_method_type => l_cur_ftci_details.s_chg_method_type,
x_rul_sequence_number => l_cur_ftci_details.rul_sequence_number,
x_mode => 'R',
x_initial_default_amount => l_cur_ftci_details.initial_default_amount,
x_acct_hier_id => l_cur_ftci_details.acct_hier_id,
x_rec_gl_ccid => l_cur_ftci_details.rec_gl_ccid,
x_rev_account_cd => p_v_rev_account_cd,
x_rec_account_cd => l_cur_ftci_details.rec_account_cd,
x_ret_gl_ccid => l_cur_ftci_details.ret_gl_ccid,
x_ret_account_cd => l_cur_ftci_details.ret_account_cd,
x_retention_level_code => l_cur_ftci_details.retention_level_code,
x_complete_ret_flag => l_cur_ftci_details.complete_ret_flag,
x_nonzero_billable_cp_flag => l_cur_ftci_details.nonzero_billable_cp_flag,
x_scope_rul_sequence_num => l_cur_ftci_details.scope_rul_sequence_num,
x_elm_rng_order_name => l_cur_ftci_details.elm_rng_order_name
);
END update_ftci_rev_acc_cd;
Modified igs_fi_control_pkg.update_row by adding two new columns
post_waiver_gl_flag, waiver_notify_finaid_flag
gurprsin 02-Jun-2005 Enh#3442712, Modified calls to igs_fi_accts_pkg.get_uk2_for_validation by adding 4 new unit level parameters
insert_ftci_accounts and updt_ftci_acct_info method
pmarada 19-Nov-2004 Bug 4017841, Removed the obsoleted res_dt_alias column reference from igs_fi_control table update row
uudayapr 23-dec-2003 Enh#3167098 Removed Column Name PRG_CHG_DT_ALIAS and added RES_DT_ALIAS in
igs_fi_control_pkg.update_row
shtatiko 12-JUN-2003 Enh# 2831582, Removed Obsoleted columns from igs_fi_control_pkg.update_row
-------------------------------------------------------------------*/
CURSOR cur_ctrl IS
SELECT fc.rowid, fc.*
FROM igs_fi_control_all fc;
SELECT 'x'
FROM igs_fi_ftci_accts
WHERE fee_type = cp_v_fee_type
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_seq_number;
SELECT entity_type_code
FROM igs_fi_hier_acct_tbl
WHERE acct_hier_id = cp_n_acct_hier_id
ORDER BY order_sequence;
SELECT course_cd, version_number, rev_account_cd
FROM igs_ps_ver
WHERE rev_account_cd IS NOT NULL
ORDER BY course_cd, version_number;
SELECT unit_cd, version_number, rev_account_cd
FROM igs_ps_unit_ver
WHERE rev_account_cd IS NOT NULL
ORDER BY unit_cd, version_number;
SELECT uoo_id, rev_account_cd
FROM igs_ps_unit_ofr_opt
WHERE rev_account_cd IS NOT NULL
ORDER BY unit_cd, version_number, cal_type, ci_sequence_number, location_cd, unit_class;
SELECT location_cd, rev_account_cd
FROM igs_ad_location
WHERE rev_account_cd IS NOT NULL
ORDER BY location_cd;
SELECT org_unit_cd, rev_account_cd
FROM igs_or_unit_acct_cd
WHERE rev_account_cd IS NOT NULL
ORDER BY org_unit_cd;
SELECT entity_type_code
FROM igs_fi_hier_acct_tbl
WHERE acct_hier_id = cp_acct_hier_id
AND entity_type_code IN ('FTCI','SA')
ORDER BY order_sequence;
SELECT COUNT(entity_type_code)
FROM igs_fi_hier_acct_tbl
WHERE acct_hier_id = cp_acct_hier_id
AND entity_type_code IN ('FTCI','SA');
insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
p_n_order_sequence => l_n_seq,
p_n_natural_account_segment => NULL,
p_v_rev_account_cd => l_v_rev_account_cd,
p_v_location_cd => NULL,
p_v_attendance_type => NULL,
p_v_attendance_mode => NULL,
p_v_course_cd => NULL,
p_n_crs_version_number => NULL,
p_v_unit_cd => NULL,
p_n_unit_version_number => NULL,
p_v_org_unit_cd => NULL,
p_v_residency_status_cd => NULL,
p_n_uoo_id => NULL,
p_v_unit_level => NULL,
p_n_unit_type_id => NULL,
p_v_unit_mode => NULL,
p_v_unit_class => NULL);
insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
p_n_order_sequence => l_n_seq,
p_n_natural_account_segment => NULL,
p_v_rev_account_cd => prg_tbl(i).rev_account_cd,
p_v_location_cd => NULL,
p_v_attendance_type => NULL,
p_v_attendance_mode => NULL,
p_v_course_cd => prg_tbl(i).course_cd,
p_n_crs_version_number => prg_tbl(i).version_number,
p_v_unit_cd => NULL,
p_n_unit_version_number => NULL,
p_v_org_unit_cd => NULL,
p_v_residency_status_cd => NULL,
p_n_uoo_id => NULL,
p_v_unit_level => NULL,
p_n_unit_type_id => NULL,
p_v_unit_mode => NULL,
p_v_unit_class => NULL);
insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
p_n_order_sequence => l_n_seq,
p_n_natural_account_segment => NULL,
p_v_rev_account_cd => unit_tbl(i).rev_account_cd,
p_v_location_cd => NULL,
p_v_attendance_type => NULL,
p_v_attendance_mode => NULL,
p_v_course_cd => NULL,
p_n_crs_version_number => NULL,
p_v_unit_cd => unit_tbl(i).unit_cd,
p_n_unit_version_number => unit_tbl(i).version_number,
p_v_org_unit_cd => NULL,
p_v_residency_status_cd => NULL,
p_n_uoo_id => NULL,
p_v_unit_level => NULL,
p_n_unit_type_id => NULL,
p_v_unit_mode => NULL,
p_v_unit_class => NULL);
insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
p_n_order_sequence => l_n_seq,
p_n_natural_account_segment => NULL,
p_v_rev_account_cd => usec_tbl(i).rev_account_cd,
p_v_location_cd => NULL,
p_v_attendance_type => NULL,
p_v_attendance_mode => NULL,
p_v_course_cd => NULL,
p_n_crs_version_number => NULL,
p_v_unit_cd => NULL,
p_n_unit_version_number => NULL,
p_v_org_unit_cd => NULL,
p_v_residency_status_cd => NULL,
p_n_uoo_id => usec_tbl(i).uoo_id,
p_v_unit_level => NULL,
p_n_unit_type_id => NULL,
p_v_unit_mode => NULL,
p_v_unit_class => NULL);
insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
p_n_order_sequence => l_n_seq,
p_n_natural_account_segment => NULL,
p_v_rev_account_cd => loc_tbl(i).rev_account_cd,
p_v_location_cd => loc_tbl(i).location_cd,
p_v_attendance_type => NULL,
p_v_attendance_mode => NULL,
p_v_course_cd => NULL,
p_n_crs_version_number => NULL,
p_v_unit_cd => NULL,
p_n_unit_version_number => NULL,
p_v_org_unit_cd => NULL,
p_v_residency_status_cd => NULL,
p_n_uoo_id => NULL,
p_v_unit_level => NULL,
p_n_unit_type_id => NULL,
p_v_unit_mode => NULL,
p_v_unit_class => NULL);
insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
p_n_order_sequence => l_n_seq,
p_n_natural_account_segment => NULL,
p_v_rev_account_cd => org_tbl(i).rev_account_cd,
p_v_location_cd => NULL,
p_v_attendance_type => NULL,
p_v_attendance_mode => NULL,
p_v_course_cd => NULL,
p_n_crs_version_number => NULL,
p_v_unit_cd => NULL,
p_n_unit_version_number => NULL,
p_v_org_unit_cd => org_tbl(i).org_unit_cd,
p_v_residency_status_cd => NULL,
p_n_uoo_id => NULL,
p_v_unit_level => NULL,
p_n_unit_type_id => NULL,
p_v_unit_mode => NULL,
p_v_unit_class => NULL);
update_ftci_rev_acc_cd(l_cur_ftci,l_cur_ctrl.rev_account_cd);
update_ftci_rev_acc_cd(l_cur_ftci,l_cur_ctrl.rev_account_cd);
update_ftci_rev_acc_cd(l_cur_ftci,l_cur_ctrl.rev_account_cd);
igs_fi_control_pkg.update_row(x_rowid => l_cur_ctrl.rowid,
x_rec_installed => l_cur_ctrl.rec_installed,
x_accounting_method => l_cur_ctrl.accounting_method,
x_set_of_books_id => l_cur_ctrl.set_of_books_id,
x_refund_dr_gl_ccid => l_cur_ctrl.refund_dr_gl_ccid,
x_refund_cr_gl_ccid => l_cur_ctrl.refund_cr_gl_ccid,
x_refund_dr_account_cd => l_cur_ctrl.refund_dr_account_cd,
x_refund_cr_account_cd => l_cur_ctrl.refund_cr_account_cd,
x_refund_dt_alias => l_cur_ctrl.refund_dt_alias,
x_fee_calc_mthd_code => l_cur_ctrl.fee_calc_mthd_code,
x_planned_credits_ind => l_cur_ctrl.planned_credits_ind,
x_rec_gl_ccid => l_cur_ctrl.rec_gl_ccid,
x_cash_gl_ccid => l_cur_ctrl.cash_gl_ccid,
x_unapp_gl_ccid => l_cur_ctrl.unapp_gl_ccid,
x_rec_account_cd => l_cur_ctrl.rec_account_cd,
x_rev_account_cd => l_cur_ctrl.rev_account_cd,
x_cash_account_cd => l_cur_ctrl.cash_account_cd,
x_unapp_account_cd => l_cur_ctrl.unapp_account_cd,
x_conv_process_run_ind => l_cur_ctrl.conv_process_run_ind,
x_currency_cd => l_cur_ctrl.currency_cd,
x_rfnd_destination => l_cur_ctrl.rfnd_destination,
x_ap_org_id => l_cur_ctrl.ap_org_id,
x_dflt_supplier_site_name => l_cur_ctrl.dflt_supplier_site_name,
x_manage_accounts => l_cur_ctrl.manage_accounts,
x_acct_conv_flag => 'Y',
x_post_waiver_gl_flag => l_cur_ctrl.post_waiver_gl_flag,
x_waiver_notify_finaid_flag => l_cur_ctrl.waiver_notify_finaid_flag
);