1 PACKAGE BODY igf_ap_todo_grps_prc_pkg AS
2 /* $Header: IGFAP28B.pls 120.10 2006/04/20 02:58:36 veramach ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: IGF_AP_TODO_GRPS_PRC_PKG |
10 | |
11 | NOTES: |
12 | This process adds one or more To Do Items to the given Student or to|
13 | the group of students using Person ID Groups for a given Award Year.|
14 | |
15 | HISTORY |
16 | Who When What |
17 | bvisvana 05-Jul-2005 Bug# 3440755. When no record for Person group|
18 | (ln_stdnt_count = 0), set retcode = 0 not 2 |
19 | bvisvana 20-Jun-2005 -FA 140 - To Do Item Process |
20 | Included Status 1 to Status 15 corresponding |
21 | to the 15 to do items. |
22 | -If the TO DO item is CAREER item then it is |
23 | updated for the base_id with which it was |
24 | added. PREFLEND to do item is not updated. |
25 | Skipped when trying to add it again. |
26 | - main proc and log_input_params gets changed|
27 | cdcruz 01-Oct-2003 Bug 3085558 - FA121 Verificaion Wksht |
28 | all parameters made optional now |
29 | bkkumar 04-jun-2003 Bug #2858504
30 | Added legacy_record_flag
31 | in the table handler calls for
32 | igf_ap_td_item_inst_pkg.insert_row
33 | rasingh 09-Jan-2003 Bug 2738442, Message IGF_AP_INVALID_QUERY |
34 | added |
35 | brajendr 19-OCT-2002 Bug # 2632359 |
36 | Modified the Log file contents with |
37 | descriptive messages |
38 |
39 | brajendr 12-Oct-2002 Changes the paramter order as specified in |
40 | the concurrent Job |
41 | Modified the Messages IGF_AP_NO_BASEID |
42 | bkkumar 04-jun-2003 Bug #2858504 |
43 | Added legacy_record_flag |
44 | in the table handler calls for |
45 | igf_ap_td_item_inst_pkg.insert_row |
46 | gvarapra 13-sep-2004 Added the validation to auto update the |
47 | To Do Item status that are attached to the |
48 | system to do type of ISIR or PROFILE. |
49 *=======================================================================*/
50
51 -- global Variables
52 ln_skip_items NUMBER := 0;
53 ln_item_cnt NUMBER := 0;
54 l_has_payment_isir VARCHAR2(1) := NULL;
55 l_has_active_profile VARCHAR2(1) := NULL;
56 l_has_isir VARCHAR2(1) := NULL;
57 l_has_profile VARCHAR2(1) := NULL;
58
59 PROCEDURE log_input_params(
60 p_awd_cal_type IN igs_ca_inst.cal_type%TYPE,
61 p_awd_seq_num IN igs_ca_inst.sequence_number%TYPE,
62 p_base_id IN NUMBER,
63 p_person_id_grp IN NUMBER,
64 p_upd_mode IN VARCHAR2,
65 p_item_number_1 IN NUMBER,
66 p_status_1 IN VARCHAR,
67 p_item_number_2 IN NUMBER,
68 p_status_2 IN VARCHAR,
69 p_item_number_3 IN NUMBER,
70 p_status_3 IN VARCHAR,
71 p_item_number_4 IN NUMBER,
72 p_status_4 IN VARCHAR,
73 p_item_number_5 IN NUMBER,
74 p_status_5 IN VARCHAR,
75 p_item_number_6 IN NUMBER,
76 p_status_6 IN VARCHAR,
77 p_item_number_7 IN NUMBER,
78 p_status_7 IN VARCHAR,
79 p_item_number_8 IN NUMBER,
80 p_status_8 IN VARCHAR,
81 p_item_number_9 IN NUMBER,
82 p_status_9 IN VARCHAR,
83 p_item_number_10 IN NUMBER,
84 p_status_10 IN VARCHAR,
85 p_item_number_11 IN NUMBER,
86 p_status_11 IN VARCHAR,
87 p_item_number_12 IN NUMBER,
88 p_status_12 IN VARCHAR,
89 p_item_number_13 IN NUMBER,
90 p_status_13 IN VARCHAR,
91 p_item_number_14 IN NUMBER,
92 p_status_14 IN VARCHAR,
93 p_item_number_15 IN NUMBER,
94 p_status_15 IN VARCHAR
95 ) AS
96 /*
97 || Created By : brajendr
98 || Created On : 23-Sep-2002
99 || Purpose : Logs all the Input Parameters
100 || Known limitations, enhancements or remarks :
101 || Change History :
102 || Who When What
103 || bvisvana 20-Jun-2005 Moved the log printing inside IF condition
104 || i.e If the item is NOT NULL then log it else don't log it
105 || Signature of log_input_params changed
106 || (reverse chronological order - newest change first)
107 */
108
109 --Cursor to find the User Parameter Award Year (which is same as Alternate Code) to display in the Log
110 CURSOR c_alternate_code(
111 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
112 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE
113 ) IS
114 SELECT alternate_code
115 FROM igs_ca_inst
116 WHERE cal_type = cp_ci_cal_type
117 AND sequence_number = cp_ci_sequence_number;
118
119 CURSOR c_get_parameters IS
120 SELECT meaning, lookup_code
121 FROM igf_lookups_view
122 WHERE lookup_type='IGF_GE_PARAMETERS'
123 AND lookup_code IN ('AWARD_YEAR','PERSON_NUMBER','PERSON_ID_GROUP','ITEM_CODE','PARAMETER_PASS','UPDATE_TD_MODE');
124
125 -- Get the details of Item codes and its descritpions
126 CURSOR c_item_details(
127 cp_todo_number igf_ap_td_item_mst.todo_number%TYPE
128 ) IS
129 SELECT description
130 FROM igf_ap_td_item_mst
131 WHERE todo_number = cp_todo_number;
132
133 -- Get the details of
134 CURSOR c_person_number(
135 cp_base_id IN NUMBER
136 ) IS
137 SELECT pe.person_number
138 FROM igs_pe_person_base_v pe,
139 igf_ap_fa_base_rec_all fa
140 WHERE pe.person_id = fa.person_id
141 AND fa.base_id = cp_base_id;
142
143 -- Get the details of
144 CURSOR c_person_group(
145 cp_person_id_grp IN NUMBER
146 ) IS
147 SELECT description
148 FROM igs_pe_all_persid_group_v
149 WHERE group_id = cp_person_id_grp;
150
151 -- Get Item Description
152 CURSOR c_item_descrption(
153 cp_item_number IN NUMBER
154 ) IS
155 SELECT description
156 FROM igf_ap_td_item_mst_all
157 WHERE todo_number = cp_item_number;
158
159 parameter_rec c_get_parameters%ROWTYPE;
160 lc_item_description igf_ap_td_item_mst.description%TYPE;
161 lv_awd_alternate_code igs_ca_inst.alternate_code%TYPE;
162 lv_award_year_pmpt igf_lookups_view.meaning%TYPE;
163 lv_person_number_pmpt igf_lookups_view.meaning%TYPE;
164 lv_person_id_grp_pmpt igf_lookups_view.meaning%TYPE;
165 lv_item_code_pmpt igf_lookups_view.meaning%TYPE;
166 lv_status_pmpt igf_lookups_view.meaning%TYPE;
167 l_para_pass igf_lookups_view.meaning%TYPE;
168 l_person_number hz_parties.party_number%TYPE;
169 l_group_desc igs_pe_persid_group_all.description%TYPE;
170 l_item_description igf_ap_td_item_mst_all.description%TYPE;
171 l_upd_mode_pmpt igf_lookups_view.meaning%TYPE;
172
173 BEGIN
174
175 -- Set all the Prompts for the Input Parameters
176 lv_status_pmpt := igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','STATUS');
177
178 OPEN c_get_parameters;
179 LOOP
180 FETCH c_get_parameters INTO parameter_rec;
181 EXIT WHEN c_get_parameters%NOTFOUND;
182
183 IF parameter_rec.lookup_code ='AWARD_YEAR' THEN
184 lv_award_year_pmpt := TRIM(parameter_rec.meaning);
185
186 ELSIF parameter_rec.lookup_code ='PERSON_NUMBER' THEN
187 lv_person_number_pmpt := TRIM(parameter_rec.meaning);
188
189 ELSIF parameter_rec.lookup_code ='PERSON_ID_GROUP' THEN
190 lv_person_id_grp_pmpt := TRIM(parameter_rec.meaning);
191
192 ELSIF parameter_rec.lookup_code ='ITEM_CODE' THEN
193 lv_item_code_pmpt := TRIM(parameter_rec.meaning);
194
195 ELSIF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
196 l_para_pass := TRIM(parameter_rec.meaning);
197
198 ELSIF parameter_rec.lookup_code ='UPDATE_TD_MODE' THEN
199 l_upd_mode_pmpt := TRIM(parameter_rec.meaning);
200
201 END IF;
202
203 END LOOP;
204 CLOSE c_get_parameters;
205
206 -- Get the Award Year Alternate Code
207 OPEN c_alternate_code(p_awd_cal_type, p_awd_seq_num);
208 FETCH c_alternate_code INTO lv_awd_alternate_code;
209 CLOSE c_alternate_code;
210
211 -- Get the Person Number
212 OPEN c_person_number(p_base_id);
213 FETCH c_person_number INTO l_person_number;
214 CLOSE c_person_number;
215
216 -- Get the Person Group
217 OPEN c_person_group(p_person_id_grp);
218 FETCH c_person_group INTO l_group_desc;
219 CLOSE c_person_group;
220
221 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
222 FND_FILE.PUT_LINE(FND_FILE.LOG, l_para_pass); --------------Parameters Passed--------------
223 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
224
225 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_award_year_pmpt,40) || ' : '|| lv_awd_alternate_code);
226 IF l_group_desc IS NOT NULL THEN
227 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_person_id_grp_pmpt,40) || ' : '|| l_group_desc);
228 END IF;
229 IF l_person_number IS NOT NULL THEN
230 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_person_number_pmpt,40) || ' : '|| l_person_number);
231 END IF;
232
233 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(l_upd_mode_pmpt,40) || ' : '|| igf_aw_gen.lookup_desc('IGF_AP_TD_UPD_MODE',p_upd_mode));
234
235 l_item_description := NULL;
236 IF p_item_number_1 IS NOT NULL THEN
237 OPEN c_item_descrption(p_item_number_1);
238 FETCH c_item_descrption INTO l_item_description;
239 CLOSE c_item_descrption;
240 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 1', 40) || ' : ' || l_item_description );
241 IF p_status_1 IS NOT NULL THEN
242 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_1));
243 END IF;
244 END IF;
245
246 l_item_description := NULL;
247 IF p_item_number_2 IS NOT NULL THEN
248 OPEN c_item_descrption(p_item_number_2);
249 FETCH c_item_descrption INTO l_item_description;
250 CLOSE c_item_descrption;
251 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 2', 40) || ' : ' || l_item_description );
252 IF p_status_2 IS NOT NULL THEN
253 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_2));
254 END IF;
255 END IF ;
256
257 l_item_description := NULL;
258 IF p_item_number_3 IS NOT NULL THEN
259 OPEN c_item_descrption(p_item_number_3);
260 FETCH c_item_descrption INTO l_item_description;
261 CLOSE c_item_descrption;
262 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 3', 40) || ' : ' || l_item_description );
263 IF p_status_3 IS NOT NULL THEN
264 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_3));
265 END IF;
266 END IF;
267
268 l_item_description := NULL;
269 IF p_item_number_4 IS NOT NULL THEN
270 OPEN c_item_descrption(p_item_number_4);
271 FETCH c_item_descrption INTO l_item_description;
272 CLOSE c_item_descrption;
273 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 4', 40) || ' : ' || l_item_description );
274 IF p_status_4 IS NOT NULL THEN
275 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_4));
276 END IF;
277 END IF ;
278
279 l_item_description := NULL;
280 IF p_item_number_5 IS NOT NULL THEN
281 OPEN c_item_descrption(p_item_number_5);
282 FETCH c_item_descrption INTO l_item_description;
283 CLOSE c_item_descrption;
284 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 5', 40) || ' : ' || l_item_description );
285 IF p_status_5 IS NOT NULL THEN
286 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_5));
287 END IF;
288 END IF;
289
290 l_item_description := NULL;
291 IF p_item_number_6 IS NOT NULL THEN
292 OPEN c_item_descrption(p_item_number_6);
293 FETCH c_item_descrption INTO l_item_description;
294 CLOSE c_item_descrption;
295 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 6', 40) || ' : ' || l_item_description );
296 IF p_status_6 IS NOT NULL THEN
300
297 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_6));
298 END IF;
299 END IF;
301 l_item_description := NULL;
302 IF p_item_number_7 IS NOT NULL THEN
303 OPEN c_item_descrption(p_item_number_7);
304 FETCH c_item_descrption INTO l_item_description;
305 CLOSE c_item_descrption;
306 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 7', 40) || ' : ' || l_item_description );
307 IF p_status_7 IS NOT NULL THEN
308 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_7));
309 END IF;
310 END IF;
311
312 l_item_description := NULL;
313 IF p_item_number_8 IS NOT NULL THEN
314 OPEN c_item_descrption(p_item_number_8);
315 FETCH c_item_descrption INTO l_item_description;
316 CLOSE c_item_descrption;
317 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 8', 40) || ' : ' || l_item_description );
318 IF p_status_8 IS NOT NULL THEN
319 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_8));
320 END IF;
321 END IF;
322
323 l_item_description := NULL;
324 IF p_item_number_9 IS NOT NULL THEN
325 OPEN c_item_descrption(p_item_number_9);
326 FETCH c_item_descrption INTO l_item_description;
327 CLOSE c_item_descrption;
328 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 9', 40) || ' : ' || l_item_description );
329 IF p_status_9 IS NOT NULL THEN
330 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_9));
331 END IF;
332 END IF;
333
334 l_item_description := NULL;
335 IF p_item_number_10 IS NOT NULL THEN
336 OPEN c_item_descrption(p_item_number_10);
337 FETCH c_item_descrption INTO l_item_description;
338 CLOSE c_item_descrption;
339 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 10', 40) || ' : ' || l_item_description );
340 IF p_status_10 IS NOT NULL THEN
341 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_10));
342 END IF;
343 END IF;
344
345 l_item_description := NULL;
346 IF p_item_number_11 IS NOT NULL THEN
347 OPEN c_item_descrption(p_item_number_11);
348 FETCH c_item_descrption INTO l_item_description;
349 CLOSE c_item_descrption;
350 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 11', 40) || ' : ' || l_item_description );
351 IF p_status_11 IS NOT NULL THEN
352 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_11));
353 END IF;
354 END IF;
355
356 l_item_description := NULL;
357 IF p_item_number_12 IS NOT NULL THEN
358 OPEN c_item_descrption(p_item_number_12);
359 FETCH c_item_descrption INTO l_item_description;
360 CLOSE c_item_descrption;
361 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 12', 40) || ' : ' || l_item_description );
362 IF p_status_12 IS NOT NULL THEN
363 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_12));
364 END IF;
365 END IF;
366
367 l_item_description := NULL;
368 IF p_item_number_13 IS NOT NULL THEN
369 OPEN c_item_descrption(p_item_number_13);
370 FETCH c_item_descrption INTO l_item_description;
371 CLOSE c_item_descrption;
372 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 13', 40) || ' : ' || l_item_description );
373 IF p_status_13 IS NOT NULL THEN
374 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_13));
375 END IF;
376 END IF;
377
378 l_item_description := NULL;
379 IF p_item_number_14 IS NOT NULL THEN
380 OPEN c_item_descrption(p_item_number_14);
381 FETCH c_item_descrption INTO l_item_description;
382 CLOSE c_item_descrption;
383 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 14', 40) || ' : ' || l_item_description );
384 IF p_status_14 IS NOT NULL THEN
385 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_14));
386 END IF;
387 END IF;
388
389 l_item_description := NULL;
390 IF p_item_number_15 IS NOT NULL THEN
391 OPEN c_item_descrption(p_item_number_15);
392 FETCH c_item_descrption INTO l_item_description;
393 CLOSE c_item_descrption;
394 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 15', 40) || ' : ' || l_item_description );
395 IF p_status_15 IS NOT NULL THEN
396 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_status_pmpt || ' 1', 40) || ' : ' || igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',p_status_15));
397 END IF;
398 END IF;
399
400 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
401 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------');
402 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
403 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
404
405 EXCEPTION
406 WHEN others THEN
407 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
411
408 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_TODO_GRPS_PRC_PKG.LOG_INPUT_PARAMS');
409 IGS_GE_MSG_STACK.ADD;
410 APP_EXCEPTION.RAISE_EXCEPTION;
412 END log_input_params;
413
414
415 PROCEDURE add_to_do(
416 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
417 p_todo_number IN igf_ap_td_item_mst_all.todo_number%TYPE,
418 p_item_cnt IN NUMBER,
419 p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
420 p_status IN igf_ap_td_item_inst_all.status%TYPE,
421 p_upd_mode IN VARCHAR2
422 ) AS
423 /*
424 || Created By : brajendr
425 || Created On :
426 || Purpose :
427 || Known limitations, enhancements or remarks :
428 || Change History :
429 || Who When What
430 || bvisvana 21-Jun-2005 Added a parameter p_status to the procedure's signature
431 || Updation of Status field if already present
432 || If item is PREFLEND, then the base id with which it was added is updated not with
433 || the current base id
434 || (reverse chronological order - newest change first)
435 */
436
437 -- Get the details of Item codes and its descritpions
438 CURSOR c_item_details(
439 cp_todo_number igf_ap_td_item_mst.todo_number%TYPE
440 ) IS
441 SELECT item_code, description, required_for_application, freq_attempt, max_attempt, career_item, system_todo_type_code
442 FROM igf_ap_td_item_mst
443 WHERE todo_number = cp_todo_number;
444
445
446 CURSOR c_chk_career_exists(
447 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
448 cp_todo_number igf_ap_td_item_mst.todo_number%TYPE
449 ) IS
450 SELECT 1 value, fab.base_id base_id
451 FROM igf_ap_fa_base_rec fab,
452 igf_ap_td_item_inst inst,
453 igf_ap_td_item_mst mst
454 WHERE fab.person_id = cp_person_id
455 AND mst.todo_number = cp_todo_number
456 AND mst.career_item = 'Y'
457 AND inst.item_sequence_number = mst.todo_number
458 AND inst.base_id = fab.base_id;
459
460 --FA 140 - To Avoid skipping of items if already present. Need to update
461 CURSOR c_to_do_item (
462 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
463 cp_item_sequence_number igf_ap_td_item_inst_all.item_sequence_number%TYPE
464 ) IS
465 SELECT item_inst.ROWID row_id, item_inst.*
466 FROM igf_ap_td_item_inst_all item_inst
467 WHERE base_id = cp_base_id
468 AND item_sequence_number = cp_item_sequence_number;
469
470 CURSOR c_check_preflender(cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE) IS
471 SELECT 'x'
472 FROM
473 igf_sl_cl_pref_lenders
474 WHERE person_id = cp_person_id AND
475 TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND NVL(TRUNC(end_date),TRUNC(sysdate));
476
477
478 lc_item_details c_item_details%ROWTYPE;
479 lc_chk_career_exists c_chk_career_exists%ROWTYPE;
480 lv_rowid ROWID;
481 lb_item_present BOOLEAN := FALSE;
482 l_status igf_ap_td_item_inst_all.status%TYPE := 'REQ';
483
484 lc_check_preflender c_check_preflender%ROWTYPE;
485 lc_to_do_item c_to_do_item%ROWTYPE;
486 l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
487
488
489 BEGIN
490
491 l_base_id := p_base_id;
492 -- check whether the TO Do item is a Career Item, then there should be one instance it for a student acrosss Award Years
493 -- bvisvana - If this is a career item do not return, instead get the base_id of the this item to which it was added
494 -- Use the base_id to validate for item present and update
495 OPEN c_chk_career_exists(p_person_id, p_todo_number);
496 FETCH c_chk_career_exists INTO lc_chk_career_exists;
497 CLOSE c_chk_career_exists;
498 IF lc_chk_career_exists.value = 1 THEN
499 l_base_id := lc_chk_career_exists.base_id;
500 END IF;
501
502 -- Get the details of Item Codes like Required for application, item_code, description
503 OPEN c_item_details( p_todo_number);
504 FETCH c_item_details INTO lc_item_details;
505 CLOSE c_item_details;
506
507 IF lc_item_details.system_todo_type_code = 'ISIR' THEN
508 IF l_has_isir = 'Y' THEN
509 IF l_has_payment_isir = 'Y' THEN
510 l_status := 'COM';
511 ELSE
512 l_status := 'REC';
513 END IF;
514 ELSE
515 l_status := 'REQ';
516 END IF;
517 ELSIF lc_item_details.system_todo_type_code = 'PROFILE' THEN
518 IF l_has_profile = 'Y' THEN
519 IF l_has_active_profile = 'Y' THEN
520 l_status := 'COM';
521 ELSE
522 l_status := 'REC';
523 END IF;
524 ELSE
525 l_status := 'REQ';
526 END IF;
530 l_status := p_status;
527 -- bvisvana - FA 140 - if Others and Instapp, then consider the status if specified else it goes as 'REQ'
528 -- So as per FA 140 Pref Lender item of Type "PREFLEND" would go as "REQ"
529 ELSIF lc_item_details.system_todo_type_code IN ('OTHERS','INSTAPP') AND p_status IS NOT NULL THEN
531 END IF;
532
533 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
534 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','l_status:'||l_status);
535 END IF;
536
537 -- To check whether the SYSTEM_TODO_TYPE_CODE = 'PREFLEND' and if so check whether a active pref lender exists for the person
538 IF lc_item_details.system_todo_type_code = 'PREFLEND' THEN
539 OPEN c_check_preflender(cp_person_id => p_person_id);
540 FETCH c_check_preflender INTO lc_check_preflender;
541 IF c_check_preflender%FOUND THEN
542 -- If already an active preflender present - then Skip that item for processing and log a message
543 fnd_message.set_name('IGF','IGF_AP_LEND_TD_NO_ASSGN');
544 fnd_message.set_token('ITEM',lc_item_details.description);
545 FND_FILE.PUT_LINE(FND_FILE.LOG ,FND_MESSAGE.GET);
546 ln_skip_items := ln_skip_items + 1;
547 RETURN;
548 END IF;
549 CLOSE c_check_preflender;
550 END IF;
551
552 -- Check whether the TO DO items was already added or not.
553 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
554 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','opening c_to_do_item with base_id/item_seq_number:'||l_base_id||'/'||p_todo_number);
555 END IF;
556 OPEN c_to_do_item(cp_base_id => l_base_id,
557 cp_item_sequence_number => p_todo_number);
558 FETCH c_to_do_item INTO lc_to_do_item;
559 IF c_to_do_item%FOUND THEN
560 lb_item_present := TRUE;
561 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
562 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','p_todo_number:'||p_todo_number||' already present for base_id:'||p_base_id);
563 END IF;
564 ELSE
565 lb_item_present := FALSE;
566 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','p_todo_number:'||p_todo_number||' not present for base_id:'||p_base_id);
568 END IF;
569 END IF;
570 CLOSE c_to_do_item;
571
572 IF lb_item_present = TRUE THEN
573 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
574 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','opening c_to_do_item for p_todo_number:'||p_todo_number||' and base_id:'||l_base_id);
575 END IF;
576 OPEN c_to_do_item(cp_base_id => l_base_id,
577 cp_item_sequence_number => p_todo_number);
578 FETCH c_to_do_item INTO lc_to_do_item;
579 CLOSE c_to_do_item;
580 IF lc_to_do_item.inactive_flag = 'Y' THEN
581 fnd_message.set_name('IGF','IGF_AP_INAC_TD_SKIP');
582 fnd_message.set_token('ITEMCODE',lc_item_details.description);
583 FND_FILE.PUT_LINE(FND_FILE.LOG ,FND_MESSAGE.GET);
584 ln_skip_items := ln_skip_items + 1;
585 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
586 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','p_todo_number:'||p_todo_number||' skipped as it is inactive');
587 END IF;
588 RETURN;
589 END IF;
590 /*
591 Here, we have to update the item based on the following conditions:
592 If p_upd_mode = UPD, then update the item at all times
593 If p_upd_mode = NO_UPD_IF_COMP, then update is the status is NOT COM
594
595 */
596 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
597 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','p_upd_mode:'||p_todo_number||' and lc_to_do_item.status:'||lc_to_do_item.status);
598 END IF;
599 IF p_upd_mode = 'UPD' OR (p_upd_mode = 'NO_UPD_IF_COMP' AND lc_to_do_item.status <> 'COM') THEN
600 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
601 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','calling igf_ap_td_item_inst_pkg.update_row with base_id/item_seq_number:'||lc_to_do_item.base_id||'/'||lc_to_do_item.item_sequence_number);
602 END IF;
603 igf_ap_td_item_inst_pkg.update_row (
604 x_mode => 'R',
605 x_rowid => lc_to_do_item.row_id,
606 x_base_id => lc_to_do_item.base_id,
607 x_item_sequence_number => lc_to_do_item.item_sequence_number,
608 x_status => l_status,
609 x_status_date => TRUNC(SYSDATE),
610 x_add_date => lc_to_do_item.add_date,
611 x_corsp_date => lc_to_do_item.corsp_date,
612 x_corsp_count => lc_to_do_item.corsp_count,
613 x_inactive_flag => lc_to_do_item.inactive_flag,
617 x_legacy_record_flag => lc_to_do_item.legacy_record_flag,
614 x_required_for_application => lc_to_do_item.required_for_application,
615 x_freq_attempt => lc_to_do_item.freq_attempt,
616 x_max_attempt => lc_to_do_item.max_attempt,
618 x_clprl_id => lc_to_do_item.clprl_id
619 );
620
621 -- Log message with the Updated To Do Item.
622 fnd_message.set_name('IGF','IGF_AP_TODO_UPD');
623 fnd_message.set_token('ITEM',lc_item_details.description);
624 fnd_message.set_token('STATUS',igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',l_status));
625 FND_FILE.PUT_LINE(FND_FILE.LOG ,FND_MESSAGE.GET);
626 END IF;
627 ELSIF lb_item_present = FALSE THEN
628 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
629 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','calling igf_ap_td_item_inst_pkg.insert_row with base_id/item_seq_number:'||l_base_id||'/'||p_todo_number);
630 END IF;
631 igf_ap_td_item_inst_pkg.insert_row (
632 x_mode => 'R',
633 x_rowid => lv_rowid,
634 x_base_id => l_base_id,
635 x_item_sequence_number => p_todo_number,
636 x_status => l_status,
637 x_status_date => TRUNC(SYSDATE),
638 x_add_date => TRUNC(SYSDATE),
639 x_corsp_date => NULL,
640 x_corsp_count => NULL,
641 x_inactive_flag => 'N',
642 x_required_for_application => lc_item_details.required_for_application,
643 x_freq_attempt => lc_item_details.freq_attempt,
644 x_max_attempt => lc_item_details.max_attempt,
645 x_legacy_record_flag => NULL,
646 x_clprl_id => NULL
647 );
648
649 -- Log message with the added To Do Item added to the student.
650 fnd_message.set_name('IGF','IGF_AP_TODO_ASSIGN');
651 fnd_message.set_token('ITEM',lc_item_details.description);
652 fnd_message.set_token('STATUS',igf_aw_gen.lookup_desc('IGF_TD_ITEM_STATUS',l_status));
653 FND_FILE.PUT_LINE(FND_FILE.LOG ,FND_MESSAGE.GET);
654 END IF;
655
656 EXCEPTION
657 WHEN others THEN
658 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
659 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_TODO_GRPS_PRC_PKG.ADD_TO_DO');
660 IGS_GE_MSG_STACK.ADD;
661 APP_EXCEPTION.RAISE_EXCEPTION;
662
663 END add_to_do;
664
665 FUNCTION assign_todo(
666 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
667 p_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE,
668 p_awd_cal_type IN igs_ca_inst.cal_type%TYPE,
669 p_awd_seq_num IN igs_ca_inst.sequence_number%TYPE,
670 p_upd_mode IN VARCHAR2,
671 p_item_number_1 IN igf_ap_td_item_mst_all.todo_number%TYPE,
672 p_status_1 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
673 p_item_number_2 IN igf_ap_td_item_mst_all.todo_number%TYPE,
674 p_status_2 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
675 p_item_number_3 IN igf_ap_td_item_mst_all.todo_number%TYPE,
676 p_status_3 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
677 p_item_number_4 IN igf_ap_td_item_mst_all.todo_number%TYPE,
678 p_status_4 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
679 p_item_number_5 IN igf_ap_td_item_mst_all.todo_number%TYPE,
680 p_status_5 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
681 p_item_number_6 IN igf_ap_td_item_mst_all.todo_number%TYPE,
682 p_status_6 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
683 p_item_number_7 IN igf_ap_td_item_mst_all.todo_number%TYPE,
684 p_status_7 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
685 p_item_number_8 IN igf_ap_td_item_mst_all.todo_number%TYPE,
686 p_status_8 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
687 p_item_number_9 IN igf_ap_td_item_mst_all.todo_number%TYPE,
688 p_status_9 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
689 p_item_number_10 IN igf_ap_td_item_mst_all.todo_number%TYPE,
690 p_status_10 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
691 p_item_number_11 IN igf_ap_td_item_mst_all.todo_number%TYPE,
692 p_status_11 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
693 p_item_number_12 IN igf_ap_td_item_mst_all.todo_number%TYPE,
694 p_status_12 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
695 p_item_number_13 IN igf_ap_td_item_mst_all.todo_number%TYPE,
696 p_status_13 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
697 p_item_number_14 IN igf_ap_td_item_mst_all.todo_number%TYPE,
698 p_status_14 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
699 p_item_number_15 IN igf_ap_td_item_mst_all.todo_number%TYPE,
700 p_status_15 IN igf_ap_td_item_inst_all.status%TYPE DEFAULT NULL,
701 p_calling_from IN VARCHAR2
702 ) RETURN BOOLEAN AS
703 /*
707 || Known limitations, enhancements or remarks :
704 || Created By : brajendr
705 || Created On : 23-Sep-2002
706 || Purpose :
708 || Change History :
709 || Who When What
710 || ridas 07-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
711 || bvisvana 21-Jun-2005 Signature of assign_todo is changed to include status from 1 to 15
712 || (reverse chronological order - newest change first)
713 */
714
715 -- Get all Active persons from the given person_id_group.
716 /* Variables for the dynamic person id group */
717 lv_status VARCHAR2(1) := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
718 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
719
720 lv_sql_stmt VARCHAR(32767) := igf_ap_ss_pkg.get_pid(p_person_id_grp,lv_status,lv_group_type);
721
722 TYPE c_person_id_grpCurTyp IS REF CURSOR ;
723 c_person_id_grp c_person_id_grpCurTyp ;
724 TYPE c_person_id_grp_recTyp IS RECORD ( person_id igs_pe_person_base_v.person_id%TYPE, person_number igs_pe_person_base_v.person_number%TYPE, full_name igs_pe_person_base_v.full_name%TYPE );
725 c_person_id_grp_rec c_person_id_grp_recTyp ;
726
727 /* CURSOR c_person_id_grp (cp_person_id_grp igs_pe_persid_group_all.group_id%TYPE) IS
728 SELECT person_id, person_number, full_name
729 FROM igs_pe_prsid_grp_mem_v
730 WHERE group_id = cp_person_id_grp
731 AND TRUNC(SYSDATE) BETWEEN NVL(start_date,TRUNC(SYSDATE)) AND NVL(end_date,TRUNC(SYSDATE));
732 */
733
734
735 -- Check whether the sudent exists in the FA system or not.
736 CURSOR c_fa_base(
737 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
738 cp_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
739 cp_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
740 ) IS
741 SELECT base_id
742 FROM igf_ap_fa_base_rec
743 WHERE person_id = cp_person_id
744 AND ci_cal_type = cp_ci_cal_type
745 AND ci_sequence_number = cp_ci_sequence_number;
746
747
748 -- Get the person number and person name with the person id.
749 CURSOR c_person_details(
750 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
751 ) IS
752 SELECT pe.person_number, pe.full_name person_name, fa.person_id
753 FROM igf_ap_fa_base_rec fa,
754 igs_pe_person_base_v pe
755 WHERE fa.base_id = cp_base_id
756 AND fa.person_id = pe.person_id;
757
758 --- Get the Person Number prompt
759 CURSOR c_get_parameters IS
760 SELECT meaning
761 FROM igf_lookups_view
762 WHERE lookup_type='IGF_GE_PARAMETERS'
763 AND lookup_code IN ('PERSON_NUMBER');
764
765
766 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
767 lc_person_details_rec c_person_details%ROWTYPE;
768 ln_item_cnt NUMBER(3) := 0;
769 l_person_number igf_lookups_view.meaning%TYPE;
770
771
772 PROCEDURE each_student_todo(
773 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
774 p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
775 ) AS
776 /*
777 || Created By : brajendr
778 || Created On :
779 || Purpose :
780 || Known limitations, enhancements or remarks :
781 || Change History :
782 || Who When What
783 || (reverse chronological order - newest change first)
784 */
785 -- Get the isir status for a student
786 CURSOR c_has_payment_isir(
787 p_base_id igf_ap_isir_matched_all.base_id%type
788 )IS
789 SELECT isir_id
790 FROM igf_ap_isir_matched_all
791 WHERE payment_isir='Y'
792 AND base_id=p_base_id
793 AND system_record_type NOT IN ('INTERNAL','SIMULATED');
794
795 -- Get the profile status for a student
796 CURSOR c_has_active_profile(
797 p_base_id igf_ap_css_profile_all.base_id%type
798 )IS
799 SELECT cssp_id
800 FROM igf_ap_css_profile_all
801 WHERE active_profile='Y'
802 AND base_id=p_base_id;
803
804 CURSOR c_has_isir(
805 p_base_id igf_ap_isir_matched_all.base_id%type
806 )IS
807 SELECT isir_id
808 FROM igf_ap_isir_matched_all
809 WHERE base_id=p_base_id
810 AND system_record_type IN ('ORIGINAL');
811
812 -- Get the profile status for a student
813 CURSOR c_has_profile(
814 p_base_id igf_ap_css_profile_all.base_id%type
815 )IS
816 SELECT cssp_id
817 FROM igf_ap_css_profile_all
818 WHERE base_id=p_base_id;
819 lc_has_payment_isir c_has_payment_isir%ROWTYPE;
820 lc_has_isir c_has_isir%ROWTYPE;
821 lc_has_profile c_has_profile%ROWTYPE;
822 lc_has_active_profile c_has_active_profile%ROWTYPE;
823 BEGIN
824
825 ln_skip_items := 0;
826 ln_item_cnt := 0;
827
828 OPEN c_has_isir(p_base_id);
829 FETCH c_has_isir INTO lc_has_isir;
830 IF c_has_isir%FOUND THEN
831 l_has_isir := 'Y';
832 OPEN c_has_payment_isir(p_base_id);
833 FETCH c_has_payment_isir INTO lc_has_payment_isir;
834 IF c_has_payment_isir%FOUND THEN
835 l_has_payment_isir := 'Y';
836 ELSE
837 l_has_payment_isir := 'N';
841 l_has_isir := 'N';
838 END IF;
839 CLOSE c_has_payment_isir;
840 ELSE
842 END IF;
843 CLOSE c_has_isir;
844
845 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
846 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','l_has_isir:'||l_has_isir);
847 END IF;
848
849 OPEN c_has_profile(p_base_id);
850 FETCH c_has_profile INTO lc_has_profile;
851 IF c_has_profile%FOUND THEN
852 l_has_profile := 'Y';
853 OPEN c_has_active_profile(p_base_id);
854 FETCH c_has_active_profile INTO lc_has_active_profile;
855 IF c_has_active_profile%FOUND THEN
856 l_has_active_profile := 'Y';
857 ELSE
858 l_has_active_profile := 'N';
859 END IF;
860 CLOSE c_has_active_profile;
861 ELSE
862 l_has_profile := 'N';
863 END IF;
864 CLOSE c_has_profile;
865
866 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
867 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','l_has_profile:'||l_has_profile);
868 END IF;
869
870 -- Process for each To Do Items, if items are not null
871 IF p_item_number_1 IS NOT NULL THEN
872 ln_item_cnt := ln_item_cnt + 1;
873 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
874 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
875 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number_1/status/p_upd_mode:'||p_base_id||'/'||p_item_number_1||'/'||p_status_1||'/'||p_upd_mode);
876 END IF;
877 add_to_do( p_base_id, p_item_number_1, ln_item_cnt, p_person_id, p_status_1,p_upd_mode);
878 END IF;
879
880 IF p_item_number_2 IS NOT NULL THEN
881 ln_item_cnt := ln_item_cnt + 1;
882 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
883 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
884 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_2||'/'||p_status_1||'/'||p_upd_mode);
885 END IF;
886 add_to_do( p_base_id, p_item_number_2, ln_item_cnt, p_person_id, p_status_2,p_upd_mode);
887 END IF;
888
889 IF p_item_number_3 IS NOT NULL THEN
890 ln_item_cnt := ln_item_cnt + 1;
891 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
892 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
893 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_3||'/'||p_status_1||'/'||p_upd_mode);
894 END IF;
895 add_to_do( p_base_id, p_item_number_3, ln_item_cnt, p_person_id, p_status_3,p_upd_mode);
896 END IF;
897
898 IF p_item_number_4 IS NOT NULL THEN
899 ln_item_cnt := ln_item_cnt + 1;
900 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
901 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
902 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_4||'/'||p_status_1||'/'||p_upd_mode);
903 END IF;
904 add_to_do( p_base_id, p_item_number_4, ln_item_cnt, p_person_id, p_status_4,p_upd_mode);
905 END IF;
906
907 IF p_item_number_5 IS NOT NULL THEN
908 ln_item_cnt := ln_item_cnt + 1;
909 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
910 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
911 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_5||'/'||p_status_1||'/'||p_upd_mode);
912 END IF;
913 add_to_do( p_base_id, p_item_number_5, ln_item_cnt, p_person_id, p_status_5,p_upd_mode);
914 END IF;
915
916 IF p_item_number_6 IS NOT NULL THEN
917 ln_item_cnt := ln_item_cnt + 1;
918 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
919 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
920 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_6||'/'||p_status_1||'/'||p_upd_mode);
921 END IF;
922 add_to_do( p_base_id, p_item_number_6, ln_item_cnt, p_person_id, p_status_6,p_upd_mode);
923 END IF;
924
925 IF p_item_number_7 IS NOT NULL THEN
926 ln_item_cnt := ln_item_cnt + 1;
927 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
928 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
929 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_7||'/'||p_status_1||'/'||p_upd_mode);
930 END IF;
931 add_to_do( p_base_id, p_item_number_7, ln_item_cnt, p_person_id, p_status_7,p_upd_mode);
932 END IF;
936 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
933
934 IF p_item_number_8 IS NOT NULL THEN
935 ln_item_cnt := ln_item_cnt + 1;
937 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
938 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_8||'/'||p_status_1||'/'||p_upd_mode);
939 END IF;
940 add_to_do( p_base_id, p_item_number_8, ln_item_cnt, p_person_id, p_status_8,p_upd_mode);
941 END IF;
942
943 IF p_item_number_9 IS NOT NULL THEN
944 ln_item_cnt := ln_item_cnt + 1;
945 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
946 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
947 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_9||'/'||p_status_1||'/'||p_upd_mode);
948 END IF;
949 add_to_do( p_base_id, p_item_number_9, ln_item_cnt, p_person_id, p_status_9,p_upd_mode);
950 END IF;
951
952 IF p_item_number_10 IS NOT NULL THEN
953 ln_item_cnt := ln_item_cnt + 1;
954 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
955 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
956 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_10||'/'||p_status_1||'/'||p_upd_mode);
957 END IF;
958 add_to_do( p_base_id, p_item_number_10, ln_item_cnt, p_person_id, p_status_10,p_upd_mode);
959 END IF;
960
961 IF p_item_number_11 IS NOT NULL THEN
962 ln_item_cnt := ln_item_cnt + 1;
963 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
964 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
965 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_11||'/'||p_status_1||'/'||p_upd_mode);
966 END IF;
967 add_to_do( p_base_id, p_item_number_11, ln_item_cnt, p_person_id, p_status_11,p_upd_mode);
968 END IF;
969
970 IF p_item_number_12 IS NOT NULL THEN
971 ln_item_cnt := ln_item_cnt + 1;
972 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
973 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
974 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_12||'/'||p_status_1||'/'||p_upd_mode);
975 END IF;
976 add_to_do( p_base_id, p_item_number_12, ln_item_cnt, p_person_id, p_status_12,p_upd_mode);
977 END IF;
978
979 IF p_item_number_13 IS NOT NULL THEN
980 ln_item_cnt := ln_item_cnt + 1;
981 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
982 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
983 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_13||'/'||p_status_1||'/'||p_upd_mode);
984 END IF;
985 add_to_do( p_base_id, p_item_number_13, ln_item_cnt, p_person_id, p_status_13,p_upd_mode);
986 END IF;
987
988 IF p_item_number_14 IS NOT NULL THEN
989 ln_item_cnt := ln_item_cnt + 1;
990 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
991 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
992 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_14||'/'||p_status_1||'/'||p_upd_mode);
993 END IF;
994 add_to_do( p_base_id, p_item_number_14, ln_item_cnt, p_person_id, p_status_14,p_upd_mode);
995 END IF;
996
997 IF p_item_number_15 IS NOT NULL THEN
998 ln_item_cnt := ln_item_cnt + 1;
999 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1000 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','ln_item_cnt:'||ln_item_cnt);
1001 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling add_to_do with base_id/p_item_number/status/p_upd_mode:'||p_base_id||'/'||p_item_number_15||'/'||p_status_1||'/'||p_upd_mode);
1002 END IF;
1003 add_to_do( p_base_id, p_item_number_15, ln_item_cnt, p_person_id, p_status_15,p_upd_mode);
1004 END IF;
1005
1006 FND_MESSAGE.SET_NAME('IGF', 'IGF_AP_TODO_ADDED');
1007 FND_MESSAGE.SET_TOKEN('COUNT', TO_CHAR(ln_item_cnt-ln_skip_items));
1008 FND_FILE.PUT_LINE(FND_FILE.LOG ,FND_MESSAGE.GET);
1009 FND_FILE.PUT_LINE(FND_FILE.LOG ,' ');
1010 FND_FILE.PUT_LINE(FND_FILE.LOG ,' ');
1011
1012 -- Update the FA Process Stauses with the TO DO Details
1013 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1014 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling igf_ap_batch_ver_prc_pkg.update_process_status');
1015 END IF;
1019 WHEN others THEN
1016 igf_ap_batch_ver_prc_pkg.update_process_status(p_base_id, NULL);
1017
1018 EXCEPTION
1020 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1021 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_TODO_GRPS_PRC_PKG.EACH_STUDENT_TODO');
1022 IGS_GE_MSG_STACK.ADD;
1023 APP_EXCEPTION.RAISE_EXCEPTION;
1024
1025 END each_student_todo; -- End of each_student_todo
1026
1027 BEGIN -- Begin of assign_todo
1028
1029 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1030 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.assign_todo.debug','entering assign_todo');
1031 END IF;
1032 -- If both Person and Person ID Group are present then return back false
1033 IF p_base_id IS NOT NULL AND p_person_id_grp IS NOT NULL THEN
1034 FND_MESSAGE.SET_NAME('IGS','IGS_FI_NO_PERS_PGRP');
1035 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1036 RETURN FALSE;
1037
1038 ELSIF p_base_id IS NULL AND p_person_id_grp IS NULL THEN
1039 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
1040 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1041 RETURN FALSE;
1042
1043 ELSE
1044
1045 -- Add To DO to the person if base id is present
1046 -- else, get the base id from the person id groups and then add TO Do for each student.
1047 IF p_base_id IS NOT NULL THEN
1048
1049 -- log a message for the processing student.
1050 OPEN c_person_details(p_base_id);
1051 FETCH c_person_details INTO lc_person_details_rec;
1052 CLOSE c_person_details;
1053 IF p_calling_from = 'TODO' THEN
1054 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PROCESSING_STUDENT');
1055 FND_MESSAGE.SET_TOKEN('PERSON_NAME',lc_person_details_rec.person_name);
1056 FND_MESSAGE.SET_TOKEN('PERSON_NUMBER',lc_person_details_rec.person_number);
1057 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1058 END IF;
1059
1060 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1061 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.assign_todo.debug','calling each_student_todo for base_id:'||p_base_id);
1062 END IF;
1063 -- Process the student, add TO DO to the students
1064 each_student_todo(p_base_id, lc_person_details_rec.person_id );
1065
1066 ELSIF p_person_id_grp IS NOT NULL THEN
1067
1068 --Bug #5021084. Passing Group ID if the group type is STATIC.
1069 IF lv_group_type = 'STATIC' THEN
1070 -- Get all the Active students from the Person Group
1071 OPEN c_person_id_grp FOR 'SELECT person_id,person_number,full_name
1072 FROM igs_pe_person_base_v
1073 WHERE person_id in ('||lv_sql_stmt||') ' USING p_person_id_grp;
1074 ELSIF lv_group_type = 'DYNAMIC' THEN
1075 -- Get all the Active students from the Person Group
1076 OPEN c_person_id_grp FOR 'SELECT person_id,person_number,full_name
1077 FROM igs_pe_person_base_v
1078 WHERE person_id in ('||lv_sql_stmt||')';
1079 END IF;
1080
1081 LOOP
1082
1083 -- Check whether the student exists in the FA System, If present assign all TO Dos to the person, Else skip the student and mention the log message
1084
1085 FETCH c_person_id_grp INTO c_person_id_grp_rec;
1086 EXIT WHEN c_person_id_grp%NOTFOUND;
1087
1088 OPEN c_fa_base( c_person_id_grp_rec.person_id, p_awd_cal_type, p_awd_seq_num);
1089 FETCH c_fa_base INTO ln_base_id;
1090 IF c_fa_base%NOTFOUND THEN
1091
1092 OPEN c_get_parameters;
1093 FETCH c_get_parameters INTO l_person_number;
1094 CLOSE c_get_parameters;
1095 FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_number|| ' : '|| c_person_id_grp_rec.person_number);
1096
1097 -- Log a message and skip the student
1098 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_BASEID');
1099 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1100 ELSE
1101
1102 -- log a message for the processing student.
1103 IF p_calling_from = 'TODO' THEN
1104 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PROCESSING_STUDENT');
1105 FND_MESSAGE.SET_TOKEN('PERSON_NAME',c_person_id_grp_rec.full_name);
1106 FND_MESSAGE.SET_TOKEN('PERSON_NUMBER',c_person_id_grp_rec.person_number);
1107 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1108 END IF;
1109
1110 -- Process the student, add TO DO to the students
1111 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1112 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.assign_todo.debug','calling each_student_todo for base_id:'||p_base_id);
1113 END IF;
1114 each_student_todo(ln_base_id, c_person_id_grp_rec.person_id);
1115 END IF;
1116 CLOSE c_fa_base;
1117
1118 END LOOP; -- Person ID Group Loop
1119
1120 END IF;
1121 RETURN TRUE;
1122 END IF; -- End of check for both Person and Person ID Group
1123
1124 EXCEPTION
1125 WHEN others THEN
1126 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1127 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_TODO_GRPS_PRC_PKG.ASSIGN_TODO');
1128 IGS_GE_MSG_STACK.ADD;
1129 APP_EXCEPTION.RAISE_EXCEPTION;
1130
1131 END assign_todo;
1132
1133
1134 PROCEDURE main(
1135 errbuf OUT NOCOPY VARCHAR2,
1136 retcode OUT NOCOPY NUMBER,
1137 p_award_year IN VARCHAR2,
1138 p_person_id_grp IN NUMBER,
1139 p_base_id IN NUMBER,
1143 p_item_2 IN NUMBER,
1140 p_upd_mode IN VARCHAR2,
1141 p_item_1 IN NUMBER,
1142 p_status_1 IN VARCHAR2 DEFAULT NULL,
1144 p_status_2 IN VARCHAR2 DEFAULT NULL,
1145 p_item_3 IN NUMBER,
1146 p_status_3 IN VARCHAR2 DEFAULT NULL,
1147 p_item_4 IN NUMBER,
1148 p_status_4 IN VARCHAR2 DEFAULT NULL,
1149 p_item_5 IN NUMBER,
1150 p_status_5 IN VARCHAR2 DEFAULT NULL,
1151 p_item_6 IN NUMBER,
1152 p_status_6 IN VARCHAR2 DEFAULT NULL,
1153 p_item_7 IN NUMBER,
1154 p_status_7 IN VARCHAR2 DEFAULT NULL,
1155 p_item_8 IN NUMBER,
1156 p_status_8 IN VARCHAR2 DEFAULT NULL,
1157 p_item_9 IN NUMBER,
1158 p_status_9 IN VARCHAR2 DEFAULT NULL,
1159 p_item_10 IN NUMBER,
1160 p_status_10 IN VARCHAR2 DEFAULT NULL,
1161 p_item_11 IN NUMBER,
1162 p_status_11 IN VARCHAR2 DEFAULT NULL,
1163 p_item_12 IN NUMBER,
1164 p_status_12 IN VARCHAR2 DEFAULT NULL,
1165 p_item_13 IN NUMBER,
1166 p_status_13 IN VARCHAR2 DEFAULT NULL,
1167 p_item_14 IN NUMBER,
1168 p_status_14 IN VARCHAR2 DEFAULT NULL,
1169 p_item_15 IN NUMBER,
1170 p_status_15 IN VARCHAR2 DEFAULT NULL
1171 ) IS
1172 /*
1173 || Created By : brajendr
1174 || Created On : 23-Sep-2002
1175 || Purpose : Main process, does the main processing.
1176 || Known limitations, enhancements or remarks :
1177 || Change History :
1178 || Who When What
1179 || ridas 07-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
1180 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1181 || bvisvana 21-Jun-2005 Signature of main is changed to include status from 1 to 15
1182 || log_input_params and assign_todo signature changed
1183 || (reverse chronological order - newest change first)
1184 */
1185
1186 lv_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
1187 lv_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE;
1188 lb_return_value BOOLEAN := FALSE;
1189 ln_stdnt_count NUMBER := 0;
1190
1191 /* Variables for the dynamic person id group */
1192 lv_status VARCHAR2(1) := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
1193 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1194 lv_sql_stmt VARCHAR(32767) := igf_ap_ss_pkg.get_pid(p_person_id_grp,lv_status,lv_group_type);
1195
1196 BEGIN
1197 igf_aw_gen.set_org_id(NULL);
1198 retcode := 0;
1199
1200 -- Get the Award Year Calender Type and the Sequence Number for processing the students in context with the given Award Year.
1201 lv_ci_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
1202 lv_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
1203
1204 -- Log Input Parameters
1205 log_input_params(
1206 lv_ci_cal_type, lv_ci_sequence_number, p_base_id, p_person_id_grp,p_upd_mode,
1207 p_item_1, p_status_1, p_item_2, p_status_2,
1208 p_item_3, p_status_3, p_item_4, p_status_4,
1209 p_item_5, p_status_5, p_item_6, p_status_6,
1210 p_item_7, p_status_7, p_item_8, p_status_8,
1211 p_item_9, p_status_9, p_item_10, p_status_10,
1212 p_item_11, p_status_11, p_item_12, p_status_12,
1213 p_item_13, p_status_13, p_item_14, p_status_14,
1214 p_item_15, p_status_15
1215 );
1216
1217 -- If Person ID Group and Person ID both are present then, exit the process stating that either of the one should be present.
1218 IF p_base_id IS NOT NULL AND p_person_id_grp IS NOT NULL THEN
1219 errbuf:= FND_MESSAGE.GET_STRING('IGS','IGS_FI_NO_PERS_PGRP');
1220 retcode := 2;
1221 RETURN;
1222 ELSIF p_base_id IS NULL AND p_person_id_grp IS NULL THEN
1223 errbuf:= FND_MESSAGE.GET_STRING('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
1224 retcode := 2;
1225 RETURN;
1226 END IF;
1227
1228 -- Check whether the Person Groups has some records, if no students were attached to the student then log the message
1229
1230 IF p_person_id_grp IS NOT NULL THEN
1231
1232 /* Changing the string to get the count only*/
1233
1234 BEGIN
1235 lv_sql_stmt := 'SELECT COUNT(1) FROM ( '||lv_sql_stmt||')';
1236
1237 --Bug #5021084. Passing Group ID if the group type is STATIC.
1238 IF lv_group_type = 'STATIC' THEN
1239 EXECUTE IMMEDIATE lv_sql_stmt INTO ln_stdnt_count USING p_person_id_grp;
1240 ELSIF lv_group_type = 'DYNAMIC' THEN
1241 EXECUTE IMMEDIATE lv_sql_stmt INTO ln_stdnt_count;
1242 END IF;
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 FND_MESSAGE.SET_NAME ('IGF','IGF_AP_INVALID_QUERY');
1247 FND_FILE.PUT_LINE (FND_FILE.LOG,FND_MESSAGE.GET);
1248 RETURN;
1249 END;
1250
1251 IF ln_stdnt_count = 0 THEN
1252 FND_MESSAGE.SET_NAME('IGF','IGF_DB_NO_PER_GRP');
1253 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1254 retcode := 0;
1255 RETURN;
1256 END IF;
1257 END IF;
1258
1259 -- Assign TODO for all the students
1260 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1261 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.main.debug','calling assgin_todo');
1262 END IF;
1263 lb_return_value := assign_todo(
1264 p_base_id, p_person_id_grp, lv_ci_cal_type, lv_ci_sequence_number,p_upd_mode,
1265 p_item_1, p_status_1, p_item_2, p_status_2,
1266 p_item_3, p_status_3, p_item_4, p_status_4,
1267 p_item_5, p_status_5, p_item_6, p_status_6,
1268 p_item_7, p_status_7, p_item_8, p_status_8,
1269 p_item_9, p_status_9, p_item_10, p_status_10,
1270 p_item_11, p_status_11, p_item_12, p_status_12,
1271 p_item_13, p_status_13, p_item_14, p_status_14,
1272 p_item_15, p_status_15, 'TODO' );
1273
1274 -- If Person ID Group and Person ID both are present then, exit the process stating that either of the one should be present.
1275 IF lb_return_value = FALSE THEN
1276 IF p_base_id IS NOT NULL AND p_person_id_grp IS NOT NULL THEN
1277 errbuf:= FND_MESSAGE.GET_STRING('IGS','IGS_FI_NO_PERS_PGRP');
1278 retcode := 2;
1279 RETURN;
1280 ELSIF p_base_id IS NULL AND p_person_id_grp IS NULL THEN
1281 errbuf:= FND_MESSAGE.GET_STRING('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
1282 retcode := 2;
1283 RETURN;
1284 END IF;
1285 END IF;
1286 EXCEPTION
1287 WHEN others THEN
1288 ROLLBACK;
1289 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
1290 RETCODE := 2 ;
1291 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1292 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_TODO_GRPS_PRC_PKG.MAIN');
1293 errbuf := FND_MESSAGE.GET ;
1294 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
1295 END main;
1296
1297
1298 END igf_ap_todo_grps_prc_pkg;
[Home] [Help]