1 PACKAGE BODY igs_fi_prc_acct_pkg AS
2 /* $Header: IGSFI60B.pls 120.5 2006/06/26 09:33:39 abshriva ship $ */
3
4 -- Global variables for the Package.
5 g_oracle_rec_installed igs_fi_control.rec_installed%TYPE;
6 g_n_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE;
7
8 TYPE accsegs IS RECORD(
9 l_segment igs_fi_acct_segs_v.segment%TYPE,
10 lv_Segment_num igs_fi_acct_segs_v.segment_num%TYPE,
11 lv_segment_value igs_ps_accts.segment_value%TYPE,
12 l_value_length fnd_flex_Value_Sets.maximum_size%TYPE);
13
14 TYPE accsegs_list IS TABLE OF accsegs;
15
16 g_accsegs accsegs_list := accsegs_list();
17 -- Profile for determining whether Nominated or Derived values are used
18 g_v_att_profile CONSTANT fnd_lookup_values.lookup_code%TYPE := FND_PROFILE.VALUE('IGS_FI_NOM_DER_VALUES');
19
20 PROCEDURE concat_seg_values(
21 p_err_string IN OUT NOCOPY VARCHAR2,
22 p_return_status OUT NOCOPY BOOLEAN
23 ) AS
24
25 /******************************************************************
26 Created By : rbezawad
27 Date Created By : 24-07-2001
28 Purpose : This procedure concatenates the Segment values
29 with the error string passed.
30
31 Known limitations,enhancements,remarks:
32 Change History
33 Who When What
34 ***************************************************************** */
35
36 -- Get the Delimiter that is set for the Segment Code Combinations
37 -- and use this for seperating the segments
38 l_v_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE := fnd_flex_ext.get_delimiter('SQLGL','GL#',g_n_coa_id);
39 l_v_null igs_lookup_values.meaning%TYPE;
40 BEGIN
41
42
43 l_v_null := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','NULL_VALUE');
44
45 -- Loop through the Segment values of the Table and concatenate
46 -- the error string with values. If segment value is NULL then
47 -- concatenate the segment value as NULL delimeted by '-'.
48
49 FOR i IN 1..g_accsegs.COUNT LOOP
50 IF g_accsegs(i).lv_segment_value IS NULL THEN
51 IF i = 1 THEN
52 p_err_string := SUBSTR(p_err_string||l_v_null,1,1000);
53 ELSE
54 p_err_string := SUBSTR(p_err_string||' '||l_v_delimiter||' '||l_v_null,1,1000);
55 END IF;
56 ELSE
57 IF i = 1 THEN
58 p_err_string := SUBSTR(p_err_string||' '||g_accsegs(i).lv_segment_value,1,1000);
59 ELSE
60 p_err_string := SUBSTR(p_err_string||' '||l_v_delimiter||' '||g_accsegs(i).lv_segment_value,1,1000);
61 END IF;
62 END IF;
63 END LOOP;
64
65 p_return_status := TRUE;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 p_return_status := FALSE;
70 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
71 fnd_message.set_token('NAME','CONCAT_SEG_VALUES: '||SQLERRM);
72 RETURN;
73 END concat_seg_values;
74
75 PROCEDURE get_charge_acct_ccids(
76 x_cr_gl_ccid OUT NOCOPY NUMBER,
77 p_error_type OUT NOCOPY NUMBER,
78 p_error_string OUT NOCOPY VARCHAR2,
79 p_return_status OUT NOCOPY BOOLEAN
80 ) AS
81
82 /*****************************************************************************
83 Created By : rbezawad
84 Date Created By : 24-07-2001
85 Purpose : This procedure returns the Revenue and
86 Receivables CCID's for a Charge Transactions.
87
88 Known limitations,enhancements,remarks:
89 Change History
90 Who When What
91 pathipat 20-Jun-2003 Bug: 3004932 - Set error type = 2 instead of 3 if
92 flexfield validation fails.
93 ******************************************************************************/
94
95 PRAGMA AUTONOMOUS_TRANSACTION;
96
97 --
98 --To get the No of Segements
99 --
100 CURSOR cur_n_seg(cp_v_enabled_flag VARCHAR2, cp_v_flex_cd VARCHAR2, cp_n_coa_id NUMBER)
101 IS
102 SELECT COUNT(*)
103 FROM fnd_id_flex_segments
104 WHERE id_flex_num = cp_n_coa_id
105 AND enabled_flag= cp_v_enabled_flag
106 AND id_flex_code = cp_v_flex_cd;
107
108 l_rev_gl_ccid NUMBER;
109 l_n_seg NUMBER; -- To hold the Number Segments defined for Flex field.
110 l_sv_list fnd_flex_ext.segmentarray;
111 BEGIN
112 OPEN cur_n_seg('Y','GL#',g_n_coa_id);
113 FETCH cur_n_seg INTO l_n_seg;
114 CLOSE cur_n_seg;
115
116 FOR i IN 1 .. g_accsegs.COUNT LOOP
117 l_sv_list(i) := g_accsegs(i).lv_segment_value;
118 END LOOP;
119 -- After the following funcion call, Revenue CCID is returned to x_cr_gl_ccid paramter which is a OUT NOCOPY paramter
120 IF (fnd_flex_ext.get_combination_id( application_short_name => 'SQLGL',
121 key_flex_code => 'GL#',
122 structure_number => g_n_coa_id,
123 validation_date => SYSDATE,
124 n_segments => l_n_seg,
125 segments => l_sv_list,
126 combination_id => l_rev_gl_ccid) = FALSE) THEN
127
128 --If the Passed Combination of Values is Invalid then ROLLBACK the Transaction and return.
129 p_error_string := fnd_flex_ext.get_message;
130 p_error_string := p_error_string||' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','SEGMENT_VALUE')||' ';
131 concat_seg_values(p_error_string,p_return_status);
132 IF (p_return_status = FALSE) THEN
133 --When and Unhandled Exception Occurs in Concat_seg_values then Return with Type 1 Error
134 p_error_type := 1;
135 p_error_string := SUBSTR(fnd_message.get,1,1000);
136 ROLLBACK;
137 RETURN;
138 END IF;
139 p_error_type := 2; -- Modified to error type 2 instead of 3 to maintain consistency
140 p_return_status := FALSE;
141 ROLLBACK;
142 RETURN;
143 ELSE
144 COMMIT; -- Commit's the transaction if a new Combination is inserted.
145 x_cr_gl_ccid := l_rev_gl_ccid; -- Revenue CCID is passed to OUT NOCOPY parameter
146 p_return_status := TRUE;
147 END IF;
148 EXCEPTION
149 WHEN OTHERS THEN
150 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
151 fnd_message.set_token('NAME','GET_CHARGE_ACCT_CCIDS: '||SQLERRM);
152 p_error_type := 1;
153 p_return_status := FALSE;
154 ROLLBACK;
155 RETURN;
156 END get_charge_acct_ccids;
157
158 PROCEDURE get_segment_values_list(
159 p_entity_type_code IN VARCHAR2,
160 p_fee_type IN VARCHAR2,
161 p_fee_cal_type IN VARCHAR2,
162 p_fee_ci_sequence_number IN NUMBER,
163 p_course_cd IN VARCHAR2,
164 p_course_version_number IN NUMBER,
165 p_org_unit_cd IN VARCHAR2,
166 p_org_start_dt IN DATE,
167 p_unit_cd IN VARCHAR2,
168 p_unit_version_number IN NUMBER,
169 p_uoo_id IN NUMBER,
170 p_location_cd IN VARCHAR2,
171 p_error_string OUT NOCOPY VARCHAR2,
172 p_return_status OUT NOCOPY BOOLEAN
173 ) AS
174
175 /**********************************************************************************************
176 Created By : rbezawad
177 Date Created By : 24-07-2001
178 Purpose : This procedure returns the the Segment Values List for the entity basing on
179 given p_entity_type_code value. This procedure also checks for an entity, whether
180 any of the segment values are NULL. If any of them are NULL then 1 is returned
181 to p_null_flag. If all the segment values are found then 0 is returned.
182 And if there is no Segment Data available in Account Tables then p_null_flag
183 will contain NULL.
184
185 Known limitations,enhancements,remarks:
186 Change History
187 Who When What
188 vchappid 19-May-2003 Build Bug# 2831572, Financial Accounting Enhancements,
189 variable p_seg_num_val is removed, update of the segments is directly done
190 Hence procedure update_seg_values is also removed since it is not required to update
191 the Global segments.
192 schodava 20-Sep-2002 Enh # 2564643 - Subaccount Removal
193 Removed references to subaccount.
194 *************************************************************************************************/
195
196 --
197 --Get the Segment Values for the Program Entity
198 --
199 CURSOR cur_ps_accts (cp_course_cd igs_ps_accts.course_cd%TYPE,
200 cp_version_number igs_ps_accts.version_number%TYPE)
201 IS
202 SELECT segment_num,segment_value
203 FROM igs_ps_accts
204 WHERE course_cd= cp_course_cd
205 AND version_number = cp_version_number;
206
207 --
208 --Get the Segment Values for the Unit Entity
209 --
210 CURSOR cur_ps_unit_accts (cp_unit_cd igs_ps_unit_accts.unit_cd%TYPE,
211 cp_version_number igs_ps_unit_accts.version_number%TYPE)
212 IS
213 SELECT segment_num,segment_value
214 FROM igs_ps_unit_accts
215 WHERE unit_cd= cp_unit_cd
216 AND version_number = cp_version_number;
217
218 --
219 --Get the Segment Values for the Unit Section Entity
220 --
221 CURSOR cur_ps_usec_accts (cp_uoo_id igs_ps_usec_accts.uoo_id%TYPE)
222 IS
223 SELECT segment_num,segment_value
224 FROM igs_ps_usec_accts
225 WHERE uoo_id = cp_uoo_id;
226
227 --
228 --Get the Segment Values for the Organization Entity
229 --
230 CURSOR cur_or_unit_accts (cp_org_unit_cd igs_or_unit_accts.org_unit_cd%TYPE,
231 cp_start_dt igs_or_unit_accts.start_dt%TYPE)
232 IS
233 SELECT segment_num,segment_value
234 FROM igs_or_unit_accts
235 WHERE org_unit_cd = cp_org_unit_cd
236 AND start_dt = cp_start_dt;
237
238 --
239 --Get the Segment Values for the Location Entity
240 --
241 CURSOR cur_ad_loc_accts (cp_location_cd igs_ad_loc_accts.location_cd%TYPE)
242 IS
243 SELECT segment_num,segment_value
244 FROM igs_ad_loc_accts
245 WHERE location_cd = cp_location_cd;
246
247 --
248 --Get the Segment Values for the Sub Account Entity
249 --
250 CURSOR cur_fi_sa_segments
251 IS
252 SELECT segment_num,segment_value
253 FROM igs_fi_sa_segments;
254
255 --
256 --Get the Segment Values for the Fee Type Entity
257 --
258 CURSOR cur_fi_f_type_accts (cp_fee_type igs_fi_f_type_accts.fee_type%TYPE,
259 cp_fee_cal_type igs_fi_f_type_accts.fee_cal_type%TYPE,
260 cp_fee_ci_sequence_number igs_fi_f_type_accts.fee_ci_sequence_number%TYPE)
261 IS
262 SELECT segment_num,segment_value
263 FROM igs_fi_f_type_accts
264 WHERE fee_type = cp_fee_type
265 AND fee_cal_type = cp_fee_cal_type
266 AND fee_ci_sequence_number = cp_fee_ci_sequence_number;
267
268 BEGIN
269
270 --Collect all Segment Values of the Entity Unit Section
271 IF (p_entity_type_code = 'USEC') THEN
272 FOR l_ps_usec_accts_rec IN cur_ps_usec_accts(p_uoo_id) LOOP
273 IF ((l_ps_usec_accts_rec.segment_value IS NOT NULL)
274 AND
275 (g_accsegs(l_ps_usec_accts_rec.segment_num).lv_segment_value) IS NULL) THEN
276 g_accsegs(l_ps_usec_accts_rec.segment_num).lv_segment_value := l_ps_usec_accts_rec.segment_value;
277 END IF;
278 END LOOP;
279
280 ELSIF (p_entity_type_code = 'LOC') THEN
281 FOR l_ad_loc_accts_rec IN cur_ad_loc_accts(p_location_cd) LOOP
282 IF ((l_ad_loc_accts_rec.segment_value IS NOT NULL)
283 AND
284 (g_accsegs(l_ad_loc_accts_rec.segment_num).lv_segment_value) IS NULL) THEN
285 g_accsegs(l_ad_loc_accts_rec.segment_num).lv_segment_value := l_ad_loc_accts_rec.segment_value;
286 END IF;
287 END LOOP;
288
289 ELSIF (p_entity_type_code = 'FTCI') THEN
290 FOR l_fi_f_type_accts_rec IN cur_fi_f_type_accts(p_fee_type, p_fee_cal_type, p_fee_ci_sequence_number) LOOP
291 IF ((l_fi_f_type_accts_rec.segment_value IS NOT NULL)
292 AND
293 (g_accsegs(l_fi_f_type_accts_rec.segment_num).lv_segment_value) IS NULL) THEN
294 g_accsegs(l_fi_f_type_accts_rec.segment_num).lv_segment_value := l_fi_f_type_accts_rec.segment_value;
295 END IF;
296 END LOOP;
297 ELSIF (p_entity_type_code = 'SA') THEN
298 FOR l_fi_sa_segments_rec IN cur_fi_sa_segments LOOP
299 IF ((l_fi_sa_segments_rec.segment_value IS NOT NULL)
300 AND
301 (g_accsegs(l_fi_sa_segments_rec.segment_num).lv_segment_value) IS NULL) THEN
302 g_accsegs(l_fi_sa_segments_rec.segment_num).lv_segment_value := l_fi_sa_segments_rec.segment_value;
303 END IF;
304 END LOOP;
305 ELSIF (p_entity_type_code = 'PS') THEN
306 FOR l_ps_accts_rec IN cur_ps_accts(p_course_cd, p_course_version_number) LOOP
307 IF ((l_ps_accts_rec.segment_value IS NOT NULL)
308 AND
309 (g_accsegs(l_ps_accts_rec.segment_num).lv_segment_value) IS NULL) THEN
310 g_accsegs(l_ps_accts_rec.segment_num).lv_segment_value := l_ps_accts_rec.segment_value;
311 END IF;
312 END LOOP;
313 ELSIF (p_entity_type_code = 'ORG') THEN
314 FOR l_or_unit_accts_rec IN cur_or_unit_accts(p_org_unit_cd, p_org_start_dt) LOOP
315 IF ((l_or_unit_accts_rec.segment_value IS NOT NULL)
316 AND
317 (g_accsegs(l_or_unit_accts_rec.segment_num).lv_segment_value) IS NULL) THEN
318 g_accsegs(l_or_unit_accts_rec.segment_num).lv_segment_value := l_or_unit_accts_rec.segment_value;
319 END IF;
320 END LOOP;
321 ELSIF (p_entity_type_code = 'UNIT') THEN
322 FOR l_ps_unit_accts_rec IN cur_ps_unit_accts(p_unit_cd, p_unit_version_number) LOOP
323 IF ((l_ps_unit_accts_rec.segment_value IS NOT NULL)
324 AND
325 (g_accsegs(l_ps_unit_accts_rec.segment_num).lv_segment_value) IS NULL) THEN
326 g_accsegs(l_ps_unit_accts_rec.segment_num).lv_segment_value := l_ps_unit_accts_rec.segment_value;
327 END IF;
328 END LOOP;
329 END IF;
330 p_return_status := TRUE;
331 EXCEPTION
332 WHEN OTHERS THEN
333 p_return_status := FALSE;
334 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
335 fnd_message.set_token('NAME','GET_SEGMENT_VALUES_LIST: '||SQLERRM);
336 RETURN;
337 END get_segment_values_list;
338
339 PROCEDURE get_natural_account(p_v_fee_type VARCHAR2,
340 p_v_fee_cal_type VARCHAR2,
341 p_n_fee_ci_seq_num NUMBER,
342 p_v_location_cd VARCHAR2,
343 p_v_att_type VARCHAR2,
344 p_v_att_mode VARCHAR2,
345 p_v_course_cd VARCHAR2,
346 p_n_crs_ver_num NUMBER,
347 p_v_unit_cd VARCHAR2,
348 p_n_unit_ver_num NUMBER,
349 p_v_org_unit_cd VARCHAR2,
350 p_v_residency_status_cd VARCHAR2,
351 p_n_uoo_id NUMBER,
352 p_v_nat_acc_seg OUT NOCOPY VARCHAR2,
353 p_v_account_cd OUT NOCOPY VARCHAR2,
354 p_n_err_type OUT NOCOPY NUMBER,
355 p_v_err_msg OUT NOCOPY VARCHAR2,
356 p_b_status OUT NOCOPY BOOLEAN,
357 p_n_unit_type_id IN NUMBER,
358 p_v_unit_class IN VARCHAR2,
359 p_v_unit_mode IN VARCHAR2,
360 p_v_unit_level IN VARCHAR2
361 )
362 AS
363 /******************************************************************
364 Created By : vchappid
365 Date Created By : 16-May-2003
366 Purpose : This is the local procedure for identifying the natural account segment,
367 reveue account code for the input attributes.
368
369 Known limitations,enhancements,remarks:
370 Change History
371 Who When What
372 abshriva 19-JUN-2006 Bug#5104329:Added cursor cur_get_att_mode
373 bannamal 03-JUN-2005 Bug#3442712 Unit Level Fee Assessment Build
374 Added new parameters for this build.
375 ***************************************************************** */
376
377 CURSOR cur_ftci_accts (cp_v_fee_type VARCHAR2,
378 cp_v_fee_cal_type VARCHAR2,
379 cp_n_sequence_number NUMBER)
380 IS
381 SELECT *
382 FROM igs_fi_ftci_accts
383 WHERE fee_type = cp_v_fee_type
384 AND fee_cal_type = cp_v_fee_cal_type
385 AND fee_ci_sequence_number = cp_n_sequence_number
386 ORDER BY order_sequence;
387
388 CURSOR cur_get_att_mode( p_att_mode igs_en_atd_mode_all.attendance_mode%TYPE)
389 IS
390 SELECT am.govt_attendance_mode
391 FROM igs_en_atd_mode_all am
392 WHERE am.attendance_mode=p_att_mode;
393
394 l_cur_ftci_accts cur_ftci_accts%ROWTYPE;
395 l_n_rec_exists NUMBER :=0;
396 l_b_flag BOOLEAN := FALSE;
397 l_b_rec_matchs BOOLEAN := FALSE;
398 l_v_natural_account_segment igs_fi_ftci_accts.natural_account_segment%TYPE;
399 l_v_rev_account_cd igs_fi_ftci_accts.rev_account_cd%TYPE;
400 cst_nominated CONSTANT igs_lookups_view.lookup_code%TYPE := 'NOMINATED';
401 cst_derived CONSTANT igs_lookups_view.lookup_code%TYPE := 'DERIVED';
402 l_v_att_mode igs_en_atd_mode_all.attendance_mode%TYPE;
403 BEGIN
404
405 FOR l_rec_ftci_accts IN cur_ftci_accts(p_v_fee_type,p_v_fee_cal_type,p_n_fee_ci_seq_num) LOOP
406 l_b_flag := TRUE;
407
408 -- Initialize the record count indicator to the number of records fetched by the cursor
409 -- when uer has not setup any accounting information then the record count will be 0
410 l_n_rec_exists := cur_ftci_accts%ROWCOUNT;
411
412 -- Match the Org Unit Code attribute when it is found that the Fee Type Account Record has a value
413 -- for the Org Unit Code column.
414 IF (l_rec_ftci_accts.org_unit_cd IS NOT NULL) THEN
415 IF (l_rec_ftci_accts.org_unit_cd <> p_v_org_unit_cd OR p_v_org_unit_cd IS NULL) THEN
416 l_b_flag := FALSE;
417 END IF;
418 END IF;
419
420 -- Match the Location Code attribute when it is found that the Fee Type Account Record has a value
421 -- for the Location Code column.
422 IF (l_rec_ftci_accts.location_cd IS NOT NULL) AND l_b_flag THEN
423 IF (l_rec_ftci_accts.location_cd <> p_v_location_cd OR p_v_location_cd IS NULL) THEN
424 l_b_flag := FALSE;
425 END IF;
426 END IF;
427
428 -- Match the Unit Section attribute when it is found that the Fee Type Account Record has a value
429 -- for the Unit Section column.
430 IF (l_rec_ftci_accts.uoo_id IS NOT NULL) AND l_b_flag THEN
431 IF (l_rec_ftci_accts.uoo_id <> p_n_uoo_id OR p_n_uoo_id IS NULL) THEN
432 l_b_flag := FALSE;
433 END IF;
434 END IF;
435
436 -- Match the Unit Code attribute when it is found that the Fee Type Account Record has a value
437 -- for the Unit Code column.
438 IF (l_rec_ftci_accts.unit_cd IS NOT NULL AND l_rec_ftci_accts.unit_version_number IS NOT NULL) AND l_b_flag THEN
439 IF ((l_rec_ftci_accts.unit_cd <> p_v_unit_cd) OR p_v_unit_cd IS NULL) THEN
440 l_b_flag := FALSE;
441 ELSIF ((l_rec_ftci_accts.unit_version_number <> p_n_unit_ver_num ) OR (p_n_unit_ver_num IS NULL )) THEN
442 l_b_flag := FALSE;
443 END IF;
444 END IF;
445
446 -- Match the Course Code attribute when it is found that the Fee Type Account Record has a value
447 -- for the Course Code column.
448 IF (l_rec_ftci_accts.course_cd IS NOT NULL AND l_rec_ftci_accts.crs_version_number IS NOT NULL) AND l_b_flag THEN
449 IF ((l_rec_ftci_accts.course_cd <> p_v_course_cd) OR p_v_course_cd IS NULL) THEN
450 l_b_flag := FALSE;
451 ELSIF ((l_rec_ftci_accts.crs_version_number <> p_n_crs_ver_num ) OR (p_n_crs_ver_num IS NULL )) THEN
452 l_b_flag := FALSE;
453 END IF;
454 END IF;
455
456 -- Match the Residency Status attribute when it is found that the Fee Type Account Record has a value
457 -- for the Residency Status column.
458 IF (l_rec_ftci_accts.residency_status_cd IS NOT NULL) AND l_b_flag THEN
459 IF (l_rec_ftci_accts.residency_status_cd <> p_v_residency_status_cd OR p_v_residency_status_cd IS NULL) THEN
460 l_b_flag := FALSE;
461 END IF;
462 END IF;
463
464 -- Match the Attendance Type attribute when it is found that the Fee Type Account Record has a value
465 -- for the Attendance Type column.
466 IF (l_rec_ftci_accts.attendance_type IS NOT NULL) AND l_b_flag THEN
467 IF (l_rec_ftci_accts.attendance_type <> p_v_att_type OR p_v_att_type IS NULL) THEN
468 l_b_flag := FALSE;
469 END IF;
470 END IF;
471
472 -- Match the Attendance Mode attribute when it is found that the Fee Type Account Record has a value
473 -- for the Attendance Mode column.
474 IF (l_rec_ftci_accts.attendance_mode IS NOT NULL) AND l_b_flag THEN
475 IF (g_v_att_profile=cst_nominated) THEN
476 IF (l_rec_ftci_accts.attendance_mode <> p_v_att_mode OR p_v_att_mode IS NULL) THEN
477 l_b_flag := FALSE;
478 END IF;
479 END IF;
480 IF (g_v_att_profile=cst_derived) THEN
481 OPEN cur_get_att_mode(l_rec_ftci_accts.attendance_mode);
482 FETCH cur_get_att_mode INTO l_v_att_mode;
483 CLOSE cur_get_att_mode;
484 IF ( l_v_att_mode <> p_v_att_mode OR p_v_att_mode IS NULL) THEN
485 l_b_flag := FALSE;
486 END IF;
487 END IF;
488 END IF;
489
490 -- Match the Unit Program Type Level attribute when it is found that the Fee Type Account Record has a value
491 -- for the Unit Program Type Level column.
492 IF (l_rec_ftci_accts.unit_type_id IS NOT NULL) AND l_b_flag THEN
493 IF (l_rec_ftci_accts.unit_type_id <> p_n_unit_type_id OR p_n_unit_type_id IS NULL) THEN
494 l_b_flag := FALSE;
495 END IF;
496 END IF;
497
498 -- Match the Unit Class attribute when it is found that the Fee Type Account Record has a value
499 -- for the Unit Class column.
500 IF (l_rec_ftci_accts.unit_class IS NOT NULL) AND l_b_flag THEN
501 IF (l_rec_ftci_accts.unit_class <> p_v_unit_class OR p_v_unit_class IS NULL) THEN
502 l_b_flag := FALSE;
503 END IF;
504 END IF;
505
506 -- Match the Unit Mode attribute when it is found that the Fee Type Account Record has a value
507 -- for the Unit Mode column.
508 IF (l_rec_ftci_accts.unit_mode IS NOT NULL) AND l_b_flag THEN
509 IF (l_rec_ftci_accts.unit_mode <> p_v_unit_mode OR p_v_unit_mode IS NULL) THEN
510 l_b_flag := FALSE;
511 END IF;
512 END IF;
513
514 -- Match the Unit Level attribute when it is found that the Fee Type Account Record has a value
515 -- for the Unit Level column.
516 IF (l_rec_ftci_accts.unit_level IS NOT NULL) AND l_b_flag THEN
517 IF (l_rec_ftci_accts.unit_level <> p_v_unit_level OR p_v_unit_level IS NULL) THEN
518 l_b_flag := FALSE;
519 END IF;
520 END IF;
521
522 -- If the record matches then assign the natural account segment, revenue account code and the exit the loop
523 IF l_b_flag THEN
524 l_b_rec_matchs := TRUE;
525 l_v_natural_account_segment := l_rec_ftci_accts.natural_account_segment;
526 l_v_rev_account_cd := l_rec_ftci_accts.rev_account_cd;
527 EXIT;
528 END IF;
529 END LOOP;
530
531 -- No records exists in the Fee Type Accounts table for the Fee Type, Fee Period assign FALSE to the variable
532 -- and exit of the procedure
533 IF l_n_rec_exists = 0 THEN
534 p_v_nat_acc_seg := NULL;
535 p_v_account_cd := NULL;
536 p_n_err_type := 0;
537 p_v_err_msg := NULL;
538 p_b_status := FALSE;
539 ELSE
540 -- When there are records existing and if the attributes matches then assign the natural account segment,
541 -- revenue account code to the variables and exit from the procedure
542 IF l_b_rec_matchs THEN
543 --records exists and matches
544 p_v_nat_acc_seg := l_v_natural_account_segment;
545 p_v_account_cd := l_v_rev_account_cd;
546 p_n_err_type := 0;
547 p_v_err_msg := NULL;
548 p_b_status := TRUE;
549 ELSE
550 -- When there are records existing and if the attributes doesnot match
551 -- assign FALSE to the variable and exit the procedure
552 p_v_nat_acc_seg := NULL;
553 p_v_account_cd := NULL;
554 p_n_err_type := 1;
555 p_v_err_msg := 'IGS_FI_NAT_ACC_SEG_NOTDERIVED';
556 p_b_status := FALSE;
557 END IF;
558 END IF;
559 END get_natural_account;
560
561 PROCEDURE build_accounts(
562 p_fee_type IN VARCHAR2,
563 p_fee_cal_type IN VARCHAR2,
564 p_fee_ci_sequence_number IN NUMBER,
565 p_course_cd IN VARCHAR2,
566 p_course_version_number IN NUMBER,
567 p_org_unit_cd IN VARCHAR2,
568 p_org_start_dt IN DATE,
569 p_unit_cd IN VARCHAR2,
570 p_unit_version_number IN NUMBER,
571 p_uoo_id IN NUMBER,
572 p_location_cd IN VARCHAR2,
573 p_transaction_type IN VARCHAR2,
574 p_credit_type_id IN NUMBER,
575 p_source_transaction_id IN NUMBER,
576 x_dr_gl_ccid IN OUT NOCOPY NUMBER,
577 x_cr_gl_ccid IN OUT NOCOPY NUMBER,
578 x_dr_account_cd IN OUT NOCOPY VARCHAR2,
579 x_cr_account_cd IN OUT NOCOPY VARCHAR2,
580 x_err_type OUT NOCOPY NUMBER,
581 x_err_string OUT NOCOPY VARCHAR2,
582 x_ret_status OUT NOCOPY BOOLEAN,
583 p_v_attendance_type IN VARCHAR2,
584 p_v_attendance_mode IN VARCHAR2,
585 p_v_residency_status_cd IN VARCHAR2,
586 p_n_unit_type_id IN NUMBER,
587 p_v_unit_class IN VARCHAR2,
588 p_v_unit_mode IN VARCHAR2,
589 p_v_unit_level IN VARCHAR2,
590 p_v_waiver_name IN VARCHAR2
591 ) IS
592
593 /*******************************************************************************
594 Created by : rbezawad
595 Date created: 19-Jul-2001
596
597 Purpose: This procedure generates debit and credit account pairs for
598 Charge and Credit Transactions.
599
600 Known limitations/enhancements/remarks:
601 1) The generated Debit and Credit Account pairs will be passed to
602 x_dr_gl_ccid, x_cr_gl_ccid, x_dr_account_cd, x_cr_account_cd
603 variables.
604 2) If any Error Occurs then x_err_type, x_err_string will be set and
605 x_ret_status will be set to FALSE. x_err_type will be set to one of
606 the values 1 to 3.
607 3) If Error Type is 1 then the calling calling programs should not
608 proceed further and the transaction should not be complete.
609 4) If Error Type is 2 or 3 then calling program should proceed further
610 and transaction shuld be complete. These kind of errors would be
611 allowed for User Correction in Charges History Form.
612 5) If Build Account Process Runs successfully then it returns
613 TRUE to x_ret_status.
614 6) In this procedure, for getting Revenue CCID for a charge transaction
615 traversing through entities is done. To Identify the Entity,
616 Lookups table(IGS_FI_ACCT_ENTITIES Lookup Type) data is used.
617
618 Change History: (who, when, what: NO CREATION RECORDS HERE!)
619 Who When What
620 bannamal 03-JUN-2005 Bug#3442712 Unit Level Fee Assessment Build
621 Added new parameters for this build.
622 uudayapr 08-mar-2004 Bug# 3478599,added 'Document' also added as valid system fee type to raise
623 Error(IGS_FI_SRC_TXN_ACC_INV) when revenue account segment value cannot
624 be derived.
625 shtatiko 13-DEC-2003 Bug# 3288973, Added code to handle transaction type of RETENTION.
626 uudayapr 16-oct-2003 Enh# 3117341 Modified as a part of AUDIT AND SPECIAL FEES Build.
627 vchappid 16-May-2003 Bug# 2831572, Modified the Build Account Process as per the Financial Accounting
628 Build.
629 shtatiko 20-JAN-2003 Bug# 2739054, Modified code so that it won't return back if receivables information
630 is not found and if the system fee type is EXTERNAL.
631 vchappid 07-Jan-2003 Bug#2737685, For a system fee type of Ancillary, user should not be allowed
632 to create any charge with error account as 'Y'.
633 schodava 20-Sep-2002 Enh # 2564643 - Subaccount Removal
634 Removed references to subaccount.
635 agairola 17-May-2002 Following modifications were done for the bug
636 2323555.
637 1. Modified the cursor cur_f_typ_ca_inst_lkp to fetch
638 the Account Codes and the receivables Account ccid
639 and removed the redundant cursors which were written
640 to fetch individual values.
641 2. Handled the code to validate if the particular value
642 is passed for the account, then the required processing
643 should not be done for getting the particular account.
644 For e.g. if the Revenue Account is passed, then the
645 processing is not done for getting the Revenue Account
646 and only receivable account is derived.
647 agairola 12-May-2002 Added the code for deleting the g_accsegs PL/SQL
648 table as part of bug 2366070
649 agairola 30-Apr-2002 Modified the cursor call for cur_ftci_rec_acct_cd
650
651 *******************************************************************************/
652
653 -- Local variables for the Procedure Build_Accounts.
654
655 --
656 --Get the CCID's, Account Codes for a Credit Type Transaction.
657 --
658 CURSOR cur_cr_types (cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE)
659 IS
660 SELECT dr_gl_ccid,
661 cr_gl_ccid,
662 dr_account_cd,
663 cr_account_cd
664 FROM igs_fi_cr_types
665 WHERE credit_type_id = cp_credit_type_id;
666
667 l_cr_types_rec cur_cr_types%ROWTYPE;
668
669 --
670 --Get the Account Hierachy ID.
671 --
672 CURSOR cur_f_typ_ca_inst_lkp (cp_fee_type igs_fi_f_typ_ca_inst_lkp_v.fee_type%TYPE,
673 cp_fee_cal_type igs_fi_f_typ_ca_inst_lkp_v.fee_cal_type%TYPE,
674 cp_fee_ci_sequence_number igs_fi_f_typ_ca_inst_lkp_v.fee_ci_sequence_number%TYPE)
675 IS
676 SELECT acct_hier_id,
677 rec_account_cd,
678 rec_gl_ccid,
679 rev_account_cd,
680 ret_gl_ccid,
681 ret_account_cd
682 FROM igs_fi_f_typ_ca_inst
683 WHERE fee_type = cp_fee_type
684 AND fee_cal_type = cp_fee_cal_type
685 AND fee_ci_sequence_number = cp_fee_ci_sequence_number;
686
687 l_f_typ_ca_inst_lkp_rec cur_f_typ_ca_inst_lkp%ROWTYPE;
688
689 --
690 --Get the Zero_fill flag defined for the Account Hierarchy ID
691 --
692 CURSOR cur_hier_accounts (cp_acct_hier_id igs_fi_hier_accounts.acct_hier_id%TYPE)
693 IS
694 SELECT zero_fill_flag
695 FROM igs_fi_hier_accounts
696 WHERE acct_hier_id = cp_acct_hier_id;
697
698 l_hier_accounts_rec cur_hier_accounts%ROWTYPE;
699
700 --
701 --Get the Account Hierarchy Order Details for Entities Associated with given Account Hierarchy ID
702 --
703 CURSOR cur_hier_acct_tbl (cp_acct_hier_id igs_fi_hier_accounts.acct_hier_id%TYPE)
704 IS
705 SELECT acct_tbl_id,
706 order_sequence,
707 entity_type_code
708 FROM igs_fi_hier_acct_tbl
709 WHERE acct_hier_id = cp_acct_hier_id
710 ORDER BY order_sequence;
711
712 --This cursor added as a part of bug#2410396
713 CURSOR cur_sys_opt
714 IS
715 SELECT rec_account_cd,
716 rec_gl_ccid
717 FROM igs_fi_control;
718 l_cur_sys_opt cur_sys_opt%ROWTYPE;
719
720 CURSOR cur_acc_segs
721 IS
722 SELECT seg.segment,seg.segment_num,val.maximum_size maximum_size
723 FROM igs_fi_acct_segs_v seg,
724 fnd_flex_value_sets val
725 WHERE seg.flex_value_set_id = val.flex_value_set_id
726 ORDER BY segment_num;
727
728 -- cursor for selecting system fee type attached to the fee type that is passed as input to this procedure
729 CURSOR c_sys_fee_type (cp_fee_type IN igs_fi_fee_type.fee_type%TYPE)
730 IS
731 SELECT s_fee_type
732 FROM igs_fi_fee_type
733 WHERE fee_type = cp_fee_type;
734 l_v_s_fee_type igs_fi_fee_type.s_fee_type%TYPE;
735
736 -- cursor for getting the Natural Account Segment for Application, Chart OF Accounts and
737 -- Segment Attribute Type of GL_ACCOUNT
738 CURSOR cur_seg_num (cp_n_coa_id NUMBER,
739 cp_n_appl_id NUMBER,
740 cp_v_attr_type VARCHAR2,
741 cp_v_attr_value VARCHAR2)
742 IS
743 SELECT application_column_name segment_name
744 FROM fnd_segment_attribute_values
745 WHERE id_flex_num = cp_n_coa_id
746 AND application_id = cp_n_appl_id
747 AND segment_attribute_type = cp_v_attr_type
748 AND attribute_value = cp_v_attr_value;
749 l_v_seg_name fnd_id_flex_segments.application_column_name%TYPE;
750
751 l_return_status BOOLEAN; -- To capture returned Status from get_charge_acct_ccids procedure call.
752 l_error_string igs_fi_invln_int.error_string%TYPE; -- To capture returned error string from get_charge_acct_ccids procedure call.
753 l_error_type NUMBER; -- To capture returned error type from get_charge_acct_ccids procedure call.
754 l_error_type_cr NUMBER;
755 l_error_string_cr igs_fi_invln_int.error_string%TYPE;
756 l_return_status_cr BOOLEAN;
757
758
759 l_rev_account_cd igs_fi_control.rev_account_cd%TYPE; -- To hold Revenue Account Code.
760 l_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE;
761 l_entity_no NUMBER;
762 l_b_err_type1 BOOLEAN := FALSE;
763
764 l_v_nat_acct_seg igs_fi_ftci_accts.natural_account_segment%TYPE;
765 l_v_account_cd igs_fi_acc.account_cd%TYPE;
766 l_n_err_type NUMBER;
767 l_v_err_msg fnd_new_messages.message_name%TYPE;
768 l_b_nat_rec BOOLEAN;
769
770 --Bug#3392095 - Tuition Waiver, Cursor to obtain the waiver program attributes for the fee, fee cal types combination.
771 CURSOR cur_get_waiver_attr(cp_v_fee_type igs_fi_inv_int_all.fee_type%TYPE,
772 cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
773 cp_n_fee_ci_sequence_number igs_fi_inv_int_all.fee_ci_sequence_number%TYPE,
774 cp_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE)
775 IS
776 SELECT fwp.fee_cal_type,
777 fwp.fee_ci_sequence_number,
778 fwp.waiver_name,
779 fwp.credit_type_id,
780 fwp.target_fee_type
781 FROM igs_fi_waiver_pgms fwp
782 WHERE fwp.adjustment_fee_type = cp_v_fee_type
783 AND fwp.fee_cal_type = cp_v_fee_cal_type
784 AND fwp.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
785 AND fwp.waiver_name = cp_v_waiver_name;
786
787 l_cur_get_waiver_attr cur_get_waiver_attr%ROWTYPE;
788
789 l_cur_cr_types_rec1 cur_cr_types%ROWTYPE;
790
791 BEGIN
792
793 -- To know whether Oracle Receivables Installed in the System or Not.
794 g_oracle_rec_installed := igs_fi_gen_005.finp_get_receivables_inst;
795 g_accsegs.DELETE;
796
797 x_ret_status := TRUE;
798
799 -- If the values are passed to the Build Account process for the accounts,
800 -- return.
801 IF ((x_dr_gl_ccid IS NOT NULL AND x_cr_gl_ccid IS NOT NULL) OR
802 (x_dr_account_cd IS NOT NULL AND x_cr_account_cd IS NOT NULL)) THEN
803 x_ret_status := TRUE;
804 RETURN;
805 END IF;
806
807 IF (p_transaction_type = 'CREDIT') THEN
808 -- If Transaction is a Credit Transaction
809
810 OPEN cur_cr_types(p_credit_type_id);
811 FETCH cur_cr_types INTO l_cr_types_rec;
812
813 IF (cur_cr_types%FOUND) THEN
814 IF (g_oracle_rec_installed = 'Y') THEN
815 -- if the Oracle Receivables is Installed then return Credit and Debit CCID's
816 x_dr_gl_ccid := l_cr_types_rec.dr_gl_ccid;
817 x_cr_gl_ccid := l_cr_types_rec.cr_gl_ccid;
818 x_dr_account_cd := NULL;
819 x_cr_account_cd := NULL;
820 ELSE
821 -- if the Oracle Receivables is Installed then return Credit and Debit Account Codes
822 x_dr_account_cd := l_cr_types_rec.dr_account_cd;
823 x_cr_account_cd := l_cr_types_rec.cr_account_cd;
824 x_dr_gl_ccid := NULL;
825 x_cr_gl_ccid := NULL;
826 END IF;
827 CLOSE cur_cr_types;
828 x_ret_status := TRUE;
829 RETURN;
830 ELSE
831 -- If there is no Account Record found for the Credit Type and Sub Account arguments then raise error and Exit.
832 CLOSE cur_cr_types;
833 x_err_string := 'IGS_FI_CR_TYPE_NO_ACCT';
834 x_err_type := 1;
835 x_ret_status := FALSE;
836 RETURN;
837 END IF;
838
839 ELSIF (p_transaction_type IN ('CHARGE', 'RETENTION')) THEN
840 -- If Transaction is a Charge Transaction
841 -- get the system fee type attached to the fee type that is passed as input to this procedure.
842 OPEN c_sys_fee_type(p_fee_type);
843 FETCH c_sys_fee_type INTO l_v_s_fee_type;
844 CLOSE c_sys_fee_type;
845
846 OPEN cur_f_typ_ca_inst_lkp(p_fee_type, p_fee_cal_type, p_fee_ci_sequence_number);
847 FETCH cur_f_typ_ca_inst_lkp INTO l_f_typ_ca_inst_lkp_rec;
848 IF ((cur_f_typ_ca_inst_lkp%NOTFOUND) AND (g_oracle_rec_installed = 'Y')) THEN
849 CLOSE cur_f_typ_ca_inst_lkp;
850 x_err_string := 'IGS_FI_NO_ACCT_HIER_FTCI';
851 x_err_type := 1;
852 x_ret_status := FALSE;
853 RETURN;
854 ELSE
855 CLOSE cur_f_typ_ca_inst_lkp;
856
857 --Bug #3392095 - Tuition Waiver. Check user has selected TRANSACTION TYPE as Waiver Adjustment
858 IF l_v_s_fee_type = 'WAIVER_ADJ' THEN
859 --Obtain the waiver program attributes for the combination of fee type, fee cal type, fee ci seq number and waiver name
860 --passed as inbound paramter to this procedure.
861 OPEN cur_get_waiver_attr(p_fee_type,p_fee_cal_type, p_fee_ci_sequence_number, p_v_waiver_name);
862 FETCH cur_get_waiver_attr INTO l_cur_get_waiver_attr;
863 IF cur_get_waiver_attr%NOTFOUND THEN
864 CLOSE cur_get_waiver_attr;
865 x_err_string := 'IGS_FI_WAV_PGM_NO_REC_FOUND';
866 x_err_type := 1;
867 x_ret_status := FALSE;
868 RETURN;
869 END IF;
870 CLOSE cur_get_waiver_attr;
871
872 --Obtain the accounting information for the waiver credit type id.
873 OPEN cur_cr_types(l_cur_get_waiver_attr.credit_type_id);
874 FETCH cur_cr_types INTO l_cur_cr_types_rec1;
875 IF cur_cr_types%NOTFOUND THEN
876 CLOSE cur_cr_types;
877 x_err_string := 'IGS_FI_CR_TYPE_NO_ACCT';
878 x_err_type := 1;
879 x_ret_status := FALSE;
880 RETURN;
881 END IF;
882 CLOSE cur_cr_types;
883 END IF;
884
885 IF (g_oracle_rec_installed = 'Y') THEN
886 --If Account Hierarchy ID found at FTCI Level then Get all the Account Hierarchy details.
887 OPEN cur_hier_accounts(l_f_typ_ca_inst_lkp_rec.acct_hier_id);
888 FETCH cur_hier_accounts INTO l_hier_accounts_rec;
889 CLOSE cur_hier_accounts;
890
891 --If Recievables CCID's found and pass to OUT NOCOPY parameters else raise Type 1 Error
892 x_dr_gl_ccid := NVL(x_dr_gl_ccid, l_f_typ_ca_inst_lkp_rec.rec_gl_ccid);
893
894 --Bug#2410396,if receivables ccid is not found at ftci level then fetch it from system options level
895 IF x_dr_gl_ccid IS NULL THEN
896 OPEN cur_sys_opt;
897 FETCH cur_sys_opt INTO l_cur_sys_opt;
898 CLOSE cur_sys_opt;
899 x_dr_gl_ccid:=l_cur_sys_opt.rec_gl_ccid;
900 END IF;
901
902 IF x_dr_gl_ccid IS NULL THEN
903 x_err_string := 'IGS_FI_NO_REC_ACCT_CD_FTCI';
904 x_err_type := 1;
905 x_ret_status := FALSE;
906
907 -- We do not return from procedure if the system fee type is EXTERNAL.
908 -- Continue to check whether revenue account setup is complete or not.
909 -- But finally after checking the revenue account setup return with x_err_type = 1
910 -- This has been done as part of Bug# 2739054
911 IF l_v_s_fee_type = 'EXTERNAL' THEN
912 l_b_err_type1 := TRUE;
913 ELSE
914 RETURN;
915 END IF;
916 END IF;
917
918 --Bug#3392095 - Tution Waiver - Check user has selected TRANSACTION TYPE as Waiver Adjustment
919 IF l_v_s_fee_type = 'WAIVER_ADJ' THEN
920 --check whether GL debit account code for credit types record found exists or not.
921 --accordingly, set the message to x_err_string var. and return back to the calling procedure.
922 IF l_cur_cr_types_rec1.dr_gl_ccid IS NULL THEN
923 x_err_string := 'IGS_FI_CR_TYPE_NO_ACCT';
924 x_err_type := 1;
925 x_ret_status := FALSE;
926 RETURN;
927 END IF;
928 x_cr_gl_ccid := l_cur_cr_types_rec1.dr_gl_ccid;
929 RETURN;
930 END IF;
931
932 -- For RETENTION Charges retention account information is mandatory
933 IF p_transaction_type = 'RETENTION' THEN
934 x_cr_gl_ccid := NVL(x_cr_gl_ccid, l_f_typ_ca_inst_lkp_rec.ret_gl_ccid);
935 IF x_cr_gl_ccid IS NULL THEN
936 x_err_string := 'IGS_FI_NO_RETENTION_ACC';
937 x_err_type := 1;
938 x_ret_status := FALSE;
939 RETURN;
940 END IF;
941 END IF;
942
943 --Check if Chart Of Accounts ID available or not.
944 g_n_coa_id := igs_fi_gen_007.get_coa_id;
945 IF (g_n_coa_id IS NULL) THEN
946 x_err_string := 'IGS_FI_NO_COA_ID';
947 x_err_type := 1;
948 x_ret_status := FALSE;
949 RETURN;
950 END IF;
951
952 IF (x_cr_gl_ccid IS NULL) THEN
953 OPEN cur_seg_num (g_n_coa_id,101,'GL_ACCOUNT','Y');
954 FETCH cur_seg_num INTO l_v_seg_name;
955 CLOSE cur_seg_num;
956 --Enh #3117341 Added 'AUDIT' also as a valid value for system fee type with
957 -- the Existing 'TUTNFEE','OTHER'
958 IF (l_v_s_fee_type IN ('TUTNFEE','OTHER','AUDIT')) THEN
959 get_natural_account( p_v_fee_type => p_fee_type,
960 p_v_fee_cal_type => p_fee_cal_type,
961 p_n_fee_ci_seq_num => p_fee_ci_sequence_number,
962 p_v_location_cd => p_location_cd,
963 p_v_att_type => p_v_attendance_type,
964 p_v_att_mode => p_v_attendance_mode,
965 p_v_course_cd => p_course_cd ,
966 p_n_crs_ver_num => p_course_version_number,
967 p_v_unit_cd => p_unit_cd,
968 p_n_unit_ver_num => p_unit_version_number,
969 p_v_org_unit_cd => p_org_unit_cd,
970 p_v_residency_status_cd => p_v_residency_status_cd,
971 p_n_uoo_id => p_uoo_id,
972 p_v_nat_acc_seg => l_v_nat_acct_seg,
973 p_v_account_cd => l_v_account_cd,
974 p_n_err_type => l_n_err_type,
975 p_v_err_msg => l_v_err_msg,
976 p_b_status => l_b_nat_rec,
977 p_n_unit_type_id => p_n_unit_type_id,
978 p_v_unit_class => p_v_unit_class,
979 p_v_unit_mode => p_v_unit_mode,
980 p_v_unit_level => p_v_unit_level
981 );
982
983 IF (l_b_nat_rec = FALSE) AND (l_n_err_type = 1) THEN
984 x_cr_gl_ccid := NULL;
985 x_err_type :=2;
986 x_err_string := fnd_message.get_string('IGS',l_v_err_msg);
987 x_ret_status := FALSE;
988 RETURN;
989 END IF;
990 END IF;
991
992 -- To initialize the Account Segments Values Global Variable.
993 FOR l_cur_acc_segs IN cur_Acc_segs LOOP
994 g_accsegs.EXTEND;
995 g_accsegs(g_accsegs.COUNT).l_segment := l_cur_acc_Segs.segment;
996 g_accsegs(g_accsegs.COUNT).lv_segment_num := l_cur_acc_Segs.segment_num;
997 --Enh #3117341 Added AUDIT also as a valid value for s_fee_type for intializing the
998 -- segment value with the natural account segment derivied by get_natural_account proc.
999 IF ((l_cur_acc_segs.segment = l_v_seg_name) AND (l_v_s_fee_type IN ('TUTNFEE','OTHER','AUDIT'))) THEN
1000 g_accsegs(g_accsegs.COUNT).lv_segment_value := l_v_nat_acct_seg;
1001 ELSE
1002 g_accsegs(g_accsegs.COUNT).lv_segment_value := NULL;
1003 END IF;
1004 g_accsegs(g_accsegs.COUNT).l_value_length := l_cur_acc_segs.maximum_size;
1005 END LOOP;
1006
1007 FOR l_hier_acct_tbl_rec IN cur_hier_acct_tbl(l_f_typ_ca_inst_lkp_rec.acct_hier_id) LOOP
1008 -- Loop through the Each entity to get the Data.
1009 -- If Oracle Receivables Installed then Get the data from ACCTS_ALL tables for the
1010 -- Entities( Program, Unit, Unit Section, Organization, Location, System Options.
1011 -- Get the Segment Values list
1012 get_segment_values_list( l_hier_acct_tbl_rec.entity_type_code,
1013 p_fee_type,
1014 p_fee_cal_type,
1015 p_fee_ci_sequence_number,
1016 p_course_cd,
1017 p_course_version_number,
1018 p_org_unit_cd,
1019 p_org_start_dt,
1020 p_unit_cd,
1021 p_unit_version_number,
1022 p_uoo_id,
1023 p_location_cd,
1024 l_error_string,
1025 l_return_status
1026 );
1027 IF (l_return_status = FALSE) THEN
1028 x_err_string := l_error_string;
1029 x_err_type := 1;
1030 x_ret_status := FALSE;
1031 RETURN;
1032 END IF;
1033 END LOOP; -- End of Entites Loop
1034
1035
1036 -- If any of the Segment Values are NULL and the entity is the last level in Hierarchy
1037 -- then check the Zero Flag value. If it is checked then fill the with zeros
1038 -- for the incomplete segment and get the CCID for the combination of segment values
1039 -- else raise error and exit.
1040 l_return_status := TRUE;
1041 FOR i IN 1..g_accsegs.COUNT LOOP
1042 IF g_accsegs(i).lv_segment_value IS NULL THEN
1043 l_return_status := FALSE;
1044 EXIT;
1045 END IF;
1046 END LOOP;
1047
1048 -- check if any of the segment is NULL at this stage. If any of the segments are null then depending
1049 -- the zero_fill_flag is set to 'Y' or not show error message.
1050 IF l_return_status = FALSE THEN
1051 IF (l_hier_accounts_rec.zero_fill_flag = 'Y') THEN
1052 -- If Zero Fill Flag is checked then fill with zeros for the incomplete segments and get CCID for combination.
1053 FOR i IN 1..g_accsegs.COUNT LOOP
1054 IF g_accsegs(i).lv_segment_value IS NULL THEN
1055 g_accsegs(i).lv_segment_value := RPAD('0',g_accsegs(i).l_value_length,'0');
1056 END IF;
1057 END LOOP;
1058 ELSE
1059 -- if the system fee type is Ancillary then the user should not be allowed to create
1060 -- any charge with error account as 'Y' Ancillary Charges cannot be created when Revenue
1061 -- Account Segments cannot be derived.
1062 -- Added Document also as a valid type to show the error msg and return from the function
1063 IF l_v_s_fee_type IN ('ANCILLARY','DOCUMENT') THEN
1064 IF l_v_s_fee_type = 'ANCILLARY' THEN
1065 x_err_string := 'IGS_FI_REV_ACCT_CD_NOT_EXIST';
1066 ELSE
1067 x_err_string := 'IGS_FI_SRC_TXN_ACC_INV';
1068 END IF;
1069
1070 x_err_type := 1;
1071 x_ret_status := FALSE; --Set the Return Status.
1072 RETURN; -- should return from this procedure
1073 ELSIF NOT l_b_err_type1 THEN
1074 -- We have to handle l_b_err_type1 = TRUE case (Receivables account setup not done at FTCI and system
1075 -- options level) to avoid masking of message IGS_FI_NO_REC_ACCT_CD_FTCI with this message.
1076 x_err_string := fnd_message.get_string('IGS','IGS_FI_ZERO_FLAG_NOT_SET');
1077 x_err_type := 2;
1078 x_ret_status := FALSE; --Set the Return Status.
1079 END IF;
1080
1081 -- Concatenate the Segment Values to The Error Sting.
1082 concat_seg_values(x_err_string,l_return_status);
1083 IF (l_return_status = FALSE) THEN
1084 --When and Unhandled Exception Occurs in Concat_seg_values then Return with Type 1 Error
1085 x_err_type := 1;
1086 x_ret_status := FALSE;
1087 RETURN;
1088 END IF;
1089 RETURN; -- Return to Calling program by setting Type 2 error type and message concatenated with segment values.
1090 END IF; --Zero flag Check
1091 END IF; -- l_return_status check
1092
1093 -- get the gl ccid when the ccid is NULL.
1094 IF x_cr_gl_ccid IS NULL THEN
1095 get_charge_acct_ccids( x_cr_gl_ccid,
1096 l_error_type_cr,
1097 l_error_string_cr,
1098 l_return_status_cr
1099 );
1100 END IF;
1101
1102 IF l_b_err_type1 THEN
1103 -- System Fee Type is External and the Receivables is not derived then the message and the return status are already set
1104 -- Just Return from the procedure.
1105 RETURN;
1106 ELSE
1107 IF l_return_status_cr THEN
1108 -- Accounting Information successfully derived
1109 x_err_string := NULL;
1110 x_err_type := NULL;
1111 x_ret_status := TRUE;
1112 RETURN;
1113 ELSE
1114 -- When there is any error in fetching the Code Combination ID then
1115 -- show that message.
1116 x_err_string := SUBSTR(l_error_string_cr,1,1000);
1117 x_err_type := l_error_type_cr;
1118 x_ret_status := FALSE;
1119 RETURN;
1120 END IF;
1121 END IF;
1122 END IF;
1123 ELSE
1124 -- If Oracle Receivables is not Installed then get the Data from Account Code String Tables for entities.
1125 -- Get the Corresponding Receivables Account Code defined at FTCI Level and pass
1126 -- to x_dr_account_cd which is a OUT NOCOPY parameter and Return.
1127 x_dr_account_cd := NVL(x_dr_account_cd,l_f_typ_ca_inst_lkp_rec.rec_account_cd);
1128
1129 --Bug#2410396,if receivables Account code is not found at ftci level then fetch it from system level
1130 IF x_dr_account_cd IS NULL THEN
1131 OPEN cur_sys_opt;
1132 FETCH cur_sys_opt INTO l_cur_sys_opt;
1133 CLOSE cur_sys_opt;
1134 x_dr_account_cd := l_cur_sys_opt.rec_account_cd;
1135 END IF;
1136
1137 -- Error out if the System Fee type is other than External
1138 -- In case of External system fee type continue with the revenue account ccid
1139 IF x_dr_account_cd IS NULL THEN
1140 x_err_string := 'IGS_FI_NO_REC_ACCT_CD_FTCI';
1141 x_err_type := 1;
1142 x_ret_status := FALSE;
1143 -- If system fee type is EXTERNAL we do not have to return but continue fetch
1144 -- revenue accounting information
1145 IF l_v_s_fee_type <> 'EXTERNAL' THEN
1146 RETURN;
1147 END IF;
1148 END IF;
1149
1150 --Bug#3392095 - Tution Waiver - Check user has selected TRANSACTION TYPE as Waiver Adjustment
1151 IF l_v_s_fee_type = 'WAIVER_ADJ' THEN
1152 --check whether debit account code for credit types record found exists or not
1153 --accordingly, set the message to x_err_string var. and return back to the calling procedure.
1154 IF l_cur_cr_types_rec1.dr_account_cd IS NULL THEN
1155 x_err_string := 'IGS_FI_CR_TYPE_NO_ACCT';
1156 x_err_type := 1;
1157 x_ret_status := FALSE;
1158 RETURN;
1159 END IF;
1160 x_cr_account_cd := l_cur_cr_types_rec1.dr_account_cd;
1161 RETURN;
1162 END IF;
1163
1164 -- For RETENTION Charges retention account information is mandatory
1165 IF p_transaction_type = 'RETENTION' THEN
1166 x_cr_account_cd := NVL(x_cr_account_cd, l_f_typ_ca_inst_lkp_rec.ret_account_cd);
1167 IF x_cr_account_cd IS NULL THEN
1168 x_err_string := 'IGS_FI_NO_RETENTION_ACC';
1169 x_err_type := 1;
1170 x_ret_status := FALSE;
1171 RETURN;
1172 END IF;
1173 END IF;
1174
1175 -- proceed with finding out the cr account code if it is not null
1176 IF (x_cr_account_cd IS NULL) THEN
1177 --Enh#3117341 added audit,special also as a valid system fee as a part of Audit and special fees build.
1178 IF (l_v_s_fee_type IN ('TUTNFEE','OTHER','AUDIT','SPECIAL')) THEN
1179 get_natural_account( p_v_fee_type => p_fee_type,
1180 p_v_fee_cal_type => p_fee_cal_type,
1181 p_n_fee_ci_seq_num => p_fee_ci_sequence_number,
1182 p_v_location_cd => p_location_cd,
1183 p_v_att_type => p_v_attendance_type,
1184 p_v_att_mode => p_v_attendance_mode,
1185 p_v_course_cd => p_course_cd ,
1186 p_n_crs_ver_num => p_course_version_number,
1187 p_v_unit_cd => p_unit_cd,
1188 p_n_unit_ver_num => p_unit_version_number,
1189 p_v_org_unit_cd => p_org_unit_cd,
1190 p_v_residency_status_cd => p_v_residency_status_cd,
1191 p_n_uoo_id => p_uoo_id,
1192 p_v_nat_acc_seg => l_v_nat_acct_seg,
1193 p_v_account_cd => l_v_account_cd,
1194 p_n_err_type => l_n_err_type,
1195 p_v_err_msg => l_v_err_msg,
1196 p_b_status => l_b_nat_rec,
1197 p_n_unit_type_id => p_n_unit_type_id,
1198 p_v_unit_class => p_v_unit_class,
1199 p_v_unit_mode => p_v_unit_mode,
1200 p_v_unit_level => p_v_unit_level
1201 );
1202
1203 IF (l_b_nat_rec = FALSE) THEN
1204 IF (l_n_err_type = 0) THEN
1205 x_cr_account_cd := NULL;
1206 x_err_type := 1;
1207 x_err_string := 'IGS_FI_REV_ACCT_CD_NOT_EXIST';
1208 x_ret_status := FALSE;
1209 RETURN;
1210 ELSIF (l_n_err_type = 1) THEN
1211 x_cr_account_cd := NULL;
1212 x_err_type := 1;
1213 x_err_string := l_v_err_msg;
1214 x_ret_status := FALSE;
1215 RETURN;
1216 END IF;
1217 ELSE
1218 x_cr_account_cd := l_v_account_cd;
1219 x_err_type := NULL;
1220 x_err_string := NULL;
1221 x_ret_status := TRUE;
1222 RETURN;
1223 END IF;
1224 ELSE
1225 -- In case when the system fee type is other than Tution/Other than
1226 -- get the rev_account_cd from the FTCI.
1227 -- No hierarchy will be taken into consideration.
1228 x_cr_account_cd := l_f_typ_ca_inst_lkp_rec.rev_account_cd;
1229 END IF;
1230
1231 -- If the system fee type is External, since the message is already set before
1232 -- just return from this procedure
1233 IF x_dr_account_cd IS NULL THEN
1234 IF l_v_s_fee_type = 'EXTERNAL' THEN
1235 RETURN;
1236 END IF;
1237 ELSE
1238 IF x_cr_account_cd IS NULL THEN
1239 x_err_type := 1;
1240 x_err_string := 'IGS_FI_REV_ACCT_CD_NOT_EXIST';
1241 x_ret_status := FALSE;
1242 RETURN;
1243 END IF; -- x_cr_account_cd check
1244 END IF; -- x_dr_account_cd Check
1245 END IF; -- x_cr_account_cd is null check
1246 END IF; -- g_oracle_rec_installed check
1247 END IF; -- cur_f_typ_ca_inst_lkp cursor
1248 ELSE
1249 -- If Transaction Type is not Credit or Charge Transaction then Raise the error and Exit.
1250 x_err_string := 'IGS_FI_INVALID_TRANS';
1251 x_err_type := 1;
1252 x_ret_status := FALSE;
1253 RETURN;
1254 END IF;
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1258 fnd_message.set_token('NAME','BUILD_ACCOUNTS: '||SQLERRM);
1259 x_err_type := 1;
1260 x_ret_status := FALSE;
1261 RETURN;
1262 END build_accounts;
1263 END igs_fi_prc_acct_pkg;
[Home] [Help]