1 PACKAGE BODY igf_ap_ver_grps_prc_pkg AS
2 /* $Header: IGFAP29B.pls 120.5 2006/04/20 02:59:03 veramach ship $ */
3
4 lb_return_value BOOLEAN := FALSE;
5
6 PROCEDURE log_input_params( p_awd_cal_type IN igs_ca_inst.cal_type%TYPE ,
7 p_awd_seq_num IN igs_ca_inst.sequence_number%TYPE ,
8 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE ,
9 p_prs_grp_id IN VARCHAR2 ,
10 p_isir_field IN igf_ap_inst_ver_item.isir_map_col%TYPE ,
11 p_item_number_1 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
12 p_item_number_2 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
13 p_item_number_3 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
14 p_item_number_4 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
15 p_item_number_5 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
16 p_item_number_6 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
17 p_item_number_7 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
18 p_item_number_8 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
19 p_item_number_9 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
20 p_item_number_10 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
21 p_item_number_11 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
22 p_item_number_12 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
23 p_item_number_13 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
24 p_item_number_14 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
25 p_item_number_15 IN igf_ap_td_item_mst_all.todo_number%TYPE
26 ) AS
27 /*
28 || Created By : masehgal
29 || Created On : 26-Sep-2002
30 || Purpose : Logs all the Input Parameters
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 --Cursor to find the User Parameter Award Year (which is same as Alternate Code) to display in the Log
38 CURSOR c_alternate_code( cp_ci_cal_type igs_ca_inst.cal_type%TYPE ,
39 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE
40 ) IS
41 SELECT alternate_code
42 FROM igs_ca_inst
43 WHERE cal_type = cp_ci_cal_type
44 AND sequence_number = cp_ci_sequence_number ;
45
46 CURSOR c_get_parameters ( cp_lkup_type VARCHAR2 ) IS
47 SELECT meaning, lookup_code
48 FROM igf_lookups_view
49 WHERE lookup_type = cp_lkup_type
50 AND lookup_code IN ('AWARD_YEAR','PERSON_NUMBER','PERSON_ID_GROUP','ITEM_CODE',
51 'PARAMETER_PASS','ISIR_FIELD') ;
52 l_lkup_type VARCHAR2(60) ;
53
54 -- Get the details of Item codes and its descritpions
55 CURSOR c_item_details( cp_todo_number igf_ap_td_item_mst.todo_number%TYPE ) IS
56 SELECT description
57 FROM igf_ap_td_item_mst
58 WHERE todo_number = cp_todo_number ;
59
60 -- Get Verification Item Descrition
61 CURSOR c_get_verif_item (cp_ci_cal_type igs_ca_inst.cal_type%TYPE ,
62 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE ,
63 cp_isir_field igf_ap_inst_ver_item.isir_map_col%TYPE,
64 cp_lkup_type VARCHAR2 ) IS
65 SELECT lkup.meaning meaning
66 FROM igf_ap_batch_aw_map map,
67 Igf_fc_sar_cd_mst sar ,
68 igf_lookups_view lkup
69 WHERE map.ci_cal_type = cp_ci_cal_type
70 AND map.ci_sequence_number = cp_ci_sequence_number
71 AND sar.sys_award_year = map.sys_award_year
72 AND sar.sar_field_name = cp_isir_field
73 AND lkup.lookup_type = cp_lkup_type
74 AND lkup.lookup_code = sar.sar_field_name
75 AND lkup.enabled_flag = 'Y' ;
76
77 -- Get the person_number
78 CURSOR c_person_number( cp_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE ) IS
79 SELECT pe.person_number
80 FROM igs_pe_person_base_v pe,
81 igf_ap_fa_base_rec_all fa
82 WHERE pe.person_id = fa.person_id
83 AND fa.base_id = cp_base_id;
84
85 -- Get the details of group
86 CURSOR c_person_group( cp_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE ) IS
87 SELECT description
88 FROM igs_pe_persid_group
89 WHERE group_id = cp_person_id_grp;
90
91 -- Get Item Description
92 CURSOR c_item_descrption( cp_item_number IN igf_ap_td_item_mst_all.todo_number%TYPE ) IS
93 SELECT description
94 FROM igf_ap_td_item_mst_all
95 WHERE todo_number = cp_item_number;
96
97
98 parameter_rec c_get_parameters%ROWTYPE ;
99 verif_item_pmpt_rec c_get_verif_item%ROWTYPE ;
100 lc_item_description igf_ap_td_item_mst.description%TYPE ;
101 lv_awd_alternate_code igs_ca_inst.alternate_code%TYPE ;
102 lv_isir_field igf_lookups_view.meaning%TYPE ;
103 lv_incl_in_tol igf_lookups_view.meaning%TYPE ;
104
105 lv_award_year_pmpt igf_lookups_view.meaning%TYPE ;
106 lv_person_number_pmpt igf_lookups_view.meaning%TYPE ;
107 lv_person_id_grp_pmpt igf_lookups_view.meaning%TYPE ;
108 lv_item_code_pmpt igf_lookups_view.meaning%TYPE ;
109 lv_isir_field_pmpt igf_lookups_view.meaning%TYPE ;
110 l_para_pass igf_lookups_view.meaning%TYPE ;
111
112 l_person_number igs_pe_person_base_v.person_number%TYPE;
113 l_group_desc igs_pe_persid_group_all.description%TYPE;
114 l_item_description igf_ap_td_item_mst_all.description%TYPE;
115
116
117 BEGIN
118
119 -- Set all the Prompts for the Input Parameters
120 l_lkup_type := 'IGF_GE_PARAMETERS' ;
121 OPEN c_get_parameters (l_lkup_type );
122 LOOP
123 FETCH c_get_parameters INTO parameter_rec ;
124 EXIT WHEN c_get_parameters%NOTFOUND ;
125
126 IF parameter_rec.lookup_code ='AWARD_YEAR' THEN
127 lv_award_year_pmpt := TRIM ( parameter_rec.meaning ) ;
128
129 ELSIF parameter_rec.lookup_code ='PERSON_NUMBER' THEN
130 lv_person_number_pmpt := TRIM ( parameter_rec.meaning );
131
132 ELSIF parameter_rec.lookup_code ='PERSON_ID_GROUP' THEN
133 lv_person_id_grp_pmpt := TRIM ( parameter_rec.meaning );
134
135 ELSIF parameter_rec.lookup_code ='ITEM_CODE' THEN
136 lv_item_code_pmpt := TRIM ( parameter_rec.meaning ) ;
137
138 ELSIF parameter_rec.lookup_code ='ISIR_FIELD' THEN
139 lv_isir_field_pmpt := TRIM ( parameter_rec.meaning ) ;
140
141 ELSIF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
142 l_para_pass := TRIM ( parameter_rec.meaning ) ;
143
144 END IF;
145
146 END LOOP;
147 CLOSE c_get_parameters ;
148
149 -- Get the Award Year Alternate Code
150 OPEN c_alternate_code( p_awd_cal_type, p_awd_seq_num ) ;
151 FETCH c_alternate_code INTO lv_awd_alternate_code ;
152 CLOSE c_alternate_code ;
153
154 -- Get the Person Number
155 OPEN c_person_number(p_base_id);
156 FETCH c_person_number INTO l_person_number;
157 CLOSE c_person_number;
158
159 -- Get the Person Group
160 OPEN c_person_group(p_prs_grp_id);
161 FETCH c_person_group INTO l_group_desc;
162 CLOSE c_person_group;
163
164 -- Get verification item meaning
165 l_lkup_type := 'IGF_AP_SAR_FIELD_MAP' ;
166 OPEN c_get_verif_item( p_awd_cal_type,
167 p_awd_seq_num,
168 p_isir_field,
169 l_lkup_type ) ;
170 FETCH c_get_verif_item INTO verif_item_pmpt_rec ;
171 lv_isir_field := verif_item_pmpt_rec.meaning ;
172 CLOSE c_get_verif_item ;
173
174 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
175 FND_FILE.PUT_LINE( FND_FILE.LOG, l_para_pass) ; --------------Parameters Passed--------------
176 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
177
178 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( lv_award_year_pmpt, 40) || ' : '|| lv_awd_alternate_code ) ;
179 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( lv_person_number_pmpt, 40) || ' : '|| l_person_number ) ;
180 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( lv_person_id_grp_pmpt, 40) || ' : '|| l_group_desc ) ;
181
182 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( lv_isir_field_pmpt, 40) || ' : '|| lv_isir_field ) ;
183
184 l_item_description := NULL;
185 IF p_item_number_1 IS NOT NULL THEN
186 OPEN c_item_descrption(p_item_number_1);
187 FETCH c_item_descrption INTO l_item_description;
188 CLOSE c_item_descrption;
189 END IF;
190 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 1', 40) || ' : ' || l_item_description );
191
192 l_item_description := NULL;
193 IF p_item_number_2 IS NOT NULL THEN
194 OPEN c_item_descrption(p_item_number_2);
195 FETCH c_item_descrption INTO l_item_description;
196 CLOSE c_item_descrption;
197 END IF ;
198 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 2', 40) || ' : ' || l_item_description );
199
200 l_item_description := NULL;
201 IF p_item_number_3 IS NOT NULL THEN
202 OPEN c_item_descrption(p_item_number_3);
203 FETCH c_item_descrption INTO l_item_description;
204 CLOSE c_item_descrption;
205 END IF;
206 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 3', 40) || ' : ' || l_item_description );
207
208 l_item_description := NULL;
209 IF p_item_number_4 IS NOT NULL THEN
210 OPEN c_item_descrption(p_item_number_4);
211 FETCH c_item_descrption INTO l_item_description;
212 CLOSE c_item_descrption;
213 END IF ;
214 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 4', 40) || ' : ' || l_item_description );
215
216 l_item_description := NULL;
217 IF p_item_number_5 IS NOT NULL THEN
218 OPEN c_item_descrption(p_item_number_5);
219 FETCH c_item_descrption INTO l_item_description;
220 CLOSE c_item_descrption;
221 END IF;
222 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 5', 40) || ' : ' || l_item_description );
223
224 l_item_description := NULL;
225 IF p_item_number_6 IS NOT NULL THEN
226 OPEN c_item_descrption(p_item_number_6);
227 FETCH c_item_descrption INTO l_item_description;
228 CLOSE c_item_descrption;
229 END IF;
230 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 6', 40) || ' : ' || l_item_description );
231
232 l_item_description := NULL;
233 IF p_item_number_7 IS NOT NULL THEN
234 OPEN c_item_descrption(p_item_number_7);
235 FETCH c_item_descrption INTO l_item_description;
236 CLOSE c_item_descrption;
237 END IF;
238 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 7', 40) || ' : ' || l_item_description );
239
240 l_item_description := NULL;
241 IF p_item_number_8 IS NOT NULL THEN
242 OPEN c_item_descrption(p_item_number_8);
243 FETCH c_item_descrption INTO l_item_description;
244 CLOSE c_item_descrption;
245 END IF;
246 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 8', 40) || ' : ' || l_item_description );
247
248 l_item_description := NULL;
249 IF p_item_number_9 IS NOT NULL THEN
250 OPEN c_item_descrption(p_item_number_9);
251 FETCH c_item_descrption INTO l_item_description;
252 CLOSE c_item_descrption;
253 END IF;
254 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 9', 40) || ' : ' || l_item_description );
255
256 l_item_description := NULL;
257 IF p_item_number_10 IS NOT NULL THEN
258 OPEN c_item_descrption(p_item_number_10);
259 FETCH c_item_descrption INTO l_item_description;
260 CLOSE c_item_descrption;
261 END IF;
262 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 10', 40) || ' : ' || l_item_description );
263
264 l_item_description := NULL;
265 IF p_item_number_11 IS NOT NULL THEN
266 OPEN c_item_descrption(p_item_number_11);
267 FETCH c_item_descrption INTO l_item_description;
268 CLOSE c_item_descrption;
269 END IF;
270 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 11', 40) || ' : ' || l_item_description );
271
272 l_item_description := NULL;
273 IF p_item_number_12 IS NOT NULL THEN
274 OPEN c_item_descrption(p_item_number_12);
275 FETCH c_item_descrption INTO l_item_description;
276 CLOSE c_item_descrption;
277 END IF;
278 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 12', 40) || ' : ' || l_item_description );
279
280 l_item_description := NULL;
281 IF p_item_number_13 IS NOT NULL THEN
282 OPEN c_item_descrption(p_item_number_13);
283 FETCH c_item_descrption INTO l_item_description;
284 CLOSE c_item_descrption;
285 END IF;
286 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 13', 40) || ' : ' || l_item_description );
287
288 l_item_description := NULL;
289 IF p_item_number_14 IS NOT NULL THEN
290 OPEN c_item_descrption(p_item_number_14);
291 FETCH c_item_descrption INTO l_item_description;
292 CLOSE c_item_descrption;
293 END IF;
294 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 14', 40) || ' : ' || l_item_description );
295
296 l_item_description := NULL;
297 IF p_item_number_15 IS NOT NULL THEN
298 OPEN c_item_descrption(p_item_number_15);
299 FETCH c_item_descrption INTO l_item_description;
300 CLOSE c_item_descrption;
301 END IF;
302 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(lv_item_code_pmpt || ' 15', 40) || ' : ' || l_item_description );
303
304 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
305 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------');
306 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
307 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
308
309 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
310 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
311 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
312 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 NULL;
317 END log_input_params ;
318
319
320 FUNCTION dup_ver_item ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE ,
321 p_isir_field IN igf_ap_inst_ver_item.isir_map_col%TYPE
322 ) RETURN BOOLEAN AS
323
324 /*
325 || Created By : masehgal
326 || Created On : 26-Sep-2002
327 || Purpose :
328 || Known limitations, enhancements or remarks :
329 || Change History :
330 || Who When What
331 || (reverse chronological order - newest change first)
332 */
333
334 -- cursor to select existing record from igf_ap_inst_ver_item for a particular base_id and isir_map_col
335 CURSOR c_ver_item_exists ( cp_base_id igf_ap_inst_ver_item.base_id%TYPE ,
336 cp_isir_field igf_ap_inst_ver_item.isir_map_col%TYPE
337 ) IS
338 SELECT 1
339 FROM igf_ap_inst_ver_item
340 WHERE base_id = cp_base_id
341 AND isir_map_col = cp_isir_field ;
342
343 ver_item_exists_rec c_ver_item_exists%ROWTYPE ;
344 lv_ver_item_exists NUMBER ;
345
346 BEGIN
347 -- open cursor for given base id and isir_field
348 OPEN c_ver_item_exists ( p_base_id , p_isir_field ) ;
349 FETCH c_ver_item_exists INTO lv_ver_item_exists ;
350 IF c_ver_item_exists%FOUND THEN
351 CLOSE c_ver_item_exists ;
352 RETURN TRUE ;
353 ELSE
354 CLOSE c_ver_item_exists ;
355 RETURN FALSE ;
356 END IF ;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 CLOSE c_ver_item_exists ;
361 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP') ;
362 IGS_GE_MSG_STACK.ADD ;
363
364 RETURN TRUE ;
365 END ;
366
367
368
369 FUNCTION add_ver_item(
370 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE ,
371 p_awd_cal_type IN igs_ca_inst.cal_type%TYPE ,
372 p_awd_seq_num IN igs_ca_inst.sequence_number%TYPE ,
373 p_isir_field IN igf_ap_inst_ver_item.isir_map_col%TYPE ,
374 p_item_number_1 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
375 p_item_number_2 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
376 p_item_number_3 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
377 p_item_number_4 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
378 p_item_number_5 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
379 p_item_number_6 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
380 p_item_number_7 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
381 p_item_number_8 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
382 p_item_number_9 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
383 p_item_number_10 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
384 p_item_number_11 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
385 p_item_number_12 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
386 p_item_number_13 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
387 p_item_number_14 IN igf_ap_td_item_mst_all.todo_number%TYPE ,
388 p_item_number_15 IN igf_ap_td_item_mst_all.todo_number%TYPE
389 ) RETURN BOOLEAN AS
390 /*
391 || Created By : masehgal
392 || Created On : 26-Sep-2002
393 || Purpose :
394 || Known limitations, enhancements or remarks :
395 || Change History :
396 || Who When What
397 || rasahoo 17-Oct-2003 #3085558 FA121 Added parameter use_blank_flag in
398 || igf_ap_inst_ver_item_pkg.insert_row
399 || masehgal 21-May-2003 #2885882 FACR113 SAR Updates
400 || Changed cursors for SAR Number updates
401 || masehgal 22-Oct-2002 Added message to show duplicate ver item rather
402 || than eding the process as an error
403 || (reverse chronological order - newest change first)
404 */
405
406 lv_rowid ROWID := NULL ;
407 lv_meaning igf_lookups_view.meaning%TYPE;
408 l_isir_map_col igf_fc_sar_cd_mst.sar_field_number%TYPE;
409 CURSOR cur_isir_desc (cp_base_id igf_ap_fa_base_rec.base_id%TYPE ,
410 lv_isir_field igf_ap_inst_ver_item.isir_map_col%TYPE,
411 cp_lkup_type VARCHAR2 ) IS
412 SELECT sar.sar_field_number isir_map_col, lkup.meaning meaning
413 FROM igf_ap_batch_aw_map map,
414 igf_ap_fa_base_rec_all fabase,
415 Igf_fc_sar_cd_mst sar ,
416 igf_lookups_view lkup
417 WHERE fabase.base_id = p_base_id
418 AND map.ci_cal_type = fabase.ci_cal_type
419 AND map.ci_sequence_number = fabase.ci_sequence_number
420 AND sar.sys_award_year = map.sys_award_year
421 AND sar.sar_field_name = lv_isir_field
422 AND lkup.lookup_type = cp_lkup_type
423 AND lkup.lookup_code = sar.sar_field_name
424 AND lkup.enabled_flag = 'Y' ;
425
426 l_lkup_type VARCHAR2(60) ;
427
428 BEGIN
429
430 -- Check whether a person_id has been passed or not
431 IF p_base_id IS NOT NULL THEN
432
433 l_lkup_type := 'IGF_AP_SAR_FIELD_MAP' ;
434 OPEN cur_isir_desc (p_base_id, p_isir_field, l_lkup_type);
435 FETCH cur_isir_desc INTO l_isir_map_col, lv_meaning;
436 CLOSE cur_isir_desc;
437
438 -- check for dup_ver_item
439 IF NOT (dup_ver_item ( p_base_id => p_base_id ,
440 p_isir_field => l_isir_map_col ) ) THEN
441
442 -- Insert in IGF_AP_INST_VER_ITEM Table
443 igf_ap_inst_ver_item_pkg.insert_row
444 (
445 X_ROWID => lv_rowid ,
446 X_BASE_ID => p_base_id ,
447 X_UDF_VERN_ITEM_SEQ_NUM => NULL ,
448 X_ITEM_VALUE => NULL ,
449 X_WAIVE_FLAG => 'N' ,
450 X_INCL_IN_TOLERANCE => NULL ,
451 X_ISIR_MAP_COL => l_isir_map_col ,
452 x_legacy_record_flag => NULL ,
453 x_use_blank_flag => NULL,
454 X_MODE => 'R'
455 );
456
457 IF (
458 (p_item_number_1 IS NOT NULL) OR
459 (p_item_number_2 IS NOT NULL) OR
460 (p_item_number_3 IS NOT NULL) OR
461 (p_item_number_4 IS NOT NULL) OR
462 (p_item_number_5 IS NOT NULL) OR
463 (p_item_number_6 IS NOT NULL) OR
464 (p_item_number_7 IS NOT NULL) OR
465 (p_item_number_8 IS NOT NULL) OR
466 (p_item_number_9 IS NOT NULL) OR
467 (p_item_number_10 IS NOT NULL) OR
468 (p_item_number_11 IS NOT NULL) OR
469 (p_item_number_12 IS NOT NULL) OR
470 (p_item_number_13 IS NOT NULL) OR
471 (p_item_number_14 IS NOT NULL) OR
472 (p_item_number_15 IS NOT NULL) )
473 THEN
474
475
476 -- Insert To Do Items in IGF_AP_TD_ITEM_INST
477 lb_return_value := igf_ap_todo_grps_prc_pkg.assign_todo (
478 p_base_id => p_base_id ,
479 p_person_id_grp => NULL ,
480 p_awd_cal_type => p_awd_cal_type ,
481 p_awd_seq_num => p_awd_seq_num ,
482 p_upd_mode => 'DO_NO_UPD',
483 p_item_number_1 => p_item_number_1 ,
484 p_item_number_2 => p_item_number_2 ,
485 p_item_number_3 => p_item_number_3 ,
486 p_item_number_4 => p_item_number_4 ,
487 p_item_number_5 => p_item_number_5 ,
488 p_item_number_6 => p_item_number_6 ,
489 p_item_number_7 => p_item_number_7 ,
490 p_item_number_8 => p_item_number_8 ,
491 p_item_number_9 => p_item_number_9 ,
492 p_item_number_10 => p_item_number_10 ,
493 p_item_number_11 => p_item_number_11 ,
494 p_item_number_12 => p_item_number_12 ,
495 p_item_number_13 => p_item_number_13 ,
496 p_item_number_14 => p_item_number_14 ,
497 p_item_number_15 => p_item_number_15 ,
498 p_calling_from => 'VER_ITEM'
499 ) ;
500
501 END IF;
502
503 -- If there are no to do items required for application complete, then update the application process status.
504 -- Bug# 3240804 Whenever a new verification item gets added then update the fed_verifiation_status to 'SELECTED'
505 igf_ap_batch_ver_prc_pkg.update_fed_verif_status(p_base_id,'SELECTED');
506
507 ELSE
508
509 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_ITEM_PRESENT');
510 FND_MESSAGE.SET_TOKEN('ITEM', lv_meaning);
511 FND_FILE.PUT_LINE(FND_FILE.LOG ,FND_MESSAGE.GET);
512 END IF ;
513 END IF ;
514 RETURN lb_return_value;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 IF cur_isir_desc%ISOPEN THEN
519 CLOSE cur_isir_desc;
520 END IF;
521 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP') ;
522 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_ASSIGN_VER_ITEM_PKG.ADD_VER_ITEM') ;
523 IGS_GE_MSG_STACK.ADD ;
524 APP_EXCEPTION.RAISE_EXCEPTION ;
525 END add_ver_item ;
526
527
528 PROCEDURE main(
529 errbuf OUT NOCOPY VARCHAR2,
530 retcode OUT NOCOPY NUMBER,
531 p_awd_yr IN VARCHAR2,
532 p_prs_grp_id IN igs_pe_prsid_grp_mem.group_id%TYPE,
533 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
534 p_isir_field IN igf_ap_inst_ver_item.isir_map_col%TYPE,
535 p_item_1 IN igf_ap_td_item_mst_all.todo_number%TYPE,
536 p_item_2 IN igf_ap_td_item_mst_all.todo_number%TYPE,
537 p_item_3 IN igf_ap_td_item_mst_all.todo_number%TYPE,
538 p_item_4 IN igf_ap_td_item_mst_all.todo_number%TYPE,
539 p_item_5 IN igf_ap_td_item_mst_all.todo_number%TYPE,
540 p_item_6 IN igf_ap_td_item_mst_all.todo_number%TYPE,
541 p_item_7 IN igf_ap_td_item_mst_all.todo_number%TYPE,
542 p_item_8 IN igf_ap_td_item_mst_all.todo_number%TYPE,
543 p_item_9 IN igf_ap_td_item_mst_all.todo_number%TYPE,
544 p_item_10 IN igf_ap_td_item_mst_all.todo_number%TYPE,
545 p_item_11 IN igf_ap_td_item_mst_all.todo_number%TYPE,
546 p_item_12 IN igf_ap_td_item_mst_all.todo_number%TYPE,
547 p_item_13 IN igf_ap_td_item_mst_all.todo_number%TYPE,
548 p_item_14 IN igf_ap_td_item_mst_all.todo_number%TYPE,
549 p_item_15 IN igf_ap_td_item_mst_all.todo_number%TYPE
550 ) IS
551 /*
552 || Created By : masehgal
553 || Created On : 26-Sep-2002
554 || Purpose : Main process, does the main processing.
555 || Known limitations, enhancements or remarks :
556 || Change History :
557 || Who When What
558 || ridas 07-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
559 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
560 || (reverse chronological order - newest change first)
561 */
562
563 -- Get all Active persons from the given person_id_group.
564 /* Variables for the dynamic person id group */
565 lv_status VARCHAR2(1) := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
566 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
567
568 lv_sql_stmt VARCHAR(32767) := igf_ap_ss_pkg.get_pid(p_prs_grp_id,lv_status,lv_group_type);
569
570 /* Variable to change the dynamic sql stmt and get the count */
571 lv_sql_cnt VARCHAR(32767) ;
572
573 TYPE c_person_id_grpCurTyp IS REF CURSOR ;
574 c_person_id_grp c_person_id_grpCurTyp ;
575 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,
576 full_name igs_pe_person_base_v.full_name%TYPE );
577 c_person_id_grp_rec c_person_id_grp_recTyp ;
578
579 -- Check whether the sudent exists in the FA system or not.
580 CURSOR c_fa_base( cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE ,
581 cp_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ,
582 cp_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
583 ) IS
584 SELECT base_id
585 FROM igf_ap_fa_base_rec
586 WHERE person_id = cp_person_id
587 AND ci_cal_type = cp_ci_cal_type
588 AND ci_sequence_number = cp_ci_sequence_number ;
589
590
591 -- Get the person number and person name with the person id.
592 CURSOR c_person_details( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
593 SELECT pe.person_number, pe.full_name, fa.person_id
594 FROM igf_ap_fa_base_rec fa, igs_pe_person_base_v pe
595 WHERE fa.base_id = cp_base_id
596 AND fa.person_id = pe.person_id;
597
598
599 --- Get the Person Number prompt
600 CURSOR c_get_parameters ( cp_lkup_type VARCHAR2 ,
601 cp_lkup_code VARCHAR2 ) IS
602 SELECT meaning
603 FROM igf_lookups_view
604 WHERE lookup_type = cp_lkup_type
605 AND lookup_code = cp_lkup_code ;
606 l_lkup_type VARCHAR2(30) ;
607 l_lkup_code VARCHAR2(30) ;
608
609 lv_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE ;
610 lv_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ;
611 lc_person_details_rec c_person_details%ROWTYPE ;
612 ln_base_id_rec c_fa_base%ROWTYPE ;
613 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE ;
614 ln_stdnt_count NUMBER := 0;
615 l_person_number igf_lookups_view.meaning%TYPE;
616 l_datatype VARCHAR2(30);
617
618
619 BEGIN
620 igf_aw_gen.set_org_id(NULL);
621 retcode := 0;
622 -- Get the Award Year Calender Type and the Sequence Number
623 -- for processing the students in context with the given Award Year.
624 lv_ci_cal_type := RTRIM(SUBSTR(p_awd_yr,1,10));
625 lv_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_awd_yr,11)));
626
627
628 -- Log Input Parameters
629 log_input_params( lv_ci_cal_type, lv_ci_sequence_number, p_base_id, p_prs_grp_id,
630 p_isir_field, p_item_1, p_item_2,
631 p_item_3, p_item_4, p_item_5, p_item_6,
632 p_item_7, p_item_8, p_item_9, p_item_10,
633 p_item_11, p_item_12, p_item_13, p_item_14,
634 p_item_15
635 );
636 IF p_isir_field IN ('DRN', 'FAA_ADJUSTMENT', 'PARENTS_EMAIL_ADDRESS_TXT', 'DEPENDENCY_OVERRIDE_IND') THEN
637 return;
638 END IF;
639 -- If Person ID Group and Person ID both are present then,
640 -- exit the process stating that either of the one should be present.
641 IF p_base_id IS NOT NULL AND p_prs_grp_id IS NOT NULL THEN
642 FND_MESSAGE.SET_NAME('IGS','IGS_FI_NO_PERS_PGRP');
643 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
644 retcode := 2;
645 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_FI_NO_PERS_PGRP');
646 RETURN ;
647
648
649 ELSIF p_base_id IS NULL AND p_prs_grp_id IS NULL THEN
650 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
651 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
652 retcode := 2;
653 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
654 RETURN ;
655 END IF;
656
657 -- fnd_file.put_line(fnd_file.log, 'SQL Statement:'|| lv_sql_stmt);
658
659 IF p_prs_grp_id IS NOT NULL THEN
660 /* Changing the string to get the count only*/
661 BEGIN
662 lv_sql_cnt := 'SELECT COUNT(1) '||substr(lv_sql_stmt,instr(lv_sql_stmt,'FROM'));
663
664 --Bug #5021084. Passing Group ID if the group type is STATIC.
665 IF lv_group_type = 'STATIC' THEN
666 EXECUTE IMMEDIATE lv_sql_cnt INTO ln_stdnt_count USING p_prs_grp_id;
667 ELSIF lv_group_type = 'DYNAMIC' THEN
668 EXECUTE IMMEDIATE lv_sql_cnt INTO ln_stdnt_count;
669 END IF;
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 FND_MESSAGE.SET_NAME ('IGF','IGF_AP_INVALID_QUERY');
674 FND_FILE.PUT_LINE (FND_FILE.LOG,FND_MESSAGE.GET);
675 RETURN;
676 END;
677
678 IF ln_stdnt_count = 0 THEN
679 FND_MESSAGE.SET_NAME('IGF','IGF_DB_NO_PER_GRP');
680 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
681 retcode := 2;
682 errbuf := FND_MESSAGE.GET_STRING('IGF','IGF_DB_NO_PER_GRP');
683 RETURN;
684 END IF;
685 END IF ;
686
687
688 IF p_base_id IS NOT NULL THEN
689
690 OPEN c_person_details(p_base_id);
691 FETCH c_person_details INTO lc_person_details_rec;
692 CLOSE c_person_details;
693
694 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PROCESSING_STUDENT');
695 FND_MESSAGE.SET_TOKEN('PERSON_NAME', lc_person_details_rec.full_name);
696 FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', lc_person_details_rec.person_number);
697 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
698
699 -- process records
700 -- Assign verification item for all the students
701
702 lb_return_value := add_ver_item(
703 p_awd_cal_type => lv_ci_cal_type,
704 p_awd_seq_num => lv_ci_sequence_number,
705 p_base_id => p_base_id,
706 p_isir_field => p_isir_field,
707 p_item_number_1 => p_item_1,
708 p_item_number_2 => p_item_2,
709 p_item_number_3 => p_item_3,
710 p_item_number_4 => p_item_4,
711 p_item_number_5 => p_item_5,
712 p_item_number_6 => p_item_6,
713 p_item_number_7 => p_item_7,
714 p_item_number_8 => p_item_8,
715 p_item_number_9 => p_item_9,
716 p_item_number_10 => p_item_10,
717 p_item_number_11 => p_item_11,
718 p_item_number_12 => p_item_12,
719 p_item_number_13 => p_item_13,
720 p_item_number_14 => p_item_14,
721 p_item_number_15 => p_item_15
722 );
723
724 -- If person_grp_id is provided , loop for all persons, check for dup_ver_item for each person
725 ELSIF p_prs_grp_id IS NOT NULL THEN
726
727 --Bug #5021084. Passing Group ID if the group type is STATIC.
728 IF lv_group_type = 'STATIC' THEN
729 -- Get all the Active students from the Person Group
730 OPEN c_person_id_grp FOR 'SELECT person_id,person_number,full_name
731 FROM igs_pe_person_base_v
732 WHERE person_id in ('||lv_sql_stmt||') ' USING p_prs_grp_id;
733 ELSIF lv_group_type = 'DYNAMIC' THEN
734 -- Get all the Active students from the Person Group
735 OPEN c_person_id_grp FOR 'SELECT person_id,person_number,full_name
736 FROM igs_pe_person_base_v
737 WHERE person_id in ('||lv_sql_stmt||')';
738 END IF;
739
740 LOOP
741 -- 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
742 FETCH c_person_id_grp INTO c_person_id_grp_rec;
743 EXIT WHEN c_person_id_grp%NOTFOUND;
744
745 -- Check whether the student exists in the FA System,
746 -- If present assign all TO Dos to the person,
747 -- Else skip the student and mention the log message
748 -- log a message for the processing student.
749
750 OPEN c_fa_base( c_person_id_grp_rec.person_id, lv_ci_cal_type, lv_ci_sequence_number ) ;
751 FETCH c_fa_base INTO ln_base_id_rec ;
752
753 IF c_fa_base%NOTFOUND THEN
754
755 l_lkup_type := 'IGF_GE_PARAMETERS' ;
756 l_lkup_code := 'PERSON_NUMBER' ;
757 OPEN c_get_parameters ( l_lkup_type, l_lkup_code ) ;
758 FETCH c_get_parameters INTO l_person_number;
759 CLOSE c_get_parameters;
760 FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_number|| ' : '|| c_person_id_grp_rec.person_number);
761
762 -- Log a message and skip the student
763 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_BASEID');
764 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
765
766 ELSE
767 ln_base_id := ln_base_id_rec.base_id ;
768
769 OPEN c_person_details(ln_base_id);
770 FETCH c_person_details INTO lc_person_details_rec;
771 CLOSE c_person_details;
772 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PROCESSING_STUDENT');
773 FND_MESSAGE.SET_TOKEN('PERSON_NAME', lc_person_details_rec.full_name);
774 FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', lc_person_details_rec.person_number);
775 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
776
777
778 -- process records
779 -- Assign verification item for all the students
780 lb_return_value := add_ver_item(
781 p_awd_cal_type => lv_ci_cal_type,
782 p_awd_seq_num => lv_ci_sequence_number,
783 p_base_id => ln_base_id ,
784 p_isir_field => p_isir_field,
785 p_item_number_1 => p_item_1,
786 p_item_number_2 => p_item_2,
787 p_item_number_3 => p_item_3,
788 p_item_number_4 => p_item_4,
789 p_item_number_5 => p_item_5,
790 p_item_number_6 => p_item_6,
791 p_item_number_7 => p_item_7,
792 p_item_number_8 => p_item_8,
793 p_item_number_9 => p_item_9,
794 p_item_number_10 => p_item_10,
795 p_item_number_11 => p_item_11,
796 p_item_number_12 => p_item_12,
797 p_item_number_13 => p_item_13,
798 p_item_number_14 => p_item_14,
799 p_item_number_15 => p_item_15
800 );
801
802 END IF ;
803 CLOSE c_fa_base ;
804 END LOOP ;
805 END IF ;
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 ROLLBACK;
810 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM) ;
811 RETCODE := 2 ;
812 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP') ;
813 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_ASSIGN_VER_ITEM_PKG.MAIN') ;
814 errbuf := FND_MESSAGE.GET ;
815 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
816 END main;
817
818
819 END igf_ap_ver_grps_prc_pkg;
[Home] [Help]