1 PACKAGE BODY igf_aw_coa_update AS
2 /* $Header: IGFAW16B.pls 120.6 2006/02/08 23:40:48 ridas noship $ */
3
4 ------------------------------------------------------------------------------
5 -- Who When What
6 --------------------------------------------------------------------------------
7
8 -- Procedure to update TO DO Items where p_run_type IN ('S','Y','P')
9 PROCEDURE update_to_do_items (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
10 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
11 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE
12 )
13 IS
14 --------------------------------------------------------------------------------
15 -- Created by : ridas, Oracle India
16 -- Date created: 27-DEC-2004
17
18 -- Change History:
19 -- Who When What
20 -- ridas 27-DEC-2004 Bug #4087686
21 --------------------------------------------------------------------------------
22
23 --Cursor to fetch persons from todo ref table
24 CURSOR c_person_ref(
25 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
26 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
27 c_person_id igs_pe_std_todo_ref.person_id%TYPE,
28 c_s_student_todo_type igs_pe_std_todo_ref.s_student_todo_type%TYPE,
29 c_sequence_number igs_pe_std_todo_ref.sequence_number%TYPE
30 ) IS
31 SELECT tref.rowid row_id,
32 tref.*
33 FROM igs_pe_std_todo_ref tref
34 WHERE tref.person_id = c_person_id
35 AND tref.s_student_todo_type = c_s_student_todo_type
36 AND tref.sequence_number = c_sequence_number
37 AND tref.cal_type = c_ci_cal_type
38 AND tref.ci_sequence_number = c_ci_sequence_number
39 AND tref.s_student_todo_type = 'IGF_COA_COMP'
40 AND tref.logical_delete_dt IS NULL;
41
42
43 --Cursor to fetch persons from todo table
44 CURSOR c_person_todo(
45 c_person_id igs_pe_std_todo.person_id%TYPE,
46 c_s_student_todo_type igs_pe_std_todo.s_student_todo_type%TYPE,
47 c_sequence_number igs_pe_std_todo.sequence_number%TYPE
48 ) IS
49 SELECT todo.rowid row_id,
50 todo.*
51 FROM igs_pe_std_todo todo
52 WHERE todo.person_id = c_person_id
53 AND todo.s_student_todo_type = c_s_student_todo_type
54 AND todo.sequence_number = c_sequence_number
55 AND todo.logical_delete_dt IS NULL
56 AND NOT EXISTS
57 (SELECT tref.person_id,
58 tref.s_student_todo_type,
59 tref.sequence_number
60 FROM igs_pe_std_todo_ref tref
61 WHERE tref.person_id = todo.person_id
62 AND tref.s_student_todo_type = todo.s_student_todo_type
63 AND tref.sequence_number = todo.sequence_number
64 AND tref.s_student_todo_type = 'IGF_COA_COMP'
65 AND tref.logical_delete_dt IS NULL
66 GROUP BY tref.person_id, tref.s_student_todo_type, tref.sequence_number
67 );
68
69 l_person_todo c_person_todo%ROWTYPE;
70
71 --Cursor to fetch the person who have a "COA Re-computation" as a "Person To Do"
72 CURSOR cur_person_dtls(
73 c_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
74 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
75 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
76 ) IS
77 SELECT distinct todo.person_id,
78 todo.s_student_todo_type,
79 todo.sequence_number
80 FROM igs_pe_std_todo todo,
81 igs_pe_std_todo_ref tref
82 WHERE todo.person_id = c_person_id
83 AND tref.person_id = todo.person_id
84 AND tref.s_student_todo_type = todo.s_student_todo_type
85 AND tref.sequence_number = todo.sequence_number
86 AND tref.cal_type = c_ci_cal_type
87 AND tref.ci_sequence_number = c_ci_sequence_number
88 AND todo.s_student_todo_type = 'IGF_COA_COMP'
89 AND todo.logical_delete_dt IS NULL
90 AND tref.logical_delete_dt IS NULL;
91
92 l_person_dtls cur_person_dtls%ROWTYPE;
93
94
95 --Cursor to fetch person id
96 CURSOR cur_get_person_id(
97 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
98 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
99 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
100 ) IS
101 SELECT person_id
102 FROM igf_ap_fa_base_rec_all
103 WHERE base_id = c_base_id
104 AND ci_cal_type = c_ci_cal_type
105 AND ci_sequence_number = c_ci_sequence_number;
106
107 l_get_person_id cur_get_person_id%ROWTYPE;
108
109 BEGIN
110 --Get the person id from the base id
111 OPEN cur_get_person_id(p_base_id,p_ci_cal_type,p_ci_sequence_number);
112 FETCH cur_get_person_id INTO l_get_person_id;
113 CLOSE cur_get_person_id;
114
115 IF l_get_person_id.person_id IS NOT NULL THEN
116 OPEN cur_person_dtls(l_get_person_id.person_id,p_ci_cal_type,p_ci_sequence_number);
117 FETCH cur_person_dtls INTO l_person_dtls;
118 CLOSE cur_person_dtls;
119
120 --If To Do
121 IF l_person_dtls.person_id IS NOT NULL THEN
122 FOR l_person_ref IN c_person_ref(p_ci_cal_type,p_ci_sequence_number,l_person_dtls.person_id,l_person_dtls.s_student_todo_type,l_person_dtls.sequence_number)
123 LOOP
124 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
125 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.update_to_do_items.debug','Updating table igs_pe_std_todo for person id: '||l_person_dtls.person_id);
126 END IF;
127
128 igs_pe_std_todo_ref_pkg.update_row(
129 x_rowid => l_person_ref.row_id,
130 x_person_id => l_person_ref.person_id,
131 x_s_student_todo_type => l_person_ref.s_student_todo_type,
132 x_sequence_number => l_person_ref.sequence_number,
133 x_reference_number => l_person_ref.reference_number,
134 x_cal_type => l_person_ref.cal_type,
135 x_ci_sequence_number => l_person_ref.ci_sequence_number,
136 x_course_cd => l_person_ref.course_cd,
137 x_unit_cd => l_person_ref.unit_cd,
138 x_other_reference => l_person_ref.other_reference,
139 x_logical_delete_dt => sysdate,
140 x_mode => 'R',
141 x_uoo_id => l_person_ref.uoo_id
142 );
143 END LOOP;
144
145 OPEN c_person_todo(l_person_dtls.person_id,l_person_dtls.s_student_todo_type,l_person_dtls.sequence_number);
146 FETCH c_person_todo INTO l_person_todo;
147
148 IF c_person_todo%FOUND THEN
149
150 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
151 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.update_to_do_items.debug','c_person_todo%FOUND');
152 END IF;
153
154 igs_pe_std_todo_pkg.update_row(
155 x_rowid => l_person_todo.row_id,
156 x_person_id => l_person_todo.person_id,
157 x_s_student_todo_type => l_person_todo.s_student_todo_type,
158 x_sequence_number => l_person_todo.sequence_number,
159 x_todo_dt => l_person_todo.todo_dt,
160 x_logical_delete_dt => sysdate,
161 x_mode => 'R'
162 );
163 END IF;
164 CLOSE c_person_todo;
165
166 END IF;
167 END IF;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
172 fnd_message.set_token('NAME','igf_aw_coa_update.update_to_do_items :' || SQLERRM);
173 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
174 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_update.update_to_do_items.exception','sql error:'||SQLERRM);
175 END IF;
176 igs_ge_msg_stack.conc_exception_hndl;
177 app_exception.raise_exception;
178
179 END update_to_do_items;
180
181
182
183
184 -- This function check whether the student attributes are matching with the COA Rate Order or not
185 -- If matching it returns the new calculated amount
186 FUNCTION is_attrib_matching(
187 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
188 p_base_details IN igf_aw_coa_gen.base_details,
189 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
190 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
191 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
192 p_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE,
193 p_item_code IN igf_aw_item.item_code%TYPE,
194 p_amount OUT NOCOPY NUMBER,
195 p_rate_order_num OUT NOCOPY NUMBER
196 ) RETURN BOOLEAN IS
197 --------------------------------------------------------------------------------
198 -- Created by : ridas, Oracle India
199 -- Date created: 26-OCT-2004
200
201 -- Change History:
202 -- Who When What
203 -- ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in
204 -- call to igf_ap_ss_pkg.get_pid
205 --------------------------------------------------------------------------------
206
207 -- Variables for the dynamic person id group
208 lv_status VARCHAR2(1);
209 lv_sql_stmt VARCHAR(32767) ;
210 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
211
212 TYPE CperexistCurTyp IS REF CURSOR ;
213 c_chk_per_exist CperexistCurTyp ;
214 lv_chk_per_exist NUMBER(1);
215
216 CURSOR c_rate_order (
217 c_ci_cal_type igs_ca_inst.cal_type%TYPE,
218 c_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
219 c_item_code igf_aw_item.item_code%TYPE
220 ) IS
221 SELECT rate.*
222 FROM igf_aw_coa_rate_det rate
223 WHERE ci_cal_type = c_ci_cal_type
224 AND ci_sequence_number = c_ci_sequence_number
225 AND item_code = c_item_code
226 ORDER BY rate_order_num ASC;
227
228 l_rate_order c_rate_order%ROWTYPE;
229
230 --Cursor to fetch Group ID
231 CURSOR c_grp_id (
232 c_grp_code igs_pe_persid_group_all.group_cd%TYPE
233 ) IS
234 SELECT group_id
235 FROM igs_pe_persid_group_all
236 WHERE group_cd = c_grp_code;
237
238 l_grp_id c_grp_id%ROWTYPE;
239
240 l_counter NUMBER;
241
242 BEGIN
243 l_counter := 0;
244
245 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
246 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','OPEN c_rate_order');
247 END IF;
248
249 OPEN c_rate_order(p_ci_cal_type,p_ci_sequence_number,p_item_code);
250 FETCH c_rate_order INTO l_rate_order;
251 IF c_rate_order%NOTFOUND THEN
252 CLOSE c_rate_order;
253 RETURN FALSE;
254 END IF;
255
256 LOOP
257
258 IF ((l_rate_order.org_unit_cd = p_base_details.org_unit_cd OR l_rate_order.org_unit_cd IS NULL)
259 AND (l_rate_order.program_type = p_base_details.program_type OR l_rate_order.program_type IS NULL)
260 AND (l_rate_order.program_location_cd = p_base_details.program_location_cd OR l_rate_order.program_location_cd IS NULL)
261 AND (l_rate_order.program_cd = p_base_details.program_cd OR l_rate_order.program_cd IS NULL)
262 AND (l_rate_order.class_standing = p_base_details.class_standing OR l_rate_order.class_standing IS NULL)
263 AND (l_rate_order.residency_status_code = p_base_details.residency_status_code OR l_rate_order.residency_status_code IS NULL)
264 AND (l_rate_order.housing_status_code = p_base_details.housing_status_code OR l_rate_order.housing_status_code IS NULL)
265 AND (l_rate_order.attendance_type = p_base_details.attendance_type OR l_rate_order.attendance_type IS NULL)
266 AND (l_rate_order.attendance_mode = p_base_details.attendance_mode OR l_rate_order.attendance_mode IS NULL)
267 AND (NVL(l_rate_order.ld_cal_type,p_ld_cal_type) = p_ld_cal_type)
268 AND (NVL(l_rate_order.ld_sequence_number,p_ld_sequence_number) = p_ld_sequence_number)
269 )
270 THEN
271
272 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
273 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','inside if condition');
274 END IF;
275
276 IF l_rate_order.pid_group_cd IS NOT NULL THEN
277 OPEN c_grp_id(l_rate_order.pid_group_cd);
278 FETCH c_grp_id INTO l_grp_id;
279 CLOSE c_grp_id;
280
281 IF l_grp_id.group_id IS NOT NULL THEN
282 -- To check whether the person exist in the group or not
283 -- Bug #5021084
284 lv_sql_stmt := igf_ap_ss_pkg.get_pid(l_grp_id.group_id,lv_status,lv_group_type);
285
286 --Bug #5021084. Passing Group ID if the group type is STATIC.
287 IF lv_group_type = 'STATIC' THEN
288 OPEN c_chk_per_exist FOR 'SELECT 1
289 FROM igf_ap_fa_base_rec fabase
290 WHERE fabase.base_id = :base_id
291 AND fabase.person_id in ( '||lv_sql_stmt||') ' USING p_base_id,l_grp_id.group_id;
292 ELSIF lv_group_type = 'DYNAMIC' THEN
293 OPEN c_chk_per_exist FOR 'SELECT 1
294 FROM igf_ap_fa_base_rec fabase
295 WHERE fabase.base_id = :base_id
296 AND fabase.person_id in ( '||lv_sql_stmt||') ' USING p_base_id;
297 END IF;
298
299 FETCH c_chk_per_exist INTO lv_chk_per_exist;
300
301 IF c_chk_per_exist%NOTFOUND THEN
302 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
303 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','c_chk_per_exist%NOTFOUND');
304 END IF;
305
306 ELSE
307 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
308 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','person found in the person group id');
309 END IF;
310
311 l_counter := 1;
312 CLOSE c_chk_per_exist;
313 EXIT;
314 END IF;
315 CLOSE c_chk_per_exist;
316 ELSE
317 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
318 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','l_grp_id.group_id IS NULL');
319 END IF;
320 END IF;
321
322 ELSE
323 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
324 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','l_rate_order.pid_group_cd IS NULL');
325 END IF;
326
327 l_counter := 1;
328 EXIT;
329 END IF;
330
331 ELSE
332
333 IF l_rate_order.org_unit_cd IS NOT NULL AND p_base_details.org_unit_cd IS NULL THEN
334 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
335 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','ORG_UNIT_CD'));
336 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
337 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
338
339 l_counter := 2;
340 END IF;
341
342 IF l_rate_order.program_type IS NOT NULL AND p_base_details.program_type IS NULL THEN
343 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
344 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','PROGRAM_TYPE'));
345 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
346 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
347
348 l_counter := 2;
349 END IF;
350
351 IF l_rate_order.program_location_cd IS NOT NULL AND p_base_details.program_location_cd IS NULL THEN
352 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
353 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','PROGRAM_LOCATION_CD'));
354 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
355 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
356
357 l_counter := 2;
358 END IF;
359
360 IF l_rate_order.program_cd IS NOT NULL AND p_base_details.program_cd IS NULL THEN
361 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
362 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','PROGRAM_CD'));
363 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
364 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
365
366 l_counter := 2;
367 END IF;
368
369 IF l_rate_order.class_standing IS NOT NULL AND p_base_details.class_standing IS NULL THEN
370 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
371 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','CLASS_STANDING'));
372 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
373 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
374
375 l_counter := 2;
376 END IF;
377
378 IF l_rate_order.residency_status_code IS NOT NULL AND p_base_details.residency_status_code IS NULL THEN
379 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
380 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','RESIDENCY_STATUS_CODE'));
381 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
382 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
383
384 l_counter := 2;
385 END IF;
386
387 IF l_rate_order.housing_status_code IS NOT NULL AND p_base_details.housing_status_code IS NULL THEN
388 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
389 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','HOUSING_STATUS_CODE'));
390 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
391 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
392
393 l_counter := 2;
394 END IF;
395
396 IF l_rate_order.attendance_type IS NOT NULL AND p_base_details.attendance_type IS NULL THEN
397 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
398 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','ATTENDANCE_TYPE'));
399 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
400 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
401
402 l_counter := 2;
403 END IF;
404
405 IF l_rate_order.attendance_mode IS NOT NULL AND p_base_details.attendance_mode IS NULL THEN
406 fnd_message.set_name('IGF','IGF_AW_STD_ATTRIB');
407 fnd_message.set_token('ATTRIBUTE',igf_aw_gen.lookup_desc('IGF_AW_COA_GEN','ATTENDANCE_MODE'));
408 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type, p_ld_sequence_number));
409 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
410
411 l_counter := 2;
412 END IF;
413
414 IF l_counter = 2 THEN
415 EXIT;
416 END IF;
417 END IF;
418
419 FETCH c_rate_order INTO l_rate_order;
420 EXIT WHEN c_rate_order%NOTFOUND;
421 END LOOP;
422 CLOSE c_rate_order;
423
424 IF l_counter = 1 THEN
425 IF l_rate_order.mult_factor_code = 'ME' THEN
426 p_amount := NVL(p_base_details.months_enrolled_num,0)*NVL(l_rate_order.mult_amount_num,0);
427 ELSIF l_rate_order.mult_factor_code = 'CP' THEN
428 p_amount := NVL(p_base_details.credit_points_num,0)*NVL(l_rate_order.mult_amount_num,0);
429 ELSIF l_rate_order.mult_factor_code = 'FA' THEN
430 p_amount := NVL(l_rate_order.mult_amount_num,0);
431 END IF;
432
433 p_rate_order_num := l_rate_order.rate_order_num;
434
435 RETURN TRUE;
436 ELSE
437
438 IF l_counter = 2 THEN
439 p_rate_order_num := -1;
440 END IF;
441 RETURN FALSE;
442 END IF;
443
444 END is_attrib_matching;
445
446
447 -- This procedure is to evaluate the COA re-computation amount
448 PROCEDURE evaluate(
449 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
450 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
451 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE
452 ) IS
453 --------------------------------------------------------------------------------
454 -- Created by : ridas, Oracle India
455 -- Date created: 26-OCT-2004
456
457 -- Change History:
458 -- Who When What
459 --------------------------------------------------------------------------------
460
461 --This cursor is to fetch person details
462 CURSOR c_base_rec (
463 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
464 ) IS
465 SELECT NVL(fab.lock_coa_flag,'N') lock_coa_flag
466 FROM igf_ap_fa_base_rec fab
467 WHERE fab.base_id = c_base_id;
468
469 l_base_rec c_base_rec%ROWTYPE;
470
471
472 --This cursor is to fetch items details for a person
473 CURSOR c_items(
474 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
475 ) IS
476 SELECT items.*
477 FROM igf_aw_coa_items items
478 WHERE items.base_id = c_base_id;
479
480
481 --This cursor is to fetch terms details against an item
482 CURSOR c_terms(
483 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
484 c_item_code igf_aw_item.item_code%TYPE
485 ) IS
486 SELECT terms.rowid row_id,
487 terms.*
488 FROM igf_aw_coa_itm_terms terms
489 WHERE base_id = c_base_id
490 AND item_code = c_item_code;
491
492
493 --Cursor to fetch the sum amount of all the terms for the Item code
494 CURSOR c_sum_amt(
495 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
496 c_item_code igf_aw_coa_itm_terms.item_code%TYPE
497 ) IS
498 SELECT SUM(NVL(amount,0)) amount
499 FROM igf_aw_coa_itm_terms term
500 WHERE base_id = c_base_id
501 AND item_code = c_item_code;
502
503 l_sum_amt c_sum_amt%ROWTYPE;
504
505
506 --Cursor to fetch item details for the base id
507 CURSOR c_item(
508 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
509 c_item_code igf_aw_coa_itm_terms.item_code%TYPE
510 ) IS
511 SELECT item.rowid row_id,
512 item.*
513 FROM igf_aw_coa_items item
514 WHERE base_id = c_base_id
515 AND item_code = c_item_code;
516
517 l_item c_item%ROWTYPE;
518
519 CURSOR c_rate_ord_exists (
520 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
521 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
522 cp_item_code igf_aw_item.item_code%TYPE
523 ) IS
524 SELECT 'X' exist
525 FROM igf_aw_coa_rate_det rate
526 WHERE ci_cal_type = cp_ci_cal_type
527 AND ci_sequence_number = cp_ci_sequence_number
528 AND item_code = cp_item_code
529 AND ROWNUM = 1;
530
531 l_rate_ord_exists c_rate_ord_exists%ROWTYPE;
532
533 l_base_details igf_aw_coa_gen.base_details;
534 ln_amount NUMBER;
535 ln_rate_order NUMBER;
536 lv_coa_itm_update VARCHAR2(1);
537 lv_award_proc_status igf_aw_award_all.awd_proc_status_code%TYPE;
538 E_SKIP_STUDENT EXCEPTION;
539
540 BEGIN
541 lv_coa_itm_update := 'N';
542
543 SAVEPOINT start_evaluate;
544
545 OPEN c_base_rec(p_base_id);
546 FETCH c_base_rec INTO l_base_rec;
547 CLOSE c_base_rec;
548
549 IF l_base_rec.lock_coa_flag = 'Y' THEN
550
551 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
552 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','base_id:'||p_base_id||' is locked');
553 END IF;
554
555 fnd_message.set_name('IGF','IGF_AW_STUD_SKIP');
556 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
557 ELSE
558 FOR l_items IN c_items(p_base_id)
559 LOOP
560 IF l_items.lock_flag = 'Y' THEN
561
562 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
563 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','l_items.item_code:'||l_items.item_code||' is locked');
564 END IF;
565
566 fnd_message.set_name('IGF','IGF_AW_SKP_LK_ITM');
567 fnd_message.set_token('ITEM_CODE',l_items.item_code);
568 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
569
570 ELSE
571 --cursor to check whether the COA item exists in the rate based setup table or not
572 OPEN c_rate_ord_exists(p_ci_cal_type, p_ci_sequence_number, l_items.item_code);
573 FETCH c_rate_ord_exists INTO l_rate_ord_exists;
574 IF c_rate_ord_exists%NOTFOUND THEN
575 CLOSE c_rate_ord_exists;
576
577 fnd_message.set_name('IGF','IGF_AW_SKIP_NON_RATE');
578 fnd_message.set_token('ITEM',l_items.item_code);
579 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
580
581 ELSIF c_rate_ord_exists%FOUND THEN
582 CLOSE c_rate_ord_exists;
583
584 FOR l_terms IN c_terms(p_base_id, l_items.item_code)
585 LOOP
586 IF l_terms.lock_flag = 'Y' THEN
587
588 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
589 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','term:'||igf_gr_gen.get_alt_code(l_terms.ld_cal_type,l_terms.ld_sequence_number)||' is locked');
590 END IF;
591
592 fnd_message.set_name('IGF','IGF_AW_SKP_LK_TRM');
593 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(l_terms.ld_cal_type,l_terms.ld_sequence_number));
594 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
595 ELSE
596 --Execute the COA re-calculation logic
597 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
598 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','Fetching student attributes for base_id:'||p_base_id);
599 END IF;
600
601 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,l_terms.ld_cal_type,l_terms.ld_sequence_number);
602
603 --Rate Order found against the student attributes
604 IF is_attrib_matching(p_base_id,
605 l_base_details,
606 p_ci_cal_type,
607 p_ci_sequence_number,
608 l_terms.ld_cal_type,
609 l_terms.ld_sequence_number,
610 l_items.item_code,
611 ln_amount,
612 ln_rate_order
613 ) THEN
614
615 IF ln_amount <> l_terms.amount THEN
616 fnd_message.set_name('IGF','IGF_AW_UPD_ITM');
617 fnd_message.set_token('ITEM_CODE',l_items.item_code);
618 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(l_terms.ld_cal_type,l_terms.ld_sequence_number));
619 fnd_message.set_token('RATE_ORDER',ln_rate_order);
620 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
621
622 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
623 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','ln_amount <> l_terms.amount for base_id:'||p_base_id);
624 END IF;
625
626 igf_aw_coa_itm_terms_pkg.update_row(
627 x_rowid => l_terms.row_id,
628 x_base_id => l_terms.base_id,
629 x_item_code => l_terms.item_code,
630 x_amount => ln_amount,
631 x_ld_cal_type => l_terms.ld_cal_type,
632 x_ld_sequence_number => l_terms.ld_sequence_number,
633 x_mode => 'R',
634 x_lock_flag => l_terms.lock_flag
635 );
636 lv_coa_itm_update := 'Y';
637 END IF;
638 ELSE
639 IF NVL(ln_rate_order,0) <> -1 THEN
640 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
641 fnd_message.set_token('ITEM_CODE',l_items.item_code);
642 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(l_terms.ld_cal_type,l_terms.ld_sequence_number));
643 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
644 END IF;
645
646 RAISE E_SKIP_STUDENT;
647 END IF; -- End of IF is_attrib_matching()
648 END IF;
649 END LOOP;
650 END IF; -- End of IF c_rate_ord_exists%NOTFOUND THEN
651
652 OPEN c_sum_amt(p_base_id,l_items.item_code);
653 FETCH c_sum_amt INTO l_sum_amt;
654 CLOSE c_sum_amt;
655
656 IF l_sum_amt.amount IS NOT NULL THEN
657 OPEN c_item(p_base_id,l_items.item_code);
658 FETCH c_item INTO l_item;
659 CLOSE c_item;
660
661 igf_aw_coa_items_pkg.update_row(
662 x_rowid => l_item.row_id,
663 x_base_id => l_item.base_id,
664 x_item_code => l_item.item_code,
665 x_amount => l_sum_amt.amount,
666 x_pell_coa_amount => l_item.pell_coa_amount,
667 x_alt_pell_amount => l_item.alt_pell_amount,
668 x_fixed_cost => l_item.fixed_cost,
669 x_legacy_record_flag => l_item.legacy_record_flag,
670 x_mode => 'R',
671 x_lock_flag => l_item.lock_flag
672 );
673
674 END IF;
675 END IF;
676 END LOOP;
677 END IF;
678
679 IF lv_coa_itm_update = 'Y' THEN
680 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
681 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','lv_coa_itm_update = Y');
682 END IF;
683
684 lv_award_proc_status := igf_aw_coa_gen.set_awd_proc_status(p_base_id);
685 END IF;
686
687 EXCEPTION
688 WHEN E_SKIP_STUDENT THEN
689 ROLLBACK TO start_evaluate;
690 fnd_message.set_name('IGF','IGF_AW_RATE_NOT_AVAIL');
691 fnd_file.put_line(fnd_file.log,RPAD(' ',5)|| fnd_message.get());
692
693 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
694 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_update.evaluate.exception','sql error message:'||SQLERRM);
695 END IF;
696
697 WHEN OTHERS THEN
698 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
699 fnd_message.set_token('NAME','igf_aw_coa_update.evaluate :' || SQLERRM);
700 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
701 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_update.evaluate.exception','sql error:'||SQLERRM);
702 END IF;
703 igs_ge_msg_stack.conc_exception_hndl;
704 app_exception.raise_exception;
705
706 END evaluate;
707
708
709 -- This procedure is the callable from concurrent manager
710 PROCEDURE main(
711 errbuf OUT NOCOPY VARCHAR2,
712 retcode OUT NOCOPY NUMBER,
713 p_award_year IN VARCHAR2,
714 p_run_type IN VARCHAR2,
715 p_pid_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
716 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
717 ) IS
718 --------------------------------------------------------------------------------
719 -- this procedure is called from concurrent manager.
720 -- if the parameters passed are not correct then procedure exits
721 -- giving reasons for errors.
722 -- Created by : ridas, Oracle India
723 -- Date created: 26-OCT-2004
724
725 -- Change History:
726 -- Who When What
727 -- ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in
728 -- call to igf_ap_ss_pkg.get_pid
729 -- tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
730 --------------------------------------------------------------------------------
731
732 param_exception EXCEPTION;
733
734 -- Variables for the dynamic person id group
735 lv_status VARCHAR2(1);
736 lv_sql_stmt VARCHAR(32767);
737 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
738
739 TYPE CpregrpCurTyp IS REF CURSOR ;
740 cur_per_grp CpregrpCurTyp ;
741
742 TYPE CpergrpTyp IS RECORD(
743 person_id igf_ap_fa_base_rec_all.person_id%TYPE,
744 person_number igs_pe_person_base_v.person_number%TYPE
745 );
746 per_grp_rec CpergrpTyp ;
747
748
749 --Cursor below retrieves all the students belonging to a given AWARD YEAR
750 CURSOR c_per_awd_yr(
751 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
752 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
753 ) IS
754 SELECT fa.base_id
755 FROM igf_ap_fa_base_rec_all fa
756 WHERE fa.ci_cal_type = c_ci_cal_type
757 AND fa.ci_sequence_number = c_ci_sequence_number
758 ORDER BY fa.base_id;
759
760 l_per_awd_rec c_per_awd_yr%ROWTYPE;
761
762
763 --Cursor below retrieves the group code for the given group id
764 CURSOR c_group_code(
765 c_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE
766 ) IS
767 SELECT group_cd
768 FROM igs_pe_persid_group_all
769 WHERE group_id = c_grp_id;
770
771 l_grp_cd c_group_code%ROWTYPE;
772
773
774 --Cursor to fetch person no based on person id
775 CURSOR c_person_no (
776 c_person_id hz_parties.party_id%TYPE
777 ) IS
778 SELECT party_number
779 FROM hz_parties
780 WHERE party_id = c_person_id;
781
782 l_person_no c_person_no%ROWTYPE;
783
784
785 --Cursor to fetch all persons who have a "COA Re-computation" as a "Person To Do"
786 CURSOR cur_person_id(
787 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
788 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
789 ) IS
790 SELECT distinct todo.person_id,
791 todo.s_student_todo_type,
792 todo.sequence_number
793 FROM igs_pe_std_todo todo,
794 igs_pe_std_todo_ref tref
795 WHERE tref.person_id = todo.person_id
796 AND tref.s_student_todo_type = todo.s_student_todo_type
797 AND tref.sequence_number = todo.sequence_number
798 AND tref.cal_type = c_ci_cal_type
799 AND tref.ci_sequence_number = c_ci_sequence_number
800 AND todo.s_student_todo_type = 'IGF_COA_COMP'
801 AND todo.logical_delete_dt IS NULL
802 AND tref.logical_delete_dt IS NULL;
803
804
805 --Cursor to fetch persons from todo ref table
806 CURSOR c_person_ref(
807 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
808 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
809 c_person_id igs_pe_std_todo_ref.person_id%TYPE,
810 c_s_student_todo_type igs_pe_std_todo_ref.s_student_todo_type%TYPE,
811 c_sequence_number igs_pe_std_todo_ref.sequence_number%TYPE
812 ) IS
813 SELECT tref.rowid row_id,
814 tref.*
815 FROM igs_pe_std_todo_ref tref
816 WHERE tref.person_id = c_person_id
817 AND tref.s_student_todo_type = c_s_student_todo_type
818 AND tref.sequence_number = c_sequence_number
819 AND tref.cal_type = c_ci_cal_type
820 AND tref.ci_sequence_number = c_ci_sequence_number
821 AND tref.s_student_todo_type = 'IGF_COA_COMP'
822 AND tref.logical_delete_dt IS NULL;
823
824
825 --Cursor to fetch persons from todo table
826 CURSOR c_person_todo(
827 c_person_id igs_pe_std_todo.person_id%TYPE,
828 c_s_student_todo_type igs_pe_std_todo.s_student_todo_type%TYPE,
829 c_sequence_number igs_pe_std_todo.sequence_number%TYPE
830 ) IS
831 SELECT todo.rowid row_id,
832 todo.*
833 FROM igs_pe_std_todo todo
834 WHERE todo.person_id = c_person_id
835 AND todo.s_student_todo_type = c_s_student_todo_type
836 AND todo.sequence_number = c_sequence_number
837 AND todo.logical_delete_dt IS NULL
838 AND NOT EXISTS
839 (SELECT tref.person_id,
840 tref.s_student_todo_type,
841 tref.sequence_number
842 FROM igs_pe_std_todo_ref tref
843 WHERE tref.person_id = todo.person_id
844 AND tref.s_student_todo_type = todo.s_student_todo_type
845 AND tref.sequence_number = todo.sequence_number
846 AND tref.s_student_todo_type = 'IGF_COA_COMP'
847 AND tref.logical_delete_dt IS NULL
848 GROUP BY tref.person_id, tref.s_student_todo_type, tref.sequence_number
849 );
850
851 l_person_todo c_person_todo%ROWTYPE;
852
853 lv_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
854 ln_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
855 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
856 lv_err_msg fnd_new_messages.message_name%TYPE;
857 lv_return_flag VARCHAR2(1);
858
859
860 BEGIN
861 igf_aw_gen.set_org_id(NULL);
862 retcode := 0;
863 errbuf := NULL;
864 lv_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
865 ln_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
866 lv_status := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
867
868
869 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
870 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_award_year:'||p_award_year);
871 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_run_type:'||p_run_type);
872 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_pid_group:'||p_pid_group);
873 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_base_id:'||p_base_id);
874 END IF;
875
876 fnd_file.new_line(fnd_file.log,1);
877
878 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
879 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'),60) || igf_gr_gen.get_alt_code(lv_ci_cal_type,ln_ci_sequence_number));
880
881 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','RUN_TYPE'),60) ||p_run_type );
882
883 OPEN c_group_code(p_pid_group);
884 FETCH c_group_code INTO l_grp_cd;
885 CLOSE c_group_code;
886
887 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'),60) || l_grp_cd.group_cd);
888 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),60) || igf_gr_gen.get_per_num(p_base_id));
889
890 fnd_file.new_line(fnd_file.log,2);
891
892 IF (p_award_year IS NULL) OR (p_run_type IS NULL) THEN
893 RAISE param_exception;
894
895 ELSIF lv_ci_cal_type IS NULL OR ln_ci_sequence_number IS NULL THEN
896 RAISE param_exception;
897
898 ELSIF (p_pid_group IS NOT NULL) AND (p_base_id IS NOT NULL) THEN
899 RAISE param_exception;
900
901 --If person selection is for all persons in the Person ID Group and
902 --Person ID Group is NULL then log error with exception
903 ELSIF p_run_type = 'P' AND p_pid_group IS NULL THEN
904 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_P');
905 fnd_file.put_line(fnd_file.log,fnd_message.get);
906 RAISE param_exception;
907
908 --If person selection is for a single person and
909 --Base ID is NULL then log error with exception
910 ELSIF p_run_type = 'S' AND p_base_id IS NULL THEN
911 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_S');
912 fnd_file.put_line(fnd_file.log,fnd_message.get);
913 RAISE param_exception;
914
915 END IF;
916
917
918 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
919
920 --COMPUTATION ONLY IF PERSON NUMBER IS PRESENT
921 IF p_run_type = 'S' AND (p_pid_group IS NULL) AND (p_base_id IS NOT NULL) THEN
922
923 fnd_file.new_line(fnd_file.log,1);
924 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
925 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(p_base_id));
926 fnd_file.put_line(fnd_file.log,fnd_message.get);
927
928 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
929 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=S with base_id:'||p_base_id);
930 END IF;
931
932 --Call evaluate procedure for COA re-computation
933 evaluate(p_base_id,lv_ci_cal_type,ln_ci_sequence_number);
934
935 --Update To Do Items
936 update_to_do_items(p_base_id,lv_ci_cal_type,ln_ci_sequence_number);
937
938 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
939 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=S done');
940 END IF;
941
942 --COMPUTATION FOR AWARD YEAR ONLY
943 ELSIF p_run_type = 'Y' AND (p_pid_group IS NULL) AND (p_base_id IS NULL) THEN
944 FOR l_per_awd_rec IN c_per_awd_yr(lv_ci_cal_type,ln_ci_sequence_number)
945 LOOP
946 fnd_file.new_line(fnd_file.log,1);
947 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
948 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(l_per_awd_rec.base_id));
949 fnd_file.put_line(fnd_file.log,fnd_message.get);
950
951 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
952 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=Y with base_id:'||l_per_awd_rec.base_id);
953 END IF;
954
955 --Call evaluate procedure for COA re-computation
956 evaluate(l_per_awd_rec.base_id,lv_ci_cal_type,ln_ci_sequence_number);
957
958 --Update To Do Items
959 update_to_do_items(l_per_awd_rec.base_id,lv_ci_cal_type,ln_ci_sequence_number);
960
961 END LOOP;
962
963 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
964 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=Y done');
965 END IF;
966
967 --COMPUTATION FOR ALL PERSONS IN THE PERSON ID GROUP
968 ELSIF (p_run_type = 'P' AND p_pid_group IS NOT NULL) THEN
969 --Bug #5021084
970 lv_sql_stmt := igf_ap_ss_pkg.get_pid(p_pid_group,lv_status,lv_group_type);
971
972 --Bug #5021084. Passing Group ID if the group type is STATIC.
973 IF lv_group_type = 'STATIC' THEN
974 OPEN cur_per_grp FOR
975 'SELECT person_id,
976 person_number
977 FROM igs_pe_person_base_v
978 WHERE person_id IN ('||lv_sql_stmt||') ' USING p_pid_group;
979 ELSIF lv_group_type = 'DYNAMIC' THEN
980 OPEN cur_per_grp FOR
981 'SELECT person_id,
982 person_number
983 FROM igs_pe_person_base_v
984 WHERE person_id IN ('||lv_sql_stmt||')';
985 END IF;
986
987 FETCH cur_per_grp INTO per_grp_rec;
988
989 IF (cur_per_grp%NOTFOUND) THEN
990 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
991 fnd_file.put_line(fnd_file.log,fnd_message.get);
992 ELSE
993 LOOP
994 -- check if person has a fa base record
995 ln_base_id := NULL;
996 lv_err_msg := NULL;
997
998 igf_gr_gen.get_base_id(
999 lv_ci_cal_type,
1000 ln_ci_sequence_number,
1001 per_grp_rec.person_id,
1002 ln_base_id,
1003 lv_err_msg
1004 );
1005
1006 IF lv_err_msg = 'NULL' THEN
1007 fnd_file.new_line(fnd_file.log,1);
1008 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
1009 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(ln_base_id));
1010 fnd_file.put_line(fnd_file.log,fnd_message.get);
1011
1012 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1013 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=P with base_id:'||ln_base_id);
1014 END IF;
1015
1016 --Call evaluate procedure for COA re-computation
1017 evaluate(ln_base_id,lv_ci_cal_type,ln_ci_sequence_number);
1018
1019 --Update To Do Items
1020 update_to_do_items(ln_base_id,lv_ci_cal_type,ln_ci_sequence_number);
1021
1022 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1023 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=P done');
1024 END IF;
1025
1026 ELSE
1027 OPEN c_person_no(per_grp_rec.person_id);
1028 FETCH c_person_no INTO l_person_no;
1029 CLOSE c_person_no;
1030
1031 fnd_message.set_name('IGF','IGF_AP_NO_BASEREC');
1032 fnd_message.set_token('STUD',l_person_no.party_number);
1033 fnd_file.new_line(fnd_file.log,1);
1034 fnd_file.put_line(fnd_file.log,fnd_message.get);
1035 END IF;
1036
1037 FETCH cur_per_grp INTO per_grp_rec;
1038 EXIT WHEN cur_per_grp%NOTFOUND;
1039 END LOOP;
1040 CLOSE cur_per_grp;
1041
1042 END IF; -- end of IF (cur_per_grp%NOTFOUND)
1043
1044
1045 --COMPUTATION FOR AUTO SELECT PERSONS ONLY
1046 ELSIF p_run_type = 'A' AND (p_pid_group IS NULL) AND (p_base_id IS NULL) THEN
1047
1048 FOR l_person_id IN cur_person_id(lv_ci_cal_type,ln_ci_sequence_number)
1049 LOOP
1050 -- check if person has a fa base record
1051 ln_base_id := NULL;
1052 lv_err_msg := NULL;
1053
1054 igf_gr_gen.get_base_id(
1055 lv_ci_cal_type,
1056 ln_ci_sequence_number,
1057 l_person_id.person_id,
1058 ln_base_id,
1059 lv_err_msg
1060 );
1061
1062 IF lv_err_msg = 'NULL' THEN
1063 fnd_file.new_line(fnd_file.log,1);
1064 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
1065 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(ln_base_id));
1066 fnd_file.put_line(fnd_file.log,fnd_message.get);
1067
1068 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1069 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=A with base_id:'||ln_base_id);
1070 END IF;
1071
1072 --Call evaluate procedure for COA re-computation
1073 evaluate(ln_base_id,lv_ci_cal_type,ln_ci_sequence_number);
1074
1075 FOR l_person_ref IN c_person_ref(lv_ci_cal_type,ln_ci_sequence_number,l_person_id.person_id,l_person_id.s_student_todo_type,l_person_id.sequence_number)
1076 LOOP
1077 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1078 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Updating table igs_pe_std_todo for person id: '||l_person_id.person_id);
1079 END IF;
1080
1081 igs_pe_std_todo_ref_pkg.update_row(
1082 x_rowid => l_person_ref.row_id,
1083 x_person_id => l_person_ref.person_id,
1084 x_s_student_todo_type => l_person_ref.s_student_todo_type,
1085 x_sequence_number => l_person_ref.sequence_number,
1086 x_reference_number => l_person_ref.reference_number,
1087 x_cal_type => l_person_ref.cal_type,
1088 x_ci_sequence_number => l_person_ref.ci_sequence_number,
1089 x_course_cd => l_person_ref.course_cd,
1090 x_unit_cd => l_person_ref.unit_cd,
1091 x_other_reference => l_person_ref.other_reference,
1092 x_logical_delete_dt => sysdate,
1093 x_mode => 'R',
1094 x_uoo_id => l_person_ref.uoo_id
1095 );
1096 END LOOP;
1097
1098 OPEN c_person_todo(l_person_id.person_id,l_person_id.s_student_todo_type,l_person_id.sequence_number);
1099 FETCH c_person_todo INTO l_person_todo;
1100
1101 IF c_person_todo%FOUND THEN
1102
1103 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1104 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','c_person_todo%FOUND');
1105 END IF;
1106
1107 igs_pe_std_todo_pkg.update_row(
1108 x_rowid => l_person_todo.row_id,
1109 x_person_id => l_person_todo.person_id,
1110 x_s_student_todo_type => l_person_todo.s_student_todo_type,
1111 x_sequence_number => l_person_todo.sequence_number,
1112 x_todo_dt => l_person_todo.todo_dt,
1113 x_logical_delete_dt => sysdate,
1114 x_mode => 'R'
1115 );
1116 END IF;
1117 CLOSE c_person_todo;
1118
1119 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1120 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=A done');
1121 END IF;
1122
1123 ELSE
1124 OPEN c_person_no(l_person_id.person_id);
1125 FETCH c_person_no INTO l_person_no;
1126 CLOSE c_person_no;
1127
1128 fnd_message.set_name('IGF','IGF_AP_NO_BASEREC');
1129 fnd_message.set_token('STUD',l_person_no.party_number);
1130 fnd_file.new_line(fnd_file.log,1);
1131 fnd_file.put_line(fnd_file.log,fnd_message.get);
1132 END IF;
1133
1134 END LOOP;
1135
1136 END IF;
1137
1138 fnd_file.new_line(fnd_file.log,1);
1139 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
1140
1141
1142 COMMIT;
1143
1144 EXCEPTION
1145 WHEN param_exception THEN
1146 retcode:=2;
1147 fnd_message.set_name('IGF','IGF_AW_PARAM_ERR');
1148 igs_ge_msg_stack.add;
1149 errbuf := fnd_message.get;
1150
1151 WHEN app_exception.record_lock_exception THEN
1152 ROLLBACK;
1153 retcode:=2;
1154 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
1155 igs_ge_msg_stack.add;
1156 errbuf := fnd_message.get;
1157
1158 WHEN OTHERS THEN
1159 ROLLBACK;
1160 retcode:=2;
1161 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1162 igs_ge_msg_stack.add;
1163 errbuf := fnd_message.get || SQLERRM;
1164 END main;
1165
1166 END igf_aw_coa_update;
[Home] [Help]