1 PACKAGE BODY igs_fi_acct_stp_cnv AS
2 /* $Header: IGSFI82B.pls 120.4 2006/05/04 23:32:53 sapanigr ship $ */
3
4 /*-------------------------------------------------------------------------
5 Created by : vvutukur, Oracle IDC
6 Date created: 23-May-2003
7
8 Purpose:
9
10 Known limitations/enhancements and/or remarks:
11
12 Change History:
13 Who When What
14 sapanigr 05-May-2006 Bug 5178077: Modified procedure updt_ftci_acct_info to disable process in R12.
15 svuppala 14-JUL-2005 Enh 3392095 - impact of Tution Waivers build
16 Modified igs_fi_control_pkg.update_row by adding two new columns
17 post_waiver_gl_flag, waiver_notify_finaid_flag
18 gurprsin 18-Jun-2005 Bug# 3392088 , Modified call to igs_fi_f_typ_ca_inst_pkg.update_row method to include
19 scope_rul_sequence_num and elm_rng_order_name
20 gurprsin 02-Jun-2005 Enh# 3442712 - Fee Based on Unit Level Attributes
21 Added 4 new columns unit_level,unit_type_id,unit_class,unit_mode in insert_ftci_accounts
22 Modifications to reflect the Addition of 4 new parameters to incorporate Unit Level Attributes.
23 updated call to insert_ftci_accts in updt_ftci_acct_info procedure to pass new parameters with NULL values
24 svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
25 Modifications to reflect the data model changes (NONZERO_BILLABLE_CP_FLAG) in
26 Fee Type Calendar Instances Table
27 rmaddipa 17-Sep-2004 Enh# 3880438 Modified the procedure update_ftci_rev_acc_cd
28 jbegum 14-June-2003 Bug# 2998266 Obsoleted the column NEXT_INVOICE_NUMBER.
29 shtatiko 12-JUN-2003 Enh# 2831582, Modified updt_ftci_acct_info.
30 -------------------------------------------------------------------*/
31
32 skip EXCEPTION;
33 incomplete_setup EXCEPTION;
34 success EXCEPTION;
35
36 --Cursor to fetch all the fee type calendar instances for account conversion.
37 CURSOR cur_ftci IS
38 SELECT ca.start_dt,
39 ca.end_dt,
40 ft.s_fee_type,
41 h.name,
42 ftci.rowid,
43 ftci.*
44 FROM igs_fi_fee_type ft,
45 igs_fi_f_typ_ca_inst ftci,
46 igs_ca_inst ca,
47 igs_fi_hier_accounts h
48 WHERE ft.fee_type = ftci.fee_type
49 AND ftci.fee_cal_type = ca.cal_type
50 AND ftci.fee_ci_sequence_number = ca.sequence_number
51 AND ftci.acct_hier_id = h.acct_hier_id
52 ORDER BY ft.fee_type,
53 ftci.fee_cal_type,
54 ca.start_dt;
55
56 PROCEDURE insert_ftci_accounts( p_v_fee_type IN VARCHAR2,
57 p_v_fee_cal_type IN VARCHAR2,
58 p_n_fee_ci_sequence_number IN NUMBER,
59 p_n_order_sequence IN NUMBER,
60 p_n_natural_account_segment IN VARCHAR2,
61 p_v_rev_account_cd IN VARCHAR2,
62 p_v_location_cd IN VARCHAR2,
63 p_v_attendance_type IN VARCHAR2,
64 p_v_attendance_mode IN VARCHAR2,
65 p_v_course_cd IN VARCHAR2,
66 p_n_crs_version_number IN NUMBER,
67 p_v_unit_cd IN VARCHAR2,
68 p_n_unit_version_number IN NUMBER,
69 p_v_org_unit_cd IN VARCHAR2,
70 p_v_residency_status_cd IN VARCHAR2,
71 p_n_uoo_id IN NUMBER,
72 p_v_unit_level IN VARCHAR2,
73 p_n_unit_type_id IN NUMBER,
74 p_v_unit_mode IN VARCHAR2,
75 p_v_unit_class IN VARCHAR2
76 ) IS
77 /*------------------------------------------------------------------
78 Created by : vvutukur, Oracle IDC
79 Date created: 23-May-2003
80
81 Purpose: Inserts record into igs_fi_ftci_accts table using the specified parameters.
82
83 Known limitations/enhancements and/or remarks:
84
85 Change History:
86 Who When What
87 gurprsin 02-Jun-2005 Enh# 3442712, Added 4 new paramters for Unit Level Attributes
88 -------------------------------------------------------------------*/
89 l_rowid VARCHAR2(25);
90 l_n_acct_id igs_fi_ftci_accts.acct_id%TYPE;
91
92 BEGIN
93
94 l_rowid := NULL;
95 l_n_acct_id := NULL;
96
97 igs_fi_ftci_accts_pkg.insert_row (
98 x_rowid => l_rowid,
99 x_acct_id => l_n_acct_id,
100 x_fee_type => p_v_fee_type,
101 x_fee_cal_type => p_v_fee_cal_type,
102 x_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
103 x_order_sequence => p_n_order_sequence,
104 x_natural_account_segment => p_n_natural_account_segment,
105 x_rev_account_cd => p_v_rev_account_cd,
106 x_location_cd => p_v_location_cd,
107 x_attendance_type => p_v_attendance_type,
108 x_attendance_mode => p_v_attendance_mode,
109 x_course_cd => p_v_course_cd,
110 x_crs_version_number => p_n_crs_version_number,
111 x_unit_cd => p_v_unit_cd,
112 x_unit_version_number => p_n_unit_version_number,
113 x_org_unit_cd => p_v_org_unit_cd,
114 x_residency_status_cd => p_v_residency_status_cd,
115 x_uoo_id => p_n_uoo_id,
116 x_mode => 'R',
117 x_unit_level => p_v_unit_level,
118 x_unit_type_id => p_n_unit_type_id,
119 x_unit_mode => p_v_unit_mode,
120 x_unit_class => p_v_unit_class
121 );
122 END insert_ftci_accounts;
123
124 PROCEDURE update_ftci_rev_acc_cd( l_cur_ftci_details cur_ftci%ROWTYPE,
125 p_v_rev_account_cd igs_fi_f_typ_ca_inst.rev_account_cd%TYPE) IS
126 /*------------------------------------------------------------------
127 Created by : vvutukur, Oracle IDC
128 Date created: 05-Jun-2003
129
130 Purpose: Updates the FTCI record with the specified revenue account code parameter value.
131
132 Known limitations/enhancements and/or remarks:
133
134 Change History:
135 Who When What
136 gurprsin 18-Jun-2005 Bug# 3392088 , Modified call to igs_fi_f_typ_ca_inst_pkg.update_row method to include
137 scope_rul_sequence_num and elm_rng_order_name
138 svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
139 TBH impact of NONZERO_BILLABLE_CP_FLAG field in Fee Type Calendar Instances Table
140 rmaddipa 17-Sep-2004 Added two parameters retention_level_code,complete_ret_flag.
141 -------------------------------------------------------------------*/
142
143 BEGIN
144
145 igs_fi_f_typ_ca_inst_pkg.update_row(
146 x_rowid => l_cur_ftci_details.rowid,
147 x_fee_type => l_cur_ftci_details.fee_type,
148 x_fee_cal_type => l_cur_ftci_details.fee_cal_type,
149 x_fee_ci_sequence_number => l_cur_ftci_details.fee_ci_sequence_number,
150 x_fee_type_ci_status => l_cur_ftci_details.fee_type_ci_status,
151 x_start_dt_alias => l_cur_ftci_details.start_dt_alias,
152 x_start_dai_sequence_number => l_cur_ftci_details.start_dai_sequence_number,
153 x_end_dt_alias => l_cur_ftci_details.end_dt_alias,
154 x_end_dai_sequence_number => l_cur_ftci_details.end_dai_sequence_number,
155 x_retro_dt_alias => l_cur_ftci_details.retro_dt_alias,
156 x_retro_dai_sequence_number => l_cur_ftci_details.retro_dai_sequence_number,
157 x_s_chg_method_type => l_cur_ftci_details.s_chg_method_type,
158 x_rul_sequence_number => l_cur_ftci_details.rul_sequence_number,
159 x_mode => 'R',
160 x_initial_default_amount => l_cur_ftci_details.initial_default_amount,
161 x_acct_hier_id => l_cur_ftci_details.acct_hier_id,
162 x_rec_gl_ccid => l_cur_ftci_details.rec_gl_ccid,
163 x_rev_account_cd => p_v_rev_account_cd,
164 x_rec_account_cd => l_cur_ftci_details.rec_account_cd,
165 x_ret_gl_ccid => l_cur_ftci_details.ret_gl_ccid,
166 x_ret_account_cd => l_cur_ftci_details.ret_account_cd,
167 x_retention_level_code => l_cur_ftci_details.retention_level_code,
168 x_complete_ret_flag => l_cur_ftci_details.complete_ret_flag,
169 x_nonzero_billable_cp_flag => l_cur_ftci_details.nonzero_billable_cp_flag,
170 x_scope_rul_sequence_num => l_cur_ftci_details.scope_rul_sequence_num,
171 x_elm_rng_order_name => l_cur_ftci_details.elm_rng_order_name
172 );
173 END update_ftci_rev_acc_cd;
174
175 PROCEDURE updt_ftci_acct_info( errbuf OUT NOCOPY VARCHAR2,
176 retcode OUT NOCOPY NUMBER
177 ) IS
178 /*------------------------------------------------------------------
179 Created by : vvutukur, Oracle IDC
180 Date created: 23-May-2003
181
182 Purpose: Converts Existing Accounting Setup Details.
183
184 Known limitations/enhancements and/or remarks:
185
186 Change History:
187 Who When What
188 sapanigr 05-May-2006 Bug 5178077: Added call to igs_ge_gen_003.set_org_id. to disable process in R12
189 svuppala 14-JUL-2005 Enh 3392095 - impact of Tution Waivers build
190 Modified igs_fi_control_pkg.update_row by adding two new columns
191 post_waiver_gl_flag, waiver_notify_finaid_flag
192 gurprsin 02-Jun-2005 Enh#3442712, Modified calls to igs_fi_accts_pkg.get_uk2_for_validation by adding 4 new unit level parameters
193 insert_ftci_accounts and updt_ftci_acct_info method
194 pmarada 19-Nov-2004 Bug 4017841, Removed the obsoleted res_dt_alias column reference from igs_fi_control table update row
195 uudayapr 23-dec-2003 Enh#3167098 Removed Column Name PRG_CHG_DT_ALIAS and added RES_DT_ALIAS in
196 igs_fi_control_pkg.update_row
197 shtatiko 12-JUN-2003 Enh# 2831582, Removed Obsoleted columns from igs_fi_control_pkg.update_row
198 -------------------------------------------------------------------*/
199
200 CURSOR cur_ctrl IS
201 SELECT fc.rowid, fc.*
202 FROM igs_fi_control_all fc;
203
204 l_cur_ctrl cur_ctrl%ROWTYPE;
205
206 CURSOR cur_chk_acct_rec_exists(cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
207 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
208 cp_n_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE
209 )IS
210 SELECT 'x'
211 FROM igs_fi_ftci_accts
212 WHERE fee_type = cp_v_fee_type
213 AND fee_cal_type = cp_v_fee_cal_type
214 AND fee_ci_sequence_number = cp_n_seq_number;
215
216 l_v_var VARCHAR2(1);
217 l_n_seq igs_fi_ftci_accts.order_sequence%TYPE;
218
219 CURSOR cur_hier(cp_n_acct_hier_id igs_fi_hier_acct_tbl.acct_hier_id%TYPE) IS
220 SELECT entity_type_code
221 FROM igs_fi_hier_acct_tbl
222 WHERE acct_hier_id = cp_n_acct_hier_id
223 ORDER BY order_sequence;
224
225 CURSOR cur_prg_rev_cd IS
226 SELECT course_cd, version_number, rev_account_cd
227 FROM igs_ps_ver
228 WHERE rev_account_cd IS NOT NULL
229 ORDER BY course_cd, version_number;
230
231 CURSOR cur_unit_rev_cd IS
232 SELECT unit_cd, version_number, rev_account_cd
233 FROM igs_ps_unit_ver
234 WHERE rev_account_cd IS NOT NULL
235 ORDER BY unit_cd, version_number;
236
237 CURSOR cur_uoo_rev_cd IS
238 SELECT uoo_id, rev_account_cd
239 FROM igs_ps_unit_ofr_opt
240 WHERE rev_account_cd IS NOT NULL
241 ORDER BY unit_cd, version_number, cal_type, ci_sequence_number, location_cd, unit_class;
242
243 CURSOR cur_loc_rev_cd IS
244 SELECT location_cd, rev_account_cd
245 FROM igs_ad_location
246 WHERE rev_account_cd IS NOT NULL
247 ORDER BY location_cd;
248
249 CURSOR cur_org_rev_cd IS
250 SELECT org_unit_cd, rev_account_cd
251 FROM igs_or_unit_acct_cd
252 WHERE rev_account_cd IS NOT NULL
253 ORDER BY org_unit_cd;
254
255 CURSOR cur_hier_oth(cp_acct_hier_id NUMBER) IS
256 SELECT entity_type_code
257 FROM igs_fi_hier_acct_tbl
258 WHERE acct_hier_id = cp_acct_hier_id
259 AND entity_type_code IN ('FTCI','SA')
260 ORDER BY order_sequence;
261
262 CURSOR cur_hier_oth_count(cp_acct_hier_id NUMBER) IS
263 SELECT COUNT(entity_type_code)
264 FROM igs_fi_hier_acct_tbl
265 WHERE acct_hier_id = cp_acct_hier_id
266 AND entity_type_code IN ('FTCI','SA');
267
268 l_v_fee_type igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPE');
269 l_v_fee_period igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_PERIOD');
270 l_v_acct_hierarchy igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','ACCT_HIERARCHY');
271 l_v_message_text igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','MESSAGE_TEXT');
272
273 l_v_rev_account_cd igs_fi_f_typ_ca_inst.rev_account_cd%TYPE;
274 l_v_entity_code igs_fi_hier_acct_tbl.entity_type_code%TYPE;
275
276 --For Program.
277 TYPE prg_rec_type IS RECORD (
278 course_cd igs_ps_ver.course_cd%TYPE,
279 version_number igs_ps_ver.version_number%TYPE,
280 rev_account_cd igs_ps_ver.rev_account_cd%TYPE);
281
282 TYPE prg_tbl_type IS TABLE OF prg_rec_type INDEX BY BINARY_INTEGER;
283 prg_tbl prg_tbl_type;
284
285
286 --For Unit.
287 TYPE unit_rec_type IS RECORD (
288 unit_cd igs_ps_unit_ver.unit_cd%TYPE,
289 version_number igs_ps_unit_ver.version_number%TYPE,
290 rev_account_cd igs_ps_unit_ver.rev_account_cd%TYPE);
291
292 TYPE unit_tbl_type IS TABLE OF unit_rec_type INDEX BY BINARY_INTEGER;
293 unit_tbl unit_tbl_type;
294
295
296 --For Unit Section.
300
297 TYPE usec_rec_type IS RECORD (
298 uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
299 rev_account_cd igs_ps_unit_ofr_opt.rev_account_cd%TYPE);
301 TYPE usec_tbl_type IS TABLE OF usec_rec_type INDEX BY BINARY_INTEGER;
302 usec_tbl usec_tbl_type;
303
304
305 --For Location.
306 TYPE loc_rec_type IS RECORD (
307 location_cd igs_ad_location.location_cd%TYPE,
308 rev_account_cd igs_ad_location.rev_account_cd%TYPE);
309
310 TYPE loc_tbl_type IS TABLE OF loc_rec_type INDEX BY BINARY_INTEGER;
311 loc_tbl loc_tbl_type;
312
313
314 --For Organization Unit Code.
315 TYPE org_rec_type IS RECORD (
316 org_unit_cd igs_or_unit_acct_cd.org_unit_cd%TYPE,
317 rev_account_cd igs_or_unit_acct_cd.rev_account_cd%TYPE);
318
319 TYPE org_tbl_type IS TABLE OF org_rec_type INDEX BY BINARY_INTEGER;
320 org_tbl org_tbl_type;
321
322 i PLS_INTEGER;
323 l_n_no_of_hier_entities NUMBER := 0;
324
325 l_org_id VARCHAR2(15);
326
327 BEGIN
328
329 BEGIN
330 l_org_id := NULL;
331 igs_ge_gen_003.set_org_id(l_org_id);
332 EXCEPTION
333 WHEN OTHERS THEN
334 fnd_file.put_line (fnd_file.log, fnd_message.get);
335 RETCODE:=2;
336 RETURN;
337 END;
338
339 --For Operating Student Finance module, user needs to setup the details in the System Options form.
340 --Check the set up done in System Options form.
341 OPEN cur_ctrl;
342 FETCH cur_ctrl INTO l_cur_ctrl;
343 --If no set up is done in System Options Form, message is logged in the log file and process completes with warning.
344 IF cur_ctrl%NOTFOUND THEN
345 CLOSE cur_ctrl;
346 fnd_message.set_name('IGS','IGS_FI_ACCT_CNV_PRC_NOT_REQ');
347 fnd_file.put_line(fnd_file.log,fnd_message.get);
348 retcode := 1;
349 RETURN;
350 ELSE--if record exists in igs_fi_control table...
351 CLOSE cur_ctrl;
352
353 --This process is intended to execute only when Oracle General Ledger is not installed in the system.
354 --Check if Oracle General Ledger is installed..
355 IF l_cur_ctrl.rec_installed = 'Y' THEN
356 --If yes, then log the error message in the logfile and the process completes with error.
357 fnd_message.set_name('IGS','IGS_FI_ACCT_CNV_PRC_NOT_VALID');
358 fnd_file.put_line(fnd_file.log,fnd_message.get);
359 retcode := 2;
360 RETURN;
361 END IF;
362
363 --This process is intended to for onetime usage only. Check if the value of acct_conv_flag in igs_fi_control_all is null.
364 --If this value is not null, then message is logged in the log file and process completes with warning.
365 IF l_cur_ctrl.acct_conv_flag IS NOT NULL THEN
366 fnd_message.set_name('IGS','IGS_FI_ACCT_CNV_PRC_NOT_REQ');
367 fnd_file.put_line(fnd_file.log,fnd_message.get);
368 retcode := 1;
369 RETURN;
370 END IF;
371 END IF;
372
373 i := 1;
374 --Capture the program details in table type variable.
375 FOR l_cur_prg_rev_cd IN cur_prg_rev_cd LOOP
376 prg_tbl(i).course_cd := l_cur_prg_rev_cd.course_cd;
377 prg_tbl(i).version_number := l_cur_prg_rev_cd.version_number;
378 prg_tbl(i).rev_account_cd := l_cur_prg_rev_cd.rev_account_cd;
379 i := i + 1;
380 END LOOP;
381
382 i := 1;
383 --Capture the unit details in table type variable.
384 FOR l_cur_unit_rev_cd IN cur_unit_rev_cd LOOP
385 unit_tbl(i).unit_cd := l_cur_unit_rev_cd.unit_cd;
386 unit_tbl(i).version_number := l_cur_unit_rev_cd.version_number;
387 unit_tbl(i).rev_account_cd := l_cur_unit_rev_cd.rev_account_cd;
388 i := i + 1;
389 END LOOP;
390
391 i := 1;
392 --Capture the unit section details in table type variable.
393 FOR l_cur_uoo_rev_cd IN cur_uoo_rev_cd LOOP
394 usec_tbl(i).uoo_id := l_cur_uoo_rev_cd.uoo_id;
395 usec_tbl(i).rev_account_cd := l_cur_uoo_rev_cd.rev_account_cd;
396 i := i + 1;
397 END LOOP;
398
399 i := 1;
400 --Capture the location details in table type variable.
401 FOR l_cur_loc_rev_cd IN cur_loc_rev_cd LOOP
402 loc_tbl(i).location_cd := l_cur_loc_rev_cd.location_cd;
403 loc_tbl(i).rev_account_cd := l_cur_loc_rev_cd.rev_account_cd;
404 i := i + 1;
405 END LOOP;
406
407 i := 1;
408 --Capture the organization unit code details in table type variable.
409 FOR l_cur_org_rev_cd IN cur_org_rev_cd LOOP
410 org_tbl(i).org_unit_cd := l_cur_org_rev_cd.org_unit_cd;
411 org_tbl(i).rev_account_cd := l_cur_org_rev_cd.rev_account_cd;
412 i := i + 1;
413 END LOOP;
414
415
416 --Loop through each FTCI record.
417 FOR l_cur_ftci IN cur_ftci LOOP
418 --log FTCI details in the log file.
419 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
420 fnd_file.put_line(fnd_file.log,'');
421 fnd_file.put_line(fnd_file.log,l_v_fee_type||RPAD(' ',10,' ')||': '||l_cur_ftci.fee_type);
422 fnd_file.put_line(fnd_file.log,l_v_fee_period||RPAD(' ',8,' ')||': '||l_cur_ftci.fee_cal_type||' '||l_cur_ftci.start_dt||' - '||l_cur_ftci.end_dt);
423 fnd_file.put_line(fnd_file.log,l_v_acct_hierarchy||RPAD(' ',1,' ')||': '||l_cur_ftci.name);
424
425 BEGIN
426
430 OPEN cur_chk_acct_rec_exists(l_cur_ftci.fee_type,
427 --If the System Fee Type of the context FTCI is either Tuition or Other.
428 IF l_cur_ftci.s_fee_type IN ('TUTNFEE','OTHER') THEN
429 --Check if a record is present in the accounts table for the context FTCI combination.
431 l_cur_ftci.fee_cal_type,
432 l_cur_ftci.fee_ci_sequence_number);
433 FETCH cur_chk_acct_rec_exists INTO l_v_var;
434
435 --For a Fee Type Calendar Instance in context, if the user has already defined the Accounting details in the Account Table,
436 --(igs_fi_ftci_accts), log the error message in the log file, skip the current FTCI record and process the next one.
437 IF cur_chk_acct_rec_exists%FOUND THEN
438 CLOSE cur_chk_acct_rec_exists;
439 fnd_message.set_name('IGS','IGS_FI_FTCI_ACCT_REC_EXISTS');
440 fnd_file.put_line(fnd_file.log,l_v_message_text||RPAD(' ',6,' ')||': '||fnd_message.get);
441 RAISE skip;
442 END IF;
443 CLOSE cur_chk_acct_rec_exists;
444
445 l_n_seq := 0;
446
447 --Get the accounting hierarchy order for the account hierarchy attached to the Fee Type Calendar Instance.
448 FOR l_cur_hier IN cur_hier(l_cur_ftci.acct_hier_id) LOOP
449
450 --If the hierarchy entity is either System Options or Fee Type.
451 IF l_cur_hier.entity_type_code IN ('FTCI','SA') THEN
452
453 --Check there is no record in the accounts table with the context FTCI details and other attributes as NULL.
454 --Added 4 new paramters for Unit Level Attributes
455 IF NOT igs_fi_ftci_accts_pkg.get_uk2_for_validation( x_fee_type => l_cur_ftci.fee_type,
456 x_fee_cal_type => l_cur_ftci.fee_cal_type,
457 x_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
458 x_location_cd => NULL,
459 x_attendance_type => NULL,
460 x_attendance_mode => NULL,
461 x_course_cd => NULL,
462 x_crs_version_number => NULL,
463 x_unit_cd => NULL,
464 x_unit_version_number => NULL,
465 x_org_unit_cd => NULL,
466 x_residency_status_cd => NULL,
467 x_uoo_id => NULL,
468 x_unit_level => NULL,
469 x_unit_type_id => NULL,
470 x_unit_mode => NULL,
471 x_unit_class => NULL
472 ) THEN
473
474 --Get the revenue account code that is set up at context entity type.(either FTCI or SA).
475 IF l_cur_hier.entity_type_code = 'FTCI' THEN
476 l_v_rev_account_cd := l_cur_ftci.rev_account_cd;
477 ELSE
478 l_v_rev_account_cd := l_cur_ctrl.rev_account_cd;
479 END IF;
480
481 IF l_v_rev_account_cd IS NOT NULL THEN
482
483 --Increment the order sequence as this is part of unique key.
484 l_n_seq := l_n_seq + 1;
485
486 --Insert a record in igs_fi_ftci_accts table.
487 --Added 4 new paramters for Unit Level Attributes
488 insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
489 p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
490 p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
491 p_n_order_sequence => l_n_seq,
492 p_n_natural_account_segment => NULL,
493 p_v_rev_account_cd => l_v_rev_account_cd,
494 p_v_location_cd => NULL,
495 p_v_attendance_type => NULL,
496 p_v_attendance_mode => NULL,
497 p_v_course_cd => NULL,
498 p_n_crs_version_number => NULL,
499 p_v_unit_cd => NULL,
500 p_n_unit_version_number => NULL,
501 p_v_org_unit_cd => NULL,
502 p_v_residency_status_cd => NULL,
503 p_n_uoo_id => NULL,
504 p_v_unit_level => NULL,
505 p_n_unit_type_id => NULL,
509 END IF;
506 p_v_unit_mode => NULL,
507 p_v_unit_class => NULL);
508 END IF;
510 ELSIF l_cur_hier.entity_type_code = 'PS' THEN
511 IF prg_tbl.COUNT > 0 THEN
512 FOR i IN prg_tbl.FIRST .. prg_tbl.LAST LOOP
513 IF prg_tbl.EXISTS(i) THEN
514 --Increment the order sequence as this is part of unique key.
515 l_n_seq := l_n_seq + 1;
516 --Insert a record in igs_fi_ftci_accts table with corresponding program details.
517 --Added 4 new paramters for Unit Level Attributes
518 insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
519 p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
520 p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
521 p_n_order_sequence => l_n_seq,
522 p_n_natural_account_segment => NULL,
523 p_v_rev_account_cd => prg_tbl(i).rev_account_cd,
524 p_v_location_cd => NULL,
525 p_v_attendance_type => NULL,
526 p_v_attendance_mode => NULL,
527 p_v_course_cd => prg_tbl(i).course_cd,
528 p_n_crs_version_number => prg_tbl(i).version_number,
529 p_v_unit_cd => NULL,
530 p_n_unit_version_number => NULL,
531 p_v_org_unit_cd => NULL,
532 p_v_residency_status_cd => NULL,
533 p_n_uoo_id => NULL,
534 p_v_unit_level => NULL,
535 p_n_unit_type_id => NULL,
536 p_v_unit_mode => NULL,
537 p_v_unit_class => NULL);
538 END IF;
539 END LOOP;
540 END IF;
541 ELSIF l_cur_hier.entity_type_code = 'UNIT' THEN
542 IF unit_tbl.COUNT > 0 THEN
543 FOR i IN unit_tbl.FIRST .. unit_tbl.LAST LOOP
544 IF unit_tbl.EXISTS(i) THEN
545
546 --Increment the order sequence as this is part of unique key.
547 l_n_seq := l_n_seq + 1;
548
549 --Insert a record in igs_fi_ftci_accts table with corresponding unit details.
550 --Added 4 new paramters for Unit Level Attributes
551 insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
552 p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
553 p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
554 p_n_order_sequence => l_n_seq,
555 p_n_natural_account_segment => NULL,
556 p_v_rev_account_cd => unit_tbl(i).rev_account_cd,
557 p_v_location_cd => NULL,
558 p_v_attendance_type => NULL,
559 p_v_attendance_mode => NULL,
560 p_v_course_cd => NULL,
561 p_n_crs_version_number => NULL,
562 p_v_unit_cd => unit_tbl(i).unit_cd,
563 p_n_unit_version_number => unit_tbl(i).version_number,
564 p_v_org_unit_cd => NULL,
565 p_v_residency_status_cd => NULL,
566 p_n_uoo_id => NULL,
567 p_v_unit_level => NULL,
568 p_n_unit_type_id => NULL,
569 p_v_unit_mode => NULL,
570 p_v_unit_class => NULL);
571 END IF;
572 END LOOP;
573 END IF;
574 ELSIF l_cur_hier.entity_type_code = 'USEC' THEN
575 IF usec_tbl.COUNT > 0 THEN
576 FOR i IN usec_tbl.FIRST .. usec_tbl.LAST LOOP
577 IF usec_tbl.EXISTS(i) THEN
578 l_n_seq := l_n_seq + 1;
579
580 --Insert a record in igs_fi_ftci_accts table with corresponding unit section details.
581 --Added 4 new paramters for Unit Level Attributes
582 insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
583 p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
584 p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
588 p_v_location_cd => NULL,
585 p_n_order_sequence => l_n_seq,
586 p_n_natural_account_segment => NULL,
587 p_v_rev_account_cd => usec_tbl(i).rev_account_cd,
589 p_v_attendance_type => NULL,
590 p_v_attendance_mode => NULL,
591 p_v_course_cd => NULL,
592 p_n_crs_version_number => NULL,
593 p_v_unit_cd => NULL,
594 p_n_unit_version_number => NULL,
595 p_v_org_unit_cd => NULL,
596 p_v_residency_status_cd => NULL,
597 p_n_uoo_id => usec_tbl(i).uoo_id,
598 p_v_unit_level => NULL,
599 p_n_unit_type_id => NULL,
600 p_v_unit_mode => NULL,
601 p_v_unit_class => NULL);
602 END IF;
603 END LOOP;
604 END IF;
605 ELSIF l_cur_hier.entity_type_code = 'LOC' THEN
606 IF loc_tbl.COUNT > 0 THEN
607 FOR i IN loc_tbl.FIRST .. loc_tbl.LAST LOOP
608 IF loc_tbl.EXISTS(i) THEN
609
610 --Increment the order sequence as this is part of unique key.
611 l_n_seq := l_n_seq + 1;
612
613 --Insert a record in igs_fi_ftci_accts table with corresponding location details.
614 --Added 4 new paramters for Unit Level Attributes
615 insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
616 p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
617 p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
618 p_n_order_sequence => l_n_seq,
619 p_n_natural_account_segment => NULL,
620 p_v_rev_account_cd => loc_tbl(i).rev_account_cd,
621 p_v_location_cd => loc_tbl(i).location_cd,
622 p_v_attendance_type => NULL,
623 p_v_attendance_mode => NULL,
624 p_v_course_cd => NULL,
625 p_n_crs_version_number => NULL,
626 p_v_unit_cd => NULL,
627 p_n_unit_version_number => NULL,
628 p_v_org_unit_cd => NULL,
629 p_v_residency_status_cd => NULL,
630 p_n_uoo_id => NULL,
631 p_v_unit_level => NULL,
632 p_n_unit_type_id => NULL,
633 p_v_unit_mode => NULL,
634 p_v_unit_class => NULL);
635 END IF;
636 END LOOP;
637 END IF;
638 ELSIF l_cur_hier.entity_type_code = 'ORG' THEN
639 IF org_tbl.COUNT > 0 THEN
640 FOR i IN org_tbl.FIRST .. org_tbl.LAST LOOP
641 IF org_tbl.EXISTS(i) THEN
642
643 --Increment the order sequence as this is part of unique key.
644 l_n_seq := l_n_seq + 1;
645
646 --Insert a record in igs_fi_ftci_accts table with corresponding org unit code details.
647 --Added 4 new paramters for Unit Level Attributes
648 insert_ftci_accounts(p_v_fee_type => l_cur_ftci.fee_type,
649 p_v_fee_cal_type => l_cur_ftci.fee_cal_type,
650 p_n_fee_ci_sequence_number => l_cur_ftci.fee_ci_sequence_number,
651 p_n_order_sequence => l_n_seq,
652 p_n_natural_account_segment => NULL,
653 p_v_rev_account_cd => org_tbl(i).rev_account_cd,
654 p_v_location_cd => NULL,
655 p_v_attendance_type => NULL,
656 p_v_attendance_mode => NULL,
657 p_v_course_cd => NULL,
658 p_n_crs_version_number => NULL,
659 p_v_unit_cd => NULL,
660 p_n_unit_version_number => NULL,
661 p_v_org_unit_cd => org_tbl(i).org_unit_cd,
662 p_v_residency_status_cd => NULL,
666 p_v_unit_mode => NULL,
663 p_n_uoo_id => NULL,
664 p_v_unit_level => NULL,
665 p_n_unit_type_id => NULL,
667 p_v_unit_class => NULL);
668 END IF;
669 END LOOP;
670 END IF;
671 END IF;
672 END LOOP;--cur_hier loop.
673
674 --If the user has not set up the Revenue Account Code anywhere...
675 IF l_n_seq = 0 THEN
676 RAISE incomplete_setup;
680
677 ELSE
678 RAISE success;
679 END IF;
681 ELSE--If system fee type is not in either TUTNFEE or OTHER.
682
683 --Check how many hierarchy order entities are defined in the hierarchy order form.
684 OPEN cur_hier_oth_count(l_cur_ftci.acct_hier_id);
685 FETCH cur_hier_oth_count INTO l_n_no_of_hier_entities;
686 CLOSE cur_hier_oth_count;
687
688 --If no hierarchy order ntities are defined for the account hierarchy, which is attached to the context FTCI.
689 IF l_n_no_of_hier_entities = 0 THEN
690 --log the incomplete account set up information message in the log file.
691 RAISE incomplete_setup;
692 ELSE--if atleast one hierarchy order entity is specified..
693
694 --Capture the first hierarchy order entity.
695 OPEN cur_hier_oth(l_cur_ftci.acct_hier_id);
696 FETCH cur_hier_oth INTO l_v_entity_code;
697 CLOSE cur_hier_oth;
698 --If the only one hierarchy order entity is specified in hierarchy order form..
699 IF l_n_no_of_hier_entities = 1 THEN
700 --If System Options is the one and only hierarchy order specified in hierarchy order form.
701 IF l_v_entity_code = 'SA' THEN
702 --Override the revenue account code at FTCI with the revenue account code that is present in System Options Level.
703 --Here irrespective of the values present at both the levels, the value will be overridden.
704 update_ftci_rev_acc_cd(l_cur_ftci,l_cur_ctrl.rev_account_cd);
705
706 --After the above update happens, if the revenue account code is NULL both at System Options Level and FTCI level.
707 --show the incomplete account set up message to the user.
708 IF l_cur_ctrl.rev_account_cd IS NULL THEN
709 RAISE incomplete_setup;
710 END IF;
711 ELSE
712 --If FTCI is the one and only hierarchy order specified in hierarchy order form, we do not need to do anything for conversion.
713 --But if revenue account code is not set up at both levels, incomplete set up message should be logged.
714 IF l_cur_ftci.rev_account_cd IS NULL THEN
715 RAISE incomplete_setup;
716 END IF;
717 END IF;
718 --If both Fee Type and System Option hierarchy orders are specified in the hierarchy order form.
719 ELSIF l_n_no_of_hier_entities = 2 THEN
720 --Check if the first in the order of sequence is Fee Type.
721 IF l_v_entity_code = 'FTCI' THEN
722 --
723 IF l_cur_ftci.rev_account_cd IS NULL THEN
724 IF l_cur_ctrl.rev_account_cd IS NOT NULL THEN
725 update_ftci_rev_acc_cd(l_cur_ftci,l_cur_ctrl.rev_account_cd);
726 ELSE
727 RAISE incomplete_setup;
728 END IF;
729 END IF;
730 --Check if the first in the order of sequence is Fee Type.
731 ELSIF l_v_entity_code = 'SA' THEN
732 --Check if a value is present for the revenue account code at System level.
733 IF l_cur_ctrl.rev_account_cd IS NOT NULL THEN
734 --the context FTCI record's revenue account code will be overridden with the revenue account code at system level,
735 --if revenue account code at system level is having some value.
736 update_ftci_rev_acc_cd(l_cur_ftci,l_cur_ctrl.rev_account_cd);
737 ELSE
738 --If revenue account code is not specified either at system level or at FTCI level..
739 IF l_cur_ftci.rev_account_cd IS NULL THEN
740 --then log the incomplete account set up message.
741 RAISE incomplete_setup;
742 END IF;
743 END IF;
744 END IF;
745 END IF;
746 END IF;
747
748 END IF;--System Fee Type.
749
750 --log successful account conversion message for the FTCI record after all the above steps are passed.
751 RAISE success;
752
753 EXCEPTION
754 WHEN incomplete_setup THEN
755 --log the information message that the accounting setup is incomplete for the context FTCI.
756 fnd_message.set_name('IGS','IGS_FI_ACCT_INCOMPLETE_SETUP');
757 fnd_file.put_line(fnd_file.log,l_v_message_text||RPAD(' ',6,' ')||': '||fnd_message.get);
758 retcode := 1;
759 WHEN success THEN
760 --log the information message that the accounting setup is completed.
761 fnd_message.set_name('IGS','IGS_FI_ACCT_CNV_PRC_SUCCESS');
762 fnd_file.put_line(fnd_file.log,l_v_message_text||RPAD(' ',6,' ')||': '||fnd_message.get);
763 WHEN skip THEN
764 NULL;
765 END;
766
767 -- Committing after each and every FTCI record.
768 COMMIT;
769
770 END LOOP;--FTCI loop
771
772 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
773
774
775 --After accounting conversion is completed, update the Account Conversion Flag to Y.
776
777 --Update acct_conv_flag column only with value 'Y', of igs_fi_control table.
778 -- Removed lockbox_context, lockbox_number_attribute and ar_int_org_id columns from the following call
779 -- as part of Enh# 2831582, Lockbox.
780 -- Bug#2998266 Removed the column next_invoice_number from call to igs_fi_control_pkg.update_row
781 -- Enh 3392095 added two new columns post_waiver_gl_flag, waiver_notify_finaid_flag
782 igs_fi_control_pkg.update_row(x_rowid => l_cur_ctrl.rowid,
783 x_rec_installed => l_cur_ctrl.rec_installed,
784 x_accounting_method => l_cur_ctrl.accounting_method,
785 x_set_of_books_id => l_cur_ctrl.set_of_books_id,
786 x_refund_dr_gl_ccid => l_cur_ctrl.refund_dr_gl_ccid,
787 x_refund_cr_gl_ccid => l_cur_ctrl.refund_cr_gl_ccid,
788 x_refund_dr_account_cd => l_cur_ctrl.refund_dr_account_cd,
789 x_refund_cr_account_cd => l_cur_ctrl.refund_cr_account_cd,
790 x_refund_dt_alias => l_cur_ctrl.refund_dt_alias,
791 x_fee_calc_mthd_code => l_cur_ctrl.fee_calc_mthd_code,
792 x_planned_credits_ind => l_cur_ctrl.planned_credits_ind,
793 x_rec_gl_ccid => l_cur_ctrl.rec_gl_ccid,
794 x_cash_gl_ccid => l_cur_ctrl.cash_gl_ccid,
795 x_unapp_gl_ccid => l_cur_ctrl.unapp_gl_ccid,
796 x_rec_account_cd => l_cur_ctrl.rec_account_cd,
797 x_rev_account_cd => l_cur_ctrl.rev_account_cd,
798 x_cash_account_cd => l_cur_ctrl.cash_account_cd,
799 x_unapp_account_cd => l_cur_ctrl.unapp_account_cd,
800 x_conv_process_run_ind => l_cur_ctrl.conv_process_run_ind,
801 x_currency_cd => l_cur_ctrl.currency_cd,
802 x_rfnd_destination => l_cur_ctrl.rfnd_destination,
803 x_ap_org_id => l_cur_ctrl.ap_org_id,
804 x_dflt_supplier_site_name => l_cur_ctrl.dflt_supplier_site_name,
805 x_manage_accounts => l_cur_ctrl.manage_accounts,
806 x_acct_conv_flag => 'Y',
807 x_post_waiver_gl_flag => l_cur_ctrl.post_waiver_gl_flag,
808 x_waiver_notify_finaid_flag => l_cur_ctrl.waiver_notify_finaid_flag
809 );
810 -- Issuing commit here to update the account conversion flag to 'Y' even if the process completes with warning.
811 -- Issued explicit commit as the concurrent manager does not commit when the process completes with warning.
812 COMMIT;
813 EXCEPTION
814 WHEN OTHERS THEN
815 ROLLBACK;
816 retcode := 2;
817 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||SQLERRM;
818 igs_ge_msg_stack.conc_exception_hndl;
819
820 END updt_ftci_acct_info;
821
822 END igs_fi_acct_stp_cnv;
[Home] [Help]