1 PACKAGE BODY igf_aw_lock_assgn_pkg AS
2 /* $Header: IGFAW18B.pls 120.3 2006/02/08 23:41:39 ridas noship $ */
3
4 ------------------------------------------------------------------------------
5 -- Who When What
6 --------------------------------------------------------------------------------
7
8
9 -- This procedure is the callable from concurrent manager
10 PROCEDURE main(
11 errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY NUMBER,
13 p_award_year IN VARCHAR2,
14 p_run_type IN VARCHAR2,
15 p_pid_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
16 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
17 p_run_mode IN VARCHAR2,
18 p_item_code IN igf_aw_item.item_code%TYPE,
19 p_term IN VARCHAR2
20 ) IS
21 --------------------------------------------------------------------------------
22 -- this procedure is called from concurrent manager.
23 -- if the parameters passed are not correct then procedure exits
24 -- giving reasons for errors.
25 -- Created by : ridas, Oracle India
26 -- Date created: 20-OCT-2004
27
28 -- Change History:
29 -- Who When What
30 -- ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call
31 -- to igf_ap_ss_pkg.get_pid
32 -- tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
33 --------------------------------------------------------------------------------
34
35 param_exception EXCEPTION;
36
37 -- Variables for the dynamic person id group
38 lv_status VARCHAR2(1);
39 lv_sql_stmt VARCHAR(32767);
40 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
41
42 TYPE CpregrpCurTyp IS REF CURSOR ;
43 cur_per_grp CpregrpCurTyp ;
44
45 TYPE CpergrpTyp IS RECORD(
46 person_id igf_ap_fa_base_rec_all.person_id%TYPE,
47 person_number igs_pe_person_base_v.person_number%TYPE
48 );
49 per_grp_rec CpergrpTyp ;
50
51
52 --Cursor below retrieves all the students belonging to a given AWARD YEAR
53 CURSOR c_per_awd_yr(
54 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
55 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
56 ) IS
57 SELECT fa.base_id
58 FROM igf_ap_fa_base_rec_all fa
59 WHERE fa.ci_cal_type = c_ci_cal_type
60 AND fa.ci_sequence_number = c_ci_sequence_number
61 ORDER BY fa.base_id;
62
63 l_per_awd_rec c_per_awd_yr%ROWTYPE;
64
65
66 --Cursor below retrieves the group code for the given group id
67 CURSOR c_group_code(
68 c_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE
69 ) IS
70 SELECT group_cd
71 FROM igs_pe_persid_group_all
72 WHERE group_id = c_grp_id;
73
74 l_grp_cd c_group_code%ROWTYPE;
75
76
77 --Cursor to fetch person no based on person id
78 CURSOR c_person_no (
79 c_person_id hz_parties.party_id%TYPE
80 ) IS
81 SELECT party_number
82 FROM hz_parties
83 WHERE party_id = c_person_id;
84
85 l_person_no c_person_no%ROWTYPE;
86
87 lv_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
88 ln_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
89 lv_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
90 ln_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
91 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
92 lv_err_msg fnd_new_messages.message_name%TYPE;
93 lv_return_flag VARCHAR2(1);
94
95
96 BEGIN
97 igf_aw_gen.set_org_id(NULL);
98 retcode := 0;
99 errbuf := NULL;
100 lv_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
101 ln_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
102 lv_ld_cal_type := LTRIM(RTRIM(SUBSTR(p_term,1,10)));
103 ln_ld_sequence_number := TO_NUMBER(SUBSTR(p_term,11));
104 lv_status := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
105
106
107 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
108 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_award_year:'||p_award_year);
109 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_run_type:'||p_run_type);
110 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_pid_group:'||p_pid_group);
111 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_base_id:'||p_base_id);
112 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_run_mode:'||p_run_mode);
113 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_item_code:'||p_item_code);
114 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','p_term:'||p_term);
115 END IF;
116
117 fnd_file.new_line(fnd_file.log,1);
118
119 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
120 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'),40) ||': '|| igf_gr_gen.get_alt_code(lv_ci_cal_type,ln_ci_sequence_number));
121
122 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','RUN_TYPE'),40) ||': '||p_run_type );
123
124 OPEN c_group_code(p_pid_group);
125 FETCH c_group_code INTO l_grp_cd;
126 CLOSE c_group_code;
127
128 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'),40) ||': '|| l_grp_cd.group_cd);
129 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),40) ||': '|| igf_gr_gen.get_per_num(p_base_id));
130 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','RUN_MODE'),40) ||': '||igf_aw_gen.lookup_desc('IGF_AW_LOCK_MODE',p_run_mode));
131 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','ITEM_CODE'),40) ||': '|| p_item_code);
132
133 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','TERM'),40) ||': '|| igf_gr_gen.get_alt_code(lv_ld_cal_type,ln_ld_sequence_number));
134
135 fnd_file.new_line(fnd_file.log,2);
136
137 IF (p_award_year IS NULL) OR (p_run_type IS NULL) OR (p_run_mode IS NULL) THEN
138 RAISE param_exception;
139
140 ELSIF lv_ci_cal_type IS NULL OR ln_ci_sequence_number IS NULL THEN
141 RAISE param_exception;
142
143 ELSIF (p_pid_group IS NOT NULL) AND (p_base_id IS NOT NULL) THEN
144 RAISE param_exception;
145
146 --If person selection is for all persons in the Person ID Group and
147 --Person ID Group is NULL then log error with exception
148 ELSIF p_run_type = 'P' AND p_pid_group IS NULL THEN
149 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_P');
150 fnd_file.put_line(fnd_file.log,fnd_message.get);
151 RAISE param_exception;
152
153 --If person selection is for a single person and
154 --Base ID is NULL then log error with exception
155 ELSIF p_run_type = 'S' AND p_base_id IS NULL THEN
156 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_S');
157 fnd_file.put_line(fnd_file.log,fnd_message.get);
158 RAISE param_exception;
159
160 END IF;
161
162
163 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
164
165 --COMPUTATION ONLY IF PERSON NUMBER IS PRESENT
166 IF p_run_type = 'S' AND (p_pid_group IS NULL) AND (p_base_id IS NOT NULL) THEN
167
168 fnd_file.new_line(fnd_file.log,1);
169 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
170 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(p_base_id));
171 fnd_file.put_line(fnd_file.log,fnd_message.get);
172
173 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
174 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','Starting Run_Type=S with base_id:'||p_base_id);
175 END IF;
176
177 IF p_run_mode = 'L' THEN
178 lv_return_flag := igf_aw_coa_gen.dolock(p_base_id,p_item_code,lv_ld_cal_type,ln_ld_sequence_number);
179 ELSIF p_run_mode = 'U' THEN
180 lv_return_flag := igf_aw_coa_gen.dounlock(p_base_id,p_item_code,lv_ld_cal_type,ln_ld_sequence_number);
181 END IF;
182
183 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
184 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','Run_Type=S done');
185 END IF;
186
187 --COMPUTATION FOR AWARD YEAR ONLY
188 ELSIF p_run_type = 'Y' AND (p_pid_group IS NULL) AND (p_base_id IS NULL) THEN
189 FOR l_per_awd_rec IN c_per_awd_yr(lv_ci_cal_type,ln_ci_sequence_number)
190 LOOP
191 fnd_file.new_line(fnd_file.log,1);
192 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
193 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(l_per_awd_rec.base_id));
194 fnd_file.put_line(fnd_file.log,fnd_message.get);
195
196 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
197 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','Starting Run_Type=Y with base_id:'||l_per_awd_rec.base_id);
198 END IF;
199
200 IF p_run_mode = 'L' THEN
201 lv_return_flag := igf_aw_coa_gen.dolock(l_per_awd_rec.base_id,p_item_code,lv_ld_cal_type,ln_ld_sequence_number);
202 ELSIF p_run_mode = 'U' THEN
203 lv_return_flag := igf_aw_coa_gen.dounlock(l_per_awd_rec.base_id,p_item_code,lv_ld_cal_type,ln_ld_sequence_number);
204 END IF;
205
206 END LOOP;
207
208 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
209 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','Run_Type=Y done');
210 END IF;
211
212 --COMPUTATION FOR ALL PERSONS IN THE PERSON ID GROUP
213 ELSIF (p_run_type = 'P' AND p_pid_group IS NOT NULL) THEN
214 --Bug #5021084
215 lv_sql_stmt := igf_ap_ss_pkg.get_pid(p_pid_group,lv_status,lv_group_type);
216
217 --Bug #5021084. Passing Group ID if the group type is STATIC.
218 IF lv_group_type = 'STATIC' THEN
219 OPEN cur_per_grp FOR
220 'SELECT person_id,
221 person_number
222 FROM igs_pe_person_base_v
223 WHERE person_id IN ('||lv_sql_stmt||') ' USING p_pid_group;
224 ELSIF lv_group_type = 'DYNAMIC' THEN
225 OPEN cur_per_grp FOR
226 'SELECT person_id,
227 person_number
228 FROM igs_pe_person_base_v
229 WHERE person_id IN ('||lv_sql_stmt||')';
230 END IF;
231
232 FETCH cur_per_grp INTO per_grp_rec;
233
234 IF (cur_per_grp%NOTFOUND) THEN
235 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
236 fnd_file.put_line(fnd_file.log,fnd_message.get);
237 ELSE
238 LOOP
239 -- check if person has a fa base record
240 ln_base_id := NULL;
241 lv_err_msg := NULL;
242
243 igf_gr_gen.get_base_id(
244 lv_ci_cal_type,
245 ln_ci_sequence_number,
246 per_grp_rec.person_id,
247 ln_base_id,
248 lv_err_msg
249 );
250
251 IF lv_err_msg = 'NULL' THEN
252 fnd_file.new_line(fnd_file.log,1);
253 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
254 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(ln_base_id));
255 fnd_file.put_line(fnd_file.log,fnd_message.get);
256
257 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
258 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','Starting Run_Type=P with base_id:'||ln_base_id);
259 END IF;
260
261 IF p_run_mode = 'L' THEN
262 lv_return_flag := igf_aw_coa_gen.dolock(ln_base_id,p_item_code,lv_ld_cal_type,ln_ld_sequence_number);
263 ELSIF p_run_mode = 'U' THEN
264 lv_return_flag := igf_aw_coa_gen.dounlock(ln_base_id,p_item_code,lv_ld_cal_type,ln_ld_sequence_number);
265 END IF;
266
267 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
268 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_lock_assgn_pkg.main.debug','Run_Type=P done');
269 END IF;
270
271 ELSE
272 OPEN c_person_no(per_grp_rec.person_id);
273 FETCH c_person_no INTO l_person_no;
274 CLOSE c_person_no;
275
276 fnd_message.set_name('IGF','IGF_AP_NO_BASEREC');
277 fnd_message.set_token('STUD',l_person_no.party_number);
278 fnd_file.new_line(fnd_file.log,1);
279 fnd_file.put_line(fnd_file.log,fnd_message.get);
280 END IF;
281
282 FETCH cur_per_grp INTO per_grp_rec;
283 EXIT WHEN cur_per_grp%NOTFOUND;
284 END LOOP;
285 CLOSE cur_per_grp;
286
287 END IF; -- end of IF (cur_per_grp%NOTFOUND)
288
289 END IF;
290
291 fnd_file.new_line(fnd_file.log,1);
292 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
293
294
295 COMMIT;
296
297 EXCEPTION
298 WHEN param_exception THEN
299 retcode:=2;
300 fnd_message.set_name('IGF','IGF_AW_PARAM_ERR');
301 igs_ge_msg_stack.add;
302 errbuf := fnd_message.get;
303
304 WHEN app_exception.record_lock_exception THEN
305 ROLLBACK;
306 retcode:=2;
307 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
308 igs_ge_msg_stack.add;
309 errbuf := fnd_message.get;
310
311 WHEN OTHERS THEN
312 ROLLBACK;
313 retcode:=2;
314 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
315 igs_ge_msg_stack.add;
316 errbuf := fnd_message.get || SQLERRM;
317 END main;
318
319 END igf_aw_lock_assgn_pkg;
[Home] [Help]