1 PACKAGE BODY igs_fi_prc_fee_rollv AS
2 /* $Header: IGSFI10B.pls 120.20 2006/06/19 07:07:10 sapanigr ship $ */
3
4 /******************************************************************
5 Created By :
6 Date Created By :
7 Purpose : This package is used for rolling over fee types from one calendar instance to
8 a future calendar instance. Package has been modulated into various functions
9 which will be checking all the validations inorder the fee rollover is carried on
10 successfully. Concurrent Manager is implicitly committing the trasanctions though
11 a COMMIT is not invoked explicitly. Data has to be set up for successful completion
12 of this process. Future Financial Year calendar instance, all Posting Account Codes
13 for the future financial calendar instance are mandatory. Calendar Instance Rollover
14 has to invoked prior to invoking fee rollover process.
15
16 Known limitations,
17 enhancements,
18 remarks :
19 Change History
20 Who When What
21 sapanigr 14-Jun-2006 Bug 5148913. Functions have been modified to catch all unhandled exceptions and
22 log the respective error message at function level.
23 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified finp_ins_roll_frtns and finp_ins_roll_tprs.
24 sapanigr 29-Mar-2006 Bug 4606670. Added validation in finp_ins_roll_far and finp_ins_roll_frtns to disallow process to
25 insert values for rates and schedules at FTCI level when they have already been defined at FCFL level and vice versa.
26 Also, added code in finp_prc_fee_rollvr, finp_ins_roll_ftci, finp_ins_roll_fcci and finp_ins_roll_fcfl
27 so that process ends in Warning if validation fails.
28 akandreg 02-Dec-2005 Bug 4747757. Added cursor cur_chk_version in function finp_ins_roll_uft to
29 handle the issue of rolling over a unit fee trigger when version is
30 not specified at FCFL level.
31 akandreg 10-Nov-2005 Bugs 4680440 , 4232201 - validation on Charge Method/Rule Seq Num at FCFL level
32 Before rolling over an FTCI, Charge Method/Rule Seq Num is validated whether source FTCI has a value.
33 This is done even for FCFL.
34 svuppala 09-Sep-2005 Bug 3822813 - The setting of variable l_b_fcfl_not_found to TRUE is removed
35 in finp_ins_roll_ftci
36 gurprsin 29-Aug-2005 Bug 3392088, Added max_chg_elements column to the IGS_FI_F_TYP_CA_INST_PKG.Insert_Row
37 svuppala 22-Aug-2005 Enh 3392095, Added waiver_calc_flag column to the IGS_FI_F_CAT_FEE_LBL_Pkg.Insert_Row
38 gurprsin 28-Jun-2005 Bug# 3392088 Modified the rollover process to incorporate
39 sub element ranges and rates table rollover.
40 svuppala 03-Jun-2005 Enh# 3442712 - Modified TBH calls to table IGS_FI_FEE_AS_RATE to include
41 5 new Unit Level Attributes in insert_row method
42 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
43 svuppala 11-Mar-2004 Bug 4224379 - Changed the function 'finp_ins_roll_uft'.
44 New cursor 'c_alt_cd' is created to get "Alternate code" from
45 igs_ca_inst_all and to send as a token in IGS_FI_ROLLOVER_UFT_ERROR.
46 Added an EXCEPTION to log a message in case of rolling over failure.
47 agairola 13-Sep-2004 Bug 3316063 - Retention Enhancements Build
48 pathipat 12-Jul-2004 Bug 3759552 - Added code to roll over Fee Trigger groups, Unit Fee Triggers and Unit Set Fee Triggers
49 Added functions for the same and corresponding calls in finp_ins_roll_fcfl().
50 Bug 3771151 - Removed references to log table IGS_GE_S_LOG_ENTRY and code to log - IGS_GE_GEN_003.GENP_INS_LOG_ENTRY
51 Bug 3771163 - Removed logging of message IGS_FI_FTCI_NO_REC_FOUND when FTCI has already been rolled over
52 uudayapr 16-oct-2003 Enh 3117341 Modified finp_ins_roll_ftci Procedure as a part of audit and special fees build.
53 pathipat 11-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
54 Modified finp_ins_roll_far() - TBH call igs_fi_fee_as_rate_pkg modified
55 pathipat 26-Jun-2003 Bug:2992967 - Table validation value set for segments
56 Modified finp_prc_fee_rollvr() and finpl_ins_roll_over_ftci_accts()
57 shtatiko 26-MAY-2003 Enh# 2831572, Added procedures log_parameters and finpl_ins_roll_over_ftci_accts.
58 Modified procedures finp_ins_roll_ftci, finp_ins_roll_fcci and finp_prc_fee_rollvr.
59 shtatiko 25-APR-2003 Enh# 2831569, Modified finp_prc_fee_rollvr and finp_ins_roll_anc
60 pathipat 24-Jan-2003 Bug:2765199 - Modified finp_prc_fee_rollvr
61 Raised l_e_user_Exception instead of app_Exception.raise_exception
62 when validations fail and process has to error out, so that 'Procedure raised unhandled exception'
63 is avoided when a proper error message has been logged.
64 Removed exception sections in finpl_chk_fss,finp_ins_roll_fcci, finp_ins_roll_frtns, finp_ins_roll_fcfl
65 finp_ins_roll_far, finp_ins_roll_er, finp_ins_roll_ctft, finp_ins_roll_cgft, finp_ins_roll_cft,
66 finp_ins_roll_anc
67 Modified exception section in finp_ins_roll_ftci
68 npalanis 23-OCT-2002 Bug : 2608360
69 residency_status_id parameter is changed to residency_status_cd
70 vvutukur 26-Aug-2002 Bug#2531390. Modifications done in functions FINP_INS_ROLL_FTCI,FINP_INS_ROLL_FCCI
71 and FINP_INS_ROLL_FCFL and removed DEFAULT in the package body.
72 vvutukur 23-Jul-2002 Bug#2425767.Modified functions finp_ins_roll_ftci,finp_ins_roll_fcfl to remove
73 references to payment_hierarchy_rank and modified function finp_ins_roll_frtns to
74 remove references to deduction_amount.
75 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
76 defined then the process should log a customized message
77 vchappid 29-May-2002 Bug#2372030, Function 'finp_ins_roll_anc' was returning FALSE when there are no records in the Ancillary Tables.
78 Should log the error message and should continue with the next rollover category or record.
79 Function 'finp_ins_roll_anc' should be invoked only if the system fee type is 'ANCILLARY'
80
81 Bug#2384909, Since Function 'finp_ins_roll_anc' was returning FALSE, next rollover category 'finp_ins_roll_revseg'
82 was not getting processed.
83
84 Bug# 2384909, Checking and Inserting the segments from the table igs_fi_f_type_accts is being done based on
85 fee_cal_type and fee_ci_sequence_number disregarding the fee type. When the same fee_cal_type and
86 fee_ci_sequence_number in different fee types then as many records are inserted, this function was returning FALSE
87 when the record is already exists, returning flase will terminate the process. Process should log the message and
88 should continue with next rollover categories
89
90 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
91 Reference to the Financial Calendar is removed as a part of SFCR005 Build. Removed the parameters account_cd, fin_cal_type
92 and fin_ci_sequence_number from the function call finp_val_ftci_rqrd
93 schodava 06-Feb-2002 Enh # 2187247
94 SFCR021 : FCI-LCI Relation
95 Removed the function for Charge Method Apportion rollover finp_ins_roll_cma
96 Sarakshi 15-Jan-2002 In function finp_ins_roll_ftci,removed the reference of subaccount_id from cursor c_ftci_fss
97 also from the insert_row of igs_fi_f_typ_ca_inst_pkg.Bug:2175865
98 sarakshi 19-Nov-2001 Added column ret_account_cd,ret_gl_ccid in the select list of cur c_ftci_fss also in
99 the call to the insert row of igs_fi_f_typ_ca_inst_pkg
100 as a prt of sfcr012, bug:2113459
101 schodava 3-Sep-2001 Bug : 1966961
102 Obsolete Items CCR
103 Removed references of the Account Code link to Financial Calendar
104 Also removed the function finp_ins_roll_fps and calls to it, as the payment schedules
105 functionality is replaced by the New Billing functionality.
106
107 Who When What
108 vchappid 17-Aug-2001 Ref: BugNo:1802900, A global X_ROWID has been defined in the package which is passed
109 into the INSERT_ROW TBH calls. This has been removed and variable l_rowid local to the
110 procedures is passed into the TBH calls. Explicit ROLLBACK is added in the outer most
111 procedure since Concurrent Manager is COMMITTING data though unhandled exception is
112 raised. All the calls to IGS_GE_GEN_003.GENP_INS_LOG_ENTRY are commented out, now all
113 comments are logged into the log file
114 sykrishn 29november2001 Removed the procedure finp_ins_roll_fe and its calls - as part of obseletion in bug 2126091.
115
116 ******************************************************************/
117
118 l_v_token1_val VARCHAR2(255);
119 token2_val VARCHAR2(255);
120
121 -- Instead of raising app_Exception.raise_Exception, raised a user_exception
122 -- to avoid the logging of 'Unhandled Exception' along with proper error message
123 l_e_user_exception EXCEPTION;
124
125 g_v_gl_installed igs_fi_control_all.rec_installed%TYPE;
126 g_n_segment_num fnd_id_flex_segments.segment_num%TYPE := NULL;
127 cst_warning CONSTANT VARCHAR2(7) := 'WARNING';
128 g_v_alternate_code igs_ca_inst_all.alternate_code%TYPE := NULL;
129
130 -- Routine to rollover fee type calendar instances between cal instances
131 FUNCTION finp_ins_roll_ftci(
132 p_fee_type IN IGS_FI_F_TYP_CA_INST.fee_type%TYPE ,
133 p_fee_cat IN IGS_FI_FEE_CAT.fee_cat%TYPE ,
134 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
135 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
136 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
137 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
138 p_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST.fee_type_ci_status%TYPE ,
139 p_message_name OUT NOCOPY VARCHAR2 ,
140 p_message_warning OUT NOCOPY VARCHAR2 )
141 RETURN BOOLEAN;
142 --
143 -- Routine to rollover fee cat calendar instances between cal instances
144 FUNCTION finp_ins_roll_fcci(
145 p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
146 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
147 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
148 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
149 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
150 p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
151 p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE ,
152 p_fee_type IN IGS_FI_FEE_TYPE.fee_type%TYPE ,
153 p_message_name OUT NOCOPY VARCHAR2 ,
154 p_message_warning OUT NOCOPY VARCHAR2 )
155 RETURN BOOLEAN;
156 --
157 -- Routine to rollover fee retention schedules between cal instances
158 FUNCTION finp_ins_roll_frtns(
159 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
160 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
161 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
162 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
163 p_relation_type IN IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE ,
164 p_fee_type IN IGS_FI_FEE_RET_SCHD.fee_type%TYPE ,
165 p_fee_cat IN IGS_FI_FEE_RET_SCHD.fee_cat%TYPE ,
166 p_message_name OUT NOCOPY VARCHAR2 )
167 RETURN BOOLEAN;
168 --
169 -- Routine to rollover fee encumbrances between cal instances
170 /* sykrishn 29november2001 Removed the procedure finp_ins_roll_fe - as part of obseletion in bug 2126091. */
171 --
172 -- Routine to rollover fee cat fee liabilities between cal instances
173 FUNCTION finp_ins_roll_fcfl(
174 p_fee_cat IN IGS_FI_F_CAT_FEE_LBL.fee_cat%TYPE ,
175 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
176 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
177 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
178 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
179 p_fee_type IN IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE ,
180 p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE ,
181 p_message_name OUT NOCOPY VARCHAR2 ,
182 p_message_warning OUT NOCOPY VARCHAR2 )
183 RETURN BOOLEAN;
184 --
185 -- Routine to rollover charge method apportionments between cal instances
186 -- Enh # 2187247
187 -- SFCR021 : FCI-LCI Relation
188 -- Removed the function for Charge Method Apportion rollover
189
190 -- Routine to rollover fee assessment rates between cal instances
191 FUNCTION finp_ins_roll_far(
192 p_fee_type IN IGS_FI_FEE_AS_RATE.fee_type%TYPE ,
193 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
194 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
195 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
196 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
197 p_relation_type IN IGS_FI_FEE_AS_RATE.s_relation_type%TYPE ,
198 p_fee_cat IN IGS_FI_FEE_AS_RATE.fee_cat%TYPE ,
199 p_message_name OUT NOCOPY VARCHAR2 )
200 RETURN BOOLEAN;
201 --
202 -- Routine to rollover elements ranges between cal instances
203 FUNCTION finp_ins_roll_er(
204 p_fee_type IN IGS_FI_ELM_RANGE.fee_type%TYPE ,
205 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
206 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
207 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
208 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
209 p_relation_type IN IGS_FI_ELM_RANGE.s_relation_type%TYPE ,
210 p_fee_cat IN IGS_FI_ELM_RANGE.fee_cat%TYPE ,
211 p_message_name OUT NOCOPY VARCHAR2 )
212 RETURN BOOLEAN;
213
214 -- Function to roll over Fee Trigger Groups
215 FUNCTION finp_ins_roll_trg_grp( p_fee_cat IN igs_fi_fee_trg_grp.fee_cat%TYPE ,
216 p_source_cal_type IN igs_ca_inst.cal_type%TYPE ,
217 p_source_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
218 p_dest_cal_type IN igs_ca_inst.cal_type%TYPE ,
219 p_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
220 p_fee_type IN igs_fi_fee_trg_grp.fee_type%TYPE ,
221 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN;
222
223 --
224 -- Routine to rollover IGS_PS_COURSE type fee triggers between cal instances
225 FUNCTION finp_ins_roll_ctft(
226 p_fee_cat IN IGS_PS_TYPE_FEE_TRG.fee_cat%TYPE ,
227 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
228 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
229 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
230 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
231 p_fee_type IN IGS_PS_TYPE_FEE_TRG.fee_type%TYPE ,
232 p_message_name OUT NOCOPY VARCHAR2 )
233 RETURN BOOLEAN;
234 --
235 -- Routine to rollover IGS_PS_COURSE group fee triggers between cal instances
236 FUNCTION finp_ins_roll_cgft(
237 p_fee_cat IN IGS_PS_GRP_FEE_TRG.fee_cat%TYPE ,
238 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
239 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
240 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
241 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
242 p_fee_type IN IGS_PS_GRP_FEE_TRG.fee_type%TYPE ,
243 p_message_name OUT NOCOPY VARCHAR2 )
244 RETURN BOOLEAN;
245 --
246 -- Routine to rollover IGS_PS_COURSE fee triggers between cal instances
247 FUNCTION finp_ins_roll_cft(
248 p_fee_cat IN IGS_PS_FEE_TRG.fee_cat%TYPE ,
249 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
250 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
251 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
252 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
253 p_fee_type IN IGS_PS_FEE_TRG.fee_type%TYPE ,
254 p_message_name OUT NOCOPY VARCHAR2 )
255 RETURN BOOLEAN;
256
257 -- Function to roll over Unit Fee Triggers between Calendar Instances
258 FUNCTION finp_ins_roll_uft( p_fee_cat IN igs_fi_unit_fee_trg.fee_cat%TYPE ,
259 p_source_cal_type IN igs_ca_inst.cal_type%TYPE ,
260 p_source_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
261 p_dest_cal_type IN igs_ca_inst.cal_type%TYPE ,
262 p_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
263 p_fee_type IN igs_fi_unit_fee_trg.fee_type%TYPE ,
264 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN;
265
266 -- Function to roll over Unit Set Fee Triggers between Calendar Instances
267 FUNCTION finp_ins_roll_usft( p_fee_cat IN igs_en_unitsetfeetrg.fee_cat%TYPE ,
268 p_source_cal_type IN igs_ca_inst.cal_type%TYPE ,
269 p_source_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
270 p_dest_cal_type IN igs_ca_inst.cal_type%TYPE ,
271 p_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
272 p_fee_type IN igs_en_unitsetfeetrg.fee_type%TYPE ,
273 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN;
274
275 --
276 -- To rollover Ancillary related segments and the rate when the Fee Calendar Instance gets rolled over
277 FUNCTION finp_ins_roll_anc(
278 p_fee_type IN IGS_FI_ANC_RT_SGMNTS.fee_type%TYPE ,
279 p_source_cal_type IN IGS_FI_ANC_RT_SGMNTS.fee_cal_type%TYPE ,
280 p_source_ci_sequence_number IN IGS_FI_ANC_RT_SGMNTS.fee_ci_sequence_number%TYPE ,
281 p_dest_cal_type IN IGS_FI_ANC_RT_SGMNTS.fee_cal_type%TYPE ,
282 p_dest_ci_sequence_number IN IGS_FI_ANC_RT_SGMNTS.fee_ci_sequence_number%TYPE ,
283 p_message_name OUT NOCOPY VARCHAR2 )
284 RETURN BOOLEAN;
285 --
286 --New function added by kkillams w.r.t. Student Finance Dld bug id: 1882122
287 --To rollover revenue segments over for Fee Type Calendar Instances occurs.
288 --
289 FUNCTION finp_ins_roll_revseg(
290 p_fee_type IN IGS_FI_F_TYPE_ACCTS_ALL.fee_type%TYPE,
291 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE,
292 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE,
293 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE,
294 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE,
295 p_message_name OUT NOCOPY VARCHAR2
296 )
297 RETURN BOOLEAN;
298
299 -- To Rollover Account Table Attribute Records IF System Fee Type is OTHER or TUITION
300 -- This has been added as part of Enh# 2831572
301 FUNCTION finpl_ins_roll_over_ftci_accts (
302 p_v_fee_type IN igs_fi_f_type_accts_all.fee_type%TYPE,
303 p_v_source_cal_type IN igs_ca_inst.cal_type%TYPE,
304 p_n_source_sequence_number IN igs_ca_inst.sequence_number%TYPE,
305 p_v_dest_cal_type IN igs_ca_inst.cal_type%TYPE,
306 p_n_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE
307 ) RETURN BOOLEAN;
308
309 -- Forward declaration of the finp_ins_roll_tprs procedure
310 PROCEDURE finp_ins_roll_tprs(p_v_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
311 p_v_source_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
312 p_n_source_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
313 p_v_dest_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
314 p_n_dest_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
315 p_b_status OUT NOCOPY BOOLEAN,
316 p_v_message_name OUT NOCOPY VARCHAR2);
317
318 --
319 -- Call to package routine allows the package to be pinned in memory.
320 PROCEDURE genp_pin_package
321 AS
322 BEGIN
323 NULL;
324 END;
325
326 -- Routine to log input parameters.
327 PROCEDURE log_parameters ( p_v_parm_type IN VARCHAR2, p_v_parm_code IN VARCHAR2 )
328 AS
329 /*----------------------------------------------------------------------------
330 Created By : shtatiko
331 Created On : 14-MAY-2003 (Added as part of Enh# 2831572)
332
333 Purpose : To log input parameters to the process
334
335 Known limitations, enhancements or remarks :
336 Change History :
337 Who When What
338 (reverse chronological order - newest change first)
339 ----------------------------------------------------------------------------*/
340 BEGIN
341 fnd_file.put_line(fnd_file.log, p_v_parm_type || ' : ' || p_v_parm_code );
342 END log_parameters;
343
344 --
345 -- Routine to process fee structure data rollover between cal instances
346 PROCEDURE finp_prc_fee_rollvr(
347 errbuf OUT NOCOPY VARCHAR2,
348 retcode OUT NOCOPY NUMBER,
349 p_rollover_fee_type_ci_ind IN VARCHAR ,
350 p_rollover_fee_cat_ci_ind IN VARCHAR ,
351 P_Source_Calendar IN VARCHAR2,
352 P_Dest_Calendar IN VARCHAR2 ,
353 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
354 p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
355 p_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST_ALL.fee_type_ci_status%TYPE ,
356 p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
357 p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL_ALL.fee_liability_status%TYPE,
358 p_org_id NUMBER
359 ) AS
360 /*----------------------------------------------------------------------------
361 || Created By :
362 || Created On :
363 || Purpose :
364 || Known limitations, enhancements or remarks :
365 || Change History :
366 || Who When What
367 || (reverse chronological order - newest change first)
368 || sapanigr 29-Mar-2006 Bug 4606670. Process ends in warning if call to finp_ins_roll_ftci or
369 || finp_ins_roll_fcci returns value cst_warning.
370 || pathipat 26-Jun-2003 Bug:2992967 - Table validation value set for segments
371 || Replaced call to igs_fi_gen_apint.get_flex_val with igs_fi_gen_apint.get_segment_num
372 || shtatiko 26-MAY-2003 Enh# 2831572, Added code to fetch value of GL installed, chart of accounts
373 || and Sequence Order for the GL_ACCOUNT_TYPE segment qualifier.
374 || shtatiko 25-APR-2003 Enh# 2831569, Added check for Manage Accounts
375 || System Option. If its value is NULL then this
376 || process cannot be executed.
377 ----------------------------------------------------------------------------*/
378 gv_other_detail VARCHAR2(255);
379 p_source_cal_type igs_ca_inst.cal_type%TYPE ;
380 p_source_sequence_number igs_ca_inst.sequence_number%TYPE ;
381 p_dest_cal_type igs_ca_inst.cal_type%TYPE ;
382 p_dest_sequence_number igs_ca_inst.sequence_number%TYPE;
383 BEGIN
384 --Block for Parameter Validation/Splitting of Parameters
385
386 igs_ge_gen_003.set_org_id(p_org_id);
387
388 retcode:=0;
389 BEGIN
390
391 -- Added by Nshee on 26-Apr-2001 during Fee Rollover Testing to show the parameters in the log file generated by the concurrent request
392 -- Modified the logging of parameters as part of Enh# 2831572.
393 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'ROLL_FEE_TYPE'),
394 igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_rollover_fee_type_ci_ind) );
395 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'ROLL_FEE_CAT'),
396 igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_rollover_fee_cat_ci_ind) );
397 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'SRC_CAL_INST'),
398 p_source_calendar );
399 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'DEST_CAL_INST'),
400 p_dest_calendar );
401 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_TYPE'),
402 p_fee_type );
403 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_CAT'),
404 p_fee_cat );
405 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FTCI_STATUS'),
406 p_fee_type_ci_status );
407 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FCAT_STATUS'),
408 p_fee_cat_ci_status );
409 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FCFL_STATUS'),
410 p_fee_liability_status );
411 fnd_file.put_line ( fnd_file.LOG, ' ' );
412
413 -- End of Addition by Nshee on 26-Apr-2001 during Fee Rollover testing to show the parameters in the log file generated by the concurrent request
414
415 p_source_cal_type := RTRIM(SUBSTR(p_source_calendar, 102, 10));
416 p_source_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_source_calendar, 113, 8)));
417 p_dest_cal_type := RTRIM(SUBSTR(p_dest_calendar, 102, 10)); -- TO trim trailing spaces in the right
418 p_dest_sequence_number := TO_NUMBER(SUBSTR(p_dest_calendar, 113, 8));
419 END;
420 --End of Block for Parameter Validation/Splitting of Parameters
421 DECLARE
422 v_message_name VARCHAR2(30);
423 v_message_warning VARCHAR2(30);
424 v_cal_type IGS_CA_TYPE.cal_type%TYPE;
425 v_prior_ci_sequence_number IGS_CA_INST.prior_ci_sequence_number%TYPE;
426 l_v_message_name fnd_new_messages.message_name%TYPE;
427 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
428
429 CURSOR c_cat IS
430 SELECT cat.cal_type
431 FROM IGS_CA_TYPE cat
432 WHERE cat.cal_type = p_source_cal_type;
433 CURSOR c_ci IS
434 SELECT ci.prior_ci_sequence_number
435 FROM IGS_CA_INST ci
436 WHERE ci.cal_type = p_dest_cal_type AND
437 ci.sequence_number = p_dest_sequence_number;
438 FUNCTION finpl_chk_fss (
439 p_fee_structure_status IN IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE,
440 p_message_name OUT NOCOPY VARCHAR2)
441 RETURN BOOLEAN AS
442 BEGIN
443 DECLARE
444 v_s_fee_structure_status IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE;
445 CURSOR c_fss (
446 cp_fee_structure_status IGS_FI_FEE_STR_STAT.fee_structure_status%TYPE) IS
447 SELECT fss.s_fee_structure_status
448 FROM IGS_FI_FEE_STR_STAT fss
449 WHERE fss.fee_structure_status= cp_fee_structure_status;
450 BEGIN
451 OPEN c_fss(p_fee_structure_status);
452 FETCH c_fss INTO v_s_fee_structure_status;
453 IF (c_fss%NOTFOUND) THEN
454 CLOSE c_fss;
455 v_message_name := 'IGS_GE_INVALID_VALUE';
456 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String ('IGS', v_message_name));
457 p_message_name := v_message_name;
458 RETURN FALSE;
459 ELSE
460 IF (v_s_fee_structure_status = 'INACTIVE') THEN
461 CLOSE c_fss;
462 v_message_name := 'IGS_FI_STATUS_PARAM_INACTIVE';
463 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String ('IGS', v_message_name));
464 p_message_name := v_message_name;
465 RETURN FALSE;
466 END IF;
467 END IF;
468 RETURN TRUE;
469 END;
470 END finpl_chk_fss;
471 BEGIN
472 -- This function will control the rollover of all fee structure data underneath
473 -- a nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
474
475 -- Check the value of Manage Accounts System Option value.
476 -- If its NULL then this process should error out by logging message.
477 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
478 p_v_message_name => l_v_message_name );
479 IF l_v_manage_accounts IS NULL THEN
480 fnd_message.set_name ( 'IGS', l_v_message_name );
481 igs_ge_msg_stack.ADD;
482 RAISE l_e_user_exception;
483 END IF;
484
485 -- Find whether Oracle General Ledger is installed or not.
486 g_v_gl_installed := igs_fi_gen_005.finp_get_receivables_inst;
487
488 -- If Oracle General Ledger is installed then fetch segment number
489 -- for the Natural Account Segment
490 IF ( g_v_gl_installed = 'Y' ) THEN
491 igs_fi_gen_apint.get_segment_num(g_n_segment_num);
492 END IF;
493
494 -- 1. Validate the parameters to the routine
495 -- Check rollover indicators
496 IF (p_rollover_fee_type_ci_ind <> 'Y' AND p_rollover_fee_cat_ci_ind <> 'Y') THEN
497 -- p_rollover_fee_type_ci_ind is duplicatly checked,
498 -- changed to p_rollover_fee_cat_ci_ind inline with CALLISTA Code
499
500 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
501 IGS_GE_MSG_STACK.ADD;
502 RAISE l_e_user_exception; -- Raised user_Exception to avoid error 'Unhandled exception' when proper error has been logged
503 END IF;
504
505 -- Check status parameters
506 IF (p_rollover_fee_type_ci_ind = 'Y' AND p_fee_type_ci_status IS NOT NULL) THEN
507 IF (finpl_chk_fss(p_fee_type_ci_status, v_message_name) = FALSE) THEN
508 Fnd_Message.Set_Name ('IGS', v_message_name);
509 IGS_GE_MSG_STACK.ADD;
510 RAISE l_e_user_exception;
511 END IF;
512 END IF;
513
514 IF (p_rollover_fee_cat_ci_ind = 'Y') THEN
515 IF (p_fee_cat_ci_status IS NOT NULL) THEN
516 IF (finpl_chk_fss(p_fee_cat_ci_status,v_message_name) = FALSE) THEN
517 Fnd_Message.Set_Name ('IGS', v_message_name);
518 IGS_GE_MSG_STACK.ADD;
519 RAISE l_e_user_exception;
520 END IF;
521 END IF;
522
523 IF (p_fee_liability_status IS NOT NULL) THEN
524 IF (finpl_chk_fss(p_fee_liability_status,v_message_name) = FALSE) THEN
525 Fnd_Message.Set_Name ('IGS', v_message_name);
526 IGS_GE_MSG_STACK.ADD;
527 RAISE l_e_user_exception;
528 END IF;
529 END IF;
530 END IF;
531
532 -- Can only transfer within the same IGS_CA_TYPE
533 IF (p_source_cal_type <> p_dest_cal_type) THEN
534 Fnd_Message.Set_Name ('IGS', 'IGS_FI_ROLLOVER_FEE_STRUCTURE');
535 IGS_GE_MSG_STACK.ADD;
536 RAISE l_e_user_exception;
537 END IF;
538
539 -- Check the calendar type exists
540 OPEN c_cat;
541 FETCH c_cat INTO v_cal_type;
542 IF (c_cat%NOTFOUND) THEN
543 CLOSE c_cat;
544 Fnd_Message.Set_Name ('IGS', 'IGS_GE_VAL_DOES_NOT_XS');
545 IGS_GE_MSG_STACK.ADD;
546 RAISE l_e_user_exception;
547 END IF;
548 CLOSE c_cat;
549
550 -- Check destination calendar instance exists
551 OPEN c_ci;
552 FETCH c_ci INTO v_prior_ci_sequence_number;
553 IF (c_ci%NOTFOUND) THEN
554 CLOSE c_ci;
555 Fnd_Message.Set_Name ('IGS', 'IGS_PS_DEST_CAL_INST_NOT_EXIS');
556 IGS_GE_MSG_STACK.ADD;
557 RAISE l_e_user_exception;
558 END IF;
559 CLOSE c_ci;
560 -- Validate the destination calendar instance
561 IF (IGS_FI_VAL_FCCI.finp_val_ci_fee(p_dest_cal_type,p_dest_sequence_number,v_message_name) = FALSE) THEN
562 Fnd_Message.Set_Name ('IGS', v_message_name);
563 IGS_GE_MSG_STACK.ADD;
564 RAISE l_e_user_exception;
565 END IF;
566
567 -- Check destination calendar instance is a
568 -- product of the source calendar instance
569 IF (v_prior_ci_sequence_number IS NULL OR v_prior_ci_sequence_number <> p_source_sequence_number) THEN
570 v_message_name := 'IGS_FI_DEST_CALINST_NOT_ROLLE';
571 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String ('IGS', v_message_name));
572 END IF;
573
574 --Get value of Alternate Code and put into Global variable.
575 g_v_alternate_code := igs_ca_gen_001.calp_get_alt_cd(p_source_cal_type,
576 p_source_sequence_number);
577
578 -- 2. Process the rollover
579 IF (p_rollover_fee_type_ci_ind = 'Y') THEN
580 -- Rollover fee type calendar instances
581 IF (finp_ins_roll_ftci(p_fee_type,
582 p_fee_cat,
583 p_source_cal_type,
584 p_source_sequence_number,
585 p_dest_cal_type,
586 p_dest_sequence_number,
587 p_fee_type_ci_status,
588 v_message_name,
589 v_message_warning) = FALSE) THEN
590 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS',v_message_name));
591 RETURN;
592 ELSIF (v_message_warning = cst_warning) THEN
593 RETCODE:=1;
594 END IF;
595 END IF;
596
597 IF (p_rollover_fee_cat_ci_ind = 'Y') THEN
598 -- Rollover fee category calendar instances
599 IF (finp_ins_roll_fcci(p_fee_cat,
600 p_source_cal_type,
601 p_source_sequence_number,
602 p_dest_cal_type,
603 p_dest_sequence_number,
604 p_fee_cat_ci_status,
605 p_fee_liability_status,
606 p_fee_type,
607 v_message_name,
608 v_message_warning) = FALSE) THEN
609 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS',v_message_name));
610 RETURN;
611 ELSIF (v_message_warning = cst_warning) THEN
612 RETCODE:=1;
613 END IF;
614 END IF;
615 fnd_file.put_line( fnd_file.LOG, RPAD('-', 79, '-') );
616
617 --Added by Nshee on 26-Apr-2001 during Fee Rollvr Testing to put the rollover completed successfully message in the log file
618 fnd_file.put_line( fnd_file.LOG, ' ' );
619 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String ('IGS', 'IGS_FI_ROLLOVER_COMPL_SUCCESS'));
620 --End of Addition by Nshee
621
622 EXCEPTION
623
624 -- When user expection is raised, do not raise 'Unhandled exception' error message
625 -- since the appropriate error is already getting logged.
626 WHEN l_e_user_exception THEN
627 ROLLBACK;
628 RETCODE:=2;
629 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
630
631 WHEN OTHERS THEN
632 -- concurrent manager is committing though an Un-Handled exception is raised
633 -- explicitly rollbacking when the process raises Un-Handled exceptions
634 -- This is incorporated as fix to Bug#1802900
635 ROLLBACK;
636 RETCODE:=2;
637 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ':' || SQLERRM;
638 fnd_file.put_line(fnd_file.log,SUBSTR(SQLERRM,1,300));
639 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
640 END;
641 END finp_prc_fee_rollvr;
642 --
643 -- Routine to rollover fee type calendar instances between cal instances
644 FUNCTION finp_ins_roll_ftci(
645 p_fee_type IN IGS_FI_F_TYP_CA_INST.fee_type%TYPE ,
646 p_fee_cat IN IGS_FI_FEE_CAT.fee_cat%TYPE ,
647 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
648 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
649 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
650 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
651 p_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST.fee_type_ci_status%TYPE ,
652 p_message_name OUT NOCOPY VARCHAR2 ,
653 p_message_warning OUT NOCOPY VARCHAR2 )
654 RETURN BOOLEAN AS
655 /*----------------------------------------------------------------------------
656 || Created By :
657 || Created On :
658 || Purpose :
659 || Known limitations, enhancements or remarks :
660 || Change History :
661 || Who When What
662 || sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions caught and appropriate error message logged.
663 || sapanigr 29-Mar-2006 Bug 4606670. Out parameter p_message_name assigned dummy value cst_warning
664 || if calls to finp_ins_roll_frtns or finp_ins_roll_far returns this value.
665 || akandreg 11-Nov-2005 Bugs 4680440 , 4232201 - validation on Charge Method/Rule Seq Num at FCFL level
666 || Before rolling over an FTCI, Charge Method/Rule Seq Num is validated whether source FTCI has a value.
667 || This is done even for FCFL.
668 || (reverse chronological order - newest change first)
669 || svuppala 09-Sep-2005 Bug 3822813 - The setting of variable l_b_fcfl_not_found to TRUE is removed
670 || svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
671 || Modifications to reflect the data model changes (NONZERO_BILLABLE_CP_FLAG) in
672 || Fee Type Calendar Instances Table
673 || agairola 13-Sep-2004 Bug 3316063 - Retention Enhancements Build
674 || pathipat 12-Jul-2004 Bug 3771163 - Removed logging of message IGS_FI_FTCI_NO_REC_FOUND
675 || when FTCI has already been rolled over
676 || uudayapr 16-oct-2003 Enh #3117341 Added Audit and SPECIAL IN SYSTEM FEE TYPE for
677 || Rollover FTCI Accounting Information As a part of Audit and Special Fees Build.
678 || shtatiko 26-MAY-2003 Enh# 2831572, finp_ins_roll_revseg procedure is called only IF GL is installed.
679 || Added call to new procedure finpl_ins_roll_over_ftci_accts.
680 || pathipat 24-Jan-2003 Bug: 2765199 - Modified exception section - removed when others then
681 || vvutukur 26-Aug-2002 Bug#2531390.The comment in the code regarding the rollover of fee payment
682 || schedules is removed to avoid confusion.Removed DEFAULTing values of
683 || l_already_rolled,l_fcfl_exists_ind to avoid gscc warnings.
684 || vvutukur 23-Jul-2002 Bug#2425767.Removed references to payment_hierarchy_rank(from cursor
685 || c_ftci_fss and from the call to IGS_FI_F_TYP_CA_INST_PKG.INSERT_ROW).
686 ----------------------------------------------------------------------------*/
687 gv_other_detail VARCHAR2(255);
688 l_v_token1_val IGS_LOOKUP_VALUES.MEANING%TYPE;
689 BEGIN
690 DECLARE
691 e_resource_busy EXCEPTION;
692 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
693 cst_active CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE :=
694 'ACTIVE';
695 cst_ftci CONSTANT IGS_FI_FEE_AS_RATE.s_relation_type%TYPE := 'FTCI';
696 v_message_name VARCHAR2(30);
697 v_message_warning VARCHAR2(30);
698 v_ftci_inserted_ind BOOLEAN;
699 v_ftci_exists_ind BOOLEAN; -- used for performing rollover of retn schds. etc. IF accnt code is alrdy rolled over.
700 v_process_next_ftci BOOLEAN;
701 v_insert_record BOOLEAN; -- used for diff bet ins/upd of rec for dest cal type. not reqd now as no upd takes place
702 l_already_rolled BOOLEAN := FALSE;
703 l_fcfl_exists_ind BOOLEAN := TRUE;
704 v_fee_cat IGS_FI_F_CAT_FEE_LBL.fee_cat%TYPE;
705 v_fee_type_ci_status IGS_FI_F_TYP_CA_INST.fee_type_ci_status%TYPE;
706 v_tmp_fee_type IGS_FI_F_TYP_CA_INST.fee_type%TYPE;
707 v_closed_ind IGS_FI_FEE_TYPE.closed_ind%TYPE;
708 v_sequence_number IGS_CA_INST.sequence_number%TYPE;
709 v_valid_dai BOOLEAN;
710 v_dummy IGS_CA_DA_INST.dt_alias%TYPE;
711 l_n_org_id IGS_FI_F_TYP_CA_INST.ORG_ID%type := igs_ge_gen_003.get_org_id;
712 l_rowid VARCHAR2(25);
713 l_b_records_found BOOLEAN;
714
715
716 CURSOR c_ftci_fss IS
717 SELECT ftci.fee_type,
718 ftci.fee_type_ci_status,
719 ftci.start_dt_alias,
720 ftci.start_dai_sequence_number,
721 ftci.end_dt_alias,
722 ftci.end_dai_sequence_number,
723 ftci.retro_dt_alias,
724 ftci.retro_dai_sequence_number,
725 ftci.s_chg_method_type,
726 ftci.rul_sequence_number,
727 ftci.initial_default_amount,
728 -- Added by kkillams ,w.r.t Student Finanace (Finance Accounting) DLD bug#1882122
729 ftci.acct_hier_id,
730 ftci.rec_gl_ccid,
731 ftci.rev_account_cd,
732 ftci.rec_account_cd,
733 --Added by Sarakshi,as a part of SFCR012 bug:2113459
734 ftci.ret_account_cd,
735 ftci.ret_gl_ccid,
736 ftci.retention_level_code,
737 ftci.complete_ret_flag,
738 --Added by svuppala,as a part of bug:4295379
739 ftci.nonzero_billable_cp_flag,
740 --Added by gurprsin,as a part of bug:3392088
741 ftci.scope_rul_sequence_num,
742 ftci.elm_rng_order_name,
743 ftci.max_chg_elements
744 FROM IGS_FI_F_TYP_CA_INST ftci,
745 IGS_FI_FEE_STR_STAT fss
746 WHERE (p_fee_type IS NULL OR
747 ftci.fee_type = p_fee_type) AND
748 ftci.fee_cal_type = p_source_cal_type AND
749 ftci.fee_ci_sequence_number = p_source_sequence_number AND
750 ftci.fee_type_ci_status = fss.fee_structure_status AND
751 fss.s_fee_structure_status = cst_active;
752 CURSOR c_ft (
753 cp_fee_type IGS_FI_F_TYP_CA_INST.fee_type%TYPE) IS
754 SELECT ft.closed_ind, ft.s_fee_type
755 FROM IGS_FI_FEE_TYPE ft
756 WHERE ft.fee_type = cp_fee_type;
757
758 l_s_fee_type igs_fi_fee_type.s_fee_type%TYPE;
759
760 -- This cursor is changed as a part of SFCR005 (Enh #1966961)
761 CURSOR c_ftci (
762 cp_fee_type IGS_FI_F_TYP_CA_INST.fee_type%TYPE) IS
763 SELECT 'x'
764 FROM IGS_FI_F_TYP_CA_INST ftci
765 WHERE ftci.fee_type = cp_fee_type AND
766 ftci.fee_cal_type = p_dest_cal_type AND
767 ftci.fee_ci_sequence_number = p_dest_sequence_number;
768 CURSOR c_fcfl (
769 cp_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE) IS
770 SELECT fcfl.fee_cat
771 FROM IGS_FI_F_CAT_FEE_LBL fcfl
772 WHERE fcfl.fee_cat = p_fee_cat AND
773 fcfl.fee_cal_type = p_source_cal_type AND
774 fcfl.fee_ci_sequence_number = p_source_sequence_number AND
775 fcfl.fee_type = cp_fee_type;
776
777 CURSOR c_dai (
778 cp_dt_alias IGS_CA_DA_INST.dt_alias%TYPE,
779 cp_sequence_number IGS_CA_DA_INST.sequence_number%TYPE,
780 cp_cal_type IGS_CA_DA_INST.cal_type%TYPE,
781 cp_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE) IS
782 SELECT DT_ALIAS
783 FROM IGS_CA_DA_INST
784 WHERE dt_alias = cp_dt_alias AND
785 sequence_number = cp_sequence_number AND
786 cal_type = cp_cal_type AND
787 ci_sequence_number = cp_ci_sequence_number;
788
789 l_b_status BOOLEAN;
790 l_v_retention_level_code igs_fi_f_typ_ca_inst.retention_level_code%TYPE;
791 BEGIN
792 -- This function will roll all IGS_FI_F_TYP_CA_INST records underneath a
793 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
794 -- It also controls the rollover of;
795 -- * fee_refund_schedule
796 -- * IGS_FI_FEE_RET_SCHD
797 -- * IGS_FI_FEE_ENCMB
798 -- * IGS_FI_CHG_MTH_APP
799 -- * IGS_FI_FEE_AS_RATE
800 -- * IGS_FI_ELM_RANGE
801 -- The assumption is being made that the "destination" IGS_CA_INST
802 -- is open and active - it is the responsibility of the calling routine
803 -- to check for this.
804 -- IGS_GE_NOTE: If some of the IGS_FI_F_TYP_CA_INST records already exist then
805 -- these may be updated IF the assiciated IGS_FI_ACC link has not been
806 -- previously established via the rollover, and it is now possible to do so.
807 --------------------------------------------------------------------------
808 p_message_name := Null;
809
810 l_b_records_found := FALSE;
811
812 -- 1. Process the fee type calendar instance records
813 -- matching the source calendar instance
814 FOR v_ftci_fss_rec IN c_ftci_fss LOOP
815 v_process_next_ftci := FALSE;
816 v_ftci_inserted_ind := FALSE;
817 v_ftci_exists_ind := FALSE;
818 v_insert_record := FALSE;
819 l_fcfl_exists_ind := TRUE;
820 l_b_records_found := TRUE;
821
822 fnd_file.put_line( fnd_file.LOG, RPAD('-', 79, '-') );
823 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_TYPE'), v_ftci_fss_rec.fee_type );
824
825 -- Rollover of Fee Type Calendar Instance is prevented if
826 -- the Charge Method has not been defined at Fee Type Calendar Instance level.
827 IF v_ftci_fss_rec.s_chg_method_type IS NULL OR v_ftci_fss_rec.rul_sequence_number IS NULL THEN
828 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_NO_ROLL_CHG_METHOD');
829 FND_MESSAGE.SET_TOKEN('FTCI_FCFL',IGS_FI_GEN_GL.get_lkp_meaning('IGS_FI_LOCKBOX','FTCI'));
830 fnd_file.put_line(fnd_file.log,fnd_message.Get);
831 v_process_next_ftci := TRUE;
832 END IF;
833 -- Check the fee type IS a liablility of the fee category
834 -- when fee category is specified
835 IF p_fee_cat IS NOT NULL THEN
836 OPEN c_fcfl(v_ftci_fss_rec.fee_type);
837 FETCH c_fcfl INTO v_fee_cat;
838 IF (c_fcfl%NOTFOUND) THEN
839 -- process next IGS_FI_F_TYP_CA_INST
840 v_process_next_ftci := TRUE;
841 l_fcfl_exists_ind := FALSE;
842 END IF;
843 CLOSE c_fcfl;
844 END IF;
845 IF (v_process_next_ftci = FALSE) THEN
846 -- Check the fee type is open
847 OPEN c_ft(v_ftci_fss_rec.fee_type);
848 FETCH c_ft INTO v_closed_ind, l_s_fee_type;
849 CLOSE c_ft;
850 IF (v_closed_ind = 'Y') THEN
851 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String ('IGS', 'IGS_FI_FEETYPE_CLOSED'));
852 -- process next IGS_FI_F_TYP_CA_INST
853 v_process_next_ftci := TRUE;
854 END IF;
855 END IF;
856
857 -- This is changed as a part of CCR SFCR005 (Enh # 1966961)
858 IF (v_process_next_ftci = FALSE) THEN
859 -- Check for the existence of the IGS_FI_F_TYP_CA_INST
860 -- record under the destination calendar.
861 OPEN c_ftci(v_ftci_fss_rec.fee_type);
862 FETCH c_ftci INTO v_dummy;
863 IF (c_ftci%FOUND) THEN
864 CLOSE c_ftci;
865 l_already_rolled := TRUE;
866 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String ('IGS','IGS_FI_FEETYPE_CALINST_ROLL'));
867
868 v_ftci_exists_ind := TRUE;
869 v_insert_record := FALSE;
870 -- when the FTCI was rolled over, it's IGS_FI_ACC
871 -- may not have been able to be carried through.
872 -- The rollover process will again attempt to
873 -- establish the IGS_FI_ACC link.
874 v_process_next_ftci := TRUE;
875 ELSE
876 CLOSE c_ftci;
877 v_insert_record := TRUE;
878 END IF;
879 END IF;
880
881 IF (v_process_next_ftci = FALSE AND v_insert_record = TRUE ) THEN
882 v_valid_dai := TRUE;
883 -- Check for the existence of the start dai
884 -- record under the destination calendar
885 OPEN c_dai( v_ftci_fss_rec.start_dt_alias,
886 v_ftci_fss_rec.start_dai_sequence_number,
887 p_dest_cal_type,
888 p_dest_sequence_number);
889 FETCH c_dai INTO v_dummy;
890 IF (c_dai%NOTFOUND) THEN
891 CLOSE c_dai;
892 v_valid_dai := FALSE;
893 l_v_token1_val := v_ftci_fss_rec.start_dt_alias ||','||TO_CHAR(v_ftci_fss_rec.start_dai_sequence_number);
894 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_TYP_SDTA_DOSNT_EXST');
895 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
896 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get);
897
898 ELSE
899 CLOSE c_dai;
900 END IF;
901 -- Check for the existence of the end dai
902 -- record under the destination calendar
903 OPEN c_dai( v_ftci_fss_rec.end_dt_alias,
904 v_ftci_fss_rec.end_dai_sequence_number,
905 p_dest_cal_type,
906 p_dest_sequence_number);
907 FETCH c_dai INTO v_dummy;
908 IF (c_dai%NOTFOUND) THEN
909 CLOSE c_dai;
910 v_valid_dai := FALSE;
911
912 l_v_token1_val := v_ftci_fss_rec.end_dt_alias ||','||TO_CHAR(v_ftci_fss_rec.end_dai_sequence_number);
913 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_TYP_EDTA_DOSNT_EXST'); --new message
914 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
915 fnd_file.put_line(fnd_file.log,fnd_message.Get);
916 ELSE
917 CLOSE c_dai;
918 END IF;
919 IF v_ftci_fss_rec.retro_dt_alias IS NOT NULL THEN
920 -- Check for the existence of the retro dai
921 -- record under the destination calendar
922 OPEN c_dai( v_ftci_fss_rec.retro_dt_alias,
923 v_ftci_fss_rec.retro_dai_sequence_number,
924 p_dest_cal_type,
925 p_dest_sequence_number);
926 FETCH c_dai INTO v_dummy;
927 IF (c_dai%NOTFOUND) THEN
928 CLOSE c_dai;
929 v_valid_dai := FALSE;
930 l_v_token1_val := v_ftci_fss_rec.retro_dt_alias ||','||TO_CHAR(v_ftci_fss_rec.retro_dai_sequence_number);
931 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_TYP_RDTA_DOSNT_EXST'); --new mwssage
932 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
933 fnd_file.put_line(fnd_file.log,fnd_message.Get);
934 ELSE
935 CLOSE c_dai;
936 END IF;
937 END IF;
938 IF v_valid_dai = FALSE THEN
939 v_process_next_ftci := TRUE;
940 END IF;
941 END IF;
942 IF (v_process_next_ftci = FALSE AND v_insert_record = TRUE ) THEN
943 IF (p_fee_type_ci_status IS NOT NULL) THEN
944 v_fee_type_ci_status := p_fee_type_ci_status;
945 ELSE
946 v_fee_type_ci_status := v_ftci_fss_rec.fee_type_ci_status;
947 END IF;
948 -- Validate the required data has been entered for
949 -- the Fee Type calendar status
950 IF IGS_FI_VAL_FTCI.finp_val_ftci_rqrd (
951 p_dest_cal_type,
952 p_dest_sequence_number,
953 v_ftci_fss_rec.fee_type,
954 NULL,
955 NULL,
956 v_ftci_fss_rec.s_chg_method_type,
957 v_ftci_fss_rec.rul_sequence_number,
958 v_fee_type_ci_status,
959 v_message_name) = FALSE THEN
960
961 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS',v_message_name));
962
963 v_process_next_ftci := TRUE;
964 ELSE
965 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
966 -- l_rowid with a value will throw Un-Handled Exception
967 IGS_FI_F_TYP_CA_INST_PKG.INSERT_ROW(
968 X_ROWID=>l_rowid,
969 X_FEE_TYPE=>v_ftci_fss_rec.fee_type,
970 X_FEE_CAL_TYPE=>p_dest_cal_type,
971 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
972 X_FEE_TYPE_CI_STATUS=>v_fee_type_ci_status,
973 X_START_DT_ALIAS=>v_ftci_fss_rec.start_dt_alias,
974 X_START_DAI_SEQUENCE_NUMBER=>v_ftci_fss_rec.start_dai_sequence_number,
975 X_END_DT_ALIAS=>v_ftci_fss_rec.end_dt_alias,
976 X_END_DAI_SEQUENCE_NUMBER=>v_ftci_fss_rec.end_dai_sequence_number,
977 X_RETRO_DT_ALIAS=>v_ftci_fss_rec.retro_dt_alias,
978 X_RETRO_DAI_SEQUENCE_NUMBER=>v_ftci_fss_rec.retro_dai_sequence_number,
979 X_S_CHG_METHOD_TYPE=>v_ftci_fss_rec.s_chg_method_type,
980 X_RUL_SEQUENCE_NUMBER=>v_ftci_fss_rec.rul_sequence_number,
981 -- Added by kkillams ,w.r.t Student Finanace (Finance Accounting) DLD bug#1882122
982 X_ACCT_HIER_ID =>v_ftci_fss_rec.acct_hier_id,
983 X_REC_GL_CCID =>v_ftci_fss_rec.rec_gl_ccid,
984 X_REV_ACCOUNT_CD =>v_ftci_fss_rec.rev_account_cd,
985 X_REC_ACCOUNT_CD =>v_ftci_fss_rec.rec_account_cd,
986 -- Added by Nishikant , to include the following new field for enhancement bug#1851586
987 X_INITIAL_DEFAULT_AMOUNT=>v_ftci_fss_rec.initial_default_amount,
988 X_MODE=>'R',
989 X_ORG_ID => l_n_org_id,
990 --Added by sarakshi, as a part of SFCR012, bug:2113459
991 X_RET_ACCOUNT_CD =>v_ftci_fss_rec.ret_account_cd,
992 X_RET_GL_CCID =>v_ftci_fss_rec.ret_gl_ccid,
993 X_RETENTION_LEVEL_CODE => v_ftci_fss_rec.retention_level_code,
994 X_COMPLETE_RET_FLAG => v_ftci_fss_rec.complete_ret_flag,
995 --Added by svuppala,as a part of bug:4295379
996 X_NONZERO_BILLABLE_CP_FLAG => v_ftci_fss_rec.nonzero_billable_cp_flag,
997 --Added by gurprsin,as a part of bug:3392088
998 X_SCOPE_RUL_SEQUENCE_NUM => v_ftci_fss_rec.scope_rul_sequence_num,
999 X_ELM_RNG_ORDER_NAME => v_ftci_fss_rec.elm_rng_order_name,
1000 X_MAX_CHG_ELEMENTS => v_ftci_fss_rec.max_chg_elements
1001 );
1002 v_ftci_inserted_ind := TRUE;
1003 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEETYPE_CALINST_ROLLED'));
1004 END IF;
1005 END IF;
1006
1007 IF (v_ftci_inserted_ind = TRUE OR v_ftci_exists_ind = TRUE ) THEN
1008 -- Identify the Retention Level from FTCI.
1009 l_v_retention_level_code := NVL(v_ftci_fss_rec.retention_level_code,'FEE_PERIOD');
1010
1011 -- If the Retention Level is Fee Period, then call the existing Retention Schedules
1012 -- Rollover Process
1013 IF l_v_retention_level_code = 'FEE_PERIOD' THEN
1014 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_frtns(
1015 p_source_cal_type,
1016 p_source_sequence_number,
1017 p_dest_cal_type,
1018 p_dest_sequence_number,
1019 cst_ftci,
1020 v_ftci_fss_rec.fee_type,
1021 NULL,
1022 v_message_name) = FALSE) THEN
1023 p_message_name := v_message_name;
1024 RETURN FALSE;
1025 ELSIF (v_message_name = cst_warning) THEN
1026 p_message_warning := v_message_name;
1027 END IF;
1028 -- If the Retention Level is Teaching Period, then call the new
1029 -- procedure for Rollover Teaching Period Retention Schedule
1030 ELSIF l_v_retention_level_code = 'TEACH_PERIOD' THEN
1031 finp_ins_roll_tprs(p_v_fee_type => v_ftci_fss_rec.fee_type,
1032 p_v_source_cal_type => p_source_cal_type,
1033 p_n_source_ci_seq_number => p_source_sequence_number,
1034 p_v_dest_cal_type => p_dest_cal_type,
1035 p_n_dest_ci_seq_number => p_dest_sequence_number,
1036 p_b_status => l_b_status,
1037 p_v_message_name => v_message_name);
1038 IF NOT l_b_status THEN
1039 p_message_name := v_message_name;
1040 RETURN FALSE;
1041 END IF;
1042 END IF;
1043 -- rollover related fee encumbrances
1044 --sykrishn 29november2001 Removed the procedure finp_ins_roll_fe - as part of obseletion in bug 2126091.
1045 -- rollover related charge method apportionments
1046 -- Enh # 2187247 : SFCR021 : FCI-LCI Relation
1047 -- Removed the call to function for Charge Method Apportion rollover
1048
1049 -- rollover related fee_assessment rates
1050 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_far(
1051 v_ftci_fss_rec.fee_type,
1052 p_source_cal_type,
1053 p_source_sequence_number,
1054 p_dest_cal_type,
1055 p_dest_sequence_number,
1056 cst_ftci,
1057 NULL,
1058 v_message_name) = FALSE) THEN
1059 p_message_name := v_message_name;
1060 RETURN FALSE;
1061 ELSIF (v_message_name = cst_warning) THEN
1062 p_message_warning := v_message_name;
1063 END IF;
1064 -- rollover related elements ranges
1065 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_er(
1066 v_ftci_fss_rec.fee_type,
1067 p_source_cal_type,
1068 p_source_sequence_number,
1069 p_dest_cal_type,
1070 p_dest_sequence_number,
1071 cst_ftci,
1072 NULL,
1073 v_message_name) = FALSE) THEN
1074 p_message_name := v_message_name;
1075 RETURN FALSE;
1076 ELSIF (v_message_name = cst_warning) THEN
1077 p_message_warning := v_message_name;
1078 END IF;
1079 -- rollover Ancillary related segments and rates, call made to new function defined during build
1080 IF (l_s_fee_type = 'ANCILLARY') THEN
1081 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_anc(
1082 v_ftci_fss_rec.fee_type,
1083 p_source_cal_type,
1084 p_source_sequence_number,
1085 p_dest_cal_type,
1086 p_dest_sequence_number,
1087 v_message_name) = FALSE) THEN
1088 p_message_name := v_message_name;
1089 RETURN FALSE;
1090 END IF;
1091 END IF;
1092 -- rollover of fee type revenue segments
1093 -- This Revenue Segements Rollover should be done only IF Oracle General Ledger is installed
1094 IF (g_v_gl_installed = 'Y' AND IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_revseg(
1095 v_ftci_fss_rec.fee_type,
1096 p_source_cal_type,
1097 p_source_sequence_number,
1098 p_dest_cal_type,
1099 p_dest_sequence_number,
1100 v_message_name) = FALSE) THEN
1101 p_message_name := v_message_name;
1102 RETURN FALSE;
1103 END IF;
1104
1105 -- Rollover FTCI Accounting Information IF the System Fee Type is OTHER or TUITION
1106 --Enh #3117341 Added AUDIT and SPECIAL as valid values for System Fee Type as a part
1107 -- AUDIT and SPECIAL FEES build.
1108 -- and the finpl_ins_roll_over_ftci_accts should be invoked for
1109 -- sysytem fee type of 'OTHER', 'TUTNFEE','AUDIT' and for fee type of
1110 -- SPECIAL it should be invoked ONLY When GL IS NOT INSTALLED in the sysytem.
1111 IF (l_s_fee_type IN ( 'OTHER', 'TUTNFEE','AUDIT','SPECIAL' ) ) THEN
1112
1113 IF l_s_fee_type = 'SPECIAL' THEN -- the rollover
1114 IF (g_v_gl_installed <> 'Y') THEN
1115 IF (finpl_ins_roll_over_ftci_accts(
1116 v_ftci_fss_rec.fee_type,
1117 p_source_cal_type,
1118 p_source_sequence_number,
1119 p_dest_cal_type,
1120 p_dest_sequence_number) = FALSE ) THEN
1121 RETURN TRUE; -- Returning TRUE because all the error messages, IF any are logged in the called procedure itself
1122 END IF; --ending part of finpl_ins_roll_over_ftci_accts .
1123 END IF; --ending part of check for general ledger is installed.
1124 ELSE --Else part l_s_fee_type = 'SPECIAL'
1125 IF (finpl_ins_roll_over_ftci_accts(
1126 v_ftci_fss_rec.fee_type,
1127 p_source_cal_type,
1128 p_source_sequence_number,
1129 p_dest_cal_type,
1130 p_dest_sequence_number) = FALSE ) THEN
1131 RETURN TRUE; -- Returning TRUE because all the error messages, IF any are logged in the called procedure itself
1132 END IF;
1133 END IF; --ending part of check for special fee type
1134 END IF;
1135 END IF;
1136 -- To show a message when there are no records available in fee type calendar instance table to rollover
1137 IF (v_insert_record = FALSE OR l_already_rolled) THEN
1138 IF p_fee_cat IS NOT NULL THEN
1139 IF NOT l_fcfl_exists_ind THEN
1140 fnd_file.put_line( fnd_file.LOG, fnd_message.get_string ('IGS', 'IGS_FI_NO_FEE_LIB_FOUND') );
1141 END IF;
1142 -- Removed logging of message IGS_FI_FTCI_NO_REC_FOUND in case the FTCI have already been rolled over
1143 -- as part of Bug 3038365
1144 END IF;
1145 END IF;
1146
1147 END LOOP;
1148
1149 -- To show a message when there are no records available in fee type calendar instance table to rollover
1150 IF ( l_b_records_found = FALSE) THEN
1151 p_message_name := 'IGS_FI_FTCI_NO_REC_FOUND';
1152 RETURN FALSE;
1153 END IF;
1154
1155 RETURN TRUE;
1156 EXCEPTION
1157 WHEN e_resource_busy THEN
1158 v_message_name := 'IGS_FI_FEETYPE_CALINST_LOCKED';
1159 p_message_name := v_message_name;
1160 RETURN FALSE;
1161
1162 WHEN OTHERS THEN
1163 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1164 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_FTCI_ERROR');
1165 fnd_message.set_token('FEE_TYPE',p_fee_type);
1166 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
1167 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
1168 fnd_file.put_line (fnd_file.log, fnd_message.get);
1169
1170 END;
1171 END finp_ins_roll_ftci;
1172 --
1173 -- Routine to rollover fee cat calendar instances between cal instances
1174 FUNCTION finp_ins_roll_fcci(
1175 p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
1176 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1177 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1178 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1179 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1180 p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
1181 p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE ,
1182 p_fee_type IN IGS_FI_FEE_TYPE.fee_type%TYPE ,
1183 p_message_name OUT NOCOPY VARCHAR2 ,
1184 p_message_warning OUT NOCOPY VARCHAR2 )
1185 /*----------------------------------------------------------------------------
1186 || Created By :
1187 || Created On :
1188 || Purpose :
1189 || Known limitations, enhancements or remarks :
1190 || Change History :
1191 || Who When What
1192 || (reverse chronological order - newest change first)
1193 || sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions caught and appropriate error message logged.
1194 || sapanigr 29-Mar-2006 Bug 4606670. Out parameter p_message_name assigned dummy value cst_warning
1195 || if calls to finp_ins_roll_frtns or finp_ins_roll_fcfl returns this value.
1196 || shtatiko 02-JUN-2003 Enh# 2831582, Logged all messages in context of a Fee Category.
1197 || pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
1198 || vvutukur 26-Aug-2002 Bug#2531390.The comment in the code regarding the rollover of fee payment
1199 || schedules is removed to avoid confusion.
1200 ----------------------------------------------------------------------------*/
1201 RETURN BOOLEAN AS
1202 gv_other_detail VARCHAR2(255);
1203 BEGIN
1204 DECLARE
1205 cst_active CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE :='ACTIVE';
1206 cst_fcci CONSTANT IGS_FI_FEE_AS_RATE.s_relation_type%TYPE := 'FCCI';
1207 v_message_name VARCHAR2(30);
1208 v_message_warning VARCHAR2(30);
1209 v_fcci_inserted_ind BOOLEAN;
1210 v_fcci_exists_ind BOOLEAN;
1211 v_process_next_fcci BOOLEAN;
1212 v_fee_cat IGS_FI_F_CAT_CA_INST.fee_cat%TYPE;
1213 v_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE;
1214 v_fee_cat_ci_status IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE;
1215 v_valid_dai BOOLEAN;
1216 v_dummy IGS_CA_DA_INST.dt_alias%TYPE;
1217 l_rowid VARCHAR2(25);
1218
1219 CURSOR c_fcci_fss IS
1220 SELECT fcci.fee_cat,
1221 fcci.fee_cat_ci_status,
1222 fcci.start_dt_alias,
1223 fcci.start_dai_sequence_number,
1224 fcci.end_dt_alias,
1225 fcci.end_dai_sequence_number,
1226 fcci.retro_dt_alias,
1227 fcci.retro_dai_sequence_number
1228 FROM IGS_FI_F_CAT_CA_INST fcci,
1229 IGS_FI_FEE_STR_STAT fss,
1230 IGS_FI_FEE_CAT fc
1231 WHERE (p_fee_cat IS NULL OR
1232 fcci.fee_cat = p_fee_cat) AND
1233 fcci.fee_cal_type = p_source_cal_type AND
1234 fcci.fee_ci_sequence_number = p_source_sequence_number AND
1235 fcci.fee_cat_ci_status = fss.fee_structure_status AND
1236 fss.s_fee_structure_status = cst_active AND
1237 fc.fee_cat = fcci.fee_cat AND
1238 fc.closed_ind = 'N';
1239 CURSOR c_fcci (
1240 cp_fee_cat IGS_FI_F_CAT_CA_INST.fee_cat%TYPE) IS
1241 SELECT fcci.fee_cat
1242 FROM IGS_FI_F_CAT_CA_INST fcci
1243 WHERE fcci.fee_cat= cp_fee_cat AND
1244 fcci.fee_cal_type = p_dest_cal_type AND
1245 fcci.fee_ci_sequence_number = p_dest_sequence_number;
1246 CURSOR c_fcfl (
1247 cp_fee_cat IGS_FI_F_CAT_FEE_LBL.fee_cat%TYPE) IS
1248 SELECT fcfl.fee_type
1249 FROM IGS_FI_F_CAT_FEE_LBL fcfl
1250 WHERE fcfl.fee_cat = cp_fee_cat AND
1251 fcfl.fee_cal_type = p_source_cal_type AND
1252 fcfl.fee_ci_sequence_number = p_source_sequence_number AND
1253 fcfl.fee_type = p_fee_type;
1254 CURSOR c_dai (
1255 cp_dt_alias IGS_CA_DA_INST.dt_alias%TYPE,
1256 cp_sequence_number IGS_CA_DA_INST.sequence_number%TYPE,
1257 cp_cal_type IGS_CA_DA_INST.cal_type%TYPE,
1258 cp_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE) IS
1259 SELECT dt_alias
1260 FROM IGS_CA_DA_INST
1261 WHERE dt_alias = cp_dt_alias AND
1262 sequence_number = cp_sequence_number AND
1263 cal_type = cp_cal_type AND
1264 ci_sequence_number = cp_ci_sequence_number;
1265 BEGIN
1266 -- This function will roll all IGS_FI_F_CAT_CA_INST records underneath a
1267 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
1268 -- It also controls the rollover of
1269 -- * fee_refund_schedule
1270 -- * IGS_FI_FEE_RET_SCHD
1271 -- * IGS_FI_FEE_ENCMB
1272 -- * IGS_FI_F_CAT_FEE_LBL
1273 -- The assumption is being made that the "destination" IGS_CA_INST
1274 -- is open and active - it is the responsibility of the calling routine
1275 -- to check for this.
1276 -- IGS_GE_NOTE: If some of the IGS_FI_F_CAT_CA_INST records already exist then
1277 -- these will remain unaltered.
1278 --------------------------------------------------------------------------
1279 p_message_name := Null;
1280 -- 1. Process the fee cat calendar instance records
1281 -- matching the source calendar instance.
1282 FOR v_fcci_fss_rec IN c_fcci_fss LOOP
1283 v_fcci_inserted_ind := FALSE;
1284 v_fcci_exists_ind := FALSE;
1285 v_process_next_fcci := FALSE;
1286
1287 fnd_file.put_line( fnd_file.LOG, RPAD('-', 79, '-') );
1288 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_CAT'), v_fcci_fss_rec.fee_cat );
1289
1290 -- Check the fee type is a liablility of the fee category
1291 -- when fee type is specified
1292 IF p_fee_type IS NOT NULL THEN
1293 OPEN c_fcfl(v_fcci_fss_rec.fee_cat);
1294 FETCH c_fcfl INTO v_fee_type;
1295 IF (c_fcfl%NOTFOUND) THEN
1296 -- process next IGS_FI_F_CAT_CA_INST
1297 v_process_next_fcci := TRUE;
1298 END IF;
1299 CLOSE c_fcfl;
1300 END IF;
1301 IF (v_process_next_fcci = FALSE) THEN
1302 -- Check for the existence of the IGS_FI_F_CAT_CA_INST
1303 -- record under the destination calendar
1304 OPEN c_fcci(v_fcci_fss_rec.fee_cat);
1305 FETCH c_fcci INTO v_fee_cat;
1306 IF (c_fcci%FOUND) THEN
1307 CLOSE c_fcci;
1308 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEECAT_CALINST_ROLL'));
1309 v_fcci_exists_ind := TRUE;
1310 ELSE
1311 CLOSE c_fcci;
1312 -- Check for the existence of the start dai
1313 -- record under the destination calendar
1314 OPEN c_dai( v_fcci_fss_rec.start_dt_alias,
1315 v_fcci_fss_rec.start_dai_sequence_number,
1316 p_dest_cal_type,
1317 p_dest_sequence_number);
1318 FETCH c_dai INTO v_dummy;
1319 IF (c_dai%NOTFOUND) THEN
1320 CLOSE c_dai;
1321 v_process_next_fcci := TRUE;
1322 l_v_token1_val := v_fcci_fss_rec.start_dt_alias ||','||TO_CHAR(v_fcci_fss_rec.start_dai_sequence_number);
1323 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_CAT_SDTA_DOSNT_EXST'); --new mwssage
1324 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
1325 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get);
1326 ELSE
1327 CLOSE c_dai;
1328 END IF;
1329 -- Check for the existence of the end dai
1330 -- record under the destination calendar
1331 OPEN c_dai( v_fcci_fss_rec.end_dt_alias,
1332 v_fcci_fss_rec.end_dai_sequence_number,
1333 p_dest_cal_type,
1334 p_dest_sequence_number);
1335 FETCH c_dai INTO v_dummy;
1336 IF (c_dai%NOTFOUND) THEN
1337 CLOSE c_dai;
1338 v_process_next_fcci := TRUE;
1339 l_v_token1_val := v_fcci_fss_rec.end_dt_alias ||','||TO_CHAR(v_fcci_fss_rec.end_dai_sequence_number);
1340 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_CAT_EDTA_DOSNT_EXST'); --new mwssage
1341 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
1342 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get);
1343 ELSE
1344 CLOSE c_dai;
1345 END IF;
1346 IF v_fcci_fss_rec.retro_dt_alias IS NOT NULL THEN
1347 -- Check for the existence of the retro dai
1348 -- record under the destination calendar
1349 OPEN c_dai( v_fcci_fss_rec.retro_dt_alias,
1350 v_fcci_fss_rec.retro_dai_sequence_number,
1351 p_dest_cal_type,
1352 p_dest_sequence_number);
1353 FETCH c_dai INTO v_dummy;
1354 IF (c_dai%NOTFOUND) THEN
1355 CLOSE c_dai;
1356 v_process_next_fcci := TRUE;
1357 l_v_token1_val := v_fcci_fss_rec.retro_dt_alias ||','||TO_CHAR(v_fcci_fss_rec.retro_dai_sequence_number);
1358 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_CAT_RDTA_DOSNT_EXST'); --new mwssage
1359 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
1360 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get);
1361
1362 ELSE
1363 CLOSE c_dai;
1364 END IF;
1365 END IF;
1366 END IF;
1367 IF (v_process_next_fcci = FALSE AND v_fcci_exists_ind = FALSE) THEN
1368 IF (p_fee_cat_ci_status IS NOT NULL) THEN
1369 v_fee_cat_ci_status := p_fee_cat_ci_status;
1370 ELSE
1371 v_fee_cat_ci_status := v_fcci_fss_rec.fee_cat_ci_status;
1372 END IF;
1373 IF IGS_FI_VAL_FCCI.finp_val_fss_closed (
1374 v_fee_cat_ci_status,
1375 v_message_name) = FALSE THEN
1376 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS',v_message_name));
1377 v_process_next_fcci := TRUE;
1378 END IF;
1379 IF IGS_FI_VAL_FCCI.finp_val_fcci_active (
1380 v_fee_cat_ci_status,
1381 p_dest_cal_type,
1382 p_dest_sequence_number,
1383 v_message_name) = FALSE THEN
1384
1385 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS',v_message_name));
1386 v_process_next_fcci := TRUE;
1387 END IF;
1388 END IF;
1389 IF (v_process_next_fcci = FALSE AND v_fcci_exists_ind = FALSE) THEN
1390 l_rowid :=NULL;-- initialise l_rowid to null before passing into the TBH
1391 -- l_rowid with a value will throw Un-Handled Exception
1392 IGS_FI_F_CAT_CA_INST_PKG.INSERT_ROW(
1393 X_ROWID=>l_rowid,
1394 X_FEE_CAT=>v_fcci_fss_rec.fee_cat,
1395 X_FEE_CAL_TYPE=>p_dest_cal_type,
1396 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
1397 X_FEE_CAT_CI_STATUS=>v_fee_cat_ci_status,
1398 X_START_DT_ALIAS=>v_fcci_fss_rec.start_dt_alias,
1399 X_START_DAI_SEQUENCE_NUMBER=>v_fcci_fss_rec.start_dai_sequence_number,
1400 X_END_DT_ALIAS=>v_fcci_fss_rec.end_dt_alias,
1401 X_END_DAI_SEQUENCE_NUMBER=>v_fcci_fss_rec.end_dai_sequence_number,
1402 X_RETRO_DT_ALIAS=>v_fcci_fss_rec.retro_dt_alias,
1403 X_RETRO_DAI_SEQUENCE_NUMBER=>v_fcci_fss_rec.retro_dai_sequence_number,
1404 X_MODE=>'R'
1405 );
1406 v_fcci_inserted_ind := TRUE;
1407 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEECAT_CALINST_ROLLED'));
1408 END IF;
1409 END IF;
1410 IF (v_fcci_inserted_ind = TRUE OR v_fcci_exists_ind = TRUE) THEN
1411
1412 -- rollover related fee retention schedule
1413 IF (finp_ins_roll_frtns(
1414 p_source_cal_type,
1415 p_source_sequence_number,
1416 p_dest_cal_type,
1417 p_dest_sequence_number,
1418 cst_fcci,
1419 NULL,
1420 v_fcci_fss_rec.fee_cat,
1421 v_message_name) = FALSE) THEN
1422 p_message_name := v_message_name;
1423 RETURN FALSE;
1424 ELSIF (v_message_name = cst_warning) THEN
1425 p_message_warning := v_message_name;
1426 END IF;
1427 -- rollover related fee encumbrances
1428 --sykrishn 29november2001 Removed the procedure finp_ins_roll_fe - as part of obseletion in bug 2126091.
1429 -- rollover related fee category fee liabilities
1430 IF (finp_ins_roll_fcfl(
1431 v_fcci_fss_rec.fee_cat,
1432 p_source_cal_type,
1433 p_source_sequence_number,
1434 p_dest_cal_type,
1435 p_dest_sequence_number,
1436 p_fee_type,
1437 p_fee_liability_status,
1438 v_message_name,
1439 v_message_warning) = FALSE) THEN
1440 p_message_name := v_message_name;
1441 RETURN FALSE;
1442 ELSIF (v_message_warning = cst_warning) THEN
1443 p_message_warning := v_message_warning;
1444 END IF;
1445 END IF;
1446 END LOOP;
1447 RETURN TRUE;
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1451 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_FCCI_ERROR');
1452 fnd_message.set_token('FEE_CAT',p_fee_cat);
1453 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
1454 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
1455 fnd_file.put_line (fnd_file.log, fnd_message.get);
1456 END;
1457 END finp_ins_roll_fcci;
1458 --
1459
1460 -- Routine to rollover fee retention schedules between cal instances
1461 FUNCTION finp_ins_roll_frtns(
1462 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1463 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1464 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1465 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1466 p_relation_type IN IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE ,
1467 p_fee_type IN IGS_FI_FEE_RET_SCHD.fee_type%TYPE ,
1468 p_fee_cat IN IGS_FI_FEE_RET_SCHD.fee_cat%TYPE ,
1469 p_message_name OUT NOCOPY VARCHAR2 )
1470 RETURN BOOLEAN AS
1471 /*----------------------------------------------------------------------------
1472 || Created By :
1473 || Created On :
1474 || Purpose :
1475 || Known limitations, enhancements or remarks :
1476 || Change History :
1477 || Who When What
1478 || (reverse chronological order - newest change first)
1479 || sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate error message logged.
1480 || sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into IGS_FI_FEE_RET_SCHD
1481 || is now rounded off to currency precision
1482 || sapanigr 29-Mar-2006 Bug# 4606670 Added check finp_val_frtns_creat. This validates that
1483 || when schedule defined at FTCI level, they cannot also be
1484 || defined at FCFL level and vice-versa.
1485 || pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
1486 || vvutukur 24-Jul-2002 Bug#2425767.Removed deduction_amount from select of cursor c_frtns_source,
1487 || removed x_deduction_amount parameter from call to IGS_FI_FEE_RET_SCHD_PKG.INSERT_ROW.
1488 ----------------------------------------------------------------------------*/
1489 gv_other_detail VARCHAR2(255);
1490 BEGIN
1491 DECLARE
1492 v_record_inserted_ind BOOLEAN;
1493 v_record_exists_ind BOOLEAN;
1494 v_valid_insert BOOLEAN;
1495 v_message_name VARCHAR2(30);
1496 v_sequence_number IGS_FI_FEE_RET_SCHD.sequence_number%TYPE;
1497 v_fee_type IGS_FI_FEE_RET_SCHD.fee_type%TYPE;
1498 v_fee_cat IGS_FI_FEE_RET_SCHD.fee_cat%TYPE;
1499 v_schedule_number IGS_FI_FEE_RET_SCHD.schedule_number%TYPE;
1500 v_dummy IGS_CA_DA_INST.dt_alias%TYPE;
1501 l_rowid VARCHAR2(25);
1502 l_b_ftci_fcci_clash_ind BOOLEAN := TRUE;
1503
1504 CURSOR c_frtns_source IS
1505 SELECT frtns.sequence_number,
1506 frtns.fee_type,
1507 frtns.fee_cat,
1508 frtns.schedule_number,
1509 frtns.dt_alias,
1510 frtns.dai_sequence_number,
1511 frtns.retention_percentage,
1512 frtns.retention_amount
1513 FROM IGS_FI_FEE_RET_SCHD frtns
1514 WHERE frtns.fee_cal_type = p_source_cal_type AND
1515 frtns.fee_ci_sequence_number = p_source_sequence_number AND
1516 frtns.s_relation_type = p_relation_type AND
1517 (frtns.fee_type = p_fee_type OR
1518 p_fee_type IS NULL) AND
1519 (frtns.fee_cat = p_fee_cat OR
1520 p_fee_cat IS NULL);
1521 CURSOR c_frtns_dest (
1522 cp_sequence_number IGS_FI_FEE_RET_SCHD.sequence_number%TYPE) IS
1523 SELECT frtns.fee_type,
1524 frtns.fee_cat,
1525 frtns.schedule_number
1526 FROM IGS_FI_FEE_RET_SCHD frtns
1527 WHERE frtns.fee_cal_type = p_dest_cal_type AND
1528 frtns.fee_ci_sequence_number = p_dest_sequence_number AND
1529 frtns.s_relation_type = p_relation_type AND
1530 frtns.sequence_number = cp_sequence_number;
1531 CURSOR c_frtns_dest_u (
1532 cp_fee_type IGS_FI_FEE_RET_SCHD.fee_type%TYPE,
1533 cp_fee_cat IGS_FI_FEE_RET_SCHD.fee_cat%TYPE,
1534 cp_schedule_number IGS_FI_FEE_RET_SCHD.schedule_number%TYPE) IS
1535 SELECT frtns.sequence_number
1536 FROM IGS_FI_FEE_RET_SCHD frtns
1537 WHERE frtns.fee_cal_type = p_dest_cal_type AND
1538 frtns.fee_ci_sequence_number = p_dest_sequence_number AND
1539 NVL(frtns.fee_type, 'NULL') = NVL(cp_fee_type, 'NULL') AND
1540 NVL(frtns.fee_cat, 'NULL') = NVL(cp_fee_cat, 'NULL') AND
1541 frtns.schedule_number = cp_schedule_number;
1542 CURSOR c_dai (
1543 cp_dt_alias IGS_CA_DA_INST.dt_alias%TYPE,
1544 cp_sequence_number IGS_CA_DA_INST.sequence_number%TYPE,
1545 cp_cal_type IGS_CA_DA_INST.cal_type%TYPE,
1546 cp_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE) IS
1547 SELECT dt_alias
1548 FROM IGS_CA_DA_INST
1549 WHERE dt_alias = cp_dt_alias AND
1550 sequence_number = cp_sequence_number AND
1551 cal_type = cp_cal_type AND
1552 ci_sequence_number = cp_ci_sequence_number;
1553 BEGIN
1554 -- This function will roll all IGS_FI_FEE_RET_SCHD records underneath a
1555 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
1556 -- The assumption is being made that the "destination" IGS_CA_INST
1557 -- is open and active - it is the responsibility of the calling routine to
1558 -- check for this.
1559 -- IGS_GE_NOTE: If some of the IGS_FI_FEE_RET_SCHD records already exist then
1560 -- these will remain unaltered.
1561 p_message_name := Null;
1562 -- 1. Process the fee retention schedule records matching the source calendar
1563 -- instance
1564 v_record_inserted_ind := FALSE;
1565 v_record_exists_ind := FALSE;
1566
1567 FOR v_frtns_source_rec IN c_frtns_source LOOP
1568 v_valid_insert := TRUE;
1569 -- Check for the existence of the IGS_FI_FEE_RET_SCHD
1570 -- record under the destination calendar
1571 OPEN c_frtns_dest(v_frtns_source_rec.sequence_number);
1572 FETCH c_frtns_dest INTO v_fee_type,
1573 v_fee_cat,
1574 v_schedule_number;
1575 IF (c_frtns_dest%FOUND) THEN
1576 CLOSE c_frtns_dest;
1577 IF (NVL(v_frtns_source_rec.fee_type, 'NULL') = NVL(v_fee_type, 'NULL') AND
1578 NVL(v_frtns_source_rec.fee_cat, 'NULL') = NVL(v_fee_cat, 'NULL')) THEN
1579 v_record_exists_ind := TRUE;
1580 ELSE
1581 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEE_RETN_SCH_CLASHES'));
1582 END IF;
1583 ELSE
1584 CLOSE c_frtns_dest;
1585 -- check the new schedule will be unique
1586 OPEN c_frtns_dest_u( v_frtns_source_rec.fee_type,
1587 v_frtns_source_rec.fee_cat,
1588 v_frtns_source_rec.schedule_number);
1589 FETCH c_frtns_dest_u INTO v_sequence_number;
1590 IF (c_frtns_dest_u%FOUND) THEN
1591 CLOSE c_frtns_dest_u;
1592 v_valid_insert := FALSE;
1593 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEE_RETN_SCH_CLASHES'));
1594
1595 ELSE
1596 CLOSE c_frtns_dest_u;
1597 -- Check for the existence of the IGS_CA_DA_INST
1598 -- record under the destination calendar
1599 OPEN c_dai( v_frtns_source_rec.dt_alias,
1600 v_frtns_source_rec.dai_sequence_number,
1601 p_dest_cal_type,
1602 p_dest_sequence_number);
1603 FETCH c_dai INTO v_dummy;
1604 IF (c_dai%NOTFOUND) THEN
1605 CLOSE c_dai;
1606 v_valid_insert := FALSE;
1607
1608 l_v_token1_val := v_frtns_source_rec.dt_alias ||','||TO_CHAR(v_frtns_source_rec.dai_sequence_number);
1609 token2_val := ' RELATION_TYPE:' || p_relation_type ||
1610 ', SEQUENCE_NUMBER:' || TO_CHAR(v_frtns_source_rec.sequence_number)||
1611 ', SCHEDULE_NUMBER:' || TO_CHAR(v_frtns_source_rec.schedule_number);
1612 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_R_SCH_DTA_DOSNT_EXST'); --new mwssage
1613 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
1614 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1615 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
1616 ELSE
1617 CLOSE c_dai;
1618 END IF;
1619 END IF;
1620 IF v_valid_insert THEN
1621 BEGIN
1622 -- When schedule to be defined at FTCI level, proceed only if not
1623 -- defined at FCFL level and vice-versa.
1624 IF (l_b_ftci_fcci_clash_ind) THEN
1625 IF IGS_FI_VAL_FRTNS.finp_val_frtns_creat(
1626 p_fee_type,
1627 p_dest_cal_type,
1628 p_dest_sequence_number,
1629 p_relation_type,
1630 v_message_name) THEN
1631 l_rowid := NULL;-- initialise l_rowid to null before passing into the TBH
1632 -- l_rowid with a value will throw Un-Handled Exception
1633 -- Call to igs_fi_gen_gl.get_formatted_amount formats ret_amount by rounding off to currency precision
1634 IGS_FI_FEE_RET_SCHD_PKG.INSERT_ROW(
1635 X_ROWID=>l_rowid,
1636 X_FEE_CAL_TYPE=>p_dest_cal_type,
1637 X_SEQUENCE_NUMBER=>v_frtns_source_rec.sequence_number,
1638 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
1639 X_S_RELATION_TYPE=>p_relation_type,
1640 X_FEE_CAT=>v_frtns_source_rec.fee_cat,
1641 X_FEE_TYPE=>v_frtns_source_rec.fee_type,
1642 X_SCHEDULE_NUMBER=>v_frtns_source_rec.schedule_number,
1643 X_DT_ALIAS=>v_frtns_source_rec.dt_alias,
1644 X_DAI_SEQUENCE_NUMBER=>v_frtns_source_rec.dai_sequence_number,
1645 X_RETENTION_PERCENTAGE=>v_frtns_source_rec.retention_percentage,
1646 X_RETENTION_AMOUNT=>igs_fi_gen_gl.get_formatted_amount(v_frtns_source_rec.retention_amount),
1647 X_MODE=>'R'
1648 );
1649 v_record_inserted_ind := TRUE;
1650 ELSE
1651 IF (v_message_name= 'IGS_FI_FEE_RETN_SCH_FEECAT') THEN
1652 fnd_message.set_name('IGS', 'IGS_FI_FRETS_FTCI_FCFL_EXIST');
1653 fnd_message.set_token('SOURCE', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FCFL'));
1654 fnd_message.set_token('DESTINATION', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FTCI'));
1655 fnd_file.put_line(fnd_file.log, fnd_message.get);
1656 ELSIF (v_message_name= 'IGS_FI_FEE_RETN_SCH_FEETYPE') THEN
1657 fnd_message.set_name('IGS', 'IGS_FI_FRETS_FTCI_FCFL_EXIST');
1658 fnd_message.set_token('SOURCE', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FTCI'));
1659 fnd_message.set_token('DESTINATION', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FCFL'));
1660 fnd_file.put_line(fnd_file.log, fnd_message.get);
1661 END IF;
1662 p_message_name := cst_warning;
1663 l_b_ftci_fcci_clash_ind := FALSE;
1664 END IF;
1665 END IF;
1666 EXCEPTION
1667 WHEN OTHERS THEN
1668 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1669 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_FRTNS_ERROR');
1670 fnd_message.set_token('FEE_CAT',p_fee_cat);
1671 fnd_message.set_token('FEE_TYPE',p_fee_type);
1672 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
1673 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
1674 fnd_message.set_token('DT_ALIAS',v_frtns_source_rec.dt_alias);
1675 fnd_file.put_line (fnd_file.log, fnd_message.get);
1676 END;
1677 END IF;
1678 END IF;
1679 END LOOP;
1680 -- 2. Check IF records rolled over
1681 IF (v_record_exists_ind = TRUE) THEN
1682 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEE_RETN_SCH_ROLLED'));
1683 END IF;
1684 IF (v_record_inserted_ind = TRUE) THEN
1685 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('IGS','IGS_FI_FEERET_SCH_ROLLED'));
1686 END IF;
1687 RETURN TRUE;
1688 END;
1689
1690
1691 END finp_ins_roll_frtns;
1692 --
1693 -- Routine to rollover fee encumbrances between cal instances
1694 --sykrishn 29november2001 Removed the procedure finp_ins_roll_fe - as part of obseletion in bug 2126091.
1695 --
1696 -- Routine to rollover fee cat fee liabilities between cal instances
1697 FUNCTION finp_ins_roll_fcfl(
1698 p_fee_cat IN IGS_FI_F_CAT_FEE_LBL.fee_cat%TYPE ,
1699 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1700 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1701 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1702 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1703 p_fee_type IN IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE ,
1704 p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE ,
1705 p_message_name OUT NOCOPY VARCHAR2 ,
1706 p_message_warning OUT NOCOPY VARCHAR2 )
1707 RETURN BOOLEAN AS
1708 /*----------------------------------------------------------------------------
1709 || Created By :
1710 || Created On :
1711 || Purpose :
1712 || Known limitations, enhancements or remarks :
1713 || Change History :
1714 || Who When What
1715 || (reverse chronological order - newest change first)
1716 || sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate error message logged.
1717 || sapanigr 29-Mar-2006 Bug 4606670. Out parameter p_message_name assigned dummy value cst_warning
1718 || if calls to finp_ins_roll_frtns or finp_ins_roll_far returns this value.
1719 || akandreg 10-Nov-2005 Bugs 4680440 , 4232201 - Validation on Charge Method/Rule Seq Num at FCFL level
1720 || Before rolling over an FCFL, validate whether Charge Method/Rule Seq Num has a value at source.
1721 || svuppala 22-Aug-2005 Enh 3392095, Added waiver_calc_flag column to the IGS_FI_F_CAT_FEE_LBL_Pkg.Insert_Row
1722 || Modified cursor c_fcfl_fss to get waiver_calc_flag
1723 || pathipat 12-Jul-2004 Bug 3759552 - Added code to roll over Fee Trig Groups, Unit Triggers
1724 || and Unit Set Triggers.
1725 || pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
1726 || vvutukur 26-Aug-2002 Bug#2531390.The comment in the code regarding the rollover of fee payment
1727 || schedules is removed to avoid confusion.
1728 || vvutukur 23-Jul-2002 Bug#2425767.Removed references to payment_hierarchy_rank(from cursor
1729 || c_fcfl_fss and from the call to IGS_FI_F_CAT_FEE_LBL_PKG.INSERT_ROW).
1730 ----------------------------------------------------------------------------*/
1731 gv_other_detail VARCHAR2(255);
1732 l_v_token1_val IGS_LOOKUP_VALUES.MEANING%TYPE;
1733 BEGIN
1734 DECLARE
1735 cst_active CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE :=
1736 'ACTIVE';
1737 cst_fcfl CONSTANT IGS_FI_FEE_AS_RATE.s_relation_type%TYPE := 'FCFL';
1738 v_message_name VARCHAR2(30);
1739 v_message_warning VARCHAR2(30);
1740 v_fcfl_inserted_ind BOOLEAN;
1741 v_fcfl_exists_ind BOOLEAN;
1742 v_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE;
1743 v_fee_liability_status IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE;
1744 v_next_fcfl BOOLEAN;
1745 v_valid_dai BOOLEAN;
1746 v_dummy IGS_CA_DA_INST.dt_alias%TYPE;
1747 l_n_org_id IGS_FI_F_CAT_FEE_LBL.ORG_ID%type := igs_ge_gen_003.get_org_id;
1748 l_rowid VARCHAR2(25);
1749 CURSOR c_fcfl_fss IS
1750 SELECT fcfl.fee_type,
1751 fcfl.fee_liability_status,
1752 fcfl.start_dt_alias,
1753 fcfl.start_dai_sequence_number,
1754 fcfl.s_chg_method_type,
1755 fcfl.rul_sequence_number,
1756 fcfl.fee_cat,
1757 fcfl.waiver_calc_flag
1758 FROM IGS_FI_F_CAT_FEE_LBL fcfl,
1759 IGS_FI_FEE_STR_STAT fss
1760 WHERE fcfl.fee_cat = p_fee_cat AND
1761 fcfl.fee_cal_type = p_source_cal_type AND
1762 fcfl.fee_ci_sequence_number = p_source_sequence_number AND
1763 (fcfl.fee_type = p_fee_type OR
1764 p_fee_type IS NULL) AND
1765 fcfl.fee_liability_status = fss.fee_structure_status AND
1766 fss.s_fee_structure_status = cst_active;
1767 CURSOR c_fcfl (
1768 cp_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE) IS
1769 SELECT fcfl.fee_type
1770 FROM IGS_FI_F_CAT_FEE_LBL fcfl
1771 WHERE fcfl.fee_cat = p_fee_cat AND
1772 fcfl.fee_cal_type = p_dest_cal_type AND
1773 fcfl.fee_ci_sequence_number = p_dest_sequence_number AND
1774 fcfl.fee_type = cp_fee_type;
1775 CURSOR c_ftci (
1776 cp_fee_type IGS_FI_F_TYP_CA_INST.fee_type%TYPE) IS
1777 SELECT ftci.fee_type
1778 FROM IGS_FI_F_TYP_CA_INST ftci
1779 WHERE ftci.fee_type = cp_fee_type AND
1780 ftci.fee_cal_type = p_dest_cal_type AND
1781 ftci.fee_ci_sequence_number = p_dest_sequence_number;
1782 CURSOR c_dai (
1783 cp_dt_alias IGS_CA_DA_INST.dt_alias%TYPE,
1784 cp_sequence_number IGS_CA_DA_INST.sequence_number%TYPE,
1785 cp_cal_type IGS_CA_DA_INST.cal_type%TYPE,
1786 cp_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE) IS
1787 SELECT dt_alias
1788 FROM IGS_CA_DA_INST
1789 WHERE dt_alias = cp_dt_alias AND
1790 sequence_number = cp_sequence_number AND
1791 cal_type = cp_cal_type AND
1792 ci_sequence_number = cp_ci_sequence_number;
1793 BEGIN
1794 -- This function will roll all IGS_FI_F_CAT_FEE_LBL records underneath a
1795 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
1796 -- It also controls the rollovr of
1797 -- * fee_refund_schedule
1798 -- * IGS_FI_FEE_RET_SCHD
1799 -- * IGS_FI_FEE_ENCMB
1800 -- * IGS_FI_CHG_MTH_APP
1801 -- * IGS_FI_FEE_AS_RATE
1802 -- * IGS_FI_ELM_RANGE
1803 -- The assumption is being made that the "destination" IGS_CA_INST
1804 -- is open and active - it is the responsibility of the calling routine
1805 -- to check for this.
1806 -- IGS_GE_NOTE: If some of the IGS_FI_F_CAT_FEE_LBL records already exist then
1807 -- these will remain unaltered.
1808 p_message_name := Null;
1809 -- 1. Process the fee category fee liability records matching the
1810 -- source calendar instance.
1811 FOR v_fcfl_fss_rec IN c_fcfl_fss LOOP
1812 v_fcfl_inserted_ind := FALSE;
1813 v_fcfl_exists_ind := FALSE;
1814
1815 -- Rollover of Fee Category Fee Liability is prevented if
1816 -- the Charge Method has been defined at Fee Category Fee Liability level.
1817
1818 IF v_fcfl_fss_rec.s_chg_method_type IS NOT NULL OR v_fcfl_fss_rec.rul_sequence_number IS NOT NULL then
1819 l_v_token1_val := IGS_FI_GEN_GL.get_lkp_meaning('IGS_FI_LOCKBOX','FCFL') ;
1820 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_NO_ROLL_CHG_METHOD');
1821 FND_MESSAGE.SET_TOKEN('FTCI_FCFL',l_v_token1_val);
1822 fnd_file.put_line(fnd_file.log,fnd_message.Get);
1823 v_next_fcfl := TRUE;
1824 END IF;
1825 -- Check for the existence of the IGS_FI_F_CAT_FEE_LBL
1826 -- record under the destination calendar
1827
1828 OPEN c_fcfl(
1829 v_fcfl_fss_rec.fee_type);
1830 FETCH c_fcfl INTO v_fee_type;
1831
1832 IF (c_fcfl%FOUND) THEN
1833 CLOSE c_fcfl;
1834 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_FEECAT_FEE_LIAB_ROLLED'));
1835 v_fcfl_exists_ind := TRUE;
1836 ELSE
1837 CLOSE c_fcfl;
1838 v_next_fcfl := FALSE;
1839 IF (p_fee_liability_status IS NOT NULL) THEN
1840 v_fee_liability_status := p_fee_liability_status;
1841 ELSE
1842 v_fee_liability_status := v_fcfl_fss_rec.fee_liability_status;
1843 END IF;
1844 -- Check for the existence of the IGS_FI_F_TYP_CA_INST
1845 -- record under the destination calendar
1846 OPEN c_ftci( v_fcfl_fss_rec.fee_type);
1847 FETCH c_ftci INTO v_fee_type;
1848 IF (c_ftci%NOTFOUND) THEN
1849 -- process next IGS_FI_F_CAT_FEE_LBL
1850 v_next_fcfl := TRUE;
1851 END IF;
1852 CLOSE c_ftci;
1853 -- validate status
1854 IF (v_next_fcfl = FALSE) THEN
1855 IF (IGS_FI_VAL_FCFL.finp_val_fcfl_active(
1856 v_fee_liability_status,
1857 p_dest_cal_type,
1858 p_dest_sequence_number,
1859 v_message_name) = FALSE) THEN
1860 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS',v_message_name));
1861 -- process next IGS_FI_F_CAT_FEE_LBL
1862 v_next_fcfl := TRUE;
1863 END IF;
1864 END IF;
1865 IF (v_next_fcfl = FALSE) THEN
1866 IF (IGS_FI_VAL_FCFL.finp_val_fcfl_status(
1867 p_dest_cal_type,
1868 p_dest_sequence_number,
1869 p_fee_cat,
1870 v_fcfl_fss_rec.fee_type,
1871 v_fee_liability_status,
1872 v_message_name) = FALSE) THEN
1873 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS',v_message_name));
1874 -- process next IGS_FI_F_CAT_FEE_LBL
1875 v_next_fcfl := TRUE;
1876 END IF;
1877 END IF;
1878 IF (v_next_fcfl = FALSE) THEN
1879 v_valid_dai := TRUE;
1880 IF v_fcfl_fss_rec.start_dt_alias IS NOT NULL THEN
1881 -- Check for the existence of the start dai
1882 -- record under the destination calendar
1883 OPEN c_dai( v_fcfl_fss_rec.start_dt_alias,
1884 v_fcfl_fss_rec.start_dai_sequence_number,
1885 p_dest_cal_type,
1886 p_dest_sequence_number);
1887 FETCH c_dai INTO v_dummy;
1888 IF (c_dai%NOTFOUND) THEN
1889 CLOSE c_dai;
1890 v_valid_dai := FALSE;
1891
1892 l_v_token1_val := v_fcfl_fss_rec.start_dt_alias ||','||TO_CHAR(v_fcfl_fss_rec.start_dai_sequence_number);
1893 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_F_CAT_F_LIA_DOSNT_EXST'); --new mwssage
1894 FND_MESSAGE.SET_TOKEN('TOKEN1',l_v_token1_val);
1895 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get);
1896 ELSE
1897 CLOSE c_dai;
1898 END IF;
1899 END IF;
1900 IF v_valid_dai THEN
1901 BEGIN
1902 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
1903 -- l_rowid with a value will throw Un-Handled Exception
1904 IGS_FI_F_CAT_FEE_LBL_PKG.INSERT_ROW(
1905 X_ROWID=>l_rowid,
1906 X_FEE_CAT=>p_fee_cat,
1907 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
1908 X_FEE_TYPE=>v_fcfl_fss_rec.fee_type,
1909 X_FEE_CAL_TYPE=>p_dest_cal_type,
1910 X_FEE_LIABILITY_STATUS=>v_fee_liability_status,
1911 X_START_DT_ALIAS=>v_fcfl_fss_rec.start_dt_alias,
1912 X_START_DAI_SEQUENCE_NUMBER=>v_fcfl_fss_rec.start_dai_sequence_number,
1913 X_S_CHG_METHOD_TYPE=>v_fcfl_fss_rec.s_chg_method_type,
1914 X_RUL_SEQUENCE_NUMBER=>v_fcfl_fss_rec.rul_sequence_number,
1915 X_WAIVER_CALC_FLAG => v_fcfl_fss_rec.waiver_calc_flag,
1916 X_MODE=>'R'
1917 );
1918
1919 v_fcfl_inserted_ind := TRUE;
1920 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_FEECAT_FEELIAB_ROLLED'));
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1924 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_FCFL_ERROR');
1925 fnd_message.set_token('FEE_CAT',p_fee_cat);
1926 fnd_message.set_token('FEE_TYPE',v_fcfl_fss_rec.fee_type);
1927 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
1928 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
1929 fnd_file.put_line (fnd_file.log, fnd_message.get);
1930 END;
1931 END IF;
1932 END IF;
1933 END IF;
1934 IF (v_fcfl_inserted_ind = TRUE OR v_fcfl_exists_ind = TRUE) THEN
1935
1936 -- rollover related fee retention schedule
1937 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_frtns(
1938 p_source_cal_type,
1939 p_source_sequence_number,
1940 p_dest_cal_type,
1941 p_dest_sequence_number,
1942 cst_fcfl,
1943 v_fcfl_fss_rec.fee_type,
1944 v_fcfl_fss_rec.fee_cat,
1945 v_message_name) = FALSE) THEN
1946 p_message_name := v_message_name;
1947 RETURN FALSE;
1948 ELSIF (v_message_name = cst_warning) THEN
1949 p_message_warning := v_message_name;
1950 END IF;
1951 -- rollover related fee encumbrances
1952 -- sykrishn 29november2001 Removed the procedure finp_ins_roll_fe - as part of obseletion in bug 2126091.
1953
1954 -- rollover related charge method apportionments
1955 -- Enh # 2187247 : SFCR021 : FCI-LCI Relation
1956 -- Removed the call to function for Charge Method Apportion rollover
1957
1958 -- rollover related fee assessment rates
1959 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_far(
1960 v_fcfl_fss_rec.fee_type,
1961 p_source_cal_type,
1962 p_source_sequence_number,
1963 p_dest_cal_type,
1964 p_dest_sequence_number,
1965 cst_fcfl,
1966 v_fcfl_fss_rec.fee_cat,
1967 v_message_name) = FALSE) THEN
1968 p_message_name := v_message_name;
1969 RETURN FALSE;
1970 ELSIF (v_message_name = cst_warning) THEN
1971 p_message_warning := v_message_name;
1972 END IF;
1973 -- rollover related elements ranges
1974 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_er(
1975 v_fcfl_fss_rec.fee_type,
1976 p_source_cal_type,
1977 p_source_sequence_number,
1978 p_dest_cal_type,
1979 p_dest_sequence_number,
1980 cst_fcfl,
1981 v_fcfl_fss_rec.fee_cat,
1982 v_message_name) = FALSE) THEN
1983 p_message_name := v_message_name;
1984 RETURN FALSE;
1985 ELSIF (v_message_name = cst_warning) THEN
1986 p_message_warning := v_message_name;
1987 END IF;
1988
1989 -- Roll over Fee Trigger Groups
1990 IF (finp_ins_roll_trg_grp( p_fee_cat => v_fcfl_fss_rec.fee_cat,
1991 p_source_cal_type => p_source_cal_type,
1992 p_source_sequence_number => p_source_sequence_number,
1993 p_dest_cal_type => p_dest_cal_type,
1994 p_dest_sequence_number => p_dest_sequence_number,
1995 p_fee_type => v_fcfl_fss_rec.fee_type,
1996 p_message_name => v_message_name) = FALSE) THEN
1997 p_message_name := v_message_name;
1998 RETURN FALSE;
1999 END IF;
2000
2001 -- rollover related IGS_PS_COURSE type fee triggers
2002 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_ctft(
2003 v_fcfl_fss_rec.fee_cat,
2004 p_source_cal_type,
2005 p_source_sequence_number,
2006 p_dest_cal_type,
2007 p_dest_sequence_number,
2008 v_fcfl_fss_rec.fee_type,
2009 v_message_name) = FALSE) THEN
2010 p_message_name := v_message_name;
2011 RETURN FALSE;
2012 END IF;
2013 -- rollover related IGS_PS_COURSE group fee triggers
2014 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_cgft(
2015 v_fcfl_fss_rec.fee_cat,
2016 p_source_cal_type,
2017 p_source_sequence_number,
2018 p_dest_cal_type,
2019 p_dest_sequence_number,
2020 v_fcfl_fss_rec.fee_type,
2021 v_message_name) = FALSE) THEN
2022 p_message_name := v_message_name;
2023 RETURN FALSE;
2024 END IF;
2025 -- rollover related IGS_PS_COURSE fee triggers
2026 IF (IGS_FI_PRC_FEE_ROLLV.finp_ins_roll_cft(
2027 v_fcfl_fss_rec.fee_cat,
2028 p_source_cal_type,
2029 p_source_sequence_number,
2030 p_dest_cal_type,
2031 p_dest_sequence_number,
2032 v_fcfl_fss_rec.fee_type,
2033 v_message_name) = FALSE) THEN
2034 p_message_name := v_message_name;
2035 RETURN FALSE;
2036 END IF;
2037
2038 -- Roll over Unit Fee Triggers
2039 IF (finp_ins_roll_uft( p_fee_cat => v_fcfl_fss_rec.fee_cat,
2040 p_source_cal_type => p_source_cal_type,
2041 p_source_sequence_number => p_source_sequence_number,
2042 p_dest_cal_type => p_dest_cal_type,
2043 p_dest_sequence_number => p_dest_sequence_number,
2044 p_fee_type => v_fcfl_fss_rec.fee_type,
2045 p_message_name => v_message_name) = FALSE) THEN
2046 p_message_name := v_message_name;
2047 RETURN FALSE;
2048 END IF;
2049
2050 -- Roll over Unit Set Fee Triggers
2051 IF (finp_ins_roll_usft( p_fee_cat => v_fcfl_fss_rec.fee_cat,
2052 p_source_cal_type => p_source_cal_type,
2053 p_source_sequence_number => p_source_sequence_number,
2054 p_dest_cal_type => p_dest_cal_type,
2055 p_dest_sequence_number => p_dest_sequence_number,
2056 p_fee_type => v_fcfl_fss_rec.fee_type,
2057 p_message_name => v_message_name) = FALSE) THEN
2058 p_message_name := v_message_name;
2059 RETURN FALSE;
2060 END IF;
2061 END IF;
2062 END LOOP;
2063 RETURN TRUE;
2064 END;
2065 END finp_ins_roll_fcfl;
2066 --
2067 -- Routine to rollover charge method apportionments between cal instances
2068 -- Enh # 2187247
2069 -- SFCR021 : FCI-LCI Relation
2070 -- Removed the function for Charge Method Apportion rollover
2071 --
2072 -- Routine to rollover fee assessment rates between cal instances
2073 FUNCTION finp_ins_roll_far(
2074 p_fee_type IN IGS_FI_FEE_AS_RATE.fee_type%TYPE ,
2075 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2076 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2077 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2078 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2079 p_relation_type IN IGS_FI_FEE_AS_RATE.s_relation_type%TYPE ,
2080 p_fee_cat IN IGS_FI_FEE_AS_RATE.fee_cat%TYPE ,
2081 p_message_name OUT NOCOPY VARCHAR2 )
2082 RETURN BOOLEAN AS
2083 /*******************************************************************/
2084 --Change History
2085 --Who When What
2086 --sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
2087 -- error message logged.
2088 --sapanigr 29-Mar-2006 Bug# 4606670 Added check finp_val_far_create. This validates that
2089 -- when rates defined at FTCI level, they cannot also be
2090 -- defined at FCFL level and vice-versa.
2091 --svuppala 03-Jun-2005 Enh# 3442712 Added Unit Program Type Level, Unit Mode, Unit Code,
2092 -- Unit Version and Unit Level
2093 --pathipat 11-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
2094 -- Modified cursor c_far_source, call to igs_fi_fee_as_rate_pkg.insert_row
2095 /*******************************************************************/
2096 gv_other_detail VARCHAR2(255);
2097 BEGIN
2098 DECLARE
2099 v_record_inserted_ind BOOLEAN;
2100 v_record_exists_ind BOOLEAN;
2101 v_message_name VARCHAR2(30);
2102 v_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE;
2103 v_logical_delete_dt IGS_FI_FEE_AS_RATE.logical_delete_dt%TYPE;
2104 v_FAR_ID NUMBER;
2105 l_rowid VARCHAR2(25);
2106 l_b_ftci_fcci_clash_ind BOOLEAN := TRUE;
2107 CURSOR c_far_source IS
2108 SELECT far.fee_type,
2109 far.rate_number,
2110 far.fee_cat,
2111 far.location_cd,
2112 far.attendance_type,
2113 far.attendance_mode,
2114 far.order_of_precedence,
2115 far.govt_hecs_payment_option,
2116 far.govt_hecs_cntrbtn_band,
2117 far.chg_rate,
2118 far.unit_class,
2119 far.residency_status_cd,
2120 far.course_cd,
2121 far.version_number,
2122 far.org_party_id,
2123 far.class_standing,
2124 far.unit_set_cd,
2125 far.us_version_number,
2126 far.unit_cd,
2127 far.unit_version_number,
2128 far.unit_level,
2129 far.unit_type_id,
2130 far.unit_mode
2131 FROM IGS_FI_FEE_AS_RATE far
2132 WHERE far.fee_type = p_fee_type AND
2133 far.fee_cal_type = p_source_cal_type AND
2134 far.fee_ci_sequence_number = p_source_sequence_number AND
2135 far.s_relation_type = p_relation_type AND
2136 far.logical_delete_dt is NULL AND
2137 (far.fee_cat = p_fee_cat OR p_fee_cat IS NULL);
2138 CURSOR c_far_dest (
2139 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE) IS
2140 SELECT far.rate_number,
2141 far.logical_delete_dt
2142 FROM IGS_FI_FEE_AS_RATE far
2143 WHERE far.fee_type = p_fee_type AND
2144 far.fee_cal_type = p_dest_cal_type AND
2145 far.fee_ci_sequence_number = p_dest_sequence_number AND
2146 far.s_relation_type = p_relation_type AND
2147 far.rate_number = cp_rate_number;
2148 BEGIN
2149 -- This function will roll all IGS_FI_FEE_AS_RATE records underneath a
2150 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
2151 -- The assumption is being made that the "destination" IGS_CA_INST
2152 -- is open and active - it is the responsibility of the calling routine to
2153 -- check for this.
2154 -- IGS_GE_NOTE: If some of the IGS_FI_FEE_AS_RATE records already exist then these will
2155 -- remain unaltered.
2156 p_message_name := Null;
2157 -- 1. Process the fee assessment rate records matching the source calendar
2158 -- instance
2159 v_record_inserted_ind := FALSE;
2160 v_record_exists_ind := FALSE;
2161
2162 FOR v_far_source_rec IN c_far_source LOOP
2163 -- Check for the existence of the IGS_FI_FEE_AS_RATE
2164 -- record under the destination calendar
2165 OPEN c_far_dest(v_far_source_rec.rate_number);
2166 FETCH c_far_dest INTO v_rate_number,
2167 v_logical_delete_dt;
2168 IF (c_far_dest%FOUND) THEN
2169 CLOSE c_far_dest;
2170 IF (v_logical_delete_dt IS NULL) THEN
2171 v_record_exists_ind := TRUE;
2172 END IF;
2173 ELSE
2174 CLOSE c_far_dest;
2175 BEGIN
2176 IF (l_b_ftci_fcci_clash_ind) THEN -- check flag
2177 -- When rates to be defined at FTCI level, proceed only
2178 -- if not defined at FCFL level and vice-versa.
2179 IF igs_fi_val_far.finp_val_far_create(p_fee_type,
2180 p_dest_cal_type,
2181 p_dest_sequence_number,
2182 p_relation_type,
2183 v_message_name) THEN
2184 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
2185 -- l_rowid with a value will throw Un-Handled Exception
2186 IGS_FI_FEE_AS_RATE_PKG.INSERT_ROW(
2187 X_ROWID => l_rowid,
2188 x_FAR_ID => v_FAR_ID,
2189 X_FEE_TYPE => v_far_source_rec.fee_type,
2190 X_FEE_CAL_TYPE => p_dest_cal_type,
2191 X_FEE_CI_SEQUENCE_NUMBER => p_dest_sequence_number,
2192 X_S_RELATION_TYPE => p_relation_type,
2193 X_RATE_NUMBER => v_far_source_rec.rate_number,
2194 X_FEE_CAT => v_far_source_rec.fee_cat,
2195 X_LOCATION_CD => v_far_source_rec.location_cd,
2196 X_ATTENDANCE_TYPE => v_far_source_rec.attendance_type,
2197 X_ATTENDANCE_MODE => v_far_source_rec.attendance_mode,
2198 X_ORDER_OF_PRECEDENCE => v_far_source_rec.order_of_precedence,
2199 X_GOVT_HECS_PAYMENT_OPTION => v_far_source_rec.govt_hecs_payment_option,
2200 X_GOVT_HECS_CNTRBTN_BAND => v_far_source_rec.govt_hecs_cntrbtn_band,
2201 X_CHG_RATE => v_far_source_rec.chg_rate,
2202 X_LOGICAL_DELETE_DT => NULL,
2203 X_RESIDENCY_STATUS_CD => v_far_source_rec.residency_status_cd,
2204 X_COURSE_CD => v_far_source_rec.course_cd,
2205 X_VERSION_NUMBER => v_far_source_rec.version_number,
2206 X_ORG_PARTY_ID => v_far_source_rec.org_party_id,
2207 X_CLASS_STANDING => v_far_source_rec.class_standing,
2208 X_MODE => 'R',
2209 x_unit_set_cd => v_far_source_rec.unit_set_cd,
2210 x_us_version_number => v_far_source_rec.us_version_number,
2211 x_unit_cd => v_far_source_rec.unit_cd,
2212 x_unit_version_number => v_far_source_rec.unit_version_number,
2213 x_unit_level => v_far_source_rec.unit_level ,
2214 x_unit_type_id => v_far_source_rec.unit_type_id,
2215 x_unit_class => v_far_source_rec.unit_class ,
2216 x_unit_mode => v_far_source_rec.unit_mode
2217 );
2218 v_record_inserted_ind := TRUE;
2219 ELSE
2220 IF (v_message_name= 'IGS_FI_ASSRATES_NOT_DEFINED') THEN
2221 fnd_message.set_name('IGS', 'IGS_FI_FAR_FTCI_FCFL_EXIST');
2222 fnd_message.set_token('SOURCE', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FCFL'));
2223 fnd_message.set_token('DESTINATION', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FTCI'));
2224 fnd_file.put_line(fnd_file.log, fnd_message.get);
2225 ELSIF (v_message_name= 'IGS_FI_ASSRATES_NOT_DFNED_FEE') THEN
2226 fnd_message.set_name('IGS', 'IGS_FI_FAR_FTCI_FCFL_EXIST');
2227 fnd_message.set_token('SOURCE', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FTCI'));
2228 fnd_message.set_token('DESTINATION', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FCFL'));
2229 fnd_file.put_line(fnd_file.log, fnd_message.get);
2230 END IF;
2231 p_message_name := cst_warning;
2232 l_b_ftci_fcci_clash_ind := FALSE;
2233 END IF;
2234 END IF;
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2238 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_FAR_ERROR');
2239 fnd_message.set_token('FEE_CAT',v_far_source_rec.fee_cat);
2240 fnd_message.set_token('FEE_TYPE',p_fee_type);
2241 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2242 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2243 fnd_message.set_token('RATE_NUMBER',v_far_source_rec.rate_number);
2244 fnd_file.put_line (fnd_file.log, fnd_message.get);
2245 END;
2246 END IF;
2247 END LOOP;
2248 -- 2. Check IF records rolled over
2249 IF (v_record_exists_ind = TRUE) THEN
2250 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_FEEASS_RATE_ROLLED'));
2251 END IF;
2252 IF (v_record_inserted_ind = TRUE) THEN
2253 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_FEEASS_RATES_ROLLED'));
2254 END IF;
2255 RETURN TRUE;
2256 END;
2257 END finp_ins_roll_far;
2258 --
2259 -- Routine to rollover elements ranges between cal instances
2260
2261 FUNCTION finp_ins_roll_er(
2262 p_fee_type IN IGS_FI_ELM_RANGE.fee_type%TYPE ,
2263 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2264 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2265 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2266 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2267 p_relation_type IN IGS_FI_ELM_RANGE.s_relation_type%TYPE ,
2268 p_fee_cat IN IGS_FI_ELM_RANGE.fee_cat%TYPE ,
2269 p_message_name OUT NOCOPY VARCHAR2 )
2270 RETURN BOOLEAN AS
2271 /*******************************************************************/
2272 --Change History
2273 --Who When What
2274 --sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
2275 -- error message logged.
2276 --gurprsin 28-Jun-2005 Bug# 3392088 Modified the rollover process to incorporate
2277 -- sub element ranges and rates table rollover.
2278 /*******************************************************************/
2279 gv_other_detail VARCHAR2(255);
2280 BEGIN
2281 DECLARE
2282 v_er_record_inserted_ind BOOLEAN;
2283 v_er_record_exists_ind BOOLEAN;
2284 v_err_record_inserted_ind BOOLEAN;
2285 v_err_record_exists_ind BOOLEAN;
2286 v_message_name VARCHAR2(30);
2287 v_range_number IGS_FI_ELM_RANGE.range_number%TYPE;
2288 v_logical_delete_dt IGS_FI_ELM_RANGE.logical_delete_dt%TYPE;
2289 v_er_id NUMBER;
2290 v_err_id NUMBER;
2291 l_rowid VARCHAR2(25);
2292 l_sub_er_id VARCHAR2(25);
2293 l_sub_err_id VARCHAR2(25);
2294
2295 v_ser_logical_delete_dt IGS_FI_SUB_ELM_RNG.logical_delete_date%TYPE;
2296 v_sert_logical_delete_dt IGS_FI_SUB_ER_RT.logical_delete_date%TYPE;
2297
2298 v_ser_record_exists_ind BOOLEAN;
2299 v_sert_record_exists_ind BOOLEAN;
2300 v_ser_record_inserted_ind BOOLEAN;
2301 v_sert_record_inserted_ind BOOLEAN;
2302
2303 l_c_v_sert_rec VARCHAR2(1);
2304 l_c_v_rates_exist VARCHAR2(1);
2305
2306 cst_incremental CONSTANT VARCHAR2(11) := 'INCREMENTAL';
2307
2308 v_sert_rate_number IGS_FI_SUB_ER_RT.far_id%TYPE := NULL;
2309
2310 CURSOR c_er_source IS
2311 SELECT er.range_number,
2312 er.fee_cat,
2313 er.lower_range,
2314 er.upper_range,
2315 er.s_chg_method_type,
2316 er.er_id
2317 FROM IGS_FI_ELM_RANGE er
2318 WHERE er.fee_type = p_fee_type AND
2319 er.fee_cal_type = p_source_cal_type AND
2320 er.fee_ci_sequence_number = p_source_sequence_number AND
2321 er.s_relation_type = p_relation_type AND
2322 er.logical_delete_dt is NULL AND
2323 (fee_cat = p_fee_cat OR
2324 p_fee_cat IS NULL);
2325 --Included selection of er_id in the cursor select query as a part of CPF build . Bug #3392088
2326 CURSOR c_er_dest (
2327 cp_range_number IGS_FI_ELM_RANGE.range_number%TYPE) IS
2328 SELECT er.er_id,
2329 er.range_number,
2330 er.logical_delete_dt
2331 FROM IGS_FI_ELM_RANGE er
2332 WHERE er.fee_type = p_fee_type AND
2333 er.fee_cal_type = p_dest_cal_type AND
2334 er.fee_ci_sequence_number = p_dest_sequence_number AND
2335 er.s_relation_type = p_relation_type AND
2336 er.range_number = cp_range_number;
2337 CURSOR c_err_source (
2338 cp_range_number IGS_FI_ELM_RANGE.range_number%TYPE) IS
2339 SELECT err.rate_number,
2340 err.fee_cat
2341 FROM IGS_FI_ELM_RANGE_RT err
2342 WHERE err.fee_type = p_fee_type AND
2343 err.fee_cal_type = p_source_cal_type AND
2344 err.fee_ci_sequence_number = p_source_sequence_number AND
2345 err.s_relation_type = p_relation_type AND
2346 err.range_number = cp_range_number AND
2347 err.logical_delete_dt IS NULL;
2348 CURSOR c_err_dest (
2349 cp_range_number IGS_FI_ELM_RANGE_RT.range_number%TYPE,
2350 cp_rate_number IGS_FI_ELM_RANGE_RT.rate_number%TYPE) IS
2351 SELECT err.range_number,
2352 err.logical_delete_dt
2353 FROM IGS_FI_ELM_RANGE_RT err
2354 WHERE err.fee_type = p_fee_type AND
2355 err.fee_cal_type = p_dest_cal_type AND
2356 err.fee_ci_sequence_number = p_dest_sequence_number AND
2357 err.s_relation_type = p_relation_type AND
2358 err.range_number = cp_range_number AND
2359 err.rate_number = cp_rate_number;
2360 --Added as a part of CPF build . Bug #3392088
2361 CURSOR c_ser_source (cp_er_id IGS_FI_SUB_ELM_RNG.er_id%TYPE) IS
2362 SELECT er_id,
2363 sub_er_id,
2364 sub_range_num,
2365 sub_chg_method_code,
2366 sub_lower_range,
2367 sub_upper_range
2368 FROM IGS_FI_SUB_ELM_RNG ser
2369 WHERE ser.er_id = cp_er_id AND
2370 ser.logical_delete_date IS NULL;
2371
2372 CURSOR c_ser_dest (
2373 cp_er_id IGS_FI_SUB_ELM_RNG.er_id%TYPE,
2374 cp_sub_range_num IGS_FI_SUB_ELM_RNG.sub_range_num%TYPE) IS
2375 SELECT sub_er_id,logical_delete_date
2376 FROM IGS_FI_SUB_ELM_RNG ser
2377 WHERE ser.er_id = cp_er_id AND
2378 ser.sub_range_num = cp_sub_range_num;
2379
2380
2381 CURSOR c_sert_source (
2382 cp_sub_er_id IGS_FI_SUB_ER_RT.sub_er_id%TYPE) IS
2383 SELECT sub_er_id,
2384 far_id,
2385 create_date
2386 FROM IGS_FI_SUB_ER_RT sert
2387 WHERE sert.sub_er_id = cp_sub_er_id AND
2388 sert.logical_delete_date IS NULL;
2389
2390 CURSOR c_sert_dest (
2391 cp_sub_er_id IGS_FI_SUB_ER_RT.sub_er_id%TYPE,
2392 cp_far_id IGS_FI_SUB_ER_RT.far_id%TYPE) IS
2393
2394 SELECT 'X'
2395 FROM IGS_FI_SUB_ER_RT sert
2396 WHERE sert.sub_er_id = cp_sub_er_id AND
2397 sert.far_id = cp_far_id;
2398
2399 CURSOR c_err_exists IS
2400 SELECT 'X'
2401 FROM IGS_FI_ELM_RANGE_RT err
2402 WHERE err.fee_type = p_fee_type AND
2403 err.fee_cal_type = p_source_cal_type AND
2404 err.fee_ci_sequence_number = p_source_sequence_number AND
2405 err.s_relation_type = p_relation_type AND
2406 (err.fee_cat = p_fee_cat OR p_fee_cat IS NULL) AND
2407 err.logical_delete_dt IS NULL;
2408
2409 CURSOR c_serr_exists IS
2410 SELECT 'X'
2411 FROM IGS_FI_SUB_ER_RT serr,
2412 IGS_FI_FEE_AS_RATE far
2413 WHERE far.fee_type = p_fee_type AND
2414 far.fee_cal_type = p_source_cal_type AND
2415 far.fee_ci_sequence_number = p_source_sequence_number AND
2416 far.s_relation_type = p_relation_type AND
2417 (far.fee_cat = p_fee_cat OR p_fee_cat IS NULL) AND
2418 far.far_id = serr.far_id AND
2419 serr.logical_delete_date IS NULL;
2420
2421 CURSOR c_far_exists IS
2422 SELECT 'X'
2423 FROM IGS_FI_FEE_AS_RATE far
2424 WHERE far.fee_type = p_fee_type AND
2425 far.fee_cal_type = p_dest_cal_type AND
2426 far.fee_ci_sequence_number = p_dest_sequence_number AND
2427 far.s_relation_type = p_relation_type AND
2428 (far.fee_cat = p_fee_cat OR p_fee_cat IS NULL) AND
2429 far.logical_delete_dt IS NULL;
2430
2431 CURSOR c_sert_far_id_to_rt_num(cp_far_id IGS_FI_SUB_ER_RT.far_id%TYPE) IS
2432 SELECT rate_number
2433 FROM IGS_FI_FEE_AS_RATE far
2434 WHERE far.far_id = cp_far_id;
2435
2436 BEGIN
2437 -- This function will roll all IGS_FI_ELM_RANGE and associated
2438 -- IGS_FI_ELM_RANGE_RT records underneath a
2439 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
2440 -- The assumption is being made that the "destination" IGS_CA_INST
2441 -- is open and active - it is the responsibility of the calling routine to
2442 -- check for this.
2443 -- IGS_GE_NOTE: If some of the elemets_range/IGS_FI_ELM_RANGE_RT records already
2444 -- exist then these will remain unaltered.
2445 p_message_name := Null;
2446 -- 1. Process the elements range records matching the source calendar instance
2447 v_er_record_inserted_ind := FALSE;
2448 v_er_record_exists_ind := FALSE;
2449 v_err_record_inserted_ind := FALSE;
2450 v_err_record_exists_ind := FALSE;
2451
2452 v_sert_record_inserted_ind := FALSE;
2453 v_sert_record_exists_ind := FALSE;
2454 v_ser_record_inserted_ind := FALSE;
2455 v_ser_record_exists_ind := FALSE;
2456
2457 -- Check whether fee assessment rates exist for the destination. If not then log message and return
2458 OPEN c_far_exists;
2459 FETCH c_far_exists INTO l_c_v_rates_exist;
2460
2461 OPEN c_err_exists;
2462 FETCH c_err_exists INTO l_c_v_rates_exist;
2463
2464 OPEN c_serr_exists;
2465 FETCH c_serr_exists INTO l_c_v_rates_exist;
2466
2467 IF c_far_exists%NOTFOUND AND (c_err_exists%FOUND OR c_serr_exists%FOUND) THEN
2468 fnd_message.set_name('IGS', 'IGS_FI_ER_FAR_NOSETUP');
2469 fnd_file.put_line(fnd_file.log, fnd_message.get);
2470 p_message_name := cst_warning;
2471 CLOSE c_serr_exists;
2472 CLOSE c_err_exists;
2473 CLOSE c_far_exists;
2474 RETURN TRUE;
2475 END IF;
2476
2477 CLOSE c_serr_exists;
2478 CLOSE c_err_exists;
2479 CLOSE c_far_exists;
2480
2481 FOR v_er_source_rec IN c_er_source LOOP
2482
2483 v_er_id := NULL;
2484
2485 -- Check for the existence of the IGS_FI_ELM_RANGE
2486 -- record under the destination calendar
2487 OPEN c_er_dest(v_er_source_rec.range_number);
2488 FETCH c_er_dest INTO v_er_id,
2489 v_range_number,
2490 v_logical_delete_dt;
2491 IF (c_er_dest%FOUND) THEN
2492 CLOSE c_er_dest;
2493 IF (v_logical_delete_dt IS NULL) THEN
2494 v_er_record_exists_ind := TRUE;
2495 --Added as a part of CPF build . Bug #3392088
2496 ELSE
2497 v_er_id := NULL;
2498 END IF;
2499 ELSE
2500
2501 CLOSE c_er_dest;
2502 BEGIN
2503 l_rowid :=NULL; -- initialise l_rowid to null before passing into the TBH
2504 -- l_rowid with a value will throw Un-Handled Exception
2505
2506 IGS_FI_EL_RNG_PKG.INSERT_ROW(
2507 X_ROWID=>l_rowid,
2508 X_ER_ID => v_er_id,
2509 X_FEE_TYPE=>p_fee_type,
2510 X_FEE_CAL_TYPE=>p_dest_cal_type,
2511 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
2512 X_S_RELATION_TYPE=>p_relation_type,
2513 X_RANGE_NUMBER=>v_er_source_rec.range_number,
2514 X_FEE_CAT=>v_er_source_rec.fee_cat,
2515 X_LOWER_RANGE=>v_er_source_rec.lower_range,
2516 X_UPPER_RANGE=>v_er_source_rec.upper_range,
2517 X_S_CHG_METHOD_TYPE=>v_er_source_rec.s_chg_method_type,
2518 X_LOGICAL_DELETE_DT=>NULL,
2519 X_MODE=>'R'
2520 );
2521 v_er_record_inserted_ind := TRUE;
2522 EXCEPTION
2523 WHEN OTHERS THEN
2524 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2525 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_ER_ERROR');
2526 fnd_message.set_token('FEE_CAT',v_er_source_rec.fee_cat);
2527 fnd_message.set_token('FEE_TYPE',p_fee_type);
2528 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2529 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2530 fnd_message.set_token('RANGE_NUMBER',v_er_source_rec.range_number);
2531 fnd_file.put_line (fnd_file.log, fnd_message.get);
2532 END;
2533 END IF;
2534
2535 --Added as a part of CPF build . Bug #3392088
2536 IF v_er_source_rec.s_chg_method_type = cst_incremental THEN
2537
2538 IF v_er_id IS NOT NULL THEN
2539
2540 FOR v_ser_source_rec IN c_ser_source(v_er_source_rec.er_id) LOOP
2541
2542 l_sub_er_id := NULL;
2543
2544 OPEN c_ser_dest(v_er_id,v_ser_source_rec.sub_range_num);
2545 FETCH c_ser_dest INTO l_sub_er_id,v_ser_logical_delete_dt;
2546
2547 IF(c_ser_dest%FOUND) THEN
2548 CLOSE c_ser_dest;
2549 IF (v_ser_logical_delete_dt IS NULL) THEN
2550 v_ser_record_exists_ind := TRUE;
2551 ELSE
2552 l_sub_er_id := NULL;
2553 END IF;
2554
2555 ELSE
2556 CLOSE c_ser_dest;
2557
2558 BEGIN
2559 l_rowid :=NULL; -- initialise l_rowid to null before passing into the TBH
2560 -- l_rowid with a value will throw Un-Handled Exception
2561 --insert a row into IGS_FI_SUB_ELM_RNG;
2562 IGS_FI_SUB_ELM_RNG_PKG.INSERT_ROW(
2563 x_rowid=> l_rowid,
2564 x_sub_er_id => l_sub_er_id,
2565 x_er_id => v_er_id,
2566 x_sub_range_num => v_ser_source_rec.sub_range_num,
2567 x_sub_lower_range => v_ser_source_rec.sub_lower_range,
2568 x_sub_upper_range => v_ser_source_rec.sub_upper_range,
2569 x_sub_chg_method_code => v_ser_source_rec.sub_chg_method_code,
2570 x_logical_delete_date => NULL,
2571 x_mode => 'R'
2572 );
2573 v_ser_record_inserted_ind := TRUE;
2574 EXCEPTION
2575 WHEN OTHERS THEN
2576 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2577 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_SUB_ER_ERROR');
2578 fnd_message.set_token('FEE_CAT',v_er_source_rec.fee_cat);
2579 fnd_message.set_token('FEE_TYPE',p_fee_type);
2580 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2581 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2582 fnd_message.set_token('RANGE_NUMBER',v_er_source_rec.range_number);
2583 fnd_message.set_token('SUB_RANGE_NUMBER',v_ser_source_rec.sub_range_num);
2584 fnd_file.put_line (fnd_file.log, fnd_message.get);
2585 END;
2586 END IF;
2587
2588 IF l_sub_er_id IS NOT NULL THEN
2589
2590 FOR v_sert_source_rec IN c_sert_source(v_ser_source_rec.sub_er_id) LOOP
2591
2592 OPEN c_sert_dest(l_sub_er_id, v_sert_source_rec.far_id);
2593 FETCH c_sert_dest INTO l_c_v_sert_rec;
2594 IF(c_sert_dest%FOUND) THEN
2595 CLOSE c_sert_dest;
2596 IF (v_sert_logical_delete_dt IS NULL) THEN
2597 v_sert_record_exists_ind := TRUE;
2598 END IF;
2599
2600 ELSE
2601 CLOSE c_sert_dest;
2602 BEGIN
2603 l_rowid :=NULL; -- initialise l_rowid to null before passing into the TBH
2604 -- l_rowid with a value will throw Un-Handled Exception
2605 l_sub_err_id :=NULL; -- initialise l_rowid to null before passing into the TBH
2606 -- l_rowid with a value will throw Un-Handled Exception
2607
2608 --insert the record into IGS_FI_SUB_ER_RT;
2609 IGS_FI_SUB_ER_RT_PKG.INSERT_ROW(
2610 x_rowid => l_rowid,
2611 x_sub_err_id => l_sub_err_id,
2612 x_sub_er_id => l_sub_er_id,
2613 x_far_id => v_sert_source_rec.far_id,
2614 x_create_date => v_sert_source_rec.create_date,
2615 x_logical_delete_date => NULL,
2616 x_mode => 'R'
2617 );
2618
2619 v_sert_record_inserted_ind := TRUE;
2620 EXCEPTION
2621 WHEN OTHERS THEN
2622 OPEN c_sert_far_id_to_rt_num(v_sert_source_rec.far_id);
2623 FETCH c_sert_far_id_to_rt_num INTO v_sert_rate_number;
2624 CLOSE c_sert_far_id_to_rt_num;
2625
2626 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2627 fnd_message.set_name('IGS','IGS_FI_ROLL_SUB_ER_RT_ERROR');
2628 fnd_message.set_token('FEE_TYPE',p_fee_type);
2629 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2630 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2631 fnd_message.set_token('RANGE_NUMBER',v_er_source_rec.range_number);
2632 fnd_message.set_token('SUB_RANGE_NUMBER',v_ser_source_rec.sub_range_num);
2633 fnd_message.set_token('RATE_NUMBER',v_sert_rate_number);
2634 fnd_file.put_line (fnd_file.log, fnd_message.get);
2635 END;
2636 END IF;
2637 END LOOP;
2638
2639 END IF; --End of processing Sub Range Rate Records
2640
2641 END LOOP;
2642
2643 END IF; --End of processing Sub Range Records
2644
2645 ELSE
2646 -- Process the elements range rate records
2647 -- matching the elements range
2648 FOR v_err_source_rec IN c_err_source(v_er_source_rec.range_number) LOOP
2649 -- Check for the existence of the IGS_FI_ELM_RANGE_RT record
2650 -- under the rolled over elements range
2651 OPEN c_err_dest(
2652 v_er_source_rec.range_number,
2653 v_err_source_rec.rate_number);
2654 FETCH c_err_dest INTO v_range_number,
2655 v_logical_delete_dt;
2656 IF (c_err_dest%FOUND) THEN
2657 CLOSE c_err_dest;
2658 IF (v_logical_delete_dt IS NULL) THEN
2659 v_err_record_exists_ind := TRUE;
2660 END IF;
2661 ELSE
2662 CLOSE c_err_dest;
2663 BEGIN
2664 l_rowid :=NULL; -- initialise l_rowid to null before passing into the TBH
2665 -- l_rowid with a value will throw Un-Handled Exception
2666 IGS_FI_ELM_RANGE_RT_PKG.INSERT_ROW(
2667 X_ROWID=>l_rowid,
2668 X_ERR_ID => v_err_id,
2669 X_FEE_TYPE=>p_fee_type,
2670 X_FEE_CAL_TYPE=>p_dest_cal_type,
2671 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
2672 X_S_RELATION_TYPE=>p_relation_type,
2673 X_RANGE_NUMBER=>v_er_source_rec.range_number,
2674 X_RATE_NUMBER=>v_err_source_rec.rate_number,
2675 X_CREATE_DT=>SYSDATE,
2676 X_FEE_CAT=>v_err_source_rec.fee_cat,
2677 X_LOGICAL_DELETE_DT=>NULL,
2678 X_MODE=>'R'
2679 );
2680 v_err_record_inserted_ind := TRUE;
2681 EXCEPTION
2682 WHEN OTHERS THEN
2683 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2684 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_ER_RT_ERROR');
2685 fnd_message.set_token('FEE_TYPE',p_fee_type);
2686 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2687 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2688 fnd_message.set_token('RANGE_NUMBER',v_er_source_rec.range_number);
2689 fnd_message.set_token('RATE_NUMBER',v_err_source_rec.rate_number);
2690 fnd_file.put_line (fnd_file.log, fnd_message.get);
2691 END;
2692 END IF;
2693 END LOOP;
2694 END IF;
2695 END LOOP;
2696 -- 2. Check IF records rolled over
2697 IF (v_er_record_exists_ind = TRUE) THEN
2698 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ELERNG_ROLLED'));
2699 END IF;
2700 IF (v_err_record_exists_ind = TRUE) THEN
2701 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ELERNG_ALREADY_ROLLED'));
2702 END IF;
2703 IF (v_er_record_inserted_ind = TRUE) THEN
2704 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ELEMENT_RANGE_ROLLED'));
2705 END IF;
2706 IF (v_err_record_inserted_ind = TRUE) THEN
2707 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ELERNG_RATES_ROLLED'));
2708 END IF;
2709
2710 IF (v_ser_record_exists_ind = TRUE) THEN
2711 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_SER_ALREADY_ROLLED'));
2712 END IF;
2713 IF (v_sert_record_exists_ind = TRUE) THEN
2714 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_SERT_ALREADY_ROLLED'));
2715 END IF;
2716 IF (v_ser_record_inserted_ind = TRUE) THEN
2717 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_SER_ROLLED'));
2718 END IF;
2719 IF (v_sert_record_inserted_ind = TRUE) THEN
2720 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_SERT_ROLLED'));
2721 END IF;
2722
2723 RETURN TRUE;
2724 END;
2725 END finp_ins_roll_er;
2726 --
2727 -- Routine to rollover IGS_PS_COURSE type fee triggers between cal instances
2728 FUNCTION finp_ins_roll_ctft(
2729 p_fee_cat IN IGS_PS_TYPE_FEE_TRG.fee_cat%TYPE ,
2730 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2731 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2732 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2733 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2734 p_fee_type IN IGS_PS_TYPE_FEE_TRG.fee_type%TYPE ,
2735 p_message_name OUT NOCOPY VARCHAR2 )
2736 RETURN BOOLEAN AS
2737 /*******************************************************************/
2738 --Change History
2739 --Who When What
2740 --sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
2741 -- error message logged.
2742 /*******************************************************************/
2743 gv_other_detail VARCHAR2(255);
2744 BEGIN
2745 DECLARE
2746 v_course_type IGS_PS_TYPE_FEE_TRG.course_type%TYPE;
2747 v_logical_delete_dt IGS_PS_TYPE_FEE_TRG.logical_delete_dt%TYPE;
2748 v_record_inserted_ind BOOLEAN;
2749 v_record_exists_ind BOOLEAN;
2750 v_message_name VARCHAR2(30);
2751 l_rowid VARCHAR2(25);
2752 CURSOR c_ctft_source IS
2753 SELECT ctft.course_type
2754 FROM IGS_PS_TYPE_FEE_TRG ctft
2755 WHERE ctft.fee_cat = p_fee_cat AND
2756 ctft.fee_cal_type = p_source_cal_type AND
2757 ctft.fee_ci_sequence_number = p_source_sequence_number AND
2758 ctft.fee_type = p_fee_type AND
2759 ctft.logical_delete_dt IS NULL;
2760 CURSOR c_ctft_dest (
2761 cp_course_type IGS_PS_TYPE_FEE_TRG.COURSE_TYPE%TYPE) IS
2762 SELECT ctft.COURSE_TYPE,
2763 ctft.logical_delete_dt
2764 FROM IGS_PS_TYPE_FEE_TRG ctft
2765 WHERE ctft.fee_cat = p_fee_cat AND
2766 ctft.fee_cal_type = p_dest_cal_type AND
2767 ctft.fee_ci_sequence_number = p_dest_sequence_number AND
2768 ctft.fee_type = p_fee_type AND
2769 ctft.COURSE_TYPE = cp_course_type;
2770 X_SYSDATE DATE;
2771 BEGIN
2772 -- This function will roll all IGS_PS_TYPE_FEE_TRG records underneath a
2773 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
2774 -- The assumption is being made that the "destination" IGS_CA_INST
2775 -- is open and active - it is the responsibility of the calling routine to
2776 -- check for this.
2777 -- IGS_GE_NOTE: If some of the IGS_PS_TYPE_FEE_TRG records already exist then these
2778 -- will remain unaltered.
2779 p_message_name := Null;
2780 -- 1. Process the IGS_PS_COURSE type fee trigger records matching the source
2781 -- calendar instance
2782 v_record_inserted_ind := FALSE;
2783 v_record_exists_ind := FALSE;
2784 FOR v_ctft_source_rec IN c_ctft_source LOOP
2785 -- Check for the existence of the IGS_PS_TYPE_FEE_TRG
2786 -- record under the destination calendar
2787 OPEN c_ctft_dest(v_ctft_source_rec.course_type);
2788 FETCH c_ctft_dest INTO v_course_type,
2789 v_logical_delete_dt;
2790 IF (c_ctft_dest%FOUND) THEN
2791 CLOSE c_ctft_dest;
2792 IF v_logical_delete_dt IS NULL THEN
2793 v_record_exists_ind := TRUE;
2794 END IF;
2795 ELSE
2796 CLOSE c_ctft_dest;
2797 BEGIN
2798 X_SYSDATE:=SYSDATE;
2799 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
2800 -- l_rowid with a value will throw Un-Handled Exception
2801 IGS_PS_TYPE_FEE_TRG_PKG.INSERT_ROW(
2802 X_ROWID=>l_rowid,
2803 X_FEE_CAT=>p_fee_cat,
2804 X_FEE_TYPE=>p_fee_type,
2805 X_CREATE_DT=>X_SYSDATE,
2806 X_COURSE_TYPE=>v_ctft_source_rec.course_type,
2807 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
2808 X_FEE_CAL_TYPE=>p_dest_cal_type,
2809 X_LOGICAL_DELETE_DT=>NULL,
2810 X_MODE=>'R'
2811 );
2812 v_record_inserted_ind := TRUE;
2813 EXCEPTION
2814 WHEN OTHERS THEN
2815 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2816 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_CTFT_ERROR');
2817 fnd_message.set_token('FEE_CAT',p_fee_cat);
2818 fnd_message.set_token('FEE_TYPE',p_fee_type);
2819 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2820 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2821 fnd_message.set_token('COURSE_TYPE',v_ctft_source_rec.course_type);
2822 fnd_file.put_line (fnd_file.log, fnd_message.get);
2823 END;
2824 END IF;
2825 END LOOP;
2826 -- 2. Check IF records rolled over
2827 IF (v_record_exists_ind = TRUE) THEN
2828 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRG_TYPE_FEETRG_ROLLED'));
2829 END IF;
2830 IF (v_record_inserted_ind = TRUE) THEN
2831 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRGTYPE_FEETRG_ROLLED'));
2832 END IF;
2833 RETURN TRUE;
2834 END;
2835 END finp_ins_roll_ctft;
2836 --
2837 -- Routine to rollover IGS_PS_COURSE group fee triggers between cal instances
2838 FUNCTION finp_ins_roll_cgft(
2839 p_fee_cat IN IGS_PS_GRP_FEE_TRG.fee_cat%TYPE ,
2840 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2841 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2842 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2843 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2844 p_fee_type IN IGS_PS_GRP_FEE_TRG.fee_type%TYPE ,
2845 p_message_name OUT NOCOPY VARCHAR2 )
2846 RETURN BOOLEAN AS
2847 /*******************************************************************/
2848 --Change History
2849 --Who When What
2850 --sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
2851 -- error message logged.
2852 /*******************************************************************/
2853 gv_other_detail VARCHAR2(255);
2854 BEGIN
2855 DECLARE
2856 v_course_group_cd IGS_PS_GRP_FEE_TRG.course_group_cd%TYPE;
2857 v_logical_delete_dt IGS_PS_GRP_FEE_TRG.logical_delete_dt%TYPE;
2858 v_record_inserted_ind BOOLEAN;
2859 v_record_exists_ind BOOLEAN;
2860 v_message_name VARCHAR2(30);
2861 l_rowid VARCHAR2(25);
2862 CURSOR c_cgft_source IS
2863 SELECT cgft.course_group_cd
2864 FROM IGS_PS_GRP_FEE_TRG cgft
2865 WHERE cgft.fee_cat = p_fee_cat AND
2866 cgft.fee_cal_type = p_source_cal_type AND
2867 cgft.fee_ci_sequence_number = p_source_sequence_number AND
2868 cgft.fee_type = p_fee_type AND
2869 cgft.logical_delete_dt IS NULL;
2870 CURSOR c_cgft_dest (
2871 cp_course_group_cd IGS_PS_GRP_FEE_TRG.course_group_cd%TYPE) IS
2872 SELECT cgft.course_group_cd,
2873 cgft.logical_delete_dt
2874 FROM IGS_PS_GRP_FEE_TRG cgft
2875 WHERE cgft.fee_cat = p_fee_cat AND
2876 cgft.fee_cal_type = p_dest_cal_type AND
2877 cgft.fee_ci_sequence_number = p_dest_sequence_number AND
2878 cgft.fee_type = p_fee_type AND
2879 cgft.course_group_cd = cp_course_group_cd;
2880 X_SYSDATE DATE;
2881 BEGIN
2882 -- This function will roll all IGS_PS_GRP_FEE_TRG records underneath a
2883 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
2884 -- The assumption is being made that the "destination" IGS_CA_INST
2885 -- is open and active - it is the responsibility of the calling routine to
2886 -- check for this.
2887 -- IGS_GE_NOTE: If some of the IGS_PS_GRP_FEE_TRG records already exist then
2888 -- these will remain unaltered.
2889 p_message_name := Null;
2890 -- 1. Process the IGS_PS_COURSE group fee trigger records matching the source
2891 -- calendar instance
2892 v_record_inserted_ind := FALSE;
2893 v_record_exists_ind := FALSE;
2894 FOR v_cgft_source_rec IN c_cgft_source LOOP
2895 -- Check for the existence of the IGS_PS_GRP_FEE_TRG
2896 -- record under the destination calendar
2897 OPEN c_cgft_dest(v_cgft_source_rec.course_group_cd);
2898 FETCH c_cgft_dest INTO v_course_group_cd,
2899 v_logical_delete_dt;
2900 IF (c_cgft_dest%FOUND) THEN
2901 CLOSE c_cgft_dest;
2902 IF v_logical_delete_dt IS NULL THEN
2903 v_record_exists_ind := TRUE;
2904 END IF;
2905 ELSE
2906 CLOSE c_cgft_dest;
2907 BEGIN
2908 X_SYSDATE := SYSDATE;
2909 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
2910 -- l_rowid with a value will throw Un-Handled Exception
2911 IGS_PS_GRP_FEE_TRG_PKG.INSERT_ROW(
2912 X_ROWID=>l_rowid,
2913 X_FEE_CAT=>p_fee_cat,
2914 X_CREATE_DT=>X_SYSDATE,
2915 X_COURSE_GROUP_CD=>v_cgft_source_rec.course_group_cd,
2916 X_FEE_CAL_TYPE=>p_dest_cal_type,
2917 X_FEE_TYPE=>p_fee_type,
2918 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
2919 X_LOGICAL_DELETE_DT=>NULL,
2920 X_MODE=>'R'
2921 );
2922 v_record_inserted_ind := TRUE;
2923 EXCEPTION
2924 WHEN OTHERS THEN
2925 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2926 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_CGFT_ERROR');
2927 fnd_message.set_token('FEE_CAT',p_fee_cat);
2928 fnd_message.set_token('FEE_TYPE',p_fee_type);
2929 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
2930 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
2931 fnd_message.set_token('COURSE_GROUP_CD',v_cgft_source_rec.course_group_cd);
2932 fnd_file.put_line (fnd_file.log, fnd_message.get);
2933 END;
2934 END IF;
2935 END LOOP;
2936 -- 2. Check IF records rolled over
2937 IF (v_record_exists_ind = TRUE) THEN
2938 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRG_GRP_FEETRG_ROLLED'));
2939 END IF;
2940 IF (v_record_inserted_ind = TRUE) THEN
2941 v_message_name := 'IGS_FI_PRGGRP_FEETRG_ROLLED';
2942 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRGGRP_FEETRG_ROLLED'));
2943 END IF;
2944 RETURN TRUE;
2945 END;
2946 END finp_ins_roll_cgft;
2947 --
2948 -- Routine to rollover IGS_PS_COURSE fee triggers between cal instances
2949 FUNCTION finp_ins_roll_cft(
2950 p_fee_cat IN IGS_PS_FEE_TRG.fee_cat%TYPE ,
2951 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2952 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2953 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2954 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2955 p_fee_type IN IGS_PS_FEE_TRG.fee_type%TYPE ,
2956 p_message_name OUT NOCOPY VARCHAR2 )
2957 RETURN BOOLEAN AS
2958 /*******************************************************************/
2959 --Change History
2960 --Who When What
2961 --sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
2962 -- error message logged.
2963 /*******************************************************************/
2964 gv_other_detail VARCHAR2(255);
2965 BEGIN
2966 DECLARE
2967 v_course_cd IGS_PS_FEE_TRG.course_cd%TYPE;
2968 v_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE;
2969 v_logical_delete_dt IGS_PS_FEE_TRG.logical_delete_dt%TYPE;
2970 v_record_inserted_ind BOOLEAN;
2971 v_record_exists_ind BOOLEAN;
2972 v_message_name VARCHAR2(30);
2973 l_rowid VARCHAR2(25);
2974 CURSOR c_cft_source IS
2975 SELECT cft.course_cd,
2976 cft.sequence_number,
2977 cft.version_number,
2978 cft.cal_type,
2979 cft.location_cd,
2980 cft.attendance_mode,
2981 cft.attendance_type,
2982 cft.fee_trigger_group_number
2983 FROM IGS_PS_FEE_TRG cft
2984 WHERE cft.fee_cat = p_fee_cat AND
2985 cft.fee_cal_type = p_source_cal_type AND
2986 cft.fee_ci_sequence_number = p_source_sequence_number AND
2987 cft.fee_type = p_fee_type AND
2988 cft.logical_delete_dt IS NULL;
2989 CURSOR c_cft_dest (
2990 cp_course_cd IGS_PS_FEE_TRG.course_cd%TYPE,
2991 cp_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE) IS
2992 SELECT cft.course_cd,
2993 cft.sequence_number,
2994 cft.logical_delete_dt
2995 FROM IGS_PS_FEE_TRG cft
2996 WHERE cft.fee_cat = p_fee_cat AND
2997 cft.fee_cal_type = p_dest_cal_type AND
2998 cft.fee_ci_sequence_number = p_dest_sequence_number AND
2999 cft.fee_type = p_fee_type AND
3000 cft.course_cd = cp_course_cd AND
3001 cft.sequence_number = cp_sequence_number;
3002 BEGIN
3003 -- This function will roll all IGS_PS_FEE_TRG records underneath a
3004 -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
3005 -- The assumption is being made that the "destination" IGS_CA_INST
3006 -- is open and active - it is the responsibility of the calling routine to
3007 -- check for this.
3008 -- IGS_GE_NOTE: If some of the IGS_PS_FEE_TRG records already exist then these will
3009 -- remain unaltered.
3010 p_message_name := Null;
3011 -- 1. Process the IGS_PS_COURSE fee trigger records matching the source calendar
3012 -- instance
3013 v_record_inserted_ind := FALSE;
3014 v_record_exists_ind := FALSE;
3015 FOR v_cft_source_rec IN c_cft_source LOOP
3016 -- Check for the existence of the IGS_PS_FEE_TRG
3017 -- record under the destination calendar
3018 OPEN c_cft_dest(
3019 v_cft_source_rec.course_cd,
3020 v_cft_source_rec.sequence_number);
3021 FETCH c_cft_dest INTO v_course_cd,
3022 v_sequence_number,
3023 v_logical_delete_dt;
3024 IF (c_cft_dest%FOUND) THEN
3025 CLOSE c_cft_dest;
3026 IF v_logical_delete_dt IS NULL THEN
3027 v_record_exists_ind := TRUE;
3028 END IF;
3029 ELSE
3030 CLOSE c_cft_dest;
3031 BEGIN
3032 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
3033 -- l_rowid with a value will throw Un-Handled Exception
3034 IGS_PS_FEE_TRG_PKG.INSERT_ROW(
3035 X_ROWID=>l_rowid,
3036 X_FEE_CAT=>p_fee_cat,
3037 X_FEE_TYPE=>p_fee_type,
3038 X_COURSE_CD=>v_cft_source_rec.course_cd,
3039 X_SEQUENCE_NUMBER=>v_cft_source_rec.sequence_number,
3040 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,
3041 X_FEE_CAL_TYPE=>p_dest_cal_type,
3042 X_VERSION_NUMBER=>v_cft_source_rec.version_number,
3043 X_CAL_TYPE=>v_cft_source_rec.cal_type,
3044 X_LOCATION_CD=>v_cft_source_rec.location_cd,
3045 X_ATTENDANCE_MODE=>v_cft_source_rec.attendance_mode,
3046 X_ATTENDANCE_TYPE=>v_cft_source_rec.attendance_type,
3047 X_CREATE_DT=>SYSDATE,
3048 X_FEE_TRIGGER_GROUP_NUMBER=>v_cft_source_rec.fee_trigger_group_number,
3049 X_LOGICAL_DELETE_DT=>NULL,
3050 X_MODE=>'R'
3051 );
3052 v_record_inserted_ind := TRUE;
3053 EXCEPTION
3054 WHEN OTHERS THEN
3055 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3056 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_CFT_ERROR');
3057 fnd_message.set_token('FEE_CAT',p_fee_cat);
3058 fnd_message.set_token('FEE_TYPE',p_fee_type);
3059 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
3060 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3061 fnd_message.set_token('COURSE_CD',v_cft_source_rec.course_cd);
3062 fnd_file.put_line (fnd_file.log, fnd_message.get);
3063 END;
3064 END IF;
3065 END LOOP;
3066 -- 2. Check IF records rolled over
3067 IF (v_record_exists_ind = TRUE) THEN
3068 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRG_FEETRG_ROLLED'));
3069 END IF;
3070 IF (v_record_inserted_ind = TRUE) THEN
3071 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_PRGFEE_TRG_ROLLED'));
3072 END IF;
3073 RETURN TRUE;
3074 END;
3075 END finp_ins_roll_cft;
3076
3077 FUNCTION finp_ins_roll_anc(
3078 p_fee_type IN IGS_FI_ANC_RT_SGMNTS.fee_type%TYPE ,
3079 p_source_cal_type IN IGS_FI_ANC_RT_SGMNTS.fee_cal_type%TYPE ,
3080 p_source_ci_sequence_number IN IGS_FI_ANC_RT_SGMNTS.fee_ci_sequence_number%TYPE ,
3081 p_dest_cal_type IN IGS_FI_ANC_RT_SGMNTS.fee_cal_type%TYPE ,
3082 p_dest_ci_sequence_number IN IGS_FI_ANC_RT_SGMNTS.fee_ci_sequence_number%TYPE ,
3083 p_message_name OUT NOCOPY VARCHAR2 )
3084 RETURN BOOLEAN AS
3085 /*************************************************************
3086 Created By :Nilotpal.Shee
3087 Date Created By :18-Apr-2001
3088 Purpose :To rollover Ancillary related segments and rates when the roll over for Fee Type Calendar Instances occurs.
3089 Know limitations, enhancements or remarks
3090 Change History
3091 Who When What
3092 sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
3093 error message logged.
3094 shtatiko 25-APR-2003 Enh# 2831569, Modified code so that function will log the message as soon as
3095 it enocounters error ( IGS_FI_RT_SGMNTS_DS_NT_XS and IGS_FI_ANC_RTS_DS_NT_XS ).
3096 And so function will always returns TRUE.
3097 pathipat 24-Jan-2003 Bug: 2765199 - Removed exception section
3098 vvutukur 02-Sep-2002 Bug#2531390.Used assignment operator by removing DEFAULT clause to
3099 avoid gscc warnings.
3100 vchappid 29-May-2002 This function was returning FALSE when there are no records in the Ancillary Tables.
3101 IF false is returned then the process is terminated and will not carry-on with the
3102 Fee Categories rollover.
3103
3104 (reverse chronological order - newest change first)
3105 ***************************************************************/
3106 gv_other_detail VARCHAR2(255);
3107
3108 BEGIN
3109 DECLARE
3110 v_record_inserted_ind BOOLEAN;
3111 v_sgmnts_record_exists_ind BOOLEAN;
3112 v_rates_record_exists_ind BOOLEAN;
3113 v_message_name VARCHAR2(30);
3114 v_rt_sgmnts_flag BOOLEAN := FALSE;
3115 v_rates_flag BOOLEAN := FALSE;
3116 l_rowid VARCHAR2(25);
3117 l_rowid2 VARCHAR2(25);
3118 -- cursor to check whether records are present to be rolled over in the Ancillary rate segments table
3119 CURSOR c_cur_source_sgmnts IS
3120 SELECT *
3121 FROM IGS_FI_ANC_RT_SGMNTS sfars
3122 WHERE sfars.fee_type = p_fee_type AND
3123 sfars.fee_cal_type = p_source_cal_type AND
3124 sfars.fee_ci_sequence_number = p_source_ci_sequence_number;
3125 -- cursor to check whether records are present to be rolled over in the Ancillary rates table
3126 CURSOR c_cur_source_rates IS
3127 SELECT *
3128 FROM IGS_FI_ANC_RATES sfnr
3129 WHERE sfnr.fee_type = p_fee_type AND
3130 sfnr.fee_cal_type = p_source_cal_type AND
3131 sfnr.fee_ci_sequence_number = p_source_ci_sequence_number;
3132 -- cursor to check whether records have already been rolled over in the Ancillary rate segments table
3133 CURSOR c_cur_dest_sgmnts IS
3134 SELECT *
3135 FROM IGS_FI_ANC_RT_SGMNTS dfars
3136 WHERE dfars.fee_type = p_fee_type AND
3137 dfars.fee_cal_type = p_dest_cal_type AND
3138 dfars.fee_ci_sequence_number = p_dest_ci_sequence_number;
3139 -- cursor to check whether records have already been rolled over in the Ancillary rates table
3140 CURSOR c_cur_dest_rates IS
3141 SELECT *
3142 FROM IGS_FI_ANC_RATES dfnr
3143 WHERE dfnr.fee_type = p_fee_type AND
3144 dfnr.fee_cal_type = p_dest_cal_type AND
3145 dfnr.fee_ci_sequence_number = p_dest_ci_sequence_number;
3146 v_cur_dest_sgmnts_rec c_cur_dest_sgmnts%ROWTYPE;
3147 v_cur_dest_rates_rec c_cur_dest_rates%ROWTYPE;
3148 l_ancillary_rate_id IGS_FI_ANC_RATES.ancillary_rate_id%TYPE;
3149 l_anc_rate_segment_id IGS_FI_ANC_RT_SGMNTS.anc_rate_segment_id%TYPE;
3150 l_ancillary_attributes IGS_FI_ANC_RT_SGMNTS.ancillary_attributes%TYPE;
3151
3152 BEGIN
3153 -- This function will roll over all Ancillary Segments and Ancillary Rates within a
3154 -- particular FTCI combination.(fee_type, Fee_cal_type and fee_ci_sequence_number).
3155 -- The assumption is being made as per the DLD that a successful rollover is the one when both
3156 -- Ancillary Segments and Ancillary Rates are rolled over i.e., when records are inserted in
3157 -- both the tables IGS_FI_ANC_RT_SGMNTS and IGS_FI_ANC_RATES.
3158 p_message_name := NULL;
3159 v_record_inserted_ind := FALSE;
3160 v_sgmnts_record_exists_ind := FALSE;
3161 v_rates_record_exists_ind := FALSE;
3162 OPEN c_cur_dest_sgmnts;
3163 FETCH c_cur_dest_sgmnts INTO v_cur_dest_sgmnts_rec;
3164 IF (c_cur_dest_sgmnts%FOUND) THEN
3165 -- This means that rollover has already happened, no rollover needs to happen
3166 v_sgmnts_record_exists_ind := TRUE;
3167 IF (v_sgmnts_record_exists_ind = TRUE) THEN
3168 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ANC_SGMNTS_ROLLED'));
3169 END IF;
3170 ELSE
3171 OPEN c_cur_dest_rates;
3172 FETCH c_cur_dest_rates INTO v_cur_dest_rates_rec;
3173 -- Check for the existence of records in the rates IGS_FI_ANC_RATES
3174 -- for the given FTCI (fee type, calendar type and sequence number)
3175 IF (c_cur_dest_rates%FOUND) THEN
3176 -- This means that rollover has already happened, no rollover needs to happen
3177 v_rates_record_exists_ind := TRUE;
3178 IF (v_rates_record_exists_ind = TRUE) THEN
3179 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ANC_RATES_ROLLED'));
3180 END IF;
3181 ELSE
3182 -- Insert into the table when records exist in the source cursor of rate segments
3183 FOR v_cur_source_sgmnts_rec IN c_cur_source_sgmnts LOOP
3184 v_rt_sgmnts_flag := TRUE;
3185 v_record_inserted_ind := TRUE;
3186 l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
3187 -- l_rowid with a value will throw Un-Handled Exception
3188 BEGIN
3189 IGS_FI_ANC_RT_SGMNTS_PKG.INSERT_ROW(
3190 X_ROWID=>l_rowid,
3191 X_ANC_RATE_SEGMENT_ID=>l_anc_rate_segment_id,
3192 X_FEE_TYPE=>p_fee_type,
3193 X_FEE_CAL_TYPE=>p_dest_cal_type,
3194 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_ci_sequence_number,
3195 X_ANCILLARY_ATTRIBUTES=>l_ancillary_attributes,
3196 X_ANCILLARY_SEGMENTS=>v_cur_source_sgmnts_rec.ancillary_segments,
3197 X_ENABLED_FLAG=>v_cur_source_sgmnts_rec.enabled_flag,
3198 X_MODE=>'R'
3199 );
3200 EXCEPTION
3201 WHEN OTHERS THEN
3202 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3203 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_ANC_ERROR');
3204 fnd_message.set_token('FEE_TYPE',p_fee_type);
3205 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
3206 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3207 fnd_file.put_line (fnd_file.log, fnd_message.get);
3208 END;
3209 END LOOP;
3210 -- Exit IF there is no record found in the cursor.
3211
3212 IF (v_rt_sgmnts_flag = FALSE) THEN
3213 v_message_name:='IGS_FI_RT_SGMNTS_DS_NT_XS';
3214 p_message_name:=v_message_name;
3215 fnd_file.put_line (fnd_file.LOG, fnd_message.get_string ('IGS', p_message_name));
3216 END IF;
3217 -- Insert into the table when records exist in the source cursor of rates
3218 -- and only insert when segments are defined in the ancillary segments tables
3219 IF v_rt_sgmnts_flag THEN
3220 FOR v_cur_source_rates_rec IN c_cur_source_rates LOOP
3221 v_rates_flag := TRUE;
3222 l_rowid2 := NULL; -- initialise l_rowid2 to null before passing into the TBH
3223 -- l_rowid2 with a value will throw Un-Handled Exception
3224 BEGIN
3225 IGS_FI_ANC_RATES_PKG.INSERT_ROW(
3226 X_ROWID=>l_rowid2,
3227 X_ANCILLARY_RATE_ID=>l_ancillary_rate_id,
3228 X_FEE_TYPE=>p_fee_type,
3229 X_FEE_CAL_TYPE=>p_dest_cal_type,
3230 X_FEE_CI_SEQUENCE_NUMBER=>p_dest_ci_sequence_number,
3231 X_ANCILLARY_ATTRIBUTE1=>v_cur_source_rates_rec.ancillary_attribute1,
3232 X_ANCILLARY_ATTRIBUTE2=>v_cur_source_rates_rec.ancillary_attribute2,
3233 X_ANCILLARY_ATTRIBUTE3=>v_cur_source_rates_rec.ancillary_attribute3,
3234 X_ANCILLARY_ATTRIBUTE4=>v_cur_source_rates_rec.ancillary_attribute4,
3235 X_ANCILLARY_ATTRIBUTE5=>v_cur_source_rates_rec.ancillary_attribute5,
3236 X_ANCILLARY_ATTRIBUTE6=>v_cur_source_rates_rec.ancillary_attribute6,
3237 X_ANCILLARY_ATTRIBUTE7=>v_cur_source_rates_rec.ancillary_attribute7,
3238 X_ANCILLARY_ATTRIBUTE8=>v_cur_source_rates_rec.ancillary_attribute8,
3239 X_ANCILLARY_ATTRIBUTE9=>v_cur_source_rates_rec.ancillary_attribute9,
3240 X_ANCILLARY_ATTRIBUTE10=>v_cur_source_rates_rec.ancillary_attribute10,
3241 X_ANCILLARY_ATTRIBUTE11=>v_cur_source_rates_rec.ancillary_attribute11,
3242 X_ANCILLARY_ATTRIBUTE12=>v_cur_source_rates_rec.ancillary_attribute12,
3243 X_ANCILLARY_ATTRIBUTE13=>v_cur_source_rates_rec.ancillary_attribute13,
3244 X_ANCILLARY_ATTRIBUTE14=>v_cur_source_rates_rec.ancillary_attribute14,
3245 X_ANCILLARY_ATTRIBUTE15=>v_cur_source_rates_rec.ancillary_attribute15,
3246 X_ANCILLARY_CHG_RATE=>v_cur_source_rates_rec.ancillary_chg_rate,
3247 X_ENABLED_FLAG=>v_cur_source_rates_rec.enabled_flag,
3248 X_MODE=>'R'
3249 );
3250 EXCEPTION
3251 WHEN OTHERS THEN
3252 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3253 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_ANC_RT_ERROR');
3254 fnd_message.set_token('FEE_TYPE',p_fee_type);
3255 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
3256 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3257 fnd_file.put_line (fnd_file.log, fnd_message.get);
3258 END;
3259 END LOOP;
3260 -- Exit IF there is no record found in the cursor.
3261 IF (v_rates_flag = FALSE) THEN
3262 v_message_name:='IGS_FI_ANC_RTS_DS_NT_XS';
3263 p_message_name:=v_message_name;
3264 fnd_file.put_line (fnd_file.LOG, fnd_message.get_string ('IGS', p_message_name));
3265 END IF;
3266 END IF; -- v_rt_sgmnts_flag IF close
3267 -- Close all open cursors.
3268 CLOSE c_cur_dest_rates;
3269 END IF;
3270 CLOSE c_cur_dest_sgmnts;
3271 END IF;
3272 -- Only when both the inserts are successful return the message of successful rollover
3273 IF (v_record_inserted_ind = TRUE) THEN
3274 FND_FILE.PUT_LINE (FND_FILE.LOG, Fnd_Message.Get_String('IGS','IGS_FI_ANC_ROLLS'));
3275 END IF;
3276 -- Return True when everything is successful
3277 RETURN TRUE;
3278 END;
3279 END finp_ins_roll_anc;
3280 FUNCTION finp_ins_roll_revseg(
3281 p_fee_type IN IGS_FI_F_TYPE_ACCTS_ALL.fee_type%TYPE,
3282 p_source_cal_type IN IGS_CA_INST.cal_type%TYPE,
3283 p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE,
3284 p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE,
3285 p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE,
3286 p_message_name OUT NOCOPY VARCHAR2
3287 )
3288 RETURN BOOLEAN AS
3289 /*************************************************************
3290 Created By :kkillams
3291 Date Created By :16-August-2001
3292 Purpose :To rollover revenue segments over for Fee Type Calendar Instances occurs.
3293 Know limitations, enhancements or remarks
3294 Change History
3295 Who When What
3296 sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
3297 error message logged.
3298 vchappid 29-May-2002 Bug# 2384909, Checking and Inserting the segments from the table igs_fi_f_type_accts
3299 is being done based on fee_cal_type and fee_ci_sequence_number disregarding the fee type
3300 When the same fee_cal_type and fee_ci_sequence_number in different fee types then as many
3301 records are inserted,
3302 this function was returning FALSE when the record is already exists, returning flase will terminate
3303 the process. Process should log the message and should continue with next rollover categories
3304 ***************************************************************/
3305 CURSOR cur_chk_ex_revsegs IS SELECT 1 FROM igs_fi_f_type_accts
3306 WHERE fee_type = p_fee_type AND
3307 fee_cal_type= p_dest_cal_type AND
3308 fee_ci_sequence_number= p_dest_sequence_number;
3309 CURSOR cur_roll_segs IS SELECT * FROM igs_fi_f_type_accts
3310 WHERE fee_type = p_fee_type AND
3311 fee_cal_type= p_source_cal_type AND
3312 fee_ci_sequence_number= p_source_sequence_number;
3313 l_rowid VARCHAR2(25);
3314 l_fee_type_accid igs_fi_f_type_accts.fee_type_account_id%TYPE;
3315 l_chk_exists cur_chk_ex_revsegs%ROWTYPE;
3316 l_roll_segs cur_roll_segs%ROWTYPE;
3317
3318 BEGIN
3319
3320 OPEN cur_chk_ex_revsegs;
3321 FETCH cur_chk_ex_revsegs INTO l_chk_exists;
3322 IF cur_chk_ex_revsegs%FOUND THEN
3323 p_message_name :='IGS_FI_REV_SEGS_ROLL_EXISTS';
3324 CLOSE cur_chk_ex_revsegs;
3325 ELSE
3326 OPEN cur_roll_segs;
3327 LOOP
3328 FETCH cur_roll_segs INTO l_roll_segs;
3329 EXIT WHEN cur_roll_Segs%NOTFOUND;
3330 BEGIN
3331 igs_fi_f_type_accts_pkg.insert_row (
3332 x_rowid => l_rowid,
3333 x_fee_type_account_id => l_fee_type_accid,
3334 x_fee_type => p_fee_type,
3335 x_fee_cal_type => p_dest_cal_type,
3336 x_fee_ci_sequence_number => p_dest_sequence_number,
3337 x_segment => l_roll_segs.segment,
3338 x_segment_num => l_roll_segs.segment_num,
3339 x_segment_value => l_roll_segs.segment_value,
3340 x_mode => 'R'
3341 );
3342 l_rowid := null; -- initialise l_rowid to null before passing into the TBH
3343 -- l_rowid with a value will throw Un-Handled Exception
3344 l_fee_type_accid:= null;
3345 EXCEPTION
3346 WHEN OTHERS THEN
3347 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3348 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_REVSEG_ERROR');
3349 fnd_message.set_token('FEE_TYPE',p_fee_type);
3350 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
3351 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3352 fnd_file.put_line (fnd_file.log, fnd_message.get);
3353 END;
3354 END LOOP;
3355 CLOSE cur_roll_segs;
3356 END IF;
3357 RETURN TRUE;
3358 END finp_ins_roll_revseg;
3359
3360 FUNCTION finpl_ins_roll_over_ftci_accts (
3361 p_v_fee_type IN igs_fi_f_type_accts_all.fee_type%TYPE,
3362 p_v_source_cal_type IN igs_ca_inst.cal_type%TYPE,
3363 p_n_source_sequence_number IN igs_ca_inst.sequence_number%TYPE,
3364 p_v_dest_cal_type IN igs_ca_inst.cal_type%TYPE,
3365 p_n_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE
3366 ) RETURN BOOLEAN AS
3367 /*************************************************************
3368 Created By : shtatiko
3369 Date Created By : 14-MAY-2003
3370
3371 Purpose : To Rollover Account Table Attribute Records.
3372 This Function is called only IF the System Fee Type of Source FTCI is OTHER or TUITION
3373
3374 Know limitations, enhancements or remarks
3375 Change History
3376 Who When What
3377 sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
3378 error message logged.
3379 gurprsin 30-MAY-2005 Enh# 3442712 Added 4 unit level Columns like unit_level,
3380 unit_mode,unit_class,unit_type_id to TBH call of igs_fi_ftci_accts_pkg
3381 in insert row method
3382 pathipat 26-Jun-2003 Bug:2992967 - Table validation value set for segments
3383 Removed cur_rev_account_Seg and its usage. Added call to
3384 fnd_flex_keyval.validate_segs()
3385 ***************************************************************/
3386 CURSOR cur_ftci_accts(cp_v_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
3387 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
3388 cp_n_fee_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
3389 SELECT *
3390 FROM igs_fi_ftci_accts
3391 WHERE fee_type = cp_v_fee_type
3392 AND fee_cal_type = cp_v_fee_cal_type
3393 AND fee_ci_sequence_number = cp_n_fee_ci_seq_number
3394 ORDER BY order_sequence;
3395 l_rec_ftci_accts cur_ftci_accts%ROWTYPE;
3396
3397 CURSOR cur_rev_account_cd(cp_v_rev_acct_cd igs_fi_f_typ_ca_inst.rev_account_cd%TYPE) IS
3398 SELECT closed_ind
3399 FROM igs_fi_acc
3400 WHERE account_cd = cp_v_rev_acct_cd;
3401 l_rec_rev_account_cd cur_rev_account_cd%ROWTYPE;
3402
3403 TYPE inactive_acc_tab_type IS TABLE OF igs_fi_ftci_accts.order_sequence%TYPE INDEX BY BINARY_INTEGER;
3404 l_v_natural_account_segment igs_fi_ftci_accts.natural_account_segment%TYPE := NULL;
3405 l_v_revenue_account_cd igs_fi_ftci_accts.rev_account_cd%TYPE := NULL;
3406 l_n_acct_id igs_fi_ftci_accts.acct_id%TYPE;
3407 l_b_inactive_account BOOLEAN;
3408 l_b_records_found BOOLEAN;
3409 tab_inactive_acc inactive_acc_tab_type;
3410 l_n_cntr PLS_INTEGER ;
3411 l_v_rowid VARCHAR2(25) := NULL;
3412 l_b_enabled BOOLEAN;
3413
3414 BEGIN
3415
3416
3417 -- Check IF the user has already created the accounting information in the destination fee calendar instance.
3418 OPEN cur_ftci_accts ( p_v_fee_type, p_v_dest_cal_type, p_n_dest_sequence_number );
3419 FETCH cur_ftci_accts INTO l_rec_ftci_accts;
3420 IF ( cur_ftci_accts%FOUND )THEN
3421 CLOSE cur_ftci_accts;
3422 fnd_message.set_name ( 'IGS', 'IGS_FI_FTCI_ACCTS_SETUP_EXISTS' );
3423 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
3424 RETURN TRUE;
3425 END IF;
3426 CLOSE cur_ftci_accts;
3427
3428 l_b_records_found := FALSE; -- Flag which tells whether Accounting information is defined at source FTCI
3429 l_b_inactive_account := FALSE; -- This will be made TRUE IF any of the accounting segment at source FTCI is inactive.
3430 l_n_cntr := 0; -- This holds the number of records with inactive accounting segments
3431
3432 -- Fetch the accounting information for the source calendar instance and loop across each record identified
3433 FOR l_rec_ftci_accts IN cur_ftci_accts ( p_v_fee_type, p_v_source_cal_type, p_n_source_sequence_number )
3434 LOOP
3435 l_b_records_found := TRUE;
3436 -- If Oracle General Ledger is Installed then check whether the Natural Account Segment is active or not.
3437 IF (g_v_gl_installed = 'Y') THEN
3438 l_v_natural_account_segment := l_rec_ftci_accts.natural_account_segment;
3439
3440 -- If segment_num is NULL, then do not validate, set flag as FALSE
3441 IF g_n_segment_num IS NULL THEN
3442 l_b_enabled := FALSE;
3443 ELSE
3444 l_b_enabled := fnd_flex_keyval.validate_segs( operation => 'CHECK_SEGMENTS',
3445 appl_short_name => 'SQLGL',
3446 key_flex_code => 'GL#',
3447 structure_number => igs_fi_gen_007.get_coa_id,
3448 displayable => g_n_segment_num,
3449 allow_nulls => TRUE,
3450 vrule => 'GL_ACCOUNT\nGL_ACCOUNT_TYPE\nI\nAPPL=IGS;NAME=IGS_FI_ACC_REV\nR',
3451 concat_segments => l_v_natural_account_segment
3452 );
3453 END IF;
3454 -- If there is an invalid segment then insert NULL for Natural Account Segment
3455 -- l_b_enabled will be FALSE IF the segment is incorrect/not of type Revenue
3456 IF (l_b_enabled = FALSE) THEN
3457 l_v_natural_account_segment := NULL;
3458 l_b_inactive_account := TRUE;
3459 l_n_cntr := l_n_cntr + 1;
3460 tab_inactive_acc(l_n_cntr) := l_rec_ftci_accts.order_sequence;
3461 END IF;
3462 ELSE
3463 -- If Oracle General Ledger is not Installed then check whether the Revenue Account Cd is active or not.
3464 l_v_revenue_account_cd := l_rec_ftci_accts.rev_account_cd;
3465 OPEN cur_rev_account_cd( l_rec_ftci_accts.rev_account_cd );
3466 FETCH cur_rev_account_cd INTO l_rec_rev_account_cd;
3467 IF ( cur_rev_account_cd%NOTFOUND
3468 OR l_rec_rev_account_cd.closed_ind = 'Y' ) THEN
3469 -- NULL should be inserted for Revenue Account Cd IF identified Revenue Account Cd is inactive
3470 l_v_revenue_account_cd := NULL;
3471 l_b_inactive_account := TRUE;
3472 l_n_cntr := l_n_cntr + 1;
3473 tab_inactive_acc(l_n_cntr) := l_rec_ftci_accts.order_sequence;
3474 END IF;
3475 CLOSE cur_rev_account_cd;
3476 END IF;
3477
3478 l_v_rowid := NULL;
3479 l_n_acct_id := NULL;
3480 -- Rollover Account Attribute Record
3481 --Added 4 unit based parameters.
3482 BEGIN
3483 igs_fi_ftci_accts_pkg.insert_row (
3484 x_rowid => l_v_rowid,
3485 x_acct_id => l_n_acct_id,
3486 x_fee_type => l_rec_ftci_accts.fee_type,
3487 x_fee_cal_type => p_v_dest_cal_type,
3488 x_fee_ci_sequence_number => p_n_dest_sequence_number,
3489 x_order_sequence => l_rec_ftci_accts.order_sequence,
3490 x_natural_account_segment => l_v_natural_account_segment,
3491 x_rev_account_cd => l_v_revenue_account_cd,
3492 x_location_cd => l_rec_ftci_accts.location_cd,
3493 x_attendance_type => l_rec_ftci_accts.attendance_type,
3494 x_attendance_mode => l_rec_ftci_accts.attendance_mode,
3495 x_course_cd => l_rec_ftci_accts.course_cd,
3496 x_crs_version_number => l_rec_ftci_accts.crs_version_number,
3497 x_unit_cd => l_rec_ftci_accts.unit_cd,
3498 x_unit_version_number => l_rec_ftci_accts.unit_version_number,
3499 x_org_unit_cd => l_rec_ftci_accts.org_unit_cd,
3500 x_residency_status_cd => l_rec_ftci_accts.residency_status_cd,
3501 x_uoo_id => l_rec_ftci_accts.uoo_id,
3502 x_mode => 'R',
3503 x_unit_level => l_rec_ftci_accts.unit_level,
3504 x_unit_type_id => l_rec_ftci_accts.unit_type_id,
3505 x_unit_mode => l_rec_ftci_accts.unit_mode,
3506 x_unit_class => l_rec_ftci_accts.unit_class
3507 );
3508 EXCEPTION
3509 WHEN OTHERS THEN
3510 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3511 fnd_message.set_name('IGS','IGS_FI_ROLL_FTCI_ACCTS_ERROR');
3512 fnd_message.set_token('FEE_TYPE',p_v_fee_type);
3513 fnd_message.set_token('FEE_CAL_TYPE',p_v_source_cal_type);
3514 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3515 fnd_file.put_line (fnd_file.log, fnd_message.get);
3516 END;
3517
3518 END LOOP;
3519
3520 IF ( l_b_records_found ) THEN
3521 -- If Rollover is successful then log the same.
3522 fnd_message.set_name ( 'IGS', 'IGS_FI_ROLL_FTCI_ACCTS_SUCCESS' );
3523 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
3524 ELSE
3525 -- If No Accounting Information is defined at Source FTCI.
3526 fnd_message.set_name ( 'IGS', 'IGS_FI_FTCI_ACCTS_NO_SETUP' );
3527 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
3528 END IF;
3529
3530 IF ( l_b_inactive_account ) THEN
3531 fnd_file.put_line ( fnd_file.LOG, ' ' );
3532 fnd_message.set_name ( 'IGS', 'IGS_FI_FTCI_ACCTS_INVALID' );
3533 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
3534 FOR l_n_cntr IN tab_inactive_acc.FIRST..tab_inactive_acc.LAST LOOP
3535 log_parameters ( igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'SEQUENCE'),
3536 tab_inactive_acc(l_n_cntr));
3537 END LOOP;
3538 END IF;
3539 RETURN TRUE;
3540
3541 END finpl_ins_roll_over_ftci_accts;
3542
3543
3544 FUNCTION finp_ins_roll_trg_grp( p_fee_cat IN igs_fi_fee_trg_grp.fee_cat%TYPE ,
3545 p_source_cal_type IN igs_ca_inst.cal_type%TYPE ,
3546 p_source_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
3547 p_dest_cal_type IN igs_ca_inst.cal_type%TYPE ,
3548 p_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
3549 p_fee_type IN igs_fi_fee_trg_grp.fee_type%TYPE ,
3550 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN AS
3551 /*************************************************************
3552 Created By : Priya Athipatla
3553 Date Created By : 12-Jul-2004
3554 Purpose : To rollover Fee Trigger Groups
3555 Know limitations, enhancements or remarks
3556 Change History
3557 Who When What
3558 (reverse chronological order - newest change first)
3559 sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
3560 error message logged.
3561 ***************************************************************/
3562 l_n_fee_trigger_group_number igs_fi_fee_trg_grp.fee_trigger_group_number%TYPE;
3563 l_d_logical_delete_dt igs_fi_fee_trg_grp.logical_delete_dt%TYPE;
3564 l_b_record_inserted_ind BOOLEAN := FALSE;
3565 l_b_record_exists_ind BOOLEAN := FALSE;
3566 l_v_message_name fnd_new_messages.message_name%TYPE;
3567 l_rowid ROWID := NULL;
3568
3569 -- Cursor to obtain data from Source Calendar Instance
3570 CURSOR c_trg_grp_source IS
3571 SELECT fee_trigger_group_number,
3572 description,
3573 comments
3574 FROM igs_fi_fee_trg_grp
3575 WHERE fee_cat = p_fee_cat
3576 AND fee_cal_type = p_source_cal_type
3577 AND fee_ci_sequence_number = p_source_sequence_number
3578 AND fee_type = p_fee_type
3579 AND logical_delete_dt IS NULL;
3580
3581 -- Cursor to check IF data already exists in Destination Calendar Instance
3582 CURSOR c_trg_grp_dest (cp_fee_trigger_group_number igs_fi_fee_trg_grp.fee_trigger_group_number%TYPE) IS
3583 SELECT fee_trigger_group_number,
3584 logical_delete_dt
3585 FROM igs_fi_fee_trg_grp
3586 WHERE fee_cat = p_fee_cat
3587 AND fee_cal_type = p_dest_cal_type
3588 AND fee_ci_sequence_number = p_dest_sequence_number
3589 AND fee_type = p_fee_type
3590 AND fee_trigger_group_number = cp_fee_trigger_group_number;
3591
3592
3593 BEGIN
3594
3595 -- This function will roll all Fee Trigger Group records underneath a nominated Calendar Instance to
3596 -- beneath another nominated Calendar Instance. The assumption is being made that the "destination" Calendar Instance
3597 -- is open and active - it is the responsibility of the calling routine to check for this.
3598 p_message_name := NULL;
3599
3600 -- Process the fee trigger group records matching the source calendar instance
3601 l_b_record_inserted_ind := FALSE;
3602 l_b_record_exists_ind := FALSE;
3603
3604 FOR l_trg_grp_source_rec IN c_trg_grp_source LOOP
3605 -- Check for the existence of the Fee Trigger Group record under the destination calendar
3606 OPEN c_trg_grp_dest(l_trg_grp_source_rec.fee_trigger_group_number);
3607 FETCH c_trg_grp_dest INTO l_n_fee_trigger_group_number, l_d_logical_delete_dt;
3608 IF (c_trg_grp_dest%FOUND) THEN
3609 CLOSE c_trg_grp_dest;
3610 IF l_d_logical_delete_dt IS NULL THEN
3611 l_b_record_exists_ind := TRUE;
3612 END IF;
3613 ELSE
3614 CLOSE c_trg_grp_dest;
3615 l_rowid := NULL;
3616 BEGIN
3617 igs_fi_fee_trg_grp_pkg.insert_row( x_rowid => l_rowid,
3618 x_fee_cat => p_fee_cat,
3619 x_fee_trigger_group_number => l_trg_grp_source_rec.fee_trigger_group_number,
3620 x_fee_cal_type => p_dest_cal_type,
3621 x_fee_ci_sequence_number => p_dest_sequence_number,
3622 x_fee_type => p_fee_type,
3623 x_description => l_trg_grp_source_rec.description,
3624 x_logical_delete_dt => NULL,
3625 x_comments => l_trg_grp_source_rec.comments,
3626 x_mode => 'R');
3627 l_b_record_inserted_ind := TRUE;
3628 EXCEPTION
3629 WHEN OTHERS THEN
3630 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3631 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_TRG_GRP_ERROR');
3632 fnd_message.set_token('FEE_CAT',p_fee_cat);
3633 fnd_message.set_token('FEE_TYPE',p_fee_type);
3634 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
3635 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3636 fnd_message.set_token('FEE_TRIGGER_GROUP_NUMBER',l_trg_grp_source_rec.fee_trigger_group_number);
3637 fnd_file.put_line (fnd_file.log, fnd_message.get);
3638 END;
3639 END IF;
3640 END LOOP;
3641
3642 IF (l_b_record_exists_ind = TRUE) THEN
3643 -- Display message that the Fee Trigger Group record already exist
3644 fnd_message.set_name('IGS','IGS_FI_TRG_GRP_EXISTS');
3645 fnd_file.put_line (fnd_file.log, fnd_message.get);
3646 END IF;
3647 IF (l_b_record_inserted_ind = TRUE) THEN
3648 -- Display message that the Fee Trigger Group record have been successfully rolled over
3649 fnd_message.set_name('IGS','IGS_FI_TRG_GRP_ROLLED');
3650 fnd_file.put_line (fnd_file.log, fnd_message.get);
3651 END IF;
3652 RETURN TRUE;
3653
3654 END finp_ins_roll_trg_grp;
3655
3656
3657 FUNCTION finp_ins_roll_uft( p_fee_cat IN igs_fi_unit_fee_trg.fee_cat%TYPE ,
3658 p_source_cal_type IN igs_ca_inst.cal_type%TYPE ,
3659 p_source_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
3660 p_dest_cal_type IN igs_ca_inst.cal_type%TYPE ,
3661 p_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
3662 p_fee_type IN igs_fi_unit_fee_trg.fee_type%TYPE ,
3663 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN AS
3664 /*************************************************************
3665 Created By : Priya Athipatla
3666 Date Created By : 12-Jul-2004
3667 Purpose : To rollover Unit Fee Triggers
3668 Know limitations, enhancements or remarks
3669 Change History
3670 Who When What
3671 akandreg 02-Dec-2005 Bug 4747757. Added cursor cur_chk_version to handle the
3672 issue of rolling over a unit fee trigger when version is
3673 not specified at FCFL level.
3674 svuppala 11-March-2004 Bug 4224379 - New cursor 'c_alt_cd' is created to get "Alternate code" from
3675 igs_ca_inst_all and to send as a token in IGS_FI_ROLLOVER_UFT_ERROR.
3676 Added an EXCEPTION to log a message in case of rolling over failure.
3677 (reverse chronological order - newest change first)
3678 ***************************************************************/
3679 l_v_unit_cd igs_fi_unit_fee_trg.unit_cd%TYPE;
3680 l_v_sequence_number igs_fi_unit_fee_trg.sequence_number%TYPE;
3681 l_d_logical_delete_dt igs_fi_unit_fee_trg.logical_delete_dt%TYPE;
3682 l_b_record_inserted_ind BOOLEAN := FALSE;
3683 l_b_record_exists_ind BOOLEAN := FALSE;
3684 l_v_message_name fnd_new_messages.message_name%TYPE;
3685 l_rowid ROWID := NULL;
3686 l_v_alt_code igs_ca_inst_all.alternate_code%TYPE;
3687
3688 -- Cursor to obtain data from Source Calendar Instance
3689 CURSOR c_uft_source IS
3690 SELECT uft.unit_cd,
3691 uft.sequence_number,
3692 uft.version_number,
3693 uft.cal_type,
3694 uft.ci_sequence_number,
3695 uft.location_cd,
3696 uft.unit_class,
3697 uft.create_dt,
3698 uft.fee_trigger_group_number
3699
3700 FROM igs_fi_unit_fee_trg uft
3701 WHERE uft.fee_cat = p_fee_cat
3702 AND uft.fee_cal_type = p_source_cal_type
3703 AND uft.fee_ci_sequence_number = p_source_sequence_number
3704 AND uft.fee_type = p_fee_type
3705 AND uft.logical_delete_dt IS NULL;
3706
3707
3708 CURSOR c_alt_cd (cp_cal_type igs_fi_unit_fee_trg.cal_type%TYPE,
3709 cp_ci_sequence_number igs_fi_unit_fee_trg.ci_sequence_number%TYPE) IS
3710 SELECT cinst.alternate_code
3711 FROM igs_ca_inst_all cinst
3712 WHERE cinst.cal_type = cp_cal_type
3713 AND cinst.sequence_number = cp_ci_sequence_number;
3714
3715 -- Cursor to check IF data already exists in Destination Calendar Instance
3716 CURSOR c_uft_dest (cp_unit_cd igs_fi_unit_fee_trg.unit_cd%TYPE,
3717 cp_sequence_number igs_fi_unit_fee_trg.sequence_number%TYPE) IS
3718 SELECT uft.unit_cd,
3719 uft.sequence_number,
3720 uft.logical_delete_dt
3721 FROM igs_fi_unit_fee_trg uft
3722 WHERE uft.fee_cat = p_fee_cat
3723 AND uft.fee_cal_type = p_dest_cal_type
3724 AND uft.fee_ci_sequence_number = p_dest_sequence_number
3725 AND uft.fee_type = p_fee_type
3726 AND uft.unit_cd = cp_unit_cd
3727 AND uft.sequence_number = cp_sequence_number;
3728
3729 CURSOR cur_chk_version(cp_v_unit_cd igs_fi_unit_fee_trg.unit_cd%TYPE,cp_v_unit_version_number igs_fi_unit_fee_trg.version_number%TYPE) IS
3730 SELECT 'x'
3731 FROM igs_ps_unit_ver psv,
3732 igs_ps_unit_stat stat
3733 WHERE psv.unit_cd = cp_v_unit_cd
3734 AND (psv.version_number = cp_v_unit_version_number OR cp_v_unit_version_number IS NULL)
3735 AND psv.unit_status = stat.unit_status
3736 AND stat.s_unit_status IN ('ACTIVE','PLANNED');
3737
3738 l_v_chk_version VARCHAR2(1);
3739
3740 BEGIN
3741
3742 -- This function will roll all Unit Fee Trigger records underneath a nominated Calendar Instance to
3743 -- beneath another nominated Calendar Instance. The assumption is being made that the "destination" Calendar Instance
3744 -- is open and active - it is the responsibility of the calling routine to check for this.
3745 p_message_name := NULL;
3746 l_v_alt_code := NULL;
3747 -- Process the Unit fee trigger records matching the source calendar instance
3748 l_b_record_inserted_ind := FALSE;
3749 l_b_record_exists_ind := FALSE;
3750
3751 FOR l_uft_source_rec IN c_uft_source LOOP
3752 -- Check for the existence of the Unit Fee Trigger record under the destination calendar
3753 OPEN c_uft_dest(l_uft_source_rec.unit_cd,l_uft_source_rec.sequence_number);
3754 FETCH c_uft_dest INTO l_v_unit_cd, l_v_sequence_number, l_d_logical_delete_dt;
3755 IF (c_uft_dest%FOUND) THEN
3756 CLOSE c_uft_dest;
3757 IF l_d_logical_delete_dt IS NULL THEN
3758 l_b_record_exists_ind := TRUE;
3759 END IF;
3760 ELSE
3761 CLOSE c_uft_dest;
3762 l_rowid := NULL;
3763 BEGIN
3764 OPEN cur_chk_version(l_uft_source_rec.unit_cd,l_uft_source_rec.version_number);
3765 FETCH cur_chk_version INTO l_v_chk_version;
3766 IF cur_chk_version%NOTFOUND THEN
3767 CLOSE cur_chk_version;
3768 fnd_message.set_name('IGS','IGS_PS_UNITVER_ST_ACTIVEPLANN');
3769 igs_ge_msg_stack.add;
3770 app_exception.raise_exception;
3771 END IF;
3772 CLOSE cur_chk_version;
3773
3774 igs_fi_unit_fee_trg_pkg.insert_row( x_rowid => l_rowid,
3775 x_fee_cat => p_fee_cat,
3776 x_fee_cal_type => p_dest_cal_type,
3777 x_fee_ci_sequence_number => p_dest_sequence_number,
3778 x_unit_cd => l_uft_source_rec.unit_cd,
3779 x_sequence_number => l_uft_source_rec.sequence_number,
3780 x_fee_type => p_fee_type,
3781 x_version_number => l_uft_source_rec.version_number,
3782 x_cal_type => l_uft_source_rec.cal_type,
3783 x_ci_sequence_number => l_uft_source_rec.ci_sequence_number,
3784 x_location_cd => l_uft_source_rec.location_cd,
3785 x_unit_class => l_uft_source_rec.unit_class,
3786 x_create_dt => SYSDATE,
3787 x_fee_trigger_group_number => l_uft_source_rec.fee_trigger_group_number,
3788 x_logical_delete_dt => NULL,
3789 x_mode => 'R');
3790
3791 l_b_record_inserted_ind := TRUE;
3792
3793 EXCEPTION
3794 WHEN OTHERS THEN
3795 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3796 IF ((l_uft_source_rec.cal_type IS NOT NULL) AND (l_uft_source_rec.ci_sequence_number IS NOT NULL)) THEN
3797 OPEN c_alt_cd(l_uft_source_rec.cal_type,l_uft_source_rec.ci_sequence_number);
3798 FETCH c_alt_cd INTO l_v_alt_code;
3799 CLOSE c_alt_cd;
3800 END IF;
3801
3802 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_UFT_ERROR');
3803 fnd_message.set_token('UNIT_CD',l_uft_source_rec.unit_cd);
3804 IF (l_uft_source_rec.version_number IS NULL) THEN
3805 fnd_message.set_token('VER_NUM',IGS_FI_GEN_GL.GET_LKP_MEANING('IGS_FI_LOCKBOX','NULL_VALUE'));
3806 ELSE
3807 fnd_message.set_token('VER_NUM',l_uft_source_rec.version_number);
3808 END IF;
3809 fnd_message.set_token('CAL_TYPE',NVL(l_uft_source_rec.cal_type,IGS_FI_GEN_GL.GET_LKP_MEANING('IGS_FI_LOCKBOX','NULL_VALUE')));
3810 fnd_message.set_token('ALT_CODE',NVL(l_v_alt_code,IGS_FI_GEN_GL.GET_LKP_MEANING('IGS_FI_LOCKBOX','NULL_VALUE')));
3811 fnd_message.set_token('LOC_CD', NVL(l_uft_source_rec.location_cd,IGS_FI_GEN_GL.GET_LKP_MEANING('IGS_FI_LOCKBOX','NULL_VALUE')));
3812 fnd_message.set_token('UNIT_CLASS', NVL(l_uft_source_rec.unit_class,IGS_FI_GEN_GL.GET_LKP_MEANING('IGS_FI_LOCKBOX','NULL_VALUE')));
3813 fnd_message.set_token('CR_DATE',l_uft_source_rec.create_dt);
3814 fnd_file.put_line (fnd_file.log, fnd_message.get);
3815
3816 END;
3817
3818 END IF;
3819 END LOOP;
3820
3821 IF (l_b_record_exists_ind = TRUE) THEN
3822 -- Display message that the Unit Triggers already exist
3823 fnd_message.set_name('IGS','IGS_FI_UNIT_TRG_EXISTS');
3824 fnd_file.put_line (fnd_file.log, fnd_message.get);
3825 END IF;
3826 IF (l_b_record_inserted_ind = TRUE) THEN
3827 -- Display message that the Unit Fee Triggers have been successfully rolled over
3828 fnd_message.set_name('IGS','IGS_FI_UNIT_TRG_ROLLED');
3829 fnd_file.put_line (fnd_file.log, fnd_message.get);
3830 END IF;
3831
3832 RETURN TRUE;
3833
3834 END finp_ins_roll_uft;
3835
3836
3837 FUNCTION finp_ins_roll_usft( p_fee_cat IN igs_en_unitsetfeetrg.fee_cat%TYPE ,
3838 p_source_cal_type IN igs_ca_inst.cal_type%TYPE ,
3839 p_source_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
3840 p_dest_cal_type IN igs_ca_inst.cal_type%TYPE ,
3841 p_dest_sequence_number IN igs_ca_inst.sequence_number%TYPE ,
3842 p_fee_type IN igs_en_unitsetfeetrg.fee_type%TYPE ,
3843 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN AS
3844 /*************************************************************
3845 Created By : Priya Athipatla
3846 Date Created By : 12-Jul-2004
3847 Purpose : To rollover Unit Set Fee Triggers
3848 Know limitations, enhancements or remarks
3849 Change History
3850 Who When What
3851 (reverse chronological order - newest change first)
3852 sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
3853 error message logged.
3854 ***************************************************************/
3855 l_v_unit_set_cd igs_en_unitsetfeetrg.unit_set_cd%TYPE;
3856 l_v_version_number igs_en_unitsetfeetrg.version_number%TYPE;
3857 l_d_logical_delete_dt igs_en_unitsetfeetrg.logical_delete_dt%TYPE;
3858 l_b_record_inserted_ind BOOLEAN := FALSE;
3859 l_b_record_exists_ind BOOLEAN := FALSE;
3860 l_v_message_name fnd_new_messages.message_name%TYPE;
3861 l_rowid ROWID := NULL;
3862 l_d_create_dt igs_en_unitsetfeetrg.create_dt%TYPE;
3863
3864 -- Cursor to obtain data from Source Calendar Instance
3865 CURSOR c_usft_source IS
3866 SELECT uft.unit_set_cd,
3867 uft.version_number,
3868 uft.fee_trigger_group_number
3869 FROM igs_en_unitsetfeetrg uft
3870 WHERE uft.fee_cat = p_fee_cat
3871 AND uft.fee_cal_type = p_source_cal_type
3872 AND uft.fee_ci_sequence_number = p_source_sequence_number
3873 AND uft.fee_type = p_fee_type
3874 AND uft.logical_delete_dt IS NULL;
3875
3876 -- Cursor to check IF data already exists in Destination Calendar Instance
3877 CURSOR c_usft_dest (cp_unit_set_cd igs_en_unitsetfeetrg.unit_set_cd%TYPE,
3878 cp_version_number igs_en_unitsetfeetrg.version_number%TYPE) IS
3879 SELECT uft.unit_set_cd,
3880 uft.version_number,
3881 uft.logical_delete_dt
3882 FROM igs_en_unitsetfeetrg uft
3883 WHERE uft.fee_cat = p_fee_cat
3884 AND uft.fee_cal_type = p_dest_cal_type
3885 AND uft.fee_ci_sequence_number = p_dest_sequence_number
3886 AND uft.fee_type = p_fee_type
3887 AND uft.unit_set_cd = cp_unit_set_cd
3888 AND uft.version_number = cp_version_number;
3889
3890 BEGIN
3891
3892 -- This function will roll all Unit Set Fee Trigger records underneath a nominated Calendar Instance to
3893 -- beneath another nominated Calendar Instance. The assumption is being made that the "destination" Calendar Instance
3894 -- is open and active - it is the responsibility of the calling routine to check for this.
3895
3896 p_message_name := NULL;
3897
3898 -- Process the Unit fee trigger records matching the source calendar instance
3899 l_b_record_inserted_ind := FALSE;
3900 l_b_record_exists_ind := FALSE;
3901
3902 FOR l_usft_source_rec IN c_usft_source LOOP
3903 -- Check for the existence of the Unit Set Fee Trigger record under the destination calendar
3904 OPEN c_usft_dest(l_usft_source_rec.unit_set_cd,l_usft_source_rec.version_number);
3905 FETCH c_usft_dest INTO l_v_unit_set_cd, l_v_version_number, l_d_logical_delete_dt;
3906 IF (c_usft_dest%FOUND) THEN
3907 CLOSE c_usft_dest;
3908 IF l_d_logical_delete_dt IS NULL THEN
3909 l_b_record_exists_ind := TRUE;
3910 END IF;
3911 ELSE
3912 CLOSE c_usft_dest;
3913 l_rowid := NULL;
3914 l_d_create_dt := SYSDATE;
3915 BEGIN
3916 igs_en_unitsetfeetrg_pkg.insert_row( x_rowid => l_rowid,
3917 x_fee_cat => p_fee_cat,
3918 x_fee_cal_type => p_dest_cal_type,
3919 x_fee_ci_sequence_number => p_dest_sequence_number,
3920 x_fee_type => p_fee_type,
3921 x_unit_set_cd => l_usft_source_rec.unit_set_cd,
3922 x_version_number => l_usft_source_rec.version_number,
3923 x_create_dt => l_d_create_dt,
3924 x_fee_trigger_group_number => l_usft_source_rec.fee_trigger_group_number,
3925 x_logical_delete_dt => NULL,
3926 x_mode => 'R');
3927 l_b_record_inserted_ind := TRUE;
3928 EXCEPTION
3929 WHEN OTHERS THEN
3930 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3931 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_USFT_ERROR');
3932 fnd_message.set_token('FEE_CAT',p_fee_cat);
3933 fnd_message.set_token('FEE_TYPE',p_fee_type);
3934 fnd_message.set_token('FEE_CAL_TYPE',p_source_cal_type);
3935 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
3936 fnd_message.set_token('UNIT_SET_CD',l_usft_source_rec.unit_set_cd);
3937 fnd_message.set_token('VERSION_NUMBER',l_usft_source_rec.version_number);
3938 fnd_file.put_line (fnd_file.log, fnd_message.get);
3939 END;
3940 END IF;
3941 END LOOP;
3942
3943 IF (l_b_record_exists_ind = TRUE) THEN
3944 -- Display message that the Unit Set Triggers already exist
3945 fnd_message.set_name('IGS','IGS_FI_USET_TRG_EXISTS');
3946 fnd_file.put_line (fnd_file.log, fnd_message.get);
3947 END IF;
3948 IF (l_b_record_inserted_ind = TRUE) THEN
3949 -- Display message that the Unit Set Fee Triggers have been successfully rolled over
3950 fnd_message.set_name('IGS','IGS_FI_USET_TRG_ROLLED');
3951 fnd_file.put_line (fnd_file.log, fnd_message.get);
3952 END IF;
3953 RETURN TRUE;
3954
3955 END finp_ins_roll_usft;
3956
3957 PROCEDURE finp_ins_roll_tprs(p_v_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
3958 p_v_source_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
3959 p_n_source_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
3960 p_v_dest_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
3961 p_n_dest_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
3962 p_b_status OUT NOCOPY BOOLEAN,
3963 p_v_message_name OUT NOCOPY VARCHAR2) AS
3964 /****************************************************************
3965 || Created By : [email protected]
3966 || Created On : 13-SEP-2004
3967 || Purpose : This procedure will rollover the FTCI+TP retention schedules
3968 || Known limitations, enhancements or remarks :
3969 || Change History :
3970 || Who When What
3971 || (reverse chronological order - newest change first)
3972 || sapanigr 14-Jun-2006 Bug 5148913. Unhandled exceptions at insert row caught and appropriate
3973 || error message logged.
3974 || sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_tp_ret_schd
3975 || is now rounded off to currency precision
3976 *****************************************************************/
3977
3978 -- Cursor for identifying Distinct Teaching Periods
3979 CURSOR cur_dist_tp(cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
3980 cp_fee_ci_seq_num igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
3981 cp_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE) IS
3982 SELECT DISTINCT teach_cal_type,
3983 teach_ci_sequence_number
3984 FROM igs_fi_tp_ret_schd
3985 WHERE fee_cal_type = cp_fee_cal_type
3986 AND fee_ci_sequence_number = cp_fee_ci_seq_num
3987 AND fee_type = cp_fee_type
3988 AND (teach_cal_type IS NOT NULL AND teach_ci_sequence_number IS NOT NULL);
3989
3990 -- Cursor for identifying the Rolled Over Instance of Teaching Period
3991 CURSOR cur_roll_tp(cp_cal_type igs_ca_inst.cal_type%TYPE,
3992 cp_seq_number igs_ca_inst.sequence_number%TYPE) IS
3993 SELECT cal_type,
3994 sequence_number
3995 FROM igs_ca_inst
3996 WHERE cal_type = cp_cal_type
3997 AND prior_ci_sequence_number = cp_seq_number;
3998
3999 -- Cursor for identifying the Retention Schedules
4000 CURSOR cur_ret_schdl(cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
4001 cp_fee_ci_seq_num igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
4002 cp_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
4003 cp_teach_cal_type igs_ca_inst.cal_type%TYPE,
4004 cp_teach_seq_num igs_ca_inst.sequence_number%TYPE) IS
4005 SELECT *
4006 FROM igs_fi_tp_ret_schd
4007 WHERE fee_cal_type = cp_fee_cal_type
4008 AND fee_ci_sequence_number = cp_fee_ci_seq_num
4009 AND fee_type = cp_fee_type
4010 AND teach_cal_type = cp_teach_cal_type
4011 AND teach_ci_sequence_number = cp_teach_seq_num;
4012
4013 -- Cursor for validating IF the Load calendar Instance has the Rolled Over
4014 -- Teaching period associated
4015 CURSOR cur_load_teach(cp_load_cal_type igs_ca_inst.cal_type%TYPE,
4016 cp_load_seq_number igs_ca_inst.sequence_number%TYPE,
4017 cp_teach_cal_type igs_ca_inst.cal_type%TYPE,
4018 cp_teach_seq_number igs_ca_inst.sequence_number%TYPE) IS
4019 SELECT 'x'
4020 FROM igs_ca_load_to_teach_v
4021 WHERE load_cal_type = cp_load_cal_type
4022 AND load_ci_sequence_number = cp_load_seq_number
4023 AND teach_cal_type = cp_teach_cal_type
4024 AND teach_ci_sequence_number = cp_teach_seq_number;
4025
4026 -- Cursor for validating IF the Date Alias Instance exists for the Rolled Over
4027 -- Teaching Period.
4028 CURSOR cur_dai_exist(cp_dt_alias igs_ca_da_inst.dt_alias%TYPE,
4029 cp_dai_seq_num igs_ca_da_inst.sequence_number%TYPE,
4030 cp_teach_cal_type igs_ca_da_inst.cal_type%TYPE,
4031 cp_teach_seq_num igs_ca_da_inst.ci_sequence_number%TYPE) IS
4032 SELECT dt_alias,
4033 sequence_number
4034 FROM igs_ca_da_inst
4035 WHERE cal_type = cp_teach_cal_type
4036 AND ci_sequence_number = cp_teach_seq_num
4037 AND dt_alias = cp_dt_alias
4038 AND sequence_number = cp_dai_seq_num;
4039
4040 l_rec_roll_tp cur_roll_tp%ROWTYPE;
4041 l_var VARCHAR2(1);
4042
4043 l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
4044 l_n_load_seq_num igs_ca_inst.sequence_number%TYPE;
4045 l_b_ret_val BOOLEAN;
4046 l_v_message_name fnd_new_messages.message_name%TYPE;
4047 l_b_rec_found BOOLEAN;
4048 l_n_ret_id igs_fi_tp_ret_schd.ftci_teach_retention_id%TYPE;
4049 l_v_rowid VARCHAR2(25);
4050 l_b_prc_rollover BOOLEAN;
4051 l_rec_dai cur_dai_exist%ROWTYPE;
4052
4053 l_rec_ret_schdl cur_ret_schdl%ROWTYPE;
4054
4055 l_v_fp_alt_cd igs_ca_inst.alternate_code%TYPE;
4056 l_v_dest_teach_alt_cd igs_ca_inst.alternate_code%TYPE;
4057 l_v_teach_alt_cd igs_ca_inst.alternate_code%TYPE;
4058 l_v_teach_label igs_lookup_values.meaning%TYPE;
4059
4060 BEGIN
4061
4062 -- Establish Savepoint for the main procedure
4063 SAVEPOINT SP_ROLLTP_MAIN;
4064
4065 p_b_status := TRUE;
4066 p_v_message_name := NULL;
4067
4068 l_b_rec_found := FALSE;
4069
4070 -- Get the Load Calendar Instance for the Destination Fee Period passed as Input
4071 l_b_ret_val := igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => p_v_dest_cal_type,
4072 p_ci_sequence_number => p_n_dest_ci_seq_number,
4073 p_cal_category => 'FEE',
4074 p_ret_cal_type => l_v_load_cal_type,
4075 p_ret_ci_sequence_number => l_n_load_seq_num,
4076 p_message_name => l_v_message_name);
4077
4078 -- If the function does not return True, the return the procedure by setting the status
4079 -- to false and the message appropriately
4080 IF NOT l_b_ret_val THEN
4081 IF l_v_message_name IS NOT NULL THEN
4082 p_b_status := FALSE;
4083 p_v_message_name := l_v_message_name;
4084 RETURN;
4085 END IF;
4086 END IF;
4087
4088 -- Get the label value for the Teaching Period
4089 l_v_teach_label := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
4090 'TEACH_CAL_ALT_CD');
4091
4092 -- Get the Alternate Code for the Destination Fee period
4093 l_v_fp_alt_cd := igs_ca_gen_001.calp_get_alt_cd(p_cal_type => p_v_dest_cal_type,
4094 p_sequence_number => p_n_dest_ci_seq_number);
4095
4096 -- Loop across all the distinct Teaching period for the Source FTCI.
4097 -- These teaching periods are selected from the Retention Schedules table IGS_FI_TP_RET_SCHD
4098 FOR rec_dist_tp IN cur_dist_tp(p_v_source_cal_type,
4099 p_n_source_ci_seq_number,
4100 p_v_fee_type) LOOP
4101 BEGIN
4102
4103 -- Establish Savepoint
4104 SAVEPOINT SP_ROLL_TP;
4105 l_b_rec_found := TRUE;
4106 l_b_prc_rollover := TRUE;
4107
4108 -- Get the alternate Code for the Teaching Period
4109 l_v_teach_alt_cd := igs_ca_gen_001.calp_get_alt_cd(p_cal_type => rec_dist_tp.teach_cal_type,
4110 p_sequence_number => rec_dist_tp.teach_ci_sequence_number);
4111
4112 -- Log the Teaching Period details in the log file
4113 fnd_file.new_line(fnd_file.log);
4114 fnd_file.put_line(fnd_file.log,
4115 l_v_teach_label||':'||l_v_teach_alt_cd);
4116
4117 -- Validation for the Rolled Over Instance of the Teaching Period.
4118 OPEN cur_roll_tp(rec_dist_tp.teach_cal_type,
4119 rec_dist_tp.teach_ci_sequence_number);
4120 FETCH cur_roll_tp INTO l_rec_roll_tp;
4121 IF cur_roll_tp%NOTFOUND THEN
4122 l_b_prc_rollover := FALSE;
4123 fnd_message.set_name('IGS',
4124 'IGS_FI_TPRS_NO_TP_ROLL');
4125 fnd_message.set_token('ALT_CD',
4126 l_v_teach_alt_cd);
4127 fnd_file.put_line(fnd_file.log,
4128 fnd_message.get);
4129 END IF;
4130 CLOSE cur_roll_tp;
4131
4132 IF l_b_prc_rollover THEN
4133
4134 -- Validation for the Teaching Period retention schedule already been rolled over
4135 OPEN cur_ret_schdl(p_v_dest_cal_type,
4136 p_n_dest_ci_seq_number,
4137 p_v_fee_type,
4138 l_rec_roll_tp.cal_type,
4139 l_rec_roll_tp.sequence_number);
4140 FETCH cur_ret_schdl INTO l_rec_ret_schdl;
4141 IF cur_ret_schdl%FOUND THEN
4142 l_b_prc_rollover := FALSE;
4143 fnd_message.set_name('IGS',
4144 'IGS_FI_TP_RET_SCHD_ROLLED');
4145 fnd_file.put_line(fnd_file.log,
4146 fnd_message.get);
4147 END IF;
4148 CLOSE cur_ret_schdl;
4149 END IF;
4150
4151 IF l_b_prc_rollover THEN
4152
4153 -- Validation for the Rolled Over teaching Period associated to the Load Period of
4154 -- the Destination Fee Period
4155 OPEN cur_load_teach(l_v_load_cal_type,
4156 l_n_load_seq_num,
4157 l_rec_roll_tp.cal_type,
4158 l_rec_roll_tp.sequence_number);
4159 FETCH cur_load_teach INTO l_var;
4160 IF cur_load_teach%NOTFOUND THEN
4161 l_b_prc_rollover := FALSE;
4162 l_v_dest_teach_alt_cd := igs_ca_gen_001.calp_get_alt_cd(l_rec_roll_tp.cal_type,
4163 l_rec_roll_tp.sequence_number);
4164 fnd_message.set_name('IGS',
4165 'IGS_FI_TPRS_NO_TP_FTCI_REL');
4166 fnd_message.set_token('ALT_CD',
4167 l_v_teach_alt_cd);
4168 fnd_message.set_token('TEACH_ALT_CD',
4169 l_v_dest_teach_alt_cd);
4170 fnd_message.set_token('FEE_ALT_CD',
4171 l_v_fp_alt_cd);
4172 fnd_file.put_line(fnd_file.log,
4173 fnd_message.get);
4174 END IF;
4175 CLOSE cur_load_teach;
4176 END IF;
4177
4178 IF l_b_prc_rollover THEN
4179
4180 -- Loop across the Retention Schedules defined for the Source FTCI and the
4181 -- teaching period in context
4182
4183 FOR rec_ret_schd IN cur_ret_schdl(p_v_source_cal_type,
4184 p_n_source_ci_seq_number,
4185 p_v_fee_type,
4186 rec_dist_tp.teach_cal_type,
4187 rec_dist_tp.teach_ci_sequence_number) LOOP
4188
4189 -- Check IF the Rolled Over Date Alias exists with the Rolled Over Teaching Period
4190 OPEN cur_dai_exist(rec_ret_schd.dt_alias,
4191 rec_ret_schd.dai_sequence_number,
4192 l_rec_roll_tp.cal_type,
4193 l_rec_roll_tp.sequence_number);
4194 FETCH cur_dai_exist INTO l_rec_dai;
4195 IF cur_dai_exist%NOTFOUND THEN
4196 CLOSE cur_dai_exist;
4197 l_b_prc_rollover := FALSE;
4198 fnd_message.set_name('IGS',
4199 'IGS_FI_TPRS_NO_DT_ALIAS_ROLL');
4200 fnd_message.set_token('ALT_CD',
4201 l_v_teach_alt_cd);
4202 fnd_message.set_token('DATE_ALIAS',
4203 rec_ret_schd.dt_alias);
4204 fnd_file.put_line(fnd_file.log,
4205 fnd_message.get);
4206 EXIT;
4207 END IF;
4208 CLOSE cur_dai_exist;
4209
4210 -- Insert the record for the New Teaching Period.
4211 -- Added call to format ret_amount by rounding off to currency precision
4212 IF l_b_prc_rollover THEN
4213 l_v_rowid := null;
4214 l_n_ret_id := null;
4215 igs_fi_tp_ret_schd_pkg.insert_row(x_rowid => l_v_rowid,
4216 x_ftci_teach_retention_id => l_n_ret_id,
4217 x_teach_cal_type => l_rec_roll_tp.cal_type,
4218 x_teach_ci_sequence_number => l_rec_roll_tp.sequence_number,
4219 x_fee_cal_type => p_v_dest_cal_type,
4220 x_fee_ci_sequence_number => p_n_dest_ci_seq_number,
4221 x_fee_type => p_v_fee_type,
4222 x_dt_alias => l_rec_dai.dt_alias,
4223 x_dai_sequence_number => l_rec_dai.sequence_number,
4224 x_ret_percentage => rec_ret_schd.ret_percentage,
4225 x_ret_amount => igs_fi_gen_gl.get_formatted_amount(rec_ret_schd.ret_amount));
4226 END IF;
4227
4228 END LOOP;
4229 END IF;
4230 EXCEPTION
4231 WHEN OTHERS THEN
4232 ROLLBACK TO SP_ROLL_TP;
4233 l_b_prc_rollover := FALSE;
4234 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
4235 fnd_message.set_name('IGS','IGS_FI_ROLLOVER_TPRS_ERROR');
4236 fnd_message.set_token('FEE_TYPE',p_v_fee_type);
4237 fnd_message.set_token('FEE_CAL_TYPE',p_v_source_cal_type);
4238 fnd_message.set_token('ALT_CODE',g_v_alternate_code);
4239 fnd_message.set_token('TEACH_CAL_TYPE',rec_dist_tp.teach_cal_type);
4240 fnd_message.set_token('TEACH_CI_ALT_CODE',l_v_teach_alt_cd);
4241 fnd_message.set_token('DT_ALIAS',l_rec_dai.dt_alias);
4242 fnd_file.put_line (fnd_file.log, fnd_message.get);
4243 END;
4244
4245
4246 -- If the Rollover validations have failed, then set
4247 -- the status to False else log the success message in the log file
4248 IF l_b_prc_rollover THEN
4249 fnd_message.set_name('IGS',
4250 'IGS_FI_ROLL_TP_FTCI');
4251 fnd_file.put_line(fnd_file.log,
4252 fnd_message.get);
4253 END IF;
4254 END LOOP;
4255
4256
4257 -- If no records were found, then return from the procedure
4258 -- with status as False and no record found message.
4259 IF NOT l_b_rec_found THEN
4260 p_v_message_name := 'IGS_FI_TPRS_NO_REC_FOUND';
4261 END IF;
4262
4263 EXCEPTION
4264 WHEN OTHERS THEN
4265 ROLLBACK TO SP_ROLLTP_MAIN;
4266 p_b_status := FALSE;
4267 fnd_file.put_line(fnd_file.log,
4268 fnd_message.get_string('IGS',
4269 'IGS_GE_UNHANDLED_EXCEPTION')||':'||sqlerrm);
4270 END finp_ins_roll_tprs;
4271
4272 END igs_fi_prc_fee_rollv;
[Home] [Help]