[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert values for rates and schedules at FTCI level when they have already been defined at FCFL level and vice versa.
Also, added code in finp_prc_fee_rollvr, finp_ins_roll_ftci, finp_ins_roll_fcci and finp_ins_roll_fcfl
so that process ends in Warning if validation fails.
akandreg 02-Dec-2005 Bug 4747757. Added cursor cur_chk_version in function finp_ins_roll_uft to
handle the issue of rolling over a unit fee trigger when version is
not specified at FCFL level.
akandreg 10-Nov-2005 Bugs 4680440 , 4232201 - validation on Charge Method/Rule Seq Num at FCFL level
Before rolling over an FTCI, Charge Method/Rule Seq Num is validated whether source FTCI has a value.
This is done even for FCFL.
svuppala 09-Sep-2005 Bug 3822813 - The setting of variable l_b_fcfl_not_found to TRUE is removed
in finp_ins_roll_ftci
gurprsin 29-Aug-2005 Bug 3392088, Added max_chg_elements column to the IGS_FI_F_TYP_CA_INST_PKG.Insert_Row
svuppala 22-Aug-2005 Enh 3392095, Added waiver_calc_flag column to the IGS_FI_F_CAT_FEE_LBL_Pkg.Insert_Row
gurprsin 28-Jun-2005 Bug# 3392088 Modified the rollover process to incorporate
sub element ranges and rates table rollover.
svuppala 03-Jun-2005 Enh# 3442712 - Modified TBH calls to table IGS_FI_FEE_AS_RATE to include
5 new Unit Level Attributes in insert_row method
gurprsin 03-Jun-2005 Enh# 3442712 - Modified TBH calls to table IGS_FI_FTCI_ACCTS to include 4 new Unit Level Attributes i.e. in insert_row method
svuppala 11-Mar-2004 Bug 4224379 - Changed the function 'finp_ins_roll_uft'.
New cursor 'c_alt_cd' is created to get "Alternate code" from
igs_ca_inst_all and to send as a token in IGS_FI_ROLLOVER_UFT_ERROR.
Added an EXCEPTION to log a message in case of rolling over failure.
agairola 13-Sep-2004 Bug 3316063 - Retention Enhancements Build
pathipat 12-Jul-2004 Bug 3759552 - Added code to roll over Fee Trigger groups, Unit Fee Triggers and Unit Set Fee Triggers
Added functions for the same and corresponding calls in finp_ins_roll_fcfl().
Bug 3771151 - Removed references to log table IGS_GE_S_LOG_ENTRY and code to log - IGS_GE_GEN_003.GENP_INS_LOG_ENTRY
Bug 3771163 - Removed logging of message IGS_FI_FTCI_NO_REC_FOUND when FTCI has already been rolled over
uudayapr 16-oct-2003 Enh 3117341 Modified finp_ins_roll_ftci Procedure as a part of audit and special fees build.
pathipat 11-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
Modified finp_ins_roll_far() - TBH call igs_fi_fee_as_rate_pkg modified
pathipat 26-Jun-2003 Bug:2992967 - Table validation value set for segments
Modified finp_prc_fee_rollvr() and finpl_ins_roll_over_ftci_accts()
shtatiko 26-MAY-2003 Enh# 2831572, Added procedures log_parameters and finpl_ins_roll_over_ftci_accts.
Modified procedures finp_ins_roll_ftci, finp_ins_roll_fcci and finp_prc_fee_rollvr.
shtatiko 25-APR-2003 Enh# 2831569, Modified finp_prc_fee_rollvr and finp_ins_roll_anc
pathipat 24-Jan-2003 Bug:2765199 - Modified finp_prc_fee_rollvr
Raised l_e_user_Exception instead of app_Exception.raise_exception
when validations fail and process has to error out, so that 'Procedure raised unhandled exception'
is avoided when a proper error message has been logged.
Removed exception sections in finpl_chk_fss,finp_ins_roll_fcci, finp_ins_roll_frtns, finp_ins_roll_fcfl
finp_ins_roll_far, finp_ins_roll_er, finp_ins_roll_ctft, finp_ins_roll_cgft, finp_ins_roll_cft,
finp_ins_roll_anc
Modified exception section in finp_ins_roll_ftci
npalanis 23-OCT-2002 Bug : 2608360
residency_status_id parameter is changed to residency_status_cd
vvutukur 26-Aug-2002 Bug#2531390. Modifications done in functions FINP_INS_ROLL_FTCI,FINP_INS_ROLL_FCCI
and FINP_INS_ROLL_FCFL and removed DEFAULT in the package body.
vvutukur 23-Jul-2002 Bug#2425767.Modified functions finp_ins_roll_ftci,finp_ins_roll_fcfl to remove
references to payment_hierarchy_rank and modified function finp_ins_roll_frtns to
remove references to deduction_amount.
vchappid 10-Jun-2002 Bug#2400315, When the Fee Cat is passed as a parameter and for the source fee period if there are no fee libility
defined then the process should log a customized message
vchappid 29-May-2002 Bug#2372030, Function 'finp_ins_roll_anc' was returning FALSE when there are no records in the Ancillary Tables.
Should log the error message and should continue with the next rollover category or record.
Function 'finp_ins_roll_anc' should be invoked only if the system fee type is 'ANCILLARY'
Bug#2384909, Since Function 'finp_ins_roll_anc' was returning FALSE, next rollover category 'finp_ins_roll_revseg'
was not getting processed.
Bug# 2384909, Checking and Inserting the segments from the table igs_fi_f_type_accts is being done based on
fee_cal_type and fee_ci_sequence_number disregarding the fee type. When the same fee_cal_type and
fee_ci_sequence_number in different fee types then as many records are inserted, this function was returning FALSE
when the record is already exists, returning flase will terminate the process. Process should log the message and
should continue with next rollover categories
vchappid 25-Apr-2002 Bug#2329407, removed the reference to the fin_cal_type, fin_ci_sequence_number from the view IGS_FI_F_TYP_CA_INST
Reference to the Financial Calendar is removed as a part of SFCR005 Build. Removed the parameters account_cd, fin_cal_type
and fin_ci_sequence_number from the function call finp_val_ftci_rqrd
schodava 06-Feb-2002 Enh # 2187247
SFCR021 : FCI-LCI Relation
Removed the function for Charge Method Apportion rollover finp_ins_roll_cma
Sarakshi 15-Jan-2002 In function finp_ins_roll_ftci,removed the reference of subaccount_id from cursor c_ftci_fss
also from the insert_row of igs_fi_f_typ_ca_inst_pkg.Bug:2175865
sarakshi 19-Nov-2001 Added column ret_account_cd,ret_gl_ccid in the select list of cur c_ftci_fss also in
the call to the insert row of igs_fi_f_typ_ca_inst_pkg
as a prt of sfcr012, bug:2113459
schodava 3-Sep-2001 Bug : 1966961
Obsolete Items CCR
Removed references of the Account Code link to Financial Calendar
Also removed the function finp_ins_roll_fps and calls to it, as the payment schedules
functionality is replaced by the New Billing functionality.
Who When What
vchappid 17-Aug-2001 Ref: BugNo:1802900, A global X_ROWID has been defined in the package which is passed
into the INSERT_ROW TBH calls. This has been removed and variable l_rowid local to the
procedures is passed into the TBH calls. Explicit ROLLBACK is added in the outer most
procedure since Concurrent Manager is COMMITTING data though unhandled exception is
raised. All the calls to IGS_GE_GEN_003.GENP_INS_LOG_ENTRY are commented out, now all
comments are logged into the log file
sykrishn 29november2001 Removed the procedure finp_ins_roll_fe and its calls - as part of obseletion in bug 2126091.
******************************************************************/
l_v_token1_val VARCHAR2(255);
SELECT cat.cal_type
FROM IGS_CA_TYPE cat
WHERE cat.cal_type = p_source_cal_type;
SELECT ci.prior_ci_sequence_number
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_dest_cal_type AND
ci.sequence_number = p_dest_sequence_number;
SELECT fss.s_fee_structure_status
FROM IGS_FI_FEE_STR_STAT fss
WHERE fss.fee_structure_status= cp_fee_structure_status;
|| c_ftci_fss and from the call to IGS_FI_F_TYP_CA_INST_PKG.INSERT_ROW).
----------------------------------------------------------------------------*/
gv_other_detail VARCHAR2(255);
v_ftci_inserted_ind BOOLEAN;
v_insert_record BOOLEAN; -- used for diff bet ins/upd of rec for dest cal type. not reqd now as no upd takes place
SELECT ftci.fee_type,
ftci.fee_type_ci_status,
ftci.start_dt_alias,
ftci.start_dai_sequence_number,
ftci.end_dt_alias,
ftci.end_dai_sequence_number,
ftci.retro_dt_alias,
ftci.retro_dai_sequence_number,
ftci.s_chg_method_type,
ftci.rul_sequence_number,
ftci.initial_default_amount,
-- Added by kkillams ,w.r.t Student Finanace (Finance Accounting) DLD bug#1882122
ftci.acct_hier_id,
ftci.rec_gl_ccid,
ftci.rev_account_cd,
ftci.rec_account_cd,
--Added by Sarakshi,as a part of SFCR012 bug:2113459
ftci.ret_account_cd,
ftci.ret_gl_ccid,
ftci.retention_level_code,
ftci.complete_ret_flag,
--Added by svuppala,as a part of bug:4295379
ftci.nonzero_billable_cp_flag,
--Added by gurprsin,as a part of bug:3392088
ftci.scope_rul_sequence_num,
ftci.elm_rng_order_name,
ftci.max_chg_elements
FROM IGS_FI_F_TYP_CA_INST ftci,
IGS_FI_FEE_STR_STAT fss
WHERE (p_fee_type IS NULL OR
ftci.fee_type = p_fee_type) AND
ftci.fee_cal_type = p_source_cal_type AND
ftci.fee_ci_sequence_number = p_source_sequence_number AND
ftci.fee_type_ci_status = fss.fee_structure_status AND
fss.s_fee_structure_status = cst_active;
SELECT ft.closed_ind, ft.s_fee_type
FROM IGS_FI_FEE_TYPE ft
WHERE ft.fee_type = cp_fee_type;
SELECT 'x'
FROM IGS_FI_F_TYP_CA_INST ftci
WHERE ftci.fee_type = cp_fee_type AND
ftci.fee_cal_type = p_dest_cal_type AND
ftci.fee_ci_sequence_number = p_dest_sequence_number;
SELECT fcfl.fee_cat
FROM IGS_FI_F_CAT_FEE_LBL fcfl
WHERE fcfl.fee_cat = p_fee_cat AND
fcfl.fee_cal_type = p_source_cal_type AND
fcfl.fee_ci_sequence_number = p_source_sequence_number AND
fcfl.fee_type = cp_fee_type;
SELECT DT_ALIAS
FROM IGS_CA_DA_INST
WHERE dt_alias = cp_dt_alias AND
sequence_number = cp_sequence_number AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
v_ftci_inserted_ind := FALSE;
v_insert_record := FALSE;
v_insert_record := FALSE;
v_insert_record := TRUE;
IF (v_process_next_ftci = FALSE AND v_insert_record = TRUE ) THEN
v_valid_dai := TRUE;
IF (v_process_next_ftci = FALSE AND v_insert_record = TRUE ) THEN
IF (p_fee_type_ci_status IS NOT NULL) THEN
v_fee_type_ci_status := p_fee_type_ci_status;
IGS_FI_F_TYP_CA_INST_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_TYPE=>v_ftci_fss_rec.fee_type,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_FEE_TYPE_CI_STATUS=>v_fee_type_ci_status,
X_START_DT_ALIAS=>v_ftci_fss_rec.start_dt_alias,
X_START_DAI_SEQUENCE_NUMBER=>v_ftci_fss_rec.start_dai_sequence_number,
X_END_DT_ALIAS=>v_ftci_fss_rec.end_dt_alias,
X_END_DAI_SEQUENCE_NUMBER=>v_ftci_fss_rec.end_dai_sequence_number,
X_RETRO_DT_ALIAS=>v_ftci_fss_rec.retro_dt_alias,
X_RETRO_DAI_SEQUENCE_NUMBER=>v_ftci_fss_rec.retro_dai_sequence_number,
X_S_CHG_METHOD_TYPE=>v_ftci_fss_rec.s_chg_method_type,
X_RUL_SEQUENCE_NUMBER=>v_ftci_fss_rec.rul_sequence_number,
-- Added by kkillams ,w.r.t Student Finanace (Finance Accounting) DLD bug#1882122
X_ACCT_HIER_ID =>v_ftci_fss_rec.acct_hier_id,
X_REC_GL_CCID =>v_ftci_fss_rec.rec_gl_ccid,
X_REV_ACCOUNT_CD =>v_ftci_fss_rec.rev_account_cd,
X_REC_ACCOUNT_CD =>v_ftci_fss_rec.rec_account_cd,
-- Added by Nishikant , to include the following new field for enhancement bug#1851586
X_INITIAL_DEFAULT_AMOUNT=>v_ftci_fss_rec.initial_default_amount,
X_MODE=>'R',
X_ORG_ID => l_n_org_id,
--Added by sarakshi, as a part of SFCR012, bug:2113459
X_RET_ACCOUNT_CD =>v_ftci_fss_rec.ret_account_cd,
X_RET_GL_CCID =>v_ftci_fss_rec.ret_gl_ccid,
X_RETENTION_LEVEL_CODE => v_ftci_fss_rec.retention_level_code,
X_COMPLETE_RET_FLAG => v_ftci_fss_rec.complete_ret_flag,
--Added by svuppala,as a part of bug:4295379
X_NONZERO_BILLABLE_CP_FLAG => v_ftci_fss_rec.nonzero_billable_cp_flag,
--Added by gurprsin,as a part of bug:3392088
X_SCOPE_RUL_SEQUENCE_NUM => v_ftci_fss_rec.scope_rul_sequence_num,
X_ELM_RNG_ORDER_NAME => v_ftci_fss_rec.elm_rng_order_name,
X_MAX_CHG_ELEMENTS => v_ftci_fss_rec.max_chg_elements
);
v_ftci_inserted_ind := TRUE;
IF (v_ftci_inserted_ind = TRUE OR v_ftci_exists_ind = TRUE ) THEN
-- Identify the Retention Level from FTCI.
l_v_retention_level_code := NVL(v_ftci_fss_rec.retention_level_code,'FEE_PERIOD');
IF (v_insert_record = FALSE OR l_already_rolled) THEN
IF p_fee_cat IS NOT NULL THEN
IF NOT l_fcfl_exists_ind THEN
fnd_file.put_line( fnd_file.LOG, fnd_message.get_string ('IGS', 'IGS_FI_NO_FEE_LIB_FOUND') );
v_fcci_inserted_ind BOOLEAN;
SELECT fcci.fee_cat,
fcci.fee_cat_ci_status,
fcci.start_dt_alias,
fcci.start_dai_sequence_number,
fcci.end_dt_alias,
fcci.end_dai_sequence_number,
fcci.retro_dt_alias,
fcci.retro_dai_sequence_number
FROM IGS_FI_F_CAT_CA_INST fcci,
IGS_FI_FEE_STR_STAT fss,
IGS_FI_FEE_CAT fc
WHERE (p_fee_cat IS NULL OR
fcci.fee_cat = p_fee_cat) AND
fcci.fee_cal_type = p_source_cal_type AND
fcci.fee_ci_sequence_number = p_source_sequence_number AND
fcci.fee_cat_ci_status = fss.fee_structure_status AND
fss.s_fee_structure_status = cst_active AND
fc.fee_cat = fcci.fee_cat AND
fc.closed_ind = 'N';
SELECT fcci.fee_cat
FROM IGS_FI_F_CAT_CA_INST fcci
WHERE fcci.fee_cat= cp_fee_cat AND
fcci.fee_cal_type = p_dest_cal_type AND
fcci.fee_ci_sequence_number = p_dest_sequence_number;
SELECT fcfl.fee_type
FROM IGS_FI_F_CAT_FEE_LBL fcfl
WHERE fcfl.fee_cat = cp_fee_cat AND
fcfl.fee_cal_type = p_source_cal_type AND
fcfl.fee_ci_sequence_number = p_source_sequence_number AND
fcfl.fee_type = p_fee_type;
SELECT dt_alias
FROM IGS_CA_DA_INST
WHERE dt_alias = cp_dt_alias AND
sequence_number = cp_sequence_number AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
v_fcci_inserted_ind := FALSE;
IGS_FI_F_CAT_CA_INST_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_CAT=>v_fcci_fss_rec.fee_cat,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_FEE_CAT_CI_STATUS=>v_fee_cat_ci_status,
X_START_DT_ALIAS=>v_fcci_fss_rec.start_dt_alias,
X_START_DAI_SEQUENCE_NUMBER=>v_fcci_fss_rec.start_dai_sequence_number,
X_END_DT_ALIAS=>v_fcci_fss_rec.end_dt_alias,
X_END_DAI_SEQUENCE_NUMBER=>v_fcci_fss_rec.end_dai_sequence_number,
X_RETRO_DT_ALIAS=>v_fcci_fss_rec.retro_dt_alias,
X_RETRO_DAI_SEQUENCE_NUMBER=>v_fcci_fss_rec.retro_dai_sequence_number,
X_MODE=>'R'
);
v_fcci_inserted_ind := TRUE;
IF (v_fcci_inserted_ind = TRUE OR v_fcci_exists_ind = TRUE) THEN
-- rollover related fee retention schedule
IF (finp_ins_roll_frtns(
p_source_cal_type,
p_source_sequence_number,
p_dest_cal_type,
p_dest_sequence_number,
cst_fcci,
NULL,
v_fcci_fss_rec.fee_cat,
v_message_name) = FALSE) THEN
p_message_name := v_message_name;
|| sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate error message logged.
|| sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into IGS_FI_FEE_RET_SCHD
|| is now rounded off to currency precision
|| sapanigr 29-Mar-2006 Bug# 4606670 Added check finp_val_frtns_creat. This validates that
|| when schedule defined at FTCI level, they cannot also be
|| defined at FCFL level and vice-versa.
|| pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
|| vvutukur 24-Jul-2002 Bug#2425767.Removed deduction_amount from select of cursor c_frtns_source,
|| removed x_deduction_amount parameter from call to IGS_FI_FEE_RET_SCHD_PKG.INSERT_ROW.
----------------------------------------------------------------------------*/
gv_other_detail VARCHAR2(255);
v_record_inserted_ind BOOLEAN;
v_valid_insert BOOLEAN;
SELECT frtns.sequence_number,
frtns.fee_type,
frtns.fee_cat,
frtns.schedule_number,
frtns.dt_alias,
frtns.dai_sequence_number,
frtns.retention_percentage,
frtns.retention_amount
FROM IGS_FI_FEE_RET_SCHD frtns
WHERE frtns.fee_cal_type = p_source_cal_type AND
frtns.fee_ci_sequence_number = p_source_sequence_number AND
frtns.s_relation_type = p_relation_type AND
(frtns.fee_type = p_fee_type OR
p_fee_type IS NULL) AND
(frtns.fee_cat = p_fee_cat OR
p_fee_cat IS NULL);
SELECT frtns.fee_type,
frtns.fee_cat,
frtns.schedule_number
FROM IGS_FI_FEE_RET_SCHD frtns
WHERE frtns.fee_cal_type = p_dest_cal_type AND
frtns.fee_ci_sequence_number = p_dest_sequence_number AND
frtns.s_relation_type = p_relation_type AND
frtns.sequence_number = cp_sequence_number;
SELECT frtns.sequence_number
FROM IGS_FI_FEE_RET_SCHD frtns
WHERE frtns.fee_cal_type = p_dest_cal_type AND
frtns.fee_ci_sequence_number = p_dest_sequence_number AND
NVL(frtns.fee_type, 'NULL') = NVL(cp_fee_type, 'NULL') AND
NVL(frtns.fee_cat, 'NULL') = NVL(cp_fee_cat, 'NULL') AND
frtns.schedule_number = cp_schedule_number;
SELECT dt_alias
FROM IGS_CA_DA_INST
WHERE dt_alias = cp_dt_alias AND
sequence_number = cp_sequence_number AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
v_record_inserted_ind := FALSE;
v_valid_insert := TRUE;
v_valid_insert := FALSE;
v_valid_insert := FALSE;
IF v_valid_insert THEN
BEGIN
-- When schedule to be defined at FTCI level, proceed only if not
-- defined at FCFL level and vice-versa.
IF (l_b_ftci_fcci_clash_ind) THEN
IF IGS_FI_VAL_FRTNS.finp_val_frtns_creat(
p_fee_type,
p_dest_cal_type,
p_dest_sequence_number,
p_relation_type,
v_message_name) THEN
l_rowid := NULL;-- initialise l_rowid to null before passing into the TBH
IGS_FI_FEE_RET_SCHD_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_SEQUENCE_NUMBER=>v_frtns_source_rec.sequence_number,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_S_RELATION_TYPE=>p_relation_type,
X_FEE_CAT=>v_frtns_source_rec.fee_cat,
X_FEE_TYPE=>v_frtns_source_rec.fee_type,
X_SCHEDULE_NUMBER=>v_frtns_source_rec.schedule_number,
X_DT_ALIAS=>v_frtns_source_rec.dt_alias,
X_DAI_SEQUENCE_NUMBER=>v_frtns_source_rec.dai_sequence_number,
X_RETENTION_PERCENTAGE=>v_frtns_source_rec.retention_percentage,
X_RETENTION_AMOUNT=>igs_fi_gen_gl.get_formatted_amount(v_frtns_source_rec.retention_amount),
X_MODE=>'R'
);
v_record_inserted_ind := TRUE;
IF (v_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEERET_SCH_ROLLED'));
|| sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate error message logged.
|| sapanigr 29-Mar-2006 Bug 4606670. Out parameter p_message_name assigned dummy value cst_warning
|| if calls to finp_ins_roll_frtns or finp_ins_roll_far returns this value.
|| akandreg 10-Nov-2005 Bugs 4680440 , 4232201 - Validation on Charge Method/Rule Seq Num at FCFL level
|| Before rolling over an FCFL, validate whether Charge Method/Rule Seq Num has a value at source.
|| svuppala 22-Aug-2005 Enh 3392095, Added waiver_calc_flag column to the IGS_FI_F_CAT_FEE_LBL_Pkg.Insert_Row
|| Modified cursor c_fcfl_fss to get waiver_calc_flag
|| pathipat 12-Jul-2004 Bug 3759552 - Added code to roll over Fee Trig Groups, Unit Triggers
|| and Unit Set Triggers.
|| pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
|| vvutukur 26-Aug-2002 Bug#2531390.The comment in the code regarding the rollover of fee payment
|| schedules is removed to avoid confusion.
|| vvutukur 23-Jul-2002 Bug#2425767.Removed references to payment_hierarchy_rank(from cursor
|| c_fcfl_fss and from the call to IGS_FI_F_CAT_FEE_LBL_PKG.INSERT_ROW).
----------------------------------------------------------------------------*/
gv_other_detail VARCHAR2(255);
v_fcfl_inserted_ind BOOLEAN;
SELECT fcfl.fee_type,
fcfl.fee_liability_status,
fcfl.start_dt_alias,
fcfl.start_dai_sequence_number,
fcfl.s_chg_method_type,
fcfl.rul_sequence_number,
fcfl.fee_cat,
fcfl.waiver_calc_flag
FROM IGS_FI_F_CAT_FEE_LBL fcfl,
IGS_FI_FEE_STR_STAT fss
WHERE fcfl.fee_cat = p_fee_cat AND
fcfl.fee_cal_type = p_source_cal_type AND
fcfl.fee_ci_sequence_number = p_source_sequence_number AND
(fcfl.fee_type = p_fee_type OR
p_fee_type IS NULL) AND
fcfl.fee_liability_status = fss.fee_structure_status AND
fss.s_fee_structure_status = cst_active;
SELECT fcfl.fee_type
FROM IGS_FI_F_CAT_FEE_LBL fcfl
WHERE fcfl.fee_cat = p_fee_cat AND
fcfl.fee_cal_type = p_dest_cal_type AND
fcfl.fee_ci_sequence_number = p_dest_sequence_number AND
fcfl.fee_type = cp_fee_type;
SELECT ftci.fee_type
FROM IGS_FI_F_TYP_CA_INST ftci
WHERE ftci.fee_type = cp_fee_type AND
ftci.fee_cal_type = p_dest_cal_type AND
ftci.fee_ci_sequence_number = p_dest_sequence_number;
SELECT dt_alias
FROM IGS_CA_DA_INST
WHERE dt_alias = cp_dt_alias AND
sequence_number = cp_sequence_number AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
v_fcfl_inserted_ind := FALSE;
IGS_FI_F_CAT_FEE_LBL_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_CAT=>p_fee_cat,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_FEE_TYPE=>v_fcfl_fss_rec.fee_type,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_LIABILITY_STATUS=>v_fee_liability_status,
X_START_DT_ALIAS=>v_fcfl_fss_rec.start_dt_alias,
X_START_DAI_SEQUENCE_NUMBER=>v_fcfl_fss_rec.start_dai_sequence_number,
X_S_CHG_METHOD_TYPE=>v_fcfl_fss_rec.s_chg_method_type,
X_RUL_SEQUENCE_NUMBER=>v_fcfl_fss_rec.rul_sequence_number,
X_WAIVER_CALC_FLAG => v_fcfl_fss_rec.waiver_calc_flag,
X_MODE=>'R'
);
v_fcfl_inserted_ind := TRUE;
IF (v_fcfl_inserted_ind = TRUE OR v_fcfl_exists_ind = TRUE) THEN
-- rollover related fee retention schedule
IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_frtns(
p_source_cal_type,
p_source_sequence_number,
p_dest_cal_type,
p_dest_sequence_number,
cst_fcfl,
v_fcfl_fss_rec.fee_type,
v_fcfl_fss_rec.fee_cat,
v_message_name) = FALSE) THEN
p_message_name := v_message_name;
v_record_inserted_ind BOOLEAN;
v_logical_delete_dt IGS_FI_FEE_AS_RATE.logical_delete_dt%TYPE;
SELECT far.fee_type,
far.rate_number,
far.fee_cat,
far.location_cd,
far.attendance_type,
far.attendance_mode,
far.order_of_precedence,
far.govt_hecs_payment_option,
far.govt_hecs_cntrbtn_band,
far.chg_rate,
far.unit_class,
far.residency_status_cd,
far.course_cd,
far.version_number,
far.org_party_id,
far.class_standing,
far.unit_set_cd,
far.us_version_number,
far.unit_cd,
far.unit_version_number,
far.unit_level,
far.unit_type_id,
far.unit_mode
FROM IGS_FI_FEE_AS_RATE far
WHERE far.fee_type = p_fee_type AND
far.fee_cal_type = p_source_cal_type AND
far.fee_ci_sequence_number = p_source_sequence_number AND
far.s_relation_type = p_relation_type AND
far.logical_delete_dt is NULL AND
(far.fee_cat = p_fee_cat OR p_fee_cat IS NULL);
SELECT far.rate_number,
far.logical_delete_dt
FROM IGS_FI_FEE_AS_RATE far
WHERE far.fee_type = p_fee_type AND
far.fee_cal_type = p_dest_cal_type AND
far.fee_ci_sequence_number = p_dest_sequence_number AND
far.s_relation_type = p_relation_type AND
far.rate_number = cp_rate_number;
v_record_inserted_ind := FALSE;
v_logical_delete_dt;
IF (v_logical_delete_dt IS NULL) THEN
v_record_exists_ind := TRUE;
IGS_FI_FEE_AS_RATE_PKG.INSERT_ROW(
X_ROWID => l_rowid,
x_FAR_ID => v_FAR_ID,
X_FEE_TYPE => v_far_source_rec.fee_type,
X_FEE_CAL_TYPE => p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER => p_dest_sequence_number,
X_S_RELATION_TYPE => p_relation_type,
X_RATE_NUMBER => v_far_source_rec.rate_number,
X_FEE_CAT => v_far_source_rec.fee_cat,
X_LOCATION_CD => v_far_source_rec.location_cd,
X_ATTENDANCE_TYPE => v_far_source_rec.attendance_type,
X_ATTENDANCE_MODE => v_far_source_rec.attendance_mode,
X_ORDER_OF_PRECEDENCE => v_far_source_rec.order_of_precedence,
X_GOVT_HECS_PAYMENT_OPTION => v_far_source_rec.govt_hecs_payment_option,
X_GOVT_HECS_CNTRBTN_BAND => v_far_source_rec.govt_hecs_cntrbtn_band,
X_CHG_RATE => v_far_source_rec.chg_rate,
X_LOGICAL_DELETE_DT => NULL,
X_RESIDENCY_STATUS_CD => v_far_source_rec.residency_status_cd,
X_COURSE_CD => v_far_source_rec.course_cd,
X_VERSION_NUMBER => v_far_source_rec.version_number,
X_ORG_PARTY_ID => v_far_source_rec.org_party_id,
X_CLASS_STANDING => v_far_source_rec.class_standing,
X_MODE => 'R',
x_unit_set_cd => v_far_source_rec.unit_set_cd,
x_us_version_number => v_far_source_rec.us_version_number,
x_unit_cd => v_far_source_rec.unit_cd,
x_unit_version_number => v_far_source_rec.unit_version_number,
x_unit_level => v_far_source_rec.unit_level ,
x_unit_type_id => v_far_source_rec.unit_type_id,
x_unit_class => v_far_source_rec.unit_class ,
x_unit_mode => v_far_source_rec.unit_mode
);
v_record_inserted_ind := TRUE;
IF (v_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_FEEASS_RATES_ROLLED'));
v_er_record_inserted_ind BOOLEAN;
v_err_record_inserted_ind BOOLEAN;
v_logical_delete_dt IGS_FI_ELM_RANGE.logical_delete_dt%TYPE;
v_ser_logical_delete_dt IGS_FI_SUB_ELM_RNG.logical_delete_date%TYPE;
v_sert_logical_delete_dt IGS_FI_SUB_ER_RT.logical_delete_date%TYPE;
v_ser_record_inserted_ind BOOLEAN;
v_sert_record_inserted_ind BOOLEAN;
SELECT er.range_number,
er.fee_cat,
er.lower_range,
er.upper_range,
er.s_chg_method_type,
er.er_id
FROM IGS_FI_ELM_RANGE er
WHERE er.fee_type = p_fee_type AND
er.fee_cal_type = p_source_cal_type AND
er.fee_ci_sequence_number = p_source_sequence_number AND
er.s_relation_type = p_relation_type AND
er.logical_delete_dt is NULL AND
(fee_cat = p_fee_cat OR
p_fee_cat IS NULL);
SELECT er.er_id,
er.range_number,
er.logical_delete_dt
FROM IGS_FI_ELM_RANGE er
WHERE er.fee_type = p_fee_type AND
er.fee_cal_type = p_dest_cal_type AND
er.fee_ci_sequence_number = p_dest_sequence_number AND
er.s_relation_type = p_relation_type AND
er.range_number = cp_range_number;
SELECT err.rate_number,
err.fee_cat
FROM IGS_FI_ELM_RANGE_RT err
WHERE err.fee_type = p_fee_type AND
err.fee_cal_type = p_source_cal_type AND
err.fee_ci_sequence_number = p_source_sequence_number AND
err.s_relation_type = p_relation_type AND
err.range_number = cp_range_number AND
err.logical_delete_dt IS NULL;
SELECT err.range_number,
err.logical_delete_dt
FROM IGS_FI_ELM_RANGE_RT err
WHERE err.fee_type = p_fee_type AND
err.fee_cal_type = p_dest_cal_type AND
err.fee_ci_sequence_number = p_dest_sequence_number AND
err.s_relation_type = p_relation_type AND
err.range_number = cp_range_number AND
err.rate_number = cp_rate_number;
SELECT er_id,
sub_er_id,
sub_range_num,
sub_chg_method_code,
sub_lower_range,
sub_upper_range
FROM IGS_FI_SUB_ELM_RNG ser
WHERE ser.er_id = cp_er_id AND
ser.logical_delete_date IS NULL;
SELECT sub_er_id,logical_delete_date
FROM IGS_FI_SUB_ELM_RNG ser
WHERE ser.er_id = cp_er_id AND
ser.sub_range_num = cp_sub_range_num;
SELECT sub_er_id,
far_id,
create_date
FROM IGS_FI_SUB_ER_RT sert
WHERE sert.sub_er_id = cp_sub_er_id AND
sert.logical_delete_date IS NULL;
SELECT 'X'
FROM IGS_FI_SUB_ER_RT sert
WHERE sert.sub_er_id = cp_sub_er_id AND
sert.far_id = cp_far_id;
SELECT 'X'
FROM IGS_FI_ELM_RANGE_RT err
WHERE err.fee_type = p_fee_type AND
err.fee_cal_type = p_source_cal_type AND
err.fee_ci_sequence_number = p_source_sequence_number AND
err.s_relation_type = p_relation_type AND
(err.fee_cat = p_fee_cat OR p_fee_cat IS NULL) AND
err.logical_delete_dt IS NULL;
SELECT 'X'
FROM IGS_FI_SUB_ER_RT serr,
IGS_FI_FEE_AS_RATE far
WHERE far.fee_type = p_fee_type AND
far.fee_cal_type = p_source_cal_type AND
far.fee_ci_sequence_number = p_source_sequence_number AND
far.s_relation_type = p_relation_type AND
(far.fee_cat = p_fee_cat OR p_fee_cat IS NULL) AND
far.far_id = serr.far_id AND
serr.logical_delete_date IS NULL;
SELECT 'X'
FROM IGS_FI_FEE_AS_RATE far
WHERE far.fee_type = p_fee_type AND
far.fee_cal_type = p_dest_cal_type AND
far.fee_ci_sequence_number = p_dest_sequence_number AND
far.s_relation_type = p_relation_type AND
(far.fee_cat = p_fee_cat OR p_fee_cat IS NULL) AND
far.logical_delete_dt IS NULL;
SELECT rate_number
FROM IGS_FI_FEE_AS_RATE far
WHERE far.far_id = cp_far_id;
v_er_record_inserted_ind := FALSE;
v_err_record_inserted_ind := FALSE;
v_sert_record_inserted_ind := FALSE;
v_ser_record_inserted_ind := FALSE;
v_logical_delete_dt;
IF (v_logical_delete_dt IS NULL) THEN
v_er_record_exists_ind := TRUE;
IGS_FI_EL_RNG_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_ER_ID => v_er_id,
X_FEE_TYPE=>p_fee_type,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_S_RELATION_TYPE=>p_relation_type,
X_RANGE_NUMBER=>v_er_source_rec.range_number,
X_FEE_CAT=>v_er_source_rec.fee_cat,
X_LOWER_RANGE=>v_er_source_rec.lower_range,
X_UPPER_RANGE=>v_er_source_rec.upper_range,
X_S_CHG_METHOD_TYPE=>v_er_source_rec.s_chg_method_type,
X_LOGICAL_DELETE_DT=>NULL,
X_MODE=>'R'
);
v_er_record_inserted_ind := TRUE;
FETCH c_ser_dest INTO l_sub_er_id,v_ser_logical_delete_dt;
IF (v_ser_logical_delete_dt IS NULL) THEN
v_ser_record_exists_ind := TRUE;
IGS_FI_SUB_ELM_RNG_PKG.INSERT_ROW(
x_rowid=> l_rowid,
x_sub_er_id => l_sub_er_id,
x_er_id => v_er_id,
x_sub_range_num => v_ser_source_rec.sub_range_num,
x_sub_lower_range => v_ser_source_rec.sub_lower_range,
x_sub_upper_range => v_ser_source_rec.sub_upper_range,
x_sub_chg_method_code => v_ser_source_rec.sub_chg_method_code,
x_logical_delete_date => NULL,
x_mode => 'R'
);
v_ser_record_inserted_ind := TRUE;
IF (v_sert_logical_delete_dt IS NULL) THEN
v_sert_record_exists_ind := TRUE;
IGS_FI_SUB_ER_RT_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_sub_err_id => l_sub_err_id,
x_sub_er_id => l_sub_er_id,
x_far_id => v_sert_source_rec.far_id,
x_create_date => v_sert_source_rec.create_date,
x_logical_delete_date => NULL,
x_mode => 'R'
);
v_sert_record_inserted_ind := TRUE;
v_logical_delete_dt;
IF (v_logical_delete_dt IS NULL) THEN
v_err_record_exists_ind := TRUE;
IGS_FI_ELM_RANGE_RT_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_ERR_ID => v_err_id,
X_FEE_TYPE=>p_fee_type,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_S_RELATION_TYPE=>p_relation_type,
X_RANGE_NUMBER=>v_er_source_rec.range_number,
X_RATE_NUMBER=>v_err_source_rec.rate_number,
X_CREATE_DT=>SYSDATE,
X_FEE_CAT=>v_err_source_rec.fee_cat,
X_LOGICAL_DELETE_DT=>NULL,
X_MODE=>'R'
);
v_err_record_inserted_ind := TRUE;
IF (v_er_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ELEMENT_RANGE_ROLLED'));
IF (v_err_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ELERNG_RATES_ROLLED'));
IF (v_ser_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_SER_ROLLED'));
IF (v_sert_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_SERT_ROLLED'));
v_logical_delete_dt IGS_PS_TYPE_FEE_TRG.logical_delete_dt%TYPE;
v_record_inserted_ind BOOLEAN;
SELECT ctft.course_type
FROM IGS_PS_TYPE_FEE_TRG ctft
WHERE ctft.fee_cat = p_fee_cat AND
ctft.fee_cal_type = p_source_cal_type AND
ctft.fee_ci_sequence_number = p_source_sequence_number AND
ctft.fee_type = p_fee_type AND
ctft.logical_delete_dt IS NULL;
SELECT ctft.COURSE_TYPE,
ctft.logical_delete_dt
FROM IGS_PS_TYPE_FEE_TRG ctft
WHERE ctft.fee_cat = p_fee_cat AND
ctft.fee_cal_type = p_dest_cal_type AND
ctft.fee_ci_sequence_number = p_dest_sequence_number AND
ctft.fee_type = p_fee_type AND
ctft.COURSE_TYPE = cp_course_type;
v_record_inserted_ind := FALSE;
v_logical_delete_dt;
IF v_logical_delete_dt IS NULL THEN
v_record_exists_ind := TRUE;
IGS_PS_TYPE_FEE_TRG_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_CAT=>p_fee_cat,
X_FEE_TYPE=>p_fee_type,
X_CREATE_DT=>X_SYSDATE,
X_COURSE_TYPE=>v_ctft_source_rec.course_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_LOGICAL_DELETE_DT=>NULL,
X_MODE=>'R'
);
v_record_inserted_ind := TRUE;
IF (v_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRGTYPE_FEETRG_ROLLED'));
v_logical_delete_dt IGS_PS_GRP_FEE_TRG.logical_delete_dt%TYPE;
v_record_inserted_ind BOOLEAN;
SELECT cgft.course_group_cd
FROM IGS_PS_GRP_FEE_TRG cgft
WHERE cgft.fee_cat = p_fee_cat AND
cgft.fee_cal_type = p_source_cal_type AND
cgft.fee_ci_sequence_number = p_source_sequence_number AND
cgft.fee_type = p_fee_type AND
cgft.logical_delete_dt IS NULL;
SELECT cgft.course_group_cd,
cgft.logical_delete_dt
FROM IGS_PS_GRP_FEE_TRG cgft
WHERE cgft.fee_cat = p_fee_cat AND
cgft.fee_cal_type = p_dest_cal_type AND
cgft.fee_ci_sequence_number = p_dest_sequence_number AND
cgft.fee_type = p_fee_type AND
cgft.course_group_cd = cp_course_group_cd;
v_record_inserted_ind := FALSE;
v_logical_delete_dt;
IF v_logical_delete_dt IS NULL THEN
v_record_exists_ind := TRUE;
IGS_PS_GRP_FEE_TRG_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_CAT=>p_fee_cat,
X_CREATE_DT=>X_SYSDATE,
X_COURSE_GROUP_CD=>v_cgft_source_rec.course_group_cd,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_TYPE=>p_fee_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_LOGICAL_DELETE_DT=>NULL,
X_MODE=>'R'
);
v_record_inserted_ind := TRUE;
IF (v_record_inserted_ind = TRUE) THEN
v_message_name := 'IGS_FI_PRGGRP_FEETRG_ROLLED';
v_logical_delete_dt IGS_PS_FEE_TRG.logical_delete_dt%TYPE;
v_record_inserted_ind BOOLEAN;
SELECT cft.course_cd,
cft.sequence_number,
cft.version_number,
cft.cal_type,
cft.location_cd,
cft.attendance_mode,
cft.attendance_type,
cft.fee_trigger_group_number
FROM IGS_PS_FEE_TRG cft
WHERE cft.fee_cat = p_fee_cat AND
cft.fee_cal_type = p_source_cal_type AND
cft.fee_ci_sequence_number = p_source_sequence_number AND
cft.fee_type = p_fee_type AND
cft.logical_delete_dt IS NULL;
SELECT cft.course_cd,
cft.sequence_number,
cft.logical_delete_dt
FROM IGS_PS_FEE_TRG cft
WHERE cft.fee_cat = p_fee_cat AND
cft.fee_cal_type = p_dest_cal_type AND
cft.fee_ci_sequence_number = p_dest_sequence_number AND
cft.fee_type = p_fee_type AND
cft.course_cd = cp_course_cd AND
cft.sequence_number = cp_sequence_number;
v_record_inserted_ind := FALSE;
v_logical_delete_dt;
IF v_logical_delete_dt IS NULL THEN
v_record_exists_ind := TRUE;
IGS_PS_FEE_TRG_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_FEE_CAT=>p_fee_cat,
X_FEE_TYPE=>p_fee_type,
X_COURSE_CD=>v_cft_source_rec.course_cd,
X_SEQUENCE_NUMBER=>v_cft_source_rec.sequence_number,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_VERSION_NUMBER=>v_cft_source_rec.version_number,
X_CAL_TYPE=>v_cft_source_rec.cal_type,
X_LOCATION_CD=>v_cft_source_rec.location_cd,
X_ATTENDANCE_MODE=>v_cft_source_rec.attendance_mode,
X_ATTENDANCE_TYPE=>v_cft_source_rec.attendance_type,
X_CREATE_DT=>SYSDATE,
X_FEE_TRIGGER_GROUP_NUMBER=>v_cft_source_rec.fee_trigger_group_number,
X_LOGICAL_DELETE_DT=>NULL,
X_MODE=>'R'
);
v_record_inserted_ind := TRUE;
IF (v_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRGFEE_TRG_ROLLED'));
sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
error message logged.
shtatiko 25-APR-2003 Enh# 2831569, Modified code so that function will log the message as soon as
it enocounters error ( IGS_FI_RT_SGMNTS_DS_NT_XS and IGS_FI_ANC_RTS_DS_NT_XS ).
And so function will always returns TRUE.
pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
vvutukur 02-Sep-2002 Bug#2531390.Used assignment operator by removing DEFAULT clause to
avoid gscc warnings.
vchappid 29-May-2002 This function was returning FALSE when there are no records in the Ancillary Tables.
IF false is returned then the process is terminated and will not carry-on with the
Fee Categories rollover.
(reverse chronological order - newest change first)
***************************************************************/
gv_other_detail VARCHAR2(255);
v_record_inserted_ind BOOLEAN;
SELECT *
FROM IGS_FI_ANC_RT_SGMNTS sfars
WHERE sfars.fee_type = p_fee_type AND
sfars.fee_cal_type = p_source_cal_type AND
sfars.fee_ci_sequence_number = p_source_ci_sequence_number;
SELECT *
FROM IGS_FI_ANC_RATES sfnr
WHERE sfnr.fee_type = p_fee_type AND
sfnr.fee_cal_type = p_source_cal_type AND
sfnr.fee_ci_sequence_number = p_source_ci_sequence_number;
SELECT *
FROM IGS_FI_ANC_RT_SGMNTS dfars
WHERE dfars.fee_type = p_fee_type AND
dfars.fee_cal_type = p_dest_cal_type AND
dfars.fee_ci_sequence_number = p_dest_ci_sequence_number;
SELECT *
FROM IGS_FI_ANC_RATES dfnr
WHERE dfnr.fee_type = p_fee_type AND
dfnr.fee_cal_type = p_dest_cal_type AND
dfnr.fee_ci_sequence_number = p_dest_ci_sequence_number;
v_record_inserted_ind := FALSE;
v_record_inserted_ind := TRUE;
IGS_FI_ANC_RT_SGMNTS_PKG.INSERT_ROW(
X_ROWID=>l_rowid,
X_ANC_RATE_SEGMENT_ID=>l_anc_rate_segment_id,
X_FEE_TYPE=>p_fee_type,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_ci_sequence_number,
X_ANCILLARY_ATTRIBUTES=>l_ancillary_attributes,
X_ANCILLARY_SEGMENTS=>v_cur_source_sgmnts_rec.ancillary_segments,
X_ENABLED_FLAG=>v_cur_source_sgmnts_rec.enabled_flag,
X_MODE=>'R'
);
IGS_FI_ANC_RATES_PKG.INSERT_ROW(
X_ROWID=>l_rowid2,
X_ANCILLARY_RATE_ID=>l_ancillary_rate_id,
X_FEE_TYPE=>p_fee_type,
X_FEE_CAL_TYPE=>p_dest_cal_type,
X_FEE_CI_SEQUENCE_NUMBER=>p_dest_ci_sequence_number,
X_ANCILLARY_ATTRIBUTE1=>v_cur_source_rates_rec.ancillary_attribute1,
X_ANCILLARY_ATTRIBUTE2=>v_cur_source_rates_rec.ancillary_attribute2,
X_ANCILLARY_ATTRIBUTE3=>v_cur_source_rates_rec.ancillary_attribute3,
X_ANCILLARY_ATTRIBUTE4=>v_cur_source_rates_rec.ancillary_attribute4,
X_ANCILLARY_ATTRIBUTE5=>v_cur_source_rates_rec.ancillary_attribute5,
X_ANCILLARY_ATTRIBUTE6=>v_cur_source_rates_rec.ancillary_attribute6,
X_ANCILLARY_ATTRIBUTE7=>v_cur_source_rates_rec.ancillary_attribute7,
X_ANCILLARY_ATTRIBUTE8=>v_cur_source_rates_rec.ancillary_attribute8,
X_ANCILLARY_ATTRIBUTE9=>v_cur_source_rates_rec.ancillary_attribute9,
X_ANCILLARY_ATTRIBUTE10=>v_cur_source_rates_rec.ancillary_attribute10,
X_ANCILLARY_ATTRIBUTE11=>v_cur_source_rates_rec.ancillary_attribute11,
X_ANCILLARY_ATTRIBUTE12=>v_cur_source_rates_rec.ancillary_attribute12,
X_ANCILLARY_ATTRIBUTE13=>v_cur_source_rates_rec.ancillary_attribute13,
X_ANCILLARY_ATTRIBUTE14=>v_cur_source_rates_rec.ancillary_attribute14,
X_ANCILLARY_ATTRIBUTE15=>v_cur_source_rates_rec.ancillary_attribute15,
X_ANCILLARY_CHG_RATE=>v_cur_source_rates_rec.ancillary_chg_rate,
X_ENABLED_FLAG=>v_cur_source_rates_rec.enabled_flag,
X_MODE=>'R'
);
IF (v_record_inserted_ind = TRUE) THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ANC_ROLLS'));
sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
error message logged.
vchappid 29-May-2002 Bug# 2384909, Checking and Inserting the segments from the table igs_fi_f_type_accts
is being done based on fee_cal_type and fee_ci_sequence_number disregarding the fee type
When the same fee_cal_type and fee_ci_sequence_number in different fee types then as many
records are inserted,
this function was returning FALSE when the record is already exists, returning flase will terminate
the process. Process should log the message and should continue with next rollover categories
***************************************************************/
CURSOR cur_chk_ex_revsegs IS SELECT 1 FROM igs_fi_f_type_accts
WHERE fee_type = p_fee_type AND
fee_cal_type= p_dest_cal_type AND
fee_ci_sequence_number= p_dest_sequence_number;
CURSOR cur_roll_segs IS SELECT * FROM igs_fi_f_type_accts
WHERE fee_type = p_fee_type AND
fee_cal_type= p_source_cal_type AND
fee_ci_sequence_number= p_source_sequence_number;
igs_fi_f_type_accts_pkg.insert_row (
x_rowid => l_rowid,
x_fee_type_account_id => l_fee_type_accid,
x_fee_type => p_fee_type,
x_fee_cal_type => p_dest_cal_type,
x_fee_ci_sequence_number => p_dest_sequence_number,
x_segment => l_roll_segs.segment,
x_segment_num => l_roll_segs.segment_num,
x_segment_value => l_roll_segs.segment_value,
x_mode => 'R'
);
sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
error message logged.
gurprsin 30-MAY-2005 Enh# 3442712 Added 4 unit level Columns like unit_level,
unit_mode,unit_class,unit_type_id to TBH call of igs_fi_ftci_accts_pkg
in insert row method
pathipat 26-Jun-2003 Bug:2992967 - Table validation value set for segments
Removed cur_rev_account_Seg and its usage. Added call to
fnd_flex_keyval.validate_segs()
***************************************************************/
CURSOR cur_ftci_accts(cp_v_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
cp_n_fee_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
SELECT *
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_fee_ci_seq_number
ORDER BY order_sequence;
SELECT closed_ind
FROM igs_fi_acc
WHERE account_cd = cp_v_rev_acct_cd;
igs_fi_ftci_accts_pkg.insert_row (
x_rowid => l_v_rowid,
x_acct_id => l_n_acct_id,
x_fee_type => l_rec_ftci_accts.fee_type,
x_fee_cal_type => p_v_dest_cal_type,
x_fee_ci_sequence_number => p_n_dest_sequence_number,
x_order_sequence => l_rec_ftci_accts.order_sequence,
x_natural_account_segment => l_v_natural_account_segment,
x_rev_account_cd => l_v_revenue_account_cd,
x_location_cd => l_rec_ftci_accts.location_cd,
x_attendance_type => l_rec_ftci_accts.attendance_type,
x_attendance_mode => l_rec_ftci_accts.attendance_mode,
x_course_cd => l_rec_ftci_accts.course_cd,
x_crs_version_number => l_rec_ftci_accts.crs_version_number,
x_unit_cd => l_rec_ftci_accts.unit_cd,
x_unit_version_number => l_rec_ftci_accts.unit_version_number,
x_org_unit_cd => l_rec_ftci_accts.org_unit_cd,
x_residency_status_cd => l_rec_ftci_accts.residency_status_cd,
x_uoo_id => l_rec_ftci_accts.uoo_id,
x_mode => 'R',
x_unit_level => l_rec_ftci_accts.unit_level,
x_unit_type_id => l_rec_ftci_accts.unit_type_id,
x_unit_mode => l_rec_ftci_accts.unit_mode,
x_unit_class => l_rec_ftci_accts.unit_class
);
sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
error message logged.
***************************************************************/
l_n_fee_trigger_group_number igs_fi_fee_trg_grp.fee_trigger_group_number%TYPE;
l_d_logical_delete_dt igs_fi_fee_trg_grp.logical_delete_dt%TYPE;
l_b_record_inserted_ind BOOLEAN := FALSE;
SELECT fee_trigger_group_number,
description,
comments
FROM igs_fi_fee_trg_grp
WHERE fee_cat = p_fee_cat
AND fee_cal_type = p_source_cal_type
AND fee_ci_sequence_number = p_source_sequence_number
AND fee_type = p_fee_type
AND logical_delete_dt IS NULL;
SELECT fee_trigger_group_number,
logical_delete_dt
FROM igs_fi_fee_trg_grp
WHERE fee_cat = p_fee_cat
AND fee_cal_type = p_dest_cal_type
AND fee_ci_sequence_number = p_dest_sequence_number
AND fee_type = p_fee_type
AND fee_trigger_group_number = cp_fee_trigger_group_number;
l_b_record_inserted_ind := FALSE;
FETCH c_trg_grp_dest INTO l_n_fee_trigger_group_number, l_d_logical_delete_dt;
IF l_d_logical_delete_dt IS NULL THEN
l_b_record_exists_ind := TRUE;
igs_fi_fee_trg_grp_pkg.insert_row( x_rowid => l_rowid,
x_fee_cat => p_fee_cat,
x_fee_trigger_group_number => l_trg_grp_source_rec.fee_trigger_group_number,
x_fee_cal_type => p_dest_cal_type,
x_fee_ci_sequence_number => p_dest_sequence_number,
x_fee_type => p_fee_type,
x_description => l_trg_grp_source_rec.description,
x_logical_delete_dt => NULL,
x_comments => l_trg_grp_source_rec.comments,
x_mode => 'R');
l_b_record_inserted_ind := TRUE;
IF (l_b_record_inserted_ind = TRUE) THEN
-- Display message that the Fee Trigger Group record have been successfully rolled over
fnd_message.set_name('IGS','IGS_FI_TRG_GRP_ROLLED');
l_d_logical_delete_dt igs_fi_unit_fee_trg.logical_delete_dt%TYPE;
l_b_record_inserted_ind BOOLEAN := FALSE;
SELECT uft.unit_cd,
uft.sequence_number,
uft.version_number,
uft.cal_type,
uft.ci_sequence_number,
uft.location_cd,
uft.unit_class,
uft.create_dt,
uft.fee_trigger_group_number
FROM igs_fi_unit_fee_trg uft
WHERE uft.fee_cat = p_fee_cat
AND uft.fee_cal_type = p_source_cal_type
AND uft.fee_ci_sequence_number = p_source_sequence_number
AND uft.fee_type = p_fee_type
AND uft.logical_delete_dt IS NULL;
SELECT cinst.alternate_code
FROM igs_ca_inst_all cinst
WHERE cinst.cal_type = cp_cal_type
AND cinst.sequence_number = cp_ci_sequence_number;
SELECT uft.unit_cd,
uft.sequence_number,
uft.logical_delete_dt
FROM igs_fi_unit_fee_trg uft
WHERE uft.fee_cat = p_fee_cat
AND uft.fee_cal_type = p_dest_cal_type
AND uft.fee_ci_sequence_number = p_dest_sequence_number
AND uft.fee_type = p_fee_type
AND uft.unit_cd = cp_unit_cd
AND uft.sequence_number = cp_sequence_number;
SELECT 'x'
FROM igs_ps_unit_ver psv,
igs_ps_unit_stat stat
WHERE psv.unit_cd = cp_v_unit_cd
AND (psv.version_number = cp_v_unit_version_number OR cp_v_unit_version_number IS NULL)
AND psv.unit_status = stat.unit_status
AND stat.s_unit_status IN ('ACTIVE','PLANNED');
l_b_record_inserted_ind := FALSE;
FETCH c_uft_dest INTO l_v_unit_cd, l_v_sequence_number, l_d_logical_delete_dt;
IF l_d_logical_delete_dt IS NULL THEN
l_b_record_exists_ind := TRUE;
igs_fi_unit_fee_trg_pkg.insert_row( x_rowid => l_rowid,
x_fee_cat => p_fee_cat,
x_fee_cal_type => p_dest_cal_type,
x_fee_ci_sequence_number => p_dest_sequence_number,
x_unit_cd => l_uft_source_rec.unit_cd,
x_sequence_number => l_uft_source_rec.sequence_number,
x_fee_type => p_fee_type,
x_version_number => l_uft_source_rec.version_number,
x_cal_type => l_uft_source_rec.cal_type,
x_ci_sequence_number => l_uft_source_rec.ci_sequence_number,
x_location_cd => l_uft_source_rec.location_cd,
x_unit_class => l_uft_source_rec.unit_class,
x_create_dt => SYSDATE,
x_fee_trigger_group_number => l_uft_source_rec.fee_trigger_group_number,
x_logical_delete_dt => NULL,
x_mode => 'R');
l_b_record_inserted_ind := TRUE;
IF (l_b_record_inserted_ind = TRUE) THEN
-- Display message that the Unit Fee Triggers have been successfully rolled over
fnd_message.set_name('IGS','IGS_FI_UNIT_TRG_ROLLED');
sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
error message logged.
***************************************************************/
l_v_unit_set_cd igs_en_unitsetfeetrg.unit_set_cd%TYPE;
l_d_logical_delete_dt igs_en_unitsetfeetrg.logical_delete_dt%TYPE;
l_b_record_inserted_ind BOOLEAN := FALSE;
SELECT uft.unit_set_cd,
uft.version_number,
uft.fee_trigger_group_number
FROM igs_en_unitsetfeetrg uft
WHERE uft.fee_cat = p_fee_cat
AND uft.fee_cal_type = p_source_cal_type
AND uft.fee_ci_sequence_number = p_source_sequence_number
AND uft.fee_type = p_fee_type
AND uft.logical_delete_dt IS NULL;
SELECT uft.unit_set_cd,
uft.version_number,
uft.logical_delete_dt
FROM igs_en_unitsetfeetrg uft
WHERE uft.fee_cat = p_fee_cat
AND uft.fee_cal_type = p_dest_cal_type
AND uft.fee_ci_sequence_number = p_dest_sequence_number
AND uft.fee_type = p_fee_type
AND uft.unit_set_cd = cp_unit_set_cd
AND uft.version_number = cp_version_number;
l_b_record_inserted_ind := FALSE;
FETCH c_usft_dest INTO l_v_unit_set_cd, l_v_version_number, l_d_logical_delete_dt;
IF l_d_logical_delete_dt IS NULL THEN
l_b_record_exists_ind := TRUE;
igs_en_unitsetfeetrg_pkg.insert_row( x_rowid => l_rowid,
x_fee_cat => p_fee_cat,
x_fee_cal_type => p_dest_cal_type,
x_fee_ci_sequence_number => p_dest_sequence_number,
x_fee_type => p_fee_type,
x_unit_set_cd => l_usft_source_rec.unit_set_cd,
x_version_number => l_usft_source_rec.version_number,
x_create_dt => l_d_create_dt,
x_fee_trigger_group_number => l_usft_source_rec.fee_trigger_group_number,
x_logical_delete_dt => NULL,
x_mode => 'R');
l_b_record_inserted_ind := TRUE;
IF (l_b_record_inserted_ind = TRUE) THEN
-- Display message that the Unit Set Fee Triggers have been successfully rolled over
fnd_message.set_name('IGS','IGS_FI_USET_TRG_ROLLED');
|| sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
|| error message logged.
|| sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_tp_ret_schd
|| is now rounded off to currency precision
*****************************************************************/
-- Cursor for identifying Distinct Teaching Periods
CURSOR cur_dist_tp(cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
cp_fee_ci_seq_num igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
cp_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE) IS
SELECT DISTINCT teach_cal_type,
teach_ci_sequence_number
FROM igs_fi_tp_ret_schd
WHERE fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_seq_num
AND fee_type = cp_fee_type
AND (teach_cal_type IS NOT NULL AND teach_ci_sequence_number IS NOT NULL);
SELECT cal_type,
sequence_number
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND prior_ci_sequence_number = cp_seq_number;
SELECT *
FROM igs_fi_tp_ret_schd
WHERE fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_seq_num
AND fee_type = cp_fee_type
AND teach_cal_type = cp_teach_cal_type
AND teach_ci_sequence_number = cp_teach_seq_num;
SELECT 'x'
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = cp_load_cal_type
AND load_ci_sequence_number = cp_load_seq_number
AND teach_cal_type = cp_teach_cal_type
AND teach_ci_sequence_number = cp_teach_seq_number;
SELECT dt_alias,
sequence_number
FROM igs_ca_da_inst
WHERE cal_type = cp_teach_cal_type
AND ci_sequence_number = cp_teach_seq_num
AND dt_alias = cp_dt_alias
AND sequence_number = cp_dai_seq_num;
igs_fi_tp_ret_schd_pkg.insert_row(x_rowid => l_v_rowid,
x_ftci_teach_retention_id => l_n_ret_id,
x_teach_cal_type => l_rec_roll_tp.cal_type,
x_teach_ci_sequence_number => l_rec_roll_tp.sequence_number,
x_fee_cal_type => p_v_dest_cal_type,
x_fee_ci_sequence_number => p_n_dest_ci_seq_number,
x_fee_type => p_v_fee_type,
x_dt_alias => l_rec_dai.dt_alias,
x_dai_sequence_number => l_rec_dai.sequence_number,
x_ret_percentage => rec_ret_schd.ret_percentage,
x_ret_amount => igs_fi_gen_gl.get_formatted_amount(rec_ret_schd.ret_amount));